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;
APPS.AHL_SIMULATION_FORECAST_V
[Home] [Help]