USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPAGEDATA

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @REVENUEID uniqueidentifier = null output,
    @AMOUNT money = null output,
    @DATE datetime = null output,
    @CONSTITUENTNAME nvarchar(255) = null output,
    @ISGIFT bit = null output,
    @ISPLEDGE bit = null output,
    @ISRECURRINGGIFT bit = null output,
    @ISMGPLEDGE bit = null output,
    @ISPLANNEDGIFT bit = null output,
    @ISPAYMENT bit = null output,
    @ISPOSTED bit = null output,
    @LINKEDRECORDID uniqueidentifier = null output,
    @ALLOWEDITPAYMENT bit = null output,
    @BENEFITSWAIVED bit = null output,
    @CANROLLBACK bit = null output,
    @CANSKIPTRANSACTION bit = null output,                    
    @ISPENDING bit = null output,
    @EXTRADATAISPOSTED bit = null output,
    @HASWRITEOFF bit = null output,
    @HASADJUSTMENTS bit = null output,
    @HASUNPOSTEDREVERSAL bit = null output,
    @HASUNPOSTEDADJUSTMENT bit = null output,
    @HASUNPOSTEDEXTRADATA bit = null output,
    @TYPE nvarchar(256) = null output,
    @PLEDGEBALANCE money = null output,
    @PAYMENTTYPECODE tinyint = null output,
    @ATTRIBUTEDEFINED bit = null output,
    @REVENUECOUNT int = null output,
    @ALLOWMATCHINGGIFTCLAIM bit = null output,
    @HASTRIBUTES bit = null output,
    @CANAPPLYTRIBUTE bit = null output,
    @PAYMENTINCLUDESGIFT bit = null output,
    @RECEIPTSTATUSCODE tinyint = null output,
    @MGPLEDGEISINACTIVE bit = null output,
    @ISCONSTITUENTHOUSEHOLD bit = null output,
    @HOUSEHOLDSCANBEDONORS bit = null output,
    @NEEDSRERECEIPT bit = null output,
    @RECEIPTCOUNT int = null output,
    @PLANNEDGIFTID uniqueidentifier = null output,    
    @REVENUEPAYMENTMETHODID uniqueidentifier = null output,
    @DONOTPOST bit = null output,
    @SALEPOSTSTATUSCODE tinyint = null output,
    @HASSOLDSTOCK bit = null output,
    @GIFTFEE_ENABLED bit = null output,
    @HASSOLDPROPERTY bit = null output,
    @ELIGIBLEFORMATCHINGGIFTCLAIM bit = null output,
    @ISORDER bit = null output,
    @SALESORDERID uniqueidentifier = null output,
    @ISORDERAPPLICATION bit = null output,
    @HASGIFTAIDGLDISTRIBUTIONS bit = null output,
    @SALESORDERPAYMENTID uniqueidentifier = null output,
    @ORDERTICKETTOTAL money = null output,
    @ORDERMEMBERSHIPTOTAL money = null output,
    @ORDERDONATIONTOTAL money = null output,
    @ORDERADJUSTMENTTOTAL money = null output,
    @ORDERHASMEMBERSHIP bit = null output,
    @ORDERHASDONATION bit = null output,
    @ORDERHASADJUSTMENT bit = null output,
    @ISMISCELLANEOUSPAYMENT bit = null output,
    @REFERENCE nvarchar(255) = null output,
    @REFUNDSTATUS tinyint = null output,
    @REFUNDTOTAL money = null output,
    @ORDERHASEVENTREGISTRATION bit = null output,
    @ORDEREVENTREGISTRATIONTOTAL money = null output,
    @ISGRANTAWARD bit = null output,
    @RGISOPEN bit = null output,
    @SPONSORSHIPID uniqueidentifier = null output,
    @SPONSORSHIPOPPORTUNITY nvarchar(200) = null output,
    @ORDERHASRESOURCE bit = null output,
    @ORDERRESOURCETOTAL money = null output,
    @ORDERHASSTAFFRESOURCE bit = null output,
    @ORDERSTAFFRESOURCETOTAL money = null output,
    @CONSTITUENTISGRANTOR bit = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @HASPLANNEDGIFTPAYOUT bit = null output,
    @RGHASDEVELOPMENTHISTORY bit = null output,
    @HASBENEFITDISTRIBUTION bit = null output,
    @HASGLDISTRIBUTIONS bit = null output,
    @HASDNPADJUSTMENT bit = null output,
    @ISMIXEDORDERPAYMENT bit = null output,
    @HASGIFTAIDSPLITONPENDINGR68PROCESS bit = null output,
    @HASPOSTEDBENEFITS bit = null output,
    @ISAUCTIONDONATION bit = null output,
    @PAYMENTALLOWSCREDITCARDREFUND bit = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @SHOWGLINFO bit = null output,
    @AUCTIONITEMID uniqueidentifier = null output,
    @HASAUCTIONPURCHASE bit = null output,
    @ISDONORCHALLENGE bit = null output,
    @AUCTIONDONATIONISRESERVEDORSOLD bit = null output,
    @HASGIFTFEEGLDISTRIBUTIONS bit = null output,
    @HASPOSTEDGIFTFEES bit = null output,
    @HASUNPOSTEDGIFTFEESADJUSTMENT bit = null output,
    @SHOWREVENUEGLDISTRIBUTIONS bit = null output,
    @ORDERHASFACILITY bit = null output,
    @ORDERFACILITYTOTAL money = null output,
    @ORDERHASMERCHANDISE bit = null output,
    @ORDERMERCHANDISETOTAL money = null output,
    @HASUNREALIZEDGAINLOSS bit = null output,
    @HASSYSTEMDISTRIBUTIONS bit = null output,
    @ISPLANNEDGIFTADDITION bit = null output,
    @PLANNEDGIFTADDITIONID uniqueidentifier = null output,
    @HASMATCHINGACCOUNTSYSTEM bit = null output,
    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
    @HASEVENTREGISTRATIONDESIGNATIONAPPLICATION bit = null output,
    @HASDONOTPOSTADJUSTMENT bit = null output,
    @ISPENDINGGIFT bit = null output,
    @ISPOSTABLE bit = null output,
    @ISGIFTTHROUGHFAFEVENT bit = null output,
    @EVENTID uniqueidentifier = null output,
    @EVENTNAME nvarchar(200) = null output,
    @ISSECURITYDEPOSITPAYMENT bit = null output,
    @HASBENEFITLIABILITYDISTRIBUTION bit = null output,
    @HASBENEFITBACKOUTDISTRIBUTION bit = null output,
    @HASBENEFITDISTRIBUTIONADJUSTMENT bit = null output,
    @HASBENEFITLIABILITYDISTRIBUTIONADJUSTMENT bit = null output,
    @HASBENEFITBACKOUTDISTRIBUTIONADJUSTMENT bit = null output,
    @HASBENEFITUNPOSTEDBACKOUTDISTRIBUTION bit = null output,
    @SHOWLINKDEPOSIT bit = null output,
    @ALLOWGLDISTRIBUTIONS bit = null output,
    @HASSOLDGIFTINKIND bit = null output,
    @ISFORSALE bit = null output,
    @ISSPONSORSHIPRECURRINGADDITIONALGIFT bit = null output,
    @GIFTINKINDAPPRAISALREQUIRED bit = null output,
    @ISMEMBERSHIPPLEDGE bit = null output,
    @HASUNPOSTEDBENEFITADJUSTMENT bit = null output,
    @RECURRINGGIFTHASCONTRIBUTEDPORTION bit = null output,
    @ISINDIVIDUAL bit = null output,
    @SHOWGIFTAIDREFUNDS bit = null output,
    @HASUNPOSTEDPAYOUTADJUSTMENT bit = null output,    
    @HASDESIGNATIONS bit = null output,
    @HASMATCHINGGIFTCLAIMAPPLICATION bit = null output,
    @REVENUEIDHASREGISTRATION bit = null output,
    @SALESMETHODTYPECODE tinyint = null output,
    @PASTDUEBYDATE money = null output,
    @HASLINKEDOPPORTUNITY bit = null output,
    @HASREFUND bit = null output
)
as
    set nocount on;
    set @DATALOADED = 0;
    set @REVENUEIDHASREGISTRATION = 0;
    declare @POSTED bit;
    declare @DONOTRECEIPT bit;
    declare @CURRENTDATE datetime = getdate();

    select  
        @ISPAYMENT = 0,
        @ISGIFT = 0,
        @ISPLEDGE = 0,
        @ISRECURRINGGIFT = 0,
        @ISMGPLEDGE = 0,
        @ISAUCTIONDONATION = 0,
        @ISPLANNEDGIFT = 0,
        @CANROLLBACK = 0,
        @CANSKIPTRANSACTION = 0,
        @EXTRADATAISPOSTED = 0,
        @ISPENDING = 0,
        @HASWRITEOFF = 0,
        @HASUNPOSTEDREVERSAL = 0,
        @HASUNPOSTEDADJUSTMENT = 0,
        @HASUNPOSTEDBENEFITADJUSTMENT = 0,
        @HASUNPOSTEDEXTRADATA = 0,
        @HASTRIBUTES = 0,
        @CANAPPLYTRIBUTE = 0,
        @PAYMENTINCLUDESGIFT = 0,
        @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('REVENUE',@CURRENTAPPUSERID),
        @ALLOWMATCHINGGIFTCLAIM = 
            case when exists 
            (
                select 
                    LI.ID 
                from 
                    dbo.FINANCIALTRANSACTIONLINEITEM LI 
                inner join 
                    dbo.REVENUESPLIT_EXT X on X.ID = LI.ID 
                where 
                    LI.FINANCIALTRANSACTIONID = @ID 
                    and LI.DELETEDON is null 
                    and LI.TYPECODE != 1
                ) 
                then 
                    1 
         else 
                    0 
        end,
        @MGPLEDGEISINACTIVE = 0,
        @GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED(),
        @ISORDER = 0,
        @ISORDERAPPLICATION = 0,
        @HASGIFTAIDGLDISTRIBUTIONS = 0,
        @ISMISCELLANEOUSPAYMENT = 0,
        @ISGRANTAWARD = 0,
        @RGISOPEN = 0,
        @CONSTITUENTISGRANTOR = 0,
        @RGHASDEVELOPMENTHISTORY = 0,
        @HASGLDISTRIBUTIONS = 0,
        @HASDNPADJUSTMENT = 0,
        @ISMIXEDORDERPAYMENT = 0,
        @PAYMENTALLOWSCREDITCARDREFUND = 0,
        @HASAUCTIONPURCHASE = 0,
        @ISDONORCHALLENGE = 0,
        @AUCTIONDONATIONISRESERVEDORSOLD = 0,
        @HASGIFTFEEGLDISTRIBUTIONS = 0,
        @HASPOSTEDGIFTFEES = 0,
        @HASUNPOSTEDGIFTFEESADJUSTMENT = 0,
        @SHOWREVENUEGLDISTRIBUTIONS = 1,
        @HASEVENTREGISTRATIONDESIGNATIONAPPLICATION = 0,
        @HASMATCHINGGIFTCLAIMAPPLICATION = 0,
        @HASDESIGNATIONS = 0,
        @HASDONOTPOSTADJUSTMENT = 0,
        @ISPENDINGGIFT = 0,
        @ISGIFTTHROUGHFAFEVENT = 0,
        @EVENTID = null,
        @EVENTNAME = '',
        @SHOWLINKDEPOSIT = 0,
        @ISSPONSORSHIPRECURRINGADDITIONALGIFT = 0,
        @RECURRINGGIFTHASCONTRIBUTEDPORTION = 0,
        @ISINDIVIDUAL = 0,
        @SHOWGIFTAIDREFUNDS = 0,
        @HASUNPOSTEDPAYOUTADJUSTMENT = 0,
        @REVENUEIDHASREGISTRATION = 0,
        @HASREFUND = 0;


    declare @GIFTINKINDORGANIZATIONFAIRMARKETVALUE money;
    declare @BASEAMOUNT money;

    select top 1
        @DATALOADED = 1,
        @REVENUEID = REVENUE.ID,
        @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
        @CONSTITUENTNAME = NF.NAME,
        @ISPAYMENT = case when FINANCIALTRANSACTION.TYPECODE = 0 then 1 else 0 end,
        @ISGIFT = case when FINANCIALTRANSACTION.TYPECODE = 0 then 1 else 0 end,
        @ISPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 1 then 1 else 0 end,
        @ISRECURRINGGIFT = case when FINANCIALTRANSACTION.TYPECODE = 2 then 1 else 0 end,
        @ISMGPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 3 then 1 else 0 end,
        @ISPLANNEDGIFT = case when FINANCIALTRANSACTION.TYPECODE = 4 then 1 else 0 end,
        @ISORDER = case when FINANCIALTRANSACTION.TYPECODE = 5 then 1 else 0 end,
        @ISAUCTIONDONATION = case when FINANCIALTRANSACTION.TYPECODE = 7 then 1 else 0 end,
        @ISDONORCHALLENGE = case when FINANCIALTRANSACTION.TYPECODE = 8 then 1 else 0 end,
        @HASUNPOSTEDADJUSTMENT = case when ADJUSTMENT.POSTSTATUSCODE = 1 then 1 else 0 end,
        @HASUNPOSTEDBENEFITADJUSTMENT = case when BENEFITADJUSTMENT.POSTSTATUSCODE = 1 then 1 else 0 end,
        @ISPOSTED = case when FINANCIALTRANSACTION.POSTSTATUSCODE != 2 then 0 else 1 end,
        @DATE = cast(FINANCIALTRANSACTION.DATE as datetime),
        @TYPE = FINANCIALTRANSACTION.TYPE,
        @PLEDGEBALANCE = 
            case when FINANCIALTRANSACTION.TYPECODE = 1 or FINANCIALTRANSACTION.TYPECODE = 3 or FINANCIALTRANSACTION.TYPECODE = 6 or FINANCIALTRANSACTION.TYPECODE = 7 or FINANCIALTRANSACTION.TYPECODE = 9 or FINANCIALTRANSACTION.TYPECODE = 15 
                then 
                    dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) 
                else 
                    0 
        end,
        @PAYMENTTYPECODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
        @DONOTRECEIPT = REVENUE.DONOTRECEIPT,
        @ISCONSTITUENTHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(FINANCIALTRANSACTION.CONSTITUENTID),
        @NEEDSRERECEIPT = REVENUE.NEEDSRERECEIPT,
        @REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID,
        @DONOTPOST = case FINANCIALTRANSACTION.POSTSTATUSCODE when 3 then 1 else 0 end,
        @ELIGIBLEFORMATCHINGGIFTCLAIM = REVENUE.ELIGIBLEFORMATCHINGGIFTCLAIM,
        @REFERENCE = REVENUE.REFERENCE,
        @ISGRANTAWARD = case when FINANCIALTRANSACTION.TYPECODE = 6 then 1 else 0 end,
        @TRANSACTIONCURRENCYID = coalesce(FINANCIALTRANSACTION.TRANSACTIONCURRENCYID, '00000000-0000-0000-0000-000000000000'),
        @ISDONORCHALLENGE = case when FINANCIALTRANSACTION.TYPECODE = 8 then 1 else 0 end,
        @PDACCOUNTSYSTEMID = case when FINANCIALTRANSACTION.TYPECODE in (0,1,4,5,6,7) then FINANCIALTRANSACTION.PDACCOUNTSYSTEMID else null end,
        @ISPENDINGGIFT = case when FINANCIALTRANSACTION.TYPECODE = 9 then 1 else 0 end,
        @ISPOSTABLE = case when FINANCIALTRANSACTION.TYPECODE in (2, 3, 8) then 0 else 1 end,
        @GIFTINKINDORGANIZATIONFAIRMARKETVALUE = GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE,
        @ISMEMBERSHIPPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 15 then 1 else 0 end,
        @AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
        @BASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT,
        @ISINDIVIDUAL = case when CONSTITUENT.ISORGANIZATION = 0 and CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISCONSTITUENT = 1 then 1 else 0 end
    from 
        dbo.FINANCIALTRANSACTION
    inner join 
        dbo.REVENUE_EXT REVENUE on FINANCIALTRANSACTION.ID = REVENUE.ID
    inner join 
        dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
    left join 
        dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
    left join 
        dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
    left join 
        dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left join 
        dbo.BENEFITADJUSTMENT on REVENUE.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE = 1
    outer apply 
        dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    where 
        REVENUE.ID = @ID 
        and FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) 
        and FINANCIALTRANSACTION.DELETEDON is null

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

    select
        @REVENUECOUNT = count(1)
    from
        dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join
        dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
    where
        FTLI.FINANCIALTRANSACTIONID = @ID and
        FTLI.DELETEDON is null and
        FTLI.TYPECODE <> 1;

    select 
        @ISGIFTTHROUGHFAFEVENT = 1,
        @EVENTID = E.ID,
        @EVENTNAME = E.NAME
    from 
        dbo.FINANCIALTRANSACTION FT
    inner join 
        dbo.REVENUE_EXT R on R.ID = FT.ID
    inner join 
        dbo.EVENT E on R.APPEALID = E.APPEALID
    inner join 
        dbo.EVENTEXTENSION EX on E.ID = EX.EVENTID
    where 
        R.ID = @ID 
        and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) 
        and FT.DELETEDON is null

    set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

    select top 1
        @HASDNPADJUSTMENT = (case when POSTSTATUSCODE = 2 then 1 else 0 end)
    from 
        dbo.UFN_REVENUE_GETADJUSTMENTSTATUS(@REVENUEID)
    order by 
        DATEADDED desc

    -- Check if any of the stock has been sold

    set @HASSOLDSTOCK = 0
    if @PAYMENTTYPECODE = 4 -- Stock payment method

    begin
        if exists 
        (    
            select 
                STOCKDETAIL.ID 
            from 
                dbo.STOCKDETAIL 
            inner join 
                dbo.STOCKSALE_EXT STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
            inner join
                dbo.FINANCIALTRANSACTION FT on STOCKSALE.ID = FT.ID
            where 
                STOCKDETAIL.ID = @REVENUEPAYMENTMETHODID
            and
                FT.DELETEDON is null
        )
            set @HASSOLDSTOCK = 1
    end

    -- Check if the property has sold

    set @HASSOLDPROPERTY = 0
    if @PAYMENTTYPECODE = 5 -- Property payment method

    begin
        if exists 
        (
            select 
                PROPERTYDETAIL.ID 
            from 
                dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL 
            where 
                ID = @REVENUEPAYMENTMETHODID 
                and SALEDATE is not null
        )
            set @HASSOLDPROPERTY = 1
    end

    -- Check if any of the gift-in-kind has been sold

    set @HASSOLDGIFTINKIND = 0
    if @PAYMENTTYPECODE = 6 -- Gift-in-kind payment method

    begin
        if exists 
        (    
            select 
                GIFTINKINDPAYMENTMETHODDETAIL.ID 
            from 
                dbo.GIFTINKINDPAYMENTMETHODDETAIL 
            inner join 
                dbo.GIFTINKINDSALE_EXT GIFTINKINDSALE on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
            inner join
                dbo.FINANCIALTRANSACTION FT on GIFTINKINDSALE.ID = FT.ID
            where 
                GIFTINKINDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID
            and
                FT.DELETEDON is null
        )
            set @HASSOLDGIFTINKIND = 1
    end

    --if Revenue is of type Sold Stock or Sold Payment and is Posted, we won't allow the user to edit the payment

    if @PAYMENTTYPECODE = 4 and exists 
        (    
            select 
                STOCKSALE.ID 
            from 
                dbo.STOCKSALE_EXT STOCKSALE 
            inner join 
                dbo.FINANCIALTRANSACTION FT on FT.ID = STOCKSALE.ID
            where 
                STOCKDETAILID = @REVENUEPAYMENTMETHODID 
                and FT.POSTSTATUSCODE = 2 
                and FT.DELETEDON is null 
                and FT.TYPECODE = 21
        )
    begin
        set @ALLOWEDITPAYMENT = 0;
        set @EXTRADATAISPOSTED = 1;
        if exists 
        (
            select 
                STOCKSALEADJUSTMENT.ID 
            from 
                dbo.STOCKSALEADJUSTMENT 
            inner join 
                dbo.STOCKSALE_EXT STOCKSALE on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID 
            where 
                STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID 
                and STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1
        )
            set @HASUNPOSTEDEXTRADATA = 1;
    end

    set @ISFORSALE = 0;
    set @GIFTINKINDAPPRAISALREQUIRED = 0;

    if @PAYMENTTYPECODE = 6 -- Gift-in-kind payment method

    begin
      --if Revenue is of type Gift-in-Kind and is Posted, we won't allow the user to edit the payment

        if exists 
        ( 
            select 
                GIFTINKINDSALE.ID 
            from 
                dbo.GIFTINKINDSALE_EXT GIFTINKINDSALE 
            inner join 
                dbo.FINANCIALTRANSACTION FT on FT.ID = GIFTINKINDSALE.ID
            where 
                GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and FT.POSTSTATUSCODE = 2 and FT.DELETEDON is null and FT.TYPECODE = 27
        )
        begin
            set @ALLOWEDITPAYMENT = 0;
            set @EXTRADATAISPOSTED = 1;
        end

        -- check that Gift-in-kind is for sale


        if exists 
            (
                select 
                    DISPOSITIONCODE 
                from 
                    dbo.GIFTINKINDPAYMENTMETHODDETAIL 
                where 
                    ID = @REVENUEPAYMENTMETHODID and DISPOSITIONCODE = 1
            )
        begin
            set @ISFORSALE = 1;

            declare @USEAPPRAISALTHRESHOLD bit;
            declare @APPRAISALTHRESHOLD money;

            select top 1
                @USEAPPRAISALTHRESHOLD = USEAPPRAISALTHRESHOLD,
                @APPRAISALTHRESHOLD = APPRAISALTHRESHOLD
            from
                dbo.GIFTINKINDAPPRAISALSETTINGS;

            if ((@USEAPPRAISALTHRESHOLD = 1) and 
                (@GIFTINKINDORGANIZATIONFAIRMARKETVALUE > @APPRAISALTHRESHOLD) and 
                not exists 
                (
                    select top(1) 1 
                    from 
                        dbo.GIFTINKINDAPPRAISAL 
          where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID
                )
            )
                set @GIFTINKINDAPPRAISALREQUIRED = 1;
        end
    end

    if @PAYMENTTYPECODE = 5 and @HASSOLDPROPERTY = 1
        select 
            @SALEPOSTSTATUSCODE = isnull(cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) 
        from 
            dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL 
        inner join 
            dbo.FINANCIALTRANSACTION FT on FT.ID = PROPERTYDETAIL.ID 
        where 
            PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID 
            and FT.DELETEDON is null 
            and FT.TYPECODE = 22;

    if @SALEPOSTSTATUSCODE = 0
    begin
        set @ALLOWEDITPAYMENT = 0;

        set @EXTRADATAISPOSTED = 1;

        if exists 
        (
            select 
                1 
            from 
                dbo.PROPERTYDETAILADJUSTMENT 
            inner join 
                dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID
            where 
                PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID
                and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1
        )
            set @HASUNPOSTEDEXTRADATA = 1;

        if 
        (    
            select 
                top 1 POSTSTATUSCODE
            from 
                dbo.PROPERTYDETAILADJUSTMENT
            inner join 
                dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID
            where 
                PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID
            order by 
                PROPERTYDETAILADJUSTMENT.DATEADDED desc
        ) = 2
            set @HASDONOTPOSTADJUSTMENT = 1;
    end

    if @ALLOWEDITPAYMENT = 1
    begin
        if @PAYMENTTYPECODE = 4 and @HASSOLDSTOCK = 1
            if 
            (
                select 
                    isnull(cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) 
                from 
                    dbo.STOCKSALE_EXT STOCKSALE
                inner join 
                    dbo.FINANCIALTRANSACTION FT on FT.ID = STOCKSALE.ID
                where 
                    STOCKDETAILID = @REVENUEPAYMENTMETHODID and FT.TYPECODE = 21 and FT.DELETEDON is null
            ) = 0 
            begin
                set @ALLOWEDITPAYMENT = 0;
                set @EXTRADATAISPOSTED = 1;

                if exists 
                (
                    select 
                        * 
                    from 
                        dbo.STOCKSALEADJUSTMENT 
                    inner join 
                        dbo.STOCKSALE_EXT STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID and STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1
                    inner join 
                        dbo.STOCKDETAIL on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
                    where STOCKDETAIL.ID = @REVENUEPAYMENTMETHODID
                )
                    set @HASUNPOSTEDEXTRADATA = 1;
                else
                    set @HASUNPOSTEDEXTRADATA = 0;                                
            end
        else
            set @EXTRADATAISPOSTED = 0;

        if @PAYMENTTYPECODE = 5 and @HASSOLDPROPERTY = 1
            if 
            (
                select 
                    isnull(cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) 
                from 
                    dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL 
                inner join 
                    dbo.FINANCIALTRANSACTION FT on FT.ID = PROPERTYDETAIL.ID 
                where 
                    PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID 
                    and FT.DELETEDON is null 
                    and FT.TYPECODE = 22
            ) = 0
            begin
  set @ALLOWEDITPAYMENT = 0;
                set @EXTRADATAISPOSTED = 1;
                if exists 
                (
                    select 
                        * 
                    from 
                        dbo.PROPERTYDETAILADJUSTMENT 
                    inner join 
                        dbo.PROPERTYDETAIL_EXT PROPERTYDETAIL on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1
                    where 
                        PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID
                )
                    set @HASUNPOSTEDEXTRADATA = 1;
            else
                set @HASUNPOSTEDEXTRADATA = 0;                            
            end
        else
            set @EXTRADATAISPOSTED = 0;

        if @PAYMENTTYPECODE = 6 and @HASSOLDGIFTINKIND = 1
            if 
            (
                select 
                    isnull(cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) 
                from 
                    dbo.GIFTINKINDSALE_EXT GIFTINKINDSALE
                inner join 
                    dbo.FINANCIALTRANSACTION FT on FT.ID = GIFTINKINDSALE.ID
                where 
                    GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and FT.DELETEDON is null and FT.TYPECODE = 27
            ) = 0 
            begin
                set @ALLOWEDITPAYMENT = 0;
                set @EXTRADATAISPOSTED = 1;
            end
            else
                set @EXTRADATAISPOSTED = 0;

    end

    if @ALLOWEDITPAYMENT = 1 
        if @POSTED = 1 
            set @ALLOWEDITPAYMENT = 0;                


    if @ISRECURRINGGIFT = 1
    begin
        select 
            @RGISOPEN = case when REVENUESCHEDULE.STATUSCODE in (0, 1, 5) then 1 else 0 end -- included the 'held' and 'lapsed' statuses in the definition of open.

        from 
            dbo.REVENUESCHEDULE
        where 
            REVENUESCHEDULE.ID = @REVENUEID;

        select
            @ISPENDING = REVENUESCHEDULE.ISPENDING,
            @BENEFITSWAIVED = REVENUE.BENEFITSWAIVED                        
        from 
            dbo.REVENUE_EXT REVENUE
        inner join 
            dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        where 
            REVENUE.ID = @REVENUEID;

        select 
            @SPONSORSHIPID = SPONSORSHIP.ID,
            @SPONSORSHIPOPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),
            @TYPE = REVENUESPLIT.TYPE + ' ' + lower(@TYPE)
        from 
            dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join 
            dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
        inner join 
            dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
        where 
            LI.FINANCIALTRANSACTIONID = @REVENUEID
            and REVENUESPLIT.TYPECODE = 9
            and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1
            and LI.DELETEDON is null and LI.TYPECODE != 1;

        if exists
        (
            select top 1 1
            from 
                dbo.RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY
            where REVENUEID = @REVENUEID
        )
            set @RGHASDEVELOPMENTHISTORY = 1;

        if exists
        (
            select top 1 1
            from 
                dbo.SPONSORSHIPRECURRINGADDITIONALGIFT
            where 
                REVENUEID = @REVENUEID
        )
            set @TYPE = 'Sponsorship recurring additional gift';

        if exists
        (
            select 
                top 1 1
            from 
                dbo.FINANCIALTRANSACTIONLINEITEM
            inner join 
                dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            where 
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and REVENUESPLIT_EXT.TYPECODE = 0
        )
            set @RECURRINGGIFTHASCONTRIBUTEDPORTION = 1

        select @PASTDUEBYDATE = AMOUNT
        from dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNT_BULK_2(@CURRENTDATE,1)
        where ID = @REVENUEID;

    end

    select 
        @HASADJUSTMENTS = case when count(*) > 0 then 1 else 0 end
    from 
    (
        select 
            ID 
        from 
            dbo.ADJUSTMENT 
        where REVENUEID = @ID
        union all
        select 
            STOCKSALEADJUSTMENT.ID 
        from 
            dbo.STOCKSALEADJUSTMENT 
        inner join 
            dbo.STOCKSALE_EXT STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
        where 
            STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID
        union all
        select 
            ID 
        from 
            dbo.PROPERTYDETAILADJUSTMENT 
        where 
            PROPERTYDETAILID = @REVENUEPAYMENTMETHODID
    ) as A;

    --ADDTRANSACTIONID


    if exists(select 1 from dbo.JOURNALENTRY_EXT where JOURNALENTRY_EXT.TABLENAMECODE = 7 and LOGICALREVENUEID = @ID)
        set @HASGIFTAIDGLDISTRIBUTIONS = 1;

    set @HASUNPOSTEDREVERSAL = dbo.UFN_HASUNPOSTEDREVERSAL(@ID);

    if @ISPLEDGE = 1 or @ISGRANTAWARD = 1 or @ISAUCTIONDONATION = 1
    begin
        select 
            @HASWRITEOFF = case when count(*) > 0 then 1 else 0 end 
        from 
            dbo.FINANCIALTRANSACTION 
        inner join 
            dbo.WRITEOFF_EXT on FINANCIALTRANSACTION.ID = WRITEOFF_EXT.ID
        where 
            TYPECODE = 20 
            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTION.PARENTID = @ID
            and isnull(FINANCIALTRANSACTION.POSTSTATUSCODE, 3) != 3;

    end

    if (@ISPLEDGE = 1 or @ISPLANNEDGIFT = 1 or @ISGRANTAWARD = 1)
        and exists
        (
            select 1
            from 
                dbo.JOURNALENTRY_EXT EXT
            inner join 
                dbo.JOURNALENTRY JE on JE.ID = EXT.ID
            inner join 
                dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
            where 
                EXT.TABLENAMECODE = 14
                and LI.FINANCIALTRANSACTIONID = @ID
                and EXT.OUTDATED = 0
        )
    begin
        set @HASUNREALIZEDGAINLOSS = 1
    end
    else
    begin
        set @HASUNREALIZEDGAINLOSS = 0
    end

    if @ISPLEDGE = 1 or @ISGRANTAWARD = 1 or @ISMEMBERSHIPPLEDGE = 1
    begin
        select
            @ISPENDING = REVENUESCHEDULE.ISPENDING,
            @BENEFITSWAIVED = REVENUE.BENEFITSWAIVED
        from 
            dbo.REVENUE_EXT REVENUE
        inner join 
            dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        where REVENUE.ID = @ID;
    end

    if @ISMGPLEDGE = 1
    begin
        select 
            @ISPENDING = REVENUESCHEDULE.ISPENDING,
            @LINKEDRECORDID = RMG.MGSOURCEREVENUEID,
            @BENEFITSWAIVED = REVENUE.BENEFITSWAIVED,
            @MGPLEDGEISINACTIVE = case when [RMG].ISACTIVE = 0 then 1 else 0 end
        from 
            dbo.REVENUE_EXT REVENUE
        inner join 
            dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
        inner join 
            dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        where 
            REVENUE.ID = @ID;
    end

    if exists 
    (
        select 
            REVENUETRIBUTE.ID 
        from 
            dbo.REVENUETRIBUTE 
        where
            REVENUETRIBUTE.REVENUEID = @ID
    )
        select @HASTRIBUTES = 1;

    if @ISPLEDGE = 1 
    begin                
        if (select coalesce(sum(RT.AMOUNT),0) from dbo.REVENUETRIBUTE RT where REVENUEID = @ID) < @BASEAMOUNT
            select @CANAPPLYTRIBUTE = 1;
    end
    else if @ISPAYMENT = 1 or @ISAUCTIONDONATION = 1
    begin
        set @PAYMENTINCLUDESGIFT = 1;
        if (select coalesce(sum(RT.AMOUNT),0) from dbo.REVENUETRIBUTE RT where REVENUEID = @ID) < @BASEAMOUNT
            set @CANAPPLYTRIBUTE = 1;

    end

    -- determine the receipt status                

    if @DONOTRECEIPT = 1
        set @RECEIPTSTATUSCODE = 1; -- do not receipt

    else
    begin
        declare @RECEIPTID uniqueidentifier;
        declare @RECEIPTDATE datetime;

        select 
            top 1 @RECEIPTID = RR.ID, 
            @RECEIPTDATE = RECEIPTDATE 
        from 
            dbo.REVENUERECEIPT RR 
        where 
            RR.REVENUEID = @REVENUEID 
        order by 
            RR.RECEIPTPROCESSDATE desc;

        if @RECEIPTID is null        
            set @RECEIPTSTATUSCODE = 3; -- not receipted

        else if @RECEIPTDATE is null
            set @RECEIPTSTATUSCODE = 2; -- pending

        else
            set @RECEIPTSTATUSCODE = 0; -- receipted

    end

    -- if re-receipts turned off

    if  ((coalesce((select top 1 RERECEIPTPAYMENTS from dbo.RECEIPTPREFERENCEINFO), 1)) = 0 And (coalesce((select top 1 SAMENUMBERONRERECEIPT from dbo.RECEIPTPREFERENCEINFO), 1)) =0)
        set @NEEDSRERECEIPT = null;

    select @RECEIPTCOUNT = count(ID) from dbo.REVENUERECEIPT where REVENUEID = @REVENUEID;

    if exists
    (
        select 
            JE.ID 
        from 
            dbo.JOURNALENTRY as JE
        inner join 
            dbo.JOURNALENTRY_EXT EXT on JE.ID = EXT.ID and EXT.TABLENAMECODE = 5
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join 
            dbo.REVENUEBENEFIT_EXT on LI.ID = REVENUEBENEFIT_EXT.ID
        where 
            EXT.BENEFITTYPECODE = 1 
            and LI.FINANCIALTRANSACTIONID = @REVENUEID 
            and LI.DELETEDON is null
    )
        set @HASBENEFITDISTRIBUTION = 1

    if exists
    (
        select 
            JE.ID 
        from 
            dbo.JOURNALENTRY as JE
        inner join 
            dbo.JOURNALENTRY_EXT EXT on JE.ID = EXT.ID and EXT.TABLENAMECODE = 5
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join 
            dbo.REVENUEBENEFIT_EXT on LI.ID = REVENUEBENEFIT_EXT.ID
        where 
            EXT.BENEFITTYPECODE = 2 
            and LI.FINANCIALTRANSACTIONID = @REVENUEID 
            and LI.DELETEDON is null
    )
        set @HASBENEFITLIABILITYDISTRIBUTION = 1                    

    if exists
    (    
        select 
            JE.ID 
        from 
            dbo.JOURNALENTRY as JE
        inner join 
            dbo.JOURNALENTRY_EXT EXT on JE.ID = EXT.ID and EXT.TABLENAMECODE = 5
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        inner join 
            dbo.REVENUEBENEFIT_EXT on LI.ID = REVENUEBENEFIT_EXT.ID
        where 
            EXT.BENEFITTYPECODE = 3 
            and LI.FINANCIALTRANSACTIONID = @REVENUEID 
            and LI.DELETEDON is null
    )
        set @HASBENEFITBACKOUTDISTRIBUTION = 1

    if exists
    (
        select 
            JE.ID 
        from 
            dbo.JOURNALENTRY as JE
        inner join 
            dbo.JOURNALENTRY_EXT EXT on JE.ID = EXT.ID and EXT.TABLENAMECODE = 5
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        left join 
            dbo.BENEFITADJUSTMENT on LI.FINANCIALTRANSACTIONID = BENEFITADJUSTMENT.REVENUEID and EXT.BENEFITTYPECODE = BENEFITADJUSTMENT.BENEFITTYPECODE 
        where 
            EXT.BENEFITTYPECODE = 3 
            and BENEFITADJUSTMENT.ID is null 
            and LI.POSTSTATUSCODE = 1 
            and LI.FINANCIALTRANSACTIONID = @REVENUEID 
            and LI.DELETEDON is null
    )
        set @HASBENEFITUNPOSTEDBACKOUTDISTRIBUTION = 1                    

    if exists(select 1 from dbo.BENEFITADJUSTMENT where BENEFITTYPECODE = 1 and REVENUEID = @REVENUEID)
        set @HASBENEFITDISTRIBUTIONADJUSTMENT = 1        

    if exists(select 1 from dbo.BENEFITADJUSTMENT where BENEFITTYPECODE = 2 and REVENUEID = @REVENUEID)                
        set @HASBENEFITLIABILITYDISTRIBUTIONADJUSTMENT = 1                    

    if exists(select 1 from dbo.BENEFITADJUSTMENT where BENEFITTYPECODE = 3 and REVENUEID = @REVENUEID)    
        set @HASBENEFITBACKOUTDISTRIBUTIONADJUSTMENT = 1    

    if @ISPLANNEDGIFT = 1 
    begin
        select
            @PLANNEDGIFTID = PLANNEDGIFTID,
            @ISPLANNEDGIFTADDITION = ISADDITION,
            @PLANNEDGIFTADDITIONID = PLANNEDGIFTADDITIONID
        from
            dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS
        where
            REVENUEID = @REVENUEID;

        if exists(select ID from dbo.PLANNEDGIFTPAYOUT where REVENUEID = @REVENUEID)
            set @HASPLANNEDGIFTPAYOUT = 1;
        if @HASPLANNEDGIFTPAYOUT = 1 
        begin
            if exists(select ID from dbo.PLANNEDGIFTPAYOUTADJUSTMENT where POSTSTATUSCODE = 1 and REVENUEID = @REVENUEID)
            set @HASUNPOSTEDPAYOUTADJUSTMENT = 1 
        end
    end

    if @ISORDER = 1
    begin
        select 
            @ORDERTICKETTOTAL = dbo.UFN_SALESORDER_GETTICKETOTAL(SALESORDER.ID),
            @ORDERMEMBERSHIPTOTAL = dbo.UFN_SALESORDER_GETMEMBERSHIPTOTAL(SALESORDER.ID),
            @ORDERDONATIONTOTAL = dbo.UFN_SALESORDER_GETDONATIONTOTAL(SALESORDER.ID),
            @ORDEREVENTREGISTRATIONTOTAL = dbo.UFN_SALESORDER_GETEVENTREGISTRATIONTOTAL([SALESORDER].[ID]),
            @ORDERADJUSTMENTTOTAL = dbo.UFN_SALESORDER_GETORDERADJUSTMENTTOTAL(SALESORDER.ID),
            @ORDERHASMEMBERSHIP = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].[ID] and SALESORDERITEM.TYPECODE = 1)), 0),
            @ORDERHASDONATION = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].[ID] and SALESORDERITEM.TYPECODE = 2)), 0),
            @ORDERHASADJUSTMENT = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].[ID] and SALESORDERITEM.TYPECODE in (3,4,5))), 0),
            @ORDERHASEVENTREGISTRATION = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].[ID] and SALESORDERITEM.TYPECODE = 6)), 0),
            @REFUNDSTATUS = SALESORDER.REFUNDSTATUS,
            @REFUNDTOTAL = (select coalesce(sum(amount), 0) from dbo.CREDIT where SALESORDERID = SALESORDER.ID and CREDIT.TYPECODE = 0),
            @SALESORDERID = ID,
            @ORDERHASRESOURCE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].ID and SALESORDERITEM.TYPECODE in (8,9))), 0),
            @ORDERRESOURCETOTAL = dbo.UFN_RESERVATION_GETRESOURCETOTAL([SALESORDER].ID),
            @ORDERHASSTAFFRESOURCE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].ID and SALESORDERITEM.TYPECODE in (10,11))), 0),
            @ORDERSTAFFRESOURCETOTAL = dbo.UFN_RESERVATION_GETSTAFFRESOURCETOTAL([SALESORDER].ID),
            @ORDERHASFACILITY = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].ID and SALESORDERITEM.TYPECODE = 7) ), 0),
            @ORDERHASMERCHANDISE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = [SALESORDER].ID and SALESORDERITEM.TYPECODE = 14) ), 0),
            @ORDERMERCHANDISETOTAL = dbo.UFN_SALESORDER_GETMERCHANDISETOTAL(SALESORDER.ID)
        from 
            dbo.[SALESORDER] 
        where 
            [SALESORDER].[REVENUEID] = @REVENUEID;

        if @ORDERHASFACILITY = 1
        begin
            select 
                @ORDERFACILITYTOTAL = sum(TOTAL)
            from 
                dbo.SALESORDERITEM
            where 
                SALESORDERID = @SALESORDERID and
                TYPECODE = 7
        end

      --If there are order credit's tied to that revenue id, we need

      --to account for those too

        select
            @AMOUNT = @AMOUNT - coalesce(sum([CREDITGLDISTRIBUTION].[AMOUNT]), 0)
        from
            dbo.[CREDITGLDISTRIBUTION]
        where
            [CREDITGLDISTRIBUTION].[REVENUEID] = @ID and
            [CREDITGLDISTRIBUTION].[OUTDATED] = 0 and
            [CREDITGLDISTRIBUTION].[TRANSACTIONTYPECODE] = 0
    end



    if @ISPAYMENT = 1
        if exists
        (
            select 
                1
            from 
                dbo.SALESORDERPAYMENT
            left join 
                dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDERPAYMENT.SALESORDERID
            where 
                SALESORDERPAYMENT.PAYMENTID = @ID
                and (SALESORDERITEM.TYPECODE is null or SALESORDERITEM.TYPECODE <> 12)
        ) 
        begin
            select 
                @ISORDERAPPLICATION = 1,
                @SALESORDERPAYMENTID = SALESORDERPAYMENT.ID,
                @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
            from 
                dbo.SALESORDERPAYMENT
            inner join 
                dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
            where 
                PAYMENTID = @REVENUEID;

            if exists
            (
                select 
                    1 
                from 
                    dbo.REVENUESPLIT 
                where 
                    REVENUEID = @ID 
                    and APPLICATIONCODE <> 10
            )
                set @ISMIXEDORDERPAYMENT = 1;
        end

        if @ISORDER = 0 and exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.APPLICATIONCODE = 11 and LI.DELETEDON is null and LI.TYPECODE != 1)
        begin
            set @ISMISCELLANEOUSPAYMENT = 1;
        end

        if @ISPAYMENT = 1 and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI 
                inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID 
                and RS.APPLICATIONCODE = 1 AND RS.TYPECODE = 1)
            set @REVENUEIDHASREGISTRATION = 1

        -- Refunds are enabled at the payment level now

        set @PAYMENTALLOWSCREDITCARDREFUND = null;

        -- is there a donation applied to an event registration?

        if @ISPAYMENT = 1 and exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0 and LI.DELETEDON is null and LI.TYPECODE != 1)
            set @HASEVENTREGISTRATIONDESIGNATIONAPPLICATION = 1;
        if exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT as [SPLITS] on LI.ID = [SPLITS].ID where LI.FINANCIALTRANSACTIONID = @ID and [SPLITS].DESIGNATIONID is not null)
            set @HASDESIGNATIONS = 1;

        -- Is there a matching gift claim application

        if @ISPAYMENT = 1
            and exists
            (
                select
                    1
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                inner join 
                    dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    --Get only applications paying a matching gift claim by inner joining to dbo.INSTALLMENTSPLITPAYMENT.

                    -- Unapplied matching gift payments would not have an installment split payment row because there is

                    -- no commitment being paid.

                inner join 
                    dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT_EXT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                where
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                    and REVENUESPLIT_EXT.TYPECODE = 0 --Gift

                    and REVENUESPLIT_EXT.APPLICATIONCODE = 7 --Matching gift

            )
            set @HASMATCHINGGIFTCLAIMAPPLICATION = 1;

    if exists(select ID from dbo.GRANTOR where ID = @CONSTITUENTID)
      select @CONSTITUENTISGRANTOR = 1

    if exists(
      select JE.ID from dbo.JOURNALENTRY as JE
      inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
      where LI.FINANCIALTRANSACTIONID = @ID
    )
        set @HASGLDISTRIBUTIONS = 1

    set @HASGIFTAIDSPLITONPENDINGR68PROCESS = dbo.UFN_REVENUE_HASGIFTAIDSPLITONPENDINGR68(@REVENUEID, @ISPAYMENT)

    if exists(select ID from FINANCIALTRANSACTIONLINEITEM where TYPECODE = 3 and POSTSTATUSCODE = 2 and FINANCIALTRANSACTIONID = @ID)
        set @HASPOSTEDBENEFITS = 1

    set @SHOWGLINFO = 0
    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('133F9BCA-00F1-4007-9792-586B931340C6') != 0
    begin
    --KevinKoe - WI 129011 - Always display the account system for the payment, regardless of whether or not the user has access to the account system

    --if dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID) > 1

        set @SHOWGLINFO = 1
    end

    if @ISAUCTIONDONATION = 1
      select 
        @AUCTIONITEMID = AUCTIONITEM.ID,
        @AUCTIONDONATIONISRESERVEDORSOLD = case
                when (AUCTIONITEMRESERVATION.ID is not null) or ([RESERVEDPARENTPACKAGE].ID is not null) or (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null) then 1
                else 0
                end
      from 
        dbo.AUCTIONITEM
        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
        left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
        left join dbo.AUCTIONITEMRESERVATION [RESERVEDPARENTPACKAGE] on [RESERVEDPARENTPACKAGE].AUCTIONITEMID = AUCTIONITEM.PACKAGEID
      where AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID;

      if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.TYPECODE = 12 and RS.APPLICATIONCODE = 12 and LI.DELETEDON is null and LI.TYPECODE != 1)
          set @HASAUCTIONPURCHASE = 1;

    if @HASAUCTIONPURCHASE = 1
      if not exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.TYPECODE <> 12 and RS.APPLICATIONCODE <> 12 and LI.DELETEDON is null and LI.TYPECODE != 1)
        set @SHOWREVENUEGLDISTRIBUTIONS = 0;

    if exists (select 1 from dbo.JOURNALENTRY_EXT where LOGICALREVENUEID = @ID and TABLENAMECODE = 8)
      set @HASGIFTFEEGLDISTRIBUTIONS = 1;

    if exists(    select EXT.ID 
                from dbo.JOURNALENTRY_EXT EXT
                inner join dbo.JOURNALENTRY JE on JE.ID = EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
          where 
            LI.POSTSTATUSCODE = 2 and 
            EXT.LOGICALREVENUEID = @ID
                    and EXT.TABLENAMECODE = 8)
      set @HASPOSTEDGIFTFEES = 1

    if exists (    select 1 from dbo.GIFTFEEADJUSTMENT
          where
            REVENUEID = @ID and
            POSTSTATUSCODE = 1) -- Not posted

      set @HASUNPOSTEDGIFTFEESADJUSTMENT = 1

    if exists(

      select 
        JE.ID 
      from dbo.JOURNALENTRY JE
      inner join dbo.FINANCIALTRANSACTIONLINEITEM LI 
        on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
      where LI.FINANCIALTRANSACTIONID = @ID 
        and JE.TYPECODE <> 0 -- system

        and LI.DELETEDON is NULL -- not outdated


      )
      or
      exists(select 1 
            from dbo.JOURNALENTRY_EXT EXT
            inner join dbo.JOURNALENTRY JE on JE.ID = EXT.ID
      where EXT.TABLENAMECODE = 2 and EXT.REVENUEPURCHASEID = @ID and JE.TYPECODE != 0 and EXT.OUTDATED = 0)
      set @HASSYSTEMDISTRIBUTIONS = 1;

      set @HASMATCHINGACCOUNTSYSTEM = 1
      if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1
      begin
        if @ISPLEDGE = 1 and not exists (select * from UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) where ID = @PDACCOUNTSYSTEMID)
        begin
          set @HASMATCHINGACCOUNTSYSTEM = 0
        end
      end

    set @ISSECURITYDEPOSITPAYMENT = 0

    if @ISPAYMENT = 1 and exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.TYPECODE = 13 and LI.DELETEDON is null and LI.TYPECODE != 1)
        set @ISSECURITYDEPOSITPAYMENT = 1

    if @ISPAYMENT = 1 and @ISMISCELLANEOUSPAYMENT = 0 and @PAYMENTTYPECODE in (0,1,2,10) --Cash, Check, Credit Card, Other

        select 
            @SHOWLINKDEPOSIT=case when DEPOSITID is null then 1 else 0 end
        from 
            dbo.BANKACCOUNTDEPOSITPAYMENT
        where 
            ID = @ID

    if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUESPLIT_EXT RS on RS.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and RS.APPLICATIONCODE not in (4, 6, 8))
        set @SHOWGIFTAIDREFUNDS = 1;

    set @HASLINKEDOPPORTUNITY = 0;
    if exists (select 1 from dbo.REVENUEOPPORTUNITY RO inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = RO.ID where LI.FINANCIALTRANSACTIONID = @ID)
        set @HASLINKEDOPPORTUNITY = 1;

    if @PAYMENTTYPECODE = 2 and exists 
        (
            select 
                1
            from 
                dbo.CREDITPAYMENT 
            inner join 
                dbo.FINANCIALTRANSACTION FT on FT.ID = CREDITPAYMENT.REVENUEID
            where 
                FT.ID = @ID 
        )
    begin
        set @HASREFUND = 1;
    end

return 0;