[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLES_PVT
Source
1 PACKAGE BODY jtf_rs_roles_pvt AS
2 /* $Header: jtfrsvob.pls 120.0 2005/05/11 08:23:11 appldev ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resource roles, like
6 create, update and delete resource Roles.
7 Its main procedures are as following:
8 Create Resource Roles
9 Update Resource Roles
10 Delete Resource Roles
11 This package valoidates the input parameters to these procedures and then
12 to do business validations and to do actual inserts, updates and deletes into tables.
13 ******************************************************************************************/
14
15 --Package variables.
16
17 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_ROLES_PVT';
18
19 --Procedure to create the resource roles based on input values passed by calling routines
20
21 PROCEDURE create_rs_resource_roles (
22 P_API_VERSION IN NUMBER,
23 P_INIT_MSG_LIST IN VARCHAR2,
24 P_COMMIT IN VARCHAR2,
25 P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
26 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
27 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
28 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
29 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
30 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
31 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
32 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
33 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
34 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
35 P_ATTRIBUTE1 IN JTF_RS_ROLES_B.ATTRIBUTE1%TYPE,
36 P_ATTRIBUTE2 IN JTF_RS_ROLES_B.ATTRIBUTE2%TYPE,
37 P_ATTRIBUTE3 IN JTF_RS_ROLES_B.ATTRIBUTE3%TYPE,
38 P_ATTRIBUTE4 IN JTF_RS_ROLES_B.ATTRIBUTE4%TYPE,
39 P_ATTRIBUTE5 IN JTF_RS_ROLES_B.ATTRIBUTE5%TYPE,
40 P_ATTRIBUTE6 IN JTF_RS_ROLES_B.ATTRIBUTE6%TYPE,
41 P_ATTRIBUTE7 IN JTF_RS_ROLES_B.ATTRIBUTE7%TYPE,
42 P_ATTRIBUTE8 IN JTF_RS_ROLES_B.ATTRIBUTE8%TYPE,
43 P_ATTRIBUTE9 IN JTF_RS_ROLES_B.ATTRIBUTE9%TYPE,
44 P_ATTRIBUTE10 IN JTF_RS_ROLES_B.ATTRIBUTE10%TYPE,
45 P_ATTRIBUTE11 IN JTF_RS_ROLES_B.ATTRIBUTE11%TYPE,
46 P_ATTRIBUTE12 IN JTF_RS_ROLES_B.ATTRIBUTE12%TYPE,
47 P_ATTRIBUTE13 IN JTF_RS_ROLES_B.ATTRIBUTE13%TYPE,
48 P_ATTRIBUTE14 IN JTF_RS_ROLES_B.ATTRIBUTE14%TYPE,
49 P_ATTRIBUTE15 IN JTF_RS_ROLES_B.ATTRIBUTE15%TYPE,
50 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLES_B.ATTRIBUTE_CATEGORY%TYPE,
51 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
52 X_MSG_COUNT OUT NOCOPY NUMBER,
53 X_MSG_DATA OUT NOCOPY VARCHAR2,
54 X_ROLE_ID OUT NOCOPY JTF_RS_ROLES_B.ROLE_ID%TYPE
55 )
56 IS
57
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_RESOURCE_ROLES';
60 l_role_type_code jtf_rs_roles_vl.role_type_code%type := p_role_type_code;
61 l_role_code jtf_rs_roles_vl.role_code%type := p_role_code;
62 l_role_name jtf_rs_roles_vl.role_name%type := p_role_name;
63 l_role_desc jtf_rs_roles_vl.role_desc%type := p_role_desc;
64 l_seeded_flag jtf_rs_roles_vl.seeded_flag%type := p_seeded_flag;
65 l_active_flag jtf_rs_roles_vl.active_flag%type := p_active_flag;
66 l_member_flag jtf_rs_roles_vl.member_flag%type := p_member_flag;
67 l_admin_flag jtf_rs_roles_vl.admin_flag%type := p_admin_flag;
68 l_lead_flag jtf_rs_roles_vl.lead_flag%type := p_lead_flag;
69 l_manager_flag jtf_rs_roles_vl.manager_flag%type := p_manager_flag;
70 l_attribute1 jtf_rs_roles_vl.attribute1%type := p_attribute1;
71 l_attribute2 jtf_rs_roles_vl.attribute2%type := p_attribute2;
72 l_attribute3 jtf_rs_roles_vl.attribute3%type := p_attribute3;
73 l_attribute4 jtf_rs_roles_vl.attribute4%type := p_attribute4;
74 l_attribute5 jtf_rs_roles_vl.attribute5%type := p_attribute5;
75 l_attribute6 jtf_rs_roles_vl.attribute6%type := p_attribute6;
76 l_attribute7 jtf_rs_roles_vl.attribute7%type := p_attribute7;
77 l_attribute8 jtf_rs_roles_vl.attribute8%type := p_attribute8;
78 l_attribute9 jtf_rs_roles_vl.attribute9%type := p_attribute9;
79 l_attribute10 jtf_rs_roles_vl.attribute10%type := p_attribute10;
80 l_attribute11 jtf_rs_roles_vl.attribute11%type := p_attribute11;
81 l_attribute12 jtf_rs_roles_vl.attribute12%type := p_attribute12;
82 l_attribute13 jtf_rs_roles_vl.attribute13%type := p_attribute13;
83 l_attribute14 jtf_rs_roles_vl.attribute14%type := p_attribute14;
84 l_attribute15 jtf_rs_roles_vl.attribute15%type := p_attribute15;
85 l_attribute_category jtf_rs_roles_vl.attribute_category%type := p_attribute_category;
86 l_rowid ROWID;
87 l_role_id jtf_rs_roles_vl.role_id%type;
88 i number;
89 l_check_char varchar2(1);
90 l_bind_data_id number;
91
92 l_return_status VARCHAR2(2000);
93 l_msg_count NUMBER;
94 l_msg_data VARCHAR2(2000);
95
96 CURSOR c_jtf_rs_roles( l_role_id IN jtf_rs_roles_vl.role_id%type ) IS
97 SELECT 'Y'
98 FROM jtf_rs_roles_vl
99 WHERE role_id = l_role_id;
100
101 CURSOR c_role_code (l_role_code IN jtf_rs_roles_vl.role_code%type) IS
102 SELECT role_code
103 FROM jtf_rs_roles_vl
104 WHERE role_code = l_role_code;
105
106 CURSOR c_role_type_code ( l_role_type_code IN jtf_rs_roles_vl.role_type_code%type) IS
107 SELECT lookup_code from fnd_lookups
108 WHERE LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
109 AND lookup_code = l_role_type_code
110 AND enabled_flag = 'Y'
111 AND trunc(sysdate) <= trunc(nvl(end_date_active,sysdate));
112
113 Dummy_char varchar2(1);
114
115 CURSOR c_role_name_check(c_role_name IN jtf_rs_roles_vl.role_name%type, c_role_type_code IN jtf_rs_roles_vl.role_type_code%type) IS
116 SELECT 'x'
117 FROM jtf_rs_roles_vl
118 WHERE upper(ROLE_NAME) = c_role_name
119 AND ROLE_TYPE_CODE = c_role_type_code;
120
121 BEGIN
122
123 SAVEPOINT create_rs_resource_roles_pvt;
124 x_return_status := fnd_api.g_ret_sts_success;
125 --DBMS_OUTPUT.put_line(' Started Create RS Resource Roles Pvt ');
126
127 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
128 RAISE fnd_api.g_exc_unexpected_error;
129 END IF;
130
131 IF fnd_api.to_boolean(p_init_msg_list) THEN
132 fnd_msg_pub.initialize;
133 END IF;
134
135 --Make the pre processing call to the user hooks
136
137 --Pre Call to the Customer Type User Hook
138 IF jtf_usr_hks.ok_to_execute(
139 'JTF_RS_ROLES_PVT',
140 'CREATE_RS_RESOURCE_ROLES',
141 'B',
142 'C')
143 THEN
144 jtf_rs_roles_cuhk.create_rs_resource_roles_pre(
145 P_ROLE_TYPE_CODE => l_role_type_code,
146 P_ROLE_CODE => l_role_code,
147 P_ROLE_NAME => l_role_name,
148 P_ROLE_DESC => l_role_desc,
149 P_ACTIVE_FLAG => l_active_flag,
150 P_SEEDED_FLAG => l_seeded_flag,
151 P_MEMBER_FLAG => l_member_flag,
152 P_ADMIN_FLAG => l_admin_flag,
153 P_LEAD_FLAG => l_lead_flag,
154 P_MANAGER_FLAG => l_manager_flag,
155 X_RETURN_STATUS => x_return_status,
156 X_MSG_COUNT => x_msg_count,
157 X_MSG_DATA => x_msg_data
158 );
159 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
160 x_return_status := fnd_api.g_ret_sts_unexp_error;
161 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
162 fnd_msg_pub.add;
163 RAISE fnd_api.g_exc_unexpected_error;
164 END IF;
165 END IF;
166
167 --Pre Call to the Vertical Type User Hook
168 IF jtf_usr_hks.ok_to_execute(
169 'JTF_RS_ROLES_PVT',
170 'CREATE_RS_RESOURCE_ROLES',
171 'B',
172 'V')
173 THEN
174 jtf_rs_roles_vuhk.create_rs_resource_roles_pre(
175 P_ROLE_TYPE_CODE => l_role_type_code,
176 P_ROLE_CODE => l_role_code,
177 P_ROLE_NAME => l_role_name,
178 P_ROLE_DESC => l_role_desc,
179 P_ACTIVE_FLAG => l_active_flag,
180 P_SEEDED_FLAG => l_seeded_flag,
181 P_MEMBER_FLAG => l_member_flag,
182 P_ADMIN_FLAG => l_admin_flag,
183 P_LEAD_FLAG => l_lead_flag,
184 P_MANAGER_FLAG => l_manager_flag,
185 X_RETURN_STATUS => x_return_status,
186 X_MSG_COUNT => x_msg_count,
187 X_MSG_DATA => x_msg_data
188 );
189 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
190 x_return_status := fnd_api.g_ret_sts_unexp_error;
191 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
192 fnd_msg_pub.add;
193 RAISE fnd_api.g_exc_unexpected_error;
194 END IF;
195 END IF;
196
197 --Pre Call to the Internal Type User Hook
198 IF jtf_usr_hks.ok_to_execute(
199 'JTF_RS_ROLES_PVT',
200 'CREATE_RS_RESOURCE_ROLES',
201 'B',
202 'I')
203 THEN
204 jtf_rs_roles_iuhk.create_rs_resource_roles_pre(
205 P_ROLE_TYPE_CODE => l_role_type_code,
206 P_ROLE_CODE => l_role_code,
207 P_ROLE_NAME => l_role_name,
208 P_ROLE_DESC => l_role_desc,
209 P_ACTIVE_FLAG => l_active_flag,
210 P_SEEDED_FLAG => l_seeded_flag,
211 P_MEMBER_FLAG => l_member_flag,
212 P_ADMIN_FLAG => l_admin_flag,
213 P_LEAD_FLAG => l_lead_flag,
214 P_MANAGER_FLAG => l_manager_flag,
215 X_RETURN_STATUS => x_return_status,
216 X_MSG_COUNT => x_msg_count,
217 X_MSG_DATA => x_msg_data
218 );
219 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
220 x_return_status := fnd_api.g_ret_sts_unexp_error;
221 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
222 fnd_msg_pub.add;
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225 END IF;
226
227 --Put in all the Validations here
228
229 --Validate that the Role Code is not null and unique
230
231 IF l_role_code is NULL THEN
232 --dbms_output.put_line ('Role Code Is Null');
233 fnd_message.set_name('JTF', 'JTF_RS_ROLE_CODE_NULL');
234 fnd_msg_pub.add;
235 x_return_status := fnd_api.g_ret_sts_unexp_error;
236 RAISE fnd_api.g_exc_unexpected_error;
237 ELSIF l_role_code is not NULL THEN
238 OPEN c_role_code (l_role_code);
239 FETCH c_role_code INTO l_role_code;
240 IF c_role_code%FOUND THEN
241 --dbms_output.put_line ('Duplicate Role Code');
242 fnd_message.set_name('JTF', 'JTF_RS_ROLE_CODE_EXISTS');
243 fnd_msg_pub.add;
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245 RAISE fnd_api.g_exc_unexpected_error;
246 END IF;
247 END IF;
248
249 --Validate Role Type Code
250
251 IF l_role_type_code is NULL THEN
252 --dbms_output.put_line ('Role Type Code Is Null');
253 fnd_message.set_name('JTF', 'JTF_RS_ROLE_TYPE_CODE_NULL');
254 fnd_msg_pub.add;
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256 RAISE fnd_api.g_exc_unexpected_error;
257 ELSIF l_role_type_code is not NULL THEN
258 OPEN c_role_type_code (l_role_type_code);
259 FETCH c_role_type_code INTO l_role_type_code;
260 IF c_role_type_code%NOTFOUND THEN
261 --dbms_output.put_line('Role type code is invalid');
262 CLOSE c_role_type_code;
263 fnd_message.set_name('JTF', 'JTF_RS_INVALID_ROLE_TYPE_CODE');
264 fnd_message.set_token('P_ROLE_TYPE_CODE', p_role_type_code);
265 fnd_msg_pub.add;
266 x_return_status := fnd_api.g_ret_sts_unexp_error;
267 RAISE fnd_api.g_exc_unexpected_error;
268 END IF;
269 END IF;
270
271 --Validate that Role Name is Not Null
272
273 IF l_role_name IS NULL THEN
274 --dbms_output.put_line ('Role Name is Null');
275 fnd_message.set_name ('JTF','JTF_RS_ROLE_NAME_NULL');
276 fnd_msg_pub.add;
277 x_return_status := fnd_api.g_ret_sts_unexp_error;
278 RAISE fnd_api.g_exc_unexpected_error;
279 ELSE
280 OPEN c_role_name_check(upper(l_role_name),l_role_type_code);
281 FETCH c_role_name_check INTO Dummy_char;
282 IF (c_role_name_check%FOUND) THEN
283 -- fnd_message.set_name('JTF', 'This Role name is already exists for the same Role type. Please choose a role name unique within this role type');
284 fnd_message.set_name('JTF', 'JTF_RS_ROLE_NAME_EXISTS');
285 fnd_msg_pub.add;
286 CLOSE c_role_name_check;
287 x_return_status := fnd_api.g_ret_sts_error;
288 RAISE fnd_api.g_exc_error;
289 END IF;
290 CLOSE c_role_name_check;
291 END IF;
292
293 --Validate the flags
294
295 --Validate the seeded flag
296 IF l_seeded_flag IS NOT NULL THEN
297 jtf_resource_utl.validate_rs_role_flags (
298 p_rs_role_flag => l_seeded_flag,
299 x_return_status => x_return_status
300 );
301 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
302 x_return_status := fnd_api.g_ret_sts_unexp_error;
303 RAISE fnd_api.g_exc_unexpected_error;
304 END IF;
305 END IF;
306
307 --Validate the member flag
308 IF l_member_flag IS NOT NULL THEN
309 jtf_resource_utl.validate_rs_role_flags (
310 p_rs_role_flag => l_member_flag,
311 x_return_status => x_return_status
312 );
313 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315 RAISE fnd_api.g_exc_unexpected_error;
316 END IF;
317 END IF;
318
319 --Validate the admin flag
320 IF l_admin_flag IS NOT NULL THEN
321 jtf_resource_utl.validate_rs_role_flags (
322 p_rs_role_flag => l_admin_flag,
323 x_return_status => x_return_status
324 );
325 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
326 x_return_status := fnd_api.g_ret_sts_unexp_error;
327 RAISE fnd_api.g_exc_unexpected_error;
328 END IF;
329 END IF;
330
331 --Validate the lead flag
332 IF l_lead_flag IS NOT NULL THEN
333 jtf_resource_utl.validate_rs_role_flags (
334 p_rs_role_flag => l_lead_flag,
335 x_return_status => x_return_status
336 );
337 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
338 x_return_status := fnd_api.g_ret_sts_unexp_error;
339 RAISE fnd_api.g_exc_unexpected_error;
340 END IF;
341 END IF;
342
343 --Validate the manager flag
344 IF l_manager_flag IS NOT NULL THEN
345 jtf_resource_utl.validate_rs_role_flags (
346 p_rs_role_flag => l_manager_flag,
347 x_return_status => x_return_status
348 );
349 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
350 x_return_status := fnd_api.g_ret_sts_unexp_error;
351 RAISE fnd_api.g_exc_unexpected_error;
352 END IF;
353 END IF;
354
355 --Validate the active flag
356 IF l_active_flag IS NOT NULL THEN
357 jtf_resource_utl.validate_rs_role_flags (
358 p_rs_role_flag => l_active_flag,
359 x_return_status => x_return_status
360 );
361 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
362 x_return_status := fnd_api.g_ret_sts_unexp_error;
363 RAISE fnd_api.g_exc_unexpected_error;
364 END IF;
365 END IF;
366
367 --Get the next value of the Role Id from the sequence
368 SELECT jtf_rs_roles_s.nextval
369 INTO l_role_id
370 FROM dual;
371
372 --Call the Table Handler to Insert Values into jtf_rs_roles Tables
373
374 jtf_rs_roles_pkg.insert_row (
375 X_ROWID => l_rowid,
376 X_ROLE_ID => l_role_id,
377 X_ATTRIBUTE3 => l_attribute3,
378 X_ATTRIBUTE4 => l_attribute4,
379 X_ATTRIBUTE5 => l_attribute5,
380 X_ATTRIBUTE6 => l_attribute6,
381 X_ATTRIBUTE7 => l_attribute7,
382 X_ATTRIBUTE8 => l_attribute8,
383 X_ATTRIBUTE9 => l_attribute9,
384 X_ATTRIBUTE10 => l_attribute10,
385 X_ATTRIBUTE11 => l_attribute11,
386 X_ATTRIBUTE12 => l_attribute12,
387 X_ATTRIBUTE13 => l_attribute13,
388 X_ATTRIBUTE14 => l_attribute14,
389 X_ATTRIBUTE15 => l_attribute15,
390 X_ATTRIBUTE_CATEGORY => l_attribute_category,
391 X_ROLE_CODE => l_role_code,
392 X_ROLE_TYPE_CODE => l_role_type_code,
393 X_SEEDED_FLAG => l_seeded_flag,
394 X_MEMBER_FLAG => l_member_flag,
395 X_ADMIN_FLAG => l_admin_flag,
396 X_LEAD_FLAG => l_lead_flag,
397 X_MANAGER_FLAG => l_manager_flag,
398 X_ACTIVE_FLAG => l_active_flag,
399 X_ATTRIBUTE1 => l_attribute1,
400 X_ATTRIBUTE2 => l_attribute2,
401 X_ROLE_NAME => l_role_name,
402 X_ROLE_DESC => l_role_desc,
403 X_CREATION_DATE => sysdate,
404 X_CREATED_BY => jtf_resource_utl.created_by,
405 X_LAST_UPDATE_DATE => sysdate,
406 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
407 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
408 );
409
410 --dbms_output.put_line('Inserted Row');
411 OPEN c_jtf_rs_roles (l_role_id);
412 FETCH c_jtf_rs_roles INTO l_check_char;
413 IF c_jtf_rs_roles%NOTFOUND THEN
414 --dbms_output.put_line('Error in Table Handler');
415 x_return_status := fnd_api.g_ret_sts_unexp_error;
416 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
417 fnd_msg_pub.add;
418 CLOSE c_jtf_rs_roles;
419 RAISE fnd_api.g_exc_unexpected_error;
420 ELSE
421 --dbms_output.put_line('Resource Role Successfully Created');
422 x_role_id := l_role_id;
423 x_return_status := fnd_api.g_ret_sts_success;
424 END IF;
425
426 --Post Call to the Customer User Hook
427 IF jtf_usr_hks.ok_to_execute(
428 'JTF_RS_ROLES_PVT',
429 'CREATE_RS_RESOURCE_ROLES',
430 'A',
431 'C')
432 THEN
433 jtf_rs_roles_cuhk.create_rs_resource_roles_post(
434 P_ROLE_TYPE_CODE => l_role_type_code,
435 P_ROLE_CODE => l_role_code,
436 P_ROLE_NAME => l_role_name,
437 P_ROLE_DESC => l_role_desc,
438 P_ACTIVE_FLAG => l_active_flag,
439 P_SEEDED_FLAG => l_seeded_flag,
440 P_MEMBER_FLAG => l_member_flag,
441 P_ADMIN_FLAG => l_admin_flag,
442 P_LEAD_FLAG => l_lead_flag,
443 P_MANAGER_FLAG => l_manager_flag,
444 P_ROLE_ID => l_role_id,
445 X_RETURN_STATUS => x_return_status,
446 X_MSG_COUNT => x_msg_count,
447 X_MSG_DATA => x_msg_data
448 );
449 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
450 x_return_status := fnd_api.g_ret_sts_unexp_error;
451 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
452 fnd_msg_pub.add;
453 RAISE fnd_api.g_exc_unexpected_error;
454 END IF;
455 END IF;
456
457 --Post Call to the Vertical Type User Hook
458 IF jtf_usr_hks.ok_to_execute(
459 'JTF_RS_ROLES_PVT',
460 'CREATE_RS_RESOURCE_ROLES',
461 'A',
462 'V')
463 THEN
464 jtf_rs_roles_vuhk.create_rs_resource_roles_post(
465 P_ROLE_TYPE_CODE => l_role_type_code,
466 P_ROLE_CODE => l_role_code,
467 P_ROLE_NAME => l_role_name,
468 P_ROLE_DESC => l_role_desc,
469 P_ACTIVE_FLAG => l_active_flag,
470 P_SEEDED_FLAG => l_seeded_flag,
471 P_MEMBER_FLAG => l_member_flag,
472 P_ADMIN_FLAG => l_admin_flag,
473 P_LEAD_FLAG => l_lead_flag,
474 P_MANAGER_FLAG => l_manager_flag,
475 P_ROLE_ID => l_role_id,
476 X_RETURN_STATUS => x_return_status,
477 X_MSG_COUNT => x_msg_count,
478 X_MSG_DATA => x_msg_data
479 );
480 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
481 x_return_status := fnd_api.g_ret_sts_unexp_error;
482 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
483 fnd_msg_pub.add;
484 RAISE fnd_api.g_exc_unexpected_error;
485 END IF;
486 END IF;
487
488 --Post Call to the Internal Type User Hook
489 IF jtf_usr_hks.ok_to_execute(
490 'JTF_RS_ROLES_PVT',
491 'CREATE_RS_RESOURCE_ROLES',
492 'A',
493 'I')
494 THEN
495 jtf_rs_roles_iuhk.create_rs_resource_roles_post(
496 P_ROLE_TYPE_CODE => l_role_type_code,
497 P_ROLE_CODE => l_role_code,
498 P_ROLE_NAME => l_role_name,
499 P_ROLE_DESC => l_role_desc,
500 P_ACTIVE_FLAG => l_active_flag,
501 P_SEEDED_FLAG => l_seeded_flag,
502 P_MEMBER_FLAG => l_member_flag,
503 P_ADMIN_FLAG => l_admin_flag,
504 P_LEAD_FLAG => l_lead_flag,
505 P_MANAGER_FLAG => l_manager_flag,
506 P_ROLE_ID => l_role_id,
507 X_RETURN_STATUS => x_return_status,
508 X_MSG_COUNT => x_msg_count,
509 X_MSG_DATA => x_msg_data
510 );
511 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
512 x_return_status := fnd_api.g_ret_sts_unexp_error;
513 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
514 fnd_msg_pub.add;
515 RAISE fnd_api.g_exc_unexpected_error;
516 END IF;
517 END IF;
518
519 /* Standard call for Message Generation */
520
521 IF jtf_usr_hks.ok_to_execute(
522 'JTF_RS_ROLES_PVT',
523 'CREATE_RS_RESOURCE_ROLES',
524 'M',
525 'M')
526 THEN
527 IF (jtf_rs_roles_cuhk.ok_to_generate_msg(
528 p_role_id => l_role_id,
529 x_return_status => x_return_status) )
530 THEN
531
532 /* Get the bind data id for the Business Object Instance */
533 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
534
535 /* Set bind values for the bind variables in the Business Object SQL */
536 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_id', l_role_id, 'S', 'N');
537
538 /* Call the message generation API */
539 jtf_usr_hks.generate_message(
540 p_prod_code => 'JTF',
541 p_bus_obj_code => 'RS_ROLE',
542 p_action_code => 'I',
543 p_bind_data_id => l_bind_data_id,
544 x_return_code => x_return_status);
545
546 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
547 --dbms_output.put_line('Returned Error status from the Message Generation API');
548 x_return_status := fnd_api.g_ret_sts_unexp_error;
549 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
550 fnd_msg_pub.add;
551 RAISE fnd_api.g_exc_unexpected_error;
552 END IF;
553 END IF;
554 END IF;
555
556 IF fnd_api.to_boolean(p_commit) THEN
557 COMMIT WORK;
558 END IF;
559
560 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
561
562 /* Calling publish API to raise create resource role event. */
563 /* added by baianand on 04/02/2003 */
564
565 begin
566 jtf_rs_wf_events_pub.create_resource_role
567 (p_api_version => 1.0
568 ,p_init_msg_list => fnd_api.g_false
569 ,p_commit => fnd_api.g_false
570 ,p_role_id => l_role_id
571 ,p_role_type_code => l_role_type_code
572 ,p_role_code => l_role_code
573 ,p_role_name => l_role_name
574 ,p_role_desc => l_role_desc
575 ,p_active_flag => l_active_flag
576 ,p_member_flag => l_member_flag
577 ,p_admin_flag => l_admin_flag
578 ,p_lead_flag => l_lead_flag
579 ,p_manager_flag => l_manager_flag
580 ,x_return_status => l_return_status
581 ,x_msg_count => l_msg_count
582 ,x_msg_data => l_msg_data);
583
584 EXCEPTION when others then
585 null;
586 end;
587
588 /* End of publish API call */
589
590
591 EXCEPTION
592 WHEN fnd_api.g_exc_unexpected_error THEN
593 --DBMS_OUTPUT.put_line (' ========================================== ');
594 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
595 ROLLBACK TO create_rs_resource_roles_pvt;
596 x_return_status := fnd_api.g_ret_sts_unexp_error;
597 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
598 WHEN OTHERS THEN
599 --DBMS_OUTPUT.put_line (' ========================================== ');
600 --DBMS_OUTPUT.put_line (' =========== Raised Others in Create Resource Role Pvt =============');
601 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
602 ROLLBACK TO create_rs_resource_roles_pvt;
603 x_return_status := fnd_api.g_ret_sts_unexp_error;
604 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
605
606 END create_rs_resource_roles;
607
608 --Procedure to update the resource roles based on input values passed by calling routines
609
610 PROCEDURE update_rs_resource_roles (
611 P_API_VERSION IN NUMBER,
612 P_INIT_MSG_LIST IN VARCHAR2,
613 P_COMMIT IN VARCHAR2,
614 P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
615 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
616 P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
617 P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
618 P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
619 P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
620 P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
621 P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
622 P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
623 P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
624 P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
625 P_ATTRIBUTE1 IN JTF_RS_ROLES_B.ATTRIBUTE1%TYPE,
626 P_ATTRIBUTE2 IN JTF_RS_ROLES_B.ATTRIBUTE2%TYPE,
627 P_ATTRIBUTE3 IN JTF_RS_ROLES_B.ATTRIBUTE3%TYPE,
628 P_ATTRIBUTE4 IN JTF_RS_ROLES_B.ATTRIBUTE4%TYPE,
629 P_ATTRIBUTE5 IN JTF_RS_ROLES_B.ATTRIBUTE5%TYPE,
630 P_ATTRIBUTE6 IN JTF_RS_ROLES_B.ATTRIBUTE6%TYPE,
631 P_ATTRIBUTE7 IN JTF_RS_ROLES_B.ATTRIBUTE7%TYPE,
632 P_ATTRIBUTE8 IN JTF_RS_ROLES_B.ATTRIBUTE8%TYPE,
633 P_ATTRIBUTE9 IN JTF_RS_ROLES_B.ATTRIBUTE9%TYPE,
634 P_ATTRIBUTE10 IN JTF_RS_ROLES_B.ATTRIBUTE10%TYPE,
635 P_ATTRIBUTE11 IN JTF_RS_ROLES_B.ATTRIBUTE11%TYPE,
636 P_ATTRIBUTE12 IN JTF_RS_ROLES_B.ATTRIBUTE12%TYPE,
637 P_ATTRIBUTE13 IN JTF_RS_ROLES_B.ATTRIBUTE13%TYPE,
638 P_ATTRIBUTE14 IN JTF_RS_ROLES_B.ATTRIBUTE14%TYPE,
639 P_ATTRIBUTE15 IN JTF_RS_ROLES_B.ATTRIBUTE15%TYPE,
640 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLES_B.ATTRIBUTE_CATEGORY%TYPE,
641 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_ROLES_B.OBJECT_VERSION_NUMBER%TYPE,
642 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
643 X_MSG_COUNT OUT NOCOPY NUMBER,
644 X_MSG_DATA OUT NOCOPY VARCHAR2
645 )
646 IS
647 l_api_version CONSTANT NUMBER := 1.0;
648 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RS_RESOURCE_ROLES';
649 l_role_id jtf_rs_roles_vl.role_id%type := p_role_id;
650 l_role_code jtf_rs_roles_vl.role_code%type := p_role_code;
651 l_role_name jtf_rs_roles_vl.role_name%type := p_role_name;
652 l_role_desc jtf_rs_roles_vl.role_desc%type := p_role_desc;
653 l_role_type_code jtf_rs_roles_vl.role_type_code%type := p_role_type_code;
654 l_seeded_flag jtf_rs_roles_vl.seeded_flag%type := p_seeded_flag;
655 l_active_flag jtf_rs_roles_vl.active_flag%type := p_active_flag;
656 l_member_flag jtf_rs_roles_vl.member_flag%type := p_member_flag;
657 l_admin_flag jtf_rs_roles_vl.admin_flag%type := p_admin_flag;
658 l_lead_flag jtf_rs_roles_vl.lead_flag%type := p_lead_flag;
659 l_manager_flag jtf_rs_roles_vl.manager_flag%type := p_manager_flag;
660 l_object_version_number jtf_rs_roles_vl.object_version_number%type :=p_object_version_number;
661 l_attribute1 jtf_rs_roles_vl.attribute1%type := p_attribute1;
662 l_attribute2 jtf_rs_roles_vl.attribute2%type := p_attribute2;
663 l_attribute3 jtf_rs_roles_vl.attribute3%type := p_attribute3;
664 l_attribute4 jtf_rs_roles_vl.attribute4%type := p_attribute4;
665 l_attribute5 jtf_rs_roles_vl.attribute5%type := p_attribute5;
666 l_attribute6 jtf_rs_roles_vl.attribute6%type := p_attribute6;
667 l_attribute7 jtf_rs_roles_vl.attribute7%type := p_attribute7;
668 l_attribute8 jtf_rs_roles_vl.attribute8%type := p_attribute8;
669 l_attribute9 jtf_rs_roles_vl.attribute9%type := p_attribute9;
670 l_attribute10 jtf_rs_roles_vl.attribute10%type := p_attribute10;
671 l_attribute11 jtf_rs_roles_vl.attribute11%type := p_attribute11;
672 l_attribute12 jtf_rs_roles_vl.attribute12%type := p_attribute12;
673 l_attribute13 jtf_rs_roles_vl.attribute13%type := p_attribute13;
674 l_attribute14 jtf_rs_roles_vl.attribute14%type := p_attribute14;
675 l_attribute15 jtf_rs_roles_vl.attribute15%type := p_attribute15;
676 l_attribute_category jtf_rs_roles_vl.attribute_category%type := p_attribute_category;
677 L_BIND_DATA_ID NUMBER;
678
679
680 CURSOR c_role_code (l_role_code IN jtf_rs_roles_vl.role_code%type) IS
681 SELECT role_code
682 FROM jtf_rs_roles_vl
683 WHERE role_code = l_role_code;
684
685 CURSOR c_role_type_code ( l_role_type_code IN jtf_rs_roles_vl.role_type_code%type) IS
686 SELECT lookup_code from fnd_lookups
687 WHERE LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
688 AND lookup_code = l_role_type_code
689 AND enabled_flag = 'Y'
690 AND trunc(sysdate) <= trunc(nvl(end_date_active,sysdate));
691
692 Dummy_char varchar2(1);
693
694 CURSOR c_role_name_check(c_role_name IN jtf_rs_roles_vl.role_name%type, c_role_type_code IN jtf_rs_roles_vl.role_type_code%type) IS
695 SELECT 'x'
696 FROM jtf_rs_roles_vl
697 WHERE upper(role_name) = c_role_name
698 AND role_type_code = c_role_type_code
699 AND role_id <> p_role_id;
700
701 CURSOR c_rs_role_update( l_role_id IN NUMBER ) IS
702 SELECT
703 Role_Code role_code,
704 DECODE(p_role_code, fnd_api.g_miss_char, role_code, p_role_code) l_role_code,
705 DECODE(p_role_type_code, fnd_api.g_miss_char, role_type_code, p_role_type_code) l_role_type_code,
706 DECODE(p_role_name, fnd_api.g_miss_char, role_name, p_role_name) l_role_name,
707 DECODE(p_seeded_flag, fnd_api.g_miss_char, seeded_flag, p_seeded_flag) l_seeded_flag,
708 DECODE(p_member_flag, fnd_api.g_miss_char, member_flag, p_member_flag) l_member_flag,
709 DECODE(p_admin_flag, fnd_api.g_miss_char, admin_flag, p_admin_flag) l_admin_flag,
710 DECODE(p_lead_flag, fnd_api.g_miss_char, lead_flag, p_lead_flag) l_lead_flag,
711 DECODE(p_manager_flag, fnd_api.g_miss_char, manager_flag, p_manager_flag) l_manager_flag,
712 DECODE(p_active_flag, fnd_api.g_miss_char, active_flag, p_active_flag) l_active_flag,
713 DECODE(p_role_desc, fnd_api.g_miss_char, role_desc, p_role_desc) l_role_desc,
714 DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
715 DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
716 DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
717 DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
718 DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
719 DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
720 DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
721 DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
722 DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
723 DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
724 DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
725 DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
726 DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
727 DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
728 DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
729 DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
730 FROM jtf_rs_roles_vl
731 WHERE role_id = l_role_id;
732
733 rs_role_rec c_rs_role_update%ROWTYPE;
734
735 l_return_status VARCHAR2(2000);
736 l_msg_count NUMBER;
737 l_msg_data VARCHAR2(2000);
738
739 l_resource_role_rec jtf_rs_roles_pvt.resource_role_rec_type;
740
741 BEGIN
742
743 SAVEPOINT update_rs_resource_roles_pvt;
744 x_return_status := fnd_api.g_ret_sts_success;
745 --DBMS_OUTPUT.put_line(' Started Update Resource Roles Pvt ');
746 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
747 RAISE fnd_api.g_exc_unexpected_error;
748 END IF;
749 IF fnd_api.to_boolean(p_init_msg_list) THEN
750 fnd_msg_pub.initialize;
751 END IF;
752
753 --Pre Call to the Customer User Hook
754 IF jtf_usr_hks.ok_to_execute(
755 'JTF_RS_ROLES_PVT',
756 'UPDATE_RS_RESOURCE_ROLES',
757 'B',
758 'C')
759 THEN
760 jtf_rs_roles_cuhk.update_rs_resource_roles_pre(
761 P_ROLE_ID => l_role_id,
762 P_ROLE_TYPE_CODE => l_role_type_code,
763 P_ROLE_CODE => l_role_code,
764 P_ROLE_NAME => l_role_name,
765 P_ROLE_DESC => l_role_desc,
766 P_ACTIVE_FLAG => l_active_flag,
767 P_SEEDED_FLAG => l_seeded_flag,
768 P_MEMBER_FLAG => l_member_flag,
769 P_ADMIN_FLAG => l_admin_flag,
770 P_LEAD_FLAG => l_lead_flag,
771 P_MANAGER_FLAG => l_manager_flag,
772 X_RETURN_STATUS => x_return_status,
773 X_MSG_COUNT => x_msg_count,
774 X_MSG_DATA => x_msg_data
775 );
776 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
777 x_return_status := fnd_api.g_ret_sts_unexp_error;
778 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
779 fnd_msg_pub.add;
780 RAISE fnd_api.g_exc_unexpected_error;
781 END IF;
782 END IF;
783
784 --Pre Call to Vertical User Hook
785 IF jtf_usr_hks.ok_to_execute(
786 'JTF_RS_ROLES_PVT',
787 'UPDATE_RS_RESOURCE_ROLES',
788 'B',
789 'V')
790 THEN
791 jtf_rs_roles_vuhk.update_rs_resource_roles_pre(
792 P_ROLE_ID => l_role_id,
793 P_ROLE_TYPE_CODE => l_role_type_code,
794 P_ROLE_CODE => l_role_code,
795 P_ROLE_NAME => l_role_name,
796 P_ROLE_DESC => l_role_desc,
797 P_ACTIVE_FLAG => l_active_flag,
798 P_SEEDED_FLAG => l_seeded_flag,
799 P_MEMBER_FLAG => l_member_flag,
800 P_ADMIN_FLAG => l_admin_flag,
801 P_LEAD_FLAG => l_lead_flag,
802 P_MANAGER_FLAG => l_manager_flag,
803 X_RETURN_STATUS => x_return_status,
804 X_MSG_COUNT => x_msg_count,
805 X_MSG_DATA => x_msg_data
806 );
807 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
808 x_return_status := fnd_api.g_ret_sts_unexp_error;
809 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
810 fnd_msg_pub.add;
811 RAISE fnd_api.g_exc_unexpected_error;
812 END IF;
813 END IF;
814
815 --Pre Call to Internal User Hook
816 IF jtf_usr_hks.ok_to_execute(
817 'JTF_RS_ROLES_PVT',
818 'UPDATE_RS_RESOURCE_ROLES',
819 'B',
820 'I')
821 THEN
822 jtf_rs_roles_iuhk.update_rs_resource_roles_pre(
823 P_ROLE_ID => l_role_id,
824 P_ROLE_TYPE_CODE => l_role_type_code,
825 P_ROLE_CODE => l_role_code,
826 P_ROLE_NAME => l_role_name,
827 P_ROLE_DESC => l_role_desc,
828 P_ACTIVE_FLAG => l_active_flag,
829 P_SEEDED_FLAG => l_seeded_flag,
830 P_MEMBER_FLAG => l_member_flag,
831 P_ADMIN_FLAG => l_admin_flag,
832 P_LEAD_FLAG => l_lead_flag,
833 P_MANAGER_FLAG => l_manager_flag,
834 X_RETURN_STATUS => x_return_status,
835 X_MSG_COUNT => x_msg_count,
836 X_MSG_DATA => x_msg_data
837 );
838 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
839 x_return_status := fnd_api.g_ret_sts_unexp_error;
840 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
841 fnd_msg_pub.add;
842 RAISE fnd_api.g_exc_unexpected_error;
843 END IF;
844 END IF;
845
846 --Put Validations here
847
848 --Validate the Role for Update
849 OPEN c_rs_role_update(l_role_id);
850 FETCH c_rs_role_update INTO rs_role_rec;
851 IF c_rs_role_update%NOTFOUND THEN
852 CLOSE c_rs_role_update;
853 fnd_message.set_name('JTF', 'JTF_RS_INVALID_ROLE_ID');
854 fnd_message.set_token('P_ROLE_ID', p_role_id);
855 fnd_msg_pub.add;
856 x_return_status := fnd_api.g_ret_sts_unexp_error;
857 RAISE fnd_api.g_exc_unexpected_error;
858 END IF;
859
860 --Validate the Resource Role
861
862 --BEGIN
863
864 --jtf_resource_utl.validate_resource_role(
865 -- p_role_id => l_role_id,
866 -- p_role_code => l_role_code,
867 -- x_return_status => x_return_status,
868 -- x_role_id => l_role_id
869 --);
870
871 --IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
872 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
873 -- RAISE fnd_api.g_exc_unexpected_error;
874 --END IF;
875
876 --END;
877
878 --End of Resource Role Validation
879
880 --Validate if the Seeded Flag is Checked
881 -- SELECT seeded_flag INTO l_seeded_flag
882 -- FROM jtf_rs_roles_vl
883 -- WHERE role_id = l_role_id;
884
885 -- IF l_seeded_flag = 'Y' THEN
886 -- --dbms_output.put_line ('Seeded Data Cannot be Updated');
887 -- fnd_message.set_name ('JTF', 'JTF_RS_SEEDED_FLAG_CHECKED');
888 -- fnd_msg_pub.add;
889 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
890 -- RAISE fnd_api.g_exc_unexpected_error;
891 -- END IF;
892
893 --Validate that the Role Code is not null and unique
894
895 IF l_role_code <> FND_API.G_MISS_CHAR THEN
896 IF l_role_code is NULL THEN
897 --dbms_output.put_line ('Role Code Is Null');
898 fnd_message.set_name('JTF', 'JTF_RS_ROLE_CODE_NULL');
899 fnd_msg_pub.add;
900 x_return_status := fnd_api.g_ret_sts_unexp_error;
901 RAISE fnd_api.g_exc_unexpected_error;
902 ELSIF (l_role_code is not NULL AND l_role_code <> rs_role_rec.role_code) THEN
903 OPEN c_role_code (l_role_code);
904 FETCH c_role_code INTO l_role_code;
905 IF c_role_code%FOUND THEN
906 --dbms_output.put_line ('Duplicate Role Code');
907 fnd_message.set_name('JTF', 'JTF_RS_ROLE_CODE_EXISTS');
908 fnd_msg_pub.add;
909 x_return_status := fnd_api.g_ret_sts_unexp_error;
910 RAISE fnd_api.g_exc_unexpected_error;
911 END IF;
912 END IF;
913 END IF;
914
915 --Validate Role Type Code
916
917 IF l_role_type_code <> FND_API.G_MISS_CHAR THEN
918 IF l_role_type_code is NULL THEN
919 --dbms_output.put_line ('Role Type Code Is Null');
920 fnd_message.set_name('JTF', 'JTF_RS_ROLE_TYPE_CODE_NULL');
921 fnd_msg_pub.add;
922 x_return_status := fnd_api.g_ret_sts_unexp_error;
923 RAISE fnd_api.g_exc_unexpected_error;
924 ELSIF l_role_type_code is not NULL THEN
925 OPEN c_role_type_code (l_role_type_code);
926 FETCH c_role_type_code INTO l_role_type_code;
927 IF c_role_type_code%NOTFOUND THEN
928 --dbms_output.put_line('Role type code is invalid');
929 CLOSE c_role_type_code;
930 fnd_message.set_name('JTF', 'JTF_RS_INVALID_ROLE_TYPE_CODE');
931 fnd_message.set_token('P_ROLE_TYPE_CODE', l_role_type_code);
932 fnd_msg_pub.add;
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 RAISE fnd_api.g_exc_unexpected_error;
935 END IF;
936 END IF;
937 END IF;
938
939 --Validate that Role Name is Not Null
940
941 IF l_role_name <> FND_API.G_MISS_CHAR THEN
942 IF l_role_name IS NULL THEN
943 --dbms_output.put_line ('Role Name is Null');
944 fnd_message.set_name ('JTF','JTF_RS_ROLE_NAME_NULL');
945 fnd_msg_pub.add;
946 x_return_status := fnd_api.g_ret_sts_unexp_error;
947 RAISE fnd_api.g_exc_unexpected_error;
948 ELSE
949 OPEN c_role_name_check(upper(l_role_name),rs_role_rec.l_role_type_code);
950 FETCH c_role_name_check INTO Dummy_char;
951 IF (c_role_name_check%FOUND) THEN
952 fnd_message.set_name('JTF', 'JTF_RS_ROLE_NAME_EXISTS');
953 fnd_msg_pub.add;
954 CLOSE c_role_name_check;
955 x_return_status := fnd_api.g_ret_sts_error;
956 RAISE fnd_api.g_exc_error;
957 END IF;
958 CLOSE c_role_name_check;
959 END IF;
960 END IF;
961
962 --End of Role Name Validation
963
964 --Validate Flags
965
966 --Validate the seeded flag
967 IF l_seeded_flag <> FND_API.G_MISS_CHAR THEN
968 IF rs_role_rec.l_seeded_flag IS NOT NULL THEN
969 jtf_resource_utl.validate_rs_role_flags (
970 p_rs_role_flag => rs_role_rec.l_seeded_flag,
971 x_return_status => x_return_status
972 );
973 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
974 x_return_status := fnd_api.g_ret_sts_unexp_error;
975 RAISE fnd_api.g_exc_unexpected_error;
976 END IF;
977 END IF;
978 END IF;
979
980 --Validate the member flag
981 IF l_member_flag <> FND_API.G_MISS_CHAR THEN
982 IF rs_role_rec.l_member_flag IS NOT NULL THEN
983 jtf_resource_utl.validate_rs_role_flags (
984 p_rs_role_flag => rs_role_rec.l_member_flag,
985 x_return_status => x_return_status
986 );
987 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
988 x_return_status := fnd_api.g_ret_sts_unexp_error;
989 RAISE fnd_api.g_exc_unexpected_error;
990 END IF;
991 END IF;
992 END IF;
993
994 --Validate the admin flag
995 IF l_admin_flag <> FND_API.G_MISS_CHAR THEN
996 IF rs_role_rec.l_admin_flag IS NOT NULL THEN
997 jtf_resource_utl.validate_rs_role_flags (
998 p_rs_role_flag => rs_role_rec.l_admin_flag,
999 x_return_status => x_return_status
1000 );
1001 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1002 x_return_status := fnd_api.g_ret_sts_unexp_error;
1003 RAISE fnd_api.g_exc_unexpected_error;
1004 END IF;
1005 END IF;
1006 END IF;
1007
1008 --Validate the lead flag
1009 IF l_lead_flag <> FND_API.G_MISS_CHAR THEN
1010 IF rs_role_rec.l_lead_flag IS NOT NULL THEN
1011 jtf_resource_utl.validate_rs_role_flags (
1012 p_rs_role_flag => rs_role_rec.l_lead_flag,
1013 x_return_status => x_return_status
1014 );
1015 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1016 x_return_status := fnd_api.g_ret_sts_unexp_error;
1017 RAISE fnd_api.g_exc_unexpected_error;
1018 END IF;
1019 END IF;
1020 END IF;
1021
1022 --Validate the manager flag
1023 IF l_manager_flag <> FND_API.G_MISS_CHAR THEN
1024 IF rs_role_rec.l_manager_flag IS NOT NULL THEN
1025 jtf_resource_utl.validate_rs_role_flags (
1026 p_rs_role_flag => rs_role_rec.l_manager_flag,
1027 x_return_status => x_return_status
1028 );
1029 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1030 x_return_status := fnd_api.g_ret_sts_unexp_error;
1031 RAISE fnd_api.g_exc_unexpected_error;
1032 END IF;
1033 END IF;
1034 END IF;
1035
1036 --Validate the active flag
1037 IF l_active_flag <> FND_API.G_MISS_CHAR THEN
1038 IF rs_role_rec.l_active_flag IS NOT NULL THEN
1039 jtf_resource_utl.validate_rs_role_flags (
1040 p_rs_role_flag => rs_role_rec.l_active_flag,
1041 x_return_status => x_return_status
1042 );
1043 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1044 x_return_status := fnd_api.g_ret_sts_unexp_error;
1045 RAISE fnd_api.g_exc_unexpected_error;
1046 END IF;
1047 END IF;
1048 END IF;
1049
1050 --Call the Lock Row Table Handler before updating the record
1051 jtf_rs_roles_pkg.lock_row (
1052 X_ROLE_ID => l_role_id,
1053 X_OBJECT_VERSION_NUMBER => l_object_version_number
1054 );
1055
1056 /* Calling publish API to raise update resource role event. */
1057 /* added by baianand on 04/02/2003 */
1058
1059 begin
1060
1061 l_resource_role_rec.role_id := l_role_id;
1062 l_resource_role_rec.role_code := rs_role_rec.l_role_code;
1063 l_resource_role_rec.role_type_code := rs_role_rec.l_role_type_code;
1064 l_resource_role_rec.role_name := rs_role_rec.l_role_name;
1065 l_resource_role_rec.role_desc := rs_role_rec.l_role_desc;
1066 l_resource_role_rec.active_flag := rs_role_rec.l_active_flag;
1067 l_resource_role_rec.member_flag := rs_role_rec.l_member_flag;
1068 l_resource_role_rec.admin_flag := rs_role_rec.l_admin_flag;
1069 l_resource_role_rec.lead_flag := rs_role_rec.l_lead_flag;
1070 l_resource_role_rec.manager_flag := rs_role_rec.l_manager_flag;
1071
1072 jtf_rs_wf_events_pub.update_resource_role
1073 (p_api_version => 1.0
1074 ,p_init_msg_list => fnd_api.g_false
1075 ,p_commit => fnd_api.g_false
1076 ,p_resource_role_rec => l_resource_role_rec
1077 ,x_return_status => l_return_status
1078 ,x_msg_count => l_msg_count
1079 ,x_msg_data => l_msg_data);
1080
1081 EXCEPTION when others then
1082 null;
1083 end;
1084
1085 /* End of publish API call */
1086
1087
1088 --Update the Object Version Number by Incrementing It
1089 l_object_version_number := l_object_version_number+1;
1090
1091 --Call the Table Handler to Update the Values in jtf_rs_role tables
1092 BEGIN
1093 jtf_rs_roles_pkg.update_row (
1094 X_ROLE_ID => l_role_id,
1095 X_ATTRIBUTE3 => rs_role_rec.l_attribute3,
1096 X_ATTRIBUTE4 => rs_role_rec.l_attribute4,
1097 X_ATTRIBUTE5 => rs_role_rec.l_attribute5,
1098 X_ATTRIBUTE6 => rs_role_rec.l_attribute6,
1099 X_ATTRIBUTE7 => rs_role_rec.l_attribute7,
1100 X_ATTRIBUTE8 => rs_role_rec.l_attribute8,
1101 X_ATTRIBUTE9 => rs_role_rec.l_attribute9,
1102 X_ATTRIBUTE10 => rs_role_rec.l_attribute10,
1103 X_ATTRIBUTE11 => rs_role_rec.l_attribute11,
1104 X_ATTRIBUTE12 => rs_role_rec.l_attribute12,
1105 X_ATTRIBUTE13 => rs_role_rec.l_attribute13,
1106 X_ATTRIBUTE14 => rs_role_rec.l_attribute14,
1107 X_ATTRIBUTE15 => rs_role_rec.l_attribute15,
1108 X_ATTRIBUTE_CATEGORY => rs_role_rec.l_attribute_category,
1109 X_ROLE_CODE => rs_role_rec.l_role_code,
1110 X_ROLE_TYPE_CODE => rs_role_rec.l_role_type_code,
1111 X_SEEDED_FLAG => rs_role_rec.l_seeded_flag,
1112 X_MEMBER_FLAG => rs_role_rec.l_member_flag,
1113 X_ADMIN_FLAG => rs_role_rec.l_admin_flag,
1114 X_LEAD_FLAG => rs_role_rec.l_lead_flag,
1115 X_MANAGER_FLAG => rs_role_rec.l_manager_flag,
1116 X_ACTIVE_FLAG => rs_role_rec.l_active_flag,
1117 X_ATTRIBUTE1 => rs_role_rec.l_attribute1,
1118 X_ATTRIBUTE2 => rs_role_rec.l_attribute2,
1119 X_ROLE_NAME => rs_role_rec.l_role_name,
1120 X_ROLE_DESC => rs_role_rec.l_role_desc,
1121 X_OBJECT_VERSION_NUMBER=> l_object_version_number,
1122 X_LAST_UPDATE_DATE => sysdate,
1123 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
1124 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
1125 );
1126
1127 p_object_version_number := l_object_version_number;
1128
1129 EXCEPTION
1130 WHEN NO_DATA_FOUND THEN
1131 CLOSE c_rs_role_update;
1132 x_return_status := fnd_api.g_ret_sts_unexp_error;
1133 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1134 fnd_msg_pub.add;
1135 RAISE fnd_api.g_exc_unexpected_error;
1136 END;
1137 --dbms_output.put_line('Role Successfully Updated');
1138
1139 --Post Call to Customer User Hook
1140 IF jtf_usr_hks.ok_to_execute(
1141 'JTF_RS_ROLES_PVT',
1142 'UPDATE_RS_RESOURCE_ROLES',
1143 'A',
1144 'C')
1145 THEN
1146 jtf_rs_roles_cuhk.update_rs_resource_roles_post(
1147 P_ROLE_ID => l_role_id,
1148 P_ROLE_TYPE_CODE => l_role_type_code,
1149 P_ROLE_CODE => l_role_code,
1150 P_ROLE_NAME => l_role_name,
1151 P_ROLE_DESC => l_role_desc,
1152 P_ACTIVE_FLAG => l_active_flag,
1153 P_SEEDED_FLAG => l_seeded_flag,
1154 P_MEMBER_FLAG => l_member_flag,
1155 P_ADMIN_FLAG => l_admin_flag,
1156 P_LEAD_FLAG => l_lead_flag,
1157 P_MANAGER_FLAG => l_manager_flag,
1158 X_RETURN_STATUS => x_return_status,
1159 X_MSG_COUNT => x_msg_count,
1160 X_MSG_DATA => x_msg_data
1161 );
1162 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1163 x_return_status := fnd_api.g_ret_sts_unexp_error;
1164 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1165 fnd_msg_pub.add;
1166 RAISE fnd_api.g_exc_unexpected_error;
1167 END IF;
1168 END IF;
1169
1170 --Post Call to Vertical User Hook
1171 IF jtf_usr_hks.ok_to_execute(
1172 'JTF_RS_ROLES_PVT',
1173 'UPDATE_RS_RESOURCE_ROLES',
1174 'A',
1175 'V')
1176 THEN
1177 jtf_rs_roles_vuhk.update_rs_resource_roles_post(
1178 P_ROLE_ID => l_role_id,
1179 P_ROLE_TYPE_CODE => l_role_type_code,
1180 P_ROLE_CODE => l_role_code,
1181 P_ROLE_NAME => l_role_name,
1182 P_ROLE_DESC => l_role_desc,
1183 P_ACTIVE_FLAG => l_active_flag,
1184 P_SEEDED_FLAG => l_seeded_flag,
1185 P_MEMBER_FLAG => l_member_flag,
1186 P_ADMIN_FLAG => l_admin_flag,
1187 P_LEAD_FLAG => l_lead_flag,
1188 P_MANAGER_FLAG => l_manager_flag,
1189 X_RETURN_STATUS => x_return_status,
1190 X_MSG_COUNT => x_msg_count,
1191 X_MSG_DATA => x_msg_data
1192 );
1193 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1194 x_return_status := fnd_api.g_ret_sts_unexp_error;
1195 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1196 fnd_msg_pub.add;
1197 RAISE fnd_api.g_exc_unexpected_error;
1198 END IF;
1199 END IF;
1200
1201 --Post Call to Vertical User Hook
1202 IF jtf_usr_hks.ok_to_execute(
1203 'JTF_RS_ROLES_PVT',
1204 'UPDATE_RS_RESOURCE_ROLES',
1205 'A',
1206 'I')
1207 THEN
1208 jtf_rs_roles_iuhk.update_rs_resource_roles_post(
1209 P_ROLE_ID => l_role_id,
1210 P_ROLE_TYPE_CODE => l_role_type_code,
1211 P_ROLE_CODE => l_role_code,
1212 P_ROLE_NAME => l_role_name,
1213 P_ROLE_DESC => l_role_desc,
1214 P_ACTIVE_FLAG => l_active_flag,
1215 P_SEEDED_FLAG => l_seeded_flag,
1216 P_MEMBER_FLAG => l_member_flag,
1217 P_ADMIN_FLAG => l_admin_flag,
1218 P_LEAD_FLAG => l_lead_flag,
1219 P_MANAGER_FLAG => l_manager_flag,
1220 X_RETURN_STATUS => x_return_status,
1221 X_MSG_COUNT => x_msg_count,
1222 X_MSG_DATA => x_msg_data
1223 );
1224 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1225 x_return_status := fnd_api.g_ret_sts_unexp_error;
1226 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1227 fnd_msg_pub.add;
1228 RAISE fnd_api.g_exc_unexpected_error;
1229 END IF;
1230 END IF;
1231
1232 /* Standard call for Message Generation */
1233
1234 IF jtf_usr_hks.ok_to_execute(
1235 'JTF_RS_ROLES_PVT',
1236 'UPDATE_RS_RESOURCE_ROLES',
1237 'M',
1238 'M')
1239 THEN
1240 IF (jtf_rs_roles_cuhk.ok_to_generate_msg(
1241 p_role_id => l_role_id,
1242 x_return_status => x_return_status) )
1243 THEN
1244
1245 /* Get the bind data id for the Business Object Instance */
1246 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1247
1248 /* Set bind values for the bind variables in the Business Object SQL */
1249 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_id', l_role_id, 'S', 'N');
1250
1251 /* Call the message generation API */
1252 jtf_usr_hks.generate_message(
1253 p_prod_code => 'JTF',
1254 p_bus_obj_code => 'RS_ROLE',
1255 p_action_code => 'U',
1256 p_bind_data_id => l_bind_data_id,
1257 x_return_code => x_return_status);
1258
1259 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1260 --dbms_output.put_line('Returned Error status from the Message Generation API');
1261 x_return_status := fnd_api.g_ret_sts_unexp_error;
1262 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1263 fnd_msg_pub.add;
1264 RAISE fnd_api.g_exc_unexpected_error;
1265 END IF;
1266 END IF;
1267 END IF;
1268
1269 IF fnd_api.to_boolean(p_commit) THEN
1270 COMMIT WORK;
1271 END IF;
1272
1273 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1274
1275 EXCEPTION
1276 WHEN fnd_api.g_exc_unexpected_error THEN
1277 --DBMS_OUTPUT.put_line (' ========================================== ');
1278 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1279 ROLLBACK TO update_rs_resource_roles_pvt;
1280 x_return_status := fnd_api.g_ret_sts_unexp_error;
1281 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1282 WHEN OTHERS THEN
1283 --DBMS_OUTPUT.put_line (' ========================================== ');
1284 --DBMS_OUTPUT.put_line (' =========== Raised Others in Update Resource Role Pub =============');
1285 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1286 ROLLBACK TO update_rs_resource_roles_pvt;
1287 x_return_status := fnd_api.g_ret_sts_unexp_error;
1288 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1289
1290 END update_rs_resource_roles;
1291
1292
1293 /* Procedure to delete the resource roles. */
1294
1295 PROCEDURE delete_rs_resource_roles
1296 (P_API_VERSION IN NUMBER,
1297 P_INIT_MSG_LIST IN VARCHAR2,
1298 P_COMMIT IN VARCHAR2,
1299 P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
1300 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
1301 P_OBJECT_VERSION_NUMBER IN JTF_RS_ROLES_B.OBJECT_VERSION_NUMBER%TYPE,
1302 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1303 X_MSG_COUNT OUT NOCOPY NUMBER,
1304 X_MSG_DATA OUT NOCOPY VARCHAR2
1305 ) IS
1306
1307 l_api_version CONSTANT NUMBER := 1.0;
1308 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_ROLES';
1309 l_role_id jtf_rs_roles_vl.role_id%type := p_role_id;
1310 l_role_code jtf_rs_roles_vl.role_code%type := p_role_code;
1311 L_BIND_DATA_ID NUMBER;
1312 l_object_version_number jtf_rs_roles_vl.object_version_number%type := p_object_version_number;
1313 -- added for NOCOPY handle in JTF_RESOURCE_UTL
1314 l_role_id_out jtf_rs_roles_vl.role_id%type;
1315
1316 l_return_status VARCHAR2(2000);
1317 l_msg_count NUMBER;
1318 l_msg_data VARCHAR2(2000);
1319
1320 FUNCTION role_used(p_role_id IN NUMBER) return boolean IS
1321 l_exists varchar2(1) := 'N';
1322 CURSOR c_role IS
1323 SELECT 'Y' from jtf_rs_role_relations
1324 WHERE role_id = p_role_id
1325 AND NVL(DELETE_FLAG,'N') <> 'Y';
1326 BEGIN
1327 OPEN c_role;
1328 FETCH c_role INTO l_exists;
1329 CLOSE c_role;
1330 IF l_exists = 'Y' THEN
1331 return true;
1332 END IF;
1333 return false;
1334 END role_used;
1335
1336 FUNCTION get_role_name(p_role_id IN NUMBER) return VARCHAR2 IS
1337 role_name jtf_Rs_roles_tl.role_name%type;
1338 cursor c_role_name is
1339 SELECT role_name from jtf_rs_roles_vl
1340 WHERE role_id = p_role_id;
1341 BEGIN
1342 OPEN c_role_name;
1343 FETCH c_role_name into role_name;
1344 CLOSE c_role_name;
1345 return role_name;
1346 END;
1347
1348 BEGIN
1349 SAVEPOINT delete_rs_resource_roles_pvt;
1350 x_return_status := fnd_api.g_ret_sts_success;
1351 --DBMS_OUTPUT.put_line(' Started Delete Resource Roles Pub ');
1352 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1353 RAISE fnd_api.g_exc_unexpected_error;
1354 END IF;
1355 IF fnd_api.to_boolean(p_init_msg_list) THEN
1356 fnd_msg_pub.initialize;
1357 END IF;
1358
1359 IF role_used(p_role_id) THEN
1360 x_return_status := fnd_api.g_ret_sts_unexp_error;
1361 fnd_message.set_name('JTF', 'JTF_RS_ROLE_USED');
1362 fnd_message.set_token('ROLE_NAME', get_role_name(p_role_id));
1363 fnd_msg_pub.add;
1364 RAISE fnd_api.g_exc_unexpected_error;
1365 END IF;
1366 --Pre Call to Customer User Hook
1367 IF jtf_usr_hks.ok_to_execute(
1368 'JTF_RS_ROLES_PVT',
1369 'DELETE_RS_RESOURCE_ROLES',
1370 'B',
1371 'C')
1372 THEN
1373 jtf_rs_roles_cuhk.delete_rs_resource_roles_pre(
1374 P_ROLE_ID => l_role_id,
1375 P_ROLE_CODE => l_role_code,
1376 X_RETURN_STATUS => x_return_status,
1377 X_MSG_COUNT => x_msg_count,
1378 X_MSG_DATA => x_msg_data
1379 );
1380 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1381 x_return_status := fnd_api.g_ret_sts_unexp_error;
1382 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1383 fnd_msg_pub.add;
1384 RAISE fnd_api.g_exc_unexpected_error;
1385 END IF;
1386 END IF;
1387
1388 --Pre Call to Vertical User Hook
1389 IF jtf_usr_hks.ok_to_execute(
1390 'JTF_RS_ROLES_PVT',
1391 'DELETE_RS_RESOURCE_ROLES',
1392 'B',
1393 'V')
1394 THEN
1395 jtf_rs_roles_vuhk.delete_rs_resource_roles_pre(
1396 P_ROLE_ID => l_role_id,
1397 P_ROLE_CODE => l_role_code,
1398 X_RETURN_STATUS => x_return_status,
1399 X_MSG_COUNT => x_msg_count,
1400 X_MSG_DATA => x_msg_data
1401 );
1402
1403 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1404 x_return_status := fnd_api.g_ret_sts_unexp_error;
1405 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1406 fnd_msg_pub.add;
1407 RAISE fnd_api.g_exc_unexpected_error;
1408 END IF;
1409 END IF;
1410
1411 --Pre Call to Internal User Hook
1412 IF jtf_usr_hks.ok_to_execute(
1413 'JTF_RS_ROLES_PVT',
1414 'DELETE_RS_RESOURCE_ROLES',
1415 'B',
1416 'I')
1417 THEN
1418 jtf_rs_roles_iuhk.delete_rs_resource_roles_pre(
1419 P_ROLE_ID => l_role_id,
1420 P_ROLE_CODE => l_role_code,
1421 X_RETURN_STATUS => x_return_status,
1422 X_MSG_COUNT => x_msg_count,
1423 X_MSG_DATA => x_msg_data
1424 );
1425 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1426 x_return_status := fnd_api.g_ret_sts_unexp_error;
1427 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1428 fnd_msg_pub.add;
1429 RAISE fnd_api.g_exc_unexpected_error;
1430 END IF;
1431 END IF;
1432
1433 --Put all Validations here
1434
1435 --Validate the Resource Role
1436
1437 BEGIN
1438
1439 jtf_resource_utl.validate_resource_role(
1440 p_role_id => l_role_id,
1441 p_role_code => l_role_code,
1442 x_return_status => x_return_status,
1443 x_role_id => l_role_id_out
1444 );
1445 -- added for NOCOPY
1446 l_role_id := l_role_id_out;
1447
1448 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1449 x_return_status := fnd_api.g_ret_sts_unexp_error;
1450 RAISE fnd_api.g_exc_unexpected_error;
1451 END IF;
1452
1453 END;
1454
1455 --Call the Lock Row Table Handler before deleting the Record
1456
1457 jtf_rs_roles_pkg.lock_row (
1458 X_ROLE_ID => l_role_id,
1459 X_OBJECT_VERSION_NUMBER => l_object_version_number
1460 );
1461
1462 --Call the Table Handler to Delete the Row from the jtf_rs_roles Table
1463
1464 jtf_rs_roles_pkg.delete_row (
1465 X_ROLE_ID => l_role_id
1466 );
1467
1468 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1469 --dbms_output.put_line('Failed status from call to private procedure');
1470 RAISE fnd_api.g_exc_unexpected_error;
1471 END IF;
1472
1473 --Post Call to Customer User Hook
1474 IF jtf_usr_hks.ok_to_execute(
1475 'JTF_RS_ROLES_PVT',
1476 'DELETE_RS_RESOURCE_ROLES',
1477 'A',
1478 'C')
1479 THEN
1480 jtf_rs_roles_cuhk.delete_rs_resource_roles_post(
1481 P_ROLE_ID => l_role_id,
1482 P_ROLE_CODE => l_role_code,
1483 X_RETURN_STATUS => x_return_status,
1484 X_MSG_COUNT => x_msg_count,
1485 X_MSG_DATA => x_msg_data
1486 );
1487 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1488 x_return_status := fnd_api.g_ret_sts_unexp_error;
1489 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1490 fnd_msg_pub.add;
1491 RAISE fnd_api.g_exc_unexpected_error;
1492 END IF;
1493 END IF;
1494
1495 --Post Call to Vertical User Hook
1496 IF jtf_usr_hks.ok_to_execute(
1497 'JTF_RS_ROLES_PVT',
1498 'DELETE_RS_RESOURCE_ROLES',
1499 'A',
1500 'V')
1501 THEN
1502 jtf_rs_roles_vuhk.delete_rs_resource_roles_post(
1503 P_ROLE_ID => l_role_id,
1504 P_ROLE_CODE => l_role_code,
1505 X_RETURN_STATUS => x_return_status,
1506 X_MSG_COUNT => x_msg_count,
1507 X_MSG_DATA => x_msg_data
1508 );
1509 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1510 x_return_status := fnd_api.g_ret_sts_unexp_error;
1511 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1512 fnd_msg_pub.add;
1513 RAISE fnd_api.g_exc_unexpected_error;
1514 END IF;
1515 END IF;
1516
1517 --Post Call to Internal User Hook
1518 IF jtf_usr_hks.ok_to_execute(
1519 'JTF_RS_ROLES_PVT',
1520 'DELETE_RS_RESOURCE_ROLES',
1521 'A',
1522 'I')
1523 THEN
1524 jtf_rs_roles_iuhk.delete_rs_resource_roles_post(
1525 P_ROLE_ID => l_role_id,
1526 P_ROLE_CODE => l_role_code,
1527 X_RETURN_STATUS => x_return_status,
1528 X_MSG_COUNT => x_msg_count,
1529 X_MSG_DATA => x_msg_data
1530 );
1531 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1532 x_return_status := fnd_api.g_ret_sts_unexp_error;
1533 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1534 fnd_msg_pub.add;
1535 RAISE fnd_api.g_exc_unexpected_error;
1536 END IF;
1537 END IF;
1538
1539 /* Standard call for Message Generation */
1540
1541 IF jtf_usr_hks.ok_to_execute(
1542 'JTF_RS_ROLES_PVT',
1543 'DELETE_RS_RESOURCE_ROLES',
1544 'M',
1545 'M')
1546 THEN
1547 IF (jtf_rs_roles_cuhk.ok_to_generate_msg(
1548 p_role_id => l_role_id,
1549 x_return_status => x_return_status) )
1550 THEN
1551
1552 /* Get the bind data id for the Business Object Instance */
1553 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1554
1555 /* Set bind values for the bind variables in the Business Object SQL */
1556 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_id', l_role_id, 'S', 'N');
1557
1558 /* Call the message generation API */
1559 jtf_usr_hks.generate_message(
1560 p_prod_code => 'JTF',
1561 p_bus_obj_code => 'RS_ROLE',
1562 p_action_code => 'D',
1563 p_bind_data_id => l_bind_data_id,
1564 x_return_code => x_return_status);
1565
1566 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1567 --dbms_output.put_line('Returned Error status from the Message Generation API');
1568 x_return_status := fnd_api.g_ret_sts_unexp_error;
1569 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1570 fnd_msg_pub.add;
1571 RAISE fnd_api.g_exc_unexpected_error;
1572 END IF;
1573 END IF;
1574 END IF;
1575
1576 IF fnd_api.to_boolean(p_commit) THEN
1577 COMMIT WORK;
1578 END IF;
1579
1580 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1581
1582 /* Calling publish API to raise delete resource role event. */
1583 /* added by baianand on 04/02/2003 */
1584
1585 begin
1586 jtf_rs_wf_events_pub.delete_resource_role
1587 (p_api_version => 1.0
1588 ,p_init_msg_list => fnd_api.g_false
1589 ,p_commit => fnd_api.g_false
1590 ,p_role_id => l_role_id
1591 ,x_return_status => l_return_status
1592 ,x_msg_count => l_msg_count
1593 ,x_msg_data => l_msg_data);
1594
1595 EXCEPTION when others then
1596 null;
1597 end;
1598
1599 /* End of publish API call */
1600
1601 EXCEPTION
1602 WHEN fnd_api.g_exc_unexpected_error THEN
1603 --DBMS_OUTPUT.put_line (' ========================================== ');
1604 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1605 ROLLBACK TO delete_rs_resource_roles_pvt;
1606 x_return_status := fnd_api.g_ret_sts_unexp_error;
1607 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1608 WHEN OTHERS THEN
1609 --DBMS_OUTPUT.put_line (' ========================================== ');
1610 --DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Resource Role Pub =============');
1611 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1612 ROLLBACK TO delete_rs_resource_roles_pvt;
1613 x_return_status := fnd_api.g_ret_sts_unexp_error;
1614 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1615
1616 END delete_rs_resource_roles;
1617
1618 END jtf_rs_roles_pvt;