VIEW: APPS.CE_BANKS_V

Source

View Text - Preformatted

SELECT BankParty.ROWID ROW_ID, BankOrgProfile.HOME_COUNTRY Home_Country, BankParty.PARTY_ID Bank_Party_Id, BankParty.PARTY_NAME Bank_Name, BankParty.ORGANIZATION_NAME_PHONETIC Bank_Name_Alt, BankParty.KNOWN_AS Short_Bank_Name, BankOrgProfile.BANK_OR_BRANCH_NUMBER Bank_Number, BankCA.start_date_active Start_Date, BankCA.end_date_active End_Date, BankParty.address1 Address_Line1, BankParty.address2 Address_Line2, BankParty.address3 Address_Line3, BankParty.address4 Address_Line4, BankParty.city City, BankParty.state State, BankParty.province Province, BankParty.postal_code Zip, BankParty.country Country, BankCA.class_code Bank_Institution_Type, BankParty.mission_statement Description, BankParty.PARTY_ID, HL.ADDRESS1, HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY, HL.STATE, HL.COUNTY, HL.POSTAL_CODE, HL.COUNTRY, HL.PROVINCE, HL.LOCATION_ID, BankParty.JGZZ_FISCAL_CODE From HZ_PARTIES BankParty, HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_CODE_ASSIGNMENTS BankCA, HZ_PARTY_SITES PS, HZ_LOCATIONS HL WHERE BankParty.PARTY_TYPE = 'ORGANIZATION' And NVL(BankParty.status, 'A') = 'A' And BankParty.PARTY_ID = BankOrgProfile.PARTY_ID And 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.OWNER_TABLE_ID = BankParty.PARTY_ID And NVL(BankCA.STATUS, 'A') = 'A' AND BankParty.PARTY_ID = PS.PARTY_ID (+) AND PS.IDENTIFYING_ADDRESS_FLAG (+) = 'Y' AND PS.LOCATION_ID = HL.LOCATION_ID (+)

View Text - HTML Formatted

SELECT BANKPARTY.ROWID ROW_ID
, BANKORGPROFILE.HOME_COUNTRY HOME_COUNTRY
, BANKPARTY.PARTY_ID BANK_PARTY_ID
, BANKPARTY.PARTY_NAME BANK_NAME
, BANKPARTY.ORGANIZATION_NAME_PHONETIC BANK_NAME_ALT
, BANKPARTY.KNOWN_AS SHORT_BANK_NAME
, BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, BANKCA.START_DATE_ACTIVE START_DATE
, BANKCA.END_DATE_ACTIVE END_DATE
, BANKPARTY.ADDRESS1 ADDRESS_LINE1
, BANKPARTY.ADDRESS2 ADDRESS_LINE2
, BANKPARTY.ADDRESS3 ADDRESS_LINE3
, BANKPARTY.ADDRESS4 ADDRESS_LINE4
, BANKPARTY.CITY CITY
, BANKPARTY.STATE STATE
, BANKPARTY.PROVINCE PROVINCE
, BANKPARTY.POSTAL_CODE ZIP
, BANKPARTY.COUNTRY COUNTRY
, BANKCA.CLASS_CODE BANK_INSTITUTION_TYPE
, BANKPARTY.MISSION_STATEMENT DESCRIPTION
, BANKPARTY.PARTY_ID
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.STATE
, HL.COUNTY
, HL.POSTAL_CODE
, HL.COUNTRY
, HL.PROVINCE
, HL.LOCATION_ID
, BANKPARTY.JGZZ_FISCAL_CODE
FROM HZ_PARTIES BANKPARTY
, HZ_ORGANIZATION_PROFILES BANKORGPROFILE
, HZ_CODE_ASSIGNMENTS BANKCA
, HZ_PARTY_SITES PS
, HZ_LOCATIONS HL
WHERE BANKPARTY.PARTY_TYPE = 'ORGANIZATION'
AND NVL(BANKPARTY.STATUS
, 'A') = 'A'
AND BANKPARTY.PARTY_ID = BANKORGPROFILE.PARTY_ID
AND 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.OWNER_TABLE_ID = BANKPARTY.PARTY_ID
AND NVL(BANKCA.STATUS
, 'A') = 'A'
AND BANKPARTY.PARTY_ID = PS.PARTY_ID (+)
AND PS.IDENTIFYING_ADDRESS_FLAG (+) = 'Y'
AND PS.LOCATION_ID = HL.LOCATION_ID (+)