Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Item/Vendor report

 0 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
NatalieD
Veteran Member
Posts: 53
Veteran Member
6/16/2016 7:26 PM
    I'm trying to write a Crystal Report that has ITEMMAST and POITEMVEN to show all the items in the item master as well as any PO13 information. When I pull in APVENMAST to pull in the vendor name I have to filter out a specific vendor group. When I do this, it eliminates any IC11 records that don't have a PO13. Any suggestions?

    SELECT "ITEMMAST"."ITEM_GROUP", "ITEMMAST"."ITEM", "ITEMMAST"."DESCRIPTION", "ITEMMAST"."DESCRIPTION2", "ITEMMAST"."INVEN_MAJCL", "ITEMMAST"."INVEN_MINCL", "ITEMMAST"."MANUF_CODE", "ITEMMAST"."MANUF_DIVISION", "ITEMMAST"."MANUF_NBR", "ITEMMAST"."HAZARD_CODE", "ITEMMAST"."ADDED_DATE", "ITEMMAST"."ACTIVE_STATUS", "ITEMMAST"."ALT_UOM_01", "ITEMMAST"."ALT_UOM_02", "ITEMMAST"."ALT_UOM_03", "ITEMMAST"."ALT_UOM_04", "ITEMMAST"."ALT_UOM_CONV_01", "ITEMMAST"."ALT_UOM_CONV_02", "ITEMMAST"."ALT_UOM_CONV_03", "ITEMMAST"."ALT_UOM_CONV_04", "ITEMMAST"."ALT_UOM_MULT_01", "ITEMMAST"."ALT_UOM_MULT_02", "ITEMMAST"."ALT_UOM_MULT_03", "ITEMMAST"."ALT_UOM_MULT_04", "ITEMMAST"."BUY_FL_01", "ITEMMAST"."BUY_FL_02", "ITEMMAST"."BUY_FL_03", "ITEMMAST"."BUY_FL_04", "ITEMMAST"."USE_IDENTIFIER", "ITEMMAST"."CONSIGNMENT_FL", "ITEMMAST"."ICN_CODE", "ITEMMAST"."ICSEGMENT", "ITEMMAST"."ICFAMILY", "ITEMMAST"."ICCLASS", "ITEMMAST"."ICCOMMODITY", "ITEMMAST"."ISS_ACCOUNT", "ITEMMAST"."ISS_SUB_ACCT", "ITEMMAST"."COMMODITY_CODE", "ICMANFCODE"."DESCRIPTION", "POITEMVEN"."VENDOR", "POITEMVEN"."VEN_ITEM", "POITEMVEN"."DEFAULT_FLG", "POITEMVEN"."R_STATUS", "POITEMVEN"."VEN_ITEM_DESC", "APVENMAST"."VENDOR_VNAME", "APVENMAST"."VENDOR_GROUP"

    FROM (("LAWSON"."ITEMMAST" "ITEMMAST" LEFT OUTER JOIN "LAWSON"."ICMANFCODE" "ICMANFCODE" ON (("ITEMMAST"."ITEM_GROUP"="ICMANFCODE"."ITEM_GROUP") AND ("ITEMMAST"."MANUF_CODE"="ICMANFCODE"."MANUF_CODE")) AND ("ITEMMAST"."MANUF_DIVISION"="ICMANFCODE"."MANUF_DIVISION")) LEFT OUTER JOIN "LAWSON"."POITEMVEN" "POITEMVEN" ON "ITEMMAST"."ITEM"="POITEMVEN"."ITEM") LEFT OUTER JOIN "LAWSON"."APVENMAST" "APVENMAST" ON "POITEMVEN"."VENDOR"="APVENMAST"."VENDOR"

    WHERE "ITEMMAST"."ITEM_GROUP"='HOSP' AND "APVENMAST"."VENDOR_GROUP"<>'1400'
    ORDER BY "ITEMMAST"."ACTIVE_STATUS", "ITEMMAST"."MANUF_CODE"