物料类别获取函数

CREATE OR REPLACE FUNCTION APPS.MEW_ITEM_GROUP_F(P_ORGANIZATION_ID   NUMBER
                                                ,P_INVENTORY_ITEM_ID NUMBER
                                                ,P_CATEGORY_SET_NAME VARCHAR2
                                                ,P_SEGMENT_NUM       NUMBER)
  RETURN VARCHAR2 IS
  V_GROUP_NO VARCHAR2(240);
BEGIN
  SELECT DECODE(P_SEGMENT_NUM
               ,1
               ,MCB.SEGMENT1
               ,2
               ,MCB.SEGMENT2
               ,3
               ,MCB.SEGMENT3
               ,4
               ,MCB.SEGMENT4
               ,5
               ,MCB.SEGMENT5
               ,6
               ,MCB.SEGMENT6
               ,'XXX')
    INTO V_GROUP_NO
    FROM MTL_CATEGORY_SETS   MCS
        ,MTL_ITEM_CATEGORIES MIC
        ,MTL_CATEGORIES_B    MCB
   WHERE MCS.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
     AND MCS.CATEGORY_SET_NAME = P_CATEGORY_SET_NAME --'MEW_ITEM'
     AND MCB.STRUCTURE_ID = MCS.STRUCTURE_ID
     AND MCB.CATEGORY_ID = MIC.CATEGORY_ID
     AND MIC.ORGANIZATION_ID = P_ORGANIZATION_ID
     AND MIC.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
  RETURN V_GROUP_NO;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;