| Oracle® Business Intelligence Applications New Features Guide Version 7.9.6.2 Part Number E16811-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications New Features Guide Version 7.9.6.2 Part Number E16811-01 |
|
|
View PDF |
This chapter contains configuration information for Oracle Supply Chain and Order Management Analytics for Oracle's JD Edwards EnterpriseOne.
This chapter includes the following topics:
Before you follow the configuration steps in this chapter, perform the source-independent configuration steps in the following chapters of the Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users:
"Configuring Common Areas and Dimensions"
"Configuring Oracle Supply Chain and Order Management Analytics"
This section contains configuration steps required before you do a full data load that apply to JD Edwards EnterpriseOne.
Section 1.2.2, "How to Configure User Defined Code Categories"
Section 1.2.3, "How to Configure Movement Type Domain Values"
Section 1.2.4, "How to Configure Transaction Types Domain Values for Sales Invoice Lines"
Section 1.2.5, "How to Configure Transaction Types Domain Values for Sales Order Lines"
Section 1.2.9, "How to Configure Inventory Return Locations"
Section 1.2.11, "How to Configure the Left Bound and Right Bound Calculation Option"
Table 1-1 lists the CSV worksheet files and the domain values for Oracle Supply Chain and Order Management Analytics that are located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Table 1-1 Domain Values and CSV Worksheet Files for Oracle Supply Chain and Order Management Analytics for JD Edwards EnterpriseOne
| Worksheet File | Description | Session |
|---|---|---|
|
file_udc_category_mapping_jde.csv |
This file is used to provide JD Edwards EnterpriseOne user defined codes that are to be populated into the W_CODE_D table. |
SDE_JDE_Code_Category_Map_Load |
|
domainvalues_Movement_Types_jde.csv |
This file provides override information of the Movement Type Description for a specific Movement Type. |
SDE_JDE_MvmntTypeDimension |
|
domainvalues_xact_type_codes_scom_jde_sales_ivclns.csv |
This file provides the Sales Order Transaction Type Codes that are applicable to sales invoice lines. |
SDE_JDE_XactTypeDimension_SalesInvoiceLine |
|
domainvalues_xact_type_codes_scom_jde_sales_ordlns.csv |
This file provides the Sales Order Transaction Type Codes that are applicable to Sales Order lines. |
SDE_JDE_XactTypeDimension_SalesOrderLine |
|
file_sales_order_status_jde.csv |
This file provides Sales Order status information to populate the W_CODE_D (by category) and W_STATUS_D tables. |
SDE_JDE_StatusDimension_SalesOrder |
|
file_lkp_chnl_typ_jde.csv |
This file serves to provide additional information by Channel Type Code to populate the Channel Type Dimension table. |
SDE_JDE_ChannelTypeDimension |
|
file_lkp_consign_inv_org_jde.csv |
This file is used to identify the Branch/Plants (Organization ID) that are used for consignment within the inventory process. |
SDE_JDE_Inventory_Daily_Bal_Fact |
|
file_lkp_return_loc_jde.csv |
This file is used to identify the return locations by Branch/Plant, Location, and Lot Number within the inventory process. |
SDE_JDE_Inventory_Daily_Bal_Fact |
This section explains how to configure the file_udc_category_mapping_jde.csv file.
You populate this file with data before running the SDE_JDE_Category_Map_Load workflow. This workflow will run a mapping of the same name that uses the CSV file as input to create a temporary table called W_JDE_UDC_CATEGORY_MAP_TMP. This temporary table is then used as source input into the SDE_JDE_CODE_DIMENSION workflow to load the W_CODE_D table.
To configure this file:
Using a text editor, open the file_udc_category_mapping_jde.csv file, located in the $pmserver\srcfiles folder.
Compare the values in this file to the values in Table 3-18 in Section 3.5.1.2, "About Configuring the Code Dimension for Oracle's JD Edwards Enterprise One or JD Edwards World UDCs" in the Oracle BI Applications Configuration Guide for Informatica PowerCenter Users.
Add any new values to the CSV file from the Table 3-18 entries by System Code, User Defined Code, and Category. Ensure that each Category value is spelled correctly, as this Category matches what is defined in the mappings for Oracle Supply Chain and Order Management Analytics for JD Edwards EnterpriseOne.
The values that are added to this CSV file will be added as UDC entries to the W_CODE_D table by Category.
For more information about the W_CODE_D table and values, see Oracle Business Analytics Warehouse Data Model Reference.
Use commas to separate the entries under the System Code, UDC column, and Category. Ensure that you do not include any spaces.
Save and close the file.
This section explains how to configure the domainvalues_Movement_Types_jde.csv. You populate this flat file with data before running the SDE_JDE_MvmntTypeDimension workflow.
To configure this file:
Identify the Movement Types in your JD Edwards EnterpriseOne source system by using the following SQL:
SELECT RTRIM(DRSY) || '~' || DRRT || '~' || LTRIM(DRKY) FROM F0005 AS MVMNT_TYPE_CODE WHERE DRSY = '00' AND DRRT = 'DT' AND DRKY <> ' '
Using a text editor, open the domainvalues_Movement_Types_jde.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the values from the MVMNT_TYPE_CODE column of the SQL to the MVMNT_TYPE_CODE column in the file.
The data must be copied starting from the second line. The first line is the column header.
In the file, for each MVMNT_TYPE_CODE, add the correct domain values for W_MVMNT_TYPE_CODE and W_MVMNT_TYPE_DESC. Valid domain values are OTHERS, CUSTOMER RETURN, or VENDOR RETURN.
All values for both W_MVMNT_TYPE_CODE and W_MVMNT_TYPE_DESC will be OTHERS except for those Document Types (Movement Types) that are actually used for Customer Returns or Vendor Returns. For those specific Document Types identified as such, use those values appropriately.
For more information on Movement Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainvalues_xact_type_codes_scom_jde_sales_ivclns.csv. You populate this flat file with data before running the SDE_JDE_XactTypeDimension_SalesInvoiceLine workflow.
To configure this file:
Using a text editor, open the domainvalues_xact_type_codes_scom_jde_sales_ivclns.csv file, located in the $pmserver\lkpfiles folder.
Identify the Invoice Line transaction types in JD Edwards EnterpriseOne by using the following SQL:
SELECT F0005.DRKY FROM F0005 WHERE DRSY = '00 ' AND DRRT = 'DT'
Map the values returned with the flat file to correspond with the XACT_TYPE_CODE source column. Associate the values in this file to the Transaction Type domain values for Invoice Lines as specified in the Oracle Business Analytics Warehouse Data Model Reference.
An example configuration is shown here:
XACT_TYPE_CODE,XACT_CODE,W_XACT_TYPE_CODE,W_XACT_TYPE_DESC,W_XACT_TYPE_CODE1,W_XACT_TYPE_DESC1 <DRKY>,SALES_IVCLNS,Standard Invoice,Standard Invoice,Order Management,Order Management source code
Note:
The values shown in the example are also the default values for the source code's domain values. If a Document Type <DRKY> is not configured in the flat file, the values presented in this example will be assumed by default for those documents.For more information about the Transaction Type domain values in W_XACT_TYPE_D, see Oracle Business Analytics Warehouse Data Model Reference.
Use commas to separate the entries under the System Code, UDC column, and Category. Ensure that you do not include any spaces.
Save and close the file.
This section explains how to configure the domainvalues_xact_type_codes_scom_jde_sales_ordlns.csv. You populate this flat file with data before running the SDE_JDE_XactTypeDimension_SalesOrderLine workflow.
To configure this file:
Using a text editor, open the domainvalues_xact_type_codes_scom_jde_sales_ordlns.csv file, located in the $pmserver\lkpfiles folder.
Identify the Order Line transaction types in JD Edwards EnterpriseOne by using the following SQL:
SELECT F0005.DRKY FROM F0005 WHERE DRSY = '00 ' AND DRRT = 'DT'
Map the values returned with the flat file to correspond with the XACT_TYPE_CODE source column. Associate the values in this column to the Transaction Type domain values for Order lines as specified in the Oracle Business Analytics Warehouse Data Model Reference.
An example configuration is shown here:
XACT_TYPE_CODE,XACT_CODE,W_XACT_TYPE_CODE,W_XACT_TYPE_DESC,W_XACT_TYPE_CODE1,W_XACT_TYPE_DESC1, W_XACT_TYPE_CODE2,W_XACT_TYPE_DESC2 <DRKY>,SALES_ORDLNS,Regular,Regular,EXTERNAL,External order,Self Ship,Internal inventory order
Note:
The values shown in the example are also the default values for the source code's domain values. If a Document Type <DRKY> is not configured in the flat file, the values presented in this example will be assumed by default for those documents.For more information about the W_XACT_TYPE_D domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Use commas to separate the entries under the System Code, UDC column, and Category. Ensure that you do not include any spaces.
Save and close the file.
This section explains how to configure the file_sales_order_status_jde.csv. You populate this flat file with data before running the SDE_JDE_StatusDimension_SalesOrder workflow. This flat file is the source for the table W_STATUS_DS and is available in the $pmserver\srcfiles folder.
To configure this file:
Using a text editor, open the file_sales_order_status_jde.csv file, located in the $pmserver\srcfiles folder.
Identify the Sales Order status combinations by analyzing your order activity rules setup, which will have data similar to this:
| DocType | LineType | Last Status | Description | Next Status | Description |
|---|---|---|---|---|---|
| SO | S | 520 | Enter Sales Order | 540 | Print Pickslips |
| SO | S | 520 | Enter Sales Order | 560 | Shipment Confirmation |
| SO | S | 520 | Enter Sales Order | 535 | In Warehouse Management |
| SO | S | 900 | Backordered in SO Entry | 540 | Print Pickslips |
Identify the distinct combination of DocType, LineType, Last Status, and Next Status from the F4211 table for all of your sales orders.
Render each of these combinations with a prefix of:
SALES_ORDLNS~<DocType>~<LineType>~<LastStatus>~NextStatus> SALES_PCKLNS~<DocType>~<LineType>~<LastStatus>~NextStatus> SALES_SCHLNS~<DocType>~<LineType>~<LastStatus>~NextStatus>
Copy the values rendered into the flat file to correspond with the STATUS_CODE, STATUS_NAME flat file column for each of the STATUS_CLASSes - SALES_ORDLNS, SALES_PCKLNS, and SALES_SCHLNS respectively.
An example configuration is shown here:
STATUS_CLASS,STATUS_CODE,STATUS_NAME,STATUS_CAT,STATUS_CAT_DESC SALES_ORDER_PROCESS,SALES_ORDLNS~SO~S~620~998,SALES_ORDLNS~SO~S~620~998,Being Processed,Being Processed SALES_ORDER_PROCESS,SALES_ORDLNS~SO~S~620~999,SALES_ORDLNS~SO~S~620~999,Closed,Closed SALES_ORDER_PROCESS,SALES_ORDLNS~SO~S~985~999,SALES_ORDLNS~SO~S~985~999,Closed,Closed SALES_PICKING_PROCESS,SALES_PCKLNS~SO~S~520~540,SALES_PCKLNS~SO~S~520~540,Not Yet Picked,Not Yet Picked SALES_PICKING_PROCESS,SALES_PCKLNS~SO~S~520~560,SALES_PCKLNS~SO~S~520~560,Not Yet Picked,Not Yet Picked
Note:
A set of hard-coded statuses are used to identify whether a Sales Order line is cancelled or not. These statuses are 980, 982, 983, 984, 985, 986, 987, 988, 990, 995, 996 and 997. No other Sales Order status is used to identify a cancelled Sales Order line. If you want a certain status combination to be treated as closed, then define it as closed in the Status Dimension flat file. When defined as closed, they will be overlooked by financial and operational backlog flags.
You do not have to configure certain statuses that are already hard-coded in the system like Closed, Cancelled, and so forth, as mentioned previously. The code automatically identifies them. However, a hard-coded status line should be present in the flat file to track these statuses, which are described in the following steps.
Append your configuration with these mandatory hardcoded statuses:
FULFILLMENT_STATUS,ORDER BOOKED,ORDER BOOKED,Order Booked,Order Booked FULFILLMENT_STATUS,ORDER ENTERED,ORDER ENTERED,Order Entered,Order Entered FULFILLMENT_STATUS,ORDER FULLY CANCELLED,ORDER FULLY CANCELLED,Order Fully Cancelled,Order Fully Cancelled FULFILLMENT_STATUS,ORDER FULLY PICKED,ORDER FULLY PICKED,Order Fully Picked,Order Fully Picked FULFILLMENT_STATUS,ORDER FULLY SCHEDULED,ORDER FULLY SCHEDULED,Order Fully Scheduled,Order Fully Scheduled FULFILLMENT_STATUS,ORDER FULLY SHIPPED,ORDER FULLY SHIPPED,Order Fully Shipped,Order Fully Shipped FULFILLMENT_STATUS,ORDER PARTIALLY CANCELLED,ORDER PARTIALLY CANCELLED,Order Partially Cancelled,Order Partially Cancelled FULFILLMENT_STATUS,ORDER PARTIALLY SCHEDULED,ORDER PARTIALLY SCHEDULED,Order Partially Scheduled,Order Partially Scheduled FULFILLMENT_STATUS,ORDER PARTIALLY SHIPPED,ORDER PARTIALLY SHIPPED,Order Partially Shipped,Order Partially Shipped FULFILLMENT_STATUS,ORDER NOT ELIGIBLE,ORDER NOT ELIGIBLE,Order Not Eligible,Order Not Eligible SALES_INVOICE_PROCESS,SALES_INVCLNS~CANCELLED,SALES_INVCLNS~CANCELLED,Cancelled,Cancelled SALES_INVOICE_PROCESS,SALES_INVCLNS~COMPLETED,SALES_INVCLNS~COMPLETED,Completed,Completed SALES_INVOICE_PROCESS,SALES_INVCLNS~OPEN,SALES_INVCLNS~OPEN,Open,Open SALES_INVOICE_PROCESS,SALES_INVCLNS~PENDING,SALES_INVCLNS~PENDING,Pending,Pending SALES_ORDER_PROCESS,SALES_ORDLNS~BEING PROCESSED,SALES_ORDLNS~BEING PROCESSED,Being Processed,Being Processed SALES_ORDER_PROCESS,SALES_ORDLNS~BLOCKED,SALES_ORDLNS~BLOCKED,Blocked,Blocked SALES_ORDER_PROCESS,SALES_ORDLNS~BOOKED,SALES_ORDLNS~BOOKED,Booked,Booked SALES_ORDER_PROCESS,SALES_ORDLNS~CANCELLED,SALES_ORDLNS~CANCELLED,Cancelled,Cancelled SALES_ORDER_PROCESS,SALES_ORDLNS~CLOSED,SALES_ORDLNS~CLOSED,Closed,Closed SALES_ORDER_PROCESS,SALES_ORDLNS~ENTERED,SALES_ORDLNS~ENTERED,Entered,Entered SALES_PICKING_PROCESS,SALES_PCKLNS~CANCELLED,SALES_PCKLNS~CANCELLED,Cancelled,Cancelled SALES_PICKING_PROCESS,SALES_PCKLNS~FULLY PICKED,SALES_PCKLNS~FULLY PICKED,Fully Picked,Fully Picked SALES_PICKING_PROCESS,SALES_PCKLNS~FULLY SHIPPED,SALES_PCKLNS~FULLY SHIPPED,Fully Shipped,Fully Shipped SALES_PICKING_PROCESS,SALES_PCKLNS~NOT RELEVANT,SALES_PCKLNS~NOT RELEVANT,Not Relevant,Not Relevant SALES_PICKING_PROCESS,SALES_PCKLNS~NOT YET PICKED,SALES_PCKLNS~NOT YET PICKED,Not Yet Picked,Not Yet Picked SALES_PICKING_PROCESS,SALES_PCKLNS~BACKORDERED,SALES_PCKLNS~BACKORDERED,Backordered,Backordered SALES_PICKING_PROCESS,SALES_PCKLNS~PURGED,SALES_PCKLNS~PURGED,Purged,Purged SALES_SCHEDULE_PROCESS,SALES_SCHLNS~BLOCKED,SALES_SCHLNS~BLOCKED,Blocked,Blocked SALES_SCHEDULE_PROCESS,SALES_SCHLNS~CANCELLED,SALES_SCHLNS~CANCELLED,Cancelled,Cancelled SALES_SCHEDULE_PROCESS,SALES_SCHLNS~CLOSED,SALES_SCHLNS~CLOSED,Closed,Closed SALES_SCHEDULE_PROCESS,SALES_SCHLNS~ENTERED,SALES_SCHLNS~ENTERED,Entered,Entered SALES_SCHEDULE_PROCESS,SALES_SCHLNS~NOT VALID,SALES_SCHLNS~NOT VALID,Not Valid,Not Valid
Save and close the file.
This section explains how to configure the file_lkp_chnl_typ_jde.csv. You populate this flat file with data before running the SDE_JDE_ChannelTypeDimension workflow.
To configure this file:
Identify the Channel Types in your JD Edwards EnterpriseOne source system by using the following SQL:
SELECT DRKY FROM F0005 WHERE DRSY = '90CB' AND DRRT = 'TC'
Using a text editor, open the file_lkp_chnl_typ_jde.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Copy the values from the DRKY column of the SQL to the CHNL_TYPE_CODE column in the file.
The data must be copied starting from the second line. The first line is the column header.
In the file, add the correct domain values for Channel Type Group Code (W_CHTY_GRP_CODE), Channel Type Subgroup Code (W_CHTY_SUBG_CODE), and W_INBOUND_TYPE_FLG for each Channel Type Code (CHNL_TYPE_CODE).
For more information on Channel Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and Close the file.
This section explains how to configure the file_lkp_consign_inv_org_jde.csv. You populate this flat file with data before running the SDE_JDE_Inventory_Daily_Bal_Fact workflow.
To configure this file:
Identify the Inventory Consignment Branch/Plants (Warehouse and Inventory Organization IDs) in your JD Edwards EnterpriseOne source system.
Using a text editor, open the file_lkp_consign_inv_org_jde.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Manually enter the first value for a valid Inventory Consignment Branch/Plant on the second line in the file. The first line is the column header.
Note:
The data for each Branch/Plant must be entered as 12 characters and right-justified with leading blanks. For example, Branch/Plant (INVENTORY_ORG_ID) ABC should be entered as ---------ABC with nine leading blanks.Enter each Inventory Consignment Branch/Plant on a separate line thereafter.
For more information on consignments, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the file_lkp_return_loc_jde.csv. You populate this flat file with data before running the SDE_JDE_Inventory_Daily_Bal_Fact workflow.
To configure this file:
Identify the Inventory Return Locations in each Branch/Plant (Warehouse/Inventory Organization ID) in your JD Edwards EnterpriseOne source system.
Using a text editor, open the file_lkp_return_loc_jde.csv file, located in the $PMServer\LkpFiles directory (for example \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Manually enter the first value for a valid Inventory Return Location on the second line in the file. The first line is the column header.
Note:
The data for each Return Location must be entered in the following format on each line:STORAGE_LOC~Branch/Plant~Location~Lot Number
where:
STORAGE_LOC~ is a constant
Branch/Plant value is 12 characters and right-justified, blank filled
Location is left-justified and 20 characters right-blank filled
Lot Number is entered with no trailing blanks
For example, an Inventory Return Location with a Branch/Plant of ABC, a Location of 123 and a Lot Number of 789, would be entered in the following format:
STORAGE_LOC~ ABC~123 ~789
Enter each Inventory Return Location on a separate line thereafter.
For more information on return locations, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to process the Bill of Materials (BOM) for exploding to a multi-level structure to ultimately populate both the W_BOM_HEADER_D and W_BOM_ITEM_F tables.
JD Edwards EnterpriseOne maintains BOM information in a single level format, but Oracle BI Applications requires it in multi-level format. Therefore, before loading data into Oracle BI Application tables, the single level structure needs to be exploded into a multi-level structure.
Because all of the BOM information is stored in one single table in JD Edwards EnterpriseOne source and there are no defined levels for the BOM, the system has to loop through iteratively to get the BOM exploded. Also, Oracle BI Applications maintains all the revisions to the components as a new version of the BOM along with their effective dates. Considering these facts, it is not feasible to use ETL to convert the single level BOM to a multi-level BOM. Therefore the logic from an existing JD Edwards EnterpriseOne UBE (R30460) was used to create a new UBE for the explosion.
This new UBE (R30461) extracts the manufactured end products and converts the single-level BOM format into a multi-level BOM format. In addition, it also extracts some required information like Left bounds/Right bounds and level parents (1 - 10).
The UBE loads the multi-level BOM structure for manufactured end products with each revision into two work files respectively for BOM header and item (component). The ETL then extracts the data from the two work files and loads it into the Oracle BI Applications tables.
Note:
If you plan to consume analytics on Bill of Materials, it is mandatory to run this UBE before starting the ETL. This UBE and the related JD Edwards EnterpriseOne objects are created solely for the benefit of analytics and therefore will not be available in the existing source system.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 off.
In JD Edwards EnterpriseOne, the left and right bounds are calculated by the UBE (R30461).
Note:
Before you run an ETL using a BOM, you must compile and deploy the SQL code in Compute_Bounds_Ora11i.sql. For more information, see Section 3.1.5, "How to Deploy Stored Procedures" in the Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users.
|
 Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
