PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/01/2017 6:57 AM by  John Beard
Purchase Order Cost Review Message (MA66.3) comments
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
pglibra
Supply Chain Revenue Coordinator
Private
Basic Member
(25 points)
Basic Member
Posts:11


Send Message:

--
06/29/2017 11:29 AM

    Good afternoon,

    I am trying to create a query in Access to look at closed cost messages to include the comments from "Has Comments" field.

    What table can I find the comment info?

    Thank you

    Paul

    Kat V
    Sr Supply Chain System Analyst
    South Broward Hospital District
    Veteran Member
    (1831 points)
    Veteran Member
    Posts:625


    Send Message:

    --
    07/14/2017 4:26 PM
    The comments are in one of the L_DM tables but I'm at a loss which one. I thought it was L_DMIV (Message Invoice) but I can't find a table called that.
    brupp
    Sr. Data Analyst
    UC Health
    Veteran Member
    (303 points)
    Veteran Member
    Posts:127


    Send Message:

    --
    07/21/2017 11:02 AM
    Our Crystal Report is pointed to L_HMAG.Object for the MA66.3 comments.
    pglibra
    Supply Chain Revenue Coordinator
    Private
    Basic Member
    (25 points)
    Basic Member
    Posts:11


    Send Message:

    --
    07/25/2017 9:27 AM

    Thanks for your response.

    What table can I find L_HMAG? I do not see it in MAINVMSG.

    Thank you

    Paul

    pglibra
    Supply Chain Revenue Coordinator
    Private
    Basic Member
    (25 points)
    Basic Member
    Posts:11


    Send Message:

    --
    07/25/2017 10:26 AM
    Ok...I found the L_HMAG table but unable to locate field that contains the actual text for the comments.
    R_NAME gets me the Title of the comment.
    Kat V
    Sr Supply Chain System Analyst
    South Broward Hospital District
    Veteran Member
    (1831 points)
    Veteran Member
    Posts:625


    Send Message:

    --
    07/25/2017 4:48 PM
    If HMAG is the header - DMAG should be the detail with the actual comment. (Welcome to Lawson Tables - why put it all in one spot when you can put it in three?)
    brupp
    Sr. Data Analyst
    UC Health
    Veteran Member
    (303 points)
    Veteran Member
    Posts:127


    Send Message:

    --
    07/26/2017 3:45 PM
    Here's the SQL - maybe there's something here that would help? I don't know SQL & really don't understand how comments work to be honest Good Luck!

    SELECT
    APINVOICE."BASE_DISC_AMT", APINVOICE."DISC_DATE", APINVOICE."DUE_DATE",
    MAINVDTL."ITEM", MAINVDTL."MATCH_UNIT_CST",
    MAINVMSG."AUTH_CODE", MAINVMSG."VENDOR", MAINVMSG."INVOICE", MAINVMSG."PO_NUMBER", MAINVMSG."PO_LINE_NBR", MAINVMSG."STATUS", MAINVMSG."CREATE_DATE", MAINVMSG."BUYER_CODE",
    POLINE."AGREEMENT_REF", POLINE."ENT_UNIT_CST", POLINE."MANUF_NBR",
    L_HMAG."OBJECT",
    POVAGRMTHD."PO_USER_FLD_3", POVAGRMTHD."PO_USER_FLD_5"
    FROM
    { oj (((("LSLMDB"."ls_apps"."APINVOICE" APINVOICE INNER JOIN "LSLMDB"."ls_apps"."MAINVDTL" MAINVDTL ON
    APINVOICE."COMPANY" = MAINVDTL."COMPANY" AND
    APINVOICE."VENDOR" = MAINVDTL."VENDOR" AND
    APINVOICE."INVOICE" = MAINVDTL."INVOICE" AND
    APINVOICE."SUFFIX" = MAINVDTL."SUFFIX" AND
    APINVOICE."PO_NUMBER" = MAINVDTL."PO_NUMBER" AND
    APINVOICE."PO_RELEASE" = MAINVDTL."PO_RELEASE" AND
    APINVOICE."PO_CODE" = MAINVDTL."PO_CODE")
    INNER JOIN "LSLMDB"."ls_apps"."MAINVMSG" MAINVMSG ON
    MAINVDTL."COMPANY" = MAINVMSG."COMPANY" AND
    MAINVDTL."VENDOR" = MAINVMSG."VENDOR" AND
    MAINVDTL."INVOICE" = MAINVMSG."INVOICE" AND
    MAINVDTL."SUFFIX" = MAINVMSG."SUFFIX" AND
    MAINVDTL."PO_NUMBER" = MAINVMSG."PO_NUMBER" AND
    MAINVDTL."PO_RELEASE" = MAINVMSG."PO_RELEASE" AND
    MAINVDTL."PO_CODE" = MAINVMSG."PO_CODE" AND
    MAINVDTL."LINE_NBR" = MAINVMSG."PO_LINE_NBR")
    INNER JOIN "LSLMDB"."ls_apps"."POLINE" POLINE ON
    MAINVMSG."COMPANY" = POLINE."COMPANY" AND
    MAINVMSG."PO_NUMBER" = POLINE."PO_NUMBER" AND
    MAINVMSG."PO_RELEASE" = POLINE."PO_RELEASE" AND
    MAINVMSG."PO_CODE" = POLINE."PO_CODE" AND
    MAINVMSG."PO_LINE_NBR" = POLINE."LINE_NBR" AND
    MAINVMSG."VENDOR" = POLINE."VENDOR")
    LEFT OUTER JOIN "LSLMDB"."ls_apps"."L_HMAG" L_HMAG ON
    MAINVMSG."L_INDEX" = L_HMAG."L_INDEX")
    LEFT OUTER JOIN "LSLMDB"."ls_apps"."POVAGRMTHD" POVAGRMTHD ON
    POLINE."AGREEMENT_REF" = POVAGRMTHD."VEN_AGRMT_REF"}
    WHERE
    MAINVMSG."STATUS" < 9 AND
    (MAINVMSG."AUTH_CODE" <> 'SRB' AND
    MAINVMSG."AUTH_CODE" <> 'EMA' AND
    MAINVMSG."AUTH_CODE" <> 'DLH')
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    07/28/2017 7:59 AM
    This has been a cause of consternation in the Lawson community forever. Here is a link to a paper I wrote way back on how comments are handled in Lawson:
    https://www.lawsonguru.com/Articles...on-v8.aspx
    Thanks for using the LawsonGuru.com forums!
    John
    John Beard
    Database Analyst
    Mass Water Resources Authority
    New Member
    (3 points)
    New Member
    Posts:1


    Send Message:

    --
    08/01/2017 6:57 AM
    I find that most other messages are stored in the L_Hxxx Table and sometimes in the L_Dxxx table also, but 90% or more are in the L_Hxxx tables. The field OBJECT has the actual message beginning in column 96 I believe. The first 95 characters are header info and can be dropped from your report. Good Luck. We also found out that MS-Access sometimes has trouble matching up the L-Index key fields because it doesn't appear to always be case sensitive when linking. SQL didn't have this problem.
    MAINVMSG zzzo zz I 0 08-NOV-06 121334 08-NOV-06 121334 113 95 TYPE=C,CREATE USER=coleman ,MODIFIED USER=coleman ,PER QUOTE 20000979
    MAINVMSG zzzn zz m 0 08-JAN-08 103654 08-JAN-08 103654 171 95 "TYPE=C,CREATE USER=coleman ,MODIFIED USER=coleman ,THIS INVOICE IS MATCHED TO WRONG PO , SHOULD BE 1072967
    JAY C "
    You are not authorized to post a reply.