CLIENT SUPPORT » BANK KALTENG Bugs

declare @TreatmentType varchar(10)= '02'

declare @EmployeeID varchar(10)= '00000219'

declare @StartDate varchar(10)= '20250115'

declare @CostType varchar(10)= '06'

declare @MedicalFamily varchar(10)= '0004'

select 

  distinct case when cover.TreatmentType = '03' 

  or cover.TreatmentType = '04' then (

    select 

      count(pa42.TreatmentType) as CountTreatmentType 

    from 

      PHRPA0042 pa42 

    where 

      pa42.TreatmentType = @TreatmentType 

      and pa42.EmployeeID = @EmployeeID 

      and pa42.ClaimStatus in('01', '02') 

      and (

        pa42.StartDate <= @StartDate 

        and pa42.StartDate >= (

          select 

            dbo.fn_formatdatetime(

              DATEADD(

                DAY, 

                convert(

                  decimal, 

                  '-' + convert(

                    varchar(50), 

                    cover.TransactionPeriod

                  )

                ), 

                GETDATE()

              ), 

              'yyyymmdd'

            ) AS DateRange

        )

      )

  ) else '0' end as trxcount, 

  cover.TransactionFrequency, 

  cover.TransactionPeriod, 

  cover.CovCompanyInLimit, 

  case when cover.WagetypeBase <> '' then (

    select 

      CAST(

        REPLACE(

          ISNULL(

            dbo.GetPEN(pa04.Amount), 

            '0'

          ), 

          ',', 

          '.'

        ) AS DECIMAL(22, 0)

      )* 5 AS Amount 

    from 

      PHRBNMEDTREATCOVER cov 

      left outer join PHRPYCU0305 CU0305 on CU0305.WageTypeCum = cov.WagetypeBase 

      left outer join phrpa0004 pa04 on pa04.WageType = CU0305.WageType 

    where 

      cov.TreatmentType = @TreatmentType 

      and cov.CostType = @CostType 

      and cov.MedicalFamily = @MedicalFamily 

      and cov.EndDate = '99991231' 

      and pa04.EmployeeID = @EmployeeID 

      and pa04.EndDate = '99991231' 

      and CU0305.EndDate = '99991231'

  ) else '0' end as Amount, 

  case when cover.PayClass <> '*' then (

    select 

      TCOVER.TransactionLimit 

    from 

      PHRBNMEDTREATCOVER TCOVER 

      left outer join PHRPA0003 pa03 on pa03.PayrollClass = TCOVER.PayClass 

    where 

      TCOVER.TreatmentType = @TreatmentType 

      and TCOVER.CostType = @CostType 

      and TCOVER.MedicalFamily = @MedicalFamily 

      and TCOVER.EndDate = '99991231' 

      and pa03.EmployeeID = @EmployeeID 

      and pa03.EndDate = '99991231'

  ) else cover.TransactionLimit end as TransactionLimit 

from 

  PHRBNMEDTREATCOVER cover 

where 

  cover.TreatmentType = @TreatmentType 

  and cover.CostType = @CostType 

  and cover.MedicalFamily = @MedicalFamily 

  and cover.EndDate = '99991231'