SQL Procedure示例程序01

/****** Object:  StoredProcedure [dbo].[SP_HRXZ_CACULATE_ITEMS]    Script Date: 02/01/2012 10:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








ALTER PROCEDURE [dbo].[SP_HRXZ_CACULATE_ITEMS] 
(
 @package_id		  nvarchar(50),
 @staffnumber         nvarchar(50)
)
AS

DECLARE @ErrorMessage                NVARCHAR(4000)
DECLARE @ErrorSeverity               int
DECLARE @ErrorState                  int

DECLARE @CursorStaff	             CURSOR
DECLARE @ptr_id                      varchar(50)
DECLARE @is_rework                   int
DECLARE @subject_code                nvarchar(50)
DECLARE @subject_id                  varchar(50)
DECLARE @caculate_begin_date         varchar(10)
DECLARE @caculate_end_date           varchar(10)
declare @id						     varchar(40)

DECLARE @item_AcutalBaseSalary           decimal(18,2)
DECLARE @item_JianBan                    decimal(18,2)
DECLARE @item_QueQin                     decimal(18,2)
DECLARE @item_AdjustSalary               decimal(18,2)
DECLARE @item_StdSalary                 decimal(18,2)
DECLARE @item_Extract                    decimal(18,2)
DECLARE @item_TaiPrize                   decimal(18,2)
DECLARE @item_TelAllowanceSalary         decimal(18,2)
DECLARE @item_PersonPensionSalary        decimal(18,2)
DECLARE @item_PersonMedicalSalary        decimal(18,2)
DECLARE @item_PersonShiYeSalary          decimal(18,2)
DECLARE @item_CompanyPensionSalary       decimal(18,2)
DECLARE @item_CompanyMedicalSalary       decimal(18,2)
DECLARE @item_CompanyShiYeSalary         decimal(18,2)
DECLARE @item_CompanyShengYuSalary       decimal(18,2)
DECLARE @item_CompanyGongShangSalary     decimal(18,2)
DECLARE @item_EcoCompensateSalary        decimal(18,2)
DECLARE @item_ManagementFeeSalary        decimal(18,2)

DECLARE @sum_YingFaSalary                decimal(18,2)
DECLARE @sum_SheBaoPerson                decimal(18,2)
DECLARE @sum_SheBaoCompany               decimal(18,2)
DECLARE @sum_TaxPerson                   decimal(18,2)
DECLARE @sum_YingShui                    decimal(18,2)
DECLARE @sum_ShiFa                       decimal(18,2)

--add by tony 2011-04-28
/***
S_POS, --门店补贴  J031
S_BONUS, --额外奖励 J032
S_OTHER,--其他项 J033
S_PERSON_HOUSE_FOUND, --公积金个人   J034 
S_PERSON_ADJUST,--调整个人部分  J035
S_COM_HOUSE_FOUND,--公积金公司  J036
S_COM_ADJUST,--调整公司部分J037
S_TAX,  --个调税  
S_NET_ADJUSTMENT  --税后补、扣款  J038
S_PERSON_FOUND_TOTAL  J039
S_COM_FOUND_TOTAL J040       
***/
DECLARE @ITEM_J031                decimal(18,2)
DECLARE @ITEM_J032               decimal(18,2)
DECLARE @ITEM_J033             decimal(18,2)
DECLARE @ITEM_J034              decimal(18,2)
DECLARE @ITEM_J035               decimal(18,2)
DECLARE @ITEM_J036                 decimal(18,2)
DECLARE @ITEM_J037                 decimal(18,2)
DECLARE @ITEM_J038              decimal(18,2)
DECLARE @ITEM_J041              decimal(18,2)

