Note – this script should only be used if the SedonaOffice system has caused a problem on a timesheet. This script should not be used to correct data entry errors the customer has done. If a customer enters the time wrong they should correct this by editing the timesheet through the front end, or adding a manual journal entry if the timesheet cannot be edited.
If the SedonaOffice system has caused an error in timesheets, the steps below will help in finding where and how to fix the bad record.
Overview
An example of timesheets from an actual job is listed below that will be used for the example scripts.
The last timesheet showing 11.48 units and should be 10.0 units.
Steps:
1. Selects to find records to fix in all the tables
SELECT * FROM OE_Job WHERE Job_code = '2331'
-- Job_Id = 2043
SELECT * FROM OE_Job_Schedule WHERE Job_Id = 2043
-- Installer_Id = 54 -- need to look at what the Job shows
SELECT * FROM OE_Job_TimeSheet WHERE Job_Id = 2043 AND Installer_Id = 54
-- Job_TimeSheet_Id = 725
-- Register_Id IN (5357945,5357946)
SELECT * FROM GL_Register WHERE Job_Id = 2043 AND Register_Id IN (5357945,5357946)
Example of all scripts above results:
2. Backup scripts for all tables that needs changed
SELECT * INTO oejobsch_SV#### FROM OE_Job_Schedule WHERE Job_Id = 2043
SELECT * INTO oejobtimesheet_SV#### FROM OE_Job_TimeSheet WHERE Job_Id = 2043 AND Job_TimeSheet_Id = 725
SELECT * INTO glreg_SV#### FROM GL_Register WHERE Job_Id = 2043 AND Register_Id IN (5357945,5357946)
** Replace SV#### with the SV[case_number]
3. Update scripts to fix the errors in the tables
UPDATE OE_Job_Schedule SET Hours = '10' WHERE Job_Id = 2043 AND Job_Schedule_Id = 40
UPDATE OE_Job_TimeSheet SET Work_Date = '2021-09-06',Units = '8.5',Amount = '212.50' WHERE Job_Id = 2043 AND Job_TimeSheet_Id = 725
UPDATE GL_Register SET Date = '2021-09-06',Amount = '212.50' WHERE Job_Id = 2043 AND Register_Id IN (5357945,5357946)
After updates are complete, go back into the job and verify the timesheet is showing correctly.
