Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

> ## Writing the BinCheck procedure to check inventory

> In this section, you add another procedure call that illustrates some of the block types you studied in this chapter and some of the list handling functions summarized in Chapter 1, " Introducing the Progress 4GL." The procedure looks at the Warehouse and Bin tables to see which Warehouses can and cannot supply the Items for a given Order.

> | To modify the `h-OrderCalcs.p` procedure, add the new statements in bold type:

>     DEFINE INPUT  PARAMETER piOrderNum          AS INTEGER     NO-UNDO.
>     DEFINE OUTPUT PARAMETER pdOrderPrice        AS DECIMAL     NO-UNDO.
>     DEFINE OUTPUT PARAMETER pdOrderTotal        AS DECIMAL     NO-UNDO.
>     DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER   NO-UNDO.
>     DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER   NO-UNDO.
>     DEFINE VARIABLE cItemList     AS CHARACTER     NO-UNDO.
>     FIND order WHERE Order.orderNum = piOrderNum NO-ERROR.
>     FOR EACH OrderLine OF Order:
>          ASSIGN pdOrderTotal = pdOrderTotal + OrderLine.ExtendedPrice
>               pdOrderPrice = pdOrderPrice + OrderLine.Price * OrderLine.Qty
>               cItemList = cItemList +
>                    (IF cItemList = "" THEN "" ELSE ",") +
>                         STRING(ItemNum).
>     END.
>     RUN h-BinCheck.p (INPUT cItemList, OUTPUT pcWarehouseList, OUTPUT
>     pcBestWarehouse).

> The additional code defines two new `OUTPUT` parameters and a new variable.

> Then, as part of the `ASSIGN` statement, it constructs a list of Item numbers for the OrderLines of the Order. To make a list, it uses a `CHARACTER` variable `cItemList`. The assignment statement effectively means:

> 1. Take the current value of the `cItemList` variable (which is initially blank).

> 2. If it’s blank, then append a blank value to it (this is just a no-op condition for the `IF-THEN-ELSE` statement, which requires both a `THEN` phrase and an `ELSE` phrase). Otherwise, if there’s already something in the list, append a comma to it to separate the Items.

> 3. Use the `STRING` built-in function to convert the integer `ItemNum` to a `CHARACTER` value and append it to the variable. (There are other built-in functions like this one named `DECIMAL`, `INTEGER`, `DATE`, and `LOGICAL` to convert character strings to those other data types as well.)

> At the end of the `FOR EACH` block, `cItemNum` holds a comma-separated list of all the Items for the current Order.

> Finally, the procedure runs another procedure, `h-BinCheck.p`, which you’ll write next.

> | To write the `h-BinCheck.p` procedure:

> 1. Save this modified version of `h-OrderCalcs.p`.

> 2. Open a New Procedure Window and start to write `h-BinCheck.p` with the following code. Add each new group of statements to the procedure as they are discussed:

>     /* h-BinCheck.p */
>     DEFINE INPUT  PARAMETER pcItemList          AS CHARACTER     NO-UNDO.
>     DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER     NO-UNDO.
>     DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER     NO-UNDO.
>     DEFINE VARIABLE iEntry       AS INTEGER     NO-UNDO.
>     DEFINE VARIABLE iItemNum     AS INTEGER     NO-UNDO.
>     DEFINE VARIABLE iWHQty       AS INTEGER     NO-UNDO.
>     DEFINE VARIABLE iWHNum       AS INTEGER     NO-UNDO.
>     DEFINE VARIABLE iBestWH      AS INTEGER     NO-UNDO.
>     DEFINE VARIABLE cBestList    AS CHARACTER   NO-UNDO.

> This procedure takes the list of item numbers as an `INPUT` parameter and returns two `CHARACTER` parameters. The various variables are used throughout the procedure. Remember that you can use the editor shortcuts (IPC, OPC, DVI, and DVC) to generate most of the `DEFINE` `PARAMETER` and `VARIABLE` statements for you.

> ##### Using list and string functions to manage a list of values

> The `h-BinCheck.p` procedure needs to make a list of how many Items are supplied by each Warehouse. There are various ways to code this, but to illustrate some more of the string manipulation functions you were introduced to in Chapter 2, "Using Basic 4GL Constructs," you’ll build this as a character string.

> | To update `h-BinCheck.p` to make a list of the number of Items supplied by each Warehouse:

> 1. Add placeholders for the count of Items in each Warehouse. The following code forms a list with as many zeroes as there are Warehouses. The zero values are later incremented to count Items supplied by each Warehouse:

>     FOR EACH Warehouse:
>               cBestList = cBestList + "0,".
>     END.
>     cBestList = RIGHT-TRIM(cBestList, ",").

> Note: The `RIGHT-TRIM` function removes the final comma from the list, rather than the `IF-THEN-ELSE` statement in the assignment that created the item list in `OrderProcs.p`. These are just different ways of doing the same thing. The `RIGHT-TRIM` function is a bit more efficient.

> 2. To loop through the list of Items, add a `DO` block with the `NUM-ENTRIES` function:

