eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_UMP_PROCESSUNIT_PVT dependencies on AHL_APPLICABLE_MRS

Line 2655: CURSOR ahl_applicable_MRs (p_csi_item_instance_id IN NUMBER) IS

2651: --------------------------------------------------------------------------------
2652: -- Process Unit for ASO installation.
2653: PROCEDURE Process_ASO_Unit IS
2654:
2655: CURSOR ahl_applicable_MRs (p_csi_item_instance_id IN NUMBER) IS
2656: SELECT DISTINCT appl.csi_item_instance_id,
2657: appl.MR_header_id,
2658: mr.Title,
2659: mr.version_number,

Line 2673: FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr

2669: appl.processing_order, -- needed for 'order by' to work. This will be the same for all Eff.
2670: appl.terminate_trigger_check,
2671: appl.accomplish_trigger_type,
2672: appl.loop_chain_seq_num
2673: FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr
2674: WHERE appl.csi_item_instance_id = p_csi_item_instance_id
2675: AND (appl.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
2676: -- check on preceding_mr_header_id is commented out as part of SB Enh
2677: --AND appl.preceding_mr_header_id IS NULL

Line 2706: UPDATE AHL_APPLICABLE_MRS appl

2702: -- Added for SB Enh: Set applicable MRs order to process terminating MRs.
2703: -- default value of processing_order = 1
2704: -- select MRs that are terminating other MRs but themselves are not 'initiated_by' MRs.
2705: -- group MRs cannot be in a 'TERMINATES' relationship.
2706: UPDATE AHL_APPLICABLE_MRS appl
2707: SET processing_order = 2,
2708: terminate_trigger_check = 'Y'
2709: WHERE EXISTS (SELECT 'x'
2710: FROM AHL_MR_RELATIONSHIPS mrr, ahl_applicable_mrs parent_appl

Line 2710: FROM AHL_MR_RELATIONSHIPS mrr, ahl_applicable_mrs parent_appl

2706: UPDATE AHL_APPLICABLE_MRS appl
2707: SET processing_order = 2,
2708: terminate_trigger_check = 'Y'
2709: WHERE EXISTS (SELECT 'x'
2710: FROM AHL_MR_RELATIONSHIPS mrr, ahl_applicable_mrs parent_appl
2711: WHERE mrr.related_mr_header_id = appl.mr_header_id
2712: AND mrr.mr_header_id = parent_appl.mr_header_id -- parent row exists
2713: AND parent_appl.csi_item_instance_id = appl.csi_item_instance_id
2714: AND mrr.relationship_code = 'TERMINATES');

Line 2756: -- Read ahl_applicable_mrs for all MRs applicable to the item instance.

2752: p_current_usage_tbl => l_current_usage_tbl,
2753: p_counter_rules_tbl => l_counter_rules_tbl);
2754: END IF;
2755:
2756: -- Read ahl_applicable_mrs for all MRs applicable to the item instance.
2757: FOR l_appl_rec IN ahl_applicable_MRs(G_config_node_tbl(i).csi_item_instance_id) LOOP
2758:
2759: IF G_DEBUG = 'Y' THEN
2760: AHL_DEBUG_PUB.debug('Found applicable MR-ID[Title]:' || l_appl_rec.mr_header_id || '[' || l_appl_rec.title || ']');

Line 2757: FOR l_appl_rec IN ahl_applicable_MRs(G_config_node_tbl(i).csi_item_instance_id) LOOP

2753: p_counter_rules_tbl => l_counter_rules_tbl);
2754: END IF;
2755:
2756: -- Read ahl_applicable_mrs for all MRs applicable to the item instance.
2757: FOR l_appl_rec IN ahl_applicable_MRs(G_config_node_tbl(i).csi_item_instance_id) LOOP
2758:
2759: IF G_DEBUG = 'Y' THEN
2760: AHL_DEBUG_PUB.debug('Found applicable MR-ID[Title]:' || l_appl_rec.mr_header_id || '[' || l_appl_rec.title || ']');
2761: END IF;

Line 3706: FROM ahl_applicable_MRs

3702: -- verify processing flag as it may have been updated during processing.
3703: BEGIN
3704: SELECT process_status_flag
3705: INTO l_process_status_flag
3706: FROM ahl_applicable_MRs
3707: WHERE mr_header_id = p_applicable_mrs_rec.mr_header_id
3708: AND csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
3709: AND rownum < 2;
3710:

Line 4559: -- update ahl_applicable MRs table after processing complete.

