SELECT aps.vendor_name SUPPLIER_NAME
, aps.segment1 SUPPLIER_NUM
, aps.national_insurance_number NI_NUMBER
, aps.company_registration_number COMPANY_REG_NUM
, aps.allow_awt_flag SUPP_ALLOW_WHT
, aps.unique_tax_reference_num SUB_CONT_CERT_NUM
, aps.vendor_type_lookup_code TYPE
, haou.name ORG_NAME
, apss.vendor_site_code SITE_CODE
, apss.allow_awt_flag SUBCONTRACTOR
, apss.email_address EMAIL
, iep.remit_advice_email REMIT_EMAIL
, apss.attribute5 PAYEE_NAME
, apss.pay_site_flag PAY_SITE
, ieppm.payment_method_code PAY_METHOD
, apss.hold_all_payments_flag HOLD_PAYMT
, apss.attribute6 INVESTIGATE_FLAG
, hp.party_name BANK_NAME
, hop.organization_name BANK_BRANCH_NAME
, hop.bank_or_branch_number BRANCH_SORT_CODE
, ieba.masked_bank_account_num BANK_ACCT
, apss.payment_currency_code PAYMENT_CURR
, apss.purchasing_site_flag PURCH_SITE
, apss.invoice_currency_code INVOICE_CURR
, apss.allow_awt_flag SITE_ALLOW_WHT
, apss.pay_group_lookup_code PAY_GROUP
, apt.name PAYMENT_TERM
, apss.pay_date_basis_lookup_code PAY_DATE_BASIS
, hl_bill.location_code BILL_TO_ADDRESS
, hl_ship.location_code SHIP_TO_ADDRESS
, apss.supplier_notif_method NOTIF_METHOD
, zr.registration_status_code TAX_REG_STATUS
, zr.tax TAX
, zr.tax_regime_code TAX_REGIME_CODE
, zr.registration_number TAX_REG_NUMBER
, gcc.concatenated_segments PAY_ACCT
, gccp.concatenated_segments PREPAY_ACCT
FROM ap_suppliers aps
, ap_supplier_sites_all apss
, hr_all_organization_units haou
, ap_terms apt
, hr_locations hl_bill
, hr_locations hl_ship
, gl_code_combinations_kfv gcc
, gl_code_combinations_kfv gccp
, zx_party_tax_profile zptp
, zx_registrations zr
, iby_ext_bank_accounts ieba
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipiu
, iby_ext_party_pmt_mthds ieppm
, hz_parties hp
, hz_organization_profiles hop
WHERE 1 = 1
AND aps.enabled_flag = 'Y'
AND aps.vendor_type_lookup_code != 'EMPLOYEE'
AND aps.vendor_id = apss.vendor_id
AND apss.org_id = haou.organization_id
AND haou.date_from < SYSDATE
AND NVL(apss.inactive_date,SYSDATE + 1) > SYSDATE
AND apt.term_id(+) = apss.terms_id
AND apt.start_date_active(+) < SYSDATE
AND apss.bill_to_location_id = hl_bill.location_id(+)
AND apss.ship_to_location_id = hl_ship.location_id(+)
AND NVL(hl_bill.inactive_date(+),SYSDATE + 1) > SYSDATE
AND NVL(hl_ship.inactive_date(+),SYSDATE + 1) > SYSDATE
AND gcc.code_combination_id = apss.accts_pay_code_combination_id
AND gccp.code_combination_id = apss.prepay_code_combination_id
AND NVL(gcc.start_date_active,SYSDATE - 1) < SYSDATE
AND NVL(gccp.start_date_active,SYSDATE - 1) < SYSDATE
AND zptp.party_id(+) = apss.party_site_id
AND zptp.party_tax_profile_id = zr.party_tax_profile_id(+)
AND NVL(zr.effective_from(+),SYSDATE - 1) < SYSDATE
AND ipiu.instrument_id=ieba.ext_bank_account_id(+)
AND NVL(ieba.start_date(+),SYSDATE - 1) < SYSDATE
AND NVL(iep.inactive_date(+),SYSDATE + 1) > SYSDATE
AND ipiu.ext_pmt_party_id(+)=iep.ext_payee_id
AND ipiu.instrument_type(+)='BANKACCOUNT'
AND ipiu.payment_flow(+)='DISBURSEMENTS'
AND NVL(ipiu.start_date(+),SYSDATE - 1) < SYSDATE
AND ieppm.ext_pmt_party_id(+) = ipiu.ext_pmt_party_id
AND NVL(ieppm.inactive_date(+),SYSDATE + 1) > SYSDATE
AND apss.vendor_site_id = iep.supplier_site_id(+)
AND hp.party_type(+) = 'ORGANIZATION'
AND hop.party_id(+) = ieba.branch_id
AND hp.party_id(+) = ieba.bank_id
AND NVL(hop.effective_start_date(+),SYSDATE - 1) < SYSDATE
AND zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
ORDER BY 1,8,9