/*DECLARE @bizprocid VARCHAR(100) = 'ESS_Overtime_AJTM'
DECLARE @reg_id VARCHAR(10) = '55'*/
DECLARE @today VARCHAR(10)
SET @today = dbo.fn_formatdatetime(GETDATE(), 'yyyymm')
DECLARE @DateFrom VARCHAR(10)
DECLARE @DateFromOVT VARCHAR(10)
DECLARE @Sequence VARCHAR(10)
--- start variable ---
SET @Sequence = (SELECT MAX([Sequence]) FROM PCMWFRUNBIZDATA WHERE RegID = @reg_id)
SET @DateFrom = (  SELECT TOP (1)
              SUBSTRING(Value, 1, 6)
        FROM    PCMWFRUNBIZDATA
		WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'DateFrom')

SET @DateFromOVT = (  SELECT TOP (1)
              Value
        FROM    PCMWFRUNBIZDATA
		WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'DateFrom')

--- end variable ---

IF @DateFrom < @today 
BEGIN 

-- start (kondisi ketika  period payroll yang sama maka di delete) redmine 1774 :  4
DECLARE @EmployeeOV AS TABLE
    (
      EmployeeID VARCHAR(MAX)
    )
INSERT  INTO @EmployeeOV
        SELECT Value 
		FROM PCMWFRUNBIZDATA 
		WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'EmployeeID_OT' 

DECLARE @Temp AS TABLE
    (
      EmployeeID VARCHAR(MAX),
	  PayrollGrup VARCHAR(MAX),
	  PayrollPeriod VARCHAR(MAX)
    )
INSERT  INTO @Temp
	SELECT pa02.EmployeeID,
		pa02.PayrollGroup, 
		ctr.PayrollPeriod 
	FROM PHRPA0002 pa02
		INNER JOIN @EmployeeOV emp ON pa02.EmployeeID = emp.EmployeeID
		LEFT JOIN PHRPYPGCTRL ctr ON pa02.PayrollGroup = ctr.PayrollGroup
	WHERE PayrollPeriod >  (SELECT TOP (1)
              SUBSTRING(Value, 1, 6)
        FROM    PCMWFRUNBIZDATA
		WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'DateFrom') and pa02.StartDate <= '20221207' and pa02.EndDate >= '20221207'	

---- end (kondisi ketika  period payroll yang sama maka di delete)

-- start kententuan ketentuan retro
DECLARE @UpdateBizData AS TABLE
    (
      DataSequencer VARCHAR(MAX),
		 FieldID VARCHAR(MAX),
		 DateFrom VARCHAR(MAX),
		  RegID VARCHAR(MAX),
		 [Sequence] VARCHAR(MAX),
		 TableReference VARCHAR(MAX),
		 BizProcessID VARCHAR(MAX)
		 
    )
INSERT  INTO @UpdateBizData
select  DataSequence, FieldID,Value,RegID,[Sequence],TableReference,BizProcessID from PCMWFRUNBIZDATA where  FieldID ='DateFrom2' and BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and DataSequence in (SELECT distinct DataSequence FROM PCMWFRUNBIZDATA WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence  and DataSequence IN ( select DataSequence from PCMWFRUNBIZDATA WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'EmployeeID_OT'  and Value IN (
    SELECT 
      EmployeeID 
    FROM 
      @Temp 
  ) ))

DECLARE @EmployeeDataSeq AS TABLE
    (
      DataSequence VARCHAR(MAX),
		 FieldID VARCHAR(MAX),
		 EmployeeID VARCHAR(MAX)
    )
INSERT  INTO @EmployeeDataSeq
select  DataSequence, FieldID,Value from PCMWFRUNBIZDATA where  FieldID ='EmployeeID_OT' and BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and DataSequence in (SELECT distinct DataSequence FROM PCMWFRUNBIZDATA WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence  and DataSequence IN ( select DataSequence from PCMWFRUNBIZDATA WHERE BizProcessID = @bizprocid and RegID = @reg_id and [Sequence] = @Sequence and FieldID = 'EmployeeID_OT'  and Value IN (
    SELECT 
      EmployeeID 
    FROM 
      @Temp 
  ) ))


UPDATE  @UpdateBizData 
SET     DateFrom =  Case when a.RetroDate > (select @DateFromOVT ) then (select @DateFromOVT )
when a.RetroDate is null  then (select @DateFromOVT )
when a.RetroDate < (select @DateFromOVT) then a.RetroDate
else a.RetroDate END 
FROM    ( SELECT  pa15.EmployeeID,pa15.RetroDate, emp.DataSequence 
FROM PHRPA0015 pa15	
LEFT JOIN @EmployeeDataSeq emp ON pa15.EmployeeID = emp.EmployeeID
where pa15.EmployeeID in (select EmployeeID from @Temp)
        ) a
where a.DataSequence = DataSequencer

UPDATE  @UpdateBizData 
SET     DateFrom = @DateFromOVT
FROM    ( SELECT  pa15.EmployeeID,pa15.RetroDate, emp.DataSequence 
FROM PHRPA0015 pa15	
LEFT JOIN @EmployeeDataSeq emp ON pa15.EmployeeID = emp.EmployeeID
where pa15.EmployeeID in (select EmployeeID from @Temp)
        ) a
where SUBSTRING(a.RetroDate,1,4) not in (select  SUBSTRING(PayrollPeriod,1,4) from PHRPYPGCTRL where EmployeeID in (select EmployeeID from @Temp)) and a.DataSequence = DataSequencer

DECLARE @T_final AS TABLE
    (
      RegID VARCHAR(MAX),
		 [Sequence] VARCHAR(MAX),
		 BizProcessID VARCHAR(MAX),
		  TableReference VARCHAR(MAX),
		 FieldID VARCHAR(MAX),
		 DataSequence VARCHAR(MAX),
		 Value VARCHAR(MAX)
    )
INSERT  INTO @T_final
SELECT RegID, [Sequence],BizProcessID,TableReference,FieldID,DataSequencer,DateFrom from @UpdateBizData

DELETE FROM PCMWFRUNBIZDATA WHERE RegID = @reg_id AND BizProcessID = @bizprocid AND Sequence = @Sequence AND FieldID = 'DateFrom2' AND DataSequence IN (SELECT DataSequencer FROM @UpdateBizData)

INSERT  INTO PCMWFRUNBIZDATA
SELECT  RegID,
 [Sequence],
 BizProcessID,
 TableReference,
 FieldID,
 DataSequence,
 Value
FROM    @T_final
WHERE RegID = @reg_id AND BizProcessID = @bizprocid AND Sequence = @Sequence AND FieldID = 'DateFrom2' AND DataSequence IN (SELECT DataSequencer FROM @UpdateBizData )

-- end kententuan ketentuan retro

SELECT 
  '1' AS res
END 
ELSE 
BEGIN 
SELECT 
  '0' AS res
END