Query PO Comments Fields - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

Freer

New Member

Posts: 2

5/19/2011 7:58 PM

Howdy,

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

John Henley

Posts: 3353

5/20/2011 1:34 PM

You can't query comments/attachments from the Addins.

Vinnie

New Member

Posts: 2

5/20/2011 2:27 PM

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

5/20/2011 3:32 PM

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

5/20/2011 3:57 PM

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

5/20/2011 4: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

New Member

Posts: 2

5/23/2011 6: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

Advanced Member

Posts: 43

5/23/2011 7: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

Greg Moeller

Veteran Member

Posts: 1498

5/25/2011 1:39 PM

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

5/25/2011 1:54 PM

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

Greg Moeller

Veteran Member

Posts: 1498

5/25/2011 3:13 PM

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

5/25/2011 3:15 PM

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

Sherry Shimek

Advanced Member

Posts: 43

5/25/2011 3:41 PM

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!!

Greg Moeller

Veteran Member

Posts: 1498

5/27/2011 7: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

Posts: 3353

10/19/2011 8: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.co...ecurityExaminer.aspx

JonA

Veteran Member

Posts: 1163

3/23/2016 12:15 PM

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.

balayogesh

Basic Member

Posts: 11

3/23/2016 5: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

Veteran Member

Posts: 1163

3/24/2016 10: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?

balayogesh

Basic Member

Posts: 11

3/24/2016 8:15 PM

ha ha ha..

John Henley

Posts: 3353

3/25/2016 5:16 PM

Here's a tutorial article I wrote back in v8; still relevant.
https://www.lawsonguru.co...ts-in-Lawson-v8.aspx

JonA

Veteran Member

Posts: 1163

3/29/2016 2:03 PM

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}