Public Queries

Understanding the Available Queries in GEMS: Using the Query Manager, Advanced Search functionality, GEMS users can search for a desired query by its functional group folder. Below is a listing of the HR approved public queries for end users and their folder assignments. A brief description of each query is provided.

GEMS Navigation: Home > Reporting Tools > Query > Query Manager

ALT INFO – Automated Leave Tracking

  • U_LEAVE_BAL_YTD_ACTV_BY_SUPID: provides balances for all leave types associated with all active employees by supervisor.
  • U_LEAVE_BAL_YTD_TERM_BY_DEPT: provides balances for all leave types associated with all terminated employees within a department where the leave hours balance is greater than zero.
  • U_LEAVE_BAL_YTD_TERM_BY_SUPID: provides balances for all leave types associated with all terminated employees by supervisor where the leave hours balance is greater than zero.
  • U_LEAVE_POSTED_BY_DEPT (Active & Terminated): provides posted leave information for all leave types associated with all active and terminated employees within a department.
  • U_LEAVE_POSTED_BY_SUPID (Active & Terminated): provides posted leave information for all leave types associated with all active and terminated employees by supervisor.
  • U_LEAVE_APPR_NOT_POST_BY_DEPT (Active & Terminated): provides approved leave information for all leave types associated with all active and terminated employees within a department.
  • U_LEAVE_APPR_NOT_POST_BY_SUPID (Active & Terminated): provides approved leave information for all leave types associated with all active and terminated employees by supervisor.
  • U_LEAVE_PEND_NOT_APPR_BY_DEPT (Active & Terminated): provides pending leave information for all leave types associated with all active and terminated employees within a department
  • U_LEAVE_PEND_NOT_APPR_BY_SUPID (Active & Terminated): provides pending leave information for all leave types associated with all active and terminated employees by supervisor.

