create or replace procedure order_Det_insert is
cursor c1 is select order_id from xxtro_orders;
c_order xxtro_orders.ORDER_ID%type;
quantity number(10,2):=5;
rate number(10,2):=2;
begin
open c1;
loop
fetch c1 into c_order;
EXIT WHEN c1%NOTFOUND;
for i in 1..3 loop
insert into order_details (ORDER_DET_ID,ORDER_ID,PRODUCT_ID,QTY,RATE,AMOUNT)
select 'OD'||TO_CHAR(LPAD(NVL(MAX(SUBSTR(order_det_id,3)),0)+1,3,0)) ORDER_DET_ID,
xxtro_orders.order_ID ORDER_ID,'P'||TO_CHAR(LPAD(NVL(MAX(SUBSTR(order_det_id,3)),0)+1,3,0)) PRODUCT_ID,
quantity+5,rate+2,quantity*rate
FROM xxtro_orders,order_details where xxtro_orders.order_id=c_order
GROUP BY xxtro_orders.ORDER_ID;
end loop;
end loop;
close c1;
end;
/