SCH_EMP_DFLT_VW

(SQL View)

SELECT S.EMPLID , S.EMPL_RCD , S.SCHEDULE_GRP , S.SCHEDULE_ID FROM PS_SCH_ASSIGN S WHERE %EffdtCheck(SCH_ASSIGN S1, S, %CurrentDateIn) AND S.USE_DFLT_WS<>'Y' UNION SELECT J.EMPLID , J.EMPL_RCD , P.SCHEDULE_GRP , P.SCHEDULE_ID FROM PS_GP_PYGRP_DTL P , PS_JOB J WHERE J.GP_PAYGROUP = P.GP_PAYGROUP AND %EffdtCheck(GP_PYGRP_DTL P1, P, %CurrentDateIn) AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT) AND EXISTS ( SELECT 'X' FROM PS_SCH_ASSIGN S WHERE S.EMPLID = J.EMPLID AND S.EMPL_RCD = J.EMPL_RCD AND %EffdtCheck(SCH_ASSIGN S1, S, %CurrentDateIn) AND S.USE_DFLT_WS='Y' ) UNION SELECT E.EMPLID , E.EMPL_RCD , W.SCHEDULE_GRP , W.SCHEDULE_ID FROM PS_TL_WRKGRP_TBL W , PS_TL_EMPL_DATA E WHERE W.WORKGROUP = E.WORKGROUP AND %EffdtCheck(TL_EMPL_DATA E1, E, %CurrentDateIn) AND %EffdtCheck(TL_WRKGRP_TBL W1, W, %CurrentDateIn) AND EXISTS ( SELECT 'X' FROM PS_SCH_ASSIGN S WHERE S.EMPLID = E.EMPLID AND S.EMPL_RCD = E.EMPL_RCD AND %EffdtCheck(SCH_ASSIGN S1, S, %CurrentDateIn) AND S.USE_DFLT_WS='Y') AND NOT EXISTS( SELECT 'X' FROM PS_GP_PYGRP_DTL P , PS_JOB J WHERE J.GP_PAYGROUP = P.GP_PAYGROUP AND %EffdtCheck(GP_PYGRP_DTL P1, P, %CurrentDateIn) AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 SCHEDULE_GRP Character(10) VARCHAR2(10) NOT NULL Scheduling Organizational Group Code

4 SCHEDULE_ID Character(16) VARCHAR2(16) NOT NULL The Schedule ID is a field used to uniquely identify a Schedule Definition or Schedule Calendar (built out Schedule Definition).

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	SCHEDULE_GRP	Character(10)	VARCHAR2(10) NOT NULL	Scheduling Organizational Group Code
4	SCHEDULE_ID	Character(16)	VARCHAR2(16) NOT NULL	The Schedule ID is a field used to uniquely identify a Schedule Definition or Schedule Calendar (built out Schedule Definition).