Configuring Deductions and Settlement Management

This chapter describes how to configure DSM and load an initial set of data.

This chapter covers the following topics:

Overview of DSM

Oracle Demantra Deduction and Settlement Management (DSM) is a Web-based, configurable tool to help users at a manufacturing company resolve settlements with customers (usually retailers) who have run promotions, sold products, and now need reconciliation. Users view the promotional events that DSM provides as possible matches, and then select one and finalize the match. Users can then attach proof of performance for the promotion, approve the match, and request a check to be sent to the customer (if appropriate). Users can also mark a settlement as a duplicate, split a settlement (typically to match only part of it), or deny a settlement.

Often, a third party (a broker) has negotiated the terms. Demantra system may be set up to enable users to collaborate with outside brokers, for example, to acquire extra information if needed.

Types of Settlements

DSM organizes settlements into two groups: trade and non-trade. For trade settlements, DSM recognizes three general types of settlements:

User Actions and Settlement Stages

Within DSM, a settlement can go through the following stages:

the picture is described in the document text

In general, claims come into DSM through the accounts payable (A/P) department. Deductions and off-invoice settlements come into DSM through in the accounts receivable (A/R) department. In all cases, the settlement is loaded into DSM automatically.

Then, in most cases, the processing is as follows:

  1. When a user receives a new settlement, he or she takes ownership of it. At this point, the settlement is In Progress.

  2. Then the user reviews the possible promotions to which the settlement may apply. Oracle Demantra Deductions and Settlement Management displays suitable possible matches.

  3. If the user finds a matching promotion, he or she matches the settlement and validate the proof of performance. Usually, the user scans in a document that shows that the promotion was actually run as required, and then uploads that as an attachment to the settlement.

  4. The user then approves the settlement. In some organizations, the customer service department (CSD) representative has authority to approve a settlement. In others, a manager does that job.

  5. If the settlement is a claim, the user issues a check request to your company's A/P department, to send a check to the customer or to the broker as needed.

Note: Within user security, the user must have New Check Request and Edit Settlement methods enabled for the user to link to or enter a check request.

In other cases, the user may find that the settlement is a duplicate or you may find another reason to deny it, per the company's policies and practices. When the user denies a settlement, the A/P system may enter a chargeback to the customer.

Also, a user can also split the settlement and match only part of it, as appropriate.

Customers and Check Recipients

If a user issues a check request, it is necessary to determine the recipient of that check. The matched event is associated with a particular customer, but the check itself might need to go to a different recipient, depending on the customer's business relationships.

For example, suppose the matched event was associated with Acme Foods. Acme Foods may have created the event with the help of a broker, and it might be appropriate to make the check payable to Acme but send it to the broker, who hand-delivers it as a customer-service gesture.

For another example, the manufacturer might receive an invoice from an indirect customer, who funded and planned the event with Acme (who then ran the event). In this case, it would be suitable to make the check payable to and send it to the indirect customer.

As a result of all this complexity, when an event is matched to a given location A, it is necessary to consider all the other locations that are related to location A in various ways. The user will view these locations and direct the check appropriately.

Data Flow in DSM

DSM works with external systems such as accounts payable and accounts receivable. Ultimately, those systems own most the data; DSM is responsible only for matching settlements to promotions and performing the associated record keeping.

At a high level, the overall flow of data is as follows:

  1. Within DSM, an automated workflow imports settlement data from external corporate systems.

  2. The same automated workflow then runs database procedures that iterate through the settlements and identify possible matches for each. The procedures write the results to an internal table for later use. Optionally, another database procedure iterates through the settlements and writes off any that fall below a specified threshold.

  3. A typical user, who is responsible for a subset of the locations (possibly an account), opens a DSM worksheet and displays settlements associated with those locations.

  4. For each unresolved settlement, the worksheet lists possible promotions that could match that settlement. Here, DSM uses the internal table that contains all possible matches.

  5. For each settlement, the user does one or more of the following:

    • Linking the settlement to a promotion. If the settlement is a claim, the user can next specify details for a check request.

      The same user or another user with higher permission then approves the settlement.

    • Denying a settlement, because it is a duplicate or for another reason.

    • Splitting the settlement, creating a new settlement in the process. A user usually splits a settlement in order to link part of it to a promotion.

  6. Within DSM, an automated workflow exports details to the external systems. The details vary by implementation but may include the following, for each settlement:

    • Settlement status, as set by DSM and its users

    • Changes to the G/L (general ledger) code, as set by DSM users

    • Current amount of the settlement, if only part of the settlement was valid

    The workflow also exports check requests (as specified within DSM) and chargebacks (if only part of the settlement was valid).

