1. Tables and Views for SCM
  2. MNT_WR_AFFECTED_ASSETS_V

MNT_WR_AFFECTED_ASSETS_V

Details

  • Schema: FUSION

  • Object owner: MNT

  • Object type: VIEW

Columns

Name

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

CREATE_WORK_ORDER_OPTION_CODE

OPERATING_ORGANIZATION_ID

MAINTENANCE_ORGANIZATION_ID

Query

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,

NULL CREATE_WORK_ORDER_OPTION_CODE,

AssetOperatingOrgPEO.organization_id OPERATING_ORGANIZATION_ID,

CAST (decode(nvl(AssetOperatingOrgPEO.eam_enabled_flag,'N'), 'Y', AssetOperatingOrgPEO.organization_id,

NVL((select iop.organization_id

from fusion.inv_org_parameters iop, fusion.cse_org_relationships cor

where iop.organization_id = cor.organization_id

and cor.relationship_type_code = 'ORA_SUPPORTS_AS_PRIMARY'

and cor.related_organization_id = AssetEO.item_organization_id),

(select mp.organization_id

from fusion.mnt_programs_b mp, inv_org_parameters iop,

fusion.mnt_work_requirements_b mwr

where mp.organization_id = iop.organization_id

and mp.program_id = mwr.program_id

and mwr.requirement_id = WorkRequirementExcludedAssetEO.requirement_id))) AS NUMBER(18)) MAINTENANCE_ORGANIZATION_ID

FROM fusion.MNT_WR_EXCLUDED_ASSETS WorkRequirementExcludedAssetEO,

fusion.CSE_ASSETS_VL AssetEO,

fusion.INV_ORGANIZATION_DEFINITIONS_V AssetOperatingOrgPEO,

fusion.MNT_PROGRAMS_VL MaintenanceProgramEO,

fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO

WHERE AssetEO.ASSET_ID = WorkRequirementExcludedAssetEO.ASSET_ID

AND AssetEO.ITEM_ID = WorkRequirementExcludedAssetEO.ITEM_ID

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

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

AND AssetEO.ITEM_ORGANIZATION_ID = AssetOperatingOrgPEO.ORGANIZATION_ID

AND WorkRequirementExcludedAssetEO.requirement_id = WorkRequirementEO.requirement_id

AND WorkRequirementEO.program_id = MaintenanceProgramEO.program_id

AND

(

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'N'

AND MaintenanceProgramEO.organization_id = AssetEO.item_organization_id

)

OR

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'Y'

AND AssetEO.item_organization_id IN

(

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

WHERE inv1.master_organization_id = inv2.master_organization_id

AND inv2.organization_id = MaintenanceProgramEO.organization_id

)

)

)

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,

WorkRequirementIncludedAssetEO.CREATE_WORK_ORDER_OPTION_CODE,

AssetOperatingOrgPEO.organization_id OPERATING_ORGANIZATION_ID,

CAST (decode(nvl(AssetOperatingOrgPEO.eam_enabled_flag,'N'), 'Y', AssetOperatingOrgPEO.organization_id,

NVL((select iop.organization_id

from fusion.inv_org_parameters iop, fusion.cse_org_relationships cor

where iop.organization_id = cor.organization_id

and cor.relationship_type_code = 'ORA_SUPPORTS_AS_PRIMARY'

and cor.related_organization_id = AssetEO.item_organization_id),

(select mp.organization_id

from fusion.mnt_programs_b mp, inv_org_parameters iop,

fusion.mnt_work_requirements_b mwr

where mp.organization_id = iop.organization_id

and mp.program_id = mwr.program_id

and mwr.requirement_id = WorkRequirementIncludedAssetEO.requirement_id))) AS NUMBER(18)) MAINTENANCE_ORGANIZATION_ID

FROM fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO,

fusion.CSE_ASSETS_VL AssetEO,

fusion.INV_ORGANIZATION_DEFINITIONS_V AssetOperatingOrgPEO,

fusion.MNT_PROGRAMS_VL MaintenanceProgramEO,

fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO

WHERE AssetEO.ASSET_ID = WorkRequirementIncludedAssetEO.ASSET_ID

AND AssetEO.ITEM_ID = WorkRequirementIncludedAssetEO.ITEM_ID

AND AssetEO.MAINTAINABLE_FLAG = 'Y'

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

AND AssetEO.ITEM_ORGANIZATION_ID = AssetOperatingOrgPEO.ORGANIZATION_ID

AND WorkRequirementIncludedAssetEO.requirement_id = WorkRequirementEO.requirement_id

