Posted Timesheets not in WIP

[Colum Creed]

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.

 

 

[Kathryn Ellis]

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.

 

[Colum Creed]

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.

 

[Andrew M]

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

 

[Colum Creed]

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.

[Andrew M]

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'

 

 

 

[Colum Creed]

Perfect

 

[Colum Creed]

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'

[Colum Creed]

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'

[matej256]

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

[navzjoshi00]

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

[matej256]

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!

[Jerin Sebastian]

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

[matej256]

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.

[navzjoshi00]

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

[Jerin Sebastian]

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

[matej256]

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.  

[pjromano]

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

[Colum Creed]

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'

[matej256]

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.

[Jerin Sebastian]

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

[matej256]

Hi Jerin,

 

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

 

Matej