Query to find the suppliers in fusion:


SELECT DISTINCT NULL business_level1,
   NULL business_level2,
   NULL business_level3,
   NULL business_level4,
   c.bu_name buname,
   to_char(assa.vendor_site_id) vendor_site_id,
   to_char(aps.vendor_id) vendor_id,
   assa.attribute1 AS GSL,
   (SELECT hp2.party_name
        FROM hz_parties hp2
        WHERE hp2.party_id = aps.party_id) AS vendor_name,
(SELECT taddn.party_name
FROM hz_addtnl_party_names taddn
WHERE taddn.party_id = aps.party_id
AND taddn.party_name_type = 'PHONETIC' 
AND taddn.preferred_flag = 'Y') AS vendor_name_alt,
(SELECT pii.income_tax_id 
FROM poz_suppliers_pii pii
WHERE  pii.vendor_id = aps.vendor_id) AS tax_identification_number,
aps.organization_type_lookup_code tax_classification,
hl.country country,
    hl.address1 address_line_1,
    hl.address2 address_line_2,
    hl.address3 address_line_3,
    hl.address4 address_line_4,
    hl.city city,
    hl.county county,
    hl.state state_province_region,
    hl.postal_code postal_code,
to_char(assa.prc_bu_id) ORG_ID,
aps.attribute1 GSL6,
assa.vendor_site_code vendor_site_code,
    iepm.payment_method_code payment_method_code,
iepm.payment_method_code remittance_type,
iepa.remit_advice_fax remit_advice_fax,
    iepa.remit_advice_email remit_advice_email,
(select tt.name
          from ap_terms_tl tt
         where tt.term_id = assa.terms_id
           and tt.language = 'US') as PAYMENT_TERMS,
     (select tt.description
          from ap_terms_tl tt
         where tt.term_id = assa.terms_id
           and tt.language = 'US') as PAYMENT_TERM_DESC,
    assa.SUPPLIER_NOTIF_METHOD,
assa.EMAIL_ADDRESS,
    assa.FOB_LOOKUP_CODE INCOTERM,
    assa.purchasing_site_flag,
    assa.pay_site_flag,
    ipiua.primary_flag primary_bank_flag,
    ieba.bank_account_name,
    ieba.bank_account_num,
    ieba.currency_code BANK_ACCOUNT_CURRENCY,
b.bank_name,
    br.bank_branch_name,
    br.branch_number,
b.HOME_COUNTRY country1,
br.eft_swift_code swift_code,
ieba.iban,
to_char(pscv.vendor_contact_id) vendor_contact_id,
pscv.first_name,
    pscv.last_name,
    pscv.email_address CONTACT_EMAIL_ADDRESS,
    pscv.phone CONTACT_PHONE,
b.BANK_NAME_ALT,
ieba.BANK_ACCOUNT_NAME_ALT,
iepm.primary_flag primary_payment_method_flag,
assa.pay_group_lookup_code,
aps.last_update_date SUPP_LAST_UPDATE_DATE,
assa.last_update_date SITE_LAST_UPDATE_DATE,
ieba.last_update_date BANK_LAST_UPDATE_DATE,
pscv.last_update_date CONTACT_LAST_UPDATE_DATE,
aps.segment1 SUPPLIER_NUMBER
FROM
    poz_supplier_sites_all_m assa,
    hz_parties hp,
    iby_ext_bank_accounts ieba,
    iby_external_payees_all iepa,
    iby_pmt_instr_uses_all ipiua,
    poz_suppliers aps,
    hz_parties hp1,
    fun_all_business_units_v c,
    ce_banks_v b,
    ce_bank_branches_v br,
hz_locations hl,
poz_supplier_contacts_v pscv,
iby_ext_party_pmt_mthds iepm
WHERE assa.vendor_site_id = iepa.supplier_site_id
    AND assa.location_id = hl.location_id
    AND hp.party_id = ieba.bank_id
    AND ipiua.instrument_id = ieba.ext_bank_account_id
    AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND iepa.ext_payee_id = iepm.ext_pmt_party_id(+) --Added
    AND assa.vendor_id = aps.vendor_id
AND aps.vendor_id = pscv.vendor_id(+)
    AND ieba.branch_id = hp1.party_id
    AND assa.prc_bu_id = c.bu_id
     AND b.bank_party_id = ieba.bank_id
    AND br.bank_party_id = b.bank_party_id
    AND br.branch_party_id = ieba.branch_id
AND aps.end_date_active IS NULL
AND assa.inactive_date IS NULL
   and aps.segment1 in('XXXX')
--AND iepm.primary_flag = 'Y' --primary_payment_method_flag
--AND ipiua.primary_flag = 'Y' --primary_bank_flag

ORDER BY c.bu_name