ARCHIVED CD 2000-009

Warning This Web page has been archived on the Web.

Archived Content

Information identified as archived is provided for reference, research or recordkeeping purposes. It is not subject to the Government of Canada Web Standards and has not been altered or updated since it was archived. Please contact us to request a format other than those available.

Note This document has been modified. The changes are identified by a vertical line "|". Revision (|)

April 5, 2000

SUBJECT: Electronic Data Transfer--Additional Information on Using Excel-Lotus Pay Equity Tool

1. PURPOSE

1.1. The purpose of this directive is to provide additional instructions to Compensation Advisors in client departments concerning the usage of the Excel and Lotus working tools to create transactions for pay equity payments for the period from March 8, 1985, to March 31, 1989, for accounts under pay cycles 7B and 7C and for all 7A service.

1.2. This directive must be read in conjunction with Compensation Directive 2000-004 dated February 1, 2000, entitled "Pay Equity Implementation - Clerical and Regulatory (CR), Stenographic and Typing (ST), Data Processing (DA)-CON, Hospital Services (HS), Library Science (LS) and Educational Support (EU) Groups", Compensation Directive 1997-041 dated December 5, 1997, entitled "Equalization Adjustment-CR, ST, DA, HS, LS and EU Groups" and Compensation Directive 2000-001 dated January 10, 2000, entitled "Pay Equity Adjustment Reports-CR, ST, DA, HS, LS, and EU Groups".

1.3. In this text, use of masculine is generic and applies to both men and women.

2. BACKGROUND

2.1. In order to permit the Pay Equity Pay transactions to be input directly into the Regional Pay System (RPS), Treasury Board Secretariat (TBS) has approved the use of a process which will allow departments to copy the Pro forma part of their spreadsheets to a 3.5-inch diskette, which will later be downloaded directly to the RPS. This process was created to eliminate the need to data capture all of the pre 1989 and 7A pay equity transactions.

Two working tools, one in Excel and the other in Lotus, have been approved to capture the pay equity information. Both versions create a pro forma and a batch header for each employee. For this exercise, Public Works and Government Services Canada (PWGSC) has constructed two COBOL conversion programs that will extract the required information from the pro forma portion of each spreadsheet, and reformat the extracted data into a readable format for the RPS. Hard copies WILL NOT be accepted for key entry.

3. PROCEDURES

The following sections provide instructions that must be followed by all Compensation Advisors for any spreadsheet that contains 7A service until July 28, 1998, or service from March 08, 1985, to March 31, 1989, under pay cycles 7B and 7C. Please refer to the spreadsheet entry guidelines that apply to you (either Excel or Lotus).

3.1. Process

Compensation Advisors will continue to enter pay equity data using the approved Excel and Lotus pay equity working tools. However, they will not be required to print hard copies of the pro formas or batch headers. Also, procedures will be provided to allow all users to enter Tax Waiver codes 395/396 and Leave Without Pay (LWOP) codes 202/270 directly in the proforma section of the spreadsheets.

PWGSC has created a macro command that will allow you to convert the proforma section of your spreadsheet to "text files". After conversion, the text files are to be copied to a 3.5 inch diskette. When the diskette is full, a message will appear.

A numerical control system has also been established so that all diskettes can be tracked through the entire process and to ensure duplicate payments are not issued.

Once all pertinent text files have been copied to your diskettes, you will be required to send them to your regional pay office by bonded courier to ensure that the information contained on the diskettes is protected. PWGSC is currently in the process of establishing how these diskettes will be received and processed by the regional pay offices. Specific individuals in each regional office will be identified as the authorized recipient of the diskettes. Once this information becomes available, departments and agencies will be advised accordingly.

Upon receipt of the diskettes, the regional pay offices will perform various control functions and load the diskettes into the conversion programs that have been created to extract the pay equity information. The extracted data will then be reformatted into a readable format for input to the PWGSC pay system.

This process will allow for pay equity payments to be issued within the available timeframe specified by TBS and key entry will not be required.

3.1.1. Unprotecting and protecting the spreadsheets

The pay equity spreadsheets have been protected to ensure that the formulas within the spreadsheets are not accidentally changed. You will therefore be required to unprotect the spreadsheets before any entries can be made to the pro forma.

