View: CE_BANK_BRANCHES_V

Product:	CE - Cash Management
Description:	This view stores bank branch information.
Implementation/DBA Data:	APPS.CE_BANK_BRANCHES_V

View Text

SELECT BRANCHPARTY.ROWID ROW_ID
, BANKORGPROFILE.HOME_COUNTRY BANK_HOME_COUNTRY
, BANKORGPROFILE.PARTY_ID BANK_PARTY_ID
, BANKORGPROFILE.ORGANIZATION_NAME BANK_NAME
, BANKORGPROFILE.ORGANIZATION_NAME_PHONETIC BANK_NAME_ALT
, BANKORGPROFILE.KNOWN_AS SHORT_BANK_NAME
, BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, BRANCHPARTY.PARTY_ID BRANCH_PARTY_ID
, BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME
, BRANCHPARTY.ORGANIZATION_NAME_PHONETIC BANK_BRANCH_NAME_ALT
, BRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER
, BRANCHCA.START_DATE_ACTIVE START_DATE
, BRANCHCA.END_DATE_ACTIVE 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
, BANKCA.CLASS_CODE BANK_INSTITUTION_TYPE
, BRANCHTYPECA.CLASS_CODE BANK_BRANCH_TYPE
, BRANCHPARTY.MISSION_STATEMENT DESCRIPTION
, BRANCHCP.EFT_SWIFT_CODE EFT_SWIFT_CODE
, BRANCHCP.EFT_USER_NUMBER EFT_USER_NUMBER
, EDICP.EDI_ID_NUMBER EDI_ID_NUMBER
, BRANCHPARTY.PARTY_ID
FROM HZ_ORGANIZATION_PROFILES BANKORGPROFILE
, HZ_CODE_ASSIGNMENTS BANKCA
, HZ_PARTIES BRANCHPARTY
, HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE
, HZ_CODE_ASSIGNMENTS BRANCHCA
, HZ_RELATIONSHIPS BRREL
, HZ_CODE_ASSIGNMENTS BRANCHTYPECA
, HZ_CONTACT_POINTS BRANCHCP
, HZ_CONTACT_POINTS EDICP
WHERE SYSDATE BETWEEN TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE)
AND NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BANKCA.CLASS_CODE IN ('BANK'
, 'CLEARINGHOUSE')
AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND (BANKCA.STATUS = 'A' OR BANKCA.STATUS IS NULL)
AND BANKCA.OWNER_TABLE_ID = BANKORGPROFILE.PARTY_ID
AND BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION'
AND BRANCHPARTY.STATUS = 'A'
AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE)
AND NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BRANCHCA.CLASS_CODE IN ('BANK_BRANCH'
, 'CLEARINGHOUSE_BRANCH')
AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND (BRANCHCA.STATUS = 'A' OR BRANCHCA.STATUS IS NULL)
AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID AND BANKORGPROFILE.PARTY_ID = BRREL.OBJECT_ID
AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF'
AND BRREL.STATUS = 'A'
AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE = 'ORGANIZATION'
AND BRREL.SUBJECT_ID = BRANCHPARTY.PARTY_ID
AND BRANCHTYPECA.CLASS_CATEGORY(+) = 'BANK_BRANCH_TYPE'
AND BRANCHTYPECA.PRIMARY_FLAG(+) = 'Y'
AND BRANCHTYPECA.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND BRANCHTYPECA.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID
AND BRANCHTYPECA.STATUS(+) = 'A'
AND BRANCHCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND BRANCHCP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID
AND BRANCHCP.CONTACT_POINT_TYPE(+) = 'EFT'
AND BRANCHCP.STATUS(+) = 'A'
AND EDICP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND EDICP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID
AND EDICP.CONTACT_POINT_TYPE(+) = 'EDI'
AND EDICP.STATUS(+) = 'A'

Columns