View: MTL_EAM_ASSET_NUM_INTERFACE_V

Product:	INV - Inventory
Description:	Asset Number Interface View
Implementation/DBA Data:	APPS.MTL_EAM_ASSET_NUM_INTERFACE_V

View Text

SELECT MEANI.INTERFACE_GROUP_ID
, MEANI.INTERFACE_HEADER_ID
, MEANI.BATCH_ID
, MEANI.BATCH_NAME
, ML2.MEANING PROCESS_FLAG_CODE
, MEANI.PROCESS_FLAG
, MEANI.INVENTORY_ITEM_ID
, MEANI.SERIAL_NUMBER
, MEANI.LAST_UPDATE_DATE
, MEANI.LAST_UPDATED_BY
, MEANI.CREATION_DATE
, MEANI.CREATED_BY
, MEANI.LAST_UPDATE_LOGIN
, MEANI.REQUEST_ID
, MEANI.PROGRAM_APPLICATION_ID
, MEANI.PROGRAM_ID
, MEANI.PROGRAM_UPDATE_DATE
, MEANI.PARENT_INVENTORY_ITEM_ID
, MEANI.PARENT_SERIAL_NUMBER
, MEANI.PARENT_INSTANCE_NUMBER
, MEANI.DESCRIPTIVE_TEXT
, MEANI.CURRENT_ORGANIZATION_ID
, MEANI.WIP_ACCOUNTING_CLASS_CODE
, MEANI.MAINTAINABLE_FLAG
, MEANI.OWNING_DEPARTMENT_ID
, MEANI.NETWORK_ASSET_FLAG
, MEANI.FA_ASSET_ID
, MEANI.PN_LOCATION_ID
, PL.LOCATION_ALIAS PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, MEANI.EAM_LOCATION_ID
, DECODE(MEANI.ASSET_CRITICALITY_ID
, NULL
, MEANI.ASSET_CRITICALITY_CODE
, ML.MEANING) ASSET_CRITICALITY_CODE
, MEANI.CATEGORY_ID
, MEANI.ERROR_CODE
, MEANI.ERROR_MESSAGE
, DECODE(MEANI.ORGANIZATION_CODE
, NULL
, MP.ORGANIZATION_CODE
, MEANI.ORGANIZATION_CODE) ORGANIZATION_CODE
, MEANI.EQP_SERIAL_NUMBER
, MEANI.EQUIPMENT_ITEM_ID
, MEANI.PROD_ORGANIZATION_ID
, MEANI.FA_ASSET_NUMBER
, DECODE(MEANI.EAM_LOCATION_ID
, NULL
, MEANI.LOCATION_CODES
, MEL.LOCATION_CODES) LOCATION_CODES
, ML3.MEANING IMPORT_MODE_CODE
, MEANI.IMPORT_MODE
, ML4.MEANING IMPORT_SCOPE_CODE
, MEANI.IMPORT_SCOPE
, DECODE(MEANI.OWNING_DEPARTMENT_ID
, NULL
, MEANI.OWNING_DEPARTMENT_CODE
, BD.DEPARTMENT_CODE) OWNING_DEPARTMENT_CODE
, MEANI.ASSET_CRITICALITY_ID
, MEANI.CURRENT_STATUS
, MEANI.PROD_ORGANIZATION_CODE
, MEANI.INSTANCE_NUMBER
, MEANI.ACTIVE_START_DATE
, MEANI.ACTIVE_END_DATE
, MEANI.SUPPLIER_WARRANTY_EXP_DATE
, MEANI.CHECKIN_STATUS
, MEANI.OPERATIONAL_LOG_FLAG
FROM MTL_EAM_ASSET_NUM_INTERFACE MEANI
, PN_LOCATIONS_ALL PL
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML
, MTL_PARAMETERS MP
, MTL_EAM_LOCATIONS MEL
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
WHERE (MEANI.PROCESS_FLAG = 'P' OR MEANI.PROCESS_FLAG = 'E' OR MEANI.PROCESS_FLAG = 'R')
AND BD.ORGANIZATION_ID (+) = MEANI.CURRENT_ORGANIZATION_ID
AND MEANI.CURRENT_ORGANIZATION_ID IS NOT NULL
AND BD.DEPARTMENT_ID = DECODE( EAM_COMMON_UTILITIES_PVT.GET_DEPT_ID(MEANI.ORGANIZATION_CODE
, MEANI.CURRENT_ORGANIZATION_ID
, MEANI.OWNING_DEPARTMENT_CODE
, MEANI.OWNING_DEPARTMENT_ID)
, NULL
, BD.DEPARTMENT_ID
, EAM_COMMON_UTILITIES_PVT.GET_DEPT_ID(MEANI.ORGANIZATION_CODE
, MEANI.CURRENT_ORGANIZATION_ID
, MEANI.OWNING_DEPARTMENT_CODE
, MEANI.OWNING_DEPARTMENT_ID))
AND MEANI.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND SYSDATE >= NVL(PL.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(PL.ACTIVE_END_DATE(+)
, SYSDATE)
AND MEL.LOCATION_ID (+) = MEANI.EAM_LOCATION_ID
AND MP.ORGANIZATION_ID = MEANI.CURRENT_ORGANIZATION_ID
AND ML.LOOKUP_CODE(+) = MEANI.ASSET_CRITICALITY_ID
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND ML2.LOOKUP_CODE(+) = DECODE(MEANI.PROCESS_FLAG
, 'P'
, 0
, 'R'
, 1
, 'E'
, 2
, 3)
AND ML2.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_STATUS'
AND ML3.LOOKUP_CODE(+) = MEANI.IMPORT_MODE
AND ML3.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_MODE'
AND ML4.LOOKUP_CODE(+) = MEANI.IMPORT_SCOPE
AND ML4.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_SCOPE' UNION SELECT MEANI.INTERFACE_GROUP_ID
, MEANI.INTERFACE_HEADER_ID
, MEANI.BATCH_ID
, MEANI.BATCH_NAME
, ML2.MEANING PROCESS_FLAG_CODE
, MEANI.PROCESS_FLAG
, MEANI.INVENTORY_ITEM_ID
, MEANI.SERIAL_NUMBER
, MEANI.LAST_UPDATE_DATE
, MEANI.LAST_UPDATED_BY
, MEANI.CREATION_DATE
, MEANI.CREATED_BY
, MEANI.LAST_UPDATE_LOGIN
, MEANI.REQUEST_ID
, MEANI.PROGRAM_APPLICATION_ID
, MEANI.PROGRAM_ID
, MEANI.PROGRAM_UPDATE_DATE
, MEANI.PARENT_INVENTORY_ITEM_ID
, MEANI.PARENT_SERIAL_NUMBER
, MEANI.PARENT_INSTANCE_NUMBER
, MEANI.DESCRIPTIVE_TEXT
, MP.ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MEANI.WIP_ACCOUNTING_CLASS_CODE
, MEANI.MAINTAINABLE_FLAG
, MEANI.OWNING_DEPARTMENT_ID
, MEANI.NETWORK_ASSET_FLAG
, MEANI.FA_ASSET_ID
, MEANI.PN_LOCATION_ID
, PL.LOCATION_ALIAS PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, MEANI.EAM_LOCATION_ID
, DECODE(MEANI.ASSET_CRITICALITY_ID
, NULL
, MEANI.ASSET_CRITICALITY_CODE
, ML.MEANING) ASSET_CRITICALITY_CODE
, MEANI.CATEGORY_ID
, MEANI.ERROR_CODE
, MEANI.ERROR_MESSAGE
, MEANI.ORGANIZATION_CODE
, MEANI.EQP_SERIAL_NUMBER
, MEANI.EQUIPMENT_ITEM_ID
, MEANI.PROD_ORGANIZATION_ID
, MEANI.FA_ASSET_NUMBER
, DECODE(MEANI.EAM_LOCATION_ID
, NULL
, MEANI.LOCATION_CODES
, MEL.LOCATION_CODES) LOCATION_CODES
, ML3.MEANING IMPORT_MODE_CODE
, MEANI.IMPORT_MODE
, ML4.MEANING IMPORT_SCOPE_CODE
, MEANI.IMPORT_SCOPE
, DECODE(MEANI.OWNING_DEPARTMENT_ID
, NULL
, MEANI.OWNING_DEPARTMENT_CODE
, BD.DEPARTMENT_CODE) OWNING_DEPARTMENT_CODE
, MEANI.ASSET_CRITICALITY_ID
, MEANI.CURRENT_STATUS
, MEANI.PROD_ORGANIZATION_CODE
, MEANI.INSTANCE_NUMBER
, MEANI.ACTIVE_START_DATE
, MEANI.ACTIVE_END_DATE
, MEANI.SUPPLIER_WARRANTY_EXP_DATE
, MEANI.CHECKIN_STATUS
, MEANI.OPERATIONAL_LOG_FLAG
FROM MTL_EAM_ASSET_NUM_INTERFACE MEANI
, PN_LOCATIONS_ALL PL
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML
, MTL_EAM_LOCATIONS MEL
, MTL_PARAMETERS MP
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
WHERE (MEANI.PROCESS_FLAG = 'P' OR MEANI.PROCESS_FLAG = 'E' OR MEANI.PROCESS_FLAG = 'R')
AND MEANI.CURRENT_ORGANIZATION_ID IS NULL
AND MEANI.ORGANIZATION_CODE IS NOT NULL
AND MP.ORGANIZATION_CODE (+) = MEANI.ORGANIZATION_CODE
AND BD.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = DECODE(EAM_COMMON_UTILITIES_PVT.GET_DEPT_ID(MEANI.ORGANIZATION_CODE
, MEANI.CURRENT_ORGANIZATION_ID
, MEANI.OWNING_DEPARTMENT_CODE
, MEANI.OWNING_DEPARTMENT_ID)
, NULL
, BD.DEPARTMENT_ID
, EAM_COMMON_UTILITIES_PVT.GET_DEPT_ID(MEANI.ORGANIZATION_CODE
, MEANI.CURRENT_ORGANIZATION_ID
, MEANI.OWNING_DEPARTMENT_CODE
, MEANI.OWNING_DEPARTMENT_ID))
AND MEANI.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND SYSDATE >= NVL(PL.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(PL.ACTIVE_END_DATE(+)
, SYSDATE)
AND MEL.LOCATION_ID (+) = MEANI.EAM_LOCATION_ID
AND ML.LOOKUP_CODE(+) = MEANI.ASSET_CRITICALITY_ID
AND ML.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND ML2.LOOKUP_CODE(+) = DECODE(MEANI.PROCESS_FLAG
, 'P'
, 0
, 'R'
, 1
, 'E'
, 2
, 3)
AND ML2.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_STATUS'
AND ML3.LOOKUP_CODE(+) = MEANI.IMPORT_MODE
AND ML3.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_MODE'
AND ML4.LOOKUP_CODE(+) = MEANI.IMPORT_SCOPE
AND ML4.LOOKUP_TYPE(+) = 'EAM_ASSET_IMPORT_SCOPE'

Columns