HR_COMP_DESCR_L

(SQL View)
 Index Back

Lang View for Comp Descrs

This Lang view is used to get the descriptions of important fields used in Compensation from latest job data row for all employees.

SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JCLANG.LANGUAGE_CD , %Coalesce(( SELECT CMPNY.DESCR FROM PS_COMPNY_TBL_LANG CMPNY WHERE JOB.COMPANY = CMPNY.COMPANY AND CMPNY.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND CMPNY.EFFDT = ( SELECT MAX(CMPNY1.EFFDT) FROM PS_COMPNY_TBL_LANG CMPNY1 WHERE CMPNY1.COMPANY = CMPNY.COMPANY AND CMPNY1.EFFDT <= (JOB.EFFDT))) ,' ') , %Coalesce(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JOB.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CURRENTDATEIN)),' ') , %Coalesce(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_LANG LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CURRENTDATEIN) AND LOCN.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT11.EFFDT <= (JOB.EFFDT))) , %Coalesce((SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT21.FIELDNAME AND XLT21.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT2.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT PGRP.DESCR FROM PS_PAYGROUP_LANG PGRP WHERE PGRP.COMPANY = JOB.COMPANY AND PGRP.PAYGROUP = JOB.PAYGROUP AND PGRP.EFFDT = ( SELECT MAX(PGRP1.EFFDT) FROM PS_PAYGROUP_LANG PGRP1 WHERE PGRP1.COMPANY = PGRP.COMPANY AND PGRP1.PAYGROUP = PGRP.PAYGROUP AND PGRP1.EFFDT <= (JOB.EFFDT))AND PGRP.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , ( SELECT XLT3.XLATLONGNAME FROM PSXLATITEMLANG XLT3 WHERE XLT3.FIELDNAME = 'SEX' AND XLT3.FIELDVALUE = PER_D.SEX AND XLT3.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT3.EFFDT = ( SELECT MAX(XLT31.EFFDT) FROM PSXLATITEMLANG XLT31 WHERE XLT31.FIELDNAME = XLT3.FIELDNAME AND XLT31.FIELDVALUE = XLT3.FIELDVALUE AND XLT31.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT31.EFFDT <= (JOB.EFFDT)) ), ( SELECT XLT4.XLATLONGNAME FROM PSXLATITEMLANG XLT4 WHERE XLT4.FIELDNAME = 'MAR_STATUS' AND XLT4.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT4.FIELDVALUE = PER_D.MAR_STATUS AND XLT4.EFFDT = ( SELECT MAX(XLT41.EFFDT) FROM PSXLATITEMLANG XLT41 WHERE XLT41.FIELDNAME = XLT4.FIELDNAME AND XLT41.FIELDVALUE = XLT4.FIELDVALUE AND XLT41.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT41.EFFDT <= (JOB.EFFDT)) ) , CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) < 25 THEN '<25' ELSE CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) BETWEEN 25 AND 34 THEN '25-34' ELSE CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) BETWEEN 35 AND 44 THEN '35-44' ELSE CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) BETWEEN 45 AND 54 THEN '45-54' ELSE CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) BETWEEN 55 AND 64 THEN '55-64' ELSE CASE WHEN (%Round((%datediff(PER_D.birthdate,%currentdatein))/365 ,0)) >= 65 THEN '65+' ELSE ( SELECT MSG1.MESSAGE_TEXT FROM PSMSGCATLANG MSG1 WHERE MSG1.MESSAGE_SET_NBR = 1000 AND MSG1.MESSAGE_NBR = 30165 AND MSG1.LANGUAGE_CD = JCLANG.LANGUAGE_CD) END END END END END END , %Coalesce(( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_LNG ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.LANGUAGE_CD = ACT1.LANGUAGE_CD AND ACT1.EFFDT <= %CurrentDateIn) AND ACT.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSN_DATA_LANG A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CURRENTDATEIN) AND A6.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') ,( SELECT XLT5.XLATLONGNAME FROM PSXLATITEMLANG XLT5 WHERE XLT5.FIELDNAME = 'HR_STATUS' AND XLT5.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT5.FIELDVALUE = JOB.HR_STATUS AND XLT5.EFFDT = ( SELECT MAX(XLT51.EFFDT) FROM PSXLATITEMLANG XLT51 WHERE XLT51.FIELDNAME = XLT5.FIELDNAME AND XLT51.FIELDVALUE = XLT5.FIELDVALUE AND XLT51.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND XLT51.EFFDT <= %CurrentDateIn)) FROM PS_JOB JOB, PS_JOBCODE_TBL JCODE, PS_JOBCODE_LANG JCLANG, PS_PERSONAL_DATA PER_D WHERE JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, %CurrentDateIn) AND JCLANG.JOBCODE = JCODE.JOBCODE AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT AND JOB.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOB X WHERE X.EMPLID = JOB.EMPLID AND X.EMPL_RCD = JOB.EMPL_RCD AND X.EFFSEQ = JOB.EFFSEQ AND X.EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(A1.EFFSEQ) FROM PS_JOB A1 WHERE A1.EMPLID = JOB.EMPLID AND A1.EFFDT = JOB.EFFDT AND A1.EMPL_RCD = JOB.EMPL_RCD) AND JOB.EMPLID = PER_D.EMPLID AND JOB.PER_ORG IN ('EMP','CWR','POI' )

  • Related Language Record for HR_COMP_DESCRS

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 COMPANY_NAME Character(30) VARCHAR2(30) NOT NULL Company Name
    7 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    8 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    9 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    10 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    11 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    14 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    15 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    16 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    17 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    18 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
    19 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    20 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    21 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    22 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    23 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50