View: EGO_GTIN_UCCNET_HIER_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 CIN messages.
Implementation/DBA Data:	APPS.EGO_GTIN_UCCNET_HIER_ATTRS_V

View Text

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

Columns