Bug #979
Fixing Service Auto Generate LQ (Leave Quota)
0%
Description
Dear tim developer mohon support nya untuk perbaikan service AutoGenerateLQ (Leave Quota) dimana pada saat ini service tersebut men generate quota pada employee yang baru saja hiring tetapi sudah mendapat kuota, padahal seharusnya kuota tersebut bisa terambil jika employee sudah bekerja lebih dari 1 tahun
database MinovaHR_ESS_BankMega_Production
database sql server 2014
192.168.3.105\MSSQLSERVER2014
Files
Updated by Saswanto Tampan almost 3 years ago
- Status changed from New to Deploy
- Assignee changed from Saswanto Tampan to Muhammad Bintar
dibuatkan job sql
Updated by Muhammad Bintar almost 3 years ago
update =
buat SP dengan nama AutoGenerateLeaveQuotaAfter1YearHiring
lalu dibuat job dengan schedule setiap 1 hari sekali di malam hari
Updated by Muhammad Bintar almost 3 years ago
USE [MinovaHR_BANK_MEGA_Production]
GO
/****** Object: StoredProcedure [dbo].[AutoGenerateLeaveQuotaAfter1YearHiring] Script Date: 25/03/2022 17:10:03 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE [dbo].[AutoGenerateLeaveQuotaAfter1YearHiring]
-- Add the parameters for the stored procedure here
--@Landscape varchar(3)
AS
BEGIN
INSERT INTO dbo.hr_md_emp_md0017
SELECT DISTINCT CONVERT, '100') AS landscape ,
md02.emp_id ,
CONVERT))+CONVERT) stdate ,
CONVERT) 1)+CONVERT) SUBSTRING, CONVERT(DATE, DATEADD(DAY, 1, hiring_date))),9,2) AS endate ,', '')
CONVERT))+CONVERT) std,
CONVERT) +1)+CONVERT)+SUBSTRING, CONVERT)),9,2) AS etd ,
CONVERT, '12') AS oriquot,
CONVERT, 'System') AS userchange,
format(getdate(),'yyyyMMddHHmmss') AS laschange,
CONVERT, 'System') AS createby,
format(getdate(),'yyyyMMddHHmmss') AS createdate,
CASE WHEN (SELECT TOP CONVERT, CONVERT+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq desc)
IS NOT NULL THEN
(SELECT TOP CONVERT, CONVERT+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq DESC) ELSE '1'
END AS seq,
CONVERT,'1000') AS absentyp,
CONVERT, '12') AS remainquot
FROM dbo.hr_md_emp_md0002 md02
LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
WHERE md02.employee_status = '01'
AND md15.hiring_date <= REPLACE) AS DATE),'
AND md02.end_date = '99991231'
AND md02.emp_id NOT IN (
SELECT DISTINCT md02.emp_id
FROM dbo.hr_md_emp_md0002 md02
LEFT OUTER JOIN dbo.hr_md_emp_md0017 md17 ON md17.emp_id = md02.emp_id
WHERE md02.employee_status = '01'
AND md02.end_date = '99991231'
AND SUBSTRING >= YEAR)
)
--AND md02.emp_id ='03053749'
END