Global maintenance script
This script puts each piece of rental equipment into and out of maintenance for start of season operation. This functionality works only on equipment that is currently checked-in – not Out, Sold, Lost, Stolen, Stored, etc. You have to find out the maintenance reason code you want to use before running this script (and edit the script to put that number in). Find out which maintenance ID to use by running the following script: SELECT * FROM r_mreasn.
DECLARE @Tally INT, @Equip_tag BIGINT, @LastEquipTag BIGINT,
@SiteNo INT, @Count INT, @TrackNo INT,
@RunTime DATETIME, @MaintReasonID INT,
@NewTrackNo BIGINT
SET NOCOUNT ON
SET @MaintReasonID = 21 -- NOTE: Change this to what you want - see instructions above
SET @Tally = 1
SET @Count = 0
SET @LastEquipTag = -99999999
SET @RunTime = CURRENT_TIMESTAMP
SELECT @SiteNo = site_no FROM Prefs
WHILE @Tally > 0
BEGIN
SELECT TOP 1 @Equip_Tag = ri.equip_tag
FROM r_invent ri
WHERE ri.status = 'I' AND ri.equip_tag > @LastEquipTag
ORDER BY ri.equip_tag
SET @Tally = @@RowCount
IF @Tally > 0
BEGIN
SET @LastEquipTag = @Equip_tag
SET @Count = @Count + 1
EXEC siriussp_get_unique_key2 'K_R_HIST', @TrackNo OUTPUT
SET @NewTrackNo = @TrackNo
SET @NewTrackNo = @NewTrackNo * 1000000 + @SiteNo
INSERT INTO r_hist (track_no, form_no, equip_tag, activ_type, descrip, notes,date_time, operator, salespoint, reason_id)
VALUES (@NewTrackNo, 0, @Equip_tag, 61, 'Maint-IN', '', @RunTime, 'UTIL', 'UTIL', @MaintReasonID)
EXEC siriussp_get_unique_key2 'K_R_HIST', @TrackNo OUTPUT
SET @NewTrackNo = @TrackNo
SET @NewTrackNo = @NewTrackNo * 1000000 + @SiteNo
INSERT INTO r_hist (track_no, form_no, equip_tag, activ_type, descrip, notes,date_time, operator, salespoint, reason_id)
VALUES (@NewTrackNo, 0, @Equip_tag, 65, 'Maint-OUT', '', @RunTime, 'UTIL', 'UTIL', @MaintReasonID)
UPDATE r_invent SET datelmaint = @RunTime, rentsmaint = 0 WHERE equip_tag = @Equip_tag
END
END
PRINT ''
PRINT 'Finished. ' + CONVERT(varchar(20), @Count) + ' pieces of rental equipment run through maintenance.'
SET NOCOUNT OFF