USP_DATAFORMTEMPLATE_ADD_OPPORTUNITYREVENUE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_OPPORTUNITYREVENUE (
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @OPPORTUNITYID uniqueidentifier,
    @RECORDID uniqueidentifier = null
)
as
    set nocount on;

    -- since multiple records can get generated, the ID returned will just be the opportunity's ID
    set @ID = @OPPORTUNITYID;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try

        declare @EARLIESTREVENUEDATE datetime;
        declare @REVENUETOAPPLY table
        (
            REVENUESPLITID uniqueidentifier
        );

    -- WI 1270999- Add matching gift payment on selecting existing MG from opportunity. 
    -- Adding matching gift from existing revenue on opportunity and then add a payment on selected MG does same thing.
    -- Insert matching gift payments
     -- WI 1125275 
    -- Adding pledge payment from existing revenue on opportunity --applicationcode=2
    insert into @REVENUETOAPPLY
    (
       REVENUESPLITID
    )
    select RSE.ID from dbo.REVENUESPLIT RS
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = RS.ID 
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = FTLI.ID
    where 
    RS.REVENUEID  = @RECORDID and
    FT.DELETEDON is null and
    FT.TYPECODE = 0 and
    RSE.APPLICATIONCODE in(7,2);        

        -- First, grab all of the revenue splits attached to pledges
        insert into @REVENUETOAPPLY 
        (
            REVENUESPLITID
        )
        select 
            REVENUESPLIT.ID
        from
            dbo.REVENUE
        inner join
            dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
        where
            REVENUE.ID = @RECORDID;

        -- Second, grab all of the revenue splits for gifts
        insert into @REVENUETOAPPLY
        (
            REVENUESPLITID
        )
        select
            REVENUESPLIT.ID
        from
            dbo.REVENUESPLIT
        where
            REVENUESPLIT.ID = @RECORDID;

        select
            @EARLIESTREVENUEDATE = min(REVENUE.DATE)
        from
            @REVENUETOAPPLY [REVENUETOAPPLY]
        inner join
            dbo.REVENUESPLIT on REVENUESPLIT.ID = [REVENUETOAPPLY].REVENUESPLITID
        inner join
            dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID;

        declare @REVENUESPLITID uniqueidentifier;
        declare SPLITCURSOR cursor local fast_forward for
            select
                REVENUESPLITID
            from
                @REVENUETOAPPLY

            Union all

            select
                FINANCIALTRANSACTIONLINEITEM.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM
                left join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID  
            where FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = SOURCE.ID  
                and SOURCE.FINANCIALTRANSACTIONID = @RECORDID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null; 

        open SPLITCURSOR;
        fetch next from SPLITCURSOR into @REVENUESPLITID;
        while @@FETCH_STATUS = 0
        begin
            exec dbo.USP_REVENUESPLIT_PULLSOLICITORSFROMOPPORTUNITY @REVENUESPLITID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE;    

            fetch next from SPLITCURSOR into @REVENUESPLITID;
        end
        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
        close SPLITCURSOR;
        deallocate SPLITCURSOR;

        insert into dbo.REVENUEOPPORTUNITY 
        (
            ID,
            OPPORTUNITYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            REVENUESPLITID,
            @ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            @REVENUETOAPPLY;

        exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @EARLIESTREVENUEDATE, @CHANGEAGENTID, @CURRENTDATE;        

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

    return 0;