Previous	Next		Contents	Index	Navigation		Library

The CN_COMM_LINES_API table contains many of the same columns as the CN_COMMISSION_LINES table in which Oracle Sales Compensation stores all information relevant to a transaction as it is processed. In general, the CN_COMM_LINES_API table excludes those columns of the CN_COMMISSION_LINES table which are filled in with information during transaction processing.

The structure of the CN_COMM_LINES_API table is as follows:

SALESREP_ID	NOT NULL NUMBER (15)	Enter the id of the salesperson who is the direct credit receiver. Required.
PROCESSED_DATE	NOT NULL DATE	Enter the date of the transaction.
PROCESSED_PERIOD_ID	NUMBER (15)	Optionally, enter the period the process date falls in.
TRANSACTION_ AMOUNT	NOT NULL NUMBER	Enter the amount of sales credit
TRX_TYPE	NOT NULL VARCHAR2 (30)	Enter a value such as INVOICE.
REVENUE_CLASS_ID	NUMBER (15)	Optionally, enter the ID of the revenue class for this transaction.
LOAD_STATUS	VARCHAR2 (30)	Enter NULL when the record is first entered; this field is updated to LOADED when Load.API is run.
ATTRIBUTE1 through ATTRIBUTE45	VARCHAR2 (80)	Descriptive flexfield columns. These columns map to the attribute columns in CN_COMMISSION_LINES table.
EMPLOYEE_NUMBER	NUMBER(15)	Should be the salesrep number in the Sale workbench.
SALESREP_NUMBER	NUMBER(15)	Not used.
ROLLUP_DATE	DATE	Not used.
ROLLUP_PERIOD_ID	NUMBER(15)	The period-id for creating rollup transactions.
SOURCE_DOC_ID	NUMBER(15)	Not used.
SOURCE_DOC_TYPE	VARCHAR2(30)	To say what source sales compensation is being fed from an AR or OE etc.
TRANSACTION_CURRENCY_CODE	VARCHAR2(15)	Transaction currency code.
EXCHANGE_RATE	NUMBER	If transaction currency code is not the same as the functional currency, then the exchange rate has to be entered which is multiplied by the transaction amount to get the functional currency.
ACCTD_TRANSACTION_AMOUNT	NUMBER	The system updates this column with functional currency amount.
TRX_ID	NUMBER(15)	Used by AR collection.
TRX_LINE_ID	NUMBER(15)	Used by AR collection.
TRX_SALES_LINE_ID	NUMBER(15)	Used by AR collection.
QUANTITY	NUMBER(10)	To enter the quantity on a transaction.

Attributes 1 through 45 are the descriptive flexfield columns you use to specify information not included in the base table. See Oracle Sales Compensation Technical Reference Manual Update for detailed information on Oracle Sales Compensation tables and columns.

Before populating the CN_COMM_LINES_API table and running the Transaction Interface Loader program, you should:

1. Create the Salesreps in the Salesperson Workbench.

2. Make sure that each Salesperson's Active_From and Active_To period is entered.

3. The columns in CN_COMM_LINES_API table map to different columns in the Oracle Sales Compensation system as follows:

• Employee_number in the API should be the same as the Salesrep_Number in the Salesperson Workbench. It is the same value found in the Employee_Number column in theCN_Salesreps view and the Salesrep_number column in the RA_Salesreps table.

• The Processed_date needs to be a date in between the Active_To and Active_Fromperiods for the Salesrep as defined in the Salesperson workbench.

• Trx_Type should one of these types : 'INV', 'PMT', 'WO', 'CBK', 'GBK', 'CM' which correspond to invoice, payment, writeoff (or adjustments), clawback, giveback or credit memo

• Transaction_Amount needs to entered. This is equivalent to Net Revenue.

• Leave Load_status as NULL. This will be updated by OSC once the transactions are loaded into the CN_COMMISSION_LINES table.

• There is no need to enter the Revenue_Class_id since OSC will classify the transaction into the appropriate revenue class based on the Attribute information defined on the transaction and the revenue class rules defined in OSC.

• Rollup_period_id is the period which determines which Salesperson hierarchy should be used to roll up the transactions from the direct credit receiver (i.e. the salesrep defined on the transaction) to indirect credit receivers in the sales credit rollup chain. If the rollup_period_id is not entered, OSC uses the processed-date as the default.

• Source_Doc_Type should define the source of transactions. For example it could be from AR, PA or other.

   To collect transactions using the open interface:

1. Load the CN_COMM_LINES_API table with your transaction data.

You can load the data using SQL*Loader, manually, or by copying from your existing tables.

2. Do one of the following:

• Using the Concurrent Manager, run the Transaction Interface Loader program.

By default, select Run from the Concurrent Requests menu to navigate to the Submit Requests window. Enter the name of the program in the Name field and select Submit.

• In SQL*Plus, run the CN_COMMISSION_LINES_PKG.Load procedure to load the transaction data from the CN_COMM_LINES_API table to the CN_COMMISSION_LINES table.

Once you run the Transaction Interface Loader or the CN_COMMISSION_LINES_PKG.Load procedure, you cannot update or delete the loaded transactions. To make changes to a transaction entered using the CN_COMM_LINES_API table, you must enter a manual transaction to cancel the transaction and enter a new transaction using the CN_COMM_LINES_API table or the Maintain Transactions window. See Entering a Manual Transaction for more information.

Error	Description
ERROR - TRX_TYPE	Incorrect transaction type. Not in the list of ('INV', 'PMT', 'WO', 'CBK', 'GBK', 'CM').
ERROR - REVENUE_CLASS	Incorrect revenue_class_id was entered but it does not exist in the CN_Revenue_Classes.
ERROR - NO EXCH RATE GIVEN	For entering the multi-currency transactions the exchange rate for the foreign currency needs to be also entered.
ERROR - INCORRECT CONV GIVEN	Incorrect conversion rate given.
ERROR - CANNOT CONV/DEFAULT	Cannot convert the given acct_transaction_amount.
SALESREP ERROR	The Salesrep Number is not valid.
PERIOD ERROR	The Processed_Date/Processed_Period_ID does not exist in the CN_SRP_PERIODS table. That is, the Processed_date does not fall in between the salesrep's Active_From and Active_To periods.
Table 1 - 2. (Page 1 of 1)