Orphan Record Deletion

The product supports a data correction method to delete orphan records from a given table. Orphan records are child table records where the parent record no longer exists.

The following points describe the functionality at a high level:

  • A request is created to capture the table from which the orphan records are to be deleted. The assumption is that prior investigation has identified the target table.
  • Back end logic generates the SQL to delete the orphan records and provides counts of the rows to be deleted.
  • When the request is submitted, a batch process is started that will run the SQL in background. Backups are taken prior to performing the deletion.
  • When the request is completed, a separate request may be created that cleans up the backup tables from the database.

This functionality is supported by configuration and logic supplied in the base business objects Orphan Record Deletion (F1-OrphanRecordDeletion) and Orphan Record Deletion Type (F1-OrphanRecDelType). The following sections describe key steps in the request lifecycle.

Capturing the Deletion Details

The base product provides a business service F1-SelectOrphanRecordsSQL that constructs the SQL to select orphan records from a target table. The business service returns additional SQL statements for the target's child and grandchild tables, if applicable. The product also provides the business service F1-CountRecords which counts the orphan records based on the SQL generated by F1-SelectOrphanRecordsSQL.

The Orphan Record Deletion BO invokes these business services in a post-processing algorithm and records the details on the request record.

Approving and Submitting the Request

Once a deletion request is captured, there are several key steps to perform.

  • Deletion request records are created in Pending state so that the SQL statements and record counts can be reviewed before proceeding.

  • If the deletion details are correct, the request can be transitioned to Ready for Approval. A to do entry is created to notify potential approvers. The request must be approved by someone other than the user who created the record. The notification to do entry type and role are configured on the Orphan Record Deletion Type.

  • The request must be approved before it can be submitted for processing. If the Orphan Record Deletion Type indicates that record counts must be verified before deletion, a warning will be issued at approval time if there is a record mismatch.
  • Once the request is in the Approved state, it can be submitted. The Submitted state is configured to be processed by a batch monitor so that record deletion is carried out in background. The monitor batch job is submitted automatically. When record deletion is complete, the request transitions to the Processed state so that the results can be reviewed.
  • If the deletion could not be completed successfully, the request will enter the Error state and a to do entry is created. The error to do entry type and role are configured on the Orphan Record Deletion Type.

Completion and Clean Up

The record deletion process includes steps to take backups of the target tables in case the results are not as expected. A separate request may be created to handle deletion of unwanted backup tables at an appropriate time. If the Orphan Record Deletion Type specifies a clean up request type, a request record of that type is created when the deletion request enters the Complete state. The clean up functionality is supported by configuration and logic supplied in the base business objects Backup Table Cleanup (F1-BackupTableCleanup) and Backup Table Cleanup Type (F1-BackupTableCleanupType).