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')) 

)