[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
FROM ahl_unit_config_headers uc
WHERE csi_item_instance_id in ( SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_csi_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR object_id = subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
)
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
AND parent_uc_header_id IS NULL;
SELECT name
FROM ahl_unit_config_headers uc
WHERE csi_item_instance_id = p_csi_item_instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
AND parent_uc_header_id IS NULL;
SELECT count(related_ue_id)
FROM ahl_ue_relationships
WHERE relationship_code = 'PARENT'
AND ue_id = p_id;
SELECT vst.start_date_time, vst.visit_id, tsk.status_code
FROM ahl_visit_tasks_b tsk, (select vst1.*
from ahl_visits_b vst1, ahl_simulation_plans_b sim
where vst1.simulation_plan_id = sim.simulation_plan_id
and sim.primary_plan_flag = 'Y'
UNION ALL
select vst1.*
from ahl_visits_b vst1
where vst1.simulation_plan_id IS NULL) vst
WHERE vst.visit_id = tsk.visit_id
AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND tsk.task_type_code = 'SUMMARY'
AND tsk.unit_effectivity_id = p_ue_id;
SELECT max(scheduled_completion_date)
FROM wip_discrete_jobs
WHERE wip_entity_id IN (select wip_entity_id
from ahl_workorders awo, ahl_visit_tasks_b tsk
where awo.visit_task_id = tsk.visit_task_id
and tsk.task_type_code <> 'SUMMARY'
and awo.status_code NOT IN ('17','22','7')
and tsk.unit_effectivity_id = p_unit_effectivity_id);
SELECT decode(vst.status_code,'CLOSED', vst.status_code, tsk.status_code)
FROM ahl_visit_tasks_b tsk, (select vst1.*
from ahl_visits_b vst1, ahl_simulation_plans_b sim
where vst1.simulation_plan_id = sim.simulation_plan_id
and sim.primary_plan_flag = 'Y'
UNION ALL
select vst1.*
from ahl_visits_b vst1
where vst1.simulation_plan_id IS NULL) vst
WHERE vst.visit_id = tsk.visit_id
AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND tsk.task_type_code = 'SUMMARY'
AND tsk.unit_effectivity_id = p_ue_id;
AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
AND tsk.unit_effectivity_id = p_ue_id;
SELECT title mr_title, version_number, copy_accomplishment_flag
FROM ahl_mr_headers_b
WHERE mr_header_id = p_mr_header_id;
SELECT version_number, copy_accomplishment_flag, mr_header_id
FROM ahl_mr_headers_b
WHERE title = p_mr_title AND
version_number = p_version_number;
SELECT * FROM (
SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
decode(ue.status_code, 'TERMINATED', ter.affect_due_calc_flag, def.affect_due_calc_flag),
decode(ue.status_code, 'TERMINATED', ter.deferral_effective_on, def.deferral_effective_on)
FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def, ahl_unit_deferrals_b ter
WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
AND ue.unit_effectivity_id = ter.unit_effectivity_id(+)
AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
AND def.unit_deferral_type(+) = 'DEFERRAL'
AND ter.unit_deferral_type(+) = 'DEFERRAL'
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND ue.mr_header_id = p_mr_header_id
--ORDER BY accomplished_date DESC;
SELECT inc.incident_number, inc.incident_id
FROM cs_incident_links link, cs_incidents_all_vl inc
WHERE link.subject_id = inc.incident_id
AND subject_type = 'SR'
AND link_type_id = p_cs_link_id
AND object_type = 'AHL_UMP_EFF'
AND object_id = p_unit_effectivity_id;
SELECT DISTINCT MR_HEADER_ID
FROM AHL_APPLICABLE_MRS
WHERE START_MR_HEADER_ID = MR_HEADER_ID
AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP', 'CHAIN');
SELECT MR_HEADER_ID
FROM AHL_APPLICABLE_MRS
WHERE CSI_ITEM_INSTANCE_ID = c_item_instance_id
AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP', 'CHAIN');
DELETE FROM AHL_APPLICABLE_MRS;
p_insert_into_tmp_tbl => 'Y',
x_applicable_mr_tbl => l_appl_mrs_tbl);
INSERT INTO AHL_APPLICABLE_MRS (
CSI_ITEM_INSTANCE_ID,
MR_HEADER_ID,
MR_EFFECTIVITY_ID,
REPETITIVE_FLAG ,
SHOW_REPETITIVE_CODE,
COPY_ACCOMPLISHMENT_CODE,
PRECEDING_MR_HEADER_ID,
IMPLEMENT_STATUS_CODE,
DESCENDENT_COUNT,
ACCOMPLISH_TRIGGER_TYPE,
START_MR_HEADER_ID,
LOOP_CHAIN_SEQ_NUM,
PROCESS_STATUS_FLAG,
PROCESSING_ORDER
) values
(l_appl_mrs_tbl(i).item_instance_id,
l_appl_mrs_tbl(i).mr_header_id,
l_appl_mrs_tbl(i).mr_effectivity_id,
l_appl_mrs_tbl(i).repetitive_flag,
l_appl_mrs_tbl(i).show_repetitive_code,
l_appl_mrs_tbl(i).copy_accomplishment_flag,
l_appl_mrs_tbl(i).preceding_mr_header_id,
l_appl_mrs_tbl(i).implement_status_code,
l_appl_mrs_tbl(i).descendent_count,
l_appl_mrs_tbl(i).relationship_code,
l_appl_mrs_tbl(i).start_mr_header_id,
l_appl_mrs_tbl(i).sequence_number,
'N', -- default
1 -- default
);
UPDATE AHL_APPLICABLE_MRS apmr
SET accomplish_trigger_type = null,
start_mr_header_id = null
WHERE start_mr_header_id is not null
AND not exists (select 'x' from AHL_APPLICABLE_MRS apmr1
where apmr1.csi_item_instance_id = apmr.csi_item_instance_id
and apmr1.mr_header_id = apmr.start_mr_header_id
and apmr1.accomplish_trigger_type = apmr.accomplish_trigger_type
and apmr1.loop_chain_seq_num = 1);
SELECT DISTINCT CSI_ITEM_INSTANCE_ID BULK COLLECT INTO l_item_instance_tbl
FROM AHL_APPLICABLE_MRS
WHERE MR_HEADER_ID = l_start_mr_relns(0).mr_header_id;
UPDATE AHL_APPLICABLE_MRS
SET ACCOMPLISH_TRIGGER_TYPE = null
WHERE MR_HEADER_ID = l_start_mr_relns(k).MR_HEADER_ID
AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP','CHAIN') ;
SELECT distinct mr_header_id, csi_item_instance_id
FROM ahl_applicable_mrs
WHERE descendent_count > 0;
DELETE FROM AHL_APPLICABLE_MR_RELNS;
SELECT distinct r.mr_header_id, r.related_mr_header_id,
r.relationship_code
FROM ahl_mr_relationships r
WHERE EXISTS (SELECT 'x'
FROM AHL_MR_HEADERS_B b1, AHL_MR_HEADERS_B b2
WHERE b1.mr_header_id = r.mr_header_id
AND b2.mr_header_id = r.related_mr_header_id
AND b1.mr_status_code = 'COMPLETE'
AND b2.mr_status_code = 'COMPLETE'
AND NVL(b1.effective_from, SYSDATE) <= SYSDATE
AND NVL(b2.effective_from, SYSDATE) <= SYSDATE
AND NVL(b1.effective_to, SYSDATE+1) >= SYSDATE
AND NVL(b2.effective_to, SYSDATE+1) >= SYSDATE)
START WITH r.mr_header_id = p_mr_id
CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
AND r.relationship_code = 'PARENT';
SELECT distinct r.mr_header_id, r.related_mr_header_id,
r.relationship_code
FROM ahl_mr_relationships r
START WITH r.mr_header_id = p_mr_id
AND r.relationship_code = 'PARENT'
AND exists (select /*+ push_subq no_unnest */ 'x' from ahl_mr_headers_b mr1
where mr1.mr_header_id = r.related_mr_header_id
and mr1.version_number = (select max(mr2.version_number)
from ahl_mr_headers_b mr2
where mr2.title = mr1.title
and mr2.mr_status_code = 'COMPLETE'
and SYSDATE between trunc(mr2.effective_from)
and trunc(nvl(mr2.effective_to,SYSDATE+1))
)
)
CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
AND r.relationship_code = 'PARENT'
AND exists (select /*+ push_subq no_unnest */ 'x' from ahl_mr_headers_b mr1
where mr1.mr_header_id = r.related_mr_header_id
and mr1.version_number = (select max(mr2.version_number)
from ahl_mr_headers_b mr2
where mr2.title = mr1.title
and mr2.mr_status_code = 'COMPLETE'
and SYSDATE between trunc(mr2.effective_from)
and trunc(nvl(mr2.effective_to,SYSDATE+1))
)
);
SELECT csi_item_instance_id,
(select subj_child_exists from ahl_config_components
where subject_id = csi_item_instance_id) child_exists
FROM (SELECT distinct csi_item_instance_id
FROM ahl_applicable_mrs
WHERE mr_header_id = p_mr_id);
/*SELECT distinct csi_item_instance_id
FROM ahl_applicable_mrs
WHERE mr_header_id = p_mr_id
AND (csi_item_instance_id = p_item_instance_id
OR csi_item_instance_id IN (SELECT subject_id
FROM csi_ii_relationships
START WITH object_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
); */
SELECT distinct csi_item_instance_id
FROM ahl_applicable_mrs amr,
(SELECT subject_id
FROM csi_ii_relationships
START WITH object_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
UNION ALL
SELECT p_item_instance_id
FROM DUAL) cs
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = cs.subject_id;
WITH INST AS (SELECT subject_id csi_item_instance_id
FROM csi_ii_relationships
START WITH object_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
UNION ALL
SELECT p_item_instance_id csi_item_instance_id
FROM DUAL)
SELECT csi_item_instance_id
FROM INST
WHERE EXISTS (SELECT 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = inst.csi_item_instance_id);
SELECT subject_id csi_item_instance_id
FROM csi_ii_relationships
WHERE EXISTS (SELECT 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = subject_id)
START WITH object_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
UNION ALL
SELECT p_item_instance_id csi_item_instance_id
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = p_item_instance_id);
SELECT /*+ push_subq */ subject_id csi_item_instance_id
FROM ahl_config_components
WHERE EXISTS (SELECT /*+ push_subq */ 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = subject_id)
START WITH object_id = p_item_instance_id
CONNECT BY PRIOR subject_id = object_id
UNION ALL
SELECT p_item_instance_id csi_item_instance_id
FROM DUAL
WHERE EXISTS (SELECT /*+ push_subq */ 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = p_item_instance_id);
SELECT p_item_instance_id csi_item_instance_id
FROM DUAL
WHERE EXISTS (SELECT /*+ push_subq */ 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = p_item_instance_id);
SELECT /*+ push_subq */ cmp.subject_id csi_item_instance_id
FROM ahl_config_components cmp
WHERE EXISTS (SELECT /*+ push_subq */ 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = cmp.subject_id)
UNION ALL
SELECT p_item_instance_id subject_id
FROM DUAL
WHERE EXISTS (SELECT /*+ push_subq */ 'x'
FROM ahl_applicable_mrs AMR
WHERE amr.mr_header_id = p_mr_id
AND amr.csi_item_instance_id = p_item_instance_id);
SELECT level depth_level, mr_header_id, csi_item_instance_id,
related_mr_header_id, related_csi_item_instance_id
FROM ahl_applicable_mr_relns
--WHERE orig_mr_header_id = p_mr_id
--AND orig_csi_item_instance_id = p_item_instance_id
START WITH mr_header_id = p_mr_id
AND csi_item_instance_id = p_item_instance_id
AND orig_mr_header_id = p_mr_id
AND orig_csi_item_instance_id = p_item_instance_id
CONNECT BY mr_header_id = PRIOR related_mr_header_id
AND csi_item_instance_id = PRIOR related_csi_item_instance_id
AND orig_mr_header_id = p_mr_id
AND orig_csi_item_instance_id = p_item_instance_id;
SELECT NVL(tree_depth_level, 0)
FROM ahl_applicable_mr_relns
WHERE orig_mr_header_id = p_orig_mr_id
AND orig_csi_item_instance_id = p_orig_item_instance_id
AND mr_header_id = p_mr_id
AND csi_item_instance_id = p_item_instance_id
AND related_mr_header_id = p_related_mr_id
AND related_csi_item_instance_id = p_related_item_instance_id;
SELECT related_mr_header_id, related_csi_item_instance_id
FROM ahl_applicable_mr_relns
WHERE orig_mr_header_id = p_mr_id
AND orig_csi_item_instance_id = p_item_instance_id
GROUP BY related_mr_header_id, related_csi_item_instance_id
HAVING COUNT(*)>1;
SELECT mr_header_id, csi_item_instance_id
FROM ahl_applicable_mr_relns
WHERE tree_depth_level = (SELECT max(tree_depth_level)
from ahl_applicable_mr_relns
where orig_mr_header_id = p_orig_mr_id
AND orig_csi_item_instance_id = p_orig_item_instance_id
AND related_mr_header_id = p_mr_id
AND related_csi_item_instance_id=p_item_instance_id)
AND orig_mr_header_id = p_orig_mr_id
AND orig_csi_item_instance_id = p_orig_item_instance_id
AND related_mr_header_id = p_mr_id
AND related_csi_item_instance_id=p_item_instance_id;
DELETE FROM AHL_APPLICABLE_MR_RELNS;
INSERT INTO AHL_APPLICABLE_MR_RELNS (
MR_HEADER_ID,
CSI_ITEM_INSTANCE_ID,
RELATED_MR_HEADER_ID,
RELATED_CSI_ITEM_INSTANCE_ID,
ORIG_MR_HEADER_ID,
ORIG_CSI_ITEM_INSTANCE_ID,
RELATIONSHIP_CODE
) values
( l_mr_header_id,
l_csi_ii_id,
l_related_mr_header_id,
--l_related_csi_ii_id,
l_related_csi_ii_tbl(k),
l_orig_mr_id,
l_orig_ii_id,
l_relationship_code
);
l_related_csi_ii_tbl.delete;
l_mr_header_tbl.delete;
l_related_mr_header_tbl.delete;
l_relationship_code_tbl.delete;
l_related_csi_ii_tbl.delete;
UPDATE ahl_applicable_mr_relns
SET tree_depth_level = l_appl_mr_relns_rec.depth_level
WHERE orig_mr_header_id = l_orig_mr_id
AND orig_csi_item_instance_id = l_orig_ii_id
AND mr_header_id = l_appl_mr_relns_rec.mr_header_id
AND csi_item_instance_id = l_appl_mr_relns_rec.csi_item_instance_id
AND related_mr_header_id = l_appl_mr_relns_rec.related_mr_header_id
AND related_csi_item_instance_id = l_appl_mr_relns_rec.related_csi_item_instance_id;
DELETE FROM ahl_applicable_mr_relns
WHERE tree_depth_level IS NULL
AND orig_mr_header_id = l_orig_mr_id
AND orig_csi_item_instance_id = l_orig_ii_id;
DELETE FROM ahl_applicable_mr_relns
WHERE (mr_header_id <> l_mr_header_id
OR csi_item_instance_id <> l_csi_ii_id)
AND orig_mr_header_id = l_orig_mr_id
AND orig_csi_item_instance_id = l_orig_ii_id
AND related_mr_header_id = l_related_mr_header_id
AND related_csi_item_instance_id = l_related_csi_ii_id;
SELECT title mr_title, version_number, copy_accomplishment_flag
FROM ahl_mr_headers_b
WHERE mr_header_id = p_mr_header_id;
SELECT version_number, copy_accomplishment_flag, mr_header_id
FROM ahl_mr_headers_b
WHERE title = p_mr_title AND
version_number = p_version_number;
SELECT * FROM (
SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
affect_due_calc_flag, deferral_effective_on
FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def
WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED')
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND ue.mr_header_id = p_mr_header_id
ORDER BY accomplished_date ASC
)
WHERE ROWNUM < 2;
SELECT ii.subject_id, ii.object_id,
(select 'Y' from csi_ii_relationships where object_id = ii.subject_id
and relationship_type_code = 'COMPONENT-OF'
and trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
and rownum = 1) child_exists
FROM csi_ii_relationships ii
START WITH ii.object_id = p_root_instance_id
AND ii.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(ii.active_end_date, sysdate+1))
CONNECT BY PRIOR ii.subject_id = ii.object_id
AND ii.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(ii.active_end_date, sysdate+1));
DELETE FROM AHL_CONFIG_COMPONENTS;
INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)
values (l_subj_id_tbl(i), l_obj_id_tbl(i), p_root_instance_id, nvl(l_child_exists_tbl(i), 'N'));
l_subj_id_tbl.delete;
l_obj_id_tbl.delete;
l_child_exists_tbl.delete;
SELECT title mr_title, version_number, copy_accomplishment_flag
FROM ahl_mr_headers_b
WHERE mr_header_id = p_mr_header_id;
SELECT version_number, copy_accomplishment_flag, mr_header_id
FROM ahl_mr_headers_b
WHERE title = p_mr_title AND
version_number = p_version_number;
SELECT * FROM (
SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
decode(ue.status_code, 'TERMINATED', ter.affect_due_calc_flag, def.affect_due_calc_flag),
decode(ue.status_code, 'TERMINATED', ter.deferral_effective_on, def.deferral_effective_on)
FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def, ahl_unit_deferrals_b ter
WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
AND ue.unit_effectivity_id = ter.unit_effectivity_id(+)
AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
AND def.unit_deferral_type(+) = 'DEFERRAL'
AND ter.unit_deferral_type(+) = 'DEFERRAL'
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND ue.mr_header_id = p_mr_header_id
--ORDER BY accomplished_date DESC;
SELECT FUA.fleet_header_id
FROM ahl_fleet_unit_assocs FUA, ahl_fleet_headers_b FLT
WHERE unit_config_header_id = c_unit_config_header_id
AND c_due_date between ASSOCIATION_START and nvl(ASSOCIATION_END,c_due_date)
AND FLT.fleet_header_id = FUA.fleet_header_id
AND FLT.status_code = 'COMPLETE'
AND FUA.simulation_plan_id = c_plan_id;
SELECT simulation_plan_id
FROM AHL_SIMULATION_PLANS_B
WHERE primary_plan_flag = 'Y'
AND status_code = 'ACTIVE'
AND nvl(simulation_type,'UMP') = 'UMP';