eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_EAM_ASSET_NUMBERS_ALL_V

Source

View Text - Preformatted


SELECT cii.rowid row_id, msn.inventory_item_id, msn.serial_number, nvl(cii.instance_description,msn.descriptive_text) as descriptive_text, cii.inv_subinventory_name current_subinventory_code, cii.inv_locator_id as current_locator_id, cii.network_asset_flag network_asset_flag, cii.context, cii.attribute1, cii.attribute2, cii.attribute3, cii.attribute4, cii.attribute5, cii.attribute6, cii.attribute7, cii.attribute8, cii.attribute9, cii.attribute10, cii.attribute11, cii.attribute12, cii.attribute13, cii.attribute14, cii.attribute15, cii.attribute16, cii.attribute17, cii.attribute18, cii.attribute19, cii.attribute20, cii.attribute21, cii.attribute22, cii.attribute23, cii.attribute24, cii.attribute25, cii.attribute26, cii.attribute27, cii.attribute28, cii.attribute29, cii.attribute30, mp.maint_organization_id current_organization_id, mp.organization_code inv_organization_code, msi.concatenated_segments, msi.eam_item_type, msi.description asset_group_description, cii.category_id category_id, mck.concatenated_segments category_name, cii.pn_location_id pn_location_id, pl.building||pl.floor||pl.office pn_location_name, pl.location_code pn_location_code, cii.asset_criticality_code asset_criticality_code, ml1.meaning asset_criticality, eomd.accounting_class_code wip_accounting_class_code, nvl(cii.maintainable_flag, 'Y') maintainable_flag, eomd.area_id, el.location_codes area, eomd.owning_department_id, bd.department_code owning_department, mp_prod.organization_code prod_organization_code, msn_prod.current_organization_id prod_organization_id, msi_prod.inventory_item_id equipment_item_id, decode(msi_prod.equipment_type,null,null,1,msn_prod.serial_number,null) eqp_serial_number, msn_prod.gen_object_id equipment_gen_object_id, cia.fa_asset_id, fa.asset_category_id, fa.asset_number fa_asset_number, msn_parent.inventory_item_id parent_item_id, msn_parent.serial_number parent_serial_number, msn_parent.gen_object_id parent_gen_object_id, cii2.instance_number parent_instance_number, cii.serial_number maintained_unit, cii.instance_number, nvl(cii.last_vld_organization_id, msn.current_organization_id) inv_organization_id, 3 maintenance_object_type, cii.instance_id maintenance_object_id, msn.gen_object_id, msn.current_status, cii.location_type_code, cii.location_id, decode(cii.location_type_code,'HZ_PARTY_SITES',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz,hz_party_sites hps WHERE hz.location_id = hps.location_id and hps.party_site_id = cii.location_id), 'HZ_LOCATIONS',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'VENDOR_SITE',(SELECT pvs.address_line1||','||pvs.address_line2||','||pvs.address_line3||','||pvs.city||','||pvs.state||','||pvs.zip||','||pvs.country FROM po_vendor_sites_all pvs WHERE pvs.vendor_site_id = cii.location_id), 'INVENTORY', (SELECT hrl.address_line_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.region_1||','||hrl.postal_code||','||hrl.country FROM hr_locations_all hrl WHERE hrl.location_id = cii.location_id), 'INTERNAL_SITE',(SELECT hrl.address_line_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.region_1||','||hrl.postal_code||','||hrl.country FROM hr_locations_all hrl WHERE hrl.location_id = cii.location_id), 'WIP',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'PROJECT',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'IN_TRANSIT',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'PO',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country from hz_locations hz where hz.location_id = cii.location_id), null) current_location, cii.CREATED_BY, cii.CREATION_DATE, cii.LAST_UPDATED_BY, cii.LAST_UPDATE_DATE, cii.LAST_UPDATE_LOGIN ,cii.supplier_warranty_exp_date ,cii.checkin_status ,cii.operational_log_flag ,ml2.meaning current_status_meaning ,cii.active_start_date ,cii.active_end_date ,cia.fa_sync_flag ,msn_parent.current_organization_id parent_organization_id ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT',GEO.INST_LATITUDE) LAT_DIRECTION ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS ,CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON',GEO.INST_LONGITUDE) LONG_DIRECTION FROM mtl_parameters mp, csi_item_instances cii, eam_org_maint_defaults eomd, csi_i_assets cia, mfg_lookups ml1, bom_departments bd, pn_locations_all pl, mtl_eam_locations el, mtl_categories_kfv mck, mtl_system_items_b_kfv msi, mtl_system_items msi_prod, mtl_serial_numbers msn_prod, mtl_parameters mp_prod, fa_additions_b fa, mtl_object_genealogy mog, mtl_serial_numbers msn, mtl_serial_numbers msn_parent, csi_item_instances cii2,mfg_lookups ml2, CSI_II_GEOLOCATIONS GEO WHERE mp.organization_id = msn.current_organization_id AND msn.current_organization_id = msi.organization_id AND msi.inventory_item_id = msn.inventory_item_id AND msi.eam_item_type in (1, 3) AND msi.serial_number_control_code <> 1 AND msn.inventory_item_id = cii.inventory_item_id(+) AND msn.serial_number = cii.serial_number(+) AND msn.gen_object_id = mog.object_id(+) AND cii.instance_id = eomd.object_id (+) AND eomd.object_type = 50 AND (eomd.organization_id = mp.maint_organization_id) AND cii.asset_criticality_code = ml1.lookup_code(+) AND ml1.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' AND eomd.owning_department_id = bd.department_id (+) AND cii.pn_location_id = pl.location_id(+) AND eomd.area_id = el.location_id(+) AND cii.category_id = mck.category_id(+) AND cii.equipment_gen_object_id = msn_prod.gen_object_id(+) AND msn_prod.current_organization_id = msi_prod.organization_id(+) AND msn_prod.inventory_item_id = msi_prod.inventory_item_id(+) AND msi_prod.organization_id = mp_prod.organization_id(+) AND msi_prod.equipment_type(+) = 1 AND cii.instance_id = cia.instance_id(+) AND cia.fa_asset_id = fa.asset_id(+) AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+) AND GEO.VALID_FLAG(+) ='Y' AND sysdate >= nvl(cia.active_start_date(+),sysdate) AND sysdate <= nvl(cia.active_end_date(+),sysdate) AND mog.parent_object_id = msn_parent.gen_object_id(+) AND mog.genealogy_type(+) = 5 AND sysdate >= nvl(mog.start_date_active(+), sysdate) AND sysdate <= nvl(mog.end_date_active(+), sysdate) AND msn_parent.inventory_item_id = cii2.inventory_item_id (+) AND msn_parent.serial_number = cii2.serial_number (+) AND ml2.lookup_type(+) = 'SERIAL_NUM_STATUS' AND ml2.lookup_code(+) = MSN.CURRENT_STATUS UNION ALL SELECT cii.rowid row_id, msn.inventory_item_id, msn.serial_number, nvl(cii.instance_description, msn.descriptive_text) as descriptive_text, cii.inv_subinventory_name current_subinventory_code, cii.inv_locator_id as current_locator_id, cii.network_asset_flag network_asset_flag, cii.context, cii.attribute1, cii.attribute2, cii.attribute3, cii.attribute4, cii.attribute5, cii.attribute6, cii.attribute7, cii.attribute8, cii.attribute9, cii.attribute10, cii.attribute11, cii.attribute12, cii.attribute13, cii.attribute14, cii.attribute15, cii.attribute16, cii.attribute17, cii.attribute18, cii.attribute19, cii.attribute20, cii.attribute21, cii.attribute22, cii.attribute23, cii.attribute24, cii.attribute25, cii.attribute26, cii.attribute27, cii.attribute28, cii.attribute29, cii.attribute30, mp.maint_organization_id current_organization_id, mp.organization_code inv_organization_code, msi.concatenated_segments, msi.eam_item_type, msi.description asset_group_description, cii.category_id category_id, mck.concatenated_segments category_name, cii.pn_location_id pn_location_id, pl.building||pl.floor||pl.office pn_location_name, pl.location_code pn_location_code, cii.asset_criticality_code asset_criticality_code, ml1.meaning asset_criticality, null wip_accounting_class_code, nvl(cii.maintainable_flag, 'Y') maintainable_flag, null, null area, null, null owning_department, mp_prod.organization_code prod_organization_code, msn_prod.current_organization_id prod_organization_id, msi_prod.inventory_item_id equipment_item_id, decode(msi_prod.equipment_type,null,null,1,msn_prod.serial_number,null) eqp_serial_number, msn_prod.gen_object_id equipment_gen_object_id, cia.fa_asset_id, fa.asset_category_id, fa.asset_number fa_asset_number, msn_parent.inventory_item_id parent_item_id, msn_parent.serial_number parent_serial_number, msn_parent.gen_object_id parent_gen_object_id,cii2.instance_number parent_instance_number, cii.serial_number maintained_unit, cii.instance_number, nvl(cii.last_vld_organization_id, msn.current_organization_id) inv_organization_id, 3 maintenance_object_type, cii.instance_id maintenance_object_id, msn.gen_object_id, msn.current_status, cii.location_type_code, cii.location_id, decode(cii.location_type_code, 'HZ_PARTY_SITES',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz,hz_party_sites hps WHERE hz.location_id = hps.location_id and hps.party_site_id = cii.location_id), 'HZ_LOCATIONS',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'VENDOR_SITE',(SELECT pvs.address_line1||','||pvs.address_line2||','||pvs.address_line3||','||pvs.city||','||pvs.state||','||pvs.zip||','||pvs.country FROM po_vendor_sites_all pvs WHERE pvs.vendor_site_id = cii.location_id), 'INVENTORY', (SELECT hrl.address_line_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.region_1||','||hrl.postal_code||','||hrl.country FROM hr_locations_all hrl WHERE hrl.location_id = cii.location_id), 'INTERNAL_SITE',(SELECT hrl.address_line_1||','||hrl.address_line_2||','||hrl.address_line_3||','||hrl.region_1||','||hrl.postal_code||','||hrl.country FROM hr_locations_all hrl WHERE hrl.location_id = cii.location_id), 'WIP',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'PROJECT',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'IN_TRANSIT',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country FROM hz_locations hz WHERE hz.location_id = cii.location_id), 'PO',(SELECT hz.address1||','||hz.address2||','||hz.address3||','||hz.address4||','||hz.city||','||hz.state||','||hz.postal_code||','||hz.country from hz_locations hz where hz.location_id = cii.location_id), null) current_location, cii.CREATED_BY, cii.CREATION_DATE, cii.LAST_UPDATED_BY, cii.LAST_UPDATE_DATE, cii.LAST_UPDATE_LOGIN, cii.supplier_warranty_exp_date, cii.checkin_status, cii.operational_log_flag, ml2.meaning current_status_meaning,cii.active_start_date,cii.active_end_date, cia.fa_sync_flag,msn_parent.current_organization_id parent_organization_id, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT',GEO.INST_LATITUDE) LAT_DIRECTION, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON',GEO.INST_LONGITUDE) LONG_DIRECTION FROM mtl_parameters mp, csi_item_instances cii, csi_i_assets cia, mfg_lookups ml1, pn_locations_all pl, mtl_categories_kfv mck, mtl_system_items_b_kfv msi, mtl_serial_numbers msn_prod, mtl_system_items msi_prod,mtl_parameters mp_prod, fa_additions_b fa, mtl_object_genealogy mog, mtl_serial_numbers msn, mtl_serial_numbers msn_parent, csi_item_instances cii2, mfg_lookups ml2, CSI_II_GEOLOCATIONS GEO WHERE mp.organization_id = msn.current_organization_id AND msn.current_organization_id = msi.organization_id AND msi.inventory_item_id = msn.inventory_item_id AND msi.eam_item_type in (1, 3) AND msi.serial_number_control_code <>1 AND msn.inventory_item_id = cii.inventory_item_id(+) AND msn.serial_number = cii.serial_number(+) AND msn.gen_object_id = mog.object_id(+) AND cii.asset_criticality_code = ml1.lookup_code(+) AND ml1.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY' AND cii.pn_location_id = pl.location_id(+) AND cii.category_id = mck.category_id(+) AND cii.equipment_gen_object_id = msn_prod.gen_object_id(+) AND msn_prod.current_organization_id = msi_prod.organization_id(+) AND msn_prod.inventory_item_id = msi_prod.inventory_item_id(+) AND msi_prod.organization_id = mp_prod.organization_id(+) AND msi_prod.equipment_type(+) = 1 AND cii.instance_id = cia.instance_id(+) AND cia.fa_asset_id = fa.asset_id(+) AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+) AND GEO.VALID_FLAG(+) ='Y' AND sysdate >= nvl(cia.active_start_date(+),sysdate) AND sysdate <= nvl(cia.active_end_date(+),sysdate) AND mog.parent_object_id = msn_parent.gen_object_id(+) AND mog.genealogy_type(+) = 5 AND sysdate >= nvl(mog.start_date_active(+), sysdate) AND sysdate <= nvl(mog.end_date_active(+), sysdate) AND msn_parent.inventory_item_id = cii2.inventory_item_id (+) AND msn_parent.serial_number = cii2.serial_number (+) AND NOT EXISTS (SELECT 1 FROM eam_org_maint_defaults eomd WHERE eomd.object_id = cii.instance_id AND eomd.organization_id = mp.maint_organization_id AND eomd.object_type = 50) AND ml2.lookup_type(+) = 'SERIAL_NUM_STATUS' AND ml2.lookup_code(+) = MSN.CURRENT_STATUS