Overview of the Configuration Process

These steps assume that you have already set up the basic Demantra implementation. This means that your implementation already contains the item levels, location levels, and promotion levels that are meaningful in the designated environment.

To configure DSM, the general steps are as follows:

  1. Identify item and location levels associated with settlements. Then run a database script to set up DSM database structures, using that information. See “Setting Up Database Structures”.

    This script also creates the canned DSM worksheets, all series used in those worksheets, methods (right-click actions) for use in DSM worksheets, all associated workflows, and an integration workflow suitable for use with DSM.

  2. Customize your existing promotion level to include a budget, and configure series to store that data. See “Configuring Promotions and Promotion Series”.

  3. Indicate how to use your promotion series. See “Identifying Key Promotion Series”.

  4. Specifying tolerance windows to control how closely to match settlements and promotions. See “Configuring Settlement Matching”.

  5. Configure the automatic write-off mechanism, if needed. See “Configuring Write-Offs”.

  6. Load the G/L codes, an initial set of invoices, and an initial set of settlements. See “Loading Initial Data and Creating Possible Matches”.

  7. Populate two tables that list the types of relationships between retailers and other entities, so that DSM can look up all locations related to the matched location.

  8. Optionally customize the DSM worksheets. At a minimum, you may want to create a Promotion Effectiveness worksheet and use that within DSM (as a sub tab worksheet).

  9. Customize the integration so that new settlements are loaded periodically. Each time settlements are loaded, be sure to run the DSM procedures in order to create proposed matches.

Setting Up Database Structures

DSM associates settlements with an item and a location level and is shipped pre-configured to an existing item and location level. If you want to change this information, run the procedure API_CONFIG_SETTLEMENT.

Internally, this association is represented in complex tables. The association can be different in different implementations, and performing the steps described below sets up the needed database structures.

This procedure:

Note: The parameters SettlementLocationExtension and SettlementProductExtension are not visible in Business Modeler.

To set up the DSM database structures

  1. Using PLSQL, query the table SYS_PARAMS and determine the values of SettlementLocationExtension and SettlementProductExtension. Change these values as necessary.

    Parameter Description
    SettlementLocationExtension Specifies the internal identifier of the location-type level with which settlements should be associated. This generally represents the entity that is being billed or refunded.
    SettlementProductExtension Specifies the internal identifier of the item-type level with which settlements should be associated. This generally represents a promoted product or a product group.

  2. If you want to change the values of Item and Location levels that are associated with the DSM settlement hierarchy, perform the steps below. Otherwise, go to step 3.

    1. Run the procedure API_CONFIG_SETTLEMENT.

    2. Import DSM data.

    3. Verify the following parameters (in the SYS_PARAMS table) are set correctly for your installation:

      • DSMPromotionBudgetSeries

      • DSMPEShipDateSeries

      • DSMALLShipDateDifference

      • DSMOIPercentDifference

      • DSMOIShipDateDifference

      • DSMOICheckProduct

      • DSMPEOIAmountSeries

      • DSMWriteOffThreshold

  3. Run the following procedures:

    • POP_ALL_MATCH_PROPOSAL.SQL

    • POP_OI_MATCH_PROPOSAL.SQL

    These procedures use the values of the parameters listed in step 2 as input. These procedures are described in the “Database Procedures” chapter.

    Tip: You can run API_CONFIG_SETTLEMENT by creating a workflow consisting of a Stored Procedure step. When defining the stored procedure’s parameters, enter the Location level ID as the first value and the Item level ID as the second. For example:

    the picture is described in the document text

Configuring Promotions and Promotion Series

