USP_DATAFORMTEMPLATE_VIEW_FLATTENEDRESERVATIONSUMMARY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@APPUSERDISPLAYNAME	nvarchar(255)	INOUT
@SALESORDERCONSTITUENTID	uniqueidentifier	INOUT
@SALESORDERCONSTITUENTNAME	nvarchar(max)	INOUT
@CONTACTCONSTITUENTID	uniqueidentifier	INOUT
@CONTACTCONSTITUENTNAME	nvarchar(max)	INOUT
@CONTACTEMAILADDRESS	UDT_EMAILADDRESS	INOUT
@CONTACTDONOTEMAIL	bit	INOUT
@CONTACTPHONENUMBER	nvarchar(100)	INOUT
@CONTACTDONOTCALL	bit	INOUT
@CONTACTPHONEISCONFIDENTIAL	bit	INOUT
@RESERVATIONDATEADDED	datetime	INOUT
@TOTALVISITORS	int	INOUT
@SALESORDERID	uniqueidentifier	INOUT
@ORDERNUMBER	int	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FLATTENEDRESERVATIONSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @APPUSERDISPLAYNAME nvarchar(255) = null output,
    @SALESORDERCONSTITUENTID uniqueidentifier = null output,
    @SALESORDERCONSTITUENTNAME nvarchar(max) = null output,
    @CONTACTCONSTITUENTID uniqueidentifier = null output,
    @CONTACTCONSTITUENTNAME nvarchar(max) = null output,
    @CONTACTEMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @CONTACTDONOTEMAIL bit = null output,
    @CONTACTPHONENUMBER nvarchar(100) = null output,
    @CONTACTDONOTCALL bit = null output,
    @CONTACTPHONEISCONFIDENTIAL bit = null output,
    @RESERVATIONDATEADDED datetime = null output,
    @TOTALVISITORS int = null output,
    @SALESORDERID uniqueidentifier = null output,
    @ORDERNUMBER int = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @APPUSERDISPLAYNAME = APPUSER.DISPLAYNAME,
        @SALESORDERCONSTITUENTID = SALESORDER.CONSTITUENTID,
        @SALESORDERCONSTITUENTNAME = SALESORDERORDERCONSTITUENT_NF.NAME,
        @CONTACTCONSTITUENTID = SALESORDER.RECIPIENTID,
        @CONTACTCONSTITUENTNAME = CONTACTCONSTITUENT_NF.NAME,
        @CONTACTEMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @CONTACTDONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
        @CONTACTPHONENUMBER = PHONE.NUMBER,
        @CONTACTDONOTCALL = PHONE.DONOTCALL,
        @CONTACTPHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
        @RESERVATIONDATEADDED = RESERVATION.DATEADDED,
        @TOTALVISITORS = dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID),
        @SALESORDERID = SALESORDER.ID,
        @ORDERNUMBER = SALESORDER.SEQUENCEID
    from dbo.RESERVATION
    inner join dbo.SALESORDER 
        on SALESORDER.ID = RESERVATION.ID
    inner join dbo.APPUSER 
        on APPUSER.ID = SALESORDER.APPUSERID
    outer apply dbo.UFN_SALESORDER_CONTACTRECORDS(RESERVATION.ID) as CONTACTRECORDS
    left outer join dbo.EMAILADDRESS 
        on EMAILADDRESS.ID = CONTACTRECORDS.EMAILADDRESSID
    left outer join dbo.PHONE 
        on PHONE.ID = CONTACTRECORDS.PHONEID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as SALESORDERORDERCONSTITUENT_NF
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.RECIPIENTID) as CONTACTCONSTITUENT_NF
    where
        RESERVATION.ID = @ID;

    return 0;