WITH REQ_5000
AS ( SELECT prha.requisition_header_id,
prha.segment1,prha.org_id,
SUM (
NVL (prda.req_line_quantity, 0) * NVL (prla.unit_price, 0))
amount,
FROM po_requisition_lines_all prla,
po_req_distributions_all prda,
po_requisition_headers_all prha
WHERE prha.requisition_header_id = prla.requisition_header_id
AND NVL (prla.cancel_flag, 'N') <> 'Y'
AND prla.requisition_line_id = prda.requisition_line_id
AND prha.AUTHORIZATION_STATUS = 'APPROVED'
-- AND prla.requisition_header_id = :1
GROUP BY prha.requisition_header_id, prha.segment1, prha.org_id
HAVING SUM (
NVL (prda.req_line_quantity, 0)
* NVL (prla.unit_price, 0)) > 5000),
MAX_APPROVER
AS ( SELECT palh.document_id,
palh.approval_list_header_id,
MAX (approval_list_line_id) approval_list_line_id
FROM po_approval_list_headers palh, po_approval_list_lines pall
WHERE 1 = 1
-- AND palh.document_id = :1
AND palh.approval_list_header_id =
pall.approval_list_header_id
GROUP BY palh.document_id, palh.approval_list_header_id)
SELECT req_5000.requisition_header_id,
req_5000.segment1,req_5000.org_id,
req_5000.amount,
pall.approver_id,
ppf.FULL_NAME
FROM req_5000,
max_approver,
po_approval_list_lines pall,
per_people_f ppf
WHERE 1 = 1
-- AND req_5000.requisition_header_id = :1
AND req_5000.requisition_header_id = max_approver.document_id
AND pall.approval_list_line_id = max_approver.approval_list_line_id
AND ppf.person_id = pall.approver_id
AND ppf.person_id IN (select distinct person_id
from per_all_assignments_f paaf, po_position_controls_all ppca , PO_CONTROL_GROUPS_all pcga
where 1=1
AND paaf.job_id = ppca.job_id
AND ppca.control_group_id = pcga.control_group_id
AND PCGA.control_group_name = 'Site Controller')
AND ppf.person_id NOT IN (5262,20517,55021,55104,55296,55897,78271,78874,79790,81023,102046,106006,111306,114966,116526,137401,143367,143388,144166,149171,150407,158046,184591,185551,220756,222272,222760,223933,224233,235338,239709,240968)
-- List of all XX_FINANCE_APPROVERS
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
--GROUP BY pall.approver_id, ppf.FULL_NAME
No comments:
Post a Comment