PT ARITA

ALTER PROCEDURE [dbo].[PRPTTMCUTIREKAP]

    (

      @year VARCHAR(4) ,

      @EmployeeID VARCHAR(12)

    ) 

AS 

--DECLARE @year VARCHAR(4) = '2021'

--DECLARE @EmployeeID VARCHAR(12) = '10000156'

DECLARE @startdate VARCHAR(12) = @year + '0101'

DECLARE @enddate VARCHAR(12) = @year + '1231'

DECLARE @now VARCHAR(10)

SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @emp_detail TABLE

(

    emp_id VARCHAR(12) ,

    emp_name VARCHAR(250) ,

    external_id VARCHAR(30) ,

    pos VARCHAR(20) ,

    pos_desc VARCHAR(250) ,

    pos_abbr VARCHAR(250) ,

    quota_type VARCHAR(20) ,

    quota_desc VARCHAR(250) ,

    quota_stdate VARCHAR(20) ,

    quota_endate VARCHAR(20) ,

	quota_stdeduction VARCHAR(20) ,

	quota_endeduction VARCHAR(20) ,

	ExpiredQuota VARCHAR(20),

    ori_quota DECIMAL(18, 1) ,

    remain_quota DECIMAL(18, 1) ,

	previous_qouta DECIMAL(18,1)

)

INSERT INTO @emp_detail

SELECT DISTINCT TOP(1)

                md2.EmployeeID ,

                md1.FullName ,

                md1.ExternalID ,

                md2.position ,

                pos.ObjectDescription ,

                pos.Abbreviation ,

				md17.AbsenceQuotaType ,

                absen.AbsenceTypeDesc ,

                md17.StartDate ,

                md17.EndDate ,

				md17.StartDeduction,

				md17.EndDeduction,

				CASE WHEN md17.ExpiredQuota <> '' THEN md17.ExpiredQuota ELSE '0' END AS ExpiredQuota,

                CASE WHEN md17.OriginalQuota <> '' THEN  md17.OriginalQuota ELSE '0' END AS OriginalQuota, 	

                CASE WHEN md17.RemainQuota <> '' THEN md17.RemainQuota  ELSE '0' END AS RemainQuota,

				CASE WHEN md17.PreviousQuota <> '' THEN md17.PreviousQuota  ELSE '0' END AS PreviousQuota

        FROM    dbo.PHRPA0002 AS md2

                INNER JOIN dbo.PHRPA0001 AS md1 ON md1.EmployeeID = md2.EmployeeID

                                                   AND md1.StartDate <= @enddate

                                                   AND md1.EndDate >= @enddate

                INNER JOIN dbo.PHRPA0017 AS md17 ON md17.EmployeeID = md1.EmployeeID

                INNER JOIN dbo.PHRTMABSTYP AS absen ON absen.AbsenceQuotaType = md17.AbsenceQuotaType

                LEFT JOIN dbo.PHROM0001 AS pos ON pos.ObjectID = md2.position

                                                  AND pos.ObjectClass = 'P'

                                                  AND pos.StartDate <= @enddate

                                                  AND pos.EndDate >= @enddate

        WHERE   ( md2.EmployeeID = @EmployeeID

                  OR @EmployeeID = ''

                )

                AND md2.StartDate <= @enddate

                AND md2.EndDate >= @enddate

                AND md17.StartDate <= @enddate

                AND md17.EndDate >= @enddate

                AND absen.quotadeduction = 'Y'

DECLARE @absence_detail TABLE

(

    emp_id VARCHAR(12) ,

    ws_type VARCHAR(20) ,

    start_date VARCHAR(20) ,

    end_date VARCHAR(20) ,

    id_on_behalf VARCHAR(12) ,

    abs_type VARCHAR(20) ,

    abs_type_desc VARCHAR(200) ,

    abs_descrition VARCHAR(200) ,

    abs_durasi DECIMAL(18, 1)

)

INSERT  INTO @absence_detail

EXECUTE PRPTTMCUTIDETAIL @year, @EmployeeID

UPDATE @absence_detail

SET start_date = (RIGHT(start_date,4) + SUBSTRING(start_date, 4,2) + LEFT(start_date,2))

	,end_date = (RIGHT(end_date,4) + SUBSTRING(end_date, 4,2) + LEFT(end_date,2))

