CLIENT SUPPORT » ROHTO BUGS

USE [MinovaES_Rohto_Prod]

GO

/****** Object:  StoredProcedure [dbo].[TIMECONSMD]    Script Date: 23/08/2021 12.10.56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[TIMECONSMD]

 @reg_id nvarchar(MAX) ,

 @empid_requester nvarchar(8)

AS

BEGIN

DECLARE @MD NVARCHAR(100)

DECLARE @now NVARCHAR(8)

DECLARE @value NVARCHAR(100)

DECLARE @Time NVARCHAR(4) 

/*SET @reg_id ='122'*/

/*SET @empid_requester='00000002'*/

SET @now =

(

    SELECT dbo.fn_formatdatetime(GETDATE(), 'YYYYMMDD')

) 

DECLARE @DynamicSQL NVARCHAR(1000)

DECLARE @Table NVARCHAR(30) =

        (

            SELECT TOP (1)

                   TableIn

            FROM dbo.PCMWFDEFFIELDPOS

            WHERE BizFlowID =

            (

                SELECT BizflowID FROM dbo.PCMWFRUNMAIN WHERE RegID = @reg_id

            )

        )

SET @MD =

(

    SELECT MdSubFieldName FROM dbo.PCMEPMDCON WHERE TableName = @Table

)

SET @Time =

(

    SELECT TimeConstraint FROM dbo.PCMEPMDCON WHERE TableName = @Table

)

IF @MD <> ''

BEGIN

    SET @value =

    (

        SELECT Value

        FROM dbo.PCMWFRUNBIZDATA

        WHERE Value IN

              (

                  SELECT MdSub

                  FROM dbo.PCMEPMDCONSUB

                  WHERE TableName = @Table

                        AND TimeConstraint = 'N2'

              )

              AND FieldID = @MD

              AND RegID = @reg_id

              AND Sequence =

              (

                  SELECT MAX(Sequence) FROM dbo.PCMWFRUNBIZDATA WHERE RegID = @reg_id

              )

    )

    IF @value IS NOT NULL

       OR @value <> ''

    BEGIN

        SET @DynamicSQL

            = N'UPDATE ' + @Table + N' SET EndDate = ''' + @now + N''' WHERE EmployeeID= ''' + @empid_requester

              + N''' AND ' + @MD + N'=''' + @value + N''' '

    END

END

ELSE IF @Time = 'N2'

BEGIN

    SET @DynamicSQL

        = N'UPDATE ' + @Table + N' SET EndDate = ''' + @now + N''' WHERE EmployeeID= ''' + @empid_requester

          + N''' AND EndDate =''99991231'' '

END

EXEC (@DynamicSQL)

SELECT 1 AS res

END