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_MR_HEADERS_B

Line 114: MR_TITLE ahl_mr_headers_b.title%TYPE

110: MR_HEADER_ID NUMBER,
111: LOOP_CHAIN_SEQ_NUM NUMBER,
112: ACCOMPLISHED_UE_ID NUMBER,
113: ACCOMPLISHMENT_EXISTS VARCHAR2(1),
114: MR_TITLE ahl_mr_headers_b.title%TYPE
115: );
116:
117: -- Begin -- Added for performance fix bug# 6893404.
118: -- number table.

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 3168: FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,

3164: -- fix for bug# 6875650. Deferral date includes timestamp.
3165: --decode (affect_due_calc_flag, 'N', trunc(nvl(visit_end_date, deferral_effective_on)), trunc(nvl(visit_end_date, due_date)))
3166:
3167: decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date))
3168: FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,
3169: (select title from ahl_mr_headers_b where mr_header_id = p_mr_header_id) mr2
3170: WHERE def.csi_item_instance_id = p_csi_instance_id
3171: AND def.mr_header_id = mr1.mr_header_id
3172: AND mr1.title = mr2.title

Line 3169: (select title from ahl_mr_headers_b where mr_header_id = p_mr_header_id) mr2

3165: --decode (affect_due_calc_flag, 'N', trunc(nvl(visit_end_date, deferral_effective_on)), trunc(nvl(visit_end_date, due_date)))
3166:
3167: decode (def.affect_due_calc_flag, 'N', def.deferral_effective_on, nvl(def.visit_end_date, def.due_date))
3168: FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,
3169: (select title from ahl_mr_headers_b where mr_header_id = p_mr_header_id) mr2
3170: WHERE def.csi_item_instance_id = p_csi_instance_id
3171: AND def.mr_header_id = mr1.mr_header_id
3172: AND mr1.title = mr2.title
3173: --AND mr_header_id = p_mr_header_id

Line 3184: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr1, ahl_mr_headers_b mr2

3180: -- check for existence of old MR revision
3181: CURSOR check_old_mr_ver (p_csi_instance_id IN NUMBER,
3182: p_mr_header_id IN NUMBER) IS
3183: SELECT 'x'
3184: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr1, ahl_mr_headers_b mr2
3185: WHERE mr2.mr_header_id = p_mr_header_id
3186: and mr1.title = mr2.title
3187: and mr1.version_number <> mr2.version_number
3188: and mr1.mr_header_id = ue.mr_header_id

Line 3415: ahl_mr_headers_b mr

3411: p_appln_usg_code IN VARCHAR2) IS
3412: SELECT ue.unit_effectivity_id, ue.status_code, reln.related_ue_id, reln.originator_ue_id, ue.mr_header_id
3413: --FROM ahl_unit_effectivities_app_v UE, ahl_UE_relationships reln,
3414: FROM ahl_unit_effectivities_b UE, ahl_UE_relationships reln,
3415: ahl_mr_headers_b mr
3416: WHERE UE.unit_effectivity_id = RELN.RELATED_UE_ID(+)
3417: AND UE.mr_header_id = mr.mr_header_id
3418: AND mr.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
3419: AND UE.csi_item_instance_id = p_csi_item_instance_id

Line 3418: AND mr.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)

3414: FROM ahl_unit_effectivities_b UE, ahl_UE_relationships reln,
3415: ahl_mr_headers_b mr
3416: WHERE UE.unit_effectivity_id = RELN.RELATED_UE_ID(+)
3417: AND UE.mr_header_id = mr.mr_header_id
3418: AND mr.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
3419: AND UE.csi_item_instance_id = p_csi_item_instance_id
3420: AND UE.application_usg_code = p_appln_usg_code
3421: AND (UE.Status_code IS NULL OR UE.status_code = 'INIT-DUE')
3422: AND UE.defer_from_ue_id IS NULL -- do not pick deferred unit effectivities.

Line 3448: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) AND

3444: visit_end_date
3445: FROM ahl_temp_unit_effectivities
3446: WHERE csi_item_instance_id = p_item_instance_id AND
3447: --mr_header_id = p_mr_header_id AND
3448: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) AND
3449: orig_csi_item_instance_id IS NOT NULL AND
3450: orig_mr_header_id IS NOT NULL AND
3451: trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) > trunc(p_last_due_date) AND
3452: trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date) AND

