SCH_CLND_DTL_VW (SQL View)	Index Back
Schedule Calendar Table The Schedule Calendar table maintains the association of Schedule Workdays to the specific date that the workday begins on.

SCH_CLND_DTL_VW

(SQL View)

Schedule Calendar Table

The Schedule Calendar table maintains the association of Schedule Workdays to the specific date that the workday begins on.

SELECT S.SETID , S.SCH_ADHOC_IND , S.SCHEDULE_ID , R.ROTATION_ID , X.THE_DATE , D.SHIFTNUM , CASE WHEN ( SELECT SH.EFFDT FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) IS NOT NULL THEN ( SELECT SH.EFFDT FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.EFFDT END , D.WRKDAY_ID , D.DAYNUM , D.SHIFT_ID , CASE WHEN ( SELECT SH.SCHED_HRS FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > 0 THEN ( SELECT SH.SCHED_HRS FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.SCHED_HRS END, CASE WHEN ( SELECT SH.DESCR FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > ' ' THEN ( SELECT SH.DESCR FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE ' ' END, CASE WHEN ( SELECT SH.END_EFFDT FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) IS NOT NULL THEN ( SELECT SH.END_EFFDT FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE S.END_EFFDT END, CASE WHEN ( SELECT SH.OFFDAY_IND FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > ' ' THEN ( SELECT SH.OFFDAY_IND FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.OFFDAY_IND END, CASE WHEN ( SELECT SH.START_TIME FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) IS NOT NULL THEN ( SELECT SH.START_TIME FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.START_TIME END, CASE WHEN ( SELECT SH.END_TIME FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) IS NOT NULL THEN ( SELECT SH.END_TIME FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.END_TIME END, CASE WHEN ( SELECT SH.END_OFFSET FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > 0 THEN ( SELECT SH.END_OFFSET FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.END_OFFSET END, CASE WHEN ( SELECT SH.STRT_STOP_DIFF FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > 0 THEN ( SELECT SH.STRT_STOP_DIFF FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.STRT_STOP_DIFF END , S.SCH_TYPE ,CASE WHEN ( SELECT SH.SYNCID FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > 0 THEN ( SELECT SH.SYNCID FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.SYNCID END, CASE WHEN ( SELECT SH.LASTUPDDTTM FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) IS NOT NULL THEN ( SELECT SH.LASTUPDDTTM FROM PS_SCH_SHIFT_TBL SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_TBL M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE D.LASTUPDDTTM END FROM PS_SCH_DEFN_TBL S , PS_SCH_DEFN_ROTATN R , PS_TL_DATES_TBL X , PS_SCH_DEFN_DTL D WHERE R.SETID = S.SETID AND R.SCH_ADHOC_IND = S.SCH_ADHOC_IND AND R.SCHEDULE_ID = S.SCHEDULE_ID AND R.EFFDT = S.EFFDT AND X.THE_DATE BETWEEN S.EFFDT AND S.END_EFFDT AND D.SETID = S.SETID AND D.SCH_ADHOC_IND = S.SCH_ADHOC_IND AND D.SCHEDULE_ID = S.SCHEDULE_ID AND D.EFFDT = S.EFFDT AND D.DAYNUM = %DateDiff(S.EFFDT, X.THE_DATE) - %Truncate((%DateDiff(S.EFFDT, X.THE_DATE)) / S.SCHEDULE_DAYS, 0) * S.SCHEDULE_DAYS + R.ROTATION_START_DAY

Related Language Record: SCH_CLND_DT_LNG

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

2 SCH_ADHOC_IND Character(1) VARCHAR2(1) NOT NULL This field is used on Schedule Definitions to indicate if a Schedule is Ad Hoc (i.e. belonging to a specific employee) or Common (used by many employees)
1=Common Schedule
2=Ad Hoc Primary Schedule
3=Ad Hoc Alternate Schedule

3 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).

4 ROTATION_ID Character(10) VARCHAR2(10) NOT NULL Rotation ID

5 DUR Date(10) DATE Date Under Report associates time being reported, scheduled or paid, to a specific date.

6 SHIFTNUM Number(2,0) SMALLINT NOT NULL Shift Sequence Number

7 EFFDT Date(10) DATE Effective Date
Default Value: %date

8 WRKDAY_ID Character(10) VARCHAR2(10) NOT NULL Workday ID

9 DAYNUM Number(3,0) SMALLINT NOT NULL Day Number

10 SHIFT_ID Character(10) VARCHAR2(10) NOT NULL Shift ID

11 SCHED_HRS Signed Number(6,2) DECIMAL(4,2) NOT NULL Scheduled Work Hours

12 DESCR Character(30) VARCHAR2(30) NOT NULL Description

13 END_EFFDT Date(10) DATE End Effdt

14 OFFDAY_IND Character(1) VARCHAR2(1) NOT NULL Off Day Indicator
N=Work Shift
Y=Off Shift

15 START_TIME Time(15) TIMESTAMP Interview Start Time

16 END_TIME Time(15) TIMESTAMP Interview End Time

17 END_OFFSET Signed Number(2,0) DECIMAL(1) NOT NULL Date Under Report Offset identifies the number of days following the start day of a shift to assign a specific punch to. If the punch lands on the same day as the start of the shift, the offset is zero. If it lands on the following day, then the offset is one and so on.

18 STRT_STOP_DIFF Number(5,2) DECIMAL(4,2) NOT NULL Start Stop Time Duration

19 SCH_TYPE Character(2) VARCHAR2(2) NOT NULL Shift Type
E=Elapsed
F=Flex
P=Punch

20 SYNCID Number(10,0) DECIMAL(10) NOT NULL The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle.

21 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
2	SCH_ADHOC_IND	Character(1)	VARCHAR2(1) NOT NULL	This field is used on Schedule Definitions to indicate if a Schedule is Ad Hoc (i.e. belonging to a specific employee) or Common (used by many employees) 1=Common Schedule 2=Ad Hoc Primary Schedule 3=Ad Hoc Alternate Schedule
3	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).
4	ROTATION_ID	Character(10)	VARCHAR2(10) NOT NULL	Rotation ID
5	DUR	Date(10)	DATE	Date Under Report associates time being reported, scheduled or paid, to a specific date.
6	SHIFTNUM	Number(2,0)	SMALLINT NOT NULL	Shift Sequence Number
7	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
8	WRKDAY_ID	Character(10)	VARCHAR2(10) NOT NULL	Workday ID
9	DAYNUM	Number(3,0)	SMALLINT NOT NULL	Day Number
10	SHIFT_ID	Character(10)	VARCHAR2(10) NOT NULL	Shift ID
11	SCHED_HRS	Signed Number(6,2)	DECIMAL(4,2) NOT NULL	Scheduled Work Hours
12	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
13	END_EFFDT	Date(10)	DATE	End Effdt
14	OFFDAY_IND	Character(1)	VARCHAR2(1) NOT NULL	Off Day Indicator N=Work Shift Y=Off Shift
15	START_TIME	Time(15)	TIMESTAMP	Interview Start Time
16	END_TIME	Time(15)	TIMESTAMP	Interview End Time
17	END_OFFSET	Signed Number(2,0)	DECIMAL(1) NOT NULL	Date Under Report Offset identifies the number of days following the start day of a shift to assign a specific punch to. If the punch lands on the same day as the start of the shift, the offset is zero. If it lands on the following day, then the offset is one and so on.
18	STRT_STOP_DIFF	Number(5,2)	DECIMAL(4,2) NOT NULL	Start Stop Time Duration
19	SCH_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Shift Type E=Elapsed F=Flex P=Punch
20	SYNCID	Number(10,0)	DECIMAL(10) NOT NULL	The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle.
21	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.