VIEW: APPS.AHL_SIMULATION_FORECAST_V

Source

View Text - Preformatted

SELECT 
sim.simulation_plan_id,
sim.simulation_ue_id,
sim.due_date,
sim.fleet_header_id,
sim.forecast_sequence,
sim.repetitive_mr_flag,
unt.unit_config_header_id, 
unt.name unit,
itm.instance_id csi_instance_id,
itm.serial_number,
mtl.concatenated_segments item_name,
mc.name master_configuration,
mc.mc_header_id,
flt.operating_org_id,
(SELECT hou.name
 FROM hr_all_organization_units hou, mtl_parameters mp
 WHERE hou.organization_id = flt.operating_org_id
 AND mp.organization_id = flt.operating_org_id
 AND mp.eam_enabled_flag = 'Y'
)OPERATING_ORG,
ueo.mr_maintenance_org_id,
ueo.mr_maintenance_org_name maintenance_org,
flt.name fleet_name,
mr.title mr_title,
mr.mr_header_id,
mr.program_type_code,
(SELECT meaning
FROM FND_LOOKUPS
    WHERE lookup_type = 'AHL_FMP_MR_PROGRAM_TYPE'
    AND lookup_code = mr.program_type_code) PROGRAM_TYPE,
mr.program_subtype_code,
(SELECT meaning
FROM FND_LOOKUPS
    WHERE lookup_type = 'AHL_FMP_MR_PROGRAM_SUBTYPE'
    AND lookup_code = mr.program_subtype_code ) PROGRAM_SUBTYPE,
mr.down_time,
mr.service_category_rank SERVICE_CATEGORY_CODE
FROM AHL_UNIT_CONFIG_HEADERS unt,
AHL_UE_SIMULATIONS sim,
CSI_ITEM_INSTANCES itm,
MTL_SYSTEM_ITEMS_KFV mtl,
AHL_MC_HEADERS_B mc,
AHL_FLEET_HEADERS_B flt,
AHL_MR_HEADERS_B mr,
AHL_UE_ORGS_DEPTS_V ueo
WHERE unt.unit_config_header_id(+) = sim.unit_config_header_id-- unit may not be there when we are copying data from effectivities table.
AND (sim.unit_config_header_id IS NULL OR ahl_util_uc_pkg.get_uc_status_code(sim.unit_config_header_id) NOT IN ('DRAFT', 'EXPIRED'))-- Check the status only if sim.UNIT_CONFIG_HEADER_ID is there in the sim ue table
AND sim.csi_item_instance_id = itm.instance_id
AND itm.inventory_item_id = mtl.inventory_item_id
AND itm.inv_master_organization_id = mtl.organization_id -- item mapping complete
AND unt.master_config_id = mc.mc_header_id(+) -- If an unit is there, then mc header id has to be there, But the unit itself may not be present
AND sim.fleet_header_id = flt.fleet_header_id(+)-- Fleet info is not mandaroty. Association may not be defined
AND sim.mr_header_id = mr.mr_header_id(+)-- ue can be there without MR header id e.g in case of NR it can be null
AND sim.simulation_ue_id = ueo.unit_effectivity_id -- get maintenance org data from the new view by Sandeep;
   

View Text - HTML Formatted

SELECT SIM.SIMULATION_PLAN_ID
, SIM.SIMULATION_UE_ID
, SIM.DUE_DATE
, SIM.FLEET_HEADER_ID
, SIM.FORECAST_SEQUENCE
, SIM.REPETITIVE_MR_FLAG
, UNT.UNIT_CONFIG_HEADER_ID
, UNT.NAME UNIT
, ITM.INSTANCE_ID CSI_INSTANCE_ID
, ITM.SERIAL_NUMBER
, MTL.CONCATENATED_SEGMENTS ITEM_NAME
, MC.NAME MASTER_CONFIGURATION
, MC.MC_HEADER_ID
, FLT.OPERATING_ORG_ID
, (SELECT HOU.NAME
FROM HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
WHERE HOU.ORGANIZATION_ID = FLT.OPERATING_ORG_ID
AND MP.ORGANIZATION_ID = FLT.OPERATING_ORG_ID
AND MP.EAM_ENABLED_FLAG = 'Y' )OPERATING_ORG
, UEO.MR_MAINTENANCE_ORG_ID
, UEO.MR_MAINTENANCE_ORG_NAME MAINTENANCE_ORG
, FLT.NAME FLEET_NAME
, MR.TITLE MR_TITLE
, MR.MR_HEADER_ID
, MR.PROGRAM_TYPE_CODE
, (SELECT MEANING FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_TYPE'
AND LOOKUP_CODE = MR.PROGRAM_TYPE_CODE) PROGRAM_TYPE
, MR.PROGRAM_SUBTYPE_CODE
, (SELECT MEANING FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'AHL_FMP_MR_PROGRAM_SUBTYPE'
AND LOOKUP_CODE = MR.PROGRAM_SUBTYPE_CODE ) PROGRAM_SUBTYPE
, MR.DOWN_TIME
, MR.SERVICE_CATEGORY_RANK SERVICE_CATEGORY_CODE FROM AHL_UNIT_CONFIG_HEADERS UNT
, AHL_UE_SIMULATIONS SIM
, CSI_ITEM_INSTANCES ITM
, MTL_SYSTEM_ITEMS_KFV MTL
, AHL_MC_HEADERS_B MC
, AHL_FLEET_HEADERS_B FLT
, AHL_MR_HEADERS_B MR
, AHL_UE_ORGS_DEPTS_V UEO WHERE UNT.UNIT_CONFIG_HEADER_ID(+) = SIM.UNIT_CONFIG_HEADER_ID-- UNIT MAY NOT BE THERE WHEN WE ARE COPYING DATA
FROM EFFECTIVITIES TABLE. AND (SIM.UNIT_CONFIG_HEADER_ID IS NULL OR AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(SIM.UNIT_CONFIG_HEADER_ID) NOT IN ('DRAFT'
, 'EXPIRED'))-- CHECK THE STATUS ONLY IF SIM.UNIT_CONFIG_HEADER_ID IS THERE IN THE SIM UE TABLE AND SIM.CSI_ITEM_INSTANCE_ID = ITM.INSTANCE_ID AND ITM.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND ITM.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID -- ITEM MAPPING COMPLETE AND UNT.MASTER_CONFIG_ID = MC.MC_HEADER_ID(+) -- IF AN UNIT IS THERE
, THEN MC HEADER ID HAS TO BE THERE
, BUT THE UNIT ITSELF MAY NOT BE PRESENT AND SIM.FLEET_HEADER_ID = FLT.FLEET_HEADER_ID(+)-- FLEET INFO IS NOT MANDAROTY. ASSOCIATION MAY NOT BE DEFINED AND SIM.MR_HEADER_ID = MR.MR_HEADER_ID(+)-- UE CAN BE THERE WITHOUT MR HEADER ID E.G IN CASE OF NR IT CAN BE NULL AND SIM.SIMULATION_UE_ID = UEO.UNIT_EFFECTIVITY_ID -- GET MAINTENANCE ORG DATA
FROM THE NEW VIEW BY SANDEEP;