PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/06/2017 9:28 PM by  Richard Lyons
Full Audit Log
 15 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Richard Lyons
Programmer/Analyst
Invisible
New Member
(7 points)
New Member
Posts:3


Send Message:

--
01/07/2017 7:53 PM

    For my current client, I have been requested to extract employee termination history to show why the employee was terminated.  I can easily do this in S3, but I am not sure where to look in the Landmark database.  Can anyone point me in the right direction?  In S3, the reason code I need was stored in the PERSACTHST table.  I am hoping there is an easy way to extract the data I need out of the Audit Log.

    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/09/2017 10:00 AM
    Hi Richard,

    That would be awesome, but unfortunately they didn't design the system to make it easy to get history. The auditlog is really intended to be exactly that - and audit log of what was changed by whom. Also, it is stored as xml in a CLOB field, which pretty much sucks.

    Probably your best bet is to try the EmployeeTurnover table. There is more to this than just terminations, but you should be able to to filter it down to what you need.

    Good Luck,
    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Richard Lyons
    Programmer/Analyst
    Invisible
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    01/11/2017 11:36 AM

    Kelly,

    Thanks for the information.  I will look into the EmployeeTurnover table.  Regarding the CLOB field, can you tell me what table this is stored in?

    Really confused why Infor would make it hard to get to this most basic of information.

    Richard

    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/11/2017 12:18 PM
    Hi Richard.

    I've had the same thought many times - it makes sense if you think of it as an audit table rather than an "hr history" table. Unfortunately, they didn't really build complete HR History tables, so the audit log is what we've got other than my final comment below.

    The location of the AuditLog xml field depends on whether your application was installed with the "split LOB" option - meaning that the Large Object (LOB) fields are separated from the other fields, which improves some maintenance performance (dbrebuilds, etc). If you do NOT have the split LOB option, then the auditlog will be in the business class table as "AuditLog". If you query the business class table and do NOT see that field, then you do have a split LOB setup.

    If you DO have split LOB, then the LOB fields (including Audit Log) will be in a tag table that is named S${prefix}, where {prefix} is the table prefix for the business class table. For example, for the Employee business class the prefix is "EMPL", so the LOB table is S$EMPL. These tables don't have employee number in them, you will have to join this to EMPLOYEE where S$EMPL.UniqueID = EMPLOYEE.UniqueID to see who it really belongs to. The prefix for WorkAssignment is EMPWA and the join would be S$EMPWA.UniqueID = WorkAssignment.UniqueID.

    By the way, there are also effective dated "snapshot" tables for the business classes as well, and they use the S_{prefix} naming (i.e. S_EMPL and S_EMPWA) and are joined the same way. These do show the business class field values by effective date, which is very handy. However, the DO NOT include the reason code or action reason, which really limits their usefulness. That's why the Turnover tables may be better for you.

    I hope this is helpful. Good Luck!
    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    steve finger
    ICS - Senior Consultant
    Infor
    Advanced Member
    (94 points)
    Advanced Member
    Posts:32


    Send Message:

    --
    01/11/2017 2:20 PM
    Name UserField_Storage
    Stamp 1441088000
    ClassicName USER-FUSFS
    SqlName UserField_Storage
    Prefix USFS
    don't know if this helps or hurts....but for what you paid.....a bargain!

    Module la
    RpgName USERFIELD_STORAGE
    Translatable false
    splitLobStorage false
    landmarkTextSearch false
    Space msf
    DBType MSF2008
    DBName msfdb
    Schema ltmtest
    SqlMapping landmark
    Charset ISO-8859-1
    TableSpace DATA
    IndexSpace INDX
    LobSpace DATA
    Field 1 Alpha 200 1 UserField_Storage.ClassName
    Field 2 UniqueID 36 1 UserField_Storage.UniqueID
    Field 3 Alpha 200 1 UserField_Storage.FieldName
    Field 4 Numeric 5 1 Type
    Field 5 Numeric 6 1 Size
    Field 6 Numeric 2 1 Decsize
    Field 7 Alpha 2000 1 Alpha
    Field 8 BinaryObject 1 1 Blob
    Field 9 Text 1 1 Clob
    Set 0 SymbolicKey
    Primary True
    SetField 0 UserField_Storage.ClassName
    SetField 1 UserField_Storage.UniqueID
    SetField 2 UserField_Storage.FieldName
    Relation 0 UserField_Storage
    RelatesTo UserField_Storage.SymbolicKey(= UserField_Storage)
    Type OneToOne
    DeleteRule DeleteRestrict
    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/11/2017 2:48 PM
    Hi Steve Finger,

    I'm confused about your post. Userfield_Storage is not really related to the AuditLog discussion, and that particular table doesn't have related S$ and S_ tables (at least on our system). Is there something specific you wanted to mention about this table in regards to the AuditLog discussion?

    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/11/2017 3:01 PM
    Hi Steve Finger,

    Now that I looked closer at your post, it appears that maybe you were demonstrating a utility that lists the info for a table. Is that true? If so, I'd love to know what it is or where you got that info dump.

    Thanks
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    steve finger
    ICS - Senior Consultant
    Infor
    Advanced Member
    (94 points)
    Advanced Member
    Posts:32


    Send Message:

    --
    01/11/2017 3:33 PM
    i was breezing thru the LTM "biz class" descriptions...this was the only occurrence of "CLOB" out of 261,000 lines of description.... so..i threw it out there. the question was "where is it". i thought maybe that was the answer.

    the mysteries of the configuration console are immense. being an old S3 codger who is learning LMK as best as an old dog can...when you add a custom field to a form/"biz class", I THINK that what happens is that it is added to a special table in LMK and attached to the appropriate thingy. THis may all be a fairy tale....i don't really know, but that's what it looks like. that being said, note that i have no idea what a CLOB is or where it comes from - custom field or "regular data item".
    as in infor employee, we have access to many secrets in the world. that being said, i don't know if this is a "internal only" utility...but i can ask. you may have to be signed on to the LMK server to access the utility that generates this listing....which would leavea out the cloud folks
    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/11/2017 4:01 PM
    Ahhh - I see. "CLOB" is an advanced data type in SQL DBs. It just stands for "Character Large Object". It appears different ways in different DB types - in DB2 the field type is "DBCLOB". However, this is different from what the application designer probably calls the field type. When doing queries and lookups, I rely heavily on SQL rather than application queries, so my reference is coming from a SQL perspective. The way it looks in Config Console is completely different and often doesn't really seem to relate to what happens on the DB.

    I do have command-line access to our LM servers, so I'd love to try the util if you can share it.

    This is a bit off-topic, but since you brought it up... custom userfield definitions (related bus class, name, type, size, etc) are stored in the USERFIELD table and the data values (record-specific values) are stored in the USERFIELD_STORAGE table. These tables then have a relationship to every other table in the system in order to relate the userfield to the relevant business class. The application joins the two tables and displays the custom userfields as if they are actually part of the "main" business class, but they are really in a tag table.

    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Kwane McNeal
    Private
    Private
    Veteran Member
    (1257 points)
    Veteran Member
    Posts:419


    Send Message:

    --
    01/11/2017 4:04 PM
    The tool he used is $LAENVDIR/bin/displaydict.
    steve finger
    ICS - Senior Consultant
    Infor
    Advanced Member
    (94 points)
    Advanced Member
    Posts:32


    Send Message:

    --
    01/11/2017 4:20 PM
    kwane...you old dog.

    yes...my boss says: the utility is in the manual and no reason not to say that's what i used. like most lawon utilities, just typing in the command by itself spawns the syntax

    it is located in bin where all the rest of the utliities live....
    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/11/2017 4:29 PM
    Ahhh - very helpful! I wish I would have had that several years ago. I use dbdoc all the time and always wished there was something similar...

    Thanks much!
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Kyle Jorgensen
    Programmer/Analyst
    WoodmenLife
    Veteran Member
    (314 points)
    Veteran Member
    Posts:116


    Send Message:

    --
    01/13/2017 10:50 AM

    How can I determine which "S$" table is associated with what other table?

    For example, if I know the name of a Landmark table how do I determine its prefix?

    Conversely, if I know the prefix (from the S$ table), how do I determine its related table?

    Woozy
    Private
    Private
    Veteran Member
    (3478 points)
    Veteran Member
    Posts:704


    Send Message:

    --
    01/13/2017 11:11 AM
    It's all based on the primary table prefix.  There really isn't an easy way to go from the S$ table backwards to the primary table.  

    There are several ways to find the primary table prefix:
    - from the command line:  displaydict prodline primarytablename
    - If you have config console access, select a configuration under the primary business class and then click the "view base LPL" link for the business class
    - If you have access to the source folder, look under $LASRCDIR/{prodline}/modules/{module}/bl/{primarybusinessclass}.busclass
    - In SQL catalog, look at the indexes for the primary table.  They will all start with the table prefix.  For example, under Employee there are "EMPLBYUNIQUEID" and "EMPLBYNAME", etc.

     

    There is likely an easier way, but these are the ways I have found.

    I hope this helps.

    Kelly

    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Kyle Jorgensen
    Programmer/Analyst
    WoodmenLife
    Veteran Member
    (314 points)
    Veteran Member
    Posts:116


    Send Message:

    --
    01/13/2017 11:30 AM
    If the indexes all start with the prefix, I should be able to use system tables in SQLServer to work my way backwards to the table the index is assigned to! Thanks!!
    Richard Lyons
    Programmer/Analyst
    Invisible
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    03/06/2017 9:28 PM
    I would like to thank all of the people that contributed to this thread. Unfortunately, at my client, none of this seemed to help. We wound up finding a table called WORKASSIGNMENTEXPORT. It had the data we were looking for, so this is what we have used to retrieve the termination reason code.
    You are not authorized to post a reply.