PT BIA

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