This lesson should take about 50 minutes to complete. After completing this lesson, you should be able to recall the issues you may need to consider when writing a Salary Management query.

Lesson 4. Questions to Ask before Writing Salary Management Queries

· prerequisites  · using this tutorial  · data training

Before you consider questions specific to querying Salary Management data, make sure you are familiar with the General Guidelines for Executing Queries.

Activity 1
Below are questions you should ask before writing a query to retrieve Salary Management data. Read the questions and their accompanying explanations. (Besides asking these questions, check the cautions in the table documentation in case additional considerations have been noted there that are not included in the questions below.)

1. What do you mean by an "employee"?
   
2. Do you need to access secured employee data?
   
3. Which job do you want?
   
4. Do you want the job title?
   
5. What do you mean by "Organization"?
   
6. What level of payment detail do you need?
   
7. Do you want to look at Distributions? Encumbrances? Payments?
   
8. Do you want to look at distribution or job history?
   
9. Are you trying to tie a payment to a job?
   
10. Do you want to screen payments by Accounting Period, Check Date or Fiscal Year?
11. Do you want hours data, and/or net pay amounts?
12. Should you use the snapshot tables?
    
13. How do you want the query results sorted?
    
14. When do you want the report?
15. Will you want to compare Salary Management detail to General Ledger balances, or Salary Management reports to BEN Financials reports?

1. What do you mean by an "employee"?
   For example, do you want only active employees? Asking for EMPLOYMENT_STATUS (from the COMP_PERSON, EMPLOYEE or EMPLOYEE_GENERAL tables) equal to 'A' only returns records where the latest value in the Payroll system is 'A', although the employee may no longer be at the University and the value may not have been updated. Similarly, a CURRENTLY_EMPLOYED (available in EMPLOYEE_GENERAL) value of 'Y' indicates employees who are currently employed by the University, although they may be on leave of some sort. You probably also want to check the PRIMARY_APPT_BEGIN_DATE and the PRIMARY_APPT_END_DATE in the EMPLOYEE or EMPLOYEE_GENERAL tables (asking for a begin date less than or equal to today's date, for example, and an end date that is greater than or equal to today's date). An alternative is to check the DISTRIBUTION_START_DATE and DISTRIBUTION_STOP_DATE and HISTORY_STATUS in the EMPLOYEE_DISTRIBUTION table, using statements similar to those for the primary appointment begin and end dates, above, and asking for a history status of 'C'.

   Some other factors to consider include:

   Only regular employees (COMP_PERSON, EMPLOYEE or EMPLOYEE_GENERAL.REG_OR_TEMP equal to 'R'), or temporary ones, too?

   Only full-time regular employees (COMP_PERSON, EMPLOYEE or EMPLOYEE_GENERAL.FULL_PART_TIME equal to 'F'), or part-time ones, too?

   Only staff, or only faculty? Check whether FACULTY_CLASS (in JOB_CLASS or JOB_CLASS_GENERAL) is null or not.

2. Do you need access to secured employee data?
   Queries will run faster if you avoid using secured employee data. Use the EMPLOYEE_GENERAL table as much as possible. Avoid using the EMPLOYEE table, or the secured columns in COMP_PERSON, unless you need secured employee data such as: employment date, leave return date, leave reason, separation reason, next salary review date, seniority date, original hire date, tenure status, tenure status date, provost staff minutes date, tenure probation date, academic base salary, primary appointment salary, work phone numbers, publications mail code, affiliation code, sex, birth date, marital status, education level, education level year, country of citizenship, visa, visa expiration date, emergency contact information, or home mailing address.

   You have access to the EMPLOYEE table and the secured columns in COMP_PERSON if you are authorized to access data for the employee's home school or organization.

3. Which job do you want?
   PennWorks supports potentially numerous roles per person, distinguished by job class, dates, role state, and other attributes. The role data is available in CO MP_PERSON_ROLE. If these are paid positions, they're merged into no more than four jobs per person active at one time, and fed to the mainframe Payroll system for use in the payroll process. In EMPLOYEE_JOB, which is fed directly from the Payroll system, an employee may have up to a maximum of four jobs at a time Check whether the job class in the EMPLOYEE_JOB table is equal to the desired job (for example, the PRIMARY_APPT_JOB_CLASS in the EMPLOYEE or EMPLOYEE_GENERAL tables). You may also want to screen job records based on the JOB_DEPT_ORG. (Keep in mind, however, that about 2% of the employees hold more than one job with the same job class in the same organization.) Also, for the latest job data, ask for HISTORY_STATUS equal to 'C' -- and if you want the jobs the employee currently holds, check the appointment begin and end dates (asking for a begin date less than or equal to today's date, for example, and an end date that is greater than or equal to today's date).

