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