[Home] [Help]
417: -- to check whether MR or any of its children has resettable counters
418: CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
419: --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
420: /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
421: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
422: WHERE CP.customer_product_id = UE.csi_item_instance_id
423: AND CP.counter_name = MRI.counter_name
424: AND MRI.reset_value IS NOT NULL
425: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
426: AND MRE.mr_header_id = UE.mr_header_id
427: AND UE.unit_effectivity_id = p_unit_effectivity_id
428: UNION
429: --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
430: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
431: WHERE CP.customer_product_id = UE.csi_item_instance_id
432: AND CP.counter_name = MRI.counter_name
433: AND MRI.reset_value IS NOT NULL
434: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
445: );
446: */
447: SELECT 'x'
448: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
449: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
450: WHERE CA.source_object_id = UE.csi_item_instance_id
451: AND ca.source_object_code = 'CP'
452: AND CP.counter_template_name = MRI.counter_name
453: AND MRI.reset_value IS NOT NULL
456: AND UE.unit_effectivity_id = p_unit_effectivity_id
457: UNION
458: SELECT 'x'
459: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
460: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
461: WHERE CA.source_object_id = UE.csi_item_instance_id
462: AND ca.source_object_code = 'CP'
463: AND CP.counter_template_name = MRI.counter_name
464: AND MRI.reset_value IS NOT NULL
892: AND unit_effectivity_id = p_unit_effectivity_id;
893:
894: -- to check whether ue is in production
895: CURSOR valid_deferral_csr(p_unit_effectivity_id IN NUMBER) IS
896: SELECT 'x' from ahl_workorder_tasks_v WO,AHL_UNIT_EFFECTIVITIES_B UE --Undid App usage related BLIND changes
897: WHERE WO.unit_effectivity_id = UE.unit_effectivity_id
898: AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
899: AND UE.unit_effectivity_id = p_unit_effectivity_id;
900:
899: AND UE.unit_effectivity_id = p_unit_effectivity_id;
900:
901: -- check whether MR is not terminated
902: /*CURSOR valid_mr_csr(p_unit_effectivity_id IN NUMBER) IS
903: select 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def, ahl_unit_effectivities_b UE
904: where UE.unit_effectivity_id = p_unit_effectivity_id
905: AND def.mr_header_id = UE.mr_header_id
906: and def.title = mr.title
907: and trunc(sysdate) between trunc(mr.effective_from)
920: -- Contrast this with the earlier cursor valid_deferral_csr, which handles SRs with no MRs and plain MRs
921: CURSOR valid_sr_deferral_csr (p_ue_id in number)
922: IS
923: SELECT 'x'
924: FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
925: WHERE WO.master_workorder_flag = 'Y'
926: /* to filter out draft / deleted WOs */
927: AND WO.STATUS_CODE NOT IN ( '17' , '22' )
928: /* to check whether visit available in client's organization */
943:
944: -- R12: UMP Deferral
945: CURSOR valid_ue_csr (p_unit_effectivity_id IN NUMBER) IS
946: SELECT 'x'
947: FROM AHL_Unit_Effectivities_B UE
948: WHERE (status_code IS NULL OR status_code = 'INIT-DUE')
949: AND unit_effectivity_id = p_unit_effectivity_id
950: AND NOT EXISTS (SELECT 'x'
951: FROM ahl_visit_tasks_b vts
972: -- SR Cancellation for nonserialized items.
973: CURSOR is_orig_ue_nonserial(p_ue_id in number)
974: IS
975: SELECT cii.serial_number
976: FROM ahl_unit_effectivities_b ue, csi_item_instances cii
977: WHERE unit_effectivity_id in (select originator_ue_id
978: from ahl_ue_relationships
979: where related_ue_id = p_ue_id)
980: AND ue.csi_item_instance_id = cii.instance_id
984: -- SR Cancellation for nonserialized items.
985: CURSOR is_sr_ue_nonserial(p_ue_id in number)
986: IS
987: SELECT cii.serial_number
988: FROM ahl_unit_effectivities_b ue, csi_item_instances cii
989: WHERE unit_effectivity_id = p_ue_id
990: AND ue.csi_item_instance_id = cii.instance_id
991: AND cii.quantity > 0
992: AND ue.object_type = 'SR';
1886: p_x_df_schedules_tbl IN OUT NOCOPY AHL_PRD_DF_PVT.df_schedules_tbl_type)IS
1887:
1888: CURSOR counter_id_csr(p_counter_name IN VARCHAR2,p_unit_effectivity_id IN NUMBER) IS
1889: --SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE
1890: SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE -- Undid App usage BLIND changes
1891: WHERE UPPER(co.counter_name) like UPPER(p_counter_name)
1892: AND co.customer_product_id = ue.csi_item_instance_id
1893: AND UE.unit_effectivity_id = p_unit_effectivity_id;
1894:
2204:
2205:
2206: CURSOR valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2207: --SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2208: SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2209: WHERE co.customer_product_id = ue.csi_item_instance_id
2210: AND co.counter_id = p_counter_id
2211: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2212: AND UD.unit_deferral_id = p_unit_deferral_id;
2211: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2212: AND UD.unit_deferral_id = p_unit_deferral_id;
2213:
2214: /*CURSOR mr_valid_counter_csr(p_unit_deferral_id IN NUMBER,p_counter_id IN NUMBER) IS
2215: SELECT 'x' from ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UD,AHL_MR_INTERVALS_V MR,CSI_CP_COUNTERS_V CO
2216: WHERE UD.unit_deferral_id = p_unit_deferral_id
2217: AND UE.unit_effectivity_id = UD.unit_effectivity_id
2218: AND co.customer_product_id = ue.csi_item_instance_id
2219: AND UE.mr_effectivity_id = MR.mr_effectivity_id
2696: CURSOR curr_counter_val_csr(p_unit_deferral_id IN NUMBER, p_counter_id IN NUMBER,p_deferral_effective_on IN DATE) IS
2697: /* Modified to fix bug# 8328818: ignore disabled counter readings.
2698: SELECT NVL(net_reading, 0) FROM cs_ctr_counter_values_v ctrread, cs_counter_groups_v ctrgrp,
2699: -- AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
2700: AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
2701: WHERE ctrread.VALUE_TIMESTAMP <= p_deferral_effective_on
2702: AND ctrread.counter_group_id = ctrgrp.counter_group_id
2703: AND SOURCE_OBJECT_CODE = 'CP'
2704: AND SOURCE_OBJECT_ID = UE.csi_item_instance_id
2708: ORDER BY ctrread.counter_id asc, ctrread.VALUE_TIMESTAMP desc;
2709: */
2710:
2711: -- Fix for bug# 8328818: ignore disabled counter readings.
2712: -- rewrote above query as we do not need AHL_UNIT_EFFECTIVITIES_B,
2713: -- cs_counter_groups_v, AHL_UNIT_DEFERRALS_B tables.
2714: SELECT * FROM (
2715: SELECT CCR.NET_READING
2716: FROM CSI_COUNTER_READINGS CCR
2725:
2726: CURSOR whichever_first_code_csr (p_unit_effectivity_id IN NUMBER) IS
2727: SELECT whichever_first_code
2728: --FROM AHL_MR_HEADERS_APP_V MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
2729: FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B UE -- Undid blind changes for app_usage code
2730: WHERE MR.mr_header_id = UE.mr_header_id
2731: AND UE.unit_effectivity_id = p_unit_effectivity_id;
2732:
2733: l_whichever_first_code ahl_mr_headers_b.whichever_first_code%TYPE;
2734:
2735:
2736: CURSOR next_due_date_csr(p_unit_effectivity_id IN NUMBER) IS
2737: SELECT UE.due_date
2738: FROM AHL_UNIT_EFFECTIVITIES_B UE
2739: WHERE UE.mr_header_id = (
2740: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2741: AND UE.csi_item_instance_id = (
2742: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2736: CURSOR next_due_date_csr(p_unit_effectivity_id IN NUMBER) IS
2737: SELECT UE.due_date
2738: FROM AHL_UNIT_EFFECTIVITIES_B UE
2739: WHERE UE.mr_header_id = (
2740: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2741: AND UE.csi_item_instance_id = (
2742: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2743: AND UE.unit_effectivity_id <> p_unit_effectivity_id
2744: AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2738: FROM AHL_UNIT_EFFECTIVITIES_B UE
2739: WHERE UE.mr_header_id = (
2740: SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2741: AND UE.csi_item_instance_id = (
2742: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
2743: AND UE.unit_effectivity_id <> p_unit_effectivity_id
2744: AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2745: ORDER BY DUE_DATE ASC;
2746:
2748:
2749: -- JKJain, NR Analysis and Forecasting
2750: CURSOR get_csi_item_instance_id(c_unit_effectivity_id IN NUMBER) IS
2751: SELECT CSI_ITEM_INSTANCE_ID
2752: FROM AHL_UNIT_EFFECTIVITIES_B
2753: WHERE unit_effectivity_id = c_unit_effectivity_id;
2754: l_fleet_header_id NUMBER;
2755: l_csi_item_inst_id NUMBER;
2756: l_uc_header_id NUMBER;
3133: );
3134: END IF;
3135:
3136: l_mr_expired := FND_API.G_FALSE;
3137: SELECT mr_header_id INTO l_mr_header_id FROM ahl_unit_effectivities_b
3138: WHERE unit_effectivity_id = p_df_header_rec.unit_effectivity_id;
3139: IF(l_mr_header_id IS NOT NULL)THEN
3140: -- find whether deferral type is MR or SR
3141: -- if MR find whether active else pluggin new mr
3368:
3369: CURSOR csi_item_instance_id_csr(p_unit_deferral_id IN NUMBER)
3370: --,p_object_version_number IN NUMBER)
3371: IS
3372: SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B UE, ahl_unit_deferrals_b UD
3373: WHERE UE.unit_effectivity_id = UD.unit_effectivity_id
3374: --AND UD.object_version_number = p_object_version_number
3375: AND UD.unit_deferral_id = p_unit_deferral_id;
3376:
3378:
3379: -- to check whether MR is not terminated already
3380: /*CURSOR valid_mr_csr(p_unit_deferral_id IN NUMBER,p_object_version_number IN NUMBER) IS
3381: SELECT 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def,
3382: ahl_unit_effectivities_b UE,ahl_unit_deferrals_b UD
3383: WHERE UD.unit_deferral_id = p_unit_deferral_id
3384: AND UD.object_version_number = p_object_version_number
3385: AND UE.unit_effectivity_id = UD.unit_effectivity_id
3386: AND def.mr_header_id = NVL(UE.mr_header_id,def.mr_header_id)
3837: -- Added object_type to query
3838: CURSOR unit_effectivity_id_csr(p_unit_deferral_id IN NUMBER,
3839: p_object_version_number IN NUMBER)IS
3840: SELECT UD.unit_effectivity_id, ue.object_type
3841: from ahl_unit_deferrals_b UD, AHL_UNIT_EFFECTIVITIES_B UE
3842: WHERE NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
3843: AND UE.unit_effectivity_id = UD.unit_effectivity_id
3844: AND UD.object_version_number = p_object_version_number
3845: AND UD.unit_deferral_id = p_unit_deferral_id;
3886: WO.status_code,
3887: WO.actual_start_date,
3888: WO.actual_end_date,
3889: WO.workorder_name
3890: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3891: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3892: AND WO.master_workorder_flag = 'N'
3893: AND WO.visit_task_id = VST.visit_task_id
3894: AND VST.unit_effectivity_id = UE.unit_effectivity_id
3899: WO.status_code,
3900: WO.actual_start_date,
3901: WO.actual_end_date,
3902: WO.workorder_name
3903: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3904: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3905: AND WO.master_workorder_flag = 'N'
3906: AND WO.visit_task_id = VST.visit_task_id
3907: AND VST.unit_effectivity_id = UE.unit_effectivity_id
4427:
4428: -- to fetch unit effectivity id
4429: CURSOR unit_effectivity_id_csr(p_unit_deferral_id IN NUMBER,p_object_version_number IN NUMBER) IS
4430: SELECT UD.unit_effectivity_id,UE.mr_header_id,UE.manually_planned_flag,UE.defer_from_ue_id
4431: from ahl_unit_deferrals_b UD,ahl_unit_effectivities_b UE
4432: WHERE UE.unit_effectivity_id = UD.unit_effectivity_id
4433: AND UD.object_version_number = p_object_version_number
4434: AND UD.unit_deferral_id = p_unit_deferral_id;
4435:
4526: sr.incident_id,
4527: sr.incident_number,
4528: sr.object_version_number
4529: FROM
4530: ahl_unit_effectivities_b ue,
4531: cs_incidents_all_b sr
4532: WHERE
4533: ue.cs_incident_id = sr.incident_id (+) and
4534: ue.unit_effectivity_id = p_ue_id;
4570: -- SR Cancellation for nonserialized items.
4571: CURSOR is_sr_ue_nonserial(p_ue_id in number)
4572: IS
4573: SELECT cii.serial_number
4574: FROM ahl_unit_effectivities_b ue, csi_item_instances cii
4575: WHERE unit_effectivity_id = p_ue_id
4576: AND ue.csi_item_instance_id = cii.instance_id
4577: AND cii.quantity > 0
4578: AND ue.object_type = 'SR';
4919: X_LAST_UPDATED_BY => fnd_global.user_id,
4920: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
4921:
4922: -- copy originating WO id seperately as table handler does not support it.
4923: UPDATE ahl_unit_effectivities_b
4924: SET originating_wo_id = ue_rec.originating_wo_id,
4925: -- added for SB Enh
4926: accomplish_trigger_type = ue_rec.accomplish_trigger_type,
4927: start_lc_ue_id = ue_rec.start_lc_ue_id,
5171: PROCEDURE process_prior_ump_deferrals(
5172: p_unit_effectivity_id IN NUMBER) IS
5173:
5174: CURSOR prior_child_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
5175: SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
5176: WHERE defer_from_ue_id IS NOT NULL
5177: --AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
5178: AND defer_from_ue_id IN (
5179: SELECT related_ue_id
5223: START WITH ue_id = p_unit_effectivity_id
5224: CONNECT BY ue_id = PRIOR related_ue_id
5225: )
5226: AND NOT EXISTS (
5227: SELECT 'x' FROM ahl_unit_effectivities_b
5228: WHERE defer_from_ue_id = UD.unit_effectivity_id
5229: );
5230:
5231: CURSOR redundant_threshold_csr(p_unit_deferral_id IN NUMBER) IS
5405:
5406: -- to check whether MR or any of its children has resettable counters
5407: CURSOR reset_counter_csr(p_unit_effectivity_id IN NUMBER) IS
5408: /* In R12, modified to use csi_counters_vl instead of csi_cp_counters_v.
5409: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5410: WHERE CP.customer_product_id = UE.csi_item_instance_id
5411: AND CP.counter_name = MRI.counter_name
5412: AND MRI.reset_value IS NOT NULL
5413: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5413: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5414: AND MRE.mr_header_id = UE.mr_header_id
5415: AND UE.unit_effectivity_id = p_unit_effectivity_id
5416: UNION
5417: SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5418: WHERE CP.customer_product_id = UE.csi_item_instance_id
5419: AND CP.counter_name = MRI.counter_name
5420: AND MRI.reset_value IS NOT NULL
5421: AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
5433: */
5434:
5435: SELECT 'x'
5436: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5437: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5438: WHERE CA.source_object_id = UE.csi_item_instance_id
5439: AND ca.source_object_code = 'CP'
5440: AND CP.counter_template_name = MRI.counter_name
5441: AND MRI.reset_value IS NOT NULL
5444: AND UE.unit_effectivity_id = p_unit_effectivity_id
5445: UNION
5446: SELECT 'x'
5447: from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
5448: AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
5449: WHERE CA.source_object_id = UE.csi_item_instance_id
5450: AND ca.source_object_code = 'CP'
5451: AND CP.counter_template_name = MRI.counter_name
5452: AND MRI.reset_value IS NOT NULL
5503: l_new_mr_header_id NUMBER;
5504: l_new_mr_applicable VARCHAR2(1);
5505:
5506: CURSOR is_ue_inst_serial_ctld(p_unit_effectivity_id IN NUMBER)IS
5507: SELECT 'X' FROM csi_item_instances csi, ahl_unit_effectivities_b ue
5508: WHERE ue.UNIT_EFFECTIVITY_ID = p_unit_effectivity_id
5509: AND csi.instance_id = ue.CSI_ITEM_INSTANCE_ID AND csi.SERIAL_NUMBER IS NOT NULL;
5510:
5511: BEGIN
5962: -- added active_end_date to fix bug# 12776603; sorao for backporting project
5963: CURSOR get_mr_details_csr(p_unit_effectivity_id IN NUMBER) IS
5964: SELECT MR.mr_header_id,MR.title,MR.effective_from,MR.effective_to,MR.version_number,UE.csi_item_instance_id,
5965: ii.active_end_date
5966: from ahl_mr_headers_b MR,ahl_unit_effectivities_b UE, csi_item_instances ii
5967: WHERE MR.mr_header_id = UE.mr_header_id
5968: AND UE.csi_item_instance_id = ii.instance_id
5969: AND UE.unit_effectivity_id = p_unit_effectivity_id;
5970:
6233: -- JKJain, NR Analysis and Forecasting
6234: FUNCTION get_fleet_from_effectivity(p_unit_effectivity_id IN NUMBER) RETURN NUMBER IS
6235:
6236: Cursor get_fleet_header_id(c_unit_effectivity_id NUMBER) IS
6237: SELECT eff.fleet_header_id from ahl_mr_effectivities eff, ahl_unit_effectivities_b ue
6238: WHERE eff.mr_effectivity_id = ue.mr_effectivity_id
6239: AND ue.unit_effectivity_id = c_unit_effectivity_id;
6240:
6241: l_fleet_header_id NUMBER := null;