Rpt_IMFI_HR_PY_WageTypeRecapV2_20240322.sql - IMFI Bugs - Redmine

Bug #2664 » Rpt_IMFI_HR_PY_WageTypeRecapV2_20240322.sql

Tri Rizqiaty, 03/22/2024 02:01 PM

    
      ??ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PY_WageTypeRecapV2]

      	 @landscape varchar(3),

      	 @payrollGroupStart varchar(2),

      	 @payrollGroupEnd varchar(2),

      	 @CostCenterStart varchar(8),

      	 @CostCenterEnd varchar(8),

      	 @employee_idStart varchar(8),

      	 @employee_idEnd varchar(8),

      	 @payPeriod varchar(6)

      AS

       --declare @landscape varchar(3) = N'100'

       --declare @payPeriod varchar(6) = '202403'

       --declare @employee_idStart varchar(8) = '20000003'

       --declare @employee_idEnd varchar(8)='20000003'

       --declare @payrollGroupStart varchar(2) =''

       --declare @payrollGroupEnd varchar(2)=''

       --declare @CostCenterStart varchar(8)

       --declare @CostCenterEnd varchar(8)

       --DECLARE @emp_type VARCHAR(12) = ''

      --//-- Variable Declaration

      DECLARE @payPeriodMonth varchar(2)

      DECLARE @payPeriodYear varchar(4)

      DECLARE @payPeriodStartDate varchar(8)

      DECLARE @payPeriodEndDate varchar(8)

      DECLARE @payPeriodEndDateDt datetime

      DECLARE @employee_idMin varchar(8)

      DECLARE @employee_idMax varchar(8)

      DECLARE @PayrollGroupMin varchar(2)

      DECLARE @PayrollGroupMax varchar(2)

      DECLARE @CostCenterMin varchar(8)

      DECLARE @CostCenterMax varchar(8)

      --//-- Table Declaration

      DECLARE @tbl_result TABLE

      	(employee_id varchar(8),

      	 employee_name varchar(50),

      	 CostCenterGroup varchar(50),

      	 CostCenterGroup_Abr varchar(50),

      	 CostCenterGroup_Desc varchar(50),

      	 CostCenter varchar(50),

      	 CostCenter_Abr varchar(50),

      	 CostCenter_Desc varchar(50),

      	 WT0101 int,WT0102 int,WT0103 int,WT0104 int,WT0105 int,WT0106 int,WT0107 int,WT0108 int,WT0109 int,WT0110 int,WT0111 int,WT0112 int,WT0113 int,WT0114 int,WT0115 int,WT0116 int,WT0117 int,WT0118 int,WT0119 int,WT0120 int,WT0121 int,WT0122 int,WT0123 int,WT0124 int,WT0125 int,WT0126 int,WT0127 int,WT0128 int,WT0129 int,WT0130 int,WT0131 int,WT0132 int,WT0133 int,WT0134 int,WT0135 int,WT0136 int,WT0137 int,WT0138 int,WT0139 int,WT0140 int,WT0141 int,WT0142 int,WT0143 int,WT0144 int,WT0145 int,WT0146 int,WT0147 int,WT0148 int,WT0149 int,WT0150 int,

      	 WT0201 int,WT0202 int,WT0203 int,WT0204 int,WT0205 int,WT0206 int,WT0207 int,WT0208 int,WT0209 int,WT0210 int,WT0211 int,WT0212 int,WT0213 int,WT0214 int,WT0215 int,WT0216 int,WT0217 int,WT0218 int,WT0219 int,WT0220 int,WT0221 int,WT0222 int,WT0223 int,WT0224 int,WT0225 int,WT0226 int,WT0227 int,WT0228 int,WT0229 int,WT0230 int,WT0231 int,WT0232 int,WT0233 int,WT0234 int,WT0235 int,WT0236 int,WT0237 int,WT0238 int,WT0239 int,WT0240 int,WT0241 int,WT0242 int,WT0243 int,WT0244 int,WT0245 int,WT0246 int,WT0247 int,WT0248 int,WT0249 int,WT0250 int,

      	 WT0301 int,WT0302 int,WT0303 int,WT0304 int,WT0305 int,WT0306 int,WT0307 int,WT0308 int,WT0309 int,WT0310 int,WT0311 int,WT0312 int,WT0313 int,WT0314 int,WT0315 int,WT0316 int,WT0317 int,WT0318 int,WT0319 int,WT0320 int,WT0321 int,WT0322 int,WT0323 int,WT0324 int,WT0325 int,WT0326 int,WT0327 int,WT0328 int,WT0329 int,WT0330 int,WT0331 int,WT0332 int,WT0333 int,WT0334 int,WT0335 int,WT0336 int,WT0337 int,WT0338 int,WT0339 int,WT0340 int,WT0341 int,WT0342 int,WT0343 int,WT0344 int,WT0345 int,WT0346 int,WT0347 int,WT0348 int,WT0349 int,WT0350 int,

      	 WT0401 int,WT0402 int,WT0403 int,WT0404 int,WT0405 int,WT0406 int,WT0407 int,WT0408 int,WT0409 int,WT0410 int,WT0411 int,WT0412 int,WT0413 int,WT0414 int,WT0415 int,WT0416 int,WT0417 int,WT0418 int,WT0419 int,WT0420 int,WT0421 int,WT0422 int,WT0423 int,WT0424 int,WT0425 int,WT0426 int,WT0427 int,WT0428 int,WT0429 int,WT0430 int,WT0431 int,WT0432 int,WT0433 int,WT0434 int,WT0435 int,WT0436 int,WT0437 int,WT0438 int,WT0439 int,WT0440 int,WT0441 int,WT0442 int,WT0443 int,WT0444 int,WT0445 int,WT0446 int,WT0447 int,WT0448 int,WT0449 int,WT0450 int)

      DECLARE @tbl_employee TABLE

      	(employee_id varchar(8),

      	 employee_name varchar(250),

      	 CCG varchar(50),

      	 CCG_Abr varchar(50),

      	 CCG_Desc varchar(250),

      	 CC varchar(50),

      	 CC_Abr varchar(50),

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

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

      		resign_date VARCHAR(8),

      		hiring_date VARCHAR(8)

      	 )

      DECLARE @tbl_tr_enc TABLE

      	(employee_id varchar(8),

      	 payPeriodMonth varchar(2),

      	 payPeriodYear varchar(4),

      	 runPeriodMonth varchar(2),

      	 runPeriodYear varchar(4),

      	 wageType varchar(4),

      	 amount varchar(250))

      --DECLARE @tbl_tr_dec TABLE

      --	(employee_id varchar(8),

      --	 payPeriodMonth varchar(2),

      --	 payPeriodYear varchar(4),

      --	 runPeriodMonth varchar(2),

      --	 runPeriodYear varchar(4),

      --	 wageType varchar(4),

      --	 amount int,

      --	 column_name varchar(6))

      DECLARE @hrtr0300 TABLE(

      	[landscape] [VARCHAR](3) NOT NULL,

      	[employee_id] [VARCHAR](8) NOT NULL,

      	[pay_period_month] [VARCHAR](2) NOT NULL,

      	[pay_period_year] [VARCHAR](4) NOT NULL,

      	[run_period_month] [VARCHAR](2) NOT NULL,

      	[run_period_year] [VARCHAR](4) NOT NULL,

      	[split_indicator] [VARCHAR](1) NOT NULL,

      	[start_date] [VARCHAR](8) NOT NULL,

      	[end_date] [VARCHAR](8) NOT NULL,

      	[data_status] [VARCHAR](1) NULL,

      	[company_id] [VARCHAR](4) NULL,

      	[cost_center] [VARCHAR](8) NULL,

      	[employee_area] [VARCHAR](4) NOT NULL,

      	[employee_office] [VARCHAR](4) NOT NULL,

      	[employee_status] [VARCHAR](2) NOT NULL,

      	[employee_type] [VARCHAR](2) NOT NULL,

      	[employee_subtype] [VARCHAR](2) NOT NULL,

      	[payroll_group] [VARCHAR](2) NOT NULL,

      	[organization] [VARCHAR](8) NOT NULL,

      	[position] [VARCHAR](8) NOT NULL,

      	[job] [VARCHAR](8) NOT NULL,

      	[pay_type] [VARCHAR](2) NULL,

      	[pay_class] [VARCHAR](2) NULL,

      	[pay_grade] [VARCHAR](2) NULL,

      	[pay_area] [VARCHAR](2) NULL,

      	[pay_curr] [VARCHAR](5) NULL,

      	[pay_curr_rate] [DECIMAL](18, 7) NULL,

      	[change_by] [VARCHAR](12) NOT NULL,

      	[change_date] [VARCHAR](14) NOT NULL,

      	[created_by] [VARCHAR](12) NOT NULL,

      	[created_date] [VARCHAR](14) NOT NULL )

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

      INSERT INTO @hrtr0300

      SELECT DISTINCT * FROM hr_tr0300 WITH (NOLOCK) WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      --//-- Set Filter

      SELECT @employee_idMin = MIN(employee_id) FROM @hrtr0300 WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      SELECT @employee_idMax = MAX(employee_id) FROM @hrtr0300  WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      SELECT @CostCenterMin = MIN(cost_center) FROM @hrtr0300  WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      SELECT @CostCenterMax = MAX(cost_center) FROM @hrtr0300 WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      SELECT @PayrollGroupMin = MIN(payroll_group) FROM @hrtr0300  WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      SELECT @PayrollGroupMax = MAX(payroll_group) FROM @hrtr0300 WHERE landscape = @landscape AND pay_period_year = run_period_year AND pay_period_month = run_period_month AND run_period_year = @payPeriodYear AND run_period_month = @payPeriodMonth

      IF (LTRIM(RTRIM(@employee_idStart)) <> '')

      BEGIN

      	SET @employee_idMin = @employee_idStart

      	IF (LTRIM(RTRIM(@employee_idEnd)) <> '')

      		SET @employee_idMax = @employee_idEnd

      	ELSE

      		SET @employee_idMax = @employee_idStart

      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

      ----//-- Select Employee

      INSERT INTO @tbl_employee

      SELECT DISTINCT md01.emp_id, 

      md01.full_name,

      '' CG, --cg.object, 

      '' CG_Abbr, --cg.abbreviation, 

      '' CG_Desc, --cg.description, 

      cc.abbreviation, 

      cc.abbreviation, 

      cc.description

      --,md2.start_date, md2.end_date, md2.movement_type

      FROM hr_md_emp_md0001 md01 WITH (NOLOCK) 

      	LEFT JOIN @hrtr0300 AS hr_tr0300

      		ON md01.landscape = hr_tr0300.landscape

      	LEFT JOIN hr_md_orm_object cc  WITH (NOLOCK)

      		ON cc.landscape = @landscape

      		AND cc.class = 'CC'

      		AND cc.start_date <= @payPeriodEndDate

      		AND cc.end_date >= @payPeriodEndDate

      	--LEFT JOIN hr_md_orm_relationship ccg  WITH (NOLOCK)

      	--	ON cc.object = ccg.object

      	--	AND ccg.landscape = @landscape

      	--	AND ccg.class = 'CC'

      	--	AND ccg.start_date <= @payPeriodEndDate

      	--	AND ccg.end_date >= @payPeriodEndDate			

      	--	AND ccg.rel_class = 'CG'

      	--	AND ccg.rel_dir = 'B'

      	--	AND ccg.rel_type = '001'

      	--LEFT JOIN hr_md_orm_object cg  WITH (NOLOCK)

      	--	ON ccg.rel_object = cg.object

      	--	AND cg.landscape = @landscape

      	--	AND cg.class = 'CG'

      	--	AND cg.start_date <= @payPeriodEndDate

      	--	AND cg.end_date >= @payPeriodEndDate

      WHERE (hr_tr0300.cost_center >= @CostCenterMin AND hr_tr0300.cost_center <= @CostCenterMax) 

      	AND ( hr_tr0300.payroll_group >= @PayrollGroupMin AND hr_tr0300.payroll_group <= @PayrollGroupMax)

      	AND md01.start_date <= @payPeriodEndDate

      	AND md01.end_date >= @payPeriodEndDate 

      	AND md01.emp_id >= @employee_idMin

      	AND md01.emp_id <= @employee_idMax

      	AND md01.emp_id = hr_tr0300.employee_id

      	AND hr_tr0300.cost_center = cc.object

      	--AND ( dbo.hr_tr0300.employee_type = @emp_type OR @emp_type = '')

      DECLARE @emphiringresign TABLE

      (

      	landscape VARCHAR(5)

      	,emp_id VARCHAR(10)

      	,sdate VARCHAR(20)

      	,edate VARCHAR(20)

      	,movtype VARCHAR(20)

      )

      INSERT INTO @emphiringresign  

      SELECT DISTINCT md2a.landscape, md2a.emp_id, md2a.start_date, md2a.end_date, md2a.movement_type

      FROM dbo.hr_md_emp_md0002 AS md2a

      WHERE md2a.landscape = @landscape AND md2a.start_date <= @payPeriodEndDate AND md2a.end_date >= @payPeriodEndDate

      AND md2a.emp_id >= @employee_idMin AND md2a.emp_id <= @employee_idMax AND md2a.movement_type = '80'

      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(

      [landscape] [varchar](3) NOT NULL,

      [employee_id] [varchar](8) NOT NULL,

      [pay_period_month] [varchar](2) NOT NULL,

      [pay_period_year] [varchar](4) NOT NULL,

      [run_period_month] [varchar](2) NOT NULL,

      [run_period_year] [varchar](4) NOT NULL,

      [split_indicator] [varchar](1) NOT NULL,

      [start_date] [varchar](8) NOT NULL,

      [end_date] [varchar](8) NOT NULL,

      [data_status] [varchar](1) NULL,

      [company_id] [varchar](4) NULL,

      [cost_center] [varchar](8) NULL,

      [employee_area] [varchar](4) NOT NULL,

      [employee_office] [varchar](4) NOT NULL,

      [employee_status] [varchar](2) NOT NULL,

      [employee_type] [varchar](2) NOT NULL,

      [employee_subtype] [varchar](2) NOT NULL,

      [payroll_group] [varchar](2) NOT NULL,

      [organization] [varchar](8) NOT NULL,

      [position] [varchar](8) NOT NULL,

      [job] [varchar](8) NOT NULL,

      [pay_type] [varchar](2) NULL,

      [pay_class] [varchar](2) NULL,

      [pay_grade] [varchar](2) NULL,

      [pay_area] [varchar](2) NULL,

      pay_curr  VARCHAR(5),

      pay_curr_rate DECIMAL(18,7),

      [change_by] [varchar](12) NOT NULL,

      [change_date] [varchar](14) NOT NULL,

      [created_by] [varchar](12) NOT NULL,

      [created_date] [varchar](14) NOT NULL

      )

      declare @emp_id varchar (8) 

      declare @pay_month varchar (2) 

      set @pay_month = substring(@payPeriod,5,2)

      declare @pay_year varchar (4) 

      set @pay_year = 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    @is_encrypted = val1

      FROM         dbo.base_cust_parameter

      where	landscape = @landscape and

      		param = 'HR_PY_ENCRYPTED' and

      		start_date <= @payPeriodEndDate and 

      		end_date >= @payPeriodEndDate

      DECLARE cur_employee CURSOR FOR SELECT DISTINCT * 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 @hrtr0300

      where	employee_id = @emp_id

      	AND pay_period_month = @pay_month

      	AND pay_period_year = @pay_year order by run_period_month desc

      select @start_date = tr0300.start_date , @end_date = tr0300.end_date

      from @tr0300 as tr0300

      select @pay_period_month = pay_period_month, @pay_period_year = pay_period_year

      from @tr0300 

      select @employe_id = md1.emp_id, @full_name = md1.full_name

      from hr_md_emp_md0001 as md1

      inner join @tr0300 as tr0300

      	on md1.emp_id = tr0300.employee_id

      	and md1.start_date <= @end_date

      	and md1.end_date >= @end_date

      select @cost_center = object.description

      from @tr0300 as tr0300

      left join hr_md_orm_object as object

      	on object.object = tr0300.cost_center

      	and object.start_date <= @start_date

      	and object.end_date >= @end_date

      	and object.class = 'CC'

      select @employee_area = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_emp_area  as object

      	on object.emp_area = tr0300.employee_area

      select @employee_office = object.emp_subarea_description

      from @tr0300 as tr0300

      left join base_cust_ref_emp_office  as object

      	on object.emp_subarea = tr0300.employee_office

      select @employee_status = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_emp_status  as object

      	on object.emp_status = tr0300.employee_status

      select @employee_type = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_emp_type  as object

      	on object.emp_type = tr0300.employee_type

      select @employee_subtype = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_emp_subtype  as object

      	on object.emp_subtype = tr0300.employee_subtype

      select @payroll_group = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_payroll_group  as object

      	on object.payroll_group = tr0300.payroll_group

      select @organization = object.description

      from @tr0300 as tr0300

      left join hr_md_orm_object as object

      	on object.object = tr0300.organization

      	and object.start_date <= @end_date

      	and object.end_date >= @end_date

      	and object.class = 'O'

      select @position = object.description

      from @tr0300 as tr0300

      left join hr_md_orm_object as object

      	on object.object = tr0300.position

      	and object.start_date <= @end_date

      	and object.end_date >= @end_date

      	and object.class = 'P'

      select @job = object.description

      from @tr0300 as tr0300

      left join hr_md_orm_object as object

      	on object.object = tr0300.job

      	and object.start_date <= @end_date

      	and object.end_date >= @end_date

      	and object.class = 'J'

      select @pay_type = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_pay_type  as object

      	on object.pay_type = tr0300.pay_type

      select @pay_class = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_pay_class  as object

      	on object.pay_class = tr0300.pay_class

      select @pay_grade = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_pay_grade  as object

      	on object.pay_grade = tr0300.pay_grade

      select @pay_area = object.description

      from @tr0300 as tr0300

      left join base_cust_ref_pay_area  as object

      	on object.pay_area = tr0300.pay_area

      INSERT INTO @tbl_employee2

      SELECT DISTINCT

      tr0300.employee_id	,

      employee_name	,

      tr0300.employee_area as employee_area_id	,

      a.employee_area_descr	,

      tr0300.employee_office as employee_office_id	,

      a.employee_office_descr	,

      tr0300.employee_status as employee_status_id	,

      a.employee_status_descr	,

      tr0300.employee_type as employee_type_id	,

      a.employee_type_descr	,

      tr0300.employee_subtype as employee_subtype_id	,

      a.employee_subtype_descr	,

      tr0300.payroll_group 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.pay_type	as pay_type_id,

      a.pay_type_descr	,

      tr0300.pay_class as pay_class_id	,

      a.pay_class_descr	,

      tr0300.pay_grade as pay_grade_id	,

      a.pay_grade_descr	,

      tr0300.pay_area as pay_area_id	,

      a.pay_area_descr

      , c.sdate AS resign_date, d.sdate AS hiring_date	

       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.employee_id

      	 LEFT JOIN @emphiringresign AS c

      	 ON a.employee_id = c.emp_id AND c.sdate <= @payPeriodEndDate AND c.edate >= @payPeriodEndDate AND c.movtype = '80'

      	 LEFT JOIN @emphiringresign AS d

      	 ON a.employee_id = d.emp_id AND c.movtype = '10'

      		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 (

      	[landscape] [varchar](4) NOT NULL,

      	[rep_id] [varchar](50) NOT NULL,

      	[row] [int] NOT NULL,

      	[col_no] [int] NOT NULL,

      	[seq_no] [int] NOT NULL,

      	[wage_type] [varchar](5) NULL,

      	[descr] [varchar](50) NULL

      )

      insert into @hr_cust_py_report

      SELECT DISTINCT [landscape]

            ,[rep_id]

            ,[row]

            ,[col_no]

            ,[seq_no]

            ,[wage_type]

            ,[descr]

        FROM [dbo].[hr_cust_py_report] WITH (NOLOCK) WHERE start_date <= @payPeriodEndDate

        AND end_date >= @payPeriodEndDate and rep_id = 'WT01R'

           declare @hr_cust_py_report_col_name TABLE (

      	[landscape] [varchar](4)  NULL,

      	[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 landscape, rep_id, row, col_no) + 49) / 50) ,2) 

      + right('00' + convert(varchar,((ROW_NUMBER () OVER (ORDER BY landscape, rep_id, row, col_no) -1) % 50)+1) ,2) as column_name

       from(

        select distinct landscape, rep_id, row, col_no from @hr_cust_py_report )a ORDER BY landscape, rep_id, row, col_no

        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 @landscape,'',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 (

      	[landscape] [varchar](4) NOT NULL,

      	[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 DISTINCT a.*, b.descr from @hr_cust_py_report_col_name as a

      	left join(

      	select MAX(landscape) as landscape, MAX(rep_id) as rep_id, MAX(row) as row, MAX(col_no) as col_no,MAX(descr) as descr

      	 from @hr_cust_py_report group by landscape, rep_id, row,col_no)as b

      	 on a.landscape =b.landscape and 

      	 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.employee_id, pr.pay_period_month, pr.pay_period_year, 

      	pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount 

      	--CASE WHEN (rep.[column] < 10)

      	--THEN 'WT0' + CONVERT(varchar(1), rep.row) + '0' + CONVERT(varchar(1), rep.[column])

      	--ELSE 'WT0' + CONVERT(varchar(1), rep.row) + CONVERT(varchar(2), rep.[column]) END

      FROM hr_tr0301 pr  WITH (NOLOCK)

      	INNER JOIN @tbl_employee emp

      		ON pr.employee_id = emp.employee_id

      	INNER JOIN hr_cust_py_report rep WITH (NOLOCK)

      		ON pr.landscape = rep.landscape

      WHERE pr.run_period_year = @payPeriodYear

      	AND pr.run_period_month = @payPeriodMonth

      	AND pr.pay_period_year = @payPeriodYear

      	AND pr.pay_period_month = @payPeriodMonth

      	AND pr.wage_type = rep.wage_type

      	--AND pr.employee_id in (select employee_id from @tbl_employee)

      	--and pr.wage_type in (select wage_type from @hr_cust_py_report)

      INSERT INTO @tbl_tr_enc

      SELECT DISTINCT pr.employee_id, pr.pay_period_month, pr.pay_period_year, 

      	pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount 

      	--CASE WHEN (rep.[column] < 10)

      	--THEN 'WT0' + CONVERT(varchar(1), rep.row) + '0' + CONVERT(varchar(1), rep.[column])

      	--ELSE 'WT0' + CONVERT(varchar(1), rep.row) + CONVERT(varchar(2), rep.[column]) END

      FROM hr_tr0301_retro pr  WITH (NOLOCK)

      	INNER JOIN @tbl_employee emp

      		ON pr.employee_id = emp.employee_id

      	INNER JOIN hr_cust_py_report rep WITH (NOLOCK) 

      		ON pr.landscape = rep.landscape

      WHERE pr.run_period_year = @payPeriodYear

      	AND pr.run_period_month = @payPeriodMonth

      	AND pr.pay_period_year = @payPeriodYear

      	AND pr.pay_period_month = @payPeriodMonth

      	AND pr.wage_type = rep.wage_type

      	AND (rep.wage_type NOT IN ('MT','MIT','MRT','5000','5001'))

      	--AND pr.employee_id in (select employee_id from @tbl_employee)

      	--and pr.wage_type in (select wage_type from @hr_cust_py_report)

      DECLARE @tbl_tr_dec TABLE

      	(col_no int,

      	 employee_id varchar(8),

      	 amount decimal(20,2))

      insert into @tbl_tr_dec

      select col_no, employee_id, SUM(amount) as amount from(

      select b.col_no,  c.employee_id, 

      CASE @is_encrypted when '0' then convert(decimal(18, 2),amount) else convert(decimal(18, 2),dbo.SDE(amount, N'M!N0V@2010')) end as amount

        from @hr_cust_py_report_desc as b

      inner  join @hr_cust_py_report as a on 

      a.landscape = b.landscape and 

      a.col_no = b.col_no and

      a.rep_id = b.rep_id and

      a.row = b.row 

      inner join @tbl_tr_enc c on 

      a.wage_type = 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	,

      		cost_center_id	,

      		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,

      		dbo.fn_formatdatetime(hiring_date, 'dd/mm/yyyy') AS hiring_date,

      		dbo.fn_formatdatetime(resign_date, 'dd/mm/yyyy') AS resign_date,

      		1 AS jum_emp,

      		--dbo.fn_formatdatetime((DATEADD(dd, -1, DATEADD(mm, 0, CONVERT(datetime, resign_date)))), 'yyyymmdd') resign_date,

      	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 DISTINCT d.*, c.column_name, c.description, c.landscape, c.rep_id, c.row from @hr_cust_py_report_desc as c left join

      (

      select DISTINCT b.*, a.col_no,a.amount--, c.start_date AS resign_date--, d.start_date AS hiring_date

      from @tbl_tr_dec as a 

      inner join @tbl_employee2 as b on a.employee_id = b.employee_id

      --left join hr_md_emp_md0002 as c 

      --	on  c.emp_id = b.employee_id 

      --	and c.start_date <= @payPeriodEndDate and c.end_date >= @payPeriodEndDate 

      --	and c.movement_type = '80'

      --left join hr_md_emp_md0002 as d 

      --	on  d.emp_id = b.employee_id 

      	--and d.start_date <= @payPeriodEndDate and d.end_date >= @payPeriodEndDate 

      	--and d.movement_type = '10'

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

      		cost_center_id	,

      		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,

      		hiring_date,

      		resign_date	

      	having employee_id is not null

      --inner join

      -- @hr_cust_py_report_desc as b on a.landscape = b.landscape and

      -- a.rep_id =b.rep_id 

      --  select @companyname as companyname, @logo as logo, @payPeriodStr as payPeriodStr, 'Employee ID' as employee_id , 'Employee Name' as employee_name, 'Cost Center Group' as CostCenterGroup, 'Cost Center Group_Abr' as CostCenterGroup_Abr, 	 'Cost Center Group_Desc' as CostCenterGroup_Desc, 	 'Cost Center' as CostCenter, 	 'Cost Center_Abr' as CostCenter_Abr, 	 'Cost Center_Desc' as CostCenter_Desc,  * from (select column_name, description from @hr_cust_py_report_desc)as SourceTable

      --PIVOT

      --(

      --  max(description)

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

« Previous
1
…
5
6
7
Next »

(7-7/7)

Project

General

Profile

CLIENT SUPPORT » IMFI Bugs

Bug #2664 » Rpt_IMFI_HR_PY_WageTypeRecapV2_20240322.sql