AND WorkRequirementEO.program_id = MaintenanceProgramEO.program_id

AND

(

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'N'

AND MaintenanceProgramEO.organization_id = AssetEO.item_organization_id

)

OR

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'Y'

AND AssetEO.item_organization_id IN

(

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

WHERE inv1.master_organization_id = inv2.master_organization_id

AND inv2.organization_id = MaintenanceProgramEO.organization_id

)

)

)

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,

WorkRequirementIncludedAssetEO.CREATE_WORK_ORDER_OPTION_CODE,

AssetOperatingOrgPEO.organization_id OPERATING_ORGANIZATION_ID,

CAST (decode(nvl(AssetOperatingOrgPEO.eam_enabled_flag,'N'), 'Y', AssetOperatingOrgPEO.organization_id,

NVL((select iop.organization_id

from fusion.inv_org_parameters iop, fusion.cse_org_relationships cor

where iop.organization_id = cor.organization_id

and cor.relationship_type_code = 'ORA_SUPPORTS_AS_PRIMARY'

and cor.related_organization_id = AssetEO.item_organization_id),

(select mp.organization_id

from fusion.mnt_programs_b mp, inv_org_parameters iop,

fusion.mnt_work_requirements_b mwr

where mp.organization_id = iop.organization_id

and mp.program_id = mwr.program_id

and mwr.requirement_id = WorkRequirementIncludedAssetEO.requirement_id))) AS NUMBER(18)) MAINTENANCE_ORGANIZATION_ID

FROM fusion.CSE_ASSETS_VL AssetEO, fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO

,fusion.MNT_WR_INCLUDED_ASSETS WorkRequirementIncludedAssetEO

,fusion.INV_ORGANIZATION_DEFINITIONS_V AssetOperatingOrgPEO

,fusion.MNT_PROGRAMS_VL MaintenanceProgramEO

WHERE WorkRequirementEO.item_id = AssetEO.item_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

AND AssetEO.ITEM_ORGANIZATION_ID = AssetOperatingOrgPEO.ORGANIZATION_ID

AND WorkRequirementEO.program_id = MaintenanceProgramEO.program_id

AND

(

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'N'

AND MaintenanceProgramEO.organization_id = AssetEO.item_organization_id

)

OR

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'Y'

AND AssetEO.item_organization_id IN

(

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

WHERE inv1.master_organization_id = inv2.master_organization_id

AND inv2.organization_id = MaintenanceProgramEO.organization_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,

WorkRequirementEO.CREATE_WORK_ORDER_OPTION_CODE,

AssetOperatingOrgPEO.organization_id OPERATING_ORGANIZATION_ID,

CAST (decode(nvl(AssetOperatingOrgPEO.eam_enabled_flag,'N'), 'Y', AssetOperatingOrgPEO.organization_id,

NVL((select iop.organization_id

from fusion.inv_org_parameters iop, fusion.cse_org_relationships cor

where iop.organization_id = cor.organization_id

and cor.relationship_type_code = 'ORA_SUPPORTS_AS_PRIMARY'

and cor.related_organization_id = AssetEO.item_organization_id),

(select mp.organization_id

from fusion.mnt_programs_b mp, inv_org_parameters iop

where mp.organization_id = iop.organization_id

and mp.program_id = WorkRequirementEO.program_id))) AS NUMBER(18)) MAINTENANCE_ORGANIZATION_ID

FROM fusion.CSE_ASSETS_VL AssetEO,

fusion.MNT_WORK_REQUIREMENTS_VL WorkRequirementEO,

fusion.INV_ORGANIZATION_DEFINITIONS_V AssetOperatingOrgPEO,

fusion.MNT_PROGRAMS_VL MaintenanceProgramEO

WHERE WorkRequirementEO.asset_id = AssetEO.asset_id

AND WorkRequirementEO.item_id = AssetEO.item_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

AND AssetEO.ITEM_ORGANIZATION_ID = AssetOperatingOrgPEO.ORGANIZATION_ID

AND WorkRequirementEO.program_id = MaintenanceProgramEO.program_id

AND

(

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'N'

AND MaintenanceProgramEO.organization_id = AssetEO.item_organization_id

)

OR

(

NVL(MaintenanceProgramEO.global_assets_flag, 'N') = 'Y'

AND AssetEO.item_organization_id IN

(

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

WHERE inv1.master_organization_id = inv2.master_organization_id

AND inv2.organization_id = MaintenanceProgramEO.organization_id

)

)

)