This query will be used to get open requisitions that require conversion to Purchase Orders in Oracle Apps R12. The Complete Purchase Requisitions made in Oracle Apps can be extracted using this query. This SQL query will return both Internal and External types of Requisitions. We will receive detailed requisition information, including Requester Information, Requisition Item Information, Price, and Quantity. This Requisition query retrieves Oracle Purchase requisitions from the backend. This Detail Requisition Query in Oracle Apps R12 with Important Oracle Apps Requisition Tables is shared below.
Query to get open requisitions that require conversion to Purchase Orders in Oracle Apps R12
SELECT prha.segment1 req_num
, TRIM(TO_CHAR((prla.quantity * prla.unit_price), ‘999,999,999.99’)) line_value
, prla.line_num
, prha.creation_date
, prla.creation_date line_creation_date
, prla.vendor_id
, prla.vendor_site_id
, NVL2(
(SELECT DISTINCT pha2.vendor_id
FROM po.po_headers_all pha2
WHERE pha2.type_lookup_code = ‘CONTRACT’
AND pha2.authorization_status = ‘APPROVED’
AND pha2.creation_date >= ’01-APR-2009′
AND pha2.vendor_id = prla.vendor_id
AND pha2.vendor_site_id = prla.vendor_site_id
AND pha.org_id = prha.org_id)
, ‘YES’
, ”
) cpa_exists
, (SELECT MIN(pah.creation_date)
FROM po.po_action_history pah
WHERE object_id = prha.requisition_header_id
AND pah.object_type_code = ‘REQUISITION’
AND pah.action_code = ‘SUBMIT’) submitted_for_approval
, (SELECT MAX(pah.creation_date)
FROM po.po_action_history pah
WHERE object_id = prha.requisition_header_id
AND pah.object_type_code = ‘REQUISITION’
AND pah.action_code = ‘APPROVE’) last_approved
, mcb.segment1 || ‘.’ || mcb.segment2 purchase_category
, pct.NAME commodity
, CASE
WHEN pct.NAME IS NOT NULL
THEN (SELECT papf2.full_name
FROM po.po_commodities_tl pct
, po.po_commodity_categories pcc
, po.po_commodity_grants pcg
, hr.per_all_people_f papf2
WHERE pct.commodity_id = pcg.commodity_id
AND pcc.category_id(+) = mcb.category_id
AND pct.commodity_id(+) = pcc.commodity_id
AND papf2.person_id = pcg.person_id
AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date)
END commodity_buyer
, CASE
— ———————————————– PUNCHOUT
WHEN prla.catalog_type = ‘EXTERNAL’
AND prla.catalog_source = ‘EXTERNAL’
AND prla.source_type_code = ‘VENDOR’
THEN ‘PUNCHOUT’
— ———————————————–INTERNAL CATALOGUE
WHEN prla.catalog_type = ‘CATALOG’
AND prla.catalog_source = ‘INTERNAL’
AND prla.source_type_code = ‘VENDOR’
AND prla.item_id IS NULL
THEN ‘LOCAL_CATALOGUE’
— ———————————————– NON CATALOGUE:
WHEN prla.catalog_type = ‘NONCATALOG’
AND prla.catalog_source = ‘INTERNAL’
AND prla.source_type_code = ‘VENDOR’
THEN ‘NONCAT’
ELSE ‘Other’
END order_type
, prla.suggested_vendor_product_code catalogue_code
, prha.emergency_po_num
, TO_CHAR(prla.need_by_date, ‘DD-MON-RRRR’) need_by_date
, prla.item_description
, hlat.description deliver_to
, prla.quantity
, prla.unit_price
, prla.unit_meas_lookup_code uom
, prla.note_to_agent note_to_buyer
, prla.note_to_vendor note_to_supplier
, papf.full_name req_prepaper
, papf.email_address req_email
, SUBSTR(haout.NAME, 0, 2) service
, haout.NAME hr_org
, prla.suggested_vendor_name supplier
, prla.suggested_vendor_location site
, prha.description req_description
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, po.po_line_locations_all plla
, po.po_lines_all pla
, po.po_headers_all pha
, hr.per_all_people_f papf
, hr.per_all_assignments_f paaf
, hr.hr_all_organization_units_tl haout
, hr.hr_locations_all_tl hlat
, inv.mtl_categories_b mcb
, po.po_commodities_tl pct
, po.po_commodity_categories pcc
WHERE prha.requisition_header_id = prla.requisition_header_id
AND plla.line_location_id(+) = prla.line_location_id
AND pla.po_line_id(+) = plla.po_line_id
AND prha.preparer_id = papf.person_id
AND pha.po_header_id(+) = plla.po_header_id
AND prla.deliver_to_location_id = hlat.location_id
AND haout.organization_id = paaf.organization_id
AND paaf.person_id = papf.person_id
AND mcb.category_id = prla.category_id
AND pcc.category_id(+) = mcb.category_id
AND pct.commodity_id(+) = pcc.commodity_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND (prla.cancel_flag IS NULL OR prla.cancel_flag = ‘N’)
AND prha.authorization_status = ‘APPROVED’
AND prha.creation_date >= ’01-JAN-2012′
AND paaf.assignment_type = ‘E’
AND paaf.primary_flag = ‘Y’
AND prla.closed_date IS NULL
AND pha.segment1 IS NULL
ORDER BY prha.segment1 DESC
, prla.line_num;