INFO: Project Accounting main Interfaces

PRC: Interface Assets to Oracle Assets (PAXCPCAL) The Interface Assets process sends valid asset lines to Oracle Assets to become fixed assets. The process creates one mass addition line in Oracle Assets for each asset line in Oracle Projects, assigning the asset information you entered for the CIP asset to the mass addition line in Oracle Assets. Interface the costs to General Ledger before you run the Interface Assets process. PRC: Interface Cross Charge Distributions to General Ledger (PACCGLTR) The process identifies the cross charged transactions...

How : to fix the ieframe.dll error

When you are downloading any software, if you encounter any error like below. res://ieframe.dll/acr_error.htm is shown on the address bar Step 1) Go to internet tools, internet options, security , Custom Level Enable -> ActiveX Controls and Plug ins Step 2) Internet Options Advanced tab Settings Security-> Uncheck "Enable memory protection to help mitigate online attacks*" Step 3) restart internet explo...

Vendors with no Invoices

select segment1 VENDOR NBR, vendor_name NAME, vendor_site_code SITE_CODE, end_date_active VEND_INACTIVE, inactive_date SITE_INACTIVE from po_vendors vend, po_vendor_sites_all sites, ap_invoices_all inv where vend.vendor_id = sites.vendor_id and sites.vendor_site_id = inv.vendor_site_id(+) and inv.vendor_site_id is nu...

INFO: Project Accounting main tables

GL_JE_BATCHES, STATUS = 'P', means "Posted". GL_JE_BATCHES, STATUS = 'U', means unposted. GL_JE_BATCHES, STATUS = 'S' mean "Selecting". GL_JE_BATCHES, STATUS = 'A' implies "Funds reservation fai...

Query : List the orders that have failed in transaction

select order_number from oks_reprocessing a where a.success_flag = 'E' and order_number not in (Select nvl(source_header_ref,'11111') from csi_txn_erro...

How: Need to Update

SELECT a.asset_number, b.life_in_months LIFE_IN_BOOKS, m.asset_number, m.attribute1 LIFE_IN_MASS_ADD FROM fa_additions a, fa_books b, fa_mass_additions m WHERE a.asset_id = b.asset_id AND a.asset_number = m.asset_number AND b.book_type_code LIKE 'BOOK NAME' --- pass the book AND m.attribute1 IS NOT NULL once you get , you need to take count SELECT COUNT(*) FROM fa_additions a, fa_books b, fa_mass_additions m WHERE a.asset_id = b.asset_id AND a.asset_number = m.asset_number AND b.book_type_code LIKE 'BOOK NAME' --- pass the book AND m.attribute1...

Walk through R12 – Oracle Suppliers

In R12 Accounts Payables, Suppliers have gone to self-service now. This is not the only change in the supplier. The suppliers objects have moved from AP product to TCA (Trading Community Architecture) DataModel. Due to this, even the underlying tables have changed. Supplier information is no more stored in PO_VENDORS Table now.  3 new tables have been introduced.  • AP_SUPPLIERS • AP_SUPPLIER_SITES_ALL • AP_SUPPLIER_CONTACTS  But...

Query : AR to GL data transfer

SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By" FROM apps.gl_je_journal_lines_v...

Query : to find the process in Workflow using a known function name or Activity name

select wat1.display_name "process name",wat.display_name "activity name",wa.function "function" from wf_process_activities wpa ,wf_activities_tl wat ,wf_activities_tl wat1 ,wf_activities wa where wpa.activity_item_type = wa.item_type and wpa.instance_label = wa.name and wat.item_type = wa.item_type and wat.version = wa.version and wat.language = 'US' and wat.name = wa.name and wat1.name = wpa.process_name and wat1.item_type = wat.item_type and wat1.version = wat.version and wat1.language = wat.language and wat1.version = wpa.process_version and...

Query : Query to find receipts against a PO shipment line

execute fnd_client_info.set_org_context('org_id'); SELECT pol.po_header_id, pol.po_line_id, pll.line_location_id, pll.quantity, rsh. shipment_header_id, rsh. receipt_source_code, rsh. vendor_id, rsh. vendor_site_id, rsh. organization_id, rsh. shipment_num, rsh. receipt_num, rsh. ship_to_location_id, rsh. bill_of_lading, rsl.shipment_line_id, rsl.QUANTITY_SHIPPED, rsl.QUANTITY_RECEIVED , rct.transaction_type, rct.transaction_id, decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0), nvl(rct.source_doc_quantity,0)...

R12 - how does iExpenses join to ap_checks_all

R12 does have the ap_expense_report_headers_all.vouchno column. You can join the ap_expense_report_headers_all.vouchno with ap_invoices_all.invoice_id. Alternatively, you can also use the following join, ap_invoices_all.PRODUCT_TABLE = 'AP_EXPENSE_REPORT_HEADERS_ALL' AND ap_invoices_all.REFERENCE_KEY1 = ap_expense_report_headers_all.report_header_id. However, would suggested to  use the invoice_id and vouchno jo...

