LM_SRCH_ACTSCC1

(SQL View)
 Index Back

Search: Activity Security

This view will set the learner group for the classes and courses without classes. The learner group is set to 999999999999999 which is used for administrative pages. In addition, class statutes for Active, Closed and Concluded are also selected for this learner group. All others are set to the class learner group based on what is set as security. In addition, the class status must be Active.

SELECT A1.LM_ACT_ID , A1.LM_CI_ID , S1.LM_LRNR_GROUP_ID FROM PS_LM_ACT A1 , PS_LM_ACT_SEC S1 WHERE A1.LM_ACT_ID = S1.LM_ACT_ID AND A1.LM_ACT_STATUS IN ('1','3','5') UNION SELECT A1X.LM_ACT_ID , A1X.LM_CI_ID , 999999999999999 FROM PS_LM_ACT A1X , PS_LM_ACT_SEC S1X WHERE A1X.LM_ACT_ID = S1X.LM_ACT_ID AND A1X.LM_ACT_STATUS IN ('1','3','5') UNION SELECT 0 , C2.LM_CI_ID , S2.LM_LRNR_GROUP_ID FROM PS_LM_CI_SEC_TBL S2 , PS_LM_CI_TBL C2 WHERE S2.LM_CI_ID = C2.LM_CI_ID AND S2.EFFDT = C2.EFFDT AND %CurrentDateIn BETWEEN C2.EFFDT AND C2.LM_END_EFFDT AND C2.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 1 FROM PS_LM_ACT A2 WHERE A2.LM_CI_ID = C2.LM_CI_ID ) UNION SELECT 0 , C2X.LM_CI_ID , 999999999999999 FROM PS_LM_CI_TBL C2X WHERE %CurrentDateIn BETWEEN C2X.EFFDT AND C2X.LM_END_EFFDT AND C2X.LM_CI_STATUS = '10' AND NOT EXISTS ( SELECT 1 FROM PS_LM_ACT A2X WHERE A2X.LM_CI_ID = C2X.LM_CI_ID ) UNION SELECT 0 , S3.LM_CI_ID , S3.LM_LRNR_GROUP_ID FROM PS_LM_CI_SEC_TBL S3 , PS_LM_CI_TBL C3 WHERE S3.LM_CI_ID = C3.LM_CI_ID AND S3.EFFDT = C3.EFFDT AND C3.LM_CI_STATUS = '10' AND %Sql(LM_ELIG_NOT_APPL_SQL,C3) UNION SELECT 0 , C3X.LM_CI_ID , 999999999999999 FROM PS_LM_CI_TBL C3X WHERE C3X.LM_CI_STATUS = '10' AND %Sql(LM_ELIG_NOT_APPL_SQL,C3X)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
2 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
3 LM_LRNR_GROUP_ID Number(15,0) DECIMAL(15) NOT NULL Learner Group ID - Business Role/ Student Group ID