USP_SPONSOR_ADDPAYMENT

            CREATE procedure dbo.USP_SPONSOR_ADDPAYMENT
(
     @REVENUEID        uniqueidentifier
    ,@APPLICATIONID    uniqueidentifier
    ,@APPLIEDAMOUNT    money
    ,@CREATIONDATE    datetime = null
    ,@CHANGEAGENTID    uniqueidentifier = null
    ,@BASEAPPLIEDAMOUNT money = null
    ,@ORGANIZATIONAPPLIEDAMOUNT money = null
)
as
begin

    set nocount on

    declare @EVENTID                uniqueidentifier = null;
    declare @APPLICATIONCURRENCYID    uniqueidentifier;
    declare @PAYMENTBASECURRENCYID    uniqueidentifier;
    declare @AMOUNTCONVERTED        money;
    declare @REVENUEDATE            datetime;
    declare @APPLICATIONEXCHANGERATEID        uniqueidentifier;
    declare @PAYMENTTRANSACTIONCURRENCYID    uniqueidentifier;    
    declare @PAYMENTBASEEXCHANGERATEID        uniqueidentifier;
    declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @POSTDATE date;
    declare @POSTSTATUSCODE tinyint;
    declare @DESIGNATIONID uniqueidentifier;

    -- pick up eventid from sponsor table

    select 
       @EVENTID = EVENTSPONSOR.EVENTID,
       @APPLICATIONCURRENCYID = EVENT.BASECURRENCYID
    from 
        dbo.EVENTSPONSOR
        inner join dbo.EVENT on EVENTSPONSOR.EVENTID = EVENT.ID
    where 
        EVENTSPONSOR.ID = @APPLICATIONID; 

    set @DESIGNATIONID = 
    (            SELECT A.DESIGNATIONID
                FROM dbo.EVENT E 
                INNER JOIN dbo.APPEALDESIGNATION A
                ON E.APPEALID = A.APPEALID AND A.ISDEFAULT = 1
                WHERE E.ID = @EVENTID
    )

    -- get currency values from the event registration payment

    select
        @PAYMENTTRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
        @PAYMENTBASECURRENCYID = V.BASECURRENCYID,
        @PAYMENTBASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
        @PAYMENTORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID,
        @REVENUEDATE = cast(FT.DATE as datetime),
        @POSTDATE = FT.POSTDATE,
        @POSTSTATUSCODE = case FT.POSTSTATUSCODE when 2 then 1 else FT.POSTSTATUSCODE end
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID
    where FT.ID = @REVENUEID;

    -- if the event currency does not equal the payment transaction currency, get an exchange rate from payment transaction currency 

    -- to event currency

    if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
    begin
        if @APPLICATIONCURRENCYID = @PAYMENTBASECURRENCYID
            set @APPLICATIONEXCHANGERATEID = @PAYMENTBASEEXCHANGERATEID;
        else
            set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(
                                                 @PAYMENTTRANSACTIONCURRENCYID
                                                ,@APPLICATIONCURRENCYID
                                                ,@REVENUEDATE
                                                ,1
                                                ,null);

        -- no exchange rate exists - error

        if @APPLICATIONEXCHANGERATEID is null
        begin
            raiserror('BBERR_APPLICATIONEXCHANGERATEDOESNOTEXIST : You cannot apply the payment toward this application. No exchange rate exists between the payment and application currencies.', 13, 1);
            return 1;
        end
    end

    --If the payment is not in the same currency as the event sponsorship, convert the payment amount to the event sponsor's currency

    if @APPLICATIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID
        set @AMOUNTCONVERTED = @APPLIEDAMOUNT;
    else
        set @AMOUNTCONVERTED = dbo.UFN_CURRENCY_CONVERT(@APPLIEDAMOUNT, @APPLICATIONEXCHANGERATEID);

    declare @REVENUESPLITID uniqueidentifier;
    set        @REVENUESPLITID = newid();

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

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

    -- Convert the applied amount into base and organization amounts if it is not provided by the caller

    if @BASEAPPLIEDAMOUNT is null or @ORGANIZATIONAPPLIEDAMOUNT is null
        exec dbo.USP_CURRENCY_GETCURRENCYVALUES 
            @APPLIEDAMOUNT, 
            @REVENUEDATE, 
            @PAYMENTBASECURRENCYID, 
            @PAYMENTBASEEXCHANGERATEID output, 
            @PAYMENTTRANSACTIONCURRENCYID output, 
            @BASEAPPLIEDAMOUNT output, 
            null, 
            @ORGANIZATIONAPPLIEDAMOUNT output, 
            @PAYMENTORGANIZATIONEXCHANGERATEID output, 
            0;

    begin try                            

        declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
        declare @ADJUSTMENTPOSTDATE date;
        declare @ADJUSTMENTID uniqueidentifier;
        select top 1 
            @ADJUSTMENTPOSTSTATUSCODE = case A.POSTSTATUSCODE when 2 then 3 else 1 end
            ,@ADJUSTMENTPOSTDATE = A.POSTDATE
            ,@ADJUSTMENTID = ALI.ID
        from dbo.ADJUSTMENT A
        left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT ALI on A.ID = ALI.ID
        where A.REVENUEID = @REVENUEID
        order by A.DATEADDED desc;

        insert into dbo.FINANCIALTRANSACTIONLINEITEM 
            (ID
            ,FINANCIALTRANSACTIONID
            ,TRANSACTIONAMOUNT
            ,VISIBLE
            ,DESCRIPTION
            ,SEQUENCE
            ,TYPECODE
            ,POSTDATE
            ,POSTSTATUSCODE
            ,BASEAMOUNT
            ,ORGAMOUNT
            ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
            -- Boilerplate

            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
            (@REVENUESPLITID
            ,@REVENUEID
            ,@APPLIEDAMOUNT
            ,1
            ,''
            ,1
            ,0
            ,isnull(@ADJUSTMENTPOSTDATE, @POSTDATE)
            ,isnull(@ADJUSTMENTPOSTSTATUSCODE, @POSTSTATUSCODE)
            ,@BASEAPPLIEDAMOUNT
            ,@ORGANIZATIONAPPLIEDAMOUNT
            ,@ADJUSTMENTID
            ,@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE)

        merge dbo.REVENUESPLIT_EXT as target
        using (select @REVENUESPLITID [ID]) as source      
        on (source.ID = target.ID)      
            when matched then
        update set 
            DESIGNATIONID = @DESIGNATIONID
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CREATIONDATE
        when not matched then
            insert 
            (
                ID
                ,DESIGNATIONID
                ,TYPECODE
                ,APPLICATIONCODE
                ,OVERRIDEBUSINESSUNITS
                ,REVENUESPLITBUSINESSUNITOVERRIDECODEID
                -- boilerplate

                ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            values 
            (    
                @REVENUESPLITID
                ,@DESIGNATIONID
                ,15
                ,15
                ,0
                ,null
                --  boilerplate

                ,@CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE);

        /* 
        We may need to add sponsor payments in a new table: EVENTSPONSORPAYMENT
        insert into dbo.EVENTSPONSORPAYMENT
        (
             ID
            ,PAYMENTID
            ,SPONSORID
            ,AMOUNT
            ,APPLICATIONCURRENCYID
            ,APPLICATIONEXCHANGERATEID
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
        )
        values (
             newid()
            ,@REVENUESPLITID
            ,@APPLICATIONID 
            ,@AMOUNTCONVERTED
            ,@APPLICATIONCURRENCYID
            ,@APPLICATIONEXCHANGERATEID
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CREATIONDATE
            ,@CREATIONDATE
        );
        */
    end try                
    begin catch
        exec dbo.USP_RAISE_ERROR;
    end catch

    -- create recognitions

    exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @REVENUESPLITID, @CHANGEAGENTID, @CREATIONDATE;
end