4.2.4 Executing the Hierarchy Flattening Process

You can execute the function from the Operations Framework Module of OFSAAI. Define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI. For more information on defining a new Batch, see the How to Define a Batch section.

To define a new task for a Batch definition, follow these steps:

1. Select the checkbox adjacent to the newly created Batch Name in the Batch Maintenance window.
2. Click Add (+) from the Task Details grid. The Task Definition window is displayed.
3. Enter the Task ID and Description.
4. Select Transform Data from the Transform Data drop-down list.
5. Select the following from the Dynamic Parameters drop-down list:
   • Datastore Type: Select the appropriate datastore type from the drop-down list:
   • Datastore Name: Select the appropriate datastore name from the drop-down list.
   • IP Address: Select the IP address from the drop-down list.
   • Rule Name: Select BATCH_HIERTRANSFORMATION from the drop-down list of available transformations. (This is a seeded Data Transformation procedure installed as part of the ALMBI Application. If you do not see this procedure in the list, contact Oracle Support).
   • Parameter List: These are comma-separated values of Dimension ID and Hierarchy ID. Following are the available Dimension ID values and Hierarchy ID values:
     • Dimension ID Values
     • ORG_UNIT_ID = 1
     • GL_ACCOUNT_ID = 2
     • COMMON_COA_ID = 3
     • PRODUCT_ID = 4
6. If you are using a user-defined Dimension, execute the following query in the database to find the value, and use the value in the Dimension ID column to process the dimension name and description:
   SELECT B.DIMENSION_ID, T.DIMENSION_NAME, T.DESCRIPTION FROM REV_DIMENSIONS_B B INNER JOIN REV_DIMENSIONS_TL T ON B.DIMENSION_ID = T.DIMENSION_ID AND T.DIMENSION_NAME LIKE '<DIMENSION NAME>'
7. Replace the tag <DIMENSION NAME> in this query with the Dimension Name you find in the UI (Navigate to OFS ALM Home page, select Common Object Maintenance, select Master Maintenance, and then select Dimension Management). This is the Dimension on which the Hierarchy you want to flatten is configured. You must create separate Batches for each Dimension.
   If all the Hierarchies belonging to a Dimension are to be processed, then provide null (in lower case) as the parameter value. Otherwise, provide the System Identifier of the Hierarchy that needs to be transformed.
   For example, you can find the Hierarchy ID through the Hierarchy User Interface at the bottom of the window, as depicted in the following figure.

   Figure 4-2 Hierarchy ID through the Hierarchy User Interface

   
   

   

   
   
   You can also execute the following query to find the unique system identifier for a specific Hierarchy:
   SELECT B.OBJECT_DEFINITION_ID, SHORT_DESC, LONG_DESC FROM FSI_M_OBJECT_DEFINITION_B B INNER JOIN FSI_M_OBJECT_DEFINITION_TL T ON B.OBJECT_DEFINITION_ID = T.OBJECT_DEFINITION_ID AND B.ID_TYPE =<ID_TYPE>
   Use the value in the HIERARCHY_ID column as the parameter for the hierarchy to be processed. <ID_TYPE> represents the dimension number to which a particular hierarchy belongs.
   For example, if all the Hierarchies for the GL Account Dimension must be processed, the parameter list should be given as follows:
   '2', null, where '2' is the Dimension ID for the seeded Dimension GL Account.
   If a particular Hierarchy with code 1000018112 needs to be processed, the parameter list should be given as follows:
   '2', '1000018112'
8. Click Save. The Task definition is saved for the selected Batch.
9. Execute the Batch.
   You can execute a Batch definition from the Batch Execution window of the OFSAAI Operations module.

   Note:

   This process can also be run using the Simplified Batch user interface. In the optional parameters field within the Simplified Batch window, specify the parameters mentioned earlier.
   Hierarchy Transformation can also be directly executed on the database through SQLPLUS.
   • Function Name: REV_BATCHHIERFLATTEN
   • Parameters: BATCH_RUN_ID, MIS_DATE, PDIMENSIONID, and PHIERARCHYID.
   • Sample Parameter Values: 'Batch1','20091231','2', and '1000018112'.

   Note:

   This process can also be run using the Simplified Batch User Interface. In the optional parameters field within the Simplified Batch window, specify the parameters mentioned above.
   The first paragraph should contain the command overview or a short description of the reference information.