LM_CURR_JOB_VW(SQL View) |
Index Back |
|---|---|
Person Attribute DataContains Employment Data |
| SELECT LM_PERSON_ID , LM_EMPL_RCD , EFFDT , LM_END_EFFDT , LM_LE_ID , LM_HIRE_DT , LM_REHIRE_DT , LM_SENIORITY_DATE , LM_SERVICE_DT , LM_PROF_EXPER_DT , LM_LAST_VERIF_DT , LM_BUSINESS_TITLE , LM_SECURITY_CLEAR , LM_JOB_REPORTING , LM_TENURE_ACCR_FLG , LM_FTE_TENURE , LM_EG_GROUP , LM_FTE_FLX_SRVC , LM_ORGANIZATION_ID , LM_ACTIVE , LM_JOBCODE_ID , LM_JOB_TITLE , LM_POSITION_NBR , BUSINESS_UNIT , CURRENCY_CD , LM_EMPL_TYPE , LM_FULL_PART_TIME , LM_JOB_INDICATOR , LM_PO_ID , LM_TU_POOL_ID , LM_PER_ORG , LM_MANAGER_ID , LM_LABOR_AGREEMENT , LM_HR_COMPANY , LM_EMPL_CTG , LM_EMPL_CTG_L1 , LM_EMPL_CTG_L2 , LM_REG_REGION , LM_SETID_LBR_AGR ,LM_LOCATION_ID , LM_ROW_ADDED_DTTM , LM_ROW_ADDED_OPRID , LM_ROW_MAINT_DTTM , LM_ROW_MAINT_OPRID FROM PS_LM_PERSON_ATTRB JOB WHERE JOB.LM_EMPL_RCD = ( SELECT MIN(JOB2.LM_EMPL_RCD) FROM PS_LM_PERSON_ATTRB JOB2 WHERE JOB2.LM_PERSON_ID = JOB.LM_PERSON_ID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB2.LM_PERSON_ID AND LM_EMPL_RCD = JOB2.LM_EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB2.LM_END_EFFDT >= %CurrentDateIn AND ((JOB2.LM_ACTIVE = 'Y' AND JOB2.LM_JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_LM_PERSON_ATTRB JOB3 WHERE JOB3.LM_PERSON_ID = JOB2.LM_PERSON_ID AND JOB3.LM_EMPL_RCD <> JOB2.LM_EMPL_RCD AND JOB3.LM_END_EFFDT >= %CurrentDateIn AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = JOB3.LM_PERSON_ID AND LM_EMPL_RCD = JOB3.LM_EMPL_RCD AND EFFDT <= %CurrentDateIn AND LM_END_EFFDT >= %CurrentDateIn) AND ((JOB3.LM_ACTIVE = 'Y' AND (JOB2.LM_ACTIVE <> 'Y' OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P'))) OR (JOB3.LM_JOB_INDICATOR = 'P' AND JOB2.LM_JOB_INDICATOR <> 'P' AND JOB2.LM_ACTIVE <> 'Y'))))) AND JOB.LM_END_EFFDT >= %CurrentDateIn AND ((JOB.LM_ACTIVE = 'Y') OR (JOB.LM_ACTIVE = 'N' AND NOT EXISTS ( SELECT 'x' FROM PS_LM_PERSON_ATTRB JOB4 WHERE JOB4.LM_PERSON_ID = JOB.LM_PERSON_ID AND JOB4.EFFDT = ( SELECT MAX(JOB5.EFFDT) FROM PS_LM_PERSON_ATTRB JOB5 WHERE JOB5.LM_PERSON_ID = JOB.LM_PERSON_ID AND JOB5.LM_EMPL_RCD = JOB4.LM_EMPL_RCD AND JOB5.EFFDT <= %CurrentDateIn) AND JOB4.LM_ACTIVE = 'Y'))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  LM_PERSON_ID |
Number(15,0) | DECIMAL(15) NOT NULL | ELM Person ID |
| 2 | LM_EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL |
Empl Record umber
Default Value: 0 |
| 3 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
| 4 | LM_END_EFFDT | Date(10) | DATE | The end effective date, automatically maintained, there's a doc on how this works, so read it |
| 5 | LM_LE_ID | Number(10,0) | DECIMAL(10) NOT NULL | Learning Environment ID - System Generated number |
| 6 | LM_HIRE_DT | Date(10) | DATE | Hire Date |
| 7 | LM_REHIRE_DT | Date(10) | DATE | Rehire Date Field currently not used, reserved for future functionality |
| 8 | LM_SENIORITY_DATE | Date(10) | DATE | Seniority Date Field currently not used, reserved for future functionality |
| 9 | LM_SERVICE_DT | Date(10) | DATE | Service Date Field currently not used, reserved for future functionality |
| 10 | LM_PROF_EXPER_DT | Date(10) | DATE | Professional Experience Date Field currently not used, reserved for future functionality |
| 11 | LM_LAST_VERIF_DT | Date(10) | DATE | Last Verification Date Field currently not used, reserved for future functionality |
| 12 | LM_BUSINESS_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Business Title Field currently not used, reserved for future functionality |
| 13 | LM_SECURITY_CLEAR | Character(1) | VARCHAR2(1) NOT NULL |
Security Clearance
Field currently not used, reserved for future functionality
0=Not Required 1=Classified 2=Secret 3=Top Secret 4=Sensitive Compartmented Info 5=Q Sensitive 6=Q Non-Sensitive 7=L (Atomic Energy Act) 8=Other |
| 14 | LM_JOB_REPORTING | Character(1) | VARCHAR2(1) NOT NULL | Job Reporting Required Field currently not used, reserved for future functionality |
| 15 | LM_TENURE_ACCR_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Accrue Tenure Services
Field currently not used, reserved for future functionality
N=Do Not Accrue Y=Accrue Tenure |
| 16 | LM_FTE_TENURE | Number(4,2) | DECIMAL(3,2) NOT NULL | FTE for Tenure Accrual Field currently not used, reserved for future functionality |
| 17 | LM_EG_GROUP | Character(6) | VARCHAR2(6) NOT NULL | Service Calculation Group Field currently not used, reserved for future functionality |
| 18 | LM_FTE_FLX_SRVC | Number(4,2) | DECIMAL(3,2) NOT NULL | FTE for Flex Service Accrual Field currently not used, reserved for future functionality |
| 19 | LM_ORGANIZATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Organization ID |
| 20 | LM_ACTIVE | Character(1) | VARCHAR2(1) NOT NULL |
Active
N=Inactive Y=Active |
| 21 | LM_JOBCODE_ID | Number(15,0) | DECIMAL(15) NOT NULL | JobCode ID |
| 22 | LM_JOB_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 23 | LM_POSITION_NBR | Character(15) | VARCHAR2(15) NOT NULL | Position Number |
| 24 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 25 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 26 | LM_EMPL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Employee Type
E=Exception Hourly H=Hourly N=Not Applicable S=Salaried |
| 27 | LM_FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full Time/Part Time
F=Full-Time P=Part-Time |
| 28 | LM_JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
Primary Job Indicator
N=Not Applicable P=Primary Job S=Secondary Job |
| 29 | LM_PO_ID | Number(10,0) | DECIMAL(10) NOT NULL | Purchase Order Id. Unique key for purchase orders. |
| 30 | LM_TU_POOL_ID | Number(10,0) | DECIMAL(10) NOT NULL | Training Unit Pool No. Unique key for Training Unit Pool Record. |
| 31 | LM_PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Persons Organizational Relationship. Brought over from HCM.
CWR=Contingent Worker EMP=Employee POI=Person Of Interest |
| 32 | LM_MANAGER_ID | Number(15,0) | DECIMAL(15) NOT NULL | Manager ID |
| 33 | LM_LABOR_AGREEMENT | Character(6) | VARCHAR2(6) NOT NULL | Labor Agreement |
| 34 | LM_HR_COMPANY | Character(3) | VARCHAR2(3) NOT NULL | HR's Company ID brought over during synchronization. |
| 35 | LM_EMPL_CTG | Character(6) | VARCHAR2(6) NOT NULL | Employee Category |
| 36 | LM_EMPL_CTG_L1 | Character(6) | VARCHAR2(6) NOT NULL | Employee Subcategory |
| 37 | LM_EMPL_CTG_L2 | Character(6) | VARCHAR2(6) NOT NULL | Employee Subcategory 2 |
| 38 | LM_REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
| 39 | LM_SETID_LBR_AGR | Character(5) | VARCHAR2(5) NOT NULL | Labor Agreement SetID |
| 40 | LM_LOCATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Location Id |
| 41 | LM_ROW_ADDED_DTTM | DateTime(26) | TIMESTAMP | Datetime Added |
| 42 | LM_ROW_ADDED_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Added By |
| 43 | LM_ROW_MAINT_DTTM | DateTime(26) | TIMESTAMP | Row Maintainted DateTime |
| 44 | LM_ROW_MAINT_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Row Maintained Oprid |