This chapter describes how to configure DSM and load an initial set of data.
This chapter covers the following topics:
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:
A claim is a request from a customer for payment. In these cases, the customer has run the promotion and is requesting to be reimbursed, based on an agreement with the customer. If a user approves the claim, DSM sends a request to the A/P department to send a check to this customer or to the broker, as applicable.
A deduction is a short payment on an invoice. In these cases, the customer has run the promotion and has made a short payment on an invoice. By permitting this short payment, the user is reimbursing the customer for running the promotion.
An off-invoice settlement represents the case where the customer was billed a lower amount (that is, “off invoice”) for the products, as compensation for running the promotion. In this kind of settlement, the settlement amount was negotiated ahead of time.
User Actions and Settlement Stages
Within DSM, a settlement can go through the following stages:
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:
When a user receives a new settlement, he or she takes ownership of it. At this point, the settlement is In Progress.
Then the user reviews the possible promotions to which the settlement may apply. Oracle Demantra Deductions and Settlement Management displays suitable possible matches.
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.
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.
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.
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:
Within DSM, an automated workflow imports settlement data from external corporate systems.
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.
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.
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.
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.
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).
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:
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.
Customize your existing promotion level to include a budget, and configure series to store that data. See “Configuring Promotions and Promotion Series”.
Indicate how to use your promotion series. See “Identifying Key Promotion Series”.
Specifying tolerance windows to control how closely to match settlements and promotions. See “Configuring Settlement Matching”.
Configure the automatic write-off mechanism, if needed. See “Configuring Write-Offs”.
Load the G/L codes, an initial set of invoices, and an initial set of settlements. See “Loading Initial Data and Creating Possible Matches”.
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.
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).
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.
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:
Updates the item and location levels with which settlements should be associated (by updating the parameters SettlementLocationExtension and SettlementProductExtension)
Upgrades the Settlement Location alias level to point to the new location level
Ensures that all standard procedures reflect the new settlement levels
Note: The parameters SettlementLocationExtension and SettlementProductExtension are not visible in Business Modeler.
To set up the DSM database structures
Using PLSQL, query the table SYS_PARAMS and determine the values of SettlementLocationExtension and SettlementProductExtension. Change these values as necessary.
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.
Run the procedure API_CONFIG_SETTLEMENT.
Import DSM data.
Verify the following parameters (in the SYS_PARAMS table) are set correctly for your installation:
DSMPromotionBudgetSeries
DSMPEShipDateSeries
DSMALLShipDateDifference
DSMOIPercentDifference
DSMOIShipDateDifference
DSMOICheckProduct
DSMPEOIAmountSeries
DSMWriteOffThreshold
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:
In order to extend your existing promotion levels to work with DSM, do the following:
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 |
Make a note of the IDs of these series, as shown in the Business Modeler.
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
“Creating a Series”
“Adding Attributes to a Level”
To connect DSM to your promotions and promotion series, you set the following parameters:
DSMPromotionBudgetSeries should be the ID of the promotion budget series. When a settlement is matched to a promotion, DSM adjusts this budget as appropriate.
DSMPEOIAmountSeries should be the ID of the series that stores the monetary off-invoice amounts for the promotions.
DSMPEShipDateSeries is the ID of the series that stores the appropriate promotion date, to be compared with settlement dates.
To configure the matching process for DSM, you must specify values for the following additional parameters:
DSMAllShipDateDifference
DSMOIPercentDifference
DSMOIShipDateDifference
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:
DSM compares the promotion date to the settlement date. Only promotions with close enough dates are considered possible matches.
The DSMAllShipDateDifference parameter specifies the window of time that Demantra uses to search for a promotion that matches a given settlement. Express this as the number of time buckets between the promotion end date (DSMPEShipDateSeries) and the settlement date.
DSM compares the promotion budget (DSMPromotionBudgetSeries) to the monetary settlement amount. A promotion is a possible match only if its remaining budget is at least as large as the settlement amount.
These parameters are used by the POP_ALL_MATCH_PROPOSAL procedure, which you should execute every time you load settlement data.
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:
DSM compares the promotion budget to the off-invoice amount. For this comparison, the DSMOIPercentDifference parameter specifies the maximum percent difference (of monetary amount) permitted when matching an off-invoice settlement to possible promotions. The promotion budget is controlled by DSMPromotionBudgetSeries.
DSM compares the promotion date to the off-invoice date. Only promotions with close enough dates are considered possible matches. You use the DSMOIShipDateDifference parameter to specify the closeness of these dates. The promotion date is controlled by DSMPEShipDateSeries.
These parameters are used by the POP_OI_MATCH_PROPOSAL procedure, which you should execute every time you load settlement data.
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:
Create a workflow to automatically run the Java class com.demantra.workflow.step.CustomWriteOffStep each time you load settlement data.
Set a value for the DSMWriteOffThreshold parameter, which specifies the monetary amount below which Demantra automatically writes off a settlement.
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:
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
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”.
Be sure to commit the changes.
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”.
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.
Optionally create and run a workflow to run the Java class com.demantra.workflow.step.CustomWriteOffStep.
See also
“Creating or Editing Workflow Schemas”
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
Populate the Customer_Type table to list all the customer types, such as the following example set:
Direct customer
Indirect customer
Broker
Other
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.
See the Oracle Demantra Release Notes for notes on adjustments or corrections to make, in case the UPGRADE_TO_DSM procedure has any defects.
You should not modify these levels without consulting Oracle.
Note: For technical reasons, the following restrictions apply:
If you use a settlement level in a worksheet, you cannot use levels from any other hierarchy in that worksheet, either to aggregate or to filter. This means that the only location level you can use is the aliased one that is inside the settlement hierarchy.
If you use a settlement level in a worksheet, all series in the worksheet must refer to tables used by the settlement hierarchy.
The following sections provide details on these levels:
Check Request
Settlement
GL Code
Invoice
Invoiced Billto
Linked Promotion
Status
Type
SettlementAlias
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”.
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”.
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”.
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”.
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.
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.
This level contains the predefined settlement statuses:
New
In Progress
Unapproved
Approved
Duplicate
Denied
Write Off
These statuses can not be customized. Do not change this level in any way.
This level contains the predefined settlement types:
Claim
Off-invoice
Deduction
Non-Trade
Claim resulting from an original claim split
Deduction resulting from an original deduction split
These types can not be customized. Do not change this level in any way.
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.
For information on the predefined series, see the Oracle Demantra Deduction and Settlement Management User's Guide.
The UPGRADE_TO_DSM procedure automatically creates integration interfaces to help you import or export the following:
G/L codes
Invoices
Settlements
Check requests
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. |
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.