Importing Third-Party Schedules
Time and Labor has the ability to accept schedules created outside of the system, either from a third-party workforce scheduling system or another source. The data should be mapped to the SCH_EXT_TBL, which is the record behind the Scheduled Time Sync and Fullsync messages. The SCH_ADHOC_DTL and SCH_MNG_SCH_TBL records serve as the final repository for this schedule override information. Process these schedules in Time Administration by setting up rules that run against this scheduled information. Reported time can be created from this scheduled time, and payable time or forecasted payable time can also be created from this scheduled time.
When importing third-party schedule information, populate the Sched Source field with a value of I so that the system recognizes the data as an import. This ensures the correct precedence processing.
This diagram shows how Time and Labor accepts external scheduling information
Time and Labor will create a common staging record structure (SCH_EXT_STG) so that it can subscribe information from various sources (LM, and Workforce Scheduling). The data subscribed is stored in the staging table. A validation application engine process (SCH_EXTVALID) is invoked to process all the data in the staging table before loading the final data into the application tables. After the validation process, the staging table will be deleted.
The staging and the application tables have exactly the same fields and key fields. Additional fields such as PROCESS_INSTANCE, AUDIT_ACTION are also included in the staging table for AE processing. Depending on the source, validated data will be inserted into the appropriate tables. For Workforce Scheduling information, the data will be inserted into SCH_ADHOC_DTL table, which carries the detailed scheduling information. It will also be inserted into the SCH_MNG_SCH_TBL for summarization purposes. .
If the SCH_EXTVALID validation process runs and finds rows of scheduled time from a workforce scheduling system that either do not pass validations, or are missing key values or are duplicate rows, the SCHEDULED_TIME_ERROR batch message gets triggered. This message publishes these rows back to the scheduling system with a REJECTED_REASON code defined in the SCH_EXT_REJ table. The SCHEDULED_TIME_ERROR message is based on the SCH_EXT_REJ record structure. The data which does not pass validations will first be staged in the SCH_EXT_REJ_STG table. Once the message has been published, the system clears the staging table, and the rejected rows will ultimately be stored in the SCH_EXT_LOG table. The fields validated are EmplID, Empl Rcd and SCHED_SOURCE. In order to use the SCHEDULED_TIME_ERROR message, its batch publish rule must be activated
Any other information such as Training, and so on, will be inserted into SCH_EXT_DTL table. Additionally, the AUDIT_SCH_TBL is an audit trail to track all transactions that are added, changed or deleted successfully into SCH_ADHOC_DTL table. A new field has been introduced SCH_CATEGORY to identify the category of the schedule. The category can be training information, absence, work, or other. The same category can come from different sources. For instance, absence is a category that can come from Workforce Scheduling or Absence systems. In the current application, ELM does not send to Time and Labor this schedule category field due to the new field has just been introduced. Therefore, Time and Labor will derive the data coming from ELM being categorized under "training data."
Note: Scheduled Time Fullsync messaging is used to transmit data from third-party scheduling systems to Time and Labor. The Scheduled Time Sync message for integration with third-party scheduling systems is not used.
Both the Scheduled Time Fullsync and Scheduled Time Sync messages are used for integration with Enterprise Learning Management.
The Scheduled Time Fullsync message never deletes what exists in the SCH_EXT_TBL or the corresponding application tables. For that reason, the Fullsync message should be run once for integration with ELM, to bring over the training information, such as activity and session information. For use with a third–party scheduling system, the Scheduled Time Fullsync should send over new scheduling information. However, if the user would like to change the schedule for a day that has already been published from a third–party scheduling system, they would need to send that same data over with the DELETE_IND equal to Y for that data and then send over the data they want added for that day with the DELETE_IND equal to N.
Also, for sending punch time schedule information, if the third–party scheduling system is transmitting data with START_TIME and END_TIME on one row of data, when it gets converted to two rows in the application table, with an In and an Out punch for the rows, with the appropriate times updated in the PUNCH_DTTM field.
Scheduled Time Sync messaging can also trigger a notification to the manager that an external schedule event has changed, if you setup the notifications for External Schedule Event on the Time and Labor Installation page. For example, an employee has enrolled in training, or the training session was cancelled, or the employee dropped the training. Both the Scheduled Time Fullsync and the Scheduled Time Sync messages insert into the AUDIT_SCH_TBL to track schedule audit changes for external scheduling events.
Note: The main record that is used to map data to is SCH_EXT_TBL. This is the record used for Scheduled Time Fullsync and Scheduled Time Sync messages.
The ELM flag on the General Installation page, needs to be checked, in order for the user to view ELM training data icons in Manage Schedules, Monthly Schedules and Calendars.
Note: Scheduling does not administer workforce scheduling, as it does not run rules to balance resources and constraints.
The following table describes the fields for SCH_EXT_TBL for the Scheduled Time Fullsync and Scheduled Time Sync messages:
Note: The fields marked as "not validated" are not part of the validation application engine, the system accepts the values as they are sent.
|
Field Name |
Type |
Validation Process |
Description |
|---|---|---|---|
|
EMPLID |
Char 11 |
EmplID must exist in HR database. Also, the person must have an active row in Job record. |
Employee ID |
|
EMPL_RCD |
Nbr 3 |
EmplID must have valid employee record. |
Employee Record Number |
|
SCHED_SOURCE |
Char 2 |
Source must be from a valid source defined. |
Source AB – Absence LM – Learning Management GP – Global Payroll OT – Other TR – HR Training Administration WF – Workforce Scheduling |
|
SEQ_NO |
Nbr 2 |
Number to define each shift per day. For multiple shifts, must use the next number in sequence to specify a different shift. If there is one row per scheduled punch, then each row will have the same number for each shift. Example: All rows for Shift 1 will have "1", all rows for Shift 2 will have "2". |
Sequence Number for multiple shifts (WFS only) |
|
SEQNUM |
Nbr 3 |
Each number should be unique for each shift. If there is one row per scheduled punch, each row SEQNUM must be unique and in order. If more than one shift per day, the numbering must start over again. |
Sequence Number |
|
SCH_CATEGORY |
Char 2 |
Field must have value when getting inserted into the application table. |
Schedule Category AB: Absence MT: Meeting OT: Other TR: Training WK: Work |
|
PUNCH_TYPE |
Char 2 |
Punch type must be from a valid punch type defined depending on the source. Not applicable for LM. |
Type of Punch 0 – Elapsed 1 – In 2 – Out 3 – Meal 4 – Break 5 - Transfer |
|
DUR |
Date 10 |
No validation on this field. |
Workday Start Date For WFS - For Punch Shifts, DUR is the Date part of PUNCH_DTTM for the first IN Punch of a Punch Shift. For Elapsed Shifts, DUR should be coming in on the WFS message. PUNCH_DTTM would not be used. For LM – DUR is equal to Session Date |
|
SCH_PRIM_ALT_IND |
Char 1 |
No validation on this field. |
Default to "P" (Primary), Values are "P" for Primary and "A" for Alternate. |
|
PUNCH_DTTM |
DtTm 26 |
No validation on this field. |
Date From WFS - PUNCH_DTTM for the first IN Punch of a Punch Shift. |
|
SCHEDULE_GRP |
Char |
No validation on this field. |
Schedule Group |
|
SETID |
Char 5 |
No validation on this field. |
Set ID |
|
WRKDAY_ID |
Char 10 |
No validation on this field. |
Workday ID |
|
OFFDAY_IND |
Char 1 |
No validation on this field. |
Off Day Indicator Y: Yes N: No |
|
SHIFT_ID |
Char 10 |
No validation on this field. |
Shift ID |
|
DELETE_IND |
Char 1 |
No validation on this field. |
Delete Schedule Detail Y: Yes N: No |
|
TL_TR_SBR |
SubRecord |
||
|
TRC |
Char 5 |
No validation on this field. |
Time Reporting Code |
|
TL_QUANTITY |
Sign 12.6 |
No validation on this field. |
Quantity |
|
CURRENCY_CD |
Char 3 |
No validation on this field. |
Currency Code |
|
COUNTRY |
Char 3 |
No validation on this field. |
Country |
|
STATE |
Char 6 |
No validation on this field. |
State |
|
LOCALITY |
Char 10 |
No validation on this field. |
Locality |
|
COMP_RATECD |
Char 6 |
No validation on this field. |
Comp Rate Code |
|
BILLABLE_IND |
Char 1 |
No validation on this field. |
Billable Indicator |
|
OVERRIDE_RATE |
Sign 12.6 |
No validation on this field. |
Override Rate |
|
TL_TASK_SBR |
SubRecord |
||
|
COMPANY |
Char 3 |
No validation on this field. |
Company |
|
BUSINESS_UNIT |
Char 5 |
No validation on this field. |
Business Unit |
|
SETID_LOCATION |
Char 5 |
No validation on this field. |
Location SetID |
|
LOCATION |
Char 10 |
No validation on this field. |
Location Code |
|
SETID_DEPT |
Char 5 |
No validation on this field. |
Department SetID |
|
DEPTID |
Char 10 |
No validation on this field. |
Department |
|
SETID_JOBCODE |
Char 5 |
No validation on this field. |
Jobcode SetID |
|
JOBCODE |
Char 6 |
No validation on this field. |
Jobcode |
|
POSITION_NBR |
Char 8 |
No validation on this field. |
Position Number |
|
PRODUCT |
Char 6 |
No validation on this field. |
Product |
|
CUSTOMER |
Char 10 |
No validation on this field. |
Customer |
|
ACCT_CD |
Char 25 |
No validation on this field. |
Account Code |
|
BUSINESS_UNIT_PC |
Char 5 |
No validation on this field. |
PC Business Unit |
|
BUSINESS_UNIT_PF |
Char 5 |
No validation on this field. |
Perf Business Unit |
|
PROJECT_ID |
Char 15 |
No validation on this field. |
Project/Grant |
|
SETID_ACTIVITY |
Char 5 |
No validation on this field. |
Activity SetID |
|
ACTIVITY_ID |
Char 15 |
No validation on this field. |
Activity ID |
|
RESOURCE_TYPE |
Char 5 |
No validation on this field. |
Resource Type |
|
SETID_RESOURCE |
Char 5 |
No validation on this field. |
Resource SetID |
|
RESOURCE_CATEGORY |
Char 5 |
No validation on this field. |
Resource Category |
|
RESOURCE_SUB_CAT |
Char 5 |
No validation on this field. |
Resource Sub category |
|
TASK |
Char 5 |
No validation on this field. |
Task |
|
USER_FIELD_1 |
Char 10 |
No validation on this field. |
User Field 1 |
|
USER_FIELD_2 |
Char 10 |
No validation on this field. |
User Field 2 |
|
USER_FIELD_3 |
Char 15 |
No validation on this field. |
User Field 3 |
|
USER_FIELD_4 |
Char 20 |
No validation on this field. |
User Field 4 |
|
USER_FIELD_5 |
Char 20 |
No validation on this field. |
User Field 5 |
|
RULE_ELEMENT_1 |
Char 10 |
No validation on this field. |
Rule Element 1 |
|
RULE_ELEMENT_2 |
Char 10 |
No validation on this field. |
Rule Element 2 |
|
RULE_ELEMENT_3 |
Char 10 |
No validation on this field. |
Rule Element 3 |
|
RULE_ELEMENT_4 |
Char 10 |
No validation on this field. |
Rule Element 4 |
|
RULE_ELEMENT_5 |
Char 10 |
No validation on this field. |
Rule Element 5 |
|
SCH_CONFIG_IND1 |
Char 1 |
No validation on this field. |
Schedule Config Indicator 1 |
|
SCH_CONFIG_IND2 |
Char 1 |
No validation on this field. |
Schedule Config Indicator 2 |
|
SCH_CONFIG_IND3 |
Char 1 |
No validation on this field. |
Schedule Config Indicator 3 |
|
SCH_CONFIG_IND4 |
Char 1 |
No validation on this field. |
Schedule Config Indicator 4 |
|
SCH_EXT_FLD1 |
Char 10 |
No validation is done as different sources can send different values. |
Key Field 1 used from Source. ELM - Session ID |
|
SCH_EXT_FLD2 |
Char 10 |
No validation is done as different sources can send different values. |
Additional key Field 2 from Sources |
|
SCH_EXT_FLD3 |
Char 10 |
No validation is done as different sources can send different values. |
Additional key Field 3 from Sources |
|
SCH_EXT_ACT_STATUS |
Char 1 |
No validation is done. LM sends this value. |
Schedule Activity Status |
|
SCH_EXT_ACT_TYPE |
Char 3 |
No validation is done. LM sends this value. |
Schedule Activity Type |
|
ACTIVITY_NAME |
Char 200 |
No validation is done. LM sends this value. |
Description of activity |
|
SCH_EXT_EVENT_TYPE |
Char 4 |
No validation is done. LM sends this value. |
Schedule Event Type |
|
START_DATE |
Date 10 |
Start Date of the Schedule. |
LM: Start Date of Session WFS: Determine the day of the shift |
|
END_DATE |
Date 10 |
End Date of the Schedule |
LM: End Date of Session WFS: End Date of the Schedule |
|
START_TIME |
Time 15 |
No validation done. |
LM: Start Time of Session WFS: Convert to IN Punch |
|
END_TIME |
Time 15 |
No validation done. |
LM: End Time of Session WFS: Convert to OUT Punch Time |
|
TIMEZONE |
Char 9 |
No validation done. Time Zone to be populated for scheduled times. Use this field to convert the time to the base time zone. |
Time Zone |
|
FACILTY_DESCR |
Char 50 |
No validation done. |
Facility Description |
|
ADDRESS1 |
Char 55 |
No validation is done. LM sends this value. |
Address1 |
|
ADDRESS2 |
Char 55 |
No validation is done. LM sends this value. |
Address2 |
|
ADDRESS3 |
Char 55 |
No validation is done. LM sends this value. |
Address3 |
|
ADDRESS4 |
Char 55 |
No validation is done. LM sends this value. |
Address4 |
|
CITY |
Char 30 |
No validation is done. LM sends this value. |
City |
|
NUM1 |
Char 6 |
No validation is done. LM sends this value. |
Number 1 |
|
NUM2 |
Char 4 |
No validation is done. LM sends this value. |
Number 2 |
|
HOUSE_TYPE |
Char 2 |
No validation is done. LM sends this value. |
Type of House AB: House Boat Reference WW: House Trailer Reference |
|
ADDR_FIELD1 |
Char 2 |
No validation is done. LM sends this value. |
Address Field 1 |
|
ADDR_FIELD2 |
Char 4 |
No validation is done. LM sends this value. |
Address Field 2 |
|
ADDR_FIELD3 |
Char 4 |
No validation is done. LM sends this value. |
Address Field 3 |
|
COUNTY |
Char 30 |
No validation is done. LM sends this value. |
County |
|
POSTAL |
Char 12 |
No validation is done. LM sends this value. |
Postal Code |
|
GEO_CODE |
Char 11 |
No validation is done. LM sends this value. |
Geographical Code |
|
IN_CITY_LIMIT |
Char 1 |
No validation is done. LM sends this value. |
In City Limit |
|
ORIGINATORID |
Char 30 |
No validation done. |
Original operator ID who creates the record |
|
DATETIME_CREATED |
DtTm 26 |
No validation is done. LM sends this value. |
Date and Time first created |
|
OPRID_LAST_UPDT |
Char 30 |
No validation is done. LM sends this value. |
Last person who updates the record |
|
DATETIME_UPDATED |
DtTm 26 |
No validation is done. LM sends this value. |
Date and Time last updated |
This section presents tips for addressing:
Rejected schedule records.
Field requirements for transmitting schedule data.
Rejecting Schedule Records
SCH_EXT_LOG is the table containing rows of all rejected scheduled time that did not make it into the PeopleSoft Application tables SCH_EXT_REJ_STG is the staging table for rejected rows that would get sent back to a third party scheduling system.
For both the SCH_EXT_LOG and SCH_EXT_REJ_STG tables, there is a SCH_REJECT_REASON field which explains why the scheduled time is rejected.
SCH_REJECT_REASON codes :
|
Code |
Description |
|---|---|
|
01 |
Invalid Employee and Employee Record Number |
|
02 |
Invalid Schedule Source |
|
03 |
Invalid Schedule Category |
|
04 |
Invalid Punch Type |
|
05 |
Invalid Activity |
|
06 |
Invalid Activity Type |
|
07 |
Invalid Event |
|
08 |
Invalid Time Zone |
|
09 |
Transaction already exists |
|
10 |
Cannot add, transaction exists |
|
11 |
Cannot change or delete, transaction exists |
Field Requirements for Transmitting Schedule Data
These are tips for working with some field requirements for transmitting schedule data:
Multiple Shifts
To send multiple shifts per day, ensure the fields SEQ_NO and SEQNUM are correctly populated.
SEQ_NO
Should be populated with the same number for the same shift for the day.
SEQNUM
Should be populated with an incremented number for each row for the same shift for the day, and then start over again if another shift exists on the same day.
Multiple Punch Rows for Punch Shift
For a punch schedule, if sending PUNCH_TYPE and PUNCH_DTTM fields, do not need to populate START_DATE, END_DATE, START_TIME and END_TIME
One row for Punch Shift
To send a summarize row for punch schedule for the day, the fields START_DATE, END_DATE, START_TIME and END_TIME need to be populated, and the system will then split the time into an IN and OUT punch into SCH_ADHOC_DTL table.
Total punch scheduled hours will be summarized based on the Schedule Total option (INCL_ML_BRK_FLG) on the Configuration tab of the TL Installation component or the Schedule Settings page on the System Settings of Global Payroll and Absence Management. The inclusion of meal and break punches to determine total scheduled hours are the options for this configuration.
DUR
DUR should always be populated with the date, whether sending Elapsed or Punch Schedule
TL_QUANTITY
Should be populated if sending Elapsed Scheduled Hours.
The following table illustrates field format examples
|
Field |
Format |
Example |
|---|---|---|
|
START_TIME |
HH:MM:SS.ssssss |
07:00:00.000000 |
|
END_TIME |
HH:MM:SS.ssssss |
13:30:00.000000 |
|
START_DATE |
YYYY-MM-DD |
2004-06-11 |
|
END_DATE |
YYYY-MM-DD |
2004-06-11 |