Incent Upload Currency Rates

Summary

The incent upload currency rates command allows developers to create and maintain currency exchange rates within the Incent application. The command transfers data from staging.currency_rate table into Incent’s xactly schema tables.

A developer must first populate a Connect staging table with exchange rate data before invoking incent upload currency rates. The general process flow is:

  1. Delete any old rows from the staging.currency_rate and staging.currency_rate_exception tables. Archive any rows as needed by per your business requirements.
  2. Insert rows with your company’s exchange rate data into the staging.currency_rate table.
  3. Call incent upload currency rates to transfer the data from staging into Incent.
  4. Check for and fix any processing errors in the staging.currency_exception table. Rerun incent upload currency rates as needed.

Syntax

incent [synchronous] upload currency rates; 

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 staging schema provides one table that a developer can populate before invoking incent upload currency rates. The table, staging.currency_rate, is described in detail below. Once populated, invoking incent upload currency rates will process each staging row. Any errors the upload encounters during processing will be written to the staging.currency_rate_exception table.  Xactly recommends the developer check for any errors in the table and using SHOW INVOCATIONS after the command completes to ensure all staging rows were processed successfully.

 

The following tables describe the columns in each of the staging tables:

staging.currency_rate – (Required)
Column Name Data Type Description xactly Target Table
action string (Required) The value instructs the type of operation the upload should perform on the row. incent upload currency rateshas two options:

  • ‘save’ tells the upload command to perform an insert operation. If the currency rate does not exist, save will insert a new row into Incent.

If the currency rate for the base and target currencies and start_dates/end_dates already exists in Incent, save will NOT perform an update. First delete the Incent row, then perform a save operation.

  • ‘delete’ is the other supported action. Delete attempts to remove a currency exchange rate from Incent using the base and target currencies and start_date/end_date values it finds on a staging row. If the base or target currency rates are null,delete will attempt to delete rows from Incent using the start_date and end_date values. In this case, delete will abort if it finds more than one matching row in Incent. Xactly best practice is to explicitly specify base and target currencies.
n/a
base_currency_name string (Optional for delete, required for save) Must match a valid currency type value in xactly.xc_unit_type.name. The starting currency in an exchange rate calculation. To convert from USD to EUR for example, USD is the base currency and EUR is the target currency. On September 14, 2016: 1 USD (base) = 0.89 EUR (target).

Conversely, to convert from EUR to USD in Incent compensation rules, EUR is the base currency and USD is the target. On September 14, 2016: 1 EUR (base) = 1.12 USD (target). Two entries are needed to convert from USD to EUR and EUR to USD.

xactly.xc_currency_rate.base_currency_name
target_currency_name string (Optional for delete, required for save) Must match a valid currency type value in xactly.xc_unit_type.name. The converted to currency in an exchange rate calculation. Refer to example in prior row. xactly.xc_currency_rate.target_currency_name
start_date date (Required) The earliest date a given exchange rate row is effective. xactly.xc_currency_rate.active_start_date
end_date date (Required) The last date a given exchange rate row is effective. xactly.xc_currency_rate.active_end_date
exchange_rate decimal (Optional for delete, required for save) The exchange rate value for the base-to-target currency conversion. Refer to examples in the base_currency_name description. xactly.xc_currency_rate.exchange_rate
staging.currency_rate_exception – The upload command writes any errors encountered during process to this table. incent upload users truncates this table automatically for each run. Archive any existing error rows as needed before running the upload command.
Column Name Data Type Description
action string Refer to staging.currency_rate
name string Refer to staging.currency_rate

 

 

Exception Messages
Exception Code Exception Message How to Resolve
102 currency rate does not exist Occurs when trying to delete an exchange rate with start_date and end_date values that do not match to a the same date range in Incent.
102 Attempting to delete currency rate which overlaps more than 1 existing currency rate. Occurs when attempting to delete a currency date row in Incent having the following conditions:

1) Null values in staging.currency_rate.base_currency_name and staging.currency_rate.target_currency_name

2) Incent has more than one exchange rate row with matching start_date and end_date values.

Provide more information on the staging row to allow Incent to narrow down the delete to one row in Incent.

103 Invalid currency name: <staging table currency given> Find valid currency values in xactly.xc_unit_type.name.
2702 Expression evaluation fails for {0}. Check that staging.currency_rate.start_date is <= before staging.currency_rate.end_date.
20079 Currency rate date range should be unique. Occurs when a staging row has start_date and end_dates that conflict or overlap with exchange rate data that already exists in Incent. First delete the date range from Incent using the delete action and then save the new row.

 

Examples

incent upload currency rates; 

screen-shot-2016-09-16-at-1-58-24-pm

select * from staging.currency_rate_exception;

screen-shot-2016-09-16-at-1-58-30-pm

Related Commands

INCENT CREDENTIAL

SHOW INVOCATIONS