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