1 PACKAGE BODY hr_po_info AS
2 /* $Header: hrpoinfo.pkb 120.0 2005/05/31 02:14:15 appldev noship $ */
3 /*
4 +==========================================================================+
5 | Copyright (c) 2003 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==========================================================================+
9
10 Please see the package specification for details on this package and
11 its procedures / functions.
12
13 Change History
14 -------+--------+-----------+-------+--------------------------------------|
15 dcasemor 10-SEP-2003 115.0 Created for bug 3120074.
16 dcasemor 15-SEP-2003 115.1 Added wrapper functions to return
17 varchar2s instead of booleans. Changed
18 initialise_globals to check for all
19 4 PO columns plus tuned the SQL. Added
20 the full_cwk_enabled pair of
21 functions. Added procedures to return
22 PL/SQL tables for PO bulk APIs.
23 dcasemor 16-OCT-2003 115.2 Added more interoperable select
24 procedures:
25 - get_vendor_for_primary_asg
26 - get_vendor_for_asg
27 - get_po_for_primary_asg
28 - get_po_for_asg
29 dcasemor 17-OCT-2003 115.3 For the above 4 procedures, only
30 return rows when one of the values
31 is set to avoid the SQL returning
32 two rows for multiple assignments.
33 sramasam 22-OCT-2003 115.4 Added procedure to return url
34 to launch Self Service CWK Placement
35 dcasemor 29-JAN-2004 115.5 Added date-effective checking
36 in get_person_for_po_line and
37 get_person_for_vendor_site to
38 prevent a too_many_rows exception.
39 njaladi 17-mar-2004 115.6 Bug 3512537: gscc fix for sql.47
40 njaladi 17-mar-2004 115.7 Bug 3512537: gscc fix for sql.47
41 sbuche 26-mar-2004 115.8 Bug 3391399:
42 Added function asg_vendor_id_exist
43 to check the existance of vendor_id
44 column in HR assignments tables.
45 sbuche 02-APR-2002 115.9 Changes requested by OTA:
46 Added parameter p_effective_date to
47 the procedure get_person_for_po_line
48 and defaulted the value Trunc(sysdate)
49 when parameter is null.
50
51 Commented following functions and
52 procedure as they will not work
53 when more than one person or
54 assignment exists for a vendor site:
55 - get_asg_id_for_vendor_site
56 - get_person_id_for_vendor_site
57 - get_person_for_vendor_site
58
59 Modified the following procedure to
60 remove the reference of commented
61 procedure:
62 - asg_exist_for_vendor_site
63 svittal 21-APR-2004 115.10 Added code to generate url for PO
64 Notification.
65 svittal 13-JUN-2004 115.11 Bug fix 3666156.
66
67 njaladi 25-May-2005 115.12 Bug Fix 4323611: Modified procedure
68 asg_exist_for_po to use bind variables
69 instead of concatenation.
70 ---------------------------------------------------------------------------|
71 */
72
73 --
74 -- Private package constant declarations.
75 --
76 g_PACKAGE CONSTANT VARCHAR2(11) := 'hr_po_info.';
77
78 --
79 -- Private package variable declarations.
80 --
81 g_asg_po_cols_exist BOOLEAN := FALSE;
82
83 -- Addded package variable to fix the Bug 3391399
84 g_asg_vendor_id_exist BOOLEAN := FALSE;
85 --
86 ---------------------------------------------------------------------------|
87 ------------------------< DEBUG_ENABLED >----------------------------------|
88 ---------------------------------------------------------------------------|
89 --
90 FUNCTION debug_enabled RETURN BOOLEAN IS
91
92 BEGIN
93
94 RETURN g_debug;
95
96 END debug_enabled;
97 --
98 ---------------------------------------------------------------------------|
99 ----------------------< INITIALISE_GLOBALS >-------------------------------|
100 ---------------------------------------------------------------------------|
101 --
102 -- This procedure is private. It is executed when the package is first
103 -- called; the package private flags remain cached thereafter.
104 --
105 PROCEDURE initialise_globals
106 IS
107
108 e_plsql_compilation_error EXCEPTION;
109 PRAGMA EXCEPTION_INIT(e_plsql_compilation_error,-06550);
110 l_count NUMBER;
111 l_sql VARCHAR2(500);
112 -- 3512537 start
113 l_status varchar2(50);
114 l_industry varchar2(50);
115 l_per_owner varchar2(30);
116 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
117 l_industry, l_per_owner);
118 -- 3512537 end
119 BEGIN
120
121 --
122 -- Check if the PO columns are on the HR assignments tables.
123 --
124 SELECT count(dd.column_name)
125 INTO l_count
126 FROM all_tab_columns dd
127 WHERE dd.table_name = 'PER_ALL_ASSIGNMENTS_F'
128 AND (dd.column_name = 'VENDOR_ID' OR
129 dd.column_name = 'VENDOR_SITE_ID' OR
130 dd.column_name = 'PO_HEADER_ID' OR
131 dd.column_name = 'PO_LINE_ID')
132 AND rownum < 5
133 AND dd.owner = l_per_owner; -- 3512537
134
135 g_asg_po_cols_exist := (l_count = 4);
136
137 -- Added code to fix the Bug 3391399
138 -- Check if VENDOR_ID column exist in the HR assignments table
139 --
140 SELECT count(dd.column_name)
141 INTO l_count
142 FROM all_tab_columns dd
143 WHERE dd.table_name = 'PER_ALL_ASSIGNMENTS_F'
144 AND dd.column_name = 'VENDOR_ID'
145 AND rownum < 2
146 AND dd.owner = l_per_owner; -- 3512537
147
148 g_asg_vendor_id_exist := (l_count = 1);
149 -- End of code added for Bug 3391339
150
151 --
152 -- Determine whether HR debugging is enabled.
153 -- This code uses NDS because the external function
154 -- is not available in base 11i.
155 --
156 l_sql :=
157 ' BEGIN '
158 ||' IF NOT hr_utility.debug_enabled THEN '
159 ||' hr_po_info.g_debug := FALSE; '
160 ||' END IF; '
161 ||' END; ';
162
163 EXECUTE IMMEDIATE l_sql;
164
165 EXCEPTION
166
167 WHEN e_plsql_compilation_error THEN
168 --
169 -- The debug_enabled function does not exist.
170 --
171 NULL;
172
173 END initialise_globals;
174 --
175 ---------------------------------------------------------------------------|
176 ------------------------< FULL_CWK_ENABLED >-------------------------------|
177 ---------------------------------------------------------------------------|
178 --
179 FUNCTION full_cwk_enabled RETURN BOOLEAN
180 IS
181
182 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'full_cwk_enabled';
183
184 BEGIN
185
186 IF g_debug THEN
187 hr_utility.set_location(l_PROC, 10);
188 END IF;
189
190 --
191 -- Return a boolean indicating whether services procurement is
192 -- installed.
193 --
194 RETURN (NVL(fnd_profile.value('PO_SERVICES_ENABLED'), 'N') = 'Y');
195
196 END full_cwk_enabled;
197 --
198 ---------------------------------------------------------------------------|
199 ------------------------< FULL_CWK_ENABLED_CHAR >--------------------------|
200 ---------------------------------------------------------------------------|
201 --
202 FUNCTION full_cwk_enabled_char RETURN VARCHAR2
203 IS
204
205 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'full_cwk_enabled_char';
206 l_return VARCHAR2(5) := g_FALSE;
207
208 BEGIN
209
210 IF g_debug THEN
211 hr_utility.set_location(l_PROC, 10);
212 END IF;
213
214 --
215 -- Return a varchar indicating whether services procurement is
216 -- installed.
217 --
218 IF full_cwk_enabled THEN
219 l_return := g_TRUE;
220 END IF;
221
222 RETURN l_return;
223
224 END full_cwk_enabled_char;
225 --
226 ---------------------------------------------------------------------------|
227 ------------------------< ASG_PO_COLS_EXIST >------------------------------|
228 ---------------------------------------------------------------------------|
229 --
230 FUNCTION asg_po_cols_exist RETURN BOOLEAN
231 IS
232
233 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asg_po_cols_exist';
234
235 BEGIN
236
237 IF g_debug THEN
238 hr_utility.set_location(l_PROC, 10);
239 END IF;
240
241 --
242 -- Package instantiation ensures this flag is always set
243 -- correctly.
244 --
245 RETURN g_asg_po_cols_exist;
246
247 END asg_po_cols_exist;
248 --
249 -- Added Function to fix the Bug 3391399
250 ---------------------------------------------------------------------------|
251 ------------------------< ASG_VENDOR_ID_EXIST >----------------------------|
252 ---------------------------------------------------------------------------|
253 --
254 FUNCTION asg_vendor_id_exist RETURN BOOLEAN
255 IS
256
257 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asg_vendor_id_exist';
258
259 BEGIN
260
261 IF g_debug THEN
262 hr_utility.set_location(l_PROC, 10);
263 END IF;
264
265 --
266 -- Package instantiation ensures this flag is always set
267 -- correctly.
268 --
269 RETURN g_asg_vendor_id_exist;
270
271 END asg_vendor_id_exist;
272 --
273 ---------------------------------------------------------------------------|
274 ------------------------< GET_PERSON_FOR_PO_LINE >-------------------------|
275 ---------------------------------------------------------------------------|
276 --
277 PROCEDURE get_person_for_po_line
278 (p_po_line_id IN NUMBER
279 ,p_person_id OUT NOCOPY NUMBER
280 ,p_assignment_id OUT NOCOPY NUMBER
281 ,p_effective_date IN DATE DEFAULT NULL)
282 IS
283
284 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'get_person_for_po_line';
285 l_sql VARCHAR2(500);
286 l_effective_date DATE;
287
288 BEGIN
289
290 IF g_debug THEN
291 hr_utility.set_location('Entering: ' || l_PROC, 10);
292 hr_utility.set_location(to_char(p_po_line_id), 20);
293 END IF;
294
295 IF p_po_line_id IS NOT NULL AND asg_po_cols_exist THEN
296 --
297 -- Select the person details, given a line. NDS is used
298 -- so that the procedure compiles when the columns do
299 -- not exist (it is only executed when the columns do
300 -- exist).
301 --
302 l_effective_date := nvl(p_effective_date,TRUNC(sysdate));
303 l_sql :=
304 ' SELECT paaf.person_id '
305 ||' ,paaf.assignment_id '
306 ||' FROM per_all_assignments_f paaf '
307 ||' WHERE paaf.po_line_id IS NOT NULL '
308 ||' AND paaf.po_line_id = '||p_po_line_id
309 ||' AND fnd_date.canonical_to_date('''
310 || fnd_date.date_to_canonical(l_effective_date)||''')'
311 ||' BETWEEN paaf.effective_start_date '
312 ||' AND paaf.effective_end_date ';
313
314 EXECUTE IMMEDIATE l_sql INTO p_person_id, p_assignment_id;
315
316 END IF;
317
318 IF g_debug THEN
319 hr_utility.set_location('Leaving: ' || l_PROC, 30);
320 END IF;
321
322 EXCEPTION
323
324 WHEN no_data_found THEN
325
326 IF g_debug THEN
327 hr_utility.set_location('Leaving: ' || l_PROC, 40);
328 END IF;
329
330 END get_person_for_po_line;
331 --
332 ---------------------------------------------------------------------------|
333 ------------------------< GET_PERSON_ID_FOR_PO_LINE >----------------------|
334 ---------------------------------------------------------------------------|
335 --
336 FUNCTION get_person_id_for_po_line
337 (p_po_line_id IN NUMBER) RETURN NUMBER
338 IS
339
340 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
341 ||'get_person_id_for_po_line';
342 l_person_id NUMBER;
343 l_dummy NUMBER;
344
345 BEGIN
346
347 IF g_debug THEN
348 hr_utility.set_location('Entering: ' || l_PROC, 10);
349 END IF;
350
351 get_person_for_po_line
352 (p_po_line_id => p_po_line_id
353 ,p_person_id => l_person_id
354 ,p_assignment_id => l_dummy);
355
356 IF g_debug THEN
357 hr_utility.set_location('Leaving: ' || l_PROC, 20);
358 END IF;
359
360 RETURN l_person_id;
361
362 END get_person_id_for_po_line;
363 --
364 ---------------------------------------------------------------------------|
365 ------------------------< GET_ASG_ID_FOR_PO_LINE >-------------------------|
366 ---------------------------------------------------------------------------|
367 --
368 FUNCTION get_asg_id_for_po_line
369 (p_po_line_id IN NUMBER) RETURN NUMBER
370 IS
371
372 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
373 ||'get_asg_id_for_po_line';
374 l_asg_id NUMBER;
375 l_dummy NUMBER;
376
377 BEGIN
378
379 IF g_debug THEN
380 hr_utility.set_location('Entering: ' || l_PROC, 10);
381 END IF;
382
383 get_person_for_po_line
384 (p_po_line_id => p_po_line_id
385 ,p_person_id => l_dummy
386 ,p_assignment_id => l_asg_id);
387
388 IF g_debug THEN
389 hr_utility.set_location('Leaving: ' || l_PROC, 20);
390 END IF;
391
392 RETURN l_asg_id;
393
394 END get_asg_id_for_po_line;
395 --
396 ---------------------------------------------------------------------------|
397 ------------------------< ASG_EXIST_FOR_PO >-------------------------------|
398 ---------------------------------------------------------------------------|
399 --
400 FUNCTION asg_exist_for_po
401 (p_po_header_id IN NUMBER) RETURN BOOLEAN
402 IS
403
404 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asg_exist_for_po';
405 l_sql VARCHAR2(500);
409
406 l_found VARCHAR2(5) := g_FALSE;
407
408 BEGIN
410 IF g_debug THEN
411 hr_utility.set_location('Entering: ' || l_PROC, 10);
412 hr_utility.set_location(to_char(p_po_header_id), 20);
413 END IF;
414
415 IF p_po_header_id IS NOT NULL AND asg_po_cols_exist THEN
416 --
417 -- Check to see if any assignments exist for this PO.
418 -- Only the first row is returned because the function
419 -- simply needs to know whether an assignments exists
420 -- or it does not.
421 -- NDS is used to avoid column dependencies.
422 --
423 -- Modified Dynamic SQL to use bind variables
424 --
425 l_sql :=
426 ' SELECT :1 '
427 ||' FROM per_all_assignments_f paaf '
428 ||' WHERE paaf.po_header_id IS NOT NULL '
429 ||' AND paaf.po_header_id = :2 '---||p_po_header_id
430 ||' AND rownum = 1';
431
432 EXECUTE IMMEDIATE l_sql INTO l_found USING g_true,p_po_header_id;
433
434 END IF;
435
436 IF g_debug THEN
437 hr_utility.set_location('Leaving: ' || l_PROC, 30);
438 END IF;
439
440 RETURN (l_found = g_TRUE);
441
442 EXCEPTION
443
444 WHEN no_data_found THEN
445
446 IF g_debug THEN
447 hr_utility.set_location('Leaving: ' || l_PROC, 40);
448 END IF;
449
450 --
451 -- There are no assignments for this PO.
452 --
453 RETURN FALSE;
454
455 END asg_exist_for_po;
456 --
457 ---------------------------------------------------------------------------|
458 ------------------------< ASG_EXIST_FOR_PO_LINE >--------------------------|
459 ---------------------------------------------------------------------------|
460 --
461 FUNCTION asg_exist_for_po_line
462 (p_po_line_id IN NUMBER) RETURN BOOLEAN
463 IS
464
465 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asg_exist_for_po_line';
466
467 BEGIN
468
469 IF g_debug THEN
470 hr_utility.set_location(l_PROC, 10);
471 END IF;
472
473 RETURN (get_asg_id_for_po_line
474 (p_po_line_id => p_po_line_id) IS NOT NULL);
475
476 END asg_exist_for_po_line;
477 --
478 ---------------------------------------------------------------------------|
479 ------------------------< ASG_EXIST_FOR_PO_CHAR >--------------------------|
480 ---------------------------------------------------------------------------|
481 --
482 FUNCTION asg_exist_for_po_char
483 (p_po_header_id IN NUMBER) RETURN VARCHAR2
484 IS
485
486 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
487 ||'asg_exist_for_po_char';
488 l_return VARCHAR2(5) := g_FALSE;
489
490 BEGIN
491
492 IF g_debug THEN
493 hr_utility.set_location('Entering: ' || l_PROC, 10);
494 END IF;
495
496 --
497 -- Record the BOOLEAN as a VARCHAR2.
498 --
499 IF asg_exist_for_po
500 (p_po_header_id => p_po_header_id) THEN
501 l_return := g_TRUE;
502 END IF;
503
504 IF g_debug THEN
505 hr_utility.set_location('Leaving: ' || l_PROC, 20);
506 END IF;
507
508 RETURN l_return;
509
510 END asg_exist_for_po_char;
511 --
512 ---------------------------------------------------------------------------|
513 ------------------------< ASG_EXIST_FOR_PO_LINE_CHAR >---------------------|
514 ---------------------------------------------------------------------------|
515 --
516 FUNCTION asg_exist_for_po_line_char
517 (p_po_line_id IN NUMBER) RETURN VARCHAR2
518 IS
519
520 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
521 ||'asg_exist_for_po_line_char';
522 l_return VARCHAR2(5) := g_FALSE;
523
524 BEGIN
525
526 IF g_debug THEN
527 hr_utility.set_location('Entering: ' || l_PROC, 10);
528 END IF;
529
530 --
531 -- Record the BOOLEAN as a VARCHAR2.
532 --
533 IF asg_exist_for_po_line
534 (p_po_line_id => p_po_line_id) THEN
535 l_return := g_TRUE;
536 END IF;
537
538 IF g_debug THEN
539 hr_utility.set_location('Leaving: ' || l_PROC, 20);
540 END IF;
541
542 RETURN l_return;
543
544 END asg_exist_for_po_line_char;
545 --
546 ---------------------------------------------------------------------------|
547 ------------------------< ASGS_EXIST_FOR_POS >-----------------------------|
548 ---------------------------------------------------------------------------|
549 --
550 PROCEDURE asgs_exist_for_pos
551 (p_po_in_tbl IN g_table_numbers_t
552 ,p_po_out_tbl OUT NOCOPY g_table_numbers_t)
553 IS
554
555 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asgs_exist_for_pos';
556 j NUMBER := 1;
557
558 BEGIN
559
563
560 IF g_debug THEN
561 hr_utility.set_location('Entering: ' || l_PROC, 10);
562 END IF;
564 --
565 -- Enumerate through the passed in PL/SQL table.
566 -- The row's index is not used but the row's value is the PO ID.
567 -- For each PO ID, determine whether any assignments are
568 -- assigned.
569 --
570 IF p_po_in_tbl.COUNT > 0 THEN
571
572 FOR i IN p_po_in_tbl.FIRST..p_po_in_tbl.LAST LOOP
573
574 IF g_debug THEN
575 hr_utility.set_location
576 (to_char(p_po_in_tbl(i)), 20);
577 END IF;
578
579 --
580 -- Only add to the out table if the PO has
581 -- assignments assigned to it.
582 --
583 IF asg_exist_for_po
584 (p_po_header_id => p_po_in_tbl(i)) THEN
585 p_po_out_tbl(j) := p_po_in_tbl(i);
586 j := j + 1;
587 END IF;
588
589 END LOOP;
590
591 END IF;
592
593 IF g_debug THEN
594 hr_utility.set_location('Leaving: ' || l_PROC, 30);
595 END IF;
596
597 END asgs_exist_for_pos;
598 --
599 ---------------------------------------------------------------------------|
600 ------------------------< ASGS_EXIST_FOR_PO_LINES >------------------------|
601 ---------------------------------------------------------------------------|
602 --
603 PROCEDURE asgs_exist_for_po_lines
604 (p_po_lines_in_tbl IN g_table_numbers_t
605 ,p_po_lines_out_tbl OUT NOCOPY g_table_numbers_t)
606 IS
607
608 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
609 ||'asgs_exist_for_po_lines';
610 j NUMBER := 1;
611
612 BEGIN
613
614 IF g_debug THEN
615 hr_utility.set_location('Entering: ' || l_PROC, 10);
616 END IF;
617
618 --
619 -- Enumerate through the passed in PL/SQL table.
620 -- The row's index is not used but the row's value is the PO Line
621 -- ID. For each PO Line ID, determine whether any assignments are
622 -- assigned.
623 --
624 IF p_po_lines_in_tbl.COUNT > 0 THEN
625
626 FOR i IN p_po_lines_in_tbl.FIRST..p_po_lines_in_tbl.LAST LOOP
627
628 IF g_debug THEN
629 hr_utility.set_location
630 (to_char(p_po_lines_in_tbl(i)), 20);
631 END IF;
632
633 --
634 -- Only add to the out table if the line has
635 -- assignments assigned to it.
636 --
637 IF asg_exist_for_po_line
638 (p_po_line_id => p_po_lines_in_tbl(i)) THEN
639 p_po_lines_out_tbl(j) := p_po_lines_in_tbl(i);
640 j := j + 1;
641 END IF;
642
643 END LOOP;
644
645 END IF;
646
647 IF g_debug THEN
648 hr_utility.set_location('Leaving: ' || l_PROC, 30);
649 END IF;
650
651 END asgs_exist_for_po_lines;
652 --
653 ---------------------------------------------------------------------------|
654 ------------------------< GET_PERSON_FOR_VENDOR_SITE >---------------------|
655 ---------------------------------------------------------------------------|
656 /*
657 ** For a vendor site, more than one Person records could exist and in
658 ** that case this procedure will not work.
659 **
660 --
661 PROCEDURE get_person_for_vendor_site
662 (p_vendor_site_id IN NUMBER
663 ,p_person_id OUT NOCOPY NUMBER
664 ,p_assignment_id OUT NOCOPY NUMBER
665 ,p_effective_date IN DATE DEFAULT NULL)
666 IS
667
671 l_effective_date DATE;
668 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
669 ||'get_person_for_vendor_site';
670 l_sql VARCHAR2(500);
672
673 BEGIN
674
675 IF g_debug THEN
676 hr_utility.set_location('Entering: ' || l_PROC, 10);
677 hr_utility.set_location(to_char(p_vendor_site_id), 20);
678 END IF;
679
680 IF p_vendor_site_id IS NOT NULL AND asg_po_cols_exist THEN
681 --
682 -- Select the person details, given a site. NDS is used
683 -- so that the procedure compiles when the columns do
684 -- not exist (it is only executed when the columns do
685 -- exist).
686 --
687 l_effective_date := nvl(p_effective_date,TRUNC(sysdate));
688 l_sql :=
689 ' SELECT paaf.person_id '
690 ||' ,paaf.assignment_id '
691 ||' FROM per_all_assignments_f paaf '
692 ||' WHERE paaf.vendor_site_id IS NOT NULL '
693 ||' AND paaf.vendor_site_id = '||p_vendor_site_id
694 ||' AND fnd_date.canonical_to_date('''
695 || fnd_date.date_to_canonical(l_effective_date)||''')'
696 ||' BETWEEN paaf.effective_start_date '
697 ||' AND paaf.effective_end_date ';
698
699 EXECUTE IMMEDIATE l_sql INTO p_person_id, p_assignment_id;
700
701 END IF;
702
703 IF g_debug THEN
704 hr_utility.set_location('Leaving: ' || l_PROC, 30);
705 END IF;
706
707 EXCEPTION
708
709 WHEN no_data_found THEN
710
711 IF g_debug THEN
712 hr_utility.set_location('Leaving: ' || l_PROC, 40);
713 END IF;
714
715 END get_person_for_vendor_site;
716 */
717 --
718 ---------------------------------------------------------------------------|
719 ------------------------< GET_PERSON_ID_FOR_VENDOR_SITE >------------------|
720 ---------------------------------------------------------------------------|
721 /*
722 ** For a vendor site, more than one Person could exist and in
723 ** that case this function will not work.
724 **
725 --
726 FUNCTION get_person_id_for_vendor_site
727 (p_vendor_site_id IN NUMBER) RETURN NUMBER
728 IS
729
730 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
731 ||'get_person_id_for_vendor_site';
732 l_person_id NUMBER;
733 l_dummy NUMBER;
734
735 BEGIN
736
737 IF g_debug THEN
738 hr_utility.set_location('Entering: ' || l_PROC, 10);
739 END IF;
740
741 get_person_for_vendor_site
742 (p_vendor_site_id => p_vendor_site_id
743 ,p_person_id => l_person_id
744 ,p_assignment_id => l_dummy);
745
746 IF g_debug THEN
747 hr_utility.set_location('Leaving: ' || l_PROC, 20);
748 END IF;
749
750 RETURN l_person_id;
751
752 END get_person_id_for_vendor_site;
753 */
754 --
755 ---------------------------------------------------------------------------|
756 ------------------------< GET_ASG_ID_FOR_VENDOR_SITE >---------------------|
757 ---------------------------------------------------------------------------|
758 /*
759 ** For a vendor site, more than one Assignment could exist and in
760 ** that case this function will not work.
761 **
762 --
763 FUNCTION get_asg_id_for_vendor_site
764 (p_vendor_site_id IN NUMBER) RETURN NUMBER
765 IS
766
767 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
768 ||'get_asg_id_for_vendor_site';
769 l_asg_id NUMBER;
770 l_dummy NUMBER;
771
772 BEGIN
773
774 IF g_debug THEN
775 hr_utility.set_location('Entering: ' || l_PROC, 10);
776 END IF;
777
778 get_person_for_vendor_site
779 (p_vendor_site_id => p_vendor_site_id
780 ,p_person_id => l_dummy
781 ,p_assignment_id => l_asg_id);
782
783 IF g_debug THEN
784 hr_utility.set_location('Leaving: ' || l_PROC, 20);
785 END IF;
786
787 RETURN l_asg_id;
788
789 END get_asg_id_for_vendor_site;
790 */
791 --
792 ---------------------------------------------------------------------------|
793 ------------------------< ASG_EXIST_FOR_VENDOR >---------------------------|
794 ---------------------------------------------------------------------------|
795 --
796 FUNCTION asg_exist_for_vendor
797 (p_vendor_id IN NUMBER) RETURN BOOLEAN
798 IS
799
800 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE || 'asg_exist_for_vendor';
801 l_sql VARCHAR2(500);
802 l_found VARCHAR2(5) := g_FALSE;
803
804 BEGIN
805
806 IF g_debug THEN
807 hr_utility.set_location('Entering: ' || l_PROC, 10);
808 hr_utility.set_location(to_char(p_vendor_id), 20);
809 END IF;
810
811 -- Bug 3391399
812 -- Call function asg_vendor_id_exist instead of asg_po_cols_exist
813 IF p_vendor_id IS NOT NULL AND asg_vendor_id_exist THEN
814 --
815 -- Check to see if any assignments exist for this vendor.
819 -- NDS is used to avoid column dependencies.
816 -- Only the first row is returned because the function
817 -- simply needs to know whether an assignments exists
818 -- or it does not.
820 --
821 l_sql :=
822 ' SELECT '''|| g_TRUE || ''''
823 ||' FROM per_all_assignments_f paaf '
824 ||' WHERE paaf.vendor_id IS NOT NULL '
825 ||' AND paaf.vendor_id = '||p_vendor_id
826 ||' AND rownum = 1';
827
828 EXECUTE IMMEDIATE l_sql INTO l_found;
829
830 END IF;
831
832 IF g_debug THEN
833 hr_utility.set_location('Leaving: ' || l_PROC, 30);
834 END IF;
835
836 RETURN (l_found = g_TRUE);
837
838 EXCEPTION
839
840 WHEN no_data_found THEN
841
842 IF g_debug THEN
843 hr_utility.set_location('Leaving: ' || l_PROC, 40);
844 END IF;
845
846 --
847 -- There are no assignments for this vendor.
848 --
849 RETURN FALSE;
850
851 END asg_exist_for_vendor;
852 --
853 ---------------------------------------------------------------------------|
854 ------------------------< ASG_EXIST_FOR_VENDOR_SITE >----------------------|
855 ---------------------------------------------------------------------------|
856 --
857 FUNCTION asg_exist_for_vendor_site
858 (p_vendor_site_id IN NUMBER) RETURN BOOLEAN
859 IS
860
861 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
862 ||'get_asg_exist_for_vendor_site';
863 l_sql VARCHAR2(500);
864 l_found VARCHAR2(5) := g_FALSE;
865
866 BEGIN
867
868 IF g_debug THEN
869 hr_utility.set_location(l_PROC, 10);
870 END IF;
871
872 --
873 -- Check to see if any assignments exist for this site.
874 -- Only the first row is returned because the function
875 -- simply needs to know whether an assignments exists
876 -- or it does not.
877 -- NDS is used to avoid column dependencies.
878 --
879 l_sql :=
880 ' SELECT '''|| g_TRUE || ''''
881 ||' FROM per_all_assignments_f paaf '
882 ||' WHERE paaf. vendor_site_id = '||p_vendor_site_id
883 ||' AND rownum = 1';
884
885 EXECUTE IMMEDIATE l_sql INTO l_found;
886
887 IF g_debug THEN
888 hr_utility.set_location('Leaving: ' || l_PROC, 20);
889 END IF;
890
891 RETURN (l_found = g_TRUE);
892
893 EXCEPTION
894
895 WHEN no_data_found THEN
896
897 IF g_debug THEN
898 hr_utility.set_location('Leaving: ' || l_PROC, 30);
899 END IF;
900
901 --
902 -- There are no assignments for this vendor site.
903 --
904 RETURN FALSE;
905 END asg_exist_for_vendor_site;
906 --
907 ---------------------------------------------------------------------------|
908 ------------------------< ASG_EXIST_FOR_VENDOR_CHAR >----------------------|
909 ---------------------------------------------------------------------------|
910 --
911 FUNCTION asg_exist_for_vendor_char
912 (p_vendor_id IN NUMBER) RETURN VARCHAR2
913 IS
914
915 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
916 ||'asg_exist_for_vendor_char';
917 l_return VARCHAR2(5) := g_FALSE;
918
919 BEGIN
920
921 IF g_debug THEN
922 hr_utility.set_location('Entering: ' || l_PROC, 10);
923 END IF;
924
925 --
926 -- Record the BOOLEAN as a VARCHAR2.
927 --
928 IF asg_exist_for_vendor
929 (p_vendor_id => p_vendor_id) THEN
930 l_return := g_TRUE;
931 END IF;
932
933 IF g_debug THEN
934 hr_utility.set_location('Leaving: ' || l_PROC, 20);
935 END IF;
936
937 RETURN l_return;
938
939 END asg_exist_for_vendor_char;
940 --
941 ---------------------------------------------------------------------------|
942 ------------------------< ASG_EXIST_FOR_VENDOR_SITE_CHAR >-----------------|
943 ---------------------------------------------------------------------------|
944 --
945 FUNCTION asg_exist_for_vendor_site_char
946 (p_vendor_site_id IN NUMBER) RETURN VARCHAR2
947 IS
948
949 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
950 ||'asg_exist_for_vendor_site_char';
951 l_return VARCHAR2(5) := g_FALSE;
952
953 BEGIN
954
955 IF g_debug THEN
956 hr_utility.set_location('Entering: ' || l_PROC, 10);
957 END IF;
958
959 --
960 -- Record the BOOLEAN as a VARCHAR2.
961 --
962 IF asg_exist_for_vendor_site
963 (p_vendor_site_id => p_vendor_site_id) THEN
964 l_return := g_TRUE;
965 END IF;
966
967 IF g_debug THEN
968 hr_utility.set_location('Leaving: ' || l_PROC, 20);
969 END IF;
970
971 RETURN l_return;
972
973 END asg_exist_for_vendor_site_char;
974 --
978 --
975 ---------------------------------------------------------------------------|
976 ------------------------< ASGS_EXIST_FOR_VENDORS >-------------------------|
977 ---------------------------------------------------------------------------|
979 PROCEDURE asgs_exist_for_vendors
980 (p_vendors_in_tbl IN g_table_numbers_t
981 ,p_vendors_out_tbl OUT NOCOPY g_table_numbers_t)
982 IS
983
984 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
985 ||'asgs_exist_for_vendors';
986 j NUMBER := 1;
987
988 BEGIN
989
990 IF g_debug THEN
991 hr_utility.set_location('Entering: ' || l_PROC, 10);
992 END IF;
993
994 --
995 -- Enumerate through the passed in PL/SQL table.
996 -- The row's index is not used but the row's value is the vendor
997 -- ID. For each Vendor ID, determine whether any assignments are
998 -- assigned.
999 --
1000 IF p_vendors_in_tbl.COUNT > 0 THEN
1001
1002 FOR i IN p_vendors_in_tbl.FIRST..p_vendors_in_tbl.LAST LOOP
1003
1004 IF g_debug THEN
1005 hr_utility.set_location
1006 (to_char(p_vendors_in_tbl(i)), 20);
1007 END IF;
1008
1009 --
1010 -- Only add to the out table if the vendor has
1011 -- assignments.
1012 --
1013 IF asg_exist_for_vendor
1014 (p_vendor_id => p_vendors_in_tbl(i)) THEN
1015 p_vendors_out_tbl(j) := p_vendors_in_tbl(i);
1016 j := j + 1;
1017 END IF;
1018
1019 END LOOP;
1020
1021 END IF;
1022
1023 IF g_debug THEN
1024 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1025 END IF;
1026
1027 END asgs_exist_for_vendors;
1028 --
1029 ---------------------------------------------------------------------------|
1030 ------------------------< ASGS_EXIST_FOR_VENDOR_SITES >--------------------|
1031 ---------------------------------------------------------------------------|
1032 --
1033 PROCEDURE asgs_exist_for_vendor_sites
1034 (p_vendor_sites_in_tbl IN g_table_numbers_t
1035 ,p_vendor_sites_out_tbl OUT NOCOPY g_table_numbers_t)
1036 IS
1037
1038 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
1039 ||'asgs_exist_for_vendor_sites';
1040 j NUMBER := 1;
1041
1042 BEGIN
1043
1044 IF g_debug THEN
1045 hr_utility.set_location('Entering: ' || l_PROC, 10);
1046 END IF;
1047
1048 --
1049 -- Enumerate through the passed in PL/SQL table.
1050 -- The row's index is not used but the row's value is the vendor site
1051 -- ID. For each vendor site, determine whether any assignments are
1052 -- assigned.
1053 --
1054 IF p_vendor_sites_in_tbl.COUNT > 0 THEN
1055
1056 FOR i IN p_vendor_sites_in_tbl.FIRST..p_vendor_sites_in_tbl.LAST
1057 LOOP
1058
1059 IF g_debug THEN
1060 hr_utility.set_location
1061 (to_char(p_vendor_sites_in_tbl(i)), 20);
1062 END IF;
1063
1064 --
1065 -- Only add to the out table if the vendor site has
1066 -- assignments.
1067 --
1068 IF asg_exist_for_vendor_site
1069 (p_vendor_site_id => p_vendor_sites_in_tbl(i)) THEN
1070 p_vendor_sites_out_tbl(j) := p_vendor_sites_in_tbl(i);
1071 j := j + 1;
1072 END IF;
1073
1074 END LOOP;
1075
1076 END IF;
1077
1078 IF g_debug THEN
1079 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1080 END IF;
1081
1082 END asgs_exist_for_vendor_sites;
1083 --
1084 ---------------------------------------------------------------------------|
1085 ------------------------< GET_VENDOR_FOR_PRIMARY_ASG >---------------------|
1086 ---------------------------------------------------------------------------|
1087 --
1088 PROCEDURE get_vendor_for_primary_asg
1089 (p_person_id IN NUMBER
1090 ,p_effective_date IN DATE
1091 ,p_vendor_id OUT NOCOPY NUMBER
1092 ,p_vendor_site_id OUT NOCOPY NUMBER)
1093 IS
1094
1095 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
1096 ||'get_vendor_for_primary_asg';
1097 l_sql VARCHAR2(1000);
1098
1099 BEGIN
1100
1101 IF g_debug THEN
1102 hr_utility.set_location('Entering: ' || l_PROC, 10);
1103 END IF;
1104
1105 IF p_person_id IS NOT NULL AND p_effective_date IS NOT NULL
1106 AND asg_po_cols_exist THEN
1107 --
1108 -- Fetch the vendor and vendor site for the person's
1109 -- primary assignment.
1110 --
1111 IF g_debug THEN
1112 hr_utility.set_location(l_PROC, 20);
1113 END IF;
1114
1115 l_sql :=
1116 ' SELECT paaf.vendor_id '
1117 ||' ,paaf.vendor_site_id '
1118 ||' FROM per_all_assignments_f paaf '
1122 || fnd_date.date_to_canonical(p_effective_date)||''')'
1119 ||' WHERE paaf.person_id = '||p_person_id
1120 ||' AND paaf.primary_flag = ''Y'''
1121 ||' AND fnd_date.canonical_to_date('''
1123 ||' BETWEEN paaf.effective_start_date '
1124 ||' AND paaf.effective_end_date '
1125 ||' AND (paaf.vendor_id IS NOT NULL OR '
1126 ||' paaf.vendor_site_id IS NOT NULL) ';
1127
1128 EXECUTE IMMEDIATE l_sql INTO p_vendor_id
1129 ,p_vendor_site_id;
1130
1131 END IF;
1132
1133 IF g_debug THEN
1134 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1135 END IF;
1136
1137 EXCEPTION
1138
1139 WHEN no_data_found THEN
1140
1141 IF g_debug THEN
1142 hr_utility.set_location('Leaving: ' || l_PROC, 40);
1143 END IF;
1144
1145 END get_vendor_for_primary_asg;
1146 --
1147 ---------------------------------------------------------------------------|
1148 ------------------------< GET_VENDOR_FOR_ASG >-----------------------------|
1149 ---------------------------------------------------------------------------|
1150 --
1151 PROCEDURE get_vendor_for_asg
1152 (p_assignment_id IN NUMBER
1153 ,p_effective_date IN DATE
1154 ,p_vendor_id OUT NOCOPY NUMBER
1155 ,p_vendor_site_id OUT NOCOPY NUMBER)
1156 IS
1157
1158 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
1159 ||'get_vendor_for_asg';
1160 l_sql VARCHAR2(1000);
1161
1162 BEGIN
1163
1164 IF g_debug THEN
1165 hr_utility.set_location('Entering: ' || l_PROC, 10);
1166 END IF;
1167
1168 IF p_assignment_id IS NOT NULL AND p_effective_date IS NOT NULL
1169 AND asg_po_cols_exist THEN
1170 --
1171 -- Fetch the vendor and vendor site for the given assignment.
1172 --
1173 IF g_debug THEN
1174 hr_utility.set_location(l_PROC, 20);
1175 END IF;
1176
1177 l_sql :=
1178 ' SELECT paaf.vendor_id '
1179 ||' ,paaf.vendor_site_id '
1180 ||' FROM per_all_assignments_f paaf '
1181 ||' WHERE paaf.assignment_id = '||p_assignment_id
1182 ||' AND fnd_date.canonical_to_date('''
1183 || fnd_date.date_to_canonical(p_effective_date)||''')'
1184 ||' BETWEEN paaf.effective_start_date '
1185 ||' AND paaf.effective_end_date '
1186 ||' AND (paaf.vendor_id IS NOT NULL OR '
1187 ||' paaf.vendor_site_id IS NOT NULL) ';
1188
1189 EXECUTE IMMEDIATE l_sql INTO p_vendor_id
1190 ,p_vendor_site_id;
1191
1192 END IF;
1193
1194 IF g_debug THEN
1195 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1196 END IF;
1197
1198 EXCEPTION
1199
1200 WHEN no_data_found THEN
1201
1202 IF g_debug THEN
1203 hr_utility.set_location('Leaving: ' || l_PROC, 40);
1204 END IF;
1205
1206 END get_vendor_for_asg;
1207 --
1208 ---------------------------------------------------------------------------|
1209 ------------------------< GET_PO_FOR_PRIMARY_ASG >-------------------------|
1210 ---------------------------------------------------------------------------|
1211 --
1212 PROCEDURE get_po_for_primary_asg
1213 (p_person_id IN NUMBER
1214 ,p_effective_date IN DATE
1215 ,p_po_header_id OUT NOCOPY NUMBER
1216 ,p_po_line_id OUT NOCOPY NUMBER)
1217 IS
1218
1219 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
1220 ||'get_po_for_primary_asg';
1221 l_sql VARCHAR2(1000);
1222
1223 BEGIN
1224
1225 IF g_debug THEN
1226 hr_utility.set_location('Entering: ' || l_PROC, 10);
1227 END IF;
1228
1229 IF p_person_id IS NOT NULL AND p_effective_date IS NOT NULL
1230 AND asg_po_cols_exist THEN
1231 --
1232 -- Fetch the PO Header and Line for the person's
1233 -- primary assignment.
1234 --
1235 IF g_debug THEN
1236 hr_utility.set_location(l_PROC, 20);
1237 END IF;
1238
1239 l_sql :=
1240 ' SELECT paaf.po_header_id '
1241 ||' ,paaf.po_line_id '
1242 ||' FROM per_all_assignments_f paaf '
1243 ||' WHERE paaf.person_id = '||p_person_id
1244 ||' AND paaf.primary_flag = ''Y'''
1245 ||' AND fnd_date.canonical_to_date('''
1246 || fnd_date.date_to_canonical(p_effective_date)||''')'
1247 ||' BETWEEN paaf.effective_start_date '
1248 ||' AND paaf.effective_end_date '
1249 ||' AND (paaf.po_header_id IS NOT NULL OR '
1250 ||' paaf.po_line_id IS NOT NULL) ';
1251
1252 EXECUTE IMMEDIATE l_sql INTO p_po_header_id
1253 ,p_po_line_id;
1254
1255 END IF;
1256
1257 IF g_debug THEN
1258 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1259 END IF;
1260
1261 EXCEPTION
1262
1263 WHEN no_data_found THEN
1264
1265 IF g_debug THEN
1266 hr_utility.set_location('Leaving: ' || l_PROC, 40);
1267 END IF;
1268
1269 END get_po_for_primary_asg;
1270 --
1271 ---------------------------------------------------------------------------|
1272 ------------------------< GET_PO_FOR_ASG >---------------------------------|
1273 ---------------------------------------------------------------------------|
1274 --
1275 PROCEDURE get_po_for_asg
1276 (p_assignment_id IN NUMBER
1277 ,p_effective_date IN DATE
1278 ,p_po_header_id OUT NOCOPY NUMBER
1279 ,p_po_line_id OUT NOCOPY NUMBER)
1280 IS
1281
1282 l_PROC CONSTANT VARCHAR2(41) := g_PACKAGE
1283 ||'get_po_for_asg';
1284 l_sql VARCHAR2(1000);
1285
1286 BEGIN
1287
1288 IF g_debug THEN
1289 hr_utility.set_location('Entering: ' || l_PROC, 10);
1290 END IF;
1291
1292 IF p_assignment_id IS NOT NULL AND p_effective_date IS NOT NULL
1293 AND asg_po_cols_exist THEN
1294 --
1295 -- Fetch the vendor and vendor site for the given assignment.
1296 --
1297 IF g_debug THEN
1298 hr_utility.set_location(l_PROC, 20);
1299 END IF;
1300
1301 l_sql :=
1302 ' SELECT paaf.po_header_id '
1303 ||' ,paaf.po_line_id '
1304 ||' FROM per_all_assignments_f paaf '
1305 ||' WHERE paaf.assignment_id = '||p_assignment_id
1306 ||' AND fnd_date.canonical_to_date('''
1307 || fnd_date.date_to_canonical(p_effective_date)||''')'
1308 ||' BETWEEN paaf.effective_start_date '
1309 ||' AND paaf.effective_end_date '
1310 ||' AND (paaf.po_header_id IS NOT NULL OR '
1311 ||' paaf.po_line_id IS NOT NULL) ';
1312
1313 EXECUTE IMMEDIATE l_sql INTO p_po_header_id
1314 ,p_po_line_id;
1315
1316 END IF;
1317
1318 IF g_debug THEN
1319 hr_utility.set_location('Leaving: ' || l_PROC, 30);
1320 END IF;
1321
1322 EXCEPTION
1323
1324 WHEN no_data_found THEN
1325
1326 IF g_debug THEN
1327 hr_utility.set_location('Leaving: ' || l_PROC, 40);
1328 END IF;
1329
1330 END get_po_for_asg;
1331 --
1332 ---------------------------------------------------------------------------|
1333 ------------------------< GET_URL_PLACE_CWK >------------------------------|
1334 ---------------------------------------------------------------------------|
1335 --
1336 -- Given a po_line_id this procedure will return the url destination which
1337 -- will be rendered in PO notification.
1338 -- On launching this url the user will be taken through the CWK Placement
1339 -- flow of pages.
1340 --
1341 PROCEDURE get_url_place_cwk
1342 (p_po_line_id IN NUMBER
1343 ,p_destination OUT NOCOPY VARCHAR2)
1344 IS
1345 cursor get_function_params (p_function_name fnd_form_functions.function_name%TYPE) is
1346 select web_html_call || '&' || parameters url from fnd_form_functions
1347 where function_name = p_function_name;
1351 l_self_service_licensed varchar2(3);
1348 l_function_name fnd_form_functions.function_name%TYPE default 'HR_CWKPLACE_MGR_SS';
1349 l_url varchar2(1000);
1350 l_cutom_cwk_plc_func fnd_form_functions.function_name%TYPE;
1352
1353 BEGIN
1354
1355 p_destination := null;
1356 --Instead of profile for customized function we have to think about some other
1357 --mechanism to store the customized function --Satish.
1358 --l_cutom_cwk_plc_func := fnd_profile.value('HR_CUST_CWK_PLACEMENT');
1359 --if(l_cutom_cwk_plc_func is not null)
1360 --then
1361 -- l_function_name := l_cutom_cwk_plc_func;
1362 --end if;
1363
1364 open get_function_params(l_function_name);
1365 fetch get_function_params into l_url;
1366 if( l_url is not null )
1367 then
1368 p_destination := 'JSP:/OA_HTML/' || l_url || '&' || 'pNtfLineId=' || p_po_line_id ;
1369 end if;
1370 --
1371 END get_url_place_cwk;
1372 --
1373 --
1374 ---------------------------------------------------------------------------|
1375 ---------------------< PACKAGE INITIALISATION >----------------------------|
1376 ---------------------------------------------------------------------------|
1377 --
1378 BEGIN
1379 --
1380 -- Initialise package variables.
1381 --
1382 initialise_globals;
1383
1384 END hr_po_info;
[Home] [Help]