Configuration Guide

10.3 HotFix 2

Back Next

Consensus Delete with End Date Code Example

Your organization needs to detect hard deletes for a staging table called C_CUSTOMER_STG. They want to perform a consensus delete of the hard-deleted records in the base object and assign an end date to the deleted records.

You need to insert the metadata that you need to detect hard deletes into the hard delete detection table for the C_CUSTOMER_STG staging table. When you detect hard deletes, the

MDM Hub

must provide a delete flag in addition to an end date for the deleted record. Also, the process must verify that all the associated cross-reference records are flagged as deleted before the

MDM Hub

flags the base object records as deleted. The name of the delete flag column is DEL_CODE and the name of the end date column in the staging table is END_DATE.

The following sample code inserts metadata into the hard delete detection table for a consensus delete with an end date for the deleted records:

On Oracle.

INSERT into c_repos_ext_hard_del_detect
	(rowid_table, hdd_enabled, hdd_type, delete_flag_column_name, delete_flag_val_active, delete_flag_val_inactive, delete_flag_val_partial, has_end_date, end_date_column_name, end_date_val)
SELECT rowid_table, 
	1 AS hdd_enabled, 
	'CONSENSUS' AS hdd_type,
	'DEL_CODE' AS delete_flag_column_name, 
	'A' AS delete_flag_val_active, 
	'I' AS delete_flag_val_inactive,
	'P' AS delete_flag_val_partial, 
	1 AS has_end_date, 
	'END_DATE' AS end_date_column_name,
	'SYSDATE' AS end_date_val
FROM  c_repos_table
WHERE table_name = 'C_CUSTOMER_STG;

On Microsoft SQL Server.

INSERT INTO [dbo].[C_REPOS_EXT_HARD_DEL_DETECT]
           ([ROWID_TABLE], [HDD_ENABLED], [HDD_TYPE], [DELETE_FLAG_COLUMN_NAME], [DELETE_FLAG_VAL_ACTIVE], [DELETE_FLAG_VAL_INACTIVE], [DELETE_FLAG_VAL_PARTIAL], [HAS_END_DATE], [END_DATE_COLUMN_NAME], [END_DATE_VAL],)
SELECT [ROWID_TABLE]
           ,1
           ,'CONSENSUS'
           ,'DEL_CODE'
           ,'A'
           ,'I'
											,'P'
											,1
           ,'END_DATE'
           ,'SYSDATE'
FROM [dbo].[C_REPOS_TABLE] 
WHERE table_name = 'C_CUSTOMER_STG';
GO

Rename Saved Search

Table of Contents

Configuration Guide

Configuration Guide

Consensus Delete with End Date Code Example

Consensus Delete with End Date Code Example