Open purchase order query in oracle apps

Open purchase order query in oracle apps

In this post , We will be discuss about Open purchase order query in oracle apps. Open Purchase order in oracle apps are those purchase orders which are still not received or some of the qty is still pending for receive from supplier side or in second case all the quantity has been received but the AP invoice is not created against all the quantity of the Purchase order. So when ever the complete billing of the Purchase order will not done in AP , oracle will keep that Purchase order still open in the system. Here below I will share the complete details and the tables used to design the Open purchase order query in oracle apps.

 

Important Table of Open purchase order query in oracle apps

1.PO_HEADERS_ALL

2.PO_LINES_ALL

3.PO_LINE_LOCATIONS_ALL

3.PO_DISTRIBUTIONS_ALL

 

 

Example of Open purchase order query in oracle apps

 SELECT
 ,poh.segment1 "PO NUM"
 ,poh.authorization_status "STATUS"
 ,pla.line_num "SEQ NUM"
 ,plla.line_location_id
 ,d.po_distribution_id
 ,poh.type_lookup_code "TYPE"
 FROM
 ,po.po_headers_all poh
 ,po.po_lines_all pla
 ,po.po_line_locations_all plla
 ,po.po_distributions_all d
 WHERE
 poh.po_header_id = pla.po_header_id
 and plla.po_line_id = pla.po_Line_id
 and plla.line_location_id = d.line_location_id
 AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
  and poh.closed_date is NULL