[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_RELATE_PUB
Source
1 PACKAGE BODY jtf_rs_group_relate_pub AS
2 /* $Header: jtfrspfb.pls 120.0 2005/05/11 08:21:07 appldev ship $ */
3
4
5 /*****************************************************************************************
6 This package body defines the procedures for managing resource group relations.
7 Its main procedures are as following:
8 Create Resource Group Relate
9 Update Resource Group Relate
10 Delete Resource Group Relate
11 This package validates the input parameters to these procedures and then
12 Calls corresponding procedures from jtf_rs_group_relate_pvt to do business
13 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_GROUP_RELATE_PUB';
19
20
21 /* Procedure to create the resource group relation
22 based on input values passed by calling routines. */
23
24 PROCEDURE create_resource_group_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_GROUP_ID IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
29 P_GROUP_NUMBER IN JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE,
30 P_RELATED_GROUP_ID IN JTF_RS_GRP_RELATIONS.RELATED_GROUP_ID%TYPE,
31 P_RELATED_GROUP_NUMBER IN JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE,
32 P_RELATION_TYPE IN JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE,
33 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
34 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
35 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
36 X_MSG_COUNT OUT NOCOPY NUMBER,
37 X_MSG_DATA OUT NOCOPY VARCHAR2,
38 X_GROUP_RELATE_ID OUT NOCOPY JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE
39 ) IS
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP_RELATE';
41 l_api_version CONSTANT NUMBER :=1.0;
42
43 l_return_status VARCHAR2(200);
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(200);
46 l_rowid VARCHAR2(200);
47 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE;
48
49 l_return_code VARCHAR2(100);
50 l_count NUMBER;
51 l_data VARCHAR2(200);
52 l_date Date;
53 l_user_id Number;
54 l_login_id Number;
55
56 l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE;
57 l_related_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE;
58
59 CURSOR grp_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
60 l_group_number JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE)
61 IS
62 SELECT group_id
63 FROM jtf_rs_groups_b
64 WHERE group_id = l_group_id
65 OR group_number = l_group_number;
66
67 grp_rec grp_cur%rowtype;
68
69
70 CURSOR chk_rel_cur(l_relation_type JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE)
71 IS
72 SELECT 'X'
73 FROM fnd_lookups
74 WHERE lookup_type = 'JTF_RS_RELATION_TYPE'
75 AND UPPER(lookup_code) = UPPER(l_relation_type);
76
77 chk_rel_rec chk_rel_cur%rowtype;
78
79
80 BEGIN
81 --Standard Start of API SAVEPOINT
82 SAVEPOINT GROUP_RELATE_SP;
83
84 x_return_status := fnd_api.g_ret_sts_success;
85
86 --Standard Call to check API compatibility
87 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
93 IF FND_API.To_boolean(P_INIT_MSG_LIST)
94 THEN
95 FND_MSG_PUB.Initialize;
96 END IF;
97
98 --GET USER ID AND SYSDATE
99 l_date := sysdate;
100 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
101 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
102
103 --check for group id/number and get valid group id
104 OPEN grp_cur(p_group_id,
105 p_group_number);
106 FETCH grp_cur INTO grp_rec;
107 IF(grp_cur%NOTFOUND)
108 THEN
109 x_return_status := fnd_api.g_ret_sts_unexp_error;
110 fnd_message.set_name ('JTF', 'JTF_RS_GRP_NOTFOUND_ERR');
111 FND_MSG_PUB.add;
112 RAISE fnd_api.g_exc_unexpected_error;
113 ELSE
114 l_group_id := grp_rec.group_id;
115 END IF;
116 CLOSE grp_cur;
117
118 --check for related group id/number and get valid group id
119 OPEN grp_cur(p_related_group_id,
120 p_related_group_number);
121 FETCH grp_cur INTO grp_rec;
122 IF(grp_cur%NOTFOUND)
123 THEN
124 x_return_status := fnd_api.g_ret_sts_unexp_error;
125 fnd_message.set_name ('JTF', 'JTF_RS_REL_GRP_NOTFOUND_ERR');
126 FND_MSG_PUB.add;
127 RAISE fnd_api.g_exc_unexpected_error;
128 ELSE
129 l_related_group_id := grp_rec.group_id;
130 END IF;
131 CLOSE grp_cur;
132
133 --check for relation type
134 OPEN chk_rel_cur(p_relation_type);
135 FETCH chk_rel_cur INTO chk_rel_rec;
136 IF(chk_rel_cur%NOTFOUND)
137 THEN
138 x_return_status := fnd_api.g_ret_sts_unexp_error;
139 fnd_message.set_name ('JTF', 'JTF_RS_REL_TYP_NOTFOUND_ERR');
140 FND_MSG_PUB.add;
141 RAISE fnd_api.g_exc_unexpected_error;
142 END IF;
143 CLOSE chk_rel_cur;
144
145 --call private api for insert
146 jtf_rs_group_relate_pvt.create_resource_group_relate(
147 P_API_VERSION => 1.0,
148 P_INIT_MSG_LIST => p_init_msg_list,
149 P_COMMIT => null,
150 P_GROUP_ID => l_group_id,
151 P_RELATED_GROUP_ID => l_related_group_id,
152 P_RELATION_TYPE => p_relation_type,
153 P_START_DATE_ACTIVE => p_start_date_active,
154 P_END_DATE_ACTIVE => p_end_date_active,
155 X_RETURN_STATUS => l_return_status,
156 X_MSG_COUNT => l_msg_count,
157 X_MSG_DATA => l_msg_data ,
158 X_GROUP_RELATE_ID => l_group_relate_id);
159
160 IF(l_return_status <> fnd_api.g_ret_sts_success)
161 THEN
162 x_return_status := fnd_api.g_ret_sts_unexp_error;
163 RAISE fnd_api.g_exc_unexpected_error;
164 END IF;
165
166 X_GROUP_RELATE_ID := l_group_relate_id;
167
168
169
170
171 --standard commit
172 IF fnd_api.to_boolean (p_commit)
173 THEN
174 COMMIT WORK;
175 END IF;
176
177
178 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
179
180 EXCEPTION
181 WHEN fnd_api.g_exc_unexpected_error
182 THEN
183 ROLLBACK TO GROUP_RELATE_SP;
184 x_return_status := fnd_api.g_ret_sts_unexp_error;
185 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
186 WHEN OTHERS
187 THEN
188 ROLLBACK TO GROUP_RELATE_SP;
189 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
190 fnd_message.set_token('P_SQLCODE',SQLCODE);
191 fnd_message.set_token('P_SQLERRM',SQLERRM);
192 fnd_message.set_token('P_API_NAME', l_api_name);
193 FND_MSG_PUB.add;
194 x_return_status := fnd_api.g_ret_sts_unexp_error;
195 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
196
197
198
199 END create_resource_group_relate;
200
201
202 /* Procedure to update the resource group relation
203 based on input values passed by calling routines. */
204
205 PROCEDURE update_resource_group_relate
206 (P_API_VERSION IN NUMBER,
207 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
208 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
209 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
210 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
211 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
212 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
213 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
214 X_MSG_COUNT OUT NOCOPY NUMBER,
215 X_MSG_DATA OUT NOCOPY VARCHAR2
216 ) IS
217 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP_RELATE';
218 l_api_version CONSTANT NUMBER :=1.0;
219
220 L_OBJECT_VERSION_NUMBER JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
221 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE := P_GROUP_RELATE_ID;
222 l_return_status VARCHAR2(200);
223 l_msg_count NUMBER;
224 l_msg_data VARCHAR2(200);
225 l_rowid VARCHAR2(200);
226
227 l_return_code VARCHAR2(100);
228 l_count NUMBER;
229 l_data VARCHAR2(200);
230 l_date Date;
231 l_user_id Number;
232 l_login_id Number;
233
234 CURSOR val_grp_rel_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE)
235 IS
236 SELECT 'X'
237 FROM jtf_rs_grp_relations
238 where group_relate_id = l_group_relate_id;
239
240 dummy VARCHAR2(30);
241
242
243
244 BEGIN
245 --Standard Start of API SAVEPOINT
246 SAVEPOINT GROUP_RELATE_SP;
247
248 x_return_status := fnd_api.g_ret_sts_success;
249
250 --Standard Call to check API compatibility
251 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
257 IF FND_API.To_boolean(P_INIT_MSG_LIST)
258 THEN
259 FND_MSG_PUB.Initialize;
260 END IF;
261
262 --GET USER ID AND SYSDATE
263 l_date := sysdate;
264 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
265 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
266 -------------------
267 OPEN val_grp_rel_cur(p_group_relate_id);
268 FETCH val_grp_rel_cur INTO dummy;
269 IF(val_grp_rel_cur%NOTFOUND)
270 THEN
271 x_return_status := fnd_api.g_ret_sts_unexp_error;
272 fnd_message.set_name ('JTF', 'JTF_RS_GRP_REL_NOTFOUND_ERR');
273 FND_MSG_PUB.add;
274 RAISE fnd_api.g_exc_unexpected_error;
275 END IF;
276 CLOSE val_grp_rel_cur;
277
278
279 --call private api for updation
280 jtf_rs_group_relate_pvt.update_resource_group_relate(
281 P_API_VERSION => 1.0,
282 P_INIT_MSG_LIST => p_init_msg_list,
283 P_COMMIT => null,
284 P_GROUP_RELATE_ID => l_group_relate_id,
285 P_START_DATE_ACTIVE => p_start_date_active,
286 P_END_DATE_ACTIVE => p_end_date_active,
287 P_OBJECT_VERSION_NUM => l_object_version_number,
288 X_RETURN_STATUS => l_return_status,
289 X_MSG_COUNT => l_msg_count,
290 X_MSG_DATA => l_msg_data );
291
292 p_object_version_num := l_object_version_number;
293
294 IF(l_return_status <> fnd_api.g_ret_sts_success)
295 THEN
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297 RAISE fnd_api.g_exc_unexpected_error;
298
299 END IF;
300
301
302 --standard commit
303 IF fnd_api.to_boolean (p_commit)
304 THEN
305 COMMIT WORK;
306 END IF;
307
308
309 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
310
311 EXCEPTION
312 WHEN fnd_api.g_exc_unexpected_error
313 THEN
314 ROLLBACK TO GROUP_RELATE_SP;
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
317 WHEN OTHERS
318 THEN
319 ROLLBACK TO GROUP_RELATE_SP;
320 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
321 fnd_message.set_token('P_SQLCODE',SQLCODE);
322 fnd_message.set_token('P_SQLERRM',SQLERRM);
323 fnd_message.set_token('P_API_NAME', l_api_name);
324 FND_MSG_PUB.add;
325 x_return_status := fnd_api.g_ret_sts_unexp_error;
326 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
327
328
329
330 END update_resource_group_relate;
331
332
333
334 /* Procedure to delete the resource group relation. */
335
336 PROCEDURE delete_resource_group_relate
337 (P_API_VERSION IN NUMBER,
338 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
339 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
340 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
341 P_OBJECT_VERSION_NUM IN JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
342 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
343 X_MSG_COUNT OUT NOCOPY NUMBER,
344 X_MSG_DATA OUT NOCOPY VARCHAR2
345 ) IS
346
347 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_RELATE';
348 l_api_version CONSTANT NUMBER :=1.0;
349
350 L_OBJECT_VERSION_NUMBER JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
351 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE;
352
353 l_return_status VARCHAR2(200);
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(200);
356 l_rowid VARCHAR2(200);
357
358 l_return_code VARCHAR2(100);
359 l_count NUMBER;
360 l_data VARCHAR2(200);
361 l_date Date;
362 l_user_id Number;
363 l_login_id Number;
364
365 CURSOR val_grp_rel_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE)
366 IS
367 SELECT 'X'
368 FROM jtf_rs_grp_relations
369 where group_relate_id = l_group_relate_id;
370
371 dummy VARCHAR2(30);
372
373
374
375 BEGIN
376 --Standard Start of API SAVEPOINT
377 SAVEPOINT GROUP_RELATE_SP;
378
379 x_return_status := fnd_api.g_ret_sts_success;
380
381 --Standard Call to check API compatibility
382 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
383 THEN
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 END IF;
386
387 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
388 IF FND_API.To_boolean(P_INIT_MSG_LIST)
389 THEN
390 FND_MSG_PUB.Initialize;
391 END IF;
392
393 --GET USER ID AND SYSDATE
394 l_date := sysdate;
395 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
396 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
397 -------------------
398 OPEN val_grp_rel_cur(p_group_relate_id);
399 FETCH val_grp_rel_cur INTO dummy;
400 IF(val_grp_rel_cur%NOTFOUND)
401 THEN
402 x_return_status := fnd_api.g_ret_sts_unexp_error;
403 fnd_message.set_name ('JTF', 'JTF_RS_GRP_REL_NOTFOUND_ERR');
404 FND_MSG_PUB.add;
405 RAISE fnd_api.g_exc_unexpected_error;
406 END IF;
407 CLOSE val_grp_rel_cur;
408
409
410 --call private api for updation
411 jtf_rs_group_relate_pvt.delete_resource_group_relate(
412 P_API_VERSION => 1.0,
413 P_INIT_MSG_LIST => p_init_msg_list,
414 P_COMMIT => null,
415 P_GROUP_RELATE_ID => p_group_relate_id,
416 P_OBJECT_VERSION_NUM => l_object_version_number,
417 X_RETURN_STATUS => l_return_status,
418 X_MSG_COUNT => l_msg_count,
419 X_MSG_DATA => l_msg_data );
420
421 IF(l_return_status <> fnd_api.g_ret_sts_success)
422 THEN
423 x_return_status := fnd_api.g_ret_sts_unexp_error;
424 RAISE fnd_api.g_exc_unexpected_error;
425
426 END IF;
427
428
429 --standard commit
430 IF fnd_api.to_boolean (p_commit)
431 THEN
432 COMMIT WORK;
433 END IF;
434
435
436 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
437
438 EXCEPTION
439 WHEN fnd_api.g_exc_unexpected_error
440 THEN
441 ROLLBACK TO GROUP_RELATE_SP;
442 x_return_status := fnd_api.g_ret_sts_unexp_error;
443 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
444 WHEN OTHERS
445 THEN
446 ROLLBACK TO GROUP_RELATE_SP;
447 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
448 fnd_message.set_token('P_SQLCODE',SQLCODE);
449 fnd_message.set_token('P_SQLERRM',SQLERRM);
450 fnd_message.set_token('P_API_NAME', l_api_name);
451 FND_MSG_PUB.add;
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
454
455
456
457 END delete_resource_group_relate;
458
459
460
461 END jtf_rs_group_relate_pub;