Supplier bank account query in fusion

 Supplier bank account query in fusion

 This SQL query will help to extract the complete Supplier Banking information's in oracle fusion. 

 Top 10 Important Tables used by Supplier bank account query in fusion

1.POZ_SUPPLIERS_V

2.POZ_SUPPLIER_SITES_V

3.IBY_EXTERNAL_PAYEES_ALL

4.IBY_PMT_INSTR_USES_ALL

5.IBY_EXT_BANK_ACCOUNTS

6.CE_BANK_BRANCHES_V

7.IBY_EXTERNAL_PAYEES_ALL

8.POZ_SUPPLIER_ADDRESS_V

9.POZ_SITE_ASSIGNMENTS_ALL_M

10.IBY_EXT_PARTY_PMT_MTHDS

Detail Supplier bank account query in fusion

SELECT PSV.VENDOR_NAME ,

  PSV.SEGMENT1 ,

  PSV.VENDOR_TYPE_LOOKUP_CODE ,

  PSV.ORGANIZATION_TYPE_LOOKUP_CODE " Organization Type" ,

  TAX_PRF.COUNTRY_CODE "TAXPAYER_COUNTRY",

  PSP.INCOME_TAX_ID "TAXPAYER_ID",

  PSV.FEDERAL_REPORTABLE_FLAG,

  PSV.TYPE_1099 "FEDERAL_INCOME_TAX_TYPE",

  PSV.STATE_REPORTABLE_FLAG STATE_REPORTABLE ,

  TAX_PRF.TAX_CLASSIFICATION_CODE "TAX_CLASSIFICATION_CODE",

  ZXR.TAX_REGIME_CODE "TAX_REGIME_CODE",

  ZXR.TAX "TAX",

  TAX_PRF.REP_REGISTRATION_NUMBER "REGISTRATION_NUMBER",

  ZXR.TAX_JURISDICTION_CODE "TAX_JURISDICTION_CODE",

  PSV.TAX_REPORTING_NAME,

  PSAV.PARTY_SITE_NAME "Supplier Address Name",

  PSAV.ADDRESS1 ,

  PSAV.ADDRESS2 ,

  PSAV.ADDRESS3 ,

  PSAV.ADDRESS4 ,

  PSAV.CITY ,

  PSAV.STATE ,

  PSAV.COUNTY ,

  PSAV.POSTAL_CODE ,

  PSAV.COUNTRY ,

  PSAV.EMAIL_ADDRESS ,

  PSSV.VENDOR_SITE_CODE ,

  TERMS.NAME "PAYMENT_TERMS",

  PSSV.INVOICE_CURRENCY_CODE ,

  PSSV.PAYMENT_CURRENCY_CODE ,

  PSSV.TAX_REPORTING_SITE_FLAG ,

  HP.PARTY_NAME BANK_PARTY_NAME,

  IEBA.COUNTRY_CODE BANK_COUNTRY,

  IEBA.FOREIGN_PAYMENT_USE_FLAG ALLOW_INT,

 IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,

  IEBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

  IEBA.CURRENCY_CODE BANK_ACCT_CURRENCY_CODE,

  IEBA.IBAN ,

  EBB.BANK_BRANCH_NAME ,

  EBB.BRANCH_NUMBER 

FROM POZ_SUPPLIERS_V PSV ,

  POZ_SUPPLIERS_PII PSP,

  ZX_PARTY_TAX_PROFILE TAX_PRF,

  FND_LOOKUP_VALUES_TL REGT,

  HZ_CODE_ASSIGNMENTS HCA,

  FND_LOOKUP_VALUES_TL HCAVAL,

  ZX_REGISTRATIONS ZXR,

  POZ_SUPPLIER_SITES_V PSSV,

  POZ_ALL_SUPPLIER_CONTACTS_V PSC,

  HZ_RELATIONSHIPS hr,

  HZ_ORG_CONTACTS hoc,

  IBY_EXTERNAL_PAYEES_ALL IEPA,

  IBY_EXT_PARTY_PMT_MTHDS IEPPM,

  IBY_PMT_INSTR_USES_ALL IPIUA ,

  IBY_EXT_BANK_ACCOUNTS IEBA,

  CE_BANK_BRANCHES_V EBB,

  HZ_PARTIES HP,

  IBY_EXTERNAL_PAYEES_ALL BANK,

  POZ_SUPPLIER_ADDRESS_V PSAV,

  AP_TERMS_TL TERMS,

  POZ_SITE_ASSIGNMENTS_ALL_M PSAA,

  HR_OPERATING_UNITS HRO,

  HR_OPERATING_UNITS HRO1,

  IBY_EXT_PARTY_PMT_MTHDS PAY_METHOD

