USP_AUCTIONPURCHASE_ADDPAYMENTTOPACKAGE

            CREATE procedure dbo.USP_AUCTIONPURCHASE_ADDPAYMENTTOPACKAGE
            (
                @REVENUEID uniqueidentifier,
                @AMOUNT money,
                @PACKAGEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as 
            set nocount on;

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

            declare @PACKAGETRANSACTIONCURRENCYID uniqueidentifier;
            declare @PACKAGEBASECURRENCYID 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 @BASEAMOUNT money;
            declare @ORGANIZATIONAMOUNT money;

            select
                @REVENUEDATE = REVENUE.DATE,
                @PAYMENTBASECURRENCYID = REVENUE.BASECURRENCYID,
                @PAYMENTTRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                @PAYMENTBASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                @PAYMENTORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID,
                @PAYMENTBASECURRENCYDECIMALDIGITS = BASE_CURRENCY.DECIMALDIGITS,
                @PAYMENTTRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTION_CURRENCY.DECIMALDIGITS,
                @TOTALREVENUETRANSACTIONAMOUNT = REVENUE.TRANSACTIONAMOUNT,
                @TOTALREVENUEBASEAMOUNT = REVENUE.AMOUNT,
                @TOTALREVENUEORGANIZATIONAMOUNT = REVENUE.ORGANIZATIONAMOUNT
            from
                dbo.REVENUE
                left join dbo.CURRENCY BASE_CURRENCY on REVENUE.BASECURRENCYID = BASE_CURRENCY.ID
                left join dbo.CURRENCY TRANSACTION_CURRENCY on REVENUE.TRANSACTIONCURRENCYID = TRANSACTION_CURRENCY.ID
            where
                REVENUE.ID = @REVENUEID;

            exec dbo.USP_CURRENCY_GETCURRENCYVALUESBYPROPORTION
                @AMOUNT = @AMOUNT,
                @TOTALAMOUNT = @TOTALREVENUETRANSACTIONAMOUNT,
                @BASECURRENCYID = @PAYMENTBASECURRENCYID,
                @TOTALBASEAMOUNT = @TOTALREVENUEBASEAMOUNT,
                @BASECURRENCYDECIMALDIGITS = @PAYMENTBASECURRENCYDECIMALDIGITS,
                @TRANSACTIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID,
                @BASEAMOUNT = @BASEAMOUNT output,
                @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
                @TOTALORGANIZATIONAMOUNT = @TOTALREVENUEORGANIZATIONAMOUNT,
                @ORGANIZATIONCURRENCYDECIMALDIGITS = @ORGANIZATIONCURRENCYDECIMALDIGITS,
                @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT output


            declare @REVENUESPLITID uniqueidentifier;

            declare @TRANSACTIONAMOUNT_TO_DISTRIBUTE money = 0;
            declare @AMOUNT_TO_DISTRIBUTE money = 0;
            declare @ORGANIZATIONAMOUNT_TO_DISTRIBUTE money = 0;
            declare @DISTRIBUTED_TRANSACTIONAMOUNT money = 0;
            declare @DISTRIBUTED_AMOUNT money = 0;
            declare @DISTRIBUTED_ORGANIZATIONAMOUNT money = 0;
            declare @TOTALDISTRIBUTED_TRANSACTIONAMOUNT money = 0;
            declare @TOTALDISTRIBUTED_AMOUNT money = 0;
            declare @TOTALDISTRIBUTED_ORGANIZATIONAMOUNT money = 0;

            declare @TOTALPACKAGETRANSACTIONVALUE money = 0;
 declare @TOTALPACKAGEBASEVALUE money = 0;
            declare @TOTALPACKAGEORGANIZATIONVALUE money = 0;

            select 
                @TOTALPACKAGETRANSACTIONVALUE = sum(AUCTIONITEM.TRANSACTIONVALUE),
                @TOTALPACKAGEBASEVALUE = sum(AUCTIONITEM.VALUE),
                @TOTALPACKAGEORGANIZATIONVALUE = sum(AUCTIONITEM.ORGANIZATIONVALUE)
            from dbo.AUCTIONITEM where PACKAGEID = @PACKAGEID;

            declare @ITEMCOUNT int = 0;
            declare @PACKAGEITEMS table
            (
                ID uniqueidentifier,
                TRANSACTIONVALUE money,
                BASEVALUE money,
                ORGANIZATIONVALUE money,
                DESIGNATIONID uniqueidentifier
            )

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

            insert into @PACKAGEITEMS
            (
                ID, 
                TRANSACTIONVALUE, 
                BASEVALUE, 
                ORGANIZATIONVALUE, 
                DESIGNATIONID
            )
            (
                select 
                    AUCTIONITEM.ID, 
                    AUCTIONITEM.TRANSACTIONVALUE, 
                    AUCTIONITEM.VALUE, 
                    AUCTIONITEM.ORGANIZATIONVALUE, 
                    DESIGNATIONID 
                from 
                    dbo.AUCTIONITEM 
                    left join dbo.REVENUESPLIT on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLIT.REVENUEID 
                where 
                    PACKAGEID = @PACKAGEID
            )

            select @ITEMCOUNT = count(ID)
            from @PACKAGEITEMS

            declare @CURRENTITEMNUMBER integer = 0;

            declare @PACKAGECURSOR_AUCTIONITEMID uniqueidentifier;
            declare @PACKAGECURSOR_TRANSACTIONVALUE money;
            declare @PACKAGECURSOR_BASEVALUE money;
            declare @PACKAGECURSOR_ORGANIZATIONVALUE money;
            declare @PACKAGECURSOR_DESIGNATIONID uniqueidentifier;

            declare @BASEPURCHASEAMOUNT money;
            declare @ORGANIZATIONPURCHASEAMOUNT money;

            declare PACKAGECURSOR cursor local fast_forward
            for 
                select 
                    ID, 
                    TRANSACTIONVALUE, 
                    BASEVALUE, 
                    ORGANIZATIONVALUE, 
                    DESIGNATIONID
                from 
                    @PACKAGEITEMS
                order by ID

            open PACKAGECURSOR
            fetch next from PACKAGECURSOR into 
                @PACKAGECURSOR_AUCTIONITEMID, 
                @PACKAGECURSOR_TRANSACTIONVALUE,
                @PACKAGECURSOR_BASEVALUE,
                @PACKAGECURSOR_ORGANIZATIONVALUE,
                @PACKAGECURSOR_DESIGNATIONID

            while @@FETCH_STATUS = 0
            begin

                set @REVENUESPLITID = newid()
                set @CURRENTITEMNUMBER = @CURRENTITEMNUMBER + 1;

                if @CURRENTITEMNUMBER <> @ITEMCOUNT
                begin
                    select
                        @DISTRIBUTED_TRANSACTIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(@PACKAGECURSOR_TRANSACTIONVALUE, @TOTALPACKAGETRANSACTIONVALUE, @AMOUNT, @PAYMENTTRANSACTIONCURRENCYDECIMALDIGITS),
                        @DISTRIBUTED_AMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(@PACKAGECURSOR_TRANSACTIONVALUE, @TOTALPACKAGETRANSACTIONVALUE, @BASEAMOUNT, @PAYMENTBASECURRENCYDECIMALDIGITS),
                        @DISTRIBUTED_ORGANIZATIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(@PACKAGECURSOR_TRANSACTIONVALUE, @TOTALPACKAGETRANSACTIONVALUE, @ORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS)

                    -- Keep track of how much we have already distributed

                    set @TOTALDISTRIBUTED_TRANSACTIONAMOUNT = @TOTALDISTRIBUTED_TRANSACTIONAMOUNT + @DISTRIBUTED_TRANSACTIONAMOUNT;
                    set @TOTALDISTRIBUTED_AMOUNT = @TOTALDISTRIBUTED_AMOUNT + @DISTRIBUTED_AMOUNT;
                    set @TOTALDISTRIBUTED_ORGANIZATIONAMOUNT = @TOTALDISTRIBUTED_ORGANIZATIONAMOUNT + @DISTRIBUTED_ORGANIZATIONAMOUNT;
                end
                else
                begin
                    set @DISTRIBUTED_TRANSACTIONAMOUNT = @AMOUNT - @TOTALDISTRIBUTED_TRANSACTIONAMOUNT;
                    set @DISTRIBUTED_AMOUNT = @BASEAMOUNT - @TOTALDISTRIBUTED_AMOUNT;
                    set @DISTRIBUTED_ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT - @TOTALDISTRIBUTED_ORGANIZATIONAMOUNT
                end

                insert into dbo.REVENUESPLIT
                (
                    ID,
                    REVENUEID,
                    AMOUNT,
                    TYPECODE,
                    APPLICATIONCODE,
                    DESIGNATIONID, 
                    BASECURRENCYID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    DATEADDED, 
                    DATECHANGED, 
                    ADDEDBYID, 
                    CHANGEDBYID
                )
                values
                (
                    @REVENUESPLITID,
                    @REVENUEID,
                    @DISTRIBUTED_AMOUNT,
                    12,
                    12,
                    @PACKAGECURSOR_DESIGNATIONID, 
                    @PAYMENTBASECURRENCYID, 
                    @DISTRIBUTED_ORGANIZATIONAMOUNT, 
                    @PAYMENTORGANIZATIONEXCHANGERATEID, 
                    @DISTRIBUTED_TRANSACTIONAMOUNT, 
                    @PAYMENTTRANSACTIONCURRENCYID, 
                    @PAYMENTBASEEXCHANGERATEID, 
                    @CHANGEDATE,
                    @CHANGEDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                );

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

                insert into dbo.AUCTIONITEMREVENUEPURCHASE(ID,AUCTIONITEMID,REVENUEPURCHASEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                values(newid(),@PACKAGECURSOR_AUCTIONITEMID,@REVENUEID,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE)

                -- create recognitions

                exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @REVENUESPLITID, @CHANGEAGENTID, @CHANGEDATE;

                fetch next from PACKAGECURSOR into 
                    @PACKAGECURSOR_AUCTIONITEMID, 
                    @PACKAGECURSOR_TRANSACTIONVALUE,
                    @PACKAGECURSOR_BASEVALUE,
                    @PACKAGECURSOR_ORGANIZATIONVALUE,
                    @PACKAGECURSOR_DESIGNATIONID
            end
            close PACKAGECURSOR
            deallocate PACKAGECURSOR