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.