WHERE PSP.VENDOR_ID(+)               =PSV.VENDOR_ID

AND TAX_PRF.PARTY_ID(+)                =PSV.PARTY_ID

AND TAX_PRF.PARTY_TAX_PROFILE_ID       = HCA.OWNER_TABLE_ID(+)

AND (HCA.OWNER_TABLE_NAME          = 'ZX_PARTY_TAX_PROFILE'

OR HCA.OWNER_TABLE_NAME           IS NULL)

AND HCAVAL.LOOKUP_CODE(+)          =HCA.CLASS_CODE

AND (REGT.LOOKUP_TYPE              ='ZX_REGISTRATIONS_TYPE'

OR REGT.LOOKUP_TYPE               IS NULL)

AND TAX_PRF.REGISTRATION_TYPE_CODE     =REGT.LOOKUP_CODE(+)

AND TAX_PRF.PARTY_TAX_PROFILE_ID       =ZXR.PARTY_TAX_PROFILE_ID(+)

AND PSSV.VENDOR_ID(+)              =PSV.VENDOR_ID

AND PSSV.VENDOR_SITE_ID            = IEPA.SUPPLIER_SITE_ID(+)

AND PSC.SUP_PARTY_ID(+)            = PSV.PARTY_ID

AND hr.subject_id(+)               =PSV.PARTY_ID

AND( hr.relationship_code          = 'CONTACT'

OR hr.relationship_code           IS NULL)

AND (hr.object_table_name          = 'HZ_PARTIES'

OR hr.object_table_name           IS NULL)

AND hoc.party_relationship_id(+)   = hr.relationship_id

AND IEPA.EXT_PAYEE_ID              = IEPPM.EXT_PMT_PARTY_ID(+)

AND ((IEPPM.INACTIVE_DATE         IS NULL)

OR (IEPPM.INACTIVE_DATE            > SYSDATE) )

AND IPIUA.EXT_PMT_PARTY_ID (+)     = IEPA.EXT_PAYEE_ID

AND ( IPIUA.INSTRUMENT_TYPE        = 'BANKACCOUNT'

OR IPIUA.INSTRUMENT_TYPE          IS NULL)

AND ( IPIUA.PAYMENT_FLOW           = 'DISBURSEMENTS'

OR IPIUA.PAYMENT_FLOW             IS NULL)

AND ( IPIUA.ORDER_OF_PREFERENCE    = 1

OR IPIUA.ORDER_OF_PREFERENCE      IS NULL)

AND IPIUA.INSTRUMENT_ID            = IEBA.EXT_BANK_ACCOUNT_ID(+)

AND HP.PARTY_ID (+)                = IEBA.BANK_ID

AND IEBA.BRANCH_ID                 = EBB.BRANCH_PARTY_ID(+)

AND BANK.EXT_PAYEE_ID(+)           =PSV.VENDOR_ID

AND PSAV.LOCATION_ID(+)            =PSSV.LOCATION_ID

AND TERMS.TERM_ID(+)               =PSSV.TERMS_ID

AND PSAA.VENDOR_SITE_ID(+)         =PSSV.VENDOR_SITE_ID

AND HRO.ORGANIZATION_ID(+)         =PSAA.BU_ID

AND HRO1.ORGANIZATION_ID(+)        =PSAA.BILL_TO_BU_ID

AND PAY_METHOD.EXT_PMT_PARTY_ID(+) =BANK.EXT_PAYEE_ID

AND BANK.EXT_PAYEE_ID              =PAY_METHOD.EXT_PMT_PARTY_ID(+)