CREATE TABLE ord
(SNO_REQ_DATE		DATE,
SNO_ORD_REF		VARCHAR2(10),
SNO_ORD_TRACER		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', 'BD100', 1111, 1, 7311, 200, 2000.00
from dual;
commit;

drop table inv;

CREATE TABLE inv
 (SNI_CON_INV_DATE	DATE,
SNI_INV_TRACER		NUMBER(8,0),
SNI_ORD_TRACER		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_REQ_DATE		DATE,
SN_CON_INV_DATE		DATE,
SN_ORD_REF		VARCHAR2(10),
SN_ORD_TRACER		NUMBER(8,0),
SN_INV_TRACER		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_req_date, sni_con_inv_date, sno_ord_ref, sno_ord_tracer, sni_inv_tracer, 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;