ALTER PROCEDURE [dbo].[Rpt_Mega_WTRecap_Summary_New]
(
@landscape VARCHAR(10)
,@payperiod VARCHAR(10)
,@companycode VARCHAR(10)
,@paygroup VARCHAR(10) 
,@emp_id VARCHAR(10)
)
AS


--DECLARE @landscape VARCHAR(10) = '100'
--DECLARE @companycode VARCHAR(10) = ''
--DECLARE @paygroup VARCHAR(10) = '01'
--DECLARE @payperiod VARCHAR(10) = '202405'
--DECLARE @emp_id VARCHAR(10) = ''


--//-- Variable Declaration
DECLARE @payPeriodMonth varchar(2)
DECLARE @payPeriodYear varchar(4)
DECLARE @payPeriodStartDate varchar(8)
DECLARE @payPeriodEndDate varchar(8)
DECLARE @payPeriodEndDateDt datetime

--//-- 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(50),
	 CCG varchar(50),
	 CCG_Abr varchar(50),
	 CCG_Desc varchar(50),
	 CC varchar(50),
	 CC_Abr varchar(50),
	 CC_Desc varchar(50),
	 org_group VARCHAR(50),
	 org_group_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)
	 )

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

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

--//-- Select Employee
INSERT INTO @tbl_employee
	SELECT md01.emp_id, md01.full_name, '' AS cg , '' AS cgabbr, '' AS cgdesc, '' AS ccabbr, '', '' AS ccdesc
		,ISNULL(orggroup.object, 'None') AS org_group
		,case when oo.location like'____' then locs.emp_subarea_description when oo.location='KPNO' then 'KPNO' else oo.location end as org_grooup_desc
	FROM hr_md_emp_md0001 AS md01
	LEFT JOIN hr_tr0300_new AS hr_tr0300 WITH (NOLOCK)
		ON md01.emp_id = hr_tr0300.employee_id
	LEFT JOIN hr_md_emp_md0002 md02  WITH (NOLOCK)
		ON  md02.landscape = @landscape
		AND md02.start_date <= @payPeriodEndDate
		AND md02.end_date >= @payPeriodEndDate 
		AND md02.emp_id = md01.emp_id
	LEFT JOIN dbo.hr_md_orm_object AS org
		ON org.landscape = md02.landscape
		AND org.object = md02.organization
		AND org.class = 'O'
		AND org.start_date <= @payPeriodEndDate
		AND org.end_date >= @payPeriodEndDate
	LEFT JOIN hr_md_orm_object orggroup 
		ON hr_tr0300.organization = orggroup.object  
		AND orggroup.class='O'
		AND orggroup.end_date='99991231'
	LEFT JOIN hr_md_orm_o_o oo 
		ON  orggroup.object= oo.object 
		AND oo.end_date='99991231'
	LEFT JOIN base_cust_ref_emp_office AS locs 
		ON oo.location=locs.emp_subarea
WHERE 	hr_tr0300.pay_period_month = @payPeriodMonth
		AND ( hr_tr0300.pay_period_year = @payPeriodYear )
		AND ( hr_tr0300.company_id = @companycode OR @companycode = '' )
        AND ( hr_tr0300.payroll_group = @paygroup OR @paygroup = '' )
        AND ( hr_tr0300.employee_id = @emp_id OR @emp_id = '' )
		AND md01.start_date <= @payPeriodEndDate
		AND md01.end_date >= @payPeriodEndDate
  
  
  
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 @org_group VARCHAR(50)
DECLARE @org_group_desc VARCHAR(250)
	 
  
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) = 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    @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 * FROM @tbl_employee

