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.
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.
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.
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.
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.
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.
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)
Small correction on the timentry to timesheet join in bold below
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'
Another correction to this.
The reference in EXTERNALBATCHID on PPA_WIP is to the PRTIMESHEET table not the PRTIMEENTRY table.
The correct sql is
RIGHT OUTER JOIN
wip.EXTERNALBATCHID = prsheet.PRID)
prsheet.prid = prtime.prtimesheetid)
wip.EXTERNALBATCHID IS NULL
AND prsheet.PRSTATUS = '4'
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
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
Have you checked the Invalid Transactions page on the admin side ?Yes of course, it is blank. Same as the table IMP_TRANSACTIONIMPORT
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
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!
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
PRChargeCode.prExternalID as ccExternalID,
PRTypeCode.prExternalID as tcExternalID
left outer join PRChargeCode on PRTimeEntry.prChargeCodeID=PRChargeCode.prID
left outer join PRTypeCode on PRTimeEntry.prTypeCodeID=PRTypeCode.prID,
PAC_MNT_RESOURCES, PAC_MNT_PROJECTS, PRTask, PRAssignment
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.
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.
In the query that was provided -
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".
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
and PRTimeSheet.prStatus=4 --timesheet in posted state
and PRResource.prTrackMode=2 -- trackmode of resource is Clarity
and PAC_MNT_RESOURCES.ACTIVE=1 -- Resource is active
and PAC_MNT_PROJECTS.APPROVED=1 -- Project is approved
and PAC_MNT_PROJECTS.STATUS='O' -- project financial is open
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.
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.
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.
niku.SRM_RESOURCES.UNIQUE_NAME as res_code,
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
Here is the query I use to do the same thing based on the suggestion I received earlier from am1
prtime.PRASSIGNMENTID = prassign.PRID)
prassign.PRTASKID = tsk.PRID)
tsk.PRPROJECTID = inv.ID)
prtime.PRTIMESHEETID = prsheet.PRID)
prsheet.PRRESOURCEID = RES.ID)
prsheet.PRTIMEPERIODID = tp.PRID)
RIGHT OUTER JOIN
(prsheet.PRID = wip.EXTERNALBATCHID)
wip.EXTERNALBATCHID IS NULL
AND prsheet.PRSTATUS = '4'
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.
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.
I've just voted for it. Thanks for this tip