1 PACKAGE BODY PA_HR_UPDATE_API AS
2 -- $Header: PARHRUPB.pls 120.30 2010/10/31 20:36:40 nisinha ship $
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
12 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
13 -- Out parameters
14 ----------------------------------------------------------------------------------------------------------------
15 PROCEDURE log_message (p_log_msg IN VARCHAR2)
16
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;
26 END log_message;
27
28 -- This API returns the job group id for the corresponding Job
29 FUNCTION get_job_group_id(
30 P_job_id IN per_jobs.job_id%type
31 ) RETURN per_job_groups.job_group_id%type
32 IS
33 v_job_grp_id per_job_groups.job_group_id%type;
34
35 BEGIN
36 v_job_grp_id := PA_JOB_UTILS.get_job_group_id(P_job_id);
37
38 return (v_job_grp_id);
39
40 END get_job_group_id;
41
42
43 -- This is an wrapper api for the check exp ou procedure
44 -- this function returns 'Y' if the given OU is valid otherwise it returns 'N'
45 FUNCTION validate_exp_OU (p_org_id IN NUMBER)
46 return VARCHAR2 IS
47 v_return_status varchar2(1000);
48 v_error_message_code varchar2(1000);
49 BEGIN
50
51 pa_hr_update_api.check_exp_OU(p_org_id => p_org_id
52 ,x_return_status => v_return_status
53 ,x_error_message_code => v_error_message_code
54 );
55
56 If v_return_status = FND_API.G_RET_STS_SUCCESS then
57 return 'Y';
58 else
59 return 'N';
60 End if;
61
62
63
64 END validate_exp_OU;
65
66
67
68
69 -- This Function returns the job level DFF based on the job_id and Job_group_id
70 -- 24-Dec: Move the logic of the code to PA_JOB_UTILS and call the function here
71 FUNCTION get_job_level(
72 P_job_id IN per_jobs.job_id%type
73 ,P_job_group_id IN per_job_groups.job_group_id%type
74 ) RETURN NUMBER
75 IS
76 l_job_level NUMBER;
77
78 BEGIN
79 l_job_level := PA_JOB_UTILS.get_job_level (
80 P_job_id => P_job_id
81 ,P_job_group_id => P_job_group_id
82 );
83
84 Return l_job_level;
85
86 END get_job_level;
87
88
89 -- This Procedure Adds messages to stack
90 PROCEDURE add_to_stack(P_return_status VARCHAR2,
91 P_error_message_code VARCHAR2
92 ) IS
93
94 BEGIN
95
96 if P_return_status <> FND_API.G_RET_STS_SUCCESS then
97 PA_UTILS.add_message(p_app_short_name => 'PA',
98 p_msg_name => P_error_message_code);
99 end if;
100
101
102 END add_to_stack;
103
104
105 -- This Function returns boolean value of true if a job is master job otherwise
106 -- it returns false -- IN parameter will be job_id
107 FUNCTION check_master_job(P_job_id IN per_Jobs.job_id%type)
108 RETURN boolean
109 IS
110 l_flag BOOLEAN;
111
112 BEGIN
113 l_flag := PA_JOB_UTILS.check_master_job(P_job_id);
114 return l_flag;
115
116 END check_master_job;
117
118
119 -- This API returns the utilization of job / person
120 -- The IN parameters will be Person_id and Date for Person's Billability
121 -- OR Job_id for the Job's billability
122 FUNCTION check_job_utilization
123 (
124 P_job_id IN number
125 ,P_person_id IN number
126 ,P_date IN date
127 ) RETURN VARCHAR2
128 IS
129
130 utilization_flag VARCHAR2(150);
131 v_job_id per_jobs.job_id%type;
132 v_job_info_type VARCHAR2(20) := 'Job Category';
133
134 BEGIN
135
136
137 v_job_id := P_job_id;
138 If P_person_id is NOT NULL AND P_date is NOT NULL AND P_job_id is NULL then
139
140 SELECT Job_id
141 INTO v_job_id
142 FROM per_all_assignments_f
143 WHERE Person_id = P_person_id
144 AND P_date BETWEEN effective_start_date
145 AND effective_end_date
146 AND job_id is NOT NULL
147 AND primary_flag = 'Y'
148 and assignment_type in ('E', 'C');
149
150 End if;
151
152 If v_job_id is NOT NULL then
153 SELECT jei_information3
154 INTO utilization_flag
155 FROM per_job_extra_info
156 WHERE job_id = v_job_id
157 AND information_type = v_job_info_type
158 AND jei_information3 IS NOT NULL; -- Bug 2898766
159 End if;
160
161 If utilization_flag is NULL then
162 utilization_flag := 'N';
163 End if;
164
165 return (utilization_flag);
166
167 EXCEPTION
168 /* Bug 2898766 - Handled the exception if more than one rows are returned */
169 WHEN TOO_MANY_ROWS THEN
170 utilization_flag := 'X';
171 return (utilization_flag );
172
173 WHEN NO_DATA_FOUND then
174 utilization_flag := 'N';
175 return (utilization_flag );
176
177 END check_job_utilization;
178
179
180
181
182 -- This API returns the Billability of job / person
183 -- The IN parameters will be Person_id and Date for Person's Billability
184 -- OR Job_id for the Job's billability
185 FUNCTION check_job_billability
186 (
187 P_job_id IN number
188 ,P_person_id IN number
189 ,P_date IN date
190 ) RETURN VARCHAR2
191 IS
192
193 Billable_flag VARCHAR2(150);
194 v_job_id per_jobs.job_id%type;
195 v_job_info_type VARCHAR2(20) := 'Job Category';
196
197 BEGIN
198
199
200 v_job_id := P_job_id;
201 If P_person_id is NOT NULL AND P_date is NOT NULL AND P_job_id is NULL then
202
203 SELECT Job_id
204 INTO v_job_id
205 FROM per_all_assignments_f
206 WHERE Person_id = P_person_id
207 AND P_date BETWEEN effective_start_date
208 AND effective_end_date
209 AND job_id is NOT NULL
210 AND primary_flag = 'Y'
211 AND assignment_type in ('E', 'C');
212 End if;
213
214 If v_job_id is NOT NULL then
215 SELECT jei_information2
216 INTO Billable_flag
217 FROM per_job_extra_info
218 WHERE job_id = v_job_id
219 AND information_type = v_job_info_type
220 AND jei_information2 IS NOT NULL; -- Bug 2898766
221 End if;
222
223 If Billable_flag is NULL then
224 Billable_flag := 'N';
225 End if;
226
227 return (Billable_flag);
228
229 EXCEPTION
230 /* Bug 2898766 - Handled the exception if more than one rows are returned */
231 WHEN TOO_MANY_ROWS THEN
232 Billable_flag := 'X';
233 return (Billable_flag );
234
235 WHEN NO_DATA_FOUND then
236 Billable_flag := 'N';
237 return (Billable_flag );
238
239 END check_job_billability;
240
241
242
243 ----------------------------------------------------------------
244 -- This API returns the schedulable_flag value of the passed job
245 ----------------------------------------------------------------
246 FUNCTION check_job_schedulable
247 (
248 p_job_id IN NUMBER
249 ,p_person_id IN NUMBER
250 ,p_date IN DATE
251 ) RETURN VARCHAR2
252 IS
253 l_schedulable_flag VARCHAR2(150) := 'N';
254 l_job_info_type VARCHAR2(20) := 'Job Category';
255 l_job_id NUMBER;
256 BEGIN
257 l_job_id := p_job_id;
258
259 IF p_person_id is NOT NULL AND p_date is NOT NULL THEN
260 SELECT job_id
261 INTO l_job_id
262 FROM per_all_assignments_f paaf
263 WHERE paaf.person_id = p_person_id
264 AND trunc(p_date) BETWEEN trunc(paaf.effective_start_date) -- Bug 8269512 : introduced trunc() on dates
265 AND trunc(paaf.effective_end_date) -- Bug 8269512 : introduced trunc() on dates
266 AND paaf.job_id is NOT NULL
267 AND paaf.primary_flag = 'Y'
268 AND paaf.assignment_type in ('E', 'C')
269 AND ((SELECT per_system_status
270 FROM per_assignment_status_types past
271 WHERE past.assignment_status_type_id = paaf.assignment_status_type_id) IN ('ACTIVE_ASSIGN','ACTIVE_CWK')); --Bug#8879958
272 END IF;
273
274 IF l_job_id is NOT NULL THEN
275 SELECT jei_information6
276 INTO l_schedulable_flag
277 FROM per_job_extra_info
278 WHERE job_id = l_job_id
279 AND information_type = l_job_info_type
280 AND jei_information6 IS NOT NULL;
281
282 IF l_schedulable_flag IS NULL THEN
283 l_schedulable_flag := 'N';
284 END IF;
285 END IF;
286
287 RETURN l_schedulable_flag;
288 EXCEPTION
289 WHEN TOO_MANY_ROWS THEN
290 l_schedulable_flag := 'X';
291 RETURN l_schedulable_flag;
292 WHEN NO_DATA_FOUND then
293 RETURN l_schedulable_flag;
294
295 END check_job_schedulable;
296
297
298
299 -- This Procedure checks whether the given OU is valid or Not
300 PROCEDURE check_exp_OU(p_org_id IN NUMBER
301 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
302 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
303 ) IS
304
305 v_error_message_code VARCHAR2(1000) := 'PA_INVALID_EXP_OU';
306 --'Invalid Operating Unit';
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
316
317 SELECT 'Y'
318 INTO v_dummy
319 FROM pa_implementations_all
320 WHERE org_id = p_org_id
321 AND rownum = 1;
322
323 End if;
324 /*
325
326 If v_dummy = 'Y' 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
336 x_error_message_code := v_error_message_code;
337 x_return_status := FND_API.G_RET_STS_ERROR;
338
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
348 raise;
349
350
351
352 END check_exp_OU;
353
354
355 -- This API makes calls to PA_REOSURCE_PVT.UPDATE_RESOURCE_DENORM api
356 -- which actually updates the pa_reosurces_denorm entity
357 PROCEDURE call_create_resource_denorm
358 (P_job_id_old per_jobs.job_id%type
359 ,P_job_id_new per_jobs.job_id%type
360 ,P_job_level_old NUMBER
361 ,P_job_level_new NUMBER
362 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
363 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
364 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
365 ) IS
366 v_return_status VARCHAR2(2000);
367 v_error_message_code VARCHAR2(2000);
368 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
369 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
370 v_job_level_old NUMBER := 0;
371 v_job_level_new NUMBER;
372 v_job_id_old PER_JOBS.JOB_ID%type;
373 v_job_id_new PER_JOBS.JOB_ID%type;
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;
383 v_job_level_old := 0;
384 If v_job_level_new is NULL then
385 v_job_level_new := 0; ---- to be confirmed if no job level is found
386 end if; ----- then assign joblevel to zero when grade is deleted
387
388 if P_job_level_old is NOT NULL then
389 v_job_level_old := P_job_level_old;
390 end if;
391 If P_job_id_old is NULL then
392 v_job_id_old := P_job_id_new;
393 end if;
394
395 if v_job_id_new is NOT NULL and v_job_level_new is NOT NULL then
396 v_resource_rec_old.job_id := v_job_id_old;
397 v_resource_rec_old.resource_job_level := v_job_level_old;
398 v_resource_rec_new.job_id := v_job_id_new;
399 v_resource_rec_new.resource_job_level := v_job_level_new;
400
401 -- Call PRM API update resource denorm which actually updates the
402 -- pa_resource_denorm entity
403 PA_RESOURCE_PVT.update_resource_denorm
404 ( p_resource_denorm_old_rec => v_resource_rec_old
405 ,p_resource_denorm_new_rec => v_resource_rec_new
406 ,x_return_status => x_return_status
407 ,x_msg_data => x_msg_data
408 ,x_msg_count => x_msg_count
409 );
410
411
412 End if;
413
414
415
416 -- reset the Error stack
417 PA_DEBUG.Reset_Err_Stack;
418
419 EXCEPTION
420
421 WHEN OTHERS THEN
422 -- 4537865 : RESET x_msg_count and x_msg_data also
423 x_msg_count := 1 ;
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
433
434 END call_create_resource_denorm;
435
436 -- This API makes calls to PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API
437 -- which actually updates the pa_resources_denorm entity
438 -- This API will update the job level of the job id passed in of the
439 -- resources denorm records
440 PROCEDURE update_job_level_res_denorm
441 ( P_job_id_old per_jobs.job_id%type
442 ,P_job_id_new per_jobs.job_id%type
443 ,P_job_level_old NUMBER
444 ,P_job_level_new NUMBER
445 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
446 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
447 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
448 ) IS
449
450 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
451 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
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');
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;
465 l_job_id_old := P_job_id_old;
466 l_job_level_old := P_job_level_old;
467
468 IF P_DEBUG_MODE = 'Y' THEN
469 log_message('====== Job Id Info ==========');
470 log_message('Job Id = ' || l_job_id_new);
471 log_message('Job Level Old = ' || l_job_level_old);
472 log_message('Job Level New = ' || l_job_level_new);
473 END IF;
474
475 l_resource_rec_old.job_id := l_job_id_old;
476 l_resource_rec_old.resource_job_level := l_job_level_old;
477 l_resource_rec_new.job_id := l_job_id_new;
478 l_resource_rec_new.resource_job_level := l_job_level_new;
479
480 -- Call PRM API update resource denorm which actually updates the
481 -- pa_resource_denorm entity
482 PA_RESOURCE_PVT.update_resource_denorm
483 ( p_resource_denorm_old_rec => l_resource_rec_old
484 ,p_resource_denorm_new_rec => l_resource_rec_new
485 ,x_return_status => x_return_status
486 ,x_msg_data => x_msg_data
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
496 -- 4537865 : RESET x_msg_count and x_msg_data also
497 x_msg_count := 1 ;
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;
507
508 -- This API gets all jobs belonging to the master job id and updates
509 -- the resource denorm records that has the affected job id
510 PROCEDURE update_all_jobs
511 ( P_job_id per_jobs.job_id%type
512 ,P_job_level_old pa_resources_denorm.resource_job_level%type
513 ,P_job_level_new pa_resources_denorm.resource_job_level%type
514 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
515 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
516 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
517 ) IS
518
519 l_job_id PER_JOBS.JOB_ID%type;
520 l_job_level_old pa_resources_denorm.resource_job_level%type;
521 l_job_level_new pa_resources_denorm.resource_job_level%type;
522 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
523 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
524
525 -- Cursor to get all affected job ids with the P_job_id level change
526 CURSOR get_job_ids(
527 l_job_id per_jobs.job_id%type
528 ) is
529 SELECT l_job_id effected_job_id
530 FROM sys.dual
531 ,per_job_groups pjg
532 WHERE pjg.master_flag = 'Y'
533 AND pjg.job_group_id = get_job_group_id(l_job_id)
534 UNION
535 SELECT distinct pjr.from_job_id effected_job_id
536 FROM pa_job_relationships pjr
537 ,per_job_groups pjg
538 WHERE pjg.master_flag = 'Y'
539 AND pjr.to_job_id = l_job_id
540 AND pjr.to_job_group_id = pjg.job_group_id
541 UNION
542 SELECT distinct pjr.to_job_id effected_job_id
543 FROM pa_job_relationships pjr
544 ,per_job_groups pjg
545 WHERE pjg.master_flag = 'Y'
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
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
560 -- just assign the value to the local variable because job_level can be
561 -- null and do not need a default value
562 l_job_level_old := P_job_level_old;
563 l_job_level_new := P_job_level_new;
564
565
566 -- if the job level changes then update all the jobs which are affected and
567 -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
568 OPEN get_job_ids(l_job_id) ;
569 LOOP
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;
579
580 update_job_level_res_denorm
581 ( P_job_id_old => l_job_id
582 ,P_job_id_new => l_job_id
583 ,P_job_level_old => l_job_level_old
584 ,P_job_level_new => l_job_level_new
585 ,x_return_status => x_return_status
586 ,x_msg_data => x_msg_data
587 ,x_msg_count => x_msg_count
588 );
589 End if;
590
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
600 -- 4537865 : RESET x_msg_count and x_msg_data also
601 x_msg_count := 1 ;
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;
611
612 -- This Procedure updates all resource denorm records affected by the
613 -- changes in job mappings in pa_job_relationships table
614 -- It will update the job levels of the resource denorm records
615 PROCEDURE pa_job_relation_job_id
616 (P_calling_mode IN VARCHAR2
617 ,P_from_job_id_old IN pa_job_relationships.from_job_id%type
618 ,P_from_job_id_new IN pa_job_relationships.from_job_id%type
619 ,P_to_job_id_old IN pa_job_relationships.from_job_id%type
620 ,P_to_job_id_new IN pa_job_relationships.from_job_id%type
621 ,P_from_job_group_id IN pa_job_relationships.from_job_id%type
622 ,P_to_job_group_id IN pa_job_relationships.from_job_id%type
623 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
624 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
625 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
626
627 l_job_level_old NUMBER;
628 l_job_level_new NUMBER;
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');
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);
642 log_message('P_from_job_id_new = ' || P_from_job_id_new);
643 log_message('P_to_job_id_old = ' || P_to_job_id_old);
644 log_message('P_to_job_id_new = ' || P_to_job_id_new);
645 log_message('P_from_job_group_id = ' || P_from_job_group_id);
646 log_message('P_to_job_group_id = ' || P_to_job_group_id);
647 END IF;
648
649 If P_calling_mode = 'INSERT' OR P_calling_mode = 'UPDATE' Then
650
651 IF P_DEBUG_MODE = 'Y' THEN
652 log_message('P_calling_mode = ' || P_calling_mode);
653 END IF;
654 ----------------------------------------------------------------------------
655 -- Need to check that the from_job_id belongs to the Proj_Res_Job_Group
656 -- If it is a PRJG job, we need to check that the to_job_id is a master job
657 -- and update the resource denorm records with the job level of the PRJG job
658 -----------------------------------------------------------------------------
659
660 If (PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_new, P_from_job_group_id) = 'Y' AND
661 PA_JOB_UTILS.check_master_job(P_to_job_id_new)) then
662
663 IF P_DEBUG_MODE = 'Y' THEN
664 log_message('From is PRJG, To is Master');
665 END IF;
666
667 l_job_level_new := PA_JOB_UTILS.get_job_level(P_from_job_id_new,P_from_job_group_id);
668
669 l_PRJG_job_id := P_from_job_id_new;
670 l_master_job_id := P_to_job_id_new;
671 l_condition := 'PM';
672
673 End if;
674
675
676 -----------------------------------------------------------------------------
677 -- Now, need to do the opposite: check that the to_job_id belongs to the PRJG
678 -- If it is a PRJG job, we need to check that the from_job_id is a master job
679 -- and update the resource denorm records with the job level of the PRJG job
680 -----------------------------------------------------------------------------
681
682 If (PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_new, P_to_job_group_id) = 'Y' AND
683 PA_JOB_UTILS.check_master_job(P_from_job_id_new)) then
684
685 IF P_DEBUG_MODE = 'Y' THEN
686 log_message('From is Master, To is PRJG');
687 END IF;
688 l_job_level_new := PA_JOB_UTILS.get_job_level(P_to_job_id_new,P_to_job_group_id);
689
690 l_PRJG_job_id := P_to_job_id_new;
691 l_master_job_id := P_from_job_id_new;
692 l_condition := 'PM';
693
694 End if;
695
696
697 -----------------------------------------------------------------------------
698 -- For Master and Normal job mapping, we need to get the job level of the job
699 -- and only updates the resource denorm records which has the normal job id
700 -- with the job level of the Master, PRJG job id
701 -- Case: from_job_id is Master and to_job_id is Normal job
702 ------------------------------------------------------------------------------
703
704 If (PA_JOB_UTILS.check_master_job(P_from_job_id_new) AND
705 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_new, P_to_job_group_id) = 'N' AND
706 NOT PA_JOB_UTILS.check_master_job(P_to_job_id_new)) THEN
707
708 IF P_DEBUG_MODE = 'Y' THEN
709 log_message('From is Master, To is Normal');
710 END IF;
711 l_job_level_new := PA_JOB_UTILS.get_job_level(P_from_job_id_new, P_from_job_group_id);
712
713 l_job_id := P_to_job_id_new;
714 l_condition := 'MN';
715
716 End If;
717
718 ----------------------------------------------------------------------------
719 -- Same as previous, but to_job_id is Master and from_job_id is normal job
720 ----------------------------------------------------------------------------
721 If (PA_JOB_UTILS.check_master_job(P_to_job_id_new) AND
722 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_new, P_from_job_group_id) = 'N' AND
723 NOT PA_JOB_UTILS.check_master_job(P_from_job_id_new)) THEN
724
725 IF P_DEBUG_MODE = 'Y' THEN
726 log_message('From is Normal, To is Master');
727 END IF;
728 l_job_level_new := PA_JOB_UTILS.get_job_level(P_to_job_id_new, P_to_job_group_id);
729
730 l_job_id := P_from_job_id_new;
731 l_condition := 'MN';
732
733 End If;
734
735
736 -- PRJG and Master Job Mapping
737 -- Update all jobs belonging to the Master job
738 If l_condition = 'PM' Then
739
740 IF P_DEBUG_MODE = 'Y' THEN
741 log_message('Updating records for PRJG job');
742 END IF;
743 update_job_level_res_denorm
744 ( P_job_id_old => NULL
745 ,P_job_id_new => l_PRJG_job_id
746 ,P_job_level_old => NULL
747 ,P_job_level_new => l_job_level_new
748 ,x_return_status => x_return_status
749 ,x_msg_data => x_msg_data
750 ,x_msg_count => x_msg_count
751 );
752
753 IF P_DEBUG_MODE = 'Y' THEN
754 log_message('Updating records for jobs belonging to master');
755 END IF;
756 update_all_jobs
757 ( P_job_id => l_master_job_id
758 ,P_job_level_old => NULL
759 ,P_job_level_new => l_job_level_new
760 ,x_return_status => x_return_status
761 ,x_msg_data => x_msg_data
762 ,x_msg_count => x_msg_count
763 );
764
765 -- Master and Normal Job mapping
766 -- Only update the normal job id
767 Elsif l_condition = 'MN' Then
768
769 IF P_DEBUG_MODE = 'Y' THEN
770 log_message('Updating records only for the normal job id');
771 END IF;
772 update_job_level_res_denorm
773 ( P_job_id_old => NULL
774 ,P_job_id_new => l_job_id
775 ,P_job_level_old => NULL
776 ,P_job_level_new => l_job_level_new
777 ,x_return_status => x_return_status
778 ,x_msg_data => x_msg_data
779 ,x_msg_count => x_msg_count
780 );
781
782 End If;
783
784 Elsif P_calling_mode = 'UPDATE' then
785
786 IF P_DEBUG_MODE = 'Y' THEN
787 log_message('P_calling_mode = UPDATE');
788 END IF;
789
790
791 Elsif P_calling_mode = 'DELETE' then
792
793 IF P_DEBUG_MODE = 'Y' THEN
794 log_message('P_calling_mode = DELETE');
795 END IF;
796 ------------------------------------------------------------------------------
797 -- The following condition checks if the from_job_id is a Master and to_job_id
798 -- is in the Proj_Res_Job_Group or the opposite
799 -- Also checks if the from_job_id is a Master Job and to_job_id is a normal job
800 -- id or the opposite
801 -- Sets the appropriate value for the job ids and the condition to update the
802 -- resource denorm records
803 ------------------------------------------------------------------------------
804
805 IF ((PA_JOB_UTILS.check_master_job(P_from_job_id_old) AND
806 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_old, P_to_job_group_id) = 'Y')) THEN
807
808 IF P_DEBUG_MODE = 'Y' THEN
809 log_message('From is Master, To is PRJG');
810 END IF;
811 l_master_job_id := P_from_job_id_old;
812 l_PRJG_job_id := P_to_job_id_old;
813 l_condition := 'PM';
814
815 ELSIF ((PA_JOB_UTILS.check_master_job(P_to_job_id_old) AND
816 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_old, P_from_job_group_id) = 'Y')) THEN
817
818 IF P_DEBUG_MODE = 'Y' THEN
819 log_message('From is PRJG, To is Master');
820 END IF;
821 l_master_job_id := P_to_job_id_old;
822 l_PRJG_job_id := P_from_job_id_old;
823 l_condition := 'PM';
824
825 ELSIF ( PA_JOB_UTILS.check_master_job(P_from_job_id_old) AND
826 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_old, P_to_job_group_id) = 'N' AND
827 NOT PA_JOB_UTILS.check_master_job(P_to_job_id_old)) THEN
828
829 IF P_DEBUG_MODE = 'Y' THEN
830 log_message('From is Master, To is Normal');
831 END IF;
832 l_master_job_id := P_from_job_id_old;
833 l_job_id := P_to_job_id_old;
834 l_condition := 'MN';
835
836 ELSIF ( PA_JOB_UTILS.check_master_job(P_to_job_id_old) AND
837 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_old, P_from_job_group_id) = 'N'AND
838 NOT PA_JOB_UTILS.check_master_job(P_from_job_id_old)) THEN
839
840 IF P_DEBUG_MODE = 'Y' THEN
841 log_message('From is Normal, To is Master');
842 END IF;
843 l_master_job_id := P_to_job_id_old;
844 l_job_id := P_from_job_id_old;
845 l_condition := 'MN';
846
847 END IF;
848
849
850 ------------------------------------------------------------
851 -- Next, update the resource denorm records correspondingly
852 -- If l_condition is 'PM' : PRJG and Master Jobs mapping,
853 -- We need to sets the job level to Null for all resource
854 -- denorm records with the job_id belonging to the Master,
855 -- and the Master Job itself
856 -- If l_condition is 'MN' : Master and Normal Jobs mapping,
857 -- We need to set the job level to Null ONLY for resource
858 -- denorm records with the job id of the Normal Job Id
859 ------------------------------------------------------------
860
861 If l_condition = 'PM' Then
862
863 IF P_DEBUG_MODE = 'Y' THEN
864 log_message('Updating records with job ids belong to Master');
865 log_message('Master Job Id = ' || l_master_job_id);
866 END IF;
867 update_all_jobs
868 ( P_job_id => l_master_job_id
869 ,P_job_level_old => NULL
870 ,P_job_level_new => NULL
871 ,x_return_status => x_return_status
872 ,x_msg_data => x_msg_data
873 ,x_msg_count => x_msg_count
874 );
875
876
877 Elsif l_condition = 'MN' Then
878
879 IF P_DEBUG_MODE = 'Y' THEN
880 log_message('Updating only records with the normal job id');
881 log_message('Job Id = ' || l_job_id);
882 END IF;
883 update_job_level_res_denorm
884 ( P_job_id_old => NULL
885 ,P_job_id_new => l_job_id
886 ,P_job_level_old => NULL
887 ,P_job_level_new => NULL
888 ,x_return_status => x_return_status
889 ,x_msg_data => x_msg_data
890 ,x_msg_count => x_msg_count
891 );
892 End If;
893
894 End if;
895
896
897
898 -- reset the Error stack
899 PA_DEBUG.Reset_Err_Stack;
900
901 EXCEPTION
902
903 WHEN NO_DATA_FOUND THEN
904 NULL;
905
906 WHEN OTHERS THEN
907 -- 4537865 : RESET x_msg_count and x_msg_data also
908 x_msg_count := 1 ;
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
918 END pa_job_relation_job_id;
919
920
921 -- Do updates on job level for PRJG job, master jobs and normal jobs
922 -- which has mapping to each other
923 PROCEDURE perform_job_updates
924 ( P_job_id per_jobs.job_id%type
925 ,P_job_level_old pa_resources_denorm.resource_job_level%type
926 ,P_job_level_new pa_resources_denorm.resource_job_level%type
927 ,P_job_group_id per_job_groups.job_group_id%type
928 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
929 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
930 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
931 ) IS
932
933 l_job_id PER_JOBS.JOB_ID%type;
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
943 ,l_job_group_id per_job_groups.job_group_id%type
944 ) IS
945
946 SELECT distinct pjr.from_job_id effected_job_id
947 FROM pa_job_relationships pjr
948 WHERE pjr.to_job_id = l_job_id
949 AND pjr.to_job_group_id = l_job_group_id
950 UNION
951 SELECT distinct pjr.to_job_id effected_job_id
952 FROM pa_job_relationships pjr
953 WHERE pjr.from_job_id = l_job_id
954 AND pjr.from_job_group_id = l_job_group_id;
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;
964
965 l_job_id := P_job_id;
966 l_job_level_old := P_job_level_old;
967 l_job_level_new := P_job_level_new;
968 l_job_group_id := P_job_group_id;
969
970 -- first updates the denorm records which has the PRJG job id
971 IF P_DEBUG_MODE = 'Y' THEN
972 log_message('Updating Denorm for PRJG job id = ' || l_job_id);
973 END IF;
974 update_job_level_res_denorm
975 ( P_job_id_old => l_job_id
976 ,P_job_id_new => l_job_id
977 ,P_job_level_old => l_job_level_old
978 ,P_job_level_new => l_job_level_new
979 ,x_return_status => x_return_status
980 ,x_msg_data => x_msg_data
981 ,x_msg_count => x_msg_count
982 );
983
984 -- next get the master job id and then updates all jobs
985 -- belonging to that master job id if the job id is NOT
986 -- a master job
987
988 If (PA_JOB_UTILS.check_master_job(l_job_id) = FALSE) Then
989
990 OPEN get_master_job(l_job_id, l_job_group_id);
991 LOOP
992 FETCH get_master_job INTO l_master_job_id;
993 Exit when get_master_job%NOTFOUND;
994
995 IF P_DEBUG_MODE = 'Y' THEN
996 log_message('Updating Denorm for jobs belongs to master job id = ' || l_master_job_id);
997 END IF;
998
999 update_all_jobs
1000 ( P_job_id => l_master_job_id
1001 ,P_job_level_old => l_job_level_old
1002 ,P_job_level_new => l_job_level_new
1003 ,x_return_status => x_return_status
1004 ,x_msg_data => x_msg_data
1005 ,x_msg_count => x_msg_count
1006 );
1007 END LOOP;
1008 CLOSE get_master_job;
1009
1010 Else
1011
1012 IF P_DEBUG_MODE = 'Y' THEN
1013 log_message('Updating Denorm for jobs belongs to master job, also PRJG job id = ' || l_job_id);
1014 END IF;
1015 update_all_jobs
1016 ( P_job_id => l_job_id
1017 ,P_job_level_old => l_job_level_old
1018 ,P_job_level_new => l_job_level_new
1019 ,x_return_status => x_return_status
1020 ,x_msg_data => x_msg_data
1021 ,x_msg_count => x_msg_count
1022 );
1023 End If;
1024
1025 EXCEPTION
1026 -- when no data found from the get_master_job cursor, then there is no
1027 -- mapping, do not do any updates
1028 WHEN NO_DATA_FOUND THEN
1029 null;
1030 WHEN OTHERS THEN -- Included WHEN OTHERS Block for 4537865
1031 -- 4537865 : RESET x_msg_count and x_msg_data also
1032 x_msg_count := 1 ;
1033 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1035 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
1036 p_procedure_name => 'perform_job_updates',
1037 p_error_text => x_msg_data );
1038 RAISE ;
1039 END perform_job_updates;
1040
1041
1042
1043 -- Main API for job level change, job mapping change
1044 -- This API will update the resource denorm records with the job level change of a job id.
1045 -- It depends on the type of job : whether it is in the Project Resource Job Group or not
1046 PROCEDURE update_job_level_dff
1047 ( P_job_id per_jobs.job_id%type
1048 ,P_job_level_old pa_resources_denorm.resource_job_level%type
1049 ,P_job_level_new pa_resources_denorm.resource_job_level%type
1050 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1051 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1052 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1053 ) IS
1054
1055 l_job_id PER_JOBS.JOB_ID%type;
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
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;
1070 l_job_level_new := P_job_level_new;
1071
1072 -- When Project Resource Job Group does not exist, we just use
1073 -- the job id and job level passed in and update the resource
1074 -- denorm records affected by this change
1075 IF PA_JOB_UTILS.Proj_Res_Job_Group_Exists(p_job_id => l_job_id) = 'N' THEN
1076 IF P_DEBUG_MODE = 'Y' THEN
1077 log_message('Proj_Res_Job_Group does not exist');
1078 log_message('Update Denorm for the Job Id and Job Level');
1079 END IF;
1080
1081 update_job_level_res_denorm
1082 ( P_job_id_old => l_job_id
1083 ,P_job_id_new => l_job_id
1084 ,P_job_level_old => l_job_level_old
1085 ,P_job_level_new => l_job_level_new
1086 ,x_return_status => x_return_status
1087 ,x_msg_data => x_msg_data
1088 ,x_msg_count => x_msg_count
1089 );
1090 ELSE
1091 -----------------------------------------------------------
1092 -- This is the case when the Proj_Res_Job_Group value exist
1093 -----------------------------------------------------------
1094 IF P_DEBUG_MODE = 'Y' THEN
1095 log_message('Proj_Res_Job_Group Exist');
1096 END IF;
1097
1098 -- get the job group id of the job id passed in
1099 l_job_group_id := PA_JOB_UTILS.get_job_group_id(l_job_id);
1100 IF P_DEBUG_MODE = 'Y' THEN
1101 log_message('Job Group Id = ' || l_job_group_id );
1102 END IF;
1103
1104 -- check whether the job group is the Proj_Res_Job_Group
1105 l_isPRJG := PA_JOB_UTILS.Is_Proj_Res_Job_Group
1106 ( p_job_id => l_job_id
1107 ,p_job_group_id => l_job_group_id);
1108
1109 -------------------------------------------------------------
1110 -- When l_isPRJG is 'Y', we need to get the master job id
1111 -- associated with the job id passed in from the job mapping
1112 -- and update all resource denorm records of the affected
1113 -- jobs
1114 -- Call procedure 'perform_job_updates' for this
1115 -- When the value is 'N', we not need to change anything
1116 -------------------------------------------------------------
1117
1118 If l_isPRJG = 'Y' Then
1119
1120 IF P_DEBUG_MODE = 'Y' THEN
1121 log_message('Job Id passed in belongs to PRJG job group');
1122 END IF;
1123
1124 perform_job_updates
1125 ( P_job_id => l_job_id
1126 ,P_job_level_old => l_job_level_old
1127 ,P_job_level_new => l_job_level_new
1128 ,P_job_group_id => l_job_group_id
1129 ,x_return_status => x_return_status
1130 ,x_msg_data => x_msg_data
1131 ,x_msg_count => x_msg_count
1132 );
1133
1134 End If;
1135
1136 END IF;
1137
1138 EXCEPTION
1139 -- when no data found from the get_master_job cursor, then there is no
1140 -- mapping, do not do any updates
1141 WHEN NO_DATA_FOUND THEN
1142 null;
1143 WHEN OTHERS THEN -- Included WHEN OTHERS Block for 4537865
1144 -- 4537865 : RESET x_msg_count and x_msg_data also
1145 x_msg_count := 1 ;
1146 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1148 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
1149 p_procedure_name => 'update_job_level_dff',
1150 p_error_text => x_msg_data );
1151 RAISE ;
1152 END update_job_level_dff;
1153
1154
1155
1156
1157
1158 -- This Procedure gets list of all the jobs which are afftected due to changes in
1159 -- grade_id in per_valid_grade entity
1160 PROCEDURE per_valid_grades_job_id
1161 (P_job_id IN per_jobs.job_id%type
1162 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1163 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1164 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1165
1166 v_return_status VARCHAR2(2000);
1167 v_error_message_code VARCHAR2(2000);
1168 v_job_group_id PER_JOBS.JOB_GROUP_ID%type;
1169 v_job_level_old NUMBER;
1170 v_job_level_new NUMBER;
1171 v_job_id PER_JOBS.JOB_ID%type;
1172 v_msg_data VARCHAR2(2000);
1173 v_msg_count NUMBER;
1174
1175 CURSOR get_job_ids(
1176 l_job_id per_jobs.job_id%type
1177 ) is
1178 SELECT l_job_id effected_job_id
1179 FROM sys.dual
1180 ,per_job_groups pjg
1181 WHERE pjg.master_flag = 'Y'
1182 AND pjg.job_group_id = get_job_group_id(l_job_id)
1183 UNION
1184 SELECT distinct pjr.from_job_id effected_job_id
1185 FROM pa_job_relationships pjr
1186 ,per_job_groups pjg
1187 WHERE pjg.master_flag = 'Y'
1188 AND pjr.to_job_id = l_job_id
1189 AND pjr.to_job_group_id = pjg.job_group_id
1190 UNION
1191 SELECT distinct pjr.to_job_id effected_job_id
1192 FROM pa_job_relationships pjr
1193 ,per_job_groups pjg
1194 WHERE pjg.master_flag = 'Y'
1195 AND pjr.from_job_id = l_job_id
1196 AND pjr.from_job_group_id = pjg.job_group_id
1197 UNION
1198 SELECT l_job_id effected_job_id
1199 FROM sys.dual
1200 ,per_job_groups pjg
1201 WHERE pjg.master_flag = 'N'
1202 AND pjg.job_group_id = get_job_group_id(l_job_id)
1203 AND NOT EXISTS (
1204 SELECT 'Y'
1205 FROM per_job_groups
1206 WHERE master_flag = 'Y'
1207 AND job_group_id = get_job_group_id(l_job_id)
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
1217 OPEN get_job_ids(P_job_id) ;
1218 LOOP
1219 fetch get_job_ids into v_job_id;
1220 Exit when get_job_ids%NOTFOUND;
1221 v_job_group_id := get_job_group_id(v_job_id);
1222 v_job_level_new := get_job_level(v_job_id,v_job_group_id);
1223 v_job_level_old := 0;
1224
1225 If v_job_id is NOT NULL then
1226 call_create_resource_denorm
1227 (P_job_id_old => v_job_id
1228 ,P_job_id_new => v_job_id
1229 ,P_job_level_old => v_job_level_old
1230 ,P_job_level_new => v_job_level_new
1231 ,x_return_status => x_return_status
1232 ,x_msg_data => x_msg_data
1233 ,x_msg_count => x_msg_count
1234 );
1235 End if;
1236
1237
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
1247 NULL;
1248
1249 WHEN OTHERS THEN
1250 -- 4537865 : RESET x_msg_count and x_msg_data also
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
1260 END per_valid_grades_job_id;
1261
1262 -- This Procedure gets list of all the jobs which are afftected due to changes in
1263 -- sequence(job level) in per grades entity
1264 PROCEDURE per_grades_job_id
1265 (P_grade_id IN per_grades.grade_id%type
1266 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1267 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1268 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1269 ) IS
1270
1271 v_return_status VARCHAR2(2000);
1272 v_error_message_code VARCHAR2(2000);
1273 v_job_group_id PER_JOBS.JOB_GROUP_ID%type;
1274 v_job_level_new NUMBER;
1275 v_job_level_old NUMBER;
1276 v_job_id PER_JOBS.JOB_ID%type;
1277 v_row_num NUMBER := 0;
1278 v_msg_data VARCHAR2(2000);
1279 v_msg_count NUMBER;
1280 CURSOR get_job_ids(l_grade_id per_grades.grade_id%type) is
1281 SELECT distinct pvg.job_id
1282 FROM per_valid_grades pvg
1283 ,per_job_groups pjg
1284 WHERE
1285 pvg.grade_id = l_grade_id
1286 AND pjg.master_flag = 'Y'
1287 AND pjg.job_group_id = get_job_group_id(pvg.job_id)
1288 UNION
1289 SELECT distinct pjr.from_job_id
1290 FROM per_valid_grades pvg
1291 ,pa_job_relationships pjr
1292 ,per_job_groups pjg
1293 WHERE pjg.master_flag = 'Y'
1294 AND pjr.to_job_id = pvg.job_id
1295 AND pjr.to_job_group_id = pjg.job_group_id
1296 AND pvg.grade_id = l_grade_id
1297 UNION
1298 SELECT distinct pjr.to_job_id
1299 FROM per_valid_grades pvg
1300 ,pa_job_relationships pjr
1301 ,per_job_groups pjg
1302 WHERE pjg.master_flag = 'Y'
1303 AND pjr.from_job_id = pvg.job_id
1304 AND pjr.from_job_group_id = pjg.job_group_id
1305 AND pvg.grade_id = l_grade_id
1306 UNION
1307 SELECT distinct pvg.job_id
1308 FROM per_valid_grades pvg
1309 ,per_job_groups pjg
1310 WHERE pjg.master_flag = 'N'
1311 AND pjg.job_group_id = get_job_group_id(pvg.job_id)
1312 AND pvg.grade_id = l_grade_id
1313 AND NOT EXISTS (
1314 SELECT 'Y'
1315 FROM per_job_groups
1316 WHERE master_flag = 'Y'
1317 AND job_group_id = get_job_group_id(pvg.job_id)
1318 );
1319
1320
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
1330 OPEN get_job_ids(P_grade_id) ;
1331 LOOP
1332 fetch get_job_ids into v_job_id;
1333 Exit when get_job_ids%NOTFOUND;
1334 v_job_group_id := get_job_group_id(v_job_id);
1335 v_job_level_new := get_job_level(v_job_id,v_job_group_id);
1336 v_job_level_old := 0;
1337 if v_job_id is NOT NULL then
1338 call_create_resource_denorm
1339 (P_job_id_old => v_job_id
1340 ,P_job_id_new => v_job_id
1341 ,P_job_level_old => v_job_level_old
1342 ,P_job_level_new => v_job_level_new
1343 ,x_return_status => x_return_status
1344 ,x_msg_data => x_msg_data
1345 ,x_msg_count => x_msg_count
1346 );
1347 End if;
1348
1349
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;
1359
1360 WHEN OTHERS THEN
1361 -- 4537865 : RESET x_msg_count and x_msg_data also
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
1371
1372 END per_grades_job_id;
1373
1374
1375
1376 -- This Procedure will get a list of all affected jobs due to change in the job mapping
1377 -- and then calls to PRM API GET_JOB_LEVEL in a loop which actually
1378 -- updates the levels in the resource denorm table.
1379 -- Whenever job mapping columns in pa_job_relationships updated,workflow will kickoff
1380 -- this api from the database trigger on table pa_job_relationships
1381 -- Pa_Job_Relationships Entity--
1382 -- IN Parameters
1383 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id -- INSERT
1384 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id,
1385 -- P_from_job_id_old,P_to_job_id_old -- UPDATE
1386 -- P_calling_mode,P_from_job_id_old,P_to_job_id_old,P_from_job_group_id,P_to_job_group_id -- DELETE
1387 PROCEDURE update_job_levels
1388 ( P_calling_mode IN VARCHAR2
1389 ,P_per_grades_grade_id IN per_grades.grade_id%type DEFAULT NULL
1390 ,P_per_grades_sequence_old IN NUMBER DEFAULT NULL
1391 ,P_per_grades_sequence_new IN NUMBER DEFAULT NULL
1392 ,P_per_valid_grade_job_id IN per_valid_grades.valid_grade_id%type DEFAULT NULL
1393 ,P_per_valid_grade_id_old IN per_grades.grade_id%type DEFAULT NULL
1394 ,P_per_valid_grade_id_new IN per_grades.grade_id%type DEFAULT NULL
1395 ,P_from_job_id_old IN pa_job_relationships.from_job_id%type DEFAULT NULL
1396 ,P_from_job_id_new IN pa_job_relationships.from_job_id%type DEFAULT NULL
1397 ,P_to_job_id_old IN pa_job_relationships.to_job_id%type DEFAULT NULL
1398 ,P_to_job_id_new IN pa_job_relationships.to_job_id%type DEFAULT NULL
1399 ,P_from_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
1400 ,P_to_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
1401 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1402 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1403 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1404 )IS
1405
1406 v_return_status VARCHAR2(2000);
1407 v_error_message_code VARCHAR2(2000);
1408 v_grade_id per_grades.grade_id%type;
1409 v_msg_data VARCHAR2(2000);
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
1419 If (P_from_job_group_id is NOT NULL and P_to_job_group_id is NOT NULL) and
1420 (P_from_job_id_old is NOT NULL or P_from_job_id_new is NOT NULL or
1421 P_to_job_id_old is NOT NULL or P_to_job_id_new is NOT NULL ) then
1422 pa_job_relation_job_id
1423 (p_calling_mode => p_calling_mode
1424 ,P_from_job_id_old => P_from_job_id_old
1425 ,P_from_job_id_new => P_from_job_id_new
1426 ,P_to_job_id_old => P_to_job_id_old
1427 ,P_to_job_id_new => P_to_job_id_new
1428 ,P_from_job_group_id => P_from_job_group_id
1429 ,P_to_job_group_id => P_to_job_group_id
1430 ,x_return_status => x_return_status
1431 ,x_msg_data => x_msg_data
1432 ,x_msg_count => x_msg_count
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
1442 NULL;
1443
1444 WHEN OTHERS THEN
1445 -- 4537865 : RESET x_msg_count and x_msg_data also
1446 x_msg_count := 1 ;
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;
1456
1457
1458 -- This Procedure updates the pa_resource_OU and set the resources
1459 -- end date active to sysdate when pa_all_organizations.inactive_date
1460 -- is updated.
1461 PROCEDURE Update_OU_resource(P_default_OU_old IN Pa_all_organizations.org_id%type
1462 ,P_default_OU_new IN Pa_all_organizations.org_id%type
1463 ,P_resource_id IN Pa_Resources_denorm.resource_id%type
1464 default NULL
1465 ,P_person_id IN Pa_Resources_denorm.person_id%type
1466 default NULL
1467 ,P_start_date IN Date default NULL
1468 ,P_end_date_old IN Date default NULL
1469 ,P_end_date_new IN Date default NULL
1470 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1471 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1472 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1473 )IS
1474
1475 v_return_status VARCHAR2(2000);
1476 v_error_message_code VARCHAR2(2000);
1477 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
1478 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
1479 v_msg_data VARCHAR2(2000);
1480 v_msg_count NUMBER;
1481 CURSOR res_denorm_recs IS
1482 SELECT resource_effective_start_date,
1483 resource_effective_end_date
1484 FROM pa_resources_denorm
1485 WHERE person_id = p_person_id
1486 AND nvl(p_end_date_new, sysdate) >= resource_effective_start_date
1487 AND resource_effective_start_date >= p_start_date
1488 AND resource_effective_end_date <= p_end_date_old
1489 AND resource_effective_end_date =
1490 (Select max(resource_effective_end_date)
1491 from pa_resources_denorm rd2
1492 where rd2.person_id = p_person_id)
1493 -- bug#10235228
1494 ;
1495 BEGIN
1496
1497 v_resource_rec_old.resource_org_id := p_default_OU_old;
1498 v_resource_rec_old.person_id := p_person_id;
1499 v_resource_rec_new.resource_org_id := p_default_OU_new;
1500 v_resource_rec_new.person_id := p_person_id;
1501
1502
1503
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
1513 v_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
1514 v_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
1515
1516
1517 PA_RESOURCE_PVT.update_resource_denorm (
1518 p_resource_denorm_old_rec => v_resource_rec_old
1519 ,p_resource_denorm_new_rec => v_resource_rec_new
1520 ,x_return_status => x_return_status
1521 ,x_msg_data => x_msg_data
1522 ,x_msg_count => x_msg_count );
1523
1524 END LOOP;
1525
1526 --If new end date is passed for this assignment (from make_resource_inactive api)
1527 If P_end_date_new is NOT NULL then
1528 Update_EndDate(
1529 p_person_id => p_person_id,
1530 p_old_start_date => p_start_date,
1531 p_new_start_date => p_start_date,
1532 p_old_end_date => p_end_date_old,
1533 p_new_end_date => p_end_date_new,
1534 x_return_status => x_return_status,
1535 x_msg_data => x_msg_data,
1536 x_msg_count => x_msg_count);
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
1546 NULL;
1547
1548 WHEN OTHERS THEN -- Set the exception Message and the stack
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
1558
1559 -- This Procedure is called from workflow process to update/create resources in projects
1560 -- the workflow would be kicked of by the database trigger on table Hr_Organization_Information
1561 -- and Pa_All_Organization entities.
1562 -- 1.Whenever the default operating Unit which is
1563 -- stored in Hr_Organization_Information.Org_information1 changes / modified ,the
1564 -- trigger kicks off the workflow and calls this api to Update the Pa_Resource_OU
1565 -- entity.
1566 -- 2.Whenever the new record is inserted into Pa_All_Organizations with Pa_Org_Use_type
1567 -- is of type 'Expenditure' or the exisitng record in Pa_all_Organiations
1568 -- is updated with inactive_date then trigger fires and kicks of the workflow,calls this
1569 -- api to Update the Pa_Resource_OU.
1570 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit or rollback
1571 -- after every resource in the loop
1572 PROCEDURE Default_OU_Change
1573 ( P_calling_mode IN VARCHAR2
1574 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
1575 ,P_Default_OU_new IN Hr_Organization_Information.Org_Information1%type
1576 ,P_Default_OU_old IN Hr_Organization_Information.Org_Information1%type
1577 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1578 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1579 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1580 ) IS
1581 PRAGMA AUTONOMOUS_TRANSACTION;
1582
1583 v_return_status VARCHAR2(2000);
1584 v_error_message_code VARCHAR2(2000);
1585 v_assn_start_date Per_all_assignments_f.Effective_Start_Date%type;
1586 v_assn_end_date Per_all_assignments_f.Effective_End_Date%type;
1587 v_Person_id Per_all_assignments_f.person_id%type;
1588 v_Default_OU Hr_Organization_Information.Org_Information1%type;
1589 v_commit VARCHAR2(200) := FND_API.G_FALSE;
1590 -- set to false since the api is being called from trigger
1591 v_validate_only VARCHAR2(200) := FND_API.G_FALSE;
1592 v_internal VARCHAR2(1) := 'Y';
1593 v_individual VARCHAR2(1) := 'Y'; -- to process single resource in loop
1594 v_resource_type VARCHAR2(15):= 'EMPLOYEE';
1595 v_org_type VARCHAR2(15):= 'YES'; --'EXPENDITURES';
1596 v_msg_data VARCHAR2(2000);
1597 v_msg_count NUMBER;
1598 v_dummy NUMBER;
1599 L_API_VERSION CONSTANT NUMBER := 1.0;
1600 v_process_further BOOLEAN := FALSE;
1601 -- get all the resources who belongs to Expenditure type of organizaion and
1602 -- belongs to Expenditure Hierarchy ,Active_Assign, and of Primary assignment type
1603 -- is 'Y' and default OU inactive date is NUll
1604
1605 CURSOR get_all_resource(l_organization_id Hr_Organization_Information.Organization_id%type) is
1606
1607 SELECT distinct
1608 ind.person_id
1609 ,ind.assignment_start_date
1610 ,ind.assignment_end_date
1611 ,to_number(hoi.org_information1) default_OU
1612 FROM pa_r_project_resources_ind_v ind
1613 ,hr_organization_information hoi
1614 WHERE ind.organization_id =
1615 /* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
1616 AND ind.assignment_end_date >= sysdate
1617 AND hoi.organization_id = l_organization_id
1618 AND hoi.org_information_context = 'Exp Organization Defaults'
1619 AND ind.organization_id = l_organization_id -- 4898509
1620 ORDER BY ind.person_id,ind.assignment_start_date ;
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
1630 -- check whether the new OU is a valid exp OU or not
1631 -- if not then donot process further
1632
1633 If P_Default_OU_new is NOT NULL then
1634
1635 v_default_OU := P_Default_OU_new;
1636 Else
1637 v_default_OU := -9999;
1638 -- For bug 5330402 Added call to make_resource_inactive
1639 -- This will take sysdate as inactive date
1640 -- Return after the call, as no further processing is required.
1641 make_resource_inactive
1642 (P_calling_mode => 'UPDATE'
1643 ,P_Organization_id => P_Organization_id
1644 ,P_Default_OU => P_Default_OU_old
1645 ,P_Default_OU_NEW => v_default_OU
1646 ,P_inactive_date => trunc(sysdate)
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
1656 (p_org_id => v_default_OU
1657 ,x_return_status => v_return_status
1658 ,x_error_message_code => v_error_message_code
1659 );
1660
1661 If v_return_status <> FND_API.G_RET_STS_SUCCESS then
1662 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1663 x_msg_data := v_error_message_code;
1664 v_process_further := FALSE;
1665 PA_UTILS.add_message(p_app_short_name => 'PA',
1666 p_msg_name => v_error_message_code);
1667 x_msg_count := fnd_msg_pub.count_msg;
1668
1669 Elsif v_return_status = FND_API.G_RET_STS_SUCCESS then
1670
1671 v_process_further := TRUE;
1672
1673 End if;
1674
1675 -- Start Bug : 4656855
1676 IF (check_pjr_default_ou(P_Organization_id, v_default_OU) <> 'Y') THEN
1677 RETURN;
1678 END IF;
1679 -- End Bug : 4656855
1680
1681 If (v_process_further) then
1682 open get_all_resource(P_Organization_id);
1683 LOOP
1684 fetch get_all_resource into
1685 v_person_id
1686 ,v_assn_start_date
1687 ,v_assn_end_date
1688 ,v_default_OU ;
1689 Exit when get_all_resource%NOTFOUND;
1690
1691 -- call the check ou change api to update records in pa_resource_OU
1692 -- for each resource belongs to this updated OU in Hr_Organization_defaults
1693 If P_calling_mode = 'UPDATE' then
1694
1695 -- check for whether the default OU is changed if so call
1696 -- check OU change api to update the resource OU entity
1697 If (NVL(P_default_OU_old,-99) <> nvl(P_default_OU_new,-99)) then
1698 -- if OU is updated then call resource denorm api to
1699 -- reflect the changes in pa_resources_denorm entity
1700 If v_person_id is NOT NULL then
1701
1702
1703 Update_OU_resource
1704 (P_default_OU_old => p_default_OU_old
1705 ,p_default_OU_new => p_default_OU_new
1706 ,P_person_id => v_person_id
1707 ,P_start_date => v_assn_start_date
1708 ,P_end_date_old => v_assn_end_date
1709 ,x_return_status => x_return_status
1710 ,x_msg_data => x_msg_data
1711 ,x_msg_count => x_msg_count
1712 );
1713
1714 -- call forecast api to regenerate the forcast items
1715 -- for the person with the organization OU change
1716 -- update forecast data for unassigned and assigned time
1717 PA_FORECASTITEM_PVT.Create_Forecast_Item
1718 (
1719 p_person_id => v_person_id
1720 ,p_start_date => v_assn_start_date
1721 ,p_end_date => v_assn_end_date
1722 ,p_process_mode => 'GENERATE'
1723 ,x_return_status => x_return_status
1724 ,x_msg_count => x_msg_count
1725 ,x_msg_data => x_msg_data
1726 ) ;
1727
1728 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1729 COMMIT;
1730 else
1731 ROLLBACK;
1732 end if;
1733
1734 End if;
1735
1736
1737 Elsif P_default_OU_old is NULL and P_default_OU_new is NOT NULL then
1738 -- when new OU is assigned to existing organization it must pull all
1739 -- resources belongs to this OU so call create resource api
1740
1741 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
1742 P_API_VERSION => L_API_VERSION
1743 ,P_COMMIT => v_commit
1744 ,P_VALIDATE_ONLY => v_validate_only
1745 ,P_INTERNAL => v_internal
1746 ,P_PERSON_ID => v_person_id
1747 ,P_INDIVIDUAL => v_individual
1748 ,P_RESOURCE_TYPE => v_resource_type
1749 ,X_RETURN_STATUS => x_return_status
1750 ,X_RESOURCE_ID => v_dummy
1751 ,X_MSG_COUNT => x_msg_count
1752 ,X_MSG_DATA => x_msg_data
1753 );
1754
1755 -- call this procedure to update the forecast data for
1756 -- assigned time ONLY for this resource
1757 -- this is called only if create_resource is a success
1758 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1759 PA_FORECASTITEM_PVT.Create_Forecast_Item
1760 ( p_person_id => v_person_id
1761 ,p_start_date => null
1762 ,p_end_date => null
1763 ,p_process_mode => 'GENERATE_ASGMT'
1764 ,x_return_status => x_return_status
1765 ,x_msg_count => x_msg_count
1766 ,x_msg_data => x_msg_data
1767 ) ;
1768
1769 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1770 COMMIT;
1771 else
1772 ROLLBACK;
1773 end if;
1774 else
1775 ROLLBACK;
1776 end if;
1777
1778 End if;
1779
1780 /* cannot raise - because will be out from the loop and will not process other records
1781 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1782 RAISE FND_API.G_EXC_ERROR;
1783 END IF;
1784 */
1785
1786 Elsif P_calling_mode = 'INSERT' then
1787 -- the P_calling_mode is 'INSERT'
1788 -- this api is called to populate resources whenever a new record is added in
1789 -- in Hr_organizatioin_information entity
1790 -- or due to insert in pa_all_organizations entity
1791
1792 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
1793 P_API_VERSION => L_API_VERSION
1794 ,P_COMMIT => v_commit
1795 ,P_VALIDATE_ONLY => v_validate_only
1796 ,P_INTERNAL => v_internal
1797 ,P_PERSON_ID => v_person_id
1798 ,P_INDIVIDUAL => v_individual
1799 ,P_RESOURCE_TYPE => v_resource_type
1800 ,X_RETURN_STATUS => x_return_status
1801 ,X_RESOURCE_ID => v_dummy
1802 ,X_MSG_COUNT => x_msg_count
1803 ,X_MSG_DATA => x_msg_data
1804 );
1805
1806
1807 -- it is also necessary to call forecast item here,
1808 -- because this is also called from project_organization_change
1809 -- when p_calling_mode is insert
1810 -- A person can belong to Org1(belong to Exp Hier), then the org
1811 -- was changed to Org2 (not belong to Exp Hier)
1812 -- If Org2 is inserted and belong to Exp Hier, we need to fix
1813 -- the assigned time for this person when he/she was with Org1
1814 -- So, call this procedure to update the forecast data for
1815 -- assigned time ONLY for this resource
1816 -- this is called only if create_resource is a success
1817 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1818 PA_FORECASTITEM_PVT.Create_Forecast_Item
1819 ( p_person_id => v_person_id
1820 ,p_start_date => null
1821 ,p_end_date => null
1822 ,p_process_mode => 'GENERATE_ASGMT'
1823 ,x_return_status => x_return_status
1824 ,x_msg_count => x_msg_count
1825 ,x_msg_data => x_msg_data
1826 ) ;
1827
1828 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1829 COMMIT;
1830 else
1831 ROLLBACK;
1832 end if;
1833 else
1834 ROLLBACK;
1835 end if;
1836
1837
1838 /* cannot raise - because will be out from the loop and will not process other records
1839 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842 */
1843
1844 End if;
1845 END LOOP;
1846 close get_all_resource;
1847
1848 --set the final return status to SUCCESS after loop
1849 x_return_status := FND_API.G_RET_STS_SUCCESS;
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;
1859 WHEN OTHERS THEN
1860 -- 4537865 : RESET x_msg_count and x_msg_data also
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;
1870
1871
1872 -- This Procedure is called from workflow process to update/create resources in projects
1873 -- The workflow would be kicked of by the database trigger on table Hr_Organization_Information
1874 -- It will update the job levels information if the Project Resource Job Group is changed
1875 -- Created by adabdull 2-JAN-2002
1876 PROCEDURE Proj_Res_Job_Group_Change
1877 ( p_calling_mode IN VARCHAR2
1878 ,p_organization_id IN Hr_Organization_Information.Organization_id%type
1879 ,p_proj_job_group_new IN Hr_Organization_Information.Org_Information1%type
1880 ,p_proj_job_group_old IN Hr_Organization_Information.Org_Information1%type
1881 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1882 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1883 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1884 ) IS
1885
1886 l_job_id per_jobs.job_id%type;
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
1896 WHERE job_group_id = l_proj_job_group_new
1897 AND business_group_id = p_organization_id;
1898
1899 CURSOR get_old_job_ids IS
1900 SELECT job_id
1901 FROM per_jobs
1902 WHERE job_group_id = l_proj_job_group_old
1903 AND business_group_id = p_organization_id;
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);
1913
1914 IF P_DEBUG_MODE = 'Y' THEN
1915 log_message('p_calling_mode = ' || p_calling_mode);
1916 log_message('old proj res job group value = ' || l_proj_job_group_old);
1917 log_message('new proj res job group value = ' || l_proj_job_group_new);
1918 END IF;
1919
1920 IF p_calling_mode = 'INSERT' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
1921
1922 -- When Insert, we update all resource denorm records with job_id that
1923 -- belongs to the new Project Resource Job Group Id
1924 OPEN get_new_job_ids;
1925 LOOP
1926
1927 FETCH get_new_job_ids INTO l_job_id;
1928 Exit when get_new_job_ids%NOTFOUND;
1929
1930 l_job_level := PA_JOB_UTILS.get_job_level(l_job_id);
1931 IF P_DEBUG_MODE = 'Y' THEN
1932 log_message('Job id to set level ' || l_job_level || ' = ' || l_job_id);
1933 END IF;
1934
1935 update_job_level_dff
1936 (P_job_id => l_job_id
1937 ,P_job_level_old => NULL
1938 ,P_job_level_new => l_job_level
1939 ,x_return_status => x_return_status
1940 ,x_msg_data => x_msg_data
1941 ,x_msg_count => x_msg_count
1942 );
1943 END LOOP;
1944 CLOSE get_new_job_ids;
1945
1946 ELSIF p_calling_mode = 'UPDATE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
1947
1948 -- When update we have to set the resource denorm records job level to NULL for job id
1949 -- that belongs to the old Project Resource Job Group Id
1950 OPEN get_old_job_ids;
1951 LOOP
1952
1953 FETCH get_old_job_ids INTO l_job_id;
1954 Exit when get_old_job_ids%NOTFOUND;
1955
1956 l_job_level := NULL;
1957
1958 IF P_DEBUG_MODE = 'Y' THEN
1959 log_message('Job id to set level Null = ' || l_job_id);
1960 END IF;
1961
1962 l_job_group_id := PA_JOB_UTILS.get_job_group_id(l_job_id);
1963
1964 perform_job_updates
1965 (P_job_id => l_job_id
1966 ,P_job_level_old => NULL
1967 ,P_job_level_new => l_job_level
1968 ,P_job_group_id => l_job_group_id
1969 ,x_return_status => x_return_status
1970 ,x_msg_data => x_msg_data
1971 ,x_msg_count => x_msg_count
1972 );
1973
1974 END LOOP;
1975 CLOSE get_old_job_ids;
1976
1977
1978 -- Also, when Update, we update all resource denorm records with job_id that
1979 -- belongs to the new Project Resource Job Group Id
1980
1981 OPEN get_new_job_ids;
1982 LOOP
1983
1984 FETCH get_new_job_ids INTO l_job_id;
1985 Exit when get_new_job_ids%NOTFOUND;
1986
1987 l_job_level := PA_JOB_UTILS.get_job_level(l_job_id);
1988 IF P_DEBUG_MODE = 'Y' THEN
1989 log_message('Job id to set level ' || l_job_level || ' = ' || l_job_id);
1990 END IF;
1991
1992 update_job_level_dff
1993 (P_job_id => l_job_id
1994 ,P_job_level_old => NULL
1995 ,P_job_level_new => l_job_level
1996 ,x_return_status => x_return_status
1997 ,x_msg_data => x_msg_data
1998 ,x_msg_count => x_msg_count
1999 );
2000
2001 END LOOP;
2002 CLOSE get_new_job_ids;
2003
2004
2005 ELSIF p_calling_mode = 'DELETE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
2006
2007 -- When Delete, we have to set the resource denorm records job level to NULL for job id
2008 -- that belongs to the old Project Resource Job Group Id
2009
2010 OPEN get_old_job_ids;
2011 LOOP
2012
2013 FETCH get_old_job_ids INTO l_job_id;
2014 Exit when get_old_job_ids%NOTFOUND;
2015
2016 l_job_level := NULL;
2017 IF P_DEBUG_MODE = 'Y' THEN
2018 log_message('Job id to set level Null = ' || l_job_id);
2019 END IF;
2020
2021 update_job_level_dff
2022 (P_job_id => l_job_id
2023 ,P_job_level_old => NULL
2024 ,P_job_level_new => l_job_level
2025 ,x_return_status => x_return_status
2026 ,x_msg_data => x_msg_data
2027 ,x_msg_count => x_msg_count
2028 );
2029
2030 END LOOP;
2031 CLOSE get_old_job_ids;
2032
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;
2042 WHEN OTHERS THEN
2043 -- 4537865 : RESET x_msg_count and x_msg_data also
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;
2053
2054 -- This API pulls all resources into PA from HR for a given organization
2055 -- Created by virangan 11-JUN-2001
2056 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit after every
2057 -- resource in the loop
2058 PROCEDURE pull_resources( p_organization_id IN pa_all_organizations.organization_id%type
2059 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2060 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2061 ,x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
2062 IS
2063 PRAGMA AUTONOMOUS_TRANSACTION;
2064 l_commit VARCHAR2(200) := FND_API.G_FALSE;
2065 -- set to false since the api is being called from trigger
2066 l_validate_only VARCHAR2(200) := FND_API.G_FALSE;
2067 l_internal VARCHAR2(1) := 'Y';
2068 l_individual VARCHAR2(1) := 'Y'; -- to process single resource in loop
2069 l_resource_type VARCHAR2(15):= 'EMPLOYEE';
2070 l_Person_id Per_all_assignments_f.person_id%type;
2071 l_return_status VARCHAR2(2000);
2072 l_msg_data VARCHAR2(2000);
2073 l_msg_count NUMBER;
2074 l_dummy NUMBER;
2075 L_API_VERSION CONSTANT NUMBER := 1.0;
2076
2077 CURSOR get_all_resource(l_organization_id Hr_Organization_Information.Organization_id%type) is
2078
2079 SELECT distinct ind.person_id
2080 FROM pa_r_project_resources_ind_v ind
2081 ,hr_organization_information hoi
2082 WHERE ind.organization_id =
2083 /* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
2084 AND ind.assignment_end_date >= sysdate
2085 AND hoi.organization_id = l_organization_id
2086 AND hoi.org_information_context = 'Exp Organization Defaults'
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
2096 fetch get_all_resource into
2097 l_person_id;
2098 Exit when get_all_resource%NOTFOUND;
2099
2100 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
2101 P_API_VERSION => L_API_VERSION
2102 ,P_COMMIT => l_commit
2103 ,P_VALIDATE_ONLY => l_validate_only
2104 ,P_INTERNAL => l_internal
2105 ,P_PERSON_ID => l_person_id
2106 ,P_INDIVIDUAL => l_individual
2107 ,P_RESOURCE_TYPE => l_resource_type
2108 ,X_RETURN_STATUS => l_return_status
2109 ,X_RESOURCE_ID => l_dummy
2110 ,X_MSG_COUNT => l_msg_count
2111 ,X_MSG_DATA => l_msg_data );
2112
2113 -- call this procedure to update the forecast data for
2114 -- assigned time ONLY for this resource
2115 -- pass null to start date and end date
2116 -- this is called only if create_resource is a success
2117 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
2118 PA_FORECASTITEM_PVT.Create_Forecast_Item(
2119 p_person_id => l_person_id
2120 ,p_start_date => null
2121 ,p_end_date => null
2122 ,p_process_mode => 'GENERATE_ASGMT'
2123 ,x_return_status => l_return_status
2124 ,x_msg_count => l_msg_count
2125 ,x_msg_data => l_msg_data
2126 ) ;
2127
2128 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2129 COMMIT;
2130 else
2131 ROLLBACK;
2132 end if;
2133 else
2134 ROLLBACK;
2135 end if;
2136
2137 END LOOP;
2138 close get_all_resource;
2139
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
2158 END pull_resources;
2159
2160
2161 -- This API will be called from workflow process to update/create resources in projects.
2162 -- The workflow would be kicked of by the database trigger on pa_all_organization entity
2163 -- whenever a inactive_date in pa_all_organization is updated this api get kicked of by the
2164 -- workflow.
2165 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit after every
2166 -- resource in the loop
2167 PROCEDURE make_resource_inactive
2168 (P_calling_mode IN VARCHAR2
2169 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
2170 ,P_Default_OU IN pa_all_organizations.org_id%type
2171 ,P_inactive_date IN pa_all_organizations.inactive_date%type
2172 ,P_Default_OU_NEW IN pa_all_organizations.org_id%type DEFAULT NULL -- Added for bug 5330402
2173 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2174 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2175 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2176 ) IS
2177 PRAGMA AUTONOMOUS_TRANSACTION;
2178 v_return_status VARCHAR2(2000);
2179 v_error_message_code VARCHAR2(2000);
2180 v_assn_start_date Per_all_assignments_f.Effective_Start_Date%type;
2181 v_assn_end_date Per_all_assignments_f.Effective_End_Date%type;
2182 v_Person_id Per_all_assignments_f.person_id%type;
2183 v_resource_id Pa_Resource_txn_attributes.resource_id%type;
2184 v_Default_OU Hr_Organization_Information.Org_Information1%type;
2185 v_msg_data VARCHAR2(2000);
2186 v_msg_count NUMBER;
2187
2188 -- get all the resources who are employees and belongs to expenditure
2189 -- organizations and in the expenditure organization hierarchy
2190 -- and have a primary assignment (Active assignment) and have assigned
2191 -- to default OU
2192
2193 -- Bug 4347907 - change to base HR tables and remove nvl on dates
2194 --MOAC changes: bug 4363092: removed nvl used with org_id
2195 CURSOR get_all_inactive_resource
2196 IS
2197 SELECT
2198 distinct
2199 assn.person_id
2200 ,assn.effective_start_date
2201 ,assn.effective_end_date
2202 , res.resource_id
2203 , hrinf.org_information1
2204 FROM per_all_assignments_f assn
2205 , hr_organization_information hrinf
2206 /* , per_person_types pertypes Commented for bug#2781713 */
2207 , per_assignment_status_types pastype
2208 , pa_resource_txn_attributes res
2209 , pa_all_organizations allorgs
2210 , per_all_people_f pep
2211 WHERE
2212 assn.assignment_status_type_id = pastype.assignment_status_type_id
2213 AND assn.person_id = res.person_id
2214 AND assn.primary_flag = 'Y'
2215 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2216 AND assn.organization_id = allorgs.organization_id
2217 AND assn.organization_id = hrinf.organization_id
2218 AND assn.effective_start_date BETWEEN pep.effective_start_date
2219 AND pep.effective_end_date
2220 AND assn.effective_end_date >= trunc(sysdate)
2221 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2222 AND hrinf.org_information_context = 'Exp Organization Defaults'
2223 /* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
2224 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2225 /* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
2226 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2227 pep.current_npw_flag = 'Y') -- CWK Changes
2228 AND pep.person_id = assn.person_id
2229 AND allorgs.organization_id = P_organization_id
2230 AND allorgs.org_id = P_default_OU
2231 AND allorgs.pa_org_use_type = 'EXPENDITURES'
2232 AND allorgs.inactive_date is Not null
2233 AND (allorgs.organization_id,allorgs.org_id) = (
2234 SELECT exporg.organization_id, exporg.org_id
2235 FROM pa_all_organizations exporg
2236 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2237 AND exporg.inactive_date is Not null
2238 AND exporg.organization_id = allorgs.organization_id
2239 AND exporg.org_id = allorgs.org_id
2240 AND rownum = 1 );
2241
2242 /* -- Added for bug 5330402
2243 cursor get_all_inactive_resource_org is same as
2244 get_all_inactive_resource but commented out inactive date condition in where clause
2245 */
2246 CURSOR get_all_inactive_resource_org
2247 IS
2248 SELECT
2249 distinct
2250 assn.person_id
2251 ,assn.effective_start_date
2252 ,assn.effective_end_date
2253 , res.resource_id
2254 -- , hrinf.org_information1
2255 FROM per_all_assignments_f assn
2256 , hr_organization_information hrinf
2257 /* , per_person_types pertypes Commented for bug#2781713 */
2258 , per_assignment_status_types pastype
2259 , pa_resource_txn_attributes res
2260 , pa_all_organizations allorgs
2261 , per_all_people_f pep
2262 WHERE
2263 assn.assignment_status_type_id = pastype.assignment_status_type_id
2264 AND assn.person_id = res.person_id
2265 AND assn.primary_flag = 'Y'
2266 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2267 AND assn.organization_id = allorgs.organization_id
2268 AND assn.organization_id = hrinf.organization_id
2269 AND assn.effective_start_date BETWEEN pep.effective_start_date
2270 AND pep.effective_end_date
2271 AND assn.effective_end_date >= trunc(sysdate)
2272 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2273 AND hrinf.org_information_context = 'Exp Organization Defaults'
2274 /* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
2275 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2276 /* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
2277 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2278 pep.current_npw_flag = 'Y') -- CWK Changes
2279 AND pep.person_id = assn.person_id
2280 AND allorgs.organization_id = P_organization_id
2281 AND allorgs.org_id = P_default_OU -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
2282 AND allorgs.pa_org_use_type = 'EXPENDITURES'
2283 -- AND allorgs.inactive_date is Not null
2284 AND (allorgs.organization_id,allorgs.org_id) = ( -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
2285 SELECT exporg.organization_id, exporg.org_id
2286 FROM pa_all_organizations exporg
2287 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2288 -- AND exporg.inactive_date is Not null
2289 AND exporg.organization_id = allorgs.organization_id
2290 AND exporg.org_id = allorgs.org_id -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
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);
2300 /* bug 5330402
2301 P_Default_OU_NEW will be null when called for orghierarchy update
2302 P_Default_OU_NEW will not be null when called for nulling out default OU
2303 */
2304 IF p_inactive_date is NOT NULL and p_calling_mode = 'UPDATE' then
2305 if P_Default_OU_NEW is null then
2306 open get_all_inactive_resource;
2307 else
2308 open get_all_inactive_resource_org;
2309 end if;
2310 LOOP
2311 if P_Default_OU_NEW is null then --bug 5330402
2312 fetch get_all_inactive_resource into
2313 v_person_id
2314 ,v_assn_start_date
2315 ,v_assn_end_date
2316 ,v_resource_id
2317 ,v_default_OU;
2318 Exit when get_all_inactive_resource%NOTFOUND;
2319 else
2320 fetch get_all_inactive_resource_org into
2321 v_person_id
2322 ,v_assn_start_date
2323 ,v_assn_end_date
2324 ,v_resource_id;
2325 Exit when get_all_inactive_resource_org%NOTFOUND;
2326 v_default_OU := P_Default_OU; --bug 5330402 setting OU to old value though currently it is null
2327 end if;
2328 If v_person_id is NOT NULL then
2329
2330 --dbms_output.put_line('Calling Update OU Resource');
2331 -- update the resource denorm with end date the resources
2332 Update_OU_resource (
2333 P_default_OU_old => v_default_OU
2334 ,p_default_OU_new => v_default_OU
2335 ,P_person_id => v_person_id
2336 ,P_start_date => v_assn_start_date
2337 ,P_end_date_old => v_assn_end_date
2338 ,P_end_date_new => p_inactive_date
2339 ,x_return_status => x_return_status
2340 ,x_msg_data => x_msg_data
2341 ,x_msg_count => x_msg_count );
2342
2343 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2344 COMMIT;
2345 else
2346 ROLLBACK;
2347 end if;
2348
2349 End if;
2350
2351 END LOOP;
2352 if P_Default_OU_NEW is null then --bug 5330402
2353 close get_all_inactive_resource;
2354 else
2355 close get_all_inactive_resource_org;
2356 end if;
2357
2358 --set the final return status to SUCCESS after loop
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
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 ;
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
2377
2378 END make_resource_inactive;
2379
2380
2381 -- This API makes calls to PA_FORECASTITEM_PVT.Create_Forecast_Item api
2382 -- which will generate or update the forecast items
2383 -- This API performs commit and rollback because it is only called by
2384 -- per_job_extra_billability, which is an autonomous transaction
2385 -- This will not affect the workflow process when doing any commit or rollback
2386 PROCEDURE call_forcast_api
2387 (P_table_name IN VARCHAR2
2388 ,P_person_id IN PER_ALL_ASSIGNMENTS_F.PERSON_ID%TYPE default NULL
2389 ,P_Job_id IN per_jobs.job_id%type default NULL
2390 ,P_billable_flag IN VARCHAR2 default NULL
2391 ,P_organization_id IN Hr_organization_information.organization_id%type default NULL
2392 ,p_start_date IN date default NULL
2393 ,P_end_date IN date default NULL
2394 ,P_resource_OU IN NUMBER default NULL
2395 ,P_resource_type IN VARCHAR2 default NULL
2396 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2397 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2398 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2399 ) IS
2400
2401 v_person_id PER_ALL_ASSIGNMENTS_F.PERSON_ID%TYPE;
2402 v_start_date Date;
2403 v_end_date Date;
2404
2405 --- This cursor picks all the persons who are Employees category and
2406 -- belongs to Expenditure Hierarchy
2407 -- and belongs to job id = paremeter
2408 Cursor person_jobs(l_job_id per_jobs.job_id%type)is
2409 SELECT distinct
2410 assn.person_id
2411 ,assn.effective_start_date
2412 ,assn.effective_end_date
2413 FROM per_all_assignments_f assn
2414 , hr_organization_information hrinf
2415 /* , per_person_types pertypes Commented for Bug#2781713 */
2416 , per_assignment_status_types pastype
2417 , per_all_people_f pep
2418 WHERE
2419 assn.assignment_status_type_id = pastype.assignment_status_type_id
2420 AND assn.primary_flag = 'Y'
2421 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2422 AND assn.job_id = l_job_id
2423 AND assn.organization_id = hrinf.organization_id
2424 AND assn.effective_start_date BETWEEN pep.effective_start_date
2425 AND pep.effective_end_date
2426 AND assn.effective_end_date >= trunc(sysdate)
2427 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2428 AND hrinf.org_information_context = 'Exp Organization Defaults'
2429 /* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
2430 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2431 /* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
2432 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2433 pep.current_npw_flag = 'Y') -- CWK Changes
2434 AND pep.person_id = assn.person_id
2435 AND assn.organization_id =
2436 (SELECT exporg.organization_id
2437 FROM pa_all_organizations exporg
2438 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2439 AND exporg.inactive_date is null
2440 AND exporg.organization_id = assn.organization_id
2441 AND rownum = 1 )
2442 ORDER BY 1,2;
2443
2444 -- This cursor picks all the persons who are Employees and belongs to the
2445 -- expenditure hierarchy and belongs to organzation where organization_id = l_org_id
2446 CURSOR person_orgs(l_org_id Hr_Organization_Information.Organization_id%type) is
2447 SELECT distinct
2448 assn.person_id
2449 ,assn.effective_start_date
2450 ,assn.effective_end_date
2451 FROM per_all_assignments_f assn
2452 , hr_organization_information hrinf
2453 /* , per_person_types pertypes Commented for Bug#2781713 */
2454 , per_assignment_status_types pastype
2455 , per_all_people_f pep
2456 WHERE
2457 assn.assignment_status_type_id = pastype.assignment_status_type_id
2458 AND assn.primary_flag = 'Y'
2459 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2460 AND assn.organization_id = l_org_id
2461 AND assn.organization_id = hrinf.organization_id
2462 AND assn.effective_start_date BETWEEN pep.effective_start_date
2463 AND pep.effective_end_date
2464 AND assn.effective_end_date >= trunc(sysdate)
2465 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2466 AND hrinf.org_information_context = 'Exp Organization Defaults'
2467 /* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
2468 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2469 /* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
2470 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2471 pep.current_npw_flag = 'Y') -- CWK Changes
2472 AND pep.person_id = assn.person_id
2473 AND assn.organization_id =
2474 (SELECT exporg.organization_id
2475 FROM pa_all_organizations exporg
2476 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2477 AND exporg.inactive_date is null
2478 AND exporg.organization_id = assn.organization_id
2479 AND rownum = 1 )
2480 ORDER BY 1,2;
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
2490 P_table_name = 'PER_GRADES' or
2491 P_table_name = 'PA_ALL_ORGANIZATIONS') and
2492 (P_job_id is NOT NULL) then
2493
2494 -- get all the persons belongs to this job Id and
2495 -- Call Forecast Item regeneration API to update the forecast
2496 -- data for unassigned and assigned time
2497
2498 OPEN person_jobs(P_job_id);
2499 LOOP
2500 fetch person_jobs into v_person_id,v_start_date,v_end_date;
2501 exit when person_jobs%notfound;
2502
2503 PA_FORECASTITEM_PVT.Create_Forecast_Item
2504 (
2505 p_person_id => v_person_id
2506 ,p_start_date => v_start_date
2507 ,p_end_date => v_end_date
2508 ,p_process_mode => 'GENERATE'
2509 ,x_return_status => x_return_status
2510 ,x_msg_count => x_msg_count
2511 ,x_msg_data => x_msg_data
2512 ) ;
2513
2514 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2515 COMMIT;
2516 else
2517 ROLLBACK;
2518 end if;
2519
2520 /* cannot raise - because will be out from the loop and will not process other records
2521 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2522 RAISE FND_API.G_EXC_ERROR;
2523 END IF;
2524 */
2525
2526 END LOOP;
2527 CLOSE person_jobs;
2528
2529 Elsif (P_table_name = 'HR_ORGANIZATION_INFORMATION')
2530 and (P_organization_id is NOT NULL) then
2531
2532 -- get all the persons who belongs to this organization and
2533 -- Call Forecast Item regeneration API to update the forecast
2534 -- data for unassigned and assigned time
2535
2536 OPEN person_orgs(P_organization_id);
2537 LOOP
2538 fetch person_orgs into v_person_id,v_start_date,v_end_date;
2539 exit when person_orgs%notfound;
2540
2541 PA_FORECASTITEM_PVT.Create_Forecast_Item
2542 (
2543 p_person_id => v_person_id
2544 ,p_start_date => v_start_date
2545 ,p_end_date => v_end_date
2546 ,p_process_mode => 'GENERATE'
2547 ,x_return_status => x_return_status
2548 ,x_msg_count => x_msg_count
2549 ,x_msg_data => x_msg_data
2550 ) ;
2551
2552 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2553 COMMIT;
2554 else
2555 ROLLBACK;
2556 end if;
2557
2558 /* cannot raise - because will be out from the loop and will not process other records
2559 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2560 RAISE FND_API.G_EXC_ERROR;
2561 END IF;
2562 */
2563 END LOOP;
2564 CLOSE person_orgs;
2565
2566 End if;
2567
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
2577 -- 4537865 : RESET x_msg_count and x_msg_data also
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
2587
2588 END call_forcast_api;
2589
2590
2591 -- This Procedure makes calls to PA_RESOURCE_PVT.update_resource_denorm api
2592 -- which actually updates the pa_resources_denorm entity
2593 PROCEDURE call_billable_resoruce_denorm
2594 (P_job_id_old per_jobs.job_id%type
2595 ,P_job_id_new per_jobs.job_id%type
2596 ,P_billable_flag_old pa_resources_denorm.billable_flag%type
2597 ,P_billable_flag_new pa_resources_denorm.billable_flag%type
2598 ,P_utilize_flag_old pa_resources_denorm.utilization_flag%type
2599 ,P_utilize_flag_new pa_resources_denorm.utilization_flag%type
2600 ,p_schedulable_flag_old pa_resources_denorm.schedulable_flag%type
2601 ,p_schedulable_flag_new pa_resources_denorm.schedulable_flag%type
2602 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2603 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2604 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2605 ) IS
2606 v_return_status VARCHAR2(2000);
2607 v_error_message_code VARCHAR2(2000);
2608 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
2609 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
2610 v_job_id_old PER_JOBS.JOB_ID%type;
2611 v_job_id_new PER_JOBS.JOB_ID%type;
2612 v_billable_flag_old pa_resources_denorm.billable_flag%type;
2613 v_billable_flag_new pa_resources_denorm.billable_flag%type;
2614 v_utilize_flag_old pa_resources_denorm.utilization_flag%type;
2615 v_utilize_flag_new pa_resources_denorm.utilization_flag%type;
2616 v_schedulable_flag_old pa_resources_denorm.schedulable_flag%type;
2617 v_schedulable_flag_new pa_resources_denorm.schedulable_flag%type;
2618 v_msg_data VARCHAR2(2000);
2619 v_msg_count NUMBER;
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
2629 If P_billable_flag_new is NOT NULL then
2630 v_billable_flag_new := P_billable_flag_new;
2631 Else
2632 v_billable_flag_new := 'N';
2633 End if;
2634
2635 If P_billable_flag_old is NOT NULL then
2636 v_billable_flag_old := P_billable_flag_old;
2637 Else
2638 v_billable_flag_old := 'N';
2639 End if;
2640
2641 If P_utilize_flag_new is NOT NULL then
2642 v_utilize_flag_new := P_utilize_flag_new;
2643 Else
2644 v_utilize_flag_new := 'N';
2645 End if;
2646
2647 If P_utilize_flag_old is NOT NULL then
2648 v_utilize_flag_old := P_utilize_flag_old;
2649 Else
2650 v_utilize_flag_old := 'N';
2651 End if;
2652
2653 If P_schedulable_flag_new is NOT NULL then
2654 v_schedulable_flag_new := P_schedulable_flag_new;
2655 Else
2656 v_schedulable_flag_new := 'N';
2657 End if;
2658 If P_schedulable_flag_old is NOT NULL then
2659 v_schedulable_flag_old := P_schedulable_flag_old;
2660 Else
2661 v_schedulable_flag_old := 'N';
2662 End if;
2663
2664 if v_job_id_new is NOT NULL then
2665 v_resource_rec_old.job_id := v_job_id_old;
2666 v_resource_rec_old.billable_flag := v_billable_flag_old;
2667 v_resource_rec_new.job_id := v_job_id_new;
2668 v_resource_rec_new.billable_flag := v_billable_flag_new;
2669 v_resource_rec_old.utilization_flag := v_utilize_flag_old;
2670 v_resource_rec_new.utilization_flag := v_utilize_flag_new;
2671 v_resource_rec_old.schedulable_flag := v_schedulable_flag_old;
2672 v_resource_rec_new.schedulable_flag := v_schedulable_flag_new;
2673
2674 -- Call PRM API update resource denorm which actually updates the
2675 -- pa_resource_denorm entity
2676
2677 PA_RESOURCE_PVT.update_resource_denorm
2678 ( p_resource_denorm_old_rec => v_resource_rec_old
2679 ,p_resource_denorm_new_rec => v_resource_rec_new
2680 ,x_return_status => x_return_status
2681 ,x_msg_data => x_msg_data
2682 ,x_msg_count => x_msg_count
2683 );
2684
2685
2686
2687 End if;
2688 -- reset the Error stack
2689 PA_DEBUG.Reset_Err_Stack;
2690
2691 EXCEPTION
2692
2693 WHEN OTHERS THEN
2694 -- 4537865 : RESET x_msg_count and x_msg_data also
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;
2704
2705 -- This Procedure is kicked off by the workflow when jei_infomration2
2706 -- which stores the jobs billability information. whenever the row is updated
2707 -- or inserted into per_job_extra_info entity which stores the job information
2708 -- and types a database triggers fires and kicks of the workflow
2709 -- This procedure makes calls to forecast regenerate apis and create resource
2710 -- denorm apis to to update the new billability for the resource
2711 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because at the end
2712 -- this procedure call call_forcast_api which do commit after every resource
2713 -- in a loop to update the forecast items
2714 PROCEDURE per_job_extra_billability
2715 (p_calling_mode IN VARCHAR2
2716 ,P_job_id IN per_jobs.job_id%type
2717 ,P_billable_flag_new IN per_job_extra_info.jei_information2%type
2718 ,P_billable_flag_old IN per_job_extra_info.jei_information2%type
2719 ,P_utilize_flag_old IN per_job_extra_info.jei_information3%type
2720 ,P_utilize_flag_new IN per_job_extra_info.jei_information3%type
2721 ,P_job_level_new IN per_job_extra_info.jei_information4%type
2722 ,P_job_level_old IN per_job_extra_info.jei_information4%type
2723 ,p_schedulable_flag_new IN per_job_extra_info.jei_information6%type
2724 ,p_schedulable_flag_old IN per_job_extra_info.jei_information6%type
2725 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2726 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2727 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
2728 PRAGMA AUTONOMOUS_TRANSACTION;
2729
2730 v_return_status VARCHAR2(2000);
2731 v_error_message_code VARCHAR2(2000);
2732 v_job_id PER_JOBS.JOB_ID%type;
2733 v_msg_data VARCHAR2(2000);
2734 v_msg_count NUMBER;
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,
2744 max(resource_effective_end_date) resource_effective_end_date
2745 FROM pa_resources_denorm
2746 WHERE job_id = p_job_id
2747 GROUP BY person_id; */
2748
2749 CURSOR all_job_res_recs IS -- Modified cursor for Bug 7336158
2750 SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
2751 max(resource_effective_end_date) resource_effective_end_date
2752 FROM pa_resources_denorm
2753 WHERE job_id = p_job_id
2754 AND resource_effective_end_date = (Select max(resource_effective_end_date)
2755 from pa_resources_denorm rd2
2756 where rd2.job_id = p_job_id
2757 AND (rd2.resource_effective_end_date >= sysdate OR rd2.resource_effective_end_date is null))
2758 GROUP BY person_id;
2759
2760 CURSOR distinct_job_res_recs IS
2761 SELECT DISTINCT res.person_id person_id
2762 FROM pa_r_project_resources_v res
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);
2772 END IF;
2773
2774 -------------------------------------------------------------------------
2775 -- If P_calling_mode='INSERT', P_utilize_flag_new will always be 'Y' and
2776 -- we need to pull those people who has the job.
2777 -- Because if P_utilize_flag_new=N, the trigger won't launch WF which
2778 -- eventually calls this API.
2779 --------------------------------------------------------------------------
2780 If P_calling_mode = 'INSERT' THEN
2781 l_pull_res_flag := 'Y';
2782
2783 -------------------------------------------------------------------------
2784 -- If P_calling_mode='UPDATE'
2785 -------------------------------------------------------------------------
2786 ELSIF P_calling_mode = 'UPDATE' THEN
2787 -- If p_utilize_flag_old='N' AND p_utilize_flag_new='Y', we need to
2788 -- pull the people who have the job
2789 IF p_utilize_flag_old='N' AND p_utilize_flag_new='Y' THEN
2790 l_pull_res_flag := 'Y';
2791
2792 ELSE
2793 -- If p_utilize_flag_old='Y' and p_utilize_flag_new ='N', we need
2794 -- to end date the resources from pa_resources_denorm
2795 IF p_utilize_flag_old ='Y' AND p_utilize_flag_new ='N' THEN
2796 l_end_date_res_flag := 'Y';
2797 END IF;
2798
2799 IF P_DEBUG_MODE = 'Y' THEN
2800 log_message('p_utilize_flag_new: ' ||p_utilize_flag_new ||
2801 ',l_end_date_res_flag: '||l_end_date_res_flag);
2802 END IF;
2803
2804 -- Update pa_resources_denorm
2805 call_billable_resoruce_denorm
2806 (P_job_id_old => P_job_id
2807 ,P_job_id_new => P_job_id
2808 ,P_billable_flag_old => P_billable_flag_old
2809 ,P_billable_flag_new => P_billable_flag_new
2810 ,P_utilize_flag_old => P_utilize_flag_old
2811 ,P_utilize_flag_new => P_utilize_flag_new
2812 ,p_schedulable_flag_old => p_schedulable_flag_old
2813 ,p_schedulable_flag_new => p_schedulable_flag_new
2814 ,x_return_status => x_return_status
2815 ,x_msg_data => x_msg_data
2816 ,x_msg_count => x_msg_count );
2817 IF P_DEBUG_MODE = 'Y' THEN
2818 log_message('After call_billable_resoruce_denorm');
2819 END IF;
2820
2821 -- Update job level
2822 update_job_level_dff
2823 (P_job_id => P_job_id
2824 ,P_job_level_old => TO_NUMBER(P_job_level_old)
2825 ,P_job_level_new => TO_NUMBER(P_job_level_new)
2826 ,x_return_status => x_return_status
2827 ,x_msg_data => x_msg_data
2828 ,x_msg_count => x_msg_count );
2829 IF P_DEBUG_MODE = 'Y' THEN
2830 log_message('After update_job_level_dff');
2831 END IF;
2832 END IF;
2833
2834 -------------------------------------------------------------------------
2835 -- If P_calling_mode='DELETE' and p_utilize_flag_old='Y', end date those
2836 -- resources who has the job. If p_utilize_flag_old='N', there won't be
2837 -- current active data. But we still need to update the other flags on
2838 -- the past date records.
2839 -------------------------------------------------------------------------
2840 ELSIF P_calling_mode = 'DELETE' THEN
2841 IF p_utilize_flag_old ='Y' THEN
2842 l_end_date_res_flag := 'Y';
2843 END IF;
2844 IF P_DEBUG_MODE = 'Y' THEN
2845 log_message('P_calling_mode=DELETE, p_utilize_flag_old:'||p_utilize_flag_old
2846 ||', l_end_date_res_flag:'||l_end_date_res_flag);
2847 END IF;
2848
2849 call_billable_resoruce_denorm
2850 (P_job_id_old => P_job_id
2851 ,P_job_id_new => P_job_id
2852 ,P_billable_flag_old => P_billable_flag_old
2853 ,P_billable_flag_new => NULL
2854 ,P_utilize_flag_old => P_utilize_flag_old
2855 ,P_utilize_flag_new => NULL
2856 ,p_schedulable_flag_old => p_schedulable_flag_old
2857 ,p_schedulable_flag_new => NULL
2858 ,x_return_status => x_return_status
2859 ,x_msg_data => x_msg_data
2860 ,x_msg_count => x_msg_count );
2861
2862 update_job_level_dff
2863 (P_job_id => P_job_id
2864 ,P_job_level_old => TO_NUMBER(P_job_level_old)
2865 ,P_job_level_new => NULL
2866 ,x_return_status => x_return_status
2867 ,x_msg_data => x_msg_data
2868 ,x_msg_count => x_msg_count );
2869 IF P_DEBUG_MODE = 'Y' THEN
2870 log_message('After calling update_job_level_dff');
2871 END IF;
2872 END IF;
2873
2874 ----------------------------------------------------------------
2875 -- pull the people
2876 ----------------------------------------------------------------
2877 IF (l_pull_res_flag = 'Y') THEN
2878 IF P_DEBUG_MODE = 'Y' THEN
2879 log_message('it will pull the people');
2880 END IF;
2881 FOR rec IN distinct_job_res_recs LOOP
2882 BEGIN
2883 IF P_DEBUG_MODE = 'Y' THEN
2884 log_message('person Id = ' || rec.person_id);
2885 END IF;
2886
2887 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
2888 p_api_version => 1.0
2889 ,p_commit => FND_API.G_FALSE
2890 ,p_validate_only => FND_API.G_FALSE
2891 ,p_internal => 'Y'
2892 ,p_person_id => rec.person_id
2893 ,p_individual => 'Y'
2894 ,p_resource_type => 'EMPLOYEE'
2895 ,x_resource_id => l_resource_id
2896 ,x_return_status => x_return_status
2897 ,x_msg_count => x_msg_count
2898 ,x_msg_data => x_msg_data );
2899
2900 IF P_DEBUG_MODE = 'Y' THEN
2901 log_message('Return status from CREATE_RESOURCE = ' || x_return_status);
2902 END IF;
2903 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2904 COMMIT;
2905 ELSE
2906 ROLLBACK;
2907 END IF;
2908
2909 EXCEPTION
2910 -- whenever an expected error is raised from this API
2911 -- will need to continue for the next record
2912 WHEN OTHERS THEN
2913 FND_MSG_PUB.get (
2914 p_encoded => FND_API.G_FALSE,
2915 p_msg_index => 1,
2916 p_data => x_msg_data,
2917 p_msg_index_out => x_msg_count );
2918 IF P_DEBUG_MODE = 'Y' THEN
2919 log_message('error msg from CREATE_RESOURCE: ' || substr(x_msg_data,1,200));
2920 END IF;
2921 END;
2922 END LOOP;
2923
2924 -----------------------------------------------------------------------------
2925 -- End date all those resources who has the job from the pa_resources_denorm
2926 -----------------------------------------------------------------------------
2927 ELSIF (l_end_date_res_flag = 'Y') THEN
2928 IF P_DEBUG_MODE = 'Y' THEN
2929 log_message('it will end date the resources');
2930 END IF;
2931 FOR rec IN all_job_res_recs LOOP
2932 BEGIN
2933 IF P_DEBUG_MODE = 'Y' THEN
2934 log_message('person Id = ' || rec.person_id ||' ,start_date: ' ||
2935 rec.resource_effective_start_date || ', end_date: '||
2936 rec.resource_effective_end_date );
2937 END IF;
2938
2939 Update_EndDate
2940 (p_person_id => rec.person_id,
2941 p_old_start_date => rec.resource_effective_start_date,
2942 p_new_start_date => rec.resource_effective_start_date,
2943 p_old_end_date => rec.resource_effective_end_date,
2944 p_new_end_date => sysdate,
2945 x_return_status => x_return_status,
2946 x_msg_count => x_msg_count,
2947 x_msg_data => x_msg_data);
2948
2949 IF P_DEBUG_MODE = 'Y' THEN
2950 log_message('Return status from Update_EndDate = ' || x_return_status);
2951 END IF;
2952 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2953 COMMIT;
2954 ELSE
2955 ROLLBACK;
2956 END IF;
2957 EXCEPTION
2958 -- whenever an expected error is raised from this API
2959 -- will need to continue for the next record
2960 WHEN OTHERS THEN
2961 IF P_DEBUG_MODE = 'Y' THEN
2962 log_message('error occured');
2963 END IF;
2964 ROLLBACK;
2965 FND_MSG_PUB.get (
2966 p_encoded => FND_API.G_FALSE,
2967 p_msg_index => 1,
2968 p_data => x_msg_data,
2969 p_msg_index_out => x_msg_count );
2970 IF P_DEBUG_MODE = 'Y' THEN
2971 log_message('error msg from Update_EndDate: ' || substr(x_msg_data,1,200));
2972 END IF;
2973 END;
2974 END LOOP;
2975
2976 END IF;
2977
2978
2979 -- call forecast api to regenerate the forcast items due to change in
2980 -- billability flag
2981 call_forcast_api
2982 (P_table_name => 'PER_JOB_EXTRA_INFO'
2983 ,P_Job_id => P_job_id
2984 ,x_return_status => x_return_status
2985 ,x_msg_data => x_msg_data
2986 ,x_msg_count => x_msg_count );
2987 IF P_DEBUG_MODE = 'Y' THEN
2988 log_message('After calling call_forcast_api, x_return_status: '||x_return_status);
2989 END IF;
2990
2991 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2992 IF P_DEBUG_MODE = 'Y' THEN
2993 log_message('It will raise exception');
2994 END IF;
2995 RAISE FND_API.G_EXC_ERROR;
2996 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2997 IF P_DEBUG_MODE = 'Y' THEN
2998 log_message('will commit');
2999 END IF;
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
3009 EXCEPTION
3010 WHEN FND_API.G_EXC_ERROR THEN
3011 ROLLBACK;
3012 x_return_status := FND_API.G_RET_STS_ERROR;
3013 WHEN NO_DATA_FOUND THEN
3014 NULL;
3015 WHEN OTHERS THEN
3016 IF P_DEBUG_MODE = 'Y' THEN
3017 log_message('Error occured');
3018 END IF;
3019 -- 4537865 : RESET x_msg_count and x_msg_data also
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
3029
3030 -- PROCEDURE
3031 -- withdraw_cand_nominations
3032 -- PURPOSE
3033 -- to withdraw all PJR candidate nominations for this
3034 -- person_id when the person is terminated in HR
3035 -- or the assignment organization no longer belongs to
3036 -- expenditure hierarchy
3037 --
3038 PROCEDURE withdraw_cand_nominations
3039 ( p_person_id IN NUMBER,
3040 p_effective_date IN DATE,
3041 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3042 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3043 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
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,
3053 pa_project_assignments asgmt,
3054 pa_project_statuses ps
3055 where cand.resource_id = l_resource_id
3056 and cand.assignment_id = asgmt.assignment_id
3057 and asgmt.assignment_type = 'OPEN_ASSIGNMENT'
3058 and asgmt.status_code = ps.project_status_code (+)
3059 and (ps.project_system_status_code = 'OPEN_ASGMT'
3060 OR ps.project_system_status_code is null)
3061 and asgmt.start_date > trunc(p_effective_date)
3062 and cand.status_code not in
3063 (select ps2.project_status_code
3064 from pa_project_statuses ps2
3065 where ps2.status_type='CANDIDATE'
3066 and ps2.project_system_status_code IN
3067 ('CANDIDATE_DECLINED','CANDIDATE_WITHDRAWN'));
3068
3069 l_candidate_in_rec PA_RES_MANAGEMENT_AMG_PUB.CANDIDATE_IN_REC_TYPE; -- Added for bug 9187892
3070
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
3080 IF (l_resource_id <> -999) THEN
3081
3082 l_status_code := FND_PROFILE.value('PA_INV_RES_CAND_STATUS');
3083 IF P_DEBUG_MODE = 'Y' THEN
3084 log_message('Candidate status code = ' || l_status_code);
3085 END IF;
3086
3087 -- if the profile option is not set, then we use the status code
3088 -- '111' that we ship to customers for 'CANDIDATE_WITHDRAWN'
3089 -- system status code
3090 IF l_status_code is null THEN
3091 l_status_code := '111';
3092 END IF;
3093
3094 FOR rec IN get_nominations(l_resource_id) LOOP
3095
3096 BEGIN
3097 SAVEPOINT save_candidate;
3098 IF P_DEBUG_MODE = 'Y' THEN
3099 log_message('Candidate Id = ' || rec.candidate_id);
3100 END IF;
3101
3102 PA_CANDIDATE_PUB.Update_Candidate
3103 (p_candidate_id => rec.candidate_id,
3104 p_status_code => l_status_code,
3105 p_ranking => null,
3106 p_change_reason_code => null,
3107 p_record_version_number => rec.record_version_number,
3108 p_init_msg_list => FND_API.G_FALSE,
3109 p_validate_status => FND_API.G_FALSE,
3110 -- Added for bug 9187892
3111 p_attribute_category => l_candidate_in_rec.attribute_category,
3112 p_attribute1 => l_candidate_in_rec.attribute1,
3113 p_attribute2 => l_candidate_in_rec.attribute2,
3114 p_attribute3 => l_candidate_in_rec.attribute3,
3115 p_attribute4 => l_candidate_in_rec.attribute4,
3116 p_attribute5 => l_candidate_in_rec.attribute5,
3117 p_attribute6 => l_candidate_in_rec.attribute6,
3118 p_attribute7 => l_candidate_in_rec.attribute7,
3119 p_attribute8 => l_candidate_in_rec.attribute8,
3120 p_attribute9 => l_candidate_in_rec.attribute9,
3121 p_attribute10 => l_candidate_in_rec.attribute10,
3122 p_attribute11 => l_candidate_in_rec.attribute11,
3123 p_attribute12 => l_candidate_in_rec.attribute12,
3124 p_attribute13 => l_candidate_in_rec.attribute13,
3125 p_attribute14 => l_candidate_in_rec.attribute14,
3126 p_attribute15 => l_candidate_in_rec.attribute15,
3127 x_record_version_number => l_rec_ver_num,
3128 x_return_status => x_return_status,
3129 x_msg_count => x_msg_count,
3130 x_msg_data => x_msg_data);
3131
3132 IF P_DEBUG_MODE = 'Y' THEN
3133 log_message('Return status from Update Candidate = ' || x_return_status);
3134 END IF;
3135
3136 EXCEPTION
3137 -- whenever an expected error is raised from this API
3138 -- will need to continue for the next record
3139 WHEN FND_API.G_EXC_ERROR THEN
3140 ROLLBACK TO save_candidate;
3141 FND_MSG_PUB.get (
3142 p_encoded => FND_API.G_FALSE,
3143 p_msg_index => 1,
3144 p_data => x_msg_data,
3145 p_msg_index_out => x_msg_count );
3146 IF P_DEBUG_MODE = 'Y' THEN
3147 log_message('Withdraw_Cand_Nominations EXPECTED ERROR: Candidate_Id =' || rec.candidate_id);
3148 log_message('Log: ' || substr(x_msg_data,1,200));
3149 END IF;
3150 END;
3151
3152 END LOOP;
3153
3154 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3155
3156 ELSE
3157 -- person does not exist in pa_resource_txn_attributes
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
3167 -- 4537865 : RESET x_msg_count and x_msg_data also
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
3177
3178 /* Procedure Update_EndDate calls Update_Resource_Denorm to update the
3179 end date in pa_resources_denorm and update FI data for the resource.
3180 This procedure now also handles automatic candidates withdrawal.
3181 It is called whenever a resource is terminated in HR, whenever
3182 the change in assignment organization which does not belong to Exp Hier,
3183 or whenever an organization is taken out from the Exp Hier. In these
3184 cases, the resource is considered no longer active in PJR.
3185 */
3186 PROCEDURE Update_EndDate(
3187 p_person_id IN per_all_people_f.person_id%TYPE,
3188 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3189 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
3190 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
3191 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3192 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3193 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3194 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3195
3196 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3197 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3198
3199 l_return_status VARCHAR2(1);
3200
3201 l_invol_term VARCHAR2(1); --bug 5683340
3202
3203 l_count NUMBER ; -- bug 7147575
3204
3205 CURSOR res_denorm_recs IS
3206 SELECT resource_effective_start_date,
3207 resource_effective_end_date
3208 FROM pa_resources_denorm
3209 WHERE person_id = p_person_id
3210 -- AND p_new_end_date >= resource_effective_start_date
3211 AND resource_effective_start_date >= p_old_start_date
3212 AND resource_effective_end_date <= p_old_end_date
3213 ;
3214
3215 --Bug 9838014 Start
3216 CURSOR get_max_end_date_term(p_person_id IN NUMBER) IS
3217 SELECT MAX (asgn_end_date) FROM
3218 (select max(res.assignment_end_date) asgn_end_date
3219 from pa_r_project_resources_v res
3220 where res.person_id = p_person_id
3221 and res.assignment_end_date IS NOT NULL
3222 UNION
3223 select max(res.assignment_end_date) ass_end_date
3224 from pa_r_project_resources_term_v res
3225 where res.person_id = p_person_id
3226 and res.assignment_end_date IS NOT NULL );
3227
3228 l_end_date DATE;
3229 --Bug 9838014 End
3230
3231 -- Bug 8791391
3232 l_resource_effective_end_date per_all_assignments_f.effective_end_date%TYPE ;
3233 l_withdraw_nom_flag VARCHAR2(1) :='Y';
3234
3235 -- Bug 9704139
3236 l_candidate_terminated VARCHAR2(3) := 'N';
3237
3238 BEGIN
3239
3240 --dbms_output.put_line('Inside Update End Date');
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
3250 FOR rec IN res_denorm_recs LOOP
3251
3252 /*Bug 9838014 Start
3253 l_count := l_count + 1 ; -- bug 7147575 :this means that there was atleast one record in pa_resources_denorm
3254
3255 --dbms_output.put_line('resource_effective_start_date:' || rec.resource_effective_start_date);
3256 --dbms_output.put_line('resource_effective_end_date:' || rec.resource_effective_end_date);
3257
3258
3259 -- Bug 4668272 - added case of p_new_end_date >
3260 -- rec.resource_effective_end_date - this case occurs when a
3261 -- reverse termination happens in HR.
3262 -- IF p_new_end_date BETWEEN rec.resource_effective_start_date AND rec.resource_effective_end_date THEN
3263 IF (p_new_end_date BETWEEN rec.resource_effective_start_date AND
3264 rec.resource_effective_end_date) OR
3265 (p_new_end_date > rec.resource_effective_end_date) THEN Bug 9838014 End */
3266
3267 --Bug 9838014 Start
3268 IF ((p_new_end_date BETWEEN rec.resource_effective_start_date AND rec.resource_effective_end_date)
3269 AND trunc(rec.resource_effective_end_date) <> trunc(p_new_end_date)) OR
3270 (p_new_end_date > rec.resource_effective_end_date AND trunc(p_old_end_date) = trunc(rec.resource_effective_end_date)) THEN
3271
3272 l_count := l_count + 1 ;
3273 --Bug 9838014 End
3274
3275 --End date this record
3276 --Set the values for the Resources Denorm Record Type
3277
3278 l_resource_rec_old.person_id := p_person_id;
3279 l_resource_rec_new.person_id := p_person_id;
3280
3281 l_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
3282 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3283
3284 l_resource_rec_old.resource_effective_end_date := rec.resource_effective_end_date;
3285 l_resource_rec_new.resource_effective_end_date := p_new_end_date;
3286
3287 --Call Resource Denorm API
3288 PA_RESOURCE_PVT.update_resource_denorm(
3289 p_resource_denorm_old_rec => l_resource_rec_old
3290 ,p_resource_denorm_new_rec => l_resource_rec_new
3291 ,x_return_status => l_return_status
3292 ,x_msg_data => x_msg_data
3293 ,x_msg_count => x_msg_count);
3294
3295 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3296 x_return_status := FND_API.G_RET_STS_ERROR ;
3297 END IF;
3298
3299 ELSIF p_new_end_date < rec.resource_effective_start_date THEN
3300 --Bug 9838014 Start
3301 OPEN get_max_end_date_term(p_person_id);
3302 FETCH get_max_end_date_term INTO l_end_date;
3303 CLOSE get_max_end_date_term;
3304
3305 IF rec.resource_effective_start_date > l_end_date THEN
3306 l_count := l_count + 1;
3307 --Bug 9838014 End
3308
3309 --Delete this record
3310 pa_resource_pvt.delete_resource_denorm(
3311 p_person_id => p_person_id
3312 ,p_res_effective_start_date => rec.resource_effective_start_date
3313 ,x_return_status => l_return_status
3314 ,x_msg_data => x_msg_data
3315 ,x_msg_count => x_msg_count);
3316
3317 END IF; --Bug 9838014
3318
3319 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3320 x_return_status := FND_API.G_RET_STS_ERROR ;
3321 END IF;
3322
3323 END IF;
3324
3325 END LOOP; --end FOR
3326
3327 IF (l_count > 0 ) THEN -- bug 7147575 : so now on, only if records are there in pa_resources_denorm, then only further code will get executed.
3328
3329 /*Call added for bug 5683340*/
3330 pa_resource_utils.init_fte_sync_wf( p_person_id => p_person_id,
3331 x_invol_term => l_invol_term,
3332 x_return_status => l_return_status,
3333 x_msg_data => x_msg_data,
3334 x_msg_count => x_msg_count);
3335
3336 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3337 x_return_status := FND_API.G_RET_STS_ERROR ;
3338 END IF ;
3339
3340 /*if block added for bug 5683340*/
3341 IF ((l_invol_term = 'N') AND (l_return_status = FND_API.G_RET_STS_SUCCESS)) THEN
3342
3343 --Call Forecast Item regeneration API
3344 --to fix forecast data after resource was end_dated
3345 --start_date passed is the date when it changes
3346 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3347 p_person_id => p_person_id,
3348 --p_start_date => p_new_end_date+1, p_old_end_date Bug 6120875
3349 p_start_date => Least(p_new_end_date+1, p_old_end_date+1),
3350 p_end_date => null,
3351 p_process_mode => 'GENERATE',
3352 x_return_status => l_return_status,
3353 x_msg_count => x_msg_count,
3354 x_msg_data => x_msg_data) ;
3355
3356 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3357 x_return_status := FND_API.G_RET_STS_ERROR ;
3358 END IF;
3359
3360
3361 -- Bug 8791391
3362 -- Candidancy should be withdrawn only if end dates have been modified
3363 -- It shouldnt get called when assignments are modified in update mode
3364 -- which causes in creation of new row and updation of teh endate of the
3365 -- existing row.
3366
3367 /*
3368 SELECT MAX(paaf.effective_end_date)
3369 INTO l_resource_effective_end_date
3370 FROM per_all_assignments_f paaf,
3371 per_assignment_status_types past
3372 WHERE paaf.person_id=p_person_id
3373 AND paaf.primary_flag = 'Y'
3374 AND paaf.assignment_type in ('E','C')
3375 AND past.assignment_status_type_id = paaf.assignment_status_type_id
3376 AND past.per_system_status in ('ACTIVE_ASSIGN','ACTIVE_CWK');
3377 */
3378
3379 -- Bug 9704139
3380 -- Use this new query to find candidate assignment end date
3381 -- If no record found then candidate is terminated
3382 -- Withdraw candidacies if the candidate is still in HR but moved org and
3383 -- no longer eligible for PJR (OR) if the candidate is terminated.
3384 BEGIN
3385
3386 SELECT max(assignment_end_date)
3387 INTO l_resource_effective_end_date
3388 FROM pa_r_project_resources_v
3389 WHERE person_id = p_person_id;
3390
3391 EXCEPTION
3392 WHEN NO_DATA_FOUND THEN
3393 -- withdraw the candidacy if the record is not found in select as the candidate is terminated
3394 l_withdraw_nom_flag := 'Y';
3395 l_candidate_terminated := 'Y';
3396
3397 END;
3398
3399 -- Bug 9704139
3400 -- check if already terminated, if not then do the below
3401 IF l_candidate_terminated = 'N' THEN
3402 -- Withdraw only when end-date is equal to the person's max effective end date.
3403 IF trunc(p_new_end_date) = trunc(l_resource_effective_end_date) THEN
3404 l_withdraw_nom_flag := 'Y';
3405 ELSE
3406 l_withdraw_nom_flag := 'N';
3407 END IF;
3408 END IF;
3409
3410
3411 -- Call this procedure to withdraw any active candidacy of this
3412 -- person in PJR whenever the person is end dated (due to termination
3413 -- or the organization no longer belong to Exp Hier)
3414 IF l_withdraw_nom_flag = 'Y' THEN --Bug 8791391
3415 withdraw_cand_nominations
3416 ( p_person_id => p_person_id,
3417 p_effective_date => p_new_end_date,
3418 x_return_status => l_return_status,
3419 x_msg_count => x_msg_count,
3420 x_msg_data => x_msg_data);
3421 END IF;
3422
3423 -- reset the Error stack
3424
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
3434 x_return_status := FND_API.G_RET_STS_ERROR ;
3435
3436 WHEN OTHERS THEN
3437 -- 4537865 : RESET x_msg_count and x_msg_data also
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
3447 /* Procedure Update_Org calls Update_Resource_Denorm and Create_Forecast_Item to update the organization and org_id for the resources in pa_resources_denorm table and regenerate forecast items for the resource respectively.
3448 */
3449 PROCEDURE Update_Org(
3450 p_person_id IN per_all_people_f.person_id%TYPE,
3451 p_old_org_id IN per_all_assignments_f.organization_id%TYPE,
3452 p_new_org_id IN per_all_assignments_f.organization_id%TYPE,
3453 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3454 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
3455 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
3456 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3457 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3458 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3459 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3460
3461 l_default_ou_old pa_resources_denorm.resource_org_id%TYPE;
3462 l_default_ou_new pa_resources_denorm.resource_org_id%TYPE;
3463
3464 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3465 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3466
3467 l_return_status VARCHAR2(1);
3468 l_org_type VARCHAR2(20);
3469 l_resource_id NUMBER(15);
3470
3471
3472 -- Cursor to get all denormalized resource records
3473 -- for this HR assignment
3474 CURSOR res_denorm_recs IS
3475 SELECT resource_effective_start_date,
3476 resource_effective_end_date
3477 FROM pa_resources_denorm
3478 WHERE person_id = p_person_id
3479 AND resource_effective_start_date >= p_new_start_date
3480 AND resource_effective_end_date <= p_new_end_date
3481 ;
3482
3483 -- CURSOR to check whether it is a Multi or Single Org Implementation
3484 --MOAC Changes : Bug 4363092: Get the value of current org from PA_MOAC_UTILS.GET_CURRENT_ORG_ID
3485 /* CURSOR check_org_type IS
3486 select decode(substr(USERENV('CLIENT_INFO'),1,1),
3487 ' ', NULL,
3488 substr(USERENV('CLIENT_INFO'),1,10)) org from dual; */
3489
3490 BEGIN
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
3500 organization, if yes then get default ou for old and new organization
3501 and update pa_resource_ou.organization.*/
3502
3503 IF (Belongs_ExpOrg(p_new_org_id) = 'Y') THEN
3504
3505 -- If the old org Id does not belong to exp hier,
3506 -- we have to fix data in resource denorm (we end dated that
3507 -- record previously when the org changes from Exp Hier
3508 -- to Non Exp Hier). So calling Create_Resource
3509 IF(Belongs_ExpOrg(p_old_org_id) = 'N') THEN
3510
3511 pa_r_project_resources_pub.create_resource (
3512 p_api_version => 1.0
3513 ,p_init_msg_list => NULL
3514 ,p_commit => FND_API.G_FALSE
3515 ,p_validate_only => NULL
3516 ,p_max_msg_count => NULL
3517 ,p_internal => 'Y'
3518 ,p_person_id => p_person_id
3519 ,p_individual => 'Y'
3520 ,p_resource_type => NULL
3521 ,x_return_status => l_return_status
3522 ,x_msg_count => x_msg_count
3523 ,x_msg_data => x_msg_data
3524 ,x_resource_id => l_resource_id);
3525
3526
3527 -- call this procedure to update the forecast data for
3528 -- assigned time ONLY for this resource
3529 -- pass null to start date and end date
3530 -- this is called only if create_resource is a success
3531 if (l_return_status = 'S' and l_resource_id is not null) then
3532 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3533 p_person_id => p_person_id
3534 ,p_start_date => null
3535 ,p_end_date => null
3536 ,p_process_mode => 'GENERATE_ASGMT'
3537 ,x_return_status => l_return_status
3538 ,x_msg_count => x_msg_count
3539 ,x_msg_data => x_msg_data
3540 ) ;
3541 end if;
3542
3543 ELSE
3544
3545 l_default_ou_old := Get_DefaultOU(p_old_org_id);
3546 l_default_ou_new := Get_DefaultOU(p_new_org_id);
3547
3548 IF (l_default_ou_new = -999) THEN
3549 /* Bug 4363092: Commenting this check as R12 will be multi org only */
3550 -- OPEN check_org_type;
3551 -- FETCH check_org_type into l_org_type;
3552 -- CLOSE check_org_type;
3553
3554 -- case for Multi-Org: no OU - so set return status
3555 -- to error and return
3556 -- IF l_org_type IS NOT NULL THEN
3557 PA_UTILS.Add_Message(
3558 p_app_short_name => 'PA'
3559 ,p_msg_name => 'PA_RS_DEF_OU_NULL');
3560
3561 x_msg_data := 'PA_RS_DEF_OU_NULL';
3562 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3563 RETURN;
3564 -- ELSE
3565 -- l_default_ou_new := NULL;
3566 -- END IF;
3567
3568 END IF;
3569
3570 --Set the values for the Resources Denorm Record Type
3571 l_resource_rec_old.person_id := p_person_id;
3572 l_resource_rec_new.person_id := p_person_id;
3573
3574 l_resource_rec_old.resource_org_id := l_default_ou_old;
3575 l_resource_rec_new.resource_org_id := l_default_ou_new;
3576
3577 l_resource_rec_old.resource_organization_id := p_old_org_id;
3578 l_resource_rec_new.resource_organization_id := p_new_org_id;
3579
3580 FOR rec IN res_denorm_recs LOOP
3581
3582 l_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
3583 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3584
3585 l_resource_rec_old.resource_effective_end_date := rec.resource_effective_end_date;
3586 l_resource_rec_new.resource_effective_end_date := rec.resource_effective_end_date;
3587
3588 --Call Resource Denorm API
3589 PA_RESOURCE_PVT.update_resource_denorm(
3590 p_resource_denorm_old_rec => l_resource_rec_old
3591 ,p_resource_denorm_new_rec => l_resource_rec_new
3592 ,x_return_status => l_return_status
3593 ,x_msg_data => x_msg_data
3594 ,x_msg_count => x_msg_count);
3595
3596 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3597 x_return_status := FND_API.G_RET_STS_ERROR ;
3598 END IF;
3599
3600 END LOOP; --end FOR
3601
3602 --Bug 7690398 put an if condition for the resource id
3603 IF (pa_resource_utils.get_resource_id(p_person_id) <> -999) THEN
3604 --Call Forecast Item regeneration API
3605 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3606 p_person_id => p_person_id,
3607 p_start_date => p_new_start_date,
3608 p_end_date => p_new_end_date,
3609 p_process_mode => 'GENERATE',
3610 x_return_status => l_return_status,
3611 x_msg_count => x_msg_count,
3612 x_msg_data => x_msg_data) ;
3613
3614 END IF;
3615
3616 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3617 x_return_status := FND_API.G_RET_STS_ERROR ;
3618 END IF;
3619 END IF; -- Bug 7690398
3620 ELSE
3621 /* In case the organization does not belong to expenditure
3622 hierarchy the record in pa_resource_denorm must be end dated*/
3623
3624 Update_EndDate(
3625 p_person_id => p_person_id,
3626 p_old_start_date => p_old_start_date,
3627 p_new_start_date => p_new_start_date,
3628 p_old_end_date => p_old_end_date,
3629 p_new_end_date => sysdate,
3630 x_return_status => l_return_status,
3631 x_msg_data => x_msg_data,
3632 x_msg_count => x_msg_count);
3633
3634 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3635 x_return_status := FND_API.G_RET_STS_ERROR ;
3636 END IF;
3637
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;
3647
3648 WHEN OTHERS THEN
3649 -- 4537865 : RESET x_msg_count and x_msg_data also
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
3659 -- Start Bug : 4656855
3660 /* Function check_pjr_default_ou checks if org_id in pa_all_organizations = the PJR default operating unit */
3661 FUNCTION check_pjr_default_ou(P_Organization_id IN Hr_Organization_Information.Organization_id%type, P_Default_OU_new IN Hr_Organization_Information.Org_Information1%type)
3662 RETURN VARCHAR2 IS
3663 -- This function returns 'Y' if org_id in pa_all_organizations = the PJR default operating unit
3664 -- otherwise , it returns 'N'
3665
3666 CURSOR c_check_pjr_default_ou IS
3667 SELECT 'Y' FROM dual WHERE exists
3668 (SELECT 'Y' FROM hr_organization_information
3669 WHERE organization_id = P_Organization_id
3670 and org_information_context = 'Exp Organization Defaults'
3671 and org_information1 = P_Default_OU_new);
3672
3673 l_dummy VARCHAR2(1);
3674 BEGIN
3675 OPEN c_check_pjr_default_ou;
3676 FETCH c_check_pjr_default_ou INTO l_dummy;
3677
3678 IF c_check_pjr_default_ou%NOTFOUND THEN
3679 CLOSE c_check_pjr_default_ou; -- Bug 5336837
3680 RETURN 'N';
3681 ELSE
3682 CLOSE c_check_pjr_default_ou; -- Bug 5336837
3683 RETURN 'Y';
3684 END IF;
3685
3686 -- CLOSE c_check_pjr_default_ou; -- Bug 5336837
3687 EXCEPTION
3688 WHEN OTHERS THEN
3689 -- Bug 5336837
3690 IF c_check_pjr_default_ou%ISOPEN THEN
3691 CLOSE c_check_pjr_default_ou ;
3692 END IF;
3693
3694 return 'N';
3695 END check_pjr_default_ou;
3696 -- End Bug : 4656855
3697
3698 /* Function Belongs_ExpOrg checks if the given organization belongs to
3699 an expenditure/event organization
3700 */
3701 FUNCTION Belongs_ExpOrg(p_org_id IN per_all_assignments_f.organization_id%TYPE)
3702 RETURN VARCHAR2 IS
3703 -- This function returns 'Y' if a given org is a Exp organization ,
3704 -- otherwise , it returns 'N'
3705
3706 CURSOR c_exp_org IS
3707 SELECT 'x'
3708 FROM dual
3709 WHERE exists
3710 (select organization_id
3711 FROM pa_all_organizations
3712 WHERE organization_id = p_org_id
3713 AND inactive_date is null
3714 AND pa_org_use_type = 'EXPENDITURES');
3715
3716 l_dummy VARCHAR2(1);
3717 BEGIN
3718 OPEN c_exp_org;
3719 FETCH c_exp_org INTO l_dummy;
3720
3721 IF c_exp_org%NOTFOUND THEN
3722 CLOSE c_exp_org; -- Bug 5336837
3723 RETURN 'N';
3724 ELSE
3725 CLOSE c_exp_org; -- Bug 5336837
3726 RETURN 'Y';
3727 END IF;
3728
3729 -- CLOSE c_exp_org; -- Bug 5336837
3730 EXCEPTION
3731 WHEN OTHERS THEN
3732 -- Bug 5336837
3733 IF c_exp_org%ISOPEN THEN
3734 CLOSE c_exp_org ;
3735 END IF;
3736
3737 return 'N';
3738 END Belongs_ExpOrg;
3739
3740 /* Function Get_DefaultOU returns the default OU for the given organization.
3741 */
3742 FUNCTION Get_DefaultOU(p_org_id IN per_all_assignments_f.organization_id%TYPE)
3743 RETURN NUMBER IS
3744 l_default_ou number;
3745 BEGIN
3746 select to_number(org_information1)
3747 into l_default_ou
3748 from hr_organization_information
3749 where organization_id = p_org_id
3750 and org_information_context = 'Exp Organization Defaults';
3751
3752 if l_default_ou is null then
3753 l_default_ou := -999;
3754 end if;
3755
3756 return l_default_ou;
3757 EXCEPTION
3758 WHEN OTHERS THEN
3759 return -999;
3760 END Get_DefaultOU;
3761
3762 /*
3763 Procedure Update_Job retrieves the job level for the job and calls
3764 Update_Resource_Denorm and Create_Forecast_Item to update the denorm
3765 table and regenerate forecast items for the resource respectively.
3766 */
3767 PROCEDURE Update_Job(
3768 p_person_id IN per_all_people_f.person_id%TYPE,
3769 p_old_job IN per_all_assignments_f.job_id%TYPE,
3770 p_new_job IN per_all_assignments_f.job_id%TYPE,
3771 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3772 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3773 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3774 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3775 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3776
3777 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3778 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3779 l_return_status VARCHAR2(1);
3780 l_resource_id NUMBER;
3781 l_resource_start_date DATE;
3782 l_resource_end_date DATE;
3783 l_old_job_group_id per_job_groups.job_group_id%type;
3784 l_new_job_group_id per_job_groups.job_group_id%type;
3785 l_old_job_level NUMBER;
3786 l_new_job_level NUMBER;
3787 l_old_job_billable pa_resources_denorm.billable_flag%type;
3788 l_new_job_billable pa_resources_denorm.billable_flag%type;
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
3798 SELECT resource_effective_start_date,
3799 resource_effective_end_date
3800 FROM pa_resources_denorm
3801 WHERE person_id = p_person_id
3802 AND resource_effective_start_date >= p_new_start_date
3803 AND resource_effective_end_date <= p_new_end_date;
3804 --Bug 9047714 Start
3805 /*CURSOR min_max_res_dates IS
3806 SELECT min(resource_effective_start_date) resource_effective_start_date,
3807 max(resource_effective_end_date) resource_effective_end_date
3808 FROM pa_resources_denorm
3809 WHERE job_id = p_old_job
3810 AND person_id = p_person_id
3811 GROUP BY person_id;*/
3812 CURSOR min_max_res_dates IS
3813 SELECT min(resource_effective_start_date) resource_effective_start_date,
3814 max(resource_effective_end_date) resource_effective_end_date
3815 FROM pa_resources_denorm
3816 WHERE job_id = p_old_job
3817 AND person_id = p_person_id
3818 AND resource_effective_end_date = (Select max(resource_effective_end_date)
3819 from pa_resources_denorm rd2
3820 where rd2.job_id = p_old_job
3821 AND rd2.person_id = p_person_id
3822 AND (rd2.resource_effective_end_date >= sysdate OR rd2.resource_effective_end_date is null))
3823 GROUP BY person_id;
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;
3833
3834 --Get the job group id for the old and new job
3835 l_old_job_group_id := get_job_group_id(p_old_job);
3836 l_new_job_group_id := get_job_group_id(p_new_job);
3837
3838 --Get the old/new job level and job flags
3839 l_old_job_level := get_job_level(P_job_id => p_old_job,
3840 P_job_group_id => l_old_job_group_id);
3841 l_new_job_level := get_job_level(P_job_id => p_new_job,
3842 P_job_group_id => l_new_job_group_id);
3843 l_old_job_billable := check_job_billability(P_job_id => p_old_job,
3844 P_person_id => NULL,
3845 p_date => NULL );
3846 l_new_job_billable := check_job_billability(P_job_id => p_new_job,
3847 P_person_id => NULL,
3848 p_date => NULL );
3849 l_old_job_utilizable := check_job_utilization(P_job_id => p_old_job,
3850 P_person_id => NULL,
3851 p_date => NULL );
3852 l_new_job_utilizable := check_job_utilization(P_job_id => p_new_job,
3853 P_person_id => NULL,
3854 p_date => NULL );
3855 l_old_job_schedulable := check_job_schedulable(P_job_id => p_old_job);
3856 l_new_job_schedulable := check_job_schedulable(P_job_id => p_new_job);
3857
3858 ------------------------------------------------------------------
3859 -- If old job's util_flag=N and new job's util_flag=Y, we need to
3860 -- pull the person.
3861 ------------------------------------------------------------------
3862 IF l_old_job_utilizable='N' AND l_new_job_utilizable='Y' THEN
3863 IF P_DEBUG_MODE = 'Y' THEN
3864 log_message('pull the person_Id = ' || p_person_id);
3865 END IF;
3866
3867 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
3868 p_api_version => 1.0
3869 ,p_commit => FND_API.G_FALSE
3870 ,p_validate_only => FND_API.G_FALSE
3871 ,p_internal => 'Y'
3872 ,p_person_id => p_person_id
3873 ,p_individual => 'Y'
3874 ,p_resource_type => 'EMPLOYEE'
3875 ,x_resource_id => l_resource_id
3876 ,x_return_status => x_return_status
3877 ,x_msg_count => x_msg_count
3878 ,x_msg_data => x_msg_data );
3879
3880 ------------------------------------------------------------------
3881 -- If old util_flag=Y and new_util_flag=N, end date the resource
3882 ------------------------------------------------------------------
3883 ELSIF l_old_job_utilizable='Y' AND l_new_job_utilizable='N' THEN
3884 OPEN min_max_res_dates;
3885 FETCH min_max_res_dates INTO l_resource_start_date, l_resource_end_date;
3886 CLOSE min_max_res_dates;
3887
3888 IF P_DEBUG_MODE = 'Y' THEN
3889 log_message('person Id = ' || p_person_id ||' ,start_date: ' ||
3890 l_resource_start_date || ', end_date: '||l_resource_end_date );
3891 END IF;
3892
3893 Update_EndDate
3894 (p_person_id => p_person_id,
3895 p_old_start_date => l_resource_start_date,
3896 p_new_start_date => l_resource_start_date,
3897 p_old_end_date => l_resource_end_date,
3898 p_new_end_date => sysdate,
3899 x_return_status => x_return_status,
3900 x_msg_count => x_msg_count,
3901 x_msg_data => x_msg_data);
3902
3903 IF P_DEBUG_MODE = 'Y' THEN
3904 log_message('Return status from Update_EndDate = ' || x_return_status);
3905 END IF;
3906
3907 ------------------------------------------------------------------
3908 -- If old util_flag=new util_flag, just update job flags
3909 ------------------------------------------------------------------
3910 ELSE
3911 --Set the values for the Resources Denorm Record Type
3912 l_resource_rec_old.person_id := p_person_id;
3913 l_resource_rec_new.person_id := p_person_id;
3914 l_resource_rec_old.job_id := p_old_job;
3915 l_resource_rec_new.job_id := p_new_job;
3916 l_resource_rec_old.resource_job_level := l_old_job_level;
3917 l_resource_rec_new.resource_job_level := l_new_job_level;
3918 l_resource_rec_old.billable_flag := l_old_job_billable;
3919 l_resource_rec_new.billable_flag := l_new_job_billable;
3920 l_resource_rec_old.utilization_flag := l_old_job_utilizable;
3921 l_resource_rec_new.utilization_flag := l_new_job_utilizable;
3922 l_resource_rec_old.schedulable_flag := l_old_job_schedulable;
3923 l_resource_rec_new.schedulable_flag := l_new_job_schedulable;
3924
3925 -- loop through all record of the person in pa_resources_denorm and update
3926 -- the job flags
3927 FOR rec IN res_denorm_recs LOOP
3928 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3929 l_resource_rec_new.resource_effective_end_date := rec.resource_effective_end_date;
3930 IF P_DEBUG_MODE = 'Y' THEN
3931 log_message('start_date:'||rec.resource_effective_start_date||', end_Date:'||
3932 rec.resource_effective_end_date);
3933 END IF;
3934
3935 PA_RESOURCE_PVT.update_resource_denorm(
3936 p_resource_denorm_old_rec => l_resource_rec_old
3937 ,p_resource_denorm_new_rec => l_resource_rec_new
3938 ,x_return_status => l_return_status
3939 ,x_msg_data => x_msg_data
3940 ,x_msg_count => x_msg_count);
3941
3942 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3943 x_return_status := FND_API.G_RET_STS_ERROR ;
3944 END IF;
3945 END LOOP;
3946
3947 END IF;
3948
3949 IF P_DEBUG_MODE = 'Y' THEN
3950 log_message('before calling Create_Forecast_Item');
3951 END IF;
3952 --Call Forecast Item regeneration API
3953 --Bug 7690398 put an if condition for the resource id
3954 IF (pa_resource_utils.get_resource_id(p_person_id) <> -999) THEN
3955 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3956 p_person_id => p_person_id,
3957 p_start_date => p_new_start_date,
3958 p_end_date => p_new_end_date,
3959 p_process_mode => 'GENERATE',
3960 x_return_status => l_return_status,
3961 x_msg_count => x_msg_count,
3962 x_msg_data => x_msg_data) ;
3963
3964 IF P_DEBUG_MODE = 'Y' THEN
3965 log_message('after calling Create_Forecast_Item, l_return_status:'||l_return_status);
3966 END IF;
3967 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
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
3977 log_message('exception was thrown');
3978 END IF;
3979 -- 4537865 : RESET x_msg_count and x_msg_data also
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
3989 /* Procedure Update_Supervisor calls Update_Resource_Denorm to update
3990 the resource's supervisor in the denorm table.
3991 */
3992 PROCEDURE Update_Supervisor(
3993 p_person_id IN per_all_people_f.person_id%TYPE,
3994 p_old_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
3995 p_new_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
3996 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3997 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3998 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3999 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4000 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4001
4002 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4003 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4004
4005 l_return_status VARCHAR2(1);
4006 l_manager_name pa_resources_denorm.manager_name%TYPE;
4007
4008 CURSOR res_denorm_recs IS
4009 SELECT resource_effective_start_date,
4010 resource_effective_end_date
4011 FROM pa_resources_denorm
4012 WHERE person_id = p_person_id
4013 AND p_new_end_date >= resource_effective_start_date
4014 AND resource_effective_start_date >= p_new_start_date
4015 AND resource_effective_end_date <= p_new_end_date
4016 ;
4017
4018 CURSOR manager_name IS
4019 SELECT DISTINCT resource_name
4020 FROM pa_resources_denorm
4021 WHERE person_id = p_new_supervisor
4022 ;
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;
4032 l_resource_rec_new.person_id := p_person_id;
4033
4034 l_resource_rec_old.manager_id := p_old_supervisor;
4035 l_resource_rec_new.manager_id := p_new_supervisor;
4036
4037 OPEN manager_name;
4038 FETCH manager_name INTO l_manager_name;
4039 l_resource_rec_new.manager_name := l_manager_name;
4040 CLOSE manager_name;
4041
4042 FOR rec IN res_denorm_recs LOOP
4043
4044 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
4045
4046 --Call Resource Denorm API
4047 PA_RESOURCE_PVT.update_resource_denorm(
4048 p_resource_denorm_old_rec => l_resource_rec_old
4049 ,p_resource_denorm_new_rec => l_resource_rec_new
4050 ,x_return_status => l_return_status
4051 ,x_msg_data => x_msg_data
4052 ,x_msg_count => x_msg_count);
4053
4054 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4055 x_return_status := FND_API.G_RET_STS_ERROR ;
4056 END IF;
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
4075 /* Procedure Update_PrimaryFlag calls Update_EndDate to end date the
4076 record for which the assignment record's primary flag has changed form
4077 yes to no and then calls Create_Forecast_Item to regenerate
4078 the forecast items for this resource.
4079 */
4080 PROCEDURE Update_PrimaryFlag(
4081 p_person_id IN per_all_people_f.person_id%TYPE,
4082 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
4083 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
4084 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
4085 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
4086 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4087 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4088 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4089
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
4099 -- Solves bug 1608837
4100 /* Update_EndDate(p_person_id => p_person_id,
4101 p_old_start_date => p_old_start_date,
4102 p_new_start_date => p_new_start_date,
4103 p_old_end_date => p_old_end_date,
4104 p_new_end_date => p_new_end_date,
4105 x_return_status => x_return_status,
4106 x_msg_data => x_msg_data,
4107 x_msg_count => x_msg_count);
4108 */
4109
4110 --Bug 9665238 put an if condition for the resource id
4111 IF (pa_resource_utils.get_resource_id(p_person_id) <> -999) THEN
4112 --Call Forecast Item regeneration API
4113 PA_FORECASTITEM_PVT.Create_Forecast_Item(
4114 p_person_id => p_person_id,
4115 p_start_date => p_new_start_date,
4116 p_end_date => p_new_end_date,
4117 p_process_mode => 'GENERATE',
4118 x_return_status => l_return_status,
4119 x_msg_count => x_msg_count,
4120 x_msg_data => x_msg_data) ;
4121 END IF;
4122
4123 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
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
4142 /* Procedure Update_Name calls updates the resource's name in pa_resources.
4143 */
4144 PROCEDURE Update_Name(
4145 p_person_id IN per_all_people_f.person_id%TYPE,
4146 p_old_name IN per_all_people_f.full_name%TYPE,
4147 p_new_name IN per_all_people_f.full_name%TYPE,
4148 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4149 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4150 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4151
4152 l_resource_id pa_resources.resource_id%TYPE;
4153 l_return_status VARCHAR2(1);
4154
4155 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
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);
4165
4166 IF (l_resource_id = -999) THEN
4167 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4168 return;
4169 END IF;
4170
4171 UPDATE pa_resources
4172 SET name = p_new_name
4173 WHERE resource_id = l_resource_id;
4174
4175 --Set the values for the Resources Denorm Record Type
4176 l_resource_rec_old.person_id := p_person_id;
4177 l_resource_rec_new.person_id := p_person_id;
4178
4179 l_resource_rec_old.resource_name := p_old_name;
4180 l_resource_rec_new.resource_name := p_new_name;
4181
4182 --Call Resource Denorm API
4183 PA_RESOURCE_PVT.update_resource_denorm(
4184 p_resource_denorm_old_rec => l_resource_rec_old
4185 ,p_resource_denorm_new_rec => l_resource_rec_new
4186 ,x_return_status => l_return_status
4187 ,x_msg_data => x_msg_data
4188 ,x_msg_count => x_msg_count);
4189
4190 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
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
4209
4210
4211 -- Procedure Address_Change Modified for location denormalization
4212 -- to update the pa_resource_denorm when person address changes with respect
4213 -- date
4214 PROCEDURE address_change ( p_calling_mode in varchar2,
4215 p_person_id in number,
4216 p_country_old in varchar2,
4217 p_country_new in varchar2,
4218 p_city_old in varchar2,
4219 p_city_new in varchar2,
4220 p_region2_old in varchar2,
4221 p_region2_new in varchar2,
4222 p_date_from_old in date,
4223 p_date_from_new in date,
4224 p_date_to_old in date,
4225 p_date_to_new in date,
4226 p_addr_prim_flag_old in varchar2,
4227 p_addr_prim_flag_new in varchar2,
4228 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
4229 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
4230 x_msg_data out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
4231
4232 l_return_status VARCHAR2(1);
4233 l_resource_id NUMBER;
4234
4235 BEGIN
4236
4237
4238 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4239
4240 IF p_addr_prim_flag_new = 'Y' THEN
4241
4242 --dbms_output.put_line('Inside address Update for person id:' || p_person_id);
4243
4244 pa_r_project_resources_pub.create_resource (
4245 p_api_version => 1.0
4246 ,p_init_msg_list => NULL
4247 ,p_commit => FND_API.G_FALSE
4248 ,p_validate_only => NULL
4249 ,p_max_msg_count => NULL
4250 ,p_internal => 'Y'
4251 ,p_person_id => p_person_id
4252 ,p_individual => 'Y'
4253 ,p_resource_type => 'EMPLOYEE'
4254 ,x_return_status => l_return_status
4255 ,x_msg_count => x_msg_count
4256 ,x_msg_data => x_msg_data
4257 ,x_resource_id => l_resource_id);
4258
4259 END IF;
4260
4261 --dbms_output.put_line('After address Update');
4262
4263 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4264 x_return_status := FND_API.G_RET_STS_ERROR ;
4265 END IF;
4266
4267 EXCEPTION
4268 when others then
4269 NULL;
4270 raise;
4271
4272 END address_change;
4273
4274 FUNCTION Get_Country_name(p_country_code VARCHAR2) RETURN VARCHAR2 IS
4275
4276 l_country_code VARCHAR2(240);
4277
4278 BEGIN
4279
4280 SELECT territory_short_name
4281 INTO l_country_code
4282 FROM fnd_territories_vl
4283 WHERE territory_code = p_country_code ;
4284
4285 RETURN l_country_code ;
4286 EXCEPTION
4287
4288 WHEN NO_DATA_FOUND THEN
4289 RETURN NULL;
4290
4291 WHEN OTHERS THEN
4292
4293 RAISE ;
4294
4295 END ;
4296
4297 FUNCTION get_job_name(P_job_id IN per_jobs.job_id%type)
4298 RETURN per_jobs.name%type IS
4299
4300 l_job_name varchar2(240);
4301
4302 BEGIN
4303
4304 SELECT name
4305 INTO l_job_name
4306 FROM per_jobs
4307 WHERE job_id = P_job_id;
4308
4309 RETURN (l_job_name) ;
4310 EXCEPTION
4311
4312 WHEN NO_DATA_FOUND THEN
4313 RETURN NULL;
4314
4315 WHEN OTHERS THEN
4316
4317 RAISE ;
4318
4319 END ;
4320
4321 FUNCTION get_org_name(P_org_id IN hr_all_organization_units.organization_id%type)
4322 RETURN hr_all_organization_units.name%type IS
4323 l_org_name varchar2(240);
4324
4325 BEGIN
4326
4327 SELECT name
4328 INTO l_org_name
4329 FROM hr_all_organization_units_tl
4330 WHERE organization_id = P_org_id
4331 AND language = USERENV('LANG');
4332
4333 RETURN (l_org_name) ;
4334 EXCEPTION
4335 WHEN NO_DATA_FOUND THEN
4336 RETURN NULL;
4337
4338 WHEN OTHERS THEN
4339 RAISE ;
4340 END ;
4341
4342 FUNCTION get_grade_name(P_grade_id IN NUMBER)
4343 RETURN VARCHAR2 IS
4344 l_grade_name varchar2(240);
4345
4346 BEGIN
4347 SELECT name
4348 INTO l_grade_name
4349 FROM per_grades
4350 WHERE grade_id = P_grade_id;
4351
4352 RETURN (l_grade_name) ;
4353 EXCEPTION
4354 WHEN NO_DATA_FOUND THEN
4355 RETURN NULL;
4356
4357 WHEN OTHERS THEN
4358 RAISE ;
4359 END ;
4360
4361 -- Added for bug 3957522
4362 -- Procedure to delete records in pa_resources_denorm
4363 PROCEDURE Delete_PA_Resource_Denorm(
4364 p_person_id IN per_all_people_f.person_id%TYPE,
4365 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
4366 p_old_end_date IN per_all_assignments_f.effective_end_date%TYPE,
4367 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4368 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4369 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4370
4371
4372 CURSOR res_denorm_recs IS
4373 SELECT resource_effective_start_date,
4374 resource_effective_end_date
4375 FROM pa_resources_denorm
4376 WHERE person_id = p_person_id
4377 AND resource_effective_start_date >= p_old_start_date
4378 AND resource_effective_end_date <= p_old_end_date
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
4388 DELETE FROM PA_RESOURCES_DENORM
4389 WHERE person_id = p_person_id
4390 AND resource_effective_start_date = rec.resource_effective_start_date;
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
4400 x_return_status := FND_API.G_RET_STS_ERROR ;
4401
4402 WHEN OTHERS THEN
4403 -- 4537865 : RESET x_msg_count and x_msg_data also
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
4413 END PA_HR_UPDATE_API;
[Home] [Help]