USP_DATAFORMTEMPLATE_VIEW_GROUPSALESSETUPGUIDE

The load procedure used by the view dataform template "Group Sales Setup Guide View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PRICETYPECOUNT	int	INOUT	price types available
@ISSALESMETHODSETUP	bit	INOUT	No required information missing
@PRICELISTCOUNT	int	INOUT	price lists available for group sales
@EVENTLOCATIONCOUNT	int	INOUT	locations available
@SUPPLYRESOURCECOUNT	int	INOUT	supply/equipment resources available
@STAFFRESOURCECOUNT	int	INOUT	staff resources available
@GROUPSALESGROUPTYPECODECOUNT	int	INOUT	group types available
@GROUPTYPERESOURCEREQUIREMENTCOUNT	int	INOUT	resource requirements created
@DAILYADMISSIONPROGRAMCOUNT	int	INOUT	daily admission programs available for group sales
@SCHEDULEDPROGRAMCOUNT	int	INOUT	scheduled programs available for group sales
@PROGRAMEVENTCOUNT	int	INOUT	events scheduled for group sales
@TRACKCOUNT	int	INOUT	tracks available
@DEPOSITREQUIRED	bit	INOUT	Deposit not required
@DEPOSITTIMEVALUE	smallint	INOUT	Deposit due
@DEPOSITTIMECODE	tinyint	INOUT	DEPOSITTIMECODE
@CONTRACTREQUIRED	bit	INOUT	Contract not required
@CONTRACTTIMEVALUE	smallint	INOUT	Contract due
@CONTRACTTIMECODE	tinyint	INOUT	CONTRACTTIMECODE
@FINALCOUNTREQUIRED	bit	INOUT	Final count not required
@FINALCOUNTTIMEVALUE	smallint	INOUT	Final count due
@FINALCOUNTTIMECODE	tinyint	INOUT	FINALCOUNTTIMECODE
@FINALTIMEVALUE	smallint	INOUT	Order balance due
@FINALTIMECODE	tinyint	INOUT	FINALTIMECODE
@RATESCALECOUNT	int	INOUT	rate scales available
@MAXIMUMCAPACITY	int	INOUT	Capacity per day is
@CONTRACTTEMPLATECOUNT	int	INOUT	contract templates available
@SECURITYDEPOSITREQUIRED	bit	INOUT	Security deposit not required
@SECURITYDEPOSITTIMEVALUE	smallint	INOUT	Security deposit due
@SECURITYDEPOSITTIMECODE	tinyint	INOUT	SECURITYDEPOSITTIMECODE
@GROUPSALESDISCOUNTCOUNT	int	INOUT	discounts available for group sales

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GROUPSALESSETUPGUIDE
(
    @DATALOADED bit = 0 output,
    @PRICETYPECOUNT int = null output,
    @ISSALESMETHODSETUP bit = null output, 
    @PRICELISTCOUNT int = null output,
    @EVENTLOCATIONCOUNT int = null output,
    @SUPPLYRESOURCECOUNT int = null output,
    @STAFFRESOURCECOUNT int = null output,
    @GROUPSALESGROUPTYPECODECOUNT int = null output,
    @GROUPTYPERESOURCEREQUIREMENTCOUNT int = null output,
    @DAILYADMISSIONPROGRAMCOUNT int = null output,
    @SCHEDULEDPROGRAMCOUNT int = null output,
    @PROGRAMEVENTCOUNT int = null output,
    @TRACKCOUNT int = null output,
    @DEPOSITREQUIRED bit = null output,
    @DEPOSITTIMEVALUE smallint = null output,
    @DEPOSITTIMECODE tinyint = null output,
    @CONTRACTREQUIRED bit = null output,
    @CONTRACTTIMEVALUE smallint = null output,
    @CONTRACTTIMECODE tinyint = null output,
    @FINALCOUNTREQUIRED bit = null output,
    @FINALCOUNTTIMEVALUE smallint = null output,
    @FINALCOUNTTIMECODE tinyint = null output,
    @FINALTIMEVALUE smallint = null output,
    @FINALTIMECODE tinyint = null output,
    @RATESCALECOUNT int = null output,
    @MAXIMUMCAPACITY int = null output,
    @CONTRACTTEMPLATECOUNT int = null output,
    @SECURITYDEPOSITREQUIRED bit = null output,
    @SECURITYDEPOSITTIMEVALUE smallint = null output,
    @SECURITYDEPOSITTIMECODE tinyint = null output,
    @GROUPSALESDISCOUNTCOUNT int = null output
)
as
    set nocount on;

    set @DATALOADED = 1;

    select @PRICETYPECOUNT = count(*) from dbo.PRICETYPECODE where ACTIVE = 1;

    declare @GROUPSALESSALESMETHODID uniqueidentifier;
    select @GROUPSALESSALESMETHODID = ID from dbo.SALESMETHOD where TYPECODE = 3 and ISACTIVE = 1;

    declare @AVAILABLEPRICETYPES table (ID uniqueidentifier not null);

    insert into @AVAILABLEPRICETYPES (ID)
    select ID from dbo.UFN_SALESMETHOD_ALLOWEDPRICETYPES(3, 0);

    if exists(select top 1 1 from dbo.SALESMETHODPAYMENTMETHOD where SALESMETHODID = @GROUPSALESSALESMETHODID)
        and exists(
            select top 1 1 from dbo.PRICETYPECODE where ACTIVE = 1
            and ID in (select id from @AVAILABLEPRICETYPES)
        )
            set @ISSALESMETHODSETUP = 1;
    else
        set @ISSALESMETHODSETUP = 0;

    select
        @PRICELISTCOUNT = count(*)
    from
        dbo.PRICELIST
    where
        ID in (
            select
                PRICELISTID
            from
                dbo.PRICE
            where
                PRICETYPECODEID in (select ID from @AVAILABLEPRICETYPES)
        ) and
        ISACTIVE = 1;

    select @EVENTLOCATIONCOUNT = count(*) from dbo.EVENTLOCATION;
    select @SUPPLYRESOURCECOUNT = count(*) from dbo.RESOURCE where ISACTIVE = 1;
    select @STAFFRESOURCECOUNT = count(*) from dbo.VOLUNTEERTYPE;
    select @GROUPSALESGROUPTYPECODECOUNT = count(*) from dbo.GROUPSALESGROUPTYPECODE where ACTIVE = 1;

    select
        @GROUPTYPERESOURCEREQUIREMENTCOUNT = count(*)
    from
        dbo.GROUPSALESGROUPTYPECODE
    where
        ID in (select GROUPSALESGROUPTYPECODEID from dbo.GROUPTYPEREQUIREDRESOURCE)
        or ID in (select GROUPSALESGROUPTYPECODEID from dbo.GROUPTYPEREQUIREDSTAFFRESOURCE);

    select
        @DAILYADMISSIONPROGRAMCOUNT = count(*)
    from
        dbo.PROGRAM
    where
        ISDAILYADMISSION = 1
        and ISACTIVE = 1
        and ID in (select PROGRAMID from dbo.PROGRAMSALESMETHOD where SALESMETHODID = @GROUPSALESSALESMETHODID);

    select
        @SCHEDULEDPROGRAMCOUNT = count(*)
    from
        dbo.PROGRAM
    where
        ISDAILYADMISSION = 0
        and ISACTIVE = 1
        and ID in (select PROGRAMID from dbo.PROGRAMSALESMETHOD where SALESMETHODID = @GROUPSALESSALESMETHODID);

    select
        @PROGRAMEVENTCOUNT = count(*)
    from
        dbo.EVENT
    inner join
        dbo.PROGRAM on PROGRAM.ID = EVENT.PROGRAMID
    where
        EVENT.ISACTIVE = 1
        and PROGRAM.ISACTIVE = 1
        and EVENT.ID in (select EVENTID from dbo.EVENTSALESMETHOD where SALESMETHODID = @GROUPSALESSALESMETHODID);

    select @TRACKCOUNT = count(*) from dbo.TRACK;

    select top 1
        @DEPOSITREQUIRED = DEPOSITREQUIRED,
        @DEPOSITTIMEVALUE = DEPOSITTIMEVALUE,
        @DEPOSITTIMECODE = DEPOSITTIMECODE,
        @SECURITYDEPOSITREQUIRED = SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITTIMEVALUE = SECURITYDEPOSITTIMEVALUE,
        @SECURITYDEPOSITTIMECODE = SECURITYDEPOSITTIMECODE,
        @CONTRACTREQUIRED = CONTRACTREQUIRED,
        @CONTRACTTIMEVALUE = CONTRACTTIMEVALUE,
        @CONTRACTTIMECODE = CONTRACTTIMECODE,
        @FINALCOUNTREQUIRED = FINALCOUNTREQUIRED,
        @FINALCOUNTTIMEVALUE = FINALCOUNTTIMEVALUE,
        @FINALCOUNTTIMECODE = FINALCOUNTTIMECODE,
        @FINALTIMEVALUE = FINALTIMEVALUE,
        @FINALTIMECODE = FINALTIMECODE,
        @MAXIMUMCAPACITY = MAXIMUMCAPACITY
    from
        dbo.GROUPSALESDEFAULT;

    select @RATESCALECOUNT = count(*) from dbo.RATESCALE where ISACTIVE = 1;
    select @CONTRACTTEMPLATECOUNT = count(*) from dbo.LETTERTEMPLATE where LETTERTEMPLATETYPECODE = 7;

    select @GROUPSALESDISCOUNTCOUNT = count(DISCOUNT.ID)
    from dbo.DISCOUNT
    inner join dbo.DISCOUNTAVAILABILITY
        on DISCOUNTAVAILABILITY.DISCOUNTID = DISCOUNT.ID
    inner join dbo.DISCOUNTAVAILABILITYSALESMETHOD
        on DISCOUNTAVAILABILITY.ID = DISCOUNTAVAILABILITYSALESMETHOD.DISCOUNTAVAILABILITYID
    inner join dbo.SALESMETHOD
        on DISCOUNTAVAILABILITYSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
    where SALESMETHOD.TYPECODE = 3
        and DISCOUNT.ISACTIVE = 1

    return 0;