The Enterprise Scheduler installation provides a SQL stored procedure, SCH_Schedule_Reseed, which will load/reload service call and project data in the Schedule from standard Dynamics GP data. However, it does not load/reload the data from the Dynamics GP Technician Schedule table, SVC00101. In order to load/reload these events in the Schedule, the following script can be used to create a SQL stored procedure, SVC_Schedule_Reseed_Tech_Events. Once the stored procedure is created, the script can be run with the command ‘EXEC SCH_Schedule_Reseed_Tech_Events’. This script should be incorporated in the next release of the Enterprise Scheduler.
/****** Object: StoredProcedure [dbo].[SCH_Schedule_Reseed_Tech_Events] Script Date: 11/17/2008 09:19:54 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[SCH_Schedule_Reseed_Tech_Events]
as
DECLARE @NUMREC INT,
@DEXROWID INT,
@TECHID CHAR(11),
@STRTDATE DATETIME,
@STRTTIME DATETIME,
@ENDDATE DATETIME,
@ENDTME DATETIME,
@TECHSTAT char(11),
@TECHID2 char(11),
@Key1Str CHAR(15),
@Key2Str CHAR(15),
@Key1Int SMALLINT,
@SCH_Display_Name CHAR(31),
@lDummyDate DATETIME
DECLARE TechEventCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT TECHID,
STRTDATE,
STRTTIME,
ENDDATE,
ENDTME,
TECHSTAT,
TECHID2,
DEX_ROW_ID
FROM dbo.SVC00101 WITH (ROWLOCK, UPDLOCK)
OPEN TechEventCursor
FETCH NEXT FROM TechEventCursor INTO @TECHID, @STRTDATE, @STRTTIME, @ENDDATE, @ENDTME, @TECHSTAT, @TECHID2, @DEXROWID
WHILE @@fetch_status = 0
BEGIN
If @TECHSTAT = ‘AVAIL’
BEGIN
return
END
If (@STRTDATE = @lDummyDate or @ENDTME = @lDummyDate)
BEGIN
return
END
Select @NUMREC = Count(*) from SCH_Schedule where Key2Str = rtrim(ltrim(@TECHID)) + rtrim(ltrim(convert(varchar(10), @DEXROWID)))
If @NUMREC > 0
BEGIN
UPDATE SCH_Schedule
SET [ModuleId]= 999,
[Key1Str] = @TECHSTAT,
[Key2Str] = rtrim(ltrim(@TECHID)) + rtrim(ltrim(convert(varchar(10), @DEXROWID))),
[Key1Int] = 2,
[LineSeq] = 1,
[TECHID] = @TECHID,
[StartDate] = @STRTDATE,
[StartTime] = @ENDDATE,
[EndDate] = @ENDDATE,
[EndTime] = @ENDTME,
[SCH_Schedule_Status] = 1,
[SCH_Display_Name] = @TECHSTAT,
[NOTEINDX] = 0.00000
WHERE Key2Str = rtrim(ltrim(@TECHID)) + rtrim(ltrim(convert(varchar(10), @DEXROWID)))
END
ELSE
BEGIN
INSERT INTO SCH_Schedule
([ModuleId]
,[Key1Str]
,[Key2Str]
,[Key1Int]
,[LineSeq]
,[TECHID]
,[StartDate]
,[StartTime]
,[EndDate]
,[EndTime]
,[SCH_Schedule_Status]
,[SCH_Display_Name]
,[NOTEINDX])
VALUES
(
999
,@TECHSTAT
,rtrim(ltrim(@TECHID)) + rtrim(ltrim(convert(varchar(10), @DEXROWID)))
,2
,1
,@TECHID
,@STRTDATE
,@STRTTIME
,@ENDDATE
,@ENDTME
,1
,@TECHSTAT
,0.00000)
END
FETCH NEXT FROM TechEventCursor INTO @TECHID, @STRTDATE, @STRTTIME, @ENDDATE, @ENDTME, @TECHSTAT, @TECHID2, @DEXROWID
END
CLOSE TechEventCursor
DEALLOCATE TechEventCursor
Posted in Enterprise Scheduler
