SCC_ADDR_TYP_VW(SQL View) |
Index Back |
|---|---|
Person Address TypeMaximum effective dated address per type for a person without regard to active / inactive status. With Address Type Order by Sequence for proper priority sorting. Address Subrecord needed for EO Address processing. SEQNO needed to supress Tools Duplicate Key Error. EFFDT needed to copy last entered address to new on row insert. |
| SELECT A.EMPLID , B.ORDER_BY_SEQ , 1 , A.ADDRESS_TYPE , A.EFFDT , A.EFF_STATUS , %subrec(ADDRESS_SBR,A) , A.ADDRESS1_AC , A.ADDRESS2_AC , A.ADDRESS3_AC , A.CITY_AC , A.REG_REGION FROM PS_HCR_PER_ADDR_I A , PS_HCR_ADDR_TYP_I B WHERE A.ADDRESS_TYPE = B.ADDRESS_TYPE AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_HCR_PER_ADDR_I A1 WHERE A.EMPLID = A1.EMPLID AND A.ADDRESS_TYPE = A1.ADDRESS_TYPE) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | ORDER_BY_SEQ |
Number(2,0) | SMALLINT NOT NULL | Order by Sequence |
| 3 | SEQNO |
Number(5,0) | INTEGER NOT NULL | Sequence Number |
| 4 | ADDRESS_TYPE |
Character(4) | VARCHAR2(4) NOT NULL |
Address Type
Default Value: INSTALLATION_CC.SCC_ADDRESS_TYPE Prompt Table: HCR_ADDR_TYP_I |
| 5 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 6 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive Default Value: A |
| 7 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: SCC_CNT_ADFMTVW |
| 8 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 9 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 10 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 11 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 12 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 13 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
| 14 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
| 15 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat Reference WW=House Trailer Reference |
| 16 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
| 17 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
| 18 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
| 19 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 20 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: STATE_TBL |
| 21 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 22 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 23 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |
| 24 | ADDRESS1_AC | Character(55) | VARCHAR2(55) NOT NULL | Alternate Character Address 1 |
| 25 | ADDRESS2_AC | Character(55) | VARCHAR2(55) NOT NULL | Alternate Character Address 2 |
| 26 | ADDRESS3_AC | Character(55) | VARCHAR2(55) NOT NULL | Alternate Character Address 3 |
| 27 | CITY_AC | Character(30) | VARCHAR2(30) NOT NULL | Alternate Character City |
| 28 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |