获取银行账户分配明细SQL

    今天给客户提取所有银行账户的机构分配信息,写了以下一段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(+);

发表评论

邮箱地址不会被公开。 必填项已用*标注