Demantra Deductions and Settlement Management to EnterpriseOne Integration

This chapter describes how Demantra Deductions and Settlement Management (DSM) integrates with EnterpriseOne.

This chapter covers the following topics:

Overview

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:

Architectural Process

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 picture is described in the document text

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.

Integration Points

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 picture is described in the document text

Business Process

The following diagram provides more details about the business process and data that flows between EnterpriseOne and Oracle Demantra DSM:

the picture is described in the document text

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:

  1. 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”.

  2. Review deductions in DSM, match them to promotions, and approve or deny the deductions.

  3. 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.

  4. Enter claims in DSM, match them to promotions and approve the claims.

  5. 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.

  6. Review the payment request in EnterpriseOne and issue payments against the payment requests from DSM and extract the payment requests from EnterpriseOne.

  7. 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.

Modelling Considerations

Setup

The following must be manually setup in preparation for the integration:

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

Series

The following Oracle Demantra series support the integration:

Attributes

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

Customer Load Enhancements

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 Enhancements

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.

Mapping

There are four files transferred between Oracle Demantra DSM and JD Edwards EnterpriseOne. They are:

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.

New Deductions

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:

Demantra Integration Data Profile Name:

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  

Approved and Denied Deductions

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.

Claims Entry

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    

Payment Requests

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  

Payment Confirmation

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