4555: END IF;
4556:
4557: END LOOP;
4558:
4559: -- update ahl_applicable MRs table after processing complete.
4560: UPDATE AHL_APPLICABLE_MRS
4561: SET process_status_flag = 'Y',
4562: accomplished_ue_id = l_accomplished_ue_id
4563: WHERE mr_header_id = p_applicable_mrs_rec.mr_header_id

Line 4560: UPDATE AHL_APPLICABLE_MRS

4556:
4557: END LOOP;
4558:
4559: -- update ahl_applicable MRs table after processing complete.
4560: UPDATE AHL_APPLICABLE_MRS
4561: SET process_status_flag = 'Y',
4562: accomplished_ue_id = l_accomplished_ue_id
4563: WHERE mr_header_id = p_applicable_mrs_rec.mr_header_id
4564: AND csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id;

Line 4606: FROM ahl_applicable_mrs mr, ahl_mr_effectivities eff

4602: -- JKJain, NR Analysis and Forecasting
4603: CURSOR ahl_applicable_csr (p_instance_id IN NUMBER,
4604: p_mr_header_id IN NUMBER) IS
4605: SELECT DISTINCT mr.mr_effectivity_id, threshold_date,eff.fleet_header_id
4606: FROM ahl_applicable_mrs mr, ahl_mr_effectivities eff
4607: WHERE mr.mr_effectivity_id = eff.mr_effectivity_id AND
4608: csi_item_instance_id = p_instance_id AND
4609: mr.mr_header_id = p_mr_header_id;
4610:

Line 7440: FROM ahl_applicable_mrs

7436: -- To check if mr has a preceding mr.
7437: CURSOR ahl_appl_mr_csr (p_item_instance_id IN NUMBER,
7438: p_mr_header_id IN NUMBER) IS
7439: SELECT 'x'
7440: FROM ahl_applicable_mrs
7441: WHERE csi_item_instance_id = p_item_instance_id AND
7442: mr_header_id = p_mr_header_id AND
7443: implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
7444: preceding_mr_header_id IS NOT NULL;

Line 7733: FROM ahl_mr_headers_app_v mr, ahl_mr_headers_b curr_mr, ahl_applicable_mrs apmr

7729: curr_mr.mr_header_id preceding_mr_header_id,
7730: apmr.descendent_count,
7731: mr.whichever_first_code,
7732: apmr.repetitive_flag
7733: FROM ahl_mr_headers_app_v mr, ahl_mr_headers_b curr_mr, ahl_applicable_mrs apmr
7734: --fix for bug number 5922149
7735: --WHERE mr.preceding_mr_header_id = curr_mr.mr_header_id AND
7736: 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
7737: curr_mr.mr_header_id = p_mr_header_id AND

Line 7763: FROM ahl_mr_headers_app_v mr, ahl_applicable_mrs apmr

7759: apmr.repetitive_flag,
7760: mr.title,
7761: mr.effective_from,
7762: mr.effective_to
7763: FROM ahl_mr_headers_app_v mr, ahl_applicable_mrs apmr
7764: 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
7765: apmr.mr_header_id = mr.mr_header_id AND
7766: trunc(sysdate) >= trunc(nvl(mr.effective_from, sysdate)) AND
7767: --fix for bug# 9263774. Process prior MR versions.

Line 7789: FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr

7785: mr.title,
7786: mr.effective_from,
7787: mr.effective_to,
7788: apmr.terminate_trigger_check
7789: FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr
7790: WHERE amr.mr_header_id = p_curr_mr_header_id
7791: AND amr.related_mr_header_id = apmr.mr_header_id
7792: AND amr.relationship_code = 'INITIATES'
7793: AND amr.related_mr_header_id = mr.mr_header_id

Line 7797: FROM ahl_mr_relationships amr1, ahl_applicable_mrs apmr1

