Table TD_INSTRUCTIONAL_PROGRAM in DWST@WHSE.ISC-AIT.UPENN.EDU

Tables

This table will hold the unique school, division, degree, major, special_program combinations that represent the instructional programs. If new combinations arrive, that record should be inserted complete with descriptions for each column. The description for school, division, degree, major, special program can be found in dwadmin.school, dwadmin.division, dwadmin.degree, dwst.major, and dwst.special_program.
Col #	Column Name	Data Type	Not Null?	Data Def.	Comments
1	INSTRUCTIONAL_PROGRAM_KEY	INTEGER	Y		Instructional Program Key is the Primary Key for the DWST.TD_INSTRUCTIONAL_PROGRAM table.
2	SCHOOL	VARCHAR2(2 BYTE)			School is a 2 character code, loaded from the DWADMIN.DEGREE_TERM table where degree_order = 1. This school code is adjusted, where for the divisions of PDM and VTP the school is shown as MD. All other schools are loaded as listed in DWADMIN.DEGREE_TERM.SCHOOL
3	SCHOOL_DESC	VARCHAR2(40 BYTE)			School Desc(ription) is the description loaded from the DWADMIN.SCHOOL table for the School value.
4	DIVISION	VARCHAR2(3 BYTE)			Division is a 3 character field, loaded from the DWADMIN.DEGREE_TERM table where degree_order = 1
5	DIVISION_DESC	VARCHAR2(41 BYTE)			Division Desc(ription) is the description loaded for a given Division from the DWADMIN.DIVISION table
6	DEGREE	VARCHAR2(3 BYTE)			Degree is a 3 character field, loaded from the DWADMIN.DEGREE_TERM table where degree_order = 1
7	DEGREE_DESC	VARCHAR2(35 BYTE)			Degree Desc(ription) is the description for a given Degree, loaded from the DWADMIN.DEGREE table
8	MAJOR	VARCHAR2(4 BYTE)			Major is a 4 character field, loaded from the DWADMIN.MAJOR_MINOR_TERM table where degree_order = 1 and major_minor_type = M
9	MAJOR_DESC	VARCHAR2(36 BYTE)			Major Desc(ription) is loaded for a given Major from the DWST.MAJOR table.
10	SPECIAL_PROGRAM	VARCHAR2(3 BYTE)			Special Program is a 3 character field, loaded from the DWADMIN.DEGREE_TERM table where degree_order = 1
11	SPECIAL_PROGRAM_DESC	VARCHAR2(50 BYTE)			Special Program Desc(ription) is loaded from the DWST.SPECIAL PROGRAM table for a given Special Program.
12	INSTRUCTIONAL_PROGRAM	VARCHAR2(13 BYTE)			Instructional Program is a 13 character field, concatenating Division, Degree, Major, Special Program together.
13	INSTRUCTIONAL_PROGRAM_TYPE	VARCHAR2(1 BYTE)			Instructional Program Type is a 1 character field, indicating the four (4) types of programs stored. The value calculation is contained within the DWST.TD_DIMENSION_LOAD insertinstructionalprogram.
14	INSTRUCTIONAL_PROGRAM_DESC	VARCHAR2(60 BYTE)			Instructional Program Desc is the description for a given Instructional Program Type, loaded from DWST.TD_PROGRAM_TYPE_LU.
15	BEGIN_EFF_DATE	DATE	Y	sysdate	Begin Eff Date is populated when a new row is added.
16	END_EFF_DATE	DATE			End Eff Date is populated only when a newer version of this dimension record is added. The default is null.
17	CURRENT_RECORD	CHAR(1 BYTE)	Y	'T'	Current Record is a T(rue)/ F(alse) Flag, indicating which of the dimension records are current. This is used for all lookups when loading the fact table.
18	CER_COLUMN	VARCHAR2(3 BYTE)			CER Column is a 3 character field, loaded during the TD_DIMENSION_LOAD insertinstructionalprogram process. The data are provided for backward compatibility in identifying divisions/schools for the Registrar's Course Enrollment and Registered Credit Reports.

**Indexes on TD_INSTRUCTIONAL_PROGRAM**
Uniqueness	Index Name	Columns
UNIQUE	SYS_C00511905	INSTRUCTIONAL_PROGRAM_KEY
NON-UNIQUE	INST_PROG_SPEC_PROG_IDX	SPECIAL_PROGRAM
NON-UNIQUE	TD_IP_TYPE_IDX	INSTRUCTIONAL_PROGRAM_TYPE
NON-UNIQUE	TD_INSTRUCTIONAL_PROG_CURR_IDX	CURRENT_RECORD
NON-UNIQUE	TD_INSTRUCTIONAL_PROG_IP_IDX	INSTRUCTIONAL_PROGRAM
NON-UNIQUE	TD_INSTRUCTIONAL_PROG_DATES	BEGIN_EFF_DATE, END_EFF_DATE

Triggers on TD_INSTRUCTIONAL_PROGRAM

Trigger Name

Status

Triggering Event

When Clause

Trigger Type

Body

TD_INST_PROGRAM_PK_TRIG

ENABLED

INSERT

BEFORE EACH ROW

BEGIN

    SELECT TD_INST_PROGRAM_PK_SEQ.NEXTVAL INTO :NEW.INSTRUCTIONAL_PROGRAM_KEY FROM dual;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;

**PK, UK, & Check Constraints on TD_INSTRUCTIONAL_PROGRAM**
Name	Type	Condition	Status	Columns
SYS_C00511905	Primary Key		ENABLED	INSTRUCTIONAL_PROGRAM_KEY

**Granted Privileges on TD_INSTRUCTIONAL_PROGRAM**
Column	Grantee	Grantable Privilege(s)	Non-Grantable Privilege(s)
	ESTEMMLE		SELECT
	DWADMIN		ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK
	YALINGX		SELECT
	DWST_SUPPORT		SELECT
	TUITION_DIST_RL		SELECT

--------- TD_INSTRUCTIONAL_PROGRAM does not reference any objects ---------

**Objects which reference table TD_INSTRUCTIONAL_PROGRAM**
Object Owner	Object Name	Object Type
DWST	TD_DISTRIBUTION_FACT	Table (FK)
DWST	TD_INSTR_PROGRAM_TUITION_FACT	Table (FK)
DWST	TD_STUDENT_REGISTRATION_FACT	Table (FK)
DWST	TD_INST_PROGRAM_PK_TRIG	Trigger