Please note you must exercise extreme caution once the spreadsheet is unprotected. All cells will be unlocked and will accept any entries whatsoever.

3.1.2. To unprotect the Lotus spreadsheet:

  • Load the spreadsheet into Lotus.
  • Click on 'File' then Click on 'Workbook Properties'. This will cause a "Workbook Properties" window to appear.
  • Click on the 'Security' tab.
  • You will notice a check mark in the "Lock Workbook" box. Click once on this box. The check mark will disappear and a "Password" window will appear.
  • Enter in upper case letters, 'PAYEQUITE'for the English version or 'PAYEQUITF'for the French version.
  • Click on 'OK' in the "Password" window. This will return you to the workbook properties window. If the password you have entered is accepted, the check mark will be absent in the 'Lock Workbook' box.
  • Click on 'OK '. The "Workbook Properties" window will disappear. Your spreadsheet is now unprotected.

After you have completed the required entries on the pro forma, it is strongly recommended that you re-protect the spreadsheet to eliminate the possibility of accidental erasure.


3.1.3. To protect the Lotus spreadsheet:

  • Click on 'File' then Click on 'Workbook Properties'. This will cause a "Workbook Properties" window to appear.
  • Click on the 'Security' tab.
  • Click once on the 'Lock Workbook' box. The 'Password' window will appear.

3.1.4. To protect the spreadsheet without a password:

  • Click on 'OK' in the 'Password'window. The "Password" window will disappear and return you to the 'Workbook Properties'window. A check mark will be present in the 'Lock Workbook' box.

3.1.5. To protect the spreadsheet with a password:

  • Enter your password in the 'Password' box. Note that Lotus is upper and lower case sensitive for the password.
  • Reenter the exact same password in the 'Verify Password' box.
  • Click on 'OK' in the 'Password'window. The password window will disappear and return you to the 'Workbook Properties'window. A check mark will be present in the 'Lock Workbook' box meaning the spreadsheet is now protected.

You cannot unlock the spreadsheet unless you know the password.

3.1.6. To unprotect the Excel spreadsheet:

  • Load the spreadsheet into Excel and go to the pro forma section by clicking on the 'Pay office form' tab located at the bottom of your spreadsheet.
  • Click on 'Tools' then Click on 'Protect'. This will cause another menu to appear.
  • Click on 'Unprotect Sheet'. Your spreadsheet is now unprotected.

After you have completed the required entries on the proforma, we strongly recommend that you protect the spreadsheet to guard against accidental changes.

3.1.7. To protect the Excel spreadsheet:

  • Click on 'Tools' then Click on 'Protect'. This will cause another menu to appear.
  • Click on 'Protect sheet'. A 'Protect Sheet'window will appear. You can protect the spreadsheet with or without a password.

3.1.8. To protect the spreadsheet without a password:

  • Click on 'OK' in the 'Protect Sheet'window. The window will disappear indicating that your spreadsheet is protected.

3.1.9. To protect the spreadsheet with a password:

  • Enter your password in the 'Password' box and click on 'OK'. Note that Excel is upper and lower case sensitive for the password.
  • Reenter the exact same password in the 'Confirm Password' box and click on 'OK'. Your spreadsheet is now protected.

You cannot unlock the spreadsheet unless you know the password.

3.2. Entering Pay Cycle/ Pay Frequency/ LWOP Periods/Deficiency Codes and Tax Waiver codes on the Pro forma

Compensation Advisors will be required to convert the proforma section of their spreadsheets to a text file so that the pay equity information can be extracted by the newly created conversion programs. Under normal circumstances, a batch header is required with each pro forma. Since most of the information on the batch header can be duplicated or obtained from the pro forma itself, the conversion programs have been designed to construct a batch header transaction for each pro forma.

This will eliminate the need to produce two separate text files with each spreadsheet. The pay cycle/pay frequency field, which is required in the Batch Header transactions, has been identified as the only field that the conversion programs will be unable to duplicate. Therefore, departments will be required to key the pay cycle/pay frequency directly into the pro forma section of the spreadsheets before converting them to text files.

