USP_DATAFORMTEMPLATE_VIEW_PROGRAM_RATESCALE

The load procedure used by the view dataform template "Program Flat Rate 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.
@RATESCALEID	uniqueidentifier	INOUT	Rate Scale ID
@RATESCALENAME	nvarchar(100)	INOUT	Rate scale
@PRICES	xml	INOUT	Prices
@USEPERTICKETAFTERMAX	bit	INOUT	Uses per ticket pricing after max
@SECURITYDEPOSITREQUIRED	bit	INOUT	Security deposit
@SECURITYDEPOSITTIMEVALUE	smallint	INOUT	Security deposit due
@SECURITYDEPOSITTIME	nvarchar(50)	INOUT	SECURITYDEPOSITTIME
@SECURITYDEPOSITTIMECODE	tinyint	INOUT	SECURITYDEPOSITTIMECODE
@SECURITYDEPOSITTYPE	nvarchar(50)	INOUT	Security deposit amount
@SECURITYDEPOSITTYPECODE	tinyint	INOUT	Security deposit amount
@SECURITYDEPOSITAMOUNT	money	INOUT	Security deposit amount
@SECURITYDEPOSITPERCENT	decimal(5, 2)	INOUT	Security deposit amount
@DEFAULTPRICINGSTRUCTURE	nvarchar(50)	INOUT	DEFAULTPRICINGSTRUCTURE
@DEFAULTPRICINGSTRUCTURECODE	tinyint	INOUT	DEFAULTPRICINGSTRUCTURECODE

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROGRAM_RATESCALE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @RATESCALEID uniqueidentifier = null output,
    @RATESCALENAME nvarchar(100) = null output,
    @PRICES xml = null output,
    @USEPERTICKETAFTERMAX bit = null output,
    @SECURITYDEPOSITREQUIRED bit = null output,
    @SECURITYDEPOSITTIMEVALUE smallint = null output,
    @SECURITYDEPOSITTIME nvarchar(50) = null output,
    @SECURITYDEPOSITTIMECODE tinyint = null output,
    @SECURITYDEPOSITTYPE nvarchar(50) = null output,
    @SECURITYDEPOSITTYPECODE tinyint = null output,
    @SECURITYDEPOSITAMOUNT money = null output,
    @SECURITYDEPOSITPERCENT decimal(5,2) = null output,
    @DEFAULTPRICINGSTRUCTURE nvarchar(50) = null output,
    @DEFAULTPRICINGSTRUCTURECODE tinyint = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select @DATALOADED = 1,
           @RATESCALEID = DEFAULTRATESCALEID,
           @DEFAULTPRICINGSTRUCTURE = DEFAULTPRICINGSTRUCTURE,
           @DEFAULTPRICINGSTRUCTURECODE = DEFAULTPRICINGSTRUCTURECODE
    from dbo.PROGRAM
    where ID = @ID

    if not (@RATESCALEID is null)
    begin
        set @PRICES = (
                        select
                            case
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM = -1 then
                                    ''
                                when (GROUPMINIMUM = 0 and GROUPMAXIMUM <> -1) or (GROUPMINIMUM = GROUPMAXIMUM) then
                                    cast(GROUPMAXIMUM as nvarchar(6))
                                when (GROUPMINIMUM <> 0 and GROUPMAXIMUM = -1) Then
                                    cast(GROUPMINIMUM as nvarchar(6))
                                else
                                    cast(GROUPMINIMUM as nvarchar(6)) + ' - ' + cast(GROUPMAXIMUM as nvarchar(6))
                                end as SIZE,
                            AMOUNT,
                            case
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM = -1 then 0
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM <> -1 then 1
                                when GROUPMINIMUM <> 0 and GROUPMAXIMUM = -1 then 2
                                else 3
                                end as FIELDTYPE,
                            cast(1 as nvarchar(1)) as DISPLAYAMOUNT
                        from dbo.RATESCALEPRICE
                        where RATESCALEID = @RATESCALEID
                        order by GROUPMINIMUM                
                        for xml raw('ITEM'),type,elements,root('PRICES'),binary base64
                    )

        select 
            @RATESCALENAME = NAME,
            @USEPERTICKETAFTERMAX = USEPERTICKETAFTERMAX
        from dbo.RATESCALE 
        where ID = @RATESCALEID
    end

    select 
        @SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITTIMEVALUE = SECURITYDEPOSITTIMEVALUE,
        @SECURITYDEPOSITTIME = SECURITYDEPOSITTIME,
        @SECURITYDEPOSITTIMECODE = SECURITYDEPOSITTIMECODE,
        @SECURITYDEPOSITTYPE = SECURITYDEPOSITTYPE,
        @SECURITYDEPOSITTYPECODE = SECURITYDEPOSITTYPECODE,
        @SECURITYDEPOSITAMOUNT = SECURITYDEPOSITAMOUNT,
        @SECURITYDEPOSITPERCENT = SECURITYDEPOSITPERCENT
    from dbo.GROUPSALESDEFAULT

    return 0;