Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Enterprise Sales Analytics > Process of Configuring Siebel Enterprise Sales Analytics for Oracle 11i >

Accounting for Negative Values in Orders, Invoices, and Picks

This task is a step in the Process of Configuring Siebel Enterprise Sales Analytics for Oracle 11i.

By default, the Siebel Customer-Centric Enterprise Warehouse does not use negative values in the quantity or amount columns for the IA_SALES_IVCLNS table or the IA_SALES_ORDLNS table. However, you can make these values negative using a column called VAR_NEGATIVE_SIGN. By default, this column has the value 1.0. To make the values negative, modify the column value to be -1.

For example, to account for a negative return value for a Return Material Authorization (RMA) or for a negative value in a credit memo, you can use a conditional statement to define the VAR_NEGATIVE_SIGN column.

Assume that the S14 column in SO_LINES_ALL table has been configured in Oracle 11i to have the value 30 if the order line is a return. You can use this identifier for returned orders as a condition for setting the VAR_NEGATIVE_SIGN column to be -1. To do this, you can modify the VAR_NEGATIVE_SIGN column's definition in the MPLT_SAO_SALES_ORDLNS as follows:

DECODE(INP_LINES_S14, 30, -1, 1)

For Oracle 11i, the VAR_NEGATIVE_SIGN column is available in the following Source Adapters—MPLT_SAI_SALES_ORDLNS, MPLT_SAI_SALES_IVCLNS and MPLT_SAI_SALES_PCKLNS.

In Oracle 11i the VAR_NEGATIVE_SIGN column's value is set based on the type of order line.

To configure mapplets to account for negative values

1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
2. Open the applicable Source Adapter mapplet.
3. Double-click the Expression transformation to open the Edit Transformation box.
4. In the Ports tab, edit the VAR_NEGATIVE_SIGN port.

   For example, if the S14 column in SO_LINES_ALL table has been configured in Oracle 11i to have the value 30 if the order line is a return, then you can use this identifier for returned orders as a condition for setting the VAR_NEGATIVE_SIGN column to be -1. To do so, you would set the VAR_NEGATIVE_SIGN column's definition as follows:

   DECODE(INP_LINES_S14, 30, -1, 1)

5. Validate and save your changes to the repository.