Incent Upload Orders

Summary

The incent upload orders command allows developers to load sales transactions into Incent. Sales transactions generally represent a sales event (Example: customer account ABC123 bought 10 widgets from salesperson John Smith on April 1, 2016). Incent rules process sales orders to calculate commissions and bonuses for your company’s payees.

Orders define a record of sale for your company that is used to trigger compensation calculations.   Each row in the orders table will produce a calculation within Incent.  Orders often include information such as a unique identifier, customer, product, amount, date, and payee assignment.  Additional custom fields allow each customer to load data relevant to their business.  All columns on the order data can be used for calculation and reporting within Incent.

It is common to have multiple types of transactional data (revenue, bookings, invoices, calls booked, etc) that needs to be loaded into Incent.  The orders table is usually categorized by “Order Type” field found on each order.  The Order Type allows the Incent admin to easily write compensation rules that identify the proper orders for calculation.

Incent Admin Guide: https://helpcenter.xactlycorp.com/article/incent-administrator-orders-introduction/

The command transfers data from the staging.order_item and staging.order_item_assignment tables into Incent.

The following list provides the generic steps to follow:

  1. DELETE data from the following three tables. Archive rows as necessary as dictated by your business requirements: staging.order_item, staging.order_item_assignment, and staging.order_item_validation_error.
  2. Perform any ETL transformations necessary to prepare your raw sales transaction data for upload into Incent. INSERT the transformed data into staging.order_item and staging.order_item_assignment.
  3. Optional: Call INCENT PURGE ORDERS to remove old order batches from Incent that you want to reload. Note: Your implementation may use Incent’s Process Group features to purge orders from Incent and reset calculation results. Check with your project team before using Connect’s INCENT PURGE ORDERS command.
  4. Call INCENT CREATE BATCHES to create register any new batch names found in your staging data with Incent.
  5. Optional: You may need to INSERT new customers, geographies, and/or products with Incent before proceeding. Refer to: INCENT UPLOAD CUSTOMERS, INCENT UPLOAD GEOGRAPHIES, and INCENT UPLOAD PRODUCTS.
  6. Call INCENT VALIDATE ORDERS to run Connect’s standard set of data validations against your staging table data. Validate runs against all rows and batches present in the staging tables.
  7. Fix or archive any staging rows that failed validation. Connect cannot upload data into Incent if a single staging row has failed validation.
  8. Finally, call INCENT UPLOAD ORDERS to transfer validated staging data into Incent.

screen-shot-2016-09-08-at-8-44-26-am

You may omit explicit calls to INCENT CREATE BATCHES and INCENT VALIDATE ORDERS. The INCENT UPLOAD ORDERS command invokes them implicitly. However, if any rows fail during the implicit data validation, the upload will not load any staging rows into Incent. Xactly recommends explicitly calling the create batch and validate commands. This approach allows you to remove any staging orders that failed validation prior to running the upload command. The upload command will not load any staging data into Incent if a single row has failed validation and is not removed from the staging tables.

Syntax

 

incent [synchronous] upload orders; 

Parameters

Parameter
Description
 
synchronous
 (Optional) The upload command runs asynchronously by default when invoked, i.e. control returns immediately to the calling program and the job runs in the background on the Connect server. Use the ‘synchronous’ keyword to force the Connect server to return control only after the upload completes.

When invoked asynchronously, you can track the status of the upload by querying the SHOW INVOCATIONS command. Xactly recommends running large data sets asynchronously to avoid client query timeouts.

Details

Ensure you have registered a valid Incent username and password within Connect before invoking the upload command. INCENT commands require valid credentials to modify data with Incent. Refer to INCENT CREDENTIAL.

The upload command operates against the complete set of rows found in the staging.order_item and staging.order_item assignment tables.

Many Incent implementations rely on a concept called, “trigger orders”. Your business requirements may specify that a Connect developer should manufacture trigger orders as part of routine data processing to affect how certain compensation rules fire. “Trigger orders” are simply sales orders that allow Incent rule writers to control the execution order and flow of their compensation rules. The concept is mentioned here because trigger orders are a type of sales order in Incent.

 

The command does not upload orders when the following Incent operations are already running: uploads to order staging, order validation, finalize, and order deletion.

screen-shot-2016-09-08-at-8-44-02-am

The following tables describe the two staging tables used to move sales orders into Incent. Refer to INCENT VALIDATE ORDERS for information on the staging.order_item_validation_error tables and messages it may contain regarding your staging table data. Use both tables when using the upload command. It is not possible to use each table in stand alone fashion, e.g. to upload employee assignments for a sales order that already exists in Incent.

staging.order_item – (Required) Insert sales order and line item detail into this table.
Column Name Data Type Description xactly Target Table
order_code string (Required) Incent enforces a constraint on order_code and item_code to uniquely identify a sales order. A single order_code can have many line items oritem_codes.

xactly.xc_order_stage.order_code
item_code string (Required) Incent enforces a constraint on order_code and item_code to uniquely identify a sales order. Use item_code to assign different line items to a given order_code.

xactly.xc_order_stage.item_code
source_id decimal Do not use. For internal Incent use only.

xactly.xc_order_stage.source_id
amount decimal (Required) The total transactional amount for a given line_item. Typically, orders represent sales transaction events as currency values (1 widget sold for $50), but can also track non-currency decimal values.

xactly.xc_order_stage.amount
amount_unit_type_name string (Required) Should correspond to a valid xactly.xc_unit_type.name value. Many numeric values in Incent must be assigned to a unit type. Examples include: USD, EUR, quantity, and percent.

xactly.xc_order_stage.amount_unit_type_id
discount decimal (Optional) Sometimes used in compensation rules and reporting to track any discount amounts or percentages given to a buyer for a given sales orders. The field’s use and context depends entirely on your company’s business requirements and Incent implementation.

