ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PY_TaxComponent_detail] 
	@landscape AS VARCHAR(3),
	@companycode AS VARCHAR(5),
	@paygroup AS VARCHAR(5),
	@payperiod AS VARCHAR(6),
	@tax_office VARCHAR(12),
	@emp_id varchar(12)
AS

--DECLARE @landscape AS VARCHAR(3) 
--DECLARE @companycode AS VARCHAR(5) 
--DECLARE @paygroup AS VARCHAR(5) 
--DECLARE @payperiod AS VARCHAR(6) 
--DECLARE @tax_office VARCHAR(12)
--DECLARE @emp_id VARCHAR(8)
--SET @landscape = '100'
--SET @companycode = '1000' 
--SET @paygroup = '11'
--SET @payperiod = '202403'
--SET @tax_office = ''
--SET @emp_id = '20000003'


DECLARE @companyname AS NVARCHAR(50)
DECLARE @companylogo AS VARBINARY(MAX)
DECLARE @payrollgroup AS NVARCHAR(50)
DECLARE @paymonth AS NVARCHAR(2)
DECLARE @payyear AS NVARCHAR(4)
DECLARE @begda AS NVARCHAR(8)
DECLARE @endda AS NVARCHAR(8)
DECLARE @enddate AS VARCHAR(8)
SET @paymonth = RIGHT(@payperiod,2)
SET @payyear = LEFT(@payperiod,4)
SET @begda = @payperiod + '01'
SET @endda = convert(NVARCHAR(8),dateadd(d,-1, dateadd(m, 1, @begda)), 112)

DECLARE @last_date AS varchar(12) 
DECLARE @last_year AS varchar(8)
DECLARE @last_month AS varchar(8)
SET @last_date = convert(VARCHAR(6), dateadd(d,0, dateadd(m,-1, @begda)), 112)
SET @last_year = LEFT(@last_date,4)
SET @last_month = RIGHT(@last_date,2)

-----//----- tabel-tabel internal
DECLARE @tbl_emp TABLE (
	emp_id varchar(8)
	,emp_name varchar(250)
	,[bank_id] [varchar](2) 
	,[bank_account] [varchar](50) 
	,[pay_grade] [varchar](8) 
	,[tax_status] [varchar](8) 
	,[npwp] [varchar](50) 
	,[jamsostek_type] [varchar](4) 
	,[jamsostek] [varchar](50) 
	,[jamsostek_level] [varchar](5)
	,[cost_center] [varchar](8) 
	,[employee_area] [varchar](4) 
	,[employee_office] [varchar](4) 
	,[employee_status] [varchar](8) 
	,[employee_type] [varchar](8) 
	,[employee_subtype] [varchar](8) 
	,[payroll_group] [varchar](8) 
	,[organization] [varchar](8) 
	,[position] [varchar](8) 
	,[job] [varchar](8) 
	,office_name varchar(250) 
	,pygroup varchar(10)
	,pygroup_desc varchar(250)
	,tax_desc varchar(250)
	,npwp_taxoffice varchar(50)
	)
	
DECLARE @tbl_py TABLE (
	emp_id varchar(8)
	,this_amount decimal(18,0)
	,wage_type VARCHAR(10)
	,wage_type_desc VARCHAR(100)
	,jum_emp DECIMAL(18,0)
	)

DECLARE @tbl_py_last TABLE (
	emp_id varchar(8)
	,last_amount decimal(18,0)
	,wage_type VARCHAR(10)
	,wage_type_desc VARCHAR(100)
	,jum_emp DECIMAL(18,0)
	)

DECLARE @tbl_tr_enc TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount varchar(250),
	 rate varchar(250),
	 flg_retro varchar(3),
	 flg_period VARCHAR(20),
	 wt_group VARCHAR(100))

DECLARE @tbl_tr_dec TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount decimal(18,0),
	 rate decimal(18,0),
	 flg_retro varchar(3),
	 flg_period VARCHAR(20),
	 wt_group VARCHAR(100))

DECLARE @tbl_tr_dec_sum TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount decimal(18,0),
	 rate decimal(18,0),
	 flg_retro varchar(1),
	 flg_period VARCHAR(20),
	 wt_group VARCHAR(100))
	 
DECLARE @t_paygroup TABLE
(
	paygroup VARCHAR(5)
)

