Uninvoiced Receipts Query r12

Hi friends, we are going to discuss about Uninvoiced Receipts Query r12. We will share the detail sql query which helps to extract the details of PO receipts which is not yet booked in AP Invoice. Using this sql query we can find out the list of Uninvoiced PO receipts in oracle apps r12. If we have booked the purchase order with three-way matching then we do book the AP invoices against the PO receipts in oracle apps r12. In three-way matching we do match the AP invoice against the PO receipt. This below query will help to find out those PO receipts which are uninvoices means these PO receipts not matched again the AP invoice in oracle apps. We will also share the important table too which helps to find out the detail of  Uninvoiced Receipts Query r12. Please find below the complete detail about Uninvoiced Receipts Query r12.

Uninvoiced Receipts Query r12

7 Most Important Tables used by Uninvoiced Receipts Query r12.

1.RCV_TRANSACTIONS

2.RCV_SHIPMENT_HEADERS

3.RCV_SHIPMENT_LINES

4.AP_SUPPLIERS

5.AP_SUPPLIER_SITES_ALL

6.AP_INVOICES_ALL

7.AP_INVOICE_LINES_ALL

Detail Level of Uninvoiced Receipts Query r12

Here below , is the detail sql query which helps to extract the uninvoiced PO Receipts in Oracle Apps r12.

select a2.RECEIPT_NUM,

ASS.VENDOR_NAME,

AS_SITE.VENDOR_SITE_CODE,

A1.transaction_type,

A1.TRANSACTION_DATE "RECEIPT_DATE",

A1.PRIMARY_QUANTITY "RECEIPT_TRANSACTION_QTY"

from 

apps.rcv_transactions a1,

apps.rcv_shipment_headers a2,

apps.rcv_shipment_lines a3,

APPS.AP_SUPPLIERS ASS,

APPS.AP_SUPPLIER_SITES_ALL AS_SITE

where 1=1--transaction_id=401

and a2.shipment_header_id=a3.shipment_header_id

and a1.shipment_line_id=a3.shipment_line_id

and a2.shipment_header_id=a1.shipment_header_id

AND A2.VENDOR_ID=ASS.VENDOR_ID

AND ASS.VENDOR_ID=AS_SITE.VENDOR_ID

AND A2.VENDOR_SITE_ID=AS_SITE.VENDOR_SITE_ID

AND A1.transaction_type = 'RECEIVE'

AND A1.TRANSACTION_ID  NOT IN (SELECT AILA.RCV_TRANSACTION_ID FROM apps.AP_INVOICES_ALL aia,apps.ap_invoice_lines_all aila

WHERE AIA.CANCELLED_BY IS NULL

AND AIA.INVOICE_ID=AILA.INVOICE_ID

AND AILA.RCV_TRANSACTION_ID IS NOT NULL)

Uninvoiced Receipts Query r12

Uninvoiced Receipts Query r12