[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_SELECTED_METER IN NUMBER,
P_CURRENT_ORG_ID IN NUMBER,
X_GROUP_ID OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_unmatched_uom_class OUT NOCOPY VARCHAR2,
x_unmatched_currency OUT NOCOPY VARCHAR2) IS
sql_stmt VARCHAR2(4000);
p_selected_meter => p_selected_meter,
p_view_by => 1,
p_from_date_clause => p_from_date_clause,
x_ref_failures => l_ref_failures);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_GROUP_ID FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
P_SELECTED_METER IN NUMBER,
P_INCLUDE_CHILDREN IN VARCHAR2,
P_VIEW_BY IN VARCHAR2,
P_COMPUTE_REPAIR_COSTS IN VARCHAR2,
P_CURRENT_ORG_ID IN VARCHAR2,
X_GROUP_ID OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_unmatched_uom_class OUT NOCOPY VARCHAR2,
x_unmatched_currency OUT NOCOPY VARCHAR2) IS
c_ref_failures SYS_REFCURSOR;
GET_FAILURE_METER_RECS_CURSOR(l_where_clause_parent, p_selected_meter, p_from_date_clause, p_View_By, x_ref_failures);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
P_SELECTED_METER IN NUMBER,
P_FROM_DATE_CLAUSE IN VARCHAR2,
P_VIEW_BY IN NUMBER,
X_REF_FAILURES OUT NOCOPY SYS_REFCURSOR) IS
sql_stmt VARCHAR2(8000);
l_selected_meter NUMBER;
IF P_SELECTED_METER IS NULL THEN
l_selected_meter := 0;
l_selected_meter := P_SELECTED_METER;
l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)';
sql_stmt := ' SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY_ID,
ASSET_CATEGORY,
ASSET_LOCATION_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT_ID,
OWNING_DEPARTMENT,
FAILURE_CODE,
FAILURE_DESC,
CAUSE_CODE,
CAUSE_DESC,
RESOLUTION_CODE,
RESOLUTION_DESC,
FAILURE_DATE,
COMMENTS,
DECODE( LAG(FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE),
NULL, (FAILURE_DATE - '||l_first_tbf_calc_clause||' ),
(FAILURE_DATE - ( LAG(FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
(DATE_COMPLETED - FAILURE_DATE )*24 TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
DECODE(METER_TYPE,2,CURRENT_READING,1,
DECODE( LAG(CURRENT_READING,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY DATE_COMPLETED),
NULL, CURRENT_READING,
(CURRENT_READING - ( LAG(CURRENT_READING,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY DATE_COMPLETED))) )) READING_BETWEEN_FAILURES,
'||'''Y'''||' INCLUDE_FOR_READING_AGGR,
'||'''Y'''||' INCLUDE_FOR_COST_AGGR
FROM (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID)MAINTAINED_GROUP_ID,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
WDJ.OWNING_DEPARTMENT OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
WDJ.DATE_COMPLETED,
EAFC.FAILURE_CODE,
EFC.DESCRIPTION FAILURE_DESC,
EAFC.CAUSE_CODE,
ECC.DESCRIPTION CAUSE_DESC,
EAFC.RESOLUTION_CODE,
ERC.DESCRIPTION RESOLUTION_DESC,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
METER.CURRENT_READING CURRENT_READING,
METER.CURRENT_READING_DATE CURRENT_READING_DATE,
CII.LAST_VLD_ORGANIZATION_ID ASSET_ORGANIZATION_ID,
METER.METER_TYPE,
CII.CREATION_DATE ASSET_CREATION_DATE
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
EAM_FAILURE_CODES EFC,
EAM_CAUSE_CODES ECC,
EAM_RESOLUTION_CODES ERC,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT WHERE ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('|| '''LANG'''|| ') and ccb.counter_type = '||'''REGULAR'''||'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND CCA.PRIMARY_FAILURE_FLAG = '||'''Y'''||'
AND CCB.EAM_REQUIRED_FLAG = '||'''Y'''||'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND EAF.SOURCE_ID = METER.WIP_ENTITY_ID (+) )';
OPEN X_REF_FAILURES FOR 'SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR FROM ('||sql_stmt||l_where_clause||')'||l_from_date_clause||' ORDER BY MAINTAINED_GROUP_ID';
sql_stmt := 'SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY_ID,
ASSET_CATEGORY,
ASSET_LOCATION_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT_ID,
OWNING_DEPARTMENT,
FAILURE_CODE,
FAILURE_DESC,
CAUSE_CODE,
CAUSE_DESC,
RESOLUTION_CODE,
RESOLUTION_DESC,
FAILURE_DATE,
COMMENTS,
DECODE( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE),
NULL, (FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = MAINTAINED_GROUP_ID
AND CII1.CURRENT_ORGANIZATION_ID = ASSET_ORGANIZATION_ID)),
(FAILURE_DATE - ( LAG(FAILURE_DATE,1,NULL) OVER (PARTITION BY '||l_partition_by||' ORDER BY FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(DATE_COMPLETED - FAILURE_DATE ) * 24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
to_number(NULL) READING_BETWEEN_FAILURES,
'||'''Y'''||' INCLUDE_FOR_READING_AGGR,
'||'''Y'''||' INCLUDE_FOR_COST_AGGR
FROM (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
WDJ.DATE_COMPLETED,
EAFC.FAILURE_CODE,
EFC.DESCRIPTION FAILURE_DESC,
EAFC.CAUSE_CODE,
ECC.DESCRIPTION CAUSE_DESC,
EAFC.RESOLUTION_CODE,
ERC.DESCRIPTION RESOLUTION_DESC,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
CII.LAST_VLD_ORGANIZATION_ID ASSET_ORGANIZATION_ID
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
EAM_FAILURE_CODES EFC,
EAM_CAUSE_CODES ECC,
EAM_RESOLUTION_CODES ERC
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND EAF.MAINT_ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID )';
OPEN X_REF_FAILURES FOR 'SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR FROM ('||SQL_STMT||l_where_clause||' ) '||l_from_date_clause;
P_SELECTED_METER IN NUMBER,
P_CURRENT_ORG_ID IN NUMBER,
X_GROUP_ID OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_unmatched_uom_class OUT NOCOPY VARCHAR2,
x_unmatched_currency OUT NOCOPY VARCHAR2) IS
l_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
l_selected_meter NUMBER;
SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR
from
(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR,
METER.PRIMARY_FAILURE_METER,
METER.METER_ID
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
WHERE
ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
(l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
AND CCB.EAM_REQUIRED_FLAG = 'Y'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND EAF.SOURCE_ID = METER.WIP_ENTITY_ID (+)
AND EAFC.FAILURE_CODE IS NOT NULL
AND EAFC.CAUSE_CODE IS NOT NULL
AND EAFC.RESOLUTION_CODE IS NOT NULL
AND EAF.FAILURE_DATE IS NOT NULL
AND EAF.OBJECT_ID = l_gen_object_id
AND (l_to_date IS NULL OR EAF.FAILURE_DATE <= l_to_date))
WHERE (l_from_date IS NULL OR FAILURE_DATE >= l_from_date);
IF p_selected_meter = 0 THEN
l_selected_meter := NULL;
l_selected_meter := p_selected_meter;
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(l_group_id, l_asset_failure_tbl);
P_SELECTED_METER IN NUMBER,
P_CURRENT_ORG_ID IN NUMBER,
X_GROUP_ID OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_unmatched_uom_class OUT NOCOPY VARCHAR2,
x_unmatched_currency OUT NOCOPY VARCHAR2) IS
l_asset_failure_tbl EAM_FAILURE_ANALYSIS_PVT.eam_asset_failure_tbl_type;
l_selected_meter number;
SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR
from
(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY CII.INSTANCE_ID, CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR,
NULL METER_ID
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAFC.FAILURE_CODE IS NOT NULL
AND EAFC.CAUSE_CODE IS NOT NULL
AND EAFC.RESOLUTION_CODE IS NOT NULL
AND EAF.FAILURE_DATE IS NOT NULL
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ;
SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR
from
(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY CII.INSTANCE_ID, EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE)) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR,
NULL METER_ID
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAFC.FAILURE_CODE IS NOT NULL
AND EAFC.CAUSE_CODE IS NOT NULL
AND EAFC.RESOLUTION_CODE IS NOT NULL
AND EAF.FAILURE_DATE IS NOT NULL
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ;
SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR
from
(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR,
METER.METER_ID,
METER.PRIMARY_FAILURE_METER
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
WHERE
ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
(l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
AND CCB.EAM_REQUIRED_FLAG = 'Y'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAFC.FAILURE_CODE IS NOT NULL
AND EAFC.CAUSE_CODE IS NOT NULL
AND EAFC.RESOLUTION_CODE IS NOT NULL
AND EAF.FAILURE_DATE IS NOT NULL
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR MSIKFV.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date);
SELECT ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
COMMENTS,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
METER_ID,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR
from
(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
EAF.AREA_ID ASSET_LOCATION_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
EAF.DEPARTMENT_ID OWNING_DEPARTMENT_ID,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY CII.INSTANCE_ID, CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR,
METER.METER_ID,
METER.PRIMARY_FAILURE_METER
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
WHERE
ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND ( (l_selected_meter IS NULL AND CCA.PRIMARY_FAILURE_FLAG = 'Y') OR
(l_selected_meter IS NOT NULL AND CCB.COUNTER_ID = l_selected_meter))
AND CCB.EAM_REQUIRED_FLAG = 'Y'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAFC.FAILURE_CODE IS NOT NULL
AND EAFC.CAUSE_CODE IS NOT NULL
AND EAFC.RESOLUTION_CODE IS NOT NULL
AND EAF.FAILURE_DATE IS NOT NULL
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR MSIKFV.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR EAF.FAILURE_DATE <= p_to_date))
WHERE (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
ORDER BY MAINTAINED_GROUP_ID;
IF p_selected_meter = 0 THEN
l_selected_meter := NULL;
l_selected_meter := p_selected_meter;
l_asset_failure_tbl.DELETE();
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
Procedure INSERT_INTO_TEMP_TABLE
( p_group_id IN NUMBER,
P_ASSET_FAILURE_TBL IN eam_asset_failure_tbl_type) IS
l_asset_failure_tbl eam_asset_failure_tbl_type;
g_module_name := 'INSERT_INTO_TEMP_TABLE';
INSERT INTO EAM_FAILURE_HISTORY_TEMP
(GROUP_ID,
ASSET_TYPE,
MAINTENANCE_OBJECT_ID,
MAINTAINED_NUMBER,
DESCRIPTIVE_TEXT,
MAINTAINED_GROUP,
MAINTAINED_GROUP_ID ,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
MAINT_ORGANIZATION_ID,
ORGANIZATION_CODE,
ASSET_CATEGORY,
ASSET_CATEGORY_ID,
ASSET_LOCATION,
OWNING_DEPARTMENT,
FAILURE_CODE,
CAUSE_CODE,
RESOLUTION_CODE,
FAILURE_DATE,
DAYS_BETWEEN_FAILURES,
TIME_TO_REPAIR,
COMMENTS,
METER_NAME,
METER_UOM,
READING_BETWEEN_FAILURES,
INCLUDE_FOR_READING_AGGR,
INCLUDE_FOR_COST_AGGR)
values
(p_group_id,
l_asset_failure_tbl(i).ASSET_TYPE,
l_asset_failure_tbl(i).MAINTENANCE_OBJECT_ID,
l_asset_failure_tbl(i).MAINTAINED_NUMBER,
l_asset_failure_tbl(i).DESCRIPTIVE_TEXT,
l_asset_failure_tbl(i).MAINTAINED_GROUP,
l_asset_failure_tbl(i).MAINTAINED_GROUP_ID,
l_asset_failure_tbl(i).WIP_ENTITY_ID,
l_asset_failure_tbl(i).WIP_ENTITY_NAME,
l_asset_failure_tbl(i).ORGANIZATION_ID,
l_asset_failure_tbl(i).ORGANIZATION_CODE,
l_asset_failure_tbl(i).ASSET_CATEGORY,
l_asset_failure_tbl(i).ASSET_CATEGORY_ID,
l_asset_failure_tbl(i).ASSET_LOCATION,
l_asset_failure_tbl(i).OWNING_DEPARTMENT,
l_asset_failure_tbl(i).FAILURE_CODE,
l_asset_failure_tbl(i).CAUSE_CODE,
l_asset_failure_tbl(i).RESOLUTION_CODE,
l_asset_failure_tbl(i).FAILURE_DATE,
l_asset_failure_tbl(i).DAYS_BETWEEN_FAILURES,
l_asset_failure_tbl(i).TIME_TO_REPAIR,
l_asset_failure_tbl(i).COMMENTS,
l_asset_failure_tbl(i).METER_NAME,
l_asset_failure_tbl(i).METER_UOM,
l_asset_failure_tbl(i).READING_BETWEEN_FAILURES,
l_asset_failure_tbl(i).INCLUDE_FOR_READING_AGGR,
l_asset_failure_tbl(i).INCLUDE_FOR_COST_AGGR);
END INSERT_INTO_TEMP_TABLE;
SELECT SUM(WEPB.ACTUAL_MAT_COST+WEPB.ACTUAL_LAB_COST+WEPB.ACTUAL_EQP_COST) COST,
WEPB.WIP_ENTITY_ID
FROM WIP_EAM_PERIOD_BALANCES WEPB, EAM_FAILURE_HISTORY_TEMP EFHT
WHERE EFHT.GROUP_ID = p_group_id
AND EFHT.MAINT_ORGANIZATION_ID = WEPB.ORGANIZATION_ID
AND EFHT.WIP_ENTITY_ID = WEPB.WIP_ENTITY_ID
AND EFHT.INCLUDE_FOR_COST_AGGR = 'Y'
GROUP BY WEPB.WIP_ENTITY_ID;
UPDATE EAM_FAILURE_HISTORY_TEMP
SET COST_TO_REPAIR = Nvl(P_repair_cost_tbl(i),0)
WHERE WIP_ENTITY_ID = P_wip_entity_id_tbl(i)
AND GROUP_ID = p_group_id
AND INCLUDE_FOR_COST_AGGR = 'Y';
select 1 into l_same_prim_curr
from cst_organization_definitions cod1, cst_organization_definitions cod2,
gl_sets_of_books gsob1, gl_sets_of_books gsob2
where cod1.organization_id = p_current_org_id
and cod2.organization_id = l_org2
and cod1.set_of_books_id = gsob1.set_of_books_id
and cod2.set_of_books_id = gsob2.set_of_books_id
and gsob1.currency_code = gsob2.currency_code;
select nvl(MUC1.CONVERSION_RATE,0) , nvl(MUC2.CONVERSION_RATE,0), MUOFVL.UOM_CODE
into l_uom1_conv_rate, l_uom2_conv_rate, l_primary_uom
from MTL_UOM_CONVERSIONS MUC1, MTL_UOM_CONVERSIONS MUC2, MTL_UNITS_OF_MEASURE_VL MUOFVL
where MUC1.UOM_CODE = l_meter_uom1
and MUC2.UOM_CODE = l_meter_uom2
AND NVL(MUC1.DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND NVL(MUC2.DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND MUC1.INVENTORY_ITEM_ID = 0
AND MUC2.INVENTORY_ITEM_ID = 0
AND MUC1.UOM_CLASS = MUC2.UOM_CLASS
AND MUOFVL.UOM_CLASS = MUC1.UOM_CLASS
AND MUOFVL.BASE_UOM_FLAG = 'Y'
AND NVL(MUOFVL.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(
SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
(
SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM MTL_SERIAL_NUMBERS MSN,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
)
UNION
SELECT CII.INSTANCE_ID OBJECT_ID --This part will select the parent assets
FROM CSI_ITEM_INSTANCES CII
WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
) ;
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN (
SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM CSI_ITEM_INSTANCES CII,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
MTL_SERIAL_NUMBERS MSN
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
)
UNION
SELECT MAINTENANCE_OBJECT_ID FROM(
SELECT MAINTENANCE_OBJECT_ID FROM (
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.CATEGORY_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
CSI_ITEM_INSTANCES CII
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID IN (SELECT EAF.OBJECT_ID OBJECT_ID --This part will select parent assets
FROM EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CII
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
)
)
WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) ) );
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM MTL_SERIAL_NUMBERS MSN,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
)
UNION
SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
FROM CSI_ITEM_INSTANCES CII
WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
) ;
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
MTL_SERIAL_NUMBERS MSN,
CSI_ITEM_INSTANCES CII
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
UNION
SELECT MAINTENANCE_OBJECT_ID FROM (
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAFC.FAILURE_CODE ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
FROM EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
)
)
WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG PRIMARY_FAILURE_METER,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
WHERE
ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND CCA.PRIMARY_FAILURE_FLAG = 'Y'
AND CCB.EAM_REQUIRED_FLAG = 'Y'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM MTL_SERIAL_NUMBERS MSN,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
)
UNION
SELECT MAINTENANCE_OBJECT_ID FROM
(
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT EM.METER_ID,
EM.METER_NAME,
EM.METER_UOM,
EAM.MAINTENANCE_OBJECT_ID,
EMR.CURRENT_READING,
EMR.CURRENT_READING_DATE,
EAM.PRIMARY_FAILURE_METER,
EMR.WIP_ENTITY_ID,
EM.METER_TYPE
FROM EAM_METERS EM,
EAM_ASSET_METERS EAM,
EAM_METER_READINGS EMR
WHERE EM.METER_ID = EAM.METER_ID
AND EMR.METER_ID = EAM.METER_ID
AND EM.REQUIRED_FLAG = 'Y'
--AND EAM.PRIMARY_FAILURE_METER = 'Y'
-- AND ( (l_selected_meter IS NULL AND EAM.PRIMARY_FAILURE_METER = 'Y') OR
-- (l_selected_meter IS NOT NULL AND EM.METER_ID = l_selected_meter))
AND EMR.METER_READING_ID IN
(SELECT METER_READING_ID FROM
(SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
FROM EAM_METER_READINGS EMR1
GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN ( SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
FROM CSI_ITEM_INSTANCES CII
WHERE(l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
)));
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
CSI_ITEM_INSTANCES CII,
(SELECT EM.METER_ID,
EM.METER_NAME,
EM.METER_UOM,
EAM.MAINTENANCE_OBJECT_ID,
EMR.CURRENT_READING,
EMR.CURRENT_READING_DATE,
EAM.PRIMARY_FAILURE_METER,
EMR.WIP_ENTITY_ID,
EM.METER_TYPE
FROM EAM_METERS EM,
EAM_ASSET_METERS EAM,
EAM_METER_READINGS EMR
WHERE EM.METER_ID = EAM.METER_ID
AND EMR.METER_ID = EAM.METER_ID
AND EM.REQUIRED_FLAG = 'Y'
--AND EAM.PRIMARY_FAILURE_METER = 'Y'
-- AND ( (l_selected_meter IS NULL AND EAM.PRIMARY_FAILURE_METER = 'Y') OR
-- (l_selected_meter IS NOT NULL AND EM.METER_ID = l_selected_meter))
AND EMR.METER_READING_ID IN
(SELECT METER_READING_ID FROM
(SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
FROM EAM_METER_READINGS EMR1
GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
-- AND MSN.GEN_OBJECT_ID = WDJ.MAINTENANCE_OBJECT_ID
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM CSI_ITEM_INSTANCES CII,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
MTL_SERIAL_NUMBERS MSN
WHERE NVL(WDJ.STATUS_TYPE,4) IN (4,5,12)
AND EAF.SOURCE_TYPE(+) = 1
AND EAF.OBJECT_TYPE(+) = 3
AND EAF.OBJECT_ID(+) = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID(+)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
UNION
SELECT MAINTENANCE_OBJECT_ID FROM (
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
CSI_ITEM_INSTANCES CII,
(SELECT EM.METER_ID,
EM.METER_NAME,
EM.METER_UOM,
EAM.MAINTENANCE_OBJECT_ID,
EMR.CURRENT_READING,
EMR.CURRENT_READING_DATE,
EAM.PRIMARY_FAILURE_METER,
EMR.WIP_ENTITY_ID,
EM.METER_TYPE
FROM EAM_METERS EM,
EAM_ASSET_METERS EAM,
EAM_METER_READINGS EMR
WHERE EM.METER_ID = EAM.METER_ID
AND EMR.METER_ID = EAM.METER_ID
AND EM.REQUIRED_FLAG = 'Y'
AND EAM.PRIMARY_FAILURE_METER = 'Y'
AND EMR.METER_READING_ID IN
(SELECT METER_READING_ID FROM
(SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
FROM EAM_METER_READINGS EMR1
GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
--AND MSN.GEN_OBJECT_ID = WDJ.MAINTENANCE_OBJECT_ID
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
FROM EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CII
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
)
)
WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT
WHERE
ccb.counter_id = cctl.counter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1)
and cctl.language = userenv('LANG') and ccb.counter_type = 'REGULAR'
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCR.COUNTER_ID(+) = CCB.COUNTER_ID
AND CCR.transaction_id = CT.transaction_id(+)
and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1) AND nvl(cca.end_date_active, SYSDATE+1)
AND CCA.PRIMARY_FAILURE_FLAG = 'Y'
AND CCB.EAM_REQUIRED_FLAG = 'Y'
AND CCR.COUNTER_VALUE_ID IN
(
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM MTL_SERIAL_NUMBERS MSN,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID
)
UNION
SELECT CII.INSTANCE_ID OBJECT_ID --This part will select all parent assets
FROM CSI_ITEM_INSTANCES CII
WHERE (l_gen_object_id IS NULL OR CII.INSTANCE_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
) ;
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - MSN.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT EM.METER_ID,
EM.METER_NAME,
EM.METER_UOM,
EAM.MAINTENANCE_OBJECT_ID,
EMR.CURRENT_READING,
EMR.CURRENT_READING_DATE,
EAM.PRIMARY_FAILURE_METER,
EMR.WIP_ENTITY_ID,
EM.METER_TYPE
FROM EAM_METERS EM,
EAM_ASSET_METERS EAM,
EAM_METER_READINGS EMR
WHERE EM.METER_ID = EAM.METER_ID
AND EMR.METER_ID = EAM.METER_ID
AND EM.REQUIRED_FLAG = 'Y'
AND EAM.PRIMARY_FAILURE_METER = 'Y'
AND EMR.METER_READING_ID IN
(SELECT METER_READING_ID FROM
(SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
FROM EAM_METER_READINGS EMR1
GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
AND EAF.OBJECT_ID IN (SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID IN
(SELECT OBJECT_ID --This part will select all children assets
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
( SELECT MSN.GEN_OBJECT_ID PARENT_OBJECT_ID
FROM CSI_ITEM_INSTANCES CII,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
AND (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date)
)
CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID)
UNION
SELECT MAINTENANCE_OBJECT_ID FROM (
SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
NVL(WDJ.ASSET_NUMBER, WDJ.REBUILD_SERIAL_NUMBER) MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY CII.INVENTORY_ITEM_ID ORDER BY EAF.FAILURE_DATE ),
--NULL, (EAF.FAILURE_DATE - CII.CREATION_DATE),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'Y' INCLUDE_FOR_READING_AGGR,
'Y' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
CSI_ITEM_INSTANCES CII,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT EM.METER_ID,
EM.METER_NAME,
EM.METER_UOM,
EAM.MAINTENANCE_OBJECT_ID,
EMR.CURRENT_READING,
EMR.CURRENT_READING_DATE,
EAM.PRIMARY_FAILURE_METER,
EMR.WIP_ENTITY_ID,
EM.METER_TYPE
FROM EAM_METERS EM,
EAM_ASSET_METERS EAM,
EAM_METER_READINGS EMR
WHERE EM.METER_ID = EAM.METER_ID
AND EMR.METER_ID = EAM.METER_ID
AND EM.REQUIRED_FLAG = 'Y'
AND EAM.PRIMARY_FAILURE_METER = 'Y'
AND EMR.METER_READING_ID IN
(SELECT METER_READING_ID FROM
(SELECT Max(EMR1.METER_READING_ID) METER_READING_ID,EMR1.WIP_ENTITY_ID,EMR1.METER_ID
FROM EAM_METER_READINGS EMR1
GROUP BY EMR1.WIP_ENTITY_ID, EMR1.METER_ID ))) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN ( SELECT EAF.OBJECT_ID OBJECT_ID --This part will select all parent assets
FROM EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CII
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND (l_gen_object_id IS NULL OR EAF.OBJECT_ID = l_gen_object_id)
AND (l_maint_group_id IS NULL OR CII.INVENTORY_ITEM_ID = l_maint_group_id)
AND (l_category_id IS NULL OR CII.CATEGORY_ID = l_category_id)
AND (p_failure_code IS NULL OR EAFC.FAILURE_CODE = p_failure_code)
)
)
WHERE (p_to_date IS NULL OR FAILURE_DATE <= p_to_date)
AND (p_from_date IS NULL OR FAILURE_DATE >= p_from_date) );
l_asset_failure_tbl.DELETE;
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
SELECT EAM_FAILURE_HISTORY_TEMP_S.NEXTVAL INTO l_group_id FROM DUAL;
INSERT_INTO_TEMP_TABLE(p_group_id => l_group_id,
p_asset_failure_tbl => l_asset_failure_tbl);
'SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - (SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID) ),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
NULL METER_ID,
NULL METER_NAME,
NULL METER_UOM,
NULL READING_BETWEEN_FAILURES,
'||'''Y'''||' INCLUDE_FOR_READING_AGGR,
'||'''Y'''||' INCLUDE_FOR_COST_AGGR ';
l_sql_stmt3 := '(SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID IN
( SELECT OBJECT_ID
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
(SELECT GEN_OBJECT_ID PARENT_OBJECT_ID FROM ';
--when it IS required to have a parent failure wo, in order to select the children assets.
l_sql_stmt4 := '(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
CII.INSTANCE_ID MAINTENANCE_OBJECT_ID,
MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EFC.DESCRIPTION FAILURE_DESC,
EAFC.CAUSE_CODE,
ECC.DESCRIPTION CAUSE_DESC,
EAFC.RESOLUTION_CODE,
ERC.DESCRIPTION RESOLUTION_DESC,
EAF.FAILURE_DATE,
EAFC.COMMENTS
FROM WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
EAM_FAILURE_CODES EFC,
EAM_CAUSE_CODES ECC,
MTL_SERIAL_NUMBERS MSN,
EAM_RESOLUTION_CODES ERC
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND EAF.MAINT_ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER )';
--when it's NOT required to have a parent failure wo, in order to select the children assets.
l_sql_stmt5 := ' (SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
CII.INSTANCE_ID MAINTENANCE_OBJECT_ID ,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT
FROM MTL_SERIAL_NUMBERS MSN,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
CSI_ITEM_INSTANCES CII,
(SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EOMD1.AREA_ID
AND BD.DEPARTMENT_ID (+)= EOMD1.OWNING_DEPARTMENT_ID
AND MSIKFV.EAM_ITEM_TYPE IN (1,3)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND EOMD1.OBJECT_ID(+) = CII.INSTANCE_ID
AND EOMD1.OBJECT_TYPE(+) = 50)';
IF (l_where_clause IS NULL) THEN --parents need NOT have a failure work order, to get children selected
OPEN x_ref_failures FOR
l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt5||' '||l_where_clause_1||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
' UNION '||
' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt5||' '||l_where_clause_1||' )) '||l_from_date_clause ||' )';
ELSIF (l_where_clause_1 IS NULL) THEN --parents need TO have atleast one failure work order, to get children selected
OPEN x_ref_failures FOR
l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt4||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
' UNION '||
' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt4||' '||l_where_clause||' )) '||l_from_date_clause ||' )';
l_first_tbf_calc_clause := '(SELECT MIN(CII1.CREATION_DATE)
FROM CSI_ITEM_INSTANCES CII1
WHERE CII1.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND CII1.LAST_VLD_ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID)';
l_sql_stmt1 := 'SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
NVL(WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) MAINTAINED_GROUP_ID ,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
CII.CATEGORY_ID ASSET_CATEGORY_ID,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EAFC.CAUSE_CODE,
EAFC.RESOLUTION_CODE,
EAF.FAILURE_DATE,
EAFC.COMMENTS,
DECODE( LAG(EAF.FAILURE_DATE,1,NULL) OVER ( PARTITION BY '||l_partition_by||' ORDER BY EAF.FAILURE_DATE ),
NULL, (EAF.FAILURE_DATE - '||l_first_tbf_calc_clause||'),
(EAF.FAILURE_DATE - ( LAG(EAF.FAILURE_DATE,1,NULL) OVER
( PARTITION BY EAF.OBJECT_ID ORDER BY EAF.FAILURE_DATE )) )) DAYS_BETWEEN_FAILURES,
(WDJ.DATE_COMPLETED - EAF.FAILURE_DATE )*24 TIME_TO_REPAIR,
METER.METER_ID,
METER.METER_NAME METER_NAME,
METER.METER_UOM METER_UOM,
DECODE(METER.METER_TYPE,2,METER.CURRENT_READING,1,
DECODE( LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ),
NULL, METER.CURRENT_READING,
(METER.CURRENT_READING -
(LAG(METER.CURRENT_READING,1,NULL) OVER ( PARTITION BY EAF.OBJECT_ID, METER.METER_ID ORDER BY WDJ.DATE_COMPLETED ))) )) READING_BETWEEN_FAILURES,
'||'''Y'''||' INCLUDE_FOR_READING_AGGR,
'||'''Y'''||' INCLUDE_FOR_COST_AGGR
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
ORG_ORGANIZATION_DEFINITIONS OOD,
(SELECT
ccb.counter_id METER_ID,
cctl.name METER_NAME,
ccb.uom_code METER_UOM,
CCA.SOURCE_OBJECT_ID MAINTENANCE_OBJECT_ID,
CCR.COUNTER_READING CURRENT_READING,
CCR.VALUE_TIMESTAMP CURRENT_READING_DATE,
CCA.PRIMARY_FAILURE_FLAG,
decode(ct.transaction_type_id,92,ct.source_header_ref_id,to_number(null)) WIP_ENTITY_ID,
CCB.reading_type METER_TYPE
FROM csi_counters_b CCB,csi_counters_tl cctl, csi_counter_readings CCR, csi_counter_associations CCA, csi_transactions CT ';
SELECT
METER_READING_ID
FROM
(
SELECT
Max(EMR1.METER_READING_ID) METER_READING_ID
FROM EAM_METER_READINGS_V EMR1
GROUP BY EMR1.WIP_ENTITY_ID,
EMR1.METER_ID
)
)) METER
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.ORGANIZATION_ID = EAF.MAINT_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+) = EAF.DEPARTMENT_ID
AND EAF.OBJECT_ID = METER.MAINTENANCE_OBJECT_ID (+)
AND METER.WIP_ENTITY_ID (+) = EAF.SOURCE_ID
AND EAF.OBJECT_ID IN ';
l_sql_stmt3 := '( SELECT CII.INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII,
MTL_SERIAL_NUMBERS MSN
WHERE CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.gen_object_id IN
(SELECT OBJECT_ID
FROM MTL_OBJECT_GENEALOGY
START WITH PARENT_OBJECT_ID IN
(SELECT GEN_OBJECT_ID PARENT_OBJECT_ID FROM';
l_sql_stmt4 := '(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
EAF.OBJECT_ID MAINTENANCE_OBJECT_ID,
MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT,
EAFC.FAILURE_CODE,
EFC.DESCRIPTION FAILURE_DESC,
EAFC.CAUSE_CODE,
ECC.DESCRIPTION CAUSE_DESC,
EAFC.RESOLUTION_CODE,
ERC.DESCRIPTION RESOLUTION_DESC,
EAF.FAILURE_DATE,
EAFC.COMMENTS
FROM WIP_DISCRETE_JOBS WDJ,
CSI_ITEM_INSTANCES CII,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
EAM_ASSET_FAILURE_CODES EAFC,
EAM_ASSET_FAILURES EAF,
EAM_FAILURE_CODES EFC,
EAM_CAUSE_CODES ECC,
EAM_RESOLUTION_CODES ERC,
MTL_SERIAL_NUMBERS MSN
WHERE WDJ.WIP_ENTITY_ID = EAF.SOURCE_ID
AND WDJ.STATUS_TYPE IN (4,5,12)
AND EAF.SOURCE_TYPE = 1
AND EAF.OBJECT_TYPE = 3
AND EAF.OBJECT_ID = CII.INSTANCE_ID
AND EAF.FAILURE_ID = EAFC.FAILURE_ID
AND EAFC.FAILURE_CODE = EFC.FAILURE_CODE
AND EAFC.CAUSE_CODE = ECC.CAUSE_CODE
AND EAFC.RESOLUTION_CODE = ERC.RESOLUTION_CODE
AND (EFC.EFFECTIVE_END_DATE IS NULL OR EFC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ECC.EFFECTIVE_END_DATE IS NULL OR ECC.EFFECTIVE_END_DATE >= SYSDATE)
AND (ERC.EFFECTIVE_END_DATE IS NULL OR ERC.EFFECTIVE_END_DATE >= SYSDATE)
AND CII.INSTANCE_ID = DECODE(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,NULL)
AND MSIKFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSIKFV.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EAF.AREA_ID
AND BD.DEPARTMENT_ID (+)= EAF.DEPARTMENT_ID
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER )';
'(SELECT MSIKFV.EAM_ITEM_TYPE ASSET_TYPE,
MSN.GEN_OBJECT_ID GEN_OBJECT_ID,
CII.INSTANCE_ID MAINTENANCE_OBJECT_ID ,
CII.INSTANCE_NUMBER MAINTAINED_NUMBER,
CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT,
MSIKFV.CONCATENATED_SEGMENTS MAINTAINED_GROUP,
MCKFV.CONCATENATED_SEGMENTS ASSET_CATEGORY,
MEL.LOCATION_CODES ASSET_LOCATION,
BD.DEPARTMENT_CODE OWNING_DEPARTMENT
FROM MTL_SERIAL_NUMBERS MSN,
MTL_CATEGORIES_KFV MCKFV,
MTL_SYSTEM_ITEMS_KFV MSIKFV,
MTL_EAM_LOCATIONS MEL,
BOM_DEPARTMENTS BD,
CSI_ITEM_INSTANCES CII,
(SELECT * FROM EAM_ORG_MAINT_DEFAULTS WHERE ORGANIZATION_ID= '||l_org_id|| ' ) EOMD1 ' ||
' WHERE CII.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MCKFV.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MEL.LOCATION_ID (+) = EOMD1.AREA_ID
AND BD.DEPARTMENT_ID (+)= EOMD1.OWNING_DEPARTMENT_ID
AND MSIKFV.EAM_ITEM_TYPE IN (1,3)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND EOMD1.OBJECT_ID(+) = CII.INSTANCE_ID
AND EOMD1.OBJECT_TYPE(+) = 50)';
IF (l_where_clause IS NULL) THEN --parents need NOT have a failure work order, to get children selected
OPEN x_ref_failures FOR
l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt5||' '||l_where_clause_1||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
' UNION '||
' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt5||' '||l_where_clause_1||' )) '||l_from_date_clause||' )';
ELSIF (l_where_clause_1 IS NULL) THEN --parents need TO have atleast one failure work order, to get children selected
OPEN x_ref_failures FOR
l_sql_stmt1||' '||l_sql_stmt2||' '||l_sql_stmt3||' '||l_sql_stmt4||' '||l_where_clause||' '||l_from_date_clause||') CONNECT BY NOCYCLE PRIOR OBJECT_ID = PARENT_OBJECT_ID )'||
' UNION '||
' SELECT MAINTENANCE_OBJECT_ID FROM ( '||l_sql_stmt1||' '||l_sql_stmt2||' (SELECT MAINTENANCE_OBJECT_ID PARENT_OBJECT_ID FROM '||l_sql_stmt4||' '||l_where_clause||' )) '||l_from_date_clause||' )';