1 PACKAGE BODY ghr_utility AS
2 /* $Header: ghutils.pkb 120.20 2005/12/09 04:37:37 sumarimu noship $ */
3 --
4 -- Figure out whether GHR is installed or not.
5 -- if GHR_US_ORG_INFORMATION is defined for current business group
6 -- it is assumed that GHR is installed.
7 --
8 g_package VARCHAR2(30) := 'ghr_utility.';
9 v_current_bg NUMBER;
10 CURSOR c_fed_bg (p_current_bg NUMBER) IS
11 SELECT hoi.org_information_context
12 , hoi.org_information1
13 FROM hr_organization_information hoi
14 WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
15 AND hoi.organization_id = p_current_bg;
16
17 CURSOR c_fed_nfc (p_current_bg NUMBER) IS
18 SELECT hoi.org_information_context
19 , hoi.org_information6
20 FROM hr_organization_information hoi
21 WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
22 AND hoi.organization_id = p_current_bg
23 AND hoi.org_information6 = 'Y';
24
25 CURSOR c_ben_pgm (p_current_bg NUMBER) IS
26 SELECT 1
27 FROM ben_pgm_f
28 WHERE name = 'Federal Employees Health Benefits' and business_group_id = p_current_bg;
29
30 CURSOR c_ben_pgm_fehb (p_current_bg NUMBER) IS
31 SELECT 1
32 FROM ben_pgm_f
33 WHERE name = 'Federal Employees Health Benefits'
34 and business_group_id = p_current_bg
35 and pgm_stat_cd = 'A';
36
37 CURSOR c_ben_pgm_tsp (p_current_bg NUMBER) IS
38 SELECT 1
39 FROM ben_pgm_f
40 WHERE name = 'Federal Thrift Savings Plan (TSP)'
41 and business_group_id = p_current_bg
42 and pgm_stat_cd = 'A';
43
44 r_fed_bg c_fed_bg%ROWTYPE;
45 v_is_ghr VARCHAR2(10);
46 r_ben_pgm c_ben_pgm%ROWTYPE;
47 r_ben_pgm_fehb c_ben_pgm%ROWTYPE;
48 r_ben_pgm_tsp c_ben_pgm%ROWTYPE;
49 v_is_ghr_ben VARCHAR2(10);
50 v_is_ghr_ben_fehb VARCHAR2(10);
51 v_is_ghr_ben_tsp VARCHAR2(10);
52 r_ben_nfc c_fed_nfc%ROWTYPE;
53 v_is_ghr_nfc VARCHAR2(10);
54
55
56 --############### Function to get Flex Number #############################################
57
58 FUNCTION get_flex_num(p_flex_code fnd_id_flex_structures_tl.id_flex_code%TYPE,
59 p_struct_name fnd_id_flex_structures_tl.id_flex_structure_name%TYPE) RETURN NUMBER IS
60
61 CURSOR c_flex_num(c_flex_code fnd_id_flex_structures_tl.id_flex_code%TYPE,
62 c_struct_name fnd_id_flex_structures_tl.id_flex_structure_name%TYPE) IS
63 select flx.id_flex_num
64 from fnd_id_flex_structures_tl flx
65 where flx.id_flex_code = c_flex_code -- 'POS'
66 and flx.application_id = 800 --
67 and flx.id_flex_structure_name = c_struct_name -- 'US Federal Position'
68 and flx.language = 'US';
69 l_flex_num fnd_id_flex_structures_tl.id_flex_num%type;
70
71 BEGIN
72 -- Get Flex ID Number
73 FOR l_cur_flex_num IN c_flex_num(p_flex_code,p_struct_name) LOOP
74 l_flex_num := l_cur_flex_num.id_flex_num;
75 END LOOP;
76 RETURN l_flex_num;
77 END get_flex_num;
78
79
80 --############### Function to get Position Flex Number #############################################
81
82 FUNCTION get_pos_flex_num(p_bus_org_id hr_all_organization_units.business_group_id%type) RETURN NUMBER IS
83 CURSOR c_pos_flex_num(c_bus_org_id hr_all_organization_units.business_group_id%type) IS
84 select org_information8
85 from hr_organization_information oi
86 where org_information_context = 'Business Group Information'
87 and organization_id = c_bus_org_id;
88
89 l_flex_num fnd_id_flex_structures_tl.id_flex_num%type;
90
91 BEGIN
92 -- Get Flex ID Number
93 FOR l_cur_flex_num IN c_pos_flex_num(p_bus_org_id) LOOP
94 l_flex_num := l_cur_flex_num.org_information8;
95 END LOOP;
96 RETURN l_flex_num;
97 END get_pos_flex_num;
98
99 --############### Function to get Flex field segment values #############################################
100 FUNCTION get_segments(p_flex_num fnd_id_flex_structures_tl.id_flex_num%type,
101 p_flex_code fnd_id_flex_segments_vl.id_flex_code%type)
102 RETURN t_flex_recs IS
103
104 CURSOR c_get_segment_rec(c_flex_num fnd_id_flex_structures_tl.id_flex_num%type,
105 c_flex_code fnd_id_flex_segments_vl.id_flex_code%type) IS
106 SELECT
107 SEGMENT_NAME,
108 DESCRIPTION,
109 ENABLED_FLAG,
110 APPLICATION_COLUMN_NAME,
111 SEGMENT_NUM,
112 DISPLAY_FLAG,
113 APPLICATION_COLUMN_INDEX_FLAG,
114 DEFAULT_VALUE,
115 RUNTIME_PROPERTY_FUNCTION,
116 ADDITIONAL_WHERE_CLAUSE,
117 REQUIRED_FLAG,
118 SECURITY_ENABLED_FLAG,
119 DISPLAY_SIZE,
120 MAXIMUM_DESCRIPTION_LEN,
121 CONCATENATION_DESCRIPTION_LEN,
122 FORM_ABOVE_PROMPT,
123 FORM_LEFT_PROMPT,
124 RANGE_CODE,
125 FLEX_VALUE_SET_ID,
126 DEFAULT_TYPE,
127 ID_FLEX_NUM,
128 ID_FLEX_CODE,
129 APPLICATION_ID,
130 ROW_ID
131 FROM
132 FND_ID_FLEX_SEGMENTS_VL
133 WHERE
134 (ID_FLEX_NUM= c_flex_num) and -- 50520
135 (ID_FLEX_CODE= c_flex_code) and -- 'POS'
136 (APPLICATION_ID= 800)
137 order by segment_num;
138 l_index NUMBER;
139 BEGIN
140 l_index := 1;
141 FOR l_cur_get_segs IN c_get_segment_rec(p_flex_num,p_flex_code) LOOP
142 l_flex_recs(l_index).SEGMENT_NAME := l_cur_get_segs.SEGMENT_NAME;
143 l_flex_recs(l_index).SEGMENT_NUM := l_cur_get_segs.SEGMENT_NUM;
144 l_index := l_index + 1;
145 END LOOP;
146 RETURN l_flex_recs;
147 END get_segments;
148
149 --############### Function to get Flex field delimiter #############################################
150 FUNCTION get_flex_delimiter(p_flex_code fnd_id_flex_segments_vl.id_flex_code%type,
151 p_flex_num fnd_id_flex_structures_vl.id_flex_num%type) RETURN VARCHAR2 IS
152 CURSOR c_get_delimiter(c_flex_code fnd_id_flex_segments_vl.id_flex_code%type,
153 c_flex_num fnd_id_flex_structures_vl.id_flex_num%type) IS
154
155 SELECT concatenated_segment_delimiter delimiter
156 FROM fnd_id_flex_structures_vl
157 WHERE (APPLICATION_ID=800) AND
158 (ID_FLEX_CODE= c_flex_code) AND
159 id_flex_num = c_flex_num; --
160 l_flex_delimiter fnd_id_flex_structures_vl.concatenated_segment_delimiter%type;
161 BEGIN
162 FOR l_get_delimiter IN c_get_delimiter(p_flex_code,p_flex_num) LOOP
163 l_flex_delimiter := l_get_delimiter.delimiter;
164 END LOOP;
165 RETURN l_flex_delimiter;
166
167 END get_flex_delimiter;
168
169
170 --############### Function to get concatenated Position name #############################################
171 FUNCTION return_pos_name(l_pos_title per_position_definitions.segment1%type,
172 l_pos_desc per_position_definitions.segment1%type,
173 l_seq_no per_position_definitions.segment1%type,
174 l_agency_code per_position_definitions.segment1%type,
175 l_po_id per_position_definitions.segment1%type,
176 l_grade per_position_definitions.segment1%type,
177 l_nfc_agency_code per_position_definitions.segment1%type,
178 l_full_title hr_positions_f.name%type)
179 RETURN VARCHAR2 IS
180 l_flex_num NUMBER;
181 l_flex_recs t_flex_recs;
182 TYPE t_pos_rec IS RECORD
183 (seq_no NUMBER,
184 segment_name VARCHAR2(200));
185 TYPE lt_pos_rec IS TABLE OF t_pos_rec INDEX BY BINARY_INTEGER;
186 l_pos_rec lt_pos_rec;
187 l_pos_indiv_rec t_pos_rec;
188 l_index NUMBER;
189 l_final VARCHAR2(2000);
190 l_seg_value VARCHAR2(2000);
191 l_pos_title_index NUMBER;
192 l_pos_desc_index NUMBER;
193 l_delimiter VARCHAR2(10);
194 l_temp varchar2(2000);
195 l_rem varchar2(2000);
196 TYPE t_pos_name_arr IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
197 l_pos_name_arr t_pos_name_arr;
198 l_delimiter_index NUMBER;
199 l_ctr NUMBER;
200 l_bus_group_id per_business_groups.business_group_id%TYPE;
201
202 BEGIN
203 l_index := 1;
204 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bus_group_id);
205
206 l_flex_num := get_pos_flex_num(p_bus_org_id=>l_bus_group_id) ;
207
208 l_flex_recs := get_segments(p_flex_num => l_flex_num,
209 p_flex_code => 'POS');
210 l_delimiter := ghr_utility.get_flex_delimiter(p_flex_code => 'POS',
211 p_flex_num => l_flex_num);
212
213 ------------------------------------------------
214 -- Storing Position segment details in a rec.
215 ------------------------------------------------
216
217 FOR lt_flex_recs IN l_flex_recs.FIRST .. l_flex_recs.LAST LOOP
218 l_pos_rec(l_index).seq_no := l_index;
219 l_pos_rec(l_index).segment_name := l_flex_recs(lt_flex_recs).SEGMENT_NAME;
220 l_index := l_index + 1;
221 END LOOP;
222
223 ------------------------------------------------
224 -- Extracting Segment values from Position Name
225 ------------------------------------------------
226 l_temp := l_full_title;
227 l_rem := l_full_title;
228 l_ctr := 1;
229
230 IF l_rem IS NOT NULL THEN
231 WHILE INSTR(l_rem,l_delimiter) > 0 LOOP
232 l_delimiter_index := INSTR(l_rem,l_delimiter);
233 l_temp := SUBSTR(l_rem,1,l_delimiter_index-1);
234 l_rem := SUBSTR(l_rem,l_delimiter_index+1);
235 l_pos_name_arr(l_ctr) := l_temp;
236 l_ctr := l_ctr + 1;
237 END LOOP;
238 l_pos_name_arr(l_ctr) := l_rem;
239 END IF;
240 ------------------------------------------------
241 -- Concatenate Position segment values
242 ------------------------------------------------
243
244 -- To concatenate flex field string values
245 FOR l_rec IN l_pos_rec.FIRST .. l_pos_rec.LAST LOOP
246 IF l_pos_rec(l_rec).segment_name = 'Position Title' THEN
247 IF ltrim(l_full_title) IS NOT NULL THEN
248 l_seg_value := l_pos_name_arr(l_pos_rec(l_rec).seq_no);
249 ELSE
250 l_seg_value := l_pos_title;
251 END IF;
252 END IF;
253
254 IF l_pos_rec(l_rec).segment_name = 'Position Description' THEN
255 IF ltrim(l_full_title) IS NOT NULL THEN
256 l_seg_value := l_pos_name_arr(l_pos_rec(l_rec).seq_no);
257 ELSE
258 l_seg_value := l_pos_desc;
259 END IF;
260 END IF;
261 IF l_pos_rec(l_rec).segment_name = 'Sequence Number' THEN
262 l_seg_value := l_seq_no;
263 END IF;
264 IF l_pos_rec(l_rec).segment_name = 'Agency/Subelement Code' THEN
265 l_seg_value := l_agency_code;
266 END IF;
267 IF l_pos_rec(l_rec).segment_name = 'Personnel Office Identifier' THEN
268 l_seg_value := l_po_id;
269 END IF;
270 IF l_pos_rec(l_rec).segment_name = 'Grade' THEN
271 l_seg_value := l_grade;
272 END IF;
273 IF l_pos_rec(l_rec).segment_name = 'NFC Agency Code' THEN
274 l_seg_value := l_nfc_agency_code;
275 END IF;
276
277 IF l_rec = l_pos_rec.LAST THEN
278 l_final := l_final || l_seg_value;
279 ELSE
280 l_final := l_final || l_seg_value || l_delimiter;
281 END IF;
282 END LOOP;
283
284 RETURN l_final;
285
286 END return_pos_name;
287
288 --############### Function to get concatenated Position name #############################################
289 FUNCTION return_nfc_pos_name(l_pos_title per_position_definitions.segment1%type,
290 l_pos_desc per_position_definitions.segment1%type,
291 l_seq_no per_position_definitions.segment1%type,
292 l_agency_code per_position_definitions.segment1%type,
293 l_po_id per_position_definitions.segment1%type,
294 l_grade per_position_definitions.segment1%type,
295 l_nfc_agency_code per_position_definitions.segment1%type,
296 l_full_title hr_positions_f.name%type)
297 RETURN VARCHAR2 IS
298 l_final VARCHAR2(2000);
299 l_delimiter VARCHAR2(10);
300 l_temp varchar2(2000);
301 l_rem varchar2(2000);
302 TYPE t_pos_name_arr IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
303 l_pos_name_arr t_pos_name_arr;
304 l_delimiter_index NUMBER;
305 l_pos_new_title per_position_definitions.segment1%type;
306 l_pos_new_desc per_position_definitions.segment1%type;
307 l_ctr NUMBER;
308 l_flex_num NUMBER;
309 l_bus_group_id per_business_groups.business_group_id%TYPE;
310
311 BEGIN
312 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bus_group_id);
313
314 l_flex_num := get_pos_flex_num(p_bus_org_id=>l_bus_group_id) ;
315
316 l_delimiter := ghr_utility.get_flex_delimiter(p_flex_code => 'POS',
317 p_flex_num => l_flex_num);
318 ------------------------------------------------
319 -- Extracting Segment values from Position Name
320 ------------------------------------------------
321 l_temp := l_full_title;
322 l_rem := l_full_title;
323 l_ctr := 1;
324
325 IF l_rem IS NOT NULL THEN
326 WHILE INSTR(l_rem,l_delimiter) > 0 LOOP
327 l_delimiter_index := INSTR(l_rem,l_delimiter);
328 l_temp := SUBSTR(l_rem,1,l_delimiter_index-1);
329 l_rem := SUBSTR(l_rem,l_delimiter_index+1);
330 l_pos_name_arr(l_ctr) := l_temp;
331 l_ctr := l_ctr + 1;
332 END LOOP;
333 l_pos_name_arr(l_ctr) := l_rem;
334 END IF;
335
336 IF ltrim(l_full_title) IS NOT NULL THEN
337 l_pos_new_title := l_pos_name_arr(1);
338 l_pos_new_desc := l_pos_name_arr(5);
339 ELSE
340 l_pos_new_title := l_pos_title;
341 l_pos_new_desc := l_pos_desc;
342 END IF;
343
344
345 ------------------------------------------------
346 -- Concatenate Position segment values
347 ------------------------------------------------
348 -- Hard coded for GPO
349 l_final := l_pos_new_title || l_delimiter || l_agency_code || l_delimiter || l_nfc_agency_code || l_delimiter ||
350 l_po_id || l_delimiter || l_pos_new_desc || l_delimiter || l_grade;
351
352 -- || l_delimiter || l_grade;
353
354 RETURN l_final;
355
356 END return_nfc_pos_name;
357
358
359 PROCEDURE validate_nfc(
360 P_POSITION_ID in NUMBER
361 ,P_SEGMENT1 in VARCHAR2
362 ,P_SEGMENT2 in VARCHAR2
363 ,P_SEGMENT3 in VARCHAR2
364 ,P_SEGMENT4 in VARCHAR2
365 ,P_SEGMENT5 in VARCHAR2
366 ,P_SEGMENT6 in VARCHAR2
367 ,P_SEGMENT7 in VARCHAR2
368 ,P_SEGMENT8 in VARCHAR2
369 ,P_SEGMENT9 in VARCHAR2
370 ,P_SEGMENT10 in VARCHAR2
371 ,P_SEGMENT11 in VARCHAR2
372 ,P_SEGMENT12 in VARCHAR2
373 ,P_SEGMENT13 in VARCHAR2
374 ,P_SEGMENT14 in VARCHAR2
375 ,P_SEGMENT15 in VARCHAR2
376 ,P_SEGMENT16 in VARCHAR2
377 ,P_SEGMENT17 in VARCHAR2
378 ,P_SEGMENT18 in VARCHAR2
379 ,P_SEGMENT19 in VARCHAR2
380 ,P_SEGMENT20 in VARCHAR2
381 ,P_SEGMENT21 in VARCHAR2
382 ,P_SEGMENT22 in VARCHAR2
383 ,P_SEGMENT23 in VARCHAR2
384 ,P_SEGMENT24 in VARCHAR2
385 ,P_SEGMENT25 in VARCHAR2
386 ,P_SEGMENT26 in VARCHAR2
387 ,P_SEGMENT27 in VARCHAR2
388 ,P_SEGMENT28 in VARCHAR2
389 ,P_SEGMENT29 in VARCHAR2
390 ,P_SEGMENT30 in VARCHAR2
391 ,P_INFORMATION6 in VARCHAR2
392 ,P_EFFECTIVE_DATE in DATE
393 ,P_LANGUAGE_CODE in VARCHAR2 ) IS
394 cursor c_old_pos_segments(c_position_id hr_all_positions_f.position_id%type,
395 c_effective_date date) is
396 select information6,segment1,segment2,segment3,segment4,
397 segment5,segment6,segment7
398 from per_position_definitions pdf, hr_all_positions_f pos
399 where pos.position_definition_id = pdf.position_definition_id
400 and pos.position_id = c_position_id
401 and c_effective_date between pos.effective_start_date and
402 pos.effective_end_date;
403
404 CURSOR c_check_child(c_position_id hr_positions_f.position_id%type) IS
405 SELECT 1 FROM hr_positions_f
406 WHERE information6 = to_char(c_position_id); -- Bug 4576746
407
408 l_child_exists BOOLEAN;
409 l_pos_cre_extra_info_id per_position_extra_info.position_extra_info_id%type;
410 l_extra_info_id per_position_extra_info.position_extra_info_id%type;
411 l_pos_cre_ovn per_position_extra_info.object_version_number%type;
412 l_ovn per_position_extra_info.object_version_number%type;
413 l_information_type per_position_extra_info.information_type%type;
414 l_session_date fnd_sessions.effective_date%type;
415 cursor c_get_session_date is
416 select trunc(effective_date) session_date
417 from fnd_sessions
418 where session_id = (select userenv('sessionid') from dual);
419
420 BEGIN
421 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
422 l_child_exists := FALSE;
423 hr_utility.set_location('Entering: Validate_NFC ',10);
424 -- Get Session Date
425 l_session_date := trunc(sysdate);
426 for ses_rec in c_get_session_date loop
427 l_session_date := ses_rec.session_date;
428 end loop;
429 hr_utility.set_location(' Validate_NFC ',20);
430 -- Check for the change in the segments
431 -- for c_old_rec in c_old_pos_segments(p_position_id) loop
432 IF p_information6 IS NOT NULL and p_information6 <> hr_api.g_varchar2
433 THEN
434 hr_utility.set_location(' Validate_NFC '||p_information6,30);
435 FOR c_old_rec in c_old_pos_segments(p_information6,l_session_date) LOOP
436 hr_utility.set_location(' Validate_NFC '||p_information6,31);
437 IF NVL(c_old_rec.segment2,hr_api.g_varchar2) <> NVL(p_segment2,hr_api.g_varchar2) OR
438 NVL(c_old_rec.segment3,hr_api.g_varchar2) <> NVL(p_segment3,hr_api.g_varchar2) OR
439 NVL(c_old_rec.segment4,hr_api.g_varchar2) <> NVL(p_segment4,hr_api.g_varchar2) OR
440 -- NVL(c_old_rec.segment6,hr_api.g_varchar2) <> NVL(p_segment6,hr_api.g_varchar2) OR
441 NVL(c_old_rec.segment7,hr_api.g_varchar2) <> NVL(p_segment7,hr_api.g_varchar2) THEN
442 hr_utility.set_message(8301,'GHR_38948_NFC_ERROR4');
443 hr_utility.raise_error;
444 END IF;
445 END LOOP;
446 END IF;
447
448 -- Raise error when master position segments are changed
449 -- when they're having child individual positions attached to it
450 IF p_information6 IS NULL or p_information6 = hr_api.g_varchar2 THEN
451 hr_utility.set_location(' Validate_NFC ',40);
452 -- Check if the segment values have changed.
453 FOR c_old_rec in c_old_pos_segments(P_POSITION_ID,l_session_date) LOOP
454 IF NVL(c_old_rec.segment2,hr_api.g_varchar2) <> NVL(p_segment2,hr_api.g_varchar2) OR
455 NVL(c_old_rec.segment3,hr_api.g_varchar2) <> NVL(p_segment3,hr_api.g_varchar2) OR
456 NVL(c_old_rec.segment4,hr_api.g_varchar2) <> NVL(p_segment4,hr_api.g_varchar2) OR
457 -- NVL(c_old_rec.segment6,hr_api.g_varchar2) <> NVL(p_segment6,hr_api.g_varchar2) OR
458 NVL(c_old_rec.segment7,hr_api.g_varchar2) <> NVL(p_segment7,hr_api.g_varchar2) THEN
459 hr_utility.set_location(' Validate_NFC ',50);
460 -- Raise error if child exists
461 FOR l_check_child IN c_check_child(P_POSITION_ID) LOOP
462 l_child_exists := TRUE;
463 EXIT;
464 END LOOP;
465 hr_utility.set_location(' Validate_NFC ',60);
466 IF l_child_exists = TRUE THEN
467 hr_utility.set_message(8301,'GHR_38949_NFC_ERROR5');
468 hr_utility.raise_error;
469 END IF;
470 END IF;
471 END LOOP;
472 END IF;
473 --
474 END IF; -- is_ghr_nfc check
475
476 END validate_nfc;
477
478
479 PROCEDURE validate_delete_nfc(
480 P_POSITION_ID in NUMBER
481 ,P_EFFECTIVE_DATE in DATE
482 ) IS
483
484 CURSOR c_check_child(c_position_id hr_positions_f.position_id%type) IS
485 SELECT 1 FROM hr_positions_f
486 WHERE information6 = to_char(c_position_id); -- Bug 4576746
487
488 l_child_exists BOOLEAN;
489
490 BEGIN
491 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
492 l_child_exists := FALSE;
493 hr_utility.set_location('Entering: Validate_Delete_NFC ',10);
494 -- Raise error if child exists
495 FOR l_check_child IN c_check_child(P_POSITION_ID) LOOP
496 l_child_exists := TRUE;
497 EXIT;
498 END LOOP;
499
500 hr_utility.set_location(' Validate_Delete_NFC ',60);
501 IF l_child_exists = TRUE THEN
502 hr_utility.set_message(8301,'GHR_38949_NFC_ERROR5');
503 hr_utility.raise_error;
504 END IF;
505 --
506 END IF; -- is_ghr_nfc check
507
508 END validate_delete_nfc;
509
510 PROCEDURE validate_create_nfc(
511 P_SEGMENT1 in VARCHAR2
512 ,P_SEGMENT2 in VARCHAR2
513 ,P_SEGMENT3 in VARCHAR2
514 ,P_SEGMENT4 in VARCHAR2
515 ,P_SEGMENT5 in VARCHAR2
516 ,P_SEGMENT6 in VARCHAR2
517 ,P_SEGMENT7 in VARCHAR2
518 ,P_SEGMENT8 in VARCHAR2
519 ,P_SEGMENT9 in VARCHAR2
520 ,P_SEGMENT10 in VARCHAR2
521 ,P_SEGMENT11 in VARCHAR2
522 ,P_SEGMENT12 in VARCHAR2
523 ,P_SEGMENT13 in VARCHAR2
524 ,P_SEGMENT14 in VARCHAR2
525 ,P_SEGMENT15 in VARCHAR2
526 ,P_SEGMENT16 in VARCHAR2
527 ,P_SEGMENT17 in VARCHAR2
528 ,P_SEGMENT18 in VARCHAR2
529 ,P_SEGMENT19 in VARCHAR2
530 ,P_SEGMENT20 in VARCHAR2
531 ,P_SEGMENT21 in VARCHAR2
532 ,P_SEGMENT22 in VARCHAR2
533 ,P_SEGMENT23 in VARCHAR2
534 ,P_SEGMENT24 in VARCHAR2
535 ,P_SEGMENT25 in VARCHAR2
536 ,P_SEGMENT26 in VARCHAR2
537 ,P_SEGMENT27 in VARCHAR2
538 ,P_SEGMENT28 in VARCHAR2
539 ,P_SEGMENT29 in VARCHAR2
540 ,P_SEGMENT30 in VARCHAR2
541 ,P_INFORMATION6 in VARCHAR2
542 ,P_EFFECTIVE_DATE in DATE
543 ,P_LANGUAGE_CODE in VARCHAR2 ) IS
544 cursor c_old_pos_segments(c_position_id hr_all_positions_f.position_id%type,
545 c_effective_date date) is
546 select information6,segment1,segment2,segment3,segment4,
547 segment5,segment6,segment7
548 from per_position_definitions pdf, hr_all_positions_f pos
549 where pos.position_definition_id = pdf.position_definition_id
550 and pos.position_id = c_position_id
551 and c_effective_date between pos.effective_start_date and
552 pos.effective_end_date;
553
554 l_pos_cre_extra_info_id per_position_extra_info.position_extra_info_id%type;
555 l_extra_info_id per_position_extra_info.position_extra_info_id%type;
556 l_pos_cre_ovn per_position_extra_info.object_version_number%type;
557 l_ovn per_position_extra_info.object_version_number%type;
558 l_information_type per_position_extra_info.information_type%type;
559 l_session_date fnd_sessions.effective_date%type;
560 cursor c_get_session_date is
561 select trunc(effective_date) session_date
562 from fnd_sessions
563 where session_id = (select userenv('sessionid') from dual);
564
565 BEGIN
566 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
567 hr_utility.set_location('Entering: HR_POSITION_BK1.CREATE_POSITION_B', 10);
568 -- Check for the change in the segments
569 -- for c_old_rec in c_old_pos_segments(p_position_id) loop
570 -- Get Session Date
571 l_session_date := trunc(sysdate);
572 for ses_rec in c_get_session_date loop
573 l_session_date := ses_rec.session_date;
574 end loop;
575 IF p_information6 IS NOT NULL THEN
576 for c_old_rec in c_old_pos_segments(p_information6,l_session_date) loop
577 IF NVL(c_old_rec.segment2,hr_api.g_varchar2) <> NVL(p_segment2,hr_api.g_varchar2) OR
578 NVL(c_old_rec.segment3,hr_api.g_varchar2) <> NVL(p_segment3,hr_api.g_varchar2) OR
579 NVL(c_old_rec.segment4,hr_api.g_varchar2) <> NVL(p_segment4,hr_api.g_varchar2) or
580 -- NVL(c_old_rec.segment6,hr_api.g_varchar2) <> NVL(p_segment6,hr_api.g_varchar2) or
581 NVL(c_old_rec.segment7,hr_api.g_varchar2) <> NVL(p_segment7,hr_api.g_varchar2) then
582 hr_utility.set_message(8301,'GHR_38948_NFC_ERROR4');
583 hr_utility.raise_error;
584 END IF;
585 end loop;
586 END IF;
587 --
588 END IF; -- is_ghr_nfc check
589
590 END validate_create_nfc;
591
592 PROCEDURE update_nfc_eit(
593 P_POSITION_ID in NUMBER
594 ,P_SEGMENT1 in VARCHAR2
595 ,P_SEGMENT2 in VARCHAR2
596 ,P_SEGMENT3 in VARCHAR2
597 ,P_SEGMENT4 in VARCHAR2
598 ,P_SEGMENT5 in VARCHAR2
599 ,P_SEGMENT6 in VARCHAR2
600 ,P_SEGMENT7 in VARCHAR2
601 ,P_SEGMENT8 in VARCHAR2
602 ,P_SEGMENT9 in VARCHAR2
603 ,P_SEGMENT10 in VARCHAR2
604 ,P_SEGMENT11 in VARCHAR2
605 ,P_SEGMENT12 in VARCHAR2
606 ,P_SEGMENT13 in VARCHAR2
607 ,P_SEGMENT14 in VARCHAR2
608 ,P_SEGMENT15 in VARCHAR2
609 ,P_SEGMENT16 in VARCHAR2
610 ,P_SEGMENT17 in VARCHAR2
611 ,P_SEGMENT18 in VARCHAR2
612 ,P_SEGMENT19 in VARCHAR2
613 ,P_SEGMENT20 in VARCHAR2
614 ,P_SEGMENT21 in VARCHAR2
615 ,P_SEGMENT22 in VARCHAR2
616 ,P_SEGMENT23 in VARCHAR2
617 ,P_SEGMENT24 in VARCHAR2
618 ,P_SEGMENT25 in VARCHAR2
619 ,P_SEGMENT26 in VARCHAR2
620 ,P_SEGMENT27 in VARCHAR2
621 ,P_SEGMENT28 in VARCHAR2
622 ,P_SEGMENT29 in VARCHAR2
623 ,P_SEGMENT30 in VARCHAR2
624 ,P_INFORMATION6 in VARCHAR2
625 ,P_EFFECTIVE_DATE in DATE
626 ,P_LANGUAGE_CODE in VARCHAR2 ) IS
627 Cursor c_pos_ei(p_position_id in NUMBER,
628 p_information_type in VARCHAR2) is
629 select position_extra_info_id,
630 object_version_number
631 from per_position_extra_info
632 where position_id = p_position_id
633 and information_type = p_information_type;
634 l_pos_cre_extra_info_id per_position_extra_info.position_extra_info_id%type;
635 l_extra_info_id per_position_extra_info.position_extra_info_id%type;
636 l_pos_cre_ovn per_position_extra_info.object_version_number%type;
637 l_ovn per_position_extra_info.object_version_number%type;
638 l_information_type per_position_extra_info.information_type%type;
639 BEGIN
640 -- Update/Create Position group1 Extra Info
641 --
642 -- EIT GHR_US_POS_GRP1
643 IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
644 l_information_type := 'GHR_US_POS_GRP1';
645 for pos_ei in c_pos_ei(p_position_id,l_information_type)
646 LOOP
647 l_extra_info_id := pos_ei.position_extra_info_id;
648 l_ovn := pos_ei.object_version_number;
649 IF l_extra_info_id is NOT NULL THEN
650 hr_position_extra_info_api.update_position_extra_info
651 ( p_position_extra_info_id => l_Extra_Info_Id
652 , p_object_version_number => l_ovn
653 , p_poei_information3 => p_segment4);
654 END IF;
655 END LOOP;
656
657 --
658 -- EIT GHR_US_POS_VALID_GRADE
659 l_information_type := 'GHR_US_POS_VALID_GRADE';
660 for pos_ei in c_pos_ei(p_position_id,l_information_type)
661 LOOP
662 l_extra_info_id := pos_ei.position_extra_info_id;
663 l_ovn := pos_ei.object_version_number;
664 IF l_extra_info_id is NOT NULL THEN
665 hr_position_extra_info_api.update_position_extra_info
666 ( p_position_extra_info_id => l_Extra_Info_Id
667 , p_object_version_number => l_ovn
668 , p_poei_information3 => p_segment7);
669 END IF;
670 END LOOP;
671
672 --
673 -- EIT GHR_US_POS_GRP3;
674 l_information_type := 'GHR_US_POS_GRP3';
675 for pos_ei in c_pos_ei(p_position_id,l_information_type)
676 LOOP
677 l_extra_info_id := pos_ei.position_extra_info_id;
678 l_ovn := pos_ei.object_version_number;
679 IF l_extra_info_id is NOT NULL THEN
680 hr_position_extra_info_api.update_position_extra_info
681 ( p_position_extra_info_id => l_Extra_Info_Id
682 , p_object_version_number => l_ovn
683 , p_poei_information21 => p_segment3);
684 END IF;
685 END LOOP;
686
687
688 END IF; -- IF ghr_utility.is_ghr_nfc = 'TRUE' THEN
689
690 END update_nfc_eit;
691
692
693
694 FUNCTION is_ghr RETURN VARCHAR2
695 IS
696 BEGIN
697 -- DK 2002-11-08 PLSQLSTD
698 -- hr_utility.set_location('Inside is_ghr' ,1);
699 RETURN v_is_ghr;
700 END;
701
702
703 FUNCTION is_ghr_ben RETURN VARCHAR2
704 IS
705 BEGIN
706 -- hr_utility.set_location('Inside is_ghr_ben' ,1);
707 RETURN v_is_ghr_ben;
708 END;
709
710
711 FUNCTION is_ghr_ben_fehb RETURN VARCHAR2
712 IS
713 BEGIN
714 -- hr_utility.set_location('Inside is_ghr_ben_fehb' ,1);
715 RETURN v_is_ghr_ben_fehb;
716 END;
717
718 FUNCTION is_ghr_ben_tsp RETURN VARCHAR2
719 IS
720 BEGIN
721 -- hr_utility.set_location('Inside is_ghr_ben_tsp' ,1);
722 RETURN v_is_ghr_ben_tsp;
723 END;
724
725 FUNCTION is_ghr_nfc RETURN VARCHAR2
726 IS
727 BEGIN
728 -- hr_utility.set_location('Inside is_ghr_ben' ,1);
729 RETURN v_is_ghr_nfc;
730 END;
731
732 PROCEDURE set_client_info
733 ( p_person_id in per_all_people_f.person_id%type default null,
734 p_position_id in hr_all_positions_f.position_id%type default null,
735 p_assignment_id in per_all_assignments_f.assignment_id%type default null)
736 is
737 cursor c_per_bus_group_id(p_person_id in per_all_people_f.person_id%TYPE) is
738 select ppf.business_group_id
739 from per_all_people_f ppf
740 where ppf.person_id = p_person_id
741 and trunc(sysdate) between ppf.effective_start_date
742 and ppf.effective_end_date;
743 cursor c_pos_bus_group_id(p_position_id in hr_all_positions_f.position_id%TYPE ) is
744 select pos.business_group_id
745 from hr_all_positions_f pos -- Venkat -- Position DT
746 where pos.position_id = p_position_id
747 and trunc(sysdate) between pos.effective_start_date
748 and pos.effective_end_date;
749 cursor c_asg_bus_group_id(
750 p_assignment_id in per_all_assignments_f.assignment_id%TYPE) is
751 select asg.business_group_id
752 from per_all_assignments_f asg
753 where asg.assignment_id = p_assignment_id
754 and trunc(sysdate) between asg.effective_start_date
755 and asg.effective_end_date;
756 cursor cur_sec_grp(p_business_group_id in
757 per_business_groups.business_group_id%TYPE) is
758 select pbg.security_group_id
759 from per_business_groups pbg
760 where pbg.business_group_id = p_business_group_id;
761 v_current_sg NUMBER;
762 l_bus_group_id per_business_groups.business_group_id%TYPE;
763 l_security_group_id per_business_groups.security_group_id%TYPE;
764 begin
765 v_current_sg := fnd_profile.value('PER_SECURITY_PROFILE_ID');
766 IF v_current_sg is not null then
767 -- We can assume that either:
768 -- a) the API is being called from a Form
769 -- or b) the call has come from a SQLPlus session where
770 -- fnd_global.apps_initialize has been called.
771 --
772 -- In either of these two cases client_info will have
773 -- already been set so nothing extra needs to be done here.
774 -- So can use HR_LOOKUPS for validation.
775 null;
776 ELSE
777 IF (p_person_id IS NOT NULL) OR
778 (p_assignment_id IS NOT NULL) OR
779 (p_position_id IS NOT NULL) THEN
780 -- Derive the business_group_id from the known ID.
781 -- Therefore can derive the security_group_id and
782 -- set CLIENT_INFO by calling hr_api.set_security_group_id
783 -- So can use HR_LOOKUPS for validation.
784 -- Getting Business Group Id
785 IF p_person_id is not null then
786 FOR c_per_bus_rec IN c_per_bus_group_id(p_person_id)
787 LOOP
788 l_bus_group_id := c_per_bus_rec.business_group_id;
789 exit;
790 END LOOP;
791 ELSIF p_position_id is not null then
792 FOR c_pos_bus_rec in c_pos_bus_group_id(p_position_id)
793 LOOP
794 l_bus_group_id := c_pos_bus_rec.business_group_id;
795 exit;
796 END LOOP;
797 ELSIF p_assignment_id is not null then
798 FOR c_asg_bus_rec in c_asg_bus_group_id(p_assignment_id)
799 LOOP
800 l_bus_group_id := c_asg_bus_rec.business_group_id;
801 exit;
802 END LOOP;
803 END IF;
804 -- Getting Security_Group_Id
805 FOR cur_sec_grp_rec in cur_sec_grp(l_bus_group_id)
806 LOOP
807 l_security_group_id := cur_sec_grp_rec.security_group_id;
808 exit;
809 END LOOP;
810 -- Set the Security Group Id in CLIENT_INFO
811 hr_api.set_security_group_id(
812 p_security_group_id => l_security_group_id
813 );
814 ELSE
815 --Cannot derive a business group, so data must be held
816 -- outside of the context of a business group
817 -- Set CLIENT_INFO to zero by calling
818 -- hr_api.set_security_group_id.
819 -- So can use HR_LOOKUPS for validation.
820
821 -- Note1: CLIENT_INFO needs to be explicitly set to
822 -- zero because the same API may have previously been
823 -- called for a row where a business_group_id was derived.
824 -- As that business_group_id does not apply for the current
825 -- row CLIENT_INFO must be set to zero. This will cause
826 -- the HR_LOOKUPS view to act in the same way as
827 -- HR_STANDARD_LOOKUPS.
828 -- Note2: This should not interfere with any Forms processing
829 -- because this will only be done when the API call has NOT
830 -- come from a Form.
831 --
832 -- Set the Security Group Id in CLIENT_INFO to 0
833 hr_api.set_security_group_id(
834 p_security_group_id => 0
835 );
836 END IF;
837 END IF;
838 -- v_current_sg := fnd_profile.value('SECURITY_GROUP_ID');
839 end set_client_info;
840
841 FUNCTION get_noa_code (p_nature_of_action_id IN NUMBER)
842 RETURN VARCHAR2 AS
843 l_noa_code ghr_nature_of_actions.code%type;
844 cursor c_noa_code is select code from ghr_nature_of_actions
845 where nature_of_action_id= p_nature_of_action_id;
846 BEGIN
847 open c_noa_code ;
848 fetch c_noa_code into l_noa_code;
849 close c_noa_code;
850 RETURN(l_noa_code);
851 END;
852
853 procedure process_nfc_auth_date(
854 --p_person_id in per_people_f.person_id%type,
855 p_effective_date in ghr_pa_requests.effective_date%type,
856 p_pa_request_id in ghr_pa_requests.pa_request_id%type)
857 is
858 cursor get_next_auth_date is
859 select nvl(max(fnd_date.canonical_to_date(rei_information3))+1,p_effective_date)
860 authentication_date
861 from ghr_pa_requests par, ghr_pa_request_extra_info rei
862 where par.person_id in ( select person_id from ghr_pa_requests where
863 pa_request_id = p_pa_request_id )
864 and par.effective_date = p_effective_date
865 and par.pa_notification_id is not null
866 and par.pa_request_id = rei.pa_request_id
867 and rei.information_type = 'GHR_US_PAR_NFC_INFO';
868 l_rei_rec ghr_pa_request_extra_info%rowtype ;
869 l_org_rec ghr_pa_request_ei_shadow%rowtype;
870 begin
871 for c_ad_rec in get_next_auth_date loop
872 l_rei_rec.information_type := 'GHR_US_PAR_NFC_INFO';
873 l_rei_rec.pa_request_id := p_pa_request_id;
874 l_rei_rec.rei_information3 := fnd_date.date_to_canonical(c_ad_rec.authentication_date);
875 GHR_NON_SF52_EXTRA_INFO.generic_populate_extra_info
876 (p_rei_rec => l_rei_rec,
877 p_org_rec => l_org_rec,
878 p_flag => 'C'
879 );
880 end loop;
881 end;
882
883 function get_nfc_prev_noa(
884 p_person_id in per_people_f.person_id%type,
885 p_pa_notification_id in ghr_pa_requests.pa_notification_id%type,
886 p_effective_date in ghr_pa_requests.effective_date%type)
887 RETURN VARCHAR2
888 IS
889 CURSOR c_get_prev_details
890 is
891 select effective_date,first_noa_code,
892 second_noa_code,pa_notification_id,pa_request_id
893 from ghr_pa_requests
894 where pa_notification_id is not null
895 and person_id = p_person_id
896 and pa_notification_id < p_pa_notification_id
897 and effective_date <= p_effective_date
898 and first_noa_code not in ('001') -- Exclude all cancellations
899 and pa_request_id not in ( select altered_pa_request_id
900 from ghr_pa_requests where
901 pa_notification_id = p_pa_notification_id) -- Excludes original action
902 order by pa_notification_id desc;
903 BEGIN
904 FOR c_prev_rec in c_get_prev_details LOOP
905 IF c_prev_rec.first_noa_code = '002' THEN
906 RETURN c_prev_rec.second_noa_code;
907 ELSE
908 RETURN c_prev_rec.first_noa_code;
909 END IF;
910 END LOOP;
911 RETURN NULL;
912 END;
913
914 procedure get_nfc_auth_codes(
915 p_person_id in per_people_f.person_id%type,
916 p_pa_notification_id in ghr_pa_requests.pa_notification_id%type,
917 p_effective_date in ghr_pa_requests.effective_date%type,
918 p_first_auth_code out nocopy ghr_pa_requests.FIRST_ACTION_LA_CODE1%type,
919 p_second_auth_code out nocopy ghr_pa_requests.FIRST_ACTION_LA_CODE1%type)
920 IS
921 cursor c_get_prev_details is
922 select effective_date,first_noa_code,
923 first_action_la_code1,first_action_la_code2,
924 second_action_la_code1,second_action_la_code2
925 from ghr_pa_requests
926 where pa_notification_id is not null
927 and person_id = p_person_id
928 and pa_notification_id < p_pa_notification_id
929 and effective_date <= p_effective_date
930 and first_noa_code not in ('001') -- Exclude all cancellations
931 and pa_request_id not in ( select altered_pa_request_id
932 from ghr_pa_requests where
933 pa_notification_id = p_pa_notification_id) -- Excludes original action
934 order by pa_notification_id desc;
935 BEGIN
936 FOR c_prev_rec in c_get_prev_details LOOP
937 IF nvl(c_prev_rec.first_noa_code,hr_api.g_varchar2) = '002' THEN
938 p_first_auth_code := c_prev_rec.second_action_la_code1;
939 p_second_auth_code := c_prev_rec.second_action_la_code2;
940 ELSE
941 p_first_auth_code := c_prev_rec.first_action_la_code1;
942 p_second_auth_code := c_prev_rec.first_action_la_code2;
943 END IF;
944 EXIT;
945 END LOOP;
946 EXCEPTION
947 when others then
948 -- NOCOPY changes
949 -- Reset IN OUT params and set OUT params
950 p_first_auth_code := null;
951 p_second_auth_code := null;
952 raise;
953 END;
954 function get_nfc_conv_action_code(
955 p_pa_request_id in ghr_pa_requests.pa_request_id%type)
956 RETURN NUMBER
957 IS
958 cursor c_ex_emp is
959 select 'X'
960 from per_people_f per, per_person_types ppt, ghr_pa_requests par
961 where par.pa_request_id = p_pa_request_id
962 and per.person_id = par.person_id
963 and ppt.person_type_id = per.person_type_id
964 and ppt.system_person_type = 'EX_EMP'
965 and (par.effective_date - 1) between per.effective_start_date
966 and per.effective_end_date
967 and par.first_noa_code like '5%';
968
969 BEGIN
970 FOR c_ex_emp_rec in c_ex_emp LOOP
971 RETURN 1;
972 END LOOP;
973 RETURN 2;
974 END;
975
976
977 BEGIN
978 v_is_ghr := 'TRUE';
979 v_current_bg := fnd_profile.value('PER_BUSINESS_GROUP_ID');
980
981 -- DK 2002-11-08 PLSQLSTD
982 --hr_utility.set_location('Inside Main Begin' ,1);
983
984 OPEN c_fed_bg(v_current_bg);
985 FETCH c_fed_bg INTO r_fed_bg;
986 IF c_fed_bg%NOTFOUND THEN
987 v_is_ghr := 'FALSE';
988 END IF;
989 CLOSE c_fed_bg;
990
991 v_is_ghr_ben := 'TRUE';
992
993 OPEN c_ben_pgm(v_current_bg);
994 FETCH c_ben_pgm INTO r_ben_pgm;
995 IF c_ben_pgm%NOTFOUND THEN
996 v_is_ghr_ben := 'FALSE';
997 END IF;
998 CLOSE c_ben_pgm;
999
1000
1001 v_is_ghr_ben_fehb := 'TRUE';
1002 OPEN c_ben_pgm_fehb(v_current_bg);
1003 FETCH c_ben_pgm_fehb INTO r_ben_pgm_fehb;
1004 IF c_ben_pgm_fehb%NOTFOUND THEN
1005 v_is_ghr_ben_fehb := 'FALSE';
1006 END IF;
1007 CLOSE c_ben_pgm_fehb;
1008
1009
1010 v_is_ghr_ben_tsp := 'TRUE';
1011 OPEN c_ben_pgm_tsp(v_current_bg);
1012 FETCH c_ben_pgm_tsp INTO r_ben_pgm_tsp;
1013 IF c_ben_pgm_tsp%NOTFOUND THEN
1014 v_is_ghr_ben_tsp := 'FALSE';
1015 END IF;
1016 CLOSE c_ben_pgm_tsp;
1017
1018 v_is_ghr_nfc := 'TRUE';
1019
1020 OPEN c_fed_nfc(v_current_bg);
1021 FETCH c_fed_nfc INTO r_ben_nfc;
1022 IF c_fed_nfc%NOTFOUND THEN
1023 v_is_ghr_nfc := 'FALSE';
1024 END IF;
1025 CLOSE c_fed_nfc;
1026
1027 -- DK 2002-11-08 PLSQLSTD
1028 --hr_utility.set_location('Leaving Main Begin' ,1);
1029
1030 end ghr_utility;
[Home] [Help]