[Home] [Help]
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
1003: WHERE originator_ue_id = p_grp_ue_id
1004: )
1005: )
1006: AND res.association_type_code = 'ROUTE'
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
1089: WHERE originator_ue_id = p_grp_ue_id
1090: )
1091: )
1092: AND res.association_type_code = 'OPERATION'
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
1281: )
1282: AND ue.mr_header_id IS NOT NULL
1283: AND ue.mr_header_id = b.mr_header_id
2429: SELECT * FROM (
2430: SELECT ue.unit_effectivity_id, ue.due_date, ue.mr_header_id,
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
2444: WHERE wo.visit_task_id = tsk.visit_task_id
2445: AND tsk.task_type_code = 'SUMMARY'
2446: AND tsk.status_code NOT IN ('DELETED','CANCELLED')
2447: AND tsk.unit_effectivity_id = UE.unit_effectivity_id)
2448: AND NOT EXISTS (SELECT 'x' FROM ahl_ue_relationships WHERE related_ue_id = UE.unit_effectivity_id)
2449: AND od.unit_effectivity_id = ue.unit_effectivity_id
2450: AND od.sim_ue_flag = 'N'
2451: AND od.mr_maintenance_org_id IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
2452: --AND od.mr_maintenance_dept_id IS NOT NULL -- removed dept check as dept can be null for ues not associated to fleet
2514: WHERE CS.incident_id = ue.cs_incident_id
2515: AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
2516: AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
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
2528: where wo.visit_task_id = tsk.visit_task_id
2529: and tsk.task_type_code = 'SUMMARY'
2530: and tsk.status_code NOT IN ('DELETED','CANCELLED')
2531: and tsk.unit_effectivity_id = UE.unit_effectivity_id)
2532: AND NOT EXISTS (SELECT 'x' from ahl_ue_relationships where related_ue_id = UE.unit_effectivity_id)
2533: --AND od.unit_effectivity_id = ue.unit_effectivity_id
2534: --AND od.sim_ue_flag = 'N'
2535: --AND od.mr_maintenance_org_id IS NOT NULL
2536: -- Added by debadey because od does not reflect SR UEs org and dept