Employee Compensation Letters
Live Oak School District
Doc rev 2.5
Table of Contents
Table of Contents 2
Overview 3
Running the MAGIC Reports 4
COR210 – Location Table Report 4
COR255 – Work Calendar Report 5
COR310 – Salary Schedule Report 6
COR510 – Position Control Report 7
COR610 – Employee/Position Report 8
PYR530 – Employee Payroll Earnings Report 9
PYR510 – Payroll Postlist Report 9
Downloading and Processing the Reports 10
Using the “LOContract” Macro 10
Using the Letter Conversion Tool 13
Loading Employee Information 13
Control Table 14
Creating the Documents 16
Preparing the Merge Text Files 16
Preparing the Merge Documents 16
Available Merge File Fields 20
Overview
The purpose of the Employee Compensentation Letter project is to prepare annual contracts for teachers of Scotts Valley USD. The basic information is extracted from reports in the CECC system on the HP, massaged in a Visual Basic application, exported to a text file and merged with Microsoft Word document which are the contracts.
To use the system you follow these steps:
· Run four MAGIC reports in CECC.
· Run the Absence Tracking report 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 exports the extract information as text 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
The MAGIC Table Report Screen
COR210 – Location Table Report
The COR210 Location Table report is submitted in the following manner:
· Select Magic
· Go to Table Master
· Press the F4 key to go to Table Reports
· For Report Title, type something that will identify the report in your print queue
· Tab down to the white space to the left of Location and type a letter “X”
· Press the (outer) Enter key
· Press F7, to stream the report.
The Work Calendar Report Screen
COR255 – Work Calendar Report
The COR255 Work Calendar report is submitted in the following manner:
· Select Magic.
· Select Reports.
· Select 2, Positions/Earnings Reports.
· Select 8, Work Calendar Report (COR255).
· For Report Title, type something that will identify the report in your print queue
· In the Summary, Detail, Both selection field type “D” for Detail
· Press the (outer) Enter key
· Press F7, to stream the report.
The Salary Schedule Report Screen
COR310 – Salary Schedule Report
The COR310 Salary Schedule report is submitted in the following manner:
· Select Magic.
· Select Reports.
· Select 2, Positions/Earnings Reports.
· Select 7, Salary Schedule Report (COR310).
· For Report Title, type something that will identify the report in your print queue
· Press the (outer) Enter key
· Press F7, to stream the report.
The Position Control Report Submission Screen
COR510 – Position Control Report
The COR510 report is submitted in the following manner:
· 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.
Remember that the report should 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 in the following manner:
· 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.
Things to remember are that the report should page break by employee name so Monarch is not confused by continuation pages. Also, Set All Employee Effective Dates and Include Vacancies to NO; set Include Earnings, Include Accounts and Print SSN to YES.
The Employee/Position Report Submission Screen
PYR530 – Employee Payroll Earnings Report
The PYR530 report is submitted in the following manner:
· Select New Payroll Systrm
· Select Payroll Reports
· Select 2, Employee Payroll Earnings Report (PYR530).
· In the “Save Rpt Sel as:” field type EMPLETTER or fill in the submit form as above and save as EMPLETTER for future use
· Press the (outer) Enter key
· Press F7, to stream the report.
PYR510 – Payroll Postlist Report
The PYR510 report is run as part of the monthly payroll processing and you will be supplied with the print queue file number of the report by the COE IT department.
Downloading and Processing the Reports
After you have successfully run the reports go into your Print Queue to identify the file number of the reports. The print queue screen looks similar to this:
The Print Queue Screen in CECC/Reflection
Make a note of the numbers in the File column for the reports and the COR or PYR 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 “LOContract” 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 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 “LOContract” and click the Run button or double-click “LOContract.”
Select the Contract Macro
Instead of running the LOContract macro from the Macro menu you may just click the “Letter” tool button if it appears on your Reflection toolbar.
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 most of the reports you may type two or three file numbers. 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 any files for empty boxes.
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 FileCOR210 / COR210.mod / Code / Location.csv
COR255 / COR255.mod / None / Calendar.csv
COR255 / COR255A.mod / None / Month.csv
COR310 / COR310.mod / Classified / Schedule.csv
COR510 / COR510.mod / None / Position.csv
COR610 / COR610Employee.mod / Teachers / Employee.csv
COR610 / COR610Account.mod / None / Account.csv
COR610 / COR610Position.mod / None / EEPosition.csv
PYR510 / PYR510.mod / None / Mandatoryl.csv
PYR530 / PYR530Employee.mod / None / Payroll.csv
PYR530 / PYR530Additional.mod / Omits / PYadditional.csv
PYR530 / PYR530Bilingual.mod / Bilingual / Bilingual.csv
Using the Letter Conversion Tool
The Letter Conversion Tool is a Visual Basic program that takes the Monarch exported information and creates the file needed to print the Microsoft Word merge documents for Employee Compensation letters. When you launch the program the main form appears. Notice that just above the buttons is a darker blue window displaying the Fiscal Year of the information currently loaded.
The Contract Letters Main Form
Loading Employee Information
To load the information from MAGIC into the tool’s tables click the Load button on the main form and the files you exported from Monarch will be loaded into the Letter 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 above the buttons.
The Employee Letter Tool Form after Load
Control Table
The Control table contains such things as school year dates. Before extracting and printing the letters 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 for teacher
· Year Dates for administrators
· Year Dates for others
Creating the Documents
After you are satisfied that all information is correct you are ready to create the text file that Microsoft Word uses to prepare compensation letters.
Preparing the Merge Text Files
To create the extract text file click the Letters button. Letters will be sorted in sequence by site and employee name. When the data is extracted the form will look similar to this.
The Employee Letter Form After Extracting Contract Records
Preparing the Merge Documents
At this point you are ready to print the actual contracts. Start Microsoft Word. Select Letter.doc from the C:\Contracts folder.
File Open Menu
Click the File menu option and select the Open sub option.
Look In Pull Down
Pull down on the Look In pull down by clicking the arrow to the right of the box.
Available Drives
Select Local Disk (C:) by double-clicking it.
Folders on Local Disk
Select the Contracts folder by double-clicking it.
Documents and Folders in the Contracts folder
Select the Contract.doc merge document by double-clicking it. You may be asked to confirm the data file to use (depends on your version of Microsoft Word and its settings); click the OK button
The Microsoft Word Merge Document
On the Microsoft Word merge document select Merge To New Document and save them as your “printed” contracts. This file may be changed in Word before actually printing the contracts.
Merge to Document button
Available Merge File Fields
The extract information used in the SCCSContract.doc is contained in the file employee.txt. The following fields may be inserted into a document from this file:
· Social security number (SSN)
· Employee’s full name, First, MI, Last (Full Name)
· Street address (Address1)
· City, state and ZIP (Address2)
· Telephone number (Phone)
· Employee’s first name (First_Name)
· Position FTE (Pos_FTE)
· Salary range (Range)
· Salary step (Step)
· Salary (Salary)
· Masters stipend (DispMast)
· Doctoral stipend (DispDoct)
· Bilingual stipend (DispBiln)
· Total of stipends (StipTotal)
· Total salary (AllTotal)
· Longevity stipend (LongAmount)
· Days per year worked (DaysYear)
· Days per year description (DispDays)
· Tenured status check (St_Tenure)
· Prob 2 status check (St_Prob2)
· Prob 1 status check (St_Prob1)
· Temporary status check (St_Temp)
· Employee FTE (EE_FTE)
· Contract start year (From_Year)
· Contract end year (Thru_Year)
· Contract dates (YearDates)
· Three-digit site code (Site)
· Accrued sick leave balance 6/30 (Sick_Accrual)
· Sick leave granted 7/1 (Sick_Granted
· Sick leave balance (Sick_Balance)
· Personal Business balance (Personal_Business)
· From 1 to 6 accounts strung together (Account)
· Hire date (Hire_Date)
· Daily rate of pay (Daily_Rate)
· Location description (Location)
· Additional college units (Units)
· From 1 to 6 position codes strung together (Pos_Code)
· From 1 to 6 position descriptions strung together (Position)
Page 2