CT_CORE_DOIT_LMS_VW
Purpose of Query
Replication of CT_DOIT_LMS_VW - The query’s design mirrors the data used to populate the Learning Management System (LMS). This query is to be used only by those agencies participating in the LMS program (ITD, DCF, DVA, DOT, OPM, DEP, DOC, SDE, DOL, DSS, DMV, FPC, DDS). Please note: the results of this query may be more current than displayed in the LMS program. If there are discrepancies between this query and the LMS, it may be due to timing of the data load into LMS.
Folder
HR
Tables
Top level
A CTW_EMPLOYEE_VW - Employee Information View
Subquery (exists)
B CTW_EMAIL_PHONE - Email Phone Rpt
C CTW_LOCATION_HR - HR Location
D CTW_POSITION - Position Information Rpt
Union level 1
E CTW_EMPLOYEE_VW - Employee Information View
F CTW_LOCATION_HR - HR Location
G CTW_POSITION - Position Information Rpt
Union level 2
H CTW_EMPLOYEE_VW - Employee Information View
Subquery (does not exist)
K CTW_EMAIL_PHONE - Email Phone Rpt
I CTW_LOCATION_HR - HR Location
J CTW_POSITION - Position Information Rpt
Note: A JOIN is an SQL command that is used to relate two or more data tables (Records in PeopleSoft) based on the use of related (key) fields from one table to the next. Once joined, data can be retrieved without repeating all of the data in every table.
Note: A Union query combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The two queries must have the same number of columns and compatible data types to unite.
Note: A subquery is a query whose results are used by another query. The main query uses the subquery’s result set as a comparison value for a selection criterion.
Prompts
DEPTID – Department Id – (Like --% wildcard enabled)
Sample Results:
Please note, due to page size limitations, sample results may be incomplete.
Criteria
A.JOB_INDICATOR - Job Indicator equal to Primary
A.EMPL_STATUS - Employee Status in list (Active, Leave, Leave with Pay and Suspended)
A.DEPTID – Department Id in list (ITD%, DCF%, DVA%, DOT%, OPM%, DEP%, DOC%, SDE%, DOL%, DSS%, DMV%, FPC% and DDS%)
exist SUBQUERY – (Business Phone number located on the CTW_EMAIL_PHONE tbl)
Union 1
E.EMPL_STATUS - Employee Status in list (Terminated, Deceased)
E.TERMINATION_DT – Termination Date greater than 1-1-2009
E.DEPTID – Department Id in list (ITD%, DCF%, DVA%, DOT%, OPM%, DEP%, DOC%, SDE%, DOL%, DSS%, DMV%, FPC% and DDS%)
H.JOB_INDICATOR - Job Indicator equal to Primary
H.EMPL_STATUS - Employee Status in list (Active, Leave, Leave with Pay and Suspended)
H.DEPTID – Department Id in list (ITD%, DCF%, DVA%, DOT%, OPM%, DEP%, DOC%, SDE%, DOL%, DSS%, DMV%, FPC% and DDS%)
does not exist SUBQUERY – (Business Phone number located on the CTW_EMAIL_PHONE tbl)
Note: Runtime prompts are included as criteria.
Fields
Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text /1 / A.DEPTID - Department / Char10 / DeptID
2 / SUBSTR(A.DEPTID, 1, 3) as DEPTID / Char10 / Department
3 / A.CT_LOCATION_DESCR - Location Description / Char30 / Location Descr
4 / A.EMPLID - EmplID / Char11 / Empl ID
5 / A.EMPL_RCD - Empl Rcd Nbr / Num3.0 / Empl Rcd#
6 / A.NAME_PREFIX - Name Prefix / Char4 / Prefix
7 / A.FIRST_NAME - First Name / Char30 / First Name
8 / A.MIDDLE_NAME - Middle Name / Char30 / Middle
9 / A.LAST_NAME - Last Name / Char30 / Last Name
10 / A.NAME_SUFFIX - Name Suffix / Char15 / Suffix
11 / C.ADDRESS1 - Address Line 1 / Char55 / Address 1
12 / REPLACE(C.ADDRESS2, ',', '') as ADDRESS2 / Char55 / Address2
13 / C.ADDRESS3 - Address Line 3 / Char55 / Address 3
14 / C.CITY - City / Char30 / City
15 / C.STATE - State / Char6 / State
16 / SUBSTR(C.POSTAL, 1, 5) AS POSTAL / Char12 / Postal
17 / A.EMAIL_ADDR - Email Address / Char70 / Email addr
18 / A.PHONE - Telephone / Char24 / Phone
19 / A.ORIG_HIRE_DT - Orig Hire / Date / Orig Hire
20 / '' / Date / Termination Date
21 / A.EMPL_STATUS - Employee Status / Char1 / N / Empl Status
22 / A.EMPL_CLASS - Employee Classification / Char3 / Empl Class
23 / A.CT_EMPCLASS_DESCR - Employee Class Description / Char30 / EE Class Descr
24 / A.JOBCODE - Job Code / Char6 / Job Code
25 / A.CT_JOBCODE_DESCR - Job Code Description / Char30 / Job Cd Descr
26 / A.BUSINESS_TITLE - Business Title / Char30 / Business Title
27 / A.FULL_PART_TIME - Full/Part Time / Char1 / N / Full/Part
28 / A.CT_SHIFT_TL - Time and Labor Shift / Char10 / T&L Shift
29 / REPLACE(A.CT_UNION_DESCR, ',', '') AS UNION_DESCR / Char30 / Union
30 / A.EEO4CODE - EEO-4 Job Category / Char1 / N / EEO-4 Cat
31 / DECODE(A.SEX, 'M', '0', DECODE(A.SEX, 'F', '1', '2')) as SEX / Char1 / SEX
32 / A.ETHNIC_GRP_CD - Ethnic Group / Char8 / Ethnic Grp
33 / A.HIGHEST_EDUC_LVL - Highest Education Level / Char2 / N / Hi Educ Lv
34 / DECODE(SUBSTR(A.DEPTID, 1, 3), 'ITD', 'DOIT', DECODE(SUBSTR(A.DEPTID, 1, 3), 'DCF', 'DCF Training Ac / Char10 / Training DEPTID
35 / DECODE(SUBSTR(A.LOCATION, 1, 3), 'DCF', SUBSTR(A.LOCATION, 1, 7), DECODE(SUBSTR(A.LOCATION, 1, 3), ' / Char10 / Training Location
36 / DECODE(A.CT_RANK, '', '', SUBSTR(A.DEPTID, 1, 3) || '2009' || A.CT_RANK) as CT_RANK2 / Char12 / CT_RANK2
37 / D.POSITION_NBR - Position Number / Char8 / Position
38 / D.DEPTID - Department / Char10 / Pos DeptID
39 / D.CT_DEPTID_CF - Chartfield Department ID / Char10 / ChrtFld Dept ID
40 / D.PROGRAM_CODE - Program Code / Char5 / Program
41 / D.LOCATION - Location Code / Char10 / Pos Location
42 / D.CT_LOCATION_DESCR - Location Description / Char30 / Pos Location Descr