DECLARE

  ANZAHL_RECHNUNGEN PLS_INTEGER := 1200;

  prodcount PLS_INTEGER := 192;
  custcount PLS_INTEGER := 12;
  poscount  PLS_INTEGER;
  invdate   DATE;
  duedate   DATE;
  paydate   DATE;
  paid      PLS_INTEGER;
  product   PLS_INTEGER;
  qty       PLS_INTEGER;
  price     NUMBER;
  amount    NUMBER;
  invnum    NUMBER;
  
BEGIN 
  
  DELETE FROM invlines;
  DELETE FROM invoices;

  FOR i IN 1..ANZAHL_RECHNUNGEN LOOP
    amount := 0;
    invdate := to_date('01.01.2000','DD.MM.YYYY')+round(dbms_random.value(1,3896));
    invnum := to_char(invdate,'YYYYMMDD')||trim(to_char(i,'000009'));
    duedate := invdate + 14;
    paid := round(dbms_random.value(1,100-to_number(to_char(duedate,'YY'))*3));
    poscount := to_number(to_char(invdate,'YY'))+round(dbms_random.value(2,5));
    IF paid > 20 THEN
      paydate := invdate + round(dbms_random.value(1,20));
    ELSE
      paydate := NULL;
    END IF;
    
    INSERT INTO invoices VALUES
    (invnum
    ,invdate
    ,duedate
    ,round(dbms_random.value(1,12))
    ,0
    ,paydate
    );
       
    FOR p IN 1..poscount LOOP
      product := round(dbms_random.value(1,prodcount));
      qty := round(dbms_random.value(1,100));

      SELECT productprice
      INTO   price
      FROM   products
      WHERE  productid = product;
      
      amount := amount + (qty*price);

      INSERT INTO invlines VALUES
      (invnum
      ,p
      ,product
      ,qty
      ,price
      ,qty*price
      );
    END LOOP;
    
    UPDATE invoices inv
    SET    invamount = amount
    WHERE  inv.invno = invnum;
    
  END LOOP;
  
  COMMIT;
  
END;       
  
  
  
  