DECLARE @t_wt TABLE
(
	wt VARCHAR(8),
	wt_group VARCHAR(100)
)

SELECT @companyname = description
	 , @companylogo = logo
FROM
	base_cust_ref_companycode
WHERE
	(landscape = @landscape)
	AND (companycode = @companycode)

SELECT @payrollgroup = description
FROM
	base_cust_ref_payroll_group
WHERE
	(landscape = @landscape)
	AND (payroll_group = @paygroup)

-----//----- get wt
INSERT INTO @t_wt
SELECT val1, val2 FROM base_cust_parameter WHERE param = 'RPT_IMFI_TAX_COMPONENT'

-----//----- pengambilan data employee
INSERT INTO @tbl_emp 	
SELECT  DISTINCT tr300.employee_id
		,md3.no_tht--,md1.full_name
		--,CASE WHEN md3.bank_account_name <> '' THEN md3.bank_account_name ELSE md1.full_name END
		,md3.[bank_id]
		,md3.[bank_account]
		,md3.[pay_grade]
		,md3.[tax_status]
		,md3.[npwp]
		,md3.[jamsostek_type]
		,md3.[jamsostek]
		,md3.[jamsostek_level]
		,tr300.[cost_center]
		,tr300.[employee_area]
		,tr300.[employee_office]
		,tr300.[employee_status]
		,tr300.[employee_type]
		,tr300.[employee_subtype]
		,tr300.[payroll_group]
		,tr300.[organization]
		,tr300.[position]
		,tr300.[job]
		,office.emp_subarea_description
		,md2.payroll_group
		,pyg.description
		,tax.description
		,office.npwp
FROM hr_tr0300 AS tr300
LEFT JOIN hr_md_emp_md0001 AS md1
	ON tr300.landscape = md1.landscape
	AND tr300.employee_id = md1.emp_id 
	AND md1.start_date <= @endda AND md1.end_date >= @endda
LEFT JOIN hr_md_emp_md0002 AS md2
	ON md2.landscape = tr300.landscape
	AND md2.emp_id = tr300.employee_id
	AND md2.start_date < = @endda AND md2.end_date >= @endda	 
LEFT JOIN hr_md_emp_md0003 AS md3
	ON tr300.landscape = md3.landscape
	AND tr300.employee_id = md3.emp_id 
	AND md3.start_date <= @endda AND md3.end_date >= @endda 
LEFT JOIN base_cust_ref_emp_office AS office
	ON office.emp_subarea = tr300.employee_office
	AND office.landscape = tr300.landscape 
LEFT JOIN base_cust_ref_tax_office AS tax
	ON tax.landscape = md2.landscape
	AND tax.tax_office = office.tax_office
LEFT JOIN dbo.base_cust_ref_payroll_group AS pyg
	ON pyg.landscape = md2.landscape
	AND pyg.payroll_group = md2.payroll_group
WHERE tr300.landscape = @landscape
	AND tr300.payroll_group = @paygroup 
	AND tr300.pay_period_month = @paymonth AND tr300.pay_period_year = @payyear
	AND tr300.run_period_month = @paymonth AND tr300.run_period_year = @payyear 
	AND (tr300.company_id = @companycode OR @companycode = '')
	AND (office.tax_office = @tax_office OR @tax_office = '')
	AND (tr300.employee_id = @emp_id OR @emp_id = '')

--------------- get data payroll --------------
INSERT INTO @tbl_tr_enc
SELECT  pr.landscape , pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
	    pr.run_period_month, pr.run_period_year, 
	    pr.wage_type, pr.amount, pr.rate, 'N', 'now'
	    ,wt.wt_group
FROM hr_tr0301 pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
	INNER JOIN @t_wt wt
		ON pr.wage_type = wt.wt
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear 
	AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear 
	--AND pr.wage_type in (SELECT * FROM base_cust_parameter WHERE PARAM = 'RPT_IMFI_TAX_COMPONENT')
	
INSERT INTO @tbl_tr_enc
SELECT	pr.landscape, pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
		pr.run_period_month, pr.run_period_year, 
		pr.wage_type, pr.amount, pr.rate, 'R', 'now'
		,wt.wt_group
