USE [MinovaES_BIA_Prod]
GO
/****** Object:  StoredProcedure [dbo].[PRPTPYWTRECAPDETAIL]    Script Date: 11/08/2025 14.16.48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PRPTPYWTRECAPDETAIL]
    (
      @PayPeriod VARCHAR(6) ,
      @EmployeeIDFrom VARCHAR(8) ,
      @EmployeeIDTo VARCHAR(8) ,
      @PayrollGroupStart VARCHAR(8) ,
      @PayrollGroupEnd VARCHAR(8) ,
      @CostCenterStart VARCHAR(8) ,
      @CostCenterEnd VARCHAR(8) ,
      @ReportType VARCHAR(50) ,
      @UserID VARCHAR(MAX) ,
      @MenuID VARCHAR(10)
    )
AS 
--DECLARE @PayPeriod VARCHAR(6) = '202507'
--DECLARE @EmployeeIDFrom VARCHAR(8) = ''
--DECLARE @EmployeeIDTo VARCHAR(8)= ''
--DECLARE @PayrollGroupStart VARCHAR(4) = '1030'
--DECLARE @PayrollGroupEnd VARCHAR(4) = ''
--DECLARE @CostCenterStart VARCHAR(8) = ''
--DECLARE @CostCenterEnd VARCHAR(8) = ''
--DECLARE @ReportType VARCHAR(50) = '0001'
--DECLARE @UserID VARCHAR(MAX)= 'nando'
--DECLARE @MenuID VARCHAR(10) = 'PYAR03'
 
 --//-- Variable Declaration
DECLARE @PayPeriodMonth VARCHAR(2)
DECLARE @PayPeriodYear VARCHAR(4)
DECLARE @PayPeriodStartDate VARCHAR(8)
DECLARE @PayPeriodEndDate VARCHAR(8)
DECLARE @PayPeriodEndDateDt DATETIME

DECLARE @EmpIDMin VARCHAR(8)
DECLARE @EmpIDMax VARCHAR(8)
DECLARE @PayrollGroupMin VARCHAR(4)
DECLARE @PayrollGroupMax VARCHAR(4)
DECLARE @CostCenterMin VARCHAR(8)
DECLARE @CostCenterMax VARCHAR(8)

--//-- declare table-table

DECLARE @tbl_employee TABLE
    (
      EmpID VARCHAR(20) ,
      EmpName VARCHAR(250) ,
      CCG VARCHAR(250) ,
      CCGAbr VARCHAR(250) ,
      CCGDesc VARCHAR(250) ,
      CC VARCHAR(250) ,
      CCAbr VARCHAR(250) ,
      CCDesc VARCHAR(250)
    )

DECLARE @tbl_employee2 TABLE
    (
      employee_id VARCHAR(250) ,
      employee_name VARCHAR(250) ,
      employee_area_id VARCHAR(250) ,
      employee_area_descr VARCHAR(250) ,
      employee_office_id VARCHAR(250) ,
      employee_office_descr VARCHAR(250) ,
      employee_status_id VARCHAR(250) ,
      employee_status_descr VARCHAR(250) ,
      employee_type_id VARCHAR(250) ,
      employee_type_descr VARCHAR(250) ,
      employee_sub_type_id VARCHAR(250) ,
      employee_sub_type_descr VARCHAR(250) ,
      payroll_group_id VARCHAR(250) ,
      payroll_group_descr VARCHAR(250) ,
      organization_id VARCHAR(250) ,
      organization_descr VARCHAR(250) ,
      CostCenter VARCHAR(250) ,
      cost_center_abbr VARCHAR(250) ,
      cost_center_descr VARCHAR(250) ,
      cost_center_group_id VARCHAR(250) ,
      cost_center_group_abbr VARCHAR(250) ,
      cost_center_group_descr VARCHAR(250) ,
      position_id VARCHAR(250) ,
      position_descr VARCHAR(250) ,
      job_id VARCHAR(250) ,
      job_descr VARCHAR(250) ,
      pay_type_id VARCHAR(250) ,
      pay_type_descr VARCHAR(250) ,
      pay_class_id VARCHAR(250) ,
      pay_class_descr VARCHAR(250) ,
      pay_grade_id VARCHAR(250) ,
      pay_grade_descr VARCHAR(250) ,
      pay_area_id VARCHAR(250) ,
      pay_area_descr VARCHAR(250)
    )

DECLARE @tbl_tr_enc TABLE
    (
      employee_id VARCHAR(20) ,
      payPeriodMonth VARCHAR(2) ,
      payPeriodYear VARCHAR(4) ,
      runPeriodMonth VARCHAR(2) ,
      runPeriodYear VARCHAR(4) ,
      wageType VARCHAR(4) ,
      amount VARCHAR(250)
    )

--//-- Setting variable
SET @PayPeriodMonth = SUBSTRING(@PayPeriod, 5, 2)
SET @PayPeriodYear = SUBSTRING(@PayPeriod, 1, 4)
SET @PayPeriodStartDate = @PayPeriod + '01'
SET @PayPeriodEndDateDt = DATEADD(dd, -1,
                                  DATEADD(mm, 1,
                                          CONVERT(DATETIME, @PayPeriodStartDate)))
SET @PayPeriodEndDate = @PayPeriod + CONVERT(VARCHAR(2), DATEPART(dd,
                                                              @PayPeriodEndDateDt))

--//-- Set Filter

SELECT  @EmpIDMin = '00000000'
--MIN(EmployeeID) FROM dbo.PHRPYTR0300 WITH (NOLOCK) WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth
SELECT  @EmpIDMax = '99999999'
--MAX(EmployeeID) FROM PHRPYTR0300 WITH (NOLOCK) WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth
SELECT  @CostCenterMin = '00000000'
--MIN(CostCenter) FROM PHRPYTR0300 WITH (NOLOCK) WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth
SELECT  @CostCenterMax = '99999999'
--MAX(CostCenter) FROM PHRPYTR0300 WITH (NOLOCK) WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth
SELECT  @PayrollGroupMin = '00'
--MIN(PayrollGroup) FROM PHRPYTR0300 WITH (NOLOCK)WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth
SELECT  @PayrollGroupMax = '99'
--MAX(PayrollGroup) FROM PHRPYTR0300 WITH (NOLOCK)WHERE RunPeriodYear = @PayPeriodYear AND RunPeriodMonth = @PayPeriodMonth

IF ( LTRIM(RTRIM(@EmployeeIDFrom)) <> '' ) 
    BEGIN
        SET @EmpIDMin = @EmployeeIDFrom
        IF ( LTRIM(RTRIM(@EmployeeIDTo)) <> '' ) 
            SET @EmpIDMax = @EmployeeIDTo
        ELSE 
            SET @EmpIDMax = @EmployeeIDFrom
    END

IF ( LTRIM(RTRIM(@PayrollGroupStart)) <> '' ) 
    BEGIN
        SET @PayrollGroupMin = @PayrollGroupStart
        IF ( LTRIM(RTRIM(@PayrollGroupEnd)) <> '' ) 
            SET @PayrollGroupMax = @PayrollGroupEnd
        ELSE 
            SET @PayrollGroupMax = @PayrollGroupStart
    END

IF ( LTRIM(RTRIM(@CostCenterStart)) <> '' ) 
    BEGIN
        SET @CostCenterMin = @CostCenterStart
        IF ( LTRIM(RTRIM(@CostCenterEnd)) <> '' ) 
            SET @CostCenterMax = @CostCenterEnd
        ELSE 
            SET @CostCenterMax = @CostCenterStart
    END

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


INSERT  INTO @tbl_employee
        SELECT DISTINCT
                md01.EmployeeID ,
                md01.FullName ,
                cg.ObjectID ,
                cg.Abbreviation ,
                cg.ObjectDescription ,
                cc.ObjectID ,
                cc.Abbreviation ,
                cc.ObjectDescription
        FROM    ( SELECT    md01.EmployeeID ,
                            md01.FullName
                  FROM      dbo.PHRPA0001 md01 WITH ( NOLOCK )
                  WHERE     md01.StartDate <= @PayPeriodEndDate
                            AND md01.EndDate >= @PayPeriodEndDate
                            AND md01.EmployeeID >= @EmpIDMin
                            AND md01.EmployeeID <= @EmpIDMax
                ) md01
                LEFT JOIN dbo.PHRPYTR0300 tr300 WITH ( NOLOCK ) ON md01.EmployeeID = tr300.EmployeeID
                                                              AND tr300.PayPeriodMonth = @PayPeriodMonth
                                                              AND tr300.PayPeriodYear = @PayPeriodYear
                                                              AND tr300.RunPeriodMonth = @PayPeriodMonth
                                                              AND tr300.RunPeriodYear = @PayPeriodYear
                INNER JOIN @TableResult AS TblAuth ON TblAuth.EmployeeID = tr300.EmployeeID
                LEFT JOIN dbo.PHROM0001 cc WITH ( NOLOCK ) ON tr300.CostCenter = cc.ObjectID
                                                              AND cc.ObjectClass = 'CC'
                                                              AND cc.StartDate <= @PayPeriodEndDate
                                                              AND cc.EndDate >= @PayPeriodEndDate
                LEFT JOIN dbo.PHROM0002 ccg WITH ( NOLOCK ) ON cc.ObjectID = ccg.ObjectID
                                                              AND ccg.ObjectClass = 'CC'
                                                              AND ccg.StartDate <= @PayPeriodEndDate
                                                              AND ccg.EndDate >= @PayPeriodEndDate
                                                              AND ccg.RelationshipClass = 'CG'
                                                              AND ccg.RelationshipDirectory = 'B'
                                                              AND ccg.RelationshipType = '001'
                LEFT JOIN PHROM0001 cg WITH ( NOLOCK ) ON ccg.RelationshipObject = cg.ObjectID
                                                          AND cg.ObjectClass = 'CG'
                                                          AND cg.StartDate <= @PayPeriodEndDate
                                                          AND cg.EndDate >= @PayPeriodEndDate
        WHERE   --(tr300.CostCenter >= @CostCenterMin AND tr300.CostCenter <= @CostCenterMax) 
                ( tr300.PayrollGroup >= @PayrollGroupMin
                  AND tr300.PayrollGroup <= @PayrollGroupMax
                )
                AND ( tr300.EmployeeID >= @EmpIDMin
                      AND tr300.EmployeeID <= @EmpIDMax
                    )

DECLARE @employee_id VARCHAR(8)
DECLARE @employee_name VARCHAR(50)
DECLARE @CCG VARCHAR(50)
DECLARE @CCG_Abr VARCHAR(50)
DECLARE @CCG_Desc VARCHAR(50)
DECLARE @CC VARCHAR(50)
DECLARE @CC_Abr VARCHAR(50)
DECLARE @CC_Desc VARCHAR(50)

DECLARE @tr0300 TABLE
    (
      [EmployeeID] [varchar](20) NOT NULL ,
      [PayPeriodMonth] [varchar](2) NOT NULL ,
      [PayPeriodYear] [varchar](4) NOT NULL ,
      [RunPeriodMonth] [varchar](2) NOT NULL ,
      [RunPeriodYear] [varchar](4) NOT NULL ,
      [SplitIndicator] [varchar](1) NOT NULL ,
      [StartDate] [varchar](8) NOT NULL ,
      [EndDate] [varchar](8) NOT NULL ,
      [DataStatus] [varchar](1) NULL ,
      [CompanyID] [varchar](4) NULL ,
      [CostCenter] [varchar](18) NULL ,
      [EmployeeArea] [varchar](4) NULL ,
      [EmployeeOffice] [varchar](4) NULL ,
      [EmployeeStatus] [varchar](4) NULL ,
      [EmployeeType] [varchar](4) NULL ,
      [EmployeeSubType] [varchar](4) NULL ,
      [PayrollGroup] [varchar](4) NULL ,
      [Organization] [varchar](18) NULL ,
      [Position] [varchar](18) NULL ,
      [Job] [varchar](18) NULL ,
      [PayrollType] [varchar](4) NULL ,
      [PayrollClass] [varchar](4) NULL ,
      [PayrollGrade] [varchar](4) NULL ,
      [PayrollArea] [varchar](4) NULL ,
      [PayrollCurrency] [varchar](4) NULL ,
      [PayrollCurrencyRate] [decimal](18, 7) NULL ,
      [CreateBy] [varchar](50) NULL ,
      [CreateDate] [varchar](14) NULL ,
      [ChangeBy] [varchar](50) NULL ,
      [ChangeDate] [varchar](14) NULL
    )
	
DECLARE @emp_id VARCHAR(10) 
DECLARE @pay_month VARCHAR(2) = ( SUBSTRING(@PayPeriod, 5, 2) )
DECLARE @pay_year VARCHAR(4) = ( SUBSTRING(@PayPeriod, 1, 4) )

DECLARE @start_date VARCHAR(10)
DECLARE @end_date VARCHAR(10)

DECLARE @pay_period_month VARCHAR(4) 
DECLARE @pay_period_year VARCHAR(4)
DECLARE @employe_id VARCHAR(8)
DECLARE @full_name VARCHAR(250)
DECLARE @company_id VARCHAR(4) 
DECLARE @cost_center VARCHAR(8) 
DECLARE @employee_area VARCHAR(250)  
DECLARE @employee_office VARCHAR(250) 
DECLARE @employee_status VARCHAR(250) 
DECLARE @employee_type VARCHAR(250)  
DECLARE @employee_subtype VARCHAR(250) 
DECLARE @payroll_group VARCHAR(250) 
DECLARE @organization VARCHAR(250) 
DECLARE @position VARCHAR(250) 
DECLARE @job VARCHAR(250) 
DECLARE @pay_type VARCHAR(250) 
DECLARE @pay_class VARCHAR(250) 
DECLARE @pay_grade VARCHAR(250)
DECLARE @pay_area VARCHAR(250)

--  --Get System Parameter
--DECLARE @is_encrypted VARCHAR

--SELECT * FROM dbo.PCMEPGENPARAM
--where	landscape = @landscape and
--		param = 'HR_PY_ENCRYPTED' and
--		start_date <= @payPeriodEndDate and 
--		end_date >= @payPeriodEndDate

DECLARE cur_employee CURSOR
FOR
    SELECT  *
    FROM    @tbl_employee
OPEN cur_employee
FETCH cur_employee INTO @employee_id, @employee_name, @CCG, @CCG_Abr,
    @CCG_Desc, @CC, @CC_Abr, @CC_Desc 
-- start the main processing loop.
WHILE @@Fetch_Status = 0 
    BEGIN 
        DELETE  FROM @tr0300
		
        SET @emp_id = @employee_id
        SET @start_date = ''
        SET @end_date = ''
        SET @pay_period_month = ''
        SET @pay_period_year = ''
        SET @employe_id = ''
        SET @full_name = ''
        SET @company_id = ''
        SET @cost_center = ''
        SET @employee_area = ''
        SET @employee_office = ''
        SET @employee_status = ''
        SET @employee_type = ''
        SET @employee_subtype = ''
        SET @payroll_group = ''
        SET @organization = ''
        SET @position = ''
        SET @job = ''
        SET @pay_type = ''
        SET @pay_class = ''
        SET @pay_grade = ''
        SET @pay_area = ''

        INSERT  INTO @tr0300
                SELECT  TOP 1 *
                FROM    dbo.PHRPYTR0300
                WHERE   EmployeeID = @emp_id
                        AND PayPeriodMonth = @pay_month
                        AND PayPeriodYear = @pay_year
                ORDER BY RunPeriodMonth DESC
			
        SELECT  @start_date = tr0300.StartDate ,
                @end_date = tr0300.EndDate
        FROM    @tr0300 AS tr0300

        SELECT  @pay_period_month = PayPeriodMonth ,
                @pay_period_year = PayPeriodYear
        FROM    @tr0300 AS tr0300 

        SELECT  @employe_id = md1.EmployeeID ,
                @full_name = md1.FullName
        FROM    dbo.PHRPA0001 AS md1
                INNER JOIN @tr0300 AS tr0300 ON md1.EmployeeID = tr0300.EmployeeID
                                                AND md1.StartDate <= @end_date
                                                AND md1.EndDate >= @end_date

        SELECT  @cost_center = obj.ObjectDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHROM0001 AS obj ON obj.ObjectID = tr0300.CostCenter
                                                  AND obj.ObjectClass = 'CC'
                                                  AND obj.StartDate <= @start_date
                                                  AND obj.EndDate >= @end_date
			
        SELECT  @employee_area = obj.EmployeeAreaDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PCMEPEMPAREA AS obj ON obj.EmployeeArea = tr0300.EmployeeArea
			
        SELECT  @employee_office = obj.EmployeeOfficeDesc
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PCMEPEMPOFF AS obj ON obj.EmployeeOffice = tr0300.EmployeeOffice

        SELECT  @employee_status = obj.EmployeeStatusDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PCMEPEMPSTAT AS obj ON obj.EmployeeStatus = tr0300.EmployeeStatus
			
        SELECT  @employee_type = obj.EmployeeTypeDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PCMEPEMPTYP AS obj ON obj.EmployeeType = tr0300.EmployeeType

        SELECT  @employee_subtype = obj.EmployeeSubTypeDesc
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PCMEPEMPSTYP AS obj ON obj.EmployeeSubType = tr0300.EmployeeSubType
		
        SELECT  @payroll_group = obj.PayrollGroupDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHRPYPGRUP AS obj ON obj.PayrollGroup = tr0300.PayrollGroup
		
        SELECT  @organization = obj.ObjectDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHROM0001 AS obj ON obj.ObjectID = tr0300.Organization
                                                  AND obj.ObjectClass = 'O'
                                                  AND obj.StartDate <= @end_date
                                                  AND obj.EndDate >= @end_date

        SELECT  @position = obj.ObjectDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHROM0001 AS obj ON obj.ObjectID = tr0300.Position
                                                  AND obj.ObjectClass = 'P'
                                                  AND obj.StartDate <= @end_date
                                                  AND obj.EndDate >= @end_date
			
        SELECT  @job = obj.ObjectDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHROM0001 AS obj ON obj.ObjectID = tr0300.Job
                                                  AND obj.ObjectClass = 'J'
                                                  AND obj.StartDate <= @end_date
                                                  AND obj.EndDate >= @end_date
			
        SELECT  @pay_type = obj.PayrollTypeDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHRPYPTYP AS obj ON obj.PayrollType = tr0300.PayrollType

        SELECT  @pay_class = obj.PayrollClassDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHRPYPCLASS AS obj ON obj.PayrollClass = tr0300.PayrollClass
			
        SELECT  @pay_grade = obj.PayrollGradeDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHRPYPGRAD AS obj ON obj.PayrollGrade = tr0300.PayrollGrade
			
        SELECT  @pay_area = obj.PayrollAreaDescription
        FROM    @tr0300 AS tr0300
                LEFT JOIN dbo.PHRPYPAREA AS obj ON obj.PayrollArea = tr0300.PayrollArea
			
        INSERT  INTO @tbl_employee2
                SELECT  tr0300.EmployeeID ,
                        employee_name ,
                        tr0300.EmployeeArea AS employee_area_id ,
                        a.employee_area_descr ,
                        tr0300.EmployeeOffice AS employee_office_id ,
                        a.employee_office_descr ,
                        tr0300.EmployeeStatus AS employee_status_id ,
                        a.employee_status_descr ,
                        tr0300.EmployeeType AS employee_type_id ,
                        a.employee_type_descr ,
                        tr0300.EmployeeSubtype AS employee_subtype_id ,
                        a.employee_subtype_descr ,
                        tr0300.PayrollGroup AS payroll_group_id ,
                        a.payroll_group_descr ,
                        tr0300.Organization AS organization_id ,
                        a.organization_descr ,
                        @CC ,
                        @CC_Abr ,
                        @CC_Desc ,
                        @CCG ,
                        @CCG_Abr ,
                        @CCG_Desc ,
                        tr0300.Position AS position_id ,
                        a.position_descr ,
                        tr0300.Job AS job_id ,
                        a.job_descr ,
                        tr0300.PayrollType AS pay_type_id ,
                        a.pay_type_descr ,
                        tr0300.PayrollClass AS pay_class_id ,
                        a.pay_class_descr ,
                        tr0300.PayrollGrade AS pay_grade_id ,
                        a.pay_grade_descr ,
                        tr0300.PayrollArea AS pay_area_id ,
                        a.pay_area_descr
                FROM    ( SELECT    @employe_id AS employee_id ,
                                    @pay_period_month AS pay_period_month ,
                                    @pay_period_year AS pay_period_year ,
                                    @full_name AS employee_name ,
                                    @cost_center AS cost_center_descr ,
                                    @employee_area AS employee_area_descr ,
                                    @employee_office AS employee_office_descr ,
                                    @employee_status AS employee_status_descr ,
                                    @employee_type AS employee_type_descr ,
                                    @employee_subtype AS employee_subtype_descr ,
                                    @payroll_group AS payroll_group_descr ,
                                    @organization AS organization_descr ,
                                    @position AS position_descr ,
                                    @job AS job_descr ,
                                    @pay_type AS pay_type_descr ,
                                    @pay_class AS pay_class_descr ,
                                    @pay_grade AS pay_grade_descr ,
                                    @pay_area AS pay_area_descr
                        ) AS a
                        INNER JOIN @tr0300 AS tr0300 ON a.employee_id = tr0300.EmployeeID
	  
        FETCH cur_employee INTO @employee_id, @employee_name, @CCG, @CCG_Abr,
            @CCG_Desc, @CC, @CC_Abr, @CC_Desc  
    END
CLOSE cur_employee
DEALLOCATE cur_employee
	

DECLARE @hr_cust_py_report TABLE
    (
      [StartDate] [varchar](8) NOT NULL ,
      [EndDate] [varchar](8) NOT NULL ,
      [ReportType] [varchar](4) NOT NULL ,
      [Row] [varchar](4) NOT NULL ,
      [Col] [varchar](4) NOT NULL ,
      [Seq] [varchar](4) NOT NULL ,
      [Wagetype] [varchar](4) NULL ,
      [WagetypeDesc] [varchar](50) NULL ,
      [IsActive] [varchar](4) NULL ,
      [Notes] [varchar](14) NULL ,
      [CreateBy] [varchar](18) NULL ,
      [CreateDate] [varchar](14) NULL ,
      [ChangeBy] [varchar](18) NULL ,
      [ChangeDate] [varchar](14) NULL
    )
  
INSERT  INTO @hr_cust_py_report
        SELECT  [StartDate] ,
                [EndDate] ,
                [ReportType] ,
                [Row] ,
                [Col] ,
                [Seq] ,
                [Wagetype] ,
                [WagetypeDesc] ,
                [IsActive] ,
                [Notes] ,
                [CreateBy] ,
                [CreateDate] ,
                [ChangeBy] ,
                [ChangeDate]
        FROM    dbo.PHRPYWTRCPDETAIL
        WHERE   StartDate <= @PayPeriodEndDate
                AND EndDate >= @PayPeriodEndDate
                AND ReportType = @ReportType				  

DECLARE @hr_cust_py_report_col_name TABLE
    (
      [rep_id] [varchar](50) NULL ,
      [row] [int] NULL ,
      [col_no] [int] NULL ,
      column_name VARCHAR(6)
    )

INSERT  INTO @hr_cust_py_report_col_name
        SELECT  * ,
                'WT' + RIGHT('00'
                             + CONVERT(VARCHAR, ( ROW_NUMBER() OVER ( ORDER BY ReportType, [Row], Col )
                                                  + 49 ) / 50), 2)
                + RIGHT('00'
                        + CONVERT(VARCHAR, ( ( ROW_NUMBER() OVER ( ORDER BY ReportType, [Row], Col )
                                               - 1 ) % 50 ) + 1), 2) AS ColName
        FROM    ( SELECT DISTINCT
                            ReportType ,
                            [Row] ,
                            Col
                  FROM      @hr_cust_py_report
                ) a
        ORDER BY ReportType ,
                [Row] ,
                Col


DECLARE @row INT
DECLARE @col INT
DECLARE @column_name VARCHAR(6)
SET @row = 0
SET @col = 0
WHILE @row < 4 
    BEGIN
        SET @row = @row + 1
        SET @col = 0
        WHILE @col < 50 
            BEGIN
                SET @col = @col + 1
                SET @column_name = NULL
		
                SELECT  @column_name = column_name
                FROM    @hr_cust_py_report_col_name
                WHERE   column_name = 'WT' + RIGHT('00'
                                                   + CONVERT(VARCHAR, @row), 2)
                        + RIGHT('00' + CONVERT(VARCHAR, @col), 2)
		
                IF @column_name IS NULL
                    OR @column_name = '' 
                    BEGIN
                        INSERT  INTO @hr_cust_py_report_col_name
                                SELECT  '' ,
                                        0 ,
                                        0 ,
                                        'WT' + RIGHT('00'
                                                     + CONVERT(VARCHAR, @row),
                                                     2) + RIGHT('00'
                                                              + CONVERT(VARCHAR, @col),
                                                              2)
                    END
            END
    END

DECLARE @hr_cust_py_report_desc TABLE
    (
      [rep_id] [varchar](50) NOT NULL ,
      [row] [int] NOT NULL ,
      [col_no] [int] NOT NULL ,
      column_name VARCHAR(6) ,
      [description] [varchar](50) NULL
    )

INSERT  INTO @hr_cust_py_report_desc
        SELECT  a.* ,
                b.WagetypeDesc
        FROM    @hr_cust_py_report_col_name AS a
                LEFT JOIN ( SELECT  MAX(ReportType) AS rep_id ,
                                    MAX([Row]) AS [row] ,
                                    MAX(Col) AS col_no ,
                                    MAX(WagetypeDesc) AS WagetypeDesc
                            FROM    @hr_cust_py_report
                            GROUP BY ReportType ,
                                    [Row] ,
                                    Col
                          ) AS b ON a.rep_id = b.rep_id
                                    AND a.[row] = b.[row]
                                    AND a.col_no = b.col_no

INSERT  INTO @tbl_tr_enc
        SELECT DISTINCT
                pr.EmployeeID ,
                pr.PayPeriodMonth ,
                pr.PayPeriodYear ,
                pr.RunPeriodMonth ,
                pr.RunPeriodYear ,
                pr.WageType ,
                pr.Amount
        FROM    dbo.PHRPYTR0301 pr WITH ( NOLOCK )
                INNER JOIN @tbl_employee emp ON pr.EmployeeID = emp.EmpID
                INNER JOIN dbo.PHRPYWTRCPDETAIL rep ON pr.WageType = rep.Wagetype
        WHERE   pr.RunPeriodYear = @payPeriodYear
                AND pr.RunPeriodMonth = @payPeriodMonth
                AND pr.PayPeriodYear = @payPeriodYear
                AND pr.PayPeriodMonth = @payPeriodMonth
	
INSERT  INTO @tbl_tr_enc
        SELECT DISTINCT
                pr.EmployeeID ,
                pr.PayPeriodMonth ,
                pr.PayPeriodYear ,
                pr.RunPeriodMonth ,
                pr.RunPeriodYear ,
                pr.WageType ,
                pr.Amount
        FROM    dbo.PHRPYTR0301RET pr WITH ( NOLOCK )
                INNER JOIN @tbl_employee emp ON pr.EmployeeID = emp.EmpID
                INNER JOIN dbo.PHRPYWTRCPDETAIL rep ON pr.WageType = rep.Wagetype
        WHERE   pr.RunPeriodYear = @payPeriodYear
                AND pr.RunPeriodMonth = @payPeriodMonth
                AND pr.PayPeriodYear = @payPeriodYear
                AND pr.PayPeriodMonth = @payPeriodMonth
	
DECLARE @tbl_tr_dec TABLE
    (
      col_no INT ,
      employee_id VARCHAR(20) ,
      amount BIGINT
    )
	
INSERT  INTO @tbl_tr_dec
        SELECT DISTINCT
                col_no ,
                employee_id ,
                SUM(amount) AS amount
        FROM    ( SELECT    b.col_no ,
                            c.employee_id ,
                            CAST(REPLACE(ISNULL(dbo.GetPEN(Amount), '0'), ',',
                                         '.') AS DECIMAL(22, 0)) AS Amount
                  FROM      @hr_cust_py_report_desc AS b
                            INNER JOIN @hr_cust_py_report AS a ON a.Col = b.col_no
                                                              AND a.ReportType = b.rep_id
                                                              AND a.Row = b.row
                            INNER JOIN @tbl_tr_enc c ON a.Wagetype = c.wageType
                ) AS d
        GROUP BY col_no ,
                employee_id

SELECT  employee_id ,
        employee_name ,
        employee_area_id ,
        employee_area_descr ,
        employee_office_id ,
        employee_office_descr ,
        employee_status_id ,
        employee_status_descr ,
        employee_type_id ,
        employee_type_descr ,
        employee_sub_type_id ,
        employee_sub_type_descr ,
        payroll_group_id ,
        payroll_group_descr ,
        organization_id ,
        organization_descr ,
        CostCenter ,
        cost_center_abbr ,
        cost_center_descr ,
        cost_center_group_id ,
        cost_center_group_abbr ,
        cost_center_group_descr ,
        position_id ,
        position_descr ,
        job_id ,
        job_descr ,
        pay_type_id ,
        pay_type_descr ,
        pay_class_id ,
        pay_class_descr ,
        pay_grade_id ,
        pay_grade_descr ,
        pay_area_id ,
        pay_area_descr ,
        MAX([WT0101]) AS [WT0101] ,
        MAX([WT0102]) AS [WT0102] ,
        MAX([WT0103]) AS [WT0103] ,
        MAX([WT0104]) AS [WT0104] ,
        MAX([WT0105]) AS [WT0105] ,
        MAX([WT0106]) AS [WT0106] ,
        MAX([WT0107]) AS [WT0107] ,
        MAX([WT0108]) AS [WT0108] ,
        MAX([WT0109]) AS [WT0109] ,
        MAX([WT0110]) AS [WT0110] ,
        MAX([WT0111]) AS [WT0111] ,
        MAX([WT0112]) AS [WT0112] ,
        MAX([WT0113]) AS [WT0113] ,
        MAX([WT0114]) AS [WT0114] ,
        MAX([WT0115]) AS [WT0115] ,
        MAX([WT0116]) AS [WT0116] ,
        MAX([WT0117]) AS [WT0117] ,
        MAX([WT0118]) AS [WT0118] ,
        MAX([WT0119]) AS [WT0119] ,
        MAX([WT0120]) AS [WT0120] ,
        MAX([WT0121]) AS [WT0121] ,
        MAX([WT0122]) AS [WT0122] ,
        MAX([WT0123]) AS [WT0123] ,
        MAX([WT0124]) AS [WT0124] ,
        MAX([WT0125]) AS [WT0125] ,
        MAX([WT0126]) AS [WT0126] ,
        MAX([WT0127]) AS [WT0127] ,
        MAX([WT0128]) AS [WT0128] ,
        MAX([WT0129]) AS [WT0129] ,
        MAX([WT0130]) AS [WT0130] ,
        MAX([WT0131]) AS [WT0131] ,
        MAX([WT0132]) AS [WT0132] ,
        MAX([WT0133]) AS [WT0133] ,
        MAX([WT0134]) AS [WT0134] ,
        MAX([WT0135]) AS [WT0135] ,
        MAX([WT0136]) AS [WT0136] ,
        MAX([WT0137]) AS [WT0137] ,
        MAX([WT0138]) AS [WT0138] ,
        MAX([WT0139]) AS [WT0139] ,
        MAX([WT0140]) AS [WT0140] ,
        MAX([WT0141]) AS [WT0141] ,
        MAX([WT0142]) AS [WT0142] ,
        MAX([WT0143]) AS [WT0143] ,
        MAX([WT0144]) AS [WT0144] ,
        MAX([WT0145]) AS [WT0145] ,
        MAX([WT0146]) AS [WT0146] ,
        MAX([WT0147]) AS [WT0147] ,
        MAX([WT0148]) AS [WT0148] ,
        MAX([WT0149]) AS [WT0149] ,
        MAX([WT0150]) AS [WT0150] ,
        MAX([WT0201]) AS [WT0201] ,
        MAX([WT0202]) AS [WT0202] ,
        MAX([WT0203]) AS [WT0203] ,
        MAX([WT0204]) AS [WT0204] ,
        MAX([WT0205]) AS [WT0205] ,
        MAX([WT0206]) AS [WT0206] ,
        MAX([WT0207]) AS [WT0207] ,
        MAX([WT0208]) AS [WT0208] ,
        MAX([WT0209]) AS [WT0209] ,
        MAX([WT0210]) AS [WT0210] ,
        MAX([WT0211]) AS [WT0211] ,
        MAX([WT0212]) AS [WT0212] ,
        MAX([WT0213]) AS [WT0213] ,
        MAX([WT0214]) AS [WT0214] ,
        MAX([WT0215]) AS [WT0215] ,
        MAX([WT0216]) AS [WT0216] ,
        MAX([WT0217]) AS [WT0217] ,
        MAX([WT0218]) AS [WT0218] ,
        MAX([WT0219]) AS [WT0219] ,
        MAX([WT0220]) AS [WT0220] ,
        MAX([WT0221]) AS [WT0221] ,
        MAX([WT0222]) AS [WT0222] ,
        MAX([WT0223]) AS [WT0223] ,
        MAX([WT0224]) AS [WT0224] ,
        MAX([WT0225]) AS [WT0225] ,
        MAX([WT0226]) AS [WT0226] ,
        MAX([WT0227]) AS [WT0227] ,
        MAX([WT0228]) AS [WT0228] ,
        MAX([WT0229]) AS [WT0229] ,
        MAX([WT0230]) AS [WT0230] ,
        MAX([WT0231]) AS [WT0231] ,
        MAX([WT0232]) AS [WT0232] ,
        MAX([WT0233]) AS [WT0233] ,
        MAX([WT0234]) AS [WT0234] ,
        MAX([WT0235]) AS [WT0235] ,
        MAX([WT0236]) AS [WT0236] ,
        MAX([WT0237]) AS [WT0237] ,
        MAX([WT0238]) AS [WT0238] ,
        MAX([WT0239]) AS [WT0239] ,
        MAX([WT0240]) AS [WT0240] ,
        MAX([WT0241]) AS [WT0241] ,
        MAX([WT0242]) AS [WT0242] ,
        MAX([WT0243]) AS [WT0243] ,
        MAX([WT0244]) AS [WT0244] ,
        MAX([WT0245]) AS [WT0245] ,
        MAX([WT0246]) AS [WT0246] ,
        MAX([WT0247]) AS [WT0247] ,
        MAX([WT0248]) AS [WT0248] ,
        MAX([WT0249]) AS [WT0249] ,
        MAX([WT0250]) AS [WT0250] ,
        MAX([WT0301]) AS [WT0301] ,
        MAX([WT0302]) AS [WT0302] ,
        MAX([WT0303]) AS [WT0303] ,
        MAX([WT0304]) AS [WT0304] ,
        MAX([WT0305]) AS [WT0305] ,
        MAX([WT0306]) AS [WT0306] ,
        MAX([WT0307]) AS [WT0307] ,
        MAX([WT0308]) AS [WT0308] ,
        MAX([WT0309]) AS [WT0309] ,
        MAX([WT0310]) AS [WT0310] ,
        MAX([WT0311]) AS [WT0311] ,
        MAX([WT0312]) AS [WT0312] ,
        MAX([WT0313]) AS [WT0313] ,
        MAX([WT0314]) AS [WT0314] ,
        MAX([WT0315]) AS [WT0315] ,
        MAX([WT0316]) AS [WT0316] ,
        MAX([WT0317]) AS [WT0317] ,
        MAX([WT0318]) AS [WT0318] ,
        MAX([WT0319]) AS [WT0319] ,
        MAX([WT0320]) AS [WT0320] ,
        MAX([WT0321]) AS [WT0321] ,
        MAX([WT0322]) AS [WT0322] ,
        MAX([WT0323]) AS [WT0323] ,
        MAX([WT0324]) AS [WT0324] ,
        MAX([WT0325]) AS [WT0325] ,
        MAX([WT0326]) AS [WT0326] ,
        MAX([WT0327]) AS [WT0327] ,
        MAX([WT0328]) AS [WT0328] ,
        MAX([WT0329]) AS [WT0329] ,
        MAX([WT0330]) AS [WT0330] ,
        MAX([WT0331]) AS [WT0331] ,
        MAX([WT0332]) AS [WT0332] ,
        MAX([WT0333]) AS [WT0333] ,
        MAX([WT0334]) AS [WT0334] ,
        MAX([WT0335]) AS [WT0335] ,
        MAX([WT0336]) AS [WT0336] ,
        MAX([WT0337]) AS [WT0337] ,
        MAX([WT0338]) AS [WT0338] ,
        MAX([WT0339]) AS [WT0339] ,
        MAX([WT0340]) AS [WT0340] ,
        MAX([WT0341]) AS [WT0341] ,
        MAX([WT0342]) AS [WT0342] ,
        MAX([WT0343]) AS [WT0343] ,
        MAX([WT0344]) AS [WT0344] ,
        MAX([WT0345]) AS [WT0345] ,
        MAX([WT0346]) AS [WT0346] ,
        MAX([WT0347]) AS [WT0347] ,
        MAX([WT0348]) AS [WT0348] ,
        MAX([WT0349]) AS [WT0349] ,
        MAX([WT0350]) AS [WT0350] ,
        MAX([WT0401]) AS [WT0401] ,
        MAX([WT0402]) AS [WT0402] ,
        MAX([WT0403]) AS [WT0403] ,
        MAX([WT0404]) AS [WT0404] ,
        MAX([WT0405]) AS [WT0405] ,
        MAX([WT0406]) AS [WT0406] ,
        MAX([WT0407]) AS [WT0407] ,
        MAX([WT0408]) AS [WT0408] ,
        MAX([WT0409]) AS [WT0409] ,
        MAX([WT0410]) AS [WT0410] ,
        MAX([WT0411]) AS [WT0411] ,
        MAX([WT0412]) AS [WT0412] ,
        MAX([WT0413]) AS [WT0413] ,
        MAX([WT0414]) AS [WT0414] ,
        MAX([WT0415]) AS [WT0415] ,
        MAX([WT0416]) AS [WT0416] ,
        MAX([WT0417]) AS [WT0417] ,
        MAX([WT0418]) AS [WT0418] ,
        MAX([WT0419]) AS [WT0419] ,
        MAX([WT0420]) AS [WT0420] ,
        MAX([WT0421]) AS [WT0421] ,
        MAX([WT0422]) AS [WT0422] ,
        MAX([WT0423]) AS [WT0423] ,
        MAX([WT0424]) AS [WT0424] ,
        MAX([WT0425]) AS [WT0425] ,
        MAX([WT0426]) AS [WT0426] ,
        MAX([WT0427]) AS [WT0427] ,
        MAX([WT0428]) AS [WT0428] ,
        MAX([WT0429]) AS [WT0429] ,
        MAX([WT0430]) AS [WT0430] ,
        MAX([WT0431]) AS [WT0431] ,
        MAX([WT0432]) AS [WT0432] ,
        MAX([WT0433]) AS [WT0433] ,
        MAX([WT0434]) AS [WT0434] ,
        MAX([WT0435]) AS [WT0435] ,
        MAX([WT0436]) AS [WT0436] ,
        MAX([WT0437]) AS [WT0437] ,
        MAX([WT0438]) AS [WT0438] ,
        MAX([WT0439]) AS [WT0439] ,
        MAX([WT0440]) AS [WT0440] ,
        MAX([WT0441]) AS [WT0441] ,
        MAX([WT0442]) AS [WT0442] ,
        MAX([WT0443]) AS [WT0443] ,
        MAX([WT0444]) AS [WT0444] ,
        MAX([WT0445]) AS [WT0445] ,
        MAX([WT0446]) AS [WT0446] ,
        MAX([WT0447]) AS [WT0447] ,
        MAX([WT0448]) AS [WT0448] ,
        MAX([WT0449]) AS [WT0449] ,
        MAX([WT0450]) AS [WT0450]
FROM    ( SELECT    d.* ,
                    c.column_name ,
                    c.description ,
                    c.rep_id ,
                    c.row
          FROM      @hr_cust_py_report_desc AS c
                    LEFT JOIN ( SELECT  b.* ,
                                        a.col_no ,
                                        a.amount
                                FROM    @tbl_tr_dec AS a
                                        INNER JOIN @tbl_employee2 AS b ON a.employee_id = b.employee_id
                              ) AS d ON c.col_no = d.col_no
        ) AS SourceTable PIVOT
( MAX(amount) FOR column_name IN ( [WT0101], [WT0102], [WT0103], [WT0104],
                                   [WT0105], [WT0106], [WT0107], [WT0108],
                                   [WT0109], [WT0110], [WT0111], [WT0112],
                                   [WT0113], [WT0114], [WT0115], [WT0116],
                                   [WT0117], [WT0118], [WT0119], [WT0120],
                                   [WT0121], [WT0122], [WT0123], [WT0124],
                                   [WT0125], [WT0126], [WT0127], [WT0128],
                                   [WT0129], [WT0130], [WT0131], [WT0132],
                                   [WT0133], [WT0134], [WT0135], [WT0136],
                                   [WT0137], [WT0138], [WT0139], [WT0140],
                                   [WT0141], [WT0142], [WT0143], [WT0144],
                                   [WT0145], [WT0146], [WT0147], [WT0148],
                                   [WT0149], [WT0150], [WT0201], [WT0202],
                                   [WT0203], [WT0204], [WT0205], [WT0206],
                                   [WT0207], [WT0208], [WT0209], [WT0210],
                                   [WT0211], [WT0212], [WT0213], [WT0214],
                                   [WT0215], [WT0216], [WT0217], [WT0218],
                                   [WT0219], [WT0220], [WT0221], [WT0222],
                                   [WT0223], [WT0224], [WT0225], [WT0226],
                                   [WT0227], [WT0228], [WT0229], [WT0230],
                                   [WT0231], [WT0232], [WT0233], [WT0234],
                                   [WT0235], [WT0236], [WT0237], [WT0238],
                                   [WT0239], [WT0240], [WT0241], [WT0242],
                                   [WT0243], [WT0244], [WT0245], [WT0246],
                                   [WT0247], [WT0248], [WT0249], [WT0250],
                                   [WT0301], [WT0302], [WT0303], [WT0304],
                                   [WT0305], [WT0306], [WT0307], [WT0308],
                                   [WT0309], [WT0310], [WT0311], [WT0312],
                                   [WT0313], [WT0314], [WT0315], [WT0316],
                                   [WT0317], [WT0318], [WT0319], [WT0320],
                                   [WT0321], [WT0322], [WT0323], [WT0324],
                                   [WT0325], [WT0326], [WT0327], [WT0328],
                                   [WT0329], [WT0330], [WT0331], [WT0332],
                                   [WT0333], [WT0334], [WT0335], [WT0336],
                                   [WT0337], [WT0338], [WT0339], [WT0340],
                                   [WT0341], [WT0342], [WT0343], [WT0344],
                                   [WT0345], [WT0346], [WT0347], [WT0348],
                                   [WT0349], [WT0350], [WT0401], [WT0402],
                                   [WT0403], [WT0404], [WT0405], [WT0406],
                                   [WT0407], [WT0408], [WT0409], [WT0410],
                                   [WT0411], [WT0412], [WT0413], [WT0414],
                                   [WT0415], [WT0416], [WT0417], [WT0418],
                                   [WT0419], [WT0420], [WT0421], [WT0422],
                                   [WT0423], [WT0424], [WT0425], [WT0426],
                                   [WT0427], [WT0428], [WT0429], [WT0430],
                                   [WT0431], [WT0432], [WT0433], [WT0434],
                                   [WT0435], [WT0436], [WT0437], [WT0438],
                                   [WT0439], [WT0440], [WT0441], [WT0442],
                                   [WT0443], [WT0444], [WT0445], [WT0446],
                                   [WT0447], [WT0448], [WT0449], [WT0450] ) )AS PivotTable
GROUP BY employee_id ,
        employee_name ,
        employee_area_id ,
        employee_area_descr ,
        employee_office_id ,
        employee_office_descr ,
        employee_status_id ,
        employee_status_descr ,
        employee_type_id ,
        employee_type_descr ,
        employee_sub_type_id ,
        employee_sub_type_descr ,
        payroll_group_id ,
        payroll_group_descr ,
        organization_id ,
        organization_descr ,
        CostCenter ,
        cost_center_abbr ,
        cost_center_descr ,
        cost_center_group_id ,
        cost_center_group_abbr ,
        cost_center_group_descr ,
        position_id ,
        position_descr ,
        job_id ,
        job_descr ,
        pay_type_id ,
        pay_type_descr ,
        pay_class_id ,
        pay_class_descr ,
        pay_grade_id ,
        pay_grade_descr ,
        pay_area_id ,
        pay_area_descr
HAVING  employee_id IS NOT NULL