USP_AUCTIONPURCHASE_ADDPAYMENT

            CREATE procedure dbo.USP_AUCTIONPURCHASE_ADDPAYMENT
            (
                @REVENUEID uniqueidentifier,
                @APPLICATIONID uniqueidentifier,
                @APPLIEDAMOUNT money,
                @DATE datetime,
                @CONSTITUENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @PDACCOUNTSYSTEMID uniqueidentifier = null,
                @BASEAPPLIEDAMOUNT money = null,
                @ORGANIZATIONAPPLIEDAMOUNT money = null
            )
            as
            begin
                set nocount on;

                if @PDACCOUNTSYSTEMID is null
                    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'    

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @ORGANIZATIONCURRENCYDECIMALDIGITS integer;
                select @ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

                declare @ITEMTRANSACTIONCURRENCYID uniqueidentifier;
                declare @ITEMBASECURRENCYID uniqueidentifier;

                declare @REVENUEDATE datetime;
                declare @PAYMENTBASECURRENCYID uniqueidentifier;
                declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
                declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
                declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @PAYMENTBASECURRENCYDECIMALDIGITS integer;
                declare @PAYMENTTRANSACTIONCURRENCYDECIMALDIGITS integer;
                declare @TOTALREVENUETRANSACTIONAMOUNT money;
                declare @TOTALREVENUEBASEAMOUNT money;
                declare @TOTALREVENUEORGANIZATIONAMOUNT money;
                                declare @POSTDATE date;
                                declare @POSTSTATUSCODE tinyint;

                select
                    @REVENUEDATE = cast(REVENUE.DATE as datetime),
                    @PAYMENTBASECURRENCYID = V.BASECURRENCYID,
                    @PAYMENTTRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @PAYMENTBASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @PAYMENTORGANIZATIONEXCHANGERATEID = REVENUE.ORGEXCHANGERATEID,
                    @PAYMENTBASECURRENCYDECIMALDIGITS = BASE_CURRENCY.DECIMALDIGITS,
                    @PAYMENTTRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTION_CURRENCY.DECIMALDIGITS,
                    @TOTALREVENUETRANSACTIONAMOUNT = REVENUE.TRANSACTIONAMOUNT,
                    @TOTALREVENUEBASEAMOUNT = REVENUE.BASEAMOUNT,
                    @TOTALREVENUEORGANIZATIONAMOUNT = REVENUE.ORGAMOUNT,
                    @POSTDATE = REVENUE.POSTDATE,
                    @POSTSTATUSCODE = case REVENUE.POSTSTATUSCODE when 2 then 1 else REVENUE.POSTSTATUSCODE end
                from dbo.FINANCIALTRANSACTION REVENUE
                inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
        left join dbo.CURRENCY BASE_CURRENCY on V.BASECURRENCYID = BASE_CURRENCY.ID
        left join dbo.CURRENCY TRANSACTION_CURRENCY on REVENUE.TRANSACTIONCURRENCYID = TRANSACTION_CURRENCY.ID
                where REVENUE.ID = @REVENUEID;

                declare @REVENUESPLITID uniqueidentifier
                set @REVENUESPLITID = newid()

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

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

                declare @AUCTIONITEMRESERVATIONID uniqueidentifier = null;
                declare @DESIGNATIONID uniqueidentifier = null;
                declare @CAMPAIGNS xml;
                declare @PURCHASEAMOUNT money = 0;
                declare @AUCTIONITEMID uniqueidentifier = null;
                declare @TYPECODE tinyint = 0;
                declare @ITEMVALUE money = 0;
                declare @WRITTENOFF bit = 0;

                select 
                    @DESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
                    @PURCHASEAMOUNT = AUCTIONITEMRESERVATION.PURCHASEAMOUNT,
                    @AUCTIONITEMID = AUCTIONITEM.ID,
                    @TYPECODE = AUCTIONITEM.TYPECODE,
                    @ITEMVALUE = case
                                    when AUCTIONITEM.TYPECODE = 0 then
                                        AUCTIONITEM.VALUE
                                    else
                                        (select sum([ITEM].VALUE) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID)
                                 end,
                    @AUCTIONITEMRESERVATIONID = AUCTIONITEMRESERVATION.ID,
                    @ITEMTRANSACTIONCURRENCYID = AUCTIONITEM.TRANSACTIONCURRENCYID,
                    @ITEMBASECURRENCYID = AUCTIONITEM.BASECURRENCYID,
                    @WRITTENOFF = case
                                    when WRITEOFF.ID is not null then 1
                                    else 0
                                  end
                from
                    dbo.AUCTIONITEMRESERVATION
                    inner join dbo.AUCTIONITEM on AUCTIONITEMRESERVATION.AUCTIONITEMID = AUCTIONITEM.ID
                    left join dbo.REVENUESPLIT on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLIT.REVENUEID
                    left join dbo.WRITEOFF on AUCTIONITEM.REVENUEAUCTIONDONATIONID = WRITEOFF.REVENUEID
                where AUCTIONITEM.ID = @APPLICATIONID

                select
                    @CAMPAIGNS = (select -- campaigns for designation

                                    CAMPAIGNID,
                                    CAMPAIGNSUBPRIORITYID
                                    from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@DESIGNATIONID, @DATE)
                                    for xml raw('ITEM'), type, elements, root('CAMPAIGNS'), BINARY BASE64);

                if @PURCHASEAMOUNT <> @APPLIEDAMOUNT
                    raiserror('BBERR_AUCTIONPURCHASE_INVALIDAMOUNT', 13, 1);

                if @ITEMTRANSACTIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
                    raiserror('BBERR_AUCTIONPURCHASE_INVALIDTRANSACTIONCURRENCYID', 13, 1);

                if dbo.UFN_AUCTIONITEMPURCHASE_VALIDATEPPURCHASEEXCHANGERATE(@APPLICATIONID) <> 1
                    raiserror('BBERR_AUCTIONPURCHASE_EXCHANGERATE', 13, 1);

                if @WRITTENOFF = 1
                    raiserror('BBERR_AUCTIONPURCHASE_AUCTIONITEMWRITTENOFF', 13, 1);

                if @TYPECODE = 0 --auction item

                    begin

                        --Check to see if the PDACCOUNTSYSTEM for the payment is the same as the item

                        if exists(select 1
                                    from dbo.PDACCOUNTSYSTEMFORREVENUE
                                    inner join dbo.AUCTIONITEM on AUCTIONITEM.REVENUEAUCTIONDONATIONID = PDACCOUNTSYSTEMFORREVENUE.ID
                                    where 
                                        AUCTIONITEM.ID = @APPLICATIONID 
                                        and 
                                        PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID <> @PDACCOUNTSYSTEMID
                                  )
                        begin
                            raiserror('BBERR_AUCTIONITEM_PAYMENT_ACCOUNTSYSTEMINVALID',13,1);
                        end

                        -- 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_GETCURRENCYVALUESBYPROPORTION
                                @AMOUNT = @APPLIEDAMOUNT,
                                @TOTALAMOUNT = @TOTALREVENUETRANSACTIONAMOUNT,
                                @BASECURRENCYID = @PAYMENTBASECURRENCYID,
                                @TOTALBASEAMOUNT = @TOTALREVENUEBASEAMOUNT,
                                @BASECURRENCYDECIMALDIGITS = @PAYMENTBASECURRENCYDECIMALDIGITS,
                                @TRANSACTIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID,
                     @BASEAMOUNT = @BASEAPPLIEDAMOUNT output,
                                @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
                                @TOTALORGANIZATIONAMOUNT = @TOTALREVENUEORGANIZATIONAMOUNT,
                                @ORGANIZATIONCURRENCYDECIMALDIGITS = @ORGANIZATIONCURRENCYDECIMALDIGITS,
                                @ORGANIZATIONAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT output

                                                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, @DATE, @DATE)

                                                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 = @DATE
                                                when not matched then
                                                    insert 
                                                    (
                                                        ID
                                                        ,DESIGNATIONID
                                                        ,TYPECODE
                                                        ,APPLICATIONCODE
                                                        ,OVERRIDEBUSINESSUNITS
                                                        ,REVENUESPLITBUSINESSUNITOVERRIDECODEID
                                                        -- boilerplate

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

                                                        ,@CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);

                        insert into dbo.AUCTIONITEMPURCHASE
                        (
                            ID,
                            AUCTIONITEMID,
                            PURCHASEID,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values
                        (
                            newid(),
                            @AUCTIONITEMID,
                            @REVENUESPLITID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @DATE,
                            @DATE
                        )

                        -- create recognitions

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

                if @TYPECODE = 1  --auction package

                begin    
                    --Check to see if the PDACCOUNTSYSTEM for the payment is the same as the item

                    if exists(select 1
                                from dbo.PDACCOUNTSYSTEMFORREVENUE
                                inner join dbo.AUCTIONITEM on AUCTIONITEM.REVENUEAUCTIONDONATIONID = PDACCOUNTSYSTEMFORREVENUE.ID
                                where 
                                    AUCTIONITEM.PACKAGEID = @APPLICATIONID 
                                    and 
                                    PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID <> @PDACCOUNTSYSTEMID
                              )
                    begin
                        raiserror('BBERR_AUCTIONITEM_PAYMENT_ACCOUNTSYSTEMINVALID_PACKAGE',13,1);
                    end

                    exec dbo.USP_AUCTIONPURCHASE_ADDPAYMENTTOPACKAGE 
                        @REVENUEID = @REVENUEID,
                        @AMOUNT = @APPLIEDAMOUNT,
                        @PACKAGEID = @AUCTIONITEMID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CHANGEDATE = @DATE;
                end
                insert into dbo.AUCTIONITEMREVENUEPURCHASE
                (
                    ID,
                    AUCTIONITEMID,
                    REVENUEPURCHASEID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values
                (
                    newid(),
                    @AUCTIONITEMID,
                    @REVENUEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @DATE,
                    @DATE
                )

                if @DESIGNATIONID is not null and @CAMPAIGNS is not null
                    exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_ADDFROMXML @REVENUESPLITID, @CAMPAIGNS, @CHANGEAGENTID;

                -- delete the auction item reservation if it existed

                exec dbo.USP_AUCTIONITEMRESERVATION_DELETEBYID_WITHCHANGEAGENTID @AUCTIONITEMRESERVATIONID, @CHANGEAGENTID;

            end