APPOINTMENT INFO

  • U_ALL: Prompts by Department ID and College/Division. This is a query of all currently active employees. USF Report: All Employees Contact Info & All Employees Job Info
  • U_ANP1: Prompts by COMPANY, VP Area, Campus, College/Division, or Department ID. This is a query of all current active A&P employees. USF Report: A/P Appointments
  • U_APP1: Prompts between Effective Dates, Also by Company, VP Area, Campus, College/Division, or Department ID. This is a query of all new hires on a Position between Effective Dates. USF Report: New Hires
  • U_APPOINTMENT_HISTORY: Prompts by Department ID or Employee ID. Gives Appointment History of Actions and Action Reasons that are not superseded. Not Effective Dated on Effective Dated Tables so gives you History, Current and Future Appointments Information
  • U_APPOINTMENTS_GA: Prompts by Company, VP Area, Campus, College/Division, or Department ID. This is a query of all current active Graduate Assistants (9181-9185)
  • U_APPTS_STUDENT_HOURLY: This query lists current data for active student hourly appointments. Based on the U_JOB_EMPL_C_VW, DEPT_TBL, JOBCODE_TBL & U_PERS_DATA_VW tables. Output includes Employee Identification, Job, demographics & location data, Phone, Comp & Annual Base Rates, FTE, OPS Service Date, Term Date and Dual Compensation Approval
  • U_AUTOMATIC_INCREASES: Prompts for Fiscal Year and Salary Plan or Department ID or College/Division or Campus or USF (for All). This is a query showing information relating to the Legislative Competitive Pay Increases.
  • U_CEQA: Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_ADM_TITLE_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & COMP_FREQ_VW Tables, this query gathers classification equity data for A&P employees. It prompts by Company, VP Area, Campus, College/Division, Department ID or Job Code. Returns Employee Identification, Job, demographics, union & location data, Phone, Compensation frequency & Annual Rate, Annual Base Rates, Employee Class, Service Date, EEO-6 Cat, FTE, OPS Service Date, Termination Date, Grade, all pay components & Biweekly Rate. USF Report: Class Salary Equity – A/P
  • U_CEQF: Prompts by COMPANY, VP Area, Campus, College/Division, Department ID or Job Code. Class Equity query for Faculty. It prompts by Company, VP Area, Campus, College/Division, Department ID or Job Code. Returns Employee Identification, Job, demographics, union & location data, Phone, Compensation frequency & Annual Rate, Annual Base Rates, Employee Class, Service Date, EEO-6 Cat, FTE, OPS Service Date, Term Date, Grade, all pay components & Biweekly Rate. USF Report: Class Salary Equity – Faculty
  • U_CEQU: Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_ADM_TITLE_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & COMP_FREQ_VW Tables, this query gathers Classification Equity data for USPS emps. It prompts by COMPANY, VP Area, Campus, College/Division, Department ID or Job Code. Returns Employee Identification, Job, demographic, union & location data, Phone, Comp frequency & Annual Rate, Annual Base Rates, Employee Class, Svc Date, EEO-6 Cat, FTE, OPS Service Date, Term Date, Grade, all pay components & Biweekly Rate. USF Report: Class Salary Equity – USPS
  • U_DIRECTORY_LISTING: Prompts by company, VP Area, Campus, College/Division or Department ID. Gives all active phased retirees, courtesy, A&P, A&P Executives, Faculty and USPS (except employee records between 600 and 899)
  • U_EMPLID_SEARCH: Prompts by Social Security number and gives you Employee ID and Name from U_PERS_DATA_VW
  • U_ESC1: Gives extra compensation appointments within a work unit. Prompted by Pay End Date (Begin) of ESC action, Pay End Date of ESC Action, Company, College/Division Code, Paying Department ID or Campus Code. USF Report: Extra Comp by Paying Dept(s)
  • U_ESC2: Gives extra compensation appointments for all employees within a work unit regardless of department in which extra compensation appointment exists. Prompt by Pay End Date (Begin) in Date of ESC action, Pay End Date of ESC Action, Company, College/Division Code, Home Department ID or Campus Code. USF Report: Extra Comp by Home Dept(s)
  • U_FAC1: Prompted by Company, VP Area, Campus, College/Division or Department ID. Faculty Appointments. Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_ADM_TITLE_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & COMP_FREQ_VW tables, this query returns a list of detailed faculty data. Prompted by Company, VP Area, Campus, College/Division or Department ID. USF Report: Faculty Appointments
  • U_FACO: Prompted by Company, VP Area, Campus, College/Division or Department ID. Sal Plan 06,08; Job Code not equal to 9188. USF Report: Faculty OPS Appointments
  • U_FWSP_BALANCES: Query gathers FWSP award balance, amount & paid as well as recipient data Prompted by Department & Effective Dating criteria. Uses records: U_FWSP_DATA, PERSONAL_DATA, JOB & EMPLOYMENT. USF Report: FWS Balances by Dept
  • U_HRLY: Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & SAL_PLAN_TBL Tables, this query lists all Hourly OPS Appointments. Prompted by Company, VP Area, Campus, College/Division or Department ID. Output returns EmplID, Job, demographics, union & location data, Phone, Compensation Frequency, Annual Rate, and Annual Base Rates, Mail Drop, Std Hrs/Wk, Employee Class, Service Date, EEO-6 Cat, FTE, OPS Service Date, Termination Date, Grade, all pay components. USF Report: Hourly OPS Appointments).
  • U_OPS_FUTURE_TERM_DATES: Prompt by Termination Date (Begin), Termination Date (End), AND Company, VP Area, College/Division Code, Department ID or Campus Code. Sal Plans 00,05,06,08,09 that are active and the department ID is not 0-0206-900 (Used for Non-Recurring appointments) Uses U_JOB_EMPL_C_VW to access future data.
  • U_PSOP: Gives data on non student salaried OPS appointments. Prompted by Company, VP Area, Campus, College/Division or Department ID. Output returns Employee Identification, Job, demographics, union & location data, Phone, Compensation frequency & Annual Rate, Annual Base Rates, Mail Drop, Std Hrs/Wk, Employee Class, Service Date, EEO-6 Cat, FTE, OPS Service Date, Termination Date, Grade, all pay components. Also tied into a USF report in the Administer Workforce > Administer Workforce (US) Menu for Professional Salaried OPS Appointments. Sal Plan 00 and Employee TYPE is ‘S”. Query is associated with Public Report: U_PSOP (Prof Salaried OPS Appointments). And found in GEMS via: Workforce Administration > Workforce Reports > USF Reports.
  • U_RESO: Resident OPS. Prompted by Company or Department ID. Output returns Employee Identification, Job, demographics, location data, Phone, Short Employee ID, Annual Rate, Annual Base Rates, Mail Drop, FTE, OPS Service Date, Termination Date, Grade, all pay components. Query is associated with Public Report: U_RESO (Resident OPS Appointments). And found in GEMS via: Workforce Administration > Workforce Reports > USF Reports.
  • U_SOPS: Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & SAL_PLAN_TBL Tables, this query lists all active OPS Student Assistants & OPS Graduate Assistants. Prompted by Company, VP Area, Campus, College/Division or Department ID. Output returns Employee Identification, Job, demographics, union & location data, Phone, Comp frequency & Annual Rate, Annual Base Rates, Mail Drop, Std Hrs/Wk, Employee Class, Svc Date, EEO-6 Cat, FTE, OPS Service Date, Termination Date and all pay components.
  • U_STAFF_EVALUATION_INFORMATION: Prompted by Company, VP Area, Campus, Department ID. Gives you current Evaluation Information for the area prompted. The Query returns Name, Employee ID, Employee Record #, Position #, Position Title, Job Code, Job Title, Job Entry Date, Anniversary Date, Probation End Date Last Review Date, Appraisal Type, Employee Class Code, Employee Class Description, FTE, Department ID, Department Name, VP Area & USF Position Date
  • U_SUPERVISOR_OPS: This query can be run by GEMS Department ID and by Pay Plan. This query shows each OPS employee in a department or pay plan, and the supervisor for that employee that is recorded in GEMS. The supervisor data is used for the official university organization charts, automated leave tracking, mailing lists to supervisors, and other purposes. This query allows users to audit the supervisory information. If you want to see employees in all OPS pay plans be sure to enter the wildcard (% sign) at the pay plan prompt. (Please note that the query may return multiple rows for some positions if the employee or supervisor has more than one appointment.) This query gathers data from the U_SUPERVISOR_VW & EMPLOYEES records. It returns an employees Name, ID, Rcd#, Title, Salary Plan, Position #, Reports-to Position w/blank field for correction, as well as the supervisors identification information.
  • U_SUPERVISOR_POSITIONS: This query can be run by GEMS Department ID and by Pay Plan. This query shows each position in a department or pay plan, and the supervisor for that position that is recorded in GEMS. The supervisor data is used for the official university organization charts, automated leave tracking, mailing lists to supervisors, and other purposes. This query allows users to audit the supervisory information. If you want to see employees in all three pay plans (USPS, A&P, and Faculty), be sure to enter the wildcard (% sign) at the pay plan prompt. (Please note that the query may return multiple rows for some positions if the employee or supervisor has more than one position.) This query gathers data from the U_SUPERVISOR_VW & EMPLOYEES records. It returns an employees Name, ID, Rcd#, Title, Salary Plan, Position #, Reports-to Position w/blank field for correction, as well as the supervisors identification information.
  • U_TENURE_INFORMATION: Gives you Tenure Information for Employees based on the U_JOB_EMPL_C_VW tied to EG_TENURE_DATA. It returns active faculty tenure information including identification data, Job Code & Title, Position #, Faculty Rank, Date of Rank, Annual Base Rate, Job FTE, Tenure FTE, Tenure Status, Tenure Status Date, Tenure Granted Date, USF Position Date, Employee Status, Contract Period, Gender, Ethnic Group, Job VP Area, Faculty Administrative Code, Administrative Title and Mail drop.
  • U_TER1: Prompts between Effective Dates, Also by COMPANY, VP Area, Campus, College/Division, or Department ID. This is a query of all terminations on a Position between Effective Dates. USF Report: Terminations
  • U_USPS: Based on JOB, U_PERS_DATA_VW, U_CONTRACT_TBL, U_COMPRATES_VW, EMPLOYMENT, DEPT_TBL, JOBCODE_TBL & COMP_FREQ_VW tables, this query returns a list of detailed USPS data. Prompted by Company, VP Area, Campus, College/Division or Department ID. Output returns Employee Identification, Job, demographics, union & location data, Phone, Compensation Frequency & Annual Rate, Annual & Hourly Base Rates, Employee Class, Service Date, EEO-6 Cat, FTE, Termination Date, Grade and all pay components. USF Report: USPS Appointments
  • VAC_POSIT_BY_VP_COLLDIV_Department ID: Based on POSITION_DATA, DEPT_TBL, JOBCODE_TBL, SAL_PLAN_TBL, U_BUDGETED_DATA, ACCT_CD_TBL tables, this query generates a list of Vacant Active Position Data. Prompted by either Department ID, VP Area or College/Division. Output returns Position#, Acct Code, State Rpt #, Dept ID & Name, College/Division, Job Code & Title, Budg FTE, Reports to Pos#, Union Code, Shift, Leadworker, Time Ltd, Competative Area DifferentialCode, Effect. Status, Sal Plan & Descr., Fund Code, Project/Grant ID & Budget Amt