>     DO iEntry = 1 TO NUM-ENTRIES(pcItemList):

> `NUM-ENTRIES` counts the entries in a list using a comma as the delimiter between entries by default. If you need to use a delimiter other than a comma, the delimiter can be an optional second argument to the function.

> 3. Add a statement that embeds two built-in functions into one statement:

>     iItemNum = INTEGER(ENTRY(iEntry, pcItemList)).

> The `ENTRY` function extracts entry number `iEntry` from `pcItemList`. It returns this to the `INTEGER` function, which converts the value back to an integer. So now you’ve restored the Item number to its original form.

> 4. Add a block of code that operates on this Item number. The Bin table represents bins or containers in each Warehouse that are used to store the various Items. It has both an ItemNum field to point to the Item record, and a WarehouseNum field to point to the Warehouse where the Bin is located. If the Qty (quantity) field for a Bin record is 0, then the Warehouse that Bin is in cannot supply that part. The code builds up this list of Warehouse names. The `LOOKUP` function looks for a string in a list. If it finds it, it returns the position of the entry in the list. Otherwise, it returns 0 if the entry is not in the list. Here the `LOOKUP` function is used to make sure that a Warehouse name is added to the list once only if it’s not already there:

>     FOR EACH Bin WHERE Bin.ItemNum = iItemNum:
>        IF Bin.Qty = 0 THEN
>        DO:
>            FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.
>            IF LOOKUP(WarehouseName, pcWarehouseList) = 0 THEN
>                pcWarehouseList = pcWarehouseList +
>                   (IF pcWarehouseList = "" THEN "" ELSE ",") + WarehouseName.
>            END.
>        END.

> 5. Still within the `DO` block that iterates on each item, add code that initializes two variables to zero using a single `ASSIGN` statement:

>     ASSIGN iWHQty = 0 iWHNum = 0.

> These variables hold the quantity of each item at a Warehouse and the Warehouse number.

> | To use the `REPEAT PRESELECT` block to pre-fetch records:

> 1. Add a `REPEAT` block that preselects each Bin that holds the current Item, along with the Warehouse where the Bin is located, filtering these to include only Warehouses in the USA. The records are sorted in descending order of their quantity. This identifies which Warehouse has the largest quantity of the Item in inventory. Remember that the `PRESELECT` phrase forces Progress to retrieve all the matching records before beginning to execute the statements in the block:

>     REPEAT PRESELECT EACH Bin WHERE Bin.ItemNum = iItemNum,
>          FIRST Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum AND
>               Warehouse.Country = "USA" BY Bin.Qty DESCENDING:

> 2. Add the code that finds the next Warehouse record in this preselected list. The first time through the `REPEAT` block, the `FIND NEXT` statement finds the first record:

>     FIND NEXT Warehouse.

> Why does the statement name the Warehouse buffer and not the Bin? The rule is that whenever you are doing a `FIND` on a `PRESELECT` result set that involves a join, you must name the last table in the join. This makes sense, because if it is a one-to-many join, the record in the last (rightmost) table in the join is the only one to change on every iteration. The first table in the join might be the same for a number of records in the second table.

> Remember also that the `REPEAT` block does not automatically iterate for you, even if you preselect the records. You have to use a `FIND` statement to move from record to record.

> 3. Add the following statements to determine whether the Warehouse with the highest inventory for the Item has a quantity at least 100 greater than the next best Warehouse. If so, it retrieves the entry in the list of best Warehouses that the code initialized with zeroes at the start of the procedure, increments it, and puts it back in the list, doing the necessary conversions to and from the `INTEGER` data type:

>          IF iWHQty NE 0 AND iWHQty - Bin.Qty > 100 THEN
>               DO:
>                    ASSIGN
>                         iBestWH = INTEGER(ENTRY(iWHNum, cBestList))
>                         iBestWH = iBestWH + 1
>                         ENTRY(iWHNum, cBestList) = STRING(iBestWH).
>               END.
>               ELSE IF iWHQty NE 0 THEN
>                    LEAVE.
>               ASSIGN iWHQty = Bin.Qty
>                    iWHNum = Warehouse.WarehouseNum.

> 4. Terminate the `REPEAT` block and the `DO` block for each item:

>     END.          /* END REPEAT PRESELECT EACH Bin... */
>     END.          /* END DO iEntry... */

> ##### Using multiple weak-scoped references in a single block

> If you take a look at the entire `DO` block, you can inspect the buffer scoping:

>     DO iEntry . . .:                         /* No buffer scoping at all */
>          FOR EACH Bin . . .:                 /* Weak-scoped reference to Bin */
>          .
>          .                                   /* --Bin is scoped to this block. */
>          .
>          END.
>          .
>          .
>          .
>          REPEAT PRESELECT EACH Bin. . .:     /* Weak-scoped reference to Bin */
>          .
>          .                                   /* -- Bin scoped to this block too */
>          .
>          END.
>     END.

> The `DO` block itself doesn’t scope any records. The `FOR EACH` block and the `REPEAT PRESELECT EACH` block each scope the Bin record with a weak scope. This is okay, and the Bin buffer is scoped to each of these two blocks in turn.

