CREATE TABLE ord
(SNO_REQUIRED_DATE		DATE,
SNO_ORD_NO		NUMBER(8,0),
SNO_LINE_NO		NUMBER(4,0),
SNO_ITEMCODE		VARCHAR2(10),
SNO_ORD_QTY		NUMBER(12,2),
SNO_ORD_VAL		NUMBER(12,2));
commit;

insert into ord
select '13-OCT-07', 1111, 1, 7311, 200, 2000.00
from dual;
commit;

drop table inv;

CREATE TABLE inv
 (SNI_INVOICE_DATE	DATE,
SNI_INV_NO		NUMBER(8,0),
SNI_ORD_NO		NUMBER(8,0),
SNI_LINE_NO		NUMBER(4,0),
SNI_ITEMCODE		VARCHAR2(10),
SNI_INV_QTY		NUMBER(12,2),
SNI_INV_VAL		NUMBER(12,2));
commit;

insert into inv
select '27-SEP-07', 2222, 1111, 1, 7311, 100, 1000.00
from dual;
insert into inv
select '13-OCT-07', 2223, 1111, 1, 7311, 72, 720.00
from dual;
commit; 

drop table cs1;

CREATE TABLE cs1
(SN_REQUIRED_DATE		DATE,
SN_INVOICE_DATE		DATE,
SN_ORD_NO		NUMBER(8,0),
SN_INV_NO		NUMBER(8,0),
SN_ITEMCODE		VARCHAR2(10),
SN_ORD_QTY		NUMBER(12,2),
SN_ORD_VAL		NUMBER(12,2),
SN_INV_QTY		NUMBER(12,2),
SN_INV_VAL		NUMBER(12,2));
commit;

insert into cs1
select sno_required_date, sni_invoice_date, 
sno_ord_no, sni_inv_no, sno_itemcode, sno_ord_qty, 
sum(sno_ord_val), sum(sni_inv_qty), sum(sni_inv_val)
from ord, inv
where sno_ord_tracer = sni_ord_tracer(+)
and sno_line_no = sni_line_no(+)
group by sno_req_date, sni_con_inv_date, sno_ord_ref, 
sno_ord_tracer, sni_inv_tracer, sno_itemcode, sno_ord_qty;
commit;

insert into cs1 
select sno_required_date, sni_invoice_date, sno_ord_no, 
sni_inv_no, sno_itemcode, sno_ord_qty, sum(sno_ord_val),
 sum(sni_inv_qty), sum(sni_inv_val) 
from ord, inv 
where sno_ord_no = sni_ord_no(+) 
and sno_line_no = sni_line_no(+) 
group by sno_required_date, sni_invoice_date, sno_ord_no,
 sni_inv_no, sno_itemcode, sno_ord_qty

select sno_ord_no, SNI_INV_NO, SNI_inv_QTY, sni_inv_val, sni_inv_qty, sni_inv_val
from ord, inv
where sno_ord_no = sni_ord_no(+)
and sno_line_no = sni_line_no(+)
union all
select sno_ord_no, null, sno_ord_qty - sum(sni_inv_qty) , sno_ord_val - sum (sni_inv_val), NULL, NULL
from ord, inv
where sno_ord_no = sni_ord_no(+)
and sno_line_no = sni_line_no(+)
group by sno_ord_no, sno_ord_qty, sno_ord_val