USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEADJUST_4

The load procedure used by the edit dataform template "Posted Pledge Edit Form 4"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(255)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@SENDPLEDGEREMINDER	bit	INOUT	Send pledge reminders
@SPLITS	xml	INOUT	Designations
@FREQUENCYCODE	tinyint	INOUT	Frequency
@NUMBEROFINSTALLMENTS	int	INOUT	No. installments remaining
@NEXTTRANSACTIONDATE	datetime	INOUT	Next installment date
@AMOUNTPAID	money	INOUT	Amount paid
@INSTALLMENTS	xml	INOUT
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Pledge is anonymous
@MAILINGID	uniqueidentifier	INOUT	Mailing
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@PLEDGESUBTYPEID	uniqueidentifier	INOUT	Subtype
@MAXSOFTCREDITAMOUNT	money	INOUT	Max soft credit amount
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@MAINSINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@FINDERNUMBERVALID	bit	INOUT	FINDERNUMBERVALID
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@OPPORTUNITYCONSTITUENTNAME	nvarchar(154)	INOUT
@OPPORTUNITYASKDATE	datetime	INOUT
@OPPORTUNITYASKAMOUNT	money	INOUT
@LASTPAYMENTSEQUENCE	int	INOUT	Installment sequence of last payment
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@ADJUSTMENTDATE	datetime	INOUT	Adjusted date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjusted post date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post status code
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment description
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@HASPOSTEDPAYMENTS	bit	INOUT	Pledge has posted payments
@ADJPAYMENT_DATE	datetime	INOUT	Adjustment date
@ADJPAYMENT_POSTDATE	datetime	INOUT	Adjustment post date
@ADJPAYMENT_REASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@ADJPAYMENT_DETAILS	nvarchar(255)	INOUT	Adjustment details
@PERCENTAGEBENEFITS	xml	INOUT	Benefits
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@BASEDECIMALDIGITS	tinyint	INOUT	Decimal digits
@BASEROUNDINGTYPECODE	tinyint	INOUT	Rounding type
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@HASRECOGNITIONCREDIT	bit	INOUT	Has recognition credits
@UPDATERECOGNITIONOPTION	tinyint	INOUT	Update recognition option
@UPDATETRIBUTEOPTION	tinyint	INOUT	Tribute update option
@HASTRIBUTE	bit	INOUT	Has tribute
@VALIDATETRIBUTES	bit	INOUT	Validate tributes
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@ISMEMBERSHIPPLEDGE	bit	INOUT
@HASPAYMENTS	bit	INOUT
@PROMPTONADJUSTMENTDATECHANGE	bit	INOUT
@ADJPAYMENT_POSTDATEDEPOSIT	datetime	INOUT
@INSTALLMENTAMOUNT	money	INOUT
@ORIGINALINSTALLMENTAMOUNT	money	INOUT
@TOTALAMOUNTWRITTENOFF	money	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEADJUST_4 (
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@CONSTITUENTID uniqueidentifier = null output
  ,@CONSTITUENTNAME nvarchar(255) = null output
  ,@DATE datetime = null output
  ,@AMOUNT money = null output
  ,@POSTSTATUSCODE tinyint = null output
  ,@POSTDATE datetime = null output
  ,@SENDPLEDGEREMINDER bit = null output
  ,@SPLITS xml = null output
  ,@FREQUENCYCODE tinyint = null output
  ,@NUMBEROFINSTALLMENTS int = null output
  ,@NEXTTRANSACTIONDATE datetime = null output
  ,@AMOUNTPAID money = null output
  ,@INSTALLMENTS xml = null output
  ,@FINDERNUMBER bigint = null output
  ,@SOURCECODE nvarchar(50) = null output
  ,@APPEALID uniqueidentifier = null output
  ,@BENEFITS xml = null output
  ,@BENEFITSWAIVED bit = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@MAILINGID uniqueidentifier = null output
  ,@CHANNELCODEID uniqueidentifier = null output
  ,@DONOTACKNOWLEDGE bit = null output
  ,@PLEDGESUBTYPEID uniqueidentifier = null output
  ,@MAXSOFTCREDITAMOUNT money = null output
  ,@MAXSOLICITORAMOUNT money = null output
  ,@TSLONG bigint = 0 output
  ,@MAINSINGLEDESIGNATIONID uniqueidentifier = null output
  ,@FINDERNUMBERVALID bit = null output
  ,@OPPORTUNITYID uniqueidentifier = null output
  ,@OPPORTUNITYCONSTITUENTNAME nvarchar(154) = null output
  ,@OPPORTUNITYASKDATE datetime = null output
  ,@OPPORTUNITYASKAMOUNT money = null output
  ,@LASTPAYMENTSEQUENCE int = null output
  ,@REFERENCE nvarchar(255) = null output
  ,@CATEGORYCODEID uniqueidentifier = null output
  ,@ADJUSTMENTDATE datetime = null output
  ,@ADJUSTMENTPOSTDATE datetime = null output
  ,@ADJUSTMENTPOSTSTATUSCODE tinyint = null output
  ,@ADJUSTMENTREASON nvarchar(300) = null output
  ,@ADJUSTMENTREASONCODEID uniqueidentifier = null output
  ,@HASPOSTEDPAYMENTS bit = null output
  ,@ADJPAYMENT_DATE datetime = null output
  ,@ADJPAYMENT_POSTDATE datetime = null output
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier = null output
  ,@ADJPAYMENT_DETAILS nvarchar(255) = null output
  ,@PERCENTAGEBENEFITS xml = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  ,@BASEEXCHANGERATEID uniqueidentifier = null output
  ,@EXCHANGERATE decimal(20, 8) = null output
  ,@HADSPOTRATE bit = null output
  ,@RATECHANGED bit = null output
  ,@BASEDECIMALDIGITS tinyint = null output
  ,@BASEROUNDINGTYPECODE tinyint = null output
  ,@TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output
  ,@HASRECOGNITIONCREDIT bit = null output
  ,@UPDATERECOGNITIONOPTION tinyint = null output
  ,@UPDATETRIBUTEOPTION tinyint = null output
  ,@HASTRIBUTE bit = null output
  ,@VALIDATETRIBUTES bit = null output
  ,@ALLOWGLDISTRIBUTIONS bit = null output
  ,@ISMEMBERSHIPPLEDGE bit = null output
  ,@HASPAYMENTS bit = null output
  ,@PROMPTONADJUSTMENTDATECHANGE bit = null output
  ,@ADJPAYMENT_POSTDATEDEPOSIT datetime = null output
  ,@INSTALLMENTAMOUNT money = null output
  ,@ORIGINALINSTALLMENTAMOUNT money = null output  
  ,@TOTALAMOUNTWRITTENOFF money = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;
  set @TSLONG = 0;

  declare @LASTINSTALLMENT uniqueidentifier;
  declare @LASTUNPAIDINSTALLMENTSEQUENCE int;
  declare @FIRSTUNPAIDINSTALLMENTSEQUENCE int;
  declare @MAXINSTALLMENTSEQUENCE int;

  select @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
    ,@ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
    ,@ADJUSTMENTPOSTSTATUSCODE = 0

  select @DATALOADED = 1
    ,@CONSTITUENTID = CONSTITUENT.ID
    ,@CONSTITUENTNAME = CONSTITUENT.name
    ,@DATE = REVENUE.[DATE]
    ,@AMOUNT = REVENUE.TRANSACTIONAMOUNT
    ,@POSTSTATUSCODE = 0
    ,@MAXSOFTCREDITAMOUNT = 0
    ,@MAXSOLICITORAMOUNT = coalesce((
        select max(AMOUNT)
        from dbo.REVENUESOLICITOR
        where REVENUESPLITID = REVENUE.ID
        ), 0)
    ,@POSTDATE = REVENUE.POSTDATE
    ,@SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER
    ,@FREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE
    ,@NUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS
    ,@NEXTTRANSACTIONDATE = coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE)
    ,@TSLONG = REVENUE.TSLONG
    ,@FINDERNUMBER = REVENUE_EXT.FINDERNUMBER
    ,@SOURCECODE = REVENUE_EXT.SOURCECODE
    ,@APPEALID = REVENUE_EXT.APPEALID
    ,@BENEFITSWAIVED = REVENUE_EXT.BENEFITSWAIVED
    ,@GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
    ,@MAILINGID = REVENUE_EXT.MAILINGID
    ,@CHANNELCODEID = REVENUE_EXT.CHANNELCODEID
    ,@DONOTACKNOWLEDGE = REVENUE_EXT.DONOTACKNOWLEDGE
    ,@PLEDGESUBTYPEID = PLEDGESUBTYPEID
    ,@REFERENCE = REVENUEREFERENCE.REFERENCE
    ,@CATEGORYCODEID = (
      select top 1 GLREVENUECATEGORYMAPPINGID
      from dbo.REVENUECATEGORY
      inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
      where REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
        and (REVENUESPLIT.DELETEDON is null)
        and (REVENUESPLIT.TYPECODE <> 1)
      )
    ,@BASECURRENCYID = BASECURRENCY.ID
    ,@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
    ,@BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID
    ,@EXCHANGERATE = CURRENCYEXCHANGERATE.RATE
    ,@HADSPOTRATE = case 
      when CURRENCYEXCHANGERATE.TYPECODE = 2
        then 1
      else 0
      end
    ,@RATECHANGED = 0
    ,@BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS
    ,@BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
    ,@TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID)
    ,@ISMEMBERSHIPPLEDGE = case REVENUE.TYPECODE when 15 then 1 else 0 end
  from dbo.FINANCIALTRANSACTION as REVENUE
  inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
  inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
  left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I with (noexpand) on REVENUE.ID = V_BASECURRENCYFORFINANCIALTRANSACTION_I.FINANCIALTRANSACTIONID
  cross apply (
    select case when REVENUE.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V_BASECURRENCYFORFINANCIALTRANSACTION_I.BASECURRENCYID) else null end as ID
  ) as BASECURRENCY
  inner join dbo.CURRENCY on CURRENCY.ID = BASECURRENCY.ID
  left join dbo.REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
  left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
  where REVENUE.ID = @ID
    and REVENUE.TYPECODE in (1,15);

  if @DATALOADED = 1
  begin
    select @ADJUSTMENTDATE = [DATE]
      ,@ADJUSTMENTPOSTDATE = POSTDATE
      ,@ADJUSTMENTPOSTSTATUSCODE = 1
      ,@ADJUSTMENTREASON = REASON
      ,@ADJUSTMENTREASONCODEID = REASONCODEID
    from dbo.ADJUSTMENT
    where REVENUEID = @ID
      and POSTSTATUSCODE = 1

    select top 1 @ADJPAYMENT_DATE = ADJ.[DATE]
      ,@ADJPAYMENT_POSTDATE = ADJ.POSTDATE
      ,@ADJPAYMENT_DETAILS = ADJ.REASON
      ,@ADJPAYMENT_REASONCODEID = ADJ.REASONCODEID
    from (
      select A.[DATE]
        ,A.POSTDATE
        ,A.REASON
        ,A.REASONCODEID
      from dbo.ADJUSTMENT A
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on A.ID = FTL.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
      inner join dbo.INSTALLMENTPAYMENT I on FTL.ID = I.PAYMENTID
      where I.PLEDGEID = @ID
        and A.POSTSTATUSCODE = 1
        and FTL.DELETEDON is null

      union all

      select A.[DATE]
        ,A.POSTDATE
        ,A.REASON
        ,A.REASONCODEID
      from dbo.WRITEOFFADJUSTMENT A
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on A.ID = FTL.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID 
      inner join dbo.FINANCIALTRANSACTION FT on FTL.FINANCIALTRANSACTIONID=FT.ID
      where FT.PARENTID=@ID
        and A.POSTSTATUSCODE = 1
        and FTL.DELETEDON is null
      ) ADJ
    order by ADJ.[DATE] desc

    if @ADJUSTMENTREASONCODEID is null
    begin
 select @ADJUSTMENTDATE = [DATE]
        ,@ADJUSTMENTPOSTDATE = POSTDATE
        ,@ADJUSTMENTPOSTSTATUSCODE = 1
        ,@ADJUSTMENTREASON = REASON
        ,@ADJUSTMENTREASONCODEID = REASONCODEID
      from dbo.BENEFITADJUSTMENT
      where REVENUEID = @ID
        and POSTSTATUSCODE = 1
    end

    select top 1 @OPPORTUNITYID = RO.OPPORTUNITYID
      ,@OPPORTUNITYCONSTITUENTNAME = OC.name
      ,@OPPORTUNITYASKDATE = O.ASKDATE
      ,@OPPORTUNITYASKAMOUNT = O.ASKAMOUNT
    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.FINANCIALTRANSACTION on REVENUESPLIT.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    left join dbo.REVENUEOPPORTUNITY RO on RO.ID = REVENUESPLIT.ID
    left join dbo.OPPORTUNITY O on O.ID = RO.OPPORTUNITYID
    left join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
    left join dbo.CONSTITUENT OC on OC.ID = PP.PROSPECTID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID
      and FINANCIALTRANSACTION.TYPECODE in (
        0
        ,1
        ,2
        ,3
        ,4
        ,5
        ,6
        ,7
        ,8
        ,9
        )
      and (REVENUESPLIT.DELETEDON is null)
      and (REVENUESPLIT.TYPECODE <> 1);

    set @AMOUNTPAID = @AMOUNT - dbo.UFN_PLEDGE_GETBALANCE(@ID);
    set @MAXINSTALLMENTSEQUENCE = @NUMBEROFINSTALLMENTS;

    --Find the first and last installment that have a balance/no payments
    select @LASTPAYMENTSEQUENCE = COALESCE(MAX(INSTALLMENT.SEQUENCE), 0)
      ,@NUMBEROFINSTALLMENTS = COUNT(INSTALLMENT.ID)
    from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@ID) INSTALLMENT
    where INSTALLMENT.BALANCE > 0;


    select @INSTALLMENTAMOUNT = PLEDGEINSTALLMENTOPTION.INSTALLMENTAMOUNT
    from dbo.FINANCIALTRANSACTION
    left outer join dbo.PLEDGEINSTALLMENTOPTION on FINANCIALTRANSACTION.ID = PLEDGEINSTALLMENTOPTION.ID 
    where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null; 

    set @ORIGINALINSTALLMENTAMOUNT = @INSTALLMENTAMOUNT;

    select @TOTALAMOUNTWRITTENOFF = isnull(SUM(I.AMOUNTWRITTENOFF) ,0)
      from dbo.UFN_INSTALLMENT_GETINFO_BULK() I
      where I.REVENUEID = @ID

    --Bug 70059 - AdamBu 12/8/09 - Ordering the list of splits so that it will be consistent with the list of
    -- installment splits, which is important when using "Distribute evenly" on the form.  This change was made
    -- here instead of in the GETSPLITS function due to performance concerns.
    set @SPLITS = (
        select SPLITS.TRANSACTIONAMOUNT AMOUNT
          ,SPLITS.APPLICATIONCODE
          ,SPLITS.CATEGORYCODEID
          ,SPLITS.DECLINESGIFTAID
          ,SPLITS.DESIGNATIONID
          ,SPLITS.ID
          ,SPLITS.TYPECODE
          ,SPLITS.TRANSACTIONCURRENCYID
          ,SPLITS.BASEEXCHANGERATEID
        from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
        left join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
        order by DESIGNATION.VANITYNAME
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('SPLITS')
          ,binary BASE64
        );
    set @INSTALLMENTS = (
        select I.ID
          ,I.[DATE]
          ,I.TRANSACTIONAMOUNT AMOUNT
          ,I.TRANSACTIONRECEIPTAMOUNT RECEIPTAMOUNT
          ,(I.TRANSACTIONAMOUNT - (I.AMOUNTPAID + I.AMOUNTWRITTENOFF)) as BALANCE
          ,(I.AMOUNTPAID + I.AMOUNTWRITTENOFF) as APPLIED
          ,I.SEQUENCE
          ,I.PAYMENTCOUNT
          ,I.WRITEOFFCOUNT
          ,I.AMOUNTWRITTENOFF
          ,(
            select SPLITINFO.ID
              ,SPLITINFO.DESIGNATIONID
              ,SPLITINFO.TRANSACTIONAMOUNT AMOUNT
              ,(SPLITINFO.AMOUNTPAID + SPLITINFO.AMOUNTWRITTENOFF) APPLIED
              ,SPLITINFO.TRANSACTIONCURRENCYID
              ,SPLITINFO.REVENUESPLITID
              ,SPLITINFO.PAYMENTCOUNT
              ,SPLITINFO.WRITEOFFCOUNT
              ,(
                select SPLITPAYMENT.ID
                      ,SPLITPAYMENT.AMOUNT
                      ,I.REVENUEID as TRANSACTIONID
                      ,SPLITINFO.REVENUESPLITID as LINEITEMID
                from dbo.INSTALLMENTSPLITPAYMENT SPLITPAYMENT
                where SPLITPAYMENT.INSTALLMENTSPLITID = SPLITINFO.ID
                for xml raw('ITEM')
                  ,type
                  ,elements
                  ,binary BASE64
               ) as INSTALLMENTSPLITPAYMENTS
              ,(
                select SPLITWRITEOFF.ID
                      ,SPLITWRITEOFF.TRANSACTIONAMOUNT as AMOUNT
                      ,I.REVENUEID as TRANSACTIONID
                      ,SPLITINFO.REVENUESPLITID as LINEITEMID
                from dbo.INSTALLMENTSPLITWRITEOFF SPLITWRITEOFF
                where SPLITWRITEOFF.INSTALLMENTSPLITID = SPLITINFO.ID
                for xml raw('ITEM')
                  ,type
                  ,elements
                  ,binary BASE64
               ) as INSTALLMENTSPLITWRITEOFFS
            from dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITINFO_BULK() SPLITINFO
            left join dbo.DESIGNATION on DESIGNATION.ID = SPLITINFO.DESIGNATIONID
            where SPLITINFO.INSTALLMENTID = I.ID
            order by DESIGNATION.VANITYNAME
            for xml raw('ITEM')
              ,type
              ,elements
              ,binary BASE64
            ) as INSTALLMENTSPLITS
          ,I.BASECURRENCYID
          ,I.ORGANIZATIONAMOUNT
          ,I.ORGANIZATIONEXCHANGERATEID
          ,I.TRANSACTIONCURRENCYID
          ,I.BASEEXCHANGERATEID
        from dbo.UFN_INSTALLMENT_GETINFO_BULK() I
        where I.REVENUEID = @ID
        order by I.[DATE]
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('INSTALLMENTS')
          ,binary BASE64
        );

    exec dbo.USP_REVENUE_GETBENEFITSSPLIT @ID
      ,@BENEFITS output
      ,@PERCENTAGEBENEFITS output

    if @FINDERNUMBER = 0
      set @FINDERNUMBER = null;

    if (not @FINDERNUMBER is null)
      set @FINDERNUMBERVALID = 1;
    else
      set @FINDERNUMBERVALID = 0;
  end

  -- PBI 40790
  set @HASPOSTEDPAYMENTS = dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@ID)
  set @HASPAYMENTS = dbo.UFN_PLEDGE_PAYMENTSEXIST(@ID);

  if exists (
      select 1
      from dbo.REVENUERECOGNITION
      inner join dbo.REVENUESPLIT on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
      where REVENUESPLIT.REVENUEID = @ID
      )
    set @HASRECOGNITIONCREDIT = 1
  else
    set @HASRECOGNITIONCREDIT = 0

  set @UPDATERECOGNITIONOPTION = 0

  if exists (
      select 1
      from dbo.REVENUETRIBUTE
      where REVENUETRIBUTE.REVENUEID = @ID
      )
    set @HASTRIBUTE = 1
  else
    set @HASTRIBUTE = 0

  set @UPDATETRIBUTEOPTION = 0;
  set @VALIDATETRIBUTES = 1;

  -- Check GL business rule for this account system and set to 'Do not post' if needed.
  declare @PDACCOUNTSYSTEMID uniqueidentifier;

  select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
  from dbo.PDACCOUNTSYSTEMFORREVENUE
  where ID = @ID;

  set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

  if @ALLOWGLDISTRIBUTIONS = 0
    set @ADJUSTMENTPOSTDATE = null;

  select
  @ADJPAYMENT_POSTDATEDEPOSIT = case when count(distinct Deposit.POSTDATE) = 1 then max(Deposit.POSTDATE) else null end,
  @PROMPTONADJUSTMENTDATECHANGE = case when count(*) > 0 then 1 else 0 end
  from dbo.FINANCIALTRANSACTIONLINEITEM as Pledge inner join dbo.FINANCIALTRANSACTIONLINEITEM as Payment on Pledge.ID = Payment.SourceLineItemID
  inner join dbo.BANKACCOUNTDEPOSITPAYMENT on Payment.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
  inner join dbo.FINANCIALTRANSACTION as Deposit on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = Deposit.ID
  where Pledge.FINANCIALTRANSACTIONID = @ID
  and Pledge.DELETEDON is null
  and Pledge.TYPECODE = 0
  and Payment.DELETEDON is null
  and Payment.TYPECODE = 0

  return 0;
end