SCC_ACTTSKVW_FL

(SQL View)
 Index Back

Tasks Activity guide


SELECT DISTINCT D.OPRID , A.PTAI_LIST_ID , A.PTAI_LABEL , A.PORTAL_NAME ,A.PTAI_PARENT_TMPL , %Coalesce(A.PTAI_DUE_DATE,ITML.SCC_TM_DUE_DATE) , A.PTAI_LIST_STATUS , A.LAST_UPDATE_DTTM , cntx.SCC_TM_KEY_VALUE , CNTX1.SCC_TM_KEY_VALUE FROM PS_PTAI_LIST A , PS_SCC_TM_CONFIG B , PSOPRDEFN D , PS_SCC_TM_PTAIITEM ITML , PS_SCC_TM_PTAIITEM ITMF , PS_SCC_TM_PTAICNTX cntx ,PS_SCC_TM_PTAICNTX CNTX1 WHERE A.PTAI_PARENT_TMPL = B.SCC_TM_TASK_ID AND A.PTAI_ACTIVE_FLG = 'Y' AND A.PTAI_IS_TMPL='N' AND A.PTAI_LIST_STATUS = 'IP' AND A.PTAI_LIST_ID = ITML.SCC_TM_LIST_ID AND ITML.SCC_TM_PTAI_SEQ = ( SELECT MAX(ITM1.SCC_TM_PTAI_SEQ) FROM PS_SCC_TM_PTAIITEM ITM1 WHERE A.PTAI_LIST_ID = ITM1.SCC_TM_LIST_ID ) AND A.PTAI_LIST_ID = ITMF.SCC_TM_LIST_ID AND ITMF.SCC_TM_PTAI_SEQ = ( SELECT MIN(ITM2.SCC_TM_PTAI_SEQ) FROM PS_SCC_TM_PTAIITEM ITM2 WHERE A.PTAI_LIST_ID = ITM2.SCC_TM_LIST_ID ) AND %Coalesce(A.PTAI_START_DT,%CurrentDateIn) <= %CurrentDateIn AND %Coalesce(ITMF.SCC_TM_START_DATE,%CurrentDateIn) <= %CurrentDateIn AND EXISTS ( SELECT 'X' FROM PS_SCC_TM_PTAILPRV F , PSROLEUSER X WHERE F.SCC_TM_LIST_ID = A. PTAI_LIST_ID AND X.ROLEUSER = D.OPRID AND ((F.SCC_TM_PTAIMBRTYPE = 'USER' AND F.SCC_TM_PTAIMBRNAME = X.ROLEUSER) OR (F.SCC_TM_PTAIMBRTYPE = 'ROLE' AND F.SCC_TM_PTAIMBRNAME = X.ROLENAME)) ) AND cntx.SCC_TM_LIST_ID =A.PTAI_LIST_ID AND cntx.SCC_TM_CTX_KEY='Y' AND cntx.FIELDNAME ='INSTITUTION' AND cntx1.SCC_TM_LIST_ID =A.PTAI_LIST_ID AND cntx1.SCC_TM_CTX_KEY='Y' AND CNTX1.FIELDNAME = 'EMPLID'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 SCC_TM_LIST_ID Character(31) VARCHAR2(31) NOT NULL Instance ID
3 SCC_TM_LABEL Character(100) VARCHAR2(100) NOT NULL Title
4 PORTAL_NAME Character(30) VARCHAR2(30) NOT NULL Portal Name (see PSPRDMDEFN).

Prompt Table: PSPRDMDEFN

5 SCC_TM_PARENT_TMPL Character(31) VARCHAR2(31) NOT NULL Parent Template ID
6 SCC_TM_DUE_DATE Date(10) DATE Due Date
7 SCC_TM_LIST_STATUS Character(2) VARCHAR2(2) NOT NULL Status
CA=Cancelled
CP=Completed
IP=In Progress
8 SCC_TM_LASTUPDDTTM DateTime(26) TIMESTAMP Last Updated
9 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID