USP_DATAFORMTEMPLATE_VIEW_ORDERPATRON

The load procedure used by the view dataform template "Order Patron View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@BOARDMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	BOARDMEMBERCONSTITUENCYTEXT
@RELATIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RELATIONCONSTITUENCYTEXT
@STAFFCONSTITUENCYTEXT	nvarchar(100)	INOUT	STAFFCONSTITUENCYTEXT
@DONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	DONORCONSTITUENCYTEXT
@FUNDRAISERCONSTITUENCYTEXT	nvarchar(100)	INOUT	FUNDRAISERCONSTITUENCYTEXT
@PROSPECTCONSTITUENCYTEXT	nvarchar(100)	INOUT	PROSPECTCONSTITUENCYTEXT
@VOLUNTEERCONSTITUENCYTEXT	nvarchar(100)	INOUT	VOLUNTEERCONSTITUENCYTEXT
@COMMUNITYMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMUNITYMEMBERCONSTITUENCYTEXT
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@USERDEFINEDCONSTITUENCYTEXT	nvarchar(4000)	INOUT	USERDEFINEDCONSTITUENCYTEXT
@ALUMNUSSTATUSTEXT	nvarchar(100)	INOUT	ALUMNUSSTATUSTEXT
@REGISTRANTSTATUSTEXT	nvarchar(100)	INOUT	REGISTRANTSTATUSTEXT
@VENDORSTATUSTEXT	nvarchar(100)	INOUT	VENDORSTATUSTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@ISASSOCIATED	bit	INOUT	ISASSOCIATED
@NOTASSOCIATED	bit	INOUT	NOTASSOCIATED
@DOESNOTEXIST	bit	INOUT	DOESNOTEXIST
@INFOEXISTS	bit	INOUT	INFOEXISTS
@CONSTITUENTNAME	nvarchar(40)	INOUT	CONSTITUENTNAME
@MEMBERSHIPS	xml	INOUT	MEMBERSHIPS
@ORDERNUMBER	int	INOUT	Order #
@ORDERDATE	datetime	INOUT	Order date
@POSTSTATUS	nvarchar(50)	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@ORDERTOTAL	money	INOUT	Total
@AMOUNTPAID	money	INOUT	Amount paid
@BALANCE	money	INOUT	Balance
@AMOUNTREFUNDED	money	INOUT	Amount refunded
@REFUNDID	uniqueidentifier	INOUT
@USERNAME	nvarchar(100)	INOUT	Created by
@SALESMETHODTYPE	nvarchar(50)	INOUT	Sales method
@ORDERSTATUS	tinyint	INOUT	Status
@DELIVERYMETHOD	nvarchar(100)	INOUT	Method
@RECIPIENT	nvarchar(154)	INOUT	Recipient
@DELIVERYADDRESS	nvarchar(300)	INOUT	Address
@DELIVERYPHONE	nvarchar(100)	INOUT	Phone
@DELIVERYEMAIL	UDT_EMAILADDRESS	INOUT	Email
@ORDERSTATUSTEXT	nvarchar(50)	INOUT	Order status
@BANKCONSTITUENCYTEXT	nvarchar(100)	INOUT	BANKCONSTITUENCYTEXT
@PATRONCONSTITUENCYTEXT	nvarchar(100)	INOUT	PATRONCONSTITUENCYTEXT
@SAMEASPATRON	nvarchar(14)	INOUT	Same as Patron
@ADDRESSREQUIRED	bit	INOUT	ADDRESSREQUIRED
@PHONEREQUIRED	bit	INOUT	PHONEREQUIRED
@EMAILREQUIRED	bit	INOUT	EMAILREQUIRED
@RECIPIENTASSOCIATED	bit	INOUT	RECIPIENTASSOCIATED
@SALESMETHODTYPECODE	tinyint	INOUT	SALESMETHODTYPECODE
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@OVERAGEKEPT	money	INOUT
@ISTAXEXEMPT	bit	INOUT
@TAXEXEMPTREASON	nvarchar(100)	INOUT
@TAXEXEMPTCOMMENTS	nvarchar(255)	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ORDERPATRON
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DATALOADED bit = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100)= null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100)= null output,
    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
    @VENDORSTATUSTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @ISASSOCIATED bit = null output,
    @NOTASSOCIATED bit = null output,
    @DOESNOTEXIST bit = null output,
    @INFOEXISTS bit = null output,
    @CONSTITUENTNAME nvarchar(40) = null output,
    @MEMBERSHIPS xml = null output,
    @ORDERNUMBER int = null output,
    @ORDERDATE datetime = null output,
    @POSTSTATUS nvarchar(50) = null output,
    @POSTDATE datetime = null output,
    @ORDERTOTAL money = null output,
    @AMOUNTPAID money = null output,
    @BALANCE money = null output,
    @AMOUNTREFUNDED money = null output,
    @REFUNDID uniqueidentifier = null output,
    @USERNAME nvarchar(100) = null output,
    @SALESMETHODTYPE nvarchar(50) = null output,
    @ORDERSTATUS tinyint = null output,
    @DELIVERYMETHOD nvarchar(100) = null output,
    @RECIPIENT nvarchar(154) = null output,
    @DELIVERYADDRESS nvarchar(300) = null output,
    @DELIVERYPHONE nvarchar(100) = null output,
    @DELIVERYEMAIL dbo.UDT_EMAILADDRESS = null output,
    @ORDERSTATUSTEXT nvarchar(50) = null output,
    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
    @SAMEASPATRON nvarchar(14) = null output,
    @ADDRESSREQUIRED bit = null output,
    @PHONEREQUIRED bit = null output,
    @EMAILREQUIRED bit = null output,
    @RECIPIENTASSOCIATED bit = null output,
    @SALESMETHODTYPECODE tinyint = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @OVERAGEKEPT money = null output,
    @ISTAXEXEMPT bit = null output,
    @TAXEXEMPTREASON nvarchar(100) = null output,
    @TAXEXEMPTCOMMENTS nvarchar(255) = null output
)
as
    set nocount on;
    set @MATCHFINDERONLINERECORDID = 0;
    set @DATALOADED = 0;

    select
        @CONSTITUENTID = CONSTITUENTID,
        @SAMEASPATRON = SAMEASPATRON
    from dbo.SALESORDER
    where @ID = ID

    set @ISASSOCIATED = 0;
    set @INFOEXISTS = 0;
    set @DOESNOTEXIST = 1;
    set @NOTASSOCIATED = 1;


    if @CONSTITUENTID is not null
    begin
        set @ISASSOCIATED = 1;
        set @INFOEXISTS = 1;
        set @DOESNOTEXIST = 0;
        set @NOTASSOCIATED = 0;

        select
            @CONSTITUENTNAME = C.NAME
        from dbo.CONSTITUENT as C
        where C.ID = @CONSTITUENTID;

        declare @CURRENTDATE date;
        set @CURRENTDATE = getdate();

        select @EMAILADDRESS = EMAILADDRESS
        from dbo.EMAILADDRESS
        where CONSTITUENTID = @CONSTITUENTID and
            ISPRIMARY = 1;

        --PHONE

        select @PHONENUMBER = PHONE.NUMBER
        from dbo.PHONE
        where PHONE.CONSTITUENTID = @CONSTITUENTID and
            PHONE.ISPRIMARY = 1;


        select
            @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID)
        from dbo.ADDRESS
        where CONSTITUENTID = @CONSTITUENTID and
            ISPRIMARY = 1;
        --Constituencies


        set @RECOGNITIONCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('3dfac92e-78bd-4051-abdc-02c675deb8f6') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISRECOGNITION(@CONSTITUENTID, @CURRENTAPPUSERID) = 1
            begin
                set @RECOGNITIONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6');
            end
        end

        set @MEMBERCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('2d11326e-8f3b-4322-9797-57c1aacfa5df') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISMEMBER(@CONSTITUENTID, @CURRENTAPPUSERID) = 1
            begin
                set @MEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df');
            end
        end

        set @BOARDMEMBERCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@CONSTITUENTID) = 1
            begin
                set @BOARDMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF');
            end
        end

        set @STAFFCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('6093915E-ADE9-42BE-88AE-304731754467') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISSTAFF(@CONSTITUENTID) = 1
            begin
                set @STAFFCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467');
            end
        end

        set @DONORCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('70165682-4324-46EC-9439-83FC0CC67E7F') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISDONOR(@CONSTITUENTID) = 1
            begin
                set @DONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F');
            end
        end

        set @LOYALDONORCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISLOYALDONOR(@CONSTITUENTID) = 1
            begin
                set @LOYALDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B');
            end
        end

        set @MAJORDONORCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISMAJORDONOR(@CONSTITUENTID) = 1
            begin
                set @MAJORDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39');
            end
        end

        if len(@LOYALDONORCONSTITUENCYTEXT) > 0 or len(@MAJORDONORCONSTITUENCYTEXT) > 0
            set @DONORCONSTITUENCYTEXT = null;

        set @FUNDRAISERCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('D2DCA06A-BE6E-40B3-B95D-59A926181923') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISFUNDRAISER(@CONSTITUENTID) = 1
            begin
                set @FUNDRAISERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923');
            end
        end

        set @PROSPECTCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('00E748FB-940D-4A7D-A133-C148B29410A8') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISPROSPECT(@CONSTITUENTID) = 1
            begin
                set @PROSPECTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8');
            end
        end

        set @VOLUNTEERCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('E7489703-3D63-4017-A2BC-88C092563C5D') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISVOLUNTEER(@CONSTITUENTID) = 1
            begin
                set @VOLUNTEERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D');
            end
        end

        set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@CONSTITUENTID);

        set @REGISTRANTSTATUSTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('C49D4B46-72A7-4206-91AA-BEABA2323E3C') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISREGISTRANT(@CONSTITUENTID) = 1
            begin
                set @REGISTRANTSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C');
            end
        end

        set @VENDORSTATUSTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('5435C96D-8617-46C3-9A62-5AFF08451A53') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISVENDOR(@CONSTITUENTID) = 1
            begin
                set @VENDORSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53');
            end
        end

        set @MATCHFINDERCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('2D04A9C5-27D0-4646-BF0F-6826E4C12632') = 1
        begin
            if @MATCHFINDERONLINERECORDID is not null and @MATCHFINDERONLINERECORDID <> 0
            begin
                set @MATCHFINDERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632');
            end
        end

        set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@CONSTITUENTID);

        set @BANKCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('171AB3CD-C4E1-4825-B693-10F524A7A594') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISBANK(@CONSTITUENTID) = 1
            begin
                set @BANKCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594');
            end
        end

        set @PATRONCONSTITUENCYTEXT = null;
        if dbo.UFN_CONSTITUENCY_ISACTIVE('A843B859-4C6B-445B-97F3-179582E270A5') = 1
        begin
            if dbo.UFN_CONSTITUENT_ISPATRON(@CONSTITUENTID) = 1
            begin
                set @PATRONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5');
            end
        end

        set @MEMBERSHIPS =
        (
            select
                MEMBERSHIP.ID [MEMBERSHIPID],
                dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) as MEMBERSHIPPROGRAMNAME,
                case when MEMBERSHIP.STATUSCODE = 1 then MT.TRANSACTIONDATE else MEMBERSHIP.EXPIRATIONDATE end as MEMBERSHIPEXPIRATION,
                dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) as MEMBERSHIPLEVELNAME,
                case when @CURRENTDATE between dateadd(month, -MEMBERSHIPLEVEL.BEFOREEXPIRATION, MEMBERSHIP.EXPIRATIONDATE) and
                        dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) then 1
                        else 0 end as INRENEWALWINDOW,
                case when @CURRENTDATE > MEMBERSHIP.EXPIRATIONDATE then 1
                        else 0 end as ISLAPSED,
                case when MEMBERSHIP.STATUSCODE = 1 then 1
                        else 0 end as ISCANCELLED,
                case MEMBERSHIP.STATUSCODE
                    when 2 then
                        1
                    else
                        0
                end [ISPENDING]
            from dbo.MEMBER
            inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
            inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
            left join dbo.MEMBERSHIPTRANSACTION MT on (MEMBERSHIP.ID = MT.MEMBERSHIPID and MT.ACTIONCODE = 4) and (MT.ID in (select top 1 ID from dbo.MEMBERSHIPTRANSACTION MT2 where MT2.ACTIONCODE = 4 and MT2.MEMBERSHIPID = MEMBERSHIP.ID order by MT2.TRANSACTIONDATE desc))
            where MEMBER.CONSTITUENTID = @CONSTITUENTID and
                MEMBER.ISDROPPED = 0
            for xml raw ('ITEM'), type, elements, root('MEMBERSHIPS'), BINARY BASE64
        )

    end

    select
        @DATALOADED = 1,
        @ORDERNUMBER = SALESORDER.SEQUENCEID,
        @ORDERDATE = SALESORDER.TRANSACTIONDATE,
        @POSTDATE = FINANCIALTRANSACTION.POSTDATE,
        @USERNAME = case SALESORDER.SALESMETHODTYPECODE
            when 2 then dbo.UFN_APPUSER_GETNAME([SALESORDER].[APPUSERID]) --The online order changeagent should always be the

            else coalesce(dbo.UFN_APPUSER_GETNAME([SALESORDER].[APPUSERID]), dbo.UFN_CHANGEAGENT_GETDESCRIPTION(SALESORDER.ADDEDBYID))
        end,
        @SALESMETHODTYPE = SALESORDER.SALESMETHODTYPE,
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
        @ORDERSTATUS = SALESORDER.STATUSCODE,
        @ORDERSTATUSTEXT = SALESORDER.STATUS,
        @DELIVERYMETHOD = coalesce(dbo.UFN_DELIVERYMETHOD_GETNAME(SALESORDER.DELIVERYMETHODID), coalesce(dbo.UFN_SALESMETHODDELIVERYMETHOD_GETDEFAULTDELIVERYMETHOD(SALESORDER.SALESMETHODTYPECODE), 'Select')),
        @RECIPIENT = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.RECIPIENTID),
        @DELIVERYADDRESS = dbo.UFN_ADDRESS_TRANSLATE(CONTACTRECORDS.ADDRESSID),
        @DELIVERYPHONE = P.NUMBER,
        @DELIVERYEMAIL = E.EMAILADDRESS,
        @ADDRESSREQUIRED = coalesce(D.ADDRESSREQUIRED, 0),
        @PHONEREQUIRED = coalesce(D.PHONEREQUIRED, 0),
        @EMAILREQUIRED = coalesce(D.EMAILREQUIRED, 0),
        @ISTAXEXEMPT = case when SALESORDERTAXEXEMPTINFO.ID is null then 0 else 1 end,
        @TAXEXEMPTREASON = SALESORDERTAXEXEMPTREASONCODE.[DESCRIPTION],
        @TAXEXEMPTCOMMENTS = SALESORDERTAXEXEMPTINFO.COMMENTS
    from dbo.SALESORDER
    left join dbo.FINANCIALTRANSACTION on SALESORDER.REVENUEID = FINANCIALTRANSACTION.ID
    outer apply dbo.UFN_SALESORDER_CONTACTRECORDS(SALESORDER.ID) as CONTACTRECORDS
    left join dbo.PHONE as P on P.ID = CONTACTRECORDS.PHONEID
    left join dbo.EMAILADDRESS as E on E.ID = CONTACTRECORDS.EMAILADDRESSID
    left join dbo.DELIVERYMETHOD as D on D.ID = SALESORDER.DELIVERYMETHODID
    left join dbo.SALESORDERTAXEXEMPTINFO on SALESORDERTAXEXEMPTINFO.ID = SALESORDER.ID
    left join dbo.SALESORDERTAXEXEMPTREASONCODE on SALESORDERTAXEXEMPTREASONCODE.ID = SALESORDERTAXEXEMPTINFO.SALESORDERTAXEXEMPTREASONCODEID
    where SALESORDER.ID = @ID;

    if @RECIPIENT is not null and @RECIPIENT <> ''
    set @RECIPIENTASSOCIATED = 1;

    select top 1 @POSTSTATUS =
        case
            when REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1 then 'Posted (adjustment pending)'
            when REVENUEPOSTED.ID is not null then 'Posted'
            when FINANCIALTRANSACTION.POSTSTATUSCODE = 3 then 'Do not post'
            else 'Not posted'
        end
    from dbo.FINANCIALTRANSACTION
    inner join dbo.SALESORDER on FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
    left join dbo.ADJUSTMENT on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID
    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FINANCIALTRANSACTION.ID
    where SALESORDER.ID = @ID
    order by ADJUSTMENT.DATEADDED desc;

    set @AMOUNTPAID = 0;
    set @AMOUNTREFUNDED = 0;

    select
        @ORDERTOTAL = TOTALS.TOTAL,
        @BALANCE = TOTALS.BALANCE,
        @AMOUNTPAID = TOTALS.AMOUNTPAID,
        @AMOUNTREFUNDED = TOTALS.REFUNDS,
        @OVERAGEKEPT = TOTALS.OVERAGEKEPT
    from
        dbo.UFN_SALESORDER_TOTALS(@ID) TOTALS;

    with REFUND as (
        select FT.ID
        from dbo.SALESORDER SO
        inner join dbo.FINANCIALTRANSACTION FT on FT.PARENTID = SO.REVENUEID
        where SO.ID = @ID and FT.TYPECODE = 23
    )
    select @REFUNDID = case when (select count(ID) from REFUND) = 1 then (select top 1 ID from REFUND) else null end;

    return 0;