SQL & PL/SQL

create qoute

hi i have table to create qoute

tables
transction(transid,transdate,AccNo,AmtPaid,TransMethod)
Account(AccNo,custNo,ActivationDate,custNo,Aaccbalance)
transction.accNo = Account.accNo
Customer(custNo,custfname,custLname)
customer.custno =account.custno
Quotation(quoteNO,Qdate,custNo,prodid,totalValue)
customer.custno = Quotation.custno
product(prodid,totQuatitySupplied,totQtyOnOrder,purchdate)
product.prodid = quotation.prodid
order(orderNo,qDate,QouteNo)
quotation.quoteNo = order.QuoteNo
quotation.qdate = order.qdate


i what to create a procedure to createOrder where one select the qoute with the product, then it subtracts the QuantityOnHand from the Products table (which is called TotQtyOnOrder in the product table) .epending on how much quantity of that product was ordered.
it also adds the totalvalue to the custome's account and adds a transaction amount & date & transID in the transaction table according to sysdate.

i try to do this

 procedure CreateNewQuote(P_QuteNo VARCHAR2,
                                          P_QDate   date,
                                          P_CUStno number,
                                          P_prodid number,
                                          P_RequestedQuantity number,
                                          P_QExtendedPrice Number,
                                          P_SubTotal number,
                                          P_Vat number,
                                          P_Total_Value number,
                                          P_terms) IS
    
/*this procedure create new qoute based on the exisisting custno and prodid you have to pass this value on your screen using list of value. and all qoute details required in the quotation*/

       cursor newqoute is select custno,qouteNo,prodid from 
                                                                       customer cust,
                                                                       quotation quo,
                                                                       product prod
                                                                       where cust.custno = P_CUStno
                                                                        and quo.prodid =  P_prodid;



integrity_err   EXCEPTION;
    PRAGMA EXCEPTION_INIT(integrity_err, -2291);
 BEGIN
 
 for quote in newquote  loop
    insert into Quotation(QuteNo,
                                   QDate,
                                   CUStno,
                                   prodid,
                                   RequestedQuantity,
                                   QExtendedPrice,
                                   SubTotal,
                                   Vat,
                                   Total_Value,
                                   terms)
                    values
                         (CUSTOMER_SEQ.nextval,
                                          QDate,
                                          quote.custNo,
                                          quote.prodid,
                                          RequestedQuantity,
                                          QExtendedPrice,
                                          SubTotal,
                                          Vat,
                                          Total_Value,
                                          terms);
                   end loop;           
                              
                              
      commit;
    
     EXCEPTION
      WHEN integrity_err THEN
        RAISE_APPLICATION_ERROR(-2291, 'Integrity Violated OUFFFF: ' || SQLCODE, TRUE);
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Customer or product does not exist: ' || SQLCODE, TRUE);                        
                                   
  END;

/

create procedure createOrder(p_quoteno number) is 

out_Quantityonhand number,
out_amount number

---check product outation order
cursor prodQuote is select prod.prodid,
                                      TotQtyOnOrder,
                                      Qouteno,
                                      Qdate,
                                      custNo,
                                      totalValue
                           from product prod,
                                  quotation quo
                                  where quo.quoteNo = p_quoteno;
                                  
                                  
                                                       
  --check quotation which end up to be an order
  cursor QouteOrder is select prod.prodid,
                                         prod.custno,
                                         Qdate,
                                         totalValue,
                                         subTotal,
                                         orderNo 
                                         from quotation quo,order ord
                                         where quo.quoteNo = p_quoteno;    
    
  p_Status varchar(3) := 'Active';                               
  p_termsCondition varchar(50) := 'there is condtion for this order payment must be made before delivery';      
                                         
 begin
 
                                                                          
for ordQoute in QouteOrder loop
      if ordQoute.QDate = sysdate then
              insert into order( orderNo,
                                       Qdate,
                                       QouteNo,
                                       QStatus,
                                       TermsCondition)
                                    values
                                    (CUSTOMER_SEQ.nextval,
                                     ordQoute.QDate,
                                     ordQoute.QouteNo,
                                     p_Status,
                                     p_termsCondition);
                       end loop;   
                       
                       end;