How do you trace a row posted into PPA_WIP from a resource timesheet back to the original timesheet record in PRTimeSheet?
When a Timesheet Entry has been exported into the Financial Module for processing, the entry record will have the following field set to true
- prtimeentry.prrmexported = 1
The prtimeentry.prid record contains segmented entries for the cells in the timesheet. Therefore, one record in the PRTimeEntry table can represent more than one record in the financial PPA_WIP table.
Once the time entries have been exported into the financial module, the transactions are processed and posted into WIP. Once the transactions are in WIP, you can then compare the timesheet posted hours versus the financial posted hours.
The PPA_WIP record has fields that help identify if it came from a timesheet entry record:
- ppa_wip.sourcemodule = 51
** Note: The 51 is an indicator that this came from posted timesheets.
- ppa_wip.externalbatchid = prtimeentry.prtimesheetid
** Note: this is the link for the financial transaction to the Timesheet Record ID
- ppa_wip.external_id = prtimeentry.prid
** Note: this is the link for the financial transaction to the Timesheet Entry (row) record ID
To compare actual hours on a posted timesheet to the posted financial transactions, you can sum up the financial transactions and the timesheet entries and group by Timesheet for that resource.
- sum(ppa_wip.quantity) = sum(prtimeentry.practsum) / 3600 if value hasn't been adjusted in any way.
Here is a sample query (for Oracle) that will show the total timesheet hours and total WIP hours grouped by Timesheet, Resource.
select t.tsprid, t.prstatus, t.teprid, t.prtimeperiodid, t.prstart, t.prfinish, t.prresourceid, t.TIME_QTY, 'PPA_WIP' Fin_Table
, sum(w.quantity) fin_qty
select ts.prid tsprid, ts.prstatus, te.prid teprid, ts.prtimeperiodid, tp.prstart, tp.prfinish, ts.prresourceid, (te.practsum/3600) TIME_QTY
from prtimesheet ts, prtimeentry te , prtimeperiod tp
AND TS.PRID = TE.PRTIMESHEETID
AND TS.PRTIMEPERIODID = TP.PRID
AND TS.PRSTATUS = 4
AND TE.PRRMEXPORTED = 1
group by ts.prid, ts.prstatus, te.prid, ts.prtimeperiodid, tp.prstart, tp.prfinish, ts.prresourceid, (te.practsum/3600 )
) t left outer join ppa_wip w on t.teprid = w.external_id
where NVL(w.status,0) <> 2
group by t.tsprid, t.prstatus, t.teprid, t.prtimeperiodid, t.prstart, t.prfinish, t.prresourceid, t.TIME_QTY, 'PPA_WIP'