In order to extend your existing promotion levels to work with DSM, do the following:

  1. Create series as follows, if you do not yet have series similar to them:

    • A promotion budget series as follows:

      Editability: Non-editable
      Data Table: Promotion level
      Data Type: Numeric
      Update Field: Promotion_budget (for example)
      Server Expression: max(promotion.promotion_budget) (for example)
      Proportionality: Proportional

      Tip: You might want to create two budget series: one that is editable and the other, non-editable. They both would use the same update field.

    • A series to store the monetary off-invoice amounts for the promotions, for cases when that is applicable.

      Data Table: Promotion level
      Data Type: Numeric
      Update Field: offinvoice (for example)
      Server Expression: max(promotion.offinvoice) (for example)
      Proportionality: Proportional

    • A series to store the ending date of each promotion, typically the date on which shipments end. This is the date that you will compare to the settlement dates, for the purpose of matching settlements to promotions.

      Editability: Non-editable
      Data Table: Promotion level
      Data Type: Date
      Update Field: endship (for example)
      Server Expression: max(promotion.until_date) (for example)
      Extra From: promotion_dates, promotion (for example)
      Extra Where promotion.promotion_id=promotion_dates.promotion_id
      Proportionality: Non-proportional

      Note: This series must have extra from and extra where expressions, or else the POP_ALL_MATCH_PROPOSAL will fail.

    • An optional series to store the starting date of each promotion, typically the date on which shipments for the promotion start.

      Editability: Non-editable
      Data Table: Promotion level
      Data Type: Date
      Update Field: startship (for example)
      Server Expression: max(promotion.from_date) (for example)
      Extra From: promotion_dates, promotion (for example)
      Extra Where promotion.promotion_id=promotion_dates.promotion_id
      Proportionality: Non-proportional

  2. Make a note of the IDs of these series, as shown in the Business Modeler.

  3. Add a budget attribute to your existing promotion level. For Column Name, specify the column that stores the budget series that you just defined.

See also

Identifying Key Promotion Series

To connect DSM to your promotions and promotion series, you set the following parameters:

Configuring Settlement Matching

To configure the matching process for DSM, you must specify values for the following additional parameters:

The matching process is different for off-invoice settlements than it is for claims and deductions. The following sections provide the details.

Claims and Deductions

To find promotions to match to a claim or deduction, DSM performs two comparisons:

Off-Invoice Settlements

An off-invoice settlement must be handled slightly differently. This kind of settlement can occur only if there was an agreement to bill the customer below the invoice rate. Typically, the settlement amount was decided at that time and less variation is anticipated than with other kinds of settlements. You specify a tolerance window to use when comparing the settlement amount to promotion amounts. For flexibility, you can specify a different tolerance for date comparison as well.

It is expected that each off-invoice settlement will be matched to only one promotion or possibly to a small number of promotions, in contrast to other kinds of settlements.

To find promotions to match to an off-invoice settlement, DSM performs the following comparisons:

These parameters are used by the POP_OI_MATCH_PROPOSAL procedure, which you should execute every time you load settlement data.

Configuring Write-Offs

If a settlement is below a certain size, DSM can automatically write it off without user intervention. This process changes the settlement status to Write Off. If your implementation requires write-offs, do the following:

  1. Create a workflow to automatically run the Java class com.demantra.workflow.step.CustomWriteOffStep each time you load settlement data.

  2. Set a value for the DSMWriteOffThreshold parameter, which specifies the monetary amount below which Demantra automatically writes off a settlement.

Loading Initial Data and Creating Possible Matches

The recommended way to load an initial set of data is to use the integration interfaces that are provided with DSM. The UPGRADE_TO_DSM procedure creates these integration interfaces. After loading the data, you should create the possible matches for use within the DSM worksheets.

This section describes how to quickly get started with an initial set of data; it does not discuss integration or automation in any detail.

The overall procedure is as follows:

  1. In the Business Modeler, open each of the import integration interfaces, click Next until you reach the last screen (the preview screen), and click Create in order to create the staging table for that interface.

    In this step, you create the following staging tables:

    • BIIO_GL_Code

    • BIIO_Settlement_Import

    • BIIO_Invoice

  2. Load data into these staging tables:

    • For G/L codes, see “G/L Code Import”.

    • For settlements, see “Settlement Import”.

    • For invoices, see “Invoice Import”.

  3. Be sure to commit the changes.

  4. Create and run a workflow or a set of workflows that execute these integration interfaces.

    Note: Because settlements refer to G/L codes and invoices, you should load G/L codes and invoices first. It is also good practice to check the results of that step before executing the interface to load settlements. You can use the Business Modeler to verify that the G/L, settlement, and invoice levels contain the members that you imported; see “Viewing the Members of a Level”.

  5. Execute the POP_ALL_MATCH_PROPOSAL and POP_OI_MATCH_PROPOSAL procedures, either from within a workflow or from a SQL command line. Verify that the new tentative matches are written into the proposed_match table.

  6. Optionally create and run a workflow to run the Java class com.demantra.workflow.step.CustomWriteOffStep.

