ALTER PROCEDURE [dbo].[GenerateAbsenQuotaCutiTahunan]
AS


DECLARE @AbsenceQuotaType VARCHAR (10)='1000'
DECLARE  @Year VARCHAR (4)=dbo.fn_formatdatetime(GETDATE(), 'yyyy')
DECLARE @CreateDate VARCHAR (50)=CONVERT(VARCHAR, GETDATE(), 112) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
DECLARE @OriginalQuota INT = 12
DECLARE @RemainQuota INT = 12
DECLARE @CreateBy VARCHAR (100) = 'minovais'
DECLARE @GenerateType VARCHAR = '1'

--GENERATE TYPE 1 = EXECUTE
--GENERATE TYPE 0 = SIMULATE

--GET PREVIOUS QUOTA

DECLARE @YearBefore VARCHAR (4) = (Select @Year-1)

select distinct 
EmployeeID
,MAX(Sequence) AS MaxSeq
into #MaxSeq
from PHRPA0017 
where left(StartDate,4)=@YearBefore and AbsenceQuotaType=@AbsenceQuotaType
group by EmployeeID

select distinct 
pa17.EmployeeID
,pa17.RemainQuota
into #RemainQuota
from PHRPA0017 pa17
inner join #MaxSeq ms on ms.EmployeeID=pa17.EmployeeID and ms.MaxSeq=pa17.Sequence 
where left(pa17.StartDate,4)=@YearBefore and pa17.AbsenceQuotaType=@AbsenceQuotaType

--select @YearBefore

select distinct
	@Year+'0101' AS StartDate
	,@Year+'1231' AS EndDate
	,pa2.EmployeeID
	,@AbsenceQuotaType AS AbsenceQuotaType
	,1 AS Sequence
	,@Year+'0101' AS StartDeduction
	,@Year+'0331' AS EndDeduction
	,@OriginalQuota AS OriginalQuota
	,@RemainQuota AS RemainQuota
	,rq.RemainQuota as PreviousQuota
	,'' as ExpiredQuota
	,'Generate Auto' AS Notes
	,@CreateBy AS CreateBy
	,@CreateDate AS CreateDate
	,@CreateBy AS ChangeBy
	,@CreateDate AS ChangeDate
	into #Result
	from PHRPA0002 pa2
	left join #RemainQuota rq on pa2.EmployeeID=rq.EmployeeID
	where EmployeeStatus='01' and EndDate='99991231' and EmployeeType='01'

IF @GenerateType = '0'
BEGIN
	select 'Simulate' AS Flag,* from #Result
END

IF @GenerateType ='1'
BEGIN
	delete from PHRPA0017 where left(StartDate,4) = @Year
	insert into PHRPA0017  select * from #Result;
	select 'Execute Success' AS Flag,* from #Result
END

drop table #RemainQuota
drop table #Result
drop table #MaxSeq