USP_RELATIONSHIP_DELETE

CREATE procedure dbo.USP_RELATIONSHIP_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;
  declare @ADDRESSID uniqueidentifier

  select @ADDRESSID=ID
  from ADDRESS
  where RELATIONSHIPID=@ID and ISPRIMARY=0

  declare @INDIVIDUALANDORGANIZATIONRELATIONSHIP bit = 0;
  declare @INDIVIDUALISCONACT bit = 0;

  select
      @INDIVIDUALANDORGANIZATIONRELATIONSHIP = 
          case
              when (not CONSTITUENTISORG = 1 and not CONSTITUENTISGROUP = 1) and (RECIPROCALCONSTITUENTISORG = 1) then
                  1
              when (CONSTITUENTISORG = 1) and (not RECIPROCALCONSTITUENTISORG = 1 and not RECIPROCALCONSTITUENTISGROUP = 1) then
                  1
              else
                  0
          end,
      @INDIVIDUALISCONACT = ISCONTACT
  from
      dbo.UFN_RELATIONSHIP_GETCONSTITUENTS(@ID)

  if @ADDRESSID is not null and not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[ADDRESSID] = @ADDRESSID) and (not @INDIVIDUALANDORGANIZATIONRELATIONSHIP = 1 and not @INDIVIDUALISCONACT = 1)
  begin
    exec dbo.USP_ADDRESS_DELETEBYID_WITHCHANGEAGENTID @ADDRESSID, @CHANGEAGENTID;
  end
  else
  begin

    select @ADDRESSID=a.ID
    from dbo.ADDRESS a
      inner join dbo.RELATIONSHIP recip on recip.ID=a.RELATIONSHIPID
      inner join dbo.RELATIONSHIP relat on 
        relat.RELATIONSHIPCONSTITUENTID = recip.RECIPROCALCONSTITUENTID and
        relat.RECIPROCALCONSTITUENTID = recip.RELATIONSHIPCONSTITUENTID and
        relat.RELATIONSHIPTYPECODEID = recip.RECIPROCALTYPECODEID and
        relat.RECIPROCALTYPECODEID = recip.RELATIONSHIPTYPECODEID
    where  relat.ID='3661ecbe-0832-484b-8184-c79626164e1d' and a.ISPRIMARY=0

    if @ADDRESSID is not null and not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[ADDRESSID] = @ADDRESSID) and (not @INDIVIDUALANDORGANIZATIONRELATIONSHIP = 1 and not @INDIVIDUALISCONACT = 1)
    begin
      exec dbo.USP_ADDRESS_DELETEBYID_WITHCHANGEAGENTID @ADDRESSID, @CHANGEAGENTID;
    end
  end

  declare @SOURCECONSTITUENTID uniqueidentifier, @RECIPIENTCONSTITUENTID uniqueidentifier

  select
    @SOURCECONSTITUENTID = R.RELATIONSHIPCONSTITUENTID,
    @RECIPIENTCONSTITUENTID = R.RECIPROCALCONSTITUENTID
  from dbo.RELATIONSHIP R
  where
    ID = @ID

  begin try
    -- Clear recognition defaults if the matching couldn't have been set through household's recognize 

    -- specified members for a members gift

    declare @DELETERECOGNITIONDEFAULT bit
    set @DELETERECOGNITIONDEFAULT = 1

    declare @COMMONHOUSEHOLDID uniqueidentifier
    select 
      @COMMONHOUSEHOLDID = GM1.GROUPID 
    from dbo.GROUPMEMBER GM1
    inner join dbo.GROUPMEMBER GM2 on GM1.GROUPID = GM2.GROUPID
    inner join dbo.GROUPDATA GD on GM1.GROUPID = GD.ID
    where
      GD.GROUPTYPECODE = 0 and
      GM1.MEMBERID = @SOURCECONSTITUENTID and
      GM2.MEMBERID = @RECIPIENTCONSTITUENTID

    if @COMMONHOUSEHOLDID is not null
    begin
      declare @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint
      set @MEMBERRECOGNIZEOTHERMEMBERSCODE = dbo.UFN_HOUSEHOLD_GETMEMBERRECOGNIZEOTHERMEMBERSSETTING(@COMMONHOUSEHOLDID)

      if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 2
        set @DELETERECOGNITIONDEFAULT = 0
    end

    declare @CONTEXTCACHE varbinary(128) = context_info();

    if not @CHANGEAGENTID is null
      set context_info @CHANGEAGENTID;

    if @DELETERECOGNITIONDEFAULT = 0
    begin
      -- Verify the two constituents don't have any other relationships

      if not exists  
      (select 1 
      from dbo.RELATIONSHIP 
      where 
 ((RELATIONSHIPCONSTITUENTID = @SOURCECONSTITUENTID and
        RECIPROCALCONSTITUENTID = @RECIPIENTCONSTITUENTID) or
        (RELATIONSHIPCONSTITUENTID = @RECIPIENTCONSTITUENTID and
        RECIPROCALCONSTITUENTID = @SOURCECONSTITUENTID)) and
        ID <> @ID)
      delete from dbo.REVENUERECOGNITIONDEFAULT
        where
          (SOURCECONSTITUENTID = @SOURCECONSTITUENTID and
          RECIPIENTCONSTITUENTID = @RECIPIENTCONSTITUENTID) or
          (SOURCECONSTITUENTID = @RECIPIENTCONSTITUENTID and
          RECIPIENTCONSTITUENTID = @SOURCECONSTITUENTID)
    end

    if not @CONTEXTCACHE is null
      set context_info @CONTEXTCACHE;

    declare @RECIPROCALRELATIONSHIPID uniqueidentifier;
    select
      @RECIPROCALRELATIONSHIPID = REC.ID
    from
      dbo.RELATIONSHIP REL
    inner join
      dbo.RELATIONSHIP REC on
        REC.RELATIONSHIPCONSTITUENTID = REL.RECIPROCALCONSTITUENTID and
        REC.RECIPROCALCONSTITUENTID = REL.RELATIONSHIPCONSTITUENTID and
        REC.RELATIONSHIPTYPECODEID = REL.RECIPROCALTYPECODEID and
        REC.RECIPROCALTYPECODEID = REL.RELATIONSHIPTYPECODEID
    where
      REL.ID = @ID;

    update BATCHREVENUEENHANCEDMATCHINGGIFTS set
      RELATIONSHIPID = null,
      CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    from
      dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS BREMG
    inner join
      dbo.BATCHREVENUE on BATCHREVENUE.ID = BREMG.BATCHREVENUEID
    inner join
      dbo.BATCH on BATCH.ID = BATCHREVENUE.BATCHID
    where
      BREMG.RELATIONSHIPID in (@ID, @RECIPROCALRELATIONSHIPID) and
      BATCH.STATUSCODE = 1;

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

  exec dbo.USP_RELATIONSHIP_DELETE_CLEANUPPARENTCORP @SOURCECONSTITUENTID, @RECIPIENTCONSTITUENTID, @CURRENTDATE, @CHANGEAGENTID;
  exec dbo.USP_RELATIONSHIP_DELETE_CLEANUPPARENTCORP @RECIPIENTCONSTITUENTID, @SOURCECONSTITUENTID, @CURRENTDATE, @CHANGEAGENTID;

  delete dbo.RELATIONSHIPBYMARRIAGE where RELATIONSHIPID = @ID or MARRIAGERELATIONSHIPID = @ID; 

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