LM_ENRL_ROST_VW

(SQL View)

SELECT A.LM_ENRLMT_ID ,A.LM_ACT_ID ,A.LM_STTS ,A.LM_ENRL_DT FROM PS_LM_ENRLMT A ,PS_LM_PERSON B ,PS_LM_PERSON_ATTRB C ,PS_LM_ORGANIZATION E WHERE A.LM_PERSON_ID = B.LM_PERSON_ID AND C.LM_PERSON_ID = A.LM_PERSON_ID AND E.LM_ORGANIZATION_ID = C.LM_ORGANIZATION_ID AND %CurrentDateIn BETWEEN E.EFFDT AND E.LM_END_EFFDT AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND A.LM_STTS <> 'PLAN' AND NOT (A.LM_OLD_STTS = 'PLAN' AND A.LM_STTS = 'CANC') AND C.LM_EMPL_RCD = ( SELECT MIN(C2.LM_EMPL_RCD) FROM PS_LM_PERSON_ATTRB C2 WHERE C2.LM_PERSON_ID = C.LM_PERSON_ID AND C2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = C2.LM_PERSON_ID AND LM_EMPL_RCD = C2.LM_EMPL_RCD AND EFFDT <= C.EFFDT) AND C2.LM_END_EFFDT >= %CurrentDateIn AND ((C2.LM_ACTIVE = 'Y' AND C2.LM_JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_LM_PERSON_ATTRB C3 WHERE C3.LM_PERSON_ID = C2.LM_PERSON_ID AND C3.LM_EMPL_RCD <> C2.LM_EMPL_RCD AND C3.LM_END_EFFDT >= %CurrentDateIn AND C3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LM_PERSON_ATTRB WHERE LM_PERSON_ID = C3.LM_PERSON_ID AND LM_EMPL_RCD = C3.LM_EMPL_RCD AND EFFDT <= %CurrentDateIn AND LM_END_EFFDT >= %CurrentDateIn) AND ((C3.LM_ACTIVE = 'Y' AND (C2.LM_ACTIVE <> 'Y' OR (C3.LM_JOB_INDICATOR = 'P' AND C2.LM_JOB_INDICATOR <> 'P'))) OR (C3.LM_JOB_INDICATOR = 'P' AND C2.LM_JOB_INDICATOR <> 'P' AND C2.LM_ACTIVE <> 'Y'))))) AND C.LM_END_EFFDT >= %CurrentDateIn AND ((C.LM_ACTIVE = 'Y') OR (C.LM_ACTIVE = 'N' AND NOT EXISTS ( SELECT 'x' FROM PS_LM_PERSON_ATTRB C4 WHERE C4.LM_PERSON_ID = C.LM_PERSON_ID AND C4.EFFDT = ( SELECT MAX(C5.EFFDT) FROM PS_LM_PERSON_ATTRB C5 WHERE C5.LM_PERSON_ID = C.LM_PERSON_ID AND C5.LM_EMPL_RCD = C4.LM_EMPL_RCD AND C5.EFFDT <= %CurrentDateIn) AND C4.LM_ACTIVE = 'Y')))

# 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_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID

3 LM_STTS Character(4) VARCHAR2(4) NOT NULL Enrollment Status
CANC=Dropped
COMP=Completed
DECL=Denied
ENRL=Enrolled
INCO=Not Completed
INPO=In-Progress
MACT=Moved to New Activity
NOTS=Not Started
PEAP=Pending Approval
PEPA=Pending Payment
PLAN=Planned
PPYA=Payment Approval
RQST=Learning Request
WAIV=Waived
WTLT=Waitlisted

4 LM_ENRL_DT Date(10) DATE Enrollment/Registration Date

#	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_ACT_ID	Number(10,0)	DECIMAL(10) NOT NULL	Activity ID
3	LM_STTS	Character(4)	VARCHAR2(4) NOT NULL	Enrollment Status CANC=Dropped COMP=Completed DECL=Denied ENRL=Enrolled INCO=Not Completed INPO=In-Progress MACT=Moved to New Activity NOTS=Not Started PEAP=Pending Approval PEPA=Pending Payment PLAN=Planned PPYA=Payment Approval RQST=Learning Request WAIV=Waived WTLT=Waitlisted
4	LM_ENRL_DT	Date(10)	DATE	Enrollment/Registration Date