- DATA VIEW NAME
- PVL
- OVERVIEW
- This view contains all position vacancy listings in the new web based PVL system. There will be one row per PVL number. This view can provide PVL counts within certain date ranges. Only Major Appointment Department, one Title Code, lowest Fund and most recent PVL Status info are available.
- LOAD FREQUENCY
- Data will be refreshed nightly (Monday through Saturday).
- DATA CUSTODIAN
- Office of Human Resources
Contact: 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 InfoAccess Home Page
- SPECIAL
CONSIDERATIONS - This view can be used in conjunction with views:
UW.PVL_UDDS, UW.PVL_TITLE, and UW.PVL_STATUS_HISTORY to get the complete picture of a PVL. Join on PVL_NUMBER only. Currently, it is hard to tie this view back to the Appointment System views. We are working on better integration between the two systems.
- QUESTIONS
- Contact: Division of Information Technology Help Desk
Phone: 264-HELP (4357) - LAST UPDATED
- 07/2002 New data view created.
11/2005 Added columns Validation_Ind, Working_Title, Grad_School_Fund, Continuing_Studies_Fund, Recruitment_Program_Text, Program_Type, Program_Round, Caregiver_Law
RDI, C4j
10/2007 Data Custodian change.
07/2011 Updated for HRS – added 2 waiver reason codes, added 2 pay basis codes and changed appt_percent_maximum and appt_percent_minimum to 8,5 format.
11/2011 – Data Custodian Change
5/2015 – Updated for HRS – added 2 new fields Jobapply_ind and Internal_recruitment_ind, changed Waiver_reason_code format from Varchar2(1) to Varchar2(2) . - DATA FORMAT
- 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 |
|---|---|---|
| *PVL_NUMBER | NUMBER (6) | A unique identifier for an individual PVL. |
| MAJOR_DEPT_UDDS_CODE | VARCHAR2 (7) | A UDDS code for the hiring department that is considered the “major” department for the PVL. One, and only one, department is specified as the “major” department on each PVL. Generally, the major department assumes responsibility for creating the PVL and its attachments. |
| MAJOR_DEPT_NAME | VARCHAR2 (60) | The textual description of the Major_Dept_Udds_Code. The field is the concatenation of the long name of the department and the abbreviated name of the division with a slash separating them. |
| NUMBER_OF_DEPT | NUMBER (2) | Count of hiring departments (UDDS codes) associated with the PVL. This data is a derived. |
| APPT_TYPE_CODE | VARCHAR2 (2) | Appointment type specified on the PVL. Valid values are: AS = Academic Staff FA = Faculty LI = Limited Appointee |
| APPT_TYPE_TEXT | VARCHAR2 (30) | Textual description of the appointment_type_code. For example, the textual description of the appointment_type “AS” is “Academic Staff”. |
| VALIDATION_IND | VARCHAR2 (1) | PVL recruitment business rules are checked by the Validate_PVL_PKG, which sets this indicator. The purpose of this indicator is to give users information so they may interpret the information on the PVL with the appropriate level of confidence. Valid values are: V – Validated N – Non-validated I – Invalid |
| PVL_CURRENT_STATUS_CODE | VARCHAR2 (2) | A code that describes the status of the PVL as of the date of the data was extracted from the PVL system. Valid values are: AA = APO Approved CA = Cancelled DA = Division Approved DR = Draft EN = Entered FI = Filled HO = Hold HP = Hire Pending RE = Released RV = Revised TA = Department Approved |
| PVL_CURRENT_STATUS_TEXT | VARCHAR2 (30) | The textual description of the PVL Current Status Code. |
| PVL_CURRENT_STATUS_EFF_DATE | DATE | The date the PVL Current Status Code was associated with the PVL. |
| TITLE_CODE | VARCHAR2 (5) | The combined title_function_code (3), title_prefix_code (1), title_scope_code (1) form the five digit ‘title code’. This code uniquely identifies a title. This is the first title after sorting on title prefix code and title scope code followed by the title function code. |
| TITLE_NAME | VARCHAR2 (21) | A textual description for the title_code. |
| WORKING_TITLE | VARCHAR2 (80) | An unformatted textual title that a department may choose to use to describe the position, as an alternative to generic title based on the unclassified title code(s) associated with this position. This field is mandatory for statuses at or above Department Approved. |
| NUMBER_OF_TITLES | NUMBER (2) | Count of title codes associated with the PVL. This count is derived. |
| PVL_TYPE_CODE | VARCHAR2 (1) | This code for the type of PVL – a new, replacement, or converted (i.e., converted from classified to unclassified) position. Valid values are: C = Conversion N = New Position R = Replacement |
| PVL_TYPE_TEXT | VARCHAR2 (30) | The textual description of the pvl_type_code. For example, the textual description “New Position” describes pvl_type_code “N”. |
| SALARY_MINIMUM_AMOUNT | NUMBER (11,3) | The minimum pay that will be offered to candidates who apply for the position vacancy. |
| SALARY_MAXIMUM_AMOUNT | NUMBER (11,3) | The maximum amount the major dept would like to pay the person who fills the position. |
| PAY_BASIS_CODE | VARCHAR2 (1) | The pay basis for the position. Valid values are: A = Annual Year B = Ten Month C = Academic Year H = Hourly L = Lump. (The display of the salary base rate will differ depending on the salary basis.) S = Summer Session V = Summer Service |
| SALARY_QUALIFIER_CODE | VARCHAR2 (1) | The code represents a textual statement that is included with the salary information on position announcements. Valid values are: C = Current Salary Plus 5-10% Increase F = Fixed M = Plus UW Medical Foundation N = Negotiable P = If UW-Madison Employee – 5-10% Increase Q = Depending on Qualifications X = Extraordinary Rang |
| SALARY_QUALIFIER_TEXT | The textual description of the salary_qualifier_code. For example, “Depending on Qualifications” is the textual description salary_qualifier_code “Q”. This textual description is printed on a position announcement. | |
| APPT_PERCENT_MINIMUM | NUMBER (8.5) | The “low” end of the percent time range of the position. |
| APPT_PERCENT_MAXIMUM | NUMBER (8,5) | The “high” end of the percent time range of the position. |
| CONTINUITY_STATUS_CODE | VARCHAR2 (1) | This code indicates if the appointment may be renewed or not. Valid values are: R = Renewable Appt T = Terminal Appt |
| CONTINUITY_STATUS_TEXT | VARCHAR2 (30) | The textual description of the continuity_status_code. For example, continuity_status_text “Terminal appt” describes continuity_status_code “T”. |
| ASSURED_CONSIDERATION_DATE | DATE | The latest date an application will be accepted for a position to insure consideration. |
| POSITION_AVAILABLE_DATE | DATE | The first date the applicant can begin working in the position. |
| APPT_END_DATE | DATE | The date the appointment ends for the position. |
| POSITIONS_RECRUITED | NUMBER (2) | The maximum number of positions (appointments) that may be appointed for the PVL. |
| POSITIONS_FILLED | NUMBER (2) | The number of positions that have been filled (i.e., appointments have been created) for the PVL. The Appointment System maintains the value of this field. |
| FUND | VARCHAR2 (3) | The fund number that identifies the source of funding for the position. |
| NUMBER_OF_FUNDS | NUMBER (2) | Count of funds associated with the PVL. This is a derived field. |
| GRAD_SCHOOL_FUND | VARCHAR2 (1) | Valid values are: Y – Graduate School will be funding this position, either in whole or in part. N – Not funded. |
| CONTINUING_STUDIES_FUND | VARCHAR2 (1) | This field is sourced from the Summer_School_Funded field. Valid values are: Y – The Department of Continuing Studies is funding this position, either in whole or in part. N – Not funded. |
| RECRUITMENT_PROGRAM_TEXT | VARCHAR2 (70) | This field may contain multiple occurrences of Recruitment_Program_Text; thus, final length could be up to Varchar2(1000). Along with Program_Type and Program_Round, Recruitment_Program_Text is part of the tracking of cluster hiring practices. This is an unformatted textual item which describes a particular cluster hiring initiative of which this PVL is a part. |
| PROGRAM_TYPE | VARCHAR2 (40) | This field may contain multiple occurrences of Program_Type; thus, final length could be up to Varchar2(1000). An unformatted textual item which categorizes the kind of special recruitment program of which this PVL is a part. Possible values might refer to Madison Plan, or Strategic Hire. This field is not restricted in its content. Current valid value: C – Cluster hire. |
| PROGRAM_ROUND | VARCHAR2 (10) | This field may contain multiple occurrences of Program_Round; thus, final length could be up to Varchar2(1000). Round refers to hiring cycles. Valid values are: 1 2 3 4 5 |
| WAIVER_STATUS_CODE | VARCHAR2 (1) | The code for the status of the PVL waiver. Valid values are: A = Approved C = Cancelled D = Denied P = Pending |
| WAIVER_STATUS_TEXT | VARCHAR2 (30) | The textual description of the waiver_status_code. For example, waiver_status_text “pending” describes waiver_status_code “P”. |
| WAIVER_REASON_CODE | VARCHAR2 (2) | The code that identifies the reason for the PVL waiver. Valid values are: A = Appt for 25% or Less B = Emergency Instrct’l C = 6 Weeks or Less D = LOA Replacement E = Training Completed F = Degree Completion G = Referral Priority H = Temp Appointment (e.g. acting) I = Reemployed Annuit J = Underutilized Hire K = Sole Source L = Spousal M = FA to AS/AS to FA N = Other P = Position Conversion by PCC Q = Summer Session Appt R = Summer Services Appt |
| WAIVER_REASON_TEXT | VARCHAR2 (30) | The textual description of the waiver reason. For example, waiver_reason_text “spousal hire” describes waiver_reason_code “L” |
| NUMBER_OF_WAIVER_REASONS | NUMBER (2) | Count of number of reasons associated with the PVL waiver. This is a derived field. |
| WAIVED_PERSON_NAME | VARCHAR2 (60) | The name of the person for whom recruitment is being waived. In the format ‘Last Name, First Name’. Only the first waived person will show here. |
| WAIVED_PERSON_SSN | VARCHAR2 (9) | The Social Security Number of the person for whom recruitment is being waived. Only the first waived person will show here. |
| NUMBER_PERSONS_WAIVED | NUMBER (2) | Count of number of people waived for the PVL. This is a derived field. |
| PRIOR_INCUMBENT_PERSON_ID | VARCHAR2 (8) | The Appointment System ID number assigned to the person who’s vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_NAME | VARCHAR2 (30) | The Appointment System name of the person who’s vacated position caused this PVL to be created. In the format Last, First MI/Middle. |
| PRIOR_INCUMBENT_TITLE_CODE | VARCHAR2 (5) | The Appointment System title code of the person whose vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_TITLE_NAME | VARCHAR2 (21) | The Appointment System title name of the person whose vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_APPT_ID | VARCHAR2 (8) | The Appointment System appointment_id of the person who’s vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_MAJOR_DEPT | VARCHAR2 (11) | The Appointment System major department of the person whose vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_PAY_BASIS | VARCHAR2 (1) | The Appointment System pay basis of the person who’s vacated position caused this PVL to be created. |
| PRIOR_INCUMBENT_BASE_RATE | NUMBER (11,3) | The Appointment System base rate of the person who’s vacated position caused this PVL to be created. |
| REP_APPROVED_IND | VARCHAR2 (1) | This indicates whether or not EDRC (Equity & Diversity Resource Center) approved the REP (Recruitment Efforts Plan). This data is derived. Valid Values are: Y = Yes N = No |
| REP_APPROVED_DATE | DATE | The date the EDRC (Equity & Diversity Resource Center) approved the REP. |
| UNDER_UTILIZED_WOMEN_IND | VARCHAR2 (1) | Indicates if the title code associated with the PVL is considered underutilized by women. This data is derived. Valid Values are: Y = Yes N = No |
| UNDER_UTILIZED_MINORITY_IND | VARCHAR2 (1) | Indicates if the title code associated with the PVL is considered underutilized by minorities. This data is derived. Valid Values are: Y = Yes N = No |
| PREAPPROVED_MARKET_RANGE_IND_ | VARCHAR2 (1) | Indicates if the job duties (for the position being recruited) qualify the PVL to use (extraordinary) minimum and maximum amounts that have been pre-approved at a higher market range by UW-System. (Note: These market amounts are not the same as the Historical Title File extraordinary amounts). |
| RAR_SALARY_MAXIMUM_AMOUNT | NUMBER (11,3) | The maximum salary amount specified on the RAR (Request for Authorization to Recruit). |
| AUTHORIZED_MAXIMUM_AMOUNT | NUMBER (11,3) | The maximum salary amount the major department would like to pay the person who fills the position. |
| REQ_TO_EXCEED_SALARY_MAX_IND | VARCHAR2 (1) | Indicates if a new market minimum and maximum range is requested for the PVL title and full/partial UDDS. In such a case, the creator of the PVL must attach market data to the PVL so it can be reviewed by UW-System to see if the title code, minimum and maximum can be added to the pre-approved higher market range stored in the PVL system. |
| INTERNET_POSTING_IND | VARCHAR2 (1) | Indicates if the vacancy is to be posted on the Internet when in PVL status “Released” or “Revised”. Note: Waived PVL’s are not posted on the Internet. Values are “Y” or “N”. |
| POSITION_CONVERSION_CODE | VARCHAR2 (1) | The code that indicates the status of converting the position from classified to academic staff. UW System Administration is authorized to make that conversion, and a PVL may not be released until such conversion has been authorized. Only APO is allowed to update this field. Valid values are: N = Not Applicable (default) P = Pending A = Approved R = Rejected |
| POSITION_CONVERSION_TEXT | VARCHAR2 (30) | A textual description of the Position_Conversion_Code. For example, Position_Conversion_Text “Pending” describes Position_Conversion_Code “P”. |
| PQ_REQUIRED_IND | VARCHAR2 (1) | Indicates if a position questionnaire (PQ) is required to be attached to the PVL. PQ’s are only used for some Program Manager and Director title codes. Valid values are: Y = Yes N = No. |
| CAREGIVER_LAW | VARCHAR2 (1) | Valid values are: Y – This position has responsibilities which require background checks under the Caregiver Law. N – This position does not have responsibilities which require background checks under the Caregiver Law. |
| PRIMARY_CONTACT_PERSON_NAME | VARCHAR2 (61) | The name of the person who may be contacted about the PVL. In the format ‘Last Name, First Name’. |
| PVL_DEGREE | VARCHAR2 (2000) | An unformatted textual description of the educational degree specialization(s) required or preferred for the position. |
| PVL_EXPERIENCE | VARCHAR2 (2000) | An unformatted textual description of the minimum number of years of experience and type of relevant work experience requested for the position. |
| PVL_PRINCIPLE_DUTIES | VARCHAR2 (2000) | An unformatted textual description of the principle duties to be performed by the person in the position. |
| JOBAPPLY_IND | VARCHAR2 (1) | If set to Yes, the data will appear in the JobApply system and allow users to set up Applicant Tracking in JobApply. If set to No then will not allow applicant recruiting via JobApply system.. |
| INTERNAL_RECRUITMENT_IND | VARCHAR2 (1)) | If set to Yes, then the Position Listing will only appear on the Internal Recruitment web site. That is, only employees working at UW Madison may apply to the Listing. If set to No, then the Listing is open to all applicants whether employed by UW Madison or not employed at UW Madison. |
| EXTRACT_DATE_TIME | DATE | The date and time these data were extracted from the source system. |