USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP

    CREATE procedure dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP
    (
        @NONPARENTORGID uniqueidentifier,
        @PARENTORGID uniqueidentifier,
        @STARTDATE date,
        @CHANGEAGENTID uniqueidentifier,
        @CHANGEDATE datetime,
        @ADDINITIALRELATIONSHIP bit,
        @OLDPARENTORGID uniqueidentifier,
        @RELATIONSHIPENDDATE date = null
    )
    as

        set nocount on;

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

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

        if @ADDINITIALRELATIONSHIP is null
            set @ADDINITIALRELATIONSHIP = 0

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

        declare @PARENTOFPARENTCHECK bit;
        declare @TOPPARENTCHECK bit;
        declare @PARENTOFPARENTORGID uniqueidentifier
        declare @PARENTOFPARENTSUBSIDIARYORGID uniqueidentifier
        declare @TOPPARENTORGID uniqueidentifier
        declare @TOPSUBSIDIARYORGID uniqueidentifier
        declare @PARENTORGCODEID uniqueidentifier
        declare @SUBSIDIARYORGCODEID uniqueidentifier
        declare @SETENDDATE bit

        select  @PARENTOFPARENTORGID=PARENTOFPARENTORGID,
                @PARENTOFPARENTSUBSIDIARYORGID=PARENTOFPARENTSUBSIDIARYORGID,
                @TOPPARENTORGID = TOPPARENTORGID,
                @TOPSUBSIDIARYORGID = TOPSUBSIDIARYORGID,
                @PARENTOFPARENTCHECK=CREATEPARENTOFPARENTRELATIONSHIP,
                @TOPPARENTCHECK = CREATETOPPARENTRELATIONSHIP,
                @PARENTORGCODEID = PARENTORGID,
                @SUBSIDIARYORGCODEID = SUBSIDIARYORGID,
                @SETENDDATE = SETENDDATE
        from dbo.RELATIONSHIPCONFIGURATIONCORPORATE

        --If there is no row in the RELATIONSHIPCONFIGURATIONCORPORATE table then create the default relationship types.

        if @PARENTORGCODEID is null
        begin

            declare @PARENTTYPEID uniqueidentifier = (select ID from dbo.RELATIONSHIPTYPECODE where DESCRIPTION = 'Parent Corporation')
            declare @SUBSIDIARYTYPEID uniqueidentifier = (select ID from dbo.RELATIONSHIPTYPECODE where DESCRIPTION = 'Subsidiary')

            if @PARENTTYPEID is null
            begin
                set @PARENTTYPEID = newid()
                exec USP_DATAFORMTEMPLATE_ADD_RELATIONSHIPCONFIGURATION @PARENTTYPEID,@CHANGEAGENTID,'Parent Corporation',0,1,1,1,1,1,1,1,1,0,0
            end

            if @SUBSIDIARYTYPEID is null
            begin
                set @SUBSIDIARYTYPEID = newid()
                exec USP_DATAFORMTEMPLATE_ADD_RELATIONSHIPCONFIGURATION @SUBSIDIARYTYPEID,@CHANGEAGENTID,'Subsidiary',0,1,1,1,1,1,1,1,1,0,0
            end

            insert into dbo.RELATIONSHIPCONFIGURATIONCORPORATE (ID,PARENTORGID,SUBSIDIARYORGID,CREATEPARENTOFPARENTRELATIONSHIP,CREATETOPPARENTRELATIONSHIP,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values (newid(),@PARENTTYPEID,@SUBSIDIARYTYPEID,0,0,@CHANGEAGENTID,@CHANGEAGENTID,getDate(),getDate());

            set @PARENTORGCODEID = @PARENTTYPEID
            set @SUBSIDIARYORGCODEID = @SUBSIDIARYTYPEID

         end


        --Bug 401235. Prevent corporate structures that include cycles. Allowing cycles the corporate structure

        --leads to infinite recursion in the CTEs used later in this stored procedure and it doesn't make any

        --business sense to allow them. This check is needed in the case where @NONPARENTORGID and the

        --new parent were in the same parent corporation tree before this edit was started and the edit

        --is just rearranging the hierarchy. To check for this, we allow the first repeated row into the 

        --RECURSIVEPARENTCHECK_CTE, but we stop the recursion after that; then we check for a duplicated in the

        --CTE results.

        declare @NONPARENTORGANDANCESTORS table
        (
            ID uniqueidentifier,
            PARENTCORPID uniqueidentifier,
            PARENTCORPISREPEAT bit
        );

        with RECURSIVEPARENTCHECK_CTE
        as
        (
            select
                ORGANIZATIONDATA.ID,
                ORGANIZATIONDATA.PARENTCORPID,
                cast(0 as bit) as PARENTCORPISREPEAT
            from
                dbo.ORGANIZATIONDATA
            where
                ORGANIZATIONDATA.ID = @NONPARENTORGID

            union all

            select
                ORGANIZATIONDATA.ID,
                ORGANIZATIONDATA.PARENTCORPID,
                cast
                    (
                        case
                            when ORGANIZATIONDATA.ID = @NONPARENTORGID
                                then 1
                            else
                                0
                        end
                        as bit
                    ) as PARENTCORPISREPEAT

            from
                dbo.ORGANIZATIONDATA
                inner join RECURSIVEPARENTCHECK_CTE on RECURSIVEPARENTCHECK_CTE.PARENTCORPID = ORGANIZATIONDATA.ID
            where
                RECURSIVEPARENTCHECK_CTE.PARENTCORPISREPEAT = 0
        )
        insert into @NONPARENTORGANDANCESTORS
        (
            ID,
            PARENTCORPID,
            PARENTCORPISREPEAT
        )
        select
            RECURSIVEPARENTCHECK_CTE.ID,
            RECURSIVEPARENTCHECK_CTE.PARENTCORPID,
            RECURSIVEPARENTCHECK_CTE.PARENTCORPISREPEAT
        from
            RECURSIVEPARENTCHECK_CTE;

        if exists (select 1 from @NONPARENTORGANDANCESTORS as NONPARENTORGANDANCESTORS where NONPARENTORGANDANCESTORS.PARENTCORPISREPEAT = 1)
        begin
            raiserror(N'BBERR_PARENTCOPRRECURSIONDETECTED : The selected parent corporation is already part of the corporate structure as a subsidiary. To make this organization a parent in the corporate structure, first remove it as a subsidiary.', 16, 1);
            return 1;
        end;


        --Set an end date if provided or default to today for all no longer valid relationships after the parent org change

        if @OLDPARENTORGID is not null and @SETENDDATE = 1
        begin

            declare @OLDTABLE table(ID uniqueidentifier);

            --If the @NONPARENTORGID record (i.e. the subsidiary) currently has a parent, this @OLDTABLE 

            --will contain the ID of that parent and all of its parents. This will be empty if the 

            --@NONPARENTORGID is getting a parent for the first time (i.e. @OLDPARENTORGID is empty guid).

            with OldParents (ID,PARENTCORPID)
            as
            (
                --Start with the old parent organization

                select OD.ID,OD.PARENTCORPID
                from dbo.ORGANIZATIONDATA OD
                where OD.ID = @OLDPARENTORGID

                union all

                --Recursively go up the chain of parents

                select OD.ID,OD.PARENTCORPID
                from dbo.ORGANIZATIONDATA OD
                inner join Oldparents on OldParents.PARENTCORPID = OD.ID
            )
            insert into @OLDTABLE select ID from OldParents;

            -- @ORGCHILDREN will contain all of the children of the @NONPARENTORGID (i.e. the subsidiary)

            --as well as their children. The entire subtree beneath the @NONPARENTORGID.

            declare @ORGCHILDREN table(ID uniqueidentifier);

            with OrgChildren
            as
            (
                --Bug 392756 Now including the @NONPARENTORGID in addition to its descendants.

                --There was a second statement that attempted to update the @NONPARENTORGID, but

                --it did too much, so instead we can roll it into this statement.

                select @NONPARENTORGID as ID

                union all

                --Recursively go down the chain of children

                select OD.ID
                from OrgChildren as OC
                inner join dbo.ORGANIZATIONDATA OD on OD.PARENTCORPID = OC.ID
            )
            insert into @ORGCHILDREN select ID from OrgChildren;

            --Identifies all of the relationships between descendants (inclusive) and ancestors. Descendants

            --are @NONPARENTORGID, any child of @NONPRENTORGID, and any descendent further down the chain.

            --Ancestors are the record currently recorded as the parent of @NONPARENTORGID (passed in as @OLDPARENTORGID)

            --and its parents up the chain.

            --Those relationships could have been created by the @PARENTOFPARENTCHECK setting, the @TOPPARENTCHECK

            --setting, or manually. We want to end date all of them when the @SETENDDATE setting is on event though they may

            --have different sources. This is the long-standing behavior, but a more robust feature set may have a more nuanced

            --approach to dealing with these different relationships; possibly considering type codes.

            update  dbo.RELATIONSHIP
                set ENDDATE = @RELATIONSHIPENDDATE
            from dbo.RELATIONSHIP
            inner join @ORGCHILDREN OC on OC.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
            inner join @OLDTABLE as OLDTABLE on OLDTABLE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID;

            --Bug 392756 Removed a buggy update statement from the fix for Bug 138185 that was modifying all relationships

            -- between parents of @NONPARENTORGID and all other constituents.


            -- set end date on recognition credits

            update dbo.REVENUERECOGNITIONDEFAULT
            set
                ENDDATE = @RELATIONSHIPENDDATE
            from dbo.REVENUERECOGNITIONDEFAULT    
                inner join @ORGCHILDREN OC on OC.ID = REVENUERECOGNITIONDEFAULT.SOURCECONSTITUENTID
                inner join @OLDTABLE as OLDTABLE on OLDTABLE.ID = REVENUERECOGNITIONDEFAULT.RECIPIENTCONSTITUENTID 
            where
                REVENUERECOGNITIONDEFAULT.SOURCECONSTITUENTID = OC.ID;

        end

        --Build a table of all subsidiaries of the parent and create the relationships based on the RELATIONSHIPCONFIGURATIONCORPORATE table.

        declare @TEMPTBL table
            (
                ID uniqueidentifier,
                RELATIONSHIPCONSTITUENTID uniqueidentifier,
                RELATIONSHIPTYPECODEID uniqueidentifier,
                RECIPROCALCONSTITUENTID uniqueidentifier,
                RECIPROCALTYPECODEID uniqueidentifier,
                STARTDATE datetime,
            ISSPOUSE bit,
            PARENTCORPID uniqueidentifier
            );

        if (@PARENTOFPARENTCHECK = 1 or @TOPPARENTCHECK = 1) and @PARENTORGID is not null
        begin

            with Parents (ID,PARENTCORPID)
            as
            (
                select OD.ID,OD.PARENTCORPID
                from dbo.ORGANIZATIONDATA OD
                where OD.ID = @NONPARENTORGID

                union all

                select OD.ID,OD.PARENTCORPID
                from dbo.ORGANIZATIONDATA OD
                inner join Parents on Parents.PARENTCORPID = OD.ID

            )

                insert into @TEMPTBL
            select newid() as ID,
                    @NONPARENTORGID as RELATIONSHIPCONSTITUENTID,
                    @PARENTOFPARENTSUBSIDIARYORGID as RELATIONSHIPTYPECODEID,
                    ID as RECIPROCALCONSTITUENTID,
                    @PARENTOFPARENTORGID as RECIPROCALTYPECODEID,
                    @STARTDATE,
                    0 as ISSPOUSE,
                    PARENTCORPID
            from Parents where Parents.ID <> @NONPARENTORGID and Parents.ID <> @PARENTORGID

            if @TOPPARENTCHECK = 1
            begin
                update @TEMPTBL set RELATIONSHIPTYPECODEID = @TOPSUBSIDIARYORGID,RECIPROCALTYPECODEID = @TOPPARENTORGID
                where PARENTCORPID is null
            end
        end

        -- Handle the case where only Top parent relationships are being created but not intermediate.

        -- Delete all the rows from the temptable that aren't the top parent relationship

        if @TOPPARENTCHECK = 1 and @PARENTOFPARENTCHECK = 0
        begin
        delete from @TEMPTBL
        where PARENTCORPID is not null
        end

        if @ADDINITIALRELATIONSHIP = 1 and @PARENTORGID is not null
        begin
            insert into @TEMPTBL (ID,RELATIONSHIPCONSTITUENTID,RELATIONSHIPTYPECODEID,RECIPROCALCONSTITUENTID,RECIPROCALTYPECODEID,STARTDATE,ISSPOUSE) 
            values
                                (newid(),@NONPARENTORGID,@SUBSIDIARYORGCODEID,@PARENTORGID,@PARENTORGCODEID,@STARTDATE,0);
        end


        declare @relationship xml

        set @relationship = (select         
                                ID,
                                RELATIONSHIPCONSTITUENTID,
                                RELATIONSHIPTYPECODEID,
                                RECIPROCALCONSTITUENTID,
                                RECIPROCALTYPECODEID,
                                STARTDATE,
                                ISSPOUSE
                        from @TEMPTBL for xml raw('ITEM'),type,elements,root('RELATIONSHIPS'),BINARY BASE64)

        if @relationship is not null
        begin
            --Add relationships

            exec USP_RELATIONSHIPS_ADDFROMXML @relationship,null,@CHANGEAGENTID,@CHANGEDATE;

        --Add recognition defaults

        declare @CONSTITUENTID uniqueidentifier,
            @RECIPROCALCONSTITUENTID uniqueidentifier,
            @ENDDATE datetime,
            @PRIMARYRECOGNITIONDEFAULTEXISTS bit,
            @PRIMARYRECOGNITIONDEFAULTMATCHFACTOR decimal(5,2),
            @PRIMARYRECOGNITIONDEFAULTTYPECODEID uniqueidentifier,
            @RECIPROCALRECOGNITIONDEFAULTEXISTS bit,
            @RECIPROCALRECOGNITIONDEFAULTMATCHFACTOR decimal(5,2),
            @RECIPROCALRECOGNITIONDEFAULTTYPECODEID uniqueidentifier;

        /*Grab a list of all relationships that were added along with any defaults that should be applied*/
        declare RELATIONSHIPCURSOR cursor local fast_forward for
            select RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
                RELATIONSHIP.RECIPROCALCONSTITUENTID,
                RELATIONSHIP.STARTDATE,
                RELATIONSHIP.ENDDATE,
                case when RECOGNITIONRELATIONSHIPDEFAULT.ID is null then 0 else 1 end PRIMARYRECOGNITIONDEFAULTEXISTS,
                RECOGNITIONRELATIONSHIPDEFAULT.MATCHFACTOR PRIMARYRECOGNITIONDEFAULTMATCHFACTOR,
                RECOGNITIONRELATIONSHIPDEFAULT.REVENUERECOGNITIONTYPECODEID PRIMARYRECOGNITIONDEFAULTTYPECODEID,
                case when RECIPDEFAULT.ID is null then 0 else 1 end RECIPROCALRECOGNITIONDEFAULTEXISTS,
                RECIPDEFAULT.MATCHFACTOR RECIPROCALRECOGNITIONDEFAULTMATCHFACTOR,
                RECIPDEFAULT.REVENUERECOGNITIONTYPECODEID RECIPROCALRECOGNITIONDEFAULTTYPECODEID
            from @TEMPTBL temp
            inner join dbo.RELATIONSHIP on RELATIONSHIP.ID = temp.ID
            left join dbo.RECOGNITIONRELATIONSHIPDEFAULT
                on RECOGNITIONRELATIONSHIPDEFAULT.CONSTITUENTTYPECODE = 1 and
                RECOGNITIONRELATIONSHIPDEFAULT.RELATIONSHIPTYPECODEID = temp.RELATIONSHIPTYPECODEID
            left join dbo.RECOGNITIONRELATIONSHIPDEFAULT RECIPDEFAULT
 on RECIPDEFAULT.CONSTITUENTTYPECODE = 1 and
                RECIPDEFAULT.RELATIONSHIPTYPECODEID = temp.RECIPROCALTYPECODEID;


        open RELATIONSHIPCURSOR;

            fetch next from RELATIONSHIPCURSOR into @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @STARTDATE, @ENDDATE, 
            @PRIMARYRECOGNITIONDEFAULTEXISTS, @PRIMARYRECOGNITIONDEFAULTMATCHFACTOR, @PRIMARYRECOGNITIONDEFAULTTYPECODEID,
            @RECIPROCALRECOGNITIONDEFAULTEXISTS, @RECIPROCALRECOGNITIONDEFAULTMATCHFACTOR, @RECIPROCALRECOGNITIONDEFAULTTYPECODEID;

            while (@@FETCH_STATUS = 0)
            begin

                /*Use common code to update recognition credits*/
                exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @STARTDATE, @ENDDATE, 
                @PRIMARYRECOGNITIONDEFAULTEXISTS, @PRIMARYRECOGNITIONDEFAULTMATCHFACTOR, @PRIMARYRECOGNITIONDEFAULTTYPECODEID,
                @RECIPROCALRECOGNITIONDEFAULTEXISTS, @RECIPROCALRECOGNITIONDEFAULTMATCHFACTOR, @RECIPROCALRECOGNITIONDEFAULTTYPECODEID,
                @CHANGEAGENTID, 0;

                fetch next from RELATIONSHIPCURSOR into @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @STARTDATE, @ENDDATE, 
                    @PRIMARYRECOGNITIONDEFAULTEXISTS, @PRIMARYRECOGNITIONDEFAULTMATCHFACTOR, @PRIMARYRECOGNITIONDEFAULTTYPECODEID,
                    @RECIPROCALRECOGNITIONDEFAULTEXISTS, @RECIPROCALRECOGNITIONDEFAULTMATCHFACTOR, @RECIPROCALRECOGNITIONDEFAULTTYPECODEID;

            end

        close RELATIONSHIPCURSOR;

        deallocate RELATIONSHIPCURSOR;

        end

        return 0;