Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle Order Management and Fulfillment Analytics Option > Process of Configuring Oracle Order Management and Fulfillment Analytics Option for Oracle 11i >

Accounting for Negative Values in Orders, Invoices, and Picks

This task is a step in the Process of Configuring Oracle Order Management and Fulfillment Analytics Option for Oracle 11i.

By default, the Oracle Business Analytics Warehouse does not use negative values in the quantity or amount columns for the W_SALES_INVOICE_LINE_F table or the W_SALES_ORDER_LINE_F 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_SA_ORA_SalesInvoiceLinesFact
• mplt_SA_ORA_SalesOrderLinesFact
• mplt_SA_ORA_SalesPickLinesFact
• mplt_SA_ORA_SalesScheduleLinesFact

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 SDE_ORA115<ver>_Adaptor folder.
2. Open the applicable Source Adapter mapplet (for example, mplt_SA_ORA_SalesInvoiceLinesFact).
3. Double-click the Expression transformation to open the Edit Transformation box.

   For example, if you are editing the mplt_SA_ORA_SalesInvoiceLinesFact mapplet, the Expression transformation is EXP_SALES_PCKLNS.

4. In the Ports tab, edit Expression value for 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.