BUDGETED POSITION

  • BPA_BUDGET_POSITION_QUERY: This query gathers data from the U_BUDGETED_POS &ACCT_CD_TBL.It is prompted by 1st 6 Chars of Department ID & obtains Position#, Adjst Dt, Department ID, Budget Amt, Acct Code, Operating Unit, Fund, Product, Initiative, ProjectID, Do Not Recreate, Job Code, Title, Sal Plan, Fac Admin Code, Grade, Work Period, Position Type, Name, EmplID, Empl Class & FTE. USF Report: Budgeted Position Summary Rpt

PAY DISTRIBUTION:

  • UPAYS1: Based on U_PAYS1_VW table, this query returns data Prompted by fiscal yr & Dept ID. Output includes Department ID, Sal Plan, Job Code, Distribution%, Acct Code, employee ID, Record Number, Name, Position#, FTE & Dept Descr

PAY HIST PREFAST

  • U_PAY_EXPEND_BY_ACCT: This query is for historical reporting for Pay Periods #0001 through #0401. Based on U_PAY_HISTORY & U_PAY_HIST_DATA tables, gathers pay history data prompted between Pay Period IDs. Edit variable Account Codes in criteria first. Output includes EmplID, SSN, Envelope#, Pers Type, Comp Rate & Comp FTE, Department ID, Acct Code, Vchr/Agncy Doc, Pay Period, Rct Type, Sep Chk#, Pcnt Distrib, Retirement Code, BOSP Chk#, Reg Hours, OT HRS, Other Hrs, Biweekly Rate, OT Earnings, Retiree Match, ORP, FICA, Ins & Life Match Amts., Disability & Pretax Amt. & Sal Admin Plan. USF Report: Pay Expenditure by Account
  • U_PAY_EXPEND_BY_DEPTID: This query is for historical reporting for Pay Periods #0001 through #0401. Based on U_PAY_HISTORY & U_PAY_HIST_DATA tables, gathers pay history data prompted by Dept and Pay Period IDs. Output includes EmplID, SSN, Envelope#, Pers Type, Comp Rate & Comp FTE, Department ID, Acct Code, Vchr/Agncy Doc, Pay Period, Rct Type, Sep Chk#, Pcnt Distrib, Retirement Code, BOSP Chk#, Reg Hours, OT HRS, Other Hrs, Biweekly Rate, OT Earnings, Retiree Match, ORP, FICA, Ins & Life Match Amts., Disability & Pretax Amt. USF Report: Pay Expenditure by Deptid
  • U_PAY_EXPEND_BY_EMPLOYEE: This query is for historical reporting for Pay Periods #0001 through #0401. It Prompts by Employee ID or SSN by Pay Period Numbers. Gives record of Pay Expenditure transfer in that period. Uses U_PAY_HISTORY and U_PAY_HIST_DATA tables. USF Report: Pay Expenditure by Employee

PAYROLL CERTS

  • U_CERTS_CERTIFIER_BY_DEPTID: Based on U_DEPT_CERT_TBL, JOB, JOBCODE_TBL & U_PERS_DATA_VW tables, this query provides a list of CERTS Certifyers. Fill in Dept ID prompt to receive Certifier ID, Name, Position, Department ID & job code
  • U_CERTS_PREPARER_BY_DEPTID: Based on U_PREPARER_VW & U_PERS_DATA_VW tables, this query provides a list of CERTS Preparers. Fill in Dept ID prompt to receive Certs Preparer ID, Name & Department ID

PAYROLL LEDGERS

  • U_PAYACTUALS: Payroll reporting for payroll postings as of Pay Period #0402 through current. This query grabs pay actual data from the U_PAYACTUALS_VW record. Requires the PS wildcard (%) to be entered for any non-defined prompts
  • U_PAYROLL_POSTING_LEDGER: Payroll reporting for payroll postings as of Pay Period #0402 through current. Provides details of Payroll posting by Pay Period End Date. Prompted by GEMS (HR) Dept ID, EmplID, and Chartfield values. Output fields include: Pay Period No., Pay Period End Date, HR Department, Last Name, First Name, ID, Empl Rcd#, Position, Job Code, Sal Plan, Pay Group, on Job, Sep Chk #, Off Cycle, RET Seq#, RET Status, RET BC Date, Check CorrectedD,t Operating Unit, DeptID, Fund, Prod, Initiative, Proj/Grt, Max State Rpt Num, Comp Rate, Distribution %, A&P Earnings, Faculty Earning, USPS Earnings, OPS Earnings, Admin Earnings, Bonus Earnings, Health Match Amt, Life Match Amt, Retire Match Amt, FICA Match Amt, PreTax Assess Amt, Leave Alloc Amt Disability Amt, Total-Amt, CA GL Run. Requires the PS wildcard (%) to be entered for any non-defined prompts

