MNT_WR_AFFECTED_ASSETS

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 FORECAST_OPTION_CODE INTIALIZE_OPTION_CODE 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

Name

REQUIREMENT_ID

ASSET_ID

ITEM_ID

ORGANIZATION_ID

HISTORY_SERVICE_DATE

HISTORY_SERVICE_INTERVAL

REQUIREMENT_START_DATE

FORECAST_OPTION_CODE

INTIALIZE_OPTION_CODE

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 WorkRequirementEO.REQUIREMENT_ID, AssetEO.ASSET_ID, AssetEO.ITEM_ID, WorkRequirementEO.ORGANIZATION_ID, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL, WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE, WorkRequirementIncludedAssetEO.STATUS_CODE AS FORECAST_OPTION_CODE, CASE WHEN WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE IS NOT NULL THEN 'ORA_HISTORY_DATE' WHEN WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE IS NOT NULL THEN 'ORA_START_DATE' ELSE NULL END AS INTIALIZE_OPTION_CODE, case 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), WorkRequirementEO.organization_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, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE HISTORY_SERVICE_DATE, WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL HISTORY_SERVICE_INTERVAL, WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE REQUIREMENT_START_DATE, WorkRequirementIncludedAssetEO.STATUS_CODE AS FORECAST_OPTION_CODE, CASE WHEN WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE IS NOT NULL THEN 'ORA_HISTORY_DATE' WHEN WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE IS NOT NULL THEN 'ORA_START_DATE' ELSE NULL END AS INTIALIZE_OPTION_CODE, '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), WorkRequirementEO.organization_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.REQUIREMENT_ID = WorkRequirementIncludedAssetEO.REQUIREMENT_ID AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_MIXED_ASSETS' AND WorkRequirementIncludedAssetEO.ORGANIZATION_ID = WorkRequirementEO.ORGANIZATION_ID AND WorkRequirementIncludedAssetEO.ASSET_ID = AssetEO.ASSET_ID 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 ) ) ) 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, WorkRequirementEO.STATUS_CODE AS FORECAST_OPTION_CODE, CASE WHEN WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE IS NOT NULL THEN 'ORA_HISTORY_DATE' ELSE NULL END AS INTIALIZE_OPTION_CODE, '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), WorkRequirementEO.organization_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.REQUIREMENT_TYPE_CODE IN ('ORA_ASSET','ORA_ROUTE') 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 DECODE (WorkRequirementEO.requirement_type_code,'ORA_ROUTE', (SELECT 'Y' FROM cse_hierarchies_b chb WHERE chb.hierarchy_id = WorkRequirementEO.route_hierarchy_id AND chb.route_asset_id = WorkRequirementEO.asset_id AND chb.route_flag = 'Y' FETCH FIRST 1 ROW ONLY),'Y') = 'Y' 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 ) ) )

SQL_Statement

SELECT WorkRequirementEO.REQUIREMENT_ID,

AssetEO.ASSET_ID,

AssetEO.ITEM_ID,

WorkRequirementEO.ORGANIZATION_ID,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL,

WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE,

WorkRequirementIncludedAssetEO.STATUS_CODE AS FORECAST_OPTION_CODE,

CASE WHEN WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE IS NOT NULL

THEN 'ORA_HISTORY_DATE'

WHEN WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE IS NOT NULL

THEN 'ORA_START_DATE'

ELSE NULL

END AS INTIALIZE_OPTION_CODE,

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

WorkRequirementEO.organization_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,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE HISTORY_SERVICE_DATE,

WorkRequirementIncludedAssetEO.HISTORY_SERVICE_INTERVAL HISTORY_SERVICE_INTERVAL,

WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE REQUIREMENT_START_DATE,

WorkRequirementIncludedAssetEO.STATUS_CODE AS FORECAST_OPTION_CODE,

CASE WHEN WorkRequirementIncludedAssetEO.HISTORY_SERVICE_DATE IS NOT NULL

THEN 'ORA_HISTORY_DATE'

WHEN WorkRequirementIncludedAssetEO.REQUIREMENT_START_DATE IS NOT NULL

THEN 'ORA_START_DATE'

ELSE NULL

END AS INTIALIZE_OPTION_CODE,

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

WorkRequirementEO.organization_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.REQUIREMENT_ID = WorkRequirementIncludedAssetEO.REQUIREMENT_ID

AND WorkrequirementEO.REQUIREMENT_TYPE_CODE = 'ORA_MIXED_ASSETS'

AND WorkRequirementIncludedAssetEO.ORGANIZATION_ID = WorkRequirementEO.ORGANIZATION_ID

AND WorkRequirementIncludedAssetEO.ASSET_ID = AssetEO.ASSET_ID

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

)

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,

WorkRequirementEO.STATUS_CODE AS FORECAST_OPTION_CODE,

CASE WHEN WorkRequirementEO.ASSET_HISTORY_SERVICE_DATE IS NOT NULL

THEN 'ORA_HISTORY_DATE'

ELSE NULL

END AS INTIALIZE_OPTION_CODE,

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

WorkRequirementEO.organization_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.REQUIREMENT_TYPE_CODE IN ('ORA_ASSET','ORA_ROUTE')

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 DECODE (WorkRequirementEO.requirement_type_code,'ORA_ROUTE',

(SELECT 'Y' FROM cse_hierarchies_b chb

WHERE chb.hierarchy_id = WorkRequirementEO.route_hierarchy_id

AND chb.route_asset_id = WorkRequirementEO.asset_id

AND chb.route_flag = 'Y' FETCH FIRST 1 ROW ONLY),'Y') = 'Y'

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

)