Clarity

Hi All,

Here is the summary of an incident..

A Timesheet period was created (May 9th to May 15th). Some timesheets were entered in this time period. Some of those were submitted, some approved and some posted.

A full database backup was taken.

Next, this time period was deleted on production environment for a week (May 9th to May 15th). This deletion has removed the data from Time sheets tables but the same time periods' timesheet data has passed through the full posting cycle (including to the WIP Table).

No impact on cost plan and other financial data as it is looking from WIP table .

Re-created the same time period on production and a few resources ( < 10) re-entered their time sheet and the time sheet data has been passed through the full cycle of financial (up to WIP Table). We will create reverse transaction for these resources timesheet to remove the duplicate records from WIP table

WIP Table has dual entries for <10 resources for the same time period due to duplicate timesheets entered and posted.

There is a gap between the backup taken and time period deletion

In order to restore the time sheet data backup has been restored on QA Environment .

Xog out all the "Posted" time sheets from QA Environment

Manually Created the new time sheets in QA environment and moved up to "Approved" status for the same period as there are few time sheets were missing in the backup while they are available in Prod WIP table for the same time period..

Xog out all the "Approved" time sheets from QA Environment

Due to issues in QA environment none of the jobs are running and we are unable to post the newly created time sheets. (Note-> we will open the time period just the time being to XOG in the time sheets after that we will close the time period)

Now we have the following questions...

Q1 ) If we will manually edit the XOG XML output files to change the approved time sheets status from 3 to 4 before importing into Production environment, what is the impact?
Q2 ) How can we protect the XOG in time sheets of deleted period not to process in Transaction table and WIP Table. We are doing so in order to maintain referential integrity and not have two transactions for each timesheet since the timesheets were created twice.



Thanks in advance.

Shalinee

"2. How can we protect the XOG in time sheets of deleted period not to process in Transaction table and WIP Table. We are doing so in order to maintain referential integrity and not have two transactions for each timesheet since the timesheets were created twice"

Once the timesheets are posted, set the "PRRMEXPORTED" flag in the "prtimeentry" table to 1

PRRMEXPORTED = A flag that indicates whether the time entry row has been processed and data sent to the project accounting component.

NJ

Thanks NJ.

[size=5]What prompted the deletion of the timesheets?

How were the original time reporting period and its timesheets deleted? Via Delete Investments job, or database update?

Have you considered the actuals on projects doubling, besides WIP ?

Potential impact of what you are doing: data integrity in project actuals, resource allocation, discrepancies between timesheets, project and task actuals, WIP, both in quantity/actuals and cost amount

And finally, please do not touch prrmexported, it is to be used only by the Clarity application[size]

Accidentally time period "marked for deletion" and deleted via Delete Investment Job.

NJ

The Post Timesheets job sets the prrmexported flag

Once again, don't touch it, ever. Please. :)

Connie

Thanks Connie for detailed explanation.
Will update you on status. So far we are planning to run entire show for one resource timesheet entry only and we will verify the status in all related tables before and after .

Shalinee

OK, will keep that in mind, Connie :tongue

NJ

Shlinee

Since the original timesheets are gone while there are WIP records in the PROD system, I'd rather you zero out the quantity of these WIP transactions, then run Import Financial Actuals to update the project actuals. Put in proper notes in your WIP adjustments to indicate the reason for zeroing quantity

As it is just one week and there are less than 10 resources affected, let your users know of the impact, have the project managers reset project allocation, ETC to allow for time to be booked again. Safest thing to do is to have the resources enter the timesheets again, you can show them from the backup what it looked like before being deleted.

The new timesheets can then be posted and processed all the way to WIP because at this time WIP would show 0 hours posted for the same assignment for that week, and is ready for the hours to come in again

However if you must follow the approach you laid out (particularly regarding XOG in timesheets), test it first in another non-prod to see what happens.

I'm more worried about your assignment actuals...

Connie

Hi Connie,

We started with incremental approach for a single resource time sheet for the deleted time period....

Here are the steps which we have followed to recover the deleted time period time sheets and assignment accruals and WIP Data...

1- XOG Out the Posted time sheet of Resource A (40 hours) of deleted time period from production backup on Non production environment
2- Re-edit the XML file of XOG Out time sheet of Resource A with 40 Hours . We have added the following:

Replace the 5 million time sheet id by -1 and added syntax action="add "then change the version="1". Save the file. Attached the XML
3- In other environment ran the following queries to collect the data of resource A:

select sum(quantity) from ppa_wip
where resource_code='vcc44264'
and transdate<='2013-05-15 00:00:00.000'
and transdate>='2013-05-09 00:00:00.000'-------------Result is 40.0 hours in QA Timesheet as well as in Production PPA_WIP Table

select prid,prname from niku.niku.prtask
where prprojectid in(select id from inv_investments where code in ('PR01679'))
and prid in (5136346)---Total 1 task from 1 against time charged 40.0 hours for 15th may week ending date


select * from prassignment
where prtaskid in ( select prid from niku.niku.prtask
where prprojectid in(select id from inv_investments where code in ('PR01679'))
and prid in (5136346))
and prresourceid=5073755-------1 row for the resource id of the resource 'vcc44264'

select SUM(PRACTSUM/3600) from prassignment
where prtaskid in ( select prid from niku.niku.prtask
where prprojectid in(select id from inv_investments where code in ('PR01679'))
and prid in (5136346))
and prresourceid=5073755-------Total Actual Hours for the resource 'vcc44264' is 322 for above 1 task till 09/04/2013. Once we do reverse, it took out data from actual table

4 -Reversed the transactions of the resource A for the same time period.
5- Approved the reversed transaction via Approved WIP adjustment GUI
6- Re validate the below queries


select * from ppa_wip
where resource_code='vcc44264'
and transdate<='2013-05-15 00:00:00.000'
and transdate>='2013-05-09 00:00:00.000'----5 rows before reverse transactions

select * from niku.PAC_IMP_ACTUALS_EXPORT
where prresourceid=5073755 ---created reverse entry for the same resource 'vcc44264' with - 40 hours

7- Open Time period for the same week
8- XOG IN the time sheet of resource A with 40 hours for the same time period
9- Time sheet created successfully with posted status
10- Close the time period again

Still we are not able to see the updated records in following tables..

1- "Create WIP Adjustment" GUI is not displaying the newly XOG in posted time sheet entries . Also WIP entries have been disappeared after reverse action from GUI while PPA_WIP has same records as before and after reverse WIP transactions
2- PR assignment table actual column is not showing the addition of newly entered 40 hours for the same time period. As this amount has been deducted after reverse WIP entries


Please help us if we are missing some other steps. Do we need to keep time period open in order to see recently xog in time sheet data in other related tables?

Thanks.