[Home] [Help]
PACKAGE BODY: APPS.OZF_DENORM_QUERIES_PVT
Source
1 PACKAGE BODY Ozf_Denorm_Queries_Pvt AS
2 /*$Header: ozfvofdb.pls 120.0 2005/06/01 02:39:55 appldev noship $*/
3
4
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
8 -- create_denorm_queries
9 --
10 -- HISTORY
11 -- pmothuku
12
13 ---------------------------------------------------------------------
14 PROCEDURE create_denorm_queries(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
17 p_commit IN VARCHAR2 := FND_API.g_false,
18 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
19
20 p_denorm_queries_rec IN denorm_queries_rec_type,
21
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25
26 x_denorm_query_id OUT NOCOPY NUMBER
27 )
28 IS
29
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_api_name CONSTANT VARCHAR2(30) := 'create_denorm_queries';
32 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
33
34 l_return_status VARCHAR2(1);
35 l_denorm_queries_rec denorm_queries_rec_type:= p_denorm_queries_rec;
36 l_stringArray stringArray;
37 l_string varchar2(32000):= l_denorm_queries_rec.SQL_STATEMENT;
38 l_denorm_count NUMBER;
39
40 CURSOR c_denorm_queries_seq IS
41 SELECT ozf_denorm_queries_s.NEXTVAL
42 FROM DUAL;
43
44
45 CURSOR c_denorm_count(denorm_id IN NUMBER) IS
46 SELECT count(*)
47 FROM ozf_denorm_queries
48 WHERE denorm_query_id = denorm_id;
49 BEGIN
50
51 --------------------- initialize -----------------------
52 SAVEPOINT create_denorm_queries;
53
54 OZF_Utility_PVT.debug_message(l_full_name||': start');
55
56 IF FND_API.to_boolean(p_init_msg_list) THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59
60 IF NOT FND_API.compatible_api_call(
61 l_api_version,
62 p_api_version,
63 l_api_name,
64 g_pkg_name
65 ) THEN
66 RAISE FND_API.g_exc_unexpected_error;
67 END IF;
68
69 x_return_status := FND_API.g_ret_sts_success;
70
71 --------------------API CODE--------------------------
72
73
74 ----------------------- validate -----------------------
75 OZF_Utility_PVT.debug_message(l_full_name ||': validate');
76 --dbms_output.put_line('the validation begins');
77
78 validate_denorm_queries(
79 p_api_version => l_api_version,
80 p_init_msg_list => p_init_msg_list,
81 p_validation_level => p_validation_level,
82 p_validation_mode =>'CRE',
83 p_denorm_queries_rec => l_denorm_queries_rec,
84 x_return_status => l_return_status,
85 x_msg_count => x_msg_count,
86 x_msg_data => x_msg_data
87 );
88 IF l_return_status = FND_API.g_ret_sts_error THEN
89 RAISE FND_API.g_exc_error;
90 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
91 RAISE FND_API.g_exc_unexpected_error;
92 END IF;
93
94 --dbms_output.put_line('the denorm query id beginning');
95 IF l_denorm_queries_rec.denorm_query_id IS NULL THEN
96 LOOP
97 OPEN c_denorm_queries_seq;
98 FETCH c_denorm_queries_seq INTO l_denorm_queries_rec.denorm_query_id;
99 CLOSE c_denorm_queries_seq;
100
101 OPEN c_denorm_count(l_denorm_queries_rec.denorm_query_id);
102 FETCH c_denorm_count INTO l_denorm_count;
103 CLOSE c_denorm_count;
104
105 EXIT WHEN l_denorm_count = 0;
106 END LOOP;
107
108 END IF;
109
110 --dbms_output.put_line('the denorm condition id IS'||l_denorm_queries_rec.condition_id_column);
111 string_length_check(l_string,l_stringArray);
112 --dbms_output.put_line('before insert'||SUBSTR(l_stringArray(1),1,150));
113 --dbms_output.put_line('before insert for sec'||SUBSTR(l_stringArray(2),1,150));
114 INSERT INTO ozf_denorm_queries(
115 DENORM_QUERY_ID
116 ,QUERY_FOR
117 ,CONTEXT
118 ,ATTRIBUTE
119 ,CONDITION_ID_COLUMN
120 ,CONDITION_NAME_COLUMN
121 ,ACTIVE_FLAG
122 ,CREATION_DATE
123 ,CREATED_BY
124 ,LAST_UPDATE_DATE
125 ,LAST_UPDATED_BY
126 ,LAST_UPDATE_LOGIN
127 ,SEEDED_FLAG
128 ,SQL_VALIDATION_1
129 ,SQL_VALIDATION_2
130 ,SQL_VALIDATION_3
131 ,SQL_VALIDATION_4
132 ,SQL_VALIDATION_5
133 ,SQL_VALIDATION_6
134 ,SQL_VALIDATION_7
135 ,SQL_VALIDATION_8,
136 OBJECT_VERSION_NUMBER
137 )
138 VALUES(
139 l_denorm_queries_rec.denorm_query_id,
140 l_denorm_queries_rec.QUERY_FOR,
141 l_denorm_queries_rec.CONTEXT,
142 l_denorm_queries_rec.ATTRIBUTE ,
143 l_denorm_queries_rec.CONDITION_ID_COLUMN,
144 l_denorm_queries_rec.CONDITION_NAME_COLUMN,
145 nvl(l_denorm_queries_rec.active_flag,'N'),
146 SYSDATE,
147 FND_GLOBAL.user_id,
148 SYSDATE,
149 FND_GLOBAL.user_id,
150 FND_GLOBAL.conc_login_id,
151 'N',
152 l_stringArray(1),
153 l_stringArray(2),
154 l_stringArray(3),
155 l_stringArray(4),
156 l_stringArray(5),
157 l_stringArray(6),
158 l_stringArray(7),
159 l_stringArray(8),
160 1
161
162 );
163
164
165
166 x_denorm_query_id := l_denorm_queries_rec.denorm_query_id;
167
168
169 IF FND_API.to_boolean(p_commit) THEN
170 COMMIT;
171 END IF;
172
173 FND_MSG_PUB.count_and_get(
174 p_encoded => FND_API.g_false,
175 p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178
179 OZF_Utility_PVT.debug_message(l_full_name ||': end');
180
181 EXCEPTION
182
183 WHEN FND_API.g_exc_error THEN
184 ROLLBACK TO create_denorm_queries;
185 x_return_status := FND_API.g_ret_sts_error;
186 FND_MSG_PUB.count_and_get(
187 p_encoded => FND_API.g_false,
188 p_count => x_msg_count,
189 p_data => x_msg_data
190 );
191
192 WHEN FND_API.g_exc_unexpected_error THEN
193 ROLLBACK TO create_denorm_queries;
194 x_return_status := FND_API.g_ret_sts_unexp_error ;
195 FND_MSG_PUB.count_and_get(
196 p_encoded => FND_API.g_false,
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200
201
202 WHEN OTHERS THEN
203
204 ROLLBACK TO create_denorm_queries;
205 x_return_status := FND_API.g_ret_sts_unexp_error ;
206
207 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
208 THEN
209 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
210 END IF;
211
212 FND_MSG_PUB.count_and_get(
213 p_encoded => FND_API.g_false,
214 p_count => x_msg_count,
215 p_data => x_msg_data
216 );
217
218 END create_denorm_queries;
219
220 ---------------------------------------------------------------------
221 -- PROCEDURE
222 -- update_denorm_queries
223 --
224 -- HISTORY
225 -- pmothuku Created
226
227 ----------------------------------------------------------------------
228 PROCEDURE update_denorm_queries(
229 p_api_version IN NUMBER,
230 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
231 p_commit IN VARCHAR2 := FND_API.g_false,
232 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
233
234 p_denorm_queries_rec IN denorm_queries_rec_type,
235
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2
239 )
240 IS
241
242 l_api_version CONSTANT NUMBER := 1.0;
243 l_api_name CONSTANT VARCHAR2(30) := 'update_denorm_queries';
244 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
245
246 l_denorm_queries_rec denorm_queries_rec_type:=p_denorm_queries_rec;
247 temp_denorm_queries_rec denorm_queries_rec_type;
248
249 l_count NUMBER;
250 l_msg VARCHAR2(2000);
251 l_msg_count NUMBER;
252 l_return_status VARCHAR2(1);
253 l_stringArray stringArray;
254 BEGIN
255
256 OZF_Utility_PVT.debug_message(l_full_name||': entered update');
257
258 -------------------- initialize -------------------------
259 SAVEPOINT update_denorm_queries;
260
261 OZF_Utility_PVT.debug_message(l_full_name||': start');
262
263 IF FND_API.to_boolean(p_init_msg_list) THEN
264 FND_MSG_PUB.initialize;
265 END IF;
266
267 IF NOT FND_API.compatible_api_call(
268 l_api_version,
269 p_api_version,
270 l_api_name,
271 g_pkg_name
272 ) THEN
273 RAISE FND_API.g_exc_unexpected_error;
274 END IF;
275
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277
278 ----------------------- validate ----------------------
279
280 OZF_Utility_PVT.debug_message(l_full_name ||': validate');
281 OZF_Utility_PVT.debug_message(l_denorm_queries_rec.denorm_query_id ||': validate1');
282
283 -- replace g_miss_char/num/date with current column values
284
285 complete_denorm_queries_rec(l_denorm_queries_rec, temp_denorm_queries_rec);
286
287 validate_denorm_queries(
288 p_api_version => l_api_version,
289 p_init_msg_list => p_init_msg_list,
290 p_validation_level => p_validation_level,
291 p_validation_mode =>'UPD',
292 p_denorm_queries_rec => temp_denorm_queries_rec,
293 x_return_status => l_return_status,
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data
296 );
297 IF l_return_status = FND_API.g_ret_sts_error THEN
298 RAISE FND_API.g_exc_error;
299 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
300 RAISE FND_API.g_exc_unexpected_error;
301 END IF;
302
303 string_length_check(temp_denorm_queries_rec.SQL_STATEMENT,l_stringArray);
304
305
306
307 UPDATE ozf_denorm_queries SET
308 last_update_date = SYSDATE,
309 last_updated_by = FND_GLOBAL.user_id,
310 last_update_login = FND_GLOBAL.conc_login_id,
311 active_flag = nvl(temp_denorm_queries_rec.active_flag,'N'),
312 query_for = temp_denorm_queries_rec.query_for,
313 context = temp_denorm_queries_rec.context,
314 attribute = temp_denorm_queries_rec.attribute,
315 condition_name_column = temp_denorm_queries_rec.condition_name_column,
316 condition_id_column = temp_denorm_queries_rec.condition_id_column,
317 sql_validation_1 = l_stringArray(1),
318 sql_validation_2 = l_stringArray(2),
319 sql_validation_3 = l_stringArray(3),
320 sql_validation_4 = l_stringArray(4),
321 sql_validation_5 = l_stringArray(5),
322 sql_validation_6 = l_stringArray(6),
323 sql_validation_7 = l_stringArray(7),
324 sql_validation_8 = l_stringArray(8),
325 object_version_number=temp_denorm_queries_rec.object_version_number+1
326 WHERE denorm_query_id = temp_denorm_queries_rec.denorm_query_id
327 AND object_version_number =temp_denorm_queries_rec.object_version_number;
328
329
330 IF (SQL%NOTFOUND) THEN
331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
332 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
333 FND_MSG_PUB.add;
334 END IF;
335 RAISE FND_API.g_exc_error;
336 END IF;
337
338
339
340
341 -------------------- finish --------------------------
342 IF FND_API.to_boolean(p_commit) THEN
343 COMMIT;
344 END IF;
345
346 FND_MSG_PUB.count_and_get(
347 p_encoded => FND_API.g_false,
348 p_count => x_msg_count,
349 p_data => x_msg_data
350 );
351
352 OZF_Utility_PVT.debug_message(l_full_name ||': end');
353
354 EXCEPTION
355
356 WHEN FND_API.g_exc_error THEN
357 ROLLBACK TO update_denorm_queries;
358 x_return_status := FND_API.g_ret_sts_error;
359 FND_MSG_PUB.count_and_get(
360 p_encoded => FND_API.g_false,
361 p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364
365 WHEN FND_API.g_exc_unexpected_error THEN
366 ROLLBACK TO update_denorm_queries;
367 x_return_status := FND_API.g_ret_sts_unexp_error ;
368 FND_MSG_PUB.count_and_get(
369 p_encoded => FND_API.g_false,
370 p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373
374 WHEN OTHERS THEN
375
376
377 ROLLBACK TO update_denorm_queries;
378 x_return_status := FND_API.g_ret_sts_unexp_error ;
379
380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
381 THEN
382 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
383 END IF;
384
385 FND_MSG_PUB.count_and_get(
386 p_encoded => FND_API.g_false,
387 p_count => x_msg_count,
388 p_data => x_msg_data
389 );
390
391 END update_denorm_queries;
392
393 ---------------------------------------------------------------
394 -- PROCEDURE
395 -- delete_denorm_queries
396 --
397 -- HISTORY
398 -- PMOTHUKU Created.
399 ---------------------------------------------------------------
400 PROCEDURE delete_denorm_queries(
401 p_api_version IN NUMBER,
402 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
403 p_commit IN VARCHAR2 := FND_API.g_false,
404
405 p_denorm_query_id IN NUMBER,
406 p_object_version IN NUMBER,
407
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 )
412 IS
413
414 l_api_version CONSTANT NUMBER := 1.0;
415 l_api_name CONSTANT VARCHAR2(30) := 'delete_event_offer';
416 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
417 l_denorm_query_id NUMBER;
418
419
420 BEGIN
421
422 --------------------- initialize -----------------------
423 SAVEPOINT delete_denorm_queries;
424
425 OZF_Utility_PVT.debug_message(l_full_name||': start');
426
427 IF FND_API.to_boolean(p_init_msg_list) THEN
428 FND_MSG_PUB.initialize;
429 END IF;
430
431 IF NOT FND_API.compatible_api_call(
432 l_api_version,
433 p_api_version,
434 l_api_name,
435 g_pkg_name
436 ) THEN
437 RAISE FND_API.g_exc_unexpected_error;
438 END IF;
439
440 x_return_status := FND_API.G_RET_STS_SUCCESS;
441
442
443 ------------------------ delete ------------------------
444 OZF_Utility_PVT.debug_message(l_full_name ||': delete');
445
446 delete ozf_denorm_queries
447 WHERE denorm_query_id = p_denorm_query_id
448 AND OBJECT_VERSION_NUMBER=p_object_version;
449
450 -------------------- finish --------------------------
451 IF FND_API.to_boolean(p_commit) THEN
452 COMMIT;
453 END IF;
454
455 FND_MSG_PUB.count_and_get(
456 p_encoded => FND_API.g_false,
457 p_count => x_msg_count,
458 p_data => x_msg_data
459 );
460
461 OZF_Utility_PVT.debug_message(l_full_name ||': end');
462
463 EXCEPTION
464
465 WHEN FND_API.g_exc_error THEN
466 ROLLBACK TO delete_denorm_queries;
467 x_return_status := FND_API.g_ret_sts_error;
468 FND_MSG_PUB.count_and_get(
469 p_encoded => FND_API.g_false,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473
474 WHEN FND_API.g_exc_unexpected_error THEN
475 ROLLBACK TO delete_denorm_queries;
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 ROLLBACK TO delete_denorm_queries;
485 x_return_status := FND_API.g_ret_sts_unexp_error ;
486
487 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
488 THEN
489 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
490 END IF;
491
492 FND_MSG_PUB.count_and_get(
493 p_encoded => FND_API.g_false,
494 p_count => x_msg_count,
495 p_data => x_msg_data
496 );
497
498 END delete_denorm_queries;
499
500
501
502
503
504 --------------------------------------------------------------------
505 -- PROCEDURE
506 -- validate_event_offer
507 --
508 -- HISTORY
509 -- pmothuku Created.
510 --------------------------------------------------------------------
511 PROCEDURE validate_denorm_queries(
512 p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
514 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
515 p_validation_mode IN VARCHAR2,
516 p_denorm_queries_rec IN denorm_queries_rec_type,
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2
520 )IS
521 l_api_version CONSTANT NUMBER := 1.0;
522 l_api_name CONSTANT VARCHAR2(30) := 'validate_denorm_queries';
523 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
524 l_return_status VARCHAR2(1);
525 l_mode varchar2(3):=p_validation_mode;
526 BEGIN
527
528 ----------------------- initialize --------------------
529 OZF_Utility_PVT.debug_message(l_full_name||': start');
530
531 IF FND_API.to_boolean(p_init_msg_list) THEN
532 FND_MSG_PUB.initialize;
533 END IF;
534
535 IF NOT FND_API.compatible_api_call(
536 l_api_version,
537 p_api_version,
538 l_api_name,
539 g_pkg_name
540 ) THEN
541 RAISE FND_API.g_exc_unexpected_error;
542 END IF;
543
544 x_return_status := FND_API.g_ret_sts_success;
545
546 ---------------------- validate ------------------------
547 OZF_Utility_PVT.debug_message(l_full_name||': check items');
548
549 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
550 check_denorm_queries_items(
551 p_denorm_queries_rec => p_denorm_queries_rec,
552 p_validation_mode => l_mode,
553 x_return_status => l_return_status
554 );
555 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
556 RAISE FND_API.g_exc_unexpected_error;
557 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
558 RAISE FND_API.g_exc_error;
559 END IF;
560 END IF;
561
562 OZF_Utility_PVT.debug_message(l_full_name||': check record');
563
564
565
566
567 -------------------- finish --------------------------
568 FND_MSG_PUB.count_and_get(
569 p_encoded => FND_API.g_false,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573
574 OZF_Utility_PVT.debug_message(l_full_name ||': end');
575
576 EXCEPTION
577
578 WHEN FND_API.g_exc_error THEN
579 x_return_status := FND_API.g_ret_sts_error;
580 FND_MSG_PUB.count_and_get(
581 p_encoded => FND_API.g_false,
582 p_count => x_msg_count,
583 p_data => x_msg_data
584 );
585
586 WHEN FND_API.g_exc_unexpected_error THEN
587 x_return_status := FND_API.g_ret_sts_unexp_error ;
588 FND_MSG_PUB.count_and_get(
589 p_encoded => FND_API.g_false,
590 p_count => x_msg_count,
591 p_data => x_msg_data
592 );
593
594 WHEN OTHERS THEN
595 x_return_status := FND_API.g_ret_sts_unexp_error;
596 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
597 THEN
598 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
599 END IF;
600
601 FND_MSG_PUB.count_and_get(
602 p_encoded => FND_API.g_false,
603 p_count => x_msg_count,
604 p_data => x_msg_data
605 );
606
607 END validate_denorm_queries;
608
609
610 ---------------------------------------------------------------------
611 -- PROCEDURE
612 -- check_denorm_queries_update_items
613 --
614 -- HISTORY
615 -- pmothuku Created.
616 ---------------------------------------------------------------------
617 PROCEDURE check_denorm_queries_upd_items(
618 p_denorm_queries_rec IN denorm_queries_rec_type,
619 x_return_status OUT NOCOPY VARCHAR2
620 )
621 IS
622
623 l_query_for varchar2(4):=p_denorm_queries_rec.query_for;
624 l_context_name varchar2(30):=p_denorm_queries_rec.context;
625 l_context_attribute varchar2(30):=p_denorm_queries_rec.attribute;
626
627 temp_query_for varchar2(4);
628 temp_context_name varchar2(30);
629 temp_context_attribute varchar2(30);
630 l_denorm_queries_rec denorm_queries_rec_type;
631 l_denorm_query_id NUMBER;
632 CURSOR chkexists(p_query_for IN varchar2,p_context_name IN varchar2,p_context_attribute IN varchar2) IS
633 select denorm_query_id from ozf_denorm_queries
634 where query_for=p_query_for and context=p_context_name
635 and attribute=p_context_attribute;
636 BEGIN
637 /*
638 OPEN chkexists(l_query_for,l_context_name,l_context_attribute);
639 FETCH chkexists INTO l_denorm_query_id ;
640 IF chkexists%NOTFOUND OR (l_denorm_query_id = p_denorm_queries_rec.denorm_query_id) THEN
641 CLOSE chkexists;
642 X_RETURN_STATUS:='S';
643 ELSE
644 IF(l_denorm_query_id<>p_denorm_queries_rec.denorm_query_id)THEN
645 OZF_Utility_PVT.debug_message('This record already exists');
646 x_return_status:='E';
647 FND_MESSAGE.set_name('OZF', 'OZF_DENORM_QUERIES_EXISTS');
648 FND_MSG_PUB.add;
649 END IF;
650 END IF;
651 */
652 NULL;
653 END ;
654
655
656 ---------------------------------------------------------------------
657 -- PROCEDURE
658 -- check_denorm_queries_req_items
659 --
660 -- HISTORY
661 -- pmothuku Created.
662 ---------------------------------------------------------------------
663 PROCEDURE check_denorm_queries_req_items(
664 p_denorm_queries_rec IN denorm_queries_rec_type,
665 x_return_status OUT NOCOPY VARCHAR2
666 )
667 IS
668 l_msg_error varchar2(4000);
669
670 l_str varchar2(32000);
671 BEGIN
672
673 x_return_status := FND_API.g_ret_sts_success;
674 IF (p_denorm_queries_rec.query_for IS NULL OR p_denorm_queries_rec.query_for = FND_API.g_miss_char) THEN
675 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
676 FND_MESSAGE.set_name('OZF', 'OZF_NO_QUERY_FOR_TYPE');
677 FND_MSG_PUB.add;
678 END IF;
679 x_return_status := FND_API.g_ret_sts_error;
680 RETURN;
681 END IF;
682 IF (p_denorm_queries_rec.context IS NULL OR p_denorm_queries_rec.context = FND_API.g_miss_char) THEN
683 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
684 FND_MESSAGE.set_name('OZF', 'OZF_NO_QUALIFIER_CONTEXT');
685 FND_MSG_PUB.add;
686 END IF;
687 x_return_status := FND_API.g_ret_sts_error;
688 RETURN;
689 END IF;
690 ------------------------ qualifier_context_attribute --------------------------
691 IF (p_denorm_queries_rec.attribute IS NULL OR p_denorm_queries_rec.attribute = FND_API.g_miss_char) THEN
692 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
693 FND_MESSAGE.set_name('OZF', 'OZF_NO_QUALIFIER_CONTEXT_ATTR');
694 FND_MSG_PUB.add;
695 END IF;
696 x_return_status := FND_API.g_ret_sts_error;
697 RETURN;
698
699
700
701 END IF;
702
703
704
705 IF (p_denorm_queries_rec.SQL_STATEMENT IS NULL ) OR (p_denorm_queries_rec.SQL_STATEMENT = FND_API.g_miss_char) THEN
706 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
707 FND_MESSAGE.set_name('OZF', 'OZF_NO_SQLSTATEMENT');
708 FND_MSG_PUB.add;
709 END IF;
710 x_return_status := FND_API.g_ret_sts_error;
711 RETURN;
712 END IF;
713
714 IF (((p_denorm_queries_rec.condition_id_column IS NULL ) OR (p_denorm_queries_rec.condition_id_column = FND_API.g_miss_char)) AND
715 ((p_denorm_queries_rec.condition_name_column IS NULL ) OR (p_denorm_queries_rec.condition_name_column = FND_API.g_miss_char)))THEN
716
717 EXECUTE IMMEDIATE p_denorm_queries_rec.SQL_STATEMENT;
718
719 ELSE
720 IF (((p_denorm_queries_rec.condition_id_column <> NULL ) OR (p_denorm_queries_rec.condition_id_column <> FND_API.g_miss_char)) AND
721 ((p_denorm_queries_rec.condition_name_column IS NULL ) OR (p_denorm_queries_rec.condition_name_column = FND_API.g_miss_char)))
722 THEN
723 l_str :=upper(p_denorm_queries_rec.SQL_STATEMENT);
724 IF(INSTR(l_str,'WHERE',1,1)>0)THEN
725 l_str:=l_str||' '||' '||'AND'||' '||' '||p_denorm_queries_rec.condition_id_column||'=1';
726 ELSE
727 l_str:=l_str||' '||' '||'WHERE'||' '||p_denorm_queries_rec.condition_id_column||'=1';
728 END IF;
729
730 ELSE
731 IF (((p_denorm_queries_rec.condition_name_column <> NULL ) OR (p_denorm_queries_rec.condition_name_column <> FND_API.g_miss_char)) AND
732 ((p_denorm_queries_rec.condition_id_column IS NULL ) OR (p_denorm_queries_rec.condition_id_column = FND_API.g_miss_char)))
733 THEN
734 l_str :=upper(p_denorm_queries_rec.SQL_STATEMENT);
735 IF(INSTR(l_str,'WHERE',1,1)>0)THEN
736 l_str:=l_str||' '||' '||'AND'||' '||' '||p_denorm_queries_rec.condition_name_column||'=''''';
737 ELSE
738 l_str:=l_str||' '||' '||'WHERE'||' '||p_denorm_queries_rec.condition_name_column||'=''''';
739 END IF;
740
741 ELSE
742 l_str :=upper(p_denorm_queries_rec.SQL_STATEMENT);
743 IF(INSTR(l_str,'WHERE',1,1)>0)THEN
744 l_str:=l_str||' '||' '||'AND'||' '||' '||p_denorm_queries_rec.condition_name_column||'='''''||''||' '||'AND'||' '||' '||p_denorm_queries_rec.condition_id_column||'=1';
745 ELSE
746 l_str:=l_str||' '||' '||'WHERE'||' '||p_denorm_queries_rec.condition_name_column||'='''''||''||' ' ||'AND'||' '||' '||p_denorm_queries_rec.condition_id_column||'=1';
747 END IF;
748 END IF;
749 END IF;
750 OZF_Utility_PVT.debug_message('the sql formed is '||l_str);
751 --p_denorm_queries_rec.SQL_STATEMENT:=p_denorm_queries_rec.SQL_STATEMENT||''||'AND'||''||p_denorm_queries_rec.condition_id_column=1;
752 EXECUTE IMMEDIATE l_str;
753 END IF;
754 EXCEPTION
755 when others then
756 x_return_status := FND_API.g_ret_sts_error;
757 FND_MESSAGE.set_name('OZF','OZF_NO_SQLSTATEMENT');
758 FND_MSG_PUB.add;
759 FND_MESSAGE.SET_NAME('OZF','OZF_API_DEBUG_MESSAGE');
760 FND_MESSAGE.SET_TOKEN('TEXT',SQLERRM);
761 FND_MSG_PUB.add;
762 END check_denorm_queries_req_items;
763
764
765 ---------------------------------------------------------------------
766 -- PROCEDURE
767 -- check_denorm_queries_items
768 --
769 -- HISTORY
770 -- PMOTHUKU Created.
771 ---------------------------------------------------------------------
772 PROCEDURE check_denorm_queries_items(
773 p_denorm_queries_rec IN denorm_queries_rec_type,
774 p_validation_mode IN VARCHAR2 ,
775 x_return_status OUT NOCOPY VARCHAR2
776 )
777 IS
778 BEGIN
779
780
781
782 -------------------------- Create or Update Mode ----------------------------
783 OZF_UTILITY_PVT.debug_message('before req_items');
784
785 check_denorm_queries_req_items(
786 p_denorm_queries_rec => p_denorm_queries_rec,
787 x_return_status => x_return_status
788 );
789
790 IF x_return_status <> FND_API.g_ret_sts_success THEN
791 RETURN;
792 END IF;
793
794 IF x_return_status <> FND_API.g_ret_sts_success THEN
795 RETURN;
796 END IF;
797 OZF_UTILITY_PVT.debug_message('THE MODE IS'||p_validation_mode);
798 IF p_validation_mode = 'CRE' THEN
799
800 check_denorm_queries_record(
801 p_denorm_queries_rec => p_denorm_queries_rec,
802 x_return_status => x_return_status
803 );
804 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
805 RAISE FND_API.g_exc_unexpected_error;
806 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
807 RAISE FND_API.g_exc_error;
808 END IF;
809 END IF;
810 -------------------------- Update Mode ----------------------------
811
812 OZF_UTILITY_PVT.debug_message('before ok_items');
813 IF p_validation_mode = 'UPD' THEN
814 check_denorm_queries_upd_items(
815 p_denorm_queries_rec => p_denorm_queries_rec,
816 x_return_status => x_return_status
817 );
818
819 IF x_return_status <> FND_API.g_ret_sts_success THEN
820 RETURN;
821 END IF;
822 END IF;
823
824 END check_denorm_queries_items;
825
826
827 ---------------------------------------------------------------------
828 -- PROCEDURE
829 -- check_denorm_queries_record
830 --
831 -- HISTORY
832 -- pmothuku Created.
833 ---------------------------------------------------------------------
834
835
836
837 PROCEDURE check_denorm_queries_record(
838 p_denorm_queries_rec IN denorm_queries_rec_type,
839 x_return_status OUT NOCOPY VARCHAR2
840 ) IS
841 l_query_for varchar2(4):=p_denorm_queries_rec.query_for;
842 l_context_name varchar2(30):=p_denorm_queries_rec.context;
843 l_context_attribute varchar2(30):=p_denorm_queries_rec.attribute;
844
845 temp_query_for varchar2(4);
846 temp_context_name varchar2(30);
847 temp_context_attribute varchar2(30);
848 l_denorm_queries_rec denorm_queries_rec_type;
849
850 CURSOR chkexists(p_query_for IN varchar2,p_context_name IN varchar2,p_context_attribute IN varchar2) IS
851 select query_for,context,attribute from ozf_denorm_queries
852 where query_for=p_query_for and context=p_context_name
853 and attribute=p_context_attribute;
854 BEGIN
855 /*
856 OPEN chkexists(l_query_for,l_context_name,l_context_attribute);
857 FETCH chkexists INTO temp_query_for,temp_context_name,temp_context_attribute;
858 IF chkexists%NOTFOUND THEN
859 CLOSE chkexists;
860 X_RETURN_STATUS:='S';
861 ELSE
862 OZF_Utility_PVT.debug_message('This record already exists');
863 x_return_status:='E';
864 FND_MESSAGE.set_name('OZF', 'OZF_DENORM_QUERIES_EXISTS');
865 FND_MSG_PUB.add;
866 END IF;
867 */
868 NULL;
869 END check_denorm_queries_record;
870
871
872 ---------------------------------------------------------------------
873 -- PROCEDURE
874 -- init_denorm_queries_rec
875 --
876 -- HISTORY
877 -- 11/23/1999 pmothuku Create.
878 ---------------------------------------------------------------------
879 PROCEDURE init_denorm_queries_rec(
880 x_denorm_queries_rec OUT NOCOPY denorm_queries_rec_type
881 )
882 IS
883 BEGIN
884
885 RETURN;
886 END init_denorm_queries_rec;
887
888
889 ---------------------------------------------------------------------
890 -- PROCEDURE
891 -- complete_denorm_queries_rec
892 --
893 -- HISTORY
894 -- pmothuku
895 ---------------------------------------------------------------------
896 PROCEDURE complete_denorm_queries_rec(
897 p_denorm_queries_rec IN denorm_queries_rec_type,
898 x_complete_rec OUT NOCOPY denorm_queries_rec_type
899 )
900 IS
901
902 CURSOR c_denorm_queries IS
903 SELECT *
904 FROM ozf_denorm_queries
905 WHERE denorm_query_id = p_denorm_queries_rec.denorm_query_id;
906
907
908 l_denorm_queries_rec c_denorm_queries%ROWTYPE;
909
910 BEGIN
911 OZF_UTILITY_PVT.debug_message('complete_denorm_queries_rec :'|| p_denorm_queries_rec.denorm_query_id);
912
913 x_complete_rec := p_denorm_queries_rec;
914
915 OPEN c_denorm_queries;
916 FETCH c_denorm_queries INTO l_denorm_queries_rec;
917 IF c_denorm_queries%NOTFOUND THEN
918 CLOSE c_denorm_queries;
919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
920 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
921 FND_MSG_PUB.add;
922 END IF;
923 RAISE FND_API.g_exc_error;
924 END IF;
925 CLOSE c_denorm_queries;
926
927
928 IF p_denorm_queries_rec.query_for = FND_API.g_miss_char THEN
929 x_complete_rec.query_for:= NULL;
930 END IF;
931 IF p_denorm_queries_rec.query_for IS NULL THEN
932 x_complete_rec.query_for:= l_denorm_queries_rec.query_for;
933 END IF;
934
935 IF p_denorm_queries_rec.creation_date = FND_API.g_miss_date THEN
936 x_complete_rec.creation_date:= NULL;
937 END IF;
938 IF p_denorm_queries_rec.creation_date IS NULL THEN
939 x_complete_rec.creation_date:= l_denorm_queries_rec.creation_date;
940 END IF;
941 IF p_denorm_queries_rec.created_by = FND_API.g_miss_num THEN
942 x_complete_rec.created_by := NULL;
943 END IF;
944 IF p_denorm_queries_rec.created_by IS NULL THEN
945 x_complete_rec.created_by := l_denorm_queries_rec.created_by;
946 END IF;
947 IF p_denorm_queries_rec.last_update_date = FND_API.g_miss_date THEN
948 x_complete_rec.last_update_date := NULL;
949 END IF;
950 IF p_denorm_queries_rec.last_update_date IS NULL THEN
951 x_complete_rec.last_update_date := l_denorm_queries_rec.last_update_date;
952 END IF;
953 IF p_denorm_queries_rec.last_updated_by = FND_API.g_miss_num THEN
954 x_complete_rec.last_updated_by := NULL;
955 END IF;
956 IF p_denorm_queries_rec.last_updated_by IS NULL THEN
957 x_complete_rec.last_updated_by := l_denorm_queries_rec.last_updated_by;
958 END IF;
959
960 IF p_denorm_queries_rec.last_update_login = FND_API.g_miss_num THEN
961 x_complete_rec.last_update_login := NULL;
962 END IF;
963 IF p_denorm_queries_rec.last_update_login IS NULL THEN
964 x_complete_rec.last_update_login := l_denorm_queries_rec.last_update_login;
965 END IF;
966
967 IF p_denorm_queries_rec.active_flag = FND_API.g_miss_char THEN
968 x_complete_rec.active_flag := NULL;
969 END IF;
970 IF p_denorm_queries_rec.active_flag IS NULL THEN
971 x_complete_rec.active_flag := l_denorm_queries_rec.active_flag;
972 END IF;
973
974 IF p_denorm_queries_rec.context = fnd_api.g_miss_char then
975 x_complete_rec.context := NULL;
976 END IF;
977 IF p_denorm_queries_rec.context IS NULL then
978 x_complete_rec.context := l_denorm_queries_rec.context;
979 END IF;
980
981 IF p_denorm_queries_rec.attribute = FND_API.g_miss_char THEN
982 x_complete_rec.attribute := NULL;
983 END IF;
984 IF p_denorm_queries_rec.attribute IS NULL THEN
985 x_complete_rec.attribute := l_denorm_queries_rec.attribute;
986 END IF;
987 IF p_denorm_queries_rec.condition_name_column = fnd_api.g_miss_char then
988 x_complete_rec.condition_name_column := NULL;
989 END IF;
990 IF p_denorm_queries_rec.condition_name_column IS NULL then
991 x_complete_rec.condition_name_column := l_denorm_queries_rec.condition_name_column;
992 END IF;
993
994 IF p_denorm_queries_rec.condition_id_column = FND_API.g_miss_char THEN
995 x_complete_rec.condition_id_column:= NULL;
996 END IF;
997 IF p_denorm_queries_rec.condition_id_column IS NULL THEN
998 x_complete_rec.condition_id_column:= l_denorm_queries_rec.condition_id_column;
999 END IF;
1000 IF p_denorm_queries_rec.object_version_number = FND_API.g_miss_num THEN
1001 x_complete_rec.object_version_number:= NULL;
1002 END IF;
1003 IF p_denorm_queries_rec.object_version_number IS NULL THEN
1004 x_complete_rec.object_version_number:= l_denorm_queries_rec.object_version_number;
1005 END IF;
1006 END complete_denorm_queries_rec;
1007
1008
1009 PROCEDURE string_length_check(sqlst IN VARCHAR2, sArray OUT NOCOPY stringArray)
1010 IS
1011
1012 sqlstatement VARCHAR2(32000):=sqlst;
1013 lengthLimit NUMBER:=4000;
1014 startIndexTrace NUMBER:=1;
1015 sLength NUMBER:=LENGTH(sqlstatement);
1016 counter NUMBER:=1;
1017 l_string1 varchar2(4000);
1018
1019 BEGIN
1020 --dbms_output.put_line('the length,startIndex and counter is'||sLength||'next'||startIndexTrace||'next'||counter);
1021 WHILE (sLength>lengthLimit) LOOP
1022 --dbms_output.put_line('statements in while');
1023 sArray(counter):=SUBSTR(sqlstatement,startIndexTrace,4000);
1024 counter:=counter+1;
1025 startIndexTrace:=startIndexTrace+lengthLimit;
1026 sLength:=sLength-startIndexTrace;
1027 END LOOP;
1028 --l_string1:=sArray(1);
1029 --dbms_output.put_line('the statements before if-else clause ');
1030 IF(counter>1) THEN
1031 --dbms_output.put_line('the statements in if of if-else clause');
1032 sArray(counter):=SUBSTR(sqlstatement,startIndexTrace);
1033
1034 ELSE
1035 --dbms_output.put_line('the statements in else of if-else clause');
1036 --dbms_output.put_line('the INPUT STRING IS'|| sqlstatement);
1037 -- dbms_output.put_line('the SUBSTRING '||SUBSTR(sqlstatement,startIndexTrace,236));
1038 sArray(counter):=SUBSTR(sqlstatement,startIndexTrace);
1039 END IF;
1040 IF(counter<8)THEN
1041 FOR i in counter+1..8 LOOP
1042 sArray(i):='';
1043 END LOOP;
1044 END IF;
1045 --dbms_output.put_line('the statements after if-else clause ');
1046 l_string1:=sArray(1);
1047
1048 --dbms_output.put_line('the separated values are'||SUBSTR(l_string1,startIndexTrace,236));
1049
1050
1051
1052 END string_length_check;
1053
1054
1055 END Ozf_Denorm_Queries_Pvt;