OPEN cur_employee
FETCH cur_employee INTO 
				   @employee_id, 
				   @employee_name, 
				   @CCG, 
				   @CCG_Abr, 
				   @CCG_Desc, 
				   @CC, 
				   @CC_Abr, 
				   @CC_Desc 
				  ,@org_group
				  ,@org_group_desc
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) [landscape]
      ,[employee_id]
      ,[pay_period_month]
      ,[pay_period_year]
      ,[run_period_month]
      ,[run_period_year]
      ,[split_indicator]
      ,[start_date]
      ,[end_date]
      ,[data_status]
      ,[company_id]
      ,[cost_center]
      ,[employee_area]
      ,[employee_office]
      ,[employee_status]
      ,[employee_type]
      ,[employee_subtype]
      ,[payroll_group]
      ,[organization]
      ,[position]
      ,[job]
      ,[pay_type]
      ,[pay_class]
      ,[pay_grade]
      ,[pay_area]
      ,[pay_curr]
      ,[pay_curr_rate]
      ,[change_by]
      ,[change_date]
      ,[created_by]
      ,[created_date]
	FROM hr_tr0300_new
	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
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	
 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
	 
  
		FETCH cur_employee INTO 
						   @employee_id, 
						   @employee_name, 
						   @CCG, 
						   @CCG_Abr, 
						   @CCG_Desc, 
						   @CC, 
						   @CC_Abr, 
						   @CC_Desc
						   ,@org_group
						   ,@org_group_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 [landscape]
      ,[rep_id]
      ,[row]
      ,[col_no]
      ,[seq_no]
      ,[wage_type]
      ,[descr]
  FROM [dbo].[hr_cust_py_report] 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 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 pr.employee_id, pr.pay_period_month, pr.pay_period_year, 
	pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount 
FROM hr_tr0301_new pr  WITH (NOLOCK)
INNER JOIN @tbl_employee emp
	ON pr.employee_id = emp.employee_id
INNER JOIN hr_cust_py_report rep
	ON pr.wage_type = rep.wage_type
WHERE pr.run_period_year = @payPeriodYear
	AND pr.run_period_month = @payPeriodMonth
	AND pr.run_period_month = pr.pay_period_month
	AND pr.run_period_year = pr.pay_period_year

INSERT INTO @tbl_tr_enc
SELECT pr.employee_id, pr.pay_period_month, pr.pay_period_year, 
	pr.run_period_month, pr.run_period_year, pr.wage_type, pr.amount 
FROM hr_tr0301_retro_new pr  WITH (NOLOCK)
INNER JOIN @tbl_employee emp
	ON pr.employee_id = emp.employee_id
INNER JOIN hr_cust_py_report rep
	ON pr.wage_type = rep.wage_type
WHERE pr.run_period_year = @payPeriodYear
	AND pr.run_period_month = @payPeriodMonth
	AND pr.run_period_month = pr.pay_period_month
	AND pr.run_period_year = pr.pay_period_year
	
DECLARE @tbl_tr_dec TABLE
	(col_no int,
	 employee_id varchar(8),
	 amount DECIMAL(22,0),
	 period_id VARCHAR(20),
	 period_desc VARCHAR(50))
	 
