Clarity

  • 1.  Timesheet status

    Posted Feb 08, 2017 03:40 AM

    Hi,

     

    I have a portlet where I do have multiple entry of same task under same project with 2 timesheet status Adjusted and Posted. Eg : Resource A is charged twice under the Project task X with Timehseet status Posted as 20 hrs, and again with status Adjusted, same resource A is charged 20 hrs. Now that I need to eliminate the posted record if I have both Posted and Adjusted for same project task.

     

    Is there any field to check when the timesheet status change from Posted to Adjusted? I tried with PRISADJUSTMENT and PRADJUSTEDID fields in PRTIMESHEET table. But that is of no help. Can you guys suggest me with a workaround please to extract only Adjusted time record from both?

     

    Monica



  • 2.  Re: Timesheet status
    Best Answer

    Posted Feb 08, 2017 03:55 AM

    I need to eliminate the posted record if I have both Posted and Adjusted for same project task

    That sort of logic can usually be done in a query with just a NOT EXISTS expression.

    So the query would have something like;

     

    SELECT some stuff

    FROM some timesheet tables TS, TE, AS

    WHERE 

    AND NOT EXISTS ( SELECT 1 FROM prtimesheet TS1, prtimentry TE1, prassignment AS1

    WHERE AS1.prtaskid = AS.prtaskid                                 /* same task as the outer select*/

    AND AS1.PRRESOURCEID= AS.PRRESOURCEID      /* same resource as the outer select*/

    AND AS1.PRID = TE1.PRASSIGNMENTID                    

    AND TE1.PRTIMESHEETID = TS1.PRID                         

    AND TS1.PRTIMEPERIODID = TS.PRTIMEPERIODID   /* same time period as the outer select */

    AND TS1.PRRESOURCEID= TS.PRRESOURCEID         /* same resource as the outer select */

    AND TS1.PRID != TS.PRID                                                /* not the same timesheet as the outer select */

    AND TS1.PRSTATUS = 4 )                                                  /* is posted */



  • 3.  Re: Timesheet status

    Posted Feb 08, 2017 04:58 AM

    That worked exactly as I wanted! Great, thank you so much! Dave_3.0