create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60'  then 
      case when a.ccn <> '091' then 
           nvl(sum(a.quantity),0) else 0 
      End else 0
 End) from_plant,
(case when a.card_code = '60' then
      case when a.ccn = '091' then
           nvl(sum(a.quantity),0) else 0 
      End else 0
End) from_party,a.document_date
from kardex a,kardex_bags d
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.document_date < '01/aug/2012'
and to_date(a.document_date) between add_months('01/aug/2012',-1) and last_day('01/aug/2012')
and a.item_code= d.item_code(+)
group by a.item_code,a.uom,a.card_code,a.ccn,a.document_date
union
select item_code Material_Code,''  uom,0 from_plant ,0 from_party ,null from kardex_bags where item_code not in (
select item_code from kardex a
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.document_date < '01/aug/2012'
and to_date(a.document_date) between add_months('01/aug/2012',-1) and last_day('01/aug/2012'))

SQL> create or replace view v as
  2  select * from emp
  3  where deptno = sys_context('userenv','client_info')
  4  /

View created.

SQL> exec dbms_application_info.set_client_info ('10');

PL/SQL procedure successfully completed.

SQL> select * from v;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

3 rows selected.

SQL> exec dbms_application_info.set_client_info ('20');

PL/SQL procedure successfully completed.

SQL> select * from v;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20

5 rows selected.

create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60'  then 
      case when a.ccn <> '091' then 
           nvl(sum(a.quantity),0) else 0 
      End else 0
 End) from_plant,
(case when a.card_code = '60' then
      case when a.ccn = '091' then
           nvl(sum(a.quantity),0) else 0 
      End else 0
End) from_party,a.document_date
from kardex a,kardex_bags d
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code)
and a.item_code= d.item_code(+)
group by a.item_code,a.uom,a.card_code,a.ccn,a.document_date
union
select item_code Material_Code,''  uom,0 from_plant ,0 from_party ,null from kardex_bags where item_code not in (
select item_code from kardex a
where exists (select b.item_code from kardex_bags b where b.item_code = a.item_code);