Program logic

The EXECUTE_HIERARCHY_GENERATE program generate hierarchy data for each hierarchy in scope, using this logic:

  • Logs a new task record for the current hierarchy using this formula: 
    task_name = <current hierarchy ID> - <current hierarchy name>
  • Identifies the level definitions for the current hierarchy and sort the definitions in the ascending order based on level_number.
  • Generates the hierarchy query. The program prepares the query starting from the base level, extending the query for each level above, and ending with the implicit top level as follows:
    • Prepares the FROM part of the query:
      • If the <level_table_name> field is populated, adds “<level_table_name> as table”.
      • If the <level_query> field is populated, verifies if the <level_query> field contains reference to a "deleted_flag" column and adds “<level_query> value as table”. Else, adds “<SELECT t.*, 'N' as deleted_flag FROM (<level_query>) t > as table”.
        Note: The program uses level_<level_number> as table alias.
      • Joins each table to the next higher-level table using LEFT JOIN (outer join). In the join, the program uses cycle_period_id and scenario_id as default join columns.
      • The program uses <level_value>/<level_value_column>/<level_value_expression> = <parent_match_value>/<parent_match_value_column>/>=<parent_source_value_expression>. Only the populated element from each side is included.
    • Prepares the SELECT part of the query:
      • Includes columns for each level value expected in the SC_HIERARCHY table. For example, base_level_value, parent_1_value, deleted_flag, etc.
      • Sets the deleted_flag of the hierarchy based on the deleted_flag of the base_level.
      • Uses <level_value>, <level_value_column>, or <level_value_expression> to select the values for each level.
      • Uses <level_descr>, <level_descr_column>, or <level_descr_expression> to select the descriptions for each level.
      • Uses <level_ae_attr> to select accounting entity values for each level.
      • Uses <level_amount_attr_1-5> to select amount attributes for each level.
      • Uses <level_attr_1-10> to select free-form attributes for each level.
      • Uses <level_orig_amount_attr_1-5> to select amount attributes in original currency for each level.
      • Uses <parent_default_value> to select the default values for each level.
        Note: This logic is considered if one of this condition exists:
        • The value of the current level is NULL.
        • The value below the current level is NULL (from the outer joins).
        • The record in the current and lower level is deleted (deleted_flag='Y').
      • Includes the hierarchy_id and hierarchy_source values from the current hierarchy definition.
      • Uses level_<level_number> as alias when <level_value_column> or <level_descr_column> is specified.
    • Prepares the WHERE part of the query; if the SOURCE parameter is specified, includes <level_0>.source = <SOURCE> to the WHERE clause of the zero-level query.
  • Populates the SC_HIERARCHY table using the generated query:
    • Deletes existing hierarchy data for the SC_HIERARCHY table using DELETE FROM SC_HIERARCHY WHERE hierarchy_id = <current hierarchy>.
      Note: This also applies to the source filter if the SOURCE parameter is specified. DELETE FROM SC_HIERARCHY WHERE hierarchy_id = <current hierarchy> WHERE source = <SOURCE>.
    • Inserts new hierarchy data using INSERT INTO SC_HIERARCHY <query generated from Where part>.
  • Commits the changes if actions on the current hierarchy are successful.
  • Completes the task log record with the actual status.

The EXECUTE_HIERARCHY_GENERATE program status at completion:

  • Success: When tasks are completed with the Success status, the end status is set to Success.
  • Warning: When any tasks are completed with the Warning status and no tasks are completed with the Failure status, the end status is set to Warning.
  • Failure: When any task is completed with the Failure status, the end status is set to Failure.
    Note: The program continues to run, and all hierarchies are processed, even if a task fails.