Scope of purge planning data

The PURGE_PLANNING_DATA program determines the scope as follows:
  • If the TABLE_NAME parameter is specified, only the planning data in the specified table is deleted.
  • If the TABLE_GROUP_NAME parameter is specified, the planning data in all tables of the specified table group are deleted in the ascending order of processing_sequence. If processing_sequence on table group tables is not defined, the default_processing_sequence defined on SZ_REG_TABLE is used.
  • If the TABLE_AREA parameter is specified, the planning data in all tables of the specified table area are deleted in the ascending order of default_processing_sequence.
  • If the TABLE_NAME, TABLE_GROUP_NAME, or TABLE_AREA parameter is not specified, the planning data in all the SZ_REG_TABLE tables are deleted in the ascending order of default_processing_sequence.
  • If the INCLUDE_XREF_TABLES parameter is not specified or if this parameter is set to Off, the planning data in the SZ_REG_TABLE tables with the prefix SC_XREF or SI_XREF are not deleted.
  • If the PURGE_SCOPE parameter is not specified, only the tables specified by TABLE_NAME, TABLE_GROUP_NAME, or TABLE_AREA are considered. The parameter definition determines the tables to be deleted.

The PURGE_PLANNING_DATA program follows these processing sequences when more than one table is included in the scope:

  • Creates the dependency tree for the tables in scope using the FK-references between the tables.
  • Determines this processing sequence:
    If TABLE_GROUP_NAME is specified  
    If processing_sequence is defined for all tables in the specified table group, then the processing_sequence from the table group is used in ascending order   
Else the tables in scope are processed using the generated dependency tree: Tables with no dependency or tables depending only on already processed tables are processed first

The PURGE_PLANNING_DATA program deletes planning data in each <current table> in scope as follows:

  • Logs new task log record for the current table in scope. This uses the task_name: <current table> as the table name in the task log. For example, SC_ITEM.
  • Create an SQL DELETE statement on the <current table> if the VERSIONS_TO_KEEP and the DAYS_TO_KEEP parameters are not specified using the SOURCE parameter as follows:
    Prepare the base delete statement  

If [<current table> is a Core table and (PURGE_SCOPE is 0 or 1)] or 
<current table> is not a Core table 
then 
<sql> = DELETE FROM <current table>  

Example:  
DELETE FROM SC_ITEM           

If SOURCE is specified, appends a where condition to the delete statement  
WHERE source = <SOURCE>  
Effective statement: 
<sql> = DELETE FROM <current table> WHERE source = <SOURCE> 

Example:  
DELETE FROM SC_ITEM WHERE source = 'nlbavwm3e86.infor.com:782';       

If <current table> is a Core table, then prepares delete statements to purge 
profile, hierarchy and extension records as follows:  

<sql profile> = 
DELETE FROM <current table>_P 
WHERE id IN (SELECT id FROM <current table> WHERE source = <SOURCE>)  

<sql hierarchy> = 
DELETE FROM <current table>_H 
WHERE id IN (SELECT id FROM <current table> WHERE source = <SOURCE>)    

<sql extension> = 
if PURGE_SCOPE is 0 or 2
DELETE FROM <current extension table>  
and if SOURCE is specified 
DELETE FROM <current extension table> WHERE source = <SOURCE>     

Example:  
DELETE FROM SC_EXT_ITEM 
or
DELETE FROM SC_EXT_ITEM WHERE source = 'nlbavwm3e86.infor.com:782'  

Executes the generated SQL DELETE statements for each <current table>, for Core tables in the order of 
    purge profile records -> purge hierarchy records -> purge table -> purge extension records
  • If the VERSIONS_TO_KEEP parameter is specified:
    • Determines the primary key columns of the <current table> from the SZ_REG_CONSTRAINT table. The <VERSION_COLUMN> is already included in the columns of the PK constraint for the <current table>.
    • Identifies the last <VERSIONS_TO_KEEP> records using the PK-constraint and the specified <VERSION_COLUMN>.
    • Identifies the records for the specified source if the SOURCE parameter is specified.
    • Identifies all records if the SOURCE parameter is not specified.
    • Includes PK information for each record in the list.
    • Deletes all records from <current table> where PK is not in the list, for Core tables in the order of purge profile records -> purge hierarchy records -> purge table.
  • If the DAYS_TO_KEEP parameter is specified:
    • Determines the latest date using max (<DATE_COLUMN>) from the <current table>.
    • Identifies older records to purge using identified latest date and the specified <DAYS_TO_KEEP>. For example: <DATE_COLUMN> less than {max(<DATE_COLUMN>) - <DAYS_TO_KEEP>}.
    • Identifies the records for the specified source if the SOURCE parameter is specified.
    • Identifies all records if the SOURCE parameter is not specified.
    • Deletes all records from <current table> where <DATE_COLUMN> less than {max(<DATE_COLUMN>) - <DAYS_TO_KEEP>}, for Core tables in the order of purge profile records -> purge hierarchy records -> purge table.
  • If the VERSIONS_TO_KEEP and DAYS_TO_KEEP parameters are specified, the records are deleted.
  • Commits the changes if all SQL statements for the <current table> are successfully executed.
  • Completes the task log record with the actual status.