-------预算--
CREATE OR REPLACE VIEW V_YUSUAN_BGY_WZ20151204 AS
SELECT tb_cube_fc05.pk_entity pk_org,/*主体pk*/
org_orgs.code orgcode,/*主体编码*/
org_orgs.name orgname,/*主体名称*/
tb_cube_fc05.pk_year pk_year,/*年份*/
tb_cube_fc05.pk_month pk_month,/*月份*/
tb_cube_fc05.pk_year||'-'||tb_cube_fc05.pk_month period,/*期间*/
sum(nvl(tb_cube_fc05.value,0)) totalcost,/*费用总额*/
tb_cube_fc05.pk_aimcurr pk_currtype,/*币种pk*/
bd_currtype.code currcode,/*币种编码*/
bd_currtype.name currname/*币种名称*/,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts
FROM tb_cube_fc05 tb_cube_fc05 inner join tb_budgetsub tb_budgetsub on tb_budgetsub.pk_obj=tb_cube_fc05.pk_measure
LEFT JOIN tb_dataattr tb_dataattr
ON (tb_cube_fc05.pk_mvtype = tb_dataattr.pk_obj)
LEFT JOIN org_dept org_dept
ON (tb_cube_fc05.pk_entity = org_dept.pk_dept)
LEFT JOIN org_orgs org_orgs
ON (tb_cube_fc05.pk_entity = org_orgs.pk_org)
LEFT JOIN bd_defdoc bd_defdoc
ON (tb_cube_fc05.pk_fc01 = bd_defdoc.pk_defdoc)
left join bd_currtype on bd_currtype.pk_currtype=tb_cube_fc05.pk_aimcurr
where tb_cube_fc05.CODE_MVTYPE='Budget' and tb_dataattr.objname='预算数' and tb_budgetsub.objname='管理（口径）费用总额' and org_orgs.code='F1001' --F1169 F1001
and substr(tb_cube_fc05.code_version,1) in( select max(substr(code_version,1)) from tb_cube_fc05 )
-- and exists(select ts from (select max(ts) ts from tb_cube_fc05 group by tb_cube_fc05.pk_year ,/*年份*/
-- tb_cube_fc05.pk_month )t where t.ts=tb_cube_fc05.ts)
group by tb_cube_fc05.pk_entity ,/*主体pk*/
org_orgs.code ,/*主体编码*/
org_orgs.name ,/*主体名称*/
tb_cube_fc05.pk_year ,/*年份*/
tb_cube_fc05.pk_month ,/*月份*/
nvl(tb_cube_fc05.value,0) ,/*费用总额*/
tb_cube_fc05.pk_aimcurr ,/*币种pk*/
bd_currtype.code ,/*币种编码*/
bd_currtype.name,tb_budgetsub.objname,tb_cube_fc05.code_version,tb_cube_fc05.ts ---费用调整单
create or replace view v_fyadjust_byg_wz as
select distinct zh.djlxbm,/*费用类型编码*/
tb.billtypename,/*费用类型名称*/
zh.bzbm,/*币种pk*/
cu.name bzname,/*币种名称*/
z.assume_org,/*组织主体pk*/
og.name orgname,/*组织主体name*/
z.assume_amount,/*承担金额*/
z.ysdate,/*费用入账日期*/
z.defitem16,/*费用类型*/
z.defitem27 from er_cshare_detail z inner join er_bxzb zh on z.pk_jkbx=zh.pk_jkbx
left join bd_billtype tb on tb.pk_billtypecode=zh.djlxbm and nvl(tb.dr,0)=0
left join bd_currtype cu on cu.pk_currtype=zh.bzbm and nvl(cu.dr,0)=0
left join org_orgs og on og.pk_org=z.assume_org and nvl(og.dr,0)=0
where zh.djlxbm in('264a','264X-Cxx-0009') and nvl(z.dr,0)=0 and nvl(zh.dr,0)=0; ---采购发票
create or replace view v_po_invoice_bgy_wz as
select pi.pk_org,/*主体pk*/
og.name ogrname,/*主体名称*/
pi.pk_busitype,/*业务流程pk*/
bt.businame,/*业务流程名称*/
pi.dbilldate,/*单据日期*/
sum(nvl(pib.norigtaxmny,0)) norigtaxmny,/*价税合计金额*/
pi.corigcurrencyid,/*币种pk*/cy.name curryname,/*币种名称*/
pi.vmemo,
--pib.vfirstcode,/*来源单据号*/
pi.taudittime provedate,/*审批日期*/
pib.vbdef2 costtype, /*费用类型*/
pib.vbdef3 applydetp /*请购部门*/
from po_invoice pi
inner join po_invoice_b pib
on pi.pk_invoice = pib.pk_invoice
and nvl(pi.dr, 0) = 0
and nvl(pib.dr, 0) = 0
inner join org_orgs og on og.pk_org=pi.pk_org
left join bd_busitype bt
on bt.pk_busitype = pi.pk_busitype
left join bd_currtype cy on cy.pk_currtype=pi.corigcurrencyid
and nvl(bt.dr, 0) = 0
group by pi.pk_org,og.name , pi.pk_busitype,
bt.businame,
pi.corigcurrencyid,cy.name,
pi.vmemo,
--pib.vfirstcode,
pi.taudittime,
pib.vbdef2, pi.dbilldate,
pib.vbdef3; ---采购订单
create or replace view v_po_order_bgy_wz as
select po.pk_busitype,/*业务流程pk*/
bt.businame,/*业务流程名称*/
po.pk_dept,/*采购部门pk*/
de.name deptname,/*采购部门名称*/
de.pk_org,/*采购主体pk*/
og.name orgname, /*采购主体名称*/
pb.vbdef3/*费用类型*/,
pb.norigtaxmny,/*价税合计*/
po.vmemo,
--pb.vsourcecode,/*来源单号*/
po.taudittime /*审批日期*/
from po_order po inner join po_order_b pb on po.pk_order=pb.pk_order and nvl(po.dr,0)=0 and nvl(pb.dr,0)=0
left join org_dept de on de.pk_dept=po.pk_dept and nvl(de.dr,0)=0
left join bd_busitype bt on bt.pk_busitype=po.pk_busitype and nvl(bt.dr,0)=0
left join org_orgs og on og.pk_org=de.pk_org and nvl(og.dr,0)=0;