• The format of the feed will be is a set of files of fixed-width columns, where entries are terminated by a carriage return. One dataset will contain person information; another will contain course information; the third will contain course-person pairing information. Person information will be provided for students, instructors, and alumni, as specified below.
• SIS may choose to produce this feed as more than three files, in which case the files will be concatenated by AcIS into the three. Specifically, SIS may divide the entries in this way:
  1. Students (S)
  2. Alumni (A)
  3. Instructors (I)
  4. Barnard Students (B)
  5. TC Students (T)
• When a person has more than one value that applies to a given column, multiple entries for that person should be included, one for each set of values. For example, if a person is a professor and a student, there should be one row for the professor role and one for the student role. If a person is a student in both the Computer Science program and the History program, the student should have two entries, one for each program. For a professor, many fields, like major would probably be empty.
• Any field for which there is no appropriate value should be padded with spaces. No fields are considered to be required though entries lacking a givenname, surname, or c-number may not be processed (and a warning generated to the appropriate place).
• All fields listed below should contain the meaningful English text value except where the word code is used.

Who should be included in this feed:

• Students = All persons with a STUD record. This includes Columbia, TC, and BC. The only records to be excluded are those where Program code= TEST or FAKE, or persons with SSN starting with S.

  For BC and TC students: Only the following columns will be provided. All others will be blank (full of spaces): surname, givenname, c-number, SSN, Date of Birth, Gender, Program Code (TCCR=Teacher's College; BCCR=Barnard College). All BC and TC person data comes from BC and TC sources and may be out of date compared to home system data.

• Alumni = One record to AcIS for each ALUM record where Grad Date is a date within the past 90 days. (If the Current Date > Graduation Date + 90 days the APRG record should be rejected.)
• Instructors = Persons with a CINS record for terms>= 20001.

  All these records will be in the same file format with spaces in columns that do not provide data for the person or population.

  In the future, we may expand this to include accepted or applying students.

  The number of student records that appear will correspond to the number of valid records from the PLAN table for this PID. (There may be additional non-student records if the person is an instructor or alumni who is provided according to the rule above.)

  The number of alumni records that appear will correspond to the number of valid records from the APRG table for this PID. (There may be additional non-alumni records if the person is an instructor or student who is provided according to the rule above.)

  There shoulde be one instructor record for any instructor in the CINS table with term >= 20001. (There may be additional non-alumni records if the person is an alum or student who is provided according to the rule above.) This may be achieved as follows:

  1. For each record from CINS for term >= 20001 one output record should be created.
  2. After all records from CINS will be processed output file should be sorted by fields 1-5
  3. Duplicates PIDs must then be eliminated

  ---

  Person Information: Fields

  bold indicates fields that are likely to have multiple values for a given person (and thus cause multiple rows to be sent for that person). italics indicate a field that could be determined from a join with a program code description table.

  AcIS Name	SIS Name	SIS Source TABLE(key)	Length	Description/Notes (AcIS + SIS)	Additional SIS Notes
  Name and personal data:
  firsname		NAME(pid)	15	The person's first name ("givenname")
  middlename		NAME(pid)	15	The person's middle name
  surname		NAME(pid)	25	The person's surname ("last name")
  suffix		NAME(pid)	4	The person's suffix, e.g. "Jr.", "Sr.", "III"
  c-number	Person-ID, PID	all	10	Unique SIS-assigned person identifier. This field is always unique to a person, though a person might have mutliple c-numbers.
  ssn		NAME(pid)	10	The person's 9-digit social security number of record in SIS. This number may be a bogus ssn, but SIS has no way to know if it is. When the registrar assigns a dummy ssn, it is used consistently, so AcIS will treat dummy ssns as if they are real.	SIS will exclude ssn's that start with an 'S'. If those bogus ssn's are associated with real people, those rows will appear in the feed with a blank ssn (spaces); otherwise all data associated with them will be omitted.
  uni	kerberos handle	PIDK(pid)	16	The person's AcIS-assigned unique identifier. AcIS will use this field as an aid in tracking changes when a c-number, name, or ssn change. However, since AcIS "owns" this data, we understand that if we change this value, the SIS data will be incorrect. Also, If SIS can get instructor handles from departments, then this will allow us to match up instructors without an ssn.
  date of birth		NAME(pid)	10	The person's date of birth, in the format YYYY-MM-DD. SIS will exclude internal dummy dates of birth (e.g. 1111-11-11). For these excluded dates, the field should contain all spaces instead of a date.
  gender		NAME(pid)	1	The person's gender, either 'M' or 'F'. Any value that is not M or F should be transmitted as a space.
  titlegroup			4	The general category of the position the person holds at the university. One of "STUD" for student, "INST" for instructor, or "ALUM" for alumni. If a person has several titlegroups, he should receive several lines in the file, one for each role.
  directory-release		NAME(pid)	1	Y/N for whether this person's directory information should be published.
  start date			10	blank for now; would indicate when record should become valid. Not needed because feed will only contain currently-valid entries.
  stop date			10	blank for now; would indicate when record should become invalid Not needed because feed will only contain currently-valid entries.
  Address fields:
  mailing address		ADDR(pid+LADRV)	80 x 4	Four lines of this person's mail address, determined from LADRV hierarchy (currently LH, MB, RE, LO, PM, AP, NK, SP, DA). Line 1: SIS ADDR-LINE-1 Line 2: SIS ADDR-LINE-2 Line 3: city, state zip for USA or CAN addresses; zip omitted (blank) for international addresses Line 4: blank
  country	COUNTRY-LONGNAME	CTRY(CTRY-CODE)	30	The name of the country that this mailing address is in	PID + LADRV -> ADDR
  mailing address type code		ADDR(pid+LADRV)	2	The address type code for this address (e.g. LH, PM)
  permanent home zip		ADDR	5	The zip code for this person's permanent address.
  residential address		ADDR(pid+RH/RE)	80 x 4	Four lines of residential address NOTE: Send the first active address from the following list of Address Types in the following priority order: RH, RE. (FS is no longer used) This address that will be published if it is on-campus (RH). RE addresses will be used only by AcIS for support purposes. The address is not a mailing address. Line 1: If RH Address: Squeeze <Room #> <Building Long Name>; if RE address, from SIS ADDR-LINE-1 Line 2: If RH Address, blank; if RE address, from SIS ADDR-LINE-2 Line 3: If RH Address, blank; if RE address, city, state   zip from ADDR Line 4: blank
  residential address type code		ADDR(pid+RH/RE)	2	The type code for this residential address (either RH or RE).
  residential phone number		ADDR(pid+RH/RE)	14	The person's phone number at place of residence.
  Residence hall code	RES-HALL-BLDG-CODE	ADDR(pid+RH)	4	If 'residential address type code' was RH, the SIS residence hall code for this building (otherwise blank). The SIS ADDR table is dependent upon URH updates to SIS Housing tables and URH running appropriate User-Submitted Batch jobs to update the ADDR table after room assignments have been made. (Currently Eyndmilia Adon is responsible for running the SHORELEB batch job from SISUMENU. She does this twice each year with incremental changes to Housing Assignments updating ADDR online when the change is processed in SIS Housing Screens.)	PID + RH -> ADDR
  residencehall name		BLDG(BLDG)		The long name of the person's residence hall. It's fine if these only exist for addresses of type RH.	PID + RH -> ADDR
  residencehall room number		ADDR(pid+RH)	8	The room in which the person lives. Per an October 2002 conversation with URH, the room # indicates the floor. The floor may be the first character (up to floor 9) or the first 2 chars (for 10 floors or more). SIS will provide the room number only and will not infer the floor.
  residencehall start date		ADDR(pid+RH)	10	The date on which the student began living in the residence hall. This is provided by URH as the ADDR-GOOD-FROM-DATE.
  residencehall stop date		ADDR(pid+RH)	10	The date on which the student will stop living in the residence hall. This is provided by URH as the ADDR-GOOD-UNTIL-DATE.
  Per-student registration information:
  student status code		STUD(pid)	2	The student's registration status as found in the STUD table. Either "NW" for new student or CN for continuing. For single-degree candidates, Student Status will be NW from STUD-creation until they register for the classes in the current program. The day after they register, Student Status changes to CN. For dual degree candidates, Student Status can go from NW to CN and back to NW until registered for classes in the second program.
  parttime/fulltime	enrollment status code	THRS(pid+TERM)	2	"FT", "HT", "PT" or "CR" (Cross-Registrant). SIS will transmit the summary Time-Status (indicated by * in Level on THRS) when there is more than one record/term in THRS. Summary time status is the aggregate status considering classes taken at different levels (grad, undergrad, etc.). For example, if a student is taking three classes in the law school and two in the business school, he's considered full-time even though he's not taking a full courseload at any one school.	TERM = the current term
  school level code	standing level	TSTA(pid+TERM)	1	What level the student is, e.g. U for undergrad, G for grad, P for professional Student Level and Year (aka standing) from TSTA reflects current student standing for each Term in which the student is registered. If TSTA exists for the current term, take level/standing from that TSTA record. If current term TSTA does not exist look for the TSTA record with the greatest TERM value and provide level/standing. Level code descriptions can be found in the LEVL screen in SIS.	TERM = latest TSTA term
  class year # at Columbia	standing level	TSTA(pid+TERM)	2	The 'year' part of the student standing (see 'school level code' above). For example, 1 for a first-year, 2 for a second-year, etc.	TERM = latest TSTA term
  graduation year	EXP-GRAD-YEAR	STUD*/ALUM	4	The person's expected graduation year, as a 4-digit number. (This is an estimate and a likely point of failure because it isn't always kept up to date by users of SIS.)
  graduation month	EXP-GRAD-MONTH	STUD*/ALUM	2	The person's expected graduation month, as a 2-digit number	YEAR+TERM -> GRAD
  billing program code		STUD(pid)	6	Student's billing program code. This corresponds to the student's "primary" registration in cases where a student is in two different programs/majors.	For values of "TEST" or "FAKE", the entire entry will be skipped
  Program code and derivatives:
  program code		PLAN*(pid)	6	One of the person's program codes. If a person has multiple program codes associated with him, he should have one row in this file for each program code.	For values of "TEST" or "FAKE", the entire entry will be skipped
  program longname		PGRM(program-code)	65	The program long-name (English words, not a code) associated with the program code listed in this entry	program-code -> PGRM
  certification type	CERT-TYPE-LONGNAME	CRTY(CRTYPE)	30	Indication of whether the program code listed in this entry corresponds to a major, minor, concentration, etc.	program-code -> PGCT -> CRTY
  department code		PGRM	4	The department code associated with the program code listed in this entry	program-code -> PGRM
  department letter name		OFFC(department-code)	65	The department letter name associated with the program code listed in this entry
  school code		PGRM	4	The school code associated with the program code listed in this entry	program-code -> PGRM
  school letter name		OFFC(school-code)	65	The school letter name associated with the program code listed in this entry
  division code		PGRM	4	The division code associated with the program code listed in this entry	program-code -> PGRM
  division letter name		OFFC(division-code)	65	The division letter name associated with the program code listed in this entry
  campus code		PGRM	4	The campus code associated with the program code listed in this entry	program-code -> PGRM
  campus letter name		OFFC(campus-code)	65	The campus letter name associated with the program code listed in this entry

  Notes:

  • (#) --> XXXX = using the (#) as a key retrieve the record from table XXXX
  • * = Source table is STUD / PLAN for STUDENT and ALUM / APRG for ALUMNI.
  • "Latest" = current or greater from all previous TSTA terms (/ul>

    ---

    Course-Person Pairings: Description

    Pairings involving students will include (based on TOOK record) current term, previous term and all future Terms.

    There should be one row in this file for each row in the TOOK table for the current term, previous term, and future terms (as specified above).

    The current term is determined from the current date (from the system clock) and the Begin-Date and End-Date from the TERM table: Using the year from the Current Date, find the record with Begin-Date <= Current Date <= End-Date. If that record is found - this is record for the current term. If not, take the last term - record with the smallest Begin-Date > Current Date.

    Instructor Data (from CINS) will be sent for terms >=20001.

    SIS will process this as 2 files (Students and Instructors), which will be concatenated by AcIS. All these records will be in the same format.

    ---

    Course-Person Pairings: Fields

    AcIS Name	SIS Name	SIS Source	Length	Description
    C-number	Person-ID, PID		10	Unique SIS-assigned person identifier.
    Class Year	TERM-YEAR		4	The four-digit year of this class
    Class Term	TERM-CODE		1	The one-digit term of this class (1=spring, 2=summer, 3=fall)
    Course prefix			4
    Course number			4
    Course suffix			1
    Section code			3
    Instructor flag			1	'Y' if person is an instructor of this class, 'N' otherwise
    Pass/fail flag			1	'Y' if person is taking this class pass/fail, 'N' if for credit, blank for instructors
    Points	NUM-UNITS		6	How many "points" or "units" this class is being taken for, or blank if n/a

    ---

    Course Information: Description

    This feed is already written and working in the SISX373B extract program. It is included here for reference. The one difference between the current feed and the desired feed is the pass/fail field.

    There should be one row in this file for each row in the CLAS table.

    The description below is incompletely filled out and some of the comments may be incorrect.

    ---

    Course Information: Fields

    Field Name	Source Table	Description
    TERM
    COURSE
    PREFIX-NAME
    SUFFIX-NAME
    DIVISION-CODE
    DIVISION-NAME
    CAMPUS-CODE
    CAMPUS-NAME
    SCHOOL-CODE
    SCHOOL-NAME
    DEPARTMENT-CODE
    DEPARTMENT-NAME
    SUBTERM-CODE
    SUBTERM-NAME
    CALL-NUMBER
    NUM-ENROLLED
    MAX-SIZE
    MIN-SIZE
    ENROLLMENT-STATUS
    NUM-UNITS
    MIN-UNITS
    UNITS-FILLER
    MAX-UNITS
    COURSE-TITLE
    COURSE-SUBTITLE
    TYPE-CODE
    TYPE-NAME
    APPROVAL
    BULLETIN-FLAGS
    CLASS-NOTES
    MEETS1
    MEETS2
    MEETS3
    MEETS4
    MEETS5
    MEETS6
    INSTRUCTOR1-PID		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR1-NAME		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR2-PID		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR2-NAME		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR3-PID		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR3-NAME		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR4-PID		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    INSTRUCTOR4-NAME		Remains for now, but eventually we hope to do away with this field. The 3rd feed (next page) provides AcIS with this information and the association of the Professor(s) to the class more effectively.
    PREFIX (SUBJECT) NAME
    FINALEXAM
    EXAM-DATE
    EXAM-MEET
    Pass/fail		Y if this course can be taken pass/fail, N otherwise. Clarify with Registrar that this field is dependable. (This field may be added later but no action needed currently.)
    CHARGE-MSG		This field is being added to the DOC feed and is currently in process of being approved by AcIS.
    CHARGE-AMT		This field is being added to the DOC feed and is currently in process of being approved by AcIS.
    CHARGE-MSG1		This field is being added to the DOC feed and is currently in process of being approved by AcIS.
    CHARGE-AMT1		This field is being added to the DOC feed and is currently in process of being approved by AcIS.

    
    MEETS1 through MEETS6 and FINALEXAM are defined thus:
    DAY CODES : 7
    START TIME : 6
    SPACE : 1
    END TIME : 6
    BLDG CODE : 4
    BLDG NAME :12
    ROOM NAME : 5
    DAY CODES:
    Monday:M, Tuesday:T, Wednesday:W, Thursday:R, Friday:F, Saturday:S, Sunday:U

    ---

    [an error occurred while processing this directive]

    ---

    [an error occurred while processing this directive]