See also

“Creating or Editing Workflow Schemas”

Describing Customer Relationships

DSM uses two tables to describe relationships such as those between a customer and a broker, so that the user can have an appropriate choice of ways to direct the check. You should populate these tables according to the requirements of the implementation.

To describe the customer relationships

  1. Populate the Customer_Type table to list all the customer types, such as the following example set:

    • Direct customer

    • Indirect customer

    • Broker

    • Other

  2. Second, the Customer_Type_Relation table describes all the relationships between the customer locations (specifically, at the location level you have associated with settlements). Each row consists of three fields:

    • CUSTOMER_LHS is the ID of a customer (for example, A).

    • RELATION_TYPE is the ID of a type of customer, which we use here as a type of relationship (for example, Broker).

    • CUSTOMER_RHS is the ID of a related customer (for example, B).

    This row means that A is a Broker for B.

In Case of Problems

See the Oracle Demantra Release Notes for notes on adjustments or corrections to make, in case the UPGRADE_TO_DSM procedure has any defects.

Reference: DSM Levels

the picture is described in the document text

You should not modify these levels without consulting Oracle.

Note: For technical reasons, the following restrictions apply:

The following sections provide details on these levels:

Check Request

This level contains the details for check requests, which are exported to the accounting systems that actually perform them. A check request is an instruction to send a check to a customer or designated third party.

This is a general level with the following attributes:

Attribute Column Name Data Type Purpose
Address line 1 CUST_ADDR_LN1 Character Address of this customer, for use in this check request.
Address line 2 CUST_ADDR_LN2 Character
Address line 3 CUST_ADDR_LN3 Character
Address line 4 CUST_ADDR_LN4 Character
Amount CHECK_REQUEST_AMOUNT Number Monetary amount of the check request.
BK Address line 1 BK_ADDR_LN1 Character Address of the broker, if applicable.
BK Address line 2 BK_ADDR_LN2 Character
BK Address line 3 BK_ADDR_LN3 Character
BK Address line 4 BK_ADDR_LN4 Character
BK Attn BK_ATTN Character Addressee of the broker.
BK City BK_CITY Character City of the broker, for use in the address.
BK Company BK_COMPANY Character Name of the broker's company, for use in the address.
BK Country BK_COUNTRY Character Country of the broker, for use in the address.
BK State BK_STATE Character State of the broker, for use in the address.
BK Zip BK_ZIP Character Postal code of the broker, for use in the address.
Check Request # CHECK_REQUEST_NUM Number Number of the check request.
Check Requested FOR CHECK_REQUEST_REASON_ID Number Reason code associated with this check request.
Customer City CUST_CITY Character City of the customer, for use in the address.
Customer Country CUST_COUNTRY Character Country of the customer, for use in the address.
Customer Reference CR_CUSTOMER_REFERENCE Character  
Customer State CUST_STATE Character State of the customer, for use in the address.
Customer Type CUSTOMER_TYPE Number  
Customer Zip CUST_ZIP Character Postal code of the customer, for use in the address.
Date Issued CHECK_REQUEST_DATE_ISSUED Date Date on which the check request was issued.
Date Requested CHECK_REQUEST_DATE Date Date of the check request.
Invoice INVOICE_ID Number ID of the invoice with which this check request is associated.
Mail To Broker MAIL_TO_BK Number  
Name CHECK_REQUEST_DESC Character Description of the check request.
Note CHECK_REQUEST_NOTE Character Note entered when check request was made.
Payee PAYEE Character Person or entity to whom the check should be written.
Promo Description CR_PROMOTION_ID Character ID of the promotion with which this check request is associated.
Settlement ID SETTLEMENT_ID Number ID of the associated settlement.

For information on importing or exporting this level, see “Check Request Import and Export”.

Settlement

The Settlement level aggregates settlement data. In general, a settlement is an outstanding sum of money that needs to be resolved, related to a promotion.

This is a general level with the following attributes.

