FED_CST_ADDR_VW

(SQL View)
 Index Back

FEDREG New Cust Address


SELECT A.DUNS_NUMBER , A.CCR_DUNS_PLUS4 , A.CCR_ADDR_TYPE , 0 , %DateNull , 'A' , ' ' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , %Substring(H.FULLNAME, 1, 40) , %Substring(H.FULLNAME, 41, 20) , A.COUNTRY , A.ADDRESS1 , A.ADDRESS2 , ' ' , ' ' , %Substring(A.ADDRESS5, 1, 30) , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , A.STATE , %Substring(A.ADDRESS6, 1, 12) , ' ' , ' ' , ' ' FROM PS_CCR_VNDR_HDR H , PS_CCR_VNDR_ADDR A WHERE %Join(COMMON_KEYS, CCR_VNDR_HDR H, CCR_VNDR_ADDR A) AND H.SOURCE_SYSTEM = 'FED' AND H.FR_BUS_TYPE IN ('2','3') AND A.CCR_ADDR_TYPE IN ('CN','FE','AF')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DUNS_NUMBER Character(9) VARCHAR2(9) NOT NULL DUNS Number
2 CCR_DUNS_PLUS4 Character(4) VARCHAR2(4) NOT NULL DUNS Suffix
3 CCR_ADDR_TYPE Character(3) VARCHAR2(3) NOT NULL SAM Address Type
4 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
5 EFFDT Date(10) DATE Effective Date
6 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 BILL_TO_ADDR Character(1) VARCHAR2(1) NOT NULL Bill To Address
9 SHIP_TO_ADDR Character(1) VARCHAR2(1) NOT NULL Ship To Address
10 SOLD_TO_ADDR Character(1) VARCHAR2(1) NOT NULL Sold To Address
11 CRSPD_TO_ADDR Character(1) VARCHAR2(1) NOT NULL Correspondence Address
12 PRIMARY_BILL_TO Character(1) VARCHAR2(1) NOT NULL Primary Bill To
13 PRIMARY_SHIP_TO Character(1) VARCHAR2(1) NOT NULL Primary Ship To
14 PRIMARY_SOLD_TO Character(1) VARCHAR2(1) NOT NULL Primary Sold To
15 ALT_NAME1 Character(40) VARCHAR2(40) NOT NULL Alternate Name 1
16 ALT_NAME2 Character(40) VARCHAR2(40) NOT NULL Alternate Name 2
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

18 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
19 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
20 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
21 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
22 CITY Character(30) VARCHAR2(30) NOT NULL City
23 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
24 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
25 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
26 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
27 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
28 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
29 COUNTY Character(30) VARCHAR2(30) NOT NULL County
30 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

31 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
32 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
33 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

34 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID