Oracle Fusion Maintenance Cloud - Maintenance Work Definition-Material- Resource Extract

SELECT

'KEY' AS "KEY",

ROWNUM SR_NO,

X.*

FROM

(

SELECT 

*

FROM

(

SELECT 

WWMNV.MAINTENANCE_WD_NAME Work_Definition_Name,

WWV.VERSION_NUMBER,

WWV.EFFECTIVE_FROM_DATE,

WWV.EFFECTIVE_TO_DATE,

WWMNV.ORGANIZATION_ID,

CASE WHEN (TRUNC(WWMNV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWMNV.LAST_UPDATE_DATE

WHEN (TRUNC(WWDV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWDV.LAST_UPDATE_DATE

WHEN (TRUNC(WWOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOB.LAST_UPDATE_DATE

WHEN (TRUNC(WWC.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWC.LAST_UPDATE_DATE

WHEN (TRUNC(WWOM.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOM.LAST_UPDATE_DATE

WHEN (TRUNC(WWOT.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOT.LAST_UPDATE_DATE

WHEN (TRUNC(WWORV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWORV.LAST_UPDATE_DATE

WHEN (TRUNC(WWV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWV.LAST_UPDATE_DATE

END AS LAST_UPDATE_DATE,

WWDV.WORK_DEFINITION_ID,

WWDV.WORK_DEFINITION_TYPE,

WWDV.WORK_DEFINITION_NAME_ID,

WWDV.WORK_METHOD_ID,

WWOB.WD_OPERATION_ID,

WWOB.WORK_CENTER_ID,

WWOB.OPERATION_SEQ_NUMBER,

WWOT.OPERATION_NAME,

WWC.WORK_CENTER_NAME,

WWOB.OPERATION_TYPE,

WWC.WORK_AREA_ID,

WWOM.WD_OPERATION_MATERIAL_ID,

WWOM.INVENTORY_ITEM_ID,

WWOM.MATERIAL_SEQ_NUMBER,

(select ITEM_NUMBER from egp_system_items_b where INVENTORY_ITEM_ID=WWOM.INVENTORY_ITEM_ID AND ROWNUM=1) ITEM,

WWOM.UOM_CODE,

WWOM.MATERIAL_QUANTITY,

WWOM.MATERIAL_TYPE,

WWORV.RESOURCE_ID,

WWORV.RESOURCE_NAME,

WWORV.RESOURCE_SEQ_NUMBER,

WWORV.ASSIGNED_UNITS,

WWORV.USAGE_RATE,

WWORV.UOM_CODE RESOURCE_UOM

FROM 

WIS_WD_MAINTENANCE_NAMES_VL WWMNV,

WIS_WORK_DEFINITIONS_V WWDV,

WIS_WD_OPERATIONS_B WWOB,

wis_work_centers_vl WWC,

WIS_WD_OPERATION_MATERIALS WWOM,

WIS_WD_OPERATIONS_TL WWOT,

WIS_WD_OPERATION_RESOURCES_V WWORV,

WIS_WD_VERSIONS WWV

WHERE 

WWMNV.WD_MNT_NAME_ID=WWDV.WD_MNT_NAME_ID

AND WWMNV.ORGANIZATION_ID=WWDV.ORGANIZATION_ID

AND WWOB.WORK_DEFINITION_ID=WWDV.WORK_DEFINITION_ID

AND WWOB.WORK_CENTER_ID=WWC.WORK_CENTER_ID

AND WWMNV.ORGANIZATION_ID=WWC.ORGANIZATION_ID

AND WWOB.WD_OPERATION_ID=WWOM.WD_OPERATION_ID

AND WWOT.LANGUAGE='US' 

AND WWOT.WD_OPERATION_ID=WWOB.WD_OPERATION_ID

AND WWOB.STANDARD_OPERATION_ID IS NULL

AND WWORV.WORK_CENTER_ID=WWOB.WORK_CENTER_ID

AND WWORV.WD_OPERATION_ID=WWOB.WD_OPERATION_ID

AND WWV.WORK_DEFINITION_ID=WWDV.WORK_DEFINITION_ID

-- AND WWMNV.MAINTENANCE_WD_CODE='Large Truck Service'

AND (

(TRUNC(WWMNV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWDV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWC.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOM.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOT.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWORV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

)

----STD OPERATION 

UNION ALL

SELECT 

WWMNV.MAINTENANCE_WD_NAME Work_Definition_Name,

WWV.VERSION_NUMBER,

WWV.EFFECTIVE_FROM_DATE,

WWV.EFFECTIVE_TO_DATE,

WWMNV.ORGANIZATION_ID,

CASE WHEN (TRUNC(WWMNV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWMNV.LAST_UPDATE_DATE

WHEN (TRUNC(WWDV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWDV.LAST_UPDATE_DATE

WHEN (TRUNC(WWOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOB.LAST_UPDATE_DATE

WHEN (TRUNC(WWC.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWC.LAST_UPDATE_DATE

WHEN (TRUNC(WSOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WSOB.LAST_UPDATE_DATE

WHEN (TRUNC(WWOM.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOM.LAST_UPDATE_DATE

WHEN (TRUNC(WWOT.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWOT.LAST_UPDATE_DATE

WHEN (TRUNC(WWORV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWORV.LAST_UPDATE_DATE

WHEN (TRUNC(WWV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) THEN WWV.LAST_UPDATE_DATE

END AS LAST_UPDATE_DATE,

WWDV.WORK_DEFINITION_ID,

WWDV.WORK_DEFINITION_TYPE,

WWDV.WORK_DEFINITION_NAME_ID,

WWDV.WORK_METHOD_ID,

WWOB.WD_OPERATION_ID,

WSOB.WORK_CENTER_ID,

WWOB.OPERATION_SEQ_NUMBER,

WSOB.STANDARD_OPERATION_CODE OPERATION_NAME,

WWC.WORK_CENTER_NAME,

WWOB.OPERATION_TYPE,

WWC.WORK_AREA_ID,

WWOM.WD_OPERATION_MATERIAL_ID,

WWOM.INVENTORY_ITEM_ID,

WWOM.MATERIAL_SEQ_NUMBER,

(select ITEM_NUMBER from egp_system_items_b where INVENTORY_ITEM_ID=WWOM.INVENTORY_ITEM_ID AND ROWNUM=1) ITEM,

WWOM.UOM_CODE,

WWOM.MATERIAL_QUANTITY,

WWOM.MATERIAL_TYPE,

WWORV.RESOURCE_ID,

WWORV.RESOURCE_NAME,

WWORV.RESOURCE_SEQ_NUMBER,

WWORV.ASSIGNED_UNITS,

WWORV.USAGE_RATE,

WWORV.UOM_CODE RESOURCE_UOM

FROM 

WIS_WD_MAINTENANCE_NAMES_VL WWMNV,

WIS_WORK_DEFINITIONS_V WWDV,

WIS_WD_OPERATIONS_B WWOB,

wis_work_centers_vl WWC,

WIS_STANDARD_OPERATIONS_B WSOB,

WIS_WD_OPERATION_MATERIALS WWOM,

WIS_WD_OPERATIONS_TL WWOT,

WIS_WD_OPERATION_RESOURCES_V WWORV,

WIS_WD_VERSIONS WWV

WHERE 

WWMNV.WD_MNT_NAME_ID=WWDV.WD_MNT_NAME_ID

AND WWMNV.ORGANIZATION_ID=WWDV.ORGANIZATION_ID

AND WWOB.WORK_DEFINITION_ID=WWDV.WORK_DEFINITION_ID

AND WSOB.STANDARD_OPERATION_ID=WWOB.STANDARD_OPERATION_ID

AND WSOB.WORK_CENTER_ID=WWC.WORK_CENTER_ID

AND WWMNV.ORGANIZATION_ID=WWC.ORGANIZATION_ID

AND WWOB.WD_OPERATION_ID=WWOM.WD_OPERATION_ID(+)

AND WWOT.LANGUAGE='US' 

AND WWOT.WD_OPERATION_ID=WWOB.WD_OPERATION_ID

AND WWOB.STANDARD_OPERATION_ID IS NOT NULL

AND WWORV.WORK_CENTER_ID=WSOB.WORK_CENTER_ID

AND WWORV.WD_OPERATION_ID=WWOB.WD_OPERATION_ID

AND WWV.WORK_DEFINITION_ID=WWDV.WORK_DEFINITION_ID

-- AND WWMNV.MAINTENANCE_WD_CODE='Large Truck Service'

AND (

(TRUNC(WWMNV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWDV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWC.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WSOB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOM.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWOT.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWORV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

OR (TRUNC(WWV.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) 

)

) 

ORDER BY WORK_DEFINITION_ID,VERSION_NUMBER,OPERATION_SEQ_NUMBER,MATERIAL_SEQ_NUMBER,RESOURCE_SEQ_NUMBER

) X