1 PACKAGE BODY pqp_utilities AS
2 /* $Header: pqputifn.pkb 120.19.12000000.2 2007/07/04 13:32:35 apmishra noship $ */
3 --
4 --
5 --
6 -- Debug Variables.
7 --
8 g_package_name VARCHAR2(31) := 'pqp_utilities.';
9
10 -- Hash Function Variable
11 g_hash_key NUMBER(15) := NULL;
12 g_hash_base NUMBER(15) := NULL;
13 g_hash_size NUMBER(15) := NULL;
14 g_conflict_check BOOLEAN ;
15 PROCEDURE debug(p_trace_message IN VARCHAR2, p_trace_location IN NUMBER)
16 IS
17 l_padding VARCHAR2(12);
18 l_max_message_length NUMBER := 72;
19 l_time NUMBER;
20 l_trace_message VARCHAR2(250);
21 BEGIN
22 IF p_trace_location IS NOT NULL
23 THEN
24 IF g_debug_entry_exits_only
25 THEN
26 IF NOT(
27 UPPER(LTRIM(p_trace_message)) LIKE 'ENTERING%'
28 OR UPPER(LTRIM(p_trace_message)) LIKE 'LEAVING%'
29 )
30 THEN
31 RETURN; -- its not an entry exit message, continue
32 END IF;
33 END IF;
34
35 -- if control reaches here either all trace messages are allowed
36 -- or it is an extry and exit message
37
38 -- its important to do the timestamp check AFTER the entry-exit check
39 /*BUG No: 6137713*/
40 IF g_debug_timestamps
41 THEN
42 l_trace_message :=
43 SUBSTR(TO_CHAR(DBMS_UTILITY.get_time) || ':'
44 || p_trace_message,1,250);
45 ELSE
46 l_trace_message := SUBSTR(p_trace_message,1,250);
47 END IF;
48
49 hr_utility.set_location(l_trace_message, p_trace_location);
50 ELSE
51 IF NOT g_debug_entry_exits_only
52 THEN
53 hr_utility.TRACE(SUBSTR(p_trace_message, 1, 250));
54 END IF;
55 END IF;
56 END debug;
57
58
59 -- This procedure is used to get configuration values
60 -- for a configuration type.
61 --
62 -- ----------------------------------------------------------------------------
63 -- |----------------------------< get_config_type_values >--------------------|
64 -- ----------------------------------------------------------------------------
65
66 PROCEDURE get_config_type_values(
67 p_configuration_type IN VARCHAR2
68 ,p_business_group_id IN NUMBER
69 ,p_legislation_code IN VARCHAR2
70 ,p_tab_config_values OUT NOCOPY t_config_values
71 )
72 IS
73 --
74 -- Cursor to fetch configuration values
75 -- for a given configuration type, bg and leg code
76 CURSOR csr_get_config_values(c_legislation_code VARCHAR2)
77 IS
78 SELECT pcv.configuration_value_id, pcv.pcv_information1
79 ,pcv.pcv_information2, pcv.pcv_information3
80 ,pcv.pcv_information4, pcv.pcv_information5
81 ,pcv.pcv_information6, pcv.pcv_information7
82 ,pcv.pcv_information8, pcv.pcv_information9
83 ,pcv.pcv_information10, pcv.pcv_information11
84 ,pcv.pcv_information12, pcv.pcv_information13
85 ,pcv.pcv_information14, pcv.pcv_information15
86 ,pcv.pcv_information16, pcv.pcv_information17
87 ,pcv.pcv_information18, pcv.pcv_information19
88 ,pcv.pcv_information20
89 FROM pqp_configuration_values pcv, pqp_configuration_types pct
90 WHERE pcv.pcv_information_category = pct.configuration_type
91 AND pct.configuration_type = p_configuration_type
92 AND ( ( pcv.business_group_id IS NOT NULL
93 AND pcv.business_group_id = p_business_group_id
94 )
95 OR ( pcv.legislation_code IS NOT NULL
96 AND pcv.legislation_code = c_legislation_code
97 AND NOT EXISTS(
98 SELECT 1
99 FROM pqp_configuration_values pcv2
100 WHERE pcv2.pcv_information_category =
101 pcv.pcv_information_category
102 AND pcv2.configuration_value_id <>
103 pcv.configuration_value_id
104 AND pcv2.business_group_id = p_business_group_id
105 AND ( ( pct.multiple_occurences_flag = 'Y'
106 AND ( ( pct.total_unique_columns = 1
107 AND pcv2.pcv_information1 =
108 pcv.pcv_information1
109 )
110 OR ( pct.total_unique_columns = 2
111 AND pcv2.pcv_information1 =
112 pcv.pcv_information1
113 AND pcv2.pcv_information2 =
114 pcv.pcv_information2
115 )
116 OR ( pct.total_unique_columns = 3
117 AND pcv2.pcv_information1 =
118 pcv.pcv_information1
119 AND pcv2.pcv_information2 =
120 pcv.pcv_information2
121 AND pcv2.pcv_information3 =
122 pcv.pcv_information3
123 )
124 OR ( pct.total_unique_columns = 4
125 AND pcv2.pcv_information1 =
126 pcv.pcv_information1
127 AND pcv2.pcv_information2 =
128 pcv.pcv_information2
129 AND pcv2.pcv_information3 =
130 pcv.pcv_information3
131 AND pcv2.pcv_information4 =
132 pcv.pcv_information4
133 )
134 OR ( pct.total_unique_columns = 5
135 AND pcv2.pcv_information1 =
136 pcv.pcv_information1
137 AND pcv2.pcv_information2 =
138 pcv.pcv_information2
139 AND pcv2.pcv_information3 =
140 pcv.pcv_information3
141 AND pcv2.pcv_information4 =
142 pcv.pcv_information4
143 AND pcv2.pcv_information5 =
144 pcv.pcv_information5
145 )
146 OR ( pct.total_unique_columns = 6
147 AND pcv2.pcv_information1 =
148 pcv.pcv_information1
149 AND pcv2.pcv_information2 =
150 pcv.pcv_information2
151 AND pcv2.pcv_information3 =
152 pcv.pcv_information3
153 AND pcv2.pcv_information4 =
154 pcv.pcv_information4
155 AND pcv2.pcv_information5 =
156 pcv.pcv_information5
157 AND pcv2.pcv_information6 =
158 pcv.pcv_information6
159 )
160 OR ( pct.total_unique_columns = 7
161 AND pcv2.pcv_information1 =
162 pcv.pcv_information1
163 AND pcv2.pcv_information2 =
164 pcv.pcv_information2
165 AND pcv2.pcv_information3 =
166 pcv.pcv_information3
167 AND pcv2.pcv_information4 =
168 pcv.pcv_information4
169 AND pcv2.pcv_information5 =
170 pcv.pcv_information5
171 AND pcv2.pcv_information6 =
172 pcv.pcv_information6
173 AND pcv2.pcv_information7 =
174 pcv.pcv_information7
175 )
176 OR ( pct.total_unique_columns = 8
177 AND pcv2.pcv_information1 =
178 pcv.pcv_information1
179 AND pcv2.pcv_information2 =
180 pcv.pcv_information2
181 AND pcv2.pcv_information3 =
182 pcv.pcv_information3
183 AND pcv2.pcv_information4 =
184 pcv.pcv_information4
185 AND pcv2.pcv_information5 =
186 pcv.pcv_information5
187 AND pcv2.pcv_information6 =
188 pcv.pcv_information6
189 AND pcv2.pcv_information7 =
190 pcv.pcv_information7
191 AND pcv2.pcv_information8 =
192 pcv.pcv_information8
193 )
194 OR ( pct.total_unique_columns = 9
195 AND pcv2.pcv_information1 =
196 pcv.pcv_information1
197 AND pcv2.pcv_information2 =
198 pcv.pcv_information2
199 AND pcv2.pcv_information3 =
200 pcv.pcv_information3
201 AND pcv2.pcv_information4 =
202 pcv.pcv_information4
203 AND pcv2.pcv_information5 =
204 pcv.pcv_information5
205 AND pcv2.pcv_information6 =
206 pcv.pcv_information6
207 AND pcv2.pcv_information7 =
208 pcv.pcv_information7
209 AND pcv2.pcv_information8 =
210 pcv.pcv_information8
211 AND pcv2.pcv_information9 =
212 pcv.pcv_information9
213 )
214 OR ( pct.total_unique_columns =
215 10
216 AND pcv2.pcv_information1 =
217 pcv.pcv_information1
218 AND pcv2.pcv_information2 =
219 pcv.pcv_information2
220 AND pcv2.pcv_information3 =
221 pcv.pcv_information3
222 AND pcv2.pcv_information4 =
223 pcv.pcv_information4
224 AND pcv2.pcv_information5 =
225 pcv.pcv_information5
226 AND pcv2.pcv_information6 =
227 pcv.pcv_information6
228 AND pcv2.pcv_information7 =
229 pcv.pcv_information7
230 AND pcv2.pcv_information8 =
231 pcv.pcv_information8
232 AND pcv2.pcv_information9 =
233 pcv.pcv_information9
234 AND pcv2.pcv_information10 =
235 pcv.pcv_information10
236 )
237 )
238 )
239 OR pct.multiple_occurences_flag = 'N'
240 ))
241 )
242 OR ( pcv.business_group_id IS NULL
243 AND pcv.legislation_code IS NULL
244 AND NOT EXISTS(
245 SELECT 1
246 FROM pqp_configuration_values pcv3
247 WHERE pcv3.pcv_information_category =
248 pcv.pcv_information_category
249 AND pcv3.configuration_value_id <>
250 pcv.configuration_value_id
251 AND ( pcv3.business_group_id =
252 p_business_group_id
253 OR pcv3.legislation_code = c_legislation_code
254 )
255 AND ( ( pct.multiple_occurences_flag = 'Y'
256 AND ( ( pct.total_unique_columns = 1
257 AND pcv3.pcv_information1 =
258 pcv.pcv_information1
259 )
260 OR ( pct.total_unique_columns = 2
261 AND pcv3.pcv_information1 =
262 pcv.pcv_information1
263 AND pcv3.pcv_information2 =
264 pcv.pcv_information2
265 )
266 OR ( pct.total_unique_columns = 3
267 AND pcv3.pcv_information1 =
268 pcv.pcv_information1
269 AND pcv3.pcv_information2 =
270 pcv.pcv_information2
271 AND pcv3.pcv_information3 =
272 pcv.pcv_information3
273 )
274 OR ( pct.total_unique_columns = 4
275 AND pcv3.pcv_information1 =
276 pcv.pcv_information1
277 AND pcv3.pcv_information2 =
278 pcv.pcv_information2
279 AND pcv3.pcv_information3 =
280 pcv.pcv_information3
281 AND pcv3.pcv_information4 =
282 pcv.pcv_information4
283 )
284 OR ( pct.total_unique_columns = 5
285 AND pcv3.pcv_information1 =
286 pcv.pcv_information1
287 AND pcv3.pcv_information2 =
288 pcv.pcv_information2
289 AND pcv3.pcv_information3 =
290 pcv.pcv_information3
291 AND pcv3.pcv_information4 =
292 pcv.pcv_information4
293 AND pcv3.pcv_information5 =
294 pcv.pcv_information5
295 )
296 OR ( pct.total_unique_columns = 6
297 AND pcv3.pcv_information1 =
298 pcv.pcv_information1
299 AND pcv3.pcv_information2 =
300 pcv.pcv_information2
301 AND pcv3.pcv_information3 =
302 pcv.pcv_information3
303 AND pcv3.pcv_information4 =
304 pcv.pcv_information4
305 AND pcv3.pcv_information5 =
306 pcv.pcv_information5
307 AND pcv3.pcv_information6 =
308 pcv.pcv_information6
309 )
310 OR ( pct.total_unique_columns = 7
311 AND pcv3.pcv_information1 =
312 pcv.pcv_information1
313 AND pcv3.pcv_information2 =
314 pcv.pcv_information2
315 AND pcv3.pcv_information3 =
316 pcv.pcv_information3
317 AND pcv3.pcv_information4 =
318 pcv.pcv_information4
319 AND pcv3.pcv_information5 =
320 pcv.pcv_information5
321 AND pcv3.pcv_information6 =
322 pcv.pcv_information6
323 AND pcv3.pcv_information7 =
324 pcv.pcv_information7
325 )
326 OR ( pct.total_unique_columns = 8
327 AND pcv3.pcv_information1 =
328 pcv.pcv_information1
329 AND pcv3.pcv_information2 =
330 pcv.pcv_information2
331 AND pcv3.pcv_information3 =
332 pcv.pcv_information3
333 AND pcv3.pcv_information4 =
334 pcv.pcv_information4
335 AND pcv3.pcv_information5 =
336 pcv.pcv_information5
337 AND pcv3.pcv_information6 =
338 pcv.pcv_information6
339 AND pcv3.pcv_information7 =
340 pcv.pcv_information7
341 AND pcv3.pcv_information8 =
342 pcv.pcv_information8
343 )
344 OR ( pct.total_unique_columns = 9
345 AND pcv3.pcv_information1 =
346 pcv.pcv_information1
347 AND pcv3.pcv_information2 =
348 pcv.pcv_information2
349 AND pcv3.pcv_information3 =
350 pcv.pcv_information3
351 AND pcv3.pcv_information4 =
352 pcv.pcv_information4
353 AND pcv3.pcv_information5 =
354 pcv.pcv_information5
355 AND pcv3.pcv_information6 =
356 pcv.pcv_information6
357 AND pcv3.pcv_information7 =
358 pcv.pcv_information7
359 AND pcv3.pcv_information8 =
360 pcv.pcv_information8
361 AND pcv3.pcv_information9 =
362 pcv.pcv_information9
363 )
364 OR ( pct.total_unique_columns =
365 10
366 AND pcv3.pcv_information1 =
367 pcv.pcv_information1
368 AND pcv3.pcv_information2 =
369 pcv.pcv_information2
370 AND pcv3.pcv_information3 =
371 pcv.pcv_information3
372 AND pcv3.pcv_information4 =
373 pcv.pcv_information4
374 AND pcv3.pcv_information5 =
375 pcv.pcv_information5
376 AND pcv3.pcv_information6 =
377 pcv.pcv_information6
378 AND pcv3.pcv_information7 =
379 pcv.pcv_information7
380 AND pcv3.pcv_information8 =
381 pcv.pcv_information8
382 AND pcv3.pcv_information9 =
383 pcv.pcv_information9
384 AND pcv3.pcv_information10 =
385 pcv.pcv_information10
386 )
387 )
388 )
389 OR pct.multiple_occurences_flag = 'N'
390 ))
391 )
392 )
393 AND ( pct.legislation_code = c_legislation_code
394 OR pct.legislation_code IS NULL
395 );
396
397 l_proc_name VARCHAR2(80)
398 := g_package_name || 'get_config_type_values';
399 l_proc_step PLS_INTEGER;
400 l_legislation_code per_business_groups_perf.legislation_code%TYPE;
401 l_config_values_rec csr_get_config_values%ROWTYPE;
402 l_config_value_id NUMBER;
403 l_tab_config_values t_config_values;
404 --
405 BEGIN
406 --
407 IF g_debug
408 THEN
409 l_proc_step := 10;
410 DEBUG('Entering: ' || l_proc_name, l_proc_step);
411 END IF;
412
413 IF p_legislation_code IS NULL THEN
414 IF p_business_group_id IS NOT NULL THEN
415 -- Get legislation code
416 l_legislation_code :=
417 pqp_utilities.pqp_get_legislation_code(p_business_group_id => p_business_group_id);
418 END IF;
419 ELSE
420 l_legislation_code := p_legislation_code;
421 END IF;
422
423
424 IF g_debug
425 THEN
426 l_proc_step := 20;
427 DEBUG('l_legislation_code: ' || l_legislation_code);
428 DEBUG(l_proc_name, l_proc_step);
429 END IF;
430
431 -- Get configuration value for the configuration type
432 OPEN csr_get_config_values(l_legislation_code);
433
434 LOOP
435 FETCH csr_get_config_values INTO l_config_values_rec;
436 EXIT WHEN csr_get_config_values%NOTFOUND;
437 -- Store the values in the collection
438 l_config_value_id :=
439 l_config_values_rec.configuration_value_id;
440 l_tab_config_values(l_config_value_id).pcv_information1 :=
441 l_config_values_rec.pcv_information1;
442 l_tab_config_values(l_config_value_id).pcv_information2 :=
443 l_config_values_rec.pcv_information2;
444 l_tab_config_values(l_config_value_id).pcv_information3 :=
445 l_config_values_rec.pcv_information3;
446 l_tab_config_values(l_config_value_id).pcv_information4 :=
447 l_config_values_rec.pcv_information4;
448 l_tab_config_values(l_config_value_id).pcv_information5 :=
449 l_config_values_rec.pcv_information5;
450 l_tab_config_values(l_config_value_id).pcv_information6 :=
451 l_config_values_rec.pcv_information6;
452 l_tab_config_values(l_config_value_id).pcv_information7 :=
453 l_config_values_rec.pcv_information7;
454 l_tab_config_values(l_config_value_id).pcv_information8 :=
455 l_config_values_rec.pcv_information8;
456 l_tab_config_values(l_config_value_id).pcv_information9 :=
457 l_config_values_rec.pcv_information9;
458 l_tab_config_values(l_config_value_id).pcv_information10 :=
459 l_config_values_rec.pcv_information10;
460 l_tab_config_values(l_config_value_id).pcv_information11 :=
461 l_config_values_rec.pcv_information11;
462 l_tab_config_values(l_config_value_id).pcv_information12 :=
463 l_config_values_rec.pcv_information12;
464 l_tab_config_values(l_config_value_id).pcv_information13 :=
465 l_config_values_rec.pcv_information13;
466 l_tab_config_values(l_config_value_id).pcv_information14 :=
467 l_config_values_rec.pcv_information14;
468 l_tab_config_values(l_config_value_id).pcv_information15 :=
469 l_config_values_rec.pcv_information15;
470 l_tab_config_values(l_config_value_id).pcv_information16 :=
471 l_config_values_rec.pcv_information16;
472 l_tab_config_values(l_config_value_id).pcv_information17 :=
473 l_config_values_rec.pcv_information17;
474 l_tab_config_values(l_config_value_id).pcv_information18 :=
475 l_config_values_rec.pcv_information18;
476 l_tab_config_values(l_config_value_id).pcv_information19 :=
477 l_config_values_rec.pcv_information19;
478 l_tab_config_values(l_config_value_id).pcv_information20 :=
479 l_config_values_rec.pcv_information20;
480
481 IF g_debug
482 THEN
483 l_proc_step := 30;
484 DEBUG( 'l_config_values_rec.pcv_information1: '
485 || l_config_values_rec.pcv_information1
486 );
487 DEBUG( 'l_config_values_rec.pcv_information2: '
488 || l_config_values_rec.pcv_information2
489 );
490 DEBUG( 'l_config_values_rec.pcv_information3: '
491 || l_config_values_rec.pcv_information3
492 );
493 DEBUG( 'l_config_values_rec.pcv_information4: '
494 || l_config_values_rec.pcv_information4
495 );
496 DEBUG( 'l_config_values_rec.pcv_information5: '
497 || l_config_values_rec.pcv_information5
498 );
499 DEBUG( 'l_config_values_rec.pcv_information6: '
500 || l_config_values_rec.pcv_information6
501 );
502 DEBUG( 'l_config_values_rec.pcv_information7: '
503 || l_config_values_rec.pcv_information7
504 );
505 DEBUG( 'l_config_values_rec.pcv_information8: '
506 || l_config_values_rec.pcv_information8
507 );
508 DEBUG( 'l_config_values_rec.pcv_information9: '
509 || l_config_values_rec.pcv_information9
510 );
511 DEBUG( 'l_config_values_rec.pcv_information10: '
512 || l_config_values_rec.pcv_information10
513 );
514 DEBUG( 'l_config_values_rec.pcv_information11: '
515 || l_config_values_rec.pcv_information11
516 );
517 DEBUG( 'l_config_values_rec.pcv_information12: '
518 || l_config_values_rec.pcv_information12
519 );
520 DEBUG( 'l_config_values_rec.pcv_information13: '
521 || l_config_values_rec.pcv_information13
522 );
523 DEBUG( 'l_config_values_rec.pcv_information14: '
524 || l_config_values_rec.pcv_information14
525 );
526 DEBUG( 'l_config_values_rec.pcv_information15: '
527 || l_config_values_rec.pcv_information15
528 );
529 DEBUG( 'l_config_values_rec.pcv_information16: '
530 || l_config_values_rec.pcv_information16
531 );
532 DEBUG( 'l_config_values_rec.pcv_information17: '
533 || l_config_values_rec.pcv_information17
534 );
535 DEBUG( 'l_config_values_rec.pcv_information18: '
536 || l_config_values_rec.pcv_information18
537 );
538 DEBUG( 'l_config_values_rec.pcv_information19: '
539 || l_config_values_rec.pcv_information19
540 );
541 DEBUG( 'l_config_values_rec.pcv_information20: '
542 || l_config_values_rec.pcv_information20
543 );
544 DEBUG(l_proc_name, l_proc_step);
545 END IF;
546 END LOOP;
547
548 CLOSE csr_get_config_values;
549 p_tab_config_values := l_tab_config_values;
550
551 IF g_debug
552 THEN
553 l_proc_step := 40;
554 DEBUG('Leaving: ' || l_proc_name, l_proc_step);
555 END IF;
556 EXCEPTION
557 WHEN OTHERS
558 THEN
559 --clear_cache;
560
561 IF SQLCODE <> hr_utility.hr_error_number
562 THEN
563 debug_others(l_proc_name, l_proc_step);
564
565 IF g_debug
566 THEN
567 DEBUG('Leaving: ' || l_proc_name, -999);
568 END IF;
569
570 fnd_message.raise_error;
571 ELSE
572 RAISE;
573 END IF;
574 END get_config_type_values;
575
576
577
578 --
579 --
580 --
581 PROCEDURE debug(p_trace_number IN NUMBER)
582 IS
583 BEGIN
584 debug(fnd_number.number_to_canonical(p_trace_number));
585 END debug;
586
587 --
588 --
589 --
590 PROCEDURE debug(p_trace_date IN DATE)
591 IS
592 BEGIN
593 debug(fnd_date.date_to_canonical(p_trace_date));
594 END debug;
595
596 --
597 --
598 --
599 PROCEDURE debug_enter(
600 p_proc_name IN VARCHAR2 DEFAULT NULL
601 ,p_trace_on IN VARCHAR2 DEFAULT NULL
602 )
603 IS
604
605 BEGIN
606
607 g_nested_level := g_nested_level + 1;
608 debug('Entering: ' || NVL(p_proc_name, g_package_name)
609 ,g_nested_level * 100);
610
611 END debug_enter;
612
613 --
614 --
615 --
616 PROCEDURE debug_exit(p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
617 IS
618 BEGIN
619
620 debug('Leaving: ' || NVL(p_proc_name, g_package_name)
621 ,-g_nested_level * 100);
622 g_nested_level := g_nested_level - 1;
623
624 END debug_exit;
625
626 --
627 --
628 --
629 PROCEDURE debug_others(
630 p_proc_name IN VARCHAR2
631 ,p_proc_step IN NUMBER DEFAULT NULL
632 )
633 IS
634 l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
635 BEGIN
636
637 IF g_debug
638 THEN
639 debug(p_proc_name, SQLCODE);
640 debug(SQLERRM);
641 END IF;
642
643 l_message :=
644 p_proc_name
645 || '{'
646 || fnd_number.number_to_canonical(p_proc_step)
647 || '}: '
648 || SUBSTRB(SQLERRM, 1, 2000);
649
650 IF g_debug
651 THEN
652 debug(l_message);
653 END IF;
654
655 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
656 fnd_message.set_token('TOKEN', l_message);
657
658 END debug_others;
659
660 --
661 --
662 --
663 -- get_col_value Funciton returns the value of the column ( passed as
664 -- input parameter ) in the table ( input parameter) for a
665 -- given Key column and its value. Depending upon the data type
666 -- of the column the value will be converted and returned in
667 -- varchar2. A Out parameter message is returned whenever there
668 -- is a error
669
670 FUNCTION get_col_value(
671 p_col_nam IN VARCHAR2
672 , -- Col Value to be found
673 p_key_val IN NUMBER
674 , -- Value of the Key Column
675 p_table IN VARCHAR2
676 , -- Table Name
677 p_key_col IN VARCHAR2
678 , -- The Key Column Name
679 p_where IN VARCHAR2
680 , -- Where Clause in addition if any
681 p_error_code OUT NOCOPY NUMBER
682 ,p_message OUT NOCOPY VARCHAR2
683 )
684 RETURN VARCHAR2
685 IS
686 l_data_type fnd_columns.column_type%TYPE;
687 l_val VARCHAR2(250);
688 l_date DATE;
689 l_num NUMBER;
690 l_message VARCHAR2(250);
691 l_proc_name VARCHAR2(61)
692 := g_package_name || 'get_col_value';
693 l_select VARCHAR2(1000);
694
695 TYPE ref_csr_typ IS REF CURSOR;
696
697 c_column ref_csr_typ;
698 BEGIN
699 g_debug := hr_utility.debug_enabled;
700 IF g_debug THEN
701 debug_enter(l_proc_name);
702 END IF;
703 p_error_code := 0;
704 IF g_debug THEN
705 debug(p_col_nam);
706 debug(p_key_val);
707 debug(p_table);
708 debug(p_key_col);
709 debug(p_where);
710 debug(l_proc_name, 10);
711 END IF;
712 -- Get the Data type of the Column and convert into varchar2
713 l_data_type :=
714 get_data_type(
715 p_col_nam => p_col_nam
716 ,p_tab_nam => p_table
717 ,p_error_code => p_error_code
718 ,p_message => l_message
719 );
720
721 IF l_message IS NOT NULL
722 THEN
723 IF g_debug THEN
724 debug(l_proc_name, 30);
725 END IF;
726 p_message := l_message;
727 p_error_code := -1;
728 RETURN NULL;
729 END IF;
730
731 --
732 IF g_debug THEN
733 debug(l_proc_name, 40);
734 END IF;
735 IF g_debug THEN
736 debug(' Key Col:' || p_key_col || ' Value : ' || p_key_val);
737 END IF;
738 --
739 l_select :=
740 'SELECT '
741 || p_col_nam
742 || ' FROM '
743 || p_table
744 || ' WHERE '
745 || p_key_col
746 || ' = '
747 || p_key_val
748 || p_where;
749 IF g_debug THEN
750 debug(l_select);
751 END IF;
752 OPEN c_column FOR 'SELECT '
753 || p_col_nam
754 || ' FROM '
755 || p_table
756 || ' WHERE '
757 || p_key_col
758 || ' = '
759 || p_key_val
760 || p_where;
761
762 IF l_data_type = 'V'
763 THEN
764 IF g_debug THEN
765 debug(l_proc_name, 40);
766 END IF;
767 FETCH c_column INTO l_val;
768 ELSIF l_data_type = 'D'
769 THEN
770 IF g_debug THEN
771 debug(l_proc_name, 50);
772 END IF;
773 FETCH c_column INTO l_date;
774 l_val := fnd_date.date_to_canonical(l_date);
775 ELSIF l_data_type = 'N'
776 THEN
777 IF g_debug THEN
778 debug(l_proc_name, 60);
779 END IF;
780 FETCH c_column INTO l_num;
781 l_val := fnd_number.number_to_canonical(l_num);
782 END IF;
783
784 CLOSE c_column;
785 IF g_debug THEN
786 debug_exit(l_proc_name);
787 END IF;
788 RETURN l_val;
789 EXCEPTION
790 --
791 WHEN NO_DATA_FOUND
792 THEN
793 IF g_debug THEN
794 debug(' No Data Found');
795 debug_exit(l_proc_name);
796 END IF;
797 fnd_message.set_name('PQP', 'PQP_230585_INV_ABS_ID');
798 fnd_message.set_token('P_KEY_VAL', p_key_col);
799 p_message := fnd_message.get();
800 p_error_code := -1;
801 RETURN NULL;
802 -- Added by tmehra for nocopy changes Feb'03
803
804 WHEN OTHERS
805 THEN
806 IF g_debug THEN
807 debug(l_proc_name||':Others Exception:');
808 debug(SQLCODE);
809 debug(SQLERRM);
810 debug_exit(l_proc_name);
811 END IF;
812 fnd_message.set_name('PQP', 'PQP_230585_INV_ABS_ID');
813 fnd_message.set_token('P_KEY_VAL', p_key_col);
814 p_message := fnd_message.get();
815 p_error_code := -1;
816 RETURN NULL;
817 --
818 END get_col_value;
819
820 -- get_data_type function returns the data type of the column name
821 -- passed as input parameter by fetching it from fnd tables. any
822 -- errors will be returned in out parameter p_message
823
824 FUNCTION get_data_type(
825 p_col_nam IN VARCHAR2
826 ,p_tab_nam IN VARCHAR2
827 ,p_error_code OUT NOCOPY NUMBER
828 ,p_message OUT NOCOPY VARCHAR2
829 )
830 RETURN VARCHAR2
831 IS
832 l_data_type fnd_columns.column_type%TYPE;
833 l_proc_name VARCHAR2(61)
834 := g_package_name || 'get_data_type';
835 BEGIN
836 g_debug := hr_utility.debug_enabled;
837 IF g_debug THEN
838 debug_enter(l_proc_name);
839 END IF;
840 p_error_code := 0;
841 OPEN csr_data_type(p_tab_nam => p_tab_nam, p_col_nam => p_col_nam);
842 FETCH csr_data_type INTO l_data_type;
843
844 IF csr_data_type%NOTFOUND
845 THEN
846 fnd_message.set_name('PQP', 'PQP_230595_INV_COL_NAME');
847 fnd_message.set_token('P_COL_NAME', p_col_nam);
848 p_message := fnd_message.get();
849 p_error_code := -1;
850 END IF;
851
852 CLOSE csr_data_type;
853 IF g_debug THEN
854 debug_exit(l_proc_name);
855 END IF;
856 RETURN l_data_type;
857 -- Added by tmehra for nocopy changes Feb'03
858
859 EXCEPTION
860 WHEN OTHERS
861 THEN
862 IF g_debug THEN
863 debug(l_proc_name||':Others Exception:');
864 debug(SQLCODE);
865 debug(SQLERRM);
866 debug_exit(l_proc_name);
867 END IF;
868 fnd_message.set_name('PQP', 'PQP_230595_INV_COL_NAME');
869 fnd_message.set_token('P_COL_NAME', p_col_nam);
870 p_message := fnd_message.get();
871 p_error_code := -1;
872 RETURN NULL;
873 END get_data_type;
874
875 -- get_ddf_value function returns the value of the Developers flex filed.
876 -- The flex filed name, Context,flex filed title are input
877 -- paramters to identify the flex field.
878 -- Depending upon the Key Column and its Value the segment value
879 -- will be identified and returned.
880
881 FUNCTION get_ddf_value(
882 p_flex_name IN VARCHAR2
883 ,p_flex_context IN VARCHAR2
884 ,p_flex_field_title IN VARCHAR2
885 ,p_key_col IN VARCHAR2
886 ,p_key_val IN NUMBER
887 ,p_effective_date IN DATE
888 ,p_eff_date_req IN VARCHAR2
889 ,p_business_group_id IN NUMBER
890 ,p_bus_group_id_req IN VARCHAR2
891 ,p_error_code OUT NOCOPY NUMBER
892 ,p_message OUT NOCOPY VARCHAR2
893 )
894 RETURN VARCHAR2
895 IS
896 l_col_name VARCHAR2(30);
897 l_val VARCHAR2(250);
898 l_message VARCHAR2(250);
899 l_tabname VARCHAR2(30);
900 l_where VARCHAR2(1000);
901 l_proc_name VARCHAR2(61)
902 := g_package_name || 'get_ddf_value';
903 BEGIN
904 g_debug := hr_utility.debug_enabled;
905 IF g_debug THEN
906 debug_enter(l_proc_name);
907 END IF;
908
909 IF g_debug THEN
910 debug(p_flex_name);
911 debug(p_flex_context);
912 debug(p_flex_field_title);
913 debug(p_key_col);
914 debug(p_key_val);
915 debug(p_effective_date);
916 debug(p_eff_date_req);
917 debug(p_business_group_id);
918 debug(p_bus_group_id_req);
919 END IF;
920
921 p_error_code := 0;
922 -- Get the Column name the field stored in table
923 IF g_debug THEN
924 debug(l_proc_name, 10);
925 END IF;
926 l_col_name :=
927 pqp_utilities.get_segment_name(
928 p_flex_name => p_flex_name
929 ,p_flex_field_title => p_flex_field_title
930 ,p_flex_context => p_flex_context
931 ,p_tab_nam => l_tabname
932 ,p_error_code => p_error_code
933 ,p_message => l_message
934 );
935 IF g_debug THEN
936 debug(l_proc_name, 20);
937 END IF;
938
939 --
940 IF l_message IS NOT NULL
941 THEN
942 IF g_debug THEN
943 debug(l_proc_name, 30);
944 END IF;
945 p_message := l_message;
946 p_error_code := -1;
947 RETURN NULL;
948 END IF;
949
950 IF g_debug THEN
951 debug(l_proc_name, 40);
952 END IF;
953
954 --
955 -- If Effective Date Validation is required then Construct the where clause
956 --
957 IF p_eff_date_req = 'Y'
958 THEN
959 IF g_debug THEN
960 debug(l_proc_name, 50);
961 END IF;
962 l_where :=
963 ' AND TO_DATE('''
964 || TO_CHAR(p_effective_date, 'DD-MM-YYYY')
965 || ''',''DD-MM-YYYY'')'
966 || ' BETWEEN effective_start_date
967 AND effective_end_date ';
968 END IF;
969
970 --
971 -- If Business Group Validation is required then Construct the where clause
972 --
973 IF p_bus_group_id_req = 'Y'
974 THEN
975 IF g_debug THEN
976 debug(l_proc_name, 60);
977 END IF;
978 l_where :=
979 l_where
980 || ' AND ( business_group_id = '
981 || TO_CHAR(p_business_group_id)
982 || ') ';
983 END IF;
984
985 IF g_debug THEN
986 debug(l_where);
987 END IF;
988 -- Get the Value of the segment for the given key col and its value.
989 IF g_debug THEN
990 debug(l_proc_name, 70);
991 END IF;
992 l_val :=
993 pqp_utilities.get_col_value(
994 p_col_nam => l_col_name
995 ,p_key_val => p_key_val
996 ,p_table => l_tabname
997 ,p_key_col => p_key_col
998 ,p_where => l_where
999 ,p_error_code => p_error_code
1000 ,p_message => l_message
1001 );
1002 IF g_debug THEN
1003 debug(l_proc_name, 30);
1004 END IF;
1005
1006 --
1007 IF l_message IS NOT NULL
1008 THEN
1009 IF g_debug THEN
1010 debug(l_proc_name, 40);
1011 END IF;
1012 p_message := l_message;
1013 p_error_code := -1;
1014 END IF;
1015
1016 --
1017 IF g_debug THEN
1018 debug_exit(l_proc_name);
1019 END IF;
1020 RETURN l_val;
1021 -- Added by tmehra for nocopy changes Feb'03
1022
1023 EXCEPTION
1024 WHEN OTHERS
1025 THEN
1026 IF g_debug THEN
1027 debug(l_proc_name||':Others Exception:');
1028 debug(SQLCODE);
1029 debug(SQLERRM);
1030 debug_exit(l_proc_name);
1031 END IF;
1032 p_message := SQLERRM;
1033 p_error_code := -1;
1034 RETURN NULL;
1035 END get_ddf_value;
1036
1037 -- get_df_value function returns value of the Descriptive Flex Filed.
1038 -- This function identifies the Context based on key column
1039 -- and its value and inturn passes those values to get_ddf_value
1040 -- and gets the segment value. In case of EITs flex context is
1041 -- passed as a parameter.
1042 FUNCTION get_df_value(
1043 p_flex_name IN VARCHAR2
1044 ,p_flex_context IN VARCHAR2
1045 ,p_flex_field_title IN VARCHAR2
1046 ,p_key_col IN VARCHAR2
1047 ,p_key_val IN VARCHAR2
1048 ,p_tab_name IN VARCHAR2
1049 ,p_effective_date IN DATE
1050 ,p_eff_date_req IN VARCHAR2
1051 ,p_business_group_id IN NUMBER
1052 ,p_bus_group_id_req IN VARCHAR2
1053 ,p_error_code OUT NOCOPY NUMBER
1054 ,p_message OUT NOCOPY VARCHAR2
1055 )
1056 RETURN VARCHAR2
1057 IS
1058 l_context VARCHAR2(30);
1059 l_val VARCHAR2(250);
1060
1061 TYPE context_ref_csr_typ IS REF CURSOR;
1062
1063 c_context context_ref_csr_typ;
1064 l_proc_name VARCHAR2(61)
1065 := g_package_name || 'get_df_value';
1066 BEGIN
1067 g_debug := hr_utility.debug_enabled;
1068 IF g_debug THEN
1069 debug_enter(l_proc_name);
1070 END IF;
1071 p_error_code := 0;
1072
1073 IF p_flex_context IS NULL
1074 THEN
1075 IF g_debug THEN
1076 debug(l_proc_name, 10);
1077 END IF;
1078 OPEN c_context FOR 'SELECT attribute_category FROM '
1079 || p_tab_name
1080 || ' WHERE '
1081 || p_key_col
1082 || '='
1083 || p_key_val;
1084 FETCH c_context INTO l_context;
1085 CLOSE c_context;
1086 IF g_debug THEN
1087 debug(l_proc_name, 20);
1088 END IF;
1089
1090 IF l_context IS NULL
1091 THEN
1092 IF g_debug THEN
1093 debug(l_proc_name, 30);
1094 END IF;
1095 fnd_message.set_name('PQP', 'PQP_230596_NO_FLEX_DATA');
1096 fnd_message.set_token('FLEX_NAME', p_flex_name);
1097 p_message := fnd_message.get();
1098 p_error_code := -1;
1099 RETURN NULL;
1100 END IF;
1101 ELSE
1102 IF g_debug THEN
1103 debug(l_proc_name, 40);
1104 END IF;
1105 l_context := p_flex_context;
1106 END IF;
1107
1108 -- Call get_ddf_value to get the value of the segment.
1109 IF g_debug THEN
1110 debug(l_proc_name, 50);
1111 END IF;
1112 l_val :=
1113 pqp_utilities.get_ddf_value(
1114 p_flex_name => p_flex_name
1115 ,p_flex_context => l_context
1116 ,p_flex_field_title => p_flex_field_title
1117 ,p_key_col => p_key_col
1118 ,p_key_val => p_key_val
1119 ,p_effective_date => p_effective_date
1120 ,p_eff_date_req => p_eff_date_req
1121 ,p_business_group_id => p_business_group_id
1122 ,p_bus_group_id_req => p_bus_group_id_req
1123 ,p_error_code => p_error_code
1124 ,p_message => p_message
1125 );
1126 IF g_debug THEN
1127 debug(l_val);
1128 END IF;
1129 IF g_debug THEN
1130 debug_exit(l_proc_name);
1131 END IF;
1132 RETURN l_val;
1133 -- Added by tmehra for nocopy changes Feb'03
1134
1135 EXCEPTION
1136 WHEN OTHERS
1137 THEN
1138 IF g_debug THEN
1139 debug(l_proc_name||':Others Exception:');
1140 debug(SQLCODE);
1141 debug(SQLERRM);
1142 debug_exit(l_proc_name);
1143 END IF;
1144 p_message := SQLERRM;
1145 p_error_code := -1;
1146 RETURN NULL;
1147 END get_df_value;
1148
1149 -- get_segment_name function returns the column Name the flex
1150 -- filed is mapped to.
1151 FUNCTION get_segment_name(
1152 p_flex_name IN VARCHAR2
1153 ,p_flex_field_title IN VARCHAR2
1154 ,p_flex_context IN VARCHAR2
1155 ,p_tab_nam OUT NOCOPY VARCHAR2
1156 ,p_error_code OUT NOCOPY NUMBER
1157 ,p_message OUT NOCOPY VARCHAR2
1158 )
1159 RETURN VARCHAR2
1160 IS
1161 l_col_name VARCHAR2(30);
1162 l_proc_name VARCHAR2(61)
1163 := g_package_name || 'get_segment_name';
1164 BEGIN
1165 g_debug := hr_utility.debug_enabled;
1166 IF g_debug THEN
1167 debug_enter(l_proc_name);
1168 END IF;
1169 p_error_code := 0;
1170 --
1171 OPEN csr_seg_name(
1172 p_flex_name => p_flex_name
1173 ,p_flex_context => p_flex_context
1174 ,p_flex_field_title => p_flex_field_title
1175 );
1176 FETCH csr_seg_name INTO l_col_name;
1177
1178 --
1179 IF csr_seg_name%NOTFOUND
1180 THEN
1181 fnd_message.set_name('PQP', 'PQP_230597_INV_SEG_NAME');
1182 fnd_message.set_token('FLEX_TITLE', p_flex_field_title);
1183 p_message := fnd_message.get();
1184 p_error_code := -1;
1185 CLOSE csr_seg_name;
1186 IF g_debug THEN
1187 debug(l_proc_name || p_message);
1188 END IF;
1189 RETURN NULL;
1190 END IF;
1191
1192 --
1193 CLOSE csr_seg_name;
1194 --
1195 --
1196 OPEN csr_tab_name(p_flex_name => p_flex_name);
1197 FETCH csr_tab_name INTO p_tab_nam;
1198 CLOSE csr_tab_name;
1199 --
1200 IF g_debug THEN
1201 debug_exit(l_proc_name);
1202 END IF;
1203 RETURN l_col_name;
1204 -- Added by tmehra for nocopy changes Feb'03
1205
1206 EXCEPTION
1207 WHEN OTHERS
1208 THEN
1209 IF g_debug THEN
1210 debug(l_proc_name||':Others Exception:');
1211 debug(SQLCODE);
1212 debug(SQLERRM);
1213 debug_exit(l_proc_name);
1214 END IF;
1215 p_message := SQLERRM;
1216 p_error_code := -1;
1217 RETURN NULL;
1218 END get_segment_name;
1219
1220 -- Function to get Concatenated String from the View
1221
1222 FUNCTION pqp_get_concat_value(
1223 p_key_col IN VARCHAR2
1224 ,p_key_val IN VARCHAR2
1225 ,p_tab_name IN VARCHAR2
1226 ,p_view_name IN VARCHAR2
1227 ,p_message OUT NOCOPY VARCHAR2
1228 )
1229 RETURN VARCHAR2
1230 IS
1231 TYPE rowid_ref_csr_typ IS REF CURSOR;
1232
1233 c_rowid rowid_ref_csr_typ;
1234 c_view rowid_ref_csr_typ;
1235 l_rowid VARCHAR2(30);
1236 l_concat_string VARCHAR2(2000);
1237 l_proc_name VARCHAR2(61)
1238 := g_package_name || 'pqp_get_concat_value';
1239 BEGIN
1240 g_debug := hr_utility.debug_enabled;
1241 IF g_debug THEN
1242 debug_enter(l_proc_name);
1243 END IF;
1244 OPEN c_rowid FOR ' SELECT rowid FROM '
1245 || p_tab_name
1246 || ' WHERE '
1247 || p_key_col
1248 || ' = '
1249 || p_key_val;
1250 FETCH c_rowid INTO l_rowid;
1251 CLOSE c_rowid;
1252 OPEN c_view FOR ' SELECT concatenated_segments
1253 FROM '
1254 || p_view_name
1255 || ' WHERE row_id = '
1256 || ''''
1257 || l_rowid
1258 || '''';
1259 FETCH c_view INTO l_concat_string;
1260 CLOSE c_view;
1261 IF g_debug THEN
1262 debug(l_concat_string);
1263 END IF;
1264 IF g_debug THEN
1265 debug_exit(l_proc_name);
1266 END IF;
1267 RETURN l_concat_string;
1268 -- Added by tmehra for nocopy changes Feb'03
1269
1270 EXCEPTION
1271 WHEN OTHERS
1272 THEN
1273 IF g_debug THEN
1274 debug(l_proc_name||':Others Exception:');
1275 debug(SQLCODE);
1276 debug(SQLERRM);
1277 debug_exit(l_proc_name);
1278 END IF;
1279 p_message := SQLERRM;
1280 RETURN NULL;
1281 END pqp_get_concat_value;
1282
1283 -- If Multiple occurances is allowed in EIT then the below function
1284 -- is called which returns the Row Count
1285 FUNCTION pqp_get_extra_element_mult(
1286 p_flex_name IN VARCHAR2
1287 , -- Extra Element Info DDF
1288 p_segment_name IN VARCHAR2
1289 , -- segment name
1290 p_flex_context IN VARCHAR2
1291 , -- information_type
1292 p_element_type_id IN NUMBER
1293 ,p_message OUT NOCOPY VARCHAR2
1294 )
1295 RETURN NUMBER
1296 IS
1297 l_col_name VARCHAR2(30);
1298 l_tab_name VARCHAR2(30);
1299 l_rowcount NUMBER;
1300 l_col_value VARCHAR2(250);
1301 l_error_code NUMBER;
1302
1303 TYPE col_value_ref_csr_typ IS REF CURSOR;
1304
1305 c_value col_value_ref_csr_typ;
1306 l_proc_name VARCHAR2(61)
1307 := g_package_name || 'pqp_get_extra_element_mult';
1308 BEGIN
1309 g_debug := hr_utility.debug_enabled;
1310 IF g_debug THEN
1311 debug_enter(l_proc_name);
1312 END IF;
1313 l_col_name :=
1314 pqp_utilities.get_segment_name(
1315 p_flex_name => p_flex_name
1316 ,p_flex_field_title => p_segment_name
1317 ,p_flex_context => p_flex_context
1318 ,p_tab_nam => l_tab_name
1319 ,p_error_code => l_error_code
1320 ,p_message => p_message
1321 );
1322
1323 IF p_message IS NOT NULL
1324 THEN
1325 RETURN 0;
1326 END IF;
1327
1328 OPEN c_value FOR ' SELECT '
1329 || l_col_name
1330 || ' FROM
1331 pay_element_type_extra_info WHERE
1332 information_type ='
1333 || ''''
1334 || p_flex_context
1335 || ''''
1336 || ' AND element_type_id ='
1337 || p_element_type_id;
1338
1339 LOOP
1340 FETCH c_value INTO l_col_value;
1341 EXIT WHEN c_value%NOTFOUND;
1342 END LOOP;
1343
1344 l_rowcount := c_value%ROWCOUNT;
1345 CLOSE c_value;
1346 IF g_debug THEN
1347 debug(l_rowcount);
1348 END IF;
1349 IF g_debug THEN
1350 debug_exit(l_proc_name);
1351 END IF;
1352 RETURN l_rowcount;
1353 -- Added by tmehra for nocopy changes Feb'03
1354
1355 EXCEPTION
1356 WHEN OTHERS
1357 THEN
1358 IF g_debug THEN
1359 debug(l_proc_name||':Others Exception:');
1360 debug(SQLCODE);
1361 debug(SQLERRM);
1362 debug_exit(l_proc_name);
1363 END IF;
1364 p_message := SQLERRM;
1365 RETURN 0;
1366 END pqp_get_extra_element_mult;
1367
1368 -- pqp_get_extra_element_info Returns the value of the Element EIT
1369 -- ( Extra Element Info DDF).
1370 -- Retunrs -1 if any error,0 if successful and rowcount in case of
1371 -- multiple occurances flag true
1372 FUNCTION pqp_get_extra_element_info(
1373 p_element_type_id IN NUMBER
1374 ,p_information_type IN VARCHAR2
1375 ,p_segment_name IN VARCHAR2
1376 ,p_value OUT NOCOPY VARCHAR2
1377 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1378 ,p_error_msg OUT NOCOPY VARCHAR2
1379 )
1380 RETURN NUMBER
1381 IS
1382 l_mult_occ_flag VARCHAR2(30);
1383 l_rowcount NUMBER;
1384 l_error_code fnd_new_messages.message_number%TYPE;
1385
1386 l_element_type_extra_info_id
1387 pay_element_type_extra_info.element_type_extra_info_id%TYPE ;
1388
1389 CURSOR csr_get_extra_info_id
1390 (p_element_type_id NUMBER
1391 ,p_information_type VARCHAR2
1392 ) IS
1393 SELECT element_type_extra_info_id
1394 FROM pay_element_type_extra_info
1395 WHERE element_type_id = p_element_type_id
1396 AND information_type = p_information_type;
1397
1398 l_proc_name VARCHAR2(61)
1399 := g_package_name || 'pqp_get_extra_element_info';
1400 BEGIN
1401 g_debug := hr_utility.debug_enabled;
1402 IF g_debug THEN
1403 debug_enter(l_proc_name);
1404 END IF;
1405 OPEN csr_mult_occur(p_information_type => p_information_type);
1406 FETCH csr_mult_occur INTO l_mult_occ_flag;
1407
1408 --
1409 IF csr_mult_occur%NOTFOUND
1410 THEN
1411 IF g_debug THEN
1412 debug(l_proc_name,15);
1413 END IF;
1414 p_error_msg :=
1415 fnd_message.get_string('PQP', 'PQP_230602_INV_INFO_TYPE');
1416 CLOSE csr_mult_occur;
1417 RETURN -1;
1418 END IF;
1419
1420 --
1421 CLOSE csr_mult_occur;
1422
1423 IF g_debug THEN
1424 debug(l_proc_name,20);
1425 END IF;
1426
1427 --
1428 IF l_mult_occ_flag = 'N'
1429 THEN
1430
1431 -- Added for Multiple Contexts for Same element
1432 OPEN csr_get_extra_info_id
1433 (p_element_type_id => p_element_type_id,
1434 p_information_type => p_information_type);
1435 FETCH csr_get_extra_info_id INTO l_element_type_extra_info_id;
1436 CLOSE csr_get_extra_info_id;
1437
1438 p_value :=
1439 pqp_utilities.get_ddf_value(
1440 p_flex_name => 'Extra Element Info DDF'
1441 ,p_flex_context => p_information_type
1442 ,p_flex_field_title => p_segment_name
1443 ,p_key_col => 'ELEMENT_TYPE_EXTRA_INFO_ID'
1444 ,p_key_val => l_element_type_extra_info_id
1445 ,p_effective_date => NULL
1446 ,p_eff_date_req => 'N'
1447 ,p_business_group_id => NULL
1448 ,p_bus_group_id_req => 'N'
1449 ,p_error_code => l_error_code
1450 ,p_message => p_error_msg
1451 );
1452
1453 IF LENGTH(p_value) > 250
1454 THEN
1455 p_value := SUBSTR(p_value, 1, 250);
1456 p_truncated_yes_no := 'Y';
1457 ELSE
1458 p_truncated_yes_no := 'N';
1459 END IF;
1460
1461 IF g_debug THEN
1462 debug(p_error_msg);
1463 debug_exit(l_proc_name);
1464 END IF;
1465
1466 --
1467 --
1468 IF p_error_msg IS NOT NULL
1469 THEN
1470 RETURN -1;
1471 ELSE
1472 RETURN 0;
1473 END IF;
1474 --
1475 ELSE
1476 --
1477 l_rowcount :=
1478 pqp_utilities.pqp_get_extra_element_mult(
1479 p_flex_name => 'Extra Element Info DDF'
1480 ,p_segment_name => p_segment_name
1481 ,p_flex_context => p_information_type
1482 ,p_element_type_id => p_element_type_id
1483 ,p_message => p_error_msg
1484 );
1485 IF g_debug THEN
1486 debug_exit(l_proc_name);
1487 END IF;
1488 RETURN l_rowcount;
1489 --
1490 END IF;
1491 -- Added by tmehra for nocopy changes Feb'03
1492
1493 EXCEPTION
1494 WHEN OTHERS
1495 THEN
1496 IF g_debug THEN
1497 debug(l_proc_name||':Others Exception:');
1498 debug(SQLCODE);
1499 debug(SQLERRM);
1500 debug_exit(l_proc_name);
1501 END IF;
1502 p_error_msg := SQLERRM;
1503 p_value := NULL;
1504 p_truncated_yes_no := NULL;
1505 RETURN -1;
1506 --
1507 END pqp_get_extra_element_info;
1508
1509 -- pqp_get_extra_element_info_det Returns the value of segment
1510 -- passed as input to the Descriptive Flex Field Extra Element Info
1511 -- Details DF
1512 FUNCTION pqp_get_extra_element_info_det(
1513 p_element_type_id IN NUMBER
1514 ,p_information_type IN VARCHAR2
1515 ,p_segment_name IN VARCHAR2
1516 ,p_value OUT NOCOPY VARCHAR2
1517 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1518 ,p_error_msg OUT NOCOPY VARCHAR2
1519 )
1520 RETURN NUMBER
1521 IS
1522 l_mult_occ_flag VARCHAR2(1);
1523 l_rowcount NUMBER;
1524 l_proc_name VARCHAR2(61)
1525 := g_package_name || 'pqp_get_extra_element_info_det';
1526 l_error_code NUMBER;
1527 BEGIN
1528 g_debug := hr_utility.debug_enabled;
1529 IF g_debug THEN
1530 debug_enter(l_proc_name);
1531 END IF;
1532 --
1533 OPEN csr_mult_occur(p_information_type => p_information_type);
1534 IF g_debug THEN
1535 debug(l_proc_name, 10);
1536 END IF;
1537 FETCH csr_mult_occur INTO l_mult_occ_flag;
1538
1539 IF csr_mult_occur%NOTFOUND
1540 THEN
1541 p_error_msg :=
1542 fnd_message.get_string('PQP', 'PQP_230602_INV_INFO_TYPE');
1543 CLOSE csr_mult_occur;
1544 RETURN -1;
1545 END IF;
1546
1547 CLOSE csr_mult_occur;
1548
1549 --
1550
1551 IF l_mult_occ_flag = 'N'
1552 THEN --single occurance. treat as a simple DF
1553 --
1554 IF g_debug THEN
1555 debug(l_proc_name, 20);
1556 END IF;
1557 p_value :=
1558 pqp_utilities.get_df_value(
1559 p_flex_name => 'Extra Element Info Details DF'
1560 ,p_flex_context => 'PQP_LEG_CODE'
1561 , --p_information_type,
1562 p_flex_field_title => p_segment_name
1563 ,p_key_col => 'ELEMENT_TYPE_ID'
1564 ,p_key_val => p_element_type_id
1565 ,p_tab_name => 'PAY_ELEMENT_TYPE_EXTRA_INFO'
1566 ,p_effective_date => NULL
1567 ,p_eff_date_req => 'N'
1568 ,p_business_group_id => NULL
1569 ,p_bus_group_id_req => 'N'
1570 ,p_error_code => l_error_code
1571 ,p_message => p_error_msg
1572 );
1573
1574 IF LENGTH(p_value) > 250
1575 THEN
1576 p_value := SUBSTR(p_value, 1, 250);
1577 p_truncated_yes_no := 'Y';
1578 ELSE
1579 p_truncated_yes_no := 'N';
1580 END IF;
1581
1582 --
1583 IF g_debug THEN
1584 debug_exit(l_proc_name);
1585 END IF;
1586
1587 IF p_error_msg IS NOT NULL
1588 THEN
1589 IF g_debug THEN
1590 debug(p_error_msg);
1591 END IF;
1592 RETURN -1;
1593 ELSE
1594 RETURN 0;
1595 END IF;
1596 --
1597 --
1598 ELSE -- multiple occurances call the function to get Rowcount.
1599 --
1600 IF g_debug THEN
1601 debug(l_proc_name, 30);
1602 END IF;
1603 l_rowcount :=
1604 pqp_utilities.pqp_get_extra_element_mult(
1605 p_flex_name => 'Extra Element Info Details DF'
1606 ,p_segment_name => p_segment_name
1607 ,p_flex_context => p_information_type
1608 ,p_element_type_id => p_element_type_id
1609 ,p_message => p_error_msg
1610 );
1611
1612 --
1613
1614 IF p_error_msg IS NOT NULL
1615 THEN
1616 IF g_debug THEN
1617 debug(p_error_msg);
1618 END IF;
1619 IF g_debug THEN
1620 debug_exit(l_proc_name);
1621 END IF;
1622 RETURN -1;
1623 ELSE
1624 IF g_debug THEN
1625 debug(l_rowcount);
1626 END IF;
1627 IF g_debug THEN
1628 debug_exit(l_proc_name);
1629 END IF;
1630 RETURN l_rowcount;
1631 END IF;
1632 --
1633 --
1634 END IF;
1635 --
1636 -- Added by tmehra for nocopy changes Feb'03
1637
1638 EXCEPTION
1639 WHEN OTHERS
1640 THEN
1641 IF g_debug THEN
1642 debug(l_proc_name||':Others Exception:');
1643 debug(SQLCODE);
1644 debug(SQLERRM);
1645 debug_exit(l_proc_name);
1646 END IF;
1647 p_error_msg := SQLERRM;
1648 p_value := NULL;
1649 p_truncated_yes_no := NULL;
1650 RETURN -1;
1651 END pqp_get_extra_element_info_det;
1652
1653 -- Function to get Element type id for a given Element Name
1654 FUNCTION pqp_get_element_type_id(
1655 p_business_group_id IN NUMBER
1656 ,p_legislation_code IN VARCHAR2
1657 ,p_effective_date IN DATE
1658 ,p_element_type_name IN VARCHAR2
1659 ,p_error_code OUT NOCOPY NUMBER
1660 ,p_message OUT NOCOPY VARCHAR2
1661 )
1662 RETURN NUMBER
1663 IS
1664 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1665 l_proc_name VARCHAR2(61)
1666 := g_package_name || 'pqp_get_element_type_id';
1667 BEGIN
1668 g_debug := hr_utility.debug_enabled;
1669 IF g_debug THEN
1670 debug_enter(l_proc_name);
1671 END IF;
1672 p_error_code := 0;
1673 --
1674 OPEN csr_element_type(
1675 p_element_type_name => p_element_type_name
1676 ,p_effective_date => p_effective_date
1677 ,p_business_group_id => p_business_group_id
1678 ,p_legislation_code => p_legislation_code
1679 );
1680 FETCH csr_element_type INTO l_element_type_id;
1681
1682 IF csr_element_type%NOTFOUND
1683 THEN
1684 p_message := fnd_message.get_string('PQP', 'PQP_230601_INV_ELE_NAME');
1685 p_error_code := -1;
1686 END IF;
1687
1688 CLOSE csr_element_type;
1689 --
1690 IF g_debug THEN
1691 debug_exit(l_proc_name);
1692 END IF;
1693 RETURN l_element_type_id;
1694 EXCEPTION
1695 WHEN OTHERS
1696 THEN
1697 IF g_debug THEN
1698 debug(l_proc_name||':Others Exception:');
1699 debug(SQLCODE);
1700 debug(SQLERRM);
1701 debug_exit(l_proc_name);
1702 END IF;
1703 p_message := SQLERRM;
1704 p_error_code := -1;
1705 RETURN NULL;
1706 END pqp_get_element_type_id;
1707
1708 -- function to get segment value for a given element name. function is
1709 -- same as pqp_get_extra_element_info but takes input element name
1710 FUNCTION pqp_get_element_extra_info(
1711 p_business_group_id IN NUMBER
1712 ,p_effective_date IN DATE
1713 ,p_element_type_name IN VARCHAR2
1714 ,p_information_type IN VARCHAR2
1715 ,p_segment_name IN VARCHAR2
1716 ,p_value OUT NOCOPY VARCHAR2
1717 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1718 ,p_error_msg OUT NOCOPY VARCHAR2
1719 )
1720 RETURN NUMBER
1721 IS
1722 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1723 l_retval NUMBER;
1724 l_error_code NUMBER;
1725 l_proc_name VARCHAR2(61)
1726 := g_package_name || 'pqp_get_element_extra_info';
1727 l_legislation_code per_business_groups.legislation_code%TYPE;
1728 BEGIN
1729 g_debug := hr_utility.debug_enabled;
1730 IF g_debug THEN
1731 debug_enter(l_proc_name);
1732 END IF;
1733 IF g_debug THEN
1734 debug(l_proc_name, 10);
1735 END IF;
1736 l_legislation_code :=
1737 pqp_utilities.pqp_get_legislation_code(p_business_group_id => p_business_group_id);
1738 IF g_debug THEN
1739 debug('l_legislation_code:' || l_legislation_code);
1740 END IF;
1741 IF g_debug THEN
1742 debug('p_element_type_name:' || p_element_type_name);
1743 END IF;
1744 l_element_type_id :=
1745 pqp_utilities.pqp_get_element_type_id(
1746 p_business_group_id => p_business_group_id
1747 ,p_legislation_code => l_legislation_code
1748 ,p_effective_date => p_effective_date
1749 ,p_element_type_name => p_element_type_name
1750 ,p_error_code => l_error_code
1751 ,p_message => p_error_msg
1752 );
1753
1754 IF p_error_msg IS NOT NULL
1755 THEN
1756 RETURN -1;
1757 END IF;
1758
1759 IF g_debug THEN
1760 debug('l_element_type_id:' || l_element_type_id);
1761 END IF;
1762 l_retval :=
1763 pqp_get_extra_element_info(
1764 p_element_type_id => l_element_type_id
1765 ,p_information_type => p_information_type
1766 ,p_segment_name => p_segment_name
1767 ,p_value => p_value
1768 ,p_truncated_yes_no => p_truncated_yes_no
1769 ,p_error_msg => p_error_msg
1770 );
1771 IF g_debug THEN
1772 debug(l_retval);
1773 END IF;
1774 IF g_debug THEN
1775 debug_exit(l_proc_name);
1776 END IF;
1777 RETURN l_retval;
1778 -- Added by tmehra for nocopy changes Feb'03
1779
1780 EXCEPTION
1781 WHEN OTHERS
1782 THEN
1783 IF g_debug THEN
1784 debug(l_proc_name||':Others Exception:');
1785 debug(SQLCODE);
1786 debug(SQLERRM);
1787 debug_exit(l_proc_name);
1788 END IF;
1789 p_error_msg := SQLERRM;
1790 p_value := NULL;
1791 p_truncated_yes_no := NULL;
1792 RETURN -1;
1793 END pqp_get_element_extra_info;
1794
1795 -- function to get segment value for a given element name. function is
1796 -- same as pqp_get_extra_element_info_det but takes input element name
1797 FUNCTION pqp_get_element_extra_info_det(
1798 p_business_group_id IN NUMBER
1799 ,p_effective_date IN DATE
1800 ,p_element_type_name IN VARCHAR2
1801 ,p_information_type IN VARCHAR2
1802 ,p_segment_name IN VARCHAR2
1803 ,p_value OUT NOCOPY VARCHAR2
1804 ,p_truncated_yes_no OUT NOCOPY VARCHAR2
1805 ,p_error_msg OUT NOCOPY VARCHAR2
1806 )
1807 RETURN NUMBER
1808 IS
1809 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1810 l_retval NUMBER;
1811 l_proc_name VARCHAR2(61)
1812 := g_package_name || 'pqp_get_element_extra_info_det';
1813 l_legislation_code per_business_groups.legislation_code%TYPE;
1814 l_error_code NUMBER;
1815 BEGIN
1816 g_debug := hr_utility.debug_enabled;
1817 IF g_debug THEN
1818 debug_enter(l_proc_name);
1819 END IF;
1820 --
1821 IF g_debug THEN
1822 debug(l_proc_name, 10);
1823 END IF;
1824 l_legislation_code :=
1825 pqp_utilities.pqp_get_legislation_code(p_business_group_id => p_business_group_id);
1826 IF g_debug THEN
1827 debug(' Legislation Code :' || l_legislation_code, 20);
1828 END IF;
1829 --
1830 debug(' pqp_utilities.pqp_get_element_type_id ' || p_element_type_name
1831 ,30);
1832 l_element_type_id :=
1833 pqp_utilities.pqp_get_element_type_id(
1834 p_business_group_id => p_business_group_id
1835 ,p_legislation_code => l_legislation_code
1836 ,p_effective_date => p_effective_date
1837 ,p_element_type_name => p_element_type_name
1838 ,p_error_code => l_error_code
1839 ,p_message => p_error_msg
1840 );
1841 IF g_debug THEN
1842 debug(' The Element type Id is ' || l_element_type_id, 40);
1843 END IF;
1844 IF g_debug THEN
1845 debug(' Calling pqp_utilities.pqp_get_extra_element_info_det');
1846 END IF;
1847 l_retval :=
1848 pqp_get_extra_element_info_det(
1849 p_element_type_id => l_element_type_id
1850 ,p_information_type => p_information_type
1851 ,p_segment_name => p_segment_name
1852 ,p_value => p_value
1853 ,p_truncated_yes_no => p_truncated_yes_no
1854 ,p_error_msg => p_error_msg
1855 );
1856 IF g_debug THEN
1857 debug_exit(l_proc_name);
1858 END IF;
1859 RETURN l_retval;
1860 -- Added by tmehra for nocopy changes Feb'03
1861
1862 EXCEPTION
1863 WHEN OTHERS
1864 THEN
1865 IF g_debug THEN
1866 debug(l_proc_name||':Others Exception:');
1867 debug(SQLCODE);
1868 debug(SQLERRM);
1869 debug_exit(l_proc_name);
1870 END IF;
1871 p_error_msg := SQLERRM;
1872 p_value := NULL;
1873 p_truncated_yes_no := NULL;
1874 RETURN -1;
1875 END pqp_get_element_extra_info_det;
1876
1877 -- This function is to get the value of a column from User Defined Tables.
1878 -- Calls type1 function and handles the exceptions and returns.If any
1879 -- errors retunrs -1 and if success returns 0
1880
1881 FUNCTION pqp_gb_get_table_value(
1882 p_business_group_id IN NUMBER
1883 ,p_effective_date IN DATE
1884 ,p_table_name IN VARCHAR2
1885 ,p_column_name IN VARCHAR2
1886 ,p_row_name IN VARCHAR2
1887 ,p_value OUT NOCOPY VARCHAR2
1888 , --Value
1889 p_error_msg OUT NOCOPY VARCHAR2
1890 ,p_refresh_cache IN VARCHAR2 DEFAULT 'N'
1891 ) -- Error if any
1892 RETURN NUMBER
1893 IS
1894 l_proc_name VARCHAR2(61)
1895 := g_package_name || 'pqp_gb_get_table_value';
1896 l_col_in_cache BOOLEAN:= FALSE;
1897 l_table_in_cache BOOLEAN:= FALSE;
1898 l_err_msg VARCHAR2(100);
1899 i NUMBER ;
1900 j NUMBER ;
1901 BEGIN
1902 --
1903 IF g_debug THEN
1904 debug_enter(l_proc_name);
1905 debug('p_business_group_id',p_business_group_id );
1906 debug('p_effective_date'||p_effective_date );
1907 debug('p_table_name'||p_table_name );
1908 debug('p_column_name'||p_column_name );
1909 debug('p_row_name'||p_row_name );
1910 debug('p_refresh_cache'||p_refresh_cache );
1911
1912 END IF;
1913
1914 --
1915
1916 j := g_cached_tbls.FIRST ;
1917
1918 WHILE j IS NOT NULL LOOP
1919 IF g_cached_tbls(j).table_name = p_table_name
1920 AND g_cached_tbls(j).column_name = p_column_name
1921 THEN
1922 IF g_debug THEN
1923 debug(l_proc_name,5);
1924 END IF;
1925
1926 IF p_refresh_cache = 'Y' THEN
1927 debug(l_proc_name,15);
1928 g_cached_tbls.DELETE(j);
1929 l_col_in_cache := FALSE;
1930 delete_udt_value
1931 (p_table_name => p_table_name
1932 ,p_column_name => p_column_name
1933 ,p_error_msg => l_err_msg
1934 );
1935 ELSE
1936 l_col_in_cache := TRUE;
1937 END IF;
1938 EXIT;
1939 END IF;
1940 j := g_cached_tbls.NEXT(j);
1941 END LOOP;
1942
1943
1944 IF NOT l_col_in_cache
1945 THEN
1946 IF g_debug THEN
1947 debug(l_proc_name, 20);
1948 END IF;
1949 pqp_utilities.get_udt_data
1950 (p_business_group_id => p_business_group_id
1951 ,p_udt_name => p_table_name
1952 ,p_effective_date => p_effective_date
1953 ,p_column_name => p_column_name
1954 ,p_error_msg => p_error_msg
1955 );
1956
1957 i := g_cached_tbls.COUNT + 1;
1958 g_cached_tbls(i).table_name := p_table_name ;
1959 g_cached_tbls(i).column_name := p_column_name ;
1960 END IF;
1961
1962 --
1963 IF g_debug THEN
1964 debug(l_proc_name, 30);
1965 END IF;
1966
1967 p_value :=
1968 pqp_utilities.get_udt_value(
1969 p_table_name => p_table_name
1970 ,p_column_name => p_column_name
1971 ,p_row_name => p_row_name
1972 ,p_effective_date => p_effective_date
1973 ,p_business_group_id => p_business_group_id
1974 );
1975 --
1976 -- p_value := hruserdt.get_table_value
1977 -- (p_bus_group_id => p_business_group_id,
1978 -- p_table_name => p_table_name,
1979 -- p_col_name => p_column_name,
1980 -- p_row_value => p_row_name,
1981 -- p_effective_date => p_effective_date ) ;
1982 --
1983 IF g_debug THEN
1984 debug_exit(l_proc_name);
1985 END IF;
1986 RETURN 0;
1987 --
1988 EXCEPTION
1989 --
1990 WHEN OTHERS
1991 THEN
1992 p_error_msg := SQLERRM;
1993 IF g_debug THEN
1994 debug_exit(l_proc_name || ' ' || ' When Others ');
1995 END IF;
1996 -- Added by tmehra for nocopy changes
1997 p_value := NULL;
1998 RETURN -1;
1999 --
2000 END pqp_gb_get_table_value;
2001
2002 ---------------
2003
2004 PROCEDURE delete_udt_value(
2005 p_table_name IN VARCHAR2
2006 ,p_column_name IN VARCHAR2 DEFAULT 'ALL'
2007 ,p_error_msg OUT NOCOPY VARCHAR2
2008 )
2009 IS
2010 l_proc_name VARCHAR2(60)
2011 := g_package_name || 'delete_udt_data';
2012 i NUMBER;
2013 BEGIN
2014 g_debug := hr_utility.debug_enabled;
2015 IF g_debug THEN
2016 debug_enter(l_proc_name);
2017 debug('p_table_name' || p_table_name);
2018 END IF;
2019
2020 i := g_udt_rec.FIRST ;
2021
2022 WHILE i IS NOT NULL LOOP
2023
2024 IF ((g_udt_rec(i).table_name = p_table_name AND g_udt_rec(i).column_name = p_column_name ) OR
2025 (g_udt_rec(i).table_name = p_table_name AND p_column_name ='ALL'))
2026 THEN
2027 IF g_debug THEN
2028 debug('------------------------------------------------------');
2029 debug('|'||g_udt_rec(i).table_name||'|'||
2030 g_udt_rec(i).column_name ||'|'||g_udt_rec(i).row_name ||'|'||
2031 g_udt_rec(i).row_high_range||'|'||g_udt_rec(i).start_date ||'|'||
2032 g_udt_rec(i).end_date||'|'||g_udt_rec(i).matrix_value ||'|') ;
2033 END IF;
2034 g_udt_rec.DELETE(i);
2035 END IF ;
2036 i := g_udt_rec.NEXT(i);
2037 END LOOP ;
2038
2039
2040 IF g_debug THEN
2041 debug_exit(l_proc_name);
2042 END IF;
2043
2044 EXCEPTION
2045 WHEN OTHERS
2046 THEN
2047 IF g_debug THEN
2048 debug(l_proc_name||':Others Exception:');
2049 debug(SQLCODE);
2050 debug(SQLERRM);
2051 debug_exit(l_proc_name);
2052 END IF;
2053 p_error_msg := SQLERRM;
2054 RAISE;
2055 END delete_udt_value;
2056
2057 -- Function get_table_value_id gets the value of a column from
2058 -- User Defined Tables.First Table Name is fetched and passed to
2059 -- pqp_gb_get_table_value to get the Value.
2060 FUNCTION pqp_gb_get_table_value_id(
2061 p_business_group_id IN NUMBER
2062 ,p_effective_date IN DATE
2063 ,p_table_id IN NUMBER
2064 ,p_column_name IN VARCHAR2
2065 ,p_row_name IN VARCHAR2
2066 ,p_value OUT NOCOPY VARCHAR2
2067 ,p_error_msg OUT NOCOPY VARCHAR2
2068 )
2069 RETURN NUMBER
2070 IS
2071 l_proc_name VARCHAR2(61)
2072 := g_package_name || 'pqp_gb_get_table_value_id';
2073 l_table_name pay_user_tables.user_table_name%TYPE;
2074 l_retval NUMBER;
2075 BEGIN
2076 --
2077 IF g_debug THEN
2078 debug_enter(l_proc_name);
2079 END IF;
2080 --
2081 OPEN csr_table_id(p_table_id => p_table_id);
2082 FETCH csr_table_id INTO l_table_name;
2083 CLOSE csr_table_id;
2084 --
2085 --
2086 l_retval :=
2087 pqp_utilities.pqp_gb_get_table_value(
2088 p_business_group_id => p_business_group_id
2089 ,p_effective_date => p_effective_date
2090 ,p_table_name => l_table_name
2091 ,p_column_name => p_column_name
2092 ,p_row_name => p_row_name
2093 ,p_value => p_value
2094 ,p_error_msg => p_error_msg
2095 );
2096 IF g_debug THEN
2097 debug_exit(l_proc_name);
2098 END IF;
2099 RETURN l_retval;
2100 -- Added by tmehra for nocopy changes Feb'03
2101
2102 EXCEPTION
2103 WHEN OTHERS
2104 THEN
2105 IF g_debug THEN
2106 debug(l_proc_name||':Others Exception:');
2107 debug(SQLCODE);
2108 debug(SQLERRM);
2109 debug_exit(l_proc_name);
2110 END IF;
2111 p_error_msg := SQLERRM;
2112 p_value := NULL;
2113 RETURN NULL;
2114 END pqp_gb_get_table_value_id;
2115
2116 -- pqp_get_legislation_code Returns the legislation code for a
2117 -- Business Group Id.
2118 FUNCTION pqp_get_legislation_code(p_business_group_id IN NUMBER)
2119 RETURN VARCHAR2
2120 IS
2121 l_proc_name VARCHAR2(61)
2122 := g_package_name || 'pqp_get_legislation_code';
2123 l_legislation_code VARCHAR2(30);
2124 BEGIN
2125 g_debug := hr_utility.debug_enabled;
2126 IF g_debug THEN
2127 debug_enter(l_proc_name);
2128 END IF;
2129 --
2130 OPEN csr_leg_code(p_business_group_id => p_business_group_id);
2131 FETCH csr_leg_code INTO l_legislation_code;
2132 CLOSE csr_leg_code;
2133 --
2134 IF g_debug THEN
2135 debug_exit(l_proc_name);
2136 END IF;
2137 RETURN l_legislation_code;
2138 END pqp_get_legislation_code;
2139
2140 -- get_udt_data Caches the Values of UDT.
2141 PROCEDURE get_udt_data(
2142 p_business_group_id IN NUMBER
2143 ,p_effective_date IN DATE
2144 ,p_udt_name IN VARCHAR2
2145 ,p_column_name IN VARCHAR2 DEFAULT 'ALL'
2146 ,p_error_msg OUT NOCOPY VARCHAR2
2147 )
2148 IS
2149 CURSOR csr_get_table_id(p_udt_name IN VARCHAR2)
2150 IS
2151 SELECT tbls.user_table_id
2152 FROM pay_user_tables tbls
2153 WHERE tbls.user_table_name = p_udt_name
2154 AND (
2155 (business_group_id IS NULL AND legislation_code IS NULL)
2156 OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
2157 OR (
2158 business_group_id IS NOT NULL
2159 AND business_group_id = p_business_group_id
2160 )
2161 );
2162
2163 CURSOR csr_get_col_name(p_user_table_id IN NUMBER
2164 ,p_user_column_name IN VARCHAR2)
2165 IS
2166 SELECT user_column_id
2167 ,user_column_name
2168 FROM pay_user_columns
2169 WHERE user_table_id = p_user_table_id
2170 AND user_column_name like p_user_column_name
2171 AND (
2172 (business_group_id IS NULL AND legislation_code IS NULL)
2173 OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
2174 OR (
2175 business_group_id IS NOT NULL
2176 AND business_group_id = p_business_group_id
2177 )
2178 )
2179 ORDER BY user_column_id;
2180
2181 CURSOR csr_get_row_name(
2182 p_user_table_id IN NUMBER
2183 ,p_effective_date IN DATE
2184 )
2185 IS
2186 SELECT user_row_id
2187 ,row_low_range_or_name
2188 ,row_high_range
2189 FROM pay_user_rows_f
2190 WHERE user_table_id = p_user_table_id
2191 AND TRUNC(p_effective_date) BETWEEN effective_start_date
2192 AND effective_end_date
2193 AND (
2194 (business_group_id IS NULL AND legislation_code IS NULL)
2195 OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
2196 OR (
2197 business_group_id IS NOT NULL
2198 AND business_group_id = p_business_group_id
2199 )
2200 )
2201 ORDER BY display_sequence;
2202
2203 CURSOR csr_get_matrix_value(
2204 p_user_column_id IN NUMBER
2205 ,p_user_row_id IN NUMBER
2206 )
2207 IS
2208 SELECT VALUE
2209 ,effective_start_date
2210 ,effective_end_date
2211 FROM pay_user_column_instances_f
2212 WHERE user_column_id = p_user_column_id
2213 AND user_row_id = p_user_row_id
2214 AND (
2215 (business_group_id IS NULL AND legislation_code IS NULL)
2216 OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
2217 OR (
2218 business_group_id IS NOT NULL
2219 AND business_group_id = p_business_group_id
2220 )
2221 );
2222
2223 l_user_column_name pay_user_columns.user_column_name%TYPE;
2224 l_user_row_name pay_user_rows_f.row_low_range_or_name%TYPE;
2225 l_matrix_value pay_user_column_instances_f.VALUE%TYPE;
2226 l_user_table_id pay_user_tables.user_table_id%TYPE;
2227 l_user_column_id pay_user_columns.user_column_id%TYPE;
2228 l_user_row_id pay_user_rows_f.user_row_id%TYPE;
2229 l_idx NUMBER;
2230 l_proc_name VARCHAR2(60)
2231 := g_package_name || 'get_udt_data';
2232 l_row_high_range pay_user_rows_f.row_high_range%TYPE;
2233 l_column_name VARCHAR2(240);
2234 BEGIN
2235 g_debug := hr_utility.debug_enabled;
2236 IF g_debug THEN
2237 debug_enter(l_proc_name);
2238 debug('p_column_name '||p_column_name);
2239 END IF;
2240 OPEN csr_get_table_id(p_udt_name => p_udt_name);
2241 FETCH csr_get_table_id INTO l_user_table_id;
2242 CLOSE csr_get_table_id;
2243
2244 IF g_udt_rec.EXISTS(1)
2245 THEN
2246 IF g_debug THEN
2247 debug(l_proc_name, 10);
2248 END IF;
2249 l_idx := g_udt_rec.LAST + 1;
2250 ELSE
2251 IF g_debug THEN
2252 debug(l_proc_name, 30);
2253 END IF;
2254 l_idx := 1;
2255 END IF;
2256
2257 IF p_column_name = 'ALL' THEN
2258 l_column_name :='%';
2259 ELSE
2260 l_column_name :=p_column_name;
2261 END IF;
2262
2263 FOR i IN csr_get_col_name(p_user_table_id => l_user_table_id
2264 ,p_user_column_name => l_column_name )
2265 LOOP
2266 l_user_column_id := i.user_column_id;
2267 l_user_column_name := i.user_column_name;
2268
2269 FOR j IN csr_get_row_name(
2270 p_user_table_id => l_user_table_id
2271 ,p_effective_date => p_effective_date
2272 )
2273 LOOP
2274 l_user_row_id := j.user_row_id;
2275 l_user_row_name := j.row_low_range_or_name;
2276 l_row_high_range := j.row_high_range;
2277
2278 FOR k IN csr_get_matrix_value(
2279 p_user_column_id => l_user_column_id
2280 ,p_user_row_id => l_user_row_id
2281 )
2282 LOOP
2283 g_udt_rec(l_idx).table_name := p_udt_name; -- comment
2284 g_udt_rec(l_idx).column_name := l_user_column_name;
2285 g_udt_rec(l_idx).row_name := l_user_row_name;
2286 g_udt_rec(l_idx).row_high_range := l_row_high_range;
2287 g_udt_rec(l_idx).matrix_value := k.VALUE;
2288 g_udt_rec(l_idx).start_date := TRUNC(k.effective_start_date);
2289 g_udt_rec(l_idx).end_date := TRUNC(k.effective_end_date);
2290 IF g_debug THEN
2291 debug('------------------------------------------------------');
2292 debug('|'||g_udt_rec(l_idx).table_name||'|'||
2293 g_udt_rec(l_idx).column_name ||'|'||g_udt_rec(l_idx).row_name ||'|'||
2294 g_udt_rec(l_idx).row_high_range||'|'||g_udt_rec(l_idx).start_date ||'|'||
2295 g_udt_rec(l_idx).end_date||'|'||g_udt_rec(l_idx).matrix_value ||'|') ;
2296 END IF;
2297 l_idx := l_idx + 1;
2298 l_matrix_value := NULL;
2299 END LOOP;
2300
2301 l_user_row_name := NULL;
2302 END LOOP;
2303 END LOOP;
2304
2305 IF g_debug THEN
2306 debug_exit(l_proc_name);
2307 END IF;
2308 -- Added by tmehra for nocopy changes Feb'03
2309
2310 EXCEPTION
2311 WHEN OTHERS
2312 THEN
2313 IF g_debug THEN
2314 debug(l_proc_name||':Others Exception:');
2315 debug(SQLCODE);
2316 debug(SQLERRM);
2317 debug_exit(l_proc_name);
2318 END IF;
2319 p_error_msg := SQLERRM;
2320 RAISE;
2321 END get_udt_data;
2322
2323 -- get_udt_value gets the value from the cache ( the values are cached in
2324 -- procedure get_udt_data) depending upon whether its Match or Range.
2325 FUNCTION get_udt_value(
2326 p_table_name IN VARCHAR2
2327 ,p_column_name IN VARCHAR2
2328 ,p_row_name IN VARCHAR2
2329 ,p_effective_date IN DATE
2330 ,p_business_group_id IN NUMBER
2331 )
2332 RETURN VARCHAR2
2333 IS
2334 CURSOR csr_get_range_match(p_table_name IN VARCHAR2)
2335 IS
2336 SELECT udt.range_or_match
2337 FROM pay_user_tables udt
2338 WHERE user_table_name = p_table_name;
2339
2340 l_range_match csr_get_range_match%ROWTYPE;
2341 l_return_value pay_user_column_instances_f.VALUE%TYPE;
2342 l_proc_name VARCHAR2(70)
2343 := g_package_name || 'get_udt_value';
2344 i NUMBER;
2345 BEGIN
2346 g_debug := hr_utility.debug_enabled;
2347 IF g_debug THEN
2348 debug_enter(l_proc_name);
2349 END IF;
2350 OPEN csr_get_range_match(p_table_name => p_table_name);
2351 FETCH csr_get_range_match INTO l_range_match;
2352 CLOSE csr_get_range_match;
2353
2354 -- Check the value in the cached PL/SQL record table for the given
2355 -- effective date
2356
2357 i := g_udt_rec.FIRST ;
2358
2359 WHILE i IS NOT NULL LOOP
2360 IF l_range_match.range_or_match = 'R'
2361 AND g_udt_rec(i).table_name = p_table_name
2362 AND g_udt_rec(i).column_name = p_column_name
2363 AND (
2364 fnd_number.canonical_to_number(p_row_name)
2365 BETWEEN fnd_number.canonical_to_number(g_udt_rec(i).row_name)
2366 AND fnd_number.canonical_to_number(g_udt_rec(i).row_high_range)
2367 )
2368 AND (
2369 p_effective_date BETWEEN g_udt_rec(i).start_date
2370 AND g_udt_rec(i).end_date
2371 )
2372 THEN
2373 l_return_value := g_udt_rec(i).matrix_value;
2374 EXIT;
2375 ELSE
2376 IF l_range_match.range_or_match = 'M'
2377 AND g_udt_rec(i).table_name = p_table_name
2378 AND g_udt_rec(i).column_name = p_column_name
2379 AND (
2380 p_effective_date BETWEEN g_udt_rec(i).start_date
2381 AND g_udt_rec(i).end_date
2382 )
2383 AND p_row_name = g_udt_rec(i).row_name
2384 THEN
2385 l_return_value := g_udt_rec(i).matrix_value;
2386 EXIT;
2387 END IF;
2388 END IF;
2389 i := g_udt_rec.NEXT(i);
2390 END LOOP;
2391
2392 IF g_debug THEN
2393 debug_exit(l_proc_name);
2394 END IF;
2395 RETURN l_return_value;
2396 END get_udt_value;
2397
2398
2399 -- Function to Set Trace on. This is a wrapper around hr_utility.trace_on
2400 -- Procedure.
2401 FUNCTION set_trace_on(
2402 p_trace_destination IN VARCHAR2
2403 ,p_trace_coverage IN VARCHAR2
2404 ,p_error_message OUT NOCOPY VARCHAR2
2405 )
2406 RETURN NUMBER
2407 IS
2408 BEGIN
2409 hr_utility.trace_on(
2410 trace_mode => p_trace_coverage
2411 ,session_identifier => p_trace_destination
2412 );
2413 RETURN 0;
2414 EXCEPTION
2415 WHEN OTHERS
2416 THEN
2417 p_error_message := SQLERRM;
2418 RETURN -1;
2419 END set_trace_on;
2420
2421 -- Function to Start Trace and Pipe Name will be Concurrent Request Id.
2422 FUNCTION set_request_trace_on(p_error_message OUT NOCOPY VARCHAR2)
2423 RETURN NUMBER
2424 IS
2425 BEGIN
2426 hr_utility.trace_on(trace_mode => 'F', session_identifier => 'REQID');
2427 RETURN 0;
2428 EXCEPTION
2429 WHEN OTHERS
2430 THEN
2431 p_error_message := SQLERRM;
2432 RETURN -1;
2433 END set_request_trace_on;
2434
2435 -- Function to Set Trace off. A Wrapper for hr_utility.trace_off.
2436 FUNCTION set_trace_off(p_error_message OUT NOCOPY VARCHAR2)
2437 RETURN NUMBER
2438 IS
2439 l_trace_status NUMBER := 0;
2440 BEGIN
2441 hr_utility.trace_off;
2442 RETURN l_trace_status;
2443 EXCEPTION
2444 WHEN OTHERS
2445 THEN
2446 p_error_message := SQLERRM;
2447 RETURN -1;
2448 END set_trace_off;
2449
2450 -- function returns the values of the look up code which is the column name
2451 -- related to the prompt defined in lookup
2452 FUNCTION get_lookup_code(
2453 p_lookup_type IN VARCHAR2
2454 ,p_lookup_meaning IN VARCHAR2
2455 ,p_message OUT NOCOPY VARCHAR2
2456 )
2457 RETURN VARCHAR2
2458 IS
2459 l_lookup_code fnd_lookup_values_vl.lookup_code%TYPE;
2460 l_proc_name VARCHAR2(61)
2461 := g_package_name || 'get_lookup_code';
2462 BEGIN
2463 g_debug := hr_utility.debug_enabled;
2464 IF g_debug THEN
2465 debug_enter(l_proc_name);
2466 END IF;
2467 OPEN csr_lookup_code(
2468 p_lookup_type => p_lookup_type
2469 ,p_lookup_meaning => p_lookup_meaning
2470 );
2471 FETCH csr_lookup_code INTO l_lookup_code;
2472
2473 --
2474 IF csr_lookup_code%NOTFOUND
2475 THEN
2476 fnd_message.set_name('PQP', 'PQP_230598_INV_TITLE');
2477 fnd_message.set_token('LKUP_MEANING', p_lookup_meaning);
2478 p_message := fnd_message.get();
2479 END IF;
2480
2481 --
2482 CLOSE csr_lookup_code;
2483 IF g_debug THEN
2484 debug_exit(l_proc_name);
2485 END IF;
2486 RETURN l_lookup_code;
2487 -- Added by tmehra for nocopy changes Feb'03
2488
2489 EXCEPTION
2490 WHEN OTHERS
2491 THEN
2492 IF g_debug THEN
2493 debug(l_proc_name||':Others Exception:');
2494 debug(SQLCODE);
2495 debug(SQLERRM);
2496 debug_exit(l_proc_name);
2497 END IF;
2498 p_message := SQLERRM;
2499 RETURN NULL;
2500 END get_lookup_code;
2501
2502 --delete_formula
2503 -- function to delete a given formula completly
2504 -- It will delete the compiled information
2505 -- and drop all the assocciated packages also.
2506 -- This function has been made by merging the code
2507 -- from the template del_formulas and drop_formula_packages
2508 -- procedures.
2509 PROCEDURE delete_formula(
2510 p_formula_id IN NUMBER
2511 ,p_drop_compiled_info IN BOOLEAN
2512 ,p_error_code OUT NOCOPY NUMBER
2513 ,p_error_message OUT NOCOPY VARCHAR2
2514 )
2515 IS
2516 CURSOR csr_package_names
2517 IS
2518 SELECT object_name
2519 FROM user_objects
2520 WHERE object_name LIKE 'FFP' || TO_CHAR(p_formula_id) || '_%'
2521 AND object_type = 'PACKAGE';
2522
2523 l_package_name user_objects.object_name%TYPE;
2524 l_proc_name VARCHAR2(61)
2525 := g_package_name || 'delete_formula';
2526 l_formula_rowid ROWID;
2527 l_return_code NUMBER := 0;
2528 BEGIN
2529 g_debug := hr_utility.debug_enabled;
2530 IF g_debug THEN
2531 debug_enter(l_proc_name);
2532 END IF;
2533 p_error_code := 0;
2534 p_error_message := NULL;
2535
2536 SELECT ROWID
2537 INTO l_formula_rowid
2538 FROM ff_formulas_f
2539 WHERE formula_id = p_formula_id;
2540
2541 IF g_debug THEN
2542 debug(l_proc_name, 20);
2543 END IF;
2544
2545 --
2546 -- Delete the compiled information and usages table rows before the
2547 -- formula row.
2548 --
2549 DELETE FROM ff_compiled_info_f
2550 WHERE formula_id = p_formula_id;
2551
2552 IF g_debug THEN
2553 debug(l_proc_name, 30);
2554 END IF;
2555
2556 --
2557 DELETE FROM ff_fdi_usages_f
2558 WHERE formula_id = p_formula_id;
2559
2560 IF g_debug THEN
2561 debug(l_proc_name, 40);
2562 END IF;
2563 --
2564 ff_formulas_f_pkg.delete_row(
2565 x_rowid => l_formula_rowid
2566 ,x_formula_id => p_formula_id
2567 ,x_dt_delete_mode => 'ZAP'
2568 ,x_validation_start_date => hr_api.g_sot
2569 ,x_validation_end_date => hr_api.g_eot
2570 );
2571 IF g_debug THEN
2572 debug(l_proc_name, 50);
2573 END IF;
2574
2575 --
2576 IF p_drop_compiled_info
2577 THEN
2578 IF g_debug THEN
2579 debug(l_proc_name, 60);
2580 END IF;
2581 OPEN csr_package_names;
2582
2583 LOOP
2584 IF g_debug THEN
2585 debug(l_proc_name, 65);
2586 END IF;
2587 FETCH csr_package_names INTO l_package_name;
2588 EXIT WHEN csr_package_names%NOTFOUND;
2589 --
2590 -- Drop the package.
2591 --
2592 IF g_debug THEN
2593 debug(l_proc_name, 70);
2594 END IF;
2595 EXECUTE IMMEDIATE 'DROP PACKAGE ' || l_package_name;
2596 IF g_debug THEN
2597 debug(l_proc_name, 75);
2598 END IF;
2599 END LOOP;
2600
2601 CLOSE csr_package_names;
2602 IF g_debug THEN
2603 debug(l_proc_name, 80);
2604 END IF;
2605 END IF;
2606
2607 IF g_debug THEN
2608 debug(l_proc_name, 90);
2609 END IF;
2610 IF g_debug THEN
2611 debug_exit(l_proc_name);
2612 END IF;
2613 EXCEPTION
2614 WHEN OTHERS
2615 THEN
2616 IF g_debug THEN
2617 debug(SQLCODE);
2618 END IF;
2619 IF g_debug THEN
2620 debug(SQLERRM);
2621 END IF;
2622 IF g_debug THEN
2623 debug(l_proc_name, -10);
2624 END IF;
2625 IF g_debug THEN
2626 debug_exit(l_proc_name);
2627 END IF;
2628 p_error_code := SQLCODE;
2629 p_error_message := SQLERRM;
2630 RAISE;
2631 END delete_formula;
2632
2633 ------------------get_event_group_id--------------------
2634 FUNCTION get_event_group_id(
2635 p_business_group_id IN NUMBER
2636 ,p_event_group_name IN VARCHAR2
2637 )
2638 RETURN NUMBER
2639 IS
2640 l_event_group_details csr_event_group_details%ROWTYPE;
2641 l_proc_name VARCHAR2(70)
2642 := g_package_name || 'get_event_group_id';
2643 BEGIN -- get_event_group_id
2644 IF g_debug THEN
2645 debug_enter(l_proc_name);
2646 END IF;
2647 OPEN csr_event_group_details(p_event_group_name, p_business_group_id);
2648 FETCH csr_event_group_details INTO l_event_group_details;
2649 CLOSE csr_event_group_details;
2650 IF g_debug THEN
2651 debug_exit(l_proc_name);
2652 END IF;
2653 RETURN l_event_group_details.event_group_id;
2654 --
2655 END;
2656 -- get_event_group_id
2657 --
2658
2659 ------------------get_events--------------------
2660 -- This overloaded get_events procedure has only 1 OUT parameters
2661
2662 FUNCTION get_events(
2663 p_assignment_id IN NUMBER
2664 ,p_element_entry_id IN NUMBER DEFAULT NULL
2665 ,p_assignment_action_id IN NUMBER DEFAULT NULL
2666 ,p_business_group_id IN NUMBER
2667 ,p_process_mode IN VARCHAR2
2668 DEFAULT 'ENTRY_EFFECTIVE_DATE'
2669 ,p_event_group_name IN VARCHAR2
2670 ,p_start_date IN DATE
2671 ,p_end_date IN DATE
2672 ,t_event_details OUT NOCOPY pqp_utilities.t_event_details_table_type
2673 )
2674 RETURN NUMBER
2675 IS
2676 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2677 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2678 l_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
2679 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2680 l_event_details pqp_utilities.t_event_details_table_type;
2681 l_event_group_id pay_event_groups.event_group_id%TYPE;
2682 l_dt_event_found VARCHAR2(20) := 'N'; -- Default NOTFOUND
2683 l_itr NUMBER;
2684 l_proc_name VARCHAR2(61)
2685 := g_package_name || 'get_events';
2686 BEGIN
2687 g_debug := hr_utility.debug_enabled;
2688 IF g_debug THEN
2689 debug_enter(l_proc_name);
2690 END IF;
2691 l_event_group_id :=
2692 pqp_utilities.get_event_group_id(
2693 p_event_group_name => p_event_group_name
2694 ,p_business_group_id => p_business_group_id
2695 );
2696 IF g_debug THEN
2697 debug('Event Group Id :' || TO_CHAR(NVL(l_event_group_id, -999)));
2698 END IF;
2699 IF g_debug THEN
2700 debug(l_proc_name, 10);
2701 END IF;
2702 pay_interpreter_pkg.entry_affected(
2703 p_element_entry_id => p_element_entry_id
2704 ,p_assignment_action_id => p_assignment_action_id
2705 ,p_assignment_id => p_assignment_id
2706 ,p_mode => NULL
2707 ,p_process => NULL
2708 ,p_event_group_id => l_event_group_id
2709 ,p_process_mode => p_process_mode
2710 ,p_start_date => p_start_date
2711 ,p_end_date => p_end_date
2712 -- Passing the BG ID explicitly. else pay_interpreter_pkg uses the cached BGID
2713 ,p_business_group_id => p_business_group_id
2714 ,t_detailed_output => l_detail_tab -- OUT
2715 ,t_proration_dates => l_proration_dates -- OUT
2716 ,t_proration_change_type => l_proration_changes -- OUT
2717 ,t_proration_type => l_pro_type_tab -- OUT
2718 );
2719 IF g_debug THEN
2720 debug(l_proc_name, 20);
2721 END IF;
2722
2723 IF (
2724 l_proration_dates.COUNT > 0 AND -- making sure we have an entry in both the plsql talbes
2725 l_proration_changes.COUNT > 0
2726 )
2727 THEN
2728 debug(
2729 'Found '
2730 || TO_CHAR(l_proration_dates.COUNT)
2731 || ' event(s) for Event Group :'
2732 || p_event_group_name
2733 ,30
2734 );
2735 -- This loop will merge event dates and event types
2736 -- into a single plsql table
2737 l_itr := l_proration_dates.FIRST;
2738
2739 WHILE l_itr <= l_proration_dates.LAST
2740 LOOP -- through change proration dates
2741 debug(
2742 l_itr
2743 || '> Date :'
2744 || TO_CHAR(l_proration_dates(l_itr), 'DD/MM/YYYY')
2745 || ' Change :'
2746 || l_proration_changes(l_itr)
2747 ,40
2748 );
2749 l_event_details(l_itr).event_date := l_proration_dates(l_itr);
2750 l_event_details(l_itr).update_type := l_proration_changes(l_itr);
2751 l_itr := l_proration_dates.NEXT(l_itr);
2752 END LOOP; -- through change proration dates
2753 --
2754
2755 IF g_debug THEN
2756 debug(l_proc_name, 50);
2757 END IF;
2758 --
2759 END IF; -- (l_proration_dates.COUNT > 0
2760
2761 t_event_details.DELETE;
2762 t_event_details := l_event_details;
2763 IF g_debug THEN
2764 debug_exit(l_proc_name);
2765 END IF;
2766 RETURN t_event_details.COUNT;
2767 EXCEPTION
2768 WHEN OTHERS
2769 THEN
2770 IF g_debug THEN
2771 debug(' Others in ');
2772 debug_exit(l_proc_name);
2773 END IF;
2774 t_event_details.DELETE;
2775 RAISE;
2776 END get_events;
2777
2778
2779 ------------------get_events--------------------
2780 -- This overloaded get_events procedure has 2 OUT parameters
2781
2782 FUNCTION get_events(
2783 p_assignment_id IN NUMBER
2784 ,p_element_entry_id IN NUMBER DEFAULT NULL
2785 ,p_assignment_action_id IN NUMBER DEFAULT NULL
2786 ,p_business_group_id IN NUMBER
2787 ,p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE'
2788 ,p_event_group_name IN VARCHAR2
2789 ,p_start_date IN DATE
2790 ,p_end_date IN DATE
2791 ,t_proration_dates OUT NOCOPY pay_interpreter_pkg.t_proration_dates_table_type
2792 ,t_proration_change_type OUT NOCOPY pay_interpreter_pkg.t_proration_type_table_type
2793 )
2794 RETURN NUMBER
2795 IS
2796 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2797 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2798 l_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
2799 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2800 l_event_details pqp_utilities.t_event_details_table_type;
2801 l_event_group_id pay_event_groups.event_group_id%TYPE;
2802 l_dt_event_found VARCHAR2(20) := 'N'; -- Default NOTFOUND
2803 l_itr NUMBER;
2804 l_proc_name VARCHAR2(61)
2805 := g_package_name || 'get_events';
2806 BEGIN
2807 g_debug := hr_utility.debug_enabled;
2808 IF g_debug THEN
2809 debug_enter(l_proc_name);
2810 END IF;
2811 l_event_group_id :=
2812 pqp_utilities.get_event_group_id(
2813 p_event_group_name => p_event_group_name
2814 ,p_business_group_id => p_business_group_id
2815 );
2816 IF g_debug THEN
2817 debug('Event Group Id :' || TO_CHAR(NVL(l_event_group_id, -999)));
2818 END IF;
2819 IF g_debug THEN
2820 debug(l_proc_name, 10);
2821 END IF;
2822 pay_interpreter_pkg.entry_affected(
2823 p_element_entry_id => p_element_entry_id
2824 ,p_assignment_action_id => p_assignment_action_id
2825 ,p_assignment_id => p_assignment_id
2826 ,p_mode => NULL
2827 ,p_process => NULL
2828 ,p_event_group_id => l_event_group_id
2829 ,p_process_mode => p_process_mode
2830 ,p_start_date => p_start_date
2831 ,p_end_date => p_end_date
2832 -- Passing the BG ID explicitly. else pay_interpreter_pkg uses the cached BGID
2833 ,p_business_group_id => p_business_group_id
2834 ,t_detailed_output => l_detail_tab -- OUT
2835 ,t_proration_dates => l_proration_dates -- OUT
2836 ,t_proration_change_type => l_proration_changes -- OUT
2837 ,t_proration_type => l_pro_type_tab -- OUT
2838 );
2839 IF g_debug THEN
2840 debug(l_proc_name, 20);
2841 END IF;
2842
2843 t_proration_dates.DELETE;
2844 t_proration_change_type.DELETE;
2845
2846 t_proration_dates := l_proration_dates;
2847 t_proration_change_type := l_proration_changes;
2848
2849 IF g_debug THEN
2850 debug_exit(l_proc_name);
2851 END IF;
2852 RETURN t_proration_dates.COUNT;
2853 EXCEPTION
2854 WHEN OTHERS
2855 THEN
2856 IF g_debug THEN
2857 debug(' Others in ');
2858 debug_exit(l_proc_name);
2859 END IF;
2860 t_proration_dates.DELETE;
2861 t_proration_change_type.DELETE;
2862 RAISE;
2863 END get_events;
2864
2865 --
2866 --
2867 --
2868 PROCEDURE check_error_code(
2869 p_error_code IN fnd_new_messages.message_number%TYPE
2870 ,p_error_message IN fnd_new_messages.MESSAGE_TEXT%TYPE
2871 DEFAULT NULL
2872 )
2873 IS
2874 l_proc_name VARCHAR2(61):=
2875 g_package_name || 'check_error_code';
2876
2877 BEGIN
2878 g_debug := hr_utility.debug_enabled;
2879 IF g_debug THEN
2880 debug_enter(l_proc_name);
2881 debug('p_error_code:' ||fnd_number.number_to_canonical(p_error_code));
2882 debug('p_error_message:'||p_error_message);
2883 END IF;
2884
2885 IF p_error_code < 0
2886 THEN
2887
2888 --IF p_error_code <> hr_utility.hr_error_number
2889 --THEN
2890 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
2891 fnd_message.set_token('TOKEN', p_error_message);
2892 fnd_message.raise_error;
2893 --ELSE
2894 -- RAISE hr_application_error;
2895 -- error is an application error , ie 20001 hence continue
2896 -- defined in header.
2897 --END IF;
2898
2899 END IF;
2900
2901 IF g_debug THEN
2902 debug_exit(l_proc_name);
2903 END IF;
2904
2905 END check_error_code;
2906
2907 --
2908 --
2909 --
2910 FUNCTION pqp_fnd_message_set_token(
2911 token IN VARCHAR2
2912 ,value IN VARCHAR2
2913 ,translate IN VARCHAR2 DEFAULT 'N'
2914 ) RETURN NUMBER IS
2915
2916 l_translate BOOLEAN := FALSE;
2917
2918 BEGIN -- pqp_fnd_message_set_token
2919
2920 IF translate = 'Y' THEN
2921 l_translate := TRUE;
2922 END IF;
2923
2924 fnd_message.set_token
2925 (token => token
2926 ,value => value
2927 ,translate => l_translate
2928 );
2929
2930 RETURN 0;
2931 --
2932 END; -- pqp_fnd_message_set_token
2933
2934 --
2935 --
2936 --
2937 FUNCTION pqp_fnd_message_set_name(
2938 application IN VARCHAR2
2939 ,name IN VARCHAR2
2940 ) RETURN NUMBER IS
2941 BEGIN -- pqp_fnd_message_set_name
2942
2943 fnd_message.set_name
2944 (application => application
2945 ,name => name
2946 );
2947
2948 RETURN 0;
2949 --
2950 END; -- pqp_fnd_message_set_name
2951 --
2952 --
2953
2954
2955
2956 -- get_round_value takes a number to be rounded,a factor to be rounded to
2957 -- type of rouding (UP,DOWN,NEAREST,ROUNDTO,NOROUND) as parameters .It returns the number rounded
2958 -- of to the upper or lower rounding factor.
2959 -- if the value for p_rounding_type is not UPPER then it is treated
2960 -- as LOWER
2961 -- Eg. 4.7,0.15,UP would be rounded to 4.75
2962 -- Eg. 4.7,0.15,DOWN would be rounded to 4.60
2963 -- Eg. 4.9,0.5,DOWN would be rounded to 4.5
2964 -- Eg. 4.5000000000000002,0.5,UP would be rounded to 5
2965 -- Eg. 4.91,.15,UP would be rounded to 5
2966 -- Eg. 4.91,.11,UP would be rounded to 4.99
2967 -- Eg. 4.91,.11,DOWN would be rounded to 4.88
2968 -- Eg. 4.91,.11,NEAREST would be rounded to 4.88
2969 -- Eg. 4.91,2,ROUNDTO would be rounded to 4.91
2970 -- Eg. 4.9156,2,ROUNDTO would be rounded to 4.92
2971 -- To Round it to a whole number pass the base value as 1.
2972
2973 FUNCTION round_value_up_down(
2974 p_value_to_round IN NUMBER
2975 ,p_base_value IN NUMBER
2976 ,p_rounding_type IN VARCHAR2
2977 )
2978 RETURN NUMBER
2979 IS
2980 l_retval NUMBER;
2981 l_lower NUMBER;
2982 l_upper NUMBER;
2983 l_decimal_part NUMBER;
2984 l_proc_name VARCHAR2(61) := g_package_name || 'round_value_up_down';
2985
2986 BEGIN
2987
2988 g_debug := hr_utility.debug_enabled;
2989
2990 IF g_debug THEN
2991 debug_enter(l_proc_name);
2992 debug('p_value_to_round:'||p_value_to_round);
2993 debug('p_base_value:'||p_base_value);
2994 debug('p_rounding_type:'||p_rounding_type);
2995 END IF;
2996
2997 l_retval := p_value_to_round;
2998 ---if rounding factor is 0 or rounding type is NOROUND then
2999 ---return the number as it is
3000 IF p_base_value <> 0 AND p_rounding_type <> 'NOROUND' THEN
3001 --- Get the decimal part of the number
3002 l_decimal_part := l_retval - FLOOR(l_retval);
3003 l_lower:=FLOOR(l_decimal_part/p_base_value);
3004 l_upper:=l_lower+1;
3005 l_lower:=p_base_value*l_lower;
3006 l_upper:=p_base_value*l_upper;
3007
3008 IF g_debug THEN
3009 debug('Decimal Part '||l_decimal_part );
3010 debug('Lower Rounding Factor '||l_lower);
3011 debug('Upper Rounding Factor '||l_upper);
3012 END IF;
3013
3014 --Condition when the input number is already rounded or is an integer
3015 IF NOT ((FLOOR(l_decimal_part/p_base_value)=
3016 (l_decimal_part/p_base_value))
3017 OR (l_decimal_part=0)) THEN
3018
3019 IF p_rounding_type = 'UP' THEN --Round UP
3020 l_retval := FLOOR(l_retval) + l_upper;
3021 ELSIF p_rounding_type='DOWN' THEN --Round Down
3022 l_retval := FLOOR(l_retval) + l_lower;
3023 ELSIF p_rounding_type='NEAREST' THEN --To Nearest
3024
3025 IF (p_value_to_round-(FLOOR(l_retval) + l_lower)) >=
3026 ((FLOOR(l_retval) + l_upper)-p_value_to_round) THEN
3027 l_retval := FLOOR(l_retval) + l_upper;
3028 ELSE
3029 l_retval := FLOOR(l_retval) + l_lower;
3030 END IF;
3031
3032 ELSIF p_rounding_type='ROUNDTO' THEN --Simple Round
3033 l_retval := ROUND(l_retval,p_base_value);
3034 END IF ;
3035
3036 END IF;--Not already rounded
3037
3038 -- Condition when rounding off exceeds
3039 -- the nearest integer value
3040 IF l_retval > (FLOOR( p_value_to_round + 1 )) THEN
3041 l_retval := FLOOR( p_value_to_round + 1 );
3042 END IF;
3043 END IF; -- IF p_base_value <> 0 AND p_rounding_type <> 'NOROUND
3044
3045 RETURN l_retval ;
3046
3047 END round_value_up_down ;
3048 --
3049 --
3050 -- This funcaion is used to fecth the values for the rounding off factors from
3051 -- the PQP_CONFIGURATION_VALUES table.These values are used to round off the
3052 -- absences days and entitlments remaining days.
3053 FUNCTION pqp_get_config_value(
3054 p_business_group_id IN NUMBER
3055 ,p_legislation_code IN VARCHAR2
3056 ,p_column_name IN VARCHAR2
3057 ,p_information_category IN VARCHAR2
3058 ) RETURN VARCHAR2 IS
3059
3060 --Local variable declaration
3061 l_column_value VARCHAR(50);
3062 TYPE ref_csr_typ IS REF CURSOR;
3063 c_column_cursor ref_csr_typ;
3064 l_temp_str VARCHAR2(1000);
3065 BEGIN
3066
3067 l_temp_str := 'SELECT '|| p_column_name ||'
3068 FROM pqp_configuration_values
3069 WHERE ((business_group_id = ' ||p_business_group_id ||'
3070 AND legislation_code IS NULL )
3071 OR (business_group_id IS NULL
3072 AND legislation_code =
3073 '||''''||p_legislation_code ||''''||')
3074 OR (business_group_id IS NULL
3075 AND legislation_code IS NULL))
3076 AND PCV_INFORMATION_CATEGORY =
3077 '|| ''''||p_information_category ||''''||'
3078 ' ;
3079
3080 OPEN c_column_cursor FOR l_temp_str;
3081 FETCH c_column_cursor INTO l_column_value;
3082 CLOSE c_column_cursor;
3083
3084 -- Assign default values to avoid erroring of absence processing in case
3085 -- no rows are present in pqp_configuration_values for the information
3086 -- category PQP_GB_OSP_OMP_ROUND.
3087
3088 /* IF (l_column_value IS NULL) AND (p_column_name='PCV_INFORMATION1' OR p_column_name='PCV_INFORMATION3') THEN
3089 l_column_value:='DOWN';
3090 END IF;
3091
3092 IF (l_column_value IS NULL) AND (p_column_name='PCV_INFORMATION2' OR p_column_name='PCV_INFORMATION4') THEN
3093 l_column_value:='0.25';
3094 END IF;
3095 */
3096
3097 RETURN l_column_value;
3098 END pqp_get_config_value;
3099
3100
3101 ------------------pqp_get_ele_type_extra_info_id
3102 -- added by : vimittal
3103 -- added date : 10-Feb-2005
3104 -- purpose : The function returns the element type extra information id
3105 -- for the passed element type id and information type.
3106 -- Return -1 in case of Error and 0 in case of Success
3107
3108 FUNCTION pqp_get_ele_type_extra_info_id(
3109 p_element_type_id IN NUMBER
3110 ,p_information_type IN VARCHAR2
3111 ,p_element_type_extra_info_id OUT NOCOPY NUMBER
3112 ,p_error_msg OUT NOCOPY VARCHAR2
3113
3114 )
3115 RETURN NUMBER
3116 IS
3117 -- this cusrsor fetches the
3118 -- element_type_extra_info_id
3119 -- for the element Type_id passed
3120 CURSOR csr_get_ele_type_extra_info_id
3121 IS
3122 SELECT pei.element_type_extra_info_id
3123 FROM pay_element_type_extra_info pei
3124 WHERE pei.information_type = p_information_type
3125 AND pei.element_type_id = p_element_type_id;
3126
3127
3128 l_element_type_extra_info_id pay_element_type_extra_info.element_type_extra_info_id%TYPE;
3129 l_retval NUMBER := 0 ;
3130 l_proc_name VARCHAR2(61)
3131 := g_package_name || 'pqp_get_ele_type_extra_info_id';
3132
3133 BEGIN
3134 g_debug := hr_utility.debug_enabled;
3135 IF g_debug THEN
3136 debug_enter(l_proc_name);
3137 END IF;
3138
3139 OPEN csr_get_ele_type_extra_info_id;
3140 FETCH csr_get_ele_type_extra_info_id INTO l_element_type_extra_info_id ;
3141
3142 IF csr_get_ele_type_extra_info_id%NOTFOUND THEN
3143 --
3144 IF g_debug THEN
3145 debug(l_proc_name, 10);
3146 END IF;
3147 l_element_type_extra_info_id := NULL ;
3148 l_retval := -1 ;
3149 END IF;
3150 CLOSE csr_get_ele_type_extra_info_id ;
3151
3152 IF g_debug THEN
3153 debug('l_element_type_extra_info_id: '||to_char(l_element_type_extra_info_id),20);
3154 END IF;
3155
3156 p_element_type_extra_info_id := l_element_type_extra_info_id;
3157
3158 IF g_debug THEN
3159 debug_exit(l_proc_name);
3160 END IF;
3161 RETURN l_retval;
3162
3163 EXCEPTION
3164 WHEN OTHERS
3165 THEN
3166 IF g_debug THEN
3167 debug(l_proc_name||':Others Exception:');
3168 debug(SQLCODE);
3169 debug(SQLERRM);
3170 debug_exit(l_proc_name);
3171 END IF;
3172 p_error_msg := SQLERRM;
3173 p_element_type_extra_info_id := NULL;
3174 RETURN -1;
3175 END pqp_get_ele_type_extra_info_id;
3176
3177
3178 --
3179 ---------------------------chk_cached_udt_bucket-------------------------------
3180 FUNCTION chk_cached_udt_bucket (p_refresh_cache IN VARCHAR2
3181 ,p_business_group IN NUMBER
3182 ,p_table_name IN VARCHAR2
3183 ,p_error_msg OUT NOCOPY VARCHAR2
3184 )
3185 RETURN BOOLEAN
3186 IS
3187 l_table_in_cache BOOLEAN:= FALSE;
3188 l_proc_name VARCHAR2(70)
3189 := g_package_name || 'chk_cached_udt_bucket';
3190 l_err_msg VARCHAR2(100);
3191
3192
3193 BEGIN
3194
3195 IF g_debug THEN
3196 debug_enter(l_proc_name);
3197
3198 END IF;
3199
3200 IF g_cached_udt.EXISTS(g_hash_key) THEN
3201 IF ( ( g_cached_udt(g_hash_key).business_group_id = p_business_group
3202 AND g_cached_udt(g_hash_key).table_name = p_table_name )
3203 OR (g_cached_udt(g_hash_key).table_name = p_table_name ))
3204 THEN
3205
3206 IF p_refresh_cache = 'Y' THEN
3207 debug(l_proc_name,15);
3208 l_table_in_cache := FALSE;
3209 delete_udt_value --have a look at this procedure as well
3210 (p_table_name => p_table_name
3211 ,p_error_msg => l_err_msg
3212 );
3213 ELSE
3214 IF g_debug THEN
3215 debug('Table is found in the hash bucket');
3216 END IF;
3217 l_table_in_cache := TRUE;
3218 END IF;
3219
3220 END IF;
3221 END IF;
3222
3223
3224 RETURN l_table_in_cache;
3225
3226 EXCEPTION
3227 WHEN OTHERS
3228 THEN
3229 IF g_debug THEN
3230 debug(l_proc_name||':Others Exception:');
3231 debug(SQLCODE);
3232 debug(SQLERRM);
3233 debug_exit(l_proc_name);
3234 END IF;
3235 p_error_msg := SQLERRM;
3236
3237
3238 END chk_cached_udt_bucket;
3239
3240 --
3241 -------------------------set_hash_parameters------------------------
3242 PROCEDURE set_hash_parameters
3243 (p_hash_base IN BINARY_INTEGER
3244 ,p_hash_size IN BINARY_INTEGER
3245 ,p_conflict_check IN BOOLEAN DEFAULT FALSE
3246 )
3247 IS
3248 BEGIN
3249 g_hash_base := p_hash_base;
3250 g_hash_size := p_hash_size;
3251 g_conflict_check := p_conflict_check;
3252 END set_hash_parameters;
3253 ------------------------get_hash_key------------------------------------
3254 FUNCTION get_hash_key
3255 (p_string IN VARCHAR2
3256 ,p_error_msg OUT NOCOPY VARCHAR2
3257 ,p_refresh_cache IN VARCHAR2
3258 ,p_business_group_id IN NUMBER
3259 ) RETURN BINARY_INTEGER
3260 IS
3261 l_hash_key BINARY_INTEGER;
3262 l_proc_name VARCHAR2(70)
3263 := g_package_name || 'get_hash_key';
3264 l_already_cached BOOLEAN ;
3265 BEGIN
3266 l_hash_key := DBMS_UTILITY.GET_HASH_VALUE(p_string, g_hash_base, g_hash_size);
3267 IF g_conflict_check THEN
3268 IF g_hash_keys.EXISTS(l_hash_key) THEN
3269 -- conflict raise an exception
3270 IF g_hash_keys(l_hash_key).hash_string <> p_string THEN
3271 -- it's a conflict situation
3272 RAISE TOO_MANY_ROWS;
3273 END IF;
3274 ELSE
3275 g_hash_keys(l_hash_key).hash_string := p_string;
3276 END IF;
3277 END IF;
3278 RETURN l_hash_key;
3279 EXCEPTION
3280 --
3281 WHEN OTHERS
3282 THEN
3283 IF g_debug THEN
3284 debug(l_proc_name||':Others Exception:');
3285 debug(SQLCODE);
3286 debug(SQLERRM);
3287 debug_exit(l_proc_name);
3288 END IF;
3289 p_error_msg := SQLERRM;
3290 RETURN -1;
3291
3292 END get_hash_key;
3293 ------------------------------reset_hash_keys----------------------------
3294 PROCEDURE reset_hash_keys
3295 IS
3296 BEGIN
3297 g_hash_keys.DELETE;
3298 END reset_hash_keys;
3299 ------------------------------set_hash_conflict_check_off----------------
3300
3301 PROCEDURE set_hash_conflict_check_off
3302 IS
3303 BEGIN
3304 reset_hash_keys;
3305 g_conflict_check := FALSE;
3306 END set_hash_conflict_check_off;
3307 -------------------set_hash_conflict_check_on-------------------------
3308 PROCEDURE set_hash_conflict_check_on
3309 IS
3310 BEGIN
3311 reset_hash_keys;
3312 g_conflict_check := TRUE;
3313 END set_hash_conflict_check_on;
3314 ---------------------------------------------------------------------------
3315
3316 -- ----------------------------------------------------------------------------
3317 -- |-------------------------< get_kflex_value >-----------------------------|
3318 -- Description: This procedure is to fetch the Key Flexfiled Value.
3319 -- This will return all segment values for the row specified by the
3320 -- key_column_name and key_column_value
3321 -- ----------------------------------------------------------------------------
3322 PROCEDURE get_kflex_value
3323 (p_entity_name IN VARCHAR2 -- name of the table holding the values
3324 ,p_key_column_name IN VARCHAR2 -- Key Column Name
3325 ,p_key_column_value IN VARCHAR2 -- Key Column Value
3326 ,p_segment_column_values OUT NOCOPY r_all_segment_values
3327 )
3328 IS
3329 l_proc varchar2(72) := g_package_name||'.get_kflex_value';
3330 -- Type Declarations
3331 TYPE base_table_ref_csr_typ IS REF CURSOR;
3332
3333 -- Variable Declarations
3334 c_base_table base_table_ref_csr_typ;
3335
3336 l_query VARCHAR2(4000); -- Dynamically constructed query
3337
3338 BEGIN
3339 hr_utility.set_location('Entering '||l_proc,10);
3340 IF (p_entity_name is not null) AND
3341 (p_key_column_name is not null) AND
3342 (p_key_column_value is not null) THEN
3343
3344 l_query :=
3345 'SELECT SEGMENT1';
3346 FOR i IN 1..30
3347 LOOP
3348 l_query := l_query||',SEGMENT'||i;
3349 END LOOP;
3350
3351 l_query := l_query||
3352 ' FROM '||p_entity_name||' '||
3353 'WHERE '||p_key_column_name||' = '||p_key_column_value;
3354 hr_utility.trace('l_query: '||l_query);
3355 hr_utility.trace('Before opening dynamic query');
3356
3357 OPEN c_base_table FOR l_query;
3358 FETCH c_base_table INTO p_segment_column_values;
3359 CLOSE c_base_table;
3360 hr_utility.trace('After precessing dynamic query');
3361 END IF; -- IF (p_entity_name is not null) AND..
3362 hr_utility.set_location('Leaving '||l_proc,10);
3363 END get_kflex_value;
3364 ---
3365 -- ----------------------------------------------------------------------------
3366 -- |-------------------------< get_kflex_value >-----------------------------|
3367 -- Description: This is to fetch the Configuration values.
3368 -- This will return the value of the column specified.
3369 -- ----------------------------------------------------------------------------
3370 PROCEDURE get_kflex_value
3371 (p_entity_name IN VARCHAR2 -- name of the table holding the values
3372 ,p_key_column_name IN VARCHAR2 -- Key Column Name
3373 ,p_key_column_value IN VARCHAR2 -- Key Column Value
3374 ,p_segment_column_name IN VARCHAR2
3375 ,p_segment_column_value OUT NOCOPY VARCHAR2
3376 )
3377 IS
3378 l_proc varchar2(72) := g_package_name||'.get_config_value';
3379 -- Type Declarations
3380 TYPE base_table_ref_csr_typ IS REF CURSOR;
3381
3382 -- Variable Declarations
3383 c_base_table base_table_ref_csr_typ;
3384
3385 l_query VARCHAR2(4000); -- Dynamically constructed query
3386 BEGIN
3387 hr_utility.set_location('Entering '||l_proc,10);
3388 l_query := 'SELECT '|| p_segment_column_name ||'
3389 FROM '|| p_entity_name ||'
3390 WHERE '||p_key_column_name||' = '|| p_key_column_value;
3391
3392 OPEN c_base_table FOR l_query;
3393 FETCH c_base_table INTO p_segment_column_value;
3394 CLOSE c_base_table;
3395
3396 hr_utility.set_location('Leaving '||l_proc,10);
3397 END get_kflex_value;
3398 --
3399 /*---------------------------------------------------------------/
3400 /--Description: This is a wrapper procedure on pay_interpreter_pkg.entries_affected
3401 -- pay_interpreter_pkg.entry_affected.
3402 -- Depending upon the elements entries on the assignment
3403 -- which are of type of elements which are attached to
3404 -- the element set which are attached to the event group
3405 -- usages, this procedure calls entries_affected or entry_affected
3406 -- and returns the table of events for the event group during the
3407 -- date range specified
3408 */
3409 PROCEDURE entries_affected
3410 (p_assignment_id IN NUMBER DEFAULT NULL
3411 ,p_event_group_id IN NUMBER DEFAULT NULL
3412 ,p_mode IN VARCHAR2 DEFAULT NULL
3413 ,p_start_date IN DATE DEFAULT hr_api.g_sot
3414 ,p_end_date IN DATE DEFAULT hr_api.g_eot
3415 ,p_business_group_id IN NUMBER
3416 ,p_detailed_output OUT NOCOPY pay_interpreter_pkg.t_detailed_output_table_type
3417 ,p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE'
3418 )
3419 IS
3420 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3421
3422 TYPE r_element_entries IS RECORD
3423 (
3424 element_entry_id t_number
3425 ,datetracked_event_id t_number
3426 );
3427
3428 l_proc VARCHAR2(70) := g_package_name||'.entries_effected';
3429 l_datetrack_ee_tab r_element_entries;
3430 l_count NUMBER := 0;
3431 l_global_env pay_interpreter_pkg.t_global_env_rec;
3432 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
3433 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
3434 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
3435 l_eg_has_ee_tab VARCHAR2(1);
3436
3437
3438 CURSOR csr_chk_eg_for_ee_tab
3439 IS
3440 SELECT 'Y'
3441 FROM pay_datetracked_events pde
3442 ,pay_dated_tables pdt
3443 WHERE event_group_id = p_event_group_id
3444 AND pde.dated_table_id = pdt.dated_table_id
3445 AND (pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
3446 OR
3447 pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
3448 )
3449 AND ROWNUM < 2;
3450
3451 FUNCTION get_element_entries_for_eg
3452 (p_event_group_id IN NUMBER
3453 ,p_assignment_id IN NUMBER
3454 ,p_start_date IN DATE
3455 ,p_end_date IN DATE
3456 ,p_element_entries_tab OUT NOCOPY r_element_entries
3457 ) RETURN NUMBER -- number of element entries in the out param table
3458 IS
3459 l_proc VARCHAR2(70) := g_package_name||'.get_element_entries_for_eg';
3460 l_purge_dte_id NUMBER;
3461 l_purge_ee_ids t_number;
3462 l_next NUMBER;
3463 l_element_entries_tab r_element_entries;
3464 l_element_set_ids_tab t_number;
3465 l_index NUMBER;
3466 l_match_exists VARCHAR2(10);
3467
3468 CURSOR csr_get_element_set (p_event_group_id IN NUMBER)
3469 IS
3470 SELECT element_set_id
3471 FROM pay_event_group_usages
3472 WHERE event_group_id = p_event_group_id;
3473
3474 CURSOR csr_element_entries
3475 (p_element_set_id IN NUMBER
3476 ,p_event_group_id IN NUMBER
3477 ,p_assignment_id IN NUMBER
3478 ,p_start_date IN DATE
3479 ,p_end_date IN DATE
3480 )
3481 IS
3482 SELECT distinct pee.element_entry_id
3483 ,pde.datetracked_event_id
3484 FROM pay_element_set_members pes
3485 ,pay_element_entries_f pee
3486 ,pay_datetracked_events pde
3487 WHERE pes.element_set_id = p_element_set_id
3488 AND pee.element_type_id = pes.element_type_id
3489 AND pee.assignment_id = p_assignment_id
3490 AND pde.event_group_id = p_event_group_id
3491 AND (
3492 p_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
3493 OR
3494 p_end_date BETWEEN pee.effective_start_date AND pee.effective_end_date
3495 OR
3496 pee.effective_start_date BETWEEN p_start_date AND p_end_date
3497 OR
3498 pee.effective_end_date BETWEEN p_start_date AND p_end_date
3499 );
3500
3501 -- this is used to check for any datetracked events for purge events on
3502 -- element entries in the event group.
3503 CURSOR csr_get_purge_events_on_eg
3504 IS
3505 SELECT datetracked_event_id
3506 FROM pay_datetracked_events pde
3507 ,pay_dated_tables pdt
3508 WHERE event_group_id = p_event_group_id
3509 AND pde.dated_table_id = pdt.dated_table_id
3510 AND pde.update_type = 'P'
3511 AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';
3512
3513
3514 -- this is used to fetch the element entry ids of the
3515 -- puged element entries.
3516 -- the element tntry ids are fetched by comparing the
3517 -- element type id in the element set attached to the
3518 -- event group and the element type id stored in the
3519 -- column 'NOTED_VALUE' of pay_process_events fro purged
3520 -- element entry events.
3521 CURSOR csr_get_purged_ee_ids (p_element_set_id IN NUMBER)
3522 IS
3523 SELECT distinct ppe.surrogate_key
3524 FROM pay_element_set_members pes
3525 ,pay_process_events ppe
3526 ,pay_event_updates peu
3527 WHERE pes.element_set_id = p_element_set_id
3528 AND ppe.assignment_id = p_assignment_id
3529 AND ppe.noted_value = pes.element_type_id
3530 AND peu.event_update_id = ppe.event_update_id
3531 AND peu.event_type = 'ZAP'
3532 AND ppe.effective_date BETWEEN p_start_date AND p_end_date;
3533
3534
3535 BEGIN
3536 hr_utility.trace('Entering: '||l_proc);
3537 hr_utility.trace('Entered get_element_entries_for_eg: EG_Id:'||to_char(p_event_group_id));
3538 hr_utility.trace('Assignment Id:'||to_char(p_assignment_id));
3539 hr_utility.trace('Start Date:'||to_char(p_start_date, 'DD/MM/YYYY'));
3540 hr_utility.trace('End Date:'||to_char(p_end_date, 'DD/MM/YYYY'));
3541
3542 p_element_entries_tab.element_entry_id.DELETE;
3543 p_element_entries_tab.datetracked_event_id.DELETE;
3544
3545 -- check for the purge datetracked events on element entries
3546 -- in the event group and include them in the p_element_entires_tab collection
3547 OPEN csr_get_purge_events_on_eg;
3548 FETCH csr_get_purge_events_on_eg INTO l_purge_dte_id;
3549
3550 OPEN csr_get_element_set (p_event_group_id => p_event_group_id);
3551 FETCH csr_get_element_set BULK COLLECT INTO l_element_set_ids_tab;
3552 CLOSE csr_get_element_set;
3553 hr_utility.trace('Count:'||to_char(l_element_set_ids_tab.COUNT));
3554 FOR i IN 1..l_element_set_ids_tab.COUNT LOOP
3555 OPEN csr_element_entries
3556 (p_element_set_id => l_element_set_ids_tab(i)
3557 ,p_event_group_id => p_event_group_id
3558 ,p_assignment_id => p_assignment_id
3559 ,p_start_date => p_start_date
3560 ,p_end_date => p_end_date
3561 );
3562 FETCH csr_element_entries BULK COLLECT INTO l_element_entries_tab;
3563 CLOSE csr_element_entries;
3564 hr_utility.trace('Count:'||to_char(l_element_entries_tab.element_entry_id.COUNT));
3565
3566
3567 IF csr_get_purge_events_on_eg%FOUND THEN
3568 -- if there are purge events in the event group
3569 hr_utility.trace('There are puge events on element entries table in the eg.');
3570 OPEN csr_get_purged_ee_ids(l_element_set_ids_tab(i));
3571 FETCH csr_get_purged_ee_ids BULK COLLECT INTO l_purge_ee_ids;
3572 CLOSE csr_get_purged_ee_ids;
3573
3574 hr_utility.trace('Fill the values in the element entries collection.');
3575 FOR i IN 1..l_purge_ee_ids.COUNT
3576 LOOP
3577 hr_utility.trace('l_purge_ee_ids(i): '||l_purge_ee_ids(i));
3578 -- bug fix 5368066. nvl is added for this bug fix.
3579 l_next := nvl(l_element_entries_tab.element_entry_id.LAST,0) + 1;
3580 l_element_entries_tab.element_entry_id(l_next) := fnd_number.canonical_to_number(l_purge_ee_ids(i));
3581 l_element_entries_tab.datetracked_event_id(l_next) := l_purge_dte_id;
3582 END LOOP;
3583 END IF;
3584
3585 FOR i IN 1..l_element_entries_tab.element_entry_id.COUNT LOOP
3586 IF p_element_entries_tab.element_entry_id.COUNT = 0 THEN
3587 p_element_entries_tab := l_element_entries_tab;
3588 EXIT;
3589 ELSE -- count is non zero
3590 l_index := p_element_entries_tab.element_entry_id.LAST;
3591 l_match_exists := 'N';
3592 FOR j IN 1..p_element_entries_tab.element_entry_id.COUNT LOOP
3593 IF p_element_entries_tab.element_entry_id(j) = l_element_entries_tab.element_entry_id(i) AND
3594 p_element_entries_tab.datetracked_event_id(j) = l_element_entries_tab.datetracked_event_id(i)
3595 THEN
3596 -- Combination exist so do nothing
3597 l_match_exists := 'Y';
3598 EXIT;
3599 END IF; -- End if of match exists check ...
3600 END LOOP; -- j loop
3601 IF l_match_exists = 'N' THEN
3602 -- store the information
3603 l_index := l_index + 1;
3604 p_element_entries_tab.element_entry_id(l_index) := l_element_entries_tab.element_entry_id(i);
3605 p_element_entries_tab.datetracked_event_id(l_index) := l_element_entries_tab.datetracked_event_id(i);
3606 END IF; -- End if of match does not exist ...
3607 END IF; -- End if of return collection count is zero check ...
3608 END LOOP; -- i loop
3609
3610 END LOOP; -- element set loop ...
3611 CLOSE csr_get_purge_events_on_eg;
3612 hr_utility.trace('Count:'||to_char(p_element_entries_tab.element_entry_id.COUNT));
3613
3614 hr_utility.trace('Leaving: '||l_proc);
3615 RETURN p_element_entries_tab.element_entry_id.COUNT;
3616
3617 EXCEPTION
3618 WHEN OTHERS THEN
3619 -- NOCOPY
3620 p_element_entries_tab.element_entry_id.DELETE;
3621 p_element_entries_tab.datetracked_event_id.DELETE;
3622 RAISE;
3623 END get_element_entries_for_eg;
3624 ---------
3625 BEGIN --entries_effected
3626 hr_utility.trace('Entering: '||l_proc);
3627 hr_utility.trace('Get the element entries for the assignment id');
3628
3629 -- Bugfix 4739067: Performance enhancement
3630 -- Checking if the event group has element entries or
3631 -- element entry values table before trying to fetch events
3632 -- If the EG does not have EE tables, we use the entry_affected call
3633 OPEN csr_chk_eg_for_ee_tab;
3634 FETCH csr_chk_eg_for_ee_tab INTO l_eg_has_ee_tab;
3635 CLOSE csr_chk_eg_for_ee_tab;
3636
3637 IF l_eg_has_ee_tab = 'Y' THEN
3638 l_count := get_element_entries_for_eg
3639 (p_event_group_id => p_event_group_id
3640 ,p_assignment_id => p_assignment_id
3641 ,p_start_date => p_start_date
3642 ,p_end_date => p_end_date
3643 ,p_element_entries_tab => l_datetrack_ee_tab
3644 );
3645 ELSE
3646 l_count := 0;
3647 END IF;
3648
3649 -----
3650 -- This line can be removed after fix from pay for missing events on mix of calls to
3651 -- entry_affected and entries_affected - kkarri
3652 pay_interpreter_pkg.t_distinct_tab := pay_interpreter_pkg.glo_monitored_events;
3653 -----
3654 IF l_count > 0 THEN
3655 hr_utility.trace('Our procedure');
3656 hr_utility.trace('Setup the global area');
3657 pay_interpreter_pkg.initialise_global(l_global_env);
3658 pay_interpreter_pkg.event_group_tables
3659 (p_event_group_id => p_event_group_id
3660 ,p_distinct_tab => pay_interpreter_pkg.glo_monitored_events
3661 );
3662 --The start and end pointers can be just for the event group.
3663 -- So, commenting out these lines. - kkarri
3664 /*l_global_env.monitor_start_ptr := 1;
3665 l_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;*/
3666 l_global_env.monitor_start_ptr
3667 := pay_interpreter_pkg.t_proration_group_tab(p_event_group_id).range_start;
3668 l_global_env.monitor_end_ptr
3669 := pay_interpreter_pkg.t_proration_group_tab(p_event_group_id).range_end;
3670 ---
3671 l_global_env.datetrack_ee_tab_use := TRUE;
3672 l_global_env.validate_run_actions := FALSE;
3673
3674 FOR i IN l_datetrack_ee_tab.element_entry_id.FIRST..l_datetrack_ee_tab.element_entry_id.LAST
3675 LOOP
3676 hr_utility.trace('----------------------------------');
3677 hr_utility.trace('i: '||i);
3678 hr_utility.trace('datetracked_event_id: '||l_datetrack_ee_tab.datetracked_event_id(i));
3679 hr_utility.trace('element_entry_id: '||l_datetrack_ee_tab.element_entry_id(i));
3680 pay_interpreter_pkg.add_datetrack_event_to_entry
3681 (p_datetracked_evt_id => l_datetrack_ee_tab.datetracked_event_id(i)
3682 ,p_element_entry_id => l_datetrack_ee_tab.element_entry_id(i)
3683 ,p_global_env => l_global_env
3684 );
3685 END LOOP;
3686 hr_utility.trace('Entered all the dte_id X ee_ids');
3687
3688 BEGIN
3689 --call entries_effected
3690 pay_interpreter_pkg.entries_affected
3691 (p_assignment_id => p_assignment_id
3692 ,p_mode => p_mode
3693 ,p_start_date => p_start_date
3694 ,p_end_date => p_end_date
3695 ,p_business_group_id => p_business_group_id
3696 ,p_global_env => l_global_env
3697 ,t_detailed_output => p_detailed_output
3698 ,p_process_mode => p_process_mode
3699 );
3700 EXCEPTION
3701 WHEN NO_DATA_FOUND THEN
3702 hr_utility.trace('No payroll run for the assignment');
3703 hr_utility.set_message(8303,'BEN_94629_NO_ASG_ACTION_ID');
3704 hr_utility.raise_error;
3705 END;
3706 -- reset l_global_env
3707 pay_interpreter_pkg.clear_dt_event_for_entry
3708 (p_global_env => l_global_env);
3709 ELSE
3710 hr_utility.trace('Normal call to entries_effected');
3711 --call entry_affected
3712 pay_interpreter_pkg.entry_affected(
3713 p_element_entry_id => NULL
3714 ,p_assignment_action_id => NULL
3715 ,p_assignment_id => p_assignment_id
3716 ,p_mode => p_mode
3717 ,p_process => NULL -- 'U' --
3718 ,p_event_group_id => p_event_group_id
3719 ,p_process_mode => p_process_mode
3720 ,p_start_date => p_start_date
3721 ,p_end_date => p_end_date
3722 ,t_detailed_output => p_detailed_output -- OUT
3723 ,t_proration_dates => l_proration_dates -- OUT
3724 ,t_proration_change_type => l_proration_changes -- OUT
3725 ,t_proration_type => l_pro_type_tab -- OUT
3726 );
3727 END IF;
3728 hr_utility.trace('Leaving: '||l_proc);
3729 END entries_affected;
3730 --
3731 END pqp_utilities;
[Home] [Help]