Oracle Fusion - SQL Query to get User Need to Approve pending approval Purchase Requestion and Purchase Order Transaction Details

 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)

    )                           email

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)

    )                         email

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