SETIDSCH_ADHOC_IND1=Common Schedule
2=Ad Hoc Primary Schedule
3=Ad Hoc Alternate Schedule
SCHEDULE_IDROTATION_IDDURSHIFTNUMEFFDTDefault Value: %date
LANGUAGE_CDSCH_CLND_DT_LNG(SQL View) |
Index Back |
|---|---|
Schedule Calendar TableThe 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, CASE WHEN ( SELECT SH.LANGUAGE_CD FROM PS_SCH_SHIFT_LANG SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_LANG M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) > ' ' THEN ( SELECT SH.LANGUAGE_CD FROM PS_SCH_SHIFT_LANG SH WHERE SH.SETID = D.SETID AND SH.SHIFT_ID = D.SHIFT_ID AND SH.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_SCH_SHIFT_LANG M WHERE M.SETID = SH.SETID AND M.SHIFT_ID = SH.SHIFT_ID AND M.EFFDT <= D.EFFDT)) ELSE ' ' 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 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 |
| # | 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 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |