MNT_WORK_REQUIREMENTS_FACT

詳細

スキーマ: FUSION
オブジェクト所有者: MNT
オブジェクト・タイプ: VIEW

列

名前
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

名前

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

問合せ

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 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 work_req.ORGANIZATION_ID = assets.ITEM_ORGANIZATION_ID 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 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) 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'

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

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 work_req.ORGANIZATION_ID = assets.ITEM_ORGANIZATION_ID

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

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'