CLIENT SUPPORT » Bank Mega

CREATE PROCEDURE [dbo].[Rpt_Mega_WTRecap_Summary_New_Header]

	@landscape varchar(3),

	@payperiod varchar(6)

AS

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

 --declare @payperiod varchar(6) = '201102'

DECLARE @payPeriodStartDate varchar(8)

DECLARE @payPeriodEndDate varchar(8)

DECLARE @payPeriodEndDateDt datetime

DECLARE @payPeriodStr varchar(50)

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 @payPeriodStr = DATENAME(month,@payPeriodEndDateDt) + ' ' + left(@payPeriodEndDate,4)

 declare @logo varbinary(max)

 declare @companyname varchar(50)

 SELECT TOP 1

       @companyname = [description]

      ,@logo = [logo]

  FROM [dbo].[base_cust_ref_companycode]  

  where landscape = @landscape 

declare @group_by varchar(50)

declare @group_by_colname varchar(50)

select top 1 @group_by = col_id, @group_by_colname = descr

from dbo.hr_cust_py_report_additional 

where	landscape = @landscape 

  and	rep_id = 'WT03R' 

  and	start_date <= @payPeriodEndDate 

  and	end_date >= @payPeriodEndDate 

  and	is_grouped = 1

if @group_by is null

begin

set @group_by  = 'cost_center_descr'

set @group_by_colname  = 'Cost Center Descr'

end

  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

----//---- cek kolom ADF buat header dari py_report_additional

----select top 1000 'ADF' + right('00' + convert(varchar,ROW_NUMBER() OVER (ORDER BY col_no)),2) as column_id ,case is_visible when 1 then descr else '' end as description from dbo.hr_cust_py_report_additional where landscape = @landscape and rep_id = 'WT01R' and start_date <= @payPeriodEndDate and end_date >= @payPeriodEndDate order by 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

  select * from (

  select  @companyname as companyname, @logo as logo, @payPeriodStr as payPeriodStr, @group_by as group_by, @group_by_colname as group_by_colname, * 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) as a cross join 

	(

SELECT ADF01	,

ADF02	,

ADF03	,

ADF04	,

ADF05	,

ADF06	,

ADF07	,

ADF08	,

ADF09	,

ADF10	,

ADF11	,

ADF12	,

ADF13	,

ADF14	,

ADF15	,

ADF16	,

ADF17	,

ADF18	,

ADF19	,

ADF20	,

ADF21	,

ADF22	,

ADF23	,

ADF24	,

ADF25	,

ADF26	,

ADF27	,

ADF28	,

ADF29	,

ADF30	,

ADF31	,

ADF32	,

ADF33	,

ADF34	,

ADF53	,

ADF54	

FROM

(select top 1000 'ADF' + right('00' + convert(varchar,ROW_NUMBER() OVER (ORDER BY col_no)),2) as column_id ,case is_visible when 1 then descr else '' end as description from dbo.hr_cust_py_report_additional where landscape = @landscape and rep_id = 'WT03R' and start_date <= @payPeriodEndDate and end_date >= @payPeriodEndDate order by col_no

) AS SourceTable

PIVOT

(

max(description)

FOR column_id IN (

ADF01	,

ADF02	,

ADF03	,

ADF04	,

ADF05	,

ADF06	,

ADF07	,

ADF08	,

ADF09	,

ADF10	,

ADF11	,

ADF12	,

ADF13	,

ADF14	,

ADF15	,

ADF16	,

ADF17	,

ADF18	,

ADF19	,

ADF20	,

ADF21	,

ADF22	,

ADF23	,

ADF24	,

ADF25	,

ADF26	,

ADF27	,

ADF28	,

ADF29	,

ADF30	,

ADF31	,

ADF32	,

ADF33	,

ADF34	,

ADF53	,

ADF54		

)

) AS PivotTable

	)	as b