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(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