Attribute Column Name Data Type Purpose
Account t_ep_lr2_EP_ID Number Description of the customer with which this settlement is associated. This must be the description field as listed in the level table, for the location level that is associated with settlements.
Amount To Link LINKED_AMOUNT Number Monetary amount that has been matched to promotions.
Cust Check Date CUSTOMER_CHECK_DATE Date Applies only to deductions and off-invoice settlements. This is the date of the check from the customer.
Customer Check # CUSTOMER_CHECK_NUM Number Applies only to deductions and off-invoice settlements. This is the number of the check from the customer for this settlement. Uses the customer's check numbering system.
Date Posted DATE_POSTED Date Date when the settlement was posted.
Event Product promoted_product Number Code of the associated item, as listed in the Demantra tables.
GL Code GL_CODE_ID Number Code of the associated G/L code, as listed in the Demantra tables.
Link Date LINK_DATE Date Date on which this settlement was linked to a promotion.
Linked Promotion PROMOTION_ID Number ID of the associated promotion, as listed in the Demantra tables.
Method Status METHOD_STATUS Number For internal use only.
Name SETTLEMENT_DESC Character Description of the settlement.
Open Amount OPEN_AMOUNT Number Remaining amount of the settlement that has not yet been matched to any promotions.
Related WS RELATED_WS Number Demantra ID of the worksheet that is associated, by default, with settlements.
Settlement # SETTLEMENT_NUMBER Number Number for the settlement, as given in the enterprise systems.
Settlement Action SETTLEMENT_ACTION_ID Number  
Settlement Amount SETTLEMENT_AMOUNT Number Total monetary amount of the settlement.
Settlement Invoice INVOICE_ID Number ID of the associated invoice, as listed in the Demantra tables.
Settlement Owner SETTLEMENT_OWNER Number DSM user who has claimed responsibility for this settlement.
Settlement Type SETTLEMENT_TYPE_ID Number Type of the settlement. This should be one of the IDs of the Type level; see “Type”.
Split Settlement ID SPLIT_SETTLEMENT_ID Number ID of the settlement that was split off from this settlement, if any.
Status SETTLEMENT_STATUS_ID Number Status of the settlement. This should be one of the IDs of the Status level; see “Status”.
Supplier Check # SUPPLIER_CHECK_NUM Number Applies only to claims. Date of the check to the customer.
Supplier Check Date SUPPLIER_CHECK_DATE Date Applies only to claims. This is the number of the check that reimburses the customer for this claim.

For information on loading settlements into DSM, see “Settlement Import”.

GL Code

This level contains the G/L codes that you loaded from the other corporate systems.

This is a general level with no attributes.

For information on loading G/L codes, see “G/L Code Import”.

Invoice

This level contains the invoices that you loaded from the other corporate systems.

This is a general level with the following attributes.

Attribute Column Name Data Type Purpose
Invoice # INVOICE_NUM Number Invoice number, as used in the corporate systems.
Invoice Date INVOICE_DATE Date Date of the invoice.
Invoiced Bill To t_ep_lr2_EP_ID Number ID of the customer to whom this invoice was issued.
Name INVOICE_DESC Character Description. Can be identical to the invoice codes.

For information on loading invoices, see “Invoice Import”.

Invoiced Billto

This level is an alias to the actual location level that you associated with settlements. Demantra creates, maintains, and uses this alias for technical reasons, and you should not make changes to it.

Linked Promotion

This level is an alias to the lowest promotion level. Demantra creates, maintains, and uses this alias for technical reasons, and you should not change it.

Status

This level contains the predefined settlement statuses:

These statuses can not be customized. Do not change this level in any way.

Type

This level contains the predefined settlement types:

These types can not be customized. Do not change this level in any way.

SettlementAlias

This level is an alias to the settlement level. Demantra creates, maintains, and uses this alias for technical reasons, and you should not change it.

Reference: DSM Series

For information on the predefined series, see the Oracle Demantra Deduction and Settlement Management User's Guide.

Reference: DSM Integration Interfaces

The UPGRADE_TO_DSM procedure automatically creates integration interfaces to help you import or export the following:

This section provides details on these integration interfaces.

G/L Code Import

Interface Name: Reason Code Integration INTERFACE
Type: Import
Description: Imports rows from a staging table and adds the new members to the GL Code level.
Staging Table: BIIO_GL_Code

BIIO_GL_Code

This staging table has the following structure:

