--ALTER PROCEDURE GENLEAVEQUOTA
--(
--	@Now VARCHAR(20)
--	,@StartDate VARCHAR(20)
--	,@EndDate VARCHAR(20)
--	,@QuotaType VARCHAR(20)
--	,@Sdeduc VARCHAR(20)
--	,@Ededuc VARCHAR(20)
--	,@Quota VARCHAR(20)
--	,@UserName VARCHAR(20)
--	,@CodeHiring VARCHAR(20)
--	,@YosMin VARCHAR(20)
--	,@YosMax VARCHAR(20)
--	,@Now14 VARCHAR(20)
--	,@MenuID VARCHAR(20)
--	,@IsProrate VARCHAR(20)
--	,@EmpType VARCHAR(20)
--	,@EmpSubType VARCHAR(20)
--	,@GradeStart VARCHAR(20)
--	,@GradeEnd VARCHAR(20)
--	,@EmpStart VARCHAR(20)
--	,@EmpEnd VARCHAR(20)
--)
--AS

DECLARE @Now VARCHAR(20) = '20221129'
DECLARE @StartDate VARCHAR(20) = '20220101'
DECLARE @EndDate VARCHAR(20) = '20221231'
DECLARE @QuotaType VARCHAR(20) = '11'
DECLARE @Sdeduc VARCHAR(20) = '20220101'
DECLARE @Ededuc VARCHAR(20) = '20221231'
DECLARE @Quota VARCHAR(20) = '12'
DECLARE @UserName VARCHAR(20) = 'tri'
DECLARE @CodeHiring VARCHAR(20) = '01'
DECLARE @YosMin VARCHAR(20) = '1'
DECLARE @YosMax VARCHAR(20) = '99'
DECLARE @Now14 VARCHAR(20) = '20221129113059'
DECLARE @MenuID VARCHAR(20) = 'TM05'
DECLARE @IsProrate VARCHAR(20) = '1'
DECLARE @EmpType VARCHAR(20) = ''
DECLARE @EmpSubType VARCHAR(20) = ''
DECLARE @GradeStart VARCHAR(20) = ''
DECLARE @GradeEnd VARCHAR(20) = ''
DECLARE @EmpStart VARCHAR(20) = ''--'99010003'
DECLARE @EmpEnd VARCHAR(20) = ''--'21021444'

DECLARE @EmpAuth TABLE (EmployeeID VARCHAR(20)) 
INSERT INTO @EmpAuth EXECUTE dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, @UserID = @UserName
SELECT * FROM 
(	SELECT DISTINCT @StartDate AS StartDate, 
		@EndDate AS EndDate, 
		md1.EmployeeID , 
		CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate 
		ELSE md39.Date 
		END AS HiringDate , 
		DATEDIFF(MONTH,( CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate ELSE md39.Date END ), @EndDate)/12 AS YearOfService , 
		@QuotaType AS AbsenceQuotaType, 
		'1' AS Sequence, 
		@Sdeduc AS StartDeduction, 
		@Ededuc AS EndDeduction, 
		CASE WHEN @IsProrate = '1' 
		THEN 
			CASE WHEN DATEDIFF(MONTH,( CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate ELSE md39.Date END ), @EndDate)/12 = 1 
			THEN CONVERT(DECIMAL(18,0),ROUND(CONVERT(DECIMAL(18,0),DATEDIFF(MONTH,CASE WHEN (md39.Date IS NULL) THEN md15.HiringDate ELSE md39.Date END,@StartDate))/12 * CONVERT(DECIMAL(18,0), @Quota),0)) 
			WHEN DATEDIFF(MONTH,( CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate ELSE md39.Date END ), @EndDate)/12 = 0
			THEN '0'
			ELSE @Quota END 
		ELSE @Quota END AS OriginalQuota,
		CASE WHEN @IsProrate = '1' 
		THEN 
			CASE WHEN DATEDIFF(MONTH,( CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate ELSE md39.Date END ), @EndDate)/12 = 1 
			THEN CONVERT(DECIMAL(18,0),ROUND(CONVERT(DECIMAL(18,0),DATEDIFF(MONTH,CASE WHEN (md39.Date IS NULL) THEN md15.HiringDate ELSE md39.Date END,@StartDate))/12 * CONVERT(DECIMAL(18,0), @Quota),0)) 
			WHEN DATEDIFF(MONTH,( CASE WHEN ( md39.Date IS NULL ) THEN md15.HiringDate ELSE md39.Date END ), @EndDate)/12 = 0
			THEN '0'
			ELSE @Quota END 
		ELSE @Quota END AS RemainQuota,
		md1.FullName , 
		md2.EmployeeOffice , 
		md2.EmployeeArea , 
		md2.EmployeeType , 
		md2.EmployeeSubType , 
		md3.PayrollGrade, 
		@UserName AS CreateBy, 
		@Now14 AS CreateDate, 
		@UserName AS ChangeBy, 
		@Now14 AS ChangeDate 
	FROM    PHRPA0015 md15 WITH(NOLOCK) 
        LEFT JOIN PHRPA0039 md39 WITH(NOLOCK) 
			ON md15.EmployeeID = md39.EmployeeID 
			AND md39.DateType = @CodeHiring 
        INNER JOIN PHRPA0001 md1 WITH(NOLOCK) 
			ON md15.EmployeeID = md1.EmployeeID 
			AND md1.StartDate <= @Now
			AND md1.EndDate >= @Now 
        INNER JOIN PHRPA0002 md2 WITH(NOLOCK) 
			ON md1.EmployeeID = md2.EmployeeID 
			AND md2.StartDate <= @Now
			AND md2.EndDate >= @Now 
        LEFT JOIN PHRPA0003 md3 WITH(NOLOCK) 
			ON md1.EmployeeID = md3.EmployeeID 
			AND md2.StartDate <= @Now
			AND md2.EndDate >= @Now
        INNER JOIN @EmpAuth EmpAuth ON md1.EmployeeID = EmpAuth.EmployeeID 
	WHERE (md2.EmployeeType = @EmpType OR @EmpType = '')
		AND (md2.EmployeeSubType = @EmpSubType OR @EmpSubType = '')
		AND (md2.EmployeeID BETWEEN @EmpStart AND @EmpEnd OR @EmpStart = '' OR @EmpEnd = '')
		AND (md3.PayrollGrade BETWEEN @GradeStart AND @GradeEnd OR @GradeStart = '' OR @GradeEnd = '')
) AS tb 
WHERE (YearOfService BETWEEN @YosMin AND @YosMax)