VIEW: APPS.EAM_ASSET_METERS_V

Source

View Text - Preformatted

SELECT cca.rowid, cca.instance_association_id as association_id, ec.initial_reading, cca.counter_id meter_id, cii.last_vld_organization_id organization_id, cii.inventory_item_id asset_group_id, cii.instance_number asset_number, cct.name meter_name, ec.reading_type meter_type, ec.uom_code meter_uom, ec.direction value_change_dir, ec.eam_required_flag, cca.primary_failure_flag, ec.used_in_scheduling, ec.default_usage_rate user_defined_rate, ec.use_past_reading, cct.description description, ec.start_date_active from_effective_date, ec.end_date_active to_effective_date, cca.created_by, cca.creation_date, cca.last_update_login, cca.last_update_date, cca.last_updated_by, cca.attribute_category, cca.attribute1 attribute1, cca.attribute2 attribute2, cca.attribute3 attribute3, cca.attribute4 attribute4, cca.attribute5 attribute5, cca.attribute6 attribute6, cca.attribute7 attribute7, cca.attribute8 attribute8, cca.attribute9 attribute9, cca.attribute10 attribute10, cca.attribute11 attribute11, cca.attribute12 attribute12, cca.attribute13 attribute13, cca.attribute14 attribute14, cca.attribute15 attribute15, TO_NUMBER(3) maintenance_object_type, cca.source_object_id maintenance_object_id, to_number(null) creation_organization_id, to_char('N') tmpl_flag, ec.created_from_counter_tmpl_id source_tmpl_id, msik.eam_item_type eam_item_type, msik.concatenated_segments asset_group, mp.organization_code, cii.instance_description as asset_description, cct1.name source_meter_name, ccr.factor, ccr.active_start_date relationship_start_date, ec.initial_reading_date, cca.maint_organization_id FROM csi_counters_b ec, csi_counters_tl cct, csi_counter_associations cca, csi_item_instances cii, mtl_parameters mp, csi_counter_relationships ccr, csi_counters_tl cct1, MTL_SYSTEM_ITEMS_B_KFV msik WHERE cca.counter_id = ec.counter_id and ec.counter_id = cct.counter_id and cct.language = userenv('LANG') and ec.counter_id = ccr.object_counter_id (+) and ccr.relationship_type_code(+) = 'CONFIGURATION' and cca.source_object_id = cii.instance_id and mp.organization_id = cii.last_vld_organization_id and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1) and SYSDATE BETWEEN nvl(ec.start_date_active, SYSDATE-1) AND nvl(ec.end_date_active, SYSDATE+1) and SYSDATE BETWEEN nvl(ccr.active_start_date(+), SYSDATE-1) AND nvl(ccr.active_end_date(+), SYSDATE+1) and cct1.counter_id (+) = ccr.source_counter_id and cct1.language (+) = userenv('LANG') and ec.counter_type = 'REGULAR' and msik.inventory_item_id = cii.inventory_item_id and msik.organization_id = mp.organization_id and msik.eam_item_type in (1,3) and mp.maint_organization_id = cca.maint_organization_id union all SELECT DISTINCT ccia.rowid, ccia.ctr_association_id as association_id, ec.initial_reading, ccia.counter_id meter_id, to_number(NULL), ccia.inventory_item_id asset_group_id, TO_CHAR(NULL) asset_number, cctt.name meter_name, ec.reading_type meter_type, ec.uom_code meter_uom, ec.direction value_change_dir, ec.eam_required_flag, ccia.primary_failure_flag, ec.used_in_scheduling, ec.default_usage_rate user_defined_rate, ec.use_past_reading, cctt.description, ec.start_date_active from_effective_date, ec.end_date_active to_effective_date, ccia.created_by, ccia.creation_date, ccia.last_update_login, ccia.last_update_date, ccia.last_updated_by, ccia.attribute_category, ccia.attribute1 attribute1, ccia.attribute2 attribute2, ccia.attribute3 attribute3, ccia.attribute4 attribute4, ccia.attribute5 attribute5, ccia.attribute6 attribute6, ccia.attribute7 attribute7, ccia.attribute8 attribute8, ccia.attribute9 attribute9, ccia.attribute10 attribute10, ccia.attribute11 attribute11, ccia.attribute12 attribute12, ccia.attribute13 attribute13, ccia.attribute14 attribute14, ccia.attribute15 attribute15, to_number(2) maintenance_object_type, ccia.inventory_item_id maintenance_object_id, to_number(null) creation_organization_id, to_char('Y') tmpl_flag, null source_tmpl_id, msik.eam_item_type eam_item_type, msik.concatenated_segments asset_group, to_char(NULL), msik.description asset_description, null source_meter_name, null factor, null relationship_start_date, ec.initial_reading_date, mp.maint_organization_id FROM csi_counter_template_b ec, csi_counter_template_tl cctt, csi_ctr_item_associations ccia, MTL_SYSTEM_ITEMS_B_KFV msik, mtl_parameters mp WHERE ccia.counter_id = ec.counter_id and ec.counter_id = cctt.counter_id and cctt.language = userenv('LANG') and SYSDATE BETWEEN nvl(ccia.start_date_active, SYSDATE-1) AND nvl(ccia.end_date_active, SYSDATE+1) and SYSDATE BETWEEN nvl(ec.start_date_active, SYSDATE-1) AND nvl(ec.end_date_active, SYSDATE+1) and ec.counter_type = 'REGULAR' and msik.inventory_item_id = ccia.inventory_item_id and msik.organization_id = mp.organization_id and msik.eam_item_type in (1,3)

