Subjects and Courses

Course Values

 

Course Delivery Method Values

 

Course Meeting Types Values

 

Course Attribute Values

 

Course Level Values

 

Course Equivalent Values

 

Course PreReq Values

 

XML

 

<?xml version="1.0" encoding="utf-8" ?>

<!--

   *

   * CampusVueCourseImport.xml

   * Copyright (C) 2006-2009, Ad Astra Information Systems, LLC.

   * All Rights Reserved

   *

   * Note: This spoke file requires an entry in the SystemSetting table with KeyName=PrimarySisConnectionString

-->

<ATE xmlns="http://aais.com/schema/7.4.0/ATE/ATE"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"

    xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"

    version="2.0" jobType="Import" validMode="Batch">

 

 <Summary>

   <![CDATA[Generic CampusVue MsSql Course Import.]]>

 </Summary>

 <JobParameters>

   <JobParameterGroup caption="CampusVue Database Settings">

     <JobParameter name="SISUser" caption="User Id"/>

     <JobParameter name="SISPass" caption="Password" password="true"/>

   </JobParameterGroup>

   <SystemParameter name="SisConnectString" settingKey="PrimarySisConnectionString"/>

 

   <ListParameter name="CampusId" caption="Campus" listSourceEntity="Campus" listSourceKeyProperty="SisKey" allowMultiple="true"/>

 </JobParameters>

 

 <EndPoints>  

   <DbmsEndPoint name="PrimarySIS" adapterType="MsSql">

     <Parameters>

       <Parameter name="SisConnectString" ref="SisConnectString"/>

       <Parameter name="SISUID" ref="SISUser"/>

       <Parameter name="SISPwd" ref="SISPass"/>

     </Parameters>

     <ConnectionString>

       <![CDATA[${SisConnectString};User Id=${SISUID};Password=${SISPwd};]]>

     </ConnectionString>

   </DbmsEndPoint>

 </EndPoints>

 

 <Pipeline>

   <Sources>

 

           <!-- Top Level (Course) Data -->          

     <Source name="Courses" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

                         <Sql >

                                 <![CDATA[

SELECT

   AdCourse.AdCourseId AS CourseSisKey,

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(AdCourse.Code, ' ', ''), PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')), LEN(AdCourse.Code) )

   ELSE

       AdCourse.Code

   END AS CourseCourseNumber,

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(AdCourse.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) -1) )

   ELSE

       AdCourse.Code

   END AS SubjectSisKey,

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(AdCourse.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) -1) )

   ELSE

       AdCourse.Code

   END AS SubjectSubjectCode,

   CASE WHEN PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) > 1 THEN

       SUBSTRING( REPLACE(AdCourse.Code, ' ', ''), 1, (PATINDEX('%[0-9]%', REPLACE(AdCourse.Code, ' ', '')) -1) )

   ELSE

       AdCourse.Code

   END AS SubjectName,

   AdCourse.Descrip AS CourseCourseTitle,

   AdCourse.Descrip AS CourseTitleSisKey,

   AdCourse.Descrip AS CourseTitleName,

   'false' AS CourseArrangedCourse,

   'false' AS CourseDoNotOptimize,

   'true' AS CourseIsInCatalog,

   AdCourse.Credits AS CourseLowCreditHours,

   AdCourse.Credits AS CourseHighCreditHours,

   AdCourse.Active AS CourseIsActive,

   AdCourseLevel.AdCourseLevelID AS CourseLevelSisKey,

   AdAttribute.AdAttributeID AS CourseAttributeCodeSisKey

FROM    AdCourse

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

LEFT OUTER JOIN AdCourseLevel ON AdCourse.AdCourseLevelID = AdCourseLevel.AdCourseLevelID

LEFT OUTER JOIN AdCourseAttribute ON AdCourse.AdCourseID = AdCourseAttribute.AdCourseID

LEFT OUTER JOIN AdAttribute ON AdCourseAttribute.AdAttributeID = AdAttribute.AdAttributeID

WHERE SyCampusList.SyCampusID = @CampusId

                   ]]>

               </Sql>

               <Schema entityName="Course" groupName="Courses"/>

           </Source>

 

 

           <!-- Detail (Child) Data -->

           <Source name="CourseDeliveryMethods" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

                         <Sql >

                                 <![CDATA[

SELECT

   AdCourse.AdCourseId AS CourseSisKey,

   CAST(AdCourse.AdCourseId AS varchar) + '_Standard' AS CourseDeliveryMethodSisKey,

   'Standard' AS DeliveryMethodSisKey,

   'Standard' AS DeliveryMethodName,

   'Standard' AS TermTypeSisKey,

   'Standard' AS TermTypeName,

   'false' AS CourseDeliveryMethodScheduleBackToBackMtgs,

   10 AS CourseDeliveryMethodMaxNumSectionsCanCreate,

   30 AS CourseDeliveryMethodMaxEnrollment,

   5 AS CourseDeliveryMethodMinEnrollment,

   15 AS CourseDeliveryMethodIdealEnrollment,

   260 AS CourseDeliveryMethodPatternTypeCode,

   1 AS CourseDeliveryMethodRepeatLimit,

   AdCourse.Active AS CourseDeliveryMethodIsActive

FROM    AdCourse

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

WHERE SyCampusList.SyCampusID = @CampusId

                   ]]>

               </Sql>

               <Schema entityName="CourseDeliveryMethod" groupName="CourseDeliveryMethods"/>

           </Source>

 

     <Source name="CourseMeetingTypes" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

   AdCourse.AdCourseId AS CourseSisKey,

   CAST(AdCourse.AdCourseId AS varchar) + '_Standard' AS CourseDeliveryMethodSisKey,

   CAST(AdCourse.AdCourseId AS varchar) + '_' + CAST(AdCourseType.AdCourseTypeID AS varchar) AS CourseMeetingTypeSisKey,

   AdCourseType.AdCourseTypeID AS MeetingTypeSisKey,

   AdCourseType.Code AS MeetingTypeName,

   AdCourse.Hours AS CourseMeetingTypeContactHours

FROM    AdCourseType

INNER JOIN AdCourse ON AdCourse.AdCourseTypeID = AdCourseType.AdCourseTypeID

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

WHERE SyCampusList.SyCampusID = @CampusId

                     ]]>

       </Sql>

       <Schema entityName="CourseMeetingType" groupName="CourseMeetingTypes"/>

     </Source>

 

     <!--<Source name="CourseAttributes" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

 AdCourse.AdCourseId AS CourseSisKey,

 AdAttribute.AdAttributeID AS AttributeCodeSisKey

FROM AdCourse

INNER JOIN AdCourseAttribute ON AdCourse.AdCourseID = AdCourseAttribute.AdCourseID

INNER JOIN AdAttribute ON AdCourseAttribute.AdAttributeID = AdAttribute.AdAttributeID

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

INNER JOIN SyCampus ON SyCampus.SyCampusID = SyCampusList.SyCampusID

WHERE SyCampus.Code = @CampusCode

                     ]]>

       </Sql>

       <Schema entityName="AttributeCode" groupName="AttributeCodes"/>

     </Source>-->

 

     <!--<Source name="CourseLevels" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

 AdCourse.AdCourseId AS CourseSisKey,

 AdCourseLevel.AdCourseLevelID AS LevelSisKey

FROM AdCourse

INNER JOIN AdCourseLevel ON AdCourse.AdCourseLevelID = AdCourseLevel.AdCourseLevelID

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

INNER JOIN SyCampus ON SyCampus.SyCampusID = SyCampusList.SyCampusID

WHERE SyCampus.Code = @CampusCode

                     ]]>

       </Sql>

       <Schema entityName="Level" groupName="Levels"/>

     </Source>-->

 

     <Source name="CourseEquivalents" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

   AdCourseEquiv.AdCourseId AS CourseSisKey,

   AdCourseEquiv.AdEquivId AS AssociatedCourseSisKey,

   'EQ' AS CourseAssociationTypeName,

   AdCourseEquiv.AdCourseEquivID AS CourseAssociationSisKey,

   1 as CourseAssociationIsActive

