- DATA VIEW NAME
- CL_CALC_BALANCE_HISTORY
- OVERVIEW
- The population of this table will be a snapshot of balances after each classified calc. This view contains balances by pay period and dates back to the June A 2008 Classified calc. There will be one row for each Person_ID/PEG combination. New balances are added to this view after every classified calculation.
- LOAD FREQUENCY
- New data will be appended every two weeks. Each new classified pay period will be added to the table after the calc. Once the pay period is added the data will remain static (i.e. updates will not be made).
- DATA CUSTODIAN
- Office of Human Resources
Catharine Derubeis - AUTHORIZATION
- You must receive approval to access data from the data custodian. For information about how to get authorized for InfoAccess data views, connect to the InfoAccess Home Page
- SPECIAL
CONSIDERATIONS - None.
- QUESTIONS
- Contact: Division of Information Technology Help Desk
Phone: 264-HELP (4357) - LAST UPDATED
- 09/2010 – New view created.
11/2011 – Custodian change c4j . - DATA FORMATS
- Primary Key Columns are identified with an asterisk(*)
- Alphanumeric Data
- VARCHAR2( N) (N) = maximum length. Field contains letters and/or numbers. Trailing blanks are eliminated.
Example: VARCHAR2 (5) could contain ‘ABC12’ or ‘ABC’ or ‘A’ - Numeric Data
- NUMBER (N) (N) = length. Field contains only numbers.
NUMBER (X,Y) = format for numbers with decimal positions.
X = total number of digits
Y = the number of digits after the decimal
Examples: NUMBER (6) represents 250012 NUMBER (6,2) represents 2500.12 - Date
- DATE – Date can be in various formats. Date functions available with your query tool can be used to format the date.
Examples: DATE (YYYYMMDD), DATE (YYDDD), DATE (DD-MM-YY)
| Column Name | Format | Description | ||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| *PERSON_ID | VARCHAR(8) | A unique number assigned to each person in the IADS HR system. | ||||||||||||||||||||||||||||||||||||||
| PERSON_NAME | VARCHAR(30) | The name currently used by a person. The format will be last name, followed by a comma and a space, then first name or initial, middle initial or name, followed by a space plus Jr. or Sr. or III if appropriate. The only special character required is a comma to end the last name. | ||||||||||||||||||||||||||||||||||||||
| PAYROLL_SECTION_CODE | VARCHAR(2) | Section containing PEG responsible for doing leave accounting. | ||||||||||||||||||||||||||||||||||||||
| PAYROLL_ENTRY_GROUP_UDDS | VARCHAR(7) | UDDS of PEG responsible for doing leave accounting. | ||||||||||||||||||||||||||||||||||||||
| PEG_UNIT | VARCHAR(1) | Unit of leave accounting UDDS (first character of Payroll_Entry_Group UDDS). | ||||||||||||||||||||||||||||||||||||||
| PEG_DIVISION | VARCHAR(2) | Division of leave accounting UDDS (2nd & 3rd character of Payroll_Entry_Group UDDS). | ||||||||||||||||||||||||||||||||||||||
| PEG_DEPARTMENT | VARCHAR(4) | Department of leave accounting UDDS (4th & 5th character of Payroll_Entry_Group UDDS). | ||||||||||||||||||||||||||||||||||||||
| PAYROLL_ENTRY_GROUP_DESCR1 | VARCHAR2(21) | First alpha descriptive name of payroll entry group assigned by payroll office. | ||||||||||||||||||||||||||||||||||||||
| PAYROLL_ENTRY_GROUP_DESCR2 | VARCHAR2(21) | Second alpha descriptive name of payroll entry group assigned by payroll office. | ||||||||||||||||||||||||||||||||||||||
| PAY_PERIOD_END_DATE | DATE | End date of pay period for which leave data was entered. | ||||||||||||||||||||||||||||||||||||||
| PAY_PERIOD_END_DATE_CCYY | VARCHAR(4) | 4-digit year of pay period for which leave data was entered. | ||||||||||||||||||||||||||||||||||||||
| PAY_PERIOD_END_DATE_MM | VARCHAR(2) | 2-digit month of pay period for which leave data was entered. | ||||||||||||||||||||||||||||||||||||||
| PAY_PERIOD_END_DATE_DD | VARCHAR(2) | 2-digit day of pay period for which leave data was entered. | ||||||||||||||||||||||||||||||||||||||
| CALC_ID | VARCHAR(9) | The unique identifier for a calculation processed in the payroll system. Consists of calendar year, payroll paid month, and sequence number. The scheduled calculations will be assigned a calculation number in advance. Special calculations will be given a calculation number in between the sequence numbers of scheduled calculations. For Example: 200908600 = Aug A Classified |
||||||||||||||||||||||||||||||||||||||
| CALC_DESCR | VARCHAR(30) | A name associated with the calculation number, such as Aug A Classified. | ||||||||||||||||||||||||||||||||||||||
| SUPERVISOR_CODE | VARCHAR2(4) | Used for reporting leave information by groups. Defined by the user within the leave accounting UDDS. | ||||||||||||||||||||||||||||||||||||||
| SENIORITY_DATE | DATE | Employment start date or adjusted continuous service date. Used to compute length of service for classified employees. | ||||||||||||||||||||||||||||||||||||||
| EMPLOYMENT_STATUS | VARCHAR(1) | Valid values are: Blank (‘ ‘) – Active T = Terminated |
||||||||||||||||||||||||||||||||||||||
| LEAVE_STATUS | VARCHAR2(1) | Current leave accounting status. Valid values are: A – Leave eligible P – On original probation R – Retire this year L – On leave without pay E – On extended original probation S – Seasonal T – Transfer employee before leave balances are entered. |
||||||||||||||||||||||||||||||||||||||
| LEAVE_STATUS_MODIFIER | VARCHAR2(1) | The leave status modifier identifies how the leave hours are to be processed. Valid values are: R – Leave processing for permanent employee. P – Leave processing for project/project employee W – Leave processing for weekend nurse employee only. |
||||||||||||||||||||||||||||||||||||||
| FLSA_STATUS | VARCHAR(1) | Exemption status from the Wages and Hour provisions of the Fair Labor Standards Act for overtime pay of time and a half or compensatory time. Valid values are: Blank (‘ ‘) E – Exempt N – Non-exempt |
||||||||||||||||||||||||||||||||||||||
| NORMAL_HOURS | NUMBER(3,1) | Number of hours normally worked in a regular biweekly pay period. Total of base hours on payroll splits related to the leave record. A change in normal hours (detected by leave maintenance) will change the vacation allocation. | ||||||||||||||||||||||||||||||||||||||
| BARGAINING_UNIT_CODE | VARCHAR(2) | The bargaining unit associated with this employee or the non-lte classified positions held by the employee. Used by the leave accounting system. Entered directly via transaction ‘ADSV’ or updated when title code entered on classified splits with transactions ‘PMAS’ or ‘PMED’.
Valid values:
|
||||||||||||||||||||||||||||||||||||||
| BARGAINING_UNIT_DESCR | VARCHAR(30) | Description of Bargaining Unit Code. | ||||||||||||||||||||||||||||||||||||||
| SABBATICAL_ELIGIBILITY | VARCHAR2(1) | Eligibility indicator to show whether or not a person has ever been eligible to accumulate sabbatical leave using an accrued amount of sick leave. Valid values:
|
||||||||||||||||||||||||||||||||||||||
| PH_CARRYOVER_IND | VARCHAR2(1) | Indicates if personal holiday hours remaining after Dec 31 should be carried over to next year. (Bargaining unit 15 only.) Valid values: Y – Carryover N or blank ( ‘ ‘) – No carryover |
||||||||||||||||||||||||||||||||||||||
| VN_CARRYOVER_IND | VARCHAR2(1) | Indicates if vacation hours remaining after Dec. 31st or June 30 should be carried over or lost. Valid values: Y – Carryover N or blank ( ‘ ‘) – No carryover. Defaults to PEG carryover indicator |
||||||||||||||||||||||||||||||||||||||
| VN_CARRYOVER | Number(5,2) | Hours of vacation carried over from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| VN_ALLOCATION | Number(5,2) | Hours of vacation allocated for current year. Computed by annual allocation program or leave maintenance, or entered via PLAM. | ||||||||||||||||||||||||||||||||||||||
| VN_EARNED_YTD | Number(5,2) | Vacation hours earned to date since January 1. Used in computing a final vacation balance for terminating employees. | ||||||||||||||||||||||||||||||||||||||
| VN_USED_YTD | Number(5,2) | Vacation hours used to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| VN_CARRYOVER_LOST_YTD | Number(5,2) | Vacation hours carried forward not used by July 1 and not requested to be retained. | ||||||||||||||||||||||||||||||||||||||
| VN_ADJUSTMENT_YTD | Number(5,2) | Adjustments to the vacation allocation for current year. These adjustments result from leave without pay(LWOP), additional paid hours for part-time employees, or transfer to sabbatical leave. This amount in added to the allocation to determine an adjusted allocation. Therefore, if the amount is greater than zero it will increase the allocation, and if the amount is less than zero, it will decrease the allocation. | ||||||||||||||||||||||||||||||||||||||
| VN_BALANCE | Number(5,2) | Current balance of vacation hours. Not available for usage if employee is on original probation or extended original probation. | ||||||||||||||||||||||||||||||||||||||
| SL_CARRYOVER | Number(7,2) | Hours of sick leave carried over from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| SL_EARNED_YTD | Number(5,2) | Hours of sick leave earned to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| SL_USED_YTD | Number(5,1) | Hours of sick leave used to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| SL_BALANCE | Number(7,2) | Sick leave hours currently available. | ||||||||||||||||||||||||||||||||||||||
| PH_CARRYOVER | Number(7,2) | Hours of personal holidays carried over from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| PH_ALLOCATION | Number(5,2) | Hours of personal holidays allocated for the year. Computed by annual allocation program or leave maintenance, or entered via PLAM. | ||||||||||||||||||||||||||||||||||||||
| PH_USED_YTD | Number(5,2) | Hours of personal holiday used to date, beginning with first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| PH_BALANCE | Number(5,2) | Current balance of personal holiday hours. Certain collective bargaining units prohibit usage while employee is on original probation or extended original probation. | ||||||||||||||||||||||||||||||||||||||
| LH_CARRYOVER | Number(5,2) | Legal holiday balance carried forward from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| LH_EARNED_YTD | Number(5,2) | Hours of legal holiday earned to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| LH_USED_YTD | Number(5,2) | Hours of legal holiday used to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| LH_BALANCE | Number(5,2) | Hours of legal holiday currently available to use, if amount greater than zero. If less than zero, hours of legal holiday used in advance. | ||||||||||||||||||||||||||||||||||||||
| CT_CARRYOVER | Number(5,2) | Hours of compensatory time which were carried over from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| CT_EARNED_STRAIGHT_YTD | Number(7,2) | Total hours of compensatory time worked which are earned at straight time during current year. | ||||||||||||||||||||||||||||||||||||||
| CT_EARNED_HOLIDAY_YTD | Number(5,2) | Total hours worked on a legal holiday where the premium is earned as compensatory time rather than paid as type of overtime. | ||||||||||||||||||||||||||||||||||||||
| CT_EARNED_TIME_HALF_YTD | Number(5,2) | Total hours of compensatory time worked which are earned at time and a half earned to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| CT_USED_YTD | Number(5,2) | Total hours of compensatory time paid or used earned to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| CT_BALANCE | Number(5,2) | Hours of compensatory time currently available. | ||||||||||||||||||||||||||||||||||||||
| SA_CARRYOVER | Number(7,2) | Hours of sabbatical leave carried forward from the last pay period ending in the previous year. | ||||||||||||||||||||||||||||||||||||||
| SA_ADDED_YTD | Number(5,1) | Hours of current year’s vacation allocation transferred to sabbatical leave. Decision for transfer or payment made in December. | ||||||||||||||||||||||||||||||||||||||
| SA_USED_YTD | Number(5,1) | Hours of sabbatical leave paid or used earned to date, beginning with the first pay period of the year. | ||||||||||||||||||||||||||||||||||||||
| SA_BALANCE | Number(7,2) | Hours of sabbatical leave currently available. | ||||||||||||||||||||||||||||||||||||||
| MILITARY_LEAVE_YTD | Number(5,2) | Hours of military leave taken to date. | ||||||||||||||||||||||||||||||||||||||
| OVERTIME_YTD | Number(7,2) | Total hours of overtime paid to date. | ||||||||||||||||||||||||||||||||||||||
| DEDUCT_LWOP_YTD | Number(7,2) | Total hours of LWOP earned to date, beginning with the first pay period of the year which caused a reduction in vacation allocation or sick leave earned. | ||||||||||||||||||||||||||||||||||||||
| NON_DEDUCT_LWOP_YTD | Number(5,2) | Total hours of LWOP to date which did not require an adjustment to vacation allocation or sick leave earnings. | ||||||||||||||||||||||||||||||||||||||
| NON_DEDUCT_LWOP_MAX | Number(5,2) | Maximum hours of LWOP which can be accumulated during year and have no effect on sick leave or vacation earnings. Awarded to eligible employees by annual allocation program or entered via PLAM. | ||||||||||||||||||||||||||||||||||||||
| EXTRACT_DATE_TIME | DATE | The date and time these data were extracted. Format : YYYYMMDD HH24:MI:SS |