Teachers Contracts System

PajaroValley Unified School District

Doc rev 2.5

Table of Contents

Table of Contents

Overview

Running the MAGIC Reports

Changing to the Correct Fiscal Year

COR410 – Employee Information Detail Report

COR510 – Position Control Report

COR610 – Employee/Position Report

COR210 – Location Table Report

COR210 – Employee Type Table Report

Downloading and Processing the Reports

Using the “PVContract” Macro

Using the Contract Conversion Tool

Loading Employee Information

Control Table

Creating the Documents

Preparing the Merge Text Files

Preparing the Merge Documents

Available Merge File Fields

Overview

The purpose of the Contracts project is to prepare annual contracts for teachers of PVUSD. The basic information is extracted from MAGIC reports in the CECC system on the HP, massaged in a Visual Basic application, exported to a text file and merged with a Microsoft Word document which is the contract.

To use the system you follow these steps:

  • Run six MAGIC reports in CECC.
  • Run a Reflection macro that downloads the report files from the HP to Monarch, launches Monarch, opens the reports, applies the models and appropriate filters and exports the extract information as comma separated value (.CSV) files
  • Run a stand alone Visual Basic program that creates files to be merged with the contract Word document.
  • Print the contract merge document in Microsoft Word

Each of these steps is discussed in detail later in this document.

Running the MAGIC Reports

Changing to the Correct Fiscal Year

Before running the MAGIC reports you must be in the correct fiscal year in the CECC system. For Teacher Contracts for the new year you need to change to the next fiscal year. At the Select Option on the menu type CJ and press the (outside) Enter key. On the Change Log-On JCW’s screen type the new fiscal year in the New Year field. Press the (outside) Enter key and then press the F7 key to make the change permanent. Press the F8 key to return to the menu. Remember to change back to the current fiscal year after running the reports.

The Change Fiscal Year Screen

The Employee Information Report Submission Screen

COR410 – Employee Information Detail Report

The COR410 report is submitted just like any other report in the CECC system. To select the report:

  • Select Magic.
  • Select Reports.
  • Select 1, Employee Reports.
  • Select 1, Employee Information Report (COR410).
  • In the “Save Rpt Sel as:” field type Contract or fill in the submit form as above and save as Contract for future use
  • Press the (outer) Enter key
  • Press F7, to stream the report.

Note that the report is set to page break by employee name so Monarch is not confused by continuation pages. Also, be sure to set Print SSN to “Y”.

The Position Control Report Submission Screen

COR510 – Position Control Report

The COR510 report is submitted just like any other report in the CECC system. To select the report:

  • Select Magic.
  • Select Reports.
  • Select 2, Positions/Earnings Reports.
  • Select 4, Position Control Report (COR510).
  • In the “Save Rpt Sel as:” field type Contract or fill in the submit form as above and save as Contract for future use
  • If necessary, change the “As of Date” to 07/01 of the appropriate calendar year. This should match the first day of the fiscal year you are processing.
  • Press the (outer) Enter key
  • Press F7, to stream the report.

Note that the report is set to page break by position so Monarch is not confused by continuation pages.

The Employee/Position Report Submission Screen

COR610 – Employee/Position Report

The COR610 report is submitted just like any other report in the CECC system. To select the report:

  • Select Magic.
  • Select Reports.
  • Select 2, Positions/Earnings Reports.
  • Select 1, Employee/Position Report (COR610).
  • In the “Save Rpt Sel as:” field type Contract or fill in the submit form as above and save as Contract for future use
  • Press the (outer) Enter key
  • Press F7, to stream the report.

Note that the report is set to page break by employee name so Monarch is not confused by continuation pages. Also, be sure to set Print SSN and Include Accounts to “Y”.

COR210 – Location Table Report

The COR210 report is submitted in a different manner. Select MAGIC from your menu. Then select 4 – MAGIC Set-Up Programs. Select 1 – Table Master. The following screen will appear.

The Table Master Screen

Press the F4 key or click the To Tbl Rpt button.

Location Table Submit

Fill in the Report Title and move down to the Location table and key an “X”. Press the (outside) Enter Key. Then Press F7 to stream the report.

COR210 – Employee Type Table Report

Now, do the same for the Employee Type table.

Type Table Submit

Fill in the Report Title and move down to the Employee Type table and key an “X”. Press the (outside) Enter Key. Then Press F7 to stream the report.

Downloading and Processing the Reports

After you have successfully run the MAGIC reports go into your Print Queue to identify the file number of the reports. The print queue screen looks like this:

The Print Queue Screen in CECC/Reflection

Make a note of the numbers in the File column for the reports and the COR report that they go with. Notice that the reports are broken into multiple files of 500 pages each. This is because each employee’s information appears on a separate page so Monarch will not be confused by continuation pages. Return to your menu to transfer the files.

Using the “PVContract” Macro

A Reflection macro has been created to simplify the task of transferring the report files to your hard disk and using Monarch to extract the information needed to update the Contract tables. After you have noted the file numbers of the MAGIC reports, return to the Reflection menu. Click the Macro menu option and select the Macros sub-menu option.

Select the Macro Menu Option

From the list of available macros click “PVContract” and click the Run button or double-click “PVContract.”

Select the Contract Macro

