USP_DATAFORMTEMPLATE_VIEW_ADVANCESALES

The load procedure used by the view dataform template "Advance sales view data 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.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SALESORDERID	uniqueidentifier	INOUT	SALESORDERID
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ORDERSTATUSCODE	tinyint	INOUT	Order status
@DELIVERYMETHODTEXT	nvarchar(100)	INOUT	Method
@RECIPIENTNAME	nvarchar(154)	INOUT	Recipient
@ITEMS	xml	INOUT	ITEMS
@SUBTOTAL	money	INOUT	Subtotal
@TAXES	money	INOUT	Taxes
@TOTAL	money	INOUT	Total
@TENDERED	money	INOUT	Tendered
@CHANGEDUE	money	INOUT	Change due
@BALANCE	money	INOUT	Balance
@AVAILABLEPAYMENTMETHODS	xml	INOUT	AVAILABLEPAYMENTMETHODS
@DELIVERYADDRESS	nvarchar(300)	INOUT	DELIVERYADDRESS
@DELIVERYPHONENUMBER	nvarchar(100)	INOUT	DELIVERYPHONENUMBER
@DELIVERYEMAILADDRESS	UDT_EMAILADDRESS	INOUT	DELIVERYEMAILADDRESS
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@MEMBERID	uniqueidentifier	INOUT	MEMBERID
@ORDERNUMBER	nvarchar(10)	INOUT	ORDERNUMBER
@UNRESOLVEDORDERNOTEID	uniqueidentifier	INOUT	Unresolved order sales order note ID
@PATRONHASMIDTERMUPGRADE	bit	INOUT	PATRONHASMIDTERMUPGRADE
@CONSTITUENCYORDER	xml	INOUT	CONSTITUENCYORDER
@SALESMETHODTYPECODE	tinyint	INOUT
@MEMBERSHIPID	uniqueidentifier	INOUT
@ISTAXEXEMPT	bit	INOUT

Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADVANCESALES
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SALESORDERID uniqueidentifier = null output,
                @CONSTITUENTID uniqueidentifier = null output,
                @ORDERSTATUSCODE tinyint = null output,
                @DELIVERYMETHODTEXT nvarchar(100) = null output,
                @RECIPIENTNAME nvarchar(154) = null output,
                @ITEMS xml = null output,
                @SUBTOTAL money = null output,
                @TAXES money = null output,
                @TOTAL money = null output,
                @TENDERED money = null output,
                @CHANGEDUE money = null output,
                @BALANCE money = null output,
                @AVAILABLEPAYMENTMETHODS xml = null output,
                @DELIVERYADDRESS nvarchar(300) = null output,
                @DELIVERYPHONENUMBER nvarchar(100) = null output,
                @DELIVERYEMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                @MATCHFINDERONLINERECORDID int = null output,
                @MEMBERID uniqueidentifier = null output,
                @ORDERNUMBER nvarchar(10) = null output,
                @UNRESOLVEDORDERNOTEID uniqueidentifier = null output,
                @PATRONHASMIDTERMUPGRADE bit = null output,
                @CONSTITUENCYORDER xml = null output,
                @SALESMETHODTYPECODE tinyint = null output,
                @MEMBERSHIPID uniqueidentifier = null output,
                @ISTAXEXEMPT bit = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;

                set @MATCHFINDERONLINERECORDID = 0;

                declare @DELIVERYMETHODID uniqueidentifier;

                declare @DELIVERYADDRESSID uniqueidentifier;
                declare @DELIVERYPHONENUMBERID uniqueidentifier;
                declare @DELIVERYEMAILADDRESSID uniqueidentifier;

                declare @RECIPIENTID uniqueidentifier;

                select 
                    @DATALOADED = 1,
                    @CONSTITUENTID = CONSTITUENTID,
                    @ORDERSTATUSCODE = STATUSCODE,
                    @DELIVERYMETHODID = DELIVERYMETHODID,
                    @RECIPIENTID = RECIPIENTID,
                    @DELIVERYADDRESSID = ADDRESSID,
                    @DELIVERYPHONENUMBERID = PHONEID,
                    @DELIVERYEMAILADDRESS = EMAILADDRESSID,
                    @ORDERNUMBER = SEQUENCEID,
                    @SALESMETHODTYPECODE = SALESMETHODTYPECODE
                from dbo.SALESORDER
                where ID = @ID

                if @SALESMETHODTYPECODE = 3
                begin
                    raiserror('You cannot open a group sales order from here.  Please select the Sales Order Page to open this record or create a Group Sales Reservations query for other browsing options.', 13, 1);
                    return 0;
                end

                declare @CHANGEAGENTID uniqueidentifier
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

                if @ORDERSTATUSCODE = 6
                    exec dbo.USP_RESERVEDORDERRECALCULATE_UPDATE @ID, @CHANGEAGENTID;

                if @SALESMETHODTYPECODE = 2
                begin
                    select @UNRESOLVEDORDERNOTEID = [ID]
                    from dbo.[SALESORDERNOTE]
                    where [SALESORDERID] = @ID

                    --Set delivery method if it has not been
                    if @DELIVERYMETHODID is null and @ORDERSTATUSCODE = 7
                    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

                        set @DELIVERYMETHODID = @DEFAULTDELIVERYMETHODID
                    end
                end

                set @DELIVERYMETHODTEXT = (select NAME from dbo.DELIVERYMETHOD where ID = @DELIVERYMETHODID);

                set @DELIVERYADDRESS = (select DESCRIPTION from dbo.ADDRESS where ID = @DELIVERYADDRESSID);
                set @DELIVERYPHONENUMBER = (select NUMBER from dbo.PHONE where ID = @DELIVERYPHONENUMBERID);
                set @DELIVERYEMAILADDRESS = (select EMAILADDRESS from dbo.EMAILADDRESS where ID = @DELIVERYEMAILADDRESSID);

                set @RECIPIENTNAME = (select NAME from dbo.CONSTITUENT where ID = @RECIPIENTID);

                exec dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESORDER_2 
                    @ID, 
                    null, 
                    @ITEMS output, 
                    @SUBTOTAL output, 
                    @TAXES output, 
                    @TOTAL output, 
                    @TENDERED output, 
                    @CHANGEDUE output, 
                    @BALANCE output,
                    @SALESORDERID output,
                    @AVAILABLEPAYMENTMETHODS output,
                    @CONSTITUENTID output,
                    @ISTAXEXEMPT = @ISTAXEXEMPT output;

                set @PATRONHASMIDTERMUPGRADE = dbo.UFN_SALESORDER_PATRONHASUPGRADE(@ID);

                set @CONSTITUENCYORDER = (
                    select 
                        V_CONSTITUENCYDISPLAYORDER.DESCRIPTION,
                        V_CONSTITUENCYDISPLAYORDER.SEQUENCE,
                        V_CONSTITUENCYDISPLAYORDER.ISSYSTEM
                    from
                        dbo.V_CONSTITUENCYDISPLAYORDER
                        inner join dbo.CONSTITUENCYDEFINITION
                            on CONSTITUENCYDEFINITION.ID = V_CONSTITUENCYDISPLAYORDER.ID
                    where
                        CONSTITUENCYDEFINITION.ISACTIVE = 1
                    order by
                        V_CONSTITUENCYDISPLAYORDER.SEQUENCE
                    for xml raw ('ITEM'), type, elements, root('CONSTITUENCYORDER'), binary base64
                )

                return 0;