1 PACKAGE BODY pa_job_utils AS
2 -- $Header: PAJBUTLB.pls 120.5.12010000.4 2009/04/21 05:50:33 snizam ship $
3
4
5 --------------------------------------------------------------------------------------------------------------
6 -- This procedure prints the text which is being passed as the input
7 -- Input parameters
8 -- Parameters Type Required Description
9 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
10 -- Out parameters
11 ----------------------------------------------------------------------------------------------------------------
12 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
13 -- Added Debug Profile Option variable initialization for bug#5094780
14 G_HR_CROSS_BUSINESS_GROUP varchar2(1) := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
15 G_PA_PROJ_RES_JOB_GRP NUMBER := to_number(fnd_profile.value('PA_PROJ_RES_JOB_GRP'));
16
17 PROCEDURE log_message (p_log_msg IN VARCHAR2)
18 IS
19 BEGIN
20 --dbms_output.put_line('log: ' || p_log_msg);
21 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
22 pa_debug.write('log_message: ' || 'HR_UPDATE_API', 'log: ' || p_log_msg, 3);
23 END IF;
24 NULL;
25 END log_message;
26
27 --
28 -- PROCEDURE
29 -- Check_JobName_Or_Id
30 -- PURPOSE
31 -- This procedure does the following
32 -- If Job name is passed converts it to the id
33 -- If Job Id is passed,
34 -- based on the check_id_flag validates it
35 -- HISTORY
36 -- 27-JUN-2000 P.Bandla Created
37 -- 11-APR-2001 virangan Added LOV fixes
38 -- 27-APR-2001 virangan Removed LOV fixes
39 --
40 PROCEDURE Check_JobName_Or_Id(
41 p_job_id IN NUMBER,
42 p_job_name IN VARCHAR2,
43 p_check_id_flag IN VARCHAR2,
44 p_job_group_id IN NUMBER := NULL, -- 5130421
45 x_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
46 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
47 x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
48 BEGIN
49 IF p_job_id IS NOT NULL AND p_job_id<>FND_API.G_MISS_NUM THEN
50 IF p_check_id_flag = 'Y' THEN
51 SELECT job_id
52 INTO x_job_id
53 FROM per_jobs
54 WHERE job_id = p_job_id
55 AND job_group_id = nvl(p_job_group_id, job_group_id) -- 5130421
56 ;
57 ELSE
58 x_job_id := p_job_id;
59 END IF;
60 ELSE
61 SELECT job_id
62 INTO x_job_id
63 FROM per_jobs
64 WHERE name = p_job_name
65 AND job_group_id = nvl(p_job_group_id, job_group_id) -- 5130421
66 ;
67 END IF;
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 x_return_status := FND_API.G_RET_STS_ERROR;
73 x_error_message_code := 'PA_JOB_INVALID_AMBIGUOUS';
74 WHEN TOO_MANY_ROWS THEN
75 x_return_status := FND_API.G_RET_STS_ERROR;
76 x_error_message_code := 'PA_JOB_INVALID_AMBIGUOUS';
77 WHEN OTHERS THEN
78 --PA_Error_Utils.Set_Error_Stack
79 -- (`pa_job_utils.check_jobname_or_id');
80 -- This sets the current program unit name in the
81 -- error stack. Helpful in Debugging
82 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
83
84 END Check_JobName_Or_id;
85
86 --
87 -- PROCEDURE
88 -- Check_JobLevel
89 -- PURPOSE
90 -- This procedure validates the job level.
91 -- HISTORY
92 -- 04-AUG-2000 P.Bandla Created
93 -- 24-DEC-2001 A.Abdullah Modified to reflect the change in job
94 -- level implementation
95 -- It checks from pa_setup_job_levels_v if
96 -- the job level exists
97 --
98
99 PROCEDURE Check_JobLevel(
100 p_level IN NUMBER,
101 x_valid OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
102 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
103 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
104 IS
105 l_level NUMBER;
106
107 CURSOR joblevels IS
108 select job_level
109 from PA_SETUP_JOB_LEVELS_V
110 where job_level = p_level;
111
112 BEGIN
113 OPEN joblevels;
114 FETCH joblevels into l_level;
115
116 IF l_level IS NOT NULL THEN
117 x_valid := 'Y' ;
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119 ELSE
120 x_valid := 'N';
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 x_error_message_code := 'PA_JOBLEVEL_INVALID_AMBIGUOUS';
123 END IF;
124
125 CLOSE joblevels;
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 x_error_message_code := 'PA_JOBLEVEL_INVALID_AMBIGUOUS';
131 WHEN OTHERS THEN
132 --PA_Error_Utils.Set_Error_Stack
133 -- (`pa_job_utils.check_jobname_or_id');
134 -- This sets the current program unit name in the
135 -- error stack. Helpful in Debugging
136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
137
138 END Check_JobLevel;
139
140 -- PROCEDURE
141 -- Check_Job_GroupName_Or_Id
142 -- PURPOSE
143 -- This procedure does the following
144 -- If job group name is passed converts it to the id
145 -- If job group Id is passed,
146 -- based on the check_id_flag validates it
147 -- HISTORY
148 -- 21-NOV-2000 P. Bandla Created
149 -- 11-APR-2001 virangan Added LOV fixes
150 --
151 PROCEDURE Check_Job_GroupName_Or_Id(
152 p_job_group_id IN NUMBER,
153 p_job_group_name IN VARCHAR2,
154 p_check_id_flag IN VARCHAR2,
155 x_job_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
156 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
157 x_error_message_code OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
158 IS
159
160 l_current_id NUMBER := NULL;
161 l_num_ids NUMBER := 0;
162 l_id_found_flag VARCHAR(1) := 'N';
163
164 CURSOR j_ids IS
165 SELECT job_group_id
166 FROM per_job_groups
167 WHERE displayed_name = p_job_group_name;
168 BEGIN
169
170 IF p_job_group_id IS NOT NULL AND p_job_group_id<>FND_API.G_MISS_NUM THEN
171 IF p_check_id_flag = 'Y' THEN
172 SELECT job_group_id
173 INTO x_job_group_id
174 FROM per_job_groups
175 WHERE job_group_id = p_job_group_id;
176 ELSIF p_check_id_flag = 'N' THEN
177 x_job_group_id := p_job_group_id;
178 ELSIF (p_check_id_flag = 'A') THEN
179 IF (p_job_group_name IS NULL) THEN
180 -- Return a null ID since the name is null.
181 x_job_group_id := NULL;
182 ELSE
183 -- Find the ID which matches the Name passed
184 OPEN j_ids;
185 LOOP
186 FETCH j_ids INTO l_current_id;
187 EXIT WHEN j_ids%NOTFOUND;
188 IF (l_current_id = p_job_group_id) THEN
189 l_id_found_flag := 'Y';
190 x_job_group_id := p_job_group_id;
191 END IF;
192 END LOOP;
193 l_num_ids := j_ids%ROWCOUNT;
194 CLOSE j_ids;
195
196 IF (l_num_ids = 0) THEN
197 -- No IDs for name
198 RAISE NO_DATA_FOUND;
199 ELSIF (l_num_ids = 1) THEN
200 -- Since there is only one ID for the name use it.
201 x_job_group_id := l_current_id;
202 ELSIF (l_id_found_flag = 'N') THEN
203 -- More than one ID for the name and none of the IDs matched
204 -- the ID passed in.
205 RAISE TOO_MANY_ROWS;
206 END IF;
207 END IF;
208 END IF;
209 ELSE
210 IF (p_job_group_name IS NOT NULL) THEN
211 SELECT job_group_id
212 INTO x_job_group_id
213 FROM per_job_groups
214 WHERE displayed_name = p_job_group_name;
215 ELSE
216 x_job_group_id := NULL;
217 END IF;
218 END IF;
219
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 x_job_group_id := NULL;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 x_error_message_code := 'PA_JOBGROUP_INVALID';
227 WHEN TOO_MANY_ROWS THEN
228 x_job_group_id := NULL;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 x_error_message_code := 'PA_JOBGROUP_INVALID';
231 WHEN OTHERS THEN
232 x_job_group_id := NULL;
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
234
235 END Check_Job_GroupName_Or_Id;
236
237 --
238 --
239 -- Procedure
240 -- created by Ranga Iyengar : 05-DEC-2000
241 -- This Api validates the given job_id and job_group_id is a part of
242 -- the pa_job_relationships entity.the IN parameters will be job_id and
243 -- job_group_id
244 --
245 --
246 PROCEDURE validate_job_relationship
247 ( p_job_id IN per_jobs.job_id%type
248 ,p_job_group_id IN per_jobs.job_group_id%type
249 ,x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
250 ,x_error_message_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
251 ) IS
252
253 v_return_status VARCHAR2(2000);
254 v_error_message_code VARCHAR2(2000):='PA_INVALID_JOB_RELATION';
255 --'This Job Not Belongs to Valid Job Group';
256 v_job_group_id per_jobs.job_group_id%type ;
257
258
259 BEGIN
260 -- Initialize the Error stack
261 PA_DEBUG.init_err_stack('PA_JOB_UTILS.validate_job_relationship');
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263 SELECT job_group_id
264 INTO v_job_group_id
265 FROM per_jobs
266 WHERE job_id = p_job_id
267 AND job_group_id = p_job_group_id;
268
269 If v_job_group_id is NOT NULL then
270 v_return_status := FND_API.G_RET_STS_SUCCESS;
271 End if;
272
273 -- reset the Error stack
274 PA_DEBUG.Reset_Err_Stack;
275 EXCEPTION
276
277 WHEN NO_DATA_FOUND THEN
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 x_error_message_code := v_error_message_code;
280
281 WHEN TOO_MANY_ROWS THEN
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284 WHEN OTHERS THEN
285 -- Set the exception Message and the stack
286 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_JOB_UTILS.validate_job_relationship'
287 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
289 raise;
290 END validate_job_relationship;
291
292
293 --------------------------------------------------------------------------------
294 -- Function: Get_Proj_Res_Job_Group
295 -- Description: This function returns the project resource job group in the system
296 --------------------------------------------------------------------------------
297 Function Get_Proj_Res_Job_Group(p_job_id IN NUMBER)
298 RETURN NUMBER
299 IS
300 l_prjg_id NUMBER;
301 l_business_group_id NUMBER;
302 BEGIN
303
304 /*Commented for 5094780
305 IF fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'Y'
306 THEN
307 log_message('CBGA Profile is Y');
308 RETURN to_number(fnd_profile.value('PA_PROJ_RES_JOB_GRP'));
309 else */
310 -- changed for 5094780
311 IF G_HR_CROSS_BUSINESS_GROUP = 'Y'
312 THEN
313 log_message('CBGA Profile is Y');
314 RETURN G_PA_PROJ_RES_JOB_GRP;
315 ELSE
316 log_message('CBGA Profile is N');
317
318 -- if the p_job_id parameter is null, we get the business group id
319 -- from the profile option: this method is also called from the
320 -- parvw031.sql view
321 IF p_job_id is null THEN
322
323 log_message('Get business group id from profile option');
324 l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
325
326 ELSE
327
328 log_message('Get business group id using job id');
329 -- Get the business group of the job id
330 SELECT BUSINESS_GROUP_ID
331 INTO l_business_group_id
332 FROM PER_JOBS
333 WHERE job_id = p_job_id;
334
335 END IF;
336
337 log_message('l_business_group_id = ' || l_business_group_id);
338
339 SELECT ORG_INFORMATION1
340 INTO l_prjg_id
341 FROM HR_ORGANIZATION_INFORMATION
342 WHERE ORGANIZATION_ID=l_business_group_id
343 AND ORG_INFORMATION_CONTEXT='Project Resource Job Group';
344
345 log_message('PRJG id returned from context = ' || l_prjg_id);
346 RETURN to_number(l_prjg_id);
347 END IF;
348
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 RETURN NULL;
352 WHEN OTHERS THEN
353 RETURN NULL;
354 END;
355
356 --------------------------------------------------------------------------------
357 -- Function: Proj_Res_Job_Group_Exists
358 -- Description: This function checks if a Project Resource Job Group Exists
359 -- in the system. Returns 'Y' if it does and returns 'N' if it does
360 -- not.
361 --------------------------------------------------------------------------------
362 Function Proj_Res_Job_Group_Exists(p_job_id IN NUMBER)
363 RETURN VARCHAR2
364 IS
365 BEGIN
366 IF Get_Proj_Res_Job_Group(p_job_id => p_job_id) is null THEN
367 RETURN 'N';
368 ELSE
369 RETURN 'Y';
370 END IF;
371 END;
372 --------------------------------------------------------------------------------
373 -- Function: Is_Proj_Res_Job_Group
374 -- Description: This function checks if a job group passed in is a
375 -- Project Resource Job Group.
376 -- Returns 'Y' if it is and returns 'N' if it is not
377 --------------------------------------------------------------------------------
378 Function Is_Proj_Res_Job_Group(p_job_id IN NUMBER,
379 p_job_group_id IN NUMBER)
380 RETURN VARCHAR2
381 IS
382 BEGIN
383 IF p_job_group_id = Get_Proj_Res_Job_Group(p_job_id => p_job_id) THEN
384 RETURN 'Y';
385 ELSE
386 RETURN 'N';
387 END IF;
388 END;
389 --------------------------------------------------------------------------------
390 -- Function: Get_Job_Mapping
391 -- Description: This function returns the job in the Project Resource Job
392 -- Group towhich p_job_id is mapped.
393 -- Returns the job_id of the job in PRJG
394 --------------------------------------------------------------------------------
395 Function Get_Job_Mapping(p_job_id IN NUMBER,
396 p_job_group_id IN NUMBER)
397 RETURN NUMBER
398 IS
399 l_master_job_id NUMBER;
400 l_master_job_group_id NUMBER;
401 l_prjg_job_id NUMBER;
402 l_prjg_id NUMBER;
403
404 CURSOR get_master_job_to IS
405 SELECT to_job_id,to_job_group_id
406 FROM pa_job_relationships, per_job_groups pjg
407 WHERE from_job_id = P_job_id
408 AND from_job_group_id = P_job_group_id
409 AND to_job_group_id = pjg.job_group_id
410 AND pjg.master_flag = 'Y';
411
412 CURSOR get_master_job_from IS
413 SELECT from_job_id,from_job_group_id
414 FROM pa_job_relationships, per_job_groups pjg
415 WHERE to_job_id = P_job_id
416 AND to_job_group_id = P_job_group_id
417 AND from_job_group_id = pjg.job_group_id
418 AND pjg.master_flag = 'Y';
419
420 CURSOR get_prjg_job_to IS
421 SELECT to_job_id
422 FROM pa_job_relationships
423 WHERE from_job_id = l_master_job_id
424 AND from_job_group_id = l_master_job_group_id
425 AND to_job_group_id = l_prjg_id;
426
427 CURSOR get_prjg_job_from IS
428 SELECT from_job_id
429 FROM pa_job_relationships
430 WHERE to_job_id = l_master_job_id
431 AND to_job_group_id = l_master_job_group_id
432 AND from_job_group_id = l_prjg_id;
433 BEGIN
434
435 l_prjg_id := Get_Proj_Res_Job_Group(p_job_id => p_job_id);
436 log_message('PRJG Job Group Id = ' || l_prjg_id);
437
438 IF l_prjg_id is null THEN
439 RETURN NULL;
440 END IF;
441
442 -- only if it is not a master job, do we use the master cursor
443 IF(NOT check_master_job(p_job_id)) THEN
444
445 log_message('Not a master job in Get Job Mapping');
446 OPEN get_master_job_to;
447 FETCH get_master_job_to INTO l_master_job_id,l_master_job_group_id;
448
449 IF get_master_job_to%NOTFOUND THEN
450 OPEN get_master_job_from;
451 FETCH get_master_job_from INTO l_master_job_id,l_master_job_group_id;
452
453 IF get_master_job_from%NOTFOUND THEN
454 -- There is no mapping with the master job, so we cannot find the mapping
455 -- with the Project Resource Job Group
456 CLOSE get_master_job_from ; -- Added for 5338664
457 CLOSE get_master_job_to ; -- Added for 5338664
458 RETURN NULL;
459 ELSE -- Added for 5338664
460 CLOSE get_master_job_from ;
461 END IF;
462 END IF;
463
464 IF get_master_job_to%ISOPEN THEN --Added for 5338664
465 CLOSE get_master_job_to;
466 END IF;
467
468 ELSE
469 log_message('Is a master job in Get Job Mapping');
470 -- it's already a master job, so just assign to the local params
471 l_master_job_id := p_job_id;
472 l_master_job_group_id := p_job_group_id;
473 END IF;
474
475 IF Is_Proj_Res_Job_Group(p_job_id => l_master_job_id,
476 p_job_group_id => l_master_job_group_id) = 'Y'
477 THEN
478 RETURN l_master_job_id;
479 END IF;
480
481 OPEN get_prjg_job_to;
482 FETCH get_prjg_job_to INTO l_prjg_job_id;
483
484 IF get_prjg_job_to%NOTFOUND THEN
485 OPEN get_prjg_job_from;
486 FETCH get_prjg_job_from INTO l_prjg_job_id;
487 CLOSE get_prjg_job_from;
488 END IF;
489 CLOSE get_prjg_job_to;
490
491 RETURN l_prjg_job_id;
492 END;
493
494 --------------------------------------------------------------------------------
495 -- Function: Get_job_level
496 -- Description: This function returns the Job Level of the job passed in
497 -- by looking at the 'Project Job Level' DFF segmeent of the
498 -- 'Job Category' DFF.
499 --------------------------------------------------------------------------------
500
501 FUNCTION get_job_level(p_job_id IN NUMBER)
502 RETURN NUMBER
503 IS
504 l_job_level per_job_extra_info.jei_information4%type;
505 BEGIN
506
507 SELECT jei_information4
508 INTO l_job_level
509 FROM per_job_extra_info
510 WHERE job_id = p_job_id
511 AND information_type = 'Job Category'
512 AND jei_information4 IS NOT NULL; -- Bug 2898766
513
514 log_message('Job Level in get_job_level = ' || l_job_level);
515 RETURN TO_NUMBER(l_job_level);
516
517 EXCEPTION
518 /* Bug 2898766 - Handled the exception if more than one rows are returned */
519 WHEN TOO_MANY_ROWS THEN
520 l_job_level := '-99';
521 RETURN TO_NUMBER(l_job_level);
522 WHEN NO_DATA_FOUND THEN
523 RETURN NULL;
524 WHEN OTHERS THEN
525 RETURN NULL;
526 END;
527
528 --------------------------------------------------------------------------------
529 -- Function get_job_level
530 -- This function returns the job level DFF based on the job_id and Job_group_id
531 -- If there is no Project Resource Job Group (PRJG) in the system,
532 -- return the Job Level associated to the job.
533 -- If the Job belongs to a Job Group which is the PRJG,
534 -- Return the Job Level.
535 -- Find the mapping of the job to a job in PRJG.
536 -- If no mapping, return null job group.
537 -- If there is mappping, return the job level associated to the job in PRJG.
538 --------------------------------------------------------------------------------
539
540 FUNCTION get_job_level
541 (P_job_id IN per_jobs.job_id%type,
542 P_job_group_id IN per_job_groups.job_group_id%type)
543 RETURN NUMBER
544 IS
545 l_job_level per_job_extra_info.jei_information4%type;
546 l_job_info_type VARCHAR2(20) := 'Job Category';
547 l_master_job_id pa_job_relationships.to_job_id%type;
548 l_job_id per_jobs.job_id%type;
549 l_job_mapping NUMBER;
550 c_job_group_id NUMBER;
551
552
553 BEGIN
554
555 /*IF Proj_Res_Job_Group_Exists(p_job_id => p_job_id) = 'N' THEN -- Commented as part of the performance bug logged 6875253
556 -- There is no Project Resource Job Group in the system, so
557 -- pass back the passed in job's job level.
558 log_message('No PRJG in system, get job level using Job Id');
559 RETURN Get_Job_Level(p_job_id => p_job_id);
560 END IF;
561
562 -- Check if the Job group of the job is a PRJG
563 IF Is_Proj_Res_Job_Group(p_job_id => p_job_id,
564 p_job_group_id => p_job_group_id) = 'Y'
565 THEN
566 -- The job group of the job is the Project Resource Job Group, so
567 -- pass back the passed in job's job level.
568 log_message('Job is in PRJG group, get job level using Job Id');
569 RETURN Get_Job_Level(p_job_id => p_job_id);
570 END IF;*/
571
572 c_job_group_id := Get_Proj_Res_Job_Group(p_job_id => p_job_id); -- newly added code in place of the above commented code -- 6875253
573
574 IF c_job_group_id IS NULL THEN
575 log_message('No PRJG in system, get job level using Job Id');
576 RETURN Get_Job_Level(p_job_id => p_job_id);
577
578 ELSE
579 IF p_job_group_id = c_job_group_id THEN
580 log_message('Job is in PRJG group, get job level using Job Id');
581 RETURN Get_Job_Level(p_job_id => p_job_id);
582 END IF;
583 END IF;
584
585 -- Get the mapping with a job in PRJG
586 log_message('Job is not in PRJG group, get mapping first');
587 l_job_mapping := Get_Job_Mapping(p_job_id => p_job_id,
588 p_job_group_id => p_job_group_id);
589
590 log_message('l_job_mapping_id = ' || l_job_mapping);
591 IF l_job_mapping is null THEN
592 RETURN NULL;
593 ELSE
594 RETURN Get_Job_Level(p_job_id => l_job_mapping);
595 END IF;
596
597 END get_job_level;
598
599 -- This Function returns boolean value of true if a job is master job otherwise
600 -- it returns false -- IN parameter will be job_id
601 FUNCTION check_master_job(P_job_id IN per_Jobs.job_id%type)
602 RETURN boolean
603 IS
604 v_job_group_id per_jobs.job_group_id%type;
605 v_master_flag per_job_groups.master_flag%type;
606
607 BEGIN
608 v_job_group_id := get_job_group_id(P_job_id);
609
610 -- Bug 4350734 - Changed to base table from per_job_groups_v
611 SELECT master_flag
612 INTO v_master_flag
613 FROM per_job_groups
614 WHERE job_group_id = v_job_group_id;
615
616 if v_master_flag = 'Y' then
617 return TRUE;
618 else
619 return FALSE;
620 end if;
621 EXCEPTION
622
623 WHEN NO_DATA_FOUND then
624 return FALSE;
625
626 END check_master_job;
627
628 -- This API returns the job group id for the corresponding Job
629 FUNCTION get_job_group_id(
630 P_job_id IN per_jobs.job_id%type
631 ) RETURN per_job_groups.job_group_id%type
632 IS
633 v_job_grp_id per_job_groups.job_group_id%type;
634
635 BEGIN
636 -- Bug 4350734 - Removed max sine it is not needed.
637
638 SELECT job_group_id
639 INTO v_job_grp_id
640 FROM per_jobs
641 WHERE job_id = P_job_id;
642
643 return (v_job_grp_id);
644
645 EXCEPTION
646
647 WHEN NO_DATA_FOUND THEN --Bug 8263219
648 return NULL;
649
650 END get_job_group_id;
651
652
653 --------------------------------------------------------------------------------
654 -- Function get_job_name
655 -- This function returns the job level DFF based on the job_id and Job_group_id
656 -- If there is no Project Resource Job Group (PRJG) in the system,
657 -- return the Job Level associated to the job.
658 -- If the Job belongs to a Job Group which is the PRJG,
659 -- Return the Job Level.
660 -- Find the mapping of the job to a job in PRJG.
661 -- If no mapping, return null job group.
662 -- If there is mappping, return the job level associated to the job in PRJG.
663 --------------------------------------------------------------------------------
664
665 FUNCTION get_job_name
666 (P_job_id IN per_jobs.job_id%type,
667 P_job_group_id IN per_job_groups.job_group_id%type)
668 RETURN VARCHAR2
669 IS
670 l_mapped_job_id NUMBER;
671 BEGIN
672 -- If there is no Project Resource Job Group in the system or
673 -- the Job group of the job is the PRJG, pass back the passed
674 -- in job's job level.
675 IF (Proj_Res_Job_Group_Exists(p_job_id => p_job_id) = 'N' OR
676 Is_Proj_Res_Job_Group(p_job_id => p_job_id,
677 p_job_group_id => p_job_group_id) = 'Y') THEN
678 log_message('No PRJG in system or job group=PRJG, get job level using Job Id');
679 RETURN PA_HR_UPDATE_API.Get_Job_Name (p_job_id => p_job_id);
680 END IF;
681
682 -- Get the mapping with a job in PRJG
683 log_message('Job is not in PRJG group, get mapping first');
684 l_mapped_job_id := Get_Job_Mapping(p_job_id => p_job_id,
685 p_job_group_id => p_job_group_id);
686
687 log_message('l_mapped_job_id = ' || l_mapped_job_id);
688 IF l_mapped_job_id is null THEN
689 RETURN NULL;
690 ELSE
691 RETURN PA_HR_UPDATE_API.Get_Job_Name(p_job_id => l_mapped_job_id);
692 END IF;
693
694 END get_job_name;
695
696 -----------------------------------------------------------------------------
697 -- Procedure : check_job_relationships
698 -- This procedure checks if there are any relatioships existing in PA
699 -- for a given job_id.
700
701 -- Called by HR API, before deleting a job
702 -----------------------------------------------------------------------------
703
704 PROCEDURE check_job_relationships (p_job_id IN number)
705 IS
706 l_job_relationship PA_JOB_RELATIONSHIPS%ROWTYPE;
707 Cursor C1 IS Select * from PA_JOB_RELATIONSHIPS where
708 FROM_JOB_ID = p_job_id OR TO_JOB_ID = p_job_id;
709 begin
710
711 Open C1;
712 fetch C1 into l_job_relationship;
713
714 IF C1%FOUND Then
715 Close C1;
716 dbms_standard.raise_application_error
717 (num => -20999
718 ,msg => 'Relations involving this job exist in Projects. Please delete those relations before deleting the job!');
719 END IF;
720
721 Close C1;
722 END check_job_relationships;
723
724 END pa_job_utils ;
725
[Home] [Help]