|
Table and Data Details |
|
|
|
|
|
Table and Data Details |
|
|
|
|
PeopleSoft Table and Data Detail
The interface selects data from various PeopleSoft tables to make academic sections, meetings, cross-list information, instructors, subjects, courses, and activity statuses available within Astra Schedule. The following tables detail the PeopleSoft fields being used and their corresponding Astra Schedule values.
Academic Sections
SIS Database Value |
Astra Schedule Value |
PS_CLASS_TBL.STRM||'-'||PS_CLASS_TBL.CRSE_ID||'-'||PS_CLASS_TBL.CRSE_OFFER_NBR||'-'||PS_CLASS_TBL.SESSION_CODE||'-'||PS_CLASS_TBL.CLASS_SECTION||'-'||PS_CLASS_TBL.CLASS_NBR |
SisKey |
PS_CLASS_TBL.STRM||'-'||PS_CLASS_TBL.CRSE_ID||'-'||PS_CLASS_TBL.CRSE_OFFER_NBR||'-'||PS_CLASS_TBL.SESSION_CODE||'-'||PS_CLASS_TBL.CLASS_SECTION||'-'||PS_CLASS_TBL.CLASS_NBR |
SectionID |
PS_CLASS_TBL.STRM |
Term |
PS_CLASS_TBL.SUBJECT |
Subject |
PS_CLASS_TBL.CATALOG_NBR |
Course |
PS_CLASS_TBL.CLASS_SECTION |
SectionNumber |
PS_CLASS_TBL.DESCR |
Title |
PS_CLASS_TBL.CAMPUS |
Campus |
PS_CLASS_TBL.ENRL_CAP |
MaxEnrollment |
PS_CLASS_TBL.ENRL_TOT |
Enrollment |
PS_CLASS_TBL.CLASS_STAT |
Status |
PS_CLASS_TBL.SSR_COMPONENT |
MeetingType |
Academic Section Meetings
SIS Database Value |
Astra Schedule Value |
PS_CLASS_MTG_PAT.STRM |
Term |
PS_CLASS_MTG_PAT.STRM||'-'||PS_CLASS_MTG_PAT.CRSE_ID||'-'||PS_CLASS_MTG_PAT.CRSE_OFFER_NBR||'-'||PS_CLASS_MTG_PAT.SESSION_CODE||'-'||PS_CLASS_MTG_PAT.CLASS_SECTION||'-'||PS_CLASS_MTG_PAT.CLASS_MTG_NBR |
SisKey |
PS_CLASS_MTG_PAT.CLASS_SECTION |
SectionNumber |
PS_CLASS_MTG_PAT.START_DT |
STARTDATE |
PS_CLASS_MTG_PAT.END_DT |
ENDDATE |
PS_CLASS_MTG_PAT.MEETING_TIME_START |
STARTTIME |
PS_CLASS_MTG_PAT.MEETING_TIME_END |
ENDTIME |
PS_CLASS_MTG_PAT.MON PS_CLASS_MTG_PAT.TUES PS_CLASS_MTG_PAT.WED PS_CLASS_MTG_PAT.THURS PS_CLASS_MTG_PAT.FRI PS_CLASS_MTG_PAT.SAT PS_CLASS_MTG_PAT.SUN |
DAYSMET |
PS_FACILITY_TBL.BLDG_CD |
Building |
PS_FACILITY_TBL.ROOM |
Room |
CrossList Information
SIS Database Value |
Astra Schedule Value |
PS_SCTN_CMBND_TBL.STRM||'-'||PS_SCTN_CMBND.SESSION_CODE||'-'||PS_SCTN_CMBND.SCTN_COMBINED_ID||'-'||PS_SCTN_CMBND.INSTITUTION |
SisKey |
PS_SCTN_CMBND_TBL.STRM |
Term |
PS_SCTN_CMBND_TBL.STRM||'-'||PS_SCTN_CMBND.SESSION_CODE||'-'||PS_SCTN_CMBND.SCTN_COMBINED_ID||'-'||PS_SCTN_CMBND.INSTITUTION |
CrossListNumber |
PS_SCTN_CMBND_TBL.ENRL_CAP |
CrossListMaxEnrollment |
PS_SCTN_CMBND_TBL.ENRL_TOT |
CrossListEnrollment |
Section Meeting Instructors
SIS Database Value |
Astra Schedule Value |
PS_CLASS_INSTR.STRM |
Term |
PS_CLASS_INSTR.EMPLID |
InstructorSisKey |
PS_NAMES.NAME |
FullName |
PS_NAMES.FIRST_NAME |
PersonFirstName |
PS_NAMES.LAST_NAME |
PersonLastName |
PS_NAMES.MIDDLE_NAME |
PersonMiddleName |
PS_EMAIL_ADDRESSES.EMAIL_ADDR |
PersonEMail |
PS_EMAIL_ADDRESSES.EMAIL_ADDR |
InstructorEMail |
XML
<?xml version="1.0" encoding="utf-8" ?>
<!--
*
* PeopleSoftSectionImport.xml
* Copyright (C) 2006-2010, 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="Always">
<Summary><![CDATA[PeopleSoft Section Import.]]></Summary>
<JobParameters>
<!-- Hub Configuraton Options -->
<JobParameter name="DeleteAstraSectionsNotInImportSet" caption="" hidden="true" expr="true" type="bool"/>
<JobParameter name="DeleteAstraMeetingsNotInImportSet" caption="" hidden="true" expr="DeleteAstraSectionsNotInImportSet" type="bool"/>
<JobParameter name="DeleteCanceledSections" caption="" hidden="true" expr="true" type="bool"/>
<JobParameter name="ImportIncompleteSections" caption="" hidden="true" expr="true" type="bool"/>
<!-- Internal -->
<JobParameter name="SectionSisKey" caption="" hidden="true"/>
<JobParameter name="SynchronizeSectionDeletes" caption="" hidden="true" expr="DeleteAstraSectionsNotInImportSet && !Realtime" type="bool"/>
<JobParameter name="SynchronizeMeetingDeletes" caption="" hidden="true" expr="DeleteAstraMeetingsNotInImportSet && !Realtime" type="bool"/>
<!-- SIS Connection -->
<JobParameterGroup caption="PeopleSoft Database Settings">
<JobParameter name="SISUser" caption="User Id" />
<JobParameter name="SISPass" caption="Password" password="true"/>
</JobParameterGroup>
<SystemParameter name="SisConnectString" settingKey="PrimarySisConnectionString"/>
<!-- Campus -->
<ListParameter name="CampusCode" caption="Campuses" listSourceEntity="Campus" listSourceKeyProperty="SisKey" allowMultiple="true"/>
<!-- Term -->
<ListParameter name="TermCode" caption="Terms" listSourceEntity="Term" listSourceKeyProperty="SisKey" allowMultiple="true"/>
<!-- Audit Table -->
<JobParameter name="UseAuditTable" caption="Use Audit Table (Y/N)" validationExpression="[NYny]" maxLength="1"/>
</JobParameters>
<EndPoints>
<DbmsEndPoint name="PrimarySIS" adapterType="Oracle">
<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>
<Source name="AstraSections" endPoint="PrimarySIS">
<Astra from="Section" rootElement="Sections">
<Select column="SisKey" as="SectionSisKey" />
<Where if="Batch">
<![CDATA[
SisKey != null
&& CampusId in ${CampusCode}
&& TermId in ${TermCode}
]]>
</Where>
<Where if="OnDemand || Realtime">
<![CDATA[
SisKey == ${SectionSisKey}
]]>
</Where>
<OrderBy column="SectionSisKey" direction="ascending"/>
</Astra>
</Source>
<Source name="AstraSectionMeetings" endPoint="PrimarySIS">
<Astra from="SectionMeeting" rootElement="SectionMeetings">
<Select column="SisKey" as="SectionMeetingSisKey" />
<Select column="Section.SisKey" as="SectionSisKey" />
<Where if="Batch">
<![CDATA[
SisKey != null
&& Section.CampusId in ${CampusCode}
&& Section.TermId in ${TermCode}
]]>
</Where>
<Where if="OnDemand || Realtime">
<![CDATA[
Section.SisKey == ${SectionSisKey}
]]>
</Where>
<OrderBy column="SectionMeetingSisKey" direction="ascending"/>
</Astra>
</Source>
<!-- Top Level (Section) Data -->
<Source name="Sections" endPoint="PrimarySIS" xsd="file:\\SIS\SectionSpokeData.xsd">
<!-- Uncomment the following to Add a Runtime Parameters-->
<SqlParameters>
<!-- Batch -->
<SqlParameter sqlBind=":CampusCode" name="CampusCode" ref="CampusCode" if="Batch"/>
<SqlParameter sqlBind=":TermCode" name="TermCode" ref="TermCode" if="Batch"/>
<!-- OnDemand and Realtime -->
<SqlParameter sqlBind=":TermCode" name="TermCode_NonBatch" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="1" if="!Batch"/>
<SqlParameter sqlBind=":CourseId" name="CourseId" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="2" if="!Batch"/>
<SqlParameter sqlBind=":CourseOfferingNumber" name="CourseOfferingNumber" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="3" if="!Batch"/>
<SqlParameter sqlBind=":SessionCode" name="SessionCode" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="4" if="!Batch"/>
<SqlParameter sqlBind=":ClassSection" name="ClassSection" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="5" if="!Batch"/>
</SqlParameters>
<Sql>
<![CDATA[
SELECT DISTINCT
c.strm||'_'||c.crse_id||'_'||c.crse_offer_nbr||'_'||c.session_code||'_'||c.class_section as SectionSisKey,
c.strm as TermSisKey,
c.campus as CampusSisKey,
c.subject as SubjectSisKey,
c.subject as SubjectName,
c.subject as SubjectCode,
c.descr as CourseName,
c.descr as CourseCourseTitle,
c.descr as SectionSectionTitle,
c.catalog_nbr as CourseNumber,
c.subject || '_' || c.catalog_nbr AS CourseSisKey,
c.subject || '_' || c.catalog_nbr || '_' || 'Standard' AS CourseDeliveryMethodSisKey,
c.class_section as SectionSectionCode,
c.enrl_cap as SectionSisMaxEnrollment,
c.enrl_tot as SectionSisEnrollment,
'Standard' AS DeliveryMethodSisKey,
'Standard' AS DeliveryMethodName,
case c.class_stat when 'X' then 'false' else 'true' end as SectionIsActive,
case c.class_stat when 'X' then 'true' else 'false' end as SectionIsCanceled
FROM ps_class_tbl c
]]>
</Sql>
<Sql if="Batch">
<![CDATA[
WHERE c.strm = :TermCode
AND c.campus = :CampusCode
]]>
</Sql>
<Sql if="!Batch">
<![CDATA[
WHERE c.strm = :TermCode
AND c.crse_id = :CourseId
AND c.crse_offer_nbr = :CourseOfferingNumber
AND c.session_code = :SessionCode
AND c.class_section = :ClassSection
]]>
</Sql>
<Schema entityName="Section" groupName="Sections"/>
</Source>
<Source name="CourseMeetingTypes" endPoint="PrimarySIS">
<SqlParameters>
<!-- Batch -->
<SqlParameter sqlBind=":CampusCode" name="CampusCode" ref="CampusCode" if="Batch"/>
<SqlParameter sqlBind=":TermCode" name="TermCode" ref="TermCode" if="Batch"/>
<!-- OnDemand and Realtime -->
<SqlParameter sqlBind=":TermCode" name="TermCode_NonBatch" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="1" if="!Batch"/>
<SqlParameter sqlBind=":CourseId" name="CourseId" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="2" if="!Batch"/>
<SqlParameter sqlBind=":CourseOfferingNumber" name="CourseOfferingNumber" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="3" if="!Batch"/>
<SqlParameter sqlBind=":SessionCode" name="SessionCode" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="4" if="!Batch"/>
<SqlParameter sqlBind=":ClassSection" name="ClassSection" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="5" if="!Batch"/>
</SqlParameters>
<Sql >
<![CDATA[
SELECT
c.subject || '_' || c.catalog_nbr AS CourseSisKey,
c.subject || '_' || c.catalog_nbr || '_Standard_' || c.ssr_component AS CourseMeetingTypeSisKey,
c.subject || '_' || c.catalog_nbr || '_Standard' AS CourseDeliveryMethodSisKey,
c.ssr_component MeetingTypeSisKey
FROM ps_class_tbl c
JOIN ps_class_mtg_pat m ON (m.crse_id = c.crse_id
AND m.strm = c.strm
AND m.crse_offer_nbr = c.crse_offer_nbr
AND m.session_code = c.session_code
AND m.class_section = c.class_section)
]]>
</Sql>
<Sql if="Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.campus = :CampusCode
]]>
</Sql>
<Sql if="!Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.crse_id = :CourseId
AND c.crse_offer_nbr = :CourseOfferingNumber
AND c.session_code = :SessionCode
AND c.class_section = :ClassSection
]]>
</Sql>
<Sql if="DeleteCanceledSections">
<![CDATA[
and c.class_stat <> 'X'
]]>
</Sql>
<Schema entityName="CourseMeetingType" groupName="CourseMeetingTypes"/>
</Source>
<Source name="SectionMeetings" endPoint="PrimarySIS" xslt="file://DBMS/Oracle/OracleSectionMeeting.xslt" xsd="file:\\SIS\SectionSpokeData.xsd" >
<!-- Uncomment the following to Add a Runtime Parameters-->
<SqlParameters>
<!-- Batch -->
<SqlParameter sqlBind=":CampusCode" name="CampusCode" ref="CampusCode" if="Batch"/>
<SqlParameter sqlBind=":TermCode" name="TermCode" ref="TermCode" if="Batch"/>
<!-- OnDemand and Realtime -->
<SqlParameter sqlBind=":TermCode" name="TermCode_NonBatch" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="1" if="!Batch"/>
<SqlParameter sqlBind=":CourseId" name="CourseId" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="2" if="!Batch"/>
<SqlParameter sqlBind=":CourseOfferingNumber" name="CourseOfferingNumber" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="3" if="!Batch"/>
<SqlParameter sqlBind=":SessionCode" name="SessionCode" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="4" if="!Batch"/>
<SqlParameter sqlBind=":ClassSection" name="ClassSection" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="5" if="!Batch"/>
</SqlParameters>
<Sql>
<![CDATA[
SELECT DISTINCT
c.strm||'_'||c.crse_id||'_'||c.crse_offer_nbr||'_'||c.session_code||'_'||c.class_section as SectionSisKey,
m.strm||'_'||m.crse_id||'_'||m.crse_offer_nbr||'_'||m.session_code||'_'||m.class_section||'_'||m.class_mtg_nbr SectionMeetingSisKey,
m.meeting_time_start SectionMeetingStartTime,
m.meeting_time_end SectionMeetingEndTime,
m.start_dt SectionMeetingStartDate,
m.end_dt SectionMeetingEndDate,
-- Get the DaysMet mask --
(CASE WHEN nvl(m.sun,'N') IN ('1','Y','T','X','U') THEN 'U' ELSE '' END ||
CASE WHEN nvl(m.mon,'N') IN ('1','Y','T','X','M') THEN 'M' ELSE '' END ||
CASE WHEN nvl(m.tues,'N') IN ('1','Y','T','X') THEN 'T' ELSE '' END ||
CASE WHEN nvl(m.wed,'N') IN ('1','Y','T','X','W') THEN 'W' ELSE '' END ||
CASE WHEN nvl(m.thurs,'N') IN ('1','Y','T','X','R','H') THEN 'R' ELSE '' END ||
CASE WHEN nvl(m.fri,'N') IN ('1','Y','T','X','F') THEN 'F' ELSE '' END ||
CASE WHEN nvl(m.sat,'N') IN ('1','Y','T','X','S') THEN 'S' ELSE '' END) AS SectionMeetingDaysMetPattern,
CASE when (m.meeting_time_start is not null and m.meeting_time_end is not null and
(CASE WHEN nvl(m.sun,'N') IN ('1','Y','T','X','U') THEN 'U' ELSE '' END ||
CASE WHEN nvl(m.mon,'N') IN ('1','Y','T','X','M') THEN 'M' ELSE '' END ||
CASE WHEN nvl(m.tues,'N') IN ('1','Y','T','X') THEN 'T' ELSE '' END ||
CASE WHEN nvl(m.wed,'N') IN ('1','Y','T','X','W') THEN 'W' ELSE '' END ||
CASE WHEN nvl(m.thurs,'N') IN ('1','Y','T','X','R','H') THEN 'R' ELSE '' END ||
CASE WHEN nvl(m.fri,'N') IN ('1','Y','T','X','F') THEN 'F' ELSE '' END ||
CASE WHEN nvl(m.sat,'N') IN ('1','Y','T','X','S') THEN 'S' ELSE '' END) is not null)
THEN
'N'
ELSE
'Y'
END AS RequiresAttention,
CASE when (m.meeting_time_start is not null and m.meeting_time_end is not null and
(CASE WHEN nvl(m.sun,'N') IN ('1','Y','T','X','U') THEN 'U' ELSE '' END ||
CASE WHEN nvl(m.mon,'N') IN ('1','Y','T','X','M') THEN 'M' ELSE '' END ||
CASE WHEN nvl(m.tues,'N') IN ('1','Y','T','X') THEN 'T' ELSE '' END ||
CASE WHEN nvl(m.wed,'N') IN ('1','Y','T','X','W') THEN 'W' ELSE '' END ||
CASE WHEN nvl(m.thurs,'N') IN ('1','Y','T','X','R','H') THEN 'R' ELSE '' END ||
CASE WHEN nvl(m.fri,'N') IN ('1','Y','T','X','F') THEN 'F' ELSE '' END ||
CASE WHEN nvl(m.sat,'N') IN ('1','Y','T','X','S') THEN 'S' ELSE '' END) is not null)
THEN
NULL
ELSE
'Invalid meeting pattern'
END AS RequiresAttentionReason,
CASE when (c.campus is not null and f.bldg_cd is not null and f.room is not null)
then
c.campus || '_' || f.bldg_cd || '_' || f.room
else
NULL
END AS RoomSisKey,
c.ssr_component MeetingTypeSisKey,
c.ssr_component MeetingTypeName
FROM ps_class_tbl c
JOIN ps_class_mtg_pat m ON (m.crse_id = c.crse_id
AND m.strm = c.strm
AND m.crse_offer_nbr = c.crse_offer_nbr
AND m.session_code = c.session_code
AND m.class_section = c.class_section)
LEFT JOIN ps_facility_tbl f ON (m.facility_id = f.facility_id)
WHERE (f.effdt is null
OR f.effdt = (SELECT max(f2.effdt)
FROM ps_facility_tbl f2
WHERE f.facility_id = f2.facility_id))
]]>
</Sql>
<Sql if="Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.campus = :CampusCode
]]>
</Sql>
<Sql if="!Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.crse_id = :CourseId
AND c.crse_offer_nbr = :CourseOfferingNumber
AND c.session_code = :SessionCode
AND c.class_section = :ClassSection
]]>
</Sql>
<Sql if="DeleteCanceledSections">
<![CDATA[
and c.class_stat <> 'X'
]]>
</Sql>
<Schema entityName="SectionMeeting" groupName="SectionMeetings"/>
</Source>
<!-- Sub Detail (Grand Child) Data -->
<Source name="SectionMeetingCrosslists" endPoint="PrimarySIS" xsd="file:\\SIS\SectionSpokeData.xsd">
<!-- Uncomment the following to Add a Runtime Parameters-->
<SqlParameters>
<!-- Batch -->
<SqlParameter sqlBind=":CampusCode" name="CampusCode" ref="CampusCode" if="Batch"/>
<SqlParameter sqlBind=":TermCode" name="TermCode" ref="TermCode" if="Batch"/>
<!-- OnDemand and Realtime -->
<SqlParameter sqlBind=":TermCode" name="TermCode_NonBatch" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="1" if="!Batch"/>
<SqlParameter sqlBind=":CourseId" name="CourseId" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="2" if="!Batch"/>
<SqlParameter sqlBind=":CourseOfferingNumber" name="CourseOfferingNumber" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="3" if="!Batch"/>
<SqlParameter sqlBind=":SessionCode" name="SessionCode" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="4" if="!Batch"/>
<SqlParameter sqlBind=":ClassSection" name="ClassSection" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="5" if="!Batch"/>
</SqlParameters>
<Sql>
<![CDATA[
SELECT DISTINCT
cxl.strm||'_'||cx.session_code||'_'||cx.sctn_combined_id||'_'||cx.institution||'_'||TO_CHAR(m.class_mtg_nbr) CrosslistSisKey,
m.strm||'_'||m.crse_id||'_'||m.crse_offer_nbr||'_'||m.session_code||'_'||m.class_section||'_'||m.class_mtg_nbr SectionMeetingSisKey,
cxl.strm as TermSisKey,
cxl.enrl_tot as CrosslistEnrollment,
cxl.enrl_cap as CrosslistMaxEnrollment,
cxl.strm||'_'||cx.session_code||'_'||cx.sctn_combined_id||'_'||cx.institution||'_'||TO_CHAR(m.class_mtg_nbr) CrosslistName
FROM ps_sctn_cmbnd_tbl cxl
JOIN ps_sctn_cmbnd cx on (cx.institution = cxl.institution
and cx.strm = cxl.strm
and cx.sctn_combined_id = cxl.sctn_combined_id)
JOIN ps_class_tbl c on (c.institution = cx.institution
and c.strm = cx.strm
and c.session_code = cx.session_code
and c.class_nbr = cx.class_nbr)
JOIN ps_class_mtg_pat m ON (m.crse_id = c.crse_id
AND m.strm = c.strm
AND m.crse_offer_nbr = c.crse_offer_nbr
AND m.session_code = c.session_code
AND m.class_section = c.class_section)
]]>
</Sql>
<Sql if="Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.campus = :CampusCode
]]>
</Sql>
<Sql if="!Batch">
<![CDATA[
AND c.strm = :TermCode
AND c.crse_id = :CourseId
AND c.crse_offer_nbr = :CourseOfferingNumber
AND c.session_code = :SessionCode
AND c.class_section = :ClassSection
]]>
</Sql>
<Sql if="DeleteCanceledSections">
<![CDATA[
and c.class_stat <> 'X'
]]>
</Sql>
<Schema entityName="Crosslist" groupName="Crosslists"/>
</Source>
<Source name="SectionMeetingInstructors" endPoint="PrimarySIS" xsd="file:\\SIS\SectionSpokeData.xsd">
<!-- Uncomment the following to Add a Runtime Parameters-->
<SqlParameters>
<!-- Batch -->
<SqlParameter sqlBind=":CampusCode" name="CampusCode" ref="CampusCode" if="Batch"/>
<SqlParameter sqlBind=":TermCode" name="TermCode" ref="TermCode" if="Batch"/>
<!-- OnDemand and Realtime -->
<SqlParameter sqlBind=":TermCode" name="TermCode_NonBatch" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="1" if="!Batch"/>
<SqlParameter sqlBind=":CourseId" name="CourseId" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="2" if="!Batch"/>
<SqlParameter sqlBind=":CourseOfferingNumber" name="CourseOfferingNumber" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="3" if="!Batch"/>
<SqlParameter sqlBind=":SessionCode" name="SessionCode" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="4" if="!Batch"/>
<SqlParameter sqlBind=":ClassSection" name="ClassSection" ref="SectionSisKey" regex="([^_]+)_([^_]+)_([^_]+)_([^_]+)_([^_]+)" match="5" if="!Batch"/>
</SqlParameters>
<Sql>
<![CDATA[
SELECT DISTINCT
i.emplid as InstructorSisKey,
i.strm||'_'||i.crse_id||'_'||i.crse_offer_nbr||'_'||i.session_code||'_'||i.class_section||'_'||i.class_mtg_nbr SectionMeetingSisKey,
n.first_name as PersonFirstName,
n.last_name as PersonLastName,
n.middle_name as PersonMiddleName,
i.emplid as PersonSisKey,
e.email_addr as PersonEMail,
e.email_addr as InstructorEMail,
'True' as InstructorIsPrimary
FROM ps_class_instr i
LEFT JOIN ps_email_addresses e ON (i.emplid = e.emplid)
JOIN (SELECT A.EMPLID ,
A.NAME,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME_TYPE ,
A.EFFDT
FROM PS_NAMES A
WHERE A.NAME_TYPE = 'PRI'
AND A.EFFDT = (SELECT MAX(B.EFFDT)
FROM PS_NAMES B
WHERE B.EMPLID = A.EMPLID
AND B.NAME_TYPE = A.NAME_TYPE
AND B.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))) n ON (i.emplid = n.emplid)
JOIN ps_class_tbl c on (i.strm = c.strm
AND i.crse_id = c.crse_id
AND i.crse_offer_nbr = c.crse_offer_nbr
AND i.session_code = c.session_code
AND i.class_section = c.class_section)
WHERE i.instr_role ='PI'
AND (e.pref_email_flag IS NULL OR e.pref_email_flag = 'Y')
]]>
</Sql>
<Sql if="Batch">
<![CDATA[
AND i.strm = :TermCode
AND c.campus = :CampusCode
]]>
</Sql>
<Sql if="!Batch">
<![CDATA[
AND i.strm = :TermCode
AND i.crse_id = :CourseId
AND i.crse_offer_nbr = :CourseOfferingNumber
AND i.session_code = :SessionCode
AND i.class_section = :ClassSection
]]>
</Sql>
<Sql if="DeleteCanceledSections">
<![CDATA[
and c.class_stat <> 'X'
]]>
</Sql>
<Schema entityName="Instructor" groupName="Instructors"/>
</Source>
</Sources>
<Phases>
<Phase name="ImportSections">
<Fetch ref="AstraSections" if="SynchronizeSectionDeletes"/>
<Fetch ref="AstraSectionMeetings" if="SynchronizeMeetingDeletes"/>
<Fetch ref="Sections"/>
<Fetch ref="SectionMeetings"/>
<Fetch ref="CourseMeetingTypes"/>
<Fetch ref="SectionMeetingCrosslists"/>
<Fetch ref="SectionMeetingInstructors"/>
<SetOperation name="SectionsNotInImportSet" if="SynchronizeSectionDeletes">
<Difference columns="SectionSisKey" entityName="Section" groupName="Sections">
<DifferenceSource ref="AstraSections"/>
<DifferenceSource ref="Sections"/>
</Difference>
</SetOperation>
<SetOperation name="SectionMeetingsNotInImportSet" if="SynchronizeMeetingDeletes">
<Difference columns="SectionMeetingSisKey" entityName="SectionMeeting" groupName="SectionMeetings">
<DifferenceSource ref="AstraSectionMeetings"/>
<DifferenceSource ref="SectionMeetings"/>
</Difference>
</SetOperation>
<SetOperation name="SectionMeetingsToDelete" if="SynchronizeMeetingDeletes" >
<Difference columns="SectionSisKey" entityName="SectionMeeting" groupName="SectionMeetings" >
<DifferenceSource ref="SectionMeetingsNotInImportSet"/>
<DifferenceSource ref="SectionsNotInImportSet" />
</Difference>
</SetOperation>
<Target endPoint="Astra">
<AstraEnvironment>
<!-- Sections -->
<Hub className="SectionHub">
<Reader ref="Sections"/>
<HubParameters>
<HubParameter name="DeleteCanceledSections" expr="DeleteCanceledSections"/>
<HubParameter name="SectionsNotInImportSet" expr="SectionsNotInImportSet" if="SynchronizeSectionDeletes"/>
</HubParameters>
<AlternateHubs>
<Hub className="CourseDeliveryMethodHub"/>
<Hub className="CourseHub" />
<Hub className="DeliveryMethodHub" />
<Hub className="SubjectHub" />
</AlternateHubs>
</Hub>
<!-- SectionMeetings -->
<Hub className="SectionMeetingHub">
<Reader ref="SectionMeetings"/>
<HubParameters>
<HubParameter name="ImportIncompleteSectionMeetings" expr="ImportIncompleteSections"/>
<HubParameter name="SectionMeetingsNotInImportSet" expr="SectionMeetingsToDelete" if="SynchronizeMeetingDeletes"/>
</HubParameters>
<AlternateHubs>
<Hub className="MeetingTypeHub" />
</AlternateHubs>
</Hub>
<!-- CourseMeetingTypes -->
<Hub className="CourseMeetingTypeHub">
<Reader ref="CourseMeetingTypes"/>
</Hub>
<!-- Crosslists -->
<Hub className="CrosslistHub">
<Reader ref="SectionMeetingCrosslists"/>
</Hub>
<!-- Instructors -->
<Hub className="SectionMeetingInstructorHub">
<Reader ref="SectionMeetingInstructors"/>
</Hub>
</AstraEnvironment>
</Target>
</Phase>
</Phases>
</Pipeline>
<!-- Audit Table Differencer Config -->
<Differencer sisType="Oracle" sisConnectStringKey="PrimarySisConnectionString" if="'y' == UseAuditTable.ToLower()">
<ConnectionString>
<![CDATA[${SisConnectString};User Id=${SISUser};Password=${SISPass};]]>
</ConnectionString>
<SISDateParameter value=""/>
<SISDiffQuery>
<![CDATA[
SELECT DISTINCT NULL SisKey, -- Section Meeting SIS Key - not required for PeopleSoft Audit Table
a.strm||'_'||a.crse_id||'_'||a.crse_offer_nbr||'_'||a.session_code||'_'||a.class_section SectionId,
SYSDATE as datestamp
FROM ps_audit_ad_astra7 a
INNER JOIN ps_class_tbl c on c.strm = a.strm AND
c.crse_id = a.crse_id AND
c.crse_offer_nbr = a.crse_offer_nbr AND
c.session_code = a.session_code AND
c.class_section = a.class_section
WHERE a.processed <> 'Y' AND a.strm IN (${GetSqlInList(TermCode)}) AND c.campus IN (${GetSqlInList(CampusCode)})
UNION
SELECT DISTINCT NULL SisKey, -- Section Meeting SIS Key - not required for PeopleSoft Audit Table
c2.strm||'_'||c2.crse_id||'_'||c2.crse_offer_nbr||'_'||c2.session_code||'_'||c2.class_section SectionId,
SYSDATE as datestamp
FROM ps_audit_ad_astra7 a
INNER JOIN ps_class_tbl c on c.strm = a.strm AND
c.crse_id = a.crse_id AND
c.crse_offer_nbr = a.crse_offer_nbr AND
c.session_code = a.session_code AND
c.class_section = a.class_section
INNER JOIN ps_sctn_cmbnd cl on c.strm = cl.strm AND
c.session_code = cl.session_code AND
c.class_nbr = cl.class_nbr
INNER JOIN ps_sctn_cmbnd cl2 on cl.strm = cl2.strm AND
cl.sctn_combined_id = cl2.sctn_combined_id
--and cl.class_nbr <> cl2.class_nbr
INNER JOIN ps_class_tbl c2 on cl2.strm = c2.strm and cl2.class_nbr = c2.class_nbr
WHERE a.processed <> 'Y' AND a.strm IN (${GetSqlInList(TermCode)}) AND c.campus IN (${GetSqlInList(CampusCode)})
]]>
</SISDiffQuery>
<!--
Configuration to mark records as processed and remove them from the audit table
-->
<SISPreImportKeyParameter value=":SisKey" />
<SISPreImportSQL>
<![CDATA[
UPDATE PS_AUDIT_AD_ASTRA7 SET PROCESSED = 'Y'
WHERE strm = SUBSTR(:SisKey, 0, INSTR(:SisKey, '_', 1, 1) - 1) AND
crse_id = SUBSTR(:SisKey, INSTR(:SisKey, '_', 1, 1) + 1, INSTR(:SisKey, '_', 1, 2) - INSTR(:SisKey, '_', 1, 1) -1) AND
crse_offer_nbr = SUBSTR(:SisKey, INSTR(:SisKey, '_', 1, 2) + 1, INSTR(:SisKey, '_', 1, 3) - INSTR(:SisKey, '_', 1, 2) -1) AND
session_code = SUBSTR(:SisKey, INSTR(:SisKey, '_', 1, 3) + 1, INSTR(:SisKey, '_', 1, 4) - INSTR(:SisKey, '_', 1, 3) -1) AND
class_section = SUBSTR(:SisKey, INSTR(:SisKey, '_', 1, 4) + 1)
]]>
</SISPreImportSQL>
<SISPostImportKeyParameter value="" />
<SISPostImportSQL>
<![CDATA[
DELETE FROM PS_AUDIT_AD_ASTRA7 WHERE PROCESSED = 'Y'
]]>
</SISPostImportSQL>
<!--
Field used as the key for the record
-->
<SISKeyField value="SisKey"/>
<!--
Comma delimeted fields used to check for differences.
Comparing on the query timestamp will force all records in the audit table
to be identified as changes.
-->
<SISFieldsToCheck value="datestamp"/>
</Differencer>
<!-- Normal Differencer Config -->
<Differencer sisType="Oracle" sisConnectStringKey="PrimarySisConnectionString">
<ConnectionString>
<![CDATA[${SisConnectString};User Id=${SISUser};Password=${SISPass};]]>
</ConnectionString>
<!-- This can be used to limit by date if the SIS supports
modified date/activity date on section records
<SISDateParameter value=":ActivityDate"/>
-->
<SISDateParameter value=""/>
<SISDiffQuery>
<![CDATA[
SELECT m.strm||'_'||m.crse_id||'_'||m.crse_offer_nbr||'_'||m.session_code||'_'||m.class_section||'_'||m.class_mtg_nbr SisKey,
c.strm Term,
m.meeting_time_start StartTime,
m.meeting_time_end EndTime,
f.bldg_cd Building,
f.room Room,
m.start_dt StartDate,
m.end_dt EndDate,
-- Get the DaysMet mask --
(CASE WHEN nvl(m.sun,'N') IN ('1','Y','T','X','U') THEN 'U' ELSE '' END ||
CASE WHEN nvl(m.mon,'N') IN ('1','Y','T','X','M') THEN 'M' ELSE '' END ||
CASE WHEN nvl(m.tues,'N') IN ('1','Y','T','X') THEN 'T' ELSE '' END ||
CASE WHEN nvl(m.wed,'N') IN ('1','Y','T','X','W') THEN 'W' ELSE '' END ||
CASE WHEN nvl(m.thurs,'N') IN ('1','Y','T','X','R','H') THEN 'R' ELSE '' END ||
CASE WHEN nvl(m.fri,'N') IN ('1','Y','T','X','F') THEN 'F' ELSE '' END ||
CASE WHEN nvl(m.sat,'N') IN ('1','Y','T','X','S') THEN 'S' ELSE '' END) AS DaysMet,
c.ssr_component MeetingType,
c.subject Subject,
c.catalog_nbr CourseNumber,
m.class_section SectionNumber,
m.session_code Session_Code,
c.descr Title,
c.campus Campus,
c.enrl_cap Max_Enrollment, c.enrl_tot Enrollment,
case c.class_stat when 'X' then 'C' else c.class_stat end as StatusCode,
c.strm||'_'||c.crse_id||'_'||c.crse_offer_nbr||'_'||c.session_code||'_'||c.class_section as SectionId
FROM ps_class_tbl c
JOIN ps_class_mtg_pat m ON
(m.crse_id = c.crse_id
AND m.strm = c.strm
AND m.crse_offer_nbr = c.crse_offer_nbr
AND m.session_code = c.session_code
AND m.class_section = c.class_section)
LEFT JOIN ps_facility_tbl f ON (m.facility_id = f.facility_id)
WHERE (f.effdt is null
OR f.effdt = (SELECT max(f2.effdt)
FROM ps_facility_tbl f2
WHERE f.facility_id = f2.facility_id))
AND c.strm IN (${GetSqlInList(TermCode)})
AND c.campus IN (${GetSqlInList(CampusCode)})
--TODO: limit by activity date......
]]>
</SISDiffQuery>
<!--
Field used as the key for the record
-->
<SISKeyField value="SisKey"/>
<!--
Comma delimeted fields used to check for differences
-->
<SISFieldsToCheck value="StartTime, EndTime, Building, Room, StartDate, EndDate, DaysMet, MeetingType, StatusCode"/>
</Differencer>
</ATE>
Page url: ?peoplesoftsisinterfaceaddendum_3.htm
