USP_SPONSORSHIPOPPORTUNITY_MARKINELIGIBLE

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_MARKINELIGIBLE
(
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @TRANSFERTYPE tinyint = 0,        -- 0=Full, 1=Pending

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

    @TOSPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @SPONSORSHIPREASONID uniqueidentifier = null,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output
)
as begin
    set nocount on;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

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

    --Get settings for TRANSFERTYPE AND MATCHRULE

    declare @SPONSORSHIPOPPORTUNITYTYPECODE tinyint
    -- Child or Project

    select 
        @SPONSORSHIPOPPORTUNITYTYPECODE = G.SPONSORSHIPOPPORTUNITYTYPECODE
  from
      SPONSORSHIPOPPORTUNITY S
      inner join SPONSORSHIPOPPORTUNITYGROUP G on G.ID = S.SPONSORSHIPOPPORTUNITYGROUPID
  where
      S.ID = @SPONSORSHIPOPPORTUNITYID
    -- Get Settings    

    SELECT top 1
        @TRANSFERTYPE = case @SPONSORSHIPOPPORTUNITYTYPECODE when 1 then INELIGIBLETRANSFERRULE
                                                             when 2 then PROJECTCLOSETRANSFERRULE
                                        end,
      @MATCHRULE = case @SPONSORSHIPOPPORTUNITYTYPECODE when 1 then INELIGIBLEMATCHINGCODE
                                                                                                            when 2 then PROJECTCLOSEMATCHINGCODE
                                      end
    from    SPONSORSHIPINFO
    order by DATEADDED

    declare @DOTRANSFERS bit
    select @DOTRANSFERS = TRANSFERSPONSORSFORINELIGIBLECHILD
    from dbo.SPONSORSHIPREASON
    where ID = @SPONSORSHIPREASONID;

    set @SUCCESSCOUNT = 0
    set @EXCEPTIONCOUNT = 0

    begin try
        -- Mark the opportunity as ineligible.

        update dbo.SPONSORSHIPOPPORTUNITY
        set ELIGIBILITYCODE = 2,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            SPONSORSHIPREASONID = @SPONSORSHIPREASONID
        where ID = @SPONSORSHIPOPPORTUNITYID;

          -- Update the project's end date to today when marking as closed.

        update 
            dbo.SPONSORSHIPOPPORTUNITYPROJECT
        set
            ENDDATE = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @SPONSORSHIPOPPORTUNITYID

        -- Delete possible pending reasons

        delete from dbo.SPONSORSHIPOPPORTUNITYREASON
        where SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID

        -- Do transfers if appropriate.

        if @DOTRANSFERS = 1
        begin
            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, NEWSPONSORSHIPOPPORTUNITYID)
            select
                SPONSORSHIP.ID,
                @TOSPONSORSHIPOPPORTUNITYID
            from dbo.SPONSORSHIP
            inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
            left outer join dbo.SPONSORSHIPTRANSACTION INITIATETRANSFERTRANSACTION on INITIATETRANSFERTRANSACTION.CONTEXTSPONSORSHIPID = SPONSORSHIP.ID and INITIATETRANSFERTRANSACTION.ACTIONCODE = 6
            left outer join dbo.SPONSORSHIP PENDINGSPONSORSHIP on PENDINGSPONSORSHIP.ID = INITIATETRANSFERTRANSACTION.TARGETSPONSORSHIPID and PENDINGSPONSORSHIP.STATUSCODE = 0
            where SPONSORSHIPOPPORTUNITY.ID = @SPONSORSHIPOPPORTUNITYID
            and SPONSORSHIP.STATUSCODE in(0,1)
            and PENDINGSPONSORSHIP.ID is null;

            declare @SPONSORPREFERENCEOPTION tinyint;
      declare @MAINTAINLOCKONTARGETOPPORTUNITY bit;
            if @TOSPONSORSHIPOPPORTUNITYID is null
      begin
                set @SPONSORPREFERENCEOPTION = 1;
        set @MAINTAINLOCKONTARGETOPPORTUNITY = 0;
      end
            else
      begin
                set @SPONSORPREFERENCEOPTION = 2
        set @MAINTAINLOCKONTARGETOPPORTUNITY = 1;
      end

            exec dbo.USP_SPONSORSHIP_BULKTRANSFER
                @SUCCESSTABLE,
                @EXCEPTIONTABLE,
                @TRANSFERTYPE,
                @MATCHRULE,
                @SPONSORSHIPREASONID,
                0,
                null,
                1,
                @CHANGEAGENTID,
                @SUCCESSCOUNT output,
                @EXCEPTIONCOUNT output,
                @SPONSORPREFERENCEOPTION,
        @MAINTAINLOCKONTARGETOPPORTUNITY

            drop table #TRANSFERS;

      if @TOSPONSORSHIPOPPORTUNITYID is not null
        exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @TOSPONSORSHIPOPPORTUNITYID, @SUCCESSCOUNT, @CHANGEAGENTID
        end

    exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @SPONSORSHIPOPPORTUNITYID, 0, @CHANGEAGENTID
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0;
end