[Home] [Help]
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:
307: v_return_status VARCHAR2(1);
308: v_dummy VARCHAR2(1):= 'N';
309: BEGIN
310: -- Initialize the Error stack
311: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.check_exp_OU');
312: -- Initialize the error status
313: x_return_status := FND_API.G_RET_STS_SUCCESS;
314:
315: If p_org_id is NOT NULL then
327: x_error_message_code := 'Exp Ou';
328: End if;
329: */
330: -- reset the Error stack
331: PA_DEBUG.Reset_Err_Stack;
332:
333: EXCEPTION
334:
335: WHEN NO_DATA_FOUND THEN
339:
340: WHEN OTHERS THEN
341: -- Set the exception Message and the stack
342: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.check_exp_OU'
343: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
344: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
345: -- 4537865 : RESET x_error_message_code also
346: x_error_message_code := SQLCODE ;
347:
374: v_msg_data VARCHAR2(2000);
375: v_msg_count NUMBER;
376: BEGIN
377: -- Initialize the Error stack
378: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_create_resoruce_denorm');
379: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
380: v_job_id_new := P_job_id_new;
381: v_job_level_new := P_job_level_new;
382: v_job_id_old := P_job_id_old;
413:
414:
415:
416: -- reset the Error stack
417: PA_DEBUG.Reset_Err_Stack;
418:
419: EXCEPTION
420:
421: WHEN OTHERS THEN
424: x_msg_data := SUBSTRB(SQLERRM ,1,240);
425:
426: -- Set the exception Message and the stack
427: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_API.call_create_resource_denorm'
428: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
429: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
430: raise;
431:
432:
452: l_job_level_old NUMBER;
453: l_job_level_new NUMBER;
454: l_job_id_old PER_JOBS.JOB_ID%type;
455: l_job_id_new PER_JOBS.JOB_ID%type;
456: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
457:
458: BEGIN
459: -- Initialize the Error stack
460: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
456: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
457:
458: BEGIN
459: -- Initialize the Error stack
460: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
461: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
462:
463: l_job_id_new := P_job_id_new;
464: l_job_level_new := P_job_level_new;
487: ,x_msg_count => x_msg_count
488: );
489:
490: -- reset the Error stack
491: PA_DEBUG.Reset_Err_Stack;
492:
493: EXCEPTION
494:
495: WHEN OTHERS THEN
498: x_msg_data := SUBSTRB(SQLERRM ,1,240);
499:
500: -- Set the exception Message and the stack
501: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_job_level_res_denorm'
502: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
503: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
504: raise;
505:
506: END update_job_level_res_denorm;
546: AND pjr.from_job_id = l_job_id
547: AND pjr.from_job_group_id = pjg.job_group_id;
548:
549: -- P_DEBUG_MODE varchar2(1); -- Bug 4352236
550: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
551:
552: BEGIN
553:
554: -- Initialize the Error stack
551:
552: BEGIN
553:
554: -- Initialize the Error stack
555: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_all_jobs');
556: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
557:
558: l_job_id := P_job_id;
559:
570: fetch get_job_ids into l_job_id;
571: Exit when get_job_ids%NOTFOUND;
572:
573: If l_job_id is NOT NULL then
574: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
575: IF (P_DEBUG_MODE ='Y') THEN
576: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
577: pa_debug.write_file('LOG',pa_debug.g_err_stage);
578: end if;
572:
573: If l_job_id is NOT NULL then
574: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
575: IF (P_DEBUG_MODE ='Y') THEN
576: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
577: pa_debug.write_file('LOG',pa_debug.g_err_stage);
578: end if;
579:
580: update_job_level_res_denorm
573: If l_job_id is NOT NULL then
574: -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
575: IF (P_DEBUG_MODE ='Y') THEN
576: pa_debug.g_err_stage := 'Log: Job Level change for job_id';
577: pa_debug.write_file('LOG',pa_debug.g_err_stage);
578: end if;
579:
580: update_job_level_res_denorm
581: ( P_job_id_old => l_job_id
591: END LOOP;
592: CLOSE get_job_ids;
593:
594: -- reset the Error stack
595: PA_DEBUG.Reset_Err_Stack;
596:
597: EXCEPTION
598:
599: WHEN OTHERS THEN
602: x_msg_data := SUBSTRB(SQLERRM ,1,240);
603:
604: -- Set the exception Message and the stack
605: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_all_jobs'
606: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
607: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
608: raise;
609:
610: END update_all_jobs;
629: l_job_id PER_JOBS.JOB_ID%type;
630: l_master_job_id PER_JOBS.JOB_ID%type;
631: l_PRJG_job_id PER_JOBS.JOB_ID%type;
632: l_condition VARCHAR2(10);
633: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
634:
635: BEGIN
636: -- Initialize the Error stack
637: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
633: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
634:
635: BEGIN
636: -- Initialize the Error stack
637: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
638: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
639:
640: IF P_DEBUG_MODE = 'Y' THEN
641: log_message('P_from_job_id_old = ' || P_from_job_id_old);
895:
896:
897:
898: -- reset the Error stack
899: PA_DEBUG.Reset_Err_Stack;
900:
901: EXCEPTION
902:
903: WHEN NO_DATA_FOUND THEN
909: x_msg_data := SUBSTRB(SQLERRM ,1,240);
910:
911: -- Set the exception Message and the stack
912: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pa_job_relation_job_id'
913: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
914: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
915: raise;
916:
917:
934: l_master_job_id PER_JOBS.JOB_ID%type;
935: l_job_level_old pa_resources_denorm.resource_job_level%type;
936: l_job_level_new pa_resources_denorm.resource_job_level%type;
937: l_job_group_id per_job_groups.job_group_id%type;
938: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
939:
940: -- Cursor to get the associated master job id of the PRJG job id
941: CURSOR get_master_job (
942: l_job_id per_jobs.job_id%type
955:
956: BEGIN
957:
958: -- Initialize the Error stack
959: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.perform_job_updates');
960: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
961: IF P_DEBUG_MODE = 'Y' THEN
962: log_message('**** Performing Job Level Updates ****');
963: END IF;
1056: l_job_level_old pa_resources_denorm.resource_job_level%type;
1057: l_job_level_new pa_resources_denorm.resource_job_level%type;
1058: l_job_group_id per_job_groups.job_group_id%type;
1059: l_isPRJG VARCHAR2(1);
1060: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1061:
1062: BEGIN
1063:
1064: -- Initialize the Error stack
1061:
1062: BEGIN
1063:
1064: -- Initialize the Error stack
1065: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_dff');
1066: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1067:
1068: l_job_id := P_job_id;
1069: l_job_level_old := P_job_level_old;
1208: );
1209: BEGIN
1210:
1211: -- Initialize the Error stack
1212: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_valid_grades_job_id');
1213: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1214:
1215: -- if the grade id changes then update all the jobs which are affected and
1216: -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1238: END LOOP;
1239: CLOSE get_job_ids;
1240:
1241: -- reset the Error stack
1242: PA_DEBUG.Reset_Err_Stack;
1243:
1244: EXCEPTION
1245:
1246: WHEN NO_DATA_FOUND THEN
1251: x_msg_count := 1 ;
1252: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1253: -- Set the exception Message and the stack
1254: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_valid_grades_job_id'
1255: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1256: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1257: raise;
1258:
1259:
1321: BEGIN
1322:
1323:
1324: -- Initialize the Error stack
1325: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_grades_job_id');
1326: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1327:
1328: -- if the sequence is changes then update all the jobs which are affected due to
1329: -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1350: END LOOP;
1351: CLOSE get_job_ids;
1352:
1353: -- reset the Error stack
1354: PA_DEBUG.Reset_Err_Stack;
1355: EXCEPTION
1356:
1357: WHEN NO_DATA_FOUND THEN
1358: NULL;
1362: x_msg_count := 1 ;
1363: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1364: -- Set the exception Message and the stack
1365: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_grades_job_id'
1366: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1367: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1368: raise;
1369:
1370:
1410: v_msg_count NUMBER;
1411:
1412: BEGIN
1413: -- Initialize the Error stack
1414: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_job_levels');
1415: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1416:
1417: -- Code for if job mapping has changed
1418:
1433: );
1434: End if;
1435:
1436: -- reset the Error stack
1437: PA_DEBUG.Reset_Err_Stack;
1438:
1439: EXCEPTION
1440:
1441: WHEN NO_DATA_FOUND THEN
1447: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1448:
1449: -- Set the exception Message and the stack
1450: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_job_levels'
1451: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1452: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1453: raise;
1454:
1455: END update_job_levels;
1504: --dbms_output.put_line('Calling Update_OU_resource');
1505: --dbms_output.put_line('End date for OU:' || p_default_OU_new || 'end date:' || P_end_date_new);
1506:
1507: -- Initialize the Error stack
1508: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_OU_resource');
1509: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1510:
1511: FOR rec IN res_denorm_recs LOOP
1512:
1537:
1538: End if;
1539:
1540: -- reset the Error stack
1541: PA_DEBUG.Reset_Err_Stack;
1542:
1543: EXCEPTION
1544:
1545: WHEN NO_DATA_FOUND THEN
1549: -- 4537865 : RESET x_msg_count and x_msg_data also
1550: x_msg_count := 1 ;
1551: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1552: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_OU_resource'
1553: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1554: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1555: raise;
1556: END Update_OU_resource;
1557:
1621:
1622: BEGIN
1623:
1624: -- Initialize the Error stack
1625: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Default_OU_Change');
1626: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1627: -- for each resource found for the default ou changes update
1628: -- pa resource ou entity with new default OU
1629:
1647: ,x_return_status => x_return_status
1648: ,x_msg_data => x_msg_data
1649: ,x_msg_count => x_msg_count
1650: ) ;
1651: PA_DEBUG.Reset_Err_Stack;
1652: Return;
1653: End if;
1654:
1655: pa_hr_update_api.check_exp_OU
1850:
1851: End if;
1852:
1853: -- reset the Error stack
1854: PA_DEBUG.Reset_Err_Stack;
1855:
1856: EXCEPTION
1857: WHEN FND_API.G_EXC_ERROR THEN
1858: x_return_status := FND_API.G_RET_STS_ERROR;
1861: x_msg_count := 1 ;
1862: x_msg_data := SUBSTRB(SQLERRM ,1,240);
1863: -- Set the exception Message and the stack
1864: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Default_OU_Change'
1865: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1866: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1867: raise;
1868:
1869: END Default_OU_Change;
1887: l_job_level NUMBER;
1888: l_proj_job_group_new NUMBER;
1889: l_proj_job_group_old NUMBER;
1890: l_job_group_id NUMBER;
1891: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1892:
1893: CURSOR get_new_job_ids IS
1894: SELECT job_id
1895: FROM per_jobs
1904:
1905: BEGIN
1906:
1907: -- Initialize the Error stack
1908: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Proj_Res_Job_Group_Change');
1909: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1910:
1911: l_proj_job_group_old := TO_NUMBER(p_proj_job_group_old);
1912: l_proj_job_group_new := TO_NUMBER(p_proj_job_group_new);
2033: END IF;
2034:
2035:
2036: -- reset the Error stack
2037: PA_DEBUG.Reset_Err_Stack;
2038:
2039: EXCEPTION
2040: WHEN FND_API.G_EXC_ERROR THEN
2041: x_return_status := FND_API.G_RET_STS_ERROR;
2044: x_msg_count := 1 ;
2045: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2046: -- Set the exception Message and the stack
2047: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Proj_Res_Job_Group_Change'
2048: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2049: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2050: raise;
2051:
2052: END Proj_Res_Job_Group_Change;
2087: ORDER BY ind.person_id;
2088:
2089: BEGIN
2090: -- Initialize the Error stack
2091: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pull_resources');
2092: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2093:
2094: open get_all_resource(p_organization_id);
2095: LOOP
2140: --set the final return status to SUCCESS after loop
2141: x_return_status := FND_API.G_RET_STS_SUCCESS;
2142:
2143: -- reset the Error stack
2144: PA_DEBUG.Reset_Err_Stack;
2145:
2146: EXCEPTION
2147: WHEN OTHERS THEN
2148: -- 4537865 : RESET x_msg_count and x_msg_data also
2149: x_msg_count := 1 ;
2150: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2151: -- Set the exception Message and the stack
2152: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pull_resources'
2153: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2154:
2155: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2156: raise;
2157:
2291: AND rownum = 1 );
2292:
2293: BEGIN
2294: -- Initialize the Error stack
2295: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.make_resource_inactive');
2296: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2297:
2298: --dbms_output.put_line('Inside make resource inactive');
2299: --dbms_output.put_line('Default OU:' || p_default_ou);
2360:
2361: END IF;
2362:
2363: -- reset the Error stack
2364: PA_DEBUG.Reset_Err_Stack;
2365: EXCEPTION
2366: WHEN OTHERS THEN
2367: -- 4537865 : RESET x_msg_count and x_msg_data also
2368: x_msg_count := 1 ;
2368: x_msg_count := 1 ;
2369: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2370: -- Set the exception Message and the stack
2371: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.make_resource_inactive'
2372: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2373: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2374: raise;
2375:
2376:
2481:
2482:
2483: BEGIN
2484: -- Initialize the Error stack
2485: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_forcast_api');
2486: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2487:
2488: If (P_table_name = 'PER_JOB_EXTRA_INFO' or
2489: P_table_name = 'PER_VALID_GRADES' or
2568: --set the final return status to SUCCESS after loop
2569: x_return_status := FND_API.G_RET_STS_SUCCESS;
2570:
2571: -- reset the Error stack
2572: PA_DEBUG.Reset_Err_Stack;
2573: EXCEPTION
2574: WHEN FND_API.G_EXC_ERROR THEN
2575: x_return_status := FND_API.G_RET_STS_ERROR;
2576: WHEN OTHERS THEN
2578: x_msg_count := 1 ;
2579: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2580: -- Set the exception Message and the stack
2581: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_forcast_api'
2582: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2584: raise;
2585:
2586:
2620:
2621: BEGIN
2622:
2623: -- Initialize the Error stack
2624: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_billable_resoruce_denorm');
2625: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2626: v_job_id_new := P_job_id_new;
2627: v_job_id_old := P_job_id_old;
2628:
2685:
2686:
2687: End if;
2688: -- reset the Error stack
2689: PA_DEBUG.Reset_Err_Stack;
2690:
2691: EXCEPTION
2692:
2693: WHEN OTHERS THEN
2695: x_msg_count := 1 ;
2696: x_msg_data := SUBSTRB(SQLERRM ,1,240);
2697: -- Set the exception Message and the stack
2698: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_billable_resoruce_denorm'
2699: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2700: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2701: raise;
2702:
2703: END call_billable_resoruce_denorm;
2735: l_pull_res_flag VARCHAR2(1) := 'N';
2736: l_end_date_res_flag VARCHAR2(1) := 'N';
2737: l_prv_person_id NUMBER;
2738: l_resource_id NUMBER;
2739: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2740:
2741: /*
2742: CURSOR all_job_res_recs IS
2743: SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
2763: WHERE res.job_id = p_job_id;
2764:
2765: BEGIN
2766: -- Initialize the Error stack
2767: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_job_extra_billability');
2768: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2769:
2770: IF P_DEBUG_MODE = 'Y' THEN
2771: log_message('beginning of per_job_extra_billability, P_calling_mode:'|| P_calling_mode || ', job_id: '||P_job_id);
3000: COMMIT;
3001: END IF;
3002:
3003: -- reset the Error stack
3004: PA_DEBUG.Reset_Err_Stack;
3005: IF P_DEBUG_MODE = 'Y' THEN
3006: log_message('after Reset_Err_Stack');
3007: END IF;
3008:
3020: x_msg_count := 1 ;
3021: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3022: -- Set the exception Message and the stack
3023: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_job_extra_billability'
3024: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3025: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3026: RAISE;
3027: END per_job_extra_billability;
3028:
3044:
3045: l_resource_id NUMBER;
3046: l_status_code VARCHAR2(30);
3047: l_rec_ver_num NUMBER;
3048: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3049:
3050: CURSOR get_nominations(l_resource_id IN NUMBER) IS
3051: select cand.candidate_id, cand.record_version_number
3052: from pa_candidates cand,
3071:
3072: BEGIN
3073:
3074: -- Initialize the Error stack
3075: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.withdraw_cand_nominations');
3076: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3077:
3078: l_resource_id := PA_RESOURCE_UTILS.get_resource_id(p_person_id);
3079:
3158: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3159: END IF;
3160:
3161: -- reset the Error stack
3162: PA_DEBUG.Reset_Err_Stack;
3163:
3164: EXCEPTION
3165:
3166: WHEN OTHERS THEN
3168: x_msg_count := 1 ;
3169: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3170: -- Set the exception Message and the stack
3171: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.withdraw_cand_nominations'
3172: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3174: raise;
3175: END withdraw_cand_nominations;
3176:
3241: --dbms_output.put_line('Person Id:' || p_person_id);
3242: --dbms_output.put_line('New End Date:' || p_new_end_date);
3243:
3244: -- Initialize the Error stack
3245: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_EndDate');
3246: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3247:
3248: l_count := 0; -- bug 7147575 : None records in pa_resources_denorm so far.
3249:
3425: END IF ; --((l_invol_term = 'N') AND (l_return_status = FND_API.G_RET_STS_SUCCESS)) bug 5683340
3426:
3427: END IF ; -- bug 7147575 : IF (l_count > 0 )
3428:
3429: PA_DEBUG.Reset_Err_Stack;
3430:
3431: EXCEPTION
3432:
3433: WHEN NO_DATA_FOUND THEN
3438: x_msg_count := 1 ;
3439: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3440: -- Set the exception Message and the stack
3441: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_EndDate'
3442: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3443: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3444: raise;
3445: END Update_EndDate;
3446:
3491:
3492: --dbms_output.put_line('Inside Update Org');
3493:
3494: -- Initialize the Error stack
3495: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Org');
3496:
3497: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3498:
3499: /* Check if new organization belongs to an expenditure
3638: END IF;
3639:
3640:
3641: -- reset the Error stack
3642: PA_DEBUG.Reset_Err_Stack;
3643:
3644: EXCEPTION
3645: WHEN NO_DATA_FOUND THEN
3646: X_RETURN_STATUS := fnd_api.g_ret_sts_error;
3650: x_msg_count := 1 ;
3651: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3652: -- Set the exception Message and the stack
3653: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Org'
3654: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3655: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3656: raise;
3657: END Update_Org;
3658:
3789: l_old_job_utilizable pa_resources_denorm.utilization_flag%type;
3790: l_new_job_utilizable pa_resources_denorm.utilization_flag%type;
3791: l_old_job_schedulable pa_resources_denorm.schedulable_flag%type;
3792: l_new_job_schedulable pa_resources_denorm.schedulable_flag%type;
3793: P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3794:
3795: -- Cursor to get all denormalized resource records
3796: -- for this HR assignment
3797: CURSOR res_denorm_recs IS
3824: --Bug 9047714 End
3825:
3826: BEGIN
3827: -- Initialize the Error stack
3828: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Job');
3829: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3830: IF P_DEBUG_MODE = 'Y' THEN
3831: log_message('beginning of Update_Job');
3832: END IF;
3968: x_return_status := FND_API.G_RET_STS_ERROR ;
3969: END IF;
3970: END IF; --Bug 7690398
3971: -- reset the Error stack
3972: PA_DEBUG.Reset_Err_Stack;
3973:
3974: EXCEPTION
3975: WHEN OTHERS THEN
3976: IF P_DEBUG_MODE = 'Y' THEN
3980: x_msg_count := 1 ;
3981: x_msg_data := SUBSTRB(SQLERRM ,1,240);
3982: -- Set the exception Message and the stack
3983: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Job'
3984: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3985: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3986: RAISE;
3987: END Update_Job;
3988:
4023:
4024: BEGIN
4025:
4026: -- Initialize the Error stack
4027: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Supervisor');
4028: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4029:
4030: --Set the values for the Resources Denorm Record Type
4031: l_resource_rec_old.person_id := p_person_id;
4057:
4058: END LOOP;--end FOR
4059:
4060: -- reset the Error stack
4061: PA_DEBUG.Reset_Err_Stack;
4062:
4063: EXCEPTION
4064: WHEN OTHERS THEN
4065: -- 4537865 : RESET x_msg_count and x_msg_data also
4066: x_msg_count := 1 ;
4067: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4068: -- Set the exception Message and the stack
4069: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Supervisor'
4070: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4071: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4072: raise;
4073: END Update_Supervisor;
4074:
4090: l_return_status VARCHAR2(1);
4091:
4092: BEGIN
4093: -- Initialize the Error stack
4094: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_PrimaryFlag');
4095: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4096:
4097: --Call Update_EndDate to end date the resource_ou record.
4098: -- Commented the end date change in PA as this is not required
4124: x_return_status := FND_API.G_RET_STS_ERROR ;
4125: END IF;
4126:
4127: -- reset the Error stack
4128: PA_DEBUG.Reset_Err_Stack;
4129:
4130: EXCEPTION
4131: WHEN OTHERS THEN
4132: -- 4537865 : RESET x_msg_count and x_msg_data also
4133: x_msg_count := 1 ;
4134: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4135: -- Set the exception Message and the stack
4136: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_PrimaryFlag'
4137: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4138: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4139: raise;
4140: END Update_PrimaryFlag;
4141:
4156: l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4157: BEGIN
4158:
4159: --Initialize the Error stack
4160: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Name');
4161: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4162:
4163: --Get resource_id for person_id
4164: l_resource_id := pa_resource_utils.get_resource_id(p_person_id);
4191: x_return_status := FND_API.G_RET_STS_ERROR ;
4192: END IF;
4193:
4194: --Reset the Error stack
4195: PA_DEBUG.Reset_Err_Stack;
4196:
4197: EXCEPTION
4198: WHEN OTHERS THEN
4199: -- 4537865 : RESET x_msg_count and x_msg_data also
4200: x_msg_count := 1 ;
4201: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4202: -- Set the exception Message and the stack
4203: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Name'
4204: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4205: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4206: raise;
4207: END Update_Name;
4208:
4379: ;
4380:
4381: BEGIN
4382: -- Initialize the Error stack
4383: PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Delete_PA_Resource_Denorm');
4384: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4385:
4386: FOR rec IN res_denorm_recs LOOP
4387: --Delete the record
4391:
4392: END LOOP; --end FOR
4393:
4394: -- reset the Error stack
4395: PA_DEBUG.Reset_Err_Stack;
4396:
4397: EXCEPTION
4398:
4399: WHEN NO_DATA_FOUND THEN
4404: x_msg_count := 1 ;
4405: x_msg_data := SUBSTRB(SQLERRM ,1,240);
4406: -- Set the exception Message and the stack
4407: FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Delete_PA_Resource_Denorm'
4408: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4409: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4410: raise;
4411: END Delete_PA_Resource_Denorm;
4412: