USP_RELATIONSHIPS_MANAGELIFECHANGES_ADDITIONAL

CREATE procedure dbo.USP_RELATIONSHIPS_MANAGELIFECHANGES_ADDITIONAL
(
  @RELATIONSHIPID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier, 
  @SPOUSEID uniqueidentifier, 
  @CHANGEAGENTID uniqueidentifier
)
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  declare @RECIPROCAL_RELATIONSHIPID uniqueidentifier; --need for creating the reciprocal relationshipbymarriage record

  select 
    @RECIPROCAL_RELATIONSHIPID = ID 
  from dbo.RELATIONSHIP 
  where ISSPOUSE = 1 and
    RECIPROCALCONSTITUENTID = @CONSTITUENTID and RELATIONSHIPCONSTITUENTID = @SPOUSEID;

  declare @SPOUSE_RELATIONSHIPCONSTITUENTID uniqueidentifier;
  declare @CONSTIT_RELATIONSHIPCONSTITUENTID uniqueidentifier;
  declare @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier;

  --join to life changes additional configuration to get new typecode for sibling relationship; exclude relationships created by the marriage process

  declare SIBLINGRELATIONSHIPCURSOR cursor local fast_forward for
    select 
      SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID, --new relationship on spouse side

      RELATIONSHIP.RECIPROCALCONSTITUENTID, --child

      MARRIAGEADDITIONALRELATIONSHIP.RELATIONSHIPTYPECODEID --new sibling type code

    from dbo.RELATIONSHIP
    inner join dbo.MARRIAGEADDITIONALRELATIONSHIPASSOCIATION 
      on MARRIAGEADDITIONALRELATIONSHIPASSOCIATION.RELATIONSHIPTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID
    inner join dbo.MARRIAGEADDITIONALRELATIONSHIP 
      on MARRIAGEADDITIONALRELATIONSHIPASSOCIATION.MARRIAGEADDITIONALRELATIONSHIPID = MARRIAGEADDITIONALRELATIONSHIP.ID 
    inner join dbo.RELATIONSHIP SPOUSERELATIONSHIP
      on SPOUSERELATIONSHIP.RECIPROCALTYPECODEID = MARRIAGEADDITIONALRELATIONSHIP.SPOUSERELATIONSHIPTYPECODEID
    where 
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
      SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SPOUSEID and
      (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= @CURRENTDATE) and
      (SPOUSERELATIONSHIP.ENDDATE is null or SPOUSERELATIONSHIP.ENDDATE >= @CURRENTDATE) and
      not exists (select ID from dbo.RELATIONSHIPBYMARRIAGE where RELATIONSHIPBYMARRIAGE.RELATIONSHIPID in (RELATIONSHIP.ID, SPOUSERELATIONSHIP.ID)) and
      not exists (select ID from dbo.DECEASEDCONSTITUENT where DECEASEDCONSTITUENT.ID in (RELATIONSHIP.RECIPROCALCONSTITUENTID, SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID)); 

  open SIBLINGRELATIONSHIPCURSOR;   

  fetch next from SIBLINGRELATIONSHIPCURSOR into @SPOUSE_RELATIONSHIPCONSTITUENTID, @CONSTIT_RELATIONSHIPCONSTITUENTID, @SPOUSE_RELATIONSHIPTYPECODEID;

  set @CURRENTDATE = getdate();

  while (@@FETCH_STATUS = 0)
  begin                    
    --this returns a list of type codes; DESCRIPTION is set to 1 if there are commonly used relationship type combinations; we grab the first to match behavior in UI

    declare @RELATIONSHIPTYPERELATESTO table(VALUE uniqueidentifier, LABEL nvarchar(255), DESCRIPTION bit);
    delete @RELATIONSHIPTYPERELATESTO;

    insert into @RELATIONSHIPTYPERELATESTO 
      exec dbo.USP_SIMPLEDATALIST_RELATIONSHIPTYPERELATESTO 0, @CONSTIT_RELATIONSHIPCONSTITUENTID, @SPOUSE_RELATIONSHIPTYPECODEID, null, 0, null;

    declare @RECIPROCALTYPECODEID uniqueidentifier = null;
    select 
      top 1 @RECIPROCALTYPECODEID = VALUE
    from @RELATIONSHIPTYPERELATESTO
    where DESCRIPTION = 1

    if not @RECIPROCALTYPECODEID is null --do nothing if there are no commonly used matches

    begin
      declare @SETID uniqueidentifier = newid();

      begin try
        insert into dbo.RELATIONSHIPSET
        (ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
        (@SETID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        declare @RELATIONSHIPID_SIBLING uniqueidentifier = newid();
        insert into dbo.RELATIONSHIP
       (
          ID,
          RELATIONSHIPCONSTITUENTID, 
          RECIPROCALCONSTITUENTID, 
          RELATIONSHIPTYPECODEID, 
          RECIPROCALTYPECODEID, 
          RELATIONSHIPSETID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )
        values
        (
          @RELATIONSHIPID_SIBLING,
          @CONSTIT_RELATIONSHIPCONSTITUENTID,
          @SPOUSE_RELATIONSHIPCONSTITUENTID, 
          @RECIPROCALTYPECODEID, 
          @SPOUSE_RELATIONSHIPTYPECODEID,
          @SETID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

        exec dbo.USP_RELATIONSHIPBYMARRIAGE_CREATE @RELATIONSHIPID, @RELATIONSHIPID_SIBLING, @CHANGEAGENTID, @CURRENTDATE;
      end try
        begin catch
        --do nothing

        end catch
    end

    fetch next from SIBLINGRELATIONSHIPCURSOR into @SPOUSE_RELATIONSHIPCONSTITUENTID, @CONSTIT_RELATIONSHIPCONSTITUENTID, @SPOUSE_RELATIONSHIPTYPECODEID;
  end

  --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

  close SIBLINGRELATIONSHIPCURSOR;        
  deallocate SIBLINGRELATIONSHIPCURSOR;
end