Product: | AHL - Complex Maintenance Repair and Overhaul |
---|---|
Description: | Item Composition Details V joins Item Groups, UOm Class and MTL table for Name and Description |
Implementation/DBA Data: | APPS.AHL_ITEM_COMP_DETAIL_V |
SELECT DISTINCT ICD.ROWID ROW_ID
, ICD.ITEM_COMP_DETAIL_ID ITEM_COMP_DETAIL_ID
, ICD.ITEM_COMPOSITION_ID ITEM_COMPOSITION_ID
, ICD.ITEM_GROUP_ID ITEM_GROUP_ID
, IGVL.NAME ITEM_GROUP_NAME
, ICD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS ITEM_NAME
, DECODE(ICD.ITEM_GROUP_ID
, NULL
, MTL.DESCRIPTION
, IGVL.DESCRIPTION) ITEM_DESCRIPTION
, ICD.INVENTORY_MASTER_ORG_ID INVENTORY_MASTER_ORG_ID
, MTL.ORGANIZATION_NAME ORGANIZATION_NAME
, MTL.ORGANIZATION_CODE ORGANIZATION_CODE
, ICD.UOM_CODE UOM_CODE
, ICUV.UNIT_OF_MEASURE UOM_MEANING
, ICD.QUANTITY QUANTITY
, ICD.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, ICD.LINK_COMP_DETL_ID LINK_COMP_DETL_ID
, ICD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ICD.LAST_UPDATED_BY LAST_UPDATED_BY
, ICD.CREATION_DATE CREATION_DATE
, ICD.CREATED_BY CREATED_BY
, ICD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ICD.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, ICD.SECURITY_GROUP_ID SECURITY_GROUP_ID
, ICD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, ICD.ATTRIBUTE1 ATTRIBUTE1
, ICD.ATTRIBUTE2 ATTRIBUTE2
, ICD.ATTRIBUTE3 ATTRIBUTE3
, ICD.ATTRIBUTE4 ATTRIBUTE4
, ICD.ATTRIBUTE5 ATTRIBUTE5
, ICD.ATTRIBUTE6 ATTRIBUTE6
, ICD.ATTRIBUTE7 ATTRIBUTE7
, ICD.ATTRIBUTE8 ATTRIBUTE8
, ICD.ATTRIBUTE9 ATTRIBUTE9
, ICD.ATTRIBUTE10 ATTRIBUTE10
, ICD.ATTRIBUTE11 ATTRIBUTE11
, ICD.ATTRIBUTE12 ATTRIBUTE12
, ICD.ATTRIBUTE13 ATTRIBUTE13
, ICD.ATTRIBUTE14 ATTRIBUTE14
, ICD.ATTRIBUTE15 ATTRIBUTE15
FROM AHL_MTL_ITEMS_NON_OU_V MTL
, AHL_ITEM_COMP_DETAILS ICD
, AHL_ITEM_GROUPS_V IGVL
, AHL_ITEM_CLASS_UOM_V ICUV
WHERE ICD.INVENTORY_ITEM_ID= MTL.INVENTORY_ITEM_ID(+)
AND ICD.INVENTORY_MASTER_ORG_ID = MTL.INVENTORY_ORG_ID(+)
AND ICD.ITEM_GROUP_ID = IGVL.ITEM_GROUP_ID(+)
AND NVL(ICD.INVENTORY_ITEM_ID
, 123) = NVL2(ICD.INVENTORY_ITEM_ID
, ICUV.INVENTORY_ITEM_ID(+)
, 123)
AND NVL(ICD.INVENTORY_MASTER_ORG_ID
, 123) =NVL2(ICD.INVENTORY_MASTER_ORG_ID
, ICUV.INVENTORY_ORG_ID(+)
, 123)
AND ICD.UOM_CODE = ICUV.UOM_CODE (+)