A form appears for you to enter the file numbers of the report files.

The Download Reports for Contracts Form

Type the file numbers for the report files under the appropriate heading. For the COR410, COR510 and COR610 reports you may type up to six file numbers and one each for the Location relport and the Employee Type report. If information has not changed you do not need to run the report and you may leave those text boxes empty; the macro will not attempt to transfer a file for that report. For file numbers, you may type just the significant digits of the file as shown in the print queue display.

You will see the Reflection transfer dialog box appear for the report files that you typed. After the files are transferred, the macro launches Monarch, opens the report’s model, invokes the proper filter, if necessary, and exports the extracted information as a Comma Separated (.csv) text file. If you ever need to perform the extract manually use the following table as a guide to the files and models.

Report File / Model / Filter / Extract File
COR210 / COR210.mod / Code / Location.csv
COR210T / COR210.mod / Code / Status.csv
COR55 / COR255.mod / None / Calendar.csv
COR55 / COR255A.mod / None / Month.csv
COR410 / COR410.mod / Positioned / Address.csv
COR510 / COR510.mod / None / Position.csv
COR610 / COR610Employee.mod / Contract / Employee.csv
COR610 / COR610Account.mod / None / Account.csv
COR625 / COR625.mod / None / Additional.csv

Using the Contract Conversion Tool

The Contract Conversion Tool is a Visual Basic program that takes the Monarch exported information and creates the files needed to print the Microsoft Word merge documents for Teacher Contracts. The program is located in your C:\Contracts folder and is named contract.exe. When you launch the program the Teacher Contracts main form appears. Notice that just below the buttons is a darker blue window displaying the Fiscal Year of the information currently loaded.

The Teacher Contracts Main Form

Loading Employee Information

To load the information from MAGIC into the tool’s tables click the Load button on the Teacher Contracts form and the files you exported from Monarch will be loaded into the Contract tables. Information about the load process is displayed in the window in the center of the form.

After the load takes place the Fiscal Year of the just-loaded information appears in the Fiscal Year window just below the buttons; be sure it is correct.

The Teachers Contracts Tool Form After Load

Control Table

The Control table contains such things as school year dates. Before extracting and printing contracts you should make necessary changes to the Control table. Usually this only needs to be done once a year. To access the Control Table Maintenance form select the File menu option and select the Control File sub-option.

The Control Table Maintenance Form

The form allows you to set the following information:

  • From Year is the starting year of the teacher contracts
  • Thru Year is the ending year for teacher contracts
  • Year Dates is the dates for teacher contracts

Creating the Documents

After you are satisfied that all information is correct you are ready to create the text files that Microsoft Word uses to prepare the contract.

Preparing the Merge Text Files

To create the extract text file click the Teacher Contracts button. The form displays a radio button and a series of check boxes to determine which type(s) of contracts to extract.

The Extract Selection

Click the All radio button to extract and print for everyone in the table. Alternatively, check the boxes beside one or more contract type check boxes to extract and print only a select group of contracts. Click the Extract button to proceed with the extract. When the data is processed the form will look similar to this.

The Teacher Contracts Form After Extracting Contract Records

Preparing the Merge Documents

At this point you are ready to print the actual contracts or letters. Start Microsoft Word. Select contract.doc from the C:\Contracts folder.

The Microsoft Word Merge Document

On the Microsoft Word merge document select Merge to Printer and the contracts will print on your Windows default printer. You may prefer to select Merge to File to first review and tweak the contract prior to printing.

Available Merge File Fields

The extract information used in the contract.doc is contained in the file employee.txt. The following fields are contained in this file:

  • Social security number, unmasked (SSN)
  • Employee’s full name (Full Name)
  • Street address (Address1)
  • City, state and ZIP (Address2)
  • Telephone number (Phone)
  • Employee’s first name (First_Name)
  • Position code (Pos_Code)
  • Position description (Position)
  • Position FTE (Pos_FTE)
  • Salary range (Range)
  • Salary step (Step)
  • Salary (Salary)
  • Masters stipend (Mast)
  • Doctoral stipend (Doct)
  • Bilingual stipend (Biln)
  • Stipend total (Stip Total)
  • Total salary (Total Salary)
  • Days per year worked (Days Year)
  • Days per year description (Disp Days)
  • Status code (Status)
  • Employee FTE (EE FTE)
  • Contract start year (FromYear)
  • Contract end year (ThruYear)
  • Contract dates (YearDates)
  • Site code (Site)
  • Health/Welfare amount (HW_Amount)
  • Account 1 (Account1)
  • Account 2 (Account2)
  • Account 3 (Account3)
  • Account 4 (Account4)
  • Account 5 (Account5)
  • Account 6 (Account6)
  • Percent 1 (Percent1)
  • Percent 2 (Percent2)
  • Percent 3 (Percent3)
  • Percent 4 (Percent4)
  • Percent 5 (Percent5)
  • Percent 6 (Percent6)
  • Hire date (Hire Date)
  • Daily rate of pay (Daily Rate)
  • Location description (Location)
  • Units of post BA work (Units)
  • Contract type description (Type)
  • Other Stipend code and amount (Other Stipend)
  • Total compensation (Total Compensation)
  • Social security number, masked (Masked SSN)

Page 1