[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;
DELETE FROM AHL_UE_SIMULATIONS
WHERE SIMULATION_PLAN_ID = p_simulation_plan_id
AND UNIT_CONFIG_HEADER_ID NOT IN
(SELECT DISTINCT FU.UNIT_CONFIG_HEADER_ID
FROM AHL_FLEET_UNIT_ASSOCS FU,
AHL_UNIT_CONFIG_HEADERS UC
WHERE SIMULATION_PLAN_ID = p_simulation_plan_id
AND UC.UNIT_CONFIG_HEADER_ID = FU.UNIT_CONFIG_HEADER_ID
AND ahl_util_uc_pkg.get_uc_status_code(UC.UNIT_CONFIG_HEADER_ID) NOT IN ('DRAFT', 'EXPIRED')
);
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;
instances we will delete open UMPs.
FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
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 simulation_plan_id
FROM ahl_fleet_unit_assocs
WHERE unit_config_header_id = c_uc_header_id
AND simulation_plan_id = nvl(G_SIMULATION_PLAN_ID, get_primary_plan_id)
AND trunc(nvl(association_end, sysdate)) >= trunc(sysdate);
SELECT FUF.uom_code, FUF.period_start_date, FUF.period_end_date,FUA.association_start ,FUA.association_end, FUF.forecasted_daily_usage usage_per_day
FROM ahl_fleet_utlzn_forecast FUF ,ahl_fleet_unit_assocs FUA, ahl_fleet_headers_b FLT
WHERE FUF.fleet_header_id = FUA.fleet_header_id
AND trunc(nvl(period_end_date, sysdate)) >= trunc(sysdate)
AND trunc(nvl(association_end, sysdate)) >= trunc(sysdate)
AND trunc(nvl(period_end_date, association_start)) >= trunc(association_start)
AND trunc(period_start_date) <= trunc(nvl(association_end,period_start_date)) -- Bug 13012968
AND FUA.unit_config_header_id = c_uc_header_id
AND FUA.simulation_plan_id = c_simulation_plan_id
AND FLT.fleet_header_id = FUA.fleet_header_id
AND FLT.status_code = 'COMPLETE'
order by uom_code, association_start, period_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
FROM AHL_UNIT_EFFECTIVITIES_B UE, II
WHERE UE.csi_item_instance_id = II.INSTANCE_ID
AND UE.application_usg_code = p_appln_usg_code
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_UE_SIMULATIONS UE, II
WHERE UE.csi_item_instance_id = II.INSTANCE_ID
AND (status_code IS NULL OR status_code IN ('INIT-DUE','EXCEPTION'))
AND UE.SIMULATION_PLAN_ID = G_SIMULATION_PLAN_ID
FOR UPDATE OF status_code NOWAIT;
l_ue_id_tbl.delete;
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,
appl.processing_order, -- needed for 'order by' to work. This will be the same for all Eff.
appl.terminate_trigger_check,
appl.accomplish_trigger_type,
appl.loop_chain_seq_num
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')
-- check on preceding_mr_header_id is commented out as part of SB Enh
--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)
-- commented to support expired MRs for bug# 9263774
--AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))
-- added check for accomplish_trigger_type and start_mr_header_id for SB Enh
AND nvl(appl.accomplish_trigger_type,'X') <> 'INITIATED_BY'
-- SB ENh: only process start MR for loops and chains
AND nvl(appl.start_mr_header_id,appl.MR_header_id) = appl.MR_header_id
ORDER BY processing_order ASC, descendent_count DESC;
UPDATE AHL_APPLICABLE_MRS appl
SET processing_order = 2,
terminate_trigger_check = 'Y'
WHERE EXISTS (SELECT 'x'
FROM AHL_MR_RELATIONSHIPS mrr, ahl_applicable_mrs parent_appl
WHERE mrr.related_mr_header_id = appl.mr_header_id
AND mrr.mr_header_id = parent_appl.mr_header_id -- parent row exists
AND parent_appl.csi_item_instance_id = appl.csi_item_instance_id
AND mrr.relationship_code = 'TERMINATES');
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 (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date))
FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,
(select title from ahl_mr_headers_b where mr_header_id = p_mr_header_id) mr2
WHERE def.csi_item_instance_id = p_csi_instance_id
AND def.mr_header_id = mr1.mr_header_id
AND mr1.title = mr2.title
--AND mr_header_id = p_mr_header_id
AND def.object_type = 'MR'
AND def.deferral_effective_on IS NOT NULL
ORDER BY def.deferral_effective_on DESC;
SELECT 'x'
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr1, ahl_mr_headers_b mr2
WHERE mr2.mr_header_id = p_mr_header_id
and mr1.title = mr2.title
and mr1.version_number <> mr2.version_number
and mr1.mr_header_id = ue.mr_header_id
and ue.csi_item_instance_id = p_csi_instance_id
and (ue.status_code IS NULL or ue.status_code = 'INIT-DUE');
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 ue.unit_effectivity_id, ue.status_code, reln.related_ue_id, reln.originator_ue_id, ue.mr_header_id
--FROM ahl_unit_effectivities_app_v UE, ahl_UE_relationships reln,
FROM ahl_unit_effectivities_b UE, ahl_UE_relationships reln,
ahl_mr_headers_b mr
WHERE UE.unit_effectivity_id = RELN.RELATED_UE_ID(+)
AND UE.mr_header_id = mr.mr_header_id
AND mr.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
AND UE.csi_item_instance_id = p_csi_item_instance_id
AND UE.application_usg_code = p_appln_usg_code
AND (UE.Status_code IS NULL OR UE.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 mr.version_number ASC, 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
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) 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 mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
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
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) 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 mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
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 * FROM (
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
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
) AND
preceding_check_flag = 'N'
UNION
SELECT due_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_preceding_instance_id AND
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
)
ORDER by due_date
)
WHERE ROWNUM < 2;
SELECT ue.unit_effectivity_id
--FROM ahl_unit_effectivities_app_v ue
FROM ahl_unit_effectivities_b ue
WHERE ue.csi_item_instance_id = p_item_instance_id
AND ue.application_usg_code = p_appln_usg_code
AND ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_title)
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');
SELECT * FROM(
SELECT FUA.association_start
FROM ahl_fleet_unit_assocs FUA, ahl_fleet_headers_b FLT
WHERE FUA.unit_config_header_id = G_UC_HEADER_ID
AND FUA.simulation_plan_id = nvl(G_SIMULATION_PLAN_ID, get_primary_plan_id)
AND FLT.fleet_header_id = FUA.fleet_header_id
AND FLT.status_code = 'COMPLETE'
AND trunc(FUA.association_start) >= trunc(l_date)
ORDER BY FUA.association_start ASC)
WHERE rownum < 2;
SELECT process_status_flag
INTO l_process_status_flag
FROM ahl_applicable_MRs
WHERE mr_header_id = p_applicable_mrs_rec.mr_header_id
AND csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
AND rownum < 2;
DELETE FROM ahl_temp_unit_SR_deferrals tdef
WHERE tdef.CSI_ITEM_INSTANCE_ID = p_applicable_mrs_rec.csi_item_instance_id
AND tdef.MR_HEADER_ID IN (select MR_HEADER_ID from ahl_mr_headers_b where title = p_applicable_mrs_rec.title)
AND trunc(tdef.DUE_DATE) >= trunc(l_mr_termination_date)
AND NOT EXISTS (select 'x' from ahl_temp_unit_SR_deferrals where orig_unit_effectivity_id = tdef.unit_effectivity_id);
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);
l_ue_id_tbl.DELETE;
l_ue_status_tbl.DELETE;
l_related_ue_tbl.DELETE;
l_orig_ue_tbl.DELETE;
UPDATE AHL_APPLICABLE_MRS
SET process_status_flag = 'Y',
accomplished_ue_id = l_accomplished_ue_id
WHERE mr_header_id = p_applicable_mrs_rec.mr_header_id
AND csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id;
SELECT DISTINCT mr.mr_effectivity_id, threshold_date,eff.fleet_header_id
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.
INT.calc_duedate_rule_code -- added for ER 7415856
--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
FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b 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 IN (select mr_header_id from ahl_mr_headers_b mr where mr.title = p_title) 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,
INT.CALC_DUEDATE_RULE_CODE -- added for ER 7415856
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,
INT.CALC_DUEDATE_RULE_CODE -- added for ER 7415856
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
--fix for bug# 9263774. Process prior MR versions.
--trunc(sysdate) <= trunc(nvl(mr.effective_to, sysdate+1)) AND
apmr.csi_item_instance_id = p_item_instance_id;
SELECT amr.related_mr_header_id mr_header_id,
mr.version_number,
apmr.csi_item_instance_id,
apmr.Implement_status_code,
apmr.copy_accomplishment_code,
apmr.show_repetitive_code,
apmr.descendent_count,
mr.whichever_first_code,
apmr.repetitive_flag,
mr.title,
mr.effective_from,
mr.effective_to,
apmr.terminate_trigger_check
FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr
WHERE amr.mr_header_id = p_curr_mr_header_id
AND amr.related_mr_header_id = apmr.mr_header_id
AND amr.relationship_code = 'INITIATES'
AND amr.related_mr_header_id = mr.mr_header_id
AND apmr.csi_item_instance_id = p_item_instance_id
-- select only those 'follow after' MRs if all parent MRs are processed.
AND NOT EXISTS (SELECT 'x'
FROM ahl_mr_relationships amr1, ahl_applicable_mrs apmr1
WHERE amr1.related_mr_header_id = amr.related_mr_header_id
AND amr1.mr_header_id = apmr1.mr_header_id
AND amr1.relationship_code NOT IN ('TERMINATES')
AND (nvl(apmr1.process_status_flag,'N') = 'N'
-- initiating MR should either be mandatory OR should be accomplished
OR (apmr1.accomplished_ue_id IS NULL AND apmr1.implement_status_code <> 'MANDATORY'))
AND csi_item_instance_id = p_item_instance_id);
SELECT * FROM (
SELECT due_date, visit_end_date
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_item_instance_id AND
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
where mr2.mr_header_id = p_mr_header_id)
) AND
preceding_check_flag = 'N'
UNION
SELECT due_date, visit_end_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_item_instance_id AND
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
where mr2.mr_header_id = p_mr_header_id )
)
ORDER by due_date ASC
)
WHERE ROWNUM < 2;
select amr.mr_header_id, apmr.accomplished_ue_id, apmr.process_status_flag
from ahl_mr_relationships amr, ahl_applicable_mrs apmr
where amr.mr_header_id = apmr.mr_header_id
and amr.relationship_code = 'INITIATES'
and amr.related_mr_header_id = p_follow_mr_header_id
and apmr.csi_item_instance_id = p_item_instance_id;
UPDATE AHL_APPLICABLE_MRS
SET PRECEDING_MR_HEADER_ID = l_preceding_mr_hdr_id
WHERE csi_item_instance_id = l_appl_rec.csi_item_instance_id
AND MR_header_id = l_appl_rec.MR_header_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 mr_header_id IN (select mr.mr_header_id
from ahl_mr_headers_b mr
where mr.title = p_applicable_mrs_rec.title)
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;
AND mr_header_id IN (select mr.mr_header_id
from ahl_mr_headers_b mr
where mr.title = p_applicable_mrs_rec.title);
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,
visit_end_date,
accomplish_trigger_type,
loop_chain_seq_num,
start_mr_header_id,
start_lc_ue_id,
-- JKJain, NR Analysis and Forecasting
fleet_header_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,
X_unit_effectivity_rec.visit_end_date,
X_unit_effectivity_rec.accomplish_trigger_type,
X_unit_effectivity_rec.loop_chain_seq_num,
X_unit_effectivity_rec.start_mr_header_id,
X_unit_effectivity_rec.start_lc_ue_id,
X_unit_effectivity_rec.fleet_header_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 ue.csi_item_instance_id, ue.mr_header_id, cii.inventory_item_id,
cii.inv_master_organization_id
FROM ahl_unit_effectivities_app_v ue, csi_item_instances cii
WHERE ue.unit_effectivity_id = p_unit_effectivity_id
AND ue.csi_item_instance_id = cii.instance_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 nvl(ccr.net_reading,0) net_reading
FROM csi_counters_b cc, csi_counter_readings ccr
WHERE cc.counter_id = p_counter_id
and ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO;
/*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,
MR.title
FROM ahl_unit_effectivities_b 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 UE.csi_item_instance_id = p_csi_item_instance_id
AND UE.application_usg_code = p_appln_usage_code
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_b ue
WHERE ue.unit_effectivity_id = uer.related_ue_id
AND uer.ue_id = p_ue_id;
SELECT 'x'
FROM ahl_applicable_mrs
WHERE csi_item_instance_id = p_item_instance_id
AND mr_header_id IN (select mr_header_id from ahl_mr_headers_b where title = p_mr_title);
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_b UE
WHERE UE.OBJECT_TYPE = 'SR'
AND UE.application_usg_code = p_appln_usg_code
AND defer_from_ue_id IS NULL
AND csi_item_instance_id = p_csi_item_instance_id
-- added init-due status as part of USAF: Complex Assembly enhancements
AND (status_code IS NULL OR status_code = 'INIT-DUE');
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,
counter_id,
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_due_date,
l_due_counter_value,
l_counter_id,
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_b 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 ue.application_usg_code = p_appln_usage_code
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 appl, ahl_mr_headers_b mr
WHERE appl.mr_header_id = p_mr_header_id AND
appl.csi_item_instance_id = p_csi_item_instance_id AND
appl.mr_header_id = mr.mr_header_id AND
trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1));
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;
SELECT DISTINCT mr.Title,
mr.version_number,
appl.mr_header_id,
appl.Implement_status_code,
appl.copy_accomplishment_code,
appl.repetitive_flag,
appl.show_repetitive_code,
appl.descendent_count,
mr.whichever_first_code,
mr.effective_to,
mr.effective_from,
appl.terminate_trigger_check,
appl.accomplish_trigger_type,
appl.loop_chain_seq_num
FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
WHERE appl.csi_item_instance_id = p_csi_item_instance_id AND
appl.mr_header_id = mr.mr_header_id AND
trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1)) AND
appl.Implement_status_code = 'OPTIONAL_DO_NOT_IMPLEMENT' AND
appl.accomplish_trigger_type = 'CHAIN' AND
appl.mr_header_id = appl.start_mr_header_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 simulation_plan_id
FROM ahl_bue_worker_data
WHERE parent_conc_request_id = p_parent_conc_pgm_id
AND child_conc_request_id IS NULL;
SELECT csi_item_instance_id
FROM ahl_unit_config_headers uc, csi_item_instances ii
WHERE uc.csi_item_instance_id = ii.instance_id
AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(uc.active_end_date,sysdate+1))
AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
AND sysdate < nvl(ii.active_end_date,sysdate+1)
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
AND NOT EXISTS (select 'x'
from csi_ii_relationships iir
where iir.relationship_type_code = 'COMPONENT-OF'
and iir.subject_id = uc.csi_item_instance_id
and trunc(nvl(iir.active_start_date, sysdate)) <= trunc(sysdate)
and trunc(sysdate) < trunc(nvl(iir.active_end_date,sysdate+1))
);
SELECT csi_item_instance_id
FROM ahl_unit_config_headers uc, csi_item_instances ii
WHERE uc.csi_item_instance_id = ii.instance_id
AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(uc.active_end_date,sysdate+1))
AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
AND sysdate < nvl(ii.active_end_date,sysdate+1)
AND unit_config_status_code <> 'DRAFT'
AND parent_uc_header_id IS NULL
AND NOT EXISTS (select 'x'
from csi_ii_relationships iir
where iir.relationship_type_code = 'COMPONENT-OF'
and iir.subject_id = uc.csi_item_instance_id
and trunc(nvl(iir.active_start_date, sysdate)) <= trunc(sysdate)
and trunc(sysdate) < trunc(nvl(iir.active_end_date,sysdate+1))
)
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
)
)
);
SELECT cii.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 cii.active_end_date <= sysdate
AND mre.inventory_item_id = cii.inventory_item_id
AND EXISTS (select 'x' from ahl_unit_effectivities_b UE
where ue.csi_item_instance_id = cii.instance_id
and (ue.status_code IS NULL OR ue.status_code IN ('INIT-DUE','EXCEPTION'))
);
SELECT distinct UC.csi_item_instance_id
from AHL_UNIT_CONFIG_HEADERS UC, AHL_FLEET_UNIT_ASSOCS FU
WHERE FU.simulation_plan_id = c_simulation_plan_id
and UC.UNIT_CONFIG_HEADER_ID = FU.UNIT_CONFIG_HEADER_ID
and nvl(FU.association_end,sysdate) >= sysdate ;
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,
-- JKJain, NR Analysis and Forecasting
simulation_plan_id) 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,
G_SIMULATION_PLAN_ID);
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,
-- JKJain, NR Analysis and Forecasting
simulation_plan_id) 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,
-- JKJain, NR Analysis and Forecasting
G_SIMULATION_PLAN_ID);
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) ;
DELETE /*+ parallel(a) */ FROM ahl_bue_worker_data a
where a.parent_conc_request_id = p_conc_request_id
and exists (select 'x' from ahl_bue_worker_data b
where b.parent_conc_request_id = a.parent_conc_request_id
and b.csi_item_instance_id = a.csi_item_instance_id
and b.rowid > a.rowid);
SELECT /*+ INDEX(BUEWD AHL_BUE_WORKER_DATA_N1) */ BUEWD.rowid, BUEWD.csi_item_instance_id, BUEWD.object_version_number
FROM ahl_bue_worker_data BUEWD
WHERE BUEWD.parent_conc_request_id = p_parent_conc_pgm_id
AND BUEWD.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
AND child_conc_request_id IS NOT NULL;
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);
OPEN fnd_concur_csr(undeleted_parent.parent_conc_request_id);
DELETE FROM AHL_BUE_WORKER_DATA
WHERE parent_conc_request_id = undeleted_parent.parent_conc_request_id
AND child_conc_request_id IS NULL;
FOR undeleted_child IN get_undeleted_child(undeleted_parent.parent_conc_request_id) LOOP
G_DEBUG_LINE_NUM := 320;
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) ;
DELETE /*+ parallel(a) */ FROM ahl_bue_worker_data a
where a.parent_conc_request_id = p_conc_request_id
and exists (select 'x' from ahl_bue_worker_data b
where b.parent_conc_request_id = a.parent_conc_request_id
and b.csi_item_instance_id = a.csi_item_instance_id
and b.rowid > a.rowid);
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 ue_id, related_ue_id
FROM ahl_ue_relationships relns
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;
SELECT 'x'
FROM ahl_ue_relationships
WHERE originator_ue_id = p_ue_id;
SELECT accomplish_trigger_type, loop_chain_seq_num, start_lc_ue_id
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT csi_item_instance_id, (select mr_header_id from ahl_mr_headers_b mr
where mr.title = exp_mr.title
and mr.version_number = exp_mr.version_number) mr_header_id
FROM
(
SELECT ue.csi_item_instance_id,
mr.Title,
max(mr.version_number) version_number
FROM ahl_unit_effectivities_app_v UE, ahl_mr_headers_b mr
-- pick up only top nodes.
WHERE ue.mr_header_id = mr.mr_header_id
AND NOT EXISTS (SELECT 'x'
FROM ahl_ue_relationships uer
WHERE uer.related_ue_id = ue.unit_effectivity_id
AND relationship_code = 'PARENT')
-- not applicable
AND NOT EXISTS (SELECT 'x'
FROM ahl_applicable_mrs aamr, ahl_mr_headers_b mr1
WHERE aamr.csi_item_instance_id = ue.csi_item_instance_id
AND aamr.mr_header_id = mr1.mr_header_id
AND mr1.title = mr.title
)
AND ue.csi_item_instance_id = p_item_instance_id
AND nvl(ue.manually_planned_flag,'N') = 'N'
AND ue.object_type = 'MR'
AND ue.defer_from_ue_id IS NULL
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND mr.effective_to < sysdate
GROUP BY UE.csi_item_instance_id, mr.Title
) exp_mr;
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,
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,
'N',
1
);
SELECT cii.inventory_item_id,
cii.inv_master_organization_id
FROM csi_item_instances cii
WHERE cii.instance_id = p_item_instance_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(ccr.net_reading,0) net_reading
FROM csi_counters_b cc, csi_counter_readings ccr
WHERE cc.counter_id = p_counter_id
and ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO;
AHL_DEBUG_PUB.debug('Table AHL_WARRANTY_CONT_CTR_B will be updated by Expiration Date ' || l_due_date || ' where WARRANTY_CNTRT_COUNTER_ID = '||p_x_warranty_counter_tbl(i).warranty_cntrt_counter_id);
SELECT amr.mr_header_id,
mr.version_number,
apmr.csi_item_instance_id,
apmr.Implement_status_code,
apmr.copy_accomplishment_code,
apmr.show_repetitive_code,
apmr.descendent_count,
mr.whichever_first_code,
apmr.repetitive_flag,
mr.title,
mr.effective_from,
mr.effective_to,
apmr.process_status_flag,
apmr.accomplished_ue_id,
apmr.terminate_trigger_check
FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr
WHERE amr.related_mr_header_id = p_mr_header_id
AND amr.mr_header_id = apmr.mr_header_id
AND amr.relationship_code = 'TERMINATES'
AND amr.mr_header_id = mr.mr_header_id
AND apmr.csi_item_instance_id = p_item_instance_id;
SELECT * FROM (
SELECT due_date, visit_end_date
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_instance_id AND
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
) AND
preceding_check_flag = 'N'
UNION
SELECT due_date, visit_end_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_instance_id AND
mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
)
ORDER by due_date
)
WHERE ROWNUM < 2;
UPDATE AHL_APPLICABLE_MRS
set TERMINATION_DATE = l_termination_date,
TERMINATING_MR_HEADER_ID = l_terminating_mr_header_id
where mr_header_id = p_applicable_mrs_rec.mr_header_id
and csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id;
SELECT apmr.mr_header_id, apmr.loop_chain_seq_num, mr.title
FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr
WHERE apmr.mr_header_id = mr.mr_header_id
AND apmr.csi_item_instance_id = p_csi_item_instance_id
AND apmr.start_mr_header_id = p_start_mr_header_id
ORDER BY loop_chain_seq_num ASC;
SELECT * FROM
(SELECT
decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date)) accomplishment_date,
unit_effectivity_id
FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,
(select title from ahl_mr_headers_b
where mr_header_id IN (select mr_header_id from ahl_applicable_MRs
where csi_item_instance_id = p_csi_instance_id
and start_mr_header_id = p_mr_header_id)
) mr2
WHERE def.csi_item_instance_id = p_csi_instance_id
AND def.mr_header_id = mr1.mr_header_id
AND mr1.title = mr2.title
AND def.object_type = 'MR'
AND def.deferral_effective_on IS NOT NULL
ORDER BY def.deferral_effective_on DESC
) WHERE ROWNUM < 2;
SELECT loop_chain_seq_num, start_lc_ue_id
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT apmr.mr_header_id, apmr.loop_chain_seq_num, mr.title
FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr
WHERE apmr.mr_header_id = mr.mr_header_id
AND apmr.csi_item_instance_id = p_csi_item_instance_id
AND apmr.start_mr_header_id = p_start_mr_header_id
ORDER BY loop_chain_seq_num ASC;
SELECT ue.start_lc_ue_id,
min(loop_chain_seq_num)
FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
WHERE ue1.unit_effectivity_id = ue2.start_lc_ue_id
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND ue.status_code IS NOT NULL AND ue.status_code <> 'INIT-DUE'
AND EXISTS (select 'x'
from ahl_mr_headers_b mr_title
(select mr_header_id from ahl_unit_effectivities_b ue1
where unit_effectivity_id = ue.start_lc_ue_id) start_mr
where
and appl_mr.start_mr_header_id = p_mr_header_id
and appl_mr.mr_header_id = mr1.mr_header_id)
AND ue.object_type = 'MR'
AND ue.defer_from_ue_id IS NULL
AND ue.accomplish_trigger_type = 'CHAIN'
AND (ue.status_code IS NULL)
-- parent is accomplished
AND NOT EXISTS (select 'x' from ahl_unit_effectivities_b
where unit_effectivity_id = ue.start_lc_ue_id
AND (status_code is NULL or status_code = 'INIT-DUE')
)
GROUP BY ue.start_lc_ue_id;
SELECT ue.start_lc_ue_id, max(ue.loop_chain_seq_num)
from ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
where ue.csi_item_instance_id = p_csi_item_instance_id
and ue.mr_header_id = mr.mr_header_id
and ue.status_code IN ('ACCOMPLISHED','TERMINATED','INIT-ACCOMPLISHED')
and mr.title in (select title from ahl_applicable_mrs appl, ahl_mr_headers_b mr1
where appl.start_mr_header_id = p_start_mr_header_id
and appl.csi_item_instance_id = p_csi_item_instance_id
and appl.mr_header_id = mr1.mr_header_id
)
and exists (select 'x' from ahl_unit_effectivities_b ue1
where ue1.csi_item_instance_id = p_csi_item_instance_id
and ue1.start_lc_ue_id = ue.start_lc_ue_id
and (ue.STATUS_CODE IS NULL or ue.STATUS_CODE = 'INIT-DUE')
)
group by ue.start_lc_ue_id;
SELECT ue.unit_effectivity_id,
ue.due_date,
ue.mr_header_id,
to_date(null) visit_end_date
FROM ahl_unit_effectivities_b ue
WHERE ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_start_mr_title)
AND ue.csi_item_instance_id = p_csi_item_instance_id
AND ue.status_code IS NULL
AND ue.manually_planned_flag = 'Y'
AND ue.defer_from_ue_id IS NULL
-- Do not pick MRs associated to a SR.
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');
SELECT unit_effectivity_id,
due_date,
mr_header_id,
visit_end_date
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_csi_item_instance_id
AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_start_mr_title);
SELECT ue.unit_effectivity_id, AHL_UMP_UTIL_PKG.get_Visit_Status (ue.unit_effectivity_id) l_visit_status,
ue.defer_from_ue_id, ue.mr_header_id, mr.title
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
where ue.mr_header_id = mr.mr_header_id
and ue.start_lc_ue_id = p_start_lc_ue_id
and ue.loop_chain_seq_num = p_chain_seq_num
and ue.accomplish_trigger_type = 'CHAIN';
SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.loop_chain_seq_num, ue.mr_header_id,
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.start_lc_ue_id = p_start_lc_ue_id
AND ue.loop_chain_seq_num = p_chain_seq_num
ORDER BY decode (ue.status_code, 'TERMINATED', ter.deferral_effective_on, ue.accomplished_date) DESC;
SELECT * FROM
( SELECT
decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date)) due_date
FROM ahl_temp_unit_SR_deferrals def
WHERE def.unit_effectivity_id = p_chain_ue_id
AND def.object_type = 'MR'
AND EXISTS (select 'x' from ahl_applicable_mrs
where csi_item_instance_id = def.csi_item_instance_id
and mr_header_id = def.mr_header_id
and accomplish_trigger_type = 'CHAIN')
AND def.deferral_effective_on IS NOT NULL
)
WHERE ROWNUM < 2;
SELECT vst.close_date_time
from ahl_visits_b vst, ahl_visit_tasks_b tsk
where tsk.unit_effectivity_id = p_ue_id
and tsk.visit_id = vst.visit_id
and tsk.task_type_code = 'SUMMARY';
UPDATE ahl_unit_effectivities_b
SET accomplish_trigger_type = p_applicable_mrs_rec.accomplish_trigger_type,
start_lc_ue_id = l_start_ue_id_tbl(i),
loop_chain_seq_num = p_applicable_mrs_rec.loop_chain_seq_num
WHERE unit_effectivity_id = l_start_ue_id_tbl(i);
l_start_ue_id_tbl.delete;
l_start_due_date_tbl.delete;
l_start_mr_id_tbl.delete;
l_start_vst_end_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.descendent_count,
mr.whichever_first_code,
mr.effective_to,
mr.effective_from,
appl.loop_chain_seq_num
FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr
WHERE appl.csi_item_instance_id = p_csi_item_instance_id
AND appl.mr_header_id = p_mr_header_id
AND (appl.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
AND appl.mr_header_id = mr.mr_header_id
AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate);
SELECT unit_effectivity_id, mr.title
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE ue.mr_header_id = mr.mr_header_id
AND ue.start_lc_ue_id = p_start_lc_ue_id
AND ue.loop_chain_seq_num = p_loop_chain_seq_num
AND (ue.STATUS_CODE IS NULL or ue.STATUS_CODE = 'INIT-DUE');
SELECT
UA.COUNTER_ID,
UA.COUNTER_VALUE,
CS.UOM_CODE,
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 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 simulation_plan_id
FROM AHL_SIMULATION_PLANS_B
WHERE primary_plan_flag = 'Y'
AND status_code = 'ACTIVE'
-- AND nvl(simulation_type,'UMP') = 'UMP'; Removed NVL condition as primary plan is seeded with UMP.
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 = nvl(c_plan_id, get_primary_plan_id);
UPDATE ahl_unit_effectivities_b
SET due_date = l_due_date,
due_counter_value = l_due_counter_value,
counter_id = l_counter_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE unit_effectivity_id = p_unit_effectivity_id;
UPDATE ahl_unit_effectivities_b
SET due_date = l_due_date,
due_counter_value = l_due_counter_value,
counter_id = l_counter_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
where originator_ue_id = p_unit_effectivity_id);
SELECT ue.csi_item_instance_id, ue.mr_header_id, cii.inventory_item_id,
cii.inv_master_organization_id
FROM ahl_unit_effectivities_app_v ue, csi_item_instances cii
WHERE ue.unit_effectivity_id = p_unit_effectivity_id
AND ue.csi_item_instance_id = cii.instance_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(ccr.net_reading,0) net_reading
FROM csi_counters_b cc, csi_counter_readings ccr
WHERE cc.counter_id = p_counter_id
and ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO;
SELECT aut.counter_id, aut.counter_value, (select uom_code from csi_counters_b where counter_id = aut.counter_id) uom_code
FROM ahl_unit_thresholds aut, ahl_unit_deferrals_b udf
WHERE udf.unit_effectivity_id = p_ue_id
AND udf.unit_deferral_id = aut.unit_deferral_id
AND udf.unit_deferral_type = 'INIT-DUE';
SELECT cs.expected_resolution_date
INTO l_expected_resolution_date
FROM ahl_unit_effectivities_b ue, cs_incidents_all_b cs
WHERE ue.unit_effectivity_id = p_unit_effectivity_id
AND cs.incident_id = ue.cs_incident_id;