Bookshelf Home | Contents | Index | PDF     

Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Supply Chain Analytics > Process of Configuring Siebel Supply Chain Analytics for Oracle 11i >

Configuring the Bill of Materials Explosion

This task is a step in the Process of Configuring Siebel Supply Chain Analytics for Oracle 11i.

The Bill of Materials (BOM) functional area allows you to determine the profit margin of the components that comprise the finished goods. BOM allows you to keep up with the most viable vendors in terms of cost and profit, and to keep your sales organization aware of product delivery status, including shortages.

You can explode the BOM structure with three different options:

  • All. All the BOM components are exploded regardless of their effective date or disable date. To explode a BOM component is to expand the BOM tree structure.
  • Current. The incremental extract logic considers any changed components that are currently effective, any components that are effective after the last extraction date, or any components that are disabled after the last extraction date.
  • Current and Future. All the BOM components that are effective now or in the future are exploded. The disabled components are left out.

These options are controlled by the EXPLODE_OPTION variable. The EXPLODE_OPTION variable is preconfigured with a value of 2, explode Current BOM structure.

In the M_I_BOM_Explode mapping, the bompexpl.exploder_userexit stored procedure explodes the BOM structure. Table 74 lists the variables for the bompexpl.exploder_userexit stored procedure.

Table 74. Variables for the bompexpl.exploder_userexit Stored Procedure
Input Variable
Preconfigured Value
Description

VERIFY_FLAG

0

A verify flag with a value of 1 only applies to standard BOM.

ORG_ID

ORGANIZATION_ID

Organization ID

ORDER_BY

1

Controls the order of the records.

1—Operation Sequence Number, Item Number

2—Item Number, Operation Sequence Number

GRP_ID

Negative Sequence ID -1, -2, and so on.

Unique value to identify current explosion.

SESSION_ID

Negative Sequence ID -1, -2, and so on.

Unique value to identify current session.

LEVELS_TO_EXPLODE

10

Levels to explode.

BOM_OR_ENG

1

1—BOM

2—ENG

IMPL_FLAG

1

1—Implemented Only

2—Implemented and Unimplemented

PLAN_FACTOR

2

1—Yes

2—No

EXPLODE_OPTION

2

1—All

2—Current

3—Current and Future

MODULE

2

1—Costing

2—BOM

3—Order Entry

4—ATO

5—WSM

CST_TYPE_ID

0

Cost type ID for cost explosion.

STD_COMP_FLAG

0

1—Explode only standard components

2—All components

EXPL_QTY

1

Explosion quantity

ITEM_ID

ROUND(TO_DECIMAL(PRODUCT_ID))

Item ID of assembly to explode.

ALT_DESG

ALTERNATE_BOM_DESIGNATOR

Alternate routing designator

COMP_CODE

NULL

Concatenated component code

REV_DATE

TO_CHAR(CREATION_DT, 'YYYY/MM/DD HH24:MI')

Explosion date

YYYY/MM/DD HH24:MI

There are five different BOM types in a source system—1- Model, 2 - Option Class, 3 - Planning, 4 - Standard, and 5 - Product Family. By default, only the Standard BOM type is extracted and exploded.

To configure the BOM explosion to the All option

  1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
  2. Open the M_I_BOM_EXPLODE mapping.
  3. Double-click the EXP_BOMPEXPL_EXPLODER_USEREXIT expression transformation to open the Edit Transformations dialog box, and click the Port tab to display the EXPLODE_OPTION port.
  4. Change the value to 1, and click Apply.
  5. Validate the mapping, and save your changes to the repository.
  6. Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
  7. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.
  8. Modify the INV.EFFECTIVITY_DATE and INV.DISABLE_DATE portions in the following default Where statement.

    INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') AND (INV.EFFECTIVITY_DATE <= TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') and (INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))

    OR

    INV.EFFECTIVITY_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')

    OR

    INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')

    )

    OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))

    GROUP BY

    as follows:

    INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')

    OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))

    GROUP BY

  9. Click Apply, and Validate the mapping and save your changes to the repository.

To configure the BOM explosion to the Current and Future option

  1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
  2. Open the M_I_BOM_EXPLODE mapping.
  3. Double-click the EXP_BOMPEXPL_EXPLODER_USEREXIT expression transformation to open the Edit Transformations dialog box, and click the Port tab to display the EXPLODE_OPTION port.
  4. Change the value to 3, and click Apply.
  5. Validate the mapping, and save your changes to the repository.
  6. Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
  7. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.
  8. Modify the INV.EFFECTIVITY_DATE and INV.DISABLE_DATE portions in the following default Where statement.

    INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') AND (INV.EFFECTIVITY_DATE <= TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') and (INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))

    OR

    INV.EFFECTIVITY_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')

    OR

    INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')

    as follows

    INV.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') AND ((INV.DISABLE_DATE > TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS') OR INV.DISABLE_DATE IS NULL))

    OR

    INV.DISABLE_DATE between TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS') and TO_DATE('$$CURRENT_DATE','MM/DD/YYYY HH24:MI:SS')

    )

    OR BOM.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS'))

  9. Click Apply, and Validate the mapping and save your changes to the repository.

To configure the BOM type

  1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
  2. Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
  3. Double-click the SQL qualifier SQ_BOM_INVENTORY_COMPONENTS to open the Edit Transformations dialog box, and click on Properties tab, open value for SQL Query.
  4. Modify BOM_ITEM_TYPE section in Where statement.

    Change the number to your BOM type. For example, change the number to 3 for a Planning BOM type.

    Where INV.BOM_ITEM_TYPE = 3 AND

    M.BOM_ITEM_TYPE = 3 AND

    NOTE:  You can also remove these two filters to extract all types of BOM.

  5. Click Apply, and Validate the mapping and save your changes to the repository.
    
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide