SQL Query to Find the PO which are closed for Invoicing in oracle apps r12: Purchase Order Closed for Invoicing SQL Query

SQL Query to Find the PO which are closed for Invoicing in oracle apps r12: Purchase Order Closed for Invoicing SQL Query

In this Post, we will be discuss about the SQL query which helps to find the Purchase Orders which are closed for Invoicing in oracle apps. PO closed for Invoicing means, we cannot create the new AP invoice in Oracle and then match with that Purchase order. System automatically set status for the PO shipment line, when the Line Quantity and the Invoice Billed Quantity matched. So if we have to find the details for the Purchase orders which are closed for Invoicing then we can refer this below sql query in oracle apps. We do get this status in PO distributions, So it represents the status for the PO line so there is an change some of the lines status showing ‘closed for Invoicing’ and other lines are still open so for those PO lines we can still create the AP invoice and match with that PO line.

DB Table to see the PO line Status (“Closed for Invoice”, “Closed for Receiving”)

“Closed for Invoice” or “Closed for Receiving” statuses stored in the CLOSED_CODE column of the PO_LINE_LOCATIONS_ALL DB table.

Detail SQL Query to Find the PO which are closed for Invoicing in oracle apps r12

Here below is the Details SQL query, to extract the list of Purchase order line closed for Invoicing.

SELECT PHA.SEGMENT1 PO_NUM,

       PLA.LINE_NUM PO_LINE_NUM,

       PLLA.SHIPMENT_NUM,

       PLA.ITEM_DESCRIPTION,     

       PLA.QUANTITY PO_LINE_QUANTITY,

       PHA.AUTHORIZATION_STATUS HEADER_APPROVAL_STATUS, PHA.APPROVED_FLAG HEADER_APPROVED_FLAG, PHA.APPROVED_DATE HEADER_APPROVED_DATE,

       PHA.CLOSED_CODE HEADER_CLOSURE_STATUS,

       PLA.CLOSED_CODE LINE_CLOSURE_STATUS,

       PLLA.CLOSED_CODE SHIPMENT_CLOSURE_STATUS,    

       PLLA.APPROVED_FLAG SHIPMENT_APPROVED_FLAG, PLLA.APPROVED_DATE SHIPMENT_APPROVED_DATE, 

       PHA.CANCEL_FLAG PO_CANCEL_FLAG, PLA.CANCEL_FLAG LINE_CANCEL_FLAG, PLLA.CANCEL_FLAG SHIPMENT_CANCEL_FLAG

       , PHA.ORG_ID, PHA.PO_HEADER_ID       

  FROM PO.PO_LINE_LOCATIONS_ALL PLLA,

       PO.PO_LINES_ALL PLA,

       PO.PO_HEADERS_ALL PHA    

 WHERE PLA.PO_LINE_ID = PLLA.PO_LINE_ID

   AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

   AND PHA.SEGMENT1=:P_PO_NUMBER

   AND NVL(PLLA.CLOSED_CODE,'TT') IN ('CLOSED FOR INVOICE')

ORDER BY PHA.SEGMENT1,PLA.LINE_NUM，PLLA.SHIPMENT_NUM;

SQL Query to Find the PO which are closed for Invoicing in oracle apps r12: Purchase Order Closed for Invoicing SQL Query