Line 3468: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)

3464: visit_end_date
3465: FROM ahl_temp_unit_SR_deferrals
3466: WHERE csi_item_instance_id = p_item_instance_id
3467: --AND mr_header_id = p_mr_header_id
3468: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
3469: AND trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) > trunc(p_last_due_date)
3470: AND trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date)
3471: AND deferral_effective_on IS NULL -- pick only SR related MRs.
3472: -- ignore records with null due dates.

Line 3498: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) AND

3494: visit_end_date
3495: FROM ahl_temp_unit_effectivities
3496: WHERE csi_item_instance_id = p_item_instance_id AND
3497: --mr_header_id = p_mr_header_id AND
3498: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title) AND
3499: orig_csi_item_instance_id IS NOT NULL AND
3500: orig_mr_header_id IS NOT NULL AND
3501: trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) >= trunc(p_last_due_date) AND
3502: trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date) AND

Line 3518: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)

3514: visit_end_date
3515: FROM ahl_temp_unit_SR_deferrals
3516: WHERE csi_item_instance_id = p_item_instance_id
3517: --AND mr_header_id = p_mr_header_id
3518: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_title)
3519: AND trunc(nvl(visit_end_date, nvl(due_date, p_last_due_date))) >= trunc(p_last_due_date)
3520: AND trunc(nvl(visit_end_date, nvl(due_date, p_due_date+1))) <= trunc(p_due_date)
3521: AND deferral_effective_on IS NULL -- pick only SR related MRs.
3522: -- ignore records with null due dates.

Line 3546: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

3542: SELECT due_date
3543: FROM ahl_temp_unit_effectivities
3544: WHERE csi_item_instance_id = p_preceding_instance_id AND
3545: --mr_header_id = p_preceding_mr_header_id AND
3546: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
3547: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
3548: ) AND
3549: preceding_check_flag = 'N'
3550:

Line 3547: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)

3543: FROM ahl_temp_unit_effectivities
3544: WHERE csi_item_instance_id = p_preceding_instance_id AND
3545: --mr_header_id = p_preceding_mr_header_id AND
3546: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
3547: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
3548: ) AND
3549: preceding_check_flag = 'N'
3550:
3551: UNION

Line 3555: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

3551: UNION
3552: SELECT due_date
3553: FROM ahl_temp_unit_SR_deferrals
3554: WHERE csi_item_instance_id = p_preceding_instance_id AND
3555: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
3556: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
3557: )
3558: ORDER by due_date
3559: )

Line 3556: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)

3552: SELECT due_date
3553: FROM ahl_temp_unit_SR_deferrals
3554: WHERE csi_item_instance_id = p_preceding_instance_id AND
3555: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
3556: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_preceding_mr_header_id)
3557: )
3558: ORDER by due_date
3559: )
3560: WHERE ROWNUM < 2;

Line 3573: AND ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_title)

3569: --FROM ahl_unit_effectivities_app_v ue
3570: FROM ahl_unit_effectivities_b ue
3571: WHERE ue.csi_item_instance_id = p_item_instance_id
3572: AND ue.application_usg_code = p_appln_usg_code
3573: AND ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_title)
3574: AND ue.status_code IS NULL
3575: AND ue.manually_planned_flag = 'Y'
3576: AND NOT EXISTS (SELECT 'x'
3577: FROM ahl_ue_relationships uer, ahl_unit_effectivities_b ue1

Line 3739: AND tdef.MR_HEADER_ID IN (select MR_HEADER_ID from ahl_mr_headers_b where title = p_applicable_mrs_rec.title)

3735: --dbms_output.put_line ('Start Build Effectivity for mr:csi' || p_applicable_mrs_rec.mr_header_id || ':' || p_applicable_mrs_rec.csi_item_instance_id );
3736: --dbms_output.put_line ('l_mr_termination_date:' || l_mr_termination_date || ':' || p_applicable_mrs_rec.title);
3737: DELETE FROM ahl_temp_unit_SR_deferrals tdef
3738: WHERE tdef.CSI_ITEM_INSTANCE_ID = p_applicable_mrs_rec.csi_item_instance_id
3739: AND tdef.MR_HEADER_ID IN (select MR_HEADER_ID from ahl_mr_headers_b where title = p_applicable_mrs_rec.title)
3740: AND trunc(tdef.DUE_DATE) >= trunc(l_mr_termination_date)
3741: AND NOT EXISTS (select 'x' from ahl_temp_unit_SR_deferrals where orig_unit_effectivity_id = tdef.unit_effectivity_id);
3742: EXCEPTION
3743: WHEN OTHERS THEN

Line 4659: ue.mr_header_id IN (select mr_header_id from ahl_mr_headers_b mr where mr.title = p_title) AND

4655: FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b ud
4656: WHERE ue.unit_effectivity_id = ud.unit_effectivity_id AND
4657: ud.unit_deferral_type = 'INIT-DUE' AND
4658: ue.csi_item_instance_id = p_csi_item_instance_id AND
4659: ue.mr_header_id IN (select mr_header_id from ahl_mr_headers_b mr where mr.title = p_title) AND
4660: ue.status_code = 'INIT-DUE';
4661:
4662: -- get all init-due counter records setup for this unit effectivity.
4663: CURSOR ahl_unit_thresholds_csr (p_unit_deferral_id IN NUMBER) IS

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 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

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
7738: apmr.mr_header_id = mr.mr_header_id AND
7739: -- Fix for bug# 6711228.
7740: -- validation moved to before this procedure call.

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 7817: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

7813: SELECT * FROM (
7814: SELECT due_date, visit_end_date
7815: FROM ahl_temp_unit_effectivities
7816: WHERE csi_item_instance_id = p_item_instance_id AND
7817: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
7818: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
7819: where mr2.mr_header_id = p_mr_header_id)
7820: ) AND
7821: preceding_check_flag = 'N'

Line 7818: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2

7814: SELECT due_date, visit_end_date
7815: FROM ahl_temp_unit_effectivities
7816: WHERE csi_item_instance_id = p_item_instance_id AND
7817: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
7818: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
7819: where mr2.mr_header_id = p_mr_header_id)
7820: ) AND
7821: preceding_check_flag = 'N'
7822:

Line 7827: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

7823: UNION
7824: SELECT due_date, visit_end_date
7825: FROM ahl_temp_unit_SR_deferrals
7826: WHERE csi_item_instance_id = p_item_instance_id AND
7827: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
7828: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
7829: where mr2.mr_header_id = p_mr_header_id )
7830: )
7831: ORDER by due_date ASC

Line 7828: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2

7824: SELECT due_date, visit_end_date
7825: FROM ahl_temp_unit_SR_deferrals
7826: WHERE csi_item_instance_id = p_item_instance_id AND
7827: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
7828: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
7829: where mr2.mr_header_id = p_mr_header_id )
7830: )
7831: ORDER by due_date ASC
7832: )

Line 8006: from ahl_mr_headers_b mr

8002: SET preceding_check_flag = 'N'
8003: WHERE csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
8004: --AND mr_header_id = p_applicable_mrs_rec.mr_header_id
8005: AND mr_header_id IN (select mr.mr_header_id
8006: from ahl_mr_headers_b mr
8007: where mr.title = p_applicable_mrs_rec.title)
8008: AND due_date >= p_next_due_date_rec.due_date;
8009: END IF;
8010: ELSE

Line 8018: from ahl_mr_headers_b mr

8014: SET preceding_check_flag = 'N'
8015: WHERE csi_item_instance_id = p_applicable_mrs_rec.csi_item_instance_id
8016: --AND mr_header_id = p_applicable_mrs_rec.mr_header_id;
8017: AND mr_header_id IN (select mr.mr_header_id
8018: from ahl_mr_headers_b mr
8019: where mr.title = p_applicable_mrs_rec.title);
8020:
8021: END IF;
8022:

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 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 10066: FROM ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UDF, ahl_mr_headers_b MR

