今天給客戶提取所有銀行賬戶的機構分配信息，寫了以下一段SQL命令，記錄下來以備後用：
SELECT CBA.BANK_ACCOUNT_NAME 賬戶名,
BANK_INFO.BANK_NAME 銀行名稱,
BANK_INFO.BRANCH_NAME 分行名稱,
BANK_INFO.BANK_ACCOUNT_NUM 銀行賬戶,
BANKACCTUSEEO.ORG_ID 機構ID,
DECODE(BANKACCTUSEEO.LEGAL_ENTITY_ID,
NULL,
FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(BANKACCTUSEEO.ORG_ID),
(SELECT XLE.NAME
FROM XLE_ENTITY_PROFILES XLE
WHERE XLE.LEGAL_ENTITY_ID = BANKACCTUSEEO.LEGAL_ENTITY_ID)) 機構描述,
BANKACCTUSEEO.AP_USE_ENABLE_FLAG 應付可用性,
BANKACCTUSEEO.AR_USE_ENABLE_FLAG 應收可用性,
BANKACCTUSEEO.END_DATE 失效日期
FROM CE_BANK_ACCT_USES_ALL BANKACCTUSEEO,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXEARNED,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXUNEARNED,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXCLAIMINV,
CE_LOOKUPS CL,
FND_LOOKUPS FL,
CE_RECEIVABLE_ACTIVITIES_V RA,
CE_BANK_ACCOUNTS CBA,
(SELECT BANKPARTY.PARTY_NAME BANK_NAME,
BRANCHPARTY.PARTY_NAME BRANCH_NAME,
TERRITORY_SHORT_NAME COUNTRY,
BA.BANK_ACCOUNT_NUM,
BRANCHPARTY.PARTY_ID BANK_BRANCH_ID,
BA.BANK_ACCOUNT_ID,
CL.MEANING ACCOUNT_CLASS_MEANING
FROM HZ_PARTIES BANKPARTY,
HZ_PARTIES BRANCHPARTY,
HZ_ORGANIZATION_PROFILES BANKORGPROFILE,
HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,
HZ_RELATIONSHIPS BRREL,
HZ_CODE_ASSIGNMENTS BANKCA,
HZ_CODE_ASSIGNMENTS BRANCHCA,
CE_LOOKUPS CL,
FND_TERRITORIES_VL FT,
CE_BANK_ACCOUNTS BA
WHERE BANKPARTY.PARTY_TYPE = 'ORGANIZATION'
AND BANKPARTY.STATUS = 'A'
AND BANKPARTY.PARTY_ID = BANKORGPROFILE.PARTY_ID
AND SYSDATE BETWEEN TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE) AND
NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE), SYSDATE + 1)
AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BANKCA.CLASS_CODE = 'BANK'
AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID
AND NVL(BANKCA.STATUS, 'A') = 'A'
AND BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION'
AND BRANCHPARTY.STATUS = 'A'
AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN
TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE(+)) AND
NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE(+)),
SYSDATE + 1)
AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BRANCHCA.CLASS_CODE = 'BANK_BRANCH'
AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID
AND NVL(BRANCHCA.STATUS, 'A') = 'A'
AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID
AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID
AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF'
AND BRREL.STATUS = 'A'
AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE = 'ORGANIZATION'
AND CL.LOOKUP_TYPE = 'ACCOUNT_CLASSIFICATION'
AND CL.LOOKUP_CODE = BA.ACCOUNT_CLASSIFICATION
AND BANKORGPROFILE.HOME_COUNTRY = TERRITORY_CODE
AND BA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID) BANK_INFO
WHERE 1 = 1
AND BANK_INFO.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_ACCOUNT_ID = BANKACCTUSEEO.BANK_ACCOUNT_ID
AND BANKACCTUSEEO.EDISC_RECEIVABLES_TRX_ID =
ARRECEIVABLESTRXEARNED.RECEIVABLES_TRX_ID(+)
AND BANKACCTUSEEO.UNEDISC_RECEIVABLES_TRX_ID =
ARRECEIVABLESTRXUNEARNED.RECEIVABLES_TRX_ID(+)
AND BANKACCTUSEEO.AR_CLAIM_INV_ACT_ID =
ARRECEIVABLESTRXCLAIMINV.RECEIVABLES_TRX_ID(+)
AND CL.LOOKUP_TYPE = 'ORGANIZATION_TYPE'
AND CL.LOOKUP_CODE = 'LEGAL_ENTITY'
AND FL.LOOKUP_TYPE(+) = 'XTR_DEAL_PRICE_MODELS'
AND FL.LOOKUP_CODE(+) = BANKACCTUSEEO.PRICING_MODEL
AND BANKACCTUSEEO.NEW_AR_RCPTS_RECEIVABLE_TRX_ID =
RA.RECEIVABLES_TRX_ID(+);
