Oracle Incentive Compensation supports use of a transaction interface to bring compensation transactions into the system. To process incentives, Oracle Incentive Compensation uses the CN_COMM_LINES_API table as a transaction interface for the following:
• Standard collection of sales credit transactions using out-of-box integration with Oracle Receivables (for Invoices/Credit Memos/Payments and so on) and Oracle Order Management (for Booked Orders).
• Collection from custom sources of data using the standard collection feature.
• Uploading transactions directly into the CN_COMM_LINES_API table using SQL*LOADER, SQL, PL/SQL routines, and so on, for processing Incentives.
• Importing data from a spreadsheet (see Defining Imports, page 5-12 and the following pages).
If you intend to upload transactions directly into the CN_COMM_LINE_API table or import data from a spreadsheet, the table below provides a detailed description of key attributes of the CN_COMM_LINES_API table. See the Oracle Incentive Compensation TRM for more detailed information on each column.
Column Data Type Description
COMM_LINES_API_ID NUMBER(15) This is the unique identifier of the table and must be populated with a value. If using the SQL* LOADER then use the sequence
CN_COMM_LINES_API_S.ne xt val.
PROCESSED_DATE DATE The date the transaction
needs to be processed for compensation calculation.
This is a mandatory column.
TRANSACTION_ AMOUNT NUMBER The transaction amount that
needs to be used as a basis for arriving at the compensation value. This is a mandatory column.
TRX_TYPE VARCHAR2(30) The type of the transaction. It
can have any one of the following values:
CBK: Claw Back (or Take Back)
CM: Credit Memo GBK: Give Back INV: Invoice
MAN: Manual Adjustment ORD: Booked Orders PMT: Payment Received WO: Write Off
This is a mandatory column.
Column Data Type Description
EMPLOYEE_NUMBER VARCHAR2(30) The resource that is receiving credit for the transaction. If using the SQL*LOADER option or the spreadsheet, the value needs to be Salesrep Number as defined in Resource Manager. Please refer to Resource Manager documentation for more information on how to define a resource.
OBJECT_VERSION_NUMBE R
NUMBER Sequential number which is
set at 1 on insert and
incremented on update. Used by APIs to ensure that the current record is passed. This is a mandatory column. When you upload a transaction directly into the API, the value must be set to 1.
SALESREP_ID NUMBER(15) This value is populated by
standard collection programs of the application. Custom and nonstandard processes should not populate a SALESREP_ID value. The EMPLOYEE_NUMBER column should be used instead.
Column Data Type Description
LOAD_STATUS VARCHAR2(30) This denotes the status of the
transaction after the
Transaction Interface Loader process is run to load transactions from the API table to the
CN_COMMISSION_HEADE RS table. Here are two important status values:
UNLOADED: Status of new transactions when they are first loaded into the API table.
It also denotes transactions that have not been picked up for loading into the
CN_COMMISSION_HEADE RS table.
LOADED: The transaction is successfully loaded into the CN_COMMISSION_HEADE RS table. See Validation Checks and Resolution Method for more details and the rest of the
LOAD_STATUS values.
TRANSACTION_CURRENC Y_CODE
VARCHAR2(15) The currency code of transaction amount that comes with the transaction, for example, USD. The value must correspond to the currency code as defined in GL-Currencies. See the GL-Currencies form to confirm. See Note: in Exchange Rates following.
Column Data Type Description
EXCHANGE_RATE NUMBER The exchange rate that needs
to be applied to determine the actual amount of the
transaction. Ideally, this matches what is defined in the GL-Daily Rates table.
Note:
Transaction_Currency_Code and Exchange_Rate are not mandatory. If these two values are left as Null the application assumes that the currency_code matches the functional currency and that no exchange rate will be applied. However, if you populate TRANSACTION_
CURRENCY_CODE with currency that is not the functional currency, you must populate EXCHANGE_RATE.
This is done automatically using the GL rates with using the Transaction screen. If done using SQL, you can populate whatever exchange rate you want.
ADJUST_STATUS VARCHAR2(10) The adjustment status of the
transaction. It is automatically updated on the Adjustments screen when the system makes adjustments. This is a system-generated field and should not be populated with any value.
Column Data Type Description
ORG_ID NUMBER Needs to be populated with
the specific org for which the transactions are being loaded for Incentives calculation. If the implementation is multi-org, then it is a mandatory column. If the implementation is not multi-org, then the column can have null value.
ROLLUP_DATE DATE The date the transactions
must use to roll up the sales credit using the compensation group hierarchy. If this column is null, the date value in the PROCESSED_DATE column will be set for this column. If you need this date to be different from the PROCESSED_DATE you can set it to a different value.
REVENUE_TYPE VARCHAR2(15) This column denotes whether
the transaction is of a Revenue or Nonrevenue sales credit type. For transactions collected directly from out-of-box integration with Oracle Order Management or Oracle Receivables, this column is populated automatically with the respective sales credit type defined in these source systems. Note: When the application collects
nonrevenue transactions from Oracle Receivables, if there is a quantity value, it is zeroed out during collection.
Column Data Type Description
ATTRIBUTE1 - ATTRIBUTE100
VARCHAR2(240) These are the descriptive flexfield columns on the transaction. They can be populated with values to be used in defining classification rules or for defining
expressions to be used in formulas, rate tables, and so on.