Supplier Bank Assignment Query SQL Oracle Fusion

 Select * from (SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  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,

  'N' detailView,

  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

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 PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  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,

  'N' detailView,

  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

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

/*AND (Payee.org_id, Payee.org_type)          IN

(SELECT uo.organization_id,

uo.organization_type

FROM ce_security_profiles_v uo

)*/

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  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,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.party_name party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  NULL site_name,

  NULL alt_site_name,

  NULL party_site_name,

  NULL organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (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, Payer.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,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.org_id                            IS NULL

AND Payer.org_type                          IS NULL

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  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,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.PARTY_NAME party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  address.party_site_name site_name,

  NULL alt_site_name,

  address.party_site_name,

  ou.NAME organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (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, Payer.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,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  HZ_CUST_ACCT_SITES_ALL site,

  HZ_CUST_SITE_USES_ALL site_use,

  hz_party_sites address,

  hr_operating_units ou,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.ACCT_SITE_USE_ID                   = site_use.site_use_id(+)

AND site_use.cust_acct_site_id               = site.cust_acct_site_id(+)

AND site.party_site_id                       = address.party_site_id(+)

AND Payer.org_id                             = ou.organization_id (+)

AND Payer.org_id                            IS NOT NULL

AND Payer.org_type                          IS NOT NULL

  /*AND (Payer.org_id, Payer.org_type)          IN

  (SELECT uo.organization_id,

  uo.organization_type

  FROM ce_security_profiles_v uo

  )*/

) where

PARTY_NAME = :bind_partyName

or bank_account_name = :bind_bankAccountName

or bank_account_number = :bind_bankAccountNumber

or party_number = :bind_partyNumber