1 PACKAGE BODY PA_HR_UPDATE_API AS
2 -- $Header: PARHRUPB.pls 120.17.12010000.3 2008/11/07 09:03:22 kjai 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
263 WHERE person_id = p_person_id
264 AND p_date BETWEEN effective_start_date
265 AND effective_end_date
266 AND job_id is NOT NULL
267 AND primary_flag = 'Y'
268 AND assignment_type in ('E', 'C');
269 END IF;
270
271 IF l_job_id is NOT NULL THEN
272 SELECT jei_information6
273 INTO l_schedulable_flag
274 FROM per_job_extra_info
275 WHERE job_id = l_job_id
276 AND information_type = l_job_info_type
277 AND jei_information6 IS NOT NULL;
278
279 IF l_schedulable_flag IS NULL THEN
280 l_schedulable_flag := 'N';
281 END IF;
282 END IF;
283
284 RETURN l_schedulable_flag;
285 EXCEPTION
286 WHEN TOO_MANY_ROWS THEN
287 l_schedulable_flag := 'X';
288 RETURN l_schedulable_flag;
289 WHEN NO_DATA_FOUND then
290 RETURN l_schedulable_flag;
291
292 END check_job_schedulable;
293
294
295
296 -- This Procedure checks whether the given OU is valid or Not
297 PROCEDURE check_exp_OU(p_org_id IN NUMBER
298 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
299 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
300 ) IS
301
302 v_error_message_code VARCHAR2(1000) := 'PA_INVALID_EXP_OU';
303 --'Invalid Operating Unit';
304 v_return_status VARCHAR2(1);
305 v_dummy VARCHAR2(1):= 'N';
306 BEGIN
307 -- Initialize the Error stack
308 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.check_exp_OU');
309 -- Initialize the error status
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311
312 If p_org_id is NOT NULL then
313
314 SELECT 'Y'
315 INTO v_dummy
316 FROM pa_implementations_all
317 WHERE org_id = p_org_id
318 AND rownum = 1;
319
320 End if;
321 /*
322
323 If v_dummy = 'Y' then
324 x_error_message_code := 'Exp Ou';
325 End if;
326 */
327 -- reset the Error stack
328 PA_DEBUG.Reset_Err_Stack;
329
330 EXCEPTION
331
332 WHEN NO_DATA_FOUND THEN
333 x_error_message_code := v_error_message_code;
334 x_return_status := FND_API.G_RET_STS_ERROR;
335
336
337 WHEN OTHERS THEN
338 -- Set the exception Message and the stack
339 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.check_exp_OU'
340 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342 -- 4537865 : RESET x_error_message_code also
343 x_error_message_code := SQLCODE ;
344
345 raise;
346
347
348
349 END check_exp_OU;
350
351
352 -- This API makes calls to PA_REOSURCE_PVT.UPDATE_RESOURCE_DENORM api
353 -- which actually updates the pa_reosurces_denorm entity
354 PROCEDURE call_create_resource_denorm
355 (P_job_id_old per_jobs.job_id%type
356 ,P_job_id_new per_jobs.job_id%type
357 ,P_job_level_old NUMBER
358 ,P_job_level_new NUMBER
359 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
360 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
361 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
362 ) IS
363 v_return_status VARCHAR2(2000);
364 v_error_message_code VARCHAR2(2000);
365 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
366 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
367 v_job_level_old NUMBER := 0;
368 v_job_level_new NUMBER;
369 v_job_id_old PER_JOBS.JOB_ID%type;
370 v_job_id_new PER_JOBS.JOB_ID%type;
371 v_msg_data VARCHAR2(2000);
372 v_msg_count NUMBER;
373 BEGIN
374 -- Initialize the Error stack
375 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_create_resoruce_denorm');
376 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
377 v_job_id_new := P_job_id_new;
378 v_job_level_new := P_job_level_new;
379 v_job_id_old := P_job_id_old;
380 v_job_level_old := 0;
381 If v_job_level_new is NULL then
382 v_job_level_new := 0; ---- to be confirmed if no job level is found
383 end if; ----- then assign joblevel to zero when grade is deleted
384
385 if P_job_level_old is NOT NULL then
386 v_job_level_old := P_job_level_old;
387 end if;
388 If P_job_id_old is NULL then
389 v_job_id_old := P_job_id_new;
390 end if;
391
392 if v_job_id_new is NOT NULL and v_job_level_new is NOT NULL then
393 v_resource_rec_old.job_id := v_job_id_old;
394 v_resource_rec_old.resource_job_level := v_job_level_old;
395 v_resource_rec_new.job_id := v_job_id_new;
396 v_resource_rec_new.resource_job_level := v_job_level_new;
397
398 -- Call PRM API update resource denorm which actually updates the
399 -- pa_resource_denorm entity
400 PA_RESOURCE_PVT.update_resource_denorm
401 ( p_resource_denorm_old_rec => v_resource_rec_old
402 ,p_resource_denorm_new_rec => v_resource_rec_new
403 ,x_return_status => x_return_status
404 ,x_msg_data => x_msg_data
405 ,x_msg_count => x_msg_count
406 );
407
408
409 End if;
410
411
412
413 -- reset the Error stack
414 PA_DEBUG.Reset_Err_Stack;
415
416 EXCEPTION
417
418 WHEN OTHERS THEN
419 -- 4537865 : RESET x_msg_count and x_msg_data also
420 x_msg_count := 1 ;
421 x_msg_data := SUBSTRB(SQLERRM ,1,240);
422
423 -- Set the exception Message and the stack
424 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_API.call_create_resource_denorm'
425 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
427 raise;
428
429
430
431 END call_create_resource_denorm;
432
433 -- This API makes calls to PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API
434 -- which actually updates the pa_resources_denorm entity
435 -- This API will update the job level of the job id passed in of the
436 -- resources denorm records
437 PROCEDURE update_job_level_res_denorm
438 ( P_job_id_old per_jobs.job_id%type
439 ,P_job_id_new per_jobs.job_id%type
440 ,P_job_level_old NUMBER
441 ,P_job_level_new NUMBER
442 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
443 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
444 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
445 ) IS
446
447 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
448 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
449 l_job_level_old NUMBER;
450 l_job_level_new NUMBER;
451 l_job_id_old PER_JOBS.JOB_ID%type;
452 l_job_id_new PER_JOBS.JOB_ID%type;
453 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
454
455 BEGIN
456 -- Initialize the Error stack
457 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_res_denorm');
458 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
459
460 l_job_id_new := P_job_id_new;
461 l_job_level_new := P_job_level_new;
462 l_job_id_old := P_job_id_old;
463 l_job_level_old := P_job_level_old;
464
465 IF P_DEBUG_MODE = 'Y' THEN
466 log_message('====== Job Id Info ==========');
467 log_message('Job Id = ' || l_job_id_new);
468 log_message('Job Level Old = ' || l_job_level_old);
469 log_message('Job Level New = ' || l_job_level_new);
470 END IF;
471
472 l_resource_rec_old.job_id := l_job_id_old;
473 l_resource_rec_old.resource_job_level := l_job_level_old;
474 l_resource_rec_new.job_id := l_job_id_new;
475 l_resource_rec_new.resource_job_level := l_job_level_new;
476
477 -- Call PRM API update resource denorm which actually updates the
478 -- pa_resource_denorm entity
479 PA_RESOURCE_PVT.update_resource_denorm
480 ( p_resource_denorm_old_rec => l_resource_rec_old
481 ,p_resource_denorm_new_rec => l_resource_rec_new
482 ,x_return_status => x_return_status
483 ,x_msg_data => x_msg_data
484 ,x_msg_count => x_msg_count
485 );
486
487 -- reset the Error stack
488 PA_DEBUG.Reset_Err_Stack;
489
490 EXCEPTION
491
492 WHEN OTHERS THEN
493 -- 4537865 : RESET x_msg_count and x_msg_data also
494 x_msg_count := 1 ;
495 x_msg_data := SUBSTRB(SQLERRM ,1,240);
496
497 -- Set the exception Message and the stack
498 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_job_level_res_denorm'
499 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501 raise;
502
503 END update_job_level_res_denorm;
504
505 -- This API gets all jobs belonging to the master job id and updates
506 -- the resource denorm records that has the affected job id
507 PROCEDURE update_all_jobs
508 ( P_job_id per_jobs.job_id%type
509 ,P_job_level_old pa_resources_denorm.resource_job_level%type
510 ,P_job_level_new pa_resources_denorm.resource_job_level%type
511 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
512 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
513 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
514 ) IS
515
516 l_job_id PER_JOBS.JOB_ID%type;
517 l_job_level_old pa_resources_denorm.resource_job_level%type;
518 l_job_level_new pa_resources_denorm.resource_job_level%type;
519 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
520 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
521
522 -- Cursor to get all affected job ids with the P_job_id level change
523 CURSOR get_job_ids(
524 l_job_id per_jobs.job_id%type
525 ) is
526 SELECT l_job_id effected_job_id
527 FROM sys.dual
528 ,per_job_groups pjg
529 WHERE pjg.master_flag = 'Y'
530 AND pjg.job_group_id = get_job_group_id(l_job_id)
531 UNION
532 SELECT distinct pjr.from_job_id effected_job_id
533 FROM pa_job_relationships pjr
534 ,per_job_groups pjg
535 WHERE pjg.master_flag = 'Y'
536 AND pjr.to_job_id = l_job_id
537 AND pjr.to_job_group_id = pjg.job_group_id
538 UNION
539 SELECT distinct pjr.to_job_id effected_job_id
540 FROM pa_job_relationships pjr
541 ,per_job_groups pjg
542 WHERE pjg.master_flag = 'Y'
543 AND pjr.from_job_id = l_job_id
544 AND pjr.from_job_group_id = pjg.job_group_id;
545
546 -- P_DEBUG_MODE varchar2(1); -- Bug 4352236
547 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
548
549 BEGIN
550
551 -- Initialize the Error stack
552 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_all_jobs');
553 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
554
555 l_job_id := P_job_id;
556
557 -- just assign the value to the local variable because job_level can be
558 -- null and do not need a default value
559 l_job_level_old := P_job_level_old;
560 l_job_level_new := P_job_level_new;
561
562
563 -- if the job level changes then update all the jobs which are affected and
564 -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
565 OPEN get_job_ids(l_job_id) ;
566 LOOP
567 fetch get_job_ids into l_job_id;
568 Exit when get_job_ids%NOTFOUND;
569
570 If l_job_id is NOT NULL then
571 -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
572 IF (P_DEBUG_MODE ='Y') THEN
573 pa_debug.g_err_stage := 'Log: Job Level change for job_id';
574 pa_debug.write_file('LOG',pa_debug.g_err_stage);
575 end if;
576
577 update_job_level_res_denorm
578 ( P_job_id_old => l_job_id
579 ,P_job_id_new => l_job_id
580 ,P_job_level_old => l_job_level_old
581 ,P_job_level_new => l_job_level_new
582 ,x_return_status => x_return_status
583 ,x_msg_data => x_msg_data
584 ,x_msg_count => x_msg_count
585 );
586 End if;
587
588 END LOOP;
589 CLOSE get_job_ids;
590
591 -- reset the Error stack
592 PA_DEBUG.Reset_Err_Stack;
593
594 EXCEPTION
595
596 WHEN OTHERS THEN
597 -- 4537865 : RESET x_msg_count and x_msg_data also
598 x_msg_count := 1 ;
599 x_msg_data := SUBSTRB(SQLERRM ,1,240);
600
601 -- Set the exception Message and the stack
602 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.update_all_jobs'
603 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605 raise;
606
607 END update_all_jobs;
608
609 -- This Procedure updates all resource denorm records affected by the
610 -- changes in job mappings in pa_job_relationships table
611 -- It will update the job levels of the resource denorm records
612 PROCEDURE pa_job_relation_job_id
613 (P_calling_mode IN VARCHAR2
614 ,P_from_job_id_old IN pa_job_relationships.from_job_id%type
615 ,P_from_job_id_new IN pa_job_relationships.from_job_id%type
616 ,P_to_job_id_old IN pa_job_relationships.from_job_id%type
617 ,P_to_job_id_new IN pa_job_relationships.from_job_id%type
618 ,P_from_job_group_id IN pa_job_relationships.from_job_id%type
619 ,P_to_job_group_id IN pa_job_relationships.from_job_id%type
620 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
621 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
622 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
623
624 l_job_level_old NUMBER;
625 l_job_level_new NUMBER;
626 l_job_id PER_JOBS.JOB_ID%type;
627 l_master_job_id PER_JOBS.JOB_ID%type;
628 l_PRJG_job_id PER_JOBS.JOB_ID%type;
629 l_condition VARCHAR2(10);
630 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
631
632 BEGIN
633 -- Initialize the Error stack
634 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pa_job_relation_job_id');
635 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
636
637 IF P_DEBUG_MODE = 'Y' THEN
638 log_message('P_from_job_id_old = ' || P_from_job_id_old);
639 log_message('P_from_job_id_new = ' || P_from_job_id_new);
640 log_message('P_to_job_id_old = ' || P_to_job_id_old);
641 log_message('P_to_job_id_new = ' || P_to_job_id_new);
642 log_message('P_from_job_group_id = ' || P_from_job_group_id);
643 log_message('P_to_job_group_id = ' || P_to_job_group_id);
644 END IF;
645
646 If P_calling_mode = 'INSERT' OR P_calling_mode = 'UPDATE' Then
647
648 IF P_DEBUG_MODE = 'Y' THEN
649 log_message('P_calling_mode = ' || P_calling_mode);
650 END IF;
651 ----------------------------------------------------------------------------
652 -- Need to check that the from_job_id belongs to the Proj_Res_Job_Group
653 -- If it is a PRJG job, we need to check that the to_job_id is a master job
654 -- and update the resource denorm records with the job level of the PRJG job
655 -----------------------------------------------------------------------------
656
657 If (PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_new, P_from_job_group_id) = 'Y' AND
658 PA_JOB_UTILS.check_master_job(P_to_job_id_new)) then
659
660 IF P_DEBUG_MODE = 'Y' THEN
661 log_message('From is PRJG, To is Master');
662 END IF;
663
664 l_job_level_new := PA_JOB_UTILS.get_job_level(P_from_job_id_new,P_from_job_group_id);
665
666 l_PRJG_job_id := P_from_job_id_new;
667 l_master_job_id := P_to_job_id_new;
668 l_condition := 'PM';
669
670 End if;
671
672
673 -----------------------------------------------------------------------------
674 -- Now, need to do the opposite: check that the to_job_id belongs to the PRJG
675 -- If it is a PRJG job, we need to check that the from_job_id is a master job
676 -- and update the resource denorm records with the job level of the PRJG job
677 -----------------------------------------------------------------------------
678
679 If (PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_new, P_to_job_group_id) = 'Y' AND
680 PA_JOB_UTILS.check_master_job(P_from_job_id_new)) then
681
682 IF P_DEBUG_MODE = 'Y' THEN
683 log_message('From is Master, To is PRJG');
684 END IF;
685 l_job_level_new := PA_JOB_UTILS.get_job_level(P_to_job_id_new,P_to_job_group_id);
686
687 l_PRJG_job_id := P_to_job_id_new;
688 l_master_job_id := P_from_job_id_new;
689 l_condition := 'PM';
690
691 End if;
692
693
694 -----------------------------------------------------------------------------
695 -- For Master and Normal job mapping, we need to get the job level of the job
696 -- and only updates the resource denorm records which has the normal job id
697 -- with the job level of the Master, PRJG job id
698 -- Case: from_job_id is Master and to_job_id is Normal job
699 ------------------------------------------------------------------------------
700
701 If (PA_JOB_UTILS.check_master_job(P_from_job_id_new) AND
702 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_new, P_to_job_group_id) = 'N' AND
703 NOT PA_JOB_UTILS.check_master_job(P_to_job_id_new)) THEN
704
705 IF P_DEBUG_MODE = 'Y' THEN
706 log_message('From is Master, To is Normal');
707 END IF;
708 l_job_level_new := PA_JOB_UTILS.get_job_level(P_from_job_id_new, P_from_job_group_id);
709
710 l_job_id := P_to_job_id_new;
711 l_condition := 'MN';
712
713 End If;
714
715 ----------------------------------------------------------------------------
716 -- Same as previous, but to_job_id is Master and from_job_id is normal job
717 ----------------------------------------------------------------------------
718 If (PA_JOB_UTILS.check_master_job(P_to_job_id_new) AND
719 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_new, P_from_job_group_id) = 'N' AND
720 NOT PA_JOB_UTILS.check_master_job(P_from_job_id_new)) THEN
721
722 IF P_DEBUG_MODE = 'Y' THEN
723 log_message('From is Normal, To is Master');
724 END IF;
725 l_job_level_new := PA_JOB_UTILS.get_job_level(P_to_job_id_new, P_to_job_group_id);
726
727 l_job_id := P_from_job_id_new;
728 l_condition := 'MN';
729
730 End If;
731
732
733 -- PRJG and Master Job Mapping
734 -- Update all jobs belonging to the Master job
735 If l_condition = 'PM' Then
736
737 IF P_DEBUG_MODE = 'Y' THEN
738 log_message('Updating records for PRJG job');
739 END IF;
740 update_job_level_res_denorm
741 ( P_job_id_old => NULL
742 ,P_job_id_new => l_PRJG_job_id
743 ,P_job_level_old => NULL
744 ,P_job_level_new => l_job_level_new
745 ,x_return_status => x_return_status
746 ,x_msg_data => x_msg_data
747 ,x_msg_count => x_msg_count
748 );
749
750 IF P_DEBUG_MODE = 'Y' THEN
751 log_message('Updating records for jobs belonging to master');
752 END IF;
753 update_all_jobs
754 ( P_job_id => l_master_job_id
755 ,P_job_level_old => NULL
756 ,P_job_level_new => l_job_level_new
757 ,x_return_status => x_return_status
758 ,x_msg_data => x_msg_data
759 ,x_msg_count => x_msg_count
760 );
761
762 -- Master and Normal Job mapping
763 -- Only update the normal job id
764 Elsif l_condition = 'MN' Then
765
766 IF P_DEBUG_MODE = 'Y' THEN
767 log_message('Updating records only for the normal job id');
768 END IF;
769 update_job_level_res_denorm
770 ( P_job_id_old => NULL
771 ,P_job_id_new => l_job_id
772 ,P_job_level_old => NULL
773 ,P_job_level_new => l_job_level_new
774 ,x_return_status => x_return_status
775 ,x_msg_data => x_msg_data
776 ,x_msg_count => x_msg_count
777 );
778
779 End If;
780
781 Elsif P_calling_mode = 'UPDATE' then
782
783 IF P_DEBUG_MODE = 'Y' THEN
784 log_message('P_calling_mode = UPDATE');
785 END IF;
786
787
788 Elsif P_calling_mode = 'DELETE' then
789
790 IF P_DEBUG_MODE = 'Y' THEN
791 log_message('P_calling_mode = DELETE');
792 END IF;
793 ------------------------------------------------------------------------------
794 -- The following condition checks if the from_job_id is a Master and to_job_id
795 -- is in the Proj_Res_Job_Group or the opposite
796 -- Also checks if the from_job_id is a Master Job and to_job_id is a normal job
797 -- id or the opposite
798 -- Sets the appropriate value for the job ids and the condition to update the
799 -- resource denorm records
800 ------------------------------------------------------------------------------
801
802 IF ((PA_JOB_UTILS.check_master_job(P_from_job_id_old) AND
803 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_old, P_to_job_group_id) = 'Y')) THEN
804
805 IF P_DEBUG_MODE = 'Y' THEN
806 log_message('From is Master, To is PRJG');
807 END IF;
808 l_master_job_id := P_from_job_id_old;
809 l_PRJG_job_id := P_to_job_id_old;
810 l_condition := 'PM';
811
812 ELSIF ((PA_JOB_UTILS.check_master_job(P_to_job_id_old) AND
813 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_old, P_from_job_group_id) = 'Y')) THEN
814
815 IF P_DEBUG_MODE = 'Y' THEN
816 log_message('From is PRJG, To is Master');
817 END IF;
818 l_master_job_id := P_to_job_id_old;
819 l_PRJG_job_id := P_from_job_id_old;
820 l_condition := 'PM';
821
822 ELSIF ( PA_JOB_UTILS.check_master_job(P_from_job_id_old) AND
823 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_to_job_id_old, P_to_job_group_id) = 'N' AND
824 NOT PA_JOB_UTILS.check_master_job(P_to_job_id_old)) THEN
825
826 IF P_DEBUG_MODE = 'Y' THEN
827 log_message('From is Master, To is Normal');
828 END IF;
829 l_master_job_id := P_from_job_id_old;
830 l_job_id := P_to_job_id_old;
831 l_condition := 'MN';
832
833 ELSIF ( PA_JOB_UTILS.check_master_job(P_to_job_id_old) AND
834 PA_JOB_UTILS.Is_Proj_Res_Job_Group(P_from_job_id_old, P_from_job_group_id) = 'N'AND
835 NOT PA_JOB_UTILS.check_master_job(P_from_job_id_old)) THEN
836
837 IF P_DEBUG_MODE = 'Y' THEN
838 log_message('From is Normal, To is Master');
839 END IF;
840 l_master_job_id := P_to_job_id_old;
841 l_job_id := P_from_job_id_old;
842 l_condition := 'MN';
843
844 END IF;
845
846
847 ------------------------------------------------------------
848 -- Next, update the resource denorm records correspondingly
849 -- If l_condition is 'PM' : PRJG and Master Jobs mapping,
850 -- We need to sets the job level to Null for all resource
851 -- denorm records with the job_id belonging to the Master,
852 -- and the Master Job itself
853 -- If l_condition is 'MN' : Master and Normal Jobs mapping,
854 -- We need to set the job level to Null ONLY for resource
855 -- denorm records with the job id of the Normal Job Id
856 ------------------------------------------------------------
857
858 If l_condition = 'PM' Then
859
860 IF P_DEBUG_MODE = 'Y' THEN
861 log_message('Updating records with job ids belong to Master');
862 log_message('Master Job Id = ' || l_master_job_id);
863 END IF;
864 update_all_jobs
865 ( P_job_id => l_master_job_id
866 ,P_job_level_old => NULL
867 ,P_job_level_new => NULL
868 ,x_return_status => x_return_status
869 ,x_msg_data => x_msg_data
870 ,x_msg_count => x_msg_count
871 );
872
873
874 Elsif l_condition = 'MN' Then
875
876 IF P_DEBUG_MODE = 'Y' THEN
877 log_message('Updating only records with the normal job id');
878 log_message('Job Id = ' || l_job_id);
879 END IF;
880 update_job_level_res_denorm
881 ( P_job_id_old => NULL
882 ,P_job_id_new => l_job_id
883 ,P_job_level_old => NULL
884 ,P_job_level_new => NULL
885 ,x_return_status => x_return_status
886 ,x_msg_data => x_msg_data
887 ,x_msg_count => x_msg_count
888 );
889 End If;
890
891 End if;
892
893
894
895 -- reset the Error stack
896 PA_DEBUG.Reset_Err_Stack;
897
898 EXCEPTION
899
900 WHEN NO_DATA_FOUND THEN
901 NULL;
902
903 WHEN OTHERS THEN
904 -- 4537865 : RESET x_msg_count and x_msg_data also
905 x_msg_count := 1 ;
906 x_msg_data := SUBSTRB(SQLERRM ,1,240);
907
908 -- Set the exception Message and the stack
909 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pa_job_relation_job_id'
910 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
912 raise;
913
914
915 END pa_job_relation_job_id;
916
917
918 -- Do updates on job level for PRJG job, master jobs and normal jobs
919 -- which has mapping to each other
920 PROCEDURE perform_job_updates
921 ( P_job_id per_jobs.job_id%type
922 ,P_job_level_old pa_resources_denorm.resource_job_level%type
923 ,P_job_level_new pa_resources_denorm.resource_job_level%type
924 ,P_job_group_id per_job_groups.job_group_id%type
925 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
926 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
927 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
928 ) IS
929
930 l_job_id PER_JOBS.JOB_ID%type;
931 l_master_job_id PER_JOBS.JOB_ID%type;
932 l_job_level_old pa_resources_denorm.resource_job_level%type;
933 l_job_level_new pa_resources_denorm.resource_job_level%type;
934 l_job_group_id per_job_groups.job_group_id%type;
935 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
936
937 -- Cursor to get the associated master job id of the PRJG job id
938 CURSOR get_master_job (
939 l_job_id per_jobs.job_id%type
940 ,l_job_group_id per_job_groups.job_group_id%type
941 ) IS
942
943 SELECT distinct pjr.from_job_id effected_job_id
944 FROM pa_job_relationships pjr
945 WHERE pjr.to_job_id = l_job_id
946 AND pjr.to_job_group_id = l_job_group_id
947 UNION
948 SELECT distinct pjr.to_job_id effected_job_id
949 FROM pa_job_relationships pjr
950 WHERE pjr.from_job_id = l_job_id
951 AND pjr.from_job_group_id = l_job_group_id;
952
953 BEGIN
954
955 -- Initialize the Error stack
956 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.perform_job_updates');
957 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
958 IF P_DEBUG_MODE = 'Y' THEN
959 log_message('**** Performing Job Level Updates ****');
960 END IF;
961
962 l_job_id := P_job_id;
963 l_job_level_old := P_job_level_old;
964 l_job_level_new := P_job_level_new;
965 l_job_group_id := P_job_group_id;
966
967 -- first updates the denorm records which has the PRJG job id
968 IF P_DEBUG_MODE = 'Y' THEN
969 log_message('Updating Denorm for PRJG job id = ' || l_job_id);
970 END IF;
971 update_job_level_res_denorm
972 ( P_job_id_old => l_job_id
973 ,P_job_id_new => l_job_id
974 ,P_job_level_old => l_job_level_old
975 ,P_job_level_new => l_job_level_new
976 ,x_return_status => x_return_status
977 ,x_msg_data => x_msg_data
978 ,x_msg_count => x_msg_count
979 );
980
981 -- next get the master job id and then updates all jobs
982 -- belonging to that master job id if the job id is NOT
983 -- a master job
984
985 If (PA_JOB_UTILS.check_master_job(l_job_id) = FALSE) Then
986
987 OPEN get_master_job(l_job_id, l_job_group_id);
988 LOOP
989 FETCH get_master_job INTO l_master_job_id;
990 Exit when get_master_job%NOTFOUND;
991
992 IF P_DEBUG_MODE = 'Y' THEN
993 log_message('Updating Denorm for jobs belongs to master job id = ' || l_master_job_id);
994 END IF;
995
996 update_all_jobs
997 ( P_job_id => l_master_job_id
998 ,P_job_level_old => l_job_level_old
999 ,P_job_level_new => l_job_level_new
1000 ,x_return_status => x_return_status
1001 ,x_msg_data => x_msg_data
1002 ,x_msg_count => x_msg_count
1003 );
1004 END LOOP;
1005 CLOSE get_master_job;
1006
1007 Else
1008
1009 IF P_DEBUG_MODE = 'Y' THEN
1010 log_message('Updating Denorm for jobs belongs to master job, also PRJG job id = ' || l_job_id);
1011 END IF;
1012 update_all_jobs
1013 ( P_job_id => l_job_id
1014 ,P_job_level_old => l_job_level_old
1015 ,P_job_level_new => l_job_level_new
1016 ,x_return_status => x_return_status
1017 ,x_msg_data => x_msg_data
1018 ,x_msg_count => x_msg_count
1019 );
1020 End If;
1021
1022 EXCEPTION
1023 -- when no data found from the get_master_job cursor, then there is no
1024 -- mapping, do not do any updates
1025 WHEN NO_DATA_FOUND THEN
1026 null;
1027 WHEN OTHERS THEN -- Included WHEN OTHERS Block for 4537865
1028 -- 4537865 : RESET x_msg_count and x_msg_data also
1029 x_msg_count := 1 ;
1030 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1032 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
1033 p_procedure_name => 'perform_job_updates',
1034 p_error_text => x_msg_data );
1035 RAISE ;
1036 END perform_job_updates;
1037
1038
1039
1040 -- Main API for job level change, job mapping change
1041 -- This API will update the resource denorm records with the job level change of a job id.
1042 -- It depends on the type of job : whether it is in the Project Resource Job Group or not
1043 PROCEDURE update_job_level_dff
1044 ( P_job_id per_jobs.job_id%type
1045 ,P_job_level_old pa_resources_denorm.resource_job_level%type
1046 ,P_job_level_new pa_resources_denorm.resource_job_level%type
1047 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1048 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1049 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1050 ) IS
1051
1052 l_job_id PER_JOBS.JOB_ID%type;
1053 l_job_level_old pa_resources_denorm.resource_job_level%type;
1054 l_job_level_new pa_resources_denorm.resource_job_level%type;
1055 l_job_group_id per_job_groups.job_group_id%type;
1056 l_isPRJG VARCHAR2(1);
1057 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1058
1059 BEGIN
1060
1061 -- Initialize the Error stack
1062 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.update_job_level_dff');
1063 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1064
1065 l_job_id := P_job_id;
1066 l_job_level_old := P_job_level_old;
1067 l_job_level_new := P_job_level_new;
1068
1069 -- When Project Resource Job Group does not exist, we just use
1070 -- the job id and job level passed in and update the resource
1071 -- denorm records affected by this change
1072 IF PA_JOB_UTILS.Proj_Res_Job_Group_Exists(p_job_id => l_job_id) = 'N' THEN
1073 IF P_DEBUG_MODE = 'Y' THEN
1074 log_message('Proj_Res_Job_Group does not exist');
1075 log_message('Update Denorm for the Job Id and Job Level');
1076 END IF;
1077
1078 update_job_level_res_denorm
1079 ( P_job_id_old => l_job_id
1080 ,P_job_id_new => l_job_id
1081 ,P_job_level_old => l_job_level_old
1082 ,P_job_level_new => l_job_level_new
1083 ,x_return_status => x_return_status
1084 ,x_msg_data => x_msg_data
1085 ,x_msg_count => x_msg_count
1086 );
1087 ELSE
1088 -----------------------------------------------------------
1089 -- This is the case when the Proj_Res_Job_Group value exist
1090 -----------------------------------------------------------
1091 IF P_DEBUG_MODE = 'Y' THEN
1092 log_message('Proj_Res_Job_Group Exist');
1093 END IF;
1094
1095 -- get the job group id of the job id passed in
1096 l_job_group_id := PA_JOB_UTILS.get_job_group_id(l_job_id);
1097 IF P_DEBUG_MODE = 'Y' THEN
1098 log_message('Job Group Id = ' || l_job_group_id );
1099 END IF;
1100
1101 -- check whether the job group is the Proj_Res_Job_Group
1102 l_isPRJG := PA_JOB_UTILS.Is_Proj_Res_Job_Group
1103 ( p_job_id => l_job_id
1104 ,p_job_group_id => l_job_group_id);
1105
1106 -------------------------------------------------------------
1107 -- When l_isPRJG is 'Y', we need to get the master job id
1108 -- associated with the job id passed in from the job mapping
1109 -- and update all resource denorm records of the affected
1110 -- jobs
1111 -- Call procedure 'perform_job_updates' for this
1112 -- When the value is 'N', we not need to change anything
1113 -------------------------------------------------------------
1114
1115 If l_isPRJG = 'Y' Then
1116
1117 IF P_DEBUG_MODE = 'Y' THEN
1118 log_message('Job Id passed in belongs to PRJG job group');
1119 END IF;
1120
1121 perform_job_updates
1122 ( P_job_id => l_job_id
1123 ,P_job_level_old => l_job_level_old
1124 ,P_job_level_new => l_job_level_new
1125 ,P_job_group_id => l_job_group_id
1126 ,x_return_status => x_return_status
1127 ,x_msg_data => x_msg_data
1128 ,x_msg_count => x_msg_count
1129 );
1130
1131 End If;
1132
1133 END IF;
1134
1135 EXCEPTION
1136 -- when no data found from the get_master_job cursor, then there is no
1137 -- mapping, do not do any updates
1138 WHEN NO_DATA_FOUND THEN
1139 null;
1140 WHEN OTHERS THEN -- Included WHEN OTHERS Block for 4537865
1141 -- 4537865 : RESET x_msg_count and x_msg_data also
1142 x_msg_count := 1 ;
1143 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1145 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API',
1146 p_procedure_name => 'update_job_level_dff',
1147 p_error_text => x_msg_data );
1148 RAISE ;
1149 END update_job_level_dff;
1150
1151
1152
1153
1154
1155 -- This Procedure gets list of all the jobs which are afftected due to changes in
1156 -- grade_id in per_valid_grade entity
1157 PROCEDURE per_valid_grades_job_id
1158 (P_job_id IN per_jobs.job_id%type
1159 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1160 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1161 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1162
1163 v_return_status VARCHAR2(2000);
1164 v_error_message_code VARCHAR2(2000);
1165 v_job_group_id PER_JOBS.JOB_GROUP_ID%type;
1166 v_job_level_old NUMBER;
1167 v_job_level_new NUMBER;
1168 v_job_id PER_JOBS.JOB_ID%type;
1169 v_msg_data VARCHAR2(2000);
1170 v_msg_count NUMBER;
1171
1172 CURSOR get_job_ids(
1173 l_job_id per_jobs.job_id%type
1174 ) is
1175 SELECT l_job_id effected_job_id
1176 FROM sys.dual
1177 ,per_job_groups pjg
1178 WHERE pjg.master_flag = 'Y'
1179 AND pjg.job_group_id = get_job_group_id(l_job_id)
1180 UNION
1181 SELECT distinct pjr.from_job_id effected_job_id
1182 FROM pa_job_relationships pjr
1183 ,per_job_groups pjg
1184 WHERE pjg.master_flag = 'Y'
1185 AND pjr.to_job_id = l_job_id
1186 AND pjr.to_job_group_id = pjg.job_group_id
1187 UNION
1188 SELECT distinct pjr.to_job_id effected_job_id
1189 FROM pa_job_relationships pjr
1190 ,per_job_groups pjg
1191 WHERE pjg.master_flag = 'Y'
1192 AND pjr.from_job_id = l_job_id
1193 AND pjr.from_job_group_id = pjg.job_group_id
1194 UNION
1195 SELECT l_job_id effected_job_id
1196 FROM sys.dual
1197 ,per_job_groups pjg
1198 WHERE pjg.master_flag = 'N'
1199 AND pjg.job_group_id = get_job_group_id(l_job_id)
1200 AND NOT EXISTS (
1201 SELECT 'Y'
1202 FROM per_job_groups
1203 WHERE master_flag = 'Y'
1204 AND job_group_id = get_job_group_id(l_job_id)
1205 );
1206 BEGIN
1207
1208 -- Initialize the Error stack
1209 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_valid_grades_job_id');
1210 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1211
1212 -- if the grade id changes then update all the jobs which are affected and
1213 -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1214 OPEN get_job_ids(P_job_id) ;
1215 LOOP
1216 fetch get_job_ids into v_job_id;
1217 Exit when get_job_ids%NOTFOUND;
1218 v_job_group_id := get_job_group_id(v_job_id);
1219 v_job_level_new := get_job_level(v_job_id,v_job_group_id);
1220 v_job_level_old := 0;
1221
1222 If v_job_id is NOT NULL then
1223 call_create_resource_denorm
1224 (P_job_id_old => v_job_id
1225 ,P_job_id_new => v_job_id
1226 ,P_job_level_old => v_job_level_old
1227 ,P_job_level_new => v_job_level_new
1228 ,x_return_status => x_return_status
1229 ,x_msg_data => x_msg_data
1230 ,x_msg_count => x_msg_count
1231 );
1232 End if;
1233
1234
1235 END LOOP;
1236 CLOSE get_job_ids;
1237
1238 -- reset the Error stack
1239 PA_DEBUG.Reset_Err_Stack;
1240
1241 EXCEPTION
1242
1243 WHEN NO_DATA_FOUND THEN
1244 NULL;
1245
1246 WHEN OTHERS THEN
1247 -- 4537865 : RESET x_msg_count and x_msg_data also
1248 x_msg_count := 1 ;
1249 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1250 -- Set the exception Message and the stack
1251 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_valid_grades_job_id'
1252 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1254 raise;
1255
1256
1257 END per_valid_grades_job_id;
1258
1259 -- This Procedure gets list of all the jobs which are afftected due to changes in
1260 -- sequence(job level) in per grades entity
1261 PROCEDURE per_grades_job_id
1262 (P_grade_id IN per_grades.grade_id%type
1263 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1264 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1265 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1266 ) IS
1267
1268 v_return_status VARCHAR2(2000);
1269 v_error_message_code VARCHAR2(2000);
1270 v_job_group_id PER_JOBS.JOB_GROUP_ID%type;
1271 v_job_level_new NUMBER;
1272 v_job_level_old NUMBER;
1273 v_job_id PER_JOBS.JOB_ID%type;
1274 v_row_num NUMBER := 0;
1275 v_msg_data VARCHAR2(2000);
1276 v_msg_count NUMBER;
1277 CURSOR get_job_ids(l_grade_id per_grades.grade_id%type) is
1278 SELECT distinct pvg.job_id
1279 FROM per_valid_grades pvg
1280 ,per_job_groups pjg
1281 WHERE
1282 pvg.grade_id = l_grade_id
1283 AND pjg.master_flag = 'Y'
1284 AND pjg.job_group_id = get_job_group_id(pvg.job_id)
1285 UNION
1286 SELECT distinct pjr.from_job_id
1287 FROM per_valid_grades pvg
1288 ,pa_job_relationships pjr
1289 ,per_job_groups pjg
1290 WHERE pjg.master_flag = 'Y'
1291 AND pjr.to_job_id = pvg.job_id
1292 AND pjr.to_job_group_id = pjg.job_group_id
1293 AND pvg.grade_id = l_grade_id
1294 UNION
1295 SELECT distinct pjr.to_job_id
1296 FROM per_valid_grades pvg
1297 ,pa_job_relationships pjr
1298 ,per_job_groups pjg
1299 WHERE pjg.master_flag = 'Y'
1300 AND pjr.from_job_id = pvg.job_id
1301 AND pjr.from_job_group_id = pjg.job_group_id
1302 AND pvg.grade_id = l_grade_id
1303 UNION
1304 SELECT distinct pvg.job_id
1305 FROM per_valid_grades pvg
1306 ,per_job_groups pjg
1307 WHERE pjg.master_flag = 'N'
1308 AND pjg.job_group_id = get_job_group_id(pvg.job_id)
1309 AND pvg.grade_id = l_grade_id
1310 AND NOT EXISTS (
1311 SELECT 'Y'
1312 FROM per_job_groups
1313 WHERE master_flag = 'Y'
1314 AND job_group_id = get_job_group_id(pvg.job_id)
1315 );
1316
1317
1318 BEGIN
1319
1320
1321 -- Initialize the Error stack
1322 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_grades_job_id');
1323 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1324
1325 -- if the sequence is changes then update all the jobs which are affected due to
1326 -- call PA_RESOURCE_PVT.UPDATE_RESOURCE_DENORM API to update the resource_denorm_table
1327 OPEN get_job_ids(P_grade_id) ;
1328 LOOP
1329 fetch get_job_ids into v_job_id;
1330 Exit when get_job_ids%NOTFOUND;
1331 v_job_group_id := get_job_group_id(v_job_id);
1332 v_job_level_new := get_job_level(v_job_id,v_job_group_id);
1333 v_job_level_old := 0;
1334 if v_job_id is NOT NULL then
1335 call_create_resource_denorm
1336 (P_job_id_old => v_job_id
1337 ,P_job_id_new => v_job_id
1338 ,P_job_level_old => v_job_level_old
1339 ,P_job_level_new => v_job_level_new
1340 ,x_return_status => x_return_status
1341 ,x_msg_data => x_msg_data
1342 ,x_msg_count => x_msg_count
1343 );
1344 End if;
1345
1346
1347 END LOOP;
1348 CLOSE get_job_ids;
1349
1350 -- reset the Error stack
1351 PA_DEBUG.Reset_Err_Stack;
1352 EXCEPTION
1353
1354 WHEN NO_DATA_FOUND THEN
1355 NULL;
1356
1357 WHEN OTHERS THEN
1358 -- 4537865 : RESET x_msg_count and x_msg_data also
1359 x_msg_count := 1 ;
1360 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1361 -- Set the exception Message and the stack
1362 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_grades_job_id'
1363 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1365 raise;
1366
1367
1368
1369 END per_grades_job_id;
1370
1371
1372
1373 -- This Procedure will get a list of all affected jobs due to change in the job mapping
1374 -- and then calls to PRM API GET_JOB_LEVEL in a loop which actually
1375 -- updates the levels in the resource denorm table.
1376 -- Whenever job mapping columns in pa_job_relationships updated,workflow will kickoff
1377 -- this api from the database trigger on table pa_job_relationships
1378 -- Pa_Job_Relationships Entity--
1379 -- IN Parameters
1380 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id -- INSERT
1381 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id,
1382 -- P_from_job_id_old,P_to_job_id_old -- UPDATE
1383 -- P_calling_mode,P_from_job_id_old,P_to_job_id_old,P_from_job_group_id,P_to_job_group_id -- DELETE
1384 PROCEDURE update_job_levels
1385 ( P_calling_mode IN VARCHAR2
1386 ,P_per_grades_grade_id IN per_grades.grade_id%type DEFAULT NULL
1387 ,P_per_grades_sequence_old IN NUMBER DEFAULT NULL
1388 ,P_per_grades_sequence_new IN NUMBER DEFAULT NULL
1389 ,P_per_valid_grade_job_id IN per_valid_grades.valid_grade_id%type DEFAULT NULL
1390 ,P_per_valid_grade_id_old IN per_grades.grade_id%type DEFAULT NULL
1391 ,P_per_valid_grade_id_new IN per_grades.grade_id%type DEFAULT NULL
1392 ,P_from_job_id_old IN pa_job_relationships.from_job_id%type DEFAULT NULL
1393 ,P_from_job_id_new IN pa_job_relationships.from_job_id%type DEFAULT NULL
1394 ,P_to_job_id_old IN pa_job_relationships.to_job_id%type DEFAULT NULL
1395 ,P_to_job_id_new IN pa_job_relationships.to_job_id%type DEFAULT NULL
1396 ,P_from_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
1397 ,P_to_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
1398 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1399 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1400 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1401 )IS
1402
1403 v_return_status VARCHAR2(2000);
1404 v_error_message_code VARCHAR2(2000);
1405 v_grade_id per_grades.grade_id%type;
1406 v_msg_data VARCHAR2(2000);
1407 v_msg_count NUMBER;
1408
1409 BEGIN
1410 -- Initialize the Error stack
1411 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_job_levels');
1412 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1413
1414 -- Code for if job mapping has changed
1415
1416 If (P_from_job_group_id is NOT NULL and P_to_job_group_id is NOT NULL) and
1417 (P_from_job_id_old is NOT NULL or P_from_job_id_new is NOT NULL or
1418 P_to_job_id_old is NOT NULL or P_to_job_id_new is NOT NULL ) then
1419 pa_job_relation_job_id
1420 (p_calling_mode => p_calling_mode
1421 ,P_from_job_id_old => P_from_job_id_old
1422 ,P_from_job_id_new => P_from_job_id_new
1423 ,P_to_job_id_old => P_to_job_id_old
1424 ,P_to_job_id_new => P_to_job_id_new
1425 ,P_from_job_group_id => P_from_job_group_id
1426 ,P_to_job_group_id => P_to_job_group_id
1427 ,x_return_status => x_return_status
1428 ,x_msg_data => x_msg_data
1429 ,x_msg_count => x_msg_count
1430 );
1431 End if;
1432
1433 -- reset the Error stack
1434 PA_DEBUG.Reset_Err_Stack;
1435
1436 EXCEPTION
1437
1438 WHEN NO_DATA_FOUND THEN
1439 NULL;
1440
1441 WHEN OTHERS THEN
1442 -- 4537865 : RESET x_msg_count and x_msg_data also
1443 x_msg_count := 1 ;
1444 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1445
1446 -- Set the exception Message and the stack
1447 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_job_levels'
1448 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1450 raise;
1451
1452 END update_job_levels;
1453
1454
1455 -- This Procedure updates the pa_resource_OU and set the resources
1456 -- end date active to sysdate when pa_all_organizations.inactive_date
1457 -- is updated.
1458 PROCEDURE Update_OU_resource(P_default_OU_old IN Pa_all_organizations.org_id%type
1459 ,P_default_OU_new IN Pa_all_organizations.org_id%type
1460 ,P_resource_id IN Pa_Resources_denorm.resource_id%type
1461 default NULL
1462 ,P_person_id IN Pa_Resources_denorm.person_id%type
1463 default NULL
1464 ,P_start_date IN Date default NULL
1465 ,P_end_date_old IN Date default NULL
1466 ,P_end_date_new IN Date default NULL
1467 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1468 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1469 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1470 )IS
1471
1472 v_return_status VARCHAR2(2000);
1473 v_error_message_code VARCHAR2(2000);
1474 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
1475 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
1476 v_msg_data VARCHAR2(2000);
1477 v_msg_count NUMBER;
1478
1479 CURSOR res_denorm_recs IS
1480 SELECT resource_effective_start_date,
1481 resource_effective_end_date
1482 FROM pa_resources_denorm
1483 WHERE person_id = p_person_id
1484 AND nvl(p_end_date_new, sysdate) >= resource_effective_start_date
1485 AND resource_effective_start_date >= p_start_date
1486 AND resource_effective_end_date <= p_end_date_old
1487 ;
1488 BEGIN
1489
1490 v_resource_rec_old.resource_org_id := p_default_OU_old;
1491 v_resource_rec_old.person_id := p_person_id;
1492 v_resource_rec_new.resource_org_id := p_default_OU_new;
1493 v_resource_rec_new.person_id := p_person_id;
1494
1495
1496
1497 --dbms_output.put_line('Calling Update_OU_resource');
1498 --dbms_output.put_line('End date for OU:' || p_default_OU_new || 'end date:' || P_end_date_new);
1499
1500 -- Initialize the Error stack
1501 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_OU_resource');
1502 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1503
1504 FOR rec IN res_denorm_recs LOOP
1505
1506 v_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
1507 v_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
1508
1509
1510 PA_RESOURCE_PVT.update_resource_denorm (
1511 p_resource_denorm_old_rec => v_resource_rec_old
1512 ,p_resource_denorm_new_rec => v_resource_rec_new
1513 ,x_return_status => x_return_status
1514 ,x_msg_data => x_msg_data
1515 ,x_msg_count => x_msg_count );
1516
1517 END LOOP;
1518
1519 --If new end date is passed for this assignment (from make_resource_inactive api)
1520 If P_end_date_new is NOT NULL then
1521 Update_EndDate(
1522 p_person_id => p_person_id,
1523 p_old_start_date => p_start_date,
1524 p_new_start_date => p_start_date,
1525 p_old_end_date => p_end_date_old,
1526 p_new_end_date => p_end_date_new,
1527 x_return_status => x_return_status,
1528 x_msg_data => x_msg_data,
1529 x_msg_count => x_msg_count);
1530
1531 End if;
1532
1533 -- reset the Error stack
1534 PA_DEBUG.Reset_Err_Stack;
1535
1536 EXCEPTION
1537
1538 WHEN NO_DATA_FOUND THEN
1539 NULL;
1540
1541 WHEN OTHERS THEN -- Set the exception Message and the stack
1542 -- 4537865 : RESET x_msg_count and x_msg_data also
1543 x_msg_count := 1 ;
1544 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1545 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_OU_resource'
1546 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1548 raise;
1549 END Update_OU_resource;
1550
1551
1552 -- This Procedure is called from workflow process to update/create resources in projects
1553 -- the workflow would be kicked of by the database trigger on table Hr_Organization_Information
1554 -- and Pa_All_Organization entities.
1555 -- 1.Whenever the default operating Unit which is
1556 -- stored in Hr_Organization_Information.Org_information1 changes / modified ,the
1557 -- trigger kicks off the workflow and calls this api to Update the Pa_Resource_OU
1558 -- entity.
1559 -- 2.Whenever the new record is inserted into Pa_All_Organizations with Pa_Org_Use_type
1560 -- is of type 'Expenditure' or the exisitng record in Pa_all_Organiations
1561 -- is updated with inactive_date then trigger fires and kicks of the workflow,calls this
1562 -- api to Update the Pa_Resource_OU.
1563 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit or rollback
1564 -- after every resource in the loop
1565 PROCEDURE Default_OU_Change
1566 ( P_calling_mode IN VARCHAR2
1567 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
1568 ,P_Default_OU_new IN Hr_Organization_Information.Org_Information1%type
1569 ,P_Default_OU_old IN Hr_Organization_Information.Org_Information1%type
1570 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1571 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1572 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1573 ) IS
1574 PRAGMA AUTONOMOUS_TRANSACTION;
1575
1576 v_return_status VARCHAR2(2000);
1577 v_error_message_code VARCHAR2(2000);
1578 v_assn_start_date Per_all_assignments_f.Effective_Start_Date%type;
1579 v_assn_end_date Per_all_assignments_f.Effective_End_Date%type;
1580 v_Person_id Per_all_assignments_f.person_id%type;
1581 v_Default_OU Hr_Organization_Information.Org_Information1%type;
1582 v_commit VARCHAR2(200) := FND_API.G_FALSE;
1583 -- set to false since the api is being called from trigger
1584 v_validate_only VARCHAR2(200) := FND_API.G_FALSE;
1585 v_internal VARCHAR2(1) := 'Y';
1586 v_individual VARCHAR2(1) := 'Y'; -- to process single resource in loop
1587 v_resource_type VARCHAR2(15):= 'EMPLOYEE';
1588 v_org_type VARCHAR2(15):= 'YES'; --'EXPENDITURES';
1589 v_msg_data VARCHAR2(2000);
1590 v_msg_count NUMBER;
1591 v_dummy NUMBER;
1592 L_API_VERSION CONSTANT NUMBER := 1.0;
1593 v_process_further BOOLEAN := FALSE;
1594 -- get all the resources who belongs to Expenditure type of organizaion and
1595 -- belongs to Expenditure Hierarchy ,Active_Assign, and of Primary assignment type
1596 -- is 'Y' and default OU inactive date is NUll
1597
1598 CURSOR get_all_resource(l_organization_id Hr_Organization_Information.Organization_id%type) is
1599
1600 SELECT distinct
1601 ind.person_id
1602 ,ind.assignment_start_date
1603 ,ind.assignment_end_date
1604 ,to_number(hoi.org_information1) default_OU
1605 FROM pa_r_project_resources_ind_v ind
1606 ,hr_organization_information hoi
1607 WHERE ind.organization_id =
1608 /* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
1609 AND ind.assignment_end_date >= sysdate
1610 AND hoi.organization_id = l_organization_id
1611 AND hoi.org_information_context = 'Exp Organization Defaults'
1612 AND ind.organization_id = l_organization_id -- 4898509
1613 ORDER BY ind.person_id,ind.assignment_start_date ;
1614
1615 BEGIN
1616
1617 -- Initialize the Error stack
1618 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Default_OU_Change');
1619 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1620 -- for each resource found for the default ou changes update
1621 -- pa resource ou entity with new default OU
1622
1623 -- check whether the new OU is a valid exp OU or not
1624 -- if not then donot process further
1625
1626 If P_Default_OU_new is NOT NULL then
1627
1628 v_default_OU := P_Default_OU_new;
1629 Else
1630 v_default_OU := -9999;
1631 -- For bug 5330402 Added call to make_resource_inactive
1632 -- This will take sysdate as inactive date
1633 -- Return after the call, as no further processing is required.
1634 make_resource_inactive
1635 (P_calling_mode => 'UPDATE'
1636 ,P_Organization_id => P_Organization_id
1637 ,P_Default_OU => P_Default_OU_old
1638 ,P_Default_OU_NEW => v_default_OU
1639 ,P_inactive_date => trunc(sysdate)
1640 ,x_return_status => x_return_status
1641 ,x_msg_data => x_msg_data
1642 ,x_msg_count => x_msg_count
1643 ) ;
1644 PA_DEBUG.Reset_Err_Stack;
1645 Return;
1646 End if;
1647
1648 pa_hr_update_api.check_exp_OU
1649 (p_org_id => v_default_OU
1650 ,x_return_status => v_return_status
1651 ,x_error_message_code => v_error_message_code
1652 );
1653
1654 If v_return_status <> FND_API.G_RET_STS_SUCCESS then
1655 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1656 x_msg_data := v_error_message_code;
1657 v_process_further := FALSE;
1658 PA_UTILS.add_message(p_app_short_name => 'PA',
1659 p_msg_name => v_error_message_code);
1660 x_msg_count := fnd_msg_pub.count_msg;
1661
1662 Elsif v_return_status = FND_API.G_RET_STS_SUCCESS then
1663
1664 v_process_further := TRUE;
1665
1666 End if;
1667
1668 -- Start Bug : 4656855
1669 IF (check_pjr_default_ou(P_Organization_id, v_default_OU) <> 'Y') THEN
1670 RETURN;
1671 END IF;
1672 -- End Bug : 4656855
1673
1674 If (v_process_further) then
1675 open get_all_resource(P_Organization_id);
1676 LOOP
1677 fetch get_all_resource into
1678 v_person_id
1679 ,v_assn_start_date
1680 ,v_assn_end_date
1681 ,v_default_OU ;
1682 Exit when get_all_resource%NOTFOUND;
1683
1684 -- call the check ou change api to update records in pa_resource_OU
1685 -- for each resource belongs to this updated OU in Hr_Organization_defaults
1686 If P_calling_mode = 'UPDATE' then
1687
1688 -- check for whether the default OU is changed if so call
1689 -- check OU change api to update the resource OU entity
1690 If (NVL(P_default_OU_old,-99) <> nvl(P_default_OU_new,-99)) then
1691 -- if OU is updated then call resource denorm api to
1692 -- reflect the changes in pa_resources_denorm entity
1693 If v_person_id is NOT NULL then
1694
1695
1696 Update_OU_resource
1697 (P_default_OU_old => p_default_OU_old
1698 ,p_default_OU_new => p_default_OU_new
1699 ,P_person_id => v_person_id
1700 ,P_start_date => v_assn_start_date
1701 ,P_end_date_old => v_assn_end_date
1702 ,x_return_status => x_return_status
1703 ,x_msg_data => x_msg_data
1704 ,x_msg_count => x_msg_count
1705 );
1706
1707 -- call forecast api to regenerate the forcast items
1708 -- for the person with the organization OU change
1709 -- update forecast data for unassigned and assigned time
1710 PA_FORECASTITEM_PVT.Create_Forecast_Item
1711 (
1712 p_person_id => v_person_id
1713 ,p_start_date => v_assn_start_date
1714 ,p_end_date => v_assn_end_date
1715 ,p_process_mode => 'GENERATE'
1716 ,x_return_status => x_return_status
1717 ,x_msg_count => x_msg_count
1718 ,x_msg_data => x_msg_data
1719 ) ;
1720
1721 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1722 COMMIT;
1723 else
1724 ROLLBACK;
1725 end if;
1726
1727 End if;
1728
1729
1730 Elsif P_default_OU_old is NULL and P_default_OU_new is NOT NULL then
1731 -- when new OU is assigned to existing organization it must pull all
1732 -- resources belongs to this OU so call create resource api
1733
1734 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
1735 P_API_VERSION => L_API_VERSION
1736 ,P_COMMIT => v_commit
1737 ,P_VALIDATE_ONLY => v_validate_only
1738 ,P_INTERNAL => v_internal
1739 ,P_PERSON_ID => v_person_id
1740 ,P_INDIVIDUAL => v_individual
1741 ,P_RESOURCE_TYPE => v_resource_type
1742 ,X_RETURN_STATUS => x_return_status
1743 ,X_RESOURCE_ID => v_dummy
1744 ,X_MSG_COUNT => x_msg_count
1745 ,X_MSG_DATA => x_msg_data
1746 );
1747
1748 -- call this procedure to update the forecast data for
1749 -- assigned time ONLY for this resource
1750 -- this is called only if create_resource is a success
1751 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1752 PA_FORECASTITEM_PVT.Create_Forecast_Item
1753 ( p_person_id => v_person_id
1754 ,p_start_date => null
1755 ,p_end_date => null
1756 ,p_process_mode => 'GENERATE_ASGMT'
1757 ,x_return_status => x_return_status
1758 ,x_msg_count => x_msg_count
1759 ,x_msg_data => x_msg_data
1760 ) ;
1761
1762 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1763 COMMIT;
1764 else
1765 ROLLBACK;
1766 end if;
1767 else
1768 ROLLBACK;
1769 end if;
1770
1771 End if;
1772
1773 /* cannot raise - because will be out from the loop and will not process other records
1774 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1775 RAISE FND_API.G_EXC_ERROR;
1776 END IF;
1777 */
1778
1779 Elsif P_calling_mode = 'INSERT' then
1780 -- the P_calling_mode is 'INSERT'
1781 -- this api is called to populate resources whenever a new record is added in
1782 -- in Hr_organizatioin_information entity
1783 -- or due to insert in pa_all_organizations entity
1784
1785 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
1786 P_API_VERSION => L_API_VERSION
1787 ,P_COMMIT => v_commit
1788 ,P_VALIDATE_ONLY => v_validate_only
1789 ,P_INTERNAL => v_internal
1790 ,P_PERSON_ID => v_person_id
1791 ,P_INDIVIDUAL => v_individual
1792 ,P_RESOURCE_TYPE => v_resource_type
1793 ,X_RETURN_STATUS => x_return_status
1794 ,X_RESOURCE_ID => v_dummy
1795 ,X_MSG_COUNT => x_msg_count
1796 ,X_MSG_DATA => x_msg_data
1797 );
1798
1799
1800 -- it is also necessary to call forecast item here,
1801 -- because this is also called from project_organization_change
1802 -- when p_calling_mode is insert
1803 -- A person can belong to Org1(belong to Exp Hier), then the org
1804 -- was changed to Org2 (not belong to Exp Hier)
1805 -- If Org2 is inserted and belong to Exp Hier, we need to fix
1806 -- the assigned time for this person when he/she was with Org1
1807 -- So, call this procedure to update the forecast data for
1808 -- assigned time ONLY for this resource
1809 -- this is called only if create_resource is a success
1810 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1811 PA_FORECASTITEM_PVT.Create_Forecast_Item
1812 ( p_person_id => v_person_id
1813 ,p_start_date => null
1814 ,p_end_date => null
1815 ,p_process_mode => 'GENERATE_ASGMT'
1816 ,x_return_status => x_return_status
1817 ,x_msg_count => x_msg_count
1818 ,x_msg_data => x_msg_data
1819 ) ;
1820
1821 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1822 COMMIT;
1823 else
1824 ROLLBACK;
1825 end if;
1826 else
1827 ROLLBACK;
1828 end if;
1829
1830
1831 /* cannot raise - because will be out from the loop and will not process other records
1832 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1833 RAISE FND_API.G_EXC_ERROR;
1834 END IF;
1835 */
1836
1837 End if;
1838 END LOOP;
1839 close get_all_resource;
1840
1841 --set the final return status to SUCCESS after loop
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843
1844 End if;
1845
1846 -- reset the Error stack
1847 PA_DEBUG.Reset_Err_Stack;
1848
1849 EXCEPTION
1850 WHEN FND_API.G_EXC_ERROR THEN
1851 x_return_status := FND_API.G_RET_STS_ERROR;
1852 WHEN OTHERS THEN
1853 -- 4537865 : RESET x_msg_count and x_msg_data also
1854 x_msg_count := 1 ;
1855 x_msg_data := SUBSTRB(SQLERRM ,1,240);
1856 -- Set the exception Message and the stack
1857 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Default_OU_Change'
1858 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1860 raise;
1861
1862 END Default_OU_Change;
1863
1864
1865 -- This Procedure is called from workflow process to update/create resources in projects
1866 -- The workflow would be kicked of by the database trigger on table Hr_Organization_Information
1867 -- It will update the job levels information if the Project Resource Job Group is changed
1868 -- Created by adabdull 2-JAN-2002
1869 PROCEDURE Proj_Res_Job_Group_Change
1870 ( p_calling_mode IN VARCHAR2
1871 ,p_organization_id IN Hr_Organization_Information.Organization_id%type
1872 ,p_proj_job_group_new IN Hr_Organization_Information.Org_Information1%type
1873 ,p_proj_job_group_old IN Hr_Organization_Information.Org_Information1%type
1874 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1875 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1876 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1877 ) IS
1878
1879 l_job_id per_jobs.job_id%type;
1880 l_job_level NUMBER;
1881 l_proj_job_group_new NUMBER;
1882 l_proj_job_group_old NUMBER;
1883 l_job_group_id NUMBER;
1884 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1885
1886 CURSOR get_new_job_ids IS
1887 SELECT job_id
1888 FROM per_jobs
1889 WHERE job_group_id = l_proj_job_group_new
1890 AND business_group_id = p_organization_id;
1891
1892 CURSOR get_old_job_ids IS
1893 SELECT job_id
1894 FROM per_jobs
1895 WHERE job_group_id = l_proj_job_group_old
1896 AND business_group_id = p_organization_id;
1897
1898 BEGIN
1899
1900 -- Initialize the Error stack
1901 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Proj_Res_Job_Group_Change');
1902 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1903
1904 l_proj_job_group_old := TO_NUMBER(p_proj_job_group_old);
1905 l_proj_job_group_new := TO_NUMBER(p_proj_job_group_new);
1906
1907 IF P_DEBUG_MODE = 'Y' THEN
1908 log_message('p_calling_mode = ' || p_calling_mode);
1909 log_message('old proj res job group value = ' || l_proj_job_group_old);
1910 log_message('new proj res job group value = ' || l_proj_job_group_new);
1911 END IF;
1912
1913 IF p_calling_mode = 'INSERT' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
1914
1915 -- When Insert, we update all resource denorm records with job_id that
1916 -- belongs to the new Project Resource Job Group Id
1917 OPEN get_new_job_ids;
1918 LOOP
1919
1920 FETCH get_new_job_ids INTO l_job_id;
1921 Exit when get_new_job_ids%NOTFOUND;
1922
1923 l_job_level := PA_JOB_UTILS.get_job_level(l_job_id);
1924 IF P_DEBUG_MODE = 'Y' THEN
1925 log_message('Job id to set level ' || l_job_level || ' = ' || l_job_id);
1926 END IF;
1927
1928 update_job_level_dff
1929 (P_job_id => l_job_id
1930 ,P_job_level_old => NULL
1931 ,P_job_level_new => l_job_level
1932 ,x_return_status => x_return_status
1933 ,x_msg_data => x_msg_data
1934 ,x_msg_count => x_msg_count
1935 );
1936 END LOOP;
1937 CLOSE get_new_job_ids;
1938
1939 ELSIF p_calling_mode = 'UPDATE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
1940
1941 -- When update we have to set the resource denorm records job level to NULL for job id
1942 -- that belongs to the old Project Resource Job Group Id
1943 OPEN get_old_job_ids;
1944 LOOP
1945
1946 FETCH get_old_job_ids INTO l_job_id;
1947 Exit when get_old_job_ids%NOTFOUND;
1948
1949 l_job_level := NULL;
1950
1951 IF P_DEBUG_MODE = 'Y' THEN
1952 log_message('Job id to set level Null = ' || l_job_id);
1953 END IF;
1954
1955 l_job_group_id := PA_JOB_UTILS.get_job_group_id(l_job_id);
1956
1957 perform_job_updates
1958 (P_job_id => l_job_id
1959 ,P_job_level_old => NULL
1960 ,P_job_level_new => l_job_level
1961 ,P_job_group_id => l_job_group_id
1962 ,x_return_status => x_return_status
1963 ,x_msg_data => x_msg_data
1964 ,x_msg_count => x_msg_count
1965 );
1966
1967 END LOOP;
1968 CLOSE get_old_job_ids;
1969
1970
1971 -- Also, when Update, we update all resource denorm records with job_id that
1972 -- belongs to the new Project Resource Job Group Id
1973
1974 OPEN get_new_job_ids;
1975 LOOP
1976
1977 FETCH get_new_job_ids INTO l_job_id;
1978 Exit when get_new_job_ids%NOTFOUND;
1979
1980 l_job_level := PA_JOB_UTILS.get_job_level(l_job_id);
1981 IF P_DEBUG_MODE = 'Y' THEN
1982 log_message('Job id to set level ' || l_job_level || ' = ' || l_job_id);
1983 END IF;
1984
1985 update_job_level_dff
1986 (P_job_id => l_job_id
1987 ,P_job_level_old => NULL
1988 ,P_job_level_new => l_job_level
1989 ,x_return_status => x_return_status
1990 ,x_msg_data => x_msg_data
1991 ,x_msg_count => x_msg_count
1992 );
1993
1994 END LOOP;
1995 CLOSE get_new_job_ids;
1996
1997
1998 ELSIF p_calling_mode = 'DELETE' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' THEN
1999
2000 -- When Delete, we have to set the resource denorm records job level to NULL for job id
2001 -- that belongs to the old Project Resource Job Group Id
2002
2003 OPEN get_old_job_ids;
2004 LOOP
2005
2006 FETCH get_old_job_ids INTO l_job_id;
2007 Exit when get_old_job_ids%NOTFOUND;
2008
2009 l_job_level := NULL;
2010 IF P_DEBUG_MODE = 'Y' THEN
2011 log_message('Job id to set level Null = ' || l_job_id);
2012 END IF;
2013
2014 update_job_level_dff
2015 (P_job_id => l_job_id
2016 ,P_job_level_old => NULL
2017 ,P_job_level_new => l_job_level
2018 ,x_return_status => x_return_status
2019 ,x_msg_data => x_msg_data
2020 ,x_msg_count => x_msg_count
2021 );
2022
2023 END LOOP;
2024 CLOSE get_old_job_ids;
2025
2026 END IF;
2027
2028
2029 -- reset the Error stack
2030 PA_DEBUG.Reset_Err_Stack;
2031
2032 EXCEPTION
2033 WHEN FND_API.G_EXC_ERROR THEN
2034 x_return_status := FND_API.G_RET_STS_ERROR;
2035 WHEN OTHERS THEN
2036 -- 4537865 : RESET x_msg_count and x_msg_data also
2037 x_msg_count := 1 ;
2038 x_msg_data := SUBSTRB(SQLERRM ,1,240);
2039 -- Set the exception Message and the stack
2040 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Proj_Res_Job_Group_Change'
2041 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2043 raise;
2044
2045 END Proj_Res_Job_Group_Change;
2046
2047 -- This API pulls all resources into PA from HR for a given organization
2048 -- Created by virangan 11-JUN-2001
2049 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit after every
2050 -- resource in the loop
2051 PROCEDURE pull_resources( p_organization_id IN pa_all_organizations.organization_id%type
2052 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2053 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2054 ,x_msg_count OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
2055 IS
2056 PRAGMA AUTONOMOUS_TRANSACTION;
2057 l_commit VARCHAR2(200) := FND_API.G_FALSE;
2058 -- set to false since the api is being called from trigger
2059 l_validate_only VARCHAR2(200) := FND_API.G_FALSE;
2060 l_internal VARCHAR2(1) := 'Y';
2061 l_individual VARCHAR2(1) := 'Y'; -- to process single resource in loop
2062 l_resource_type VARCHAR2(15):= 'EMPLOYEE';
2063 l_Person_id Per_all_assignments_f.person_id%type;
2064 l_return_status VARCHAR2(2000);
2065 l_msg_data VARCHAR2(2000);
2066 l_msg_count NUMBER;
2067 l_dummy NUMBER;
2068 L_API_VERSION CONSTANT NUMBER := 1.0;
2069
2070 CURSOR get_all_resource(l_organization_id Hr_Organization_Information.Organization_id%type) is
2071
2072 SELECT distinct ind.person_id
2073 FROM pa_r_project_resources_ind_v ind
2074 ,hr_organization_information hoi
2075 WHERE ind.organization_id =
2076 /* Changed for Bug 2499051- l_organization_id */ hoi.organization_id
2077 AND ind.assignment_end_date >= sysdate
2078 AND hoi.organization_id = l_organization_id
2079 AND hoi.org_information_context = 'Exp Organization Defaults'
2080 ORDER BY ind.person_id;
2081
2082 BEGIN
2083 -- Initialize the Error stack
2084 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.pull_resources');
2085 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2086
2087 open get_all_resource(p_organization_id);
2088 LOOP
2089 fetch get_all_resource into
2090 l_person_id;
2091 Exit when get_all_resource%NOTFOUND;
2092
2093 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
2094 P_API_VERSION => L_API_VERSION
2095 ,P_COMMIT => l_commit
2096 ,P_VALIDATE_ONLY => l_validate_only
2097 ,P_INTERNAL => l_internal
2098 ,P_PERSON_ID => l_person_id
2099 ,P_INDIVIDUAL => l_individual
2100 ,P_RESOURCE_TYPE => l_resource_type
2101 ,X_RETURN_STATUS => l_return_status
2102 ,X_RESOURCE_ID => l_dummy
2103 ,X_MSG_COUNT => l_msg_count
2104 ,X_MSG_DATA => l_msg_data );
2105
2106 -- call this procedure to update the forecast data for
2107 -- assigned time ONLY for this resource
2108 -- pass null to start date and end date
2109 -- this is called only if create_resource is a success
2110 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
2111 PA_FORECASTITEM_PVT.Create_Forecast_Item(
2112 p_person_id => l_person_id
2113 ,p_start_date => null
2114 ,p_end_date => null
2115 ,p_process_mode => 'GENERATE_ASGMT'
2116 ,x_return_status => l_return_status
2117 ,x_msg_count => l_msg_count
2118 ,x_msg_data => l_msg_data
2119 ) ;
2120
2121 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2122 COMMIT;
2123 else
2124 ROLLBACK;
2125 end if;
2126 else
2127 ROLLBACK;
2128 end if;
2129
2130 END LOOP;
2131 close get_all_resource;
2132
2133 --set the final return status to SUCCESS after loop
2134 x_return_status := FND_API.G_RET_STS_SUCCESS;
2135
2136 -- reset the Error stack
2137 PA_DEBUG.Reset_Err_Stack;
2138
2139 EXCEPTION
2140 WHEN OTHERS THEN
2141 -- 4537865 : RESET x_msg_count and x_msg_data also
2142 x_msg_count := 1 ;
2143 x_msg_data := SUBSTRB(SQLERRM ,1,240);
2144 -- Set the exception Message and the stack
2145 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.pull_resources'
2146 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2147
2148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2149 raise;
2150
2151 END pull_resources;
2152
2153
2154 -- This API will be called from workflow process to update/create resources in projects.
2155 -- The workflow would be kicked of by the database trigger on pa_all_organization entity
2156 -- whenever a inactive_date in pa_all_organization is updated this api get kicked of by the
2157 -- workflow.
2158 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because we'll commit after every
2159 -- resource in the loop
2160 PROCEDURE make_resource_inactive
2161 (P_calling_mode IN VARCHAR2
2162 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
2163 ,P_Default_OU IN pa_all_organizations.org_id%type
2164 ,P_inactive_date IN pa_all_organizations.inactive_date%type
2165 ,P_Default_OU_NEW IN pa_all_organizations.org_id%type DEFAULT NULL -- Added for bug 5330402
2166 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2167 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2168 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2169 ) IS
2170 PRAGMA AUTONOMOUS_TRANSACTION;
2171 v_return_status VARCHAR2(2000);
2172 v_error_message_code VARCHAR2(2000);
2173 v_assn_start_date Per_all_assignments_f.Effective_Start_Date%type;
2174 v_assn_end_date Per_all_assignments_f.Effective_End_Date%type;
2175 v_Person_id Per_all_assignments_f.person_id%type;
2176 v_resource_id Pa_Resource_txn_attributes.resource_id%type;
2177 v_Default_OU Hr_Organization_Information.Org_Information1%type;
2178 v_msg_data VARCHAR2(2000);
2179 v_msg_count NUMBER;
2180
2181 -- get all the resources who are employees and belongs to expenditure
2182 -- organizations and in the expenditure organization hierarchy
2183 -- and have a primary assignment (Active assignment) and have assigned
2184 -- to default OU
2185
2186 -- Bug 4347907 - change to base HR tables and remove nvl on dates
2187 --MOAC changes: bug 4363092: removed nvl used with org_id
2188 CURSOR get_all_inactive_resource
2189 IS
2190 SELECT
2191 distinct
2192 assn.person_id
2193 ,assn.effective_start_date
2194 ,assn.effective_end_date
2195 , res.resource_id
2196 , hrinf.org_information1
2197 FROM per_all_assignments_f assn
2198 , hr_organization_information hrinf
2199 /* , per_person_types pertypes Commented for bug#2781713 */
2200 , per_assignment_status_types pastype
2201 , pa_resource_txn_attributes res
2202 , pa_all_organizations allorgs
2203 , per_all_people_f pep
2204 WHERE
2205 assn.assignment_status_type_id = pastype.assignment_status_type_id
2206 AND assn.person_id = res.person_id
2207 AND assn.primary_flag = 'Y'
2208 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2209 AND assn.organization_id = allorgs.organization_id
2210 AND assn.organization_id = hrinf.organization_id
2211 AND assn.effective_start_date BETWEEN pep.effective_start_date
2212 AND pep.effective_end_date
2213 AND assn.effective_end_date >= trunc(sysdate)
2214 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2215 AND hrinf.org_information_context = 'Exp Organization Defaults'
2216 /* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
2217 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2218 /* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
2219 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2220 pep.current_npw_flag = 'Y') -- CWK Changes
2221 AND pep.person_id = assn.person_id
2222 AND allorgs.organization_id = P_organization_id
2223 AND allorgs.org_id = P_default_OU
2224 AND allorgs.pa_org_use_type = 'EXPENDITURES'
2225 AND allorgs.inactive_date is Not null
2226 AND (allorgs.organization_id,allorgs.org_id) = (
2227 SELECT exporg.organization_id, exporg.org_id
2228 FROM pa_all_organizations exporg
2229 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2230 AND exporg.inactive_date is Not null
2231 AND exporg.organization_id = allorgs.organization_id
2232 AND exporg.org_id = allorgs.org_id
2233 AND rownum = 1 );
2234
2235 /* -- Added for bug 5330402
2236 cursor get_all_inactive_resource_org is same as
2237 get_all_inactive_resource but commented out inactive date condition in where clause
2238 */
2239 CURSOR get_all_inactive_resource_org
2240 IS
2241 SELECT
2242 distinct
2243 assn.person_id
2244 ,assn.effective_start_date
2245 ,assn.effective_end_date
2246 , res.resource_id
2247 -- , hrinf.org_information1
2248 FROM per_all_assignments_f assn
2249 , hr_organization_information hrinf
2250 /* , per_person_types pertypes Commented for bug#2781713 */
2251 , per_assignment_status_types pastype
2252 , pa_resource_txn_attributes res
2253 , pa_all_organizations allorgs
2254 , per_all_people_f pep
2255 WHERE
2256 assn.assignment_status_type_id = pastype.assignment_status_type_id
2257 AND assn.person_id = res.person_id
2258 AND assn.primary_flag = 'Y'
2259 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2260 AND assn.organization_id = allorgs.organization_id
2261 AND assn.organization_id = hrinf.organization_id
2262 AND assn.effective_start_date BETWEEN pep.effective_start_date
2263 AND pep.effective_end_date
2264 AND assn.effective_end_date >= trunc(sysdate)
2265 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2266 AND hrinf.org_information_context = 'Exp Organization Defaults'
2267 /* AND pertypes.system_person_type = 'EMP' Commented for bug#2781713 */
2268 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2269 /* AND pep.person_type_id = pertypes.person_type_id Commented for bug#2781713 */
2270 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2271 pep.current_npw_flag = 'Y') -- CWK Changes
2272 AND pep.person_id = assn.person_id
2273 AND allorgs.organization_id = P_organization_id
2274 AND allorgs.org_id = P_default_OU -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
2275 AND allorgs.pa_org_use_type = 'EXPENDITURES'
2276 -- AND allorgs.inactive_date is Not null
2277 AND (allorgs.organization_id,allorgs.org_id) = ( -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
2278 SELECT exporg.organization_id, exporg.org_id
2279 FROM pa_all_organizations exporg
2280 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2281 -- AND exporg.inactive_date is Not null
2282 AND exporg.organization_id = allorgs.organization_id
2283 AND exporg.org_id = allorgs.org_id -- Removed the NVL as this is not required.Sunkalya.Bug#5330402
2284 AND rownum = 1 );
2285
2286 BEGIN
2287 -- Initialize the Error stack
2288 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.make_resource_inactive');
2289 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2290
2291 --dbms_output.put_line('Inside make resource inactive');
2292 --dbms_output.put_line('Default OU:' || p_default_ou);
2293 /* bug 5330402
2294 P_Default_OU_NEW will be null when called for orghierarchy update
2295 P_Default_OU_NEW will not be null when called for nulling out default OU
2296 */
2297 IF p_inactive_date is NOT NULL and p_calling_mode = 'UPDATE' then
2298 if P_Default_OU_NEW is null then
2299 open get_all_inactive_resource;
2300 else
2301 open get_all_inactive_resource_org;
2302 end if;
2303 LOOP
2304 if P_Default_OU_NEW is null then --bug 5330402
2305 fetch get_all_inactive_resource into
2306 v_person_id
2307 ,v_assn_start_date
2308 ,v_assn_end_date
2309 ,v_resource_id
2310 ,v_default_OU;
2311 Exit when get_all_inactive_resource%NOTFOUND;
2312 else
2313 fetch get_all_inactive_resource_org into
2314 v_person_id
2315 ,v_assn_start_date
2316 ,v_assn_end_date
2317 ,v_resource_id;
2318 Exit when get_all_inactive_resource_org%NOTFOUND;
2319 v_default_OU := P_Default_OU; --bug 5330402 setting OU to old value though currently it is null
2320 end if;
2321 If v_person_id is NOT NULL then
2322
2323 --dbms_output.put_line('Calling Update OU Resource');
2324 -- update the resource denorm with end date the resources
2325 Update_OU_resource (
2326 P_default_OU_old => v_default_OU
2327 ,p_default_OU_new => v_default_OU
2328 ,P_person_id => v_person_id
2329 ,P_start_date => v_assn_start_date
2330 ,P_end_date_old => v_assn_end_date
2331 ,P_end_date_new => p_inactive_date
2332 ,x_return_status => x_return_status
2333 ,x_msg_data => x_msg_data
2334 ,x_msg_count => x_msg_count );
2335
2336 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2337 COMMIT;
2338 else
2339 ROLLBACK;
2340 end if;
2341
2342 End if;
2343
2344 END LOOP;
2345 if P_Default_OU_NEW is null then --bug 5330402
2346 close get_all_inactive_resource;
2347 else
2348 close get_all_inactive_resource_org;
2349 end if;
2350
2351 --set the final return status to SUCCESS after loop
2352 x_return_status := FND_API.G_RET_STS_SUCCESS;
2353
2354 END IF;
2355
2356 -- reset the Error stack
2357 PA_DEBUG.Reset_Err_Stack;
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 -- 4537865 : RESET x_msg_count and x_msg_data also
2361 x_msg_count := 1 ;
2362 x_msg_data := SUBSTRB(SQLERRM ,1,240);
2363 -- Set the exception Message and the stack
2364 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.make_resource_inactive'
2365 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2367 raise;
2368
2369
2370
2371 END make_resource_inactive;
2372
2373
2374 -- This API makes calls to PA_FORECASTITEM_PVT.Create_Forecast_Item api
2375 -- which will generate or update the forecast items
2376 -- This API performs commit and rollback because it is only called by
2377 -- per_job_extra_billability, which is an autonomous transaction
2378 -- This will not affect the workflow process when doing any commit or rollback
2379 PROCEDURE call_forcast_api
2380 (P_table_name IN VARCHAR2
2381 ,P_person_id IN PER_ALL_ASSIGNMENTS_F.PERSON_ID%TYPE default NULL
2382 ,P_Job_id IN per_jobs.job_id%type default NULL
2383 ,P_billable_flag IN VARCHAR2 default NULL
2384 ,P_organization_id IN Hr_organization_information.organization_id%type default NULL
2385 ,p_start_date IN date default NULL
2386 ,P_end_date IN date default NULL
2387 ,P_resource_OU IN NUMBER default NULL
2388 ,P_resource_type IN VARCHAR2 default NULL
2389 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2390 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2391 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2392 ) IS
2393
2394 v_person_id PER_ALL_ASSIGNMENTS_F.PERSON_ID%TYPE;
2395 v_start_date Date;
2396 v_end_date Date;
2397
2398 --- This cursor picks all the persons who are Employees category and
2399 -- belongs to Expenditure Hierarchy
2400 -- and belongs to job id = paremeter
2401 Cursor person_jobs(l_job_id per_jobs.job_id%type)is
2402 SELECT distinct
2403 assn.person_id
2404 ,assn.effective_start_date
2405 ,assn.effective_end_date
2406 FROM per_all_assignments_f assn
2407 , hr_organization_information hrinf
2408 /* , per_person_types pertypes Commented for Bug#2781713 */
2409 , per_assignment_status_types pastype
2410 , per_all_people_f pep
2411 WHERE
2412 assn.assignment_status_type_id = pastype.assignment_status_type_id
2413 AND assn.primary_flag = 'Y'
2414 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2415 AND assn.job_id = l_job_id
2416 AND assn.organization_id = hrinf.organization_id
2417 AND assn.effective_start_date BETWEEN pep.effective_start_date
2418 AND pep.effective_end_date
2419 AND assn.effective_end_date >= trunc(sysdate)
2420 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2421 AND hrinf.org_information_context = 'Exp Organization Defaults'
2422 /* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
2423 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2424 /* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
2425 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2426 pep.current_npw_flag = 'Y') -- CWK Changes
2427 AND pep.person_id = assn.person_id
2428 AND assn.organization_id =
2429 (SELECT exporg.organization_id
2430 FROM pa_all_organizations exporg
2431 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2432 AND exporg.inactive_date is null
2433 AND exporg.organization_id = assn.organization_id
2434 AND rownum = 1 )
2435 ORDER BY 1,2;
2436
2437 -- This cursor picks all the persons who are Employees and belongs to the
2438 -- expenditure hierarchy and belongs to organzation where organization_id = l_org_id
2439 CURSOR person_orgs(l_org_id Hr_Organization_Information.Organization_id%type) is
2440 SELECT distinct
2441 assn.person_id
2442 ,assn.effective_start_date
2443 ,assn.effective_end_date
2444 FROM per_all_assignments_f assn
2445 , hr_organization_information hrinf
2446 /* , per_person_types pertypes Commented for Bug#2781713 */
2447 , per_assignment_status_types pastype
2448 , per_all_people_f pep
2449 WHERE
2450 assn.assignment_status_type_id = pastype.assignment_status_type_id
2451 AND assn.primary_flag = 'Y'
2452 AND assn.assignment_type in ('E', 'C') -- CWK Changes
2453 AND assn.organization_id = l_org_id
2454 AND assn.organization_id = hrinf.organization_id
2455 AND assn.effective_start_date BETWEEN pep.effective_start_date
2456 AND pep.effective_end_date
2457 AND assn.effective_end_date >= trunc(sysdate)
2458 AND pastype.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- CWK Changes
2459 AND hrinf.org_information_context = 'Exp Organization Defaults'
2460 /* AND pertypes.system_person_type = 'EMP' Commented for Bug#2781713 */
2461 AND (pep.employee_number is not null OR pep.npw_number IS NOT NULL) -- CWK Changes
2462 /* AND pep.person_type_id = pertypes.person_type_id Commented for Bug#2781713 */
2463 AND (pep.current_employee_flag = 'Y' /* added for bug#2781713 */ OR
2464 pep.current_npw_flag = 'Y') -- CWK Changes
2465 AND pep.person_id = assn.person_id
2466 AND assn.organization_id =
2467 (SELECT exporg.organization_id
2468 FROM pa_all_organizations exporg
2469 WHERE exporg.pa_org_use_type = 'EXPENDITURES'
2470 AND exporg.inactive_date is null
2471 AND exporg.organization_id = assn.organization_id
2472 AND rownum = 1 )
2473 ORDER BY 1,2;
2474
2475
2476 BEGIN
2477 -- Initialize the Error stack
2478 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_forcast_api');
2479 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2480
2481 If (P_table_name = 'PER_JOB_EXTRA_INFO' or
2482 P_table_name = 'PER_VALID_GRADES' or
2483 P_table_name = 'PER_GRADES' or
2484 P_table_name = 'PA_ALL_ORGANIZATIONS') and
2485 (P_job_id is NOT NULL) then
2486
2487 -- get all the persons belongs to this job Id and
2488 -- Call Forecast Item regeneration API to update the forecast
2489 -- data for unassigned and assigned time
2490
2491 OPEN person_jobs(P_job_id);
2492 LOOP
2493 fetch person_jobs into v_person_id,v_start_date,v_end_date;
2494 exit when person_jobs%notfound;
2495
2496 PA_FORECASTITEM_PVT.Create_Forecast_Item
2497 (
2498 p_person_id => v_person_id
2499 ,p_start_date => v_start_date
2500 ,p_end_date => v_end_date
2501 ,p_process_mode => 'GENERATE'
2502 ,x_return_status => x_return_status
2503 ,x_msg_count => x_msg_count
2504 ,x_msg_data => x_msg_data
2505 ) ;
2506
2507 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2508 COMMIT;
2509 else
2510 ROLLBACK;
2511 end if;
2512
2513 /* cannot raise - because will be out from the loop and will not process other records
2514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2515 RAISE FND_API.G_EXC_ERROR;
2516 END IF;
2517 */
2518
2519 END LOOP;
2520 CLOSE person_jobs;
2521
2522 Elsif (P_table_name = 'HR_ORGANIZATION_INFORMATION')
2523 and (P_organization_id is NOT NULL) then
2524
2525 -- get all the persons who belongs to this organization and
2526 -- Call Forecast Item regeneration API to update the forecast
2527 -- data for unassigned and assigned time
2528
2529 OPEN person_orgs(P_organization_id);
2530 LOOP
2531 fetch person_orgs into v_person_id,v_start_date,v_end_date;
2532 exit when person_orgs%notfound;
2533
2534 PA_FORECASTITEM_PVT.Create_Forecast_Item
2535 (
2536 p_person_id => v_person_id
2537 ,p_start_date => v_start_date
2538 ,p_end_date => v_end_date
2539 ,p_process_mode => 'GENERATE'
2540 ,x_return_status => x_return_status
2541 ,x_msg_count => x_msg_count
2542 ,x_msg_data => x_msg_data
2543 ) ;
2544
2545 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2546 COMMIT;
2547 else
2548 ROLLBACK;
2549 end if;
2550
2551 /* cannot raise - because will be out from the loop and will not process other records
2552 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2553 RAISE FND_API.G_EXC_ERROR;
2554 END IF;
2555 */
2556 END LOOP;
2557 CLOSE person_orgs;
2558
2559 End if;
2560
2561 --set the final return status to SUCCESS after loop
2562 x_return_status := FND_API.G_RET_STS_SUCCESS;
2563
2564 -- reset the Error stack
2565 PA_DEBUG.Reset_Err_Stack;
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 WHEN OTHERS THEN
2570 -- 4537865 : RESET x_msg_count and x_msg_data also
2571 x_msg_count := 1 ;
2572 x_msg_data := SUBSTRB(SQLERRM ,1,240);
2573 -- Set the exception Message and the stack
2574 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_forcast_api'
2575 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577 raise;
2578
2579
2580
2581 END call_forcast_api;
2582
2583
2584 -- This Procedure makes calls to PA_RESOURCE_PVT.update_resource_denorm api
2585 -- which actually updates the pa_resources_denorm entity
2586 PROCEDURE call_billable_resoruce_denorm
2587 (P_job_id_old per_jobs.job_id%type
2588 ,P_job_id_new per_jobs.job_id%type
2589 ,P_billable_flag_old pa_resources_denorm.billable_flag%type
2590 ,P_billable_flag_new pa_resources_denorm.billable_flag%type
2591 ,P_utilize_flag_old pa_resources_denorm.utilization_flag%type
2592 ,P_utilize_flag_new pa_resources_denorm.utilization_flag%type
2593 ,p_schedulable_flag_old pa_resources_denorm.schedulable_flag%type
2594 ,p_schedulable_flag_new pa_resources_denorm.schedulable_flag%type
2595 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2596 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2597 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2598 ) IS
2599 v_return_status VARCHAR2(2000);
2600 v_error_message_code VARCHAR2(2000);
2601 v_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
2602 v_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
2603 v_job_id_old PER_JOBS.JOB_ID%type;
2604 v_job_id_new PER_JOBS.JOB_ID%type;
2605 v_billable_flag_old pa_resources_denorm.billable_flag%type;
2606 v_billable_flag_new pa_resources_denorm.billable_flag%type;
2607 v_utilize_flag_old pa_resources_denorm.utilization_flag%type;
2608 v_utilize_flag_new pa_resources_denorm.utilization_flag%type;
2609 v_schedulable_flag_old pa_resources_denorm.schedulable_flag%type;
2610 v_schedulable_flag_new pa_resources_denorm.schedulable_flag%type;
2611 v_msg_data VARCHAR2(2000);
2612 v_msg_count NUMBER;
2613
2614 BEGIN
2615
2616 -- Initialize the Error stack
2617 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.call_billable_resoruce_denorm');
2618 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2619 v_job_id_new := P_job_id_new;
2620 v_job_id_old := P_job_id_old;
2621
2622 If P_billable_flag_new is NOT NULL then
2623 v_billable_flag_new := P_billable_flag_new;
2624 Else
2625 v_billable_flag_new := 'N';
2626 End if;
2627
2628 If P_billable_flag_old is NOT NULL then
2629 v_billable_flag_old := P_billable_flag_old;
2630 Else
2631 v_billable_flag_old := 'N';
2632 End if;
2633
2634 If P_utilize_flag_new is NOT NULL then
2635 v_utilize_flag_new := P_utilize_flag_new;
2636 Else
2637 v_utilize_flag_new := 'N';
2638 End if;
2639
2640 If P_utilize_flag_old is NOT NULL then
2641 v_utilize_flag_old := P_utilize_flag_old;
2642 Else
2643 v_utilize_flag_old := 'N';
2644 End if;
2645
2646 If P_schedulable_flag_new is NOT NULL then
2647 v_schedulable_flag_new := P_schedulable_flag_new;
2648 Else
2649 v_schedulable_flag_new := 'N';
2650 End if;
2651 If P_schedulable_flag_old is NOT NULL then
2652 v_schedulable_flag_old := P_schedulable_flag_old;
2653 Else
2654 v_schedulable_flag_old := 'N';
2655 End if;
2656
2657 if v_job_id_new is NOT NULL then
2658 v_resource_rec_old.job_id := v_job_id_old;
2659 v_resource_rec_old.billable_flag := v_billable_flag_old;
2660 v_resource_rec_new.job_id := v_job_id_new;
2661 v_resource_rec_new.billable_flag := v_billable_flag_new;
2662 v_resource_rec_old.utilization_flag := v_utilize_flag_old;
2663 v_resource_rec_new.utilization_flag := v_utilize_flag_new;
2664 v_resource_rec_old.schedulable_flag := v_schedulable_flag_old;
2665 v_resource_rec_new.schedulable_flag := v_schedulable_flag_new;
2666
2667 -- Call PRM API update resource denorm which actually updates the
2668 -- pa_resource_denorm entity
2669
2670 PA_RESOURCE_PVT.update_resource_denorm
2671 ( p_resource_denorm_old_rec => v_resource_rec_old
2672 ,p_resource_denorm_new_rec => v_resource_rec_new
2673 ,x_return_status => x_return_status
2674 ,x_msg_data => x_msg_data
2675 ,x_msg_count => x_msg_count
2676 );
2677
2678
2679
2680 End if;
2681 -- reset the Error stack
2682 PA_DEBUG.Reset_Err_Stack;
2683
2684 EXCEPTION
2685
2686 WHEN OTHERS THEN
2687 -- 4537865 : RESET x_msg_count and x_msg_data also
2688 x_msg_count := 1 ;
2689 x_msg_data := SUBSTRB(SQLERRM ,1,240);
2690 -- Set the exception Message and the stack
2691 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.call_billable_resoruce_denorm'
2692 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2694 raise;
2695
2696 END call_billable_resoruce_denorm;
2697
2698 -- This Procedure is kicked off by the workflow when jei_infomration2
2699 -- which stores the jobs billability information. whenever the row is updated
2700 -- or inserted into per_job_extra_info entity which stores the job information
2701 -- and types a database triggers fires and kicks of the workflow
2702 -- This procedure makes calls to forecast regenerate apis and create resource
2703 -- denorm apis to to update the new billability for the resource
2704 -- Make this procedure a PRAGMA AUTONOMOUS_TRANSACTION because at the end
2705 -- this procedure call call_forcast_api which do commit after every resource
2706 -- in a loop to update the forecast items
2707 PROCEDURE per_job_extra_billability
2708 (p_calling_mode IN VARCHAR2
2709 ,P_job_id IN per_jobs.job_id%type
2710 ,P_billable_flag_new IN per_job_extra_info.jei_information2%type
2711 ,P_billable_flag_old IN per_job_extra_info.jei_information2%type
2712 ,P_utilize_flag_old IN per_job_extra_info.jei_information3%type
2713 ,P_utilize_flag_new IN per_job_extra_info.jei_information3%type
2714 ,P_job_level_new IN per_job_extra_info.jei_information4%type
2715 ,P_job_level_old IN per_job_extra_info.jei_information4%type
2716 ,p_schedulable_flag_new IN per_job_extra_info.jei_information6%type
2717 ,p_schedulable_flag_old IN per_job_extra_info.jei_information6%type
2718 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2719 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2720 ,x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
2721 PRAGMA AUTONOMOUS_TRANSACTION;
2722
2723 v_return_status VARCHAR2(2000);
2724 v_error_message_code VARCHAR2(2000);
2725 v_job_id PER_JOBS.JOB_ID%type;
2726 v_msg_data VARCHAR2(2000);
2727 v_msg_count NUMBER;
2728 l_pull_res_flag VARCHAR2(1) := 'N';
2729 l_end_date_res_flag VARCHAR2(1) := 'N';
2730 l_prv_person_id NUMBER;
2731 l_resource_id NUMBER;
2732 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2733
2734 /*
2735 CURSOR all_job_res_recs IS
2736 SELECT person_id, min(resource_effective_start_date) resource_effective_start_date,
2737 max(resource_effective_end_date) resource_effective_end_date
2738 FROM pa_resources_denorm
2739 WHERE job_id = p_job_id
2740 GROUP BY person_id; */
2741
2742 CURSOR all_job_res_recs IS -- Modified cursor for Bug 7336158
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 AND resource_effective_end_date = (Select max(resource_effective_end_date)
2748 from pa_resources_denorm rd2
2749 where rd2.job_id = p_job_id
2750 AND (rd2.resource_effective_end_date >= sysdate OR rd2.resource_effective_end_date is null))
2751 GROUP BY person_id;
2752
2753 CURSOR distinct_job_res_recs IS
2754 SELECT DISTINCT res.person_id person_id
2755 FROM pa_r_project_resources_v res
2756 WHERE res.job_id = p_job_id;
2757
2758 BEGIN
2759 -- Initialize the Error stack
2760 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.per_job_extra_billability');
2761 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2762
2763 IF P_DEBUG_MODE = 'Y' THEN
2764 log_message('beginning of per_job_extra_billability, P_calling_mode:'|| P_calling_mode || ', job_id: '||P_job_id);
2765 END IF;
2766
2767 -------------------------------------------------------------------------
2768 -- If P_calling_mode='INSERT', P_utilize_flag_new will always be 'Y' and
2769 -- we need to pull those people who has the job.
2770 -- Because if P_utilize_flag_new=N, the trigger won't launch WF which
2771 -- eventually calls this API.
2772 --------------------------------------------------------------------------
2773 If P_calling_mode = 'INSERT' THEN
2774 l_pull_res_flag := 'Y';
2775
2776 -------------------------------------------------------------------------
2777 -- If P_calling_mode='UPDATE'
2778 -------------------------------------------------------------------------
2779 ELSIF P_calling_mode = 'UPDATE' THEN
2780 -- If p_utilize_flag_old='N' AND p_utilize_flag_new='Y', we need to
2781 -- pull the people who have the job
2782 IF p_utilize_flag_old='N' AND p_utilize_flag_new='Y' THEN
2783 l_pull_res_flag := 'Y';
2784
2785 ELSE
2786 -- If p_utilize_flag_old='Y' and p_utilize_flag_new ='N', we need
2787 -- to end date the resources from pa_resources_denorm
2788 IF p_utilize_flag_old ='Y' AND p_utilize_flag_new ='N' THEN
2789 l_end_date_res_flag := 'Y';
2790 END IF;
2791
2792 IF P_DEBUG_MODE = 'Y' THEN
2793 log_message('p_utilize_flag_new: ' ||p_utilize_flag_new ||
2794 ',l_end_date_res_flag: '||l_end_date_res_flag);
2795 END IF;
2796
2797 -- Update pa_resources_denorm
2798 call_billable_resoruce_denorm
2799 (P_job_id_old => P_job_id
2800 ,P_job_id_new => P_job_id
2801 ,P_billable_flag_old => P_billable_flag_old
2802 ,P_billable_flag_new => P_billable_flag_new
2803 ,P_utilize_flag_old => P_utilize_flag_old
2804 ,P_utilize_flag_new => P_utilize_flag_new
2805 ,p_schedulable_flag_old => p_schedulable_flag_old
2806 ,p_schedulable_flag_new => p_schedulable_flag_new
2807 ,x_return_status => x_return_status
2808 ,x_msg_data => x_msg_data
2809 ,x_msg_count => x_msg_count );
2810 IF P_DEBUG_MODE = 'Y' THEN
2811 log_message('After call_billable_resoruce_denorm');
2812 END IF;
2813
2814 -- Update job level
2815 update_job_level_dff
2816 (P_job_id => P_job_id
2817 ,P_job_level_old => TO_NUMBER(P_job_level_old)
2818 ,P_job_level_new => TO_NUMBER(P_job_level_new)
2819 ,x_return_status => x_return_status
2820 ,x_msg_data => x_msg_data
2821 ,x_msg_count => x_msg_count );
2822 IF P_DEBUG_MODE = 'Y' THEN
2823 log_message('After update_job_level_dff');
2824 END IF;
2825 END IF;
2826
2827 -------------------------------------------------------------------------
2828 -- If P_calling_mode='DELETE' and p_utilize_flag_old='Y', end date those
2829 -- resources who has the job. If p_utilize_flag_old='N', there won't be
2830 -- current active data. But we still need to update the other flags on
2831 -- the past date records.
2832 -------------------------------------------------------------------------
2833 ELSIF P_calling_mode = 'DELETE' THEN
2834 IF p_utilize_flag_old ='Y' THEN
2835 l_end_date_res_flag := 'Y';
2836 END IF;
2837 IF P_DEBUG_MODE = 'Y' THEN
2838 log_message('P_calling_mode=DELETE, p_utilize_flag_old:'||p_utilize_flag_old
2839 ||', l_end_date_res_flag:'||l_end_date_res_flag);
2840 END IF;
2841
2842 call_billable_resoruce_denorm
2843 (P_job_id_old => P_job_id
2844 ,P_job_id_new => P_job_id
2845 ,P_billable_flag_old => P_billable_flag_old
2846 ,P_billable_flag_new => NULL
2847 ,P_utilize_flag_old => P_utilize_flag_old
2848 ,P_utilize_flag_new => NULL
2849 ,p_schedulable_flag_old => p_schedulable_flag_old
2850 ,p_schedulable_flag_new => NULL
2851 ,x_return_status => x_return_status
2852 ,x_msg_data => x_msg_data
2853 ,x_msg_count => x_msg_count );
2854
2855 update_job_level_dff
2856 (P_job_id => P_job_id
2857 ,P_job_level_old => TO_NUMBER(P_job_level_old)
2858 ,P_job_level_new => NULL
2859 ,x_return_status => x_return_status
2860 ,x_msg_data => x_msg_data
2861 ,x_msg_count => x_msg_count );
2862 IF P_DEBUG_MODE = 'Y' THEN
2863 log_message('After calling update_job_level_dff');
2864 END IF;
2865 END IF;
2866
2867 ----------------------------------------------------------------
2868 -- pull the people
2869 ----------------------------------------------------------------
2870 IF (l_pull_res_flag = 'Y') THEN
2871 IF P_DEBUG_MODE = 'Y' THEN
2872 log_message('it will pull the people');
2873 END IF;
2874 FOR rec IN distinct_job_res_recs LOOP
2875 BEGIN
2876 IF P_DEBUG_MODE = 'Y' THEN
2877 log_message('person Id = ' || rec.person_id);
2878 END IF;
2879
2880 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
2881 p_api_version => 1.0
2882 ,p_commit => FND_API.G_FALSE
2883 ,p_validate_only => FND_API.G_FALSE
2884 ,p_internal => 'Y'
2885 ,p_person_id => rec.person_id
2886 ,p_individual => 'Y'
2887 ,p_resource_type => 'EMPLOYEE'
2888 ,x_resource_id => l_resource_id
2889 ,x_return_status => x_return_status
2890 ,x_msg_count => x_msg_count
2891 ,x_msg_data => x_msg_data );
2892
2893 IF P_DEBUG_MODE = 'Y' THEN
2894 log_message('Return status from CREATE_RESOURCE = ' || x_return_status);
2895 END IF;
2896 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2897 COMMIT;
2898 ELSE
2899 ROLLBACK;
2900 END IF;
2901
2902 EXCEPTION
2903 -- whenever an expected error is raised from this API
2904 -- will need to continue for the next record
2905 WHEN OTHERS THEN
2906 FND_MSG_PUB.get (
2907 p_encoded => FND_API.G_FALSE,
2908 p_msg_index => 1,
2909 p_data => x_msg_data,
2910 p_msg_index_out => x_msg_count );
2911 IF P_DEBUG_MODE = 'Y' THEN
2912 log_message('error msg from CREATE_RESOURCE: ' || substr(x_msg_data,1,200));
2913 END IF;
2914 END;
2915 END LOOP;
2916
2917 -----------------------------------------------------------------------------
2918 -- End date all those resources who has the job from the pa_resources_denorm
2919 -----------------------------------------------------------------------------
2920 ELSIF (l_end_date_res_flag = 'Y') THEN
2921 IF P_DEBUG_MODE = 'Y' THEN
2922 log_message('it will end date the resources');
2923 END IF;
2924 FOR rec IN all_job_res_recs LOOP
2925 BEGIN
2926 IF P_DEBUG_MODE = 'Y' THEN
2927 log_message('person Id = ' || rec.person_id ||' ,start_date: ' ||
2928 rec.resource_effective_start_date || ', end_date: '||
2929 rec.resource_effective_end_date );
2930 END IF;
2931
2932 Update_EndDate
2933 (p_person_id => rec.person_id,
2934 p_old_start_date => rec.resource_effective_start_date,
2935 p_new_start_date => rec.resource_effective_start_date,
2936 p_old_end_date => rec.resource_effective_end_date,
2937 p_new_end_date => sysdate,
2938 x_return_status => x_return_status,
2939 x_msg_count => x_msg_count,
2940 x_msg_data => x_msg_data);
2941
2942 IF P_DEBUG_MODE = 'Y' THEN
2943 log_message('Return status from Update_EndDate = ' || x_return_status);
2944 END IF;
2945 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2946 COMMIT;
2947 ELSE
2948 ROLLBACK;
2949 END IF;
2950 EXCEPTION
2951 -- whenever an expected error is raised from this API
2952 -- will need to continue for the next record
2953 WHEN OTHERS THEN
2954 IF P_DEBUG_MODE = 'Y' THEN
2955 log_message('error occured');
2956 END IF;
2957 ROLLBACK;
2958 FND_MSG_PUB.get (
2959 p_encoded => FND_API.G_FALSE,
2960 p_msg_index => 1,
2961 p_data => x_msg_data,
2962 p_msg_index_out => x_msg_count );
2963 IF P_DEBUG_MODE = 'Y' THEN
2964 log_message('error msg from Update_EndDate: ' || substr(x_msg_data,1,200));
2965 END IF;
2966 END;
2967 END LOOP;
2968
2969 END IF;
2970
2971
2972 -- call forecast api to regenerate the forcast items due to change in
2973 -- billability flag
2974 call_forcast_api
2975 (P_table_name => 'PER_JOB_EXTRA_INFO'
2976 ,P_Job_id => P_job_id
2977 ,x_return_status => x_return_status
2978 ,x_msg_data => x_msg_data
2979 ,x_msg_count => x_msg_count );
2980 IF P_DEBUG_MODE = 'Y' THEN
2981 log_message('After calling call_forcast_api, x_return_status: '||x_return_status);
2982 END IF;
2983
2984 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2985 IF P_DEBUG_MODE = 'Y' THEN
2986 log_message('It will raise exception');
2987 END IF;
2988 RAISE FND_API.G_EXC_ERROR;
2989 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2990 IF P_DEBUG_MODE = 'Y' THEN
2991 log_message('will commit');
2992 END IF;
2993 COMMIT;
2994 END IF;
2995
2996 -- reset the Error stack
2997 PA_DEBUG.Reset_Err_Stack;
2998 IF P_DEBUG_MODE = 'Y' THEN
2999 log_message('after Reset_Err_Stack');
3000 END IF;
3001
3002 EXCEPTION
3003 WHEN FND_API.G_EXC_ERROR THEN
3004 ROLLBACK;
3005 x_return_status := FND_API.G_RET_STS_ERROR;
3006 WHEN NO_DATA_FOUND THEN
3007 NULL;
3008 WHEN OTHERS THEN
3009 IF P_DEBUG_MODE = 'Y' THEN
3010 log_message('Error occured');
3011 END IF;
3012 -- 4537865 : RESET x_msg_count and x_msg_data also
3013 x_msg_count := 1 ;
3014 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3015 -- Set the exception Message and the stack
3016 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.per_job_extra_billability'
3017 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3019 RAISE;
3020 END per_job_extra_billability;
3021
3022
3023 -- PROCEDURE
3024 -- withdraw_cand_nominations
3025 -- PURPOSE
3026 -- to withdraw all PJR candidate nominations for this
3027 -- person_id when the person is terminated in HR
3028 -- or the assignment organization no longer belongs to
3029 -- expenditure hierarchy
3030 --
3031 PROCEDURE withdraw_cand_nominations
3032 ( p_person_id IN NUMBER,
3033 p_effective_date IN DATE,
3034 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3035 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3036 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
3037
3038 l_resource_id NUMBER;
3039 l_status_code VARCHAR2(30);
3040 l_rec_ver_num NUMBER;
3041 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3042
3043 CURSOR get_nominations(l_resource_id IN NUMBER) IS
3044 select cand.candidate_id, cand.record_version_number
3045 from pa_candidates cand,
3046 pa_project_assignments asgmt,
3047 pa_project_statuses ps
3048 where cand.resource_id = l_resource_id
3049 and cand.assignment_id = asgmt.assignment_id
3050 and asgmt.assignment_type = 'OPEN_ASSIGNMENT'
3051 and asgmt.status_code = ps.project_status_code (+)
3052 and (ps.project_system_status_code = 'OPEN_ASGMT'
3053 OR ps.project_system_status_code is null)
3054 and asgmt.start_date > trunc(p_effective_date)
3055 and cand.status_code not in
3056 (select ps2.project_status_code
3057 from pa_project_statuses ps2
3058 where ps2.status_type='CANDIDATE'
3059 and ps2.project_system_status_code IN
3060 ('CANDIDATE_DECLINED','CANDIDATE_WITHDRAWN'));
3061
3062
3063 BEGIN
3064
3065 -- Initialize the Error stack
3066 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.withdraw_cand_nominations');
3067 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3068
3069 l_resource_id := PA_RESOURCE_UTILS.get_resource_id(p_person_id);
3070
3071 IF (l_resource_id <> -999) THEN
3072
3073 l_status_code := FND_PROFILE.value('PA_INV_RES_CAND_STATUS');
3074 IF P_DEBUG_MODE = 'Y' THEN
3075 log_message('Candidate status code = ' || l_status_code);
3076 END IF;
3077
3078 -- if the profile option is not set, then we use the status code
3079 -- '111' that we ship to customers for 'CANDIDATE_WITHDRAWN'
3080 -- system status code
3081 IF l_status_code is null THEN
3082 l_status_code := '111';
3083 END IF;
3084
3085 FOR rec IN get_nominations(l_resource_id) LOOP
3086
3087 BEGIN
3088 SAVEPOINT save_candidate;
3089 IF P_DEBUG_MODE = 'Y' THEN
3090 log_message('Candidate Id = ' || rec.candidate_id);
3091 END IF;
3092
3093 PA_CANDIDATE_PUB.Update_Candidate
3094 (p_candidate_id => rec.candidate_id,
3095 p_status_code => l_status_code,
3096 p_ranking => null,
3097 p_change_reason_code => null,
3098 p_record_version_number => rec.record_version_number,
3099 p_validate_status => FND_API.G_FALSE,
3100 p_init_msg_list => FND_API.G_FALSE,
3101 x_record_version_number => l_rec_ver_num,
3102 x_return_status => x_return_status,
3103 x_msg_count => x_msg_count,
3104 x_msg_data => x_msg_data);
3105
3106 IF P_DEBUG_MODE = 'Y' THEN
3107 log_message('Return status from Update Candidate = ' || x_return_status);
3108 END IF;
3109
3110 EXCEPTION
3111 -- whenever an expected error is raised from this API
3112 -- will need to continue for the next record
3113 WHEN FND_API.G_EXC_ERROR THEN
3114 ROLLBACK TO save_candidate;
3115 FND_MSG_PUB.get (
3116 p_encoded => FND_API.G_FALSE,
3117 p_msg_index => 1,
3118 p_data => x_msg_data,
3119 p_msg_index_out => x_msg_count );
3120 IF P_DEBUG_MODE = 'Y' THEN
3121 log_message('Withdraw_Cand_Nominations EXPECTED ERROR: Candidate_Id =' || rec.candidate_id);
3122 log_message('Log: ' || substr(x_msg_data,1,200));
3123 END IF;
3124 END;
3125
3126 END LOOP;
3127
3128 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3129
3130 ELSE
3131 -- person does not exist in pa_resource_txn_attributes
3132 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3133 END IF;
3134
3135 -- reset the Error stack
3136 PA_DEBUG.Reset_Err_Stack;
3137
3138 EXCEPTION
3139
3140 WHEN OTHERS THEN
3141 -- 4537865 : RESET x_msg_count and x_msg_data also
3142 x_msg_count := 1 ;
3143 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3144 -- Set the exception Message and the stack
3145 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.withdraw_cand_nominations'
3146 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3148 raise;
3149 END withdraw_cand_nominations;
3150
3151
3152 /* Procedure Update_EndDate calls Update_Resource_Denorm to update the
3153 end date in pa_resources_denorm and update FI data for the resource.
3154 This procedure now also handles automatic candidates withdrawal.
3155 It is called whenever a resource is terminated in HR, whenever
3156 the change in assignment organization which does not belong to Exp Hier,
3157 or whenever an organization is taken out from the Exp Hier. In these
3158 cases, the resource is considered no longer active in PJR.
3159 */
3160 PROCEDURE Update_EndDate(
3161 p_person_id IN per_all_people_f.person_id%TYPE,
3162 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3163 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
3164 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
3165 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3166 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3167 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3168 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3169
3170 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3171 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3172
3173 l_return_status VARCHAR2(1);
3174
3175 l_invol_term VARCHAR2(1); --bug 5683340
3176
3177 l_count NUMBER ; -- bug 7147575
3178
3179 CURSOR res_denorm_recs IS
3180 SELECT resource_effective_start_date,
3181 resource_effective_end_date
3182 FROM pa_resources_denorm
3183 WHERE person_id = p_person_id
3184 -- AND p_new_end_date >= resource_effective_start_date
3185 AND resource_effective_start_date >= p_old_start_date
3186 AND resource_effective_end_date <= p_old_end_date
3187 ;
3188
3189 BEGIN
3190
3191 --dbms_output.put_line('Inside Update End Date');
3192 --dbms_output.put_line('Person Id:' || p_person_id);
3193 --dbms_output.put_line('New End Date:' || p_new_end_date);
3194
3195 -- Initialize the Error stack
3196 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_EndDate');
3197 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3198
3199 l_count := 0; -- bug 7147575 : None records in pa_resources_denorm so far.
3200
3201 FOR rec IN res_denorm_recs LOOP
3202
3203 l_count := l_count + 1 ; -- bug 7147575 :this means that there was atleast one record in pa_resources_denorm
3204
3205 --dbms_output.put_line('resource_effective_start_date:' || rec.resource_effective_start_date);
3206 --dbms_output.put_line('resource_effective_end_date:' || rec.resource_effective_end_date);
3207
3208
3209 -- Bug 4668272 - added case of p_new_end_date >
3210 -- rec.resource_effective_end_date - this case occurs when a
3211 -- reverse termination happens in HR.
3212 -- IF p_new_end_date BETWEEN rec.resource_effective_start_date AND rec.resource_effective_end_date THEN
3213 IF (p_new_end_date BETWEEN rec.resource_effective_start_date AND
3214 rec.resource_effective_end_date) OR
3215 (p_new_end_date > rec.resource_effective_end_date) THEN
3216
3217 --End date this record
3218 --Set the values for the Resources Denorm Record Type
3219
3220 l_resource_rec_old.person_id := p_person_id;
3221 l_resource_rec_new.person_id := p_person_id;
3222
3223 l_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
3224 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3225
3226 l_resource_rec_old.resource_effective_end_date := rec.resource_effective_end_date;
3227 l_resource_rec_new.resource_effective_end_date := p_new_end_date;
3228
3229 --Call Resource Denorm API
3230 PA_RESOURCE_PVT.update_resource_denorm(
3231 p_resource_denorm_old_rec => l_resource_rec_old
3232 ,p_resource_denorm_new_rec => l_resource_rec_new
3233 ,x_return_status => l_return_status
3234 ,x_msg_data => x_msg_data
3235 ,x_msg_count => x_msg_count);
3236
3237 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3238 x_return_status := FND_API.G_RET_STS_ERROR ;
3239 END IF;
3240
3241 ELSIF p_new_end_date < rec.resource_effective_start_date THEN
3242
3243 --Delete this record
3244 pa_resource_pvt.delete_resource_denorm(
3245 p_person_id => p_person_id
3246 ,p_res_effective_start_date => rec.resource_effective_start_date
3247 ,x_return_status => l_return_status
3248 ,x_msg_data => x_msg_data
3249 ,x_msg_count => x_msg_count);
3250
3251 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3252 x_return_status := FND_API.G_RET_STS_ERROR ;
3253 END IF;
3254
3255 END IF;
3256
3257 END LOOP; --end FOR
3258
3259 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.
3260
3261 /*Call added for bug 5683340*/
3262 pa_resource_utils.init_fte_sync_wf( p_person_id => p_person_id,
3263 x_invol_term => l_invol_term,
3264 x_return_status => l_return_status,
3265 x_msg_data => x_msg_data,
3266 x_msg_count => x_msg_count);
3267
3268 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3269 x_return_status := FND_API.G_RET_STS_ERROR ;
3270 END IF ;
3271
3272 /*if block added for bug 5683340*/
3273 IF ((l_invol_term = 'N') AND (l_return_status = FND_API.G_RET_STS_SUCCESS)) THEN
3274
3275 --Call Forecast Item regeneration API
3276 --to fix forecast data after resource was end_dated
3277 --start_date passed is the date when it changes
3278 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3279 p_person_id => p_person_id,
3280 --p_start_date => p_new_end_date+1, p_old_end_date Bug 6120875
3281 p_start_date => Least(p_new_end_date+1, p_old_end_date+1),
3282 p_end_date => null,
3283 p_process_mode => 'GENERATE',
3284 x_return_status => l_return_status,
3285 x_msg_count => x_msg_count,
3286 x_msg_data => x_msg_data) ;
3287
3288 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3289 x_return_status := FND_API.G_RET_STS_ERROR ;
3290 END IF;
3291
3292
3293 -- Call this procedure to withdraw any active candidacy of this
3294 -- person in PJR whenever the person is end dated (due to termination
3295 -- or the organization no longer belong to Exp Hier)
3296 withdraw_cand_nominations
3297 ( p_person_id => p_person_id,
3298 p_effective_date => p_new_end_date,
3299 x_return_status => l_return_status,
3300 x_msg_count => x_msg_count,
3301 x_msg_data => x_msg_data);
3302
3303 -- reset the Error stack
3304
3305 END IF ; --((l_invol_term = 'N') AND (l_return_status = FND_API.G_RET_STS_SUCCESS)) bug 5683340
3306
3307 END IF ; -- bug 7147575 : IF (l_count > 0 )
3308
3309 PA_DEBUG.Reset_Err_Stack;
3310
3311 EXCEPTION
3312
3313 WHEN NO_DATA_FOUND THEN
3314 x_return_status := FND_API.G_RET_STS_ERROR ;
3315
3316 WHEN OTHERS THEN
3317 -- 4537865 : RESET x_msg_count and x_msg_data also
3318 x_msg_count := 1 ;
3319 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3320 -- Set the exception Message and the stack
3321 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_EndDate'
3322 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3324 raise;
3325 END Update_EndDate;
3326
3327 /* 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.
3328 */
3329 PROCEDURE Update_Org(
3330 p_person_id IN per_all_people_f.person_id%TYPE,
3331 p_old_org_id IN per_all_assignments_f.organization_id%TYPE,
3332 p_new_org_id IN per_all_assignments_f.organization_id%TYPE,
3333 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3334 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
3335 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
3336 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3337 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3338 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3339 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3340
3341 l_default_ou_old pa_resources_denorm.resource_org_id%TYPE;
3342 l_default_ou_new pa_resources_denorm.resource_org_id%TYPE;
3343
3344 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3345 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3346
3347 l_return_status VARCHAR2(1);
3348 l_org_type VARCHAR2(20);
3349 l_resource_id NUMBER(15);
3350
3351
3352 -- Cursor to get all denormalized resource records
3353 -- for this HR assignment
3354 CURSOR res_denorm_recs IS
3355 SELECT resource_effective_start_date,
3356 resource_effective_end_date
3357 FROM pa_resources_denorm
3358 WHERE person_id = p_person_id
3359 AND resource_effective_start_date >= p_new_start_date
3360 AND resource_effective_end_date <= p_new_end_date
3361 ;
3362
3363 -- CURSOR to check whether it is a Multi or Single Org Implementation
3364 --MOAC Changes : Bug 4363092: Get the value of current org from PA_MOAC_UTILS.GET_CURRENT_ORG_ID
3365 /* CURSOR check_org_type IS
3366 select decode(substr(USERENV('CLIENT_INFO'),1,1),
3367 ' ', NULL,
3368 substr(USERENV('CLIENT_INFO'),1,10)) org from dual; */
3369
3370 BEGIN
3371
3372 --dbms_output.put_line('Inside Update Org');
3373
3374 -- Initialize the Error stack
3375 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Org');
3376
3377 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3378
3379 /* Check if new organization belongs to an expenditure
3380 organization, if yes then get default ou for old and new organization
3381 and update pa_resource_ou.organization.*/
3382
3383 IF (Belongs_ExpOrg(p_new_org_id) = 'Y') THEN
3384
3385 -- If the old org Id does not belong to exp hier,
3386 -- we have to fix data in resource denorm (we end dated that
3387 -- record previously when the org changes from Exp Hier
3388 -- to Non Exp Hier). So calling Create_Resource
3389 IF(Belongs_ExpOrg(p_old_org_id) = 'N') THEN
3390
3391 pa_r_project_resources_pub.create_resource (
3392 p_api_version => 1.0
3393 ,p_init_msg_list => NULL
3394 ,p_commit => FND_API.G_FALSE
3395 ,p_validate_only => NULL
3396 ,p_max_msg_count => NULL
3397 ,p_internal => 'Y'
3398 ,p_person_id => p_person_id
3399 ,p_individual => 'Y'
3400 ,p_resource_type => NULL
3401 ,x_return_status => l_return_status
3402 ,x_msg_count => x_msg_count
3403 ,x_msg_data => x_msg_data
3404 ,x_resource_id => l_resource_id);
3405
3406
3407 -- call this procedure to update the forecast data for
3408 -- assigned time ONLY for this resource
3409 -- pass null to start date and end date
3410 -- this is called only if create_resource is a success
3411 if (l_return_status = 'S' and l_resource_id is not null) then
3412 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3413 p_person_id => p_person_id
3414 ,p_start_date => null
3415 ,p_end_date => null
3416 ,p_process_mode => 'GENERATE_ASGMT'
3417 ,x_return_status => l_return_status
3418 ,x_msg_count => x_msg_count
3419 ,x_msg_data => x_msg_data
3420 ) ;
3421 end if;
3422
3423 ELSE
3424
3425 l_default_ou_old := Get_DefaultOU(p_old_org_id);
3426 l_default_ou_new := Get_DefaultOU(p_new_org_id);
3427
3428 IF (l_default_ou_new = -999) THEN
3429 /* Bug 4363092: Commenting this check as R12 will be multi org only */
3430 -- OPEN check_org_type;
3431 -- FETCH check_org_type into l_org_type;
3432 -- CLOSE check_org_type;
3433
3434 -- case for Multi-Org: no OU - so set return status
3435 -- to error and return
3436 -- IF l_org_type IS NOT NULL THEN
3437 PA_UTILS.Add_Message(
3438 p_app_short_name => 'PA'
3439 ,p_msg_name => 'PA_RS_DEF_OU_NULL');
3440
3441 x_msg_data := 'PA_RS_DEF_OU_NULL';
3442 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3443 RETURN;
3444 -- ELSE
3445 -- l_default_ou_new := NULL;
3446 -- END IF;
3447
3448 END IF;
3449
3450 --Set the values for the Resources Denorm Record Type
3451 l_resource_rec_old.person_id := p_person_id;
3452 l_resource_rec_new.person_id := p_person_id;
3453
3454 l_resource_rec_old.resource_org_id := l_default_ou_old;
3455 l_resource_rec_new.resource_org_id := l_default_ou_new;
3456
3457 l_resource_rec_old.resource_organization_id := p_old_org_id;
3458 l_resource_rec_new.resource_organization_id := p_new_org_id;
3459
3460 FOR rec IN res_denorm_recs LOOP
3461
3462 l_resource_rec_old.resource_effective_start_date := rec.resource_effective_start_date;
3463 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3464
3465 l_resource_rec_old.resource_effective_end_date := rec.resource_effective_end_date;
3466 l_resource_rec_new.resource_effective_end_date := rec.resource_effective_end_date;
3467
3468 --Call Resource Denorm API
3469 PA_RESOURCE_PVT.update_resource_denorm(
3470 p_resource_denorm_old_rec => l_resource_rec_old
3471 ,p_resource_denorm_new_rec => l_resource_rec_new
3472 ,x_return_status => l_return_status
3473 ,x_msg_data => x_msg_data
3474 ,x_msg_count => x_msg_count);
3475
3476 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3477 x_return_status := FND_API.G_RET_STS_ERROR ;
3478 END IF;
3479
3480 END LOOP; --end FOR
3481
3482
3483 --Call Forecast Item regeneration API
3484 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3485 p_person_id => p_person_id,
3486 p_start_date => p_new_start_date,
3487 p_end_date => p_new_end_date,
3488 p_process_mode => 'GENERATE',
3489 x_return_status => l_return_status,
3490 x_msg_count => x_msg_count,
3491 x_msg_data => x_msg_data) ;
3492
3493 END IF;
3494
3495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3496 x_return_status := FND_API.G_RET_STS_ERROR ;
3497 END IF;
3498
3499 ELSE
3500 /* In case the organization does not belong to expenditure
3501 hierarchy the record in pa_resource_denorm must be end dated*/
3502
3503 Update_EndDate(
3504 p_person_id => p_person_id,
3505 p_old_start_date => p_old_start_date,
3506 p_new_start_date => p_new_start_date,
3507 p_old_end_date => p_old_end_date,
3508 p_new_end_date => sysdate,
3509 x_return_status => l_return_status,
3510 x_msg_data => x_msg_data,
3511 x_msg_count => x_msg_count);
3512
3513 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3514 x_return_status := FND_API.G_RET_STS_ERROR ;
3515 END IF;
3516
3517 END IF;
3518
3519
3520 -- reset the Error stack
3521 PA_DEBUG.Reset_Err_Stack;
3522
3523 EXCEPTION
3524 WHEN NO_DATA_FOUND THEN
3525 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
3526
3527 WHEN OTHERS THEN
3528 -- 4537865 : RESET x_msg_count and x_msg_data also
3529 x_msg_count := 1 ;
3530 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3531 -- Set the exception Message and the stack
3532 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Org'
3533 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3535 raise;
3536 END Update_Org;
3537
3538 -- Start Bug : 4656855
3539 /* Function check_pjr_default_ou checks if org_id in pa_all_organizations = the PJR default operating unit */
3540 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)
3541 RETURN VARCHAR2 IS
3542 -- This function returns 'Y' if org_id in pa_all_organizations = the PJR default operating unit
3543 -- otherwise , it returns 'N'
3544
3545 CURSOR c_check_pjr_default_ou IS
3546 SELECT 'Y' FROM dual WHERE exists
3547 (SELECT 'Y' FROM hr_organization_information
3548 WHERE organization_id = P_Organization_id
3549 and org_information_context = 'Exp Organization Defaults'
3550 and org_information1 = P_Default_OU_new);
3551
3552 l_dummy VARCHAR2(1);
3553 BEGIN
3554 OPEN c_check_pjr_default_ou;
3555 FETCH c_check_pjr_default_ou INTO l_dummy;
3556
3557 IF c_check_pjr_default_ou%NOTFOUND THEN
3558 CLOSE c_check_pjr_default_ou; -- Bug 5336837
3559 RETURN 'N';
3560 ELSE
3561 CLOSE c_check_pjr_default_ou; -- Bug 5336837
3562 RETURN 'Y';
3563 END IF;
3564
3565 -- CLOSE c_check_pjr_default_ou; -- Bug 5336837
3566 EXCEPTION
3567 WHEN OTHERS THEN
3568 -- Bug 5336837
3569 IF c_check_pjr_default_ou%ISOPEN THEN
3570 CLOSE c_check_pjr_default_ou ;
3571 END IF;
3572
3573 return 'N';
3574 END check_pjr_default_ou;
3575 -- End Bug : 4656855
3576
3577 /* Function Belongs_ExpOrg checks if the given organization belongs to
3578 an expenditure/event organization
3579 */
3580 FUNCTION Belongs_ExpOrg(p_org_id IN per_all_assignments_f.organization_id%TYPE)
3581 RETURN VARCHAR2 IS
3582 -- This function returns 'Y' if a given org is a Exp organization ,
3583 -- otherwise , it returns 'N'
3584
3585 CURSOR c_exp_org IS
3586 SELECT 'x'
3587 FROM dual
3588 WHERE exists
3589 (select organization_id
3590 FROM pa_all_organizations
3591 WHERE organization_id = p_org_id
3592 AND inactive_date is null
3593 AND pa_org_use_type = 'EXPENDITURES');
3594
3595 l_dummy VARCHAR2(1);
3596 BEGIN
3597 OPEN c_exp_org;
3598 FETCH c_exp_org INTO l_dummy;
3599
3600 IF c_exp_org%NOTFOUND THEN
3601 CLOSE c_exp_org; -- Bug 5336837
3602 RETURN 'N';
3603 ELSE
3604 CLOSE c_exp_org; -- Bug 5336837
3605 RETURN 'Y';
3606 END IF;
3607
3608 -- CLOSE c_exp_org; -- Bug 5336837
3609 EXCEPTION
3610 WHEN OTHERS THEN
3611 -- Bug 5336837
3612 IF c_exp_org%ISOPEN THEN
3613 CLOSE c_exp_org ;
3614 END IF;
3615
3616 return 'N';
3617 END Belongs_ExpOrg;
3618
3619 /* Function Get_DefaultOU returns the default OU for the given organization.
3620 */
3621 FUNCTION Get_DefaultOU(p_org_id IN per_all_assignments_f.organization_id%TYPE)
3622 RETURN NUMBER IS
3623 l_default_ou number;
3624 BEGIN
3625 select to_number(org_information1)
3626 into l_default_ou
3627 from hr_organization_information
3628 where organization_id = p_org_id
3629 and org_information_context = 'Exp Organization Defaults';
3630
3631 if l_default_ou is null then
3632 l_default_ou := -999;
3633 end if;
3634
3635 return l_default_ou;
3636 EXCEPTION
3637 WHEN OTHERS THEN
3638 return -999;
3639 END Get_DefaultOU;
3640
3641 /*
3642 Procedure Update_Job retrieves the job level for the job and calls
3643 Update_Resource_Denorm and Create_Forecast_Item to update the denorm
3644 table and regenerate forecast items for the resource respectively.
3645 */
3646 PROCEDURE Update_Job(
3647 p_person_id IN per_all_people_f.person_id%TYPE,
3648 p_old_job IN per_all_assignments_f.job_id%TYPE,
3649 p_new_job IN per_all_assignments_f.job_id%TYPE,
3650 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3651 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3652 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3653 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3654 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3655
3656 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3657 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3658 l_return_status VARCHAR2(1);
3659 l_resource_id NUMBER;
3660 l_resource_start_date DATE;
3661 l_resource_end_date DATE;
3662 l_old_job_group_id per_job_groups.job_group_id%type;
3663 l_new_job_group_id per_job_groups.job_group_id%type;
3664 l_old_job_level NUMBER;
3665 l_new_job_level NUMBER;
3666 l_old_job_billable pa_resources_denorm.billable_flag%type;
3667 l_new_job_billable pa_resources_denorm.billable_flag%type;
3668 l_old_job_utilizable pa_resources_denorm.utilization_flag%type;
3669 l_new_job_utilizable pa_resources_denorm.utilization_flag%type;
3670 l_old_job_schedulable pa_resources_denorm.schedulable_flag%type;
3671 l_new_job_schedulable pa_resources_denorm.schedulable_flag%type;
3672 P_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
3673
3674 -- Cursor to get all denormalized resource records
3675 -- for this HR assignment
3676 CURSOR res_denorm_recs IS
3677 SELECT resource_effective_start_date,
3678 resource_effective_end_date
3679 FROM pa_resources_denorm
3680 WHERE person_id = p_person_id
3681 AND resource_effective_start_date >= p_new_start_date
3682 AND resource_effective_end_date <= p_new_end_date;
3683
3684 CURSOR min_max_res_dates IS
3685 SELECT min(resource_effective_start_date) resource_effective_start_date,
3686 max(resource_effective_end_date) resource_effective_end_date
3687 FROM pa_resources_denorm
3688 WHERE job_id = p_old_job
3689 AND person_id = p_person_id
3690 GROUP BY person_id;
3691
3692 BEGIN
3693 -- Initialize the Error stack
3694 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Job');
3695 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3696 IF P_DEBUG_MODE = 'Y' THEN
3697 log_message('beginning of Update_Job');
3698 END IF;
3699
3700 --Get the job group id for the old and new job
3701 l_old_job_group_id := get_job_group_id(p_old_job);
3702 l_new_job_group_id := get_job_group_id(p_new_job);
3703
3704 --Get the old/new job level and job flags
3705 l_old_job_level := get_job_level(P_job_id => p_old_job,
3706 P_job_group_id => l_old_job_group_id);
3707 l_new_job_level := get_job_level(P_job_id => p_new_job,
3708 P_job_group_id => l_new_job_group_id);
3709 l_old_job_billable := check_job_billability(P_job_id => p_old_job,
3710 P_person_id => NULL,
3711 p_date => NULL );
3712 l_new_job_billable := check_job_billability(P_job_id => p_new_job,
3713 P_person_id => NULL,
3714 p_date => NULL );
3715 l_old_job_utilizable := check_job_utilization(P_job_id => p_old_job,
3716 P_person_id => NULL,
3717 p_date => NULL );
3718 l_new_job_utilizable := check_job_utilization(P_job_id => p_new_job,
3719 P_person_id => NULL,
3720 p_date => NULL );
3721 l_old_job_schedulable := check_job_schedulable(P_job_id => p_old_job);
3722 l_new_job_schedulable := check_job_schedulable(P_job_id => p_new_job);
3723
3724 ------------------------------------------------------------------
3725 -- If old job's util_flag=N and new job's util_flag=Y, we need to
3726 -- pull the person.
3727 ------------------------------------------------------------------
3728 IF l_old_job_utilizable='N' AND l_new_job_utilizable='Y' THEN
3729 IF P_DEBUG_MODE = 'Y' THEN
3730 log_message('pull the person_Id = ' || p_person_id);
3731 END IF;
3732
3733 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE (
3734 p_api_version => 1.0
3735 ,p_commit => FND_API.G_FALSE
3736 ,p_validate_only => FND_API.G_FALSE
3737 ,p_internal => 'Y'
3738 ,p_person_id => p_person_id
3739 ,p_individual => 'Y'
3740 ,p_resource_type => 'EMPLOYEE'
3741 ,x_resource_id => l_resource_id
3742 ,x_return_status => x_return_status
3743 ,x_msg_count => x_msg_count
3744 ,x_msg_data => x_msg_data );
3745
3746 ------------------------------------------------------------------
3747 -- If old util_flag=Y and new_util_flag=N, end date the resource
3748 ------------------------------------------------------------------
3749 ELSIF l_old_job_utilizable='Y' AND l_new_job_utilizable='N' THEN
3750 OPEN min_max_res_dates;
3751 FETCH min_max_res_dates INTO l_resource_start_date, l_resource_end_date;
3752 CLOSE min_max_res_dates;
3753
3754 IF P_DEBUG_MODE = 'Y' THEN
3755 log_message('person Id = ' || p_person_id ||' ,start_date: ' ||
3756 l_resource_start_date || ', end_date: '||l_resource_end_date );
3757 END IF;
3758
3759 Update_EndDate
3760 (p_person_id => p_person_id,
3761 p_old_start_date => l_resource_start_date,
3762 p_new_start_date => l_resource_start_date,
3763 p_old_end_date => l_resource_end_date,
3764 p_new_end_date => sysdate,
3765 x_return_status => x_return_status,
3766 x_msg_count => x_msg_count,
3767 x_msg_data => x_msg_data);
3768
3769 IF P_DEBUG_MODE = 'Y' THEN
3770 log_message('Return status from Update_EndDate = ' || x_return_status);
3771 END IF;
3772
3773 ------------------------------------------------------------------
3774 -- If old util_flag=new util_flag, just update job flags
3775 ------------------------------------------------------------------
3776 ELSE
3777 --Set the values for the Resources Denorm Record Type
3778 l_resource_rec_old.person_id := p_person_id;
3779 l_resource_rec_new.person_id := p_person_id;
3780 l_resource_rec_old.job_id := p_old_job;
3781 l_resource_rec_new.job_id := p_new_job;
3782 l_resource_rec_old.resource_job_level := l_old_job_level;
3783 l_resource_rec_new.resource_job_level := l_new_job_level;
3784 l_resource_rec_old.billable_flag := l_old_job_billable;
3785 l_resource_rec_new.billable_flag := l_new_job_billable;
3786 l_resource_rec_old.utilization_flag := l_old_job_utilizable;
3787 l_resource_rec_new.utilization_flag := l_new_job_utilizable;
3788 l_resource_rec_old.schedulable_flag := l_old_job_schedulable;
3789 l_resource_rec_new.schedulable_flag := l_new_job_schedulable;
3790
3791 -- loop through all record of the person in pa_resources_denorm and update
3792 -- the job flags
3793 FOR rec IN res_denorm_recs LOOP
3794 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3795 l_resource_rec_new.resource_effective_end_date := rec.resource_effective_end_date;
3796 IF P_DEBUG_MODE = 'Y' THEN
3797 log_message('start_date:'||rec.resource_effective_start_date||', end_Date:'||
3798 rec.resource_effective_end_date);
3799 END IF;
3800
3801 PA_RESOURCE_PVT.update_resource_denorm(
3802 p_resource_denorm_old_rec => l_resource_rec_old
3803 ,p_resource_denorm_new_rec => l_resource_rec_new
3804 ,x_return_status => l_return_status
3805 ,x_msg_data => x_msg_data
3806 ,x_msg_count => x_msg_count);
3807
3808 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3809 x_return_status := FND_API.G_RET_STS_ERROR ;
3810 END IF;
3811 END LOOP;
3812
3813 END IF;
3814
3815 IF P_DEBUG_MODE = 'Y' THEN
3816 log_message('before calling Create_Forecast_Item');
3817 END IF;
3818 --Call Forecast Item regeneration API
3819 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3820 p_person_id => p_person_id,
3821 p_start_date => p_new_start_date,
3822 p_end_date => p_new_end_date,
3823 p_process_mode => 'GENERATE',
3824 x_return_status => l_return_status,
3825 x_msg_count => x_msg_count,
3826 x_msg_data => x_msg_data) ;
3827
3828 IF P_DEBUG_MODE = 'Y' THEN
3829 log_message('after calling Create_Forecast_Item, l_return_status:'||l_return_status);
3830 END IF;
3831 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3832 x_return_status := FND_API.G_RET_STS_ERROR ;
3833 END IF;
3834
3835 -- reset the Error stack
3836 PA_DEBUG.Reset_Err_Stack;
3837
3838 EXCEPTION
3839 WHEN OTHERS THEN
3840 IF P_DEBUG_MODE = 'Y' THEN
3841 log_message('exception was thrown');
3842 END IF;
3843 -- 4537865 : RESET x_msg_count and x_msg_data also
3844 x_msg_count := 1 ;
3845 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3846 -- Set the exception Message and the stack
3847 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Job'
3848 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3850 RAISE;
3851 END Update_Job;
3852
3853 /* Procedure Update_Supervisor calls Update_Resource_Denorm to update
3854 the resource's supervisor in the denorm table.
3855 */
3856 PROCEDURE Update_Supervisor(
3857 p_person_id IN per_all_people_f.person_id%TYPE,
3858 p_old_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
3859 p_new_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
3860 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3861 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3862 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3863 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3864 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3865
3866 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3867 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
3868
3869 l_return_status VARCHAR2(1);
3870 l_manager_name pa_resources_denorm.manager_name%TYPE;
3871
3872 CURSOR res_denorm_recs IS
3873 SELECT resource_effective_start_date,
3874 resource_effective_end_date
3875 FROM pa_resources_denorm
3876 WHERE person_id = p_person_id
3877 AND p_new_end_date >= resource_effective_start_date
3878 AND resource_effective_start_date >= p_new_start_date
3879 AND resource_effective_end_date <= p_new_end_date
3880 ;
3881
3882 CURSOR manager_name IS
3883 SELECT DISTINCT resource_name
3884 FROM pa_resources_denorm
3885 WHERE person_id = p_new_supervisor
3886 ;
3887
3888 BEGIN
3889
3890 -- Initialize the Error stack
3891 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Supervisor');
3892 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3893
3894 --Set the values for the Resources Denorm Record Type
3895 l_resource_rec_old.person_id := p_person_id;
3896 l_resource_rec_new.person_id := p_person_id;
3897
3898 l_resource_rec_old.manager_id := p_old_supervisor;
3899 l_resource_rec_new.manager_id := p_new_supervisor;
3900
3901 OPEN manager_name;
3902 FETCH manager_name INTO l_manager_name;
3903 l_resource_rec_new.manager_name := l_manager_name;
3904 CLOSE manager_name;
3905
3906 FOR rec IN res_denorm_recs LOOP
3907
3908 l_resource_rec_new.resource_effective_start_date := rec.resource_effective_start_date;
3909
3910 --Call Resource Denorm API
3911 PA_RESOURCE_PVT.update_resource_denorm(
3912 p_resource_denorm_old_rec => l_resource_rec_old
3913 ,p_resource_denorm_new_rec => l_resource_rec_new
3914 ,x_return_status => l_return_status
3915 ,x_msg_data => x_msg_data
3916 ,x_msg_count => x_msg_count);
3917
3918 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3919 x_return_status := FND_API.G_RET_STS_ERROR ;
3920 END IF;
3921
3922 END LOOP;--end FOR
3923
3924 -- reset the Error stack
3925 PA_DEBUG.Reset_Err_Stack;
3926
3927 EXCEPTION
3928 WHEN OTHERS THEN
3929 -- 4537865 : RESET x_msg_count and x_msg_data also
3930 x_msg_count := 1 ;
3931 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3932 -- Set the exception Message and the stack
3933 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Supervisor'
3934 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3936 raise;
3937 END Update_Supervisor;
3938
3939 /* Procedure Update_PrimaryFlag calls Update_EndDate to end date the
3940 record for which the assignment record's primary flag has changed form
3941 yes to no and then calls Create_Forecast_Item to regenerate
3942 the forecast items for this resource.
3943 */
3944 PROCEDURE Update_PrimaryFlag(
3945 p_person_id IN per_all_people_f.person_id%TYPE,
3946 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
3947 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
3948 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
3949 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
3950 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3951 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3952 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
3953
3954 l_return_status VARCHAR2(1);
3955
3956 BEGIN
3957 -- Initialize the Error stack
3958 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_PrimaryFlag');
3959 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3960
3961 --Call Update_EndDate to end date the resource_ou record.
3962 -- Commented the end date change in PA as this is not required
3963 -- Solves bug 1608837
3964 /* Update_EndDate(p_person_id => p_person_id,
3965 p_old_start_date => p_old_start_date,
3966 p_new_start_date => p_new_start_date,
3967 p_old_end_date => p_old_end_date,
3968 p_new_end_date => p_new_end_date,
3969 x_return_status => x_return_status,
3970 x_msg_data => x_msg_data,
3971 x_msg_count => x_msg_count);
3972 */
3973
3974 --Call Forecast Item regeneration API
3975 PA_FORECASTITEM_PVT.Create_Forecast_Item(
3976 p_person_id => p_person_id,
3977 p_start_date => p_new_start_date,
3978 p_end_date => p_new_end_date,
3979 p_process_mode => 'GENERATE',
3980 x_return_status => l_return_status,
3981 x_msg_count => x_msg_count,
3982 x_msg_data => x_msg_data) ;
3983
3984 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3985 x_return_status := FND_API.G_RET_STS_ERROR ;
3986 END IF;
3987
3988 -- reset the Error stack
3989 PA_DEBUG.Reset_Err_Stack;
3990
3991 EXCEPTION
3992 WHEN OTHERS THEN
3993 -- 4537865 : RESET x_msg_count and x_msg_data also
3994 x_msg_count := 1 ;
3995 x_msg_data := SUBSTRB(SQLERRM ,1,240);
3996 -- Set the exception Message and the stack
3997 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_PrimaryFlag'
3998 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4000 raise;
4001 END Update_PrimaryFlag;
4002
4003 /* Procedure Update_Name calls updates the resource's name in pa_resources.
4004 */
4005 PROCEDURE Update_Name(
4006 p_person_id IN per_all_people_f.person_id%TYPE,
4007 p_old_name IN per_all_people_f.full_name%TYPE,
4008 p_new_name IN per_all_people_f.full_name%TYPE,
4009 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4010 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4011 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4012
4013 l_resource_id pa_resources.resource_id%TYPE;
4014 l_return_status VARCHAR2(1);
4015
4016 l_resource_rec_old PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4017 l_resource_rec_new PA_RESOURCE_PVT.Resource_Denorm_Rec_Type;
4018 BEGIN
4019
4020 --Initialize the Error stack
4021 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Update_Name');
4022 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4023
4024 --Get resource_id for person_id
4025 l_resource_id := pa_resource_utils.get_resource_id(p_person_id);
4026
4027 IF (l_resource_id = -999) THEN
4028 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4029 return;
4030 END IF;
4031
4032 UPDATE pa_resources
4033 SET name = p_new_name
4034 WHERE resource_id = l_resource_id;
4035
4036 --Set the values for the Resources Denorm Record Type
4037 l_resource_rec_old.person_id := p_person_id;
4038 l_resource_rec_new.person_id := p_person_id;
4039
4040 l_resource_rec_old.resource_name := p_old_name;
4041 l_resource_rec_new.resource_name := p_new_name;
4042
4043 --Call Resource Denorm API
4044 PA_RESOURCE_PVT.update_resource_denorm(
4045 p_resource_denorm_old_rec => l_resource_rec_old
4046 ,p_resource_denorm_new_rec => l_resource_rec_new
4047 ,x_return_status => l_return_status
4048 ,x_msg_data => x_msg_data
4049 ,x_msg_count => x_msg_count);
4050
4051 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4052 x_return_status := FND_API.G_RET_STS_ERROR ;
4053 END IF;
4054
4055 --Reset the Error stack
4056 PA_DEBUG.Reset_Err_Stack;
4057
4058 EXCEPTION
4059 WHEN OTHERS THEN
4060 -- 4537865 : RESET x_msg_count and x_msg_data also
4061 x_msg_count := 1 ;
4062 x_msg_data := SUBSTRB(SQLERRM ,1,240);
4063 -- Set the exception Message and the stack
4064 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Update_Name'
4065 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4067 raise;
4068 END Update_Name;
4069
4070
4071
4072 -- Procedure Address_Change Modified for location denormalization
4073 -- to update the pa_resource_denorm when person address changes with respect
4074 -- date
4075 PROCEDURE address_change ( p_calling_mode in varchar2,
4076 p_person_id in number,
4077 p_country_old in varchar2,
4078 p_country_new in varchar2,
4079 p_city_old in varchar2,
4080 p_city_new in varchar2,
4081 p_region2_old in varchar2,
4082 p_region2_new in varchar2,
4083 p_date_from_old in date,
4084 p_date_from_new in date,
4085 p_date_to_old in date,
4086 p_date_to_new in date,
4087 p_addr_prim_flag_old in varchar2,
4088 p_addr_prim_flag_new in varchar2,
4089 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
4090 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
4091 x_msg_data out NOCOPY varchar2) IS --File.Sql.39 bug 4440895
4092
4093 l_return_status VARCHAR2(1);
4094 l_resource_id NUMBER;
4095
4096 BEGIN
4097
4098
4099 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4100
4101 IF p_addr_prim_flag_new = 'Y' THEN
4102
4103 --dbms_output.put_line('Inside address Update for person id:' || p_person_id);
4104
4105 pa_r_project_resources_pub.create_resource (
4106 p_api_version => 1.0
4107 ,p_init_msg_list => NULL
4108 ,p_commit => FND_API.G_FALSE
4109 ,p_validate_only => NULL
4110 ,p_max_msg_count => NULL
4111 ,p_internal => 'Y'
4112 ,p_person_id => p_person_id
4113 ,p_individual => 'Y'
4114 ,p_resource_type => 'EMPLOYEE'
4115 ,x_return_status => l_return_status
4116 ,x_msg_count => x_msg_count
4117 ,x_msg_data => x_msg_data
4118 ,x_resource_id => l_resource_id);
4119
4120 END IF;
4121
4122 --dbms_output.put_line('After address Update');
4123
4124 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4125 x_return_status := FND_API.G_RET_STS_ERROR ;
4126 END IF;
4127
4128 EXCEPTION
4129 when others then
4130 NULL;
4131 raise;
4132
4133 END address_change;
4134
4135 FUNCTION Get_Country_name(p_country_code VARCHAR2) RETURN VARCHAR2 IS
4136
4137 l_country_code VARCHAR2(240);
4138
4139 BEGIN
4140
4141 SELECT territory_short_name
4142 INTO l_country_code
4143 FROM fnd_territories_vl
4144 WHERE territory_code = p_country_code ;
4145
4146 RETURN l_country_code ;
4147 EXCEPTION
4148
4149 WHEN NO_DATA_FOUND THEN
4150 RETURN NULL;
4151
4152 WHEN OTHERS THEN
4153
4154 RAISE ;
4155
4156 END ;
4157
4158 FUNCTION get_job_name(P_job_id IN per_jobs.job_id%type)
4159 RETURN per_jobs.name%type IS
4160
4161 l_job_name varchar2(240);
4162
4163 BEGIN
4164
4165 SELECT name
4166 INTO l_job_name
4167 FROM per_jobs
4168 WHERE job_id = P_job_id;
4169
4170 RETURN (l_job_name) ;
4171 EXCEPTION
4172
4173 WHEN NO_DATA_FOUND THEN
4174 RETURN NULL;
4175
4176 WHEN OTHERS THEN
4177
4178 RAISE ;
4179
4180 END ;
4181
4182 FUNCTION get_org_name(P_org_id IN hr_all_organization_units.organization_id%type)
4183 RETURN hr_all_organization_units.name%type IS
4184 l_org_name varchar2(240);
4185
4186 BEGIN
4187
4188 SELECT name
4189 INTO l_org_name
4190 FROM hr_all_organization_units_tl
4191 WHERE organization_id = P_org_id
4192 AND language = USERENV('LANG');
4193
4194 RETURN (l_org_name) ;
4195 EXCEPTION
4196 WHEN NO_DATA_FOUND THEN
4197 RETURN NULL;
4198
4199 WHEN OTHERS THEN
4200 RAISE ;
4201 END ;
4202
4203 FUNCTION get_grade_name(P_grade_id IN NUMBER)
4204 RETURN VARCHAR2 IS
4205 l_grade_name varchar2(240);
4206
4207 BEGIN
4208 SELECT name
4209 INTO l_grade_name
4210 FROM per_grades
4211 WHERE grade_id = P_grade_id;
4212
4213 RETURN (l_grade_name) ;
4214 EXCEPTION
4215 WHEN NO_DATA_FOUND THEN
4216 RETURN NULL;
4217
4218 WHEN OTHERS THEN
4219 RAISE ;
4220 END ;
4221
4222 -- Added for bug 3957522
4223 -- Procedure to delete records in pa_resources_denorm
4224 PROCEDURE Delete_PA_Resource_Denorm(
4225 p_person_id IN per_all_people_f.person_id%TYPE,
4226 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
4227 p_old_end_date IN per_all_assignments_f.effective_end_date%TYPE,
4228 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4229 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4230 x_msg_count OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
4231
4232
4233 CURSOR res_denorm_recs IS
4234 SELECT resource_effective_start_date,
4235 resource_effective_end_date
4236 FROM pa_resources_denorm
4237 WHERE person_id = p_person_id
4238 AND resource_effective_start_date >= p_old_start_date
4239 AND resource_effective_end_date <= p_old_end_date
4240 ;
4241
4242 BEGIN
4243 -- Initialize the Error stack
4244 PA_DEBUG.init_err_stack('PA_HR_UPDATE_API.Delete_PA_Resource_Denorm');
4245 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4246
4247 FOR rec IN res_denorm_recs LOOP
4248 --Delete the record
4249 DELETE FROM PA_RESOURCES_DENORM
4250 WHERE person_id = p_person_id
4251 AND resource_effective_start_date = rec.resource_effective_start_date;
4252
4253 END LOOP; --end FOR
4254
4255 -- reset the Error stack
4256 PA_DEBUG.Reset_Err_Stack;
4257
4258 EXCEPTION
4259
4260 WHEN NO_DATA_FOUND THEN
4261 x_return_status := FND_API.G_RET_STS_ERROR ;
4262
4263 WHEN OTHERS THEN
4264 -- 4537865 : RESET x_msg_count and x_msg_data also
4265 x_msg_count := 1 ;
4266 x_msg_data := SUBSTRB(SQLERRM ,1,240);
4267 -- Set the exception Message and the stack
4268 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_HR_UPDATE_API.Delete_PA_Resource_Denorm'
4269 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4271 raise;
4272 END Delete_PA_Resource_Denorm;
4273
4274 END PA_HR_UPDATE_API;
[Home] [Help]