[Home] [Help]
PACKAGE BODY: APPS.FEM_FOLDERS_UTL_PKG
Source
1 PACKAGE BODY fem_folders_utl_pkg AS
2 /* $Header: fem_folders_utl.plb 120.2 2005/07/26 14:10:30 appldev ship $ */
3
4 -- ***********************
5 -- Package constants
6 -- ***********************
7 g_pkg_name CONSTANT VARCHAR2(30) := 'fem_folders_utl_pkg';
8
9 g_ret_sts_success CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_success;
10 g_ret_sts_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_error;
11 g_ret_sts_unexp_error CONSTANT VARCHAR2(1):= fnd_api.g_ret_sts_unexp_error;
12
13 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
14
15 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
16 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
17 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
18 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
19 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
20 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
21
22 -- ******************************************************************************
23 PROCEDURE get_personal_folder (p_user_id IN NUMBER, p_folder_id OUT NOCOPY NUMBER) AS
24
25 -- ==========================================================================
26 -- Check if folder exists which was created by the user with the
27 -- same name as the user name. If the folder does not exist,
28 -- create the folder.
29 -- ==========================================================================
30
31 v_user_name VARCHAR2(30);
32 v_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
33
34 BEGIN
35 -- Retrieve user name
36 SELECT user_name INTO v_user_name
37 FROM fnd_user
38 WHERE user_id = p_user_id;
39
40 -- Retrieve personal folder
41 SELECT folder_id INTO p_folder_id
42 FROM fem_folders_vl
43 WHERE created_by = p_user_id
44 AND upper(folder_name) = UPPER(v_user_name);
45
46 EXCEPTION WHEN NO_DATA_FOUND THEN
47
48 IF v_user_name IS NOT NULL THEN
49 -- Create new personal folder since one does not exist
50 SELECT fem_folder_id_seq.nextval INTO p_folder_id FROM DUAL;
51
52 INSERT INTO fem_folders_b (folder_id, object_version_number,
53 created_by, creation_date, last_updated_by,
54 last_update_date, last_update_login) VALUES
55 (p_folder_id, 1, p_user_id, sysdate, p_user_id, sysdate, v_last_update_login);
56
57 INSERT INTO fem_folders_tl (folder_id, folder_name, description,
58 created_by, creation_date, last_updated_by,
59 last_update_date, last_update_login, language, source_lang)
60 SELECT
61 p_folder_id, v_user_name, v_user_name,
62 p_user_id, sysdate, p_user_id, sysdate, v_last_update_login,
63 l.language_code, userenv('LANG')
64 FROM fnd_languages l
65 WHERE l.installed_flag IN ('I', 'B')
66 AND NOT EXISTS
67 (SELECT NULL
68 FROM fem_folders_tl t
69 WHERE t.folder_id = p_folder_id
70 AND t.language = l.language_code);
71
72 INSERT INTO fem_user_folders (folder_id, user_id, write_flag,
73 object_version_number, created_by, creation_date, last_updated_by,
74 last_update_date, last_update_login) VALUES
75 (p_folder_id, p_user_id, 'Y',1, p_user_id, sysdate, p_user_id,
76 sysdate, v_last_update_login);
77
78 COMMIT;
79
80 fem_engines_pkg.tech_message(p_severity => 3,
81 p_module => 'fem.plsql.fem_folder_utl_pkg.get_default_folder',
82 p_msg_text => 'Created personal folder for USER_ID: '||p_user_id||' FOLDER_ID: '||p_folder_id||' FOLDER_NAME: '||v_user_name);
83
84 END IF;
85
86 END get_personal_folder;
87
88 -- ******************************************************************************
89 PROCEDURE assign_user_to_folder (p_api_version IN NUMBER,
90 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
91 p_user_id IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
92 p_folder_id IN NUMBER,
93 p_write_flag IN VARCHAR2 DEFAULT 'N',
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2,
96 x_return_status OUT NOCOPY VARCHAR2) AS
97
98 -- ==========================================================================
99 -- Assign user to a folder.
100 -- ==========================================================================
101 v_last_update_login NUMBER := FND_GLOBAL.Login_Id;
102 l_api_name CONSTANT VARCHAR2(30) := 'assign_user_to_folder';
103 l_api_version CONSTANT NUMBER := 1.0;
104
105 BEGIN
106
107 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
108 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
109 p_msg_text => 'Begin. P_USER_ID: '||p_user_id||
110 ' P_FOLDER_ID:'||p_folder_id||
111 ' P_WRITE_FLAG:'||p_write_flag||' P_COMMIT:'||p_commit);
112
113 -- Standard Start of API savepoint
114 SAVEPOINT assign_user_to_folder_pub;
115
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call (l_api_version,
118 p_api_version,
119 l_api_name,
120 g_pkg_name)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 -- Initialize API return status to success
126 x_return_status := G_RET_STS_SUCCESS;
127
128 -- Assign user to folder. (The select statement serves as a validation
129 -- to ensure that the folder is valid).
130 INSERT INTO fem_user_folders (folder_id, user_id, write_flag,
131 object_version_number, created_by, creation_date, last_updated_by,
132 last_update_date, last_update_login)
133 SELECT
134 folder_id, p_user_id, p_write_flag,
135 1, p_user_id, sysdate, p_user_id,
136 sysdate, v_last_update_login
137 FROM fem_folders_vl
138 WHERE folder_id = p_folder_id;
139
140 IF FND_API.To_Boolean( p_commit ) THEN
141 COMMIT WORK;
142 END IF;
143
144 fem_engines_pkg.tech_message(p_severity => c_log_level_1,
145 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
146 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
147
148 EXCEPTION
149 WHEN DUP_VAL_ON_INDEX THEN
150 NULL;
151 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
152 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
153 p_msg_text => 'End. User already assigned to folder. X_RETURN_STATUS: '||x_return_status);
154
155 WHEN NO_DATA_FOUND THEN
156 ROLLBACK TO assign_user_to_folder_pub;
157 x_return_status := g_ret_sts_error;
158
159 fem_engines_pkg.put_message(p_app_name =>'FEM',
160 p_msg_name =>'FEM_FOLDER_DOES_NOT_EXIST_ERR',
161 p_token1 => 'FOLDER_ID',
162 p_value1 => p_folder_id,
163 p_trans1 => 'N');
164
165 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
166 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
167 p_msg_name =>'FEM_FOLDER_DOES_NOT_EXIST_ERR',
168 p_token1 => 'FOLDER_ID',
169 p_value1 => p_folder_id,
170 p_trans1 => 'N');
171
172 -- Standard call to get message count and if count is 1, get message info.
173 FND_MSG_PUB.Count_And_Get
174 (p_count => x_msg_count,
175 p_data => x_msg_data);
176
177 fem_engines_pkg.tech_message(p_severity => c_log_level_3,
178 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
179 p_msg_text => 'End. X_RETURN_STATUS: '||x_return_status);
180
181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
182 ROLLBACK TO assign_user_to_folder_pub;
183 x_return_status := g_ret_sts_unexp_error;
184
185 fem_engines_pkg.tech_message(p_severity => c_log_level_2,
186 p_module => 'fem.plsql.'||g_pkg_name||'.'||l_api_name,
187 p_msg_text => 'Incompatible API call made to '||g_pkg_name||'.'||
188 l_api_name||' version: '||l_api_version);
189
190
191 END assign_user_to_folder;
192
193 -- ******************************************************************************
194 END fem_folders_utl_pkg;