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(VARCHAR(20), '100') AS landscape ,
        md02.emp_id ,
		CONVERT(VARCHAR,YEAR(GETDATE()))+CONVERT(VARCHAR,SUBSTRING(hiring_date,5,4)) stdate ,
		CONVERT(VARCHAR,YEAR(GETDATE()) +1)+CONVERT(VARCHAR,SUBSTRING(hiring_date,5,2))+ SUBSTRING(CONVERT(VARCHAR(MAX), CONVERT(DATE, DATEADD(DAY, -1, hiring_date))),9,2) AS endate ,
		CONVERT(VARCHAR,YEAR(GETDATE()))+CONVERT(VARCHAR,SUBSTRING(hiring_date,5,4)) std,
		CONVERT(VARCHAR,YEAR(GETDATE()) +1)+CONVERT(VARCHAR,SUBSTRING(hiring_date,5,2))+SUBSTRING(CONVERT(VARCHAR(MAX), CONVERT(DATE, DATEADD(DAY, -1, hiring_date))),9,2) AS etd ,
        CONVERT(VARCHAR(20), '12') AS oriquot,
                 CONVERT(VARCHAR(20), 'System') AS userchange,
                format(getdate(),'yyyyMMddHHmmss') AS laschange,
                 CONVERT(VARCHAR(20), 'System') AS createby,
                 format(getdate(),'yyyyMMddHHmmss') AS createdate,
                CASE WHEN (SELECT TOP(1) CONVERT(VARCHAR(20), CONVERT(INT,seq)+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(1) CONVERT(VARCHAR(20), CONVERT(INT,seq)+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(VARCHAR(20),'1000') AS absentyp,
                 CONVERT(VARCHAR(20), '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(CAST(DATEADD(YEAR, -1, GETDATE()) 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( md17.[start_date],0,5) >= YEAR(GETDATE())   
                )
		--AND md02.emp_id  ='03053749'
END