月度三层预测数据

--不包括区公司有但是订单部没有预测的品牌
select decode(d.menuid,14,'低档烟',decode(c.statguid,1,'1',5,'5','省外烟')) ftype,c.cigacode,c.ciganame,c.barbarcode,
       sum(decode(a.ftype,4,a.adjustamount,0))/5 manager,
       sum(decode(a.ftype,6,a.adjustamount,0))/5 market,
       sum(decode(a.ftype,8,a.adjustamount,0)) comp
from (
select 4 ftype,tobaid,adjustamount
from mmcforecli
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0
union all
select 6 ftype,tobaid,adjustamount
from mmcforemar
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0 and submited=2
union all
select 8 ftype,tobaid,adjustamount
from mmcforecomp
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0 and submited=1
) a,mmcompcigamanage b,mmcigainfo c,(select * from ciga2menu where menuid=14) d
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid and c.mmcigainfoid=d.cigaid(+)
and exists (
    select 0 from mmcforeorder md
    where md.cigainfo=c.mmcigainfoid and md.status=1 and md.deletedflag=0
    and to_char(md.foredate,'yyyymm')='&yyyymm')
group by decode(d.menuid,14,'低档烟',decode(c.statguid,1,'1',5,'5','省外烟')),c.cigacode,c.ciganame,c.barbarcode

--市场部门月度货源需求预测提报表
select decode(d.menuid,14,'低档烟',decode(c.statguid,1,'1',5,'1','省外烟')) ftype,
       c.ciganame,c.barbarcode,
       sum(decode(a.ftype,4,a.adjustamount,0))/250 manager,
       sum(decode(a.ftype,6,a.adjustamount,0))/5 market,
       sum(decode(a.ftype,8,a.adjustamount,0)) comp
from (
select 4 ftype,tobaid,adjustamount
from mmcforecli
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0
union all
select 6 ftype,tobaid,adjustamount
from mmcforemar
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0 and submited=2
union all
select 8 ftype,tobaid,adjustamount
from mmcforecomp
where to_char(foredate,'yyyymm')='&yyyymm' and deletedflag=0 and submited=1
) a,mmcompcigamanage b,mmcigainfo c,(select * from ciga2menu where menuid=14) d
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid and c.mmcigainfoid=d.cigaid(+)
group by decode(d.menuid,14,'低档烟',decode(c.statguid,1,'1',5,'1','省外烟')),
         c.ciganame,c.barbarcode

--区公司有订单部没有的预测数据
select mc.cigacode,mc.ciganame,mc.barbarcode,sum(mp.adjustamount) adjustamount
from mmcforecomp mp,mmcompcigamanage me,mmcigainfo mc
where mp.tobaid=me.mmcompcigamanageid and me.cigaid=mc.mmcigainfoid
and mp.deletedflag=0 and mp.submited=1
and to_char(mp.foredate,'yyyymm')=&yf
and not exists (
    select 0 from mmcforeorder md
    where md.cigainfo=mc.mmcigainfoid and md.status=1 and md.deletedflag=0
    and to_char(md.foredate,'yyyymm')=&yf)
group by mc.statguid,mc.cigacode,mc.ciganame,mc.barbarcode

 

--客户经理月预测汇总
select c.cigacode 卷烟代码,c.ciganame 卷烟名称,sum(a.adjustamount)/5 "预测量（箱）"
from mmcforemgr a,mmcompcigamanage b,mmcigainfo c
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid
and to_char(a.foredate,'yyyymm')='&yyyymm' and a.submited=2 and a.deletedflag=0
group by c.cigacode,c.ciganame
order by c.ciganame

--市场部月预测汇总
select c.cigacode 卷烟代码,c.ciganame 卷烟名称,sum(a.adjustamount)/5 "预测量（箱）"
from mmcforemar a,mmcompcigamanage b,mmcigainfo c
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid
and a.submited=2 and a.deletedflag=0
and to_char(a.foredate,'yyyymm')='&yyyymm'
group by c.cigacode,c.ciganame
order by c.ciganame

--区公司月预测汇总
select c.cigacode 卷烟代码,c.ciganame 卷烟名称,sum(a.adjustamount) "预测量（箱）"
from mmcforecomp a,mmcompcigamanage b,mmcigainfo c,bizentity d
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid and a.orgid=d.bizentityid
and a.submited=1 and a.deletedflag=0
and to_char(a.foredate,'yyyymm')='&yyyymm'
group by c.cigacode,c.ciganame
order by c.ciganame

--区公司月预测明细
select d.name 区公司,c.cigacode 卷烟代码,c.ciganame 卷烟名称,sum(a.adjustamount) "预测量（箱）"
from mmcforecomp a,mmcompcigamanage b,mmcigainfo c,bizentity d
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid and a.orgid=d.bizentityid
and a.submited=1 and a.deletedflag=0
and to_char(a.foredate,'yyyymm')='&yyyymm'
group by d.name,c.cigacode,c.ciganame

--市场部月预测明细
select d.name 市场部,c.cigacode 卷烟代码,c.ciganame 卷烟名称,sum(a.adjustamount)/5 "预测量（箱）"
from mmcforemar a,mmcompcigamanage b,mmcigainfo c,bizentity d
where a.tobaid=b.mmcompcigamanageid and b.cigaid=c.mmcigainfoid and a.orgid=d.bizentityid
and a.submited=2 and a.deletedflag=0
and to_char(a.foredate,'yyyymm')='&yyyymm'
group by d.name,c.cigacode,c.ciganame