This chapter contains the following topics:
Section 4.1, "Understanding Oracle Demantra Predictive Trade Planning"
Section 4.2, "Integrating JD Edwards EnterpriseOne Customer and Item Information"
Section 4.3, "Integrating JD Edwards EnterpriseOne Future List Price and Item Cost"
Section 4.4, "Integrating JD Edwards EnterpriseOne Sales History"
Section 4.5, "Integrating JD Edwards EnterpriseOne Promotional Pricing"
Oracle Demantra Predictive Trade Planning provides robust trade promotion and account planning, sales forecasting, and promotion optimization capabilities. It is a sales and promotion planning system that enables account managers to develop highly accurate, account-level sales forecasts and event planning from their daily sales planning activities. A single planning environment provides visibility to all the information that an account manager needs daily to make profitable decisions. The predicted effect on trade-fund budgets and manufacturer and retailer profitability projections are available immediately. All account-level information is continuously aggregated and visible to sales management at any time.
This section provides an overview of JD Edwards EnterpriseOne Customer and Item integration with Oracle Demantra products and discusses how to:
Run the SCP Customer Master Information Extract program.
Run the SCP Item UOM Extract program.
Set processing options for the SCP Item UOM Extract program (R34A480)
The JD Edwards EnterpriseOne integration with Oracle Demantra products requires that item and customer information be extracted from the JD Edwards EnterpriseOne system. The Oracle Demantra workflow imports the extracted customer and item data and processes it in the Oracle Demantra system. The JD Edwards EnterpriseOne system supports incremental loads of the customer and item data, and maintains this data exclusively. This integration leverages or enhances existing batch solutions that you may use to extract customer and item information.
Both the Customer Master Information Extract program (R34A530) and the Item UOM Extract program (R34A480) are initiated by Oracle Demantra (through Runubexml.exe) with predefined values for the From Days and Incremental Load Indicator fields. Incremental loads enable you to import into Oracle Demantra only those items and customers that have been modified since the last interface run. The From Days value is always an absolute value. The From Date value is calculated as follows:
From Date = System Date – From Days
End Date = System Date
This table lists the results of these formulas:
| Example | Given Criteria | Results | ||
|---|---|---|---|---|
| System Date | From Days | From Date | End Date | |
| 1 | 01/19/2007 | 5 | 01/14/2007 | 01/19/2007 |
| 2 | 01/19/2007 | 0 | 01/19/2007 | 01/19/2007 |
|
Note: The system date is the date on which the program is running.These calculations occur only if the Incremental Load Indicator field contains a value of 1. A value other than 1 indicates a full load. When the incremental load indicator is 1, the JD Edwards EnterpriseOne system sends only items that were modified between the From Date and the End Date to the Oracle Demantra product |
The SCP Customer Master Information Extract program (R34A530) extracts customer information to a text file. In addition to the data mapping requirements for Oracle Demantra Demand Management, the system extracts the customer's parent address number and description. The system extracts and incrementally loads customer data from these tables:
Customer Master by Line of Business (F03012)
Address Book Master (F0101)
Address Book - Who's Who (F0111)
Address Book - Phone Numbers (F0115)
Address by Date (F0116)
The SCP Item UOM Extract program (R34A480) extracts item information to a text file. The system extracts and incrementally loads item information from these tables:
Item Master (F4101)
Item Branch (F4012)
Item Cost (F4105)
Item Units of Measure Conversion Factors (F41002)
APS Integration Constants (F34A10)
The SCP Outbound Processor retrieves four parameters from the RUNUBEXML.exe file to:
Pass the From Days,Incremental Load Flag, and Demantra Flag values to the SCP Item UOM Extract program to perform an incremental load for the items.
Pass the From Days,Incremental Load Flag, and Demantra Flag values to the SCP Customer Master Information Extract program to perform an incremental load for the customers.
To run the SCP Customer Master Information Extract program:
Select Planning and Scheduling (G34A), Planning Outbound Processor and complete these steps:
On the Available Versions form, select a version of SCP Outbound Processor (R34A400).
From the Row menu, select Processing Options.
On the Processing Options form, select the Items tab.
Indicate which version of the SCP Customer Master Information Extract program you want the system to run.
Click OK.
|
Note: The SCP Customer Master Information Extract program does not appear in any JD Edwards EnterpriseOne menus and does not contain processing options. You indicate a version of the SCP Customer Master Information Extract program that the system runs while running the SCP Outbound Processor program. |
Select Planning & Scheduling (G34A), Planning Outbound Processor and complete these steps:
On the Available Versions form, select a version of the SCP Outbound Processor program (R34A400).
Select Processing Options from the Row menu.
On the Processing Options form, select the Items tab.
Indicate which version of the SCP Item UOM Extract program you want the system to run.
Click OK.
|
Note: The SCP Item UOM Extract program does not appear in any JD Edwards EnterpriseOne menus. You indicate a version of the SCP Item UOM Extract program that the system runs with running the SCP Outbound Processor program. |
Processing options enable you to specify the default processing for programs and reports.
Specify the cost method that the system uses during the extract. Values are:
01: Last in.
02: Weighted average.
03: Memo.
04: Current.
05: Future.
06: Lot.
07: Standard.
08: Purchasing-base cost no adds.
09: Manufacturing last cost.
20: Historical average.
|
Note: The system stores these values in the 40/CM UDC table. |
This section provides an overview of JD Edwards EnterpriseOne Future List Price and Item Cost integration with Oracle Demantra products and discusses how to:
Run the Future List Price Extract program.
Set processing options for the Future List Price Extract program (R45529).
Run the Item Cost Extract program.
Set processing options for the Item Cost Extract program (R41053).
The Oracle Demantra Predictive Trade Planning system uses the future list price (or base price) and item cost information to calculate the profitability of a promotion when planning a promotion. The future list price and item cost information are extracted from the JD Edwards EnterpriseOne system. These extracts are currently implemented in the Oracle Demantra Demand Management integration as part of SCP Outbound Processing. Although base price and item cost exist in the current extracts, they are not consumed by the Oracle Demantra product as part of the Demand Management integration.
List price information is maintained in JD Edwards EnterpriseOne products and interfaces with the Oracle Demantra Predictive Trade Planning product. The Oracle Demantra system uses this data to determine the expected price to be paid by customers without any promotional pricing included. You run the Future List Price program (R45529) to extract data from the JD Edwards EnterpriseOne system.
The JD Edwards EnterpriseOne system enables you to enter base prices for the same branch/plant, item, customer, unit of measure, and currency with overlapping dates. Whenever you set date ranges with overlapping dates, the price that expires first is the price that is used for the date range. To accomplish this in the Trade Promotions integration, the system sorts the records in the flat file in descending order by item, customer group, customer, and effective through date. The workflow processes that load the JD Edwards EnterpriseOne extract files into the Oracle Demantra system take the first price they encounter in the Oracle Demantra table and overwrite it with subsequent prices for the same item and customer. Thus, the price for the date range that expires first is the one that is loaded into the Oracle Demantra system last, and it is used for trade promotions planning.
The Future List Price Extract program (R45529) extracts item base price data from the JD Edwards EnterpriseOne system and stores the data in a flat file. The program selects records from the Item Base Price table (F4106), where these fields meet the stated criteria:
Location is blank.
Lot/Serial Number is blank.
Item Group Key ID is blank.
Lot Grade is blank.
From Potency is zero.
Item cost information is maintained in the JD Edwards EnterpriseOne system and imported into Oracle Demantra Predictive Trade Planning. The Oracle Demantra system uses this data to determine the cost of an item for promotional planning purposes. You run the SCP Item UOM Extract program (R41053) to extract item cost data from the JD Edwards EnterpriseOne system.
The Item Cost Extract program (R41053) selects the item cost based on the cost method specified in the processing options for the version. Customers can add their own cost methods, and a cost method specific to Trade Promotions Planning. customers can maintain future costs using cost method 05 or Trade Promotion Planning costs using their own configured cost method. The item cost information must come from a configurable cost method.
The system extracts the item cost by branch in the primary unit of measure, regardless of cost level in the JD Edwards EnterpriseOne system.
The Item Cost Extract program (R41053) extracts data from the JD Edwards EnterpriseOne system to a flat file. The UBE selects records from the Item Branch table (F4102) using data selection criteria and a processing option for the cost method.
Select Planning and Scheduling (G34A), Planning Outbound Processor and complete these steps:
On the Available Versions form, select a version of SCP Outbound Processor (R34A400).
From the Row menu, select Processing Options.
On the Processing Options form, select the List Price tab.
Indicate which version of the Future List Price Extract program you want the system to run.
Click OK.
|
Note: The Future List Price Extract program does not appear in any JD Edwards EnterpriseOne menus. You indicate a version of the Future List Price Extract program that the system runs with running the SCP Outbound Processor program. |
Processing options enable you to specify default processing for programs and reports.
Specify whether to include item pricing in the extract file. Values are:
Blank: Do not include.
1: Include.
Specify whether to include records in the Item Base Price table (F4106) for a specific customer price group in the extract file. These records are selected in addition to item pricing and item/customer pricing records. The customer price group specified must be a simple customer price group, and it must exist in the 40|PC UDC table. Records for this customer price group cannot exist in the Group Code Key Definition table (F4092). To review entries in this table, use the Group Code Definition application (P4092). Values are:
Blank: Do not include.
1: Include.
Specify whether item/customer pricing is included in the extract file. These records are selected in addition to customer price group records. Values are:
Blank: Do not include.
1: Include.
Specify which currency the system should extract. If you enter a currency code, the system extracts that specified currency. If you do not enter a currency code, the system extracts the currency designated for company 000000.
Enter a unit of measure for the price that the system extracts. If you do not enter a unit of measure, the system extracts prices in the item's primary UOM. Values are:
01: Primary UOM.
02: Secondary UOM.
03: Purchasing UOM.
04: Pricing UOM.
05: Shipping UOM.
06: Production UOM.
07: Component UOM.
08: Weight UOM.
09: Volume UOM.
|
Note: The system stores these values in the 34A|UM UDC table. |
Enter the branch/plant for which the system extracts prices. If you do not enter a branch/plant, the system extracts prices for all branch/plants.
Specify whether the system allows the extraction of multiple prices. Values are:
Blank: Error.
1: Allow multiple price extractions.
|
Note: If you leave the Branch/Plant option blank but select this option, the system reports an error and stops processing.If you leave the Branch/Plant option and this option blank, the system allows multiple prices to be written to the extract file. If you enter a value for the Branch/Plant option, multiple prices are not possible. For the purposes of the Future List Price Extract program (R45529), item-specific prices, customer group prices, item/customer prices and prices that have overlapping effective and expiration dates are not considered multiple prices. |
Select Planning and Scheduling (G34A), Planning Outbound Processor and complete these steps:
On the Available Versions form, select a version of SCP Outbound Processor (R34A400).
From the Row menu, select Processing Options.
On the Processing Options form, select the Item Cost tab.
Indicate which version of the Item Cost Extract program you want the system to run.
Click OK.
|
Note: The Item Cost Extract program does not appear in any JD Edwards EnterpriseOne menus. You indicate a version of the Item Cost Extract program that the system runs with running the SCP Outbound Processor program. |
This section provides an overview of D Edwards EnterpriseOne Sales History integration with Oracle Demantra products and discusses how to:
Run the SCP Sales History Extract programs.
Set processing options for the SCP Sales History Extract program (R34A425) and the SCP F4211 Sales History Extract program (R34A435).
The JD Edwards EnterpriseOne Sales History integration with the Demantra Predictive Trade Planning product requires that information about spending because of discounted promotional pricing (off-invoice deals) and net price is extracted from the JD Edwards EnterpriseOne system.
To support promotional pricing, the sales order history extracts include spending that results from discounted promotional pricing (off-invoice deals). This amount is broken down by promotion (for example, PROMOTION01 incurred a 1.00 USD discount, and PROMOTION02 incurred a 0.50 USD discount). The extended amount converted to the currency code of the default company 00000 is imported to the Oracle Demantra system. Since the sales order history extracts are set up to extract overlapping date ranges (two weeks worth of data for each week), the system could—potentially—duplicate the extraction of promotional pricing data. The full key from the Price History table (F4074) is sent in the flat file, and Oracle Demantra processing detects and bypasses duplicates when loading the data into the Oracle Demantra system.
To support net price, the sales order history extracts are enhanced to convert the unit price to the currency code of default company 00000. The unit of measure for the unit price is converted to the planning unit of measure, as defined in the integration constants. The planning unit of measure is converted to achieve consistency with other JD Edwards EnterpriseOne extracts.
The SCP Sales History Extract program (R34A425) retrieves:
Sales history information that is extracted from the Sales Order History table (F42119).
Sales orders with specific item category codes and other sales detail information from the Sales Order History table.
Sales orders by document type, line type, and status, using the Supply/Demand Inclusion Rules program (P34004).
The SCP F4211 Sales History Extract program (R34A435) retrieves:
Sales history information that is extracted from the Sales Order Detail table (F4211).
Sales orders with specific item category codes and other sales detail information from the Sales Order Detail table.
Sales orders by document type, line type, and status, using the Supply/Demand Inclusion Rules program (P34004).
Select Planning and Scheduling (G34A), Planning Outbound Processor and complete these steps:
On the Available Versions form, select a version of SCP Outbound Processor (R34A400).
From the Row menu, select Processing Options.
On the Processing Options form, select the Sales History tab.
Indicate which versions of the SCP Sales History Extract and the SCP F4211 Sales History Extract programs you want the system to run.
Click OK.
|
Note: The SCP Sales History Extract and SCP F4211 Sales History Extract programs do not appear in any JD Edwards EnterpriseOne menus. You indicate the versions of the respective sales history extract programs that the system runs with running the SCP Outbound Processor program. |
Processing options enable you to specify the default processing for programs and reports. Both sales order extract programs have the same processing options.
Specify the beginning date for the selection of sales history to be included. The system does not include sales orders with a promised ship date before this date.
Define which version of the supply/demand inclusion rules the program reads. These rules define the criteria used to select items for processing.
Specify whether the system includes promotional pricing as part of the sales history extract. A separate extract file is created for price history. Values are:
Blank: Do not include.
1: Include.
|
Note: A Price History Extract File Definition must be provided in the SCP Outbound Processor processing options. |
This section provides an overview of JD Edwards EnterpriseOne Promotional Pricing integration with Oracle Demantra and discusses:
Running the Inbound Promotional Pricing program.
Setting processing options for Inbound Promotional Pricing (R45640).
Running the Live Promotions Update program.
Setting processing options for Live Promotions Update (R45720ZB)
Running the Pricing Table Purge program.
Setting processing options for Pricing Table Purge (R45400P).
The Demantra Predictive Trade Planning system enables you to create a promotional price for an item or SKU. The Oracle Demantra system achieves this price based upon many factors. The JD Edwards EnterpriseOne Advanced Pricing integration with Oracle Demantra requires that after you accept and activate a promotional discount, the discount must be applied to the JD Edwards EnterpriseOne advanced pricing tables. The accounting for the discount occurs in one of two methods:
Off-invoice. The discount is applied to the final price that is invoiced to the customer.
Billback. This discount does not come from the invoiced price, but rather from an accrual account. In most cases, the customer shortpays the invoice at a later date. The Oracle Demantra system validates the shortpay as accepted per agreement and sends a confirmation to the JD Edwards EnterpriseOne Accounts Receivable system in the form of a write-off that is deducted from the amount of the accrual account.
The Inbound Promotional Pricing program (R45640) performs field editing for the inbound text file and translates the text file into the Live Promotions Interface - Inbound/Outbound table (F4572Z1).
The Live Promotions Update program (R45720ZB) updates the Live Promotions Interface - Inbound/Outbound table. It also creates pricing definitions that correspond to an Oracle Demantra promotion. The system uses a 10 digit promotion ID to represent an Oracle Demantra promotion. The JD Edwards EnterpriseOne Advanced Pricing system stores the promotion ID within the Promotion Header table (F40P01) and the Price Adjustment Type table (F4071).
The SCP Inbound Processor program (R34A410) program transfers flat file extracts from the Oracle Demantra system to the JD Edwards EnterpriseOne system.
The design assumptions include:
There is no support for level breaks or basket pricing.
The G/L Offset and Subledger in G/L fields are the only adjustment definition fields that you can manually modify. If the Live Promotions Update program is processing a changed promotion, the system does not update these fields in the Price Adjustment Type table.
The JD Edwards EnterpriseOne Advance Pricing system stores promotions in the Price Adjustment Type table. The system marks the FUTUSE12 (future use) field with DMTR to indicate that a promotion is from the Oracle Demantra system.
There is no support for percentage-based adjustment amounts. You create all adjustments based upon following:
The Basis Code equals 5 (add on amount).
The Adjustment Control Code equals 2 (print on document) or 4 (accrued to G/L).
The Live Promotions Update program updates the data when it encounters an existing promotion for either a billback or off-invoice. An inbound record which does not have an address book number or an item indicates that the JD Edwards EnterpriseOne system deletes the promotion.
For every promotion, the JD Edwards EnterpriseOne system creates an adjustment definition name using the Next Numbers Revisions program (P0002). The next numbers are not company-specific.
The currency amount for promotions is expressed as a negative number in the flat file.
The Live Promotions Update program provides a warning message in the message center if you manually attach a customer-and-item combination to an Oracle Demantra controlled promotion and attempt to import the same item-and-customer combination.
The Oracle Demantra Predictive Trade Planning integration requires that old or obsolete pricing information is purged from these JD Edwards EnterpriseOne pricing tables:
Price Adjustment Schedule (F4070).
Price Adjustment Type (F4071).
Price Adjustment Detail (F4072).
Free Goods Master (F4073).
Rebates Thresholds (F4077).
The price purging process is applied to all general adjustments and not limited to promotion adjustments. All purge pricing information is archived in these tables:
Price Adjustment Schedule Purge (F4070P).
Price Adjustment Type Purge (F4071P).
Price Adjustment Detail Purge (F4072P).
Free Goods Master File Purge (F4073P).
Rebates Thresholds (F4077P).
The Pricing Table Purge program (R45400P) deletes all obsolete pricing information and updates the preceding purge tables.
During the purge process, The JD Edwards EnterpriseOne system goes through various categorized scenarios to purge pricing information. The categories include:
Adjustments.
Expired adjustment details.
Obsolete adjustment definitions.
The following table list scenarios and explanations:
| Scenario | Explanation |
|---|---|
| Adjustments | The adjustments in the schedule are deleted from the Price Adjustment Schedule table if these adjustments expire before the date defined in the processing option in this schedule. The deleted records are archived in the Price Adjustment Schedule Purge table. |
| Expired Adjustment Details | If the adjustment details expire before the date defined in the processing option and if there are no associated rebate records in the Rebates Threshold table, the adjustment detail records are deleted from the Price Adjustment Detail table. The deleted records are archived in the Price Adjustment Detail Purge table.
If the adjustment details expire before the date defined in the processing option and if there are associated rebate records in the Rebates Threshold table, then:
If the system deletes adjustment details records which are associated with free goods, the system deletes the free goods records from the Free Goods Master File table and archives the records in the Free Goods Master File Purge table. |
| Obsolete Adjustment Definitions | If the system does not find the adjustment name in any active schedule, the adjustment does not have any detail lines, and the adjustment name does not appear in the Price History table, the system considers the adjustment definition as obsolete.
The system checks the adjustment name in the Price History table before purging because an adjustment name might still be attached to a transaction. If deleted, then the adjustment name in the Price History table becomes invalid. Furthermore, when you create a credit order, the adjustment name must be in the Price History table. |
Select Daily Processing (G4231121), Inbound Promotional Pricing.
Processing options enable you to specify the default processing for programs and reports.
Select to automatically call the Live Promotions Update program (R45720ZB), which runs over the data created in the batch run. Values are:
Blank: No.
1: Yes.
Specify a version of the Live Promotions Update program that the system runs. If you leave this processing option blank, the system uses version XJDE0001. The system runs a version only when you activate the option to automatically launch live promotions update.
Select Daily Processing (G4231121), Live Promotions Update.
Processing options enable you to specify the default processing for programs and reports.
Enter the preference hierarchy the system uses to write the promotional price adjustment definitions. The promotional hierarchy must use a hierarchy based on the Sold to, Ship to, or parent address number. The system writes the item number as promotional detail records with an individual item number and customer number.
Specify the unit of measure that the system converts the promotional amount to. A conversion does not occur if the value is set to the planning unit of measure.
Specify the branch/plant the system uses to find a conversion factor when conversions are set up to be branch specific.
Enter the G/L offset for the billback promotional price adjustment definition. You can change the value on the price adjustment definition after the system creates it. If a promotion is updated by this pricing engine then the value is not used to update the promotional adjustment definition.
Enter the subledger for the billback promotional price adjustment definition. You can change the value on the price adjustment definition after the system creates it. If a promotion is updated by this pricing engine then this value is not used to update the promotional adjustment definition.
Enter the G/L offset for the off-invoice promotional price adjustment definition. You can change the value on the price adjustment definition after the system creates it. If a promotion is updated by this pricing engine then this value is not used to update the promotional adjustment definition.
Enter the subledger for the off-invoice promotional price adjustment definition. You can change the value on the price adjustment definition after the system creates it. If a promotion is updated by this pricing engine then this value is not used to update the promotional adjustment definition.
Enter the product code of the UDC value that the system uses as the driver for the schedule application.
Enter the user defined code value that the system uses as the driver for schedule application.
Enter the driver method for which the system uses the UDC values entered in options 1 and 2. Values are:
Blank: The system ignores the values in options 1 and 2 and does not apply adjustment definitions to any schedules. This action enables you to manually attach adjustment definitions to appropriate schedules.
1: The system uses the UDC values that you entered in options 1 and 2 to apply the created adjustment definitions to the schedule name indicated in the code value of the UDC. The value must exist in the 40|AS UDC.
2: The system uses values in from the 40|AS UDC to apply to valid schedules. The system skips the values indicated in options 1 and 2. The value must exist in the 40/AS UDC.
The system applies new promotional adjustment definitions to the schedules that you determine here. If you are adding promotional details to existing promotional adjustment definitions, the effective dates on these selected schedules are potentially extended to ensure resolution of these new details, but changed promotional definitions are not added to these schedules.
Enter the beginning sequence value that the system applies to the schedule. This is the first number identified for schedule application.
Enter the ending sequence value that the system applies to the schedule. This is the last number identified for schedule application.
Enter the value the system uses as the factor by which to increment the number while looking for a place to apply the schedule.
Select Periodic Processing (G4231122), Pricing Table Purge.
Processing options enable you to specify the default processing for programs and reports.
Enter the number of past days from today's date that the adjustment definition in the schedule or the adjustment detail should have expired before purging. This is an absolute. For example: If today's date is 02/02/2010 and you specify 365 as the Purge Days, the system purges all adjustments that expired before 02/02/2009.
If the adjustment name is not in any active schedule, the adjustment does not have detail lines, and the adjustment name is not in the Price History table (F4074), the system considers the adjustment definition as obsolete.