USE MinovaES_HINO_Prod
select distinct pt16.StartDate,pt16.EndDate,pt16.EmployeeID,pt16.OvertimeType,pt16.StartTime,pt16.EndTime,pt16.DayType,pt16.Duration
,pt16.DayBeforeFlag,pt16.PlanDuration,pt16.StartPlan,pt16.EndPlan,pt16.Notes,pt16.CreateBy,pt16.CreateDate,pt16.ChangeBy,pt16.ChangeDate
into #PHRPA0016 from PHRPAT016 pt16 left outer join
PHRPA0002 pa2 on pa2.EmployeeID = pt16.EmployeeID and (pt16.StartDate between pa2.StartDate and pa2.EndDate) left outer join
PHRPYPGCTRL ctrl on ctrl.PayrollGroup = pa2.PayrollGroup

where Convert(int, left(pt16.EndDate,6)) < =
  CASE 
      WHEN RIGHT(ctrl.PayrollPeriod-1,2 )='00' THEN Convert(int,  convert(varchar, left(ctrl.PayrollPeriod,4)-1) + convert(varchar, 12))
      else Convert(int, ctrl.PayrollPeriod-1)
  END
 and ctrl.PayrollControlStatus in ('C','F')
 and left(ctrl.PayrollPeriod,4) =
   CASE 
      WHEN RIGHT(ctrl.PayrollPeriod-1,2 )='00' then  left(pt16.StartDate,4) +1 else  left(pt16.StartDate,4)  
   end


 insert into PHRPA0016
SELECT  *   
FROM  #PHRPA0016 A  
WHERE NOT EXISTS (SELECT *      
                  FROM PHRPA0016 B
                  WHERE A.StartDate+A.EndDate+A.EmployeeID+A.OvertimeType = B.StartDate+B.EndDate+B.EmployeeID+B.OvertimeType);

update PHRPA0015 set RetroDate= case
when PHRPA0015.RetroDate ='' then a.StartDate
when PHRPA0015.RetroDate > a.StartDate then a.StartDate  else RetroDate end,
CorrectStatus='1' from (SELECT TOP(1) StartDate,EmployeeID from #PHRPA0016  ORDER BY StartDate asc)a
where PHRPA0015.EmployeeID=a.EmployeeID

DELETE FROM PHRPAT016 WHERE EmployeeID+StartDate IN (select EmployeeID+StartDate  from #PHRPA0016)
drop table #PHRPA0016