[Home] [Help]
36: --
37: begin
38: if g_debug then
39: l_proc := g_package||'create_eliminated_rec';
40: hr_utility.set_location('Entering:'|| l_proc, 5);
41: end if;
42: --
43: open c3;
44: fetch c3 into l_ovn, l_position_definition_id, l_name;
64: ,p_datetrack_mode =>'UPDATE'
65: );
66: end if;
67: if g_debug then
68: hr_utility.set_location('Leaving:'|| l_proc, 30);
69: end if;
70: end;
71: -- Reset FULL_HR to True
72: procedure reset_hr_installation is
84: begin
85: --
86: if g_debug then
87: l_proc := g_package||'get_hr_installation';
88: hr_utility.set_location('Entering:'|| l_proc, 5);
89: end if;
90: --
91: -- Find if full hr installation or shared hr installation
92: --
99: FULL_HR := TRUE;
100: Elsif l_status = 'S' then
101: FULL_HR := FALSE;
102: Else
103: hr_utility.set_message(801,'HR_NULL_INSTALLATION_STATUS');
104: hr_utility.raise_error;
105: End if;
106: --
107: -- For SHARED HR testing purposes
100: Elsif l_status = 'S' then
101: FULL_HR := FALSE;
102: Else
103: hr_utility.set_message(801,'HR_NULL_INSTALLATION_STATUS');
104: hr_utility.raise_error;
105: End if;
106: --
107: -- For SHARED HR testing purposes
108: -- FULL_HR := FALSE;
107: -- For SHARED HR testing purposes
108: -- FULL_HR := FALSE;
109: --
110: if g_debug then
111: hr_utility.set_location('Leaving:'|| l_proc, 30);
112: end if;
113: end;
114: --
115: -- Function to delete unused per_position_definitions
337: where position_definition_id = l_position_definition_id;
338: --
339: begin
340: --
341: g_debug := hr_utility.debug_enabled;
342: if g_debug then
343: l_proc := g_package||'create_position';
344: hr_utility.set_location('Entering:'|| l_proc, 5);
345: end if;
340: --
341: g_debug := hr_utility.debug_enabled;
342: if g_debug then
343: l_proc := g_package||'create_position';
344: hr_utility.set_location('Entering:'|| l_proc, 5);
345: end if;
346: --
347: -- Issue a savepoint
348: --
352: --
353: -- Get business_group_id using job.
354: --
355: if g_debug then
356: hr_utility.set_location(l_proc, 10);
357: end if;
358: --
359: open csr_job_bg;
360: fetch csr_job_bg
361: into l_business_group_id;
362: --
363: if csr_job_bg%notfound then
364: close csr_job_bg;
365: hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
366: hr_utility.raise_error;
367: else
368: close csr_job_bg;
369: end if;
362: --
363: if csr_job_bg%notfound then
364: close csr_job_bg;
365: hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
366: hr_utility.raise_error;
367: else
368: close csr_job_bg;
369: end if;
370: --
368: close csr_job_bg;
369: end if;
370: --
371: if g_debug then
372: hr_utility.set_location(l_proc, 15);
373: end if;
374: --
375: /*
376: obsoleted code [Date Tracking] vmolasi
382: l_date_effective := trunc(p_date_effective) ;
383: l_date_end := trunc(p_date_end);
384: --
385: if g_debug then
386: hr_utility.set_location(l_proc || ' l_name ' || l_name, 200);
387: hr_utility.set_location(l_proc || ' l_pos_def_id '|| l_position_definition_id, 201);
388: end if;
389: --
390: -- 2242339 get segment values if p_position_definition_id entered with a value
383: l_date_end := trunc(p_date_end);
384: --
385: if g_debug then
386: hr_utility.set_location(l_proc || ' l_name ' || l_name, 200);
387: hr_utility.set_location(l_proc || ' l_pos_def_id '|| l_position_definition_id, 201);
388: end if;
389: --
390: -- 2242339 get segment values if p_position_definition_id entered with a value
391: --
393: --
394: then
395: --
396: if g_debug then
397: hr_utility.set_location(l_proc, 15);
398: end if;
399: --
400: --set indicator to show p_position_definition_id did not enter program null
401: --
522: ,p_object_version_number => p_object_version_number
523: ,p_validate => p_validate
524: );
525: if g_debug then
526: hr_utility.set_location(l_proc, 25);
527: end if;
528: --
529: -- Create Eliminated Record is date_end is not null
530: --
534: p_object_version_number => p_object_version_number,
535: p_effective_date => p_date_end);
536: end if;
537: if g_debug then
538: hr_utility.set_location(l_proc, 30);
539: end if;
540: --
541: -- get the updated Object version number of the per_all_positions table
542: -- which is returned to the user as ovn of created row.
543: --
544: p_object_version_number := per_refresh_position.get_position_ovn;
545: --
546: if g_debug then
547: hr_utility.set_location('per_all_ovn is '||p_object_version_number||l_proc,9);
548: end if;
549: --
550: -- When in validation only mode raise the Validate_Enabled exception
551: --
560: --
561: reset_hr_installation;
562: --
563: if g_debug then
564: hr_utility.set_location(' Leaving:'||l_proc, 30);
565: end if;
566: exception
567: when hr_api.validate_enabled then
568: reset_hr_installation;
617: l_object_version_number number;
618: begin
619: if g_debug then
620: l_proc := g_package||'get_dt_position_ovn' ;
621: hr_utility.set_location(' Entering:'||l_proc, 5);
622: end if;
623: open c1;
624: fetch c1 into l_object_version_number;
625: close c1;
625: close c1;
626: return l_object_version_number;
627: if c1%notfound then
628: close c1;
629: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
630: hr_utility.set_message_token('Function', l_proc);
631: hr_utility.set_message_token('STEP','5');
632: hr_utility.raise_error;
633: end if;
626: return l_object_version_number;
627: if c1%notfound then
628: close c1;
629: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
630: hr_utility.set_message_token('Function', l_proc);
631: hr_utility.set_message_token('STEP','5');
632: hr_utility.raise_error;
633: end if;
634: if g_debug then
627: if c1%notfound then
628: close c1;
629: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
630: hr_utility.set_message_token('Function', l_proc);
631: hr_utility.set_message_token('STEP','5');
632: hr_utility.raise_error;
633: end if;
634: if g_debug then
635: hr_utility.set_location(' Leaving:'||l_proc, 30);
628: close c1;
629: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
630: hr_utility.set_message_token('Function', l_proc);
631: hr_utility.set_message_token('STEP','5');
632: hr_utility.raise_error;
633: end if;
634: if g_debug then
635: hr_utility.set_location(' Leaving:'||l_proc, 30);
636: end if;
631: hr_utility.set_message_token('STEP','5');
632: hr_utility.raise_error;
633: end if;
634: if g_debug then
635: hr_utility.set_location(' Leaving:'||l_proc, 30);
636: end if;
637: end get_dt_position_ovn;
638: --
639: --
676: l_proc varchar2(72) ;
677: begin
678: if g_debug then
679: l_proc := g_package||'delete_eliminated_rec' ;
680: hr_utility.set_location(' Entering:'||l_proc, 5);
681: end if;
682: -- Delete Eliminated Record
683: open c2;
684: fetch c2 into r2;
701: end if;
702: close c2;
703: --
704: if g_debug then
705: hr_utility.set_location(' Leaving:'||l_proc, 30);
706: end if;
707: --
708: end;
709: --
897: where position_definition_id = l_position_definition_id;
898: --
899: begin
900: --
901: g_debug := hr_utility.debug_enabled;
902: if g_debug then
903: l_proc := g_package||'update_position';
904: hr_utility.set_location('Entering:'|| l_proc, 5);
905: end if;
900: --
901: g_debug := hr_utility.debug_enabled;
902: if g_debug then
903: l_proc := g_package||'update_position';
904: hr_utility.set_location('Entering:'|| l_proc, 5);
905: end if;
906: --
907: -- Issue a savepoint
908: --
921: (p_position_id => p_position_id
922: ,p_object_version_number => p_object_version_number);
923: --
924: if g_debug then
925: hr_utility.set_location(l_proc, 15);
926: end if;
927: --
928: if not l_api_updating
929: then
927: --
928: if not l_api_updating
929: then
930: if g_debug then
931: hr_utility.set_location(l_proc, 20);
932: end if;
933: --
934: -- As this an updating API, the position should already exist.
935: --
932: end if;
933: --
934: -- As this an updating API, the position should already exist.
935: --
936: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
937: hr_utility.raise_error;
938: else
939: if l_position_definition_id is null
940: then
933: --
934: -- As this an updating API, the position should already exist.
935: --
936: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
937: hr_utility.raise_error;
938: else
939: if l_position_definition_id is null
940: then
941: l_null_ind := 0;
943: l_position_definition_id
944: := per_pos_shd.g_old_rec.position_definition_id;
945: --
946: if g_debug then
947: hr_utility.set_location(l_proc, 25);
948: end if;
949: --
950: else
951: -- 2242339
987: l_segment29,
988: l_segment30;
989: close c_segments;
990: if g_debug then
991: hr_utility.set_location(l_proc, 27);
992: end if;
993: --
994: end if;
995: end if;
998: fetch csr_idsel
999: into l_flex_num;
1000: if csr_idsel%NOTFOUND then
1001: close csr_idsel;
1002: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1003: hr_utility.set_message_token('PROCEDURE', l_proc);
1004: hr_utility.set_message_token('STEP','5');
1005: hr_utility.raise_error;
1006: end if;
999: into l_flex_num;
1000: if csr_idsel%NOTFOUND then
1001: close csr_idsel;
1002: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1003: hr_utility.set_message_token('PROCEDURE', l_proc);
1004: hr_utility.set_message_token('STEP','5');
1005: hr_utility.raise_error;
1006: end if;
1007: close csr_idsel;
1000: if csr_idsel%NOTFOUND then
1001: close csr_idsel;
1002: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1003: hr_utility.set_message_token('PROCEDURE', l_proc);
1004: hr_utility.set_message_token('STEP','5');
1005: hr_utility.raise_error;
1006: end if;
1007: close csr_idsel;
1008: --
1001: close csr_idsel;
1002: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1003: hr_utility.set_message_token('PROCEDURE', l_proc);
1004: hr_utility.set_message_token('STEP','5');
1005: hr_utility.raise_error;
1006: end if;
1007: close csr_idsel;
1008: --
1009: -- Code hr_kflex_utility.upd_or_sel_keyflex_comb etc has been Obsoleted due to
1019: close get_curr_esd;
1020: --
1021: -- As this an updating API, the position should already exist.
1022: --
1023: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1024: hr_utility.raise_error;
1025: else
1026: close get_curr_esd;
1027: end if;
1020: --
1021: -- As this an updating API, the position should already exist.
1022: --
1023: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1024: hr_utility.raise_error;
1025: else
1026: close get_curr_esd;
1027: end if;
1028: if g_debug then
1025: else
1026: close get_curr_esd;
1027: end if;
1028: if g_debug then
1029: hr_utility.set_location(l_proc, 30);
1030: end if;
1031: --
1032: -- Delete Eliminated Rec
1033: delete_eliminated_rec(p_position_id);
1031: --
1032: -- Delete Eliminated Rec
1033: delete_eliminated_rec(p_position_id);
1034: if g_debug then
1035: hr_utility.set_location(l_proc, 35);
1036: end if;
1037: --
1038: -- ovn of date tracked table is fetched for passing
1039: --
1039: --
1040: l_object_version_number := get_dt_position_ovn(p_position_id,l_effective_date);
1041: --
1042: if g_debug then
1043: hr_utility.set_location('hr_all_ovn is '||l_object_version_number||l_proc,5);
1044: end if;
1045: --
1046: -- fnd_message.set_name('PQH','DATE-'||l_object_version_number);
1047: -- fnd_message.raise_error;
1046: -- fnd_message.set_name('PQH','DATE-'||l_object_version_number);
1047: -- fnd_message.raise_error;
1048: --
1049: if g_debug then
1050: hr_utility.set_location(l_proc || 'l_pos_def_id '|| l_position_definition_id, 401);
1051: hr_utility.set_location(l_proc || 'p_pos_def_id '|| p_position_definition_id, 402);
1052: hr_utility.set_location(l_proc || 'l_name '|| l_name, 403);
1053: hr_utility.set_location(l_proc || 'p_name '|| p_name, 404);
1054: end if;
1047: -- fnd_message.raise_error;
1048: --
1049: if g_debug then
1050: hr_utility.set_location(l_proc || 'l_pos_def_id '|| l_position_definition_id, 401);
1051: hr_utility.set_location(l_proc || 'p_pos_def_id '|| p_position_definition_id, 402);
1052: hr_utility.set_location(l_proc || 'l_name '|| l_name, 403);
1053: hr_utility.set_location(l_proc || 'p_name '|| p_name, 404);
1054: end if;
1055: l_effective_date := greatest(l_effective_date, l_date_effective);
1048: --
1049: if g_debug then
1050: hr_utility.set_location(l_proc || 'l_pos_def_id '|| l_position_definition_id, 401);
1051: hr_utility.set_location(l_proc || 'p_pos_def_id '|| p_position_definition_id, 402);
1052: hr_utility.set_location(l_proc || 'l_name '|| l_name, 403);
1053: hr_utility.set_location(l_proc || 'p_name '|| p_name, 404);
1054: end if;
1055: l_effective_date := greatest(l_effective_date, l_date_effective);
1056: l_position_definition_id := p_position_definition_id;
1049: if g_debug then
1050: hr_utility.set_location(l_proc || 'l_pos_def_id '|| l_position_definition_id, 401);
1051: hr_utility.set_location(l_proc || 'p_pos_def_id '|| p_position_definition_id, 402);
1052: hr_utility.set_location(l_proc || 'l_name '|| l_name, 403);
1053: hr_utility.set_location(l_proc || 'p_name '|| p_name, 404);
1054: end if;
1055: l_effective_date := greatest(l_effective_date, l_date_effective);
1056: l_position_definition_id := p_position_definition_id;
1057: l_name := p_name;
1056: l_position_definition_id := p_position_definition_id;
1057: l_name := p_name;
1058: --
1059: if g_debug then
1060: hr_utility.set_location(l_proc, 40);
1061: end if;
1062: --
1063: hr_position_api.update_position
1064: (p_position_id => p_position_id
1140: ,p_validate => p_validate
1141: );
1142: --
1143: if g_debug then
1144: hr_utility.set_location(l_proc, 45);
1145: end if;
1146: -- Create Eliminated Record
1147: -- fnd_message.set_name('PQH','DATE-'||l_object_version_number);
1148: -- fnd_message.raise_error;
1157: p_object_version_number => l_object_version_number,
1158: p_effective_date => l_date_end+1);
1159: end if;
1160: if g_debug then
1161: hr_utility.set_location(l_proc, 50);
1162: end if;
1163: --
1164: -- get the updated Object version number of the per_all_positions table which
1165: -- is to be returned to the user as ovn of updated row.
1166: --
1167: p_object_version_number := per_refresh_position.get_position_ovn;
1168: --
1169: if g_debug then
1170: hr_utility.set_location('per_all_ovn is '||p_object_version_number||l_proc,9);
1171: end if;
1172: --
1173: --
1174: -- When in validation only mode raise the Validate_Enabled exception
1187: --
1188: reset_hr_installation;
1189: --
1190: if g_debug then
1191: hr_utility.set_location(' Leaving:'||l_proc, 11);
1192: end if;
1193: exception
1194: when hr_api.validate_enabled then
1195: --
1249: l_esd date;
1250: begin
1251: if g_debug then
1252: l_proc := g_package||'get_dt_position_esd' ;
1253: hr_utility.set_location(' Entering:'||l_proc, 5);
1254: end if;
1255: open c1;
1256: fetch c1 into l_esd;
1257: close c1;
1257: close c1;
1258: return l_esd;
1259: if c1%notfound then
1260: close c1;
1261: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1262: hr_utility.set_message_token('Function', l_proc);
1263: hr_utility.set_message_token('STEP','5');
1264: hr_utility.raise_error;
1265: end if;
1258: return l_esd;
1259: if c1%notfound then
1260: close c1;
1261: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1262: hr_utility.set_message_token('Function', l_proc);
1263: hr_utility.set_message_token('STEP','5');
1264: hr_utility.raise_error;
1265: end if;
1266: if g_debug then
1259: if c1%notfound then
1260: close c1;
1261: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1262: hr_utility.set_message_token('Function', l_proc);
1263: hr_utility.set_message_token('STEP','5');
1264: hr_utility.raise_error;
1265: end if;
1266: if g_debug then
1267: hr_utility.set_location(' Leaving:'||l_proc, 30);
1260: close c1;
1261: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1262: hr_utility.set_message_token('Function', l_proc);
1263: hr_utility.set_message_token('STEP','5');
1264: hr_utility.raise_error;
1265: end if;
1266: if g_debug then
1267: hr_utility.set_location(' Leaving:'||l_proc, 30);
1268: end if;
1263: hr_utility.set_message_token('STEP','5');
1264: hr_utility.raise_error;
1265: end if;
1266: if g_debug then
1267: hr_utility.set_location(' Leaving:'||l_proc, 30);
1268: end if;
1269: end get_dt_position_esd;
1270: --
1271: procedure delete_position(
1283: l_industry varchar2(1);
1284: --
1285: begin
1286: --
1287: g_debug := hr_utility.debug_enabled;
1288: if g_debug then
1289: l_proc := g_package||'delete_position';
1290: hr_utility.set_location('Entering:'|| l_proc, 5);
1291: end if;
1286: --
1287: g_debug := hr_utility.debug_enabled;
1288: if g_debug then
1289: l_proc := g_package||'delete_position';
1290: hr_utility.set_location('Entering:'|| l_proc, 5);
1291: end if;
1292: --
1293: -- Issue a savepoint
1294: --
1318: --
1319: reset_hr_installation;
1320: --
1321: if g_debug then
1322: hr_utility.set_location('Exiting:'|| l_proc, 30);
1323: end if;
1324: --
1325: exception
1326: when others then
1351: --
1352: begin
1353: --
1354:
1355: g_debug := hr_utility.debug_enabled;
1356: if g_debug then
1357: l_proc := g_package||'lck';
1358: hr_utility.set_location('Entering:'|| l_proc, 10);
1359: end if;
1354:
1355: g_debug := hr_utility.debug_enabled;
1356: if g_debug then
1357: l_proc := g_package||'lck';
1358: hr_utility.set_location('Entering:'|| l_proc, 10);
1359: end if;
1360: --
1361: --
1362: -- esd of date tracked table is fetched for passing
1378: ,p_datetrack_mode => l_datetrack_mode
1379: );
1380: --
1381: if g_debug then
1382: hr_utility.set_location(' Leaving:'||l_proc, 70);
1383: end if;
1384: --
1385:
1386: end;
1467: --
1468: begin
1469: if g_debug then
1470: l_proc := g_package||'maintain_valid_grades';
1471: hr_utility.set_location('Entering:'|| l_proc, 5);
1472: end if;
1473: --
1474: IF p_maintenance_mode = 'DATE_EFFECTIVE' THEN
1475: --
1483: ,p_argument => 'date_effective'
1484: ,p_argument_value => p_date_effective);
1485: --
1486: if g_debug then
1487: hr_utility.set_location(l_proc, 10);
1488: end if;
1489: --
1490: FOR c_vgr_rec IN csr_valid_grades LOOP
1491: --
1494: -- later than that new Date Effective or is null, then update that
1495: -- valid grade's Date From to that new Date Effective.
1496: --
1497: if g_debug then
1498: hr_utility.set_location(l_proc, 15);
1499: end if;
1500: if (c_vgr_rec.date_from < p_date_effective and
1501: nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_effective ) then
1502: --
1500: if (c_vgr_rec.date_from < p_date_effective and
1501: nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_effective ) then
1502: --
1503: if g_debug then
1504: hr_utility.set_location(l_proc, 20);
1505: end if;
1506: --
1507: per_vgr_upd.upd
1508: (p_valid_grade_id => c_vgr_rec.valid_grade_id
1520: --
1521: elsif (c_vgr_rec.date_to < p_date_effective) then
1522: --
1523: if g_debug then
1524: hr_utility.set_location(l_proc, 25);
1525: end if;
1526: --
1527: per_vgr_del.del
1528: (p_valid_grade_id => c_vgr_rec.valid_grade_id
1548: ,p_argument => 'date_end'
1549: ,p_argument_value => p_date_end);
1550: --
1551: if g_debug then
1552: hr_utility.set_location(l_proc, 30);
1553: end if;
1554: --
1555: for c_vgr_rec in csr_valid_grades loop
1556: --
1559: -- that new End Date or is null, then update that valid grade's Date To
1560: -- to that new End Date.
1561: --
1562: if g_debug then
1563: hr_utility.set_location(l_proc, 35);
1564: end if;
1565: --
1566: if (c_vgr_rec.date_from < p_date_end and
1567: nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_end ) then
1566: if (c_vgr_rec.date_from < p_date_end and
1567: nvl(c_vgr_rec.date_to, hr_api.g_eot) > p_date_end ) then
1568: --
1569: if g_debug then
1570: hr_utility.set_location(l_proc, 40);
1571: end if;
1572: --
1573: per_vgr_upd.upd
1574: (p_valid_grade_id => c_vgr_rec.valid_grade_id
1585: --
1586: elsif (c_vgr_rec.date_from > p_date_end) then
1587: --
1588: if g_debug then
1589: hr_utility.set_location(l_proc, 45);
1590: end if;
1591: --
1592: per_vgr_del.del
1593: (p_valid_grade_id => c_vgr_rec.valid_grade_id
1602: --
1603: END IF;
1604: --
1605: if g_debug then
1606: hr_utility.set_location(l_proc, 50);
1607: end if;
1608: --
1609: p_valid_grades_changed := l_valid_grade_changed;
1610: if g_debug then
1607: end if;
1608: --
1609: p_valid_grades_changed := l_valid_grade_changed;
1610: if g_debug then
1611: hr_utility.set_location('Leaving: '||l_proc, 55);
1612: end if;
1613: --
1614: end maintain_valid_grades;
1615: --
1639: begin
1640:
1641: if g_debug then
1642: l_proc :='synchronize_per_all_positions';
1643: hr_utility.set_location('Entering:'||l_proc, 1);
1644: end if;
1645: --
1646: -- Check mandatory parameters have been set
1647: --
1650: ,p_argument => 'position_id'
1651: ,p_argument_value => p_position_id
1652: );
1653: if g_debug then
1654: hr_utility.set_location(l_proc, 20);
1655: end if;
1656: --
1657: hr_api.mandatory_arg_error
1658: (p_api_name => l_proc
1659: ,p_argument => 'effective_date'
1660: ,p_argument_value => p_effective_date
1661: );
1662: if g_debug then
1663: hr_utility.set_location(l_proc, 30);
1664: end if;
1665: --
1666: /*
1667: hr_api.mandatory_arg_error
1675: --
1676: -- lock row in per_all_positions
1677: --
1678: if g_debug then
1679: hr_utility.set_location(l_proc, 40);
1680: end if;
1681: --
1682: open c1(p_position_id);
1683: fetch c1 into l_ovn;
1684: if c1%notfound then
1685: close c1;
1686: /*
1687: if g_debug then
1688: hr_utility.set_location(l_proc, 50);
1689: end if;
1690: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1691: hr_utility.raise_error;
1692: */
1686: /*
1687: if g_debug then
1688: hr_utility.set_location(l_proc, 50);
1689: end if;
1690: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1691: hr_utility.raise_error;
1692: */
1693: else
1694: close c1;
1687: if g_debug then
1688: hr_utility.set_location(l_proc, 50);
1689: end if;
1690: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1691: hr_utility.raise_error;
1692: */
1693: else
1694: close c1;
1695: --
1693: else
1694: close c1;
1695: --
1696: if g_debug then
1697: hr_utility.set_location(l_proc, 60);
1698: end if;
1699: --
1700: per_pos_shd.lck(
1701: p_position_id => p_position_id
1703: --
1704: -- delete row from per_all_positions
1705: --
1706: if g_debug then
1707: hr_utility.set_location(l_proc, 70);
1708: end if;
1709: --
1710: per_pos_del.del(
1711: p_position_id => p_position_id
1711: p_position_id => p_position_id
1712: ,p_object_version_number => l_ovn);
1713: --
1714: if g_debug then
1715: hr_utility.set_location(l_proc, 80);
1716: end if;
1717: --
1718: end if;
1719: elsif p_datetrack_mode = hr_api.g_update or
1730: begin
1731: -- if l_effective_end_date <> hr_api.g_eot then
1732: -- lock the position rows explicitly
1733: if g_debug then
1734: hr_utility.set_location(l_proc, 80);
1735: end if;
1736:
1737: l_ovn := p_object_version_number;
1738: --
1749: end if;
1750: */
1751: --
1752: if g_debug then
1753: hr_utility.set_location(l_proc, 90);
1754: end if;
1755: --
1756: -- update copied_to_old_position_flag in all the following rows
1757: update hr_all_positions_f
1767: ,hr_api.g_future_change, p_effective_date - 1
1768: );
1769: --
1770: if g_debug then
1771: hr_utility.set_location(l_proc, 100);
1772: end if;
1773: -- call refresh_position procedure to replicate changes in
1774: -- per_all_positions
1775: --
1779: ,p_object_version_number => p_object_version_number
1780: ,p_refresh_date => trunc(sysdate));
1781: --
1782: if g_debug then
1783: hr_utility.set_location(l_proc, 110);
1784: end if;
1785: -- end if;
1786: end;
1787: end if;
1785: -- end if;
1786: end;
1787: end if;
1788: if g_debug then
1789: hr_utility.set_location( 'Leaving : ' || l_proc, 200);
1790: end if;
1791: --
1792:
1793: end synchronize_per_all_positions;
2104: where position_definition_id = l_position_definition_id;
2105: --
2106: begin
2107: --
2108: g_debug := hr_utility.debug_enabled;
2109: if g_debug then
2110: l_proc := g_package||'create_position';
2111: hr_utility.set_location('Entering:'|| l_proc, 10);
2112: end if;
2107: --
2108: g_debug := hr_utility.debug_enabled;
2109: if g_debug then
2110: l_proc := g_package||'create_position';
2111: hr_utility.set_location('Entering:'|| l_proc, 10);
2112: end if;
2113: --
2114: -- Issue a savepoint
2115: --
2124: --
2125: -- Get business_group_id using job.
2126: --
2127: if g_debug then
2128: hr_utility.set_location(l_proc, 10);
2129: end if;
2130: --
2131: open csr_job_bg;
2132: fetch csr_job_bg
2133: into l_business_group_id;
2134: --
2135: if csr_job_bg%notfound then
2136: close csr_job_bg;
2137: hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
2138: hr_utility.raise_error;
2139: else
2140: close csr_job_bg;
2141: end if;
2134: --
2135: if csr_job_bg%notfound then
2136: close csr_job_bg;
2137: hr_utility.set_message(801, 'HR_51090_JOB_NOT_EXIST');
2138: hr_utility.raise_error;
2139: else
2140: close csr_job_bg;
2141: end if;
2142: --
2140: close csr_job_bg;
2141: end if;
2142: --
2143: if g_debug then
2144: hr_utility.set_location(l_proc, 15);
2145: end if;
2146: --
2147: open isdel;
2148: fetch isdel into l_flex_num;
2150: close isdel;
2151: --
2152: -- the flex structure has not been found
2153: --
2154: hr_utility.set_message(801, 'HR_7471_FLEX_PEA_INVLALID_ID');
2155: hr_utility.raise_error;
2156: end if;
2157: close isdel;
2158: --
2151: --
2152: -- the flex structure has not been found
2153: --
2154: hr_utility.set_message(801, 'HR_7471_FLEX_PEA_INVLALID_ID');
2155: hr_utility.raise_error;
2156: end if;
2157: close isdel;
2158: --
2159: if g_debug then
2156: end if;
2157: close isdel;
2158: --
2159: if g_debug then
2160: hr_utility.set_location(l_proc, 30);
2161: end if;
2162: --
2163: --
2164: l_date_effective := trunc(p_date_effective);
2164: l_date_effective := trunc(p_date_effective);
2165: l_date_end := trunc(p_date_end);
2166: --
2167: if g_debug then
2168: hr_utility.set_location(l_proc, 35);
2169: end if;
2170: --
2171: -- if p_availability_status_id is NULL then default it to 'ACTIVE' status_id
2172: --
2175: fetch c1 into l_availability_status_id;
2176: if c1%notfound then
2177: close c1;
2178: --
2179: hr_utility.set_message(801,'HR_INVALID_ACTIVE_POS_STATUS');
2180: hr_utility.raise_error;
2181: --
2182: else
2183: close c1;
2176: if c1%notfound then
2177: close c1;
2178: --
2179: hr_utility.set_message(801,'HR_INVALID_ACTIVE_POS_STATUS');
2180: hr_utility.raise_error;
2181: --
2182: else
2183: close c1;
2184: end if;
2186: l_availability_Status_id := p_availability_status_id;
2187: end if;
2188: --
2189: if g_debug then
2190: hr_utility.set_location(l_proc, 20);
2191: end if;
2192: --
2193: -- 2242339 get segment values if p_job_definition_id entered with a value
2194: -- also get flex number for this flex structure.
2197: --
2198: then
2199: --
2200: if g_debug then
2201: hr_utility.set_location(l_proc, 15);
2202: end if;
2203: --
2204: -- set indicator to show p_position_definition_id did not enter pgm null
2205: --
2245: if csr_idsel%NOTFOUND
2246: then
2247: close csr_idsel;
2248: /*-- start change for the bug 5682240
2249: hr_utility.set_message (801, 'No flex number for this position definition id'); -- orignal
2250: -------------------------- */
2251: hr_utility.set_location('No flex number for position definition id= '||l_position_definition_id, 630);
2252:
2253: end if;
2247: close csr_idsel;
2248: /*-- start change for the bug 5682240
2249: hr_utility.set_message (801, 'No flex number for this position definition id'); -- orignal
2250: -------------------------- */
2251: hr_utility.set_location('No flex number for position definition id= '||l_position_definition_id, 630);
2252:
2253: end if;
2254: close csr_idsel;
2255: else
2687: --
2688: -- call refresh_position procedure to replicate changes in per_all_positions
2689: --
2690: if g_debug then
2691: hr_utility.set_location ( l_proc, 1000);
2692: hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2693: end if;
2694: --
2695: synchronize_per_all_positions
2688: -- call refresh_position procedure to replicate changes in per_all_positions
2689: --
2690: if g_debug then
2691: hr_utility.set_location ( l_proc, 1000);
2692: hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2693: end if;
2694: --
2695: synchronize_per_all_positions
2696: (p_position_id => l_position_id
2699: ,p_object_version_number => l_object_version_number
2700: );
2701: --
2702: if g_debug then
2703: hr_utility.set_location ( l_proc, 1001);
2704: hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2705: end if;
2706: --
2707: --
2700: );
2701: --
2702: if g_debug then
2703: hr_utility.set_location ( l_proc, 1001);
2704: hr_utility.set_location ( 'POSITION ID : ' || l_position_id || l_proc, 1000);
2705: end if;
2706: --
2707: --
2708: end;
2912: --
2913: end;
2914: --
2915: if g_debug then
2916: hr_utility.set_location(l_proc, 60);
2917: end if;
2918: --
2919: -- When in validation only mode raise the Validate_Enabled exception
2920: --
2931: p_position_definition_id := l_position_definition_id;
2932: p_name := l_name;
2933: --
2934: if g_debug then
2935: hr_utility.set_location(' Leaving:'||l_proc, 70);
2936: end if;
2937: --
2938: exception
2939: --
2959: p_position_definition_id := l_position_definition_id;
2960: end if;
2961: p_name := l_name;
2962: if g_debug then
2963: hr_utility.set_location(' Leaving:'||l_proc, 80);
2964: end if;
2965: --
2966: when others then
2967: --
3259: where position_definition_id = l_position_definition_id;
3260: --
3261: begin
3262: --
3263: g_debug := hr_utility.debug_enabled;
3264: if g_debug then
3265: l_proc := g_package||'update_position';
3266: hr_utility.set_location('Entering:'|| l_proc, 10);
3267: end if;
3262: --
3263: g_debug := hr_utility.debug_enabled;
3264: if g_debug then
3265: l_proc := g_package||'update_position';
3266: hr_utility.set_location('Entering:'|| l_proc, 10);
3267: end if;
3268: --
3269: -- Issue a savepoint if operating in validation only mode
3270: --
3277: l_language_code := p_language_code;
3278: hr_api.validate_language_code(p_language_code => l_language_code);
3279: --
3280: if g_debug then
3281: hr_utility.set_location(l_proc, 20);
3282: end if;
3283: --
3284: -- Process Logic
3285: --
3293: --
3294: then
3295: --
3296: if g_debug then
3297: hr_utility.set_location(l_proc, 15);
3298: end if;
3299: --
3300: -- set indicator to show p_position_definition_id did not enter pgm null
3301: --
3335: l_segment30;
3336: close c_segments;
3337: --
3338: if g_debug then
3339: hr_utility.set_location(l_proc, 27);
3340: end if;
3341: --
3342: else
3343: l_null_ind := 0;
3525: --
3526: end;
3527:
3528: if g_debug then
3529: hr_utility.set_location(l_proc, 30);
3530: end if;
3531: --
3532: -- Validation in addition to Table Handlers
3533: --
3538: ,p_effective_Date => p_effective_Date
3539: ,p_object_version_number => l_object_version_number);
3540: --
3541: if g_debug then
3542: hr_utility.set_location(l_proc, 40);
3543: end if;
3544: --
3545: if not l_api_updating then
3546: if g_debug then
3543: end if;
3544: --
3545: if not l_api_updating then
3546: if g_debug then
3547: hr_utility.set_location(l_proc, 50);
3548: end if;
3549: --
3550: -- As this an updating API, the position should already exist.
3551: --
3548: end if;
3549: --
3550: -- As this an updating API, the position should already exist.
3551: --
3552: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
3553: hr_utility.raise_error;
3554: else
3555: --
3556: if g_debug then
3549: --
3550: -- As this an updating API, the position should already exist.
3551: --
3552: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
3553: hr_utility.raise_error;
3554: else
3555: --
3556: if g_debug then
3557: hr_utility.set_location(l_proc, 60);
3553: hr_utility.raise_error;
3554: else
3555: --
3556: if g_debug then
3557: hr_utility.set_location(l_proc, 60);
3558: end if;
3559: --
3560: if l_null_ind = 0
3561: then
3567: fetch csr_idsel
3568: into l_flex_num;
3569: if csr_idsel%NOTFOUND then
3570: close csr_idsel;
3571: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3572: hr_utility.set_message_token('PROCEDURE', l_proc);
3573: hr_utility.set_message_token('STEP','5');
3574: hr_utility.raise_error;
3575: end if;
3568: into l_flex_num;
3569: if csr_idsel%NOTFOUND then
3570: close csr_idsel;
3571: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3572: hr_utility.set_message_token('PROCEDURE', l_proc);
3573: hr_utility.set_message_token('STEP','5');
3574: hr_utility.raise_error;
3575: end if;
3576: close csr_idsel;
3569: if csr_idsel%NOTFOUND then
3570: close csr_idsel;
3571: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3572: hr_utility.set_message_token('PROCEDURE', l_proc);
3573: hr_utility.set_message_token('STEP','5');
3574: hr_utility.raise_error;
3575: end if;
3576: close csr_idsel;
3577: --
3570: close csr_idsel;
3571: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3572: hr_utility.set_message_token('PROCEDURE', l_proc);
3573: hr_utility.set_message_token('STEP','5');
3574: hr_utility.raise_error;
3575: end if;
3576: close csr_idsel;
3577: --
3578: open csr_isfirstrow;
3578: open csr_isfirstrow;
3579: fetch csr_isfirstrow into l_minesd;
3580: if csr_isfirstrow%NOTFOUND then
3581: close csr_idsel;
3582: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3583: hr_utility.set_message_token('PROCEDURE', l_proc);
3584: hr_utility.set_message_token('STEP','6');
3585: hr_utility.raise_error;
3586: else
3579: fetch csr_isfirstrow into l_minesd;
3580: if csr_isfirstrow%NOTFOUND then
3581: close csr_idsel;
3582: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3583: hr_utility.set_message_token('PROCEDURE', l_proc);
3584: hr_utility.set_message_token('STEP','6');
3585: hr_utility.raise_error;
3586: else
3587: close csr_isfirstrow;
3580: if csr_isfirstrow%NOTFOUND then
3581: close csr_idsel;
3582: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3583: hr_utility.set_message_token('PROCEDURE', l_proc);
3584: hr_utility.set_message_token('STEP','6');
3585: hr_utility.raise_error;
3586: else
3587: close csr_isfirstrow;
3588: end if;
3581: close csr_idsel;
3582: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3583: hr_utility.set_message_token('PROCEDURE', l_proc);
3584: hr_utility.set_message_token('STEP','6');
3585: hr_utility.raise_error;
3586: else
3587: close csr_isfirstrow;
3588: end if;
3589: --
3587: close csr_isfirstrow;
3588: end if;
3589: --
3590: if g_debug then
3591: hr_utility.set_location(l_proc||'l_pos_def_id : '
3592: ||l_position_definition_id, 700);
3593: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3594: hr_utility.set_location(l_proc, 70);
3595: end if;
3589: --
3590: if g_debug then
3591: hr_utility.set_location(l_proc||'l_pos_def_id : '
3592: ||l_position_definition_id, 700);
3593: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3594: hr_utility.set_location(l_proc, 70);
3595: end if;
3596: --
3597: if l_null_ind = 1 and l_name is null
3590: if g_debug then
3591: hr_utility.set_location(l_proc||'l_pos_def_id : '
3592: ||l_position_definition_id, 700);
3593: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3594: hr_utility.set_location(l_proc, 70);
3595: end if;
3596: --
3597: if l_null_ind = 1 and l_name is null
3598: then
3682: end if;
3683: end if;
3684: --
3685: if g_debug then
3686: hr_utility.set_location(l_proc||'l_pos_def_id : '
3687: || l_position_definition_id, 700);
3688: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3689: hr_utility.set_location(l_proc, 80);
3690: end if;
3684: --
3685: if g_debug then
3686: hr_utility.set_location(l_proc||'l_pos_def_id : '
3687: || l_position_definition_id, 700);
3688: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3689: hr_utility.set_location(l_proc, 80);
3690: end if;
3691: /*
3692: --
3685: if g_debug then
3686: hr_utility.set_location(l_proc||'l_pos_def_id : '
3687: || l_position_definition_id, 700);
3688: hr_utility.set_location(l_proc||'l_name :' || l_name, 701);
3689: hr_utility.set_location(l_proc, 80);
3690: end if;
3691: /*
3692: --
3693: -- Because we may need to maintain the valid grade dates, need to
3696: -- hr_psf_shd.lck(p_position_id => p_position_id
3697: -- ,p_object_version_number => l_object_version_number);
3698: --
3699: if g_debug then
3700: hr_utility.set_location(l_proc, 85);
3701: end if;
3702: Maintain Grade requirements to be investigated
3703: --
3704: -- If date_effective is being updated , then need to maintain valid grades
3719: --
3720: end if;
3721: --
3722: if g_debug then
3723: hr_utility.set_location(l_proc, 90);
3724: end if;
3725: --
3726: -- If date_end is being updated , then need to maintain valid grades
3727: -- accordingly for that position.
3743: --
3744: -- Update Position Details
3745: --
3746: if g_debug then
3747: hr_utility.set_location(l_proc||'l_pos_def_id : '
3748: || l_position_definition_id, 800);
3749: hr_utility.set_location(l_proc||'l_name :' || l_name, 801);
3750: end if;
3751: --
3745: --
3746: if g_debug then
3747: hr_utility.set_location(l_proc||'l_pos_def_id : '
3748: || l_position_definition_id, 800);
3749: hr_utility.set_location(l_proc||'l_name :' || l_name, 801);
3750: end if;
3751: --
3752: hr_psf_upd.upd
3753: (
3910: --
3911: -- call refresh_position procedure to replicate changes in per_all_positions
3912: --
3913: if g_debug then
3914: hr_utility.set_location ( l_proc, 1000);
3915: end if;
3916: --
3917: synchronize_per_all_positions
3918: (p_position_id => p_position_id
3921: ,p_object_version_number => l_object_version_number
3922: );
3923: --
3924: if g_debug then
3925: hr_utility.set_location ( l_proc, 1001);
3926: end if;
3927: --
3928: end;
3929: --
4110: --
4111: end;
4112: --
4113: if g_debug then
4114: hr_utility.set_location(l_proc, 100);
4115: end if;
4116: -- ----------- Maintain Grade requirements to be investigated
4117: --
4118: -- Because we may need to maintain the valid grade dates, need to
4121: -- hr_psf_shd.lck(p_position_id => p_position_id
4122: -- ,p_object_version_number => l_object_version_number);
4123: --
4124: if g_debug then
4125: hr_utility.set_location(l_proc, 85);
4126: end if;
4127: --
4128: -- If date_effective is being updated , then need to maintain valid grades
4129: -- accordingly for that position.
4143: --
4144: end if;
4145: --
4146: if g_debug then
4147: hr_utility.set_location(l_proc, 90);
4148: end if;
4149: --
4150: -- If date_end is being updated , then need to maintain valid grades
4151: -- accordingly for that position.
4170: p_valid_grades_changed_warning := FALSE;
4171: end if;
4172: --
4173: if g_debug then
4174: hr_utility.set_location(l_proc, 110);
4175: end if;
4176: --
4177: -- When in validation only mode raise the Validate_Enabled exception
4178: --
4188: p_effective_start_date := l_effective_start_date;
4189: p_effective_end_date := l_effective_end_date;
4190: --
4191: if g_debug then
4192: hr_utility.set_location('date effective is '||to_char(p_date_effective)
4193: ||l_proc,192);
4194: hr_utility.set_location(' Leaving:'||l_proc, 120);
4195: end if;
4196: --
4190: --
4191: if g_debug then
4192: hr_utility.set_location('date effective is '||to_char(p_date_effective)
4193: ||l_proc,192);
4194: hr_utility.set_location(' Leaving:'||l_proc, 120);
4195: end if;
4196: --
4197: exception
4198: --
4209: --
4210: p_object_version_number := p_object_version_number;
4211: p_position_definition_id := p_position_definition_id;
4212: if g_debug then
4213: hr_utility.set_location(' Leaving:'||l_proc, 130);
4214: end if;
4215: --
4216: when others then
4217: --
4259: --
4260: begin
4261:
4262: --
4263: g_debug := hr_utility.debug_enabled;
4264: if g_debug then
4265: l_proc := g_package||'delete_position';
4266: hr_utility.set_location('Entering:'|| l_proc, 10);
4267: end if;
4262: --
4263: g_debug := hr_utility.debug_enabled;
4264: if g_debug then
4265: l_proc := g_package||'delete_position';
4266: hr_utility.set_location('Entering:'|| l_proc, 10);
4267: end if;
4268: --
4269: -- Issue a savepoint if operating in validation only mode
4270: --
4270: --
4271: savepoint delete_position;
4272: --
4273: if g_debug then
4274: hr_utility.set_location(l_proc, 20);
4275: end if;
4276: --
4277: -- Process Logic
4278: --
4367: --
4368: -- call refresh_position procedure to replicate changes in per_all_positions
4369: --
4370: if g_debug then
4371: hr_utility.set_location ( l_proc, 1000);
4372: end if;
4373: --
4374: synchronize_per_all_positions
4375: (p_position_id => p_position_id
4378: ,p_object_version_number => l_object_version_number
4379: );
4380: --
4381: if g_debug then
4382: hr_utility.set_location ( l_proc, 1001);
4383: end if;
4384: --
4385: -- delete Position Defintions if not used
4386: --
4426: --
4427: end;
4428: --
4429: if g_debug then
4430: hr_utility.set_location(l_proc, 60);
4431: end if;
4432: --
4433: -- When in validation only mode raise the Validate_Enabled exception
4434: --
4436: raise hr_api.validate_enabled;
4437: end if;
4438: --
4439: if g_debug then
4440: hr_utility.set_location(' Leaving:'||l_proc, 70);
4441: end if;
4442: --
4443: exception
4444: --
4492: l_validation_end_date date;
4493: --
4494: begin
4495: --
4496: g_debug := hr_utility.debug_enabled;
4497: if g_debug then
4498: l_proc := g_package||'lck';
4499: hr_utility.set_location('Entering:'|| l_proc, 10);
4500: end if;
4495: --
4496: g_debug := hr_utility.debug_enabled;
4497: if g_debug then
4498: l_proc := g_package||'lck';
4499: hr_utility.set_location('Entering:'|| l_proc, 10);
4500: end if;
4501: --
4502: hr_psf_shd.lck
4503: (
4509: ,p_datetrack_mode => p_datetrack_mode
4510: );
4511: --
4512: if g_debug then
4513: hr_utility.set_location(' Leaving:'||l_proc, 70);
4514: end if;
4515: --
4516: end lck;
4517: --