[Home] [Help]
1319: Also if the default OU is NULL for a resource
1320: then gives an expected error. */
1321:
1322: PROCEDURE CHECK_OU(
1323: P_DEFAULT_OU IN PA_RESOURCES_DENORM.RESOURCE_ORG_ID%TYPE,
1324: P_EXP_ORG IN VARCHAR2,
1325: X_EXP_OU OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1326: X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1327: IS
2117: and rownum = 1 );
2118:
2119: CURSOR check_res_denorm IS
2120: select 'Y'
2121: from pa_resources_denorm
2122: where person_id = l_person_id
2123: and rownum=1;
2124:
2125: /*Cursor added for Bug 6943551*/
2126: CURSOR cur_denorm_del(p_person_id IN NUMBER ) IS
2127: SELECT prd.person_id,
2128: prd.resource_effective_start_date,
2129: prd.resource_effective_end_date
2130: FROM pa_resources_denorm prd,
2131: per_all_assignments_f paf,
2132: per_assignment_status_types past
2133: WHERE prd.person_id = paf.person_id
2134: AND prd.person_id = p_person_id
2143: CURSOR cur_denorm_del_redundant(p_person_id IN NUMBER, l_prof_date IN DATE) IS
2144: SELECT prd.person_id,
2145: prd.resource_effective_start_date,
2146: prd.resource_effective_end_date
2147: FROM pa_resources_denorm prd
2148: WHERE prd.resource_effective_end_date < sysdate
2149: AND prd.resource_effective_end_date < l_prof_date
2150: AND prd.resource_effective_end_date is not null
2151: AND prd.person_id = p_person_id
2246:
2247: ------------------------------------------------------------------------
2248: -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and
2249: -- PA_RESOURCE_TXN_ATTRIBUTES. No need for calendar assignment, timeline
2250: -- generation and we also do not populate the pa_resources_denorm table
2251: ------------------------------------------------------------------------
2252:
2253: IF (p_scheduled_member_flag = 'N') THEN
2254:
2555: -----------------------------------------------------------------------
2556: BEGIN
2557: select 'Y'
2558: into l_exists
2559: from pa_resources_denorm
2560: where person_id = l_person_id
2561: and l_assignment_end_date BETWEEN resource_effective_start_date
2562: AND resource_effective_end_date;
2563:
3378:
3379: /* Changes start for Bug 5662589 */
3380: ---------------------------------------------------------------------
3381: --This IF tells us the last record for a person as from cur_resources.
3382: --Here we are going to delete all rows for pa_resources_denorm for
3383: --which the resource_effective_end_date is greater than
3384: --max(assignment_end_date) from pa_r_project_resources_term_v res
3385: ---------------------------------------------------------------------
3386: IF (trunc(l_end_date) = trunc(l_assignment_end_date))
3391: IF P_DEBUG_MODE = 'Y' THEN
3392: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3393: END IF;
3394:
3395: DELETE FROM PA_RESOURCES_DENORM
3396: WHERE person_id = l_person_id
3397: AND resource_effective_start_date > l_end_date;
3398:
3399: log_message('right after calling deleting denorm data');
3413: /*2749061: Do not create FI for past employees*/
3414: IF (trunc(l_end_date) = trunc(l_assignment_end_date)) THEN
3415: -- AND trunc(l_end_date) >= sysdate) THEN
3416:
3417: /* Cleaning pa_resources_denorm start *** Bug 9198412 */
3418: IF (trunc(eRec.termination_date) IS NULL
3419: and trunc(l_assignment_end_date) < trunc(sysdate)
3420: and l_refresh = 'Y') THEN
3421:
3418: IF (trunc(eRec.termination_date) IS NULL
3419: and trunc(l_assignment_end_date) < trunc(sysdate)
3420: and l_refresh = 'Y') THEN
3421:
3422: delete from pa_resources_denorm
3423: where person_id = l_person_id
3424: and resource_effective_start_date > l_end_date;
3425:
3426: END IF;
3423: where person_id = l_person_id
3424: and resource_effective_start_date > l_end_date;
3425:
3426: END IF;
3427: /* Cleaning pa_resources_denorm start end *** Bug 9198412 */
3428:
3429: /* Removing data from pa_resources_demorn as per 7336526 */
3430: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3431: FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3429: /* Removing data from pa_resources_demorn as per 7336526 */
3430: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3431: FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3432:
3433: DELETE FROM pa_resources_denorm
3434: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec2.RESOURCE_EFFECTIVE_START_DATE
3435: AND PERSON_ID = delrec2.PERSON_ID;
3436:
3437: END LOOP ;
3440:
3441: /*Code starts for Bug 6943551*/
3442: FOR delrec IN cur_denorm_del(l_person_id) LOOP
3443:
3444: DELETE FROM pa_resources_denorm
3445: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec.RESOURCE_EFFECTIVE_START_DATE
3446: AND PERSON_ID = delrec.PERSON_ID; --bug#8840426
3447:
3448: END LOOP ;
3679:
3680: END;
3681:
3682: END LOOP;
3683: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3684: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3685: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3686:
3687: DELETE FROM pa_resources_denorm
3683: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3684: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3685: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3686:
3687: DELETE FROM pa_resources_denorm
3688: WHERE person_id = delrec3.person_id
3689: AND resource_effective_start_date = delrec3.assignment_start_date
3690: AND resource_organization_id = l_p_org_id;
3691: