1. Tables and Views for Financials
  2. CE_ALL_BANK_BRANCHES_V

CE_ALL_BANK_BRANCHES_V

Details

  • Schema: FUSION

  • Object owner: CE

  • Object type: VIEW

Columns

Name

ROW_ID

BANK_HOME_COUNTRY

BANK_PARTY_ID

BANK_NAME

BANK_NAME_ORIGINAL

BANK_NAME_ALT

SHORT_BANK_NAME

BANK_NUMBER

BANK_PARTY_NUMBER

BRANCH_PARTY_ID

BANK_BRANCH_NAME

BANK_BRANCH_NAME_ORIGINAL

BANK_BRANCH_NAME_ALT

BRANCH_NUMBER

BRANCH_PARTY_NUMBER

START_DATE

END_DATE

ADDRESS_LINE1

ADDRESS_LINE2

ADDRESS_LINE3

ADDRESS_LINE4

CITY

STATE

PROVINCE

ZIP

COUNTRY

BANK_INSTITUTION_TYPE

BANK_BRANCH_TYPE

DESCRIPTION

EFT_SWIFT_CODE

EFT_USER_NUMBER

EDI_ID_NUMBER

PK_ID

BANK_CODE

EDI_LOCATION

RFC

COUNTRY_NAME

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATED_BY

CREATION_DATE

OBJECT_VERSION_NUMBER

EXTERNAL_BANK_TYPE

Query

SQL_Statement

SELECT

CBRANCH.ROWID ROW_ID,

CBRANCH.HOME_COUNTRY_CODE Bank_Home_Country,

CBANK.Bank_Party_Id,

decode(CBANK.Bank_Institution_Type, 'BANK', CBANK.Bank_Name,

decode(CBANK.Bank_Name, 'CE_EMP_UNSPECIFIED_BANK', null, CBANK.Bank_Name) ) Bank_Name,

CBANK.BANK_NAME BANK_NAME_ORIGINAL ,

CBANK.Bank_Name_Alt,

NULL Short_Bank_Name,

CBANK.Bank_Number,

CBANK.BANK_PARTY_NUMBER,

CBRANCH.Branch_Party_Id,

decode(CBRANCH.BRANCH_INSTITUTION_TYPE, 'BANK_BRANCH', CBRANCH.Bank_Branch_Name,

decode(CBRANCH.Bank_Branch_Name, 'CE_EMP_UNSPECIFIED_BRANCH', null, CBRANCH.Bank_Branch_Name)) Bank_Branch_Name,

CBRANCH.Bank_Branch_Name BANK_BRANCH_NAME_ORIGINAL ,

CBRANCH.BANK_BRANCH_NAME_ALT,

CBRANCH.BRANCH_NUMBER,

BranchParty.PARTY_NUMBER BRANCH_PARTY_NUMBER,

CBRANCH.start_date,

CBRANCH.end_date,

BranchParty.address1 Address_Line1,

BranchParty.address2 Address_Line2,

BranchParty.address3 Address_Line3,

BranchParty.address4 Address_Line4,

BranchParty.city City,

BranchParty.state State,

BranchParty.province Province,

BranchParty.postal_code Zip,

BranchParty.country Country,

CBANK.Bank_Institution_Type,

CBRANCH.Bank_Branch_Type,

BranchParty.mission_statement Description,

CBRANCH.EFT_Swift_Code,

CBRANCH.EFT_User_Number,

CBRANCH.EDI_ID_Number,

CBRANCH.Branch_Party_Id PK_ID,

CBRANCH.bank_code,

CBRANCH.edi_location,

CBRANCH.rfc_code rfc,

FT.TERRITORY_SHORT_NAME COUNTRY_NAME,

BranchParty.LAST_UPDATE_DATE,

BranchParty.LAST_UPDATED_BY,

BranchParty.LAST_UPDATE_LOGIN,

BranchParty.CREATED_BY,

BranchParty.CREATION_DATE,

BranchParty.OBJECT_VERSION_NUMBER,

substr(CBANK.Bank_Institution_Type, 1, (length(CBANK.Bank_Institution_Type) -5)) EXTERNAL_BANK_TYPE

From CE_INDEX_BANK_BRANCHES CBRANCH,

CE_INDEX_BANKS CBANK,

HZ_PARTIES BranchParty,

FND_TERRITORIES_VL FT

Where

CBRANCH.BRANCH_INSTITUTION_TYPE in ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH', 'EMPLOYEE_BANK_BRANCH' , 'CUSTOMER_BANK_BRANCH', 'SUPPLIER_BANK_BRANCH' , 'ADHOC_PAYEE_BANK_BRANCH')

And trunc(SYSDATE) between TRUNC(CBRANCH.start_date)

and NVL(TRUNC(CBRANCH.end_date), SYSDATE+1)

AND CBRANCH.BANK_PARTY_ID = CBANK.BANK_PARTY_ID

And CBANK.Bank_Institution_Type in ('BANK', 'CLEARINGHOUSE', 'EMPLOYEE_BANK' , 'CUSTOMER_BANK', 'SUPPLIER_BANK' , 'ADHOC_PAYEE_BANK')

And trunc(SYSDATE) between TRUNC(CBANK.start_date)

and NVL(TRUNC(CBANK.end_date), SYSDATE+1)

AND CBRANCH.HOME_COUNTRY_CODE = FT.TERRITORY_CODE

AND CBRANCH.BRANCH_PARTY_ID = BranchParty.PARTY_ID

And BranchParty.PARTY_TYPE = 'ORGANIZATION'

And BranchParty.status = 'A'