1. Tables and Views for Procurement
  2. POZ_EXT_BANK_ACCOUNTS_V

POZ_EXT_BANK_ACCOUNTS_V

Details

  • Schema: FUSION

  • Object owner: POZ

  • Object type: VIEW

Columns

Name

EXT_BANK_ACCOUNT_ID

BANK_ACCOUNT_NAME

MASKED_BANK_ACCOUNT_NUM

BANK_ACCOUNT_TYPE

START_DATE

END_DATE

BANK_ID

BANK_NAME

BANK_NUMBER

BRANCH_ID

BRANCH_NAME

BRANCH_NUMBER

EFT_SWIFT_CODE

Query

SQL_Statement

SELECT

BankAccount.ext_bank_account_id,

BankAccount.bank_account_name,

BankAccount.masked_bank_account_num,

BankAccount.bank_account_type,

BankAccount.start_date,

BankAccount.end_date,

BankAccount.bank_id,

CEBANK.Bank_Name bank_name,

BankProfile.bank_or_branch_number bank_number,

BankAccount.branch_id,

CEBRANCH.Bank_Branch_Name branch_name,

BranchProfile.bank_or_branch_number branch_number,

BranchCP.eft_swift_code

FROM

IBY_EXT_BANK_ACCOUNTS BankAccount,

HZ_ORGANIZATION_PROFILES BankProfile,

HZ_ORGANIZATION_PROFILES BranchProfile,

HZ_CONTACT_POINTS BranchCP,

CE_INDEX_BANKS CEBANK,

CE_INDEX_BANK_BRANCHES CEBRANCH

WHERE

BankAccount.bank_id = CEBANK.BANK_PARTY_ID(+)

AND CEBANK.BANK_PARTY_ID = BankProfile.party_id(+)

AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(BankProfile.effective_start_date(+)), SYSDATE-1)

AND NVL(TRUNC(BankProfile.effective_end_date(+)), SYSDATE+1)

AND BankAccount.branch_id = CEBRANCH.BRANCH_PARTY_ID(+)

AND CEBRANCH.BRANCH_PARTY_ID = BranchProfile.party_id(+)

AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(BranchProfile.effective_start_date(+)) ,SYSDATE-1)

AND NVL(TRUNC(BranchProfile.effective_end_date(+)), SYSDATE+1)

AND BranchCP.owner_table_id(+) = CEBRANCH.BRANCH_PARTY_ID

AND BranchCP.owner_table_name(+) = 'HZ_PARTIES'

AND BranchCP.contact_point_type(+) = 'EFT'

AND BranchCP.status(+) = 'A'

AND TRUNC(SYSDATE) between NVL(TRUNC(BranchCP.start_date(+)), SYSDATE-1) AND NVL(TRUNC(BranchCP.end_date(+)), SYSDATE+1)