KJL

??ALTER PROCEDURE [dbo].[rp_prest] --exec rp_prest '100', '00000324', '02-2010', '02-2010', 'scriberion', 'triadblank'

--	-- Add the parameters for the stored procedure here

    @EmployeeID VARCHAR(18) ,

    @RunPeriod VARCHAR(8) ,

    @PayPeriod VARCHAR(8) ,

    @Key1 NVARCHAR(MAX) ,

    @Key2 NVARCHAR(MAX) ,

    @MenuID VARCHAR(10) ,

    @UserID VARCHAR(10)

----WITH ENCRYPTION

AS 

--DECLARE @EmployeeID VARCHAR(8)

--DECLARE @RunPeriod VARCHAR(8)

--DECLARE @PayPeriod VARCHAR(8)

--DECLARE @Key1 NVARCHAR(MAX)

--DECLARE @Key2 NVARCHAR(MAX)

--DECLARE @UserID VARCHAR(10) = 'minovais'

--DECLARE @MenuID VARCHAR(10) = 'PA01'

--SET @EmployeeID = '00000008'

--SET @RunPeriod = '012016'

--SET @PayPeriod = '012016'

--SET @Key1 = 'scriberion'

--SET @Key2 = 'triadblank'

--BEGIN

--	-- SET NOCOUNT ON added to prevent extra result sets from

--	-- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    DECLARE @RunPeriodMonth VARCHAR(2)

    DECLARE @RunPeriodYear VARCHAR(4)

    DECLARE @PayPeriodMonth VARCHAR(2)

    DECLARE @PayPeriodYear VARCHAR(4)

    DECLARE @begda AS NVARCHAR(8)

    DECLARE @endda AS NVARCHAR(8)

        ------------------ // Get Otorisasi //---------------

    DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )

    INSERT  INTO @TableResult

            EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)

                @UserID = @UserID -- varchar(max)

------------------ // End Get Otorisasi //---------------

    SET @RunPeriodMonth = LEFT(@RunPeriod, 2) --01

    SET @RunPeriodYear = RIGHT(@RunPeriod, 4) --2016

    SET @PayPeriodMonth = LEFT(@PayPeriod, 2) --01

    SET @PayPeriodYear = RIGHT(@PayPeriod, 4) --2016

    SET @begda = @RunPeriodYear + @RunPeriodMonth + '01'

    SET @endda = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1, @begda)), 112)

    SELECT  c.EmployeeID ,

            pd.FullName ,

            @PayPeriod 'PayPeriod' ,

            @RunPeriod 'RunPeriod' ,

            c.WageType ,

            wt.WTDefinitionDesc ,

            wt.CalculationDescription ,

            wt.WageGroup ,

            wt.Taxable ,

            wt.Regular ,

            wt.Type ,

            wt.PreTax ,

            wt.Valuation ,

            wt.Overwrite ,

            wt.IsPesangon ,

            wt.Base ,

            wt.Percentage ,

            wt.TaxSubsidy ,

            wt.Prorate1 ,

            wt.ProrateType ,

            wt.ProrateMethod ,

            wt.DebitAcc ,

            wt.CreditAcc ,

            wt.SPTNo ,

            wt.WageTypeGenerated ,

            wt.WTGeneratedPercentage ,

            wt.Notes ,

            ISNULL(m.Number, 1) Number ,

	--m.Rate,

            CAST(ISNULL(dbo.GetPEN(m.Rate), '0') AS DECIMAL) 'Rate' ,

	--m.Amount,

                --ISNULL(dbo.GetPEN(m.Amount), '0') 'Amount' , 

            CAST(REPLACE(ISNULL(dbo.GetPEN(m.Amount), '0'), ',', '.') AS DECIMAL) 'Amount' ,

	--c.amount 'cumulative_amount',

                --ISNULL(dbo.GetPEN(c.Amount), '0') 'CumulativeAmount'

            CAST(REPLACE(ISNULL(dbo.GetPEN(c.Amount), '0'), ',', '.') AS DECIMAL) 'CumulativeAmount'

    FROM    dbo.PHRPYTR0301CUM c

            LEFT JOIN dbo.PHRPYTR0301 m ON ( m.EmployeeID = c.EmployeeID

                                             AND m.PayPeriodMonth = c.PayPeriodMonth

                                             AND m.PayPeriodYear = c.PayPeriodYear

                                             AND m.RunPeriodMonth = c.RunPeriodMonth

                                             AND m.RunPeriodYear = c.RunPeriodYear

                                             AND m.SplitIndicator = c.SplitIndicator

                                             AND m.WageType = c.WageType

                                           )

            INNER JOIN @TableResult lt ON lt.EmployeeID = c.EmployeeID

            INNER JOIN dbo.PHRPYCU0300 wt ON ( c.WageType = wt.WageTypeDefinition

                                               AND wt.StartDate <= @endda

                                               AND wt.EndDate >= @endda

                                             )

            INNER JOIN dbo.PHRPA0001 pd ON ( c.EmployeeID = pd.EmployeeID

                                             AND pd.StartDate <= @endda

                                             AND pd.EndDate >= @endda