Two stored procedures could be modified to only process the lines where time has actually been entered on.
The first stored procedure is zDP_PTE10001L_1 by adding an additional AND clause "and pte_quantity<>0 " which will remove the lines that do not have time on them from being considered.
The second stored procedure is a temporary stored procedure that gets generated in the dex code. In this particular instance, the stored procedure is ##zDP_2113547F_1.
When executing that stored procedure , you get results which includes all the lines regardless if any time has been entered for them…
If you modify the stored procedure to include a where clause
to exclude results from the pte_qty_week_x columns that equal 0
you receive significantly less results.
By making the changes, GP will only process the projects that have time applied to them.
You can further optimize the process by only processing the actual day if pte_qty_week_1 through 7 is greater than zero. In a sample dex trace, GP calls a lot of stored procedures for each day that scan a lot of PA tables even if no time has been entered for it. For example,it will will loop through each call 7 times for the one line that the employee has a pay code on even though he did not even use it.
Comments
We noticed this issue when upgrading to GP 2018 R2 from 2015 R2 where the Timesheets will include extra lines for 0 QTY line items in the PTE100001 table
Example:
User enters 8 hours of personal time for 1 day (Example Monday), instead of 1 line item for 8 hours, there are 6 (0) QTY line items (For Tuesday - Sunday) for and 1 line for the 8 hours personal time.
I was considering a Trigger to remove these extra items to see if that would be a possible work around. It would be interesting to see if these extra lines also posted thru to the PA table after approval.
We also have poor performance when bring up Timesheets and Expense Reports to be approved. We were up 5 minutes due to bug in GP 2015 R2 but its down to 40 seconds which is better but still needs improvement.
Category: Project Accounting
Theresa Nistler (administrator)
Thank you for your suggestion. We will consider this functionality for a future release.
Best regards.