Clarity

  • 1.  Populate button on Timesheet not adding Incidents

    Posted Mar 30, 2012 12:00 PM
    We have a user who, when using the Populate button on new timesheets, does not get the previous week's Incidents added to the new sheet. This happens every week. Other users are able to add these same Incidents to their timesheet(s) using the Populate button. Project Tasks are added using the Populate button with no issue.

    I've tried using the SQL below to trace but thus far haven't found the reason. I can't seem to find the step that is actually putting the Incidents rows into PSTIMEENTRY, even when tracing a user that it works for.

    update niku.cmn_sec_users set sqltrace_active = 10 where user_name =<loginID>

    Has anyone seen this before? Or does anyone happen to know the SQL used to add Incidents to a new Timesheet?


  • 2.  RE: Populate button on Timesheet not adding Incidents

     
    Posted Apr 04, 2012 12:29 PM
    Hi All,

    Anyone have any suggestions for qkenny?

    Thanks!
    Chris


  • 3.  RE: Populate button on Timesheet not adding Incidents
    Best Answer

    Posted Apr 05, 2012 06:47 AM
    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