USP_CONSTITUENT_DELETE

CREATE procedure dbo.USP_CONSTITUENT_DELETE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  begin try
    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTRESEARCHREQUESTCONSTITUENT')
      begin

      --see if the constituent is being researched

      if exists(select 1 from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where CONSTITUENTID = @ID and STATUSCODE in (0, 1, 2, 3)) begin
        raiserror('BBERR_PROSPECTRESEARCHREQUEST_HASOPENREQUEST', 13, 1);
        return 1;
      end

      --see if the constituent requested, submitted, or is assigned to an open request

      if exists(select 1 from dbo.PROSPECTRESEARCHREQUEST where (SUBMITTEDBYID = @ID or REQUESTEDBYID = @ID or ASSIGNEDTOID = @ID) and STATUSCODE in (1,2,3,5)) 
        or exists(select 1 from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where ASSIGNEDTOID = @ID and STATUSCODE in (1, 2, 3, 5)) 
      begin
        raiserror('BBERR_PROSPECTRESEARCHREQUEST_REQUESTORSUBMITTERASSIGNEE', 13, 1);
        return 1;
      end
      else begin
        update dbo.PROSPECTRESEARCHREQUEST set SUBMITTEDBYID = null where SUBMITTEDBYID = @ID
        update dbo.PROSPECTRESEARCHREQUEST set REQUESTEDBYID = null where REQUESTEDBYID = @ID
        update dbo.PROSPECTRESEARCHREQUEST set ASSIGNEDTOID = null where ASSIGNEDTOID = @ID
        update dbo.PROSPECTRESEARCHREQUESTCONSTITUENT set ASSIGNEDTOID = null where ASSIGNEDTOID = @ID
      end
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'WEALTH')
      begin
      --check for pending wealthpoint updates

      if exists(select 1 from dbo.WEALTH where ID=@ID and PENDINGSEARCH=1) begin
        raiserror('Current constituent has WealthPoint updates pending.', 13, 1);
        return 1;
      end
    end;

    if exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFT') and
      exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFTBENEFICIARY')
        begin
          if exists(
            select top(1) PGB.ID
            from dbo.PLANNEDGIFTBENEFICIARY PGB
            inner join dbo.PLANNEDGIFT PG on PG.ID = PGB.PLANNEDGIFTID
            where PG.CONSTITUENTID = @ID
          )
            raiserror('CONSTITUENTHASPLANNEDGIFTWITHBENEFICIARY', 13, 1)

          if exists(
            select top(1) PGB.ID
            from dbo.PLANNEDGIFTBENEFICIARY PGB
            where PGB.BENEFICIARYCONSTITUENTID = @ID
          )
            raiserror('CONSTITUENTISBENEFICIARY', 13, 1)
        end

    if exists(select object_id from sys.objects where type = 'U' and name = 'NAMINGOPPORTUNITYRECOGNITION')
      begin
        if exists(select top 1 ID from dbo.NAMINGOPPORTUNITYRECOGNITION where CONSTITUENTID = @ID)
          raiserror('CONSTITUENTHASNAMEDRECOGNITIONS', 13, 1);
      end

    declare @contextCache varbinary(128);
    --cache current context information

    set @contextCache = CONTEXT_INFO();
    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUE')
      begin

        --Bug 70637 - Adambu 12/9/09 - Do not throw this error, as it can cause merge failures.

        --    Instead, allow the cascade delete on BATCHREVENUECONSTITUENT fire, removing the

        --    exception this was added to handle.

        ----DanielCo[05/12/2009] - Make sure the constituent doesn't have revenue in revenue batch

        --if exists(select ID from dbo.BATCHREVENUE where CONSTITUENTID = @ID)

        --    begin

        --        raiserror('BBERR_BATCHREVENUE_HASREVENUE', 13, 1);

        --        return 1;

        --    end


        --remove constituent from batch revenue author field

        update
          dbo.BATCHREVENUE
        set
          NOTEAUTHORID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          NOTEAUTHORID = @ID;
      end

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUECONSTITUENTGROUPMEMBER')
    begin
      delete
        GROUPMEMBER
      from
        dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GROUPMEMBER
      inner join
        dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENT.ID = GROUPMEMBER.MEMBERID
      where
        BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID = @ID;
    end

    --remove constituent (relationship) from grantors

    if exists(select object_id from sys.objects where type = 'U' and name = 'GRANTOR') begin
      delete from
        dbo.GRANTOR
      where    
        ID = @ID;

      update
        dbo.GRANTOR
      set
        CONTACTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        CONTACTID in (select RELATIONSHIP.ID from dbo.RELATIONSHIP where RELATIONSHIP.RECIPROCALCONSTITUENTID = @ID);
    end 


    --remove constituent (sponsor) from funding request

    if exists(select object_id from sys.objects where type = 'U' and name = 'FUNDINGREQUESTSPONSOR') begin
      delete from
        dbo.FUNDINGREQUESTSPONSOR
      where
        SPONSORID = @ID;
    end

    --remove constituent (funding request manager) from funding request

    if exists(select object_id from sys.objects where type = 'U' and name = 'FUNDINGREQUEST') begin
      update
        dbo.FUNDINGREQUEST
      set
        PRIMARYMANAGERID = null,
        FUNDINGREQUEST.CHANGEDBYID = @CHANGEAGENTID,
        FUNDINGREQUEST.DATECHANGED = @CURRENTDATE
      where
        FUNDINGREQUEST.PRIMARYMANAGERID = @ID;

      update
        dbo.FUNDINGREQUEST
      set
        SECONDARYMANAGERID = null,
        FUNDINGREQUEST.CHANGEDBYID = @CHANGEAGENTID,
        FUNDINGREQUEST.DATECHANGED = @CURRENTDATE
      where
        FUNDINGREQUEST.SECONDARYMANAGERID = @ID;
    end

    --remove constituent from funding plans 

    if exists(select object_id from sys.objects where type = 'U' and name = 'FUNDINGPLAN') begin
      update
        dbo.FUNDINGPLAN
      set
        FUNDINGPLANMANAGERID = null,
        FUNDINGPLAN.CHANGEDBYID = @CHANGEAGENTID,
        FUNDINGPLAN.DATECHANGED = @CURRENTDATE
      where
        FUNDINGPLAN.FUNDINGPLANMANAGERID = @ID;
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHADDRESSVALIDATION')
      begin
        --JamesWill 10/17/2008 WI 16257 Delete rows from any address validation batches that reference this constituent 

        delete from dbo.BATCHADDRESSVALIDATION
          where CONSTITUENTID = @ID;

        delete BATCHADDRESSVALIDATION
          from dbo.BATCHADDRESSVALIDATION
          inner join dbo.ADDRESS on ADDRESS.ID = BATCHADDRESSVALIDATION.ADDRESSID 
          where ADDRESS.CONSTITUENTID = @ID;
    end

    --remove constituent from author field on constituent documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'CONSTITUENTATTACHMENT')
      update
        dbo.CONSTITUENTATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'CONSTITUENTMEDIALINK')
      update
        dbo.CONSTITUENTMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'CONSTITUENTNOTE')
 update
        dbo.CONSTITUENTNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on event notes

    if exists(select object_id from sys.objects where type = 'U' and name = 'EVENTNOTE')
      update
        dbo.EVENTNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on designation level notes

    if exists(select object_id from sys.objects where type = 'U' and name = 'DESIGNATIONLEVELNOTE')
      update
        dbo.DESIGNATIONLEVELNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on step notes

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEPNOTE')
      update
        dbo.STEPNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on revenue detail notes

    if exists(select object_id from sys.objects where type = 'U' and name = 'REVENUEDETAILNOTE')
      update
        dbo.REVENUEDETAILNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on job documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'JOBATTACHMENT')
      update
        dbo.JOBATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'JOBMEDIALINK')
      update
        dbo.JOBMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'JOBNOTE')
      update
        dbo.JOBNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on volunteer documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'VOLUNTEERATTACHMENT')
      update
        dbo.VOLUNTEERATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'VOLUNTEERMEDIALINK')
      update
        dbo.VOLUNTEERMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'VOLUNTEERNOTE')
      update
        dbo.VOLUNTEERNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on interaction documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONATTACHMENT')
      update
        dbo.INTERACTIONATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONMEDIALINK')
      update
        dbo.INTERACTIONMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONNOTE')
      update
        dbo.INTERACTIONNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on planned gift documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFTATTACHMENT')
      update
        dbo.PLANNEDGIFTATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFTMEDIALINK')
      update
        dbo.PLANNEDGIFTMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFTNOTE')
      update
        dbo.PLANNEDGIFTNOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from author field on revenue documentation

    if exists(select object_id from sys.objects where type = 'U' and name = 'REVENUEATTACHMENT')
      update
        dbo.REVENUEATTACHMENT
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'REVENUEMEDIALINK')
      update
        dbo.REVENUEMEDIALINK
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'REVENUENOTE')
      update
        dbo.REVENUENOTE
      set
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        AUTHORID = @ID;

    --remove constituent from sponsor field on volunteer

    if exists(select object_id from sys.objects where type = 'U' and name = 'VOLUNTEER')
      update
        dbo.VOLUNTEER
      set
        SPONSORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        SPONSORID = @ID;

    --delete the planned gift relationships in which the constituent is involved

    if exists(select object_id from sys.objects where type = 'U' and name = 'PLANNEDGIFTRELATIONSHIP')
      delete from
        dbo.PLANNEDGIFTRELATIONSHIP
      where
        RELATIONSHIPID in (select ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @ID);

    --delete DESIGNATIONLEVELDONORINFORMATION in which the constituent is involved

    if exists(select object_id from sys.objects where type = 'U' and name = 'DESIGNATIONLEVELDONORINFORMATION')  
    begin  
        delete from dbo.DESIGNATIONLEVELDONORINFORMATION where ConstituentID = @ID
    end

    --delete the relationships in which the constituent is involved

    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
    begin

      --Set an end-date for relationships auto-formed based on Relationship Corporate Configuration

      declare @PARENTCORPID uniqueidentifier = (select PARENTCORPID from dbo.ORGANIZATIONDATA where ID = @ID)
      if @PARENTCORPID is not null
      begin
        --Using this function to set end dates on all invalid intermediate parent relationships based on the Parent Corporation change

        exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @ID, null, @CURRENTDATE, @CHANGEAGENTID, @CURRENTDATE, 0, @PARENTCORPID
      end

      delete from
        dbo.RELATIONSHIPBYMARRIAGE
      from
        dbo.RELATIONSHIP
        inner join dbo.RELATIONSHIPBYMARRIAGE
          on RELATIONSHIP.ID in (RELATIONSHIPBYMARRIAGE.RELATIONSHIPID, RELATIONSHIPBYMARRIAGE.MARRIAGERELATIONSHIPID)
      where
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID;

      delete from
        dbo.RELATIONSHIP
      where
        RELATIONSHIPCONSTITUENTID = @ID;
    end

    --remove constituent as parent organization from organization data

    if exists(select object_id from sys.objects where type = 'U' and name = 'ORGANIZATIONDATA')
      update
        dbo.ORGANIZATIONDATA
      set
        PARENTCORPID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        PARENTCORPID = @ID;

    --remove constituent (fundraiser) from prospects

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECT')
    begin
      update
        dbo.PROSPECT
      set
        PROSPECTMANAGERFUNDRAISERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        PROSPECTMANAGERFUNDRAISERID = @ID;
    end

    --remove constituent (fundraiser) from prospect manager history

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTMANAGERHISTORY')
    begin
      delete from
        dbo.PROSPECTMANAGERHISTORY
      where
        FUNDRAISERID = @ID;
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONADDITIONALFUNDRAISER')
      delete from 
        dbo.INTERACTIONADDITIONALFUNDRAISER
      where
        FUNDRAISERID = @ID;    

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONPARTICIPANT')
      delete from 
        dbo.INTERACTIONPARTICIPANT
      where
        CONSTITUENTID = @ID;    

    if exists(select object_id from sys.objects where type = 'U' and name = 'NAMINGOPPORTUNITY')
    begin
      --delete any named recognitions associated with the plans for this constituent


      delete NAMINGOPPORTUNITYRECOGNITION from dbo.NAMINGOPPORTUNITYRECOGNITION
      inner join dbo.NAMINGOPPORTUNITYRECOGNITIONMGLINK on NAMINGOPPORTUNITYRECOGNITIONMGLINK.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID
      inner join dbo.NAMINGOPPORTUNITYMGOPPORTUNITY on NAMINGOPPORTUNITYMGOPPORTUNITY.ID = NAMINGOPPORTUNITYRECOGNITIONMGLINK.MGOPPORTUNITYLINKID
      inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = NAMINGOPPORTUNITYMGOPPORTUNITY.OPPORTUNITYID
      inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      where PROSPECTPLAN.PROSPECTID = @ID

      delete NAMINGOPPORTUNITYMGOPPORTUNITY from dbo.NAMINGOPPORTUNITYMGOPPORTUNITY
      inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = NAMINGOPPORTUNITYMGOPPORTUNITY.OPPORTUNITYID
      inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
      where PROSPECTPLAN.PROSPECTID = @ID
    end

    --remove constituent (fundraiser) from prospect plans

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTPLAN')
    begin
      update
        dbo.PROSPECTPLAN
      set
        PRIMARYMANAGERFUNDRAISERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        PRIMARYMANAGERFUNDRAISERID = @ID;

      update
        dbo.PROSPECTPLAN
      set
        SECONDARYMANAGERFUNDRAISERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        SECONDARYMANAGERFUNDRAISERID = @ID;
    end

    --remove constituent (fundraiser) from prospect plan manager history

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTPLANMANAGERHISTORY')
    begin
      delete from
        dbo.PROSPECTPLANMANAGERHISTORY
      where
        FUNDRAISERID = @ID;
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'SECONDARYFUNDRAISER')
      delete from
        dbo.SECONDARYFUNDRAISER
      where
        FUNDRAISERID = @ID;

    --remove constituent (fundraiser) from steps

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTION')
      update
        dbo.INTERACTION
      set
        FUNDRAISERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        FUNDRAISERID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'INTERACTIONADDITIONALFUNDRAISER')
      delete from 
        dbo.INTERACTIONADDITIONALFUNDRAISER
      where
        FUNDRAISERID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'CAMPAIGNFUNDRAISER')
      delete from
        dbo.CAMPAIGNFUNDRAISER
      where
        CONSTITUENTID = @ID;

    -- Remove plan participants from plans that will be removed through cascading deletes because 

    -- they are tied to the constituent being deleted

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTPLAN') and
      exists(select object_id from sys.objects where type = 'U' and name = 'PLANPARTICIPANT')
    begin
      delete from dbo.PLANPARTICIPANT 
      where PROSPECTPLANID in (select ID from dbo.PROSPECTPLAN where PROSPECTID = @ID);

                -- AdamBu - Bug 22878 - Delete this constituent's plan participant records.

                delete dbo.PLANPARTICIPANT
                where CONSTITUENTID = @ID

    end


    --remove any registrant guests of the this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'REGISTRANT')
    begin
      -- Delete registrant and guest group member records

      if exists(select object_id from sys.objects where type = 'U' and name = 'EVENTGROUPMEMBER')
      begin
        delete from dbo.EVENTGROUPMEMBER
        where
          REGISTRANTID in
          (
            select
              REGISTRANT.ID
            from
              dbo.REGISTRANT
            where
              REGISTRANT.CONSTITUENTID = @ID
          );

        delete from dbo.EVENTGROUPMEMBER
        where
          REGISTRANTID in
          (
            select
              GUEST.ID
            from
              dbo.REGISTRANT GUEST
              inner join dbo.REGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
            where
              HOST.CONSTITUENTID = @ID
          );
      end


      -- Delete any seats for the registrant and guests

      if exists(select object_id from sys.objects where type = 'U' and name = 'EVENTSEATINGSEAT')
        update dbo.EVENTSEATINGSEAT set
          EVENTSEATINGSEAT.REGISTRANTID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          EVENTSEATINGSEAT.REGISTRANTID in
            (
              select
                REGISTRANT.ID
              from
                dbo.REGISTRANT
              where
                REGISTRANT.CONSTITUENTID = @ID
            )
          or 
          EVENTSEATINGSEAT.REGISTRANTID in
          (
            select
              GUEST.ID
            from
              dbo.REGISTRANT GUEST
              inner join dbo.REGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
            where
              HOST.CONSTITUENTID = @ID
          );

      -- Delete any registrant registration maps for this registrant and their guests

      -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

      if exists(select object_id from sys.objects where type = 'U' and name = 'REGISTRANTREGISTRATIONMAP')
        delete from
          dbo.REGISTRANTREGISTRATIONMAP
        where
          REGISTRANTREGISTRATIONMAP.REGISTRANTID in
            (
              select
                REGISTRANT.ID
              from
                dbo.REGISTRANT
              where
                REGISTRANT.CONSTITUENTID = @ID
            )
          or 
          REGISTRANTREGISTRATIONMAP.REGISTRANTID in
            (
              select
                GUEST.ID
              from
                dbo.REGISTRANT GUEST
                inner join dbo.REGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
              where
                HOST.CONSTITUENTID = @ID
            );

      if exists(select object_id from sys.objects where type = 'U' and name = 'REGISTRANT')
        begin
          --Remove the registrant's guests

          delete from
            dbo.REGISTRANT
          where
            REGISTRANT.ID in
            (
              select 
                GUEST.ID 
              from 
                dbo.REGISTRANT GUEST
                inner join dbo.REGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
              where
                HOST.CONSTITUENTID = @ID
            );

          --Remove the registrant

          delete from
            dbo.REGISTRANT
          where
            REGISTRANT.CONSTITUENTID = @ID;
        end

      if exists(select object_id from sys.objects where type = 'U' and name = 'REGISTRANTPACKAGE')
      begin
        -- Delete any packages associated with this registrant

        delete from dbo.REGISTRANTPACKAGE where
          REGISTRANTPACKAGE.GUESTOFCONSTITUENTID = @ID

        delete from dbo.REGISTRANTPACKAGE where
          REGISTRANTPACKAGE.CONSTITUENTID = @ID
          and REGISTRANTPACKAGE.GUESTOFCONSTITUENTID is null

        update dbo.REGISTRANTPACKAGE set
          CONSTITUENTID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          REGISTRANTPACKAGE.CONSTITUENTID = @ID
          and REGISTRANTPACKAGE.GUESTOFCONSTITUENTID is not null
      end
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'EVENTCOORDINATOR')
      delete from
        dbo.EVENTCOORDINATOR
      where
        EVENTCOORDINATOR.CONSTITUENTID = @ID;

    -- same block as above, except with revenue batch registrations

    --remove any revenue batch registrant guests of the this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEREGISTRANT')
    begin

      -- Delete any registrant registration maps for this registrant and their guests

      -- Deleting from REGISTRANTREGISTRATIONMAP cascade deletes REGISTRANTPREFERENCEMAP

      if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEREGISTRANTREGISTRATIONMAP')
        delete from
          dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
        where
          BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
            (
              select
                BATCHREVENUEREGISTRANT.ID
              from
                dbo.BATCHREVENUEREGISTRANT
              where
                BATCHREVENUEREGISTRANT.CONSTITUENTID = @ID
            )
          or 
          BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTID in
            (
              select
                GUEST.ID
              from
                dbo.BATCHREVENUEREGISTRANT GUEST
                inner join dbo.BATCHREVENUEREGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
              where
                HOST.CONSTITUENTID = @ID
            );

      if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEAPPLICATION')
     begin
        --BATCHREVENUEAPPLICATION cannot support multiple cascade paths so set the batch revenue registrant IDs to null before deleting

        update dbo.BATCHREVENUEAPPLICATION set
          BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        from
          dbo.BATCHREVENUEAPPLICATION
        inner join
          dbo.BATCHREVENUE on BATCHREVENUE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEID
        where
          BATCHREVENUE.CONSTITUENTID = @ID;
      end

      if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEREGISTRANT')
      begin
          --Remove the registrant's guests

          delete from
            dbo.BATCHREVENUEREGISTRANT
          where
            BATCHREVENUEREGISTRANT.ID in
            (
              select 
                GUEST.ID 
              from 
                dbo.BATCHREVENUEREGISTRANT GUEST
                inner join dbo.BATCHREVENUEREGISTRANT HOST on GUEST.GUESTOFREGISTRANTID = HOST.ID
              where
                HOST.CONSTITUENTID = @ID
            );

          --Remove the registrant

          delete from
            dbo.BATCHREVENUEREGISTRANT
          where
            BATCHREVENUEREGISTRANT.CONSTITUENTID = @ID;
      end

      if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEREGISTRANTPACKAGE')
      begin
        -- Delete any packages associated with this registrant

        delete from dbo.BATCHREVENUEREGISTRANTPACKAGE where
          BATCHREVENUEREGISTRANTPACKAGE.GUESTOFCONSTITUENTID = @ID

        delete from dbo.BATCHREVENUEREGISTRANTPACKAGE where
          BATCHREVENUEREGISTRANTPACKAGE.CONSTITUENTID = @ID
          and BATCHREVENUEREGISTRANTPACKAGE.GUESTOFCONSTITUENTID is null

        update dbo.BATCHREVENUEREGISTRANTPACKAGE set
          CONSTITUENTID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          BATCHREVENUEREGISTRANTPACKAGE.CONSTITUENTID = @ID
          and BATCHREVENUEREGISTRANTPACKAGE.GUESTOFCONSTITUENTID is not null
      end
    end

    --delete any constituent record type attributes that reference this constituent

    exec dbo.USP_ATTRIBUTE_DELETECONSTITUENTVALUE @ID;

    --delete data from Attribute(ATTRIBUTEGUID) table for this constituent record

    exec dbo.USP_ATTRIBUTE_DELETECONSTITUENTATTRIBUTE @ID;

    --clear any registrant batch rows that use this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREGISTRANT')
    begin
      update dbo.BATCHREGISTRANT
      set
        CONSTITUENTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        BATCHREGISTRANT.CONSTITUENTID = @ID;

      update dbo.BATCHREGISTRANT
      set
        GUESTCONSTITUENTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        BATCHREGISTRANT.GUESTCONSTITUENTID = @ID;
    end

    --clear any invitee rows that use this constituent as an INVITATIONRECIPIENTCONSTITUENTID

    if exists(select object_id from sys.objects where type = 'U' and name = 'INVITEE')
    begin
      update dbo.INVITEE
      set
        INVITATIONRECIPIENTCONSTITUENTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        INVITEE.INVITATIONRECIPIENTCONSTITUENTID = @ID;
    end

    --clear any BBNC Profile Update batch rows that use this constituent as a primary business

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHBBNCPROFILE')
      update dbo.BATCHBBNCPROFILE
      set
        PRIMARYBUSINESSORGANIZATIONLINKID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
