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
ALTER 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