[Home] [Help]
303: x_next_due_date_rec OUT NOCOPY next_due_date_rec_type,
304: x_mr_interval_found OUT NOCOPY BOOLEAN,
305: x_return_val OUT NOCOPY BOOLEAN);
306:
307: -- To write a record into ahl_temp_unit_effectivities.
308: PROCEDURE Create_temp_unit_effectivity (X_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE);
309:
310: -- To process the decendents in case the mr is a group MR.
311: PROCEDURE Process_GroupMR (p_applicable_mrs_rec IN applicable_mrs_rec_type,
304: x_mr_interval_found OUT NOCOPY BOOLEAN,
305: x_return_val OUT NOCOPY BOOLEAN);
306:
307: -- To write a record into ahl_temp_unit_effectivities.
308: PROCEDURE Create_temp_unit_effectivity (X_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE);
309:
310: -- To process the decendents in case the mr is a group MR.
311: PROCEDURE Process_GroupMR (p_applicable_mrs_rec IN applicable_mrs_rec_type,
312: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
308: PROCEDURE Create_temp_unit_effectivity (X_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE);
309:
310: -- To process the decendents in case the mr is a group MR.
311: PROCEDURE Process_GroupMR (p_applicable_mrs_rec IN applicable_mrs_rec_type,
312: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
313: p_unit_effectivity_tbl IN unit_effectivity_tbl_type,
314: p_old_UE_forecast_sequence IN NUMBER := -1);
315:
316: -- To process the dependent MRs based on the value of preceding MR.
529: -- Following procedures were added to fix bug# 9263774.
530: -------------------------------------------------------
531: -- To process prior revision of a MR.
532: PROCEDURE Process_Prior_MR_Version (p_applicable_mrs_rec IN applicable_mrs_rec_type,
533: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
534: p_unit_effectivity_tbl IN unit_effectivity_tbl_type,
535: p_old_UE_forecast_sequence IN NUMBER);
536:
537: -- Get applicability for expired MRs.
565:
566:
567: -- process child MRs in a loop or chain
568: PROCEDURE Process_Loop_Chain_MRs (p_item_instance_id IN NUMBER,
569: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
570: p_repetivity_flag IN VARCHAR2 := 'Y',
571: p_current_usage_tbl IN counter_values_tbl_type,
572: p_counter_rules_tbl IN counter_rules_tbl_type,
573: p_last_due_date IN DATE,
725: END IF;
726:
727: -- Initialize temporary tables.
728:
729: DELETE FROM AHL_TEMP_UNIT_EFFECTIVITIES;
730: DELETE FROM AHL_TEMP_UNIT_SR_DEFERRALS;
731:
732: /* this validation will be done after getting root instance.
733: -- validate item instance.
3426: WHERE PARENT_UE.UNIT_EFFECTIVITY_ID = RELN.ORIGINATOR_UE_ID
3427: AND PARENT_UE.STATUS_CODE = 'INIT-ACCOMPLISHED')
3428: ORDER BY mr.version_number ASC, forecast_sequence ASC ;
3429:
3430: -- Get group MRs if any, from ahl_temp_unit_effectivities for the item instance.
3431: -- select only the first row.
3432: -- modified to support multiple MR revisions for bug# 9263774
3433: CURSOR ahl_temp_ue_csr (p_item_instance_id IN NUMBER,
3434: --p_mr_header_id IN NUMBER,
3441: orig_csi_item_instance_id,
3442: orig_mr_header_id,
3443: --orig_forecast_sequence,
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
3476: )
3477: WHERE ROWNUM < 2;
3478:
3479: -- in case of 'next-due', we need to check p_last_due_date equality condition too.
3480: -- Get group MRs if any, from ahl_temp_unit_effectivities for the item instance.
3481: -- select only the first row.
3482: -- modified to support multiple MR revisions for bug# 9263774
3483: CURSOR ahl_temp_ue_csr1 (p_item_instance_id IN NUMBER,
3484: --p_mr_header_id IN NUMBER,
3491: orig_csi_item_instance_id,
3492: orig_mr_header_id,
3493: --orig_forecast_sequence,
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
3539: CURSOR preceding_due_date_csr (p_preceding_instance_id IN NUMBER,
3540: p_preceding_mr_header_id IN NUMBER) IS
3541: SELECT * FROM (
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)
3593: l_forecast_sequence NUMBER;
3594: l_old_UE_forecast_sequence NUMBER;
3595: l_next_due_date_rec next_due_date_rec_type;
3596:
3597: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
3598: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
3599:
3600: l_temp_grp_mr_rec ahl_temp_ue_csr%ROWTYPE;
3601:
3594: l_old_UE_forecast_sequence NUMBER;
3595: l_next_due_date_rec next_due_date_rec_type;
3596:
3597: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
3598: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
3599:
3600: l_temp_grp_mr_rec ahl_temp_ue_csr%ROWTYPE;
3601:
3602: l_last_accomplishment_date DATE;
7360: ------------------------------------------------------------------------------
7361: -- To process the decendents in case the mr is a group MR.
7362:
7363: PROCEDURE Process_GroupMR (p_applicable_mrs_rec IN applicable_mrs_rec_type,
7364: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
7365: p_unit_effectivity_tbl IN unit_effectivity_tbl_type,
7366: p_old_UE_forecast_sequence IN NUMBER := -1)
7367:
7368: IS
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;
7445:
7446: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
7447: l_initialize_ue_rec ahl_temp_unit_effectivities%ROWTYPE;
7448:
7449: l_junk VARCHAR2(1);
7450: l_visit_end_date DATE;
7443: implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
7444: preceding_mr_header_id IS NOT NULL;
7445:
7446: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
7447: l_initialize_ue_rec ahl_temp_unit_effectivities%ROWTYPE;
7448:
7449: l_junk VARCHAR2(1);
7450: l_visit_end_date DATE;
7451: l_unit_effectivity_id NUMBER;
7811: CURSOR get_prec_mr_duedate_csr (p_mr_header_id IN NUMBER,
7812: p_item_instance_id IN number) IS
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)
7997: IF (p_next_due_date_rec.due_date IS NULL) THEN
7998: null; /* leave all preceding_check_flag = 'Y' */
7999: ELSE
8000: -- modified to fix bug# 9263774(due date calc for prior MR versions).
8001: UPDATE ahl_temp_unit_effectivities
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
8009: END IF;
8010: ELSE
8011: /* this MR has its accomplishments; update all records irrespective of due date */
8012: -- modified to fix bug# 9263774(due date calc for prior MR versions).
8013: UPDATE ahl_temp_unit_effectivities
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
8027: END Update_check_flag;
8028:
8029:
8030: -----------------------------------------------------------
8031: -- To write a record into ahl_temp_unit_effectivities.
8032:
8033: PROCEDURE Create_temp_unit_effectivity (X_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE)
8034:
8035: IS
8029:
8030: -----------------------------------------------------------
8031: -- To write a record into ahl_temp_unit_effectivities.
8032:
8033: PROCEDURE Create_temp_unit_effectivity (X_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE)
8034:
8035: IS
8036:
8037: BEGIN
8039: AHL_DEBUG_PUB.Debug('Start Create Temporary Unit Effect');
8040: END IF;
8041:
8042: -- insert into temporary table.
8043: insert into ahl_temp_unit_effectivities (
8044: csi_item_instance_id,
8045: MR_header_id,
8046: due_date,
8047: mr_interval_id,
8711:
8712:
8713: l_del_SQLstmt_str VARCHAR2(2000);
8714:
8715: l_temp_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
8716: /* record structure to hold the activity and due date details */
8717:
8718: -- Added to fix bug# 3546136.
8719: l_temp_ue_initrec ahl_temp_unit_effectivities%ROWTYPE;
8715: l_temp_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
8716: /* record structure to hold the activity and due date details */
8717:
8718: -- Added to fix bug# 3546136.
8719: l_temp_ue_initrec ahl_temp_unit_effectivities%ROWTYPE;
8720:
8721: -- parameters needed to call get_accomplishment_details.
8722: l_last_accomplishment_date DATE;
8723: l_last_acc_counter_val_tbl counter_values_tbl_type;
8997: l_due_at_counter_val_tbl counter_values_tbl_type;
8998:
8999: l_applicable_mrs_rec applicable_mrs_rec_type;
9000:
9001: l_temp_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
9002: l_temp_ue_initrec ahl_temp_unit_effectivities%ROWTYPE;
9003:
9004: -- Added for bug# 6711228
9005: l_no_forecast_flag BOOLEAN;
8998:
8999: l_applicable_mrs_rec applicable_mrs_rec_type;
9000:
9001: l_temp_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
9002: l_temp_ue_initrec ahl_temp_unit_effectivities%ROWTYPE;
9003:
9004: -- Added for bug# 6711228
9005: l_no_forecast_flag BOOLEAN;
9006: l_mr_accomplish_exists BOOLEAN;
9464: -- Added service_line_id to fix FP bug# 5481605.
9465: -- read temporary table sequentially.
9466: CURSOR ahl_mr_header_csr IS
9467: SELECT DISTINCT mr_header_id, csi_item_instance_id, service_line_id
9468: FROM ahl_temp_unit_effectivities;
9469:
9470: -- retrieve current unit effectivity records.
9471: CURSOR ahl_unit_effectivity_csr (p_csi_item_instance_id IN NUMBER,
9472: p_mr_header_id IN NUMBER,
9485: CURSOR ahl_temp_effectivity_csr(p_csi_item_instance_id IN NUMBER,
9486: p_mr_header_id IN NUMBER,
9487: p_service_line_id IN NUMBER) IS
9488: SELECT unit_effectivity_id
9489: FROM ahl_temp_unit_effectivities
9490: WHERE mr_header_id = p_mr_header_id
9491: AND csi_item_instance_id = p_csi_item_instance_id
9492: AND service_line_id = p_service_line_id
9493: FOR UPDATE OF unit_effectivity_id
9549: l_unit_effectivity_id := l_ue_id_tbl(i);
9550:
9551: -- update record.
9552:
9553: UPDATE ahl_temp_unit_effectivities
9554: SET unit_effectivity_id = l_unit_effectivity_id
9555: WHERE CURRENT OF ahl_temp_effectivity_csr;
9556: END IF; -- ahl_temp_effectivity_csr%NOTFOUND
9557: END LOOP; -- l_ue_id_tbl.FIRST
14162: --------------------------------------------------------------
14163: -- Added to fix bug# 9263774
14164: -- Process prior MR versions.
14165: PROCEDURE Process_Prior_MR_Version (p_applicable_mrs_rec IN applicable_mrs_rec_type,
14166: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
14167: p_unit_effectivity_tbl IN unit_effectivity_tbl_type,
14168: p_old_UE_forecast_sequence IN NUMBER)
14169: IS
14170:
14201: implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
14202: --preceding_mr_header_id IS NOT NULL;
14203: accomplish_trigger_type = 'INITIATED_BY';
14204:
14205: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
14206: l_initialize_ue_rec ahl_temp_unit_effectivities%ROWTYPE;
14207:
14208: l_junk VARCHAR2(1);
14209: l_visit_end_date DATE;
14202: --preceding_mr_header_id IS NOT NULL;
14203: accomplish_trigger_type = 'INITIATED_BY';
14204:
14205: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
14206: l_initialize_ue_rec ahl_temp_unit_effectivities%ROWTYPE;
14207:
14208: l_junk VARCHAR2(1);
14209: l_visit_end_date DATE;
14210:
14785: CURSOR get_earliest_date_csr(p_mr_header_id IN NUMBER,
14786: p_instance_id IN NUMBER) IS
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
15001: l_mr_seq_num nbr_tbl_type;
15002: l_mr_title_tbl vchar_tbl_type;
15003:
15004: l_loop_chain_MR_tbl loop_chain_MR_tbl_type;
15005: --l_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15006:
15007: l_lc_mr_accomplishment_date DATE;
15008: l_lc_acc_ue_id NUMBER;
15009: l_lc_acc_deferral_flag BOOLEAN;
15017: l_found_deferral VARCHAR2(1);
15018:
15019: l_no_forecast_flag BOOLEAN;
15020: l_last_acc_counter_val_tbl counter_values_tbl_type;
15021: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15022:
15023: l_loop_due_date DATE;
15024: l_loop_last_due_date DATE;
15025: l_loop_last_counter_val_tbl counter_values_tbl_type;
15316: l_mr_seq_num nbr_tbl_type;
15317: l_mr_title_tbl vchar_tbl_type;
15318:
15319: l_loop_chain_MR_tbl loop_chain_MR_tbl_type;
15320: --l_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15321:
15322: l_lc_mr_accomplishment_date DATE;
15323: l_lc_acc_ue_id NUMBER;
15324: l_lc_acc_deferral_flag BOOLEAN;
15332: l_found_deferral VARCHAR2(1);
15333:
15334: l_no_forecast_flag BOOLEAN;
15335: l_last_acc_counter_val_tbl counter_values_tbl_type;
15336: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15337:
15338: l_loop_due_date DATE;
15339: l_loop_last_due_date DATE;
15340: l_loop_last_counter_val_tbl counter_values_tbl_type;
15359: l_start_due_date_tbl date_tbl_type;
15360: l_start_mr_id_tbl nbr_tbl_type;
15361: l_start_vst_end_tbl date_tbl_type;
15362:
15363: l_unplan_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15364: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
15365: l_iteration_seq number;
15366:
15367: l_visit_start_date DATE;
15360: l_start_mr_id_tbl nbr_tbl_type;
15361: l_start_vst_end_tbl date_tbl_type;
15362:
15363: l_unplan_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15364: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
15365: l_iteration_seq number;
15366:
15367: l_visit_start_date DATE;
15368: l_visit_end_date DATE;
15644: END Process_Chain_Init;
15645: -----------------------------------------------------------
15646:
15647: PROCEDURE Process_Loop_Chain_MRs (p_item_instance_id IN NUMBER,
15648: p_new_unit_effectivity_rec IN ahl_temp_unit_effectivities%ROWTYPE,
15649: p_repetivity_flag IN VARCHAR2 := 'Y',
15650: p_current_usage_tbl IN counter_values_tbl_type,
15651: p_counter_rules_tbl IN counter_rules_tbl_type,
15652: p_last_due_date IN DATE,
15703: l_no_forecast_available VARCHAR2(1) := 'N';
15704:
15705: l_next_due_date_rec next_due_date_rec_type;
15706: l_mr_accomplish_exists BOOLEAN;
15707: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15708: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
15709:
15710: l_first_time_calc VARCHAR2(1);
15711: l_repetivity_flag VARCHAR2(1);
15704:
15705: l_next_due_date_rec next_due_date_rec_type;
15706: l_mr_accomplish_exists BOOLEAN;
15707: l_new_unit_effectivity_rec ahl_temp_unit_effectivities%ROWTYPE;
15708: l_new_unit_effectivity_initrec ahl_temp_unit_effectivities%ROWTYPE;
15709:
15710: l_first_time_calc VARCHAR2(1);
15711: l_repetivity_flag VARCHAR2(1);
15712: