SELECT DISTINCT ACA.int_actor_type_id
			FROM tbl_access_control_rules ACR
			INNER JOIN tbl_access_control_allow ACA ON ACR.int_access_rule_id = ACA.int_access_rule_id
			WHERE
				
					str_fuse_action LIKE ? 
				AND ISNULL(ACA.int_actor_type_id,'')<>''
				AND ACA.bit_allow = 1

SET NOCOUNT ON

	DECLARE @UsesRefundRequest CHAR(1)

	SET @UsesRefundRequest = 0
	SET @UsesRefundRequest = (
			SELECT 1
			FROM RefundPaymentMethods
			WHERE RefundPaymentMethodID = 7
				AND STATUS = 'A'
			)

	SELECT TOP 1 rg.RegionID
		, rg.NAME
		, rg.Address1
		, rg.Address2
		, rtrim(rg.City) + ', ' + rg.STATE + '  ' + rg.Zip AS CSZ
		, rg.Phone1
		, rg.SiteTitle
		, rg.Footer
		/**/
		, rg.prefooter
		, rg.brandingfooter
		/**/
		, rg.MetaText
		, rg.EmailAux4
		, IsNull((
				SELECT Fee
				FROM Products
				WHERE Code = 'REGFEE'
					AND STATUS = 'A'
					AND RegionID = rg.RegionID
				), 0.00) AS RegistrationFee
		, IsNull((
				SELECT Fee
				FROM Products
				WHERE Code = 'DEFERRED_PAYMENT_FEE'
					AND STATUS = 'A'
					AND RegionID = rg.RegionID
				), 0.00) AS deferred_payment_fee
		, fc.*
		, @UsesRefundRequest AS UsesRefundRequest
		, rg.privacy_data_mask_id
		, rg.str_google_verify
		, rg.str_yahoo_verify
		, rg.str_hubspot_portal_id
		, rg.str_hubspot_salog_version
		, rg.str_hubspot_ppa
		, rg.str_genoo_api_key
		, rg.str_genoo_tracking_code
		, rg.TimeZoneHourAdjustment
		/**/
		/**/
		/**/
		, e.str_erp_name
		/**/
		/**/
		, CAST(DecryptByKeyAutoCert(cert_id('LumensCert'), NULL, ec.varbin_erp_password) AS NVARCHAR(500)) AS str_erp_password_decrypted
		/**/
		/**/
		, rg.theURL
		, rg.URLText
		, rg.str_analytics_linked_domains
		, rg.GTMContainerID
		, rg.str_social_media_sharing
		, ec.*
		, (
			SELECT TOP 1 Description
			FROM PaymentMethods
			WHERE PaymentMethodID >= 200
				AND STATUS = 'A'
		) AS str_internal_payment_method_name
	FROM FeatureConfiguration fc
	INNER JOIN Regions rg ON fc.RegionID = rg.RegionID
	LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id
	LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> ''
	WHERE rg.STATUS = 'A'

SELECT regionId,orgID
				FROM featureConfigurationQry
				WHERE baseurl LIKE '%v8template2.augusoft.net%'

