CREATE OR REPLACE TRIGGER boin_af_rw_all_1
  AFTER INSERT OR UPDATE ON book_inventory

  FOR EACH ROW
DECLARE

  CURSOR c_prod IS
    SELECT prod.prds_product_code, prod.item_number
      FROM products prod
     WHERE :NEW.prod_prds_product_code = prod.prds_product_code
       AND (:NEW.prod_item_number = prod.item_number);

  CURSOR c_boin is
    SELECT boin.quantity_on_hand, boin.reorder_point
    
      FROM book_inventory boin, products prod
     WHERE :NEW.prod_prds_product_code = prod.prds_product_code
       AND :NEW.prod_item_number = prod.item_number
       AND boin.Prod_Prds_Product_Code = prod.prds_product_code
       AND boin.prod_item_number = prod.item_number;

BEGIN
  OPEN c_prod;
  FETCH c_prod
    INTO v_prod_code, v_item_no; /*, v_qty_onhand, v_reorder_pnt*/
  CLOSE c_prod;

  OPEN c_boin;
  FETCH c_boin
    INTO v_qty_onhand, v_reorder_pnt;
  CLOSE c_boin;

  IF v_qty_onhand < v_reorder_pnt THEN
  
    v_message_text := PKG_CCH_GLOBAL.FCT_RET_MESSAGE('05000');
    INSERT INTO test67443
      (description, col1, col2, col3, col4, col5, col6)
    values
      ('2',
       v_prod_code,
       v_item_no,
       v_qty_onhand,
       v_reorder_pnt,
       v_message_text,
       null);
  END IF;
END;