• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

GLTrans - PO Line/MAInvdtl

 4 Replies
 1 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
JosieD77
New Member
Posts: 1
New Member
8/6/2025 6:13 PM
Hello, we have an existing tabular model for finance account distributions that I am editing. It is a query using Lawson Infor data that is currently showing the distribution amount rolled up to the PO level. We have been asked to add quantity from the PO Line/MAINVDTL level, and we are incorporating that as well as the MMDIST table . We have different invoice handling types such as SERV and MAT. For SERV type, the MMDIST table isn't necessarily used, but I have not been able to find a way to get from GL Trans to APDist to MINCDTL/PO LIne without duplicating the GL Trans Object ID. For other types where the MMDIST table is used, such as MAT, MMDIST gets me to the right grain. Does anyone have experience doing something like this?
John Henley
Posts: 3359
8/6/2025 7:26 PM
Hi JosieD77, I have also had this problem before. MAINVCDTL is related to APINVOICE not APDISTRIB, so you will have that as an issue. Can you create a subquery against MAINVCTL that joins to APINVOICE? Or have you tried to go from MMDIST to MAINVCDTL. One other option would be to do a separate query of service and then UNION with query of materials. That way you're not trying to cram everything into one query?
Thanks for using the LawsonGuru.com forums!
John
JosieD77
New Member
Posts: 1
New Member
8/6/2025 8:30 PM
Thanks John,

I am doing a large union which does have a separate query for service. I do also join to APINvoice from APDist , then from APInvoice to MAInvdtl but the issue is that I have not found a way to relate the po lines to the GL Object ID so once I get down to the PO line, it repeats the object id for each line if that makes sense. The requesters want the detail from the PO LIne but the amounts to roll up to match what is in APDIST/GL Trans.
John Henley
Posts: 3359
8/13/2025 6:43 PM
Would you be able to share your query and some sample output? that might make it easier to see where you are running into issues.
Thanks for using the LawsonGuru.com forums!
John
Bill Ianni
Veteran Member
Posts: 112
Veteran Member
9/1/2025 8:18 PM
Are you certain MAINVCDTL is the correct name for the table? Did you mean MAINVDTL (MA43) or MACINVDTL MA54.3)? I'm going to presume you intended MAINVDTL because MACINVDTL is a conversion table and therefore an APINVOICE record cannot possibly exist for it. For reporting purposes, MAINVDTL is essentially the POLINE "on the invoice". That table shows the PO line values such as quantity and unit cost as well as invoiced values. Similarly, APDISTRIB is essentially the same as MMDIST. The exception will be for Inventoried (I-type) items. In the case of Inventory, the APDISTRIB will be the RNI account defined on MA01. On APDISTRIB, when you see that RNI account, you know you have in Inventory item and the GL will come from IC tables ICCATEGORY and ITEMLOC for the given Item. Otherwise APDISTRIB = MMDIST, but APDISTRIB will also include no PO entries such as Taxes and AOC's. But those are easily filterable. Lastly, GLTrans is relatable to the extent the setup for Detail Posting is "Y". Otherwise if Detail Posting is "N" then the system "summarizes" the interface form AP to GL in which case the opportunity to reconcile GLTRANS to POLINE cannot be accomplished.