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.

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

Importing Third-Party Schedules

Managing Third-Party Schedules

Rejecting Schedule Records

Field Requirements for Transmitting Schedule Data