Oracle EBS – Query to get Purchase Order Details approved based on Hierarchy

Oracle EBS – Query to get Purchase Order Details approved based on Hierarchy

Select * from
(
SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) rp_run_date, po.*
FROM (SELECT ou, supplier_number, supplier_name, po_number,
po_creation_date, po_approved_date,
SUM (line_amount) po_amount, NULL line_description,
NULL line_amount, po_header_id, NULL po_line_id,
preparer requestor_name, sequence_num,
action_code || row_num action_code, row_num, action_person,
currency_code, rate
FROM (SELECT hou.NAME ou, pv.segment1 supplier_number,
pv.vendor_name supplier_name, prh.segment1 po_number,
TO_DATE (prh.creation_date,
‘DD-MON-RRRR’
) po_creation_date,
TO_DATE (prh.approved_date,
‘DD-MON-RRRR’
) po_approved_date,
NULL line_description,
(SELECT SUM ( NVL (prl.quantity, 0)
* NVL (prl.unit_price, 0)
)
FROM po_lines_all prl
WHERE prl.po_header_id = prh.po_header_id
AND prl.item_id IS NULL
AND NVL (prl.cancel_flag, ‘N’) = ‘N’)
line_amount,
pah.sequence_num,
DECODE (pah.action_code,
‘APPROVE’, ‘APPROVER’,
‘REJECT’, ‘REJECTOR’
) action_code,
ROW_NUMBER () OVER (PARTITION BY pah.object_id ORDER BY pah.sequence_num)
AS row_num,
papf.full_name action_person,
prh.po_header_id po_header_id,
papf1.full_name preparer, prh.currency_code,
prh.rate
FROM po_headers_all prh,
hr_operating_units hou,
po_action_history pah,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf,
po_vendors pv,
po_vendor_sites_all pvs,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf1
WHERE prh.org_id = hou.organization_id
AND prh.po_header_id = pah.object_id
AND papf.person_id = pah.employee_id
AND prh.agent_id = papf1.person_id(+)
AND pah.object_type_code = ‘PO’
AND prh.vendor_id = pv.vendor_id
AND prh.vendor_site_id = pvs.vendor_site_id
AND NVL (prh.cancel_flag, ‘N’) = ‘N’
AND :p_type = ‘PO’
AND :p_level = ‘Summary’
AND pv.vendor_id = NVL (:p_vendor_id, pv.vendor_id)
AND prh.approved_date BETWEEN :p_from_date AND :p_to_date
AND prh.org_id = :p_org_id
AND EXISTS (
SELECT NULL
FROM po_lines_all pla
WHERE pla.po_header_id = prh.po_header_id
AND pla.item_id IS NULL
AND NVL (cancel_flag, ‘N’) = ‘N’)
AND prh.org_id IN (81, 82) — Only for US and CA OU
AND prh.authorization_status = ‘APPROVED’
AND pah.action_code IN (‘APPROVE’, ‘REJECT’))
GROUP BY ou,
supplier_number,
supplier_name,
po_number,
po_creation_date,
po_approved_date,
po_header_id,
preparer,
sequence_num,
action_code || row_num,
row_num,
action_person,
currency_code,
rate) po)
Where (po_amount)>=nvl(:p_po_total,po_amount)
UNION
SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) rp_run_date, pol.*
FROM (SELECT DISTINCT ou, supplier_number, supplier_name, po_number,
po_creation_date, po_approved_date,
line_amount po_amount,
pla.item_description line_description,
NVL (pla.quantity, 0)
* NVL (pla.unit_price, 0) po_line_amount,
po.po_header_id, pla.po_line_id,
preparer requestor_name, sequence_num,
action_code || row_num action_code, row_num,
action_person, currency_code, rate
FROM (SELECT hou.NAME ou, pv.segment1 supplier_number,
pv.vendor_name supplier_name,
prh.segment1 po_number,
TO_DATE (prh.creation_date,
‘DD-MON-RRRR’
) po_creation_date,
TO_DATE (prh.approved_date,
‘DD-MON-RRRR’
) po_approved_date,
NULL line_description,
(SELECT SUM ( NVL (prl.quantity, 0)
* NVL (prl.unit_price, 0)
)
FROM po_lines_all prl
WHERE prl.po_header_id = prh.po_header_id
AND prl.item_id IS NULL
AND NVL (prl.cancel_flag, ‘N’) = ‘N’)
line_amount,
pah.sequence_num,
DECODE (pah.action_code,
‘APPROVE’, ‘APPROVER’,
‘REJECT’, ‘REJECTOR’
) action_code,
ROW_NUMBER () OVER (PARTITION BY pah.object_id ORDER BY pah.sequence_num)
AS row_num,
papf.full_name action_person,
prh.po_header_id po_header_id,
papf1.full_name preparer, prh.currency_code,
prh.rate
FROM po_headers_all prh,
hr_operating_units hou,
po_action_history pah,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf,
po_vendors pv,
po_vendor_sites_all pvs,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf1
WHERE prh.org_id = hou.organization_id
AND prh.po_header_id = pah.object_id
AND papf.person_id = pah.employee_id
AND prh.agent_id = papf1.person_id(+)
AND pah.object_type_code = ‘PO’
AND prh.vendor_id = pv.vendor_id
AND prh.vendor_site_id = pvs.vendor_site_id
AND NVL (prh.cancel_flag, ‘N’) = ‘N’
AND :p_type = ‘PO’
AND :p_level = ‘Detail’
AND pv.vendor_id =
NVL (:p_vendor_id, pv.vendor_id)
AND prh.approved_date BETWEEN :p_from_date
AND :p_to_date
AND prh.org_id = :p_org_id
AND prh.org_id IN
(81, 82) — Only for US and CA OU
AND prh.authorization_status = ‘APPROVED’
AND pah.action_code IN (‘APPROVE’, ‘REJECT’)) po,
po_lines_all pla
WHERE po.po_header_id = pla.po_header_id
AND pla.item_id IS NULL
AND NVL (pla.cancel_flag, ‘N’) = ‘N’) pol
Where (po_amount)>=nvl(:p_po_total,po_amount)
Union
SELECT DISTINCT TO_CHAR(SYSDATE,’DD-MON-YYYY HH:MI:SS’) RP_RUN_DATE, ou, pv.segment1 supplier_number,
pv.vendor_name supplier_name, po_number,
po_creation_date, po_approved_date,
NVL(line_amount,0) po_amount,
NULL line_description,
NULL po_line_amount,
po.requisition_header_id PO_Header_id,
NULL PO_Line_id,
preparer requestor_name, sequence_num,
action_code || row_num action_code, row_num,
action_person, currency_code, rate
FROM (SELECT hou.NAME ou,
prh.segment1 po_number,
TO_DATE (prh.creation_date,
‘DD-MON-RRRR’
) po_creation_date,
TO_DATE (prh.approved_date,
‘DD-MON-RRRR’
) po_approved_date,
NULL line_description,
(SELECT SUM ( NVL (prl.quantity, 0)
* NVL (prl.unit_price, 0)
)
FROM po_Requisition_lines_all prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.item_id IS NULL
AND NVL (prl.cancel_flag, ‘N’) = ‘N’)
line_amount,
pah.sequence_num,
DECODE (pah.action_code,
‘APPROVE’, ‘APPROVER’,
‘REJECT’, ‘REJECTOR’
) action_code,
ROW_NUMBER () OVER (PARTITION BY pah.object_id ORDER BY pah.sequence_num)
AS row_num,
papf.full_name action_person,
prh.requisition_header_id ,
papf1.full_name preparer
FROM po_requisition_headers_all prh,
hr_operating_units hou,
po_action_history pah,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf1
WHERE prh.org_id = hou.organization_id
AND prh.requisition_header_id = pah.object_id
AND papf.person_id = pah.employee_id
AND prh.preparer_id = papf1.person_id(+)
AND pah.object_type_code = ‘REQUISITION’
AND NVL (prh.cancel_flag, ‘N’) = ‘N’
AND :p_type = ‘REQUISITION’
AND :p_level = ‘Summary’
AND prh.approved_date BETWEEN :p_from_date
AND :p_to_date
AND prh.org_id = :p_org_id
AND prh.org_id IN
(81, 82) — Only for US and CA OU
AND prh.authorization_status = ‘APPROVED’
AND pah.action_code IN (‘APPROVE’, ‘REJECT’)) po,
po_requisition_lines_all prl,po_vendors pv,po_vendor_Sites_All pvs
WHERE po.requisition_header_id = prl.requisition_header_id
AND prl.item_id IS NULL
AND prl.vendor_id = pv.vendor_id
AND prl.vendor_site_id = pvs.vendor_site_id
AND pv.vendor_id =
NVL (:p_vendor_id, pv.vendor_id)
AND NVL (prl.cancel_flag, ‘N’) = ‘N’
and (line_amount)>=nvl(:p_po_total,line_amount)
Union
SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) rp_run_date, pol.*
FROM (
SELECT DISTINCT ou, pv.segment1 supplier_number,
pv.vendor_name supplier_name, po_number,
po_creation_date, po_approved_date,
line_amount po_amount,
prl.item_description line_description,
NVL (prl.quantity, 0)
* NVL (prl.unit_price, 0) po_line_amount,
po.requisition_header_id PO_Header_id,
prl.requisition_line_id PO_LINE_ID,
preparer requestor_name, sequence_num,
action_code || row_num action_code, row_num,
action_person, currency_code, rate
FROM (SELECT hou.NAME ou,
prh.segment1 po_number,
TO_DATE (prh.creation_date,
‘DD-MON-RRRR’
) po_creation_date,
TO_DATE (prh.approved_date,
‘DD-MON-RRRR’
) po_approved_date,
NULL line_description,
(SELECT SUM ( NVL (prl.quantity, 0)
* NVL (prl.unit_price, 0)
)
FROM po_Requisition_lines_all prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.item_id IS NULL
AND NVL (prl.cancel_flag, ‘N’) = ‘N’)
line_amount,
pah.sequence_num,
DECODE (pah.action_code,
‘APPROVE’, ‘APPROVER’,
‘REJECT’, ‘REJECTOR’
) action_code,
ROW_NUMBER () OVER (PARTITION BY pah.object_id ORDER BY pah.sequence_num)
AS row_num,
papf.full_name action_person,
prh.requisition_header_id ,
papf1.full_name preparer
FROM po_requisition_headers_all prh,
hr_operating_units hou,
po_action_history pah,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf,
(SELECT DISTINCT person_id, full_name
FROM per_all_people_f) papf1
WHERE prh.org_id = hou.organization_id
AND prh.requisition_header_id = pah.object_id
AND papf.person_id = pah.employee_id
AND prh.preparer_id = papf1.person_id(+)
AND pah.object_type_code = ‘REQUISITION’
AND NVL (prh.cancel_flag, ‘N’) = ‘N’
AND :p_type = ‘REQUISITION’
AND :p_level = ‘Detail’
AND prh.approved_date BETWEEN :p_from_date
AND :p_to_date
AND prh.org_id = :p_org_id
AND prh.org_id IN
(81, 82) — Only for US and CA OU
AND prh.authorization_status = ‘APPROVED’
AND pah.action_code IN (‘APPROVE’, ‘REJECT’)) po,
po_requisition_lines_all prl,po_vendors pv,po_vendor_Sites_All pvs
WHERE po.requisition_header_id = prl.requisition_header_id
AND prl.item_id IS NULL
AND prl.vendor_id = pv.vendor_id
AND prl.vendor_site_id = pvs.vendor_site_id
AND pv.vendor_id =
NVL (:p_vendor_id, pv.vendor_id)
AND NVL (prl.cancel_flag, ‘N’) = ‘N’
) pol
where (PO_amount)>=nvl(:p_po_total,PO_amount)
AND PO_Amount0
Order By Supplier_Number,Po_Number