Field Data Type Purpose
GL_CODE_CODE varchar2(120) Cannot be null. Short version of the general ledger codes, as used in the enterprise system. Example: SPOILED
GL_CODE_DESC varchar2(2000) Cannot be null. Longer description of the codes. In some cases, these are identical to the codes.

Invoice Import

Interface Name: Invoice Integration INTERFACE
Type: Import
Description: Imports rows from a staging table and adds the new members to the Invoice level.
Staging Table: BIIO_Invoice

BIIO_Invoice

This staging table has the following structure:

Field Data Type Purpose
INVOICE_CODE varchar2(120) Cannot be null. Unique code for the invoice, for use in Demantra.
INVOICE_DESC varchar2(2000) Cannot be null. Description. Can be identical to the invoice codes.
LR2 varchar2(120) Cannot be null. Code of the customer with which this invoice is associated. This must be the code field as listed in the level table, for the location level that is associated with settlements.
INVOICE_NUM number(20,10) Invoice number, as used in the corporate systems.
INVOICE_DATE date Date of the invoice.
T_EP_LR2_EP_ID varchar2(2000) Description of the customer with which this invoice is associated. This must be the description field as listed in the level table, for the location level that is associated with settlements.

Settlement Import

Interface Name: SETTLEMENT LEVEL import
Type: Import
Description: Imports rows from a staging table and adds the new members to the Settlement level.
Staging Table: BIIO_Settlement_Import

BIIO_Settlement_Import

This staging table has the following structure:

Field Data Type Purpose
SETTLEMENT_CODE varchar2(500) Cannot be null. Unique code for the settlement, for use in Demantra.
SETTLEMENT_DESC varchar2(2000) Cannot be null. Description of the settlement.
INVOICE_CODE varchar2(120) Cannot be null. Code of the associated invoice, as listed in the Demantra tables.
GL_CODE_CODE varchar2(120) Cannot be null. Code of the associated G/L code, as listed in the Demantra tables.
SETTLEMENT_STATUS_CODE varchar2(120) Cannot be null. Status of the settlement. This should be one of the codes of the Status level; see “Status”.
SETTLEMENT_TYPE_CODE varchar2(120) Cannot be null. Type of the settlement. This should be one of the codes of the Type level; see “Type”.
PROMOTION_CODE varchar2(120) Cannot be null. Code of the associated promotion, as listed in the Demantra tables.
SETTLEMENT_OWNER varchar2(50) DSM user who has claimed responsibility for this settlement.
DATE_POSTED date Should not be null. Date when the settlement was posted.
CUSTOMER_CHECK_NUM number(20,10) Applies only to deductions and off-invoice settlements. This is the number of the check from the customer for this settlement. Uses the customer's check numbering system.
CUSTOMER_CHECK_DATE date Applies only to deductions and off-invoice settlements. This is the date of the check from the customer.
SETTLEMENT_AMOUNT number(20,10) Total monetary amount of the settlement.
SUPPLIER_CHECK_NUM number(20,10) Applies only to claims. This is the number of the check that reimburses the customer for this claim.
SUPPLIER_CHECK_DATE date Applies only to claims. Date of the check to the customer.
SETTLEMENT_TYPE_ID varchar2(2000) Type of the settlement. This should be one of the IDs of the Type level; see “Type”.
SETTLEMENT_ACTION_ID varchar2(255)  
LINKED_AMOUNT number(20,10) Monetary amount that has been matched to promotions.
OPEN_AMOUNT number(20,10) Remaining amount of the settlement that has not yet been matched to any promotions.
INVOICE_ID varchar2(2000) ID of the associated invoice, as listed in the Demantra tables.
T_EP_LR2_EP_ID varchar2(2000) Description of the customer with which this settlement is associated. This must be the description field as listed in the level table, for the location level that is associated with settlements.
GL_CODE_ID varchar2(2000) ID of the associated G/L code, as listed in the Demantra tables.
SETTLEMENT_STATUS_ID varchar2(2000) Status of the settlement. This should be one of the IDs of the Status level; see “Status”.
PROMOTION_ID varchar2(2000) ID of the associated promotion, as listed in the Demantra tables.
PROMOTED_PRODUCT varchar2(2000) Description field of the associated item, as listed in the level table for the appropriate level.
LINK_DATE date Date on which this settlement was linked to a promotion.
METHOD_STATUS varchar2(200) For internal use only.
SPLIT_SETTLEMENT_ID number(20,10) ID of the settlement that was split off from this settlement, if any.
RELATED_WS number(20,10) Demantra ID of the worksheet that is associated, by default, with settlements.
SETTLEMENT_number number(20,10) Number for the settlement, as given in the enterprise systems.

