/*依照新给定的提成表格,结算代理商提成*/
/*V1.01*/
/*200705268*/
/*20070604:修改添加本地通话和长途*/
/*20070925修正*/
/*
@foot结算方式:0=按本系统内标记的已经收款的号码 1=按原系统出票的记录*/
/*20071021修正1:增加单独计算某个分局客户(特殊),2:长途+本地,增加全部费用选项 3.客户范围增加客户必须单线收益率达标,4.代理商的排除项可以单独设置*/
alter procedure pr_agent_deduct_new(@cycle int,@foot smallint=0,@ext_supplier varchar(10) ='',@debug smallint = 0)
as
declare @SQLCMD varchar(8000)
declare @cyclestring varchar(9)
set @cyclestring = cast(@cycle as varchar(9))
/*删除结果表内的该帐期资料*/
delete from loc_agent_deduct_new
where cycle = @cycle and
(@ext_supplier = '' or
supplier = @ext_supplier) /*如果单独重算一个代理商,只能删除这一个的资料*/
-----------------------------------------------------------------------------------------------------------------------
/*公式*/
create table #agent_deduct_percent
(supplier varchar(10),
seq smallint,
type char(1),
custarea varchar(6),
escpecial int,
servtype varchar(6),
callout char(1),
billitems varchar(6),
sign1 char(1),
amt1 int,
sign2 char(1),
amt2 int,
per dec(6,4),
okgrade smallint, /*金额对比应该套用的公式等级*/
objectamt_ys int , /*写入应收*/
objectamt int, /*实收金额*/
deductamt int, /*应提金额*/
foot smallint /*按收款还是出票结算*/
)
/*按新帐期拷贝公式样本loc_sys_deduct_percent进入到提成结果表loc_agent_deduct_new*/
insert into #agent_deduct_percent
select distinct t1.supplier,seq,type,custarea,escpecial,servtype,callout,billitems,sign1,amt1,sign2,amt2,per,0,0,0,0,@foot
from loc_sys_deduct_percent t1,a_inv_supplier t2
where t1.supplier = t2.supplier and
t2.deducttype = 10 and /*20070926*/
(@ext_supplier = '' or
t1.supplier = @ext_supplier)
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
-----------------------------------------------------------------------
/*应收金额按业务种类和长途本地的金额值*/
create table #acct_amt
(
substation varchar(6), /*分局代号*/
supplier varchar(10), /*供应商代码*/
acct_id int, /*合同号*/
acc_nbr varchar(16), /*电话号码*/
billitem int, /*bill_item_type_id*/
charge int, /*应收金额总额*/
DP03amt tinyint, /*客户范围,用于计算DP03=全部客户-特殊客户,需要枚举特殊客户范围逐一排除*/
DP05amt int, /*单线收益率金额*/
DP11amt tinyint, /*super = '10' 固话*/
DP12amt tinyint, /*super = '11' 语音专线*/
DP13amt tinyint, /*super = '12' 汇线通*/
localcallamt tinyint, /*本地通话费*/
longcallamt tinyint, /*长途通话*/
eliminateamt tinyint, /*不参与结算提成的项目的金额*/
upfrontamt int, /*upfront*/
received tinyint, /*payment ramark已经收款(现金或转帐)*/
invsend tinyint /*已经出过票的*/
)
--客户资料写入
insert into #acct_amt
select t3.substation,t1.salesid,t1.acct_id,t1.acc_nbr,0,0,0,0,0,0,0,0,0,0,0,0
from loc_sys_sales_acct t1,a_inv_supplier t2,rmt_f_acct t3
where t1.salesid = t2.supplier and
t1.acct_id = t3.acct_id and
t2.deducttype = 10 and
exists(select 1 from loc_sys_deduct_percent where supplier = t1.salesid) and
(@ext_supplier = '' or
t1.salesid = @ext_supplier) and
t1.cycle = @cycle and
t1.type = 'P'
--rmt_a_bill_item_51_x金额写入
set @SQLCMD = '
insert into #acct_amt
select t1.supplier,
t1.acct_id,
t1.acc_nbr,
t2.bill_item_type_id,
t2.due_charge,
t1.DP11amt,
t1.DP12amt,
t1.DP13amt,
t1.localcallamt,
t1.longcallamt,
t1.eliminateamt,
t1.upfrontamt,
t1.received,
t1.invsend
from #acct_amt t1,rmt_a_bill_item_51_'+ @cyclestring + ' t2
where t1.acct_id = t2.acct_id and
t1.acc_nbr = t2.acc_nbr and
t2.due_charge>0 and
t2.bill_item_type_id >0
'
exec(@SQLCMD)
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
if (@debug >0) select * from #acct_amt
-------------------------------------------------//DEBUG
------------------------------------------------------------------
--Dp05amt单线收益率
set @SQLCMD = '
update #acct_amt
set DP05amt = (select sum(t10.charge) from #acct_amt t10 where t10.acct_id = t1.acct_id)/ /*金额*/
(select count(1) from rmt_f_serv t11 where t11.acct_id = t1.acct_id and t11.state in(''F01'',''F0A'',''F0J''))
from #acct_amt t1
'
exec(@SQLCMD)
--业务种类四种写入
set @SQLCMD = '
update #acct_amt
set DP11amt = 1
from #acct_amt t1,
rmt_a_bill_item_51_'+ @cyclestring + ' t2,
rmt_f_serv t3,rmt_s_serv_type_little t4
where t1.billitem > 0 and
t2.acct_id = t3.acct_id and
t2.acc_nbr = t3.acc_nbr and
t3.serv_type_id %100 = t4.serv_type_id and
t4.super = 10 and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr
'
exec(@SQLCMD)
--
set @SQLCMD = '
update #acct_amt
set DP12amt = 1
from #acct_amt t1,
rmt_a_bill_item_51_'+ @cyclestring + ' t2,
rmt_f_serv t3,rmt_s_serv_type_little t4
where t1.billitem > 0 and
t2.acct_id = t3.acct_id and
t2.acc_nbr = t3.acc_nbr and
t3.serv_type_id %100 = t4.serv_type_id and
t4.super = 11 and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr
'
exec(@SQLCMD)
--
set @SQLCMD = '
update #acct_amt
set DP13amt = 1
from #acct_amt t1,
rmt_a_bill_item_51_'+ @cyclestring + ' t2,
rmt_f_serv t3,rmt_s_serv_type_little t4
where t1.billitem > 0 and
t2.acct_id = t3.acct_id and
t2.acc_nbr = t3.acc_nbr and
t3.serv_type_id %100 = t4.serv_type_id and
t4.super = 12 and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr
'
exec(@SQLCMD)
--/*DP21,全部;DP22,排除项目*///20071021:修正:需要按代理商设置1.FORALL全部;2.FORPART:部分;3.代号:只适合该代理商//--------------------
set @SQLCMD = '
update #acct_amt
set eliminateamt = 1
from #acct_amt t1,
loc_agent_eliminate_items t3
where t1.billitem > 0 and
t1.billitem = t3.billitem and
(t3.include =''A'' or /*全部*/
(t3.include =''B'' and /*特殊除外的代理商*/
not t1.supplier in(select t10.supplier
from loc_agent_eliminate_items t10
where t10.cycle = ' + @cyclestring + ' and
t10.include = ''C'')) or
(t3.include =''C'' and t1.supplier = t3.supplier)) and /*特殊的代理商*/
t3.cycle = '+ @cyclestring
exec(@SQLCMD)
--localcallamt本地通话费
set @SQLCMD = '
update #acct_amt
set localcallamt = 1
from #acct_amt t1,
rmt_bill_item_type_distill t3
where t1.billitem > 0 and
t1.billitem = t3.bill_item_type_id and
t3.cycle = '+ @cyclestring + ' and
t3.is_localcall = ''Y''
'
exec(@SQLCMD)
--longcallamt长途话费
set @SQLCMD = '
update #acct_amt
set longcallamt = 1
from #acct_amt t1,
rmt_bill_item_type_distill t3
where t1.billitem > 0 and
t1.billitem = t3.bill_item_type_id and
t3.cycle = '+ @cyclestring + ' and
t3.is_longdistance = ''Y''
'
exec(@SQLCMD)
--预付费金额
set @SQLCMD = '
update #acct_amt
set upfrontamt = (select isnull(sum(fee),0)
from CTTMISTICKET.dbo.rmt_unfront_total t2
where t2.cycle = ' + @cyclestring +' and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr)
from #acct_amt t1
where t1.billitem =0' /*特别注意billitem=0为预付*/
exec(@SQLCMD)
--received (1/0)
set @SQLCMD = '
update #acct_amt
set received = 1
from #acct_amt t1,
rmt_a_payment_tab t2,
rmt_a_payment_remark t3
where t2.cyc_seq_nbr = ' + @cyclestring +' and
t2.cyc_seq_nbr = t3.cyc_seq_nbr and
t2.tab_nbr = t3.tab_nbr and
t2.tab_nbr_type = t3.tab_nbr_type and
t2.state = ''R01'' and
t2.this_payment >0 and
(t3.receive =''是'' or
t3.transfer =''是'') and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr'
exec(@SQLCMD)
--已经出票的号码
set @SQLCMD = '
update #acct_amt
set invsend = 1
from #acct_amt t1,
rmt_a_payment_tab t2
where t2.cyc_seq_nbr = ' + @cyclestring +' and
t2.state = ''R01'' and
t2.this_payment >0 and
t2.acct_id = t1.acct_id and
t2.acc_nbr = t1.acc_nbr'
exec(@SQLCMD)
--删除不是出票代表号码的记录(金额为0 or null)
delete from #acct_amt
where billitem >0 and (charge = 0 or charge is null)
delete from #acct_amt
where billitem =0 and upfrontamt = 0
------------------------------------------//DEBUG
if @debug>0 select * from #acct_amt
------------------------------------------//DEBUG
--------------------------------------------------------------------------------------
/*游标使用的变量*/
declare @supplier varchar(10)
declare @seq smallint
declare @type char(1) /*A:后付B:预付*/
declare @custarea varchar(6)
declare @escpecial int
declare @servtype varchar(6)
declare @callout char(1)
declare @billitems varchar(6)
/*设置光标来推算*/
declare cur_supplier_distinct cursor for
select distinct supplier,seq,type,custarea,escpecial,servtype,callout,billitems
from #agent_deduct_percent
open cur_supplier_distinct
fetch cur_supplier_distinct into
@supplier,@seq,@type,@custarea,@escpecial,@servtype,@callout,@billitems
while (@@fetch_status) = 0
begin
/*写入应收总额:objectamt_ys*/
if (@type = 'B') /*预付*/
begin
update #agent_deduct_percent
set objectamt_ys = (select isnull(sum(upfrontamt),0)
from #acct_amt t2
where t2.supplier = t1.supplier and
t2.billitem = 0) /*后付/预付标致位*/
from #agent_deduct_percent t1
where t1.supplier = @supplier and
t1.seq = @seq
end
if (@type = 'A') /*后付*/
begin
update #agent_deduct_percent
set objectamt_ys = (select isnull(sum(charge),0)
from #acct_amt t2
where t2.supplier = t1.supplier and
t2.billitem > 0 and /*后付/预付标致位*/
(@custarea = 'DP01' or
(@custarea = 'DP02' and escpecial = t2.acct_id) or
(@custarea = 'DP03' and
exists(select 1
from #agent_deduct_percent
where supplier = t1.supplier and
custarea = 'DP02'))or /*客户范围,用于计算DP03=全部客户-特殊客户,需要枚举特殊客户范围逐一排除*/
(@custarea = 'DP04' and escpecial =cast(t2.substation as int)) or
(@custarea = 'DP05' and escpecial*100 < t2.DP05amt)) and
((@servtype = 'DP11' and DP11amt = 1) or
(@servtype = 'DP12' and DP12amt = 1) or
(@servtype = 'DP13' and DP13amt = 1) or
(@servtype = 'DP14' and (DP11amt = 1 or DP12amt = 1 or DP13amt = 1))) and
(@callout = 'Z'or /*20071021全部*/
(@callout = 'A' and (localcallamt = 1 or longcallamt = 1)) or
(@callout = 'B' and localcallamt = 1) or
(@callout = 'C' and longcallamt = 1)) and
(@billitems = 'DP21' or
(@billitems = 'DP22' and eliminateamt = 0))
)
from #agent_deduct_percent t1
where t1.supplier = @supplier and
t1.seq = @seq
end
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
/*比对应该属于哪个提成级别,注意是用应收金额来比较的*/
update #agent_deduct_percent
set okgrade = 1
where ((sign1 = 'A' and objectamt_ys/100.00 >amt1) or
(sign1 = 'B' and objectamt_ys/100.00 >=amt1)) and
((sign2 = 'A' and objectamt_ys/100.00 <amt2) or
(sign2 = 'B' and objectamt_ys/100.00 <=amt2)) and
supplier = @supplier and
seq = @seq
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
/*写入实收总额:objectamt*/
if (@type = 'B') /*预付*/
begin
update #agent_deduct_percent /*预付费的两个一样*/
set objectamt = objectamt_ys
where supplier = @supplier and
seq = @seq
end
if (@type = 'A') /*后付*/
begin
update #agent_deduct_percent
set objectamt = (select isnull(sum(charge),0)
from #acct_amt t2
where t2.supplier = t1.supplier and
seq = @seq and
t2.billitem > 0 and /*后付/预付标致位*/
(@custarea = 'DP01' or
(@custarea = 'DP02' and escpecial = t2.acct_id) or
(@custarea = 'DP03' and escpecial =0) or
(@custarea = 'DP04' and escpecial =cast(t2.substation as int)) and
((@servtype = 'DP11' and DP11amt = 1) or
(@servtype = 'DP12' and DP12amt = 1) or
(@servtype = 'DP13' and DP13amt = 1) or
(@servtype = 'DP14' and (DP11amt = 1 or DP12amt = 1 or DP13amt = 1))) and
(@callout = 'Z'or /*20071021全部*/
(@callout = 'A' and (localcallamt = 1 or longcallamt = 1)) or
(@callout = 'B' and localcallamt = 1) or
(@callout = 'C' and longcallamt = 1)) and
(@billitems = 'DP21' or
(@billitems = 'DP22' and eliminateamt = 0)) and
((@foot = 0 and received = 1) or /*与应收的差别*/
(@foot = 1 and invsend = 1)) /*按出票金额还是按实际收款标记的金额*/
)
from #agent_deduct_percent t1
where t1.supplier = @supplier and
t1.seq = @seq
end
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
set @supplier=''
set @seq=0
set @type=''
set @custarea=''
set @escpecial=''
set @servtype=''
set @callout=''
set @billitems=''
fetch cur_supplier_distinct into
@supplier,@seq,@type,@custarea,@escpecial,@servtype,@callout,@billitems
end
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
/*删除 okgrade=0的行*/
delete from #agent_deduct_percent where okgrade=0 /*不匹配的等级*/
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
/*计算结算的结果值deductamt*/
update #agent_deduct_percent
set deductamt = objectamt*per
-------------------------------------------------//DEBUG
if (@debug >0) select * from #agent_deduct_percent
-------------------------------------------------//DEBUG
--------------------------------------------------------------------------------------
/*将#agent_deduct_percent的计算结果插入loc_agent_deduct_new*/
insert into loc_agent_deduct_new
select @cycle,supplier,seq,type,custarea,escpecial,servtype,billitems,objectamt_ys/100.00,objectamt/100.00,per,deductamt/100.00,foot
from #agent_deduct_percent
/*将明细也存档备检查*/
delete from loc_agent_deduct_acct where cycle = @cycle
delete from loc_agent_deduct_acct where cycle < @cycle and datediff(mm,createdate,getdate()) > -12
insert into loc_agent_deduct_acct
select @cycle,*,getdate()
from #acct_amt
/*清理临时表*/
drop table #agent_deduct_percent
drop table #acct_amt
close cur_supplier_distinct
deallocate cur_supplier_distinct