[Home] [Help]
PACKAGE BODY: APPS.AMS_CELL_PVT
Source
1 Package Body AMS_CELL_PVT AS
2 /* $Header: amsvcelb.pls 120.3 2006/05/03 04:05:42 aanjaria noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_CELL_PVT';
5
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- create_cell
10 --
11 -- HISTORY
12 -- 12/15/99 mpande Created.
13 -- 01/19/01 yxliu modified, add field "sel_type" to cell_rec_type
14 -- 04/16/01 yxliu modified, add column country
15 -- 11/02/05 musman fixed bug: 4695424
16 ---------------------------------------------------------------------
17 PROCEDURE create_cell(
18 p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
20 p_commit IN VARCHAR2 := FND_API.g_false,
21 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
22
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26
27 p_cell_rec IN cell_rec_type,
28 x_cell_id OUT NOCOPY NUMBER
29 )
30 IS
31
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'create_cell';
34 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
35
36 l_return_status VARCHAR2(1);
37 l_cell_rec cell_rec_type := p_cell_rec;
38 l_cell_count NUMBER;
39
40 CURSOR c_cell_seq IS
41 SELECT ams_cells_all_b_s.NEXTVAL
42 FROM DUAL;
43
44 CURSOR c_cell_count(p_cell_id IN NUMBER) IS
45 SELECT COUNT(*)
46 FROM ams_cells_vl ACV
47 WHERE ACV.cell_id = p_cell_id;
48
49 CURSOR c_default_cell_user_status_id IS
50 SELECT user_status_id
51 FROM ams_user_statuses_vl
52 WHERE system_status_type = 'AMS_LIST_SEGMENT_STATUS'
53 AND system_status_code = 'DRAFT'
54 AND enabled_flag = 'Y'
55 AND default_flag = 'Y';
56
57 BEGIN
58
59 --------------------- initialize -----------------------
60 SAVEPOINT create_cell;
61
62 AMS_Utility_PVT.debug_message(l_full_name||': start');
63
64 IF FND_API.to_boolean(p_init_msg_list) THEN
65 FND_MSG_PUB.initialize;
66 END IF;
67
68 IF NOT FND_API.compatible_api_call(
69 l_api_version,
70 p_api_version,
71 l_api_name,
72 g_pkg_name
73 ) THEN
74 RAISE FND_API.g_exc_unexpected_error;
75 END IF;
76
77 x_return_status := FND_API.g_ret_sts_success;
78
79
80 OPEN c_default_cell_user_status_id;
81 FETCH c_default_cell_user_status_id INTO l_cell_rec.user_status_id;
82 CLOSE c_default_cell_user_status_id;
83
84 ----------------------- validate -----------------------
85 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
86
87 validate_cell(
88 p_api_version => l_api_version,
89 p_init_msg_list => p_init_msg_list,
90 p_validation_level => p_validation_level,
91 x_return_status => l_return_status,
92 x_msg_count => x_msg_count,
93 x_msg_data => x_msg_data,
94 p_cell_rec => l_cell_rec
95 );
96 IF l_return_status = FND_API.g_ret_sts_error THEN
97 RAISE FND_API.g_exc_error;
98 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
99 RAISE FND_API.g_exc_unexpected_error;
100 END IF;
101
102 -- try to generate a unique id from the sequence
103 IF l_cell_rec.cell_id IS NULL THEN
104 LOOP
105
106 -- dbms_output.put_line('CELL ID = ' ||l_cell_rec.cell_id);
107 OPEN c_cell_seq;
108 FETCH c_cell_seq INTO l_cell_rec.cell_id;
109 CLOSE c_cell_seq;
110 -- l_cell_count := 0;
111 OPEN c_cell_count(l_cell_rec.cell_id);
112 FETCH c_cell_count INTO l_cell_count;
113 CLOSE c_cell_count;
114 -- dbms_output.put_line('CELL ID = ' ||l_cell_rec.cell_id || ' Count = ' || l_cell_count);
115 EXIT WHEN l_cell_count = 0;
116 END LOOP;
117 END IF;
118
119 -------------------------- insert --------------------------
120 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
121 AMS_Utility_PVT.debug_message('CELLiD' ||l_cell_rec.cell_id);
122
123 INSERT INTO ams_cells_all_b(
124 cell_id,
125 sel_type,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 object_version_number,
132 cell_code,
133 MARKET_SEGMENT_FLAG,
134 ENABLED_FLAG,
135 ORIGINAL_SIZE,
136 PARENT_CELL_ID,
137 owner_id,
138 ORG_ID,
139 user_status_id,
140 status_code,
141 status_date,
142 country
143 )
144 VALUES(
145 l_cell_rec.cell_id,
146 l_cell_rec.sel_type,
147 SYSDATE,
148 FND_GLOBAL.user_id,
149 SYSDATE,
150 FND_GLOBAL.user_id,
151 FND_GLOBAL.conc_login_id,
152 1, -- object_version_number
153 l_cell_rec.cell_code,
154 'Y', -- always to be true for segment
155 NVL(l_cell_rec.enabled_flag,'Y'),
156 l_cell_rec.ORIGINAL_SIZE ,
157 l_cell_rec.PARENT_CELL_ID,
158 l_cell_rec.owner_id,--FND_GLOBAL.user_id,
159 TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)),
160 NVL(l_cell_rec.user_status_id, 400),
161 NVL(l_cell_rec.status_code, 'DRAFT'),
162 SYSDATE,
163 FND_PROFILE.value ('AMS_SRCGEN_USER_CITY')
164 );
165
166 INSERT INTO ams_cells_all_tl(
167 cell_id,
168 language,
169 last_update_date,
170 last_updated_by,
171 creation_date,
172 created_by,
173 last_update_login,
174 source_lang,
175 cell_name,
176 description
177 )
178 SELECT
179 l_cell_rec.cell_id,
180 l.language_code,
181 SYSDATE,
182 FND_GLOBAL.user_id,
183 SYSDATE,
184 FND_GLOBAL.user_id,
185 FND_GLOBAL.conc_login_id,
186 USERENV('LANG'),
187 l_cell_rec.cell_name,
188 l_cell_rec.description
189 FROM fnd_languages l
190 WHERE l.installed_flag in ('I', 'B')
191 AND NOT EXISTS(
192 SELECT NULL
193 FROM ams_cells_all_tl t
194 WHERE t.cell_id = l_cell_rec.cell_id
195 AND t.language = l.language_code );
196
197 --dbms_output.put_line('returen status is: ' || x_return_status);
198 ------------------------- finish -------------------------------
199 x_cell_id := l_cell_rec.cell_id;
200
201 IF FND_API.to_boolean(p_commit) THEN
202 COMMIT;
203 END IF;
204
205 FND_MSG_PUB.count_and_get(
206 p_encoded => FND_API.g_false,
207 p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 AMS_Utility_PVT.debug_message(l_full_name ||': end');
212
213 EXCEPTION
214
215 WHEN FND_API.g_exc_error THEN
216 ROLLBACK TO create_cell;
217 x_return_status := FND_API.g_ret_sts_error;
218 FND_MSG_PUB.count_and_get(
219 p_encoded => FND_API.g_false,
220 p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223 WHEN FND_API.g_exc_unexpected_error THEN
224 ROLLBACK TO create_cell;
225 x_return_status := FND_API.g_ret_sts_unexp_error ;
226 FND_MSG_PUB.count_and_get(
227 p_encoded => FND_API.g_false,
228 p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 WHEN OTHERS THEN
233 ROLLBACK TO create_cell;
234 x_return_status := FND_API.g_ret_sts_unexp_error ;
235
236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
237 THEN
238 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
239 END IF;
240
241 FND_MSG_PUB.count_and_get(
245 );
242 p_encoded => FND_API.g_false,
243 p_count => x_msg_count,
244 p_data => x_msg_data
246 END create_cell;
247
248
249 ---------------------------------------------------------------
250 -- PROCEDURE
251 -- delete_cell
252 --
253 -- HISTORY
254 -- 12/15/99 mpande Created.
255 ---------------------------------------------------------------
256 PROCEDURE delete_cell(
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
259 p_commit IN VARCHAR2 := FND_API.g_false,
260
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264
265 p_cell_id IN NUMBER,
266 p_object_version IN NUMBER
267 )
268 IS
269
270 l_api_version CONSTANT NUMBER := 1.0;
271 l_api_name CONSTANT VARCHAR2(30) := 'delete_cell';
272 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
273
274 BEGIN
275
276 --------------------- initialize -----------------------
277 SAVEPOINT delete_cell;
278
279 AMS_Utility_PVT.debug_message(l_full_name||': start');
280
281 IF FND_API.to_boolean(p_init_msg_list) THEN
282 FND_MSG_PUB.initialize;
283 END IF;
284
285 IF NOT FND_API.compatible_api_call(
286 l_api_version,
287 p_api_version,
288 l_api_name,
289 g_pkg_name
290 ) THEN
291 RAISE FND_API.g_exc_unexpected_error;
292 END IF;
293
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 ------------------------ delete ------------------------
297 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
298
299 UPDATE ams_cells_all_b
300 SET enabled_flag = 'N'
301 WHERE cell_id = p_cell_id
302 AND object_version_number = p_object_version;
303
304 IF (SQL%NOTFOUND) THEN
305 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
306 THEN
307 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
308 FND_MSG_PUB.add;
309 END IF;
310 RAISE FND_API.g_exc_error;
311 END IF;
312
313
314 -------------------- finish --------------------------
315 IF FND_API.to_boolean(p_commit) THEN
316 COMMIT;
317 END IF;
318
319 FND_MSG_PUB.count_and_get(
320 p_encoded => FND_API.g_false,
321 p_count => x_msg_count,
322 p_data => x_msg_data
323 );
324
325 AMS_Utility_PVT.debug_message(l_full_name ||': end');
326
327 EXCEPTION
328
329 WHEN FND_API.g_exc_error THEN
330 ROLLBACK TO delete_cell;
331 x_return_status := FND_API.g_ret_sts_error;
332 FND_MSG_PUB.count_and_get(
333 p_encoded => FND_API.g_false,
334 p_count => x_msg_count,
335 p_data => x_msg_data
336 );
337
338 WHEN FND_API.g_exc_unexpected_error THEN
339 ROLLBACK TO delete_cell;
340 x_return_status := FND_API.g_ret_sts_unexp_error ;
341 FND_MSG_PUB.count_and_get(
342 p_encoded => FND_API.g_false,
343 p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346
347 WHEN OTHERS THEN
348 ROLLBACK TO delete_cell;
349 x_return_status := FND_API.g_ret_sts_unexp_error ;
350
351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
352 THEN
353 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
354 END IF;
355
356 FND_MSG_PUB.count_and_get(
357 p_encoded => FND_API.g_false,
358 p_count => x_msg_count,
359 p_data => x_msg_data
360 );
361
362 END delete_cell;
363
364
365 -------------------------------------------------------------------
366 -- PROCEDURE
367 -- lock_cell
368 --
369 -- HISTORY
370 -- 12/15/99 mpande Created.
371 --------------------------------------------------------------------
372 PROCEDURE lock_cell(
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
375
376 x_return_status OUT NOCOPY VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 x_msg_data OUT NOCOPY VARCHAR2,
379
380 p_cell_id IN NUMBER,
381 p_object_version IN NUMBER
382 )
383 IS
384
385 l_api_version CONSTANT NUMBER := 1.0;
386 l_api_name CONSTANT VARCHAR2(30) := 'lock_cell';
387 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
388
389 l_cell_id NUMBER;
390
391 CURSOR c_cell_b IS
392 SELECT cell_id
393 FROM ams_cells_all_b
394 WHERE cell_id = p_cell_id
395 AND object_version_number = p_object_version
396 FOR UPDATE NOWAIT;
397
398 CURSOR c_cell_tl IS
399 SELECT cell_id
400 FROM ams_cells_all_tl
401 WHERE cell_id = p_cell_id
402 AND USERENV('LANG') IN (language, source_lang)
403 FOR UPDATE NOWAIT;
404
405 BEGIN
406
407 -------------------- initialize ------------------------
408 AMS_Utility_PVT.debug_message(l_full_name||': start');
409
410 IF FND_API.to_boolean(p_init_msg_list) THEN
411 FND_MSG_PUB.initialize;
412 END IF;
413
414 IF NOT FND_API.compatible_api_call(
415 l_api_version,
416 p_api_version,
417 l_api_name,
418 g_pkg_name
419 ) THEN
420 RAISE FND_API.g_exc_unexpected_error;
421 END IF;
422
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 ------------------------ lock -------------------------
426 AMS_Utility_PVT.debug_message(l_full_name||': lock');
427
428 OPEN c_cell_b;
429 FETCH c_cell_b INTO l_cell_id;
430 IF (c_cell_b%NOTFOUND) THEN
431 CLOSE c_cell_b;
432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
433 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
434 FND_MSG_PUB.add;
435 END IF;
436 RAISE FND_API.g_exc_error;
437 END IF;
438 CLOSE c_cell_b;
439
440 OPEN c_cell_tl;
441 CLOSE c_cell_tl;
442
443 -------------------- finish --------------------------
444 FND_MSG_PUB.count_and_get(
445 p_encoded => FND_API.g_false,
446 p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449
450 AMS_Utility_PVT.debug_message(l_full_name ||': end');
451
452 EXCEPTION
453
454 WHEN AMS_Utility_PVT.resource_locked THEN
455 x_return_status := FND_API.g_ret_sts_error;
456 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
457 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
458 FND_MSG_PUB.add;
459 END IF;
460
461 FND_MSG_PUB.count_and_get(
462 p_encoded => FND_API.g_false,
463 p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466
467 WHEN FND_API.g_exc_error THEN
468 x_return_status := FND_API.g_ret_sts_error;
469 FND_MSG_PUB.count_and_get(
470 p_encoded => FND_API.g_false,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474
475 WHEN FND_API.g_exc_unexpected_error THEN
476 x_return_status := FND_API.g_ret_sts_unexp_error ;
477 FND_MSG_PUB.count_and_get(
478 p_encoded => FND_API.g_false,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482
483 WHEN OTHERS THEN
484 x_return_status := FND_API.g_ret_sts_unexp_error ;
485 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
486 THEN
487 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
488 END IF;
489
490 FND_MSG_PUB.count_and_get(
491 p_encoded => FND_API.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 END lock_cell;
497
498
499 ---------------------------------------------------------------------
500 -- PROCEDURE
501 -- update_cell
502 --
503 -- HISTORY
504 -- 12/15/99 mpande Created.
505 -- 01/21/01 yxliu Added sel_type
506 -- 10/23/01 yxliu owner_id can be changed
507 ----------------------------------------------------------------------
508 PROCEDURE update_cell(
509 p_api_version IN NUMBER,
510 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
511 p_commit IN VARCHAR2 := FND_API.g_false,
512 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
513
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517
518 p_cell_rec IN cell_rec_type
519 )
520 IS
521
522 l_api_version CONSTANT NUMBER := 1.0;
523 l_api_name CONSTANT VARCHAR2(30) := 'update_cell';
524 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
525
526 l_cell_rec cell_rec_type;
527 l_return_status VARCHAR2(1);
528
529 BEGIN
530
531 -------------------- initialize -------------------------
532 SAVEPOINT update_cell;
533
534 AMS_Utility_PVT.debug_message(l_full_name||': start');
535
536 IF FND_API.to_boolean(p_init_msg_list) THEN
537 FND_MSG_PUB.initialize;
538 END IF;
539
540 IF NOT FND_API.compatible_api_call(
541 l_api_version,
542 p_api_version,
543 l_api_name,
544 g_pkg_name
545 ) THEN
546 RAISE FND_API.g_exc_unexpected_error;
547 END IF;
548
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 ----------------------- validate ----------------------
552 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
553 AMS_Utility_PVT.debug_message(l_full_name ||': just before call the complete_cell_rec');
554
555 -- replace g_miss_char/num/date with current column values
556 complete_cell_rec(p_cell_rec, l_cell_rec);
557
558 AMS_Utility_PVT.debug_message(l_full_name ||': after complete_cell_rec');
559 -- item level
560 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
561 check_cell_items(
562 p_cell_rec => l_cell_rec,
563 p_validation_mode => JTF_PLSQL_API.g_update,
564 x_return_status => l_return_status
565 );
566 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
567 RAISE FND_API.g_exc_unexpected_error;
568 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
569 RAISE FND_API.g_exc_error;
570 END IF;
571 END IF;
572
573 -- record level
574 -------------------------- update --------------------
575 AMS_Utility_PVT.debug_message(l_full_name ||': update' ||l_cell_rec.cell_id);
576
577 UPDATE ams_cells_all_b SET
578 sel_type = l_cell_rec.sel_type,
579 last_update_date = SYSDATE,
580 last_updated_by = FND_GLOBAL.user_id,
581 last_update_login = FND_GLOBAL.conc_login_id,
582 object_version_number = l_cell_rec.object_version_number + 1,
583 cell_code = l_cell_rec.cell_code,
584 enabled_flag = NVL(l_cell_rec.enabled_flag,'Y'),
585 parent_cell_id = l_cell_rec.parent_cell_id,
586 original_size = l_cell_rec.original_size,
587 user_status_id = l_cell_rec.user_status_id,
588 status_code = l_cell_rec.status_code,
589 status_date = l_cell_rec.status_date,
590 owner_id = l_cell_rec.owner_id
591 WHERE cell_id = l_cell_rec.cell_id
592 AND object_version_number = l_cell_rec.object_version_number;
593
594 IF (SQL%NOTFOUND) THEN
595 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
596 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
597 FND_MSG_PUB.add;
598 END IF;
599 AMS_Utility_PVT.debug_message(l_full_name ||': update b');
600 RAISE FND_API.g_exc_error;
601 END IF;
602
603 update ams_cells_all_tl set
604 cell_name = l_cell_rec.cell_name,
605 description = l_cell_rec.description,
606 last_update_date = SYSDATE,
607 last_updated_by = FND_GLOBAL.user_id,
608 last_update_login = FND_GLOBAL.conc_login_id,
609 source_lang = USERENV('LANG')
610 WHERE cell_id = l_cell_rec.cell_id
611 AND USERENV('LANG') IN (language, source_lang);
612
613 IF (SQL%NOTFOUND) THEN
614 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
615 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
616 FND_MSG_PUB.add;
617 AMS_Utility_PVT.debug_message(l_full_name ||': updatetl');
618 END IF;
619 RAISE FND_API.g_exc_error;
620 END IF;
621
622 -------------------- finish --------------------------
623 IF FND_API.to_boolean(p_commit) THEN
624 COMMIT;
625 END IF;
626
627 FND_MSG_PUB.count_and_get(
628 p_encoded => FND_API.g_false,
629 p_count => x_msg_count,
630 p_data => x_msg_data
631 );
632
633 AMS_Utility_PVT.debug_message(l_full_name ||': end');
634
635 EXCEPTION
636
637 WHEN FND_API.g_exc_error THEN
638 ROLLBACK TO update_cell;
639 x_return_status := FND_API.g_ret_sts_error;
640 FND_MSG_PUB.count_and_get(
641 p_encoded => FND_API.g_false,
642 p_count => x_msg_count,
643 p_data => x_msg_data
644 );
645
646 WHEN FND_API.g_exc_unexpected_error THEN
647 ROLLBACK TO update_cell;
648 x_return_status := FND_API.g_ret_sts_unexp_error;
649 FND_MSG_PUB.count_and_get(
650 p_encoded => FND_API.g_false,
651 p_count => x_msg_count,
652 p_data => x_msg_data
653 );
654
655 WHEN OTHERS THEN
656 ROLLBACK TO update_cell;
657 x_return_status := FND_API.g_ret_sts_unexp_error;
658
659 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
660 THEN
661 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
662 END IF;
663
664 FND_MSG_PUB.count_and_get(
665 p_encoded => FND_API.g_false,
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 END update_cell;
671
672
673 --------------------------------------------------------------------
674 -- PROCEDURE
675 -- validate_cell
676 --
677 -- HISTORY
678 -- 12/15/99 mpande Created.
679 --------------------------------------------------------------------
680 PROCEDURE validate_cell(
681 p_api_version IN NUMBER,
682 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
683 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
684
685 x_return_status OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_msg_data OUT NOCOPY VARCHAR2,
688
689 p_cell_rec IN cell_rec_type
690 )
691 IS
692
693 l_api_version CONSTANT NUMBER := 1.0;
694 l_api_name CONSTANT VARCHAR2(30) := 'validate_cell';
695 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
696
697 l_return_status VARCHAR2(1);
698
699 BEGIN
700
701 ----------------------- initialize --------------------
702 AMS_Utility_PVT.debug_message(l_full_name||': start');
703
704 IF FND_API.to_boolean(p_init_msg_list) THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707
708 IF NOT FND_API.compatible_api_call(
709 l_api_version,
710 p_api_version,
711 l_api_name,
712 g_pkg_name
713 ) THEN
714 RAISE FND_API.g_exc_unexpected_error;
715 END IF;
716
717 x_return_status := FND_API.g_ret_sts_success;
718
719 ---------------------- validate ------------------------
720 AMS_Utility_PVT.debug_message(l_full_name||': check items');
721
722 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
723 check_cell_items(
724 p_cell_rec => p_cell_rec,
725 p_validation_mode => JTF_PLSQL_API.g_create,
726 x_return_status => l_return_status
727 );
728 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
729 RAISE FND_API.g_exc_unexpected_error;
730 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
731 RAISE FND_API.g_exc_error;
732 END IF;
733 END IF;
734
735
736 -------------------- finish --------------------------
737 FND_MSG_PUB.count_and_get(
738 p_encoded => FND_API.g_false,
739 p_count => x_msg_count,
740 p_data => x_msg_data
741 );
742
743 AMS_Utility_PVT.debug_message(l_full_name ||': end');
744
745 EXCEPTION
746
747 WHEN FND_API.g_exc_error THEN
748 x_return_status := FND_API.g_ret_sts_error;
749 FND_MSG_PUB.count_and_get(
750 p_encoded => FND_API.g_false,
751 p_count => x_msg_count,
752 p_data => x_msg_data
753 );
754
755 WHEN FND_API.g_exc_unexpected_error THEN
756 x_return_status := FND_API.g_ret_sts_unexp_error ;
757 FND_MSG_PUB.count_and_get(
758 p_encoded => FND_API.g_false,
759 p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 WHEN OTHERS THEN
764 x_return_status := FND_API.g_ret_sts_unexp_error;
765 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
766 THEN
767 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
768 END IF;
769
770 FND_MSG_PUB.count_and_get(
771 p_encoded => FND_API.g_false,
772 p_count => x_msg_count,
773 p_data => x_msg_data
774 );
775
776 END validate_cell;
777
778 ---------------------------------------------------------------------
779 -- PROCEDURE
780 -- check_cell_req_items
781 --
782 -- HISTORY
783 -- 12/15/99 mpande Created.
784 --
785 -- NOTES
786
787 ---------------------------------------------------------------------
788 PROCEDURE check_cell_req_items(
789 p_cell_rec IN cell_rec_type,
790 x_return_status OUT NOCOPY VARCHAR2
791 )
792 IS
793 BEGIN
794
795 x_return_status := FND_API.g_ret_sts_success;
796 ------------------------ cell_code --------------------------
797 IF p_cell_rec.cell_code IS NULL THEN
798 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
799 THEN
800 FND_MESSAGE.set_name('AMS', 'AMS_CELL_NO_CODE');
801 FND_MSG_PUB.add;
802 END IF;
803 x_return_status := FND_API.g_ret_sts_error;
804 RETURN;
805 END IF;
806
807 ------------------------ cell_name --------------------------
808 IF p_cell_rec.cell_name IS NULL THEN
809 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
810 THEN
811 FND_MESSAGE.set_name('AMS', 'AMS_CELL_NO_NAME');
812 FND_MSG_PUB.add;
813 END IF;
814 x_return_status := FND_API.g_ret_sts_error;
815 RETURN;
816 END IF;
817
818 END check_cell_req_items;
819
820
821 ---------------------------------------------------------------------
822 -- PROCEDURE
823 -- check_cell_uk_items
824 --
825 -- HISTORY
826 -- 12/15/99 mpande Created.
827 ---------------------------------------------------------------------
828 PROCEDURE check_cell_uk_items(
829 p_cell_rec IN cell_rec_type,
830 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
831 x_return_status OUT NOCOPY VARCHAR2
832 )
833 IS
834 l_valid_flag VARCHAR2(1);
835 BEGIN
836
837 x_return_status := FND_API.g_ret_sts_success;
838
839 -- For create_cell, when cell_id is passed in, we need to
840 -- check if this cell_id is unique.
841 IF p_validation_mode = JTF_PLSQL_API.g_create
842 AND p_cell_rec.cell_id IS NOT NULL
843 THEN
844 IF AMS_Utility_PVT.check_uniqueness(
845 'ams_cells_vl',
846 'cell_id = ' || p_cell_rec.cell_id
847 ) = FND_API.g_false
848 THEN
849 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
850 THEN
851 FND_MESSAGE.set_name('AMS', 'AMS_CELL_DUPLICATE_ID');
852 FND_MSG_PUB.add;
853 END IF;
854 x_return_status := FND_API.g_ret_sts_error;
855 RETURN;
856 END IF;
857 END IF;
858
859
860 -- Check if cell_name is unique. Need to handle create and
861 -- update differently.
862 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
863 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
867 ELSE
864 'ams_cells_vl',
865 'cell_name = ''' || p_cell_rec.cell_name ||''''
866 );
868 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
869 'ams_cells_vl',
870 'cell_name = ''' || p_cell_rec.cell_name ||
871 ''' AND cell_id <> ' || p_cell_rec.cell_id
872 );
873 END IF;
874
875 IF l_valid_flag = FND_API.g_false THEN
876 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
877 THEN
878 FND_MESSAGE.set_name('AMS', 'AMS_CELL_DUPLICATE_NAME');
879 FND_MSG_PUB.add;
880 END IF;
881 x_return_status := FND_API.g_ret_sts_error;
882 RETURN;
883 END IF;
884 -- Check if cell_code is unique. Need to handle create and
885 -- update differently.
886 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
887 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
888 'ams_cells_vl',
889 'cell_code = ''' || p_cell_rec.cell_code||''''
890 );
891 ELSE
892 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
893 'ams_cells_vl',
894 'cell_code = ''' || p_cell_rec.cell_name ||
895 ''' AND cell_id <> ' || p_cell_rec.cell_id
896 );
897 END IF;
898
899 IF l_valid_flag = FND_API.g_false THEN
900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
901 THEN
902 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_CODE');
903 FND_MSG_PUB.add;
904 END IF;
905 x_return_status := FND_API.g_ret_sts_error;
906 RETURN;
907 END IF;
908
909 END check_cell_uk_items;
910
911
912 ---------------------------------------------------------------------
913 -- PROCEDURE
914 -- check_cell_fk_items
915 --
916 -- HISTORY
917 -- 12/15/99 mpande Created.
918 ---------------------------------------------------------------------
919 PROCEDURE check_cell_fk_items(
920 p_cell_rec IN cell_rec_type,
921 x_return_status OUT NOCOPY VARCHAR2
922 )
923 IS
924 BEGIN
925 x_return_status := FND_API.g_ret_sts_success;
926 --------------------ownerid---------------------------
927 IF p_cell_rec.owner_id <> FND_API.g_miss_num THEN
928 IF AMS_Utility_PVT.check_fk_exists(
929 'ams_jtf_rs_emp_v',
930 'resource_id',
931 p_cell_rec.owner_id
932 ) = FND_API.g_false
933 THEN
934 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
935 THEN
936 FND_MESSAGE.set_name('AMS', 'AMS_CELL_BAD_OWNER_USER_ID');
937 FND_MSG_PUB.add;
938 END IF;
939 x_return_status := FND_API.g_ret_sts_error;
940 RETURN;
941 END IF;
942 END IF;
943
944 --------------------- parent_cell_id ------------------------
945 IF p_cell_rec.parent_cell_id <> FND_API.g_miss_num THEN
946 IF AMS_Utility_PVT.check_fk_exists(
947 'ams_cells_vl',
948 'cell_id',
949 p_cell_rec.parent_cell_id
950 ) = FND_API.g_false
951 THEN
952 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
953 THEN
954 FND_MESSAGE.set_name('AMS', 'AMS_CELL_WRONG_PARENT_CELL_ID');
955 FND_MSG_PUB.add;
956 END IF;
957 x_return_status := FND_API.g_ret_sts_error;
958 RETURN;
959 END IF;
960 END IF;
961
962 END check_cell_fk_items;
963
964
965
966 ---------------------------------------------------------------------
967 -- PROCEDURE
968 -- check_cell_flag_items
969 --
970 -- HISTORY
971 -- 15/12/99 mpande Created.
972 ---------------------------------------------------------------------
973 PROCEDURE check_cell_flag_items(
974 p_cell_rec IN cell_rec_type,
975 x_return_status OUT NOCOPY VARCHAR2
976 )
977 IS
978 BEGIN
979
980 x_return_status := FND_API.g_ret_sts_success;
981
982 ----------------------- enabled_flag ------------------------
983 IF p_cell_rec.enabled_flag <> FND_API.g_miss_char
984 AND p_cell_rec.enabled_flag IS NOT NULL
985 THEN
986 IF AMS_Utility_PVT.is_Y_or_N(p_cell_rec.enabled_flag) = FND_API.g_false
987 THEN
988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
989 THEN
990 FND_MESSAGE.set_name('AMS', 'AMS_CELL_WRONG_ENABLED_FLAG');
991 FND_MSG_PUB.add;
992 END IF;
993 x_return_status := FND_API.g_ret_sts_error;
994 RETURN;
995 END IF;
996 END IF;
997
998
999 END check_cell_flag_items;
1000
1001 ---------------------------------------------------------------------
1002 -- PROCEDURE
1003 -- check_cell_hier_items
1004 --
1005 -- HISTORY
1006 -- 07/13/01 yxliu Created.
1007 -- 08/28/01 yxliu Modified, Just grasp active children instead of all.
1008 --
1009 -- Note
1010 -- If want to cancel a segment, need to check all its children are
1011 -- cancelled, or archived too.
1012 ---------------------------------------------------------------------
1013 PROCEDURE check_cell_hier_items(
1014 p_cell_rec IN cell_rec_type,
1015 x_return_status OUT NOCOPY VARCHAR2
1016 )
1017 IS
1018 l_active_children_count NUMBER;
1019 l_count number;
1020 cursor c_check_parent(l_id in number,l_child_id in number) is
1021 select count(1)
1025 connect by prior a.cell_id = a.parent_cell_id
1022 from ams_cells_all_b
1023 where cell_id in (select a.cell_id
1024 from ams_cells_all_b a
1026 start with parent_cell_id = l_id)
1027 and cell_id = l_child_id ;
1028
1029 BEGIN
1030
1031 x_return_status := FND_API.g_ret_sts_success;
1032
1033 ----------------------- status ------------------------
1034 IF p_cell_rec.status_code = 'CANCELLED'
1035 THEN
1036 SELECT COUNT(*) INTO l_active_children_count
1037 FROM ams_cells_vl
1038 WHERE parent_cell_id = p_cell_rec.cell_id
1039 AND (status_code = 'DRAFT' or status_code = 'AVAILABLE');
1040
1041 IF l_active_children_count > 0
1042
1043 THEN
1044 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1045 THEN
1046 FND_MESSAGE.set_name('AMS', 'AMS_CELL_CANCEL_ERROR');
1047 FND_MSG_PUB.add;
1048 END IF;
1049 x_return_status := FND_API.g_ret_sts_error;
1050 RETURN;
1051 END IF;
1052 END IF;
1053
1054 x_return_status := FND_API.g_ret_sts_success;
1055
1056 ----------------------- Over lapping child --------------------
1057 IF p_cell_rec.parent_cell_id <> FND_API.g_miss_num
1058 AND p_cell_rec.parent_cell_id IS NOT NULL
1059 THEN
1060 open c_check_parent(p_cell_rec.cell_id,p_cell_rec.parent_cell_id);
1061 fetch c_check_parent into l_count;
1062 close c_check_parent;
1063 IF l_count > 0
1064 THEN
1065 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1066 THEN
1067 FND_MESSAGE.set_name('AMS', 'AMS_CELL_PARENT_ERROR');
1068 FND_MSG_PUB.add;
1069 END IF;
1070 x_return_status := FND_API.g_ret_sts_error;
1071 RETURN;
1072 END IF;
1073 END IF;
1074
1075 END check_cell_hier_items;
1076
1077 ---------------------------------------------------------------------
1078 -- PROCEDURE
1079 -- check_cell_items
1080 --
1081 -- HISTORY
1082 -- 12/15/99 mpande Created.
1083 -- 07/13/01 yxliu Added check_cell_hier_items
1084 ---------------------------------------------------------------------
1085 PROCEDURE check_cell_items(
1086 p_cell_rec IN cell_rec_type,
1087 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1088 x_return_status OUT NOCOPY VARCHAR2
1089 )
1090 IS
1091 BEGIN
1092
1093 check_cell_req_items(
1094 p_cell_rec => p_cell_rec,
1095 x_return_status => x_return_status
1096 );
1097
1098 IF x_return_status <> FND_API.g_ret_sts_success THEN
1099 RETURN;
1100 END IF;
1101
1102 check_cell_uk_items(
1103 p_cell_rec => p_cell_rec,
1104 p_validation_mode => p_validation_mode,
1105 x_return_status => x_return_status
1106 );
1107
1108 IF x_return_status <> FND_API.g_ret_sts_success THEN
1109 RETURN;
1110 END IF;
1111
1112 check_cell_fk_items(
1113 p_cell_rec => p_cell_rec,
1114 x_return_status => x_return_status
1115 );
1116
1117 IF x_return_status <> FND_API.g_ret_sts_success THEN
1118 RETURN;
1119 END IF;
1120
1121 check_cell_flag_items(
1122 p_cell_rec => p_cell_rec,
1123 x_return_status => x_return_status
1124 );
1125
1126 IF x_return_status <> FND_API.g_ret_sts_success THEN
1127 RETURN;
1128 END IF;
1129
1130 check_cell_hier_items(
1131 p_cell_rec => p_cell_rec,
1132 x_return_status => x_return_status
1133 );
1134
1135 IF x_return_status <> FND_API.g_ret_sts_success THEN
1136 RETURN;
1137 END IF;
1138 END check_cell_items;
1139
1143 --
1140 ---------------------------------------------------------------------
1141 -- PROCEDURE
1142 -- init_cell_rec
1144 -- HISTORY
1145 -- 12/15/99 mpande Created.
1146 -- 08/28/01 yxliu Add new columns
1147 ---------------------------------------------------------------------
1148 PROCEDURE init_cell_rec(
1149 x_cell_rec OUT NOCOPY cell_rec_type
1150 )
1151 IS
1152 BEGIN
1153 -- dbms_output.put_line('init Start');
1154 x_cell_rec.cell_id := FND_API.g_miss_num;
1155 x_cell_rec.last_update_date := FND_API.g_miss_date;
1156 x_cell_rec.last_updated_by := FND_API.g_miss_num;
1157 x_cell_rec.creation_date := FND_API.g_miss_date;
1158 x_cell_rec.created_by := FND_API.g_miss_num;
1159 x_cell_rec.last_update_login := FND_API.g_miss_num;
1160 x_cell_rec.object_version_number := FND_API.g_miss_num;
1161 x_cell_rec.parent_cell_id := FND_API.g_miss_num;
1162 x_cell_rec.cell_code := FND_API.g_miss_char;
1163 x_cell_rec.original_size := FND_API.g_miss_num;
1164 x_cell_rec.enabled_flag := FND_API.g_miss_char;
1165 x_cell_rec.owner_id := FND_API.g_miss_num;
1166 x_cell_rec.cell_name := FND_API.g_miss_char;
1167 x_cell_rec.description := FND_API.g_miss_char;
1168 x_cell_rec.sel_type := FND_API.g_miss_char;
1169 x_cell_rec.org_id := FND_API.g_miss_num;
1170 x_cell_rec.status_code := FND_API.g_miss_char;
1171 x_cell_rec.status_date := FND_API.g_miss_date;
1172 x_cell_rec.user_status_id := FND_API.g_miss_num;
1173
1174 -- dbms_output.put_line('init End');
1175 END init_cell_rec;
1176
1177
1178 ---------------------------------------------------------------------
1179 -- PROCEDURE
1180 -- complete_cell_rec
1181 --
1182 -- HISTORY
1183 -- 12/15/99 mpande Created.
1184 ---------------------------------------------------------------------
1185 PROCEDURE complete_cell_rec(
1186 p_cell_rec IN cell_rec_type,
1187 x_complete_rec OUT NOCOPY cell_rec_type
1188 )
1189 IS
1190
1191 CURSOR c_cell IS
1192 SELECT *
1193 FROM ams_cells_vl
1194 WHERE cell_id = p_cell_rec.cell_id;
1195
1196 l_cell_rec c_cell%ROWTYPE;
1197
1198 BEGIN
1199
1200 x_complete_rec := p_cell_rec;
1201
1202 OPEN c_cell;
1203 FETCH c_cell INTO l_cell_rec;
1204 IF c_cell%NOTFOUND THEN
1205 CLOSE c_cell;
1206 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1207 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1208 FND_MSG_PUB.add;
1209 END IF;
1210 RAISE FND_API.g_exc_error;
1211 END IF;
1212 CLOSE c_cell;
1213
1214 IF p_cell_rec.owner_id = FND_API.g_miss_num THEN
1215 x_complete_rec.owner_id := l_cell_rec.owner_id;
1216 END IF;
1217
1218 IF p_cell_rec.enabled_flag = FND_API.g_miss_char THEN
1219 x_complete_rec.enabled_flag := l_cell_rec.enabled_flag;
1220 END IF;
1221
1222 IF p_cell_rec.original_size = FND_API.g_miss_num THEN
1223 x_complete_rec.original_size := l_cell_rec.original_size;
1224 END IF;
1225
1226 IF p_cell_rec.cell_code = FND_API.g_miss_char THEN
1227 x_complete_rec.cell_code := l_cell_rec.cell_code;
1228 END IF;
1229
1230 IF p_cell_rec.parent_cell_id = FND_API.g_miss_num THEN
1231 x_complete_rec.parent_cell_id:= l_cell_rec.parent_cell_id;
1232 END IF;
1233
1234
1235 IF p_cell_rec.cell_name = FND_API.g_miss_char THEN
1236 x_complete_rec.cell_name := l_cell_rec.cell_name;
1237 END IF;
1238
1239 IF p_cell_rec.description = FND_API.g_miss_char THEN
1240 x_complete_rec.description := l_cell_rec.description;
1241 END IF;
1242
1243 IF p_cell_rec.sel_type = FND_API.g_miss_char THEN
1244 x_complete_rec.sel_type := l_cell_rec.sel_type;
1245 END IF;
1246
1247 IF p_cell_rec.user_status_id = FND_API.g_miss_num THEN
1248 x_complete_rec.user_status_id := l_cell_rec.user_status_id;
1249 END IF;
1250
1251 IF p_cell_rec.status_date = FND_API.g_miss_date THEN
1252 x_complete_rec.status_date := l_cell_rec.status_date;
1253 END IF;
1254
1255 x_complete_rec.status_code := AMS_Utility_PVT.get_system_status_code(
1256 x_complete_rec.user_status_id );
1257
1258
1259 END complete_cell_rec;
1260
1261 ---------------------------------------------------------------------
1262 -- PROCEDURE
1263 -- add_sel_workbook
1264 --
1265 -- HISTORY
1266 -- 01/19/01 yxliu Created.
1267 ---------------------------------------------------------------------
1268 PROCEDURE add_sel_workbook(
1269 p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1271 p_commit IN VARCHAR2 := FND_API.g_false,
1272 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1273
1274 x_return_status OUT NOCOPY VARCHAR2,
1275 x_msg_count OUT NOCOPY NUMBER,
1276 x_msg_data OUT NOCOPY VARCHAR2,
1277
1278 p_cell_id IN NUMBER,
1279 p_discoverer_sql_id IN NUMBER
1280 )
1281 IS
1282
1283 l_api_version CONSTANT NUMBER := 1.0;
1284 l_api_name CONSTANT VARCHAR2(30) := 'add_sel_workbook';
1288
1285 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1286
1287 l_return_status VARCHAR2(1);
1289 l_cell_id NUMBER := p_cell_id;
1290 l_discoverer_sql_id NUMBER := p_discoverer_sql_id;
1291 l_act_disc_id NUMBER;
1292
1293 l_sql_string VARCHAR2(32767) := '';
1294 l_from_position NUMBER := 0;
1295
1296 CURSOR c_discoverer_cell is
1297 SELECT workbook_owner_name, workbook_name, worksheet_name
1298 FROM ams_discoverer_sql
1299 WHERE discoverer_sql_id = l_discoverer_sql_id;
1300
1301 l_discoverer_cell_rec c_discoverer_cell%ROWTYPE;
1302
1303 CURSOR c_discoverer_sql (p_workbook_name IN VARCHAR2,
1304 p_worksheet_name IN VARCHAR2,
1305 p_workbook_owner_name IN VARCHAR2) IS
1306 SELECT sql_string, sequence_order
1307 FROM ams_discoverer_sql
1308 WHERE workbook_name = p_workbook_name
1309 AND worksheet_name = p_worksheet_name
1310 AND workbook_owner_name = p_workbook_owner_name
1311 ORDER BY sequence_order;
1312
1313 l_discoverer_sql_rec c_discoverer_sql%ROWTYPE;
1314
1315 cursor c_act_disc_seq is
1316 select ams_act_discoverer_all_s.NEXTVAL
1317 from DUAL;
1318 BEGIN
1319
1320 --------------------- initialize -----------------------
1321 SAVEPOINT add_sel_workbook;
1322
1323 AMS_Utility_PVT.debug_message(l_full_name||': start');
1324
1325 IF FND_API.to_boolean(p_init_msg_list) THEN
1326 FND_MSG_PUB.initialize;
1327 END IF;
1328
1329 IF NOT FND_API.compatible_api_call(
1330 l_api_version,
1331 p_api_version,
1332 l_api_name,
1333 g_pkg_name
1334 ) THEN
1335 RAISE FND_API.g_exc_unexpected_error;
1336 END IF;
1337
1338 x_return_status := FND_API.g_ret_sts_success;
1339
1340 ------------------------ start -------------------------
1341 AMS_Utility_PVT.debug_message(l_full_name ||': start');
1342
1343 OPEN c_discoverer_cell;
1344 FETCH c_discoverer_cell INTO l_discoverer_cell_rec;
1345 IF c_discoverer_cell%NOTFOUND THEN
1346 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1347 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1348 FND_MSG_PUB.add;
1349 END IF;
1350 RAISE FND_API.g_exc_error;
1351 END IF;
1352 CLOSE c_discoverer_cell;
1353
1354 ------------------ Verify the sql string --------------
1355 -- Verify that the sql string in this workbook is a valid cell sql string,
1356 -- e.g., there is party_id in the select column.
1357
1358 AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1359 OPEN c_discoverer_sql (l_discoverer_cell_rec.workbook_name,
1360 l_discoverer_cell_rec.worksheet_name,
1361 l_discoverer_cell_rec.workbook_owner_name);
1362 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1363 WHILE c_discoverer_sql%FOUND
1364 LOOP
1365 l_sql_string := l_sql_string || l_discoverer_sql_rec.sql_string;
1366 -- dbms_output.put_line('sequence_order is ' ||
1367 -- l_discoverer_sql_rec.sequence_order);
1368 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1369 END LOOP;
1370 CLOSE c_discoverer_sql;
1371
1372 l_sql_string := upper(l_sql_string);
1373
1374 -- find ' from ' position
1375 IF instr(l_sql_string, ' FROM ') > 0
1376 THEN
1377 l_from_position := instr(l_sql_string, ' FROM ');
1378 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ') > 0
1379 THEN
1380 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ');
1381 ELSIF instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10)) > 0
1382 THEN
1383 l_from_position := instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10));
1384 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10)) >0
1385 THEN
1386 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10));
1387 END IF;
1388
1389 IF instr(l_sql_string, 'PARTY_ID') = 0
1390 OR l_from_position = 0
1391 OR instr(l_sql_string, 'PARTY_ID') > l_from_position
1392 THEN
1393 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1394 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_SQL');
1395 FND_MSG_PUB.add;
1396 END IF;
1397 RAISE FND_API.g_exc_error;
1398 -- x_return_status := FND_API.g_ret_sts_error;
1399 RETURN;
1400 END IF;
1401
1402
1403 -- Don't support "order by" and "group by" in query
1404 -- Check if query has these clauses
1405 IF instr(l_sql_string, 'ORDER BY') > 0
1406 OR instr(l_sql_string, 'GROUP BY') > 0
1407 THEN
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1409 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_ORDERBY');
1410 FND_MSG_PUB.add;
1411 END IF;
1412 RAISE FND_API.g_exc_error;
1413 -- x_return_status := FND_API.g_ret_sts_error;
1414 RETURN;
1415 END IF;
1416
1417
1418 ----------------------- Insert ---------------------------
1419 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
1420
1421 OPEN c_act_disc_seq;
1422 FETCH c_act_disc_seq INTO l_act_disc_id;
1423 CLOSE c_act_disc_seq;
1424
1425 INSERT INTO ams_act_discoverer_all(
1426 activity_discoverer_id,
1427 last_update_date,
1428 last_updated_by,
1429 creation_date,
1430 created_by,
1431 last_update_login,
1432 object_version_number,
1433 workbook_name,
1437 discoverer_sql_id,
1434 workbook_owner,
1435 act_discoverer_used_by_id,
1436 arc_act_discoverer_used_by,
1438 worksheet_name
1439 )
1440 VALUES(
1441 l_act_disc_id,
1442 SYSDATE,
1443 FND_GLOBAL.user_id,
1444 SYSDATE,
1445 FND_GLOBAL.user_id,
1446 FND_GLOBAL.conc_login_id,
1447 1,
1448 l_discoverer_cell_rec.workbook_name,
1449 l_discoverer_cell_rec.workbook_owner_name,
1450 l_cell_id,
1451 'CELL',
1452 l_discoverer_sql_id,
1453 l_discoverer_cell_rec.worksheet_name
1454 );
1455
1456
1457 ------------------------- finish -------------------------------
1458
1459 IF FND_API.to_boolean(p_commit) THEN
1460 COMMIT;
1461 END IF;
1462
1463 FND_MSG_PUB.count_and_get(
1464 p_encoded => FND_API.g_false,
1465 p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468
1469 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1470
1471 EXCEPTION
1472
1473 WHEN FND_API.g_exc_error THEN
1474 ROLLBACK TO add_sel_workbook;
1475 x_return_status := FND_API.g_ret_sts_error;
1476 FND_MSG_PUB.count_and_get(
1477 p_encoded => FND_API.g_false,
1478 p_count => x_msg_count,
1479 p_data => x_msg_data
1480 );
1481
1482 WHEN FND_API.g_exc_unexpected_error THEN
1483 ROLLBACK TO add_sel_workbook;
1484 x_return_status := FND_API.g_ret_sts_unexp_error ;
1485 FND_MSG_PUB.count_and_get(
1486 p_encoded => FND_API.g_false,
1487 p_count => x_msg_count,
1488 p_data => x_msg_data
1489 );
1490
1491
1492 WHEN OTHERS THEN
1493 ROLLBACK TO add_sel_workbook;
1494 x_return_status := FND_API.g_ret_sts_unexp_error ;
1495
1496 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1497 THEN
1498 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1499 END IF;
1500
1501 FND_MSG_PUB.count_and_get(
1502 p_encoded => FND_API.g_false,
1503 p_count => x_msg_count,
1504 p_data => x_msg_data
1505 );
1506
1507 END add_sel_workbook;
1508
1509 ---------------------------------------------------------------------
1510 -- PROCEDURE
1511 -- add_sel_sql
1512 --
1513 -- HISTORY
1514 -- 02/02/01 yxliu Created.
1515 -- 04/10/01 yxliu Modified. Use AMS_List_Query_PVT.Create_List_Query.
1516 ---------------------------------------------------------------------
1517 PROCEDURE add_sel_sql(
1518 p_api_version IN NUMBER,
1519 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1520 p_commit IN VARCHAR2 := FND_API.g_false,
1521 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1522
1523 x_return_status OUT NOCOPY VARCHAR2,
1524 x_msg_count OUT NOCOPY NUMBER,
1525 x_msg_data OUT NOCOPY VARCHAR2,
1526
1527 p_cell_id IN NUMBER,
1531 p_source_object_name IN VARCHAR2
1528 p_cell_name IN VARCHAR2,
1529 p_cell_code IN VARCHAR2,
1530 p_sql_string IN VARCHAR2,
1532 )
1533 IS
1534
1535 l_api_version CONSTANT NUMBER := 1.0;
1536 l_api_name CONSTANT VARCHAR2(30) := 'add_sel_sql';
1537 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1538
1539 l_return_status VARCHAR2(1);
1540
1541 l_sql_string VARCHAR2(20000) := p_sql_string;
1542
1543 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type := AMS_List_Query_PVT.g_miss_list_query_rec;
1544 l_list_query_id NUMBER := FND_API.G_MISS_NUM;
1545
1546 l_from_position NUMBER := 0;
1547
1548 BEGIN
1549
1550 --------------------- initialize -----------------------
1551 SAVEPOINT add_sel_sql;
1552
1553 AMS_Utility_PVT.debug_message(l_full_name||': initialize');
1554
1555 IF FND_API.to_boolean(p_init_msg_list) THEN
1556 FND_MSG_PUB.initialize;
1557 END IF;
1558
1559 IF NOT FND_API.compatible_api_call(
1560 l_api_version,
1561 p_api_version,
1562 l_api_name,
1563 g_pkg_name
1564 ) THEN
1565 RAISE FND_API.g_exc_unexpected_error;
1566 END IF;
1567
1568 x_return_status := FND_API.g_ret_sts_success;
1569
1570 ------------------------ start -------------------------
1571 AMS_Utility_PVT.debug_message(l_full_name ||': start');
1572
1573 ------------------ Verify the sql string --------------
1574 -- Verify that the l_sql_string is a valid cell sql string,
1575 -- e.g., there is party_id in the select column.
1576
1577 AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1578
1579 l_sql_string := upper(l_sql_string);
1580
1581 IF instr(l_sql_string, ' FROM ') > 0
1582 THEN
1583 l_from_position := instr(l_sql_string, ' FROM ');
1584 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ') > 0
1585 THEN
1586 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ');
1587 ELSIF instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10)) > 0
1588 THEN
1589 l_from_position := instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10));
1590 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10)) >0
1591 THEN
1592 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10));
1593 END IF;
1594
1595 IF instr(l_sql_string, 'PARTY_ID') = 0
1596 OR instr(l_sql_string, 'PARTY_ID', 1, 1) > l_from_position
1597 THEN
1598 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1599 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_SQL');
1600 FND_MSG_PUB.add;
1601 END IF;
1602 RAISE FND_API.g_exc_error;
1603 RETURN;
1604 END IF;
1605
1606 IF instr(l_sql_string, 'ORDER BY') > 0
1607 OR instr(l_sql_string, 'GROUP BY') > 0
1608 THEN
1609 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1610 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_ORDERBY');
1611 FND_MSG_PUB.add;
1612 END IF;
1613 RAISE FND_API.g_exc_error;
1614 -- x_return_status := FND_API.g_ret_sts_error;
1615 RETURN;
1616 END IF;
1617
1618 ----------------------- Insert ---------------------------
1619 AMS_Utility_PVT.debug_message(l_full_name ||': Create_List_Query');
1620
1621 l_list_query_rec.name := p_cell_name;
1622 l_list_query_rec.type := p_cell_code;
1623 l_list_query_rec.sql_string := p_sql_string;
1624 l_list_query_rec.source_object_name := p_source_object_name;
1625 l_list_query_rec.primary_key := 'PARTY_ID';
1626 l_list_query_rec.act_list_query_used_by_id := p_cell_id;
1627 l_list_query_rec.arc_act_list_query_used_by := 'CELL';
1628
1629 AMS_List_Query_PVT.Create_List_Query(
1630 p_api_version_number => l_api_version,
1631 p_init_msg_list => p_init_msg_list,
1632 p_commit => p_commit,
1633 p_validation_level => p_validation_level,
1634 x_return_status => l_return_status,
1635 x_msg_count => x_msg_count,
1636 x_msg_data => x_msg_data,
1637 p_list_query_rec => l_list_query_rec,
1638 x_list_query_id => l_list_query_id
1639 );
1640 IF l_return_status = FND_API.g_ret_sts_error THEN
1641 RAISE FND_API.g_exc_error;
1642 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1643 RAISE FND_API.g_exc_unexpected_error;
1644 END IF;
1645
1646 ------------------------- finish -------------------------------
1647
1648 IF FND_API.to_boolean(p_commit) THEN
1649 COMMIT;
1650 END IF;
1651
1652 FND_MSG_PUB.count_and_get(
1653 p_encoded => FND_API.g_false,
1654 p_count => x_msg_count,
1655 p_data => x_msg_data
1656 );
1657
1658 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1659
1660 EXCEPTION
1661
1662 WHEN FND_API.g_exc_error THEN
1663 ROLLBACK TO add_sel_sql;
1664 x_return_status := FND_API.g_ret_sts_error;
1665 FND_MSG_PUB.count_and_get(
1666 p_encoded => FND_API.g_false,
1667 p_count => x_msg_count,
1668 p_data => x_msg_data
1669 );
1670
1671 WHEN FND_API.g_exc_unexpected_error THEN
1672 ROLLBACK TO add_sel_sql;
1673 x_return_status := FND_API.g_ret_sts_unexp_error ;
1674 FND_MSG_PUB.count_and_get(
1675 p_encoded => FND_API.g_false,
1676 p_count => x_msg_count,
1677 p_data => x_msg_data
1678 );
1682 ROLLBACK TO add_sel_sql;
1679
1680
1681 WHEN OTHERS THEN
1683 x_return_status := FND_API.g_ret_sts_unexp_error ;
1684
1685 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1686 THEN
1687 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1688 END IF;
1689
1690 FND_MSG_PUB.count_and_get(
1691 p_encoded => FND_API.g_false,
1692 p_count => x_msg_count,
1693 p_data => x_msg_data
1694 );
1695
1696 END add_sel_sql;
1697
1698
1699 ---------------------------------------------------------------------
1700 -- PROCEDURE
1701 -- get_single_sql
1702 --
1703 -- HISTORY
1704 -- 01/17/01 yxliu Created.
1705 ---------------------------------------------------------------------
1706 PROCEDURE get_single_sql(
1707 p_api_version IN NUMBER,
1708 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1709 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1710
1711 x_return_status OUT NOCOPY VARCHAR2,
1712 x_msg_count OUT NOCOPY NUMBER,
1713 x_msg_data OUT NOCOPY VARCHAR2,
1714
1715 p_cell_id IN NUMBER,
1716 x_sql_string OUT NOCOPY VARCHAR2
1717 )
1718 IS
1719
1720 l_api_version CONSTANT NUMBER := 1.0;
1721 l_api_name CONSTANT VARCHAR2(30) := 'get_single_sql';
1722 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1723
1724 l_return_status VARCHAR2(1);
1725
1726 l_cell_id NUMBER := p_cell_id;
1727 l_sel_type VARCHAR2(30);
1728 l_sql_string VARCHAR2(20000) := '';
1729
1730 CURSOR c_act_discoverer (p_cell_id IN NUMBER) IS
1731 SELECT workbook_name, worksheet_name, workbook_owner
1732 FROM ams_act_discoverer_all
1733 WHERE arc_act_discoverer_used_by = 'CELL'
1734 AND act_discoverer_used_by_id = p_cell_id;
1735
1736 l_act_discoverer_rec c_act_discoverer%ROWTYPE;
1737
1738 CURSOR c_discoverer_sql (p_workbook_name IN VARCHAR2,
1739 p_worksheet_name IN VARCHAR2,
1740 p_workbook_owner_name IN VARCHAR2) IS
1741 SELECT sql_string, sequence_order
1742 FROM ams_discoverer_sql
1743 WHERE workbook_name = p_workbook_name
1744 AND worksheet_name = p_worksheet_name
1745 AND workbook_owner_name = p_workbook_owner_name
1746 ORDER BY sequence_order;
1747
1748 l_discoverer_sql_rec c_discoverer_sql%ROWTYPE;
1749
1750 BEGIN
1751
1752 --------------------- initialize -----------------------
1753
1754 AMS_Utility_PVT.debug_message(l_full_name||': start');
1755
1756 IF FND_API.to_boolean(p_init_msg_list) THEN
1757 FND_MSG_PUB.initialize;
1758 END IF;
1759
1760 IF NOT FND_API.compatible_api_call(
1761 l_api_version,
1762 p_api_version,
1763 l_api_name,
1764 g_pkg_name
1765 ) THEN
1766 RAISE FND_API.g_exc_unexpected_error;
1767 END IF;
1768
1769 x_return_status := FND_API.g_ret_sts_success;
1770 x_sql_string := '';
1771
1772 ---------------------- get sel type --------------------
1773 AMS_Utility_PVT.debug_message(l_full_name ||': get sel type');
1774
1775 SELECT sel_type into l_sel_type
1776 FROM ams_cells_all_b
1777 WHERE cell_id = l_cell_id;
1778
1779 ------------------- get sql string ---------------------
1780 AMS_Utility_PVT.debug_message(l_full_name ||': get sql string');
1781
1782 IF upper(l_sel_type) = 'DIWB' THEN
1783 AMS_Utility_PVT.debug_message(l_full_name ||': get workbook');
1784 OPEN c_act_discoverer (l_cell_id);
1785 FETCH c_act_discoverer INTO l_act_discoverer_rec;
1786 CLOSE c_act_discoverer;
1787
1788 AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1789 OPEN c_discoverer_sql (l_act_discoverer_rec.workbook_name,
1790 l_act_discoverer_rec.worksheet_name,
1791 l_act_discoverer_rec.workbook_owner);
1792 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1793 WHILE c_discoverer_sql%FOUND
1794 LOOP
1795 l_sql_string := l_sql_string || l_discoverer_sql_rec.sql_string;
1796 --dbms_output.put_line('sequence_order is ' ||
1797 -- l_discoverer_sql_rec.sequence_order);
1798 --dbms_output.put('sql string is: ' || l_sql_string);
1799 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
1800 END LOOP;
1801 CLOSE c_discoverer_sql;
1802
1803 ELSIF upper(l_sel_type) = 'SQL' THEN
1804 AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
1805 SELECT query INTO l_sql_string
1806 FROM ams_list_queries_all
1807 WHERE upper(arc_act_list_query_used_by) = 'CELL'
1808 AND act_list_query_used_by_id = l_cell_id;
1809 END IF;
1810
1811 ------------------------- finish -------------------------------
1812 x_sql_string := l_sql_string;
1813
1814 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1815
1816 EXCEPTION
1817
1818 WHEN NO_DATA_FOUND THEN
1819 x_return_status := FND_API.g_ret_sts_unexp_error ;
1820
1821 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1822 THEN
1823 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1824 END IF;
1825
1826 FND_MSG_PUB.count_and_get(
1830 );
1827 p_encoded => FND_API.g_false,
1828 p_count => x_msg_count,
1829 p_data => x_msg_data
1831
1832 WHEN FND_API.g_exc_error THEN
1833 x_return_status := FND_API.g_ret_sts_error;
1834 FND_MSG_PUB.count_and_get(
1835 p_encoded => FND_API.g_false,
1836 p_count => x_msg_count,
1837 p_data => x_msg_data
1838 );
1839
1840 WHEN FND_API.g_exc_unexpected_error THEN
1841 x_return_status := FND_API.g_ret_sts_unexp_error ;
1842 FND_MSG_PUB.count_and_get(
1843 p_encoded => FND_API.g_false,
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847
1848 END get_single_sql;
1849
1850 ---------------------------------------------------------------------
1851 -- PROCEDURE
1852 -- get_comp_sql
1853 --
1854 -- HISTORY
1855 -- 01/18/01 yxliu Created.
1856 ---------------------------------------------------------------------
1857 PROCEDURE get_comp_sql(
1858 p_api_version IN NUMBER,
1859 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1860 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1861
1862 x_return_status OUT NOCOPY VARCHAR2,
1863 x_msg_count OUT NOCOPY NUMBER,
1864 x_msg_data OUT NOCOPY VARCHAR2,
1865
1866 p_cell_id IN NUMBER,
1867 p_party_id_only IN VARCHAR2 := FND_API.g_false,
1868 x_sql_tbl OUT NOCOPY DBMS_SQL.VARCHAR2S
1869 )
1870 IS
1871
1872 l_api_version CONSTANT NUMBER := 1.0;
1873 l_api_name CONSTANT VARCHAR2(30) := 'get_comp_sql';
1874 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1875
1876 l_return_status VARCHAR2(1);
1877
1878 l_cell_id NUMBER := p_cell_id;
1879 l_parent_cell_id NUMBER;
1880 l_temp_cell_id NUMBER;
1881 l_sql_string VARCHAR2(32767) := '';
1882 l_sql_string1 VARCHAR2(32767) := '';
1883
1884 l_parent_sql_string VARCHAR2(32767) := '';
1885 l_parent_sql_string1 VARCHAR2(32767) := '';
1886
1887 l_count NUMBER;
1888 l_length NUMBER;
1889 l_string_copy VARCHAR2(32767);
1890 l_sql_cur NUMBER;
1891
1892 l_party_id_string VARCHAR2(32767) := '';
1893
1894 BEGIN
1895
1896 --------------------- initialize -----------------------
1897
1898 AMS_Utility_PVT.debug_message(l_full_name||': start');
1899
1900 IF FND_API.to_boolean(p_init_msg_list) THEN
1901 FND_MSG_PUB.initialize;
1902 END IF;
1903
1904 IF NOT FND_API.compatible_api_call(
1905 l_api_version,
1906 p_api_version,
1907 l_api_name,
1908 g_pkg_name
1909 ) THEN
1910 RAISE FND_API.g_exc_unexpected_error;
1911 END IF;
1912
1913 x_return_status := FND_API.g_ret_sts_success;
1914 x_sql_tbl(1) := '';
1915
1916 ---------- get sql string for current cell ------------
1917 AMS_Utility_PVT.debug_message(l_full_name ||': get sql string for current cell');
1918 get_single_sql(
1919 p_api_version => l_api_version,
1920 p_init_msg_list => p_init_msg_list,
1921 p_validation_level => p_validation_level,
1922 x_return_status => l_return_status,
1923 x_msg_count => x_msg_count,
1924 x_msg_data => x_msg_data,
1925 p_cell_id => l_cell_id,
1926 x_sql_string => l_sql_string
1927 );
1928 IF l_return_status = FND_API.g_ret_sts_error THEN
1929 RAISE FND_API.g_exc_error;
1930 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1931 RAISE FND_API.g_exc_unexpected_error;
1932 END IF;
1933
1934
1935 -- if we only need party_id column for the current cell sql
1936 IF FND_API.to_boolean(p_party_id_only) THEN
1937 l_sql_string1 := l_sql_string;
1938 format_sql_string(l_sql_string1, l_sql_string,l_party_id_string);
1939 END IF;
1940
1941 AMS_Utility_PVT.debug_message(l_full_name ||'- l_sql_string from formatSql:'||l_sql_string);
1942 AMS_Utility_PVT.debug_message(l_full_name ||'- l_party_id_string from formatSql:'||l_party_id_string);
1943
1944 ---------- put sql string into sql table ------------
1945 AMS_Utility_PVT.debug_message(l_full_name ||': put sql string into sql table');
1946
1947 l_count := 0;
1948 l_string_copy := l_sql_string;
1949 l_length := length(l_string_copy);
1950
1951 LOOP
1952 l_count := l_count + 1;
1953 IF l_length < 255 THEN
1954 x_sql_tbl(l_count) := l_string_copy;
1955 EXIT;
1956 ELSE
1957 x_sql_tbl(l_count) := substr(l_string_copy, 1, 255);
1958 l_string_copy := substr(l_string_copy, 256);
1959 END IF;
1960 l_length := length(l_string_copy);
1961 END LOOP;
1962
1963 ---------- get sql string for parent cell -------------
1964 SELECT parent_cell_id INTO l_parent_cell_id
1965 FROM ams_cells_all_b
1966 WHERE cell_id = l_cell_id;
1967
1968 WHILE l_parent_cell_id is not NULL
1969 LOOP
1970
1971 AMS_Utility_PVT.debug_message(l_full_name ||': get sql string for parent cell');
1972 get_single_sql(
1973 p_api_version => l_api_version,
1974 p_init_msg_list => p_init_msg_list,
1975 p_validation_level => p_validation_level,
1976 x_return_status => l_return_status,
1977 x_msg_count => x_msg_count,
1978 x_msg_data => x_msg_data,
1979 p_cell_id => l_parent_cell_id,
1983 AMS_Utility_PVT.debug_message(l_full_name ||': return status from get single sql'|| x_return_status);
1980 x_sql_string => l_parent_sql_string
1981 );
1982
1984
1985 IF x_return_status = FND_API.g_ret_sts_error THEN
1986 RAISE FND_API.g_exc_error;
1987 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1988 RAISE FND_API.g_exc_unexpected_error;
1989 END IF;
1990
1991 -- manipulate parent cell's sql string
1992 l_parent_sql_string1 := l_parent_sql_string;
1993 format_sql_string(l_parent_sql_string1, l_parent_sql_string);
1994 IF instr(upper(l_sql_string), 'WHERE') > 0 THEN
1995 --l_parent_sql_string := ' AND PARTY_ID IN (' || l_parent_sql_string || ')';
1996 l_parent_sql_string := ' AND '|| l_party_id_string ||' IN (' || l_parent_sql_string || ')';
1997 ELSE
1998 l_parent_sql_string := ' WHERE PARTY_ID IN (' || l_parent_sql_string || ')';
1999 END IF;
2000
2001 AMS_Utility_PVT.debug_message(l_full_name || ':parent sql string ' || l_parent_sql_string);
2002
2003 -- put parent cell's sql into sql table
2004 l_string_copy := l_parent_sql_string;
2005 l_length := length(l_string_copy);
2006
2007 LOOP
2008 l_count := l_count + 1;
2009 IF l_length < 255 THEN
2010 x_sql_tbl(l_count) := l_string_copy;
2011 EXIT;
2012 ELSE
2013 x_sql_tbl(l_count) := substr(l_string_copy, 1, 255);
2014 l_string_copy := substr(l_string_copy, 256);
2015 END IF;
2016 l_length := length(l_string_copy);
2017 END LOOP;
2018
2019 -- keep going
2020 l_temp_cell_id := l_parent_cell_id;
2021 SELECT parent_cell_id INTO l_parent_cell_id
2022 FROM ams_cells_all_b
2023 WHERE cell_id = l_temp_cell_id;
2024 END LOOP;
2025
2026 ------------------- Parse the result sql ----------------------
2027 AMS_Utility_PVT.debug_message(l_full_name || ': parse the result sql');
2028
2029 l_count := x_sql_tbl.first;
2030 if (l_count is not null) then
2031 loop
2032 AMS_Utility_PVT.debug_message(x_sql_tbl(l_count));
2033 l_count := x_sql_tbl.next(l_count);
2034 exit when (l_count is null);
2035 end loop;
2036 end if;
2037
2038
2039
2040 IF (DBMS_SQL.IS_Open(l_sql_cur) = FALSE ) THEN
2041 l_sql_cur := DBMS_SQL.Open_Cursor;
2042 END IF;
2043 DBMS_SQL.Parse(l_sql_cur,
2044 x_sql_tbl,
2045 x_sql_tbl.first,
2046 x_sql_tbl.last,
2047 FALSE,
2048 DBMS_SQL.Native);
2049
2050
2051 ------------------------- finish -------------------------------
2052 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2053
2054 EXCEPTION
2055
2056 WHEN NO_DATA_FOUND THEN
2057 AMS_Utility_PVT.debug_message(l_full_name || ': No Data Found error in get composite sql for cell ' || l_cell_id);
2058 AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2059 x_return_status := FND_API.g_ret_sts_unexp_error ;
2060
2061 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2062 THEN
2063 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2064 END IF;
2065
2066 FND_MSG_PUB.count_and_get(
2067 p_encoded => FND_API.g_false,
2068 p_count => x_msg_count,
2069 p_data => x_msg_data
2070 );
2071
2072 WHEN FND_API.g_exc_error THEN
2073 AMS_Utility_Pvt.Debug_Message(l_full_name ||': Expected error in get composite sql for cell '||l_cell_id);
2074 AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2075
2076 x_return_status := FND_API.g_ret_sts_error;
2077 FND_MSG_PUB.count_and_get(
2078 p_encoded => FND_API.g_false,
2079 p_count => x_msg_count,
2080 p_data => x_msg_data
2081 );
2082
2083 WHEN FND_API.g_exc_unexpected_error THEN
2084 AMS_Utility_Pvt.Debug_Message(l_full_name || ': Unexpected error in get composite sql for cell ' || l_cell_id);
2085 AMS_Utility_Pvt.Debug_Message(l_full_name || ': Please check if the workbook or sql statement is valid');
2086 x_return_status := FND_API.g_ret_sts_unexp_error ;
2087 FND_MSG_PUB.count_and_get(
2088 p_encoded => FND_API.g_false,
2089 p_count => x_msg_count,
2090 p_data => x_msg_data
2091 );
2092
2093 WHEN OTHERS THEN
2094 AMS_Utility_Pvt.Debug_Message(l_full_name ||': Error in get composite sql for cell ' || l_cell_id);
2095 AMS_Utility_Pvt.Debug_Message(l_full_name ||': Please check if the workbook or sql statement is valid');
2096 x_return_status := FND_API.g_ret_sts_unexp_error ;
2097 FND_MSG_PUB.count_and_get(
2098 p_encoded => FND_API.g_false,
2099 p_count => x_msg_count,
2100 p_data => x_msg_data
2101 );
2102
2103
2104
2105 END get_comp_sql;
2106
2107 ---------------------------------------------------------------------
2108 -- PROCEDURE
2109 -- format_sql_string
2110 --
2111 -- HISTORY
2112 -- 01/18/01 yxliu Created.
2113 -- 07/05/01 yxliu Modified, remove white space and ; from string end
2114 -- 10/18/01 yxliu Modified. remove upper the original string
2115 ---------------------------------------------------------------------
2116
2117 procedure format_sql_string
2118 ( p_string IN VARCHAR2,
2119 x_string OUT NOCOPY VARCHAR2
2120
2121 ) IS
2122 l_party_id_string VARCHAR2(32767);
2123
2124 BEGIN
2125
2129
2126 format_sql_string (p_string,x_string,l_party_id_string);
2127
2128 END;
2130
2131 procedure format_sql_string
2132 ( p_string IN VARCHAR2,
2133 x_string OUT NOCOPY VARCHAR2,
2134 x_party_id_string OUT NOCOPY VARCHAR2
2135 )
2136
2137 IS
2138 l_string VARCHAR2(32767) := p_string;
2139 l_tmp_string VARCHAR2(32767);
2140 l_pos_party_id NUMBER;
2141 l_pos_comma NUMBER;
2142 l_pos_space NUMBER;
2143 l_party_id_str VARCHAR2 (100);
2144 l_api_name CONSTANT VARCHAR2(30) := 'format_sql_string';
2145 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2146
2147 BEGIN
2148
2149 AMS_Utility_PVT.debug_message(l_full_name||': start');
2150 --l_string := upper(l_string);
2151
2152 -- if there are redundant white space at end of the string, remove it
2153 l_string := rtrim(l_string);
2154
2155 -- if there is ";" at the end of the string, remove it.
2156 IF instr(l_string, ';') = length(l_string)
2157 THEN
2158 l_string := substr(l_string, 1, length(l_string) -1);
2159 END IF;
2160
2161 -- if there is no party_id in the select clause, raise exception
2162 IF instr(upper(l_string), 'PARTY_ID') = 0
2163 OR instr(upper(l_string), 'PARTY_ID',1,1) > instr(upper(l_string), 'FROM',1,1)
2164 THEN
2165 RAISE FND_API.g_exc_unexpected_error;
2166
2167 ELSIF instr(upper(l_string), '.PARTY_ID') = 0 THEN
2168 -- simple select ... party_id .... from ...
2169 x_string := concat('SELECT DISTINCT PARTY_ID ',
2170 substr(l_string, instr(upper(l_string), 'FROM')));
2171 AMS_Utility_PVT.debug_message(l_full_name||':x_string:'||x_string);
2172
2173 -- bug:4695424 fix, adding the party_id string for where there is no alias
2174 x_party_id_string := 'PARTY_ID';
2175 ELSE
2176 -- select ... ams_table.party_id ... from ...
2177 -- get select ... ams_table.party_id
2178 l_tmp_string := substr(l_string, 1, instr(upper(l_string), '.PARTY_ID')+8);
2179 -- find the position of comma which is just before ams_table.party_id
2180 l_pos_comma := instr(l_tmp_string, ',', -1, 1);
2181 -- find the position of space which is just before ams_table.party_id
2182 l_pos_space := instr(l_tmp_string, ' ', -1, 1);
2183 IF l_pos_comma < l_pos_space THEN
2184 -- get select ... , ams_table.party_id
2185 -- or select ams_table.party_id ...
2186 l_party_id_str := substr(l_tmp_string, l_pos_space +1);
2187 ELSE
2188 -- get select ... ,ams_table.party_id
2189 l_party_id_str := substr(l_tmp_string, l_pos_comma + 1);
2190 END IF;
2191 AMS_Utility_PVT.debug_message(l_full_name||': l_party_id_str'||l_party_id_str);
2192 x_party_id_string := l_party_id_str;
2193 x_string := 'SELECT DISTINCT ' || l_party_id_str || ' '|| substr(l_string, instr(upper(l_string), 'FROM'));
2194 END IF;
2195
2196 END format_sql_string;
2197
2198 ---------------------------------------------------------------------
2199 -- PROCEDURE
2200 -- get_workbook_sql
2201 --
2202 -- HISTORY
2203 -- 03/01/2001 yxliu Created.
2204 ---------------------------------------------------------------------
2205 PROCEDURE get_workbook_sql(
2206 p_api_version IN NUMBER,
2207 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2208 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2209
2210 x_return_status OUT NOCOPY VARCHAR2,
2211 x_msg_count OUT NOCOPY NUMBER,
2212 x_msg_data OUT NOCOPY VARCHAR2,
2213
2214 p_disc_sql_id IN NUMBER,
2215 x_sql_string OUT NOCOPY VARCHAR2
2216 )
2217 IS
2218
2219 l_api_version CONSTANT NUMBER := 1.0;
2220 l_api_name CONSTANT VARCHAR2(30) := 'get_workbook_sql';
2221 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2222
2223 l_return_status VARCHAR2(1);
2224
2225 l_disc_sql_id NUMBER := p_disc_sql_id;
2226 l_sql_string VARCHAR2(20000) := '';
2227
2228 CURSOR c_discoverer (p_disc_sql_id IN NUMBER) IS
2229 SELECT workbook_name, worksheet_name, workbook_owner_name
2230 FROM ams_discoverer_sql
2231 WHERE discoverer_sql_id = p_disc_sql_id;
2232
2233 l_discoverer_rec c_discoverer%ROWTYPE;
2234
2235 CURSOR c_discoverer_sql (p_workbook_name IN VARCHAR2,
2236 p_worksheet_name IN VARCHAR2,
2237 p_workbook_owner_name IN VARCHAR2) IS
2238 SELECT sql_string, sequence_order
2239 FROM ams_discoverer_sql
2240 WHERE workbook_name = p_workbook_name
2241 AND worksheet_name = p_worksheet_name
2242 AND workbook_owner_name = p_workbook_owner_name
2243 ORDER BY sequence_order;
2244
2245 l_discoverer_sql_rec c_discoverer_sql%ROWTYPE;
2246
2247 BEGIN
2248
2249 --------------------- initialize -----------------------
2250
2251 AMS_Utility_PVT.debug_message(l_full_name||': start');
2252
2253 IF FND_API.to_boolean(p_init_msg_list) THEN
2254 FND_MSG_PUB.initialize;
2255 END IF;
2256
2257 IF NOT FND_API.compatible_api_call(
2258 l_api_version,
2259 p_api_version,
2260 l_api_name,
2261 g_pkg_name
2262 ) THEN
2263 RAISE FND_API.g_exc_unexpected_error;
2264 END IF;
2265
2266 x_return_status := FND_API.g_ret_sts_success;
2267 x_sql_string := '';
2268
2269 ------------------- get sql string ---------------------
2270
2271 AMS_Utility_PVT.debug_message(l_full_name ||': get workbook');
2272 OPEN c_discoverer (l_disc_sql_id);
2273 FETCH c_discoverer INTO l_discoverer_rec;
2274 CLOSE c_discoverer;
2275
2279 l_discoverer_rec.workbook_owner_name);
2276 AMS_Utility_PVT.debug_message(l_full_name ||': get SQL string');
2277 OPEN c_discoverer_sql (l_discoverer_rec.workbook_name,
2278 l_discoverer_rec.worksheet_name,
2280 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
2281
2282 WHILE c_discoverer_sql%FOUND
2283 LOOP
2284 l_sql_string := l_sql_string || l_discoverer_sql_rec.sql_string;
2285 FETCH c_discoverer_sql INTO l_discoverer_sql_rec;
2286 END LOOP;
2287 CLOSE c_discoverer_sql;
2288
2289 ------------------------- finish -------------------------------
2290 x_sql_string := l_sql_string;
2291
2292 FND_MSG_PUB.count_and_get(
2293 p_encoded => FND_API.g_false,
2294 p_count => x_msg_count,
2295 p_data => x_msg_data
2296 );
2297
2298 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2299
2300 EXCEPTION
2301
2302 WHEN NO_DATA_FOUND THEN
2303 x_return_status := FND_API.g_ret_sts_unexp_error ;
2304
2305 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2306 THEN
2307 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2308 END IF;
2309
2310 FND_MSG_PUB.count_and_get(
2311 p_encoded => FND_API.g_false,
2312 p_count => x_msg_count,
2313 p_data => x_msg_data
2314 );
2315
2316 WHEN FND_API.g_exc_error THEN
2317 x_return_status := FND_API.g_ret_sts_error;
2318 FND_MSG_PUB.count_and_get(
2319 p_encoded => FND_API.g_false,
2320 p_count => x_msg_count,
2321 p_data => x_msg_data
2322 );
2323
2324 WHEN FND_API.g_exc_unexpected_error THEN
2325 x_return_status := FND_API.g_ret_sts_unexp_error ;
2326 FND_MSG_PUB.count_and_get(
2327 p_encoded => FND_API.g_false,
2328 p_count => x_msg_count,
2329 p_data => x_msg_data
2330 );
2331
2332 END get_workbook_sql;
2333
2334 ---------------------------------------------------------------------
2335 -- PROCEDURE
2336 -- get_segment_size
2337 --
2338 -- DESCRIPTION
2339 -- Dynamically execute the input sql_string to get segment size
2340 --
2341 -- HISTORY
2342 -- 03/01/2001 yxliu Created.
2343 ---------------------------------------------------------------------
2344 PROCEDURE get_segment_size(
2345 p_api_version IN NUMBER,
2346 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2347 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2348
2349 x_return_status OUT NOCOPY VARCHAR2,
2350 x_msg_count OUT NOCOPY NUMBER,
2351 x_msg_data OUT NOCOPY VARCHAR2,
2352
2353 p_sql_string IN VARCHAR2,
2354 x_segment_size OUT NOCOPY NUMBER
2355 )
2356 IS
2357
2358 l_api_version CONSTANT NUMBER := 1.0;
2359 l_api_name CONSTANT VARCHAR2(30) := 'get_segment_size';
2360 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2361
2362 l_return_status VARCHAR2(1);
2363
2364 l_sql_string VARCHAR2(32767) := p_sql_string;
2365
2366 l_party_cur NUMBER;
2367 l_dummy NUMBER;
2368 l_size NUMBER;
2369
2370 BEGIN
2371
2372 --------------------- initialize -----------------------
2373
2374 AMS_Utility_PVT.debug_message(l_full_name||': start');
2375
2376 IF FND_API.to_boolean(p_init_msg_list) THEN
2377 FND_MSG_PUB.initialize;
2378 END IF;
2379
2380 IF NOT FND_API.compatible_api_call(
2381 l_api_version,
2382 p_api_version,
2383 l_api_name,
2384 g_pkg_name
2385 ) THEN
2386 RAISE FND_API.g_exc_unexpected_error;
2387 END IF;
2388
2389 x_return_status := FND_API.g_ret_sts_success;
2390
2391 ------------------ Validate sql string ----------------------
2392 AMS_Utility_PVT.debug_message(l_full_name||': validate sql string');
2393
2394 l_sql_string := upper(l_sql_string);
2395 IF instr(l_sql_string, 'PARTY_ID') = 0
2396 OR instr(l_sql_string, 'PARTY_ID', 1, 1) > instr(l_sql_string, 'FROM', 1, 1)
2397 THEN
2398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2399 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_SQL');
2400 FND_MSG_PUB.add;
2401 END IF;
2402 RAISE FND_API.g_exc_error;
2403 -- x_return_status := FND_API.g_ret_sts_error;
2404 RETURN;
2405 END IF;
2406
2407 ------------------- get segment size ---------------------
2408
2409 AMS_Utility_PVT.debug_message(l_full_name ||': get segment size');
2410
2411 l_party_cur := DBMS_SQL.OPEN_CURSOR;
2412 l_sql_string := 'SELECT COUNT(*) FROM (' || l_sql_string || ')';
2413
2414 DBMS_SQL.PARSE(l_party_cur, l_sql_string, DBMS_SQL.Native);
2415 DBMS_SQL.DEFINE_COLUMN(l_party_cur, 1, l_size);
2416 l_dummy := DBMS_SQL.EXECUTE(l_party_cur);
2417 LOOP
2418 IF DBMS_SQL.FETCH_ROWS(l_party_cur) > 0 then
2419 DBMS_SQL.COLUMN_VALUE(l_party_cur, 1, l_size);
2420 x_segment_size := l_size;
2421 ELSE
2422 EXIT;
2423 END IF;
2424 END LOOP;
2425
2426 DBMS_SQL.CLOSE_CURSOR(l_party_cur);
2427
2428 ------------------------- finish -------------------------------
2429
2430 FND_MSG_PUB.count_and_get(
2431 p_encoded => FND_API.g_false,
2432 p_count => x_msg_count,
2433 p_data => x_msg_data
2434 );
2435
2436 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2440 WHEN NO_DATA_FOUND THEN
2437
2438 EXCEPTION
2439
2441 x_return_status := FND_API.g_ret_sts_unexp_error ;
2442
2443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2444 THEN
2445 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2446 END IF;
2447
2448 FND_MSG_PUB.count_and_get(
2449 p_encoded => FND_API.g_false,
2450 p_count => x_msg_count,
2451 p_data => x_msg_data
2452 );
2453
2454 WHEN FND_API.g_exc_error THEN
2455 x_return_status := FND_API.g_ret_sts_error;
2456 FND_MSG_PUB.count_and_get(
2457 p_encoded => FND_API.g_false,
2458 p_count => x_msg_count,
2459 p_data => x_msg_data
2460 );
2461
2462 WHEN FND_API.g_exc_unexpected_error THEN
2463 x_return_status := FND_API.g_ret_sts_unexp_error ;
2464 FND_MSG_PUB.count_and_get(
2465 p_encoded => FND_API.g_false,
2466 p_count => x_msg_count,
2467 p_data => x_msg_data
2468 );
2469
2470 WHEN OTHERS THEN
2471 x_return_status := FND_API.g_ret_sts_unexp_error ;
2472
2473 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2474 THEN
2475 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2476 END IF;
2477
2478 FND_MSG_PUB.count_and_get(
2479 p_encoded => FND_API.g_false,
2480 p_count => x_msg_count,
2481 p_data => x_msg_data
2482 );
2483 END get_segment_size;
2484
2485
2486 ---------------------------------------------------------------------
2487 -- PROCEDURE
2488 -- get_comp_segment_size
2489 --
2490 -- DESCRIPTION
2491 -- For input cell_id, get the composite sql string associated to it
2492 -- Then dynamically execute the comp sql to get segment size
2493 --
2494 -- HISTORY
2495 -- 04/16/2001 yxliu Created. using the get_comp_sql to get the segment
2496 -- size which means segment and all its ancestors'
2497 -- criteria.
2498 -- 08/28/2001 yxliu Modified. Use count(*) instead of walking through
2499 -- returned records to get count
2500 ---------------------------------------------------------------------
2501 PROCEDURE get_comp_segment_size(
2502 p_api_version IN NUMBER,
2503 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2504 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2505
2506 x_return_status OUT NOCOPY VARCHAR2,
2507 x_msg_count OUT NOCOPY NUMBER,
2508 x_msg_data OUT NOCOPY VARCHAR2,
2509
2510 p_cell_id IN NUMBER,
2511 x_segment_size OUT NOCOPY NUMBER
2512 )
2513 IS
2514
2515 l_api_version CONSTANT NUMBER := 1.0;
2516 l_api_name CONSTANT VARCHAR2(30) := 'get_comp_segment_size';
2517 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2518
2519 l_return_status VARCHAR2(1);
2520
2521 l_cell_id NUMBER := p_cell_id;
2522 l_sql_tbl DBMS_SQL.varchar2s ;
2523 l_sql_tbl_new DBMS_SQL.varchar2s;
2524 l_segment_size NUMBER;
2525
2526 l_temp NUMBER ;
2527 l_party_cur NUMBER ;
2528 l_dummy NUMBER ;
2529 l_count NUMBER ;
2530
2531 BEGIN
2532
2533 --------------------- initialize -----------------------
2534
2535 AMS_Utility_PVT.debug_message(l_full_name||': start');
2536
2537 IF FND_API.to_boolean(p_init_msg_list) THEN
2538 FND_MSG_PUB.initialize;
2539 END IF;
2540
2541 IF NOT FND_API.compatible_api_call(
2542 l_api_version,
2543 p_api_version,
2544 l_api_name,
2545 g_pkg_name
2546 ) THEN
2547 RAISE FND_API.g_exc_unexpected_error;
2548 END IF;
2549
2550 x_return_status := FND_API.g_ret_sts_success;
2551
2552 ------------------ Get comp sql string ----------------------
2553 AMS_Utility_PVT.debug_message(l_full_name||': get comp sql string');
2554 AMS_CELL_PVT.get_comp_sql(
2555 p_api_version => 1,
2556 p_init_msg_list => NULL,
2557 p_validation_level => NULL,
2558 x_return_status => x_return_status,
2559 x_msg_count => x_msg_count,
2560 x_msg_data => x_msg_data,
2561 p_cell_id => l_cell_id,
2562 p_party_id_only => FND_API.g_true,
2563 x_sql_tbl => l_sql_tbl
2564 );
2565
2566 IF x_return_status = FND_API.g_ret_sts_error THEN
2567 RAISE FND_API.g_exc_error;
2568 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2569 RAISE FND_API.g_exc_unexpected_error;
2570 END IF;
2571
2572
2573 ------------------- get segment size ---------------------
2574 AMS_Utility_PVT.debug_message(l_full_name ||': get segment size');
2575 AMS_Utility_PVT.debug_message(l_full_name ||': construct new sql');
2576
2577 l_count := 1 ;
2578 l_sql_tbl_new(l_count) := 'select count(*) from ( ';
2579 for i in l_sql_tbl.first .. l_sql_tbl.last
2580 loop
2581 l_count := l_count + 1;
2582 l_sql_tbl_new(l_count) := l_sql_tbl(i);
2583 end loop;
2584 l_sql_tbl_new(l_count+1) := ' ) ';
2585
2586 -- Open the cursor and parse it
2587 AMS_Utility_PVT.Debug_Message(l_api_name||': Parse the new sql ');
2588 IF (DBMS_SQL.Is_Open(l_party_cur) = FALSE) THEN
2589 l_party_cur := DBMS_SQL.Open_Cursor ;
2590 END IF;
2591 DBMS_SQL.Parse(l_party_cur ,
2592 l_sql_tbl_new,
2593 l_sql_tbl_new.first,
2597
2594 l_sql_tbl_new.last,
2595 FALSE,
2596 DBMS_SQL.Native) ;
2598 -- l_dummy := DBMS_SQL.Execute(l_party_cur);
2599 DBMS_SQL.DEFINE_COLUMN(l_party_cur,1,l_temp);
2600 l_dummy := DBMS_SQL.Execute(l_party_cur);
2601 AMS_Utility_PVT.Debug_Message(l_api_name||': Executed the new sql ');
2602
2603 LOOP
2604 IF DBMS_SQL.FETCH_ROWS(l_party_cur)>0 THEN
2605 -- get column values of the row
2606 DBMS_SQL.COLUMN_VALUE(l_party_cur,1, l_temp);
2607 ELSE
2608 EXIT;
2609 END IF;
2610 END LOOP;
2611 l_segment_size := l_temp;
2612 -- dbms_output.put_line('l_segment_size: ' || l_segment_size);
2613
2614 DBMS_SQL.CLOSE_CURSOR(l_party_cur);
2615
2616 ------------------------- finish -------------------------------
2617 -- x_segment_size := l_count;
2618 x_segment_size := l_segment_size;
2619
2620 AMS_Utility_PVT.debug_message(l_full_name ||': x_segment_size:'||x_segment_size);
2621
2622 FND_MSG_PUB.count_and_get(
2623 p_encoded => FND_API.g_false,
2624 p_count => x_msg_count,
2625 p_data => x_msg_data
2626 );
2627
2628 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2629
2630 EXCEPTION
2631
2632 WHEN FND_API.g_exc_error THEN
2633 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
2634 DBMS_SQL.Close_Cursor(l_party_cur) ;
2635 END IF;
2636 x_return_status := FND_API.g_ret_sts_error;
2637 FND_MSG_PUB.count_and_get(
2638 p_encoded => FND_API.g_false,
2639 p_count => x_msg_count,
2640 p_data => x_msg_data
2641 );
2642
2643 WHEN FND_API.g_exc_unexpected_error THEN
2644 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
2645 DBMS_SQL.Close_Cursor(l_party_cur) ;
2646 END IF;
2647 x_return_status := FND_API.g_ret_sts_unexp_error ;
2648 FND_MSG_PUB.count_and_get(
2649 p_encoded => FND_API.g_false,
2650 p_count => x_msg_count,
2651 p_data => x_msg_data
2652 );
2653
2654 WHEN OTHERS THEN
2655 IF (DBMS_SQL.Is_Open(l_party_cur) = TRUE) THEN
2656 DBMS_SQL.Close_Cursor(l_party_cur) ;
2657 END IF;
2658 x_return_status := FND_API.g_ret_sts_unexp_error ;
2659
2660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2661 THEN
2662 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2663 END IF;
2664
2665 FND_MSG_PUB.count_and_get(
2666 p_encoded => FND_API.g_false,
2667 p_count => x_msg_count,
2668 p_data => x_msg_data
2669 );
2670 END get_comp_segment_size;
2671
2672 ---------------------------------------------------------------------
2673 -- PROCEDURE
2674 -- create_sql_cell
2675 --
2676 -- HISTORY
2677 -- 03/01/01 yxliu created, create a segment and add entries into
2678 -- corresponding mapping tables
2679 ---------------------------------------------------------------------
2680 PROCEDURE create_sql_cell(
2681 p_api_version IN NUMBER,
2682 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2683 p_commit IN VARCHAR2 := FND_API.g_false,
2684 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2685
2686 x_return_status OUT NOCOPY VARCHAR2,
2687 x_msg_count OUT NOCOPY NUMBER,
2688 x_msg_data OUT NOCOPY VARCHAR2,
2689
2690 p_sql_cell_rec IN sqlcell_rec_type,
2691 x_cell_id OUT NOCOPY NUMBER
2692 )
2693 IS
2694
2695 l_api_version CONSTANT NUMBER := 1.0;
2696 l_api_name CONSTANT VARCHAR2(30) := 'create_sql_cell';
2697 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2698
2699 l_return_status VARCHAR2(1);
2700 l_sql_cell_rec sqlcell_rec_type := p_sql_cell_rec;
2701 l_cell_rec cell_rec_type;
2702
2703 BEGIN
2704
2705 --------------------- initialize -----------------------
2706 SAVEPOINT create_sql_cell;
2707
2708 AMS_Utility_PVT.debug_message(l_full_name||': start');
2709
2710 IF FND_API.to_boolean(p_init_msg_list) THEN
2711 FND_MSG_PUB.initialize;
2712 END IF;
2713
2714 IF NOT FND_API.compatible_api_call(
2715 l_api_version,
2716 p_api_version,
2717 l_api_name,
2718 g_pkg_name
2719 ) THEN
2720 RAISE FND_API.g_exc_unexpected_error;
2721 END IF;
2722
2723 x_return_status := FND_API.g_ret_sts_success;
2724
2725 ------------------ call create_cell -------------------
2726
2727 --bmuthukr bug 5130570
2728 if trim(l_sql_cell_rec.list_sql_string) is null AND l_sql_cell_rec.sel_type = 'SQL' then
2729 FND_MESSAGE.set_name('AMS', 'AMS_CELL_BLANK_SQL');
2730 FND_MSG_PUB.add;
2731 RAISE FND_API.g_exc_error;
2732 end if;
2733 --
2734
2735 AMS_Utility_PVT.debug_message(l_full_name ||': create_cell');
2736
2737 l_cell_rec.cell_id := l_sql_cell_rec.cell_id;
2738 l_cell_rec.sel_type := l_sql_cell_rec.sel_type;
2739 l_cell_rec.last_update_date := l_sql_cell_rec.last_update_date;
2740 l_cell_rec.last_updated_by := l_sql_cell_rec.last_updated_by;
2741 l_cell_rec.creation_date := l_sql_cell_rec.creation_date;
2742 l_cell_rec.created_by := l_sql_cell_rec.created_by;
2743 l_cell_rec.last_update_login := l_sql_cell_rec.last_update_login;
2744 l_cell_rec.object_version_number := l_sql_cell_rec.object_version_number;
2745 l_cell_rec.cell_code := l_sql_cell_rec.cell_code;
2749 l_cell_rec.org_id := l_sql_cell_rec.org_id;
2746 l_cell_rec.enabled_flag := l_sql_cell_rec.enabled_flag;
2747 l_cell_rec.original_size := l_sql_cell_rec.original_size;
2748 l_cell_rec.parent_cell_id := l_sql_cell_rec.parent_cell_id;
2750 l_cell_rec.owner_id := l_sql_cell_rec.owner_id;
2751 l_cell_rec.cell_name := l_sql_cell_rec.cell_name;
2752 l_cell_rec.description := l_sql_cell_rec.description;
2753 l_cell_rec.status_code := l_sql_cell_rec.status_code;
2754 l_cell_rec.status_date := l_sql_cell_rec.status_date;
2755 l_cell_rec.user_status_id := l_sql_cell_rec.user_status_id;
2756
2757
2758 create_cell(
2759 p_api_version => l_api_version,
2760 p_init_msg_list => p_init_msg_list,
2761 p_validation_level => p_validation_level,
2762 p_commit => FND_API.g_false,
2763 x_return_status => l_return_status,
2764 x_msg_count => x_msg_count,
2765 x_msg_data => x_msg_data,
2766 p_cell_rec => l_cell_rec,
2767 x_cell_id => x_cell_id
2768 );
2769
2770 IF l_return_status = FND_API.g_ret_sts_error THEN
2771 RAISE FND_API.g_exc_error;
2772 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2773 RAISE FND_API.g_exc_unexpected_error;
2774 END IF;
2775
2776 --------------------- add selection ----------------------------
2777 AMS_Utility_PVT.debug_message(l_full_name ||': add selection');
2778 IF 'DIWB' = upper(l_sql_cell_rec.sel_type)
2779 AND l_sql_cell_rec.discoverer_sql_id IS NOT NULL THEN
2780 add_sel_workbook(
2781 p_api_version => l_api_version,
2782 p_init_msg_list => p_init_msg_list,
2783 p_commit => FND_API.g_false,
2784 p_validation_level => p_validation_level,
2785 x_return_status => l_return_status,
2786 x_msg_count => x_msg_count,
2787 x_msg_data => x_msg_data,
2788 p_cell_id => x_cell_id,
2789 p_discoverer_sql_id => l_sql_cell_rec.discoverer_sql_id
2790 );
2791 IF l_return_status = FND_API.g_ret_sts_error THEN
2792 RAISE FND_API.g_exc_error;
2793 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2794 RAISE FND_API.g_exc_unexpected_error;
2795 END IF;
2796 ELSIF 'SQL' = upper(l_sql_cell_rec.sel_type) THEN
2797 add_sel_sql(
2798 p_api_version => l_api_version,
2799 p_init_msg_list => p_init_msg_list,
2800 p_commit => FND_API.g_false,
2801 p_validation_level => p_validation_level,
2802 x_return_status => l_return_status,
2803 x_msg_count => x_msg_count,
2804 x_msg_data => x_msg_data,
2805 p_cell_id => x_cell_id,
2806 p_cell_name => l_sql_cell_rec.cell_name,
2807 p_cell_code => l_sql_cell_rec.cell_code,
2808 p_sql_string => l_sql_cell_rec.list_sql_string,
2809 p_source_object_name => l_sql_cell_rec.source_object_name
2810 );
2811 IF l_return_status = FND_API.g_ret_sts_error THEN
2812 RAISE FND_API.g_exc_error;
2813 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2814 RAISE FND_API.g_exc_unexpected_error;
2815 END IF;
2816 END IF;
2817
2818 ------------------------- finish -------------------------------
2819
2820 IF FND_API.to_boolean(p_commit) THEN
2821 COMMIT;
2822 END IF;
2823
2824 FND_MSG_PUB.count_and_get(
2825 p_encoded => FND_API.g_false,
2826 p_count => x_msg_count,
2827 p_data => x_msg_data
2828 );
2829
2830 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2831
2832 EXCEPTION
2833
2834 WHEN FND_API.g_exc_error THEN
2835 ROLLBACK TO create_sql_cell;
2836 x_return_status := FND_API.g_ret_sts_error;
2837 FND_MSG_PUB.count_and_get(
2838 p_encoded => FND_API.g_false,
2839 p_count => x_msg_count,
2840 p_data => x_msg_data
2841 );
2842 WHEN FND_API.g_exc_unexpected_error THEN
2843 ROLLBACK TO create_sql_cell;
2844 x_return_status := FND_API.g_ret_sts_unexp_error ;
2845 FND_MSG_PUB.count_and_get(
2846 p_encoded => FND_API.g_false,
2847 p_count => x_msg_count,
2848 p_data => x_msg_data
2849 );
2850
2851 WHEN OTHERS THEN
2852 ROLLBACK TO create_sql_cell;
2853 x_return_status := FND_API.g_ret_sts_unexp_error ;
2854
2855 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2856 THEN
2857 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2858 END IF;
2859
2860 FND_MSG_PUB.count_and_get(
2861 p_encoded => FND_API.g_false,
2862 p_count => x_msg_count,
2863 p_data => x_msg_data
2864 );
2865 END create_sql_cell;
2866
2867 ---------------------------------------------------------------------
2868 -- PROCEDURE
2869 -- update_sql_cell
2870 --
2871 -- HISTORY
2872 -- 03/01/01 yxliu created, update the segment and based on the sel_type,
2873 -- update the corresponding mapping tables
2874 -- 04/10/01 yxliu modified. 1. Use AMS_List_Query_PVT.Update_list_Query
2875 -- 2. Remove the checking for change of sel_type. End user
2876 -- cannot change sel_type from GUI.
2877 -- 04/17/01 yxliu add verify sql string for update list query
2878 -- 08/31/01 yxliu added source object name for update list sql
2879 -- 08/31/01 yxliu Add get proper FROM position for validate sql string
2883 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2880 ---------------------------------------------------------------------
2881 PROCEDURE update_sql_cell(
2882 p_api_version IN NUMBER,
2884 p_commit IN VARCHAR2 := FND_API.g_false,
2885 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2886
2887 x_return_status OUT NOCOPY VARCHAR2,
2888 x_msg_count OUT NOCOPY NUMBER,
2889 x_msg_data OUT NOCOPY VARCHAR2,
2890
2891 p_sql_cell_rec IN sqlcell_rec_type
2892 )
2893 IS
2894
2895 l_api_version CONSTANT NUMBER := 1.0;
2896 l_api_name CONSTANT VARCHAR2(30) := 'update_sql_cell';
2897 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2898
2899 l_return_status VARCHAR2(1);
2900 l_sql_cell_rec sqlcell_rec_type := p_sql_cell_rec;
2901 l_cell_rec cell_rec_type;
2902 l_sql_string VARCHAR2(20000);
2903
2904 l_from_position NUMBER := 0;
2905
2906 CURSOR c_cell IS
2907 SELECT *
2908 FROM ams_cells_sel_all_v
2909 WHERE cell_id = p_sql_cell_rec.cell_id;
2910
2911 l_sql_cell_rec_old c_cell%ROWTYPE;
2912
2913 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type;
2914 l_object_version_number NUMBER;
2915 l_list_object_version_number NUMBER;
2916
2917 CURSOR c_get_list_query(p_list_query_id NUMBER) IS
2918 SELECT object_version_number
2919 FROM AMS_LIST_QUERIES_ALL
2920 WHERE list_query_id = p_list_query_id;
2921
2922 BEGIN
2923
2924 --------------------- initialize -----------------------
2925 SAVEPOINT update_sql_cell;
2926
2927 AMS_Utility_PVT.debug_message(l_full_name||': start');
2928
2929 IF FND_API.to_boolean(p_init_msg_list) THEN
2930 FND_MSG_PUB.initialize;
2931 END IF;
2932
2933 IF NOT FND_API.compatible_api_call(
2934 l_api_version,
2935 p_api_version,
2936 l_api_name,
2937 g_pkg_name
2938 ) THEN
2939 RAISE FND_API.g_exc_unexpected_error;
2940 END IF;
2941
2942 x_return_status := FND_API.g_ret_sts_success;
2943
2944 ------------------ call update_cell -------------------
2945
2946 --bmuthukr bug 5130570
2947 if trim(l_sql_cell_rec.list_sql_string) is null then
2948 FND_MESSAGE.set_name('AMS', 'AMS_CELL_BLANK_SQL');
2949 FND_MSG_PUB.add;
2950 RAISE FND_API.g_exc_error;
2951 end if;
2952 --
2953
2954 AMS_Utility_PVT.debug_message(l_full_name ||': update_cell');
2955
2956 l_cell_rec.cell_id := l_sql_cell_rec.cell_id;
2957 l_cell_rec.sel_type := l_sql_cell_rec.sel_type;
2958 l_cell_rec.last_update_date := l_sql_cell_rec.last_update_date;
2959 l_cell_rec.last_updated_by := l_sql_cell_rec.last_updated_by;
2960 l_cell_rec.creation_date := l_sql_cell_rec.creation_date;
2961 l_cell_rec.created_by := l_sql_cell_rec.created_by;
2962 l_cell_rec.last_update_login := l_sql_cell_rec.last_update_login;
2963 l_cell_rec.object_version_number := l_sql_cell_rec.object_version_number;
2964 l_cell_rec.cell_code := l_sql_cell_rec.cell_code;
2965 l_cell_rec.enabled_flag := l_sql_cell_rec.enabled_flag;
2966 l_cell_rec.original_size := l_sql_cell_rec.original_size;
2967 l_cell_rec.parent_cell_id := l_sql_cell_rec.parent_cell_id;
2968 l_cell_rec.org_id := l_sql_cell_rec.org_id;
2969 l_cell_rec.owner_id := l_sql_cell_rec.owner_id;
2970 l_cell_rec.cell_name := l_sql_cell_rec.cell_name;
2971 l_cell_rec.description := l_sql_cell_rec.description;
2972 l_cell_rec.status_code := l_sql_cell_rec.status_code;
2973 l_cell_rec.status_date := l_sql_cell_rec.status_date;
2974 l_cell_rec.user_status_id := l_sql_cell_rec.user_status_id;
2975
2976
2977 update_cell(
2978 p_api_version => l_api_version,
2979 p_init_msg_list => p_init_msg_list,
2980 p_validation_level => p_validation_level,
2981 p_commit => FND_API.g_false,
2982 x_return_status => l_return_status,
2983 x_msg_count => x_msg_count,
2984 x_msg_data => x_msg_data,
2985 p_cell_rec => l_cell_rec
2986 );
2987
2988 IF l_return_status = FND_API.g_ret_sts_error THEN
2989 RAISE FND_API.g_exc_error;
2990 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2991 RAISE FND_API.g_exc_unexpected_error;
2992 END IF;
2993
2994 --------------------- update selection ----------------------------
2995 AMS_Utility_PVT.debug_message(l_full_name ||': update selection');
2996
2997 OPEN c_cell;
2998 FETCH c_cell INTO l_sql_cell_rec_old;
2999 IF c_cell%NOTFOUND THEN
3000 CLOSE c_cell;
3001 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3002 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3003 FND_MSG_PUB.add;
3004 END IF;
3005 RAISE FND_API.g_exc_error;
3006 END IF;
3007 CLOSE c_cell;
3008
3009 IF 'DIWB' = upper(l_sql_cell_rec.sel_type) THEN
3010 IF l_sql_cell_rec.discoverer_sql_id IS NULL AND
3011 l_sql_cell_rec_old.discoverer_sql_id IS NOT NULL THEN
3012
3013 DELETE FROM ams_act_discoverer_all
3014 WHERE act_discoverer_used_by_id = l_sql_cell_rec.cell_id
3015 AND arc_act_discoverer_used_by = 'CELL';
3016
3017 IF (SQL%NOTFOUND) THEN
3018 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3019 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3020 FND_MSG_PUB.add;
3021 END IF;
3025 ELSIF l_sql_cell_rec.discoverer_sql_id IS NOT NULL AND
3022 AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');
3023 RAISE FND_API.g_exc_error;
3024 END IF;
3026 l_sql_cell_rec_old.discoverer_sql_id IS NULL THEN
3027
3028 add_sel_workbook(
3029 p_api_version => l_api_version,
3030 p_init_msg_list => p_init_msg_list,
3031 p_commit => FND_API.g_false,
3032 p_validation_level => p_validation_level,
3033 x_return_status => l_return_status,
3034 x_msg_count => x_msg_count,
3035 x_msg_data => x_msg_data,
3036 p_cell_id => l_sql_cell_rec.cell_id,
3037 p_discoverer_sql_id => l_sql_cell_rec.discoverer_sql_id
3038 );
3039 IF l_return_status = FND_API.g_ret_sts_error THEN
3040 RAISE FND_API.g_exc_error;
3041 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3042 RAISE FND_API.g_exc_unexpected_error;
3043 END IF;
3044
3045 ELSIF l_sql_cell_rec.discoverer_sql_id <> l_sql_cell_rec_old.discoverer_sql_id THEN
3046 -- remove the old workbook relationship
3047 DELETE FROM ams_act_discoverer_all
3048 WHERE act_discoverer_used_by_id = l_sql_cell_rec.cell_id
3049 AND arc_act_discoverer_used_by = 'CELL';
3050
3051 IF (SQL%NOTFOUND) THEN
3052 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3053 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3054 FND_MSG_PUB.add;
3055 END IF;
3056 AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');
3057 RAISE FND_API.g_exc_error;
3058 END IF;
3059 -- add new workbook relationship
3060 add_sel_workbook(
3061 p_api_version => l_api_version,
3062 p_init_msg_list => p_init_msg_list,
3063 p_commit => FND_API.g_false,
3064 p_validation_level => p_validation_level,
3065 x_return_status => l_return_status,
3066 x_msg_count => x_msg_count,
3067 x_msg_data => x_msg_data,
3068 p_cell_id => l_sql_cell_rec.cell_id,
3069 p_discoverer_sql_id => l_sql_cell_rec.discoverer_sql_id
3070 );
3071 IF l_return_status = FND_API.g_ret_sts_error THEN
3072 RAISE FND_API.g_exc_error;
3073 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3074 RAISE FND_API.g_exc_unexpected_error;
3075 END IF;
3076 END IF;
3077 -- end if discoverer_sql_id changed
3078 ELSIF 'SQL' = upper(l_sql_cell_rec.sel_type) THEN
3079 l_sql_string := upper(l_sql_cell_rec.list_sql_string);
3080 -- find ' from ' position
3081 IF instr(l_sql_string, ' FROM ') > 0
3082 THEN
3083 l_from_position := instr(l_sql_string, ' FROM ');
3084 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ') > 0
3085 THEN
3086 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM ');
3087 ELSIF instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10)) > 0
3088 THEN
3089 l_from_position := instr(l_sql_string, ' FROM'||FND_GLOBAL.LOCAL_CHR(10));
3090 ELSIF instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10)) >0
3091 THEN
3092 l_from_position := instr(l_sql_string, FND_GLOBAL.LOCAL_CHR(10)||'FROM'||FND_GLOBAL.LOCAL_CHR(10));
3093 END IF;
3094
3095 IF instr(l_sql_string, 'PARTY_ID') = 0
3096 OR l_from_position = 0
3097 OR instr(l_sql_string, 'PARTY_ID') > l_from_position
3098 THEN
3099 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3100 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_SQL');
3101 FND_MSG_PUB.add;
3102 END IF;
3103 RAISE FND_API.g_exc_error;
3104 RETURN;
3105 END IF;
3106
3107 IF instr(l_sql_string, 'ORDER BY') > 0
3108 OR instr(l_sql_string, 'GROUP BY') > 0
3109 THEN
3110 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3111 FND_MESSAGE.set_name('AMS', 'AMS_CELL_INVALID_ORDERBY');
3112 FND_MSG_PUB.add;
3113 END IF;
3114 RAISE FND_API.g_exc_error;
3115 -- x_return_status := FND_API.g_ret_sts_error;
3116 RETURN;
3117 END IF;
3118
3119 l_list_query_rec.list_query_id := l_sql_cell_rec.list_query_id;
3120 l_list_query_rec.name := l_sql_cell_rec.cell_name;
3121 l_list_query_rec.type := l_sql_cell_rec.cell_code;
3122 l_list_query_rec.sql_string := l_sql_cell_rec.list_sql_string;
3123 l_list_query_rec.primary_key := 'PARTY_ID';
3124 l_list_query_rec.object_version_number := l_sql_cell_rec.list_query_version_number;
3125 l_list_query_rec.source_object_name := l_sql_cell_rec.source_object_name;
3126
3127 AMS_UTILITY_PVT.debug_message('l_full_name: update_list_query');
3128
3129 AMS_List_Query_PVT.Update_List_Query(
3130 p_api_version_number => l_api_version,
3131 p_init_msg_list => p_init_msg_list,
3132 p_commit => FND_API.g_false,
3133 p_validation_level => p_validation_level,
3134 x_return_status => l_return_status,
3135 x_msg_count => x_msg_count,
3136 x_msg_data => x_msg_data,
3137 p_list_query_rec => l_list_query_rec,
3138 x_object_version_number => l_object_version_number
3139 );
3140 IF l_return_status = FND_API.g_ret_sts_error THEN
3141 RAISE FND_API.g_exc_error;
3145 END IF;
3142 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3143 RAISE FND_API.g_exc_unexpected_error;
3144 END IF;
3146 -- end selection type
3147
3148 ------------------------- finish -------------------------------
3149
3150 IF FND_API.to_boolean(p_commit) THEN
3151 COMMIT;
3152 END IF;
3153
3154 FND_MSG_PUB.count_and_get(
3155 p_encoded => FND_API.g_false,
3156 p_count => x_msg_count,
3157 p_data => x_msg_data
3158 );
3159
3160 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3161
3162 EXCEPTION
3163
3164 WHEN FND_API.g_exc_error THEN
3165 ROLLBACK TO update_sql_cell;
3166 x_return_status := FND_API.g_ret_sts_error;
3167 FND_MSG_PUB.count_and_get(
3168 p_encoded => FND_API.g_false,
3169 p_count => x_msg_count,
3170 p_data => x_msg_data
3171 );
3172 WHEN FND_API.g_exc_unexpected_error THEN
3173 ROLLBACK TO update_sql_cell;
3174 x_return_status := FND_API.g_ret_sts_unexp_error ;
3175 FND_MSG_PUB.count_and_get(
3176 p_encoded => FND_API.g_false,
3177 p_count => x_msg_count,
3178 p_data => x_msg_data
3179 );
3180
3181 WHEN OTHERS THEN
3182 ROLLBACK TO update_sql_cell;
3183 x_return_status := FND_API.g_ret_sts_unexp_error ;
3184
3185 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
3186 THEN
3187 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3188 END IF;
3189
3190 FND_MSG_PUB.count_and_get(
3191 p_encoded => FND_API.g_false,
3192 p_count => x_msg_count,
3193 p_data => x_msg_data
3194 );
3195 END update_sql_cell;
3196
3197 /*****************************************************************************/
3198 -- Procedure
3199 -- Update_Segment_Size
3200 --
3201 -- Purpose
3202 -- This procedure will calculate the segment size for one cell or all cells.
3203 -- If called to update all cells, one fail will not block the process, but
3204 -- leave the segment size for that cell NULL.
3205 -- Notes
3206 --
3207 --
3208 -- History
3209 -- 04/09/2001 yxliu created
3210 -- 04/16/2001 yxliu modified. use get_comp_segment_size instead.
3211 -- 06/20/2001 yxliu modified the way to update single cell
3212 ------------------------------------------------------------------------------
3213 PROCEDURE Update_Segment_Size
3214 ( p_cell_id IN NUMBER DEFAULT NULL,
3215 x_return_status OUT NOCOPY VARCHAR2,
3216 x_msg_count OUT NOCOPY NUMBER,
3217 x_msg_data OUT NOCOPY VARCHAR2
3218 )
3219 IS
3220 l_api_name CONSTANT VARCHAR2(30) := 'Update_Segment_Size';
3221
3222 l_return_status VARCHAR2(1) ;
3223 l_msg_count NUMBER ;
3224 l_msg_data VARCHAR2(2000);
3225
3226 l_cell_id NUMBER := p_cell_id;
3227 l_object_version_number NUMBER;
3228 l_sql_string VARCHAR2(20000) := '';
3229 l_segment_size NUMBER ;
3230
3231 t_cell_id t_number;
3232 t_object_version_number t_number;
3233 t_segment_size t_number;
3234
3235 l_iterator NUMBER := 1;
3236
3237 CURSOR c_all_cell_rec IS
3238 SELECT cell_id, object_version_number
3239 FROM ams_cells_vl;
3240
3241 l_cell_rec cell_rec_type;
3242
3243 BEGIN
3244 AMS_Utility_PVT.Debug_Message(l_api_name||' Start ');
3245
3246 x_return_status := FND_API.G_RET_STS_SUCCESS;
3247
3248 IF p_cell_id IS NOT NULL
3249 THEN
3250 -- Create the Savepoint
3251 SAVEPOINT Update_Segment_Size;
3252
3253 -- Update segment size for one particular cell
3254
3255 -- Calculate segment size
3256 AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ' || l_cell_id);
3257 AMS_CELL_PVT.get_comp_segment_size(
3258 p_api_version => 1,
3259 p_init_msg_list => FND_API.g_false,
3260 p_validation_level => FND_API.g_valid_level_full,
3261 x_return_status => x_return_status,
3262 x_msg_count => x_msg_count,
3263 x_msg_data => x_msg_data,
3264 p_cell_id => l_cell_id,
3265 x_segment_size => l_segment_size
3266 );
3267 IF x_return_status = FND_API.g_ret_sts_error THEN
3268 RAISE FND_API.g_exc_error;
3269 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3270 RAISE FND_API.g_exc_unexpected_error;
3271 END IF;
3272
3273 -- Update cell record
3274 AMS_Utility_PVT.Debug_Message(l_api_name||' update_cell');
3275
3276 UPDATE ams_cells_all_b
3277 SET object_version_number = object_version_number + 1 ,
3278 original_size = l_segment_size
3279 WHERE cell_id = l_cell_id;
3280
3281 -- If no errors, commit the work
3282 COMMIT WORK;
3283 ELSE
3284 -- Get all the cells
3285
3286 -- Create the Savepoint
3287 SAVEPOINT Update_Segment_Size;
3288
3289 OPEN c_all_cell_rec;
3290 LOOP -- the loop for all CELL_IDs
3291 FETCH c_all_cell_rec INTO l_cell_id, l_object_version_number;
3292 EXIT WHEN c_all_cell_rec%NOTFOUND;
3293
3294 t_cell_id(l_iterator) := l_cell_id;
3295 t_object_version_number(l_iterator) := l_object_version_number + 1;
3296
3297
3298 -- Calculate segment size
3299 AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ' || l_cell_id);
3300 AMS_CELL_PVT.get_comp_segment_size(
3301 p_api_version => 1,
3302 p_init_msg_list => NULL,
3303 p_validation_level => NULL,
3304 x_return_status => x_return_status,
3305 x_msg_count => x_msg_count,
3306 x_msg_data => x_msg_data,
3307 p_cell_id => l_cell_id,
3308 x_segment_size => l_segment_size
3309 );
3310 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3311 t_segment_size(l_iterator) := l_segment_size;
3312 ELSE
3313 t_segment_size(l_iterator) := null;
3314 FND_MSG_PUB.count_and_get(
3315 p_encoded => FND_API.g_false,
3316 p_count => x_msg_count,
3317 p_data => x_msg_data
3318 );
3319 END IF;
3320
3321 l_iterator := l_iterator + 1;
3322 END LOOP; -- end: the loop for all CELL_IDs
3323 CLOSE c_all_cell_rec;
3324
3325 -- Do bulk update
3326 AMS_Utility_PVT.Debug_Message(l_api_name||' get segment size for cell ');
3327 FORALL I in t_cell_id.first .. t_cell_id.last
3328 UPDATE ams_cells_all_b
3329 SET object_version_number = t_object_version_number(i) ,
3330 original_size = t_segment_size(i)
3331 WHERE cell_id = t_cell_id(i);
3332
3333 -- commit;
3334 COMMIT WORK;
3335 END IF;
3336
3337 EXCEPTION
3338 WHEN FND_API.g_exc_error THEN
3339 IF (c_all_cell_rec%ISOPEN) THEN
3340 close c_all_cell_rec ;
3341 END IF;
3342 ROLLBACK TO Update_Segment_Size;
3343 x_return_status := FND_API.g_ret_sts_error;
3344 FND_MSG_PUB.count_and_get(
3345 p_encoded => FND_API.g_false,
3346 p_count => x_msg_count,
3347 p_data => x_msg_data
3348 );
3349 WHEN FND_API.g_exc_unexpected_error THEN
3350 IF (c_all_cell_rec%ISOPEN) THEN
3351 close c_all_cell_rec ;
3352 END IF;
3353 ROLLBACK TO Update_Segment_Size;
3354 x_return_status := FND_API.g_ret_sts_unexp_error ;
3355 FND_MSG_PUB.count_and_get (
3356 p_encoded => FND_API.g_false,
3357 p_count => x_msg_count,
3358 p_data => x_msg_data
3359 );
3360 WHEN OTHERS THEN
3361 IF (c_all_cell_rec%ISOPEN) THEN
3362 close c_all_cell_rec ;
3363 END IF;
3364 ROLLBACK TO Update_Segment_Size;
3365 x_return_status := FND_API.g_ret_sts_unexp_error ;
3366
3367 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3368 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3369 END IF;
3370
3371 FND_MSG_PUB.count_and_get (
3372 p_encoded => FND_API.g_false,
3373 p_count => x_msg_count,
3374 p_data => x_msg_data
3375 );
3376 END Update_Segment_Size;
3377
3378 /*****************************************************************************/
3379 -- Procedure
3380 -- Refresh_Segment_Size
3381 --
3382 -- Purpose
3383 -- This procedure is created to as a concurrent program which
3384 -- will call the update_segment_size and will return errors if any
3385 --
3386 -- Notes
3387 --
3388 --
3389 -- History
3390 -- 04/09/2001 yxliu created
3391 -- 06/20/2001 yxliu moved to package AMS_Party_Mkt_Seg_Loader_PVT
3392 ------------------------------------------------------------------------------
3393
3394 --PROCEDURE Refresh_Segment_Size
3395 --( errbuf OUT NOCOPY VARCHAR2,
3396 -- retcode OUT NUMBER,
3397 -- p_cell_id IN NUMBER DEFAULT NULL
3398 --)
3399 --IS
3400 -- l_return_status VARCHAR2(1) ;
3401 -- l_msg_count NUMBER ;
3402 -- l_msg_data VARCHAR2(2000);
3403 --BEGIN
3404 -- FND_MSG_PUB.initialize;
3405 -- -- Call the procedure to refresh Segment size
3406 -- Update_Segment_Size
3407 -- ( p_cell_id => p_cell_id,
3408 -- x_return_status => l_return_status,
3409 -- x_msg_count => l_msg_count,
3410 -- x_msg_data => l_msg_data);
3411 --
3412 -- -- Write_log ;
3413 -- Ams_Utility_Pvt.Write_Conc_log ;
3414 -- IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
3415 -- retcode :=0;
3416 -- ELSE
3417 -- retcode :=1;
3418 -- errbuf := l_msg_data ;
3419 -- END IF;
3420 --END Refresh_Segment_Size ;
3421
3422 END AMS_cell_PVT;