Import Financial Actuals job does negative transactions that can cause Project actuals to be out of sync
Article ID: 22819
Updated On:
Products
Issue/Introduction
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:
- Create Project with financial properties (associate rate matrix, etc)
- Assign a Resource with active financial properties to the project task
- 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
- Submit, and approve the timesheet
- (wait 5 minutes) Execute 'Post Timesheets' job
- Execute 'Post Financial Transactions' job (check invalid transactions page to ensure that transactions were removed - successful)
- Navigate to Main Application, Post to WIP and post the transactions
- 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 - Navigate to Main Application > Transaction Entry
- 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 - Post the (Voucher) Transaction Entry to WIP
- Check the PAC_IMP_ACTUALEXPORT table for the record and see that the negative values appear
- Execute the 'Import Financial Actuals' job
- Check the PAC_IMP_ACTUALEXPORT table - the record has been removed
- 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.
Environment
Component:
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.
- Use the 'Posted Transaction Review' portlet to identify the specific project, task, resource, date, and quantities for individual transactions
- If you cannot match a specific quantity you may have to query to actual daily slice to find the amounts which need to be fixed
- 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 positive 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)
- Post the transaction into WIP
- Run Import Financial Actuals job to make the assignment quantity correct.
- Pause the Import Financial Actuals job
- Reverse the transaction you just did so now WIP is correct.
- Have that record removed from the import table (pac_imp_actuals_export) so it does not import back to the project again.
Now assignments have been corrected and WIP is correct.
There is no way to fix this except to not import the record again or WIP and assignment will always be out of sync.
Additional Information
Feedback
