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 that fit the parameters you specify and then interfaces the cross charged distributions to General Ledger.

PRC: Interface Expense Reports from Payables(PAAPIMP)
This process gets expense report information from Payables. This process creates pre- approved expense report batches from expense report information entered in Self-Service Expenses or in the invoices window (in payables).

PRC: Interface Expense Reports to Payables (PATTER)
The Interface Expense Reports to Payables process collects all eligible expense reports, including adjustments, in Oracle Projects and interfaces them to the Oracle Payables interface tables.

PRC: Interface Intercompany Invoices to Receivables (PATTAR-IC)
This process collects all eligible intercompany invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables.

PRC: Interface Invoices to Receivables (PATTAR)
This process collects all eligible draft invoices in Oracle Projects and interfaces them to the Oracle Receivables interface tables. The process also maintains the project balances of unbilled receivable and unearned revenue and creates accounting transactions for these amounts.

PRC: Interface Labor Costs to General Ledger (PAGGLT)
This process collects all eligible labor costs in Oracle Projects and interfaces them to the Oracle General Ledger interface tables. The interface process also determines the liability account for the labor costs.

PRC: Interface Revenue to General Ledger (PATTGL)
This process collects all eligible revenue in Oracle Projects and interfaces it to the Oracle General Ledger interface tables. This process also maintains project balances for unbilled receivables and unearned revenue and creates accounting transactions for these amounts.
PRC: Interface Supplier Costs (PAAPIMP_SI)

The PRC: Interface Supplier Invoices from Payables process retrieves the following items and interface them to Oracle Projects: All eligible posted, project related supplier invoices from Oracle Payables, Tax lines for project related intercompany invoices. The process first populates the Transaction Import Interface table, creating a cost distributed expenditure item and cost distribution line for each invoice distribution line, and expenditure for each invoice.

PRC: Interface Supplier Invoice Adjustment Costs to Payables (PAVTVC)
This process collects all eligible supplier invoice adjustment costs in Oracle Projects and interfaces them to Oracle Payables. The process determines the liability account for the supplier invoice costs.
PRC: Interface Total Burdened Cost to GL PACTFTBC This process collects all eligible total burdened distribution lines in Oracle Projects and interfaces them to Oracle General Ledger.

PRC: Interface Usage and Miscellaneous Costs to General Ledger (PASGLT) This process collects all eligible cost distribution lines of the following transactions in Oracle Projects and interfaces them to the Oracle General Ledger interface tables: usage costs, miscellaneous transaction costs, burden transaction costs, and Inventory and WIP transactions not already costed or accounted. The interface process also determines the liability account for these costs.

PRC: Transaction Import PAXTRTRX Transaction Import
This is an open interface that enables you to load transactions from external cost collection systems into Oracle Projects. Transaction Import creates pre-approved expenditure items from transaction data entered in external cost collection systems.

Read More

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 explorer

Read More

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 null;

Read More

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 fails"

Read More

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_errors)

Read More

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 IS NOT NULL

and finally here is your scripts for Update
UPDATE fa_books b
SET life_in_months = (
SELECT m.attribute1
FROM fa_mass_additions m, fa_additions a
WHERE a.asset_id = b.asset_id
AND a.asset_number = m.asset_number
AND m.book_type_code LIKE 'BOOK NAME' --- pass the book
AND m.attribute1 IS NOT NULL)
WHERE b.book_type_code LIKE 'BOOK NAME' --- pass the book

Read More

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 don’t get confused as your customizations can still work as there are views created with names of PO_VENDORS, PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS for backward compatibility.

 

Being a part of the TCA, these tables are closely linked to the hz tables. Here is the list of few imp HZ Tables that are affected when a new supplier is added.

 

• HZ_PARTIES – master table along with AP_SUPPLIERS instead of PO_VENDORS
• HZ_PARTY_SITES – master table for supplier sites along with AP_SUPPLIER_SITES_ALL instead of PO_VENDORS_SITES_ALL
• HZ_LOCATIONS – contains the party sites information
• HZ_PARTY_USG_ASSIGNMENTS – stores party usages
• HZ_ORGANIZATION_PROFILES – Captures additional Supplier information, e.g. credit scoring details of Supplier or
• the Number of Employees working in Supplier Organization
• IBY_EXTERNAL_PAYEES_ALL – Captures Payment related details of the Supplier.
• POS_SUPPLIER_MAPPINGS – This table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID.

This is useful in cases whereby two vendors effectively belong the same HZ_Party Record.

 

see the diagram below for Supplier/Sites Mappings to TCA Objects.

 

Supplier/Sites Mappings to TCA Objects

 

 

So whenever a supplier is added in R12, an entry is made in all these tables.

 

Functionally also, creating the supplier is different from 11i. Here are the steps to create a new supplier.

 

1. Hit the CREATE SUPPLIER button

 

2. Enter a unique supplier name (Organization Name) along with other optional other information like Alias, Tax Registration Number, D-U-N-S number.

 

3. If the Supplier Number Entry option in the Payables System Setup window is set to Automatic, Payables automatically enters a Supplier Number for you. If this option is set to Manual, you must enter a unique Supplier Number.

 

4. Click Apply. The system creates the supplier record and accesses the Suppliers: Quick Update page.

 

5. To create Supplier Sites, you will have to create the locations for that supplier. For that, click on the “Address Book” Button.

 

