LM_PEND_DIF_REQ

(SQL View)
 Index Back

Pending DIF Requests

This will list enrollments that are DIF enrolled and Hour Tracking Status is pending. Also, only the DIF activities are selected on this view.

SELECT E.LM_ENRLMT_ID , E.LM_PERSON_ID , E.LM_ENRL_DT , E.LM_ACT_ID , E.LM_CI_ID , E.LM_TRAINING_CLASS , E.LM_TRG_PLN_CTGY , B.LM_HR_COMPANY , B.BUSINESS_UNIT , B.LM_ORGANIZATION_ID , D.LM_DIF_BALANCE_HRS , D.LM_DIF_LIMIT_HRS , ( SELECT DIF.LM_DIF_PLANNED_HRS FROM PS_LM_DIF_PLAN_VW DIF WHERE DIF.LM_PERSON_ID = E.LM_PERSON_ID), ( SELECT SUM(LM_TOTAL_DUR_HRS) FROM PS_LM_ENR_LCDUR_VW WHERE LM_ENRLMT_ID = E.LM_ENRLMT_ID AND LM_HOUR_TYPE IN ('3', '4')) FROM PS_LM_ENRLMT E, PS_LM_PERSON_ATTRB B, PS_LM_DIFHIST_TBL D WHERE E.LM_ENRLMT_ID IN ( SELECT S.LM_ENRLMT_ID FROM PS_LM_ENR_LC_TBL S WHERE S.LM_HRS_TRACK_STTS IN ('1', '3')) AND E.LM_PERSON_ID = B.LM_PERSON_ID AND E.LM_ACT_DIF_FLG = 'Y' AND B.LM_PERSON_ID = D.LM_PERSON_ID AND %CurrentDateIn BETWEEN B.EFFDT AND B.LM_END_EFFDT AND B.LM_EMPL_RCD = D.LM_EMPL_RCD AND D.LM_AS_OF_DATE = ( SELECT MAX(X.LM_AS_OF_DATE) FROM PS_LM_DIFHIST_TBL X WHERE X.LM_PERSON_ID = D.LM_PERSON_ID AND X.LM_EMPL_RCD = D.LM_EMPL_RCD) UNION SELECT E.LM_ENRLMT_ID , E.LM_PERSON_ID , E.LM_ENRL_DT , E.LM_ACT_ID , E.LM_CI_ID , E.LM_TRAINING_CLASS , E.LM_TRG_PLN_CTGY , B.LM_HR_COMPANY , B.BUSINESS_UNIT , B.LM_ORGANIZATION_ID , 0 , 0 , 0 ,0 FROM PS_LM_ENRLMT E , PS_LM_PERSON_ATTRB B WHERE E.LM_ENRLMT_ID IN ( SELECT S.LM_ENRLMT_ID FROM PS_LM_ENR_LC_TBL S WHERE S.LM_HRS_TRACK_STTS IN ('1', '3')) AND E.LM_PERSON_ID = B.LM_PERSON_ID AND E.LM_ACT_DIF_FLG = 'Y' AND %CurrentDateIn BETWEEN B.EFFDT AND B.LM_END_EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_DIFHIST_TBL D WHERE D.LM_PERSON_ID = B.LM_PERSON_ID AND D.LM_EMPL_RCD = B.LM_EMPL_RCD) UNION SELECT E.LM_LRQ_ID , E.LM_REQUESTOR_ID , E.LM_CREATED_DT , E.LM_ACT_ID , E.LM_CI_ID , ' ' , ' ' , B.LM_HR_COMPANY , B.BUSINESS_UNIT , B.LM_ORGANIZATION_ID , D.LM_DIF_BALANCE_HRS , D.LM_DIF_LIMIT_HRS , ( SELECT DIF.LM_DIF_PLANNED_HRS FROM PS_LM_DIF_PLAN_VW DIF WHERE DIF.LM_PERSON_ID = E.LM_REQUESTOR_ID), ( SELECT SUM(LM_TOTAL_DUR_HRS) FROM PS_LM_LRQ_HRS WHERE LM_LRQ_ID = E.LM_LRQ_ID AND LM_HOUR_TYPE IN ('3', '4')) FROM PS_LM_LRN_REQ E, PS_LM_PERSON_ATTRB B, PS_LM_DIFHIST_TBL D WHERE E.LM_LRQ_STATUS IN ('1', '3') AND E.LM_REQUESTOR_ID = B.LM_PERSON_ID AND E.LM_ACT_DIF_FLG = 'Y' AND E.LM_HRS_TRACK_STTS IN ('1', '3') AND B.LM_PERSON_ID = D.LM_PERSON_ID AND %CurrentDateIn BETWEEN B.EFFDT AND B.LM_END_EFFDT AND B.LM_EMPL_RCD = D.LM_EMPL_RCD AND D.LM_AS_OF_DATE = ( SELECT MAX(X.LM_AS_OF_DATE) FROM PS_LM_DIFHIST_TBL X WHERE X.LM_PERSON_ID = D.LM_PERSON_ID AND X.LM_EMPL_RCD = D.LM_EMPL_RCD) UNION SELECT E.LM_LRQ_ID , E.LM_REQUESTOR_ID , E.LM_CREATED_DT , E.LM_ACT_ID , E.LM_CI_ID , ' ' , ' ' , B.LM_HR_COMPANY , B.BUSINESS_UNIT , B.LM_ORGANIZATION_ID , 0 , 0 , 0 ,0 FROM PS_LM_LRN_REQ E , PS_LM_PERSON_ATTRB B WHERE E.LM_LRQ_STATUS IN ('1', '3') AND E.LM_REQUESTOR_ID = B.LM_PERSON_ID AND E.LM_ACT_DIF_FLG = 'Y' AND E.LM_HRS_TRACK_STTS IN ('1', '3') AND %CurrentDateIn BETWEEN B.EFFDT AND B.LM_END_EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_DIFHIST_TBL D WHERE D.LM_PERSON_ID = B.LM_PERSON_ID AND D.LM_EMPL_RCD = B.LM_EMPL_RCD)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_ENRLMT_ID Number(10,0) DECIMAL(10) NOT NULL Enrollment ID
2 LM_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL ELM Person ID
3 LM_ENRL_DT Date(10) DATE Enrollment/Registration Date
4 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
5 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
6 LM_TRAINING_CLASS Character(10) VARCHAR2(10) NOT NULL Training Class
7 LM_TRG_PLN_CTGY Character(10) VARCHAR2(10) NOT NULL Training Plan Category
8 LM_HR_COMPANY Character(3) VARCHAR2(3) NOT NULL HR's Company ID brought over during synchronization.
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 LM_ORGANIZATION_ID Number(15,0) DECIMAL(15) NOT NULL Organization ID
11 LM_DIF_BALANCE_HRS Signed Number(13,3) DECIMAL(11,3) NOT NULL DIF Balance
12 LM_DIF_LIMIT_HRS Number(6,0) INTEGER NOT NULL DIF in Advance
13 LM_DIF_PLANNED_HRS Signed Number(13,3) DECIMAL(11,3) NOT NULL Planned DIF Balance
14 LM_HRS_DIF_HORS Number(10,3) DECIMAL(9,3) NOT NULL Learning Component DIF Duration out of working schedule in Hours