PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/29/2016 10:03 AM by  JonA
Query PO Comments Fields
 20 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Freer
Contracts Analyst
St. Lukes Health System
New Member
(4 points)
New Member
Posts:2


Send Message:

--
05/19/2011 3:58 PM
    Howdy,

    Does anyone know how to qurey the comments to print on purchase orders?

    John Henley
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    Send Message:

    --
    05/20/2011 9:34 AM
    You can't query comments/attachments from the Addins.
    Thanks for using the LawsonGuru.com forums!
    John
    Vinnie
    Private
    Private
    New Member
    (4 points)
    New Member
    Posts:2


    Send Message:

    --
    05/20/2011 10:27 AM
    You can use Crystal Reports.  I would assume you could use SQL also.

    Use tables:
    PURCHORDER
    L_HPCR

    Thanks,
    Vince McNamar
    Group Health Cooperative
    mcnamar.w@ghc.org
    Kevin Hagan
    Private
    Private
    Advanced Member
    (74 points)
    Advanced Member
    Posts:30


    Send Message:

    --
    05/20/2011 11:32 AM
    L_HPCR and L_DPCR store comments for Purchase Orders. When the OBJECT field contains more than 1024 characters it will "overflow" into L_DPCR.

    Below is an example detailing a query that finds the comment for Purchase Order number 1099:

    SELECT L_INDEX, R_NAME,OBJECT
    FROM L_HPCR WHERE L_INDEX =
    (SELECT L_INDEX FROM PURCHORDER
    WHERE PO_NUMBER LIKE '%1099%')

    The OBJECT field also stores the TYPE value of the comment. Below are the values that represent the TYPE of comment:

    I = Internal
    P = Purchase Order
    R = Receiving Documents
    O = Purchase Order Header
    T = Purchase Order Trailer
    E = Copy Comments
    B = Bill To
    D = Delivery Ticket
    Q = Vendor Contact
    N = Invoice Comment
    jrbledsoe001
    Veteran Member
    (219 points)
    Veteran Member
    Posts:91


    Send Message:

    --
    05/20/2011 11:57 AM
    Hi,

    I thought comment information could be accessed by the MS Add In using the last tab to map to drill fields. Unfortunately I can't find anything in my notes to support my theory. I can't remember if you have to do a application form query and then map the appropiate field to the corresponding dirll field. I tried it on AP comments but didn't have any luck. It's possible the feature was available in earlier version of the add in. Crystal is the way to go.

    Be sure to post your solution so that we can learn from you.

    I didn't see the L_HPCR table listed the the data file text for PO system code. Here are your fields:

    SQL> describe lsfmigd.l_hpcr
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    FILENAME NOT NULL CHAR(30)
    L_INDEX NOT NULL CHAR(4)
    ATCHNBR NOT NULL CHAR(2)
    R_NAME NOT NULL CHAR(50)
    R_TYPE NOT NULL CHAR(1)
    CRTDATE NOT NULL DATE
    CRTTIME NOT NULL NUMBER(6)
    MODDATE NOT NULL DATE
    MODTIME NOT NULL NUMBER(6)
    R_SIZE NOT NULL NUMBER(6)
    HEADSIZE NOT NULL NUMBER(4)
    OBJECT NOT NULL CHAR(512)

    Anyone know where Lawson hides the documentation for the comment tables (tables starting with L_)?

    jrbledsoe001
    Veteran Member
    (219 points)
    Veteran Member
    Posts:91


    Send Message:

    --
    05/20/2011 12:00 PM
    I'm sorry the SQL describe statement got jumbled. You can get the same information on Oracle using the describe statement; describe [yourdatatables].[lawson table]
    Freer
    Contracts Analyst
    St. Lukes Health System
    New Member
    (4 points)
    New Member
    Posts:2


    Send Message:

    --
    05/23/2011 2:44 PM
    Thanks a ton for the information. I really would not have been able to find the table the comments are stored in without your help!

    I have ODBC access but am just using MS Access to query the Oracle DB. I have linked the L_DPCR and L_HPCR tables to tables POLINESRC, POLINE and PURCHORDER using the L_Index field to get the comments to print on PO's. The comments are stored in the Object field in the L_DPCR and L_HPCR tables.

    Appreciate the assistance!
    Sherry Shimek
    Private
    Private
    Advanced Member
    (94 points)
    Advanced Member
    Posts:42


    Send Message:

    --
    05/23/2011 3:03 PM

    On a related topic - does anyone have a listing showing which L_ tables are linked to which forms or at the very the system they correspond to?

    Thanks,
    Sherry Shimek
    Catholic Health Initiatives

     

    Sherry Shimek Catholic Health Initiatives Englewood CO
    Greg Moeller
    Private
    Private
    Veteran Member
    (3915 points)
    Veteran Member
    Posts:1391


    Send Message:

    --
    05/25/2011 9:39 AM
    All of the L_ tables are pretty identifiable if you know the key.

    L_H??? is the Header information for the table with the Lawson abreviation ???
    For instance: L_HEMP is the header information for the EMPLOYEE table- abreviation EMP
    L_DEMP would be the detail information for the EMPLOYEE table.

    L_HGLC and L_DGLC contain the information for the GLCONTROL table- Lawson abreviation GLC.

    etc.
    Sherry Shimek
    Private
    Private
    Advanced Member
    (94 points)
    Advanced Member
    Posts:42


    Send Message:

    --
    05/25/2011 9:54 AM
    Thank you, Greg. Can you help me determine what system the following three tables and their related header table are related to, please?
    L_DPBB
    L_DPCC
    L_DWCR
    Sherry Shimek Catholic Health Initiatives Englewood CO
    Greg Moeller
    Private
    Private
    Veteran Member
    (3915 points)
    Veteran Member
    Posts:1391


    Send Message:

    --
    05/25/2011 11:13 AM
    The first 2 tables we do not have here at Genesis Health System (and I thought we bought EVERY product ever released by Lawson ;-) But the third one (L_DWCR) is the WCCLASSOVR table belonging to the PR system code.

    You can check these yourself on your system... Log into Add-Ins. On the 'Welcome' screen select/verify 'Database Tables' option is checked. On the 'Fields' tab enter the prefix (PBB and PCC) in the 'Table Prefix Translation' box and hit .

    This should bring back the information that you are looking for.
    Greg Moeller
    Private
    Private
    Veteran Member
    (3915 points)
    Veteran Member
    Posts:1391


    Send Message:

    --
    05/25/2011 11:15 AM
    That last line after hit should say 'hit the tab key'.
    Sherry Shimek
    Private
    Private
    Advanced Member
    (94 points)
    Advanced Member
    Posts:42


    Send Message:

    --
    05/25/2011 11:41 AM
    I was able to find the other two by checking our development areas. They are as follows:

    PBBILL (PB)
    PBCLIENT (PB)

    PB is Pay to Bill that isn't implemented here.

    Thank you for your help!!
    Sherry Shimek Catholic Health Initiatives Englewood CO
    Greg Moeller
    Private
    Private
    Veteran Member
    (3915 points)
    Veteran Member
    Posts:1391


    Send Message:

    --
    05/27/2011 3:01 PM
    To answer jrbledsoe's question about where Lawson hides the doc for the L_ tables... I haven't found it... but I found a better way to generate it myself. (provided database access)

    Download SchemaSpy's jar file: http://sourceforge.net/projects/schemaspy/
    Download GraphViz: http://www.graphviz.org/Download.php
    Download SchemaSpy GUI: http://mac.softpedia.com/...a/SchemaSpyGUI.shtml
    * Not technically necessary, but very handy.

    Configure to connect to your type of DB: SQL, Oracle, etc. And generate web-pages similar to the LBI schema's that John has posted under the ERD tab of this web site. That way, when new tables get introduced, you can just generate new web-pages, and you don't have to wait for Lawson to actually document their layout.
    SchemaSpy also gives you the relationships, conditions, etc.
    John Henley
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    Send Message:

    --
    10/19/2011 4:45 PM
    Posted By Sherry on 05/23/2011 03:03 PM

    On a related topic - does anyone have a listing showing which L_ tables are linked to which forms or at the very the system they correspond to?

    Thanks,
    Sherry Shimek
    Catholic Health Initiatives

    I have a security tool/examiner that, among other things, includes the ability to quickly see tables/system codes, etc. and includes the prefix and whether or not the table has attachments.  
    https://www.danalytics.com/Products/LawsonSecurityExaminer.aspx

    Thanks for using the LawsonGuru.com forums!
    John
    JonA
    Private
    Private
    Veteran Member
    (2959 points)
    Veteran Member
    Posts:1041


    Send Message:

    --
    03/23/2016 8:15 AM
    I'm trying to use Crystal to create a better looking printed PO but when I bring in the L_DPCR and L_HPCR tables and link them to PURCHORDER if there are multiple comment entries the PO lines duplicate for each comment. Also, not all the comments show in my report. I originally tried to put the comments at the report footer but only one comment would show. Putting it at the page header or footer, if there are 4 comments and only one PO line, the PO line will be listed 4 times but since there's only one page, only one comment shows. More PO lines will yield more pages with a random comment on each page. I want all the comments regardless of how many there to show in one area similar to what you see if you print the PO from Lawson.
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    balayogesh
    Private
    Private
    Basic Member
    (33 points)
    Basic Member
    Posts:11


    Send Message:

    --
    03/23/2016 1:37 PM
    Create a sub report with "Hponumber" as parameter and pass the PO number from main report. Attach this sub report to a group by po no footer in main report.

    I spent 5 days on this (I am not a SQL expert)

    Sub Report Command

    With
    grpcmt as
    (
      select L_index, atchnbr from L_DPCR group by L_index, atchnbr having count(*) < 2
    ),
    
    PODMT AS
    (
      select lp2.L_index, lp2.atchnbr, (select lp1.object from L_DPCR lp1 where lp1.l_index = lp2.l_index and lp1.atchnbr = lp2.atchnbr and lp1.seqnbr = 00) || rtrim(xmlagg(xmlelement(e,((lp2.OBJECT)),',').extract('//text()') order by seqnbr).GetClobVal(),',') as CMT 
    FROM L_DPCR lp2 
    where lp2.seqnbr > 00
    group by lp2.L_index, lp2.atchnbr
    union all
    select gmt.L_index, gmt.atchnbr, TO_CLOB(dp.object) as CMT from L_DPCR dp left OUTER join grpcmt gmt on dp.L_index = gmt.L_index and dp.atchnbr = gmt.atchnbr  
    ),
    
    POCMT AS
    (
      SELECT PCR.COMPANY, PCR.PO_NUMBER, TRIM(SUBSTR(L_HPCR.OBJECT,L_HPCR.HEADSIZE+1,L_HPCR.R_SIZE) || POD.CMT) CMT, L_HPCR.R_NAME,
        ROW_NUMBER() OVER (PARTITION BY L_HPCR.L_INDEX ORDER BY L_HPCR.L_INDEX, L_HPCR.ATCHNBR) rn, L_HPCR.L_INDEX, L_HPCR.atchnbr
      FROM PURCHORDER PCR
      INNER JOIN L_HPCR 
      ON PCR.L_INDEX = L_HPCR.L_INDEX
      LEFT OUTER JOIN PODMT POD
      ON PCR.L_INDEX = POD.L_INDEX and
      POD.atchnbr = L_HPCR.atchnbr
    )
    select * from POCMT where PO_NUMBER = {?Hponumber} 
    JonA
    Private
    Private
    Veteran Member
    (2959 points)
    Veteran Member
    Posts:1041


    Send Message:

    --
    03/24/2016 6:37 AM
    Thanks! Would you believe the same thought of bringing the comments in with a subreport came to me this morning in the shower?
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    balayogesh
    Private
    Private
    Basic Member
    (33 points)
    Basic Member
    Posts:11


    Send Message:

    --
    03/24/2016 4:15 PM
    ha ha ha..
    John Henley
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    Send Message:

    --
    03/25/2016 1:16 PM
    Here's a tutorial article I wrote back in v8; still relevant.
    https://www.lawsonguru.com/Articles/View/tabid/346/ArticleId/549/Comment-Attachments-in-Lawson-v8.aspx
    Thanks for using the LawsonGuru.com forums!
    John
    JonA
    Private
    Private
    Veteran Member
    (2959 points)
    Veteran Member
    Posts:1041


    Send Message:

    --
    03/29/2016 10:03 AM
    Thanks John. I reviewed the document. A bit beyond my security access. However I was able to create the subreport. I ended up with a formula that concatenates L_HPCR and L_DPCR and removes the leading 95 characters in L_HPCR

    IF {L_HPCR.OBJECT} = ""
    THEN " "
    ELSE Right ({L_HPCR.OBJECT},(Length ({L_HPCR.OBJECT}) - 95)) & {L_DPCR.OBJECT}

    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    You are not authorized to post a reply.