Also, since no hard copies of the pro forma or batch header will be accepted, a method has been devised which will allow Compensation Advisors to enter the tax waiver codes 395/396 and the LWOP codes 202/270 directly on the pro forma section of their spreadsheets.


3.2.1. Entering the Pay Cycle /Pay Frequency on the Pro forma

As mentioned above, Compensation Advisors will be required to key the pay cycle/pay frequency directly into the pro forma section of the spreadsheets.

The pay cycle/pay frequency entered will be used to identify the pay cycle/pay frequency paylist that the pay equity payment will be paid from. Please note that previous service that was paid on a different pay cycle/pay frequency can be reported on the same pro forma. You must enter the pay cycle/pay frequency of the employee's current paylist or the pay cycle/pay frequency of the paylist the employee was on at the time he left the public service.

To enter the pay cycle and pay frequency on the pro forma, the spreadsheet must be unprotected.

  • Lotus entry:
    • Go to the Pro forma section of the spreadsheet by clicking on the 'Pro forma' tab.
    • Using the 'Arrow' keys, bring the cursor to cell B:A1. This is the very first cell located at the upper left hand corner of the Pro forma. Please note that the cell identification is also displayed above cell B:A1 in the upper left hand corner of your spreadsheet.
    • Enter the correct pay cycle and pay frequency in this area. Please note that the pay frequency must be entered using the upper case (e. g. 7A, 7B).
  • Excel entry:
    • Go to the pro forma section of the spreadsheet by clicking on the 'Pay office form' tab.
    • Using the 'Arrow' keys, bring the cursor to cell A5 which is located on the left hand side of the spreadsheet near the very top. Please note that the cell identification is also displayed in the upper left hand corner of the spreadsheet.
    • Enter the correct pay cycle and pay frequency in this area. Please note that the pay frequency must be entered using the upper case (e. g. 7A, 7B).

3.2.2. Leave Without Pay (LWOP) Periods

Pensionable periods of LWOP between March 8, 1985, to March 31, 1989 must be reported on the spreadsheets. Previously, if you were breaking the periods of service with the periods of LWOP, these entries will have to be adjusted. Now, the full service period is reported and is reduced by the number of LWOP hours in the entire year.

Example: A full time employee in the CR4 classification has qualified service from April 1, 1985 to March 31, 1986. During that year, the employee was on sick LWOP from June 8, 1985 to December 21, 1985.

Previous Lotus entry:
Sub
Pos
.
Start
Date
End
Date
LWOP hrs
7B, 7C only
CR4 1985/04/01 1985/06/07  
CR 4 1985/12/22 1986/03/31  

The service period has been broken by the period of LWOP which is from June 8, 1985 to December 21, 1985.

Adjusted Lotus entry:
Sub
Pos
.
Start
Date
End
Date
LWOP hrs
7B, 7C only
CR4 1985/04/01 1986/03/31 997.50

Previous Excel entry:
Start
Date
End
Date
Group and Level
Subst. Acting
7B, 7C only
Hours LWOP
1-Apr-85 7-June-85 CR4  
22-Dec-85 31-Mar-86 CR4  
Adjusted Excel entry:
Start
Date
End
Date
Group and Level
Subst. Acting
7B, 7C only
Hours LWOP
1-Apr-85 31-Mar-86 CR4 997.50

Delete all your entries for the second line.

The 997.50 entry in the LWOP column represents the total amount of hours in the LWOP period or 133 days multiplied by 7.5 hours per day.

The above adjustment will cause a blank line to appear on the pro forma because a line was deleted. This is of no consequence because when the conversion of information is done, the blank lines will be eliminated.

All service periods reported must be broken by fiscal year. Therefore, if you are reporting periods of LWOP that are over a year in length, there may be instances where the amount payable is nil due to an offsetting LWOP record.

Example: An employee was on LWOP from April 1, 1985, to March 31, 1986, (261 days). When this period is reported on the spreadsheet, a '0'amount will be reflected because 1957.50 hours will also be reported in the LWOP column.


3.2.3. Entering LWOP codes 202/270 on the pro forma

The pay equity adjustment payments will be reduced by the periods of LWOP. Pension contributions for the LWOP periods are to be withheld at the single rate or the double rate.

