[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_check_flag (p_applicable_mrs_rec IN applicable_mrs_rec_type,
p_dependent_mr_flag IN BOOLEAN,
p_next_due_date_rec IN next_due_date_rec_type);
DELETE FROM AHL_TEMP_UNIT_EFFECTIVITIES;
DELETE FROM AHL_TEMP_UNIT_SR_DEFERRALS;
SELECT trunc(min(okl.end_date)), trunc(okh.start_date)
FROM okc_k_headers_b okh, okc_k_lines_b okl
WHERE OKL.DNZ_CHR_ID = OKH.ID
AND OKH.CONTRACT_NUMBER = p_contract_number
AND OKH.CONTRACT_NUMBER_MODIFIER = p_contract_modifier
GROUP BY OKH.ID, OKH.start_date;
SELECT trunc(min(okl.end_date)), trunc(okh.start_date)
FROM okc_k_headers_b okh, okc_k_lines_b okl
WHERE OKL.DNZ_CHR_ID = OKH.ID
AND OKH.CONTRACT_NUMBER = p_contract_number
GROUP BY OKH.ID, OKH.start_date;
SELECT min(instance_id), max(instance_id)
FROM csi_item_instances csi,
(select me.inventory_item_id
from ahl_mr_effectivities me, ahl_mr_headers_app_v mr
where mr.mr_header_id = me.mr_header_id
and mr.type_code = 'PROGRAM') mre
WHERE trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate) AND
trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
AND mre.inventory_item_id = csi.inventory_item_id;
SELECT instance_number, active_end_date, inventory_item_id,
inv_master_organization_id
FROM csi_item_instances
WHERE instance_id = p_csi_item_instance_id;
SELECT name, active_start_date, active_end_date, master_config_id, unit_config_header_id,
unit_config_status_code
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_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 root.object_id
FROM csi_ii_relationships root
WHERE NOT EXISTS (SELECT 'x'
FROM csi_ii_relationships
WHERE subject_id = root.object_id
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
)
START WITH root.subject_id = p_instance_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
CONNECT BY PRIOR root.object_id = root.subject_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
SELECT subject_id , object_id, position_reference
FROM csi_ii_relationships
START WITH object_id = p_csi_root_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))
ORDER BY level;
l_subj_id_tbl.DELETE;
l_obj_id_tbl.DELETE;
l_posn_ref_tbl.DELETE;
SELECT uf_header_id, use_unit_flag
FROM ahl_uf_headers
WHERE unit_config_header_id = p_uc_header_id;
SELECT uom_code, start_date, end_date, usage_per_day
FROM ahl_uf_details
WHERE uf_header_id = p_uf_header_id
AND trunc(nvl(end_date, sysdate)) >= trunc(sysdate)
order by uom_code, start_date;
SELECT uf_header_id, use_unit_flag
FROM ahl_uf_headers
WHERE csi_item_instance_id = p_csi_item_instance_id;
SELECT uom_code, start_date, end_date, usage_per_day
FROM ahl_uf_headers uh, ahl_uf_details ud, csi_item_instances csi
WHERE uh.uf_header_id = ud.uf_header_id
AND csi.instance_id = p_csi_item_instance_id
AND csi.inventory_item_id = uh.inventory_item_id
AND trunc(nvl(end_date, sysdate)) >= trunc(sysdate)
order by uom_code, start_date;
SELECT UNIT_EFFECTIVITY_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE csi_item_instance_id = p_csi_item_instance_id
AND (status_code IS NULL OR status_code = 'INIT-DUE')
FOR UPDATE OF object_version_number NOWAIT;
SELECT 1
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE csi_item_instance_id = p_csi_item_instance_id
AND (status_code IS NULL OR status_code IN ('INIT-DUE','EXCEPTION'))
FOR UPDATE OF status_code NOWAIT;
WITH II AS (SELECT p_csi_item_instance_id instance_id
FROM DUAL
UNION ALL
SELECT A.SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS A
START WITH OBJECT_ID = p_csi_item_instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN TRUNC(NVL(ACTIVE_START_DATE,SYSDATE))
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1))
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN TRUNC(NVL(ACTIVE_START_DATE,SYSDATE))
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1))
)
SELECT 1
FROM AHL_UNIT_EFFECTIVITIES_APP_V UE, II
WHERE UE.csi_item_instance_id = II.INSTANCE_ID
AND (status_code IS NULL OR status_code IN ('INIT-DUE','EXCEPTION'))
FOR UPDATE OF status_code NOWAIT;
l_ue_id_tbl.delete;
l_ue_id_tbl.delete;
SELECT DISTINCT appl.csi_item_instance_id,
appl.MR_header_id,
mr.Title,
mr.version_number,
appl.Implement_status_code,
appl.copy_accomplishment_code,
appl.repetitive_flag,
appl.show_repetitive_code,
appl.preceding_mr_header_id,
appl.descendent_count,
mr.whichever_first_code,
mr.effective_to,
mr.effective_from
--FROM ahl_applicable_MRs appl, ahl_mr_headers_vl mr
FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr
WHERE appl.csi_item_instance_id = p_csi_item_instance_id
AND (appl.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
AND appl.preceding_mr_header_id IS NULL
AND appl.mr_header_id = mr.mr_header_id
AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))
ORDER BY descendent_count DESC;
SELECT uom_code, ratio
FROM ahl_ctr_update_rules
WHERE relationship_id = p_relationship_id
AND rule_code = 'STANDARD';
SELECT relationship_id
FROM ahl_relationships_b
START WITH relationship_id = p_start_node_id
CONNECT BY PRIOR parent_relationship_id = relationship_id
AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate + 1));
SELECT to_number(position_reference) position_reference
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 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 relationship_id
FROM ahl_mc_relationships
WHERE parent_relationship_id IS NULL
START WITH relationship_id = p_start_node_id
CONNECT BY PRIOR parent_relationship_id = relationship_id
AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate + 1));
l_uom_code ahl_ctr_update_rules.uom_code%TYPE;
SELECT counter_id, uom_code, net_reading, counter_name
FROM csi_cp_counters_v
WHERE customer_product_id = p_csi_item_instance_id
ORDER BY uom_code;
SELECT cc.counter_id, cc.uom_code,
cc.counter_template_name counter_name
from csi_counter_associations cca, csi_counters_vl cc
where cca.counter_id = cc.counter_id
AND source_object_code = 'CP'
AND source_object_id = p_csi_instance_id;
SELECT cc.counter_id, cc.uom_code,
cc.counter_template_name counter_name,
(select ccr.net_reading
from csi_counter_readings ccr
where ccr.counter_id = cc.counter_id
and value_timestamp = (select max(value_timestamp) from csi_counter_readings rd
where counter_id = cc.counter_id
and nvl(disabled_flag,'N') = 'N')) net_reading
FROM csi_counter_associations cca, csi_counters_vl cc
WHERE cca.counter_id = cc.counter_id
AND source_object_code = 'CP'
AND source_object_id = p_csi_item_instance_id;
SELECT cc.counter_id, cc.uom_code,
cc.counter_template_name counter_name,
(select ccr.net_reading
from csi_counter_readings ccr
where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
and nvl(ccr.disabled_flag,'N') = 'N')
FROM csi_counter_associations cca, csi_counters_vl cc
WHERE cca.counter_id = cc.counter_id
AND source_object_code = 'CP'
AND source_object_id = p_csi_instance_id;
SELECT nvl(cv.net_reading,0) net_reading
FROM csi_counter_values_v cv
WHERE cv.counter_id = p_counter_id
AND rownum < 2;
SELECT * FROM
(SELECT net_reading
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND nvl(disabled_flag,'N') = 'N'
ORDER BY value_timestamp desc)
WHERE rownum < 2;
SELECT ua.counter_id, ua.counter_value, cs.uom_code, cs.name counter_name
FROM ahl_unit_accomplishmnts ua, cs_counters_v cs
WHERE ua.counter_id = cs.counter_id AND
ua.unit_effectivity_id = p_unit_effectivity_id
ORDER BY cs.uom_code;
SELECT
UA.COUNTER_ID,
UA.COUNTER_VALUE,
CS.UOM_CODE,
--CS.NAME COUNTER_NAME
CS.COUNTER_TEMPLATE_NAME COUNTER_NAME
FROM
AHL_UNIT_ACCOMPLISHMNTS UA,
CSI_COUNTERS_VL CS
WHERE
UA.COUNTER_ID = CS.COUNTER_ID AND
UA.UNIT_EFFECTIVITY_ID = P_UNIT_EFFECTIVITY_ID
ORDER BY
CS.UOM_CODE ;
SELECT counter_id, uom_code, counter_name
FROM csi_cp_counters_v
WHERE customer_product_id = p_csi_instance_id
ORDER BY uom_code;
SELECT cc.counter_id, cc.uom_code,
cc.counter_template_name counter_name
from csi_counter_associations cca, csi_counters_vl cc
where cca.counter_id = cc.counter_id
AND source_object_code = 'CP'
AND source_object_id = p_csi_instance_id;
SELECT
-- fix for bug# 6875650. Deferral date includes timestamp.
--decode (affect_due_calc_flag, 'N', trunc(nvl(visit_end_date, deferral_effective_on)), trunc(nvl(visit_end_date, due_date)))
decode (affect_due_calc_flag, 'N', deferral_effective_on, nvl(visit_end_date, due_date))
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_csi_instance_id
AND mr_header_id = p_mr_header_id
AND object_type = 'MR'
AND deferral_effective_on IS NOT NULL
ORDER BY deferral_effective_on DESC;
SELECT ue.unit_effectivity_id, ue.status_code, reln.related_ue_id, reln.originator_ue_id
FROM ahl_unit_effectivities_app_v UE, ahl_UE_relationships reln
WHERE UE.unit_effectivity_id = RELN.RELATED_UE_ID(+)
AND mr_header_id = p_mr_header_id
AND csi_item_instance_id = p_csi_item_instance_id
AND (UE.Status_code IS NULL OR status_code = 'INIT-DUE')
AND UE.defer_from_ue_id IS NULL -- do not pick deferred unit effectivities.
AND nvl(UE.manually_planned_flag,'N') = 'N' -- do not pick manually planned UEs.
-- do not pick up child UEs if parent init-accomplished.
AND NOT EXISTS (SELECT 'x' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE
WHERE PARENT_UE.UNIT_EFFECTIVITY_ID = RELN.ORIGINATOR_UE_ID
AND PARENT_UE.STATUS_CODE = 'INIT-ACCOMPLISHED')
ORDER BY forecast_sequence ASC;
SELECT *
FROM (
SELECT due_date,
orig_csi_item_instance_id,
orig_mr_header_id,
--orig_forecast_sequence,
visit_end_date
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_item_instance_id AND
mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id IS NOT NULL AND
orig_mr_header_id IS NOT NULL AND
trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) > trunc(p_last_due_date) AND
trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date) AND
preceding_check_flag = 'N'
/* ignore records with null due dates */
/* order selected rows so that the record with max due date is first */
/* consider visit end date instead of due date, if it is available. */
UNION
-- Get SR's.
SELECT due_date,
csi_item_instance_id orig_csi_item_instance_id,
mr_header_id orig_mr_header_id,
visit_end_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_item_instance_id
AND mr_header_id = p_mr_header_id
AND trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) > trunc(p_last_due_date)
AND trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date)
AND deferral_effective_on IS NULL -- pick only SR related MRs.
-- ignore records with null due dates.
-- ignore deferral records.
ORDER BY due_date DESC
)
WHERE ROWNUM < 2;
SELECT *
FROM (
SELECT due_date,
orig_csi_item_instance_id,
orig_mr_header_id,
--orig_forecast_sequence,
visit_end_date
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_item_instance_id AND
mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id IS NOT NULL AND
orig_mr_header_id IS NOT NULL AND
trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) >= trunc(p_last_due_date) AND
trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date) AND
preceding_check_flag = 'N'
/* ignore records with null due dates */
/* order selected rows so that the record with max due date is first */
/* consider visit end date instead of due date, if it is available. */
UNION
-- Get SR's.
SELECT due_date,
csi_item_instance_id orig_csi_item_instance_id,
mr_header_id orig_mr_header_id,
visit_end_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_item_instance_id
AND mr_header_id = p_mr_header_id
AND trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) >= trunc(p_last_due_date)
AND trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date)
AND deferral_effective_on IS NULL -- pick only SR related MRs.
-- ignore records with null due dates.
-- ignore deferral records.
ORDER BY due_date DESC
)
WHERE ROWNUM < 2;
SELECT 'x'
FROM ahl_applicable_mr_relns
WHERE orig_csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id;
SELECT due_date
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_preceding_instance_id AND
mr_header_id = p_preceding_mr_header_id AND
preceding_check_flag = 'N'
ORDER by due_date;
SELECT ue.unit_effectivity_id
FROM ahl_unit_effectivities_app_v ue
WHERE ue.csi_item_instance_id = p_item_instance_id
AND ue.mr_header_id = p_mr_header_id
AND ue.status_code IS NULL
AND ue.manually_planned_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM ahl_ue_relationships uer, ahl_unit_effectivities_b ue1
WHERE uer.related_ue_id = ue.unit_effectivity_id
AND uer.originator_ue_id = ue1.unit_effectivity_id
AND ue1.object_type = 'SR');
l_ue_id_tbl.DELETE;
l_ue_status_tbl.DELETE;
l_related_ue_tbl.DELETE;
l_orig_ue_tbl.DELETE;
Update_check_flag (p_applicable_mrs_rec => p_applicable_mrs_rec,
p_dependent_mr_flag => l_dependent_mr_flag,
p_next_due_date_rec => l_next_due_date_rec);
SELECT DISTINCT mr.mr_effectivity_id, threshold_date
FROM ahl_applicable_mrs mr, ahl_mr_effectivities eff
WHERE mr.mr_effectivity_id = eff.mr_effectivity_id AND
csi_item_instance_id = p_instance_id AND
mr.mr_header_id = p_mr_header_id;
SELECT INT.mr_interval_id, INT.start_date, INT.stop_date,
INT.start_value, INT.stop_value, INT.counter_id,
INT.interval_value, INT.tolerance_after, INT.tolerance_before,
INT.earliest_due_value -- added for bug# 6358940.
--Replaced cs_counters_v with cs_counters to fix perf bug# 3786647.
--FROM ahl_mr_intervals INT, cs_counters_v CTR, cs_counters_v CN
--replaced cs_counters CTR with csi_counter_template_vl
--and cs_counters CN with csi_counters_vl CN to fix bug# 5918525.
FROM ahl_mr_intervals INT, csi_counter_template_vl CTR --, csi_counters_vl CN
WHERE INT.counter_id = CTR.counter_id AND
--CTR.name = CN.name AND -- bug# 5918525.
--CTR.name = CN.counter_template_name AND -- removed for perf fix.
CTR.name = p_counter_name AND
INT.mr_effectivity_id = p_mr_effectivity_id AND
--CN.counter_id = p_counter_id AND -- removed for perf fix.
(
( (nvl(start_value, p_counter_value+1) <= p_counter_value AND
--p_counter_value < nvl(stop_value, p_counter_value+1)) OR
-- Fix for bug# 3482307.
p_counter_value <= nvl(stop_value, p_counter_value+1)) OR
(trunc(nvl(start_date, p_start_date+1)) <= trunc(p_start_date) AND
--trunc(p_start_date) < trunc(nvl(stop_date, p_start_date+1)) )
-- Fix for bug# 3482307.
trunc(p_start_date) <= trunc(nvl(stop_date, p_start_date+1)) )
)
OR
/* pick records with no start/stop values/dates. */
(start_value IS NULL AND stop_value IS NULL AND start_date IS NULL AND stop_date IS NULL
AND interval_value IS NOT NULL)
);
SELECT ud.set_due_date, ue.unit_effectivity_id, ud.unit_deferral_id
FROM ahl_unit_effectivities_app_v ue, ahl_unit_deferrals_vl ud
WHERE ue.unit_effectivity_id = ud.unit_effectivity_id AND
ud.unit_deferral_type = 'INIT-DUE' AND
ue.csi_item_instance_id = p_csi_item_instance_id AND
ue.mr_header_id = p_mr_header_id AND
ue.status_code = 'INIT-DUE';
SELECT counter_id, counter_value
FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_unit_deferral_id;
SELECT DISTINCT name counter_name
FROM ahl_mr_intervals int, csi_counter_template_vl ctr
WHERE int.mr_effectivity_id = p_mr_effectivity_id
AND int.counter_id = ctr.counter_id;
SELECT INT.start_value, INT.stop_value,
INT.interval_value
FROM ahl_mr_intervals INT, cs_counters_v CTR, cs_counters_v CN
WHERE INT.counter_id = CTR.counter_id AND
CTR.name = CN.name AND
INT.mr_effectivity_id = p_mr_effectivity_id AND
CN.counter_id = p_counter_id AND
( nvl(start_value, p_counter_value+1) <= p_counter_value AND
--p_counter_value < nvl(stop_value, p_counter_value)
p_counter_value < nvl(stop_value, p_counter_value + 1)--fix for bug number 3713078
);
SELECT
INT.START_VALUE,
INT.STOP_VALUE,
INT.INTERVAL_VALUE
FROM
AHL_MR_INTERVALS INT,
CSI_COUNTER_TEMPLATE_VL CTR,
--CSI_COUNTER_TEMPLATE_VL CN --bug# 5918525
csi_counters_vl CN
WHERE
INT.COUNTER_ID = CTR.COUNTER_ID AND
--CTR.NAME = CN.NAME AND -- bug# 5918525
CTR.NAME = CN.counter_template_name AND
INT.MR_EFFECTIVITY_ID = P_MR_EFFECTIVITY_ID AND
CN.COUNTER_ID = P_COUNTER_ID AND
(
NVL(START_VALUE, P_COUNTER_VALUE +1) <= P_COUNTER_VALUE AND
P_COUNTER_VALUE < NVL(STOP_VALUE, P_COUNTER_VALUE + 1)
) ;
/* SELECT INT.start_date, INT.stop_date,
INT.interval_value
FROM ahl_mr_intervals INT, cs_counters_v CTR, cs_counters_v CN
WHERE INT.counter_id = CTR.counter_id AND
CTR.name = CN.name AND
INT.mr_effectivity_id = p_mr_effectivity_id AND
CN.counter_id = p_counter_id AND
trunc(INT.start_date) = trunc(p_stop_date);
SELECT
INT.START_DATE,
INT.STOP_DATE,
INT.INTERVAL_VALUE
FROM
AHL_MR_INTERVALS INT,
CSI_COUNTER_TEMPLATE_VL CTR,
--CSI_COUNTER_TEMPLATE_VL CN --bug# 5918525
csi_counters_vl CN
WHERE
INT.COUNTER_ID = CTR.COUNTER_ID
--AND CTR.NAME = CN.NAME --bug# 5918525
AND CTR.NAME = CN.counter_template_name
AND INT.MR_EFFECTIVITY_ID = P_MR_EFFECTIVITY_ID
AND CN.COUNTER_ID = P_COUNTER_ID
AND TRUNC(INT.START_DATE) = TRUNC(P_STOP_DATE) ;
SELECT INT.mr_interval_id, INT.start_date, INT.stop_date,
INT.start_value, INT.stop_value, INT.counter_id,
INT.interval_value, INT.tolerance_after, INT.tolerance_before
-- Replaced cs_counters_v with cs_counters to fix perf bug# 3786647.
--FROM ahl_mr_intervals INT, cs_counters_v CTR, cs_counters_v CN
FROM ahl_mr_intervals INT, cs_counters CTR, cs_counters CN
WHERE INT.counter_id = CTR.counter_id AND
CTR.name = CN.name AND
INT.mr_effectivity_id = p_mr_effectivity_id AND
CN.counter_id = p_counter_id AND
INT.start_value > p_counter_value
ORDER BY INT.start_value;
SELECT *
FROM (
SELECT
INT.MR_INTERVAL_ID,
INT.START_DATE,
INT.STOP_DATE,
INT.START_VALUE,
INT.STOP_VALUE,
INT.COUNTER_ID,
INT.INTERVAL_VALUE,
INT.TOLERANCE_AFTER,
INT.TOLERANCE_BEFORE,
INT.EARLIEST_DUE_VALUE
FROM
AHL_MR_INTERVALS INT,
CSI_COUNTER_TEMPLATE_VL CTR --,
--CSI_COUNTER_TEMPLATE_VL CN --bug# 5918525
--csi_counters_vl CN
WHERE
INT.COUNTER_ID = CTR.COUNTER_ID
-- AND CTR.NAME = CN.NAME --bug# 5918525
--AND CTR.NAME = CN.counter_template_name
AND CTR.NAME = p_counter_name
AND INT.MR_EFFECTIVITY_ID = P_MR_EFFECTIVITY_ID
--AND CN.COUNTER_ID = P_COUNTER_ID
AND INT.START_VALUE > P_COUNTER_VALUE
ORDER BY
INT.START_VALUE ASC
)
WHERE ROWNUM < 2;
/*SELECT INT.mr_interval_id, INT.start_date, INT.stop_date,
INT.tolerance_after, INT.tolerance_before, INT.interval_value
-- Replaced cs_counters_v with cs_counters to fix perf bug# 3786647.
--FROM ahl_mr_intervals INT, cs_counters_v CTR, cs_counters_v CN
FROM ahl_mr_intervals INT, cs_counters CTR, cs_counters CN
WHERE INT.counter_id = CTR.counter_id AND
CTR.name = CN.name AND
INT.mr_effectivity_id = p_mr_effectivity_id AND
CN.counter_id = p_counter_id AND
INT.start_date > p_start_date
ORDER BY INT.start_date;
SELECT *
FROM (
SELECT
INT.MR_INTERVAL_ID,
INT.START_DATE,
INT.STOP_DATE,
INT.TOLERANCE_AFTER,
INT.TOLERANCE_BEFORE,
INT.INTERVAL_VALUE,
INT.EARLIEST_DUE_VALUE
FROM
AHL_MR_INTERVALS INT,
CSI_COUNTER_TEMPLATE_VL CTR --,
--CSI_COUNTER_TEMPLATE_VL CN --bug# 5918525
--csi_counters_vl CN
WHERE
INT.COUNTER_ID = CTR.COUNTER_ID
--AND CTR.NAME = CN.NAME --bug# 5918525
--AND CTR.NAME = CN.counter_template_name
AND CTR.NAME = p_counter_name
AND INT.MR_EFFECTIVITY_ID = P_MR_EFFECTIVITY_ID
--AND CN.COUNTER_ID = P_COUNTER_ID
AND INT.START_DATE > P_START_DATE
ORDER BY
INT.START_DATE ASC
)
WHERE ROWNUM < 2;
SELECT mr_header_id, csi_item_instance_id,
related_mr_header_id,
related_csi_item_instance_id
FROM ahl_applicable_mr_relns
WHERE level = p_level
START WITH mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_mr_header_id = mr_header_id AND
PRIOR related_csi_item_instance_id = csi_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
ORDER BY level, mr_header_id, csi_item_instance_id;
SELECT mr_header_id, csi_item_instance_id, related_mr_header_id,
related_csi_item_instance_id
FROM ahl_applicable_mr_relns
START WITH mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_mr_header_id = mr_header_id AND
PRIOR related_csi_item_instance_id = csi_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
ORDER BY level, mr_header_id, csi_item_instance_id;
SELECT ue_id, related_ue_id
FROM ahl_ue_relationships relns
WHERE level = p_level
START WITH ue_id = p_unit_effectivity_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_ue_id = ue_id AND
originator_ue_id = p_unit_effectivity_id AND
relationship_code = 'PARENT'
ORDER BY level;
SELECT ue1.mr_header_id, ue1.csi_item_instance_id,
ue2.mr_header_id related_mr_header_id,
ue2.csi_item_instance_id related_csi_item_instance_id
--FROM ahl_unit_effectivities_app_v ue1, ahl_unit_effectivities_app_v ue2
FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
WHERE ue1.unit_effectivity_id = p_ue_id AND
ue2.unit_effectivity_id = p_related_ue_id;
SELECT 'x'
FROM ahl_applicable_mrs
WHERE csi_item_instance_id = p_item_instance_id AND
mr_header_id = p_mr_header_id AND
implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
preceding_mr_header_id IS NOT NULL;
l_ue_details_tbl.DELETE(j);
l_grp_details_tbl.DELETE(i);
SELECT mr.mr_header_id,
apmr.csi_item_instance_id,
apmr.Implement_status_code,
apmr.copy_accomplishment_code,
apmr.show_repetitive_code,
--apmr.preceding_mr_header_id, -- fix for bug# 5922149
curr_mr.mr_header_id preceding_mr_header_id,
apmr.descendent_count,
mr.whichever_first_code,
apmr.repetitive_flag
FROM ahl_mr_headers_app_v mr, ahl_mr_headers_b curr_mr, ahl_applicable_mrs apmr
--fix for bug number 5922149
--WHERE mr.preceding_mr_header_id = curr_mr.mr_header_id AND
WHERE mr.preceding_mr_header_id IN (SELECT t.mr_header_id FROM ahl_mr_headers_b t where t.title = curr_mr.title ) AND
curr_mr.mr_header_id = p_mr_header_id AND
apmr.mr_header_id = mr.mr_header_id AND
-- Fix for bug# 6711228.
-- validation moved to before this procedure call.
-- curr_mr.implement_status_code = 'MANDATORY' AND
trunc(sysdate) >= trunc(nvl(mr.effective_from, sysdate)) AND
trunc(sysdate) <= trunc(nvl(mr.effective_to, sysdate+1)) AND
apmr.csi_item_instance_id = p_item_instance_id;
SELECT mr.mr_header_id,
mr.version_number,
apmr.csi_item_instance_id,
apmr.Implement_status_code,
apmr.copy_accomplishment_code,
apmr.show_repetitive_code,
--apmr.preceding_mr_header_id,
apmr.descendent_count,
mr.whichever_first_code,
apmr.repetitive_flag,
mr.title,
mr.effective_from,
mr.effective_to
FROM ahl_mr_headers_app_v mr, ahl_applicable_mrs apmr
WHERE mr.preceding_mr_header_id IN (SELECT t.mr_header_id FROM ahl_mr_headers_app_v t where t.title = p_curr_mr_title ) AND
apmr.mr_header_id = mr.mr_header_id AND
trunc(sysdate) >= trunc(nvl(mr.effective_from, sysdate)) AND
trunc(sysdate) <= trunc(nvl(mr.effective_to, sysdate+1)) AND
apmr.csi_item_instance_id = p_item_instance_id;
PROCEDURE Update_check_flag (p_applicable_mrs_rec IN applicable_mrs_rec_type,
p_dependent_mr_flag IN BOOLEAN,
p_next_due_date_rec IN next_due_date_rec_type)
IS
l_preceding_check_flag VARCHAR2(1);
AHL_DEBUG_PUB.Debug('Start Update Check Flag');
UPDATE ahl_temp_unit_effectivities
SET preceding_check_flag = 'N'
WHERE csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
AND mr_header_id = p_applicable_mrs_rec.mr_header_id
AND due_date >= p_next_due_date_rec.due_date;
/* this MR has its accomplishments; update all records irrespective of due date */
UPDATE ahl_temp_unit_effectivities
SET preceding_check_flag = 'N'
WHERE csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
AND mr_header_id = p_applicable_mrs_rec.mr_header_id;
AHL_DEBUG_PUB.Debug('End Update Check Flag');
END Update_check_flag;
insert into ahl_temp_unit_effectivities (
csi_item_instance_id,
MR_header_id,
due_date,
mr_interval_id,
mr_effectivity_id,
due_counter_value,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
orig_forecast_sequence,
preceding_csi_item_instance_id,
preceding_mr_header_id,
preceding_forecast_seq,
forecast_sequence,
tolerance_before,
tolerance_after,
preceding_check_flag,
unit_effectivity_id,
repetitive_mr_flag,
tolerance_flag,
message_code,
service_line_id,
program_mr_header_id,
earliest_due_date,
latest_due_date,
counter_id)
values (
X_unit_effectivity_rec.csi_item_instance_id,
X_unit_effectivity_rec.MR_header_id,
X_unit_effectivity_rec.due_date,
X_unit_effectivity_rec.mr_interval_id,
X_unit_effectivity_rec.mr_effectivity_id,
X_unit_effectivity_rec.due_counter_value,
X_unit_effectivity_rec.parent_csi_item_instance_id,
X_unit_effectivity_rec.parent_mr_header_id,
X_unit_effectivity_rec.orig_csi_item_instance_id,
X_unit_effectivity_rec.orig_mr_header_id,
X_unit_effectivity_rec.orig_forecast_sequence,
X_unit_effectivity_rec.preceding_csi_item_instance_id,
X_unit_effectivity_rec.preceding_mr_header_id,
X_unit_effectivity_rec.preceding_forecast_seq,
X_unit_effectivity_rec.forecast_sequence,
X_unit_effectivity_rec.tolerance_before,
X_unit_effectivity_rec.tolerance_after,
X_unit_effectivity_rec.preceding_check_flag,
X_unit_effectivity_rec.unit_effectivity_id,
X_unit_effectivity_rec.repetitive_mr_flag,
X_unit_effectivity_rec.tolerance_flag,
X_unit_effectivity_rec.message_code,
X_unit_effectivity_rec.service_line_id,
X_unit_effectivity_rec.program_mr_header_id,
X_unit_effectivity_rec.earliest_due_date,
X_unit_effectivity_rec.latest_due_date,
X_unit_effectivity_rec.counter_id
);
INSERT INTO AHL_APPLICABLE_MRS (
CSI_ITEM_INSTANCE_ID,
MR_HEADER_ID,
MR_EFFECTIVITY_ID,
REPETITIVE_FLAG ,
SHOW_REPETITIVE_CODE,
IMPLEMENT_STATUS_CODE,
WHICHEVER_FIRST_CODE,
SERVICE_LINE_ID,
PROGRAM_MR_HEADER_ID,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
COVERAGE_IMP_LEVEL,
PM_SCHEDULE_EXISTS)
VALUES ( l_appl_activities_tbl(j).ITEM_INSTANCE_ID,
l_appl_activities_tbl(j).MR_HEADER_ID,
l_appl_activities_tbl(j).MR_EFFECTIVITY_ID,
l_appl_activities_tbl(j).REPETITIVE_FLAG,
l_appl_activities_tbl(j).SHOW_REPETITIVE_CODE,
l_appl_activities_tbl(j).IMPLEMENT_STATUS_CODE,
l_appl_activities_tbl(j).WHICHEVER_FIRST_CODE,
l_appl_activities_tbl(j).SERVICE_LINE_ID,
l_appl_activities_tbl(j).PROGRAM_MR_HEADER_ID,
l_appl_programs_tbl(i).SERVICE_START_DATE,
l_appl_programs_tbl(i).SERVICE_END_DATE,
l_appl_programs_tbl(i).COVERAGE_TYPE_IMP_LEVEL,
nvl(l_appl_activities_tbl(j).ACT_SCHEDULE_EXISTS,'N')
);
AHL_DEBUG_PUB.debug('Successfully inserted for Act ID:' || l_appl_activities_tbl(j).MR_HEADER_ID);
SELECT DISTINCT appl.program_mr_header_id, mr.whichever_first_code
FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
-- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has
-- the filter of application_usg_code.
WHERE appl.program_mr_header_id = mr.mr_header_id
AND pm_schedule_exists = 'N';
SELECT mr_effectivity_id, program_duration, program_duration_uom_code,
threshold_date
FROM ahl_mr_effectivities
where mr_effectivity_id = p_mr_effectivity_id;
SELECT start_value, stop_value, counter_id, counter_name, mr_interval_id
FROM ahl_mr_intervals_v
WHERE mr_effectivity_id = p_mr_effectivity_id;
l_UnSch_program_tbl.DELETE;
l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
' SET program_end_date = :1' ||
' WHERE program_mr_header_id = :2'||
' AND PM_schedule_exists = :3';
l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
' SET program_end_date = LEAST (nvl(:1,contract_end_date),' ||
' decode(:2,0, contract_end_date, contract_start_date + :3),' ||
' contract_end_date)' ||
' WHERE program_mr_header_id = :4' ||
' AND PM_schedule_exists = :5';
l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
' SET program_end_date = LEAST ( GREATEST (nvl(:1,contract_end_date),' ||
' decode(:2,0, contract_end_date, contract_start_date + :3)),' ||
' contract_end_date)' ||
' WHERE program_mr_header_id = :4' ||
' AND PM_schedule_exists = :5';
SELECT DISTINCT mr_header_id, csi_item_instance_id, service_line_id,
program_mr_header_id, contract_end_date, program_end_date,
show_repetitive_code,
repetitive_flag
FROM ahl_applicable_mrs
WHERE pm_schedule_exists = 'Y';
SELECT mr_header_id, service_line_id, mr_effectivity_id,
program_mr_header_id, contract_end_date, program_end_date
FROM ahl_applicable_mrs
WHERE mr_header_id = p_mr_header_id
AND pm_schedule_exists = 'Y'
ORDER BY coverage_imp_level , contract_start_date;
SELECT due_date
FROM ahl_unit_effectivities_b
WHERE mr_header_id = p_mr_header_id
AND csi_item_instance_id = p_csi_item_instance_id
AND trunc(accomplished_date) = trunc(p_accomplishment_date)
AND status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
ORDER BY due_date desc;
SELECT due_date, accomplished_date
FROM ahl_unit_effectivities_b
WHERE mr_header_id = p_mr_header_id
AND csi_item_instance_id = p_csi_item_instance_id
AND service_line_id = p_service_line_id
--AND trunc(accomplished_date) = trunc(p_accomplishment_date)
AND status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
ORDER BY accomplished_date desc, due_date desc;
l_del_SQLstmt_str := 'DELETE FROM ahl_applicable_mrs' ||
' WHERE mr_header_id = :1 AND pm_schedule_exists = ''N'' '||
' AND service_line_id <> :2';
SELECT DISTINCT appl.mr_header_id,
appl.csi_item_instance_id,
appl.whichever_first_code,
appl.repetitive_flag,
appl.show_repetitive_code,
mr.effective_to,
mr.effective_from
FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
WHERE appl.mr_header_id = mr.mr_header_id AND
appl.pm_schedule_exists = 'N';
SELECT program_mr_header_id,
service_line_id, contract_start_date, contract_end_date, program_end_date
FROM ahl_applicable_mrs
WHERE mr_header_id = p_mr_header_id
AND pm_schedule_exists = 'N'
AND trunc(program_end_date) >= trunc(p_due_date) -- eliminate expired programs.
AND trunc(p_due_date) >= trunc(contract_start_date)
AND trunc(p_due_date) <= LEAST(trunc(contract_end_date), nvl(trunc(program_end_date),trunc(contract_end_date)))
ORDER BY contract_start_date;
SELECT program_mr_header_id,
service_line_id,
contract_start_date, contract_end_date, program_end_date
FROM ahl_applicable_mrs
WHERE pm_schedule_exists = 'N'
AND trunc(program_end_date) >= trunc(p_due_date) -- eliminate expired programs.
AND mr_header_id = p_mr_header_id
AND trunc(p_due_date) <= trunc(contract_start_date)
ORDER BY contract_start_date;
SELECT DISTINCT mr_header_id, csi_item_instance_id, service_line_id
FROM ahl_temp_unit_effectivities;
SELECT ue.unit_effectivity_id
FROM ahl_unit_effectivities_b UE
WHERE mr_header_id = p_mr_header_id
AND csi_item_instance_id = p_csi_item_instance_id
AND application_usg_code = p_appl_usg_code
AND service_line_id = p_service_line_id
AND (UE.Status_code IS NULL OR status_code IN ('INIT-DUE','DEFERRED'))
--ORDER BY forecast_sequence ASC;
SELECT unit_effectivity_id
FROM ahl_temp_unit_effectivities
WHERE mr_header_id = p_mr_header_id
AND csi_item_instance_id = p_csi_item_instance_id
AND service_line_id = p_service_line_id
FOR UPDATE OF unit_effectivity_id
ORDER BY forecast_sequence ASC;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_unit_effectivity_id
WHERE CURRENT OF ahl_temp_effectivity_csr;
l_ue_id_tbl.DELETE;
SELECT csi_item_instance_id, mr_header_id
FROM ahl_unit_effectivities_app_v
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT whichever_first_code
FROM ahl_mr_headers_app_v
WHERE mr_header_id = p_mr_header_id;
SELECT position_reference
FROM csi_ii_relationships
WHERE 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));
SELECT nvl(net_reading, 0)
FROM csi_cp_counters_v
WHERE customer_product_id = p_csi_item_instance_id
AND counter_id = p_counter_id;
SELECT nvl(cv.net_reading,0) net_reading
FROM csi_counter_values_v cv
WHERE cv.counter_id = p_counter_id
AND cv.counter_id IN (select counter_id
from csi_counter_associations cca
where source_object_code = 'CP'
AND source_object_id = p_csi_item_instance_id
AND cca.counter_id = cv.counter_id)
AND rownum < 2;
/*SELECT net_reading
FROM cs_ctr_counter_values_v cv, cs_counter_groups cg
WHERE cv.counter_group_id = cg.counter_group_id
AND cg.source_object_code = 'CP'
AND cg.source_object_id = p_csi_item_instance_id
AND cv.counter_id = p_counter_id
AND trunc(VALUE_TIMESTAMP) <= trunc(p_reading_date)
ORDER BY cv.value_timestamp desc; */
SELECT --DISTINCT
CCR.NET_READING
FROM
CSI_COUNTERS_VL CC,
--CS_COUNTER_GROUPS CCA,
--priyan
--changes for Bug #5207990.
CS_CSI_COUNTER_GROUPS CCA,
CSI_COUNTER_READINGS CCR
WHERE
CC.DEFAULTED_GROUP_ID (+) = CCA.COUNTER_GROUP_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCR.COUNTER_ID = CC.COUNTER_ID
AND CCA.SOURCE_OBJECT_ID = P_CSI_ITEM_INSTANCE_ID
AND CC.COUNTER_ID = P_COUNTER_ID
AND TRUNC(CCR.VALUE_TIMESTAMP) <= TRUNC(P_READING_DATE)
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT * FROM (
SELECT CCR.NET_READING
FROM
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = P_COUNTER_ID
AND nvl(CCR.disabled_flag,'N') = 'N'
AND TRUNC(CCR.VALUE_TIMESTAMP) <= TRUNC(P_READING_DATE)
ORDER BY
CCR.VALUE_TIMESTAMP DESC
)
WHERE rownum < 2;
SELECT value_timestamp
FROM cs_ctr_counter_values_v cv, cs_counter_groups cg
WHERE cv.counter_group_id = cg.counter_group_id
AND cg.source_object_code = 'CP'
AND cg.source_object_id = p_csi_item_instance_id
AND cv.counter_id = p_counter_id
AND nvl(cv.net_reading,0) >= p_counter_value
ORDER BY value_timestamp asc;
SELECT --DISTINCT
CCR.VALUE_TIMESTAMP
FROM
CSI_COUNTERS_VL CC,
--CS_COUNTER_GROUPS CCA,
--priyan
--Refer Bug # 5207990 for changes.
CS_CSI_COUNTER_GROUPS CCA,
CSI_COUNTER_READINGS CCR
WHERE
CC.DEFAULTED_GROUP_ID (+) = CCA.COUNTER_GROUP_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCR.COUNTER_ID = CC.COUNTER_ID
AND CCA.SOURCE_OBJECT_ID = P_CSI_ITEM_INSTANCE_ID
AND CC.COUNTER_ID = P_COUNTER_ID
AND NVL(CCR.NET_READING,0) >= P_COUNTER_VALUE
ORDER BY
CCR.VALUE_TIMESTAMP ASC;
SELECT * FROM (
SELECT CCR.VALUE_TIMESTAMP
FROM CSI_COUNTER_READINGS CCR
WHERE CCR.COUNTER_ID = P_COUNTER_ID
AND nvl(disabled_flag,'N') = 'N'
AND NVL(CCR.NET_READING,0) >= P_COUNTER_VALUE
ORDER BY CCR.VALUE_TIMESTAMP ASC
)
WHERE rownum < 2;
SELECT
UE.UNIT_EFFECTIVITY_ID,
UE.OBJECT_TYPE,
UE.CSI_ITEM_INSTANCE_ID,
UE.MR_HEADER_ID,
UE.STATUS_CODE,
UE.DEFER_FROM_UE_ID,
nvl(MR.whichever_first_code, 'FIRST') whichever_first_code,
UDF.unit_deferral_type, UE.CS_INCIDENT_ID, UDF.DEFERRAL_EFFECTIVE_ON,
UDF.AFFECT_DUE_CALC_FLAG, UDF.SET_DUE_DATE, UDF.unit_deferral_id
FROM ahl_unit_effectivities_app_v UE, ahl_unit_deferrals_b UDF, ahl_mr_headers_b MR
WHERE UE.defer_from_ue_id = UDF.unit_effectivity_id
AND UE.mr_header_id = MR.mr_header_id(+)
AND csi_item_instance_id = p_csi_item_instance_id
AND status_code IS NULL
--AND defer_from_ue_id IS NOT NULL -- not required as joining table ahl_unit_deferrals_b
AND UDF.unit_deferral_type IN ('DEFERRAL', 'MEL','CDL')
AND NOT EXISTS (SELECT 'x'
FROM ahl_ue_relationships
WHERE related_ue_id = UE.unit_effectivity_id
AND relationship_code = 'PARENT')
AND UDF.approval_status_code = 'DEFERRED'
ORDER BY DEFERRAL_EFFECTIVE_ON ASC;
SELECT AFFECT_DUE_CALC_FLAG, SET_DUE_DATE, unit_deferral_id, deferral_effective_on
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_deferred_from_ue_id
AND unit_deferral_type = 'DEFERRAL';
SELECT counter_id, counter_value, ctr_value_type_code
FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_unit_deferral_id;
SELECT related_mr_header_id,
related_csi_item_instance_id,
csi_item_instance_id parent_csi_item_instance_id,
mr_header_id parent_mr_header_id
FROM ahl_applicable_mr_relns
START WITH mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_mr_header_id = mr_header_id AND
PRIOR related_csi_item_instance_id = csi_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
ORDER BY level;
select nvl(repair_time,0), cs.expected_resolution_date
from ahl_repair_categories rc, cs_incidents_all_b cs
where rc.sr_urgency_id(+) = cs.incident_urgency_id
and cs.incident_id = p_cs_incident_id;
SELECT ue.unit_effectivity_id, ue.status_code,
UE.CSI_ITEM_INSTANCE_ID, UE.MR_HEADER_ID
FROM ahl_ue_relationships uer, ahl_unit_effectivities_app_v ue
WHERE ue.unit_effectivity_id = uer.related_ue_id
AND uer.ue_id = p_ue_id;
insert into ahl_temp_unit_SR_deferrals (
unit_effectivity_id,
object_type,
csi_item_instance_id,
mr_header_id,
due_date,
counter_id,
due_counter_value,
tolerance_flag,
message_code,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
orig_unit_effectivity_id,
visit_end_date,
deferral_effective_on,
affect_due_calc_flag,
group_match_flag)
VALUES (
unit_effectivity_rec.unit_effectivity_id,
unit_effectivity_rec.object_type,
unit_effectivity_rec.csi_item_instance_id,
unit_effectivity_rec.mr_header_id,
l_calc_due_date,
l_calc_counter_id,
l_calc_due_counter_value,
l_calc_tolerance_flag,
l_calc_message_code,
null,
null,
null,
null,
null,
l_visit_end_date,
l_deferral_effective_on,
--l_affect_due_date_calc,
unit_effectivity_rec.AFFECT_DUE_CALC_FLAG,
l_grp_match);
insert into ahl_temp_unit_SR_deferrals (
unit_effectivity_id,
object_type,
csi_item_instance_id,
mr_header_id,
due_date,
due_counter_value,
tolerance_flag,
message_code,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
orig_unit_effectivity_id,
visit_end_date,
deferral_effective_on,
affect_due_calc_flag,
group_match_flag)
VALUES (
null,
'MR',
ahl_applicable_grp_rec.related_csi_item_instance_id,
ahl_applicable_grp_rec.related_mr_header_id,
l_calc_due_date,
l_calc_due_counter_value,
l_calc_tolerance_flag,
l_calc_message_code,
ahl_applicable_grp_rec.parent_csi_item_instance_id,
ahl_applicable_grp_rec.parent_mr_header_id,
unit_effectivity_rec.csi_item_instance_id,
unit_effectivity_rec.mr_header_id,
unit_effectivity_rec.unit_effectivity_id,
l_visit_end_date,
l_deferral_effective_on,
--l_affect_due_date_calc,
unit_effectivity_rec.AFFECT_DUE_CALC_FLAG,
l_grp_match);
SELECT
UE.UNIT_EFFECTIVITY_ID,
UE.OBJECT_TYPE,
UE.CSI_ITEM_INSTANCE_ID,
UE.MR_HEADER_ID,
UE.STATUS_CODE,
UE.DUE_DATE,
UE.DUE_COUNTER_VALUE
FROM ahl_unit_effectivities_app_v UE
WHERE UE.OBJECT_TYPE = 'SR'
AND defer_from_ue_id IS NULL
AND csi_item_instance_id = p_csi_item_instance_id
AND status_code IS NULL;
SELECT related_mr_header_id,
related_csi_item_instance_id,
csi_item_instance_id parent_csi_item_instance_id,
mr_header_id parent_mr_header_id
FROM ahl_applicable_mr_relns
START WITH mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_mr_header_id = mr_header_id AND
PRIOR related_csi_item_instance_id = csi_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
ORDER BY level;
SELECT ue.unit_effectivity_id, ue.status_code,
UE.CSI_ITEM_INSTANCE_ID, UE.MR_HEADER_ID
FROM ahl_ue_relationships uer, ahl_unit_effectivities_app_v ue
WHERE ue.unit_effectivity_id = uer.related_ue_id
AND uer.ue_id = p_ue_id;
insert into ahl_temp_unit_SR_deferrals (
unit_effectivity_id,
object_type,
csi_item_instance_id,
mr_header_id,
due_date,
due_counter_value,
tolerance_flag,
message_code,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_mr_header_id,
orig_csi_item_instance_id,
orig_unit_effectivity_id,
visit_end_date,
deferral_effective_on,
affect_due_calc_flag,
group_match_flag)
VALUES (
ahl_ue_sr_rec.unit_effectivity_id,
ahl_ue_sr_rec.object_type,
ahl_ue_sr_rec.csi_item_instance_id,
null,
ahl_ue_sr_rec.due_date,
ahl_ue_sr_rec.due_counter_value,
l_calc_tolerance_flag,
l_calc_message_code,
null,
null,
null,
null,
null,
l_visit_end_date,
null,
'Y',
l_grp_match);
insert into ahl_temp_unit_SR_deferrals (
unit_effectivity_id,
object_type,
csi_item_instance_id,
mr_header_id,
due_date,
due_counter_value,
tolerance_flag,
message_code,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
orig_unit_effectivity_id,
visit_end_date,
deferral_effective_on,
affect_due_calc_flag,
group_match_flag)
VALUES (
null,
null,
ahl_applicable_grp_rec.related_csi_item_instance_id,
ahl_applicable_grp_rec.related_mr_header_id,
ahl_ue_sr_rec.due_date,
ahl_ue_sr_rec.due_counter_value,
l_calc_tolerance_flag,
l_calc_message_code,
ahl_applicable_grp_rec.parent_csi_item_instance_id,
ahl_applicable_grp_rec.parent_mr_header_id,
ahl_ue_sr_rec.csi_item_instance_id,
ahl_ue_sr_rec.mr_header_id,
ahl_ue_sr_rec.unit_effectivity_id,
l_visit_end_date,
null,
'Y',
l_grp_match);
SELECT related_ue_id, ue_id, level
FROM ahl_ue_relationships
START WITH ue_id = p_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY PRIOR related_ue_id = ue_id
AND relationship_code = 'PARENT'
ORDER BY level;
SELECT ue1.mr_header_id, ue1.csi_item_instance_id,
ue2.mr_header_id related_mr_header_id,
ue2.csi_item_instance_id related_csi_item_instance_id
FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
WHERE ue1.unit_effectivity_id = p_ue_id AND
ue2.unit_effectivity_id = p_related_ue_id;
SELECT mr_header_id, csi_item_instance_id,
related_mr_header_id,
related_csi_item_instance_id, level
FROM ahl_applicable_mr_relns
START WITH mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_mr_header_id = mr_header_id AND
PRIOR related_csi_item_instance_id = csi_item_instance_id AND
orig_mr_header_id = p_mr_header_id AND
orig_csi_item_instance_id = p_item_instance_id AND
relationship_code = 'PARENT'
ORDER BY level;
l_ue_details_tbl.DELETE(j);
l_grp_details_tbl.DELETE(i);
SELECT ue.mr_header_id,
ue.csi_item_instance_id,
ue.unit_effectivity_id,
ue.status_code
FROM ahl_unit_effectivities_app_v ue
/*
WHERE NOT EXISTS ( SELECT 'x'
--FROM ahl_ue_relationships uer, ahl_unit_effectivities_app_v ue1
FROM ahl_ue_relationships uer, ahl_unit_effectivities_b ue1
WHERE uer.related_ue_id = ue.unit_effectivity_id AND
uer.originator_ue_id = ue1.unit_effectivity_id AND
ue1.object_type <> 'SR' )
*/
-- pick up only top nodes.
WHERE NOT EXISTS (SELECT 'x'
FROM ahl_ue_relationships uer
WHERE uer.related_ue_id = ue.unit_effectivity_id
AND relationship_code = 'PARENT')
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND nvl(ue.manually_planned_flag,'N') = 'Y'
AND ue.object_type = 'MR'
AND ue.defer_from_ue_id IS NULL
AND (ue.status_code IS NULL OR ue.status_code = 'EXCEPTION');
SELECT 'x'
FROM ahl_applicable_mrs
WHERE mr_header_id = p_mr_header_id AND
csi_item_instance_id = p_csi_item_instance_id;
SELECT
UNIT_EFFECTIVITY_ID ,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID ,
MR_HEADER_ID,
STATUS_CODE ,
DUE_DATE ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
REMARKS ,
MESSAGE_CODE ,
PRECEDING_UE_ID ,
DATE_RUN ,
SET_DUE_DATE ,
ACCOMPLISHED_DATE ,
CANCEL_REASON_CODE,
EARLIEST_DUE_DATE,
LATEST_DUE_DATE,
SERVICE_LINE_ID,
PROGRAM_MR_HEADER_ID,
defer_from_ue_id,
cs_incident_id,
qa_collection_id,
orig_deferral_ue_id,
application_usg_code,
object_type,
counter_id,
manually_planned_flag,
LOG_SERIES_CODE,
LOG_SERIES_NUMBER, FLIGHT_NUMBER, MEL_CDL_TYPE_CODE,
POSITION_PATH_ID,
ATA_CODE, UNIT_CONFIG_HEADER_ID,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER
FROM ahl_unit_effectivities_vl
WHERE unit_effectivity_id = p_unit_effectivity_id
FOR UPDATE OF message_code NOWAIT;
SELECT related_ue_id
FROM ahl_ue_relationships
WHERE relationship_code = 'PARENT' AND
originator_ue_id = p_unit_effectivity_id;
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_ue_rec.mr_header_id,
X_STATUS_CODE => l_status_code,
X_DUE_DATE => l_ue_rec.due_date,
X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
X_REMARKS => l_ue_rec.remarks,
X_MESSAGE_CODE => l_message_code,
X_PRECEDING_UE_ID => l_ue_rec.preceding_ue_id,
X_DATE_RUN => sysdate,
X_SET_DUE_DATE => l_ue_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
X_cs_incident_id => l_ue_rec.cs_incident_id,
X_qa_collection_id => l_ue_rec.qa_collection_id,
X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
X_application_usg_code => l_ue_rec.application_usg_code,
X_object_type => l_ue_rec.object_type,
X_counter_id => l_ue_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_rec.position_path_id,
X_ATA_CODE => l_ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
X_ATTRIBUTE1 => l_ue_rec.attribute1,
X_ATTRIBUTE2 => l_ue_rec.attribute2,
X_ATTRIBUTE3 => l_ue_rec.attribute3,
X_ATTRIBUTE4 => l_ue_rec.attribute4,
X_ATTRIBUTE5 => l_ue_rec.attribute5,
X_ATTRIBUTE6 => l_ue_rec.attribute6,
X_ATTRIBUTE7 => l_ue_rec.attribute7,
X_ATTRIBUTE8 => l_ue_rec.attribute8,
X_ATTRIBUTE9 => l_ue_rec.attribute9,
X_ATTRIBUTE10 => l_ue_rec.attribute10,
X_ATTRIBUTE11 => l_ue_rec.attribute11,
X_ATTRIBUTE12 => l_ue_rec.attribute12,
X_ATTRIBUTE13 => l_ue_rec.attribute13,
X_ATTRIBUTE14 => l_ue_rec.attribute14,
X_ATTRIBUTE15 => l_ue_rec.attribute15,
X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number + 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_ue_rec.mr_header_id,
X_STATUS_CODE => l_status_code,
X_DUE_DATE => l_ue_rec.due_date,
X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
X_REMARKS => l_ue_rec.remarks,
X_MESSAGE_CODE => l_message_code,
X_PRECEDING_UE_ID => l_ue_rec.preceding_ue_id,
X_DATE_RUN => sysdate,
X_SET_DUE_DATE => l_ue_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
X_cs_incident_id => l_ue_rec.cs_incident_id,
X_qa_collection_id => l_ue_rec.qa_collection_id,
X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
X_application_usg_code => l_ue_rec.application_usg_code,
X_object_type => l_ue_rec.object_type,
X_counter_id => l_ue_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_rec.position_path_id,
X_ATA_CODE => l_ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
X_ATTRIBUTE1 => l_ue_rec.attribute1,
X_ATTRIBUTE2 => l_ue_rec.attribute2,
X_ATTRIBUTE3 => l_ue_rec.attribute3,
X_ATTRIBUTE4 => l_ue_rec.attribute4,
X_ATTRIBUTE5 => l_ue_rec.attribute5,
X_ATTRIBUTE6 => l_ue_rec.attribute6,
X_ATTRIBUTE7 => l_ue_rec.attribute7,
X_ATTRIBUTE8 => l_ue_rec.attribute8,
X_ATTRIBUTE9 => l_ue_rec.attribute9,
X_ATTRIBUTE10 => l_ue_rec.attribute10,
X_ATTRIBUTE11 => l_ue_rec.attribute11,
X_ATTRIBUTE12 => l_ue_rec.attribute12,
X_ATTRIBUTE13 => l_ue_rec.attribute13,
X_ATTRIBUTE14 => l_ue_rec.attribute14,
X_ATTRIBUTE15 => l_ue_rec.attribute15,
X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number + 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
SELECT * FROM (
SELECT CCR.NET_READING
FROM
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = P_COUNTER_ID
AND nvl(disabled_flag,'N') = 'N'
AND CCR.VALUE_TIMESTAMP <= P_READING_DATE
ORDER BY CCR.VALUE_TIMESTAMP DESC
)
WHERE ROWNUM < 2;
SELECT mr_header_id
FROM ahl_mr_headers_app_v
WHERE mr_header_id = c_mr_header_id;
SELECT mr_header_id, mr_effectivity_id, inventory_item_id
FROM ahl_mr_effectivities_app_v
WHERE mr_header_id = c_mr_header_id;
SELECT itm.organization_id
FROM mtl_system_items_b itm, mtl_parameters mtl
WHERE itm.inventory_item_id = c_inventory_item_id
AND itm.organization_id = mtl.organization_id
AND mtl.master_organization_id = mtl.organization_id;
SELECT min(instance_id), max(instance_id), count(instance_id)
FROM csi_item_instances
WHERE inventory_item_id = c_inventory_item_id
AND inv_master_organization_id = c_inventory_org_id
AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and
trunc(nvl(active_end_date,sysdate+1))
GROUP BY inventory_item_id, inv_master_organization_id;
SELECT cii.instance_id
FROM csi_item_instances cii, ahl_mr_effectivities mre, mtl_system_items_b msi
WHERE cii.inventory_item_id = msi.inventory_item_id
AND cii.inv_master_organization_id = msi.organization_id
AND cii.inventory_item_id = mre.inventory_item_id
AND mre.mr_header_id = p_mr_header_id
AND SYSDATE between trunc(nvl(cii.active_start_date,sysdate)) and
trunc(nvl(cii.active_end_date,sysdate+1))
AND cii.instance_id >= c_start_inst_id
AND cii.instance_id <= c_end_inst_id;
SELECT count(instance_id)
FROM csi_item_instances csi, ahl_mr_effectivities me
WHERE csi.instance_id >= p_start_inst_id and csi.instance_id <= p_end_inst_id
AND csi.inventory_item_id = me.inventory_item_id
AND me.mr_header_id = p_mr_header_id
AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and
trunc(nvl(active_end_date,sysdate+1));
SELECT cii.instance_id
FROM csi_item_instances cii, ahl_mr_effectivities mre, mtl_system_items_b msi
-- repalced mtl_system_items_kfv with mtl_system items_b.
WHERE cii.inventory_item_id = msi.inventory_item_id
AND cii.inv_master_organization_id = msi.organization_id
AND cii.inventory_item_id = mre.inventory_item_id
AND mre.mr_header_id = p_mr_header_id
AND SYSDATE between trunc(nvl(cii.active_start_date,sysdate)) and
trunc(nvl(cii.active_end_date,sysdate+1))
AND cii.instance_id >= c_start_inst_id
AND cii.instance_id <= c_end_inst_id;
SELECT cii.instance_id
FROM csi_item_instances cii, ahl_mr_effectivities mre, mtl_system_items_b msi,
(select mr_header_id
from ahl_mr_headers_app_v
where type_code = 'PROGRAM') mr
WHERE cii.inventory_item_id = msi.inventory_item_id
AND cii.inv_master_organization_id = msi.organization_id
AND cii.inventory_item_id = mre.inventory_item_id
AND mre.mr_header_id = mr.mr_header_id
AND SYSDATE between trunc(nvl(cii.active_start_date,sysdate)) and
trunc(nvl(cii.active_end_date,sysdate+1))
AND cii.instance_id >= c_start_inst_id
AND cii.instance_id <= c_end_inst_id;
SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL;
SELECT csi_item_instance_id
FROM ahl_unit_config_headers uc
WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
AND exists (select 'x'
from mtl_category_set_valid_cats cs, mtl_item_categories itc,
csi_item_instances cii2
where cs.category_set_id = fnd_profile.value('AHL_BUE_ITEM_CATEGORY_SET')
AND cs.category_set_id = itc.category_set_id
AND cs.category_id = itc.category_id
AND itc.category_id = p_mtl_category_id
AND cii2.instance_id = uc.csi_item_instance_id
AND itc.organization_id = cii2.inv_master_organization_id
AND itc.inventory_item_id = cii2.inventory_item_id
); -- get units matching item category.
SELECT instance_id from
(SELECT DISTINCT nvl(root_instance_id, instance_id) instance_id from
(
SELECT cii.instance_id,
(select object_id from csi_ii_relationships parent
where not exists (select 'x' from csi_ii_relationships
where subject_id = parent.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)))
start with parent.subject_id = cii.instance_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
connect by prior parent.object_id = parent.subject_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
) Root_instance_id
FROM csi_item_instances cii, ahl_mr_effectivities mre
WHERE mre.inventory_item_id = nvl(null, mre.inventory_item_id)
AND mre.mr_header_id = nvl(null,mre.mr_header_id)
-- added nvl conditions above as this seems to force use of index on
-- ahl_mr_headers_b and also brings query cost down.
AND mre.relationship_id is null
AND mre.inventory_item_id = cii.inventory_item_id
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = mre.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC')
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_app_v MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
AND trunc(nvl(cii.active_start_date, sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
)
) valid_inst
WHERE /*(p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
) -- get UCs only.
)
OR */ -- this cursor is not used when p_opt_uc = 1
-- get components.
(p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
)
OR
-- get all but ignore UCs as they have alredy been selected.
(p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
);
SELECT instance_id from
(SELECT DISTINCT nvl(root_instance_id, instance_id) instance_id from
(
SELECT cii.instance_id,
(select object_id from csi_ii_relationships parent
where not exists (select 'x' from csi_ii_relationships
where subject_id = parent.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)))
start with parent.subject_id = cii.instance_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
connect by prior parent.object_id = parent.subject_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
) Root_instance_id
FROM csi_item_instances cii,
(select distinct me.inventory_item_id
from ahl_mr_headers_app_v mr, ahl_mr_effectivities me
where mr.mr_header_id = me.mr_header_id AND
mr.mr_status_code = 'COMPLETE' AND
MR.program_type_code NOT IN ('MO_PROC') AND -- added in R12
trunc(effective_from) <= trunc(sysdate) AND
trunc(nvl(effective_to,sysdate)) >= trunc(sysdate)
and me.inventory_item_id is not null
) mre
WHERE trunc(nvl(cii.active_start_date, sysdate)) <= trunc(sysdate) AND
trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
AND mre.inventory_item_id = cii.inventory_item_id
)
) valid_inst
WHERE exists (select 'x'
from mtl_category_set_valid_cats cs, mtl_item_categories itc,
csi_item_instances cii2
where cs.category_set_id = fnd_profile.value('AHL_BUE_ITEM_CATEGORY_SET')
AND cs.category_set_id = itc.category_set_id
AND cs.category_id = itc.category_id
AND itc.category_id = p_mtl_category_id
AND cii2.instance_id = valid_inst.instance_id
AND itc.organization_id = cii2.inv_master_organization_id
AND itc.inventory_item_id = cii2.inventory_item_id
) -- get root nodes matching item category.
AND ((p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
) -- get UCs only.
)
OR
-- get components.
(p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
)
OR
-- when process option is ALL.
-- get components in this case too as UC's have already been selected.
(p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
)
);
INSERT INTO AHL_BUE_WORKER_DATA
(parent_conc_request_id,
csi_item_instance_id,
child_conc_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number) VALUES
(p_conc_request_id,
l_instance_id_tbl(instance_indx),
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1);
l_instance_id_tbl.DELETE;
INSERT INTO AHL_BUE_WORKER_DATA
(parent_conc_request_id,
csi_item_instance_id,
child_conc_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number) VALUES
(p_conc_request_id,
l_instance_id_tbl(instance_indx),
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1);
fnd_file.put_line(fnd_file.log, 'Following error(s) occured while inserting into table ahl_bue_worker_data');
l_instance_id_tbl.DELETE;
DELETE FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_conc_request_id
AND rowid not in (SELECT MIN(rowid)
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_conc_request_id
GROUP BY csi_item_instance_id, parent_conc_request_id) ;
SELECT rowid, csi_item_instance_id, object_version_number
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_parent_conc_pgm_id
AND child_conc_request_id IS NULL;
SELECT rowid, csi_item_instance_id
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_parent_conc_pgm_id
AND child_conc_request_id IS NULL
AND ROWID = p_rowid
FOR UPDATE OF child_conc_request_id NOWAIT;
UPDATE ahl_bue_worker_data
set child_conc_request_id = p_conc_child_req_id,
last_update_date = sysdate,
object_version_number = object_version_number + 1,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE ROWID = l_rowid
AND object_version_number = l_object_version_number;
END LOOP; -- select next row.
CURSOR get_undeleted_parents(p_conc_request_id IN NUMBER) IS
SELECT DISTINCT parent_conc_request_id
FROM ahl_bue_worker_data
WHERE parent_conc_request_id <> p_conc_request_id;
CURSOR get_undeleted_child(p_conc_request_id IN NUMBER) IS
SELECT DISTINCT child_conc_request_id
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_conc_request_id;
SELECT rowid
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_parent_conc_id
AND child_conc_request_id = p_child_conc_id
FOR UPDATE OF object_version_number NOWAIT;
SELECT 'x'
FROM fnd_concurrent_requests
WHERE REQUEST_ID = p_conc_req_id;
DELETE from ahl_bue_worker_data
WHERE parent_conc_request_id = p_parent_conc_request_id
AND child_conc_request_id = p_child_conc_request_id ;
FOR undeleted_parent IN get_undeleted_parents(p_parent_conc_request_id) LOOP
/* not needed - check only child conc request status
-- check parent status.
l_req_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => undeleted_parent.parent_conc_request_id,
--appl_shortname => 'AHL',
--program => 'AHLUEFF',
phase => l_rphase,
status => l_rstatus,
dev_phase => l_dphase,
dev_status => l_dstatus,
message => l_message);
FOR undeleted_child IN get_undeleted_child(undeleted_parent.parent_conc_request_id) LOOP
l_req_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => undeleted_child.child_conc_request_id,
--appl_shortname => 'AHL',
--program => 'AHLWUEFF',
phase => l_rphase,
status => l_rstatus,
dev_phase => lc_dphase,
dev_status => l_dstatus,
message => l_message);
OPEN fnd_concur_csr(undeleted_child.child_conc_request_id);
OPEN lock_child_rows(undeleted_parent.parent_conc_request_id,
undeleted_child.child_conc_request_id);
DELETE FROM ahl_bue_worker_data
WHERE rowid = l_rowid_tbl(j);
EXIT; -- abort delete for child.
l_rowid_tbl.delete;
END LOOP; -- undeleted_child IN
END LOOP; -- undeleted_parent IN
SELECT instance_id from
(SELECT DISTINCT nvl(root_instance_id, instance_id) instance_id from
(
SELECT cii.instance_id,
(select object_id from csi_ii_relationships parent
where not exists (select 'x' from csi_ii_relationships
where subject_id = parent.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)))
start with parent.subject_id = cii.instance_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
connect by prior parent.object_id = parent.subject_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
) Root_instance_id
FROM csi_item_instances cii, ahl_mr_instances_temp mr
WHERE trunc(nvl(cii.active_start_date, sysdate)) <= trunc(sysdate) AND
trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
AND mr.item_instance_id = cii.instance_id
)
) valid_inst
WHERE (p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
) -- get UCs only.
)
OR
-- get components.
(p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
)
OR
-- get all but do not select draft UCs as Process Unit will raise error.
(p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code = 'DRAFT'
)
);
SELECT instance_id from
(SELECT DISTINCT nvl(root_instance_id, instance_id) instance_id from
(
SELECT cii.instance_id,
(select object_id from csi_ii_relationships parent
where not exists (select 'x' from csi_ii_relationships
where subject_id = parent.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)))
start with parent.subject_id = cii.instance_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
connect by prior parent.object_id = parent.subject_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
) Root_instance_id
FROM csi_item_instances cii,ahl_mr_instances_temp mr
WHERE trunc(nvl(cii.active_start_date, sysdate)) <= trunc(sysdate) AND
trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
AND mr.item_instance_id = cii.instance_id
)
) valid_inst
WHERE exists (select 'x'
from mtl_category_set_valid_cats cs, mtl_item_categories itc,
csi_item_instances cii2
where cs.category_set_id = fnd_profile.value('AHL_BUE_ITEM_CATEGORY_SET')
AND cs.category_set_id = itc.category_set_id
AND cs.category_id = itc.category_id
AND itc.category_id = p_mtl_category_id
AND cii2.instance_id = valid_inst.instance_id
AND itc.organization_id = cii2.inv_master_organization_id
AND itc.inventory_item_id = cii2.inventory_item_id
) -- get root nodes matching item category.
-- either UC or components.
AND ((p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
) -- get UCs only.
)
OR
-- get components.
(p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
-- instance is a UC if in status draft.
--AND unit_config_status_code <> 'DRAFT'
--AND parent_uc_header_id IS NULL
)
)
OR
-- when process option is ALL. Ignore Draft UC's
(p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
where csi_item_instance_id = valid_inst.instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
AND unit_config_status_code = 'DRAFT'
)
)
);
SELECT csi_item_instance_id
FROM (
SELECT DISTINCT nvl(Root_instance_id, csi_item_instance_id) csi_item_instance_id
FROM (
SELECT DISTINCT UE.csi_item_instance_id,
(select object_id from csi_ii_relationships parent
where not exists (select 'x' from csi_ii_relationships
where subject_id = parent.object_id and
trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1)))
start with parent.subject_id = UE.CSI_ITEM_INSTANCE_ID and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
connect by prior parent.object_id = parent.subject_id and
parent.relationship_type_code = 'COMPONENT-OF' and
trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1))
) Root_instance_id
FROM ahl_unit_effectivities_app_v UE
WHERE UE.mr_header_id = p_mr_header_id
AND (UE.status_code IS NULL OR UE.status_code IN ('INIT-DUE','EXCEPTION'))
AND NOT EXISTS (Select 1
FROM ahl_mr_instances_temp
WHERE item_instance_id = ue.csi_item_instance_id)
)
) valid_inst
WHERE NOT EXISTS (Select 1
FROM AHL_BUE_WORKER_DATA
WHERE csi_item_instance_id = valid_inst.csi_item_instance_id
AND parent_conc_request_id = p_conc_request_id) ;
INSERT INTO AHL_BUE_WORKER_DATA
(parent_conc_request_id,
csi_item_instance_id,
child_conc_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number) VALUES
(p_conc_request_id,
l_instance_id_tbl(instance_indx),
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1);
fnd_file.put_line(fnd_file.log, 'Following error(s) occured while inserting into table ahl_bue_worker_data');
l_instance_id_tbl.DELETE;
l_instance_id_tbl.DELETE;
INSERT INTO AHL_BUE_WORKER_DATA
(parent_conc_request_id,
csi_item_instance_id,
child_conc_request_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number) VALUES
(p_conc_request_id,
l_instance_id_tbl(instance_indx),
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1);
fnd_file.put_line(fnd_file.log, 'Following error(s) occured while inserting into table ahl_bue_worker_data');
l_instance_id_tbl.DELETE;
DELETE FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_conc_request_id
AND rowid not in (SELECT MIN(rowid)
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_conc_request_id
GROUP BY csi_item_instance_id, parent_conc_request_id) ;
SELECT * FROM
(SELECT net_reading
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND nvl(disabled_flag,'N') = 'N'
ORDER BY value_timestamp desc)
WHERE rownum < 2;