|
Oracle® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle's Supply Chain Analytics family of products > Process of Configuring Supply Chain Analytics for Oracle 11i >
Configuring the Bill of Materials Explosion Options
This task is a step in the Process of Configuring 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 SDE_ORA_BomItemFact mapping, the bompexpl.exploder_userexit stored procedure explodes the BOM structure. Table 56 lists the variables for the bompexpl.exploder_userexit stored procedure.
Table 56. 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 SDE_ORA115<Ver>_Adaptor.
- Open the SDE_ORA_BomItemFact 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.
- Go to Mapplet Designer, and open mplt_BC_ORA_BOMHeaderDimension.
- 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 following default Where condition from:
((
/* CURRENT valid component changed */
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
/* Component that became effective after last extract date and before today's extract, for CURRENT Option*/
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
/* Component that become disabled after last extract date and before today's extract, for CURRENT and CURRENT-FUTURE Option*/
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
To:
(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 SDE_ORA115<Ver>_Adaptor.
- Open the SDE_ORA_BOMItemFact 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.
- Go to Mapplet Designer, and open mplt_BC_ORA_BOMHeaderDimension.
- 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 following default Where condition from:
((
/* CURRENT valid component changed */
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
/* Component that became effective after last extract date and before today's extract, for CURRENT Option*/
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
/* Component that become disabled after last extract date and before today's extract, for CURRENT and CURRENT-FUTURE Option*/
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
To:
((
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'))
GROUP BY
- Click Apply, and Validate the mapping and save your changes to the repository.
To configure the BOM type
- In PowerCenter Designer, open the SDE_ORA115<Ver>_Adaptor.
- Open the mplt_BC_ORA_BOMHeaderDimension 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.
|
