Program logic

The EXECUTE_HIERARCHY_PREPARE program generate hierarchy links for each hierarchy-table link definition in scope, using this logic:

Logs a new task log record for the current hierarchy using this formula:

task_name = <current hierarchy ID> - <current hierarchy name> - <current table name>

Generates the hierarchy link query in this sequence:
- Prepares the FROM part of the query: The table_hier_value_query value is added as an inline table. Else, the SZ_HIERARCHY_TABLE.table_name value is added.
- Prepares the SELECT part of the query: The hierarchy_id and type values are included from the current hierarchy definition. Possible conditions:
  - If the table_hier_value_query field is specified, considers the base_level_value column from the table_hier_value_query table, to select the hierarchy base level value for the current table.
  - If the table_hier_value_query field is not specified, considers the <table_hier_value>, <table_hier_value_column>, or <table_heir_value_expression> parameter, to select the hierarchy base level value for the current table depending on which field is populated.
  - If the table_hier_value_query field is specified, considers the id column from the table_hier_value_query table, to select the ID value for the current table.
  - If the table_hier_value_query field is not specified, considers the id column from the table_name table, to select the ID value for the current table.
- Prepares the WHERE part of the query. Possible scenarios:
  - If the <table_heir_value>, <table_heir_value_column> or <table_heir_value_expression> field is specified, includes this condition:
```
cycle_period_id = 'Base' AND scenario_id = 'Base' 
```
  - If the Source parameter is specified, includes this condition:
```
cycle_period_id = 'Base' AND scenario_id = 'Base' AND source = <SOURCE>
```

Populates the <current table>_H table with the generated query using this logic:

Deletes existing hierarchy link data using the DELETE FROM <current table>_H WHERE hierarchy_id = <current hierarchy> query.

If the Source parameter is specified, the program uses this query:

DELETE FROM <current table>_H 
WHERE hierarchy_id = <current hierarchy> 
    AND (NOT EXISTS (SELECT id FROM <current table> WHERE   <current table>.id = <current table>_H.id)
        OR
             EXISTS (SELECT id FROM <current table> WHERE <current table>.id = <current table>_H.id    
                                                    AND <current table>.source = <SOURCE> 
                     )
 )

Inserts new hierarchy link data using INSERT INTO <current table>_H <hierarchy query generated>.

Commits the changes if the actions on the current hierarchy and table are successfully executed.
Logs the task record with the actual status.

The EXECUTE_HIERARCHY_PREPARE program status at completion:

Success: When tasks are completed with the Success status, the end status is set to Success.
Warning: When any task is 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 table hierarchies are processed, even if a task fails.