View Text - HTML Formatted

SELECT CII.ROWID ROW_ID
, MSN.INVENTORY_ITEM_ID
, MSN.SERIAL_NUMBER
, NVL(CII.INSTANCE_DESCRIPTION
, MSN.DESCRIPTIVE_TEXT) AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MP.ORGANIZATION_CODE INV_ORGANIZATION_CODE
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML1.MEANING ASSET_CRITICALITY
, EOMD.ACCOUNTING_CLASS_CODE WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, EOMD.AREA_ID
, EL.LOCATION_CODES AREA
, EOMD.OWNING_DEPARTMENT_ID
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, MP_PROD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, MSN_PROD.GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID
, CII2.INSTANCE_NUMBER PARENT_INSTANCE_NUMBER
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, NVL(CII.LAST_VLD_ORGANIZATION_ID
, MSN.CURRENT_ORGANIZATION_ID) INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.LOCATION_TYPE_CODE
, CII.LOCATION_ID
, DECODE(CII.LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CII.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT PVS.ADDRESS_LINE1||'
, '||PVS.ADDRESS_LINE2||'
, '||PVS.ADDRESS_LINE3||'
, '||PVS.CITY||'
, '||PVS.STATE||'
, '||PVS.ZIP||'
, '||PVS.COUNTRY
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CII.LOCATION_ID)
, 'INVENTORY'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'WIP'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PROJECT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PO'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, NULL) CURRENT_LOCATION
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.CHECKIN_STATUS
, CII.OPERATIONAL_LOG_FLAG
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CIA.FA_SYNC_FLAG
, MSN_PARENT.CURRENT_ORGANIZATION_ID PARENT_ORGANIZATION_ID
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT'
, GEO.INST_LATITUDE) LAT_DIRECTION
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON'
, GEO.INST_LONGITUDE) LONG_DIRECTION
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, EAM_ORG_MAINT_DEFAULTS EOMD
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML1
, BOM_DEPARTMENTS BD
, PN_LOCATIONS_ALL PL
, MTL_EAM_LOCATIONS EL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SYSTEM_ITEMS MSI_PROD
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_PARAMETERS MP_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, CSI_ITEM_INSTANCES CII2
, MFG_LOOKUPS ML2
, CSI_II_GEOLOCATIONS GEO
WHERE MP.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND MSN.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID(+)
AND MSN.SERIAL_NUMBER = CII.SERIAL_NUMBER(+)
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND CII.INSTANCE_ID = EOMD.OBJECT_ID (+)
AND EOMD.OBJECT_TYPE = 50
AND (EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID)
AND CII.ASSET_CRITICALITY_CODE = ML1.LOOKUP_CODE(+)
AND ML1.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID (+)
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND EOMD.AREA_ID = EL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.ORGANIZATION_ID = MP_PROD.ORGANIZATION_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+)
AND GEO.VALID_FLAG(+) ='Y'
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
AND MSN_PARENT.INVENTORY_ITEM_ID = CII2.INVENTORY_ITEM_ID (+)
AND MSN_PARENT.SERIAL_NUMBER = CII2.SERIAL_NUMBER (+)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS UNION ALL SELECT CII.ROWID ROW_ID
, MSN.INVENTORY_ITEM_ID
, MSN.SERIAL_NUMBER
, NVL(CII.INSTANCE_DESCRIPTION
, MSN.DESCRIPTIVE_TEXT) AS DESCRIPTIVE_TEXT
, CII.INV_SUBINVENTORY_NAME CURRENT_SUBINVENTORY_CODE
, CII.INV_LOCATOR_ID AS CURRENT_LOCATOR_ID
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, CII.CONTEXT
, CII.ATTRIBUTE1
, CII.ATTRIBUTE2
, CII.ATTRIBUTE3
, CII.ATTRIBUTE4
, CII.ATTRIBUTE5
, CII.ATTRIBUTE6
, CII.ATTRIBUTE7
, CII.ATTRIBUTE8
, CII.ATTRIBUTE9
, CII.ATTRIBUTE10
, CII.ATTRIBUTE11
, CII.ATTRIBUTE12
, CII.ATTRIBUTE13
, CII.ATTRIBUTE14
, CII.ATTRIBUTE15
, CII.ATTRIBUTE16
, CII.ATTRIBUTE17
, CII.ATTRIBUTE18
, CII.ATTRIBUTE19
, CII.ATTRIBUTE20
, CII.ATTRIBUTE21
, CII.ATTRIBUTE22
, CII.ATTRIBUTE23
, CII.ATTRIBUTE24
, CII.ATTRIBUTE25
, CII.ATTRIBUTE26
, CII.ATTRIBUTE27
, CII.ATTRIBUTE28
, CII.ATTRIBUTE29
, CII.ATTRIBUTE30
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MP.ORGANIZATION_CODE INV_ORGANIZATION_CODE
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, CII.CATEGORY_ID CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.PN_LOCATION_ID PN_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE PN_LOCATION_NAME
, PL.LOCATION_CODE PN_LOCATION_CODE
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY_CODE
, ML1.MEANING ASSET_CRITICALITY
, NULL WIP_ACCOUNTING_CLASS_CODE
, NVL(CII.MAINTAINABLE_FLAG
, 'Y') MAINTAINABLE_FLAG
, NULL
, NULL AREA
, NULL
, NULL OWNING_DEPARTMENT
, MP_PROD.ORGANIZATION_CODE PROD_ORGANIZATION_CODE
, MSN_PROD.CURRENT_ORGANIZATION_ID PROD_ORGANIZATION_ID
, MSI_PROD.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID
, DECODE(MSI_PROD.EQUIPMENT_TYPE
, NULL
, NULL
, 1
, MSN_PROD.SERIAL_NUMBER
, NULL) EQP_SERIAL_NUMBER
, MSN_PROD.GEN_OBJECT_ID EQUIPMENT_GEN_OBJECT_ID
, CIA.FA_ASSET_ID
, FA.ASSET_CATEGORY_ID
, FA.ASSET_NUMBER FA_ASSET_NUMBER
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID
, CII2.INSTANCE_NUMBER PARENT_INSTANCE_NUMBER
, CII.SERIAL_NUMBER MAINTAINED_UNIT
, CII.INSTANCE_NUMBER
, NVL(CII.LAST_VLD_ORGANIZATION_ID
, MSN.CURRENT_ORGANIZATION_ID) INV_ORGANIZATION_ID
, 3 MAINTENANCE_OBJECT_TYPE
, CII.INSTANCE_ID MAINTENANCE_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.LOCATION_TYPE_CODE
, CII.LOCATION_ID
, DECODE(CII.LOCATION_TYPE_CODE
, 'HZ_PARTY_SITES'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
, HZ_PARTY_SITES HPS
WHERE HZ.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = CII.LOCATION_ID)
, 'HZ_LOCATIONS'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'VENDOR_SITE'
, (SELECT PVS.ADDRESS_LINE1||'
, '||PVS.ADDRESS_LINE2||'
, '||PVS.ADDRESS_LINE3||'
, '||PVS.CITY||'
, '||PVS.STATE||'
, '||PVS.ZIP||'
, '||PVS.COUNTRY
FROM PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_SITE_ID = CII.LOCATION_ID)
, 'INVENTORY'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'INTERNAL_SITE'
, (SELECT HRL.ADDRESS_LINE_1||'
, '||HRL.ADDRESS_LINE_2||'
, '||HRL.ADDRESS_LINE_3||'
, '||HRL.REGION_1||'
, '||HRL.POSTAL_CODE||'
, '||HRL.COUNTRY
FROM HR_LOCATIONS_ALL HRL
WHERE HRL.LOCATION_ID = CII.LOCATION_ID)
, 'WIP'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PROJECT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'IN_TRANSIT'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, 'PO'
, (SELECT HZ.ADDRESS1||'
, '||HZ.ADDRESS2||'
, '||HZ.ADDRESS3||'
, '||HZ.ADDRESS4||'
, '||HZ.CITY||'
, '||HZ.STATE||'
, '||HZ.POSTAL_CODE||'
, '||HZ.COUNTRY
FROM HZ_LOCATIONS HZ
WHERE HZ.LOCATION_ID = CII.LOCATION_ID)
, NULL) CURRENT_LOCATION
, CII.CREATED_BY
, CII.CREATION_DATE
, CII.LAST_UPDATED_BY
, CII.LAST_UPDATE_DATE
, CII.LAST_UPDATE_LOGIN
, CII.SUPPLIER_WARRANTY_EXP_DATE
, CII.CHECKIN_STATUS
, CII.OPERATIONAL_LOG_FLAG
, ML2.MEANING CURRENT_STATUS_MEANING
, CII.ACTIVE_START_DATE
, CII.ACTIVE_END_DATE
, CIA.FA_SYNC_FLAG
, MSN_PARENT.CURRENT_ORGANIZATION_ID PARENT_ORGANIZATION_ID
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LATITUDE) LAT_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LATITUDE) LAT_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LATITUDE) LAT_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LAT'
, GEO.INST_LATITUDE) LAT_DIRECTION
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DEGREES_FROM_DD(GEO.INST_LONGITUDE) LONG_DEGREES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_MINUTES_FROM_DD(GEO.INST_LONGITUDE) LONG_MINUTES
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_SECONDS_FROM_DD(GEO.INST_LONGITUDE) LONG_SECONDS
, CSI_GIS_INSTANCE_LOC_UTL_PKG.GET_DIRECTION_FROM_DD('LON'
, GEO.INST_LONGITUDE) LONG_DIRECTION
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, CSI_I_ASSETS CIA
, MFG_LOOKUPS ML1
, PN_LOCATIONS_ALL PL
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SERIAL_NUMBERS MSN_PROD
, MTL_SYSTEM_ITEMS MSI_PROD
, MTL_PARAMETERS MP_PROD
, FA_ADDITIONS_B FA
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
, CSI_ITEM_INSTANCES CII2
, MFG_LOOKUPS ML2
, CSI_II_GEOLOCATIONS GEO
WHERE MP.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSN.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND MSI.SERIAL_NUMBER_CONTROL_CODE <>1
AND MSN.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID(+)
AND MSN.SERIAL_NUMBER = CII.SERIAL_NUMBER(+)
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND CII.ASSET_CRITICALITY_CODE = ML1.LOOKUP_CODE(+)
AND ML1.LOOKUP_TYPE(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.EQUIPMENT_GEN_OBJECT_ID = MSN_PROD.GEN_OBJECT_ID(+)
AND MSN_PROD.CURRENT_ORGANIZATION_ID = MSI_PROD.ORGANIZATION_ID(+)
AND MSN_PROD.INVENTORY_ITEM_ID = MSI_PROD.INVENTORY_ITEM_ID(+)
AND MSI_PROD.ORGANIZATION_ID = MP_PROD.ORGANIZATION_ID(+)
AND MSI_PROD.EQUIPMENT_TYPE(+) = 1
AND CII.INSTANCE_ID = CIA.INSTANCE_ID(+)
AND CIA.FA_ASSET_ID = FA.ASSET_ID(+)
AND CII.INSTANCE_ID = GEO.INSTANCE_ID(+)
AND GEO.VALID_FLAG(+) ='Y'
AND SYSDATE >= NVL(CIA.ACTIVE_START_DATE(+)
, SYSDATE)
AND SYSDATE <= NVL(CIA.ACTIVE_END_DATE(+)
, SYSDATE)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
AND MSN_PARENT.INVENTORY_ITEM_ID = CII2.INVENTORY_ITEM_ID (+)
AND MSN_PARENT.SERIAL_NUMBER = CII2.SERIAL_NUMBER (+)
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50)
AND ML2.LOOKUP_TYPE(+) = 'SERIAL_NUM_STATUS'
AND ML2.LOOKUP_CODE(+) = MSN.CURRENT_STATUS