4. Do you want the job title?
   If so, for which job? In Business Objects, the primary appointment job in the EMPLOYEE_GENERAL table is linked to the job class in the JOB_CLASS_GENERAL table - if you want the title of the primary job, use JOB_CLASS_GENERAL. If you want the title for all jobs (regardless of whether they are primary appointments), use the JOB_CLASS table.

   Keep in mind that, as a result of the University's Classification Redesign Project, a new job classification system for staff positions was developed in 1998. Job classes in the online Payroll system and the Data Warehouse were converted to this new system over April 8 - 9, 1998. Faculty classes were unaffected by this conversion. Some classes were reused, however, and only the Job_Titles now used for those classes are available.

5. What do you mean by "Organization"?
   Do you want to find employees of only a certain organization? If so, do you want only those of a certain home organization? The home organization is the one responsible for maintaining the employee's PennWorks records -- see HOME_ORG in COMP_PERSON, or HOME_DEPT_ORG in the EMPLOYEE or EMPLOYEE_GENERAL tables. Or do you want only employees whose primary appointment is in the organization? In that case, see PRIMARY_DEPT_ORG in the EMPLOYEE or EMPLOYEE_GENERAL tables. For employees whose roles or jobs are in a particular organization which may or may not be their home or primary organization, screen on ROLE_ORG in COMP_PERSON_ROLE, or JOB_DEPT_ORG in EMPLOYEE_JOB, respectively. Or, if you want to see out of which organization's budget the employee was paid, check POSTED_COA_ORG. Finally, if you are the organization responsible for managing a grant fund, and want to find those employees who were paid from it, screen on RESPONSIBLE_ORG.

   In records before July 16, 1999 you will also find values in the HOME_DEPT, JOB_DEPT and PRIMARY_DEPARTMENT columns. These 5-digit numbers identify, respectively, the department that owned the employee's record, the department with primary responsibility for the employee's job, and the department responsible for the employee's primary appointment. Department numbers were used in the Personnel/Payroll and related systems through fiscal year 1999 and were converted to four-digit organization codes on July 16, 1999. All records after that date will have null values for the department columns, and the organization columns should be used in their place (HOME_DEPT is replaced by HOME_DEPT_ORG, JOB_DEPT by JOB_DEPT_ORG, PRIMARY_DEPARTMENT by PRIMARY_DEPT_ORG). At the same time, school codes were converted to school / center codes, for which the same date cautions apply (HOME_SCHOOL became HOME_SCHOOL_CTR, JOB_SCHOOL became JOB_SCHOOL_CTR).

6. What level of payment detail do you need?
   Within the EMPLOYEE_PAYMENT table, payments are stored by check date, account number and earnings type for each employee. Although monthly paid employees typically have only one payment per accounting period per account number (with an EARNINGS_TYPE of 'RMO'), weekly employees may have many earnings types for one or more accounts for one check date (to record such earnings types as sick pay, vacation pay, or overtime, in addition to regular pay). The EMPLOYEE_PAYMENT_SUMMARY table, however, aggregates payments at the fiscal year level, by employee and account number.

   Keep in mind that PAR_EARNINGS also stores payment information, by earnings type and account combination, for each pay period. This includes payment dollar amounts for all employees, as well as hours worked for weekly-paid employees. The earnings amounts in PAR_EARNINGS should match EMPLOYEE_PAYMENT data for the same pay period, with the exception of the CTU earnings type (CPUP Taxable Tuition).

7. Do you want to look at Distributions? Encumbrances? Payments?
   Planned payments to employees, encumbered payments and actual payment expenditures are stored in different tables: distributions may be found in COMP_PERSON_DISTRIBUTION , COMP_DISTRIBUTION_EXT and EMPLOYEE_DISTRIBUTION; encumbrances are in EMPLOYEE_ENCUMBRANCE; payment data is available in EMPLOYEE_PAYMENT, EMPLOYEE_PAYMENT_SUMMARY and PAR_EARNINGS. If you want to determine the source from which an employee is to be paid or was to be paid as entered into PennWorks, use COMP_DISTRIBUTION and COMP_DISTRIBUTION_EXT. For distribution records that have been processed by the Payroll system, including the start and end dates of the distribution from that source (a DISTRIBUTION_COA_ACCOUNT), consult EMPLOYEE_DISTRIBUTION. Encumbrances, alternatively, are funds which have been set aside by not yet spent for the specific planned approved salary expenditure. To determine the amount currently or previously encumbered for salary expenses, broken down by employee, consult the EMPLOYEE_ENCUMBRANCE table (or the EMPLOYEE_ENCUMBRANCE_CURRENT table for encumbrances for the current period only). Finally, the EMPLOYEE_PAYMENT and EMPLOYEE_PAYMENT_SUMMARY tables store actual salary expenses, after the payments or reallocations were made. Each table contains records by employee, by account, by date. The PAR_EARNINGS table contains similar information, and hours worked as well.

