CE_ALL_BANK_BRANCHES_SIM

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_NUMBER BANK_PARTY_NUMBER BRANCH_PARTY_ID BANK_BRANCH_NAME BANK_BRANCH_NAME_ORIGINAL BRANCH_NUMBER BRANCH_PARTY_NUMBER COUNTRY PK_ID COUNTRY_NAME EXTERNAL_BANK_TYPE EFT_SWIFT_CODE

Name

ROW_ID

BANK_HOME_COUNTRY

BANK_PARTY_ID

BANK_NAME

BANK_NAME_ORIGINAL

BANK_NUMBER

BANK_PARTY_NUMBER

BRANCH_PARTY_ID

BANK_BRANCH_NAME

BANK_BRANCH_NAME_ORIGINAL

BRANCH_NUMBER

BRANCH_PARTY_NUMBER

COUNTRY

PK_ID

COUNTRY_NAME

EXTERNAL_BANK_TYPE

EFT_SWIFT_CODE

Query

SQL_Statement
SELECT CBRANCH.ROWID ROW_ID, CBRANCH.HOME_COUNTRY_CODE Bank_Home_Country, CBANK.BANK_PARTY_ID 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_Number, CBANK.BANK_PARTY_NUMBER, CBRANCH.BRANCH_PARTY_ID 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.BRANCH_NUMBER, CBRANCH.BRANCH_PARTY_NUMBER, CBRANCH.HOME_COUNTRY_CODE Country, CBRANCH.BRANCH_PARTY_ID PK_ID, FT.TERRITORY_SHORT_NAME COUNTRY_NAME, substr(CBANK.Bank_Institution_Type, 1, (length(CBANK.Bank_Institution_Type) -5)) EXTERNAL_BANK_TYPE, CBRANCH.EFT_Swift_Code FROM CE_INDEX_BANK_BRANCHES CBRANCH, CE_INDEX_BANKS CBANK, FND_TERRITORIES_VL FT WHERE CBRANCH.BRANCH_INSTITUTION_TYPE in ('BANK_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', '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

SQL_Statement

SELECT

CBRANCH.ROWID ROW_ID,

CBRANCH.HOME_COUNTRY_CODE Bank_Home_Country,

CBANK.BANK_PARTY_ID 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_Number,

CBANK.BANK_PARTY_NUMBER,

CBRANCH.BRANCH_PARTY_ID 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.BRANCH_NUMBER,

CBRANCH.BRANCH_PARTY_NUMBER,

CBRANCH.HOME_COUNTRY_CODE Country,

CBRANCH.BRANCH_PARTY_ID PK_ID,

FT.TERRITORY_SHORT_NAME COUNTRY_NAME,

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

CBRANCH.EFT_Swift_Code

FROM

CE_INDEX_BANK_BRANCHES CBRANCH,

CE_INDEX_BANKS CBANK,

FND_TERRITORIES_VL FT

WHERE

CBRANCH.BRANCH_INSTITUTION_TYPE in ('BANK_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', '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