

TCA
Sr. No.	Detail	API Name	Module
1	Create Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
2	Create a Customer Account Relationship API	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE	TCA
3	API to update a Customer Account Relationship TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE	TCA
4	API to Update a Customer Account TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT	TCA
5	Create Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE	TCA
6	API to Create a Customer Site TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE	TCA
7	API to Create a Customer Site Use TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE	TCA
8	API to Create a Party Site TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE	TCA
9	API to Create a Party Site Use TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE	TCA
10	API to create a Person Type Party and Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
11	API to Create a Phone Number (Contacts) TCA R12	HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT	TCA
12	API to create customer Addresses in TCA R12	HZ_LOCATION_V2PUB.CREATE_LOCATION	TCA
13	API to create Group in TCA R12	HZ_PARTY_V2PUB.CREATE_GROUP	TCA
14	API to populate loc_id HZ_LOCATIONS R12	HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT	TCA
15	API to update a Customer Profile TCA R12	HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE	TCA
16	API to Update Customer Address in Oracle TCA R12	HZ_LOCATION_V2PUB.UPDATE_LOCATION	TCA
17	Assign Receipt method to Customer via API	hz_payment_method_pub.create_payment_method	TCA
18	Create Party of type Organization in Oracle TCA using API	hz_party_v2pub.create_organization	TCA
AP
19	API to apply hold on AP invoice in R12	AP_HOLDS_PKG.INSERT_SINGLE_HOLD	AP
20	API to cancel single AP invoice	AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE	AP
21	API to find AP INVOICE Status	AP_INVOICES_PKG.GET_APPROVAL_STATUS	AP
22	API to release hold on AP invoice in R12	AP_HOLDS_PKG.RELEASE_SINGLE_HOLD	AP
23	Credit Memo Creation via API	ar_credit_memo_api_pub.create_request	AP
24	API to create Supplier in R12	pos_vendor_pub_pkg.Create_Vendor	AP
25	API to create supplier site in R12	pos_vendor_pub_pkg.create_vendor_site	AP
26	API to create or update Supplier Hub UDA attr	pos_vendor_pub_pkg.process_user_attrs_data
PO
27	API for Cancelling the Purchase Order (PO) Document	PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT	PO
28	API to Update Purchase Order Document (PO)	PO_CHANGE_API1_S.UPDATE_PO	PO
AOL
29	API to load value in value set	FND_FLEX_VALUES_PKG.LOAD_ROW	AOL
30	API to submit concurrent program	fnd_request.submit_request	AOL

SQL Query To find the PO Vendor Name from the Purchase Order

select ASP.* from PO_HEADERS_ALL PHA,AP_SUPPLIERS ASP
WHERE PHA.VENDOR_ID=ASP.VENDOR_ID
AND PHA.SEGMENT1=:PO_NUMBER
AND PHA.ORG_ID=:ORG_ID

SQL Query To find the PO Vendor SITE from the Purchase Order

select ASSA.*

from PO_HEADERS_ALL PHA,

AP_SUPPLIER_SITES_ALL ASSA
WHERE PHA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND PHA.SEGMENT1=:PO_NUMBER
AND PHA.ORG_ID=:ORG_ID

SQL Query To find the PO Matching AP Invoice Numbers from the Purchase Order

select *
from ap_invoice_lines_all aila,
ap_invoices_all aia,
po_headers_all pha
where aila.po_header_id=pha.po_header_id
and aila.invoice_id=aia.invoice_id
and pha.segment1=:PO_NUMBER

SQL Query To find the PO Lines from the Purchase Order

select * from
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PHA.SEGMENT1=:PO_NUMBER
AND PHA.ORG_ID=:ORG_ID

SQL Query To find the PO Distributions Data from the Purchase Order

select PDA.* from
PO_HEADERS_ALL PHA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND PHA.SEGMENT1=:PO_NUMBER
AND PHA.ORG_ID=:ORG_ID

SQL Query To find the PO Receipts from the Purchase Order

select * from

rcv_shipment_lines RSL,

rcv_shipment_headers RSH,

PO_HEADERS_ALL PHA
where 1=1
and RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
and RSL.po_header_id=PHA.po_header_id
and PHA.SEGMENT1=:PO_NUMBER

PO_HEADERS_ALL :

This is the Purchase Order Headers Table in Oracle Apps. Here below you will find po_headers_all table columns details.
We can find the po number in po_headers_all in Segment1 Column of PO_HEADERS_ALL table.
This table have information's like PO Number, PO Type , Vendor Id ,Vendor Site Id, Currency ,PO Status.

PO_LINES_ALL :

Here's Below we can find po_lines_all table description

This is the PO Lines Tables in Oracle Apps. In This Table We have Item Id , Qty , Item Description ,UOM , UNIT_PRICE and many other important Columns.

PO_DISTRIBUTIONS_ALL :

This Contains PO distributions Data. It handles the GL Account for the Line and Ship to locations too.

PO_LINE_LOCATIONS_ALL :

