Bug #1951 ยป script job sql.txt
| 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 |
where left(pt16.EndDate,6) <= ctrl.PayrollPeriod-1 and left(ctrl.PayrollPeriod,4) = left(pt16.StartDate,4) and ctrl.PayrollControlStatus='C' |
| 8 |
|
| 9 |
insert into PHRPA0016 select * from #PHRPA0016 |
| 10 |
update PHRPA0015 set RetroDate= case |
| 11 |
when PHRPA0015.RetroDate ='' then a.StartDate |
| 12 |
when PHRPA0015.RetroDate > a.StartDate then a.StartDate end, |
| 13 |
CorrectStatus='1' from (SELECT TOP(1) StartDate,EmployeeID from #PHRPA0016 ORDER BY StartDate asc)a |
| 14 |
where PHRPA0015.EmployeeID=a.EmployeeID |
| 15 |
|
| 16 |
|
| 17 |
DELETE FROM PHRPAT016 WHERE EmployeeID+StartDate IN (select EmployeeID+StartDate from #PHRPA0016) |
| 18 |
|
| 19 |
drop table #PHRPA0016 |