3:
4: --FUNCTION Get_Country_name(p_country_code VARCHAR2) RETURN VARCHAR2 ;
5:
6: -- Global variable for debugging. Bug 4352236.
7: -- G_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
8: --------------------------------------------------------------------------------------------------------------
9: -- This procedure prints the text which is being passed as the input
10: -- Input parameters
11: -- Parameters Type Required Description
17: IS
18: -- P_DEBUG_MODE varchar2(1); -- Bug 4352236 - use global variable G_DEBUG_MODE
19: BEGIN
20: --dbms_output.put_line('log: ' || p_log_msg);
21: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
22: -- IF (G_DEBUG_MODE ='Y') THEN
23: pa_debug.write('HR_UPDATE_API', 'log: ' || p_log_msg, 3);
24: -- END IF;
25: -- NULL;
19: BEGIN
20: --dbms_output.put_line('log: ' || p_log_msg);
21: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
22: -- IF (G_DEBUG_MODE ='Y') THEN
23: pa_debug.write('HR_UPDATE_API', 'log: ' || p_log_msg, 3);
24: -- END IF;
25: -- NULL;
26: END log_message;
27:
304: v_return_status VARCHAR2(1);
305: v_dummy VARCHAR2(1):= 'N';
306: BEGIN
307: -- Initialize the Error stack
308: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.check_exp_OU');
309: -- Initialize the error status
310: x_return_status := FND_API.G_RET_STS_SUCCESS;
311:
312: If p_org_id is NOT NULL then
324: x_error_message_code := 'Exp Ou';
325: End if;
326: */
327: -- reset the Error stack
328: PA_DEBUG.Reset_Err_Stack;
329:
330: EXCEPTION
331:
332: WHEN NO_DATA_FOUND THEN
336:
337: WHEN OTHERS THEN
338: -- Set the exception Message and the stack
339: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.check_exp_OU'
340: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
341: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342: -- 4537865 : RESET x_error_message_code also
343: x_error_message_code := SQLCODE ;
344:
371: v_msg_data VARCHAR2(2000);
372: v_msg_count NUMBER;
373: BEGIN
374: -- Initialize the Error stack
375: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_create_resoruce_denorm');
376: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
377: v_job_id_new := P_job_id_new;
378: v_job_level_new := P_job_level_new;
379: v_job_id_old := P_job_id_old;
410:
411:
412:
413: -- reset the Error stack
414: PA_DEBUG.Reset_Err_Stack;
415:
416: EXCEPTION
417:
418: WHEN OTHERS THEN
421: x_msg_data := SUBSTRB(SQLERRM ,1,240);
422:
423: -- Set the exception Message and the stack
424: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_API.call_create_resource_denorm'
425: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
426: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
427: raise;
428:
429:
449: l_job_level_old NUMBER;
450: l_job_level_new NUMBER;
451: l_job_id_old PER_JOBS.JOB_ID%type;
452: l_job_id_new PER_JOBS.JOB_ID%type;
453: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
454:
455: BEGIN
456: -- Initialize the Error stack
457: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
453: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
454:
455: BEGIN
456: -- Initialize the Error stack
457: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
458: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
459:
460: l_job_id_new := P_job_id_new;
461: l_job_level_new := P_job_level_new;
484: ,x_msg_count => x_msg_count
485: );
486:
487: -- reset the Error stack
488: PA_DEBUG.Reset_Err_Stack;
489:
490: EXCEPTION
491:
492: WHEN OTHERS THEN
495: x_msg_data := SUBSTRB(SQLERRM ,1,240);
496:
497: -- Set the exception Message and the stack
498: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_job_level_res_denorm'
499: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
500: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501: raise;
502:
503: END update_job_level_res_denorm;
543: AND pjr.from_job_id = l_job_id
544: AND pjr.from_job_group_id = pjg.job_group_id;
545:
546: -- P_DEBUG_MODE varchar2(1); -- Bug 4352236
547: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
548:
549: BEGIN
550:
551: -- Initialize the Error stack
548:
549: BEGIN
550:
551: -- Initialize the Error stack
552: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_all_jobs');
553: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
554:
555: l_job_id := P_job_id;
556:
567: fetch get_job_ids into l_job_id;
568: Exit when get_job_ids%NOTFOUND;
569:
570: If l_job_id is NOT NULL then
571: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
572: IF (P_DEBUG_MODE ='Y') THEN
573: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
574: pa_debug.write_file('LOG',pa_debug.g_err_stage);
575: end if;
569:
570: If l_job_id is NOT NULL then
571: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
572: IF (P_DEBUG_MODE ='Y') THEN
573: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
574: pa_debug.write_file('LOG',pa_debug.g_err_stage);
575: end if;
576:
577: update_job_level_res_denorm
570: If l_job_id is NOT NULL then
571: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
572: IF (P_DEBUG_MODE ='Y') THEN
573: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
574: pa_debug.write_file('LOG',pa_debug.g_err_stage);
575: end if;
576:
577: update_job_level_res_denorm
578: ( P_job_id_old => l_job_id
588: END LOOP;
589: CLOSE get_job_ids;
590:
591: -- reset the Error stack
592: PA_DEBUG.Reset_Err_Stack;
593:
594: EXCEPTION
595:
596: WHEN OTHERS THEN
599: x_msg_data := SUBSTRB(SQLERRM ,1,240);
600:
601: -- Set the exception Message and the stack
602: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_all_jobs'
603: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
604: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605: raise;
606:
607: END update_all_jobs;
626: l_job_id PER_JOBS.JOB_ID%type;
627: l_master_job_id PER_JOBS.JOB_ID%type;
628: l_PRJG_job_id PER_JOBS.JOB_ID%type;
629: l_condition VARCHAR2(10);
630: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
631:
632: BEGIN
633: -- Initialize the Error stack
634: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
630: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
631:
632: BEGIN
633: -- Initialize the Error stack
634: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
635: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
636:
637: IF P_DEBUG_MODE = 'Y' THEN
638: log_message('P_from_job_id_old = ' || P_from_job_id_old);
892:
893:
894:
895: -- reset the Error stack
896: PA_DEBUG.Reset_Err_Stack;
897:
898: EXCEPTION
899:
900: WHEN NO_DATA_FOUND THEN
906: x_msg_data := SUBSTRB(SQLERRM ,1,240);
907:
908: -- Set the exception Message and the stack
909: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pa_job_relation_job_id'
910: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
911: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
912: raise;
913:
914:
931: l_master_job_id PER_JOBS.JOB_ID%type;
932: l_job_level_old pa_resources_denorm.resource_job_level%type;
933: l_job_level_new pa_resources_denorm.resource_job_level%type;
934: l_job_group_id per_job_groups.job_group_id%type;
935: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
936:
937: -- Cursor to get the associated master job id of the PRJG job id
938: CURSOR get_master_job (
939: l_job_id per_jobs.job_id%type
952:
953: BEGIN
954:
955: -- Initialize the Error stack
956: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.perform_job_updates');
957: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
958: IF P_DEBUG_MODE = 'Y' THEN
959: log_message('**** Performing Job Level Updates ****');
960: END IF;
1053: l_job_level_old pa_resources_denorm.resource_job_level%type;
1054: l_job_level_new pa_resources_denorm.resource_job_level%type;
1055: l_job_group_id per_job_groups.job_group_id%type;
1056: l_isPRJG VARCHAR2(1);
1057: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1058:
1059: BEGIN
1060:
1061: -- Initialize the Error stack
1058:
1059: BEGIN
1060:
1061: -- Initialize the Error stack
1062: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_dff');
1063: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1064:
1065: l_job_id := P_job_id;
1066: l_job_level_old := P_job_level_old;
1205: );
1206: BEGIN
1207:
1208: -- Initialize the Error stack
1209: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_valid_grades_job_id');
1210: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1211:
1212: -- if the grade id changes then update all the jobs which are affected and
1213: -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1235: END LOOP;
1236: CLOSE get_job_ids;
1237:
1238: -- reset the Error stack
1239: PA_DEBUG.Reset_Err_Stack;
1240:
1241: EXCEPTION
1242:
1243: WHEN NO_DATA_FOUND THEN
1248: x_msg_count := 1 ;
1249: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1250: -- Set the exception Message and the stack
1251: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_valid_grades_job_id'
1252: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1253: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1254: raise;
1255:
1256:
1318: BEGIN
1319:
1320:
1321: -- Initialize the Error stack
1322: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_grades_job_id');
1323: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1324:
1325: -- if the sequence is changes then update all the jobs which are affected due to
1326: -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1347: END LOOP;
1348: CLOSE get_job_ids;
1349:
1350: -- reset the Error stack
1351: PA_DEBUG.Reset_Err_Stack;
1352: EXCEPTION
1353:
1354: WHEN NO_DATA_FOUND THEN
1355: NULL;
1359: x_msg_count := 1 ;
1360: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1361: -- Set the exception Message and the stack
1362: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_grades_job_id'
1363: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1364: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1365: raise;
1366:
1367:
1407: v_msg_count NUMBER;
1408:
1409: BEGIN
1410: -- Initialize the Error stack
1411: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_job_levels');
1412: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1413:
1414: -- Code for if job mapping has changed
1415:
1430: );
1431: End if;
1432:
1433: -- reset the Error stack
1434: PA_DEBUG.Reset_Err_Stack;
1435:
1436: EXCEPTION
1437:
1438: WHEN NO_DATA_FOUND THEN
1444: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1445:
1446: -- Set the exception Message and the stack
1447: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_job_levels'
1448: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1449: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1450: raise;
1451:
1452: END update_job_levels;
1497: --dbms_output.put_line('Calling Update_OU_resource');
1498: --dbms_output.put_line('End date for OU:' || p_default_OU_new || 'end date:' || P_end_date_new);
1499:
1500: -- Initialize the Error stack
1501: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_OU_resource');
1502: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1503:
1504: FOR rec IN res_denorm_recs LOOP
1505:
1530:
1531: End if;
1532:
1533: -- reset the Error stack
1534: PA_DEBUG.Reset_Err_Stack;
1535:
1536: EXCEPTION
1537:
1538: WHEN NO_DATA_FOUND THEN
1542: -- 4537865 : RESET x_msg_count and x_msg_data also
1543: x_msg_count := 1 ;
1544: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1545: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_OU_resource'
1546: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1547: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1548: raise;
1549: END Update_OU_resource;
1550:
1614:
1615: BEGIN
1616:
1617: -- Initialize the Error stack
1618: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Default_OU_Change');
1619: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1620: -- for each resource found for the default ou changes update
1621: -- pa resource ou entity with new default OU
1622:
1640: ,x_return_status => x_return_status
1641: ,x_msg_data => x_msg_data
1642: ,x_msg_count => x_msg_count
1643: ) ;
1644: PA_DEBUG.Reset_Err_Stack;
1645: Return;
1646: End if;
1647:
1648: pa_hr_update_api.check_exp_OU
1843:
1844: End if;
1845:
1846: -- reset the Error stack
1847: PA_DEBUG.Reset_Err_Stack;
1848:
1849: EXCEPTION
1850: WHEN FND_API.G_EXC_ERROR THEN
1851: x_return_status := FND_API.G_RET_STS_ERROR;
1854: x_msg_count := 1 ;
1855: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1856: -- Set the exception Message and the stack
1857: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Default_OU_Change'
1858: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1859: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1860: raise;
1861:
1862: END Default_OU_Change;
1880: l_job_level NUMBER;
1881: l_proj_job_group_new NUMBER;
1882: l_proj_job_group_old NUMBER;
1883: l_job_group_id NUMBER;
1884: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1885:
1886: CURSOR get_new_job_ids IS
1887: SELECT job_id
1888: FROM per_jobs
1897:
1898: BEGIN
1899:
1900: -- Initialize the Error stack
1901: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Proj_Res_Job_Group_Change');
1902: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1903:
1904: l_proj_job_group_old := TO_NUMBER(p_proj_job_group_old);
1905: l_proj_job_group_new := TO_NUMBER(p_proj_job_group_new);
2026: END IF;
2027:
2028:
2029: -- reset the Error stack
2030: PA_DEBUG.Reset_Err_Stack;
2031:
2032: EXCEPTION
2033: WHEN FND_API.G_EXC_ERROR THEN
2034: x_return_status := FND_API.G_RET_STS_ERROR;
2037: x_msg_count := 1 ;
2038: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2039: -- Set the exception Message and the stack
2040: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Proj_Res_Job_Group_Change'
2041: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2042: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2043: raise;
2044:
2045: END Proj_Res_Job_Group_Change;
2080: ORDER BY ind.person_id;
2081:
2082: BEGIN
2083: -- Initialize the Error stack
2084: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pull_resources');
2085: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2086:
2087: open get_all_resource(p_organization_id);
2088: LOOP
2133: --set the final return status to SUCCESS after loop
2134: x_return_status := FND_API.G_RET_STS_SUCCESS;
2135:
2136: -- reset the Error stack
2137: PA_DEBUG.Reset_Err_Stack;
2138:
2139: EXCEPTION
2140: WHEN OTHERS THEN
2141: -- 4537865 : RESET x_msg_count and x_msg_data also
2142: x_msg_count := 1 ;
2143: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2144: -- Set the exception Message and the stack
2145: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pull_resources'
2146: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2147:
2148: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2149: raise;
2150:
2284: AND rownum = 1 );
2285:
2286: BEGIN
2287: -- Initialize the Error stack
2288: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.make_resource_inactive');
2289: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2290:
2291: --dbms_output.put_line('Inside make resource inactive');
2292: --dbms_output.put_line('Default OU:' || p_default_ou);
2353:
2354: END IF;
2355:
2356: -- reset the Error stack
2357: PA_DEBUG.Reset_Err_Stack;
2358: EXCEPTION
2359: WHEN OTHERS THEN
2360: -- 4537865 : RESET x_msg_count and x_msg_data also
2361: x_msg_count := 1 ;
2361: x_msg_count := 1 ;
2362: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2363: -- Set the exception Message and the stack
2364: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.make_resource_inactive'
2365: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2366: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2367: raise;
2368:
2369:
2474:
2475:
2476: BEGIN
2477: -- Initialize the Error stack
2478: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_forcast_api');
2479: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2480:
2481: If (P_table_name = 'PER_JOB_EXTRA_INFO' or
2482: P_table_name = 'PER_VALID_GRADES' or
2561: --set the final return status to SUCCESS after loop
2562: x_return_status := FND_API.G_RET_STS_SUCCESS;
2563:
2564: -- reset the Error stack
2565: PA_DEBUG.Reset_Err_Stack;
2566: EXCEPTION
2567: WHEN FND_API.G_EXC_ERROR THEN
2568: x_return_status := FND_API.G_RET_STS_ERROR;
2569: WHEN OTHERS THEN
2571: x_msg_count := 1 ;
2572: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2573: -- Set the exception Message and the stack
2574: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_forcast_api'
2575: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2576: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577: raise;
2578:
2579:
2613:
2614: BEGIN
2615:
2616: -- Initialize the Error stack
2617: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_billable_resoruce_denorm');
2618: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2619: v_job_id_new := P_job_id_new;
2620: v_job_id_old := P_job_id_old;
2621:
2678:
2679:
2680: End if;
2681: -- reset the Error stack
2682: PA_DEBUG.Reset_Err_Stack;
2683:
2684: EXCEPTION
2685:
2686: WHEN OTHERS THEN
2688: x_msg_count := 1 ;
2689: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2690: -- Set the exception Message and the stack
2691: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_billable_resoruce_denorm'
2692: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2693: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2694: raise;
2695:
2696: END call_billable_resoruce_denorm;
2728: l_pull_res_flag VARCHAR2(1) := 'N';
2729: l_end_date_res_flag VARCHAR2(1) := 'N';
2730: l_prv_person_id NUMBER;
2731: l_resource_id NUMBER;
2732: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2733:
2734: /*
2735: CURSOR all_job_res_recs IS
2736: SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
2756: WHERE res.job_id = p_job_id;
2757:
2758: BEGIN
2759: -- Initialize the Error stack
2760: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_job_extra_billability');
2761: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2762:
2763: IF P_DEBUG_MODE = 'Y' THEN
2764: log_message('beginning of per_job_extra_billability, P_calling_mode:'|| P_calling_mode || ', job_id: '||P_job_id);
2993: COMMIT;
2994: END IF;
2995:
2996: -- reset the Error stack
2997: PA_DEBUG.Reset_Err_Stack;
2998: IF P_DEBUG_MODE = 'Y' THEN
2999: log_message('after Reset_Err_Stack');
3000: END IF;
3001:
3013: x_msg_count := 1 ;
3014: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3015: -- Set the exception Message and the stack
3016: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_job_extra_billability'
3017: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3018: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3019: RAISE;
3020: END per_job_extra_billability;
3021:
3037:
3038: l_resource_id NUMBER;
3039: l_status_code VARCHAR2(30);
3040: l_rec_ver_num NUMBER;
3041: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3042:
3043: CURSOR get_nominations(l_resource_id IN NUMBER) IS
3044: select cand.candidate_id, cand.record_version_number
3045: from pa_candidates cand,
3062:
3063: BEGIN
3064:
3065: -- Initialize the Error stack
3066: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.withdraw_cand_nominations');
3067: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3068:
3069: l_resource_id := PA_RESOURCE_UTILS.get_resource_id(p_person_id);
3070:
3132: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3133: END IF;
3134:
3135: -- reset the Error stack
3136: PA_DEBUG.Reset_Err_Stack;
3137:
3138: EXCEPTION
3139:
3140: WHEN OTHERS THEN
3142: x_msg_count := 1 ;
3143: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3144: -- Set the exception Message and the stack
3145: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.withdraw_cand_nominations'
3146: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3147: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3148: raise;
3149: END withdraw_cand_nominations;
3150:
3192: --dbms_output.put_line('Person Id:' || p_person_id);
3193: --dbms_output.put_line('New End Date:' || p_new_end_date);
3194:
3195: -- Initialize the Error stack
3196: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_EndDate');
3197: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3198:
3199: l_count := 0; -- bug 7147575 : None records in pa_resources_denorm so far.
3200:
3305: END IF ; --((l_invol_term = 'N') AND (l_return_status = FND_API.G_RET_STS_SUCCESS)) bug 5683340
3306:
3307: END IF ; -- bug 7147575 : IF (l_count > 0 )
3308:
3309: PA_DEBUG.Reset_Err_Stack;
3310:
3311: EXCEPTION
3312:
3313: WHEN NO_DATA_FOUND THEN
3318: x_msg_count := 1 ;
3319: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3320: -- Set the exception Message and the stack
3321: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_EndDate'
3322: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3323: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3324: raise;
3325: END Update_EndDate;
3326:
3371:
3372: --dbms_output.put_line('Inside Update Org');
3373:
3374: -- Initialize the Error stack
3375: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Org');
3376:
3377: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3378:
3379: /* Check if new organization belongs to an expenditure
3517: END IF;
3518:
3519:
3520: -- reset the Error stack
3521: PA_DEBUG.Reset_Err_Stack;
3522:
3523: EXCEPTION
3524: WHEN NO_DATA_FOUND THEN
3525: X_RETURN_STATUS := fnd_api.g_ret_sts_error;
3529: x_msg_count := 1 ;
3530: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3531: -- Set the exception Message and the stack
3532: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Org'
3533: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3534: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3535: raise;
3536: END Update_Org;
3537:
3668: l_old_job_utilizable pa_resources_denorm.utilization_flag%type;
3669: l_new_job_utilizable pa_resources_denorm.utilization_flag%type;
3670: l_old_job_schedulable pa_resources_denorm.schedulable_flag%type;
3671: l_new_job_schedulable pa_resources_denorm.schedulable_flag%type;
3672: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3673:
3674: -- Cursor to get all denormalized resource records
3675: -- for this HR assignment
3676: CURSOR res_denorm_recs IS
3690: GROUP BY person_id;
3691:
3692: BEGIN
3693: -- Initialize the Error stack
3694: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Job');
3695: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3696: IF P_DEBUG_MODE = 'Y' THEN
3697: log_message('beginning of Update_Job');
3698: END IF;
3832: x_return_status := FND_API.G_RET_STS_ERROR ;
3833: END IF;
3834:
3835: -- reset the Error stack
3836: PA_DEBUG.Reset_Err_Stack;
3837:
3838: EXCEPTION
3839: WHEN OTHERS THEN
3840: IF P_DEBUG_MODE = 'Y' THEN
3844: x_msg_count := 1 ;
3845: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3846: -- Set the exception Message and the stack
3847: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Job'
3848: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3849: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3850: RAISE;
3851: END Update_Job;
3852:
3887:
3888: BEGIN
3889:
3890: -- Initialize the Error stack
3891: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Supervisor');
3892: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3893:
3894: --Set the values for the Resources Denorm Record Type
3895: l_resource_rec_old.person_id := p_person_id;
3921:
3922: END LOOP;--end FOR
3923:
3924: -- reset the Error stack
3925: PA_DEBUG.Reset_Err_Stack;
3926:
3927: EXCEPTION
3928: WHEN OTHERS THEN
3929: -- 4537865 : RESET x_msg_count and x_msg_data also
3930: x_msg_count := 1 ;
3931: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3932: -- Set the exception Message and the stack
3933: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Supervisor'
3934: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3935: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3936: raise;
3937: END Update_Supervisor;
3938:
3954: l_return_status VARCHAR2(1);
3955:
3956: BEGIN
3957: -- Initialize the Error stack
3958: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_PrimaryFlag');
3959: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3960:
3961: --Call Update_EndDate to end date the resource_ou record.
3962: -- Commented the end date change in PA as this is not required
3985: x_return_status := FND_API.G_RET_STS_ERROR ;
3986: END IF;
3987:
3988: -- reset the Error stack
3989: PA_DEBUG.Reset_Err_Stack;
3990:
3991: EXCEPTION
3992: WHEN OTHERS THEN
3993: -- 4537865 : RESET x_msg_count and x_msg_data also
3994: x_msg_count := 1 ;
3995: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3996: -- Set the exception Message and the stack
3997: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_PrimaryFlag'
3998: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3999: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4000: raise;
4001: END Update_PrimaryFlag;
4002:
4017: l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4018: BEGIN
4019:
4020: --Initialize the Error stack
4021: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Name');
4022: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4023:
4024: --Get resource_id for person_id
4025: l_resource_id := pa_resource_utils.get_resource_id(p_person_id);
4052: x_return_status := FND_API.G_RET_STS_ERROR ;
4053: END IF;
4054:
4055: --Reset the Error stack
4056: PA_DEBUG.Reset_Err_Stack;
4057:
4058: EXCEPTION
4059: WHEN OTHERS THEN
4060: -- 4537865 : RESET x_msg_count and x_msg_data also
4061: x_msg_count := 1 ;
4062: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4063: -- Set the exception Message and the stack
4064: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Name'
4065: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4066: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4067: raise;
4068: END Update_Name;
4069:
4240: ;
4241:
4242: BEGIN
4243: -- Initialize the Error stack
4244: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Delete_PA_Resource_Denorm');
4245: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4246:
4247: FOR rec IN res_denorm_recs LOOP
4248: --Delete the record
4252:
4253: END LOOP; --end FOR
4254:
4255: -- reset the Error stack
4256: PA_DEBUG.Reset_Err_Stack;
4257:
4258: EXCEPTION
4259:
4260: WHEN NO_DATA_FOUND THEN
4265: x_msg_count := 1 ;
4266: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4267: -- Set the exception Message and the stack
4268: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Delete_PA_Resource_Denorm'
4269: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4270: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4271: raise;
4272: END Delete_PA_Resource_Denorm;
4273: