Oracle Fusion Maintenance Cloud - Asset Work Order Resource Extract Query

select 

'KEY' AS "KEY",

ROWNUM SR_NO,

WWO.WORK_ORDER_ID,

WWO.ORGANIZATION_ID,

HOU.NAME ORG_NAME,

WWO.WORK_ORDER_PRIORITY,

WWO.WORK_ORDER_NUMBER,

WWOT.WORK_ORDER_DESCRIPTION,

WWST.WO_STATUS_NAME,

WWA.WO_ASSET_ID,

CAB.ASSET_ID,

CAB.ASSET_NUMBER,

CAV.DESCRIPTION,

CAB.SERIAL_NUMBER,

WWO.PLANNED_START_DATE WO_START_DATE,

WWO.PLANNED_COMPLETION_DATE WO_COMPLETION_DATE,

CASE WHEN (WWO.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWO.LAST_UPDATE_DATE

WHEN (WWOT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWOT.LAST_UPDATE_DATE

WHEN (WWA.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWA.LAST_UPDATE_DATE

WHEN (WWST.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWST.LAST_UPDATE_DATE

WHEN (WWD.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWD.LAST_UPDATE_DATE

WHEN (WWDNT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWDNT.LAST_UPDATE_DATE

WHEN (WWOV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWOV.LAST_UPDATE_DATE

WHEN (WWORV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWORV.LAST_UPDATE_DATE

WHEN (WRT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WRT.LAST_UPDATE_DATE

WHEN (WRB.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WRB.LAST_UPDATE_DATE

END AS LAST_UPDATE_DATE,

WWO.WORK_ORDER_TYPE,

WWO.WORK_ORDER_SUB_TYPE,

WWO.WORK_DEFINITION_ID,

WWDNT.WORK_DEF_NAME,

WWOV.WD_OPERATION_ID,

WWOV.WORK_CENTER_ID,

WWOV.OPERATION_SEQ_NUMBER,

WWOV.OPERATION_DESCRIPTION OPERATION_CODE,

WWOV.OPERATION_TYPE,

WWOV.OPERATION_NAME,

WWOV.WORK_CENTER_CODE,

WWOV.PLANNED_START_DATE OPERATION_START_DATE,

WWOV.PLANNED_COMPLETION_DATE OPERATION_END_DATE,

WWOV.WO_OPERATION_ID,

/*

WWOMV.WO_OPERATION_MATERIAL_ID,

WWOMV.MATERIAL_SEQ_NUMBER,

WWOMV.INVENTORY_ITEM_ID,

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

(SELECT 

ESIT.DESCRIPTION

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = WWOMV.INVENTORY_ITEM_ID

AND ROWNUM=1

)     ITEM_DESC,

WWOMV.QUANTITY REQUIRED_QTY,

WWOMV.UOM_CODE ITEM_UOM,

WWOMV.REQUIRED_DATE,

WWOMV.SUPPLY_TYPE,

WWOMV.SUPPLY_SUBINVENTORY,

*/

WWORV.WO_OPERATION_RESOURCE_ID,

WWORV.RESOURCE_ID,

WWORV.RESOURCE_SEQ_NUMBER,

WRT.RESOURCE_NAME,

DECODE(WWORV.BASIS_TYPE,1,'Variable',2,'Fixed') BASIS,

WWORV.ASSIGNED_UNITS AVAILABLE_UNIT,

WWORV.USAGE_RATE,

WWORV.REQUIRED_USAGE,

WWORV.UOM_CODE RESOURCE_UOM,

WRB.RESOURCE_TYPE,

WWORV.SCHEDULED_FLAG,

WWORV.CHARGE_TYPE

FROM 

WIE_WORK_ORDERS_B WWO,

HR_ORGANIZATION_UNITS HOU,

WIE_WORK_ORDERS_TL WWOT,

WIE_WO_ASSETS WWA,

CSE_ASSETS_B CAB,

CSE_ASSETS_VL CAV,

WIE_WO_STATUSES_TL WWST,

WIS_WORK_DEFINITIONS WWD,

WIS_WORK_DEFINITION_NAMES_TL WWDNT,

WIE_WO_OPERATIONS_V  WWOV,

-- WIE_WO_OPERATION_MATERIALS_V WWOMV,

WIE_WO_OPERATION_RESOURCES WWORV,

WIS_RESOURCES_B WRB,

WIS_RESOURCES_TL WRT

WHERE 

WWO.ORGANIZATION_ID=HOU.ORGANIZATION_ID

AND WWO.WORK_ORDER_ID=WWOT.WORK_ORDER_ID

AND WWOT.SOURCE_LANG='US'

AND WWO.WORK_ORDER_ID=WWA.WORK_ORDER_ID(+)  --- Outer join to fetch all work order data even asset detail is not available

AND WWO.ORGANIZATION_ID=WWA.ORGANIZATION_ID(+) --- Outer join to fetch all work order data even asset detail is not available

AND WWA.ASSET_ID = CAB.ASSET_ID

AND CAB.ASSET_ID = CAV.ASSET_ID

AND WWO.WORK_ORDER_STATUS_ID=WWST.WO_STATUS_ID

AND WWST.LANGUAGE='US' 

AND WWO.WORK_DEFINITION_ID=WWD.WORK_DEFINITION_ID(+)    ---Outer Join Fetch WO data even Work Definition is blank

AND WWO.ORGANIZATION_ID=WWD.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Work Definition is blank

AND WWD.WORK_DEFINITION_NAME_ID=WWDNT.WORK_DEFINITION_NAME_ID

AND WWDNT.LANGUAGE='US' 

AND WWO.WORK_ORDER_ID=WWOV.WORK_ORDER_ID(+) ---Outer Join Fetch WO data even Operation data is null

AND WWO.ORGANIZATION_ID=WWOV.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Operation data is null

-- AND WWO.WORK_ORDER_ID=WWOMV.WORK_ORDER_ID(+) ---Outer Join Fetch WO data even Material data is null

-- AND WWO.ORGANIZATION_ID=WWOMV.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Material data is null

-- AND WWOV.WO_OPERATION_ID=WWOMV.WO_OPERATION_ID

AND WWOV.WO_OPERATION_ID=WWORV.WO_OPERATION_ID(+)

AND WWOV.ORGANIZATION_ID=WWORV.ORGANIZATION_ID(+)

AND WRB.RESOURCE_ID=WWORV.RESOURCE_ID

AND WRB.ORGANIZATION_ID = WWORV.ORGANIZATION_ID

AND WRT.RESOURCE_ID=WRB.RESOURCE_ID

AND WRT.SOURCE_LANG='US'

--AND WWO.WORK_ORDER_NUMBER='GDI2797'

AND (

(WWO.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWOT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss'))

OR (WWA.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWST.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWD.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWDNT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWOV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWORV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WRB.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WRT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

)

Oracle Fusion Maintenance Cloud - Asset Work Order Material Extract Query

select 

'KEY' AS "KEY",

ROWNUM SR_NO,

WWO.WORK_ORDER_ID,

WWO.ORGANIZATION_ID,

HOU.NAME ORG_NAME,

WWO.WORK_ORDER_PRIORITY,

WWO.WORK_ORDER_NUMBER,

WWOT.WORK_ORDER_DESCRIPTION,

WWST.WO_STATUS_NAME,

WWA.WO_ASSET_ID,

CAB.ASSET_ID,

CAB.ASSET_NUMBER,

CAV.DESCRIPTION,

CAB.SERIAL_NUMBER,

WWO.PLANNED_START_DATE WO_START_DATE,

WWO.PLANNED_COMPLETION_DATE WO_COMPLETION_DATE,

CASE WHEN (WWO.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWO.LAST_UPDATE_DATE

WHEN (WWOT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWOT.LAST_UPDATE_DATE

WHEN (WWA.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWA.LAST_UPDATE_DATE

WHEN (WWST.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWST.LAST_UPDATE_DATE

WHEN (WWD.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWD.LAST_UPDATE_DATE

WHEN (WWDNT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWDNT.LAST_UPDATE_DATE

WHEN (WWOV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWOV.LAST_UPDATE_DATE

WHEN (WWOMV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) THEN WWOMV.LAST_UPDATE_DATE

END AS LAST_UPDATE_DATE,

WWO.WORK_ORDER_TYPE,

WWO.WORK_ORDER_SUB_TYPE,

WWO.WORK_DEFINITION_ID,

WWDNT.WORK_DEF_NAME,

WWOV.WD_OPERATION_ID,

WWOV.WORK_CENTER_ID,

WWOV.OPERATION_SEQ_NUMBER,

WWOV.OPERATION_DESCRIPTION OPERATION_CODE,

WWOV.OPERATION_TYPE,

WWOV.OPERATION_NAME,

WWOV.WORK_CENTER_CODE,

WWOV.PLANNED_START_DATE OPERATION_START_DATE,

WWOV.PLANNED_COMPLETION_DATE OPERATION_END_DATE,

WWOV.WO_OPERATION_ID,

WWOMV.WO_OPERATION_MATERIAL_ID,

WWOMV.MATERIAL_SEQ_NUMBER,

WWOMV.INVENTORY_ITEM_ID,

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

(SELECT 

ESIT.DESCRIPTION

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = WWOMV.INVENTORY_ITEM_ID

AND ROWNUM=1

)     ITEM_DESC,

WWOMV.QUANTITY REQUIRED_QTY,

WWOMV.UOM_CODE ITEM_UOM,

WWOMV.REQUIRED_DATE,

(select meaning from WIE_WO_SUPPLY_TYPES_V where LOOKUP_CODE=WWOMV.SUPPLY_TYPE) SUPPLY_TYPE,

WWOMV.SUPPLY_SUBINVENTORY

FROM 

WIE_WORK_ORDERS_B WWO,

HR_ORGANIZATION_UNITS HOU,

WIE_WORK_ORDERS_TL WWOT,

WIE_WO_ASSETS WWA,

CSE_ASSETS_B CAB,

CSE_ASSETS_VL CAV,

WIE_WO_STATUSES_TL WWST,

WIS_WORK_DEFINITIONS WWD,

WIS_WORK_DEFINITION_NAMES_TL WWDNT,

WIE_WO_OPERATIONS_V  WWOV,

WIE_WO_OPERATION_MATERIALS_V WWOMV

WHERE 

WWO.ORGANIZATION_ID=HOU.ORGANIZATION_ID

AND WWO.WORK_ORDER_ID=WWOT.WORK_ORDER_ID

AND WWOT.SOURCE_LANG='US'

AND WWO.WORK_ORDER_ID=WWA.WORK_ORDER_ID(+)  --- Outer join to fetch all work order data even asset detail is not available

AND WWO.ORGANIZATION_ID=WWA.ORGANIZATION_ID(+) --- Outer join to fetch all work order data even asset detail is not available

AND WWA.ASSET_ID = CAB.ASSET_ID

AND CAB.ASSET_ID = CAV.ASSET_ID

AND WWO.WORK_ORDER_STATUS_ID=WWST.WO_STATUS_ID

AND WWST.LANGUAGE='US' 

AND WWO.WORK_DEFINITION_ID=WWD.WORK_DEFINITION_ID(+)    ---Outer Join Fetch WO data even Work Definition is blank

AND WWO.ORGANIZATION_ID=WWD.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Work Definition is blank

AND WWD.WORK_DEFINITION_NAME_ID=WWDNT.WORK_DEFINITION_NAME_ID

AND WWDNT.LANGUAGE='US' 

AND WWO.WORK_ORDER_ID=WWOV.WORK_ORDER_ID(+) ---Outer Join Fetch WO data even Operation data is null

AND WWO.ORGANIZATION_ID=WWOV.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Operation data is null

AND WWO.WORK_ORDER_ID=WWOMV.WORK_ORDER_ID(+) ---Outer Join Fetch WO data even Material data is null

AND WWO.ORGANIZATION_ID=WWOMV.ORGANIZATION_ID(+) ---Outer Join Fetch WO data even Material data is null

AND WWOV.WO_OPERATION_ID=WWOMV.WO_OPERATION_ID

--AND WWO.WORK_ORDER_NUMBER='GDI2797'

AND (

(WWO.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWOT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss'))

OR (WWA.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWST.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWD.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWDNT.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWOV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

OR (WWOMV.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DT,'mm-dd-yyyy hh24:mi:ss')  AND TO_DATE(:P_END_DT,'mm-dd-yyyy hh24:mi:ss')) 

)

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

Oracle Fusion Maintenance Cloud - Work Order Maintenance Cost Details Extract Query

   SELECT *

    FROM (SELECT CWO.CST_WORK_ORDER_ID,

                 CWO.WORK_ORDER_NUMBER,

                 MNW.WORK_ORDER_DESCRIPTION,

                -- CACT.TRANSACTION_DATE,

                 DECODE (CWO.WORK_ORDER_TYPE, 'CORRECTIVE', 'CM', 'PM') WO_TYPE,

                 MNW.WORK_ORDER_PRIORITY,

                 (SELECT NAME

                    FROM HR_ORGANIZATION_UNITS

                   WHERE ORGANIZATION_ID = CACT.INVENTORY_ORG_ID)

                    RIG_OPERATING_ORG,

                 NULL WORK_GROUP, 

                 NVL (

                    (SELECT SUM (QUANTITY * UNIT_COST * WIP_TXN_SIGN)

                       FROM CST_WORK_ORDER_COSTS CWOS

                      WHERE     CWOS.COST_ORG_ID = CACT.COST_ORG_ID

                            AND CWOS.COST_BOOK_ID = CACT.COST_BOOK_ID

                            AND CWOS.CST_WORK_ORDER_ID = CACT.CST_WORK_ORDER_ID

                            AND CWOS.WIP_COST_TYPE = 'COMPONENT'),

                    0)

                    MATERIAL_ACTUAL,

                 NVL (

                    (SELECT SUM (QUANTITY * UNIT_COST * WIP_TXN_SIGN)

                       FROM CST_WORK_ORDER_COSTS CWOS

                      WHERE     CWOS.COST_ORG_ID = CACT.COST_ORG_ID

                            AND CWOS.COST_BOOK_ID = CACT.COST_BOOK_ID

                            AND CWOS.CST_WORK_ORDER_ID = CACT.CST_WORK_ORDER_ID

                            AND CWOS.WIP_COST_TYPE = 'RESOURCE'),

                    0)

                    SERVICE_ACTUAL,

                   NVL (

                      (SELECT SUM (QUANTITY * UNIT_COST * WIP_TXN_SIGN)

                         FROM CST_WORK_ORDER_COSTS CWOS

                        WHERE     CWOS.COST_ORG_ID = CACT.COST_ORG_ID

                              AND CWOS.COST_BOOK_ID = CACT.COST_BOOK_ID

                              AND CWOS.CST_WORK_ORDER_ID =

                                     CACT.CST_WORK_ORDER_ID

                              AND CWOS.WIP_COST_TYPE = 'COMPONENT'),

                      0)

                 + NVL (

                      (SELECT SUM (QUANTITY * UNIT_COST * WIP_TXN_SIGN)

                         FROM CST_WORK_ORDER_COSTS CWOS

                        WHERE     CWOS.COST_ORG_ID = CACT.COST_ORG_ID

                              AND CWOS.COST_BOOK_ID = CACT.COST_BOOK_ID

                              AND CWOS.CST_WORK_ORDER_ID =

                                     CACT.CST_WORK_ORDER_ID

                              AND CWOS.WIP_COST_TYPE = 'RESOURCE'),

                      0)

                    TOTAL_COST,

                 CACT.COST_ORG_ID,

                 CACT.COST_BOOK_ID,

                 CACT.INVENTORY_ORG_ID

            FROM CST_WORK_ORDERS CWO,

                 wie_work_orders_vl MNW,

                 CST_ALL_COST_TRANSACTIONS_V CACT

           WHERE     CWO.WORK_METHOD_ID = MNW.WORK_METHOD_ID

                 AND CWO.WORK_ORDER_NUMBER = MNW.WORK_ORDER_NUMBER

                 AND CACT.CST_WORK_ORDER_ID = CWO.CST_WORK_ORDER_ID

                 AND CACT.TXN_SOURCE_REF_DOC_NUMBER = CWO.WORK_ORDER_NUMBER

                 AND TRUNC (CACT.TRANSACTION_DATE) >=

                        TRUNC (NVL (:p_from_date, CACT.TRANSACTION_DATE))

                 AND TRUNC (CACT.TRANSACTION_DATE) <=

                        TRUNC (NVL (:p_to_date, CACT.TRANSACTION_DATE))

                 AND CACT.INVENTORY_ORG_ID =

                        NVL (:p_org, CACT.INVENTORY_ORG_ID)

AND CWO.WORK_ORDER_TYPE= NVL(:p_work_type,CWO.WORK_ORDER_TYPE)

                        ) X

GROUP BY CST_WORK_ORDER_ID,

         WORK_ORDER_NUMBER,

         WORK_ORDER_DESCRIPTION,

        -- TRANSACTION_DATE,

         WO_TYPE,

         RIG_OPERATING_ORG,

         WORK_GROUP,

         WORK_ORDER_PRIORITY,

         MATERIAL_ACTUAL,

         SERVICE_ACTUAL,

         TOTAL_COST,

         COST_ORG_ID,

         COST_BOOK_ID,

         INVENTORY_ORG_ID

ORDER BY WORK_ORDER_NUMBER

Oracle Fusion Maintenance Cloud - Maintenance Asset Details Query

 SELECT 

  'KEY' AS "KEY",

ROWNUM SR_NO,

       CAB.ASSET_ID,

       CAB.ASSET_NUMBER,

       CAV.DESCRIPTION,

       (SELECT ASSET_NUMBER

          FROM CSE_ASSETS_B

         WHERE ASSET_ID = (SELECT PARENT_ASSET_ID

                             FROM CSE_AS_BUILT_RELATIONS_V

                            WHERE CHILD_ASSET_ID = CAB.ASSET_ID AND ROWNUM=1))

          PARENT_ASSET,

       (SELECT 

ESI.ITEM_NUMBER

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = CAB.ITEM_ID

AND ESIT.ORGANIZATION_ID=CAB.ITEM_ORGANIZATION_ID

)ITEM_NUMBER,

       (SELECT 

ESIT.DESCRIPTION

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = CAB.ITEM_ID

AND ESIT.ORGANIZATION_ID=CAB.ITEM_ORGANIZATION_ID

)     IETEM_DESC,

       CAB.SERIAL_NUMBER,

       (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID=(select ITEM_ORGANIZATION_ID

from

CSE_ASSETS_HIST where TRANSACTED_ASSET_ID =

(select MIN(TRANSACTED_ASSET_ID) from CSE_ASSETS_HIST where asset_id = CAB.asset_id)) )

          MAINTANENCE_ORG,                                   --MAINTENANCE ORG

       (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID = CAB.ITEM_ORGANIZATION_ID)

          OPERATING_ORG,                                       --OPERATING ORG

       CAB.CURRENT_LOCATION_CONTEXT,                          -- LOCATION TYPE

       (SELECT HP.ADDRESS1

        || ', '

        || HP.ADDRESS1

        || ', '

        || HP.ADDRESS3

        || ', '

        || HP.CITY

        || ', '

        || HP.COUNTRY FROM HZ_PARTIES HP WHERE HP.PARTY_ID=CAB.CONTACT_ID )

          LOCATION,                                               -- LOCATION,

       CAB.CONTACT_ID,

       (SELECT HP.PARTY_NAME FROM HZ_PARTIES HP WHERE HP.PARTY_ID=CAB.CONTACT_ID) CONTACT,                                     

       TO_CHAR(CAB.CREATION_DATE,'DD-MM-YY HH:MI:SS') CREATION_DATE,                              

       TO_CHAR(CAB.ACTIVE_END_DATE,'DD-MM-YY HH:MI:SS') ACTIVE_END_DATE,                                  

       CAB.ATTRIBUTE_CHAR1 ASSET_STATUS

  FROM CSE_ASSETS_B CAB,

       CSE_ASSETS_VL CAV

 WHERE     CAB.ASSET_ID = CAV.ASSET_ID

 AND ((TRUNC(CAB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) OR TRUNC(CAB.LAST_UPDATE_DATE)= TRUNC(SYSDATE))

Oracle Fusion Maintenance Cloud - Maintenance Asset Meter Details Extract Query

 

SELECT

'KEY' AS "KEY",

 ROWNUM SR_NO,

       CAB.ASSET_ID,

       CAB.ASSET_NUMBER,

       CAV.DESCRIPTION Asset_Description,

   (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID = CAB.ITEM_ORGANIZATION_ID)

          OPERATING_ORG,

  CAB.ATTRIBUTE_CHAR2 Primary_Custodian,

CM.METER_ID, 

CM.METER_DEFINITION_ID,

CMDT.METER_NAME,

CMDB.METER_CODE,

CMDB.UOM_CODE,

CM.ACTIVE_START_DATE,

CM.ACTIVE_END_DATE,

CMDB.INITIAL_READING_VALUE,

(select READING_DATE from CSE_METER_READINGS where meter_id=CM.METER_ID and INITIAL_FLAG='Y') Intial_Reading_Date,

(select READING_VALUE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Last_Reading,

(select READING_DATE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Last_Reading_Date,

(select READING_VALUE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Life_to_date_Reading,

(select WORK_ORDER_NUMBER 

from MNT_WORK_ORDERS_V where WORK_ORDER_ID =

(select WORK_ORDER_ID from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

) )Work_Order,

CM.DAILY_UTILIZATION_RATE Base_Utilisation_Rate,

decode(CM.ALLOW_IN_MAINT_PROGRAM_FLAG,'Y','YES','NO') Allow_Maintenance,

CM.CREATED_BY Recorded_By

FROM CSE_ASSETS_B CAB,

       CSE_ASSETS_VL CAV,

  CSE_METER_DEFINITIONS_B CMDB,

  CSE_METER_DEFINITIONS_TL CMDT,

  CSE_METERS CM

 WHERE     CAB.ASSET_ID = CAV.ASSET_ID

 AND CMDB.METER_DEFINITION_ID=CMDT.METER_DEFINITION_ID

 AND CMDT.LANGUAGE='US'

 AND CM.METER_DEFINITION_ID=CMDB.METER_DEFINITION_ID

 AND CM.meter_object_id=CAB.ASSET_ID

 AND ((TRUNC(CAB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) OR TRUNC(CAB.LAST_UPDATE_DATE)= TRUNC(SYSDATE))