[Home] [Help]
PACKAGE BODY: APPS.GHR_API
Source
1 PACKAGE BODY ghr_api AS
2 /* $Header: ghapiapi.pkb 120.15.12020000.1 2012/06/29 04:06:27 appldev 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
2923 --6850492
2924 p_job_id := l_assignment_data.job_id;
2925 p_organization_id := l_assignment_data.organization_id;
2926 p_grade_id := l_assignment_data.grade_id;
2927 --6850492
2928
2929 Ghr_History_Fetch.Fetch_ASGEI_prior_root_sf50(
2930 p_assignment_id => p_assignment_id,
2931 p_information_type => 'GHR_US_ASG_SF52',
2932 p_date_effective => l_effective_date,
2933 p_altered_pa_request_id => p_altered_pa_request_id,
2934 p_noa_id_corrected => p_noa_id_corrected,
2935 p_get_ovn_flag => 'Y',
2936 p_asgei_data => l_asg_ei_data
2937 );
2938 p_work_schedule := l_asg_ei_data.aei_information7;
2939 p_pay_rate_determinant := l_asg_ei_data.aei_information6;
2940
2941 Else
2942
2943 for per_type in c_per_type(p_person_id,l_effective_date) loop
2944 l_person_type := per_type.system_person_type;
2945 end loop;
2946
2947 -- VSM added following if stmt. and the TRUE result code to take care of refresh of correction SF52.
2948 if p_altered_pa_request_id is not NULL and
2949 p_noa_id_corrected is not NULL then
2950 if p_assignment_id is NULL then
2951 hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2952 hr_utility.raise_error;
2953 else
2954 ghr_history_fetch.fetch_assignment
2955 ( p_assignment_id => p_assignment_id
2956 ,p_date_effective => p_effective_date
2957 ,p_altered_pa_request_id => p_altered_pa_request_id
2958 ,p_noa_id_corrected => p_noa_id_corrected
2959 ,p_assignment_data => l_assignment_data
2960 ,p_pa_history_id => p_pa_history_id
2961 ,p_result_code => l_result_code);
2962 if l_result_code = 'not_found' then
2963 null;
2964 -- raise error.
2965 end if;
2966 p_duty_station_location_id := l_assignment_data.location_id;
2967 l_position_id := l_assignment_data.position_id;
2968 l_organization_id := l_assignment_data.organization_id;
2969 l_job_id := l_assignment_data.job_id;
2970 l_grade_id := l_assignment_data.grade_id;
2971 end if;
2972
2973 else
2974 if p_person_id is NULL and p_assignment_id is NULL then
2975 hr_utility.set_message(8301, 'GHR_38037_API_ARG_ERR');
2976 hr_utility.raise_error;
2977 elsif p_assignment_id is NULL then
2978 -- If we were not given an assignment id then we need to get the 'default'
2979 -- assignment. The definition of 'default' assignment is not yet clearly
2980 -- defined!
2981 -- Every person must have at least one assignment, now if they are an
2982 -- 'employee' then the 'default' will be the primary assignment (i.e. primary_flag = 'Y')
2983 -- since a person can have only one primary assignment.
2984 -- However if you are an 'applicant' we do not know which assignment to choose as
2985 -- they apparently do not have a primary assignment hence for the moment just chose the first
2986 -- one!!
2987 -- It is also not clear at the moment the exact definition of an 'employee' as opposed
2988 -- to an 'applicant'.
2989 -- In conclusion to all this we will first try and get the 'primary' assignment and
2990 -- if there isn't one we will just get the first one we can!
2991 for per_type in c_per_type(p_person_id,l_effective_date) loop
2992 l_person_type := per_type.system_person_type;
2993 end loop;
2994 If l_person_type = 'EX_EMP' then
2995 hr_utility.set_location('Ex Employee in conversion action',1);
2996 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
2997 l_asg_by_per_id_found := TRUE;
2998 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
2999 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
3000 l_organization_id := c_assignment_ex_emp_rec.organization_id;
3001 l_job_id := c_assignment_ex_emp_rec.job_id;
3002 l_position_id := c_assignment_ex_emp_rec.position_id;
3003 l_grade_id := c_assignment_ex_emp_rec.grade_id;
3004 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
3005
3006 EXIT;
3007 END LOOP;
3008
3009 Else
3010 --RP
3011 if l_person_type = 'EX_EMP' then
3012 hr_utility.set_location('Ex Employee in conversion action with asg id',1);
3013 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
3014 l_asg_by_per_id_found := TRUE;
3015 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
3016 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
3017 l_organization_id := c_assignment_ex_emp_rec.organization_id;
3018 l_job_id := c_assignment_ex_emp_rec.job_id;
3019 l_position_id := c_assignment_ex_emp_rec.position_id;
3020 l_grade_id := c_assignment_ex_emp_rec.grade_id;
3021 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
3022 EXIT;
3023 END LOOP;
3024 Else
3025
3026 FOR c_assignment_by_per_id_rec in c_assignment_by_per_id (p_person_id, l_effective_date) LOOP
3027 l_asg_by_per_id_found := TRUE;
3028 --
3029 p_assignment_id := c_assignment_by_per_id_rec.assignment_id;
3030 l_organization_id := c_assignment_by_per_id_rec.organization_id;
3031 l_job_id := c_assignment_by_per_id_rec.job_id;
3032 l_position_id := c_assignment_by_per_id_rec.position_id;
3033 l_grade_id := c_assignment_by_per_id_rec.grade_id;
3034 p_duty_station_location_id := c_assignment_by_per_id_rec.location_id;
3035 EXIT;
3036 END LOOP;
3037 IF NOT l_asg_by_per_id_found THEN
3038 -- Couldn't get a primary assignment so try for any other
3039 --
3040 FOR c_asg_by_per_id_not_prim_rec in c_asg_by_per_id_not_prim (p_person_id, l_effective_date)
3041 LOOP
3042 l_asg_by_per_id_found := TRUE;
3043 --
3044 p_assignment_id := c_asg_by_per_id_not_prim_rec.assignment_id;
3045 l_organization_id := c_asg_by_per_id_not_prim_rec.organization_id;
3046 l_job_id := c_asg_by_per_id_not_prim_rec.job_id;
3047 l_position_id := c_asg_by_per_id_not_prim_rec.position_id;
3048 l_grade_id := c_asg_by_per_id_not_prim_rec.grade_id;
3049 p_duty_station_location_id := c_asg_by_per_id_not_prim_rec.location_id;
3050
3051 EXIT;
3052 END LOOP;
3053 --
3054 END IF;
3055 End if;
3056 --
3057 IF NOT l_asg_by_per_id_found THEN
3058 hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
3059 hr_utility.raise_error;
3060 END IF;
3061 End if;
3062 ELSE -- p_assignment_id is not NULL
3063 hr_utility.set_location('asg id is not null : Asg Id : ' || p_assignment_id,1);
3064 If l_person_type = 'EX_EMP' then
3065 hr_utility.set_location('Ex Employee in conversion action',1);
3066 for c_assignment_ex_emp_rec in c_assignment_ex_emp loop
3067 l_asg_by_asg_id_found := TRUE;
3068 hr_utility.set_location('Ex Employee - with Asg , value found ',1);
3069 l_effective_date := c_assignment_ex_emp_rec.effective_end_date;
3070 p_assignment_id := c_assignment_ex_emp_rec.assignment_id;
3071 l_organization_id := c_assignment_ex_emp_rec.organization_id;
3072 l_job_id := c_assignment_ex_emp_rec.job_id;
3073 l_position_id := c_assignment_ex_emp_rec.position_id;
3074 l_grade_id := c_assignment_ex_emp_rec.grade_id;
3075 p_duty_station_location_id := c_assignment_ex_emp_rec.location_id;
3076 EXIT;
3077 END LOOP;
3078 Else
3079 for c_assignment_by_asg_id_rec
3080 in c_assignment_by_asg_id (p_assignment_id, l_effective_date) loop
3081 l_asg_by_asg_id_found := TRUE;
3082 l_organization_id := c_assignment_by_asg_id_rec.organization_id;
3083 l_job_id := c_assignment_by_asg_id_rec.job_id;
3084 l_position_id := c_assignment_by_asg_id_rec.position_id;
3085 l_grade_id := c_assignment_by_asg_id_rec.grade_id;
3086 p_duty_station_location_id := c_assignment_by_asg_id_rec.location_id;
3087 exit;
3088 end loop;
3089 End if;
3090 if not l_asg_by_asg_id_found then
3091 hr_utility.set_message(8301, 'GHR_38023_API_INV_ASG');
3092 hr_utility.raise_error;
3093 end if;
3094 END IF;
3095 END IF;
3096
3097 p_position_id := l_position_id;
3098 p_organization_id := l_organization_id;
3099 p_job_id := l_job_id;
3100 p_grade_id := l_grade_id;
3101
3102 hr_utility.set_location(l_proc, 2);
3103
3104 if p_altered_pa_request_id is not NULL and
3105 p_noa_id_corrected is not NULL then
3106 hr_utility.set_location(l_proc, 104);
3107
3108 /* retrieve_business_group_id
3109 ( p_person_id => p_person_id,
3110 p_effective_date => l_effective_date,
3111 p_altered_pa_request_id => p_altered_pa_request_id,
3112 p_noa_id_corrected => p_noa_id_corrected,
3113 p_pa_history_id => p_pa_history_id,
3114 p_business_group_id => l_business_group_id
3115 );
3116
3117 p_position_title := get_position_title_pos
3118 (p_position_id => l_position_id
3119 ,p_business_group_id => l_business_group_id ) ;
3120
3121 p_position_number := get_position_desc_no_pos
3122 (p_position_id => l_position_id
3123 ,p_business_group_id => l_business_group_id);
3124
3125 p_position_seq_no := get_position_sequence_no_pos
3126 (p_position_id => l_position_id
3127 ,p_business_group_id => l_business_group_id);
3128
3129 */
3130 else
3131 --
3132 -- Retrieve Position Title
3133 --
3134 p_position_title := get_position_title (p_person_id => p_person_id
3135 ,p_assignment_id => p_assignment_id
3136 ,p_effective_date => l_effective_date);
3137 hr_utility.set_location(l_proc, 3);
3138 --
3139 -- Retrieve Position Number
3140 --
3141 p_position_number := get_position_description_no
3142 (p_person_id => p_person_id
3143 ,p_assignment_id => p_assignment_id
3144 ,p_effective_date => l_effective_date);
3145
3146 p_position_seq_no := get_position_sequence_no
3147 (p_person_id => p_person_id
3148 ,p_assignment_id => p_assignment_id
3149 ,p_effective_date => l_effective_date);
3150 END IF;
3151
3152 hr_utility.set_location(l_proc, 4);
3153 --
3154 -- Retrieve Grade Key Flexfield Information
3155 --
3156 for c_grade_kff_rec in c_grade_kff (l_grade_id) loop
3157 l_grade_kff_found := TRUE;
3158 p_pay_plan := c_grade_kff_rec.segment1;
3159 p_grade_or_level := c_grade_kff_rec.segment2;
3160 exit;
3161 end loop;
3162 if not l_grade_kff_found then
3163 -- hr_utility.set_message(8301, 'GHR_38026_API_INV_GRD');
3164 -- hr_utility.raise_error;
3165 null;
3166 end if;
3167 hr_utility.set_location(l_proc, 5);
3168 --
3169 -- Retrieve Job Occupational Series
3170 --
3171 ghr_history_api.get_g_session_var(l_session);
3172 hr_utility.set_location('p_assignment id before getting job is ' || p_assignment_id,1);
3173 if l_session.noa_id_correct is null then --RP
3174 p_occ_code := get_job_occupational_series (p_person_id => p_person_id
3175 ,p_assignment_id => p_assignment_id
3176 ,p_effective_date => l_effective_date);
3177 else
3178 for job_id_rec in c_job_id loop
3179 l_job_id := job_id_rec.job_id;
3180 l_business_group_id := job_id_rec.business_group_id;
3181 exit;
3182 end loop;
3183 p_occ_code := get_job_occ_series_job (p_job_id => l_job_id
3184 ,p_business_group_id => l_business_group_id);
3185 end if; --RP
3186 hr_utility.set_location(l_proc, 6);
3187
3188
3189 -- VSM added following 2 parameters to all the calls to GHR_History_Fetch procedures
3190 -- ,p_altered_pa_request_id => p_altered_pa_request_id
3191 -- ,p_noa_id_corrected => p_noa_id_corrected
3192
3193
3194 --
3195 -- Retrieve Assignment Developer Descriptive Flexfield Information
3196 --
3197
3198 -- This procedures uses session variables for p_altered)pa_request_id and p_noa_id_corrected
3199 ghr_history_fetch.fetch_asgei (p_assignment_id => p_assignment_id
3200 ,p_information_type => 'GHR_US_ASG_SF52'
3201 ,p_date_effective => l_effective_date
3202 ,p_asg_ei_data => l_asg_ei_data
3203 );
3204
3205 p_step_or_rate := l_asg_ei_data.aei_information3;
3206
3207 hr_utility.set_location(l_proc, 7);
3208 --
3209 -- Retrieve Position Developer Descriptive Flexfield Information
3210 --
3211 -- This procedures uses session variables for p_altered)pa_request_id and p_noa_id_corrected
3212 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3213 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
3214 ,p_date_effective => l_effective_date
3215 ,p_pos_ei_data => l_pos_ei_data
3216 );
3217 -- FWFA Changes Bug# 4444609
3218 l_pay_basis := l_pos_ei_data.poei_information6;
3219 l_pay_table_identifier := l_pos_ei_data.poei_information5;
3220 -- FWFA Changes
3221
3222 hr_utility.set_location(l_proc, 8);
3223 --
3224 -- Retrieve work_Schedule -- (Added for OGSD)
3225
3226 If p_assignment_id is null then
3227 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3228 ,p_information_type => 'GHR_US_POS_GRP1'
3229 ,p_date_effective => l_effective_date
3230 ,p_pos_ei_data => l_pos_ei_data
3231 );
3232
3233 p_work_Schedule := l_pos_ei_data.poei_information10;
3234 hr_utility.set_location(l_proc,9);
3235 Else
3236
3237 -- VSM Prior PRD and Work Schedule
3238 Ghr_History_Fetch.Fetch_ASGEI_prior_root_sf50(
3239 p_assignment_id => p_assignment_id,
3240 p_information_type => 'GHR_US_ASG_SF52',
3241 p_date_effective => l_effective_date,
3242 p_altered_pa_request_id => p_altered_pa_request_id,
3243 p_noa_id_corrected => p_noa_id_corrected,
3244 p_get_ovn_flag => 'Y',
3245 p_asgei_data => l_asg_ei_data);
3246
3247 p_work_schedule := l_asg_ei_data.aei_information7;
3248 p_pay_rate_determinant := l_asg_ei_data.aei_information6;
3249
3250 End if;
3251
3252 --Start New Retained Grade Processing
3253 hr_utility.set_location('PRD is ' ||p_pay_rate_determinant,9);
3254 IF p_pay_rate_determinant IN ('A','B','E','F','U','V') THEN
3255 -- use retained details...
3256 BEGIN
3257 l_retained_grade :=
3258 ghr_pc_basic_pay.get_retained_grade_details
3259 (p_person_id
3260 ,l_effective_date);
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 BEGIN
3264 l_retained_grade :=
3265 ghr_pc_basic_pay.get_retained_grade_details
3266 (p_person_id
3267 ,(l_effective_date - 1));
3268 EXCEPTION
3269 WHEN OTHERS THEN
3270 hr_utility.set_message(8301,'GHR_38699_MISSING_RETAINED_DET');
3271 hr_utility.raise_error;
3272 END;
3273 END;
3274 if l_retained_grade.temp_step is NULL THEN -- Temp. Promo RG Changes
3275 l_update34_date := ghr_pay_caps.update34_implemented_date(p_person_id);
3276 hr_utility.set_location('Update 34 date is ' ||l_update34_date,10);
3277 hr_utility.set_location('Effective date is ' ||l_effective_date,11);
3278 if l_update34_date is not null AND l_effective_date >= l_update34_date then
3279 p_pay_basis := l_retained_grade.pay_basis;
3280 -- FWFA Changes Bug#4444609
3281 p_pay_table_identifier := l_retained_grade.user_table_id;
3282 -- FWFA Changes
3283 hr_utility.set_location('RET 1 pay basis is ' ||p_pay_basis,11);
3284 else
3285 -- FWFA Changes Bug#4444609
3286 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3287 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3288 p_pay_table_identifier := l_pay_table_identifier;
3289 p_pay_basis := l_pay_basis;
3290 -- FWFA Changes
3291 hr_utility.set_location('POS 1 pay basis is ' ||p_pay_basis,11);
3292 end if;
3293 ELSE
3294 -- FWFA Changes Bug#4444609
3295 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3296 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3297 p_pay_table_identifier := l_pay_table_identifier;
3298 p_pay_basis := l_pay_basis;
3299 -- FWFA Changes
3300 END IF;
3301 ELSE
3302 -- FWFA Changes Bug#4444609
3303 -- Modified the p_pay_basis assignment to l_pay_basis as there may be a chance of
3304 -- l_pos_ei_data.poei_information6 could be of GHR_US_POS_GRP1
3305 p_pay_table_identifier := l_pay_table_identifier;
3306 p_pay_basis := l_pay_basis;
3307 -- FWFA Changes
3308 hr_utility.set_location('POS 2 pay basis is ' ||p_pay_basis,11);
3309 END IF;
3310 hr_utility.set_location('Final from pay basis is ' ||p_pay_basis,11);
3311 --End New Retained Grade Processing
3312
3313 -- Changed for Basic Salary Rate
3314 --
3315 retrieve_element_entry_value (p_element_name => 'Basic Salary Rate'
3316 ,p_input_value_name => 'Rate'
3317 ,p_assignment_id => p_assignment_id
3318 ,p_effective_date => l_effective_date
3319 ,p_value => l_basic_pay
3320 ,p_multiple_error_flag => l_multi_error_flag);
3321 /* if l_basic_pay is NULL then
3322 l_basic_pay := 0;
3323 end if;
3324 */
3325 if l_multi_error_flag then
3326 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3327 hr_utility.raise_error;
3328 end if;
3329 --
3330
3331 -- FWFA Changes Bug#4444609 Modified 'Locality Pay' to 'Locality Pay or SR Supplement'
3332 retrieve_element_entry_value (p_element_name => 'Locality Pay or SR Supplement'
3333 -- FWFA Changes
3334 ,p_input_value_name => 'Rate'
3335 -- Changed from 'Amount' to 'Rate' by Ashu Gupta
3336 ,p_assignment_id => p_assignment_id
3337 ,p_effective_date => l_effective_date
3338 ,p_value => l_locality_adj
3339 ,p_multiple_error_flag => l_multi_error_flag);
3340 /*if l_locality_adj is NULL then
3341 l_locality_adj := 0;
3342 end if;
3343 */
3344 if l_multi_error_flag then
3345 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3346 hr_utility.raise_error;
3347 end if;
3348 --
3349
3350 -- Processing Total Pay and Adjusted Basic Pay
3351 -- NAME DATE BUG COMMENTS
3352 -- Ashley 17-JUL-03 Payroll Intg Modified the Input Value name
3353 -- Changes from Total Salary -> Amount
3354 -- Adjusted Pay -> Amount
3355 --
3356
3357 retrieve_element_entry_value (p_element_name => 'Adjusted Basic Pay'
3358 ,p_input_value_name => 'Amount'
3359 ,p_assignment_id => p_assignment_id
3360 ,p_effective_date => l_effective_date
3361 ,p_value => l_adj_basic_pay
3362 ,p_multiple_error_flag => l_multi_error_flag);
3363 /*if l_adj_basic_pay is NULL then
3364 l_adj_basic_pay := 0;
3365 end if;
3366 */
3367 if l_multi_error_flag then
3368 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3369 hr_utility.raise_error;
3370 end if;
3371 --
3372 --
3373 -- 7/28/97 Added retrieval of Other pay and the 7 items that make it up!
3374 retrieve_element_entry_value (p_element_name =>'Other Pay'
3375 ,p_input_value_name => 'Amount'
3376 ,p_assignment_id => p_assignment_id
3377 ,p_effective_date => l_effective_date
3378 ,p_value => l_other_pay
3379 ,p_multiple_error_flag => l_multi_error_flag);
3380 /*if l_other_pay is NULL then
3381 l_other_pay := 0;
3382 end if;
3383 */
3384 if l_multi_error_flag then
3385 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3386 hr_utility.raise_error;
3387 end if;
3388 --
3389
3390
3391 retrieve_element_entry_value (p_element_name => 'Total Pay'
3392 ,p_input_value_name => 'Amount'
3393 ,p_assignment_id => p_assignment_id
3394 ,p_effective_date => l_effective_date
3395 ,p_value => l_total_salary
3396 ,p_multiple_error_flag => l_multi_error_flag);
3397 /* if l_total_salary is NULL then
3398 l_total_salary := 0;
3399 end if;
3400 */
3401 if l_multi_error_flag then
3402 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3403 hr_utility.raise_error;
3404 end if;
3405 --
3406 --
3407 p_total_salary := round(l_total_salary,2);
3408 p_basic_pay := round(l_basic_pay,2);
3409 p_locality_adj := round(l_locality_adj,0);
3410 p_adj_basic_pay := round(l_adj_basic_pay,2);
3411 p_other_pay := l_other_pay;
3412 --
3413 -- Retrieve Organzation Information
3414 --
3415 hr_utility.set_location(l_proc, 9);
3416 --
3417 -- Retrieve Position's Organization's address lines
3418
3419
3420 ghr_history_fetch.fetch_positionei (p_position_id => l_position_id
3421 ,p_information_type => 'GHR_US_POS_GRP1'
3422 ,p_date_effective => p_effective_date
3423 ,p_pos_ei_data => l_pos_ei_data
3424 );
3425 l_org_id := l_pos_ei_data.poei_information21;
3426
3427 If l_org_id is not null then
3428 for org_address in c_org_address(l_org_id) loop
3429 p_position_org_line1 := org_address.org_information5;
3430 p_position_org_line2 := org_address.org_information6;
3431 p_position_org_line3 := org_address.org_information7;
3432 p_position_org_line4 := org_address.org_information8;
3433 p_position_org_line5 := org_address.org_information9;
3434 p_position_org_line6 := org_address.org_information10;
3435 End loop;
3436 End if;
3437 End if;
3438 --
3439 retrieve_element_entry_value (p_element_name => 'AUO'
3440 ,p_input_value_name => 'Amount'
3441 ,p_assignment_id => p_assignment_id
3442 ,p_effective_date => l_effective_date
3443 ,p_value => l_au_overtime
3444 ,p_multiple_error_flag => l_multi_error_flag);
3445 /*if l_au_overtime is NULL then
3446 l_au_overtime := 0;
3447 end if;
3448 */
3449 if l_multi_error_flag then
3450 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3451 hr_utility.raise_error;
3452 end if;
3453 --
3454 retrieve_element_entry_value (p_element_name => 'AUO'
3455 ,p_input_value_name => 'Premium Pay Ind'
3456 ,p_assignment_id => p_assignment_id
3457 ,p_effective_date => l_effective_date
3458 ,p_value => l_auo_premium_pay_indicator
3459 ,p_multiple_error_flag => l_multi_error_flag);
3460 /*if l_auo_premium_pay_indicator is NULL then
3461 l_auo_premium_pay_indicator := 0;
3462 end if;
3463 */
3464 if l_multi_error_flag then
3465 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3466 hr_utility.raise_error;
3467 end if;
3468 --
3469 retrieve_element_entry_value (p_element_name => 'Availability Pay'
3470 ,p_input_value_name => 'Amount'
3471 ,p_assignment_id => p_assignment_id
3472 ,p_effective_date => l_effective_date
3473 ,p_value => l_availability_pay
3474 ,p_multiple_error_flag => l_multi_error_flag);
3475 /* if l_availability_pay is NULL then
3476 l_availability_pay := 0;
3477 end if;
3478 */
3479 if l_multi_error_flag then
3480 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3481 hr_utility.raise_error;
3482 end if;
3483 --
3484
3485 retrieve_element_entry_value (p_element_name => 'Availability Pay'
3486 ,p_input_value_name => 'Premium Pay Ind'
3487 ,p_assignment_id => p_assignment_id
3488 ,p_effective_date => l_effective_date
3489 ,p_value => l_ap_premium_pay_indicator
3490 ,p_multiple_error_flag => l_multi_error_flag);
3491 /* if l_ap_premium_pay_indicator is NULL then
3492 l_ap_premium_pay_indicator := 0;
3493 end if;
3494 */
3495 if l_multi_error_flag then
3496 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3497 hr_utility.raise_error;
3498 end if;
3499 --
3500 retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3501 ,p_input_value_name => 'Amount'
3502 ,p_assignment_id => p_assignment_id
3503 ,p_effective_date => l_effective_date
3504 ,p_value => l_retention_allowance
3505 ,p_multiple_error_flag => l_multi_error_flag);
3506 -- added 06-Oct by Sue
3507 retrieve_element_entry_value (p_element_name => 'Retention Allowance'
3508 ,p_input_value_name => 'Percentage'
3509 ,p_assignment_id => p_assignment_id
3510 ,p_effective_date => l_effective_date
3511 ,p_value => l_retention_allow_percentage
3512 ,p_multiple_error_flag => l_multi_error_flag);
3513 /*if l_retention_allowance is NULL then
3514 l_retention_allowance := 0;
3515 end if;
3516 */
3517 if l_multi_error_flag then
3518 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3519 hr_utility.raise_error;
3520 end if;
3521 --
3522 retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3523 ,p_input_value_name => 'Amount'
3524 ,p_assignment_id => p_assignment_id
3525 ,p_effective_date => l_effective_date
3526 ,p_value => l_supervisory_differential
3527 ,p_multiple_error_flag => l_multi_error_flag);
3528 -- added 06-Oct by Sue
3529 retrieve_element_entry_value (p_element_name => 'Supervisory Differential'
3530 ,p_input_value_name => 'Percentage'
3531 ,p_assignment_id => p_assignment_id
3532 ,p_effective_date => p_effective_date
3533 ,p_value => l_supervisory_diff_percentage
3534 ,p_multiple_error_flag => l_multi_error_flag);
3535 /* if l_supervisory_differential is NULL then
3536 l_supervisory_differential := 0;
3537 end if;
3538 */
3539 if l_multi_error_flag then
3540 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3541 hr_utility.raise_error;
3542 end if;
3543 --
3544 retrieve_element_entry_value (p_element_name => 'Staffing Differential'
3545 ,p_input_value_name => 'Amount'
3546 ,p_assignment_id => p_assignment_id
3547 ,p_effective_date => l_effective_date
3548 ,p_value => l_staffing_differential
3549 ,p_multiple_error_flag => l_multi_error_flag);
3550 -- added 06-Oct by Sue
3551 retrieve_element_entry_value (p_element_name => 'Staffing Differential'
3552 ,p_input_value_name => 'Percent'
3553 ,p_assignment_id => p_assignment_id
3554 ,p_effective_date => l_effective_date
3555 ,p_value => l_staffing_diff_percentage
3556 ,p_multiple_error_flag => l_multi_error_flag);
3557 /*if l_staffing_differential is NULL then
3558 l_staffing_differential := 0;
3559 end if;
3560 */
3561 if l_multi_error_flag then
3562 hr_utility.set_message(8301, 'GHR_38014_API_MULTI_ELE_ENTR');
3563 hr_utility.raise_error;
3564 end if;
3565 --
3566 p_au_overtime := l_au_overtime;
3567 p_auo_premium_pay_indicator := l_auo_premium_pay_indicator;
3568 p_availability_pay := l_availability_pay;
3569 p_ap_premium_pay_indicator := l_ap_premium_pay_indicator;
3570 p_retention_allowance := l_retention_allowance;
3571 p_retention_allow_percentage := l_retention_allow_percentage;
3572 p_supervisory_differential := l_supervisory_differential;
3573 p_supervisory_diff_percentage := l_supervisory_diff_percentage;
3574 p_staffing_differential := l_staffing_differential;
3575 p_staffing_diff_percentage := l_staffing_diff_percentage;
3576 hr_utility.set_location(' Leaving:'||l_proc, 20);
3577 EXCEPTION
3578 when others then
3579 -- NOCOPY Changes
3580 -- Reset IN OUT params and Set OUT params to null
3581 p_assignment_id := v_assignment_id;
3582 p_position_title := null;
3583 p_position_number := null;
3584 p_position_seq_no := null;
3585 p_pay_plan := null;
3586 p_job_id := null;
3587 p_occ_code := null;
3588 p_grade_id := null;
3589 p_grade_or_level := null;
3590 p_step_or_rate := null;
3591 p_total_salary := null;
3592 p_pay_basis := null;
3593 -- FWFA Changes Bug#4444609
3594 p_pay_table_identifier := null;
3595 -- FWFA Changes
3596 p_basic_pay := null;
3597 p_locality_adj := null;
3598 p_adj_basic_pay := null;
3599 p_other_pay := null;
3600 p_au_overtime := null;
3601 p_auo_premium_pay_indicator := null;
3602 p_availability_pay := null;
3603 p_ap_premium_pay_indicator := null;
3604 p_retention_allowance := null;
3605 p_retention_allow_percentage := null;
3606 p_supervisory_differential := null;
3607 p_supervisory_diff_percentage := null;
3608 p_staffing_differential := null;
3609 p_staffing_diff_percentage := null;
3610 p_organization_id := null;
3611 p_position_org_line1 := null;
3612 p_position_org_line2 := null;
3613 p_position_org_line3 := null;
3614 p_position_org_line4 := null;
3615 p_position_org_line5 := null;
3616 p_position_org_line6 := null;
3617 p_position_id := null;
3618 p_duty_station_location_id := null;
3619 p_pay_rate_determinant := null;
3620 p_work_schedule := null;
3621 raise;
3622 end sf52_from_data_elements;
3623 --
3624 --
3625 -- ---------------------------------------------------------------------------
3626 -- |--------------------------< return_upd_hr_dml_status >----------------|
3627 -- --------------------------------------------------------------------------
3628 FUNCTION return_upd_hr_dml_status RETURN BOOLEAN IS
3629 l_proc varchar2(72) := g_package||'return_upd_hr_dml_status';
3630 begin
3631 hr_utility.set_location('Entering:'||l_proc,5);
3632 return (nvl(g_api_dml, false));
3633 hr_utility.set_location(' Leaving:'||l_proc,10);
3634 end return_upd_hr_dml_status;
3635
3636 -- ---------------------------------------------------------------------------
3637 -- |--------------------------< return_special_information >----------------|
3638 -- --------------------------------------------------------------------------
3639
3640 Procedure return_special_information
3641 (p_person_id in number
3642 ,p_structure_name in varchar2
3643 ,p_effective_date in date
3644 ,p_special_info OUT NOCOPY ghr_api.special_information_type
3645 )
3646 is
3647 l_proc varchar2(72) := 'return_special_information ';
3648 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
3649 l_max_segment per_analysis_criteria.segment1%type;
3650
3651 Cursor c_flex_num is
3652 select flx.id_flex_num
3653 from fnd_id_flex_structures_tl flx
3654 where flx.id_flex_code = 'PEA' --
3655 and flx.application_id = 800 --
3656 and flx.id_flex_structure_name = p_structure_name
3657 and flx.language = 'US';
3658
3659 Cursor c_sit is
3660 select pea.analysis_criteria_id,
3661 pan.date_from, -- added for bug fix : 609285
3662 pan.person_analysis_id,
3663 pan.object_version_number,
3664 pea.start_date_active,
3665 pea.segment1,
3666 pea.segment2,
3667 pea.segment3,
3668 pea.segment4,
3669 pea.segment5,
3670 pea.segment6,
3671 pea.segment7,
3672 pea.segment8,
3673 pea.segment9,
3674 pea.segment10,
3675 pea.segment11,
3676 pea.segment12,
3677 pea.segment13,
3678 pea.segment14,
3679 pea.segment15,
3680 pea.segment16,
3681 pea.segment17,
3682 pea.segment18,
3683 pea.segment19,
3684 pea.segment20,
3685 pea.segment21,
3686 pea.segment22,
3687 pea.segment23,
3688 pea.segment24,
3689 pea.segment25,
3690 pea.segment26,
3691 pea.segment27,
3692 pea.segment28,
3693 pea.segment29,
3694 pea.segment30
3695 from per_analysis_Criteria pea,
3696 per_person_analyses pan
3697 where pan.person_id = p_person_id
3698 and pan.id_flex_num = l_id_flex_num
3699 and pea.analysis_Criteria_id = pan.analysis_criteria_id
3700 and p_effective_date
3701 between nvl(pan.date_from,p_effective_date)
3702 and nvl(pan.date_to,p_effective_date)
3703 and p_effective_date
3704 between nvl(pea.start_date_active,p_effective_date)
3705 and nvl(pea.end_date_active,p_effective_date)
3706 order by 2 desc, 3 desc;
3707
3708 begin
3709
3710 for flex_num in c_flex_num loop
3711 l_id_flex_num := flex_num.id_flex_num;
3712 End loop;
3713
3714 If l_id_flex_num is null then
3715 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3716 hr_utility.raise_error;
3717 End if;
3718
3719 for special_info in c_sit loop
3720 p_special_info.segment1 := special_info.segment1;
3721 p_special_info.segment2 := special_info.segment2;
3722 p_special_info.segment3 := special_info.segment3;
3723 p_special_info.segment4 := special_info.segment4;
3724 p_special_info.segment5 := special_info.segment5;
3725 p_special_info.segment6 := special_info.segment6;
3726 p_special_info.segment7 := special_info.segment7;
3727 p_special_info.segment8 := special_info.segment8;
3728 p_special_info.segment9 := special_info.segment9;
3729 p_special_info.segment10 := special_info.segment10;
3730 p_special_info.segment11 := special_info.segment11;
3731 p_special_info.segment12 := special_info.segment12;
3732 p_special_info.segment13 := special_info.segment13;
3733 p_special_info.segment14 := special_info.segment14;
3734 p_special_info.segment15 := special_info.segment15;
3735 p_special_info.segment16 := special_info.segment16;
3736 p_special_info.segment17 := special_info.segment17;
3737 p_special_info.segment18 := special_info.segment18;
3738 p_special_info.segment19 := special_info.segment19;
3739 p_special_info.segment20 := special_info.segment20;
3740 p_special_info.segment21 := special_info.segment21;
3741 p_special_info.segment22 := special_info.segment22;
3742 p_special_info.segment23 := special_info.segment23;
3743 p_special_info.segment24 := special_info.segment24;
3744 p_special_info.segment25 := special_info.segment25;
3745 p_special_info.segment26 := special_info.segment26;
3746 p_special_info.segment27 := special_info.segment27;
3747 p_special_info.segment28 := special_info.segment28;
3748 p_special_info.segment29 := special_info.segment29;
3749 p_special_info.segment30 := special_info.segment30;
3750 p_special_info.person_analysis_id := special_info.person_analysis_id;
3751 p_special_info.object_version_number := special_info.object_version_number;
3752
3753 exit;
3754 End loop;
3755 EXCEPTION
3756 when others then
3757 -- NOCOPY changes
3758 -- Reset IN OUT params and set OUT params
3759 p_special_info := null;
3760 raise;
3761 End return_special_information;
3762
3763
3764 -- ---------------------------------------------------------------------------
3765 -- |--------------------------< return_education_Details >----------------|
3766 -- --------------------------------------------------------------------------
3767
3768 Procedure return_education_Details
3769 (p_person_id in per_people_f.person_id%type,
3770 p_effective_date in date,
3771 p_education_level OUT NOCOPY per_analysis_criteria.segment1%type,
3772 p_academic_discipline OUT NOCOPY per_analysis_criteria.segment2%type,
3773 p_year_degree_attained OUT NOCOPY per_analysis_criteria.segment3%type
3774 )
3775 is
3776
3777 l_proc varchar2(72) := 'return_education_Details';
3778 l_special_info ghr_api.special_information_type;
3779 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
3780
3781 Cursor c_flex_num is
3782 select flx.id_flex_num
3783 from fnd_id_flex_structures_tl flx
3784 where flx.id_flex_code = 'PEA' --
3785 and flx.application_id = 800 --
3786 and flx.id_flex_structure_name = 'US Fed Education'
3787 and flx.language = 'US';
3788
3789 /*Cursor to get the highest education level for the person , as of the effective date */
3790
3791
3792 Cursor c_sit is
3793 select pea.segment1,
3794 pea.segment2,
3795 pea.segment3,
3796 pea.segment4,
3797 pea.segment5,
3798 pea.segment6,
3799 pea.segment7,
3800 pea.segment8,
3801 pea.segment9,
3802 pea.segment10,
3803 pea.segment11,
3804 pea.segment12,
3805 pea.segment13,
3806 pea.segment14,
3807 pea.segment15,
3808 pea.segment16,
3809 pea.segment17,
3810 pea.segment18,
3811 pea.segment19,
3812 pea.segment20
3813 from per_analysis_criteria pea,
3814 per_person_analyses pan
3815 where pan.person_id = p_person_id
3816 and pan.id_flex_num = l_id_flex_num
3817 and pea.id_flex_num = pan.id_flex_num
3818 and p_effective_date
3819 between nvl(pan.date_from,p_effective_date)
3820 and nvl(pan.date_to,p_effective_date)
3821 and p_effective_date
3822 between nvl(pea.start_date_active,p_effective_date)
3823 and nvl(pea.end_date_active,p_effective_date)
3824 and pan.analysis_criteria_id = pea.analysis_criteria_id
3825 order by 1 desc;
3826
3827 begin
3828
3829 hr_utility.set_location('Entering ' || l_proc,5);
3830
3831 for flex_num in c_flex_num loop
3832 hr_utility.set_location(l_proc,10);
3833 l_id_flex_num := flex_num.id_flex_num;
3834 end loop;
3835 hr_utility.set_location(l_proc,15);
3836 for sit in c_sit loop
3837 hr_utility.set_location(l_proc,20);
3838 p_education_level := sit.segment1;
3839 p_academic_discipline := sit.segment2;
3840 p_year_degree_attained := sit.segment3;
3841 exit;
3842 end loop;
3843 hr_utility.set_location(l_proc,25);
3844
3845 hr_utility.set_location('Leaving ' ||l_proc,30);
3846 EXCEPTION
3847 when others then
3848 -- NOCOPY changes
3849 -- Reset IN OUT params and set OUT params
3850 p_education_level := null;
3851 p_academic_discipline := null;
3852 p_year_degree_attained := null;
3853 raise;
3854 End return_education_details;
3855
3856 -- ---------------------------------------------------------------------------
3857 -- |--------------------------< call_work_flow>----------------|
3858 -- --------------------------------------------------------------------------
3859
3860 Procedure call_workflow
3861 (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3862 p_action_taken in ghr_pa_routing_history.action_taken%type,
3863 p_old_action_taken in ghr_pa_routing_history.action_taken%type default null,
3864 p_error in varchar2 default null
3865 )
3866 is
3867 --
3868 l_proc varchar2(72) := 'Call_workflow';
3869 l_pa_routing_history_rec ghr_pa_routing_history%rowtype;
3870 l_forward_to_name ghr_pa_routing_history.user_name%type;
3871 l_cnt number;
3872 l_act_cnt number;
3873 l_groupbox_id number;
3874 l_user_name ghr_pa_routing_history.user_name%type;
3875 l_object_version_number ghr_pa_requests.object_version_number%type;
3876
3877 Cursor c_par is
3878 select par.object_version_number
3879 from ghr_pa_requests par
3880 where par.pa_request_id = p_pa_request_id;
3881
3882 Cursor c_routing_count is
3883 select count(*) cnt
3884 from ghr_pa_routing_history
3885 where pa_request_id = p_pa_request_id;
3886
3887 Cursor c_routing_action is
3888 select count(1) act_cnt
3889 from ghr_pa_routing_history
3890 where pa_request_id = p_pa_request_id
3891 and action_taken = 'INITIATED';
3892 /* Bug# 5634964. The existing code is like l_cnt =2 and l_cnt>2 are wrote without considering the action_taken
3893 of 'INITIATED'. When user routed to inbox there will be one record in the ghr_pa_routing_history table with
3894 action_taken as 'INTIATED'. To work existing code for INITIATED actoons, added the above cursor*/
3895
3896 Cursor c_forwarding_name is
3897 select pa_routing_history_id,
3898 pa_request_id,
3899 initiator_flag,
3900 requester_flag,
3901 authorizer_flag,
3902 personnelist_flag,
3903 approver_flag,
3904 reviewer_flag,
3905 approved_flag,
3906 user_name,
3907 user_name_employee_id,
3908 user_name_emp_first_name,
3909 user_name_emp_last_name,
3910 user_name_emp_middle_names,
3911 groupbox_id,
3912 notepad ,
3913 routing_list_id,
3914 routing_seq_number,
3915 nature_of_action_id,
3916 noa_family_code,
3917 second_nature_of_action_id,
3918 object_version_number
3919 from ghr_pa_routing_history
3920 where pa_request_id = p_pa_request_id
3921 order by pa_routing_history_id desc;
3922
3923 Cursor c_groupbox_name is
3924 select name
3925 from ghr_groupboxes
3926 where groupbox_id = l_pa_routing_history_rec.groupbox_id;
3927
3928 begin
3929
3930 hr_utility.set_location('Entering ' || l_proc,5);
3931
3932 If p_action_taken not in ('NOT_ROUTED','UPDATE_HR','CONTINUE') then
3933 hr_utility.set_location(l_proc || p_action_taken,10);
3934 for routing_rec in c_routing_count loop
3935 hr_utility.set_location(l_proc,15);
3936 l_cnt := routing_rec.cnt;
3937 -- Begin Bug# 5634964
3938 IF p_action_taken not in('INITIATED') and p_old_action_taken = 'FUTURE_ACTION' THEN
3939 for routing_action in c_routing_action loop
3940 l_act_cnt := routing_action.act_cnt;
3941 end loop;
3942 l_cnt := l_cnt - nvl(l_act_cnt,0);
3943 END IF;
3944 -- End Bug# 5634964
3945 end loop;
3946 -- The very fact that it is a 2nd routing history and action taken is not 'UPDATE_HR_COMPLETE' means
3947 -- work flow needs to be initiated. In all other cases , the blockingofparequest needs to be called
3948 If l_cnt = 2 then
3949 hr_utility.set_location(l_proc ||p_action_taken,20);
3950 If p_action_taken not in ('FUTURE_ACTION','CANCELED') then
3951 hr_utility.set_location('not FA or CAncel',1);
3952 If (p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED') and
3953 p_old_action_taken = 'FUTURE_ACTION' then
3954 hr_utility.set_location('nothing',1);
3955 Null;
3956 Else --(p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED')
3957 hr_utility.set_location('else not Update Coplete',1);
3958 for forwarding_name in c_forwarding_name loop
3959 hr_utility.set_location(l_proc,25);
3960 l_pa_routing_history_rec.groupbox_id := forwarding_name.groupbox_id;
3961 l_pa_routing_history_rec.user_name := forwarding_name.user_name;
3962 hr_utility.set_location('groupbox ' || l_pa_routing_history_rec.groupbox_id,1);
3963 hr_utility.set_location('username ' || l_pa_routing_history_rec.user_name,1);
3964 exit;
3965 end loop;
3966 If l_pa_routing_history_rec.user_name is null then
3967 hr_utility.set_location(l_proc || 'user not null',30);
3968 for groupbox_name in c_groupbox_name loop
3969 hr_utility.set_location(l_proc,35);
3970 l_forward_to_name := groupbox_name.name;
3971 end loop;
3972 Else
3973 l_forward_to_name := l_pa_routing_history_rec.user_name;
3974 End if;
3975 If l_forward_to_name is null then
3976 hr_utility.set_message(8301,'GHR_38276_FORWARD_NAME_REQD');
3977 hr_utility.raise_error;
3978 End if;
3979 hr_utility.set_location(l_proc,40);
3980 ghr_wf_pkg.startsf52process
3981 (p_pa_request_id => p_pa_request_id,
3982 p_forward_to_name => l_forward_to_name,
3983 p_error_msg => p_error
3984 );
3985 End if; --(p_action_taken = 'UPDATE_HR_COMPLETE'or p_action_taken = 'ENDED')
3986 hr_utility.set_location('after call to start sf52' || p_old_action_taken,11);
3987 Elsif p_action_taken in ('FUTURE_ACTION','CANCELED')then
3988 hr_utility.set_location(l_proc,43);
3989 ghr_wf_pkg.CompleteBlockingOfparequest
3990 (p_pa_request_id => p_pa_request_id,
3991 p_error_msg => p_error
3992 );
3993 End if;--p_action_taken not in ('FUTURE_ACTION','CANCELED')
3994
3995 -- If there are more than 2 routing history records, then workflow has already started and
3996 -- now we need to know whether to transfer OUT to blockingoffutureactions of blockingofparequest
3997 Elsif l_cnt > 2 then
3998 hr_utility.set_location('Old Action Taken ' || p_old_action_taken,1);
3999 hr_utility.set_location(l_proc,45);
4000 If nvl(p_old_action_taken,hr_api.g_varchar2) = 'FUTURE_ACTION' and
4001 p_action_taken ='UPDATE_HR_COMPLETE' then
4002 ghr_wf_pkg.completeblockingoffutureaction
4003 (p_pa_request_id => p_pa_request_id,
4004 p_action_taken => p_action_taken,
4005 p_error_msg => p_error);
4006 Else
4007 ghr_wf_pkg.CompleteBlockingOfparequest
4008 (p_pa_request_id => p_pa_request_id,
4009 p_error_msg => p_error);
4010 End if;
4011 End if; --l_cnt = 2
4012 End if; -- p_action_taken not in ('NOT_ROUTED','UPDATE_HR','CONTINUE')
4013
4014 If p_action_taken = 'CONTINUE' then
4015 hr_utility.set_location('continue',1);
4016 -- Update the current status of the PA Request with the next in hierarchy (after update_hr,future_action), which is APPROVED.
4017 for par in c_par loop
4018 l_object_version_number := par.object_version_number;
4019 end loop;
4020 ghr_par_upd.upd
4021 (p_pa_request_id => p_pa_request_id,
4022 p_status => 'APPROVED',
4023 p_object_version_number => l_object_version_number);
4024
4025 for routing_rec in c_routing_count loop
4026 l_cnt := routing_rec.cnt;
4027 end loop;
4028
4029 for forwarding_name in c_forwarding_name loop
4030 hr_utility.set_location(l_proc,25);
4031 hr_utility.set_location('inside rh loop',2);
4032 l_pa_routing_history_rec.pa_routing_history_id := forwarding_name.pa_routing_history_id;
4033 l_pa_routing_history_rec.pa_request_id := forwarding_name.pa_request_id;
4034 l_pa_routing_history_rec.initiator_flag := forwarding_name.initiator_flag;
4035 l_pa_routing_history_rec.requester_flag := forwarding_name.requester_flag;
4036 l_pa_routing_history_rec.authorizer_flag := forwarding_name.authorizer_flag;
4037 l_pa_routing_history_rec.personnelist_flag := forwarding_name.personnelist_flag;
4038 l_pa_routing_history_rec.approver_flag := forwarding_name.approver_flag;
4039 l_pa_routing_history_rec.reviewer_flag := forwarding_name.reviewer_flag;
4040 l_pa_routing_history_rec.approved_flag := forwarding_name.approved_flag;
4041 l_pa_routing_history_rec.user_name := forwarding_name.user_name;
4042 l_pa_routing_history_rec.user_name_employee_id := forwarding_name.user_name_employee_id;
4043 l_pa_routing_history_rec.user_name_emp_first_name := forwarding_name.user_name_emp_first_name;
4044 l_pa_routing_history_rec.user_name_emp_last_name := forwarding_name.user_name_emp_last_name;
4045 l_pa_routing_history_rec.user_name_emp_middle_names := forwarding_name.user_name_emp_middle_names;
4046 l_pa_routing_history_rec.groupbox_id := forwarding_name.groupbox_id;
4047 l_pa_routing_history_rec.routing_list_id := forwarding_name.routing_list_id;
4048 l_pa_routing_history_rec.routing_seq_number := forwarding_name.routing_seq_number;
4049 l_pa_routing_history_rec.notepad := forwarding_name.notepad;
4050 l_pa_routing_history_rec.nature_of_action_id := forwarding_name.nature_of_action_id;
4051 l_pa_routing_history_rec.second_nature_of_action_id := forwarding_name.second_nature_of_action_id;
4052 l_pa_routing_history_rec.noa_family_code := forwarding_name.noa_family_code;
4053 l_pa_routing_history_rec.object_version_number := forwarding_name.object_version_number;
4054 exit;
4055 end loop;
4056
4057 If l_pa_routing_history_rec.user_name is null then
4058 hr_utility.set_location('user name is null',1);
4059 hr_utility.set_location(l_proc,30);
4060 for groupbox_name in c_groupbox_name loop
4061 hr_utility.set_location('inside groupbox cursor',2);
4062 hr_utility.set_location(l_proc,35);
4063 l_forward_to_name := groupbox_name.name;
4064 end loop;
4065 Else
4066 l_forward_to_name := l_pa_routing_history_rec.user_name;
4067 End if;
4068 hr_utility.set_location('Forward to name : ' || l_forward_to_name,3);
4069 If l_forward_to_name is null then
4070 hr_utility.set_message(8301,'GHR_38276_FORWARD_NAME_REQD');
4071 hr_utility.raise_error;
4072 End if;
4073 hr_utility.set_location('Before call to prh' || l_proc,40);
4074
4075 ghr_prh_ins.ins
4076 (
4077 p_pa_routing_history_id => l_pa_routing_history_rec.pa_routing_history_id,
4078 p_pa_request_id => l_pa_routing_history_rec.pa_request_id,
4079 p_attachment_modified_flag => nvl(l_pa_routing_history_rec.attachment_modified_flag,'N') ,
4080 p_initiator_flag => nvl(l_pa_routing_history_rec.initiator_flag,'N'),
4081 p_approver_flag => nvl(l_pa_routing_history_rec.approver_flag,'N'),
4082 p_reviewer_flag => nvl(l_pa_routing_history_rec.reviewer_flag,'N') ,
4083 p_requester_flag => nvl(l_pa_routing_history_rec.requester_flag,'N') ,
4084 p_authorizer_flag => nvl(l_pa_routing_history_rec.authorizer_flag,'N'),
4085 p_personnelist_flag => nvl(l_pa_routing_history_rec.personnelist_flag,'N'),
4086 p_approved_flag => nvl(l_pa_routing_history_rec.approved_flag,'N'),
4087 p_user_name => l_pa_routing_history_rec.user_name,
4088 p_user_name_employee_id => l_pa_routing_history_rec.user_name_employee_id,
4089 p_user_name_emp_first_name => l_pa_routing_history_rec.user_name_emp_first_name,
4090 p_user_name_emp_last_name => l_pa_routing_history_rec.user_name_emp_last_name ,
4091 p_user_name_emp_middle_names => l_pa_routing_history_rec.user_name_emp_middle_names,
4092 p_groupbox_id => l_pa_routing_history_rec.groupbox_id,
4093 p_routing_seq_number => l_pa_routing_history_rec.routing_seq_number,
4094 p_routing_list_id => l_pa_routing_history_rec.routing_list_id,
4095 p_notepad => l_pa_routing_history_rec.notepad,
4096 p_nature_of_action_id => l_pa_routing_history_rec.nature_of_action_id,
4097 p_second_nature_of_action_id => l_pa_routing_history_rec.second_nature_of_action_id,
4098 p_noa_family_code => l_pa_routing_history_rec.noa_family_code,
4099 p_object_version_number => l_pa_routing_history_rec.object_version_number
4100 );
4101
4102 If l_cnt = 1 then
4103 ghr_wf_pkg.startsf52process
4104 (p_pa_request_id => p_pa_request_id,
4105 p_forward_to_name => l_forward_to_name,
4106 p_error_msg => p_error);
4107 Else
4108 ghr_wf_pkg.CompleteBlockingOfFutureAction
4109 (p_pa_request_id => p_pa_request_id
4110 ,p_action_taken => p_action_taken
4111 ,p_error_msg => p_error );
4112 End if;
4113 End if; --p_action_taken = 'CONTINUE'
4114
4115 End call_workflow;
4116
4117 FUNCTION restricted_attribute (
4118 p_user_name in VARCHAR2
4119 , p_attribute in VARCHAR2
4120 )
4121 RETURN BOOLEAN
4122 IS
4123
4124 CURSOR c_restricted IS
4125 SELECT rpm.restricted_proc_method
4126 FROM FND_USER USR
4127 , PER_PEOPLE_EXTRA_INFO PEI
4128 , GHR_RESTRICTED_PROC_METHODS RPM
4129 , GHR_PA_DATA_FIELDS PDF
4130 WHERE usr.user_name = p_user_name
4131 AND pdf.name = p_attribute
4132 AND pei.person_id = usr.employee_id
4133 AND pei.information_type = 'GHR_US_PER_USER_INFO'
4134 AND rpm.restricted_form = pei.pei_information3
4135 AND rpm.pa_data_field_id = pdf.pa_data_field_id;
4136
4137 l_restricted_proc_method VARCHAR2(30);
4138
4139 BEGIN
4140
4141 OPEN c_restricted;
4142 FETCH c_restricted INTO l_restricted_proc_method;
4143 CLOSE c_restricted;
4144
4145 RETURN NVL(l_restricted_proc_method, 'DO') = 'ND';
4146
4147 END restricted_attribute;
4148
4149
4150 end ghr_api;