[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');
3417: /* Removing data from pa_resources_demorn as per 7336526 */
3418: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3419: FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3420:
3421: DELETE FROM pa_resources_denorm
3422: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec2.RESOURCE_EFFECTIVE_START_DATE
3423: AND PERSON_ID = delrec2.PERSON_ID;
3424:
3425: END LOOP ;
3428:
3429: /*Code starts for Bug 6943551*/
3430: FOR delrec IN cur_denorm_del(l_person_id) LOOP
3431:
3432: DELETE FROM pa_resources_denorm
3433: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec.RESOURCE_EFFECTIVE_START_DATE;
3434:
3435: END LOOP ;
3436: /*Code ends for Bug 6943551*/
3665:
3666: END;
3667:
3668: END LOOP;
3669: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3670: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3671: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3672:
3673: DELETE FROM pa_resources_denorm
3669: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3670: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3671: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3672:
3673: DELETE FROM pa_resources_denorm
3674: WHERE person_id = delrec3.person_id
3675: AND resource_effective_start_date = delrec3.assignment_start_date
3676: AND resource_organization_id = l_p_org_id;
3677: