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
                                           )
    WHERE   c.EmployeeID = @EmployeeID
            AND c.PayPeriodMonth = @PayPeriodMonth
            AND c.PayPeriodYear = @PayPeriodYear
            AND c.RunPeriodMonth = @RunPeriodMonth
            AND c.RunPeriodYear = @RunPeriodYear 
	--AND wt.code <> 'THP'
            AND dbo.CekObjectKey(@Key1, @Key2, 'M!N0V@2010') > 0
    ORDER BY c.EmployeeID ,
            wt.Sequence ,
            wt.WageTypeDefinition