SELECT
'Requisition' type,
prha.document_status approval_status,
to_char(prha.creation_date, 'DD-MM-YYYY') po_date,
prha.requisition_number requisition_number,
replace(replace(prha.description,
CHR(13),
''),
CHR(10),
'') pr_po_desc,
(
SELECT
SUM((nvl(unit_price, amount) * nvl(quantity, 1)))
FROM
por_requisition_lines_all
WHERE
requisition_header_id = prha.requisition_header_id
) pr_po_amount,
(
SELECT
prla.currency_code
FROM
por_requisition_lines_all prla
WHERE
prla.requisition_header_id = prha.requisition_header_id
AND ROWNUM = 1
) currency,
prha.requisition_header_id,
NULL supplier_name,
wf.assigneesdisplayname pending_with_whom,
to_char(wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,
email.*,
wf.identificationkey,
wf.PROCESSNAME,
wf.STATE
FROM
po_action_history pah,
por_requisition_headers_all prha,
fa_fusion_soainfra.wftask wf,
-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,
hr_operating_units d,
(
SELECT
panf.full_name,
fu.username,
pea.email_address
FROM
per_users fu,
per_all_people_f papf,
per_person_names_f panf,
per_email_addresses pea
WHERE
fu.person_id = papf.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND panf.person_id = papf.person_id
AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date
AND panf.name_type = 'GLOBAL'
AND pea.person_id = papf.person_id
AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)
WHERE
pah.object_id = prha.requisition_header_id
AND prha.req_bu_id = d.organization_id
AND pah.object_type_code = 'REQ'
AND prha.document_status IN ( 'PENDING APPROVAL' )
AND wf.compositeinstanceid = prha.approval_instance_id
AND wf.assignees IS NOT NULL
AND wf.tasknumber = (
SELECT
MAX(tasknumber)
FROM
fa_fusion_soainfra.wftask wf1
WHERE
wf.correlationid = wf1.correlationid -- and wf1.state = 'ASSIGNED'
AND wf1.compositeinstanceid = prha.approval_instance_id
)
-- AND WASSS_V.taskid(+) = wf.taskid
AND email.username (+) = upper(substr(wf.assignees,
1,
instr(wf.assignees, ',') - 1)) ----WASSS_V.ASSIGNEE
AND pah.sequence_num = (
SELECT
MAX(pah1.sequence_num)
FROM
po_action_history pah1
WHERE
pah1.object_id = pah.object_id -----rEQ---
AND pah1.object_type_code = 'REQ'
AND prha.document_status IN ( 'PENDING APPROVAL' ) -- AND pah1.action_code = nvl(:p_status,pah1.action_code)
)
---Parameters
-- AND prha.requisition_header_id = nvl(:por_number, prha.requisition_header_id)
-- AND decode(:p_bu, NULL, 1, d.organization_id) = nvl(:p_bu, 1)
-- AND NVL (:PO_Number,1) = 1
UNION ALL
SELECT
'Purchase Order',
pv.change_order_status approval_status,
to_char(ph.creation_date, 'DD-MM-YYYY') po_date,
--------------
ph.segment1 po_number,
replace(replace(ph.comments,
CHR(13),
''),
CHR(10),
'') pr_po_desc,
(
SELECT
SUM(nvl(pl1.unit_price, pl1.amount) * decode(pl1.purchase_basis, 'SERVICES', 1, pl1.quantity))
FROM
po_lines_all pl1
WHERE
pl1.po_header_id = ph.po_header_id
) pr_po_amount,
ph.currency_code currency,
ph.po_header_id,
pv.vendor_name supplier_name,
wf.assigneesdisplayname pending_with_whom,
to_char(wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,
email.*,
wf.identificationkey,
wf.PROCESSNAME,
wf.STATE
FROM
po_action_history poh,
fa_fusion_soainfra.wftask wf,
-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,
po_headers_all ph,
po_versions pv,
poz_suppliers_v pv,
hr_operating_units d,
(
SELECT
panf.full_name,
fu.username,
pea.email_address
FROM
per_users fu,
per_all_people_f papf,
per_person_names_f panf,
per_email_addresses pea
WHERE
fu.person_id = papf.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND panf.person_id = papf.person_id
AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date
AND panf.name_type = 'GLOBAL'
AND pea.person_id = papf.person_id
AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)
WHERE
poh.correlation_id = wf.correlationid
AND poh.object_id = ph.po_header_id
AND ph.po_header_id = pv.po_header_id
AND wf.compositeinstanceid = pv.approval_instance_id
--AND wf.state = 'ASSIGNED'
AND assignees IS NOT NULL
-- AND ph.segment1 = 'ISG0000048'
AND poh.object_type_code = 'PO'
-- AND poh.ACTION_CODE = 'SUBMIT'
-- AND ph.DOCUMENT_STATUS IN ('PENDING APPROVAL')
-- ('WITHDRAWN', 'REJECTED', 'PENDING APPROVAL')
AND pv.change_order_status = 'PENDING APPROVAL'
AND ph.vendor_id = pv.vendor_id
AND ph.prc_bu_id = d.organization_id
AND wf.tasknumber = (
SELECT
MAX(tasknumber)
FROM
fa_fusion_soainfra.wftask wf1
WHERE
wf.correlationid = wf1.correlationid --
AND wf1.state = 'ASSIGNED'
AND wf1.compositeinstanceid = pv.approval_instance_id
)
-- AND WASSS_V.taskid(+) = wf.taskid
AND email.username (+) = upper(substr(wf.assignees,
1,
instr(wf.assignees, ',') - 1))
AND poh.sequence_num = (
SELECT
max ( pah1.sequence_num ) FROM po_action_history pah1 WHERE pah1.object_id = poh.object_id -----Po_header_id
AND pah1.object_type_code = 'PO' -- AND pah1.action_code = nvl(:p_status,pah1.action_code)
)
---Parameters
-- AND ph.po_header_id = nvl(:po_number, ph.po_header_id)
-- AND decode(:p_bu, NULL, 1, ph.prc_bu_id) = nvl(:p_bu, 1)
-- AND NVL (:POR_Number,1) =1
No comments:
Post a Comment