xactly.xc_order_stage.discount
quantity decimal (Optional) Generally used to track the number of product units sold for the given order line item.

xactly.xc_order_stage.quantity
incentive_date date (Required) Represents the transaction or close date of the order line item. Value determines the monthly period the line item is assigned to. Refer to staging.order_item.period_name and staging.order_item_batch_name.

xactly.xc_order_stage.incentive_date
order_date date (Optional) Stores when a customer first placed the sales order.

xactly.xc_order_stage.order_date
order_type_name string (Optional) Should correspond to a valid xactly.xc_order_type.name value. Business requirements may specify that compensation rules behave differently for different types of orders.

xactly.xc_order_stage.order_type_id
batch_name string (Required) Incent requires every sales order row to be assigned to an order batch (refer to xactly.xc_user_batch). Incent uses batch names to group transactions together to optimize processing in Incent’s processing queue. Xactly recommends limiting the number of orders in a batch to 20,000 rows. Please contact Xactly’s technical support team for additional guidance if you wish to use larger batch sizes.

A batch_name cannot cross monthly periods and cannot be assigned to more than one batch_type_name (see next column).

xactly.xc_order_stage.batch_id

xactly.xc_order_stage.batch_name

xactly.xc_user_batch.batch_name
batch_type_name string (Optional) Every sales order batch in Incent must be assigned to a batch type name. Batch type names are used alongside Incent’s Process Group feature. Your compensation administrator defines the set of possible values. Queryxactly.xc_batch_type.name for the list of valid values.

Two choices exist when populating the stage table:

  1. Setting to null causes the upload command to use the default batch type name, “Commissionable Events”.
  2. Use a valid value from xactly.xc_batch_type.name. Do not assign the samebatch_name to different batch_type_name values.
 xactly.xc_user_batch.batch_type_id
period_name string (Required) The leaf level, monthly period name for the given incentive_date. UseLookupPeriodName() function to return a period name given a date value. The functions helps you avoid cluttering your SQL statements with joins to thexactly.xc_period and xactly.xc_period_type tables. The upload command converts the period_name value given in the staging table to the appropriateperiod_id in the target table.

xactly.xc_order_stage.period_id
product_name string (Optional) If populated, must correspond to a valid xactly.xc_product.name. UseINCENT UPLOAD PRODUCTS to upload new products into Incent or modify existing ones prior to uploading stage orders.

The upload will transform the product_name into the correct product_id during the upload.

xactly.xc_order_stage.product_id

xactly.xc_order_stage.product_name
customer_name string (Optional) If populated, must correspond to a valid xactly.xc_customer.name. Use INCENT UPLOAD CUSTOMERS to upload new customers into Incent or modify existing ones prior to uploading stage orders.

The upload will transform the product_name into the correct product_id during the upload.

xactly.xc_order_stage.customer_id

xactly.xc_order_stage.customer_name
geography_name string (Optional) If populated, must correspond to a valid xactly.xc_geography.name. Use INCENT UPLOAD GEOGRAPHIES to upload new geographies into Incent or modify existing ones prior to uploading stage orders.

The upload will transform the product_name into the correct product_id during the upload.

xactly.xc_order_stage.geography_id

xactly.xc_order_stage.geography_name
related_order_code string (Optional) Use related_order_code and related_item_code to associate a staging sales order with a different sales order that already exists in Incent. The related sales order may exist in a different batch and period.

xactly.xc_order_stage.rel_order_code

xactly.xc_order_stage.related_item_id
related_item_code string (Optional) Use related_order_code and related_item_code to associate a staging sales order with a different sales order that already exists in Incent. The related sales order may exist in a different batch and period.

xactly.xc_order_stage.rel_item_code

xactly.xc_order_stage.related_item_id
description string (Optional) Can store notes or other information regarding the sales order.

xactly.xc_order_stage.descr
Custom_Fields varies (Optional) Incent allows compensation administrators to add custom string, numeric, and date columns to sales orders. The column labels given by the business user in Incent’s UI should be present in the staging.order_item table. If they are not, please contact Xactly’s tech support team. They can work with you to make the custom column labels visible. The sync must be performed by Xactly personnel until further notice. xactly.xc_order_stage.Custom_Fields

 

staging.order_item_assignment – (Required) Use this table to assign sales orders to specific employees. Generally, employees assigned to a sales order receive commission or other compensation for their involvement in closing a given sales order. Behavior is controlled by your implementation’s Incent rules and business requirements. Every staging.order_item row must have a least one order_item_assignment. Insert one row for each distinct employee assignment to a given order_code anditem_code.
Column Name Data Type Description xactly Target Table
order_code string (Required) Refer to staging.order_item

xactly.xc_order_stage.order_stage_id

xactly.xc_order_stage_asgnmt.order_stage_id
item_code string (Required) Refer to staging.order_item

xactly.xc_order_stage.order_stage_id

xactly.xc_order_stage_asgnmt.order_stage_id
employee_id string (Required) Must correspond to a a valid xactly.xc_participant.employee_id value. xactly.xc_order_stage_asgnmt.participant_id
split_amount_pct decimal (Required) Set to a decimal or integer value between 0-100. Sometimes used in Incent compensation rules to determine the percentage of a sales order that the listed payee is qualified to receive. xactly.xc_order_stage_asgnmt.split_amount_pct

 

Examples

incent upload orders;

screen-shot-2016-09-08-at-8-44-11-am

Related Commands

INCENT CALCULATE

INCENT CREATE BATCHES

INCENT CREDENTIAL

INCENT PURGE ORDERS

INCENT VALIDATE ORDERS

SHOW INVOCATIONS