Application Express Interface for EBusiness Suite Payroll and

Application Express Interface for E-Business Suite Payroll and 3 rd Party Timekeeping Systems Florida Organizations Realizing Collaborative E-Business Solutions

Who Are We? Florida Organizations Realizing Collaborative E-Business Solutions

Polk County Overview 2 Business Groups – Board of County Co. & Clerk of Courts 2000 Board employees (500+ Fire & EMS Employees) (Source 2 Employees holding positions, not paid in Oracle) 360 Clerk employees 5 Labor Bargaining Units – EMU, FBC, FCS, FFU, PEU 2400 Oracle Self Service Users External IRecruitment and ISupplier (Public) Users Florida Organizations Realizing Collaborative E-Business Solutions

E-Business Suite 12. 1. 3 Footprint Relevant: Human Resources Time and Labor General Ledger Labor Distribution Others: Assets Oracle Advanced Benefits Inventory Payables Projects Purchasing Hyperion Employee Self-Service Payroll Cash Management Receivables Grants Accounting Manager Self-Service Florida Organizations Realizing Collaborative E-Business Solutions

But First Florida Organizations Realizing Collaborative E-Business Solutions

My Drivers Linda Grimes – Systems Analyst Shri Kulkarni – Oracle Developer Sury Ricciardi – Programmer Analyst Lana Hicks – Fiscal Functional Analyst Florida Organizations Realizing Collaborative E-Business Solutions

Kronos Workforce Telestaff Florida Organizations Realizing Collaborative E-Business Solutions

Kronos Workforce Telestaff Overview Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff Overview – Before Integration • Telestaff is a scheduling, not a payroll system. It assists in scheduling and replacing employees when people call in and can’t work. • Battalion Chiefs update Telestaff information daily for Sick, Vacation, PTO, Overtime, Extra Premiums, etc. • Timekeepers run exception reports to identify changes from planned schedule and manually adjust the Oracle Time and Labor timecards.

Why Interface? Currently time is recorded in Telestaff. After generating employees’ weekly scheduled hours, exception reports are created to assist Timekeepers in manually entering time into OTL. This new automated process will eliminate a lot of data entry time for the Timekeepers as well as catch errors prior to creating OTL Timecards. Validates Sick and Vacation Accruals In short: previous method was time-intensive and error-prone

What We Faced Florida Organizations Realizing Collaborative E-Business Solutions

Terminology and Classifications Telestaff Oracle Payroll Focus: Fully staffed firehouses Focus: Pay people properly Regular Hours FTO Medical Field Training Officer WOC (Work Out of Class) Not applicable to payroll Overtime Scheduled Extra Shift Regular Hours (in excess of base) Overtime Scheduled Florida Organizations Realizing Collaborative E-Business Solutions

Shift Rotation 24 Hour Shifts Week 1 2 Hours in Pay Period Mon Tue Wed Thu Fri Sat Sun A B C A B C 120 96 3 4 C A A B B C C A 120 96 120 5 6 B C C A A B B C 96 120 FIRE (per pay period) 120 hrs => 106 Regular 14 Overtime 96 hrs => 96 Regular EMS (per pay week) 72 hrs => 40 Regular 32 Overtime 48 hrs => 40 Regular 8 Overtime

What We Did Florida Organizations Realizing Collaborative E-Business Solutions

Kronos Telestaff Integration OTL

Basic Oracle Payroll Process OTL HXC X Create Timecard Explode OT Approve or Reject X Transfer Time from OTL to BEE PAYROLL HXT PR Staff Make Final Adjustments Validate for BEE Transfer to BEE Transfer BEE to Element Entries Convert Telestaff Time Elements to OTL Elements Run Payroll APEX Middleware Costing Record Shifts Worked TELESTAFF Transfer to GL

Process Overview Scheduled job runs in Telestaff at 9: 00 AM to “finalize” data This can be run manually at any time A scheduled job runs to extract the relevant data from Telestaff to a. csv file The extracted data is loaded into staging tables The data is validated: Current employee – fetch personnel data, work schedule, position, supervisor Confirm leave time is available to employees taking sick, vacation, or PTO time Create an error and warning report EMS timekeeper reviews report, adjusts data in the staging table and notifies Battalion Chiefs of corrections to be made in Telestaff

