Rpt_HR_PY_eSPT_2014_202510101140.sql - IMFI Bugs - Redmine

Bug #3948 » Rpt_HR_PY_eSPT_2014_202510101140.sql

Tri Rizqiaty, 10/10/2025 11:41 AM

    
      ??USE [MinovaHR_Indomobil_Prod]

      GO

      /****** Object:  StoredProcedure [dbo].[Rpt_HR_PY_eSPT_2014]    Script Date: 10/10/2025 11.10.10 ******/

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      ALTER procedure [dbo].[Rpt_HR_PY_eSPT_2014]

      	@landscape varchar(3),

      	@company_code varchar(4),

      	@year varchar(4),

      	@tax_office varchar(8),

      	@employee_status varchar(2) = null,

      	@payroll_group varchar(2) = null,

      	@emp_id_beg varchar(8) = null,

      	@emp_id_end varchar(8) = NULL

      AS

      SET NOCOUNT ON;

      --DECLARE @landscape varchar(3) = '100'

      --DECLARE @company_code varchar(4) = '1000'

      --DECLARE @year varchar(4) = '2025'

      --DECLARE @tax_office varchar(8) = 'BDG'

      --DECLARE @employee_status varchar(2) = ''

      --DECLARE @payroll_group varchar(2) = '13'

      --DECLARE @emp_id_beg varchar(8) = ''--'20240091'

      --DECLARE @emp_id_end varchar(8) = ''--'20240091'

      DECLARE @begda INT

      DECLARE @endda INT

      DECLARE @employee_status_beg VARCHAR(2)

      DECLARE @employee_status_end VARCHAR(2)

      DECLARE @payroll_group_beg VARCHAR(2)

      DECLARE @payroll_group_END VARCHAR(2)

      --Set begin & end of period

      SET @begda = @year + '0101'

      SET @endda = @year + '1231'

      --Set employee status

      IF @employee_status = '' or  @employee_status is null 

      BEGIN

      	SET @employee_status_beg = '00'

      	SET @employee_status_end = '99'

      END

      ELSE

      BEGIN

      	SET @employee_status_beg = @employee_status

      	SET @employee_status_end = @employee_status

      END

      --Set employee group

      IF @payroll_group = '' or @payroll_group  is null

      BEGIN

      	SET @payroll_group_beg = @payroll_group

      	SET @payroll_group_END = @payroll_group

      END

      ELSE

      BEGIN

      	SET @payroll_group_beg = @payroll_group

      	SET @payroll_group_END = @payroll_group

      END

      --Set employee id start

      IF @emp_id_beg = '' or  @emp_id_beg is null 

      BEGIN

      	SET @emp_id_beg = '00000000'

      	SET @emp_id_end = '99999999'

      END

      IF @emp_id_end = '' or  @emp_id_end is null 

      	SET @emp_id_end =  @emp_id_beg 

      --Get system parameter

      DECLARE @is_encrypted VARCHAR

      DECLARE @npwp_address_subtype VARCHAR(4)

      SELECT @is_encrypted = val1

      FROM dbo.base_cust_parameter

      WHERE

      	landscape = @landscape

      	AND param = 'HR_PY_ENCRYPTED'

      	AND start_date <= @endda

      	AND end_date >= @endda

      SELECT @npwp_address_subtype = val1

      FROM dbo.base_cust_parameter

      WHERE

      	landscape = @landscape

      	AND param = 'HR_PY_NPWP_ADDRESS_SUBTYPE'

      	AND start_date <= @endda

      	AND end_date >= @endda

      --#ENDregion

      --Get employee tax office list

      DECLARE @emp_office table (

      	[landscape] [VARCHAR](3) not null,

      	[emp_subarea] [VARCHAR](4) not null,

      	[emp_area] [VARCHAR](4) not null,

      	[emp_subarea_description] [nVARCHAR](250) null,

      	[tax_office] [VARCHAR](3) null,

      	[npwp] [VARCHAR](30) null,

      	[address] [VARCHAR](300) null,

      	[employee_office_group] [VARCHAR](100) null,

      	[ump_code] [VARCHAR](100) null,

      	[remarks] [VARCHAR](100) null)

      INSERT INTO @emp_office

      SELECT *

      FROM

      	base_cust_ref_emp_office

      WHERE

      	landscape = @landscape

      	AND tax_office = @tax_office

      --Get Employee Tobe Processed

      DECLARE @employee_tobe_process2 table 

      (

      	landscape VARCHAR(3),

      	emp_id VARCHAR(8),

      	start_date INT,

      	end_date INT,

      	employee_status VARCHAR(2)

      )

      DECLARE @employee_tobe_process table 

      (

      	landscape VARCHAR(3),

      	emp_id VARCHAR(8),

      	start_date INT,

      	end_date INT

      )

      DECLARE @payroll_header TABLE (employee_id varchar(8), start_date varchar(8), end_date varchar(8), employee_office varchar(4), tax_office varchar(10), run_period_month VARCHAR(2))

      DECLARE @payroll_header_tmp TABLE (employee_id varchar(8), start_date varchar(8), end_date varchar(8), employee_office varchar(4), tax_office varchar(10), run_period_month VARCHAR(2))

      DECLARE @payroll_header0 TABLE (employee_id varchar(8), start_date varchar(8), end_date varchar(8), employee_office varchar(4), tax_office varchar(10), run_period_month VARCHAR(2))

      INSERT INTO @payroll_header_tmp

      SELECT DISTINCT employee_id, ph.start_date, ph.end_date, md2.employee_office, md2.tax_office, run_period_month

      		FROM hr_tr0300 ph

      		INNER JOIN hr_md_emp_md0002 md2

      				ON md2.landscape = ph.landscape

      					AND md2.emp_id = ph.employee_id

      			WHERE ph.landscape = @landscape 

      			AND ph.start_date >= @begda 

      			AND ph.end_date <= @endda

      			AND md2.start_date <= ph.end_date

      			AND md2.end_date >= ph.end_date

      			AND employee_id BETWEEN @emp_id_beg AND @emp_id_end

      			AND (md2.payroll_group = @payroll_group OR @payroll_group = '')

      ----SELECT DISTINCT employee_id FROm @payroll_header_tmp order by employee_id asc	

      --get the latest run data

      INSERT INTO @payroll_header0

      SELECT py_head.*

      FROM

        @payroll_header_tmp as py_head

        inner join (SELECT employee_id

      				   , start_date

      				   , end_date

      				   , max(run_period_month) as run_period_month

      			  FROM

      				  @payroll_header_tmp

      			  group by

      				  employee_id

      				, start_date

      				, end_date) a

      	  on py_head.employee_id = a.employee_id 

      		 and py_head.start_date = a.start_date 

      		 and py_head.end_date = a.end_date

      		 and py_head.run_period_month = a.run_period_month

      INSERT INTO @payroll_header

      SELECT DISTINCT employee_id, start_date, end_date, employee_office, ph0.tax_office, run_period_month

      		FROM @payroll_header0 ph0

      			LEFT OUTER JOIN @emp_office eo

      				ON (ph0.employee_office = eo.emp_subarea OR ph0.tax_office = eo.tax_office)

      		WHERE eo.landscape = @landscape

      			AND ((ph0.tax_office = @tax_office) OR 

      				 (eo.tax_office = @tax_office AND (ISNULL(ph0.tax_office, '') = '')))

      DECLARE @count_pay_header INT

      SELECT @count_pay_header = COUNT(*)

      FROM @payroll_header

      IF (@count_pay_header = 0)

      BEGIN

      	INSERT INTO @payroll_header

      	SELECT DISTINCT employee_id, start_date, end_date, employee_office, ph0.tax_office, run_period_month

      			FROM @payroll_header0 ph0

      			WHERE ph0.tax_office = @tax_office

      END	

      --get list spliiter running payroll for others TAX Office.

      SELECT DISTINCT employee_id, start_date, end_date, employee_office, run_period_month

      INTO #tmp0

      	FROM @payroll_header0 ph0

      	WHERE NOT EXISTS 

      		(

      			SELECT * 

      			FROM @payroll_header ph

      			WHERE ph0.employee_id = ph.employee_id

      				AND ph0.start_date = ph.start_date

      				AND ph0.end_date = ph.end_date

      				AND ph0.employee_office = ph.employee_office

      				AND ph0.tax_office = ph.tax_office

      				AND ph0.run_period_month = ph.run_period_month

      		)

      -- Get list date range splitter.

      SELECT a.employee_id, a.start_date mindt0, b.start_date maxdt0

      INTO #tmp

      FROM @payroll_header a, @payroll_header b

      WHERE a.employee_id = b.employee_id

      	AND b.start_date > a.start_date

      	AND (convert(INT,substring(b.start_date,5,2)) - convert(INT,substring(a.start_date,5,2))) > 1

      	AND (

      			SELECT COUNT(*)

      			FROM @payroll_header c

      			WHERE (convert(INT,substring(a.start_date,5,2)) < convert(INT,substring(c.start_date,5,2)))

      				AND (convert(INT,substring(b.start_date,5,2)) > convert(INT,substring(c.end_date,5,2)))

      				AND c.employee_id = a.employee_id

      		  ) = 0

      	AND (

      			SELECT COUNT(*)

      			FROM #tmp0 d

      			WHERE d.employee_id = a.employee_id

      				AND a.start_date < d.start_date

      				AND b.start_date > d.end_date

      		) <> 0

      DROP TABLE #tmp0

      DECLARE @n_empty_range INT  

      SELECT @n_empty_range = COUNT(*)

      FROM #tmp	  

      IF (@n_empty_range <> 0)

      BEGIN

      	INSERT INTO @employee_tobe_process

      	SELECT ddd.landscape, ddd.employee_id, ddd.mindt, ddd.maxdt

      	FROM

      	(

      		SELECT @landscape 'landscape', dd.employee_id, MIN(dd.start_date) mindt, MAX(dd.end_date) maxdt

      		FROM

      		(

      			SELECT x.employee_id, x.start_date, x.end_date, SUM(ISNULL(d.flag, 0)) flag

      			FROM @payroll_header x

      			LEFT OUTER JOIN

      			(

      				SELECT a.*, 1 'flag'

      				FROM @payroll_header a

      				LEFT OUTER JOIN #tmp b

      					ON a.employee_id = b.employee_id

      				WHERE a.start_date <= b.mindt0

      					AND a.end_date < b.maxdt0

      			) d

      			ON x.employee_id = d.employee_id

      				AND x.start_date = d.start_date

      				AND x.end_date = d.end_date

      			GROUP BY x.employee_id, x.start_date, x.end_date

      		) dd

      		GROUP BY dd.employee_id, dd.flag	

      	) ddd

      	ORDER BY ddd.employee_id, ddd.mindt		

      END

      ELSE

      BEGIN

      	INSERT INTO @employee_tobe_process

      	SELECT @landscape, main.employee_id, mindate.mindt, maxdate.maxdt

      		FROM (SELECT DISTINCT employee_id 

      			FROM @payroll_header) main

      		INNER JOIN (SELECT ph.employee_id, MIN(ph.start_date) mindt 

      			FROM @payroll_header ph

      			GROUP BY employee_id) mindate

      			ON main.employee_id = mindate.employee_id

      		INNER JOIN (SELECT employee_id, MAX(end_date) maxdt 

      			FROM @payroll_header ph

      			GROUP BY employee_id) maxdate

      			ON main.employee_id = maxdate.employee_id

      END

      DROP TABLE #tmp

      --SELECT * FROM @employee_tobe_process   

      DECLARE @kode_form VARCHAR(7)

      DECLARE @tahun_pajak VARCHAR(4)

      DECLARE @pembetulan INT

      DECLARE @nomor_urut VARCHAR(7)

      DECLARE @npwp_pegawai VARCHAR(15)

      DECLARE @nama_pegawai VARCHAR(50)

      DECLARE @alamat_pegawai VARCHAR(255)

      DECLARE @jabatan_pegawai VARCHAR(30)

      DECLARE @jenis_kelamin VARCHAR(1)

      DECLARE @status_pegawai VARCHAR(1)

      DECLARE @status_kawin VARCHAR(1)

      DECLARE @flag_asing VARCHAR(1)

      DECLARE @status_ptkp  VARCHAR(2)

      DECLARE @jumlah_tanggungan VARCHAR(1)

      DECLARE @masa_perolehan_1 VARCHAR(2)

      DECLARE @masa_perolehan_2 VARCHAR(2)

      DECLARE @flag_status  VARCHAR(1)

      DECLARE @a1 decimal

      DECLARE @flg_a2 VARCHAR(1)

      DECLARE @a2 decimal

      DECLARE @a3 decimal

      DECLARE @a4 decimal

      DECLARE @a5 decimal

      DECLARE @a6 decimal

      DECLARE @a7 decimal

      DECLARE @a8 decimal

      DECLARE @a9 decimal

      DECLARE @a10 decimal

      DECLARE @a11 decimal

      DECLARE @a12 decimal

      DECLARE @a13 decimal

      DECLARE @a14 decimal

      DECLARE @a15 decimal

      DECLARE @a16 DECIMAL

      DECLARE @a16_flag DECIMAL

      DECLARE @a17 decimal

      DECLARE @a18 decimal

      DECLARE @a19 decimal

      DECLARE @a19b decimal

      DECLARE @a20 decimal

      DECLARE @a21 decimal

      DECLARE @a22 decimal

      DECLARE @a22a decimal

      DECLARE @a22b decimal

      DECLARE @a23 decimal

      DECLARE @a24 decimal

      DECLARE @flg_a24 VARCHAR(1)

      DECLARE @bln_a24 VARCHAR(6)

      DECLARE @masa_pajak VARCHAR(10)

      DECLARE @kode_pajak VARCHAR(30)

      DECLARE @npwp_pemotong VARCHAR(15)

      DECLARE @nama_pemotong VARCHAR(200)

      DECLARE @company_name VARCHAR(200)

      DECLARE @a19murni DECIMAL

      DECLARE @a17_att DECIMAL

      DECLARE @ktp VARCHAR(50)

      DECLARE @jml_bulan_perolehan int

      DECLARE @tot_biaya_jabatan decimal

      DECLARE @result table (              

      	 kode_form VARCHAR(7)

      	,tahun_pajak VARCHAR(4)

      	,pembetulan INT

      	,nomor_urut CHAR(7)

      	,npwp_pegawai VARCHAR(30)

      	,nama_pegawai VARCHAR(50)

      	,alamat_pegawai VARCHAR(255)

      	,jabatan_pegawai VARCHAR(30)

      	,jenis_kelamin VARCHAR(5)

      	,status_pegawai VARCHAR(5)

      	,status_kawin VARCHAR(5)

      	,flag_asing VARCHAR(1)

      	,status_ptkp  VARCHAR(5)

      	,jumlah_tanggungan VARCHAR(1)

      	,masa_perolehan_1 VARCHAR(2)

      	,masa_perolehan_2 VARCHAR(2)

      	,flag_status  VARCHAR(1)

      	,a1 decimal

      	,flg_a2 VARCHAR(1)

      	,a2 DECIMAL

      	,a3 decimal

      	,a4 decimal

      	,a5 decimal

      	,a6 decimal

      	,a7 decimal

      	,a8 decimal

      	,a9 decimal

      	,a10 decimal

      	,a11 decimal

      	,a12 decimal

      	,a13 decimal

      	,a14 decimal

      	,a15 decimal

      	,a16 decimal

      	,a17 decimal

      	,a18 decimal

      	,a19 decimal

      	,a20 decimal

      	,a21 decimal

      	,a22 decimal

      	,a22a decimal

      	,a22b decimal

      	,a23 decimal

      	,a24 decimal

      	,flg_a24 VARCHAR(1)

      	,bln_a24 VARCHAR(6)

      	,KTP VARCHAR(50)

      	,nama_pemotong VARCHAR(200)

      	,npwp_pemotong VARCHAR(30)

      	,kode_pajak VARCHAR(30)

      	,company_name VARCHAR(200)

      	,emp_id VARCHAR(20)

      )                   

      DECLARE @cu0300 table(

      	[landscape] [VARCHAR](3) not null,

      	[code] [nVARCHAR](4) not null,

      	[start_date] [nVARCHAR](8) not null,

      	[end_date] [nVARCHAR](8) not null,	

      	[spt_no] [INT] null

      )

      DECLARE @cu0300_tmp table(

      	[landscape] [VARCHAR](3) not null,

      	[code] [nVARCHAR](4) not null,

      	[start_date] [nVARCHAR](8) not null,

      	[end_date] [nVARCHAR](8) not null,	

      	[spt_no] [INT] null

      )

      DECLARE @tbl_tr_prev_ori table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_prev_ori_tmp table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_prev_cum table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_prev_cum_tmp table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_dec_ori table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_dec_ori_tmp table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_dec table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_dec_tmp table (

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount DECIMAL,

      	 spt_no INT,

      	 run_period_month VARCHAR(2)

      )

      DECLARE @tbl_tr_dec_total table(

      	landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 amount decimal,

      	 spt_no INT

      )

      DECLARE @tbl_tr_enc table(

      	 landscape VARCHAR (3),

      	 employee_id VARCHAR(8),

      	 wage_type VARCHAR(4),

      	 amount VARCHAR(250),

      	 spt_no INT

      )

      SET @kode_form = 'D113248'

      SET @tahun_pajak = @year

      SET @pembetulan = 0

      DECLARE @c_landscape VARCHAR(3)

      DECLARE @c_emp_id VARCHAR(8)

      DECLARE @c_start_date INT

      DECLARE @c_end_date INT

      DECLARE @last_period_payroll INT

      DECLARE @previous_period_payroll INT

      DECLARE @previous_period_payroll0 INT

      DECLARE @previous_period_payroll_varchar VARCHAR(2)

      -- Run Cursor

      DECLARE cur_employee CURSOR FOR

      SELECT * FROM @employee_tobe_process

      OPEN cur_employee

      FETCH cur_employee INTO @c_landscape,

                          @c_emp_id,

                          @c_start_date,

                          @c_end_date

      WHILE @@fetch_status = 0

      BEGIN

          SET @last_period_payroll = @c_end_date

      	SET @nomor_urut = convert(integer,right(@c_emp_id,7))

      	SELECT @npwp_pegawai = replace(replace(isnull(npwp, '000000000000000'), '.', ''), '-', '')

      		 , @status_kawin = case LEFT(tax_status, 1)

      						   when 'T' then

      							   '1'

      						   ELSE

      							   '2'

      						   END

      		 --, @status_ptkp = case LEFT(tax_status, 1)

      			--			   when 'T' then

      			--				   '1'

      			--			   ELSE

      			--				   '2'

      			--			   END

      		 , @status_ptkp = a.tax_status

      		 , @jumlah_tanggungan = case right(tax_status, 1)

      								when 'K' then

      									'0'

      								ELSE

      									right(tax_status, 1)

      								END

      		 , @nama_pegawai = a.no_tht -------// add by Tri nwh 20220722

      	FROM

      		hr_md_emp_md0003 AS a

      	--LEFT JOIN dbo.base_cust_ref_tax_status AS b

      	--	ON a.landscape = b.landscape AND a.tax_status = b.code

      	WHERE

      		a.landscape = @c_landscape

      		and

      		emp_id = @c_emp_id

      		and

      		start_date <= @c_end_date

      		and

      		end_date >= @c_end_date

      	SELECT  DISTINCT @a17 = ptkp

      	FROM

      		[dbo].[hr_cu0302]

      	WHERE

      		landscape = @c_landscape

      		and

      		start_date <= @c_end_date

      		and

      		end_date >= @c_end_date

      		and status = (SELECT DISTINCT tax_status

      					  FROM

      						  hr_md_emp_md0003

      					  WHERE

      						  landscape = @c_landscape

      						  and

      						  emp_id = @c_emp_id

      						  and

      						  start_date <= @c_end_date

      						  and

      						  end_date >= @c_end_date)

      	  --SELECT @nama_pegawai = full_name												------// comment by Tri nwh 20220722

      		 --  ,@jenis_kelamin = b.description--, @jenis_kelamin = convert(INT, gender)	------// comment by Tri nwh 20220722	

      		 --  ,@ktp = a.ktp																------// comment by Tri nwh 20220722

      		SELECT @jenis_kelamin = b.description--, @jenis_kelamin = convert(INT, gender)	

      		,@ktp = a.ktp

      	  FROM

      		  hr_md_emp_md0001 AS a

      	  LEFT JOIN dbo.base_cust_ref_gender AS b

      		ON a.landscape = b.landscape AND a.gender = b.code

      	  WHERE

      		  a.landscape = @c_landscape

      		  and

      		  emp_id = @c_emp_id

      		  and

      		  start_date <= @c_end_date

      		  and

      		  end_date > -@c_end_date

      	  SELECT @nama_pemotong = t.description , @npwp_pemotong = o.npwp

      	  FROM dbo.hr_md_emp_md0002 AS a

      	  LEFT JOIN dbo.base_cust_ref_emp_office AS o

      		ON a.landscape = o.landscape AND a.employee_office = o.emp_subarea

      	  LEFT JOIN dbo.base_cust_ref_tax_office AS t

      		ON a.landscape = t.landscape AND t.tax_office = o.tax_office

      	  WHERE a.landscape = @landscape 

      			AND a.start_date <= @c_end_date AND end_date >= @c_end_date

      			AND a.emp_id = @c_emp_id

      	  SELECT @kode_pajak = e.kode_pajak, @company_name = g.description

      	  FROM dbo.hr_md_emp_md0002 AS a

      	  LEFT JOIN dbo.base_cust_ref_emp_type AS e

      		ON a.landscape = e.landscape AND a.employee_type = e.emp_type

      	  LEFT JOIN dbo.base_cust_ref_companycode AS g

      		ON a.landscape = g.landscape AND a.company_id = g.companycode

      	  WHERE a.landscape = @landscape 

      			AND a.start_date <= @c_end_date AND end_date >= @c_end_date

      			AND a.emp_id = @c_emp_id

      	  --alamat & flag status belakangan

      	  SELECT @alamat_pegawai = replace(street, ',', ' ')

      	  FROM

      		  hr_md_emp_md0006 as md06

      	  WHERE

      		  landscape = @c_landscape

      		  and

      		  emp_id = @c_emp_id

      		  and

      		  start_date <= @c_end_date

      		  and

      		  end_date >= @c_end_date

      		  and address_type = @npwp_address_subtype

      	  SELECT @jabatan_pegawai = mdobj.description

      	  FROM

      		  hr_md_emp_md0002 as md02

      		  inner join hr_md_orm_object as mdobj

      			  on

      			  md02.landscape = mdobj.landscape

      			  and

      			  md02.position = mdobj.object

      			  and

      			  md02.emp_id = @c_emp_id

      			  and

      			  md02.start_date <= @c_end_date

      			  and

      			  md02.end_date > -@c_end_date

      			  and

      			  mdobj.start_date <= @c_end_date

      			  and

      			  mdobj.end_date > -@c_end_date

      			  and

      			  mdobj.class = 'P'

      	  SELECT @status_pegawai = case employee_status

      							   when '01' then

      								   '1'

      							   when '03' then

      								   '2'

      							   ELSE

      								   '1'

      							   END

      		   , @flag_asing = case employee_type

      						   when '99' then

      							   '1'

      						   ELSE

      							   '0'

      						   END

      	  FROM

      		  hr_md_emp_md0002 as md02

      	  WHERE

      		  landscape = @c_landscape

      		  and

      		  emp_id = @c_emp_id

      		  and

      		  start_date <= @c_end_date

      		  and

      		  end_date >= @c_end_date 

      	SET

      	@masa_perolehan_1 = convert(INTeger,substring(convert(VARCHAR, @c_start_date),5,2))

      	SET

      	@masa_perolehan_2 = convert(INTeger,substring(convert(VARCHAR, @c_end_date),5,2))

      	SET @jml_bulan_perolehan = (CONVERT(int, @masa_perolehan_2) - CONVERT(int, @masa_perolehan_1) + 1)

      				  --#region getwtconfiguration

      	DELETE @cu0300_tmp

      	INSERT INTO @cu0300_tmp

      	SELECT landscape

      	   , code

      	   , start_date

      	   , end_date

      	   , spt_no

      	FROM

      	  hr_cu0300

      	WHERE

      	  landscape = @c_landscape

      	  and

      	  start_date <= @c_end_date

      	  and

      	  end_date >= @c_start_date

      	  and

      	  spt_no > 0

      	UPDATE @cu0300_tmp

      	SET

      	  start_date = @begda

      	WHERE

      	  start_date < @c_start_date

      	UPDATE @cu0300_tmp

      	SET

      	  end_date = @endda

      	WHERE

      	  end_date > @c_end_date

      	DELETE @cu0300

      	INSERT INTO @cu0300

      	SELECT cu0300.*

      	FROM

      	  @cu0300_tmp as cu0300

      	  inner join (SELECT landscape

      					   , code

      					   , min(start_date) as start_date

      					   , max(end_date) as end_date

      				  FROM

      					  @cu0300_tmp

      				  group by

      					  landscape

      					, code) a

      		  on cu0300.landscape = a.landscape and cu0300.code = a.code and cu0300.end_date = a.end_date

      DELETE @tbl_tr_dec

      DELETE @tbl_tr_dec_ori

      DELETE @tbl_tr_dec_tmp

      DELETE @tbl_tr_dec_ori_tmp

      DECLARE @cc_landscape VARCHAR(3)

      DECLARE @cc_code VARCHAR(4)

      DECLARE @cc_start_date INT

      DECLARE @cc_end_date INT

      DECLARE @cc_spt_no INT

      		INSERT INTO @tbl_tr_dec_tmp

      		SELECT tr301.landscape

      		   , tr301.employee_id

      		   , tr301.wage_type

      		   , case @is_encrypted

      			 when '0' then

      				 convert(decimal, tr301.amount)

      			 ELSE

      				 convert(decimal, dbo.SDE(tr301.amount, N'M!N0V@2010'))

      			 END

      		   , cu300.spt_no

      		   , tr301.run_period_month

      		FROM

      		  hr_tr0301_cum as tr301

      		  INNER JOIN @cu0300 as cu300

      			ON cu300.code = tr301.wage_type

      		WHERE

      		  tr301.landscape = @landscape

      		  and tr301.employee_id = @c_emp_id

      		  and tr301.pay_period_month = substring(convert(VARCHAR, @last_period_payroll), 5, 2)

      		  and tr301.pay_period_year = tr301.run_period_year

      		  and tr301.run_period_year = left(@last_period_payroll, 4)

      		--get the latest run data

      		INSERT INTO @tbl_tr_dec

      		SELECT tr_dec.*

      		FROM

      		  @tbl_tr_dec_tmp as tr_dec

      		  inner join (SELECT landscape

      						   , employee_id

      						   , wage_type

      						   , max(run_period_month) as run_period_month

      					  FROM

      						  @tbl_tr_dec_tmp

      					  group by

      						  landscape

      						, employee_id

      						, wage_type) a

      			  on tr_dec.landscape = a.landscape 

      				 and tr_dec.employee_id = a.employee_id 

      				 and tr_dec.wage_type = a.wage_type

      				 and tr_dec.run_period_month = a.run_period_month

      		INSERT INTO @tbl_tr_dec_ori_tmp

      		SELECT tr301.landscape

      		   , tr301.employee_id

      		   , tr301.wage_type

      		   , case @is_encrypted

      			 when '0' then

      				 convert(decimal, tr301.amount)

      			 ELSE

      				 convert(decimal, dbo.SDE(tr301.amount, N'M!N0V@2010'))

      			 END

      		   , cu300.spt_no

      		   , tr301.run_period_month

      		FROM

      		  hr_tr0301 as tr301

      		  INNER JOIN @cu0300 as cu300

      			ON cu300.code = tr301.wage_type

      		WHERE

      		  tr301.landscape = @landscape

      		  and tr301.employee_id = @c_emp_id

      		  and tr301.pay_period_month = substring(convert(VARCHAR, @last_period_payroll), 5, 2)

      		  and tr301.pay_period_year = tr301.run_period_year

      		  and tr301.run_period_year = left(@last_period_payroll, 4)

      		--get the latest run data

      		INSERT INTO @tbl_tr_dec_ori

      		SELECT tr_dec_ori.*

      		FROM

      		  @tbl_tr_dec_ori_tmp as tr_dec_ori

      		  inner join (SELECT landscape

      						   , employee_id

      						   , wage_type

      						   , max(run_period_month) as run_period_month

      					  FROM

      						  @tbl_tr_dec_ori_tmp

      					  group by

      						  landscape

      						, employee_id

      						, wage_type) a

      			  on tr_dec_ori.landscape = a.landscape 

      				 and tr_dec_ori.employee_id = a.employee_id 

      				 and tr_dec_ori.wage_type = a.wage_type

      				 and tr_dec_ori.run_period_month = a.run_period_month

      		IF (@masa_perolehan_1 > '1' OR @masa_perolehan_1 > '01')

      		BEGIN

      			SET @previous_period_payroll = CONVERT(int, @masa_perolehan_1) - 1

      			IF (@previous_period_payroll < 10)

      				SET @previous_period_payroll_varchar = '0' + CONVERT(varchar(1), @previous_period_payroll)

      			ELSE

      				SET @previous_period_payroll_varchar = CONVERT(varchar(2), @previous_period_payroll)

      			--get the latest pay period if previous period no pay period.

      			SET @previous_period_payroll0 = @previous_period_payroll

      			SELECT TOP(1) @previous_period_payroll0 = CONVERT(int, pay_period_month)

      			FROM dbo.hr_tr0300

      			WHERE landscape = @landscape

      			  and employee_id = @c_emp_id

      			  and pay_period_month <= @previous_period_payroll_varchar

      			  and pay_period_year = left(@last_period_payroll, 4)

      			  and run_period_year = left(@last_period_payroll, 4)

      			ORDER BY pay_period_month DESC

      			IF (@previous_period_payroll0 < 10)

      				SET @previous_period_payroll_varchar = '0' + CONVERT(varchar(1), @previous_period_payroll0)

      			ELSE

      				SET @previous_period_payroll_varchar = CONVERT(varchar(2), @previous_period_payroll0)

      			DELETE FROM @tbl_tr_prev_ori

      			DELETE FROM @tbl_tr_prev_cum

      			DELETE FROM @tbl_tr_prev_ori_tmp

      			DELETE FROM @tbl_tr_prev_cum_tmp

      			INSERT INTO @tbl_tr_prev_ori_tmp

      			SELECT tr301.landscape

      			   , tr301.employee_id

      			   , tr301.wage_type

      			   , case @is_encrypted

      				 when '0' then

      					 convert(decimal, tr301.amount)

      				 ELSE

      					 convert(decimal, dbo.SDE(tr301.amount, N'M!N0V@2010'))

      				 END

      			   , cu300.spt_no

      			   , tr301.run_period_month

      			FROM

      			  hr_tr0301 as tr301

      			  INNER JOIN @cu0300 as cu300

      				ON cu300.code = tr301.wage_type

      			WHERE

      			  tr301.landscape = @landscape

      			  and tr301.employee_id = @c_emp_id

      			  --and tr301.run_period_month = @previous_period_payroll_varchar

      			  and tr301.pay_period_month = @previous_period_payroll_varchar

      			  and tr301.pay_period_year = tr301.run_period_year

      			  and tr301.run_period_year = left(@last_period_payroll, 4)

      			--get the latest run data

      			INSERT INTO @tbl_tr_prev_ori

      			SELECT tr_prev_ori.*

      			FROM

      			  @tbl_tr_prev_ori_tmp as tr_prev_ori

      			  inner join (SELECT landscape

      							   , employee_id

      							   , wage_type

      							   , max(run_period_month) as run_period_month

      						  FROM

      							  @tbl_tr_prev_ori_tmp

      						  group by

      							  landscape

      							, employee_id

      							, wage_type) a

      				  on tr_prev_ori.landscape = a.landscape 

      					 and tr_prev_ori.employee_id = a.employee_id 

      					 and tr_prev_ori.wage_type = a.wage_type

      					 and tr_prev_ori.run_period_month = a.run_period_month

      			INSERT INTO @tbl_tr_prev_cum_tmp

      			SELECT tr301.landscape

      			   , tr301.employee_id

      			   , tr301.wage_type

      			   , case @is_encrypted

      				 when '0' then

      					 convert(decimal, tr301.amount)

      				 ELSE

      					 convert(decimal, dbo.SDE(tr301.amount, N'M!N0V@2010'))

      				 END

      			   , cu300.spt_no

      			   , tr301.run_period_month

      			FROM

      			  hr_tr0301_cum as tr301

      			  INNER JOIN @cu0300 as cu300

      				ON cu300.code = tr301.wage_type

      			WHERE

      			  tr301.landscape = @landscape

      			  and tr301.employee_id = @c_emp_id

      			  --and tr301.run_period_month = @previous_period_payroll_varchar

      			  and tr301.pay_period_month = @previous_period_payroll_varchar

      			  and tr301.pay_period_year = tr301.run_period_year

      			  and tr301.run_period_year = left(@last_period_payroll, 4)

      			--get the latest run data

      			INSERT INTO @tbl_tr_prev_cum

      			SELECT tr_prev_cum.*

      			FROM

      			  @tbl_tr_prev_cum_tmp as tr_prev_cum

      			  inner join (SELECT landscape

      							   , employee_id

      							   , wage_type

      							   , max(run_period_month) as run_period_month

      						  FROM

      							  @tbl_tr_prev_cum_tmp

      						  group by

      							  landscape

      							, employee_id

      							, wage_type) a

      				  on tr_prev_cum.landscape = a.landscape 

      					 and tr_prev_cum.employee_id = a.employee_id 

      					 and tr_prev_cum.wage_type = a.wage_type

      					 and tr_prev_cum.run_period_month = a.run_period_month	

      		END 

      	DECLARE @emp_status varchar(2), 

      		@emp_type varchar(2),

      		@movement_type varchar(2),

      		@movement_reason varchar(2),

      		@emp_type_payroll varchar(20),

      		@movement_reason_payroll varchar(20),

      		@is_pegawai_baru varchar(1)

      	DECLARE @death_mov_reason varchar(5)

      	SELECT TOP(1) @death_mov_reason = val1

      	FROM base_cust_parameter

      	WHERE landscape = @landscape

      		AND [param] = 'HR_ESPT_MOV_REASON_DEATH'

      		AND [start_date] <= @endda

      		AND end_date >= @endda

      	SELECT @emp_status = employee_status,

      		@emp_type = employee_type,

      		@movement_type = movement_type,

      		@movement_reason = reason,

      		@emp_type_payroll = ret.payroll_process,

      		@movement_reason_payroll = rmr.payroll_process

      	FROM hr_md_emp_md0002 md02

      		INNER JOIN base_cust_ref_emp_type ret

      			ON emp_type = employee_type

      		INNER JOIN base_cust_ref_mov_reason rmr

      			ON code = reason

      	WHERE emp_id = @c_emp_id

      		AND start_date <= @endda

      		AND end_date >= @endda

      	SET @is_pegawai_baru = '0'

      	IF @c_start_date > @begda

      	BEGIN

      		SELECT @is_pegawai_baru = CASE WHEN hiring_date >= @c_start_date THEN '1' ELSE '0' END

      		FROM hr_md_emp_md0015

      		WHERE emp_id = @c_emp_id

      	END

      		-- Setting flag status

      		--SELECT @emp_status, @masa_perolehan_1, @masa_perolehan_2, @emp_type_payroll, @movement_reason_payroll

      		IF @emp_status = '01' AND @masa_perolehan_1 = '1' AND @masa_perolehan_2 = '12' AND ISNULL(@emp_type_payroll,'') <> 'EXP'

      			SET @flag_status = '0'

      		ELSE IF @emp_status = '01' AND @masa_perolehan_1 = '1' AND @masa_perolehan_2 = '12' AND ISNULL(@emp_type_payroll,'') = 'EXP'

      			SET @flag_status = '3'

      		ELSE IF @emp_status = '01' AND @masa_perolehan_2 <> '12'

      			SET @flag_status = '1'

      		ELSE IF @is_pegawai_baru = '1'

      			SET @flag_status = '4'

      		ELSE IF @emp_status = '01' AND @masa_perolehan_2 <> '1' AND @masa_perolehan_2 = '12'

      			SET @flag_status = '5'

      		ELSE IF (@movement_type = '17' OR @emp_status <> '01') 

      		BEGIN

      			IF @movement_reason = '12'

      				SET @flag_status = '2'

      			ELSE IF @emp_type_payroll = 'EXP' OR @movement_reason = @death_mov_reason

      				SET @flag_status = '3'

      			ELSE IF @masa_perolehan_1 = '1' AND @masa_perolehan_2 = '12'

      				SET @flag_status = '0'

      			ELSE

      				SET @flag_status = '2'

      		END

      				  SELECT @a1 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 1

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a1 = @a1 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 1

      				  group by

      					  spt_no

      SET @a1=isnull(@a1,0)

      				  SELECT @a2 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 2

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a2 = @a2 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 2

      				  group by

      					  spt_no

      SET @a2=isnull(@a2,0)

      				  SELECT @a3 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 3

      				  group by

      					  spt_no

        				IF (@masa_perolehan_1 <> '1')

      					SELECT @a3 = @a3 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 3

      				  group by

      					  spt_no

      SET @a3=isnull(@a3,0)

      				  SELECT @a4 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 4

      				  group by

      					  spt_no

      SET @a4=isnull(@a4,0)

      				  SELECT @a5 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 5

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a5 = @a5 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 5

      				  group by

      					  spt_no

      SET @a5=isnull(@a5,0)

      				  SELECT @a6 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 6

      				  group by

      					  spt_no

      SET @a6=isnull(@a6,0)

      SET @a7=isnull(@a1+@a2+@a3+@a4+@a5+@a6,0)

      				  SELECT @a8 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 8

      				  group by

      					  spt_no

        				IF (@masa_perolehan_1 <> '1')

      					SELECT @a8 = @a8 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 8

      				  group by

      					  spt_no

      SET @a8=isnull(@a8,0)

      SET @a9=isnull(@a7+@a8,0)

      				  SELECT @a10 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 10

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a10 = @a10 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 10

      				  group by

      					  spt_no

      SET @a10=isnull(@a10,0)

      SET @tot_biaya_jabatan = 500000 * @jml_bulan_perolehan

      IF (@a10 > @tot_biaya_jabatan)

      BEGIN

      	--SET @a10 = @tot_biaya_jabatan ---// comment by Tri 2024130 IMFI

      	SET @a11 = 0

      END

      				  SELECT @a11 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 11

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a11 = @a11 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 11

      				  group by

      					  spt_no

      SET @a11=isnull(@a11,0)

      /*IF ((@a10 + @a11) > @tot_biaya_jabatan) ---// comment by Tri 2024130 IMFI

      BEGIN

      	SET @a11 = @tot_biaya_jabatan - @a10	

      END*/

      				  SELECT @a12 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec

      				  WHERE

      					  spt_no = 12

      				  group by

      					  spt_no

      				IF (@masa_perolehan_1 <> '1')

      					SELECT @a12 = @a12 - isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_prev_cum

      				  WHERE

      					  spt_no = 12

      				  group by

      					  spt_no

      SET @a12=isnull(@a12,0)

      SET @a13=isnull(@a10+@a11+@a12,0)

      SET @a14=isnull(@a9-@a13,0)

      				  SELECT @a15 = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec_ori

      				  WHERE

      					  spt_no = 15

      				  group by

      					  spt_no

      SET @a15=isnull(@a15,0)

      --select @a14, @a15

      SET @a16=isnull(@a14+@a15,0)

      -- spt_no 16 case khusus flag_status khusus

      				  SELECT @a16_flag = isnull(sum(amount), 0)

      				  FROM

      					  @tbl_tr_dec_ori

      				  WHERE

      					  spt_no = 16

      				  group by

      					  spt_no

      SET @a16_flag=isnull(@a16_flag,0)

      	IF @movement_reason = @death_mov_reason

      		OR ISNULL(@emp_type_payroll,'') = 'EXP'

      	BEGIN

      		SELECT @a16 = CASE WHEN ISNULL(@emp_type_payroll,'') = 'EXP' THEN @a16 ELSE @a16 * 12 / (CONVERT(int, @masa_perolehan_2) - CONVERT(int, @masa_perolehan_1) + 1) END

      	END

      	IF @flag_status = '1' OR @flag_status = '3'

      	BEGIN

      		SET @a16 = @a16_flag

      	END

      	SET @a18 = @a16 - @a17

      	if(right(RTRIM(CAST(@a18 as varchar(19))),3) <> '000')

      	BEGIN

      		DECLARE @Pembulatan1000 AS VARCHAR(19)

      		SET @Pembulatan1000 = CAST(@a18 as varchar(19)); 	

      		----SET @a18 = SUBSTRING(@Pembulatan1000,1,LEN(RTRIM(@Pembulatan1000)) - 3) + REPLICATE('0',3); 	

      		IF LEN(@Pembulatan1000) > 3

      			SET @a18 = SUBSTRING(@Pembulatan1000, 1, LEN(RTRIM(@Pembulatan1000)) - 3) + REPLICATE('0', 3)

      		ELSE

      			SET @a18 = @Pembulatan1000

      	END

      --ambil mt murni tanpa kondisi

      	 SELECT @a19murni = isnull(sum(amount), 0)

      	  FROM

      		  @tbl_tr_dec

      	  WHERE

      		  spt_no = 19

      	  group by

      		  spt_no

      --ambil att murni tanpa kondisi

      	 SELECT @a17_att = isnull(sum(amount), 0)

      	  FROM

      		  @tbl_tr_dec_ori

      	  WHERE

      		  spt_no = 99

      	  group by

      		  spt_no

      --cek status employee apakah sudah resign

      	  SELECT @a19 = isnull(sum(amount), 0)

      	  FROM

      		  @tbl_tr_dec

      	  WHERE

      		  spt_no = 19

      	  group by

      		  spt_no

      	  IF ISNULL(@emp_type_payroll,'') = 'EXP'

      	  BEGIN

      		  SELECT @a19b = isnull(sum(amount), 0)

      		  FROM

      			  @tbl_tr_prev_cum

      		  WHERE

      			  spt_no = 19

      		  group by

      			  spt_no

      			SET @a19 = @a19 * 12 / (CONVERT(int, @masa_perolehan_2) - CONVERT(int, @masa_perolehan_1) + 1)			

      	  END

      	ELSE IF (@movement_type = 17 OR @emp_status <> '01')

      	BEGIN

      	  IF @movement_reason = '12'

      	  BEGIN

      		SET @a19 = isnull(@a19murni,0)

      	  END

      	END

      	ELSE

      	BEGIN

      		IF ((@masa_perolehan_1 = '1') AND (@masa_perolehan_2 <> '12'))

      		BEGIN

      			SET @a19 = @a19 * 12 / CONVERT(int, @masa_perolehan_2)

      		END

      	END

      	SET @a19=isnull(@a19,0)

      				  SELECT @a20 = CASE WHEN CONVERT(int,@masa_perolehan_1) > 1 THEN isnull(sum(amount), 0) ELSE 0 END

      				  FROM

      					  @tbl_tr_dec_ori

      				  WHERE

      					  spt_no = 20

      				  group by

      					  spt_no

      			SET @a20=isnull(@a20,0)

      		IF (@emp_status = '01' AND NOT (@masa_perolehan_1 = '1' AND @masa_perolehan_2 = '12'))

      			OR ISNULL(@emp_type_payroll,'') = 'EXP'

      			OR @movement_reason = @death_mov_reason

      		BEGIN

      			SET @a21 = isnull(@a19murni-@a20,0)--@a2--@a19 

      		END

      		ELSE

      			SET @a21= isnull(@a19-@a20,0)

      		---- sementara di hard-code dulu, untuk handling case mks.

      		SET @a22 = @a21 + @a20

      		SET @a22a = 0

      		SET @a22b = @a21

      		SET @a23=isnull(@a21-@a22,0)

      		SET @a24=isnull(@a23,0)

      		SET @bln_a24 = substring(convert(VARCHAR,@c_end_date),5,2) +  left(convert(VARCHAR,@c_end_date),4) 

      		IF( @a21-@a22 = 0)

      		BEGIN

      		SET @flg_a24 = '0'

      		SET @bln_a24 = ''

      		END

      		IF( @a21-@a22 > 0)

      		BEGIN

      		SET @flg_a24 = '1'

      		END

      		IF( @a21-@a22 < 0)

      		BEGIN

      		SET @flg_a24 = '2'

      		END

      		SET @flg_a2 = '0'

      		--Pegawai yang dipindahkan

      		SET @flg_a2 = '1'

      				  delete @tbl_tr_dec

      					DECLARE @TglHiring VARCHAR(20) = ''

      					SELECT @TglHiring = hiring_date FROM hr_md_emp_md0015 where emp_id = @c_emp_id and LEFT(hiring_date, 4)= @year

      				  insert				  INTo					  @result

      				  SELECT @kode_form

      					   , @tahun_pajak

      					   , @pembetulan

      					   , CONVERT(char, RIGHT('0000000' + CONVERT(VARCHAR(7),@nomor_urut),7)) AS nomor_urut

      					   , @npwp_pegawai

      					   , @nama_pegawai

      					   , @alamat_pegawai

      					   , @jabatan_pegawai

      					   , @jenis_kelamin

      					   , @status_pegawai

      					   , @status_kawin

      					   , @flag_asing

      					   , @status_ptkp

      					   , @jumlah_tanggungan

      					   , CASE WHEN @TglHiring <> '' THEN CASE WHEN @TglHiring < @c_start_date THEN CONVERT(DECIMAL(18,0),SUBSTRING(@TglHiring, 5,2)) ELSE @masa_perolehan_1 END ELSE @masa_perolehan_1 END masa_perolehan_1

      					   --, @masa_perolehan_1

      					   , @masa_perolehan_2

      					   , @flag_status

      					   , @a1

      					   , @flg_a2

      					   , @a2

      					   , @a3

      					   , @a4

      					   , @a5

      					   , @a6

      					   , @a7

      					   , @a8

      					   , @a9

      					   , @a10

      					   , @a11

      					   , @a12

      					   , @a13

      					   , @a14

      					   , CASE WHEN @movement_reason = @death_mov_reason THEN @a16 - @a14 ELSE @a15 END --@a15 ----//edit tri 20241228 meninggal dr @a15

      					   , @a16

      					   , @a17

      					   , @a18

      					   , CASE WHEN @movement_reason = @death_mov_reason THEN @a19 ELSE @a17_att END --@a17_att --@a19 ----//edit tri 20241228 meninggal dr @a17_att

      					   , @a20

      					   , @a21

      					   , @a22

      					   , @a22a

      					   , @a22b

      					   , @a23

      					   , @a24

      					   , @flg_a24

      					   , @bln_a24

      					   , @ktp --@c_emp_id

      					   , @nama_pemotong

      					   , @npwp_pemotong

      					   , @kode_pajak

      					   , @company_name

      					   , @c_emp_id

      SET @nomor_urut = null

      SET @npwp_pegawai = null

      SET @nama_pegawai = null

      SET @alamat_pegawai = null

      SET @jabatan_pegawai = null

      SET @jenis_kelamin = null

      SET @status_pegawai = null

      SET @status_kawin = null

      SET @flag_asing = null

      SET @status_ptkp = null

      SET @jumlah_tanggungan = null

      SET @masa_perolehan_1 = null

      SET @masa_perolehan_2 = null

      SET @flag_status = null

      SET @a1 = null

      SET @flg_a2 = null

      SET @a2 = null

      SET @a3 = null

      SET @a4 = null

      SET @a5 = null

      SET @a6 = null

      SET @a7 = null

      SET @a8 = null

      SET @a9 = null

      SET @a10 = null

      SET @a11 = null

      SET @a12 = null

      SET @a13 = null

      SET @a14 = null

      SET @a15 = null

      SET @a16 = NULL

      SET @a16_flag = NULL

      SET @a17 = null

      SET @a18 = null

      SET @a19 = null

      SET @a20 = null

      SET @a21 = null

      SET @a22 = null

      SET @a22a = null

      SET @a22b = null

      SET @a23 = null

      SET @a24 = null

      SET @flg_a24 = null

      SET @bln_a24 = NULL 

      		fetch cur_employee INTo @c_landscape,

      							@c_emp_id,

      							@c_start_date,

      							@c_end_date   

         END

      close cur_employee

      deallocate cur_employee

      		update @result

      		SET

      			npwp_pegawai = '000000000000000'

      		WHERE

      			len(npwp_pegawai) < 15

      		update @result

      		SET

      			alamat_pegawai = '-'

      		WHERE

      			alamat_pegawai = '' or

      			alamat_pegawai is null

      		--SET @masa_pajak = (CONVERT(DECIMAL(22,0),@masa_perolehan_2))- (CONVERT(DECIMAL(22,0),@masa_perolehan_1)) + 1

      		SELECT 

      			masa_perolehan_2 AS masa_pajak

      			,tahun_pajak

      			,pembetulan 

      			--,'1.1-' + CONVERT(CHAR(2), RIGHT('00' + CONVERT(VARCHAR(2),masa_perolehan_2),2)) + '.' + RIGHT(@year,2) + '-' + nomor_urut AS no_bukti_potong 

      			,'1.1-' + CONVERT(CHAR(2), RIGHT('00' + CONVERT(VARCHAR(2),masa_perolehan_2),2)) + '.' + RIGHT(@year,2) + '-' + CONVERT(char, RIGHT('0000000' + CONVERT(VARCHAR(7),ROW_NUMBER() OVER(ORDER BY emp_id DESC)),7)) AS no_bukti_potong 

      			--,CASE WHEN @TglHiring < @c_start_date THEN CONVERT(DECIMAL(18,0),SUBSTRING(@TglHiring, 5,2)) ELSE masa_perolehan_1 END masa_perolehan_1

      			,masa_perolehan_1

      			,masa_perolehan_2 

      			,npwp_pegawai

      			,ktp AS NIK

      			,nama_pegawai 

      			,alamat_pegawai 

      			,CASE 

      				WHEN jenis_kelamin = 'W' OR jenis_kelamin = 'Wanita' OR jenis_kelamin = 'Female' OR jenis_kelamin = 'F' THEN 'F'

      				WHEN jenis_kelamin = 'P' OR jenis_kelamin = 'Pria' OR jenis_kelamin = 'Male' OR jenis_kelamin = 'M' THEN 'M'

      			 END AS jenis_kelamin

      			,CASE 

      				WHEN status_ptkp = 'TK' THEN 'TK'

      				WHEN status_ptkp = 'K0' OR status_ptkp = 'K1' OR status_ptkp = 'K2' OR status_ptkp = 'K3' THEN 'K' 

      				WHEN status_ptkp = 'T1' OR status_ptkp = 'T2' OR status_ptkp = 'T3' THEN 'HB'	

      			 END AS status_ptkp

      			,CASE WHEN status_ptkp = 'TK' THEN '0' ELSE RIGHT(status_ptkp, 1) END AS jumlah_tanggungan --,jumlah_tanggungan

      			,jabatan_pegawai AS nama_jabatan

      			,CASE WHEN flag_asing = 0 THEN 'N' ELSE 'Y' END AS wp_luar_negeri

      			,'' AS kode_negara

      			,kode_pajak AS kode_pajak

      			--,status_pegawai 

      			--,status_kawin 

      			--,flag_asing 	

      			--,flag_status  

      			,a1 AS a1

      			--,flg_a2 

      			,a2 AS a2

      			,a3 AS a3

      			,a4 AS a4

      			,a5 AS a5

      			,a6 AS a6

      			,a8 AS a7

      			,a9 AS a8

      			,a10 + a11 AS a9

      			,a12 AS a10

      			,a13 AS a11

      			,a14 AS a12

      			,a15 AS a13

      			,CASE WHEN a16 < 0 THEN 0 ELSE a16 END AS a14

      			,a17 AS a15

      			,CASE WHEN a18 < 0 THEN 0 ELSE a18 END AS a16

      			,a19 AS a17 --@a17_att AS a17 

      			,CASE WHEN a20 < 0 THEN 0 ELSE a20 END AS a18

      			,a21 AS a19

      			,a22 AS a20

      			,'' AS status_pindah

      			,npwp_pemotong AS npwp_pemotong

      			,company_name AS nama_pemotong --nama_pemotong AS nama_pemotong

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

      		FROM @result

      		--where ktp = '3173026111930001'

(3-3/4)

Project

General

Profile

CLIENT SUPPORT » IMFI Bugs

Bug #3948 » Rpt_HR_PY_eSPT_2014_202510101140.sql