ORACLE MASTERMINDS

Fusion Applications - Supplier And Bank Account Assignments

 The below query provides the detailed information of Bank, Branch & Accounts assigned to Supplier at the account level or site level in Fusion Applications.

 SELECT  ibyextbankaccts.bank_account_id,
  ibyextbankaccts.bank_account_name,
  ibyextbankaccts.bank_account_number,
  ibyextbankaccts.currency_code,
  ibyextbankaccts.bank_name,
  ibyextbankaccts.bank_number,
  ibyextbankaccts.bank_branch_name,
  ibyextbankaccts.bank_account_type,
  ibyextbankaccts.branch_number,
  ibyextbankaccts.iban_number,
  ibyextbankaccts.eft_swift_code, 
  PaymentInstrumentUsesEO.PAYMENT_FLOW,
  supplier.vendor_name party_name,
  supplier.vendor_name_alt alt_party_name,
  supplier.segment1 party_number,
  NULL site_name,
  NULL alt_site_name,
  address.party_site_name,
  NULL organization_name,
  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SUPPLIER'
  ),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='ADDRESS'
  )),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='ADDRESS_OPERATING'
  )),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SITE'
  )) assignment_level,
  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,
  ibyextbankaccts.bank_party_id bank_party_id,
  ibyextbankaccts.branch_party_id branch_party_id,
  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'Y'
  ),
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'N'
  )) FOREIGN_PAYMENT_USE_FLAG,
  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'Y'
  ),
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'N'
  )) primary_flag,
  Payee.payee_party_id party_id,
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SUPPLIER'
  ) party_type
  ,PaymentInstrumentUsesEO.created_by
FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,
  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,
  IBY_EXTERNAL_PAYEES_ALL Payee,
  poz_suppliers_v supplier,
  hz_party_sites address
WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id
AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'
AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'
AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id
AND Payee.payee_party_id                     = supplier.party_id
AND Payee.party_site_id                      = address.party_site_id(+)
AND Payee.org_id                            IS NULL
AND Payee.org_type                          IS NULL
UNION
SELECT ibyextbankaccts.bank_account_id,
  ibyextbankaccts.bank_account_name,
  ibyextbankaccts.bank_account_number,
  ibyextbankaccts.currency_code,
  ibyextbankaccts.bank_name,
  ibyextbankaccts.bank_number,
  ibyextbankaccts.bank_branch_name,
  ibyextbankaccts.bank_account_type,
  ibyextbankaccts.branch_number,
  ibyextbankaccts.iban_number,
  ibyextbankaccts.eft_swift_code, 
  PaymentInstrumentUsesEO.PAYMENT_FLOW,
  supplier.vendor_name party_name,
  supplier.vendor_name_alt alt_party_name,
  supplier.segment1 party_number,
  DECODE(supplier.vendor_type_lookup_code, 'EMPLOYEE', NVL(
  (SELECT NVL(ALC.displayed_field, site.VENDOR_SITE_CODE)
  FROM AP_LOOKUP_CODES ALC
  WHERE ALC.lookup_type(+) = 'VENDOR_SITE_CODE'
  AND ALC.lookup_code(+)   = site.VENDOR_SITE_CODE
  ), site.VENDOR_SITE_CODE), site.VENDOR_SITE_CODE) site_name,
  site.vendor_site_code_alt alt_site_name,
  address.party_site_name,
  ou.NAME organization_name,
  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SUPPLIER'
  ),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='ADDRESS'
  )),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='ADDRESS_OPERATING'
  )),
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SITE'
  )) assignment_level,
  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,
  ibyextbankaccts.bank_party_id bank_party_id,
  ibyextbankaccts.branch_party_id branch_party_id,
  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'Y'
  ),
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'N'
  )) FOREIGN_PAYMENT_USE_FLAG,
  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'Y'
  ),
  (SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_YES_NO'
  AND lookup_code   = 'N'
  )) primary_flag,
  Payee.payee_party_id party_id,
  (SELECT MEANING
  FROM fnd_lookup_values_vl
  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'
  AND LOOKUP_CODE   ='SUPPLIER'
  ) party_type
  ,PaymentInstrumentUsesEO.created_by
FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,
  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,
  IBY_EXTERNAL_PAYEES_ALL Payee,
  poz_suppliers_v supplier,
  POZ_SUPPLIER_SITES_V site,
  hz_party_sites address,
  hr_operating_units ou
WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id
AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'
AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'
AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id
AND Payee.payee_party_id                     = supplier.party_id
AND Payee.supplier_site_id                   = site.vendor_site_id(+)
AND Payee.party_site_id                      = address.party_site_id(+)
AND Payee.org_id                             = ou.organization_id (+)
AND Payee.org_id                            IS NOT NULL
AND Payee.org_type                          IS NOT NULL