Configuring the Stored Procedure for the Left Bound and Right Bound Calculation Option

The SIL_BOMItemFact mapping contains the stored procedure called COMPUTE_BOUNDS which traverses the exploded BOM tree structure and calculates the left bound and right bound. By default, the COMPUTE_BOUNDS stored procedure is turned off.

Note:

This procedure applies to E-Business Suite and Oracle Fusion source systems. This procedure is not required for JD Edwards EnterpriseOne (in JD Edwards EnterpriseOne, the left and right bounds are calculated automatically by the UBE (R30461).

You can use the left bound and the right bound calculation to expedite some reporting requirements. For example, you can find the components in a subassembly within a finished product. Left bound and right bound are stored in the W_BOM_ITEM_F table for each BOM node, and they have one row of data in the W_BOM_ITEM_F table. The COMPUTE_BOUNDS stored procedure traverses the exploded BOM tree structure and calculates the left bound and right bound. By default, the COMPUTE_BOUNDS stored procedure is off and the W_BOM_ITEM_F.LEFT_BOUNDS and W_BOM_ITEM_F.RIGHT_BOUNDS columns are empty.

The figure illustrates a sample BOM structure with the left bound and right bound values listed for each node. To find all the components under node B, you select the components with a top product key value of A, the left bound value is greater than 2, and the right bound value is less than 17.

Description of GUID-77047184-D47E-4D0E-863A-237D67014135-default.gif follows
Description of the illustration GUID-77047184-D47E-4D0E-863A-237D67014135-default.gif

You can use the following process to turn on the left bound and the right bound calculation and populate the W_BOM_ITEM_F.LEFT_BOUNDS and W_BOM_ITEM_F.RIGHT_BOUNDS columns.

  1. In ODI, navigate to SILOS, then SIL_BOMItemFact, then Packages, and edit the SIL_BOMItemFact package.
  2. Display the Diagram tab.
  3. Choose the Next step on success tool (green OK. arrow button).
  4. Draw a line connecting the Refresh IS_INCREMENTAL icon to the Run COMPUTE_BOUNDS icon.
  5. Draw a line connecting the Run COMPUTE_BOUNDS icon to the Run SIL_BOMItemFact icon.
  6. Save the Package.
  7. Generate the associated Scenario.

The first step of the COMPUTE_BOUNDS ODI procedure attempts to create or replace the associated stored procedure in Oracle Business Analytics Warehouse. The user account under which the scenario runs must have the appropriate permissions for this step to succeed. Alternatively, the stored procedure can be deployed manually and the first step of the ODI procedure can then be disabled to avoid granting such permissions.