PRPTPYWTRECAPDETAIL_202508131131.sql - PT BIA - Redmine

Bug #3783 » PRPTPYWTRECAPDETAIL_202508131131.sql

Tri Rizqiaty, 08/13/2025 11:32 AM

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

« Previous
1
2
3
4
Next »

(4-4/4)

Project

General

Profile

PT BIA

Bug #3783 » PRPTPYWTRECAPDETAIL_202508131131.sql