CLIENT SUPPORT » BBG Bugs

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