FROM hr_tr0301_retro pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
	INNER JOIN @t_wt wt
		ON pr.wage_type = wt.wt
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear 
	AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear 
	AND (pr.wage_type NOT IN ('MT','5000','50001'))
	--AND pr.wage_type in ('1000','1300','1202','1221','1222','1223','1224','1225','1226','1227','1228','1229','1304','1200','1201','1301','1203','1210','1212','1213','1302','1500','1205','1231','1232','1233','1234','1235','1303','1501','1504','2000','2001','2002','2003','1502','6000','6200','6202','6203','6204','6205','6206','6207','6500','6501','6502','6503','6504','1503','3000','3100','3200','5000','5001','JER3','JER2','TD','TI','MT','TP') ----------- tambahin wage_type apa aja ya---------

INSERT INTO @tbl_tr_enc
SELECT  pr.landscape , pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
	    pr.run_period_month, pr.run_period_year, 
	    pr.wage_type, pr.amount, pr.rate, 'N', 'last'
	    ,wt.wt_group
FROM hr_tr0301 pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
	INNER JOIN @t_wt wt
		ON pr.wage_type = wt.wt
WHERE pr.pay_period_month = @last_month AND pr.pay_period_year = @last_year 
	AND pr.run_period_month = @last_month AND pr.run_period_year = @last_year
	--AND pr.wage_type in ('1000','1300','1202','1221','1222','1223','1224','1225','1226','1227','1228','1229','1304','1200','1201','1301','1203','1210','1212','1213','1302','1500','1205','1231','1232','1233','1234','1235','1303','1501','1504','2000','2001','2002','2003','1502','6000','6200','6202','6203','6204','6205','6206','6207','6500','6501','6502','6503','6504','1503','3000','3100','3200','5000','5001','JER3','JER2','TD','TI','MT','TP') ----------- tambahin wage_type apa aja ya---------
	
INSERT INTO @tbl_tr_enc
SELECT	pr.landscape, pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
		pr.run_period_month, pr.run_period_year, 
		pr.wage_type, pr.amount, pr.rate, 'R', 'last'
		,wt.wt_group
FROM hr_tr0301_retro pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
	INNER JOIN @t_wt wt
		ON pr.wage_type = wt.wt
WHERE pr.pay_period_month = @last_month AND pr.pay_period_year = @last_year 
	AND pr.run_period_month = @last_month AND pr.run_period_year = @last_year
	AND (pr.wage_type NOT IN ('MT','5000','50001'))
	--AND pr.wage_type in ('1000','1300','1202','1221','1222','1223','1224','1225','1226','1227','1228','1229','1304','1200','1201','1301','1203','1210','1212','1213','1302','1500','1205','1231','1232','1233','1234','1235','1303','1501','1504','2000','2001','2002','2003','1502','6000','6200','6202','6203','6204','6205','6206','6207','6500','6501','6502','6503','6504','1503','3000','3100','3200','5000','5001','JER3','JER2','TD','TI','MT','TP') ----------- tambahin wage_type apa aja ya---------
	 
INSERT INTO @tbl_tr_dec
SELECT pr.landscape, pr.employee_id, 
	   pr.payPeriodMonth, pr.payPeriodYear, 
	   pr.runPeriodMonth, pr.runPeriodYear, pr.wage_type,
	   CONVERT(decimal(18,0), dbo.SDE(pr.amount, 'M!N0V@2010')),
	   CONVERT(decimal(18,0), dbo.SDE(pr.rate, 'M!N0V@2010')),  pr.flg_retro, pr.flg_period
	   ,wt_group
FROM @tbl_tr_enc pr

INSERT INTO @tbl_tr_dec_sum
SELECT tbl_tr_dec.landscape, employee_id,
	   payPeriodMonth, payPeriodYear,
	   runPeriodMonth, runPeriodYear, wage_type,
	   SUM(amount)AS amount,
	   SUM(rate) AS rate,
	   min(flg_retro) AS flg_retro, flg_period
	   ,wt_group
FROM @tbl_tr_dec AS tbl_tr_dec
GROUP BY tbl_tr_dec.landscape, employee_id, 
		 payPeriodMonth, payPeriodYear,
		 runPeriodMonth,runPeriodYear, wage_type, flg_period
		 ,wt_group


-----//----- data payroll bulan ini
INSERT INTO @tbl_py 
SELECT  tbl_tr_dec_sum.employee_id
		,tbl_tr_dec_sum.amount 'amount'
		,tbl_tr_dec_sum.wage_type
		,wt_group
		,1
