Query PO Comments Fields

Sort:
You are not authorized to post a reply.
Author
Messages


Freer











New Member



Posts: 2




New Member



    Howdy,

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



    John Henley











    Senior Member



    Posts: 3348




    Senior Member



      You can't query comments/attachments from the Addins.
      Thanks for using the LawsonGuru.com forums!
      John


      Vinnie











      New Member



      Posts: 2




      New Member



        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











        Advanced Member



        Posts: 30




        Advanced Member



          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



          Posts: 91




          Veteran Member



            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



            Posts: 91




            Veteran Member



              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











              New Member



              Posts: 2




              New Member



                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











                Advanced Member



                Posts: 43




                Advanced Member



                  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











                  Veteran Member



                  Posts: 1498




                  Veteran Member



                    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











                    Advanced Member



                    Posts: 43




                    Advanced Member



                      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











                      Veteran Member



                      Posts: 1498




                      Veteran Member



                        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











                        Veteran Member



                        Posts: 1498




                        Veteran Member



                          That last line after hit should say 'hit the tab key'.


                          Sherry Shimek











                          Advanced Member



                          Posts: 43




                          Advanced Member



                            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











                            Veteran Member



                            Posts: 1498




                            Veteran Member



                              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











                              Senior Member



                              Posts: 3348




                              Senior Member



                                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.co...ecurityExaminer.aspx

                                Thanks for using the LawsonGuru.com forums!
                                John


                                JonA











                                Veteran Member



                                Posts: 1162




                                Veteran Member



                                  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 - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health


                                  balayogesh











                                  Basic Member



                                  Posts: 11




                                  Basic Member



                                    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











                                    Veteran Member



                                    Posts: 1162




                                    Veteran Member



                                      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 - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health


                                      balayogesh











                                      Basic Member



                                      Posts: 11




                                      Basic Member



                                        ha ha ha..


                                        John Henley











                                        Senior Member



                                        Posts: 3348




                                        Senior Member



                                          Here's a tutorial article I wrote back in v8; still relevant.
                                          https://www.lawsonguru.co...ts-in-Lawson-v8.aspx
                                          Thanks for using the LawsonGuru.com forums!
                                          John


                                          JonA











                                          Veteran Member



                                          Posts: 1162




                                          Veteran Member



                                            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 - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
                                            You are not authorized to post a reply.