Rpt_IMFI_HR_PY_TaxComponent_detail_20240322.sql - IMFI Bugs - Redmine

Bug #2664 » Rpt_IMFI_HR_PY_TaxComponent_detail_20240322.sql

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

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

(6-6/7)

Project

General

Profile

CLIENT SUPPORT » IMFI Bugs

Bug #2664 » Rpt_IMFI_HR_PY_TaxComponent_detail_20240322.sql