• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Supply Chain Management Lawson S3 Supply Chain

Tying ITEM to ACCT-UNIT

 3 Replies
 0 Subscribed to this topic
 38 Subscribed to this forum
Sort:
Author
Messages
mfc
New Member Send Private Message
Posts: 3
New Member
9/7/2010 8:45 PM
I am new to both this forum and to Lawson so please be gentle :laugh: My employer would like to separate out OR inventory from the general population both logically and physically. I have been tasked to do both. I use SQL Analyzer (and more recently LinqPad) to query from a test environment refreshed monthly. My goal is to link ITEMMAST.ITEM to (potentially) MMDIST.ACCT_UNIT by way of the POLINE table. I used the lawson.insight 8.0.x Entity Relationship Diagram for PO provided by this site to navigate my way. I would like to identify all ITEM's associated with a particular ACCT_UNIT of LEVEL_DEPTH 2. Unfortunately, my multi-table query returned only 23 rows, far less than the 5k rows expected. I'm hoping for clues to construct a query that will return all rows associated with our Surgery Dept. I can provide more info as needed. Thank you for any response.
David Williams
Veteran Member Send Private Message
Posts: 1127
Veteran Member
9/8/2010 2:42 PM
You should just be able to query against MMDIST to get everything you need.
David Williams
mfc
New Member Send Private Message
Posts: 3
New Member
9/8/2010 8:17 PM
Thank you for your reply, I appreciate it. I was hoping for a more detailed instruction, I am learning as I go. As I understand it, the MMDIST table keeps a record of material distribution from warehouse to business unit and ties the issued item to the General Ledger. It is sort of a "middle tier" between the GL and inventory. As I understand it, there are 2 paths to move (issue) inventory (at least in my facility), through a purchase order (PO, or POLINE) or a requisition (REQLINE). I think I need to query both avenues to fully populate my dataset. My final goal is to generate a dataset composed of ITEM, DESCRIPTION, and ACCT_UNIT for all units used exclusively by our Perioperative accounting unit. I've composed 2 queries, the first linking MMD -> PLI -> ITL -> ITM and the second query from MMD ->RLN->ITM to capture the full breadth of the dataset. Am I on the right track? Thanks again.
mfc
New Member Send Private Message
Posts: 3
New Member
9/10/2010 7:33 PM
Problem solved, I had the structure correct but was using an incorrect filter from our GL table. For your consideration: [code] SELECT DISTINCT CONVERT(bigint, ITM.ITEM) AS ITEM, ITM.DESCRIPTION, ITL.LOCATION, MMD.ACCT_UNIT FROM PROD.MMDIST MMD JOIN PROD.POLINE ON MMD.DOC_NUMBER = PROD.POLINE.PO_NUMBER JOIN PROD.ITEMLOC ITL ON PROD.POLINE.ITEM = ITL.ITEM JOIN PROD.ITEMMAST ITM ON ITM.ITEM = ITL.ITEM WHERE MMD.ACCT_UNIT LIKE '203030%' AND ITL.LOCATION = 'SPDS' UNION SELECT DISTINCT CONVERT(bigint, ITM.ITEM) AS ITEM, ITM.DESCRIPTION, ITL.LOCATION, MMD.ACCT_UNIT FROM PROD.MMDIST AS MMD JOIN PROD.REQLINE RLN ON MMD.DOC_NBR_NUM = RLN.REQ_NUMBER AND MMD.LINE_NBR = RLN.LINE_NBR JOIN PROD.ITEMMAST ITM ON RLN.ITEM = ITM.ITEM JOIN PROD.ITEMLOC ITL ON RLN.ITEM = ITL.ITEM WHERE MMD.ACCT_UNIT LIKE '203030%' AND ITL.LOCATION = 'SPDS' ORDER BY ITEM [/code] Now all I have to do is cross reference the output with items used by the general population to reduce the dataset to those items used exclusively by the OR. Case Closed.