BEGIN
	--[STATE] = 1表示正在计算,计算完成后置0
	IF NOT EXISTS(SELECT 1 FROM HRXZ_PACKAGE_CACULATE_STATE WHERE PACKAGE_ID=@package_id)
		INSERT INTO HRXZ_PACKAGE_CACULATE_STATE(PACKAGE_ID,[STATE]) VALUES(@package_id,1)
	ELSE
		UPDATE HRXZ_PACKAGE_CACULATE_STATE SET [STATE]=1 WHERE PACKAGE_ID = @package_id

	PRINT '1'
	SELECT @caculate_begin_date=SALARYDATESTART,@caculate_end_date=SALARYDATEEND,@is_rework=ISREWORK FROM PAYROLLPACKAGE WHERE PACKAGEID=@package_id
	print @is_rework
	IF @is_rework=0
		BEGIN TRY
			PRINT '2'
			IF @staffnumber<>'' 
				SET @CursorStaff = CURSOR FAST_FORWARD READ_ONLY FOR	
					SELECT OID FROM HR_PROMOTER WHERE STAFFNUMBER=@staffnumber
			ELSE		
				SET @CursorStaff = CURSOR FAST_FORWARD READ_ONLY FOR	
					SELECT DISTINCT STAFFID FROM PAYPACKAGESTAFF WHERE PACKAGEID=@package_id
			
			OPEN @CursorStaff
			FETCH NEXT FROM @CursorStaff 
			INTO @ptr_id

			WHILE @@FETCH_STATUS = 0
			BEGIN
				--首先删除自动导入的资料，只删除薪资计算开始日期的，如果用户要在系统中自己手动加入数据，生效日期请不要选择计算开始日期
				DELETE FROM PAYPERSONALADDREDUSEITEM WHERE USERID=@ptr_id AND EFFECTTIME=@caculate_begin_date

				--循环计算每个单项科目（非公式科目）的值
				--J001	XZ_实际底薪
				SET @subject_code = 'J001'		
				SET @item_AcutalBaseSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_AcutalBaseSalary=dbo.GetActualBaseSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_AcutalBaseSalary,@caculate_begin_date				
				
				--J002	XZ_加班费
				SET @subject_code = 'J002'		
				SET @item_JianBan = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_JianBan=dbo.GetJiaBanSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_JianBan,@caculate_begin_date				
				
				--J003	XZ_缺勤扣款
				SET @subject_code = 'J003'		
				SET @item_QueQin = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_QueQin=dbo.GetQueQinSalary(@ptr_id,@caculate_begin_date,@caculate_end_date)		
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_QueQin,@caculate_begin_date				

				--J004	XZ_调整工资
				SET @subject_code = 'J004'		
				SET @item_AdjustSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_AdjustSalary=dbo.GetAdjustSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_AdjustSalary,@caculate_begin_date				
				
				--J005	XZ_标准底薪
				SET @subject_code = 'J005'		
				SET @item_StdSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_StdSalary=dbo.GetBaseSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_StdSalary,@caculate_begin_date				

				--插入计算提成和台数嘉奖每天的配置
				EXEC [SP_HRXZ_GetPrizeSetting] @ptr_id,@caculate_begin_date,@caculate_end_date,'1'
				EXEC [SP_HRXZ_GetPrizeSetting] @ptr_id,@caculate_begin_date,@caculate_end_date,'2'
				
				-- 新的提成计算方式 add by tony 
				-- 如果存在15分钟之内的数据，就不重新计算
				 if(not EXISTS( SELECT TOP 1 * FROM hrxz_ptr_prize_value  
						WHERE PACKAGE_ID=@package_id AND DATEDIFF(n, LAST_UPDATE_TIME, GETDATE())<15 ))
						BEGIN
							DELETE FROM hrxz_ptr_prize_value WHERE PACKAGE_ID=@package_id
							EXEC [SP_HRXZ_InitPtrPrize_SN]  @package_id, @caculate_begin_date, @caculate_end_date
							EXEC [SP_HRXZ_InitPtrPrize_GM]  @package_id, @caculate_begin_date, @caculate_end_date
						END
				
				
				--J006	XZ_提成奖金
				SET @subject_code = 'J006'		
				SET @item_Extract = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_Extract=dbo.GetPrizeSettingValue(@ptr_id,@caculate_begin_date,@caculate_end_date,'1') 
				-- add by tony 
				SELECT @item_Extract = @item_Extract + dbo.GetPrizeSettingValueNew(@package_id, @ptr_id, '1')
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_Extract,@caculate_begin_date				

				--J007	XZ_台数嘉奖
				SET @subject_code = 'J007'		
				SET @item_TaiPrize = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_TaiPrize=dbo.GetPrizeSettingValue(@ptr_id,@caculate_begin_date,@caculate_end_date,'2') 
				-- add by tony 
				SELECT @item_TaiPrize = @item_TaiPrize + dbo.GetPrizeSettingValueNew(@package_id, @ptr_id, '2')
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_TaiPrize,@caculate_begin_date				

				--J008	XZ_电话津贴
				SET @subject_code = 'J008'		
				SET @item_TelAllowanceSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_TelAllowanceSalary=dbo.GetTelAllowanceSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_TelAllowanceSalary,@caculate_begin_date				
			
				--J012	XZ_养老个人
				SET @subject_code = 'J012'		
				SET @item_PersonPensionSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_PersonPensionSalary=dbo.GetPersonPensionSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_PersonPensionSalary,@caculate_begin_date				
				
				--J013	XZ_医疗个人
				SET @subject_code = 'J013'		
				SET @item_PersonMedicalSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_PersonMedicalSalary=dbo.GetPersonMedicalSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_PersonMedicalSalary,@caculate_begin_date				

				--J014	XZ_失业个人
				SET @subject_code = 'J014'		
				SET @item_PersonShiYeSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_PersonShiYeSalary=dbo.GetPersonShiYeSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_PersonShiYeSalary,@caculate_begin_date				

