VIEW: APPS.EGO_GTIN_ATTRS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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