[Home] [Help]
994: WHERE r.route_id = res.object_id
995: AND r.mr_header_id IN
996: (SELECT mr.mr_header_id
997: FROM ahl_mr_headers_b mr,
998: ahl_unit_effectivities_b ue
999: WHERE mr.mr_header_id = ue.mr_header_id
1000: AND ue.unit_effectivity_id IN
1001: (SELECT related_ue_id
1002: FROM ahl_ue_relationships
1080: AND r.mr_header_id IN
1081: ( -- all mr headers belonging to child UEs
1082: SELECT mr.mr_header_id
1083: FROM ahl_mr_headers_b mr,
1084: ahl_unit_effectivities_b ue
1085: WHERE mr.mr_header_id = ue.mr_header_id
1086: AND ue.unit_effectivity_id IN
1087: (SELECT related_ue_id
1088: FROM ahl_ue_relationships
1272: CURSOR child_ue_details(p_parent_ue_id NUMBER)
1273: IS
1274: SELECT ue.unit_effectivity_id, ue.mr_header_id, ue.csi_item_instance_id,
1275: ue.due_date, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, b.down_time
1276: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b b, AHL_UE_ORGS_DEPTS_V od
1277: WHERE ue.unit_effectivity_id IN
1278: (SELECT related_ue_id
1279: FROM ahl_ue_relationships
1280: WHERE originator_ue_id = p_parent_ue_id
2392: FROM ahl_mr_headers_b mr
2393: WHERE mr_status_code = 'COMPLETE'
2394: AND EXISTS
2395: (SELECT 1
2396: FROM ahl_unit_effectivities_b ueb
2397: WHERE ueb.mr_header_id = mr.mr_header_id
2398: AND (ueb.status_code IS NULL
2399: OR ueb.status_code = 'INIT-DUE')
2400: );
2431: ue.csi_item_instance_id, ue.object_type, ue.defer_from_ue_id,
2432: mr.title title, ue.manually_planned_flag, ii.quantity,
2433: (SELECT 'Y' from ahl_ue_relationships WHERE originator_ue_id = ue.unit_effectivity_id AND ROWNUM < 2) grp_flag,
2434: mr.down_time, mr.uom_code, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, ue.fleet_header_id
2435: FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B mr, AHL_UE_ORGS_DEPTS_V od
2436: WHERE UE.csi_item_instance_id = II.instance_id
2437: AND UE.mr_header_id = mr.mr_header_id
2438: AND UE.application_usg_code = 'AHL'
2439: AND UE.object_type = 'MR'
2470: ues.defer_from_ue_id,
2471: mr.title title,
2472: DECODE(ues.unit_effectivity_id, NULL, 'N',
2473: (SELECT manually_planned_flag
2474: FROM ahl_unit_effectivities_b
2475: WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
2476: ii.quantity,
2477: (SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
2478: mr.down_time,
2517: ii.quantity,
2518: (SELECT 'Y' from ahl_ue_relationships
2519: where originator_ue_id = ue.unit_effectivity_id and rownum < 2) grp_flag,
2520: flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ue.fleet_header_id
2521: FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_FLEET_HEADERS_B flt
2522: WHERE UE.csi_item_instance_id = II.instance_id
2523: AND UE.APPLICATION_USG_CODE = 'AHL'
2524: AND UE.Object_type = 'SR'-- Deb: When object type is SR then mr_header_id is null. In that case all mr dependent cursor is going to fail
2525: AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2554: AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
2555: AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
2556: DECODE(ues.unit_effectivity_id, NULL, 'N',
2557: (SELECT manually_planned_flag
2558: FROM ahl_unit_effectivities_b
2559: WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
2560: ii.quantity,
2561: (SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
2562: flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ues.fleet_header_id