--log table-------------------------------------------
CREATE TABLE DBO.TBL_LOG
(
  CODE         VARCHAR2(1000),
  CODE_ERROR   VARCHAR2(1000 BYTE),
  CREATED_ON   DATE,
  CREATED_BY   VARCHAR2(100 BYTE),
  MODIFIED_ON  DATE,
  MODIFIED_BY  VARCHAR2(100 BYTE)
)

---log procedure--------------------------------

CREATE OR REPLACE PROCEDURE DBO.prc_write_log
(
p_code   IN NUMBER,
p_error  IN VARCHAR2 )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TBL_LOG
(CODE , CODE_ERROR , CREATED_ON  , CREATED_BY , 
MODIFIED_ON , MODIFIED_BY )
VALUES(p_code,p_error, null, null, sysdate, user);

END;
/

--package-----------------------------------------

CREATE OR REPLACE package body DBO.pkg_pur_order
As
   Function func_select_cols (f_order_no        IN   NUMBER,
                              f_order_company   IN   VARCHAR2)
   Return TBL_PURCHASE_ORDERS.P_NO_OF_GOODS%type
   IS
    v_no_of_goods   NUMBER;
    Begin
      Select p_no_of_goods into v_no_of_goods
      from tbl_purchase_orders
      where pur_order_no = f_order_no
      and p_order_company = f_order_company;
    Exception
     When no_data_found then Null;
     When Others Then Null;
    End func_select_cols;
    
   Procedure prc_orders --(pur_order_no IN number)
   IS
   
    PRAGMA AUTONOMOUS_TRANSACTION;
       Begin
          Insert Into tbl_purchase_orders(pur_order_no,PUR_ORDER_NAME,
          P_ORDER_TYPE ,P_ORDER_PAYMENT ,P_ORDER_COMPANY  ,P_ORDER_OWNER ,P_NO_OF_GOODS)
          values(104,'WELDING MACHINES',null, 50000, 'KALA MECHANICS','Mr. Krish',100);
          Insert Into tbl_purchase_orders(pur_order_no,PUR_ORDER_NAME,
          P_ORDER_TYPE ,P_ORDER_PAYMENT ,P_ORDER_COMPANY  ,P_ORDER_OWNER ,P_NO_OF_GOODS)
          values(104,'WELDING MACHINES',null, 50000, 'MORANI MECHANICS','Mr. John',100);
          Insert Into tbl_purchase_orders(pur_order_no,PUR_ORDER_NAME,
          P_ORDER_TYPE ,P_ORDER_PAYMENT ,P_ORDER_COMPANY  ,P_ORDER_OWNER ,P_NO_OF_GOODS)
          values(105,'WELDING MACHINES',null, 50000, 'PRI MECHANICS','Mr. Adi',100);
          Commit;
          Exception
            When Others then 
            rollback;
              prc_write_log(sqlcode, sqlerrm);              
            
       End prc_orders;
    End pkg_pur_order;
/