SCH_CLND_SHF_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_SHF_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 , D.SEQNUM , D.PUNCH_TIME , D.TIMEZONE , D.PUNCH_TYPE , D.STRT_STOP_DIFF , D.DUR_OFFSET , D.SCH_CONFIG_IND1 , D.SCH_CONFIG_IND2 , D.SCH_CONFIG_IND3 , D.SCH_CONFIG_IND4 , S.TIME_RPTG_TMPLT , S.TASKGROUP , D.TRC , D.CURRENCY_CD , D.COUNTRY , D.STATE , D.LOCALITY , D.COMP_RATECD , D.BILLABLE_IND , D.OVERRIDE_RATE , D.OVERRIDE_RSN_CD , D.RULE_ELEMENT_1 , D.RULE_ELEMENT_2 , D.RULE_ELEMENT_3 , D.RULE_ELEMENT_4 , D.RULE_ELEMENT_5 , D.TASK_PROFILE_ID , D.TL_COMMENTS , D.COMPANY , D.BUSINESS_UNIT , D.SETID_LOCATION , D.LOCATION , D.SETID_DEPT , D.DEPTID , D.SETID_JOBCODE , D.JOBCODE , D.POSITION_NBR , D.PRODUCT , D.CUSTOMER , D.ACCT_CD , D.BUSINESS_UNIT_PC , D.BUSINESS_UNIT_PF , D.PROJECT_ID , D.SETID_ACTIVITY , D.ACTIVITY_ID , D.RESOURCE_TYPE , D.SETID_RESOURCE , D.RESOURCE_CATEGORY , D.RESOURCE_SUB_CAT , D.TASK , D.USER_FIELD_1 , D.USER_FIELD_2 , D.USER_FIELD_3 , D.USER_FIELD_4 , D.USER_FIELD_5 FROM PS_SCH_DEFN_TBL S , PS_SCH_DEFN_ROTATN R , PS_TL_DATES_TBL X , PS_SCH_DEFN_SHFT 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) + R.ROTATION_START_DAY - 1 - %Truncate((%DateDiff(S.EFFDT, X.THE_DATE) + R.ROTATION_START_DAY - 1) / S.SCHEDULE_DAYS, 0) * S.SCHEDULE_DAYS + 1

# 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 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number

8 PUNCH_TIME Time(15) TIMESTAMP punch time

9 TIMEZONE Character(9) VARCHAR2(9) NOT NULL Time Zone

10 PUNCH_TYPE Character(2) VARCHAR2(2) NOT NULL Global Punch Types
0=Elapsed
1=In
2=Out
3=Meal
4=Break
5=Transfer
Prompt Table: SCH_PUNCH_VW

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

12 DUR_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.

13 SCH_CONFIG_IND1 Character(1) VARCHAR2(1) NOT NULL Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields.
Y/N Table Edit
Default Value: N

14 SCH_CONFIG_IND2 Character(1) VARCHAR2(1) NOT NULL Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields.
Y/N Table Edit
Default Value: N

15 SCH_CONFIG_IND3 Character(1) VARCHAR2(1) NOT NULL Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields.
Y/N Table Edit
Default Value: N

16 SCH_CONFIG_IND4 Character(1) VARCHAR2(1) NOT NULL Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields.
Y/N Table Edit
Default Value: N

17 TIME_RPTG_TMPLT Character(10) VARCHAR2(10) NOT NULL Time Reporting Template ID

18 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup

19 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code used to report time in Time and Labor
Prompt Table: TL_HRS_TRC_VW

20 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Prompt Table: CURRENCY_CD_TBL

21 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

22 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

23 LOCALITY Character(10) VARCHAR2(10) NOT NULL Locality
Prompt Table: LOCAL_TAX_TBL

24 COMP_RATECD Character(6) VARCHAR2(6) NOT NULL Comp Rate Code
Prompt Table: COMP_RT_REG_VW

25 BILLABLE_IND Character(1) VARCHAR2(1) NOT NULL Billable Indicator
N=No
Y=Yes
Y/N Table Edit
Default Value: N

26 OVERRIDE_RATE Signed Number(20,6) DECIMAL(18,6) NOT NULL Override Rate.

27 OVERRIDE_RSN_CD Character(5) VARCHAR2(5) NOT NULL Override Reason Code
Prompt Table: TL_OVRD_RSN_TBL

28 RULE_ELEMENT_1 Character(10) VARCHAR2(10) NOT NULL Rule Element 1
Prompt Table: TL_RULE_ELEMNT1

29 RULE_ELEMENT_2 Character(10) VARCHAR2(10) NOT NULL Rule Element 2
Prompt Table: TL_RULE_ELEMNT2

