PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CRPTLPPOMESSPAYSLIP]    Script Date: 20/06/2023 13.41.30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CRPTLPPOMESSPAYSLIP]

    (

      @PayPeriod VARCHAR(6) ,

      @EmpID VARCHAR(20) ,

      @Running VARCHAR(2) ,

      @PayType VARCHAR(2) 

    )

AS

    --DECLARE @PayPeriod VARCHAR(6) = '202302'

    --DECLARE @EmpID VARCHAR(8) = '11050019'

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

    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

            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

            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

    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 ,

					dbo.fn_formatdatetime_indonesia(c308.StartDate, 'DD MMMM YYYY') as StartDateDesc,

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

                    CAST(REPLACE(ISNULL(dbo.GetPEN(tr301R.Rate), '0'), ',',