USP_DATAFORMTEMPLATE_VIEW_ITINERARYDETAILPAGEDATA
The load procedure used by the view dataform template "Itinerary Detail Page Data View"
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. |
| @ARRIVALDATE | datetime | INOUT | ArrivalDate |
| @ARRIVALTIME | UDT_HOURMINUTE | INOUT | ArrivalTime |
| @ATTENDEES | xml | INOUT | Prices |
| @ITINERARYNAME | nvarchar(100) | INOUT | Name |
| @RESERVATIONNAME | nvarchar(100) | INOUT | Name |
| @RESERVATIONID | uniqueidentifier | INOUT | Reservation ID |
| @NUMBEROFDAYS | int | INOUT | Days |
| @COST | money | INOUT | Cost |
| @TRACKNAME | nvarchar(100) | INOUT | Track |
| @RESOURCES | xml | INOUT | RESOURCES |
| @CONFLICTSEXIST | bit | INOUT | Conflicts exist |
| @ORDERSTATUSCODE | tinyint | INOUT | ORDERSTATUSCODE |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ITINERARYDETAILPAGEDATA
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@ARRIVALDATE datetime = null output,
@ARRIVALTIME dbo.UDT_HOURMINUTE = null output,
@ATTENDEES xml = null output,
@ITINERARYNAME nvarchar(100) = null output,
@RESERVATIONNAME nvarchar(100) = null output,
@RESERVATIONID uniqueidentifier = null output,
@NUMBEROFDAYS integer = null output,
@COST money = null output, -- By design, cost is an expression of potential cost - not the sum of the actual sales order items
@TRACKNAME nvarchar(100) = null output,
@RESOURCES xml = null output,
@CONFLICTSEXIST bit = null output,
@ORDERSTATUSCODE tinyint = null output
)
as
set nocount on;
set @DATALOADED = 0;
select @DATALOADED = 1,
@ARRIVALDATE = RESERVATION.ARRIVALDATE,
@ARRIVALTIME = RESERVATION.ARRIVALTIME,
@ITINERARYNAME = ITINERARY.NAME,
@RESERVATIONNAME = RESERVATION.NAME,
@RESERVATIONID = RESERVATION.ID,
@TRACKNAME = TRACK.NAME,
@ORDERSTATUSCODE = SALESORDER.STATUSCODE
from dbo.ITINERARY
inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
inner join dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
left join dbo.TRACK on
TRACK.ID = ITINERARY.TRACKID
where ITINERARY.ID = @ID;
if @DATALOADED= 1
begin
set @ATTENDEES =
(
select QUANTITY,
dbo.UFN_PRICETYPECODE_GETDESCRIPTION(PRICETYPECODEID) as PRICETYPE,
PRICETYPECODEID as PRICETYPECODEID,
SEQUENCE
from dbo.ITINERARYATTENDEE
where ITINERARYATTENDEE.ITINERARYID = @ID
for xml raw('ITEM'),type,elements,root('ATTENDEES'),binary base64
);
declare @LASTDATE datetime;
select top 1 @LASTDATE = ENDDATE
from dbo.ITINERARYITEM
where ITINERARYITEM.ITINERARYID = @ID
order by ENDDATE desc
set @NUMBEROFDAYS = datediff(day, @ARRIVALDATE, @LASTDATE);
-- Make sure to determine if we have an items that are conflicts
if ((select count(ITINERARYITEM.ID)
from dbo.ITINERARYITEM
where (ITINERARYITEM.ITINERARYID = @ID) and
(ITINERARYITEM.INVALIDREASONCODE = 10)) > 0)
set @CONFLICTSEXIST = 1;
else
set @CONFLICTSEXIST = 0;
set @RESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ID);
with DISTINCTITEMS_CTE as
(
select
PROGRAMID,
EVENTID,
ITINERARYID
from dbo.ITINERARYITEM
where ITINERARYID = @ID
group by PROGRAMID, EVENTID, STARTDATE, ITINERARYID
)
select @COST = isnull(
sum(
case SALESORDERITEM.PRICINGSTRUCTURECODE
when 1 then 0
else
T.items.value('(QUANTITY)[1]','integer') * SALESORDERITEM.PRICE
end
), 0
)
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = SALESORDERITEM.SALESORDERID
inner join DISTINCTITEMS_CTE DI on DI.ITINERARYID = ITINERARY.ID
inner join @ATTENDEES.nodes('/ATTENDEES/ITEM') T(items) on T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') = SALESORDERITEMTICKET.PRICETYPECODEID
where SALESORDERITEM.SALESORDERID = @RESERVATIONID and
((DI.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and SALESORDERITEMTICKET.EVENTID is null) or DI.EVENTID = SALESORDERITEMTICKET.EVENTID)
declare @FEETABLE table
(
FEEAMOUNT decimal(10,3),
QUANTITY integer
);
with DISTINCTITEMS_CTE as
(
select
PROGRAMID,
EVENTID,
ITINERARYID
from dbo.ITINERARYITEM
where ITINERARYID = @ID
group by PROGRAMID, EVENTID, STARTDATE, ITINERARYID
)
insert into @FEETABLE
(
FEEAMOUNT,
QUANTITY
)
select
case SALESORDERITEM.PRICINGSTRUCTURECODE
when 1 then 0
else
case FEE.TYPECODE
when 0 then FEE.AMOUNT
else (FEE.[PERCENT] * .01 * SALESORDERITEMTICKET.PRICE)
end
end as FEEAMOUNT,
T.items.value('(QUANTITY)[1]', 'integer') as QUANTITY
from dbo.SALESORDERITEMFEE
inner join dbo.FEE on SALESORDERITEMFEE.FEEID = FEE.ID
inner join dbo.SALESORDERITEM on SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.RESERVATION on RESERVATION.ID = SALESORDERITEM.SALESORDERID
inner join dbo.ITINERARY ON ITINERARY.RESERVATIONID = RESERVATION.ID
inner join DISTINCTITEMS_CTE DI on DI.ITINERARYID = ITINERARY.ID
inner join @ATTENDEES.nodes('/ATTENDEES/ITEM') T(items) on T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') = SALESORDERITEMTICKET.PRICETYPECODEID
where SALESORDERITEMFEE.APPLIESTOCODE = 1
and SALESORDERITEM.SALESORDERID = @RESERVATIONID
and ITINERARY.ID = @ID
and ((DI.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and SALESORDERITEMTICKET.EVENTID is null) or DI.EVENTID = SALESORDERITEMTICKET.EVENTID)
declare @FEES money
select @FEES = isnull(SUM(FEEAMOUNT * QUANTITY), 0)
from @FEETABLE
-- Add resource costs to the total cost
declare @RESOURCECOST money
declare @ATTENDEECOUNT as integer
select @ATTENDEECOUNT = COALESCE((
select sum(QUANTITY)
from dbo.ITINERARYATTENDEE
where ITINERARYID = @ID),0)
declare @STARTDATETIME as datetime
select @STARTDATETIME = dbo.UFN_ITINERARY_STARTDATETIME(@ID)
declare @ENDDATETIME as datetime
select @ENDDATETIME = dbo.UFN_ITINERARY_ENDDATETIME(@ID)
declare @DURATION as decimal(20,4)
if (@ENDDATETIME is null or @STARTDATETIME is null)
set @DURATION = 0
else
select @DURATION = DATEDIFF(s, @STARTDATETIME, @ENDDATETIME) / (3600.0)
select @RESOURCECOST = COALESCE(sum(TOTAL),0)
from (
select (case dbo.RESOURCE.ISPERTICKETITEM
when 0 then
case dbo.ITINERARYRESOURCE.PRICINGSTRUCTURECODE
when 2 then @DURATION * dbo.ITINERARYRESOURCE.QUANTITYNEEDED
else dbo.ITINERARYRESOURCE.QUANTITYNEEDED
end
else
case dbo.ITINERARYRESOURCE.PRICINGSTRUCTURECODE
when 2 then @DURATION * @ATTENDEECOUNT * ITINERARYRESOURCE.PERTICKETQUANTITY
else @ATTENDEECOUNT * ITINERARYRESOURCE.PERTICKETQUANTITY
end
end) *
(case dbo.ITINERARYRESOURCE.PRICINGSTRUCTURECODE
when 0 then 0 /* No Charge */
else
case RESERVATION.PRICINGCODE
when 1 then /* Rate Scale */
case COALESCE(RESERVATIONRATESCALE.INCLUDEALLRESOURCES, 0)
when 1 then 0 /* If all resources are included, cost is 0 */
else
case when RESERVATIONRATESCALERESOURCE.ID is null
then dbo.ITINERARYRESOURCE.PRICE
else 0
end
end
else
dbo.ITINERARYRESOURCE.PRICE
end
end) as TOTAL
from dbo.ITINERARYRESOURCE
inner join dbo.SALESORDERITEMITINERARYRESOURCE on ITINERARYRESOURCE.ID = SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID
inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
left join dbo.RESERVATIONRATESCALERESOURCE on RESERVATIONRATESCALE.ID = RESERVATIONRATESCALERESOURCE.RESERVATIONRATESCALEID and ITINERARYRESOURCE.RESOURCEID = RESERVATIONRATESCALERESOURCE.RESOURCEID
where dbo.ITINERARYRESOURCE.ITINERARYID = @ID
union all
select (case dbo.ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then COALESCE(DATEDIFF(s, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID)),0) / (3600.0) * dbo.ITINERARYSTAFFRESOURCE.QUANTITYNEEDED
else dbo.ITINERARYSTAFFRESOURCE.QUANTITYNEEDED
end) *
(case dbo.ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 0 then 0 /* No Charge */
else
case RESERVATION.PRICINGCODE
when 1 then /* Rate Scale */
case COALESCE(RESERVATIONRATESCALE.INCLUDEALLSTAFFRESOURCES, 0)
when 1 then 0 /* If all resources are included, cost is 0 */
else
case when RESERVATIONRATESCALESTAFFRESOURCE.ID is null
then dbo.ITINERARYSTAFFRESOURCE.PRICE
else 0
end
end
else
dbo.ITINERARYSTAFFRESOURCE.PRICE
end
end) as TOTAL
from dbo.ITINERARYSTAFFRESOURCE
inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on ITINERARYSTAFFRESOURCE.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID
inner join dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
left join dbo.RESERVATIONRATESCALESTAFFRESOURCE on RESERVATIONRATESCALE.ID = RESERVATIONRATESCALESTAFFRESOURCE.RESERVATIONRATESCALEID and ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = RESERVATIONRATESCALESTAFFRESOURCE.VOLUNTEERTYPEID
where dbo.ITINERARYSTAFFRESOURCE.ITINERARYID = @ID
union all
select (case dbo.RESOURCE.ISPERTICKETITEM
when 0 then
case dbo.ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
when 2 then isnull(DATEDIFF(s, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME),0) / (3600.0) * dbo.ITINERARYITEMRESOURCE.QUANTITYNEEDED
else dbo.ITINERARYITEMRESOURCE.QUANTITYNEEDED
end
else
case dbo.ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
when 2 then isnull(DATEDIFF(s, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME),0) / (3600.0) * @ATTENDEECOUNT * ITINERARYITEMRESOURCE.PERTICKETQUANTITY
else @ATTENDEECOUNT * ITINERARYITEMRESOURCE.PERTICKETQUANTITY
end
end) *
(case dbo.ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
when 0 then 0 /* No Charge */
else
case RESERVATION.PRICINGCODE
when 1 then /* Rate Scale */
case isnull(RESERVATIONRATESCALE.INCLUDEALLRESOURCES, 0)
when 1 then 0 /* If all resources are included, cost is 0 */
else
case when RESERVATIONRATESCALERESOURCE.ID is null
then dbo.ITINERARYITEMRESOURCE.PRICE
else 0
end
end
else
dbo.ITINERARYITEMRESOURCE.PRICE
end
end) as TOTAL
from dbo.ITINERARYITEMRESOURCE
inner join dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE on ITINERARYITEMRESOURCE.ID = SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID
inner join dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
left join dbo.RESERVATIONRATESCALERESOURCE on RESERVATIONRATESCALE.ID = RESERVATIONRATESCALERESOURCE.RESERVATIONRATESCALEID and ITINERARYITEMRESOURCE.RESOURCEID = RESERVATIONRATESCALERESOURCE.RESOURCEID
where dbo.ITINERARYITEM.ITINERARYID = @ID
and ITINERARYITEM.INVALIDREASONCODE = 0
union all
select (case dbo.ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then isnull(DATEDIFF(s, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME),0) / (3600.0) * dbo.ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED
else dbo.ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED
end) *
(case dbo.ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 0 then 0 /* No Charge */
else
case RESERVATION.PRICINGCODE
when 1 then /* Rate Scale */
case isnull(RESERVATIONRATESCALE.INCLUDEALLSTAFFRESOURCES, 0)
when 1 then 0 /* If all resources are included, cost is 0 */
else
case when RESERVATIONRATESCALESTAFFRESOURCE.ID is null
then dbo.ITINERARYITEMSTAFFRESOURCE.PRICE
else 0
end
end
else
dbo.ITINERARYITEMSTAFFRESOURCE.PRICE
end
end) as TOTAL
from dbo.ITINERARYITEMSTAFFRESOURCE
inner join dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on ITINERARYITEMSTAFFRESOURCE.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID
inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
left join dbo.RESERVATIONRATESCALESTAFFRESOURCE on RESERVATIONRATESCALE.ID = RESERVATIONRATESCALESTAFFRESOURCE.RESERVATIONRATESCALEID and ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = RESERVATIONRATESCALESTAFFRESOURCE.VOLUNTEERTYPEID
where dbo.ITINERARYITEM.ITINERARYID = @ID
and ITINERARYITEM.INVALIDREASONCODE = 0
) as T
-- Add location costs to the total cost
declare @LOCATIONCOST money
select
@LOCATIONCOST = coalesce(sum(TOTAL),0)
from dbo.SALESORDERITEM
inner join dbo.ITINERARYITEMLOCATION on
ITINERARYITEMLOCATION.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.ITINERARYITEM on
ITINERARYITEMLOCATION.ID = ITINERARYITEM.ID
where
ITINERARYITEM.ITINERARYID = @ID and
SALESORDERITEM.SALESORDERID = @RESERVATIONID and
SALESORDERITEM.TYPECODE = 7
set @COST = @COST + @FEES + @RESOURCECOST + @LOCATIONCOST
end
return 0;