--add by tony 2011-04-28===================
		        --XZ_门店补贴  J031
		        SET @subject_code = 'J031'		
				SET @ITEM_J031 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_POS', @ITEM_J031 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J031,@caculate_begin_date				

				--XZ_额外奖励  J032
		        SET @subject_code = 'J032'		
				SET @ITEM_J032 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_BONUS', @ITEM_J032 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J032,@caculate_begin_date	
				
				--XZ_其他项  J033
		        SET @subject_code = 'J033'		
				SET @ITEM_J033 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_OTHER', @ITEM_J033 OUTPUT
				PRINT 'S_OTHER'
				PRINT @subject_code
				PRINT @subject_id
				PRINT @ITEM_J033
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J033,@caculate_begin_date	
				
				--XZ_公积金个人  J034
		        SET @subject_code = 'J034'		
				SET @ITEM_J034 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_PERSON_HOUSE_FOUND', @ITEM_J034 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J034,@caculate_begin_date	
				
				--XZ_调整个人部分  J035
		        SET @subject_code = 'J035'		
				SET @ITEM_J035 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_PERSON_ADJUST', @ITEM_J035 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J035,@caculate_begin_date	
				
				--XZ_公积金公司  J036
		        SET @subject_code = 'J036'		
				SET @ITEM_J036 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_COM_HOUSE_FOUND', @ITEM_J036 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J036,@caculate_begin_date	
				
				--XZ_调整公司部分  J037
		        SET @subject_code = 'J037'		
				SET @ITEM_J037 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_COM_ADJUST', @ITEM_J037 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J037,@caculate_begin_date
				
				--XZ_税后补、扣款  J038
		        SET @subject_code = 'J038'		
				SET @ITEM_J038 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_NET_ADJUSTMENT', @ITEM_J038 OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J038,@caculate_begin_date
				
				--XZ_最低工资标准补偿  J041
		        SET @subject_code = 'J041'		
				SET @ITEM_J041 = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code				 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@ITEM_J041,@caculate_begin_date