For all periods after March 31, 1989, pension contributions will be calculated at the appropriate rate automatically. However, for all LWOP periods that took place from March 8, 1985, to March 31, 1989, Compensation Advisors will be required to complete a pay action code (PAC) 71 using entitlement code 202 for single or entitlement code 270 for double rate so that pension contributions can be calculated and collected appropriately.

Normally, PAC 71s are submitted using a form type '53'. However, for this particular exercise , Compensation Advisors will enter their PAC 71s directly to the pro forma section of the spreadsheet which is a form '56'. The COBOL conversion program will extract all PAC 71s from this form and will reformat to the correct form type for input to the pay system. A batch header for the PAC 71s will also be created by the conversion programs.

With each PAC 71 entry, you will be required to report the amount of money the employee would have been eligible to receive under pay equity if he had not been on LWOP. LWOP at single rate and LWOP at the double rate must be reported separately. Once calculated, you will enter this amount as a PAC 71 entry on the pro forma.

To facilitate your calculations, enter the particular LWOP period as a qualified service period in a blank version of the spreadsheet using all pertinent employee information such as group, level and assigned workweek. The spreadsheet will then automatically determine the amount the employee would have received. The amount calculated can then be copied for its subsequent entry on the pro forma portion of the employee's spreadsheet. Please note that for part time employees, you may be required to enter several periods as per the instructions provided with the Excel or Lotus spreadsheets.

Another method of calculating this amount is with the formula:

  • For employees paid at an annual rate:

    (Yearly rate payable under pay equity ÷ 260.88) x the number of work days in the period.
  • For employees paid at an hourly rate:

    (Yearly rate payable under pay equity ÷ 1956.6) x the number of hours in the period.

Treasury Board has established a special pension contribution rate for the retroactive period from March 8, 1985, to December 22, 1996, of 4.7% for single rate and 9.4% for double rate.

Departments must use indicator 'PE' in field 71 on the pro forma part of the spreadsheet. Please refer to Section 5.1.2 of Compensation Directive 2000-004 (date February 1, 2000) for more information regarding LWOP.

It should be noted that Field 61, Field 67 and Field 68 must be blank for all PAC 71 reported.

To enter PAC 71 codes 202/270 proceed as follows: (spreadsheet must be unprotected)

Example: An employee at the CR4 group and level was on LWOP from May 8, 1985, to September 11, 1985. The employee's assigned work week was 37.50 hours. The first step is to determine how much the employee would have received if he was not on LWOP for this particular period. This amounts to $403.93. (We used a blank spreadsheet to determine this amount.) The entire LWOP period can be reported using code 202.

  • Load the employee's spreadsheet into Excel or Lotus.
  • Go to the pro forma section of the spreadsheet by clicking on 'Pay office form' tab for the Excel version or 'Pro forma' tab for the Lotus version.
  • Using the 'Arrow' keys, go to a blank row on the pro forma and bring your cursor to column '60'(columns 8 to 15 will require no input).
  • Enter the information directly in the specified columns as per the following examples:
Lotus entry: PAC 71 (Single rate entry)
60 61 62 63 64 65 66 67 68 71
71   202 ^08/05/85/1 ^11/09/85/2 0 403.93     PE

| Note that you MUST enter '^'(Press Shift+6), before the 'From'and 'To'dates. This tells Lotus to accept your entry in a date format (dd/mm/yy/1or2). The "^" will not be displayed once you exit the cell. Without this symbol, Lotus would convert your entry to a number. If you see *** in field 61 or 62, the column is not wide enough. To change the width of your column, click once on the column letter of the column you wish to widen. This is located at the top of the spreadsheet. The column will be highlighted. Right click and choose 'Fit Widest Number'in the shortcut menu.

Excel entry: PAC 71 (Single rate entry)
60 61 62 63 64 65 66 67 68 71
71   202 08/05/85/1 11/09/85/2 '0 403.93     PE

