USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_APPLYMULTIPLE

            CREATE procedure dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_APPLYMULTIPLE
                (
                    @APPLICATIONS xml,
                    @CHANGEAGENTID uniqueidentifier = null
                )
            as
            begin
                set nocount on;

                /*
                 *
                 * NOTE: Rules about transaction types and subledger specific details should be handled in a subledger
                 *          application sproc that calls this sproc.
                 *
                 */

                begin try
                    -- Get a table with the new applications in it.

                    declare @APPLICATIONSTABLE table
                    (
                        ID uniqueidentifier NOT NULL,
                        SOURCELINEITEMID uniqueidentifier NOT NULL,
                        TARGETLINEITEMID uniqueidentifier NOT NULL,
                        AMOUNT money NOT NULL,
                        POSTDATE datetime NULL,
                        POSTSTATUSCODE tinyint NOT NULL
                    )

                    insert into @APPLICATIONSTABLE
                        (ID,
                        SOURCELINEITEMID,
                        TARGETLINEITEMID,
                        AMOUNT,
                        POSTDATE,
                        POSTSTATUSCODE)
                    select isnull(ID, newid()),
                            SOURCELINEITEMID,
                            TARGETLINEITEMID,
                            AMOUNT,
                            POSTDATE,
                            POSTSTATUSCODE
                    from dbo.UFN_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_FROMITEMLISTXML(@APPLICATIONS);


                    -- Check basic application rules


                    -- Do not allow zero dollar or negative applications

                    if (exists (select APPLICATIONSTABLE.ID 
                                from @APPLICATIONSTABLE as APPLICATIONSTABLE
                                where APPLICATIONSTABLE.AMOUNT <= 0))
                        raiserror('ERR_LINEITEMAPPLICATION_AMOUNTMUSTBEGREATERTHANZERO', 13,1);

                    -- Cannot apply from a deleted source line item or a source line item that is not a standard line item

                    if (exists (select FINANCIALTRANSACTIONLINEITEM.ID
                                from dbo.FINANCIALTRANSACTIONLINEITEM
                                    inner join @APPLICATIONSTABLE as APPLICATIONSTABLE
                                        on APPLICATIONSTABLE.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                                where (not FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) or
                                         (FINANCIALTRANSACTIONLINEITEM.TYPECODE != 0)))
                        raiserror('ERR_LINEITEMAPPLICATION_SOURCELINEITEMDELETEDORNOTSTANDARD', 13,1);

                    -- Cannot apply to a deleted line item or a line item that is not a standard line item

                    if (exists (select FINANCIALTRANSACTIONLINEITEM.ID
                                from dbo.FINANCIALTRANSACTIONLINEITEM
                                    inner join @APPLICATIONSTABLE as APPLICATIONSTABLE
                                        on APPLICATIONSTABLE.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                                where (not FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) or
                                         (FINANCIALTRANSACTIONLINEITEM.TYPECODE != 0)))
                         raiserror('ERR_LINEITEMAPPLICATION_TARGETLINEITEMDELETEDORNOTSTANDARD', 13,1);

                    -- Amount applied cannot exceed amount left to apply for source line item.

                    if (exists
                        (select FINANCIALTRANSACTIONLINEITEM.ID        
                         from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join (select APPLICATIONSTABLE_INNER1.SOURCELINEITEMID,
                                               APPLICATIONSTABLE_INNER1.AMOUNT
                                         from @APPLICATIONSTABLE as APPLICATIONSTABLE_INNER1

                                         union

                                         select EXISTINGAPPLICATIONLINEITEM.SOURCELINEITEMID,
                                                EXISTINGAPPLICATIONLINEITEM.TRANSACTIONAMOUNT
                                         from dbo.FINANCIALTRANSACTIONLINEITEM as EXISTINGAPPLICATIONLINEITEM
                                            left outer join @APPLICATIONSTABLE as APPLICATIONSTABLE_INNER2
                                                on APPLICATIONSTABLE_INNER2.ID = EXISTINGAPPLICATIONLINEITEM.ID
                                         where (APPLICATIONSTABLE_INNER2.ID is null) and 
                                                (EXISTINGAPPLICATIONLINEITEM.TYPECODE = 2) and
                                                (EXISTINGAPPLICATIONLINEITEM.DELETEDON is null) and
                                                (EXISTINGAPPLICATIONLINEITEM.SOURCELINEITEMID in (select SOURCELINEITEMID from @APPLICATIONSTABLE))) as FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS
                                on FINANCIALTRANSACTIONLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.SOURCELINEITEMID
                         group by FINANCIALTRANSACTIONLINEITEM.ID, 
                                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                         having  FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT < sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.AMOUNT, 0))))
                         raiserror('ERR_LINEITEMAPPLICATION_AMOUNTEXCEEDSAMOUNTLEFTTOAPPLY', 13,1);

                    -- Amount applied cannot exceed amount left to be applied to for target line item.

                    if (exists
                        (select FINANCIALTRANSACTIONLINEITEM.ID        
                          from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join (select APPLICATIONSTABLE_INNER1.TARGETLINEITEMID,
                                                APPLICATIONSTABLE_INNER1.AMOUNT
                                         from @APPLICATIONSTABLE as APPLICATIONSTABLE_INNER1

                                         union

                                         select EXISTINGAPPLICATIONLINEITEM.TARGETLINEITEMID,
                                                EXISTINGAPPLICATIONLINEITEM.TRANSACTIONAMOUNT
                                         from dbo.FINANCIALTRANSACTIONLINEITEM as EXISTINGAPPLICATIONLINEITEM
                                            left outer join @APPLICATIONSTABLE as APPLICATIONSTABLE_INNER2
                                                on APPLICATIONSTABLE_INNER2.ID = EXISTINGAPPLICATIONLINEITEM.ID
                                         where (APPLICATIONSTABLE_INNER2.ID is null) and
                                                (EXISTINGAPPLICATIONLINEITEM.TYPECODE = 2) and
                                                (EXISTINGAPPLICATIONLINEITEM.DELETEDON is null) and
                                                (EXISTINGAPPLICATIONLINEITEM.TARGETLINEITEMID in (select TARGETLINEITEMID from @APPLICATIONSTABLE))) as FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS
                                on FINANCIALTRANSACTIONLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.TARGETLINEITEMID
                         group by FINANCIALTRANSACTIONLINEITEM.ID, 
                                     FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                         having  FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT < sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATIONAMOUNTS.AMOUNT, 0))))
                         raiserror('ERR_LINEITEMAPPLICATION_AMOUNTEXCEEDSAMOUNTLEFTTOAPPLYTO', 13,1);

                    -- Get the change agent

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

                    -- Get the current date

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

                    -- Actually add the application


                    -- Update the existing line item applications

                    update dbo.FINANCIALTRANSACTIONLINEITEM
                    set 
                        SOURCELINEITEMID = APPLICATIONSTABLE.SOURCELINEITEMID,
                        TARGETLINEITEMID = APPLICATIONSTABLE.TARGETLINEITEMID,
                        TRANSACTIONAMOUNT = APPLICATIONSTABLE.AMOUNT,
                        BASEAMOUNT = APPLICATIONSTABLE.AMOUNT,
                        ORGAMOUNT = APPLICATIONSTABLE.AMOUNT,
                        POSTDATE = APPLICATIONSTABLE.POSTDATE,
                        POSTSTATUSCODE = APPLICATIONSTABLE.POSTSTATUSCODE,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    from @APPLICATIONSTABLE as APPLICATIONSTABLE
                    where (FINANCIALTRANSACTIONLINEITEM.ID = APPLICATIONSTABLE.ID) and 
                            ((APPLICATIONSTABLE.SOURCELINEITEMID != FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID) or
                             (APPLICATIONSTABLE.TARGETLINEITEMID != FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID) or
                             (APPLICATIONSTABLE.AMOUNT != FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) or
                             (APPLICATIONSTABLE.POSTDATE != FINANCIALTRANSACTIONLINEITEM.POSTDATE) or 
                             (APPLICATIONSTABLE.POSTSTATUSCODE != FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE))

                    -- Remove anything that already exists as a line item

                    delete from @APPLICATIONSTABLE
                    from @APPLICATIONSTABLE as APPLICATIONSTABLE
                        left outer join FINANCIALTRANSACTIONLINEITEM 
                            on FINANCIALTRANSACTIONLINEITEM.ID = APPLICATIONSTABLE.ID
                    where (not FINANCIALTRANSACTIONLINEITEM.ID is null);

                    -- Next the line item that is the application

                    insert into dbo.FINANCIALTRANSACTIONLINEITEM
                        (ID, 
                        FINANCIALTRANSACTIONID,
                        TRANSACTIONAMOUNT,
                        BASEAMOUNT,
                        ORGAMOUNT,
                        VISIBLE, 
                        TYPECODE, 
                        DELETEDON, 
                        POSTDATE,
                        POSTSTATUSCODE,
                        SOURCELINEITEMID, 
                        TARGETLINEITEMID, 
                        DATEADDED, 
                        ADDEDBYID, 
                        DATECHANGED, 
                        CHANGEDBYID)
                    select APPLICATIONSTABLE.ID,
                            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                            APPLICATIONSTABLE.AMOUNT,
                            APPLICATIONSTABLE.AMOUNT,
                            APPLICATIONSTABLE.AMOUNT,
                            1, -- VISIBLE

                            2, -- TYPECODE: Application

                            NULL, -- DELETEDON,

                            APPLICATIONSTABLE.POSTDATE,
                            APPLICATIONSTABLE.POSTSTATUSCODE,
                            APPLICATIONSTABLE.SOURCELINEITEMID,
                         APPLICATIONSTABLE.TARGETLINEITEMID,
                            @CURRENTDATE, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CHANGEAGENTID
                    from @APPLICATIONSTABLE as APPLICATIONSTABLE
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM
                            on APPLICATIONSTABLE.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID

                    /*
                     *
                     * NOTE: Distributions should be handled here!
                     *
                     */

                end try

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

                return 0;
            end