USP_CONSTITUENT_LINK_CLIENTUSER

CREATE procedure dbo.USP_CONSTITUENT_LINK_CLIENTUSER
(
@CONSTITUENTID uniqueidentifier,
@CLIENTUSERID int
)
as
begin

  set nocount on;
  DECLARE @SequenceID int, @ID int;    

  --if the constituent is already linked then don't do anything

  if exists(
      SELECT cu.ID
          FROM dbo.ClientUsers cu  
          INNER JOIN dbo.BackOfficeSystemUsers bosu 
            ON cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0  
          INNER JOIN dbo.BackOfficeSystemPeople bosp 
              ON bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0  
          INNER JOIN dbo.CONSTITUENT C
              ON bosp.BackofficeRecordID = C.SEQUENCEID     
          WHERE 
              C.ID = @CONSTITUENTID 
  )
  or
  exists(        
      SELECT cu.ID
          FROM dbo.ClientUsers cu  
          INNER JOIN dbo.BackOfficeSystemUsers bosu 
            ON cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0  
          INNER JOIN dbo.BackOfficeSystemPeople bosp 
              ON bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0  
          INNER JOIN dbo.CONSTITUENT C
              ON bosp.BackofficeRecordID = C.SEQUENCEID     
          WHERE 
              cu.ID = @CLIENTUSERID 
  )

    Return;

  SELECT @SequenceID = C.SEQUENCEID
  FROM dbo.CONSTITUENT C
  WHERE C.ID = @CONSTITUENTID;


  --populate BackOfficeSystemPeople table

  BEGIN TRY
    BEGIN TRAN

    --search existing record

    SELECT @ID = ID 
    FROM dbo.BackOfficeSystemPeople 
    WHERE BackOfficeSystemID = 0 
      and BackofficeRecordID = @SequenceID

    IF @ID is NULL  --no link before, so create the link

    BEGIN 
      INSERT INTO dbo.BackOfficeSystemPeople 
      (BackOfficeSystemID, 
      BackofficeRecordID
      )
      Values
      (0, --CRM

      @SequenceID
      );

      SELECT @ID = SCOPE_IDENTITY();  --BackOfficeSystemPeopleID

    END

    INSERT INTO dbo.BackOfficeSystemUsers         
    (
    BackofficePeopleID,
    ClientUsersID
    )
    VALUES
    (
    @ID,
    @CLIENTUSERID
    );

    COMMIT TRAN;

  END TRY
  BEGIN CATCH
    IF (XACT_STATE() <>0)
          Rollback Tran;
  END CATCH;

  Return;  
end