CE_BANK_BRANCHES

Details

Schema: FUSION
Object owner: CE
Object type: VIEW

Columns

Name
ROW_ID BANK_HOME_COUNTRY BANK_PARTY_ID BANK_PARTY_NUMBER BANK_NAME BANK_NAME_ALT SHORT_BANK_NAME BANK_NUMBER BRANCH_PARTY_ID BANK_BRANCH_NAME BRANCH_PARTY_NUMBER BANK_BRANCH_NAME_ALT BRANCH_NUMBER START_DATE END_DATE ADDRESS_LINE1 ADDRESS_LINE2 ADDRESS_LINE3 ADDRESS_LINE4 CITY STATE PROVINCE ZIP COUNTRY BANK_INSTITUTION_TYPE BRANCH_INSTITUTION_TYPE BANK_BRANCH_TYPE DESCRIPTION EFT_SWIFT_CODE EFT_USER_NUMBER EDI_ID_NUMBER PK_ID BANK_CODE EDI_LOCATION RFC COUNTRY_NAME REQUEST_ID LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN CREATED_BY CREATION_DATE OBJECT_VERSION_NUMBER

Name

ROW_ID

BANK_HOME_COUNTRY

BANK_PARTY_ID

BANK_PARTY_NUMBER

BANK_NAME

BANK_NAME_ALT

SHORT_BANK_NAME

BANK_NUMBER

BRANCH_PARTY_ID

BANK_BRANCH_NAME

BRANCH_PARTY_NUMBER

BANK_BRANCH_NAME_ALT

BRANCH_NUMBER

START_DATE

END_DATE

ADDRESS_LINE1

ADDRESS_LINE2

ADDRESS_LINE3

ADDRESS_LINE4

CITY

STATE

PROVINCE

ZIP

COUNTRY

BANK_INSTITUTION_TYPE

BRANCH_INSTITUTION_TYPE

BANK_BRANCH_TYPE

DESCRIPTION

EFT_SWIFT_CODE

EFT_USER_NUMBER

EDI_ID_NUMBER

PK_ID

BANK_CODE

EDI_LOCATION

RFC

COUNTRY_NAME

REQUEST_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATED_BY

CREATION_DATE

OBJECT_VERSION_NUMBER

Query

SQL_Statement
SELECT CBRANCH.ROWID ROW_ID, CBRANCH.HOME_COUNTRY_CODE Bank_Home_Country, CBANK.Bank_Party_Id, CBANK.BANK_PARTY_NUMBER, CBANK.Bank_Name, CBANK.Bank_Name_Alt, NULL Short_Bank_Name, CBANK.Bank_Number, CBRANCH.Branch_Party_Id, CBRANCH.Bank_Branch_Name, CBRANCH.BRANCH_PARTY_NUMBER, CBRANCH.BANK_BRANCH_NAME_ALT, CBRANCH.BRANCH_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.Branch_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, CBRANCH.REQUEST_ID REQUEST_ID, BranchParty.LAST_UPDATE_DATE, BranchParty.LAST_UPDATED_BY, BranchParty.LAST_UPDATE_LOGIN, BranchParty.CREATED_BY, BranchParty.CREATION_DATE, BranchParty.OBJECT_VERSION_NUMBER 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') 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') 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'

SQL_Statement

SELECT

CBRANCH.ROWID ROW_ID,

CBRANCH.HOME_COUNTRY_CODE Bank_Home_Country,

CBANK.Bank_Party_Id,

CBANK.BANK_PARTY_NUMBER,

CBANK.Bank_Name,

CBANK.Bank_Name_Alt,

NULL Short_Bank_Name,

CBANK.Bank_Number,

CBRANCH.Branch_Party_Id,

CBRANCH.Bank_Branch_Name,

CBRANCH.BRANCH_PARTY_NUMBER,

CBRANCH.BANK_BRANCH_NAME_ALT,

CBRANCH.BRANCH_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.Branch_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,

CBRANCH.REQUEST_ID REQUEST_ID,

BranchParty.LAST_UPDATE_DATE,

BranchParty.LAST_UPDATED_BY,

BranchParty.LAST_UPDATE_LOGIN,

BranchParty.CREATED_BY,

BranchParty.CREATION_DATE,

BranchParty.OBJECT_VERSION_NUMBER

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')

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')

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'