Query to find uninvoiced receipts in Oracle apps

Hi friends, we are going to discuss about the Query to find uninvoiced receipts in Oracle apps. We will share the detail sql query which helps to extract the list of PO receipts which are still not invoiced in Oracle apps payables. In this post , We will be working to develop this Purchasing receipt query which will help to share the uninvoiced receipts against which we have not booked any AP invoice in oracle apps. In this query , we are talking about 3- way matching PO receipts. In 3- way matching PO's , we cannot the match the AP invoice with Purchase order directly.In 3- way matching PO , we can only the match the AP invoice with PO receipt. If any PO receipt , which is not yet matched to any AP Invoice we do call ad uninvoiced receipt in oracle apps. We will also share some of the important tables too which helps to store the uninvoiced receipts details in oracle apps. 

Query to find uninvoiced receipts in Oracle apps

6 Important Tables to find uninvoiced receipts in Oracle apps

1.RCV_SHIPMENT_LINES

2.AP_SUPPLIERS

3.AP_SUPPLIER_SITES_ALL

4.AP_INVOICES_ALL

5.RCV_TRANSACTIONS

6.RCV_SHIPMENT_HEADERS

 Detail level of Query to find uninvoiced receipts in Oracle apps

select a2.RECEIPT_NUM,

ASS.VENDOR_NAME,

AS_SITE.VENDOR_SITE_CODE,

A1.TRANSACTION_DATE "RECEIPT_DATE",

A1.PRIMARY_QUANTITY "RECEIPT_TRANSACTION_QTY",

A1.transaction_type

from

apps.rcv_shipment_headers a2,

APPS.AP_SUPPLIERS ASS,

APPS.AP_SUPPLIER_SITES_ALL AS_SITE,

apps.rcv_shipment_lines a3,

apps.rcv_transactions a1

where 1=1

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)

AND ASS.VENDOR_ID=AS_SITE.VENDOR_ID

AND A2.VENDOR_SITE_ID=AS_SITE.VENDOR_SITE_ID

AND A1.transaction_type = 'RECEIVE'

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

Query to find uninvoiced receipts in Oracle apps

Query to find uninvoiced receipts in Oracle apps