USP_SPONSORSHIPLOCATION_CLOSE

CREATE procedure dbo.USP_SPONSORSHIPLOCATION_CLOSE
(
    @SPONSORSHIPLOCATIONID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output
)
as begin
    set nocount on;

    declare @MATCHRULE tinyint        -- 0=Standard, 1=Nearest location

    declare @SPONSORSHIPREASONID uniqueidentifier
    declare @COMMENT nvarchar(255)
    declare @PROGRAMMAPPINGSXML xml
    declare @PROGRAMMAPPINGS table (FROMSPONSORSHIPPROGRAMID uniqueidentifier,
                                    TOSPONSORSHIPPROGRAMID uniqueidentifier,
                                    TOSPONSORSHIPLOCATIONID uniqueidentifier)

    --default

    set @MATCHRULE = 0
    -- Find the match rule custom setting

    select top 1
        @MATCHRULE = LOCATIONCLOSEMATCHINGCODE
    from dbo.SPONSORSHIPINFO
    order by DATEADDED

    select @SPONSORSHIPREASONID = SPONSORSHIPREASONID,
           @PROGRAMMAPPINGSXML = PROGRAMMAPPINGS,
                 @COMMENT = COMMENT
    from dbo.SPONSORSHIPLOCATIONCLOSEPROCESS
    where ID = @SPONSORSHIPLOCATIONID;

    insert into @PROGRAMMAPPINGS
    select *
    from dbo.UFN_SPONSORSHIPLOCATIONCLOSEPROCESS_PROGRAMMAPPINGS_FROMITEMLISTXML(@PROGRAMMAPPINGSXML)

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

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

    declare @HIERARCHYPATH hierarchyid

    select @HIERARCHYPATH = HIERARCHYPATH
    from dbo.SPONSORSHIPLOCATION
    where ID = @SPONSORSHIPLOCATIONID

    begin try
        ------------------------------------------------------------------

        -- Update all opportunities in the area to unavailable and mark the location inactive.

        exec dbo.USP_SPONSORSHIPLOCATION_INACTIVE_REASON @SPONSORSHIPLOCATIONID, @CHANGEAGENTID, @SPONSORSHIPREASONID, @COMMENT

        ------------------------------------------------------------------

        -- Transfer all sponsorships for the location to another location.


        create table #TRANSFERS (
            FROMSPONSORSHIPID uniqueidentifier,
            NEWSPONSORSHIPPROGRAMID uniqueidentifier,
            NEWSPONSORSHIPLOCATIONID uniqueidentifier,
            NEWSPONSORSHIPOPPORTUNITYID uniqueidentifier,
            NEWCHILDGENDERCODE tinyint,
            NEWSPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier,
            NEWISHIVPOSITIVECODE tinyint,
            NEWHASCONDITIONCODE tinyint,
            NEWISORPHANEDCODE tinyint,
            NEWSPROPPPROJECTCATEGORYCODEID uniqueidentifier);

        insert into #TRANSFERS (FROMSPONSORSHIPID, NEWSPONSORSHIPPROGRAMID, NEWSPONSORSHIPLOCATIONID)
        select
            SPONSORSHIP.ID,
            MAPPINGS.TOSPONSORSHIPPROGRAMID,
            MAPPINGS.TOSPONSORSHIPLOCATIONID
        from dbo.SPONSORSHIP
        inner join @PROGRAMMAPPINGS MAPPINGS on MAPPINGS.FROMSPONSORSHIPPROGRAMID = SPONSORSHIP.SPONSORSHIPPROGRAMID
        inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
        where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
        and SPONSORSHIP.STATUSCODE = 1
        union
        -- pending transfers

        select
            ACTIVESPONSORSHIP.ID,
            MAPPINGS.TOSPONSORSHIPPROGRAMID,
            MAPPINGS.TOSPONSORSHIPLOCATIONID
        from dbo.SPONSORSHIP PENDINGSPONSORSHIP
        inner join dbo.SPONSORSHIPOPPORTUNITY PENDINGSPONSORSHIPOPPORTUNITY on PENDINGSPONSORSHIPOPPORTUNITY.ID = PENDINGSPONSORSHIP.SPONSORSHIPOPPORTUNITYID
        inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = PENDINGSPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
        inner join dbo.SPONSORSHIPTRANSACTION INITIATETRANSFERTRANSACTION on INITIATETRANSFERTRANSACTION.TARGETSPONSORSHIPID = PENDINGSPONSORSHIP.ID and INITIATETRANSFERTRANSACTION.ACTIONCODE = 6
        inner join dbo.SPONSORSHIP ACTIVESPONSORSHIP on ACTIVESPONSORSHIP.ID = INITIATETRANSFERTRANSACTION.CONTEXTSPONSORSHIPID
        inner join @PROGRAMMAPPINGS MAPPINGS on MAPPINGS.FROMSPONSORSHIPPROGRAMID = ACTIVESPONSORSHIP.SPONSORSHIPPROGRAMID
        where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
        and PENDINGSPONSORSHIP.STATUSCODE = 0
        and ACTIVESPONSORSHIP.STATUSCODE = 1

        exec dbo.USP_SPONSORSHIP_BULKTRANSFER
            @SUCCESSTABLE,
            @EXCEPTIONTABLE,
            0,
            @MATCHRULE,
            @SPONSORSHIPREASONID,
            1,
            @SPONSORSHIPLOCATIONID,
            0,
            @CHANGEAGENTID,
            @SUCCESSCOUNT output,
            @EXCEPTIONCOUNT output,
      1,
      0

        drop table #TRANSFERS;

        if @EXCEPTIONCOUNT = 0
        begin
            ------------------------------------------------------------------

            -- Mark opportunity groups and programs that reference the location as closed.

            declare @GROUPS table (ID uniqueidentifier)

            insert into @GROUPS (ID)
            select SPONSORSHIPOPPORTUNITYGROUP.ID
            from dbo.SPONSORSHIPOPPORTUNITYGROUP
            inner join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPLOCATIONID
            where SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1;

            update dbo.SPONSORSHIPPROGRAM
            set ISINACTIVE = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where SPONSORSHIPOPPORTUNITYGROUPID in(select ID from @GROUPS);

            update dbo.SPONSORSHIPOPPORTUNITYGROUP
            set ISINACTIVE = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID in(select ID from @GROUPS);

            ------------------------------------------------------------------

            -- Mark the location as closed.

            update dbo.SPONSORSHIPLOCATION
            set STATUSCODE = 2,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE,
                SPONSORSHIPREASONID = @SPONSORSHIPREASONID,
                COMMENT = case ID when @SPONSORSHIPLOCATIONID then @COMMENT else COMMENT end
            where ID = @SPONSORSHIPLOCATIONID
            or HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1;
        end

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

    return 0;
end