|
1
|
USE MinovaES_HINO_Prod
|
|
2
|
select distinct pt16.StartDate,pt16.EndDate,pt16.EmployeeID,pt16.OvertimeType,pt16.StartTime,pt16.EndTime,pt16.DayType,pt16.Duration
|
|
3
|
,pt16.DayBeforeFlag,pt16.PlanDuration,pt16.StartPlan,pt16.EndPlan,pt16.Notes,pt16.CreateBy,pt16.CreateDate,pt16.ChangeBy,pt16.ChangeDate
|
|
4
|
into #PHRPA0016 from PHRPAT016 pt16 left outer join
|
|
5
|
PHRPA0002 pa2 on pa2.EmployeeID = pt16.EmployeeID and (pt16.StartDate between pa2.StartDate and pa2.EndDate) left outer join
|
|
6
|
PHRPYPGCTRL ctrl on ctrl.PayrollGroup = pa2.PayrollGroup
|
|
7
|
|
|
8
|
where Convert(int, left(pt16.EndDate,6)) < =
|
|
9
|
CASE
|
|
10
|
WHEN RIGHT(ctrl.PayrollPeriod-1,2 )='00' THEN Convert(int, convert(varchar, left(ctrl.PayrollPeriod,4)-1) + convert(varchar, 12))
|
|
11
|
else Convert(int, ctrl.PayrollPeriod-1)
|
|
12
|
END
|
|
13
|
and ctrl.PayrollControlStatus in ('C','F')
|
|
14
|
and left(ctrl.PayrollPeriod,4) =
|
|
15
|
CASE
|
|
16
|
WHEN RIGHT(ctrl.PayrollPeriod-1,2 )='00' then left(pt16.StartDate,4) +1 else left(pt16.StartDate,4)
|
|
17
|
end
|
|
18
|
|
|
19
|
|
|
20
|
insert into PHRPA0016
|
|
21
|
SELECT *
|
|
22
|
FROM #PHRPA0016 A
|
|
23
|
WHERE NOT EXISTS (SELECT *
|
|
24
|
FROM PHRPA0016 B
|
|
25
|
WHERE A.StartDate+A.EndDate+A.EmployeeID+A.OvertimeType = B.StartDate+B.EndDate+B.EmployeeID+B.OvertimeType);
|
|
26
|
|
|
27
|
update PHRPA0015 set RetroDate= case
|
|
28
|
when PHRPA0015.RetroDate ='' then a.StartDate
|
|
29
|
when PHRPA0015.RetroDate > a.StartDate then a.StartDate else RetroDate end,
|
|
30
|
CorrectStatus='1' from (SELECT TOP(1) StartDate,EmployeeID from #PHRPA0016 ORDER BY StartDate asc)a
|
|
31
|
where PHRPA0015.EmployeeID=a.EmployeeID
|
|
32
|
|
|
33
|
DELETE FROM PHRPAT016 WHERE EmployeeID+StartDate IN (select EmployeeID+StartDate from #PHRPA0016)
|
|
34
|
drop table #PHRPA0016
|