View Text - HTML Formatted

SELECT CCA.ROWID
, CCA.INSTANCE_ASSOCIATION_ID AS ASSOCIATION_ID
, EC.INITIAL_READING
, CCA.COUNTER_ID METER_ID
, CII.LAST_VLD_ORGANIZATION_ID ORGANIZATION_ID
, CII.INVENTORY_ITEM_ID ASSET_GROUP_ID
, CII.INSTANCE_NUMBER ASSET_NUMBER
, CCT.NAME METER_NAME
, EC.READING_TYPE METER_TYPE
, EC.UOM_CODE METER_UOM
, EC.DIRECTION VALUE_CHANGE_DIR
, EC.EAM_REQUIRED_FLAG
, CCA.PRIMARY_FAILURE_FLAG
, EC.USED_IN_SCHEDULING
, EC.DEFAULT_USAGE_RATE USER_DEFINED_RATE
, EC.USE_PAST_READING
, CCT.DESCRIPTION DESCRIPTION
, EC.START_DATE_ACTIVE FROM_EFFECTIVE_DATE
, EC.END_DATE_ACTIVE TO_EFFECTIVE_DATE
, CCA.CREATED_BY
, CCA.CREATION_DATE
, CCA.LAST_UPDATE_LOGIN
, CCA.LAST_UPDATE_DATE
, CCA.LAST_UPDATED_BY
, CCA.ATTRIBUTE_CATEGORY
, CCA.ATTRIBUTE1 ATTRIBUTE1
, CCA.ATTRIBUTE2 ATTRIBUTE2
, CCA.ATTRIBUTE3 ATTRIBUTE3
, CCA.ATTRIBUTE4 ATTRIBUTE4
, CCA.ATTRIBUTE5 ATTRIBUTE5
, CCA.ATTRIBUTE6 ATTRIBUTE6
, CCA.ATTRIBUTE7 ATTRIBUTE7
, CCA.ATTRIBUTE8 ATTRIBUTE8
, CCA.ATTRIBUTE9 ATTRIBUTE9
, CCA.ATTRIBUTE10 ATTRIBUTE10
, CCA.ATTRIBUTE11 ATTRIBUTE11
, CCA.ATTRIBUTE12 ATTRIBUTE12
, CCA.ATTRIBUTE13 ATTRIBUTE13
, CCA.ATTRIBUTE14 ATTRIBUTE14
, CCA.ATTRIBUTE15 ATTRIBUTE15
, TO_NUMBER(3) MAINTENANCE_OBJECT_TYPE
, CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID
, TO_NUMBER(NULL) CREATION_ORGANIZATION_ID
, TO_CHAR('N') TMPL_FLAG
, EC.CREATED_FROM_COUNTER_TMPL_ID SOURCE_TMPL_ID
, MSIK.EAM_ITEM_TYPE EAM_ITEM_TYPE
, MSIK.CONCATENATED_SEGMENTS ASSET_GROUP
, MP.ORGANIZATION_CODE
, CII.INSTANCE_DESCRIPTION AS ASSET_DESCRIPTION
, CCT1.NAME SOURCE_METER_NAME
, CCR.FACTOR
, CCR.ACTIVE_START_DATE RELATIONSHIP_START_DATE
, EC.INITIAL_READING_DATE
, CCA.MAINT_ORGANIZATION_ID
FROM CSI_COUNTERS_B EC
, CSI_COUNTERS_TL CCT
, CSI_COUNTER_ASSOCIATIONS CCA
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, CSI_COUNTER_RELATIONSHIPS CCR
, CSI_COUNTERS_TL CCT1
, MTL_SYSTEM_ITEMS_B_KFV MSIK
WHERE CCA.COUNTER_ID = EC.COUNTER_ID
AND EC.COUNTER_ID = CCT.COUNTER_ID
AND CCT.LANGUAGE = USERENV('LANG')
AND EC.COUNTER_ID = CCR.OBJECT_COUNTER_ID (+)
AND CCR.RELATIONSHIP_TYPE_CODE(+) = 'CONFIGURATION'
AND CCA.SOURCE_OBJECT_ID = CII.INSTANCE_ID
AND MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND SYSDATE BETWEEN NVL(CCA.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(CCA.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(EC.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(EC.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(CCR.ACTIVE_START_DATE(+)
, SYSDATE-1)
AND NVL(CCR.ACTIVE_END_DATE(+)
, SYSDATE+1)
AND CCT1.COUNTER_ID (+) = CCR.SOURCE_COUNTER_ID
AND CCT1.LANGUAGE (+) = USERENV('LANG')
AND EC.COUNTER_TYPE = 'REGULAR'
AND MSIK.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIK.EAM_ITEM_TYPE IN (1
, 3)
AND MP.MAINT_ORGANIZATION_ID = CCA.MAINT_ORGANIZATION_ID UNION ALL SELECT DISTINCT CCIA.ROWID
, CCIA.CTR_ASSOCIATION_ID AS ASSOCIATION_ID
, EC.INITIAL_READING
, CCIA.COUNTER_ID METER_ID
, TO_NUMBER(NULL)
, CCIA.INVENTORY_ITEM_ID ASSET_GROUP_ID
, TO_CHAR(NULL) ASSET_NUMBER
, CCTT.NAME METER_NAME
, EC.READING_TYPE METER_TYPE
, EC.UOM_CODE METER_UOM
, EC.DIRECTION VALUE_CHANGE_DIR
, EC.EAM_REQUIRED_FLAG
, CCIA.PRIMARY_FAILURE_FLAG
, EC.USED_IN_SCHEDULING
, EC.DEFAULT_USAGE_RATE USER_DEFINED_RATE
, EC.USE_PAST_READING
, CCTT.DESCRIPTION
, EC.START_DATE_ACTIVE FROM_EFFECTIVE_DATE
, EC.END_DATE_ACTIVE TO_EFFECTIVE_DATE
, CCIA.CREATED_BY
, CCIA.CREATION_DATE
, CCIA.LAST_UPDATE_LOGIN
, CCIA.LAST_UPDATE_DATE
, CCIA.LAST_UPDATED_BY
, CCIA.ATTRIBUTE_CATEGORY
, CCIA.ATTRIBUTE1 ATTRIBUTE1
, CCIA.ATTRIBUTE2 ATTRIBUTE2
, CCIA.ATTRIBUTE3 ATTRIBUTE3
, CCIA.ATTRIBUTE4 ATTRIBUTE4
, CCIA.ATTRIBUTE5 ATTRIBUTE5
, CCIA.ATTRIBUTE6 ATTRIBUTE6
, CCIA.ATTRIBUTE7 ATTRIBUTE7
, CCIA.ATTRIBUTE8 ATTRIBUTE8
, CCIA.ATTRIBUTE9 ATTRIBUTE9
, CCIA.ATTRIBUTE10 ATTRIBUTE10
, CCIA.ATTRIBUTE11 ATTRIBUTE11
, CCIA.ATTRIBUTE12 ATTRIBUTE12
, CCIA.ATTRIBUTE13 ATTRIBUTE13
, CCIA.ATTRIBUTE14 ATTRIBUTE14
, CCIA.ATTRIBUTE15 ATTRIBUTE15
, TO_NUMBER(2) MAINTENANCE_OBJECT_TYPE
, CCIA.INVENTORY_ITEM_ID MAINTENANCE_OBJECT_ID
, TO_NUMBER(NULL) CREATION_ORGANIZATION_ID
, TO_CHAR('Y') TMPL_FLAG
, NULL SOURCE_TMPL_ID
, MSIK.EAM_ITEM_TYPE EAM_ITEM_TYPE
, MSIK.CONCATENATED_SEGMENTS ASSET_GROUP
, TO_CHAR(NULL)
, MSIK.DESCRIPTION ASSET_DESCRIPTION
, NULL SOURCE_METER_NAME
, NULL FACTOR
, NULL RELATIONSHIP_START_DATE
, EC.INITIAL_READING_DATE
, MP.MAINT_ORGANIZATION_ID
FROM CSI_COUNTER_TEMPLATE_B EC
, CSI_COUNTER_TEMPLATE_TL CCTT
, CSI_CTR_ITEM_ASSOCIATIONS CCIA
, MTL_SYSTEM_ITEMS_B_KFV MSIK
, MTL_PARAMETERS MP
WHERE CCIA.COUNTER_ID = EC.COUNTER_ID
AND EC.COUNTER_ID = CCTT.COUNTER_ID
AND CCTT.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN NVL(CCIA.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(CCIA.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(EC.START_DATE_ACTIVE
, SYSDATE-1)
AND NVL(EC.END_DATE_ACTIVE
, SYSDATE+1)
AND EC.COUNTER_TYPE = 'REGULAR'
AND MSIK.INVENTORY_ITEM_ID = CCIA.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIK.EAM_ITEM_TYPE IN (1
, 3)