LM_ACT_ONEFC_VW

(SQL View)
 Index Back

Facilty IDs per Activity

Use this view to segragate the activities with single and multiple facilities. If multiple facilties occur, then facility id number is set to maximum 9999999999

SELECT DISTINCT FC.LM_ACT_ID , FC.LM_FCLTY_ID , FC.LM_ROOM_ID , LM_SS_FCLTY_DISP FROM PS_LM_SES_VW FC WHERE EXISTS ( SELECT SS.LM_ACT_ID FROM PS_LM_SES SS WHERE FC.LM_ACT_ID = SS.LM_ACT_ID AND SS.LM_FCLTY_ID > 0 AND SS.LM_SES_DT = ( SELECT MAX(X.LM_SES_DT) FROM PS_LM_SES X WHERE SS.LM_ACT_ID = X.LM_ACT_ID AND SS.LM_LC_ID = X.LM_LC_ID AND SS.LM_SES_ID = X.LM_SES_ID AND X.LM_SES_DT <= %CurrentDateIn) GROUP BY SS.LM_ACT_ID HAVING COUNT(DISTINCT SS.LM_FCLTY_ID) = 1 ) UNION SELECT DISTINCT FC.LM_ACT_ID , 9999999999 , 9999999999 , 'Y' FROM PS_LM_SES_VW FC WHERE EXISTS ( SELECT SS.LM_ACT_ID FROM PS_LM_SES SS WHERE FC.LM_ACT_ID = SS.LM_ACT_ID AND SS.LM_FCLTY_ID > 0 AND SS.LM_SES_DT = ( SELECT MAX(X.LM_SES_DT) FROM PS_LM_SES X WHERE SS.LM_ACT_ID = X.LM_ACT_ID AND SS.LM_LC_ID = X.LM_LC_ID AND SS.LM_SES_ID = X.LM_SES_ID AND X.LM_SES_DT <= %CurrentDateIn) GROUP BY SS.LM_ACT_ID HAVING COUNT(DISTINCT SS.LM_FCLTY_ID) > 1 )

# 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_FCLTY_ID Number(10,0) DECIMAL(10) NOT NULL Facility ID
3 LM_ROOM_ID Number(10,0) DECIMAL(10) NOT NULL Room ID
4 LM_SS_FCLTY_DISP Character(1) VARCHAR2(1) NOT NULL Display facility through self service