1 PACKAGE BODY OKE_UTILS AS
2 /* $Header: OKEUTILB.pls 120.3 2006/01/17 12:13:35 ausmani noship $ */
3
4 --
5 -- Private Global Variables
6 --
7 G_Emp_ID per_all_people_f.person_id%type := NULL;
8 G_Emp_Name per_all_people_f.full_name%type := NULL;
9 G_User_ID fnd_user.user_id%type := NULL;
10 G_Yes VARCHAR2(80) := NULL;
11 G_No VARCHAR2(80) := NULL;
12
13 G_K_Hdr_ID_Curr NUMBER := NULL;
14 G_Fmt_Length NUMBER := NULL;
15 G_Fmt_Mask VARCHAR2(80) := NULL;
16
17 --
18 -- This is a body global variable storing the value of
19 -- USERENV('LANG'). The value is cached into this variable so that
20 -- calling functions do not have to hit the database to determine this
21 -- value.
22 --
23 G_Userenv_Lang fnd_languages.language_code%TYPE;
24
25 --
26 -- Multi-Org Security Globals
27 --
28 G_Access VARCHAR2(1) := NULL;
29
30
31
32 --
33 -- Name : Curr_Emp_ID
34 -- Pre-reqs : FND_GLOBAL.INITIALIZE
35 -- Function : This function returns the employee ID derived from
36 -- the current user
37 --
38 --
39 -- Parameters :
40 -- IN : None
41 -- OUT : None
42 --
43 -- Returns : NUMBER
44 --
45
46 FUNCTION Curr_Emp_ID
47 RETURN NUMBER IS
48
49 CURSOR csr ( c_user_id number ) IS
50 SELECT employee_id
51 FROM fnd_user
52 WHERE user_id = c_user_id;
53
54 BEGIN
55 --
56 -- Result is cached into a global variable to speed up repeated
57 -- lookups. In the extreme rare case when the USER_ID changed
58 -- midstream, the last used USER_ID used to retrieve the
59 -- employee ID is also cached to check for mismatch
60 --
61 IF ( G_Emp_ID IS NULL
62 OR G_User_ID <> FND_GLOBAL.User_ID ) THEN
63 /*
64 OPEN csr ( FND_GLOBAL.user_id );
65 FETCH csr INTO G_Emp_ID;
66 CLOSE csr;
67 */
68 G_Emp_ID := FND_GLOBAL.Employee_ID;
69 G_User_ID := FND_GLOBAL.User_ID;
70
71 END IF;
72
73 RETURN ( G_Emp_ID );
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 RETURN ( TO_NUMBER(NULL) );
78
79 END Curr_Emp_ID;
80
81 --
82 -- Name : Curr_Emp_Name
83 -- Pre-reqs : FND_GLOBAL.INITIALIZE
84 -- Function : This function returns the employee name derived from
85 -- the current user
86 --
87 --
88 -- Parameters :
89 -- IN : None
90 -- OUT : None
91 --
92 -- Returns : VARCHAR2
93 --
94
95 FUNCTION Curr_Emp_Name
96 RETURN VARCHAR2 IS
97
98 CURSOR csr ( c_person_id number ) IS
99 SELECT full_name
100 FROM per_all_people_f
101 WHERE person_id = c_person_id;
102
103 BEGIN
104 --
105 -- Result is cached into a global variable to speed up repeated
106 -- lookups. In the extreme rare case when the USER_ID changed
107 -- midstream, the last used USER_ID used to retrieve the
108 -- employee name is also cached to check for mismatch
109 --
110 IF ( G_Emp_Name IS NULL
111 OR G_User_ID <> FND_GLOBAL.User_ID ) THEN
112
113 OPEN csr ( Curr_Emp_ID );
114 FETCH csr INTO G_Emp_Name;
115 CLOSE csr;
116
117 G_User_ID := FND_GLOBAL.User_ID;
118
119 END IF;
120
121 RETURN ( G_Emp_Name );
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 RETURN ( NULL );
126
127 END Curr_Emp_Name;
128
129
130 --
131 -- Name : Yes_No / Sys_Yes_No
132 -- Pre-reqs : None
133 -- Function : This function returns the yes/no string based on the
134 -- lookups YES_NO and SYS_YES_NO.
135 --
136 --
137 -- Parameters :
138 -- IN : None
139 -- OUT : None
140 --
141 -- Returns : VARCHAR2
142 --
143 -- Note : The cached values are shared between Yes_No and
144 -- Sys_Yes_No as the text is extremely unlikely to
145 -- differ between the two lookups.
146 --
147
148 FUNCTION Yes_No
149 ( X_Lookup_Code IN VARCHAR2
150 ) return varchar2 IS
151
152 CURSOR c IS
153 SELECT meaning
154 FROM fnd_lookups
155 WHERE lookup_type = 'YES_NO'
156 AND lookup_code = X_Lookup_Code;
157
158 BEGIN
159
160 IF ( X_Lookup_Code = 'Y' ) THEN
161 IF ( G_Yes IS NULL ) THEN
162 OPEN c;
163 FETCH c INTO G_Yes;
164 CLOSE c;
165 END IF;
166 RETURN ( G_Yes );
167 ELSIF ( X_Lookup_Code = 'N' ) THEN
168 IF ( G_No IS NULL ) THEN
169 OPEN c;
170 FETCH c INTO G_No;
171 CLOSE c;
172 END IF;
173 RETURN ( G_No );
174 ELSE
175 RETURN ( NULL );
176 END IF;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 IF ( c%ISOPEN ) THEN
181 CLOSE c;
182 END IF;
183 RETURN ( NULL );
184
185 END Yes_No;
186
187
188 FUNCTION Sys_Yes_No
189 ( X_Lookup_Code IN NUMBER
190 ) return varchar2 IS
191
192 CURSOR c IS
193 SELECT meaning
194 FROM mfg_lookups
195 WHERE lookup_type = 'SYS_YES_NO'
196 AND lookup_code = X_Lookup_Code;
197
198 BEGIN
199
200 IF ( X_Lookup_Code = 'Y' ) THEN
201 IF ( G_Yes IS NULL ) THEN
202 OPEN c;
203 FETCH c INTO G_Yes;
204 CLOSE c;
205 END IF;
206 RETURN ( G_Yes );
207 ELSIF ( X_Lookup_Code = 'N' ) THEN
208 IF ( G_No IS NULL ) THEN
209 OPEN c;
210 FETCH c INTO G_No;
211 CLOSE c;
212 END IF;
213 RETURN ( G_No );
214 ELSE
215 RETURN ( NULL );
216 END IF;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 IF ( c%ISOPEN ) THEN
221 CLOSE c;
222 END IF;
223 RETURN ( NULL );
224
225 END Sys_Yes_No;
226
227
228 --
229 -- Name : Chg_Request_Num
230 -- Pre-reqs : None
231 -- Function : This function returns the related Change Request
232 -- Number and Change Status for the given contract
233 -- either for the current version or a specific
234 -- major version.
235 --
236 --
237 -- Parameters :
238 -- IN : X_K_Header_ID NUMBER
239 -- X_Major_Version NUMBER
240 -- X_History_Use VARCHAR2
241 -- X_Curr_Indicator VARCHAR2
242 -- X_Current_Only VARCHAR2
243 -- OUT : X_Change_Request VARCHAR2
244 -- X_Change_Status VARCHAR2
245 --
246
247 PROCEDURE Chg_Request_Num
248 ( X_K_Header_ID IN NUMBER
249 , X_Major_Version IN NUMBER
250 , X_Current_Only IN VARCHAR2
251 , X_Curr_Indicator IN VARCHAR2
252 , X_Change_Request OUT NOCOPY VARCHAR2
253 , X_Change_Status OUT NOCOPY VARCHAR2
254 , X_History_Use IN VARCHAR2
255 ) IS
256
257 BEGIN
258 --
259 -- Logic moved to OKE_CHG_REQ_UTILS.GET_CHG_REQUEST
260 --
261 OKE_CHG_REQ_UTILS.Get_Chg_Request
262 ( X_K_Header_ID
263 , X_Major_Version
264 , X_Change_Request
265 , X_Change_Status
266 , X_History_Use
267 );
268
269 END;
270
271
272 --
273 -- Name : Item_Number
274 -- Pre-reqs : None
275 -- Function : This function returns the item number for a given
276 -- inventory organization and item ID.
277 --
278 --
279 -- Parameters :
280 -- IN : X_Inventory_Org_ID NUMBER
281 -- X_Item_ID NUMBER
282 -- OUT : None
283 --
284 -- Returns : VARCHAR2
285 --
286
287 FUNCTION Item_Number
288 ( X_Inventory_Org_ID IN NUMBER
289 , X_Item_ID IN NUMBER
290 ) RETURN VARCHAR2 IS
291
292 CURSOR ItemNum IS
293 SELECT Item_Number
294 FROM mtl_item_flexfields
295 WHERE organization_id = X_Inventory_Org_ID
296 AND inventory_item_id = X_Item_ID;
297
298 L_Item_Number VARCHAR2(2000);
299
300 BEGIN
301
302 IF ( X_Inventory_Org_ID IS NULL OR X_Item_ID IS NULL ) THEN
303 RETURN ( NULL );
304 END IF;
305
306 OPEN ItemNum;
307 FETCH ItemNum INTO L_Item_Number;
308 CLOSE ItemNum;
309
310 RETURN ( L_Item_Number );
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 RETURN ( NULL );
315 END Item_Number;
316
317
318 --
319 -- Name : Item_Description
320 -- Pre-reqs : None
321 -- Function : This function returns the item description for a given
322 -- inventory organization and item ID.
323 --
324 --
325 -- Parameters :
326 -- IN : X_Inventory_Org_ID NUMBER
327 -- X_Item_ID NUMBER
328 -- OUT : None
329 --
330 -- Returns : VARCHAR2
331 --
332
333 FUNCTION Item_Description
334 ( X_Inventory_Org_ID IN NUMBER
335 , X_Item_ID IN NUMBER
336 ) RETURN VARCHAR2 IS
337
338 CURSOR ItemDesc IS
339 SELECT Description
340 FROM mtl_system_items
341 WHERE organization_id = X_Inventory_Org_ID
342 AND inventory_item_id = X_Item_ID;
343
344 L_Item_Description VARCHAR2(2000);
345
346 BEGIN
347
348 IF ( X_Inventory_Org_ID IS NULL OR X_Item_ID IS NULL ) THEN
349 RETURN ( NULL );
350 END IF;
351
352 OPEN ItemDesc;
353 FETCH ItemDesc INTO L_Item_Description;
354 CLOSE ItemDesc;
355
356 RETURN ( L_Item_Description );
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 RETURN ( NULL );
361 END Item_Description;
362
363
364 --
365 -- Name : Check_Unique
366 -- Pre-reqs : None
367 -- Function : This function checks uniqueness of a column
368 -- value in the given table.
369 --
370 -- Parameters :
371 -- IN : X_K_Header_ID NUMBER
372 -- X_Major_Version NUMBER
373 -- X_Current_Only VARCHAR2 DEFAULT Y
374 -- X_Curr_Indicator VARCHAR2 DEFAULT N
375 -- OUT : None
376 --
377 -- Returns : BOOLEAN
378 --
379
380 FUNCTION Check_Unique
381 ( X_Table_Name IN VARCHAR2
382 , X_Column_Name IN VARCHAR2
383 , X_Column_Value IN VARCHAR2
384 , X_ROWID_Column IN VARCHAR2
385 , X_Row_ID IN VARCHAR2
386 , X_Translated IN VARCHAR2
387 ) RETURN BOOLEAN IS
388
389 TYPE chk_unq_rc IS REF CURSOR;
390
391 c chk_unq_rc;
392 stmt VARCHAR2(2000);
393 dummy NUMBER := 0;
394
395 BEGIN
396
397 stmt := 'SELECT 1 FROM ' || X_Table_Name ||
398 ' WHERE ' || X_Column_Name || ' = :column_value';
399
400 IF ( X_Row_ID IS NOT NULL ) THEN
401 stmt := stmt || ' AND ' || X_ROWID_Column || ' <> :row_id';
402 END IF;
403
404 IF ( X_Translated = 'Y' ) THEN
405 stmt := stmt || ' AND LANGUAGE = USERENV(''LANG'')';
406 END IF;
407
408 --
409 -- Check for existing records using NDS
410 --
411 IF ( X_Row_ID IS NOT NULL ) THEN
412 OPEN c FOR stmt USING X_Column_Value , X_Row_ID;
413 ELSE
414 OPEN c FOR stmt USING X_Column_Value;
415 END IF;
416 FETCH c INTO dummy;
417
418 IF ( c%notfound ) THEN
419 CLOSE c;
420 RETURN ( TRUE );
421 END IF;
422
423 RETURN ( FALSE );
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 CLOSE c;
428 RAISE;
429
430 END Check_Unique;
431
432
433
434 -- Function get_location_description
435 -- Purpose:
436 -- returns location name by location_id
437 --
438 --
439 --
440 FUNCTION get_location_description(id NUMBER)
441 RETURN VARCHAR2 IS
442
443 l_return_val VARCHAR2(240);
444
445 CURSOR c_hr IS
446 SELECT nvl(description,'')
447 FROM hr_locations_all
448 WHERE location_id=id;
449
450 CURSOR c_hz IS
451 SELECT substr(address1,1,240)
452 FROM hz_locations
453 WHERE location_id=id;
454
455 CURSOR c_both IS
456 SELECT description
457 FROM hr_locations
458 WHERE location_id=id;
459
460 BEGIN
461
462 OPEN c_hr;
463 FETCH c_hr INTO l_return_val;
464 IF c_hr%NOTFOUND THEN
465 OPEN c_hz;
466 FETCH c_hz INTO l_return_val;
467 IF c_hz%NOTFOUND THEN
468 l_return_val:='ERROR-NO SUCH LOCATION_ID';
469 END IF;
470 CLOSE c_hz;
471 END IF;
472 CLOSE c_hr;
473 RETURN l_return_val;
474
475 END get_location_description;
476
477
478
479
480 -- Function get_term_values
481 -- Purpose:
482 -- to be used by view definition oke_k_terms_v only
483 --
484 --
485 --
486 FUNCTION get_term_values(p_term_code VARCHAR2, p_term_value_pk1 VARCHAR2,
487 p_term_value_pk2 VARCHAR2,p_call_option VARCHAR2 )
488 RETURN VARCHAR2 IS
489
490 v_chr1 varchar2(240);
491 v_chr2 varchar2(240);
492 v_date1 date;
493 v_date2 date;
494
495 begin
496
497 if p_term_code = 'AP_PAYMENT_TERMS' then
498
499 SELECT NAME , DESCRIPTION , START_DATE_ACTIVE , END_DATE_ACTIVE
500 INTO v_chr1 , v_chr2 , v_date1 , v_date2
501 FROM AP_TERMS
502 WHERE TERM_ID = to_number(p_term_value_pk1);
503
504 elsif p_term_code='IB_SHIPPING_METHOD' then
505
506 SELECT DESCRIPTION , DESCRIPTION , TO_DATE(NULL) , DISABLE_DATE
507 into v_chr1 , v_chr2 , v_date1 , v_date2
508 FROM ORG_FREIGHT
509 WHERE ORGANIZATION_ID = to_number(p_term_value_pk1)
510 AND FREIGHT_CODE = p_term_value_pk2;
511
512 elsif p_term_code='RA_PAYMENT_TERMS' then
513
514 SELECT NAME , DESCRIPTION , START_DATE_ACTIVE , END_DATE_ACTIVE
515 INTO v_chr1 , v_chr2 , v_date1 , v_date2
516 FROM RA_TERMS
517 WHERE TERM_ID = to_number(p_term_value_pk1);
518
519 else
520
521 SELECT LU.MEANING , LU.DESCRIPTION , LU.START_DATE_ACTIVE , LU.END_DATE_ACTIVE
522 INTO v_chr1 , v_chr2 , v_date1 , v_date2
523 FROM FND_LOOKUP_VALUES_VL LU
524 , OKE_TERMS_B T
525 WHERE T.TERM_CODE = p_term_code
526 AND LU.VIEW_APPLICATION_ID = T.VIEW_APPLICATION_ID
527 AND LU.LOOKUP_TYPE = T.LOOKUP_TYPE
528 AND LU.LOOKUP_CODE = p_term_value_pk1;
529
530 end if;
531
532 if p_call_option='MEANING' then
533 return v_chr1;
534 elsif p_call_option='DESCRIPTION' then
535 return v_chr2;
536 elsif p_call_option='START_DATE_ACTIVE' then
537 return v_date1;
538 elsif p_call_option='END_DATE_ACTIVE' then
539 return v_date2;
540 else return 'ERROR';
541 end if;
542
543
544 END get_term_values;
545
546
547 -- Function get_ob_terms
548 -- Purpose: See specs in OKEUTILS.pls
549 --
550
551 FUNCTION get_term_value (p_id NUMBER,p_term_code VARCHAR2) RETURN VARCHAR2 IS
552 l_term_value varchar2(80) := null;
553 cursor c_term_value(id number,code varchar2) is
554 select term_value_pk1
555 from oke_k_terms
556 where k_header_id = p_id
557 and term_code = p_term_code
558 and rownum=1;
559 BEGIN
560 if p_term_code is not null then
561 open c_term_value(p_id,p_term_code);
562 fetch c_term_value into l_term_value;
563 close c_term_value;
564 end if;
565
566 return l_term_value;
567 Exception
568 When others then
569 if c_term_value%ISOPEN then
570 close c_term_value;
571 end if;
572 return null;
573 END get_term_value;
574
575
576
577 -- Function get_userenv_lang
578 -- Purpose: See specs in OKEUTILS.pls
579 -- Briefly: This caches the value of userenv('lang') so
580 -- that subsequent calls do not result in a database hit
581 --
582 --
583 FUNCTION get_userenv_lang RETURN VARCHAR2 IS
584
585 BEGIN
586
587 -- Determine if this was determined before by examining the global
588 -- variable g_userenv_lang. If this is NOT null, return the value,
589 -- otherwise, determine the value, populate the global variable and
590 -- return the value.
591
592 IF g_userenv_lang IS NULL
593 THEN
594 g_userenv_lang := USERENV('LANG');
595 END IF;
596
597 RETURN g_userenv_lang;
598
599 END get_userenv_lang;
600
601
602 --
603 -- Name : Get_K_Curr_Fmt_Mask
604 -- Pre-reqs : None
605 -- Function : This function returns the format mask for the
606 -- currency of the given contract. This is used in
607 -- the flowdown view to speed up format time as the
608 -- return value is cached.
609 --
610 -- Parameters :
611 -- IN : X_K_Header_ID NUMBER
612 -- X_Field_Length NUMBER
613 -- OUT : None
614 --
615 -- Returns : VARCHAR2
616 --
617
618 FUNCTION Get_K_Curr_Fmt_Mask
619 ( X_K_Header_ID IN NUMBER
620 , X_Field_Length IN NUMBER
621 ) RETURN VARCHAR2 IS
622
623 CURSOR csr ( C_K_Header_ID NUMBER
624 , C_Field_Length NUMBER ) IS
625 SELECT FND_CURRENCY_CACHE.Get_Format_Mask
626 ( Currency_Code , C_Field_Length )
627 FROM okc_k_headers_b
628 WHERE id = C_K_Header_ID;
629
630 BEGIN
631 --
632 -- Result is cached into a global variable to speed up repeated
633 -- lookups.
634 --
635 IF ( G_K_Hdr_ID_Curr IS NULL
636 OR G_Fmt_Mask IS NULL
637 OR G_K_Hdr_ID_Curr <> X_K_Header_ID
638 OR G_Fmt_Length <> X_Field_Length ) THEN
639
640 OPEN csr ( X_K_Header_ID , X_Field_Length );
641 FETCH csr INTO G_Fmt_Mask;
642 CLOSE csr;
643
644 G_K_Hdr_ID_Curr := X_K_Header_ID;
645 G_Fmt_Length := X_Field_Length;
646
647 END IF;
648
649 RETURN ( G_Fmt_Mask );
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 RETURN ( FND_CURRENCY_CACHE.GET_FORMAT_MASK( 'USD' , 38 ) );
654
655 END Get_K_Curr_Fmt_Mask;
656
657
658 -- -------------------------------------------------------------------
659 -- Multi-Org Security
660 -- -------------------------------------------------------------------
661 PROCEDURE Set_Org_Context
662 ( X_Org_ID NUMBER
663 , X_Inv_Org_ID NUMBER
664 ) IS
665
666 is_multi_org VARCHAR2(1);
667
668 BEGIN
669
670 -- select nvl(multi_org_flag , 'N')
671 -- into is_multi_org
672 -- from fnd_product_groups
673 -- where rownum = 1;
674 --
675 -- if ( is_multi_org = 'Y' and X_Org_ID <> -99 ) then
676 -- FND_CLIENT_INFO.Set_Org_Context(X_Org_ID);
677 -- end if;
678 OKC_CONTEXT.Set_OKC_Org_Context(X_Org_ID , X_Inv_Org_ID);
679
680 END Set_Org_Context;
681
682
683 FUNCTION Org_ID
684 RETURN NUMBER IS
685 BEGIN
686
687 -- RETURN nvl( to_number(rtrim(substr( userenv('CLIENT_INFO') , 1 , 10 ))) , -99 );
688
689 RETURN nvl(mo_global.get_current_org_id,-99);
690
691 END Org_ID;
692
693
694 FUNCTION Cross_Org_Access
695 RETURN VARCHAR2 IS
696 BEGIN
697
698 IF ( G_Access IS NULL ) THEN
699 G_Access := nvl( fnd_profile.value('OKE_CROSS_ORG_ACCESS') , 'Y' );
700 END IF;
701 RETURN ( G_Access );
702
703 END Cross_Org_Access;
704
705
706 -- -------------------------------------------------------------------
707 -- PL/SQL Server Debugger
708 -- -------------------------------------------------------------------
709
710 --
711 -- All functions have been moved to OKE_DEBUG. Procedures retained
712 -- for compilation dependencies only.
713 --
714 PROCEDURE Enable_Debug IS
715 BEGIN
716 NULL;
717 END Enable_Debug;
718
719 PROCEDURE Disable_Debug IS
720 BEGIN
721 NULL;
722 END Disable_Debug;
723
724 FUNCTION Debug_Mode
725 RETURN VARCHAR2 IS
726 BEGIN
727 RETURN ( 'N' );
728 END Debug_Mode;
729
730 PROCEDURE Debug ( text IN VARCHAR2 ) IS
731 BEGIN
732 NULL;
733 END Debug;
734
735 FUNCTION IS_VALID_DATE_RANGE (P_DATE_FROM IN DATE
736 ,P_DATE_TO IN DATE
737 ,P_PROJECT_ID IN NUMBER
738 ) return number
739 IS
740 d_proj_start_date date;
741 d_proj_end_date date;
742 n_valid number :=1;
743 n_invalid number :=0;
744
745 BEGIN
746
747 IF P_DATE_FROM IS NULL OR P_DATE_TO IS NULL OR p_project_id IS NULL THEN
748 RETURN n_invalid;
749 END IF;
750
751 BEGIN
752 select ppa.start_date
753 ,ppa.completion_date
754 into d_proj_start_date
755 ,d_proj_end_date
756 from pa_projects_all ppa
757 where ppa.project_id =p_project_id
758 ;
759 EXCEPTION
760 when no_data_found then
761 RETURN n_invalid;
762 when too_many_rows then
763 RETURN n_invalid;
764 when others then
765 RETURN n_invalid;
766 END;
767
768 IF (P_DATE_FROM >= nvl(d_proj_start_date,P_DATE_FROM)) AND (P_DATE_TO <= nvl(d_proj_end_date,P_DATE_TO)) THEN
769 RETURN n_valid;
770 ELSE
771 RETURN n_invalid;
772 END IF;
773
774 END IS_VALID_DATE_RANGE;
775
776
777
778 FUNCTION Retrieve_Article_Text (P_id IN NUMBER
779 ,P_position IN NUMBER
780 ,P_next_pos OUT NOCOPY NUMBER)return VARCHAR2
781
782 IS
783 l_article_text CLOB;
784 l_article_length NUMBER;
785 l_append_text VARCHAR2(32000);
786 l_position NUMBER;
787 l_read_length NUMBER := 10000;
788
789
790 CURSOR c_text IS
791 select text
792 from okc_k_articles_v where id = P_id;
793
794 BEGIN
795
796 OPEN c_text;
797 FETCH c_text INTO l_article_text;
798 CLOSE c_text;
799
800 l_article_length := dbms_lob.getlength(l_article_text);
801 l_position := p_position;
802
803 if l_article_length >= (l_position) then
804
805 dbms_lob.read(l_article_text,l_read_length,l_position,l_append_text);
806 p_next_pos := l_position + l_read_length;
807 end if;
808
809 return l_append_text;
810
811 END;
812
813
814 FUNCTION Retrieve_WF_Role_Name (P_header_id IN NUMBER,
815 P_role_id IN NUMBER)
816 return VARCHAR2 is
817
818 Cursor Get_WF_User (person_id NUMBER)
819 IS
820 SELECT r.name
821 FROM wf_roles r
822 WHERE r.orig_system ='PER'
823 AND r.orig_system_id = person_id;
824
825 Cursor Get_Header_Assignments
826 IS
827 SELECT Resource_source_id
828 FROM PA_Project_Parties
829 WHERE Resource_type_id = 101 -- employees only
830 AND Object_type = 'OKE_K_HEADERS' -- header assignments only
831 AND Object_id = P_header_id -- for the requested header id only
832 AND Project_role_id = P_role_id -- for the requested role
833 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
834 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active));
835 Cursor Get_Program_Assignments
836 IS
837 SELECT Resource_source_id
838 FROM PA_Project_Parties pr, OKE_K_Headers
839 WHERE Resource_type_id = 101 -- employees only
840 AND Object_type = 'OKE_PROGRAMS' -- program assignments only
841 AND Object_id = program_id -- for the program id
842 AND K_Header_id = P_header_id -- related to the requested header id
843 AND Project_role_id = P_role_id -- for the requested role
844 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
845 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active))
846 and not exists ( -- Same Person shouldnot exist at contract level in any other role
847 SELECT 'x'
848 FROM PA_Project_Parties pr1
849 WHERE Resource_type_id = 101
850 AND Object_type = 'OKE_K_HEADERS'
851 AND Object_id = P_header_id
852 AnD pr.resource_id = pr1.resource_id
853 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
854 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active)))
855 order by pr.creation_date;
856
857 Cursor Get_Site_Assignments
858 IS
859 SELECT Resource_source_id
860 FROM PA_Project_Parties pr
861 WHERE Resource_type_id = 101 -- employees only
862 AND Object_type = 'OKE_PROGRAMS' -- site assignments only
863 AND Object_id = 0 -- for the program id is 0 for site
864 AND Project_role_id = P_role_id -- for the requested role
865 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
866 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active))
867 and not exists ( -- Same Person shouldnot exist at contract level in any other role
868 SELECT 'x'
869 FROM PA_Project_Parties pr1
870 WHERE Resource_type_id = 101
871 AND Object_type = 'OKE_K_HEADERS'
872 AND Object_id = P_header_id
873 AnD pr.resource_id = pr1.resource_id
874 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
875 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active)))
876 and not exists ( -- Same Person shouldnot exist at program level in any other role
877 SELECT 'x'
878 FROM PA_Project_Parties pr2, OKE_K_Headers
879 WHERE Resource_type_id = 101
880 AND Object_type = 'OKE_PROGRAMS'
881 AND Object_id = program_id
882 AND K_Header_id = P_header_id
883 AND pr.resource_id = pr2.resource_id
884 AND Trunc(SYSDATE) >= Trunc(Start_Date_Active)
885 AND (End_Date_Active IS NULL OR Trunc(SYSDATE) <= Trunc(End_Date_Active)))
886 order by pr.creation_date;
887
888 l_person_id NUMBER := -1;
889 l_wf_user VARCHAR2(200);
890
891 BEGIN
892
893 OPEN Get_Header_Assignments;
894 FETCH Get_Header_Assignments INTO l_person_id;
895 IF Get_Header_Assignments%NOTFOUND THEN
896 OPEN Get_Program_Assignments;
897 FETCH Get_Program_Assignments INTO l_person_id;
898 IF Get_Program_Assignments%NOTFOUND THEN
899 OPEN Get_Site_Assignments;
900 FETCH Get_Site_Assignments INTO l_person_id;
901 IF Get_Site_Assignments%NOTFOUND THEN
902 CLOSE Get_Site_Assignments;
903 CLOSE Get_Program_Assignments;
904 CLOSE Get_Header_Assignments;
905 return NULL;
906 END IF;
907 CLOSE Get_Site_Assignments;
908 END IF;
909 CLOSE Get_Program_Assignments;
910 END IF;
911 CLOSE Get_Header_Assignments;
912
913 OPEN Get_WF_User(l_person_id);
914 FETCH Get_WF_User INTO l_wf_user;
915 IF Get_WF_User%NOTFOUND THEN
916 CLOSE Get_WF_User;
917 return NULL;
918 END IF;
919 CLOSE Get_WF_User;
920
921 return l_wf_user;
922
923 END Retrieve_WF_Role_Name;
924
925 PROCEDURE Set_Multi_org_Access IS
926 BEGIN
927 If nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'),'N')='N'
928 and nvl( fnd_profile.value('OKE_CROSS_ORG_ACCESS') , 'Y' ) ='Y' then
929 mo_global.set_policy_context('B', NULL);
930 End if;
931 END Set_Multi_org_Access;
932
933
934
935 END OKE_UTILS;
[Home] [Help]