EMPLIDEMPL_RCDEFFDTDefault Value: %date
EFFSEQLANGUAGE_CDHR_PG_CURR_LNG2(SQL View) |
Index Back |
|---|---|
Lang View for Current HC - HR |
| SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , 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, ( 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)), ( 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.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 L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O) |
| # | 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_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Company Descr |
| 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 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 15 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
| 16 | ESTAB_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 17 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 18 | DESCR4 | Character(30) | VARCHAR2(30) NOT NULL | Descr 4 |
| 19 | DESCR5 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
| 20 | DESCR100 | Character(100) | VARCHAR2(100) NOT NULL | Length 100 Description |
| 21 | DESCR100_2 | Character(100) | VARCHAR2(100) NOT NULL | Description |
| 22 | DESCR6 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
| 23 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 24 | SUPERVISOR_NAME | Character(50) | VARCHAR2(50) NOT NULL | Supervisor Name |
| 25 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 26 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |