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 (https://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
Can ISC Software Solutions help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
1 thought on “SQL Script to Delete Unused Segments”