7793: AND amr.related_mr_header_id = mr.mr_header_id
7794: AND apmr.csi_item_instance_id = p_item_instance_id
7795: -- select only those 'follow after' MRs if all parent MRs are processed.
7796: AND NOT EXISTS (SELECT 'x'
7797: FROM ahl_mr_relationships amr1, ahl_applicable_mrs apmr1
7798: WHERE amr1.related_mr_header_id = amr.related_mr_header_id
7799: AND amr1.mr_header_id = apmr1.mr_header_id
7800: AND amr1.relationship_code NOT IN ('TERMINATES')
7801: AND (nvl(apmr1.process_status_flag,'N') = 'N'

Line 7840: from ahl_mr_relationships amr, ahl_applicable_mrs apmr

7836: CURSOR get_preceding_mrs_csr (p_item_instance_id IN number,
7837: p_follow_mr_header_id IN number) IS
7838:
7839: select amr.mr_header_id, apmr.accomplished_ue_id, apmr.process_status_flag
7840: from ahl_mr_relationships amr, ahl_applicable_mrs apmr
7841: where amr.mr_header_id = apmr.mr_header_id
7842: and amr.relationship_code = 'INITIATES'
7843: and amr.related_mr_header_id = p_follow_mr_header_id
7844: and apmr.csi_item_instance_id = p_item_instance_id;

Line 7929: -- update ahl_applicable_mrs with preceding mr ID for further processing.

7925:
7926: IF G_DEBUG = 'Y' THEN
7927: AHL_DEBUG_PUB.Debug('Calculated l_preceding_mr_hdr_id:' || l_preceding_mr_hdr_id);
7928: END IF;
7929: -- update ahl_applicable_mrs with preceding mr ID for further processing.
7930: UPDATE AHL_APPLICABLE_MRS
7931: SET PRECEDING_MR_HEADER_ID = l_preceding_mr_hdr_id
7932: WHERE csi_item_instance_id = l_appl_rec.csi_item_instance_id
7933: AND MR_header_id = l_appl_rec.MR_header_id;

Line 7930: UPDATE AHL_APPLICABLE_MRS

7926: IF G_DEBUG = 'Y' THEN
7927: AHL_DEBUG_PUB.Debug('Calculated l_preceding_mr_hdr_id:' || l_preceding_mr_hdr_id);
7928: END IF;
7929: -- update ahl_applicable_mrs with preceding mr ID for further processing.
7930: UPDATE AHL_APPLICABLE_MRS
7931: SET PRECEDING_MR_HEADER_ID = l_preceding_mr_hdr_id
7932: WHERE csi_item_instance_id = l_appl_rec.csi_item_instance_id
7933: AND MR_header_id = l_appl_rec.MR_header_id;
7934:

Line 8161: -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table

8157:
8158: END log_error_messages;
8159:
8160: -----------------------------------------------------------------------
8161: -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table
8162: -- for preventive maintenance installation.
8163: -- Pre-reqs :
8164: -- Parameters :
8165: --

Line 8234: INSERT INTO AHL_APPLICABLE_MRS (

8230: FOR j IN l_appl_activities_tbl.FIRST..l_appl_activities_tbl.LAST LOOP
8231: IF (l_appl_activities_tbl(j).service_line_id = l_appl_programs_tbl(i).service_line_id)
8232: THEN
8233:
8234: INSERT INTO AHL_APPLICABLE_MRS (
8235: CSI_ITEM_INSTANCE_ID,
8236: MR_HEADER_ID,
8237: MR_EFFECTIVITY_ID,
8238: REPETITIVE_FLAG ,

Line 8365: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr

8361:
8362: -- get all programs which have no schedules.
8363: CURSOR ahl_cont_not_scheduled_csr IS
8364: SELECT DISTINCT appl.program_mr_header_id, mr.whichever_first_code
8365: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
8366: -- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has
8367: -- the filter of application_usg_code.
8368: WHERE appl.program_mr_header_id = mr.mr_header_id
8369: AND pm_schedule_exists = 'N';

Line 8366: -- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has

8362: -- get all programs which have no schedules.
8363: CURSOR ahl_cont_not_scheduled_csr IS
8364: SELECT DISTINCT appl.program_mr_header_id, mr.whichever_first_code
8365: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
8366: -- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has
8367: -- the filter of application_usg_code.
8368: WHERE appl.program_mr_header_id = mr.mr_header_id
8369: AND pm_schedule_exists = 'N';
8370:

Line 8571: -- Update record in ahl_applicable_mrs with the calculated program end date.

8567: AHL_DEBUG_PUB.Debug('Program end date:' || l_program_due_date );
8568: AHL_DEBUG_PUB.Debug('Program calender days:' || l_program_calender_days );
8569: END IF;
8570:
8571: -- Update record in ahl_applicable_mrs with the calculated program end date.
8572: IF (l_program_expired_flag) THEN
8573: -- For this case, set program end date = sysdate -1.
8574: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
8575: ' SET program_end_date = :1' ||

Line 8574: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||

8570:
8571: -- Update record in ahl_applicable_mrs with the calculated program end date.
8572: IF (l_program_expired_flag) THEN
8573: -- For this case, set program end date = sysdate -1.
8574: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
8575: ' SET program_end_date = :1' ||
8576: ' WHERE program_mr_header_id = :2'||
8577: ' AND PM_schedule_exists = :3';
8578:

Line 8590: --UPDATE ahl_applicable_mrs

8586: ELSE
8587:
8588: -- dbms_output.put_line('Not expired');
8589: -- set program end date = least of (program_calculated_date, contract_start + calender_days, contract_end_date)
8590: --UPDATE ahl_applicable_mrs
8591: --SET program_end_date = LEAST (nvl(l_program_due_date,contract_end_date),
8592: -- decode(l_program_calender_days,0, contract_end_date, contract_start_date + l_program_calender_days),
8593: -- contract_end_date)
8594: --WHERE program_mr_header_id = program_rec.program_mr_header_id

Line 8602: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||

8598: -- and l_program_due_date.
8599:
8600: IF (program_rec.whichever_first_code = 'FIRST') THEN
8601:
8602: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
8603: ' SET program_end_date = LEAST (nvl(:1,contract_end_date),' ||
8604: ' decode(:2,0, contract_end_date, contract_start_date + :3),' ||
8605: ' contract_end_date)' ||
8606: ' WHERE program_mr_header_id = :4' ||

Line 8610: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||

8606: ' WHERE program_mr_header_id = :4' ||
8607: ' AND PM_schedule_exists = :5';
8608: ELSE
8609:
8610: l_upd_SQLstmt_str := 'UPDATE ahl_applicable_mrs' ||
8611: ' SET program_end_date = LEAST ( GREATEST (nvl(:1,contract_end_date),' ||
8612: ' decode(:2,0, contract_end_date, contract_start_date + :3)),' ||
8613: ' contract_end_date)' ||
8614: ' WHERE program_mr_header_id = :4' ||

Line 8648: FROM ahl_applicable_mrs

8644: SELECT DISTINCT mr_header_id, csi_item_instance_id, service_line_id,
8645: program_mr_header_id, contract_end_date, program_end_date,
8646: show_repetitive_code,
8647: repetitive_flag
8648: FROM ahl_applicable_mrs
8649: WHERE pm_schedule_exists = 'Y';
8650:
8651: -- Fix for FP bug# 6327241. We should be able to process multiple contracts.
8652: /*

Line 8658: FROM ahl_applicable_mrs

8654: -- and contract_start_date. Process only the first one.
8655: CURSOR ahl_cont_scheduled_csr(p_mr_header_id IN NUMBER) IS
8656: SELECT mr_header_id, service_line_id, mr_effectivity_id,
8657: program_mr_header_id, contract_end_date, program_end_date
8658: FROM ahl_applicable_mrs
8659: WHERE mr_header_id = p_mr_header_id
8660: AND pm_schedule_exists = 'Y'
8661: ORDER BY coverage_imp_level , contract_start_date;
8662: */

Line 8934: l_del_SQLstmt_str := 'DELETE FROM ahl_applicable_mrs' ||

8930: END IF; /* count */
8931: --END IF; /* one time mr flag */
8932:
8933: -- Delete duplicate programs/contracts for this mr_header_id if they exist.
8934: l_del_SQLstmt_str := 'DELETE FROM ahl_applicable_mrs' ||
8935: ' WHERE mr_header_id = :1 AND pm_schedule_exists = ''N'' '||
8936: ' AND service_line_id <> :2';
8937:
8938: IF G_DEBUG = 'Y' THEN

Line 8967: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr

8963: appl.repetitive_flag,
8964: appl.show_repetitive_code,
8965: mr.effective_to,
8966: mr.effective_from
8967: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
8968: WHERE appl.mr_header_id = mr.mr_header_id AND
8969: appl.pm_schedule_exists = 'N';
8970:
8971: l_last_day_of_window DATE;

Line 9327: FROM ahl_applicable_mrs

9323: CURSOR ahl_contract_exists_csr(p_mr_header_id IN NUMBER,
9324: p_due_date IN DATE) IS
9325: SELECT program_mr_header_id,
9326: service_line_id, contract_start_date, contract_end_date, program_end_date
9327: FROM ahl_applicable_mrs
9328: WHERE mr_header_id = p_mr_header_id
9329: AND pm_schedule_exists = 'N'
9330: AND trunc(program_end_date) >= trunc(p_due_date) -- eliminate expired programs.
9331: AND trunc(p_due_date) >= trunc(contract_start_date)

Line 9341: FROM ahl_applicable_mrs

9337: p_due_date IN DATE) IS
9338: SELECT program_mr_header_id,
9339: service_line_id,
9340: contract_start_date, contract_end_date, program_end_date
9341: FROM ahl_applicable_mrs
9342: WHERE pm_schedule_exists = 'N'
9343: AND trunc(program_end_date) >= trunc(p_due_date) -- eliminate expired programs.
9344: AND mr_header_id = p_mr_header_id
9345: AND trunc(p_due_date) <= trunc(contract_start_date)

Line 10171: FROM ahl_applicable_mrs

10167: -- Added for SB Enh.
10168: l_junk VARCHAR2(1);
10169: CURSOR check_applicable_mr(p_mr_title IN VARCHAR2, p_item_instance_id IN NUMBER) IS
10170: SELECT 'x'
10171: FROM ahl_applicable_mrs
10172: WHERE csi_item_instance_id = p_item_instance_id
10173: AND mr_header_id IN (select mr_header_id from ahl_mr_headers_b where title = p_mr_title);
10174:
10175: BEGIN

Line 11038: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr

11034: -- added select columns for SB Enh.
11035: CURSOR ahl_applicable_mr_csr (p_csi_item_instance_id IN NUMBER,
11036: p_mr_header_id IN NUMBER) IS
11037: SELECT 'x'
11038: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
11039: WHERE appl.mr_header_id = p_mr_header_id AND
11040: appl.csi_item_instance_id = p_csi_item_instance_id AND
11041: appl.mr_header_id = mr.mr_header_id AND
11042: trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1));

Line 11137: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr

11133: mr.effective_from,
11134: appl.terminate_trigger_check,
11135: appl.accomplish_trigger_type,
11136: appl.loop_chain_seq_num
11137: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
11138: WHERE appl.csi_item_instance_id = p_csi_item_instance_id AND
11139: appl.mr_header_id = mr.mr_header_id AND
11140: trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1)) AND
11141: appl.Implement_status_code = 'OPTIONAL_DO_NOT_IMPLEMENT' AND

