TJ_SellPrize_20120101
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[TJ_SellPrize_20120101]
@al_orno INT, --订单号
@as_ym CHAR(7), @as_ctym CHAR(7), --重提日期
@ai_cttimes INT, @al_return INT, --
@as_info VARCHAR(500) OUT --返回信息
AS
BEGIN
DECLARE @li_have_row INT
DECLARE @li_ct INT
DECLARE @ls_gs VARCHAR(1000)
DECLARE
@ll_yt INT,
@li_flag1 INT
SET @li_flag1 = 0
DECLARE @al_prize NUMERIC(18, 4)
DECLARE @al_prize_manager NUMERIC(18, 4)
DECLARE @ldb_all_return NUMERIC(18, 4)
DECLARE @ls_gs_manager VARCHAR(1000)
DECLARE @ls_error VARCHAR(1000)
DECLARE @ld_month_end DATETIME
DECLARE
@ldb_PrizeDiffer NUMERIC(18, 4),
@ldb_info_rate NUMERIC(18, 4),
@ldb_bc_rate NUMERIC(18, 4)
DECLARE @gs_userName VARCHAR(50)
DECLARE
@ldt_ValDate DATETIME,
@ldt_BackDate DATETIME
--@ldts_assign.getitemstring(ll,'fld_emno')
--@ldts_assign.getitemnumber(ll,'fld_corp')
--@ldts_assign.getitemnumber(ll,'fld_province')
--@ldts_assign.getitemnumber(ll,'fld_company')
--@ldts_assign.getitemnumber(ll,'fld_pre')
DECLARE
tbl_assign_Cursor CURSOR FOR
SELECT fld_emno, fld_pre, fld_province, fld_company, fld_pre
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 ISNULL(@as_ctym, '') = '' --重提日期为空为正常TJ
BEGIN
SET @as_ctym = @as_ym
SET @li_ct = 0
END
ELSE
--为重提
BEGIN
SET @ls_gs = @ls_gs + '<重提:' + @as_ym + '>'
SET @li_ct = 1
END
SET @ls_gs = '(tj20120101)'
SET @as_info = '(tj20120101)'
DECLARE @ls_ProdType VARCHAR(1000)
DECLARE @ls_ErrType VARCHAR(1000)
DECLARE @ls_PrizeDate VARCHAR(10)
DECLARE @ls_errorType VARCHAR(10)
DECLARE @ld_temp VARCHAR(10)
--清除TJ记录
--获取TJ月最后一天的日期：因为TJ是针对某一个月，那对于某些
--需要时间期限的判断，以TJ月最后一天为期限。
SET @ls_PrizeDate = @as_ym
SET @ld_temp = CONVERT(DATETIME, @as_ym + '-01', 120)
--set @ld_month_end=CONVERT(datetime, (date(year(ld_temp)+sign(month(@ld_temp) -12)+1,mod(month(@ld_temp)+1,13)+abs(sign(mod(month(@ld_temp)+1,13)) -1),1),)
--清除该订单该奖项该月TJ结果表中的计算结果
IF @li_ct = 0 --重提的已经在重提前一次性删除所有TJ月的TJ结果，不需要再删除 ？？isnull(t_cttimes,0)=@ai_cttimes该条件是否需要？
BEGIN
DELETE FROM tbl_priseresult
WHERE t_orno = @al_orno AND (t_priz = 3 OR t_priz = 10) AND t_yymm = @as_ym AND ISNULL(t_cttimes, 0) = @ai_cttimes
END
--获取订单信息Begin
DECLARE @li_zfz INT
DECLARE
@li_zzj INT,
@li_xxj INT,
@li_brdq INT,
@li_zzzj INT
DECLARE
@li_080901 INT,
@li_xlh INT,
@li_lsl INT,
@li_aj4 INT
DECLARE @li_070516 INT
DECLARE @ldb_dkr NUMERIC(18, 4)
DECLARE
@li_mort_years INT,
@li_mort_per INT,
@li_416 INT,
@ld_getdate DATETIME,
@li_hg INT,
@li_qk INT
DECLARE
@li_yj INT,
@li_PayFlag INT,
@li_exch INT,
@li_LawFlag INT,
@li_zzz INT,
@ls_zzz CHAR(7),
@ldb_OrderSum NUMERIC(18, 4)
DECLARE
@ls_OrderLevel CHAR(5),
@li_BackFlag INT,
@li_CustomType INT,
@ldt_SendDate DATETIME,
@li_CompCode INT
DECLARE
@ls_CrewId CHAR(20),
@ll_company INT,
@ll_province INT,
@li_orderFlag INT,
@ll_corp INT,
@li_jxs INT,
@ldb_yxdb INT,
@ls_zfz CHAR(7)
DECLARE
@ldb_Accuracy NUMERIC(18, 4),
@li_jsb INT,
@li_yjhx INT,
@ldb_zs NUMERIC(18, 4),
@ldb_yhq NUMERIC(18, 4),
@ldb_nopp NUMERIC(18, 4),
@ldb_xzjbc NUMERIC(18, 4),
@li_ajqzsk INT,
@li_sx INT
SELECT @li_zfz = ISNULL(t_cl, 0),
@li_zzj = ISNULL(t_zzj, 0),
@li_xxj = ISNULL(t_xxj, 0),
@li_brdq = ISNULL(t_brdq, 0),
@li_zzzj = ISNULL(t_zzzj, 0),
@li_080901 = DATEDIFF(DAY, '2008-08-31', t_sdat),
@li_xlh = ISNULL(t_xlh, 0),
@li_lsl = ISNULL(t_lls, 0),
@li_aj4 = ISNULL(t_aj4, 0),
@ldb_dkr = ISNULL(t_dkr, 0),
@li_070516 = DATEDIFF(DAY, '2007-05-15', t_sdat),
@li_mort_per = t_mort_years,
@li_mort_per = t_mort_per,
@li_416 = DATEDIFF(DAY, '2007-04-12', t_sdat),
@ld_getdate = GETDATE(),
@li_hg = ISNULL(t_rtn, 0),
@li_qk = ISNULL(t_qk, 0),
@li_yj = ISNULL(t_yj, 0),
@li_PayFlag = ISNULL(t_pamd, 0),
@li_exch = ISNULL(t_exch, 0),
@li_LawFlag = ISNULL(t_cl, 0),
@li_zzz = ISNULL(t_zzj, 0),
@ls_zzz = ISNULL(CONVERT(CHAR(7), t_zzsj, 121), '2020-01'),
@ldb_OrderSum = isnull(t_amnt, 0),
@ls_OrderLevel = ISNULL(t_grad, 'C'),
@li_BackFlag = ISNULL(t_rtnd, 0),
@li_CustomType = ISNULL(t_cutp, 0),
@ls_ProdType = t_item,
@ldt_SendDate = t_sdat,
@ls_CrewId = t_emno,
@li_orderFlag = t_flag,
@ll_corp = t_org,
@li_jxs = ISNULL(t_jxs, 0),
@ldb_yxdb = ISNULL(t_yxdb, 0),
@ls_zfz = ISNULL(CONVERT(CHAR(7), t_cldat, 121), '2050-01'),
@ldb_Accuracy = ISNULL(t_accuracy, 0),
@li_jsb = ISNULL(t_jsb, 0),
@li_yjhx = ISNULL(t_yjhx, 0),
@ldb_zs = ISNULL(t_prst, 0),
@ldb_yhq = ISNULL(t_yhq, 0),
@ldb_nopp = ISNULL(t_nopp, 0),
@ldb_xzjbc = ISNULL(t_xzjbc, 0),
@li_ajqzsk = ISNULL(t_ajqzsk, 0),
@li_sx = ISNULL(t_ppas, 0)
FROM tbl_sls
WHERE t_orno = @al_orno
--获取订单信息END
--订单信息处理BEGIN
--如果转法制了
IF @li_LawFlag = 1
BEGIN
--如果转法日期大于提奖日期 转法标志从新设置为0
IF @ls_zfz > @as_ym
SET @li_LawFlag = 0
END
IF @li_zzz = 1
BEGIN
--如果转专日期大于提奖日期 转专标志从新设置为0
IF @ls_zzz > @as_ym
SET @li_zzz = 0
END
IF @li_PayFlag = 6
SET @li_qk = 1 --根据付未类型确定是否为全款
--订单信息处理END
--select @ldb_plan_sum=sum(t_amnt) from tbl_backorder where t_orno=@al_orno
IF @li_xlh = 1 --销售新利恒产品，不计销售奖
BEGIN
SET @as_info = '销售新利恒产品，不计销售奖!'
SELECT @as_info AS info, 0 AS rtn, 0 priz
RETURN 0
END
IF @li_LawFlag = 1
BEGIN
SET @as_info = @as_info + '订单转法制，不提销售奖'
--将错误信息写入系统日志表中
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, 0 AS rtn, 0 priz
RETURN 0
END
--转法制
IF @li_zzz = 1
BEGIN
SET @as_info = @as_info + '订单转专制，不提销售奖!'
--将错误信息写入系统日志表中
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, 0 AS rtn, 0 priz
RETURN 0
END
--换物
DECLARE @ldb_exchPara NUMERIC(18, 4)
IF @li_exch = 1
BEGIN
SET @ldb_exchPara = 0.8
END
ELSE
BEGIN
SET @ldb_exchPara = 1
END
DECLARE
@ldb_info NUMERIC(18, 4),
@ldb_sale_per NUMERIC(18, 4),
@ldb_BasicPrize NUMERIC(18, 4),
@ldb_LowPrize NUMERIC(18, 4)
DECLARE
@ldb_LevelA NUMERIC(18, 4),
@ldb_LevelA1 NUMERIC(18, 4),
@ldb_LevelB NUMERIC(18, 4),
@ldb_LevelC NUMERIC(18, 4)
DECLARE
@ldb_LevelC1 NUMERIC(18, 4),
@ldb_LevelD NUMERIC(18, 4),
@ldb_a_per NUMERIC(18, 4),
@ldb_b_per NUMERIC(18, 4)
DECLARE
@ldb_c_per NUMERIC(18, 4),
@ldb_d_per NUMERIC(18, 4),
@ls_type CHAR(20),
@ldb_sale_a NUMERIC(18, 4)
DECLARE
@ldb_sale_b NUMERIC(18, 4),
@ldb_fsyzjxs NUMERIC(18, 4)
----获取提奖参数
SET @li_have_row = 0
SELECT TOP 1
@li_have_row = 1,
@ldb_info = ISNULL(t_info, 0),
@ldb_sale_per = ISNULL(t_jbc_xsjbl, 0.008),
@ldb_BasicPrize = ISNULL(t_sale, 0),
@ldb_LowPrize = ISNULL(t_lpri, 0),
@ldb_LevelA = ISNULL(t_a, 0),
@ldb_LevelA1 = ISNULL(t_a1, 0),
@ldb_LevelB = ISNULL(t_b, 0),
@ldb_LevelC = ISNULL(t_c, 0),
@ldb_LevelC1 = ISNULL(t_c1, 0),
@ldb_LevelD = ISNULL(t_d, 0),
@ldb_a_per = ISNULL(t_a_per, 0),
@ldb_b_per = ISNULL(t_b_per, 0),
@ldb_c_per = ISNULL(t_c_per, 0),
@ldb_d_per = ISNULL(t_d_per, 0),
@ls_type = t_type,
@ldb_sale_a = ISNULL(t_sale_a, 0),
@ldb_sale_b = ISNULL(t_sale_b, 0),
@ldb_fsyzjxs = ISNULL(t_fsyzjxs, 0)
FROM tbl_proprize
WHERE t_item = @ls_ProdType AND t_udat <= @ldt_SendDate
ORDER BY t_udat DESC;
IF @li_have_row = 0
BEGIN
SET @as_info = @as_info + '获取提奖参数失败!'
--将错误信息写入系统日志表中
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('错误', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, -1 AS rtn, 0 priz
RETURN 0
END
DECLARE @ldb_ppri NUMERIC(18, 4)
----获取提奖参数
SET @li_have_row = 0
SET @ldb_ppri = 0
SELECT TOP 1
@ldb_ppri = t_ppri,
@li_have_row = 1
FROM tbl_proprice
WHERE t_item = @ls_ProdType AND t_udat <= @ldt_SendDate
ORDER BY t_udat DESC
IF @li_have_row = 0
BEGIN
SET @as_info = @as_info + '获取提奖价格参数失败!'
--将错误信息写入系统日志表中
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('错误', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, -1 AS rtn, 0 priz
RETURN 0
END
--取得所有回款计划的总金额，该金额做为实际的合同金额
DECLARE @ldb_plan_sum NUMERIC(18, 4)
SELECT @ldb_plan_sum = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno
IF ISNULL(@ldb_plan_sum, 0) <= 0
BEGIN
SET @as_info = @as_info + '回款计划为零!'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('数据错误', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, -1 AS rtn, 0 priz
RETURN
END
DECLARE @ldb_amnt NUMERIC(18, 4)
DECLARE @ldb_Back NUMERIC(18, 4)
--将回款计划做为总的合同金额
SET @ldb_ordersum = @ldb_plan_sum
SET @ldb_amnt = @ldb_ordersum
--从回款信息表中取得截止TJ月的实际回款金额
SELECT @ldb_Back = ISNULL(SUM(t_rtmn), 0)
FROM tbl_retamn
WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym AND isnull(t_cash, 0) = 1
IF @ldb_back IS NULL
SET @ldb_back = 0
-----
DECLARE @ldb_lpri NUMERIC(18, 4)
IF isnull(@ldb_fsyzjxs, 0) = 0
SET @ldb_fsyzjxs = 0.5 --搅拌站非三一主机系数设定为0.5 2011
SET @ls_type = RTRIM(LTRIM(@ls_type))
SET @ldb_lpri = @ldb_LowPrize --保底佣金
--2008-06-17 力士乐主油泵保底佣金减半
IF @li_lsl = 1
BEGIN
SET @ldb_lpri = @ldb_lpri / 2
SET @ls_gs = @ls_gs + ',力士乐主油泵。'
END
--2009.01.01换房换物抵款合同，保底佣金设为0
IF @li_exch = 1
SET @ldb_lpri = 0
DECLARE @ls_sdat CHAR(10)
DECLARE @ldb_zk NUMERIC(18, 4)
DECLARE @ldb_cjj NUMERIC(18, 4)
DECLARE @ldb_yjpara NUMERIC(18, 4)
SELECT @ls_sdat = CONVERT(CHAR(10), @ldt_senddate, 121)
IF @li_yj = 1 -- --优价
BEGIN
SET @ldb_cjj = @ldb_amnt - (@ldb_zs + @ldb_yhq) * 0.5 - @ldb_zk - @ldb_xzjbc --计算出实际价格(可能是多台的总价) add by wangq11 2010-07-21 ,2010-08-07 增加优价机赠送配件*0.5
SET @ldb_yjpara = @ldb_cjj / @ldb_ppri
SET @ls_gs = @ls_gs + '优价机,合同金额(' + STR(@ldb_cjj) + ')/保护价(' + STR(@ldb_ppri) + ')=' + STR(
@ldb_cjj / @ldb_ppri) + ','
END
ELSE
BEGIN
SET @ldb_yjpara = 1
END
--地区系数 2011 直接取自订单
--地区系数
DECLARE @ldb_AreaPara NUMERIC(18, 4)
IF @li_brdq = 1
SET @ldb_AreaPara = 1.2
ELSE
SET @ldb_AreaPara = 1
--取得是否已经提过销售奖的标志
DECLARE @ll_sale_flag INT
DECLARE @ls_have_ym CHAR(7)
DECLARE @sale_count_sjc NUMERIC(18, 4) --销售奖已提比率
SET @ll_sale_flag = 0
SET @ls_have_ym = ''
SELECT TOP 1
@ll_sale_flag = t_orno,
@ls_have_ym = t_yymm
FROM tbl_priseresult
WHERE t_priz = 3 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
IF @ll_sale_flag IS NULL
SET @ll_sale_flag = 0
SELECT @sale_count_sjc = SUM(ISNULL(t_ratio, 0))
FROM tbl_priseresult
WHERE t_priz = 3 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;
IF @sale_count_sjc IS NULL
SET @sale_count_sjc = 0
IF @sale_count_sjc >= 1
BEGIN
SET @as_info = @as_info + '销售奖已于' + @ls_have_ym + '被提取!'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 2, @as_info)
SELECT @as_info AS info, 0 AS rtn, 0 priz
RETURN
END
--计算该定单所提取的所有奖项
DECLARE @ldb_PrizeSum NUMERIC(18, 4)
DECLARE @ldb_cjsf NUMERIC(18, 4)
DECLARE @ldb_qkbl NUMERIC(18, 4)
DECLARE @lb_zw BIT --占位变量，在需要有个语句的地方用set @lb_zw=1
--declare @ldb_BasicPrize numeric(18,4)
SELECT @ldb_PrizeSum = SUM(t_amnt)
FROM tbl_priseresult
WHERE t_orno = @al_orno AND t_priz <> 1 AND t_crep <> 1000000
IF @ldb_PrizeSum IS NULL
SET @ldb_PrizeSum = 0
------根据产品类型进行不同的处理－－－－－－－－－－－－－－－－－－－－
IF @ls_type IN ('泵车', '拖泵', '车载泵', '布料机', '布料杆')
BEGIN
--set @lb_zw=1 --语法占位
SET @ldb_cjsf = 0.5
SET @ldb_qkbl = 0.9
--预提S
IF @ll_yt = 0
begin
IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
--DATEDIFF
begin
IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
SET @as_info = @as_info + '发货后没有超过30天,不予TJ'
-- set @ls_errorType='一般提示'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 3, @as_info)
SELECT @as_info AS info, 0 AS rtn, 3 priz
RETURN 0
end
end
--如果是经销商销售的,不提销售奖
IF @li_jxs = 1
--set @ls_ErrType='一般提示'
begin
SET @as_info = @as_info + '二级经销商销售，不提销售奖!'
--set @as_info=@as_info+@ls_ErrType+':'+@ls_error
--将错误信息写入系统日志表中
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 2, @ls_error)
SELECT @as_info AS info, 0 AS rtn, 3 priz
RETURN 0
end
END
ELSE
IF @ls_type IN ('搅拌车')
BEGIN
SET @lb_zw = 1 --语法占位
--2008年开始按销售价的百分比提取
------2012-5-2
IF @ldb_sale_per < 0 OR @ldb_sale_per IS NULL
SET @ldb_sale_per = 0.008
IF @li_zzzj = 1
--三一底盘搅拌车
-- begin
IF @li_PayFlag = 1 OR @li_PayFlag = 5
SET @ldb_sale_per = 0.012
ELSE
SET @ldb_sale_per = 0.015
-- end
ELSE
IF @li_PayFlag = 1 OR @li_PayFlag = 5
SET @ldb_sale_per = 0.008
ELSE
SET @ldb_sale_per = 0.0065
SET @ldb_BasicPrize = @ldb_ordersum * @ldb_sale_per
SET @ls_gs = @ls_gs + ',销售奖基数(' + STR(@ldb_BasicPrize) + ')=合同价(' + STR(@ldb_ordersum) +
')×比例(' + STR(@ldb_sale_per) + ')'
SET @ldb_qkbl = 0.95
--预提S
IF @ll_yt = 0
IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
SET @as_info = '发货后没有超过30天,不予TJ'
SET @ls_errorType = '一般提示'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_errorType, @al_orno, @as_ym, 3, @as_info);
RETURN 0
--预提E
END
ELSE
IF @ls_type IN ('混凝土搅拌站')
BEGIN
SET @ldb_cjsf = 0.5
SET @lb_zw = 1 --语法占位
IF @ldb_ordersum >= @ldb_ppri
BEGIN
SET @ldb_BasicPrize = @ldb_sale_a
SET @ls_gs = @ls_gs + '高于保护价(' + STR(@ldb_ppri) + '),'
END
ELSE
BEGIN
SET @ldb_BasicPrize = @ldb_sale_b
SET @ls_gs = @ls_gs + '低于保护价(' + STR(@ldb_ppri) + '),'
END
IF @li_zzzj = 1
BEGIN
SET @ldb_BasicPrize = @ldb_BasicPrize
SET @ls_gs = @ls_gs + '三一主机,销售奖标准(' + STR(@ldb_BasicPrize) + ')'
END
ELSE
BEGIN
SET @ldb_BasicPrize = @ldb_BasicPrize * @ldb_fsyzjxs
SET @ls_gs = @ls_gs + '非三一主机,销售奖标准(' + STR(@ldb_BasicPrize) + ')'
END
SET @ldb_qkbl = 0.95
--预提S--
IF @ll_yt = 0
IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
SET @as_info = '发货后没有超过30天,不予TJ'
SET @ls_errorType = '一般提示'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_errorType, @al_orno, @as_ym, 3, @as_info);
COMMIT;
RETURN 0
--预提E--
END
ELSE
IF @ls_type IN ('沥青搅拌站')
BEGIN
SET @lb_zw = 1 --语法占位
SET @ldb_cjsf = 0.5
SET @ldb_qkbl = 0.9
--预提S--
IF @ll_yt = 0
IF @ldb_all_return / @ldb_amnt < @ldb_qkbl
IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30 AND @li_PayFlag = 1
SET @as_info = '发货后没有超过30天,不予TJ'
SET @ls_errorType = '一般提示'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_errorType, @al_orno, @as_ym, 3, @as_info);
COMMIT;
RETURN 0
END
ELSE
IF @ls_type IN ('砂浆车')
BEGIN
SET @lb_zw = 1 --语法占位
SET @ldb_cjsf = 0.5
SET @ldb_qkbl = 0.9
END
ELSE
BEGIN
SET @ldb_cjsf = 0.5
SET @ldb_qkbl = 0.9
SET @lb_zw = 1 --语法占位
END
DECLARE @ldb_RecMoney NUMERIC(18, 4)
DECLARE @ldb_FirstPay NUMERIC(18, 4)
DECLARE @ldb_AllMoney NUMERIC(18, 4)
DECLARE @ldb_OrderPara NUMERIC(18, 4)
DECLARE @ldb_fkbl NUMERIC(18, 4)
DECLARE @ldb_jbzzjxs NUMERIC(18, 4)
DECLARE @ldb_back_rate NUMERIC(18, 4)
DECLARE @ldb_rate_sjc NUMERIC(18, 4)
----根据付款方式进行计算----
IF @li_PayFlag = 1 OR @li_PayFlag = 6 --分期 全款付款方式
BEGIN
SELECT @ldb_RecMoney = SUM(t_rtmn) ----首付款
FROM tbl_retamn
WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 1
IF @ldb_RecMoney IS NULL
SET @ldb_RecMoney = 0
SET @ldb_FirstPay = @ldb_RecMoney
SET @ls_gs = @ls_gs + ',首付款(' + STR(@ldb_FirstPay) + ')'
SELECT @ldb_AllMoney = SUM(t_rtmn) --当前已回货款
FROM tbl_retamn
WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym
SET @ls_gs = @ls_gs + ',已回款总额为(' + STR(@ldb_AllMoney) + ')'
SET @ls_OrderLevel = 'C'
SET @ldb_OrderPara = 1.1
--2011 hjt li_qk系数没有用,是否按照付款方式里的来?
IF NOT @ls_type = '砂浆车'
IF @ll_yt = 0
IF NOT @li_qk = 1
-- @ld_sendDate=date(ldt_sendDate)
--@days=DaysAfter(ld_sendDate,ld_month_end)
IF DATEDIFF(DAY, @ldt_sendDate, @ld_month_end) < 30
--发货后没有超过一个月(30天)，什么都不干，返回
SET @ls_errorType = '一般提示'
SET @as_info = '发货后没有超过一个月(30天)，不予提销售奖。'
RETURN (0)
IF @li_qk = 1
BEGIN
SET @ls_OrderLevel = 'A'
SET @ldb_OrderPara = 1.3
SET @ls_gs = @ls_gs + '全款'
END
ELSE
IF @ldb_FirstPay / @ldb_plan_sum >= @ldb_cjsf
BEGIN
SET @ls_OrderLevel = 'C'
SET @ldb_OrderPara = 1
SET @ls_gs = @ls_gs + '首付50%以上'
END
ELSE
BEGIN
SET @ls_OrderLevel = 'D' --<=50%@0.8
SET @ldb_OrderPara = 0.8
SET @ls_gs = @ls_gs + '首付50%以下'
END
SET @ldb_jbzzjxs = 1
SET @ldb_fkbl = 0.5 ----计提付款比例
IF @li_exch = 1 --换房、换物和抵款合同销售奖合同等级系数为0.7。
BEGIN
SET @ldb_LevelA = 0.7
SET @ldb_Levelb = 0.7
SET @ldb_LevelC = 0.7
SET @ldb_LevelD = 0.7
SET @ldb_OrderPara = 0.7
SET @ls_gs = @ls_gs + '换房换物和抵款合同,'
END
SELECT @ldb_AllMoney = SUM(t_rtmn) --当前已回货款
FROM tbl_retamn
WHERE t_orno = @al_orno AND CONVERT(CHAR(7), t_rdat, 121) <= @as_ym
IF @ldb_AllMoney IS NULL
SET @ldb_AllMoney = 0
SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2)
IF @ll_yt = 1
SET @ldb_back_rate = 1
IF @ls_type = '砂浆车'
--分别在累计货款回笼30%、70%、100%时计提。
-- ldb_back_rate, ldb_rate_sjc 回款比例，计提比例
SET @ldb_rate_sjc = 0
IF @ldb_back_rate >= 1
BEGIN
IF @sale_count_sjc >= 1
BEGIN
SET @ldb_rate_sjc = 0
END
ELSE
IF @sale_count_sjc >= 0.7
BEGIN
SET @ldb_rate_sjc = 0.3
SET @ls_gs = @ls_gs + '30%销售奖:'
END
ELSE
IF @sale_count_sjc >= 0.3
BEGIN
SET @ldb_rate_sjc = 0.7
SET @ls_gs = @ls_gs + '70%销售奖:'
END
ELSE
BEGIN
SET @ldb_rate_sjc = 1
SET @ls_gs = @ls_gs + '100%销售奖:'
END
END
ELSE
IF @ldb_back_rate >= 0.7
BEGIN
IF @sale_count_sjc >= 0.7
SET @ldb_rate_sjc = 0
ELSE
IF @sale_count_sjc >= 0.3
BEGIN
SET @ldb_rate_sjc = 0.4
SET @ls_gs = @ls_gs + '40%销售奖:'
END
ELSE
BEGIN
SET @ldb_rate_sjc = 0.7
SET @ls_gs = @ls_gs + '70%销售奖:'
END
END
ELSE
IF @ldb_back_rate >= 0.3
BEGIN
IF @sale_count_sjc >= 0.3
SET @ldb_rate_sjc = 0
ELSE
BEGIN
SET @ldb_rate_sjc = 0.3
SET @ls_gs = @ls_gs + '30%销售奖:'
END
END
IF @ldb_rate_sjc > 0
BEGIN
SET @ldb_cjj = @ldb_amnt - (@ldb_zs + @ldb_yhq) - @ldb_zk - @ldb_xzjbc --计算出实际价格(可能是多台的总价)
SET @al_prize = @ldb_amnt * @ldb_rate_sjc * 0.02
SET @ls_gs = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
')*本次提奖比例(' + STR(@ldb_rate_sjc) + ')*.0.02]'
SET @al_prize_manager = @ldb_amnt * @ldb_rate_sjc * 0.01
SET @ls_gs_manager = @ls_gs + '分公司经理销售奖(' + STR(@al_prize_manager) + ')[' + STR(@ldb_amnt) +
')*本次提奖比例(' + STR(@ldb_rate_sjc) + ')*.0.01]'
END
ELSE
IF @ls_type = '搅拌车'
--分别在累计货款回笼60%、100%时计提。
IF @ll_yt = 1
SET @ldb_back_rate = 0.7
IF @ldb_back_rate >= 1
BEGIN
IF @sale_count_sjc >= 1
SET @ldb_rate_sjc = 0
ELSE
IF @sale_count_sjc >= 0.5
BEGIN
SET @ldb_rate_sjc = 0.5
SET @ls_gs = @ls_gs + '50%销售奖:'
END
ELSE
BEGIN
SET @ldb_rate_sjc = 1
SET @ls_gs = @ls_gs + '100%销售奖:'
END
END
ELSE
IF @ldb_back_rate >= 0.6
BEGIN
IF @sale_count_sjc >= 0.5
SET @ldb_rate_sjc = 0
ELSE
BEGIN
SET @ldb_rate_sjc = 0.5
SET @ls_gs = @ls_gs + '50%销售奖:'
END
IF @ldb_rate_sjc > 0
BEGIN
--区分sy底盘与非sy底盘
SET @al_prize = @ldb_amnt * @ldb_rate_sjc * @ldb_sale_per
SET @ls_gs = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*销售奖系数(' + STR(@ldb_sale_per) + ')]'
END
END
ELSE --除搅拌车 砂浆车外的 分期或全款合同
IF @ll_yt = 1
SET @ldb_AllMoney = @ldb_plan_sum
IF @ldb_AllMoney / @ldb_plan_sum >= 0.5
BEGIN
SET @ldb_rate_sjc = 1
IF @ls_type = '混凝土搅拌站'
BEGIN
SET @ls_gs = @ls_gs + '销售奖:基数(' + STR(@ldb_BasicPrize) + ')×地区系数(' + STR(@ldb_AreaPara) +
')' + '×合同系数(' + STR(@ldb_OrderPara) + ')'
SET @al_prize = @ldb_BasicPrize * @ldb_AreaPara * @ldb_OrderPara
END
ELSE
--非混凝土搅拌站
--begin
SET @ls_gs = @ls_gs + '销售奖:基数(' + STR(@ldb_BasicPrize) + ')×合同系数(' + STR(@ldb_OrderPara) +
')×地区系数(' + STR(@ldb_AreaPara) + ')×优价系数(' + STR(@ldb_yjpara) + ')'
SET @al_prize = @ldb_BasicPrize * @ldb_OrderPara * @ldb_AreaPara * @ldb_yjpara
-- end
END
ELSE
BEGIN
SET @ls_gs = @ls_gs + '回款未达到' + STR(@ldb_fkbl) + '不予TJ.'
SET @as_info = @ls_gs
END
END
ELSE
--按揭销售、融资租赁
-- 手续是否办完
DECLARE @li_MortFlag INT
DECLARE @ldb_BackPay NUMERIC(18, 4)
DECLARE
@ls_pdat VARCHAR(7),
@ls_yhfdsj VARCHAR(7),
@ls_ajdzsj VARCHAR(7),
@ls_yhfdsj_date VARCHAR(7) -- 办理按揭时间
SELECT @ls_ajdzsj = ISNULL(CONVERT(VARCHAR(7), t_ajdzsj, 121), '2050-12'),
@ls_yhfdsj = ISNULL(CONVERT(VARCHAR(7), t_yhfdsj, 121), '2050-12'),
@ls_yhfdsj_date = ISNULL(CONVERT(VARCHAR(11), t_yhfdsj, 121), '2050-12-30')
FROM tbl_sls
WHERE t_orno = @al_orno
--预提S
IF @ll_yt = 0
IF @li_zzj = 0 AND @li_zfz = 0
IF (@ls_type <> '砂浆车')
IF @ls_pdat > @as_ym OR @ls_yhfdsj > @as_ym OR @ls_ajdzsj > @as_ym
SET @as_info = '按揭费用未到账或者银行未放贷或权证未受控，不能提取价格奖'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('一般提示', @al_orno, @as_ym, 2, @as_info);
SELECT @as_info AS info, 0 AS rtn, 0 priz
-- commit;
RETURN 0
IF @li_ajqzsk <> 1
--2010-11-02 wangq11 现在发现系统中在没有维护“取得按揭权证受控'但提了销售奖.请检查TJ系统程序设置是否没有考虑这一条件.
--按揭权证受控,需要考虑权证受控时间,否则重提会有错误
SET @as_info = '未取得按揭权证受控，不能提取销售奖'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('操作错误', @al_orno, @as_ym, 3, @as_info);
SELECT @as_info AS info, -1 AS rtn, 0 priz
RETURN 0
IF @ll_yt = 1
SET @li_sx = 1
IF @ls_type = '砂浆车'
BEGIN
--首付款到账,按首付款的2%计提
--抵押登记手续办理完毕后，每月按符合计提条件还贷额的2%计TJ
IF @li_sx = 1 --手续办完
BEGIN
IF @ll_yt = 0
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), t_hdrq, 121)
<= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
<= @as_ym) AND t_orno = @al_orno
ELSE
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), ISNULL(t_hdrq, t_sdat), 121)
<= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
<= @as_ym) AND t_orno = @al_orno;
IF @ldb_AllMoney IS NULL
SET @ldb_AllMoney = 0
SET @ls_gs = @ls_gs + '已回首付或还贷:' + STR(@ldb_AllMoney) + ';'
END
ELSE
--手续未办完
BEGIN
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
<= @as_ym);
IF ISNULL(@ldb_AllMoney, 0) = 0
SET @ldb_AllMoney = 0
SET @ls_gs = @ls_gs + '手续未办完，已回首付:' + STR(@ldb_AllMoney) + ';'
SET @as_info = '手续未办完,不能提取销售奖'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('操作错误', @al_orno, @as_ym, 3, @as_info);
RETURN -0
SET @ldb_rate_sjc = 0
SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2) --回款率,该TJ的比率
END
IF @ll_yt = 1
SET @ldb_back_rate = 1 --预提
--sale_count_sjc已TJ的比率
DECLARE @ldb_tjl NUMERIC(18, 4) --本次需TJ的比率
SET @ldb_tjl = @ldb_back_rate - @sale_count_sjc
IF @ldb_tjl > 0
SET @ldb_rate_sjc = @ldb_tjl
SET @ls_gs = @ls_gs + '本次TJ比率(' + STR(@ldb_rate_sjc)
IF @ldb_rate_sjc > 0
SET @al_prize = @ldb_amnt * @ldb_rate_sjc * 0.02
SET @ls_gs = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*.0.02]'
SET @al_prize_manager = @ldb_amnt * @ldb_rate_sjc * 0.01
SET @ls_gs_manager = @ls_gs + '分公司经理销售奖(' + STR(@al_prize_manager) + ')[' + STR(@ldb_amnt) +
')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*.0.01]'
END
ELSE
IF @ls_type = '搅拌车' --搅拌车按揭或融资\
BEGIN
-- 首付款和相关费用到账、银行放贷后计提50%；抵押登记手续办理完毕后，客户还完所有贷款计提余下的50%。
IF @ll_yt = 1
SET @li_sx = 1 --预提
IF @li_sx = 1 --手续办完
BEGIN
IF @ll_yt = 0
BEGIN
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND (t_hdrq IS NOT NULL AND (ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0) AND CONVERT(CHAR(7), t_hdrq, 121)
<= @as_ym) OR ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
<= @as_ym) AND t_orno = @al_orno
END
ELSE
BEGIN
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 0 OR ISNULL(t_first, 0) = 0))
IF ISNULL(@ldb_AllMoney, 0) = 0
SET @ldb_AllMoney = 0
SET @ls_gs = @ls_gs + '已回首付或还贷:' + STR(@ldb_AllMoney) + ';'
END
END
ELSE
--手续未办完
BEGIN
SELECT @ldb_AllMoney = SUM(t_amnt)
FROM tbl_backorder
WHERE t_orno = @al_orno AND ((ISNULL(t_cash, 0) = 1 OR ISNULL(t_first, 0) = 1) AND ISNULL(t_law, 0) = 0 AND CONVERT(CHAR(7), t_sdat, 121)
<= @as_ym);
IF ISNULL(@ldb_AllMoney, 0) = 0
SET @ldb_AllMoney = 0
SET @ls_gs = @ls_gs + '手续未办完，已回首付:' + STR(@ldb_AllMoney) + ';'
SET @as_info = '手续未办完,不能提取销售奖'
--@ls_ErrType='操作错误'
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES ('操作错误', @al_orno, @as_ym, 3, @as_info)
RETURN -0
SET @ldb_back_rate = ROUND(@ldb_AllMoney / @ldb_plan_sum, 2) --判断是否全部还贷
IF @ll_yt = 1
SET @ldb_back_rate = 0.7
IF @ldb_back_rate < 1
SET @ldb_rate_sjc = 0.5 - @sale_count_sjc
ELSE
SET @ldb_rate_sjc = 1 - @sale_count_sjc
SET @ls_gs = @ls_gs + '本次TJ比率(' + STR(@ldb_rate_sjc)
IF @ldb_rate_sjc > 0
SET @al_prize = @ldb_amnt * @ldb_rate_sjc * @ldb_sale_per
SET @ls_gs = @ls_gs + ',销售奖(' + STR(@al_prize) + ')[合同金额(' + STR(@ldb_amnt) +
')*本次TJ比例(' + STR(@ldb_rate_sjc) + ')*' + STR(@ldb_sale_per) + ')]'
END
END
ELSE
--非砂浆车 搅拌车 按揭或融资
BEGIN
SET @ldb_OrderPara = 1
--2008 6.4
IF @li_PayFlag = 7 --自办按揭
BEGIN
IF @li_exch = 1 --抵债换物合同按八成三年计提
BEGIN
SET @li_mort_years = 3
SET @li_mort_per = 8
SET @ls_gs = @ls_gs + '换物合同,'
SET @ldb_OrderPara = 0.7
END
DECLARE @ll_sfs INT
--取得首付分期数，如分多期则按八成三年计提---huangjt 2009-09-04
SET @ll_sfs = 0
SELECT @ll_sfs = COUNT(*)
FROM tbl_backorder
WHERE t_orno = @al_orno AND ISNULL(t_first, 0) = 1;
IF @ll_sfs > 1 -----huangjt 2009-09-04 加入首付款期数的自动判定
BEGIN
SET @li_mort_years = 3
SET @li_mort_per = 8
SET @ls_gs = @ls_gs + '首付期数(' + STR(@ll_sfs) + ')'
SET @ldb_OrderPara = 1
END
IF @li_aj4 = 1 --首付分期按八成三年计提
BEGIN
SET @li_mort_years = 3
SET @li_mort_per = 8
SET @ls_gs = @ls_gs + '按揭4,'
SET @ldb_OrderPara = 1
END
IF @li_hg = 1 --公司承提回购
BEGIN
SET @ls_OrderLevel = 'B'
SET @ldb_OrderPara = 1.1
SET @ls_gs = @ls_gs + '公司承担回购,'
--***********--2010-07-12 by wangq11 2010规则p29******************--
IF @li_mort_years <= 2
SET @li_mort_years = 2
IF @li_mort_years > 2 AND @li_mort_years <= 3
SET @li_mort_years = 3
IF @li_mort_per <= 7
SET @li_mort_per = 7
IF @li_mort_per > 7
SET @li_mort_per = 8
--Messagebox(string(li_mort_per)+'成',String(li_mort_years)+'年')
--*******************************--
IF @li_mort_per = 7
BEGIN
IF @li_mort_years = 3
BEGIN
SET @ls_OrderLevel = 'B'
SET @ldb_OrderPara = 1.1
SET @ls_gs = @ls_gs + '七成三年'
END
ELSE
IF @li_mort_years = 2
BEGIN
SET @ls_OrderLevel = 'A1'
SET @ldb_OrderPara = 1.2
SET @ls_gs = @ls_gs + '七成二年'
END
ELSE
BEGIN
SET @ls_OrderLevel = 'C1'
SET @ls_gs = @ls_gs + '其它按揭'
SET @ldb_OrderPara = 0.9
END
END
ELSE
IF @li_mort_per = 8
BEGIN
IF @li_mort_years = 3
BEGIN
SET @ls_OrderLevel = 'C'
SET @ldb_OrderPara = 1
SET @ls_gs = @ls_gs + '八成三年'
END
ELSE
IF @li_mort_years = 2
BEGIN
SET @ls_OrderLevel = 'B'
SET @ldb_OrderPara = 1.1
SET @ls_gs = @ls_gs + '八成二年'
END
ELSE
BEGIN
SET @ls_OrderLevel = 'C1'
SET @ldb_OrderPara = 0.9
SET @ls_gs = @ls_gs + '其它按揭'
END
END
ELSE
BEGIN
SET @ls_OrderLevel = 'C1'
SET @ldb_OrderPara = 0.9
SET @ls_gs = @ls_gs + '其它按揭'
END
END --messagebox(ls_gs,ls_gs)
--if li_mort_per=8 then --八成
-- ldb_Levelb=1
--end if
ELSE
--公司不承提回购
BEGIN
SET @ls_OrderLevel = 'A'
SET @ldb_OrderPara = 1.3
SET @ls_gs = @ls_gs + '公司不承提回购,'
END
END
ELSE
IF @li_PayFlag = 3
BEGIN
SET @ls_OrderLevel = 'A'
SET @ldb_OrderPara = 1.3
SET @ls_gs = @ls_gs + '融资,'
END
ELSE
IF @li_PayFlag = 4
BEGIN
SET @ls_OrderLevel = 'C'
SET @ldb_OrderPara = 1
SET @ls_gs = @ls_gs + '一类融资,'
END
ELSE
IF @li_PayFlag = 5
BEGIN
SET @ls_OrderLevel = 'D'
SET @ldb_OrderPara = 0.8
SET @ls_gs = @ls_gs + '二类融资,'
END
ELSE
IF @li_PayFlag = 2
BEGIN
SET @ls_OrderLevel = 'c'
SET @ldb_OrderPara = 1
SET @ls_gs = @ls_gs + '代办按揭'
END
--2009.01.01
IF @li_exch = 1 --换房、换物和抵款合同销售奖合同等级系数为0.7。
BEGIN
SET @ldb_LevelA = 0.7
SET @ldb_LevelA1 = 0.7
SET @ldb_Levelb = 0.7
SET @ldb_LevelC = 0.7
SET @ldb_LevelC1 = 0.7
SET @ldb_LevelD = 0.7
SET @ldb_OrderPara = 0.7
SET @ls_gs = @ls_gs + '换房换物和抵款合同,'
END
IF @li_aj4 = 1 --首付分期按八成三年计提
BEGIN
SET @li_mort_years = 3
SET @li_mort_per = 8
SET @ls_gs = @ls_gs + '按揭4,'
SET @ldb_OrderPara = 1
SET @ldb_rate_sjc = 1
END
SET @ldb_rate_sjc = 1
SET @ls_gs = @ls_gs + '销售奖@基数(' + STR(@ldb_BasicPrize) + ')×合同系数(' + STR(@ldb_OrderPara) +
')×地区系数(' + STR(@ldb_AreaPara) + ')×优价系数(' + STR(@ldb_yjpara) + ')'
SET @al_prize = @ldb_BasicPrize * @ldb_OrderPara * @ldb_AreaPara * @ldb_yjpara
END
IF ISNULL(@al_prize, 0) = 0
BEGIN
SET @al_prize = 0
END
SET @ldb_PrizeDiffer = 0
IF @al_prize = 0
BEGIN
SET @as_info = @as_info + '本月无销售奖!' + @ls_gs
RETURN 0
END
--2008检查是否有低价罚与延期罚息需要抵扣。
DECLARE
@ldb_cdjr NUMERIC(18, 4),
@ldb_wqdcr NUMERIC(18, 4),
@ldb_diff_all NUMERIC(18, 4) --ldb_diff_all所有未冲抵差额,本次需冲抵金额,本条TJ数据未冲抵金额.
DECLARE @ll_id INT
--
--本月回笼奖不为零，检查是否有低价罚与延期罚息需要抵扣。
--如果本次回笼奖加上所有已TJ超过保底佣金，则将多的部分冲抵
--2008-06-17 力士乐主油泵奖罚减半
IF @li_lsl = 1
BEGIN
SET @al_prize = @al_prize / 2
END
IF @li_yj = 1 AND @li_jsb = 1
BEGIN
SET @ldb_lpri = 0
END
IF @li_yjhx = 1
BEGIN
SET @ldb_lpri = @ldb_lpri * (@ldb_ordersum - @ldb_dkr) / @ldb_ordersum
END
ELSE
BEGIN
IF @li_yj = 1
BEGIN
SET @ldb_lpri = @ldb_lpri * (@ldb_ordersum - @ldb_zs * 0.5 - @ldb_yhq * 0.5 - @ldb_zk + @ldb_nopp - @ldb_xzjbc) /
@ldb_ppri
END
END
DECLARE @row INT
DECLARE @ls_emno2 VARCHAR(100) --@ldts_assign.getitemstring(ll,'fld_emno')
DECLARE @ll_corp2 INT --@ldts_assign.getitemnumber(ll,'fld_corp')
DECLARE @ll_province2 INT --@ldts_assign.getitemnumber(ll,'fld_province')
DECLARE @ll_company2 INT --@ldts_assign.getitemnumber(ll,'fld_company')
DECLARE @ldb_pre NUMERIC(18, 4) --@ldts_assign.getitemnumber(ll,'fld_pre')
DECLARE @ldb_cdjr_pre NUMERIC(18, 4)
DECLARE @ll_total INT
DECLARE @ll INT
IF @al_prize + @ldb_PrizeSum > 0
BEGIN
SELECT @ldb_diff_all = SUM(ISNULL(t_diff, 0) + ISNULL(t_ycd, 0))
FROM tbl_priseresult
WHERE ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;
IF ISNULL(@ldb_diff_all, 0) = 0
BEGIN
SET @ldb_diff_all = 0
END
SET @ldb_diff_all = ROUND(@ldb_diff_all, 0)
IF @ldb_diff_all < 0 --有差额需冲抵
BEGIN
IF @ldb_diff_all + (@al_prize + @ldb_PrizeSum - 0) <= 0
BEGIN
--正好将本次TJ多余金额全部冲抵或全部冲抵还不够
SET @ldb_cdjr = ROUND((@al_prize + @ldb_PrizeSum - 0), 0)
IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
BEGIN
SET @ldb_cdjr = 0
END
WHILE @ldb_cdjr > 0
BEGIN
SET @row = 1 -- row=ldts_assign.retrieve(al_orno,as_ym)
SET @ll_total = 0
--for @ll=1 to @row
SET @ll = 1
IF EXISTS
(SELECT 1
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)
))
BEGIN
OPEN tbl_assign_Cursor
WHILE 1 = 1
BEGIN
FETCH tbl_assign_Cursor
INTO @ls_emno2, @ll_corp2, @ll_province2, @ll_province2, @ldb_pre --
IF @@fetch_status <> 0
BREAK
--while @ll<=@row
--begin
SET @ll = @ll + 1
SET @ldb_cdjr_pre = @ldb_cdjr
SET @ll_total = @ll_total + 1
SET @ls_gs = @ls_gs + 'TJ分配，获取' + STR(@ldb_pre) + ';'
SET @ldb_wqdcr = 0
--2009-11-21分公司：备抵人与冲抵人必须一致;个人：所属分公司一致即可
IF @ls_emno2 = '1000000'
BEGIN
SELECT TOP 1
@ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
@ll_id = t_id
FROM tbl_priseresult
WHERE ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
AND ISNULL(t_diff, 0) + ISNULL(t_ycd, 0) < 0 AND t_crep = @ls_emno2
ORDER BY t_cdat ASC
END
ELSE
BEGIN
SELECT TOP 1
@ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
@ll_id = t_id
FROM tbl_priseresult
WHERE ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
AND ISNULL(t_diff, 0) + ISNULL(t_ycd, 0) < 0 AND t_crep IN (SELECT fld_emno
FROM tbl_assign
WHERE fld_company =
@ll_company2 AND fld_orno = @al_orno) AND t_crep <> '1000000'
ORDER BY t_cdat ASC
END
IF @ldb_wqdcr + (@ldb_cdjr_pre) * @ldb_pre >= 0 --本次的差额被全部冲抵完
BEGIN
--冲回本次差额
--插入TJ结果表
INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
SELECT GETDATE(), t_orno, t_crep,
t_corp, t_company, t_comp,
t_province, @as_ctym, t_rdat,
3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
1, '<冲抵－销售奖>' + t_yymm + t_gs, '<冲抵－销售奖>' + t_yymm + t_gs2,
@gs_userName, @ai_cttimes
FROM tbl_priseresult
WHERE t_id = @ll_id
--将所有差额冲抵
UPDATE tbl_priseresult
SET t_ycd = -t_diff
WHERE t_id = @ll_id
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);
RETURN -1
END
SET @ldb_cdjr = ROUND(@ldb_wqdcr + @ldb_cdjr, 0)
IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
SET @ldb_cdjr = 0
END
ELSE
--本次的差额只被冲抵一部分,即ldb_cdjr*ldb_pre这么多
BEGIN
--冲回本次产生金额
INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
SELECT GETDATE(), t_orno, t_crep,
t_corp, t_company, t_comp,
t_province, @as_ctym, t_rdat,
3 t_priz, 0, -@ldb_cdjr_pre * @ldb_pre,
1, '<冲抵－销售奖>' + t_yymm + t_gs, '<冲抵－销售奖>' + t_yymm + t_gs,
@gs_userName, @ai_cttimes
FROM tbl_priseresult
WHERE t_id = @ll_id
UPDATE tbl_priseresult
SET t_ycd = ISNULL(t_ycd, 0) + @ldb_cdjr_pre * @ldb_pre
WHERE t_id = @ll_id
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);
RETURN -1
END
SET @ldb_cdjr = ROUND(@ldb_cdjr - @ldb_cdjr_pre * @ldb_pre, 0)
IF -1 <= @ldb_cdjr AND @ldb_cdjr <= 1
SET @ldb_cdjr = 0
END
END
CLOSE tbl_assign_Cursor
DEALLOCATE tbl_assign_Cursor
END
ELSE
--没有TJ分配
BEGIN
SET @ldb_wqdcr = 0
SELECT TOP 1
@ldb_wqdcr = ISNULL(t_diff, 0) + ISNULL(t_ycd, 0),
@ll_id = t_id --into @ldb_wqdcr,@ll_id
FROM tbl_priseresult
WHERE ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes AND
ISNULL (t_diff
,
0) + ISNULL (t_ycd, 0)
< 0
ORDER BY t_cdat ASC
IF @ldb_wqdcr + @ldb_cdjr >= 0 --本次的差额被全部冲抵完
BEGIN
--冲回本次差额
INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
SELECT GETDATE(), t_orno, t_crep, t_corp,
t_company, t_comp, t_province, @as_ctym,
t_rdat, 3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
1, '<冲抵－销售奖>' + t_yymm + t_gs, '<冲抵>' + t_yymm + t_gs2, @gs_userName,
@ai_cttimes
FROM tbl_priseresult
WHERE t_id = @ll_id
--将所有差额冲抵
UPDATE tbl_priseresult
SET t_ycd = -t_diff
WHERE t_id = @ll_id;
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);
RETURN -1
END
SET @ldb_cdjr = @ldb_wqdcr + @ldb_cdjr
END
ELSE
BEGIN
--本次的差额只被冲抵一部分,即ldb_cdjr这么多
--冲回本次产生金额
INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat,
t_priz, t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
SELECT GETDATE(), t_orno, t_crep, t_corp, t_company, t_comp, t_province,
@as_ctym, t_rdat, 3 t_priz, 0, -@ldb_cdjr, 1, '<冲抵－销售奖>' + t_yymm + t_gs,
'<冲抵－销售奖>' + t_yymm + t_gs, @gs_userName, @ai_cttimes
FROM tbl_priseresult
WHERE t_id = @ll_id;
UPDATE tbl_priseresult
SET t_ycd = ISNULL(t_ycd, 0) + @ldb_cdjr
WHERE t_id = @ll_id;
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);
RETURN -1
END
SET @ldb_cdjr = 0
END
END
END
END
END
ELSE
BEGIN
--奖冲抵完之后还有多
--冲回所有差额
INSERT tbl_priseresult(t_cdat, t_orno, t_crep, t_corp, t_company, t_comp, t_province, t_yymm, t_rdat, t_priz,
t_diff, t_amnt, t_adjust, t_gs, t_gs2, t_user, t_cttimes)
SELECT GETDATE(), t_orno, t_crep, t_corp, t_company, t_comp,
t_province, @as_ctym, t_rdat, 3 t_priz, 0, ISNULL(t_ycd, 0.00) + ISNULL(t_diff, 0.00),
1, '<冲抵－销售奖>' + t_yymm + t_gs, '<冲抵－销售奖>' + t_yymm + t_gs, @gs_userName, @ai_cttimes
FROM tbl_priseresult
WHERE t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes AND ISNULL(t_ycd, 0) + ISNULL(t_diff, 0) < 0
--将所有差额冲抵
UPDATE tbl_priseresult
SET t_ycd = -t_diff
WHERE ISNULL(t_ycd, 0) + ISNULL(t_diff, 0) < 0 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info);
RETURN -1
END
END
--获取TJ分配比例
DECLARE
@ls_gs_tmp VARCHAR(1000),
@ls_gs_manager_tmp VARCHAR(1000)
SET @row = 1 --ldts_assign.retrieve(al_orno,as_ym)
SET @ll_total = 0
OPEN tbl_assign_Cursor
WHILE 1 = 1
BEGIN
FETCH tbl_assign_Cursor
INTO @ls_emno2, @ll_corp2, @ll_province2, @ll_province2, @ldb_pre --
IF @@fetch_status <> 0
BREAK
SET @ll = @ll + 1
SET @ldb_cdjr_pre = @ldb_cdjr
SET @ll_total = @ll_total + 1
SET @ls_gs = @ls_gs + 'TJ分配，获取' + STR(@ldb_pre) + ';'
SET @ldb_wqdcr = 0
--插入TJ结果表
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, @al_prize * @ldb_pre * 0.85, @ldb_PrizeDiffer * @ldb_pre, @as_ctym, GETDATE(),
@ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
@gs_username, @ai_cttimes, @ldb_rate_sjc * @ldb_pre * 0.85)
--分公司
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, (@al_prize_manager + @al_prize) * @ldb_pre * 0.15, @ldb_PrizeDiffer * @ldb_pre,
@as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
@ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
@ai_cttimes, @ldb_rate_sjc * @ldb_pre * 0.15)
--分公司经理
IF @ls_type = '砂浆车'
BEGIN
SET @ls_gs_manager_tmp = @ls_gs_manager + 'TJ分配，获取' + STR(@ldb_pre) + ';'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, @al_prize_manager * @ldb_pre * 0.85, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
GETDATE(), '3000000', @ll_corp2, @ll_company2, @ll_province2, @ls_gs_manager_tmp,
@ls_gs_manager_tmp, @li_flag1, @gs_username, @ai_cttimes, 0)
END
IF @li_xxj = 1
BEGIN
--需要提信息奖
SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
FROM tbl_priseresult
WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
IF ISNULL(@ldb_info_rate, 0) = 0
BEGIN
SET @ldb_info_rate = 0
END
SET @ldb_bc_rate = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate
--扣除营销代表销售奖做为信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
@ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
@gs_username, @ai_cttimes)
--插入信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
@ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
@gs_username, @ai_cttimes, @ldb_bc_rate * @ldb_pre * 0.85)
--扣除分公司部分销售奖做为信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
@as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
@ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
@ai_cttimes)
--插入信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
@as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
@ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
@ai_cttimes, @ldb_bc_rate * @ldb_pre * 0.15)
END
ELSE
BEGIN
SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
FROM tbl_priseresult
WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes
IF ISNULL(@ldb_info_rate, 0) = 0
SET @ldb_info_rate = 0
--sale_count_sjc + ldb_rate_sjc 已提的销售奖比率 ldb_info_rate已提的信息奖比率
-- sale_count_sjc + ldb_rate_sjc - ldb_info_rate --本次需冲的信息奖比率
SET @ldb_bc_rate = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate
--扣除营销代表销售奖做为信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85 * @ldb_pre, 0, @as_ctym, GETDATE(),
@ls_emno2, @ll_corp2, @ll_company2, @ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1,
@gs_username, @ai_cttimes)
-- --插入信息奖
-- insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
-- values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.85*@ldb_pre,0,@as_ctym,getdate(),@ll_emno2,@ll_corp2,@ll_company2,@ll_province2,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*@ldb_pre*0.85)
--扣除分公司部分销售奖做为信息奖
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15 * @ldb_pre, @ldb_PrizeDiffer * @ldb_pre,
@as_ctym, GETDATE(), '1000000', @ll_corp2, @ll_company2,
@ll_province2, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username,
@ai_cttimes)
-- --插入信息奖
-- insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
-- values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.15*@ldb_pre,@ldb_PrizeDiffer*@ldb_pre,@as_ctym,getdate(),1000000,@ll_corp2,@ll_company2,@ll_province2,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*@ldb_pre*0.15)
END
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info)
RETURN -1
END
END
CLOSE tbl_assign_Cursor
DEALLOCATE tbl_assign_Cursorn
--DESTROY @ldts_assign
IF @ll_total = 0 --没有分配系数，照常
BEGIN
--插入TJ结果表
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, @al_prize * 0.85, @ldb_PrizeDiffer, @as_ctym, GETDATE(), @ls_CrewId, @ll_corp,
@ll_company, @ll_province, @ls_gs, @ls_gs, @li_flag1, @gs_username, @ai_cttimes, @ldb_rate_sjc * 0.85)
--分公司
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, (@al_prize_manager + @al_prize) * 0.15, @ldb_PrizeDiffer, @as_ctym, GETDATE(),
'1000000', @ll_corp, @ll_company, @ll_province, @ls_gs, @ls_gs, @li_flag1,
@gs_username, @ai_cttimes, @ldb_rate_sjc * 0.15)
--分公司经理
IF @ls_type = '砂浆车'
BEGIN
SET @ls_gs_manager = @ls_gs_manager + '公司提留0.15.'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 3, @al_prize_manager * 0.85, @ldb_PrizeDiffer, @as_ctym, GETDATE(),
'3000000', @ll_corp, @ll_company, @ll_province, @ls_gs_manager, @ls_gs_manager, @li_flag1,
@gs_username, @ai_cttimes, 0)
END
IF @li_xxj = 1 --需要提信息奖
BEGIN
SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
FROM tbl_priseresult
WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;
IF ISNULL(@ldb_info_rate, 0) = 0
BEGIN
SET @ldb_info_rate = 0
END
--扣除营销代表销售奖做为信息奖
SET @ls_gs_tmp = '信息奖扣除'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
@ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes)
--插入信息奖
SET @ls_gs_tmp = '信息奖'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
@ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes,
@ldb_bc_rate * 0.85)
--扣除分公司部分销售奖做为信息奖
SET @ls_gs_tmp = '信息奖扣除'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
@ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes)
--插入信息奖
SET @ls_gs_tmp = '信息奖'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes, t_ratio)
VALUES (@al_orno, @ldt_BackDate, 1, @ldb_info * @ldb_bc_rate * 0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
@ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes, @ldb_bc_rate * 0.15)
END
ELSE
BEGIN
SELECT @ldb_info_rate = SUM(ISNULL(t_ratio, 0))
FROM tbl_priseresult
WHERE t_priz = 1 AND ISNULL(t_adjust, 0) <> 1 AND t_orno = @al_orno AND ISNULL(t_cttimes, 0) = @ai_cttimes;
IF ISNULL(@ldb_info_rate, 0) = 0
BEGIN
SET @ldb_info_rate = 0
END
--sale_count_sjc + ldb_rate_sjc 已提的销售奖比率 ldb_info_rate已提的信息奖比率
-- sale_count_sjc + ldb_rate_sjc - ldb_info_rate --本次需冲的信息奖比率
SET @ldb_bc_rate = @sale_count_sjc + @ldb_rate_sjc - @ldb_info_rate
--扣除营销代表销售奖做为信息奖
SET @ls_gs_tmp = '信息奖扣除'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.85, 0, @as_ctym, GETDATE(), @ls_CrewId,
@ll_corp, @ll_company, @ll_province, @ls_gs_tmp, @ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes);
-- --插入信息奖
-- ls_gs_tmp='信息奖'
-- insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
-- values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.85,0,@as_ctym,getdate(),@ls_CrewId,@ll_corp,@ll_company,@ll_province,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*0.85);
--扣除分公司部分销售奖做为信息奖
SET @ls_gs_tmp = '信息奖扣除'
INSERT INTO tbl_priseresult(t_orno, t_rdat, t_priz, t_amnt, t_diff, t_yymm, t_cdat, t_crep, t_corp, t_company,
t_province, t_gs, t_gs2, t_flag1, t_user, t_cttimes)
VALUES (@al_orno, @ldt_BackDate, 3, @ldb_info * @ldb_bc_rate * -0.15, @ldb_PrizeDiffer * @ldb_pre, @as_ctym,
GETDATE(), '1000000', @ll_corp, @ll_company, @ll_province, @ls_gs_tmp,
@ls_gs_tmp, @li_flag1, @gs_username, @ai_cttimes);
-- --插入信息奖
-- ls_gs_tmp='信息奖'
-- insert into tbl_priseresult(t_orno,t_rdat,t_priz,t_amnt,t_diff,t_yymm,t_cdat,t_crep,t_corp,t_company,t_province,t_gs,t_gs2,t_flag1,t_user,t_cttimes,t_ratio)
-- values (@al_orno,@ldt_BackDate,1,@ldb_info*@ldb_bc_rate*0.15,@ldb_PrizeDiffer*@ldb_pre,@as_ctym,getdate(),1000000,@ll_corp,@ll_company,@ll_province,@ls_gs_tmp,@ls_gs_tmp,@li_flag1,@gs_username,@ai_cttimes,@ldb_bc_rate*0.15);
END
IF @@ERROR <> 0
BEGIN
SET @as_info = '插入TJ结果表操作失败' + dbo.SysGetErrorMSG(@@ERROR)
SET @ls_ErrType = '数据库错误'
ROLLBACK;
INSERT INTO tbl_log(t_error, t_orno, t_yymm, t_priz, t_desc)
VALUES (@ls_ErrType, @al_orno, @as_ym, 3, @as_info)
RETURN -1
END
END
END
SET @as_info = @ls_gs
RETURN
-----------------------------------------------------------------------------------------------
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/75527/viewspace-722605/ ，如需转载，请注明出处，否则将追究法律责任。
