USP_DATAFORMTEMPLATE_VIEW_SALESORDERPAGEDATA

The load procedure used by the view dataform template "Sales Order Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ORDERNUMBER	int	INOUT	Order number
@CONSTITUENTNAME	nvarchar(154)	INOUT	Name
@CONSTITUENTEXISTS	bit	INOUT	Exists
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@ORDERSTATUS	tinyint	INOUT	Status
@TRANSACTIONDATE	datetime	INOUT	Transaction date
@ORDERTOTAL	money	INOUT	Order total
@ORDERITEMCOUNT	int	INOUT	Order item count
@ORDERTICKETTOTAL	money	INOUT	Order ticket total
@ORDERMEMBERSHIPTOTAL	money	INOUT	Order membership total
@ORDERDONATIONTOTAL	money	INOUT	Order donation total
@ORDERADJUSTMENTTOTAL	money	INOUT	Order adjustments total
@ORDERHASMEMBERSHIP	bit	INOUT	Order has memberships
@ORDERHASDONATION	bit	INOUT	Order has donations
@ORDERHASADJUSTMENT	bit	INOUT	Order has adjustments
@REVENUEID	uniqueidentifier	INOUT	Revenue ID
@ISPOSTED	bit	INOUT	Is posted
@DONOTPOST	bit	INOUT	Do not post
@ORDERPAYMENTCOUNT	int	INOUT	Payment count
@ORDERBALANCE	money	INOUT	Order balance
@REFUNDSTATUS	int	INOUT	Refund Status
@ORDERHASEVENTREGISTRATION	bit	INOUT	Order has event registration
@ORDEREVENTREGISTRATIONTOTAL	money	INOUT	Order event registration total
@REFUNDTOTAL	money	INOUT	Refund total
@SALESMETHODTYPECODE	tinyint	INOUT	SALESMETHODTYPECODE
@PRIMARYMEMBERSHIPID	uniqueidentifier	INOUT	Primary membership ID
@PRIMARYMEMBERSHIPDESCRIPTION	nvarchar(max)	INOUT	Primary membership
@ORDERHASRESOURCE	bit	INOUT	ORDERHASRESOURCE
@ORDERRESOURCETOTAL	money	INOUT	ORDERRESOURCETOTAL
@ORDERHASSTAFFRESOURCE	bit	INOUT	ORDERHASSTAFFRESOURCE
@ORDERSTAFFRESOURCETOTAL	money	INOUT	ORDERSTAFFRESOURCETOTAL
@PATRONHASMEMBERSHIP	bit	INOUT	PATRONHASMEMBERSHIP
@LASTRECALCULATIONDATE	datetime	INOUT	Last recalculation date
@PRICINGCODE	tinyint	INOUT	PRICINGCODE
@ORDERHASADDITIONALTICKETS	bit	INOUT	ORDERHASADDITIONALTICKETS
@ORDERHASACTIVETICKETS	bit	INOUT	ORDERHASACTIVETICKETS
@ORDERFLATRATETOTAL	money	INOUT	Order flat rate ticket total
@ORDERADDITIONALTICKETSTOTAL	money	INOUT	Order additional tickets total
@ORDERHASLOCATION	bit	INOUT	ORDERHASLOCATION
@ORDERLOCATIONTOTAL	money	INOUT	ORDERLOCATIONTOTAL
@ORDERHASMERCHANDISE	bit	INOUT	ORDERHASMERCHANDISE
@ORDERMERCHANDISETOTAL	money	INOUT	ORDERMERCHANDISETOTAL
@HASUNPOSTEDADJUSTMENT	bit	INOUT	HASUNPOSTEDADJUSTMENT
@PATRONHASMIDTERMUPGRADE	bit	INOUT	PATRONHASMIDTERMUPGRADE
@MEMBERSHIPID	uniqueidentifier	INOUT
@HASBENEFITDISTRIBUTION	bit	INOUT
@ORDERHASTICKETS	bit	INOUT
@ORDERHASNONDAILYTICKETS	bit	INOUT
@EMAILTICKETSAVAILABLE	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ORDERNUMBER int = null output,
    @CONSTITUENTNAME nvarchar(154) = null output,
    @CONSTITUENTEXISTS bit = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ORDERSTATUS tinyint = null output,
    @TRANSACTIONDATE datetime = null output,
    @ORDERTOTAL money = null output,
    @ORDERITEMCOUNT int = 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,
    @REVENUEID uniqueidentifier = null output,
    @ISPOSTED bit = null output,
    @DONOTPOST bit = null output,
    @ORDERPAYMENTCOUNT int = null output,
    @ORDERBALANCE money = null output,
    @REFUNDSTATUS int = null output,
    @ORDERHASEVENTREGISTRATION bit = null output,
    @ORDEREVENTREGISTRATIONTOTAL money = null output,
    @REFUNDTOTAL money = null output,
    @SALESMETHODTYPECODE tinyint = null output,
    @PRIMARYMEMBERSHIPID uniqueidentifier = null output,
    @PRIMARYMEMBERSHIPDESCRIPTION nvarchar(max) = null output,
    @ORDERHASRESOURCE bit = null output,
    @ORDERRESOURCETOTAL money = null output,
    @ORDERHASSTAFFRESOURCE bit = null output,
    @ORDERSTAFFRESOURCETOTAL money = null output,
    @PATRONHASMEMBERSHIP bit = null output,
    @LASTRECALCULATIONDATE datetime = null output,
    @PRICINGCODE tinyint = null output,
    @ORDERHASADDITIONALTICKETS bit = null output,
    @ORDERHASACTIVETICKETS bit = null output,
    @ORDERFLATRATETOTAL money = null output,
    @ORDERADDITIONALTICKETSTOTAL money = null output,
    @ORDERHASLOCATION bit = null output,
    @ORDERLOCATIONTOTAL money = null output,
    @ORDERHASMERCHANDISE bit = null output,
    @ORDERMERCHANDISETOTAL money = null output,
    @HASUNPOSTEDADJUSTMENT bit = null output,
    @PATRONHASMIDTERMUPGRADE bit = null output,
    @MEMBERSHIPID uniqueidentifier = null output,
    @HASBENEFITDISTRIBUTION bit = null output,
    @ORDERHASTICKETS bit = null output,
    @ORDERHASNONDAILYTICKETS bit = null output,
    @EMAILTICKETSAVAILABLE bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select 
        @ORDERSTATUS = SALESORDER.STATUSCODE, 
        @TRANSACTIONDATE = SALESORDER.TRANSACTIONDATE,
        @LASTRECALCULATIONDATE  = SALESORDER.LASTRECALCULATIONDATE,
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
    from dbo.SALESORDER
    where SALESORDER.ID = @ID

    -- Since this view form is used during payment add, we can't raise an error hear for incomplete reservations
    --if @SALESMETHODTYPECODE = 3 and @ORDERSTATUS <> 1
    --    raiserror('Only completed reservations can be viewed by this page.', 13, 1);

    declare @CHANGEAGENTID uniqueidentifier
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

    if @ORDERSTATUS = 6
        exec dbo.USP_RESERVEDORDERRECALCULATE_UPDATE @ID, @CHANGEAGENTID

    else if @ORDERSTATUS = 7
    begin
        --Set delivery method if it has not been
        if not exists(select 1 from dbo.[SALESORDER] where [ID] = @ID and [DELIVERYMETHODID] is not null)
        begin
            declare @DEFAULTDELIVERYMETHODID uniqueidentifier
            select @DEFAULTDELIVERYMETHODID = [SALESMETHODDELIVERYMETHOD].[DELIVERYMETHODID]
            from dbo.[SALESMETHODDELIVERYMETHOD]
            inner join dbo.[SALESMETHOD]
                on [SALESMETHODDELIVERYMETHOD].[SALESMETHODID] = [SALESMETHOD].[ID]
            where 
                [SALESMETHOD].[TYPECODE] = (select [SALESMETHODTYPECODE] from dbo.[SALESORDER] where [ID] = @ID) and
                [SALESMETHODDELIVERYMETHOD].[ISDEFAULT] = 1

            update dbo.[SALESORDER]
            set 
                [DELIVERYMETHODID] = @DEFAULTDELIVERYMETHODID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where [ID] = @ID
        end
    end

    select 
        @DATALOADED = 1,
        @ORDERNUMBER = SALESORDER.SEQUENCEID,
        @CONSTITUENTEXISTS = case when SALESORDER.CONSTITUENTID is null then 0 else 1 end,
        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID),
        @ORDERSTATUS = SALESORDER.STATUSCODE,
        @TRANSACTIONDATE = SALESORDER.TRANSACTIONDATE,
        @ORDERTOTAL = TOTALS.TOTAL,
        @ORDERITEMCOUNT = coalesce((select count(SALESORDERITEM.ID) from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE in (0,1,2,6,7,8,9,10,11)), 0),
        @ORDERTICKETTOTAL = dbo.UFN_SALESORDER_GETTICKETOTAL(@ID),
        @ORDERMEMBERSHIPTOTAL = dbo.UFN_SALESORDER_GETMEMBERSHIPTOTAL(@ID),
        @ORDERDONATIONTOTAL = dbo.UFN_SALESORDER_GETDONATIONTOTAL(@ID),
        @ORDERADJUSTMENTTOTAL = dbo.UFN_SALESORDER_GETORDERADJUSTMENTTOTAL(@ID),
        @ORDEREVENTREGISTRATIONTOTAL = dbo.UFN_SALESORDER_GETEVENTREGISTRATIONTOTAL(@ID),
        @ORDERHASTICKETS = coalesce((select 1 where exists(select ID from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 0)), 0),
        @ORDERHASMEMBERSHIP = coalesce((select 1 where exists(select ID from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 1)), 0),
        @ORDERHASDONATION = coalesce((select 1 where exists(select ID from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 2)), 0),
        @ORDERHASADJUSTMENT = coalesce((select 1 where exists(select ID from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE in (3,4,5))), 0),
        @ORDERHASEVENTREGISTRATION = coalesce((select 1 where exists(select ID from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 6)), 0),
        @REVENUEID = isnull([SALESORDER].[REVENUEID], '00000000-0000-0000-0000-000000000000'),
        @ISPOSTED = coalesce((select case when POSTSTATUSCODE = 2 then 1 else 0 end from dbo.FINANCIALTRANSACTION FT where FT.ID = SALESORDER.REVENUEID), 0),
        @DONOTPOST = coalesce((select case when POSTSTATUSCODE = 3 then 1 else 0 end from dbo.FINANCIALTRANSACTION FT where FT.ID = SALESORDER.REVENUEID), 0),
        @ORDERPAYMENTCOUNT = (select count(ID) from dbo.[SALESORDERPAYMENT] where [SALESORDERPAYMENT].[SALESORDERID] = @ID) + 
                    (select count(ID) from dbo.[RESERVATIONSECURITYDEPOSITPAYMENT] where [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID] = @ID),
        @ORDERBALANCE = TOTALS.BALANCE,
        @REFUNDSTATUS = SALESORDER.REFUNDSTATUS,
        @REFUNDTOTAL = TOTALS.REFUNDS,
        @ORDERHASRESOURCE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE in (8,9))), 0),
        @ORDERRESOURCETOTAL = dbo.UFN_RESERVATION_GETRESOURCETOTAL(@ID),
        @ORDERHASSTAFFRESOURCE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE in (10,11))), 0),
        @ORDERSTAFFRESOURCETOTAL = dbo.UFN_RESERVATION_GETSTAFFRESOURCETOTAL(@ID),
        @PRICINGCODE = coalesce((select top(1) PRICINGCODE from dbo.RESERVATION where ID = @ID), 0),
        @ORDERHASLOCATION = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 7) ), 0),
        @ORDERHASMERCHANDISE = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 14)), 0),
        @ORDERMERCHANDISETOTAL = dbo.UFN_SALESORDER_GETMERCHANDISETOTAL(@ID),
        @PATRONHASMIDTERMUPGRADE = dbo.UFN_SALESORDER_PATRONHASUPGRADE(@ID),
        @ORDERHASNONDAILYTICKETS = coalesce(
            (select 1 where exists(
                select SALESORDERITEM.ID
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                    inner join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
                where SALESORDERITEM.SALESORDERID = @ID 
                    and SALESORDERITEM.TYPECODE = 0
                    and PROGRAM.ISDAILYADMISSION = 0
            ))
        , 0)
    from dbo.SALESORDER
    left outer join dbo.CONSTITUENT on 
        SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    outer apply dbo.UFN_SALESORDER_TOTALS(@ID) TOTALS
    where
        SALESORDER.ID = @ID;

    if @DATALOADED = 1
    begin
        if @CONSTITUENTEXISTS = 1
        begin
            if exists
            (
                select [ID] from dbo.MEMBER
                where 
                    MEMBER.CONSTITUENTID = @CONSTITUENTID and 
                    MEMBER.ISDROPPED = 0
            )
                set @PATRONHASMEMBERSHIP = 1
            else
                set @PATRONHASMEMBERSHIP = 0

            select top(1)
                @PRIMARYMEMBERSHIPID = MEMBER.ID,
                @PRIMARYMEMBERSHIPDESCRIPTION = '' + MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME,
                @MEMBERSHIPID = MEMBERSHIP.ID
            from dbo.MEMBER
                inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
            where
                MEMBER.CONSTITUENTID = @CONSTITUENTID and
                MEMBER.ISDROPPED = 0 and
                @CURRENTDATE <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) and
                MEMBERSHIP.STATUSCODE = 0
            order by
                MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS asc
        end

        if @PRICINGCODE = 1
        begin
            set @ORDERHASADDITIONALTICKETS = 0

            -- Find out if there are items outside the flat rate
            -- and get their total if they exist
            if exists (select 1 from dbo.SALESORDERITEM where PRICINGSTRUCTURECODE <> 1 and TYPECODE = 0 and SALESORDERID = @ID)
            begin
                set @ORDERHASADDITIONALTICKETS = 1
                declare @TICKETFEES money;
                declare @TICKETDISCOUNTS money;

                select @ORDERADDITIONALTICKETSTOTAL = sum(TOTAL)
                from dbo.SALESORDERITEM
                where PRICINGSTRUCTURECODE <> 1 and TYPECODE = 0 and SALESORDERID = @ID

                select @TICKETFEES = coalesce(sum([SALESORDERITEM].[TOTAL]), 0)
                from dbo.[SALESORDERITEM]
                    inner join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
                where 
                    [SALESORDERITEM].[SALESORDERID] = @ID and 
                    [SALESORDERITEMFEE].[APPLIESTOCODE] = 1 and 
                    [SALESORDERITEMFEE].[SALESORDERITEMID] in 
                        (select [ID] from dbo.[SALESORDERITEM]
                        where [SALESORDERITEM].[SALESORDERID] = @ID and 
                        [SALESORDERITEM].[TYPECODE] = 0 and
                        [SALESORDERITEM].[PRICINGSTRUCTURECODE] <> 1)

                select @TICKETDISCOUNTS = coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]), 0)
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                    inner join [SALESORDERITEM] on [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
                where
                    [SALESORDERITEM].[TYPECODE] = 0 and
                    [SALESORDERITEM].[SALESORDERID] = @ID and
                    [SALESORDERITEM].[PRICINGSTRUCTURECODE] <> 1

                set @ORDERADDITIONALTICKETSTOTAL = (@ORDERADDITIONALTICKETSTOTAL + @TICKETFEES) - @TICKETDISCOUNTS;
            end
            else
            begin                            
                set @ORDERADDITIONALTICKETSTOTAL = 0
            end

            -- Get the flat rate price for tickets
            select
                @ORDERFLATRATETOTAL = coalesce(sum(AMOUNT), 0.0)
            from dbo.RESERVATIONRATESCALEAPPLICATION 
            where RESERVATIONRATESCALEID = @ID and TYPECODE = 0
        end
        else
        begin
            set @ORDERHASADDITIONALTICKETS = 0
        end

        if exists(select * from dbo.SALESORDERITEM inner join dbo.TICKET on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID where SALESORDERITEM.SALESORDERID = @ID and TICKET.STATUSCODE = 0)
            set @ORDERHASACTIVETICKETS = 1
        else
            set @ORDERHASACTIVETICKETS = 0

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

        if @ISPOSTED = 1 and exists (select 1 from dbo.ADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1)
            set @HASUNPOSTEDADJUSTMENT = 1;
        else
            set @HASUNPOSTEDADJUSTMENT = 0;


        -- MDC - Bug 207553 copied from the revenue transaction page expression form.
        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                        

        -- check if all tickets in this order have been applied to a membership. If yes, set the refund status to hide the refund task
        if exists 
        (
            select 
                1 
            from 
                dbo.TICKET 
            inner join 
                dbo.SALESORDERITEMTICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
            inner join 
                dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
            where 
                SALESORDERITEM.SALESORDERID = @ID
                and TICKET.APPLIEDTOMEMBERSHIPSALESORDERID is not null
                and @REFUNDSTATUS <> 2
                and not exists 
                (
                    select 
                        1 
                    from 
                        dbo.TICKET 
                    inner join 
                        dbo.SALESORDERITEMTICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
                    inner join 
                        dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                    where 
                        SALESORDERITEM.SALESORDERID = @ID
                        and SALESORDERITEM.TYPECODE  = 0 
                        and TICKET.APPLIEDTOMEMBERSHIPSALESORDERID is null
                )
        )
            set @REFUNDSTATUS = 2;

    end

    set @EMAILTICKETSAVAILABLE = 0
    select @EMAILTICKETSAVAILABLE = 1
    from dbo.SALESORDER
        inner join dbo.DELIVERYMETHOD on DELIVERYMETHOD.ID = SALESORDER.DELIVERYMETHODID 
        inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEM.ID
    where DELIVERYMETHOD.PRINTCODE = 2
        and DELIVERYMETHOD.ISACTIVE = 1
        and TICKET.STATUSCODE in (0,1)
        and SALESORDER.ID = @ID

return 0;