Telestaff APEX Overview Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff APEX Security name name name Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff APEX Overview Emp ID Emp ID Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff Apex Overview name name Emp ID name Emp ID Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff APEX Summary Report Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff APEX Detail by Employee name Emp ID Emp ID name Emp ID name Emp ID name Florida Organizations Realizing Collaborative E-Business Solutions

Crosswalk Detail by Employee

Telestaff Apex Overview Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff Apex Overview Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff Apex Overview Emp ID name Warnings do not stop processing. Errors do. Florida Organizations Realizing Collaborative E-Business Solutions

Telestaff Apex Overview name Emp ID name

Create HXC Timecard hxc_timestore_deposit. create_timecard_bb ( p_start_time=> l_start_time, p_stop_time=> l_stop_time, p_resource_id=> rgetemployes. person_id, p_comment_text=> null, --'Created using API: Tele. Staff Time. Card', p_app_blocks=> l_tbl_timecard_info, p_time_building_block_id=> l_tc_bb_id ); IF l_count = 1 THEN hxc_timestore_deposit. create_day_bb ( p_day => l_start_time+i_day , --rgetshifts. work_day, p_parent_building_block_id=> l_tc_bb_id, -- returned by create_timecard_bb p_comment_text=> rgetshifts. notes, -- 'Created using API: Telestaff Day', p_app_blocks=> l_tbl_timecard_info, p_time_building_block_id=> l_day_bb_id ); END IF;

Time Store Update hxc_timestore_deposit. create_detail_bb ( p_type=> 'MEASURE', p_measure=> l_pay_hours, p_parent_building_block_id=> l_day_bb_id, p_comment_text=> rgetshifts. notes, --'Created using API: Telestaff Detail', p_app_blocks=> l_tbl_timecard_info, p_app_attributes=> l_tbl_attributes_info, p_time_building_block_id=> l_detail_bb_id ); - hxc_timestore_deposit. create_attribute ( p_building_block_id=> l_detail_bb_id, p_attribute_name=> 'Dummy Element Context', p_attribute_value=> 'ELEMENT - '||l_element_type_id, p_app_attributes=> l_tbl_attributes_info );

Time Store Update hxc_timestore_deposit. execute_deposit_process ( p_validate => FALSE, p_app_blocks => l_tbl_timecard_info, p_app_attributes => l_tbl_attributes_info, p_messages => l_tbl_messages, p_mode => 'SUBMIT', p_deposit_process => 'OTL Deposit Process', p_timecard_id => l_new_timecard_id, p_timecard_ovn => l_new_timecard_ovn );

Problems in Testing Parallel testing will initially be against the HXT tables because the time cards will come into OTL as exploded. However after go-live we needed reports to validate all the hours in Telestaff are going into OTL Revealed need to compare total hours to ensure completeness Telestaff => Middleware => Oracle Time Change problem 8: 00 am – 8: 00 am = 24 hours calculated (Telestaff) but only worked 23 hours (Spring) (In the Fall, vice versa). Needed to correct in Middleware before transferring to OTL Florida Organizations Realizing Collaborative E-Business Solutions

Problems in Parallel Testing Occasionally, the ‘Finalize’ process must be repeated over several days when timecards are due early due to holidays. After finalize, you must un-finalize and sometimes everything isn’t accurate Must find a way to ensure all hours are transferred correctly. Florida Organizations Realizing Collaborative E-Business Solutions

Creating/Updating User Tables Polk_telestaff_people – demographic info and accruals from Oracle Polk_telestaff_elements –links elements between Telestaff and Oracle Polk_telestaff_interface – stores weekly data from Telestaff Polk_telestaff_interface_week 1 – holds data from polk_telestaff_interface for first week of pay period so that Timekeepers can load week two data Polk_telestaff_int_data – intermediate table that populates info like Holiday Time not recorded in Telestaff Polk_telestaff_processed_data – final table prior to OTL update that will explode Overtime following rules setup in Oracle.

Paralleling Telestaff to Oracle OTL Paralleling is very tedious due to adjustments in Super Timekeeper. Early processing due to holiday weeks causes a lot of adjustments. Holidays are not entered in Telestaff. Populated in APEX based on work schedule. Those that flex hours that week cannot be automated and matched totally. Timecards are exploded in the APEX app and will come into OTL as exploded. You must compare APEX to the HXT Tables not HXC tables. Issues in early version of Telestaff exposed the need to upgrade Kronos. Needed reports that could cross-match hours in Telestaff to the elements we write into Oracle HXC tables.

