Monday, December 5, 2022

Oracle Cloud Fusion: Supplier Bank Accounts Query

SELECT (SELECT NAME
        FROM   hr_operating_units
        WHERE  organization_id = pssm.prc_bu_id) BU_name,
   ps.segment1 supplier_number,
       hzp.party_name supplier_name,
       pssm.vendor_site_code supplier_site,
       bank.party_name bank_name,
       branch.party_name branch_name,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ieb.IBAN
FROM   poz_suppliers ps,
       poz_supplier_sites_all_m pssm,
       iby_external_payees_all payee,
       iby_pmt_instr_uses_all uses,
       iby_ext_bank_accounts ieb,
       hz_parties bank,
       hz_parties branch,
       hz_parties HZP
WHERE  ps.vendor_id = pssm.vendor_id
       AND ps.party_id = payee.payee_party_id
       AND payee.supplier_site_id = pssm.vendor_site_id
       AND uses.instrument_type = 'BANKACCOUNT'
       AND payee.ext_payee_id = uses.ext_pmt_party_id
       AND uses.payment_function = 'PAYABLES_DISB'
       AND uses.instrument_id = ieb.ext_bank_account_id
       AND ieb.bank_id = bank.party_id(+)
       AND ieb.branch_id = branch.party_id(+)
       AND hzp.party_id = ps.party_id
       AND SYSDATE BETWEEN Nvl(uses.start_date, SYSDATE) AND
                           Nvl(uses.end_date, SYSDATE)
       AND SYSDATE BETWEEN Nvl(ieb.start_date, SYSDATE) AND
                           Nvl(ieb.end_date, SYSDATE)
       AND Nvl(ps.end_date_active, SYSDATE + 1) > Trunc (SYSDATE)
       AND Nvl(pssm.inactive_date, SYSDATE + 1) > Trunc (SYSDATE) 

No comments:

Post a Comment