ALTER PROCEDURE [dbo].[CRPTLPPOMESSPAYSLIP]
    (
      @PayPeriod VARCHAR(6) ,
      @EmpID VARCHAR(20) ,
      @Running VARCHAR(2) ,
      @PayType VARCHAR(2) 
    )
AS

    --DECLARE @PayPeriod VARCHAR(6) = '202501'
    --DECLARE @EmpID VARCHAR(8) = '00000002'
    --DECLARE @Running VARCHAR(2) = '1'
    --DECLARE @PayType VARCHAR(2) = ''
    

    DECLARE @CompanyCode AS VARCHAR(50) 
    DECLARE @CompanyName AS VARCHAR(250) 
    DECLARE @CompanyLogo AS VARBINARY(MAX)
    DECLARE @PayMonth AS NVARCHAR(2)
    DECLARE @PayYear AS NVARCHAR(4)
    DECLARE @Begda AS NVARCHAR(8)
    DECLARE @Endda AS NVARCHAR(8)

	DECLARE @PayGroup AS VARCHAR(50) 
	DECLARE @BegdaWS AS NVARCHAR(8)
    DECLARE @EnddaWS AS NVARCHAR(8)

    SET @PayMonth = RIGHT(@PayPeriod, 2)
    SET @PayYear = LEFT(@PayPeriod, 4)
    SET @Begda = @PayPeriod + '01'
    SET @Endda = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1, @Begda)), 112)
    
   

    DECLARE @PaySlipSigned AS NVARCHAR(50)

    SELECT TOP ( 1 )
            @PaySlipSigned = Value
    FROM    dbo.PHRPYCU0307
    WHERE   ( PayrollParameter = 'PAYSLIPSIGNED' )
            AND ( StartDate <= @Endda
                  AND EndDate >= @Endda
                )

    IF ( @Running = '1' )
        BEGIN
            SELECT TOP ( 1 )
                    @CompanyCode = CompanyID
					,@PayGroup = PayrollGroup
            FROM    dbo.PHRPYTR0300 
             WHERE   ( EmployeeID = @EmpID )
                    AND ( PayPeriodMonth = @PayMonth )
                    AND ( PayPeriodYear = @PayYear )
                    AND ( RunPeriodMonth = @PayMonth )
                    AND ( RunPeriodYear = @PayYear )
        END
    ELSE
        BEGIN
            SELECT TOP ( 1 )
                    @CompanyCode = CompanyID
					,@PayGroup = PayrollGroup
            FROM    dbo.PHRPYTR0300SIM
            WHERE   ( EmployeeID = @EmpID )
                    AND ( PayPeriodMonth = @PayMonth )
                    AND ( PayPeriodMonth = @PayYear )
                    AND ( RunPeriodMonth = @PayMonth )
                    AND ( RunPeriodYear = @PayYear )
        END

       

    SELECT  @CompanyName = COM.CompanyDescription ,
            @CompanyLogo = CAT.FileData
    FROM    dbo.PCMEPCOMPID AS COM
	LEFT JOIN dbo.SFILECATALOG CAT ON COM.Logo = CAT.FileName
    WHERE   ( CompanyID = @CompanyCode )
            AND StartDate <= @Endda
            AND EndDate >= @Endda

	DECLARE @Disclaimer VARCHAR(250)
	SELECT @Disclaimer = Value1 FROM dbo.PCMEPGENPARAM WHERE Parameter = 'DISCLAIMEREPAYSLIP'


	IF @CompanyCode = '3000'
	BEGIN 
	SELECT @Disclaimer = Value1 FROM dbo.PCMEPGENPARAM WHERE Parameter = 'DISCLAIMEREPAYSLIPIHATEC'
	END

	----//---- get hari kerja start
	DECLARE @PeriodPayGroup VARCHAR(20)
	SELECT @PeriodPayGroup = PayrollPeriod FROM dbo.PHRPYPGRUP WHERE PayrollGroup = @PayGroup

	SELECT @BegdaWS = StartDate, @EnddaWS = EndDate FROM dbo.PHRPYTRPAYPERIOD WHERE PayrollPeriod = @PeriodPayGroup AND StartDate <= @Begda AND EndDate >= @Begda
	
	DECLARE @TotHK DECIMAL(18,0)
	SELECT @TotHK = COUNT(pa125.StartDate)
	FROM PHRPA0025 AS pa25
	LEFT JOIN PHRPA0125 AS pa125
		ON pa25.EmployeeID = pa125.EmployeeID
	WHERE pa25.EmployeeID = @EmpID
		AND pa25.StartDate <= @EnddaWS AND pa25.EndDate >= @EnddaWS
		AND pa125.StartDate >= @BegdaWS AND pa125.StartDate <= @EnddaWS
		AND pa125.DayType IN (SELECT DayType FROM dbo.PHRTMDAYTYP WHERE Flag = 'WORK')
	----//---- get hari kerja end

	----//---- get attendance start
	DECLARE @TotAtt DECIMAL(18,2)
	SELECT @TotAtt = COUNT(StartDate)
	FROM dbo.PHRPA0019
	WHERE EmployeeID = @EmpID
		AND StartDate >= @BegdaWS AND StartDate <= @EnddaWS
		AND AttendanceType = '1000'
	----//---- get hari kerja end

    IF ( @Running = '1' )
        BEGIN   
		
            SELECT  @PaySlipSigned PaySlipSigned ,
                    @CompanyName CompanyName ,
                    @CompanyLogo logo ,
                    c308.ReportID ,
                    c308.GroupBy ,
                    c308P.PayslipDescription ParentDescription ,
                    c308.Sequence ,
                    c308.PayslipDescription ,
                    c308.PayrollType ,
                    c308.WageType ,
                    c308.IsDisplayWageType ,
                    c308.IsDisplayNumber ,
                    c308.IsDisplayRate ,
                    c308.IsDisplayAmount ,
                    c308.StartDate ,
                    c308.EndDate ,
                    c308.IsActive ,
                    c308.ChangeBy ,
                    c308.ChangeDate ,
                    c308.CreateBy ,
                    c308.CreateDate ,
                    tr301.EmployeeID ,
                    tr301.PayPeriodMonth ,
                    tr301.PayPeriodYear ,
                    tr301.RunPeriodMonth ,
                    tr301.RunPeriodYear ,
                    tr301.SplitIndicator ,
					CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Rate), '0'), ',', '.') AS DECIMAL) 'rate' ,
                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount), '0'), ',','.') AS DECIMAL) 'amount' ,
                    CASE WHEN c308.WageType = '1001' THEN CONVERT(DECIMAL(22,2),@TotAtt) ELSE CONVERT(DECIMAL(22,2),tr301.Number) END AS Number,
                    tr301.Currency ,
                    tr301.Flag ,
                    md001.FullName ,
					md001.ExternalID ,
                    md003.TaxStatus ,
                    taxstat.TaxStatusDescription TaxStatusDesc ,
                    md001.MaritalStatus ,
                    ms.MaritalStatusDescription MaritalDesc ,
                    md003.PayrollType AS PayIndustry ,
                    md003.PayrollArea ,
                    md003.PayrollClass ,
                    md003.PayrollGrade ,
                    md003.NPWP ,
                    grd.PayrollGradeDescription AS PayGradeDesc ,
                    md002.Job ,
                    md002.EmployeeArea ,
                    area.EmployeeAreaDescription AreaDesc ,
                    md002.Organization ,
                    md002.OrgEasy AS OrgDesc, --org.ObjectDescription OrgDesc , ----// comment for ekno
                    md002.Position ,
                    md002.PositionEasy AS PosDesc --pos.ObjectDescription PosDesc ----// comment for ekno
					,@Disclaimer AS Disclaimer
					,pa22.BenefitNo AS NoKPJ
					,@TotHK AS TotHK
            FROM    dbo.PHRPYCU0308 AS c308
                    LEFT JOIN dbo.PHRPYCU0308 AS c308P ON c308.GroupBy = c308P.ReportID
					INNER JOIN ( SELECT * ,
                                        'N' RetroType
                                 FROM   PHRPYTR0301
                                 UNION ALL
                                 SELECT * ,
                                        'R' RetroType
                                 FROM   PHRPYTR0301RET
                               ) tr301 ON c308.WageType = tr301.WageType
                                          AND c308.RetroType = tr301.RetroType
                                          AND ( c308.PayrollType = @PayType OR @PayType = '' )
                    INNER JOIN ( SELECT m1.*
                                 FROM   dbo.PHRPA0001 m1
                                 WHERE  ( m1.StartDate <= @Endda AND m1.EndDate >= @Endda)
                               ) md001 ON md001.EmployeeID = tr301.EmployeeID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHRPA0003 m1
                                WHERE   ( m1.StartDate <= @Endda AND m1.EndDate >= @Endda)
                              ) md003 ON md001.EmployeeID = md003.EmployeeID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHRPA0002 m1
                                WHERE   ( m1.StartDate <= @Endda AND m1.EndDate >= @Endda)
                              ) md002 ON tr301.EmployeeID = md002.EmployeeID
                    LEFT JOIN dbo.PCMEPEMPAREA area ON md002.EmployeeArea = area.EmployeeArea
                                                       AND area.StartDate <= @Endda AND area.EndDate >= @Endda
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHROM0001 m1
                                WHERE   ObjectClass = 'O'
                                AND (StartDate <= @Endda AND EndDate >= @Endda)
                              ) org ON md002.Organization = org.ObjectID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    PHROM0001 m1
                                WHERE   ObjectClass = 'P'
                                AND (StartDate <= @Endda AND EndDate >= @Endda)
                              ) pos ON md002.Position = pos.ObjectID
                    LEFT JOIN dbo.PHRPYPGRAD grd ON md003.PayrollGrade = grd.PayrollGrade
													AND md003.PayrollClass = grd.PayrollClass
                                                    AND grd.StartDate <= @Endda
                                                    AND grd.EndDate >= @Endda
                    LEFT JOIN dbo.PHRPAMSTAT ms ON md001.MaritalStatus = ms.MaritalStatus
                                                   AND ms.StartDate <= @Endda
                                                   AND ms.EndDate >= @Endda
                    LEFT JOIN dbo.PHRPYTAXSTAT taxstat ON md003.TaxStatus = taxstat.TaxStatus
                                                          AND taxstat.StartDate <= @Endda
                                                          AND taxstat.EndDate >= @Endda
					LEFT JOIN dbo.PHRPA0022 pa22 ON md002.EmployeeID = pa22.EmployeeID
													AND pa22.StartDate <= @Endda AND pa22.EndDate >= @Endda
            WHERE   tr301.EmployeeID = @EmpID
                    AND tr301.PayPeriodMonth = @PayMonth
                    AND tr301.PayPeriodYear = @PayYear
                    AND tr301.RunPeriodMonth = @PayMonth
                    AND tr301.RunPeriodYear = @PayYear
                    AND CAST(REPLACE(ISNULL(dbo.GetPEN(tr301.Amount), '0'),
                                     ',', '.') AS DECIMAL) <> 0
            ORDER BY c308.GroupBy ,
                    c308.WageType
        END
    ELSE
        BEGIN   
		
            SELECT  @PaySlipSigned PaySlipSigned ,
                    @CompanyName CompanyName ,
                    @CompanyLogo logo ,
                    c308.ReportID ,
                    c308.GroupBy ,
                    c308P.PayslipDescription ParentDescription ,
                    c308.Sequence ,
                    c308.PayslipDescription ,
                    c308.PayrollType ,
                    c308.WageType ,
                    c308.IsDisplayWageType ,
                    c308.IsDisplayNumber ,
                    c308.IsDisplayRate ,
                    c308.IsDisplayAmount ,
                    c308.StartDate ,
                    c308.EndDate ,
                    c308.IsActive ,
                    c308.ChangeBy ,
                    c308.ChangeDate ,
                    c308.CreateBy ,
                    c308.CreateDate ,
                    tr301Sim.EmployeeID ,
                    tr301Sim.PayPeriodMonth ,
                    tr301Sim.PayPeriodYear ,
                    tr301Sim.RunPeriodMonth ,
                    tr301Sim.RunPeriodYear ,
                    tr301Sim.SplitIndicator ,
                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301Sim.Rate), '0'), ',',
                                 '.') AS DECIMAL) 'rate' ,
                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301Sim.Amount), '0'), ',',
                                 '.') AS DECIMAL) 'amount' ,
                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301RSim.Rate), '0'), ',',
                                 '.') AS DECIMAL) 'rate_r' ,
                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301RSim.Amount), '0'),
                                 ',', '.') AS DECIMAL) 'amount_r' ,
                    ( CAST(REPLACE(ISNULL(dbo.GetPEN(tr301Sim.Rate), '0'), ',',
                                   '.') AS DECIMAL)
                      + CAST(REPLACE(ISNULL(dbo.GetPEN(tr301RSim.Rate), '0'),
                                     ',', '.') AS DECIMAL) ) 'total_rate' ,
                    ( CAST(REPLACE(ISNULL(dbo.GetPEN(tr301Sim.Amount), '0'),
                                   ',', '.') AS DECIMAL)
                      + CAST(REPLACE(ISNULL(dbo.GetPEN(tr301RSim.Amount), '0'),
                                     ',', '.') AS DECIMAL) ) 'total_amount' ,
                    tr301Sim.Number ,
                    tr301Sim.Currency ,
                    tr301Sim.Flag ,
                    md001.FullName ,
					md001.ExternalID ,
                    md003.TaxStatus ,
                    taxstat.TaxStatusDescription TaxStatusDesc ,
                    md001.MaritalStatus ,
                    ms.MaritalStatusDescription MaritalDesc ,
                    md003.PayrollType AS PayIndustry ,
                    md003.PayrollArea ,
                    md003.PayrollClass ,
                    md003.PayrollGrade ,
                    md003.NPWP ,
                    grd.PayrollGradeDescription AS PayGradeDesc ,
                    md002.Job ,
                    md002.EmployeeArea ,
                    area.EmployeeAreaDescription AreaDesc ,
                    md002.Organization ,
                    org.ObjectDescription OrgDesc ,
                    md002.Position ,
                    pos.ObjectDescription PosDesc
					,@Disclaimer AS Disclaimer
            FROM    dbo.PHRPYCU0308 AS c308
                    LEFT JOIN dbo.PHRPYCU0308 AS c308P ON c308.GroupBy = c308P.ReportID
                    INNER JOIN ( SELECT * ,
                                        'N' RetroType
                                 FROM   dbo.PHRPYTR0301SIM
                               ) tr301Sim ON c308.WageType = tr301Sim.WageType
                                             AND c308.RetroType = tr301Sim.RetroType
                                             AND ( c308.PayrollType = @PayType
                                                   OR @PayType = ''
                                                 )
                    LEFT OUTER JOIN ( SELECT    * ,
                                                'R' retrotype
                                      FROM      dbo.PHRPYTR0301RETSIM
                                    ) tr301RSim ON c308.WageType = tr301RSim.WageType
                                                   AND ( c308.PayrollType = @PayType
                                                         OR @PayType = ''
                                                       )
                                                   AND tr301RSim.EmployeeID = @EmpID
                                                   --AND tr301RSim.PayPeriodMonth = @PayMonth
                                                   --AND tr301RSim.PayPeriodYear = @PayYear
                                                   AND tr301RSim.RunPeriodMonth = @PayMonth
                                                   AND tr301RSim.RunPeriodYear = @PayYear
                    INNER JOIN ( SELECT m1.*
                                 FROM   dbo.PHRPA0001 m1
                                 WHERE  ( m1.StartDate <= @Endda
                                          AND m1.EndDate >= @Endda
                                        )
                               ) md001 ON md001.EmployeeID = tr301Sim.EmployeeID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHRPA0003 m1
                                WHERE   ( m1.StartDate <= @Endda
                                          AND m1.EndDate >= @Endda
                                        )
                              ) md003 ON md001.EmployeeID = md003.EmployeeID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHRPA0002 m1
                                WHERE   ( m1.StartDate <= @Endda
                                          AND m1.EndDate >= @Endda
                                        )
                              ) md002 ON tr301Sim.EmployeeID = md002.EmployeeID
                    LEFT JOIN dbo.PCMEPEMPAREA area ON md002.EmployeeArea = area.EmployeeArea
                                                       AND area.StartDate <= @Endda
                                                       AND area.EndDate >= @Endda
                    LEFT JOIN ( SELECT  m1.*
                                FROM    dbo.PHROM0001 m1
                                WHERE   ObjectClass = 'O'
                                AND (StartDate <= @Endda AND EndDate >= @Endda)
				--AND (StartDate <= @Endda AND StartDate >= @Endda)
                              ) org ON md002.Organization = org.ObjectID
                    LEFT JOIN ( SELECT  m1.*
                                FROM    PHROM0001 m1
                                WHERE   ObjectClass = 'P'
                                AND (StartDate <= @Endda AND EndDate >= @Endda)
				--AND (StartDate <= @Endda AND StartDate >= @Endda)
                              ) pos ON md002.Position = pos.ObjectID
                    LEFT JOIN dbo.PHRPYPGRAD grd ON md003.PayrollGrade = grd.PayrollGrade
													AND md003.PayrollClass = grd.PayrollClass
                                                    AND grd.StartDate <= @Endda
                                                    AND grd.EndDate >= @Endda
                    LEFT JOIN dbo.PHRPAMSTAT ms ON md001.MaritalStatus = ms.MaritalStatus
                                                   AND ms.StartDate <= @Endda
                                                   AND ms.EndDate >= @Endda
                    LEFT JOIN dbo.PHRPYTAXSTAT taxstat ON md003.TaxStatus = taxstat.TaxStatus
                                                          AND taxstat.StartDate <= @Endda
                                                          AND taxstat.EndDate >= @Endda
            WHERE   tr301Sim.EmployeeID = @EmpID
                    AND tr301Sim.PayPeriodMonth = @PayMonth
                    AND tr301Sim.PayPeriodYear = @PayYear
                    AND tr301Sim.RunPeriodMonth = @PayMonth
                    AND tr301Sim.RunPeriodYear = @PayYear
                    AND CAST(REPLACE(ISNULL(dbo.GetPEN(tr301Sim.Amount), '0'),
                                     ',', '.') AS DECIMAL) <> 0
            ORDER BY c308.GroupBy ,
                    c308.WageType
        END