UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS(
  @REVENUEID uniqueidentifier
)
    returns table            
            as            
            return (
                select 
                    '00000000-0000-0000-0000-000000000000' as ID,
                    ID as INSTALLMENTID,
                    DATE,
                    TRANSACTIONAMOUNT as AMOUNT,
                    (TRANSACTIONAMOUNT - (AMOUNTPAID + AMOUNTWRITTENOFF)) as BALANCE,
                    (AMOUNTPAID + AMOUNTWRITTENOFF) as APPLIED,
                    SEQUENCE,
                    (
                        select 
                            '00000000-0000-0000-0000-000000000000' as ID,
                            SPLITINFO.ID as INSTALLMENTSPLITID, 
                            SPLITINFO.DESIGNATIONID, 
                            SPLITINFO.TRANSACTIONAMOUNT as AMOUNT,
                            (SPLITINFO.AMOUNTPAID + SPLITINFO.AMOUNTWRITTENOFF) APPLIED,
                            SPLITINFO.TRANSACTIONCURRENCYID,
                            SPLITINFO.REVENUESPLITID,
                            SPLITINFO.PAYMENTCOUNT,
                            SPLITINFO.WRITEOFFCOUNT,
                            (
                                select
                                    ISP.ID,
                                    ISP.AMOUNT,
                                    ISP.PAYMENTID LINEITEMID,
                                    LI.FINANCIALTRANSACTIONID TRANSACTIONID
                                from
                                    dbo.INSTALLMENTSPLITPAYMENT ISP
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = ISP.PAYMENTID
                                where
                                    ISP.INSTALLMENTSPLITID = SPLITINFO.ID
                                for xml raw('ITEM'),type,elements,BINARY BASE64
                            ) INSTALLMENTSPLITPAYMENTS,
                            (
                                select
                                    ISW.ID,
                                    ISW.AMOUNT,
                                    ISW.WRITEOFFID TRANSACTIONID
                                from
                                    dbo.INSTALLMENTSPLITWRITEOFF ISW
                                where
                                    ISW.INSTALLMENTSPLITID = SPLITINFO.ID
                                for xml raw('ITEM'),type,elements,BINARY BASE64
                            ) INSTALLMENTSPLITWRITEOFFS
                        from dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITINFO_BULK() SPLITINFO
                        left join dbo.DESIGNATION on DESIGNATION.ID = SPLITINFO.DESIGNATIONID
                        where SPLITINFO.INSTALLMENTID = INSTALLMENT.ID
                        order by DESIGNATION.VANITYNAME
                        for xml raw('ITEM'),type,elements,BINARY BASE64
                    ) INSTALLMENTSPLITS,
                    case
                        when PAYMENTCOUNT > 0 then cast(1 as bit)
                        else cast(0 as bit)
                    end as PAYMENTAPPLIED,
                    PAYMENTCOUNT,
                    WRITEOFFCOUNT,
                    TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT
                from dbo.UFN_INSTALLMENT_GETINFO_BULK() as INSTALLMENT
                where 
                    REVENUEID = @REVENUEID);