USE [prise];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: Sidney
-- ALTER date: 2010-10-16
-- Description: 备抵－冲抵
-- =============================================
ALTER PROCEDURE [dbo].[ProceBDCD]
@al_orno INT, @as_ym VARCHAR(7), @as_ctym VARCHAR(7), @as_user VARCHAR(20), @ai_cttimes INT
AS
BEGIN
DECLARE @ldc_wcd NUMERIC(18, 4) --未冲抵金额
DECLARE @ldc_cd NUMERIC(18, 4)
DECLARE @ldc_prise_all NUMERIC(18, 4)
DECLARE @ldc_prise_ym NUMERIC(18, 4)
DECLARE @ldc_bd NUMERIC(18, 4) --是否设定保底多少才冲抵
DECLARE @as_info VARCHAR(500)
DECLARE @ls_emno VARCHAR(20)
DECLARE @li_assign INT
DECLARE @ldc_pre NUMERIC(18, 4)
DECLARE @ls_emno2 VARCHAR(20)
DECLARE @ai_sum INT
SET @as_info = ''
set @ldc_bd = 0 ---默认总提奖高于0就冲抵
IF isnull(@as_ctym, '') = ''
SET @as_ctym = @as_ym
--获取当月所有提奖
SELECT @ldc_prise_ym = sum(t_amnt)
FROM tbl_priseresult
WHERE t_orno = @al_orno and t_yymm = @as_ctym
if @ldc_prise_ym is null
set @ldc_prise_ym = 0
--获取所有提奖
SELECT @ldc_prise_all = sum(t_amnt)
FROM tbl_priseresult
WHERE t_orno = @al_orno
if @ldc_prise_all is null
set @ldc_prise_all = 0
--获取未备抵金额，当月备抵部分（提前兑付产生）不算
select @ldc_wcd = sum(t_amnt)
FROM tbl_bdcd
WHERE t_orno = @al_orno AND t_cttimes = @ai_cttimes and t_jzy <> @as_ctym
if @ldc_wcd is null
set @ldc_wcd = 0
SELECT @li_assign = count(*)
FROM tbl_assign
WHERE tbl_assign.fld_orno = @al_orno AND (@as_ym BETWEEN CONVERT(CHAR(7), isnull(fld_udat, '2010-01-01'), 121) AND CONVERT(CHAR(7), isnull(fld_ddat, '2055-01-01'), 121)
)
IF @li_assign IS NULL
SET @li_assign = 0
DECLARE
tbl_assign_Cursor CURSOR FOR
SELECT rtrim(ltrim(fld_emno)), isnull(fld_pre, 0)
FROM tbl_assign
WHERE tbl_assign.fld_orno = @al_orno AND (@as_ym BETWEEN CONVERT(CHAR(7), isnull(fld_udat, '2010-01-01'), 121) AND CONVERT(CHAR(7), isnull(fld_ddat, '2055-01-01'), 121)
)
if @ldc_prise_ym < 0
BEGIN
set @as_info = @as_info + '本月总提奖为负(' + str(@ldc_prise_ym, 8, 2) + '),小于零部分备抵！'
IF @li_assign > 0
BEGIN
OPEN tbl_assign_Cursor
---分公司部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, -@ldc_prise_ym * 0.15, @as_info + '分公司部分(15%)',
'', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.15, @ai_cttimes, getdate(),
'分公司备抵部分(15%)')
FETCH NEXT FROM tbl_assign_Cursor
INTO @ls_emno2, @ldc_pre
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, @ls_emno2, 9, -@ldc_prise_ym * 0.85 * @ldc_pre,
@as_info + '，个人部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')', '', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, @ls_emno2, @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.85 * @ldc_pre, @ai_cttimes, getdate(),
'个人备抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')')
FETCH NEXT FROM tbl_assign_Cursor
INTO @ls_emno2, @ldc_pre
END
CLOSE tbl_assign_Cursor
DEALLOCATE tbl_assign_Cursor
END
ELSE
BEGIN
-- insert into tbl_priseresult
---分公司部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, -@ldc_prise_ym * 0.15,
@as_info + ',分公司备抵部分(15%)', '', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.15, @ai_cttimes, getdate(),
'分公司备抵部分(15%)')
---个人部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, @ls_emno, 9, -@ldc_prise_ym * 0.85,
@as_info + '，个人备抵部分(85%)', '', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, @ls_emno, @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.85, @ai_cttimes, getdate(),
'个人备抵部分(85%)')
END
SELECT @as_info AS info, @ldc_prise_ym AS rtn, 9 priz
END
else --本月提奖大于零，检查是否需要冲抵
set @ldc_cd = 0
if @ldc_prise_ym > 0 and @ldc_wcd < 0 and @ldc_prise_all > @ldc_bd --有可能会要设定在总提奖大于保底佣金是才冲抵
begin --本月提奖大于零且有提奖未冲抵
if -@ldc_wcd > @ldc_prise_ym --待冲抵金额大于本月提奖,最多冲抵本月提奖
begin
if -@ldc_prise_ym + @ldc_prise_all > @ldc_bd --如果全部冲抵后大于保底，则全部冲抵,否则只冲抵大于保底部分
begin
set @ldc_cd = -@ldc_prise_ym
end
else
begin
set @ldc_cd = -(@ldc_prise_all - @ldc_bd)
end
end
else ---待冲抵金额小于本月提奖，最多冲抵待冲抵金额
begin
if @ldc_wcd + @ldc_prise_all > @ldc_bd --如果全部冲抵后大于保底，则全部冲抵,否则只冲抵大于保底部分
begin
set @ldc_cd = @ldc_wcd
end
else
begin
set @ldc_cd = -(@ldc_prise_all - @ldc_bd)
end
end
if @li_assign > 0 --有提奖分配
begin
OPEN tbl_assign_Cursor
---分公司部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, @ldc_cd * 0.15, @as_info + '分公司冲抵部分(15%)',
'', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.15, @ai_cttimes, getdate(),
'分公司冲抵部分(15%)')
FETCH NEXT FROM tbl_assign_Cursor
INTO @ls_emno2, @ldc_pre
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(),
8,
@al_orno,
@as_ctym,
@ls_emno2,
9,
@ldc_cd * 0.85 * @ldc_pre,
@as_info + '，个人冲抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')',
'',
@as_user,
@ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, @ls_emno2, @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.85 * @ldc_pre, @ai_cttimes, getdate(),
'个人冲抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')')
FETCH NEXT FROM tbl_assign_Cursor
INTO @ls_emno2, @ldc_pre
END
CLOSE tbl_assign_Cursor
DEALLOCATE tbl_assign_Cursor
end
else --无提奖分配
begin
---分公司部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, @ldc_cd * 0.15, @as_info + ',分公司冲抵部分(15%)',
'', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.15, @ai_cttimes, getdate(),
'分公司冲抵部分(15%)')
---个人部分
INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
t_cttimes )
VALUES (getdate(), 8, @al_orno, @as_ctym, @ls_emno, 9, @ldc_cd * 0.85, @as_info + '，个人冲抵部分(85%)',
'', @as_user, @ai_cttimes)
INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
VALUES (@al_orno, @ls_emno, @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.85, @ai_cttimes, getdate(),
'个人冲抵部分(85%)')
end
end
END
GO
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/75527/viewspace-721997/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/75527/viewspace-721997/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