10062: nvl(MR.whichever_first_code, 'FIRST') whichever_first_code,
10063: UDF.unit_deferral_type, UE.CS_INCIDENT_ID, UDF.DEFERRAL_EFFECTIVE_ON,
10064: UDF.AFFECT_DUE_CALC_FLAG, UDF.SET_DUE_DATE, UDF.unit_deferral_id,
10065: MR.title
10066: FROM ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UDF, ahl_mr_headers_b MR
10067: WHERE UE.defer_from_ue_id = UDF.unit_effectivity_id
10068: AND UE.mr_header_id = MR.mr_header_id(+)
10069: AND UE.csi_item_instance_id = p_csi_item_instance_id
10070: AND UE.application_usg_code = p_appln_usage_code

Line 10173: AND mr_header_id IN (select mr_header_id from ahl_mr_headers_b where title = p_mr_title);

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
10176:
10177: IF G_DEBUG = 'Y' THEN

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 12877: -- ahl_mr_headers_b and also brings query cost down.

12873: FROM csi_item_instances cii, ahl_mr_effectivities mre
12874: WHERE mre.inventory_item_id = nvl(null, mre.inventory_item_id)
12875: AND mre.mr_header_id = nvl(null,mre.mr_header_id)
12876: -- added nvl conditions above as this seems to force use of index on
12877: -- ahl_mr_headers_b and also brings query cost down.
12878: AND mre.relationship_id is null
12879: AND mre.inventory_item_id = cii.inventory_item_id
12880: AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
12881: WHERE MR.mr_header_id = mre.mr_header_id

Line 14391: SELECT csi_item_instance_id, (select mr_header_id from ahl_mr_headers_b mr

14387: PROCEDURE Process_Appl_Expired_MRs
14388: IS
14389:
14390: CURSOR get_mr_csr(p_item_instance_id IN NUMBER) IS
14391: SELECT csi_item_instance_id, (select mr_header_id from ahl_mr_headers_b mr
14392: where mr.title = exp_mr.title
14393: and mr.version_number = exp_mr.version_number) mr_header_id
14394: FROM
14395: (

Line 14399: FROM ahl_unit_effectivities_app_v UE, ahl_mr_headers_b mr

14395: (
14396: SELECT ue.csi_item_instance_id,
14397: mr.Title,
14398: max(mr.version_number) version_number
14399: FROM ahl_unit_effectivities_app_v UE, ahl_mr_headers_b mr
14400: -- pick up only top nodes.
14401: WHERE ue.mr_header_id = mr.mr_header_id
14402: AND NOT EXISTS (SELECT 'x'
14403: FROM ahl_ue_relationships uer

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 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 14791: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

14787: SELECT * FROM (
14788: SELECT due_date, visit_end_date
14789: FROM ahl_temp_unit_effectivities
14790: WHERE csi_item_instance_id = p_instance_id AND
14791: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
14792: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
14793: ) AND
14794: preceding_check_flag = 'N'
14795:

Line 14792: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)

14788: SELECT due_date, visit_end_date
14789: FROM ahl_temp_unit_effectivities
14790: WHERE csi_item_instance_id = p_instance_id AND
14791: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
14792: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
14793: ) AND
14794: preceding_check_flag = 'N'
14795:
14796: UNION

Line 14800: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1

14796: UNION
14797: SELECT due_date, visit_end_date
14798: FROM ahl_temp_unit_SR_deferrals
14799: WHERE csi_item_instance_id = p_instance_id AND
14800: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
14801: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
14802: )
14803: ORDER by due_date
14804: )

Line 14801: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)

14797: SELECT due_date, visit_end_date
14798: FROM ahl_temp_unit_SR_deferrals
14799: WHERE csi_item_instance_id = p_instance_id AND
14800: mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
14801: where mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = p_mr_header_id)
14802: )
14803: ORDER by due_date
14804: )
14805: WHERE ROWNUM < 2;

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 14978: FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1,

14974: SELECT * FROM
14975: (SELECT
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)

