---Payables Internal Bank account query---

SELECT

b.bank_name

,b.institution_type

,b.bank_branch_name

,b.end_date bank_inactive_on

,b.BANK_NUM

,B.BANK_BRANCH_TYPE

,b.eft_user_number eft_number

,B.EFT_SWIFT_CODE

,b.address_line1

,b.address_line2

,b.address_line3

,b.address_line4

,b.city

,b.state

,b.zip

,b.county

,b.country

,a.bank_account_name

,a.bank_account_num

,a.INACTIVE_DATE bank_account_inactive_on

,a.currency_code

,a.bank_account_type

,a.ACCOUNT_TYPE account_use

,a.ZERO_AMOUNTS_ALLOWED allow_zero_payments

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.ASSET_CODE_COMBINATION_ID) bank_acc_cash_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.cash_clearing_ccid) bank_acc_cash_clr_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.bank_charges_ccid) bank_acc_bank_charges_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.bank_errors_ccid) bank_acc_bank_errors_account                     

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.GAIN_CODE_COMBINATION_ID) gain_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=a.LOSS_CODE_COMBINATION_ID) loss_account            

,c.name pay_document_name

,c.INACTIVE_DATE pay_doc_inactive_on

,c.DISBURSEMENT_TYPE_LOOKUP_CODE disbursement_type

,(select name from APPS.AP_CHECK_FORMATS a2 where check_format_id = c.CHECK_FORMAT_ID) payment_format

,c.LAST_DOCUMENT_NUM

,c.LAST_AVAILABLE_DOCUMENT_NUM

,c.FUTURE_DATED_PAYMENTS_FLAG

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=c.BANK_CHARGES_CCID) doc_bank_charge_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=c.CASH_CLEARING_CCID) doc_cash_clearing_account

,(select CONCATENATED_SEGMENTS

        from APPS.gl_code_combinations_kfv a1

            where a1.code_combination_id=c.BANK_ERRORS_CCID) doc_bank_errors_account

,a.RECEIPT_MULTI_CURRENCY_FLAG multi_currency_rec_flag

,a.MULTI_CURRENCY_FLAG multi_currency_payments_flag

,DECODE(E.TRX_TYPE,'CREDIT','Receipt','DEBIT','Payment',E.TRX_TYPE) BANK_TRAN_CD_TRX_TYPE

,E.TRX_CODE BANK_TRAN_CD_TRX_CODE

,E.DESCRIPTION BANK_TRAN_CD_DESCRIPTION

,E.START_DATE BANK_TRAN_CD_START_DT

,E.END_DATE BANK_TRAN_CD_END_DT

,DECODE(E.RECONCILE_FLAG,'AR','AR Receipts','AP','AP Payments',E.RECONCILE_FLAG) BANK_TRAN_CD_TRAN_SOURCE

,d.name ou_name

FROM APPS.AP_BANK_ACCOUNTS_ALL a

,apps.ap_bank_branches b

,APPS.AP_CHECK_STOCKS_all C

,hr_operating_units d

,apps.ce_transaction_codes e

WHERE a.bank_branch_id(+) = b.bank_branch_id

and account_type='INTERNAL'

and c.bank_account_id(+) = a.bank_account_id

and d.organization_id(+) = a.org_id

and a.ORG_ID = C.ORG_ID(+)

and a.bank_account_id = e.bank_account_id(+)

order by BANK_NAME