[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_ARCHIVE_DBITEMS_PKG
Source
1 package body pay_za_archive_dbitems_pkg as
2 /* $Header: pyzaadbi.pkb 120.8 2011/12/21 13:08:16 ssarap ship $ */
3
4 -------------------------------------------------------------------------
5 -- This procedure creates the archive routes needed by the
6 -- create_archive_dbi procedure
7 -------------------------------------------------------------------------
8 procedure create_archive_routes is
9
10 l_text ff_routes.text%TYPE;
11 l_source_context_id number;
12 l_source_text_context_id number;
13 l_assignment_action_context_id number;
14 l_source_number_context_id number;
15 l_exists varchar2(1);
16
17 begin
18
19 -- Find the Context ID's
20 select context_id
21 into l_assignment_action_context_id
22 from ff_contexts
23 where context_name = 'ASSIGNMENT_ACTION_ID';
24
25 select context_id
26 into l_source_context_id
27 from ff_contexts
28 where context_name = 'SOURCE_ID';
29
30 select context_id
31 into l_source_text_context_id
32 from ff_contexts
33 where context_name = 'SOURCE_TEXT';
34
35 select context_id
36 into l_source_number_context_id
37 from ff_contexts
38 where context_name = 'SOURCE_NUMBER';
39
40 -------------------------------------------------------------------------
41 -- Define the number archive route
42 -------------------------------------------------------------------------
43 begin
44
45 l_text :=
46 ' ff_archive_items target
47 where target.user_entity_id = &U1
48 and target.context1 = &B1 /* context assignment action id */';
49
50 select 'y'
51 into l_exists
52 from ff_routes
53 where route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';
54
55 update ff_routes
56 set text = l_text
57 where route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';
58
59 exception when no_data_found then
60
61 insert into ff_routes
62 (
63 route_id,
64 route_name,
65 user_defined_flag,
66 description,
67 text,
68 last_update_date,
69 last_updated_by,
70 last_update_login,
71 created_by,
72 creation_date
73 )
74 values
75 (
76 ff_routes_s.nextval,
77 'ZA_NUMBER_ARCHIVE_ROUTE',
78 'N',
79 'Generic number archive route',
80 l_text,
81 sysdate,
82 0,
83 0,
84 0,
85 sysdate
86 );
87
88 -- Define the route parameter
89 insert into ff_route_parameters
90 (
91 route_parameter_id,
92 route_id,
93 data_type,
94 parameter_name,
95 sequence_no
96 )
97 select ff_route_parameters_s.nextval,
98 ff_routes_s.currval,
99 'N',
100 'User Entity ID',
101 1
102 from dual;
103
104 -- Define the route context usage
105 insert into ff_route_context_usages
106 (
107 route_id,
108 context_id,
109 sequence_no
110 )
111 select ff_routes_s.currval,
112 l_assignment_action_context_id,
113 1
114 from dual;
115
116 end;
117
118 -------------------------------------------------------------------------
119 -- Define the date archive route
120 -------------------------------------------------------------------------
121 begin
122
123 l_text :=
124 ' ff_archive_items target
125 where target.user_entity_id = &U1
126 and target.context1 = &B1 /* context assignment action id */';
127
128 select 'y'
129 into l_exists
130 from ff_routes
131 where route_name = 'ZA_DATE_ARCHIVE_ROUTE';
132
133 update ff_routes
134 set text = l_text
135 where route_name = 'ZA_DATE_ARCHIVE_ROUTE';
136
137 exception when no_data_found then
138
139 insert into ff_routes
140 (
141 route_id,
142 route_name,
143 user_defined_flag,
144 description,
145 text,
146 last_update_date,
147 last_updated_by,
148 last_update_login,
149 created_by,
150 creation_date
151 )
152 values
153 (
154 ff_routes_s.nextval,
155 'ZA_DATE_ARCHIVE_ROUTE',
156 'N',
157 'Generic date archive route',
158 l_text,
159 sysdate,
160 0,
161 0,
162 0,
163 sysdate
164 );
165
166 -- Define the route parameter
167 insert into ff_route_parameters
168 (
169 route_parameter_id,
170 route_id,
171 data_type,
172 parameter_name,
173 sequence_no
174 )
175 select ff_route_parameters_s.nextval,
176 ff_routes_s.currval,
177 'N',
178 'User Entity ID',
179 1
180 from dual;
181
182 -- Define the route context usage
183 insert into ff_route_context_usages
184 (
185 route_id,
186 context_id,
187 sequence_no
188 )
189 select ff_routes_s.currval,
190 l_assignment_action_context_id,
191 1
192 from dual;
193
194 end;
195
196 -------------------------------------------------------------------------
197 -- Define the character archive route
198 -------------------------------------------------------------------------
199 begin
200
201 l_text :=
202 ' ff_archive_items target
203 where target.user_entity_id = &U1
204 and target.context1 = &B1 /* context assignment action id */';
205
206 select 'y'
207 into l_exists
208 from ff_routes
209 where route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';
210
211 update ff_routes
212 set text = l_text
213 where route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';
214
215 exception when no_data_found then
216
217 insert into ff_routes
218 (
219 route_id,
220 route_name,
221 user_defined_flag,
222 description,
223 text,
224 last_update_date,
225 last_updated_by,
226 last_update_login,
227 created_by,
228 creation_date
229 )
230 values
231 (
232 ff_routes_s.nextval,
233 'ZA_CHARACTER_ARCHIVE_ROUTE',
234 'N',
235 'Generic character archive route',
236 l_text,
237 sysdate,
238 0,
239 0,
240 0,
241 sysdate
242 );
243
244 -- Define the route parameter
245 insert into ff_route_parameters
246 (
247 route_parameter_id,
248 route_id,
249 data_type,
250 parameter_name,
251 sequence_no
252 )
253 select ff_route_parameters_s.nextval,
254 ff_routes_s.currval,
255 'N',
256 'User Entity ID',
257 1
258 from dual;
259
260 -- Define the route context usage
261 insert into ff_route_context_usages
262 (
263 route_id,
264 context_id,
265 sequence_no
266 )
267 select ff_routes_s.currval,
268 l_assignment_action_context_id,
269 1
270 from dual;
271
272 end;
273
274 -------------------------------------------------------------------------
275 -- Define the Deductions archive route
276 -------------------------------------------------------------------------
277 begin
278
279 l_text :=
280 ' ff_archive_items target,
281 ff_archive_item_contexts fac,
282 ff_archive_item_contexts fac1
283 where target.user_entity_id = &U1
284 and target.context1 = &B1 /* context assignment action id */
285 and fac.archive_item_id = target.archive_item_id
286 and fac.context = to_char(&B2) /* 2nd context of source_id (SARS code) */
287 and fac1.archive_item_id = target.archive_item_id
288 and fac1.context = &B3 /* 3rd context of clearance no */';
289
290 select 'y'
291 into l_exists
292 from ff_routes
293 where route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';
294
295 update ff_routes
296 set text = l_text
297 where route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';
298
299 exception when no_data_found then
300
301 insert into ff_routes
302 (
303 route_id,
304 route_name,
305 user_defined_flag,
306 description,
307 text,
308 last_update_date,
309 last_updated_by,
310 last_update_login,
311 created_by,
312 creation_date
313 )
314 values
315 (
316 ff_routes_s.nextval,
317 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE',
318 'N',
319 'IRP5 Deductions archive route',
320 l_text,
321 sysdate,
322 0,
323 0,
324 0,
325 sysdate
326 );
327
328 -- Define the route parameter
329 insert into ff_route_parameters
330 (
331 route_parameter_id,
332 route_id,
333 data_type,
334 parameter_name,
335 sequence_no
336 )
337 select ff_route_parameters_s.nextval,
338 ff_routes_s.currval,
339 'N',
340 'User Entity ID',
341 1
342 from dual;
343
344 -- Define the route context usage
345 insert into ff_route_context_usages
346 (
347 route_id,
348 context_id,
349 sequence_no
350 )
351 select ff_routes_s.currval,
352 l_assignment_action_context_id,
353 1
354 from dual;
355
356 insert into ff_route_context_usages
357 (
358 route_id,
359 context_id,
360 sequence_no
361 )
362 select ff_routes_s.currval,
363 l_source_context_id,
364 2
365 from dual;
366
367 insert into ff_route_context_usages
368 (
369 route_id,
370 context_id,
371 sequence_no
372 )
373 select ff_routes_s.currval,
374 l_source_text_context_id,
375 3
376 from dual;
377
378 end;
379
380 -------------------------------------------------------------------------
381 -- Define the 4001_4003 archive route
382 -------------------------------------------------------------------------
383 begin
384
385 l_text :=
386 ' ff_archive_items target,
387 ff_archive_item_contexts fac
388 where target.user_entity_id = &U1
389 and target.context1 = &B1 /* context assignment action id */
390 and fac.archive_item_id = target.archive_item_id
391 and fac.context = &B2 /* 2nd context of clearance no */';
392
393 select 'y'
394 into l_exists
395 from ff_routes
396 where route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';
397
398 update ff_routes
399 set text = l_text
400 where route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';
401
402 exception when no_data_found then
403
404 insert into ff_routes
405 (
406 route_id,
407 route_name,
408 user_defined_flag,
409 description,
410 text,
411 last_update_date,
412 last_updated_by,
413 last_update_login,
414 created_by,
415 creation_date
416 )
417 values
418 (
419 ff_routes_s.nextval,
420 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE',
421 'N',
422 'IRP5 4001 to 4003 archive route',
423 l_text,
424 sysdate,
425 0,
426 0,
427 0,
428 sysdate
429 );
430
431 -- Define the route parameter
432 insert into ff_route_parameters
433 (
434 route_parameter_id,
435 route_id,
436 data_type,
437 parameter_name,
438 sequence_no
439 )
440 select ff_route_parameters_s.nextval,
441 ff_routes_s.currval,
442 'N',
443 'User Entity ID',
444 1
445 from dual;
446
447 -- Define the route context usage
448 insert into ff_route_context_usages
449 (
450 route_id,
451 context_id,
452 sequence_no
453 )
454 select ff_routes_s.currval,
455 l_assignment_action_context_id,
456 1
457 from dual;
458
459 insert into ff_route_context_usages
460 (
461 route_id,
462 context_id,
463 sequence_no
464 )
465 select ff_routes_s.currval,
466 l_source_text_context_id,
467 2
468 from dual;
469
470 end;
471
472 -------------------------------------------------------------------------
473 -- Define the LMPSM number archive route with Source_Text context
474 -------------------------------------------------------------------------
475 begin
476
477 l_text :=
478 ' ff_archive_items target,
479 ff_archive_item_contexts faic,
480 ff_contexts fc
481 where target.user_entity_id = &U1
482 and target.context1 = &B1 /* context assignment action id */
483 And target.archive_item_id = faic.archive_item_id
484 and fc.context_name = ''SOURCE_TEXT''
485 and faic.context_id = fc.context_id
486 AND faic.CONTEXT = &B2 /* context SOURCE_TEXT for lump sums */';
487
488 select 'y'
489 into l_exists
490 from ff_routes
491 where route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';
492
493 update ff_routes
494 set text = l_text
495 where route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';
496
497 exception when no_data_found then
498
499 insert into ff_routes
500 (
501 route_id,
502 route_name,
503 user_defined_flag,
504 description,
505 text,
506 last_update_date,
507 last_updated_by,
508 last_update_login,
509 created_by,
510 creation_date
511 )
512 values
513 (
514 ff_routes_s.nextval,
515 'ZA_LMPSM_ARCHIVE_ROUTE',
516 'N',
517 'Generic number archive route with SOURCE_TEXT context',
518 l_text,
519 sysdate,
520 0,
521 0,
522 0,
523 sysdate
524 );
525
526 -- Define the route parameter
527 insert into ff_route_parameters
528 (
529 route_parameter_id,
530 route_id,
531 data_type,
532 parameter_name,
533 sequence_no
534 )
535 select ff_route_parameters_s.nextval,
536 ff_routes_s.currval,
537 'N',
538 'User Entity ID',
539 1
540 from dual;
541
542 -- Define the route context usage
543 insert into ff_route_context_usages
544 (
545 route_id,
546 context_id,
547 sequence_no
548 )
549 select ff_routes_s.currval,
550 l_assignment_action_context_id,
551 1
552 from dual;
553
554 insert into ff_route_context_usages
555 (
556 route_id,
557 context_id,
558 sequence_no
559 )
560 select ff_routes_s.currval,
561 l_source_text_context_id,
562 2
563 from dual;
564
565 end;
566
567 -------------------------------------------------------------------------
568 -- Define the CLRNO number archive route with Source_Number context
569 -------------------------------------------------------------------------
570 begin
571
572 l_text :=
573 ' ff_archive_items target,
574 ff_archive_item_contexts faic,
575 ff_contexts fc
576 where target.user_entity_id = &U1
577 and target.context1 = &B1 /* context assignment action id */
578 And target.ARCHIVE_ITEM_ID = faic.ARCHIVE_ITEM_ID
579 and fc.context_name = ''SOURCE_NUMBER''
580 and faic.context_id = fc.context_id
581 AND faic.CONTEXT = TO_CHAR(&B2) /* context SOURCE_NUMBER for Clearance Number */';
582
583 select 'y'
584 into l_exists
585 from ff_routes
586 where route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';
587
588 update ff_routes
589 set text = l_text
590 where route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';
591
592 exception when no_data_found then
593
594 insert into ff_routes
595 (
596 route_id,
597 route_name,
598 user_defined_flag,
599 description,
600 text,
601 last_update_date,
602 last_updated_by,
603 last_update_login,
604 created_by,
605 creation_date
606 )
607 values
608 (
609 ff_routes_s.nextval,
610 'ZA_CLRNO_ARCHIVE_ROUTE',
611 'N',
612 'Generic number archive route with SOURCE_NUMBER context',
613 l_text,
614 sysdate,
615 0,
616 0,
617 0,
618 sysdate
619 );
620
621 -- Define the route parameter
622 insert into ff_route_parameters
623 (
624 route_parameter_id,
625 route_id,
626 data_type,
627 parameter_name,
628 sequence_no
629 )
630 select ff_route_parameters_s.nextval,
631 ff_routes_s.currval,
632 'N',
633 'User Entity ID',
634 1
635 from dual;
636
637 -- Define the route context usage
638 insert into ff_route_context_usages
639 (
640 route_id,
641 context_id,
642 sequence_no
643 )
644 select ff_routes_s.currval,
645 l_assignment_action_context_id,
646 1
647 from dual;
648
649 insert into ff_route_context_usages
650 (
651 route_id,
652 context_id,
653 sequence_no
654 )
655 select ff_routes_s.currval,
656 l_source_number_context_id,
657 2
658 from dual;
659
660 end;
661
662 --End Added for balance feed Enhancement and Context balance functionality
663
664
665 end create_archive_routes;
666
667 -------------------------------------------------------------------------
668 -- This procedure creates an archive database item, for the live database
669 -- item that is passed as a parameter
670 -- Note: p_item_name should be A_ and then the name of the live database
671 -- item
672 -------------------------------------------------------------------------
673 procedure create_archive_dbi(p_item_name varchar2) is
674
675 -- Find the attributes from the live database item and create an
676 -- arcive version of it
677
678 l_dbi_null_allowed_flag varchar2(1);
679 l_dbi_description varchar2(240);
680 l_dbi_data_type varchar2(1);
681 l_dbi_user_name varchar(240);
682 l_ue_notfound_allowed_flag varchar2(1);
683 l_ue_creator_type varchar2(30);
684 l_ue_entity_description varchar2(240);
685 l_user_entity_seq number;
686 l_user_entity_id number;
687 l_route_parameter_id number;
688 l_dummy_id number;
689 l_route_id number;
690 l_live_route_id number;
691 l_character_archive_route_id number;
692 l_number_archive_route_id number;
693 l_date_archive_route_id number;
694 l_irp5_deductions_route number;
695 l_4001_4003_archive_route_id number;
696 l_lmpsm_archive_route_id number;
697 l_clrno_archive_route_id number;
698 l_old_route_id number;
699 l_definition_text varchar2(240);
700
701 type t_number_tbl is table of number index by binary_integer;
702 l_fast_formula_id_tbl t_number_tbl;
703
704 begin
705
706 begin
707
708 SELECT formula_id
709 BULK COLLECT INTO l_fast_formula_id_tbl
710 FROM ff_fdi_usages_f
711 WHERE item_name = p_item_name
712 AND usage = 'D';
713
714 FOR i IN 1..l_fast_formula_id_tbl.count
715 LOOP
716 DELETE
717 FROM ff_compiled_info_f
718 WHERE formula_id = l_fast_formula_id_tbl(i);
719 END LOOP;
720
721 delete from ff_fdi_usages_f
722 where item_name = p_item_name
723 and usage = 'D';
724
725 -- Check whether the ZA database item exists
726 select ue.notfound_allowed_flag,
727 ue.creator_type,
728 ue.entity_description,
729 ue.route_id,
730 dbi.null_allowed_flag,
731 dbi.description ,
732 dbi.data_type,
733 dbi.user_name
734 into l_ue_notfound_allowed_flag,
735 l_ue_creator_type,
736 l_ue_entity_description,
737 l_live_route_id,
738 l_dbi_null_allowed_flag,
739 l_dbi_description,
740 l_dbi_data_type,
741 l_dbi_user_name
742 from ff_database_items dbi,
743 ff_user_entities ue
744 where dbi.user_name = substr(p_item_name, 3, length(p_item_name) - 2)
745 and dbi.user_entity_id = ue.user_entity_id
746 and ue.legislation_code = 'ZA'
747 and ue.business_group_id is null;
748
749 exception
750 when no_data_found then
751 -- Check whether the core database item exists
752 select ue.notfound_allowed_flag,
753 ue.creator_type,
754 ue.entity_description,
755 ue.route_id,
756 dbi.null_allowed_flag,
757 dbi.description,
758 dbi.data_type,
759 dbi.user_name
760 into l_ue_notfound_allowed_flag,
761 l_ue_creator_type,
762 l_ue_entity_description,
763 l_live_route_id,
764 l_dbi_null_allowed_flag,
765 l_dbi_description,
766 l_dbi_data_type,
767 l_dbi_user_name
768 from ff_database_items dbi,
769 ff_user_entities ue
770 where dbi.user_name = substr(p_item_name, 3, length(p_item_name) - 2)
771 and dbi.user_entity_id = ue.user_entity_id
772 and ue.legislation_code is null
773 and ue.business_group_id is null;
774
775 end;
776
777 select route_id
778 into l_number_archive_route_id
779 from ff_routes
780 where route_name = 'ZA_NUMBER_ARCHIVE_ROUTE';
781
782 select route_id
783 into l_date_archive_route_id
784 from ff_routes
785 where route_name = 'ZA_DATE_ARCHIVE_ROUTE';
786
787 select route_id
788 into l_character_archive_route_id
789 from ff_routes
790 where route_name = 'ZA_CHARACTER_ARCHIVE_ROUTE';
791
792 select route_id
793 into l_irp5_deductions_route
794 from ff_routes
795 where route_name = 'ZA_IRP5_DEDUCTIONS_ARCHIVE_ROUTE';
796
797 select route_id
798 into l_4001_4003_archive_route_id
799 from ff_routes
800 where route_name = 'ZA_IRP5_4001_4003_ARCHIVE_ROUTE';
801
802 select route_id
803 into l_lmpsm_archive_route_id
804 from ff_routes
805 where route_name = 'ZA_LMPSM_ARCHIVE_ROUTE';
806
807 select route_id
808 into l_clrno_archive_route_id
809 from ff_routes
810 where route_name = 'ZA_CLRNO_ARCHIVE_ROUTE';
811
812 -- Choose the archive route, based on the live db item's data type
813 if l_dbi_data_type = 'N' then
814
815 if l_dbi_user_name = 'ZA_IRP5_DEDUCTIONS' then
816
817 l_definition_text := 'to_number(target.value)';
818 l_route_id := l_irp5_deductions_route;
819
820 elsif l_dbi_user_name in
821 (
822 'ZA_IRP5_CUR_PENSION',
823 'ZA_IRP5_ANN_PENSION',
824 'ZA_IRP5_ARR_PENSION',
825 'ZA_IRP5_CUR_PROVIDENT',
826 'ZA_IRP5_ANN_PROVIDENT'
827 ) then
828
829 l_definition_text := 'to_number(target.value)';
830 l_route_id := l_4001_4003_archive_route_id;
831
832 elsif instr(l_dbi_user_name,'_ASG_LMPSM_',1) > 0 Then
833 l_definition_text := 'to_number(target.value)';
834 l_route_id := l_lmpsm_archive_route_id;
835
836
837 elsif instr(l_dbi_user_name,'_ASG_CLRNO_',1) > 0 Then
838 l_definition_text := 'to_number(target.value)';
839 l_route_id := l_clrno_archive_route_id;
840
841 else
842
843 l_definition_text := 'to_number(target.value)';
844 l_route_id := l_number_archive_route_id;
845
846 end if;
847
848 elsif l_dbi_data_type = 'D' then
849
850 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
851 l_route_id := l_date_archive_route_id;
852
853 else
854
855 l_definition_text := 'target.value';
856 l_route_id := l_character_archive_route_id;
857
858 end if;
859
860 -- Find the User Entity Route parameter that goes with the archive route
861 select route_parameter_id
862 into l_route_parameter_id
863 from ff_route_parameters
864 where parameter_name = 'User Entity ID'
865 and route_id = l_route_id;
866
867 begin
868
869 -- Check to see if the archive database item already exist
870 select user_entity_id, route_id
871 into l_user_entity_seq, l_old_route_id
872 from ff_user_entities
873 where user_entity_name = p_item_name
874 and legislation_code = 'ZA'
875 and business_group_id is null;
876
877 IF l_old_route_id <> l_route_id then
878 DELETE FROM ff_route_parameter_values
879 where user_entity_id = l_user_entity_seq
880 AND route_parameter_id <> l_route_parameter_id;
881
882 END if;
883
884 update ff_user_entities
885 set route_id = l_route_id,
886 notfound_allowed_flag = 'Y', -- l_ue_notfound_allowed_flag,
887 entity_description = substr('Archive of ' || l_ue_entity_description, 1, 240)
888 where user_entity_name = p_item_name
889 and legislation_code = 'ZA'
890 and business_group_id is null;
891
892 begin
893
894 select route_parameter_id
895 into l_dummy_id
896 from ff_route_parameter_values
897 where route_parameter_id = l_route_parameter_id
898 and user_entity_id = l_user_entity_seq;
899
900 update ff_route_parameter_values
901 set value = l_user_entity_seq
902 where route_parameter_id = l_route_parameter_id
903 and user_entity_id = l_user_entity_seq;
904
905 exception when no_data_found then
906
907 insert into ff_route_parameter_values
908 (
909 route_parameter_id,
910 user_entity_id,
911 value,
912 last_update_date,
913 last_updated_by,
914 last_update_login,
915 created_by,
916 creation_date
917 )
918 values
919 (
920 l_route_parameter_id,
921 l_user_entity_seq,
922 l_user_entity_seq,
923 sysdate,
924 0,
925 0,
926 0,
927 sysdate
928 );
929
930 end;
931
932 update ff_database_items
933 set user_entity_id = l_user_entity_seq,
934 data_type = l_dbi_data_type,
935 definition_text = l_definition_text,
936 null_allowed_flag = 'Y', -- l_dbi_null_allowed_flag,
937 description = substr('Archive of item ' || l_dbi_description, 1, 240)
938 where user_name = p_item_name;
939
940 exception when no_data_found then
941
942 -- Create the archive database item
943 select ff_user_entities_s.nextval
944 into l_user_entity_seq
945 from dual;
946
947 insert into ff_user_entities
948 (
949 user_entity_id,
950 business_group_id,
951 legislation_code,
952 route_id,
953 notfound_allowed_flag,
954 user_entity_name,
955 creator_id,
956 creator_type,
957 entity_description,
958 last_update_date,
959 last_updated_by,
960 last_update_login,
961 created_by,
962 creation_date
963 )
964 values
965 (
966 l_user_entity_seq, -- user_entity_id
967 null, -- business_group_id
968 'ZA', -- legislation_code
969 l_route_id, -- route_id
970 'Y', -- l_ue_notfound_allowed_flag, -- notfound_allowed_flag
971 p_item_name, -- user_entity_name
972 0, -- creator_id
973 'X', -- archive extract creator_type
974 substr('Archive of ' || l_ue_entity_description, 1, 240), -- entity_description
975 sysdate, -- last_update_date
976 0, -- last_updated_by
977 0, -- last_update_login
978 0, -- created_by
979 sysdate -- creation_date
980 );
981
982 insert into ff_route_parameter_values
983 (
984 route_parameter_id,
985 user_entity_id,
986 value,
987 last_update_date,
988 last_updated_by,
989 last_update_login,
990 created_by,
991 creation_date
992 )
993 values
994 (
995 l_route_parameter_id,
996 l_user_entity_seq,
997 l_user_entity_seq,
998 sysdate,
999 0,
1000 0,
1001 0,
1002 sysdate
1003 );
1004
1005 insert into ff_database_items
1006 (
1007 user_name,
1008 user_entity_id,
1009 data_type,
1010 definition_text,
1011 null_allowed_flag,
1012 description,
1013 last_update_date,
1014 last_updated_by,
1015 last_update_login,
1016 created_by,
1017 creation_date
1018 )
1019 values
1020 (
1021 p_item_name,
1022 l_user_entity_seq,
1023 l_dbi_data_type,
1024 l_definition_text,
1025 'Y', -- l_dbi_null_allowed_flag,
1026 substr('Archive of item ' || l_dbi_description, 1, 240),
1027 sysdate,
1028 0,
1029 0,
1030 0,
1031 sysdate
1032 );
1033
1034 end;
1035
1036 end create_archive_dbi;
1037
1038 end pay_za_archive_dbitems_pkg;