6. On the Address Book page, click the Create button.

 

7. Fill all the address details and address purpose. i.e. Purchasing, Payment or RFQ Only.

 

8. You can add as many locations as you wish.

 

9. When created, you can manage the addresses for other information.

10. The system only displays sites that are in your MOAC profile

 

11. The address status indicates whether the supplier has provided any updates for the address. Using iSupplier Portal’s Supplier Profile Management tools, suppliers can enter address book information online, creating any number of new addresses, modifying the details for existing addresses, and indicating how each address is used.

 

12. Suppliers can also inactivate addresses that are obsolete. Buyer administrators need to approve any changes in order to update the master supplier details.

 

13. Address statuses include:
• New. A supplier has created a new address.
• Change Pending. A supplier has changed the address details. Click the Update icon to review the changes that have been made. The page displays the original address details and the changes, indicated by a blue dot. Buyer administrators can approve or make additional modifications to the changes before approving or rejecting the change. If the supplier has indicated that the address should be removed, there is a status change from Active to Inactive.
• Current. There are no pending updates for the address.

 

 14. Since suppliers are stored in TCA, the address details for the supplier may be used by other Oracle products so be careful if removing supplier addresses. If the address is inactivated, the system no longer associates it to any contacts, and any bank account assignments to the address are inactivated. Methods to inactivate addresses include:

• You can click the Remove button on the Address Book page. This sets the address status to inactive and sets the Inactive Date for every site that is associated with the address in all operating units to today’s date.
• You can update the address and set its status to Inactive. This changes the address status and does not inactivate any of the sites that are using the address.
• You can use the Manage Sites page to manually update the Inactive Date for each site.

 

Read More

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 gjjlv ,
gl_je_lines gje ,
gl_je_headers gjh ,
gl_je_batches gjb ,
ar_cash_receipts_all acra ,
apps.ra_customers ra ,
apps.gl_code_combinations_kfv glcc ,
ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID


Filter with Segment1 or other segments

Read More

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 (upper(wa.function) like '%'||upper('&func_name')||'%' or (wa.FUNCTION IS NULL AND '&func_name'='%'))
and (upper(wat.display_name) like '%'||upper('&act_name')||'%' or (wat.display_name IS NULL AND '&act_name'='%'))

Using this query we can find the workflow process name where the given function name or activity is being called.

Read More

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) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302 --- check it out
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
/

Read More

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 join.

Read More

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
• Intercompany invoice details for cross charged transactions
• PA_DRAFT_INVOICES_ALL
• Information about draft invoices generated for projects
• PA_DRAFT_REVENUES_ALL
• Information about draft revenue generated for projects
• PA_EXPENDITURE_COST_RATES_ALL
• Cost rates for non-labor expenditure types
• PA_EXPENDITURE_GROUPS_ALL
• Groups of pre-approved expenditures
• PA_EXPENDITURE_ITEMS_ALL
• The smallest units of expenditure charged to projects and tasks
• PA_EXPENDITURES_ALL
• Groups of expenditure items incurred by employees or organizations for an expenditure period
• PA_PERIODS_ALL
• Implementation-defined periods against which project performance is measured
• PA_PROJECT_ASSET_LINES_ALL
• Summarized project CIP costs
• PA_PROJECT_ASSETS_ALL
• Assets defined for capital projects
• PA_EXPENDITURE_TYPES
• Implementation-defined classifications of expenditures charged to projects and tasks
• PA_EXPENDITURE_CATEGORIES
• Implementation-defined groupings of expenditure types by type of cost
• PA_CLASS_CATEGORIES
• Implementation-defined categories for classifying projects
• PA_CLASS_CODES
• Implementation-defined values within class categories that can be used to classify projects
• PA_PROJECT_CLASSES
• Class codes of class categories that are used to classify projects
• PA_BUDGET_VERSIONS
• Versions of project budgets
• PA_BUDGET_LINES
• Detail lines of project and task budgets

Read More

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 := ’Smith’;
hz_cust_account_v2pub.create_cust_account( ’T’, p_cust_account_rec, p_person_rec,
p_customer_profile_rec, ’F’, x_cust_account_id, x_account_number, x_party_id,
x_party_number,
x_profile_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 = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>
FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More

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 := ’1272023’;
p_contact_point_rec.primary_flag := ’Y’;
p_contact_point_rec.contact_point_purpose := ’BUSINESS’;
p_phone_rec.phone_area_code := ’650’;
p_phone_rec.phone_country_code := ’1’;
p_phone_rec.phone_number := ’506-7000’;
p_phone_rec.phone_line_type := ’GEN’;
p_contact_point_rec.created_by_module := ’TCA_EXAMPLE’;
hz_contact_point_v2pub.create_contact_point( ’T’, p_contact_point_rec,
p_edi_rec, p_email_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_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 = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More

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 = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More

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 site id '||x_party_site_id);
dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;


hz_party_sites get updated

Read More

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 id '||x_location_id);
dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END

Record get created in hz_locations table

Read More

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 = ’||x_return_status,1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More

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 ai.po_header_id = ph.po_header_id(+)
and ph.PO_HEADER_ID = pda.PO_HEADER_ID
and ah.invoice_id = aid.invoice_id
and aid.po_distribution_id = pda.po_distribution_id
and aid.dist_code_combination_id = gcc.code_combination_id

Read More