This is the PO Lines Tables in Oracle Apps. In This Table We have Item Id , Qty , Item Description ,UOM , UNIT_PRICE and many other important Columns. This table also provide information about total received qty against PO line and Total Invoiced Quantity too.

How to Unlock the Locked Table in ORACLE

Your package in not getting compiled and its keep on running forever? Are you getting lock objects issue? Is Oracle throwing error as ORA-04021 :timeout occurred

If Yes then its not your connection issue or system issue. Its issue with the objects which you are trying to compile.

Oracle puts locks while performing any DDL or DML operation on oracle tables. When table locks is present on any tables in Oracle we cannot run DDL on those tables. Some of the locks automatically set by oracle are RS and RX Locks.

We have to kill the session which holds the lock in order to execute further operations.

Follow the below steps to kill the session and forcibly unlock the table.

First we need to find which all objects are locked and then we need to kill the session and unlock the objects.

To find sid, serial# and process of locked object:

select a.sid||'|'|| a.serial#||'|'|| a.process
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME=upper('XX_OBJECT_NAME');

To find process holding the lock by passing table name:

select distinct a.process
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME=upper('XX_OBJECT_NAME');

To find blocking session and type of lock.

select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

How to kill blocking sessions:

To find sql_id from sid
SQL> select sql_id from v$session where sid=&sid;

To find sql_full text from sql_id
SQL > select sql_fulltext from gv$sql where sql_id ="&SLQ_ID";

To kill sessions:
SQL> alter session kill session 'sid,serial#' immediate;

Using above query you can easily find the locked objects or the session holding the locks into the database. Blocking locks are very common into the database so you must check and eliminate before it cause more damage to the database.

Base Tables in Oracle Payables(AP):

The base tables in AP are as follows:

1) AP_INVOICES_ALL

2) AP_INVOICE_PAYMENTS_ALL

3) AP_INVOICE_DISTRIBUTIONS_ALL

4) AP_PAYMENT_SCHEDULES_ALL

5)AP_PAYMENT_HISTORY_ALL

6)AP_CHECKS_ALL

7) AP_HOLDS_ALL

8) AP_AE_LINES_ALL

9) AP_AE_HEADERS_ALL

1) AP_INVOICES_ALL:

AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have one or more scheduled payments.

An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that it calculates on invoices that are overdue. Your Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.

2) AP_INVOICE_PAYMENTS_ALL:

AP_INVOICE_PAYMENTS_ALL contains records of invoice payments that you made to suppliers. There is one row for each payment you make for each invoice. There is one payment and one invoice for each payment in this table. Your Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment. When you void a payment, your Oracle Payables application inserts an additional payment line that is the negative of the original payment line.

Values for POSTED_FLAG may be 'Y' for accounted payments or 'N' for unaccounted payments. Values for ACCRUAL_POSTED_FLAG may be 'Y' for accounted payments or 'N' for unaccounted payments under accrual basis accounting; values for CASH_POSTED_FLAG

may be 'Y' for accounted payments or 'N' for unaccounted payments under cash basis accounting.For manual payments and Quick payments, this table corresponds to the Select Invoices window in the Payment workbench.

3) AP_INVOICE_DISTRIBUTIONS_ALL:

AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution information that is manually entered or system-generated. There is one row for each invoice distribution. A distribution must be associated with an invoice. An invoice can have multiple distributions.

4)AP_PAYMENT_SCHEDULES_ALL:

AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be ’Y’ to place a hold on the scheduled payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.

5) AP_PAYMENT_HISTORY_ALL:

AP_PAYMENT_HISTORY_ALL stores the clearing/unclearing history for payments. It also stores the maturity history for future dated payments. The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable. Any time a payment is cleared or uncleared, a row is inserted into this table for the payment. The values for TRANSACTION_TYPE can be PAYMENT MATURITY, PAYMENT CLEARING, or PAYMENT UNCLEARING. Each row in this table also has the accounting status for the maturity, clearing or unclearing event.

6) AP_CHECKS_ALL:

AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers. You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier's bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.

7) AP_HOLDS_ALL:

AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non–matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice–shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Approval), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID,and HOLD_LOOKUP_CODE.

8) AP_AE_LINES_ALL:

An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.

9)AP_AE_HEADERS_ALL:

An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was encountered during the transfer to GL process.

Before jumping to quick solution, lets look at why SOAP 1.2

Difference between SOAP 1.1 and SOAP 1.2

Offers a clear processing model
Provides improved interoperability with testing and implementation requirements
Based on XML Information Set
Offers protocol independence for developers by providing a binding framework
Includes HTTP binding for improved integration to the World Wide Web
Delivers a well-defined extensibility model
Provides improved support for Web standards.

WSDL Changes in SOAP 1.2

1. Namespace Changes: SOAP 1.2 supports the following namespace definition: xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"

2.SOAP 1.2 uses “application/soap+xml” as the Content Type, whereas SOAP 1.1 uses “text/xml”.

3.SOAP:Operation and SOAP Binding must be specified in SOAP 1.2 WSDL.

If you have any question please leave it in comment.

Oracle Apps -Learn and grow