FROM @tbl_tr_dec_sum AS tbl_tr_dec_sum 
WHERE flg_period = 'now'

-----//------ pengambilan data bulan lalu
INSERT INTO @tbl_py_last  
SELECT  tbl_tr_dec_sum.employee_id
		,tbl_tr_dec_sum.amount 'amount'
		,tbl_tr_dec_sum.wage_type
		,wt_group
		,1
FROM @tbl_tr_dec_sum AS tbl_tr_dec_sum 
WHERE flg_period = 'last'

--SELECT DISTINCT tbl_emp.employee_office 
--			,tbl_emp.office_name 
--			,tbl_emp.tax_desc
--			,isnull(SUM(tbl_py.this_amount), 0) AS this_amount
--			,tbl_py.wage_type_desc
--			,tbl_py.jum_emp
--			--,SUM(tbl_py.jum_emp) AS jum_emp
--FROM @tbl_emp AS tbl_emp
--INNER JOIN @tbl_py AS tbl_py
--	ON tbl_py.emp_id = tbl_emp.emp_id  
--WHERE tbl_py.this_amount > 0
--GROUP BY tbl_emp.employee_office
--		,tbl_emp.office_name 
--		,tbl_emp.tax_desc
--		,tbl_py.wage_type_desc
--		,tbl_py.jum_emp

--SELECT DISTINCT tbl_emp.emp_id
--			,tbl_emp.employee_office 
--			,tbl_emp.office_name 
--			,tbl_emp.tax_desc
--			,isnull(tbl_py.this_amount, 0) AS this_amount
--			,tbl_py.wage_type_desc
--			,jum.jum_emp
--	FROM @tbl_emp AS tbl_emp
--	INNER JOIN @tbl_py AS tbl_py
--		ON tbl_py.emp_id = tbl_emp.emp_id 
--	LEFT JOIN ( 
--				SELECT tbl_emp.employee_office 
--						,tbl_emp.office_name 
--						,tbl_emp.tax_desc
--						,COUNT(tbl_emp.emp_id) AS jum_emp 
--				FROM @tbl_emp AS tbl_emp
--				GROUP BY tbl_emp.employee_office 
--						,tbl_emp.office_name 
--						,tbl_emp.tax_desc
--			  ) AS jum
--		ON tbl_emp.employee_office  = jum.employee_office 
--		AND tbl_emp.tax_desc = jum.tax_desc
--	WHERE tbl_py.this_amount > 0

---//----- data result 
SELECT * FROM 
(
SELECT DISTINCT  tbl_emp.emp_id , tbl_emp.emp_name,
tbl_emp.employee_office 
			,tbl_emp.office_name 
			,tbl_emp.tax_desc
			,tbl_emp.npwp_taxoffice
			,isnull(SUM(tbl_py.this_amount), 0) AS this_amount
			,tbl_py.wage_type_desc
			,jum.jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN ( 
				SELECT tbl_emp.employee_office 
						,tbl_emp.office_name 
						,tbl_emp.tax_desc
						,COUNT(tbl_emp.emp_id) AS jum_emp 
				FROM @tbl_emp AS tbl_emp
				GROUP BY tbl_emp.employee_office 
						,tbl_emp.office_name 
						,tbl_emp.tax_desc
			  ) AS jum
		ON tbl_emp.employee_office  = jum.employee_office 
		AND tbl_emp.tax_desc = jum.tax_desc
	--WHERE tbl_py.this_amount > 0
	GROUP BY tbl_emp.emp_id , tbl_emp.emp_name,
tbl_emp.employee_office 
			,tbl_emp.office_name, tbl_emp.tax_desc
		,tbl_py.wage_type_desc
		,jum.jum_emp
		,tbl_emp.npwp_taxoffice
) AS SourceTable
PIVOT
(
	SUM (this_amount)
FOR wage_type_desc IN
(	[Basic Salary], 
	[HP Allowance],
	[Meal Allowance],
	[Non Taxable Income],
	[Rapel/Other Allowance],
	[Tax],
	[Tax Allowance],
	[Total Allowance],
	[Total Income],
	[Total Tax Component],
	[Transport Allowance],
	[Position Allowance],
	[Insentif Allowance],
	[Medical],
	[THR/Bonus],
	[Total Deduction],
	[Overtime],
	[JKK/JKM],
	[Tax Paid]
	
)
) AS pvt