30 RULE_ELEMENT_3 Character(10) VARCHAR2(10) NOT NULL Rule Element 3
Prompt Table: TL_RULE_ELEMNT3

31 RULE_ELEMENT_4 Character(10) VARCHAR2(10) NOT NULL Rule Element 4
Prompt Table: TL_RULE_ELEMNT4

32 RULE_ELEMENT_5 Character(10) VARCHAR2(10) NOT NULL Rule Element 5
Prompt Table: TL_RULE_ELEMNT5

33 TASK_PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Task Profile ID
Prompt Table: TL_TSKGRP_PRF_V

34 TL_COMMENTS Character(254) VARCHAR2(254) NOT NULL Comments

35 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

36 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: TL_BUS_UNIT_VW

37 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

38 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: TL_LOCATION_VW

39 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

40 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: TL_DEPT_TBL_VW

41 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

42 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: TL_JOBCODE_VW

43 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: POSITION_DATA

44 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product
Prompt Table: TL_PRODUCT_TBL

45 CUSTOMER Character(10) VARCHAR2(10) NOT NULL Customer
Prompt Table: TL_CUSTOMER

46 ACCT_CD Character(25) VARCHAR2(25) NOT NULL Combination Code
Prompt Table: TL_ACCTCD_BU_VW

47 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL Project Costing Business Unit
Prompt Table: BUS_UNIT_PRJ_VW

48 BUSINESS_UNIT_PF Character(5) VARCHAR2(5) NOT NULL Perf Meas Business Unit
Prompt Table: BUS_UNIT_TBL_PF

49 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project/Grant
Prompt Table: %EDIT_PROJECT

50 SETID_ACTIVITY Character(5) VARCHAR2(5) NOT NULL Activity Set ID

51 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
Prompt Table: %EDIT_ACTIVITY

52 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Project Costing Resource Type
Prompt Table: TL_PROJ_RTYPE_V

53 SETID_RESOURCE Character(5) VARCHAR2(5) NOT NULL Resource Set ID

54 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Project Costing Resource Category
Prompt Table: %EDIT_RES_CAT

55 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Project Costing Resource Sub Category
Prompt Table: %EDIT_RES_SUB

56 TASK Character(5) VARCHAR2(5) NOT NULL Task
Prompt Table: TL_TASK

57 USER_FIELD_1 Character(10) VARCHAR2(10) NOT NULL User Field 1
Prompt Table: TL_USER_FIELD_1

58 USER_FIELD_2 Character(10) VARCHAR2(10) NOT NULL User Field 2
Prompt Table: TL_USER_FIELD_2

59 USER_FIELD_3 Character(15) VARCHAR2(15) NOT NULL User Field 3
Prompt Table: TL_USER_FIELD_3

60 USER_FIELD_4 Character(20) VARCHAR2(20) NOT NULL User Field 4
Prompt Table: TL_USER_FIELD_4

61 USER_FIELD_5 Character(20) VARCHAR2(20) NOT NULL User Field 5
Prompt Table: TL_USER_FIELD_5

