academic planning database web-based user guide...the academic planning database (apdb) is a...

Information and Application Services Enterprise Systems Group

April 2011

Academic Planning Database Web-Based User Guide

Contents

OVERVIEW ......................................................................................................................................... 1

CONVENTIONS USED IN THIS GUIDE ............................................................................................. 2

CAMPUS SUBMISSION PROCEDURES ........................................................................................... 3

USERS AND ROLES .......................................................................................................................... 4

CAMPUS CODE TABLE ..................................................................................................................... 5

FILE LAYOUT ..................................................................................................................................... 6

FACULTY TRANSACTION FILE .................................................................................................................. 6 SECTION TRANSACTION FILE .................................................................................................................. 7

LOGON TO THE SYSTEM ................................................................................................................. 9

SUCCESSFUL LOGON ........................................................................................................................... 11 UNSUCCESSFUL LOGON ....................................................................................................................... 11

LOGOFF THE SYSTEM .................................................................................................................... 12

CHANGE YOUR ACCOUNT PASSWORD ...................................................................................... 13

LOAD YOUR TRANSACTION FILE ................................................................................................. 14

LOAD MESSAGES ................................................................................................................................. 16

EDIT PROCESS ................................................................................................................................ 17

CREATE THE MASTER FILES ................................................................................................................. 17 MASTER FILE AUDIT ............................................................................................................................. 20 SECTION MASTER FILE GROUP CODE AUDIT ......................................................................................... 23 SECTION AND FACULTY MASTER FILES CROSSOVER ............................................................................. 25

REPORTS ......................................................................................................................................... 28

SAMPLE REPORT SCREENS .................................................................................................................. 29 53 – Course Section Report ....................................................................................................................... 29 54 – Section Size Frequency Distribution (SSFD) Report ........................................................................... 30 55 – Faculty Assignment By Department (FAD) Report ............................................................................ 30 61 – Summary By Class and Level (SCAL) Report ...................................................................................... 31 62 – Course and Section Data (CSDR) Report ........................................................................................... 31

FINALIZE SECTION/FACULTY DATA ........................................................................................................ 32 SEND NOTIFICATION TO THE CHANCELLOR’S OFFICE ............................................................................ 34

SYSTEM MESSAGES ...................................................................................................................... 36

Web-Based User Guide Page 1 Office of the Chancellor April 2011

Overview The Academic Planning Database (APDB) is a computerized management information system based upon two separate but coordinated files. These files are: 1) Faculty Master file that contains information on all persons compensated from instructional faculty or instructional administration accounts, and all persons having teaching responsibilities in the California State University, regardless of funding sources, and 2) Section Master file that contains information on all courses offered during a given term. The two files are linked by Social Security Number. Together they provide information for academic planning and administration. The Social Security Numbers are stored in the section master records and the section identification of courses taught are stored in the faculty master records. Data from these files are combined to provide valuable planning tools for both the campus administration and the Chancellor’s Office. .


Conventions Used In This Guide The following are the typeface conventions used throughout this guide: Bold Indicates either one of the following: emphasis of a specific word or phrase specific

selection from a list or option. Example: Click on the OK button Names of screens, directories, pull-down menus, buttons and dialog boxes. Example: select click on Edit > File Upload from the Main Menu screen > symbol Sequence in which you should select a menu option. Example: select the topic > click on the Help icon


Campus Submission Procedures The step-by-step procedure for submitting the Academic Planning Database (APDB) data using the web-accessible system is as follows: 1) Open Internet Explorer (version 7.0 or higher is required) Go the URL: http://www.calstate.edu/es/intranet/applications/aa/apdb/ Click on the Application Logon Screen link and logon using your APDB user name and

password. Your user name and password are assigned to you by the Chancellor’s Office.

2) Load your Faculty and Section files to the Chancellor’s Office SFTP server (coweusftp101.calstate.edu). To load your campus file, click on Upload Transaction Files from the Main Menu. Select the file to be loaded > enter the parameters > click on the Upload button. A message will display when the file is loaded. NOTE: Your files can also be manually loaded to the server using a secure file transfer protocol. The username and password is your UNIX account information provided to you by the Chancellor’s Office.

3) Edit your campus file. Click on Edit Process > run the procedures in the order listed:

