VIEW: APPS.AHL_MTL_ITEMS_EAM_V

Source

View Text - Preformatted

SELECT MAST.inventory_item_id, MAST.organization_id, hou.name, (select organization_code FROM mtl_parameters WHERE organization_id = MAST.organization_id) organization_code, MAST.item_type, MAST.concatenated_segments, MAST.description, MTL.revision_qty_control_code revision_qty_cntrl_code, MTL.lot_control_code, MTL.serial_number_control_code serial_nbr_cntrl_code, MTL.comms_nl_trackable_flag, MTL.service_item_flag, MTL.primary_uom_code, MTL.purchasing_enabled_flag, MTL.inventory_item_flag, MTL.wip_supply_type, MTL.build_in_wip_flag, MTL.eng_item_flag, MTL.eam_item_type, MTL.inventory_item_status_code, MTL.stock_enabled_flag, MTL.mtl_transactions_enabled_flag, MTL.outside_operation_flag, MTL.purchasing_item_flag, MTL.enabled_flag, MTL.start_date_active, MTL.end_date_active, MP.eam_enabled_flag FROM MTL_SYSTEM_ITEMS_KFV MTL, fnd_lookup_values_vl IT, MTL_PARAMETERS MP, hr_organization_units hou, MTL_SYSTEM_ITEMS_KFV MAST WHERE MTL.ORGANIZATION_ID = MP.ORGANIZATION_ID and MP.MASTER_ORGANIZATION_ID = MAST.ORGANIZATION_ID and MAST.inventory_item_id = MTL.inventory_item_id and MAST.ORGANIZATION_ID = hou.organization_id and IT.lookup_code (+) = MAST.item_type and IT.lookup_type (+) = 'ITEM_TYPE' and decode(AHL_UTIL_PKG.IS_PM_INSTALLED, 'Y', 'Y', MP.eam_enabled_flag) = 'Y' and trunc(sysdate) between trunc(nvl(MTL.start_date_active, sysdate)) and trunc(nvl(MTL.end_date_active, sysdate))

View Text - HTML Formatted

SELECT MAST.INVENTORY_ITEM_ID
, MAST.ORGANIZATION_ID
, HOU.NAME
, (SELECT ORGANIZATION_CODE
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = MAST.ORGANIZATION_ID) ORGANIZATION_CODE
, MAST.ITEM_TYPE
, MAST.CONCATENATED_SEGMENTS
, MAST.DESCRIPTION
, MTL.REVISION_QTY_CONTROL_CODE REVISION_QTY_CNTRL_CODE
, MTL.LOT_CONTROL_CODE
, MTL.SERIAL_NUMBER_CONTROL_CODE SERIAL_NBR_CNTRL_CODE
, MTL.COMMS_NL_TRACKABLE_FLAG
, MTL.SERVICE_ITEM_FLAG
, MTL.PRIMARY_UOM_CODE
, MTL.PURCHASING_ENABLED_FLAG
, MTL.INVENTORY_ITEM_FLAG
, MTL.WIP_SUPPLY_TYPE
, MTL.BUILD_IN_WIP_FLAG
, MTL.ENG_ITEM_FLAG
, MTL.EAM_ITEM_TYPE
, MTL.INVENTORY_ITEM_STATUS_CODE
, MTL.STOCK_ENABLED_FLAG
, MTL.MTL_TRANSACTIONS_ENABLED_FLAG
, MTL.OUTSIDE_OPERATION_FLAG
, MTL.PURCHASING_ITEM_FLAG
, MTL.ENABLED_FLAG
, MTL.START_DATE_ACTIVE
, MTL.END_DATE_ACTIVE
, MP.EAM_ENABLED_FLAG
FROM MTL_SYSTEM_ITEMS_KFV MTL
, FND_LOOKUP_VALUES_VL IT
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, MTL_SYSTEM_ITEMS_KFV MAST
WHERE MTL.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MASTER_ORGANIZATION_ID = MAST.ORGANIZATION_ID
AND MAST.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND MAST.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND IT.LOOKUP_CODE (+) = MAST.ITEM_TYPE
AND IT.LOOKUP_TYPE (+) = 'ITEM_TYPE'
AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED
, 'Y'
, 'Y'
, MP.EAM_ENABLED_FLAG) = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MTL.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(MTL.END_DATE_ACTIVE
, SYSDATE))