Note that you MUST enter an apostrophe (') before the 0 in field 65. Once you exit the cell, the apostrophe will disappear and only the "0'will appear in field 65. Please ensure that field 61 is blank.

The following is another example of reporting a continuous period of LWOP using one entry for the single rate and another entry for the double rate.

Example: A full time employee at the CR5 group and level was on LWOP from May 27, 1987, to November 19, 1987. For the LWOP from May 27, 1987, to August 26, 1987, superannuation will be charged at the single rate while the remaining portion of the LWOP period will be charged at double rate.

By using a blank spreadsheet, we determined that the employee would have received $215.55 for the period May 27, 1987, to August 26, 1987, and $199.22 for the period August 27, 1987, to November 19, 1987. The PAC 71 entries for this period of LWOP would be as follows:

Lotus entry :
60 61 62 63 64 65 66 67 68 71
71   202 ^27/05/87/1 ^26/08/87/2 0 215.55     PE
71   270 ^27/08/87/1 ^19/11/87/2 0 199.22     PE

| You must enter '^'(Shift+6) before the From and To dates.

Excel entry:
60 61 62 63 64 65 66 67 68 71
71   202 27/05/87/1 26/08/87/2 '0 215.55     PE
71   270 27/08/87/1 19/11/87/2 '0 199.22     PE

In the above example, the system will calculate a pension contribution amount of $10.13 for the single rate (215.55 x 4.7%) and $18.73 for the double rate (199.22 x 9.4%). Field 61 must be blank when using a PAC 71.


3.2.4. Entering the Tax Waiver code 395/396 on the Pro forma portion of the spreadsheet

If tax waivers are to be applied to the applicable payment, go to the pro forma section of the spreadsheet and bring your cursor to a blank row. Tax waivers are entered on the pro formas using PAC 18Cs.

Lotus entry:
60 61 62 63 64 65 66 67 68 71
18 C 395 ^08/03/85/1 ^31/03/89/2 0 5630.00      
18 C 396 ^08/03/85/1 ^31/03/89/2 0 5630.00      

| You must enter '^'(Shift+6) before the From and To dates.

Excel entry:
60 61 62 63 64 65 66 67 68 71
18 C 395 08/03/85/1 31/03/89/2 '0 5630.00      
18 C 396 08/03/85/1 31/03/89/2 '0 5630.00      

You must enter (') before the 0 in field 65.

3.2.5. 7A Accounts

All PAC 18's reported by Compensation Advisors for 7A accounts must have the Standard Work Week (SWW), Assigned Work Week (AWW) and the Non Standard Work Week Indicator (NSWW) entered in 71, left justified. The AWW must be the same as what is shown on the salary service history for the period being reported.

  • Go to the pro forma section of the spreadsheet by clicking on the 'Pro forma' tab for the Lotus version or the 'Pay Office form' for Excel.
  • Unprotect the spreadsheet.
  • Enter the 9-digit reference number in 71 (left justified).

Example 375022501

  • Protect the spreadsheet.
  • Save the changes.

3.2.6. Personal Record Identifier (PRI) Format

Before closing your spreadsheet, ensure that the PRI (Personal Record Identifier) has been entered as a 9-digit number, e.g. 012345678 (enter leading '0'). Should you determine that the PRI must be reentered, simply return to the Input Sheet portion of the spreadsheet and reenter the PRI using the correct format.

4. CONVERTING LOTUS/EXCEL SPREADSHEETS TO TEXT FILES

The following instructions are for the conversion of the spreadsheets into text files on a 3.5-inch diskette for later transmission. You will be required to perform these procedures on each spreadsheet that contains service periods under pay cycle 7A or any service periods between March 8, 1985, to March 31, 1989.


4.1. Lotus Instructions

  1. Download the "SaveText.123" Macro.
  2. Save in the Pay Equity Directory with other pay equity files.
  3. Insert a diskette in the A: drive. Listed below are the instructions to assign a volume label to a diskette.

New diskette:

  1. Double click on "My Computer" icon.
  2. Select "File", "Format name".
  3. Under "Format Type" select "Full".
  4. Under "Other Options" type the Label name which must be eight characters in length. The first three digits are the department, the next two are the pay office and the last three are a number from 001-999.

    If there are data files on the diskette:

    • Click on the "Start" button and select "Programs", "MS-DOS Prompt".
    • At the command prompt type A: and press <ENTER>.
    • Type the Label name as described above and press <ENTER>.
    • Type exit and press <ENTERto close the "MS-DOS Prompt".
  5. Open the workbook "SaveText.123". This workbook must be open each time users need to save their workbooks in a text format.
  6. Open the workbook that you want to save as text.
  7. Go to the pro forma section of your spreadsheet by clicking on the 'Pro forma' tab.
  8. Print a copy of your pro forma for completing the control log.
  9. Click on 'Actions' and then on 'Save P.E. As text' on the menu bar. This will cause a 'Save P.E. As text' dialogue to appear.
  10. Rename the file using the following format:
    • First three characters must be the department acronym.
    • Last five characters are a number from 00001 to 99999.
  11. Click on 'Save'. The pro forma will be saved on your A: drive as a comma separated value (CSV) file.
  12. When your diskette is full , a warning message will appear as you attempt to save a file. The message will read 'Error writing file! Verify that your disk has enough free space and that it is not write protected!'. This means there is insufficient room on the diskette to save the current file or that your diskette is write protected. Click on OK. The message will disappear and you will return to the 'SAVE AS 'window. Click on 'Cancel' and remove your full diskette from the A: drive or unprotect your diskette. Insert a new diskette in the A: drive, enter the diskette volume label and click on 'Save'. If you attempt to save a spreadsheet that is not pay equity, the following message will appear: 'No sheet in workbook: <workbook name>.
  13. Make a backup of the diskette.

    To make a copy of a disk using Windows95:

    • Click once on 'My Computer' icon. A window will appear showing all the various directories available to you.
    • Click once on your A: drive. This is identified as '3 ½ Floppy (A:)'.
    • Click once on 'File' then click once on 'Copy Disk'. A "Copy Disk" window will appear. Ensure that the 'Copy from' box and the 'Copy to' box both indicate the A: drive.
    • Click on 'Start' and follow the instructions.
  14. Identify the diskettes (original and backup) using the following diskette naming format:
    • First three characters must be the department acronym.
    • Next two characters are the pay office number.
    • Last three characters will be a number from 001 to 999.

    Example: DVA08001

    Please ensure that each diskette has a unique name.

  15. The external label on each diskette must be completed with the following information:
    • Department name/contact number.
    • Pay Office.
    • Diskette name (as described in 13).

Note: Please use a soft tipped pen when writing on the label affixed to the diskette. Otherwise, information may be corrupted.


4.2. Excel Instructions

  1. Download the "SaveText.xls" Macro.
  2. Save in the Pay Equity Directory as "SaveText.xls" along with your other pay equity spreadsheets.
  3. Insert a diskette in the A: drive. Listed below are the instructions to assign a volume label to a diskette.

    New Diskette:

    1. Double click on "My Computer" icon.
    2. Select "File", "Format name".
    3. Under "Format Type" select "Full".
    4. Under "Other Options" type the Label name which must be eight characters in length. The first three digits are the department, the next two are the pay office and the last three are a number from 001-999.

    If there are data files on the diskette:

    • Click on the "Start" button and select "Programs", "MS-DOS Prompt".
    • At the command prompt type A: and press <ENTER>.
    • Type in the Label name as described above and press <ENTER>.
    • Type exit and press <ENTERto close the "MS-DOS Prompt".
  4. Open the workbook "SaveText.xls". Whenever you need to save your spreadsheets as text, this file must be opened first. This makes the macro visible to all the other worksheets you may open.
  5. Click on the "Enable Macro" button.
  6. Open the workbook you want to be saved as text. If any modifications are done to this workbook, they should be saved before doing the "Save as Text" command or they will be lost.
  7. Click simultaneously on keys CTRL + T. This will cause a "Save P.E. As Text" dialogue to appear.
  8. Click once on the file "name box" to bring your cursor into the box. Rename the file using the following format:
    • First three characters must be the department acronym.
    • Last five characters are a number from 00001 to 99999.
  9. Click on 'SAVE'. The pro forma will then be saved on the A: drive as a text file.
  10. The spreadsheet will close automatically.
  11. Repeat the above process (from step 6) until your diskette is full. When your diskette is full, a warning message will appear as you attempt to save the file. The message will read <filenamecannot be accessed. The file may be read-only, or you may be trying to access a read-only location. The server that the information is stored on may not be responding. This message means the particular file you were saving DID NOT get saved as there is insufficient room on the diskette. Click Cancel, replace the diskette in the A: drive and perform the save again.
  12. Make a backup of the diskette.

    To make a copy of a diskette using Windows 95:
    • Click once on 'My Computer' icon. A window will appear showing all the various directories available to you.
    • Click once on your A: drive. This is identified as '3 ½ Floppy (A:)'.
    • Click once on 'File' then click once on 'Copy Disk'. A "Copy Disk" window will appear. Ensure that the 'Copy from' box and the 'Copy to' box both indicate drive A:.
    • Click on 'Start' and follow the instructions.
  13. Identify the diskettes (original and backup) using the following diskette naming format:
    • First three characters must be the department acronym.
    • Next two characters are the pay office number.
    • Last three characters are a number from 001 to 999.
    Example: DVA08001

    Please ensure that each diskette has a unique name.
  14. The exterior label on each diskette must contain the following information:
    • Department name/contact number.
    • Pay Office.
    • Diskette name (as described in 13).

Note: Please use a soft tipped pen when writing on the label affixed to the diskette. Otherwise, information may be corrupted.


5. CONTROL LOG FUNCTIONS

5.1. Along with every diskette sent to your regional pay office, you are required to complete a control log report for each account.

The report must indicate the grand total of transactions and rate amount for all accounts on the diskette. These totals will be used as a control check by your regional pay office.

To facilitate this process, PWGSC has produced a control log in both Excel and Lotus that allows you to capture the information for each account. Once your entries are completed, the spreadsheet will produce a report listing all the information mentioned above.

The control procedures are as follows:

After you have completed copying the text files to a diskette, load the Control log spreadsheet in Excel or Lotus.

In the header section of the spreadsheet insert:

  • Department name
  • Paylist number
  • Diskette name

You can copy text files from different paylists on the same diskette. The spreadsheet will allow for up to 8 separate paylist entries.

Using the information from the pro formas that were previously printed, insert the following for each account, in the applicable column on the spreadsheet:

  • Employee's PRI
  • Surname (first four letters)
  • Total transactions (Total Field 71)
  • Total rate amount (Total Field 66)

The Control Log spreadsheet will allow for up to 100 entries for each diskette.

5.2. After completing all the entries on the spreadsheet, add the transaction and rate totals from each pro forma to ensure that it matches the transaction and rate total at the bottom of the spreadsheet. This is very important. PWGSC will produce a report showing these amounts after each diskette is processed. If the totals do not match, the diskette will be returned to the originating department for corrective action.

Print two copies of the spreadsheet, one for your records and the other copy for your regional pay office. Both signature blocks must be signed to confirm financial authority under section 33 and 34 of the Financial Administration Act. Each diskette sent must be accompanied by a control log report. PWGSC will not process any diskettes that have a missing control log report.

Send the diskette(s) and control log reports, by bonded courier, to your pay office along with a transmittal notice, to the attention of the individual that has been identified as the authorized recipient for your region.

The transmittal notice must contain the following:

  • Department name
  • Departmental contact name and number
  • Pay Office
  • List of all diskettes being sent.

Upon receipt, your pay office will confirm receipt by ensuring that all diskettes listed on the transmittal notice are included along with their corresponding control log reports. Pay Office personnel will then sign the transmittal notice and return a copy to you for your records.

5.3. All diskettes will be processed through an edit routine. If errors are not found, the data obtained will be stored in the system for later processing. If errors are detected, the entire diskette containing the errors will not be processed. A report will be sent to the originating department for corrective action. The Compensation Advisor will retrieve the backup for the disk identified in error and perform the necessary adjustments. This may involve a repetition of some or all of the above steps. Once completed, the corrected diskette will be copied and will be returned to the pay office for processing along with the control log report and the transmittal notice. It is extremely important that the corrected diskette is returned with the same label and contains the same accounts that were on the original diskette. Failure to do this may result in payments being missed or duplicated.

Once data has been stored in the system, Pay Offices cannot alter any of the information.

6. INQUIRIES

6.1. Any request for information regarding the foregoing should be addressed to your PWGSC Compensation Services Office.


Original Signed by
R. Jolicoeur

R. Jolicoeur
Director General
Compensation Sector
Government Operational Service