39 – Create Section Master File 64 – Create Faculty Master File 37 – Section Master File Audit 58 – Faculty Master File Audit 34 – Section Master File Group Code Audit 56 – Section and Faculty Master Files Crossover A message will display when a given procedure has completed. Check each report for errors before proceeding to the next edit. The reports can be downloaded from the Chancellor’s Office SFTP server (coweusftp101.calstate.edu). All catastrophic errors must be corrected. The catastrophic errors are identified by a ‘C’ prefix followed by an error number. Repeat Steps 2 and 3 until all errors are eliminated.

4) Run the Reports procedures in the order listed: The reports can be downloaded from the

Chancellor’s Office SFTP server (coweusftp101.calstate.edu). 53 – Course Section Report 54 – Section Size Frequency Distribution (SSFD) Report 55 – Faculty Assignment By Department (FAD) Report 61 – Summary By Class and Level (SCAL) Report 62 – Course and Section Data (CSDR) Report

5) Re-load your Faculty and Section transaction files to the Chancellor’s Office SFTP server. See Step 2.

6) You are ready to create the master files. Click on Finalize Section/Faculty Data > enter the

parameters > click on the Finalize Data button. Check the detail report for errors (should be error free), and that the procedure completed successfully.

7) Notify the APDB coordinator(s) at the Chancellor’s Office that your campus has completed

processing for a given term. Click on the Notify CO button from the Finalize Section/Faculty Data screen.

http://www.calstate.edu/es/intranet/applications/aa/apdb/�


Users and Roles The functions of the system have various levels of sensitivity and are not appropriate for access by all users. All users of the system are assigned specific roles and privileges. Roles describe a user’s work responsibility and privileges determine the access to functions of the system. Privileges are assigned to each role and roles are then assigned to each user. The table below describes the user role in the use of the APDB System. Screen Access Staff Role (Campus) Upload Transaction Files Yes Restricted by Campus Edit Process Yes Restricted by Campus 39 - Create Section Master File 64 - Create Faculty Master 37 - Section Master File Audit 58 - Faculty Master File Audit 34 - Section Master File Group Code Audit 56 - Section and Faculty Master Files Crossover Reports Yes Restricted by Campus 53 - Course Section Report 54 - Section Size Frequency Distribution (SSFD) Report 61 - Summary By Class and Level (SCAL) Report 62 – Course and Section Data (CSDR) Report Finalize Section/Faculty Data Yes Restricted by Campus


Campus Code Table The Campus Code Table lists the valid two digit numeric codes for the CSU campuses. Campus Code Campus Name 35 Bakersfield 66 Calexico 73 Channel Islands 20 Chico 55 Dominguez Hills 05 East Bay 25 Fresno 50 Fullerton 30 Humboldt 40 Long Beach 45 Los Angeles 07 Maritime Academy 06 Monterey Bay 70 Northridge 94 Moss Landing 10 Pomona 60 Sacramento 63 San Bernardino 65 San Diego 75 San Francisco 80 San Jose 15 San Luis Obispo 68 San Marcos 85 Sonoma 90 Stanislaus


File Layout Faculty Transaction File File Length: 120 File Name: APD.PROD.FACyytcc

where: yy = The last two digits of the calendar year for which a report or job related process is being generated or performed.

t = term

