首页 > 代码库 > Oracle 销售统计报表 实例

Oracle 销售统计报表 实例

待优化的SQL

create or replace procedure slbksoiqa2(
     var_date1    date default to_date(‘1969-01-01‘,‘yyyy-mm-dd‘),
     var_date2    date default to_date(‘2069-01-01‘,‘yyyy-mm-dd‘),
     var_cust1    varchar2 default ‘‘,
     var_cust2    varchar2 default ‘ZZZZZZZZ‘,
     var_slspsn1  varchar2 default ‘‘,
     var_slspsn2  varchar2 default ‘ZZZZZZZZ‘
     --resultcursor   out   sys_refcursor
)
/*
作者:苏炎江
时间:2016-10-18
功能:销售预留费用统计(新)
内容:统计开模费均摊、认证费均摊、制定运费、市场费均摊、销售折扣加价、rom备件预留金、差异总额
*/
as
    -------已下单金额--------
    var_diff_amt     decimal(19,4);   --下单
    var_list_amt     decimal(19,4);
    var_freight_amt  decimal(19,4);
    var_mould_amt    decimal(19,4);
    var_attest_amt   decimal(19,4);
    var_market_amt   decimal(19,4);
    var_rom_amt      decimal(19,4);
    var_tool_amt     decimal(19,4);
    var_cust         varchar2(50);
    var_slspsn       varchar2(50);
    var_count        Integer;
    -------ROM/DMZ 预留分配--------
    var_list_amt1    decimal(19,4);
    var_freight_amt1 decimal(19,4);
    var_mould_amt1   decimal(19,4);
    var_attest_amt1  decimal(19,4);
    var_market_amt1  decimal(19,4);
    var_tool_amt1    decimal(19,4);
   /* --------期初金额----------
    var_list_amt2    decimal(19,4);
    var_freight_amt2 decimal(19,4);
    var_mould_amt2   decimal(19,4);
    var_attest_amt2  decimal(19,4);
    var_market_amt2  decimal(19,4);
    var_rom_amt2     decimal(19,4);
    var_diff_amt2    decimal(19,4);*/
