VIEW: APPS.CE_BANK_BRANCHES_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'