ALTER PROCEDURE GenerateAbsenQuotaYear
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'

select distinct
	@Year+'0101' AS StartDate
	,@Year+'1231' AS EndDate
	,EmployeeID
	,@AbsenceQuotaType AS AbsenceQuotaType
	,1 AS Sequence
	,@Year+'0101' AS StartDeduction
	,@Year+'1231' AS EndDeduction
	,@OriginalQuota AS OriginalQuota
	,@RemainQuota AS 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 where EmployeeStatus='01' and EndDate='99991231'

IF @GenerateType = '0'
BEGIN
	select * from #Result
END

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

drop table #Result