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
|
|
|
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
- In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
- Open the M_I_BOM_EXPLODE mapping.
- 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.
- Change the value to 1, and click Apply.
- Validate the mapping, and save your changes to the repository.
- Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
- 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.
- 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
- Click Apply, and Validate the mapping and save your changes to the repository.
To configure the BOM explosion to the Current and Future option
- In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
- Open the M_I_BOM_EXPLODE mapping.
- 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.
- Change the value to 3, and click Apply.
- Validate the mapping, and save your changes to the repository.
- Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
- 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.
- 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'))
- Click Apply, and Validate the mapping and save your changes to the repository.
To configure the BOM type
- In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
- Open the MPLT_BCI_STAGE_BOM_COMPONENTS mapplet.
- 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.
- 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.
- Click Apply, and Validate the mapping and save your changes to the repository.
|