Tuesday 13 January 2015

Query to find all items assigned to categories of a category set :

SELECT   mcats.category_set_name,
           mcat.segment1 || '.' || mcat.segment2 category,
           msi.segment1 item,
           msi.description item_desc
    FROM   mtl_item_categories micat,
           mtl_category_sets mcats,
           mtl_categories mcat,
           mtl_system_items_vl msi
   WHERE       mcats.category_set_name LIKE 'Inv%'
           AND micat.category_set_id = mcats.category_set_id
           AND micat.category_id = mcat.category_id
           AND mcat.segment1 LIKE 'N%'
           AND msi.inventory_item_id = micat.inventory_item_id
           AND msi.organization_id = micat.organization_id
           AND msi.organization_id = 204
ORDER BY   1, 2, 3
/

No comments:

Post a Comment