$Applaud$

Query to Get PO Matched Invoices in Oracle Apps R12 - Orapoint

  • Home
  • Query to Get PO Matched Invoices in Oracle Apps R12

In this article we will show SQL query to get PO matched Invoices in Oracle Apps R12, This sql query will return a list of invoices that have been matched to purchase orders in Oracle apps. I’m going to share the table that this sql query uses. When we match a purchase order with a Payables Invoice, the purchase order’s PO_DISTRIBUTION_ID is stored in the Invoice’s ap_invoice_distributions_all.

Important tables name which are used in SQL query

PO_HEADERS_ALL contains header information for your purchasing documents. you will find one row for each document you create. There are six types of documents that use PO_HEADERS_ALL: RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders and Contracts.
PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. you will find one row for each distribution line you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing: Standard Purchase Orders, Planned Purchase Orders, Planned Purchase Order Releases and Blanket Purchase Order Releases.
AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice youenter. An invoice can have one or more invoice distribution lines.

Query to Get PO Matched Invoices in Oracle Apps R12

SELECT DISTINCT PHA.SEGMENT1 PO_NUMBER
,AIA.INVOICE_NUM INVOICE_NUMBER
,RSH.RECEIPT_NUM RECEIPT_NUMBER
FROM   PO_HEADERS_ALL PHA
,PO_DISTRIBUTIONS_ALL PDA
,AP_INVOICE_DISTRIBUTIONS_ALL AID
,AP_INVOICES_ALL AIA
WHERE PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
     AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
     AND AIA.INVOICE_ID=AID.INVOICE_ID
     AND PHA.SEGMENT1=NVL(:P_PO_NUM,PHA.SEGMENT1)
     AND AIA.INVOICE_NUM=NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)
ORDER BY 2;