[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_ORGS_PVT
Source
1 PACKAGE BODY jtf_rs_group_orgs_pvt AS
2 /* $Header: jtfrseob.pls 120.0 2005/05/11 08:19:56 appldev noship $ */
3
4 /*****************************************************************************************
5 This is a private API that caller will invoke.
6 It provides procedures for managing resource group to HR Org mapping
7 Its main procedures are as following:
8 Create Group Org
9 Update Group Org
10 Delete Group Org
11 Calls to these procedures will invoke calls to table handlers which
12 do actual insert, update and delete into tables.
13 ******************************************************************************************/
14
15 --Package variables.
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_GROUP_ORGS_PVT';
18
19 /* Procedure to create the resource group - HR Org mapping
20 */
21
22 PROCEDURE create_group_org
23 (P_API_VERSION IN NUMBER,
24 P_INIT_MSG_LIST IN VARCHAR2,
25 P_COMMIT IN VARCHAR2,
26 P_GROUP_ID IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
27 P_ORGANIZATION_ID IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE,
28 P_ATTRIBUTE1 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE1%TYPE,
29 P_ATTRIBUTE2 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE2%TYPE,
30 P_ATTRIBUTE3 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE3%TYPE,
31 P_ATTRIBUTE4 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE4%TYPE,
32 P_ATTRIBUTE5 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE5%TYPE,
33 P_ATTRIBUTE6 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE6%TYPE,
34 P_ATTRIBUTE7 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE7%TYPE,
35 P_ATTRIBUTE8 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE8%TYPE,
36 P_ATTRIBUTE9 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE9%TYPE,
37 P_ATTRIBUTE10 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE10%TYPE,
38 P_ATTRIBUTE11 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE11%TYPE,
39 P_ATTRIBUTE12 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE12%TYPE,
40 P_ATTRIBUTE13 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE13%TYPE,
41 P_ATTRIBUTE14 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE14%TYPE,
42 P_ATTRIBUTE15 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE15%TYPE,
43 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE_CATEGORY%TYPE,
44 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
45 X_MSG_COUNT OUT NOCOPY NUMBER,
46 X_MSG_DATA OUT NOCOPY VARCHAR2
47 ) IS
48
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_GROUP_ORG';
51
52 l_return_status VARCHAR2(30);
53 l_msg_data VARCHAR2(2000);
54 l_msg_count NUMBER;
55
56 l_group_id JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE;
57 l_organization_id JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE;
58 l_group_start_date DATE;
59 l_group_end_date DATE;
60 l_group_name jtf_rs_groups_tl.group_name%TYPE;
61 l_org_start_date DATE;
62 l_org_end_date DATE;
63 l_org_name hr_all_organization_units_tl.NAME%TYPE;
64 l_mapping_exist VARCHAR2(30);
65
66 CURSOR c_validate_group(ll_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE) IS
67 SELECT start_date_active,
68 NVL(end_date_active, TRUNC(SYSDATE)+1),
69 group_name
70 FROM jtf_rs_groups_vl
71 WHERE group_id = ll_group_id
72 ;
73
74 CURSOR c_validate_hr_org(ll_org_id IN hr_all_organization_units.organization_id%TYPE) IS
75 SELECT hr.date_from,
76 NVL(hr.date_to, TRUNC(SYSDATE)+1),
77 hr.NAME
78 FROM hr_all_organization_units_vl hr
79 WHERE hr.organization_id = ll_org_id
80 ;
81
82 CURSOR c_check_dup_group_org_map(ll_group_id IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
83 ll_org_id IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE) IS
84 SELECT 'Y'
85 FROM jtf_rs_group_organizations
86 WHERE group_id = ll_group_id
87 AND organization_id = ll_org_id
88 ;
89
90 BEGIN
91
92 SAVEPOINT create_group_org;
93
94 --initialize variables
95 l_group_id := p_group_id;
96 l_organization_id := p_organization_id;
97 l_mapping_exist := 'N';
98 x_return_status := fnd_api.g_ret_sts_success;
99
100 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
101 RAISE fnd_api.g_exc_unexpected_error;
102 END IF;
103
104 IF fnd_api.to_boolean(p_init_msg_list) THEN
105 fnd_msg_pub.initialize;
106 END IF;
107
108 /* Validate that the group is valid and not end dated */
109
110 OPEN c_validate_group(l_group_id);
111 FETCH c_validate_group INTO l_group_start_date, l_group_end_date, l_group_name;
112 CLOSE c_validate_group;
113
114
115 IF (l_group_start_date IS NULL) THEN
116 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GROUP');
117 fnd_message.set_token('P_GROUP_ID',l_group_id);
118 fnd_msg_pub.add;
119 RAISE fnd_api.g_exc_error;
120 ELSIF
121 ((l_group_start_date IS NOT NULL) AND
122 (l_group_end_date < TRUNC(SYSDATE))
123 ) THEN
124 fnd_message.set_name('JTF', 'JTF_RS_INACTIVE_GROUP');
125 fnd_message.set_token('P_GROUP_NAME',l_group_name);
126 fnd_msg_pub.add;
127 RAISE fnd_api.g_exc_error;
128 END IF;
129
130
131 /* Validate that the HR Org is valid and not end dated */
132
133 OPEN c_validate_hr_org(l_organization_id);
134 FETCH c_validate_hr_org INTO l_org_start_date, l_org_end_date, l_org_name;
135 CLOSE c_validate_hr_org;
136
137
138 IF (l_org_start_date IS NULL) THEN
139 fnd_message.set_name('JTF', 'JTF_RS_INVALID_HR_ORG');
140 fnd_message.set_token('P_ORG_ID',l_organization_id);
141 fnd_msg_pub.add;
142 RAISE fnd_api.g_exc_error;
143 ELSIF
144 ((l_org_start_date IS NOT NULL) AND
145 (l_org_end_date < TRUNC(SYSDATE))
146 ) THEN
147 fnd_message.set_name('JTF', 'JTF_RS_INACTIVE_HR_ORG');
148 fnd_message.set_token('P_ORG_NAME',l_org_name);
149 fnd_msg_pub.add;
150 RAISE fnd_api.g_exc_error;
151 END IF;
152
153 /* Validate that Resource Group and HR Org mapping does not already exist */
154
155 OPEN c_check_dup_group_org_map(l_group_id,l_organization_id);
156 FETCH c_check_dup_group_org_map INTO l_mapping_exist;
157 CLOSE c_check_dup_group_org_map;
158
159 IF (l_mapping_exist = 'Y') THEN
160 fnd_message.set_name('JTF', 'JTF_RS_GROUP_ORG_MAP_EXIST');
161 fnd_message.set_token('P_GROUP_NAME',l_group_name);
162 fnd_message.set_token('P_ORG_NAME',l_org_name);
163 fnd_msg_pub.add;
164 RAISE fnd_api.g_exc_error;
165 END IF;
166
167 /* Insert the row into the table */
168
169 INSERT INTO jtf_rs_group_organizations (
170 GROUP_ID,
171 ORGANIZATION_ID,
172 OBJECT_VERSION_NUMBER,
173 CREATION_DATE,
174 CREATED_BY,
175 LAST_UPDATE_DATE,
176 LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN,
178 ATTRIBUTE1,
179 ATTRIBUTE2,
180 ATTRIBUTE3,
181 ATTRIBUTE4,
182 ATTRIBUTE5,
183 ATTRIBUTE6,
184 ATTRIBUTE7,
185 ATTRIBUTE8,
186 ATTRIBUTE9,
187 ATTRIBUTE10,
188 ATTRIBUTE11,
189 ATTRIBUTE12,
190 ATTRIBUTE13,
191 ATTRIBUTE14,
192 ATTRIBUTE15,
193 ATTRIBUTE_CATEGORY
194 ) VALUES (
195 P_GROUP_ID,
196 P_ORGANIZATION_ID,
197 1,
198 SYSDATE,
199 jtf_resource_utl.created_by,
200 SYSDATE,
201 jtf_resource_utl.updated_by,
202 jtf_resource_utl.login_id,
203 P_ATTRIBUTE1,
204 P_ATTRIBUTE2,
205 P_ATTRIBUTE3,
206 P_ATTRIBUTE4,
207 P_ATTRIBUTE5,
208 P_ATTRIBUTE6,
209 P_ATTRIBUTE7,
210 P_ATTRIBUTE8,
211 P_ATTRIBUTE9,
212 P_ATTRIBUTE10,
213 P_ATTRIBUTE11,
214 P_ATTRIBUTE12,
215 P_ATTRIBUTE13,
216 P_ATTRIBUTE14,
217 P_ATTRIBUTE15,
218 P_ATTRIBUTE_CATEGORY
219 );
220
221 IF fnd_api.to_boolean(p_commit) THEN
222 COMMIT WORK;
223 END IF;
224
225 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
226
227 EXCEPTION
228
229 WHEN fnd_api.g_exc_error THEN
230 ROLLBACK TO create_group_org;
231 x_return_status := fnd_api.g_ret_sts_error;
232 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
233 p_data => x_msg_data);
234
235 WHEN fnd_api.g_exc_unexpected_error THEN
236 ROLLBACK TO create_group_org;
237 x_return_status := fnd_api.g_ret_sts_unexp_error;
238 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
239 p_data => x_msg_data);
240
241 WHEN OTHERS THEN
242 ROLLBACK TO create_group_org;
243 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
244 fnd_message.set_token('P_SQLCODE',SQLCODE);
245 fnd_message.set_token('P_SQLERRM',SQLERRM);
246 fnd_message.set_token('P_API_NAME', g_pkg_name||'.'||l_api_name);
247 FND_MSG_PUB.add;
248 x_return_status := fnd_api.g_ret_sts_unexp_error;
249 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
250 p_data => x_msg_data);
251
252 END create_group_org;
253
254
255 /* Procedure to update the resource group - HR Org mapping
256 */
257
258 PROCEDURE update_group_org
259 (P_API_VERSION IN NUMBER,
260 P_INIT_MSG_LIST IN VARCHAR2,
261 P_COMMIT IN VARCHAR2,
262 P_GROUP_ID IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
263 P_ORGANIZATION_ID IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE,
264 P_ATTRIBUTE1 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE1%TYPE,
265 P_ATTRIBUTE2 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE2%TYPE,
266 P_ATTRIBUTE3 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE3%TYPE,
267 P_ATTRIBUTE4 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE4%TYPE,
268 P_ATTRIBUTE5 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE5%TYPE,
269 P_ATTRIBUTE6 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE6%TYPE,
270 P_ATTRIBUTE7 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE7%TYPE,
271 P_ATTRIBUTE8 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE8%TYPE,
272 P_ATTRIBUTE9 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE9%TYPE,
273 P_ATTRIBUTE10 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE10%TYPE,
274 P_ATTRIBUTE11 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE11%TYPE,
275 P_ATTRIBUTE12 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE12%TYPE,
276 P_ATTRIBUTE13 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE13%TYPE,
277 P_ATTRIBUTE14 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE14%TYPE,
278 P_ATTRIBUTE15 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE15%TYPE,
279 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE_CATEGORY%TYPE,
280 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_GROUP_ORGANIZATIONS.OBJECT_VERSION_NUMBER%TYPE,
281 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
282 X_MSG_COUNT OUT NOCOPY NUMBER,
283 X_MSG_DATA OUT NOCOPY VARCHAR2
284 )
285 IS
286 l_api_version CONSTANT NUMBER := 1.0;
287 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP_ORG';
288 l_group_id JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE;
289 l_organization_id JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE;
290 l_object_version_number JTF_RS_GROUP_ORGANIZATIONS.object_version_number%type;
291
292 CURSOR c_group_org_update(ll_group_id IN NUMBER,
293 ll_organization_id IN NUMBER) IS
294 SELECT
295 group_id l_group_id,
296 organization_id l_organization_id,
297 object_version_number l_object_version_number,
298 DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
299 DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
300 DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
301 DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
302 DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
303 DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
304 DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
305 DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
306 DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
307 DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
308 DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
309 DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
310 DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
311 DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
312 DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
313 DECODE(p_attribute_category,fnd_api.g_miss_char, attribute_category, p_attribute_category) l_attribute_category
314 FROM jtf_rs_group_organizations
315 WHERE group_id = ll_group_id
316 AND organization_id = ll_organization_id;
317
318 group_org_rec c_group_org_update%ROWTYPE;
319
320 BEGIN
321 SAVEPOINT sp_update_group_org;
322
323 -- initialize valriables
324 l_group_id := p_group_id;
325 l_organization_id := p_organization_id;
326 l_object_version_number := p_object_version_number;
327 x_return_status := fnd_api.g_ret_sts_success;
328
329 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
330 RAISE fnd_api.g_exc_unexpected_error;
331 END IF;
332
333 IF fnd_api.to_boolean(p_init_msg_list) THEN
334 fnd_msg_pub.initialize;
335 END IF;
336
337 --Fetch the existing data from table
338 OPEN c_group_org_update(l_group_id, l_organization_id);
339 FETCH c_group_org_update INTO group_org_rec;
340 IF c_group_org_update%NOTFOUND THEN
341 CLOSE c_group_org_update;
342 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GRP_ORG_ID');
343 fnd_message.set_token('P_GRP_ID', l_group_id);
344 fnd_message.set_token('P_ORG_ID', l_organization_id);
345 fnd_msg_pub.add;
346 RAISE fnd_api.g_exc_unexpected_error;
347 ELSE
348 CLOSE c_group_org_update;
349 END IF;
350
351 --Check if object Version numbers match
352 IF (group_org_rec.l_object_version_number = l_object_version_number)
353 THEN
354 NULL;
355 ELSE
356 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
357 fnd_msg_pub.add;
358 RAISE fnd_api.g_exc_error;
359 END IF;
360
361 --Update the Object Version Number by Incrementing It
362 l_object_version_number := l_object_version_number+1;
363
364 --Update the Values in jtf_rs_group_organizations
365
366 UPDATE jtf_rs_group_organizations SET
367 GROUP_ID = group_org_rec.l_group_id,
368 ORGANIZATION_ID = group_org_rec.l_organization_id,
369 OBJECT_VERSION_NUMBER = l_object_version_number,
370 LAST_UPDATE_DATE = SYSDATE,
371 LAST_UPDATED_BY = jtf_resource_utl.updated_by,
372 LAST_UPDATE_LOGIN = jtf_resource_utl.login_id,
373 ATTRIBUTE1 = group_org_rec.l_attribute1,
374 ATTRIBUTE2 = group_org_rec.l_attribute2,
375 ATTRIBUTE3 = group_org_rec.l_attribute3,
376 ATTRIBUTE4 = group_org_rec.l_attribute4,
377 ATTRIBUTE5 = group_org_rec.l_attribute5,
378 ATTRIBUTE6 = group_org_rec.l_attribute6,
379 ATTRIBUTE7 = group_org_rec.l_attribute7,
380 ATTRIBUTE8 = group_org_rec.l_attribute8,
381 ATTRIBUTE9 = group_org_rec.l_attribute9,
382 ATTRIBUTE10 = group_org_rec.l_attribute10,
383 ATTRIBUTE11 = group_org_rec.l_attribute11,
384 ATTRIBUTE12 = group_org_rec.l_attribute12,
385 ATTRIBUTE13 = group_org_rec.l_attribute13,
386 ATTRIBUTE14 = group_org_rec.l_attribute14,
387 ATTRIBUTE15 = group_org_rec.l_attribute15,
388 ATTRIBUTE_CATEGORY = group_org_rec.l_attribute_category
389 WHERE GROUP_ID = l_group_id
390 AND ORGANIZATION_ID = l_organization_id;
391
392 p_object_version_number := l_object_version_number;
393
394 IF fnd_api.to_boolean(p_commit) THEN
395 COMMIT WORK;
396 END IF;
397
398 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
399
400 EXCEPTION
401 WHEN fnd_api.g_exc_error THEN
402 ROLLBACK TO sp_update_group_org;
403 x_return_status := fnd_api.g_ret_sts_error;
404 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
405 WHEN fnd_api.g_exc_unexpected_error THEN
406 ROLLBACK TO sp_update_group_org;
407 x_return_status := fnd_api.g_ret_sts_unexp_error;
408 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
409 WHEN OTHERS THEN
410 ROLLBACK TO sp_update_group_org;
411 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
412 fnd_message.set_token('P_SQLCODE',SQLCODE);
413 fnd_message.set_token('P_SQLERRM',SQLERRM);
414 fnd_message.set_token('P_API_NAME',g_pkg_name||'.'||l_api_name);
415 FND_MSG_PUB.add;
416 x_return_status := fnd_api.g_ret_sts_unexp_error;
417 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
418
419 END update_group_org;
420
421
422 /* Procedure to delete resource group - HR Org mapping
423 */
424
425 PROCEDURE delete_group_org
426 (P_API_VERSION IN NUMBER,
427 P_INIT_MSG_LIST IN VARCHAR2,
428 P_COMMIT IN VARCHAR2,
429 P_GROUP_ID IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
430 P_ORGANIZATION_ID IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE,
431 P_OBJECT_VERSION_NUMBER IN JTF_RS_GROUP_ORGANIZATIONS.OBJECT_VERSION_NUMBER%TYPE,
432 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
433 X_MSG_COUNT OUT NOCOPY NUMBER,
434 X_MSG_DATA OUT NOCOPY VARCHAR2
435 )
436
437 IS
438
439 l_api_version CONSTANT NUMBER := 1.0;
440 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_ORG';
441
442 l_object_version_number JTF_RS_GROUP_ORGANIZATIONS.OBJECT_VERSION_NUMBER%TYPE;
443
444 CURSOR c_group_org_id(ll_group_id IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
445 ll_organization_id IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE)
446 IS
447 SELECT object_version_number
448 FROM jtf_rs_group_organizations
449 WHERE group_id = ll_group_id
450 AND organization_id = ll_organization_id
451 ;
452
453 BEGIN
454
455 SAVEPOINT sp_delete_group_org;
456
457 x_return_status := fnd_api.g_ret_sts_success;
458
459 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
460 RAISE fnd_api.g_exc_unexpected_error;
461 END IF;
462
463 IF fnd_api.to_boolean(p_init_msg_list) THEN
464 fnd_msg_pub.initialize;
465 END IF;
466
467 /* Validate that the specified group id and organization_id is valid */
468
469 OPEN c_group_org_id(p_group_id, p_organization_id);
470 FETCH c_group_org_id INTO l_object_version_number;
471 CLOSE c_group_org_id;
472
473 IF (l_object_version_number IS NULL) THEN
474 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GRP_ORG_ID');
475 fnd_message.set_token('P_GRP_ID', p_group_id);
476 fnd_message.set_token('P_ORG_ID', p_organization_id);
477 fnd_msg_pub.add;
478 RAISE fnd_api.g_exc_error;
479 END IF;
480
481 --Check if object Version numbers match
482 IF (l_object_version_number = p_object_version_number)
483 THEN
484 NULL;
485 ELSE
486 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
487 fnd_msg_pub.add;
488 RAISE fnd_api.g_exc_error;
489 END IF;
490
491 /* delete table data*/
492
493 DELETE FROM jtf_rs_group_organizations
494 WHERE GROUP_ID = P_GROUP_ID
495 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
496
497 IF fnd_api.to_boolean(p_commit) THEN
498 COMMIT WORK;
499 END IF;
500
501 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
502
503 EXCEPTION
504
505 WHEN fnd_api.g_exc_error THEN
506 ROLLBACK TO sp_delete_group_org;
507 x_return_status := fnd_api.g_ret_sts_error;
508 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
509 p_data => x_msg_data);
510 WHEN fnd_api.g_exc_unexpected_error THEN
511 ROLLBACK TO sp_delete_group_org;
512 x_return_status := fnd_api.g_ret_sts_unexp_error;
513 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
514 p_data => x_msg_data);
515 WHEN OTHERS THEN
516 ROLLBACK TO sp_delete_group_org;
517 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
518 fnd_message.set_token('P_SQLCODE',SQLCODE);
519 fnd_message.set_token('P_SQLERRM',SQLERRM);
520 fnd_message.set_token('P_API_NAME', g_pkg_name||'.'||l_api_name);
521 FND_MSG_PUB.add;
522 x_return_status := fnd_api.g_ret_sts_unexp_error;
523 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
524 p_data => x_msg_data);
525
526 END delete_group_org;
527
528 END jtf_rs_group_orgs_pvt;