CLIENT SUPPORT » BBG Bugs

ALTER PROCEDURE [dbo].[GenerateMedPlafMass]

AS

DECLARE @year VARCHAR(12) = dbo.fn_formatdatetime(GETDATE(), 'yyyy')

DECLARE @issimulate VARCHAR (2)='0'

DECLARE @treattype varchar(50)=''

DECLARE @CreateBy VARCHAR(200)=''

DECLARE @ChangeBy VARCHAR(200)=''

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

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

DECLARE @paygradestart VARCHAR(20)=''

DECLARE @paygradeend VARCHAR(20)=''

DECLARE @empstart VARCHAR(200)='00000000'

DECLARE @empend VARCHAR(200)='99999999'

DECLARE @emptype VARCHAR(200)=''

DECLARE @empsubtype VARCHAR(200)=''

DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @datecreate VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmddhhmmdd')

DECLARE @CreateDate VARCHAR(200)=''

DECLARE @ChangeDate VARCHAR(200)=''

set @CreateDate =@datecreate

set @ChangeDate =@datecreate

DECLARE @datatable as table (

HeaderSequence varchar(500),

Sequence varchar(500),

StartDate varchar(500),

EndDate varchar(500),

EmployeeID varchar(500),

TreatmentType varchar(500),

Plafond varchar(500),

PlafondRemain varchar(500),

PlafondUsed varchar(500),

MedicalFamily varchar(500),

PayrollGrade varchar(500),

CreateBy varchar(500),

CreateDate varchar(500),

ChangeBy varchar(500),

ChangeDate varchar(500)

)

DECLARE @datatablefinish as table (

HeaderSequence varchar(500),

Sequence varchar(500),

StartDate varchar(500),

EndDate varchar(500),

EmployeeID varchar(500),

TreatmentType varchar(500),

Plafond varchar(500),

PlafondRemain varchar(500),

PlafondUsed varchar(500),

MedicalFamily varchar(500),

PayrollGrade varchar(500),

CreateBy varchar(500),

CreateDate varchar(500),

ChangeBy varchar(500),

ChangeDate varchar(500)

)

if @emptype !='' or @emptype is not null

begin

select convert(varchar(50),'1')as HeaderSequence

,ROW_NUMBER()  OVER (partition by pa2.EmployeeID order by pa2.EmployeeID) as [Sequence],@startdate as StartDate,@enddate as EndDate,pa2.EmployeeID

,PLAF.TreatmentType,PLAF.FixedValue as Plafond,PLAF.FixedValue as PlafondRemain,convert(varchar(50),'0.00')as PlafondUsed ,PLAF.MedicalFamily

,pa3.PayrollGrade

into #temp

from PHRPA0002 pa2 left outer join 

PHRPA0003 pa3 on pa2.EmployeeID = pa3.EmployeeID and pa3.EndDate='99991231' left outer join 

PHRBNMEDTREATPLAF PLAF on PLAF.CompanyID = pa2.CompanyID and PLAF.PayClass = pa3.PayrollClass and PLAF.EndDate='99991231'

and (PLAF.PayClass = pa3.PayrollClass  or PLAF.PayClass ='*')and (PLAF.PayType = pa3.PayrollType  or PLAF.PayType ='*')

and (PLAF.PayGrade = pa3.PayrollGrade  or PLAF.PayGrade ='*')and (PLAF.PayArea = pa3.PayrollArea  or PLAF.PayArea ='*')

--and PLAF.TreatmentType = @treattype

where pa2.EmployeeStatus='01' and pa2.StartDate <= @now and pa2.EndDate >= @now and pa2.EmployeeType = @emptype 

update #temp set

HeaderSequence = a.HeaderSequence from (select  max(HeaderSequence) + 1as HeaderSequence,EmployeeID from phrpa0040 group by EmployeeID)a where a.EmployeeID = #temp.EmployeeID

insert into @datatable

select *,

 @CreateBy,

 @CreateDate,

 @ChangeBy,

 @ChangeDate

 from #temp

 drop table #temp

end

if @emptype ='' or @emptype is null

begin

select convert(varchar(50),'1')as HeaderSequence

,ROW_NUMBER()  OVER (partition by pa2.EmployeeID order by pa2.EmployeeID) as [Sequence],@startdate as StartDate,@enddate as EndDate,pa2.EmployeeID

,PLAF.TreatmentType,PLAF.FixedValue as Plafond,PLAF.FixedValue as PlafondRemain,convert(varchar(50),'0.00')as PlafondUsed ,PLAF.MedicalFamily

,pa3.PayrollGrade

into #temp2

from PHRPA0002 pa2 left outer join 

PHRPA0003 pa3 on pa2.EmployeeID = pa3.EmployeeID and pa3.EndDate='99991231' left outer join 

PHRBNMEDTREATPLAF PLAF on PLAF.CompanyID = pa2.CompanyID  and PLAF.EndDate='99991231'

and (PLAF.PayClass = pa3.PayrollClass  or PLAF.PayClass ='*')and (PLAF.PayType = pa3.PayrollType  or PLAF.PayType ='*')

and (PLAF.PayGrade = pa3.PayrollGrade  or PLAF.PayGrade ='*')and (PLAF.PayArea = pa3.PayrollArea  or PLAF.PayArea ='*')

--and PLAF.TreatmentType = @treattype

where pa2.EmployeeStatus='01' and pa2.StartDate <= @now and pa2.EndDate >= @now 

update #temp2 set

HeaderSequence = a.HeaderSequence from (select  max(HeaderSequence) + 1as HeaderSequence,EmployeeID from phrpa0040 group by EmployeeID)a where a.EmployeeID = #temp2.EmployeeID

insert into @datatable

select *,

 @CreateBy,

 @CreateDate,

 @ChangeBy,

 @ChangeDate

 from #temp2

drop table #temp2

end

if @paygradestart !='' and @empstart=''

begin

insert into @datatablefinish

select * from @datatable where PayrollGrade between @paygradestart and @paygradeend

end

if @empstart !='' and @paygradestart =''

begin

insert into @datatablefinish

select * from @datatable where  EmployeeID between @empstart and @empend 

end

if @paygradestart !='' and @empstart !=''

begin

insert into @datatablefinish

select * from @datatable where PayrollGrade between @paygradestart and @paygradeend and EmployeeID between @empstart and @empend 

end

if @issimulate !='0'

begin

insert into PHRPA0040

select distinct

StartDate ,

EndDate ,

EmployeeID ,

HeaderSequence ,

'',

'System Mass',

CreateBy ,

CreateDate ,

ChangeBy ,

ChangeDate ,

'','','','','','','','','','','','','','',0,'','','',null

from @datatablefinish

insert into PHRPA0041

select 

EmployeeID ,

HeaderSequence ,

Sequence,

TreatmentType,

Plafond,

PlafondRemain,

PlafondUsed,

MedicalFamily,

''

from @datatablefinish

end

--select distinct EmployeeID,'Sukses Generate'as Status from @datatablefinish

select 

EmployeeID ,

HeaderSequence ,

Sequence,

TreatmentType,

Plafond,

PlafondRemain,

PlafondUsed,

MedicalFamily,

CASE WHEN @issimulate='0' then 'Simulate' WHEN @issimulate='1' then 'Save Success' end as Status

from @datatablefinish