MNT_WORK_REQUIREMENTS_FACT

Details

Schema: FUSION
Object owner: MNT
Object type: VIEW

Columns

Name
REQUIREMENT_ID OBJECT_VERSION_NUMBER REQUIREMENT_NAME ORGANIZATION_ID PROGRAM_ID REQUIREMENT_TYPE_CODE ITEM_ID ASSET_ID STATUS_CODE FORECAST_USING_CYCLE_FLAG INTERVALS_IN_THE_CYCLE CALENDAR_BASED_FLAG METER_BASED_FLAG CONDITION_BASED_FLAG SCHEDULE_PATTERN_ID ACTIVE_START_DATE ACTIVE_END_DATE LAST_FORECAST_DATE SUPPRESS_MERGE_CODE SUPPRESS_MERGE_OVERRIDE_FLAG MODIFIED_FLAG NEXT_WORK_ORDER_ONLY_FLAG CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN REQUEST_ID JOB_DEFINITION_NAME JOB_DEFINITION_PACKAGE ASSET_ID_FINAL DAY_BASED_FLAG NUMBER_OF_DAYS NEXT_FORECAST_DUE_BY_CODE ASSET_HISTORY_SERVICE_DATE ASSET_HISTORY_SERVICE_INTERVAL ASSET_INCLUSION_TYPE_CODE REQUIREMENT_REFERENCE FORECAST_WINDOW_IN_DAYS WORK_ORDER_WINDOW_IN_DAYS CREATE_WORK_ORDER_OPTION_CODE WORK_ORDER_STATUS_ID WORK_ORDER_PRIORITY FIRM_PLANNED_FLAG WO_STATUS_ID WO_STATUS_NAME LANGUAGE

Name

REQUIREMENT_ID

OBJECT_VERSION_NUMBER

REQUIREMENT_NAME

ORGANIZATION_ID

PROGRAM_ID

REQUIREMENT_TYPE_CODE

ITEM_ID

ASSET_ID

STATUS_CODE

FORECAST_USING_CYCLE_FLAG

INTERVALS_IN_THE_CYCLE

CALENDAR_BASED_FLAG

METER_BASED_FLAG

CONDITION_BASED_FLAG

SCHEDULE_PATTERN_ID

ACTIVE_START_DATE

ACTIVE_END_DATE

LAST_FORECAST_DATE

SUPPRESS_MERGE_CODE

SUPPRESS_MERGE_OVERRIDE_FLAG

MODIFIED_FLAG

NEXT_WORK_ORDER_ONLY_FLAG

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

REQUEST_ID

JOB_DEFINITION_NAME

JOB_DEFINITION_PACKAGE

ASSET_ID_FINAL

DAY_BASED_FLAG

NUMBER_OF_DAYS

NEXT_FORECAST_DUE_BY_CODE

ASSET_HISTORY_SERVICE_DATE

ASSET_HISTORY_SERVICE_INTERVAL

ASSET_INCLUSION_TYPE_CODE

REQUIREMENT_REFERENCE

FORECAST_WINDOW_IN_DAYS

WORK_ORDER_WINDOW_IN_DAYS

CREATE_WORK_ORDER_OPTION_CODE

WORK_ORDER_STATUS_ID

WORK_ORDER_PRIORITY

FIRM_PLANNED_FLAG

WO_STATUS_ID

WO_STATUS_NAME

LANGUAGE

Query

