Tying ITEM to ACCT-UNIT

Sort:
You are not authorized to post a reply.
Author
Messages
mfc
New Member
Posts: 3
New Member
    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
      You should just be able to query against MMDIST to get everything you need.
      David Williams
      mfc
      New Member
      Posts: 3
      New Member
        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
          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.
          You are not authorized to post a reply.