USP_DATAFORMTEMPLATE_VIEW_RESERVATIONDETAILPAGEDATA

The load procedure used by the view dataform template "Reservation Detail Page Data 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.
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ORDERNUMBER	int	INOUT	ORDERNUMBER
@CONSTITUENTNAME	nvarchar(154)	INOUT	CONSTITUENTNAME
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@AMOUNTDUE	money	INOUT	AMOUNTDUE
@DEPOSITREQUIRED	bit	INOUT	DEPOSITREQUIRED
@FINALCOUNTREQUIRED	bit	INOUT	FINALCOUNTREQUIRED
@CONTRACTREQUIRED	bit	INOUT	CONTRACTREQUIRED
@ORDERSTATUSCODE	tinyint	INOUT	ORDERSTATUSCODE
@ORDERTICKETTOTAL	money	INOUT	ORDERTICKETTOTAL
@ORDERHASRESOURCE	bit	INOUT	ORDERHASRESOURCE
@ORDERRESOURCETOTAL	money	INOUT	ORDERRESOURCETOTAL
@ORDERHASSTAFFRESOURCE	bit	INOUT	ORDERHASSTAFFRESOURCE
@ORDERSTAFFRESOURCETOTAL	money	INOUT	ORDERSTAFFRESOURCETOTAL
@ORDERHASADJUSTMENT	bit	INOUT	ORDERHASADJUSTMENT
@ORDERADJUSTMENTTOTAL	money	INOUT	ORDERADJUSTMENTTOTAL
@CANCOMPLETE	bit	INOUT	CANCOMPLETE
@PRICINGCODE	tinyint	INOUT	PRICINGCODE
@ORDERBALANCESENT	bit	INOUT	ORDERBALANCESENT
@HASUNSCHEDULEDITEMS	bit	INOUT	HASUNSCHEDULEDITEMS
@CONTACTCONSTITUENTID	uniqueidentifier	INOUT	CONTACTCONSTITUENTID
@CONTACTNAME	nvarchar(154)	INOUT	CONTACTNAME
@ORDERPAYMENTCOUNT	tinyint	INOUT	ORDERPAYMENTCOUNT
@ORDERHASADDITIONALTICKETS	bit	INOUT	ORDERHASADDITIONALTICKETS
@HASUNASSIGNEDSTAFFRESOURCES	bit	INOUT	HASUNASSIGNEDSTAFFRESOURCES
@SECURITYDEPOSITREQUIRED	bit	INOUT	SECURITYDEPOSITREQUIRED
@SECURITYDEPOSITDUE	money	INOUT	SECURITYDEPOSITDUE
@ORDERFLATRATETOTAL	money	INOUT	ORDERFLATRATETOTAL
@ORDERADDITIONALTICKETSTOTAL	money	INOUT	ORDERADDITIONALTICKETSTOTAL
@ORDERHASLOCATION	bit	INOUT	ORDERHASLOCATION
@ORDERLOCATIONTOTAL	money	INOUT	ORDERLOCATIONTOTAL
@DEPOSITTYPECODE	int	INOUT
@SECURITYDEPOSITTYPECODE	int	INOUT
@DEPOSITREQUIREDDEFAULT	bit	INOUT
@SECURITYDEPOSITREQUIREDDEFAULT	bit	INOUT
@RESERVATIONNAME	nvarchar(100)	INOUT
@ISREFUNDABLE	bit	INOUT
@ISSECURITYDEPOSITEREFUNDABLE	bit	INOUT
@TAXES	money	INOUT
@ISTAXEXEMPT	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RESERVATIONDETAILPAGEDATA
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ORDERNUMBER int = null output,
    @CONSTITUENTNAME nvarchar(154) = null output,
    @ISORGANIZATION bit = null output,
    @AMOUNTDUE money = null output,
    @DEPOSITREQUIRED bit = null output,
    @FINALCOUNTREQUIRED bit = null output,
    @CONTRACTREQUIRED bit = null output,
    @ORDERSTATUSCODE tinyint = null output,
    @ORDERTICKETTOTAL money = null output,
    @ORDERHASRESOURCE bit = null output,
    @ORDERRESOURCETOTAL money = null output,
    @ORDERHASSTAFFRESOURCE bit = null output,
    @ORDERSTAFFRESOURCETOTAL money = null output,
    @ORDERHASADJUSTMENT bit = null output,
    @ORDERADJUSTMENTTOTAL money = null output,
    @CANCOMPLETE bit = null output,
    @PRICINGCODE tinyint = null output,
    @ORDERBALANCESENT bit = null output,
    @HASUNSCHEDULEDITEMS bit = null output,
    @CONTACTCONSTITUENTID uniqueidentifier = null output,
    @CONTACTNAME nvarchar(154) = null output,
    @ORDERPAYMENTCOUNT tinyint = null output,
    @ORDERHASADDITIONALTICKETS bit = null output,
    @HASUNASSIGNEDSTAFFRESOURCES bit = null output,
    @SECURITYDEPOSITREQUIRED bit = null output,
    @SECURITYDEPOSITDUE money = null output,
    @ORDERFLATRATETOTAL money = null output,
    @ORDERADDITIONALTICKETSTOTAL money = null output,
    @ORDERHASLOCATION bit = null output,
    @ORDERLOCATIONTOTAL money = null output,
    @DEPOSITTYPECODE int = null output,
    @SECURITYDEPOSITTYPECODE int = null output,
    @DEPOSITREQUIREDDEFAULT bit  = null output,
    @SECURITYDEPOSITREQUIREDDEFAULT bit = null output,
    @RESERVATIONNAME nvarchar(100) = null output,
    @ISREFUNDABLE bit = null output,
    @ISSECURITYDEPOSITEREFUNDABLE bit = null  output,
    @TAXES money = null output,
    @ISTAXEXEMPT bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

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

    select
        @DATALOADED = 1,
        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
        @ORDERNUMBER = SALESORDER.SEQUENCEID,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @CONSTITUENTNAME = CONSTITUENT.NAME,
        @DEPOSITREQUIRED = DEPOSITREQUIRED,
        @FINALCOUNTREQUIRED = FINALCOUNTREQUIRED,
        @CONTRACTREQUIRED = CONTRACTREQUIRED,
        @ORDERSTATUSCODE = [SALESORDER].[STATUSCODE],
        @ORDERTICKETTOTAL = dbo.UFN_SALESORDER_GETTICKETOTAL(@ID),
        @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),
        @ORDERHASADJUSTMENT = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE in (3,4,5))), 0),
        @ORDERADJUSTMENTTOTAL = dbo.UFN_SALESORDER_GETORDERADJUSTMENTTOTAL(@ID),
        @CANCOMPLETE = case when ARRIVALDATE <= @CURRENTDATE and STATUSCODE not in (1, 5)
                then 1
                else 0
            end,
        @ISSECURITYDEPOSITEREFUNDABLE = case when SECURITYDEPOSITSTATUSCODE > 0
                then 1
                else 0
            end,
        @PRICINGCODE = PRICINGCODE,
        @ORDERBALANCESENT = [RESERVATION].[ORDERBALANCESENT],
        @CONTACTCONSTITUENTID = SALESORDER.RECIPIENTID,
        @CONTACTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.RECIPIENTID),
        @ORDERPAYMENTCOUNT = dbo.UFN_RESERVATION_GETPAYMENTCOUNT(@ID),
        @SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITDUE = dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@ID),
        @ORDERHASLOCATION = coalesce((select 1 where exists(select [ID] from dbo.SALESORDERITEM where SALESORDERITEM.SALESORDERID = @ID and SALESORDERITEM.TYPECODE = 7) ), 0),
        @RESERVATIONNAME = RESERVATION.NAME
    from
        dbo.RESERVATION
    inner join
        dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
    inner join
        dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    where
        RESERVATION.ID = @ID;

    if @DATALOADED = 1
    begin
        set @AMOUNTDUE = dbo.UFN_RESERVATION_GETAMOUNTDUE(@ID);

        if exists(select top 1 1
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                    where ITINERARY.RESERVATIONID = @ID and ITINERARYITEM.INVALIDREASONCODE <> 0)
            set @HASUNSCHEDULEDITEMS = 1;
        else
            set @HASUNSCHEDULEDITEMS = 0;

        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 @ORDERHASSTAFFRESOURCE = 1
        begin
            set @HASUNASSIGNEDSTAFFRESOURCES = dbo.UFN_RESERVATION_HASUNASSIGNEDSTAFFRESOURCES(@ID);
        end
        else
        begin
            set @HASUNASSIGNEDSTAFFRESOURCES = 0
        end

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

        select  
            @DEPOSITTYPECODE = DEPOSITTYPECODE, 
            @SECURITYDEPOSITTYPECODE = SECURITYDEPOSITTYPECODE,
            @DEPOSITREQUIREDDEFAULT  = DEPOSITREQUIRED,
            @SECURITYDEPOSITREQUIREDDEFAULT = SECURITYDEPOSITREQUIRED
            from dbo.GROUPSALESDEFAULT

        declare @SALESORDERBALANCE money;
        declare @SALESORDERREFUNDS money;
        declare @SALESORDERAMOUNTPAID money;
        select
            @SALESORDERBALANCE = BALANCE,
            @SALESORDERREFUNDS = REFUNDS,
            @SALESORDERAMOUNTPAID = AMOUNTPAID
        from
            dbo.UFN_SALESORDER_TOTALS(@ID)

        if @ORDERSTATUSCODE = 1
            begin
                if @SALESORDERBALANCE < 0
                    set @ISREFUNDABLE = 1
                else
                    set @ISREFUNDABLE = 0
            end
        else
            begin
                if @SALESORDERAMOUNTPAID > @SALESORDERREFUNDS
                    set @ISREFUNDABLE = 1    
                else
                    set @ISREFUNDABLE = 0
            end

        select @TAXES = TAXES from dbo.UFN_SALESORDER_TOTALS(@ID);
        set @ISTAXEXEMPT = dbo.UFN_SALESORDER_ISTAXEXEMPT(@ID);
    end

    return 0;