LOCATION_WRK_VW

(SQL View)
 Index Back

Location Tbl - Worker View

This view is used to validate the Location Codes for RB_WORKER table. This view should be kept in sync. with the LOCATION_TBL record except for the SETID field which is replaced by SETID_LOCATION field. Selects only the current active rows.

SELECT A.SETID , A.LOCATION , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCR_AC , A.DESCRSHORT , A.BUILDING , A.FLOOR , A.SECTOR , A.JURISDICTION , A.ATTN_TO , %subrec(ADDRESS_SBR,a) , %subrec(BUS_PHONE_SBR,a) , A.SETID_SALARY , A.SAL_ADMIN_PLAN , A.LANG_CD , A.HOLIDAY_SCHEDULE , %subrec(LOC_TBLUSA_SBR,a) , %subrec(LOC_TBLCAN_SBR,a) , %subrec(WRKS_CNCL_SBR,a) , %subrec(LOC_TBLGER_SBR,a) , %subrec(LOC_TBLUK_SBR,a) , %subrec(LOC_TBL_FED_SBR,a) , %subrec(LOC_TBLESP_SBR,a) , A.LABEL_FORMAT_ID2 , A.LABEL_FORMAT_ID3 , A.USG_LBL_FORMAT_ID , A.COMMENTS_2000 , A.REG_REGION FROM PS_LOCATION_TBL A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_LOCATION_TBL B WHERE B.SETID = A.SETID AND B.LOCATION = A.LOCATION AND B.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

  • Related Language Record: LOCATION_WRKVLN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID

    Prompt Table: SP_SETID_NONVW

    2 LOCATION Character(15) VARCHAR2(15) NOT NULL This field stores the Location Code.
    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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Description
    7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    8 BUILDING Character(10) VARCHAR2(10) NOT NULL Building #
    9 FLOOR Character(10) VARCHAR2(10) NOT NULL Floor #
    10 SECTOR Character(10) VARCHAR2(10) NOT NULL Sector
    11 JURISDICTION Character(5) VARCHAR2(5) NOT NULL Jurisdiction
    12 ATTN_TO Character(30) VARCHAR2(30) NOT NULL Attention
    13 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

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

    Prompt Table: %EDIT_STATE

    27 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    28 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    29 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    Default Value: Y

    30 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL This field stores the Country Code values.
    31 PHONE Character(24) VARCHAR2(24) NOT NULL This field stores the Telephone number.
    32 EXTENSION Character(6) VARCHAR2(6) NOT NULL This field stores the Phone extension.
    33 FAX Character(24) VARCHAR2(24) NOT NULL This is the fax number field.
    34 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL SETID_SALARY field used in the HCM/ CRM sync Application Messages.
    35 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL SAL_ADMIN_PLAN field used in the HCM/ CRM sync Application Messages.
    36 LANG_CD Character(3) VARCHAR2(3) NOT NULL Language Code Field. This Field is imported into CRM DB from Human Resources Database.
    37 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL This is the holiday schedule field.
    38 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
    39 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
    40 CAN_CMA Character(2) VARCHAR2(2) NOT NULL Canadian Census Metropol Area
    01=Calgary, Alberta
    02=Edmonton, Alberta
    03=Halifax, Nova Scotia
    04=Montreal, Quebec
    05=Regina, Saskatchewa
    06=Toronto, Ontario
    07=Vancouver, British Columbia
    08=Winnipeg, Manitoba
    99=Not Applicable
    41 CAN_OEE_AREACD Character(2) VARCHAR2(2) NOT NULL CEC Management Area
    42 GEOLOC_CODE Character(10) VARCHAR2(10) NOT NULL Geographical Location Code
    43 OFFICE_TYPE Character(1) VARCHAR2(1) NOT NULL Office Type
    44 NCR_SW_CAN Character(1) VARCHAR2(1) NOT NULL National Capital Region

    Y/N Table Edit

    45 TBS_OFFICE_CD_CAN Character(10) VARCHAR2(10) NOT NULL Treasury Board Office Code
    46 WRKS_CNCL_ID_LCL Character(9) VARCHAR2(9) NOT NULL Works Council ID
    47 SPK_COMM_ID_GER Character(9) VARCHAR2(9) NOT NULL Spokesman Committee ID
    48 TARIFF_AREA_GER Character(3) VARCHAR2(3) NOT NULL Tarif Area
    49 TARIFF_GER Character(2) VARCHAR2(2) NOT NULL Tariff
    50 INDUST_INSP_ID_GER Character(4) VARCHAR2(4) NOT NULL Industrial Inspection ID
    51 NI_REPORT_SW_UK Character(1) VARCHAR2(1) NOT NULL Northern Ireland report Locati

    Y/N Table Edit

    52 GVT_GEOLOC_CD Character(9) VARCHAR2(9) NOT NULL Geog Location Code
    53 GVT_DESIG_AGENT Character(4) VARCHAR2(4) NOT NULL GVT Designated Agent Code
    54 SOC_SEC_WRK_CTR Character(3) VARCHAR2(3) NOT NULL Social Security Work Center
    55 MATRICULA_NBR Number(3,0) SMALLINT NOT NULL Matricula Number
    56 LABEL_FORMAT_ID2 Character(30) VARCHAR2(30) NOT NULL Carton Label Format ID
    57 LABEL_FORMAT_ID3 Character(30) VARCHAR2(30) NOT NULL Ship Container Label Format ID
    58 USG_LBL_FORMAT_ID Character(30) VARCHAR2(30) NOT NULL Item Usage Label Format
    59 COMMENTS_2000 Long Character(32700) CLOB Comment Text
    60 REG_REGION Character(5) VARCHAR2(5) NOT NULL REG_REGION field used in the HCM / CRM sync Application Messages.