declare
	V_COMMAND  varchar2(5000);
begin


V_COMMAND := 'Create or replace view Challan_view as 
		 select  qry1.dcno,qry1.dcdate,qry1.customername,qry1.perscode,qry1.persname,qry1.commission,qry1.netrate,
	   qry1.itemcode,qry1.itemname,qry1.itemunit,qry1.dcrate,qry1.dcqty,qry1.disc_pers,qry1.disc_amount,
	   qry1.tax_perc,qry1.tax_amount,qry1.netamount,qry1.chk,
	   qry2.idcqty, nvl(qry1.dcqty,0) - nvl(qry2.idcqty,0) Remaining
 from 

(select a.dcno,a.dcdate,a.customername,a.perscode,a.persname,a.commission,a.netrate,
	   b.itemcode,b.itemname,b.itemunit,b.dcrate,b.dcqty,b.disc_pers,b.disc_amount,
	   b.tax_perc,b.tax_amount,b.netamount,b.chk
	   from masterdc a , dc_detail b
	   where a.dcno = b.dcno) qry1,
	   
(select b.dcno, b.dcdate,b.itemcode,b.itemname,
	   b.dcqty IDCQTY
	   from masterinv a , inv_detail b
	   where a.invno = b.invno) qry2
	   
where qry1.dcno = qry2.dcno (+)
--and qry1.itemcode = qry2.itemcode(+)
and nvl(qry2.idcqty,0) < nvl(qry1.dcqty,0)';
FORMS_DDL(V_COMMAND);
end;

if :challan1_view.chk = 'Y' then 
update challan_view
set chk ='Y';
end if;