CLIENT SUPPORT » IMFI Bugs

??ALTER procedure [dbo].[SPTBulanan_Bupot_Resign_Coretax]

	@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

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

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

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

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

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

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

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

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

-- Extract year and month from @nowperiod

DECLARE @nowmonth varchar (2) = dbo.fn_formatdatetime(GETDATE(),'mm')

DECLARE @nowperiod varchar (6) = dbo.fn_formatdatetime(GETDATE(),'yyyymm')

-- Calculate the next month

DECLARE @years INT = CAST(SUBSTRING(@nowperiod, 1, 4) AS INT);

DECLARE @months INT = CAST(SUBSTRING(@nowperiod, 5, 2) AS INT);

SET @months = @months + 1;

DECLARE @next_month VARCHAR(6) = CAST(@year AS VARCHAR(4)) + RIGHT('0' + CAST(@months AS VARCHAR(2)), 2);

-- Temporary tables for final SELECT

select distinct emp_subarea into #emp_subarea from base_cust_ref_emp_office with (nolock) where tax_office=@tax_office

select distinct emp_id,start_date,end_date,npwp,npwp_date,tax_status into #md3 from hr_md_emp_md0003 with (nolock) where end_date='99991231'

select distinct emp_id,start_date,end_date,full_name,ktp,gender into #md1 from hr_md_emp_md0001 with (nolock) where end_date='99991231'

select distinct emp_id,start_date,end_date,address_type,street into #md6 from hr_md_emp_md0006 with (nolock) where end_date='99991231' and address_type='01'

select distinct emp_id,hiring_date into #md15 from hr_md_emp_md0015 with (nolock)

-- Result

select distinct

	emp_id

	,start_date

	,end_date

	,position

	,employee_type

	,employee_status

	,employee_office

	,movement_type

into #result

from hr_md_emp_md0002 with (nolock) 

where 

	 @year+'1231' between start_date and end_date

	and (payroll_group=@payroll_group or @payroll_group='')

	 and (emp_id>=@emp_id_beg or @emp_id_beg='')

	and (emp_id<=@emp_id_end or @emp_id_end='')

	--and (employee_office in (select emp_subarea from #emp_subarea) or @tax_office='')

	and ((employee_status in ('03','04') and left(start_date,4)=@year) or (employee_status in ('01','02')))

	----select * from  #result

-- Result_

select distinct 

r.emp_id

,r.employee_status

,case 

	when left(min(md2.start_date),4)=@year then right(left(min(md2.start_date),6),2) -- Employee Aktif

	when  left(min(md2.start_date),4)=@year AND r.employee_status IN ('03', '04') THEN

	CASE

			WHEN RIGHT(min(md2.start_date), 1) = '1' THEN 

				-- Subtract 1 from the month, ensuring it doesn't go below 01

				RIGHT(CONCAT('0', 

					CASE 

						WHEN CAST(RIGHT(LEFT(min(md2.start_date), 6), 2) AS INT) - 1 < 1 

						THEN 1 

						ELSE CAST(RIGHT(LEFT(min(md2.start_date), 6), 2) AS INT) - 1 

					END

				), 2) -- Employee Resign di awal bulan

			ELSE RIGHT(LEFT(min(md2.start_date), 6), 2) -- Employee Resign di tengah bulan

		END

	else '01'

end as start_date

,CASE 

	WHEN LEFT(MAX(md2.end_date), 4) = @year THEN RIGHT(LEFT(MAX(md2.end_date), 6), 2) -- Employee Aktif

	WHEN LEFT(MAX(md2.start_date), 4) = @year AND r.employee_status IN ('03', '04') THEN 

		CASE

			WHEN RIGHT(MAX(md2.start_date), 1) = '1' THEN 

				-- Subtract 1 from the month, ensuring it doesn't go below 01

				RIGHT(CONCAT('0', 

					CASE 

						WHEN CAST(RIGHT(LEFT(MAX(md2.start_date), 6), 2) AS INT) - 1 < 1 

						THEN 1 

						ELSE CAST(RIGHT(LEFT(MAX(md2.start_date), 6), 2) AS INT) - 1 

					END

				), 2) -- Employee Resign di awal bulan

			ELSE RIGHT(LEFT(MAX(md2.start_date), 6), 2) -- Employee Resign di tengah bulan

		END

	ELSE '12'

END as end_date

,office.tax_office AS employee_office

,r.movement_type

into #result_

from #result r

left join hr_md_emp_md0002 md2 on r.emp_id=md2.emp_id

LEFT JOIN base_cust_ref_emp_office as office

	ON md2.employee_office = office.emp_subarea

where 

@year between left(md2.start_date,4) and left(md2.end_date,4)

group by r.emp_id,office.tax_office,r.employee_status,r.movement_type

--	--select * from #result_ where emp_id='20231441'

--Result_2

SELECT 

    emp_id,

    start_date,

    end_date,

	employee_status,

    employee_office,

	CASE 

		WHEN movement_type='54' THEN 'Annualized'