DECLARE @EndDeductionDate VARCHAR(20) = (SELECT DISTINCT ExpiredQuota FROM @emp_detail)  --------- end date deduction quota cuti tahun kemarin di md17

DECLARE @SumPrevQuotaNow DECIMAL(22,1) = (SELECT DISTINCT previous_qouta FROM @emp_detail) --------- jumlah quota cuti dari tahun kemarin di md17

DECLARE @RemaingQuotaNow DECIMAL(22,1) = (SELECT DISTINCT remain_quota FROM @emp_detail) --------- jumlah quota cuti dari tahun ini di md17

DECLARE @DurTaken DECIMAL(22,1)  --------- jumlah cuti yang terjadi di tahun sekarang

SELECT DISTINCT @DurTaken = SUM(abs_durasi)

FROM @absence_detail

DECLARE @DurQuotaSetengahLast DECIMAL(22,1) --------- jumlah cuti setengah hari yang terjadi di tahun kemarin 

SELECT DISTINCT @DurQuotaSetengahLast = ISNULL(SUM(abs_durasi),0)

FROM @absence_detail AS AbsDetail

WHERE end_date <= @EndDeductionDate AND abs_durasi < 1

DECLARE @DurQuotaSetengahNow DECIMAL(22,1) --------- jumlah cuti setengah hari yang terjadi di tahun sekarang 

SELECT DISTINCT @DurQuotaSetengahNow = ISNULL(SUM(abs_durasi),0)

FROM @absence_detail AS AbsDetail

WHERE end_date > @EndDeductionDate AND abs_durasi < 1

DECLARE @DurQuotaLast DECIMAL(22,1) --------- jumlah cuti yang terjadi sampai expired date yang ada di md17

SELECT DISTINCT @DurQuotaLast = ISNULL(SUM(abs_durasi),0)

FROM @absence_detail AS AbsDetail

WHERE end_date <= @EndDeductionDate

DECLARE @DurQuotaNow DECIMAL(22,1) --------- jumlah cuti yang terjadi setelah expired date yang ada di md17

SELECT DISTINCT @DurQuotaNow = ISNULL(SUM(ISNULL(abs_durasi,0)),0)

FROM @absence_detail AS AbsDetailx

WHERE end_date > @EndDeductionDate

DECLARE @Use_Prev_Quota DECIMAL(22,1) --------- jumlah cuti tahun yang menggunakan quota tahun kemarin

SET @Use_Prev_Quota = ( CASE WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @SumPrevQuotaNow ELSE @DurQuotaLast END )

DECLARE @SisaCutiInExpiredDate DECIMAL(22,1)  --------- sisa kelebihan cuti yang termasuk dalam expired date

SET @SisaCutiInExpiredDate = CASE 

								 WHEN @SumPrevQuotaNow > 0 THEN

									 CASE 

										WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @DurQuotaLast - @SumPrevQuotaNow ELSE 0 

									 END

								 ELSE 0

							 END

DECLARE @used_current_quota DECIMAL(22,1) --------- jumlah cuti tahun yang menggunakan quota tahun sekarang

SET @used_current_quota = @DurTaken - CASE WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @SumPrevQuotaNow ELSE @DurQuotaLast END

DECLARE @Remaining_Quotas DECIMAL(22,1) -------- jumlah remaning quota di tahun sekarang setelah dikurangi cuti setengah hari & sisa cuti dari tahun kemarin

SET  @Remaining_Quotas = CASE 

							WHEN @now > @EndDeductionDate THEN @RemaingQuotaNow - (CASE WHEN @SumPrevQuotaNow > 0 THEN @DurQuotaSetengahNow ELSE @DurQuotaSetengahLast + @DurQuotaSetengahNow END) - @SisaCutiInExpiredDate 

							WHEN @now <= @EndDeductionDate THEN @RemaingQuotaNow - ( @DurQuotaSetengahNow + @DurQuotaSetengahLast )

						 END

SELECT distinct *

,@DurTaken AS totaaal

, @Use_Prev_Quota AS Use_Prev_Quota

, @used_current_quota AS used_current_quota

, emp_detail.previous_qouta AS previous_remaining_quota

, @Remaining_Quotas AS Remaining_Quotas

FROM @emp_detail AS emp_detail