PAYROLL REGISTER

  • U_DEPT_CHECK_REG: Prompts between Pay End Dates and by GEMS Department ID. Gives all Confirmed or Calculated checks in the period prompted grouped by Department ID prompted. USF Report: Dept Check Register

PERT

  • U_ER_ADMIN_DEPT_SUPV: This query uses the U_EFFORT_REPORT, U_EFFORT_DATA & U_JOB_EMPL_H_VW tables to produce effort Reporting Data by Department ID. Fill in prompts for Department ID as well as Pay Period Begin & End Dates Prompts for Department ID Like and Reporting period; returns a list of effort reports, supvid, Reports To or their missing value for each effort report.. Output includes Employee Id, Name, Record Number, Sal Plan, Fis Yr, Pay Begin Dt, Pay Period End Dt, Effect Dt, effort Cert., Changed, DA Change, Employee Change, Reject, Due Dt, Effective Rep. Department ID, Job Rec Department ID, Sal Plan, Reports To Supv ID.
  • U_ER_ADMIN_PAY_ERN_DIST: This query uses the tables: PAY_CHECK – Pay Check, PAY_ERN_DIST – Pay Check Earnings Dist, PERSONAL_DATA – Emplid / Name, ACCT_CD_TBL – Account Codes, JOB – EE Job History, PAY_CALENDAR – Pay Calendar, and U_PERT_TERM_PP – PERT IN-TERM Pay Periods. Query returns U_TERM_BEG_DT, U_TERM_END_DT, U_TERM_ID, NAME, EMPLID, EMPL_RCD, DEPTID, RUN_ID, OFF_CYCLE, PAY_END_DT, U_PP_PRORATE_PCT, ACCT_CD, PROJECT_ID, FUND_CODE, ERNCD, SUM( B.EARNINGS), REVERSED, SAL_ADMIN_PLAN, EMPL_STATUS, EFFDT, EFFSEQ, COMP_FREQUENCY, .FTE, U_COMP_FTE, POSITION_NBR, REPORTS_TO, SUPERVISOR_ID, COMPANY, PAYGROUP, DEPTID, EFFDT, EFF_STATUS, DESCR, ACCOUNT, PRODUCT, OPERATING_UNIT, CHARTFIELD1, U_SAMAS_ACCOUNT, U_BUD_ENT, LAST_NAME, FIRST_NAME, ACCT_CD. The purpose is to extract the payroll data for employees in a way that mirrors the View Pay History feature in PERT. This report can be run whether or not an employee has an effort report. So, it can be used by departments proactively to review payroll. This query contains prompts for TERMID, Emplid, Deptid or Project. It is recommended that this query be run by term. It is also recommended that at maximum it should be run by Project, but is not recommended that it be run by Deptid
  • U_ER_ADMIN_PAY_TERM: This query contains the tables: U_ER_ERNHIST_VW, ACCT_CD_TBL, PERSONAL_DATA, and JOB and prompts for TERMID and Emplid. Query returns U_TERM_BEG_DT, U_TERM_END_DT, U_TERM_ID, DESCR, NAME, EMPLID, EMPL_RCD, DEPTID, PAY_END_DT, U_PP_PRORATE_PCT, ACCT_CD, PROJECT_ID, HP_RETDIST_SEQ_NO, HP_RETDIST_STATUS, TRANSACTION_DATE, ERNCD, EARNINGS, U_EARNINGS_PRORTD, U_PP_PRORATE_TYPE, ELIGIBLE, U_EARNINGS_EFFORT, DESCR, ACCOUNT, DEPTID, PRODUCT, FUND_CODE, OPERATING_UNIT, CHARTFIELD1, U_BUD_ENT, U_SAMAS_ACCOUNT, EFF_STATUS, JOBCODE, POSITION_NBR, EMPL_STATUS, SAL_ADMIN_PLAN, LAST_NAME, FIRST_NAME, ACCT_CD, EFFDT for a given employee and term. The purpose is to extract the payroll data for a given employee in a way that mirrors the View Pay History feature in PERT. This report can be run by departments to proactively review payroll and is not dependent on whether or not an effort report exists
  • U_ER_ADMIN_PETS: Pre-FAST! This query contains the tables: PERSONAL_DATA,ACCT_CD_TBL, JOB, U_PRE_FAST_HD, and PAY_CALENDAR and prompts for DEPTID LIKE, PROJECT_ID LIKE, EMPLID LIKE AND U_TERM_ID LIKE. However, it is not recommended to attempt to run this query by deptid.. Query returns U_TERM_BEG_DT, U_TERM_END_DT, U_TERM_ID, NAME, EMPLID, EMPL_RCD, DEPTID, PAY_END_DT, RUN_ID, U_PAY_PERIOD, U_PP_PRORATE_PCT, U_REC_TYPE, TRANSACTION_DATE, U_PAY_HIST_SEQ_NUM, ACCT_CD, PROJECT_ID, FUND_CODE, U_BIWEEKLY_RT, U_OVERTIME_EARNING, OTH_EARNS, SAL_ADMIN_PLAN, EMPL_STATUS, EFFDT, EFFSEQ, COMP_FREQUENCY, FTE, U_COMP_FTE, POSITION_NBR, REPORTS_TO, SUPERVISOR_ID, COMPANY, PAYGROUP, DEPTID, EFFDT, EFF_STATUS, DESCR, ACCOUNT, PRODUCT, OPERATING_UNIT, CHARTFIELD1, U_SAMAS_ACCOUNT, U_BUD_ENT, LAST_NAME, FIRST_NAME, ACCT_CD. The purpose of this query is to review PETs for employee(s) by project, or deptid for pay periods prior to 7/4/2003
  • U_ER_ADMIN_REVIEW: Based on the U_EFFORT_VW table, this query gathers effoRate View Data by Dept ID Status of ALL Effort Reports for all reporting periods prompted by department (like). Output includes Department ID, employee ID, Name, Employee Record Number, Pay Begin Date, Pay Period End date, Salary Plan, Effort Certification, Changed, DA Change & Reject
  • U_ER_ADMIN_SUPV: This query uses the U_EFFORT_REPORT, U_EFFORT_DATA, U_JOB_EMPL_H_VW, ACCT_CD_TBL, U_PROJECT_INFO & PERSONAL_DTA_VW tables to produce effort Reporting Data by Supervisor ID. Prompts for Reporting Period and Supvervisor ID and returns the effort reports for one supervisor (OPS). Output includes Employee Id, Name, Record Number, Sal Plan, Fiscal Yr, Pay Begin Dt, Pay Period End Dt, Effect Dt, effort Cert., Changed, DA Change, Employee Change, Reject, Due Dt, Effective Rep. Department ID, Job Rec Department ID, Sal Plan & Supervisor Name
  • U_ER_ALT_CERTS_FOR EMPLID: This query uses the tables U_PERT_CERTS, U_EFFORT_DATA, and PERSONAL_DATA. It returns DEPTID, EMPLID, NAME, EMPL_RCD, SAL_ADMIN_PLAN, PAY_BEGIN_DT, PAY_END_DT, FISCAL_YEAR, U_CERTIFIER_ID, NAME, U_CERTIFIED_BY. The purpose is to identify the allowable certifiers for uncertified effort reports for a given DeptID
  • U_ER_HISTORY_EMPLID: Query uses tables U_EFFORT_REPORT and U_EMPLOYEES_VW and prompts for Emplid. It Returns EmplID, Empl Rcd Nbr, Salary Administration Plan, Effective Date, Effort Certification, Date of action, Certifier’s ID, Changed, Dept Admin Change, Reject, Due Date, Department, Name, Fiscal Year, Pay Period Begin Date, Pay Period End Date for all effort reports in the system for a given employee
  • U_ER_STATUS_EMPL: Query uses tables U_EFFORT_REPORT and U_EMPLOYEES_VW. Prompt by Fiscal Year, Pay Begin Date, Pay End Date and Employee ID. Query returns EMPLID, EMPL_RCD, EFFDT, SAL_ADMIN_PLAN, U_CERTIFY, ACTION_DATE, U_CERTIFIER_ID, CHANGED, U_DA_CHANGE, U_REJECT, DT_DUE, DEPTID, NAME for a given employee and reporting period

PAYROLL REGISTER:

  • U_DEPT_CHECK_REG: Prompts between Pay End Dates and by GEMS Department ID. Gives all Confirmed or Calculated checks in the period prompted grouped by Department ID prompted. USF Report: Dept Check Register

RETRO DISTRIBUTION

  • U_RET_REQ_ADD_CHRGS_TO_ACCT: This query is to be run (and attached to the PET form) by people submitting Payroll Expenditure Transfer (PET) requests to identify the status and corresponding chartfields of the GEMS Acct-cd to which the payroll charges are to be moved
  • U_RET_REQ_DEL_CHRGS_FROM_ALL: This query is to be run (and attached to the PET form) by people submitting Payroll Expenditure Transfer (PET) requests to identify the full details of the payroll charges (earnings, deductions, and taxes) that are to be moved

Comments are closed.