--=========================================
				--J015	XZ_养老公司
				SET @subject_code = 'J015'		
				SET @item_CompanyPensionSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_CompanyPensionSalary=dbo.GetCompanyPensionSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_CompanyPensionSalary,@caculate_begin_date				
				
				--J016	XZ_医疗公司
				SET @subject_code = 'J016'		
				SET @item_CompanyMedicalSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_CompanyMedicalSalary=dbo.GetCompanyMedicalSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_CompanyMedicalSalary,@caculate_begin_date							

				--J017	XZ_失业公司
				SET @subject_code = 'J017'		
				SET @item_CompanyShiYeSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_CompanyShiYeSalary=dbo.GetCompanyShiYeSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_CompanyShiYeSalary,@caculate_begin_date				

				--J018	XZ_生育公司
				SET @subject_code = 'J018'		
				SET @item_CompanyShengYuSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_CompanyShengYuSalary=dbo.GetCompanyShengYuSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_CompanyShengYuSalary,@caculate_begin_date				

				--J019	XZ_工伤公司
				SET @subject_code = 'J019'		
				SET @item_CompanyGongShangSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_CompanyGongShangSalary=dbo.GetCompanyGongShangSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_CompanyGongShangSalary,@caculate_begin_date				
				
					
				--J020	XZ_经济补偿金
				SET @subject_code = 'J020'		
				SET @item_EcoCompensateSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_EcoCompensateSalary=dbo.GetEcoCompensateSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_EcoCompensateSalary,@caculate_begin_date				

				--J024	XZ_管理费
				SET @subject_code = 'J024'		
				SET @item_ManagementFeeSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code
				SELECT @item_ManagementFeeSalary=dbo.GetManagementFeeSalary(@ptr_id,@caculate_begin_date,@caculate_end_date) 
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@item_ManagementFeeSalary,@caculate_begin_date			

				--J009	XZ_应发工资(公式) =({XZ_实际底薪}+{XZ_提成奖金}+{XZ_台数嘉奖}+{XZ_电话津贴}+{XZ_加班费}-{XZ_缺勤扣款}+{XZ_调整工资})	
				SET @subject_code = 'J009'		
				SET @sum_YingFaSalary = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code						
				SET @sum_YingFaSalary=@item_AcutalBaseSalary + @item_Extract + @item_TaiPrize 
									  + @item_TelAllowanceSalary+@item_JianBan-@item_QueQin+@item_AdjustSalary
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_YingFaSalary,@caculate_begin_date		 	
				
				--J010	XZ_社保个人(公式) = ({XZ_养老个人}+{XZ_医疗个人}+{XZ_失业个人})	 + XZ_公积金个人	
				SET @subject_code = 'J010'		
				SET @sum_SheBaoPerson = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code		
				SET @sum_SheBaoPerson=@item_PersonPensionSalary+@item_PersonMedicalSalary+@item_PersonShiYeSalary + @ITEM_J034
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_SheBaoPerson,@caculate_begin_date	

				--J011	XZ_社保公司(公式) = ({XZ_养老公司}+{XZ_医疗公司}+{XZ_工伤公司}+{XZ_失业公司}+{XZ_生育公司})	+ XZ_公积金公司
				SET @subject_code = 'J011'		
				SET @sum_SheBaoCompany = 0 
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code		
				SET @sum_SheBaoCompany=(@item_CompanyPensionSalary+@item_CompanyMedicalSalary+@item_CompanyGongShangSalary+@item_CompanyShiYeSalary+@item_CompanyShengYuSalary)	 + @ITEM_J036
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_SheBaoCompany,@caculate_begin_date	

				--J022	XZ_应税总额（公式） = {XZ_应发工资}-{XZ_社保个人}
				SET @subject_code = 'J022'		
				SET @sum_YingShui = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code		
				SET @sum_YingShui=@sum_YingFaSalary - @sum_SheBaoPerson --+ 10000
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_YingShui,@caculate_begin_date

				--J021	XZ_个调税(通过系统设定公式) = ([计税函数_获取所得税税额({XZ_应税总额})]
				SET @subject_code = 'J021'		
				SET @sum_TaxPerson = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code		
				--SET @sum_TaxPerson=22
				--个调税 从上传的其他项中读取
				exec  dbo.[SP_HRXZ_GetPersonOtherSalary] @ptr_id,@caculate_begin_date,@caculate_end_date,'S_TAX', @sum_TaxPerson OUTPUT
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_TaxPerson,@caculate_begin_date	

				--J023	XZ_实发工资（公式）= {XZ_应发工资}-{XZ_个调税}-{XZ_社保个人}+{XZ_经济补偿金}
				SET @subject_code = 'J023'		
				SET @sum_ShiFa = 0
				SET @subject_id = ''
				set @id = newid()
				SELECT @subject_id=PAYROLLSUBJECTID FROM PAYPAYROLLSUBJECT WHERE SUBJECTCODE=@subject_code		
				--SET @sum_ShiFa=@sum_YingShui-@sum_TaxPerson-@sum_SheBaoPerson + @item_EcoCompensateSalary
				exec dbo.[SP_HRXZ_INSERT_ADDREDUSEITEM] @ptr_id,@id,@subject_id,@sum_ShiFa,@caculate_begin_date			

				FETCH NEXT FROM @CursorStaff 
						INTO @ptr_id
			END

			CLOSE @CursorStaff
			DEALLOCATE @CursorStaff

			UPDATE HRXZ_PACKAGE_CACULATE_STATE SET [STATE]=0 WHERE PACKAGE_ID = @package_id
	END TRY
	BEGIN CATCH
		UPDATE HRXZ_PACKAGE_CACULATE_STATE SET [STATE]=0 WHERE PACKAGE_ID = @package_id
		--THROW ERROR
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   )
	END CATCH
END