[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCHIVE_UTILS
Source
1 package body pay_archive_utils as
2 /* $Header: pyarcutl.pkb 120.1 2011/12/21 18:19:03 pparate ship $ */
3 --
4 -------------------------------------------------------------------
5 --FUNCTION get_context_id (Private)
6 --DESCRIPTION: very simple context_id fetch with error handling.
7 -------------------------------------------------------------------
8 FUNCTION get_context_id(p_context_name IN VARCHAR2) RETURN NUMBER IS
9 --
10 l_context_id number;
11 --
12 cursor c_get_context(c_context_name varchar2) is
13 select context_id
14 from ff_contexts
15 where context_name = c_context_name;
16 --
17 begin
18 --
19 open c_get_context(p_context_name);
20 fetch c_get_context into l_context_id;
21 close c_get_context;
22 --
23 if l_context_id is null then
24 RAISE NO_DATA_FOUND;
25 end if;
26 --
27
28 return l_context_id;
29 --
30 END get_context_id;
31
32 -------------------------------------------------------------------------
33 -- FUNCTION get_number_archive_route (Private)
34 -- DESCRIPTION: Define the seeded number archive route. Check for
35 -- existance, if this does not exist, create as necessary.
36 -- This is used by the main procedure, mainly to select the apt
37 -- route_id, but on first call, will create the seeded route.
38 -------------------------------------------------------------------------
39 FUNCTION get_number_archive_route RETURN NUMBER IS
40 --
41 l_text clob;
42 l_assignment_action_context_id number;
43 l_exists varchar2(1);
44 l_route_id number;
45 --
46 cursor check_exists is
47 select route_id from ff_routes
48 where route_name = 'ARCHIVE_NUMBER_ROUTE';
49 --
50 BEGIN
51 --
52 open check_exists;
53 fetch check_exists into l_route_id;
54 close check_exists;
55 --
56 IF l_route_id is null then
57 --
58 -- Create the route, context and parameter.
59 -- First define the text.
60 --
61 l_text := 'ff_archive_items target
62 where target.user_entity_id = &U1
63 and target.context1 = &B1 /* context assignment action id */';
64 --
65 insert into ff_routes
66 (
67 route_id,
68 route_name,
69 user_defined_flag,
70 description,
71 text,
72 last_update_date,
73 last_updated_by,
74 last_update_login,
75 created_by,
76 creation_date
77 )
78 values
79 (
80 ff_routes_s.nextval,
81 'ARCHIVE_NUMBER_ROUTE',
82 'N',
83 'Generic number archive route',
84 l_text,
85 sysdate,
86 0,
87 0,
88 0,
89 sysdate
90 );
91
92 -- Define the route parameter
93 insert into ff_route_parameters
94 (
95 route_parameter_id,
96 route_id,
97 data_type,
98 parameter_name,
99 sequence_no
100 )
101 select
102 ff_route_parameters_s.nextval,
103 ff_routes_s.currval,
104 'N',
105 'User Entity ID',
106 1
107 from dual;
108 --
109 -- Define the route context usage after retrieving the context id.
110 --
111 l_assignment_action_context_id := get_context_id('ASSIGNMENT_ACTION_ID');
112 --
113 insert into ff_route_context_usages
114 (
115 route_id,
116 context_id,
117 sequence_no
118 )
119 select
120 ff_routes_s.currval,
121 l_assignment_action_context_id,
122 1
123 from dual;
124 --
125 -- set the route_id to be returned
126 --
127 select ff_routes_s.currval into l_route_id from dual;
128 --
129 END IF;
130 --
131 RETURN l_route_id;
132 --
133 end get_number_archive_route;
134
135 -------------------------------------------------------------------------
136 -- FUNCTION get_char_archive_route (Private)
137 -- DESCRIPTION: Define the seeded character archive route. Check for
138 -- existance, if this does not exist, create as necessary.
139 -- Used by main procedure as above.
140 -------------------------------------------------------------------------
141 FUNCTION get_char_archive_route RETURN NUMBER IS
142 --
143 l_text clob;
144 l_assignment_action_context_id number;
145 l_exists varchar2(1);
146 l_route_id number;
147 --
148 cursor check_exists is
149 select route_id from ff_routes
150 where route_name = 'ARCHIVE_CHAR_ROUTE';
151 --
152 BEGIN
153 --
154 open check_exists;
155 fetch check_exists into l_route_id;
156 close check_exists;
157
158 IF l_route_id is null then
159 --
160 -- Create the route, context and parameter.
161 -- First define the route text.
162 --
163 l_text := 'ff_archive_items target
164 where target.user_entity_id = &U1
165 and target.context1 = &B1 /* context assignment action id */';
166 --
167 insert into ff_routes
168 (
169 route_id,
170 route_name,
171 user_defined_flag,
172 description,
173 text,
174 last_update_date,
175 last_updated_by,
176 last_update_login,
177 created_by,
178 creation_date
179 )
180 values
181 (
182 ff_routes_s.nextval,
183 'ARCHIVE_CHAR_ROUTE',
184 'N',
185 'Generic character archive route',
186 l_text,
187 sysdate,
188 0,
189 0,
190 0,
191 sysdate
192 );
193
194 -- Define the route parameter
195 insert into ff_route_parameters
196 (
197 route_parameter_id,
198 route_id,
199 data_type,
200 parameter_name,
201 sequence_no
202 )
203 select
204 ff_route_parameters_s.nextval,
205 ff_routes_s.currval,
206 'N',
207 'User Entity ID',
208 1
209 from dual;
210 --
211 -- Define the route context usage.
212 -- Get the context_id for the assignment_action context
213 --
214 l_assignment_action_context_id := get_context_id('ASSIGNMENT_ACTION_ID');
215 --
216 insert into ff_route_context_usages
217 (
218 route_id,
219 context_id,
220 sequence_no
221 )
222 select
223 ff_routes_s.currval,
224 l_assignment_action_context_id,
225 1
226 from dual;
227 --
228 -- set the route_id to be returned
229 --
230 select ff_routes_s.currval into l_route_id from dual;
231 --
232 END IF;
233 --
234 RETURN l_route_id;
235 --
236 end get_char_archive_route;
237 --
238 -------------------------------------------------------------------------
239 -- FUNCTION get_two_context_route (Private)
240 -- DESCRIPTION: Define the seeded two-context archive route. Check for
241 -- existance, if this does not exist, create as necessary.
242 -- The first context usage will be assignment action id, the second is
243 -- user-defined and is taken in as a parameter here. The
244 -- route name is made up using the second context as below, this is
245 -- because we cannot use the same route with two DBI's that have different
246 -- contexts, due to the route_context_usages.
247 -------------------------------------------------------------------------
248 FUNCTION get_two_context_route (p_second_context_name IN VARCHAR2)
249 RETURN NUMBER IS
250 --
251 l_text clob;
252 l_second_context_id number;
253 l_assignment_action_context_id number;
254 l_route_id number;
255 l_new_route_name varchar2(80);
256 --
257 cursor check_exists(c_route_name varchar2) is
258 select route_id from ff_routes
259 where route_name = c_route_name;
260 --
261 BEGIN
262 --
263 l_new_route_name := 'ARCHIVE_'||p_second_context_name||'_ROUTE';
264 --
265 -- dbms_output.put_line('route name :'||l_new_route_name);
266 open check_exists(l_new_route_name);
267 fetch check_exists into l_route_id;
268 close check_exists;
269 --
270 -- dbms_output.put_line('Route id: '||to_char(l_route_id));
271 IF l_route_id is null then
272 --
273 -- Create the route code for two-context route.
274 --
275 -- dbms_output.put_line('creating route...');
276 l_text := 'ff_archive_items target,
277 ff_archive_item_contexts fac,
278 ff_contexts ffc
279 where target.user_entity_id = &U1
280 and target.context1 = &B1 /* context assignment action id */
281 and fac.archive_item_id = target.archive_item_id
282 and ffc.context_id = fac.context_id
283 and fac.context = decode(ffc.data_type,''T'',&B2,to_char(&B2)) /*2nd context*/';
284 --
285 insert into ff_routes
286 (
287 route_id,
288 route_name,
289 user_defined_flag,
290 description,
291 text,
292 last_update_date,
293 last_updated_by,
294 last_update_login,
295 created_by,
296 creation_date
297 )
298 values
299 (
300 ff_routes_s.nextval,
301 l_new_route_name,
302 'N',
303 'Two Context Generic archive route',
304 l_text,
305 sysdate,
306 0,
307 0,
308 0,
309 sysdate
310 );
311
312 -- Define the route parameter
313 insert into ff_route_parameters
314 (
315 route_parameter_id,
316 route_id,
317 data_type,
318 parameter_name,
319 sequence_no
320 )
321 select
322 ff_route_parameters_s.nextval,
323 ff_routes_s.currval,
324 'N',
325 'User Entity ID',
326 1
327 from dual;
328 --
329 -- Define the first route context usage , based on the
330 -- Assignment_action_id
331 --
332 l_assignment_action_context_id := get_context_id('ASSIGNMENT_ACTION_ID');
333 --
334 insert into ff_route_context_usages
335 (
336 route_id,
337 context_id,
338 sequence_no
339 )
340 select
341 ff_routes_s.currval,
342 l_assignment_action_context_id,
343 1
344 from dual;
345 --
346 -- Define second route context usage, based on the parameter.
347 --
348 l_second_context_id := get_context_id(p_second_context_name);
349 --
350 insert into ff_route_context_usages
351 (
352 route_id,
353 context_id,
354 sequence_no
355 )
356 select
357 ff_routes_s.currval,
358 l_second_context_id,
359 2
360 from dual;
361 --
362 -- Set the route ID to be returned
363 --
364 select ff_routes_s.currval into l_route_id from dual;
365 --
366 END IF;
367 --
368 RETURN l_route_id;
369 --
370 end get_two_context_route;
371
372 -------------------------------------------------------------------------
373 -- PROCEDURE: create_archive_dbi (Public)
374 -- DESCRIPTION: This procedure creates an archive database item,
375 -- for the live database item that is passed as a parameter.
376 -- This first checks that the routes have been set up, then
377 -- creates the archive Database Item.
378 -- If the procedure is to use a second context in addition to
379 -- the Assignment Action, this is passed as the secondary context
380 -- name.
381 -- If the procedure is called with a predefined route name, that route
382 -- has to be set up with it's contexts and parameter previously.
383 --
384 -------------------------------------------------------------------------
385 procedure create_archive_dbi(p_live_dbi_name VARCHAR2,
386 p_archive_route_name VARCHAR2 DEFAULT NULL,
387 p_secondary_context_name VARCHAR2 DEFAULT NULL) is
388
389 -- Find the attributes from the live database item and create an
390 -- archive version of it
391
392 l_dbi_null_allowed_flag varchar2(1);
393 l_dbi_description varchar2(240);
394 l_dbi_data_type varchar2(1);
395 l_dbi_user_name varchar(240);
396 l_ue_notfound_allowed_flag varchar2(1);
397 l_ue_creator_type varchar2(30);
398 l_ue_entity_description varchar2(240);
399 l_ue_legislation_code varchar2(4);
400 l_ue_business_group_id number;
401 l_user_entity_seq number;
402 l_user_entity_id number;
403 l_route_parameter_id number;
404 l_dummy_id number;
405 l_route_id number;
406 l_live_route_id number;
407 l_character_archive_route_id number;
408 l_number_archive_route_id number;
409 l_definition_text varchar2(240);
410 l_archive_dbi_name varchar2(80);
411
412 begin
413
414 begin
415 --
416 -- Get all the required information from the
417 -- Live database item, and it's associated user entity
418 --
419 select
420 ue.notfound_allowed_flag,
421 ue.creator_type,
422 ue.entity_description,
423 ue.route_id,
424 ue.legislation_code,
425 ue.business_group_id,
426 dbi.null_allowed_flag,
427 dbi.description ,
428 dbi.data_type,
429 dbi.user_name
430 into
431 l_ue_notfound_allowed_flag,
432 l_ue_creator_type,
433 l_ue_entity_description,
434 l_live_route_id,
435 l_ue_legislation_code,
436 l_ue_business_group_id,
437 l_dbi_null_allowed_flag,
438 l_dbi_description,
439 l_dbi_data_type,
440 l_dbi_user_name
441 from
442 ff_database_items dbi,
443 ff_user_entities ue
444 where dbi.user_name = p_live_dbi_name
445 and dbi.user_entity_id = ue.user_entity_id
446 and ue.business_group_id is null;
447 --
448 -- Note that for USER-DEFINED DBI's to be archived, the above line should
449 -- be removed.
450 --
451 end;
452 --
453 -- Set the Archive DBI's name. If concatenation is > 80,
454 -- This will error with ORA-6502, rather than trying to
455 -- insert into the DB later.
456 --
457 l_archive_dbi_name := 'A_'||p_live_dbi_name;
458 --
459 -- Calculate which Definition Text to use based on the DBI's Data Type
460 --
461 IF l_dbi_data_type = 'N' then
462 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
463 ELSIF l_dbi_data_type = 'D' then
464 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
465 ELSE
466 l_definition_text := 'target.value';
467 END IF;
468 --
469 -- ROUTE CREATION/CHECK CODE.
470 -- Check to see whether the call has been made without an archive route
471 --
472 IF p_archive_route_name is null then
473 --
474 -- Check for the secondary context, if this does not exist
475 -- Choose the seeded single archive route, based once again on the live
476 -- db item's data type. Check Existance and create if necessary.
477 --
478 IF p_secondary_context_name is null then
479 --
480 IF l_dbi_data_type = 'N' OR l_dbi_data_type = 'D' then
481 --
482 -- Use the Number Route
483 l_route_id := get_number_archive_route;
484 ELSE
485 --
486 -- Use the Character Route
487 l_route_id := get_char_archive_route;
488 END IF;
489 ELSE
490 --
491 -- There are two contexts, create the route
492 l_route_id := get_two_context_route(p_secondary_context_name);
493 END IF;
494 --
495 ELSE
496 --
497 -- The route is provided, so this procedure does not
498 -- have to create it, or its context usages or params.
499 -- If supplied route name does not exist, allow NO_DATA_FOUND
500 -- to be raised within this anonymous block.
501 --
502 BEGIN
503 --
504 select route_id
505 into l_route_id
506 from ff_routes where
507 route_name = p_archive_route_name;
508 --
509 END;
510 --
511 END IF; -- END OF ROUTE CREATION/CHECK CODE.
512 --
513 -- Find the User Entity Route parameter that goes with the archive route
514 --
515 select route_parameter_id
516 into l_route_parameter_id
517 from ff_route_parameters
518 where parameter_name = 'User Entity ID'
519 and route_id = l_route_id;
520
521 BEGIN
522 --
523 -- Check to see if the archive database item already exists.
524 -- This will raise an EXCEPTION if it doesn't exist, which
525 -- will cause the INSERT (below). If it does exist, update description
526 -- etc.
527 --
528 select user_entity_id
529 into l_user_entity_seq
530 from ff_user_entities
531 where user_entity_name = l_archive_dbi_name
532 and business_group_id is null;
533
534 -- Removed as you are not allowed to update this table.
535 -- update ff_user_entities
536 -- set route_id = l_route_id,
537 -- notfound_allowed_flag = 'Y', -- l_ue_notfound_allowed_flag,
538 -- entity_description = substr('Archive of ' || l_ue_entity_description, 1, 240)
539 -- where user_entity_name = l_archive_dbi_name
540 -- and business_group_id is null;
541 --
542 -- Does the route parameter exist
543 --
544 begin
545
546 select route_parameter_id
547 into l_dummy_id
548 from ff_route_parameter_values
549 where route_parameter_id = l_route_parameter_id
550 and user_entity_id = l_user_entity_seq;
551
552 update ff_route_parameter_values
553 set value = l_user_entity_seq
554 where route_parameter_id = l_route_parameter_id
555 and user_entity_id = l_user_entity_seq;
556
557 exception when no_data_found then
558 --
559 -- route parameter does not exist so cause insert
560 --
561 insert into ff_route_parameter_values
562 (
563 route_parameter_id,
564 user_entity_id,
565 value,
566 last_update_date,
567 last_updated_by,
568 last_update_login,
569 created_by,
570 creation_date
571 )
572 values
573 (
574 l_route_parameter_id,
575 l_user_entity_seq,
576 l_user_entity_seq,
577 sysdate,
578 0,
579 0,
580 0,
581 sysdate
582 );
583
584 end;
585
586 update ff_database_items
587 set user_entity_id = l_user_entity_seq,
588 data_type = l_dbi_data_type,
589 definition_text = l_definition_text,
590 null_allowed_flag = 'Y', -- l_dbi_null_allowed_flag,
591 description = substr('Archive of ' || l_dbi_description, 1, 240)
592 where user_name = l_archive_dbi_name;
593
594 EXCEPTION WHEN NO_DATA_FOUND THEN
595 --
596 -- Archive DBI does not exist, so create the User Entity,
597 -- Route Parameter, and DBI.
598 --
599 select ff_user_entities_s.nextval into l_user_entity_seq from dual;
600
601 insert into ff_user_entities
602 (
603 user_entity_id,
604 business_group_id,
605 legislation_code,
606 route_id,
607 notfound_allowed_flag,
608 user_entity_name,
609 creator_id,
610 creator_type,
611 entity_description,
612 last_update_date,
613 last_updated_by,
614 last_update_login,
615 created_by,
616 creation_date
617 )
618 values
619 (
620 l_user_entity_seq, /* user_entity_id */
621 null,
622 --l_ue_business_group_id, /* business_group_id */
623 l_ue_legislation_code, /* legislation_code */
624 l_route_id, /* route_id */
625 'Y',
626 -- l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
627 l_archive_dbi_name, /* user_entity_name */
628 0, /* creator_id */
629 'X', /* archive extract creator_type */
630 substr('Archive of ' || l_ue_entity_description, 1, 240), /* entity_description */
631 sysdate, /* last_update_date */
632 0, /* last_updated_by */
633 0, /* last_update_login */
634 0, /* created_by */
635 sysdate /* creation_date */
636 );
637
638 insert into ff_route_parameter_values
639 (
640 route_parameter_id,
641 user_entity_id,
642 value,
643 last_update_date,
644 last_updated_by,
645 last_update_login,
646 created_by,
647 creation_date
648 )
649 values
650 (
651 l_route_parameter_id,
652 l_user_entity_seq,
653 l_user_entity_seq,
654 sysdate,
655 0,
656 0,
657 0,
658 sysdate
659 );
660
661 insert into ff_database_items
662 (
663 user_name,
664 user_entity_id,
665 data_type,
666 definition_text,
667 null_allowed_flag,
668 description,
669 last_update_date,
670 last_updated_by,
671 last_update_login,
672 created_by,
673 creation_date
674 )
675 values
676 (
677 l_archive_dbi_name,
678 l_user_entity_seq,
679 l_dbi_data_type,
680 l_definition_text,
681 'Y',
682 -- l_dbi_null_allowed_flag,
683 substr('Archive of item ' || l_dbi_description, 1, 240),
684 sysdate,
685 0,
686 0,
687 0,
688 sysdate
689 );
690
691 end;
692
693 end create_archive_dbi;
694 ------------------------------------------------------------------------------------
695 -- PROCEDURE: create_archive_dbi (Public, overloaded)
696 -- DESCRIPTION: This procedure creates an EXTRACT type Archive DBI, which
697 -- is prefixed with 'X_'. The extract DBI's name is a parameter
698 -- to the procedure, whereas the above procedure uses the live DBI
699 -- name as a parameter. In this case there is no live DBI, and the
700 -- route_id must always be passed in and (implicitly!) pre-defined.
701 ------------------------------------------------------------------------------------
702 --
703 procedure create_archive_dbi(p_extract_item_name VARCHAR2,
704 p_route_id NUMBER,
705 p_data_type VARCHAR2,
706 p_legislation_code VARCHAR2,
707 p_null_allowed_flag VARCHAR2 DEFAULT 'Y',
708 p_notfound_allowed_flag VARCHAR2 DEFAULT 'Y') IS
709 --
710 cursor get_valid_route (c_route_id number) is
711 select route_id
712 from ff_routes
713 where route_id = c_route_id;
714 --
715 cursor get_user_entity_id (c_user_entity_name varchar2) is
716 select user_entity_id
717 from ff_user_entities
718 where user_entity_name = c_user_entity_name;
719 --
720 cursor get_route_parameter_id (c_route_id number) is
721 select route_parameter_id
722 from ff_route_parameters
723 where parameter_name = 'User Entity ID'
724 and route_id = c_route_id;
725 --
726
727 l_definition_text varchar2(240);
728 l_description varchar2(12) := 'Extract Item';
729 l_dummy number;
730 l_user_entity_id number;
731 l_route_parameter_id number;
732 --
733 BEGIN
734 --
735 -- Calculate which Definition Text to use based on the Data Type
736 --
737 if p_data_type = 'N' then
738 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
739 elsif p_data_type = 'D' then
740 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
741 else
742 l_definition_text := 'target.value';
743 end if;
744 --
745 -- Validate Route ID and ensure route parameter exists
746 --
747 open get_valid_route(p_route_id);
748 fetch get_valid_route into l_dummy;
749 if get_valid_route%NOTFOUND then
750 RAISE NO_DATA_FOUND;
751 end if;
752 close get_valid_route;
753 --
754 open get_route_parameter_id(p_route_id);
755 fetch get_route_parameter_id into l_route_parameter_id;
756 if get_route_parameter_id%NOTFOUND then
757 RAISE NO_DATA_FOUND;
758 end if;
759 close get_route_parameter_id;
760 --
761 -- Check to see if User entity exists
762 --
763 open get_user_entity_id (p_extract_item_name);
764 fetch get_user_entity_id into l_user_entity_id;
765 close get_user_entity_id;
766 --
767 IF l_user_entity_id is null then
768 --
769 -- Create a new User Entity and DBI, and Route Parameter Value.
770 --
771 BEGIN
772 --
773 select ff_user_entities_s.nextval into l_user_entity_id from dual;
774 --
775 insert into ff_user_entities
776 (
777 user_entity_id,
778 business_group_id,
779 legislation_code,
780 route_id,
781 notfound_allowed_flag,
782 user_entity_name,
783 creator_id,
784 creator_type,
785 entity_description,
786 last_update_date,
787 last_updated_by,
788 last_update_login,
789 created_by,
790 creation_date
791 )
792 values
793 (
794 l_user_entity_id,
795 null,
796 p_legislation_code,
797 p_route_id,
798 p_notfound_allowed_flag,
799 p_extract_item_name,
800 0,
801 'X', -- SUBJECT TO CHECKING
802 l_description,
803 sysdate,
804 0,
805 0,
806 0,
807 sysdate
808 );
809 --
810 insert into ff_route_parameter_values
811 (
812 route_parameter_id,
813 user_entity_id,
814 value,
815 last_update_date,
816 last_updated_by,
817 last_update_login,
818 created_by,
819 creation_date
820 )
821 values
822 (
823 l_route_parameter_id,
824 l_user_entity_id,
825 l_user_entity_id,
826 sysdate,
827 0,
828 0,
829 0,
830 sysdate
831 );
832 --
833 insert into ff_database_items
834 (
835 user_name,
836 user_entity_id,
837 data_type,
838 definition_text,
839 null_allowed_flag,
840 description,
841 last_update_date,
842 last_updated_by,
843 last_update_login,
844 created_by,
845 creation_date
846 )
847 values
848 (
849 p_extract_item_name,
850 l_user_entity_id,
851 p_data_type,
852 l_definition_text,
853 p_null_allowed_flag,
854 l_description,
855 sysdate,
856 0,
857 0,
858 0,
859 sysdate
860 );
861 END;
862 --
863 --
864 -- NB cannot update ff_user_entities.
865 --
866 END IF;
867 --
868 end create_archive_dbi;
869 --
870 end pay_archive_utils;