FROM AdCourse

INNER JOIN AdCourseEquiv ON AdCourse.AdCourseID = AdCourseEquiv.AdEquivID

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

WHERE SyCampusList.SyCampusID = @CampusId

                     ]]>

       </Sql>

       <Schema entityName="CourseAssociation" groupName="CourseAssociations"/>

     </Source>

 

     <Source name="CoursePreReqs" endPoint="PrimarySIS">

       <SqlParameters>

         <SqlParameter sqlBind="@CampusId" name="CampusId" ref="CampusId"  />

       </SqlParameters>

       <Sql >

         <![CDATA[

SELECT

   AdCoursePreReq.AdCourseId AS CourseSisKey,

   AdCoursePreReq.AdPreReqID AS AssociatedCourseSisKey,

   'PR' AS CourseAssociationTypeName,

   AdCoursePreReq.AdCoursePreReqID AS CourseAssociationSisKey,

   1 as CourseAssociationIsActive

FROM AdCourse

INNER JOIN AdCoursePreReq ON AdCourse.AdCourseID = AdCoursePreReq.AdPreReqID

INNER JOIN SyCampusGrp ON AdCourse.SyCampusGrpID = SyCampusGrp.SyCampusGrpID

INNER JOIN SyCampusList ON SyCampusGrp.SyCampusGrpID = SyCampusList.SyCampusGrpID

WHERE SyCampusList.SyCampusID = @CampusId

                     ]]>

       </Sql>

       <Schema entityName="CourseAssociation" groupName="CourseAssociations"/>

     </Source>

 

   </Sources>

 

   <Phases>

       <Phase name="ImportCourses">

         <Fetch ref="Courses"/>

         <Fetch ref="CourseDeliveryMethods"/>

         <Fetch ref="CourseMeetingTypes" />

         <!--<Fetch ref="CourseAttributes" />

             <Fetch ref="CourseLevels" />-->

         <Fetch ref="CourseEquivalents" />

         <Fetch ref="CoursePreReqs" />

 

         <Target endPoint="Astra">

           <AstraEnvironment>

 

             <Hub className="CourseHub">

               <Reader ref="Courses"/>

               <AlternateHubs>

                 <Hub className="CourseTitleHub"/>

                 <Hub className="SubjectHub"/>

                 <Hub className="LevelHub"/>

                 <Hub className="AttributeCodeHub"/>

               </AlternateHubs>

             </Hub>

 

             <Hub className="CourseDeliveryMethodHub">

               <Reader ref="CourseDeliveryMethods"/>

               <AlternateHubs>

                 <Hub className="DeliveryMethodHub"/>

                 <Hub className="TermTypeHub"/>

               </AlternateHubs>

             </Hub>

 

             <Hub className="CourseMeetingTypeHub">

               <Reader ref="CourseMeetingTypes"/>

               <AlternateHubs>

                 <Hub className="MeetingTypeHub"/>

               </AlternateHubs>

             </Hub>

 

               <!--<AlternateHubs reader="CourseAttributes">

                 <Hub className="AttributeCodeHub"/>

               </AlternateHubs>

 

               <AlternateHubs reader="CourseLevels">

                 <Hub className="LevelHub"/>

               </AlternateHubs>-->

 

             <Hub className="CourseAssociationHub">

               <Reader ref="CourseEquivalents"/>

               <Reader ref="CoursePreReqs"/>

             </Hub>

           </AstraEnvironment>

         </Target>          

       </Phase>

 

       <Phase name="PostReverseEquivCleanUp">

           <Target endPoint="Astra">

               <AstraEnvironment>

                 <Hub className="CourseReverseEquivCoReqPostProcesserHub">

                   <NoReaders/>

                 </Hub>

               </AstraEnvironment>

           </Target>

       </Phase>

 

 

   </Phases>

 

 </Pipeline>

</ATE>

 

Page url: ?subjects_and_courses.htm