The Kronos Transaction Assistant (cont)
If you have ever weeped or gnashed your teeth over the Kronos Transaction Assistant, then perhaps we can assist with at least the weeping part (see your dentist about the gnashing). There are two Kronos database tables that contain all the information that you need to know about errors that are displayed on Transaction assistant. These tables are FAILEDXMLDATA and FAILEDXMLBODY. And here’s a query that will pull all person-level data out:
select distinct a.failedxmldataid "Error ID",c.personnum "Empl ID",
c.personfullname "Employee Name",
c.employmentstatus "Kronos Status",
convert(char,c.employmentstatusdt,101) "Kronos Status Dt",
convert(char,c.companyhiredtm,101) "Kronos Hire Dt",
convert(char,b.updatedtm,120) "Error Dt/Time",b.errorcode "Err Code",
substring(b.sourcedetails,1,25) "Import Source",
rtrim(d.fieldname) "Field Name",
substring(d.internalmesgtxt,charindex('The person',d.internalmesgtxt),190) "Error Message"
from failedxmlbody a,
failedxmldata b,
vp_employeev42 c,
importresult d
where a.failedxmldataid = b.failedxmldataid
and substring(a.xmlreqbody,charindex('<PersonNumber>',a.xmlreqbody)+14,07)
= c.personnum
and charindex('<PersonNumber>',a.xmlreqbody) > 0
and a.xmlreqkey = 0
and b.importresultid = d.importresultid
and b.updatedtm >= '@@Date to Display@@' + ' ' + '**Start Time (hh:mm)**' + ':00'
and b.updatedtm <= '@@Date to Display@@' + ' ' + '**End Time (hh:mm)**' + ':59.999'
order by c.personfullname
If you have never worked with command-line SQL (this is for SQL Server – Oracle uses different date formats and string functions) this may look a little intimidating. Actually, we have a tool that fills in the blanks in the SQL (everything surrounded by ‘@@’ or ‘**’ is prompted by the program and filled in by the user at run time), and then shells out to Excel with a result set when it’s done. So we get the result in a spreadsheet (see below). And we even include the Employee Number (gasp!) in the data element list. Amazing.
With substitutions, the SQL for the 2nd and 3rd lines from the bottom might look like this, to display errors from August 28 starting at 1:00AM:
and b.updatedtm >= '2010-08-28 01:00:00'
and b.updatedtm <= '2010-08-28 23:59:59.999'
So fear not – maybe one day Kronos will display the employee number that caused the error, or even allow you to filter on it. Or let you filter (not just sort) by error message. Or let you display all the punches for a particular missing badge number all together. But that day is not yet here. In the meantime, you need not be left in the dark. The data is there. All you need to know is how to get it out.
Get the promised ROI out of your Kronos Implementation
Gain insights into how to get the most from what you have. Kronos Performance, Configuration Best Practices, Enforcing your Labor Policies, Custom Training needs, and Fit/Gap driven plans to help get you from 'here' to 'there' by requesting a free review.