Lawson tables to query for report

I am hoping that someone has created a report with addin queries or using something else to obtain the following criteria:

Company

Account Unit with description

Account with description

Vendor number and name

Invoice

Invoice Amt

PO number

Posting Date

Fiscal period and Fiscal year

I have been working different scenarios and have not been successful. I need this information for 2 calendar years. Any information will be appreciated. Thanks in advance.

Find more posts tagged with

Infor Lawson Financials Group - Discussion

Comments

unknown

Achievable via Crystal Reports. Do you want to select based on GL posting dates?

unknown

i've done it in lawson addins, but also with a sql query.... let me know if i can assist.

unknown

Here is a Sql query for something similar.

SELECT PUR.PO_NUMBER

,convert(VARCHAR(10),PUR.PO_DATE, 101) as 'PO_Date'

,POL.LINE_NBR

,CASE

WHEN POL.ITEM_TYPE = 'N' THEN 'Non-Stock'

WHEN POL.ITEM_TYPE = 'X' THEN 'Special'

else ' '

END as 'Item_Type'

,POL.ITEM

,POL.VEN_ITEM

,POL.DESCRIPTION

,POL.QUANTITY

,POL.REC_QTY

,(POL.QUANTITY - POL.REC_QTY) as 'Remaining'

,PUR.VENDOR

,PUR.PURCH_FR_LOC

,CASE

WHEN APV.VENDOR IS NULL THEN APM.VENDOR_VNAME

ELSE APV.VENDOR_VNAME

END AS 'Vendor_Name'

,PUR.BUYER_CODE

,MMD.ACCT_UNIT

FROM dbo.PURCHORDER PUR

left join POLINE POL on PUR.PO_NUMBER = POL.PO_NUMBER

left join MMDIST MMD on PUR.PO_NUMBER = MMD.DOC_NUMBER and MMD.SYSTEM_CD = 'PO' and MMD.DOC_TYPE = 'PT' and POL.LINE_NBR = MMD.LINE_NBR

left join APVENLOC APV on POL.VENDOR = APV.VENDOR and POL.PURCH_FR_LOC = APV.LOCATION_CODE

left join APVENMAST APM on POL.VENDOR = APM.VENDOR

where PO_DATE >= '<!Select_Date>' and PUR.ISSUED_FLAG = 'Y' and PUR.CLOSED_FL = 'N' and (MMD.ACCT_UNIT = '8700' or MMD.ACCT_UNIT = '6600') and (POL.REC_QTY < POL.QUANTITY) and (POL.ITEM_TYPE = 'N' or POL.ITEM_TYPE = 'X')

cpashley

Yes. I would need GL posting dates for this. We are not using crystal reporting but any information is appreciated.

cpashley

Lawson addins would be wonderful due to the turnaround time that I have been given for this

timezones.xlsx

VID048_Navigation_How to Include Menu Name in the Tabs.zip

cpashley

If you have a template for the addins - my email address is tammy.wiese@ssmhealth.com

construction.pdf

unknown

here is the SQL from Crystal:

SELECT "APDISTRIB"."COMPANY", "APDISTRIB"."DIS_ACCT_UNIT", "APDISTRIB"."DIS_ACCOUNT", "APDISTRIB"."VENDOR", "APDISTRIB"."DISTRIB_DATE", "APVENMAST"."VENDOR_VNAME", "APINVOICE"."INVOICE_DTE", "APDISTRIB"."INVOICE", "APDISTRIB"."SUFFIX", "APINVOICE"."PO_NUMBER", "APINVOICE"."DESCRIPTION", "APDISTRIB"."ORIG_TRAN_AMT", "GLNAMES"."DESCRIPTION", "GLCHARTDTL"."ACCOUNT_DESC"

FROM (((("LAWPROD"."APDISTRIB" "APDISTRIB" INNER JOIN "LAWPROD"."APINVOICE" "APINVOICE" ON (((("APDISTRIB"."COMPANY"="APINVOICE"."COMPANY") AND ("APDISTRIB"."CANCEL_SEQ"="APINVOICE"."CANCEL_SEQ")) AND ("APDISTRIB"."SUFFIX"="APINVOICE"."SUFFIX")) AND ("APDISTRIB"."INVOICE"="APINVOICE"."INVOICE")) AND ("APDISTRIB"."VENDOR"="APINVOICE"."VENDOR")) INNER JOIN "LAWPROD"."APVENMAST" "APVENMAST" ON "APDISTRIB"."VENDOR"="APVENMAST"."VENDOR") INNER JOIN "LAWPROD"."GLMASTER" "GLMASTER" ON ((("APDISTRIB"."DIST_COMPANY"="GLMASTER"."COMPANY") AND ("APDISTRIB"."DIS_ACCT_UNIT"="GLMASTER"."ACCT_UNIT")) AND ("APDISTRIB"."DIS_ACCOUNT"="GLMASTER"."ACCOUNT")) AND ("APDISTRIB"."DIS_SUB_ACCT"="GLMASTER"."SUB_ACCOUNT")) INNER JOIN "LAWPROD"."GLNAMES" "GLNAMES" ON ("GLMASTER"."COMPANY"="GLNAMES"."COMPANY") AND ("GLMASTER"."ACCT_UNIT"="GLNAMES"."ACCT_UNIT")) INNER JOIN "LAWPROD"."GLCHARTDTL" "GLCHARTDTL" ON (("GLMASTER"."ACCOUNT"="GLCHARTDTL"."ACCOUNT") AND ("GLMASTER"."SUB_ACCOUNT"="GLCHARTDTL"."SUB_ACCOUNT")) AND ("GLMASTER"."CHART_NAME"="GLCHARTDTL"."CHART_NAME")

WHERE "APDISTRIB"."COMPANY"=1000 AND ("APDISTRIB"."DISTRIB_DATE">=TO_DATE ('01-01-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "APDISTRIB"."DISTRIB_DATE"<TO_DATE ('01-02-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

ORDER BY "APDISTRIB"."COMPANY", "APDISTRIB"."DIS_ACCT_UNIT", "APDISTRIB"."DIS_ACCOUNT", "APDISTRIB"."VENDOR", "APDISTRIB"."DISTRIB_DATE", "APINVOICE"."INVOICE_DTE"

For add-ins, you may need to do more than one query and then use vlookup (or hlookup...) to add in some of the description fields that you want. APDISTRIB has a DISTRIB-DATE, which indicates the GL posting date. Start there and grab the other fields needed. You should be able to pull dis-acct-unit, dis-acct, dis-sub-acct, vendor, invoice, orig-tran-amt, and po-number all from the apdistrib table. Then see what relationships are available to APVENMAST for the vendor-vname, GLNAMES for the description, and GLCHARTDTL for the account-desc.

unknown

sending now from kelly.powell@badcock.com

amacknig

Hi Tammy, i emailed you two Add-In Queries, one for the PO info and one for the GL distribution/vendor info. you can call me (509-467-3627) to go over how they work.

Gail Gudgel

Copyright © 2025 Infor. All rights reserved.