USP_ORGANIZATIONMERGER_MAKEORGCHILD

Used by the Organization Merger Add Data Form. Makes the organization a child organization of the parent organization passed in.
Parameters

Parameter	Parameter Type	Mode
@CHILDORGANIZATIONID	uniqueidentifier	IN
@PARENTORGANIZATIONID	uniqueidentifier	IN
@MERGERDATE	datetime	IN
@CURRENTDATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_ORGANIZATIONMERGER_MAKEORGCHILD
            (
                @CHILDORGANIZATIONID uniqueidentifier,
                @PARENTORGANIZATIONID uniqueidentifier,
                @MERGERDATE datetime,
                @CURRENTDATE datetime,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                -- end previous parent history record

                update
                    ORGANIZATIONPARENTHISTORY
                set
                    ENDDATE = DateAdd("d", -1, @MERGERDATE),
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from
                    dbo.ORGANIZATIONPARENTHISTORY
                    inner join dbo.ORGANIZATIONDATA on ORGANIZATIONPARENTHISTORY.CHILDCORPID = ORGANIZATIONDATA.ID
                        and ORGANIZATIONPARENTHISTORY.PARENTCORPID = ORGANIZATIONDATA.PARENTCORPID
                where
                    ORGANIZATIONDATA.ID = @CHILDORGANIZATIONID
                        and (ORGANIZATIONPARENTHISTORY.ENDDATE >= @MERGERDATE or ORGANIZATIONPARENTHISTORY.ENDDATE IS NULL)

                -- create new parent history record

                insert into dbo.ORGANIZATIONPARENTHISTORY
                (
                    ID, 
                    CHILDCORPID, 
                    PARENTCORPID, 
                    STARTDATE, 
                    DATEADDED, 
                    DATECHANGED, 
                    ADDEDBYID, 
                    CHANGEDBYID
                )
                values
                (
                    newid(),
                    @CHILDORGANIZATIONID,
                    @PARENTORGANIZATIONID,
                    @MERGERDATE,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                )

                declare @OLDPARENTORGID uniqueidentifier
                select @OLDPARENTORGID = PARENTCORPID
                from dbo.ORGANIZATIONDATA
                where
                    ID = @CHILDORGANIZATIONID    

                --make org child of target

                update
                    ORGANIZATIONDATA
                set
                    PARENTCORPID = @PARENTORGANIZATIONID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from
                    dbo.ORGANIZATIONDATA
                where
                    ID = @CHILDORGANIZATIONID;

                if @@ROWCOUNT = 0
                  insert into dbo.ORGANIZATIONDATA
                  (
                    ID,
                    PARENTCORPID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                  )
                  values
                  (
                    @CHILDORGANIZATIONID,
                    @PARENTORGANIZATIONID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                  );

                --create the parent/subsidiary relationships

                exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @NONPARENTORGID=@CHILDORGANIZATIONID,
                    @PARENTORGID=@PARENTORGANIZATIONID,
                    @STARTDATE=@MERGERDATE,
                    @CHANGEAGENTID=@CHANGEAGENTID,
                    @CHANGEDATE=@CURRENTDATE,
                    @ADDINITIALRELATIONSHIP=1,
                    @OLDPARENTORGID=@OLDPARENTORGID                
            end