This CE Bank Branch Details Report query displays bank and branch information with addresses and EFT/EDI details, offering a detailed view of organizational banking profiles and their electronic transaction capabilities.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Displays bank and branch details with addresses and EFT/EDI info
HOP1.HOME_COUNTRY BANK_HOME_COUNTRY,
HOP1.ORGANIZATION_NAME BANK_NAME,
HOP1.BANK_OR_BRANCH_NUMBER BANK_NUMBER,
HZ.PARTY_NAME BANK_BRANCH_NAME,
HOP2.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER,
HCA2.START_DATE_ACTIVE START_DATE,
HCA2.END_DATE_ACTIVE END_DATE,
HZ.ADDRESS1 ADDRESS_LINE1,
HZ.ADDRESS2 ADDRESS_LINE2,
HZ.ADDRESS3 ADDRESS_LINE3,
HZ.ADDRESS4 ADDRESS_LINE4,
HZ.CITY,
HZ.STATE ,
HZ.PROVINCE,
HZ.POSTAL_CODE ZIP,
HZ.COUNTRY,
HCA1.CLASS_CODE BANK_INSTITUTION_TYPE,
HCA3.CLASS_CODE BANK_BRANCH_TYPE,
HZ.MISSION_STATEMENT DESCRIPTION,
HCP1.EFT_SWIFT_CODE ,
HCP1.EFT_USER_NUMBER ,
HCP2.EDI_ID_NUMBER ,
HZ.CATEGORY_CODE,
HR.RELATIONSHIP_CODE
FROM
HZ_ORGANIZATION_PROFILES HOP1,
HZ_CODE_ASSIGNMENTS HCA1,
HZ_PARTIES HZ,
HZ_ORGANIZATION_PROFILES HOP2,
HZ_CODE_ASSIGNMENTS HCA2,
HZ_RELATIONSHIPS HR,
HZ_CODE_ASSIGNMENTS HCA3,
HZ_CONTACT_POINTS HCP1,
HZ_CONTACT_POINTS HCP2
WHERE
1 = 1
AND SYSDATE BETWEEN TRUNC(HOP1.EFFECTIVE_START_DATE)
AND NVL(TRUNC(HOP1.EFFECTIVE_END_DATE), SYSDATE + 1)
AND HCA1.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND HCA1.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
AND HCA1.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND (HCA1.STATUS = 'A' OR HCA1.STATUS IS NULL)
AND HCA1.OWNER_TABLE_ID = HOP1.PARTY_ID
AND HZ.PARTY_TYPE = 'ORGANIZATION'
AND HZ.STATUS = 'A'
AND HOP2.PARTY_ID = HZ.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HOP2.EFFECTIVE_START_DATE)
AND NVL(TRUNC(HOP2.EFFECTIVE_END_DATE), SYSDATE + 1)
AND HCA2.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND HCA2.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH')
AND HCA2.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND (HCA2.STATUS = 'A' OR HCA2.STATUS IS NULL)
AND HCA2.OWNER_TABLE_ID = HZ.PARTY_ID
AND HOP1.PARTY_ID = HR.OBJECT_ID
AND HR.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
AND HR.RELATIONSHIP_CODE = 'BRANCH_OF'
AND HR.STATUS = 'A'
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.SUBJECT_TYPE = 'ORGANIZATION'
AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TYPE = 'ORGANIZATION'
AND HR.SUBJECT_ID = HZ.PARTY_ID
AND HCA3.CLASS_CATEGORY(+) = 'BANK_BRANCH_TYPE'
AND HCA3.PRIMARY_FLAG(+) = 'Y'
AND HCA3.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCA3.OWNER_TABLE_ID(+) = HZ.PARTY_ID
AND HCA3.STATUS(+) = 'A'
AND HCP1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP1.OWNER_TABLE_ID(+) = HZ.PARTY_ID
AND HCP1.CONTACT_POINT_TYPE(+) = 'EFT'
AND HCP1.STATUS(+) = 'A'
AND HCP2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP2.OWNER_TABLE_ID(+) = HZ.PARTY_ID
AND HCP2.CONTACT_POINT_TYPE(+) = 'EDI'
AND HCP2.STATUS(+) = 'A';