Settlement Export

Interface Name: SETTLEMENT LEVEL export
Type: Export
Description: Exports members of the Settlement level. Performs full export (not incremental) to \TEMP\IntegrationDIR\Export_Settlement.TXT.
Staging Table: N/A

Check Request Import and Export

Interface Name: CHECK Request Integration INTERFACE
Type: Import & Export
Description: When used for import: imports rows from a staging table and adds the new members to the Check Request level.
When used for export: performs full export (not incremental) to \TEMP\IntegrationDIR\Export_CheckRequest.TXT
Staging Table: BIIO_CheckRequest

BIIO_CheckRequest

This staging table has the following structure:

Field Data Type Purpose
CHECK_REQUEST_CODE varchar2(120) Cannot be null. Unique code for the check request, for use in Demantra.
CHECK_REQUEST_DESC varchar2(2000) Cannot be null. Description of the check request.
INVOICE_ID varchar2(2000) ID of the invoice with which this check request is associated.
CHECK_REQUEST_NUM number(20,10) Number of the check request.
CHECK_REQUEST_AMOUNT number(20,10) Monetary amount of the check request.
CHECK_REQUEST_DATE date Date of the check request.
CHECK_REQUEST_DATE_ISSUED date Date on which the check request was issued.
CHECK_REQUEST_REASON_ID varchar2(255) Reason code associated with this check request.
CHECK_REQUEST_NOTE varchar2(200) Note entered when the check request was created.
CR_CUSTOMER_REFERENCE varchar2(200)  
CR_PROMOTION_ID varchar2(2000) ID of the promotion with which this check request is associated.
CUSTOMER_TYPE number(20,10) ID of a customer type, from the Customer_Type table. See “Reference: Other DSM Tables”.
PAYEE varchar2(200) Person or entity to whom the check should be written.
CUST_ADDR_LN1 varchar2(200) Address of this customer, for use in this check request.
CUST_ADDR_LN2 varchar2(200)
CUST_ADDR_LN3 varchar2(200)
CUST_ADDR_LN4 varchar2(200)
CUST_CITY varchar2(200)
CUST_STATE varchar2(200)
CUST_ZIP varchar2(200)
CUST_COUNTRY varchar2(200)
MAIL_TO_BK number(20,10)  
BK_COMPANY varchar2(200) Name of the associated broker, if any, for use in this check request.
BK_ATTN varchar2(200) Addressee of the broker.
BK_ADDR_LN1 varchar2(200) Address of the broker.
BK_ADDR_LN2 varchar2(200)
BK_ADDR_LN3 varchar2(200)
BK_ADDR_LN4 varchar2(200)
BK_CITY varchar2(200)
BK_STATE varchar2(200)
BK_ZIP varchar2(200)
BK_COUNTRY varchar2(200)
SETTLEMENT_ID number(20,10) ID of the associated settlement.

Reference: Other DSM Tables

DSM uses the following additional tables; see “Describing Customer Relationships”.

Customer_Type

This table has the following structure:

Field Data Type Purpose
CUSTOMER_TYPE_ID number(10) Cannot be null. Unique ID for the customer type, for use in Demantra.
CUSTOMER_TYPE_CODE varchar2(240) Cannot be null. Unique code for the customer type, for use in Demantra.
CUSTOMER_TYPE_DESC varchar2(1000) Description of the customer type.
FICTIVE_CHILD number(10) Ignore this field.
IS_FICTIVE number(1) Ignore this field.
LAST_UPDATE_DATE date Ignore this field.

Customer_Type_Relation

This table has the following structure:

Field Data Type Purpose
CUSTOMER_LHS number(10) Cannot be null. ID of a customer, specifically a member of the location level with which settlements are associated, in this implementation.
RELATION_TYPE number(10) Cannot be null. ID of a customer type, from the Customer_Type table.
CUSTOMER_RHS number(10) Cannot be null. ID of a customer, specifically a member of the location level with which settlements are associated, in this implementation.

If CUSTOMER_LHS refers to Customer A, RELATION_TYPE refers to the Broker type, and CUSTOMER_RHS refers to Customer B, then this row means that Customer A is a Broker for Customer B.