我写的第100个存储器,谁知道怎么写能简单些吗?

/*依照新给定的提成表格，结算代理商提成*/
/*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