SQL_Statement
SELECT work_req.requirement_id requirement_id, work_req.object_version_number object_version_number, work_req_tl.requirement_name requirement_name, work_req.organization_id organization_id, work_req.program_id program_id, work_req.requirement_type_code requirement_type_code, work_req.item_id item_id, nvl(work_req.asset_id, assets.asset_id) asset_id, work_req.status_code status_code, work_req.forecast_using_cycle_flag forecast_using_cycle_flag, work_req.intervals_in_the_cycle intervals_in_the_cycle, work_req.calendar_based_flag calendar_based_flag, work_req.meter_based_flag meter_based_flag, work_req.condition_based_flag condition_based_flag, work_req.schedule_pattern_id schedule_pattern_id, work_req.active_start_date active_start_date, work_req.active_end_date active_end_date, work_req.last_forecast_date last_forecast_date, work_req.suppress_merge_code suppress_merge_code, work_req.suppress_merge_override_flag suppress_merge_override_flag, work_req.modified_flag modified_flag, work_req.next_work_order_only_flag next_work_order_only_flag, work_req.created_by created_by, work_req.creation_date creation_date, work_req.last_updated_by last_updated_by, work_req.last_update_date last_update_date, work_req.last_update_login last_update_login, work_req.request_id request_id, work_req.job_definition_name job_definition_name, work_req.job_definition_package job_definition_package, nvl(assets.asset_id, work_req.asset_id) asset_id_final, work_req.Day_Based_Flag Day_Based_Flag, work_req.Number_Of_Days Number_Of_Days, work_req.Next_Forecast_Due_By_Code Next_Forecast_Due_By_Code, work_req.Asset_History_Service_Date Asset_History_Service_Date, work_req.Asset_History_Service_Interval Asset_History_Service_Interval, work_req.Asset_Inclusion_Type_Code Asset_Inclusion_Type_Code, work_req.Requirement_Reference Requirement_Reference, work_req.Forecast_Window_In_Days Forecast_Window_In_Days, work_req.Work_Order_Window_In_Days Work_Order_Window_In_Days, work_req.Create_Work_Order_Option_Code Create_Work_Order_Option_Code, work_req.Work_Order_Status_Id Work_Order_Status_Id, work_req.Work_Order_Priority Work_Order_Priority, work_req.Firm_Planned_Flag Firm_Planned_Flag, wo_statuses_tl.wo_status_id Wo_Status_id, wo_statuses_tl.wo_status_name Wo_Status_Name, wo_statuses_tl.language language FROM mnt_work_requirements_b work_req LEFT OUTER JOIN FUSION.mnt_work_requirements_tl work_req_tl ON work_req_tl.requirement_id = work_req.requirement_id AND work_req_tl.language = USERENV('LANG') LEFT OUTER JOIN FUSION.CSE_ASSETS_B assets ON work_req.ITEM_ID = assets.ITEM_ID AND assets.ITEM_ORGANIZATION_ID IN ( SELECT prog.organization_id FROM mnt_programs_b prog WHERE prog.program_id = work_req.program_id AND NVL(prog.global_assets_flag, 'N') = 'N' UNION ALL SELECT inv1.organization_id FROM fusion.inv_org_parameters inv1, fusion.inv_org_parameters inv2, mnt_programs_b prog WHERE inv1.master_organization_id = inv2.master_organization_id AND inv2.organization_id = prog.organization_id AND prog.program_id = work_req.program_id AND NVL(prog.global_assets_flag, 'N') = 'Y' ) AND work_req.REQUIREMENT_TYPE_CODE = 'ORA_ITEM' AND assets.MAINTAINABLE_FLAG = 'Y' AND assets.NEW_WO_ALLOWED_FLAG = 'Y' AND (assets.active_end_date is null OR assets.active_end_date > sysdate) AND ( (NVL(work_req.asset_inclusion_type_code,'ORA_AUTOMATIC') = 'ORA_AUTOMATIC' AND assets.asset_id NOT IN ( SELECT excluded_assets.asset_id FROM mnt_wr_excluded_assets excluded_assets WHERE work_req.requirement_id=excluded_assets.requirement_id)) OR (work_req.asset_inclusion_type_code = 'ORA_MANUAL' AND assets.asset_id IN ( SELECT included_assets.asset_id FROM mnt_wr_included_assets included_assets WHERE work_req.requirement_id=included_assets.requirement_id)) ) LEFT OUTER JOIN FUSION.egp_system_items_bv items on items.inventory_item_id = work_req.item_id and items.organization_id = work_req.organization_id and items.allow_maintenance_asset_flag = 'Y' LEFT OUTER JOIN FUSION.wie_wo_statuses_tl wo_statuses_tl ON work_req.work_order_status_Id = wo_statuses_tl.wo_status_Id AND wo_statuses_tl.language = USERENV('LANG')

SQL_Statement

SELECT work_req.requirement_id requirement_id,

work_req.object_version_number object_version_number,

work_req_tl.requirement_name requirement_name,

work_req.organization_id organization_id,

work_req.program_id program_id,

work_req.requirement_type_code requirement_type_code,

work_req.item_id item_id,

nvl(work_req.asset_id, assets.asset_id) asset_id,

work_req.status_code status_code,

work_req.forecast_using_cycle_flag forecast_using_cycle_flag,

work_req.intervals_in_the_cycle intervals_in_the_cycle,

work_req.calendar_based_flag calendar_based_flag,

