Import Financial Actuals job does negative transactions that can cause Project actuals to be out of sync

Document ID : KB000022819
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue:

The application is transaction-centric and all units and costs actuals are created in the system through transaction entry records from timesheets, XOG or Transaction Entry Vouchers. The user is allowed to enter a Transaction Entry for a specific date. The entry can be a negative value for ANY VALUE of units, rates, and costs. When the Voucher Transaction Entry is posted to WIP, a record is created in the PAC_IMP_ACTUALEXPORT table with the correct values. When the 'Import Financial Actuals' job executes the entry is processed and if no errors are encountered the record is removed from the table. This is a problem when the transaction entry is a NEGATIVE value and the value is GREATER than the positive actual values that already exist in the project for that transaction date, resource, task combination. The record is processed by negating only the amount that a positive value already exists and no error message or information message is returned to the table. The record is removed.  This leaves the project actual units to only go down to zero and thus be out of sync with the total amount in WIP.

Steps to Reproduce:

  1. Create Project with financial properties (associate rate matrix, etc)
  2. Assign a Resource with active financial properties to the project task
  3. Create a timesheet for the Resource on the project for 1 week - 8 hours a day for this task for a total of 40 hours for the week
  4. Submit, and approve the timesheet
  5. (wait 5 minutes) Execute 'Post Timesheets' job
  6. Execute 'Post Financial Transactions' job (check invalid transactions page to ensure that transactions were removed - successful)
  7. Navigate to Main Application, Post to WIP and post the transactions
  8. Check posted transactions - example : there will be 5 transactions from the timesheet for
    FOR EACH DAY (5 records)
    Units = 8
    Rate per unit = 100
    Cost per unit = 10
  9. Navigate to Main Application > Transaction Entry
  10. Create a single transaction entry for this project, task, resource for 1 of the dates used on the timesheet. On this transaction enter the following values:
    Create 1 Record for 1 of the dates used above
    Units = -40
    Rate = -100
    Cost = -10
  11. Post the (Voucher) Transaction Entry to WIP
  12. Check the PAC_IMP_ACTUALEXPORT table for the record and see that the negative values appear
  13. Execute the 'Import Financial Actuals' job
  14. Check the PAC_IMP_ACTUALEXPORT table - the record has been removed
  15. Check the Project - the total amount of actuals has been only reduced by 8 because only 8 hours exist for the specific date that the negative transaction was applied.

Expected Result: The application should report that the transactional data was not completely applied to the project because there was no matching transaction to offset the value.
Actual Result: No Error messages are reported, the record is removed from the table, and the financial data is out-of-sync with the overall Project actuals.

 

Cause:

The Project Actual Units does not allow a negative quantity.  Therefore, when the original transaction is posted into the project it sets the amount to zero.  Then any subsequent adjustments or reversals for that negative amount on the WIP side will cause the posting back to the project to increase the units.  

Resolution:

The application is working within the current design.

At this time, due to the current design, there is no way to synchronize the data between the project and WIP data.  On synchronized transactions, the end-user must ensure that the incoming data on a Transaction Entry will have existing positive values on the project greater or equal to the Transaction Entry for a negative value. (compare using absolute values: for example - the project should have a minimum value of +10 on a specific date if the new Transaction Entry has a value of -10).

Below are recommendations on how to perform adjustments properly in the future.  Performing these actions on the data already out of sync will not balance the amounts.

Recommended Method for quantity adjustments:

Typically, you can simply adjust the existing source transactions from Timesheets down to zero and process the transaction into WIP.  Alternatively, you can create a WIP Adjustment on the existing WIP record down to zero and this would allow for correct adjustments.   

Steps to make new transactions to negate/offset matching existing transactions:

The steps below are for creating a new voucher transaction entry to offset the existing WIP records so that both the original and the offset transactions are current records shown in the application. When using this method, you have to ensure that you find the existing transactions and enter the new transaction with the same specific details on the transactions, entering amounts that would total no less than zero.

  1. Use the 'Posted Transaction Review' portlet to identify the specific project, task, resource, date and quantities for individual transactions
  2. Create the Voucher Transaction Entry record with these same details and enter in a negative quantity to negate the original transaction - being careful NOT to enter a negative quantity more than the postive quantity (i.e. if you have a positive value of 8, enter only up to -8 on the negative quantity for a total that does not go below zero)
  3. Post the transaction into WIP
  4. Now there will be 2 current records shown - the original positive quantity and the new negative quantity ; when summed, the amount is reduced to zero.

Additional Information:

An idea has been logged within the communities, please add your comments and vote for it: Ability to adjust Actual Hours entered through Transaction Entry 

Reference TEC536102 : PPM and OWB: Negative Transaction adjustment will cause bad data on assignment Actual Cost field