5.15.1 Executing the Hierarchy Flattening Transformation

You can execute this procedure from SQL Plus/PLSQL/Batch Maintenance window within OFSAAI framework: Using SQL Plus/PLSQL and Using OFSAAI Batch Maintenance

1. Using SQL Plus/PLSQL, execute below:
   function rev_batchHierFlatten(batch_run_id varchar2, mis_date varchar2, pDimensionId varchar2, pHierarchyId varchar2, )

   • Function Name: rev_batchHierFlatten
   • Parameters: batch_run_id, mis_date, pDimensionId, pHierarchyId
     • batch_run_id: It is the batch run id. Batch Run ID value is passed from the Batch execution UI. Therefore, it is not required to define it as a parameter value in Batch Maintenance.
     • mis_date: This parameter value is passed from the Batch execution UI. Therefore, it is not required to define it as a parameter value in Batch Maintenance. Follow the date format, YYYYMMDD
     • pDimensionId- Enter the Dimension id . To find dimension id, execute the following query in database to find the value and use the value in dimension id column for the dimension name / description to be processed:

       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>'

       Replace <dimension name> in the preceding query with the Dimension Name you find in the UI (Maintenance > Dimension Management) for the dimension on which the Hierarchy you want to flatten is configured.
     • pHierarchyId: Enter Hierarchy id. If all the hierarchies belonging to a dimension are to be processed then, provide NULL as the parameter value. Else, provide the System Identifier of the hierarchy that needs to be transformed.

       Execute the following query in database if only a single hierarchy is to be processed and use the value in hierarchy_id column as parameter for the hierarchy to be processed:

       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 = 5

     • If all the hierarchies for GL Account dimension must be processed, the parameter list should be given as follows (where '2' is the dimension id for the seeded dimension GL Account):

       '2',null

     • If a particular hierarchy with code 1000018112 must be processed (you can obtain this code by executing the preceding query in the database), the parameter list should be given as follows:

       '2', '1000018112'

       SQL ExampleSQL> var fn_return_val number;SQL> execute :fn_return_val:= rev_batchHierFlatten ('Batch1 ', '20091231 ', '2 ', '1000018112');SQL> print fn_return_val

       PLSQL Example

       DECLARE fn_return_val number := null;BEGIN fn_return_val := rev_batchHierFlatten('Batch1', '20091231', '2', 1000018112'); IF fn_return_val = 1 THEN Dbms_output.put_line('Execution status of batchHierFlatten is' ||fn_return_val || ' --Successful'); ELSIF fn_return_val = 0 THEN Dbms_output.put_line('Execution status of batchHierFlatten is' ||fn_return_val || ' --FAILURE'); END IF;EXCEPTION WHEN OTHERS THEN Dbms_output.put_line('Execution status of batchHierFlatten is' || SQLCODE || '-' || SQLERRM); END;

   On successful execution of rev_batchHierFlatten function in Database, value returned will be 1 or 0. 1 indicates successful execution and 0 indicates failure in execution. This function will be present in Atomic Schema.
2. To execute the procedure from OFSAAI Batch Maintenance, run the following batch and specify the following parameters:
   • Datastore Type: Select appropriate datastore from the list
   • Datastore Name: Select appropriate name from the list
   • IP address: Select the IP address from the list
   • Rule Name: batch_hierTransformation
   • Parameter List: Dimension ID, Hierarchy ID