Project

General

Profile

Feature #1684

[HINO - Payroll] Proses Payroll membaca Overtime -1 M

Added by Mohammad Daud about 2 years ago. Updated about 2 years ago.

Status:
Closed
Priority:
High
Assignee:
Saswanto Tampan
Start date:
10/10/2022
Due date:
10/13/2022
% Done:

0%

Estimated time:
Spent time:
4.00 h

Description

Dear Team Developer,

Minta bantuannya untuk dapat melakukan penyesuaian terkait dengan proses Payroll untuk membaca data Overtime, kondisi yang diinginkan adalah data Overtime dibulan berjalan tidak dihitung oleh engine Payroll, contoh Kasusnya adalah jika saat ini Proses Payroll Bulan September maka lemburan yang akan dibaca oleh sytem lemburan bulan Agustus kebawah.

For Info Solusi sesuai dengan Diskusi Posting akan masuk kedalam Master data tampungan Overtime dan dibuatkan Service yang akan running setiap hari untuk memindahkan data Overtime di tabel tampungan ke tabel Original.

Akses ke aplikasi http://remote.minovais.com:61120/Account/Login
User woty
Pass 123Aa

database sql server 2019
remote.minovais.com, 1445\MSSQLSERVER2019
minova hino prod agustus 2022

Files

clipboard-202210111138-spifp.png (6.09 KB) clipboard-202210111138-spifp.png Fiona Avangeline Jonathan, 10/11/2022 11:38 AM
clipboard-202211071649-oq07g.png (257 KB) clipboard-202211071649-oq07g.png Muhammad Bintar, 11/07/2022 04:49 PM
#1

Updated by Kezia Pawitra Yulianti about 2 years ago

  • Due date changed from 10/12/2022 to 10/13/2022
  • Status changed from New to Assigned
  • Assignee changed from Saswanto Tampan to Fiona Avangeline Jonathan

Fiona,
tolong d bantu untuk dapat dibuatkan:
1. dl tabel penampung nya untuk MD Overtime.
2. Dibuatkan postingan baru ke tabel penampung tsb

Untuk detail nya, Silahkan diskusi dgn daud untuk requirement nya.

Kalau sudah ada tabel penampung nya, baru nanti task ini di assign ke mang wanto untuk dibuatkan servicenya

Tks

#2

Updated by Fiona Avangeline Jonathan about 2 years ago

Mang Wanto,

Table tampungannya sudah dibuat dengan nama PHRPAT016 . (MD asli nya PHRPA0016)
Sudah dibuatkan SDATATABLEFIELD, SDATATABLE, Tabel Fisik dan ubah FIELDPOS.

Sudah di tes juga ketika mengajukan ESS overtime, data nya ke posting ke tabel tampungan. Di table asli PHRPA0016 kosong.

Terima kasih.

#3

Updated by Saswanto Tampan about 2 years ago

  • Assignee changed from Saswanto Tampan to Mohammad Daud

SILAHKAN DI BUAT JOB SQL DI DB YANG BERSANGKUTAN YANG AUTO RUN SETIAP HARI MENGGUNAKAN QUERY INI :

insert into PHRPA0016
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 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 left(pt16.EndDate,6) <= ctrl.PayrollPeriod-1 and left(ctrl.PayrollPeriod,4) = left(pt16.StartDate,4) and ctrl.PayrollControlStatus='C'

#4

Updated by Kezia Pawitra Yulianti about 2 years ago

ini udah d update khan ya querynya supaya mindahin dr tabel tampungan ke tabel real?

#5

Updated by Muhammad Bintar about 2 years ago

update sudah di deploy ke hino production.
tinggal monitoring untuk ESS yang terposting apakah masuk ke tabel tampungan atau tidak.

#6

Updated by Saswanto Tampan about 2 years ago

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 left(pt16.EndDate,6) <= ctrl.PayrollPeriod-1 and left(ctrl.PayrollPeriod,4) = left(pt16.StartDate,4) and ctrl.PayrollControlStatus='C'

insert into PHRPA0016 select * from #PHRPA0016
update PHRPA0015 set RetroDate= case
when PHRPA0015.RetroDate ='' then a.StartDate
when PHRPA0015.RetroDate > a.StartDate then a.StartDate end,
CorrectStatus='1' from (select StartDate,EmployeeID from #PHRPA0016)a
where PHRPA0015.EmployeeID=a.EmployeeID

drop table #PHRPA0016

#7

Updated by Muhammad Bintar about 2 years ago

  • Status changed from Deploy to Closed
  • Assignee changed from Mohammad Daud to Saswanto Tampan
#8

Updated by Muhammad Bintar about 2 years ago

update query baru ============================
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 left(pt16.EndDate,6) <= ctrl.PayrollPeriod-1 and left(ctrl.PayrollPeriod,4) = left(pt16.StartDate,4) and ctrl.PayrollControlStatus='C'

insert into PHRPA0016 select * from #PHRPA0016
update PHRPA0015 set RetroDate= case
when PHRPA0015.RetroDate ='' then a.StartDate
when PHRPA0015.RetroDate > a.StartDate then a.StartDate end,
CorrectStatus='1' from (SELECT TOP 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

Also available in: Atom PDF