/****** Object:  StoredProcedure [dbo].[workorders_upsert]    Script Date: 4/27/2023 11:23:26 AM ******/
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO




ALTER   PROCEDURE [dbo].[workorders_upsert]
  @workorders maximo_workorder_list READONLY
AS
BEGIN
  SET NOCOUNT ON;



  DECLARE @result INT = -1;
  DECLARE @wonum NVARCHAR(20);
  DECLARE @woclass NVARCHAR(16);
  DECLARE @wopriority INT;
  DECLARE @plusunit NVARCHAR(12);
  DECLARE @location NVARCHAR(40);
  DECLARE @worktype NVARCHAR(5);
  DECLARE @bpucr NVARCHAR(10);
  DECLARE @status NVARCHAR(16);
  DECLARE @statusdate DATETIME;
  DECLARE @description NVARCHAR(100);
  DECLARE @bpjobtype NVARCHAR(4);
  DECLARE @siteid NVARCHAR(8);
  DECLARE @plusoutagecode NVARCHAR(12);
  DECLARE @istask INT;
  DECLARE @taskid NVARCHAR(12);
  DECLARE @parent NVARCHAR(20);
  DECLARE @pmnum NVARCHAR(10);
  DECLARE @plusdeaddate DATETIME;
  DECLARE @pmduedate DATE;
  DECLARE @jpnum NVARCHAR(12);
  DECLARE @bpengmodtype NVARCHAR(4);
  DECLARE @pluspmod NVARCHAR(25);
  DECLARE @fincntrlid NVARCHAR(8);
  DECLARE @plusasfound NVARCHAR(16);
  DECLARE @actfinish DATE;
  DECLARE @workorderid DECIMAL(18, 0);
  DECLARE @bpplanningctr NVARCHAR(10);
  DECLARE @ldtext NVARCHAR(MAX);
  DECLARE @is_deleted BIT;
  DECLARE @todayDate DATETIME = GETDATE();
  DECLARE @NULL_END_DATE DATETIME = '9999/12/31';
  DECLARE @scd_start DATETIME;
  DECLARE @current_scd_start DATETIME;
  DECLARE @previous_record_scd_end DATETIME;



  DECLARE upsert_cursor CURSOR FOR
    SELECT
        wonum,
      woclass,
      wopriority,
      plusunit,
      location,
      worktype,
      bpucr,
      status,
      statusdate,
      description,
      bpjobtype,
      siteid,
      plusoutagecode,
      istask,
      taskid,
      parent,
      pmnum,
      plusdeaddate,
      pmduedate,
      jpnum,
      bpengmodtype,
      pluspmod,
      fincntrlid,
      plusasfound,
      actfinish,
      workorderid,
      bpplanningctr,
      ldtext,
      is_deleted,
      scd_start
    FROM @workorders;



  BEGIN TRY
    OPEN upsert_cursor;
    FETCH NEXT FROM upsert_cursor INTO
        @wonum,
      @woclass,
      @wopriority,
      @plusunit,
      @location,
      @worktype,
      @bpucr,
      @status,
      @statusdate,
      @description,
      @bpjobtype,
      @siteid,
      @plusoutagecode,
      @istask,
      @taskid,
      @parent,
      @pmnum,
      @plusdeaddate,
      @pmduedate,
      @jpnum,
      @bpengmodtype,
      @pluspmod,
      @fincntrlid,
      @plusasfound,
      @actfinish,
      @workorderid,
      @bpplanningctr,
      @ldtext,
      @is_deleted,
      @scd_start;



    WHILE @@FETCH_STATUS = 0
    BEGIN
      BEGIN TRANSACTION;



        SELECT @current_scd_start = MAX([SCD_START])
        FROM maximo_workorder 
        WHERE
          wonum = @wonum AND
          workorderid = @workorderid AND
          IS_CURRENT = 1 AND
          SCD_END = @NULL_END_DATE



      SELECT @previous_record_scd_end = MIN([SCD_END]) 
        FROM maximo_workorder
          WHERE
           wonum = @wonum AND
          workorderid = @workorderid AND
          IS_CURRENT = 0 AND
          SCD_END > @scd_start
      
      IF(@scd_start > @current_scd_start or @current_scd_start is null)
      BEGIN




        UPDATE maximo_workorder
          SET 
            SCD_END = @scd_start, 
            IS_CURRENT = 0
          WHERE 
            wonum = @wonum AND
            workorderid = @workorderid AND
            IS_CURRENT = 1 AND
            SCD_END = @NULL_END_DATE;



        INSERT INTO maximo_workorder (
          wonum,
          woclass,
          wopriority,
          plusunit,
          location,
          worktype,
          bpucr,
          status,
          statusdate,
          description,
          bpjobtype,
          siteid,
          plusoutagecode,
          istask,
          taskid,
          parent,
          pmnum,
          plusdeaddate,
          pmduedate,
          jpnum,
          bpengmodtype,
          pluspmod,
          fincntrlid,
          plusasfound,
          actfinish,
          workorderid,
          bpplanningctr,
          ldtext,
          SCD_START,
          SCD_END,
          IS_DELETED,
          IS_CURRENT
        ) VALUES (
          @wonum,
          @woclass,
          @wopriority,
          @plusunit,
          @location,
          @worktype,
          @bpucr,
          @status,
          @statusdate,
          @description,
          @bpjobtype,
          @siteid,
          @plusoutagecode,
          @istask,
          @taskid,
          @parent,
          @pmnum,
          @plusdeaddate,
          @pmduedate,
          @jpnum,
          @bpengmodtype,
          @pluspmod,
          @fincntrlid,
          @plusasfound,
          @actfinish,
          @workorderid,
          @bpplanningctr,
          @ldtext,
          @scd_start,
          @NULL_END_DATE,
          @is_deleted,
          1
        );



      END
      ELSE
      BEGIN
        IF (@scd_start < @current_scd_start AND @scd_start NOT IN (SELECT SCD_START FROM maximo_workorder WHERE wonum = @wonum AND workorderid = @workorderid))
        BEGIN
          UPDATE maximo_workorder
            SET 
              SCD_END = @scd_start
            WHERE 
              wonum = @wonum AND
              workorderid = @workorderid AND
              IS_CURRENT = 0 AND
              SCD_END = @previous_record_scd_end;



          INSERT INTO maximo_workorder (
            wonum,
            woclass,
            wopriority,
            plusunit,
            location,
            worktype,
            bpucr,
            status,
            statusdate,
            description,
            bpjobtype,
            siteid,
            plusoutagecode,
            istask,
            taskid,
            parent,
            pmnum,
            plusdeaddate,
            pmduedate,
            jpnum,
            bpengmodtype,
            pluspmod,
            fincntrlid,
            plusasfound,
            actfinish,
            workorderid,
            bpplanningctr,
            ldtext,
            SCD_START,
            SCD_END,
            IS_DELETED,
            IS_CURRENT
          ) VALUES (
            @wonum,
            @woclass,
            @wopriority,
            @plusunit,
            @location,
            @worktype,
            @bpucr,
            @status,
            @statusdate,
            @description,
            @bpjobtype,
            @siteid,
            @plusoutagecode,
            @istask,
            @taskid,
            @parent,
            @pmnum,
            @plusdeaddate,
            @pmduedate,
            @jpnum,
            @bpengmodtype,
            @pluspmod,
            @fincntrlid,
            @plusasfound,
            @actfinish,
            @workorderid,
            @bpplanningctr,
            @ldtext,
            @scd_start,
            COALESCE(@previous_record_scd_end, @current_scd_start),
            @is_deleted,
            0
          );
        END
        ELSE
        BEGIN



          UPDATE maximo_workorder
          SET
          wonum = @wonum,
          woclass = @woclass,
          wopriority = @wopriority,
          plusunit = @plusunit,
          location = @location,
          worktype = @worktype,
          bpucr = @bpucr,
          status = @status,
          statusdate = @statusdate,
          description = @description,
          bpjobtype = @bpjobtype,
          siteid = @siteid,
          plusoutagecode = @plusoutagecode,
          istask = @istask,
          taskid = @taskid,
          parent = @parent,
          pmnum = @pmnum,
          plusdeaddate = @plusdeaddate,
          pmduedate = @pmduedate,
          jpnum = @jpnum,
          bpengmodtype = @bpengmodtype,
          pluspmod = @pluspmod,
          fincntrlid = @fincntrlid,
          plusasfound = @plusasfound,
          actfinish = @actfinish,
          workorderid = @workorderid,
          bpplanningctr = @bpplanningctr,
          ldtext = @ldtext
          WHERE wonum = @wonum AND
              workorderid = @workorderid AND
              SCD_START = @scd_start
        END
      
      END



      COMMIT TRANSACTION;



      FETCH NEXT FROM upsert_cursor INTO
        @wonum,
        @woclass,
        @wopriority,
        @plusunit,
        @location,
        @worktype,
        @bpucr,
        @status,
        @statusdate,
        @description,
        @bpjobtype,
        @siteid,
        @plusoutagecode,
        @istask,
        @taskid,
        @parent,
        @pmnum,
        @plusdeaddate,
        @pmduedate,
        @jpnum,
        @bpengmodtype,
        @pluspmod,
        @fincntrlid,
        @plusasfound,
        @actfinish,
        @workorderid,
        @bpplanningctr,
        @ldtext,
        @is_deleted,
        @scd_start;
    END;



    SET @result = 0;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;
  END CATCH;



  CLOSE upsert_cursor;
  DEALLOCATE upsert_cursor;



  RETURN @result;
END;



GO