This AP Bank Account Listing query retrieves the Internal bank, branch, bank account and payment document that has been defined.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves internal bank account details.
CBA.BANK_ACCOUNT_NAME,
CBBV.BANK_NAME,
CBBV.BANK_BRANCH_NAME,
CBA.ACCOUNT_CLASSIFICATION,
CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
CBA.DESCRIPTION DESCRIPTION,
CBA.START_DATE,
CBA.END_DATE,
CBA.CURRENCY_CODE,
NVL (CBA.MAX_CHECK_AMOUNT, 0) MAX_CHECK_AMOUNT,
NVL (CBA.MIN_CHECK_AMOUNT, 0) MIN_CHECK_AMOUNT,
ALC.DISPLAYED_FIELD DISBURSEMENT_TYPE,
HAOU.NAME ORGANIZATION_NAME,
CPD.PAYMENT_DOCUMENT_NAME,
CPD.FORMAT_CODE DISBURSEMENT_CODE,
CPD.NUMBER_OF_SETUP_DOCUMENTS NUM_SETUP_CHECKS,
CPD.LAST_ISSUED_DOCUMENT_NUMBER,
CPD.LAST_AVAILABLE_DOCUMENT_NUMBER,
CPD.INACTIVE_DATE,
CBBV.BANK_INSTITUTION_TYPE,
GCC.ACCOUNT_TYPE,
CBBV.BRANCH_PARTY_ID,
CBA.BANK_ACCOUNT_ID,
HAOU.ORGANIZATION_ID,
CGAC.BANK_ACCT_USE_ID
FROM
CE_BANK_BRANCHES_V CBBV,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_GL_ACCOUNTS_CCID CGAC,
GL_CODE_COMBINATIONS GCC,
GL_CODE_COMBINATIONS GCC2,
CE_PAYMENT_DOCUMENTS CPD,
AP_LOOKUP_CODES ALC,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND CPD.INTERNAL_BANK_ACCOUNT_ID(+) = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID
AND CBAUA.BANK_ACCT_USE_ID = CGAC.BANK_ACCT_USE_ID
AND GCC.CODE_COMBINATION_ID(+) = CGAC.ASSET_CODE_COMBINATION_ID
AND GCC2.CODE_COMBINATION_ID(+) = CGAC.CASH_CLEARING_CCID
AND CBAUA.ORG_ID = HAOU.ORGANIZATION_ID
AND ALC.LOOKUP_TYPE(+) = 'DISBURSEMENT TYPE'
AND ALC.LOOKUP_CODE(+) = CPD.FORMAT_CODE
AND CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'