VIEW: APPS.EGO_GTIN_UCCNET_HIER_ATTRS_V

Source

View Text - Preformatted

SELECT MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, MXR.CROSS_REFERENCE GLOBAL_TRADE_ITEM_NUMBER, MSI.CONCATENATED_SEGMENTS, 'SELLER_ASSIGNED', EGA.BRAND_OWNER_GLN, EGA.BRAND_OWNER_NAME, EGA.STORAGE_HANDLING_TEMP_MAX, EGA.UOM_STORAGE_HANDLING_TEMP_MAX, EGA.STORAGE_HANDLING_TEMP_MIN, EGA.UOM_STORAGE_HANDLING_TEMP_MIN, EGA.TRADE_ITEM_COUPON, (SELECT meaning FROM fnd_lookups WHERE LOOKUP_TYPE = 'EGO_UCCNET_DATA_SOURCE_GLN' AND LOOKUP_CODE = 'GLN' ) , ( SELECT meaning FROM fnd_lookups WHERE lookup_type = 'EGO_UCCNET_DATA_SOURCE_GLN' AND LOOKUP_CODE = 'NAME' ) , '', '840', DECODE(EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT, 'Y', 1, 'N', 0, 0), DECODE(MSI.CUSTOMER_ORDER_ENABLED_FLAG, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_TRADE_ITEM_A_BASE_UNIT, 'Y', 1, 'N', 0, 0), DECODE(MSI.SHIPPABLE_ITEM_FLAG, 'Y', 1, 'N', 0, 0), DECODE(MSI.INVOICE_ENABLED_FLAG, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_TRADE_ITEM_A_VARIABLE_UNIT, 'Y', 1, 'N', 0, 0), MSI.DIMENSION_UOM_CODE, MSI.UNIT_LENGTH, MSI.DIMENSION_UOM_CODE UOM_DEPTH, MSI.UNIT_HEIGHT, MSI.DIMENSION_UOM_CODE UOM_HEIGHT, MSI.UNIT_WIDTH, MSI.DIMENSION_UOM_CODE UOM_WIDTH, MSI.UNIT_WEIGHT, MSI.WEIGHT_UOM_CODE, 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 ) ) DIAMETER, EGA.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, 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, EGA.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_STRGTH, EGA.UOM_GENERIC_INGREDIENT_STRGTH, EGA.INGREDIENT_STRENGTH, DECODE(EGA.IS_NET_CONTENT_DEC_FLAG, 'Y', 1, 'N', 0, ''), EGA.NET_CONTENT, EGA.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, 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, MSI.UNIT_VOLUME, MSI.VOLUME_UOM_CODE, EGA.ISBN_NUMBER, EGA.ISSN_NUMBER, EGA.DEGREE_OF_ORIGINAL_WORT, EGA.FAT_PERCENT_IN_DRY_MATTER, EGA.PERCENT_OF_ALCOHOL_BY_VOL, DECODE(EGA.IS_PACK_MARKED_WITH_EXP_DATE, 'Y', 1, 'N', 0, ''), DECODE(EGA.IS_PACK_MARKED_WITH_GREEN_DOT, 'Y', 1, 'N', 0, ''), DECODE(EGA.IS_PACK_MARKED_WITH_INGRED, 'Y', 1, 'N', 0, ''), DECODE(EGA.IS_PACKAGE_MARKED_AS_REC, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_PACKAGE_MARKED_RET, 'Y', 1, 'N', 0, 0), MSI.SHELF_LIFE_DAYS, DECODE(EGA.IS_INGREDIENT_IRRADIATED, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_RAW_MATERIAL_IRRADIATED, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_TRADE_ITEM_GENETICALLY_MOD, 'Y', 1, 'N', 0, 0), DECODE(EGA.IS_TRADE_ITEM_IRRADIATED, 'Y', 1, 'N', 0, 0), EGA.MATERIAL_SAFETY_DATA_SHEET_NO, EGA.RETAIL_PRICE_ON_TRADE_ITEM, 'USD', 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, EGA.ORDER_QUANTITY_MAX, EGA.ORDER_QUANTITY_MIN, EGA.ORDER_SIZING_FACTOR, MSI.DIMENSION_UOM_CODE, EGA.ORDER_QUANTITY_MULTIPLE, EGA.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, EGA.BRAND_NAME, EGA.SUB_BRAND, EGT.DESCRIPTION_SHORT, MST.DESCRIPTION, MSI.TRADE_ITEM_DESCRIPTOR, EGA.EANUCC_CODE, EGA.EANUCC_TYPE, DECODE(EGA.IS_TRADE_ITEM_INFO_PRIVATE, 'Y', 1, 'N', 0, 0), EGA.DEL_TO_DIST_CNTR_TEMP_MAX, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MAX, EGA.DEL_TO_DIST_CNTR_TEMP_MIN, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MIN, EGA.DELIVERY_TO_MRKT_TEMP_MAX, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MAX, EGA.DELIVERY_TO_MRKT_TEMP_MIN, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MIN, EGA.CATALOG_PRICE, 'USD', EGA.SUGGESTED_RETAIL_PRICE, 'USD', EGA.EFFECTIVE_START_DATE, EGA.EFFECTIVE_END_DATE, EGA.EFFECTIVE_DATE, SYSDATE, SYSDATE, EGA.CANCELED_DATE, EGA.DISCONTINUED_DATE, SYSDATE, EGA.END_AVAILABILITY_DATE_TIME, EGA.START_AVAILABILITY_DATE_TIME, EGA.CONSUMER_AVAIL_DATE_TIME, DECODE(EGA.HAS_BATCH_NUMBER, 'Y', 1, 'N', 0, ''), DECODE(EGA.IS_NON_SOLD_TRADE_RET_FLAG, 'Y', 1, 'N', 0, ''), DECODE(EGA.IS_TRADE_ITEM_MAR_REC_FLAG, 'Y', 1, 'N', 0, ''), EGA.QUANTITY_OF_COMP_LAY_ITEM, EGA.QUANITY_OF_ITEM_IN_LAYER, EGA.QUANTITY_OF_ITEM_INNER_PACK, EGA.QUANTITY_OF_INNER_PACK, EGA.SECURITY_TAG_LOCATION, EGA.URL_FOR_WARRANTY, EGT.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, DECODE(EGA.IS_TRADE_ITEM_RECALLED, 'Y', 1, 'N', 0, 0) IS_TRADE_ITEM_RECALLED, EGA.MODEL_NUMBER, MSI.PRIMARY_UOM_CODE TRADE_ITEM_UOM, MSI.SECONDARY_UOM_CODE TRADE_ITEM_ALTERNATE_UOM, 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, EGT.TRADE_ITEM_FINISH_DESCRIPTION, EGA.DEPT_OF_TRNSPRT_DANG_GOODS_NUM, EGA.RETURN_GOODS_POLICY, EGA.IS_OUT_OF_BOX_PROVIDED, EVE.TOTAL_QUANTITY, EVE.NUMBER_OF_CHILDREN, GPC.concatenated_segments, GPC.concatenated_segments, GPC.description, GPC.concatenated_segments, MCKFV.concatenated_segments, MCKFV.concatenated_segments, MCTL.description, EVE.BATCH_ID, EVE.INDUSTRY, EVE.PARENT_GTIN, EVE.PLAN_LEVEL, EVE.QUANTITY, EVE.SUBBATCH_ID, EVE.SUPPLIER_GLN, EVE.TARGET_MARKET, EVE.TOP_GTIN, EVE.TP_GLN, decode(EGA.IS_BARCODE_SYMBOLOGY_DERIVABLE, 'Y', 1, 'N', 0, '') IS_BARCODE_SYMBOLOGY_DERIVABLE, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.UNIT_LENGTH, '') OUTOFBOX_DEPTH, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.DIMENSION_UOM_CODE, '') UOM_OUTOFBOX_DEPTH, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.UNIT_HEIGHT, '') OUTOFBOX_HEIGHT, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.DIMENSION_UOM_CODE, '') UOM_OUTOFBOX_HEIGHT, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.UNIT_WIDTH, '') OUTOFBOX_WIDTH, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED, 'Y', MSI.DIMENSION_UOM_CODE, '') UOM_OUTOFBOX_WIDTH, DECODE(EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT, 'Y', MSI.PRIMARY_UOM_CODE, '') SELLING_UOM, MSI.SECONDARY_UOM_CODE ORDERING_UOM FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_TL MST, MTL_CROSS_REFERENCES MXR, MTL_CATEGORIES_B_KFV MCKFV, MTL_CATEGORIES_TL MCTL, MTL_DEFAULT_CATEGORY_SETS MDC, EGO_ITEM_GTN_ATTRS_B EGA, EGO_ITEM_GTN_ATTRS_TL EGT, MTL_PARAMETERS MP , MTL_ITEM_CATEGORIES MIC, EGO_UCCNET_EVENTS EVE, (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.ORGANIZATION_ID = EGT.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = EGT.INVENTORY_ITEM_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 MCKFV.CATEGORY_ID = MIC.CATEGORY_ID AND MDC.FUNCTIONAL_AREA_ID = 12 AND MCTL.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 MXR.UOM_CODE = MSI.PRIMARY_UOM_CODE AND MXR.CROSS_REFERENCE_TYPE = 'GTIN' AND MXR.CROSS_REFERENCE = EVE.GTIN 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
, MSI.INVENTORY_ITEM_ID
, MXR.CROSS_REFERENCE GLOBAL_TRADE_ITEM_NUMBER
, MSI.CONCATENATED_SEGMENTS
, 'SELLER_ASSIGNED'
, EGA.BRAND_OWNER_GLN
, EGA.BRAND_OWNER_NAME
, EGA.STORAGE_HANDLING_TEMP_MAX
, EGA.UOM_STORAGE_HANDLING_TEMP_MAX
, EGA.STORAGE_HANDLING_TEMP_MIN
, EGA.UOM_STORAGE_HANDLING_TEMP_MIN
, EGA.TRADE_ITEM_COUPON
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'EGO_UCCNET_DATA_SOURCE_GLN'
AND LOOKUP_CODE = 'GLN' )
, ( SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'EGO_UCCNET_DATA_SOURCE_GLN'
AND LOOKUP_CODE = 'NAME' )
, ''
, '840'
, DECODE(EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(MSI.CUSTOMER_ORDER_ENABLED_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_TRADE_ITEM_A_BASE_UNIT
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(MSI.SHIPPABLE_ITEM_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_TRADE_ITEM_A_VARIABLE_UNIT
, 'Y'
, 1
, 'N'
, 0
, 0)
, MSI.DIMENSION_UOM_CODE
, MSI.UNIT_LENGTH
, MSI.DIMENSION_UOM_CODE UOM_DEPTH
, MSI.UNIT_HEIGHT
, MSI.DIMENSION_UOM_CODE UOM_HEIGHT
, MSI.UNIT_WIDTH
, MSI.DIMENSION_UOM_CODE UOM_WIDTH
, MSI.UNIT_WEIGHT
, MSI.WEIGHT_UOM_CODE
, 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 ) ) DIAMETER
, EGA.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
, 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
, EGA.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_STRGTH
, EGA.UOM_GENERIC_INGREDIENT_STRGTH
, EGA.INGREDIENT_STRENGTH
, DECODE(EGA.IS_NET_CONTENT_DEC_FLAG
, 'Y'
, 1
, 'N'
, 0
, '')
, EGA.NET_CONTENT
, EGA.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
, 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
, MSI.UNIT_VOLUME
, MSI.VOLUME_UOM_CODE
, EGA.ISBN_NUMBER
, EGA.ISSN_NUMBER
, EGA.DEGREE_OF_ORIGINAL_WORT
, EGA.FAT_PERCENT_IN_DRY_MATTER
, EGA.PERCENT_OF_ALCOHOL_BY_VOL
, DECODE(EGA.IS_PACK_MARKED_WITH_EXP_DATE
, 'Y'
, 1
, 'N'
, 0
, '')
, DECODE(EGA.IS_PACK_MARKED_WITH_GREEN_DOT
, 'Y'
, 1
, 'N'
, 0
, '')
, DECODE(EGA.IS_PACK_MARKED_WITH_INGRED
, 'Y'
, 1
, 'N'
, 0
, '')
, DECODE(EGA.IS_PACKAGE_MARKED_AS_REC
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_PACKAGE_MARKED_RET
, 'Y'
, 1
, 'N'
, 0
, 0)
, MSI.SHELF_LIFE_DAYS
, DECODE(EGA.IS_INGREDIENT_IRRADIATED
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_RAW_MATERIAL_IRRADIATED
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_TRADE_ITEM_GENETICALLY_MOD
, 'Y'
, 1
, 'N'
, 0
, 0)
, DECODE(EGA.IS_TRADE_ITEM_IRRADIATED
, 'Y'
, 1
, 'N'
, 0
, 0)
, EGA.MATERIAL_SAFETY_DATA_SHEET_NO
, EGA.RETAIL_PRICE_ON_TRADE_ITEM
, 'USD'
, 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
, EGA.ORDER_QUANTITY_MAX
, EGA.ORDER_QUANTITY_MIN
, EGA.ORDER_SIZING_FACTOR
, MSI.DIMENSION_UOM_CODE
, EGA.ORDER_QUANTITY_MULTIPLE
, EGA.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
, EGA.BRAND_NAME
, EGA.SUB_BRAND
, EGT.DESCRIPTION_SHORT
, MST.DESCRIPTION
, MSI.TRADE_ITEM_DESCRIPTOR
, EGA.EANUCC_CODE
, EGA.EANUCC_TYPE
, DECODE(EGA.IS_TRADE_ITEM_INFO_PRIVATE
, 'Y'
, 1
, 'N'
, 0
, 0)
, EGA.DEL_TO_DIST_CNTR_TEMP_MAX
, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MAX
, EGA.DEL_TO_DIST_CNTR_TEMP_MIN
, EGA.UOM_DEL_TO_DIST_CNTR_TEMP_MIN
, EGA.DELIVERY_TO_MRKT_TEMP_MAX
, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MAX
, EGA.DELIVERY_TO_MRKT_TEMP_MIN
, EGA.UOM_DELIVERY_TO_MRKT_TEMP_MIN
, EGA.CATALOG_PRICE
, 'USD'
, EGA.SUGGESTED_RETAIL_PRICE
, 'USD'
, EGA.EFFECTIVE_START_DATE
, EGA.EFFECTIVE_END_DATE
, EGA.EFFECTIVE_DATE
, SYSDATE
, SYSDATE
, EGA.CANCELED_DATE
, EGA.DISCONTINUED_DATE
, SYSDATE
, EGA.END_AVAILABILITY_DATE_TIME
, EGA.START_AVAILABILITY_DATE_TIME
, EGA.CONSUMER_AVAIL_DATE_TIME
, DECODE(EGA.HAS_BATCH_NUMBER
, 'Y'
, 1
, 'N'
, 0
, '')
, DECODE(EGA.IS_NON_SOLD_TRADE_RET_FLAG
, 'Y'
, 1
, 'N'
, 0
, '')
, DECODE(EGA.IS_TRADE_ITEM_MAR_REC_FLAG
, 'Y'
, 1
, 'N'
, 0
, '')
, EGA.QUANTITY_OF_COMP_LAY_ITEM
, EGA.QUANITY_OF_ITEM_IN_LAYER
, EGA.QUANTITY_OF_ITEM_INNER_PACK
, EGA.QUANTITY_OF_INNER_PACK
, EGA.SECURITY_TAG_LOCATION
, EGA.URL_FOR_WARRANTY
, EGT.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
, DECODE(EGA.IS_TRADE_ITEM_RECALLED
, 'Y'
, 1
, 'N'
, 0
, 0) IS_TRADE_ITEM_RECALLED
, EGA.MODEL_NUMBER
, MSI.PRIMARY_UOM_CODE TRADE_ITEM_UOM
, MSI.SECONDARY_UOM_CODE TRADE_ITEM_ALTERNATE_UOM
, 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
, EGT.TRADE_ITEM_FINISH_DESCRIPTION
, EGA.DEPT_OF_TRNSPRT_DANG_GOODS_NUM
, EGA.RETURN_GOODS_POLICY
, EGA.IS_OUT_OF_BOX_PROVIDED
, EVE.TOTAL_QUANTITY
, EVE.NUMBER_OF_CHILDREN
, GPC.CONCATENATED_SEGMENTS
, GPC.CONCATENATED_SEGMENTS
, GPC.DESCRIPTION
, GPC.CONCATENATED_SEGMENTS
, MCKFV.CONCATENATED_SEGMENTS
, MCKFV.CONCATENATED_SEGMENTS
, MCTL.DESCRIPTION
, EVE.BATCH_ID
, EVE.INDUSTRY
, EVE.PARENT_GTIN
, EVE.PLAN_LEVEL
, EVE.QUANTITY
, EVE.SUBBATCH_ID
, EVE.SUPPLIER_GLN
, EVE.TARGET_MARKET
, EVE.TOP_GTIN
, EVE.TP_GLN
, DECODE(EGA.IS_BARCODE_SYMBOLOGY_DERIVABLE
, 'Y'
, 1
, 'N'
, 0
, '') IS_BARCODE_SYMBOLOGY_DERIVABLE
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.UNIT_LENGTH
, '') OUTOFBOX_DEPTH
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.DIMENSION_UOM_CODE
, '') UOM_OUTOFBOX_DEPTH
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.UNIT_HEIGHT
, '') OUTOFBOX_HEIGHT
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.DIMENSION_UOM_CODE
, '') UOM_OUTOFBOX_HEIGHT
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.UNIT_WIDTH
, '') OUTOFBOX_WIDTH
, DECODE(EGA.IS_OUT_OF_BOX_PROVIDED
, 'Y'
, MSI.DIMENSION_UOM_CODE
, '') UOM_OUTOFBOX_WIDTH
, DECODE(EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT
, 'Y'
, MSI.PRIMARY_UOM_CODE
, '') SELLING_UOM
, MSI.SECONDARY_UOM_CODE ORDERING_UOM
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_TL MST
, MTL_CROSS_REFERENCES MXR
, MTL_CATEGORIES_B_KFV MCKFV
, MTL_CATEGORIES_TL MCTL
, MTL_DEFAULT_CATEGORY_SETS MDC
, EGO_ITEM_GTN_ATTRS_B EGA
, EGO_ITEM_GTN_ATTRS_TL EGT
, MTL_PARAMETERS MP
, MTL_ITEM_CATEGORIES MIC
, EGO_UCCNET_EVENTS EVE
, (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.ORGANIZATION_ID = EGT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = EGT.INVENTORY_ITEM_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 MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
AND MDC.FUNCTIONAL_AREA_ID = 12
AND MCTL.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 MXR.UOM_CODE = MSI.PRIMARY_UOM_CODE
AND MXR.CROSS_REFERENCE_TYPE = 'GTIN'
AND MXR.CROSS_REFERENCE = EVE.GTIN
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