USP_DATAFORMTEMPLATE_VIEW_ORDERFEESTAXES

The load procedure used by the view dataform template "Order Fees and Taxes"

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.
@SALESMETHODFEES	xml	INOUT	Sales method fees
@PROGRAMFEES	xml	INOUT	Program fees

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ORDERFEESTAXES
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @SALESMETHODFEES xml = null output,
--                @FEETAXES xml = null output,

                @PROGRAMFEES xml = null output
--                @PROGRAMTAXES xml = null output,


            )
            as
                set nocount on;
                set @DATALOADED = 1;

                declare @SALESMETHODID uniqueidentifier
                select @SALESMETHODID = SALESMETHOD.ID,
                    @DATALOADED = 1
                from dbo.SALESORDER 
                inner join dbo.SALESMETHOD on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE
                where SALESORDER.ID = @ID

                -- Getting sales method fees

                set @SALESMETHODFEES =
                (
                    select
                        FEE.ID as FEEID,
                        SALESMETHODFEE.SALESMETHODID,
                        FEE.AMOUNT,
                        FEE.APPLIESTOCODE,
                        FEE.[PERCENT],
                        FEE.TYPECODE
                    from dbo.SALESMETHODFEE
                    inner join dbo.FEE on FEE.ID = SALESMETHODFEE.FEEID
                    where SALESMETHODFEE.SALESMETHODID = @SALESMETHODID
                    for xml raw ('ITEM'), type, elements, root('SALESMETHODFEES'), BINARY BASE64
                )

                -- Getting program order fees, need to refactor this query

                set @PROGRAMFEES =
                (
                    select 
                        FEE.ID as FEEID,
                        PROGRAMFEE.PROGRAMID,
                        FEE.AMOUNT,
                        FEE.APPLIESTOCODE,
                        FEE.[PERCENT],
                        FEE.TYPECODE
                    from FEE 
                    inner join dbo.PROGRAMFEE on FEE.ID = PROGRAMFEE.FEEID 
                    where PROGRAMFEE.PROGRAMID in 
                        (select PROGRAM.ID 
                        from dbo.PROGRAM 
                        inner join dbo.EVENT on EVENT.PROGRAMID = PROGRAM.ID 
                        inner join dbo.SALESORDERITEMTICKET on EVENT.ID = SALESORDERITEMTICKET.EVENTID 
                        inner join dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                        where SALESORDERITEM.SALESORDERID = @ID)
                    for xml raw ('ITEM'), type, elements, root('PROGRAMFEES'), BINARY BASE64
                )

                return 0;