[Home] [Help]
2082: mr.whichever_first_code,
2083: mr.effective_to,
2084: mr.effective_from
2085: --FROM ahl_applicable_MRs appl, ahl_mr_headers_vl mr
2086: FROM ahl_applicable_MRs appl, ahl_mr_headers_b mr
2087: WHERE appl.csi_item_instance_id = p_csi_item_instance_id
2088: AND (appl.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
2089: AND appl.preceding_mr_header_id IS NULL
2090: AND appl.mr_header_id = mr.mr_header_id
6443: curr_mr.mr_header_id preceding_mr_header_id,
6444: apmr.descendent_count,
6445: mr.whichever_first_code,
6446: apmr.repetitive_flag
6447: FROM ahl_mr_headers_app_v mr, ahl_mr_headers_b curr_mr, ahl_applicable_mrs apmr
6448: --fix for bug number 5922149
6449: --WHERE mr.preceding_mr_header_id = curr_mr.mr_header_id AND
6450: 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
6451: curr_mr.mr_header_id = p_mr_header_id AND
6446: apmr.repetitive_flag
6447: FROM ahl_mr_headers_app_v mr, ahl_mr_headers_b curr_mr, ahl_applicable_mrs apmr
6448: --fix for bug number 5922149
6449: --WHERE mr.preceding_mr_header_id = curr_mr.mr_header_id AND
6450: 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
6451: curr_mr.mr_header_id = p_mr_header_id AND
6452: apmr.mr_header_id = mr.mr_header_id AND
6453: -- Fix for bug# 6711228.
6454: -- validation moved to before this procedure call.
6891:
6892: -- get all programs which have no schedules.
6893: CURSOR ahl_cont_not_scheduled_csr IS
6894: SELECT DISTINCT appl.program_mr_header_id, mr.whichever_first_code
6895: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
6896: -- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has
6897: -- the filter of application_usg_code.
6898: WHERE appl.program_mr_header_id = mr.mr_header_id
6899: AND pm_schedule_exists = 'N';
6892: -- get all programs which have no schedules.
6893: CURSOR ahl_cont_not_scheduled_csr IS
6894: SELECT DISTINCT appl.program_mr_header_id, mr.whichever_first_code
6895: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
6896: -- replaced ahl_mr_headers_app_v with ahl_mr_headers_b as ahl_applicable_mrs has
6897: -- the filter of application_usg_code.
6898: WHERE appl.program_mr_header_id = mr.mr_header_id
6899: AND pm_schedule_exists = 'N';
6900:
7491: appl.repetitive_flag,
7492: appl.show_repetitive_code,
7493: mr.effective_to,
7494: mr.effective_from
7495: FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
7496: WHERE appl.mr_header_id = mr.mr_header_id AND
7497: appl.pm_schedule_exists = 'N';
7498:
7499: l_last_day_of_window DATE;
8574: UE.DEFER_FROM_UE_ID,
8575: nvl(MR.whichever_first_code, 'FIRST') whichever_first_code,
8576: UDF.unit_deferral_type, UE.CS_INCIDENT_ID, UDF.DEFERRAL_EFFECTIVE_ON,
8577: UDF.AFFECT_DUE_CALC_FLAG, UDF.SET_DUE_DATE, UDF.unit_deferral_id
8578: FROM ahl_unit_effectivities_app_v UE, ahl_unit_deferrals_b UDF, ahl_mr_headers_b MR
8579: WHERE UE.defer_from_ue_id = UDF.unit_effectivity_id
8580: AND UE.mr_header_id = MR.mr_header_id(+)
8581: AND csi_item_instance_id = p_csi_item_instance_id
8582: AND status_code IS NULL
11186: FROM csi_item_instances cii, ahl_mr_effectivities mre
11187: WHERE mre.inventory_item_id = nvl(null, mre.inventory_item_id)
11188: AND mre.mr_header_id = nvl(null,mre.mr_header_id)
11189: -- added nvl conditions above as this seems to force use of index on
11190: -- ahl_mr_headers_b and also brings query cost down.
11191: AND mre.relationship_id is null
11192: AND mre.inventory_item_id = cii.inventory_item_id
11193: AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
11194: WHERE MR.mr_header_id = mre.mr_header_id