USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT

            CREATE procedure dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT
                (
                    @SOURCELINEITEMID uniqueidentifier = null,
                    @TARGETLINEITEMID uniqueidentifier = null,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @APPLICATIONS xml
                )
            as
            begin
                begin try

                    -- Check the basic rule that only one of the two IDs can be passed in

                    if ((not @SOURCELINEITEMID is null) and (not @TARGETLINEITEMID is null))
                        raiserror('USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT cannot be called with both source and target ID set.' , 16, 1);

                    -- Check the basic rule that one of the two IDs must be passed in

                    if ((@SOURCELINEITEMID is null) and (@TARGETLINEITEMID is null))
                        raiserror('USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT must be called with either source or target ID set.' , 16, 1);

                    -- Get the change agent and date information

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

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

                    -- 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(APPLICATIONS.ID, newid()),
                            APPLICATIONS.SOURCELINEITEMID,
                            APPLICATIONS.TARGETLINEITEMID,
                            APPLICATIONS.AMOUNT,
                            APPLICATIONS.POSTDATE,
                            APPLICATIONS.POSTSTATUSCODE
                    from dbo.UFN_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_FROMITEMLISTXML(@APPLICATIONS) as APPLICATIONS;

                    -- Make sure that the user is only editing applications for a single source or applied to line item

                    if (not @SOURCELINEITEMID is null)
                    begin
                        if (exists
                                (select APPLICATIONSTABLE.ID
                                from @APPLICATIONSTABLE as APPLICATIONSTABLE
                                where APPLICATIONSTABLE.SOURCELINEITEMID != @SOURCELINEITEMID))
                            raiserror('USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT cannot edit more than one set of source items.' , 16, 1); 
                    end
                    else if (not @TARGETLINEITEMID is null)
                    begin
                        if (exists
                                (select APPLICATIONSTABLE.ID
                                from @APPLICATIONSTABLE as APPLICATIONSTABLE
                                where APPLICATIONSTABLE.TARGETLINEITEMID != @TARGETLINEITEMID))
                            raiserror('USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT cannot edit more than one set of applied to items.' , 16, 1); 
                    end

                    -- Now unapply any existing applications that are NOT included as part of this edit or are edited!

                    --  For applications an edit is a reversal and a reapply.


                    -- First create a table of items to reverse

                    declare @APPLICATIONSXML xml;

                    -- Get the items to reverse

                    set @APPLICATIONSXML = 
                        (select FINANCIALTRANSACTIONLINEITEM.ID as ID,
                            FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID,
                            FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID,
                            FINANCIALTRANSACTIONLINEITEM.POSTDATE as POSTDATE,  -- Post date and status may be different in the XML than what is in the DB

                            FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE as POSTSTATUSCODE
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                            left outer join @APPLICATIONSTABLE as APPLICATIONSTABLE
                                on FINANCIALTRANSACTIONLINEITEM.ID = APPLICATIONSTABLE.ID
                        where ((FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = @SOURCELINEITEMID) or (FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID = @TARGETLINEITEMID)) and -- Make sure we have one of the items we should be working with

                              (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                              (((not @SOURCELINEITEMID is null) and
                                 (FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID != APPLICATIONSTABLE.TARGETLINEITEMID)) or -- Edited application applied to different line item                        

                               ((not @TARGETLINEITEMID is null) and
                                (FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID != APPLICATIONSTABLE.SOURCELINEITEMID)) or -- Edited application from different source

                               (APPLICATIONSTABLE.ID is null) or -- Application is not in edited list

                               ((not APPLICATIONSTABLE.AMOUNT is null) and
                                (FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT != APPLICATIONSTABLE.AMOUNT) and 
                                (FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2)) or -- Amount of posted application changed

                               (APPLICATIONSTABLE.AMOUNT = 0)) -- Application totally unapplied

                        for xml raw('ITEM'),type,elements,root('APPLICATIONS'),BINARY BASE64);

                    -- Update the IDs of the unapplied items, we need to create new items not edit the old items

                    update @APPLICATIONSTABLE
                        set ID = newid()
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                        left outer join @APPLICATIONSTABLE as APPLICATIONSTABLE
                            on FINANCIALTRANSACTIONLINEITEM.ID = APPLICATIONSTABLE.ID
                    where ((FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = @SOURCELINEITEMID) or (FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID = @TARGETLINEITEMID)) and -- Make sure we have one of the items we should be working with

                          (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                          (((not @SOURCELINEITEMID is null) and
                             (FINANCIALTRANSACTIONLINEITEM.TARGETLINEITEMID != APPLICATIONSTABLE.TARGETLINEITEMID)) or -- Edited application applied to different line item                        

                           ((not @TARGETLINEITEMID is null) and
                            (FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID != APPLICATIONSTABLE.SOURCELINEITEMID)) or -- Edited application from different source

                           (APPLICATIONSTABLE.ID is null) or -- Application is not in edited list

                           ((not APPLICATIONSTABLE.AMOUNT is null) and
                            (FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT != APPLICATIONSTABLE.AMOUNT) and 
                            (FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2))) -- Amount of posted application changed

                            -- Zeroed out apps are not included as they will not be created or edited within the apply code


                    -- Unapply the items

                    exec dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_UNAPPLYMULTIPLE @APPLICATIONSXML, @CHANGEAGENTID;

                    -- Now create new applications based on the information passed in

                    set @APPLICATIONSXML = 
                        (select APPLICATIONSTABLE.ID,
                                APPLICATIONSTABLE.SOURCELINEITEMID,
                                APPLICATIONSTABLE.TARGETLINEITEMID,
                                APPLICATIONSTABLE.AMOUNT,
                                APPLICATIONSTABLE.POSTDATE, 
                                APPLICATIONSTABLE.POSTSTATUSCODE 
                        from @APPLICATIONSTABLE as APPLICATIONSTABLE 
                            left outer join FINANCIALTRANSACTIONLINEITEM
                                on FINANCIALTRANSACTIONLINEITEM.ID = APPLICATIONSTABLE.ID
                        where (APPLICATIONSTABLE.AMOUNT > 0) and -- New amount is greater than zero

                                ((FINANCIALTRANSACTIONLINEITEM.ID is null) or -- Application does not already exist

                                    (not FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) or -- Existing application is marked as deleted

                                    (APPLICATIONSTABLE.AMOUNT != FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) or -- Application amount has changed

                                    (APPLICATIONSTABLE.POSTSTATUSCODE != FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE) or  -- Application post status changed

                                    (APPLICATIONSTABLE.POSTDATE != FINANCIALTRANSACTIONLINEITEM.POSTDATE))  -- Application post date changed.

                        for xml raw('ITEM'),type,elements,root('APPLICATIONS'),BINARY BASE64);

                    -- Apply the items

                    exec dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_APPLYMULTIPLE @APPLICATIONSXML, @CHANGEAGENTID;


                end try

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

                return 0;
            end