/****************************************************************************************************************
-- SQL Script : ES_Purge_ContactBase.sql
--****************************************************************************************************************
-- Description :
--------Delete all contact records in the ContactBase table that are not associated with an active policy or quote
--------and no associated with any customer validation and interaction note data already stored in CRM
--------Inactive contacts are defined as contact records satisfying all of the following conditions:
--------1. Contacts not associated with any active policy (eas_policy)
--------2. Contacts not associated with any active quote (eas_quote)
--------3. Contact records that are not associated with any records in the following tables:
--------a. eas_customervalidationBase
--------b. eas_interactionBase
--------The physical delete needs to be cascaded to the following entities based on the foreign key constraints.
--------1. ContactBase
--------2. eas_policybase
--------3. eas_quotebase
--------4. eas_policycontactbase
--------5. eas_contact_policy_relationship
--------6. eas_quotecontactbase
--------7. eas_contact_quote_relationship
--------8. CustomerAddressBase
---PID : 15-CLM-174
-- Developed : Babji Reddy
--created_date : 03/14/2016
-- Reviewed by :
-- Modified By :
-- Release : 16-REL-03
--********************************************************************************************************************/
--USE ENV_CRM
--GO
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=object_id(N'ES_Purge_ContactBase') AND TYPE IN(N'P',N'PC'))
BEGIN
DROP PROC dbo.ES_Purge_ContactBase
END
GO
CREATE PROCEDURE dbo.ES_Purge_ContactBase
(
@number_of_purgerecords INT=NULL
)
AS
BEGIN
BEGIN TRY;
SET NOCOUNT ON;
DECLARE @message VARCHAR(MAX)=''
DECLARE @CustomerAddressBase INT=0
DECLARE @eas_contact_quote_relationshipBase INT=0
DECLARE @eas_quotecontactBase INT=0
DECLARE @eas_quoteBase INT=0
DECLARE @eas_policycontactBase INT=0
DECLARE @eas_contact_policy_relationshipBase INT=0
DECLARE @eas_policyBase INT=0
DECLARE @contactbase INT=0
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE dbo.Temp_ContactBase_main
END
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'ISUserTable')=1)
BEGIN
DROP TABLE dbo.Temp_ContactBase_sub
END
CREATE TABLE dbo.Temp_ContactBase_main
(
ContactId uniqueidentifier NOT NULL,
CONSTRAINT PK_Temp_ContactBase_main PRIMARY KEY CLUSTERED (ContactId)
)
CREATE TABLE Temp_ContactBase_sub
(
ContactId uniqueidentifier NOT NULL,
CONSTRAINT PK_Temp_ContactBase_sub PRIMARY KEY CLUSTERED (ContactId)
)
IF @number_of_purgerecords IS NULL
BEGIN
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase (NOLOCK) where statecode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_interactionBase (NOLOCk)
UNION
SELECt eas_contactid AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECt eas_PolicyHolderNameId AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECT eas_primaryinsured FROM eas_quotebase (NOLOCK) WHERE statecode = 0
UNION
SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT ContactId FROM contactbase (NOLOCK)
EXCEPT
SELECT ContactId FROM Un_wanted_contacts
END
ELSE
BEGIN
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase (NOLOCK) where statecode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_interactionBase (NOLOCk)
UNION
SELECt eas_contactid AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECt eas_PolicyHolderNameId AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECT eas_primaryinsured FROM eas_quotebase (NOLOCK) WHERE statecode = 0
UNION
SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT top (@number_of_purgerecords) ContactId FROM (
SELECT ContactId FROM contactbase (NOLOCK)
EXCEPT
SELECT ContactId FROM Un_wanted_contacts) a
END
INSERT INTO dbo.Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
WHILE EXISTS( SELECT 1 FROM Temp_ContactBase_main)
BEGIN
--CustomerAddressBase
--eas_contact_quote_relationship
--eas_quotecontactbase
--eas_quotebase
--eas_policybase
--eas_policycontactbase
--eas_contact_policy_relationship
--contactBase
SET @message=''
---CustomerAddressBase
DELETE cab
FROM dbo.CustomerAddressBase cab
INNER JOIN dbo.Temp_ContactBase_sub cb WITH(NOLOCk)
ON cab.ParentId=cb.ContactId
SET @CustomerAddressBase=@CustomerAddressBase+@@ROWCOUNT
SET @message=@message+'No of purge records in CustomerAddressBase:'+CAST(@CustomerAddressBase AS VARCHAR(10))
-- eas_contact_quote_relationshipBase
DELETE ecqr
FROM dbo.eas_contact_quote_relationshipBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.ContactId=re_sub.ContactId
SET @eas_contact_quote_relationshipBase=@eas_contact_quote_relationshipBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_quote_relationshipBase:'+CAST(@eas_contact_quote_relationshipBase AS VARCHAR(10))
--eas_quotecontactBase
DELETE ecqr
FROM dbo.eas_quotecontactBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_Contact=re_sub.ContactId
SET @eas_quotecontactBase=@eas_quotecontactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_quotecontactBase:'+CAST(@eas_quotecontactBase AS VARCHAR(10))
--eas_quoteBase
DELETE ecqr
FROM dbo.eas_quoteBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_primaryinsured=re_sub.ContactId
SET @eas_quoteBase=@eas_quoteBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_quoteBase:'+CAST(@eas_quoteBase AS VARCHAR(10))
--eas_policycontactBase
DELETE ecv
FROm dbo.eas_policycontactBase ecv
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecv.eas_contactid=re_sub.ContactId
SET @eas_policycontactBase=@eas_policycontactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_policycontactBase:'+CAST(@eas_policycontactBase AS VARCHAR(10))
---eas_contact_policy_relationship
DELETE ecpr
FROM dbo.eas_contact_policy_relationshipBase ecpr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecpr.contactid=re_sub.ContactId
SET @eas_contact_policy_relationshipBase=@eas_contact_policy_relationshipBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_contact_policy_relationshipBase AS VARCHAR(10))
---eas_policyBase
DELETE ecqr
FROM dbo.eas_policyBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_PolicyHolderId=re_sub.ContactId
SET @eas_policyBase=@eas_policyBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_policyBase AS VARCHAR(10))
--- Contactbase
DELETE ab
FROM dbo.ContactBase ab
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ab.ContactId=re_sub.ContactId
SET @ContactBase=@ContactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number Of Purge Records in ContactBase:'+CAST(@ContactBase AS VARCHAR(10))
DELETE ab
FROM Temp_ContactBase_main ab
INNER JOIN Temp_ContactBase_sub y WITH(NOLOCK)
On ab.ContactId=y.ContactId
TRUNCATE TABLE Temp_ContactBase_sub
INSERT INTO Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
END
INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE Temp_ContactBase_main
END
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE Temp_ContactBase_sub
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = N'Error in SP: dbo.ES_Purge_ContactBase: ' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message+@ErrorMessage
RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END
-- SQL Script : ES_Purge_ContactBase.sql
--****************************************************************************************************************
-- Description :
--------Delete all contact records in the ContactBase table that are not associated with an active policy or quote
--------and no associated with any customer validation and interaction note data already stored in CRM
--------Inactive contacts are defined as contact records satisfying all of the following conditions:
--------1. Contacts not associated with any active policy (eas_policy)
--------2. Contacts not associated with any active quote (eas_quote)
--------3. Contact records that are not associated with any records in the following tables:
--------a. eas_customervalidationBase
--------b. eas_interactionBase
--------The physical delete needs to be cascaded to the following entities based on the foreign key constraints.
--------1. ContactBase
--------2. eas_policybase
--------3. eas_quotebase
--------4. eas_policycontactbase
--------5. eas_contact_policy_relationship
--------6. eas_quotecontactbase
--------7. eas_contact_quote_relationship
--------8. CustomerAddressBase
---PID : 15-CLM-174
-- Developed : Babji Reddy
--created_date : 03/14/2016
-- Reviewed by :
-- Modified By :
-- Release : 16-REL-03
--********************************************************************************************************************/
--USE ENV_CRM
--GO
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=object_id(N'ES_Purge_ContactBase') AND TYPE IN(N'P',N'PC'))
BEGIN
DROP PROC dbo.ES_Purge_ContactBase
END
GO
CREATE PROCEDURE dbo.ES_Purge_ContactBase
(
@number_of_purgerecords INT=NULL
)
AS
BEGIN
BEGIN TRY;
SET NOCOUNT ON;
DECLARE @message VARCHAR(MAX)=''
DECLARE @CustomerAddressBase INT=0
DECLARE @eas_contact_quote_relationshipBase INT=0
DECLARE @eas_quotecontactBase INT=0
DECLARE @eas_quoteBase INT=0
DECLARE @eas_policycontactBase INT=0
DECLARE @eas_contact_policy_relationshipBase INT=0
DECLARE @eas_policyBase INT=0
DECLARE @contactbase INT=0
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE dbo.Temp_ContactBase_main
END
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'ISUserTable')=1)
BEGIN
DROP TABLE dbo.Temp_ContactBase_sub
END
CREATE TABLE dbo.Temp_ContactBase_main
(
ContactId uniqueidentifier NOT NULL,
CONSTRAINT PK_Temp_ContactBase_main PRIMARY KEY CLUSTERED (ContactId)
)
CREATE TABLE Temp_ContactBase_sub
(
ContactId uniqueidentifier NOT NULL,
CONSTRAINT PK_Temp_ContactBase_sub PRIMARY KEY CLUSTERED (ContactId)
)
IF @number_of_purgerecords IS NULL
BEGIN
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase (NOLOCK) where statecode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_interactionBase (NOLOCk)
UNION
SELECt eas_contactid AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECt eas_PolicyHolderNameId AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECT eas_primaryinsured FROM eas_quotebase (NOLOCK) WHERE statecode = 0
UNION
SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT ContactId FROM contactbase (NOLOCK)
EXCEPT
SELECT ContactId FROM Un_wanted_contacts
END
ELSE
BEGIN
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase (NOLOCK) where statecode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION
SELECT eas_contactid AS ContactId FROM eas_interactionBase (NOLOCk)
UNION
SELECt eas_contactid AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECt eas_PolicyHolderNameId AS ContactId FROM eas_customervalidationBase (NOLOCk)
UNION
SELECT eas_primaryinsured FROM eas_quotebase (NOLOCK) WHERE statecode = 0
UNION
SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT top (@number_of_purgerecords) ContactId FROM (
SELECT ContactId FROM contactbase (NOLOCK)
EXCEPT
SELECT ContactId FROM Un_wanted_contacts) a
END
INSERT INTO dbo.Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
WHILE EXISTS( SELECT 1 FROM Temp_ContactBase_main)
BEGIN
--CustomerAddressBase
--eas_contact_quote_relationship
--eas_quotecontactbase
--eas_quotebase
--eas_policybase
--eas_policycontactbase
--eas_contact_policy_relationship
--contactBase
SET @message=''
---CustomerAddressBase
DELETE cab
FROM dbo.CustomerAddressBase cab
INNER JOIN dbo.Temp_ContactBase_sub cb WITH(NOLOCk)
ON cab.ParentId=cb.ContactId
SET @CustomerAddressBase=@CustomerAddressBase+@@ROWCOUNT
SET @message=@message+'No of purge records in CustomerAddressBase:'+CAST(@CustomerAddressBase AS VARCHAR(10))
-- eas_contact_quote_relationshipBase
DELETE ecqr
FROM dbo.eas_contact_quote_relationshipBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.ContactId=re_sub.ContactId
SET @eas_contact_quote_relationshipBase=@eas_contact_quote_relationshipBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_quote_relationshipBase:'+CAST(@eas_contact_quote_relationshipBase AS VARCHAR(10))
--eas_quotecontactBase
DELETE ecqr
FROM dbo.eas_quotecontactBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_Contact=re_sub.ContactId
SET @eas_quotecontactBase=@eas_quotecontactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_quotecontactBase:'+CAST(@eas_quotecontactBase AS VARCHAR(10))
--eas_quoteBase
DELETE ecqr
FROM dbo.eas_quoteBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_primaryinsured=re_sub.ContactId
SET @eas_quoteBase=@eas_quoteBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_quoteBase:'+CAST(@eas_quoteBase AS VARCHAR(10))
--eas_policycontactBase
DELETE ecv
FROm dbo.eas_policycontactBase ecv
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecv.eas_contactid=re_sub.ContactId
SET @eas_policycontactBase=@eas_policycontactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_policycontactBase:'+CAST(@eas_policycontactBase AS VARCHAR(10))
---eas_contact_policy_relationship
DELETE ecpr
FROM dbo.eas_contact_policy_relationshipBase ecpr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecpr.contactid=re_sub.ContactId
SET @eas_contact_policy_relationshipBase=@eas_contact_policy_relationshipBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_contact_policy_relationshipBase AS VARCHAR(10))
---eas_policyBase
DELETE ecqr
FROM dbo.eas_policyBase ecqr
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ecqr.eas_PolicyHolderId=re_sub.ContactId
SET @eas_policyBase=@eas_policyBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_policyBase AS VARCHAR(10))
--- Contactbase
DELETE ab
FROM dbo.ContactBase ab
INNER JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
ON ab.ContactId=re_sub.ContactId
SET @ContactBase=@ContactBase+@@ROWCOUNT
SET @message=@message+SPACE(2)+'Number Of Purge Records in ContactBase:'+CAST(@ContactBase AS VARCHAR(10))
DELETE ab
FROM Temp_ContactBase_main ab
INNER JOIN Temp_ContactBase_sub y WITH(NOLOCK)
On ab.ContactId=y.ContactId
TRUNCATE TABLE Temp_ContactBase_sub
INSERT INTO Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
END
INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE Temp_ContactBase_main
END
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
BEGIN
DROP TABLE Temp_ContactBase_sub
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = N'Error in SP: dbo.ES_Purge_ContactBase: ' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message+@ErrorMessage
RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END
No comments:
Post a Comment