insert into @tbl_tr_dec
select DISTINCT col_no, employee_id, SUM(amount) as amount, period_id, period_desc from(
select DISTINCT 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
,(c.payPeriodMonth + c.payPeriodYear) AS period_id
,dbo.fn_formatdatetime_indonesia((c.payPeriodYear + c.payPeriodMonth + '01'), 'mmmm yyyy') AS period_desc
  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, period_id, period_desc


SELECT 
SUM([WT0101]) as [WT0101],
SUM([WT0102]) as [WT0102],
SUM([WT0103]) as [WT0103],
SUM([WT0104]) as [WT0104],
SUM([WT0105]) as [WT0105],
SUM([WT0106]) as [WT0106],
SUM([WT0107]) as [WT0107],
SUM([WT0108]) as [WT0108],
SUM([WT0109]) as [WT0109],
SUM([WT0110]) as [WT0110],
SUM([WT0111]) as [WT0111],
SUM([WT0112]) as [WT0112],
SUM([WT0113]) as [WT0113],
SUM([WT0114]) as [WT0114],
SUM([WT0115]) as [WT0115],
SUM([WT0116]) as [WT0116],
SUM([WT0117]) as [WT0117],
SUM([WT0118]) as [WT0118],
SUM([WT0119]) as [WT0119],
SUM([WT0120]) as [WT0120],
SUM([WT0121]) as [WT0121],
SUM([WT0122]) as [WT0122],
SUM([WT0123]) as [WT0123],
SUM([WT0124]) as [WT0124],
SUM([WT0125]) as [WT0125],
SUM([WT0126]) as [WT0126],
SUM([WT0127]) as [WT0127],
SUM([WT0128]) as [WT0128],
SUM([WT0129]) as [WT0129],
SUM([WT0130]) as [WT0130],
SUM([WT0131]) as [WT0131],
SUM([WT0132]) as [WT0132],
SUM([WT0133]) as [WT0133],
SUM([WT0134]) as [WT0134],
SUM([WT0135]) as [WT0135],
SUM([WT0136]) as [WT0136],
SUM([WT0137]) as [WT0137],
SUM([WT0138]) as [WT0138],
SUM([WT0139]) as [WT0139],
SUM([WT0140]) as [WT0140],
SUM([WT0141]) as [WT0141],
SUM([WT0142]) as [WT0142],
SUM([WT0143]) as [WT0143],
SUM([WT0144]) as [WT0144],
SUM([WT0145]) as [WT0145],
SUM([WT0146]) as [WT0146],
SUM([WT0147]) as [WT0147],
SUM([WT0148]) as [WT0148],
SUM([WT0149]) as [WT0149],
SUM([WT0150]) as [WT0150],
SUM([WT0201]) as [WT0201],
SUM([WT0202]) as [WT0202],
SUM([WT0203]) as [WT0203],
SUM([WT0204]) as [WT0204],
SUM([WT0205]) as [WT0205],
SUM([WT0206]) as [WT0206],
SUM([WT0207]) as [WT0207],
SUM([WT0208]) as [WT0208],
SUM([WT0209]) as [WT0209],
SUM([WT0210]) as [WT0210],
SUM([WT0211]) as [WT0211],
SUM([WT0212]) as [WT0212],
SUM([WT0213]) as [WT0213],
SUM([WT0214]) as [WT0214],
SUM([WT0215]) as [WT0215],
SUM([WT0216]) as [WT0216],
SUM([WT0217]) as [WT0217],
SUM([WT0218]) as [WT0218],
SUM([WT0219]) as [WT0219],
SUM([WT0220]) as [WT0220],
SUM([WT0221]) as [WT0221],
SUM([WT0222]) as [WT0222],
SUM([WT0223]) as [WT0223],
SUM([WT0224]) as [WT0224],
SUM([WT0225]) as [WT0225],
SUM([WT0226]) as [WT0226],
SUM([WT0227]) as [WT0227],
SUM([WT0228]) as [WT0228],
SUM([WT0229]) as [WT0229],
SUM([WT0230]) as [WT0230],
SUM([WT0231]) as [WT0231],
SUM([WT0232]) as [WT0232],
SUM([WT0233]) as [WT0233],
SUM([WT0234]) as [WT0234],
SUM([WT0235]) as [WT0235],
SUM([WT0236]) as [WT0236],
SUM([WT0237]) as [WT0237],
SUM([WT0238]) as [WT0238],
SUM([WT0239]) as [WT0239],
SUM([WT0240]) as [WT0240],
SUM([WT0241]) as [WT0241],
SUM([WT0242]) as [WT0242],
SUM([WT0243]) as [WT0243],
SUM([WT0244]) as [WT0244],
SUM([WT0245]) as [WT0245],
SUM([WT0246]) as [WT0246],
SUM([WT0247]) as [WT0247],
SUM([WT0248]) as [WT0248],
SUM([WT0249]) as [WT0249],
SUM([WT0250]) as [WT0250],
SUM([WT0301]) as [WT0301],
SUM([WT0302]) as [WT0302],
SUM([WT0303]) as [WT0303],
SUM([WT0304]) as [WT0304],
SUM([WT0305]) as [WT0305],
SUM([WT0306]) as [WT0306],
SUM([WT0307]) as [WT0307],
SUM([WT0308]) as [WT0308],
SUM([WT0309]) as [WT0309],
SUM([WT0310]) as [WT0310],
SUM([WT0311]) as [WT0311],
SUM([WT0312]) as [WT0312],
SUM([WT0313]) as [WT0313],
SUM([WT0314]) as [WT0314],
SUM([WT0315]) as [WT0315],
SUM([WT0316]) as [WT0316],
SUM([WT0317]) as [WT0317],
SUM([WT0318]) as [WT0318],
SUM([WT0319]) as [WT0319],
SUM([WT0320]) as [WT0320],
SUM([WT0321]) as [WT0321],
SUM([WT0322]) as [WT0322],
SUM([WT0323]) as [WT0323],
SUM([WT0324]) as [WT0324],
SUM([WT0325]) as [WT0325],
SUM([WT0326]) as [WT0326],
SUM([WT0327]) as [WT0327],
SUM([WT0328]) as [WT0328],
SUM([WT0329]) as [WT0329],
SUM([WT0330]) as [WT0330],
SUM([WT0331]) as [WT0331],
SUM([WT0332]) as [WT0332],
SUM([WT0333]) as [WT0333],
SUM([WT0334]) as [WT0334],
SUM([WT0335]) as [WT0335],
SUM([WT0336]) as [WT0336],
SUM([WT0337]) as [WT0337],
SUM([WT0338]) as [WT0338],
SUM([WT0339]) as [WT0339],
SUM([WT0340]) as [WT0340],
SUM([WT0341]) as [WT0341],
SUM([WT0342]) as [WT0342],
SUM([WT0343]) as [WT0343],
SUM([WT0344]) as [WT0344],
SUM([WT0345]) as [WT0345],
SUM([WT0346]) as [WT0346],
SUM([WT0347]) as [WT0347],
SUM([WT0348]) as [WT0348],
SUM([WT0349]) as [WT0349],
SUM([WT0350]) as [WT0350],
SUM([WT0401]) as [WT0401],
SUM([WT0402]) as [WT0402],
SUM([WT0403]) as [WT0403],
SUM([WT0404]) as [WT0404],
SUM([WT0405]) as [WT0405],
SUM([WT0406]) as [WT0406],
SUM([WT0407]) as [WT0407],
SUM([WT0408]) as [WT0408],
SUM([WT0409]) as [WT0409],
SUM([WT0410]) as [WT0410],
SUM([WT0411]) as [WT0411],
SUM([WT0412]) as [WT0412],
SUM([WT0413]) as [WT0413],
SUM([WT0414]) as [WT0414],
SUM([WT0415]) as [WT0415],
SUM([WT0416]) as [WT0416],
SUM([WT0417]) as [WT0417],
SUM([WT0418]) as [WT0418],
SUM([WT0419]) as [WT0419],
SUM([WT0420]) as [WT0420],
SUM([WT0421]) as [WT0421],
SUM([WT0422]) as [WT0422],
SUM([WT0423]) as [WT0423],
SUM([WT0424]) as [WT0424],
SUM([WT0425]) as [WT0425],
SUM([WT0426]) as [WT0426],
SUM([WT0427]) as [WT0427],
SUM([WT0428]) as [WT0428],
SUM([WT0429]) as [WT0429],
SUM([WT0430]) as [WT0430],
SUM([WT0431]) as [WT0431],
SUM([WT0432]) as [WT0432],
SUM([WT0433]) as [WT0433],
SUM([WT0434]) as [WT0434],
SUM([WT0435]) as [WT0435],
SUM([WT0436]) as [WT0436],
SUM([WT0437]) as [WT0437],
SUM([WT0438]) as [WT0438],
SUM([WT0439]) as [WT0439],
SUM([WT0440]) as [WT0440],
SUM([WT0441]) as [WT0441],
SUM([WT0442]) as [WT0442],
SUM([WT0443]) as [WT0443],
SUM([WT0444]) as [WT0444],
SUM([WT0445]) as [WT0445],
SUM([WT0446]) as [WT0446],
SUM([WT0447]) as [WT0447],
SUM([WT0448]) as [WT0448],
SUM([WT0449]) as [WT0449],
SUM([WT0450]) as [WT0450]
,COUNT(DISTINCT employee_id) AS TotEmpID
,period_id
,period_desc
,convert(decimal(28,0), '1') AS flag
,org_group
,org_group_desc
FROM (
		SELECT DISTINCT d.*, c.column_name, c.description, c.landscape, c.rep_id, c.row
				,h.org_group
				,h.org_group_desc 
		FROM @hr_cust_py_report_desc as c 
		LEFT JOIN  ( SELECT DISTINCT b.*, a.col_no, a.amount, period_id, period_desc 
					 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 
		LEFT JOIN @tbl_employee AS h
		ON h.employee_id = d.employee_id
)AS SourceTable
PIVOT
(
  SUM(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 
	period_id
	,period_desc
	,org_group
	,org_group_desc
HAVING org_group IS NOT NULL