MNT_WR_AFFECTED_ASSETS

詳細

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

列

名前
REQUIREMENT_ID ASSET_ID ITEM_ID ORGANIZATION_ID HISTORY_SERVICE_DATE HISTORY_SERVICE_INTERVAL REQUIREMENT_START_DATE STATUS_CODE ASSET_NUMBER DESCRIPTION NEW_WO_ALLOWED_FLAG SERIAL_NUMBER WORK_ORDER_CREATED_FLAG

名前

REQUIREMENT_ID

ASSET_ID

ITEM_ID

ORGANIZATION_ID

HISTORY_SERVICE_DATE

HISTORY_SERVICE_INTERVAL

REQUIREMENT_START_DATE

STATUS_CODE

ASSET_NUMBER

DESCRIPTION

NEW_WO_ALLOWED_FLAG

SERIAL_NUMBER

WORK_ORDER_CREATED_FLAG

問合せ

SQL_Statement
SELECT WorkRequirementExcludedAssetEO.REQUIREMENT_ID, WorkRequirementExcludedAssetEO.ASSET_ID, WorkRequirementExcludedAssetEO.ITEM_ID, WorkRequirementExcludedAssetEO.ORGANIZATION_ID, NULL HISTORY_SERVICE_DATE, NULL HISTORY_SERVICE_INTERVAL, NULL REQUIREMENT_START_DATE, 'ORA_EXCLUDE' AS STATUS_CODE, AssetEO.ASSET_NUMBER, AssetEO.DESCRIPTION, AssetEO.NEW_WO_ALLOWED_FLAG, AssetEO.SERIAL_NUMBER, (select 'Y' from fusion.cse_assets_vl where asset_id = AssetEO.asset_id and exists(select '1' from fusion.mnt_forecasts forecast, fusion.mnt_forecast_lines flines where forecast.forecast_id = flines.forecast_id and flines.requirement_id = WorkRequirementExcludedAssetEO.requirement_id and forecast.asset_id = AssetEO.asset_id and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG FROM fusion.MNT_WR_EXCLUDED_ASSETS WorkRequirementExcludedAssetEO, fusion.CSE_ASSETS_VL AssetEO WHERE AssetEO.ASSET_ID = WorkRequirementExcludedAssetEO.ASSET_ID AND AssetEO.ITEM_ID = WorkRequirementExcludedAssetEO.ITEM_ID AND AssetEO.ITEM_ORGANIZATION_ID = WorkRequirementExcludedAssetEO.ORGANIZATION_ID AND AssetEO.MAINTAINABLE_FLAG = 'Y' AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE UNION SELECT WorkRequirementIncludedAssetEO.REQUIREMENT_ID, WorkRequirementIncludedAssetEO.ASSET_ID, WorkRequirementIncludedAssetEO.ITEM_ID, WorkRequirementIncludedAssetEO.ORGANIZATION_ID, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL, WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE, 'ORA_INCLUDE' AS STATUS_CODE, AssetEO.ASSET_NUMBER, AssetEO.DESCRIPTION, AssetEO.NEW_WO_ALLOWED_FLAG, AssetEO.SERIAL_NUMBER, (select 'Y' from fusion.cse_assets_vl where asset_id = AssetEO.asset_id and exists(select '1' from fusion.mnt_forecasts forecast, fusion.mnt_forecast_lines flines where forecast.forecast_id = flines.forecast_id and flines.requirement_id = WorkRequirementIncludedAssetEO.requirement_id and forecast.asset_id = AssetEO.asset_id and forecast.work_order_id is not null)) AS WORK_ORDER_CREATED_FLAG FROM fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO, fusion.CSE_ASSETS_VL AssetEO WHERE AssetEO.ASSET_ID = WorkRequirementIncludedAssetEO.ASSET_ID AND AssetEO.ITEM_ID = WorkRequirementIncludedAssetEO.ITEM_ID AND AssetEO.ITEM_ORGANIZATION_ID = WorkRequirementIncludedAssetEO.ORGANIZATION_ID AND AssetEO.MAINTAINABLE_FLAG = 'Y' AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE UNION SELECT WorkRequirementEO.REQUIREMENT_ID, AssetEO.ASSET_ID, AssetEO.ITEM_ID, WorkRequirementEO.ORGANIZATION_ID, case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE end as HISTORY_SERVICE_DATE, case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.ASSET_HISTORY_SERVICE_INTERVAL when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL end as HISTORY_SERVICE_INTERVAL, case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.active_start_date when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE else WorkRequirementEO.active_start_date end as REQUIREMENT_START_DATE, case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then 'ORA_INCLUDE' when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' and WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE = 'ORA_MANUAL' and exists (select 'X' from fusion.MNT_WR_INCLUDED_ASSETS IA where WorkRequirementEO.REQUIREMENT_ID = IA.REQUIREMENT_ID and AssetEO.ASSET_ID = IA.ASSET_ID) then 'ORA_INCLUDE' when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' and (WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE = 'ORA_AUTOMATIC' or WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE is null) and not exists (select 'X' from fusion.MNT_WR_EXCLUDED_ASSETS EA where WorkRequirementEO.REQUIREMENT_ID = EA.REQUIREMENT_ID and AssetEO.ASSET_ID = EA.ASSET_ID) then 'ORA_INCLUDE' else 'ORA_EXCLUDE' end as STATUS_CODE, AssetEO.ASSET_NUMBER, AssetEO.DESCRIPTION, AssetEO.NEW_WO_ALLOWED_FLAG, AssetEO.SERIAL_NUMBER, (select 'Y' from fusion.cse_assets_vl where asset_id = AssetEO.asset_id and exists(select '1' from fusion.mnt_forecasts forecast, fusion.mnt_forecast_lines flines where forecast.forecast_id = flines.forecast_id and flines.requirement_id = WorkRequirementEO.requirement_id and forecast.asset_id = AssetEO.asset_id and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG FROM fusion.CSE_ASSETS_VL AssetEO, fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO , fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO WHERE WorkRequirementEO.item_id = AssetEO.item_id AND WorkRequirementEO.organization_id = AssetEO.ITEM_ORGANIZATION_ID AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_ITEM' AND AssetEO.MAINTAINABLE_FLAG = 'Y' AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE AND WorkRequirementIncludedAssetEO.REQUIREMENT_ID(+) = WorkRequirementEO.REQUIREMENT_ID AND WorkRequirementIncludedAssetEO.ORGANIZATION_ID(+)= WorkRequirementEO.ORGANIZATION_ID AND WorkRequirementIncludedAssetEO.ITEM_ID(+) = WorkRequirementEO.ITEM_ID AND WorkRequirementIncludedAssetEO.ASSET_ID(+)= AssetEO.ASSET_ID AND WorkRequirementIncludedAssetEO.ITEM_ID(+) = AssetEO.ITEM_ID UNION SELECT WorkRequirementEO.REQUIREMENT_ID, AssetEO.ASSET_ID, AssetEO.ITEM_ID, WorkRequirementEO.ORGANIZATION_ID, WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE HISTORY_SERVICE_DATE, WorkRequirementEO.ASSET_HISTORY_SERVICE_INTERVAL HISTORY_SERVICE_INTERVAL, WorkRequirementEO.active_start_date REQUIREMENT_START_DATE, 'ORA_INCLUDE' STATUS_CODE, AssetEO.ASSET_NUMBER, AssetEO.DESCRIPTION, AssetEO.NEW_WO_ALLOWED_FLAG, AssetEO.SERIAL_NUMBER, (select 'Y' from fusion.cse_assets_vl where asset_id = AssetEO.asset_id and exists(select '1' from fusion.mnt_forecasts forecast, fusion.mnt_forecast_lines flines where forecast.forecast_id = flines.forecast_id and flines.requirement_id = WorkRequirementEO.requirement_id and forecast.asset_id = AssetEO.asset_id and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG FROM fusion.CSE_ASSETS_VL AssetEO, fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO WHERE WorkRequirementEO.asset_id = AssetEO.asset_id AND WorkRequirementEO.item_id = AssetEO.item_id AND WorkRequirementEO.organization_id = AssetEO.ITEM_ORGANIZATION_ID AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_ASSET' AND WorkrequirementEO.STATUS_CODE = 'ORA_ACTIVE' AND AssetEO.MAINTAINABLE_FLAG = 'Y' AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE

SQL_Statement

SELECT WorkRequirementExcludedAssetEO.REQUIREMENT_ID,

WorkRequirementExcludedAssetEO.ASSET_ID,

WorkRequirementExcludedAssetEO.ITEM_ID,

WorkRequirementExcludedAssetEO.ORGANIZATION_ID,

NULL HISTORY_SERVICE_DATE,

NULL HISTORY_SERVICE_INTERVAL,

NULL REQUIREMENT_START_DATE,

'ORA_EXCLUDE' AS STATUS_CODE,

AssetEO.ASSET_NUMBER,

AssetEO.DESCRIPTION,

AssetEO.NEW_WO_ALLOWED_FLAG,

AssetEO.SERIAL_NUMBER,

(select 'Y'

from fusion.cse_assets_vl

where asset_id = AssetEO.asset_id

and exists(select '1'

from fusion.mnt_forecasts forecast,

fusion.mnt_forecast_lines flines

where forecast.forecast_id = flines.forecast_id

and flines.requirement_id = WorkRequirementExcludedAssetEO.requirement_id

and forecast.asset_id = AssetEO.asset_id

and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG

FROM fusion.MNT_WR_EXCLUDED_ASSETS WorkRequirementExcludedAssetEO,

fusion.CSE_ASSETS_VL AssetEO

WHERE AssetEO.ASSET_ID = WorkRequirementExcludedAssetEO.ASSET_ID

AND AssetEO.ITEM_ID = WorkRequirementExcludedAssetEO.ITEM_ID

AND AssetEO.ITEM_ORGANIZATION_ID = WorkRequirementExcludedAssetEO.ORGANIZATION_ID

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE

UNION

SELECT WorkRequirementIncludedAssetEO.REQUIREMENT_ID,

WorkRequirementIncludedAssetEO.ASSET_ID,

WorkRequirementIncludedAssetEO.ITEM_ID,

WorkRequirementIncludedAssetEO.ORGANIZATION_ID,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL,

WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE,

'ORA_INCLUDE' AS STATUS_CODE,

AssetEO.ASSET_NUMBER,

AssetEO.DESCRIPTION,

AssetEO.NEW_WO_ALLOWED_FLAG,

AssetEO.SERIAL_NUMBER,

(select 'Y'

from fusion.cse_assets_vl

where asset_id = AssetEO.asset_id

and exists(select '1'

from fusion.mnt_forecasts forecast,

fusion.mnt_forecast_lines flines

where forecast.forecast_id = flines.forecast_id

and flines.requirement_id = WorkRequirementIncludedAssetEO.requirement_id

and forecast.asset_id = AssetEO.asset_id

and forecast.work_order_id is not null)) AS WORK_ORDER_CREATED_FLAG

FROM fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO,

fusion.CSE_ASSETS_VL AssetEO

WHERE AssetEO.ASSET_ID = WorkRequirementIncludedAssetEO.ASSET_ID

AND AssetEO.ITEM_ID = WorkRequirementIncludedAssetEO.ITEM_ID

AND AssetEO.ITEM_ORGANIZATION_ID = WorkRequirementIncludedAssetEO.ORGANIZATION_ID

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE

UNION

SELECT WorkRequirementEO.REQUIREMENT_ID,

AssetEO.ASSET_ID,

AssetEO.ITEM_ID,

WorkRequirementEO.ORGANIZATION_ID,

case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE

when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE end as HISTORY_SERVICE_DATE,

case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.ASSET_HISTORY_SERVICE_INTERVAL

when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL end as HISTORY_SERVICE_INTERVAL,

case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then WorkRequirementEO.active_start_date

when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' then WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE

else WorkRequirementEO.active_start_date end as REQUIREMENT_START_DATE,

case when WorkRequirementEO.Requirement_type_code = 'ORA_ASSET' then 'ORA_INCLUDE'

when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' and WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE = 'ORA_MANUAL'

and exists (select 'X' from fusion.MNT_WR_INCLUDED_ASSETS IA where WorkRequirementEO.REQUIREMENT_ID = IA.REQUIREMENT_ID and AssetEO.ASSET_ID = IA.ASSET_ID) then 'ORA_INCLUDE'

when WorkRequirementEO.Requirement_type_code = 'ORA_ITEM' and (WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE = 'ORA_AUTOMATIC' or WorkRequirementEO.ASSET_INCLUSION_TYPE_CODE is null)

and not exists (select 'X' from fusion.MNT_WR_EXCLUDED_ASSETS EA where WorkRequirementEO.REQUIREMENT_ID = EA.REQUIREMENT_ID and AssetEO.ASSET_ID = EA.ASSET_ID) then 'ORA_INCLUDE'

else 'ORA_EXCLUDE' end as STATUS_CODE,

AssetEO.ASSET_NUMBER,

AssetEO.DESCRIPTION,

AssetEO.NEW_WO_ALLOWED_FLAG,

AssetEO.SERIAL_NUMBER,

(select 'Y'

from fusion.cse_assets_vl

where asset_id = AssetEO.asset_id

and exists(select '1'

from fusion.mnt_forecasts forecast,

fusion.mnt_forecast_lines flines

where forecast.forecast_id = flines.forecast_id

and flines.requirement_id = WorkRequirementEO.requirement_id

and forecast.asset_id = AssetEO.asset_id

and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG

FROM fusion.CSE_ASSETS_VL AssetEO, fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO

, fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO

WHERE WorkRequirementEO.item_id = AssetEO.item_id

AND WorkRequirementEO.organization_id = AssetEO.ITEM_ORGANIZATION_ID

AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_ITEM'

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE

AND WorkRequirementIncludedAssetEO.REQUIREMENT_ID(+) = WorkRequirementEO.REQUIREMENT_ID

AND WorkRequirementIncludedAssetEO.ORGANIZATION_ID(+)= WorkRequirementEO.ORGANIZATION_ID

AND WorkRequirementIncludedAssetEO.ITEM_ID(+) = WorkRequirementEO.ITEM_ID

AND WorkRequirementIncludedAssetEO.ASSET_ID(+)= AssetEO.ASSET_ID

AND WorkRequirementIncludedAssetEO.ITEM_ID(+) = AssetEO.ITEM_ID

UNION

SELECT WorkRequirementEO.REQUIREMENT_ID,

AssetEO.ASSET_ID,

AssetEO.ITEM_ID,

WorkRequirementEO.ORGANIZATION_ID,

WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE HISTORY_SERVICE_DATE,

WorkRequirementEO.ASSET_HISTORY_SERVICE_INTERVAL HISTORY_SERVICE_INTERVAL,

WorkRequirementEO.active_start_date REQUIREMENT_START_DATE,

'ORA_INCLUDE' STATUS_CODE,

AssetEO.ASSET_NUMBER,

AssetEO.DESCRIPTION,

AssetEO.NEW_WO_ALLOWED_FLAG,

AssetEO.SERIAL_NUMBER,

(select 'Y'

from fusion.cse_assets_vl

where asset_id = AssetEO.asset_id

and exists(select '1'

from fusion.mnt_forecasts forecast,

fusion.mnt_forecast_lines flines

where forecast.forecast_id = flines.forecast_id

and flines.requirement_id = WorkRequirementEO.requirement_id

and forecast.asset_id = AssetEO.asset_id

and forecast.work_order_id is not null)) WORK_ORDER_CREATED_FLAG

FROM fusion.CSE_ASSETS_VL AssetEO, fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO

WHERE WorkRequirementEO.asset_id = AssetEO.asset_id

AND WorkRequirementEO.item_id = AssetEO.item_id

AND WorkRequirementEO.organization_id = AssetEO.ITEM_ORGANIZATION_ID

AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_ASSET'

AND WorkrequirementEO.STATUS_CODE = 'ORA_ACTIVE'

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

AND NVL(AssetEO.ACTIVE_END_DATE,SYSDATE+1) > SYSDATE