This chapter describes how Demantra Deductions and Settlement Management (DSM) integrates with EnterpriseOne.
This chapter covers the following topics:
Important: The content in this chapter refers to the new integration available between J.D. Edwards EnterpriseOne and the Value Chain Planning product suite (which includes Oracle Demantra) using the Process Integration Pack (PIP) Oracle Value Chain Planning Integration to J.D. Edwards EnterpriseOne.
It does not refer to the older flat-file based integration between JD Edwards EnterpriseOne and Demantra.
Please contact Oracle Support Services for the certification status of this PIP-based integration with Oracle Demantra version 7.3.
The Oracle Demantra Deductions and Settlement Management (DSM) product requires implementation of Oracle Demantra Demand Management and Predictive Trade Planning (PTP) as prerequisites. This chapter describes integrating DSM to the JD Edwards EnterpriseOne application suite, given DM and PTP have already been integrated. For more information, see:
"Demantra Demand Management to EnterpriseOne Integration"
"Demantra Predictive Trade Planning to EnterpriseOne Integration"
Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Installation Guide
Oracle Value Chain Planning Integration to JD Edwards EnterpriseOne Implementation Guide
Oracle EnterpriseOne and Oracle Demantra exchange information through the use of the Oracle Data Integrator (ODI) Adapter for Value Chain Planning, the Oracle Data Integrator (ODI) to transform the data, and Oracle Demantra integration interfaces and workflows as shown in the following diagram:
The integration processes can be run when required from APS Planning. After EnterpriseOne to Demantra Demand Management integration loads Demantra with sales history, price list and units of measure data, DSM data can be collected (deductions, claims). The DSM data flows directly from EnterpriseOne to Demantra via ODI, where it is transformed.
The following integration points are part of the integration between the Oracle Demantra Deductions and Settlement Management (DSM) module and JD Edwards EnterpriseOne applications.
The following diagram provides more details about the business process and data that flows between EnterpriseOne and Oracle Demantra DSM:
Note: Extracting information from EnterpriseOne needs to be done completely by the user prior to running any of the collection programs that bring data into DSM (such as Deductions and Payment Confirmations). Similarly, importing information back into EnterpriseOne needs to be done after running any publish programs that export data out of DSM (such as Approved/Denied Deductions and Payment Requests).
The business process is as follows:
From APS, run Collect DSM Data with the parameter "Load Deductions" = Yes.
Deductions – Deduction information extracted from the EnterpriseOne Accounts Receivable (A/R) module is loaded into DSM via ODI. When new deductions are created in EnterpriseOne, they are sent from EnterpriseOne to DSM via ODI and loaded as settlements of type “Deduction”.
Review deductions in DSM, match them to promotions, and approve or deny the deductions.
From APS, run Publish DSM Results with the parameter "Publish Deduction Dispositions" = Yes.
Approved and Denied Deductions – When deductions are cleared in DSM, the resulting information is transformed by ODI and available for import back into the EnterpriseOne Accounts Receivable (A/R) module. You will need to import the Deduction Dispositions file into EnterpriseOne.
Note: A single deduction received from EnterpriseOne can be split into multiple deductions in DSM. Each of the split deductions interfaces back to EnterpriseOne separately. Only deductions that have reached a status of Approved or Denied interface to EnterpriseOne.
Enter claims in DSM, match them to promotions and approve the claims.
From APS, run Publish DSM Results with parameter "Publish Claims" = Yes.
Payment Requests – Requests for Payment, such as Check Requests, are exported from DSM and available for import into the EnterpriseOne Accounts Payable (A/P) module. You will need to import the Claims into EnterpriseOne.
Review the payment request in EnterpriseOne and issue payments against the payment requests from DSM and extract the payment requests from EnterpriseOne.
From APS, run Collect DSM Data with parameter "Load Payment Confirmations" = Yes.
Payment Confirmation – The payment confirmations extracted from EnterpriseOne are transformed by ODI and loaded into DSM where they can be viewed against the claims entered in Step 4.
The following additional interface to Oracle Demantra is provided:
Claims Entry – Claims for trade payment must be manually entered into the Demantra DSM module.
The following must be manually setup in preparation for the integration:
The "GL Code" level in the Settlement hierarchy must have an entry with a "GL Code Desc" value of "Default."
The “Status” level in the Settlement hierarchy must have an entry with a “Settlement Status Desc” value of “New.”
The “Type” level in the Settlement hierarchy must have an entry with a “Settlement Type Desc” value of “Deduction.”
The “Invoice” level in the Settlement hierarchy must have an entry with an “Invoice Desc” value of “DEFAULT SETTLEMENT Invoice."
The “Linked Promotion” level in the Settlement hierarchy must have an entry with a “Promotion Desc” value of “Default Promotion.”
The “GL Code” level in the Settlement hierarchy must be populated with valid EnterpriseOne Reason Codes.
The table containing valid EnterpriseOne Currency Codes is called "curr_code".. This table will needed to be populated with valid EnterpriseOne currency codes. Initial values are:
CODE_ID | CURR_CODE | CODE_DESC |
---|---|---|
0 | USD | US Dollar |
1 | CAD | Canadian Dollar |
2 | EUR | European Euro |
The following Oracle Demantra series support the integration:
The "Closed Settlement Amount" references the existing settlement_amount field on the Settlement table for Settlements with a Status of:
Approved
Duplicate
Denied, or
Write Off
“Settlement Account ID” that references the existing t_ep_lr2_EP_ID field on the Settlement table.
The following attributes and series support the DSM integration. The “Corresponding Series” column indicates the series that corresponds to the attribute. Series are required to show attributes in worksheets and include them in the integration interface data profiles:
Note: For every row in this table, the Demantra Level is: "Settlement" and the Demantra Database Table is "SETTLEMENT".
EnterpriseOne Field Name |
EnterpriseOne Field Type |
Demantra Level Attribute |
Demantra Database Table Column |
Corresponding Series | Comments |
---|---|---|---|---|---|
Settlement Amount (Transaction Currency) |
Number (15,2) | Settlement Amount Native Curr | SETTLEMENT _AMOUNT_NATIVE_CURR | Settlement Amount Native Curr | Settlement amount in the native currency. Applies to both Deduction and Claims. |
Transaction Currency Code | String (3) | Native Curr | NATIVE_CURR_ ID | Native Curr | Native currency code. Applies to both Deduction and Claims |
Display Decimals | Integer (1) | Native Display Decimals | NATIVE_DISPLAY_ DECIMALS | Native Display Decimals | Number of decimal positions that the native currency amount should be rounded to during conversions and splits |
Document – Original | Number (8) | EnterpriseOne Document | E1_DOCUMENT | E1 Document | EnterpriseOne reference. Not used in DSM |
Document Type – Original | String (2) | EntepriseOne Document Type |
E1_DOCUMENT_ TYPE | E1 Document Type | EnterpriseOne reference. Not used in DSM |
Document Pay Item – Orig | String (3) | EnterpriseOne Document Pay Item |
E1_DOCUMENT_ PAY_ITEM | E1 Document Pay Item | EnterpriseOne reference. Not used in DSM |
Document Company (Original) |
String (5) | EnterpriseOne Document Company |
E1_DOCUMENT_ COMPANY | E1 Document Company | EnterpriseOne reference. Not used in DSM |
Invoice Date | Date | EnterpriseOne Invoice Date |
E1_INVOICE_ DATE | E1 Invoice Date | Invoice Date for invoice that deduction occurred on. Since we won't be loading Invoices from EnterpriseOne, it is probably best to not try to populate the existing Invoice Date on the invoice file. |
Check Amount (Base Currency) |
Number (15,2) | Cust Check Amount | CUST_CHECK_ AMOUNT | Cust Check Amount | Amount of the customer's check in the base currency that paid the invoice from which the deduction was created |
Check Amount (Transaction Currency) |
Number (15,2) | Cust Check Amount Native Curr | CUST_CHECK_ AMOUNT_NATIVE _CURR | Cust Check Amount Native Curr | Amount of the customer's check in the native currency that paid the invoice from which the deduction was created |
Currency Code | String(3) | Cust Check Native Curr Code | CUST_CHECK_NATIVE_CURR_CODE | Cust Check Native Curr Code | Currency Code for the customer's check |
Display Decimals | Integer(1) | Cust Check Display Decimals | CUST_CHECK_ DISPLAY_ DECIMALS | Cust Check Display Decimals | Display decimals for the customer's check amount in native currency |
Claim Invoice # | Char | Claim Invoice Num | CLAIM_INVOICE_ NUM | Claim Invoice Num | The Invoice Number from the claim paperwork. Manually entered when entering Claim. |
Claim Invoice Date | Date | Claim Invoice Date | CLAIM_INVOICE_ DATE | Claim Invoice Date | The Invoice Date from the claim paperwork. Manually entered when entering claim. |
Settlement Type | String (2) | Check Type | CHECK_TYPE | Check Type | Indicates the type of payment issued in EnterpriseOne to satisfy the check request |
Check Amount | Number (15,2) | Check Amount | CHECK_AMOUNT | Chekck Amount | The amount of the payment generated from EnterpriseOne for the claim |
Currency | String (3) | Check Currency | CHECK_CURRENCY | Check Currency | The currency code for the payment issued from EnterpriseOne |
Multiple Payments | String (10) | Multiple Payment Indicator | MULTIPLE_PAYMENT_INDICATOR | Multiple Payment Indicator | Indicates if multiple payments were issued in EnterpriseOne to satisfy the claim |
Bill-To to Customer Hierarchy Load – DSM activities are performed at a bill-to customer level. The deduction and claim information exchanged with EnterpriseOne is at the Bill-To level. The integration between J.D. Edwards EnterpriseOne and Demantra Demand Management loads the bill-to information from EnterpriseOne into the Bill To level in Demantra. A Bill To level is preconfigured in the Demantra site hierarchy and DSM contains a preconfigured level called Invoiced Bill To that is mapped to the Bill To level in the site hierarchy.
The integration loads Bill-To Address Number from EnterpriseOne into the code field of the Bill To level and it loads the Bill-To Address Name from EnterpriseOne into the description field of the Bill To level.
Multi-Currency Support – EnterpriseOne supports multi-currency. Therefore, deductions can be created in different currencies in EnterpriseOne. When deductions are sent from EnterpriseOne to DSM both the native, or transaction, currency amount and a base, or common, currency amount are included. DSM is enhanced to store the native currency amount, and to recalculate this amount when a deduction is split. When approved or denied deductions are sent from DSM to EnterpriseOne, the amount in the native currency is included.
There are four files transferred between Oracle Demantra DSM and JD Edwards EnterpriseOne. They are:
New Deductions (EnterpriseOne to Demantra DSM)
Approved and Denied Deductions (Demantra DSM to EnterpriseOne)
Payment Requests (Demantra DSM to EnterpriseOne)
Payment Confirmation (EnterpriseOne to Demantra DSM)
Lastly, there is another interface, Claims Entry, which is a manual method for users to enter claims into Demantra DSM.
Note: You may need to modify the default values of the time filters defined in the integration interface data profiles to suit your implementation needs.
The purpose of this interface is to extract new Deductions from EnterpriseOne and load them into DSM as Settlements of Type “Deduction”. Details:
EnterpriseOne Extract Name: Deductions.txt
ODI Package Name: LoadE1DeductionsDataToDMPkg
Demantra Integration Interface Names:
Settlement Invoice Integration
SETTLEMENT LEVEL import
Demantra Integration Data Profile Name:
Invoice Import Integration
SETTLEMENT Import
Demantra Workflow: AIA-E1ToDSM_New Deduction_Download
Note: This Workflow contains a step to create an Invoice for every Bill-To that exists in the Bill-To level (parent of Site). The Invoice Code is set to the Bill-To Code. When a Settlement is loaded, it is related to the Invoice that corresponds to the Bill-To on the Settlement.
The following table maps the fields in the flat file exported from EnterpriseOne to the corresponding fields in Demantra after being transformed by ODI:
Note: For every row in this table, the Level is: "Settlement" and the Table is "SETTLEMENT".
EnterpriseOne Extract Field Name |
Field Type (Value) |
Settlement Level Attribute |
Column | New |
---|---|---|---|---|
E1 Deduction Id | Number (15) | N/A | SETTLEMENT _CODE | |
Settlement Date of Origin | Date | Date Posted | DATE_POSTED | |
Settlement Amount (Base Currency) |
Number (15,2) | Settlement Amount | SETTLEMENT_AMOUNT | |
Settlement Amount (Transaction Currency) |
Number (15,2) | Settlement Amount Native Curr | SETTLEMENT_AMOUNT_NATIVE_CURR | Y |
Transaction Currency Code | String (3) | Native Curr Code | NATIVE_CURR_CODE | Y |
Display Decimals | Integer (1) | Native Display Decimals | NATIVE_DISPLAY_DECIMALS | Y |
Document – Original | Number (8) | EnterpriseOne Document | E1_DOCUMENT | Y |
Document Type – Original | String (2) | EnterpriseOne Document Type |
E1_DOCUMENT_TYPE | Y |
Document Pay Item – Orig | String (3) | EnterpriseOne Document Pay Item |
E1_DOCUMENT_PAY_ITEM | Y |
Document Company (Original) |
String (5) | EnterpriseOne Document Company |
E1_DOCUMENT_COMPANY | Y |
Invoice Date | Date | EnterpriseOne Invoice Date |
E1_INVOICE_DATE | Y |
Check # | String (25) | Customer Check # | CUSTOMER_CHECK_NUM | |
Check Date | Date | Cust Check Date | CUSTOMER_CHECK_DATE | |
Check Amount (Base Currency) |
Number (15,2) | Cust Check Amount | CUST_CHECK_AMOUN | Y |
Check Amount (Transaction Currency) | Number (15,2) | Cust Check Amount Native Curr | CUST_CHECK_AMOUNT_NATIVE_CURR | Y |
Currency Code | String (3) | Cust Check Native Curr Code | CUST_CHECK_NATIVE_CURR_CODE | Y |
Display Decimals | Integer (1) | Cust Check Display Decimals | CUST_CHECK_DISPLAY_DECIMALS | Y |
G/L or Reason code | Default | GL Code | GL_CODE_ID | |
Status | New | Status | SETTLEMENT_STATUS_ID | |
Settlement Type | Deduction | Settlement Type | SETTLEMENT_TYPE_ID | |
Company Customer Number |
String (5) Number (8) |
NA Account |
NA T_EP_LR2_EP_ID |
Note: Customer Number is the Customer Bill-to Number.
The following table maps the required fields for a Settlement Level import:
Demantra Field Name |
EnterpriseOne Extract Field Name |
Comments |
---|---|---|
SETTLEMENT_CODE | E1 Deduction ID | |
SETTLEMENT_DESC | E1 Deduction ID | |
INVOICE_CODE | Bill-To Customer | Invoice is a required field and is used to identify the Bill-To Customer for the Settlement. Since actual invoices are not loaded from E1 to DSM, an Invoice is automatically created for each Bill-To. The Invoice Code will equal the Bill-To Code, which is populated with the EnterpriseOne Bill-To Number. |
GL_CODE_CODE | G/L or Reason code | A GL Code in the Settlement hierarchy with a value of “Default” must exist |
SETTLEMENT_STATUS_CODE | Status | A Status in the Settlement hierarchy with a value of “New” must exist |
SETTLEMENT_TYPE_CODE | Settlement Type | A Type in the Settlement hierarchy with a value of “Deduction” must exist |
PROMOTION_CODE | Hard-coded value “Default Promotion” | A Linked Promotion in the Settlement hierarchy with this value must exist. |
DATE_POSTED | Settlement Date of Origin |
The purpose of this interface is to extract approved and denied Deductions from DSM and load them into EnterpriseOne. Details:
Demantra Extract Name: DeductionDispositions.txt
ODI Package Name: LoadDMDedDispositionsDataToE1Pkg
Demantra Integration Interface Name: E1 Deduction Export
Demantra Integration Data Profile Name: E1DeductionExport
Demantra Workflow: AIA-DSMtoE1_Deduction_Export
The following table defines the layout of the Approved and Denied Deduction file and maps the fields to the corresponding fields in Demantra:
Note: For every row in this table, the Demantra Level is: "Settlement" and the Demantra Database Table is "SETTLEMENT".
EnterpriseOne Field Name |
EnterpriseOne Field Type |
Demantra Series | Demantra SETTLEMENT Database Table Column |
Comments |
---|---|---|---|---|
E1 Deduction Id | Number (15) | N/A | SETTLEMENT_ CODE | Settlement Level Code |
Settlement Amount (Base Currency) |
Number (15,2) | Closed Settlement Amount | SETTLEMENT_ AMOUNT | Not used by EnterpriseOne.. |
Settlement Amount (Native Currency |
Number (15,2) | Settlement Amount Native Curr | SETTLEMENT_ AMOUNT_NATIVE _CURR | |
Deduction Reason Code | String (2) | GL Code | GL_CODE_ID | Based on codes manually provided during implementation. |
Status (Approved / Denied) |
N/A | Settlement Status | SETTLEMENT_STATUS_ID | Not used by EnterpriseOne. |
The purpose of this interface is to provide a method for users to manually enter claims into Demantra DSM. The "New Settlement" method is used to enter claims into Demantra DSM.
Attribute | Editable | Mandatory | Comments |
---|---|---|---|
Settlement Type | Y | Y | Select value “Claim” from drop-down list. |
Settlement Invoice | Y | Y | Select from a drop-down list of invoices which have been automatically populated with an invoice for each Bill-To Customer. |
E1 Company Code | Y | Y | Enter the E1 Company Code that applies to the claim. The default value is 00000. This value is mandatory for the claim to be successfully imported into EnterpriseOne. |
Settlement Amount | Y | Y | Enter claim amount in base currency |
Settlement Amount (Native Currency) | Y | Y | Enter claim amount in native/transaction currency |
Native Curr | Y | Select claim native/transaction currency from a list of currency codes | |
Native Display Decimals | Y | ||
Date Posted | Y | Y | Enter date |
Claim Invoice Num | Y | ||
Claim Invoice Date | Y | ||
Settlement Status | Y | ||
GL Code | Y | Default value of“DEFAULT GL Code” | |
Settlement Owner | Y |
The purpose of this interface is to extract check requests for approved claims from DSM and load them into EnterpriseOne. Details:
Demantra Extract Name: Claims.txt
ODI Package Name: LoadDMClaimDataToE1Pkg
Demantra Integration Interface Name: E1 Claim Export
Demantra Integration Data Profile Name: E1_ClaimExport
Note: This data profile does an incremental export of settlements with a Type of “Claim” or “Claim resulting FROM an original claim split” and a Status of “Approved”.
Demantra Workflow: AIA-DSMToE1_Claim_Export
The following table defines the layout of the Approved Check Requests file and maps the fields to the corresponding fields in Demantra:
Note: For every row in this table, the Demantra Level is: "Settlement" and the Demantra Database Table is "SETTLEMENT".
EnterpriseOne Field Name |
EnterpriseOne Field Type |
Demantra Series Level Attribute |
Demantra SETTLEMENT Database Table Column |
Comments |
---|---|---|---|---|
Check Request ID | String (22) | N/A | SETTLEMENT_ CODE | Code field for Settlement that the Check Request is for. Link to SETTLEMENT using SETTLEMENT_ID from CHECK_REQUEST |
Customer / Account | String (14) | Settlement Account ID | T_EP_LR2_EP_ID | |
E1 Company Code | String (5) | E1 Company Code | E1_COMPANY_CODE | The Customer Number (T_EP_LR2_EP_ID) and E1 Company Code fields are concatenated together with a space when the workflow AIA-DSMToE1_Claim_Export exports the payment requests into claim.txt. For example, if Customer Number=4663 and E1 Company Code=00200, then the resulting second field in claim.txt is "4663 00200". |
Claim Invoice # | String (25) | Claim Invoice Num | CLAIM_INVOICE_ NUM | |
Claim Invoice Date | Date | Claim Invoice Date | CLAIM_INVOICE_ DATE | |
Settlement Date of Origin | Date | Settlement Date Posted | DATE_POSTED | |
Settlement Amount (Base Currency) |
Number (15,2) | Closed Settlement Amount | SETTLEMENT_ AMOUNT | |
Settlement Amount (Native Currency) | Number (15,2) | Settlement Amount Native Curr | SETTLEMENT_ AMOUNT_NATIVE _CURR | |
Transaction Currency Code | String (3) | Native Curr | NATIVE_CURR_CODE |
The purpose of this interface is to extract processed payments from EnterpriseOne and update the corresponding Check Request in DSM to indicate that the payment has been issued. Details:
EnterpriseOne Extract Name: APConfirm.txt
ODI Package Name: LoadE1APConfirmDataToDMPkg
Demantra Integration Interface Name: E1 Payment Confirmation Import
Demantra Integration Data Profile Name: E1_Payment_Import
Demantra Workflow: AIA-E1ToPTP_APConfirm_Import
The following table maps the fields in the flat file exported from EnterpriseOne to the corresponding fields in Demantra:
Note: For every row in this table, the Demantra level is: "Settlement" and the Demantra database table is "SETTLEMENT".
EnterpriseOne Field Name |
EnterpriseOne Field Type |
Demantra Settlement Level Attribute |
Demantra SETTLEMENT Database Table Column |
---|---|---|---|
Check Request ID | String (30) | N/A | SETTLEMENT_ CODE |
Check # | Number (8) | Supplier Check # | SUPPLIER_CHECK_NUM |
Settlement Type | String (2) | Check Type | CHECK_TYPE |
Check Amount | Number (15,2) | Check Amount | CHECK_AMOUNT |
Currency | String(3) | Check Curr Code | CHECK_CURRENCY |
Check Date | Date | Supplier Check Date | SUPPLIER_CHECK_DATE |
Multiple Payments | String(10) | Multiple Payment Indicator | MULTIPLE_PAYMENT_INDICATOR |