隨手筆記

銀行資訊查詢SQL

select CBU.ORG_ID OU_ID,
       GLE.SHORT_NAME OU_NAME,
       BANK.PARTY_ID BANK_ID,
       BANK.PARTY_NAME BANK_NAME,
       BRANCH.PARTY_ID BANK_BRANCH_ID,
       BRANCH.PARTY_NAME BANK_BRANCH_NAME,
       BRANCH.ADDRESS1 || BRANCH.ADDRESS2 || BRANCH.ADDRESS3 ||
       BRANCH.ADDRESS4 ADDRESS,
       HCP.EFT_SWIFT_CODE SWIFT_CODE,
       CBA.BANK_ACCOUNT_NAME,
       CBA.BANK_ACCOUNT_NUM,
       CBA.CURRENCY_CODE,
       CBA.ACCOUNT_CLASSIFICATION ACCOUNT_TYPE,
       DECODE(CBU.AP_USE_ENABLE_FLAG, 'Y', 'AP') MODEL_TYPE,
       CGA.AP_ASSET_CCID BANK_ACCOUNT_CCID,
       CBA.BANK_ACCOUNT_ID,
       CBU.BANK_ACCT_USE_ID
  from CE.CE_BANK_ACCOUNTS      CBA,
       AR.HZ_PARTIES            BANK,
       AR.HZ_PARTIES            BRANCH,
       AR.HZ_CONTACT_POINTS     HCP,
       CE.CE_GL_ACCOUNTS_CCID   CGA,
       CE.CE_BANK_ACCT_USES_ALL CBU,
       APPS.HR_OPERATING_UNITS  HOU,
       GL.GL_LEDGERS            GLE
 where CBA.BANK_ID = BANK.PARTY_ID
   and CBA.BANK_BRANCH_ID = BRANCH.PARTY_ID
   and CBA.BANK_ACCOUNT_ID = CBU.BANK_ACCOUNT_ID
   and CBU.ORG_ID = HOU.ORGANIZATION_ID
   and HOU.SET_OF_BOOKS_ID = GLE.LEDGER_ID
   and CGA.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
   and CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
   and nvl(CBA.END_DATE, sysdate) >= sysdate
   and HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   anD HCP.OWNER_TABLE_ID(+) = BRANCH.PARTY_ID
   and HCP.CONTACT_POINT_TYPE(+) = 'EFT'
   and HCP.STATUS(+) = 'A'
   and CBU.AP_USE_ENABLE_FLAG = 'Y'
union all
select CBU.ORG_ID OU_ID,
       GLE.SHORT_NAME OU_NAME,
       BANK.PARTY_ID BANK_ID,
       BANK.PARTY_NAME BANK_NAME,
       BRANCH.PARTY_ID BANK_BRANCH_ID,
       BRANCH.PARTY_NAME BANK_BRANCH_NAME,
       BRANCH.ADDRESS1 || BRANCH.ADDRESS2 || BRANCH.ADDRESS3 ||
       BRANCH.ADDRESS4 ADDRESS,
       HCP.EFT_SWIFT_CODE SWIFT_CODE,
       CBA.BANK_ACCOUNT_NAME,
       CBA.BANK_ACCOUNT_NUM,
       CBA.CURRENCY_CODE,
       CBA.ACCOUNT_CLASSIFICATION ACCOUNT_TYPE,
       DECODE(CBU.AR_USE_ENABLE_FLAG, 'Y', 'AR') MODEL_TYPE,
       CGA.AR_ASSET_CCID BANK_ACCOUNT_CCID,
       CBA.BANK_ACCOUNT_ID,
       CBU.BANK_ACCT_USE_ID
  from CE.CE_BANK_ACCOUNTS      CBA,
       AR.HZ_PARTIES            BANK,
       AR.HZ_PARTIES            BRANCH,
       AR.HZ_CONTACT_POINTS     HCP,
       CE.CE_GL_ACCOUNTS_CCID   CGA,
       CE.CE_BANK_ACCT_USES_ALL CBU,
       APPS.HR_OPERATING_UNITS  HOU,
       GL.GL_LEDGERS            GLE
 where CBA.BANK_ID = BANK.PARTY_ID
   and CBA.BANK_BRANCH_ID = BRANCH.PARTY_ID
   and CBA.BANK_ACCOUNT_ID = CBU.BANK_ACCOUNT_ID
   and CBU.ORG_ID = HOU.ORGANIZATION_ID
   and HOU.SET_OF_BOOKS_ID = GLE.LEDGER_ID
   and CGA.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
   and CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
   and nvl(CBA.END_DATE, sysdate) >= sysdate
   and HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   and HCP.OWNER_TABLE_ID(+) = BRANCH.PARTY_ID
   and HCP.CONTACT_POINT_TYPE(+) = 'EFT'
   and HCP.STATUS(+) = 'A'
   and CBU.AR_USE_ENABLE_FLAG = 'Y';