Function get_stock_info

This function verifies that the stock number entered for a new row in the arr_items array exists in the stock table. It retrieves the description, unit of measure, and the correct price based on whether promotional pricing is in effect for the order.

Function get_stock_info:
01 FUNCTION get_stock_info(curr_pa, id)
02   DEFINE curr_pa SMALLINT,
03          id INTEGER,
04          sqltext STRING
05 
06   IF id IS NULL THEN    
07      RETURN FALSE
08   END IF
09 
10   LET sqltext="SELECT description, unit,"
11   IF order_rec.promo = "N" THEN 
12      LET sqltext=sqltext || "reg_price"
13   ELSE
14      LET sqltext=sqltext || "promo_price"
15   END IF
16   LET sqltext=sqltext ||
17       " FROM stock WHERE stock_num = ? AND fac_code = ?"
18 
19   WHENEVER ERROR CONTINUE
20   PREPARE get_stock_cursor FROM sqltext 
21   EXECUTE get_stock_cursor 
22         INTO arr_items[curr_pa].description,
23              arr_items[curr_pa].unit, 
24              arr_items[curr_pa].price 
25         USING id, order_rec.fac_code 
26   WHENEVER ERROR STOP
27 
28   RETURN (SQLCA.SQLCODE == 0)
29 
30 END FUNCTION
Note:
  • Line 02 the index of the current row in the array is passed to this function and stored in the variable curr_pa.
  • Lines 10 thru 17 check whether the promotional pricing is in effect for the current order, and build a SELECT statement to retrieve the description, unit, and regular or promotional price from the stock table for a new item that is being added to the items table.
  • Lines 20 thru 25 prepare and execute the SQL statement created before.
  • Line 28 checks SQLCA.SQLCODE and returns TRUE if the database could be updated without error.