Depending on whether the reconcile utility has been run or not, choose from the resolution below that fits your scenario:
RESOLUTION 1 If you have not ran the reconcile utility yet, then install R2 for BP 5.1 to resolve this issue. However, if you are using BP 5.0 or are not able to update to R2 for BP 5.1 yet, then use the steps below to create a trigger on the TATX1030 HR table that will correct the format on the time-stamp, so the system will see a link between the transactions created in the UPR10302 payroll and TATX1030 HR tables.
1. Open a query window in SQL Server Management Studio using the appropriate method below:
- If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
- If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start , point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
- If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. Click on the
New Query button at the top to open a query window.
3. Copy in the script below and execute it against the company database to create the trigger.
create trigger tr_CorrectTS
create trigger tr_CorrectTS
on TATX1030
FOR INSERT as if (select DATEPART(ms,CHANGETIME_I) from inserted) > 0
begin update TATX1030 set CHANGETIME_I = CONVERT (varchar , CHANGETIME_I, 120 )
where DATEPART(ms,CHANGETIME_I) > 0
end
go
Important Note: When you install the fix in R2 for BP 5.1, you will need to remove this trigger from the TATX1030 table. See the MORE INFORMATION section below.
RESOLUTION 2
If you already ran the reconcile option and a RECON batch was created in Payroll and the attendance transations duplicated in the TATX1030 table, then follow these steps:
1. Open a query window in SQL Server Management Studio using the appropriate method below:
- If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start , point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
- If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
**Please make a current backup of the company database before continuing with these steps.**
2. Click on the
New Query button at the top to open a query window.
3. Run this script in SQL Server Management Studio against the company database to correct the timestamp in the TATX1030 table so the system sees the records as matching between the HR and Payroll tables: (This will also prevent these transactions from being put into a RECON batch again, in case the reconcile utility is run for any reason.)
update TATX1030 set CHANGETIME_I = CONVERT (varchar , CHANGETIME_I, 120 )where DATEPART(ms,CHANGETIME_I) > 0
4. For BP 5.1 (64 bit environments0 update Business Portal to Release/Service Pack 2 to get this issue fixed:
CustomerSourcehttps://mbs.microsoft.com/customersource/downloads/servicepacks/mdbp51_patchreleases.htm?printpage=falsePartnerSourcehttps://mbs.microsoft.com/partnersource/downloads/servicepack/mdbp51_patchreleases.htm?printpage=falseNOTE: If you are using BP 5.0 or are not able to update to BP 5.1 R2 yet, you will need to run the script below to get around this issue: Run this script against the company database to create a corresponding trigger that will correct the format on the timestamp for all future records inserted into this table:
create trigger tr_CorrectTS
on TATX1030
FOR INSERT
as
if (select DATEPART(ms,CHANGETIME_I) from inserted) > 0
begin
update TATX1030 set CHANGETIME_I = CONVERT (varchar , CHANGETIME_I, 120 ) where DATEPART(ms,CHANGETIME_I) > 0
end
go
Important Note: When you update to R2 for BP 5.1, you will need to remove this trigger from the TATX1030 table. See the MORE INFORMATION section below.
5. In Microsoft Dynamics GP, print the Employee Attendance Detail and Employee Attendance Summary reports at this point in time. To do this, click on
Reports, point to
Human Resources and click on
Attendance. In the Attendance Report Options window, create a new option and select each Report respectively. Print both attendance reports for reference at this point in time.
6. Determine if the attendance balances are correct for the employee or not, and then follow the appropriate method below:
Method 1 - If the attendance balances are overstated by the duplicate transaction amount, then just delete the RECON batch in GP, and this will delete the transactions it created out of the TATX1030 and also adjust the overall attendance balance. (Note: the balances still may not be ‘correct’.) If the balances are not correct at this point in time, then delete the RECON batch in GP and steps to correct the balances will be in a later step (Step 7).
If you followed Method 1, then print the Employee Attendance Detail and Employee Attendance Summary reports again to verify the balances changed as expected. (See step 5.)
Method 2 - If the attendance balances are correct, then you will need to remove the RECON Batch directly in SQL and cannot remove them in GP, as removing them in GP would cause the attendance balance to get updated incorrectly. To not affect the attendance balance, you will have to remove the RECON batch directly in SQL by running both scripts below against the company database:
DELETE FROM UPR10301 WHERE BACHNUMB ='RECON'
DELETE FROM UPR10302 WHERE BACHNUMB ='RECON'
In Microsoft Dynamics GP, verify that the RECON batch is no longer listed in the Payroll Batch window.
7. If the attendance balances are still incorrect and duplicate entries exist in the TATX1030 table, you can use one of the methods below to correct the balances:
Method 1 (Supported) - Leave the duplicate entries in the TATX1030 table. Then you can key offsetting Hours Available Adjustment entries to offset these duplicate transactions. This will bring the balance back to where it should be, and also leave an audit trail of what happened. This will also adjust the Hours Available Attendance balance stored in the TATM1030 table at the same time.
To do this, click on
Transactions, point to
Human Resources, and click on
Transaction Entry. Enter the
Employee ID and
Time Code. For Transaction Type, select
Hours Available Adjustment. Enter the hours as a positive to increase the attendance balance, or a negative to decrease the attendance balance as needed. Enter a Start Date that has the same current year to also adjust the hours that are currently tracked against the yearly maximum defined on the accrual setup. Enter a Reason and Type and
Save.
Method 2 (Not supported) - Or if you decide that you want to remove the duplicate transactions from the TATX1030, you may decide to write a script that uses a unique field to identify these transactions. (Use a date range, along with the CHANGEBY_I column as part of the script. If you need the script written for you, please contact Microsoft Professional Services for a consulting service.) After removing the duplicate transactions, you will want to figure out the employee's true attendance balances and then also update the balance directly in the TATM1030 table. (In the TATM1030 table, you may want to edit the HOURSAVAILABLE_I, HOURS_I, DAYSWRKD and WKSWRKD columns appropriately.)
8. Print the Employee Attendance Detail and Employee Attendance Summary reports again (see step 5) to verify the balances are correct at this point in time. Verify that the total of the Detail report matches the Attendance Summary report for each employee.
*It is possible that the detail did not match the summary even prior to this time. If so, you may need to investigate which report is correct. You may need to key additional HR adjustment entries and/or update the Hours Available balance directly in the TATX1030 table to match the detail report. The goal is to have the detailed transactions in sync with the overall Hours Available balance, if you utilize both reports or want to see what detail makes up the hours available balance at any time. (Note: if you don't use the attendance detail report at all, then just verify that the hours available balance in the TATM1030 is correct.)
9. If you have a Yearly Maximum defined on the accrual code setup, it is also recommended to verify that the hours tracked against this maximum are correct to date. To do this, click on
Cards, point to
Human Resources, point to
Employee - Attendance and click on
Summary. Enter the
Employee ID and click on
Yearly Accruals. The Employee Yearly Accruals window is a tracking window only, where the system tracks how many hours have been accrued so far in the calendar year. Verify these totals are correct for each attendance Time Code, so the employee does not max out too early in the year, or go over the alotted maximum hours. This is just a tracking window, so you can edit freely in this window as you wish, and it will not affect the current balance or Hours Available for this time code.
10. If you are using BP 5.1, this issue has been fixed in Release 2 for Business Portal. When you install the fix, you will want to be sure to remove this trigger from the TATX1030 table. See the MORE INFORMATION section below for a script to do this, and also make yourself a note or reminder so you don't forget to remove the trigger once the fix is installed.