SCC_PERADR_AFLT

(SQL View)
 Index Back

Addresses Affiliation Filtered

Filter persons based on whether their affiliation is in the affiliation routing table. This is to enable content based routing for the full sync.

SELECT A.EMPLID ,A.ADDRESS_TYPE ,A.EFFDT ,A.EFF_STATUS ,%subrec(ADDRESS_NPC_SBR,A) ,A.ADDRESS1_AC ,A.ADDRESS2_AC ,A.ADDRESS3_AC ,A.CITY_AC ,A.REG_REGION FROM PS_HCR_PER_ADDR_I A WHERE EXISTS ( SELECT 'X' FROM PS_SCC_AFL_PERSON AF , PS_SCC_AFL_ROUTING R WHERE AF.EMPLID = A.EMPLID AND R.INSTITUTION = AF.INSTITUTION AND R.SCC_AFL_CODE = AF.SCC_AFL_CODE )

  • Parent record: SCC_ADRTYP_AFLT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type
    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

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

    Default Value: A

    5 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Default Value: OPR_DEF_TBL_HR.COUNTRY

    Prompt Table: COUNTRY_TBL

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

    Prompt Table: STATE_TBL

    19 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    20 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    21 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
    22 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
    23 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
    24 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
    25 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
    26 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region