[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLE_RELATE_PUB
Source
1 PACKAGE BODY jtf_rs_role_relate_pub AS
2 /* $Header: jtfrsplb.pls 120.2 2006/01/27 17:48:23 baianand ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resource roles, like
6 create and update resource roles.
7 Its main procedures are as following:
8 Create Resource Role Relate
9 Update Resource Role Relate
10 Delete Resource Role Relate
11 This package validates the input parameters to these procedures and then
12 Calls corresponding procedures from jtf_rs_role_relate_pvt
13 to do business validations and to do actual inserts, updates and deletes into tables.
14 ******************************************************************************************/
15
16 /* Package variables. */
17
18 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_ROLE_RELATE_PUB';
19
20
21 /* Procedure to create the resource roles
22 based on input values passed by calling routines. */
23
24 PROCEDURE create_resource_role_relate
25 (P_API_VERSION IN NUMBER,
26 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
27 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
28 P_ROLE_RESOURCE_TYPE IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE,
29 P_ROLE_RESOURCE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
30 P_ROLE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
31 P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
32 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
33 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
34 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
35 X_MSG_COUNT OUT NOCOPY NUMBER,
36 X_MSG_DATA OUT NOCOPY VARCHAR2,
37 X_ROLE_RELATE_ID OUT NOCOPY JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE
38 ) IS
39
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_ROLE_RELATE';
41 l_api_version CONSTANT NUMBER :=1.0;
42
43 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE := 'X';
44 l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE := p_role_resource_id;
45 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE := p_role_id;
46 l_role_code JTF_RS_ROLES_B.ROLE_CODE%TYPE := p_role_code;
47 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := p_start_date_active;
48 l_end_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := p_end_date_active;
49
50 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
51 l_return_status VARCHAR2(200);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(200);
54
55 CURSOR role_relate_type_cur
56 IS
57 select b.object_code
58 from jtf_objects_b a, jtf_object_usages b
59 where b.OBJECT_USER_CODE = 'RESOURCE_ROLES'
60 AND b.object_code = a.object_code;
61
62 role_relate_type_rec role_relate_type_cur%rowtype;
63
64 CURSOR role_cur(l_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
65 l_role_code JTF_RS_ROLES_B.ROLE_CODE%TYPE)
66 IS
67 select role_id
68 from jtf_rs_roles_b
69 where (role_id = l_role_id )
70 OR (role_code = l_role_code );
71
72 role_rec role_cur%rowtype;
73
74 L_FOUND BOOLEAN;
75
76 BEGIN
77 --Standard Start of API SAVEPOINT
78 SAVEPOINT ROLE_RELATE_SP;
79
80 x_return_status := fnd_api.g_ret_sts_success;
81
82 --Standard Call to check API compatibility
83 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
84 THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
89 IF FND_API.To_boolean(P_INIT_MSG_LIST)
90 THEN
91 FND_MSG_PUB.Initialize;
92 END IF;
93
94 --fetch the role resoure types and validate whether correct role_resource_type has been sent in as in param
95 FOR role_relate_type_rec IN role_relate_type_cur
96 LOOP
97
98 IF role_relate_type_rec.object_code = P_ROLE_RESOURCE_TYPE
99 THEN
100
101 l_role_resource_type := P_ROLE_RESOURCE_TYPE;
102 EXIT;
103 END IF;
104 END LOOP;
105
106 IF l_role_resource_type = 'X'
107 THEN
108 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_RL_RES_TYPE');
109 FND_MSG_PUB.add;
110 RAISE fnd_api.g_exc_error;
111 ELSE
112
113 --call procedure to check whether id exists for the object
114 JTF_RESOURCE_UTL.CHECK_OBJECT_EXISTENCE(
115 P_OBJECT_CODE => l_role_resource_type ,
116 P_SELECT_ID => l_role_resource_id ,
117 P_OBJECT_USER_CODE => 'RESOURCE_ROLES',
118 X_FOUND => L_FOUND,
119 X_RETURN_STATUS => L_RETURN_STATUS
120 );
121 IF(l_return_status <> fnd_api.g_ret_sts_success)
122 THEN
123 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
124 RAISE FND_API.G_EXC_ERROR;
125 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128 ELSE
129 IF NOT(l_found)
130 --if the id is not found then raise error
131 THEN
132 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_RR_RESOURCE');
133 FND_MSG_PUB.add;
134 RAISE fnd_api.g_exc_error;
135 END IF;
136 END IF;
137
138 END IF;
139
140 --check whether the role id passed in is valid
141 IF((l_role_id IS NOT NULL ) OR (l_role_code IS NOT NULL))
142 THEN
143
144 OPEN role_cur(l_role_id, l_role_code);
145 FETCH role_cur INTO role_rec;
146 IF (role_cur%NOTFOUND)
147 THEN
148 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_ROLE');
149 FND_MSG_PUB.add;
150 RAISE fnd_api.g_exc_error;
151 ELSE
152 l_role_id := role_rec.role_id;
153 END IF;
154 CLOSE role_cur;
155 ELSE
156 --if both role id and role code is null then raise error
157 fnd_message.set_name ('JTF', 'JTF_RS_ROLE');
158 FND_MSG_PUB.add;
159 RAISE fnd_api.g_exc_error;
160
161 END IF;
162
163
164 --call private api for inserting record
165 jtf_rs_role_relate_pvt.create_resource_role_relate
166 (P_API_VERSION => 1.0,
167 P_INIT_MSG_LIST => null,
168 P_COMMIT => null,
169 P_ROLE_RESOURCE_TYPE => l_role_resource_type,
170 P_ROLE_RESOURCE_ID => l_role_resource_id,
171 P_ROLE_ID => l_role_id,
172 P_START_DATE_ACTIVE => l_start_date_active,
173 P_END_DATE_ACTIVE => l_end_date_active,
174 P_ATTRIBUTE1 => null,
175 P_ATTRIBUTE2 => null,
176 P_ATTRIBUTE3 => null,
177 P_ATTRIBUTE4 => null,
178 P_ATTRIBUTE5 => null,
179 P_ATTRIBUTE6 => null,
180 P_ATTRIBUTE7 => null,
181 P_ATTRIBUTE8 => null,
182 P_ATTRIBUTE9 => null,
183 P_ATTRIBUTE10 => null,
184 P_ATTRIBUTE11 => null,
185 P_ATTRIBUTE12 => null,
186 P_ATTRIBUTE13 => null,
187 P_ATTRIBUTE14 => null,
188 P_ATTRIBUTE15 => null,
189 P_ATTRIBUTE_CATEGORY => null,
190 X_RETURN_STATUS => l_return_status,
191 X_MSG_COUNT => l_msg_count,
192 X_MSG_DATA => l_msg_data,
193 X_ROLE_RELATE_ID => l_role_relate_id);
194
195 X_RETURN_STATUS := l_return_status;
196 X_MSG_COUNT := l_msg_count;
197 X_MSG_DATA := l_msg_data;
198 X_ROLE_RELATE_ID := l_role_relate_id;
199
200
201
202 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
203 RAISE FND_API.G_EXC_ERROR;
204 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207
208
209 IF fnd_api.to_boolean (p_commit)
210 THEN
211 COMMIT WORK;
212 END IF;
213
214
215 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
216
217 EXCEPTION
218 WHEN fnd_api.g_exc_error THEN
219 ROLLBACK TO ROLE_RELATE_SP;
220 x_return_status := fnd_api.g_ret_sts_error;
221 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
222 p_data => x_msg_data);
223 WHEN fnd_api.g_exc_unexpected_error THEN
224 ROLLBACK TO ROLE_RELATE_SP;
225 x_return_status := fnd_api.g_ret_sts_unexp_error;
226 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
227 p_data => x_msg_data);
228 WHEN OTHERS THEN
229 ROLLBACK TO ROLE_RELATE_SP;
230 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
231 fnd_message.set_token('P_SQLCODE',SQLCODE);
232 fnd_message.set_token('P_SQLERRM',SQLERRM);
233 fnd_message.set_token('P_API_NAME', l_api_name);
234 FND_MSG_PUB.add;
235 x_return_status := fnd_api.g_ret_sts_unexp_error;
236 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
237 p_data => x_msg_data);
238
239 END create_resource_role_relate;
240
241
242
243 /* Procedure to update the resource roles
244 based on input values passed by calling routines. */
245
246 PROCEDURE update_resource_role_relate
247 (P_API_VERSION IN NUMBER,
248 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
249 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
250 P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
251 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
252 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE,
253 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
254 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
255 X_MSG_COUNT OUT NOCOPY NUMBER,
256 X_MSG_DATA OUT NOCOPY VARCHAR2
257 ) IS
258
259 CURSOR role_relate_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
260 IS
261 SELECT role_relate_id,
262 object_version_number
263 FROM jtf_rs_role_relations
264 WHERE role_relate_id = l_role_relate_id;
265
266 role_relate_rec role_relate_cur%rowtype;
267
268 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_ROLE_RELATE';
269 l_api_version CONSTANT NUMBER :=1.0;
270
271 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := p_start_date_active;
272 l_end_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := p_end_date_active;
273 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
274
275 l_object_version_number JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
276 l_return_status VARCHAR2(200);
277 l_msg_count NUMBER;
278 l_msg_data VARCHAR2(200);
279
280
281 BEGIN
282 --Standard Start of API SAVEPOINT
283 SAVEPOINT ROLE_RELATE_SP;
284
285 x_return_status := fnd_api.g_ret_sts_success;
286
287 --Standard Call to check API compatibility
288 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
294 IF FND_API.To_boolean(P_INIT_MSG_LIST)
295 THEN
296 FND_MSG_PUB.Initialize;
297 END IF;
298
299 open role_relate_cur(l_role_relate_id);
300 fetch role_relate_cur into role_relate_rec;
301 IF(role_relate_cur%found)
302 THEN
303
304 IF role_relate_rec.object_version_number = l_object_version_number
305 THEN
306 --call private api for update
307 jtf_rs_role_relate_pvt.update_resource_role_relate
308 (P_API_VERSION => 1.0,
309 P_INIT_MSG_LIST => null,
310 P_COMMIT => null,
311 P_ROLE_RELATE_ID => l_role_relate_id,
312 P_START_DATE_ACTIVE => l_start_date_active,
313 P_END_DATE_ACTIVE => l_end_date_active,
314 P_OBJECT_VERSION_NUM => l_object_version_number,
315 X_RETURN_STATUS => l_return_status,
316 X_MSG_COUNT => l_msg_count,
317 X_MSG_DATA => l_msg_data);
318
319 X_RETURN_STATUS := l_return_status;
320 X_MSG_COUNT := l_msg_count;
321 X_MSG_DATA := l_msg_data;
322
323 --if success then update object version number
324 IF (L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS)
325 THEN
326 p_object_version_num := l_object_version_number;
327 ELSE
328 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
329 RAISE FND_API.G_EXC_ERROR;
330 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333 END IF;
334 ELSE
335 fnd_message.set_name ('JTF', 'JTF_RS_OBJECT_VER_ERR');
336 FND_MSG_PUB.add;
337 RAISE fnd_api.g_exc_error;
338 END IF;
339 END IF;
340
341
342 IF fnd_api.to_boolean (p_commit)
343 THEN
344 COMMIT WORK;
345 END IF;
346
347
348 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
349
350 EXCEPTION
351 WHEN fnd_api.g_exc_error THEN
352 ROLLBACK TO ROLE_RELATE_SP;
353 x_return_status := fnd_api.g_ret_sts_error;
354 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
355 p_data => x_msg_data);
356 WHEN fnd_api.g_exc_unexpected_error THEN
357 ROLLBACK TO ROLE_RELATE_SP;
358 x_return_status := fnd_api.g_ret_sts_unexp_error;
359 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
360 p_data => x_msg_data);
361 WHEN OTHERS THEN
362 ROLLBACK TO ROLE_RELATE_SP;
363 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
364 fnd_message.set_token('P_SQLCODE',SQLCODE);
365 fnd_message.set_token('P_SQLERRM',SQLERRM);
366 fnd_message.set_token('P_API_NAME', l_api_name);
367 FND_MSG_PUB.add;
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
370 p_data => x_msg_data);
371
372 END UPDATE_RESOURCE_ROLE_RELATE;
373
374
375
376 /* Procedure to delete the resource roles. */
377
378 PROCEDURE delete_resource_role_relate
379 (P_API_VERSION IN NUMBER,
380 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
381 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
382 P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
383 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
384 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
385 X_MSG_COUNT OUT NOCOPY NUMBER,
386 X_MSG_DATA OUT NOCOPY VARCHAR2)
387 IS
388
389 CURSOR role_relate_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
390 IS
391 SELECT role_relate_id,
392 object_version_number
393 FROM jtf_rs_role_relations
394 WHERE role_relate_id = l_role_relate_id;
395
396 role_relate_rec role_relate_cur%rowtype;
397
398 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_ROLE_RELATE';
399 l_api_version CONSTANT NUMBER :=1.0;
400
401
402 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := p_role_relate_id;
403
404 l_object_version_number JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
405 l_return_status VARCHAR2(200);
406 l_msg_count NUMBER;
407 l_msg_data VARCHAR2(200);
408
409
410 BEGIN
411 --Standard Start of API SAVEPOINT
412 SAVEPOINT ROLE_RELATE_SP;
413
414 x_return_status := fnd_api.g_ret_sts_success;
415
416 --Standard Call to check API compatibility
417 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
418 THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421
422 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
423 IF FND_API.To_boolean(P_INIT_MSG_LIST)
424 THEN
425 FND_MSG_PUB.Initialize;
426 END IF;
427
428 open role_relate_cur(l_role_relate_id);
429 fetch role_relate_cur into role_relate_rec;
430 IF(role_relate_cur%found)
431 THEN
432
433 IF role_relate_rec.object_version_number = l_object_version_number
434 THEN
435 --call private api for DELETE
436 jtf_rs_role_relate_pvt.delete_resource_role_relate
437 (P_API_VERSION => 1.0,
438 P_INIT_MSG_LIST => null,
439 P_COMMIT => null,
440 P_ROLE_RELATE_ID => l_role_relate_id,
441 P_OBJECT_VERSION_NUM => l_object_version_number,
442 X_RETURN_STATUS => l_return_status,
443 X_MSG_COUNT => l_msg_count,
444 X_MSG_DATA => l_msg_data);
445
446 X_RETURN_STATUS := l_return_status;
447 X_MSG_COUNT := l_msg_count;
448 X_MSG_DATA := l_msg_data;
449
450 --if success then update object version number
451 IF (L_RETURN_STATUS <> fnd_api.g_ret_sts_success)
452 THEN
453 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
454 RAISE FND_API.G_EXC_ERROR;
455 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458 END IF;
459 ELSE
460 fnd_message.set_name ('JTF', 'JTF_RS_OBJECT_VER_ERR');
461 FND_MSG_PUB.add;
462 RAISE fnd_api.g_exc_error;
463 END IF;
464 END IF;
465
466
467 IF fnd_api.to_boolean (p_commit)
468 THEN
469 COMMIT WORK;
470 END IF;
471
472
473 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
474
475 EXCEPTION
476 WHEN fnd_api.g_exc_error THEN
477 ROLLBACK TO ROLE_RELATE_SP;
478 x_return_status := fnd_api.g_ret_sts_error;
479 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
480 p_data => x_msg_data);
481 WHEN fnd_api.g_exc_unexpected_error THEN
482 ROLLBACK TO ROLE_RELATE_SP;
483 x_return_status := fnd_api.g_ret_sts_unexp_error;
484 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
485 p_data => x_msg_data);
486 WHEN OTHERS THEN
487 ROLLBACK TO ROLE_RELATE_SP;
488 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
489 fnd_message.set_token('P_SQLCODE',SQLCODE);
490 fnd_message.set_token('P_SQLERRM',SQLERRM);
491 fnd_message.set_token('P_API_NAME', l_api_name);
492 FND_MSG_PUB.add;
493 x_return_status := fnd_api.g_ret_sts_unexp_error;
494 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
495 p_data => x_msg_data);
496
497 END delete_resource_role_relate;
498
499 END jtf_rs_role_relate_pub;