This chapter covers the following topics:
You must define from where Oracle Incentive Compensation collects the transactions on which commission is paid. The first step is setting up collection parameters. Then, you can proceed to configuring tables and columns, defining any external tables you want to use, defining collection sources and mapping, and finally, generating collection packages.
Navigation
Setup Tasks > Collection > Setup Collection Parameters
When you set up collection parameters, a number of factors affect how transactions are collected.
The Collection Batch Size affects the amount of time the application requires to process the total transactions. The ideal collection batch size is related to your transaction volume and the capacity of your system CPU. Start by using the default setting and adjust it if needed.
The Number of Days for Clawback sets the number of days allowed after the invoice payment due date before sales credit that has been paid out to resources is taken back. This number depends on company business practices.
The Reload Errored Transactions parameter determines whether or not the system resets the load status of errored transactions to UNLOADED so that they can be picked up again during the next transaction loading process. The default setting is No.
When Apply Invoice Splits and Move to Credit Memos and Payments is set to Yes, any splits or moves done to Invoices are automatically updated to the credit memos and payments, unless the transactions are delinked. This is a convenience that can save time, but if you don't need to split credit memos, you can set it to No. The default setting is No.
If Collect Credit Memos from Oracle Receivables is set to No, the application collects only invoices and regular credit memos. If set to Yes, then the application also collects account credit memos when running Oracle Receivable Collection. Set this to match your business practice. The default setting is No.
The Negate Original Transactions during Revenue Adjustments Collection parameter gives you a choice of how transactions are collected for revenue adjustments. If set to the default setting, Yes, Revenue Adjustments Collection first negates the corresponding transactions that have been collected before, and then re-collects from Oracle Receivables with the new revenue adjustments. If your business practices indicate it, you can set the parameter to No, and only the new revenue adjustments in Oracle Receivables will be collected.
Tables from Accounts Receivable, Order Management, or an external source that are used in collecting and calculating transactions in Oracle Incentive Compensation must be defined before they can be used in collection.
Oracle Incentive Compensation collects transaction data from the comm.lines.api table for processing commissions for resources. Set up the columns that contain the data you need to collect. The application comes seeded with the setup you need for standard collection. For custom collection use any of the 100 configurable attribute columns.
After you have defined tables for custom collection, use the Columns area below it to define specific columns and relate them to columns in other tables. Be sure that you have selected Collection in the Usage column and selected Attributes in the View Column list of values in the Columns section below.
Navigation
Setup Tasks > Collection > Configure Tables and Columns
Notes
Specify the CN schema to narrow your search to Oracle Incentive Compensation tables.
A table user name is an optional name you can specify that is easier to remember and relevant to its use. Aliases are set up when the table is built, and are used in the same way.
For the Attributes view:
The column name is set in the application, but you can assign a user name to it to match your business process or for ease of use. The external call column is currently not used. You can see the name on the Collection related pages.
The Data Type field indicates if the column contains alphanumeric material (VARCHAR2), numerical data (NUMBER), or a date (DATE).
The data length is already defined in the database.
Check the Usage box to identify whether this column is available to build expressions for formulas.
The Foreign Key is not used in Collection.
If the information you need is in tables that are not in Oracle Incentive Compensation, you can join those tables to destination tables in Oracle Incentive Compensation. For example, you may want to use a product category name from an external table when you are building your classification rules.
The tables must already exist, and must be in the same instance as Oracle Incentive Compensation.
Navigation
Setup Tasks > Collection > Define External Table Mapping
Notes
When you search for a table you must supply a valid search criteria value in at least one field.
The table name alias is system generated and the Columns link appears in the External Columns column. It is used by the system to generate SQL code.
Select a table to open the Columns area, where you can map columns.
You can collect transactions from a variety of transaction sources. Oracle Incentive Compensation is delivered with two predefined transaction sources that allow the collection of data from Oracle Receivables and Oracle Order Management. Collection from these two seeded transaction sources is known as Standard Collection. For standard collection sources, you do not need to set parameters, notification queries, or collection queries.
Open collections allows you to collect data from any legacy system. For example, you can use a homegrown Order Management system that is the source of sales credits that need to be calculated to pay compensation to your sales force.
If you use Open collections, you must set all details for the collection process. These processes are accessed through the Details icon:
Use source tables to specify all the tables which are used during the creation of compensation transactions.
Use parameters to identify the transactions to be collected by a Notification Query (see below).
Use a notification query to identify and mark transactions for the collection process.
Use column mapping to specify what data is used to fill each of the destination columns when a compensation record is collected from the transaction source.
Use a collection query after the notification query, to collect all the data attributes mapped in the column mapping tab.
Triggers: Triggers are PL/SQL statements (functions and procedures) that you can insert at defined points in the collection procedure
Filters: Filters allow you to define criteria to remove unwanted transactions.
Both of the standard transaction sources are delivered with a set of mappings to populate the important columns in CN_COMM_LINES_API. You are allowed to change source values for these mappings and also to create new mappings of your own. See Column Mapping.
These are the setups that need to be done to collect data from this system for Open Collections.
Identify the header tables and line tables in the legacy system. You need to define either synonyms or views in the apps schema to point to these tables.
Register the tables in Oracle Incentive Compensation.
Define a new transaction source.
Add the new transaction source to the TRX TYPES lookup.
Identify the source tables from which the transactions are to be built.
Define the Notification and Collection Queries.
Define Runtime Parameters.
Define the mapping from the source system into an API table in Oracle Incentive Compensation (CN_COMM_LINES_API). Mapping is the way to specify how data from the source tables are used to populate the destination fields in the compensation transaction.
Define any optional collection actions, including triggers or filters. Triggers can be set up at three stages of collection:
Pre-notification
Post-notification
Post-collection
Test generate a collection package (PL/SQL package) and correct errors, if any.
Review and when there are no errors, generate the collection package.
The end result of setting up Collections for a particular Transaction Source is a PL/SQL package that is stored in the applications database. The Collect procedure within this package builds compensation transactions from the Transaction Source and stores them in CN_COMM_LINES_API. The Collect procedure executes the listing notification and the creation and updating of compensation transactions.
When you use a transaction source other than the two standard transaction sources, you must decide from which source tables you want to collect the transactions. The source of transaction lines will be the Line table. You can also use a Header table to collect other information that you need to calculate compensation, such as an employee number.
When using open collections, you must tell Oracle Incentive Compensation from which transaction source you will obtain the data for processing commissions. In the Transaction Source area, perform the following procedure to set up a custom transaction source.
Tables must be set up already in the transaction source.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping
Notes
For each Transaction Source there are three pieces of information:
Name: User-defined and changeable, and may include legacy sources.
Type: The short name of the Transaction Source. It is user-defined, must be unique, cannot be empty, and cannot be changed after it is created. When creating a new transaction source, the Type cannot be the same type as any existing seeded transaction source, such as Order Booking (OC), or Receivables (AR). It also cannot be the same as the type of existing receivables events, such as INV (Invoice), PMT (Payment), CBK (Clawback), or WO (Writeoff).
Status: Complete/Incomplete. This indicates whether the Collection package has been generated for the Transaction Source since the latest setup changes were made.
The Line Table is the primary source table of the Transaction Source.
The Key Column is the unique primary key of the line table.
Click Save after filling in all of the fields.
For any open or custom transaction source, you must create a new line in the TRX TYPES lookup in Oracle Incentive Compensation. If this is not done, all transactions collected from the custom transaction source will not appear on the Transactions page until after they have been loaded using the Load Transactions process.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping
Prerequisites
Steps
Find the value in the Transaction Source Type column for the custom transaction source.
In the Compensation Workbench, log in as the Incentive Compensation Administrator responsibility.
Click the Lookups link.
Query the lookup type TRX TYPES.
Add a new row for the custom transaction source and enter the Transaction Source Type value from step 1 into the Code column in the lookup set.
Save.
After the Transaction source has been defined, you must specify all the tables which are used during the creation of compensation transactions--the Direct Mapping tables. For the Receivables Posting and Order Booking selections, all the transaction source data is predefined and cannot be deleted or modified.
However, for custom transaction sources, the Line Table and Key column are required, which were defined when creating a new transaction source. The Line table contains the line items against which compensation is to be paid. The Key column is the field in the table which uniquely identifies each line.
Tables must be registered before they can be used.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon
Notes
Extra tables are used to attach additional information to the transactions so that they can be calculated. This table information should exist for all of the resources and may indicate Territory, Organization, or some other distinguishing feature.
If you specify an optional Header table, you must specify a Key column for it and in the Line Table Header Identifier you must specify the field in the line record which allows it to be joined to the Key column of the Header table.
Parameters allow you to narrow your focus of a notification query. For example, you can specify parameters for start date and end date if you are using a custom transaction source. The parameters are registered here during the collection setup process but the specific values are entered during the collection submission process. This allows you to change the values of the parameters whenever you need to without having to regenerate the collection package. For example, if one of the parameters is the name of a month, then each month you can change it while leaving the collection setup alone.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Parameters
If you are collecting data from a source other than the two standard collection sources, Receivables Posting and Order Booking, you need to tell Oracle Incentive Compensation from where to collect the transaction data and what data to collect. You can generate a list of transactions that are eligible for compensation using the Notification Query and Parameters sections. For standard integration with Oracle Receivables and Oracle Order Management, you cannot edit the query conditions.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Notification Query
Notes
The Notification Query shows the exact query which will be used to create the Notification list of line-level transactions which are eligible for compensation. The query joins together the mandatory Line table and the optional Header table. The optional Header table area is provided on the Source Tables page because many times users want to restrict the collection using an AND clause, which makes the Header table necessary.
The AND area is the only place where you can make changes; the FROM and WHERE sections are read-only. They were defined earlier during source table definition. The AND area links the tables together and creates the exact conditions for collecting the transactions needed for calculation.
Use the Mapping page to specify what data is used to fill each of the destination columns when a compensation record is collected from the Transaction Source.
Some of the Source Expression and Destination fields are prepopulated with the mandatory mappings required to perform collection to the CN_COMM_LINES_API. Some mandatory columns include Employee_Number, Transaction_Amount, Transaction_Type, and Source_Doc_Type. Some mandatory mappings cannot be removed but permit you to select the source expression, and some have both the source expression and the destination column defined and cannot be changed. You cannot generate a collection package if any of the required mappings contains a blank source expression field.
The contents of a source expression can be more than just a column name. It can contain an expression formed from one or more of the following items. An example for each is shown in parentheses.
a null value (NULL)
literal value ('My Text')
a column name (booked_date)
a table name with a column name (l_order_headers.booked_date)
a table alias with a column name (loh10000.booked_date)
a SQL function NVL(lol10001.ordered_quantity, 0)
a user function my_function(loh10000.booked_date,lsc10002.salesrep_id)
Mapping can be direct or indirect. Direct mapping uses source data from one or more of the tables in the From clause of the Collection Query. It is part of the Collection Creation query, so to define a direct mapping you simply type the appropriate SQL expression into the Source Expression field.
Indirect mapping is more complex, and uses From and Where clauses in an UPDATE statement. UPDATE statements are run after the main collection process has completed. Indirect mapping is used under special circumstances, for example, when you want to collect from a table that affects only some of the resources for whom you are collecting transactions.
There are two types of indirect mapping, free-form and relationship. With free-form mapping, you must manually type the exact FROM/WHERE clause in the FROM/WHERE box in the Indirect Table Details section on the Mapping page. A relationship indirect mapping requires that you set up a relationship on the External Table page first and then select it in the Join Relationship field of the Indirect Table Details section. The relationship type of indirect mapping is more restrictive, in that you can only define simple equivalence joins (=). Free-form mapping can include other tests, such as OR, BETWEEN, <, and so on. Relationship indirect mappings also only allow a join to a single indirect table.
However, the benefits of relationship mapping are that it is simpler to set up, can be reused in multiple mappings, and is easier to maintain.
Tables must exist in the transaction Source and the destination. For relationship indirect mapping, a Join relationship must be set up for any tables you plan to use.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Mapping
Notes
If you want to perform indirect mapping, after you have set up the source expression and destination, click the Indirect Mapping icon to open the Indirect Mapping page.
For a relationship mapping, enter a join relationship. You must have set up the join relationship in advance.
For a freeform mapping, enter the From/Where clause.
The API table in Oracle Incentive Compensation contains 100 seeded attributes that you can use for direct or indirect mapping, for example, Attribute 1, Attribute 2...Attribute 99.
The Collection Query area lists the exact tables and rows from those tables that you need to perform a collection. The tables in the FROM clause include the Line table, the (optional) Header table, and all of the tables listed as Extra Direct Tables on the Source Tables page. The WHERE clause already contains the necessary join information to get the right rows from the Line and Header tables. You must complete the WHERE clause for any Extra Direct tables.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Collection Query
You can change the Collection processing for the transaction source by adding triggers. If you are using the standard collection sources, you can also use filters (see the following section).
Triggers are single or multiple PL/SQL statements (functions and procedures) that you can insert into the cn_comm_lines_api_all table at three defined points in the Collect procedure:
Pre-Notification: at the beginning of the Notification query
Post-Notification: between running the Notification and Collection queries
Post-Collection: after the Collection query has been run
Use the Pre-Notification and Post-Notification insertion points if you want to make changes to the source tables. Use a Post-Collection trigger to make changes to data that is already loaded into the API table.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Triggers
Notes
The code can run up to approximately 2,000 characters.
Transaction filters are especially relevant to Receivables and Order Management, because you cannot change the collection query for those standard transaction sources. Filters allow you to define criteria to remove unwanted transactions. For example, if you want to filter out any transactions less than $100, you enter this text on a line in the Filters section:
api.transaction_amount < 100
There are two methods of filtering:
Mark as Filtered: Filtered transaction is collected in the CN_COMM_LINES_API, is marked as Filtered, but is not picked up for calculation.
Physically Delete: Transaction is deleted from CN_COMM_LINES_API.
Deleting the transactions helps keep the table at a more efficient size, so it is the recommended option.
Navigation
Setup Tasks > Collection > Define Collection Sources and Mapping > Details icon > Filters
After the setup is complete, you must generate a collections package before it can be used. You can test generate a package before generating the final package.
Event:The Event column is only relevant to the Receivables Posting transaction source.
Collect Flag: The Collect flag indicates whether or not to generate the package for the corresponding transaction source or Receivables event.
Test Status: The Test Status field shows if there is an error in the test package.
Package Status: The Package Status field tells you whether or not the Collection package has been generated for the Transaction Source since the latest setup changes were made.
To distinguish a test package from the actual package, the package name of the test version always ends with "_t"
A generated package replaces the existing version of the package, so in order to be sure it is correct, you can create a test version of the package by selecting the transaction source and clicking Test Generate.
The Test Generation--Details page displays the lines where the errors are so you can fix them. Mistakes are often due to invalid SQL that has been entered on the Queries or Mapping pages or in a trigger.
The Package Text section of the Test Generation--Details page also lets you review the entire generated package to be sure it works correctly. For example, you can see exactly where in the Collect procedure your triggers will be executed. If everything looks fine, return to the Generate Collection Package page and click Generate.
As a convenience, Oracle Incentive Compensation groups five separate transaction sources into the Receivables Posting transaction source. You can select which Receivables events you want to be collected. By excluding transactions that you do not need, you can save time in the collection process. The default value for each event is No.
Before you generate a collection package, the collections setup must be complete. A complete collections setup means that the following are defined and complete:
Transaction sources
Queries
Collection mapping
Direct and indirect mappings
Triggers and filters
Navigation
Setup Tasks > Collection > Generate Collection Packages