Line 14979: (select title from ahl_mr_headers_b

14975: (SELECT
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

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 15195: from ahl_mr_headers_b mr_title

15191: WHERE ue1.unit_effectivity_id = ue2.start_lc_ue_id
15192: AND ue.csi_item_instance_id = p_csi_item_instance_id
15193: AND ue.status_code IS NOT NULL AND ue.status_code <> 'INIT-DUE'
15194: AND EXISTS (select 'x'
15195: from ahl_mr_headers_b mr_title
15196: (select mr_header_id from ahl_unit_effectivities_b ue1
15197: where unit_effectivity_id = ue.start_lc_ue_id) start_mr
15198: where
15199: and appl_mr.start_mr_header_id = p_mr_header_id

Line 15217: from ahl_unit_effectivities_b ue, ahl_mr_headers_b mr

15213: -- get inprogress chain MRs
15214: CURSOR get_inprogress_chain_MRs(p_start_mr_header_id IN NUMBER,
15215: p_csi_item_instance_id IN NUMBER) IS
15216: SELECT ue.start_lc_ue_id, max(ue.loop_chain_seq_num)
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

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 15241: WHERE ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_start_mr_title)

15237: ue.due_date,
15238: ue.mr_header_id,
15239: to_date(null) visit_end_date
15240: FROM ahl_unit_effectivities_b ue
15241: WHERE ue.mr_header_id IN (select amh.mr_header_id from ahl_mr_headers_b amh where amh.title = p_start_mr_title)
15242: AND ue.csi_item_instance_id = p_csi_item_instance_id
15243: AND ue.status_code IS NULL
15244: AND ue.manually_planned_flag = 'Y'
15245: AND ue.defer_from_ue_id IS NULL

Line 15262: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_start_mr_title);

15258: mr_header_id,
15259: visit_end_date
15260: FROM ahl_temp_unit_SR_deferrals
15261: WHERE csi_item_instance_id = p_csi_item_instance_id
15262: AND mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1 where mr1.title = p_start_mr_title);
15263:
15264: -- get chain UE id
15265: CURSOR get_chain_ue_csr(p_start_lc_ue_id in number,
15266: p_chain_seq_num in number) is

Line 15269: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr

15265: CURSOR get_chain_ue_csr(p_start_lc_ue_id in number,
15266: p_chain_seq_num in number) is
15267: SELECT ue.unit_effectivity_id, AHL_UMP_UTIL_PKG.get_Visit_Status (ue.unit_effectivity_id) l_visit_status,
15268: ue.defer_from_ue_id, ue.mr_header_id, mr.title
15269: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
15270: where ue.mr_header_id = mr.mr_header_id
15271: and ue.start_lc_ue_id = p_start_lc_ue_id
15272: and ue.loop_chain_seq_num = p_chain_seq_num
15273: and ue.accomplish_trigger_type = 'CHAIN';

Line 15356: l_mr_title ahl_mr_headers_b.title%TYPE;

15352: l_chain_ue_id number;
15353: l_visit_status varchar2(30);
15354: l_defer_from_ue_id number;
15355: l_mr_header_id number;
15356: l_mr_title ahl_mr_headers_b.title%TYPE;
15357:
15358: l_start_ue_id_tbl nbr_tbl_type;
15359: l_start_due_date_tbl date_tbl_type;
15360: l_start_mr_id_tbl nbr_tbl_type;

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

Line 15687: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr

15683:
15684: CURSOR ahl_ue_csr (p_start_lc_ue_id IN NUMBER,
15685: p_loop_chain_seq_num IN NUMBER) IS
15686: SELECT unit_effectivity_id, mr.title
15687: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
15688: WHERE ue.mr_header_id = mr.mr_header_id
15689: AND ue.start_lc_ue_id = p_start_lc_ue_id
15690: AND ue.loop_chain_seq_num = p_loop_chain_seq_num
15691: AND (ue.STATUS_CODE IS NULL or ue.STATUS_CODE = 'INIT-DUE');

Line 15718: l_ue_mr_title ahl_mr_headers_b.title%TYPE;

15714: l_visit_end_date DATE;
15715: l_visit_assign_code VARCHAR2(30);
15716:
15717: l_ue_id NUMBER;
15718: l_ue_mr_title ahl_mr_headers_b.title%TYPE;
15719:
15720: BEGIN
15721:
15722: IF G_DEBUG = 'Y' THEN