8. Do you want to look at distribution or job history?
   PennWorks allows users to determine a record state for distribution and role data. In COMP_DISTRIBUTION, COMP_DISTRIBUTION_EXT and COMP_PERSON_ROLE, a state of "D" (Disabled) means that the record is no longer in use, although it likely was at some point in the past. A state of "X" (Deleted from PennWorks) may mean that the record was created in error, or could have been used in the past and is no longer active. Future records (state of "F") are also stored as placeholders for future processing.

   Keep in mind that both EMPLOYEE_DISTRIBUTION and EMPLOYEE_JOB store history records. Rows with a History_Status of "C" reproduce the distributions (for EMPLOYEE_DISTRIBUTION) or jobs (for EMPLOYEE_JOB)as they currently exist in Payroll (or as they existed most recently before the employee was purged from Payroll). Rows with a History_Status of "H" represent earlier versions of the same data. Additions and subtractions to the list of an employee's appointments can change the order of jobs.

For EMPLOYEE_DISTRIBUTION:
If you are investigating distribution history, you must take the Job_Dept_Org and Job_Class into account, as well as the History_Status_Date, Last_Extract_Date, Job_Sequence_Number and Distribution_Sequence_Number. In most cases, you should look for rows with a History_Status of "C." Only when tracing distributions over time, even for defunct jobs, would you need to provide the "history" rows. "Current" does not mean "currently active." Querying by this status column will not retrieve an accurate list of currently active distributions. For most queries, limit rows by Distribution_Start_Date and Distribution_Stop_Date as well as History_Status.

For EMPLOYEE_JOB:
If you are investigating job history, take into account the History_Status_Date, the Last_Extract_Date, the Job_Dept_Org and Job_Class, as well as the Job_Sequence_Number. In most cases, look for rows with a History_Status of "C". Only when you need to trace jobs over time would you need to probe through the "history" rows. "Current" does not mean "currently active." You cannot query by this status column and get an accurate list of currently active jobs. For most queries, you will need to limit rows by Appt_Begin_Date and Appt_End_Date as well as History_Status. Note: If there is no definite end date for the employee's appointment to the job, Appt_End_Date will default to the latest available date in the Warehouse, which is 12/31/9999 (although the default in Payroll appears as 99/99/9999).

9. Are you trying to tie a payment to a job?
   In the event that an employee holds two different jobs of the same Job_Class paid by the same account, the Salary Management system does not track specifically which payments (or encumbrances) were made for which job. The Warehouse uses special logic to determine the position associated with a payment, and identifies that position with the Job_Link_ID. The Job_Link_ID identifies a job row in the EMPLOYEE_JOB table, and can be used to join directly to the EMPLOYEE_PAYMENT or EMPLOYEE_ENCUMBRANCE tables (to match payments or encumbrances to job), and also to EMPLOYEE and EMPLOYEE_GENERAL to find the attributes of the primary and primary academic appointment jobs. These joins alredy exist in the Business Objects RSALGMT universe. The Employee Payments class in the univese contains a subfolder, "EP Job", to show job data directly related to a particular payment.
   [Job_Link_ID was added to the Salary Management collection in June 2002. Prior to that, extensive date comparisons were needed to tie payments to jobs to avoid double reporting for job class and organization or historical records, be sure to use the following logic:

   EMPLOYEE_PAYMENT.JOB_CLASS = EMPLOYEE_JOB.JOB_CLASS AND
   EMPLOYEE_PAYMENT.JOB_DEPT_ORG = EMPLOYEE_JOB.JOB_DEPT_ORG AND
   EMPLOYEE_PAYMENT.UNIV_POSITION_NUMBER = EMPLOYEE_JOB.UNIV_POSITION_NUMBER AND
   ((EMPLOYEE_JOB.HISTORY_STATUS_DATE IS NULL AND EMPLOYEE_PAYMENT.CHECK_DATE >= EMPLOYEE_JOB.LAST_EXTRACT_DATE) OR(EMPLOYEE_PAYMENT.CHECK_DATE BETWEEN EMPLOYEE_JOB.LAST_EXTRACT_DATE AND EMPLOYEE_JOB.HISTORY_STATUS_DATE))]

