[Home] [Help]
PACKAGE BODY: APPS.FUN_SEQ_UTILS
Source
1 PACKAGE BODY fun_seq_utils AS
2 /* $Header: funsqrlb.pls 120.17 2004/11/04 01:16:12 masada noship $ */
3
4 --
5 -- Global Variable
6 --
7 g_module CONSTANT VARCHAR2(30) DEFAULT 'fun.plsql.fun_seq_utils';
8 g_table_names Table_Name_Tab;
9 g_cache_size BINARY_INTEGER DEFAULT 0;
10
11 --
12 -- Constant
13 --
14 g_last_update_login CONSTANT NUMBER := 0;
15
16 --
17 -- Exceptions
18 --
19 invalid_table EXCEPTION;
20 PRAGMA EXCEPTION_INIT(invalid_table, 100);
21
22 no_seq_entity_found EXCEPTION;
23 PRAGMA EXCEPTION_INIT(no_seq_entity_found, 100);
24
25 invalid_context_type EXCEPTION;
26 invalid_event_code EXCEPTION;
27 invalid_date_type EXCEPTION;
28
29 --
30 -- Subtypes
31 --
32 SUBTYPE Debug_Loc IS VARCHAR2(1000);
33 --
34 -- PROCEDURE NAME:
35 -- show_exception
36 --
37 PROCEDURE show_exception (
38 p_routine IN VARCHAR2) IS
39 BEGIN
40 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
41 fnd_message.set_token('ROUTINE', p_routine);
42 fnd_message.set_token('ERRNO', TO_CHAR(sqlcode));
43 fnd_message.set_token('REASON',sqlerrm);
44 app_exception.raise_exception;
45 END show_exception;
46
47 --
48 -- Standard Logging Procedures
49 --
50 -- P_module:
51 -- fun.
52 -- Usage:
53 PROCEDURE Log(
54 p_level IN NUMBER,
55 p_module IN VARCHAR2,
56 p_message_text IN VARCHAR2) IS
57 BEGIN
58 IF (p_level >= fnd_log.g_current_runtime_level) THEN
59 fnd_log.string(log_level => p_level,
60 module => p_module,
61 message => p_message_text);
62 END IF;
63 END Log;
64
65 --
66 -- Log messages at Statement level
67 -- Severity:
68 -- 1
69 -- Usage:
70 -- Low level detailed messages
71 -- Example:
72 -- Copying buffer x to y
73 --
74 PROCEDURE Log_Statement(
75 p_module IN VARCHAR2,
76 p_message_text IN VARCHAR2) IS
77 BEGIN
78 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
79 log(p_level => fnd_log.level_statement,
80 p_module => p_module,
81 p_message_text => p_message_text);
82 END IF;
83 END Log_Statement;
84 --
85 -- Log messages at Procedure level
86 -- Severity:
87 -- 2
88 -- Usage:
89 -- API level flow of Applicatoin and
90 -- important events
91 -- Example:
92 -- Calling an API, Returning from an API, and so on.
93 --
94 PROCEDURE Log_Procedure(
95 p_module IN VARCHAR2,
96 p_message_text IN VARCHAR2) IS
97 BEGIN
98 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
99 log(p_level => fnd_log.level_procedure,
100 p_module => p_module,
101 p_message_text => p_message_text);
102 END IF;
103 END Log_Procedure;
104 --
105 -- Log messages at Event level
106 -- Severity:
107 -- 3
108 -- Usage:
109 -- A significant milestone in the normal execution path
110 -- of an application
111 -- Example:
112 -- User Authenticated, Starting Business Transaction, and so on.
113 --
114 PROCEDURE Log_Event(
115 p_module IN VARCHAR2,
116 p_message_text IN VARCHAR2) IS
117 BEGIN
118 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level) THEN
119 log(p_level => fnd_log.level_event,
120 p_module => p_module,
121 p_message_text => p_message_text);
122 END IF;
123 END Log_Event;
124 --
125 -- Log messages at Exception level
126 -- Severity:
127 -- 4
128 -- Usage:
129 -- Internal Software failure condition
130 -- Example:
131 -- Detailed Exception Stack Trace, Nullpointer, Rntime Exception,
132 -- and so on.
133 --
134 PROCEDURE Log_Exception(
135 p_module IN VARCHAR2,
136 p_message_text IN VARCHAR2) IS
137 BEGIN
138 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
139 log(p_level => fnd_log.level_exception,
140 p_module => p_module,
141 p_message_text => p_message_text);
142 END IF;
143 END Log_Exception;
144 --
145 -- Log messages at Error level
146 -- Severity:
147 -- 5
148 -- Usage:
149 -- External Condition that causes a business rule/component failure.
150 -- Example:
151 -- Authentication failure, Invalid input value, and so on.
152 --
153 PROCEDURE Log_Error(
154 p_module IN VARCHAR2,
155 p_message_text IN VARCHAR2) IS
156 BEGIN
157 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
158 log(p_level => fnd_log.level_error,
159 p_module => p_module,
160 p_message_text => p_message_text);
161 END IF;
162 END Log_Error;
163 --
164 -- Log messages at Error level
165 -- Severity:
166 -- 6
167 -- Usage:
168 -- Error that prevents complete system execute, System Alerts
169 -- Example:
170 -- Required file not found, Database failure in placing an order,
171 -- and so on.
172 --
173 PROCEDURE Log_Unexpected(
174 p_module IN VARCHAR2,
175 p_message_text IN VARCHAR2) IS
176 BEGIN
177 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
178 log(p_level => fnd_log.level_unexpected,
179 p_module => p_module,
180 p_message_text => p_message_text);
181 END IF;
182 END Log_Unexpected;
183
184 --
185 -- PROCEDURE NAME:
186 -- create_entity
187 -- DESCRIPTION:
188 -- Register Sequence Entity
189 -- INPUT:
190 -- - p_application_id
191 -- - p_table_name
192 -- - p_entity_name
193 -- - p_mode
194 -- Valid values are SEED or CUSTOM.
195 -- If the value is SEED, user id becomes 1.
196 --
197 PROCEDURE create_entity (
198 p_application_id IN NUMBER,
199 p_table_name IN VARCHAR2,
200 p_entity_name IN VARCHAR2) IS
201
202 l_user_id NUMBER DEFAULT 1;
203 l_debug_loc Debug_Loc;
204
205 BEGIN
206 --
207 -- Initialize
208 --
209 l_debug_loc := 'create_entity';
210
211 --
212 -- Check if the combination of application id and table name is valid
213 -- If invalid, exception is raised within is_table_name_valid.
214 --
215 IF NOT is_table_name_valid (
216 p_application_id => p_application_id,
217 p_table_name => p_table_name)
218 THEN
219 l_debug_loc := l_debug_loc || ' -> '||'is_table_name_valid';
220 RAISE invalid_table;
221 END IF;
222 --
223 -- Insert Sequence Entity Information into fun_seq_entities
224 --
225 l_debug_loc := l_debug_loc || '->' || 'insert into fun_seq_entities';
226 --
227 INSERT INTO fun_seq_entities (
228 application_id,
229 table_name,
230 entity_name,
231 created_by,
232 creation_date,
233 last_updated_by,
234 last_update_date,
235 last_update_login)
236 VALUES (
237 p_application_id,
238 p_table_name,
239 p_entity_name,
240 l_user_id,
241 sysdate,
242 l_user_id,
243 sysdate,
244 g_last_update_login);
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 show_exception (
249 p_routine => l_debug_loc);
250 END create_entity;
251
252 PROCEDURE create_sequencing_rule (
253 p_application_id IN NUMBER,
254 p_table_name IN VARCHAR2,
255 p_context_type IN VARCHAR2,
256 p_event_code IN VARCHAR2,
257 p_date_type IN VARCHAR2,
258 p_flex_context_code IN VARCHAR2) IS
259 l_user_id NUMBER DEFAULT 1;
260 l_debug_loc Debug_Loc;
261 BEGIN
262 --
263 -- Initialize
264 --
265 l_debug_loc := 'create_sequencing_rule';
266
267 --
268 -- 1. Check if Sequence Entity Does exist for given application id
269 -- and table name combination.
270 --
271 IF NOT is_seq_entity_registered (
272 p_application_id => p_application_id,
273 p_table_name => p_table_name)
274 THEN
275 l_debug_loc := l_debug_loc || ' -> '|| 'is_seq_entity_registered';
276 RAISE no_seq_entity_found;
277 END IF;
278 --
279 -- 2. Check if context type is valid
280 --
281 IF NOT is_context_type_valid (
282 p_context_type => p_context_type)
283 THEN
284 l_debug_loc := l_debug_loc || ' -> '|| 'is_context_type_valid';
285 RAISE invalid_context_type;
286 END IF;
287 --
288 -- 3. Check if sequence event is valid
289 --
290 IF NOT is_event_valid (
291 p_event => p_event_code)
292 THEN
293 l_debug_loc := l_debug_loc || ' -> '|| 'is_event_valid';
294 RAISE invalid_event_code;
295 END IF;
296 --
297 -- 4. Check if sequence event is valid
298 --
299 IF NOT is_date_type_valid (
300 p_date_type => p_date_type)
301 THEN
302 l_debug_loc := l_debug_loc || ' -> '|| 'is_date_type_valid';
303 RAISE invalid_date_type;
304 END IF;
305 --
306 -- Insert records into fun_seq_rules
307 --
308 l_debug_loc := l_debug_loc || '->' || 'insert into fun_seq_rules';
309 --
310 INSERT INTO fun_seq_rules (
311 application_id,
312 table_name,
313 context_type,
314 event_code,
315 date_type,
316 flex_context_code,
317 created_by,
318 creation_date,
319 last_updated_by,
320 last_update_date,
321 last_update_login)
322 VALUES (
323 p_application_id,
324 p_table_name,
325 p_context_type,
326 p_event_code,
327 p_date_type,
328 p_flex_context_code,
329 l_user_id,
330 sysdate,
331 l_user_id,
332 sysdate,
333 g_last_update_login);
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 show_exception (
338 p_routine => l_debug_loc);
339 END create_sequencing_rule;
340
341 PROCEDURE delete_entity (
342 p_application_id IN NUMBER,
343 p_table_name IN VARCHAR2) IS
344 l_debug_loc Debug_Loc;
345 BEGIN
346 --
347 -- Initialize
348 --
349 l_debug_loc := 'delete_entity';
350
351 --
352 -- 1. Check if Sequence Entity Does exist for given application id
353 -- and table name combination.
354 --
355 IF NOT is_seq_entity_registered (
356 p_application_id => p_application_id,
357 p_table_name => p_table_name)
358 THEN
359 l_debug_loc := l_debug_loc || ' -> '|| 'is_seq_entity_registered';
360 RAISE no_seq_entity_found;
361 END IF;
362 --
363 -- Delete Sequence Rules
364 --
365 DELETE
366 FROM fun_seq_rules sr
367 WHERE sr.application_id = p_application_id
368 AND sr.table_name = p_table_name;
369
370 --
371 -- Delete Sequence Entity
372 --
373 DELETE
374 FROM fun_seq_entities se
375 WHERE se.application_id = p_application_id
376 AND se.table_name = p_table_name;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 show_exception (
381 p_routine => l_debug_loc);
382 END;
383
384 PROCEDURE delete_sequencing_rule (
385 p_application_id IN NUMBER,
386 p_table_name IN VARCHAR2,
387 p_context_type IN VARCHAR2,
388 p_event_code IN VARCHAR2,
389 p_date_type IN VARCHAR2) IS
390
391 l_debug_loc Debug_Loc;
392 BEGIN
393 --
394 -- Initialize
395 --
396 l_debug_loc := 'delete_sequencing_rule';
397 --
398 -- Check if Sequence Entity Does exist for given application id
399 -- and table name combination.
400 --
401 IF NOT is_seq_entity_registered (
402 p_application_id => p_application_id,
403 p_table_name => p_table_name)
404 THEN
405 l_debug_loc := l_debug_loc || ' -> '|| 'is_seq_entity_registered';
406 RAISE no_seq_entity_found;
407 END IF;
408
409 --
410 -- 2. Check if context type is valid
411 --
412 IF NOT is_context_type_valid (
413 p_context_type => p_context_type)
414 THEN
415 l_debug_loc := l_debug_loc || ' -> '|| 'is_context_type_valid';
416 RAISE invalid_context_type;
417 END IF;
418 --
419 -- 3. Check if sequence event is valid
420 --
421 IF NOT is_event_valid (
422 p_event => p_event_code)
423 THEN
424 l_debug_loc := l_debug_loc || ' -> '|| 'is_event_valid';
425 RAISE invalid_event_code;
426 END IF;
427 --
428 -- 4. Check if sequence control date type is valid
429 --
430 IF NOT is_date_type_valid (
431 p_date_type => p_date_type)
432 THEN
433 l_debug_loc := l_debug_loc || ' -> '|| 'is_date_type_valid';
434 RAISE invalid_date_type;
435 END IF;
436
437 DELETE
438 FROM fun_seq_rules sr
439 WHERE sr.application_id = p_application_id
440 AND sr.table_name = p_table_name
441 AND sr.context_type = p_context_type
442 AND sr.event_code = p_event_code
443 AND sr.date_type = p_date_type;
444 EXCEPTION
445 WHEN OTHERS THEN
446 show_exception (
447 p_routine => l_debug_loc);
448 END delete_sequencing_rule;
449
450 PROCEDURE update_entity (
451 p_application_id IN NUMBER,
452 p_table_name IN VARCHAR2,
453 p_entity_name IN VARCHAR2) IS
454
455 l_user_id NUMBER DEFAULT 1;
456 no_seq_entity_found EXCEPTION;
457 l_debug_loc Debug_Loc;
458
459 BEGIN
460 --
461 -- Initialize
462 --
463 l_debug_loc := 'update_entity';
464 --
465 -- Check if Sequence Entity Does exist for given application id
466 -- and table name combination.
467 --
468 IF NOT is_seq_entity_registered (
469 p_application_id => p_application_id,
470 p_table_name => p_table_name)
471 THEN
472 l_debug_loc := l_debug_loc || ' -> '|| 'is_seq_entity_registered';
473 RAISE no_seq_entity_found;
474 END IF;
475
476 UPDATE fun_seq_entities se
477 SET se.entity_name = p_entity_name,
478 se.last_updated_by = l_user_id,
479 se.last_update_date = sysdate,
480 se.last_update_login = l_user_id
481 WHERE se.application_id = p_application_id
482 AND se.table_name = p_table_name;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 show_exception (
487 p_routine => l_debug_loc);
488 END update_entity;
489
490 --
491 -- Supportive Procedures / Functions
492 --
493 FUNCTION is_context_type_valid (
494 p_context_type IN VARCHAR2) RETURN BOOLEAN IS
495 BEGIN
496 IF is_lookup_valid (
497 p_lookup_type => 'FUN_SEQ_CONTEXT_TYPE',
498 p_lookup_code => p_context_type)
499 THEN
500 RETURN (TRUE);
501 ELSE
502 RETURN (FALSE);
503 END IF;
504 END is_context_type_valid;
505
506 FUNCTION is_event_valid (
507 p_event IN VARCHAR2) RETURN BOOLEAN IS
508 BEGIN
509 IF is_lookup_valid (
510 p_lookup_type => 'FUN_SEQ_EVENT',
511 p_lookup_code => p_event)
512 THEN
513 RETURN (TRUE);
514 ELSE
515 RETURN (FALSE);
516 END IF;
517 END is_event_valid;
518
519 FUNCTION is_date_type_valid (
520 p_date_type IN VARCHAR2) RETURN BOOLEAN IS
521 BEGIN
522 IF is_lookup_valid (
523 p_lookup_type => 'FUN_SEQ_DATE_TYPE',
524 p_lookup_code => p_date_type)
525 THEN
526 RETURN (TRUE);
527 ELSE
528 RETURN (FALSE);
529 END IF;
530 END;
531
532 FUNCTION is_lookup_valid (
533 p_lookup_type IN VARCHAR2,
534 p_lookup_code IN VARCHAR2) RETURN BOOLEAN IS
535
536 l_dummy VARCHAR2(1);
537 BEGIN
538 SELECT 'x'
539 INTO l_dummy
540 FROM fnd_lookups fl
541 WHERE fl.lookup_type = p_lookup_type
542 AND fl.lookup_code = p_lookup_code;
543 RETURN (TRUE);
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 RETURN (FALSE);
547 END;
548 --
549 -- FUNCTION NAME:
550 -- is_table_name_valid
551 -- DESCRIPTION:
552 -- Check if table name is valid.
553 --
554 FUNCTION is_table_name_valid(
555 p_application_id IN NUMBER,
556 p_table_name IN VARCHAR2) RETURN BOOLEAN IS
557
558 l_tb_rec Table_Name_Rec;
559 l_cache_index BINARY_INTEGER;
560 l_defined BOOLEAN;
561
562 BEGIN
563 l_tb_rec.application_id := p_application_id;
564 l_tb_rec.table_name := p_table_name;
565
566 l_cache_index := find_table_name_in_cache(l_tb_rec);
567
568 --
569 -- Return True if a matching record is found in the cache
570 --
571 IF l_cache_index < g_cache_size THEN
572 RETURN (TRUE);
573 --
574 -- If a matching record is not found, retrieve it from
575 -- the database table
576 --
577 ELSE
578 l_defined := find_table_name_in_db(l_tb_rec);
579 IF (l_defined) THEN
580 g_table_names(g_cache_size).application_id := l_tb_rec.application_id;
581 g_table_names(g_cache_size).table_name := l_tb_rec.table_name;
582 g_cache_size := g_cache_size + 1;
583 RETURN (TRUE);
584 ELSE
585 RETURN (FALSE);
586 END IF;
587 END IF;
588 RETURN (TRUE);
589 END is_table_name_valid;
590
591
592 --
593 -- FUNCTION NAME: find_table_name_in_cache
594 -- DESCRIPTION:
595 -- Called from is_table_name_valid
596 -- - If there is no record in the cache, return 0.
597 -- - If there is a record in the cache but no matching record is found,
598 -- return the cache size (
599 -- - If there is a record in the cache and a matching record is found,
600 -- return the index
601 --
602 FUNCTION find_table_name_in_cache (
603 p_table_name_rec IN Table_Name_Rec) RETURN BINARY_INTEGER IS
604
605 l_tb_rec Table_Name_Rec;
606 l_cache_index BINARY_INTEGER;
607 l_found BOOLEAN;
608
609 BEGIN
610 l_tb_rec := p_table_name_rec;
611 l_cache_index := 0;
612 l_found := FALSE;
613
614 WHILE (l_cache_index < g_cache_size) AND (NOT l_found) LOOP
615 IF g_table_names(l_cache_index).application_id = l_tb_rec.application_id
616 AND g_table_names(l_cache_index).table_name = l_tb_rec.table_name THEN
617 l_found := TRUE;
618 ELSE
619 l_cache_index := l_cache_index + 1;
620 END IF;
621 END LOOP;
622
623 RETURN l_cache_index;
624 END find_table_name_in_cache;
625
626 --
627 -- FUNCTION NAME: find_table_name_in_db
628 --
629 --
630 FUNCTION find_table_name_in_db (
631 p_table_name_rec IN Table_Name_Rec) RETURN BOOLEAN IS
632
633 l_dummy VARCHAR2(1);
634
635 CURSOR c_table IS
636 SELECT
637 'x'
638 FROM
639 fnd_tables ft
640 WHERE
641 ft.application_id = p_table_name_rec.application_id
642 AND ft.table_name = p_table_name_rec.table_name;
643
644 BEGIN
645 OPEN c_table;
646 FETCH c_table INTO l_dummy;
647 IF (c_table%NOTFOUND) THEN
648 RETURN FALSE;
649 ELSE
650 RETURN TRUE;
651 END IF;
652
653 CLOSE c_table;
654 END find_table_name_in_db;
655
656 FUNCTION is_seq_entity_registered (
657 p_application_id IN NUMBER,
658 p_table_name IN VARCHAR2) RETURN BOOLEAN IS
659
660 l_dummy VARCHAR2(1);
661 BEGIN
662 SELECT 'x'
663 INTO l_dummy
664 FROM fun_seq_entities se
665 WHERE se.application_id = p_application_id
666 AND se.table_name = p_table_name;
667 RETURN (TRUE);
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 RETURN (FALSE);
671 END is_seq_entity_registered;
672
673
674 --
675 -- Create Sequencing Setup Data
676 --
677 PROCEDURE create_setup_data (
678 p_sequence_rec IN sequence_rec_type,
679 p_version_rec IN version_rec_type,
680 p_context_rec IN context_rec_type,
681 p_assignment_rec IN assignment_rec_type,
682 p_owner IN VARCHAR2,
683 p_last_update_date IN VARCHAR2,
684 p_custom_mode IN VARCHAR2) IS
685
686 l_seq_header_id fun_seq_headers.seq_header_id%TYPE;
687 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
688 BEGIN
689 --
690 -- Delete Existing Records if their status is New
691 --
692 -- delete_sequence(p_header_name => p_sequence_rec.header_name);
693 -- delete_context (p_context_name => p_context_rec.name);
694
695 --
696 -- Create Sequence Header
697 --
698 create_sequence (
699 p_sequence_rec => p_sequence_rec,
700 p_owner => p_owner,
701 p_last_update_date => p_last_update_date,
702 p_custom_mode => p_custom_mode,
703 x_seq_header_id => l_seq_header_id);
704
705 --
706 -- Create Version
707 --
708 create_version (
709 p_seq_header_id => l_seq_header_id,
710 p_header_name => p_sequence_rec.header_name,
711 p_version_rec => p_version_rec,
712 p_owner => p_owner,
713 p_last_update_date => p_last_update_date,
714 p_custom_mode => p_custom_mode);
715 --
716 -- Create Sequencing Context
717 --
718 create_context (
719 p_context_rec => p_context_rec,
720 p_owner => p_owner,
721 p_last_update_date => p_last_update_date,
722 p_custom_mode => p_custom_mode,
723 x_seq_context_id => l_seq_context_id);
724
725 --
726 -- Create Assignment
727 --
728 create_assignment (
729 p_seq_context_id => l_seq_context_id,
730 p_seq_header_id => l_seq_header_id,
731 p_assignment_rec => p_assignment_rec,
732 p_owner => p_owner,
733 p_last_update_date => p_last_update_date,
734 p_custom_mode => p_custom_mode);
735 END create_setup_data;
736
737 PROCEDURE create_sequence (
738 p_sequence_rec IN sequence_rec_type,
739 p_owner IN VARCHAR2,
740 p_last_update_date IN VARCHAR2,
741 p_custom_mode IN VARCHAR2,
742 x_seq_header_id OUT NOCOPY NUMBER) IS
743
744 l_seq_header_id NUMBER;
745 f_luby NUMBER; -- entity owner in file
746 f_ludate DATE; -- entity update date in file
747 db_luby NUMBER; -- entity owner in db
748 db_ludate DATE; -- entity update date in db
749 BEGIN
750 f_luby := fnd_load_util.owner_id(p_owner);
751 f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
752 sysdate);
753 BEGIN
754 --
755 -- Retrieve WHO columns from existing Sequence.
756 --
757 SELECT seq_header_id,
758 last_updated_by,
759 last_update_date
760 INTO x_seq_header_id,
761 db_luby,
762 db_ludate
763 FROM fun_seq_headers
764 WHERE header_name = p_sequence_rec.header_name;
765
766 --
767 -- Update Description if allowed
768 --
769 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
770 db_ludate, p_custom_mode)) THEN
771 UPDATE fun_seq_headers
772 SET description = p_sequence_rec.description,
773 last_updated_by = f_luby,
774 last_update_date = f_ludate,
775 last_update_login = 0
776 WHERE seq_header_id = x_seq_header_id;
777
778 END IF;
779
780 --
781 -- Insert a record
782 --
783 EXCEPTION
784 WHEN no_data_found THEN
785 INSERT
786 INTO fun_seq_headers (
787 seq_header_id,
788 header_name,
789 gapless_flag,
790 description,
791 obsolete_flag,
792 object_version_number,
793 last_update_date,
794 last_updated_by,
795 creation_date,
796 created_by,
797 last_update_login)
798 VALUES (
799 fun_seq_headers_s.NEXTVAL, -- Sequence Header Id
800 p_sequence_rec.header_name, -- Name
801 p_sequence_rec.gapless, -- Type (Gapless)
802 p_sequence_rec.description, -- Description
803 'N', -- Obsolete Flag
804 1, -- Object Version Number
805 f_ludate, -- Last Update Date
806 f_luby, -- Last Updated By
807 f_ludate, -- Creation Date
808 f_luby, -- Created By
809 0) -- Last Update Login
810 RETURNING seq_header_id INTO x_seq_header_id;
811 END;
812 END create_sequence;
813
814 PROCEDURE create_version (
815 p_seq_header_id IN NUMBER,
816 p_header_name IN VARCHAR2,
817 p_version_rec IN version_rec_type,
818 p_owner IN VARCHAR2,
819 p_last_update_date IN VARCHAR2,
820 p_custom_mode IN VARCHAR2) IS
821
822 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
823
824 l_module CONSTANT VARCHAR2(100) DEFAULT g_module || '.' || 'create_version';
825 f_luby NUMBER; -- entity owner in file
826 f_ludate DATE; -- entity update date in file
827 db_luby NUMBER; -- entity owner in db
828 db_ludate DATE; -- entity update date in db
829
830 BEGIN
831 f_luby := fnd_load_util.owner_id(p_owner);
832 f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
833 sysdate);
834 BEGIN
835 --
836 -- Retrieve WHO columns from existing Sequence Version.
837 --
838 SELECT last_updated_by,
839 last_update_date
840 INTO db_luby,
841 db_ludate
842 FROM fun_seq_versions
843 WHERE seq_header_id = p_seq_header_id
844 AND version_name = p_version_rec.version_name;
845 --
846 -- Update Version Name
847 --
848 -- IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
849 -- db_ludate, p_custom_mode)) THEN
850 -- NULL;
851 -- END IF;
852
853 EXCEPTION
854 WHEN no_data_found THEN
855 INSERT
856 INTO fun_seq_versions (
857 seq_version_id,
858 seq_header_id,
859 version_name,
860 header_name,
861 initial_value,
862 start_date,
863 end_date,
864 current_value,
865 use_status_code,
866 object_version_number,
867 last_update_date,
868 last_updated_by,
869 creation_date,
870 created_by,
871 last_update_login)
872 VALUES(
873 fun_seq_versions_s.NEXTVAL, -- Sequence Version Id
874 p_seq_header_id,
875 p_version_rec.version_name,
876 p_header_name,
877 p_version_rec.initial_value,
878 TRUNC(p_version_rec.start_date),
879 p_version_rec.end_date,
880 p_version_rec.current_value,
881 'NEW',
882 1, -- Object Version Number
883 f_ludate, -- Last Update Date
884 f_luby, -- Last Updated By
885 f_ludate, -- Creation Date
886 f_luby, -- Created By
887 0)
888 RETURNING seq_version_id INTO l_seq_version_id;
889
890 --
891 -- Create Database Sequence
892 --
893 create_db_sequence (
894 p_seq_version_id => l_seq_version_id,
895 p_initial_value => p_version_rec.initial_value);
896 END;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 --
901 -- Logging
902 --
903 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
904 fnd_log.string(
905 log_level => fnd_log.level_exception,
906 module => l_module,
907 message =>
908 'p_seq_header_id: ' || p_seq_header_id || ', ' ||
909 'p_header_name: ' || p_header_name || ', ' ||
910 'p_version_rec.version_name: ' || p_version_rec.version_name || ', ' ||
911 'p_version_rec.initial_value: '|| p_version_rec.initial_value || ', ' ||
912 'p_version_rec.current_value: '|| p_version_rec.current_value || ', ' ||
913 'p_version_rec.start_date: ' || p_version_rec.start_date || ', ' ||
914 'p_version_rec.end_date: ' || p_version_rec.end_date || ', ' ||
915 'SQLERRM: ' || SQLERRM);
916 END IF;
917 --
918 -- Raise Exception
919 --
920 show_exception('fun_seq_utils.create_version');
921 END create_version;
922
923 PROCEDURE recreate_version IS
924 l_header_name fun_seq_versions.header_name%TYPE;
925 l_seq_header_id fun_seq_versions.seq_header_id%TYPE;
926 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
927 l_obs_version_rec version_rec_type;
928 l_new_version_rec version_rec_type;
929 l_max_number fun_seq_versions.initial_value%TYPE;
930
931 l_module CONSTANT VARCHAR2(100) DEFAULT
932 g_module || '.' || 'recreate_version';
933 BEGIN
934 l_header_name := '$SEQUENCE$.INTERCOMPANY';
935 l_max_number := get_max_number;
936 --
937 -- Recreate Versions when there exists one or more
938 -- numeric trx numbers stored in FUN_TRX_BATCHES.
939 --
940 IF l_max_number IS NOT NULL THEN
941 --
942 -- Lock the transaction table to prevent the creation of new records
943 -- during the recreation process.
944 --
945 LOCK TABLE fun_trx_batches IN EXCLUSIVE MODE NOWAIT;
946 --
947 -- Retrieve an active Version information
948 --
949 get_active_version (
950 p_header_name => l_header_name,
951 x_seq_header_id => l_seq_header_id,
952 x_seq_version_id => l_seq_version_id,
953 x_version_rec => l_obs_version_rec);
954 --
955 -- Obsolete the active Version
956 --
957 obsolete_version (p_seq_version_id => l_seq_version_id);
958 --
959 -- Build New Version Information
960 --
961 l_new_version_rec.version_name := 'V' ||
962 (TO_NUMBER(LTRIM(l_obs_version_rec.version_name,'V')) + 1);
963 l_new_version_rec.initial_value := l_max_number + 1;
964 l_new_version_rec.current_value := null;
965 l_new_version_rec.start_date := sysdate;
966 l_new_version_rec.end_date := null;
967 --
968 -- Create a new Version
969 -- Note: DDL (Create Sequence) is issued within this procedure.
970 -- i.e. implicit commit is issued and lock is released automatically.
971 --
972 create_version (
973 p_seq_header_id => l_seq_header_id,
974 p_header_name => l_header_name,
975 p_version_rec => l_new_version_rec,
976 p_owner => 'USER',
977 p_last_update_date => fnd_date.date_to_canonical(SYSDATE),
978 p_custom_mode => 'FORCE');
979
980 END IF;
981 EXCEPTION
982 WHEN OTHERS THEN
983 --
984 -- If the system cannot lock the table, display the message to the user.
985 --
986 IF SQLCODE = -54 THEN
987 IF fnd_log.level_error>= fnd_log.g_current_runtime_level THEN
988
989 fnd_message.set_name('FUN','FUN_SEQ_IC_TRX_LOCKED');
990 fnd_log.message(
991 log_level => fnd_log.level_error,
992 module => l_module,
993 pop_message => FALSE); -- Displayed to the user layer
994 fnd_message.raise_error;
995 END IF;
996 --
997 -- Logging
998 --
999 ELSE
1000 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1001 fnd_log.string(
1002 log_level => fnd_log.level_exception,
1003 module => l_module,
1004 message => 'SQLERRM: ' || SQLERRM);
1005 END IF;
1006 END IF;
1007 --
1008 -- Raise Exception
1009 --
1010 app_exception.raise_exception;
1011 END recreate_version;
1012
1013 PROCEDURE create_db_sequence (
1014 p_seq_version_id IN NUMBER,
1015 p_initial_value IN NUMBER) IS
1016
1017 l_fnd_user fnd_oracle_userid.oracle_username%TYPE;
1018 l_db_sequence_name VARCHAR2(30);
1019 l_sql_stmt VARCHAR2(2000);
1020
1021 l_module CONSTANT VARCHAR2(100) DEFAULT
1022 g_module || '.' || 'create_db_sequence';
1023 BEGIN
1024 --
1025 -- Get AOL User Name
1026 --
1027 SELECT MIN(ou.oracle_username) -- In case for multiple installation
1028 INTO l_fnd_user
1029 FROM fnd_product_installations pi,
1030 fnd_oracle_userid ou
1031 WHERE ou.oracle_id = pi.oracle_id
1032 AND application_id = 0;
1033
1034 --
1035 -- Construct SQL statement
1036 --
1037 l_db_sequence_name := 'FUN_SEQ_S' || p_seq_version_id;
1038 l_sql_stmt := 'CREATE SEQUENCE ' ||l_db_sequence_name||
1039 ' MINVALUE 1 ' ||
1040 ' START WITH ' || p_initial_value ||
1041 ' NOCACHE';
1042 --
1043 -- Register the Sequence to dictionary
1044 --
1045 ad_ddl.do_ddl(l_fnd_user, 'FUN', ad_ddl.create_sequence, l_sql_stmt, l_db_sequence_name);
1046
1047 --
1048 -- Update Database Sequence column of fun_seq_versions
1049 --
1050 UPDATE fun_seq_versions
1051 SET db_sequence_name = l_db_sequence_name
1052 WHERE seq_version_id = p_seq_version_id;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 --
1056 -- Logging
1057 --
1058 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1059 fnd_log.string(
1060 log_level => fnd_log.level_exception,
1061 module => l_module,
1062 message => 'p_seq_version_id: ' || p_seq_version_id || ', ' ||
1063 'p_initial_value: ' || p_initial_value || ', ' ||
1064 'SQLERRM: ' || SQLERRM);
1065 END IF;
1066 --
1067 -- Raise Exception
1068 --
1069 app_exception.raise_exception;
1070 END create_db_sequence;
1071
1072 PROCEDURE create_context (
1073 p_context_rec IN context_rec_type,
1074 p_owner IN VARCHAR2,
1075 p_last_update_date IN VARCHAR2,
1076 p_custom_mode IN VARCHAR2,
1077 x_seq_context_id OUT NOCOPY NUMBER) IS
1078
1079 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
1080 f_luby NUMBER; -- entity owner in file
1081 f_ludate DATE; -- entity update date in file
1082 db_luby NUMBER; -- entity owner in db
1083 db_ludate DATE; -- entity update date in db
1084 BEGIN
1085 f_luby := fnd_load_util.owner_id(p_owner);
1086 f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
1087 sysdate);
1088 BEGIN
1089 --
1090 -- Retrieve WHO columns from existing Sequence Version.
1091 --
1092 SELECT seq_context_id,
1093 last_updated_by,
1094 last_update_date
1095 INTO
1096 x_seq_context_id,
1097 db_luby,
1098 db_ludate
1099 FROM fun_seq_contexts
1100 WHERE application_id = p_context_rec.application_id
1101 AND table_name = p_context_rec.table_name
1102 AND context_type = p_context_rec.context_type
1103 AND context_value = p_context_rec.context_value
1104 AND event_code = p_context_rec.event_code
1105 AND inactive_date IS NULL;
1106 --
1107 -- Update Sequencing Context Name
1108 --
1109 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1110 db_ludate, p_custom_mode)) THEN
1111 UPDATE fun_seq_contexts
1112 SET name = p_context_rec.NAME
1113 WHERE seq_context_id = x_seq_context_id
1114 AND inactive_date IS NULL;
1115 END IF;
1116
1117 EXCEPTION
1118 WHEN no_data_found THEN
1119 INSERT
1120 INTO fun_seq_contexts (
1121 seq_context_id,
1122 application_id,
1123 table_name,
1124 context_type,
1125 context_value,
1126 event_code,
1127 date_type,
1128 NAME,
1129 require_assign_flag,
1130 obsolete_flag,
1131 inactive_date,
1132 object_version_number,
1133 last_update_date,
1134 last_updated_by,
1135 creation_date,
1136 created_by,
1137 last_update_login)
1138 VALUES (
1139 fun_seq_contexts_s.NEXTVAL,
1140 p_context_rec.application_id,
1141 p_context_rec.table_name,
1142 p_context_rec.context_type,
1143 p_context_rec.context_value,
1144 p_context_rec.event_code,
1145 p_context_rec.date_type,
1146 p_context_rec.name,
1147 p_context_rec.require_assign_flag,
1148 'N', -- Obsolete Flag
1149 NULL, -- Inactive Date
1150 1,
1151 f_ludate, -- Last Update Date
1152 f_luby, -- Last Updated By
1153 f_ludate, -- Creation Date
1154 f_luby, -- Created By
1155 0)
1156 RETURNING seq_context_id INTO x_seq_context_id;
1157 END;
1158 END create_context;
1159
1160 --
1161 -- Do not use for GL or XLA
1162 --
1163 PROCEDURE create_assignment (
1164 p_seq_context_id IN NUMBER,
1165 p_seq_header_id IN NUMBER,
1166 p_assignment_rec IN assignment_rec_type,
1167 p_owner IN VARCHAR2,
1168 p_last_update_date IN VARCHAR2,
1169 p_custom_mode IN VARCHAR2) IS
1170
1171 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
1172 f_luby NUMBER; -- entity owner in file
1173 f_ludate DATE; -- entity update date in file
1174 db_luby NUMBER; -- entity owner in db
1175 db_ludate DATE; -- entity update date in db
1176 BEGIN
1177 f_luby := fnd_load_util.owner_id(p_owner);
1178 f_ludate := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'),
1179 sysdate);
1180 BEGIN
1181 --
1182 -- Retrieve WHO columns from existing Sequence Version.
1183 --
1184 SELECT assignment_id,
1185 last_updated_by,
1186 last_update_date
1187 INTO
1188 l_assignment_id,
1189 db_luby,
1190 db_ludate
1191 FROM fun_seq_assignments
1192 WHERE seq_context_id = p_seq_context_id
1193 AND use_status_code <> 'OBSOLETE';
1194 --
1195 -- Create a record
1196 --
1197 EXCEPTION
1198 WHEN no_data_found THEN
1199 INSERT
1200 INTO fun_seq_assignments (
1201 assignment_id,
1202 seq_context_id,
1203 seq_header_id,
1204 link_to_assignment_id,
1205 priority,
1206 control_attribute_structure,
1207 balance_type,
1208 journal_source,
1209 journal_category,
1210 accounting_event_type,
1211 accounting_entry_type,
1212 document_category,
1213 start_date,
1214 end_date,
1215 use_status_code,
1216 object_version_number,
1217 last_update_date,
1218 last_updated_by,
1219 creation_date,
1220 created_by,
1221 last_update_login)
1222 VALUES (
1223 fun_seq_assignments_s.NEXTVAL,
1224 p_seq_context_id,
1225 p_seq_header_id,
1226 NULL, -- Link to Assignment Id,
1227 NULL, -- Priority
1228 p_assignment_rec.control_attribute_structure,
1229 NULL, -- Balance Type
1230 NULL, -- Jounral Source
1231 NULL, -- Journal Category
1232 NULL, -- Accounting Event Type
1233 NULL, -- Accounting Entry Type
1234 NULL, -- Document Category
1235 TRUNC(p_assignment_rec.start_date),
1236 p_assignment_rec.end_date,
1237 'NEW',
1238 1,
1239 f_ludate, -- Last Update Date
1240 f_luby, -- Last Updated By
1241 f_ludate, -- Creation Date
1242 f_luby, -- Created By
1243 0);
1244 END;
1245 END create_assignment;
1246
1247 --
1248 -- Delete Sequence and its Versions
1249 -- if they are new.
1250 --
1251 PROCEDURE delete_sequence (
1252 p_header_name IN VARCHAR2) IS
1253
1254 TYPE seq_version_id_tbl_type IS TABLE OF
1255 fun_seq_versions.seq_version_id%TYPE INDEX BY BINARY_INTEGER;
1256
1257 l_seq_ver_id_tbl seq_version_id_tbl_type;
1258 l_seq_header_id fun_seq_headers.seq_header_id%TYPE;
1259 BEGIN
1260 --
1261 -- Lock Sequence Versions
1262 --
1263 SELECT sv.seq_version_id
1264 BULK COLLECT
1265 INTO l_seq_ver_id_tbl
1266 FROM fun_seq_versions sv
1267 WHERE sv.header_name = p_header_name
1268 AND sv.use_status_code = 'NEW'
1269 FOR UPDATE NOWAIT;
1270 --
1271 -- Delete Versions
1272 --
1273 FORALL i IN l_seq_ver_id_tbl.FIRST..l_seq_ver_id_tbl.LAST
1274 DELETE
1275 FROM fun_seq_versions sv
1276 WHERE sv.seq_version_id = l_seq_ver_id_tbl(i);
1277 --
1278 -- Lock Sequence Header
1279 --
1280 SELECT sh.seq_header_id
1281 INTO l_seq_header_id
1282 FROM fun_seq_headers sh
1283 WHERE sh.header_name = p_header_name
1284 AND NOT EXISTS (SELECT 1
1285 FROM fun_seq_versions sv
1286 WHERE sv.seq_header_id = sh.seq_header_id
1287 AND sv.use_status_code <> 'NEW')
1288 FOR UPDATE NOWAIT;
1289 --
1290 -- Delelete Sequence Header
1291 --
1292 DELETE
1293 FROM fun_seq_headers sh
1294 WHERE sh.seq_header_id = l_seq_header_id;
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 ROLLBACK;
1298 END delete_sequence;
1299
1300 --
1301 -- Delete Sequence Context and its Assignments
1302 -- if any of them is not used.
1303 --
1304 PROCEDURE delete_context (
1305 p_context_name IN VARCHAR2) IS
1306
1307 TYPE assign_id_tbl_type IS TABLE OF
1308 fun_seq_assignments.assignment_id%TYPE INDEX BY BINARY_INTEGER;
1309
1310 l_assign_id_tbl assign_id_tbl_type;
1311 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
1312 BEGIN
1313 --
1314 -- Lock Assignments
1315 --
1316 SELECT sa.assignment_id
1317 BULK COLLECT
1318 INTO l_assign_id_tbl
1319 FROM fun_seq_assignments sa,
1320 fun_seq_contexts sc
1321 WHERE sc.seq_context_id = sa.seq_context_id
1322 AND sc.name = p_context_name
1323 AND sa.use_status_code = 'NEW'
1324 FOR UPDATE NOWAIT;
1325 --
1326 -- Delete Versions
1327 --
1328 FORALL i IN l_assign_id_tbl.FIRST..l_assign_id_tbl.LAST
1329 DELETE
1330 FROM fun_seq_assignments sa
1331 WHERE sa.assignment_id = l_assign_id_tbl(i);
1332 --
1333 -- Lock Sequence Header
1334 --
1335 SELECT sc.seq_context_id
1336 INTO l_seq_context_id
1337 FROM fun_seq_contexts sc
1338 WHERE sc.name = p_context_name
1339 AND NOT EXISTS (SELECT 1
1340 FROM fun_seq_assignments sa
1341 WHERE sa.seq_context_id = sc.seq_context_id
1342 AND sa.use_status_code <> 'NEW')
1343 FOR UPDATE NOWAIT;
1344 --
1345 -- Delelete Sequence Header
1346 --
1347 DELETE
1348 FROM fun_seq_contexts sc
1349 WHERE sc.seq_context_id = l_seq_context_id;
1350
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 ROLLBACK;
1354 END delete_context;
1355
1356 PROCEDURE obsolete_version (
1357 p_seq_version_id IN NUMBER) IS
1358
1359 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
1360 l_module CONSTANT VARCHAR2(100) DEFAULT
1361 g_module || '.' || 'obsolete_version';
1362 BEGIN
1363
1364 SELECT sv.seq_version_id
1365 INTO l_seq_version_id
1366 FROM fun_seq_versions sv
1367 WHERE sv.seq_version_id = p_seq_version_id
1368 FOR UPDATE NOWAIT;
1369
1370 UPDATE fun_seq_versions sv
1371 SET use_status_code = 'OBSOLETE'
1372 WHERE sv.seq_version_id = l_seq_version_id;
1373
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 --
1377 -- Logging
1378 --
1379 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1380 fnd_log.string(
1381 log_level => fnd_log.level_exception,
1382 module => l_module,
1383 message => 'p_seq_version_id: ' || p_seq_version_id || ', ' ||
1384 'SQLERRM: ' || SQLERRM);
1385 END IF;
1386 --
1387 -- Raise Exception
1388 --
1389 app_exception.raise_exception;
1390 END obsolete_version;
1391
1392 PROCEDURE get_active_version (
1393 p_header_name IN VARCHAR2,
1394 x_seq_header_id OUT NOCOPY NUMBER,
1395 x_seq_version_id OUT NOCOPY NUMBER,
1396 x_version_rec OUT NOCOPY version_rec_type) IS
1397
1398 l_module CONSTANT VARCHAR2(100) DEFAULT
1399 g_module || '.' || 'get_active_version';
1400 BEGIN
1401 --
1402 -- Raise exceptions when,
1403 -- - multiple records are returned.
1404 -- Return null when,
1405 -- - no record is returned.
1406 --
1407 -- Need to obsolete 'New' Versions in case
1408 -- manual numeric numbers are recorded on
1409 -- transactions.
1410 --
1411 SELECT sv.seq_header_id,
1412 sv.seq_version_id,
1413 sv.version_name,
1414 sv.initial_value,
1415 sv.current_value,
1416 sv.start_date,
1417 sv.end_date
1418 INTO x_seq_header_id,
1419 x_seq_version_id,
1420 x_version_rec.version_name,
1421 x_version_rec.initial_value,
1422 x_version_rec.current_value,
1423 x_version_rec.start_date,
1424 x_version_rec.end_date
1425 FROM fun_seq_versions sv
1426 WHERE sv.header_name = p_header_name
1427 AND sv.use_status_code IN ('USED','NEW');
1428 EXCEPTION
1429 WHEN NO_DATA_FOUND THEN
1430 x_seq_version_id := NULL;
1431 WHEN OTHERS THEN
1432 --
1433 -- Logging
1434 --
1435 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1436 fnd_log.string(
1437 log_level => fnd_log.level_exception,
1438 module => l_module,
1439 message => 'p_header_name: ' || p_header_name || ', ' ||
1440 'SQLERRM: ' || SQLERRM);
1441 END IF;
1442 --
1443 -- Raise Exception
1444 --
1445 app_exception.raise_exception;
1446 END get_active_version;
1447
1448 FUNCTION get_max_number RETURN NUMBER IS
1449 l_max_number fun_seq_versions.initial_value%TYPE;
1450 BEGIN
1451 SELECT max(TO_NUMBER(tb.batch_number))
1452 INTO l_max_number
1453 FROM fun_trx_batches tb
1454 WHERE TRANSLATE(tb.batch_number,'0123456789','0000000000')
1455 = RPAD('0',LENGTH(tb.batch_number),'0');
1456
1457 RETURN l_max_number;
1458 EXCEPTION
1459 WHEN NO_DATA_FOUND THEN
1460 RETURN NULL;
1461 END get_max_number;
1462
1463 END fun_seq_utils;