GPCH_BK_SIC_VW

(SQL View)
 Index Back

SIC Bank Definition view

View that lists all the SIC bank definitions that have some difference with the HR bank definition.

SELECT A.COUNTRY_CD , A.BANK_CD , A.BRANCH_EC_CD , A.EFFDT , A.EFF_STATUS , A.GPCH_BK_SIC_NEW_BC , A.DESCRSHORT , A.BANK_NM , %subrec(ADDRESS_SBR,A) , A.PHONE , A.BANK_NAME_AC , A.GPCH_UPDATE , %TrimSubstr(A.BANK_ID_CODE,1,11) FROM PS_GPCH_BK_SIC_UPD A WHERE ((A.BRANCH_EC_CD IN ( SELECT B.BRANCH_EC_CD FROM PS_BANK_BRANCH_TBL B WHERE A.COUNTRY_CD = B.COUNTRY_CD AND A.BANK_CD = B.BANK_CD AND A.BRANCH_EC_CD = B.BRANCH_EC_CD AND (A.EFF_STATUS <> B.EFF_STATUS OR A.DESCRSHORT <> B.DESCRSHORT OR %Substring(A.BANK_NM,1,30) <> B.DESCR OR A.ADDRESS1 <> B.ADDRESS1 OR A.ADDRESS4 <> B.ADDRESS4 OR (%TrimSubstr(A.POSTAL,1,1) %Concat %TrimSubstr(A.POSTAL,2,1) %Concat %TrimSubstr(A.POSTAL,3,1) %Concat %TrimSubstr(A.POSTAL,4,1) %Concat %TrimSubstr(A.POSTAL,5,1) %Concat %TrimSubstr(A.POSTAL,6,1) %Concat %TrimSubstr(A.POSTAL,7,1) %Concat %TrimSubstr(A.POSTAL,8,1) %Concat %TrimSubstr(A.POSTAL,9,1) %Concat %TrimSubstr(A.POSTAL,10,1) %Concat %TrimSubstr(A.POSTAL,11,1) %Concat %TrimSubstr(A.POSTAL,12,1) <> %TrimSubstr(B.POSTAL,1,1) %Concat %TrimSubstr(B.POSTAL,2,1) %Concat %TrimSubstr(B.POSTAL,3,1) %Concat %TrimSubstr(B.POSTAL,4,1) %Concat %TrimSubstr(B.POSTAL,5,1) %Concat %TrimSubstr(B.POSTAL,6,1) %Concat %TrimSubstr(B.POSTAL,7,1) %Concat %TrimSubstr(B.POSTAL,8,1) %Concat %TrimSubstr(B.POSTAL,9,1) %Concat %TrimSubstr(B.POSTAL,10,1) %Concat %TrimSubstr(B.POSTAL,11,1) %Concat %TrimSubstr(B.POSTAL,12,1) ) OR A.CITY <> B.CITY OR A.STATE <> B.STATE OR A.PHONE <> B.PHONE OR A.BANK_ID_CODE <> B.BANK_ID_CODE )) AND A.BRANCH_EC_CD <> ' ') OR (A.BRANCH_EC_CD = ' ' AND A.BANK_CD IN ( SELECT B.BANK_CD FROM PS_BANK_EC_TBL B WHERE A.COUNTRY_CD = B.COUNTRY_CD AND A.BANK_CD = B.BANK_CD AND (A.EFF_STATUS <> B.EFF_STATUS OR A.DESCRSHORT <> B.DESCRSHORT OR A.BANK_NM <> B.BANK_NM OR A.ADDRESS1 <> B.ADDRESS1 OR A.ADDRESS4 <> B.ADDRESS4 OR A.STATE <> B.STATE OR (%TrimSubstr(A.POSTAL,1,1) %Concat %TrimSubstr(A.POSTAL,2,1) %Concat %TrimSubstr(A.POSTAL,3,1) %Concat %TrimSubstr(A.POSTAL,4,1) %Concat %TrimSubstr(A.POSTAL,5,1) %Concat %TrimSubstr(A.POSTAL,6,1) %Concat %TrimSubstr(A.POSTAL,7,1) %Concat %TrimSubstr(A.POSTAL,8,1) %Concat %TrimSubstr(A.POSTAL,9,1) %Concat %TrimSubstr(A.POSTAL,10,1) %Concat %TrimSubstr(A.POSTAL,11,1) %Concat %TrimSubstr(A.POSTAL,12,1) <> %TrimSubstr(B.POSTAL,1,1) %Concat %TrimSubstr(B.POSTAL,2,1) %Concat %TrimSubstr(B.POSTAL,3,1) %Concat %TrimSubstr(B.POSTAL,4,1) %Concat %TrimSubstr(B.POSTAL,5,1) %Concat %TrimSubstr(B.POSTAL,6,1) %Concat %TrimSubstr(B.POSTAL,7,1) %Concat %TrimSubstr(B.POSTAL,8,1) %Concat %TrimSubstr(B.POSTAL,9,1) %Concat %TrimSubstr(B.POSTAL,10,1) %Concat %TrimSubstr(B.POSTAL,11,1) %Concat %TrimSubstr(B.POSTAL,12,1) ) OR A.CITY <> B.CITY OR A.PHONE <> B.PHONE OR A.BANK_ID_CODE <> B.BANK_ID_CODE)))) OR ((A.BRANCH_EC_CD NOT IN ( SELECT B.BRANCH_EC_CD FROM PS_BANK_BRANCH_TBL B WHERE A.COUNTRY_CD = B.COUNTRY_CD AND A.BANK_CD = B.BANK_CD AND A.BRANCH_EC_CD = B.BRANCH_EC_CD) AND A.BRANCH_EC_CD <> ' ') OR (A.BANK_CD NOT IN ( SELECT B.BANK_CD FROM PS_BANK_EC_TBL B WHERE A.COUNTRY_CD = B.COUNTRY_CD AND A.BANK_CD = B.BANK_CD) AND A.BRANCH_EC_CD = ' '))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COUNTRY_CD Character(3) VARCHAR2(3) NOT NULL Country Code
2 BANK_CD Character(11) VARCHAR2(11) NOT NULL Bank ID
3 BRANCH_EC_CD Character(17) VARCHAR2(17) NOT NULL Bank Branch ID
4 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

6 GPCH_BK_SIC_NEW_BC Character(5) VARCHAR2(5) NOT NULL New BC Number
7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
8 BANK_NM Character(40) VARCHAR2(40) NOT NULL Bank Name
9 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

10 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
11 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
12 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
13 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
14 CITY Character(30) VARCHAR2(30) NOT NULL City
15 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
16 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
17 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
18 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
19 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
20 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
21 COUNTY Character(30) VARCHAR2(30) NOT NULL County
22 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

23 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
24 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
25 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

26 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
27 BANK_NAME_AC Character(15) VARCHAR2(15) NOT NULL This field stores the bank name in an alternate character format
28 GPCH_UPDATE Character(1) VARCHAR2(1) NOT NULL Update

Y/N Table Edit

29 BANK_ID_CODE Character(11) VARCHAR2(11) NOT NULL Bank Identifier Code