Previous	Next		Contents	Index	Navigation		Library

An example of using an expression may be the following scenario--you want to bring the information in the QUANTITY_ORDERED column in the Oracle Receivables table RA_CUSTOMER_TRX_LINES into Oracle Sales Compensation table CN_TRX_LINES; if the value in the QUANTITY_ORDERED column is NULL, you want it changed into zero (0) in the corresponding Oracle Sales Compensation table. You would accomplish this by using an expression.

Oracle Sales Compensation moves information from the CN_TRX_LINES attribute columns to the attribute columns in the CN_COMMISSION_LINES table by matching the value in a column called the User Column Name. The first part of the mapping procedure, therefore, consists of defining the User Column Name for the Oracle Sales Compensation tables CN_TRX_LINES (at the Line level) and CN_COMMISSION_LINES on the Tables and Columns form. Table 3 illustrates the source and destination tables and columns for the mapping.

Table 3: Source and Destination Tables and Columns for Direct Mappingfrom Oracle Receivables to Oracle Sales Compensation at the Line Levelusing an Expression

Receivables Source Table	Receivables Source Table Alias	Receivables Source Column Name	Receivables Source Column Expression
RA_CUSTOMER _TRX_LINES	RCTL	QUANTITY_ORDERED	NVL(RCTL.QUANTITY_ORDERED,0)

Intermediate Sales Compensation Table	Intermediate Sales Compensation Table Column	Intermediate Sales Compensation Table User Name Column
CN_TRX_LINES (Line level)	ATTRIBUTE2	AR_QUANTITY_ORDERED
Destination Sales Compensation Table	Destination Sales Compensation Table Column Name	Destination Sales Compensation Table User Name Column
CN_COMMISSION_LINES	ATTRIBUTE3	AR_QUANTITY_ORDERED

   To define direct mapping at the line level using an expression:

1. Navigate to the Tables and Columns form.

By default, choose Tables and Columns from the Setup menu.

2. Select Query->Enter from the menu and type CN_TRX_LINES in the Table Name field to execute a query.

3. Select Columns to see the columns in the CN_TRX_LINES table.

4. Move to the row for the column named ATTRIBUTE2.

5. In the User Column Name field on that row, type a unique name that will identify the information that the column will hold.

For this example, type AR_QUANTITY_ORDERED in the User Column Name field, which represents the Receivables line item type.

Attention: The name you type in the User Column Name field is your name for the column, and this name must NOT be the same as the system name for the column. If you are bringing in transaction information into the ATTRIBUTE1 field, then the name in the User Column Name field must NOT be ATTRIBUTE1.

6. Repeat step 5 to create a User Column Name for ATTRIBUTE3 in CN_COMMISSION_LINES called AR_QUANTITY_ORDERED.

Now, you must set up the mapping between the QUANTITY_ORDERED column in the Oracle Receivables table RA_CUSTOMER_TRX_LINES, and the ATTRIBUTE2 column in the Oracle Sales Compensation table CN_TRX_LINES.

7. Navigate to the Receivables Mapping form.

By default, choose Collections from the Setup menu, and then choose Mapping.

8. Select Line from the alternate region.

You use the Line column mappings because the data is at the line item level and it is being moved into destination table CN_TRX_LINES in Oracle Sales Compensation.

9. With the Line mappings displayed, select Edit->New Record to enter a new mapping.

10. Enter the following values:

Destination Column: ATTRIBUTE2 AR Source Table: RA_CUSTOMER_TRX_LINES Expression: NVL(RCTL.QUANTITY_ORDERED,0)

11. Save your work and close the AR Mapping window.

   To collect the transaction data according to the mappings you just defined:

1. In the Collections window, select Synchronize.

2. Follow the steps described in Tasks 2 and 3 of the section Overview of the Oracle Receivables Transaction Data Collection Process for the steps required to complete the data collection.

Note: For more information on running the Concurrent Manager, see the Oracle Applications User's Guide.