Clarity

Expand all | Collapse all

Posted Timesheets not in WIP

Jump to Best Answer
  • 1.  Posted Timesheets not in WIP

    Posted 02-18-2014 10:16 AM

    We discovered some timesheets that had not been posted to WIP. This was because our "Post to WIP" job was sending in a "start date" parameter to the PAC_PRC_POSTTOWIPS_SP that was after the timesheet transaction date.

    I have seen that other users have created portlets to detect "posted" timesheets that have not been posted to WIP. What tables/columns are you using to detect these timesheets?

    Note this is not an issue of records stuck in the Invalid transactions table. They don't make it that far.

     

     



  • 2.  RE: Posted Timesheets not in WIP
    Best Answer

    Broadcom Employee
    Posted 02-18-2014 12:35 PM

    Transactions available for posting to WIP are not automatically displayed. You must first search for the transactions you want to post to WIP.  The expected behavior for the 'Post to WIP' action in the Clarity application UI has an underlying built-in function to only post the transactions waiting for the current fiscal period up to the current date.  If you want to ensure that all outstanding transactions waiting for posting are pulled in from previous dates or into the future dates, then you must fill out the date fields in the Filter Section.  The 'default' fiscal period referred to the in the documentation indicates the type of period such as Monthly, Annually, etc set up in the default Entity. 

    This is documented in the Clarity Financial Management Guide : Transaction Processing › Work-in-Progress Transactions › Post Transactions to Work-in Progress

    Navigate to the Clarity Bookshelf for more details within this guide.

    From Date and To Date

    Defines a specific date range for posting transactions. Use the date picker to post transactions within the specified date range.

    You must specify a From Date and To Date to return past or future transactions that are outside the default fiscal period.

     



  • 3.  RE: Posted Timesheets not in WIP

    Posted 02-18-2014 01:06 PM

    From using Clarity, I have discovered it is the other way round from what you describe. If I "Post to WIP" from within the UI,  it picks up ALL timesheets regardless of what fiscal period they fall into. If I want to limit what is processed, I have to put in dates. In fact, this is how we discovered some timesheets had not been posted to WIP.

    However, what I am looking for is a way to see what timesheets have been "posted" but not posted to WIP without running the Post to WIP.This seems to be a common need for users. I have looked in the discussion boards and others have created a portlet to see these timesheets. I am looking for the query used to identify these records.

     



  • 4.  RE: Posted Timesheets not in WIP

    Posted 02-20-2014 09:35 AM

    Before posted to wip, the transactions are held in ppa_transcontrol. You can just query this to see what transactions are pending to be posted to wip. 

    They will appear in this table once post timesheets and post transactions to financials jobs have been run. 

    Andrew

     



  • 5.  RE: Posted Timesheets not in WIP

    Posted 02-20-2014 10:33 AM

    Thanks Andrew but that does not resolve what I am looking for since I want to find out what timesheet records are being ignored in the timesheet posting process.

    ppa_transcontrol shows only those records that have not been ignored.



  • 6.  RE: Posted Timesheets not in WIP

    Posted 02-20-2014 11:26 AM

    high level overview of the "timesheet posting process":

    user submits timesheets (prtimesheet.prstatus 1)

    manager approves (prtimesheet.prstatus 3)

    admin runs post timesheets (prtimesheet.prstatus 4)

    admin runs post transactions to financials (transactions created in ppa_transcontrol, errors in invalid transactions (IMP_TRANSACTIONIMPORT)

    admin runs post to wip (transactions now in ppa_wip and appear in cost plans etc)

     

    This lists timesheets that are not in WIP but have posted status (prstatus 4)

    SELECT
     
    PRTIME.PRTIMESHEETID,
    prtime.prid
     
    from
    niku.ppa_wip wip right join niku.PRTIMEENTRY prtime on wip.EXTERNALBATCHID = prtime.PRID inner join niku.prtimesheet prsheet on prtime.prid = prsheet.prid
    where wip.EXTERNALBATCHID is null and prsheet.prstatus = '4'

     

     

     



  • 7.  RE: Posted Timesheets not in WIP

    Posted 02-20-2014 02:22 PM

    Perfectsmiley

     



  • 8.  RE: Posted Timesheets not in WIP

    Posted 03-03-2014 11:29 AM

    Small correction on the timentry to timesheet join in bold below

    SELECT
     
    PRTIME.PRTIMESHEETID,
    prtime.prid
     
    from
    ppa_wip wip right join PRTIMEENTRY prtime
    on wip.EXTERNALBATCHID = prtime.PRID
    inner join prtimesheet prsheet on prtime.prtimesheetid = prsheet.prid
    where wip.EXTERNALBATCHID is null and prsheet.prstatus = '4'



  • 9.  RE: Posted Timesheets not in WIP

    Posted 03-04-2014 11:46 AM

    Another correction to this.

    The reference in EXTERNALBATCHID on PPA_WIP is to the PRTIMESHEET table not the PRTIMEENTRY table.

    The correct sql is

    SELECT
        prtime.prid,
        PRTIME.PRTIMESHEETID,
        prsheet.PRTIMEPERIODID
    FROM
        CLARITY.PPA_WIP wip
    RIGHT OUTER JOIN
        CLARITY.PRTIMESHEET prsheet
    ON
        (
            wip.EXTERNALBATCHID = prsheet.PRID)
    INNER JOIN
        CLARITY.PRTIMEENTRY prtime
    ON
        (
            prsheet.prid = prtime.prtimesheetid)
    WHERE
        wip.EXTERNALBATCHID IS NULL
    AND prsheet.PRSTATUS = '4'



  • 10.  Re: Posted Timesheets not in WIP

    Posted 06-26-2014 08:28 AM

    Hi Colum,

     

    thank you very much for this query. I have exactly "problem" as you. Some of the timesheets got into the status "posted" but never has been posted to transactions and next to the table ppa_wip by "Post to WIP" process.

     

     

     

    May I ask you if you have found the root cause why this issue happened and have you resolved it?

     

    Many thanks for your response



  • 11.  Re: Posted Timesheets not in WIP

    Posted 06-26-2014 10:15 AM

    Have you checked the Invalid Transactions page on the admin side ?

     

    Once the timesheets are posted, you run the Post Transactions to Financials job, which populates the data into the ppa_transcontrol and ppa_transcontrol_values tables

     

    It is at this stage that the validation is done, and the unsuccessful records can be seen on the Invalid Transactions page

     

    NJ



  • 12.  Re: Posted Timesheets not in WIP

    Posted 06-26-2014 10:25 AM

    Have you checked the Invalid Transactions page on the admin side ?
    Yes of course, it is blank. Same as the table IMP_TRANSACTIONIMPORT


    Once the timesheets are posted, you run the Post Transactions to Financials job, which populates the data into the ppa_transcontrol and ppa_transcontrol_values tables

    I fear I cannot do this repeatly because I'm not doing these steps but other team(other country even). I'm only responsible for working well

     

    It is at this stage that the validation is done, and the unsuccessful records can be seen on the Invalid Transactions page

    I've checked ppa_wip table the first, there are no records from TS for some resources as I expected. ppa_transcontrol table is blank as usually. So only step where could be the problem is job "Post transaction to financials". This job creates transactions from POSTED timesheets. Behind this job is stored procedure PAC_IMP_NPMTRANSIMPORT_SP. I think when these timesheets would have been created as transactions, all next steps of process(ppa_transcontrol, ppa_wipp) would have been fine. But there were some reason why this stored procedure PAC_IMP_NPMTRANSIMPORT_SP haven't processed some Timesheets which were POSTED.


    And still my question ... have you found the root cause why this issue happened(yo you, in the first post) and have you resolved it?


    Many thanks for your effort!





  • 13.  Re: Posted Timesheets not in WIP

    Broadcom Employee
    Posted 06-26-2014 06:40 PM

    I foresee 2 reasons why your timesheets did not become a financial transaction

     

    1. Did you check the PRTimeEntry.PRRMExported for this timesheet transaction which did not get to ppa_wip? If this is 0, it means the Post timesheet job has not picked this transaction to imp_transactionimport table. Below is the query you can use to find which all transactions are not processed by the Post timesheet job

    select  PRTimeEntry.*,

                PRChargeCode.prExternalID as ccExternalID,

                PRTypeCode.prExternalID as tcExternalID

          from    PRTimeEntry

                left outer join PRChargeCode on PRTimeEntry.prChargeCodeID=PRChargeCode.prID

                left outer join PRTypeCode on PRTimeEntry.prTypeCodeID=PRTypeCode.prID,

                PRTimeSheet, PRResource,

                PAC_MNT_RESOURCES, PAC_MNT_PROJECTS, PRTask, PRAssignment

          where   PRTimeEntry.prRMExported=0

          and   PRTimeEntry.prTimeSheetID=PRTimeSheet.prID

          and   PRTimeSheet.prStatus=4

          and   PRTimeSheet.prResourceID=PRResource.prID

          and   PRResource.prTrackMode=2

          and   PAC_MNT_RESOURCES.ID=PRResource.prID

          and   PAC_MNT_RESOURCES.ACTIVE=1

          and   PAC_MNT_PROJECTS.ID=PRTask.prProjectID

          and   PAC_MNT_PROJECTS.APPROVED=1

          and   PAC_MNT_PROJECTS.STATUS='O'

          and   PRTask.prId = PRAssignment.prTaskID

          and   PRTimeEntry.prAssignmentID = PRAssignment.prID

          and  PAC_MNT_PROJECTS.PROJECT_CODE=  'x'

     

    From this query, you can see the different conditions which should be satisfied for the post timesheet job to pick the timesheet transaction. For eg., a resource enters timesheet and before the post timesheet job runs, the project manager has put the project financial status ON HOLD. In such situation it will not even process to imp_transactionimport table. So check for these conditions and correct them.

     

    2. I wouldn't want this to happen but a worst case scenario and I have seen this in the past. The timesheet got processed to imp_transactionimport table but it was not qualified to be financial and was marked as ERROR in invalid transaction list. The application admin (or whoevere is responsible to clear the invalid transaction) deletes that transaction from the invalid transaction list. If this has happened, there is no way to get those transactions back.

     

    Since this is a timesheet transaction, its not recommended to do a Transaction Entry because of many reasons and primary reason would be, your portlets like posted timesheet review will have discrepancy as it will not show these transactions but when you check the timesheet its posted there.

     

    - Jerin



  • 14.  Re: Posted Timesheets not in WIP

    Posted 06-27-2014 06:04 AM

    Hi sebje,

     

    Thanks fot he response.

     

    1.) When I ran your query I got 0 records.

    In my assumption, no transactions from the Timesheets have been created.

     

    2) I think nobody could delete some invalid transactions from the imp_transactionimport table.



    Do you have any other ideas? Many thanks for all your effort.



  • 15.  Re: Posted Timesheets not in WIP

    Posted 06-27-2014 08:58 AM

    In the query that was provided -

     

    and   PAC_MNT_PROJECTS.STATUS='O'


    If you say "When I ran your query I got 0 records.", this means that the Financial Status of the project(s) is  not Open. Hence, you need to first select the status as "OPEN".


    NJ



  • 16.  Re: Posted Timesheets not in WIP

    Broadcom Employee
    Posted 06-29-2014 07:10 AM

    Hi matej256,

    The first and foremost thing you need to check would be PRTimeEntry.prRMExported for this 'missing' transaction. As I mentioned in the previous post, if this is 0, then the post timesheet has not picked your record. Now, the reason why it has not picked includes what NJ has mentioned above and also you need to check for other conditions as well in that query

                 PRTimeEntry.prRMExported=0

          and   PRTimeEntry.prTimeSheetID=PRTimeSheet.prID

          and   PRTimeSheet.prStatus=4 --timesheet in posted state

          and   PRTimeSheet.prResourceID=PRResource.prID

          and   PRResource.prTrackMode=2 -- trackmode of resource is Clarity

          and   PAC_MNT_RESOURCES.ID=PRResource.prID

          and   PAC_MNT_RESOURCES.ACTIVE=1 -- Resource is active

          and   PAC_MNT_PROJECTS.ID=PRTask.prProjectID

          and   PAC_MNT_PROJECTS.APPROVED=1 -- Project is approved

          and   PAC_MNT_PROJECTS.STATUS='O' -- project financial is open

          and   PRTask.prId = PRAssignment.prTaskID

          and   PRTimeEntry.prAssignmentID = PRAssignment.prID

    If  the PRRMExported is 1 for this transaction and if you are sure this transaction is not in imp_transactionimport or ppa_transcontrol table, then open a case with CA Support.

     

    Regards,

    - Jerin



  • 17.  Re: Posted Timesheets not in WIP

    Posted 06-30-2014 03:52 AM

    Hi all,

     

    when I exclude and  PAC_MNT_PROJECTS.STATUS='O' from conditions, I also got 0 records.

     

    Condition and  PAC_MNT_PROJECTS.PROJECT_CODE=  'x' causes that I'm still getting 0 records. When I exclude this, I got some records. Financial status is Open, I've checked this as the first thing in my investigating process. We have project code in shape "0NumNumNumNumNm" so e.g. '032323'.

     

    PRTimeEntry.prRMExported field has value 0 for all records I got. Yes, it means that if this is 0, then the 'post timesheet' job has not picked my record, as you said.

    So are there any conditions for picking up timesheets which are "APPROVED" but didn't get "POST" status? I thought that all Approved timesheets are set to "POSTED" status by job 'post timesheets'.


    Note: All projects fullfill conditions to go through over the whole 'timesheet' process. I assume it is due to fact that only some timesheets of resources didn't go to transactions and ppa_wip table, but some other resources did it. I assume it is due to resources. These resources were created after upgrade to ver 13.3 of Clarity. For "older" resources we are not facing this issue.


    Many thanks for your responses. Maybe it will be better to raise ticket to CA support.  





  • 18.  Re: Posted Timesheets not in WIP

    Posted 06-30-2014 11:18 AM

    I've used this query to identify the transactions that were posted but not in WIP.  We even made a portlet out of this in order to clean up the data regularly.

     

    SELECT niku.PRTIMESHEET.PRSTATUS,

           niku.PRTIMEENTRY.PRMODTIME,

           niku.PRTIMEENTRY.PRID,

           niku.PRTIMEENTRY.PRACTSUM,

           niku.SRM_PROJECTS.UNIQUE_NAME,

           niku.SRM_RESOURCES.LAST_NAME,

           niku.PRTIMEENTRY.PRRMEXPORTED,

           niku.SRM_RESOURCES.FIRST_NAME,

           niku.SRM_RESOURCES.UNIQUE_NAME as res_code,

           niku.PRASSIGNMENT.PRSTART,

           niku.PRASSIGNMENT.PRFINISH

    FROM         niku.SRM_RESOURCES INNER JOIN

                          niku.PRTASK INNER JOIN

                          niku.PRTIMESHEET INNER JOIN

                          niku.PRTIMEENTRY ON niku.PRTIMESHEET.PRID = niku.PRTIMEENTRY.PRTIMESHEETID INNER JOIN

                          niku.PRASSIGNMENT ON niku.PRTIMEENTRY.PRASSIGNMENTID = niku.PRASSIGNMENT.PRID ON

                          niku.PRTASK.PRID = niku.PRASSIGNMENT.PRTASKID INNER JOIN

                          niku.SRM_PROJECTS ON niku.PRTASK.PRPROJECTID = niku.SRM_PROJECTS.ID ON

                          niku.SRM_RESOURCES.ID = niku.PRTIMESHEET.PRRESOURCEID

    WHERE     (niku.PRTIMESHEET.PRSTATUS = 4) AND (niku.PRTIMEENTRY.PRMODTIME > CONVERT(DATETIME, '2005-01-01 00:00:00', 102)) AND

                          (niku.PRTIMEENTRY.PRACTSUM > 0) AND (niku.PRTIMEENTRY.PRRMEXPORTED = 0)

    ORDER BY niku.SRM_PROJECTS.UNIQUE_NAME



  • 19.  Re: Posted Timesheets not in WIP

    Posted 06-30-2014 02:52 PM

    Here is the query I use to do the same thing based on the suggestion I received earlier from am1

    SELECT

        prsheet.PRSTATUS,

        PRTIME.PRTIMESHEETID,

        RES.LAST_NAME,

        RES.FIRST_NAME,

        RES.UNIQUE_NAME,

        prsheet.PRTIMEPERIODID,

        tp.PRSTART,

        tp.PRFINISH,

        prtime.ROLE_ID,

        inv.NAME,

        inv.CODE,

        prtime.PRCHARGECODEID,

        prtime.PRID,

        wip.EXTERNALBATCHID,

        prtime.PRRMEXPORTED

    FROM

        PRTIMEENTRY prtime

    INNER JOIN

        PRASSIGNMENT prassign

    ON

        (

            prtime.PRASSIGNMENTID = prassign.PRID)

    INNER JOIN

        PRTASK tsk

    ON

        (

            prassign.PRTASKID = tsk.PRID)

    INNER JOIN

        INV_INVESTMENTS inv

    ON

        (

            tsk.PRPROJECTID = inv.ID)

    INNER JOIN

        PRTIMESHEET prsheet

    ON

        (

            prtime.PRTIMESHEETID = prsheet.PRID)

    INNER JOIN

        SRM_RESOURCES RES

    ON

        (

            prsheet.PRRESOURCEID = RES.ID)

    INNER JOIN

        PRTIMEPERIOD tp

    ON

        (

            prsheet.PRTIMEPERIODID = tp.PRID)

    RIGHT OUTER JOIN

        PPA_WIP wip

    ON

          (prsheet.PRID = wip.EXTERNALBATCHID)

    WHERE

        wip.EXTERNALBATCHID IS NULL

    AND prsheet.PRSTATUS = '4'



  • 20.  Re: Posted Timesheets not in WIP

    Posted 07-01-2014 05:21 AM

    Hi all,

     

    thanks for queries, I could retrieve posted timesheets not in WIP.

     

    However I'd like to find the reason why this happened.

    I've XOGged some "problem" resources with posted timesheets not in WIP from PROD system to DEV system

     

    When I fullfill Timesheets, got approved, run job postTimesheets they were posted. Then I ran other financial jobs and successfully went all transactions to ppa_wip table. So w/o any issues.

     

    But the questions is why some of POSTED timesheets in PROD are not in ppa_wip table. We assume(sure) that they were posted long ago we've ran postTimesheets job. 

     

    Thank to all for your effort.



  • 21.  Re: Posted Timesheets not in WIP

    Broadcom Employee
    Posted 10-24-2014 08:35 PM

    All,

    There is an IDEA posted in the communities to build an OOTB portlet which shows up all the timesheet records which are stuck in timesheet table because PRRMEXPORTED = 0. If you feel this is going to benefit you to find the 'missing' timesheet hours from UI than writing queries and executing in DB, go vote for the idea.

    https://communities.ca.com/ideas/235716001#comment-233899003

     

    Regards,

    Jerin



  • 22.  Re: Posted Timesheets not in WIP

    Posted 04-28-2015 07:18 AM

    Hi Jerin,

     

    I've just voted for it. Thanks for this tip

     

    Matej