CLIENT SUPPORT » IMFI Bugs

??USE [MinovaHR_Indomobil_Prod]

GO

/****** Object:  StoredProcedure [dbo].[Rpt_IMFI_HR_PY_TaxComponent]    Script Date: 24/07/2024 09.52.38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PY_TaxComponent] 

	@landscape AS VARCHAR(3),

	@companycode AS VARCHAR(5),

	@paygroup AS VARCHAR(5),

	@payperiod AS VARCHAR(6),

	@tax_office VARCHAR(12),

	@emp_id VARCHAR(12)

AS

--DECLARE @landscape AS VARCHAR(3) 

--DECLARE @companycode AS VARCHAR(5) 

--DECLARE @paygroup AS VARCHAR(5) 

--DECLARE @payperiod AS VARCHAR(6) 

--DECLARE @tax_office VARCHAR(12)

--DECLARE @emp_id VARCHAR(12)

--SET @landscape = '100'

--SET @companycode = '1000' 

--SET @paygroup = '11'

--SET @payperiod = '201904'

--SET @tax_office = ''

--SET @emp_id = ''

DECLARE @companyname AS NVARCHAR(50)

DECLARE @companylogo AS VARBINARY(MAX)

DECLARE @payrollgroup AS NVARCHAR(50)

DECLARE @paymonth AS NVARCHAR(2)

DECLARE @payyear AS NVARCHAR(4)

DECLARE @begda AS NVARCHAR(8)

DECLARE @endda AS NVARCHAR(8)

DECLARE @enddate AS VARCHAR(8)

SET @paymonth = RIGHT(@payperiod,2)

SET @payyear = LEFT(@payperiod,4)

SET @begda = @payperiod + '01'

SET @endda = convert(NVARCHAR(8),dateadd(d,-1, dateadd(m, 1, @begda)), 112)

DECLARE @last_date AS varchar(12) 

DECLARE @last_year AS varchar(8)

DECLARE @last_month AS varchar(8)

SET @last_date = convert(VARCHAR(6), dateadd(d,0, dateadd(m,-1, @begda)), 112)

SET @last_year = LEFT(@last_date,4)

SET @last_month = RIGHT(@last_date,2)

-----//----- tabel-tabel internal

DECLARE @tbl_emp TABLE (

	emp_id varchar(8)

	,emp_name varchar(250)

	,[bank_id] [varchar](2) 

	,[bank_account] [varchar](50) 

	,[pay_grade] [varchar](8) 

	,[tax_status] [varchar](8) 

	,[npwp] [varchar](50) 

	,[jamsostek_type] [varchar](4) 

	,[jamsostek] [varchar](50) 

	,[jamsostek_level] [varchar](5)

	,[cost_center] [varchar](8) 

	,[employee_area] [varchar](4) 

	,[employee_office] [varchar](4) 

	,[employee_status] [varchar](8) 

	,[employee_type] [varchar](8) 

	,[employee_subtype] [varchar](8) 

	,[payroll_group] [varchar](8) 

	,[organization] [varchar](8) 

	,[position] [varchar](8) 

	,[job] [varchar](8) 

	,office_name varchar(250) 

	,pygroup varchar(10)

	,pygroup_desc varchar(250)

	,tax_desc varchar(250)

	,npwp_taxoffice varchar(50)

	)

DECLARE @tbl_py TABLE (

	emp_id varchar(8)

	,this_amount decimal(18,0)

	,wage_type VARCHAR(10)

	,wage_type_desc VARCHAR(100)

	,jum_emp DECIMAL(18,0)

	)

DECLARE @tbl_py_last TABLE (

	emp_id varchar(8)

	,last_amount decimal(18,0)

	,wage_type VARCHAR(10)

	,wage_type_desc VARCHAR(100)

	,jum_emp DECIMAL(18,0)

	)

DECLARE @tbl_tr_enc TABLE

	(landscape varchar (3),

	 employee_id varchar(8),

	 payPeriodMonth varchar(2),

	 payPeriodYear varchar(4),

	 runPeriodMonth varchar(2),

	 runPeriodYear varchar(4),

	 wage_type varchar(4),

	 amount varchar(250),

	 rate varchar(250),

	 flg_retro varchar(3),

	 flg_period VARCHAR(20),

	 wt_group VARCHAR(100))

DECLARE @tbl_tr_dec TABLE

	(landscape varchar (3),

	 employee_id varchar(8),

	 payPeriodMonth varchar(2),

	 payPeriodYear varchar(4),

	 runPeriodMonth varchar(2),

	 runPeriodYear varchar(4),

	 wage_type varchar(4),

	 amount bigint,

	 rate bigint,

	 flg_retro varchar(3),

	 flg_period VARCHAR(20),

	 wt_group VARCHAR(100))

DECLARE @tbl_tr_dec_sum TABLE

	(landscape varchar (3),

	 employee_id varchar(8),

	 payPeriodMonth varchar(2),

	 payPeriodYear varchar(4),

	 runPeriodMonth varchar(2),

	 runPeriodYear varchar(4),

	 wage_type varchar(4),

	 amount bigint,

	 rate bigint,

	 flg_retro varchar(1),