Oracle EBS - SQL Query to get the Purchase Requisition Number List which we need to Delegate to other user while employee is on leave

 SELECT

    segment1 pr,
    wf_item_key,
    wf_item_type,
    authorization_status
FROM
    apps.po_requisition_headers_all
WHERE
    authorization_status NOT IN ( 'APPROVED', 'CANCELLED' )
    AND requisition_header_id IN (
        SELECT
            object_id
        FROM
            apps.po_action_history pah, apps.per_all_people_f  ppf, apps.fnd_user          fu
        WHERE
                1 = 1
            AND pah.object_type_code = 'REQUISITION'
            AND pah.action_code IS NULL
            AND pah.employee_id = fu.employee_id
            AND nvl(fu.end_date, sysdate) >= sysdate
            AND fu.employee_id = ppf.person_id
            AND ppf.effective_end_date > sysdate
--and pah.employee_id=18581
            AND ppf.full_name LIKE '%Lien%Fung%' --user full name who is on leave
    );

Oracle EBS - SQL Query - Supplier Site associated Bank Details

Subject: Business need to extract the supplier site level associated bank details.

--SQL:

SELECT

    'Suppplier Site'    "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    iban,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv,

    apps.ap_supplier_sites_all   ass

WHERE

        1 = 1

    AND aps.vendor_id = ass.vendor_id

    AND ass.org_id = :p_org_id

    AND iepa.payee_party_id = aps.party_id

    AND iepa.party_site_id IS NOT NULL

    AND iepa.supplier_site_id IS NOT NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - SQL Query - Supplier Header associated Bank Details

Subject: Business required to get the supplier information where Supplier Header associated bank details.

--SQL:

 SELECT

    'Suppplier Header'  "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv

WHERE

        1 = 1

    AND aps.vendor_id IN (

        SELECT

            vendor_id

        FROM

            apps.ap_supplier_sites_all ass

        WHERE

            org_id = nvl(:p_org_id, org_id)

    )

    AND iepa.payee_party_id = aps.party_id

    AND party_site_id IS NULL

    AND supplier_site_id IS NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

    AND ieba.bank_account_num IS NOT NULL

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num