[Home] [Help]
282: BEGIN
283:
284: BEGIN
285:
286: INSERT INTO hri_cs_suph
287: (sup_business_group_id
288: ,sup_person_id
289: ,sup_assignment_id
290: ,sup_assignment_status_type_id
375: BEGIN
376:
377: g_chain_transactions := g_chain_transactions + 1;
378:
379: UPDATE hri_cs_suph
380: SET effective_end_date = p_end_date
381: WHERE sub_person_id = p_person_id
382: AND p_end_date BETWEEN effective_start_date AND effective_end_date;
383:
399: CURSOR existing_chain_csr IS
400: SELECT
401: effective_start_date
402: ,effective_end_date
403: FROM hri_cs_suph
404: WHERE sub_person_id = g_crrnt_chain(p_level).person_id
405: AND g_crrnt_chain_start_date
406: BETWEEN effective_start_date AND effective_end_date;
407:
408: /* Bug 2670477 - join by person id */
409: CURSOR next_chain_start_csr IS
410: SELECT
411: MIN(effective_start_date) next_chain_start_date
412: FROM hri_cs_suph
413: WHERE sub_person_id = g_crrnt_chain(p_level).person_id
414: AND effective_start_date > g_crrnt_chain_start_date;
415:
416: l_existing_chain_start DATE;
451: IF (l_existing_chain_start < g_crrnt_chain_start_date) THEN
452: g_chain_transactions := g_chain_transactions + 1;
453: /* End date existing chain */
454: /* Bug 2670477 - join by person id */
455: UPDATE hri_cs_suph
456: SET effective_end_date = g_crrnt_chain_start_date - 1
457: WHERE sub_person_id =
458: g_crrnt_chain(p_level).person_id
459: AND effective_start_date = l_existing_chain_start;
462: ELSIF (l_existing_chain_start = g_crrnt_chain_start_date) THEN
463: g_chain_transactions := g_chain_transactions + 1;
464: /* Delete existing chain */
465: /* Bug 2670477 - join by person id */
466: DELETE FROM hri_cs_suph
467: WHERE sub_person_id = g_crrnt_chain(p_level).person_id
468: AND effective_start_date = l_existing_chain_start;
469:
470: END IF;
477: BEGIN
478: IF (g_final_date_tab(g_crrnt_chain(p_level).person_id) IS NOT NULL) THEN
479: g_chain_transactions := g_chain_transactions + 1;
480: /* Bug 2670477 - join by person id */
481: DELETE FROM hri_cs_suph
482: WHERE sub_person_id = g_crrnt_chain(p_level).person_id
483: AND effective_start_date > g_final_date_tab(g_crrnt_chain(p_level).person_id);
484: END IF;
485: EXCEPTION WHEN OTHERS THEN
1027:
1028: /* Selects single link in chain for a non-terminated supervisor on a date */
1029: CURSOR chain_csr IS
1030: SELECT *
1031: FROM hri_cs_suph
1032: WHERE sub_person_id = p_person_id
1033: AND sup_person_id = p_person_id
1034: AND sub_invalid_flag_code = 'N'
1035: AND sub_leaf_flag_code = p_from_leaf_flag
1044:
1045: g_chain_transactions := g_chain_transactions + 1;
1046:
1047: /* Update all links in chain at once */
1048: UPDATE hri_cs_suph
1049: SET sub_leaf_flag_code = p_to_leaf_flag
1050: WHERE sub_person_id = p_person_id
1051: AND effective_start_date = p_change_date
1052: AND sub_invalid_flag_code = 'N';
1056:
1057: g_chain_transactions := g_chain_transactions + 2;
1058:
1059: /* Insert new chain */
1060: INSERT INTO hri_cs_suph
1061: (sup_business_group_id
1062: ,sup_person_id
1063: ,sup_assignment_id
1064: ,sup_assignment_status_type_id
1094: ,orphan_flag_code
1095: ,p_to_leaf_flag
1096: ,p_change_date
1097: ,chain_rec.effective_end_date
1098: FROM hri_cs_suph
1099: WHERE sub_person_id = p_person_id
1100: AND effective_start_date = chain_rec.effective_start_date
1101: AND sub_invalid_flag_code = 'N';
1102:
1100: AND effective_start_date = chain_rec.effective_start_date
1101: AND sub_invalid_flag_code = 'N';
1102:
1103: /* End date existing chain */
1104: UPDATE hri_cs_suph
1105: SET effective_end_date = p_change_date - 1
1106: WHERE sub_person_id = p_person_id
1107: AND effective_start_date = chain_rec.effective_start_date
1108: AND sub_invalid_flag_code = 'N';