VIEW: APPS.MTL_EAM_ASSET_NUM_INTERFACE_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'