[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_STATUS_MENU_PVT
Source
1 package body PA_ROLE_STATUS_MENU_PVT AS
2 /* $Header: PAXRSMVB.pls 115.3 2003/08/21 05:21:40 sulkumar ship $ */
3
4 procedure INSERT_ROW (
5 p_commit IN VARCHAR2:=FND_API.G_FALSE,
6 p_debug_mode in varchar2 default 'N',
7 P_ROLE_STATUS_MENU_ID OUT NOCOPY NUMBER,
8 P_ROLE_ID IN NUMBER,
9 P_STATUS_TYPE IN VARCHAR2,
10 P_STATUS_CODE IN VARCHAR2,
11 P_MENU_ID IN NUMBER,
12 P_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
13 P_LAST_UPDATE_DATE IN DATE,
14 P_LAST_UPDATED_BY IN NUMBER,
15 P_CREATION_DATE IN DATE,
16 P_CREATED_BY IN NUMBER,
17 P_LAST_UPDATE_LOGIN IN NUMBER,
18 p_return_status OUT NOCOPY varchar2,
19 p_msg_count out NOCOPY number,
20 p_msg_data out NOCOPY varchar2
21 ) IS
22
23 l_sqlcode varchar2(30);
24 l_error_message_code varchar2(30);
25
26 l_new_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
27 l_new_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
28 l_new_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
29 l_new_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
30 l_mod_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
31 l_mod_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
32 l_mod_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
33 l_mod_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
34 l_del_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
35 l_del_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
36 l_del_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
37
38 l_status_level varchar2(30);
39 l_count number;
40 l_menu_name varchar2(30);
41
42 BEGIN
43
44
45 FND_MSG_PUB.initialize;
46
47 p_msg_count := 0;
48
49 -- Check if the role status is a duplicate
50 pa_role_status_menu_utils.check_dup_role_status(
51 p_role_status_menu_id => p_role_status_menu_id,
52 p_role_id => p_role_id,
53 p_status_code => p_status_code,
54 p_return_status => p_return_status,
55 p_error_message_code => l_error_message_code);
56
57 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
58
59 -- Call the table handler to insert into the table
60
61 pa_role_status_menu_pkg.insert_row(
62 -- P_ROWID => P_ROWID,
63 P_ROLE_STATUS_MENU_ID => P_ROLE_STATUS_MENU_ID,
64 P_ROLE_ID => P_ROLE_ID,
65 P_STATUS_TYPE => P_STATUS_TYPE,
66 P_STATUS_CODE => P_STATUS_CODE,
67 P_MENU_ID => P_MENU_ID,
68 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
69 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
70 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
71 P_CREATION_DATE => P_CREATION_DATE,
72 P_CREATED_BY => P_CREATED_BY,
73 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
74 );
75
76 -- hr_utility.trace('after insert_row');
77 select count(*)
78 into l_count
79 from pa_project_parties
80 where project_role_id = p_role_id;
81
82 IF l_count > 0 THEN
83
84 select nvl(status_level, 'SYSTEM')
85 into l_status_level
86 from pa_project_role_types_b
87 where project_role_id = p_role_id;
88
89 select menu_name
90 into l_menu_name
91 from fnd_menus_vl
92 where menu_id = p_menu_id;
93
94 l_new_status_code_tbl.extend;
95 l_new_status_type_tbl.extend;
96 l_new_menu_name_tbl.extend;
97 l_new_role_sts_menu_id_tbl.extend;
98 l_new_status_code_tbl(1) := p_status_code;
99 l_new_status_type_tbl(1) := p_status_type;
100 l_new_menu_name_tbl(1) := l_menu_name;
101 l_new_role_sts_menu_id_tbl(1) := p_role_status_menu_id;
102
103 pa_security_pvt.update_status_based_sec
104 (p_commit => FND_API.G_FALSE,
105 p_project_role_id => p_role_id,
106 p_status_level => l_status_level,
107 p_new_status_code_tbl => l_new_status_code_tbl,
108 p_new_status_type_tbl => l_new_status_type_tbl,
109 p_new_menu_name_tbl => l_new_menu_name_tbl,
110 p_new_role_sts_menu_id_tbl => l_new_role_sts_menu_id_tbl,
111 p_mod_status_code_tbl => l_mod_status_code_tbl,
112 p_mod_status_type_tbl => l_mod_status_type_tbl,
113 p_mod_menu_id_tbl => l_mod_menu_id_tbl,
114 p_mod_role_sts_menu_id_tbl => l_mod_role_sts_menu_id_tbl,
115 p_del_status_code_tbl => l_del_status_code_tbl,
116 p_del_status_type_tbl => l_del_status_type_tbl,
117 p_del_role_sts_menu_id_tbl => l_del_role_sts_menu_id_tbl,
118 x_return_status => p_return_status,
119 x_msg_count => p_msg_count,
120 x_msg_data => p_msg_data
121 );
122 END IF;
123
124 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
125 fnd_message.set_name('PA', l_error_message_code);
126 fnd_msg_pub.ADD;
127 p_msg_count := p_msg_count + 1;
128
129 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
130
131 fnd_msg_pub.add_exc_msg
132 (p_pkg_name => 'pa_role_status_menu_utils',
133 p_procedure_name => 'check_dup_role_status',
134 p_error_text => l_error_message_code);
135
136 p_msg_count := p_msg_count + 1;
137
138 END IF;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142
143 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 l_sqlcode := SQLCODE;
145
146 fnd_msg_pub.add_exc_msg
147 (p_pkg_name => 'PA_ROLE_STATUS_MENU_PVT',
148 p_procedure_name => 'INSERT_ROW',
149 p_error_text => l_sqlcode);
150
151 p_msg_count := p_msg_count + 1;
152
153 END;
154
155 procedure LOCK_ROW (
156 p_commit IN VARCHAR2:=FND_API.G_FALSE,
157 p_debug_mode in varchar2 default 'N',
158 P_ROLE_STATUS_MENU_ID IN NUMBER,
159 P_OBJECT_VERSION_NUMBER IN NUMBER,
160 p_return_status OUT NOCOPY varchar2,
161 p_msg_count out NOCOPY number,
162 p_msg_data out NOCOPY varchar2
163 ) IS
164
165 l_sqlcode varchar2(30);
166
167 BEGIN
168
169 FND_MSG_PUB.initialize;
170
171 p_msg_count := 0;
172
173 -- Call the table handler to lock the row
174
175 pa_role_status_menu_pkg.lock_row(
176 P_ROLE_STATUS_MENU_ID => P_ROLE_STATUS_MENU_ID,
177 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER
178 );
179
180 p_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184
185 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 l_sqlcode := SQLCODE;
187
188 fnd_msg_pub.add_exc_msg
189 (p_pkg_name => 'PA_ROLE_STATUS_MENU_PVT',
190 p_procedure_name => 'LOCK_ROW',
191 p_error_text => l_sqlcode);
192
193 p_msg_count := p_msg_count + 1;
194 END;
195
196 procedure UPDATE_ROW (
197 p_commit IN VARCHAR2:=FND_API.G_FALSE,
198 p_debug_mode in varchar2 default 'N',
199 -- P_ROWID IN OUT NOCOPY VARCHAR2,
200 P_ROLE_STATUS_MENU_ID IN NUMBER,
201 P_ROLE_ID IN NUMBER,
202 P_STATUS_TYPE IN VARCHAR2,
203 P_STATUS_CODE IN VARCHAR2,
204 P_MENU_ID IN NUMBER,
205 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
206 P_LAST_UPDATE_DATE IN DATE,
207 P_LAST_UPDATED_BY IN NUMBER,
208 P_CREATION_DATE IN DATE,
209 P_CREATED_BY IN NUMBER,
210 P_LAST_UPDATE_LOGIN IN NUMBER,
211 p_return_status OUT NOCOPY varchar2,
212 p_msg_count out NOCOPY number,
213 p_msg_data out NOCOPY varchar2
214 ) is
215
216 l_new_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
217 l_new_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
218 l_new_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
219 l_new_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
220 l_mod_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
221 l_mod_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
222 l_mod_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
223 l_mod_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
224 l_del_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
225 l_del_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
226 l_del_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
227
228 l_status_level varchar2(30);
229 l_count number;
230
231 l_status_code VARCHAR2(80);
232 l_error_message_code VARCHAR2(30);
233 l_sqlcode VARCHAR2(30);
234
235 BEGIN
236
237 FND_MSG_PUB.initialize;
238
239 p_msg_count := 0;
240
241 -- Check for duplicates.
242
243 select status_code
244 into l_status_code
245 from pa_role_status_menu_map
246 where role_status_menu_id = p_role_status_menu_id;
247
248 p_return_status := FND_API.G_RET_STS_SUCCESS;
249
250 IF l_status_code <> p_status_code THEN
251
252 -- Check if the role status is a duplicate
253
254 pa_role_status_menu_utils.check_dup_role_status(
255 p_role_status_menu_id => p_role_status_menu_id,
256 p_role_id => p_role_id,
257 p_status_code => p_status_code,
258 p_return_status => p_return_status,
259 p_error_message_code => l_error_message_code);
260
261 END IF;
262
263 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
264
265 -- Call the table handler to update the row
266
267 pa_role_status_menu_pkg.update_row(
268 -- P_ROWID => P_ROWID,
269 P_ROLE_STATUS_MENU_ID => P_ROLE_STATUS_MENU_ID,
270 P_ROLE_ID => P_ROLE_ID,
271 P_STATUS_TYPE => P_STATUS_TYPE,
272 P_STATUS_CODE => P_STATUS_CODE,
273 P_MENU_ID => P_MENU_ID,
274 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
275 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
276 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
277 P_CREATION_DATE => P_CREATION_DATE,
278 P_CREATED_BY => P_CREATED_BY,
279 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
280 );
281
282 select count(*)
283 into l_count
284 from pa_project_parties
285 where project_role_id = p_role_id;
286
287 IF l_count > 0 THEN
288
289 select nvl(status_level, 'SYSTEM')
290 into l_status_level
291 from pa_project_role_types_b
292 where project_role_id = p_role_id;
293
294 l_mod_status_code_tbl.extend;
295 l_mod_status_type_tbl.extend;
296 l_mod_menu_id_tbl.extend;
297 l_mod_role_sts_menu_id_tbl.extend;
298
299 l_mod_status_code_tbl(1) := p_status_code;
300 l_mod_status_type_tbl(1) := p_status_type;
301 l_mod_menu_id_tbl(1) := p_menu_id;
302 l_mod_role_sts_menu_id_tbl(1) := p_role_status_menu_id;
303
304 pa_security_pvt.update_status_based_sec
305 (p_commit => FND_API.G_FALSE,
306 p_project_role_id => p_role_id,
307 p_status_level => l_status_level,
308 p_new_status_code_tbl => l_new_status_code_tbl,
309 p_new_status_type_tbl => l_new_status_type_tbl,
310 p_new_menu_name_tbl => l_new_menu_name_tbl,
311 p_new_role_sts_menu_id_tbl => l_new_role_sts_menu_id_tbl,
312 p_mod_status_code_tbl => l_mod_status_code_tbl,
313 p_mod_status_type_tbl => l_mod_status_type_tbl,
314 p_mod_menu_id_tbl => l_mod_menu_id_tbl,
315 p_mod_role_sts_menu_id_tbl => l_mod_role_sts_menu_id_tbl,
316 p_del_status_code_tbl => l_del_status_code_tbl,
317 p_del_status_type_tbl => l_del_status_type_tbl,
318 p_del_role_sts_menu_id_tbl => l_del_role_sts_menu_id_tbl,
319 x_return_status => p_return_status,
320 x_msg_count => p_msg_count,
321 x_msg_data => p_msg_data
322 );
323 END IF;
324
325
326 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
327 fnd_message.set_name('PA', l_error_message_code);
328 fnd_msg_pub.ADD;
329
330 p_msg_count := p_msg_count + 1;
331 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
332
333 fnd_msg_pub.add_exc_msg
334 (p_pkg_name => 'PA_ROLE_STATUS_MENU_UTILS',
335 p_procedure_name => 'check_dup_role_status',
336 p_error_text => l_error_message_code);
337
338 p_msg_count := p_msg_count + 1;
339 END IF;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343
344 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 l_sqlcode := SQLCODE;
346
347 fnd_msg_pub.add_exc_msg
348 (p_pkg_name => 'PA_ROLE_STATUS_MENU_PVT',
349 p_procedure_name => 'UPDATE_ROW',
350 p_error_text => l_sqlcode);
351
352 p_msg_count := p_msg_count + 1;
353 END;
354
355
356 procedure DELETE_ROW (
357 p_commit in VARCHAR2 := FND_API.G_FALSE,
358 p_debug_mode in varchar2 default 'N',
359 P_ROLE_STATUS_MENU_ID IN NUMBER,
360 P_OBJECT_VERSION_NUMBER IN NUMBER,
361 p_return_status out NOCOPY varchar2,
362 p_msg_count out NOCOPY number,
363 p_msg_data out NOCOPY varchar2
364 ) is
365
366 l_new_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
367 l_new_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
368 l_new_menu_name_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
369 l_new_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
370 l_mod_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
371 l_mod_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
372 l_mod_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
373 l_mod_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
374 l_del_status_code_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
375 l_del_status_type_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
376 l_del_role_sts_menu_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
377
378 l_status_level varchar2(30);
379 l_count number;
380
381 l_role_id NUMBER;
382 l_status_code VARCHAR2(30);
383 l_status_type VARCHAR2(30);
384 l_error_message_code VARCHAR2(30);
385 l_sqlcode VARCHAR2(30);
386
387 BEGIN
388 -- hr_utility.trace_on(NULL, 'RMFORM');
389 -- hr_utility.trace('start');
390
391 FND_MSG_PUB.initialize;
392
393 p_msg_count := 0;
394
395 p_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 -- hr_utility.trace('before my stuff');
398 select role_id, status_code, status_type
399 into l_role_id, l_status_code, l_status_type
400 from pa_role_status_menu_map
401 where role_status_menu_id = p_role_status_menu_id;
402
403 select count(*)
404 into l_count
405 from pa_project_parties
406 where project_role_id = l_role_id;
407
408
409 select nvl(status_level, 'SYSTEM')
410 into l_status_level
411 from pa_project_role_types_b
412 where project_role_id = l_role_id;
413
414 l_del_status_code_tbl.extend;
415 l_del_status_type_tbl.extend;
416 l_del_role_sts_menu_id_tbl.extend;
417
418 l_del_status_code_tbl(1) := l_status_code;
419 l_del_status_type_tbl(1) := l_status_type;
420 l_del_role_sts_menu_id_tbl(1) := p_role_status_menu_id;
421
422 -- Call the table handler to delete the role status menu mapping.
423
424 pa_role_status_menu_pkg.delete_row(
425 P_ROLE_STATUS_MENU_ID => P_ROLE_STATUS_MENU_ID,
426 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER
427 );
428
429 -- hr_utility.trace('before update_status_based_sec');
430
431 IF l_count > 0 THEN
432
433 pa_security_pvt.update_status_based_sec
434 (p_commit => FND_API.G_FALSE,
435 p_project_role_id => l_role_id,
436 p_status_level => l_status_level,
437 p_new_status_code_tbl => l_new_status_code_tbl,
438 p_new_status_type_tbl => l_new_status_type_tbl,
439 p_new_menu_name_tbl => l_new_menu_name_tbl,
440 p_new_role_sts_menu_id_tbl => l_new_role_sts_menu_id_tbl,
441 p_mod_status_code_tbl => l_mod_status_code_tbl,
442 p_mod_status_type_tbl => l_mod_status_type_tbl,
443 p_mod_menu_id_tbl => l_mod_menu_id_tbl,
444 p_mod_role_sts_menu_id_tbl => l_mod_role_sts_menu_id_tbl,
445 p_del_status_code_tbl => l_del_status_code_tbl,
446 p_del_status_type_tbl => l_del_status_type_tbl,
447 p_del_role_sts_menu_id_tbl => l_del_role_sts_menu_id_tbl,
448 x_return_status => p_return_status,
449 x_msg_count => p_msg_count,
450 x_msg_data => p_msg_data
451 );
452 -- hr_utility.trace('after update_status_based_sec');
453 -- hr_utility.trace('p_return_status is ' || p_return_status);
454 -- hr_utility.trace('p_msg_data is ' || p_msg_data);
455
456 END IF; -- l_count > 0
457 -- IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
458 -- END IF;
459
460 EXCEPTION
461 WHEN OTHERS THEN
462
463 -- hr_utility.trace('SQLERRM is ' || SQLERRM);
464 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
465
466 l_sqlcode := SQLCODE;
467
468 fnd_msg_pub.add_exc_msg
469 (p_pkg_name => 'PA_ROLE_STATUS_MENU_PVT',
470 p_procedure_name => 'DELETE_ROW',
471 p_error_text => l_sqlcode);
472
473 p_msg_count := p_msg_count + 1;
474
475 END;
476 end PA_ROLE_STATUS_MENU_PVT;