[Home] [Help]
PACKAGE BODY: APPS.BOMPRFIL
Source
1 package body BOMPRFIL as
2 /* $Header: BOMRFILB.pls 115.1 99/07/16 05:16:02 porting ship $ */
3 procedure bom_pr_get_profile(
4 appl_short_name IN VARCHAR2,
5 profile_name IN VARCHAR2,
6 user_id IN NUMBER,
7 resp_appl_id IN NUMBER,
8 resp_id IN NUMBER,
9 profile_value OUT VARCHAR2,
10 return_code OUT NUMBER,
11 return_message OUT VARCHAR2)
12 IS
13 l_pr_option_id NUMBER;
14 l_pr_level_id NUMBER;
15 l_pr_level_value NUMBER;
16 l_pr_value VARCHAR2(10);
17
18 no_profile_found EXCEPTION;
19 CURSOR get_profile_cursor(c_appl_short_name IN VARCHAR2,
20 c_profile_name IN VARCHAR2,
21 c_user_id IN NUMBER,
22 c_resp_appl_id IN NUMBER,
23 c_resp_id IN NUMBER) IS
24 SELECT opt.profile_option_id,
25 level_id,
26 level_value,
27 substrb(profile_option_value,1,10)
28 FROM fnd_profile_options opt,
29 fnd_application appl,
30 fnd_profile_option_values val
31 WHERE opt.application_id = val.application_id
32 AND opt.profile_option_id = val.profile_option_id
33 AND opt.application_id = appl.application_id
34 AND appl.application_short_name = c_appl_short_name
35 AND opt.profile_option_name = c_profile_name
36 AND (
37 (val.level_id = 10001) /* Site */
38 OR
39 (val.level_id = 10002 /* Application */
40 and val.level_value = c_resp_appl_id
41
42 )
43 OR
44 (val.level_id = 10003 /* Responsibility */
45 and val.level_value_application_id = c_resp_appl_id
46 and val.level_value = c_resp_id
47 )
48 OR
49 (val.level_id = 10004 /* User */
50 and val.level_value = c_user_id
51 )
52 )
53 ORDER BY opt.profile_option_id, level_id desc;
54
55 BEGIN
56 /* Set appropriate defaults */
57
58 profile_value := NULL;
59
60 OPEN get_profile_cursor(appl_short_name,profile_name,user_id,resp_appl_id,
61 resp_id);
62
63 FETCH get_profile_cursor INTO l_pr_option_id,
64 l_pr_level_id,
65 l_pr_level_value,
66 l_pr_value;
67 IF get_profile_cursor%NOTFOUND THEN
68 RAISE no_profile_found;
69 END IF;
70
71 CLOSE get_profile_cursor;
72
73 profile_value := l_pr_value;
74 return_code := 0;
75 return_message := 'Profile value found';
76
77
78 EXCEPTION
79 WHEN no_profile_found THEN
80 return_code := -9999;
81 return_message := 'BOM_NO_PROFILE_VALUE';
82 WHEN OTHERS THEN
83 return_code := SQLCODE;
84 return_message := 'BOMPRFIL.bom_pr_get_profile ' || SUBSTRB(SQLERRM,1,100);
85 END bom_pr_get_profile;
86
87 END BOMPRFIL;