GL Major/Minor Report Categories SQL Fix

1. Delete all Account Part Instances with a dot remaining in the instance number. Once you confirm that the correct rows will be delete, change to commit, run the script, and change back to rollback.

BEGIN TRANSACTION 

DELETE FROM GLPI WHERE Instance LIKE '%.%' 

SELECT * 

FROM GLPI WHERE Instance LIKE '%.%' 

ROLLBACK TRANSACTION 

2. When the chart of accounts is inserted prior to the code format being set to the correct format, the part numbers on the chart of accounts are incorrect. We use the following script to correct the part numbers:

BEGIN TRAN 

UPDATE ac SET ac.Part1 = p.Part1, ac.Part2 = p.Part2, ac.Part3 = p.Part3, ac.AllParts = p.Part1 + p.Part2 + p.Part3  

--SELECT ac.*, p.* FROM GLAC ac OUTER APPLY ssfPartsFromAccount(ac.GLAcct) p 

--WHERE GLCo=1 

SELECT * FROM GLAC 

WHERE Part1 LIKE '%.%' AND Part2 LIKE '%.%' 

ROLLBACK TRAN

For script used to create the function: vista-reports\resource\silvertrek-toolbox\Vista Implementations\FixAccountParts.sql 

For script used with the trial balance GL mapping template:

vista-reports\resource\silvertrek-toolbox\Vista Implementations\GLMappingTempTableProcess.sql