CLIENT SUPPORT » Bank Mega

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