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'