begin

    delete from tpt_slbkso1;
    insert into tpt_slbkso1(tpt_cust,tpt_slspsn,
                           tpt_sod_list_amt,tpt_sod_freight_amt,tpt_sod_mould_amt,tpt_sod_attest_amt,tpt_sod_rom_amt,tpt_sod_market_amt,tpt_sod_diff_amt,tpt_sod_tool_amt ,
                           tpt_mrbd_list_amt,tpt_mrbd_freight_amt,tpt_mrbd_mould_amt,tpt_mrbd_attest_amt,tpt_mrbd_rom_amt, tpt_mrbd_market_amt,tpt_mrbd_diff_amt,tpt_mrb_tool_amt ,
                           tpt_sod_list_amt1,tpt_sod_freight_amt1,tpt_sod_mould_amt1,tpt_sod_attest_amt1,tpt_sod_rom_amt1,tpt_sod_market_amt1,tpt_sod_diff_amt1,tpt_sod_tool_amt1
                           )

    select sdh_cust,sdh_so_slspsn,
           sum(sod_list_amt),sum(sod_freight_amt),sum(sod_mould_amt),sum(sod_attest_amt),sum(sod_rom_amt),sum(sod_market_amt),sum(sod_diff_amt),sum(sod_tool_amt),
           sum(mrbd_list_amt),sum(mrbd_freight_amt),sum(mrbd_mould_amt),sum(mrbd_attest_amt),sum(mrbd_rom_atm),sum(mrbd_market_amt),sum(mrbd_diff_amt),sum(mrbd_tool_amt),
           sum(sod_list_amt1),sum(sod_freight_amt1),sum(sod_mould_amt1),sum(sod_attest_amt1),sum(sod_rom_amt1),sum(sod_market_amt1),sum(sod_diff_amt1),sum(sod_tool_amt1)
    from(
     ------------------------------------已关单出货数量----------------------------------------------------
      select sdh_cust,sdh_so_slspsn,
             nvl(sod_list_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_list_amt,nvl(sod_freight_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_freight_amt,
             nvl(sod_mould_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_mould_amt,nvl(sod_attest_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_attest_amt,
             nvl(sod_rom_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_rom_amt,nvl(sod_market_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_market_amt,
             nvl(get_so_nbr_price(sdh_dn_dn,sdh_dnd_line) * sdh_qty_shp * sdh_ex_rate,0) as sod_diff_amt,nvl(sod_tool_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_tool_amt,

             0 as mrbd_list_amt,0 as mrbd_freight_amt,0 as mrbd_mould_amt,0 as mrbd_attest_amt,0 as mrbd_rom_atm,0 as mrbd_market_amt,0 as mrbd_diff_amt,0 as mrbd_tool_amt,

             0 as sod_list_amt1,0 as sod_freight_amt1,0 as sod_mould_amt1,0 as sod_attest_amt1,0 as sod_rom_amt1,0 as sod_market_amt1,0 as sod_diff_amt1,0 sod_tool_amt1
      from sdh_hist
      left join sod_det on sod_nbr=sdh_so_nbr and sod_line=sdh_sod_line
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from so_mstr) on so_nbr=sdh_so_nbr and so_char1 is null
      left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(sdh_cust,var_cust1,var_cust2)=1
        and between2(sdh_so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(sdh_crt_date,‘dd‘)>=var_date1 and trunc(sdh_crt_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sdh_qty_shp <> 0
        and so_char1 is null
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and sdh_prog_code  in (‘SLDNMTA1‘,‘SLDNMTA5‘) --and sod_status=‘C‘   --出货数量含关单与未关单的出货数量
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
      union all
      select sdh_cust,sdh_so_slspsn,nvl(sod_list_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_list_amt,nvl(sod_freight_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_freight_amt,
             nvl(sod_mould_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_mould_amt,nvl(sod_attest_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_attest_amt,
             nvl(sod_rom_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_rom_amt,nvl(sod_market_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_market_amt,
             nvl(huinan.get_so_nbr_price@huinanerp(sdh_dn_dn,sdh_dnd_line) * sdh_qty_shp * sdh_ex_rate,0) as sod_diff_amt,
             nvl(sod_tool_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_tool_amt,
             0,0,0,0,0,0,0,0,
             0,0,0,0,0,0,0,0
      from huinan.sdh_hist@huinanerp
      left join huinan.sod_det@huinanerp on sod_nbr=sdh_so_nbr and sod_line=sdh_sod_line
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr=sdh_so_nbr and so_char1 is null
      left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(sdh_cust,var_cust1,var_cust2)=1
        and between2(sdh_so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(sdh_crt_date,‘dd‘)>=var_date1 and trunc(sdh_crt_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sdh_qty_shp <> 0
        and so_char1 is null
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and sdh_prog_code  in (‘SLDNMTA1‘,‘SLDNMTA5‘) --and sod_status=‘C‘    --出货数量含关单与未关单的出货数量
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
     ---------------------------------------------------已关单退货------------------------------------------------------
     union all
     select sdh_cust,sdh_so_slspsn,
             nvl(sod_list_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_list_amt,nvl(sod_freight_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_freight_amt,
             nvl(sod_mould_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_mould_amt,nvl(sod_attest_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_attest_amt,
             nvl(sod_rom_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_rom_amt,nvl(sod_market_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_market_amt,
             nvl(get_so_nbr_price1(sdh_dn_dn,sdh_dnd_line) * sdh_qty_shp * sdh_ex_rate,0) as sod_diff_amt,nvl(sod_tool_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_tool_amt,

             0 as mrbd_list_amt,0 as mrbd_freight_amt,0 as mrbd_mould_amt,0 as mrbd_attest_amt,0 as mrbd_rom_atm,0 as mrbd_market_amt,0 as mrbd_diff_amt,0 as mrbd_tool_amt,

             0 as sod_list_amt1,0 as sod_freight_amt1,0 as sod_mould_amt1,0 as sod_attest_amt1,0 as sod_rom_amt1,0 as sod_market_amt1,0 as sod_diff_amt1,0 sod_tool_amt1
      from sdh_hist
      left join sod_det on sod_nbr=sdh_so_nbr and sod_line=sdh_sod_line
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from so_mstr) on so_nbr=sdh_so_nbr and so_char1 is null
      left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(sdh_cust,var_cust1,var_cust2)=1
        and between2(sdh_so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(sdh_crt_date,‘dd‘)>=var_date1 and trunc(sdh_crt_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sdh_qty_shp <> 0
        and so_char1 is null
        --and sod_status = ‘C‘
        and sdh_prog_code  in(‘SLCRMTA1‘,‘SLCRMTA2‘)
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
      union all
      select sdh_cust,sdh_so_slspsn,nvl(sod_list_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_list_amt,nvl(sod_freight_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_freight_amt,
             nvl(sod_mould_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_mould_amt,nvl(sod_attest_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_attest_amt,
             nvl(sod_rom_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_rom_amt,nvl(sod_market_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_market_amt,
             nvl(huinan.get_so_nbr_price1@huinanerp(sdh_dn_dn,sdh_dnd_line) * sdh_qty_shp * sdh_ex_rate,0) as sod_diff_amt,
             nvl(sod_tool_cost*sdh_qty_shp * sdh_ex_rate*nvl(exd_r_rate,1),0) as sod_tool_amt,
             0,0,0,0,0,0,0,0,
             0,0,0,0,0,0,0,0
      from huinan.sdh_hist@huinanerp
      left join huinan.sod_det@huinanerp on sod_nbr=sdh_so_nbr and sod_line=sdh_sod_line
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr=sdh_so_nbr and so_char1 is null
      left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(sdh_cust,var_cust1,var_cust2)=1
        and between2(sdh_so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(sdh_crt_date,‘dd‘)>=var_date1 and trunc(sdh_crt_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sdh_qty_shp <> 0
        and so_char1 is null
        --and sod_status = ‘C‘
        and sdh_prog_code  in (‘SLCRMTA1‘,‘SLCRMTA2‘)
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
     -------------------------------------未关单 未出货数量---------------------------------------------------------------------
      union all
      select so_cust,so_slspsn,
             0,0,0,0,0,0,0,0,
             0,0,0,0,0,0,0,0,
             nvl(sod_list_cost*(sod_qty_ord-sod_qty_shp)*so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_freight_cost*(sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(sod_mould_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_attest_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(sod_rom_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_market_cost*(sod_qty_ord-sod_qty_shp) * so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(round((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * (sod_qty_ord-sod_qty_shp),4) * so_ex_rate,0),nvl(sod_tool_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0)
      from sod_det
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from so_mstr) on so_nbr=sod_nbr  and so_char1 is null
      left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(so_cust,var_cust1,var_cust2)=1
        and between2(so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(so_ord_date,‘dd‘)>=var_date1
        and trunc(so_ord_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and so_char1 is null
        and sod_status is null
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
     union all
      select so_cust,so_slspsn,
             0,0,0,0,0,0,0,0,
             0,0,0,0,0,0,0,0,
             nvl(sod_list_cost*(sod_qty_ord-sod_qty_shp)*so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_freight_cost*(sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(sod_mould_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_attest_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(sod_rom_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0),nvl(sod_market_cost*(sod_qty_ord-sod_qty_shp) * so_ex_rate*nvl(exd_r_rate,1),0),
             nvl(round((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * (sod_qty_ord-sod_qty_shp),4) * so_ex_rate,0),nvl(sod_tool_cost* (sod_qty_ord-sod_qty_shp) *so_ex_rate*nvl(exd_r_rate,1),0)
      from huinan.sod_det@huinanerp
      left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr=sod_nbr and so_char1 is null
      left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where between2(so_cust,var_cust1,var_cust2)=1
        and between2(so_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(so_ord_date,‘dd‘)>=var_date1
        and trunc(so_ord_date,‘dd‘)<=var_date2
        and trunc(so_ord_date,‘dd‘) >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘) or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘) or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘))
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and so_char1 is null
        and sod_status is null
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
       ---------------------------------------扣款记录-------------------------------------------------------------------
      union all
      select mrb_vend,mrb_slspsn,
             0,0,0,0,0,0,0,0,
             (case when mrb_payment=‘CP0A‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--销售折扣加价
             (case when mrb_payment=‘CP09‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--指定运费
             (case when mrb_payment=‘CP07‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--开模费
             (case when mrb_payment=‘CP08‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--认证费
             (case when mrb_payment=‘CP12‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣rom备件预留金
             (case when mrb_payment=‘CP13‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣市场费
             (case when (mrb_payment=‘CP01‘ and mrb_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣差异额度
             0, --已扣工具费
             0,0,0,0,0,0,0,0
      --from sdh_hist join mrb_mstr on mrb_nbr=sdh_dn_dn
      from mrb_mstr
      join mrbd_det on mrb_nbr = mrbd_nbr
      left join exd_det on exd_curr=mrbd_curr and mrb_date between exd_eff_date and exd_end_date
      where between2(mrb_vend,var_cust1,var_cust2)=1
        and between2(mrb_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(mrb_date,‘dd‘)>=var_date1 and trunc(mrb_date,‘dd‘)<=var_date2
        and mrb_prog_code=‘SLBKMTA1‘
        and mrb_pst = 1
        --and mrb_payment in (‘CP0A‘,‘CP09‘,‘CP07‘,‘CP08‘,‘CP12‘,‘CP13‘,‘CP01‘)
        --and sdh_crt_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)  -----金额管控从这个时间点开始
        /*注:扣款记录必须从12月8号下午2点整(程序更新时间) 以后审核过账的数据才能算是有效扣款数据,并在报表中过滤无效扣款数据*/
      union all
      select mrb_vend,mrb_slspsn,
             0,0,0,0,0,0,0,0,
             (case when mrb_payment=‘CP0A‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--销售折扣加价
             (case when mrb_payment=‘CP09‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--指定运费
             (case when mrb_payment=‘CP07‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--开模费
             (case when mrb_payment=‘CP08‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end),--认证费
             (case when mrb_payment=‘CP12‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣rom备件预留金
             (case when mrb_payment=‘CP13‘ then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣市场费
             (case when (mrb_payment=‘CP01‘ and mrb_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) then mrbd_price *mrbd_qty * nvl(exd_rate,1) else 0 end), --已扣差异额度
             0, --已扣工具费
             0,0,0,0,0,0,0,0
      --from huinan.sdh_hist@huinanerp join huinan.mrb_mstr@huinanerp on mrb_nbr=sdh_dn_dn
      from huinan.mrb_mstr@huinanerp
      join huinan.mrbd_det@huinanerp on mrb_nbr = mrbd_nbr
      left join huinan.exd_det@huinanerp on exd_curr=mrbd_curr and mrb_date between exd_eff_date and exd_end_date
      where between2(mrb_vend,var_cust1,var_cust2)=1
        and between2(mrb_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(mrb_date,‘dd‘)>=var_date1 and trunc(mrb_date,‘dd‘)<=var_date2
        and mrb_prog_code=‘SLBKMTA1‘
        and mrb_pst = 1
        --and mrb_payment in (‘CP0A‘,‘CP09‘,‘CP07‘,‘CP08‘,‘CP12‘,‘CP13‘,‘CP01‘)
        --and sdh_crt_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)   -----金额管控从这个时间点开始
        ---客户退款单
      union all
      select crf_vend,crf_slspsn,
             0,0,0,0,0,0,0,0,
             0,
             0,
             0,
             0,
             0,
             0,
             crfd_price *crfd_qty * nvl(exd_rate,1)*(-1), --DMZ返款
             0,
             0,0,0,0,0,0,0,0
      --from sdh_hist join mrb_mstr on mrb_nbr=sdh_dn_dn
      from crf_mstr
      join crfd_det on crf_nbr = crfd_nbr
      left join exd_det on exd_curr=crfd_curr and crf_date between exd_eff_date and exd_end_date
      where between2(crf_vend,var_cust1,var_cust2)=1
        and between2(crf_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(crf_date,‘dd‘)>=var_date1 and trunc(crf_date,‘dd‘)<=var_date2
        and crf_prog_code=‘SLBKMTA2‘
        and crf_pst = 1
        and crf_type=‘005‘
      union all
      select crf_vend,crf_slspsn,
             0,0,0,0,0,0,0,0,
             0,
             0,
             0,
             0,
             0,
             0,
             crfd_price *crfd_qty * nvl(exd_rate,1)*(-1), --DMZ返款
             0,
             0,0,0,0,0,0,0,0
      --from sdh_hist join mrb_mstr on mrb_nbr=sdh_dn_dn
      from huinan.crf_mstr@huinanerp
      join huinan.crfd_det@huinanerp on crf_nbr = crfd_nbr
      left join huinan.exd_det@huinanerp on exd_curr=crfd_curr and crf_date between exd_eff_date and exd_end_date
      where between2(crf_vend,var_cust1,var_cust2)=1
        and between2(crf_slspsn,var_slspsn1,var_slspsn2)=1
        and trunc(crf_date,‘dd‘)>=var_date1 and trunc(crf_date,‘dd‘)<=var_date2
        and crf_prog_code=‘SLBKMTA2‘
        and crf_pst = 1
        and crf_type=‘005‘
      -----------------------------------------代客采购申购单工具费扣款----------------------------------------------------------------------
      union all
      select pr_cust,pr_req_by,
             0,0,0,0,0,0,0,0,
             0,
             0,
             0,
             0,
             0,
             0,
             0,
             req_price*req_qty* nvl(exd_rate,1), --已扣工具费
             0,0,0,0,0,0,0,0
      from pr_mstr
      join req_mstr on pr_nbr = req_pr_nbr 
      left join exd_det on exd_curr=pr_curr and pr_rel_date between exd_eff_date and exd_end_date
      where 1=1
      and between2(pr_cust,var_cust1,var_cust2)=1
      and between2(pr_req_by,var_slspsn1,var_slspsn2)=1
      and trunc(pr_rel_date,‘dd‘)>=var_date1 and trunc(pr_rel_date,‘dd‘)<=var_date2
      and pr_prog_code=‘PUPRMTA7‘
      and pr_pst = 1
      and pr_payment=‘CP05B‘
      union all
      select pr_cust,pr_req_by,
             0,0,0,0,0,0,0,0,
             0,
             0,
             0,
             0,
             0,
             0,
             0,
             req_price*req_qty* nvl(exd_rate,1), --已扣工具费
             0,0,0,0,0,0,0,0
      from huinan.pr_mstr@huinanerp
      join huinan.req_mstr@huinanerp on pr_nbr = req_pr_nbr 
      join huinan.cm_mstr@huinanerp on cm_addr = pr_cust
      left join huinan.exd_det@huinanerp on exd_curr=pr_curr and pr_rel_date between exd_eff_date and exd_end_date
      where 1=1
      and between2(pr_cust,var_cust1,var_cust2)=1
      and between2(pr_req_by,var_slspsn1,var_slspsn2)=1
      and trunc(pr_rel_date,‘dd‘)>=var_date1 and trunc(pr_rel_date,‘dd‘)<=var_date2
      and pr_prog_code=‘PUPRMTA7‘
      and pr_pst = 1
      and pr_payment=‘CP05B‘
      ------------------------------------------代客采购申购单工具费扣款 end-----------------------------------------------------------------  
      union all
      select b.certp_cust,b.certp_sp_slspsn,
             0,0,0,0,0,0,0,0,
             (case when b.certp_payment=‘CP0A‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--销售折扣加价
             (case when b.certp_payment=‘CP09‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--指定运费
             (case when b.certp_payment=‘CP07‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--开模费
             (case when b.certp_payment=‘CP08‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--认证费
             (case when b.certp_payment=‘CP12‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--已扣rom备件预留金
             (case when b.certp_payment=‘CP13‘ then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--已扣市场费
             (case when (b.certp_payment=‘CP01‘ and b.certp_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--已扣差异额度
             0,
             0,0,0,0,0,0,0,0
      from certpd_det a join certp_mstr b on b.certp_nbr=a.certpd_nbr
      where between2(b.certp_cust,var_cust1,var_cust2)=1
        and between2(b.certp_sp_slspsn,var_slspsn1,var_slspsn2)=1
        and b.certp_date>=var_date1 and b.certp_date<=var_date2
        and b.certp_prog_code=‘ADPAMTA1‘
        and b.certp_pst=1
        --and b.certp_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)   -----金额管控从这个时间点开始
      union all
      select b.certp_cust,b.certp_sp_slspsn,
             0,0,0,0,0,0,0,0,
             (case when b.certp_payment=‘CP0A‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--销售折扣加价
             (case when b.certp_payment=‘CP09‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--指定运费
             (case when b.certp_payment=‘CP07‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--开模费
             (case when b.certp_payment=‘CP08‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--认证费
             (case when b.certp_payment=‘CP12‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--已扣rom备件预留金
             (case when b.certp_payment=‘CP13‘ then a.certpd_cert_cost* huinan.GetExRate@huinanerp(b.certp_curr, b.certp_date) else 0 end),--已扣市场费
             (case when (b.certp_payment=‘CP01‘ and b.certp_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)) then a.certpd_cert_cost*GetExRate(b.certp_curr, b.certp_date) else 0 end),--已扣差异额度
             0,
             0,0,0,0,0,0,0,0
      from huinan.certpd_det@huinanerp a join huinan.certp_mstr@huinanerp b on b.certp_nbr=a.certpd_nbr
      where between2(b.certp_cust,var_cust1,var_cust2)=1
        and between2(b.certp_sp_slspsn,var_slspsn1,var_slspsn2)=1
        and b.certp_date>=var_date1 and b.certp_date<=var_date2
        and b.certp_prog_code=‘ADPAMTA1‘
        and b.certp_pst=1
        --and b.certp_pst_date >= to_date(‘2016-12-08 14:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)   -----金额管控从这个时间点开始
      union all
      ---------------------------------------ROM/DMZ扣款单------------------------------------------------------------------------------
      select adb_cust,adb_slspsn,
             0,0,0,0,0,0,0,0,
             (case when adb_payment in (‘010‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--销售折扣加价
             (case when adb_payment in (‘007‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--指定运费
             (case when adb_payment in (‘008‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--开模费
             (case when adb_payment in (‘009‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--认证费
             (case when adb_payment in (‘001‘,‘002‘,‘003‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--已扣rom备件预留金
             (case when adb_payment in (‘004‘,‘005‘) then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end), --已扣市场费均摊
             (case when adb_payment=‘006‘ then adb_amt *GetExRate(adb_curr , adb_date ) else 0 end),--已扣差异额度
             0,
             0,0,0,0,0,0,0,0
      from adb_mstr
      where 1=1
      and between2(adb_cust,var_cust1,var_cust2)=1
      and between2(adb_slspsn,var_slspsn1,var_slspsn2)=1
      and adb_pst = 1
      and adb_date>=var_date1 and adb_date<=var_date2
      -----------------------------------------ROM/DMZ预留费用分配-------------------------------------------------------------------------------------
      union all
      select adc_cust,adc_slspsn,
             adcd_list_amt,adcd_freight_amt,adcd_mould_amt,adcd_attest_amt,0,adcd_market_amt,0,adcd_tool_amt,
             0,--销售折扣加价
             0,--指定运费
             0,--开模费
             0,--认证费
             (case when adcd_type = ‘001‘ then (adcd_list_amt+adcd_freight_amt+adcd_mould_amt+adcd_attest_amt+adcd_market_amt+adcd_sales_amt+adcd_tool_amt) else 0 end),--已扣rom备件预留金
             0, --已扣市场费均摊
             (case when adcd_type = ‘002‘ then (adcd_list_amt+adcd_freight_amt+adcd_mould_amt+adcd_attest_amt+adcd_market_amt+adcd_sales_amt+adcd_tool_amt) else 0 end),--已扣差异额度
             0,
             0,0,0,0,0,0,0,0
      from adc_mstr
      left join adcd_det on adcd_nbr = adc_nbr
      where 1=1
      and between2(adc_cust ,var_cust1,var_cust2)=1
      and between2(adc_slspsn ,var_slspsn1,var_slspsn2)=1
      and adc_pst = 1
      and adc_date >= var_date1 and adc_date <= var_date2
      ----------------------------------------------运费扣款-------------------------------------------------------------------------
      union all
      select frb_cust,frb_slspsn,
             0,0,0,0,0,0,0,0,
             0,--销售折扣加价
             (case when (frbd_costype = ‘CP09‘ OR frbd_costype = ‘001‘) then frbd_feight_cost *GetExRate(frbd_curr, frb_date) else 0 end),--指定运费
             0,--开模费
             0,--认证费
             0,--已扣rom备件预留金
             0, --已扣市场费均摊
             0,--已扣差异额度
             0,
             0,0,0,0,0,0,0,0
      from frb_mstr
      left join frbd_det on frbd_nbr = frb_nbr
      where 1=1
      and between2(frb_cust ,var_cust1,var_cust2)=1
      and between2(frb_slspsn ,var_slspsn1,var_slspsn2)=1
      and frb_pst  = 1
      and frb_date >=var_date1 and frb_date <=var_date2
     ----------------------------------------------期初金额数------------------------------------------------------------------
     union all
     select gend_name,cm_slspsn,
            to_number(nvl(gend_property5,0)),to_number(nvl(gend_property4,0)),to_number(nvl(gend_property2,0)),to_number(nvl(gend_property3,0)),
            to_number(nvl(gend_property6,0)),to_number(nvl(gend_property7,0)),to_number(nvl(gend_property8,0)),to_number(nvl(gend_property9,0)),
            0,0,0,0,0,0,0,0,
            0,0,0,0,0,0,0,0
     from gend_det join cm_mstr on cm_addr=gend_name
     where 1=1
       and between2(gend_name,var_cust1,var_cust2)=1
       and between2(cm_slspsn,var_slspsn1,var_slspsn2)=1
       --and to_date(gend_property1,‘yyyymmdd‘) between var_date1 and var_date2
       and gend_gen=‘SOYU_BEGIN‘ and gend_disabled=0
    ) group by sdh_cust,sdh_so_slspsn;

   declare cursor cur_cust is
   select tpt_cust,tpt_slspsn from tpt_slbkso1;
   begin
   open cur_cust;
   loop
     fetch cur_cust into var_cust , var_slspsn;
     exit when cur_cust%notfound;


 --下单单价差异总金额
    select count(1),sum(mould_total),sum(attest_total),sum(freight_total),sum(market_total),sum(list_total),sum(rom_total),sum(diff_total),sum(tool_amt)
    into var_count,var_mould_amt,var_attest_amt,var_freight_amt,var_market_amt,var_list_amt,var_rom_amt,var_diff_amt,var_tool_amt
    from (
    select nvl(sod_mould_cost* sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*sod_qty_ord*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * SOD_QTY_ORD * so_ex_rate,0) as diff_total
       nvl(get_so_sub_price2(sod_nbr,sod_line) * so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) tool_amt
    from sod_det
    join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from so_mstr) on so_nbr=sod_nbr  and so_char1 is null
    left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
    left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
    where 1=1
        and so_cust = var_cust
        and so_slspsn = var_slspsn
        and so_ord_date>=var_date1
        and so_ord_date<=var_date2
        and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sod_status is null
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
    union all
    select nvl(sod_mould_cost* sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*sod_qty_ord*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * SOD_QTY_ORD * so_ex_rate,0) as diff_total
       nvl(huinan.get_so_sub_price2@huinanerp(sod_nbr,sod_line)  *so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*sod_qty_ord *so_ex_rate*nvl(exd_r_rate,1),0) tool_amt
    from huinan.sod_det@huinanerp
      join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr=sod_nbr and so_char1 is null
      left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where 1=1
        and so_cust = var_cust
        and so_slspsn = var_slspsn
        and so_ord_date>=var_date1 and so_ord_date<=var_date2
        and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and sod_status is null
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
     union all
     select nvl(sod_mould_cost* sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*sod_qty_shp*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * SOD_QTY_ORD * so_ex_rate,0) as diff_total
       nvl(get_so_sub_price2(sod_nbr,sod_line) * so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) tool_amt
    from sod_det
    join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from so_mstr) on so_nbr=sod_nbr  and so_char1 is null
    left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
    left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
    where 1=1
        and so_cust = var_cust
        and so_slspsn = var_slspsn
        and so_ord_date>=var_date1
        and so_ord_date<=var_date2
        and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        --and SOD_CLOSE_BY = ‘SYSTEM‘
        and sod_status = ‘C‘
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
    union all
    select nvl(sod_mould_cost* sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*sod_qty_shp*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price) * SOD_QTY_ORD * so_ex_rate,0) as diff_total
       nvl(huinan.get_so_sub_price2@huinanerp(sod_nbr,sod_line)  *so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*sod_qty_shp *so_ex_rate*nvl(exd_r_rate,1),0) tool_amt
    from huinan.sod_det@huinanerp
      join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr=sod_nbr and so_char1 is null
      left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
      left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
      where 1=1
        and so_slspsn = var_slspsn
        and so_cust = var_cust
        and so_ord_date>=var_date1
        and so_ord_date<=var_date2
        and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
        and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
        and so_pst=1 and so_prog_code=‘SLSOMTA1‘
        --and SOD_CLOSE_BY = ‘SYSTEM‘
        and sod_status = ‘C‘
        and (sod_mould_cost+sod_attest_cost+sod_freight_cost+sod_market_cost+sod_list_cost+sod_rom_cost+sod_adj_price+sod_sp_adj_price) <> 0
     union all
     select
       nvl(sod_mould_cost* CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*CRD_QTY_RTN*(-1)*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price)* CRD_QTY_RTN*(-1)*so_ex_rate,0) as diff_total
       nvl(get_so_nbr_price1(crd_cr,crd_line)* CRD_QTY_RTN*(-1)*so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0)
     from crd_det
     join sod_det on crd_so = sod_nbr and crd_so_line = sod_line
     left join cr_mstr on cr_cr=crd_cr
     left join so_mstr on so_nbr = sod_nbr
     left join pt_mstr on pt_part=sod_part
     left join gend_det on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
     left join exd_det on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
     where 1=1
     and cr_pst=1
     and so_char1 is null
     and so_ord_date >= var_date1
     and so_ord_date <= var_date2
     and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
     and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
     and so_slspsn = var_slspsn
     and so_cust = var_cust
    union all
    select
       nvl(sod_mould_cost* CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as mould_total,
       nvl(sod_attest_cost* CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as attest_total,
       nvl(sod_freight_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as freight_total,
       nvl(sod_market_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as market_total,
       nvl(sod_list_cost*CRD_QTY_RTN*(-1)*so_ex_rate*nvl(exd_r_rate,1),0) as list_total,
       nvl(sod_rom_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0) as rom_total,
       --nvl((sod_adj_price+sod_sp_adj_price+sod_top_adj_price)* CRD_QTY_RTN*(-1)*so_ex_rate,0) as diff_total
       nvl(huinan.get_so_nbr_price1@huinanerp(crd_cr,crd_line)* CRD_QTY_RTN*(-1)*so_ex_rate,0) as diff_total,
       nvl(sod_tool_cost*CRD_QTY_RTN*(-1) *so_ex_rate*nvl(exd_r_rate,1),0)
    from huinan.crd_det@huinanerp
    join huinan.sod_det@huinanerp on crd_so = sod_nbr and crd_so_line = sod_line
    left join huinan.cr_mstr@huinanerp on cr_cr=crd_cr
    left join (select so_nbr,so_cust,so_slspsn,so_ex_rate,so_ord_date,so_pst,so_prog_code,so_char1,SO_QUOTE_PROJECT from huinan.so_mstr@huinanerp) on so_nbr = sod_nbr
    left join huinan.pt_mstr@huinanerp on pt_part=sod_part
    left join huinan.gend_det@huinanerp on Gend_Option=SO_QUOTE_PROJECT and GEND_GEN=‘QUOTE_PROJECT‘
    left join huinan.exd_det@huinanerp on exd_curr=GEND_PROPERTY7 and so_ord_date between exd_eff_date and exd_end_date
   where 1=1
    and cr_pst=1
    and so_char1 is null
    and so_ord_date >= var_date1
    and so_ord_date <= var_date2
    and so_ord_date >= to_date(‘2016-06-01‘,‘yyyy-mm-dd‘)
    and ((so_nbr like ‘SOPA%‘ and so_nbr >= ‘SOPA1606‘)
       or (so_nbr like ‘SO%‘ and so_nbr not like ‘SOPA%‘ and so_nbr >= ‘SO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FGSO%‘ and so_nbr >= ‘FGSO1606‘)
       or (so_nbr like ‘GSO%‘ and so_nbr >= ‘GSO1606‘)
       or (so_nbr like ‘FSO%‘ and so_nbr >= ‘FSO1606‘)
       or (so_nbr like ‘PSO%‘ and so_nbr >= ‘PSO1606‘)
       or (so_nbr like ‘QSO%‘ and so_nbr >= ‘QSO1606‘)
       or (so_nbr like ‘ESO%‘ and so_nbr >= ‘ESO1606‘)
     )
    and so_slspsn = var_slspsn
    and so_cust = var_cust
        )
        ;

    select sum(adcd_list_amt),sum(adcd_freight_amt),sum(adcd_mould_amt),sum(adcd_attest_amt),sum(adcd_market_amt),sum(adcd_tool_amt)
      into var_list_amt1,var_freight_amt1,var_mould_amt1,var_attest_amt1,var_market_amt1,var_tool_amt1
      from adc_mstr
      left join adcd_det on adcd_nbr = adc_nbr
      where 1=1
      and adc_pst = 1
      and adc_cust = var_cust
      and adc_slspsn = var_slspsn
      and adc_date >= var_date1 and adc_date <= var_date2;

    update tpt_slbkso1 set
         TPT_MOULD_TOTAL = nvl(var_mould_amt,0)+nvl(var_mould_amt1,0),
         TPT_FREIGHT_TOTAL = nvl(var_freight_amt,0)+nvl(var_freight_amt1,0),
         TPT_ATTEST_TOTAL = nvl(var_attest_amt,0)+nvl(var_attest_amt1,0),
         TPT_LIST_TOTAL = nvl(var_list_amt,0)+nvl(var_list_amt1,0),
         TPT_ROM_TOTAL = nvl(var_rom_amt,0),
         TPT_MARKET_TOTAL = nvl(var_market_amt,0)+nvl(var_market_amt1,0),
         TPT_SOD_DIFF_TOTAL = nvl(var_diff_amt,0),
         TPT_TOOL_TOTAL = nvl(var_tool_amt,0)+nvl(var_tool_amt1,0)
    where 1=1
    and tpt_cust = var_cust and tpt_slspsn = var_slspsn;
    end loop;
    close cur_cust;
    end;

    /*  期初金额
      均摊费用期初金额(描述=客户代码;属性1=年月日;属性2=开模费均摊;属性3=认证费均摊;属性4=指定运费;
                                  属性5=销售折扣加价;属性6=ROM备件预留金;属性7=市场费均摊;属性8=差异总额)

     select to_number(nvl(gend_property2,0)),to_number(nvl(gend_property3,0)),to_number(nvl(gend_property4,0)),to_number(nvl(gend_property5,0)),
           to_number(nvl(gend_property6,0)),to_number(nvl(gend_property7,0)),to_number(nvl(gend_property8,0))
     into var_mould_amt2,var_attest_amt2,var_freight_amt2,var_list_amt2,var_rom_amt2,var_market_amt2,var_diff_amt2
     from gend_det join cm_mstr on cm_addr=gend_name
     where gend_name = var_cust
       and between2(cm_slspsn,var_slspsn1,var_slspsn2)=1
       and to_date(gend_property1,‘yyyymmdd‘) between var_date1 and var_date2
       and gend_gen=‘SOYU_BEGIN‘ and gend_disabled=0;*/
     merge into tpt_slbkso1
     using(
     select gend_name,to_number(nvl(gend_property2,0)) as mould_amt,to_number(nvl(gend_property3,0)) as attest_amt,
           to_number(nvl(gend_property4,0)) as freight_amt,to_number(nvl(gend_property5,0)) as list_amt,
           to_number(nvl(gend_property6,0)) as rom_amt,to_number(nvl(gend_property7,0)) as market_amt,to_number(nvl(gend_property8,0)) as diff_amt,
           to_number(nvl(gend_property9,0)) as tool_amt
     from gend_det join cm_mstr on cm_addr=gend_name
     where 1=1
       and between2(gend_name,var_cust1,var_cust2)=1
       and between2(cm_slspsn,var_slspsn1,var_slspsn2)=1
       --and to_date(gend_property1,‘yyyymmdd‘) between var_date1 and var_date2
       and gend_gen=‘SOYU_BEGIN‘ and gend_disabled=0
     ) t
     on (t.gend_name = tpt_cust)
     when matched then
       update set
         TPT_MOULD_TOTAL = TPT_MOULD_TOTAL + t.mould_amt,
         TPT_FREIGHT_TOTAL = TPT_FREIGHT_TOTAL + t.freight_amt,
         TPT_ATTEST_TOTAL = TPT_ATTEST_TOTAL + t.attest_amt,
         TPT_LIST_TOTAL = TPT_LIST_TOTAL + t.list_amt,
         TPT_ROM_TOTAL = TPT_ROM_TOTAL + t.rom_amt,
         TPT_MARKET_TOTAL = TPT_MARKET_TOTAL + t.market_amt,
         TPT_SOD_DIFF_TOTAL = TPT_SOD_DIFF_TOTAL + t.diff_amt,
         TPT_TOOL_TOTAL =  TPT_TOOL_TOTAL + t.tool_amt;

   /*open resultcursor for
     select t.* ,cm_sort,usr_name,‘RMB‘ as tpt_curr,
            t.tpt_sod_list_amt-t.tpt_mrbd_list_amt as sub_list_amt,
            t.tpt_sod_freight_amt-t.tpt_mrbd_freight_amt as sub_freihgt_amt,
            t.tpt_sod_mould_amt-t.tpt_mrbd_mould_amt as sub_mould_amt,
            t.tpt_sod_attest_amt-t.tpt_mrbd_attest_amt as sub_attest_amt ,
            t.tpt_sod_rom_amt-t.tpt_mrbd_rom_amt as sub_rom_amt,
            t.tpt_sod_market_amt-t.tpt_mrbd_market_amt as sub_market_amt,
            t.tpt_sod_diff_amt - TPT_MRBD_DIFF_AMT as sub_diff_amt,

            TPT_LIST_TOTAL as sod_list_amt1,
            TPT_FREIGHT_TOTAL as sod_freight_amt1,
            TPT_MOULD_TOTAL as sod_mould_amt1,
            TPT_ATTEST_TOTAL as sod_attest_amt1,
            TPT_ROM_TOTAL as sod_rom_amt1,
            TPT_MARKET_TOTAL as sod_market_amt1,
            TPT_SOD_DIFF_TOTAL as sod_diff_amt1,

            TPT_LIST_TOTAL -t.tpt_mrbd_list_amt as sub_list_amt1,
            TPT_FREIGHT_TOTAL-t.tpt_mrbd_freight_amt as sub_freihgt_amt1,
            TPT_MOULD_TOTAL-t.tpt_mrbd_mould_amt as sub_mould_amt1,
            TPT_ATTEST_TOTAL-t.tpt_mrbd_attest_amt as sub_attest_amt1,
            TPT_ROM_TOTAL-t.tpt_mrbd_rom_amt as sub_rom_amt1,
            TPT_MARKET_TOTAL-t.tpt_mrbd_market_amt as sub_market_amt1,
            TPT_SOD_DIFF_TOTAL-t.tpt_mrbd_diff_amt as sub_diff_amt1
     from tpt_slbkso1 t
     left join cm_mstr on cm_addr=t.tpt_cust
     left join usr_mstr on usr_user=t.tpt_slspsn
where 1=1*/
   return;
exception
  when others then rollback;
end slbksoiqa2;

  

Oracle 销售统计报表 实例