[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
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.unit_effectivity_id = p_ue_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.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 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 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;
DELETE FROM AHL_APPLICABLE_MRS;
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
) 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
);
SELECT distinct mr_header_id, csi_item_instance_id, descendent_count
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 '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 '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 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 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_orig_mr_id,
l_orig_ii_id,
l_relationship_code
);
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 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;