#	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	SEQNUM	Number(3,0)	SMALLINT NOT NULL	Sequence Number
8	PUNCH_TIME	Time(15)	TIMESTAMP	punch time
9	TIMEZONE	Character(9)	VARCHAR2(9) NOT NULL	Time Zone
10	PUNCH_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Global Punch Types 0=Elapsed 1=In 2=Out 3=Meal 4=Break 5=Transfer Prompt Table: SCH_PUNCH_VW
11	STRT_STOP_DIFF	Number(5,2)	DECIMAL(4,2) NOT NULL	Start Stop Time Duration
12	DUR_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.
13	SCH_CONFIG_IND1	Character(1)	VARCHAR2(1) NOT NULL	Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields. Y/N Table Edit Default Value: N
14	SCH_CONFIG_IND2	Character(1)	VARCHAR2(1) NOT NULL	Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields. Y/N Table Edit Default Value: N
15	SCH_CONFIG_IND3	Character(1)	VARCHAR2(1) NOT NULL	Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields. Y/N Table Edit Default Value: N
16	SCH_CONFIG_IND4	Character(1)	VARCHAR2(1) NOT NULL	Schedule Configuration Indicator is used to identify whether or not a specific punch (shift segment) contributes to a Schedule Configuration total field. There are four indicators to support four total fields. Y/N Table Edit Default Value: N
17	TIME_RPTG_TMPLT	Character(10)	VARCHAR2(10) NOT NULL	Time Reporting Template ID
18	TASKGROUP	Character(10)	VARCHAR2(10) NOT NULL	Taskgroup
19	TRC	Character(5)	VARCHAR2(5) NOT NULL	Time Reporting Code used to report time in Time and Labor Prompt Table: TL_HRS_TRC_VW
20	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Prompt Table: CURRENCY_CD_TBL
21	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
22	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
23	LOCALITY	Character(10)	VARCHAR2(10) NOT NULL	Locality Prompt Table: LOCAL_TAX_TBL
24	COMP_RATECD	Character(6)	VARCHAR2(6) NOT NULL	Comp Rate Code Prompt Table: COMP_RT_REG_VW
25	BILLABLE_IND	Character(1)	VARCHAR2(1) NOT NULL	Billable Indicator N=No Y=Yes Y/N Table Edit Default Value: N
26	OVERRIDE_RATE	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Override Rate.
27	OVERRIDE_RSN_CD	Character(5)	VARCHAR2(5) NOT NULL	Override Reason Code Prompt Table: TL_OVRD_RSN_TBL
28	RULE_ELEMENT_1	Character(10)	VARCHAR2(10) NOT NULL	Rule Element 1 Prompt Table: TL_RULE_ELEMNT1
29	RULE_ELEMENT_2	Character(10)	VARCHAR2(10) NOT NULL	Rule Element 2 Prompt Table: TL_RULE_ELEMNT2
30	RULE_ELEMENT_3	Character(10)	VARCHAR2(10) NOT NULL	Rule Element 3 Prompt Table: TL_RULE_ELEMNT3
31	RULE_ELEMENT_4	Character(10)	VARCHAR2(10) NOT NULL	Rule Element 4 Prompt Table: TL_RULE_ELEMNT4
32	RULE_ELEMENT_5	Character(10)	VARCHAR2(10) NOT NULL	Rule Element 5 Prompt Table: TL_RULE_ELEMNT5
33	TASK_PROFILE_ID	Character(10)	VARCHAR2(10) NOT NULL	Task Profile ID Prompt Table: TL_TSKGRP_PRF_V
34	TL_COMMENTS	Character(254)	VARCHAR2(254) NOT NULL	Comments
35	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
36	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: TL_BUS_UNIT_VW
37	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
38	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: TL_LOCATION_VW
39	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
40	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: TL_DEPT_TBL_VW
41	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
42	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: TL_JOBCODE_VW
43	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: POSITION_DATA
44	PRODUCT	Character(6)	VARCHAR2(6) NOT NULL	Product Prompt Table: TL_PRODUCT_TBL
45	CUSTOMER	Character(10)	VARCHAR2(10) NOT NULL	Customer Prompt Table: TL_CUSTOMER
46	ACCT_CD	Character(25)	VARCHAR2(25) NOT NULL	Combination Code Prompt Table: TL_ACCTCD_BU_VW
47	BUSINESS_UNIT_PC	Character(5)	VARCHAR2(5) NOT NULL	Project Costing Business Unit Prompt Table: BUS_UNIT_PRJ_VW
48	BUSINESS_UNIT_PF	Character(5)	VARCHAR2(5) NOT NULL	Perf Meas Business Unit Prompt Table: BUS_UNIT_TBL_PF
49	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project/Grant Prompt Table: %EDIT_PROJECT
50	SETID_ACTIVITY	Character(5)	VARCHAR2(5) NOT NULL	Activity Set ID
51	ACTIVITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Activity ID Prompt Table: %EDIT_ACTIVITY
52	RESOURCE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Project Costing Resource Type Prompt Table: TL_PROJ_RTYPE_V
53	SETID_RESOURCE	Character(5)	VARCHAR2(5) NOT NULL	Resource Set ID
54	RESOURCE_CATEGORY	Character(5)	VARCHAR2(5) NOT NULL	Project Costing Resource Category Prompt Table: %EDIT_RES_CAT
55	RESOURCE_SUB_CAT	Character(5)	VARCHAR2(5) NOT NULL	Project Costing Resource Sub Category Prompt Table: %EDIT_RES_SUB
56	TASK	Character(5)	VARCHAR2(5) NOT NULL	Task Prompt Table: TL_TASK
57	USER_FIELD_1	Character(10)	VARCHAR2(10) NOT NULL	User Field 1 Prompt Table: TL_USER_FIELD_1
58	USER_FIELD_2	Character(10)	VARCHAR2(10) NOT NULL	User Field 2 Prompt Table: TL_USER_FIELD_2
59	USER_FIELD_3	Character(15)	VARCHAR2(15) NOT NULL	User Field 3 Prompt Table: TL_USER_FIELD_3
60	USER_FIELD_4	Character(20)	VARCHAR2(20) NOT NULL	User Field 4 Prompt Table: TL_USER_FIELD_4
61	USER_FIELD_5	Character(20)	VARCHAR2(20) NOT NULL	User Field 5 Prompt Table: TL_USER_FIELD_5