[Home] [Help]
410: -- to check whether MR or any of its children has resettable counters
411: CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
412: --SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES_APP_V MRE, AHL_UNIT_EFFECTIVITIES_APP_V UE
413: /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
414: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
415: WHERE CP.customer_product_id = UE.csi_item_instance_id
416: AND CP.counter_name = MRI.counter_name
417: AND MRI.reset_value IS NOT NULL
418: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
419: AND MRE.mr_header_id = UE.mr_header_id
420: AND UE.unit_effectivity_id = p_unit_effectivity_id
421: UNION
422: --SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES_APP_V MRE, AHL_UNIT_EFFECTIVITIES_APP_V UE
423: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
424: WHERE CP.customer_product_id = UE.csi_item_instance_id
425: AND CP.counter_name = MRI.counter_name
426: AND MRI.reset_value IS NOT NULL
427: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
438: );
439: */
440: SELECT 'x'
441: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
442: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
443: WHERE CA.source_object_id = UE.csi_item_instance_id
444: AND ca.source_object_code = 'CP'
445: AND CP.counter_template_name = MRI.counter_name
446: AND MRI.reset_value IS NOT NULL
449: AND UE.unit_effectivity_id = p_unit_effectivity_id
450: UNION
451: SELECT 'x'
452: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
453: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
454: WHERE CA.source_object_id = UE.csi_item_instance_id
455: AND ca.source_object_code = 'CP'
456: AND CP.counter_template_name = MRI.counter_name
457: AND MRI.reset_value IS NOT NULL
866: AND unit_effectivity_id = p_unit_effectivity_id;
867:
868: -- to check whether ue is in production
869: CURSOR valid_deferral_csr(p_unit_effectivity_id IN NUMBER) IS
870: SELECT 'x' from ahl_workorder_tasks_v WO,AHL_UNIT_EFFECTIVITIES_B UE --Undid App usage related BLIND changes
871: WHERE WO.unit_effectivity_id = UE.unit_effectivity_id
872: AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
873: AND UE.unit_effectivity_id = p_unit_effectivity_id;
874:
873: AND UE.unit_effectivity_id = p_unit_effectivity_id;
874:
875: -- check whether MR is not terminated
876: /*CURSOR valid_mr_csr(p_unit_effectivity_id IN NUMBER) IS
877: select 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def, ahl_unit_effectivities_b UE
878: where UE.unit_effectivity_id = p_unit_effectivity_id
879: AND def.mr_header_id = UE.mr_header_id
880: and def.title = mr.title
881: and trunc(sysdate) between trunc(mr.effective_from)
894: -- Contrast this with the earlier cursor valid_deferral_csr, which handles SRs with no MRs and plain MRs
895: CURSOR valid_sr_deferral_csr (p_ue_id in number)
896: IS
897: SELECT 'x'
898: FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
899: WHERE WO.master_workorder_flag = 'Y'
900: /* to filter out draft / deleted WOs */
901: AND WO.STATUS_CODE NOT IN ( '17' , '22' )
902: /* to check whether visit available in client's organization */
917:
918: -- R12: UMP Deferral
919: CURSOR valid_ue_csr (p_unit_effectivity_id IN NUMBER) IS
920: SELECT 'x'
921: FROM AHL_Unit_Effectivities_B UE
922: WHERE status_code IS NULL OR status_code = 'INIT-DUE'
923: AND unit_effectivity_id = p_unit_effectivity_id
924: AND NOT EXISTS (SELECT 'x'
925: FROM ahl_visit_tasks_b vts
945: -- SR Cancellation for nonserialized items.
946: CURSOR is_orig_ue_nonserial(p_ue_id in number)
947: IS
948: SELECT cii.serial_number
949: FROM ahl_unit_effectivities_b ue, csi_item_instances cii
950: WHERE unit_effectivity_id in (select originator_ue_id
951: from ahl_ue_relationships
952: where related_ue_id = p_ue_id)
953: AND ue.csi_item_instance_id = cii.instance_id
1823: p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type)IS
1824:
1825: CURSOR counter_id_csr(p_counter_name IN VARCHAR2,p_unit_effectivity_id IN NUMBER) IS
1826: --SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE
1827: SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE -- Undid App usage BLIND changes
1828: WHERE UPPER(co.counter_name) like UPPER(p_counter_name)
1829: AND co.customer_product_id = ue.csi_item_instance_id
1830: AND UE.unit_effectivity_id = p_unit_effectivity_id;
1831:
2141:
2142:
2143: CURSOR valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2144: --SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2145: SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2146: WHERE co.customer_product_id = ue.csi_item_instance_id
2147: AND co.counter_id = p_counter_id
2148: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2149: AND UD.unit_deferral_id = p_unit_deferral_id;
2148: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2149: AND UD.unit_deferral_id = p_unit_deferral_id;
2150:
2151: /*CURSOR mr_valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2152: SELECT 'x' from ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UD,AHL_MR_INTERVALS_V MR,CSI_CP_COUNTERS_V CO
2153: WHERE UD.unit_deferral_id = p_unit_deferral_id
2154: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2155: AND co.customer_product_id = ue.csi_item_instance_id
2156: AND UE.mr_effectivity_id = MR.mr_effectivity_id
2632:
2633: CURSOR curr_counter_val_csr(p_unit_deferral_id IN NUMBER, p_counter_id IN NUMBER,p_deferral_effective_on IN DATE) IS
2634: SELECT NVL(net_reading, 0) FROM cs_ctr_counter_values_v ctrread, cs_counter_groups_v ctrgrp,
2635: -- AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2636: AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2637: WHERE ctrread.VALUE_TIMESTAMP <= p_deferral_effective_on
2638: AND ctrread.counter_group_id = ctrgrp.counter_group_id
2639: AND SOURCE_OBJECT_CODE = 'CP'
2640: AND SOURCE_OBJECT_ID = UE.csi_item_instance_id
2647:
2648: CURSOR whichever_first_code_csr (p_unit_effectivity_id IN NUMBER) IS
2649: SELECT whichever_first_code
2650: --FROM AHL_MR_HEADERS_APP_V MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
2651: FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B UE -- Undid blind changes for app_usage code
2652: WHERE MR.mr_header_id = UE.mr_header_id
2653: AND UE.unit_effectivity_id = p_unit_effectivity_id;
2654:
2655: l_whichever_first_code ahl_mr_headers_b.whichever_first_code%TYPE;
2656:
2657:
2658: CURSOR next_due_date_csr(p_unit_effectivity_id IN NUMBER) IS
2659: SELECT UE.due_date
2660: FROM AHL_UNIT_EFFECTIVITIES_B UE
2661: WHERE UE.mr_header_id = (
2662: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2663: AND UE.csi_item_instance_id = (
2664: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2658: CURSOR next_due_date_csr(p_unit_effectivity_id IN NUMBER) IS
2659: SELECT UE.due_date
2660: FROM AHL_UNIT_EFFECTIVITIES_B UE
2661: WHERE UE.mr_header_id = (
2662: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2663: AND UE.csi_item_instance_id = (
2664: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2665: AND UE.unit_effectivity_id <> p_unit_effectivity_id
2666: AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2660: FROM AHL_UNIT_EFFECTIVITIES_B UE
2661: WHERE UE.mr_header_id = (
2662: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2663: AND UE.csi_item_instance_id = (
2664: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2665: AND UE.unit_effectivity_id <> p_unit_effectivity_id
2666: AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2667: ORDER BY DUE_DATE ASC;
2668:
3231:
3232: CURSOR csi_item_instance_id_csr(p_unit_deferral_id IN NUMBER)
3233: --,p_object_version_number IN NUMBER)
3234: IS
3235: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B UE, ahl_unit_deferrals_b UD
3236: WHERE UE.unit_effectivity_id = UD.unit_effectivity_id
3237: --AND UD.object_version_number = p_object_version_number
3238: AND UD.unit_deferral_id = p_unit_deferral_id;
3239:
3241:
3242: -- to check whether MR is not terminated already
3243: /*CURSOR valid_mr_csr(p_unit_deferral_id IN NUMBER,p_object_version_number IN NUMBER) IS
3244: SELECT 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def,
3245: ahl_unit_effectivities_b UE,ahl_unit_deferrals_b UD
3246: WHERE UD.unit_deferral_id = p_unit_deferral_id
3247: AND UD.object_version_number = p_object_version_number
3248: AND UE.unit_effectivity_id = UD.unit_effectivity_id
3249: AND def.mr_header_id = NVL(UE.mr_header_id,def.mr_header_id)
3681: -- Added object_type to query
3682: CURSOR unit_effectivity_id_csr(p_unit_deferral_id IN NUMBER,
3683: p_object_version_number IN NUMBER)IS
3684: SELECT UD.unit_effectivity_id, ue.object_type
3685: from ahl_unit_deferrals_b UD, AHL_UNIT_EFFECTIVITIES_B UE
3686: WHERE NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
3687: AND UE.unit_effectivity_id = UD.unit_effectivity_id
3688: AND UD.object_version_number = p_object_version_number
3689: AND UD.unit_deferral_id = p_unit_deferral_id;
3730: WO.status_code,
3731: WO.actual_start_date,
3732: WO.actual_end_date,
3733: WO.workorder_name
3734: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3735: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3736: AND WO.master_workorder_flag = 'N'
3737: AND WO.visit_task_id = VST.visit_task_id
3738: AND VST.unit_effectivity_id = UE.unit_effectivity_id
3743: WO.status_code,
3744: WO.actual_start_date,
3745: WO.actual_end_date,
3746: WO.workorder_name
3747: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3748: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3749: AND WO.master_workorder_flag = 'N'
3750: AND WO.visit_task_id = VST.visit_task_id
3751: AND VST.unit_effectivity_id = UE.unit_effectivity_id
4356: sr.incident_id,
4357: sr.incident_number,
4358: sr.object_version_number
4359: FROM
4360: ahl_unit_effectivities_b ue,
4361: cs_incidents_all_b sr
4362: WHERE
4363: ue.cs_incident_id = sr.incident_id (+) and
4364: ue.unit_effectivity_id = p_ue_id;
4793: PROCEDURE process_prior_ump_deferrals(
4794: p_unit_effectivity_id IN NUMBER) IS
4795:
4796: CURSOR prior_child_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
4797: SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
4798: WHERE defer_from_ue_id IS NOT NULL
4799: --AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
4800: AND defer_from_ue_id IN (
4801: SELECT related_ue_id
4845: START WITH ue_id = p_unit_effectivity_id
4846: CONNECT BY ue_id = PRIOR related_ue_id
4847: )
4848: AND NOT EXISTS (
4849: SELECT 'x' FROM ahl_unit_effectivities_b
4850: WHERE defer_from_ue_id = UD.unit_effectivity_id
4851: );
4852:
4853: CURSOR redundant_threshold_csr(p_unit_deferral_id IN NUMBER) IS
5022:
5023: -- to check whether MR or any of its children has resettable counters
5024: CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
5025: /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
5026: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5027: WHERE CP.customer_product_id = UE.csi_item_instance_id
5028: AND CP.counter_name = MRI.counter_name
5029: AND MRI.reset_value IS NOT NULL
5030: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5030: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5031: AND MRE.mr_header_id = UE.mr_header_id
5032: AND UE.unit_effectivity_id = p_unit_effectivity_id
5033: UNION
5034: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5035: WHERE CP.customer_product_id = UE.csi_item_instance_id
5036: AND CP.counter_name = MRI.counter_name
5037: AND MRI.reset_value IS NOT NULL
5038: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5050: */
5051:
5052: SELECT 'x'
5053: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5054: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5055: WHERE CA.source_object_id = UE.csi_item_instance_id
5056: AND ca.source_object_code = 'CP'
5057: AND CP.counter_template_name = MRI.counter_name
5058: AND MRI.reset_value IS NOT NULL
5061: AND UE.unit_effectivity_id = p_unit_effectivity_id
5062: UNION
5063: SELECT 'x'
5064: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5065: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5066: WHERE CA.source_object_id = UE.csi_item_instance_id
5067: AND ca.source_object_code = 'CP'
5068: AND CP.counter_template_name = MRI.counter_name
5069: AND MRI.reset_value IS NOT NULL