[Home] [Help]
PACKAGE BODY: APPS.GHR_API
Source
1 PACKAGE BODY ghr_api AS
2 /* $Header: ghapiapi.pkb 120.10.12010000.2 2008/08/05 15:01:11 ubhat ship $ */
3 --
4 -- Private Record Type
5 --
6 type org_info_rec_type is record
7 (information1 hr_organization_information.org_information1%type
8 ,information2 hr_organization_information.org_information2%type
9 ,information3 hr_organization_information.org_information3%type
10 ,information4 hr_organization_information.org_information4%type
11 ,information5 hr_organization_information.org_information5%type
12 );
13 --
14 -- Private Cursors
15 --
16 cursor c_asg_pos_by_per_id (per_id number, eff_date date) is
17 select asg.position_id
18 from per_all_assignments_f asg
19 where asg.person_id = per_id
20 and asg.assignment_type <> 'B'
21 and trunc(eff_date) between asg.effective_start_date
22 and asg.effective_end_date
23 and asg.primary_flag = 'Y';
24 --
25 cursor c_asg_pos_by_asg_id (asg_id number, eff_date date) is
26 select asg.position_id
27 from per_all_assignments_f asg
28 where asg.assignment_id = asg_id
29 and asg.assignment_type <> 'B'
30 and trunc(eff_date) between asg.effective_start_date
31 and asg.effective_end_date;
32 --
33 cursor c_asg_job_by_per_id (per_id number, eff_date date) is
34 select asg.job_id
35 from per_all_assignments_f asg
36 where asg.person_id = per_id
37 and asg.assignment_type <> 'B'
38 and trunc(eff_date) between asg.effective_start_date
39 and asg.effective_end_date
40 and asg.primary_flag = 'Y';
41 --
42 cursor c_asg_job_by_asg_id (asg_id number, eff_date date) is
43 select asg.job_id
44 from per_all_assignments_f asg
45 where asg.assignment_id = asg_id
46 and asg.assignment_type <> 'B'
47 and trunc(eff_date) between asg.effective_start_date
48 and asg.effective_end_date;
49
50 --
51 Cursor c_per_type(per_id number,eff_date date) is
52 Select ppt.system_person_type
53 from per_all_people_f per,per_person_types ppt
54 where per.person_id = per_id
55 and trunc(eff_date) between per.effective_start_date
56 and per.effective_end_date
57 and per.person_type_id = ppt.person_type_id;
58 --
59 -- ---------------------------------------------------------------------------
60 -- |--------------------< retrieve_business_group_id >-----------------------|
61 -- ---------------------------------------------------------------------------
62 -- {Start of Comments}
63 --
64 -- Description:
65 -- Retrieve business group id
66 --
67 -- Prerequisites:
68 -- Either p_person_id or p_assignment_id must be provided.
69 --
70 -- In Parameters:
71 -- p_person_id
72 -- The default is NULL.
73 -- p_assignment_id
74 -- The default is NULL.
75 -- p_effective_date
76 -- The default is sysdate.
77 --
78 -- out Parameters:
79 -- p_business_group_id
80 --
81 -- Post Success:
82 -- Processing continues.
83 --
84 -- Post Failure:
85 -- An application error will be raised and processing is terminated.
86 --
87 -- Developer Implementation Notes:
88 -- Calling hr_utility procedures are commented out to these procedures
89 -- update the database which violates the function requirement.
90 --
91 -- Access Status:
92 -- Internal Use Only.
93 --
94 -- {End of Comments}
95 -- ---------------------------------------------------------------------------
96 --
97 procedure retrieve_business_group_id
98 (p_person_id in per_people_f.person_id%type default null
99 ,p_assignment_id in per_assignments_f.assignment_id%type default null
100 ,p_effective_date in date default sysdate
101 ,p_business_group_id OUT NOCOPY per_business_groups.business_group_id%type
102 ) is
103 --
104 l_proc varchar2(72) := g_package||'retrieve_business_group_id';
105 l_person_found boolean := FALSE;
106 l_assignment_found boolean := FALSE;
107 l_person_type per_person_types.system_person_type%type;
108 --
109 cursor c_person (per_id number, eff_date date) is
110 select per.business_group_id
111 from per_all_people_f per
112 where per.person_id = per_id
113 and trunc(eff_date) between per.effective_start_date
114 and per.effective_end_date;
115 --
116 cursor c_assignment (asg_id number, eff_date date) is
117 select asg.business_group_id
118 from per_all_assignments_f asg
119 where asg.assignment_id = asg_id
120 and asg.assignment_type <> 'B'
121 and trunc(eff_date) between asg.effective_start_date
122 and asg.effective_end_date;
123 --
124 begin
125 -- hr_utility.set_location('Entering:'||l_proc, 1);
126 p_business_group_id := NULL;
127 --
128 if p_person_id is NULL and p_assignment_id is NULL then
129 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
130 -- hr_utility.raise_error;
131 null;
132 elsif p_assignment_id is NULL then
133 for per_type in c_per_type(p_person_id,p_effective_date) loop
134 l_person_type := per_type.system_person_type;
135 end loop;
136 If l_person_type = 'EX_EMP' then -- Roh
137 null;
138 Else
139 for c_person_rec in c_person (p_person_id, p_effective_date) loop
140 l_person_found := TRUE;
141 p_business_group_id := c_person_rec.business_group_id;
142 exit;
143 end loop;
144 if not l_person_found then
145 -- hr_utility.set_message(8301, 'GHR_38024_API_INV_PER');
146 -- hr_utility.raise_error;
147 null;
148 end if;
149 End if;-- Roh
150 else -- p_assignment_id is not NULL
151 for c_assignment_rec in c_assignment (p_assignment_id, p_effective_date) loop
152 l_assignment_found := TRUE;
153 p_business_group_id := c_assignment_rec.business_group_id;
154 exit;
155 end loop;
156 if not l_assignment_found then
157 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
158 -- hr_utility.raise_error;
159 null;
160 end if;
161 end if;
162 --
163 -- hr_utility.set_location(' Leaving:'||l_proc, 2);
164 EXCEPTION when others then
165 -- NOCOPY changes
166 -- Reset IN OUT params and set OUT params
167 p_business_group_id := NULL;
168 raise;
169 end retrieve_business_group_id;
170
171 -- retrieve_business_group_id overloaded to cater to refresh for the correction of SF52.
172 -- vsm
173 procedure retrieve_business_group_id
174 (p_person_id in per_people_f.person_id%type default null
175 ,p_assignment_id in per_assignments_f.assignment_id%type default null
176 ,p_effective_date in date default sysdate
177 ,p_altered_pa_request_id in number
178 ,p_pa_history_id in number
179 ,p_noa_id_corrected in number
180 ,p_business_group_id OUT NOCOPY per_business_groups.business_group_id%type
181 ) is
182 --
183 l_result_code varchar2(30);
184 l_people_data per_all_people_f%rowtype;
185 l_assignment_data per_all_assignments_f%rowtype;
186 l_proc varchar2(72) := g_package||'retrieve_business_group_id';
187 Begin
188 if p_person_id is not null then
189 ghr_history_fetch.fetch_people
190 ( p_person_id => p_person_id
191 ,p_date_effective => p_effective_date
192 ,p_altered_pa_request_id => p_altered_pa_request_id
193 ,p_noa_id_corrected => p_noa_id_corrected
194 ,p_pa_history_id => p_pa_history_id
195 ,p_people_data => l_people_data
196 ,p_result_code => l_result_code);
197 p_business_group_id := l_people_data.business_group_id;
198 elsif p_assignment_id is not null then
199 ghr_history_fetch.fetch_assignment
200 ( p_assignment_id => p_assignment_id
201 ,p_date_effective => p_effective_date
202 ,p_altered_pa_request_id => p_altered_pa_request_id
203 ,p_noa_id_corrected => p_noa_id_corrected
204 ,p_assignment_data => l_assignment_data
205 ,p_result_code => l_result_code);
206 p_business_group_id := l_assignment_data.business_group_id;
207 end if;
208 EXCEPTION when others then
209 -- NOCOPY changes
210 -- Reset IN OUT params and set OUT params
211 p_business_group_id := NULL;
212 raise;
213
214 end retrieve_business_group_id;
215 --
216
217 --
218 -- ---------------------------------------------------------------------------
219 -- |--------------------< retrieve_gov_kff_setup_info >----------------------|
220 -- ---------------------------------------------------------------------------
221 -- {Start of Comments}
222 --
223 -- Description:
224 -- Retrieve government key flexfields setup information.
225 --
226 -- Prerequisites:
227 -- Data must be existed in organization information with
228 -- ORG_INFORMATION = 'GHR_US_ORG_INFORMATION'.
229 --
230 -- In Parameters:
231 -- p_business_group_id
232 --
233 -- OUT NOCOPY Parameters:
234 -- p_org_info_rec
235 --
236 -- Post Success:
237 -- Processing continues.
238 --
239 -- Post Failure:
240 -- An application error will be raised and processing is terminated.
241 --
242 -- Developer Implementation Notes:
243 -- Calling hr_utility procedures are commented OUT NOCOPY due to these procedures
244 -- update the database which violates the function requirement.
245 --
246 -- Access Status:
247 -- Internal Use Only.
248 --
249 -- {End of Comments}
250 -- ---------------------------------------------------------------------------
251 procedure retrieve_gov_kff_setup_info
252 (p_business_group_id in per_business_groups.business_group_id%type
253 ,p_org_info_rec OUT NOCOPY org_info_rec_type
254 ) is
255 --
256 l_proc varchar2(72) := g_package||'retrieve_gov_kff_setup_info';
257 l_org_info_id hr_organization_information.org_information_id%type;
258 l_org_info_found boolean := FALSE;
259 --
260 cursor c_organization_information (org_id number) is
261 select oi.org_information1,
262 oi.org_information2,
263 oi.org_information3,
264 oi.org_information4,
265 oi.org_information5
266 from hr_organization_information oi
267 where oi.organization_id = org_id
268 and oi.org_information_context = 'GHR_US_ORG_INFORMATION';
269 --
270 begin
271 -- hr_utility.set_location('Entering:'||l_proc, 1);
272 p_org_info_rec.information1 := NULL;
273 p_org_info_rec.information2 := NULL;
274 p_org_info_rec.information3 := NULL;
275 p_org_info_rec.information4 := NULL;
276 p_org_info_rec.information5 := NULL;
277 --
278 for c_organization_information_rec in
279 c_organization_information (p_business_group_id) loop
280 l_org_info_found := TRUE;
281 p_org_info_rec.information1 := c_organization_information_rec.org_information1;
282 p_org_info_rec.information2 := c_organization_information_rec.org_information2;
283 p_org_info_rec.information3 := c_organization_information_rec.org_information3;
284 p_org_info_rec.information4 := c_organization_information_rec.org_information4;
285 p_org_info_rec.information5 := c_organization_information_rec.org_information5;
286 exit;
287 end loop;
288 if not l_org_info_found then
289 -- hr_utility.set_message(8301, 'GHR_38025_API_INV_ORG');
290 -- hr_utility.raise_error;
291 null;
292 end if;
293 -- hr_utility.set_location(l_proc, 2);
294 --
295 if (p_org_info_rec.information1 is NULL
296 and p_org_info_rec.information2 is NULL
297 and p_org_info_rec.information3 is NULL
298 and p_org_info_rec.information4 is NULL
299 and p_org_info_rec.information5 is NULL) then
300 -- hr_utility.set_message(8301, 'GHR_38033_API_ORG_DDF_NOT_EXST');
301 -- hr_utility.raise_error;
302 null;
303 end if;
304 --
305 -- hr_utility.set_location(' Leaving:'||l_proc, 3);
306 --
307 exception
308 when no_data_found then
309 -- hr_utility.set_message(8301, 'GHR_38033_API_ORG_DDF_NOT_EXST');
310 -- hr_utility.raise_error;
311 null;
312 when others then
313 -- NOCOPY changes
314 -- Reset IN OUT params and set OUT params
315 p_org_info_rec := NULL;
316 raise;
317 end retrieve_gov_kff_setup_info;
318 --
319 -- ---------------------------------------------------------------------------
320 -- |-------------------< retrieve_segment_information >----------------------|
321 -- ---------------------------------------------------------------------------
322 -- {Start of Comments}
323 --
324 -- Description:
325 -- Retrieve position/job segment information
326 --
327 -- Prerequisites:
328 --
329 -- In Parameters:
330 -- p_information
331 -- p_code
332 -- 'POS' - Position
333 -- 'JOB' - Job
334 --
335 -- OUT NOCOPY Parameters:
336 -- p_segment
337 --
338 -- Post Success:
339 -- Processing continues.
340 --
341 -- Post Failure:
342 -- An application error will be raised and processing is terminated.
343 --
344 -- Developer Implementation Notes:
345 -- Calling hr_utility procedures are commented OUT NOCOPY due to these procedures
346 -- update the database which violates the function requirement.
347 --
348 -- Access Status:
349 -- Internal Use Only.
350 --
351 -- {End of Comments}
352 -- ---------------------------------------------------------------------------
353 procedure retrieve_segment_information
354 (p_information in varchar2
355 ,p_id in number
356 ,p_code in varchar2
357 ,p_effective_date in date default sysdate
358 ,p_segment OUT NOCOPY varchar2
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'retrieve_segment_information';
362 l_segment varchar2(150);
363 l_pos_seg1_found boolean := FALSE;
364 l_pos_seg2_found boolean := FALSE;
365 l_pos_seg3_found boolean := FALSE;
366 l_pos_seg4_found boolean := FALSE;
367 l_pos_seg5_found boolean := FALSE;
368 l_pos_seg6_found boolean := FALSE;
369 l_pos_seg7_found boolean := FALSE;
370 l_pos_seg8_found boolean := FALSE;
371 l_pos_seg9_found boolean := FALSE;
372 l_pos_seg10_found boolean := FALSE;
373 l_pos_seg11_found boolean := FALSE;
374 l_pos_seg12_found boolean := FALSE;
375 l_pos_seg13_found boolean := FALSE;
376 l_pos_seg14_found boolean := FALSE;
377 l_pos_seg15_found boolean := FALSE;
378 l_pos_seg16_found boolean := FALSE;
379 l_pos_seg17_found boolean := FALSE;
380 l_pos_seg18_found boolean := FALSE;
381 l_pos_seg19_found boolean := FALSE;
382 l_pos_seg20_found boolean := FALSE;
383 l_pos_seg21_found boolean := FALSE;
384 l_pos_seg22_found boolean := FALSE;
385 l_pos_seg23_found boolean := FALSE;
386 l_pos_seg24_found boolean := FALSE;
387 l_pos_seg25_found boolean := FALSE;
388 l_pos_seg26_found boolean := FALSE;
389 l_pos_seg27_found boolean := FALSE;
390 l_pos_seg28_found boolean := FALSE;
391 l_pos_seg29_found boolean := FALSE;
392 l_pos_seg30_found boolean := FALSE;
393 l_job_seg1_found boolean := FALSE;
394 l_job_seg2_found boolean := FALSE;
395 l_job_seg3_found boolean := FALSE;
396 l_job_seg4_found boolean := FALSE;
397 l_job_seg5_found boolean := FALSE;
398 l_job_seg6_found boolean := FALSE;
399 l_job_seg7_found boolean := FALSE;
400 l_job_seg8_found boolean := FALSE;
401 l_job_seg9_found boolean := FALSE;
402 l_job_seg10_found boolean := FALSE;
403 l_job_seg11_found boolean := FALSE;
404 l_job_seg12_found boolean := FALSE;
405 l_job_seg13_found boolean := FALSE;
406 l_job_seg14_found boolean := FALSE;
407 l_job_seg15_found boolean := FALSE;
408 l_job_seg16_found boolean := FALSE;
409 l_job_seg17_found boolean := FALSE;
410 l_job_seg18_found boolean := FALSE;
411 l_job_seg19_found boolean := FALSE;
412 l_job_seg20_found boolean := FALSE;
413 l_job_seg21_found boolean := FALSE;
414 l_job_seg22_found boolean := FALSE;
415 l_job_seg23_found boolean := FALSE;
416 l_job_seg24_found boolean := FALSE;
417 l_job_seg25_found boolean := FALSE;
418 l_job_seg26_found boolean := FALSE;
419 l_job_seg27_found boolean := FALSE;
420 l_job_seg28_found boolean := FALSE;
421 l_job_seg29_found boolean := FALSE;
422 l_job_seg30_found boolean := FALSE;
423 --
424 -- Private Variables for Dynamic SQL
425 --
426 -- l_cursor_id number := NULL;
427 -- l_select_string varchar2(2000) := NULL;
428 -- l_numrows integer := NULL;
429 --
430 cursor c_pos_seg1 (pos_id number) is
431 select pdf.segment1
432 from hr_all_positions_f pos, per_position_definitions pdf
433 where pos.position_definition_id = pdf.position_definition_id
434 and pos.position_id = pos_id
435 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
436 --
437 cursor c_pos_seg2 (pos_id number) is
438 select pdf.segment2
439 from hr_all_positions_f pos, per_position_definitions pdf
440 where pos.position_definition_id = pdf.position_definition_id
441 and pos.position_id = pos_id
442 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
443 --
444 cursor c_pos_seg3 (pos_id number) is
445 select pdf.segment3
446 from hr_all_positions_f pos, per_position_definitions pdf
447 where pos.position_definition_id = pdf.position_definition_id
448 and pos.position_id = pos_id
449 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
450 --
451 cursor c_pos_seg4 (pos_id number) is
452 select pdf.segment4
453 from hr_all_positions_f pos, per_position_definitions pdf
454 where pos.position_definition_id = pdf.position_definition_id
455 and pos.position_id = pos_id
456 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
457 --
458 cursor c_pos_seg5 (pos_id number) is
459 select pdf.segment5
460 from hr_all_positions_f pos, per_position_definitions pdf
461 where pos.position_definition_id = pdf.position_definition_id
462 and pos.position_id = pos_id
463 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
464 --
465 cursor c_pos_seg6 (pos_id number) is
466 select pdf.segment6
467 from hr_all_positions_f pos, per_position_definitions pdf
468 where pos.position_definition_id = pdf.position_definition_id
469 and pos.position_id = pos_id
470 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
471 --
472 cursor c_pos_seg7 (pos_id number) is
473 select pdf.segment7
474 from hr_all_positions_f pos, per_position_definitions pdf
475 where pos.position_definition_id = pdf.position_definition_id
476 and pos.position_id = pos_id
477 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
478 --
479 cursor c_pos_seg8 (pos_id number) is
480 select pdf.segment8
481 from hr_all_positions_f pos, per_position_definitions pdf
482 where pos.position_definition_id = pdf.position_definition_id
483 and pos.position_id = pos_id
484 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
485 --
486 cursor c_pos_seg9 (pos_id number) is
487 select pdf.segment9
488 from hr_all_positions_f pos, per_position_definitions pdf
489 where pos.position_definition_id = pdf.position_definition_id
490 and pos.position_id = pos_id
491 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
492 --
493 cursor c_pos_seg10 (pos_id number) is
494 select pdf.segment10
495 from hr_all_positions_f pos, per_position_definitions pdf
496 where pos.position_definition_id = pdf.position_definition_id
497 and pos.position_id = pos_id
498 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
499 --
500 cursor c_pos_seg11 (pos_id number) is
501 select pdf.segment11
502 from hr_all_positions_f pos, per_position_definitions pdf
503 where pos.position_definition_id = pdf.position_definition_id
504 and pos.position_id = pos_id
505 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
506 --
507 cursor c_pos_seg12 (pos_id number) is
508 select pdf.segment12
509 from hr_all_positions_f pos, per_position_definitions pdf
510 where pos.position_definition_id = pdf.position_definition_id
511 and pos.position_id = pos_id
512 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
513 --
514 cursor c_pos_seg13 (pos_id number) is
515 select pdf.segment13
516 from hr_all_positions_f pos, per_position_definitions pdf
517 where pos.position_definition_id = pdf.position_definition_id
518 and pos.position_id = pos_id
519 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
520 --
521 cursor c_pos_seg14 (pos_id number) is
522 select pdf.segment14
523 from hr_all_positions_f pos, per_position_definitions pdf
524 where pos.position_definition_id = pdf.position_definition_id
525 and pos.position_id = pos_id
526 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
527 --
528 cursor c_pos_seg15 (pos_id number) is
529 select pdf.segment15
530 from hr_all_positions_f pos, per_position_definitions pdf
531 where pos.position_definition_id = pdf.position_definition_id
532 and pos.position_id = pos_id
533 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
534 --
535 cursor c_pos_seg16 (pos_id number) is
536 select pdf.segment16
537 from hr_all_positions_f pos, per_position_definitions pdf
538 where pos.position_definition_id = pdf.position_definition_id
539 and pos.position_id = pos_id
540 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
541 --
542 cursor c_pos_seg17 (pos_id number) is
543 select pdf.segment17
544 from hr_all_positions_f pos, per_position_definitions pdf
545 where pos.position_definition_id = pdf.position_definition_id
546 and pos.position_id = pos_id
547 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
548 --
549 cursor c_pos_seg18 (pos_id number) is
550 select pdf.segment18
551 from hr_all_positions_f pos, per_position_definitions pdf
552 where pos.position_definition_id = pdf.position_definition_id
553 and pos.position_id = pos_id
554 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
555 --
556 cursor c_pos_seg19 (pos_id number) is
557 select pdf.segment19
558 from hr_all_positions_f pos, per_position_definitions pdf
559 where pos.position_definition_id = pdf.position_definition_id
560 and pos.position_id = pos_id
561 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
562 --
563 cursor c_pos_seg20 (pos_id number) is
564 select pdf.segment20
565 from hr_all_positions_f pos, per_position_definitions pdf
566 where pos.position_definition_id = pdf.position_definition_id
567 and pos.position_id = pos_id
568 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
569 --
570 cursor c_pos_seg21 (pos_id number) is
571 select pdf.segment21
572 from hr_all_positions_f pos, per_position_definitions pdf
573 where pos.position_definition_id = pdf.position_definition_id
574 and pos.position_id = pos_id
575 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
576 --
577 cursor c_pos_seg22 (pos_id number) is
578 select pdf.segment22
579 from hr_all_positions_f pos, per_position_definitions pdf
580 where pos.position_definition_id = pdf.position_definition_id
581 and pos.position_id = pos_id
582 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
583 --
584 cursor c_pos_seg23 (pos_id number) is
585 select pdf.segment23
586 from hr_all_positions_f pos, per_position_definitions pdf
587 where pos.position_definition_id = pdf.position_definition_id
588 and pos.position_id = pos_id
589 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
590 --
591 cursor c_pos_seg24 (pos_id number) is
592 select pdf.segment24
593 from hr_all_positions_f pos, per_position_definitions pdf
594 where pos.position_definition_id = pdf.position_definition_id
595 and pos.position_id = pos_id
596 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
597 --
598 cursor c_pos_seg25 (pos_id number) is
599 select pdf.segment25
600 from hr_all_positions_f pos, per_position_definitions pdf
601 where pos.position_definition_id = pdf.position_definition_id
602 and pos.position_id = pos_id
603 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
604 --
605 cursor c_pos_seg26 (pos_id number) is
606 select pdf.segment26
607 from hr_all_positions_f pos, per_position_definitions pdf
608 where pos.position_definition_id = pdf.position_definition_id
609 and pos.position_id = pos_id
610 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
611 --
612 cursor c_pos_seg27 (pos_id number) is
613 select pdf.segment27
614 from hr_all_positions_f pos, per_position_definitions pdf
615 where pos.position_definition_id = pdf.position_definition_id
616 and pos.position_id = pos_id
617 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
618 --
619 cursor c_pos_seg28 (pos_id number) is
620 select pdf.segment28
621 from hr_all_positions_f pos, per_position_definitions pdf
622 where pos.position_definition_id = pdf.position_definition_id
623 and pos.position_id = pos_id
624 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
625 --
626 cursor c_pos_seg29 (pos_id number) is
627 select pdf.segment29
628 from hr_all_positions_f pos, per_position_definitions pdf
629 where pos.position_definition_id = pdf.position_definition_id
630 and pos.position_id = pos_id
631 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
632 --
633 cursor c_pos_seg30 (pos_id number) is
634 select pdf.segment30
635 from hr_all_positions_f pos, per_position_definitions pdf
636 where pos.position_definition_id = pdf.position_definition_id
637 and pos.position_id = pos_id
638 and p_effective_date between pos.effective_start_date and pos.effective_end_date;
639 --
640
641
642 --
643 cursor c_job_seg1 (p_job_id number) is
644 select jdf.segment1
645 from per_jobs job, per_job_definitions jdf
646 where job.job_definition_id = jdf.job_definition_id
647 and job.job_id = p_job_id;
648 --
649 cursor c_job_seg2 (p_job_id number) is
650 select jdf.segment2
651 from per_jobs job, per_job_definitions jdf
652 where job.job_definition_id = jdf.job_definition_id
653 and job.job_id = p_job_id;
654 --
655 cursor c_job_seg3 (p_job_id number) is
656 select jdf.segment3
657 from per_jobs job, per_job_definitions jdf
658 where job.job_definition_id = jdf.job_definition_id
659 and job.job_id = p_job_id;
660 --
661 cursor c_job_seg4 (p_job_id number) is
662 select jdf.segment4
663 from per_jobs job, per_job_definitions jdf
664 where job.job_definition_id = jdf.job_definition_id
665 and job.job_id = p_job_id;
666 --
667 cursor c_job_seg5 (p_job_id number) is
668 select jdf.segment5
669 from per_jobs job, per_job_definitions jdf
670 where job.job_definition_id = jdf.job_definition_id
671 and job.job_id = p_job_id;
672 --
673 cursor c_job_seg6 (p_job_id number) is
674 select jdf.segment6
675 from per_jobs job, per_job_definitions jdf
676 where job.job_definition_id = jdf.job_definition_id
677 and job.job_id = p_job_id;
678 --
679 cursor c_job_seg7 (p_job_id number) is
680 select jdf.segment7
681 from per_jobs job, per_job_definitions jdf
682 where job.job_definition_id = jdf.job_definition_id
683 and job.job_id = p_job_id;
684 --
685 cursor c_job_seg8 (p_job_id number) is
686 select jdf.segment8
687 from per_jobs job, per_job_definitions jdf
688 where job.job_definition_id = jdf.job_definition_id
689 and job.job_id = p_job_id;
690 --
691 cursor c_job_seg9 (p_job_id number) is
692 select jdf.segment9
693 from per_jobs job, per_job_definitions jdf
694 where job.job_definition_id = jdf.job_definition_id
695 and job.job_id = p_job_id;
696 --
697 cursor c_job_seg10 (p_job_id number) is
698 select jdf.segment10
699 from per_jobs job, per_job_definitions jdf
700 where job.job_definition_id = jdf.job_definition_id
701 and job.job_id = p_job_id;
702 --
703 cursor c_job_seg11 (p_job_id number) is
704 select jdf.segment11
705 from per_jobs job, per_job_definitions jdf
706 where job.job_definition_id = jdf.job_definition_id
707 and job.job_id = p_job_id;
708 --
709 cursor c_job_seg12 (p_job_id number) is
710 select jdf.segment12
711 from per_jobs job, per_job_definitions jdf
712 where job.job_definition_id = jdf.job_definition_id
713 and job.job_id = p_job_id;
714 --
715 cursor c_job_seg13 (p_job_id number) is
716 select jdf.segment13
717 from per_jobs job, per_job_definitions jdf
718 where job.job_definition_id = jdf.job_definition_id
719 and job.job_id = p_job_id;
720 --
721 cursor c_job_seg14 (p_job_id number) is
722 select jdf.segment14
723 from per_jobs job, per_job_definitions jdf
724 where job.job_definition_id = jdf.job_definition_id
725 and job.job_id = p_job_id;
726 --
727 cursor c_job_seg15 (p_job_id number) is
728 select jdf.segment15
729 from per_jobs job, per_job_definitions jdf
730 where job.job_definition_id = jdf.job_definition_id
731 and job.job_id = p_job_id;
732 --
733 cursor c_job_seg16 (p_job_id number) is
734 select jdf.segment16
735 from per_jobs job, per_job_definitions jdf
736 where job.job_definition_id = jdf.job_definition_id
737 and job.job_id = p_job_id;
738 --
739 cursor c_job_seg17 (p_job_id number) is
740 select jdf.segment17
741 from per_jobs job, per_job_definitions jdf
742 where job.job_definition_id = jdf.job_definition_id
743 and job.job_id = p_job_id;
744 --
745 cursor c_job_seg18 (p_job_id number) is
746 select jdf.segment18
747 from per_jobs job, per_job_definitions jdf
748 where job.job_definition_id = jdf.job_definition_id
749 and job.job_id = p_job_id;
750 --
751 cursor c_job_seg19 (p_job_id number) is
752 select jdf.segment19
753 from per_jobs job, per_job_definitions jdf
754 where job.job_definition_id = jdf.job_definition_id
755 and job.job_id = p_job_id;
756 --
757 cursor c_job_seg20 (p_job_id number) is
758 select jdf.segment20
759 from per_jobs job, per_job_definitions jdf
760 where job.job_definition_id = jdf.job_definition_id
761 and job.job_id = p_job_id;
762 --
763 cursor c_job_seg21 (p_job_id number) is
764 select jdf.segment21
765 from per_jobs job, per_job_definitions jdf
766 where job.job_definition_id = jdf.job_definition_id
767 and job.job_id = p_job_id;
768 --
769 cursor c_job_seg22 (p_job_id number) is
770 select jdf.segment22
771 from per_jobs job, per_job_definitions jdf
772 where job.job_definition_id = jdf.job_definition_id
773 and job.job_id = p_job_id;
774 --
775 cursor c_job_seg23 (p_job_id number) is
776 select jdf.segment23
777 from per_jobs job, per_job_definitions jdf
778 where job.job_definition_id = jdf.job_definition_id
779 and job.job_id = p_job_id;
780 --
781 cursor c_job_seg24 (p_job_id number) is
782 select jdf.segment24
783 from per_jobs job, per_job_definitions jdf
784 where job.job_definition_id = jdf.job_definition_id
785 and job.job_id = p_job_id;
786 --
787 cursor c_job_seg25 (p_job_id number) is
788 select jdf.segment25
789 from per_jobs job, per_job_definitions jdf
790 where job.job_definition_id = jdf.job_definition_id
791 and job.job_id = p_job_id;
792 --
793 cursor c_job_seg26 (p_job_id number) is
794 select jdf.segment26
795 from per_jobs job, per_job_definitions jdf
796 where job.job_definition_id = jdf.job_definition_id
797 and job.job_id = p_job_id;
798 --
799 cursor c_job_seg27 (p_job_id number) is
800 select jdf.segment27
801 from per_jobs job, per_job_definitions jdf
802 where job.job_definition_id = jdf.job_definition_id
803 and job.job_id = p_job_id;
804 --
805 cursor c_job_seg28 (p_job_id number) is
806 select jdf.segment28
807 from per_jobs job, per_job_definitions jdf
808 where job.job_definition_id = jdf.job_definition_id
809 and job.job_id = p_job_id;
810 --
811 cursor c_job_seg29 (p_job_id number) is
812 select jdf.segment29
813 from per_jobs job, per_job_definitions jdf
814 where job.job_definition_id = jdf.job_definition_id
815 and job.job_id = p_job_id;
816 --
817 cursor c_job_seg30 (p_job_id number) is
818 select jdf.segment30
819 from per_jobs job, per_job_definitions jdf
820 where job.job_definition_id = jdf.job_definition_id
821 and job.job_id = p_job_id;
822
823 --
824 begin
825 -- hr_utility.set_location('Entering:'||l_proc, 1);
826 /*
827 --
828 -- Open the cursor
829 --
830 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
831 --
832 -- Create the query string
833 --
834 if p_code = 'POS' then
835 l_select_string
836 := 'select pdf.' || p_information
837 || ' from per_positions pos, per_position_definitions pdf'
838 || ' where pos.position_definition_id = pdf.position_definition_id'
839 || ' and pos.position_id = ' || p_id;
840 elsif p_code = 'JOB' then
841 l_select_string
842 := 'select jdf.' || p_information
843 || ' from per_jobs job, per_job_definitions jdf'
844 || ' where job.job_definition_id = jdf.job_definition_id'
845 || ' and job.job_id = ' || p_id;
846 else -- Invalid p_code
847 null;
848 end if;
849 --
850 -- Parse the query
851 --
852 DBMS_SQL.PARSE(l_cursor_id, l_select_string, DBMS_SQL.V7);
853 --
854 -- Define the output variable
855 --
856 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_segment, 150);
857 --
858 -- Execute the query
859 --
860 l_numrows := DBMS_SQL.EXECUTE(l_cursor_id);
861 if DBMS_SQL.FETCH_ROWS(l_cursor_id) != 0 then
862 --
863 -- Retrieve row from the buffer into PL/SQL variable
864 --
865 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_segment);
866 --
867 -- Close the cursor
868 --
869 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
870 end if;
871 --
872 p_segment := l_segment;
873 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
874 --
875 --
876 exception
877 when others then
878 --
879 -- Close cursor, then raise error
880 --
881 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
882 -- hr_utility.set_message(8301,'GHR_9999_API_DYNAMIC_SQL_ERR');
883 -- hr_utility.raise_error;
884 */
885 --
886 p_segment := NULL;
887 --
888 if p_code = 'POS' then
889 if p_information = 'SEGMENT1' then
890 for c_pos_seg1_rec in c_pos_seg1 (p_id) loop
891 l_pos_seg1_found := TRUE;
892 p_segment := c_pos_seg1_rec.segment1;
893 exit;
894 end loop;
895 if not l_pos_seg1_found then
896 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
897 -- hr_utility.raise_error;
898 null;
899 end if;
900 --
901 elsif p_information = 'SEGMENT2' then
902 for c_pos_seg2_rec in c_pos_seg2 (p_id) loop
903 l_pos_seg2_found := TRUE;
904 p_segment := c_pos_seg2_rec.segment2;
905 exit;
906 end loop;
907 if not l_pos_seg2_found then
908 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
909 -- hr_utility.raise_error;
910 null;
911 end if;
912 --
913 elsif p_information = 'SEGMENT3' then
914 for c_pos_seg3_rec in c_pos_seg3 (p_id) loop
915 l_pos_seg3_found := TRUE;
916 p_segment := c_pos_seg3_rec.segment3;
917 exit;
918 end loop;
919 if not l_pos_seg3_found then
920 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
921 -- hr_utility.raise_error;
922 null;
923 end if;
924 --
925 elsif p_information = 'SEGMENT4' then
926 for c_pos_seg4_rec in c_pos_seg4 (p_id) loop
927 l_pos_seg4_found := TRUE;
928 p_segment := c_pos_seg4_rec.segment4;
929 exit;
930 end loop;
931 if not l_pos_seg4_found then
932 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
933 -- hr_utility.raise_error;
934 null;
935 end if;
936 --
937 elsif p_information = 'SEGMENT5' then
938 for c_pos_seg5_rec in c_pos_seg5 (p_id) loop
939 l_pos_seg5_found := TRUE;
940 p_segment := c_pos_seg5_rec.segment5;
941 exit;
942 end loop;
943 if not l_pos_seg5_found then
944 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
945 -- hr_utility.raise_error;
946 null;
947 end if;
948 --
949 elsif p_information = 'SEGMENT6' then
950 for c_pos_seg6_rec in c_pos_seg6 (p_id) loop
951 l_pos_seg6_found := TRUE;
952 p_segment := c_pos_seg6_rec.segment6;
953 exit;
954 end loop;
955 if not l_pos_seg6_found then
956 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
957 -- hr_utility.raise_error;
958 null;
959 end if;
960 --
961 elsif p_information = 'SEGMENT7' then
962 for c_pos_seg7_rec in c_pos_seg7 (p_id) loop
963 l_pos_seg7_found := TRUE;
964 p_segment := c_pos_seg7_rec.segment7;
965 exit;
966 end loop;
967 if not l_pos_seg7_found then
968 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
969 -- hr_utility.raise_error;
970 null;
971 end if;
972 --
973 elsif p_information = 'SEGMENT8' then
974 for c_pos_seg8_rec in c_pos_seg8 (p_id) loop
975 l_pos_seg8_found := TRUE;
976 p_segment := c_pos_seg8_rec.segment8;
977 exit;
978 end loop;
979 if not l_pos_seg8_found then
980 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
981 -- hr_utility.raise_error;
982 null;
983 end if;
984 --
985 elsif p_information = 'SEGMENT9' then
986 for c_pos_seg9_rec in c_pos_seg9 (p_id) loop
987 l_pos_seg9_found := TRUE;
988 p_segment := c_pos_seg9_rec.segment9;
989 exit;
990 end loop;
991 if not l_pos_seg9_found then
992 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
993 -- hr_utility.raise_error;
994 null;
995 end if;
996 --
997 elsif p_information = 'SEGMENT10' then
998 for c_pos_seg10_rec in c_pos_seg10 (p_id) loop
999 l_pos_seg10_found := TRUE;
1000 p_segment := c_pos_seg10_rec.segment10;
1001 exit;
1002 end loop;
1003 if not l_pos_seg10_found then
1004 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1005 -- hr_utility.raise_error;
1006 null;
1007 end if;
1008 --
1009 elsif p_information = 'SEGMENT11' then
1010 for c_pos_seg11_rec in c_pos_seg11 (p_id) loop
1011 l_pos_seg11_found := TRUE;
1012 p_segment := c_pos_seg11_rec.segment11;
1013 exit;
1014 end loop;
1015 if not l_pos_seg11_found then
1016 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1017 -- hr_utility.raise_error;
1018 null;
1019 end if;
1020 --
1021 elsif p_information = 'SEGMENT12' then
1022 for c_pos_seg12_rec in c_pos_seg12 (p_id) loop
1023 l_pos_seg12_found := TRUE;
1024 p_segment := c_pos_seg12_rec.segment12;
1025 exit;
1026 end loop;
1027 if not l_pos_seg12_found then
1028 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1029 -- hr_utility.raise_error;
1030 null;
1031 end if;
1032 --
1033 elsif p_information = 'SEGMENT13' then
1034 for c_pos_seg13_rec in c_pos_seg13 (p_id) loop
1035 l_pos_seg13_found := TRUE;
1036 p_segment := c_pos_seg13_rec.segment13;
1037 exit;
1038 end loop;
1039 if not l_pos_seg13_found then
1040 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1041 -- hr_utility.raise_error;
1042 null;
1043 end if;
1044 --
1045 elsif p_information = 'SEGMENT14' then
1046 for c_pos_seg14_rec in c_pos_seg14 (p_id) loop
1047 l_pos_seg14_found := TRUE;
1048 p_segment := c_pos_seg14_rec.segment14;
1049 exit;
1050 end loop;
1051 if not l_pos_seg14_found then
1052 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1053 -- hr_utility.raise_error;
1054 null;
1055 end if;
1056 --
1057 elsif p_information = 'SEGMENT15' then
1058 for c_pos_seg15_rec in c_pos_seg15 (p_id) loop
1059 l_pos_seg15_found := TRUE;
1060 p_segment := c_pos_seg15_rec.segment15;
1061 exit;
1062 end loop;
1063 if not l_pos_seg15_found then
1064 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1065 -- hr_utility.raise_error;
1066 null;
1067 end if;
1068 --
1069 elsif p_information = 'SEGMENT15' then
1070 for c_pos_seg15_rec in c_pos_seg15 (p_id) loop
1071 l_pos_seg15_found := TRUE;
1072 p_segment := c_pos_seg15_rec.segment15;
1073 exit;
1074 end loop;
1075 if not l_pos_seg15_found then
1076 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1077 -- hr_utility.raise_error;
1078 null;
1079 end if;
1080 --
1081 elsif p_information = 'SEGMENT16' then
1082 for c_pos_seg16_rec in c_pos_seg16 (p_id) loop
1083 l_pos_seg16_found := TRUE;
1084 p_segment := c_pos_seg16_rec.segment16;
1085 exit;
1086 end loop;
1087 if not l_pos_seg16_found then
1088 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1089 -- hr_utility.raise_error;
1090 null;
1091 end if;
1092 --
1093 elsif p_information = 'SEGMENT17' then
1094 for c_pos_seg17_rec in c_pos_seg17 (p_id) loop
1095 l_pos_seg17_found := TRUE;
1096 p_segment := c_pos_seg17_rec.segment17;
1097 exit;
1098 end loop;
1099 if not l_pos_seg17_found then
1100 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1101 -- hr_utility.raise_error;
1102 null;
1103 end if;
1104 --
1105 elsif p_information = 'SEGMENT18' then
1106 for c_pos_seg18_rec in c_pos_seg18 (p_id) loop
1107 l_pos_seg18_found := TRUE;
1108 p_segment := c_pos_seg18_rec.segment18;
1109 exit;
1110 end loop;
1111 if not l_pos_seg18_found then
1112 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1113 -- hr_utility.raise_error;
1114 null;
1115 end if;
1116 --
1117 elsif p_information = 'SEGMENT19' then
1118 for c_pos_seg19_rec in c_pos_seg19 (p_id) loop
1119 l_pos_seg19_found := TRUE;
1120 p_segment := c_pos_seg19_rec.segment19;
1121 exit;
1122 end loop;
1123 if not l_pos_seg19_found then
1124 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1125 -- hr_utility.raise_error;
1126 null;
1127 end if;
1128 --
1129 elsif p_information = 'SEGMENT20' then
1130 for c_pos_seg20_rec in c_pos_seg20 (p_id) loop
1131 l_pos_seg20_found := TRUE;
1132 p_segment := c_pos_seg20_rec.segment20;
1133 exit;
1134 end loop;
1135 if not l_pos_seg20_found then
1136 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1137 -- hr_utility.raise_error;
1138 null;
1139 end if;
1140 --
1141 elsif p_information = 'SEGMENT21' then
1142 for c_pos_seg21_rec in c_pos_seg21 (p_id) loop
1143 l_pos_seg21_found := TRUE;
1144 p_segment := c_pos_seg21_rec.segment21;
1145 exit;
1146 end loop;
1147 if not l_pos_seg21_found then
1148 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1149 -- hr_utility.raise_error;
1150 null;
1151 end if;
1152 --
1153 elsif p_information = 'SEGMENT22' then
1154 for c_pos_seg22_rec in c_pos_seg22 (p_id) loop
1155 l_pos_seg22_found := TRUE;
1156 p_segment := c_pos_seg22_rec.segment22;
1157 exit;
1158 end loop;
1159 if not l_pos_seg22_found then
1160 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1161 -- hr_utility.raise_error;
1162 null;
1163 end if;
1164 --
1165 elsif p_information = 'SEGMENT23' then
1166 for c_pos_seg23_rec in c_pos_seg23 (p_id) loop
1167 l_pos_seg23_found := TRUE;
1168 p_segment := c_pos_seg23_rec.segment23;
1169 exit;
1170 end loop;
1171 if not l_pos_seg23_found then
1172 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1173 -- hr_utility.raise_error;
1174 null;
1175 end if;
1176 --
1177 elsif p_information = 'SEGMENT24' then
1178 for c_pos_seg24_rec in c_pos_seg24 (p_id) loop
1179 l_pos_seg24_found := TRUE;
1180 p_segment := c_pos_seg24_rec.segment24;
1181 exit;
1182 end loop;
1183 if not l_pos_seg24_found then
1184 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1185 -- hr_utility.raise_error;
1186 null;
1187 end if;
1188 --
1189 elsif p_information = 'SEGMENT25' then
1190 for c_pos_seg25_rec in c_pos_seg25 (p_id) loop
1191 l_pos_seg25_found := TRUE;
1192 p_segment := c_pos_seg25_rec.segment25;
1193 exit;
1194 end loop;
1195 if not l_pos_seg25_found then
1196 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1197 -- hr_utility.raise_error;
1198 null;
1199 end if;
1200 --
1201 elsif p_information = 'SEGMENT26' then
1202 for c_pos_seg26_rec in c_pos_seg26 (p_id) loop
1203 l_pos_seg26_found := TRUE;
1204 p_segment := c_pos_seg26_rec.segment26;
1205 exit;
1206 end loop;
1207 if not l_pos_seg26_found then
1208 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1209 -- hr_utility.raise_error;
1210 null;
1211 end if;
1212 --
1213 elsif p_information = 'SEGMENT27' then
1214 for c_pos_seg27_rec in c_pos_seg27 (p_id) loop
1215 l_pos_seg27_found := TRUE;
1216 p_segment := c_pos_seg27_rec.segment27;
1217 exit;
1218 end loop;
1219 if not l_pos_seg27_found then
1220 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1221 -- hr_utility.raise_error;
1222 null;
1223 end if;
1224 --
1225 elsif p_information = 'SEGMENT28' then
1226 for c_pos_seg28_rec in c_pos_seg28 (p_id) loop
1227 l_pos_seg28_found := TRUE;
1228 p_segment := c_pos_seg28_rec.segment28;
1229 exit;
1230 end loop;
1231 if not l_pos_seg28_found then
1232 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1233 -- hr_utility.raise_error;
1234 null;
1235 end if;
1236 --
1237 elsif p_information = 'SEGMENT29' then
1238 for c_pos_seg29_rec in c_pos_seg29 (p_id) loop
1239 l_pos_seg29_found := TRUE;
1240 p_segment := c_pos_seg29_rec.segment29;
1241 exit;
1242 end loop;
1243 if not l_pos_seg29_found then
1244 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1245 -- hr_utility.raise_error;
1246 null;
1247 end if;
1248 --
1249 elsif p_information = 'SEGMENT30' then
1250 for c_pos_seg30_rec in c_pos_seg30 (p_id) loop
1251 l_pos_seg30_found := TRUE;
1252 p_segment := c_pos_seg30_rec.segment30;
1253 exit;
1254 end loop;
1255 if not l_pos_seg30_found then
1256 -- hr_utility.set_message(8301, 'GHR_38028_API_INV_POS');
1257 -- hr_utility.raise_error;
1258 null;
1259 end if;
1260 end if; /* p_information */
1261 --
1262 elsif p_code = 'JOB' then
1263 if p_information = 'SEGMENT1' then
1264 for c_job_seg1_rec in c_job_seg1 (p_id) loop
1265 l_job_seg1_found := TRUE;
1266 p_segment := c_job_seg1_rec.segment1;
1267 exit;
1268 end loop;
1269 if not l_job_seg1_found then
1270 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1271 -- hr_utility.raise_error;
1272 null;
1273 end if;
1274 --
1275 elsif p_information = 'SEGMENT2' then
1276 for c_job_seg2_rec in c_job_seg2 (p_id) loop
1277 l_job_seg2_found := TRUE;
1278 p_segment := c_job_seg2_rec.segment2;
1279 exit;
1280 end loop;
1281 if not l_job_seg2_found then
1282 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1283 -- hr_utility.raise_error;
1284 null;
1285 end if;
1286 --
1287 elsif p_information = 'SEGMENT3' then
1288 for c_job_seg3_rec in c_job_seg3 (p_id) loop
1289 l_job_seg3_found := TRUE;
1290 p_segment := c_job_seg3_rec.segment3;
1291 exit;
1292 end loop;
1293 if not l_job_seg3_found then
1294 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1295 -- hr_utility.raise_error;
1296 null;
1297 end if;
1298 --
1299 elsif p_information = 'SEGMENT4' then
1300 for c_job_seg4_rec in c_job_seg4 (p_id) loop
1301 l_job_seg4_found := TRUE;
1302 p_segment := c_job_seg4_rec.segment4;
1303 exit;
1304 end loop;
1305 if not l_job_seg4_found then
1306 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1307 -- hr_utility.raise_error;
1308 null;
1309 end if;
1310 --
1311 elsif p_information = 'SEGMENT5' then
1312 for c_job_seg5_rec in c_job_seg5 (p_id) loop
1313 l_job_seg5_found := TRUE;
1314 p_segment := c_job_seg5_rec.segment5;
1315 exit;
1316 end loop;
1317 if not l_job_seg5_found then
1318 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1319 -- hr_utility.raise_error;
1320 null;
1321 end if;
1322 --
1323 elsif p_information = 'SEGMENT6' then
1324 for c_job_seg6_rec in c_job_seg6 (p_id) loop
1325 l_job_seg6_found := TRUE;
1326 p_segment := c_job_seg6_rec.segment6;
1327 exit;
1328 end loop;
1329 if not l_job_seg6_found then
1330 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1331 -- hr_utility.raise_error;
1332 null;
1333 end if;
1334 --
1335 elsif p_information = 'SEGMENT7' then
1336 for c_job_seg7_rec in c_job_seg7 (p_id) loop
1337 l_job_seg7_found := TRUE;
1338 p_segment := c_job_seg7_rec.segment7;
1339 exit;
1340 end loop;
1341 if not l_job_seg7_found then
1342 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1343 -- hr_utility.raise_error;
1344 null;
1345 end if;
1346 --
1347 elsif p_information = 'SEGMENT8' then
1348 for c_job_seg8_rec in c_job_seg8 (p_id) loop
1349 l_job_seg8_found := TRUE;
1350 p_segment := c_job_seg8_rec.segment8;
1351 exit;
1352 end loop;
1353 if not l_job_seg8_found then
1354 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1355 -- hr_utility.raise_error;
1356 null;
1357 end if;
1358 --
1359 elsif p_information = 'SEGMENT9' then
1360 for c_job_seg9_rec in c_job_seg9 (p_id) loop
1361 l_job_seg9_found := TRUE;
1362 p_segment := c_job_seg9_rec.segment9;
1363 exit;
1364 end loop;
1365 if not l_job_seg9_found then
1366 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1367 -- hr_utility.raise_error;
1368 null;
1369 end if;
1370 --
1371 elsif p_information = 'SEGMENT10' then
1372 for c_job_seg10_rec in c_job_seg10 (p_id) loop
1373 l_job_seg10_found := TRUE;
1374 p_segment := c_job_seg10_rec.segment10;
1375 exit;
1376 end loop;
1377 if not l_job_seg10_found then
1378 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1379 -- hr_utility.raise_error;
1380 null;
1381 end if;
1382 --
1383 elsif p_information = 'SEGMENT11' then
1384 for c_job_seg11_rec in c_job_seg11 (p_id) loop
1385 l_job_seg11_found := TRUE;
1386 p_segment := c_job_seg11_rec.segment11;
1387 exit;
1388 end loop;
1389 if not l_job_seg11_found then
1390 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1391 -- hr_utility.raise_error;
1392 null;
1393 end if;
1394 --
1395 elsif p_information = 'SEGMENT12' then
1396 for c_job_seg12_rec in c_job_seg12 (p_id) loop
1397 l_job_seg12_found := TRUE;
1398 p_segment := c_job_seg12_rec.segment12;
1399 exit;
1400 end loop;
1401 if not l_job_seg12_found then
1402 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1403 -- hr_utility.raise_error;
1404 null;
1405 end if;
1406 --
1407 elsif p_information = 'SEGMENT13' then
1408 for c_job_seg13_rec in c_job_seg13 (p_id) loop
1409 l_job_seg13_found := TRUE;
1410 p_segment := c_job_seg13_rec.segment13;
1411 exit;
1412 end loop;
1413 if not l_job_seg13_found then
1414 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1415 -- hr_utility.raise_error;
1416 null;
1417 end if;
1418 --
1419 elsif p_information = 'SEGMENT14' then
1420 for c_job_seg14_rec in c_job_seg14 (p_id) loop
1421 l_job_seg14_found := TRUE;
1422 p_segment := c_job_seg14_rec.segment14;
1423 exit;
1424 end loop;
1425 if not l_job_seg14_found then
1426 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1427 -- hr_utility.raise_error;
1428 null;
1429 end if;
1430 --
1431 elsif p_information = 'SEGMENT15' then
1432 for c_job_seg15_rec in c_job_seg15 (p_id) loop
1433 l_job_seg15_found := TRUE;
1434 p_segment := c_job_seg15_rec.segment15;
1435 exit;
1436 end loop;
1437 if not l_job_seg15_found then
1438 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1439 -- hr_utility.raise_error;
1440 null;
1441 end if;
1442 --
1443 elsif p_information = 'SEGMENT15' then
1444 for c_job_seg15_rec in c_job_seg15 (p_id) loop
1445 l_job_seg15_found := TRUE;
1446 p_segment := c_job_seg15_rec.segment15;
1447 exit;
1448 end loop;
1449 if not l_job_seg15_found then
1450 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1451 -- hr_utility.raise_error;
1452 null;
1453 end if;
1454 --
1455 elsif p_information = 'SEGMENT16' then
1456 for c_job_seg16_rec in c_job_seg16 (p_id) loop
1457 l_job_seg16_found := TRUE;
1458 p_segment := c_job_seg16_rec.segment16;
1459 exit;
1460 end loop;
1461 if not l_job_seg16_found then
1462 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1463 -- hr_utility.raise_error;
1464 null;
1465 end if;
1466 --
1467 elsif p_information = 'SEGMENT17' then
1468 for c_job_seg17_rec in c_job_seg17 (p_id) loop
1469 l_job_seg17_found := TRUE;
1470 p_segment := c_job_seg17_rec.segment17;
1471 exit;
1472 end loop;
1473 if not l_job_seg17_found then
1474 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1475 -- hr_utility.raise_error;
1476 null;
1477 end if;
1478 --
1479 elsif p_information = 'SEGMENT18' then
1480 for c_job_seg18_rec in c_job_seg18 (p_id) loop
1481 l_job_seg18_found := TRUE;
1482 p_segment := c_job_seg18_rec.segment18;
1483 exit;
1484 end loop;
1485 if not l_job_seg18_found then
1486 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1487 -- hr_utility.raise_error;
1488 null;
1489 end if;
1490 --
1491 elsif p_information = 'SEGMENT19' then
1492 for c_job_seg19_rec in c_job_seg19 (p_id) loop
1493 l_job_seg19_found := TRUE;
1494 p_segment := c_job_seg19_rec.segment19;
1495 exit;
1496 end loop;
1497 if not l_job_seg19_found then
1498 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1499 -- hr_utility.raise_error;
1500 null;
1501 end if;
1502 --
1503 elsif p_information = 'SEGMENT20' then
1504 for c_job_seg20_rec in c_job_seg20 (p_id) loop
1505 l_job_seg20_found := TRUE;
1506 p_segment := c_job_seg20_rec.segment20;
1507 exit;
1508 end loop;
1509 if not l_job_seg20_found then
1510 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1511 -- hr_utility.raise_error;
1512 null;
1513 end if;
1514 --
1515 elsif p_information = 'SEGMENT21' then
1516 for c_job_seg21_rec in c_job_seg21 (p_id) loop
1517 l_job_seg21_found := TRUE;
1518 p_segment := c_job_seg21_rec.segment21;
1519 exit;
1520 end loop;
1521 if not l_job_seg21_found then
1522 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1523 -- hr_utility.raise_error;
1524 null;
1525 end if;
1526 --
1527 elsif p_information = 'SEGMENT22' then
1528 for c_job_seg22_rec in c_job_seg22 (p_id) loop
1529 l_job_seg22_found := TRUE;
1530 p_segment := c_job_seg22_rec.segment22;
1531 exit;
1532 end loop;
1533 if not l_job_seg22_found then
1534 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1535 -- hr_utility.raise_error;
1536 null;
1537 end if;
1538 --
1539 elsif p_information = 'SEGMENT23' then
1540 for c_job_seg23_rec in c_job_seg23 (p_id) loop
1541 l_job_seg23_found := TRUE;
1542 p_segment := c_job_seg23_rec.segment23;
1543 exit;
1544 end loop;
1545 if not l_job_seg23_found then
1546 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1547 -- hr_utility.raise_error;
1548 null;
1549 end if;
1550 --
1551 elsif p_information = 'SEGMENT24' then
1552 for c_job_seg24_rec in c_job_seg24 (p_id) loop
1553 l_job_seg24_found := TRUE;
1554 p_segment := c_job_seg24_rec.segment24;
1555 exit;
1556 end loop;
1557 if not l_job_seg24_found then
1558 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1559 -- hr_utility.raise_error;
1560 null;
1561 end if;
1562 --
1563 elsif p_information = 'SEGMENT25' then
1564 for c_job_seg25_rec in c_job_seg25 (p_id) loop
1565 l_job_seg25_found := TRUE;
1566 p_segment := c_job_seg25_rec.segment25;
1567 exit;
1568 end loop;
1569 if not l_job_seg25_found then
1570 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1571 -- hr_utility.raise_error;
1572 null;
1573 end if;
1574 --
1575 elsif p_information = 'SEGMENT26' then
1576 for c_job_seg26_rec in c_job_seg26 (p_id) loop
1577 l_job_seg26_found := TRUE;
1578 p_segment := c_job_seg26_rec.segment26;
1579 exit;
1580 end loop;
1581 if not l_job_seg26_found then
1582 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1583 -- hr_utility.raise_error;
1584 null;
1585 end if;
1586 --
1587 elsif p_information = 'SEGMENT27' then
1588 for c_job_seg27_rec in c_job_seg27 (p_id) loop
1589 l_job_seg27_found := TRUE;
1590 p_segment := c_job_seg27_rec.segment27;
1591 exit;
1592 end loop;
1593 if not l_job_seg27_found then
1594 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1595 -- hr_utility.raise_error;
1596 null;
1597 end if;
1598 --
1599 elsif p_information = 'SEGMENT28' then
1600 for c_job_seg28_rec in c_job_seg28 (p_id) loop
1601 l_job_seg28_found := TRUE;
1602 p_segment := c_job_seg28_rec.segment28;
1603 exit;
1604 end loop;
1605 if not l_job_seg28_found then
1606 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1607 -- hr_utility.raise_error;
1608 null;
1609 end if;
1610 --
1611 elsif p_information = 'SEGMENT29' then
1612 for c_job_seg29_rec in c_job_seg29 (p_id) loop
1613 l_job_seg29_found := TRUE;
1614 p_segment := c_job_seg29_rec.segment29;
1615 exit;
1616 end loop;
1617 if not l_job_seg29_found then
1618 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1619 -- hr_utility.raise_error;
1620 null;
1621 end if;
1622 --
1623 elsif p_information = 'SEGMENT30' then
1624 for c_job_seg30_rec in c_job_seg30 (p_id) loop
1625 l_job_seg30_found := TRUE;
1626 p_segment := c_job_seg30_rec.segment30;
1627 exit;
1628 end loop;
1629 if not l_job_seg30_found then
1630 -- hr_utility.set_message(8301, 'GHR_38029_API_INV_JOB');
1631 -- hr_utility.raise_error;
1632 null;
1633 end if;
1634 end if; /* p_information */
1635 --
1636 else -- Invalid p_code
1637 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
1638 -- hr_utility.raise_error;
1639 null;
1640 end if;
1641 --
1642 exception when others then
1643 -- NOCOPY changes
1644 -- Reset IN OUT params and set OUT params
1645 p_segment := NULL;
1646 raise;
1647 end retrieve_segment_information;
1648 --
1649 -- ---------------------------------------------------------------------------
1650 -- |-------------------< retrieve_element_entry_value >----------------------|
1651 -- ---------------------------------------------------------------------------
1652 --
1653 procedure retrieve_element_entry_value
1654 (p_element_name in pay_element_types_f.element_name%type
1655 ,p_input_value_name in pay_input_values_f.name%type
1656 ,p_assignment_id in pay_element_entries_f.assignment_id%type
1657 ,p_effective_date in date
1658 ,p_value OUT NOCOPY varchar2
1659 ,p_multiple_error_flag OUT NOCOPY boolean
1660 ) is
1661 --
1662 l_proc varchar2(72) := g_package||'retrieve_element_entry_value';
1663 l_processing_type pay_element_types_f.processing_type%type;
1664 l_ele_proc_type_found boolean := FALSE;
1665 l_rec_ele_ent_val_found boolean := FALSE;
1666 l_nonrec_ele_ent_val_found boolean := FALSE;
1667 l_session ghr_history_api.g_session_var_type;
1668 --
1669 cursor c_ele_processing_type (ele_name in varchar2
1670 ,eff_date in date
1671 ,bg_id in number
1672 ) is
1673 select elt.processing_type
1674 from pay_element_types_f elt
1675 where trunc(eff_date) between elt.effective_start_date
1676 and elt.effective_end_date
1677 and upper(elt.element_name) = upper(ele_name)
1678 --Ashley
1679 and (elt.business_group_id is null or elt.business_group_id = bg_id);
1680 --
1681 cursor c_rec_ele_entry_value (ele_name in varchar2
1682 ,input_name in varchar2
1683 ,asg_id in number
1684 ,eff_date in date
1685 ,bg_id in number) is
1686 select eev.screen_entry_value screen_entry_value
1687 from pay_element_types_f elt,
1688 pay_input_values_f ipv,
1689 pay_element_entries_f ele,
1690 pay_element_entry_values_f eev
1691 where trunc(eff_date) between elt.effective_start_date
1692 and elt.effective_end_date
1693 and trunc(eff_date) between ipv.effective_start_date
1694 and ipv.effective_end_date
1695 and trunc(eff_date) between ele.effective_start_date
1696 and ele.effective_end_date
1697 and trunc(eff_date) between eev.effective_start_date
1698 and eev.effective_end_date
1699 and elt.element_type_id = ipv.element_type_id
1700 and upper(elt.element_name) = upper(ele_name)
1701 and ipv.input_value_id = eev.input_value_id
1702 and ele.assignment_id = asg_id
1703 and ele.element_entry_id + 0 = eev.element_entry_id
1704 and upper(ipv.name) = upper(input_name)
1705 -- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0) --Ashley
1706 and (elt.business_group_id is null or elt.business_group_id = bg_id);
1707 --
1708 cursor c_nonrec_ele_entry_value (ele_name in varchar2
1709 ,input_name in varchar2
1710 ,asg_id in number
1711 ,eff_date in date
1712 ,bg_id in number) is
1713 select eev.screen_entry_value screen_entry_value
1714 from pay_element_types_f elt,
1715 pay_input_values_f ipv,
1716 pay_element_entries_f ele,
1717 pay_element_entry_values_f eev
1718 where trunc(eff_date) between elt.effective_start_date
1719 and elt.effective_end_date
1720 and trunc(eff_date) between ipv.effective_start_date
1721 and ipv.effective_end_date
1722 and ele.effective_end_date =
1723 (select max(ele2.effective_end_date)
1724 from pay_element_entries_f ele2
1725 where ele2.element_entry_id = ele.element_entry_id)
1726 and eev.effective_end_date =
1727 (select max(eev2.effective_end_date)
1728 from pay_element_entries_f eev2
1729 where eev2.element_entry_id = eev.element_entry_id)
1730 and elt.element_type_id = ipv.element_type_id
1731 and upper(elt.element_name) = upper(ele_name)
1732 and ipv.input_value_id = eev.input_value_id
1733 and ele.assignment_id = asg_id
1734 and ele.element_entry_id + 0 = eev.element_entry_id
1735 and upper(ipv.name) = upper(input_name)
1736 -- and NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0) --Ashley
1737 and (elt.business_group_id is null or elt.business_group_id = bg_id);
1738 --
1739 --
1740 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
1741 Select distinct business_group_id bg
1742 from per_assignments_f
1743 where assignment_id = p_assignment_id
1744 and p_eff_date between effective_start_date
1745 and effective_end_date;
1746
1747 --
1748 --
1749 ll_bg_id NUMBER;
1750 ll_pay_basis VARCHAR2(80);
1751 l_new_element_name VARCHAR2(80);
1752 ll_effective_Date DATE;
1753 l_ele_type_id NUMBER;
1754 l_ele_name VARCHAR2(80);
1755 l_sal_basis_id NUMBER;
1756
1757 --
1758 begin
1759 --
1760 hr_utility.set_location('Entering:'||l_proc, 1);
1761 ghr_history_api.get_g_session_var(l_session);
1762 ll_effective_date := p_effective_date;
1763 --
1764 -- Initialization
1765 -- Pick the business group id and also pay basis for later use
1766 hr_utility.set_location('Asg Id'||to_char(p_assignment_id), 20);
1767
1768 For BG_rec in Cur_BG(p_assignment_id,p_effective_date)
1769 Loop
1770 ll_bg_id:=BG_rec.bg;
1771 End Loop;
1772
1773 ----
1774 ---- The New Changes after 08/22 patch
1775 ---- For all elements in HR User old function will fetch the same name.
1776 ---- because of is_script will be FALSE
1777 ----
1778 ---- For all elements (except BSR) in Payroll user old function.
1779 ---- for BSR a new function which will fetch from assignmnet id.
1780 ----
1781
1782 IF (p_element_name = 'Basic Salary Rate'
1783 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
1784 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- '||l_proc, 1);
1785 l_new_element_name :=
1786 pqp_fedhr_uspay_int_utils.return_new_element_name(
1787 p_assignment_id => p_assignment_id,
1788 p_business_group_id => ll_bg_id,
1789 p_effective_date => ll_effective_date);
1790 ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
1791 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
1792 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- '||l_proc, 1);
1793 l_new_element_name :=
1794 pqp_fedhr_uspay_int_utils.return_new_element_name(
1795 p_fedhr_element_name => p_element_name,
1796 p_business_group_id => ll_bg_id,
1797 p_effective_date => ll_effective_date,
1798 p_pay_basis => NULL);
1799
1800 END IF;
1801
1802 hr_utility.set_location('Element Name ' ||p_element_name,1000);
1803 hr_utility.set_location('BG ID '|| nvl(to_char(ll_bg_id),'NULL'),2000);
1804 hr_utility.set_location('Eff date'|| p_effective_date ,3000);
1805 hr_utility.set_location('pay basis ' || ll_pay_basis,3500);
1806 --
1807 -- the p_element_name is replaced with l_new_element_name
1808 -- in further calls.
1809 --
1810 hr_utility.set_location('New element Name ' ||l_new_element_name,100000);
1811
1812 hr_utility.set_location(l_proc,2);
1813
1814 If l_session.noa_id_correct is not null then
1815 hr_utility.set_location(l_proc,3);
1816
1817 -- History package call fetch_element_entry_value picks new element name
1818 -- again in its call so sending old element name.
1819 ghr_history_fetch.fetch_element_entry_value
1820 (p_element_name => p_element_name,
1821 p_input_value_name => p_input_value_name,
1822 p_assignment_id => p_assignment_id,
1823 p_date_effective => p_effective_date,
1824 p_screen_entry_value => p_value
1825 );
1826 Else
1827 hr_utility.set_location(l_proc,4);
1828 -- sending new element name for cursors to get proper element_type_id's,
1829 -- Processing_types, element_entry_id's
1830 for c_ele_processing_type_rec in
1831 c_ele_processing_type (l_new_element_name
1832 ,p_effective_date
1833 ,ll_bg_id) loop
1834 l_ele_proc_type_found := TRUE;
1835 l_processing_type := c_ele_processing_type_rec.processing_type;
1836 exit;
1837 end loop;
1838 if not l_ele_proc_type_found then
1839 hr_utility.set_message(8301, 'GHR_38034_API_EL_TYPE_NOT_EXST');
1840 hr_utility.raise_error;
1841 end if;
1842 hr_utility.set_location(l_proc, 5);
1843 --
1844 if l_processing_type = 'R' then -- Recurring element
1845 for c_rec_ele_entry_value_rec in
1846 c_rec_ele_entry_value (l_new_element_name
1847 ,p_input_value_name
1848 ,p_assignment_id
1849 ,p_effective_date
1850 ,ll_bg_id) loop
1851 l_rec_ele_ent_val_found := TRUE;
1852 if c_rec_ele_entry_value%rowcount > 1 then
1853 p_multiple_error_flag := TRUE;
1854 p_value := NULL;
1855 exit;
1856 else
1857 p_multiple_error_flag := FALSE;
1858 p_value := c_rec_ele_entry_value_rec.screen_entry_value;
1859 end if;
1860 end loop;
1861 if not l_rec_ele_ent_val_found then
1862 -- hr_utility.set_message(8301, 'GHR_38036_API_EL_ENT_NOT_EXIST');
1863 -- hr_utility.raise_error;
1864 p_value := NULL;
1865 end if;
1866 elsif l_processing_type = 'N' then -- Nonrecurring element
1867 for c_nonrec_ele_entry_value_rec in
1868 c_nonrec_ele_entry_value (l_new_element_name
1869 ,p_input_value_name
1870 ,p_assignment_id
1871 ,p_effective_date
1872 ,ll_bg_id) loop
1873 l_nonrec_ele_ent_val_found := TRUE;
1874 if c_nonrec_ele_entry_value%rowcount > 1 then
1875 p_multiple_error_flag := TRUE;
1876 p_value := NULL;
1877 exit;
1878 else
1879 p_multiple_error_flag := FALSE;
1880 p_value := c_nonrec_ele_entry_value_rec.screen_entry_value;
1881 end if;
1882 end loop;
1883 if not l_nonrec_ele_ent_val_found then
1884 -- hr_utility.set_message(8301, 'GHR_38036_API_EL_ENT_NOT_EXIST');
1885 -- hr_utility.raise_error;
1886 p_value := NULL;
1887 end if;
1888 hr_utility.set_location(l_proc, 6);
1889 else -- Neither recurring nor nonrecurring element
1890 hr_utility.set_message(8301, 'GHR_38035_API_INV_PROC_TYPE');
1891 hr_utility.raise_error;
1892 end if;
1893 hr_utility.set_location(l_proc, 7);
1894 --
1895 hr_utility.set_location(' Leaving:'||l_proc, 8);
1896 --
1897 End If;
1898 exception
1899 when others then
1900 -- NOCOPY changes
1901 -- Reset IN OUT params and set OUT params
1902
1903 if p_element_name in ('AUO','Availability Pay','Staffing Differential',
1904 'Health Benefits') then
1905 p_value := null;
1906 p_multiple_error_flag := null;
1907 else
1908 hr_utility.set_message(800, 'HR_7465_PLK_NOT_ELGBLE_ELE_NME');
1909 hr_utility.set_message_token('ELEMENT_NAME', p_element_name);
1910 hr_utility.raise_error;
1911
1912 end if;
1913 hr_utility.set_location(' Leaving:'||l_proc, 8);
1914
1915 end retrieve_element_entry_value;
1916 --
1917 -- ---------------------------------------------------------------------------
1918 -- |--------------------------< get_position_title >-------------------------|
1919 -- ---------------------------------------------------------------------------
1920 --
1921 function get_position_title
1922 (p_person_id in per_people_f.person_id%type default NULL
1923 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
1924 ,p_effective_date in date default sysdate
1925 ) return varchar2 is
1926 --
1927 l_proc varchar2(72) := g_package||'get_position_title';
1928 l_effective_date date;
1929 l_business_group_id per_all_positions.business_group_id%type;
1930 l_position_id per_all_positions.position_id%type;
1931 l_segment_rec org_info_rec_type;
1932 l_segment per_position_definitions.segment1%type;
1933 l_asg_pos_by_per_id_found boolean := FALSE;
1934 l_asg_pos_by_asg_id_found boolean := FALSE;
1935 --
1936 begin
1937 -- hr_utility.set_location('Entering:'||l_proc, 1);
1938 --
1939 if p_effective_date is NULL then
1940 l_effective_date := sysdate;
1941 else
1942 l_effective_date := p_effective_date;
1943 end if;
1944 --
1945 retrieve_business_group_id (p_person_id => p_person_id
1946 ,p_assignment_id => p_assignment_id
1947 ,p_effective_date => l_effective_date
1948 ,p_business_group_id => l_business_group_id);
1949 if l_business_group_id is NULL then
1950 -- hr_utility.set_message(8301, 'GHR_38038_API_INV_BG');
1951 -- hr_utility.raise_error;
1952 null;
1953 end if;
1954 -- hr_utility.set_location(l_proc, 2);
1955 --
1956 retrieve_gov_kff_setup_info (p_business_group_id => l_business_group_id
1957 ,p_org_info_rec => l_segment_rec);
1958 if l_segment_rec.information2 is NULL then
1959 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
1960 -- hr_utility.raise_error;
1961 null;
1962 end if;
1963 -- hr_utility.set_location(l_proc, 3);
1964 --
1965 IF p_person_id is NULL and p_assignment_id is NULL THEN
1966 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
1967 -- hr_utility.raise_error;
1968 null;
1969 ELSIF p_assignment_id IS NULL THEN
1970 FOR c_asg_pos_by_per_id_rec IN c_asg_pos_by_per_id (p_person_id, l_effective_date) loop
1971 l_asg_pos_by_per_id_found := TRUE;
1972 l_position_id := c_asg_pos_by_per_id_rec.position_id;
1973 EXIT;
1974 END LOOP;
1975 IF NOT l_asg_pos_by_per_id_found THEN
1976 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
1977 -- hr_utility.raise_error;
1978 null;
1979 END IF;
1980 else -- p_assignment_id is not NULL
1981 for c_asg_pos_by_asg_id_rec
1982 in c_asg_pos_by_asg_id (p_assignment_id, l_effective_date) loop
1983 l_asg_pos_by_asg_id_found := TRUE;
1984 l_position_id := c_asg_pos_by_asg_id_rec.position_id;
1985 exit;
1986 end loop;
1987 if not l_asg_pos_by_asg_id_found then
1988 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
1989 -- hr_utility.raise_error;
1990 null;
1991 end if;
1992 end if;
1993 -- hr_utility.set_location(l_proc, 4);
1994 --
1995 retrieve_segment_information (p_information => l_segment_rec.information2
1996 ,p_id => l_position_id
1997 ,p_code => 'POS'
1998 ,p_segment => l_segment
1999 ,p_effective_date => p_effective_date);
2000 --
2001 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2002 return l_segment;
2003 --
2004 end get_position_title;
2005 --
2006 -- ---------------------------------------------------------------------------
2007 -- |--------------------------< get_position_title_pos >----------------------|
2008 -- ---------------------------------------------------------------------------
2009 --
2010 function get_position_title_pos
2011 (p_position_id in per_all_positions.position_id%type
2012 ,p_business_group_id in per_all_positions.business_group_id%type
2013 ,p_effective_date in date default sysdate
2014 ) return varchar2 IS
2015 --
2016 l_proc varchar2(72) := g_package||'get_position_title_pos';
2017 l_segment_rec org_info_rec_type;
2018 l_segment per_position_definitions.segment1%type;
2019 --
2020 begin
2021 -- hr_utility.set_location('Entering:'||l_proc, 1);
2022 --
2023 if p_position_id IS NULL OR p_business_group_id IS NULL then
2024 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2025 -- hr_utility.raise_error;
2026 null;
2027 end if;
2028 --
2029 -- hr_utility.set_location(l_proc, 2);
2030 --
2031 retrieve_gov_kff_setup_info (p_business_group_id => p_business_group_id
2032 ,p_org_info_rec => l_segment_rec);
2033 --
2034 if l_segment_rec.information2 is NULL then
2035 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2036 -- hr_utility.raise_error;
2037 null;
2038 end if;
2039 -- hr_utility.set_location(l_proc, 3);
2040 --
2041 retrieve_segment_information (p_information => l_segment_rec.information2
2042 ,p_id => p_position_id
2043 ,p_code => 'POS'
2044 ,p_effective_date => p_effective_date
2045 ,p_segment => l_segment);
2046 --
2047 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2048 return l_segment;
2049 --
2050 end get_position_title_pos;
2051 --
2052 -- ---------------------------------------------------------------------------
2053 -- |---------------------< get_position_description_no >---------------------|
2054 -- ---------------------------------------------------------------------------
2055 --
2056 function get_position_description_no
2057 (p_person_id in per_people_f.person_id%type default NULL
2058 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
2059 ,p_effective_date in date default sysdate
2060 ) return varchar2 is
2061 --
2062 l_proc varchar2(72) := g_package||'get_position_description_no';
2063 l_effective_date date;
2064 l_business_group_id per_all_positions.business_group_id%type;
2065 l_position_id per_all_positions.position_id%type;
2066 l_segment_rec org_info_rec_type;
2067 l_segment per_position_definitions.segment1%type;
2068 l_asg_pos_by_per_id_found boolean := FALSE;
2069 l_asg_pos_by_asg_id_found boolean := FALSE;
2070 --
2071 begin
2072 -- hr_utility.set_location('Entering:'||l_proc, 1);
2073 --
2074 if p_effective_date is NULL then
2075 l_effective_date := sysdate;
2076 else
2077 l_effective_date := p_effective_date;
2078 end if;
2079 --
2080 retrieve_business_group_id (p_person_id => p_person_id
2081 ,p_assignment_id => p_assignment_id
2082 ,p_effective_date => l_effective_date
2083 ,p_business_group_id => l_business_group_id);
2084 if l_business_group_id is NULL then
2085 -- hr_utility.set_message(8301, 'GHR_38038_API_INV_BG');
2086 -- hr_utility.raise_error;
2087 null;
2088 end if;
2089 -- hr_utility.set_location(l_proc, 2);
2090 --
2091 if p_person_id is NULL and p_assignment_id is NULL then
2092 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2093 -- hr_utility.raise_error;
2094 null;
2095 elsif p_assignment_id is NULL then
2096 for c_asg_pos_by_per_id_rec
2097 in c_asg_pos_by_per_id (p_person_id, l_effective_date) loop
2098 l_asg_pos_by_per_id_found := TRUE;
2099 l_position_id := c_asg_pos_by_per_id_rec.position_id;
2100 exit;
2101 end loop;
2102 if not l_asg_pos_by_per_id_found then
2103 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2104 -- hr_utility.raise_error;
2105 null;
2106 end if;
2107 else -- p_assignment_id is not NULL
2108 for c_asg_pos_by_asg_id_rec
2109 in c_asg_pos_by_asg_id (p_assignment_id, l_effective_date) loop
2110 l_asg_pos_by_asg_id_found := TRUE;
2111 l_position_id := c_asg_pos_by_asg_id_rec.position_id;
2112 exit;
2113 end loop;
2114 if not l_asg_pos_by_asg_id_found then
2115 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2116 -- hr_utility.raise_error;
2117 null;
2118 end if;
2119 end if;
2120 -- hr_utility.set_location(l_proc, 3);
2121 --
2122 retrieve_gov_kff_setup_info (p_business_group_id => l_business_group_id
2123 ,p_org_info_rec => l_segment_rec);
2124 if l_segment_rec.information3 is NULL then
2125 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2126 -- hr_utility.raise_error;
2127 null;
2128 end if;
2129 -- hr_utility.set_location(l_proc, 4);
2130 --
2131 retrieve_segment_information (p_information => l_segment_rec.information3
2132 ,p_id => l_position_id
2133 ,p_code => 'POS'
2134 ,p_segment => l_segment
2135 ,p_effective_date => p_effective_date);
2136 --
2137 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2138 --
2139 return l_segment;
2140 --
2141 end get_position_description_no;
2142 --
2143 -- ---------------------------------------------------------------------------
2144 -- |--------------------------< get_position_desc_no_pos >----------------------|
2145 -- ---------------------------------------------------------------------------
2146 --
2147 function get_position_desc_no_pos
2148 (p_position_id in per_all_positions.position_id%type
2149 ,p_business_group_id in per_all_positions.business_group_id%type
2150 ,p_effective_date in date default sysdate
2151 ) return varchar2 IS
2152 --
2153 l_proc varchar2(72) := g_package||'get_position_desc_no_pos';
2154 l_segment_rec org_info_rec_type;
2155 l_segment per_position_definitions.segment1%type;
2156 --
2157 begin
2158 -- hr_utility.set_location('Entering:'||l_proc, 1);
2159 --
2160 if p_position_id IS NULL OR p_business_group_id IS NULL then
2161 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2162 -- hr_utility.raise_error;
2163 null;
2164 end if;
2165 --
2166 -- hr_utility.set_location(l_proc, 2);
2167 --
2168 retrieve_gov_kff_setup_info (p_business_group_id => p_business_group_id
2169 ,p_org_info_rec => l_segment_rec);
2170 --
2171 if l_segment_rec.information3 is NULL then
2172 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2173 -- hr_utility.raise_error;
2174 null;
2175 end if;
2176 -- hr_utility.set_location(l_proc, 3);
2177 --
2178 retrieve_segment_information (p_information => l_segment_rec.information3
2179 ,p_id => p_position_id
2180 ,p_code => 'POS'
2181 ,p_segment => l_segment
2182 ,p_effective_date => p_effective_date);
2183 --
2184 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2185 return l_segment;
2186 --
2187 end get_position_desc_no_pos;
2188 --
2189 -- ---------------------------------------------------------------------------
2190 -- |----------------------< get_position_sequence_no >-----------------------|
2191 -- ---------------------------------------------------------------------------
2192 --
2193 function get_position_sequence_no
2194 (p_person_id in per_people_f.person_id%type default NULL
2195 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
2196 ,p_effective_date in date default sysdate
2197 ) return varchar2 is
2198 --
2199 l_proc varchar2(72) := g_package||'get_position_sequence_no';
2200 l_effective_date date;
2201 l_business_group_id per_all_positions.business_group_id%type;
2202 l_position_id per_all_positions.position_id%type;
2203 l_segment_rec org_info_rec_type;
2204 l_segment per_position_definitions.segment1%type;
2205 l_asg_pos_by_per_id_found boolean := FALSE;
2206 l_asg_pos_by_asg_id_found boolean := FALSE;
2207 --
2208 begin
2209 -- hr_utility.set_location('Entering:'||l_proc, 1);
2210 --
2211 if p_effective_date is NULL then
2212 l_effective_date := sysdate;
2213 else
2214 l_effective_date := p_effective_date;
2215 end if;
2216 --
2217 retrieve_business_group_id (p_person_id => p_person_id
2218 ,p_assignment_id => p_assignment_id
2219 ,p_effective_date => l_effective_date
2220 ,p_business_group_id => l_business_group_id);
2221 if l_business_group_id is NULL then
2222 -- hr_utility.set_message(8301, 'GHR_38038_API_INV_BG');
2223 -- hr_utility.raise_error;
2224 null;
2225 end if;
2226 -- hr_utility.set_location(l_proc, 2);
2227 --
2228 if p_person_id is NULL and p_assignment_id is NULL then
2229 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2230 -- hr_utility.raise_error;
2231 null;
2232 elsif p_assignment_id is NULL then
2233 for c_asg_pos_by_per_id_rec
2234 in c_asg_pos_by_per_id (p_person_id, l_effective_date) loop
2235 l_asg_pos_by_per_id_found := TRUE;
2236 l_position_id := c_asg_pos_by_per_id_rec.position_id;
2237 exit;
2238 end loop;
2239 if not l_asg_pos_by_per_id_found then
2240 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2241 -- hr_utility.raise_error;
2242 null;
2243 end if;
2244 else -- p_assignment_id is not NULL
2245 for c_asg_pos_by_asg_id_rec
2246 in c_asg_pos_by_asg_id (p_assignment_id, l_effective_date) loop
2247 l_asg_pos_by_asg_id_found := TRUE;
2248 l_position_id := c_asg_pos_by_asg_id_rec.position_id;
2249 exit;
2250 end loop;
2251 if not l_asg_pos_by_asg_id_found then
2252 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2253 -- hr_utility.raise_error;
2254 null;
2255 end if;
2256 end if;
2257 -- hr_utility.set_location(l_proc, 3);
2258 --
2259 retrieve_gov_kff_setup_info (p_business_group_id => l_business_group_id
2260 ,p_org_info_rec => l_segment_rec);
2261 if l_segment_rec.information4 is NULL then
2262 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2263 -- hr_utility.raise_error;
2264 null;
2265 end if;
2266 -- hr_utility.set_location(l_proc, 4);
2267 --
2268 retrieve_segment_information (p_information => l_segment_rec.information4
2269 ,p_id => l_position_id
2270 ,p_code => 'POS'
2271 ,p_effective_date => p_effective_date
2272 ,p_segment => l_segment);
2273 --
2274 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2275 --
2276 return l_segment;
2277 --
2278 end get_position_sequence_no;
2279 --
2280 -- ---------------------------------------------------------------------------
2281 -- |--------------------------< get_position_sequence_no_pos >----------------|
2282 -- ---------------------------------------------------------------------------
2283 --
2284 function get_position_sequence_no_pos
2285 (p_position_id in per_all_positions.position_id%type
2286 ,p_business_group_id in per_all_positions.business_group_id%type
2287 ,p_effective_date in date default sysdate
2288 ) return varchar2 IS
2289 --
2290 l_proc varchar2(72) := g_package||'get_position_desc_no_pos';
2291 l_segment_rec org_info_rec_type;
2292 l_segment per_position_definitions.segment1%type;
2293 --
2294 begin
2295 -- hr_utility.set_location('Entering:'||l_proc, 1);
2296 --
2297 if p_position_id IS NULL OR p_business_group_id IS NULL then
2298 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2299 -- hr_utility.raise_error;
2300 null;
2301 end if;
2302 --
2303 -- hr_utility.set_location(l_proc, 2);
2304 --
2305 retrieve_gov_kff_setup_info (p_business_group_id => p_business_group_id
2306 ,p_org_info_rec => l_segment_rec);
2307 --
2308 if l_segment_rec.information4 is NULL then
2309 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2310 -- hr_utility.raise_error;
2311 null;
2312 end if;
2313 -- hr_utility.set_location(l_proc, 3);
2314 --
2315 retrieve_segment_information (p_information => l_segment_rec.information4
2316 ,p_id => p_position_id
2317 ,p_code => 'POS'
2318 ,p_effective_date => p_effective_date
2319 ,p_segment => l_segment);
2320 --
2321 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2322 return l_segment;
2323 --
2324 end get_position_sequence_no_pos;
2325 --
2326 -- ---------------------------------------------------------------------------
2327 -- |----------------------< get_position_agency_code >-----------------------|
2328 -- ---------------------------------------------------------------------------
2329 --
2330 function get_position_agency_code
2331 (p_person_id in per_people_f.person_id%type default NULL
2332 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
2333 ,p_effective_date in date default sysdate
2334 ) return varchar2 is
2335 --
2336 l_proc varchar2(72) := g_package||'get_position_agency_code';
2337 l_effective_date date;
2338 l_business_group_id per_all_positions.business_group_id%type;
2339 l_position_id per_all_positions.position_id%type;
2340 l_segment_rec org_info_rec_type;
2341 l_segment per_position_definitions.segment1%type;
2342 l_asg_pos_by_per_id_found boolean := FALSE;
2343 l_asg_pos_by_asg_id_found boolean := FALSE;
2344 --
2345 begin
2346 -- hr_utility.set_location('Entering:'||l_proc, 1);
2347 --
2348 if p_effective_date is NULL then
2349 l_effective_date := sysdate;
2350 else
2351 l_effective_date := p_effective_date;
2352 end if;
2353 --
2354 retrieve_business_group_id (p_person_id => p_person_id
2355 ,p_assignment_id => p_assignment_id
2356 ,p_effective_date => l_effective_date
2357 ,p_business_group_id => l_business_group_id);
2358 if l_business_group_id is NULL then
2359 -- hr_utility.set_message(8301, 'GHR_38038_API_INV_BG');
2360 -- hr_utility.raise_error;
2361 null;
2362 end if;
2363 -- hr_utility.set_location(l_proc, 2);
2364 --
2365 if p_person_id is NULL and p_assignment_id is NULL then
2366 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2367 -- hr_utility.raise_error;
2368 null;
2369 elsif p_assignment_id is NULL then
2370 for c_asg_pos_by_per_id_rec
2371 in c_asg_pos_by_per_id (p_person_id, l_effective_date) loop
2372 l_asg_pos_by_per_id_found := TRUE;
2373 l_position_id := c_asg_pos_by_per_id_rec.position_id;
2374 exit;
2375 end loop;
2376 if not l_asg_pos_by_per_id_found then
2377 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2378 -- hr_utility.raise_error;
2379 null;
2380 end if;
2381 else -- p_assignment_id is not NULL
2382 for c_asg_pos_by_asg_id_rec
2383 in c_asg_pos_by_asg_id (p_assignment_id, l_effective_date) loop
2384 l_asg_pos_by_asg_id_found := TRUE;
2385 l_position_id := c_asg_pos_by_asg_id_rec.position_id;
2386 exit;
2387 end loop;
2388 if not l_asg_pos_by_asg_id_found then
2389 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2390 -- hr_utility.raise_error;
2391 null;
2392 end if;
2393 end if;
2394 -- hr_utility.set_location(l_proc, 3);
2395 --
2396 retrieve_gov_kff_setup_info (p_business_group_id => l_business_group_id
2397 ,p_org_info_rec => l_segment_rec);
2398 if l_segment_rec.information5 is NULL then
2399 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2400 -- hr_utility.raise_error;
2401 null;
2402 end if;
2403 -- hr_utility.set_location(l_proc, 4);
2404 --
2405 retrieve_segment_information (p_information => l_segment_rec.information5
2406 ,p_id => l_position_id
2407 ,p_code => 'POS'
2408 ,p_effective_date => p_effective_date
2409 ,p_segment => l_segment);
2410 --
2411 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2412 --
2413 return l_segment;
2414 --
2415 end get_position_agency_code;
2416 --
2417 -- ---------------------------------------------------------------------------
2418 -- |--------------------------< get_position_agency_code_pos >----------------|
2419 -- ---------------------------------------------------------------------------
2420 --
2421 function get_position_agency_code_pos
2422 (p_position_id in per_all_positions.position_id%type
2423 ,p_business_group_id in per_all_positions.business_group_id%type
2424 ,p_effective_date in date default sysdate
2425 ) return varchar2 IS
2426 --
2427 l_proc varchar2(72) := g_package||'get_position_agency_code_pos';
2428 l_segment_rec org_info_rec_type;
2429 l_segment per_position_definitions.segment1%type;
2430 --
2431 begin
2432 -- hr_utility.set_location('Entering:'||l_proc, 1);
2433 --
2434 if p_position_id IS NULL OR p_business_group_id IS NULL then
2435 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2436 -- hr_utility.raise_error;
2437 null;
2438 end if;
2439 --
2440 -- hr_utility.set_location(l_proc, 2);
2441 --
2442 retrieve_gov_kff_setup_info (p_business_group_id => p_business_group_id
2443 ,p_org_info_rec => l_segment_rec);
2444 --
2445 if l_segment_rec.information5 is NULL then
2446 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2447 -- hr_utility.raise_error;
2448 null;
2449 end if;
2450 -- hr_utility.set_location(l_proc, 3);
2451 --
2452 retrieve_segment_information (p_information => l_segment_rec.information5
2453 ,p_id => p_position_id
2454 ,p_code => 'POS'
2455 ,p_segment => l_segment
2456 ,p_effective_date => p_effective_date);
2457 --
2458 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2459 return l_segment;
2460 --
2461 end get_position_agency_code_pos;
2462 --
2463 -- ---------------------------------------------------------------------------
2464 -- |---------------------< get_job_occupational_series >---------------------|
2465 -- ---------------------------------------------------------------------------
2466 --
2467 function get_job_occupational_series
2468 (p_person_id in per_people_f.person_id%type default NULL
2469 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
2470 ,p_effective_date in date default sysdate
2471 ) return varchar2 is
2472 --
2473 l_proc varchar2(72) := g_package||'get_job_occupational_series';
2474 l_effective_date date;
2475 l_business_group_id per_all_positions.business_group_id%type;
2476 l_job_id per_jobs.job_id%type;
2477 l_segment_rec org_info_rec_type;
2478 l_segment per_position_definitions.segment1%type;
2479 l_asg_job_by_per_id_found boolean := FALSE;
2480 l_asg_job_by_asg_id_found boolean := FALSE;
2481 --
2482 begin
2483 -- hr_utility.set_location('Entering:'||l_proc, 1);
2484 --
2485 if p_effective_date is NULL then
2486 l_effective_date := sysdate;
2487 else
2488 l_effective_date := p_effective_date;
2489 end if;
2490 --
2491 retrieve_business_group_id (p_person_id => p_person_id
2492 ,p_assignment_id => p_assignment_id
2493 ,p_effective_date => l_effective_date
2494 ,p_business_group_id => l_business_group_id);
2495 if l_business_group_id is NULL then
2496 -- hr_utility.set_message(8301, 'GHR_38038_API_INV_BG');
2497 -- hr_utility.raise_error;
2498 null;
2499 end if;
2500 -- hr_utility.set_location(l_proc, 2);
2501 --
2502 if p_person_id is NULL and p_assignment_id is NULL then
2503 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2504 -- hr_utility.raise_error;
2505 null;
2506 elsif p_assignment_id is NULL then
2507
2508 for c_asg_job_by_per_id_rec
2509 in c_asg_job_by_per_id (p_person_id, l_effective_date) loop
2510 l_asg_job_by_per_id_found := TRUE;
2511 l_job_id := c_asg_job_by_per_id_rec.job_id;
2512 exit;
2513 end loop;
2514 if not l_asg_job_by_per_id_found then
2515 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2516 -- hr_utility.raise_error;
2517 null;
2518 end if;
2519 else -- p_assignment_id is not NULL
2520 for c_asg_job_by_asg_id_rec
2521 in c_asg_job_by_asg_id (p_assignment_id, l_effective_date) loop
2522 l_asg_job_by_asg_id_found := TRUE;
2523 l_job_id := c_asg_job_by_asg_id_rec.job_id;
2524 exit;
2525 end loop;
2526 if not l_asg_job_by_asg_id_found then
2527 -- hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
2528 -- hr_utility.raise_error;
2529 null;
2530 end if;
2531 end if;
2532 -- hr_utility.set_location(l_proc, 3);
2533 --
2534 retrieve_gov_kff_setup_info (p_business_group_id => l_business_group_id
2535 ,p_org_info_rec => l_segment_rec);
2536 if l_segment_rec.information1 is NULL then
2537 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2538 -- hr_utility.raise_error;
2539 null;
2540 end if;
2541 -- hr_utility.set_location(l_proc, 4);
2542 --
2543 retrieve_segment_information (p_information => l_segment_rec.information1
2544 ,p_id => l_job_id
2545 ,p_code => 'JOB'
2546 ,p_segment => l_segment);
2547 --
2548 -- hr_utility.set_location(' Leaving:'||l_proc, 5);
2549 --
2550 return l_segment;
2551 --
2552 end get_job_occupational_series;
2553 --
2554 -- ---------------------------------------------------------------------------
2555 -- |---------------------< get_job_occ_series_job >---------------------------|
2556 -- ---------------------------------------------------------------------------
2557 --
2558 function get_job_occ_series_job
2559 (p_job_id in per_jobs.job_id%type
2560 ,p_business_group_id in per_all_positions.business_group_id%type
2561 ) return varchar2 IS
2562 --
2563 l_proc varchar2(72) := g_package||'get_job_occ_series_job';
2564 l_segment_rec org_info_rec_type;
2565 l_segment per_position_definitions.segment1%type;
2566 --
2567 begin
2568 -- hr_utility.set_location('Entering:'||l_proc, 1);
2569 --
2570 if p_job_id IS NULL OR p_business_group_id IS NULL then
2571 -- hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2572 -- hr_utility.raise_error;
2573 null;
2574 end if;
2575 --
2576 -- hr_utility.set_location(l_proc, 2);
2577 --
2578 retrieve_gov_kff_setup_info (p_business_group_id => p_business_group_id
2579 ,p_org_info_rec => l_segment_rec);
2580 if l_segment_rec.information1 is NULL then
2581 -- hr_utility.set_message(8301, 'GHR_38022_API_GHR_ORG_INFO_ERR');
2582 -- hr_utility.raise_error;
2583 null;
2584 end if;
2585 -- hr_utility.set_location(l_proc, 3);
2586 --
2587 retrieve_segment_information (p_information => l_segment_rec.information1
2588 ,p_id => p_job_id
2589 ,p_code => 'JOB'
2590 ,p_segment => l_segment
2591 );
2592 --
2593 -- hr_utility.set_location(' Leaving:'||l_proc, 4);
2594 --
2595 return l_segment;
2596 --
2597 end get_job_occ_series_job;
2598 --
2599 -- ---------------------------------------------------------------------------
2600 -- |-----------------------< sf52_from_data_elements >-----------------------|
2601 -- ---------------------------------------------------------------------------
2602 --
2603 procedure sf52_from_data_elements
2604 (p_person_id in per_people_f.person_id%type default NULL
2605 ,p_assignment_id IN OUT NOCOPY per_assignments_f.assignment_id%type
2606 ,p_effective_date in date default sysdate
2607 -- vsm added following 2 parameters
2608 ,p_altered_pa_request_id in number
2609 ,p_noa_id_corrected in number
2610 -- vsm added pa_history_id
2611 ,p_pa_history_id in number
2612 ,p_position_title OUT NOCOPY varchar2
2613 ,p_position_number OUT NOCOPY varchar2
2614 ,p_position_seq_no OUT NOCOPY number
2615 ,p_pay_plan OUT NOCOPY varchar2
2616 ,p_job_id OUT NOCOPY number
2617 ,p_occ_code OUT NOCOPY varchar2
2618 ,p_grade_id OUT NOCOPY number
2619 ,p_grade_or_level OUT NOCOPY varchar2
2620 ,p_step_or_rate OUT NOCOPY varchar2
2621 ,p_total_salary OUT NOCOPY number
2622 ,p_pay_basis OUT NOCOPY varchar2
2623 -- FWFA Changes Bug#4444609
2624 ,p_pay_table_identifier OUT NOCOPY number
2625 -- FWFA Changes
2626 ,p_basic_pay OUT NOCOPY number
2627 ,p_locality_adj OUT NOCOPY number
2628 ,p_adj_basic_pay OUT NOCOPY number
2629 ,p_other_pay OUT NOCOPY number
2630 ,p_au_overtime OUT NOCOPY NUMBER
2631 ,p_auo_premium_pay_indicator OUT NOCOPY VARCHAR2
2632 ,p_availability_pay OUT NOCOPY NUMBER
2633 ,p_ap_premium_pay_indicator OUT NOCOPY VARCHAR2
2634 ,p_retention_allowance OUT NOCOPY NUMBER
2635 ,p_retention_allow_percentage OUT NOCOPY NUMBER
2636 ,p_supervisory_differential OUT NOCOPY NUMBER
2637 ,p_supervisory_diff_percentage OUT NOCOPY NUMBER
2638 ,p_staffing_differential OUT NOCOPY NUMBER
2639 ,p_staffing_diff_percentage OUT NOCOPY NUMBER
2640 ,p_organization_id OUT NOCOPY number
2641 ,p_position_org_line1 OUT NOCOPY varchar2 -- Position_org_line1 .. 6
2642 ,p_position_org_line2 OUT NOCOPY varchar2
2643 ,p_position_org_line3 OUT NOCOPY varchar2
2644 ,p_position_org_line4 OUT NOCOPY varchar2
2645 ,p_position_org_line5 OUT NOCOPY varchar2
2646 ,p_position_org_line6 OUT NOCOPY varchar2
2647 ,p_position_id OUT NOCOPY per_all_positions.position_id%type
2648 ,p_duty_station_location_id OUT NOCOPY hr_locations.location_id%type -- duty_station_location_id
2649 ,p_pay_rate_determinant OUT NOCOPY varchar2
2650 ,p_work_schedule OUT NOCOPY varchar2
2651 ) is
2652 --
2653 l_proc varchar2(72) := g_package||'sf52_from_by_assignment';
2654 l_organization_id hr_organization_units.organization_id%type;
2655 l_position_id per_all_positions.position_id%type;
2656 l_job_id per_jobs.job_id%type;
2657 l_grade_id per_grades.grade_id%type;
2658 l_location_id hr_locations.location_id%type;
2659 l_total_salary number;
2660 l_basic_pay number;
2661 l_locality_adj number;
2662 l_adj_basic_pay number;
2663 l_other_pay number;
2664 l_au_overtime NUMBER;
2665 l_auo_premium_pay_indicator VARCHAR2(30);
2666 l_availability_pay NUMBER;
2667 l_ap_premium_pay_indicator VARCHAR2(30);
2668 l_retention_allowance NUMBER;
2669 l_retention_allow_percentage NUMBER;
2670 l_supervisory_differential NUMBER;
2671 l_supervisory_diff_percentage NUMBER;
2672 l_staffing_differential NUMBER;
2673 l_staffing_diff_percentage NUMBER;
2674 l_org_info_rec org_info_rec_type;
2675 l_multi_error_flag boolean;
2676 l_asg_by_per_id_found boolean := FALSE;
2677 l_asg_by_asg_id_found boolean := FALSE;
2678 l_organization_found boolean := FALSE;
2679 l_location_found boolean := FALSE;
2680 l_grade_kff_found boolean := FALSE;
2681 l_assignment_ddf_found boolean := FALSE;
2682 l_position_ddf_found boolean := FALSE;
2683 l_asg_ei_data per_assignment_extra_info%rowtype;
2684 l_pos_ei_data per_position_extra_info%rowtype;
2685 l_element_entry_data pay_element_entry_values_f%rowtype;
2686 l_org_id hr_organization_units.organization_id%type;
2687 --vsm
2688 l_business_group_id per_business_groups.business_group_id%type;
2689 l_result_code varchar2(30);
2690 l_assignment_data per_all_assignments_f%rowtype;
2691 l_person_type per_person_types.system_person_type%type;
2692 l_effective_date date;
2693 l_assignment_id per_assignments_f.assignment_id%type;
2694 v_assignment_id per_assignments_f.assignment_id%type;
2695 l_session ghr_history_api.g_session_var_type;
2696 l_pa_request_id ghr_pa_requests.pa_request_id%type;
2697 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
2698 l_update34_date date;
2699 -- FWFA Changes Bug#4444609
2700 l_pay_basis varchar2(30);
2701 l_pay_table_identifier NUMBER(10);
2702 -- FWFA Changes
2703
2704 --
2705 -- Cursor to get person type
2706 cursor c_person_type is
2707 select system_person_type
2708 from per_all_people_f ppf,
2709 per_person_types ppt
2710 where ppf.person_id = p_person_id
2711 and ppt.person_type_id = ppf.person_type_id;
2712 --
2713 cursor c_assignment_by_per_id (per_id number, eff_date date) is
2714 select asg.assignment_id,
2715 asg.organization_id,
2716 asg.job_id,
2717 asg.position_id,
2718 asg.grade_id,
2719 asg.location_id
2720 from per_all_assignments_f asg
2721 where asg.person_id = per_id
2722 and asg.assignment_type <> 'B'
2723 and trunc(eff_date) between asg.effective_start_date
2724 and asg.effective_end_date
2725 and asg.primary_flag = 'Y';
2726 --
2727 Cursor c_assignment_ex_emp is
2728 select asg.effective_end_date,asg.assignment_id,asg.organization_id,
2729 asg.job_id,
2730 asg.position_id,
2731 asg.grade_id,
2732 asg.location_id
2733 from per_all_assignments_f asg
2734 where asg.person_id = p_person_id
2735 and asg.assignment_type <> 'B'
2736 and asg.effective_start_date < p_effective_date
2737 and asg.primary_flag = 'Y'
2738 order by asg.effective_start_date desc;
2739 --
2740 CURSOR c_asg_by_per_id_not_prim (p_per_id number, p_eff_date date) IS
2741 SELECT asg.assignment_id,
2742 asg.organization_id,
2743 asg.job_id,
2744 asg.position_id,
2745 asg.grade_id,
2746 asg.location_id
2747 FROM per_all_assignments_f asg
2748 WHERE asg.person_id = p_per_id
2749 AND asg.assignment_type <> 'B'
2750 AND trunc(p_eff_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
2751 ORDER BY asg.assignment_id;
2752 --
2753 cursor c_assignment_by_asg_id (asg_id number, eff_date date) is
2754 select asg.organization_id,
2755 asg.job_id,
2756 asg.position_id,
2757 asg.grade_id,
2758 asg.location_id
2759 from per_all_assignments_f asg
2760 where asg.assignment_id = asg_id
2761 and asg.assignment_type <> 'B'
2762 and trunc(eff_date) between asg.effective_start_date
2763 and asg.effective_end_date;
2764 --
2765
2766 cursor c_grade_kff (grd_id number) is
2767 select gdf.segment1,
2768 gdf.segment2
2769 from per_grades grd,
2770 per_grade_definitions gdf
2771 where grd.grade_id = grd_id
2772 and grd.grade_definition_id = gdf.grade_definition_id;
2773 --
2774 cursor c_assignment_ddf (asg_id number) is
2775 select aei.aei_information3,
2776 aei.aei_information6
2777 from per_assignment_extra_info aei
2778 where aei.assignment_id = asg_id
2779 and aei.information_type = 'GHR_US_ASG_SF52';
2780 --
2781 cursor c_position_ddf (pos_id number) is
2782 select poi.poei_information6
2783 from per_position_extra_info poi
2784 where poi.position_id = pos_id
2785 and poi.information_type = 'GHR_US_POS_VALID_GRADE';
2786
2787
2788 cursor c_org_address (org_id number) is
2789 select oi.org_information5,
2790 oi.org_information6,
2791 oi.org_information7,
2792 oi.org_information8,
2793 oi.org_information9,
2794 oi.org_information10
2795 from hr_organization_information oi
2796 where oi.organization_id = org_id
2797 and oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
2798
2799 ----
2800 ----For Better Performance it was suggested that the last line in the below
2801 ----Query to be eliminated. Customer suggested to put the paranthesis one line up.
2802 ----Bug 2770551. AVR Suggested 20-JAN-03.
2803 ----
2804 /**** Commented the original...
2805 Cursor c_job_id is
2806 select job_id, business_group_id
2807 from ghr_assignments_h_v
2808 where pa_request_id =
2809 (select min(pa_request_id)
2810 from ghr_pa_requests
2811 connect by pa_request_id = prior altered_pa_request_id
2812 start with pa_request_id = p_altered_pa_request_id
2813 and nature_of_action_id = p_noa_id_corrected
2814 );
2815 --order by pa_history_id desc;
2816
2817 */
2818
2819 Cursor c_job_id is
2820 select job_id, business_group_id
2821 from ghr_assignments_h_v
2822 where pa_request_id =
2823 (select min(pa_request_id)
2824 from ghr_pa_requests
2825 connect by pa_request_id = prior altered_pa_request_id
2826 start with pa_request_id = p_altered_pa_request_id
2827 );
2828 --order by pa_history_id desc;
2829
2830 Cursor c_orig_par_id is
2831 select min(pa_request_id) pa_request_id
2832 from ghr_pa_requests
2833 connect by pa_request_id = prior altered_pa_request_id
2834 start with pa_request_id = p_altered_pa_request_id;
2835
2836 Cursor c_orig_par_rec is
2837 Select par.employee_assignment_id,
2838 par.from_adj_basic_pay,
2839 par.from_basic_pay,
2840 par.from_grade_or_level,
2841 par.from_locality_adj,
2842 par.from_occ_code,
2843 par.from_office_symbol,
2844 par.from_other_pay_amount,
2845 par.from_pay_basis,
2846 par.from_pay_plan,
2847 par.from_position_id,
2848 par.from_position_org_line1,
2849 par.from_position_org_line2,
2850 par.from_position_org_line3,
2851 par.from_position_org_line4,
2852 par.from_position_org_line5,
2853 par.from_position_org_line6,
2854 par.from_position_seq_no,
2855 par.from_position_title,
2856 par.from_position_number,
2857 -- FWFA Changes Bug#4444609
2858 par.from_pay_table_identifier,
2859 -- FWFA Changes
2860 par.from_step_or_rate,
2861 par.from_total_salary
2862 from ghr_pa_requests par
2863 where pa_request_id = l_pa_request_id;
2864 --
2865 --
2866 begin
2867 hr_utility.set_location('Entering:'||l_proc, 1);
2868 --
2869 l_effective_date := p_effective_date;
2870 l_assignment_id := p_assignment_id;
2871
2872 --Initilization for NOCOPY Changes
2873 --
2874 v_assignment_id := p_assignment_id;
2875 --
2876 If p_noa_id_corrected is not null or p_altered_pa_request_id is not null then
2877 -- get original RPA , the very first;
2878 for orig_par_id in c_orig_par_id loop
2879 l_pa_request_id := orig_par_id.pa_request_id;
2880 end loop;
2881 for orig_par_rec in c_orig_par_rec loop
2882 p_assignment_id := orig_par_rec.employee_assignment_id;
2883 p_position_title := orig_par_rec.from_position_title;
2884 p_position_number := orig_par_rec.from_position_number;
2885 p_position_seq_no := orig_par_rec.from_position_seq_no;
2886 p_pay_plan := orig_par_rec.from_pay_plan;
2887 -- FWFA Changes Bug#4444609
2888 p_pay_table_identifier := orig_par_rec.from_pay_table_identifier;
2889 -- FWFA Changes
2890 p_occ_code := orig_par_rec.from_occ_code;
2891 --p_grade_id
2892 p_grade_or_level := orig_par_rec.from_grade_or_level;
2893 p_step_or_rate := orig_par_rec.from_step_or_rate;
2894 p_total_salary := orig_par_rec.from_total_salary;
2895 p_pay_basis := orig_par_rec.from_pay_basis;
2896 p_basic_pay := orig_par_rec.from_basic_pay;
2897 p_locality_adj := orig_par_rec.from_locality_adj;
2898 p_adj_basic_pay := orig_par_rec.from_adj_basic_pay;
2899 p_other_pay := orig_par_rec.from_other_pay_amount;
2900 p_position_id := orig_par_rec.from_position_id;
2901 p_position_org_line1 := orig_par_rec.from_position_org_line1;
2902 p_position_org_line2 := orig_par_rec.from_position_org_line2;
2903 p_position_org_line3 := orig_par_rec.from_position_org_line3;
2904 p_position_org_line4 := orig_par_rec.from_position_org_line4;
2905 p_position_org_line5 := orig_par_rec.from_position_org_line5;
2906 p_position_org_line6 := orig_par_rec.from_position_org_line6;
2907
2908 end loop;
2909 ghr_history_fetch.fetch_assignment
2910 ( p_assignment_id => p_assignment_id
2911 ,p_date_effective => l_effective_date
2912 ,p_altered_pa_request_id => p_altered_pa_request_id
2913 ,p_noa_id_corrected => p_noa_id_corrected
2914 ,p_assignment_data => l_assignment_data
2915 ,p_pa_history_id => p_pa_history_id
2916 ,p_result_code => l_result_code);
2917 if l_result_code = 'not_found' then
2918 null;
2919 -- raise error.
2920 end if;
2921 p_duty_station_location_id := l_assignment_data.location_id;
2922 Ghr_History_Fetch.Fetch_ASGEI_prior_root_sf50(
2923 p_assignment_id => p_assignment_id,
2924 p_information_type => 'GHR_US_ASG_SF52',
2925 p_date_effective => l_effective_date,
2926 p_altered_pa_request_id => p_altered_pa_request_id,
2927 p_noa_id_corrected => p_noa_id_corrected,
2928 p_get_ovn_flag => 'Y',
2929 p_asgei_data => l_asg_ei_data
2930 );
2931 p_work_schedule := l_asg_ei_data.aei_information7;
2932 p_pay_rate_determinant := l_asg_ei_data.aei_information6;
2933
2934 Else
2935
2936 for per_type in c_per_type(p_person_id,l_effective_date) loop
2937 l_person_type := per_type.system_person_type;
2938 end loop;
2939
2940 -- VSM added following if stmt. and the TRUE result code to take care of refresh of correction SF52.
2941 if p_altered_pa_request_id is not NULL and
2942 p_noa_id_corrected is not NULL then
2943 if p_assignment_id is NULL then
2944 hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2945 hr_utility.raise_error;
2946 else
2947 ghr_history_fetch.fetch_assignment
2948 ( p_assignment_id => p_assignment_id
2949 ,p_date_effective => p_effective_date
2950 ,p_altered_pa_request_id => p_altered_pa_request_id
2951 ,p_noa_id_corrected => p_noa_id_corrected
2952 ,p_assignment_data => l_assignment_data
2953 ,p_pa_history_id => p_pa_history_id
2954 ,p_result_code => l_result_code);
2955 if l_result_code = 'not_found' then
2956 null;
2957 -- raise error.
2958 end if;
2959 p_duty_station_location_id := l_assignment_data.location_id;
2960 l_position_id := l_assignment_data.position_id;
2961 l_organization_id := l_assignment_data.organization_id;
2962 l_job_id := l_assignment_data.job_id;
2963 l_grade_id := l_assignment_data.grade_id;
2964 end if;
2965
2966 else
2967 if p_person_id is NULL and p_assignment_id is NULL then
2968 hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2969 hr_utility.raise_error;
2970 elsif p_assignment_id is NULL then
2971 -- If we were not given an assignment id then we need to get the 'default'
2972 -- assignment. The definition of 'default' assignment is not yet clearly
2973 -- defined!
2974 -- Every person must have at least one assignment, now if they are an
2975 -- 'employee' then the 'default' will be the primary assignment (i.e. primary_flag = 'Y')
2976 -- since a person can have only one primary assignment.
2977 -- However if you are an 'applicant' we do not know which assignment to choose as
2978 -- they apparently do not have a primary assignment hence for the moment just chose the first
2979 -- one!!
2980 -- It is also not clear at the moment the exact definition of an 'employee' as opposed
2981 -- to an 'applicant'.
2982 -- In conclusion to all this we will first try and get the 'primary' assignment and
2983 -- if there isn't one we will just get the first one we can!
2984 for per_type in c_per_type(p_person_id,l_effective_date) loop
2985 l_person_type := per_type.system_person_type;
2986 end loop;
2987 If l_person_type = 'EX_EMP' then
2988 hr_utility.set_location('Ex Employee in conversion action',1);
2989 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
2990 l_asg_by_per_id_found := TRUE;
2991 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
2992 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
2993 l_organization_id := c_assignment_ex_emp_rec.organization_id;
2994 l_job_id := c_assignment_ex_emp_rec.job_id;
2995 l_position_id := c_assignment_ex_emp_rec.position_id;
2996 l_grade_id := c_assignment_ex_emp_rec.grade_id;
2997 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
2998
2999 EXIT;
3000 END LOOP;
3001
3002 Else
3003 --RP
3004 if l_person_type = 'EX_EMP' then
3005 hr_utility.set_location('Ex Employee in conversion action with asg id',1);
3006 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
3007 l_asg_by_per_id_found := TRUE;
3008 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
3009 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
3010 l_organization_id := c_assignment_ex_emp_rec.organization_id;
3011 l_job_id := c_assignment_ex_emp_rec.job_id;
3012 l_position_id := c_assignment_ex_emp_rec.position_id;
3013 l_grade_id := c_assignment_ex_emp_rec.grade_id;
3014 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
3015 EXIT;
3016 END LOOP;
3017 Else
3018
3019 FOR c_assignment_by_per_id_rec in c_assignment_by_per_id (p_person_id, l_effective_date) LOOP
3020 l_asg_by_per_id_found := TRUE;
3021 --
3022 p_assignment_id := c_assignment_by_per_id_rec.assignment_id;
3023 l_organization_id := c_assignment_by_per_id_rec.organization_id;
3024 l_job_id := c_assignment_by_per_id_rec.job_id;
3025 l_position_id := c_assignment_by_per_id_rec.position_id;
3026 l_grade_id := c_assignment_by_per_id_rec.grade_id;
3027 p_duty_station_location_id := c_assignment_by_per_id_rec.location_id;
3028 EXIT;
3029 END LOOP;
3030 IF NOT l_asg_by_per_id_found THEN
3031 -- Couldn't get a primary assignment so try for any other
3032 --
3033 FOR c_asg_by_per_id_not_prim_rec in c_asg_by_per_id_not_prim (p_person_id, l_effective_date)
3034 LOOP
3035 l_asg_by_per_id_found := TRUE;
3036 --
3037 p_assignment_id := c_asg_by_per_id_not_prim_rec.assignment_id;
3038 l_organization_id := c_asg_by_per_id_not_prim_rec.organization_id;
3039 l_job_id := c_asg_by_per_id_not_prim_rec.job_id;
3040 l_position_id := c_asg_by_per_id_not_prim_rec.position_id;
3041 l_grade_id := c_asg_by_per_id_not_prim_rec.grade_id;
3042 p_duty_station_location_id := c_asg_by_per_id_not_prim_rec.location_id;
3043
3044 EXIT;
3045 END LOOP;
3046 --
3047 END IF;
3048 End if;
3049 --
3050 IF NOT l_asg_by_per_id_found THEN
3051 hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
3052 hr_utility.raise_error;
3053 END IF;
3054 End if;
3055 ELSE -- p_assignment_id is not NULL
3056 hr_utility.set_location('asg id is not null : Asg Id : ' || p_assignment_id,1);
3057 If l_person_type = 'EX_EMP' then
3058 hr_utility.set_location('Ex Employee in conversion action',1);
3059 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
3060 l_asg_by_asg_id_found := TRUE;
3061 hr_utility.set_location('Ex Employee - with Asg , value found ',1);
3062 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
3063 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
3064 l_organization_id := c_assignment_ex_emp_rec.organization_id;
3065 l_job_id := c_assignment_ex_emp_rec.job_id;
3066 l_position_id := c_assignment_ex_emp_rec.position_id;
3067 l_grade_id := c_assignment_ex_emp_rec.grade_id;
3068 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
3069 EXIT;
3070 END LOOP;
3071 Else
3072 for c_assignment_by_asg_id_rec
3073 in c_assignment_by_asg_id (p_assignment_id, l_effective_date) loop
3074 l_asg_by_asg_id_found := TRUE;
3075 l_organization_id := c_assignment_by_asg_id_rec.organization_id;
3076 l_job_id := c_assignment_by_asg_id_rec.job_id;
3077 l_position_id := c_assignment_by_asg_id_rec.position_id;
3078 l_grade_id := c_assignment_by_asg_id_rec.grade_id;
3079 p_duty_station_location_id := c_assignment_by_asg_id_rec.location_id;
3080 exit;
3081 end loop;
3082 End if;
3083 if not l_asg_by_asg_id_found then
3084 hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
3085 hr_utility.raise_error;
3086 end if;
3087 END IF;
3088 END IF;
3089
3090 p_position_id := l_position_id;
3091 p_organization_id := l_organization_id;
3092 p_job_id := l_job_id;
3093 p_grade_id := l_grade_id;
3094
3095 hr_utility.set_location(l_proc, 2);
3096
3097 if p_altered_pa_request_id is not NULL and
3098 p_noa_id_corrected is not NULL then
3099 hr_utility.set_location(l_proc, 104);
3100
3101 /* retrieve_business_group_id
3102 ( p_person_id => p_person_id,
3103 p_effective_date => l_effective_date,
3104 p_altered_pa_request_id => p_altered_pa_request_id,
3105 p_noa_id_corrected => p_noa_id_corrected,
3106 p_pa_history_id => p_pa_history_id,
3107 p_business_group_id => l_business_group_id
3108 );
3109
3110 p_position_title := get_position_title_pos
3111 (p_position_id => l_position_id
3112 ,p_business_group_id => l_business_group_id ) ;
3113
3114 p_position_number := get_position_desc_no_pos
3115 (p_position_id => l_position_id
3116 ,p_business_group_id => l_business_group_id);
3117
3118 p_position_seq_no := get_position_sequence_no_pos
3119 (p_position_id => l_position_id
3120 ,p_business_group_id => l_business_group_id);
3121
3122 */
3123 else
3124 --
3125 -- Retrieve Position Title
3126 --
3127 p_position_title := get_position_title (p_person_id => p_person_id
3128 ,p_assignment_id => p_assignment_id
3129 ,p_effective_date => l_effective_date);
3130 hr_utility.set_location(l_proc, 3);
3131 --
3132 -- Retrieve Position Number
3133 --
3134 p_position_number := get_position_description_no
3135 (p_person_id => p_person_id
3136 ,p_assignment_id => p_assignment_id
3137 ,p_effective_date => l_effective_date);
3138
3139 p_position_seq_no := get_position_sequence_no
3140 (p_person_id => p_person_id
3141 ,p_assignment_id => p_assignment_id
3142 ,p_effective_date => l_effective_date);
3143 END IF;
3144
3145 hr_utility.set_location(l_proc, 4);
3146 --
3147 -- Retrieve Grade Key Flexfield Information
3148 --
3149 for c_grade_kff_rec in c_grade_kff (l_grade_id) loop
3150 l_grade_kff_found := TRUE;
3151 p_pay_plan := c_grade_kff_rec.segment1;
3152 p_grade_or_level := c_grade_kff_rec.segment2;
3153 exit;
3154 end loop;
3155 if not l_grade_kff_found then
3156 -- hr_utility.set_message(8301, 'GHR_38026_API_INV_GRD');
3157 -- hr_utility.raise_error;
3158 null;
3159 end if;
3160 hr_utility.set_location(l_proc, 5);
3161 --
3162 -- Retrieve Job Occupational Series
3163 --
3164 ghr_history_api.get_g_session_var(l_session);
3165 hr_utility.set_location('p_assignment id before getting job is ' || p_assignment_id,1);
3166 if l_session.noa_id_correct is null then --RP
3167 p_occ_code := get_job_occupational_series (p_person_id => p_person_id
3168 ,p_assignment_id => p_assignment_id
3169 ,p_effective_date => l_effective_date);
3170 else
3171 for job_id_rec in c_job_id loop
3172 l_job_id := job_id_rec.job_id;
3173 l_business_group_id := job_id_rec.business_group_id;
3174 exit;
3175 end loop;
3176 p_occ_code := get_job_occ_series_job (p_job_id => l_job_id
3177 ,p_business_group_id => l_business_group_id);
3178 end if; --RP
3179 hr_utility.set_location(l_proc, 6);
3180
3181
3182 -- VSM added following 2 parameters to all the calls to GHR_History_Fetch procedures
3183 -- ,p_altered_pa_request_id => p_altered_pa_request_id
3184 -- ,p_noa_id_corrected => p_noa_id_corrected
3185
3186
3187 --
3188 -- Retrieve Assignment Developer Descriptive Flexfield Information
3189 --
3190
3191 -- This procedures uses session variables for p_altered)pa_request_id and p_noa_id_corrected
3192 ghr_history_fetch.fetch_asgei (p_assignment_id => p_assignment_id
3193 ,p_information_type => 'GHR_US_ASG_SF52'
3194 ,p_date_effective => l_effective_date
3195 ,p_asg_ei_data => l_asg_ei_data
3196 );
3197
3198 p_step_or_rate := l_asg_ei_data.aei_information3;
3199
3200 hr_utility.set_location(l_proc, 7);
3201 --
3202 -- Retrieve Position Developer Descriptive Flexfield Information
3203 --
3204 -- This procedures uses session variables for p_altered)pa_request_id and p_noa_id_corrected
3205 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3206 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
3207 ,p_date_effective => l_effective_date
3208 ,p_pos_ei_data => l_pos_ei_data
3209 );
3210 -- FWFA Changes Bug# 4444609
3211 l_pay_basis := l_pos_ei_data.poei_information6;
3212 l_pay_table_identifier := l_pos_ei_data.poei_information5;
3213 -- FWFA Changes
3214
3215 hr_utility.set_location(l_proc, 8);
3216 --
3217 -- Retrieve work_Schedule -- (Added for OGSD)
3218
3219 If p_assignment_id is null then
3220 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3221 ,p_information_type => 'GHR_US_POS_GRP1'
3222 ,p_date_effective => l_effective_date
3223 ,p_pos_ei_data => l_pos_ei_data
3224 );
3225
3226 p_work_Schedule := l_pos_ei_data.poei_information10;
3227 hr_utility.set_location(l_proc,9);
3228 Else
3229
3230 -- VSM Prior PRD and Work Schedule
3231 Ghr_History_Fetch.Fetch_ASGEI_prior_root_sf50(
3232 p_assignment_id => p_assignment_id,
3233 p_information_type => 'GHR_US_ASG_SF52',
3234 p_date_effective => l_effective_date,
3235 p_altered_pa_request_id => p_altered_pa_request_id,
3236 p_noa_id_corrected => p_noa_id_corrected,
3237 p_get_ovn_flag => 'Y',
3238 p_asgei_data => l_asg_ei_data);
3239
3240 p_work_schedule := l_asg_ei_data.aei_information7;
3241 p_pay_rate_determinant := l_asg_ei_data.aei_information6;
3242
3243 End if;
3244
3245 --Start New Retained Grade Processing
3246 hr_utility.set_location('PRD is ' ||p_pay_rate_determinant,9);
3247 IF p_pay_rate_determinant IN ('A','B','E','F','U','V') THEN
3248 -- use retained details...
3249 BEGIN
3250 l_retained_grade :=
3251 ghr_pc_basic_pay.get_retained_grade_details
3252 (p_person_id
3253 ,l_effective_date);
3254 EXCEPTION
3255 WHEN OTHERS THEN
3256 BEGIN
3257 l_retained_grade :=
3258 ghr_pc_basic_pay.get_retained_grade_details
3259 (p_person_id
3260 ,(l_effective_date - 1));
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 hr_utility.set_message(8301,'GHR_38699_MISSING_RETAINED_DET');
3264 hr_utility.raise_error;
3265 END;
3266 END;
3267 if l_retained_grade.temp_step is NULL THEN -- Temp. Promo RG Changes
3268 l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
3269 hr_utility.set_location('Update 34 date is ' ||l_update34_date,10);
3270 hr_utility.set_location('Effective date is ' ||l_effective_date,11);
3271 if l_update34_date is not null AND l_effective_date >= l_update34_date then
3272 p_pay_basis := l_retained_grade.pay_basis;
3273 -- FWFA Changes Bug#4444609
3274 p_pay_table_identifier := l_retained_grade.user_table_id;
3275 -- FWFA Changes
3276 hr_utility.set_location('RET 1 pay basis is ' ||p_pay_basis,11);
3277 else
3278 -- FWFA Changes Bug#4444609
3279 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3280 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3281 p_pay_table_identifier := l_pay_table_identifier;
3282 p_pay_basis := l_pay_basis;
3283 -- FWFA Changes
3284 hr_utility.set_location('POS 1 pay basis is ' ||p_pay_basis,11);
3285 end if;
3286 ELSE
3287 -- FWFA Changes Bug#4444609
3288 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3289 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3290 p_pay_table_identifier := l_pay_table_identifier;
3291 p_pay_basis := l_pay_basis;
3292 -- FWFA Changes
3293 END IF;
3294 ELSE
3295 -- FWFA Changes Bug#4444609
3296 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3297 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3298 p_pay_table_identifier := l_pay_table_identifier;
3299 p_pay_basis := l_pay_basis;
3300 -- FWFA Changes
3301 hr_utility.set_location('POS 2 pay basis is ' ||p_pay_basis,11);
3302 END IF;
3303 hr_utility.set_location('Final from pay basis is ' ||p_pay_basis,11);
3304 --End New Retained Grade Processing
3305
3306 -- Changed for Basic Salary Rate
3307 --
3308 retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
3309 ,p_input_value_name => 'Rate'
3310 ,p_assignment_id => p_assignment_id
3311 ,p_effective_date => l_effective_date
3312 ,p_value => l_basic_pay
3313 ,p_multiple_error_flag => l_multi_error_flag);
3314 /* if l_basic_pay is NULL then
3315 l_basic_pay := 0;
3316 end if;
3317 */
3318 if l_multi_error_flag then
3319 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3320 hr_utility.raise_error;
3321 end if;
3322 --
3323
3324 -- FWFA Changes Bug#4444609 Modified 'Locality Pay' to 'Locality Pay or SR Supplement'
3325 retrieve_element_entry_value (p_element_name => 'Locality Pay or SR Supplement'
3326 -- FWFA Changes
3327 ,p_input_value_name => 'Rate'
3328 -- Changed from 'Amount' to 'Rate' by Ashu Gupta
3329 ,p_assignment_id => p_assignment_id
3330 ,p_effective_date => l_effective_date
3331 ,p_value => l_locality_adj
3332 ,p_multiple_error_flag => l_multi_error_flag);
3333 /*if l_locality_adj is NULL then
3334 l_locality_adj := 0;
3335 end if;
3336 */
3337 if l_multi_error_flag then
3338 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3339 hr_utility.raise_error;
3340 end if;
3341 --
3342
3343 -- Processing Total Pay and Adjusted Basic Pay
3344 -- NAME DATE BUG COMMENTS
3345 -- Ashley 17-JUL-03 Payroll Intg Modified the Input Value name
3346 -- Changes from Total Salary -> Amount
3347 -- Adjusted Pay -> Amount
3348 --
3349
3350 retrieve_element_entry_value (p_element_name => 'Adjusted Basic Pay'
3351 ,p_input_value_name => 'Amount'
3352 ,p_assignment_id => p_assignment_id
3353 ,p_effective_date => l_effective_date
3354 ,p_value => l_adj_basic_pay
3355 ,p_multiple_error_flag => l_multi_error_flag);
3356 /*if l_adj_basic_pay is NULL then
3357 l_adj_basic_pay := 0;
3358 end if;
3359 */
3360 if l_multi_error_flag then
3361 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3362 hr_utility.raise_error;
3363 end if;
3364 --
3365 --
3366 -- 7/28/97 Added retrieval of Other pay and the 7 items that make it up!
3367 retrieve_element_entry_value (p_element_name =>'Other Pay'
3368 ,p_input_value_name => 'Amount'
3369 ,p_assignment_id => p_assignment_id
3370 ,p_effective_date => l_effective_date
3371 ,p_value => l_other_pay
3372 ,p_multiple_error_flag => l_multi_error_flag);
3373 /*if l_other_pay is NULL then
3374 l_other_pay := 0;
3375 end if;
3376 */
3377 if l_multi_error_flag then
3378 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3379 hr_utility.raise_error;
3380 end if;
3381 --
3382
3383
3384 retrieve_element_entry_value (p_element_name => 'Total Pay'
3385 ,p_input_value_name => 'Amount'
3386 ,p_assignment_id => p_assignment_id
3387 ,p_effective_date => l_effective_date
3388 ,p_value => l_total_salary
3389 ,p_multiple_error_flag => l_multi_error_flag);
3390 /* if l_total_salary is NULL then
3391 l_total_salary := 0;
3392 end if;
3393 */
3394 if l_multi_error_flag then
3395 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3396 hr_utility.raise_error;
3397 end if;
3398 --
3399 --
3400 p_total_salary := round(l_total_salary,2);
3401 p_basic_pay := round(l_basic_pay,2);
3402 p_locality_adj := round(l_locality_adj,0);
3403 p_adj_basic_pay := round(l_adj_basic_pay,2);
3404 p_other_pay := l_other_pay;
3405 --
3406 -- Retrieve Organzation Information
3407 --
3408 hr_utility.set_location(l_proc, 9);
3409 --
3410 -- Retrieve Position's Organization's address lines
3411
3412
3413 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3414 ,p_information_type => 'GHR_US_POS_GRP1'
3415 ,p_date_effective => p_effective_date
3416 ,p_pos_ei_data => l_pos_ei_data
3417 );
3418 l_org_id := l_pos_ei_data.poei_information21;
3419
3420 If l_org_id is not null then
3421 for org_address in c_org_address(l_org_id) loop
3422 p_position_org_line1 := org_address.org_information5;
3423 p_position_org_line2 := org_address.org_information6;
3424 p_position_org_line3 := org_address.org_information7;
3425 p_position_org_line4 := org_address.org_information8;
3426 p_position_org_line5 := org_address.org_information9;
3427 p_position_org_line6 := org_address.org_information10;
3428 End loop;
3429 End if;
3430 End if;
3431 --
3432 retrieve_element_entry_value (p_element_name => 'AUO'
3433 ,p_input_value_name => 'Amount'
3434 ,p_assignment_id => p_assignment_id
3435 ,p_effective_date => l_effective_date
3436 ,p_value => l_au_overtime
3437 ,p_multiple_error_flag => l_multi_error_flag);
3438 /*if l_au_overtime is NULL then
3439 l_au_overtime := 0;
3440 end if;
3441 */
3442 if l_multi_error_flag then
3443 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3444 hr_utility.raise_error;
3445 end if;
3446 --
3447 retrieve_element_entry_value (p_element_name => 'AUO'
3448 ,p_input_value_name => 'Premium Pay Ind'
3449 ,p_assignment_id => p_assignment_id
3450 ,p_effective_date => l_effective_date
3451 ,p_value => l_auo_premium_pay_indicator
3452 ,p_multiple_error_flag => l_multi_error_flag);
3453 /*if l_auo_premium_pay_indicator is NULL then
3454 l_auo_premium_pay_indicator := 0;
3455 end if;
3456 */
3457 if l_multi_error_flag then
3458 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3459 hr_utility.raise_error;
3460 end if;
3461 --
3462 retrieve_element_entry_value (p_element_name => 'Availability Pay'
3463 ,p_input_value_name => 'Amount'
3464 ,p_assignment_id => p_assignment_id
3465 ,p_effective_date => l_effective_date
3466 ,p_value => l_availability_pay
3467 ,p_multiple_error_flag => l_multi_error_flag);
3468 /* if l_availability_pay is NULL then
3469 l_availability_pay := 0;
3470 end if;
3471 */
3472 if l_multi_error_flag then
3473 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3474 hr_utility.raise_error;
3475 end if;
3476 --
3477
3478 retrieve_element_entry_value (p_element_name => 'Availability Pay'
3479 ,p_input_value_name => 'Premium Pay Ind'
3480 ,p_assignment_id => p_assignment_id
3481 ,p_effective_date => l_effective_date
3482 ,p_value => l_ap_premium_pay_indicator
3483 ,p_multiple_error_flag => l_multi_error_flag);
3484 /* if l_ap_premium_pay_indicator is NULL then
3485 l_ap_premium_pay_indicator := 0;
3486 end if;
3487 */
3488 if l_multi_error_flag then
3489 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3490 hr_utility.raise_error;
3491 end if;
3492 --
3493 retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3494 ,p_input_value_name => 'Amount'
3495 ,p_assignment_id => p_assignment_id
3496 ,p_effective_date => l_effective_date
3497 ,p_value => l_retention_allowance
3498 ,p_multiple_error_flag => l_multi_error_flag);
3499 -- added 06-Oct by Sue
3500 retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3501 ,p_input_value_name => 'Percentage'
3502 ,p_assignment_id => p_assignment_id
3503 ,p_effective_date => l_effective_date
3504 ,p_value => l_retention_allow_percentage
3505 ,p_multiple_error_flag => l_multi_error_flag);
3506 /*if l_retention_allowance is NULL then
3507 l_retention_allowance := 0;
3508 end if;
3509 */
3510 if l_multi_error_flag then
3511 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3512 hr_utility.raise_error;
3513 end if;
3514 --
3515 retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3516 ,p_input_value_name => 'Amount'
3517 ,p_assignment_id => p_assignment_id
3518 ,p_effective_date => l_effective_date
3519 ,p_value => l_supervisory_differential
3520 ,p_multiple_error_flag => l_multi_error_flag);
3521 -- added 06-Oct by Sue
3522 retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3523 ,p_input_value_name => 'Percentage'
3524 ,p_assignment_id => p_assignment_id
3525 ,p_effective_date => p_effective_date
3526 ,p_value => l_supervisory_diff_percentage
3527 ,p_multiple_error_flag => l_multi_error_flag);
3528 /* if l_supervisory_differential is NULL then
3529 l_supervisory_differential := 0;
3530 end if;
3531 */
3532 if l_multi_error_flag then
3533 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3534 hr_utility.raise_error;
3535 end if;
3536 --
3537 retrieve_element_entry_value (p_element_name => 'Staffing Differential'
3538 ,p_input_value_name => 'Amount'
3539 ,p_assignment_id => p_assignment_id
3540 ,p_effective_date => l_effective_date
3541 ,p_value => l_staffing_differential
3542 ,p_multiple_error_flag => l_multi_error_flag);
3543 -- added 06-Oct by Sue
3544 retrieve_element_entry_value (p_element_name => 'Staffing Differential'
3545 ,p_input_value_name => 'Percent'
3546 ,p_assignment_id => p_assignment_id
3547 ,p_effective_date => l_effective_date
3548 ,p_value => l_staffing_diff_percentage
3549 ,p_multiple_error_flag => l_multi_error_flag);
3550 /*if l_staffing_differential is NULL then
3551 l_staffing_differential := 0;
3552 end if;
3553 */
3554 if l_multi_error_flag then
3555 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3556 hr_utility.raise_error;
3557 end if;
3558 --
3559 p_au_overtime := l_au_overtime;
3560 p_auo_premium_pay_indicator := l_auo_premium_pay_indicator;
3561 p_availability_pay := l_availability_pay;
3562 p_ap_premium_pay_indicator := l_ap_premium_pay_indicator;
3563 p_retention_allowance := l_retention_allowance;
3564 p_retention_allow_percentage := l_retention_allow_percentage;
3565 p_supervisory_differential := l_supervisory_differential;
3566 p_supervisory_diff_percentage := l_supervisory_diff_percentage;
3567 p_staffing_differential := l_staffing_differential;
3568 p_staffing_diff_percentage := l_staffing_diff_percentage;
3569 hr_utility.set_location(' Leaving:'||l_proc, 20);
3570 EXCEPTION
3571 when others then
3572 -- NOCOPY Changes
3573 -- Reset IN OUT params and Set OUT params to null
3574 p_assignment_id := v_assignment_id;
3575 p_position_title := null;
3576 p_position_number := null;
3577 p_position_seq_no := null;
3578 p_pay_plan := null;
3579 p_job_id := null;
3580 p_occ_code := null;
3581 p_grade_id := null;
3582 p_grade_or_level := null;
3583 p_step_or_rate := null;
3584 p_total_salary := null;
3585 p_pay_basis := null;
3586 -- FWFA Changes Bug#4444609
3587 p_pay_table_identifier := null;
3588 -- FWFA Changes
3589 p_basic_pay := null;
3590 p_locality_adj := null;
3591 p_adj_basic_pay := null;
3592 p_other_pay := null;
3593 p_au_overtime := null;
3594 p_auo_premium_pay_indicator := null;
3595 p_availability_pay := null;
3596 p_ap_premium_pay_indicator := null;
3597 p_retention_allowance := null;
3598 p_retention_allow_percentage := null;
3599 p_supervisory_differential := null;
3600 p_supervisory_diff_percentage := null;
3601 p_staffing_differential := null;
3602 p_staffing_diff_percentage := null;
3603 p_organization_id := null;
3604 p_position_org_line1 := null;
3605 p_position_org_line2 := null;
3606 p_position_org_line3 := null;
3607 p_position_org_line4 := null;
3608 p_position_org_line5 := null;
3609 p_position_org_line6 := null;
3610 p_position_id := null;
3611 p_duty_station_location_id := null;
3612 p_pay_rate_determinant := null;
3613 p_work_schedule := null;
3614 raise;
3615 end sf52_from_data_elements;
3616 --
3617 --
3618 -- ---------------------------------------------------------------------------
3619 -- |--------------------------< return_upd_hr_dml_status >----------------|
3620 -- --------------------------------------------------------------------------
3621 FUNCTION return_upd_hr_dml_status RETURN BOOLEAN IS
3622 l_proc varchar2(72) := g_package||'return_upd_hr_dml_status';
3623 begin
3624 hr_utility.set_location('Entering:'||l_proc,5);
3625 return (nvl(g_api_dml, false));
3626 hr_utility.set_location(' Leaving:'||l_proc,10);
3627 end return_upd_hr_dml_status;
3628
3629 -- ---------------------------------------------------------------------------
3630 -- |--------------------------< return_special_information >----------------|
3631 -- --------------------------------------------------------------------------
3632
3633 Procedure return_special_information
3634 (p_person_id in number
3635 ,p_structure_name in varchar2
3636 ,p_effective_date in date
3637 ,p_special_info OUT NOCOPY ghr_api.special_information_type
3638 )
3639 is
3640 l_proc varchar2(72) := 'return_special_information ';
3641 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
3642 l_max_segment per_analysis_criteria.segment1%type;
3643
3644 Cursor c_flex_num is
3645 select flx.id_flex_num
3646 from fnd_id_flex_structures_tl flx
3647 where flx.id_flex_code = 'PEA' --
3648 and flx.application_id = 800 --
3649 and flx.id_flex_structure_name = p_structure_name
3650 and flx.language = 'US';
3651
3652 Cursor c_sit is
3653 select pea.analysis_criteria_id,
3654 pan.date_from, -- added for bug fix : 609285
3655 pan.person_analysis_id,
3656 pan.object_version_number,
3657 pea.start_date_active,
3658 pea.segment1,
3659 pea.segment2,
3660 pea.segment3,
3661 pea.segment4,
3662 pea.segment5,
3663 pea.segment6,
3664 pea.segment7,
3665 pea.segment8,
3666 pea.segment9,
3667 pea.segment10,
3668 pea.segment11,
3669 pea.segment12,
3670 pea.segment13,
3671 pea.segment14,
3672 pea.segment15,
3673 pea.segment16,
3674 pea.segment17,
3675 pea.segment18,
3676 pea.segment19,
3677 pea.segment20,
3678 pea.segment21,
3679 pea.segment22,
3680 pea.segment23,
3681 pea.segment24,
3682 pea.segment25,
3683 pea.segment26,
3684 pea.segment27,
3685 pea.segment28,
3686 pea.segment29,
3687 pea.segment30
3688 from per_analysis_Criteria pea,
3689 per_person_analyses pan
3690 where pan.person_id = p_person_id
3691 and pan.id_flex_num = l_id_flex_num
3692 and pea.analysis_Criteria_id = pan.analysis_criteria_id
3693 and p_effective_date
3694 between nvl(pan.date_from,p_effective_date)
3695 and nvl(pan.date_to,p_effective_date)
3696 and p_effective_date
3697 between nvl(pea.start_date_active,p_effective_date)
3698 and nvl(pea.end_date_active,p_effective_date)
3699 order by 2 desc, 3 desc;
3700
3701 begin
3702
3703 for flex_num in c_flex_num loop
3704 l_id_flex_num := flex_num.id_flex_num;
3705 End loop;
3706
3707 If l_id_flex_num is null then
3708 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3709 hr_utility.raise_error;
3710 End if;
3711
3712 for special_info in c_sit loop
3713 p_special_info.segment1 := special_info.segment1;
3714 p_special_info.segment2 := special_info.segment2;
3715 p_special_info.segment3 := special_info.segment3;
3716 p_special_info.segment4 := special_info.segment4;
3717 p_special_info.segment5 := special_info.segment5;
3718 p_special_info.segment6 := special_info.segment6;
3719 p_special_info.segment7 := special_info.segment7;
3720 p_special_info.segment8 := special_info.segment8;
3721 p_special_info.segment9 := special_info.segment9;
3722 p_special_info.segment10 := special_info.segment10;
3723 p_special_info.segment11 := special_info.segment11;
3724 p_special_info.segment12 := special_info.segment12;
3725 p_special_info.segment13 := special_info.segment13;
3726 p_special_info.segment14 := special_info.segment14;
3727 p_special_info.segment15 := special_info.segment15;
3728 p_special_info.segment16 := special_info.segment16;
3729 p_special_info.segment17 := special_info.segment17;
3730 p_special_info.segment18 := special_info.segment18;
3731 p_special_info.segment19 := special_info.segment19;
3732 p_special_info.segment20 := special_info.segment20;
3733 p_special_info.segment21 := special_info.segment21;
3734 p_special_info.segment22 := special_info.segment22;
3735 p_special_info.segment23 := special_info.segment23;
3736 p_special_info.segment24 := special_info.segment24;
3737 p_special_info.segment25 := special_info.segment25;
3738 p_special_info.segment26 := special_info.segment26;
3739 p_special_info.segment27 := special_info.segment27;
3740 p_special_info.segment28 := special_info.segment28;
3741 p_special_info.segment29 := special_info.segment29;
3742 p_special_info.segment30 := special_info.segment30;
3743 p_special_info.person_analysis_id := special_info.person_analysis_id;
3744 p_special_info.object_version_number := special_info.object_version_number;
3745
3746 exit;
3747 End loop;
3748 EXCEPTION
3749 when others then
3750 -- NOCOPY changes
3751 -- Reset IN OUT params and set OUT params
3752 p_special_info := null;
3753 raise;
3754 End return_special_information;
3755
3756
3757 -- ---------------------------------------------------------------------------
3758 -- |--------------------------< return_education_Details >----------------|
3759 -- --------------------------------------------------------------------------
3760
3761 Procedure return_education_Details
3762 (p_person_id in per_people_f.person_id%type,
3763 p_effective_date in date,
3764 p_education_level OUT NOCOPY per_analysis_criteria.segment1%type,
3765 p_academic_discipline OUT NOCOPY per_analysis_criteria.segment2%type,
3766 p_year_degree_attained OUT NOCOPY per_analysis_criteria.segment3%type
3767 )
3768 is
3769
3770 l_proc varchar2(72) := 'return_education_Details';
3771 l_special_info ghr_api.special_information_type;
3772 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
3773
3774 Cursor c_flex_num is
3775 select flx.id_flex_num
3776 from fnd_id_flex_structures_tl flx
3777 where flx.id_flex_code = 'PEA' --
3778 and flx.application_id = 800 --
3779 and flx.id_flex_structure_name = 'US Fed Education'
3780 and flx.language = 'US';
3781
3782 /*Cursor to get the highest education level for the person , as of the effective date */
3783
3784
3785 Cursor c_sit is
3786 select pea.segment1,
3787 pea.segment2,
3788 pea.segment3,
3789 pea.segment4,
3790 pea.segment5,
3791 pea.segment6,
3792 pea.segment7,
3793 pea.segment8,
3794 pea.segment9,
3795 pea.segment10,
3796 pea.segment11,
3797 pea.segment12,
3798 pea.segment13,
3799 pea.segment14,
3800 pea.segment15,
3801 pea.segment16,
3802 pea.segment17,
3803 pea.segment18,
3804 pea.segment19,
3805 pea.segment20
3806 from per_analysis_criteria pea,
3807 per_person_analyses pan
3808 where pan.person_id = p_person_id
3809 and pan.id_flex_num = l_id_flex_num
3810 and pea.id_flex_num = pan.id_flex_num
3811 and p_effective_date
3812 between nvl(pan.date_from,p_effective_date)
3813 and nvl(pan.date_to,p_effective_date)
3814 and p_effective_date
3815 between nvl(pea.start_date_active,p_effective_date)
3816 and nvl(pea.end_date_active,p_effective_date)
3817 and pan.analysis_criteria_id = pea.analysis_criteria_id
3818 order by 1 desc;
3819
3820 begin
3821
3822 hr_utility.set_location('Entering ' || l_proc,5);
3823
3824 for flex_num in c_flex_num loop
3825 hr_utility.set_location(l_proc,10);
3826 l_id_flex_num := flex_num.id_flex_num;
3827 end loop;
3828 hr_utility.set_location(l_proc,15);
3829 for sit in c_sit loop
3830 hr_utility.set_location(l_proc,20);
3831 p_education_level := sit.segment1;
3832 p_academic_discipline := sit.segment2;
3833 p_year_degree_attained := sit.segment3;
3834 exit;
3835 end loop;
3836 hr_utility.set_location(l_proc,25);
3837
3838 hr_utility.set_location('Leaving ' ||l_proc,30);
3839 EXCEPTION
3840 when others then
3841 -- NOCOPY changes
3842 -- Reset IN OUT params and set OUT params
3843 p_education_level := null;
3844 p_academic_discipline := null;
3845 p_year_degree_attained := null;
3846 raise;
3847 End return_education_details;
3848
3849 -- ---------------------------------------------------------------------------
3850 -- |--------------------------< call_work_flow>----------------|
3851 -- --------------------------------------------------------------------------
3852
3853 Procedure call_workflow
3854 (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3855 p_action_taken in ghr_pa_routing_history.action_taken%type,
3856 p_old_action_taken in ghr_pa_routing_history.action_taken%type default null,
3857 p_error in varchar2 default null
3858 )
3859 is
3860 --
3861 l_proc varchar2(72) := 'Call_workflow';
3862 l_pa_routing_history_rec ghr_pa_routing_history%rowtype;
3863 l_forward_to_name ghr_pa_routing_history.user_name%type;
3864 l_cnt number;
3865 l_act_cnt number;
3866 l_groupbox_id number;
3867 l_user_name ghr_pa_routing_history.user_name%type;
3868 l_object_version_number ghr_pa_requests.object_version_number%type;
3869
3870 Cursor c_par is
3871 select par.object_version_number
3872 from ghr_pa_requests par
3873 where par.pa_request_id = p_pa_request_id;
3874
3875 Cursor c_routing_count is
3876 select count(*) cnt
3877 from ghr_pa_routing_history
3878 where pa_request_id = p_pa_request_id;
3879
3880 Cursor c_routing_action is
3881 select count(1) act_cnt
3882 from ghr_pa_routing_history
3883 where pa_request_id = p_pa_request_id
3884 and action_taken = 'INITIATED';
3885 /* Bug# 5634964. The existing code is like l_cnt =2 and l_cnt>2 are wrote without considering the action_taken
3886 of 'INITIATED'. When user routed to inbox there will be one record in the ghr_pa_routing_history table with
3887 action_taken as 'INTIATED'. To work existing code for INITIATED actoons, added the above cursor*/
3888
3889 Cursor c_forwarding_name is
3890 select pa_routing_history_id,
3891 pa_request_id,
3892 initiator_flag,
3893 requester_flag,
3894 authorizer_flag,
3895 personnelist_flag,
3896 approver_flag,
3897 reviewer_flag,
3898 approved_flag,
3899 user_name,
3900 user_name_employee_id,
3901 user_name_emp_first_name,
3902 user_name_emp_last_name,
3903 user_name_emp_middle_names,
3904 groupbox_id,
3905 notepad ,
3906 routing_list_id,
3907 routing_seq_number,
3908 nature_of_action_id,
3909 noa_family_code,
3910 second_nature_of_action_id,
3911 object_version_number
3912 from ghr_pa_routing_history
3913 where pa_request_id = p_pa_request_id
3914 order by pa_routing_history_id desc;
3915
3916 Cursor c_groupbox_name is
3917 select name
3918 from ghr_groupboxes
3919 where groupbox_id = l_pa_routing_history_rec.groupbox_id;
3920
3921 begin
3922
3923 hr_utility.set_location('Entering ' || l_proc,5);
3924
3925 If p_action_taken not in ('NOT_ROUTED','UPDATE_HR','CONTINUE') then
3926 hr_utility.set_location(l_proc || p_action_taken,10);
3927 for routing_rec in c_routing_count loop
3928 hr_utility.set_location(l_proc,15);
3929 l_cnt := routing_rec.cnt;
3930 -- Begin Bug# 5634964
3931 IF p_action_taken not in('INITIATED') and p_old_action_taken = 'FUTURE_ACTION' THEN
3932 for routing_action in c_routing_action loop
3933 l_act_cnt := routing_action.act_cnt;
3934 end loop;
3935 l_cnt := l_cnt - nvl(l_act_cnt,0);
3936 END IF;
3937 -- End Bug# 5634964
3938 end loop;
3939 -- The very fact that it is a 2nd routing history and action taken is not 'UPDATE_HR_COMPLETE' means
3940 -- work flow needs to be initiated. In all other cases , the blockingofparequest needs to be called
3941 If l_cnt = 2 then
3942 hr_utility.set_location(l_proc ||p_action_taken,20);
3943 If p_action_taken not in ('FUTURE_ACTION','CANCELED') then
3944 hr_utility.set_location('not FA or CAncel',1);
3945 If (p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED') and
3946 p_old_action_taken = 'FUTURE_ACTION' then
3947 hr_utility.set_location('nothing',1);
3948 Null;
3949 Else --(p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED')
3950 hr_utility.set_location('else not Update Coplete',1);
3951 for forwarding_name in c_forwarding_name loop
3952 hr_utility.set_location(l_proc,25);
3953 l_pa_routing_history_rec.groupbox_id := forwarding_name.groupbox_id;
3954 l_pa_routing_history_rec.user_name := forwarding_name.user_name;
3955 hr_utility.set_location('groupbox ' || l_pa_routing_history_rec.groupbox_id,1);
3956 hr_utility.set_location('username ' || l_pa_routing_history_rec.user_name,1);
3957 exit;
3958 end loop;
3959 If l_pa_routing_history_rec.user_name is null then
3960 hr_utility.set_location(l_proc || 'user not null',30);
3961 for groupbox_name in c_groupbox_name loop
3962 hr_utility.set_location(l_proc,35);
3963 l_forward_to_name := groupbox_name.name;
3964 end loop;
3965 Else
3966 l_forward_to_name := l_pa_routing_history_rec.user_name;
3967 End if;
3968 If l_forward_to_name is null then
3969 hr_utility.set_message(8301,'GHR_38276_FORWARD_NAME_REQD');
3970 hr_utility.raise_error;
3971 End if;
3972 hr_utility.set_location(l_proc,40);
3973 ghr_wf_pkg.startsf52process
3974 (p_pa_request_id => p_pa_request_id,
3975 p_forward_to_name => l_forward_to_name,
3976 p_error_msg => p_error
3977 );
3978 End if; --(p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED')
3979 hr_utility.set_location('after call to start sf52' || p_old_action_taken,11);
3980 Elsif p_action_taken in ('FUTURE_ACTION','CANCELED')then
3981 hr_utility.set_location(l_proc,43);
3982 ghr_wf_pkg.CompleteBlockingOfparequest
3983 (p_pa_request_id => p_pa_request_id,
3984 p_error_msg => p_error
3985 );
3986 End if;--p_action_taken not in ('FUTURE_ACTION','CANCELED')
3987
3988 -- If there are more than 2 routing history records, then workflow has already started and
3989 -- now we need to know whether to transfer OUT to blockingoffutureactions of blockingofparequest
3990 Elsif l_cnt > 2 then
3991 hr_utility.set_location('Old Action Taken ' || p_old_action_taken,1);
3992 hr_utility.set_location(l_proc,45);
3993 If nvl(p_old_action_taken,hr_api.g_varchar2) = 'FUTURE_ACTION' and
3994 p_action_taken ='UPDATE_HR_COMPLETE' then
3995 ghr_wf_pkg.completeblockingoffutureaction
3996 (p_pa_request_id => p_pa_request_id,
3997 p_action_taken => p_action_taken,
3998 p_error_msg => p_error);
3999 Else
4000 ghr_wf_pkg.CompleteBlockingOfparequest
4001 (p_pa_request_id => p_pa_request_id,
4002 p_error_msg => p_error);
4003 End if;
4004 End if; --l_cnt = 2
4005 End if; -- p_action_taken not in ('NOT_ROUTED','UPDATE_HR','CONTINUE')
4006
4007 If p_action_taken = 'CONTINUE' then
4008 hr_utility.set_location('continue',1);
4009 -- Update the current status of the PA Request with the next in hierarchy (after update_hr,future_action), which is APPROVED.
4010 for par in c_par loop
4011 l_object_version_number := par.object_version_number;
4012 end loop;
4013 ghr_par_upd.upd
4014 (p_pa_request_id => p_pa_request_id,
4015 p_status => 'APPROVED',
4016 p_object_version_number => l_object_version_number);
4017
4018 for routing_rec in c_routing_count loop
4019 l_cnt := routing_rec.cnt;
4020 end loop;
4021
4022 for forwarding_name in c_forwarding_name loop
4023 hr_utility.set_location(l_proc,25);
4024 hr_utility.set_location('inside rh loop',2);
4025 l_pa_routing_history_rec.pa_routing_history_id := forwarding_name.pa_routing_history_id;
4026 l_pa_routing_history_rec.pa_request_id := forwarding_name.pa_request_id;
4027 l_pa_routing_history_rec.initiator_flag := forwarding_name.initiator_flag;
4028 l_pa_routing_history_rec.requester_flag := forwarding_name.requester_flag;
4029 l_pa_routing_history_rec.authorizer_flag := forwarding_name.authorizer_flag;
4030 l_pa_routing_history_rec.personnelist_flag := forwarding_name.personnelist_flag;
4031 l_pa_routing_history_rec.approver_flag := forwarding_name.approver_flag;
4032 l_pa_routing_history_rec.reviewer_flag := forwarding_name.reviewer_flag;
4033 l_pa_routing_history_rec.approved_flag := forwarding_name.approved_flag;
4034 l_pa_routing_history_rec.user_name := forwarding_name.user_name;
4035 l_pa_routing_history_rec.user_name_employee_id := forwarding_name.user_name_employee_id;
4036 l_pa_routing_history_rec.user_name_emp_first_name := forwarding_name.user_name_emp_first_name;
4037 l_pa_routing_history_rec.user_name_emp_last_name := forwarding_name.user_name_emp_last_name;
4038 l_pa_routing_history_rec.user_name_emp_middle_names := forwarding_name.user_name_emp_middle_names;
4039 l_pa_routing_history_rec.groupbox_id := forwarding_name.groupbox_id;
4040 l_pa_routing_history_rec.routing_list_id := forwarding_name.routing_list_id;
4041 l_pa_routing_history_rec.routing_seq_number := forwarding_name.routing_seq_number;
4042 l_pa_routing_history_rec.notepad := forwarding_name.notepad;
4043 l_pa_routing_history_rec.nature_of_action_id := forwarding_name.nature_of_action_id;
4044 l_pa_routing_history_rec.second_nature_of_action_id := forwarding_name.second_nature_of_action_id;
4045 l_pa_routing_history_rec.noa_family_code := forwarding_name.noa_family_code;
4046 l_pa_routing_history_rec.object_version_number := forwarding_name.object_version_number;
4047 exit;
4048 end loop;
4049
4050 If l_pa_routing_history_rec.user_name is null then
4051 hr_utility.set_location('user name is null',1);
4052 hr_utility.set_location(l_proc,30);
4053 for groupbox_name in c_groupbox_name loop
4054 hr_utility.set_location('inside groupbox cursor',2);
4055 hr_utility.set_location(l_proc,35);
4056 l_forward_to_name := groupbox_name.name;
4057 end loop;
4058 Else
4059 l_forward_to_name := l_pa_routing_history_rec.user_name;
4060 End if;
4061 hr_utility.set_location('Forward to name : ' || l_forward_to_name,3);
4062 If l_forward_to_name is null then
4063 hr_utility.set_message(8301,'GHR_38276_FORWARD_NAME_REQD');
4064 hr_utility.raise_error;
4065 End if;
4066 hr_utility.set_location('Before call to prh' || l_proc,40);
4067
4068 ghr_prh_ins.ins
4069 (
4070 p_pa_routing_history_id => l_pa_routing_history_rec.pa_routing_history_id,
4071 p_pa_request_id => l_pa_routing_history_rec.pa_request_id,
4072 p_attachment_modified_flag => nvl(l_pa_routing_history_rec.attachment_modified_flag,'N') ,
4073 p_initiator_flag => nvl(l_pa_routing_history_rec.initiator_flag,'N'),
4074 p_approver_flag => nvl(l_pa_routing_history_rec.approver_flag,'N'),
4075 p_reviewer_flag => nvl(l_pa_routing_history_rec.reviewer_flag,'N') ,
4076 p_requester_flag => nvl(l_pa_routing_history_rec.requester_flag,'N') ,
4077 p_authorizer_flag => nvl(l_pa_routing_history_rec.authorizer_flag,'N'),
4078 p_personnelist_flag => nvl(l_pa_routing_history_rec.personnelist_flag,'N'),
4079 p_approved_flag => nvl(l_pa_routing_history_rec.approved_flag,'N'),
4080 p_user_name => l_pa_routing_history_rec.user_name,
4081 p_user_name_employee_id => l_pa_routing_history_rec.user_name_employee_id,
4082 p_user_name_emp_first_name => l_pa_routing_history_rec.user_name_emp_first_name,
4083 p_user_name_emp_last_name => l_pa_routing_history_rec.user_name_emp_last_name ,
4084 p_user_name_emp_middle_names => l_pa_routing_history_rec.user_name_emp_middle_names,
4085 p_groupbox_id => l_pa_routing_history_rec.groupbox_id,
4086 p_routing_seq_number => l_pa_routing_history_rec.routing_seq_number,
4087 p_routing_list_id => l_pa_routing_history_rec.routing_list_id,
4088 p_notepad => l_pa_routing_history_rec.notepad,
4089 p_nature_of_action_id => l_pa_routing_history_rec.nature_of_action_id,
4090 p_second_nature_of_action_id => l_pa_routing_history_rec.second_nature_of_action_id,
4091 p_noa_family_code => l_pa_routing_history_rec.noa_family_code,
4092 p_object_version_number => l_pa_routing_history_rec.object_version_number
4093 );
4094
4095 If l_cnt = 1 then
4099 p_error_msg => p_error);
4096 ghr_wf_pkg.startsf52process
4097 (p_pa_request_id => p_pa_request_id,
4098 p_forward_to_name => l_forward_to_name,
4100 Else
4101 ghr_wf_pkg.CompleteBlockingOfFutureAction
4102 (p_pa_request_id => p_pa_request_id
4103 ,p_action_taken => p_action_taken
4104 ,p_error_msg => p_error );
4105 End if;
4106 End if; --p_action_taken = 'CONTINUE'
4107
4108 End call_workflow;
4109
4110 FUNCTION restricted_attribute (
4111 p_user_name in VARCHAR2
4112 , p_attribute in VARCHAR2
4113 )
4114 RETURN BOOLEAN
4115 IS
4116
4117 CURSOR c_restricted IS
4118 SELECT rpm.restricted_proc_method
4119 FROM FND_USER USR
4120 , PER_PEOPLE_EXTRA_INFO PEI
4121 , GHR_RESTRICTED_PROC_METHODS RPM
4122 , GHR_PA_DATA_FIELDS PDF
4123 WHERE usr.user_name = p_user_name
4124 AND pdf.name = p_attribute
4125 AND pei.person_id = usr.employee_id
4126 AND pei.information_type = 'GHR_US_PER_USER_INFO'
4127 AND rpm.restricted_form = pei.pei_information3
4128 AND rpm.pa_data_field_id = pdf.pa_data_field_id;
4129
4130 l_restricted_proc_method VARCHAR2(30);
4131
4132 BEGIN
4133
4134 OPEN c_restricted;
4135 FETCH c_restricted INTO l_restricted_proc_method;
4136 CLOSE c_restricted;
4137
4138 RETURN NVL(l_restricted_proc_method, 'DO') = 'ND';
4139
4140 END restricted_attribute;
4141
4142
4143 end ghr_api;