Oracle EBS ERP Bank Information query View

CREATE OR REPLACE VIEW XXX_STD_CE_BANKINFO_V AS

SELECT cbau.org_id

Cba.account_owner_party_id L_Party_ID

Cba.account_owner_org_id Legal_Entity

Hou.name org_name

Cbv.bank_name

Cbb.bank_branch_name

Cba.bank_account_name

Cba.bank_account_num

Cba.currency_code

Cbv.bank_party_id Bank_ID

Cba.bank_account_id

Cbau.bank_acct_use_id

Nvl (cba.multi_currency_allowed_flag,'N') Mutii_Flag

Nvl (cba.payment_multi_currency_flag,'N') MutiiAP_Flag

Nvl (cba.receipt_multi_currency_flag,'N') MutiiAR_Flag

Gcc.concatenated_segments Bank_Account

Gccap.concatenated_segments BankAP_Account

Gccar.concatenated_segments BankAR_Account

Gcc.code_combination_id gl_CCID

Gccap.code_combination_id AP_CCID

Gccar.code_combination_id AR_CCID

Cbau.end_date bank_use_end_date

Cba.end_date bank_end_date

Gccap_Clearing.concatenated_segments BankAPClearing_Account

Gccap_Clearing.code_combination_id APClearing_CCID

Gccap_Charges.concatenated_segments BankAPCharges_Account

Gccap_Charges.code_combination_id APCharges_CCID

Gccap_BankErrors.concatenated_segments BankAPBankErrors_Account

Gccap_BankErrors.code_combination_id APBankErrors_CCID

Gccap_RealizedGain.concatenated_segments BankAPRealizedGain_Account

Gccap_RealizedGain.code_combination_id APRealizedGain_CCID

Gccap_RealizedLoss.concatenated_segments BankAPRealizedLoss_Account

Gccap_RealizedLoss.code_combination_id APRealizedLoss_CCID

Gccap_Future.concatenated_segments BankAPRealizedFuture_Account

Gccap_Future.code_combination_id APRealizedFuture_CCID

FROM ce_banks_v cbv

Ce_bank_accounts cba

Ce_bank_branches_v cbb

Ce_bank_acct_uses_all cbau

Ce_gl_accounts_ccid cga

Gl_code_combinations_kfv gcc

Gl_code_combinations_kfv gccap

Gl_code_combinations_kfv gccar

Gl_code_combinations_kfv gccap_Clearing

Gl_code_combinations_kfv gccap_Charges

Gl_code_combinations_kfv gccap_BankErrors

Gl_code_combinations_kfv gccap_RealizedGain

Gl_code_combinations_kfv gccap_RealizedLoss

Gl_code_combinations_kfv gccap_Future

Hr_operating_units hou

Where cbv.bank_party_id = cba.bank_id

And cbb.bank_party_id = cba.bank_id

And cbb.branch_party_id = cba.bank_branch_id

And cbau.bank_account_id = cba.bank_account_id

And cbau.bank_acct_use_id = cga.bank_acct_use_id

And cbau.org_id = hou.organization_id

And gcc.code_combination_id (+) = cba.asset_code_combination_id

And nvl (cga.ap_asset_ccid, cga.ar_asset_ccid) = gccap.code_combination_id (+)

And cga.cash_clearing_ccid = gccap_Clearing.code_combination_id (+)

And cga.bank_charges_ccid = gccap_Charges.code_combination_id (+)

And cga.bank_errors_ccid = gccap_BankErrors.code_combination_id (+)

And cga.gain_code_combination_id = gccap_RealizedGain.code_combination_id (+)

And cga.loss_code_combination_id = gccap_RealizedLoss.code_combination_id (+)

And cga.future_dated_payment_ccid = gccap_Future.code_combination_id (+)

And nvl (cga.ar_asset_ccid, cga.ap_asset_ccid) = gccar.code_combination_id (+)