EMPL_LOC_ESP_VW

(SQL View)
 Index Back

Employee Labor Agreemnt VW ESP


SELECT A.EMPLID , A.EFFDT , A.CONTRACT_NUM , B.SETID , A.LOCATION , B.DESCR , B.ADDR_FIELD1 %Concat ' ' %Concat B.ADDRESS1 %Concat ', ' %Concat B.ADDR_FIELD2 %Concat '. ' %Concat B.ADDR_FIELD3 %Concat ' ' %Concat B.NUM1 %Concat ' ' %Concat B.NUM2 , B.CITY , B.STATE FROM PS_JOB A , PS_LOCATION_TBL B WHERE A.SETID_LOCATION = B.SETID AND A.LOCATION = B.LOCATION AND B.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_LOCATION_TBL ED WHERE A.SETID_LOCATION = ED.SETID AND A.LOCATION = ED.LOCATION AND ED.EFFDT <= A.EFFDT) AND A.EFFSEQ= ( SELECT MAX(A_ED.EFFSEQ) FROM PS_JOB A_ED WHERE A.EMPLID=A_ED.EMPLID AND A.EFFDT=A_ED.EFFDT AND A.CONTRACT_NUM=A_ED.CONTRACT_NUM)

  • Parent record: EMPL_CTG_L2

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

    Default Value: %date

    3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    4 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_HR.SETID

    Prompt Table: SETID_TBL

    5 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 ADDRESS100 Character(100) VARCHAR2(100) NOT NULL Address 1
    8 CITY Character(30) VARCHAR2(30) NOT NULL City
    9 STATE Character(6) VARCHAR2(6) NOT NULL State