While the General Ledger Year-End Close routine can delete unused segments, during implementation, or creation of new companies , we sometimes end up with segments created which are not needed. The below script can be used to remove all segments not assigned to an account (segments which have been used will not be removed).
The script allows the user to define which segment should be removed by changing the highlighted parameter:
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ DECLARE @SGMTNUMB AS VARCHAR(2) = 3 DELETE FROM GL40200 WHERE SGMTNUMB = @SGMTNUMB AND SGMNTID NOT IN ( SELECT CASE @SGMTNUMB WHEN 1 THEN GL100.ACTNUMBR_1 WHEN 2 THEN GL100.ACTNUMBR_2 WHEN 3 THEN GL100.ACTNUMBR_3 WHEN 4 THEN GL100.ACTNUMBR_4 WHEN 5 THEN GL100.ACTNUMBR_5 WHEN 6 THEN GL100.ACTNUMBR_6 WHEN 7 THEN GL100.ACTNUMBR_7 WHEN 8 THEN GL100.ACTNUMBR_8 WHEN 9 THEN GL100.ACTNUMBR_9 WHEN 10 THEN GL100.ACTNUMBR_10 END FROM GL00105 AS GL105 INNER JOIN GL00100 AS GL100 ON GL100.ACTINDX = GL105.ACTINDX ) GO