select * from
2 (
3 select orgname,
max(substr(t2.code_version,
1)) CODE_VERSION
from
4 (
5 SELECT tb_cube_fc05.pk_entity pk_org,
/*主体pk*/
6 org_orgs.code orgcode,
/*主体编码*/
7 org_orgs.name orgname,
/*主体名称*/
8 tb_cube_fc05.pk_year pk_year,
/*年份*/
9 tb_cube_fc05.pk_month pk_month,
/*月份*/
10 tb_cube_fc05.pk_year
||‘-‘||tb_cube_fc05.pk_month period,
/*期间*/
11 sum(nvl(tb_cube_fc05.value,
0)) totalcost,
/*费用总额*/
12 tb_cube_fc05.pk_aimcurr pk_currtype,
/*币种pk*/
13 bd_currtype.code currcode,
/*币种编码*/
14 bd_currtype.name currname
/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
15 FROM tb_cube_fc05 tb_cube_fc05
inner join tb_budgetsub tb_budgetsub
on tb_budgetsub.pk_obj
=tb_cube_fc05.pk_measure
16 LEFT JOIN tb_dataattr tb_dataattr
17 ON (tb_cube_fc05.pk_mvtype
= tb_dataattr.pk_obj)
18 LEFT JOIN org_dept org_dept
19 ON (tb_cube_fc05.pk_entity
= org_dept.pk_dept)
20 LEFT JOIN org_orgs org_orgs
21 ON (tb_cube_fc05.pk_entity
= org_orgs.pk_org)
22 LEFT JOIN bd_defdoc bd_defdoc
23 ON (tb_cube_fc05.pk_fc01
= bd_defdoc.pk_defdoc)
24 left join bd_currtype
on bd_currtype.pk_currtype
=tb_cube_fc05.pk_aimcurr
25 where tb_cube_fc05.CODE_MVTYPE
=‘Budget‘ and tb_dataattr.objname
=‘预算数‘ and tb_budgetsub.objcode
=‘FC701‘ --and org_orgs.code=‘232‘ --F1169 F1001
26 -- and substr(tb_cube_fc05.code_version,1) in( select max(substr(code_version,1)) from tb_cube_fc05 )
27 -- and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
28 -- tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
29 group by tb_cube_fc05.pk_entity ,
/*主体pk*/
30 org_orgs.code ,
/*主体编码*/
31 org_orgs.name ,
/*主体名称*/
32 tb_cube_fc05.pk_year ,
/*年份*/
33 tb_cube_fc05.pk_month ,
/*月份*/
34 nvl(tb_cube_fc05.value,
0) ,
/*费用总额*/
35 tb_cube_fc05.pk_aimcurr ,
/*币种pk*/
36 bd_currtype.code ,
/*币种编码*/
37 bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
38 )t2
group by t2.orgname
--org_orgs.orgname in (select distinct org_orgs.orgname from org_orgs)
39 )d1
40 left join
41 (
42 SELECT tb_cube_fc05.pk_entity pk_org,
/*主体pk*/
43 org_orgs.code orgcode,
/*主体编码*/
44 org_orgs.name orgname,
/*主体名称*/
45 tb_cube_fc05.pk_year pk_year,
/*年份*/
46 tb_cube_fc05.pk_month pk_month,
/*月份*/
47 tb_cube_fc05.pk_year
||‘-‘||tb_cube_fc05.pk_month period,
/*期间*/
48 sum(nvl(tb_cube_fc05.value,
0)) totalcost,
/*费用总额*/
49 tb_cube_fc05.pk_aimcurr pk_currtype,
/*币种pk*/
50 bd_currtype.code currcode,
/*币种编码*/
51 bd_currtype.name currname
/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
52 FROM tb_cube_fc05 tb_cube_fc05
inner join tb_budgetsub tb_budgetsub
on tb_budgetsub.pk_obj
=tb_cube_fc05.pk_measure
53 LEFT JOIN tb_dataattr tb_dataattr
54 ON (tb_cube_fc05.pk_mvtype
= tb_dataattr.pk_obj)
55 LEFT JOIN org_dept org_dept
56 ON (tb_cube_fc05.pk_entity
= org_dept.pk_dept)
57 LEFT JOIN org_orgs org_orgs
58 ON (tb_cube_fc05.pk_entity
= org_orgs.pk_org)
59 LEFT JOIN bd_defdoc bd_defdoc
60 ON (tb_cube_fc05.pk_fc01
= bd_defdoc.pk_defdoc)
61 left join bd_currtype
on bd_currtype.pk_currtype
=tb_cube_fc05.pk_aimcurr
62 where tb_cube_fc05.CODE_MVTYPE
=‘Budget‘ and tb_dataattr.objname
=‘预算数‘ and tb_budgetsub.objcode
=‘FC701‘ --and org_orgs.code=‘232‘ --F1169 F1001
63
64
65
66 -- and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
67 -- tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
68 group by
69
70 tb_cube_fc05.pk_entity ,
/*主体pk*/
71 org_orgs.code ,
/*主体编码*/
72 org_orgs.name ,
/*主体名称*/
73 tb_cube_fc05.pk_year ,
/*年份*/
74 tb_cube_fc05.pk_month ,
/*月份*/
75 nvl(tb_cube_fc05.value,
0) ,
/*费用总额*/
76 tb_cube_fc05.pk_aimcurr ,
/*币种pk*/
77 bd_currtype.code ,
/*币种编码*/
78 bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
79 order by org_orgs.name,tb_cube_fc05.pk_month
80
81 )d2
on d1.orgname
=D2.orgname
and d1.CODE_VERSION
=d2.code_version
预算oracle
标签：