> The final block of code walks through the list of best Warehouses for this Order’s items. At this point the `cBestList` variable holds a list of numbers for each Warehouse. Each number is the count of Items where that Warehouse has an inventory at least 100 better than the next best Warehouse. This block checks whether there’s a Warehouse that is the best for either all or all but one of the Items. If so, you find that Warehouse record and save off the WarehouseName to pass back. By now all the statements and functions in this block should be familiar to you.

> | To end the procedure, use the following code:

>     DO iEntry = 1 TO NUM-ENTRIES(cBestList):
>        IF INTEGER(ENTRY(iEntry, cBestList)) >= (NUM-ENTRIES(pcItemList) - 1) THEN
>           DO:
>               FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.
>               pcBestWarehouse = Warehouse.WarehouseName.
>               LEAVE.
>          END.
>     END.

> This procedure is a little complicated, but these examples show how the different block types interact and how to use some of the built-in functions listed in Chapter 2, " Using Basic 4GL Constructs."

> ##### Examining the scope with weak and strong references

> One final question before you move on: The Bin record buffer is scoped to the two blocks inside the main `DO` block, but at what level is the Warehouse record buffer scoped? Look back through the entire procedure to come up with an answer before looking at this excerpt from the listing file:

>       File Name          Line Blk.     Type     Tran            Blk. Label
>     -------------------- ---- --------- ---- --------------------------------
>     .\h-BinCheck.p          0   Procedure No
>          Buffers: sports2000.Warehouse
>     .\h-BinCheck.p          14   For      No
>     .\h-BinCheck.p          19   Do       No
>     .\h-BinCheck.p          21   For      No
>          Buffers: sports2000.Bin
>     .\h-BinCheck.p          22   Do       No
>     .\h-BinCheck.p          32   Repeat   No
>          Buffers: sports2000.Bin
>     .\h-BinCheck.p          37   Do       No
>     .\h-BinCheck.p          51   Do       No
>     .\h-BinCheck.p          52   Do       No

> You see the two blocks where the Bin buffer is scoped. You also see that the Warehouse buffer is scoped to the entire procedure (line 0). Why is this?

> There are several free references to the Warehouse buffer that aren’t in blocks that provide record scoping. This includes, among others, the final `DO` block of the procedure. As a result, Progress raises the scope of the buffer all the way to the procedure itself because there’s no other block to scope it to. In your sample procedure, which is only reading records from the database and not updating them, it doesn’t make a lot of difference. If the procedure had a transaction that updated the Warehouse record, though, you might find that the record and the record lock on it are held much longer than you expected or wanted, resulting in record contention between different users accessing the table at the same time.

> What could you do to avoid this? Define a strong scope for the Warehouse record wherever it’s used.

> | To define a strong scope for the Warehouse record:

> 1. First make the first `DO` block around the `FIND Warehouse` statement scope the buffer to the block:

>     DO FOR Warehouse:
>          FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.

> 2. Press SHIFT+F2 to do a syntax check. What do you get?

> Why did this error happen? You tried to force the scope of the buffer to this block, but the free reference in the `DO` block at the end of the procedure still forces the scope up to the top, and those two conflict.

> 3. Change the final `DO` block to place a strong scope there:

>     DO FOR Warehouse:
>          FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.

> Now a syntax check succeeds. If you compile the procedure and get a listing file, you see that the Warehouse buffer is scoped all over the place:

>       File Name          Line Blk.     Type     Tran            Blk. Label
>     -------------------- ---- --------- ---- --------------------------------
>     .\h-BinCheck.p          0   Procedure No
>     .\h-BinCheck.p          14   For      No
>          Buffers: sports2000.Warehouse
>     .\h-BinCheck.p          19   Do       No
>     .\h-BinCheck.p          21   For      No
>          Buffers: sports2000.Bin
>     .\h-BinCheck.p          22   Do       No
>          Buffers: sports2000.Warehouse
>     .\h-BinCheck.p          32   Repeat   No
>          Buffers: sports2000.Warehouse
>          Buffers: sports2000.Bin
>     .\h-BinCheck.p          37   Do       No
>     .\h-BinCheck.p          51   Do       No
>     .\h-BinCheck.p          52   Do       No
>          Buffers: sports2000.Warehouse

> Take a look at the scope for each of these blocks:

> - The Warehouse buffer is now scoped to the very first `FOR EACH Warehouse` block, with its weak scope. Progress tried to do this before. Because the other references to Warehouse forced the scope up to the procedure level, this weak scope disappeared (that’s why it’s called weak).

> - It’s scoped to the `DO FOR Warehouse` block because you added that strong-scoped reference.

> - It’s also scoped to the `REPEAT PRESELECT` block. This is another weak scope that didn’t hold when the buffer scope was forced to the top.

> - It’s scoped to the final `DO FOR Warehouse` block, with its strong-scoped reference you just added.

> When you start writing serious procedures that update the database, you’ll be a lot more successful if you keep your buffer scope small like this. You should get into the habit now.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095