where
        BATCHBBNCPROFILE.PRIMARYBUSINESSORGANIZATIONLINKID = @ID;

    -- Clear any membership where the constituent is either a member of the group itself

    if exists(select object_id from sys.objects where type = 'U' and name = 'GROUPMEMBER')
    begin
      if exists(select object_id from sys.objects where type = 'U' and name = 'GROUPMEMBERDATERANGE')
        delete from dbo.GROUPMEMBERDATERANGE 
        where GROUPMEMBERID in (select ID from dbo.GROUPMEMBER where GROUPID = @ID or MEMBERID = @ID);

      delete from dbo.GROUPMEMBER where GROUPID = @ID or MEMBERID = @ID
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'MEMBERSHIP')
      begin
        -- Clear any membership where constituent is giver

        if exists(select ID from dbo.MEMBERSHIP where GIVENBYID = @ID)
        begin
          update dbo.MEMBERSHIP
            set ISGIFT = 0,
              GIVENBYID = null,
              SENDRENEWALCODE = 1,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
          where GIVENBYID = @ID;

          update dbo.MEMBERSHIPTRANSACTION
            set ISGIFT = 0,
              DONORID = null,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
          where DONORID = @ID;
        end
      end

      -- If the constituent is a dropped member on a membership then it is OK to delete them

      if exists(select object_id from sys.objects where type = 'U' and name = 'MEMBER')
      begin
        delete from dbo.MEMBER where ISDROPPED = 1 and CONSTITUENTID = @ID
      end

    --remove the constituent reference on any BBNC client user records tied to this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'NETCOMMUNITYCLIENTUSER')
      update
        dbo.NETCOMMUNITYCLIENTUSER
      set
        NETCOMMUNITYCLIENTUSER.CONSTITUENTID = null,
        NETCOMMUNITYCLIENTUSER.CHANGEDBYID = @CHANGEAGENTID,
        NETCOMMUNITYCLIENTUSER.DATECHANGED = @CURRENTDATE
      where
        NETCOMMUNITYCLIENTUSER.CONSTITUENTID = @ID;

    --clear any BBNC email records tied to this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'NETCOMMUNITYEMAILJOBRECIPIENT')
      begin
        update
          dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION
        set
          LATESTEMAILJOBRECIPIENTID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        from
          dbo.NETCOMMUNITYEMAILJOBRECIPIENT
          inner join dbo.NETCOMMUNITYNEWSLETTERSUBSCRIPTION on NETCOMMUNITYEMAILJOBRECIPIENT.ID = NETCOMMUNITYNEWSLETTERSUBSCRIPTION.LATESTEMAILJOBRECIPIENTID
        where
          NETCOMMUNITYEMAILJOBRECIPIENT.CONSTITUENTID = @ID;

        delete from
          dbo.NETCOMMUNITYEMAILJOBRECIPIENT
        where
          NETCOMMUNITYEMAILJOBRECIPIENT.CONSTITUENTID = @ID;
      end

    -- Clear any recognition default records.  The SOURCECONSTITUENTID has cascading delete turned

    -- on so the operation only looks at RECIPIENTCONSTITUENTID

    if exists(select object_id from sys.objects where type = 'U' and name = 'REVENUERECOGNITIONDEFAULT')
      delete from dbo.REVENUERECOGNITIONDEFAULT where RECIPIENTCONSTITUENTID = @ID;

    -- Clear any exchange calendar batch rows that reference this constituent.

    if exists(select object_id from sys.objects where type = 'U' and name = 'EXCHANGECALENDARITEMBATCH')
      delete from 
        dbo.EXCHANGECALENDARITEMBATCH 
      where 
        CONSTITUENTID = @ID;

    -- Remove corporate structure selections that reference this constituent as ROOT.

    if exists(select object_id from sys.objects where type = 'U' and name = 'CORPORATESTRUCTURESELECTION')
      delete from 
        dbo.CORPORATESTRUCTURESELECTION 
      where 
        ROOTID = @ID;


        -- Remove educational involvements

    if exists(select object_id from sys.objects where type = 'U' and name = 'EDUCATIONALINVOLVEMENT')
      delete from 
        dbo.EDUCATIONALINVOLVEMENT 
      where 
        CONSTITUENTID = @ID;

    -- Remove constituent from author field on stewardship documentation                        

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPATTACHMENT')
    begin
      update dbo.STEWARDSHIPATTACHMENT
      set 
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        AUTHORID = @ID;

      delete from dbo.STEWARDSHIPATTACHMENT
      where PLANID = @ID;
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPMEDIALINK')
    begin
      update dbo.STEWARDSHIPMEDIALINK
      set 
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        AUTHORID = @ID;

      delete from dbo.STEWARDSHIPMEDIALINK
      where PLANID = @ID;
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPNOTE')
    begin
      update dbo.STEWARDSHIPNOTE
      set 
        AUTHORID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        AUTHORID = @ID;

      delete from dbo.STEWARDSHIPNOTE
      where PLANID = @ID;
    end

    -- Delete any stewardship plan participant records for this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPPLANSTEWARD')
      delete from dbo.STEWARDSHIPPLANSTEWARD
      where CONSTITUENTID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPPLANSTEP')
    begin
      -- remove this constituent from any stewardship steps for which they are the contact person

      update dbo.STEWARDSHIPPLANSTEP
      set 
        CONTACTPERSONID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        CONTACTPERSONID = @ID;

      -- remove this constituent from any stewardship steps to which they've been assigned

      update dbo.STEWARDSHIPPLANSTEP
      set 
        CONSTITUENTID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        CONSTITUENTID = @ID;            

      if exists(select object_id from sys.objects where type = 'U' and name = 'EXCHANGECALENDARITEMBATCH')
      delete from 
        dbo.EXCHANGECALENDARITEMBATCH 
      where 
        STEWARDSHIPSTEPID in (select ID from dbo.STEWARDSHIPPLANSTEP where PLANID = @ID);

    end 

    -- remove this constituent from any stewardship plans for which they are the plan coordinator

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPPLAN')
      update dbo.STEWARDSHIPPLAN
      set 
        MANAGERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        MANAGERID = @ID;

    -- remove this constituent from any prospect team assignments in which s/he is a team member.  (CascadeDelete could

    -- not be used bc/ there is already a CascadeDelete for the prospect ID in the prospect team assignment.

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTTEAM')
      delete from dbo.PROSPECTTEAM where MEMBERID = @ID;

    if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTASSIGNMENTREQUEST')
    begin
      /* RSC 4/14/09 work item 31996.  Delete any prospect assignment requests for this constituent. */
      if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTASSIGNMENTREQUESTPROSPECT')
        delete 
         PROSPECTASSIGNMENTREQUEST 
        from 
          dbo.PROSPECTASSIGNMENTREQUEST 
          inner join dbo.PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUESTPROSPECT.ID = PROSPECTASSIGNMENTREQUEST.ID
        where
          PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID = @ID;

      /* RSC 4/14/09 work item 31996.  Delete any prospect plan requests for which this constituent is the prospect. */
      if exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTASSIGNMENTREQUESTPROSPECTPLAN') and
         exists(select object_id from sys.objects where type = 'U' and name = 'PROSPECTPLAN')
        delete 
          PROSPECTASSIGNMENTREQUEST 
        from 
          dbo.PROSPECTASSIGNMENTREQUEST 
          inner join dbo.PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID = PROSPECTASSIGNMENTREQUEST.ID
          inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
        where
          PROSPECTPLAN.PROSPECTID = @ID;
    end

    /* Delete constituent relationship managers for this constituent */
    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIPMANAGER')
      delete from 
        dbo.RELATIONSHIPMANAGER 
      where
        RELATIONSHIPMANAGER.CONSTITUENTID = @ID;

    -- Delete any revenue batch recognition for this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUERECOGNITION')
      delete from dbo.BATCHREVENUERECOGNITION
      where CONSTITUENTID = @ID;

    -- Delete any revenue batch letters for this constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUELETTER')
      delete from dbo.BATCHREVENUELETTER
      where ACKNOWLEDGEEID = @ID;

    --decrement research group member count that reference this constituent

    --Make sure the table exists

    if exists(select object_id from sys.objects where type = 'U' and name = 'RESEARCHGROUP')
      update RESEARCHGROUP set 
        NUMMEMBERS = case when NUMMEMBERS - 1 > 0 then NUMMEMBERS - 1 else 0 end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where 
        RESEARCHGROUP.ID in
          (
            select
              RESEARCHGROUPID
            from RESEARCHGROUPMEMBER
            where CONSTITUENTID = @ID
          )

    --clear any Constituent Update batch rows that use this constituent as a spouse recordid

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHCONSTITUENTUPDATE')
      update dbo.BATCHCONSTITUENTUPDATE
        set
          SPOUSE_ID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          BATCHCONSTITUENTUPDATE.SPOUSE_ID = @ID;

    --clear any Constituent Update batch rows that use this constituent as a business recordid

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHCONSTITUENTUPDATE')
      update dbo.BATCHCONSTITUENTUPDATE
        set
          BUSINESS_ID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          BATCHCONSTITUENTUPDATE.BUSINESS_ID = @ID;

    --clear any Educational History Update batch rows that use this constituent as a primary context recordid

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHEDUCATIONALHISTORYUPDATE')
      update dbo.BATCHEDUCATIONALHISTORYUPDATE
        set
          PRIMARYCONTEXTRECORDID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          BATCHEDUCATIONALHISTORYUPDATE.PRIMARYCONTEXTRECORDID = @ID;
    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHINTERACTION')
    begin
      --Delete any of the constituents interactions in batch

      delete from dbo.BATCHINTERACTION 
      where CONSTITUENTID = @ID

      --remove constituent from batch revenue author field

      update
        dbo.BATCHINTERACTION
      set
        FUNDRAISERID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        FUNDRAISERID = @ID;
    end

        --remove constituent (organization) from merger history

    if exists(select object_id from sys.objects where type = 'U' and name = 'ORGANIZATIONMERGERHISTORY') begin
      delete from
        dbo.ORGANIZATIONMERGERHISTORY
      where
        SOURCEORG1ID = @ID OR SOURCEORG2ID = @ID OR NEWORGID = @ID
    end

        --remove constituent (organization) from parent org history

    if exists(select object_id from sys.objects where type = 'U' and name = 'ORGANIZATIONPARENTHISTORY') begin
      delete from
        dbo.ORGANIZATIONPARENTHISTORY
      where
        CHILDCORPID = @ID or PARENTCORPID = @ID
    end

    --remove constituent (constituent) from Stewardship Plan Step Participant

    if exists(select object_id from sys.objects where type = 'U' and name = 'STEWARDSHIPPLANSTEPPARTICIPANT') begin
      delete from
        dbo.STEWARDSHIPPLANSTEPPARTICIPANT
      where
        CONSTITUENTID = @ID
    end

        --remove batch membership dues rows, if any

        if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHMEMBERSHIPDUES') 
        begin
      delete from
        dbo.BATCHMEMBERSHIPDUES
      where
        BILLTOCONSTITUENTID = @ID or MEMBERSHIPRECIPIENTID = @ID;
    end

    -- remove the national fundraising group

    if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGN') 
    begin
      declare @NFGS table(ID int identity(1,1), NFGID uniqueidentifier)
      insert into @NFGS(NFGID) 
      select ID from FAFNFGCAMPAIGN where GROUPCONSTITUENTID =@ID
    end

    --clear any Stewardship Plan Step Update batch rows that use this constituent as a constituent recordid.

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHSTEWARDSHIPPLANSTEPUPDATE')
    begin
       update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE  
          set 
            CONSTITUENTID = null,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where 
            CONSTITUENTID=@ID;
    end

    declare @NFGNO int
    declare @index int
    declare @NFGID uniqueidentifier

    select @NFGNO = count(*),@index=1 from @NFGS

    if @NFGNO>0 begin
      while @index <= @NFGNO
      begin
        --AN NFG can have 1-n levels, each of which need to be deleted before the NFG itself can be  

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGNLEVEL') 
        begin
          declare @LEVELS table(LEVELID uniqueidentifier)
          select @NFGID = NFGID from @NFGS where ID=@index

          insert into @LEVELS(LEVELID)
          select ID from FAFNFGCAMPAIGNLEVEL 
          where NFGCAMPAIGNID = @NFGID
        end

        --clear out any associations between local groups and the campaign levels

        if exists(select object_id from sys.objects where type = 'U' and name = 'TEAMEXTENSION') 
        begin
          update dbo.TEAMEXTENSION 
          set NFGCAMPAIGNLEVELID=NULL, CHANGEDBYID=@CHANGEAGENTID, DATECHANGED=@CURRENTDATE
          where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)
        end

        --delete any site display entries for those levels

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGNLEVELDISPLAYSITE')
        begin
          delete from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE 
          where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)
        end

        --delete any friendly url values for those levels

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFFRIENDLYURLPARAMS') 
        begin
          delete from dbo.FAFFRIENDLYURLPARAMS 
          where NFGCAMPAIGNLEVELID in (select LEVELID from @LEVELS)
        end

        --finally, delete the levels themselves

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGNLEVEL') 
        begin
          delete from FAFNFGCAMPAIGNLEVEL 
          where NFGCAMPAIGNID = @NFGID
        end

        --now, on to the actual campaign deletion

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGNDISPLAYSITE')
        begin
          delete from FAFNFGCAMPAIGNDISPLAYSITE 
          where NFGCAMPAIGNID = @NFGID
        end

        if exists(select object_id from sys.objects where type = 'U' and name = 'FAFNFGCAMPAIGN')
        begin
          exec dbo.USP_FAFNFGCAMPAIGN_DELETEBYID_WITHCHANGEAGENTID @NFGID, @CHANGEAGENTID
        end

        set @Index = @Index + 1
      end
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'FAFORGANIZATIONINDIVIDUALRELATION')
    begin
      delete from FAFORGANIZATIONINDIVIDUALRELATION 
      where INDIVIDUALCONSTITUENTID = @ID or ORGANIZATIONCONSTITUENTID = @ID
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'EVENTSPONSOR')
    begin
      update EVENTSPONSOR 
      set ORGANIZATIONCONSTITUENTID = null 
      where ORGANIZATIONCONSTITUENTID = @ID
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHCONSTITUENT')
    begin
      update dbo.BATCHCONSTITUENT
      set PRIMARYBUSINESSID = null
      where PRIMARYBUSINESSID = @ID
    end

    if exists(select object_id from sys.objects where type = 'U' and name = 'RECURRINGGIFTAMENDMENT')
    begin
      merge dbo.RECURRINGGIFTAMENDMENT t
      using (select @ID as ID) s
      on (s.ID = t.CONSTITUENTID) or (s.ID = t.PREVIOUSCONSTITUENTID)
      when matched and (t.AMENDMENTTYPECODE <> 0) and 
        (t.PREVIOUSCONSTITUENTID is null or t.CONSTITUENTID is null) then
          delete
      when matched then
        update set t.CONSTITUENTID = nullif(t.CONSTITUENTID, @ID),
            t.PREVIOUSCONSTITUENTID = nullif(t.PREVIOUSCONSTITUENTID, @ID),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE;
    end

    -- Delete any address references in Address Update Batch for the constituent

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHCONSTITUENTADDRESSUPDATE')
    begin
      delete from
        dbo.BATCHCONSTITUENTADDRESSUPDATE
      where
        ADDRESSID in
          (select ID from dbo.ADDRESS where CONSTITUENTID = @ID);
    end

    --BUG:953391, Delete any rows, if present from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS table

    if exists(select object_id from sys.objects where type = 'U' and name = 'BATCHREVENUEENHANCEDMATCHINGGIFTS')
    begin
      delete from
        dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
      where
        ORGANIZATIONID = @ID;
    end

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  exec dbo.USP_CONSTITUENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
  return 0;