[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_DYNAMIC_GROUPS_PVT
Source
1 PACKAGE BODY jtf_rs_dynamic_groups_pvt AS
2 /* $Header: jtfrsvyb.pls 120.0 2005/05/11 08:23:22 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a private API that caller will invoke.
6 It provides procedures for managing Dynamic Groups, like
7 create, update and delete Dynamic Groups from other modules.
8 Its main procedures are as following:
9 Create Dynamic Groups
10 Update Dynamic Groups
11 Delete Dynamic Groups
12 ******************************************************************************************/
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_DYNAMIC_GROUPS_PVT';
15
16 /* Procedure to create the Dynamic Groups
17 based on input values passed by calling routines. */
18
19 PROCEDURE create_dynamic_groups
20 (P_API_VERSION IN NUMBER,
21 P_INIT_MSG_LIST IN VARCHAR2,
22 P_COMMIT IN VARCHAR2,
23 P_GROUP_NAME IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_NAME%TYPE,
24 P_GROUP_DESC IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_DESC%TYPE,
25 P_USAGE IN JTF_RS_DYNAMIC_GROUPS_B.USAGE%TYPE,
26 P_START_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.START_DATE_ACTIVE%TYPE,
27 P_END_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.END_DATE_ACTIVE%TYPE,
28 P_SQL_TEXT IN JTF_RS_DYNAMIC_GROUPS_B.SQL_TEXT%TYPE,
29 P_ATTRIBUTE1 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE1%TYPE,
30 P_ATTRIBUTE2 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE2%TYPE,
31 P_ATTRIBUTE3 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE3%TYPE,
32 P_ATTRIBUTE4 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE4%TYPE,
33 P_ATTRIBUTE5 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE5%TYPE,
34 P_ATTRIBUTE6 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE6%TYPE,
35 P_ATTRIBUTE7 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE7%TYPE,
36 P_ATTRIBUTE8 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE8%TYPE,
37 P_ATTRIBUTE9 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE9%TYPE,
38 P_ATTRIBUTE10 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE10%TYPE,
39 P_ATTRIBUTE11 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE11%TYPE,
40 P_ATTRIBUTE12 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE12%TYPE,
41 P_ATTRIBUTE13 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE13%TYPE,
42 P_ATTRIBUTE14 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE14%TYPE,
43 P_ATTRIBUTE15 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE15%TYPE,
44 P_ATTRIBUTE_CATEGORY IN JTF_RS_GRP_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
45 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
46 X_MSG_COUNT OUT NOCOPY NUMBER,
47 X_MSG_DATA OUT NOCOPY VARCHAR2,
48 X_GROUP_ID OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
49 X_GROUP_NUMBER OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.GROUP_NUMBER%TYPE
50 )
51 IS
52
53 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DYNAMIC_GROUPS';
54 l_api_version CONSTANT NUMBER :=1.0;
55
56 l_return_status VARCHAR2(200);
57 l_msg_count NUMBER;
58 l_msg_data VARCHAR2(200);
59 l_rowid VARCHAR2(200);
60
61 l_return_code VARCHAR2(100);
62 l_count NUMBER;
63 l_data VARCHAR2(200);
64 l_date Date;
65 l_user_id Number;
66 l_login_id Number;
67
68
69 l_group_name jtf_rs_dynamic_groups_tl.group_name%type;
70 l_group_desc jtf_rs_dynamic_groups_tl.group_desc%type;
71 l_usage jtf_rs_dynamic_groups_b.usage%type;
72 l_start_date_active jtf_rs_dynamic_groups_b.start_date_active%type;
73 l_end_date_active jtf_rs_dynamic_groups_b.end_date_active%type;
74 l_sql_text jtf_rs_dynamic_groups_b.sql_text%type;
75
76
77 l_cursorid NUMBER;
78 v_dummy integer;
79
80 l_group_id jtf_rs_dynamic_groups_b.group_id%type;
81 l_group_number jtf_rs_dynamic_groups_b.group_number%type;
82
83 l_bind_data_id number;
84
85 BEGIN
86
87 l_group_name := P_GROUP_NAME;
88 l_group_desc := P_GROUP_DESC;
89 l_usage := P_USAGE;
90 l_start_date_active := P_START_DATE_ACTIVE;
91 l_end_date_active := P_END_DATE_ACTIVE;
92 l_sql_text := P_SQL_TEXT;
93
94 --Standard Start of API SAVEPOINT
95 SAVEPOINT GROUP_DYNAMIC_SP;
96
97 x_return_status := fnd_api.g_ret_sts_success;
98
99 --Standard Call to check API compatibility
100 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
101 THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
106 IF FND_API.To_boolean(P_INIT_MSG_LIST)
107 THEN
108 FND_MSG_PUB.Initialize;
109 END IF;
110
111 --GET USER ID AND SYSDATE
112 l_date := sysdate;
113 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
114 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
115
116 -- user hook calls for customer
117 -- Customer pre- processing section - mandatory
118 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'C' ))
119 then
120 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'C' ))
121 then
122 JTF_RS_DYNAMIC_GROUPS_CUHK.CREATE_DYNAMIC_GROUPS_PRE(P_GROUP_NAME => p_group_name,
123 P_GROUP_DESC => p_group_desc,
124 P_USAGE => p_usage,
125 P_START_DATE_ACTIVE => p_start_date_active,
126 P_END_DATE_ACTIVE => p_end_date_active,
127 P_SQL_TEXT => p_sql_text,
128 p_data => L_data,
129 p_count => L_count,
130 P_return_code => l_return_code);
131 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
132 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
133 fnd_msg_pub.add;
134
135 IF l_return_code = fnd_api.g_ret_sts_error THEN
136 raise fnd_api.g_exc_error;
137 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
138 raise fnd_api.g_exc_unexpected_error;
139 END IF;
140 END IF;
141 /*
142 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
143 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
144 x_return_status := fnd_api.g_ret_sts_unexp_error;
145 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
146 FND_MSG_PUB.add;
147 RAISE fnd_api.g_exc_unexpected_error;
148 end if;
149 */
150 end if;
151 end if;
152
153 /* Verticle industry pre- processing section - mandatory */
154
155 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'V' ))
156 then
157 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'V' ))
158 then
159
160
161 JTF_RS_DYNAMIC_GROUPS_VUHK.CREATE_DYNAMIC_GROUPS_PRE(P_GROUP_NAME => p_group_name,
162 P_GROUP_DESC => p_group_desc,
163 P_USAGE => p_usage,
164 P_START_DATE_ACTIVE => p_start_date_active,
165 P_END_DATE_ACTIVE => p_end_date_active,
166 P_SQL_TEXT => p_sql_text,
167 p_data => L_data,
168 p_count => L_count,
169 P_return_code => l_return_code);
170 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
171 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
172 fnd_msg_pub.add;
173
174 IF l_return_code = fnd_api.g_ret_sts_error THEN
175 raise fnd_api.g_exc_error;
176 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
177 raise fnd_api.g_exc_unexpected_error;
178 END IF;
179 END IF;
180 /*
181 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
182 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
183 x_return_status := fnd_api.g_ret_sts_unexp_error;
184 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
185 FND_MSG_PUB.add;
186 RAISE fnd_api.g_exc_unexpected_error;
187 end if;
188 */
189 end if;
190 end if;
191
192 /* Internal industry pre- processing section - mandatory */
193
194 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'I' ))
195 then
196 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'B', 'I' ))
197 then
198
199
200 JTF_RS_DYNAMIC_GROUPS_IUHK.CREATE_DYNAMIC_GROUPS_PRE(P_GROUP_NAME => p_group_name,
201 P_GROUP_DESC => p_group_desc,
202 P_USAGE => p_usage,
203 P_START_DATE_ACTIVE => p_start_date_active,
204 P_END_DATE_ACTIVE => p_end_date_active,
205 P_SQL_TEXT => p_sql_text,
206 p_data => L_data,
207 p_count => L_count,
208 P_return_code => l_return_code);
209 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
210 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
211 fnd_msg_pub.add;
212
213 IF l_return_code = fnd_api.g_ret_sts_error THEN
214 raise fnd_api.g_exc_error;
215 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
216 raise fnd_api.g_exc_unexpected_error;
217 END IF;
218 END IF;
219 /*
220 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
221 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
224 FND_MSG_PUB.add;
225 RAISE fnd_api.g_exc_unexpected_error;
226 end if;
227 */
228 end if;
229 end if;
230
231
232 -- end of user hook call
233
234
235
236
237
238
239 --call default date validation utl
240 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(l_start_date_active,
241 l_end_date_active,
242 l_return_status);
243 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
244 IF l_return_status = fnd_api.g_ret_sts_error THEN
245 RAISE fnd_api.g_exc_error;
246 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
247 RAISE fnd_api.g_exc_unexpected_error;
248 END IF;
249 END IF;
250 /*
251 IF(l_return_status <> fnd_api.g_ret_sts_success)
252 THEN
253 x_return_status := fnd_api.g_ret_sts_unexp_error;
254 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
255 FND_MSG_PUB.add;
256 RAISE fnd_api.g_exc_unexpected_error;
257 END IF;
258 */
259 --call usage validation
260
261 JTF_RESOURCE_UTL.VALIDATE_USAGE(l_usage,
262 l_return_status);
263
264 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
265 IF l_return_status = fnd_api.g_ret_sts_error THEN
266 RAISE fnd_api.g_exc_error;
267 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
268 RAISE fnd_api.g_exc_unexpected_error;
269 END IF;
270 END IF;
271 /*
272 IF(l_return_status <> fnd_api.g_ret_sts_success)
273 THEN
274 x_return_status := fnd_api.g_ret_sts_unexp_error;
275 fnd_message.set_name ('JTF', 'JTF_RS_USAGE_ERR');
276 FND_MSG_PUB.add;
277 RAISE fnd_api.g_exc_unexpected_error;
278 END IF;
279 */
280 IF l_sql_text is NOT NULL
281 THEN
282 if (instr(ltrim(upper(l_sql_text)),'SELECT') = 1) then
283 --VALIDATE SQL STATEMENT
284 BEGIN
285 l_cursorid := DBMS_SQL.OPEN_CURSOR;
286 DBMS_SQL.PARSE(l_cursorid, l_sql_text, DBMS_SQL.V7);
287 EXCEPTION
288 WHEN OTHERS THEN
289 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
290 fnd_message.set_name ('JTF', 'JTF_RS_SQL_TEXT_ERR');
291 FND_MSG_PUB.add;
292 -- RAISE fnd_api.g_exc_unexpected_error;
293 RAISE fnd_api.g_exc_error;
294 END;
295 else
296 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
297 fnd_message.set_name ('JTF', 'JTF_RS_SQL_NOT_A_SELECT');
298 FND_MSG_PUB.add;
299 -- RAISE fnd_api.g_exc_unexpected_error;
300 RAISE fnd_api.g_exc_error;
301 end if;
302 END IF;
303
304 --call table handler for insert
305 SELECT TO_CHAR(jtf_rs_groups_s.nextval), jtf_rs_dynamic_groups_s.nextval
306 INTO l_group_number, l_group_id
307 FROM dual;
308
309
310 jtf_rs_dynamic_groups_pkg.insert_row(
311 X_ROWID => l_rowid,
312 X_GROUP_ID => l_group_id,
313 X_GROUP_NUMBER => l_group_number,
314 X_USAGE => l_usage,
315 X_START_DATE_ACTIVE => l_start_date_active,
316 X_END_DATE_ACTIVE => l_end_date_active,
317 X_SQL_TEXT => l_sql_text,
318 X_ATTRIBUTE1 => p_attribute1,
319 X_ATTRIBUTE2 => p_attribute2,
320 X_ATTRIBUTE3 => p_attribute3,
321 X_ATTRIBUTE4 => p_attribute4,
322 X_ATTRIBUTE5 => p_attribute5,
323 X_ATTRIBUTE6 => p_attribute6,
324 X_ATTRIBUTE7 => p_attribute7,
325 X_ATTRIBUTE8 => p_attribute8,
326 X_ATTRIBUTE9 => p_attribute9,
327 X_ATTRIBUTE10 => p_attribute10,
328 X_ATTRIBUTE11 => p_attribute11,
329 X_ATTRIBUTE12 => p_attribute12,
330 X_ATTRIBUTE13 => p_attribute13,
331 X_ATTRIBUTE14 => p_attribute14,
332 X_ATTRIBUTE15 => p_attribute15,
333 X_ATTRIBUTE_CATEGORY => p_attribute_category,
334 X_GROUP_NAME => l_group_name,
335 X_GROUP_DESC => l_group_desc,
336 X_CREATION_DATE => l_date,
337 X_CREATED_BY => l_user_id,
338 X_LAST_UPDATE_DATE => l_date,
339 X_LAST_UPDATED_BY => l_user_id,
340 X_LAST_UPDATE_LOGIN => l_login_id);
341
342 x_group_id := l_group_id;
343 x_group_number := l_group_number;
344
345
346 -- user hook calls for customer
347 -- Customer pre- processing section - mandatory
348 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'CREATE_DYNAMIC_GROUPS', 'A', 'C' ))
349 then
350 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'CREATE_DYNAMIC_GROUPS', 'A', 'C' ))
351 then
352 JTF_RS_DYNAMIC_GROUPS_CUHK.CREATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => l_group_id,
353 P_GROUP_NAME => p_group_name,
354 P_GROUP_DESC => p_group_desc,
355 P_USAGE => p_usage,
356 P_START_DATE_ACTIVE => p_start_date_active,
357 P_END_DATE_ACTIVE => p_end_date_active,
358 P_SQL_TEXT => p_sql_text,
359 p_data => L_data,
360 p_count => L_count,
361 P_return_code => l_return_code);
362 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
363 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
364 fnd_msg_pub.add;
365
366 IF l_return_code = fnd_api.g_ret_sts_error THEN
367 raise fnd_api.g_exc_error;
368 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
369 raise fnd_api.g_exc_unexpected_error;
370 END IF;
371 END IF;
372 /*
373 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
374 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
375 x_return_status := fnd_api.g_ret_sts_unexp_error;
376 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
377 FND_MSG_PUB.add;
378 RAISE fnd_api.g_exc_unexpected_error;
379 end if;
380 */
381 end if;
382 end if;
383
384 /* Vertical industry post- processing section - mandatory */
385
386 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'A', 'V' ))
387 then
388 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'A', 'V' ))
389 then
390
391
392 JTF_RS_DYNAMIC_GROUPS_VUHK.CREATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => l_group_id,
393 P_GROUP_NAME => p_group_name,
394 P_GROUP_DESC => p_group_desc,
395 P_USAGE => p_usage,
396 P_START_DATE_ACTIVE => p_start_date_active,
397 P_END_DATE_ACTIVE => p_end_date_active,
398 P_SQL_TEXT => p_sql_text,
399 p_data => L_data,
400 p_count => L_count,
401 P_return_code => l_return_code);
402 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
403 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
404 fnd_msg_pub.add;
405
406 IF l_return_code = fnd_api.g_ret_sts_error THEN
407 raise fnd_api.g_exc_error;
408 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
409 raise fnd_api.g_exc_unexpected_error;
410 END IF;
411 END IF;
412 /*
413 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
414 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
415 x_return_status := fnd_api.g_ret_sts_unexp_error;
416 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
417 FND_MSG_PUB.add;
418 RAISE fnd_api.g_exc_unexpected_error;
419 end if;
420 */
421 end if;
422 end if;
423
424 /* iNTERNAL post- processing section - mandatory */
425
426 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'A', 'I' ))
427 then
428 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'CREATE_DYNAMIC_GROUPS', 'A', 'I' ))
429 then
430
431
432 JTF_RS_DYNAMIC_GROUPS_IUHK.CREATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => l_group_id,
433 P_GROUP_NAME => p_group_name,
434 P_GROUP_DESC => p_group_desc,
435 P_USAGE => p_usage,
436 P_START_DATE_ACTIVE => p_start_date_active,
437 P_END_DATE_ACTIVE => p_end_date_active,
438 P_SQL_TEXT => p_sql_text,
439 p_data => L_data,
440 p_count => L_count,
441 P_return_code => l_return_code);
442 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
443 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
444 fnd_msg_pub.add;
445
446 IF l_return_code = fnd_api.g_ret_sts_error THEN
447 raise fnd_api.g_exc_error;
448 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
449 raise fnd_api.g_exc_unexpected_error;
450 END IF;
451 END IF;
452 /*
453 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
454 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
455 x_return_status := fnd_api.g_ret_sts_unexp_error;
456 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
457 FND_MSG_PUB.add;
458 RAISE fnd_api.g_exc_unexpected_error;
459 end if;
460 */
461 end if;
462 end if;
463
464 -- end of user hook call
465 IF jtf_resource_utl.ok_to_execute(
466 'JTF_RS_DYNAMIC_GROUPS_PVT',
467 'CREATE_DYNAMIC_GROUPS',
468 'M',
469 'M')
470 THEN
471 IF jtf_usr_hks.ok_to_execute(
472 'JTF_RS_DYNAMIC_GROUPS_PVT',
473 'CREATE_DYNAMIC_GROUPS',
474 'M',
475 'M')
476 THEN
477
478 IF (jtf_rs_dynamic_groups_cuhk.ok_to_generate_msg(
479 p_group_id => l_group_id,
480 x_return_status => l_return_status) )
481 THEN
482
483 /* Get the bind data id for the Business Object Instance */
484
485 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
486
487
488 /* Set bind values for the bind variables in the Business Object
489 SQL */
490
491 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_id',
492 l_group_id, 'S', 'N');
493
494
495 /* Call the message generation API */
496
497 jtf_usr_hks.generate_message(
498 p_prod_code => 'JTF',
499 p_bus_obj_code => 'RS_DGP',
500 p_action_code => 'I', /* I/U/D */
501 p_bind_data_id => l_bind_data_id,
502 x_return_code => x_return_status);
503
504 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
505 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
506 fnd_msg_pub.add;
507
508 IF x_return_status = fnd_api.g_ret_sts_error THEN
509 raise fnd_api.g_exc_error;
510 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
511 raise fnd_api.g_exc_unexpected_error;
512 END IF;
513 END IF;
514 /*
515 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
516 x_return_status := fnd_api.g_ret_sts_unexp_error;
517
518 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
519 fnd_msg_pub.add;
520
521 RAISE fnd_api.g_exc_unexpected_error;
522
523 END IF;
524 */
525 END IF;
526
527 END IF;
528 END IF;
529
530
531
532 --standard commit
533 IF fnd_api.to_boolean (p_commit)
534 THEN
535 COMMIT WORK;
536 END IF;
537
538
539 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
540
541 EXCEPTION
542
543 WHEN fnd_api.g_exc_error THEN
544 ROLLBACK TO group_dynamic_sp;
545 x_return_status := fnd_api.g_ret_sts_error;
546 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
547 p_data => x_msg_data);
548 WHEN fnd_api.g_exc_unexpected_error THEN
549 ROLLBACK TO group_dynamic_sp;
550 x_return_status := fnd_api.g_ret_sts_unexp_error;
551 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
552 p_data => x_msg_data);
553 WHEN OTHERS THEN
554 ROLLBACK TO group_dynamic_sp;
555 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
556 fnd_message.set_token('P_SQLCODE',SQLCODE);
557 fnd_message.set_token('P_SQLERRM',SQLERRM);
558 fnd_message.set_token('P_API_NAME', l_api_name);
559 FND_MSG_PUB.add;
560 x_return_status := fnd_api.g_ret_sts_unexp_error;
561 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
562 p_data => x_msg_data);
563 /*
564 WHEN fnd_api.g_exc_unexpected_error
565 THEN
566 ROLLBACK TO GROUP_DYNAMIC_SP;
567 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
568 FND_MSG_PUB.add;
569 x_return_status := fnd_api.g_ret_sts_unexp_error;
570 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
571 WHEN OTHERS
572 THEN
573 ROLLBACK TO GROUP_DYNAMIC_SP;
574 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
575 FND_MSG_PUB.add;
576 x_return_status := fnd_api.g_ret_sts_unexp_error;
577 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
578 */
579
580 END create_dynamic_groups;
581
582
583 /* Procedure to update the Dynamic Groups
584 based on input values passed by calling routines. */
585
586 PROCEDURE update_dynamic_groups
587 (P_API_VERSION IN NUMBER,
588 P_INIT_MSG_LIST IN VARCHAR2,
589 P_COMMIT IN VARCHAR2,
590 P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
591 P_GROUP_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_NUMBER%TYPE,
592 P_GROUP_NAME IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_NAME%TYPE,
593 P_GROUP_DESC IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_DESC%TYPE,
594 P_USAGE IN JTF_RS_DYNAMIC_GROUPS_B.USAGE%TYPE,
595 P_START_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.START_DATE_ACTIVE%TYPE,
596 P_END_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.END_DATE_ACTIVE%TYPE,
597 P_SQL_TEXT IN JTF_RS_DYNAMIC_GROUPS_B.SQL_TEXT%TYPE,
598 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE,
599 P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
600 P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
601 P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
602 P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
603 P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
604 P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
605 P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
606 P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
607 P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
608 P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
609 P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
610 P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
611 P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
612 P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
613 P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
614 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
615 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
616 X_MSG_COUNT OUT NOCOPY NUMBER,
617 X_MSG_DATA OUT NOCOPY VARCHAR2
618 )
619 IS
620 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DYNAMIC_GROUPS';
621 l_api_version CONSTANT NUMBER :=1.0;
622 l_bind_data_id number;
623
624 l_return_status VARCHAR2(200);
625 l_msg_count NUMBER;
626 l_msg_data VARCHAR2(200);
627 l_rowid VARCHAR2(200);
628
629 l_return_code VARCHAR2(100);
630 l_count NUMBER;
631 l_data VARCHAR2(200);
632 l_date Date;
633 l_user_id Number;
634 l_login_id Number;
635
636 l_group_id jtf_rs_dynamic_groups_b.group_id%type;
637 l_group_number jtf_rs_dynamic_groups_b.group_number%type ;
638 l_group_name jtf_rs_dynamic_groups_tl.group_name%type ;
639 l_group_desc jtf_rs_dynamic_groups_tl.group_desc%type ;
640 l_usage jtf_rs_dynamic_groups_b.usage%type ;
641 l_start_date_active jtf_rs_dynamic_groups_b.start_date_active%type;
642 l_end_date_active jtf_rs_dynamic_groups_b.end_date_active%type ;
643 l_sql_text jtf_rs_dynamic_groups_b.sql_text%type ;
644 l_object_version_number jtf_rs_dynamic_groups_b.object_version_number%type;
645
646 l_attribute1 jtf_rs_dynamic_groups_b.attribute1%type;
647 l_attribute2 jtf_rs_dynamic_groups_b.attribute2%type;
648 l_attribute3 jtf_rs_dynamic_groups_b.attribute3%type;
649 l_attribute4 jtf_rs_dynamic_groups_b.attribute4%type;
650 l_attribute5 jtf_rs_dynamic_groups_b.attribute5%type;
651 l_attribute6 jtf_rs_dynamic_groups_b.attribute6%type;
652 l_attribute7 jtf_rs_dynamic_groups_b.attribute7%type;
653 l_attribute8 jtf_rs_dynamic_groups_b.attribute8%type;
654 l_attribute9 jtf_rs_dynamic_groups_b.attribute9%type;
655 l_attribute10 jtf_rs_dynamic_groups_b.attribute10%type;
656 l_attribute11 jtf_rs_dynamic_groups_b.attribute11%type;
657 l_attribute12 jtf_rs_dynamic_groups_b.attribute12%type;
658 l_attribute13 jtf_rs_dynamic_groups_b.attribute13%type;
659 l_attribute14 jtf_rs_dynamic_groups_b.attribute14%type;
660 l_attribute15 jtf_rs_dynamic_groups_b.attribute15%type;
661 l_attribute_category jtf_rs_dynamic_groups_b.attribute_category%type;
662
663
664
665 l_cursorid NUMBER;
666 v_dummy integer;
667
668
669 CURSOR dyn_grp_cur(l_group_id jtf_rs_dynamic_groups_b.group_id%type)
670 IS
671 SELECT group_number,
672 usage ,
673 start_date_active,
674 end_date_active ,
675 sql_text,
676 object_version_number,
677 attribute1 ,
678 attribute2 ,
679 attribute3 ,
680 attribute4 ,
681 attribute5 ,
682 attribute6 ,
683 attribute7 ,
684 attribute8 ,
685 attribute9 ,
686 attribute10 ,
687 attribute11 ,
688 attribute12 ,
689 attribute13 ,
690 attribute14 ,
691 attribute15 ,
692 attribute_category,
693 group_name,
694 group_desc
695 FROM jtf_rs_dynamic_groups_vl
696 WHERE group_id = l_group_id;
697
698 dyn_grp_rec dyn_grp_cur%rowtype;
699
700
701
702
703 BEGIN
704
705 l_group_id := p_group_id;
706 l_object_version_number := p_object_version_number;
707
708 --Standard Start of API SAVEPOINT
709 SAVEPOINT GROUP_DYNAMIC_SP;
710
711 x_return_status := fnd_api.g_ret_sts_success;
712
713 --Standard Call to check API compatibility
714 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
715 THEN
716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718
719 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
720 IF FND_API.To_boolean(P_INIT_MSG_LIST)
721 THEN
722 FND_MSG_PUB.Initialize;
723 END IF;
724
725 --GET USER ID AND SYSDATE
726 l_date := sysdate;
727 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
728 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
729
730 -- user hook calls for customer
731 -- Customer pre- processing section - mandatory
732 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'C' ))
733 then
734 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'C' ))
735 then
736 JTF_RS_DYNAMIC_GROUPS_CUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
737 P_GROUP_NUMBER => p_group_number,
738 P_GROUP_NAME => p_group_name,
739 P_GROUP_DESC => p_group_desc,
740 P_USAGE => p_usage,
741 P_START_DATE_ACTIVE => p_start_date_active,
742 P_END_DATE_ACTIVE => p_end_date_active,
743 P_SQL_TEXT => p_sql_text,
744 P_OBJECT_VERSION_NUMBER => p_object_version_number,
745 p_data => L_data,
746 p_count => L_count,
747 P_return_code => l_return_code);
748 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
749 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
750 fnd_msg_pub.add;
751
752 IF l_return_code = fnd_api.g_ret_sts_error THEN
753 raise fnd_api.g_exc_error;
754 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
755 raise fnd_api.g_exc_unexpected_error;
756 END IF;
757 END IF;
758 /*
759 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
760 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
761 x_return_status := fnd_api.g_ret_sts_unexp_error;
762 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
763 FND_MSG_PUB.add;
764 RAISE fnd_api.g_exc_unexpected_error;
765 end if;
766 */
767 end if;
768 end if;
769
770 /* Vertical industry pre- processing section - mandatory */
771
772 if ( JTF_resource_utl.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'V' ))
773 then
774 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'V' ))
775 then
776
777
778 JTF_RS_DYNAMIC_GROUPS_VUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
779 P_GROUP_NUMBER => p_group_number,
780 P_GROUP_NAME => p_group_name,
781 P_GROUP_DESC => p_group_desc,
782 P_USAGE => p_usage,
783 P_START_DATE_ACTIVE => p_start_date_active,
784 P_END_DATE_ACTIVE => p_end_date_active,
785 P_SQL_TEXT => p_sql_text,
786 P_OBJECT_VERSION_NUMBER => p_object_version_number,
787 p_data => L_data,
788 p_count => L_count,
789 P_return_code => l_return_code);
790 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
791 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
792 fnd_msg_pub.add;
793
794 IF l_return_code = fnd_api.g_ret_sts_error THEN
795 raise fnd_api.g_exc_error;
796 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
797 raise fnd_api.g_exc_unexpected_error;
798 END IF;
799 END IF;
800 /*
801 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
802 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
803 x_return_status := fnd_api.g_ret_sts_unexp_error;
804 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
805 FND_MSG_PUB.add;
806 RAISE fnd_api.g_exc_unexpected_error;
807 end if;
808 */
809 end if;
810 end if;
811
812 /* Internal pre- processing section - mandatory */
813
814 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'I' ))
815 then
816 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'B', 'I' ))
817 then
818
819
820 JTF_RS_DYNAMIC_GROUPS_IUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
821 P_GROUP_NUMBER => p_group_number,
822 P_GROUP_NAME => p_group_name,
823 P_GROUP_DESC => p_group_desc,
824 P_USAGE => p_usage,
825 P_START_DATE_ACTIVE => p_start_date_active,
826 P_END_DATE_ACTIVE => p_end_date_active,
827 P_SQL_TEXT => p_sql_text,
828 P_OBJECT_VERSION_NUMBER => p_object_version_number,
829 p_data => L_data,
830 p_count => L_count,
831 P_return_code => l_return_code);
832 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
833 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
834 fnd_msg_pub.add;
835
836 IF l_return_code = fnd_api.g_ret_sts_error THEN
837 raise fnd_api.g_exc_error;
838 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
839 raise fnd_api.g_exc_unexpected_error;
840 END IF;
841 END IF;
842 /*
843 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
844 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
845 x_return_status := fnd_api.g_ret_sts_unexp_error;
846 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
847 FND_MSG_PUB.add;
848 RAISE fnd_api.g_exc_unexpected_error;
849 end if;
850 */
851 end if;
852 end if;
853
854
855 -- end of user hook call
856
857
858
859
860 OPEN dyn_grp_cur(l_group_id);
861 FETCH dyn_grp_cur INTO dyn_grp_rec;
862 CLOSE dyn_grp_cur;
863
864
865
866 --assign values to the local variables
867 IF(p_group_number = FND_API.G_MISS_CHAR)
868 THEN
869 l_group_number := dyn_grp_rec.group_number;
870 ELSE
871 l_group_number:= p_group_number;
872 END IF;
873
874
875 IF(p_group_name = FND_API.G_MISS_CHAR)
876 THEN
877 l_group_name := dyn_grp_rec.group_name;
878 ELSE
879 l_group_name := p_group_name;
880 END IF;
881
882 IF(p_group_desc = FND_API.G_MISS_CHAR)
883 THEN
884 l_group_desc := dyn_grp_rec.group_desc;
885 ELSE
886 l_group_desc := p_group_desc;
887 END IF;
888
889 IF(p_usage = FND_API.G_MISS_CHAR)
890 THEN
891 l_usage := dyn_grp_rec.usage ;
892 ELSE
893 l_usage := p_usage ;
894 END IF;
895 IF(p_sql_text = FND_API.G_MISS_CHAR)
896 THEN
897 l_sql_text := dyn_grp_rec.sql_text ;
898 ELSE
899 l_sql_text := p_sql_text;
900 END IF;
901
902
903
904 IF(p_start_date_active = FND_API.G_MISS_DATE)
905 THEN
906
907 l_start_date_active := dyn_grp_rec.start_date_active;
908 ELSE
909 l_start_date_active := p_start_date_active;
910 END IF;
911 IF(p_end_date_active = FND_API.G_MISS_DATE)
912 THEN
913 l_end_date_active := dyn_grp_rec.end_date_active;
914 ELSE
915 l_end_date_active := p_end_date_active;
916 END IF;
917 IF(p_attribute1 = FND_API.G_MISS_CHAR)
918 THEN
919 l_attribute1 := dyn_grp_rec.attribute1;
920 ELSE
921 l_attribute1 := p_attribute1;
922 END IF;
923 IF(p_attribute2= FND_API.G_MISS_CHAR)
924 THEN
925 l_attribute2 := dyn_grp_rec.attribute2;
926 ELSE
927 l_attribute2 := p_attribute2;
928 END IF;
929 IF(p_attribute3 = FND_API.G_MISS_CHAR)
930 THEN
931 l_attribute3 := dyn_grp_rec.attribute3;
932 ELSE
933 l_attribute3 := p_attribute3;
934 END IF;
935 IF(p_attribute4 = FND_API.G_MISS_CHAR)
936 THEN
937 l_attribute4 := dyn_grp_rec.attribute1;
938 ELSE
939 l_attribute4 := p_attribute4;
940 END IF;
941 IF(p_attribute5 = FND_API.G_MISS_CHAR)
942 THEN
943 l_attribute5 := dyn_grp_rec.attribute5;
944 ELSE
945 l_attribute5 := p_attribute5;
946 END IF;
947 IF(p_attribute6 = FND_API.G_MISS_CHAR)
948 THEN
949 l_attribute6 := dyn_grp_rec.attribute1;
950 ELSE
951 l_attribute6 := p_attribute6;
952 END IF;
953 IF(p_attribute7 = FND_API.G_MISS_CHAR)
954 THEN
955 l_attribute7 := dyn_grp_rec.attribute7;
956 ELSE
957 l_attribute7 := p_attribute7;
958 END IF;
959 IF(p_attribute8 = FND_API.G_MISS_CHAR)
960 THEN
961 l_attribute8 := dyn_grp_rec.attribute8;
962 ELSE
963 l_attribute8 := p_attribute8;
964 END IF;
965 IF(p_attribute9 = FND_API.G_MISS_CHAR)
966 THEN
967 l_attribute9 := dyn_grp_rec.attribute9;
968 ELSE
969 l_attribute9 := p_attribute9;
970 END IF;
971 IF(p_attribute10 = FND_API.G_MISS_CHAR)
972 THEN
973 l_attribute10 := dyn_grp_rec.attribute10;
974 ELSE
975 l_attribute10 := p_attribute10;
976 END IF;
977 IF(p_attribute11 = FND_API.G_MISS_CHAR)
978 THEN
979 l_attribute11 := dyn_grp_rec.attribute11;
980 ELSE
981 l_attribute11 := p_attribute11;
982 END IF;
983 IF(p_attribute12 = FND_API.G_MISS_CHAR)
984 THEN
985 l_attribute12 := dyn_grp_rec.attribute12;
986 ELSE
987 l_attribute12 := p_attribute12;
988 END IF;
989 IF(p_attribute13 = FND_API.G_MISS_CHAR)
990 THEN
991 l_attribute13 := dyn_grp_rec.attribute13;
992 ELSE
993 l_attribute13 := p_attribute13;
994 END IF;
995 IF(p_attribute14 = FND_API.G_MISS_CHAR)
996 THEN
997 l_attribute14 := dyn_grp_rec.attribute14;
998 ELSE
999 l_attribute14 := p_attribute14;
1000 END IF;
1001 IF(p_attribute15 = FND_API.G_MISS_CHAR)
1002 THEN
1003 l_attribute15 := dyn_grp_rec.attribute15;
1004 ELSE
1005 l_attribute15 := p_attribute15;
1006 END IF;
1007
1008 IF(p_attribute_category = FND_API.G_MISS_CHAR)
1009 THEN
1010 l_attribute_category := dyn_grp_rec.attribute_category;
1011 ELSE
1012 l_attribute_category := p_attribute_category;
1013 END IF;
1014
1015 --validate dates
1016 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(l_start_date_active,
1017 l_end_date_active,
1018 l_return_status);
1019
1020 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1021 IF l_return_status = fnd_api.g_ret_sts_error THEN
1022 RAISE fnd_api.g_exc_error;
1023 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1024 RAISE fnd_api.g_exc_unexpected_error;
1025 END IF;
1026 END IF;
1027 /*
1028 IF(l_return_status <> fnd_api.g_ret_sts_success)
1029 THEN
1030 x_return_status := fnd_api.g_ret_sts_unexp_error;
1031 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
1032 FND_MSG_PUB.add;
1033 RAISE fnd_api.g_exc_unexpected_error;
1034 END IF;
1035 */
1036 --call usage validation
1037 JTF_RESOURCE_UTL.VALIDATE_USAGE(l_usage,
1038 l_return_status);
1039
1040 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1041 IF l_return_status = fnd_api.g_ret_sts_error THEN
1042 RAISE fnd_api.g_exc_error;
1043 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1044 RAISE fnd_api.g_exc_unexpected_error;
1045 END IF;
1046 END IF;
1047 /*
1048 IF(l_return_status <> fnd_api.g_ret_sts_success)
1049 THEN
1050 x_return_status := fnd_api.g_ret_sts_unexp_error;
1051 fnd_message.set_name ('JTF', 'JTF_RS_USAGE_ERR');
1052 FND_MSG_PUB.add;
1053 RAISE fnd_api.g_exc_unexpected_error;
1054 END IF;
1055 */
1056
1057 IF l_sql_text is NOT NULL
1058 THEN
1059 if (instr(ltrim(upper(l_sql_text)),'SELECT') = 1) then
1060 --VALIDATE SQL STATEMENT
1061 BEGIN
1062 l_cursorid := DBMS_SQL.OPEN_CURSOR;
1063 DBMS_SQL.PARSE(l_cursorid, l_sql_text, DBMS_SQL.V7);
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
1068 fnd_message.set_name ('JTF', 'JTF_RS_SQL_TEXT_ERR');
1069 FND_MSG_PUB.add;
1070 -- RAISE fnd_api.g_exc_unexpected_error;
1071 RAISE fnd_api.g_exc_error;
1072 END;
1073 else
1074 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
1075 fnd_message.set_name ('JTF', 'JTF_RS_SQL_NOT_A_SELECT');
1076 FND_MSG_PUB.add;
1077 -- RAISE fnd_api.g_exc_unexpected_error;
1078 RAISE fnd_api.g_exc_error;
1079 end if;
1080 END IF;
1081
1082
1083 --call lock row
1084 BEGIN
1085
1086 jtf_rs_dynamic_groups_pkg.lock_row(
1087 x_group_id => l_group_id,
1088 x_object_version_number => p_object_version_number
1089 );
1090
1091 EXCEPTION
1092
1093 WHEN OTHERS THEN
1094 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
1095 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1096 fnd_msg_pub.add;
1097 -- RAISE fnd_api.g_exc_unexpected_error;
1098 RAISE fnd_api.g_exc_error;
1099
1100 END;
1101 l_object_version_number := l_object_version_number + 1;
1102
1103
1104 --call update table handler
1105 jtf_rs_dynamic_groups_pkg.update_row(
1106 X_GROUP_ID => l_group_id,
1107 X_GROUP_NUMBER => l_group_number,
1108 X_USAGE => l_usage,
1109 X_START_DATE_ACTIVE => l_start_date_active,
1110 X_END_DATE_ACTIVE => l_end_date_active,
1111 X_SQL_TEXT => l_sql_text,
1112 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1113 X_ATTRIBUTE1 => l_attribute1,
1114 X_ATTRIBUTE2 => l_attribute2,
1115 X_ATTRIBUTE3 => l_attribute3,
1116 X_ATTRIBUTE4 => l_attribute4,
1117 X_ATTRIBUTE5 => l_attribute5,
1118 X_ATTRIBUTE6 => l_attribute6,
1119 X_ATTRIBUTE7 => l_attribute7,
1120 X_ATTRIBUTE8 => l_attribute8,
1121 X_ATTRIBUTE9 => l_attribute9,
1122 X_ATTRIBUTE10 => l_attribute10,
1123 X_ATTRIBUTE11 => l_attribute11,
1124 X_ATTRIBUTE12 => l_attribute12,
1125 X_ATTRIBUTE13 => l_attribute13,
1126 X_ATTRIBUTE14 => l_attribute14,
1127 X_ATTRIBUTE15 => l_attribute15,
1128 X_ATTRIBUTE_CATEGORY => l_attribute_category,
1129 X_GROUP_NAME => l_group_name,
1130 X_GROUP_DESC => l_group_desc,
1131 X_LAST_UPDATE_DATE => l_date,
1132 X_LAST_UPDATED_BY => l_user_id,
1133 X_LAST_UPDATE_LOGIN => l_login_id);
1134
1135
1136 p_object_version_number := l_object_version_number;
1137
1138 -- user hook calls for customer
1139 -- Customer pre- processing section - mandatory
1140 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'UPDATE_DYNAMIC_GROUPS', 'A', 'C' ))
1141 then
1142 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'UPDATE_DYNAMIC_GROUPS', 'A', 'C' ))
1143 then
1144 JTF_RS_DYNAMIC_GROUPS_CUHK.UPDATE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
1145 P_GROUP_NUMBER => p_group_number,
1146 P_GROUP_NAME => p_group_name,
1147 P_GROUP_DESC => p_group_desc,
1148 P_USAGE => p_usage,
1149 P_START_DATE_ACTIVE => p_start_date_active,
1150 P_END_DATE_ACTIVE => p_end_date_active,
1151 P_SQL_TEXT => p_sql_text,
1152 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1153 p_data => L_data,
1154 p_count => L_count,
1155 P_return_code => l_return_code);
1156 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1157 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1158 fnd_msg_pub.add;
1159
1160 IF l_return_code = fnd_api.g_ret_sts_error THEN
1161 raise fnd_api.g_exc_error;
1162 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1163 raise fnd_api.g_exc_unexpected_error;
1164 END IF;
1165 END IF;
1166 /*
1167 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1168 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1169 x_return_status := fnd_api.g_ret_sts_unexp_error;
1170 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1171 FND_MSG_PUB.add;
1172 RAISE fnd_api.g_exc_unexpected_error;
1173 end if;
1174 */
1175 end if;
1176 end if;
1177
1178 /* Verticle industry pre- processing section - mandatory */
1179
1180 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'V' ))
1181 then
1182 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'V' ))
1183 then
1184
1185
1186 JTF_RS_DYNAMIC_GROUPS_VUHK.UPDATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
1187 P_GROUP_NUMBER => p_group_number,
1188 P_GROUP_NAME => p_group_name,
1189 P_GROUP_DESC => p_group_desc,
1190 P_USAGE => p_usage,
1191 P_START_DATE_ACTIVE => p_start_date_active,
1192 P_END_DATE_ACTIVE => p_end_date_active,
1193 P_SQL_TEXT => p_sql_text,
1194 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1195 p_data => L_data,
1196 p_count => L_count,
1197 P_return_code => l_return_code);
1198 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1199 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1200 fnd_msg_pub.add;
1201
1202 IF l_return_code = fnd_api.g_ret_sts_error THEN
1203 raise fnd_api.g_exc_error;
1204 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1205 raise fnd_api.g_exc_unexpected_error;
1206 END IF;
1207 END IF;
1208 /*
1209 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1210 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1211 x_return_status := fnd_api.g_ret_sts_unexp_error;
1212 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1213 FND_MSG_PUB.add;
1214 RAISE fnd_api.g_exc_unexpected_error;
1215 end if;
1216 */
1217 end if;
1218 end if;
1219
1220 /* Internal pre- processing section - mandatory */
1221
1222 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'I' ))
1223 then
1224 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'UPDATE_DYNAMIC_GROUPS', 'A', 'I' ))
1225 then
1226
1227
1228 JTF_RS_DYNAMIC_GROUPS_IUHK.UPDATE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
1229 P_GROUP_NUMBER => p_group_number,
1230 P_GROUP_NAME => p_group_name,
1231 P_GROUP_DESC => p_group_desc,
1232 P_USAGE => p_usage,
1233 P_START_DATE_ACTIVE => p_start_date_active,
1234 P_END_DATE_ACTIVE => p_end_date_active,
1235 P_SQL_TEXT => p_sql_text,
1236 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1237 p_data => L_data,
1238 p_count => L_count,
1239 P_return_code => l_return_code);
1240 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1241 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1242 fnd_msg_pub.add;
1243
1244 IF l_return_code = fnd_api.g_ret_sts_error THEN
1245 raise fnd_api.g_exc_error;
1246 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1247 raise fnd_api.g_exc_unexpected_error;
1248 END IF;
1249 END IF;
1250 /*
1251 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1252 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1253 x_return_status := fnd_api.g_ret_sts_unexp_error;
1254 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1255 FND_MSG_PUB.add;
1256 RAISE fnd_api.g_exc_unexpected_error;
1257 end if;
1258 */
1259 end if;
1260 end if;
1261
1262 -- end of user hook call
1263
1264 IF jtf_resource_utl.ok_to_execute(
1265 'JTF_RS_DYNAMIC_GROUPS_PVT',
1266 'UPDATE_DYNAMIC_GROUPS',
1267 'M',
1268 'M')
1269 THEN
1270 IF jtf_usr_hks.ok_to_execute(
1271 'JTF_RS_DYNAMIC_GROUPS_PVT',
1272 'UPDATE_DYNAMIC_GROUPS',
1273 'M',
1274 'M')
1275 THEN
1276
1277 IF (jtf_rs_dynamic_groups_cuhk.ok_to_generate_msg(
1278 p_group_id => l_group_id,
1279 x_return_status => x_return_status) )
1280 THEN
1281
1282 /* Get the bind data id for the Business Object Instance */
1283
1284 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1285
1286
1287 /* Set bind values for the bind variables in the Business Object
1288 SQL */
1289
1290 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_id',
1291 l_group_id, 'S', 'N');
1292
1293
1294 /* Call the message generation API */
1295
1296 jtf_usr_hks.generate_message(
1297 p_prod_code => 'JTF',
1298 p_bus_obj_code => 'RS_DGP',
1299 p_action_code => 'U', /* I/U/D */
1300 p_bind_data_id => l_bind_data_id,
1301 x_return_code => x_return_status);
1302
1303 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1304 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1305 fnd_msg_pub.add;
1306
1307 IF x_return_status = fnd_api.g_ret_sts_error THEN
1308 raise fnd_api.g_exc_error;
1309 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1310 raise fnd_api.g_exc_unexpected_error;
1311 END IF;
1312 END IF;
1313 /*
1314 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1315 x_return_status := fnd_api.g_ret_sts_unexp_error;
1316
1317 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1318 fnd_msg_pub.add;
1319
1320 RAISE fnd_api.g_exc_unexpected_error;
1321
1322 END IF;
1323 */
1324 END IF;
1325
1326 END IF;
1327 END IF;
1328
1329
1330
1331 --standard commit
1332 IF fnd_api.to_boolean (p_commit)
1333 THEN
1334 COMMIT WORK;
1335 END IF;
1336
1337
1338 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1339
1340 EXCEPTION
1341
1342 WHEN fnd_api.g_exc_error THEN
1343 ROLLBACK TO group_dynamic_sp;
1344 x_return_status := fnd_api.g_ret_sts_error;
1345 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1346 p_data => x_msg_data);
1347 WHEN fnd_api.g_exc_unexpected_error THEN
1348 ROLLBACK TO group_dynamic_sp;
1349 x_return_status := fnd_api.g_ret_sts_unexp_error;
1350 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1351 p_data => x_msg_data);
1352 WHEN OTHERS THEN
1353 ROLLBACK TO group_dynamic_sp;
1354 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1355 fnd_message.set_token('P_SQLCODE',SQLCODE);
1356 fnd_message.set_token('P_SQLERRM',SQLERRM);
1357 fnd_message.set_token('P_API_NAME', l_api_name);
1358 FND_MSG_PUB.add;
1359 x_return_status := fnd_api.g_ret_sts_unexp_error;
1360 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1361 p_data => x_msg_data);
1362 /*
1363 WHEN fnd_api.g_exc_unexpected_error
1364 THEN
1365 ROLLBACK TO GROUP_DYNAMIC_SP;
1366 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
1367 FND_MSG_PUB.add;
1368 x_return_status := fnd_api.g_ret_sts_unexp_error;
1369 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1370 WHEN OTHERS
1371 THEN
1372 ROLLBACK TO GROUP_DYNAMIC_SP;
1373 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
1374 FND_MSG_PUB.add;
1375 x_return_status := fnd_api.g_ret_sts_unexp_error;
1376 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1377 */
1378
1379 END;
1380
1381
1382
1383 /* Procedure to delete the Dynamic Groups. */
1384
1385 PROCEDURE delete_dynamic_groups
1386 (P_API_VERSION IN NUMBER,
1387 P_INIT_MSG_LIST IN VARCHAR2,
1388 P_COMMIT IN VARCHAR2,
1389 P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
1390 P_OBJECT_VERSION_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE,
1391 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1392 X_MSG_COUNT OUT NOCOPY NUMBER,
1393 X_MSG_DATA OUT NOCOPY VARCHAR2
1394 )
1395 IS
1396 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DYNAMIC_GROUPS';
1397 l_api_version CONSTANT NUMBER :=1.0;
1398 l_bind_data_id number;
1399
1400 l_return_status VARCHAR2(200);
1401 l_msg_count NUMBER;
1402 l_msg_data VARCHAR2(200);
1403 l_rowid VARCHAR2(200);
1404
1405 l_return_code VARCHAR2(100);
1406 l_count NUMBER;
1407 l_data VARCHAR2(200);
1408 l_date Date;
1409 l_user_id Number;
1410 l_login_id Number;
1411
1412 BEGIN
1413 --Standard Start of API SAVEPOINT
1414 SAVEPOINT GROUP_DYNAMIC_SP;
1415
1416 x_return_status := fnd_api.g_ret_sts_success;
1417
1418 --Standard Call to check API compatibility
1419 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1420 THEN
1421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1422 END IF;
1423
1424 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1425 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1426 THEN
1427 FND_MSG_PUB.Initialize;
1428 END IF;
1429
1430 --GET USER ID AND SYSDATE
1431 l_date := sysdate;
1432 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1433 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1434
1435 -- user hook calls for customer
1436 -- Customer pre- processing section - mandatory
1437 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'C' ))
1438 then
1439 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'C' ))
1440 then
1441 JTF_RS_DYNAMIC_GROUPS_CUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
1442 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1443 p_data => L_data,
1444 p_count => L_count,
1445 P_return_code => l_return_code);
1446 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1447 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1448 fnd_msg_pub.add;
1449
1450 IF l_return_code = fnd_api.g_ret_sts_error THEN
1451 raise fnd_api.g_exc_error;
1452 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1453 raise fnd_api.g_exc_unexpected_error;
1454 END IF;
1455 END IF;
1456 /*
1457 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1458 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1459 x_return_status := fnd_api.g_ret_sts_unexp_error;
1460 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1461 FND_MSG_PUB.add;
1462 RAISE fnd_api.g_exc_unexpected_error;
1463 end if;
1464 */
1465 end if;
1466 end if;
1467
1468 /* Vertical industry pre- processing section - mandatory */
1469
1470 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'V' ))
1471 then
1472 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'V' ))
1473 then
1474
1475
1476 JTF_RS_DYNAMIC_GROUPS_VUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
1477 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1478 p_data => L_data,
1479 p_count => L_count,
1480 P_return_code => l_return_code);
1481 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1482 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1483 fnd_msg_pub.add;
1484
1485 IF l_return_code = fnd_api.g_ret_sts_error THEN
1486 raise fnd_api.g_exc_error;
1487 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1488 raise fnd_api.g_exc_unexpected_error;
1489 END IF;
1490 END IF;
1491 /*
1492 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1493 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1494 x_return_status := fnd_api.g_ret_sts_unexp_error;
1495 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1496 FND_MSG_PUB.add;
1497 RAISE fnd_api.g_exc_unexpected_error;
1498 end if;
1499 */
1500 end if;
1501 end if;
1502
1503 /* Internal pre- processing section - mandatory */
1504
1505 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'I' ))
1506 then
1507 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'B', 'I' ))
1508 then
1509
1510
1511 JTF_RS_DYNAMIC_GROUPS_IUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
1512 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1513 p_data => L_data,
1514 p_count => L_count,
1515 P_return_code => l_return_code);
1516 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1517 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1518 fnd_msg_pub.add;
1519
1520 IF l_return_code = fnd_api.g_ret_sts_error THEN
1521 raise fnd_api.g_exc_error;
1522 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1523 raise fnd_api.g_exc_unexpected_error;
1524 END IF;
1525 END IF;
1526 /*
1527 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1528 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1529 x_return_status := fnd_api.g_ret_sts_unexp_error;
1530 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1531 FND_MSG_PUB.add;
1532 RAISE fnd_api.g_exc_unexpected_error;
1533 end if;
1534 */
1535 end if;
1536 end if;
1537
1538 -- end of user hook call
1539
1540
1541
1542 --call lock row
1543 BEGIN
1544
1545 jtf_rs_dynamic_groups_pkg.lock_row(
1546 x_group_id => p_group_id,
1547 x_object_version_number => p_object_version_number
1548 );
1549
1550 EXCEPTION
1551
1552 WHEN OTHERS THEN
1553 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
1554 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1555 fnd_msg_pub.add;
1556 -- RAISE fnd_api.g_exc_unexpected_error;
1557 RAISE fnd_api.g_exc_error;
1558
1559 END;
1560
1561 --call table handler to delete the group
1562 jtf_rs_dynamic_groups_pkg.delete_row(p_group_id);
1563
1564
1565 -- user hook calls for customer
1566 -- Customer post- processing section - mandatory
1567 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'DELETE_DYNAMIC_GROUPS', 'A', 'C' ))
1568 then
1569 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_POST', 'DELETE_DYNAMIC_GROUPS', 'A', 'C' ))
1570 then
1571
1572 JTF_RS_DYNAMIC_GROUPS_CUHK.DELETE_DYNAMIC_GROUPS_PRE(P_GROUP_ID => p_group_id,
1573 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1574 p_data => L_data,
1575 p_count => L_count,
1576 P_return_code => l_return_code);
1577 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1578 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1579 fnd_msg_pub.add;
1580
1581 IF l_return_code = fnd_api.g_ret_sts_error THEN
1582 raise fnd_api.g_exc_error;
1583 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1584 raise fnd_api.g_exc_unexpected_error;
1585 END IF;
1586 END IF;
1587 /*
1588 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1589 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1590 x_return_status := fnd_api.g_ret_sts_unexp_error;
1591 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1592 FND_MSG_PUB.add;
1593 RAISE fnd_api.g_exc_unexpected_error;
1594 end if;
1595 */
1596 end if;
1597 end if;
1598
1599 /* Verticle industry post- processing section - mandatory */
1600
1601 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'V' ))
1602 then
1603 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'V' ))
1604 then
1605
1606
1607 JTF_RS_DYNAMIC_GROUPS_VUHK.DELETE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
1608 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1609 p_data => L_data,
1610 p_count => L_count,
1611 P_return_code => l_return_code);
1612 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1613 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1614 fnd_msg_pub.add;
1615
1616 IF l_return_code = fnd_api.g_ret_sts_error THEN
1617 raise fnd_api.g_exc_error;
1618 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1619 raise fnd_api.g_exc_unexpected_error;
1620 END IF;
1621 END IF;
1622 /*
1623 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1624 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1625 x_return_status := fnd_api.g_ret_sts_unexp_error;
1626 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1627 FND_MSG_PUB.add;
1628 RAISE fnd_api.g_exc_unexpected_error;
1629 end if;
1630 */
1631 end if;
1632 end if;
1633
1634 /* Internal post- processing section - mandatory */
1635
1636 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'I' ))
1637 then
1638 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_DYNAMIC_GROUPS_PVT', 'DELETE_DYNAMIC_GROUPS', 'A', 'I' ))
1639 then
1640
1641
1642 JTF_RS_DYNAMIC_GROUPS_IUHK.DELETE_DYNAMIC_GROUPS_POST(P_GROUP_ID => p_group_id,
1643 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1644 p_data => L_data,
1645 p_count => L_count,
1646 P_return_code => l_return_code);
1647 IF NOT (l_return_code = fnd_api.g_ret_sts_success) THEN
1648 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1649 fnd_msg_pub.add;
1650
1651 IF l_return_code = fnd_api.g_ret_sts_error THEN
1652 raise fnd_api.g_exc_error;
1653 ELSIF l_return_code = fnd_api.g_ret_sts_unexp_error THEN
1654 raise fnd_api.g_exc_unexpected_error;
1655 END IF;
1656 END IF;
1657 /*
1658 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1659 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1660 x_return_status := fnd_api.g_ret_sts_unexp_error;
1661 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1662 FND_MSG_PUB.add;
1663 RAISE fnd_api.g_exc_unexpected_error;
1664 end if;
1665 */
1666 end if;
1667 end if;
1668
1669
1670 -- end of user hook call
1671
1672 IF jtf_resource_utl.ok_to_execute(
1673 'JTF_RS_DYNAMIC_GROUPS_PVT',
1674 'DELETE_DYNAMIC_GROUPS',
1675 'M',
1676 'M')
1677 THEN
1678 IF jtf_usr_hks.ok_to_execute(
1679 'JTF_RS_DYNAMIC_GROUPS_PVT',
1680 'DELETE_DYNAMIC_GROUPS',
1681 'M',
1682 'M')
1683 THEN
1684
1685 IF (jtf_rs_dynamic_groups_cuhk.ok_to_generate_msg(
1686 p_group_id => p_group_id,
1687 x_return_status => x_return_status) )
1688 THEN
1689
1690 /* Get the bind data id for the Business Object Instance */
1691
1692 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1693
1694
1695 /* Set bind values for the bind variables in the Business Object
1696 SQL */
1697
1698 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_id',
1699 p_group_id, 'S', 'N');
1700
1701
1702 /* Call the message generation API */
1703
1704 jtf_usr_hks.generate_message(
1705 p_prod_code => 'JTF',
1706 p_bus_obj_code => 'RS_DGP',
1707 p_action_code => 'D', /* I/U/D */
1708 p_bind_data_id => l_bind_data_id,
1709 x_return_code => x_return_status);
1710
1711
1712 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1713 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1714 fnd_msg_pub.add;
1715
1716 IF x_return_status = fnd_api.g_ret_sts_error THEN
1717 raise fnd_api.g_exc_error;
1718 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1719 raise fnd_api.g_exc_unexpected_error;
1720 END IF;
1721 END IF;
1722 /*
1723 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1724 x_return_status := fnd_api.g_ret_sts_unexp_error;
1725
1726 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1727 fnd_msg_pub.add;
1728
1729 RAISE fnd_api.g_exc_unexpected_error;
1730
1731 END IF;
1732 */
1733 END IF;
1734
1735 END IF;
1736 END IF;
1737
1738
1739 --standard commit
1740 IF fnd_api.to_boolean (p_commit)
1741 THEN
1742 COMMIT WORK;
1743 END IF;
1744
1745
1746 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1747
1748 EXCEPTION
1749
1750 WHEN fnd_api.g_exc_error THEN
1751 ROLLBACK TO group_dynamic_sp;
1752 x_return_status := fnd_api.g_ret_sts_error;
1753 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1754 p_data => x_msg_data);
1755 WHEN fnd_api.g_exc_unexpected_error THEN
1756 ROLLBACK TO group_dynamic_sp;
1757 x_return_status := fnd_api.g_ret_sts_unexp_error;
1758 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1759 p_data => x_msg_data);
1760 WHEN OTHERS THEN
1761 ROLLBACK TO group_dynamic_sp;
1762 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1763 fnd_message.set_token('P_SQLCODE',SQLCODE);
1764 fnd_message.set_token('P_SQLERRM',SQLERRM);
1765 fnd_message.set_token('P_API_NAME', l_api_name);
1766 FND_MSG_PUB.add;
1767 x_return_status := fnd_api.g_ret_sts_unexp_error;
1768 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1769 p_data => x_msg_data);
1770 /*
1771 WHEN fnd_api.g_exc_unexpected_error
1772 THEN
1773 ROLLBACK TO GROUP_DYNAMIC_SP;
1774 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
1775 FND_MSG_PUB.add;
1776 x_return_status := fnd_api.g_ret_sts_unexp_error;
1777 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1778 WHEN OTHERS
1779 THEN
1780 ROLLBACK TO GROUP_DYNAMIC_SP;
1781 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PVT_ERR');
1782 FND_MSG_PUB.add;
1783 x_return_status := fnd_api.g_ret_sts_unexp_error;
1784 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1785 */
1786 END;
1787 END jtf_rs_dynamic_groups_pvt;