1. User Guide
  2. Object Management
  3. Adding Dimension Tables and Key Dimension (Leaf) Registration
  4. Leaf Registration
  5. Executing Leaf Registration Procedure

3.2.5.1 Executing Leaf Registration Procedure

You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from Batch Maintenance window within OFSAAI framework.

To execute the Leaf Registration, follow these steps:
  1. To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner. The function requires 19 parameters. The syntax for calling the procedure is:
    function rev_leaf_registration(batch_run_id varchar2, mis_date varchar2, memDataType varchar2, dimName varchar2, description varchar2, memberBTableName varchar2, memberTLTableName varchar2, hierarchyTableName varchar2, attributeTableName varchar2, memberCol varchar2, memberDispCodeCol varchar2, memberNameCol varchar2, memberDescCol varchar2, dimTypeCode varchar2, simpleDimFlag varchar2, keyDimFlag char, writeFlag varchar2, catalogTableType char, flattenedTableName in varchar2, membercodecol in varchar2 )
    • batch_run_id : any string to identify the executed batch.
    • mis_date : in the format YYYYMMDD.
    • memDataType : member data type of Dimension as in NUMBER, VARCHAR2, CHAR.
    • dimName : name of the dimension to be added (less than 21 chars).
    • description : description of the dimension (less than 255 chars).
    • memberBTableName : Member Base Table Name input as either null or a value with suffix '_CD' or '_B'.
    • memberTLTableName : Member TL Table Name input as either null or name of the table.
    • hierarchyTableName : Hierarchy Table Name input as either null or name of the table.
    • attributeTableName : Attribute Table Name input as either null or name of the table.
    • memberCol : Member Column Name input as either null or name of the column.
    • memberDispCodeCol : Member Display Code Column Name input as either null or name of the column. For simple dimensions, enter the same field as the memberCol. Do not user display column for simple dimensions.
    • memberNameCol : Member Name Column input as either null or name of the column.
    • memberDescCol : Member Description Column input as either null or name of the column.
    • dimTypeCode : Code for the dimension Type as in 'PROD for product type', 'ORGN for Organizational Unit', 'CCOA for Common Chart of Accounts', 'FINELE for Financial Element', 'GL for General Ledger Account', 'OTHER for any other type'.
    • All user defined dimensions will have DIMENSION_TYPE_CODE as 'OTHER'. User defined dimensions which are product related will have DIMENSION_TYPE_CODE as 'PROD'.
    • simpleDimFlag : 'Y' or 'N' to determine Simple Dimension.

      Simple dimensions are created to store CODE and Descriptions. These tables are used by the User Interfaces to list values in drop downs / radio buttons, and so on. Simple dimensions are not reverse populated.

      Example: Country, Currencies, Customer Type.

    • keyDimFlag : 'Y' or 'N' to determine Key Dimension.

      Key dimensions are dimensions which get reverse populated to the legacy tables.

      Example: Product, Org Unit, General Ledger.

    • writeFlag : 'Y' or 'N' to determine whether Dimension should appear in drop down list in Dimension Management > Members.
    • catalogTableType : 'L', 'B', or 'I' to determine table type for key dimensions.
    • For a Simple Dimension, this value should be set to Null.
    • flattenedTableName : Flattened Table Name input as either null or name of the table.
    • membercodecol: Alphanumeric Code column. Populates the MEMBER_CODE_COLUMN column in REV_DIMENSIONS_B. The value provided should be a valid code column from the relevant DIM_<DIMENSION>_B (key dimension) or FSI_<DIM>_CD (simple dimension) table. For simple dimensions use the display code column.

    Example for Key Dimension:

    Declare num number; Begin num := rev_leaf_registration('BATCH_NO_01', '20101216', 'NUMBER', 'SIMPLE DIMENSION', 'SIMPLE DIMENSION DESC', 'FSI_DIM_SIMPLE_CD', 'FSI_DIM_SIMPLE_MLS', 'null', 'null', 'SIMPLE_CD', 'SIMPLE_CD', 'SIMPLE_NAME_Dim', 'SIMPLE_DESCRIPTION', 'OTHER', 'Y', 'N', 'Y', 'B', 'FLATTEN_PROD_TABLE', 'SIMPLE_DISPLAY_CODE'); End; Example for Simple Dimension: Declare num number; Begin num := rev_leaf_registration('BATCH_NO_01', '20101216', 'NUMBER', 'SIMPLE DIMENSION', 'SIMPLE DIMENSION DESC', 'FSI_DIM_SIMPLE_CD', 'FSI_DIM_SIMPLE_MLS', 'null', 'null', 'SIMPLE_CD', 'SIMPLE_DISPLAY_CODE', 'SIMPLE_NAME_Dim', 'SIMPLE_DESCRIPTION', 'OTHER', 'Y', 'N', 'Y', 'B', 'FLATTEN_PROD_TABLE'); End;

  2. To execute the procedure from OFSAAI Batch Management, create a new Batch with the Task as TRANSFORM DATA and specify the following parameters for the task:
    • Datastore Type: Select appropriate datastore from list
    • Datastore Name: Select appropriate name from the list
    • IP address: Select the IP address from the list
    • Rule Name: batch_leaf_registration
    • Parameter List: Member Data type , Dimension Name, Dimension Description, Member Base Table Name, Member Translation Table Name, Hierarchy Table Name, Attribute Table Name, Member Column , Member Display Code Column, Member Name Column, Member Description Column , Dimension Type Code , Simple Dimension Flag , Key Dimension Flag , writeFlag, Catalog Table Type , Flatten Table Name