112: From MTL_SYSTEM_ITEMS_B Item
113: Where Item.INVENTORY_ITEM_FLAG = 'Y'
114: And NOT EXISTS
115: ( Select 'X'
116: From MTL_ITEM_CATEGORIES Cat
117: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
118: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
119: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
120: )
230: Where ( Item.PURCHASING_ITEM_FLAG = 'Y' OR
231: Item.INTERNAL_ORDER_FLAG = 'Y' )
232: And NOT EXISTS
233: ( Select 'X'
234: From MTL_ITEM_CATEGORIES Cat
235: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
236: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
237: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
238: );
267: From MTL_SYSTEM_ITEMS_B Item
268: Where Item.MRP_PLANNING_CODE <> 6
269: And NOT EXISTS
270: ( Select 'X'
271: From MTL_ITEM_CATEGORIES Cat
272: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
273: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
274: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
275: )
401: From MTL_SYSTEM_ITEMS_B Item
402: Where Item.COSTING_ENABLED_FLAG = 'Y'
403: And NOT EXISTS
404: ( Select 'X'
405: From MTL_ITEM_CATEGORIES Cat
406: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
407: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
408: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
409: );
438: From MTL_SYSTEM_ITEMS_B Item
439: Where Item.ENG_ITEM_FLAG = 'Y'
440: And NOT EXISTS
441: ( Select 'X'
442: From MTL_ITEM_CATEGORIES Cat
443: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
444: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
445: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
446: )
471: From MTL_SYSTEM_ITEMS_B Item
472: Where Item.CUSTOMER_ORDER_FLAG = 'Y'
473: And NOT EXISTS
474: ( Select 'X'
475: From MTL_ITEM_CATEGORIES Cat
476: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
477: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
478: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
479: )
518: WHERE
519: Item.EAM_ITEM_TYPE IS NOT NULL
520: AND NOT EXISTS
521: ( SELECT 'X'
522: FROM MTL_ITEM_CATEGORIES Cat
523: WHERE
524: Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
525: AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
526: AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
543: WHERE
544: Item.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY')
545: AND NOT EXISTS
546: ( SELECT 'X'
547: FROM MTL_ITEM_CATEGORIES Cat
548: WHERE
549: Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
550: AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
551: AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
567: WHERE
568: Item.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION', 'USAGE')
569: AND NOT EXISTS
570: ( SELECT 'X'
571: FROM MTL_ITEM_CATEGORIES Cat
572: WHERE
573: Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
574: AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
575: AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
595: Where ( Item.CUSTOMER_ORDER_FLAG = 'Y' OR
596: Item.INTERNAL_ORDER_FLAG = 'Y' )
597: And NOT EXISTS
598: ( Select 'X'
599: From MTL_ITEM_CATEGORIES Cat
600: Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
601: And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
602: And Cat.CATEGORY_SET_ID = P_Category_Set_Id
603: )
622: Begin
623:
624: Select 'X'
625: Into buffer
626: From MTL_ITEM_CATEGORIES Cat,
627: MTL_DEFAULT_CATEGORY_SETS DefCat
628: Where DefCat.FUNCTIONAL_AREA_ID = 12
629: And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
630: And NOT EXISTS
628: Where DefCat.FUNCTIONAL_AREA_ID = 12
629: And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
630: And NOT EXISTS
631: ( Select 'X'
632: From MTL_ITEM_CATEGORIES Cat1
633: Where Cat1.INVENTORY_ITEM_ID = Cat.INVENTORY_ITEM_ID
634: And Cat1.ORGANIZATION_ID = Cat.ORGANIZATION_ID
635: And Cat1.CATEGORY_SET_ID = P_Category_Set_Id
636: )
653: Begin
654:
655: Select 'X'
656: Into buffer
657: From MTL_ITEM_CATEGORIES Cat,
658: MTL_DEFAULT_CATEGORY_SETS DefCat
659: Where DefCat.FUNCTIONAL_AREA_ID = 21
660: And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
661: And NOT EXISTS
659: Where DefCat.FUNCTIONAL_AREA_ID = 21
660: And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
661: And NOT EXISTS
662: ( Select 'X'
663: From MTL_ITEM_CATEGORIES Cat1
664: Where Cat1.INVENTORY_ITEM_ID = Cat.INVENTORY_ITEM_ID
665: And Cat1.ORGANIZATION_ID = Cat.ORGANIZATION_ID
666: And Cat1.CATEGORY_SET_ID = P_Category_Set_Id
667: )