Purge plan

The PURGE_PLAN program considers these input parameters:

C2*

The SCV database connection details.

JOBID

An external job ID. This parameter is used when the PURGE_PLAN program is executed as part of another job in SCV.

Note: If this parameter is specified:

• An existing entry must be available in the SZ_EXECUTION_LOG table with the Started status. An error message is logged, and the execution is stopped if an entry with the Started status is not available for the specified execution_id value in the SZ_EXECUTION_LOG table.

• The PURGE_PLAN program uses the specified value as the execution_id value without creating a new execution_id.

PLAN_TABLE_NAME

The name of the plan table to be deleted.

Note: If this parameter is specified:

• An exisitng entry must be available in the SZ_REG_TABLE table where table_area is set to Core and plan_table_flag is set to True. An error message is logged, and the execution is stopped if the Core plan table is not available with the specified <PLAN_TABLE_NAME> value in the SZ_REG_TABLE table.
• The specified table must be a header plan table. An error is logged, and the execution is stopped if the specified table is a child plan table (that is, an FK-type constraint exists on <PLAN_TABLE_NAME> where parent_reference_flag = ‘Y’).

The PURGE_PLAN program deletes plan results in the specified <PLAN_TABLE_NAME> as follows:

• Logs new task log record for the PLAN_TABLE_NAME parameter. This uses the task_name format: <PLAN_TABLE_NAME> as the table name in the task log. For example, SC_ITEM.
• Validates the input parameters as follows:
  • Verifies if the columns corresponding to each specified input parameter are available in the specified <PLAN_TABLE_NAME>.
  • Logs a Warning message for each input parameter or column that is not available in the table.
• Creates an SQL DELETE statement on the <PLAN_TABLE_NAME> using the input parameters created as follows:

  1. Prepares the base purge condition  
     <condition> = WHERE 1=1  
  
  2. Appends a condition for <SOURCE>  
     <condition> = <condition> + AND source = <SOURCE>  
  
  3. If specified, appends a condition for <CYCLE_PERIOD>  
     <condition> = <condition> + AND cycle_period_id = <CYCLE_PERIOD>  
  
  4. If specified, appends a condition for <SCENARIO>  
     <condition> = <condition> + AND scenario_id = <SCENARIO>  
  
  5. If specified, appends a condition for <PLAN_NAME>  
     <condition> = <condition> + AND plan_name = <PLAN_NAME>  
  
  6. If specified, appends a condition for <PLAN_CATEGORY>  
     <condition> = <condition> + AND plan_category = <PLAN_CATEGORY>  
  
  7. If specified, appends a condition for <PLAN_TYPE>  
     <condition> = <condition> + AND plan_type = <PLAN_TYPE>  
  
  8. If specified, appends a condition for <PLAN_VERSION>  
     <condition> = <condition> + AND plan_version = <PLAN_VERSION>  
  
  9. If specified, appends a condition for <PLAN_SUBTYPE>  
     <condition> = <condition> + AND plan_subtype = <PLAN_SUBTYPE>  
  
  10. If specified, appends a condition for <PLAN_VALUE_STATUS>  
     <condition> = <condition> + AND plan_value_status = <PLAN_VALUE_STATUS>  
  
  11. If specified, appends a condition for <PLAN_VALUE_TYPE>  
     <condition> = <condition> + AND plan_value_type = <PLAN_VALUE_TYPE>  
  
  12. If specified, appends a condition for <EXPORT_STATUS>  
     <condition> = <condition> + AND export_status = <EXPORT_STATUS>  
  
  13. If specified, appends a condition for <SCP_ORDER_STATUS>  
     <condition> = <condition> + AND scp_order_status = <SCP_ORDER_STATUS>  
  
  14. Find the Export Horizon Key column defined on <PLAN_TABLE_NAME>  
     <EK constraint> = Entry in SZ_REG_CONSTRAINT where table_name = <PLAN_TABLE_NAME> and type = 'EK'  
     <EK constraint column> = Entry in SZ_REG_CONSTRAINT_COLUMN where constraint_id = <EK constraint> and column_sequence = 1  
  
  15. If specified, appends a condition for <PERIOD_START>  
     If no EK constraints are defined on <PLAN_TABLE_NAME> or no constraint columns are found, then log error and return.  
  
     Else:      
     <condition> = <condition> + AND <EK constraint column> >= <PERIOD_START>  
  
  16. If specified, appends a condition for <PERIOD_END>  
     If no EK constraints are defined on <PLAN_TABLE_NAME> or no constraint columns are found, then log error and return.  
  
     Else:      
     <condition> = <condition> + AND <EK constraint column> <= <PERIOD_END>  
  
  17. If <PLAN_TABLE_NAME> has child tables, then prepares and executes delete statements to purge each child table.  
     If <child table> has an FK-type constraint where parent_reference_flag = "Y" and where the referenced constraint is defined on <PLAN_TABLE_NAME>, then <PLAN_TABLE_NAME> is the parent of <child table>. Records in <child table> must be purged first.  
  
     For each <child table>:  
     a. Purge profile records  
        DELETE FROM <child table>_P WHERE id IN  
          (SELECT id FROM <child table> WHERE EXISTS  
              (SELECT 'X'  
               FROM <PLAN_TABLE_NAME>  
               WHERE <child table>.<FK-column(n)> = <PLAN_TABLE_NAME>.<FK-column(n)>  
               AND <condition>  
              )  
          )  
     b. Purge hierarchy records  
        DELETE FROM <child table>_H WHERE id IN  
          (SELECT id FROM <child table> WHERE EXISTS  
              (SELECT 'X'  
               FROM <PLAN_TABLE_NAME>  
               WHERE <child table>.<FK-column(n)> = <PLAN_TABLE_NAME>.<FK-column(n)>  
               AND <condition>  
              )  
          )  
     c. Purge child table  
        DELETE FROM <child table> WHERE EXISTS  
          (SELECT 'X'  
           FROM <PLAN_TABLE_NAME>  
           WHERE <child table>.<FK-column(n)> = <PLAN_TABLE_NAME>.<FK-column(n)>  
           AND <condition>  
          )  
  18. Prepares and executes delete statements to purge <PLAN_TABLE_NAME>.  
     a. Purge profile records  
        DELETE FROM <PLAN_TABLE_NAME>_P WHERE id IN (SELECT id FROM <PLAN_TABLE_NAME> <condition>)  
     b. Purge hierarchy records  
        DELETE FROM <PLAN_TABLE_NAME>_H WHERE id IN (SELECT id FROM <PLAN_TABLE_NAME> <condition>)  
     c. Purge <PLAN_TABLE_NAME>  
        DELETE FROM <PLAN_TABLE_NAME> <condition>  
  

• Commits the changes if the SQL statements for <child table> and <PLAN_TABLE_NAME> are executed successfully.
• Completes the task log record with the actual status.

This table lists the default values for the input parameters of the PURGE_PLAN program: