视图的使用

1、

/*

*创建报表视图

*/

create or replace view vm_claim_countcashrptdata as

select

       --事业部财务部

       (select bus.finasyscode

           from t_org_department bus

          where bus.status = 1 and bus.id =

                ((select d1.parentid from t_org_department d1 where d1.status = 1 and d1.finasyscode = (select d.finorgnum

                   from t_fins_dept2fin d

                  where d.effect = 1 and d.deptnum = decode(foss.dept, null, v.dept))))) as BUSIORGNUM,

       --财务部

        (select d.finorgnum

           from t_fins_dept2fin d

          where d.effect = 1 and d.deptnum = decode(foss.dept, null, v.dept)) as FINORGNUM,

       --营业部

       decode(foss.dept, null, v.dept) as DEPTNUM,

       --(case when foss.dept is null then v.dept else foss.dept end)as dept,

       --现金收银金额

       nvl(foss.amt, 0) CASHAMT,

       --汇款金额

       nvl(v.amt, 0) REMITAMT,

       --累计收银金额

       nvl(rpt.totalcashamt, 0) TOTALCASHAMT,

       --累计未汇款

       nvl(rpt.totalunremitamt, 0) TOTALUNREMITAMT

  from (

        --汇总现金缴款数据

        select sum(nvl(f.cashamt, 0)) amt,

                --营业部

                f.depthnum dept

          from t_claim_fosscashdata f

         group by f.depthnum) foss

  full join vm_claim_remittancerecord v on v.dept = foss.dept

  left join T_CLAIM_CASHCHECKRPT rpt on rpt.deptnum = decode(foss.dept, null, v.dept)

       and rpt.rptdate = trunc(sysdate-2)

  WITH READ ONLY ;

2、