CU_BAR_HR_00003

• First Name, Not for update to AD, possible display for Directory

• Middle Name, Not for update to AD, possible display for Directory

• Last Name, Not for update to AD, possible display for Directory

• CUNYfirst identification, key to associate to AD ID/SAM

• Active/Inactive Position Status - we are only looking for those with "Active" value

• Full-time or part-time position identification
• F, P values

• Identification of position as either primary or secondary for an employee. An employee may have both primary and secondary, and could have multiple secondary (adjunct in two positions and/or departments). A person who is full time at Baruch should have the full time position as primary. A person who is part-time only at Baruch (no other CUNY position) may have the part-time position as primary. A person may have a primary position at another CUNY school which will result in any postion at Baruch (likely part time) to be a secondary. 
• This primary/secondary value will most likely not to be stored in AD but will be used to identify loading of values into specified attributes/fields for AD and Directory. 
• Job_Ind_CD values P or S, Job_Ind_LD values "Primary Job", "Secondary Job"

• Employee record number is a unique value to reflect an employee's job(s). Record number is assigned for each distinct position an employee is holding at the same time. Record number assignment for position(s) begins with "0" and goes up sequentially.
• This value will most likely not be stored in AD but may be used for position identification

• Identification of faculty and staff. 
• The JOBCODE_CD is a six-digit numeric beginning with 1-5. Codes beginning with "3" are faculty, all other codes are staff.

  • 200000 – Executive Compensation Plan (Staff), ECP Below Vice President, ECP Vice President and Above

  • 300000 – Faculty / CLT / Other Inst (Faculty), Faculty, Continuing Ed, Laboratory / Research, Non-Teaching / Administrative

  • 400000 – HEO Series (Staff), Non-Teaching / Administrative

  • 500000 – Civil Service / Classified (Staff), Competitive, Non-Competitive

LABOR_AGREEMENT

UNION_POS_LD

• Union/Labor Agreement and Membership Identification 
• LABOR_AGREEMENT (PSC, WC, BC, ECP, STE, CME, NONE). 
• UNION_POS_LD includes detail local information for WC and BC (DC37 Local 2054, Teamsters Local 237, SEIU Local 300, Executive Compenstation Plan, Classified Service Managers, etc. )

DEPT_ID_POSITION orDEPT_ID_JOB

DEPT_DESCR_POSITION or DEPT_DESCR_JOB

• Baruch Department of Employment
• Numeric value in DEPT_ID_POSITION/DEPT_ID_JOB associated to a descriptive department title in DEPT_DESCR_POSITION/DEPT_DESCR_JOB

In CJR values for both "POSITION" and "JOB" include same values, but are these always necessarily the same?

• May be used for update to AD, display in Directory
• value in CJR appears to come from Phone Information Phone Type of Work

• May be used for update to AD, display in Directory, no value in CJR 
• HCM contains Phone Type FAX

• Not for update to AD, possible display for Directory, 
• value in CJR appears to come from Email Type of Business

LOC_ID_POSITION and LOC_ID_JOB

LOC_LD_POSITION and LOC_LD_JOB

• Building Name - Building for department or employee?
• LOC_ID_POSITION and LOC_ID_JOB (BRUCH-B, BRUCH-H)
• LOC_LD_POSITION and LOC_LD_JOB (WmAnita Newman Vertical Campus, Library & Technology Building)

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?

• Street Address
• LOC_ADDR1 (55 Lexington Ave, 151 East 25th St)

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)

• Room/Office number
• LOC_ADDR2 is a value in CJR, null for all but could possibly be used for Room

Use of LOC_ADDR2 for Room/Office number - address type to be used and process for maintenance will need to be confirmed with HR

• Mail box number - used for mailing of letters/packages
• CJR didn't include Address 3 but there is an Address 3 value with address type that could be used for mail box

Use of Address 3 for Mail Box number - address type to be used and process for maintenance will need to be confirmed with HR

• HR/System/Administrative Title - official title assigned by HR, title may or may not communicate function and responsibilities of your position

• Functional or Working Title - title that clearly identifies the key job functions of your position to someone internal or external to Baruch

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

• Listing of areas of responsibility

use, storage (location), and maintenance process and policy of this value to be determined 

PS_JOB - EE

PS_EMAIL_ADDRESSES

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