cc = numeric campus code (for a list of valid values, see section titled Campus Code Table

The Faculty Transaction file must be properly named and formatted. For more details on the individual data elements shown below, please refer to the APDB Data Element Dictionary at the following URL: http://www.calstate.edu/es/intranet/applications/aa/apdb/apdb-data-elements-dictionary/index.shtml Data Element Starting

Position Ending Position

Comments

Record Type 1 Valid Value ‘F’ Appointment Sequence 2 Filler 3 Year 4 5 Term Code 6 Campus Code 7 8 Social Security Number 9 17 Initial 1 18 Initial 2 19 Last Name 20 37 School Code 38 39 Department Code 40 42 Range Code 43 Filler 44 Instructional Faculty Fraction 45 48 Instructional Administrative Fraction 49 52 Administrative Level 53 Assigned Time Code 1 54 55 Assigned Time WTU 1 56 58 Assigned Time Code 2 59 60 Assigned Time WTU 2 61 63 Assigned Time Code 3 64 65 Assigned Time WTU 3 66 68 Other Support Fraction 69 72 Filler 73 120

http://www.calstate.edu/es/intranet/applications/aa/apdb/apdb-data-elements-dictionary/index.shtml�



File Layout Section Transaction File File Length: 120 File Name: APD.PROD.SECyytcc

where: yy = The last two digits of the calendar year for which a report or job related process is being generated or performed.

t = term

cc =numeric campus code (for a list of valid values, see section titled Campus Code Table

The Section Transaction file must be properly named and formatted. For more details on the individual data elements shown below, please refer to the APDB Data Element Dictionary at the following URL: http://www.calstate.edu/es/intranet/applications/aa/apdb/apdb-data-elements-dictionary/index.shtml Data Element

Starting Position

Ending Position

Comments

Record Type 1 Valid Value ‘S’ Service Learning Indicator 2 Section Duration Type 3 Discipline Code 4 8 Course Level Code 9 School Code 10` 11 Department Code 12 14 Course Abbreviation 15 19 Course Number 20 23 Course Suffix 24 26 Section Number 27 28 Line Sequence Number 29 30 Course Classification Number 31 32 Segment Credit Units 33 35 Enrollment36 38 Space Type 39 Begin Time 40 43 End Time 44 47 Meeting Days 48 52 Facility ID 53 56 Facility Number 53 55 Facility Suffix 56




File Layout Section Transaction File (continued) Data Element

Starting Position

Ending Position

Comments

Space ID 57 62 Space Number 57 60 Space Suffix 61 62 To Be Arranged 63 65 Social Security Number 66 74 Learning Mode 81 83 Group Code Control 81 83 Team Teaching Fraction 84 87 Override WTU 88 90 Graduate Student Enrollment 91 93 Non-resident Graduate Student Enrollment

94

96

Total Non-resident Student Enrollment 97 99 Filler 100 120


Logon To The System Open Internet Explorer and type in the following URL: http://www.calstate.edu/es/intranet/applications/ Click on the Academic Planning Database link.

The Academic Planning Database page will display. Click on the Application Logon Screen link. The first time this URL is accessed from a desktop computer, a Java Runtime Environment (JRE) version will need to be downloaded and installed. Click on the following URL for instructions to download the applet: http://www.calstate.edu/es/java-runtime-environment.shtml . NOTE: To properly install the applet, technical support may be needed to provide you with ‘administrator privileges’.

http://www.calstate.edu/es/intranet/applications/�

http://www.calstate.edu/es/java-runtime-environment.shtml�


Logon To The System (continued) The APDB Logon screen will display and prompt you for your Oracle User Name and Password. Your user name and password will be assigned to you by the Chancellor’s Office. Enter your User Name and Password > click on the Connect button.

Initial Logon This security dialog box will display the first time you logon to the system. Click on the ‘Always trust content from this publisher’ box > Click on the Run button.

To eliminate typing this URL repeatedly, use the Add to Favorites feature under the Favorites menu in Internet Explorer.

1 2

3


Logon To The System (continued) Successful Logon The main menu screen for the APDB system will display when a valid user name and password have been entered.

Unsuccessful Logon A dialog box will display a message when an invalid user name and/or password have been entered. Click on the OK button. Re-enter your user name and password > click on the Connect button. For assistance with your account information, please send an email to [email protected].

mailto:[email protected]�


Logoff The System To logoff the system, click on the Exit APDB.

You are logged off the system when the Logon screen is displayed.


Change Your Account Password To change your password, enter your User Name > click on the Change Password icon .

The Change Password dialog box will display. Enter your Oracle User Id > current password > a new password. Re-enter the new password to confirm your entry. The password must adhere to the following requirements: • Must be different from the old password by at least 3 characters • Must contain at least three (3) out of the four (4) following character types:

− At least one uppercase alphabetic character (A-Z) − At least one lowercase alphabetic character (a-z) − At least one special character. The special character should be an underscore ‘_’ − At least one number (0-9)

Click on the OK button. If the two new password entries agree a confirming message will be displayed when the password is successfully changed. Click the OK button on the message. The Logon window will display for you to logon with your user name and new password.

Click on the Change Password

1

2

3

1

2

4


Load Your Transaction File This procedure will load your Faculty and Section transaction files from your personal computer to the Chancellor’s Office SFTP server. Due to the sensitive nature of the data, these files will automatically be deleted from the server after it has processed. NOTE: You can also manually load to the SFTP server ‘coweusftp101.calstate.edu’ via a secure file transfer protocol using your UNIX account information. Step 1 Select Upload Transaction Files.

Step 2 The Upload Transaction Files screen will display. Click on the Section Transaction File button or the Faculty Transaction File button.


Load Your Transaction File (continued) Step 3 The parameters entered are validated against the name of the respective transaction file. Enter the following parameters: • Campus

Click on the downward arrow to the right of the data field. A dialog box will display for you to select a campus > click on the OK button.

• Year (YYYY) Enter the four digit calendar year.

• Term Click on the downward arrow to the right of the data field to select a term.

• File Name Click on the Path Name icon. . Select the path > double click file you want to load. The file name and path information will display in the File Name box.

Click on the Upload button.

1

2

3

4 5


Load Your Transaction File (continued) Load Messages A dialog box will display the load status. Step 4 Unsuccessful Load A dialog box will display when an error occurred during the load process. Click on the OK button. Verify the file name format. The parameters specified for processing must match the year, term and campus code identified in the file name > resubmit the procedure.

Successful Load The system will display a message when the file has been successfully loaded. This message may display in one of the two forms shown below. When the dialog box is displayed, click on the OK button.


Edit Process Create The Master Files Procedures 39 and 64 will create the Section Master file and the Faculty Master file, respectively. Both of these files are created on the Chancellor’s Office APDB compiler server and are not accessible to the campus users. The edit reports from these procedures will be downloaded to the Chancellor’s Office SFTP file transfer server for your review. Check for errors and correct all catastrophic errors. Do not proceed with the master file audits until your transaction files are error free. The cycle of file transmission, editing, report review, and data revision continues until the transaction files are error-free. Step 1 Select Edit Process.

The Edit Process screen will display.


Edit Process Create The Master Files (continued) Step 2 Click on the 39-Create Section Master File button or the 64-Create Faculty Master File button.

Identifies the prerequisite


Edit Process Create The Master Files (continued) Step 3 Enter the following parameters: • Campus




Click on the Run Edit button.

Step 4 A dialog box will display when the procedures have been successfully submitted. Click on the OK button.

These parameters are system defined.


Edit Process Master File Audit Step 1 Select Edit Process.



Edit Process Master File Audit (continued) Step 2 Click on the 37 – Section Master File Audit button or the 58 – Faculty Master File Audit button.

Step 3 Enter the following parameters:

• Include all records (Y) or only those with errors (N)?

Click on Yes or No button.

• Validate Instructors against faculty file? Click on Yes or No button. This parameter is only valid for procedure 37 – Section Master File Audit.

• Include all data elements (Y/N)? Click on Yes or No button. This parameter is only valid for procedure 58– Faculty Master File Audit.

Identifies the prerequisites


Edit Process Master File Audit (continued)

• Campus




Click on the Run Edit button. Step 4 A dialog box will display when the procedures have been successfully submitted. Click on the OK button.


Edit Process Section Master File Group Code Audit Step 1 Select Edit Process.



Edit Process Section Master File Group Code Audit (continued) Step 2 Click on 34 – Section Master File Group Code Audit.

Step 3 Enter the following parameters: • Campus




Click on the Run Edit button. Step 4 A dialog box will display when the procedure has been successfully submitted. Click on the OK button.

These parameters are defined by the system.



Edit Process Section And Faculty Master Files Crossover Step 1 Select Edit Process.



Edit Process Section And Faculty Master Files Crossover (continued) Step 2 Click on 56 – Section and Faculty Master Files Crossover.





Click on the Run Edit button.

These parameters are defined by the system.



Edit Process Section And Faculty Master Files Crossover (continued) Step 4 A dialog box will display when the procedure has been successfully submitted. Click on the OK button.


Reports The reports procedures will enable you to process selected reports. After the report is processed, the report will automatically download to your secure file transfer server account for your review. Step 1 Select Reports.

Step 2 The Reports screen will display the following reports > click on the report that you want to process. • 53 - Course Section Report • 54 - Section Size Frequency Distribution (SSFD) Report • 55 - Faculty Assignment By Depart (FAD) Report • 61 - Summary By Class and Level (SCAL) Report • 62 - Course and Section Data (CSDR) Report

Identifies the prerequisite


Reports (continued) Step 3 The parameters displayed are report specific. Depending on the report, a different set of parameters will display and the default values can differ. The active buttons are highlighted for you to select a value other than the default. Step 4 Enter your parameters > click on the Run Reports button. Step 5 A dialog box will display when the procedure has been successfully submitted. Click on the OK button.

Sample Report Screens 53 – Course Section Report The Course Section report provides the following information: - Level and mode of instruction for each school and department on campus - Distribution of Full Time Equivalent Students (FTES) and faculty resources (FTEF) necessary

to support the students - Student/faculty ratio (FTES/FTEF) for each level of aggregation - Comparative percentages of a given level of instruction to the department as a whole or of a

department to the school, or of a school to the entire campus


Reports (continued) 54 – Section Size Frequency Distribution (SSFD) Report The SSFD report provides the following information: - Distribution of enrollments by mode of instruction for each school and department on campus - Average enrollments by mode of instruction - Can be used at the campus to study under-and over-enrolled sections in order to re-adjust

course offerings for upcoming terms

55 – Faculty Assignment By Department (FAD) Report The FAD Report provides the following information: - Individuals with a teaching assignment on campus and/or any individual paid through

instructional funds will be listed alphabetically by department. Each individual’s rank and position fraction(s) are indicated along with any assigned time activity

- Faculty member’s section assignments, section enrollments and associated workload. In addition, department and/or school workload summaries are provided by faculty type and rank


Reports (continued) 61 – Summary By Class and Level (SCAL) Report The SCAL report provides the following information: - Number of sections, FTES, FTEF, SCU, WTU, and average section size by mode and level of

instruction - Relevant information for evaluating faculty-staffing needs on campus, utilizing the mode and

level information provided - Analysis of how resources are currently being utilized across course classification categories

within a given school or department

62 – Course and Section Data (CSDR) Report The CSDR report provides the following information: - Statistics on a wide variety of characteristics by classroom and supervision modes of instruction

and three aggregated levels of instruction - Can be used to analyze student enrollment and faculty workload trends - Can be used to make projections for curricula adjustments or to make comparisons between

the different workload factors displayed


Finalize Section/Faculty Data This procedure will create the Section Master and Faculty Master files. Prior to submitting this procedure, both Faculty and Section transaction files need to be re-loaded to the Chancellor’s Office SFTP server. Step 1 Select Finalize Section/Faculty Data.

The Finalize Section/Faculty Data screen will display.


Finalize Section/Faculty Data (continued)





Click on the Finalize Data button. Step 3 A dialog box will display when the procedure has been successfully submitted. Click on the OK button.



Send Notification To The Chancellor’s Office Step 1 Select Finalize Section/Faculty Data.

The Finalize Section/Faculty Data screen will display.


Send Notification To The Chancellor’s Office (continued) Step 2 Enter the following parameters: • Campus




Click on the Notify CO button.

Step 3 An email form will display addressed to the Chancellor Office contacts with an informative message that your files have been finalized. Click on the Send button.


System Messages Screen Type Message Corrective Action Upload Transaction Files Please enter/select a campus. The campus code was

either missing or invalid. Select a valid campus from the dropdown box.

Please enter a valid Year YYYY format.

The four digit year must match the year specified on the transaction file.

The File Name format must be APD.PROD.SECyytcc.

Verify and correct the format and/or name of the Section Transaction file.

The File Name format must be APD.PROD.FACyytcc.

Verify and correct the format and/or name of the Faculty Transaction file.

File uploaded successfully to the Application Server.

Informative message, no action needed.

File uploaded to Application Server failed.

Upload your file to the application server.

The file was not successfully uploaded to the SFTP server. Please contact your system administrator.

Please contact the technical support person for information.

Edit Process Please enter/select a campus. The campus code was

either missing or invalid. Select a valid campus from the List box.


The year specified for processing must be in the YYYY format. For example: 2009

Please select a Term. The Term was either missing or invalid. Select a valid term from the dropdown box.

An unknown error has occurred. Please contact your system administrator.


The Job 39 – has been successfully submitted. Please download the output reports from the sftp server.

Review your output reports.


Review your output reports






System Messages (continued) Screen Type Message Corrective Action The Job 34 – has been successfully

submitted. Please download the output reports from the sftp server.




Reports Please enter/select a campus. Use the List box and select your campus.


The year specified for processing must be in the YYYY format. For example: 2009

Please select a Term. The Term was either missing or invalid. Select a valid term from the List box.



A Discipline/Category Total cannot be requested simultaneously with a School/Department Total. Please try again. You must select at least one total to be printed. Please try again.

Correct the error and reprocess.











Finalize Section/Faculty Data Please enter/select a campus. Use the dropdown box

and select your campus. Please enter a valid Year YYYY

format. The year specified for processing must be in the YYYY format. For example: 2009


System Messages (continued) Screen Type Message Corrective Action Please select a Term. The Term was either

missing or invalid. Select a valid term from the List box.



The Job 99 – has been successfully submitted. Please download the finalized data from the SFTP server.


academic planning database web-based user guide...the academic planning database (apdb) is a...

Documents