How to Use (Oracle Sales Compensation Help)


	Previous	Next		Contents	Index	Navigation		Library

How to Use

This section provides the steps for using the upload and download hierarchy features.

Before using this feature, it is important to understand the use of the Unique Column in these procedures, so that when asked to provide the name of the Unique Column in the concurrent manager programs, the user will choose the appropriate one.

The unique column that the user should choose is the column in the Dimension table which uniquely identifies each entry in the dimension table. In the Dimension form in Oracle Sales Compensation you associate each dimension with a dimension table that stores the dimension information. The dimension table will contain at the minimum a Primary Key column and a Dimension Value Column. The Dimension Value column uniquely identifies an entry then there needs to be a unique column defined in this dimension table. For example, for the salesperson dimension, the dimension value column corresponds to the column called Name in the CN_SALESREPS table. However the Name field does not contain unique information, since it is possible that two salesreps may have the same name. Instead there is another column in the salesperson dimension table, in this case, the employee number which contains information which uniquely identifies each salesperson since no two reps can have the same employee number..

Though you can sometimes choose the primary key column as unique column, in some cases, the value for primary key may vary from database to database. For example, for the salesperson dimension, the primary key column is salesrep_id which is system generated. In the test implementation of Oracle Sales Compensation for example, salesrep id for Bill Shallet with employee # 888 may be 1001, but in the production implementation, salesrep_id may be 1089. If you use salesrep id as unique column, 1001 may refer to a different salesrep or no salesrep at all. So if you use the concurrent procedure to upload the hierarchy specifying the Primary Key column as the Unique Column, then your upload procedure may fail when it cannot find the unique column in the dimension table which corresponds to the unique column value defined in the dimension Hierarchy api table.

In general, you need to choose a unique column which is database independent and unique. For some dimensions, it is easy to find a column which meets this requirement. For the revenue class dimension for example, the dimension value which corresponds to the revenue class name may be uniquely identified across databases. So in this case the dimension value column may be the unique column.

To upload hierarchy:

1. Navigate to Setup->Dimensions form within Oracle Sales Compensation. Choose the dimension that you will be uploading. Navigate to the Hierarchies form and Define the Hierarchy Name, and the Interval From and To periods.

2. Populate the CN_HIERARCHIES_API table making sure to include data for all the NOT NULL columns:

Name	Null?	Type
HIERARCHY_API_ID	NOT NULL	NUMBER(15)
DIMENSION	NOT NULL	VARCHAR2(30)
HEADER_HIER_NAME	NOT NULL	VARCHAR2(30)
START_PERIOD_NAME	NOT NULL	VARCHAR2(30)
END_PERIOD_NAME	NOT NULL	VARCHAR2(30)
DIMENSION_VALUE	NOT NULL	VARCHAR2(30)
UNIQUE_VALUE	NOT NULL	VARCHAR2(30)
ANCESTOR_DIM_VALUE		VARCHAR2(30)
ANCESTOR_UNIQUE_VALUE		VARCHAR2(30)
LOADING_STATUS		VARCHAR2(100)
ORG_ID		NUMBER(15)
Table 45 - 1. (Page 1 of 1)

Hierarchy_API_ID corresponds to the primary key column of the api table (this ID can be generated by the CN_HIERARCHIES_API_S sequence)

Dimension corresponds to the name of the dimension

Header_Hier_Name corresponds to the name of the hierarchy within the dimension

Start_Period_Name defines the start period of the hierarchy

End_Period_Name defines the end period of the hierarchy

Dimension_Value defines the Dimension Value information which will be seen as the Nodes in the hierarchy

Unique_Value includes data which uniquely identifies the entry. The values in this column should correspond to values defined in the Unique Column of the dimension table.

Ancestor_Dim_Value contains the dimension value information for the parent of this node

Ancestor_Unique_Value contains the data which will uniquely identify the parent node

Loading_Status will be updated by the Concurrent Manager procedures to identify whether the column has been loaded or not.

Org_id corresponds to the Org_id that is being loaded

3. Run the Concurrent Manager procedure called UPLOAD HIERARCHY to transfer data from the CN_HIERARCHIES_API into the appropriate dimension hierarchy tables within Oracle Sales Compensation.

You need to specify the following parameters:

Dimension name: e.g. Salespeople, Revenue Class

Header hierarchy name. e.g. Salespeople Hierarchy, Revenue Class Hierarchy or other hierarchies already defined in your system.

Start Period

End Period

Unique Column Name.

Remember you can upload only ONE dimension hierarchy at a time using the concurrent manager procedure.

To download hierarchy

Run the Concurrent Manager procedure called DOWNLOAD HIERARCHY to transfer data from the appropriate dimension hierarchy tables within Oracle Sales Compensation to the CN_HIERARCHIES_API table.

You need to specify the following parameters.

Dimension name: e.g. salespeople, revenue class

Hierarchy name. e.g. Salespeople Hierarchy, Revenue Class Hierarchy or other hierarchies already defined in your system.

Start Period

End Period

Unique Column Name.

Contents

Index

Navigation

Library