CLIENT SUPPORT » HINO Bugs

USE [MinovaES_HINO_Dev]

GO

/****** Object:  StoredProcedure [dbo].[GenerateMedPlafMass]    Script Date: 27/12/2023 11.43.48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GenerateMedPlafMass]

(

@issimulate VARCHAR(20),

@CreateBy VARCHAR(200),

@ChangeBy VARCHAR(200),

@startdate VARCHAR(20),

@enddate VARCHAR(20),

@paygradestart VARCHAR(20),

@paygradeend VARCHAR(20),

@empstart VARCHAR(200),

@empend VARCHAR(200),

@emptype VARCHAR(200),

@empsubtype VARCHAR(200)

)

AS

--DECLARE @issimulate VARCHAR (2)='0'

--DECLARE @treattype varchar(50)=''

--DECLARE @CreateBy VARCHAR(200)='00000111'

--DECLARE @ChangeBy VARCHAR(200)='00000111'

--DECLARE @startdate VARCHAR(20)='20240101'

--DECLARE @enddate VARCHAR(20)='20241231'

--DECLARE @paygradestart VARCHAR(20)=''

--DECLARE @paygradeend VARCHAR(20)=''

--DECLARE @empstart VARCHAR(200)='00000445'

--DECLARE @empend VARCHAR(200)='00000446'

--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

--set @CreateBy =''

--set @ChangeBy =''

--set @startdate =@now

--set @enddate =@now

--set @paygradestart ='00'

--set @paygradeend ='03'

--set @empstart ='10000148'

--set @empend ='10000151'

--set @emptype ='04'

--set @empsubtype ='10'

SELECT EmployeeID,CAST(REPLACE(ISNULL(dbo.GetPEN(Amount), '0'), ',', '.') AS DECIMAL(22,0)) as Amount 

into #pa4

from PHRPA0004 

where 

	EmployeeID between @empstart and @empend 

	and WageType=(select top(1)WageType from PHRPYCU0305 where WageTypeCum='B003' and EndDate='99991231')

	and @startdate<=EndDate

	and @enddate>=StartDate

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)+5 as [Sequence]

,@startdate as StartDate

,@enddate as EndDate

,pa2.EmployeeID

,PLAF.TreatmentType

,case 

when PLAF.TreatmentType='300' then pa4.Amount

when PLAF.TreatmentType='400' then pa4.Amount

else PLAF.FixedValue end as Plafond

,case 

when PLAF.TreatmentType='300' then pa4.Amount

when PLAF.TreatmentType='400' then pa4.Amount

else PLAF.FixedValue end as PlafondRemain

,convert(varchar(50),'0.00')as PlafondUsed 

,PLAF.MedicalFamily

,pa3.PayrollGrade

into #temp

from PHRPA0002 pa2 

left join #pa4 pa4 on pa2.EmployeeID=pa4.EmployeeID

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 and pa2.EmployeeType = @emptype  and pa2.EmployeeID between @empstart and @empend 

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)+5 as [Sequence],@startdate as StartDate

,@enddate as EndDate,pa2.EmployeeID

,PLAF.TreatmentType

,case 

when PLAF.TreatmentType='300' then pa4.Amount*PLAf.Number

when PLAF.TreatmentType='400' then pa4.Amount*PLAf.Number

else PLAF.FixedValue end as Plafond

,case 

when PLAF.TreatmentType='300' then pa4.Amount*PLAf.Number

when PLAF.TreatmentType='400' then pa4.Amount*PLAf.Number

else PLAF.FixedValue end as PlafondRemain

,convert(varchar(50),'0.00')as PlafondUsed 

,PLAF.MedicalFamily

,pa3.PayrollGrade

into #temp2

from PHRPA0002 pa2 

left join #pa4 pa4 on pa2.EmployeeID=pa4.EmployeeID

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 and pa2.EmployeeID between @empstart and @empend 

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 

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

drop table #pa4