work_req.meter_based_flag meter_based_flag,

work_req.condition_based_flag condition_based_flag,

work_req.schedule_pattern_id schedule_pattern_id,

work_req.active_start_date active_start_date,

work_req.active_end_date active_end_date,

work_req.last_forecast_date last_forecast_date,

work_req.suppress_merge_code suppress_merge_code,

work_req.suppress_merge_override_flag suppress_merge_override_flag,

work_req.modified_flag modified_flag,

work_req.next_work_order_only_flag next_work_order_only_flag,

work_req.created_by created_by,

work_req.creation_date creation_date,

work_req.last_updated_by last_updated_by,

work_req.last_update_date last_update_date,

work_req.last_update_login last_update_login,

work_req.request_id request_id,

work_req.job_definition_name job_definition_name,

work_req.job_definition_package job_definition_package,

nvl(assets.asset_id, work_req.asset_id) asset_id_final,

work_req.Day_Based_Flag Day_Based_Flag,

work_req.Number_Of_Days Number_Of_Days,

work_req.Next_Forecast_Due_By_Code Next_Forecast_Due_By_Code,

work_req.Asset_History_Service_Date Asset_History_Service_Date,

work_req.Asset_History_Service_Interval Asset_History_Service_Interval,

work_req.Asset_Inclusion_Type_Code Asset_Inclusion_Type_Code,

work_req.Requirement_Reference Requirement_Reference,

work_req.Forecast_Window_In_Days Forecast_Window_In_Days,

work_req.Work_Order_Window_In_Days Work_Order_Window_In_Days,

work_req.Create_Work_Order_Option_Code Create_Work_Order_Option_Code,

work_req.Work_Order_Status_Id Work_Order_Status_Id,

work_req.Work_Order_Priority Work_Order_Priority,

work_req.Firm_Planned_Flag Firm_Planned_Flag,

wo_statuses_tl.wo_status_id Wo_Status_id,

wo_statuses_tl.wo_status_name Wo_Status_Name,

wo_statuses_tl.language language

FROM mnt_work_requirements_b work_req

LEFT OUTER JOIN FUSION.mnt_work_requirements_tl work_req_tl

ON work_req_tl.requirement_id = work_req.requirement_id

AND work_req_tl.language = USERENV('LANG')

LEFT OUTER JOIN FUSION.CSE_ASSETS_B assets

ON work_req.ITEM_ID = assets.ITEM_ID

AND assets.ITEM_ORGANIZATION_ID IN

(

SELECT prog.organization_id FROM mnt_programs_b prog

WHERE prog.program_id = work_req.program_id

AND NVL(prog.global_assets_flag, 'N') = 'N'

UNION ALL

SELECT inv1.organization_id FROM fusion.inv_org_parameters inv1, fusion.inv_org_parameters inv2, mnt_programs_b prog

WHERE inv1.master_organization_id = inv2.master_organization_id

AND inv2.organization_id = prog.organization_id

AND prog.program_id = work_req.program_id

AND NVL(prog.global_assets_flag, 'N') = 'Y'

)

AND work_req.REQUIREMENT_TYPE_CODE = 'ORA_ITEM'

AND assets.MAINTAINABLE_FLAG = 'Y'

AND assets.NEW_WO_ALLOWED_FLAG = 'Y'

AND (assets.active_end_date is null OR assets.active_end_date > sysdate)

AND

(

(NVL(work_req.asset_inclusion_type_code,'ORA_AUTOMATIC') = 'ORA_AUTOMATIC'

AND assets.asset_id NOT IN (

SELECT excluded_assets.asset_id

FROM mnt_wr_excluded_assets excluded_assets

WHERE work_req.requirement_id=excluded_assets.requirement_id))

OR

(work_req.asset_inclusion_type_code = 'ORA_MANUAL'

AND assets.asset_id IN (

SELECT included_assets.asset_id

FROM mnt_wr_included_assets included_assets

WHERE work_req.requirement_id=included_assets.requirement_id))

)

LEFT OUTER JOIN FUSION.egp_system_items_bv items

on items.inventory_item_id = work_req.item_id

and items.organization_id = work_req.organization_id

and items.allow_maintenance_asset_flag = 'Y'

LEFT OUTER JOIN FUSION.wie_wo_statuses_tl wo_statuses_tl

ON work_req.work_order_status_Id = wo_statuses_tl.wo_status_Id

AND wo_statuses_tl.language = USERENV('LANG')