Extract Query Inventory Item Category Set

The Following SQL Extract Query Will Help to Extract Inventory Item Category Set along with categories assigned in category set from Database.


--------------------------------------------------------------------------
INVENTORY CATEGORY SET EXTRACT --------------------------------------------------------------------------
********************HEADER************************
--------------------------------------------------------------------------

SELECT A.CATEGORY_SET_NAME,
  A.DESCRIPTION,
  A.STRUCTURE_NAME       AS FLEX_STRUCTURE,
  E.MEANING              AS CONTROLLED_AT,
  B.CATEGORY_CONCAT_SEGS AS DEFAULT_CATEGORY,
  C.MEANING              AS MULTI_ITEM_CAT_ASS_FLAG,
  D.MEANING              AS ENFORCE_VALID_CATEGORIES
FROM MTL_CATEGORY_SETS_V A,
  MTL_CATEGORIES_V B,
  FND_LOOKUPS C,
  FND_LOOKUPS D,
  MFG_LOOKUPS E
WHERE A.DEFAULT_CATEGORY_ID = B.CATEGORY_ID
  --AND A.CATEGORY_SET_NAME         = 'XXAL01'
AND C.LOOKUP_TYPE               ='YES_NO'
AND D.LOOKUP_TYPE               ='YES_NO'
AND A.MULT_ITEM_CAT_ASSIGN_FLAG = C.LOOKUP_CODE
AND A.VALIDATE_FLAG             = D.LOOKUP_CODE
AND A.CONTROL_LEVEL             = E.LOOKUP_CODE
AND E.LOOKUP_TYPE               = 'INV_CATEGORY_SET_CONTROL_LEVEL' ;

-------------------------------------------------------------------------------
**************************LINES**************************************
-------------------------------------------------------------------------------

SELECT A.CATEGORY_SET_NAME   AS C_CATEGORY_SET_NAME,
  B.STRUCTURE_NAME           AS C_FLEX_STRUCTURE,
  A.CATEGORY_CONCAT_SEGMENTS AS C_CATEGORY
FROM MTL_CATEGORY_SET_VALID_CATS_V A,
  MTL_CATEGORY_SETS_V B
WHERE A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
--AND a.CATEGORY_SET_NAME = 'XXAL01'
--AND B.STRUCTURE_NAME = 'Item Categories'
------------------------------------------------------------------
*********BASE TABLES********************************************
------------------------------------------------------------------
SELECT * FROM MTL_CATEGORY_SETS_V;
SELECT * FROM MTL_CATEGORIES_V;
SELECT * FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'YES_NO';
SELECT * FROM MTL_CATEGORY_SET_VALID_CATS_V;