Query to find open po in oracle apps

Query to find open po in oracle apps

In this post , We will discuss about Query to find open po in oracle apps. This SQL query help to find all the Open Purchase order from the system which is still pending for receiving or Pending for AP invoice also. This sql query helps to provide the complete purchase order detail. Here below is the complete Query to find open po in oracle apps.

 

Example of Query to find open po in oracle apps.

 

SELECT DISTINCT POH.VENDOR_NAME,POH.SEGMENT1 PO_NUMBER,

pol.line_num "Line",

pltl.line_type "Line Type",

--msib.segment1 "Item",

(SELECT msi.segment1

FROM mtl_system_items_b msi

WHERE msi.inventory_item_id=pol.item_id

AND ROWNUM=1) "Item",

pol.item_description "Item Description",

pol.item_revision "Item Revision",

mcbkfv.concatenated_segments "Category Name",

pol.amount "Amount",

(SELECT SUM(plla.quantity)-(SUM(plla.quantity_cancelled)+SUM(plla.quantity_received))

FROM po_line_locations_all plla

WHERE plla.po_line_id=pol.po_line_id

) "Quantity",

(SELECT uom_code

FROM mtl_units_of_measure_tl

WHERE unit_of_measure=pol.unit_meas_lookup_code

AND language =USERENV('LANG')

) "UOM",

pol.unit_price "price",

pol.secondary_qty "Secondary Quantity",

pol.secondary_uom "Secondary UOM",

NULL "Supplier Item",

pol.negotiated_by_preparer_flag "Negotiated",

(SELECT pohz.hazard_class

FROM po_hazard_classes_tl pohz

WHERE 1 =1

AND pohz.language =USERENV('LANG')

AND pohz.hazard_class_id=pol.hazard_class_id

) "Hazard Class",

(SELECT poun.un_number

FROM po_un_numbers_tl poun

WHERE 1 =1

AND poun.language =USERENV('LANG')

AND poun.un_number_id=pol.un_number_id

) "UN Number",

pol.note_to_vendor "Note to Supplier"

FROM po_lines_all pol,

po_line_types_tl pltl,

mtl_categories_b_kfv mcbkfv,

( Select aps.VENDOR_NAME,poh.*

FROM po_headers_all poh,

xle_entity_profiles lep,

hr_operating_units hro,

hr_all_organization_units_vl hroutl_bg, --vl table has to be used

ap_supplier_sites_all sup_site,

ap_suppliers aps,

hr_locations bill,

hr_locations ship,

per_all_people_f buyer,

 

 

po_doc_style_headers sty

 

WHERE 1 =1

AND NVL(poh.closed_code,'OPEN') ='OPEN'

AND NVL(poh.cancel_flag,'N') ='N'

AND poh.type_lookup_code ='STANDARD'

AND sty.style_id =poh.style_id

AND lep.transacting_entity_flag ='Y'

AND lep.legal_entity_id =hro.default_legal_context_id

AND hroutl_bg.organization_id =hro.business_group_id

AND hro.organization_id =poh.org_id

AND aps.vendor_id =poh.vendor_id

AND sup_site.vendor_site_id =poh.vendor_site_id

AND bill.location_id =poh.ship_to_location_id

AND ship.location_id =poh.bill_to_location_id

AND buyer.person_id =poh.agent_id

AND EXISTS

(SELECT 1

FROM po_line_locations_all plla

WHERE 1=1

AND plla.closed_code='OPEN'

AND NVL(plla.cancel_flag,'N')='N'

AND (plla.quantity -(plla.quantity_cancelled+plla.quantity_received))>0

AND plla.po_header_id =poh.po_header_id

)

AND SYSDATE BETWEEN buyer.effective_start_date AND NVL(buyer.effective_end_date,SYSDATE+1)

and hro.name=:P_OU_NMAE

) poh

WHERE 1 =1

AND NVL(pol.closed_code,'OPEN') ='OPEN'

AND NVL(pol.cancel_flag,'N') ='N'

AND poh.po_header_id =pol.po_header_id

AND EXISTS

(SELECT 1

FROM po_line_locations_all plla

WHERE 1=1

AND plla.po_line_id=pol.po_line_id

AND plla.closed_code='OPEN'

AND NVL(plla.cancel_flag,'N')='N'

AND ((NVL(plla.quantity,0) -(NVL(plla.quantity_cancelled,0)+NVL(plla.quantity_received,0)))>0 OR ((plla.quantity-PLLA.QUANTITY_BILLED) >0))

)AND mcbkfv.category_id (+) = pol.category_id

AND pltl.language =USERENV('LANG')

AND pltl.line_type_id =pol.line_type_id

ORDER BY pol.po_header_id,

pol.po_line_id;