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


          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.