PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 05/01/2019 3:19 PM by  JonA
Query for Procurement History
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Rebecca Bosley
Business Application Analyst
Private
New Member
(1 points)
New Member
Posts:1


Send Message:

--
04/11/2019 1:21 PM

    I have been racking my brain trying to figure out what tables would be best to query so that I can report on all X, N, and I type items that our organization purchased last year using one of our medical supply accounts. The problem I run into is that if I pull from the POLINE table, I'm not able to see the inventory purchases because our inventory orders all go through a balance sheet account. I can look at REQLINE, but our leadership says that they aren't comfortable using those figures because they may not accurately reflect what was actually paid for the items. I have tried using Crystal Reports to write a query that joins the MMDIST table with the REQLINE table and the POLINE table, but it isn't working no matter how I do my joins (I either end up with hundreds of thousands of duplicate lines, or I only get 120 results for the entire year, which isn't possible). There has to be an easy way to do this that I haven't considered! Any help would be greatly appreciated!

    SWilkins
    Admin Systems Architect
    Phoenix Children's Hospital
    Advanced Member
    (61 points)
    Advanced Member
    Posts:23


    Send Message:

    --
    04/11/2019 3:43 PM
    How often you run IC130 can have an impact on this answer but it sounds like you need to add the table ICTRANS and field OFF-ACCOUNT
    Kat V
    Sr Supply Chain System Analyst
    South Broward Hospital District
    Veteran Member
    (2422 points)
    Veteran Member
    Posts:826


    Send Message:

    --
    04/15/2019 10:54 AM
    Have you looked at MM280?
    JonA
    Private
    Private
    Veteran Member
    (3098 points)
    Veteran Member
    Posts:1092


    Send Message:

    --
    05/01/2019 3:19 PM
    I've run into this same issue with Crystal. What I do is run separate reports for N/X versus I items.

    I'm joining POLINE, MMDIST and MAINVDTL

    Record selection for nonstock and special lines:
    {MMDIST.POSTING_TYPE} <> "RN" and
    {MMDIST.POSTING_TYPE} <> "H1" and
    {POLINE.ITEM_TYPE} <> "S" and
    {MAINVDTL.DISTRIB_DATE} >= CDate (2018,04,01) and
    {MAINVDTL.DISTRIB_DATE} <= CDate (2019,03,31)

    Record selection for inventory lines:
    //{MMDIST.POSTING_TYPE} <> "RN" and
    //{MMDIST.POSTING_TYPE} <> "H1" and
    {POLINE.ITEM_TYPE} = "I" and
    {MAINVDTL.DISTRIB_DATE} >= CDate (2018,04,01) and
    {MAINVDTL.DISTRIB_DATE} <= CDate (2019,03,31)

    Since the inventory line report doesn't show the GL account you might be able to also join ITEMLOC and ICCATEGORY to pull in the issue account for those items. But what I usually do is run a separate report out of ITEMLOC and do a vlookup against that data to identify the inventory items that are medical supplies.
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    You are not authorized to post a reply.