LM_PERSON_IDLM_EMPL_RCDEFFDTDefault Value: %date
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
N=Not Applicable
P=Primary Job
S=Secondary Job
CWR=Contingent Worker
EMP=Employee
POI=Person Of Interest
LM_PRIM_JOB_VW(SQL View) |
Index Back |
|---|---|
Primary job row.Returns the most current primary job row from LM_PERSON_ATTRB. In ELM Version 9, LM_PERSON_ATTRB mirrors LM_PERSON_JOB without EFFSEQ. Note that a person may or may not have EMPL_RCD = 0. |
| SELECT LM_PERSON_ID , LM_EMPL_RCD , EFFDT , LM_LE_ID , LM_ORGANIZATION_ID , LM_JOBCODE_ID , LM_JOB_TITLE , LM_POSITION_NBR , BUSINESS_UNIT , CURRENCY_CD , LM_EMPL_TYPE , LM_JOB_INDICATOR , LM_PO_ID , LM_TU_POOL_ID , LM_PER_ORG , LM_MANAGER_ID , LM_HR_COMPANY FROM PS_LM_PERSON_ATTRB ATTRB WHERE ATTRB.LM_EMPL_RCD = ( SELECT MIN(ATTRB2.LM_EMPL_RCD) FROM PS_LM_PERSON_ATTRB ATTRB2 WHERE ATTRB2.LM_PERSON_ID = ATTRB.LM_PERSON_ID AND ATTRB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = ATTRB2.LM_PERSON_ID AND LM_EMPL_RCD = ATTRB2.LM_EMPL_RCD AND EFFDT <= ATTRB.EFFDT) AND ((ATTRB2.LM_ACTIVE = 'Y' AND ATTRB2.LM_JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_LM_PERSON_ATTRB ATTRB3 WHERE ATTRB3.LM_PERSON_ID = ATTRB2.LM_PERSON_ID AND ATTRB3.LM_EMPL_RCD <> ATTRB2.LM_EMPL_RCD AND ATTRB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = ATTRB3.LM_PERSON_ID AND LM_EMPL_RCD = ATTRB3.LM_EMPL_RCD AND EFFDT <= ATTRB.EFFDT) AND ((ATTRB3.LM_ACTIVE = 'Y' AND (ATTRB2.LM_ACTIVE <> 'Y' OR (ATTRB3.LM_JOB_INDICATOR = 'P' AND ATTRB2.LM_JOB_INDICATOR <> 'P'))) OR (ATTRB3.LM_JOB_INDICATOR = 'P' AND ATTRB2.LM_JOB_INDICATOR <> 'P' AND ATTRB2.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 |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | LM_LE_ID | Number(10,0) | DECIMAL(10) NOT NULL | Learning Environment ID - System Generated number |
| 5 | LM_ORGANIZATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Organization ID |
| 6 | LM_JOBCODE_ID | Number(15,0) | DECIMAL(15) NOT NULL | JobCode ID |
| 7 | LM_JOB_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 8 | LM_POSITION_NBR | Character(15) | VARCHAR2(15) NOT NULL | Position Number |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 11 | LM_EMPL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Employee Type
E=Exception Hourly H=Hourly N=Not Applicable S=Salaried |
| 12 | LM_JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
Primary Job Indicator
N=Not Applicable P=Primary Job S=Secondary Job |
| 13 | LM_PO_ID | Number(10,0) | DECIMAL(10) NOT NULL | Purchase Order Id. Unique key for purchase orders. |
| 14 | LM_TU_POOL_ID | Number(10,0) | DECIMAL(10) NOT NULL | Training Unit Pool No. Unique key for Training Unit Pool Record. |
| 15 | 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 |
| 16 | LM_MANAGER_ID | Number(15,0) | DECIMAL(15) NOT NULL | Manager ID |
| 17 | LM_HR_COMPANY | Character(3) | VARCHAR2(3) NOT NULL | HR's Company ID brought over during synchronization. |