View: EGO_GTIN_ATTRS_V

Product:	EGO - Advanced Product Catalog
Description:	This view contains GTINs and all GDSN attributes (Single-row only). This view is used to send RCIR messages.
Implementation/DBA Data:	APPS.EGO_GTIN_ATTRS_V

View Text

SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MXR.CROSS_REFERENCE GLOBAL_TRADE_ITEM_NUMBER
, EGA.BRAND_OWNER_GLN BRAND_OWNER_GLN
, EGA.BRAND_OWNER_NAME BRAND_OWNER_NAME
, EGA.STORAGE_HANDLING_TEMP_MAX STORAGE_HANDLING_TEMP_MAX
, EGA.UOM_STORAGE_HANDLING_TEMP_MAX UOM_STORAGE_HANDLING_TEMP_MAX
, EGA.STORAGE_HANDLING_TEMP_MIN STORAGE_HANDLING_TEMP_MIN
, EGA.UOM_STORAGE_HANDLING_TEMP_MIN UOM_STORAGE_HANDLING_TEMP_MIN
, EGA.TRADE_ITEM_COUPON TRADE_ITEM_COUPON
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'EGO_UCCNET_DATA_SOURCE_GLN'
AND LOOKUP_CODE = 'GLN') GLN_INFORMATION_PROVIDER
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'EGO_UCCNET_DATA_SOURCE_GLN'
AND LOOKUP_CODE = 'NAME') NAME_OF_INFORMATION_PROVIDER
, '' ADDRESS_OF_INFO_PROVIDER
, '840' TARGET_MARKET_COUNTRY_CODE
, EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT IS_TRADE_ITEM_A_CONSUMER_UNIT
, MSI.CUSTOMER_ORDER_ENABLED_FLAG IS_TRADE_ITEM_AN_ORD_UNIT
, EGA.IS_TRADE_ITEM_A_BASE_UNIT IS_TRADE_ITEM_A_BASE_UNIT
, MSI.SHIPPABLE_ITEM_FLAG IS_TRADE_ITEM_A_DESPATCH_UNIT
, MSI.INVOICE_ENABLED_FLAG IS_TRADE_ITEM_AN_INVOICE_UNIT
, EGA.IS_TRADE_ITEM_A_VARIABLE_UNIT IS_TRADE_ITEM_A_VARIABLE_UNIT
, MSI.UNIT_LENGTH DEPTH
, MSI.DIMENSION_UOM_CODE UOM_DEPTH
, MSI.UNIT_HEIGHT HEIGHT
, MSI.DIMENSION_UOM_CODE UOM_HEIGHT
, MSI.UNIT_WIDTH WIDTH
, MSI.DIMENSION_UOM_CODE UOM_WIDTH
, MSI.UNIT_WEIGHT NET_WEIGHT
, MSI.WEIGHT_UOM_CODE UOM_WEIGHT
, DECODE(EGA.DIAMETER
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT( EGA.INVENTORY_ITEM_ID
, 3
, EGA.DIAMETER
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'DIAMETER'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'DIAMETER'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_DIAMETER
, NULL
, NULL )) DIAMATER
, EGA.UOM_DIAMETER UOM_DIAMETER
, DECODE(EGA.GROSS_WEIGHT
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT( EGA.INVENTORY_ITEM_ID
, 3
, EGA.GROSS_WEIGHT
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'GROSS_WEIGHT'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'GROSS_WEIGHT'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_GROSS_WEIGHT
, NULL
, NULL)) GROSS_WEIGHT
, EGA.UOM_GROSS_WEIGHT UOM_GROSS_WEIGHT
, DECODE(EGA.DRAINED_WEIGHT
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 3
, EGA.DRAINED_WEIGHT
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'DRAINED_WEIGHT'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'DRAINED_WEIGHT'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_DRAINED_WEIGHT
, NULL
, NULL)) DRAINED_WEIGHT
, EGA.UOM_DRAINED_WEIGHT UOM_DRAINED_WEIGHT
, EGA.GENERIC_INGREDIENT GENERIC_INGREDIENT
, DECODE(EGA.GENERIC_INGREDIENT_STRGTH
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.GENERIC_INGREDIENT_STRGTH
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'GENERIC_INGREDIENT_STRGTH'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'GENERIC_INGREDIENT_STRGTH'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_GENERIC_INGREDIENT_STRGTH
, NULL
, NULL)) GENERIC_INGREDIENT_STRENGTH
, EGA.UOM_GENERIC_INGREDIENT_STRGTH UOM_GENERIC_INGREDIENT_STRTH
, EGA.INGREDIENT_STRENGTH INGREDIENT_STRENGTH
, EGA.IS_NET_CONTENT_DEC_FLAG IS_NET_CONTENT_DEC_FLAG
, EGA.NET_CONTENT
, EGA.UOM_NET_CONTENT UOM_NET_CONTENT
, DECODE(EGA.PEG_HORIZONTAL
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.PEG_HORIZONTAL
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'PEG_HORIZONTAL'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'PEG_HORIZONTAL'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_PEG_HORIZONTAL
, NULL
, NULL)) PEG_HORIZONTAL
, EGA.UOM_PEG_HORIZONTAL UOM_PEG_HORIZONTAL
, DECODE(EGA.PEG_VERTICAL
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.PEG_VERTICAL
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'PEG_VERTICAL'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'PEG_VERTICAL'
AND ATTR_GROUP_NAME = 'TRADE_ITEM_MEASUREMENTS'
AND APPLICATION_ID = 431))
, EGA.UOM_PEG_VERTICAL
, NULL
, NULL)) PEG_VERTICAL
, EGA.UOM_PEG_VERTICAL UOM_PEG_VERTICAL
, MSI.UNIT_VOLUME VOLUME
, MSI.DIMENSION_UOM_CODE UOM_DIMENSION
, MSI.VOLUME_UOM_CODE UOM_VOLUME
, EGA.ISBN_NUMBER ISBN_NUMBER
, EGA.ISSN_NUMBER ISSN_NUMBER
, EGA.DEGREE_OF_ORIGINAL_WORT DEGREE_OF_ORIGINAL_WORT
, EGA.FAT_PERCENT_IN_DRY_MATTER FAT_PERCENT_IN_DRY_MATTER
, EGA.PERCENT_OF_ALCOHOL_BY_VOL PERCENT_OF_ALCOHOL_BY_VOL
, EGA.IS_PACK_MARKED_WITH_EXP_DATE IS_PACK_MARKED_WITH_EXP_DATE
, EGA.IS_PACK_MARKED_WITH_GREEN_DOT IS_PACK_MARKED_WITH_GREEN_DOT
, EGA.IS_PACK_MARKED_WITH_INGRED IS_PACK_MARKED_WITH_INGRED
, EGA.IS_PACKAGE_MARKED_AS_REC IS_PACKAGE_MARKED_AS_REC
, EGA.IS_PACKAGE_MARKED_RET IS_PACKAGE_MARKED_RET
, EGA.IS_INGREDIENT_IRRADIATED IS_INGREDIENT_IRRADIATED
, EGA.IS_RAW_MATERIAL_IRRADIATED IS_RAW_MATERIAL_IRRADIATED
, EGA.IS_TRADE_ITEM_GENETICALLY_MOD IS_TRADE_ITEM_GENETICALLY_MOD
, EGA.IS_TRADE_ITEM_IRRADIATED IS_TRADE_ITEM_IRRADIATED
, EGA.MATERIAL_SAFETY_DATA_SHEET_NO MATERIAL_SAFETY_DATA_SHEET_NO
, EGA.RETAIL_PRICE_ON_TRADE_ITEM RETAIL_PRICE_ON_TRADE_ITEM
, 'USD' UOM_RETAIL_PRICE_ON_TRADE_ITEM
, DECODE(EGA.ORDERING_LEAD_TIME
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.ORDERING_LEAD_TIME
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'ORDERING_LEAD_TIME'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'ORDERING_LEAD_TIME'
AND ATTR_GROUP_NAME = 'ORDER_INFORMATION'
AND APPLICATION_ID = 431))
, EGA.UOM_ORDERING_LEAD_TIME
, NULL
, NULL)) ORDERING_LEAD_TIME
, EGA.UOM_ORDERING_LEAD_TIME UOM_ORDERING_LEAD_TIME
, EGA.ORDER_QUANTITY_MAX ORDER_QUANTITY_MAX
, EGA.ORDER_QUANTITY_MIN ORDER_QUANTITY_MIN
, EGA.ORDER_SIZING_FACTOR ORDER_SIZING_FACTOR
, EGA.ORDER_QUANTITY_MULTIPLE ORDER_QUANTITY_MULTIPLE
, EGA.STACKING_FACTOR STACKING_FACTOR
, DECODE(EGA.STACKING_WEIGHT_MAXIMUM
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.STACKING_WEIGHT_MAXIMUM
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'STACKING_WEIGHT_MAXIMUM'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'STACKING_WEIGHT_MAXIMUM'
AND ATTR_GROUP_NAME = 'HANDLING_INFORMATION'
AND APPLICATION_ID = 431))
, EGA.UOM_STACKING_WEIGHT_MAXIMUM
, NULL
, NULL)) STACKING_WEIGHT_MAXIMUM
, EGA.UOM_STACKING_WEIGHT_MAXIMUM UOM_STACKING_WEIGHT_MAXIMUM
, EGA.BRAND_NAME BRAND_NAME
, EGA.SUB_BRAND SUB_BRAND
, EGT.DESCRIPTION_SHORT DESCRIPTION_SHORT
, MST.DESCRIPTION ADDITIONAL_TRADE_ITEM_DESC
, MSI.TRADE_ITEM_DESCRIPTOR TRADE_ITEM_UNIT_DESCRIPTOR
, EGA.EANUCC_CODE EANUCC_CODE
, EGA.EANUCC_TYPE EANUCC_TYPE
, EGA.IS_TRADE_ITEM_INFO_PRIVATE IS_TRADE_ITEM_INFO_PRIVATE
, EGA.DEL_TO_DIST_CNTR_TEMP_MAX DEL_TO_DIST_CENTER_TEMP_MAX
, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MAX UOM_DEL_TO_DIST_CNTR_TEMP_MAX
, EGA.DEL_TO_DIST_CNTR_TEMP_MIN DEL_TO_DIST_CENTER_TEMP_MIN
, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MIN UOM_DEL_TO_DIST_CNTR_TEMP_MIN
, EGA.DELIVERY_TO_MRKT_TEMP_MAX DELIVERY_TO_MARKET_TEMP_MAX
, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MAX UOM_DELIVERY_TO_MRKT_TEMP_MAX
, EGA.DELIVERY_TO_MRKT_TEMP_MIN DELIVERY_TO_MARKET_TEMP_MIN
, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MIN UOM_DELIVERY_TO_MRKT_TEMP_MIN
, EGA.CATALOG_PRICE CATALOG_PRICE
, 'USD' UOM_CATALOG_PRICE
, EGA.SUGGESTED_RETAIL_PRICE SUGGESTED_RETAIL_PRICE
, 'USD' UOM_SUGGESTED_RETAIL_PRICE
, EGA.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, EGA.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, EGA.EFFECTIVE_DATE EFFECTIVE_DATE
, SYSDATE PUBLICATION_DATE
, SYSDATE DELETION_DATE
, EGA.CANCELED_DATE CANCELED_DATE
, EGA.DISCONTINUED_DATE DISCONTINUED_DATE
, EGA.END_AVAILABILITY_DATE_TIME END_AVAILABILITY_DATE_TIME
, EGA.START_AVAILABILITY_DATE_TIME START_AVAILABILITY_DATE_TIME
, EGA.CONSUMER_AVAIL_DATE_TIME CONSUMER_AVAIL_DATE_TIME
, EGA.HAS_BATCH_NUMBER HAS_BATCH_NUMBER
, EGA.IS_NON_SOLD_TRADE_RET_FLAG IS_NON_SOLD_TRADE_RET_FLAG
, EGA.IS_TRADE_ITEM_MAR_REC_FLAG IS_TRADE_ITEM_MAR_REC_FLAG
, EGA.QUANTITY_OF_COMP_LAY_ITEM QUANTITY_OF_COMP_LAY_ITEM
, EGA.QUANITY_OF_ITEM_IN_LAYER QUANITY_OF_ITEM_IN_LAYER
, EGA.QUANTITY_OF_ITEM_INNER_PACK QUANTITY_OF_ITEM_INNER_PACK
, EGA.QUANTITY_OF_INNER_PACK QUANTITY_OF_INNER_PACK
, EGA.SECURITY_TAG_LOCATION SECURITY_TAG_LOCATION
, EGA.URL_FOR_WARRANTY URL_FOR_WARRANTY
, EGT.WARRANTY_DESCRIPTION WARRANTY_DESCRIPTION
, DECODE(EGA.NESTING_INCREMENT
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.NESTING_INCREMENT
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'NESTING_INCREMENT'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'NESTING_INCREMENT'
AND ATTR_GROUP_NAME = 'UCCNET_HARDLINES'
AND APPLICATION_ID = 431))
, EGA.UOM_NESTING_INCREMENT
, NULL
, NULL)) NESTING_INCREMENT
, EGA.UOM_NESTING_INCREMENT UOM_NESTING_INCREMENT
, EGA.IS_TRADE_ITEM_RECALLED IS_TRADE_ITEM_RECALLED
, EGA.MODEL_NUMBER MODEL_NUMBER
, MSI.PRIMARY_UOM_CODE UOM_TRADE_ITEM
, MSI.SECONDARY_UOM_CODE UOM_ALTERNATE_TRADE_ITEM
, DECODE(EGA.PIECES_PER_TRADE_ITEM
, NULL
, NULL
, INV_CONVERT.INV_UM_CONVERT (EGA.INVENTORY_ITEM_ID
, 0
, EGA.PIECES_PER_TRADE_ITEM
, (SELECT DISTINCT UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE BASE_UOM_FLAG = 'Y'
AND UOM_CLASS = (SELECT UOM_CLASS
FROM EGO_ATTRS_V
WHERE DATABASE_COLUMN = 'PIECES_PER_TRADE_ITEM'
AND ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND ATTR_NAME = 'PIECES_PER_TRADE_ITEM'
AND ATTR_GROUP_NAME = 'UCCNET_HARDLINES'
AND APPLICATION_ID = 431))
, EGA.UOM_PIECES_PER_TRADE_ITEM
, NULL
, NULL)) PIECES_PER_TRADE_ITEM
, EGA.UOM_PIECES_PER_TRADE_ITEM UOM_PIECES_PER_TRADE_ITEM
, EGT.TRADE_ITEM_FINISH_DESCRIPTION TRADE_ITEM_FINISH_DESCRIPTION
, EGA.DEPT_OF_TRNSPRT_DANG_GOODS_NUM DEPT_OF_TRNSPRT_DANG_GOODS_NUM
, EGA.RETURN_GOODS_POLICY RETURN_GOODS_POLICY
, EGA.IS_OUT_OF_BOX_PROVIDED IS_OUT_OF_BOX_PROVIDED
, GPC.CONCATENATED_SEGMENTS CATALOG_ITEM_CLASSIFICIATION
, GPC.CONCATENATED_SEGMENTS CLASSIFICATION_CATEGORY_CODE
, GPC.DESCRIPTION CLASSIFICATION_CATEGORY_DES
, GPC.CONCATENATED_SEGMENTS CLASSIFICATION_CATEGORY_NAME
, MCKFV.CONCATENATED_SEGMENTS ADD_CLASS_CATALOG_NAME
, MCKFV.CONCATENATED_SEGMENTS ADD_CLASS_CATALOG_CODE
, MCTL.DESCRIPTION ADD_CLASS_CATALOG_DES
FROM MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MST
, MTL_CROSS_REFERENCES MXR
, MTL_CATEGORIES_B_KFV MCKFV
, MTL_ITEM_CATEGORIES MIC
, MTL_CATEGORIES_TL MCTL
, MTL_DEFAULT_CATEGORY_SETS MDC
, EGO_ITEM_GTN_ATTRS_B EGA
, EGO_ITEM_GTN_ATTRS_TL EGT
, MTL_PARAMETERS MP
, ( SELECT B.CONCATENATED_SEGMENTS
, TL.DESCRIPTION
, MIC.INVENTORY_ITEM_ID
, MIC.ORGANIZATION_ID
FROM MTL_CATEGORIES_B_KFV B
, MTL_CATEGORIES_TL TL
, MTL_DEFAULT_CATEGORY_SETS DC
, MTL_ITEM_CATEGORIES MIC
WHERE DC.FUNCTIONAL_AREA_ID = 21
AND DC.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = B.CATEGORY_ID
AND B.CATEGORY_ID = TL.CATEGORY_ID
AND TL.LANGUAGE = USERENV('LANG') ) GPC
WHERE MSI.INVENTORY_ITEM_ID = EGA.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = EGA.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = EGT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = EGT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MST.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MST.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MXR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND NVL(MXR.ORGANIZATION_ID
, MSI.ORGANIZATION_ID) = MSI.ORGANIZATION_ID
AND MXR.UOM_CODE = MSI.PRIMARY_UOM_CODE
AND MXR.CROSS_REFERENCE_TYPE = 'GTIN'
AND MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = MDC.CATEGORY_SET_ID
AND MDC.FUNCTIONAL_AREA_ID = 12
AND MCTL.CATEGORY_ID = MIC.CATEGORY_ID
AND MST.LANGUAGE = USERENV('LANG')
AND EGT.LANGUAGE = USERENV('LANG')
AND MCTL.LANGUAGE = USERENV('LANG')
AND GPC.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND GPC.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID

Columns