USP_REVENUE_DEFAULTMARKETINGINFORMATION

CREATE procedure dbo.USP_REVENUE_DEFAULTMARKETINGINFORMATION
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
    set nocount on

    declare @SOURCECODE nvarchar(50) = '';
    declare @MAILINGID uniqueidentifier = null;
    declare @APPEALID uniqueidentifier = null;
    declare @APPLICATIONCODE tinyint;
    declare @COUNT int = 0;

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

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

    --One and only one application is applied.

    select 
        @COUNT = count(ID)
    from dbo.UFN_REVENUE_ALLAPPLICATIONS(@REVENUEID);

    if @COUNT = 1
    begin
        --Get the application code from the revenue split.

        select @APPLICATIONCODE = APPLICATIONCODE
        from dbo.REVENUESPLIT
        where REVENUEID = @REVENUEID

        if @APPLICATIONCODE=3 -- recurring gift

            select 
                @SOURCECODE = REVENUE.SOURCECODE,
                @MAILINGID = REVENUE.MAILINGID,
                @APPEALID = REVENUE.APPEALID
            from 
                dbo.REVENUESPLIT
                inner join dbo.RECURRINGGIFTACTIVITY on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                inner join dbo.REVENUE on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = REVENUE.ID
            where 
                REVENUESPLIT.REVENUEID = @REVENUEID;

        if @APPLICATIONCODE=2 -- pledge

            select 
                @SOURCECODE = REVENUE.SOURCECODE,
                @MAILINGID = REVENUE.MAILINGID,
                @APPEALID = REVENUE.APPEALID 
            from dbo.REVENUESPLIT
                inner join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                inner join dbo.REVENUE on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
            where 
                REVENUESPLIT.REVENUEID = @REVENUEID;
    end    

    --If appeal found default if one is not present

    if (@SOURCECODE is not null and @SOURCECODE <> '') or @MAILINGID is not null or @APPEALID is not null
        update dbo.REVENUE
        set 
            SOURCECODE = coalesce(nullif(SOURCECODE, ''), @SOURCECODE),
            MAILINGID = coalesce(MAILINGID, @MAILINGID),
            APPEALID = coalesce(APPEALID, @APPEALID),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
        from dbo.REVENUE
            where 
                ID = @REVENUEID 
                and (
                    SOURCECODE is null 
                    or SOURCECODE = ''
                    or MAILINGID is null
                    or APPEALID is null
                )