HR_PG_MVMT_LNG2

(SQL View)
 Index Back

Lang View for HC Movement - HR

This view is used as the language view for HR Admin's Headcount Movement PG.

SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , '0' , L.LANGUAGE_CD , ( SELECT CMPNY.DESCR FROM PS_COMPNY_TBL_LANG CMPNY WHERE JOB.COMPANY = CMPNY.COMPANY AND CMPNY.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(COMPNY_TBL_LANG CMPNY1, CMPNY, %CurrentDateIn)), ( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JOB.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT GPPG.DESCR FROM PS_GP_PYGRP_LANG GPPG WHERE GPPG.GP_PAYGROUP = JOB.GP_PAYGROUP AND GPPG.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT PGRP.DESCR FROM PS_PAYGROUP_LANG PGRP WHERE PGRP.COMPANY = JOB.COMPANY AND PGRP.PAYGROUP = JOB.PAYGROUP AND PGRP.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PAYGROUP_LANG PGRP1, PGRP, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT XLT3.XLATLONGNAME FROM PSXLATITEMLANG XLT3 WHERE XLT3.FIELDNAME = 'SEX' AND XLT3.FIELDVALUE = PER_D.SEX AND XLT3.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT31, XLT3, %CurrentDateIn)), ( SELECT XLT4.XLATLONGNAME FROM PSXLATITEMLANG XLT4 WHERE XLT4.FIELDNAME = 'MAR_STATUS' AND XLT4.FIELDVALUE = PER_D.MAR_STATUS AND XLT4.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT41, XLT4, %CurrentDateIn)), 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 = L.LANGUAGE_CD) END END END END END END, CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END, ( 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 = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT XLT5.XLATLONGNAME FROM PSXLATITEMLANG XLT5 WHERE XLT5.FIELDNAME = 'HR_STATUS' AND XLT5.FIELDVALUE = JOB.HR_STATUS AND XLT5.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT51, XLT5, %CurrentDateIn)) FROM PS_JOB JOB, PS_PERSONAL_DATA PER_D, PSLANGUAGES L WHERE JOB.EMPLID = PER_D.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , '1' , L.LANGUAGE_CD , ( SELECT CMPNY.DESCR FROM PS_COMPNY_TBL_LANG CMPNY WHERE JOB.COMPANY = CMPNY.COMPANY AND CMPNY.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(COMPNY_TBL_LANG CMPNY1, CMPNY, %CurrentDateIn)), ( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JOB.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT GPPG.DESCR FROM PS_GP_PYGRP_LANG GPPG WHERE GPPG.GP_PAYGROUP = JOB.GP_PAYGROUP AND GPPG.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT PGRP.DESCR FROM PS_PAYGROUP_LANG PGRP WHERE PGRP.COMPANY = JOB.COMPANY AND PGRP.PAYGROUP = JOB.PAYGROUP AND PGRP.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PAYGROUP_LANG PGRP1, PGRP, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT XLT3.XLATLONGNAME FROM PSXLATITEMLANG XLT3 WHERE XLT3.FIELDNAME = 'SEX' AND XLT3.FIELDVALUE = PER_D.SEX AND XLT3.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT31, XLT3, %CurrentDateIn)), ( SELECT XLT4.XLATLONGNAME FROM PSXLATITEMLANG XLT4 WHERE XLT4.FIELDNAME = 'MAR_STATUS' AND XLT4.FIELDVALUE = PER_D.MAR_STATUS AND XLT4.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT41, XLT4, %CurrentDateIn)), 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 = L.LANGUAGE_CD) END END END END END END, CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30163 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END, ( 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 = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT XLT5.XLATLONGNAME FROM PSXLATITEMLANG XLT5 WHERE XLT5.FIELDNAME = 'HR_STATUS' AND XLT5.FIELDVALUE = JOB.HR_STATUS AND XLT5.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT51, XLT5, %CurrentDateIn)) FROM PS_JOB JOB, PS_PERSONAL_DATA PER_D, PSLANGUAGES L WHERE JOB.EMPLID = PER_D.EMPLID AND (JOB.ACTION = 'XFR' OR (JOB.ACTION = 'POS' AND JOB.ACTION_REASON = 'XFR')) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O)

  • Related Language Record for HR_PG_MVMT_HR_I

    • # 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 DIRVALUEFLAG Character(1) VARCHAR2(1) NOT NULL Directory Value?
    6 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    7 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    8 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    9 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    10 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    11 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    12 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    15 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    16 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    17 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    19 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    20 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    21 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
    22 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    23 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    24 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    25 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    26 NAME3 Character(40) VARCHAR2(40) NOT NULL Name 3
    27 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50