SET NOCOUNT ON

			SELECT OrgID
				, RegionID
				, MenuItemID
				, MenuItemType
				, MainLevel
				, SubLevel
				
				,MainLevelSortOrder
				, SubLevelSortOrder
				, 
					SiteURL
				
				, AccessLevel
				, IsNull(DisplayCriteria_1, '') AS DisplayCriteria_1
				
				, IsNull(DisplayValue_1, '') AS DisplayValue_1
				, IsNull(DisplayCriteria_2, '') AS DisplayCriteria_2
				, IsNull(DisplayValue_2, '') AS DisplayValue_2
				, Condition
				, LTRIM(RTRIM(IsNull(NewWindowName, ''))) AS NewWindowName
				, m.Label AS UniqueLable
				
				, CASE
					WHEN str_user_nav_label IS NULL
						THEN CASE
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9440'
									THEN Replace(Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"'), 'Membership', 'Membership')
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9620'
									THEN Replace(Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"'), 'Member', 'Member')
										/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'Donate'
									THEN Replace(m.Label, 'Donate', 'Donate')
										/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'MERCHANDISE'
									THEN Replace(m.Label, 'MERCHANDISE', 'Merchandise')
								WHEN m.UniqueLabel = 'registration/scholarships' OR m.UniqueLabel = 'myscholarships'
									THEN Replace(m.Label, 'Scholarships', 'Scholarship')
								ELSE Replace(Replace(Replace(m.Label, '\', '\\'), '''', '\'''), '"', '\"')
								END
					ELSE
						/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
						CASE
							WHEN str_menu_label LIKE 'Donate'
								THEN (str_user_nav_label)
							ELSE str_user_nav_label
							END
						/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
					END AS Label
				
				, CASE
					WHEN str_user_nav_label IS NULL
						THEN CASE
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9440'
									THEN Replace(m.Label, 'Membership', 'Membership')
								WHEN MenuItemType != 'Lnk'
									AND SiteURL = 'index.cfm?fuseaction=9620'
									THEN Replace(m.Label, 'Member', 'Member')
									/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'Donate'
									THEN Replace(m.Label, 'Donate', 'Donate')
									/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
								WHEN m.Label LIKE 'MERCHANDISE'
									THEN Replace(m.Label, 'MERCHANDISE', 'Merchandise')
								WHEN m.UniqueLabel = 'registration/scholarships' OR m.UniqueLabel = 'myscholarships'
									THEN Replace(m.Label, 'Scholarships', 'Scholarship')
								ELSE m.Label
								END
					ELSE
						/*BEGIN NEO 00106  20 FEB 2015 Student Donations UC-02 */
						CASE
							WHEN str_menu_label LIKE 'Donate'
								THEN dbo.fun_strip_html((str_user_nav_label))
							ELSE dbo.fun_strip_html(str_user_nav_label)
							END
						/*END NEO 00106  20 FEB 2015 Student Donations UC-02 */
					END AS LabelNoHTML 
				
				, ISNULL(int_parent_id,0) as int_parent_id
				, int_sort_order
				, m.UniqueLabel
				, m.bit_default_behavior
			FROM MenuItems m
			LEFT JOIN tbl_user_nav_menu_label u ON m.MenuItemID = u.int_menu_item_id
			WHERE STATUS = 'A'
				AND SubCategoryID IS NULL
				AND (
					
						int_actor_type_id = ?
						OR
						(
							int_actor_type_id IS NULL
							AND AccessLevel = ?
						)
					
				)
				
				AND ISNULL(SiteURL, '') NOT LIKE 'index.cfm?fuseaction=1010' /*29 MAY 2012 NEO 00043 TRACKER 29863*/
				
			ORDER BY int_sort_order

SET ANSI_NULLS ON

		SELECT IsNull((
					CASE
						WHEN str_dashboard_name = ''
							THEN NULL
						ELSE str_dashboard_name
						END
					), CASE
					WHEN ActorTypeID = 55
						THEN 'Dashboard'
					ELSE 'my dashboard'
					END) AS str_dashboard_name /* 02 APRIL 2014 NEO 0088 TRACKER 40005 */
		FROM ActorTypes
		WHERE ActorTypeID = ?

SELECT pfo.int_order
				, pf.str_field_display_name
				, pf.str_field_name
				, pfo.int_profile_field_tab_id
				, pf.str_actor_types
				, pf.chr_field_type
			FROM tbl_profile_field_order pfo
			INNER JOIN tbl_profile_field pf ON pfo.int_profile_field_id = pf.int_profile_field_id
			WHERE pf.int_region_id = ?
				AND bit_enabled = ?
				AND
				(
					(
						pf.chr_field_type not in ('C','O')
					)
					
						OR
						(
							pf.chr_field_type in ('C','O') AND
							(
								pf.str_actor_types LIKE ? 
							)
						)
					
				)
			ORDER BY pfo.int_profile_field_tab_id
				, pfo.int_order ASC

select * from tbl_social_media_details WHERE int_region_id = ?
			order by int_order asc

select * from UIVariables where RegionID = ?

SELECT menitms.SiteURL AS nav_site_url
			, menitms.label AS nav_label
			, menitms.rowNum
			, COUNT(menitms.rowNum) OVER () AS Total_Rows
			, menitms.DisplayCriteria_1
			, menitms.DisplayValue_1
			, menitms.DisplayCriteria_2
			, menitms.DisplayValue_2
			, menitms.Condition
			, menitms.NewWindowName
		FROM (
			SELECT SiteURL
				, label
				, DisplayCriteria_1
				, DisplayValue_1
				, DisplayCriteria_2
				, DisplayValue_2
				, Condition
				, Row_Number() OVER (
					ORDER BY MenuitemID ASC
					) AS rowNum
				, NewWindowName
			FROM MenuItems
			WHERE AccessLevel 
			
					= ? 
			
				AND RegionID = ?
			AND bit_display_in_nav=1
				AND STATUS = ?
				
	) menitms

SELECT menitms.SiteURL
			, menitms.label
			, menitms.AccessLevel
			, menitms.rowNum
			, ((rowNum - 1) / 4) + 1 AS block
			, menitms.DisplayCriteria_1
			, menitms.DisplayValue_1
			, menitms.DisplayCriteria_2
			, menitms.DisplayValue_2
			, menitms.Condition
			, menitms.NewWindowName
		FROM (
			SELECT SiteURL
				, label
				, AccessLevel
				, DisplayCriteria_1
				, DisplayValue_1
				, DisplayCriteria_2
				, DisplayValue_2
				, Condition
				, Row_Number() OVER (
					ORDER BY MenuitemID ASC
					) AS rowNum
				, NewWindowName
			FROM MenuItems
			WHERE AccessLevel
			
			
					= ? 
			
				AND RegionID = ?
			AND bit_display_in_footer=1
				AND STATUS = ?
				
	) menitms

UPDATE tbl_cart_class_detail
			SET EnteredDate = dbo.fun_GetDateCT()
			WHERE SessionID = ?;
			DELETE
			FROM tbl_cart_class_detail
			WHERE DATEDIFF(minute,EnteredDate,dbo.fun_GetDateCT()) >= 61;
			UPDATE tbl_cart_merchandise_detail
			SET EnteredDate = dbo.fun_GetDateCT()
			WHERE SessionID = ?;
			DELETE FROM tbl_cart_merchandise_detail
			WHERE DATEDIFF(minute,EnteredDate,dbo.fun_GetDateCT()) >= 61;

SELECT str_header_script
				, str_footer_script
			FROM tbl_style_settings WITH (NOLOCK)
			-- WHERE int_region_id = Commented as long as multiple regions are not there

select * from qTempMenu where MenuItemID NOT IN  (562,2,633)

SELECT isNull(str_user_nav_label,'') AS str_menu_label
		FROM tbl_user_nav_menu_label
		WHERE int_actor_type_id = ?
		AND str_menu_label LIKE 'DONATE'
		AND str_display_condition='application.appvars[ri].bit_allow_student_donation EQ 1 AND application.appvars[ri].bit_display_donate_in_navmenu EQ 1';

select * from qMenu where int_parent_id = 532

select * from qMenu where int_parent_id = 538

select * from qMenu where int_parent_id = 539

select * from qMenu where int_parent_id = 540

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 6

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 458

select * from qMenu where int_parent_id = 500

select * from qMenu where int_parent_id = 532

select * from qMenu where int_parent_id = 538

select * from qMenu where int_parent_id = 539

select * from qMenu where int_parent_id = 540

select * from qMenu where int_parent_id = 5

select * from qMenu where int_parent_id = 6

select * from qMenu where int_parent_id = 7

select * from qMenu where int_parent_id = 458

select * from qMenu where int_parent_id = 500

SELECT isNull(str_user_nav_label,'') AS str_menu_label
		FROM tbl_user_nav_menu_label
		WHERE int_actor_type_id = ?
		AND str_menu_label LIKE 'DONATE'
		AND str_display_condition='application.appvars[ri].bit_allow_student_donation EQ 1 AND application.appvars[ri].bit_display_donate_in_navmenu EQ 1';

SET NOCOUNT ON

		SELECT rg.Phone1
			,rg.Phone1Ext
			,rg.email
		FROM Regions rg
		WHERE rg.RegionID = ?
			AND rg.STATUS = 'A'

SELECT TOP 1 Label  FROM base_field_customized WITH (NOLOCK)
		WHERE base_field = 'class_section'
		AND base_table_id  = 2 AND customized_base_field_id IS NOT NULL

SELECT TOP 1 fc.str_text_when_class_waiting_list_not_allowed
		FROM FeatureConfiguration fc
		INNER JOIN Regions rg ON fc.RegionID = rg.RegionID
		LEFT JOIN tbl_erp e ON fc.int_erp_id = e.int_erp_id
		LEFT JOIN tbl_erp_config ec ON ec.str_erp_url <> ''
		WHERE rg.STATUS = 'A'

SELECT sc.CategoryId
				, sc.SubCategoryID
			FROM tbl_class_listing tcl WITH (NOLOCK)
			INNER JOIN Subcategories sc WITH (NOLOCK) ON sc.SubCategoryID = tcl.SubCategoryID
			WHERE tcl.classid = ?;

Select Name
		
		,[dbo].[fun_strip_html](Name) as NameNoHTML
		from categories WITH (NOLOCK)
		where CategoryID = ?

SELECT NAME
			,Description
			
			,str_description_no_html AS DescriptionNoHTML
			,Status
		FROM subcategories WITH (NOLOCK)
		WHERE SubCategoryID = ?
			AND CategoryID = ?

select  contents from contents WITH (NOLOCK) where
	path= ?

/*
				What should happen : Refer Tracker 43106
				Best Match for the 'Recommended Classes' box for a Student 
				Lumens will compare the courses for all the classes in which 
					the student has been registered (but not those for which the registrations have been cancelled or transferred) 
					to the courses that other students, who also registered in the same classes as the student, have been registered in (but not those for which the registrations have been cancelled or transferred). 
				Lumens will determine the 20 courses which are  
				1) the parents of the classes most frequently registered for and  
				2) are currently available for the student to register in. 
				  
				Lumens will display the Class Names of the three (3) classes that are children of the most frequently registered  
				courses from that list in the 'Recommended Courses' box. 
				  
				If the student has not registered for any classes, or if the user is a guest, then Lumens will determine the 20 classes that are most frequently registered for in the Category being viewed that are available for registration for this student. 
				Once determined, Lumens will display the Class Names of the three (3) courses from that list. 

				Aswin 01/02/2018 : If the student has not registered for any classes - I dont think this is handled.
						Also - arguments.recommended_type is not asked for by the spec.
			*/
			DECLARE @LearnerID AS INT
				, @SubcategoryID AS INT;

			SET @LearnerID = 0;

			

			SET @SubcategoryID = ?;

			SELECT TOP 20 co.NAME
				, co.courseid
				, co.subcategoryid
				, c.ClassID
				, c.NAME AS class_name
				, count(co.courseid) AS RegCount
			FROM registrations r WITH (NOLOCK)
			INNER JOIN classes c WITH (NOLOCK) ON r.classid = c.classid
			INNER JOIN tbl_class_detail tcd WITH (NOLOCK) ON tcd.int_class_id = c.classid
			INNER JOIN courses co WITH (NOLOCK) ON c.courseid = co.courseid
			WHERE 1 = 1 
				 
					AND c.dat_public_registration_closes > dbo.fun_GetDateCT()
					AND c.STATUS IN (
						'A'
						, 'C'
						) 
				
				AND co.status in ('A')
				
					AND tcd.bit_display_to_public = 1 
				
				
			GROUP BY co.courseid
				, co.NAME
				, co.subcategoryid
				, c.ClassID
				, c.NAME
			HAVING SUM(c.RemainingSeats) > 1
			ORDER BY RegCount DESC

select str_class_listing_order from tbl_class_listing_order

SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass
		FROM CATALOGS
		WHERE CatalogID = 1
			AND NAME = 'All Catalogs'

SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass
		FROM CATALOGS
		WHERE CatalogID = 1
			AND NAME = 'All Catalogs'

SELECT COUNT(catalogid) AS IsAllCatalogMeansAllClass
		FROM CATALOGS
		WHERE CatalogID = 1
			AND NAME = 'All Catalogs'

SET NOCOUNT ON
		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
		DECLARE @MembershipID AS INT
			,@TimeZoneShift AS FLOAT
			,@AllcatalogId AS INT/*TRACKER 37132 : SEP 24 2013 NEO 0126 */
			,@SearchKey VARCHAR(500)
			,@SubCategoryID INT
			,@StrSubCategoryID VARCHAR(20)
			,@CategoryID INT
			,@classtype INT
			,@CourseName VARCHAR(255)
			,@StartDate DATETIME
			,@EndDate DATETIME
			,@StartTimeHour INT
			,@StartTimeMinute INT
			,@EndTimeHour INT
			,@EndTimeMinute INT
			,@City VARCHAR(255)
			,@ActorID INT
			,@LearnerID INT
			,@ClassID INT
			,@InstructorID INT
			,@CourseNumber VARCHAR(30)
			,@LocationID INT
			,@CourseSeriesID INT
			,@containsearchKey VARCHAR(510)
			,@bit_use_guest_checkout BIT;

		

		SET @LearnerID = ?

		SET @MembershipID = IsNull((
					SELECT MembershipID
					FROM learners
					WHERE learnerid = @LearnerID
					), 0)

		/*  PAV 12 Aug 2009 added below which now incorporates date AND time into the query */
		SET @TimeZoneShift = IsNull((
					SELECT TimeZoneHourAdjustment
					FROM Regions
					WHERE RegionID = ?
					), 0)

		SET @AllcatalogId = (
				SELECT catalogid
				FROM catalogs
				WHERE NAME = 'All Catalogs'
				) /*TRACKER 37132 : SEP 24 2013 NEO 0126 */
		
			SET @SubCategoryID = ?;
			SET @StrSubCategoryID = ?;
		
			SET @CategoryID = ?;
		
		SET @SearchKey = ?;
		SET @containsearchKey = '"'+replace(@SearchKey,'"','')+'*"';

		

		SET @bit_use_guest_checkout = ?;


		/*new advanced search fields */
		DECLARE @durationTypeID INT
			, @ceus decimal(5,2)
			, @ContactHours decimal(10,2)
			, @InstructionalMethodId INT
			, @ClassRoom VARCHAR(30)
			, @LMSID INT
			, @BadgeID INT
			, @BadgeTemplateID INT
			, @PublicRegistrationStarts DATETIME
			, @PublicRegistrationEnds DATETIME
			, @MinAge INT
			, @MaxAge INT
			, @LimitSelfRegistrationMembershipTypeID INT
			, @QtyRegistration BIT
			, @CountTowardsBenefitsMembershipTypeID BIT
			, @AllowMultipleRegistrations BIT
			, @RequiresRegFee BIT
			, @UsesLottery BIT
			, @LotteryRunDate DATE
			, @LotteryCloseDate DATE
			, @LotteryTypeID INT
			, @ClassUnlockKey VARCHAR(100)
			, @MinPrice DECIMAL(10,2)
			, @MaxPrice DECIMAL(10,2);

		
		/*new advanced search fields */

		/* Table variables to populate the list of ID's to build the query from.  This change improved performance by 40%+.  These have the majority of the conditions AND are joined to each of the union sections. */
		DECLARE @ClassIDTable1 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for first part of Union  */
		DECLARE @ClassIDTable2 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for second part of Union  */
		DECLARE @ClassIDTable3 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for third part of Union  */
		DECLARE @ClassIDTable4 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/* for fourth part of Union  */
		DECLARE @ClassIDTable5 TABLE (
			ClassListingID INT NOT NULL
			, Rank INT
			, Rank2 INT
			);/*  */

		/**************************************************************************/
		/******* Begin Populate ID's for first part of union for classes **********/
		/**************************************************************************/
			INSERT INTO @ClassIDTable1
			SELECT DISTINCT ClassListingID
				
					,0 AS Rank
					,0 AS Rank2
				
			FROM tbl_class_listing cl
				
				INNER JOIN Courses co ON co.CourseID = cl.CourseID
			WHERE 1 = 1
				/* 7/7/12/2012 tracker 20200 */
				
				/* end tracker 20200*/
				
					AND (
							Cl.SubCategoryID = @SubCategoryID
							OR CL.str_alt_subcategory_ids LIKE ''+@StrSubCategoryID+',%'
							OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+',%'
							OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+''
							OR CL.str_alt_subcategory_ids = @StrSubCategoryID
							)
				
					AND DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.AppearanceStartDate
							AND DateAdd(ms, - 10, CAST(CL.AppearanceEndDate AS DATETIME))
				
				/* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
				/* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
					AND (CL.bit_display_to_public = 1)
				
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				
				
				AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout
				/**/
				AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID )
				AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' )
				AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts )
				AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds )
				AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge )
				AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge )
				AND ( @LimitSelfRegistrationMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',')
						WHERE value = @LimitSelfRegistrationMembershipTypeID
					)
				)
				AND ( @CountTowardsBenefitsMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_count_towards_membership_benefits, ',')
						WHERE value = @CountTowardsBenefitsMembershipTypeID
					)
				)
				AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice )
				AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice )
				AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery )
				AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate )
				AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate )
				AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID )
				AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID )
				AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID )
				AND ( @ceus IS NULL OR cl.CEUs = @ceus )
				AND ( @ContactHours IS NULL OR cl.ContactHrs = @ContactHours )
				AND ( @LMSID IS NULL OR cl.int_lms_integration_id = @LMSID )
				AND ( @QtyRegistration IS NULL OR cl.AllowQtyPurchase = @QtyRegistration )
				AND ( @AllowMultipleRegistrations IS NULL OR cl.bit_allow_multiple_registration = @AllowMultipleRegistrations )
				AND ( @RequiresRegFee IS NULL OR cl.regFeeRequired = @RequiresRegFee )
				/* these require joins to other tables and can result in peformance degradation */
				AND (
						@InstructionalMethodID IS NULL
						OR EXISTS (  SELECT 1 FROM tbl_ClassSchedules WHERE ClassID = cl.ClassID AND int_instructional_method_id = @InstructionalMethodID )
				)
				AND (
						@ClassUnlockKey IS NULL
						OR EXISTS (
							SELECT TOP 1 1
							FROM tbl_restriction_code_detail rcd
							INNER JOIN tbl_table t ON rcd.int_table_id = t.int_table_id
							INNER JOIN tbl_restriction_code rc ON rc.int_restriction_code_id = rcd.int_restriction_code_id
							WHERE t.str_original_table_name = 'Classes'
							    AND rcd.int_primary_key_id = cl.ClassID
							    AND rc.str_restriction_code = @ClassUnlockKey
						)
				)
				
				/**/

				  /* don't delete if Class ID is the keyword because rank will be null  */
					IF (
							SELECT Count(CourseID)
							FROM Courses
							WHERE CourseNumber = @SearchKey
							) = 0 /* Delete null rank records if the keyword is NOT a course number */
					BEGIN
						DELETE
						FROM @ClassIDTable1
						WHERE (
								rank IS NULL
								AND rank2 IS NULL  /* PAV 14 Jul 2017 - changed from OR to AND to resolve issue where tenses of words were not returning correct records */
								)
							AND (
								classlistingid NOT IN (
									SELECT classlistingid
									FROM tbl_class_listing
									WHERE (ClassName LIKE '%'+@SearchKey+'%')
										OR (DescriptionNoHTML LIKE '%'+@SearchKey+'%')
									)
								) /* Tracker 42483,42624,42606 Sep 10 2014 Neo 0126 */
					END
				
		/**************************************************************************/
		/***** End Populate ID's for first part of union for classes **************/
		/**************************************************************************/


		/**************************************************************************/
		/***** BEGIN Populate ID's for second part of union for course series *****/
		/**************************************************************************/
		
			/*00097 11/10/2011 Tracker 27177 */
			INSERT INTO @ClassIDTable2
			SELECT DISTINCT ClassListingID
				
				,0 AS Rank
				,0 AS Rank2
				
			FROM tbl_class_listing cl
			
			INNER JOIN tbl_class_course_series_group ccsg ON ccsg.int_Series_group_id = CL.int_course_series_group_id
			INNER JOIN CourseSeriesGroups CSG ON CSG.SeriesGroupID = CL.int_course_series_group_id
			LEFT JOIN Courses co ON co.CourseID = cl.CourseID
			WHERE CL.str_class_type = 'CS'
				AND (
					SELECT COUNT(c2.classid)
					FROM tbl_class_course_series_group cg2
					INNER JOIN classes c2 ON c2.classid = cg2.int_class_id
					WHERE cg2.int_series_group_id = ccsg.int_series_group_id
						AND c2.STATUS NOT IN ('X')
					) = (
					SELECT COUNT(cg3.int_class_id)
					FROM tbl_class_course_series_group cg3
					WHERE cg3.int_series_group_id = ccsg.int_series_group_id
					)
				/*END 15 JUNE 2012 NEO V 0049 TRACKER 30029*/
				AND (
					SELECT count(cl1.classid)
					FROM tbl_class_course_series_group cg
					INNER JOIN classes cl1 ON cl1.classid = cg.int_class_id
					WHERE cg.int_series_group_id = ccsg.int_series_group_id
						AND cl1.STATUS IN ('A','C')
					) > 0
				AND cl.chr_status = 'A'
				
				/* Begin Tracker 27353 19 Dec 2011  */
				
				/* End Tracker 27353 19 Dec 2011  */
				
				/* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
				/* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
					AND (
						Cl.SubCategoryID = @SubCategoryID
						OR CL.str_alt_subcategory_ids LIKE ''+@StrSubCategoryID+',%'
						OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+',%'
						OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+''
						OR CL.str_alt_subcategory_ids = @StrSubCategoryID
					)
				
				/* 7/7/12/2012 tracker 20200 */
				
				/* end tracker 20200*/
				/* BEGIN PAV 7 Jan 2013 allow search for single course series  */
				
				/* END PAV 7 Jan 2013 allow search for single course series  */
				/* Begin: 00097 11232011  Tracker: 27171 */
				AND
				/* BEGIN 09 MAY 2014 NEO 0088 TRACKER 40819 */
				
					DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.AppearanceStartDate
							AND DateAdd(ms, - 10, CAST(CL.AppearanceEndDate AS DATETIME))
				
				/* END 09 MAY 2014 NEO 0088 TRACKER 40819 */
				/* End: 00097 11232011  Tracker: 27171 */
				/* Tracker 40369 Jan 16 2015 Neo 00159 */
				AND (
						SELECT count(clR.classid)
						FROM tbl_class_course_series_group cgR
						INNER JOIN classes clR ON clR.classid = cgR.int_class_id
						WHERE cgR.int_series_group_id = ccsg.int_series_group_id
							AND clR.STATUS IN ('R')
						) = 0
				AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout
				/**/
				AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID )
				AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' )
				AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts )
				AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds )
				AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge )
				AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge )
				AND ( @LimitSelfRegistrationMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',')
						WHERE value = @LimitSelfRegistrationMembershipTypeID
					)
				)
				AND ( @CountTowardsBenefitsMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_count_towards_membership_benefits, ',')
						WHERE value = @CountTowardsBenefitsMembershipTypeID
					)
				)
				AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice )
				AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice )
				AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery )
				AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate )
				AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate )
				AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID )
				AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID )
				AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID )
				AND 1 = CASE WHEN @Ceus IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @ContactHours IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @LMSID IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @QtyRegistration IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @AllowMultipleRegistrations IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @RequiresRegFee IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @InstructionalMethodID IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @ClassUnlockKey IS NULL THEN 1 ELSE 0 END
				
				/**/
				  /* don't delete if Class ID is the keyword because rank will be null  */
					IF (
							SELECT Count(CourseID)
							FROM Courses
							WHERE CourseNumber = @SearchKey
							) = 0 /* Delete null rank records if the keyword is NOT a course number */
					BEGIN
						DELETE
						FROM @ClassIDTable2
						WHERE (rank IS NULL
							AND rank2 IS NULL)
					END
				
		/**************************************************************************/
		/***** END Populate ID's for second part of union for course series *******/
		/**************************************************************************/

		/**************************************************************************/
		/***** BEGIN Populate ID's for third part of union for certificates   *****/
		/**************************************************************************/
		
			INSERT INTO @ClassIDTable3
			SELECT DISTINCT ClassListingID
				
				,0 AS Rank
				,0 AS Rank2
				
			FROM tbl_class_listing cl
				
				INNER JOIN tbl_certificates_classes tcc1 ON tcc1.int_certificate_id = CL.int_certificate_id AND tcc1.int_certificate_group_id = 0
				INNER JOIN Courses co ON co.CourseID = tcc1.int_course_id
				INNER JOIN tbl_certificates tc ON tc.int_certificate_id = CL.int_certificate_id AND tc.chr_status = 'P' AND tc.bit_use_certificate_groups = 0
			WHERE CL.str_class_type = 'cf'
				
				/*-  8/28/2012  tracker 30987-*/
				

					AND (
							Cl.SubCategoryID = @SubCategoryID
							OR CL.str_alt_subcategory_ids LIKE ''+@StrSubCategoryID+',%'
							OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+',%'
							OR CL.str_alt_subcategory_ids LIKE '%,'+@StrSubCategoryID+''
							OR CL.str_alt_subcategory_ids = @StrSubCategoryID
							)
				
				/*BEGIN 13 Aug 2012 PAV TRACKER 30987 SEARCH VALUES */
				
				/*END 13 Aug 2012 PAV TRACKER 30987 SEARCH VALUES */
				
				/*BEGIN 18 JAN 2012 NEO 00043 TRACKER 27847 SEARCH VALUES */
				
				/* BEGIN : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
				/* END : 09 SEP 2014 NEO 00159 TRACKER 42510 */
				
				/*END 18 JAN 2012 NEO 00043 TRACKER 27847 SEARCH VALUES */
				/* 7/7/12/2012 tracker 20200 */
				
				/* end tracker 20200*/
				/* BEGIN PAV 7 Jan 2013 allow search for single course series  */
				
				/* END PAV 7 Jan 2013 allow search for single course series  */
				/* -8/17/2012 tracker 31042*/
				
				AND
				/* BEGIN 09 MAY 2014 NEO 0088 TRACKER 40819 */
				
					DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.AppearanceStartDate
							AND DateAdd(ms, - 10, CAST(CL.AppearanceEndDate AS DATETIME))
				
				/* END 09 MAY 2014 NEO 0088 TRACKER 40819 */
				AND ISNULL(cl.bit_use_guest_checkout,0) = @bit_use_guest_checkout
				/**/
				AND ( @durationTypeID IS NULL OR cl.int_duration_type_id = @durationTypeID )
				AND ( @ClassRoom IS NULL OR cl.ClassRoom LIKE '%'+@ClassRoom+'%' )
				AND ( @PublicRegistrationStarts IS NULL OR cl.dat_public_registration_begin >= @PublicRegistrationStarts )
				AND ( @PublicRegistrationEnds IS NULL OR cl.dat_public_registration_closes <= @PublicRegistrationEnds )
				AND ( @MinAge IS NULL OR cl.MinAge >= @MinAge )
				AND ( @MaxAge IS NULL OR cl.MaxAge <= @MaxAge )
				AND ( @LimitSelfRegistrationMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_limit_self_reg_mbrship_type_ids, ',')
						WHERE value = @LimitSelfRegistrationMembershipTypeID
					)
				)
				AND ( @CountTowardsBenefitsMembershipTypeID IS NULL
					OR EXISTS (
						SELECT 1 FROM
						STRING_SPLIT(cl.str_count_towards_membership_benefits, ',')
						WHERE value = @CountTowardsBenefitsMembershipTypeID
					)
				)
				AND ( @MinPrice IS NULL OR cl.Cost >= @MinPrice )
				AND ( @MaxPrice IS NULL OR cl.Cost <= @MaxPrice )
				AND ( @UsesLottery IS NULL OR cl.bit_uses_class_lottery = @UsesLottery )
				AND ( @LotteryCloseDate IS NULL OR cl.dat_lottery_close_date = @LotteryCloseDate )
				AND ( @LotteryRunDate IS NULL OR cl.dat_lottery_run_date = @LotteryRunDate )
				AND ( @LotteryTypeID IS NULL OR cl.int_lottery_type_id = @LotteryTypeID )
				AND ( @BadgeID IS NULL OR cl.int_badge_integration_id = @BadgeID )
				AND ( @BadgeTemplateID IS NULL OR cl.int_badge_integration_template_id = @BadgeTemplateID )
				AND 1 = CASE WHEN @Ceus IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @ContactHours IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @LMSID IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @QtyRegistration IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @AllowMultipleRegistrations IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @RequiresRegFee IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @InstructionalMethodID IS NULL THEN 1 ELSE 0 END
				AND 1 = CASE WHEN @ClassUnlockKey IS NULL THEN 1 ELSE 0 END
				
				/**/

			DELETE FROM @ClassIDTable3 where rank is null and rank2 is null  /* PAV 14 Jul 2017 - changed from OR to AND to resolve issue where tenses of words were not returning correct records */
		
		/**************************************************************************/
		/***** END Populate ID's for third part of union for certificates   *******/
		/**************************************************************************/

		/*****************************************************************************************/
		/***** Begin Populate ID's for fourth part of union for certificate program sections *****/
		/*****************************************************************************************/
		
		/******************************************************************************************/
		/***** END Populate ID's for fourth part of union for certificates program sections *******/
		/******************************************************************************************/

		/********************************************************************************/
		/***** BEGIN Populate ID's for fifth part of union for certificate groups   *****/
		/********************************************************************************/
		
		/********************************************************************************/
		/***** END Populate ID's for fifth part of union for certificate groups   *****/
		/********************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from first part of union for classes ***************************/
		/**************************************************************************************************/
			SELECT DISTINCT
				
				0 AS Rank
				,0 AS Rank2
				
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				
				,str_class_schedule_display_student_login AS str_class_schedule_display
				
				,showLocation AS showLocation
				,showCity AS showCity
				,showRoom AS showRoom
				,showCityLocation AS showCityLocation
				
				,CASE
					WHEN cl.bit_disp_instr_method_to_public = 1
						THEN instructionMethod
					ELSE ''
					END
				
				AS InstructionalMethod
				,DaysOfWeek AS DaysOfWeek
				,Instructors AS Instructor
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.URLText
					END AS CourseURLText
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.ClassURL
					END AS ClassURL
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.ClassURlText
					END AS ClassURlText
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				,c.AllowQtyPurchase AS AllowQtyPurchase
				/* NEO 00083 Membership enhancement UC 10 SS2 */
				,c.RequiresReleaseForm AS RequiresReleaseForm
				,'' AS int_age_limit
				/*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/
				,CASE
					WHEN csj.int_series_group_id IS NULL
						THEN 0
					ELSE 1
					END AS InCourseSeries
				/* JS 10/29/2009 tracker 16972 */
				/*- 2/8/2012 tracker 28153 -*/
				,IsNull((
						SELECT count(registrationid)
						FROM registrations
						WHERE classid = cl.classid
							AND iscancelled = 0
							AND int_series_group_id > 0
						), 0) AS SeriesRegs
				,IsNull((
						SELECT count(registrationID)
						FROM registrations
						WHERE classID = cl.classID
							AND Iscancelled = 0
						), 0) AS TotalRegs
				,IsNull((
						SELECT sum(int_reserved_seats)
						FROM tbl_class_course_series_group
						WHERE int_class_ID = cl.classID
						), 0) AS ReservedSeats
				/* End tracker 28153 */
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/
				,CL.DescriptionNoHTML AS description
				,CL.description AS descriptionFull
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/
				,(
					CL.TotalSeats - (
						SELECT isnull(SUM(ccsg1.int_reserved_seats), 0)
						FROM tbl_class_course_series_group ccsg1
						WHERE ccsg1.int_class_id = CL.ClassID
						)
					) AS ClassTotalSeats
				/*00097 01/27/2012 Tracker:27948*/
				,CL.ClassListingID
				,CL.ClassID
				,CL.CourseID
				,CL.SubCategoryID
				,CL.CourseNumber
				,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */
				,CL.CourseTitle
				,CL.NAME
				, CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.CourseURL
					END AS CourseURL
				, CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.URLText
					END AS URLText
				,CL.IsOnlineCourse
				,CL.Keywords
				,CL.ClassName
				,CL.CatalogID
				,CL.SectionNumber
				,CL.classStartDate
				,CL.classEndDate
				,CL.StartDate
				,CL.StartDateOrder
				,CL.EndDate
				,CL.EndDateOrder
				,CL.AppearanceStartDate
				,CL.AppearanceEndDate
				,CL.HotPixStartDate
				,CL.HotPixEndDate
				,CL.dat_public_registration_begin
				,CL.dat_public_registration_closes
				,CL.dat_staff_registration_begin
				,CL.str_alt_subcategory_ids
				,CL.IsFeaturedSpeaker
				,CL.ClassRating
				,CL.ClassRatingCount
				,CL.NumberSessions
				,CL.NumberWeeks
				,CL.DaysOfWeek
				,CL.Cost
				,CL.CostNoMaterialsFee
				,CL.MaterialsCost
				,CL.LocationID
				,CASE
					WHEN co.ExternalCourseProviderID = 1
						THEN ''
					ELSE CL.Location
					END AS Location
				,CL.City
				,CL.ClassRoom
				,CL.SpecialNotice
				,CL.TotalSeats
				,CL.InternalComments
				,CL.Comments
				,CL.PublicRegistration
				,CL.contactPhone
				/*,CL.InCourseSeries*/
				,CL.LessonReleaseTimes
				,CL.NumberofSessions
				,CL.NumberofWeeks
				,CL.StartDate1
				,CL.EndDate1
				,CL.CourseLogoPath
				,CL.AltCourseLogoPath
				,CL.bit_display_course_logo
				,CL.TimeZoneHourAdjustment
				,CL.MinAge
				,CL.MaxAge
				,CL.str_class_time_display
				,CL.int_certificate_id
				,CL.str_class_icon
				
				,co.coursegraphic
				,sc.str_subCat_graphic
				,ca.str_Cat_graphic
				
				
				,c.str_class_graphic_filename
				
				,CL.str_class_icon_alt_descr
				,csj.int_series_group_id int_course_series_group_id
				,CL.int_course_series_id
				,CL.str_class_type
				,CL.chr_status
				,CL.int_duration_type_id
				,CL.int_quantity
				,CL.bit_display_to_public
				,CL.dat_publish_date
				
				,CL.GoNumber
				 AS  GoNumber
				
				,CL.int_program_section_id
				,CL.str_count_towards_membership_benefits
				,CL.StaffDisplayStartDate
				,CL.StaffDisplayEndDate
				,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,cl.CourseTitle AS NAME
				,c.dat_staff_registration_begin AS dat_staff_registration_begin
				,co.CourseNumber AS CourseNo
				,co.int_schedule_type_id AS ScheduleType
				,CL.int_duration_type_id AS DurationType
				,CL.int_quantity
				,CL.int_time_period_id
				,(
					SELECT str_description
					FROM tbl_time_periods
					WHERE int_time_period_id = CL.int_time_period_id
					) AS TimeDescription
				,(SELECT count(R.RegistrationID)
					FROM registrations R
					WHERE R.classID = CL.classID
					AND R.IsCancelled = 0) AS registrationCount
				,c.TotalSeats - (SELECT count(registrationid) from registrations where classid = c.classid and iscancelled = 0) as remainingseats
				,(
					SELECT count(R.RegistrationID)
					FROM registrations R
					WHERE R.classID = CL.classID
						AND R.IsCancelled = 0
						AND (
							R.int_series_group_id = 0
							OR R.int_series_group_id IS NULL
							)
					) AS FilledSeats
				/*00097 01/27/2012 Tracker:27948*/
				
				,IsNull(wl.WaitingListCount, 0) AS waitingList
				,CASE
					WHEN IsNull(wl.WaitingListCount, 0) > 0
						THEN 1
					ELSE 0
					END AS bit_class_on_waiting_list
				,CASE
					WHEN (
							c.remainingseats <= 0
							OR IsNull(wl.WaitingListCount, 0) > 0
							)
						THEN 1
					ELSE 0
					END AS isfull
				
				,CASE
					/* 10 July 2012 Poornima Added a check to see if the registration is a quantity registration */
					WHEN EXISTS (
							SELECT RegistrationID
							FROM Registrations R
							WHERE R.classid = cl.classid
								AND R.LearnerID = @LearnerID
								AND R.IsCancelled = 0
								AND IsNull(R.bit_qty_reg_additional, 0) = 0
							)
						THEN 1
					ELSE 0
					END AS bit_enrolled
				/*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */
				,c.bit_allow_multiple_registration
				,CASE
					WHEN EXISTS (
							SELECT w.WaitListID
							FROM WaitingLists w
							INNER JOIN WaitingListEntries we ON w.waitlistid = we.waitlistid
							WHERE w.classid = cl.classid
								AND we.LearnerID = @LearnerID
							)
						THEN 1
					ELSE 0
					END AS bit_on_waiting_list
				/*
					Author      : Neo 00007
					Date        : 13 Oct 2010
					Function    : For Tracker 20377 - Collect whether the class is in cart for that learner.
				*/
				/* Aswin 20 Feb 2015 updated the incart logic*/
				,(
					SELECT CASE
							WHEN (
									SELECT COUNT(DISTINCT int_learner_id)
									FROM tbl_cart_class_detail tcd
									INNER JOIN classes ON classes.classid = tcd.int_class_id
									WHERE tcd.int_class_id = Cl.ClassID
										AND classes.bit_allow_multiple_registration = 0
										/* some customers use household as group having large number of members */
										
											AND int_learner_id IN (?)
										
									) = ?
								THEN 1
							ELSE 0
							END
					) AS learnerAlreadyInCart
				/* 00097 NLUAT-3256 14 Aug 2012 */
				/* Aswin 20 Feb 2015 updated the incart logic*/
				,'' AS typeName
				,'' AS Email
				,'' AS FName
				,'' AS LName
				,'' AS ClassesToComplete
				,'' AS classCount
				,0 AS sortorder
				,'' AS bitDisplay
				,'' AS bitRegistration
				/* END  For Tracker 20377 - Collect whether the class is in cart for that learner.-*/
				,'' AS int_certificate_id
				/* Added July 11 2012 Tracker 30411 Neo 00110*/
				,'' AS bit_multiple_elective_groups /* Added July 11 2012 Tracker 30411 Neo 00110*/
				/* Begin Author 00097 : Neo AI Course Series change 19 July 2011*/
				, (
					SELECT str_course_series_type_name FROM tbl_course_series_type
					WHERE int_course_series_type_id = csj.int_course_series_type_id
					) AS CourseSeriesType
				,0 AS SeriesGroupMatFeeAdv
				/* End Author 00097 : Neo AI Course Series Use Case :19 July 2011*/
				/* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/
				,(
					SELECT sum(ISNULL(int_reserved_seats, 0))
					FROM tbl_certificates_classes
					WHERE int_class_id = c.ClassID
					) AS int_certificate_reserved_seats
				,(
					SELECT sum(ISNULL(int_remaining_seats, 0))
					FROM tbl_certificates_classes
					WHERE int_class_id = c.ClassID
					) AS int_certificate_remaining_seats
				,(
					SELECT sum(ISNULL(int_reserved_seats, 0))
					FROM tbl_class_course_series_group tccsg1
					INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid
					WHERE int_class_id = c.ClassID
						AND STATUS <> 'I'
					) AS int_courseseries_reserved_seats /*Tracker 38702 jan 14 2014 Neo 0126 */
				,(
					SELECT sum(ISNULL(int_remaining_seats, 0))
					FROM tbl_class_course_series_group tccsg1
					INNER JOIN courseseries cs1 ON tccsg1.int_course_series_id = cs1.courseseriesid
					WHERE int_class_id = c.ClassID
						AND STATUS <> 'I'
					) AS int_courseseries_remaining_seats /*Tracker 38702 jan 14 2014 Neo 0126 */
				/* End AI Certificates Added Features 2011-10-14 Feature Change 1 */
				,IsNull((
						SELECT count(registrationid)
						FROM registrations
						WHERE classid = c.classid
							AND iscancelled = 0
							AND int_certificate_id > 0
						), 0) AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */
				/* BEGIN PAV 2 May 2012 Class listing grouping  */
				,co.ExternalCourseProviderID
				/* END PAV 2 May 2012 Class listing grouping  */
				,CASE
					WHEN (
							LEN(cl.minage) > 0
							AND LEN(cl.maxage) > 0
							)
						THEN convert(VARCHAR, cl.minage) + ' - ' + convert(VARCHAR, cl.maxage)
					ELSE ''
					END AS ClassAges
				/* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				,'' AS UseProgramSection
				,'' AS ProgramSectionStartDate
				,'' AS ProgramSectionEndDate
				,'' AS isAllProgramSectionClassesAvailable
				,'' AS bit_use_prerequisite
				,'' AS isProgramSectionSeatsAvailable
				/* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */
				,cl.str_limit_self_reg_mbrship_type_ids
				,(
					SELECT SUM(CONVERT(INT, bit_display_classes_members_only)) AS bit_display_classes_members_only
					FROM MembershipTypes mt
					LEFT JOIN Classes c ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
					WHERE c.ClassID IN (CL.classid)
					) AS bit_display_classes_members_only
				,(
					SELECT SUM(CONVERT(INT, bit_allow_student_purchase_mbrship)) AS bit_allow_student_purchase_mbrship
					FROM MembershipTypes mt
					LEFT JOIN Classes c ON ',' + c.str_limit_self_reg_mbrship_type_ids + ',' LIKE '%,' + CAST(mt.MembershipTypeID AS VARCHAR(255)) + '%'
					WHERE c.ClassID IN (CL.classid)
					) AS bit_allow_student_purchase_mbrship
				/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110 */
				,CASE
					WHEN c.contactPhone IS NULL
						THEN co.contactPhone
					ELSE c.contactPhone
					END AS contactphonenumber
				,c.ContactHrs
				,c.CEUs
				,c.flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/
				/* Begin NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,stuff((
						SELECT ',' + CAST(cf.NAME AS VARCHAR(100)) + '||' + CAST(CAST(cl.CostNoMaterialsFee - clf.DiscountAmount_1 AS money) AS VARCHAR(100))
						FROM ClassFees clf
						LEFT JOIN CourseFees cf ON clf.CourseFeeID = cf.CourseFeeID
						WHERE ClassID = cl.classID
							AND clf.STATUS = 'A'
							AND cf.NAME > ''
						FOR XML path('')
						), 1, 1, '') optionalAttributeDiscount
				/* End NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,tcdt.str_delivery_type
				/* Begin NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,ISNULL(c.QualityScore, 0) AS QualityScore
				,ISNULL((
						SELECT int_quality_score_upper_limit
						FROM featureconfiguration
						WHERE regionid = 1
						), 1) AS QualityScoreUpperLimit
				,ISNULL((
						SELECT SUM(QualityScore)
						FROM Classes
						WHERE courseid = c.CourseID
						), 1) AS QualityScoreSum
				,(
					SELECT COUNT(ClassID)
					FROM Classes
					WHERE courseid = c.CourseID
						AND ISNULL(QualityScore, 0) > 0
					) AS ClassesWithQualityScore
				/* End NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,1 AS bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */
				/* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				
				/* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				,(
					SELECT TOP 1 LTrim(FName) + ',' + LName AS InstructorName
					FROM tbl_class_instructor
					INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id
					WHERE tbl_class_instructor.int_class_id = c.classid
					ORDER BY InstructorName
					) AS str_instructorname
				/*extra cols for QofQ sorting*/
				, lower(cl.ClassName) as ClassNameSort
				, lower(CL.Location) as LocationSort
				, lower(CL.CourseNumber) as CourseNumberSort
				, lower((
						SELECT TOP 1 CASE
								WHEN LEN(LTrim(RTrim(CatalogName))) > 0
									THEN CatalogName
								ELSE LTrim(FName) + ',' + LName
								END AS InstructorName
						FROM tbl_class_instructor
						INNER JOIN instructors ON instructors.instructorid = tbl_class_instructor.int_instructor_id
						WHERE tbl_class_instructor.int_class_id = c.classid
						ORDER BY InstructorName
						)) AS InstructorNameSort
				/*extra cols for QofQ sorting*/
				, cl.dat_public_registration_begin AS TermPublicRegistrationBegin
				, c.TermID
				, NULL str_series_icon
				, NULL str_series_icon_alt_descr
				
					, 0 AS AvailableItemCount
				
				, cl.bit_allow_wcg_registration
				, CL.bit_allow_waiting_lists
				, CL.str_default_text_for_call_message_class
				
				,CL.int_certificate_group_id
				
					, 0 AS bit_use_class_prerequisites
				
			FROM @ClassIDTable1 CT1
				INNER JOIN tbl_class_listing CL ON CT1.ClassListingID = CL.ClassListingID
				/* PAV Tracker 25230 7/26/2011 added for full-text change  */
				/* PAV Tracker 26142 9/15/2011 altered to LEFT to accomodate using keyword field for ClassID - plus added use of FeeTextTable in AND criteria below */
				INNER JOIN classes c ON cl.classid = c.classid
				INNER JOIN Courses co ON co.CourseID = c.CourseID
				
				LEFT JOIN (SELECT ClassID, count(classid) as WaitingListCount FROM waitingLists group by ClassID) wl on cl.classid = wl.classid
				LEFT JOIN (select int_class_id, count(int_class_id) as ReservationCount from tbl_cart_class_detail group by int_class_id) ccd on ccd.int_class_id = cl.classid
				/* LEFT JOIN(SELECT int_time_period_id as TimePeriod from tbl_time_periods) tp on cd.int_time_period_id= tp.TimePeriod */
				LEFT JOIN (
					SELECT s.int_class_id
						, min(s.int_series_group_id) int_series_group_id
						, MIN(ISNULL(cs.int_course_series_type_id, 3)) int_course_series_type_id /*default is series?*/
					FROM tbl_class_course_series_group s
					INNER JOIN courseseries cs ON s.int_course_series_id = cs.courseseriesid
					INNER JOIN courseseriesgroups csg ON s.int_Series_group_id = csg.SeriesGroupID
					WHERE cs.STATUS = 'A'
						AND csg.STATUS = 'A'
						AND (
							SELECT COUNT(ClassID)
							FROM Classes
							WHERE classid IN (
									SELECT int_class_id
									FROM tbl_class_course_series_group
									WHERE int_Series_group_id = s.int_Series_group_id
									)
								AND Classes.STATUS = 'X'
							) = 0
					GROUP BY s.int_class_id
				) csj ON csj.int_class_id = cl.classid /* JS 10/29/2009 tracker 16372 Tracker 42084 Jul 24 2014 neo 00126 */
				/* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */
				LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID
				LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID
				LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */
			WHERE 1 = 1
			/* PAV Tracker 26142 9/15/2011 moved to here to accomodate using keyword field for ClassID - when a join doesn't work */
			
			AND c.status IN ('A','C')/* JS 11/15/2009 Tracker 16558 */
			/*
				Below condition for checking whether user can register on the basis of
				comparison with display start on AND display end on date for open-ended schedule type
			*/
			AND
				
					DateAdd(hh, @TimeZoneShift, dbo.fun_GetDateCT()) BETWEEN cl.AppearanceStartDate
							AND DateAdd(ms, - 10, CAST(CL.AppearanceEndDate AS DATETIME))
				
			/*
				Below condition for checking whether students can register
				on basis of comparison with public registration begins AND public registration closes
			*/
			
			AND c.IsContractTraining = 0/* TRACKER 36774 AUG 20 2013 NEO 0126*/
			/* Begin:Tracker 39813 Apr 14 2014 Neo 0126 */
			
			/* End:Tracker 39813 Apr 14 2014 Neo 0126 */
			/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
		/**************************************************************************************************/
		/******************** End select from first part of union for classes *****************************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from second part of union for course series ********************/
		/**************************************************************************************************/
		
			/*00097 11/10/2011 Tracker 27177 */
			UNION
			/* gets Course Series */
			SELECT DISTINCT
				
				0 AS Rank
				,0 AS Rank2
				
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				,'' AS str_class_schedule_display
				,'' AS showLocation
				,'' AS showCity
				,'' AS showRoom
				,'' AS showCityLocation
				,'' AS InstructionalMethod
				,'' AS DaysOfWeek
				,'' AS Instructor
				,(
					SELECT URLText
					FROM Courses
					WHERE CourseID IN (
							SELECT CourseID
							FROM Classes
							WHERE ClassID = cl.ClassID
							)
					) AS CourseURLText
				,(
					SELECT str_url
					FROM tbl_class_detail
					WHERE int_class_id = cl.ClassID
					) AS ClassURL
				,(
					SELECT str_url_text
					FROM tbl_class_detail
					WHERE int_class_id = cl.ClassID
					) AS ClassURlText
				,
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				0 AS AllowQtyPurchase
				,/* NEO 00083 Membership enhancement UC 10 SS2 */
				ISNULL(csg.bit_requires_release_form, 0) AS RequiresReleaseForm
				/* Tracker 44934 Feb 16 2015 Neo 0126 */
				,'' AS int_age_limit
				/*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/
				,1 AS InCourseSeries

				/* 2/8/2012 tracker 28153 */
				,'' AS SeriesRegs
				,'' AS TotalRegs
				,'' AS ReservedSeats
				/* End Tracker 28153 */
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/
				,CL.DescriptionNoHTML AS description
				,CL.description AS descriptionFull
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/
				,(
					CL.TotalSeats - (
						SELECT isnull(SUM(ccsg1.int_reserved_seats), 0)
						FROM tbl_class_course_series_group ccsg1
						WHERE ccsg1.int_class_id = CL.ClassID
						)
					) AS ClassTotalSeats
				/*00097 01/27/2012 Tracker:27948*/
				,CL.ClassListingID
				,CL.ClassID
				,CL.CourseID
				,CL.SubCategoryID
				,CL.CourseNumber
				,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */
				,CL.CourseTitle
				,CL.NAME
				,CL.CourseURL
				,CL.URLText
				,CL.IsOnlineCourse
				,CL.Keywords
				,CL.ClassName
				,CL.CatalogID
				,CL.SectionNumber
				,CL.classStartDate
				,CL.classEndDate
				,CL.StartDate
				,CL.StartDateOrder
				,CL.EndDate
				,CL.EndDateOrder
				,CL.AppearanceStartDate
				,CL.AppearanceEndDate
				,CL.HotPixStartDate
				,CL.HotPixEndDate
				,CL.dat_public_registration_begin
				,CL.dat_public_registration_closes
				,CL.dat_staff_registration_begin
				,CL.str_alt_subcategory_ids
				,CL.IsFeaturedSpeaker
				,CL.ClassRating
				,CL.ClassRatingCount
				,CL.NumberSessions
				,CL.NumberWeeks
				,CL.DaysOfWeek
				,CL.Cost
				,CL.CostNoMaterialsFee
				,CL.MaterialsCost
				,CL.LocationID
				,CL.Location
				,CL.City
				,CL.ClassRoom
				,CL.SpecialNotice
				,CL.TotalSeats
				,CL.InternalComments
				,CL.Comments
				,CL.PublicRegistration
				,CASE
					WHEN CL.contactPhone <> ''
						THEN CL.contactPhone
					ELSE ?/* Tracker 40369 Jan 16 2015 Neo 00159 */
					END AS contactPhone
				,CL.LessonReleaseTimes
				,CL.NumberofSessions
				,CL.NumberofWeeks
				,CL.StartDate1
				,CL.EndDate1
				,CL.CourseLogoPath
				,CL.AltCourseLogoPath
				,CL.bit_display_course_logo
				,CL.TimeZoneHourAdjustment
				,CL.MinAge
				,CL.MaxAge
				,CL.str_class_time_display
				,CL.int_certificate_id
				,CL.str_class_icon
				
				,'' AS coursegraphic
				,sc.str_subCat_graphic
				,ca.str_Cat_graphic
				
				
				,cs.str_series_graphic AS str_class_graphic_filename
				
				,cs.str_series_graphic_alt_descr AS str_class_icon_alt_descr
				,CL.int_course_series_group_id
				,CL.int_course_series_id
				,CL.str_class_type
				,CL.chr_status
				,CL.int_duration_type_id
				,CL.int_quantity
				,CL.bit_display_to_public
				,CL.dat_publish_date
				
				, NULL AS  GoNumber
				
				,CL.int_program_section_id
				,CL.str_count_towards_membership_benefits
				,CL.StaffDisplayStartDate
				,CL.StaffDisplayEndDate
				,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,'' AS NAME
				,'' AS dat_staff_registration_begin
				,'' AS CourseNo
				,0 AS ScheduleType
				,0 AS DurationType
				,0 AS int_quantity
				,0 AS int_time_period_id
				,'' AS TimeDescription
				,0 AS registrationCount
				,0 remainingseats
				,0 AS FilledSeats
				
				,WL.WaitingListCount AS waitingList
				,0 bit_class_on_waiting_list
				,0 AS isfull
				
				/* 09/29/2011 00097 Lisa_CourseSeriesTesting_2011-9-30 Begin*/
				,CASE
					/* 10 July 2012 Poornima Added a check to see if the registration is a quantity registration */
					WHEN EXISTS (
							SELECT RegistrationID
							FROM Registrations R
							WHERE R.int_series_group_id = CL.int_course_series_group_id
								AND R.LearnerID = @LearnerID
								AND R.IsCancelled = 0
								AND IsNull(R.bit_qty_reg_additional, 0) = 0
							)
						THEN 1
					ELSE 0
					END AS bit_enrolled
				/*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */
				,0 AS bit_allow_multiple_registration
				/* 09/29/2011 00097 Lisa_CourseSeriesTesting_2011-9-30 End*/
				,0 AS bit_on_waiting_list
				/*   0 as ageLimit, */
				/* 09/29/2011 00097 For:Course Series  begin*/
				/* BEGIN: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F12 Date:18 July 2013 */
				
				/* END: NEO 00091 AI Company Qty Reg Use Cases 2013-02-21 - F12 Date:18 July 2013 */
				/* Aswin 20 Feb 2015 updated the incart logic*/
				,(
					SELECT CASE
							WHEN (
									SELECT COUNT(DISTINCT ccd.int_learner_id)
									FROM tbl_cart_class_detail ccd
									WHERE
										/* some customers use household as group having large number of members */
										
											ccd.int_learner_id IN (?)
										
										AND ccd.int_Series_group_id = ccsg.int_Series_group_id
									) = ?
								THEN 1
							ELSE 0
							END
					) AS learnerAlreadyInCart
				/* Aswin 20 Feb 2015 updated the incart logic*/
				
				/* 09/29/2011 00097 For:Course Series  End*/
				,'' AS typeName
				,'' AS Email
				,'' AS FName
				,'' AS LName
				,'' AS ClassesToComplete
				,'' AS classCount
				,1 AS sortorder
				,'' AS bitDisplay
				,'' AS bitRegistration
				,'' AS int_certificate_id
				/*Added July 11 2012 Tracker 30411 Neo 00110*/
				,'' AS bit_multiple_elective_groups
				/*Added July 11 2012 Tracker 30411 Neo 00110*/
				/* Begin Author 00097 : Neo AI Course Series change 19 July 2011*/
				,cst.str_course_series_type_name AS CourseSeriesType
				,csg.SeriesGroupMatFeeAdv
				/* End Author 00097 : Neo AI Course Series Use Case :19 July 2011*/
				/* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/
				,NULL AS int_certificate_reserved_seats
				,NULL AS int_certificate_remaining_seats
				,NULL AS int_courseseries_reserved_seats
				,NULL AS int_courseseries_remaining_seats
				/* End AI Certificates Added Features 2011-10-14 Feature Change 1 */
				,NULL AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */
				/* BEGIN PAV 2 May 2012 Class listing grouping  */
				,NULL AS ExternalCourseProviderID
				/* END PAV 2 May 2012 Class listing grouping  */
				,'' AS ClassAges
				/* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				,'' AS UseProgramSection
				,'' AS ProgramSectionStartDate
				,'' AS ProgramSectionEndDate
				,'' AS isAllProgramSectionClassesAvailable
				,'' AS bit_use_prerequisite
				,'' AS isProgramSectionSeatsAvailable
				/* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				,'' AS str_limit_self_reg_mbrship_type_ids
				,'' AS bit_display_classes_members_only
				,'' AS bit_allow_student_purchase_mbrship
				,'' AS contactphonenumber
				/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				,0 AS ContactHrs
				,0 AS CEUs
				,0 AS flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/
				,'' AS optionalAttributeDiscount /* Added NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,tcdt.str_delivery_type
				/* Begin NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,0 AS QualityScore
				,0 AS QualityScoreUpperLimit
				,0 AS QualityScoreSum
				,0 AS ClassesWithQualityScore
				/* End NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,csg.bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */
				/* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				
				/* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				,'' AS str_instructorname
				/*extra cols for QofQ sorting*/
				, lower(cl.ClassName) as ClassNameSort
				, lower(CL.Location) as LocationSort
				, lower(CL.CourseNumber) as CourseNumberSort
				, '' InstructorNameSort
				/*extra cols for QofQ sorting*/
				, NULL AS TermPublicRegistrationBegin
				, NULL AS TermID
				, cs.str_series_icon
				, cs.str_series_icon_alt_descr
				, 0 AS AvailableItemCount /* change to series count */
				, cl.bit_allow_wcg_registration
				, CL.bit_allow_waiting_lists
				, CSG.str_default_text_for_call_message_class
				
				,CL.int_certificate_group_id
				, 0 AS bit_use_class_prerequisites
				
			FROM @ClassIDTable2 CT2
				INNER JOIN tbl_class_listing CL ON CT2.ClassListingID = CL.ClassListingID
				INNER JOIN CourseSeriesGroups CSG ON CSG.SeriesGroupID = CL.int_course_series_group_id
				INNER JOIN tbl_course_series_type cst ON cst.int_course_series_type_id = CSG.int_course_series_type_id
				/* 09/29/2011 00097 For:Course Series  begin*/
				INNER JOIN tbl_class_course_series_group ccsg ON ccsg.int_Series_group_id = CL.int_course_series_group_id
				/* 09/29/2011 00097 For:Course Series  End*/
				/* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */
				LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID
				LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID
				INNER JOIN CourseSeries CS ON CS.CourseSeriesID = CSG.CourseSeriesID /* 04 APR 2013 NEO 00088 TRACKER 34397 */
				LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */
				LEFT JOIN (SELECT int_course_series_group_id, count(int_course_series_group_id) as WaitingListCount FROM waitingLists where int_course_series_group_id is not null group by int_course_series_group_id) wl on cl.int_course_series_group_id = wl.int_course_series_group_id
			WHERE 1 = 1
				AND CS.Status = 'A'/* 04 APR 2013 NEO 00088 TRACKER 34397 */
				/*  NEO 00106  OCT 14 2014 Tracker 43197  */
				
				AND CS.bit_disp_cs_to_public = 1
				
				/*  NEO 00106  OCT 14 2014 Tracker 43197  */
				/* PAV 6 Feb 2012 added so a URL can retrieve an entire category - Tracker 28161  */
				
			/* End Author G : Neo AI Course Series Use Case :06 28 June 2011*/
		
		/**************************************************************************************************/
		/******************** End select from second part of union for course series **********************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from third part of union for Certificates **********************/
		/**************************************************************************************************/
		
			/*Begin Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
			UNION
			/* gets Certificates */
			SELECT DISTINCT
				
				0 AS Rank
				,0 AS Rank2
				
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				,'' AS str_class_schedule_display
				,'' AS showLocation
				,'' AS showCity
				,'' AS showRoom
				,'' AS showCityLocation
				,'' AS InstructionalMethod
				,'' AS DaysOfWeek
				,'' AS Instructor
				,(
					SELECT URLText
					FROM Courses
					WHERE CourseID IN (
							SELECT CourseID
							FROM Classes
							WHERE ClassID = cl.ClassID
							)
					) AS CourseURLText
				,(
					SELECT str_url
					FROM tbl_class_detail
					WHERE int_class_id = cl.ClassID
					) AS ClassURL
				,(
					SELECT str_url_text
					FROM tbl_class_detail
					WHERE int_class_id = cl.ClassID
					) AS ClassURlText
				/* MODIFY NEO 00106  Flexible Schedules 06 SEP 2013 */
				,0 AS AllowQtyPurchase
				/* NEO 00083 Membership enhancement UC 10 SS2 */
				,'' AS RequiresReleaseForm
				,'' AS int_age_limit
				/*Added Jan 17 2013 AI Membership Enhancement UC 10 Neo 00110*/
				,0 AS InCourseSeries
				/* 2/8/2012 tracker 28153 */
				,'' AS SeriesRegs
				/* '' AS NonSeriesRegs, */
				,'' AS TotalRegs
				,'' AS ReservedSeats
				/* End Tracker 28153 */
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue Begin*/
				,CL.DescriptionNoHTML AS description
				,CL.description AS descriptionFull
				/*Author :00097 date: 26 Aug 2011 Task: Certificate Issue End*/
				,(
					CL.TotalSeats - (
						SELECT isnull(SUM(ccsg1.int_reserved_seats), 0)
						FROM tbl_class_course_series_group ccsg1
						WHERE ccsg1.int_class_id = CL.ClassID
						)
					) AS ClassTotalSeats
				/*00097 01/27/2012 Tracker:27948*/
				,CL.ClassListingID
				,CL.ClassID
				,CL.CourseID
				,CL.SubCategoryID
				,CL.CourseNumber
				,CL.int_delivery_type_id /* Added Neo 00049 22nd Jan 2013 Flexible schedules II F-05 */
				,CL.CourseTitle
				,CL.NAME
				,CL.CourseURL
				,CL.URLText
				,CL.IsOnlineCourse
				,CL.Keywords
				,CL.ClassName
				,CL.CatalogID
				,CL.SectionNumber
				,CL.classStartDate
				,CL.classEndDate
				,CL.StartDate
				,CL.StartDateOrder
				,CL.EndDate
				,CL.EndDateOrder
				,CL.AppearanceStartDate
				,CL.AppearanceEndDate
				,CL.HotPixStartDate
				,CL.HotPixEndDate
				,CL.dat_public_registration_begin
				,CL.dat_public_registration_closes
				,CL.dat_staff_registration_begin
				,CL.str_alt_subcategory_ids
				,CL.IsFeaturedSpeaker
				,CL.ClassRating
				,CL.ClassRatingCount
				,CL.NumberSessions
				,CL.NumberWeeks
				,CL.DaysOfWeek
				,CL.Cost
				,CL.CostNoMaterialsFee
				,CL.MaterialsCost
				,CL.LocationID
				,CL.Location
				,CL.City
				,CL.ClassRoom
				,CL.SpecialNotice
				,CL.TotalSeats
				,CL.InternalComments
				,CL.Comments
				,CL.PublicRegistration
				,CL.contactPhone
				/*CL.InCourseSeries*/
				,CL.LessonReleaseTimes
				,CL.NumberofSessions
				,CL.NumberofWeeks
				,CL.StartDate1
				,CL.EndDate1
				,CL.CourseLogoPath
				,CL.AltCourseLogoPath
				,CL.bit_display_course_logo
				,CL.TimeZoneHourAdjustment
				,CL.MinAge
				,CL.MaxAge
				,CL.str_class_time_display
				,CL.int_certificate_id
				,CL.str_class_icon
				
				,'' AS coursegraphic
				,sc.str_subCat_graphic
				,ca.str_Cat_graphic
				
				
				,'' AS str_class_graphic_filename
				
				,CL.str_class_icon_alt_descr
				,CL.int_course_series_group_id
				,CL.int_course_series_id
				,CL.str_class_type
				,CL.chr_status
				,CL.int_duration_type_id
				,CL.int_quantity
				,CL.bit_display_to_public
				,CL.dat_publish_date
				
				, NULL AS  GoNumber
				
				,CL.int_program_section_id
				,CL.str_count_towards_membership_benefits
				,CL.StaffDisplayStartDate
				,CL.StaffDisplayEndDate
				,convert(DATETIME, convert(VARCHAR, cl.classStartDate, 101) + ' ' + IsNull(cl.starttime1, '0:00'), 121) AS ClStartDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,convert(DATETIME, convert(VARCHAR, cl.classEndDate, 101) + ' ' + cl.endtime1, 121) AS ClEndDate
				/*30 MAR 2012 NEO 00043 TRACKER 28840*/
				,tc.str_certificate_name AS NAME
				,'' AS dat_staff_registration_begin
				,'' AS CourseNo
				,0 AS ScheduleType
				,0 AS DurationType
				,0 AS int_quantity
				,0 AS int_time_period_id
				,'' AS TimeDescription
				,0 AS registrationCount
				,0 remainingseats
				,0 AS FilledSeats
				
				,0 AS waitingList
				,0 bit_class_on_waiting_list
				,0 AS isfull
				
				,0 AS bit_enrolled
				/*Added 06 AUG 2012 NEO 00049 Rochester Community Schools Register for a Class Multiple Times */
				,0 AS bit_allow_multiple_registration
				,0 AS bit_on_waiting_list
				/*  0 as ageLimit, */
				,0 AS learnerAlreadyInCart
				,tct.str_certificate_type_name AS typeName
				,st.Email AS Email
				/* Begin: May 31,2012 NEO 00091 Tracker:29766 */
				,st.FName
				,st.LName
				/* End: May 31,2012 NEO 00091 Tracker:29766 */
				,tc.int_courses_required_completion AS ClassesToComplete
				,tc.int_courses_offered AS classCount
				,1 AS sortorder
				,tc.bit_display_to_public AS bitDisplay
				,tc.bit_allow_public_registration AS bitRegistration
				,tc.int_certificate_id
				/* Added July 11 2012 Tracker 30411 Neo 00110*/
				,tc.bit_multiple_elective_groups
				/*Added July 11 2012 Tracker 30411 Neo 00110*/
				/* Begin Author 00098 : Neo AI Course Series change 20 July 2011*/
				,'' AS CourseSeriesType
				,0 AS SeriesGroupMatFeeAdv
				/* End Author 00098 : Neo AI Course Series Use Case :20 July 2011*/
				/* Neo D 00045 AI Certificates Added Features 2011-10-14 Feature Change 1 Date:Nov 17,2011*/
				,NULL AS int_certificate_reserved_seats
				,NULL AS int_certificate_remaining_seats
				,NULL AS int_courseseries_reserved_seats
				,NULL AS int_courseseries_remaining_seats
				/* End  AI Certificates Added Features 2011-10-14 Feature Change 1*/
				,NULL AS int_certificate_regs /* Added Feb 20 2012 Fun: Tracker 28377,28043 Neo s 00045 */
				/* BEGIN PAV 2 May 2012 Class listing grouping  */
				,NULL AS ExternalCourseProviderID
				/* END PAV 2 May 2012 Class listing grouping  */
				,'' AS ClassAges
				/* BEGIN: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				,tc.bit_use_program_sections AS UseProgramSection
				,'' AS ProgramSectionStartDate
				,'' AS ProgramSectionEndDate
				,'' AS isAllProgramSectionClassesAvailable
				,tc.bit_use_prerequisite
				,'' AS isProgramSectionSeatsAvailable
				/* END: NEO 00091 Date:Sep 20,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
				/* Begin Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				,'' AS str_limit_self_reg_mbrship_type_ids
				,'' AS bit_display_classes_members_only
				,'' AS bit_allow_student_purchase_mbrship
				,'' AS contactphonenumber
				/* End Dec 07 2012 AIMembership Enhancement UC 10 Neo 00110  */
				,0 AS ContactHrs
				,0 AS CEUs
				,0 AS flt_PLUs /*Modify NEO 00187 03 JUN 2015 PLU&Registration Transfer UC-04*/
				,'' AS optionalAttributeDiscount /* Added NEO 00049 Frederick Discount Price Display 12th Feb 2013 */
				,tcdt.str_delivery_type
				/* Begin NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,0 AS QualityScore
				,0 AS QualityScoreUpperLimit
				,0 AS QualityScoreSum
				,0 AS ClassesWithQualityScore
				/* End NEO 00088 AI VCCS Modifications Group 3  09 April 2014 */
				,1 AS bit_disp_cs_to_public /* 30 JUL 2014 NEO 00159 TRACKER 39672 */
				/* Begin NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				
				/* End NEO 00164 1st DEC 2014 OLLI Lottery UC-08 */
				,'' AS str_instructorname
				/*extra cols for QofQ sorting*/
				, lower(cl.ClassName) as ClassNameSort
				, lower(CL.Location) as LocationSort
				, lower(CL.CourseNumber) as CourseNumberSort
				, '' InstructorNameSort
				/*extra cols for QofQ sorting*/
				, NULL AS TermPublicRegistrationBegin
				, NULL AS TermID
				, NULL AS str_series_icon
				, NULL AS str_series_icon_alt_descr
				, 0 AS AvailableItemCount /* change to certificate_item count */
				, cl.bit_allow_wcg_registration
				, CL.bit_allow_waiting_lists
				, tc.str_default_text_for_call_message_class
				
				,CL.int_certificate_group_id
				
					, 0 AS bit_use_class_prerequisites
				
			FROM @ClassIDTable3 CT3
				INNER JOIN tbl_class_listing CL ON CT3.ClassListingID = CL.ClassListingID
				INNER JOIN tbl_certificates tc ON CL.int_certificate_id = tc.int_certificate_id
					AND tc.chr_status = 'P' /* Author: Neo s 00045 Description :  Certificates UC-16 Issue Fix Date: 25 Aug 2011 */
				INNER JOIN tbl_certificate_type tct ON tc.int_certificate_type_id = tct.int_certificate_type_id
				INNER JOIN Staff st ON tc.int_staff_contact_id = st.StaffID
				/* 09/15/2011 00097 For: Certificates UC-16 Issue List Begin*/
				INNER JOIN tbl_certificates_classes tcc1 ON tcc1.int_certificate_id = CL.int_certificate_id
				/* PAV 6 Feb 2012 added next 2 joins so a URL can retrieve an entire category - Tracker 28161 */
				LEFT JOIN SubCategories sc ON sc.SubCategoryID = cl.SubCategoryID
				LEFT JOIN Categories ca ON ca.CategoryID = sc.CategoryID
				/* 09/15/2011 00097 For: Certificates UC-16 Issue List End*/
				INNER JOIN tbl_class_listing tcl ON tcl.classid = tcc1.int_class_id /* TRACKER 36067 AUG 20 2013 NEO 0126*/
				LEFT JOIN tbl_course_delivery_types tcdt ON tcdt.int_delivery_type_id = cl.int_delivery_type_id /* 01 JULY 2014 NEO 00088 TRACKER 41851 */
				/* Begin : Tracker 49643 31 Dec 2015 Neo 00198 */
				
				/* End : Tracker 49643 31 Dec 2015 Neo 00198 */
			WHERE CL.str_class_type = 'cf' AND tc.bit_use_certificate_groups = 0
				AND CL.int_certificate_group_id = 0
				/* PAV 6 Feb 2012 added so a URL can retrieve an entire category - Tracker 28161  */
				
				/*Author: 00097 Date: 29 Aug 2011 Task: Certificate Issue begin*/
				
				   AND tc.bit_display_to_public = 1
				
				/*Author: 00097 Date: 29 Aug 2011 Task: Certificate Issue End*/
				/* BEGIN : TRACKER 36067 AUG 20 2013 NEO 0126*/
				
				/* END : TRACKER 36067 AUG 20 2013 NEO 0126*/
				/*Begin:Tracker 39813 Apr 14 2014 Neo 0126 */
				
				/*End:Tracker 39813 Apr 14 2014 Neo 0126 */
				/* BEGIN: NEO 00091 Date:Sep 25,2012 AI V7.3 Full-Time Certificate Use Cases 2012-08-03 UC-13 */
		
		/*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
		/**************************************************************************************************/
		/******************** End select from third  of union for Certificates ****************************/
		/**************************************************************************************************/

		/**************************************************************************************************/
		/******************** Begin select from fourth part of union for Program Sections *****************/
		/**************************************************************************************************/
		
		/**************************************************************************************************/
		/******************** End select from fourth part of union for Program Sections *******************/
		/**************************************************************************************************/

		/********************************************************************************************************/
		/******************** Begin select from fifth part of union for Certificate Groups **********************/
		/********************************************************************************************************/
		
		/*End Author: Neo 00098 Description : Certificates UC-16 , Date : 18 JUL 2011*/
		/********************************************************************************************************/
		/******************** Begin select from fifth part of union for Certificate Groups **********************/
		/********************************************************************************************************/

		/*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue Begin*/
		
			ORDER BY
			
					CL.str_class_type Desc ,
				ClassName,CLSTARTDATE,CourseNumber 
		/*Author: 00097 Date: 27 Aug 2011 Task: Certificate Issue End*/
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type = 'cf'

		UNION

		SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type = 'ps'
			AND isProgramSectionSeatsAvailable = 1
		

			UNION

			SELECT 0 AS total_rec_count
				,*
			FROM qGetClassListing
			WHERE str_class_type = 'cs'
				AND bit_disp_cs_to_public = 1
		

		UNION

		SELECT 0 AS total_rec_count
			,*
		FROM qGetClassListing
		WHERE str_class_type NOT IN (
				'cf'
				,'ps'
				,'cs'
				)

SELECT *
		FROM qGetClassListingPagination
		
			ORDER BY
				
						str_class_type DESC
						,
					ClassNameSort,ClStartDate,CourseNumberSort

select * from qSocialMedia order by int_order desc

q_customer_by_domain (Datasource=LumensCustomers, Time=0ms) in E:\Lumens\app_setup.cfc @ 02:53:35.035

CGI Variables:
AUTH_PASSWORD=
AUTH_TYPE=
AUTH_USER=
CERT_COOKIE=
CERT_FLAGS=
CERT_ISSUER=
CERT_KEYSIZE=
CERT_SECRETKEYSIZE=
CERT_SERIALNUMBER=
CERT_SERVER_ISSUER=
CERT_SERVER_SUBJECT=
CERT_SUBJECT=
CF_TEMPLATE_PATH=E:\Lumens\index.cfm
CONTENT_LENGTH=0
CONTENT_TYPE=
CONTEXT_PATH=
GATEWAY_INTERFACE=CGI/1.1
HTTPS=off
HTTPS_KEYSIZE=
HTTPS_SECRETKEYSIZE=
HTTPS_SERVER_ISSUER=
HTTPS_SERVER_SUBJECT=
HTTP_ACCEPT=*/*
HTTP_ACCEPT_ENCODING=
HTTP_ACCEPT_LANGUAGE=
HTTP_CONNECTION=
HTTP_COOKIE=
HTTP_HOST=v8template2.augusoft.net
HTTP_REFERER=https://v8template2.augusoft.net/index.cfm?method=ClassListing.ClassListingDisplay&int_category_id=3&int_sub_category_id=9&int_catalog_id=&frmCoursesMenu=1
HTTP_URL=/index.cfm?method=ClassListing.ClassListingDisplay&int_category_id=3&int_sub_category_id=9&int_catalog_id=
HTTP_USER_AGENT=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)
LOCAL_ADDR=172.31.100.167
PATH_INFO=
PATH_TRANSLATED=E:\Lumens\index.cfm
QUERY_STRING=method=ClassListing.ClassListingDisplay&int_category_id=3&int_sub_category_id=9&int_catalog_id=
REMOTE_ADDR=172.31.103.75
REMOTE_HOST=172.31.103.75
REMOTE_USER=
REQUEST_METHOD=GET
SCRIPT_NAME=/index.cfm
SERVER_NAME=v8template2.augusoft.net
SERVER_PORT=80
SERVER_PORT_SECURE=0
SERVER_PROTOCOL=HTTP/1.1
SERVER_SOFTWARE=Microsoft-IIS/10.0
WEB_SERVER_API=

Cookie Variables:
LUMPERSIST=3B91FD9D623344E1EFAD1D73DF32B508

Session Variables:
actortypeid=90
addtoken=CFID=9908406&CFTOKEN=99acffdab76e4fe5-64F2D92B-9916-C32F-BDBEB74777257585&jsessionid=D46267C6CC56F22364C0071B66E193D7.cfusion
allowunmaskeddata=0
appname=v8Template2
arrtrackflowregister=Array (3)
basecfcs=versions.v81QA
basecode=/versions/v81QA
bit_canvas_failed_process=false
bit_member=0
customcct=
customcfcs=Customers.v8Template2
customerid=487
custompath=/Customers/v8Template2/custom
debug=0
debugoutput=1
docxdir=DocxHTML
domain=v8template2.augusoft.net
ds=v8Template2
enabledlivechat=false
enabledsupportwidget=false
enteredsiteusingipaddress=1
foundeo_firewall_ip=3.144.189.177
foundeo_firewall_ua=Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)
loggedin=No
loggeduseremail=
loggeduserfname=Guest
loggeduserlname=
membershiptypeid=0
menuid=1
orgid=1
privacypolicyack=
regionid=1
sessionid=D46267C6CC56F22364C0071B66E193D7.cfusion
sitepath=/Customers/v8Template2
sslreturnurl=https://v8template2.augusoft.net/
stylesheetpath=/Customers/v8Template2/StyleSheets/style.css
timeoutminutes=60
urltoken=CFID=9908406&CFTOKEN=99acffdab76e4fe5-64F2D92B-9916-C32F-BDBEB74777257585&jsessionid=D46267C6CC56F22364C0071B66E193D7.cfusion
version=v81QA

URL Parameters:
int_catalog_id=
int_category_id=3
int_sub_category_id=9
method=ClassListing.ClassListingDisplay