alter proc sp_bg_count_amt_hz @as_userid char(10)
asdelete from t_bg_count_amt where usercode =@as_useridselect pt_part,pt_desc1,pt_desc2,pl_prod_desc,pl_prod_line
into #a
from pl_mstr,pt_mstr
where pl_prod_line =pt_prod_line and pt_status='Y' select bg_part,t.opdate,bg_nbr,bg_cust,bg_corpno,bg_corpname,bg_factname,flag_other,bg_sort,flag_fee,flag_cw,bg_dzrmk ,so_po,price_ys,qty_act,t_zh_fee,flag_sort
into #b
from bg_m t,so_mstr m,sod_det d where t.bg_cust=m.so_cust and m.so_nbr=d.sod_nbr and t.bg_part=d.sod_part select round(sum(case sod_det.sod_um when 'KG' then tr_qty_weight when 'M' then tr_qty_piece else tr_qty_pc end * tr_price),3)*-1 amt,
sum(tr_qty_weight)*-1 num,tr_part,tr_effdate,so_po
into #c
from tr_hist inner join so_mstr on ( tr_hist.tr_addr = so_mstr.so_cust )
inner join sod_det on (sod_det.sod_nbr = tr_hist.tr_nbr and sod_det.sod_part = tr_hist.tr_part
and sod_det.sod_nbr = so_mstr.so_nbr)
where ( tr_hist.tr_type like 'ISS-SO%' or tr_hist.tr_type like 'RCT-SOR%' )
and tr_addr in (select cust from cust_right where usercode='ADM' and flag='Y')
group by tr_part,tr_effdate,so_po select a.bg_part,opdate,a.bg_nbr,bg_cust,cm_type,bg_corpno,bg_corpname,bg_factname,a.qty_act,a.bg_sort,flag_other,a.flag_sort,
sum(case when d.flag='Y' then bg_price else 0 end) bg_amt,a.flag_fee,a.flag_cw,a.bg_dzrmk,t.pt_part,t.pl_prod_desc,t.pl_prod_line,
isnull(sum(case when d.flag='Y' then price_ys else 0 end),0) kh_amt,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ) zq,
case when cm_type='AS02' and (pl_prod_desc not like '%定牌%' and (pl_prod_line like '071[6-9]%' or pl_prod_line like '072[0-1]%') ) then '50000' else 0 end sale_amt,
dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),opdate) exdate ,t_zh_fee
into #d
from bg_m a,cm_mstr k,bg_d d,#a t
where a.bg_cust=k.cm_addr and t.pt_part=a.bg_part and a.bg_nbr=d.bg_nbr and not exists(select 1 from bg_m b, cm_mstr k1 where b.bg_cust = k1.cm_addr and b.bg_part=a.bg_part and
convert(varchar(19),a.opdate,20) + convert(varchar(19),'2050-01-01' -
dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),a.opdate) + a.opdate,20) + a.bg_nbr >
convert(varchar(19),b.opdate,20) + convert(varchar(19),'2050-01-01' -
dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),b.opdate) + b.opdate,20) + b.bg_nbr)
group by a.bg_part,a.opdate,a.bg_nbr,bg_cust,cm_type,bg_corpno,bg_corpname,bg_factname,bg_sort,flag_fee,flag_cw,bg_dzrmk,a.qty_act,
a.flag_other,t_zh_fee,t.pt_part,t.pl_prod_desc,t.pl_prod_line,flag_sort select usercode,cust
into #e from syscode4 s,cust_right c
where sysvalue2=c.usercode and syscode='每日接单短信提醒' and flag ='Y'insert t_bg_count_amt(t_part,opdate,exp_date,bg_nbr,cm_addr,sale_out_amt,amt_num,t_po,cm_type,corp_no,corp_name,t_factname,t_um,t_other,t_zh_fee,
t_qty_act,bc_amt,kh_amt,set_c,sale_amt,t_sort,t_fee,t_flag_cw,t_rmks,usercode,tr_effdate,t_user)select c.bg_part,c.opdate,c.exdate,c.bg_nbr,c.bg_cust,sum(d.amt),sum(d.num),d.so_po,c.cm_type,c.bg_corpno,c.bg_corpname,c.bg_factname,'支',
c.flag_other,
case
when c.cm_type='AS02' and (c.pl_prod_desc not like '%定牌%' and (c.pl_prod_line like '071[6-9]%' or c.pl_prod_line like '072[0-1]%') ) and sum(d.amt) >50000 then '免费'
when c.cm_type='AS02' and (pl_prod_desc not like '%定牌%' and (c.pl_prod_line like '071[6-9]%' or c.pl_prod_line like '072[0-1]%') ) and sum(d.amt) <50000 then '收费'
when c.cm_type='AS02' and (pl_prod_desc like '%定牌%' and (c.pl_prod_line like '071[6-9]%' or c.pl_prod_line like '072[0-1]%') ) then '收费'
when c.cm_type='AS13' and sum(d.amt)/35 - kh_amt >0 then '免费'
when c.cm_type='AS13' and sum(d.amt)/35 - kh_amt <0 then '收费'
when c.cm_type='AS09' and sum(d.num)>100000 then '免费'
when c.cm_type='AS09' and sum(d.num)<100000 then '收费'
when c.cm_type='AS07' then '收费' when c.cm_type='AS22' then '收费' else '' end ,
c.qty_act,c.bg_amt,c.kh_amt,c.zq,c.sale_amt,c.bg_sort,c.flag_fee,c.flag_cw,c.bg_dzrmk,@as_userid,d.tr_effdate,s.usercodefrom #d c,#c d,#e s
where c.bg_part =d.tr_part and s.cust=c.bg_cust and d.tr_effdate >= c.opdate and d.tr_effdate<= c.exdate
group by c.bg_part, c.opdate, c.exdate, c.bg_nbr, c.bg_cust,d.amt,d.num,d.so_po,c.cm_type,c.bg_corpno,c.bg_corpname,c.bg_factname,
c.flag_other,c.qty_act,c.bg_amt,c.kh_amt,c.zq,c.sale_amt,c.bg_sort,c.flag_fee,c.flag_cw,c.bg_dzrmk,tr_effdate,t_zh_fee,pl_prod_desc,
pl_prod_line,s.usercode,flag_sortunion all
select a.bg_part,a.opdate,dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),opdate),a.bg_nbr,a.bg_cust,0,0,so_po,cm_type,a.bg_corpno,a.bg_corpname,
a.bg_factname,'支',a.flag_other,
case
when cm_type='AS01' and (t.pl_prod_desc not like '%定牌%' and (t.pl_prod_line like '070[1-9]%' or t.pl_prod_line like '071[0-4]%')) then '免费'
when cm_type='AS01' and (t.pl_prod_desc like '%定牌%' and (t.pl_prod_line like '070[1-9]%' or t.pl_prod_line like '071[0-4]%')) and a.flag_sort in ('Y','N','T') then '免费'
when cm_type='AS01' and (t.pl_prod_desc like '%定牌%' and (t.pl_prod_line like '070[1-9]%' or t.pl_prod_line like '071[0-4]%')) and a.bg_sort in ('N','C') then '收费'
when cm_type='AS10' then '免费' when cm_type='AS16' then '免费或收费' when cm_type='AS15' then '免费或收费'
when cm_addr like 'AS04001%' then '免费' when cm_type='AS21' then '免费' when cm_addr like 'AS04002%' then '收费' when cm_addr like 'AS16005%' then '免费或收费'
when cm_addr like 'AS04004%' then '收费' when cm_addr like 'AS04007%' then '收费' when cm_addr like 'AS04003%' then '收费' when cm_addr like 'AS04006%' then '收费'
when cm_addr like 'AS04005%' then '收费' when cm_type ='AS06' and bg_sort ='N' then '收费'
when cm_type='AS06' and bg_sort ='C' then '免费' else '' end,
a.qty_act,isnull(sum(case when d.flag='Y' then bg_price else 0 end),0),isnull(sum(case when d.flag='Y' then price_ys else 0 end),0),
case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ,0,a.bg_sort,a.flag_fee,a.flag_cw,a.bg_dzrmk,@as_userid,'',s.usercodefrom #b a,#e s,#a t,cm_mstr k,bg_d dwhere a.bg_cust=k.cm_addr and s.cust= a.bg_cust and a.bg_part =t.pt_part and (case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end )='' and a.bg_nbr=d.bg_nbr and not exists(select 1 from bg_m b, cm_mstr k1 where b.bg_cust = k1.cm_addr and b.bg_part=a.bg_part and
convert(varchar(19),a.opdate,20) + convert(varchar(19),'2050-01-01' -
dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),a.opdate) + a.opdate,20) + a.bg_nbr >
convert(varchar(19),b.opdate,20) + convert(varchar(19),'2050-01-01' -
dateadd(month,convert(int,case when cm_type='AS02' then '3'when cm_type='AS09' then '12' when cm_type='AS13' then '12'
when cm_type='AS22' then '12' when cm_type='AS07' then '12'else '' end ),b.opdate) + b.opdate,20) + b.bg_nbr)
group by a.bg_part,a.opdate,a.bg_nbr,bg_cust,cm_type,bg_corpno,bg_corpname,bg_factname,bg_sort,flag_fee,flag_cw,bg_dzrmk,a.qty_act,
a.flag_other,t_zh_fee,cm_addr,so_po,s.usercode,pl_prod_line,pl_prod_desc,flag_sort
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货