10. Do you want to screen payments by Accounting Period, Check Date, or Fiscal Year?
    Depending on the level of detail or aggregation you need, you can screen on a particular check date (which is stored in the Warehouse in date format), the corresponding financial accounting period, or the fiscal year. The CHECK_DATE is the date on which the payment is available to the employee (and is printed on the check), or the effective date of an adjustment. Accounting periods are identified by month and calendar year, are based on the accounting date in Salary Management, and correspond to the BEN Financials accounting periods. The sum of payments from a given account for a given period should equal the salary balances in the General Ledger collection for the same period. The check date translates by calendar month and year to the accounting period, even if the time worked fell in the previous month. For example, a check date of July 2, 1999 falls in the JUL-99 accounting period, even though most of the work was done in June 1999. Similarly, the accounting period for a given payment translates to fiscal year, regardless of when the work was done. Using the same example, the July 2 1999 check falls in fiscal year 2000 (the JUL-99 period is the first period of fiscal year 2000), even though most of the work was done in fiscal year 1999.

11. Do you want hours data, and/or net pay amounts?
    While EMPLOYEE_PAYMENT stores total gross amounts only, the PAR_MAIN table allows you to see net pay, as well as other gross amounts for each employee, based on home org security. In addition, the PAR_EARNINGS table breaks out the gross amounts by earnings type for all employees, and also includes hours worked for weekly-paid employees.
12. Should you use the snapshot tables?
    The Salary Management snapshot tables were created to facilitate historical reporting of employee, salary, and role/job-related data. Since it can be very cumbersome to report on employee or role/job history from the standard tables, the snapshots capture the employee, distribution, role/job or ethnicity record as it appeared on last day of the accounting period. If you're trying to do longitudinal reporting on employees, and/or their roles/jobs or distributions, refer to the snapshots notes for information about using those tables and the RSALsnap universe.

13. How do you want the query results sorted?
    The query will run faster if the results are sorted in the Business Objects report rather than in the query. However, if the results are to be exported for use by another software package, it is better to specify the sort in the query.

14. When do you want the report?
    Besides helping you plan your workload so you can produce the report on time, you may want to ask this question if the report pertains to the currently open period. You may want to discuss changing the report due date so you run the report as soon as possible after the weekly payments are refreshed, or postpone running it until the period has closed.

15. Will you want to compare Salary Management detail to General Ledger balances, or Salary Management reports to BEN Financials reports?
    The Data Warehouse is updated with payments and encumbrance data directly following Salary Management, which may actually be a couple days in advance of BEN Financials. To pull matching data from BEN Financials, Salary Management and the Data Warehouse at the end of a period you'll need to run your system reports and Warehouse queries between the Tuesday after payroll runs, producing the last weekly check for the month, and before the Monday of the following week. By that last Tuesday, monthly payroll has already been run, so those actuals and updated encumbrances will be correct. This limits the risk of retrieving data from Salary Management or the Warehouse for the last weekly pay of the month which may, in fact, be posted to the next BEN Financials Accounting Period. (For example, for AUG-99, reports would have to have been run between August 27 and August 30th.)

Activity 2
Fill-in-the blank with the appropriate word to recall the questions you need to ask before writing a Salary Management query. Review the questions at the top of this page to determine if your answers are correct.

1. How can you identify active employees?
   
2. You should use EMPLOYEE_GENERAL (rather than the EMPLOYEE table) if possible. Why?
   
3. How many jobs can an employee hold at one time?
   
4. When would you use the JOB_CLASS_GENERAL table (rather than the JOB_CLASS table) to look up a Job_Title?
   
5. What is the difference between the Home_Dept_Org and the Job_Dept_Org?
   
6. Under what circumstances would you use the EMPLOYEE_PAYMENT_SUMMARY table (rather than the EMPLOYEE_PAYMENT table)?
   
7. What is the last period for which legacy accounts are populated in the Salary Management data in the Warehouse? What is the first period for which BEN Financials accounts are populated?
   
8. What is the difference between a distribution and an encumbrance?
   
9. What is the logic for joining the EMPLOYEE_JOB and EMPLOYEE_PAYMENT tables to avoid double-counting records? Using this logic, under what circumstances might some records be double-counted? Which data element can you use to most accurately link payments and jobs?
   
10. If the Check_Date is July 1, 2000, what is the Accounting _Period?
    
11. Under what circumstances should you have your query sort the results (rather than sorting them in the report)?
    
12. What issues should you consider when deciding when you should run a report for the currently open period?
    
13. When is the best time of month to query the Warehouse so that you can compare Salary Management data from the Warehouse to reports from BEN Financials or the Salary Management system?