Telestaff to Oracle OTL Discrepancies Battalion Chiefs may enter time as sick but timekeepers need to change it to vacation or lost time if they have exceeded their available leave balances. Sick may also need to be correctly reported as FMLA Sick which may not be known to the Battalion Chiefs. Incorrect Premium Hours may be corrected by timekeeper when entering into OTL Incorrect Element selections by Battalion Chiefs are manually changed by timekeepers. Florida Organizations Realizing Collaborative E-Business Solutions

Recap Telestaff to OTL Old Procedures Timekeepers produce a daily exception report from Telestaff that will list any changes in normal schedules. Example: Overtime, Sick, Vacation, PTO and Premium Hours Once a week Timekeepers pre-populate the Oracle Timecard based on the employees scheduled shift assigned. They will then make any needed changes from the daily exception reports. Due to complex 106 hour overtime rules; after overtime explodes, payroll staff had to manually move hours to Overtime Scheduled.

Recap Telestaff to OTL New Procedures Battalion Chiefs update Telestaff daily (call ins, ride-up) Each day, timekeeper extracts data from Telestaff and loads the interface tables which can be viewed in the APEX app. Timekeeper reviews various reports, informs Battalion Chiefs of corrections needed in Telestaff At the end of each week after validation and corrections, timekeeper uploads (Push to OTL button) into Oracle OTL tables, in Submitted Status. Approvers can still reject timecard to make last minute changes. Florida Organizations Realizing Collaborative E-Business Solutions

Recap of Benefits The new system eliminates the need for timekeepers to generate OTL timecards based on schedules, then make changes during the week. Timekeeper simply runs a process daily to bring in records from the Telestaff system for review. They compare the Exceptions, notify Battalion Chiefs to make any changes. The following day, in the same week, they will bring over the corrected time. There are over 300 Fire personnel and over 100 EMT/Paramedics with the 56 hour shift rotation. The remainder of employees are normal 40 hour week employees. Timekeeper effort went from 24+ person hours/week to 3 person hours/week Payroll staff time was reduced by almost 30% Almost no manual checks are needed to correct the pay.

Contact Information Linda Grimes – Systems Analyst – Linda. Grimes@polk-county. net Shri Kulkarni – Oracle Developer – Shri. Kulkarni@polk-county. net Sury Ricciardi – Programmer Analyst – Sury. Ricciardi@polk-county. net Lana Hicks – Fiscal Functional Analyst – Lana. Hicks@polk-county. net APEX in EBS SIG http: //apexsig. communities. oaug. org/ Florida Organizations Realizing Collaborative E-Business Solutions

Want more? Florida Organizations Realizing Collaborative E-Business Solutions

Why Use APEX? ■ Rapid Application Development ▪ Shorter learning curve than OAF, ADF, Java ■ Avoid customizations ■ No impact on patching and upgrading ■ Fun and Free! But check with Oracle ▪ EBS limited use database license does not allow for an additional schema ■ Fully supported by Oracle ■ Force Multiplier – users can do a lot without code changes

Why Use APEX with the E-Business Suite? “Oracle E-Business Suite delivers a wide range of functionality to handle core areas of your business processing needs. However, there are situations where you want to extend your information systems beyond the range of Oracle E-Business Suite. Many times these necessary extensions are meant to handle unique industry conventions, specific customer requirements, or perhaps to offer some other competitive edge. Sometimes these change requests are simple enough, but other times more extensive customizations are needed. In these scenarios, Oracle Application Express, also known as Oracle APEX, provides an easy way to create supplemental applications that are easily integrated with your Oracle E-Business Suite and its data. ” - Extending Oracle E-Business Suite Release 12. 1 and above using Oracle Application Express [Revision 2]

Possible Use Cases § Real time access to EBS data § Additional reporting and analysis § Simple and customized export of data § Graphical representation of EBS data § Deliver new business functionality § Custom ordering system § Searchable personnel directory § Custom data collection § § Eliminate Access databases and Excel spreadsheets to standardize data entry APEX can NOT be used to customize EBS pages or forms

Searchable personnel directory/dashboard Used with permission

Architecture - Logical E-Business Suite Database Grant Select APPS Schema CUSTOM Schema EBS APIs APEX Engine

Architecture - Physical Glassfish will no longer be supported on ORDS 17. 4

Free Workspace Good place to learn APEX fundamentals No E-Business Suite https: //apex. oracle. com/

http: //apexsig. communities. oaug. org/