Below is the query that populates tasks in timesheet -
SELECT * FROM ( SELECT T.PRID AS TASK_ID
,T.PRNAME AS NAME
,T.PREXTERNALID AS UNIQUE_NAME
,T.PRSTART AS START_DATE
,T.PRFINISH AS FINISH_DATE
,T.PRSTATUS AS TASK_STATUS
,T.PRWBSSEQUENCE
,P.ID AS PROJECT_ID
,P.NAME AS PROJECT_NAME
,P.CODE AS PROJECT_UNIQUE_NAME
FROM INV_INVESTMENTS P, INV_PROJECTS PRJ, PRTEAM
, PRTASK T , (SELECT prtaskid, prresourceid FROM PRASSIGNMENT WHERE PRRESOURCEID=<ResourceID>) A
WHERE T.PRID = A.PRTASKID AND PRTEAM.PRRESOURCEID=<ResourceID>
AND PRTEAM.PRISOPEN <> 0
AND P.ID = PRTEAM.PRPROJECTID
AND P.IS_ACTIVE <> 0
AND PRJ.PRID = P.ID AND PRJ.IS_TEMPLATE = 0
AND P.IS_OPEN_FOR_TE <> 0 AND P.TRACK_MODE = 2
AND T.PRPROJECTID = P.ID
AND T.PRISMILESTONE = 0
AND T.PRISTASK <> 0
AND T.IS_OPEN_TE = 1 AND T.PRSTATUS <> 2 MINUS
SELECT T.PRID AS TASK_ID
,T.PRNAME AS NAME
,T.PREXTERNALID AS UNIQUE_NAME
,T.PRSTART AS START_DATE
,T.PRFINISH AS FINISH_DATE
,T.PRSTATUS AS TASK_STATUS
,T.PRWBSSEQUENCE
,P.ID AS PROJECT_ID
,P.NAME AS PROJECT_NAME
,P.CODE AS PROJECT_UNIQUE_NAME
FROM INV_INVESTMENTS P, INV_PROJECTS PRJ, PRTEAM
, PRTASK T , (SELECT prtaskid, prresourceid FROM PRASSIGNMENT WHERE PRRESOURCEID=<ResourceID>) A
WHERE T.PRID = A.PRTASKID AND PRTEAM.PRRESOURCEID=<ResourceID>
AND PRTEAM.PRISOPEN <> 0
AND P.ID = PRTEAM.PRPROJECTID
AND P.IS_ACTIVE <> 0
AND PRJ.PRID = P.ID AND PRJ.IS_TEMPLATE = 0
AND P.IS_OPEN_FOR_TE <> 0 AND P.TRACK_MODE = 2
AND T.PRPROJECTID = P.ID
AND T.PRISMILESTONE = 0
AND T.PRISTASK <> 0
AND T.IS_OPEN_TE = 1
AND (EXISTS
(SELECT PARENT.PRID FROM PRTASK PARENT
WHERE PARENT.PRPROJECTID = P.ID
AND PARENT.PRWBSSEQUENCE < T.PRWBSSEQUENCE
AND PARENT.WBS_NNBSEQ > T.PRWBSSEQUENCE
AND PARENT.IS_OPEN_TE = 0)
OR T.PRID IN
(SELECT DISTINCT A1.PRTASKID
FROM PRASSIGNMENT A1, PRTIMEENTRY TE1
WHERE TE1.PRASSIGNMENTID = A1.PRID
AND TE1.PRTIMESHEETID = <TimesheetId>)
) ) SORTABLE_DATA_SET ORDER BY PROJECT_NAME asc, PRWBSSEQUENCE asc;
Below is the query for Incidents -
SELECT I.ID AS INCIDENT_ID
, I.SUBJECT AS INCIDENT_NAME
, I.INCIDENT_CODE
, I.DESCRIPTION DESCRIPTION
, I.STATUS_CODE
, (SELECT NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='IMM_STATUS' AND LOOKUP_CODE=I.STATUS_CODE AND LANGUAGE_CODE=<language_code>) AS STATUS_CAPTION
, I.CREATED_DATE CREATED_DATE
, I.RESOLUTION_DATE
, INV.ID AS INV_ID
, INV.NAME AS INV_NAME
, INV.CODE AS INV_UNIQUE_NAME
, C.NAME AS CATEGORY
FROM IMM_INCIDENTS I
, IMM_CATEGORIES C
, IMM_OBJECT_CATEGORIES OC
, INV_INVESTMENTS INV
WHERE C.ID = I.CATEGORY_ID
AND OC.CATEGORY_ID = C.ID
AND INV.ID = OC.OBJECT_ID
AND (I.RESOLUTION_DATE IS NULL OR I.RESOLUTION_DATE >= null) AND I.ASSIGNED_TO=(SELECT USER_ID FROM SRM_RESOURCES WHERE ID=<resourceid>) ORDER BY INCIDENT_NAME asc, INV_NAME asc;
NJ