USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFTUIPAYMENTFIELDS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFTUIPAYMENTFIELDS
(
    @ID nvarchar(73),
    @DATALOADED bit = 0 output,
    @ACTIONABOVEAMOUNT money = null output,
    @OVERPAYMENTCODE tinyint = null output,
    @ACTIONBELOWAMOUNTS xml = null output,
  @UNDERPAYMENTCODE tinyint = null output,
  @SPLITS xml = null output,
  @DESIGNATIONID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

  declare @RGID uniqueidentifier = cast(substring(@ID,1,36) as uniqueidentifier);

  declare @PAYMENTSPLITID uniqueidentifier;

  if len(@ID) > 36
   set @PAYMENTSPLITID = cast(substring(@ID,38,36) as uniqueidentifier);

  if @PAYMENTSPLITID is null
    select @DATALOADED = 1,
           @ACTIONABOVEAMOUNT = ACTIONABOVEAMOUNT,
           @OVERPAYMENTCODE = OVERPAYMENTCODE,
           @ACTIONBELOWAMOUNTS = ACTIONBELOWAMOUNTS,
           @UNDERPAYMENTCODE = INSTALLMENTUNDERPAYMENTCODE,
           @SPLITS = (select 
                        SPLITS.ID, 
                        SPLITS.TRANSACTIONAMOUNT AMOUNT, 
                        SPLITS.DESIGNATIONID, 
                        DESIGNATION.USERID DESIGNATIONDESCRIPTION,
                        SPLITS.TRANSACTIONCURRENCYID
                      from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
                      left join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
                      for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)
    from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(@RGID,null) i
    cross apply dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(@RGID,
                                                         i.BALANCE,
                                                         dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_2(@RGID, null, 1),
                                                         i.DATE,
                                                         getdate(),
                                                         null);
  else
    select @DATALOADED = 1,
           --REVENUESPLIT_EXT.DESIGNATIONID,
           @ACTIONABOVEAMOUNT = u.ACTIONABOVEAMOUNT,
           @OVERPAYMENTCODE = u.OVERPAYMENTCODE,
           @ACTIONBELOWAMOUNTS = u.ACTIONBELOWAMOUNTS,
           @UNDERPAYMENTCODE = u.INSTALLMENTUNDERPAYMENTCODE,
           @DESIGNATIONID = s.DESIGNATIONID
    from dbo.FINANCIALTRANSACTIONLINEITEM l
    inner join dbo.REVENUESPLIT_EXT s on s.ID = l.ID
    inner join dbo.RECURRINGGIFTINSTALLMENT i on i.ID = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTIGNOREPAYMENT_2(@RGID, l.FINANCIALTRANSACTIONID, null, l.DATEADDED)
    cross apply dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(@RGID,
                                                         dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(i.ID, l.FINANCIALTRANSACTIONID),
                                                         dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTIGNOREPAYMENT_2(@RGID, l.FINANCIALTRANSACTIONID, l.DATEADDED, 1),
                                                         i.DATE,
                                                         l.DATEADDED,
                                                         l.FINANCIALTRANSACTIONID) u
    where l.ID = @PAYMENTSPLITID;

    return 0;