SQL Query to find open PO in Oracle Fusion

SQL Query to find open PO in Oracle Fusion

Hi friends, we are going to discuss about SQL Query to find open PO in Oracle Fusion. We will share the detail sql query to extract the open Purchase orders in Oracle Fusion. Using this sql query , we can find out the list of open PO's in oracle system. This is one of the most important sql query in oracle fusion. We will be share the two types of sql query in oracle fusion. First sql query will find the open PO as on date and the second query will find the open PO in back date. We will also share the important table related to open po in oracle fusion. Please find below the detail SQL Query to find open PO in Oracle Fusion.

3 Important Tables in SQL Query to find open PO in Oracle Fusion

1.po_lines_all

2.po_headers_all

3.poz_suppliers_v 

SQL Query to find open PO in Oracle Fusion

SQL Query to find open PO in Oracle Fusion on Back Date

select psv.vendor_name,pha.segment1 po_num, 

sum ( (pla.unit_price * pla.quantity)) PO_TOTAL,

TO_CHAR(pha.creation_date,'DD-MON-YYYY') PO_DATE

from po_lines_all pla, po_headers_all pha,poz_suppliers_v psv

where pla.po_header_id = pha.po_header_id

and pha.PRC_BU_ID=:P_BU_ID

and pha.vendor_id=psv.vendor_id

and pha.po_header_id in (select pha1.PO_HEADER_ID

from po_lines_all pla1, po_headers_all pha1,poz_suppliers_v psv1

where pla1.po_header_id = pha1.po_header_id

and pha1.PRC_BU_ID=:P_BU_ID

and pha1.vendor_id=psv1.vendor_id

and nvl((select sum(nvl(QUANTITY_INVOICED,0)) from ap_invoice_lines_all

where po_header_id is not null

and po_header_id=pha1.po_header_id 

and accounting_date  <='2019-03-31'

and po_line_id=pla1.po_line_id),0)<>pla1.quantity

and pha1.creation_date  <='2019-03-31')

group by psv.vendor_name,pha.segment1,TO_CHAR(pha.creation_date,'DD-MON-YYYY') 

SQL Query to find open PO in Oracle Fusion as on date.

select psv.vendor_name,pha.segment1 po_num, 

sum ( (pla.unit_price * pla.quantity)) PO_TOTAL,

TO_CHAR(pha.creation_date,'DD-MON-YYYY') PO_DATE

from po_lines_all pla, po_headers_all pha,poz_suppliers_v psv

where pla.po_header_id = pha.po_header_id

and pha.PRC_BU_ID=:P_BU_ID

and pha.vendor_id=psv.vendor_id

and pha.closed_code = 'OPEN'

group by psv.vendor_name,pha.segment1,TO_CHAR(pha.creation_date,'DD-MON-YYYY') 

SQL Query to find open PO in Oracle Fusion