Line 14198: FROM ahl_applicable_mrs

14194: -- To check if mr has a preceding mr.
14195: CURSOR ahl_appl_mr_csr (p_item_instance_id IN NUMBER,
14196: p_mr_header_id IN NUMBER) IS
14197: SELECT 'x'
14198: FROM ahl_applicable_mrs
14199: WHERE csi_item_instance_id = p_item_instance_id AND
14200: mr_header_id = p_mr_header_id AND
14201: implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
14202: --preceding_mr_header_id IS NOT NULL;

Line 14408: FROM ahl_applicable_mrs aamr, ahl_mr_headers_b mr1

14404: WHERE uer.related_ue_id = ue.unit_effectivity_id
14405: AND relationship_code = 'PARENT')
14406: -- not applicable
14407: AND NOT EXISTS (SELECT 'x'
14408: FROM ahl_applicable_mrs aamr, ahl_mr_headers_b mr1
14409: WHERE aamr.csi_item_instance_id = ue.csi_item_instance_id
14410: AND aamr.mr_header_id = mr1.mr_header_id
14411: AND mr1.title = mr.title
14412: )

Line 14467: -- Populate temporary table ahl_applicable_mrs.

14463: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
14464: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
14465: END IF;
14466:
14467: -- Populate temporary table ahl_applicable_mrs.
14468: IF (l_appl_mrs_tbl.COUNT > 0) THEN
14469: FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
14470: INSERT INTO AHL_APPLICABLE_MRS (
14471: CSI_ITEM_INSTANCE_ID,

Line 14470: INSERT INTO AHL_APPLICABLE_MRS (

14466:
14467: -- Populate temporary table ahl_applicable_mrs.
14468: IF (l_appl_mrs_tbl.COUNT > 0) THEN
14469: FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
14470: INSERT INTO AHL_APPLICABLE_MRS (
14471: CSI_ITEM_INSTANCE_ID,
14472: MR_HEADER_ID,
14473: MR_EFFECTIVITY_ID,
14474: REPETITIVE_FLAG ,

Line 14774: FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr

14770: mr.effective_to,
14771: apmr.process_status_flag,
14772: apmr.accomplished_ue_id,
14773: apmr.terminate_trigger_check
14774: FROM ahl_mr_relationships amr, ahl_applicable_mrs apmr, ahl_mr_headers_b mr
14775: WHERE amr.related_mr_header_id = p_mr_header_id
14776: AND amr.mr_header_id = apmr.mr_header_id
14777: AND amr.relationship_code = 'TERMINATES'
14778: AND amr.mr_header_id = mr.mr_header_id

Line 14933: UPDATE AHL_APPLICABLE_MRS

14929:
14930: l_termination_date := trunc(l_termination_date);
14931:
14932: IF (l_termination_date IS NOT NULL) THEN
14933: UPDATE AHL_APPLICABLE_MRS
14934: set TERMINATION_DATE = l_termination_date,
14935: TERMINATING_MR_HEADER_ID = l_terminating_mr_header_id
14936: where mr_header_id = p_applicable_mrs_rec.mr_header_id
14937: and csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id;

Line 14964: FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr

14960: -- get child loop or chain MRs
14961: CURSOR get_lp_chain_mrs(p_start_mr_header_id IN NUMBER,
14962: p_csi_item_instance_id IN NUMBER) IS
14963: SELECT apmr.mr_header_id, apmr.loop_chain_seq_num, mr.title
14964: FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr
14965: WHERE apmr.mr_header_id = mr.mr_header_id
14966: AND apmr.csi_item_instance_id = p_csi_item_instance_id
14967: AND apmr.start_mr_header_id = p_start_mr_header_id
14968: ORDER BY loop_chain_seq_num ASC;

Line 14980: where mr_header_id IN (select mr_header_id from ahl_applicable_MRs

14976: decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date)) accomplishment_date,
14977: unit_effectivity_id
14978: FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,
14979: (select title from ahl_mr_headers_b
14980: where mr_header_id IN (select mr_header_id from ahl_applicable_MRs
14981: where csi_item_instance_id = p_csi_instance_id
14982: and start_mr_header_id = p_mr_header_id)
14983: ) mr2
14984: WHERE def.csi_item_instance_id = p_csi_instance_id

Line 15178: FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr

15174: -- get child loop or chain MRs
15175: CURSOR get_lp_chain_mrs(p_start_mr_header_id IN NUMBER,
15176: p_csi_item_instance_id IN NUMBER) IS
15177: SELECT apmr.mr_header_id, apmr.loop_chain_seq_num, mr.title
15178: FROM ahl_applicable_MRs apmr, ahl_mr_headers_b mr
15179: WHERE apmr.mr_header_id = mr.mr_header_id
15180: AND apmr.csi_item_instance_id = p_csi_item_instance_id
15181: AND apmr.start_mr_header_id = p_start_mr_header_id
15182: ORDER BY loop_chain_seq_num ASC;

Line 15221: and mr.title in (select title from ahl_applicable_mrs appl, ahl_mr_headers_b mr1

15217: from ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
15218: where ue.csi_item_instance_id = p_csi_item_instance_id
15219: and ue.mr_header_id = mr.mr_header_id
15220: and ue.status_code IN ('ACCOMPLISHED','TERMINATED','INIT-ACCOMPLISHED')
15221: and mr.title in (select title from ahl_applicable_mrs appl, ahl_mr_headers_b mr1
15222: where appl.start_mr_header_id = p_start_mr_header_id
15223: and appl.csi_item_instance_id = p_csi_item_instance_id
15224: and appl.mr_header_id = mr1.mr_header_id
15225: )

Line 15299: AND EXISTS (select 'x' from ahl_applicable_mrs

15295: decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date)) due_date
15296: FROM ahl_temp_unit_SR_deferrals def
15297: WHERE def.unit_effectivity_id = p_chain_ue_id
15298: AND def.object_type = 'MR'
15299: AND EXISTS (select 'x' from ahl_applicable_mrs
15300: where csi_item_instance_id = def.csi_item_instance_id
15301: and mr_header_id = def.mr_header_id
15302: and accomplish_trigger_type = 'CHAIN')
15303: AND def.deferral_effective_on IS NOT NULL

Line 15677: FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr

15673: mr.whichever_first_code,
15674: mr.effective_to,
15675: mr.effective_from,
15676: appl.loop_chain_seq_num
15677: FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr
15678: WHERE appl.csi_item_instance_id = p_csi_item_instance_id
15679: AND appl.mr_header_id = p_mr_header_id
15680: AND (appl.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
15681: AND appl.mr_header_id = mr.mr_header_id