三算对比视图

create or replace view v_bpm_costtrianalysis as
select pk_corp,
pk_project,
pk_stattime,
pk_costdef,
nvl(sum(insumnum), 0) insumnum,
nvl(sum(insummny), 0) insummny,
nvl(sum(costnum), 0) costsumnum,
nvl(sum(costmny), 0) costsummny,
nvl(sum(realnum), 0) realsumnum,
nvl(sum(realmny), 0) realsummny
from ((select e.pk_corp,
e.pk_project,
e.pk_stattime,
f.pk_costdef,
sum(Nboverworknum) insumnum,
sum(Nbworkbasemny) insummny,
0 costnum,
0 costmny,
0 realnum,
0 realmny
from (select a.pk_valrep_list,
a.pk_contbill,
a.pk_valuereport,
c.pk_corp,
c.pk_project,
c.pk_stattime,
a.Nboverworknum,
a.Nbworkbasemny
from bpm_in_valrep_list a
left outer join (select b.pk_corp,
b.pk_project,
b.pk_valuereport,
b.pk_stattime,
b.dr
from bpm_in_valrep b) c on a.pk_valuereport =
c.pk_valuereport
where a.dr = 0
and c.dr = 0) e
left outer join (select d.pk_contlist_real, d.pk_costdef
from bpm_cm_contlist_real d) f on e.pk_contbill =
f.pk_contlist_real
group by e.pk_corp, e.pk_project, e.pk_stattime, f.pk_costdef)
union all
(select m.pk_corp,
m.pk_project,
m.pk_stattime,
m.pk_costdef,
0 innum,
0 inmny,
sum(napprovenum) costnum,
sum(napprovemny) costmny,
0 realnum,
0 realmny
from (select k.pk_corp,
k.pk_project,
k.pk_stattime,
k.pk_costdef,
k.napprovenum,
k.napprovemny
from (select i.pk_costfsh_b,
i.pk_costfsh,
j.pk_corp,
j.pk_project,
i.pk_costdef,
j.pk_stattime,
i.napprovenum,
i.napprovemny
from bpm_pc_costfsh_b i
left outer join bpm_pc_costfsh j on i.pk_costfsh =

j.pk_costfsh
where i.dr = 0
and j.vbillstatus = '1'
and j.dr = 0) k) m
group by m.pk_corp, m.pk_project, m.pk_stattime, m.pk_costdef)
union all
(select pk_corp,
pk_project,
pk_stattime,
pk_costdef,
0 innum,
0inmny,
0costnum,
0costmny,
0 realnum,
sum(mny) realmny
from ((select o.pk_corp,
o.pk_project,
o.pk_stattime,
n.pk_costdef,
n.nfeemny mny
from bpm_pc_factbill_b n
left outer join bpm_pc_factbill o on n.pk_factbill =
o.pk_factbill
where n.dr = 0
and o.dr = 0
and o.vbillstatus = '1') union all
(select q.pk_corp,
q.pk_project,
q.pk_stattime,
p.pk_costdef,
p.nfeemny mny
from bpm_pc_factcont_bil p
left outer join bpm_pc_factcont q on p.pk_factcont =
q.pk_factcont
where p.dr = 0
and q.dr = 0
and q.vbillstatus = '1') union all
(select s.pk_corp,
s.pk_project,
s.pk_stattime,
r.pk_costdef,
r.nbasemny mny
from bpm_pc_factcont_div r
left outer join bpm_pc_factcont s on r.pk_factcont =
s.pk_factcont
where r.dr = 0
and s.dr = 0
and s.vbillstatus = '1') union all
(select t.pk_corp,
t.pk_project,
u.pk_projperiod_b pk_stattime,
t.pk_costdef,
t.thismoney mny
from bpm_mt_settle t
left outer join bpm_pc_factleamch u on t.pk_factleamch =
u.pk_factleamch
where t.dr = 0
and u.dr = 0) union all
(select w.pk_corp,
w.pk_project,
w.pk_projperiod_b pk_stattime,
v.pk_costdef,
v.thismoney mny
from bpm_circul_settle v
left outer join bpm_pc_factleasmt w on v.pk_factleasmt =

w.pk_factleasmt
where v.dr = 0
and w.dr = 0) union all
(select y.pk_corp,
y.pk_project,
y.pk_stattime,
x.pk_costdef,
x.factbillmoney mny
from bpm_mt_factbillinfo x
left outer join bpm_mt_factbill y on x.pk_mt_factbill =
y.pk_mt_factbill
where x.dr = 0
and y.dr = 0
and y.vbillstatus = '1'))
group by pk_corp, pk_project, pk_stattime, pk_costdef)) base
group by pk_corp, pk_project, pk_stattime, pk_costdef