INFO: Project Accounting main tables

Here are the below are important Oracle Project Accounting tables PA_PROJECTS_ALL Information about projects PA_AGREEMENTS_ALL Customer contracts that serve as the basis for work authorization PA_BILL_RATES_ALL Information about bill rates and markups of standard bill rate schedules PA_BILLING_ASSIGNMENTS_ALL Assignments of billing extensions to a project type, project, or task PA_COST_DISTRIBUTION_LINES_ALL Information about expenditure item cost distribution PA_CUST_REV_DIST_LINES_ALL Information about expenditure item revenue distribution PA_DRAFT_INVOICE_DETAILS_ALL...

API: Customer API -Create a Customer Account

DECLARE p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE; p_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE; p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE REC_TYPE; x_cust_account_id NUMBER; x_account_number VARCHAR2(2000); x_party_id NUMBER; x_party_number VARCHAR2(2000); x_profile_id NUMBER; x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); BEGIN p_cust_account_rec.account_name := ’John’’s A/c’; p_cust_account_rec.created_by_module := ’TCA_EXAMPLE’; p_person_rec.person_first_name := ’John’; p_person_rec.person_last_name...

API: Customer API -Create a Contact Point ( of type Phone)

DECLARE p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE; p_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE; p_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE; p_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE; p_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE; p_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE; x_return_status VARCHAR2(2000); x_msg_coun t NUMBER; x_msg_data VARCHAR2(2000); x_contact_point_id NUMBER; BEGIN p_contact_point_rec.contact_point_type := ’PHONE’; p_contact_point_rec.owner_table_name := ’HZ_PARTIES’; p_contact_point_rec.owner_table_id...

API: Customer API -Create a Party Site Use Using the above party site create a party site use

DECLARE p_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_RE C_TYPE; x_party_site_use_id NUMBER; x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); BEGIN p_party_site_use_rec.site_use_type := ’SHIP_TO’; p_party_site_use_rec.party_site_id := 349327; p_party_site_use_rec.created_by_module := ’TCA_EXAMPLE’; hz_party_site_v2pub.create_party_site_use( ’T’, p_party_site_use_rec, x_party_site_use_id, x_return_status, x_msg_count, x_msg_data); dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,1,255)); dbms_output.put_line(’x_msg_count...

API: Customer API -Create a Party site

DECLARE p_party_site_rec HZ_PARTY_SITE_V2PU B.PARTY_SITE_REC_TYPE; x_party_site_id NUMBER; x_party_site_number VARCHAR2(2000); x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); BEGIN p_party_site_rec.party_id := 1272023; p_party_site_rec.location_id := 359086; p_party_site_rec.identifying_address_flag := ’Y’; p_party_site_rec.created_by_module := ’TCA_EXAMPLE’; hz_party_site_v2pub.create_party_site( ’T’, p_party_site_rec, x_party_site_id, x_party_site_number, x_return_status, x_msg_count, x_msg_data); dbms_output.put_line('party...

API: Customer API -Create a Location

DECLARE p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE; x_location_id NUMBER; x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); BEGIN p_location_rec.country := ’US’; p_location_rec.address1 := ’300 Oracle Parkway’; p_location_rec.address2 := ’13th Floor’; p_location_rec.city := ’Redwood Shores’; p_location_rec.postal_code := ’94065’; p_location_rec.state := ’CA’; p_location_rec.created_by_module := ’TCA_EXAMPLE’; hz_location_v2pub.create_location( ’T’, p_location_rec, x_location_id, x_return_status, x_msg_count, x_msg_data); dbms_output.put_line('location...

API: Customer API -Create an Organization

DECLARE p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; x_return_status VARCHAR2(2000); x_msg_count NUMBER; x_msg_data VARCHAR2(2000); x_party_id NUMBER; x_party_number VARCHAR2(2000); x_profile_id NUMBER; BEGIN p_organization_rec.organization_name := ’ABC Corporation’; p_organization_rec.created_by_module := ’TCA_EXAMPLE’; hz_party_v2pub.create_organization ( ’T’, p_organization_rec, x_return_status, x_msg_count, x_msg_data, x_party_id, x_party_number, x_profile_id); dbms_output.put_line('party id '||x_party_id); dbms_output.put_line(SubStr(’x_return_status...

Query : Invoices on hold

select distinct ai.invoice_num,ai.invoice_amount,ai.invoice_date ,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number ,ph.segment1 po_number ,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 --,aid.AMOUNT from ap_invoices_all ai ,ap_holds_all ah ,po_vendors pv ,po_headers_all ph ,ap_invoice_distributions_all aid ,po_distributions_all pda ,gl_code_combinations gcc where 1=1 and ai.invoice_id = ah.invoice_id and ai.vendor_id = pv.vendor_id --and...