USP_CHARGE_AUTOAPPLY

            CREATE procedure dbo.USP_CHARGE_AUTOAPPLY
                (
                    @CHARGEID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null
                )
            as
            begin
                set nocount on;


                begin try
                  -- Get the change agent

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

                  declare @CHARGELINEITEMID uniqueidentifier
                  declare @TOTALAMOUNT money
                  declare @TOTALAPPLIEDAMOUNT money 

                  select TOP 1 @TOTALAMOUNT = TRANSACTIONAMOUNT, @CHARGELINEITEMID = ID from dbo.FINANCIALTRANSACTIONLINEITEM 
                  where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=@CHARGEID 
                  and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                  and FINANCIALTRANSACTIONLINEITEM.TYPECODE=0

                  select @TOTALAMOUNT = TRANSACTIONAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.ID=@CHARGELINEITEMID
                  if @TOTALAMOUNT>0 
                    SET @TOTALAMOUNT = @TOTALAMOUNT - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
                                                                                from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
                                                                                where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null) and
                                                                                        (FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = @CHARGELINEITEMID)), 0)

                  if @TOTALAMOUNT>0
                  begin
                    declare @APPLICATIONSTOAPPLY table
                    (
                        ID uniqueidentifier NULL,
                        SOURCELINEITEMID uniqueidentifier NOT NULL,
                        TARGETLINEITEMID uniqueidentifier NOT NULL,
                        AMOUNT money NOT NULL,
                        TRANDATE date NOT NULL,
                        APPLIEDAMOUNT money NOT NULL                                            
                    )

                    insert into @APPLICATIONSTOAPPLY
                    (
                        ID,
                        SOURCELINEITEMID,
                        TARGETLINEITEMID,
                        AMOUNT,
                        TRANDATE,
                        APPLIEDAMOUNT                        
                    )                  
                      select  APP.ID,                            
                            APP.SOURCELINEITEMID,
                            @CHARGELINEITEMID,
                            APP.TOTALAMOUNTREMAINING,
                            APP.DATE,
                            0                              
                      from dbo.UFN_CHARGEAPPLICATIONS(@CHARGELINEITEMID, null, 1) AS APP
                    where (APP.TOTALAMOUNTREMAINING>0 and not APP.TOTALAMOUNTREMAINING is null);

                    update APP set ID=newid() from @APPLICATIONSTOAPPLY APP where ID is null

                    set @TOTALAPPLIEDAMOUNT=0

                    declare @ROWID uniqueidentifier;
                    declare @ROWAMOUNT money

                    --Apply oldest one first

                    declare APPLY cursor forward_only read_only for select ID, AMOUNT
                    from @APPLICATIONSTOAPPLY      
                    order by TRANDATE asc;

                    open APPLY;
                    fetch next from APPLY into @ROWID, @ROWAMOUNT;

                    while (@@fetch_status = 0 and @TOTALAPPLIEDAMOUNT<@TOTALAMOUNT)
                    begin

                          if @TOTALAPPLIEDAMOUNT + @ROWAMOUNT < @TOTALAMOUNT                          
                          begin
                            update @APPLICATIONSTOAPPLY 
                            set APPLIEDAMOUNT = @ROWAMOUNT 
                            where ID = @ROWID;

                            set @TOTALAPPLIEDAMOUNT =@TOTALAPPLIEDAMOUNT  + @ROWAMOUNT
                          end
                          else
                          begin
                            update @APPLICATIONSTOAPPLY 
                            set APPLIEDAMOUNT = @TOTALAMOUNT-@TOTALAPPLIEDAMOUNT 
                            where ID = @ROWID;

                            set @TOTALAPPLIEDAMOUNT = @TOTALAMOUNT
                          end                            
                          fetch next from APPLY into @RowID, @ROWAMOUNT;
                    end;
                    close APPLY;
                    deallocate APPLY;


                    declare @APPLICATIONSXML xml;

                    --TODO: Postdate and poststatus will need to be changed to the lineitems poststatus/postdate

                    set @APPLICATIONSXML = 
                        (select APPLICATIONSTOAPPLY.ID,
                                APPLICATIONSTOAPPLY.SOURCELINEITEMID,
                                APPLICATIONSTOAPPLY.TARGETLINEITEMID,
                                APPLICATIONSTOAPPLY.APPLIEDAMOUNT AS AMOUNT,
                                GETDATE() AS POSTDATE,
                                1 AS POSTSTATUSCODE
                        from @APPLICATIONSTOAPPLY as APPLICATIONSTOAPPLY 
                        where APPLICATIONSTOAPPLY.APPLIEDAMOUNT > 0
                        for xml raw('ITEM'),type,elements,root('APPLICATIONS'),BINARY BASE64);

                -- Apply the items

                exec dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_APPLYMULTIPLE @APPLICATIONSXML, @CHANGEAGENTID;

              end
              end try

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

                return 0;

            end