USP_BBNC_COMMITPROFILEUPDATEEMPLOYMENTHISTORY

    CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEMPLOYMENTHISTORY
        (
          @CONSTITUENTID uniqueidentifier = null,
          @RELATIONSHIPJOBINFOID uniqueidentifier = null,
          @ORGNAME nvarchar(100) = '',
          @ORGLINKID uniqueidentifier = null output,
          @POSITION nvarchar(100) = '',
          @INDUSTRYCODEID uniqueidentifier = null,
          @UPDATEINDUSTRYCODEID bit = 0,
          @CHANGEAGENTID uniqueidentifier = null,
          @ISCONTACT bit = 0,
          @CONTACTTYPE uniqueidentifier = null,
          @DEPARTMENT nvarchar(200) = '',
          @DIVISION nvarchar(200) = '',
          @JOBSCHEDULECODEID uniqueidentifier = null,
          @JOBCATEGORYCODEID uniqueidentifier = null,
          @CAREERLEVELCODEID uniqueidentifier = null,
          @STARTDATE datetime = null,
          @ENDDATE datetime = null,
          @MATCHESGIFTS bit = 0,
          @MAKEPRIMARY bit = 0,
          @CHANGEDATE datetime = null,
          @COMMENTS nvarchar(max) = '',
          @RELATIONSHIPSETID uniqueidentifier = null,
          @RESPONSIBILITY nvarchar(250) = '',
          @ISPRIVATERECORD bit = 0
        ) as
          set nocount on;

          declare @RELATIONSHIPID uniqueidentifier;
          declare @ORGID uniqueidentifier;
          declare @RELATIONSHIPTYPECODEID uniqueidentifier;
          declare @RECIPROCALTYPECODEID uniqueidentifier;
          declare @BUSINESSADDRESSTYPECODEID uniqueidentifier;
          declare @BUSINESSADDRESSID uniqueidentifier;
          declare @SEQUENCE int;
          declare @ORGANIZATIONPRIMARYADDRESSTYPECODEID uniqueidentifier;

          if @CONSTITUENTID is null
            begin
              raiserror('The constituent ID is required',16,1);
              return -2;
            end

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

          if @CHANGEDATE is null
            set @CHANGEDATE = getdate();

          declare @INFOSOURCECODEID uniqueidentifier;
          set @INFOSOURCECODEID = null;

          --ORGANIZATIONPRIMARYADDRESSTYPECODEID can be null

          select top (1)
            @BUSINESSADDRESSTYPECODEID = [BUSINESSADDRESSTYPECODEID],
            @ORGANIZATIONPRIMARYADDRESSTYPECODEID = [ORGANIZATIONPRIMARYADDRESSTYPECODEID],
            @INFOSOURCECODEID = [INFOSOURCECODEID]
          from
            dbo.NETCOMMUNITYDEFAULTCODEMAP;

          if @BUSINESSADDRESSTYPECODEID is null
            begin
              raiserror('You must define the default primary business address type code.',16,1);
              return -2;
            end

          select top 1
            @RELATIONSHIPTYPECODEID = [PRIMARYBUSINESSRELATIONSHIPTYPECODEID], 
            @RECIPROCALTYPECODEID = [PRIMARYBUSINESSRECIPROCALTYPECODEID]
          from
            dbo.NETCOMMUNITYDEFAULTCODEMAP;

          if @RELATIONSHIPTYPECODEID is null or @RECIPROCALTYPECODEID is null
            begin
              raiserror('You must define the default relationship type codes for primary business relationships.',16,1);
              return -2;
            end

          begin try
                -- We will have a primary business

                set @ORGID = @ORGLINKID;

                if @ORGID is null
                  begin
                    -- We need a new primary business

                    set @ORGID = newid();
                    insert into dbo.CONSTITUENT 
                    (
                      ID,
                      ISORGANIZATION,
                      KEYNAME,
                      ADDEDBYID,
                      CHANGEDBYID,  
                      DATEADDED,  
                      DATECHANGED
                    )
                    values
                    (
                      @ORGID,
                      1,
        @ORGNAME,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CHANGEDATE,
                      @CHANGEDATE
                    );

                    set @ORGLINKID = @ORGID; --JamesWill 09/27/2007 CR284214-092707 


                    --add origin information

                    if @INFOSOURCECODEID is not null
                    begin
                      exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @ID = @ORGLINKID,
                      @CHANGEAGENTID=@CHANGEAGENTID,
                      @INFOSOURCECODEID = @INFOSOURCECODEID,
                      @REVENUEID = null
                    end
                  end

                if @RELATIONSHIPSETID is null
                begin                
                  if @RELATIONSHIPJOBINFOID is not null
                    begin
                      set @RELATIONSHIPSETID = (select RELATIONSHIPSETID from RELATIONSHIPJOBINFO where ID = @RELATIONSHIPJOBINFOID);
                    end
                  else
                    begin
                      set @RELATIONSHIPSETID = (select RELATIONSHIPSETID 
                            from RELATIONSHIP 
                            where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and 
                              RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGID and
                              RELATIONSHIP.RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID)
                    end
                end

                if @MAKEPRIMARY = 1
                  begin
                      -- If there is an existing primary business relationship with a different org, mark it as not primary

                      update dbo.RELATIONSHIP set
                        ISPRIMARYBUSINESS = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                      where
                        (RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                        or RELATIONSHIP.RECIPROCALCONSTITUENTID = @CONSTITUENTID)
                        and RELATIONSHIP.ISPRIMARYBUSINESS = 1;

                      -- Use the existing relationship if the organization is the same

                      if @RELATIONSHIPJOBINFOID is not null
                      begin
                        select
                          @RELATIONSHIPSETID = RELATIONSHIPSETID
                        from
                          dbo.RELATIONSHIP
                        where
                          RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                          and RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGID
                          and RELATIONSHIP.ISPRIMARYBUSINESS = 0;

                        update dbo.RELATIONSHIP set
                          ISPRIMARYBUSINESS = 1,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CHANGEDATE
                        where
                          RELATIONSHIP.RELATIONSHIPSETID = @RELATIONSHIPSETID;
                      end
                  end
                else
                  begin
                    if @RELATIONSHIPSETID is not null
                      begin

                        update dbo.RELATIONSHIP set
                          ISPRIMARYBUSINESS = 0,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CHANGEDATE
                        where
                          (RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                          or RELATIONSHIP.RECIPROCALCONSTITUENTID = @CONSTITUENTID)
                          and RELATIONSHIP.RELATIONSHIPSETID = @RELATIONSHIPSETID;
                       end                      
                  end

               -- If we did not find an update an existing non-primary relationship for this org, look for an existing primary relationship

                select
                  @RELATIONSHIPID = ID
                from
                  dbo.RELATIONSHIP
                where
                  RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                  and RELATIONSHIP.RECIPROCALCONSTITUENTID = @ORGID
                  and RELATIONSHIP.RELATIONSHIPSETID = @RELATIONSHIPSETID;

                if (@RELATIONSHIPJOBINFOID is null and @RELATIONSHIPSETID is null)
                  begin                    


                    set @RELATIONSHIPSETID = newid();
                    insert into dbo.RELATIONSHIPSET
                    (
                      ID,
                      ADDEDBYID,
                      CHANGEDBYID,
                      DATEADDED,
                      DATECHANGED
                    )
                    values
                    (
                      @RELATIONSHIPSETID,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CHANGEDATE,
                      @CHANGEDATE
                    );


                    set @RELATIONSHIPID = newid();
                    insert into dbo.RELATIONSHIP
                    (
                      ID,
                      RELATIONSHIPSETID,
                      RELATIONSHIPCONSTITUENTID,
                      RECIPROCALCONSTITUENTID,
                      RELATIONSHIPTYPECODEID,
                      RECIPROCALTYPECODEID,
                      ISPRIMARYBUSINESS,
                      ISCONTACT,
                      CONTACTTYPECODEID,
                      ISMATCHINGGIFTRELATIONSHIP,
                      COMMENTS,
                      ADDEDBYID,
                      CHANGEDBYID,
                      DATEADDED,
                      DATECHANGED
                    )
                    values
                    (
                      @RELATIONSHIPID,
                      @RELATIONSHIPSETID,                                            
                      @CONSTITUENTID,
                      @ORGID,
                      @RELATIONSHIPTYPECODEID,
                      @RECIPROCALTYPECODEID,                                            
                      @MAKEPRIMARY,
                      @ISCONTACT,
                      @CONTACTTYPE,
                      @MATCHESGIFTS,
                      @COMMENTS,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CHANGEDATE,
                      @CHANGEDATE
                    );

                                        declare @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0
                                        declare @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100
                                        declare @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null
                                        declare @PRIMARYCONSTITUENTTYPE tinyint = 0
                                        declare @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0
                                        declare @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100
                                        declare @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null
                                        declare @RECIPROCALCONSTITUENTTYPE tinyint = 0

                                        select @PRIMARYCONSTITUENTTYPE = case 
                                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household

                                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group

                                            when ISORGANIZATION = 1 then 1 --Organization

               else 0 end --Individual

                                        from dbo.CONSTITUENT where ID = @CONSTITUENTID;

                                        select @RECIPROCALCONSTITUENTTYPE = case 
                                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISGROUP(ID) = 1 then 3 --Household

                                            when ISGROUP = 1 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1 then 2 -- Group

                                            when ISORGANIZATION = 1 then 1 --Organization

                                            else 0 end --Individual

                                        from dbo.CONSTITUENT where ID = @ORGID;

                                        -- add default recognition credits if creating a new relationship

                                        select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                                            @PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                            @PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                                        where RRD.CONSTITUENTTYPECODE=@PRIMARYCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RELATIONSHIPTYPECODEID

                                        select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                                            @RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                            @RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                                        where RRD.CONSTITUENTTYPECODE=@RECIPROCALCONSTITUENTTYPE and RRD.RELATIONSHIPTYPECODEID=@RECIPROCALTYPECODEID

                                        exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @ORGID,
                                            NULL, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
                                            @PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 
                                            @RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;

                  end
                else
                  begin
                    update 
                      dbo.RELATIONSHIP
                    set
                      [ISPRIMARYBUSINESS] = @MAKEPRIMARY,
                      [ISCONTACT] = @ISCONTACT, 
                      [CONTACTTYPECODEID] = @CONTACTTYPE,
                      [ISMATCHINGGIFTRELATIONSHIP] = @MATCHESGIFTS,
                      [COMMENTS] = @COMMENTS,
                      [CHANGEDBYID] = @CHANGEAGENTID,
                      [DATECHANGED] = @CHANGEDATE
                    where
                    [RELATIONSHIPSETID] = @RELATIONSHIPSETID;

                    --Add relationshipsetid if missing

                    if @RELATIONSHIPSETID is null
                      exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @RELATIONSHIPID
                  end


                --An update is added if no position is passed then the current is removed.

                if len(coalesce(@POSITION, '')) > 0
                begin
                  --add if not present

                  if @RELATIONSHIPJOBINFOID is null
                    insert into dbo.RELATIONSHIPJOBINFO
                    (
                      RELATIONSHIPSETID,
                      JOBTITLE,
                      STARTDATE,
                      ENDDATE,
                      JOBDIVISION,
             JOBDEPARTMENT,
                      JOBCATEGORYCODEID,
                      CAREERLEVELCODEID,
                      JOBSCHEDULECODEID,
                      ADDEDBYID,
                      CHANGEDBYID,
                      DATEADDED,
                      DATECHANGED,
                      JOBRESPONSIBILITY,
                      ISPRIVATERECORD
                    )
                    values
                    (
                      @RELATIONSHIPSETID,
                      @POSITION,
                      @STARTDATE,
                      @ENDDATE,
                      @DIVISION,
                      @DEPARTMENT,
                      @JOBCATEGORYCODEID,
                      @CAREERLEVELCODEID,
                      @JOBSCHEDULECODEID,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CHANGEDATE,
                      @CHANGEDATE,
                      @RESPONSIBILITY,
                      @ISPRIVATERECORD
                    );
                  else
                    update 
                      dbo.RELATIONSHIPJOBINFO
                    set
                      [JOBTITLE] = @POSITION,
                      [STARTDATE] = @STARTDATE,
                      [ENDDATE] = @ENDDATE,
                      [JOBDIVISION] = @DIVISION,
                      [JOBDEPARTMENT] = @DEPARTMENT,
                      [JOBCATEGORYCODEID] = @JOBCATEGORYCODEID,
                      [CAREERLEVELCODEID] = @CAREERLEVELCODEID,
                      [JOBSCHEDULECODEID] = @JOBSCHEDULECODEID,
                      [CHANGEDBYID] = @CHANGEAGENTID,
                      [DATECHANGED] = @CHANGEDATE,
                      [JOBRESPONSIBILITY] = @RESPONSIBILITY,
                      [ISPRIVATERECORD] = @ISPRIVATERECORD
                    where
                    [ID] = @RELATIONSHIPJOBINFOID;
                end
                else
                  exec dbo.USP_RELATIONSHIPJOBINFO_DELETEBYID_WITHCHANGEAGENTID @RELATIONSHIPJOBINFOID, @CHANGEAGENTID;

                -- Now ORGID points to the correct record and RELATIONSHIPID points

                --  to the correct relationship but the data that hangs off of it

                --  may need to be updated.


                if @UPDATEINDUSTRYCODEID = 1
                  if exists (select 1 from dbo.ORGANIZATIONDATA where [ID] = @ORGID)
                    update dbo.ORGANIZATIONDATA set
                      INDUSTRYCODEID = @INDUSTRYCODEID,
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED = @CHANGEDATE
                    where
                      ID = @ORGID
                      and
                      (
                        (INDUSTRYCODEID is null and @INDUSTRYCODEID is not null)
                        or
                        (INDUSTRYCODEID is not null and @INDUSTRYCODEID is null)
                        or
                        INDUSTRYCODEID <> @INDUSTRYCODEID
                      );
                  else
                    if @INDUSTRYCODEID is not null
                      insert into dbo.ORGANIZATIONDATA
                      (
                        ID,
                        INDUSTRYCODEID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                      )
                      values
                      (
                        @ORGID,
                        @INDUSTRYCODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                      );


                --If it's a new primary business that didn't have an existing relationship before, add address

                if @MAKEPRIMARY = 1 
                  and @RELATIONSHIPJOBINFOID is null 
                  and exists (select 1 from dbo.ADDRESS where [CONSTITUENTID] = @ORGID)
                begin
                  declare @CONSTITADDRESSID uniqueidentifier;
                  set @CONSTITADDRESSID = newid();

                  declare @STATEID uniqueidentifier;
                  declare @COUNTRYID uniqueidentifier;
                  declare @ADDRESSBLOCK nvarchar(300);
                  declare @CITY nvarchar(100);
                  declare @POSTCODE nvarchar(24);

                  select top (1)
                    @STATEID = STATEID,
                    @COUNTRYID = COUNTRYID,
                    @ADDRESSBLOCK = ADDRESSBLOCK,
                    @CITY = CITY,
                    @POSTCODE = POSTCODE
                  from dbo.ADDRESS
                  where [CONSTITUENTID] = @ORGID;

                  insert into dbo.ADDRESS
                    (
                      ID,
                      CONSTITUENTID,
                      ADDRESSTYPECODEID,
                      ISPRIMARY,
                      COUNTRYID,
                      STATEID,
                      ADDRESSBLOCK,
                      CITY,
                      POSTCODE,
                      ADDEDBYID,
                      CHANGEDBYID,
                      DATEADDED,
                      DATECHANGED
                    )
                    values
                    (
                        @CONSTITADDRESSID,
                        @CONSTITUENTID,
                        @BUSINESSADDRESSTYPECODEID,
                        0,
                        coalesce(@COUNTRYID, dbo.UFN_COUNTRY_GETDEFAULT()),
                        @STATEID,
                        @ADDRESSBLOCK,
                        @CITY,
                        @POSTCODE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    );

                    insert into dbo.ADDRESSVALIDATIONUPDATE
                    (
                      ID,
                      INFOSOURCECODEID,
                      ADDEDBYID,
                      CHANGEDBYID,
                      DATEADDED,
                      DATECHANGED
                    )
                    values
                    (
                      @CONSTITADDRESSID,
                      @INFOSOURCECODEID,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CHANGEDATE,
                      @CHANGEDATE
                    );


                end               

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

          return 0;