USP_DATAFORMTEMPLATE_VIEW_REVENUEUPDATEBATCH_GETREVENUE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUEUPDATEBATCH_GETREVENUE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REVENUESPLITID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output, --disabled  
    @AMOUNT money = null output,
    @DATE datetime = null output,
    @TYPECODE tinyint = null output, --disabled
    @PAYMENTMETHODCODE tinyint = null output,
    @APPLICATIONINFO nvarchar(60) = null output,
    @APPEALID uniqueidentifier = null output,
    @OPPORTUNITYID uniqueidentifier = null output,
    @SINGLEDESIGNATIONID uniqueidentifier = null output,
    @POSTSTATUSCODE tinyint = null output,
    @POSTDATE datetime = null output,
    @DONOTACKNOWLEDGE bit = null output,
    @DONOTRECEIPT bit = null output,
    @MAILINGID uniqueidentifier = null output,
    @FINDERNUMBER bigint = null output,
    @SOURCECODE nvarchar(60) = null output,
    @CHANNELCODEID uniqueidentifier = null output,
    @GIVENANONYMOUSLY bit = null output,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
    @RECEIPTAMOUNT money = null output,
    @RECEIPTTYPECODE tinyint = null output,
    @REFERENCE nvarchar(255) = null output,
    @CATEGORYCODEID uniqueidentifier = null output,
    @REVENUELOOKUPID nvarchar(100) = null output,
    @ISADJUSTMENT bit = null output,
    @ADJUSTMENTDATE datetime = null output,
    @ADJUSTMENTPOSTDATE datetime = null output,
    @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
    @ADJUSTMENTREASON nvarchar(300) = null output,
    @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
    --check fields
    @CHECKDATE dbo.UDT_FUZZYDATE = null output,
    @CHECKNUMBER nvarchar(20) = null output,
    --credit card fields
    @CREDITTYPECODEID uniqueidentifier = null output,
    @CREDITCARDNUMBER nvarchar(20) = null output,
    @CARDHOLDERNAME nvarchar(255) = null output,
    @EXPIRESON dbo.UDT_FUZZYDATE = null output,
    @AUTHORIZATIONCODE nvarchar(20) = null output,
    --direct debit fields
    @CONSTITUENTACCOUNTID uniqueidentifier = null output,
    @DIRECTDEBITISREJECTED bit = null output,
    @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
    @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
    @REFERENCENUMBER nvarchar(20) = null output,
    --gift in kind fields
    @GIFTINKINDITEMNAME nvarchar(100) = null output,
    @GIFTINKINDDISPOSITIONCODE tinyint = null output,
    @GIFTINKINDNUMBEROFUNITS int = null output,
    @GIFTINKINDFAIRMARKETVALUE money = null output,
    @GIFTINKINDSUBTYPECODEID uniqueidentifier = null output,
    --property details
    @PROPERTYSUBTYPECODEID uniqueidentifier = null output,
    --stock
    @ISSUER nvarchar(100) = null output,
    @NUMBEROFUNITS decimal(20,3) = null output,
    @SYMBOL nvarchar(25) = null output,
    @MEDIANPRICE decimal(19,4) = null output,
    @LOWPRICE decimal(19,4) = null output,
    @HIGHPRICE decimal(19,4) = null output,
    @SPLITS xml = null output,
    @MULTIPLESPLITS bit = null output,
    --installments
    @INSTALLMENTS xml = null output,  
    @INSTALLMENTFREQUENCYCODE tinyint = null output,
    @INSTALLMENTSTARTDATE datetime = null output,
    @INSTALLMENTENDDATE datetime = null output,
    @NUMBEROFINSTALLMENTS int = null output,
    @SENDPLEDGEREMINDER bit = null output,  
    @ACKNOWLEDGEDATE datetime = null output,  
    @ACKNOWLEDGEEID uniqueidentifier = null output,  
    @LETTERCODEID uniqueidentifier = null output,
    --lockbox
    @LOCKBOXID uniqueidentifier = null output,
    @LOCKBOXBATCHNUMBER nvarchar(100) = null output,
    @LOCKBOXBATCHSEQUENCE int = null output,
    @ADDITIONALAPPLICATIONSSTREAM xml = null output,
    --notes
    @NOTETITLE nvarchar(50) = null output,
    @NOTEAUTHORID uniqueidentifier = null output,
    @NOTEDATEENTERED datetime = null output,
    @NOTETYPECODEID uniqueidentifier = null output,
    @NOTETEXTNOTE nvarchar(max) = null output,
    --misc  
    @OTHERTYPECODEID uniqueidentifier = null output,    
    @PLEDGESUBTYPEID uniqueidentifier = null output,
    @ORIGINALAPPLICATIONINFO nvarchar(60) = null output,
    @REVENUESTREAMS xml = null output,
    @TRIBUTES xml = null output,
    @BENEFITS xml = null output,
    @PERCENTAGEBENEFITS xml = null output,
    @MATCHINGGIFTS xml = null output,
    @APPLICATIONSOLICITORS xml = null output,
    @APPLICATIONRECOGNITIONS xml = null output,
    @APPLICATIONBUSINESSUNITS xml = null output,
    @LETTERS xml = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @BASECURRENCYID uniqueidentifier = null output,
    @BASEEXCHANGERATEID uniqueidentifier = null output,
    @EXCHANGERATE decimal(20,8) = null output,
    @BASEAMOUNT money = null output,
    @NOTES xml = null output,
    @ISSPONSORSHIPRG bit = null output,
    @LASTPAYMENTSEQUENCE int = null output,
    @LASTUNPAIDROW int = null output,
    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
     -- UK fields
    @AMOUNTFORVAT money = null output,
    @VATTAXRATEID uniqueidentifier = null output,
    @VATAMOUNT money = null output,
    @STANDINGORDERSETUP bit = null output,
    @STANDINGORDERSETUPDATE datetime = null output,
    @USESYSTEMGENERATEDREFERENCENUMBER bit = null output,
    @STANDINGORDERREFERENCENUMBER nvarchar(18) = null output,
    @DDISOURCECODEID uniqueidentifier = null output,
    @DDISOURCEDATE date = null output,
    @INSTALLMENTSCHEDULESEEDDATE date = null output,
    @ISPOSTED bit = null output,
    @HASPOSTEDPAYMENTS bit = null output,
    @ORIGINALINSTALLMENTS xml = null output,
    @CREDITCARDTOKEN uniqueidentifier = null output,
    @DEPOSITID uniqueidentifier = null output,
    @PLEDGEAMOUNTPAID money = null output,
    @NUMBEROFINSTALLMENTSFORWEBSHELL int = null output,
    @HASSOLDFIXEDASSETS bit = null output,
    @SALEAMOUNT money = null output,
    @SALEDATE datetime = null output,
    @BROKERFEE money = null output,
    @SALEPOSTSTATUSCODE tinyint = null output,
    @SALEPOSTDATE datetime = null output,
    @GIFTAIDCOMMITTED bit = null output,
    @RGSTATUSCODE tinyint = null output,
    @INSTALLMENTAMOUNTFORWEBSHELL money = null output,
    @INSTALLMENTAMOUNT money = null output,
    @ADJPAYMENT_POSTDATEDEPOSIT date = null output,
    @PROMPTONADJUSTMENTDATECHANGE bit = null output,
    @BENEFITSWAIVED bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @MULTIPLESPLITS = 0;

    --adjustment fields 
    declare @ISPAYMENT bit = 0;
    declare @ISPLEDGE bit = 0;
        declare @ISRECURRINGGIFT bit = 0;
    set @ISPOSTED  = 0;
    declare @ISMISCELLANEOUSPAYMENT bit = 0;
    declare @ISORDERAPPLICATION bit = 0;
    declare @HASGIFTAIDSPLITONPENDINGR68PROCESS bit;

    -- main batch row fields
    select 
        @DATALOADED = 1,
        @REVENUESPLITID =  case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
                                                         else (select ID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) end,
        @CONSTITUENTID = R.CONSTITUENTID,
        @AMOUNT = R.TRANSACTIONAMOUNT,
        @DATE = R.DATE,
        @TYPECODE = case when R.TRANSACTIONTYPECODE = 2 then 3 else R.TRANSACTIONTYPECODE end,
        @PAYMENTMETHODCODE = case when (R.TRANSACTIONTYPECODE = 2 and RP.PAYMENTMETHODCODE = 2 and RS.CREDITCARDID is null) then 98 else RP.PAYMENTMETHODCODE end,
        @APPLICATIONINFO = dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONINFO(@ID),
        @APPEALID = R.APPEALID,
        @OPPORTUNITYID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
                                         else (select OPPORTUNITYID from dbo.REVENUEOPPORTUNITY RO inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                                         where RS.REVENUEID = R.ID AND RS.APPLICATIONCODE IN (0,7)) end,
        @SINGLEDESIGNATIONID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
                                                         else (select DESIGNATIONID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) end,
        @POSTSTATUSCODE = case when R.DONOTPOST = 1 then 2 else 1 end,
        @POSTDATE = R.POSTDATE,    
        @DONOTACKNOWLEDGE = R.DONOTACKNOWLEDGE,
        @DONOTRECEIPT = R.DONOTRECEIPT,
        @MAILINGID = R.MAILINGID,
        @FINDERNUMBER = case R.FINDERNUMBER when 0 then null else R.FINDERNUMBER end,
        @SOURCECODE = R.SOURCECODE,
        @CHANNELCODEID = R.CHANNELCODEID,
        @GIVENANONYMOUSLY  = R.GIVENANONYMOUSLY,
        @OTHERPAYMENTMETHODCODEID = OP.OTHERPAYMENTMETHODCODEID,  
        @RECEIPTAMOUNT = R.RECEIPTAMOUNT,
        @RECEIPTTYPECODE = R.RECEIPTTYPECODE,
        @REFERENCE = RR.REFERENCE,
                @CATEGORYCODEID = case 
                                                        when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then 
                                                                case when (R.TRANSACTIONTYPECODE = 1 or R.TRANSACTIONTYPECODE = 2) -- display revenue category for pledge or recurring gift (it is the same for each split)
                                                                        then
                                                                        (select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUECATEGORY.ID where REVENUESPLIT.REVENUEID = R.ID)
                                                                 else
                                                                    null 
                                                                 end                                                                                
                                                         else (select GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUECATEGORY.ID where REVENUESPLIT.REVENUEID = R.ID) end,                                             
        @REVENUELOOKUPID = R.LOOKUPID,
        @ISPAYMENT = case when R.TRANSACTIONTYPECODE = 0 then 1 else 0 end,
        @ISPOSTED = case when P.ID is null then 0 else 1 end,
        @ISPLEDGE = case when R.TRANSACTIONTYPECODE = 1 then 1 else 0 end,
                @ISRECURRINGGIFT = case when R.TRANSACTIONTYPECODE = 2 then 1 else 0 end,
                @SPLITS = case when R.TRANSACTIONTYPECODE in (1,2) then dbo.UFN_REVENUEUPDATEBATCH_GETSPLITS_TOITEMLISTXML(@ID) else null end,
                @INSTALLMENTS = case when R.TRANSACTIONTYPECODE = 1 then dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@ID) else null end,
            @ORIGINALINSTALLMENTS = case when R.TRANSACTIONTYPECODE = 1 then dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@ID) else null end,
                @INSTALLMENTFREQUENCYCODE = RS.FREQUENCYCODE, 
                @INSTALLMENTSTARTDATE = case when R.TRANSACTIONTYPECODE = 2 then RS.STARTDATE else coalesce(RS.NEXTTRANSACTIONDATE, RS.STARTDATE) end,
                @INSTALLMENTENDDATE = RS.ENDDATE,
                @NUMBEROFINSTALLMENTS = RS.NUMBEROFINSTALLMENTS,
                @SENDPLEDGEREMINDER = coalesce(RS.SENDPLEDGEREMINDER,1),
                @LOCKBOXID = RB.LOCKBOXID,
                @LOCKBOXBATCHNUMBER = RB.BATCHNUMBER,
                @LOCKBOXBATCHSEQUENCE = RB.BATCHSEQUENCE,
                @ADDITIONALAPPLICATIONSSTREAM = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETADDITIONALAPPLICATIONS_TOITEMLISTXML(@ID) else null end,
                --@NOTETITLE = RN.TITLE,
                --@NOTEAUTHORID = RN.AUTHORID,
                --@NOTEDATEENTERED = RN.DATEENTERED,
                --@NOTETYPECODEID = RN.REVENUENOTETYPECODEID,
                --@NOTETEXTNOTE = RN.TEXTNOTE,
                @OTHERTYPECODEID = case when (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = R.ID) <> 1 then null
                                                                 else (select OTHERTYPECODEID from dbo.REVENUESPLITOTHER inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITOTHER.ID where REVENUESPLIT.REVENUEID = R.ID) end,
                @PLEDGESUBTYPEID = RS.PLEDGESUBTYPEID,
                @REVENUESTREAMS = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONSTREAM_TOITEMLISTXML(@ID, R.CONSTITUENTID) else null end,
                @TRIBUTES = case when R.TRANSACTIONTYPECODE in (0,1) then dbo.UFN_REVENUEUPDATEBATCH_GETTRIBUTES_TOITEMLISTXML(@ID) else null end,
                @MATCHINGGIFTS = case when R.TRANSACTIONTYPECODE = 0 then dbo.UFN_REVENUEUPDATEBATCH_GETMATCHINGGIFTS_TOITEMLISTXML(@ID) else null end,
                @APPLICATIONRECOGNITIONS = dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONS_TOITEMLISTXML(@ID),
                @APPLICATIONSOLICITORS = dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORS_TOITEMLISTXML(@ID),
                @APPLICATIONBUSINESSUNITS = dbo.UFN_REVENUEUPDATEBATCH_GETBUSINESSUNITS(@ID, R.TRANSACTIONTYPECODE),
                @LETTERS = dbo.UFN_REVENUEUPDATEBATCH_GETREVENUELETTERS(@ID),
        @BASECURRENCYID = R.BASECURRENCYID,
        @TRANSACTIONCURRENCYID = R.TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = R.BASEEXCHANGERATEID,
        @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE,
                @BASEAMOUNT = R.AMOUNT,
                @ISSPONSORSHIPRG = case when R.TRANSACTIONTYPECODE = 2 and (select top 1 TYPECODE from dbo.REVENUESPLIT where REVENUEID = @ID) = 9 then 1 else 0 end,
                @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID,
            @AMOUNTFORVAT = REVENUEVAT.TRANSACTIONAMOUNTTOTAX,
            @VATTAXRATEID = REVENUEVAT.VATTAXRATEID,
            @VATAMOUNT = REVENUEVAT.TRANSACTIONVATAMOUNT,
            @PLEDGEAMOUNTPAID = case
                                when R.TRANSACTIONTYPECODE = 1 then (R.TRANSACTIONAMOUNT - dbo.UFN_PLEDGE_GETBALANCE(R.ID))
                                else null
                                end,
                @RGSTATUSCODE = RS.STATUSCODE
    from dbo.REVENUE R
    inner join dbo.REVENUEPAYMENTMETHOD RP on R.ID = RP.REVENUEID  
        left join dbo.PDACCOUNTSYSTEMFORREVENUE on R.ID = PDACCOUNTSYSTEMFORREVENUE.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL OP on OP.ID = RP.ID
    left join dbo.REVENUEREFERENCE RR on R.ID = RR.ID
    left join dbo.REVENUEPOSTED P on P.ID = R.ID
    left join dbo.REVENUESCHEDULE RS on RS.ID = R.ID
    left join dbo.REVENUELETTER RL on RL.REVENUEID = R.ID
    left join dbo.REVENUELOCKBOX RB on RB.ID = R.ID
        left join dbo.REVENUENOTE RN on RN.REVENUEID = R.ID
        left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = R.BASEEXCHANGERATEID
        left join dbo.REVENUEVAT on R.ID = REVENUEVAT.ID
    where R.ID = @ID     

    -- If the revenue has been committed through the R68 process, we want to disable the gift aid fields
    if exists
        (
            select
                1
            from
                @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)
            where
                T.c.value('GIFTAIDCOMMITTED[1]', 'bit') = 1
        )
    begin
        set @GIFTAIDCOMMITTED = 1;
    end
    else
    begin
        set @GIFTAIDCOMMITTED = 0;
    end

    if not @APPLICATIONINFO is null
         set @ORIGINALAPPLICATIONINFO = @APPLICATIONINFO
         if len(@APPLICATIONINFO) > 3
                set @SINGLEDESIGNATIONID = null

    if @SINGLEDESIGNATIONID is null and @TYPECODE in (1,3)
        set @MULTIPLESPLITS = 1

    -- payment related fields

        exec dbo.USP_REVENUE_GETPAYMENTDETAILS
                @REVENUEID = @ID,            
                @CHECKDATE = @CHECKDATE output,
                @CHECKNUMBER = @CHECKNUMBER output,

                @CARDHOLDERNAME = @CARDHOLDERNAME output,
                @CREDITCARDNUMBER = @CREDITCARDNUMBER output,
                @CREDITTYPECODEID = @CREDITTYPECODEID output,
                @AUTHORIZATIONCODE = @AUTHORIZATIONCODE output,
                @EXPIRESON = @EXPIRESON output,

                @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output,
                @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED output,
                @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE output,
                @REFERENCEDATE = @REFERENCEDATE output,
                @REFERENCENUMBER = @REFERENCENUMBER output,

                @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME output,
                @GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE output,
                @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS output,
                @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE output,
                @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID output,

                @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID output,

                @ISSUER = @ISSUER output,
                @NUMBEROFUNITS = @NUMBEROFUNITS output,
                @SYMBOL = @SYMBOL output,
                @MEDIANPRICE = @MEDIANPRICE output,
                @LOWPRICE = @LOWPRICE output,
                @HIGHPRICE = @HIGHPRICE output,
                @SALEAMOUNT = @SALEAMOUNT output,
                @SALEDATE = @SALEDATE output,
                @BROKERFEE = @BROKERFEE output,
                @SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE output,
                @SALEPOSTDATE = @SALEPOSTDATE output;

    if @PAYMENTMETHODCODE = 3 --Direct Debit
    begin
        --Direct Debit w/ Paperless mandate fields is UK only                        
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
        begin
            select 
                @REFERENCEDATE = REFERENCEDATE,
                @REFERENCENUMBER = REFERENCENUMBER,
                @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
                @DDISOURCECODEID = DDISOURCECODEID,
                @DDISOURCEDATE = DDISOURCEDATE
                --@SENDNEWINSTRUCTION = SENDPMINSTRUCTION,
                --@NEWINSTRUCTIONTOSEND = case when PMINSTRUCTIONTOSENDCODE = 0 then null else PMINSTRUCTIONTOSENDCODE end,
                --@PMINSTRUCTIONDATE_NEW = PMINSTRUCTIONDATE_NEW,
                --@PMINSTRUCTIONDATE_CANCEL = PMINSTRUCTIONDATE_CANCEL,
                --@PMINSTRUCTIONDATE_SETUP = PMINSTRUCTIONDATE_SETUP,
                --@PMADVANCENOTICESENTDATE = PMADVANCENOTICESENTDATE
            from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
                where ID = @ID 
        end
        else
        begin
            select 
                @REFERENCEDATE = REFERENCEDATE,
                @REFERENCENUMBER = REFERENCENUMBER,
                @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
            from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
                where ID = @ID
        end
    end
    if @PAYMENTMETHODCODE = 11 -- standing order
    begin
        select 
            @REFERENCEDATE = REFERENCEDATE,
            @REFERENCENUMBER = N'',
            @STANDINGORDERREFERENCENUMBER = dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(REVENUESCHEDULESTANDINGORDERPAYMENT.ID),
            @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID,
                 @STANDINGORDERSETUP = STANDINGORDERSETUP,
                 @STANDINGORDERSETUPDATE = STANDINGORDERSETUPDATE,
            @USESYSTEMGENERATEDREFERENCENUMBER = USESYSTEMGENERATEDREFERENCENUMBER
        from dbo.REVENUESCHEDULESTANDINGORDERPAYMENT
        left join dbo.REVENUESTANDINGORDER on REVENUESTANDINGORDER.ID = REVENUESCHEDULESTANDINGORDERPAYMENT.ID
        where REVENUESCHEDULESTANDINGORDERPAYMENT.ID = @ID

        if @USESYSTEMGENERATEDREFERENCENUMBER is null
             select @USESYSTEMGENERATEDREFERENCENUMBER = 0;
        if @STANDINGORDERSETUP is null
             select @STANDINGORDERSETUP = 0
    end;

        set @HASSOLDFIXEDASSETS = 0;
        if @PAYMENTMETHODCODE in (4, 6) --stock, gift-in-kind
        begin
            -- sold stock or sold gift-in-kind
            if exists (select 1 from dbo.FINANCIALTRANSACTION
                                 where FINANCIALTRANSACTION.PARENTID = @ID
                                 and FINANCIALTRANSACTION.TYPECODE in (21,27))
                set @HASSOLDFIXEDASSETS = 1;
        end

        if @PAYMENTMETHODCODE = 5  --Property
        begin
            -- sold property
            if exists (select 1 from dbo.FINANCIALTRANSACTION
                                 inner join dbo.PROPERTYDETAIL_EXT on PROPERTYDETAIL_EXT.ID = FINANCIALTRANSACTION.ID
                                 where FINANCIALTRANSACTION.PARENTID = @ID
                                 and PROPERTYDETAIL_EXT.SALEDATE is not null)
                set @HASSOLDFIXEDASSETS = 1;
        end

    -- adjustment logic
    if exists(select ID from dbo.REVENUESPLIT where REVENUEID = @ID and APPLICATIONCODE = 11)
    begin
        set @ISMISCELLANEOUSPAYMENT = 1;
    end    

    if @ISPAYMENT = 1
        if exists(select 'x' from dbo.SALESORDERPAYMENT
                    inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDERPAYMENT.SALESORDERID
                    where SALESORDERPAYMENT.PAYMENTID = @ID and SALESORDERITEM.TYPECODE <> 12) 
            begin
                select @ISORDERAPPLICATION = 1        
                from dbo.SALESORDERPAYMENT
                where PAYMENTID = @ID;  
            end            
    set @HASGIFTAIDSPLITONPENDINGR68PROCESS = dbo.UFN_REVENUE_HASGIFTAIDSPLITONPENDINGR68(@ID, @ISPAYMENT)

    if @ISPLEDGE = 1 or @ISRECURRINGGIFT = 1
        begin
            if @PAYMENTMETHODCODE = 98 -- if this is a reference only credit card method then retrieve the cc details from the CREDITCARDPAYMENTMETHODDETAIL table 
            begin
                select 
                    @CARDHOLDERNAME = D.CARDHOLDERNAME,
                    @CREDITCARDNUMBER = D.CREDITCARDPARTIALNUMBER,
                    @CREDITTYPECODEID = D.CREDITTYPECODEID,
                    @EXPIRESON = D.EXPIRESON
                from dbo.REVENUEPAYMENTMETHOD M
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL D on M.ID =  D.ID
                where M.REVENUEID = @ID;
            end
            else
            begin
                select
                    @CARDHOLDERNAME = CREDITCARD.CARDHOLDERNAME,
                    @CREDITCARDNUMBER = CREDITCARD.CREDITCARDPARTIALNUMBER,
                    @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID,
                    @EXPIRESON = CREDITCARD.EXPIRESON,
                    @CREDITCARDTOKEN = CREDITCARD.CREDITCARDTOKEN
                from dbo.REVENUE
                inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
                where REVENUE.ID = @ID;
            end

            --make sure next transaction date is properly calculated (based on existing payed previous transactions) WI#254736
            if @ISRECURRINGGIFT = 1
                exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2 @ID = @ID, @SCHEDULESEEDDATE = @INSTALLMENTSCHEDULESEEDDATE output;
        end

    --payment adjustment
    if @ISPAYMENT = 1 and @ISPOSTED = 1 and @ISMISCELLANEOUSPAYMENT = 0 and @ISORDERAPPLICATION = 0 and @HASGIFTAIDSPLITONPENDINGR68PROCESS = 0
    begin
        set @ISADJUSTMENT = 1;     
        set @POSTSTATUSCODE = null
    end;
    --pledge adjustment
    if @ISPLEDGE = 1 and @ISPOSTED = 1
    begin
        set @ISADJUSTMENT = 1;     
        set @POSTSTATUSCODE = null
    end;
    if @ISADJUSTMENT = 1
        begin
                select  
                @ADJUSTMENTDATE = getdate()

                select top 1
                    @ADJUSTMENTDATE = ADJUSTMENT.DATE,
                    @ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
                    @ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE,
                    @ADJUSTMENTREASON = ADJUSTMENT.REASON,
                    @ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID
                from dbo.ADJUSTMENT
                where ADJUSTMENT.REVENUEID = @ID
                order by dateadded 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

                --if the most recent adjustment has been posted then in essence we have no adjustment so clear out the adjustment fields
                if @ADJUSTMENTPOSTSTATUSCODE = 0
                begin
                    select @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                        @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                        @ADJUSTMENTPOSTSTATUSCODE = 1,
                        @ADJUSTMENTREASON = '',
                        @ADJUSTMENTREASONCODEID = null
                end
        end

  --set BENEFITSWAIVED
  select @BENEFITSWAIVED = BENEFITSWAIVED from REVENUE_EXT where ID = @ID

     --load the benefits 
     set @BENEFITS = 
        (
             select '00000000-0000-0000-0000-000000000000' as ID,
                            BENEFITID, 
                            QUANTITY,
                            UNITVALUE, 
                            TOTALVALUE, 
                            DETAILS,
                            SEQUENCE,
                            ID as REVENUEBENEFITID,
                            BASECURRENCYID as BENEFITCURRENCYID,
                            TRANSACTIONCURRENCYID,
                            TRANSACTIONTOTALVALUE,
                            BASECURRENCYID
             from UFN_REVENUE_GETBENEFITS_3(@ID) B
             where B.USEPERCENT = 0
             for xml raw('ITEM'), type, elements, root('BENEFITS'), binary base64
        );


    set @PERCENTAGEBENEFITS = 
        (
             select '00000000-0000-0000-0000-000000000000' as ID,
                            BENEFITID, 
                            PERCENTAPPLICABLEAMOUNT,
                            VALUEPERCENT, 
                            TOTALVALUE, 
                            DETAILS,
                            SEQUENCE,
                            ID as REVENUEBENEFITID,
                            TRANSACTIONCURRENCYID,
                            TRANSACTIONTOTALVALUE,
                            BASECURRENCYID
             from UFN_REVENUE_GETBENEFITS_3(@ID) B
             where B.USEPERCENT = 1
             for xml raw('ITEM'), type, elements, root('PERCENTAGEBENEFITS'), binary base64
        );


 set @NOTES = 
    (
             select '00000000-0000-0000-0000-000000000000' as ID,
                            REVENUENOTETYPECODEID as NOTETYPECODEID, 
                            TITLE AS NOTETITLE,
                            DATEENTERED AS NOTEDATEENTERED, 
                            AUTHORID AS NOTEAUTHORID, 
                            TEXTNOTE AS NOTETEXTNOTE,
                            ID as REVENUENOTEID,
                            HTMLNOTE AS NOTEHTMLNOTE
             from REVENUENOTE RN
             where RN.REVENUEID = @ID
             for xml raw('ITEM'), type, elements, root('NOTES'), binary base64
        );

        --INSTALLMENTS--
        --Find the first and last installment that have a full balance/no payments
        declare @LASTUNPAIDINSTALLMENTSEQUENCE integer;
        declare @FIRSTUNPAIDINSTALLMENTSEQUENCE integer;
        declare @MAXINSTALLMENTSEQUENCE integer;
        set @MAXINSTALLMENTSEQUENCE = @NUMBEROFINSTALLMENTS
        select
                @FIRSTUNPAIDINSTALLMENTSEQUENCE = min(SEQUENCE),
                @LASTUNPAIDINSTALLMENTSEQUENCE = max(SEQUENCE)
        from dbo.INSTALLMENT
    left join dbo.INSTALLMENTPAYMENT on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
    left join dbo.INSTALLMENTWRITEOFF on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
        where
                INSTALLMENT.REVENUEID = @ID and
        INSTALLMENTPAYMENT.INSTALLMENTID is null and
        INSTALLMENTWRITEOFF.INSTALLMENTID is null;

        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  @INSTALLMENTAMOUNTFORWEBSHELL  =  @INSTALLMENTAMOUNT;
        /*
        select
                @RESCHEDULEBALANCE = sum(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID))
        from dbo.INSTALLMENT
        where
                INSTALLMENT.REVENUEID = @ID;
         */

        if @FIRSTUNPAIDINSTALLMENTSEQUENCE is not null
        begin
        set @LASTPAYMENTSEQUENCE = @FIRSTUNPAIDINSTALLMENTSEQUENCE - 1;              
        -- set @NUMBEROFINSTALLMENTS to the number of unpaid installments

        --Bug 131198 - AdamBu - Fixed copied from Edit (Bug 120271).
                --set @NUMBEROFINSTALLMENTS = (@LASTUNPAIDINSTALLMENTSEQUENCE - @FIRSTUNPAIDINSTALLMENTSEQUENCE) + 1;
        end
        else
        begin
                --all installments have a payment towards them
                set @LASTPAYMENTSEQUENCE = (@MAXINSTALLMENTSEQUENCE);

        --Bug 131198 - AdamBu - Fixed copied from Edit (Bug 120271).
                --set @NUMBEROFINSTALLMENTS = 0;
        end
        --If there have been no payments towards the balance, then the @MAXINSTALLSEQUENCE
        --and the @LASTUNPAIDINSTALLMENTSEQUENCE will be equal.  If they are equal,
        --we do not care about the last unpaid row.  Set it to 0 as it can never be 0 on its own.
        --Otherwise, set it to the sequence number of the last installment that has no payments
        --applied to it.
        if @LASTUNPAIDINSTALLMENTSEQUENCE = @MAXINSTALLMENTSEQUENCE
                set @LASTUNPAIDROW = 0;
        else
                set @LASTUNPAIDROW = @LASTUNPAIDINSTALLMENTSEQUENCE;

        --set @INSTALLMENTS = dbo.UFN_REVENUEUPDATEBATCH_LOADINSTALLMENTS_TOITEMLISTXML(@ID)

    --RUB WebShell uses different logic to calculate installments and, as a result, needs the number of installments to be calculated differently.
        select
        @NUMBEROFINSTALLMENTSFORWEBSHELL = count(INSTALLMENT.ID)
        from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@ID) INSTALLMENT
        where INSTALLMENT.BALANCE > 0;

        set @HASPOSTEDPAYMENTS = dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@ID);

    select @DEPOSITID = DEPOSITID
    from dbo.BANKACCOUNTDEPOSITPAYMENT
    where ID = @ID;

    --JamesWill WI192027 2012-02-02 Default the adjustment post date to the deposit's post date if available        
    if @ISADJUSTMENT = 1
    begin
            if not @DEPOSITID is null
            begin
                select @ADJUSTMENTPOSTDATE = POSTDATE 
                from dbo.BANKACCOUNTDEPOSITPAYMENT 
                inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
                inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
                where BANKACCOUNTDEPOSITPAYMENT.ID = @ID;
            end
            else if @ADJUSTMENTPOSTDATE is null
            begin
                select @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
            end
    end

    --JamesWill WI192027 2012-02-02 If there's still not an adjustment post date, default it to the record's post date
    if @ADJUSTMENTPOSTDATE is null           
        set @ADJUSTMENTPOSTDATE = @POSTDATE; 

    if @ADJUSTMENTPOSTDATE is null           
            select @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    if @ISPLEDGE = 1 and @HASPOSTEDPAYMENTS = 1
        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;