DECLARE
  v_cust_cntr number(6) := 1;
  v_so_cntr number(8);
  v_so_cntx number(8);
  v_sodetail_cntr number(8);
  v_sonum number(8);
  v_qty number(3);
  v_cost number(8,2);
  v_price number(8,2);
  v_itemno number(3);
  v_partno varchar2(6);
  v_salesagent number(5);
  v_orderdate date;
  v_shipdate date;
  v_customer rec.customers%rowtype;
  CURSOR customer_cursor IS
    SELECT * from rec.customers;
BEGIN
  update rec.customers set balance = 0;
  commit;
  dbms_output.enable(50000);
  open customer_cursor;
  fetch customer_cursor into v_customer;
  while customer_cursor%found LOOP
     DBMS_OUTPUT.PUT_LINE (v_cust_cntr);
     v_so_cntr :=  round((random.randmax(100)*random.randmax(100))/100,0);
     v_salesagent :=  random.randmax(9)*10;
     DBMS_OUTPUT.PUT_LINE (v_so_cntr);
     while v_so_cntr>0 LOOP
        v_so_cntx := round(v_so_cntr/11,0)+1;
        if v_so_cntx >= 1 AND v_so_cntx <= 3 then
           v_orderdate:='0'||to_char(v_so_cntx*v_so_cntx)||'-JUN-01';
           v_shipdate:='1'||to_char(v_so_cntx*v_so_cntx)||'-JUN-01';
        else
           if v_so_cntx >= 4 AND v_so_cntx <= 6 then
              v_orderdate:=to_char(v_so_cntx*3)||'-JUL-01';
              v_shipdate:=to_char(v_so_cntx*4)||'-JUL-01';
           else
              v_orderdate:=to_char(v_so_cntx*2)||'-AUG-01';
              v_shipdate:=to_char(v_so_cntx*3)||'-AUG-01';
           end if;
        end if; 
        select rec.sonumseq.nextval into v_sonum from dual;
        insert into rec.SalesOrders values(v_sonum, v_customer.id, v_orderdate, v_shipdate, 'UPS','0',v_salesagent);
        --generate random number between 1 and 10 to be number sodetail lines
        v_sodetail_cntr := random.randmax(10);
        while v_sodetail_cntr>0 LOOP
            --randomly select inventory item from 1st to 15th
            v_itemno := mod((random.randmax(15)*random.randmax(15)),15)+1;
            select partno, price, cost into v_partno,v_price, v_cost from rec.inventory where itemno=v_itemno;
            --randomly select qty sold between 1 and 10
            v_qty := random.randmax(10);
            --insert sodetail row
            if v_customer.custtype='W' then
               v_price := round(v_price * .98, 2);
            end if;
            insert into rec.sodetail values (v_sonum, v_sodetail_cntr, v_partno, v_qty, v_cost, v_price);
            --update order total   
            update rec.salesorders set ordertotal=ordertotal+(v_price*v_qty) where sonum=v_sonum;
            update rec.inventory set onhand=onhand-v_qty where partno=v_partno;
           v_sodetail_cntr := v_sodetail_cntr - 1; 
           commit;  
        end loop;       
        v_so_cntr := v_so_cntr - 1;
    END LOOP;
    commit;
    fetch customer_cursor into v_customer;
    v_cust_cntr := v_cust_cntr + 1;
  end loop;
  close customer_cursor;
  commit;
  --open 
END;
/
DECLARE
  v_salesorder rec.salesorders%rowtype;
  CURSOR salesorder_cursor IS
    SELECT * from rec.salesorders where to_char(orderdate,'MM')='08';
BEGIN
  open salesorder_cursor;
  fetch salesorder_cursor into v_salesorder;
  while salesorder_cursor%found LOOP
      update customers set balance = balance + v_salesorder.ordertotal where id = v_salesorder.custid;
      commit;
      fetch salesorder_cursor into v_salesorder;
  end loop;
  close salesorder_cursor;
END;
/