Blanket purchase order query in oracle apps r12.Blanket purchase order query in oracle apps r12. po_releases_all table in r12

Blanket purchase order query in oracle apps r12. po_releases_all table in r12

po_releases_all table in r12

po_releases_all

 

This table provide Purchase order Releases. Here We will get the Po_header_id , PO Release Number , Agent Id, Release Date , PO release Apprvoe Date,

AUTHORIZATION_STATUS,RELEASE_TYPE

blanket release in oracle apps

 

SELECT poh.segment1 po_number

     , poh.type_lookup_code

     , pr.release_num

     , poh.creation_date

     , pv.vendor_name supplier

     , pvs.vendor_site_code supplier_site_code

     , hl.location_code ship_to_location_code

     , pb.agent_name buyer_name

     , msi.segment1 item_number

     , msi.description item_desc

     , msi.inventory_item_status_code item_status

     , pll.quantity

     , pll.quantity_received

     , pll.quantity_cancelled

     , pll.quantity_billed

     , pol.unit_price

     , mp.organization_code receiving_org_code

     , (SELECT mc.concatenated_segments

        FROM mtl_categories_kfv mc, mtl_item_categories mic, mtl_category_sets mcs

        WHERE mcs.category_set_name = 'PURCHASING'

          AND mcs.category_set_id = mic.category_set_id

          AND mic.inventory_item_id = msi.inventory_item_id

          AND mic.organization_id = msi.organization_id

          AND mic.category_id = mc.category_id)

          po_category

FROM po_headers_all poh

   , po_lines_all pol

   , po_line_locations_all pll

   , po_releases_all pr

   , mtl_system_items msi

   , org_organization_definitions mp

   , po_vendors pv

   , po_vendor_sites_all pvs

   , po_agents_v pb

   , hr_locations hl

WHERE poh.type_lookup_code IN ('BLANKET')

  AND msi.inventory_item_id = pol.item_id

  AND msi.organization_id = pll.ship_to_organization_id

  AND mp.organization_id = msi.organization_id

  AND poh.po_header_id = pol.po_header_id

  AND pol.po_line_id = pll.po_line_id

  AND pr.po_header_id(+) = poh.po_header_id

  AND NVL (pll.po_release_id, 1) = NVL (pr.po_release_id, 1)

  AND poh.vendor_id = pv.vendor_id

  AND poh.vendor_site_id = pvs.vendor_site_id

  AND pvs.vendor_id = pv.vendor_id

  AND pb.agent_id = poh.agent_id

  AND hl.location_id = poh.ship_to_location_id

  AND poh.org_id = :p_org_id

ORDER BY poh.segment1, pr.release_num