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
