PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 2/29/2016 2:45 PM by  John Henley
L_HACV and L_DACV environment tables
 6 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Tom_gbmc
Sr Systems Analyst
Private
New Member
(7 points)
New Member
Posts:3


Send Message:

--
2/26/2016 1:45 PM
    We have a request from a user to modify one of our crystal reports, reporting on activities, to include the comments from AC10. Infor told me that this information is stored in L_HACV and L_DACV and that these are environment tables and not accessed in the same way as a 'typical' database table. Has anyone had any experience using these tables and is there a reasonable way to be able to access them as well as the other AC tables in one report?

    Thanks
    ALB
    Systems Analyst
    Hospital
    Veteran Member
    (253 points)
    Veteran Member
    Posts:123


    Send Message:

    --
    2/26/2016 2:23 PM
    I have accessed the comments on APINVOICE. On ACACTIVITY, there is an index with the fields L_ATACV_SS_SW which is Y if there are comments and N if there are no comments. The next field is L_INDEX (4 characters) which is part of the key to L_HACV and L_DACV. The other part of the key is FILENAME which would be ACACTIVITY in your case. If you have multiple comments, there is a field called ATCHNBR (2 characters). R_SIZE is the length of the total comments in all tables. The actual comments are in a field called OBJECT (512 characters). The first 95 or so characters (whatever the value of HEADSIZE is) stores information about the comment. My experience has been that HEADSIZE has always been 95, so I will assume 95 characters the rest of this explanation. Adjust accordingly. From characters 96-512 are the actual comments. If someone puts in a comment longer than 512-96 characters, it overflows to L_DACV. L_HACV and L_DACV have similar indexes (FILENAME, L_INDEX, and ATCHNBR). OBJECT on L_DACV is purely comments and is 1024 characters. There is a SEQNBR field which is used so that is there are more than 1024 characters of comments to be stored, it goes into the next L_DACV record. All tables with comments have L_Hxxx and L_Dxxx tables where xxx is the 3 character prefix of the table. I look at FILEDEF in the GEN database when I run across a table or prefix I do not recognize. If you use COBOL, there are APIs to pull the data from the comments tables. I believe the data is sliced up by characters not at the end of a word. Let's say your comment is 2,000 characters long and HEADSIZE is 95. The first 417 characters of the comment are stored in characters 96-512 of OBJECT in L_HACV. Characters 418-1441 of the comment are stored in OBJECT the first SEQNBR of L_DACV while the rest will be in the 2nd SEQNBR.
    Tom_gbmc
    Sr Systems Analyst
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    2/26/2016 3:45 PM
    Hi Angela, that was a very detailed summary of how to accomplish that task. Unfortunately our Lawson is hosted and I don't even have access to a command line much less the ability to write a cobol program. All of our 'homegrown' programs are in Crystal and at this point I can't easily see how to accomplish this with Crystal. I do appreciate your help though.

    Thanks
    Tom
    Greg Moeller
    Private
    Private
    Veteran Member
    (4115 points)
    Veteran Member
    Posts:1467


    Send Message:

    --
    2/26/2016 4:22 PM
    If all your programs are in Crystal, continue to use Crystal. You just need to know how to link the tables together.
    As for the character counts, you can use combinations of the right and left functions in Crystal to extract the data that you need.
    Tom_gbmc
    Sr Systems Analyst
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    2/26/2016 5:10 PM
    Thanks Greg. I guess I can try that but my immediate problem is that when I use Crystal or access, those tables are not available to me. I have sent a 'ticket' to the company that hosts our Lawson to see if I can get access to them. As a relatively new crystal reports user (I am a long time cobol programmer) I think I will stumble through getting the links working but I can give it a shot if I have access to those table.

    Thanks again
    Tom
    Deron
    Private
    Private
    Advanced Member
    (67 points)
    Advanced Member
    Posts:25


    Send Message:

    --
    2/26/2016 5:41 PM
    Nice summary Angela. I would add that you could use a list aggregation function in your SQL statement to "summarize" the OBJECT values. If you're an Oracle user, this link should help. <http://www.techonthenet.c...unctions/listagg.php>
    John Henley
    Private
    Private
    Senior Member
    (9887 points)
    Senior Member
    Posts:3313


    Send Message:

    --
    2/29/2016 2:45 PM
    Thanks for using the LawsonGuru.com forums!
    John
    You are not authorized to post a reply.