Background
We are interested in using CUNYfirst HCM data in our Active Directory to support the creation and maintenance of segmented distribution lists and possible our Online Directory. We began identification of employees and employee data through review of the Current Jobs Report (CJR). We are now looking to establish a query to select active employees from HCM along with the employee attributes required to support the project.
Below are the field/attribute names from the CJR which we are interested in querying. We want to select these field values for any employee who has an active position status at the time the query is run. In the CJR an active position status is represented as "HR_STATUS" equal to "Active". The query results will also include indication of a position as a primary or secondary position (Job_Ind_CD value of P/S)
See Documentation about this project and details about data storage in BCTC-Network Services space
For this query are eliminating the Contact information and specific address information (LOC_ADDR1,2, etc) as these fields don't currently provide reliable data. We are also eliminating the Title information as there is not currently a plan for use (use of JOBCODE_CD for "Faculty" or "Staff" identification is sufficient).
Field Name in CJR | Value Description and Function |
---|---|
FIRST_NM |
|
MIDDLE_NM |
|
LAST_NM |
|
EMPLID |
|
HR_STATUS |
|
Full/Part_Time |
|
Job_Ind_CD or Job_Ind_LD |
|
Empl_RCD |
|
JOBCODE_CD |
|
UNION_POS_LD |
|
DEPT_ID_POSITION orDEPT_ID_JOB DEPT_DESCR_POSITION or DEPT_DESCR_JOB |
In CJR values for both "POSITION" and "JOB" include same values, but are these always necessarily the same? |
Contact Information | |
Work_Phone |
|
No fax value in CJR |
|
WORK_EMAIL |
|
Location/Address Information | |
LOC_ID_POSITION and LOC_ID_JOB LOC_LD_POSITION and LOC_LD_JOB |
In CJR values for both "POSITION" and "JOB" include same values, but are these always necessarily the same? Are the values for these fields in the CJR associated to the Employee's Current Address type value? |
LOC_ADDR1 |
Use of LOC_ADDR1 for Street address - address type to be used and process for maintenance will need to be confirmed with HR |
LOC_ADDR2 LOCATION_DESCR (not sure where this label is from - not in CJR) |
Use of LOC_ADDR2 for Room/Office number - address type to be used and process for maintenance will need to be confirmed with HR |
(Address 3) |
Use of Address 3 for Mail Box number - address type to be used and process for maintenance will need to be confirmed with HR |
Title Information | |
DESCR, Pos_LD, orCU_CONTRACT_TITLE |
|
? |
use, storage (location), and maintenance process and policy of this value to be determined Description of functional/working title use and policy from WVU HR |
? |
use, storage (location), and maintenance process and policy of this value to be determined |
Query Details
Records Used
include any details about choice of records
PS_JOB
PS_PERSON_NAME
PS_DEPT_TBL
PS_EMAIL_ADDRESSES
...
Expressions
details of any expressions used
Criteria
- HR_STATUS = Active
- FULL_PART_TIME = F, P ( Full/Part Time)
- LABOR_AGREEMENT in list (‘PSC’,WC’,’BC’,’ECP’,’STE’,’CME’,’NONE’)
- JOB_INDICATOR in list (P,S) – Primary & Secondary
- EMPL_STATUS in list (A, L, P, S) – Active, Leave of Absence, Leave With Pay and Suspended
- E_ADDR_TYPE (Email Type) = BUSN (Business)
- PREF_PHONE_FLAG either is null or ‘Y’.
SQL
Query SQL: | |
---|---|
SELECT B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.EMPLID, A.HR_STATUS, A.FULL_PART_TIME, A.JOB_INDICATOR, A.EMPL_RCD, A.JOBCODE, H.DESCR, A.LABOR_AGREEMENT, A.UNION_POS, A.UNION_CD, I.DESCR, A.DEPTID, C.DESCR, D.EMAIL_ADDR, E.PHONE, E.PHONE_TYPE, A.LOCATION, G.DESCR, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),C.SETID,C.DEPTID,TO_CHAR(C.EFFDT,'YYYY-MM-DD'),H.SETID,H.JOBCODE,TO_CHAR(H.EFFDT,'YYYY-MM-DD'),I.UNION_CD,TO_CHAR(I.EFFDT,'YYYY-MM-DD'),G.SETID,G.LOCATION,TO_CHAR(G.EFFDT,'YYYY-MM-DD') FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSON_NAME B, PS_DEPT_TBL C, (SELECT TD.EMAIL_ADDR, TD.E_ADDR_TYPE, TD.EMPLID FROM PS_EMAIL_ADDRESSES TD,PS_PERALL_SEC_QRY D1 WHERE TD.EMPLID = D1.EMPLID AND D1.OPRID = '23420211' ) D, (SELECT TE.PHONE, TE.PHONE_TYPE, TE.PREF_PHONE_FLAG, TE.EMPLID FROM PS_PERSONAL_PHONE TE,PS_PERALL_SEC_QRY E1 WHERE TE.EMPLID = E1.EMPLID AND E1.OPRID = '23420211' ) E, PS_PERSONAL_DATA F, PS_PERALL_SEC_QRY F1, PS_JOBCODE_TBL H, PS_UNION_TBL I, PS_LOCATION_TBL G, PS_SET_CNTRL_REC G2 WHERE G.LOCATION = A.LOCATION AND G2.SETCNTRLVALUE = A.BUSINESS_UNIT AND G2.RECNAME = 'LOCATION_TBL' AND G2.SETID = G.SETID AND A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.OPRID = '23420211' AND F.EMPLID = F1.EMPLID AND F1.OPRID = '23420211' AND ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.HR_STATUS = 'A' AND A.FULL_PART_TIME IN ('F','P') AND A.LABOR_AGREEMENT IN ('PSC','WC','BC','ECP','STE','CME','NONE') AND C.DEPTID = A.DEPTID AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.DEPTID = C_ED.DEPTID AND C_ED.EFFDT <= SYSDATE) AND C.SETID = 'BAR01' AND B.EMPLID = D.EMPLID(+) AND D.E_ADDR_TYPE = 'BUSN' AND B.EMPLID = E.EMPLID(+) AND A.JOB_INDICATOR IN ('P','S') AND B.EMPLID = F.EMPLID AND H.JOBCODE = A.JOBCODE AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_JOBCODE_TBL H_ED WHERE H.SETID = H_ED.SETID AND H.JOBCODE = H_ED.JOBCODE AND H_ED.EFFDT <= SYSDATE) AND I.UNION_CD = A.UNION_CD AND I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_UNION_TBL I_ED WHERE I.UNION_CD = I_ED.UNION_CD AND I_ED.EFFDT <= SYSDATE) AND A.EMPL_STATUS IN ('A','L','P','S') AND ( E.PREF_PHONE_FLAG IS NULL OR E.PREF_PHONE_FLAG = 'Y') AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_LOCATION_TBL G_ED WHERE G.SETID = G_ED.SETID AND G.LOCATION = G_ED.LOCATION AND G_ED.EFFDT <= A.EFFDT) ) ORDER BY 4, 7, 8 |