/****** 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