[Home] [Help]
PACKAGE BODY: APPS.PY_W2_DBITEMS
Source
1 package body py_w2_dbitems as
2 /* $Header: pymagdbi.pkb 120.0 2005/05/29 06:44:15 appldev noship $ */
3 PROCEDURE create_dbi is
4 l_text long;
5 l_tax_unit_context_id number;
6 l_jurisdiction_code_context_id number;
7 l_user_entity_id number;
8 l_context1 number;
9 l_context2 number;
10
11 --
12 -- ******** local procedure : insert_route_context_usages ********
13 --
14 procedure insert_route_context_usages
15 (
16 p_context_id in number,
17 p_sequence_no in number
18 ) is
19 begin -- [
20 hr_utility.set_location('gbstrdbi.insert_route_context_usages', 1);
21 insert into ff_route_context_usages
22 (route_id,
23 context_id,
24 sequence_no)
25 select ff_routes_s.currval,
26 p_context_id,
27 p_sequence_no
28 from dual;
29 end insert_route_context_usages; -- ]
30 --
31 -- ******** local procedure : insert_user_entity ********
32 --
33 procedure insert_user_entity
34 (
35 p_user_entity_name in varchar2,
36 p_description in varchar2
37 ) is
38 begin -- [
39 hr_utility.set_location('gbstrdbi.insert_user_entity', 1);
40 insert into ff_user_entities
41 (user_entity_id,
42 business_group_id,
43 legislation_code,
44 route_id,
45 notfound_allowed_flag,
46 user_entity_name,
47 creator_id,
48 creator_type,
49 entity_description,
50 last_update_date,
51 last_updated_by,
52 last_update_login,
53 created_by,
54 creation_date)
55 --
56 values (ff_user_entities_s.nextval,
57 null,
58 'US',
59 ff_routes_s.currval,
60 'N',
61 p_user_entity_name ,
62 0,
63 'SEH', /* SEH */
64 p_description,
65 sysdate,
66 0,
67 0,
68 0,
69 sysdate);
70 end insert_user_entity; -- ]
71 --
72 -- ******** local procedure : insert_curr_database_item ********
73 --
74 procedure insert_curr_database_item
75 (
76 p_user_name in varchar2,
77 p_definition_text in varchar2,
78 p_description in varchar2,
79 p_data_type in varchar2,
80 p_null_allowed_flag in varchar2
81 ) is
82 begin -- [
83 hr_utility.set_location('gbstrdbi.insert_curr_database_item', 1);
84 hr_utility.trace ('creating : ' || p_user_name);
85 insert into ff_database_items (
86 user_name,
87 user_entity_id,
88 data_type,
89 definition_text,
90 null_allowed_flag,
91 description,
92 last_update_date,
93 last_updated_by,
94 last_update_login,
95 created_by,
96 creation_date)
97 --
98 values (p_user_name,
99 ff_user_entities_s.currval,
100 p_data_type,
101 p_definition_text,
102 p_null_allowed_flag,
103 p_description,
104 sysdate,
105 0,
106 0,
107 0,
108 sysdate);
109 end insert_curr_database_item; -- ]
110
111 --
112 -- ******** local procedure : insert_database_item ********
113 --
114 procedure insert_database_item
115 (
116 p_user_name in varchar2,
117 p_user_entity_id in number,
118 p_definition_text in varchar2,
119 p_description in varchar2,
120 p_data_type in varchar2,
121 p_null_allowed_flag in varchar2
122 ) is
123 begin -- [
124 hr_utility.set_location('gbstrdbi.insert_database_item', 1);
125 hr_utility.trace ('creating : ' || p_user_name);
126 insert into ff_database_items (
127 user_name,
128 user_entity_id,
129 data_type,
130 definition_text,
131 null_allowed_flag,
132 description,
133 last_update_date,
134 last_updated_by,
135 last_update_login,
136 created_by,
137 creation_date)
138 --
139 values (p_user_name,
140 p_user_entity_id,
141 p_data_type,
142 p_definition_text,
143 p_null_allowed_flag,
144 p_description,
145 sysdate,
146 0,
147 0,
148 0,
149 sysdate);
150 end insert_database_item; -- ]
151 --
152 --
153 -- ******** local procedure : insert_route ********
154 --
155 procedure insert_route
156 (
157 p_route_name in varchar2,
158 p_description in varchar2,
159 p_text in varchar2
160 ) is
161 begin -- [
162 hr_utility.trace ('creating route : ' || p_route_name);
163 hr_utility.set_location('gbstrdbi.insert_route', 1);
164 insert into ff_routes
165 (route_id,
166 route_name,
167 user_defined_flag,
168 description,
169 text,
170 last_update_date,
171 last_updated_by,
172 last_update_login,
173 created_by,
174 creation_date)
175 values (ff_routes_s.nextval,
176 p_route_name,
177 'N',
178 p_description,
179 p_text,
180 sysdate,
181 0,
182 0,
183 0,
184 sysdate);
185 end insert_route; -- ]
186 --
187 ------------------------------ begin -------------------------------
188 --
189 BEGIN -- [
190 --
191 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
192 -- +
193 -- Route for TAX UNIT INFORMATION *
194 -- +
195 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
196 --
197 select context_id
198 into l_tax_unit_context_id
199 from ff_contexts
200 where context_name = 'TAX_UNIT_ID';
201
202 --++ l_text :=
203 --++ 'hr_tax_units_v target
204 --++ where target.tax_unit_id = B1 ';
205 --
206 --++ insert_route
207 --++ ('US_TAX_UNIT_INFORMATION',
208 --++ 'Tax unit information',
209 --++ l_text);
210 --
211 --++ insert_route_context_usages
212 --++ (l_tax_unit_context_id,1);
213 --
214 --++ insert_user_entity
215 --++ ('US_TAX_UNIT_INFORMATION',
216 --++ 'Tax unit information');
217 --
218 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
219 -- +
220 -- Database items for TAX UNIT INFORMATION *
221 -- +
222 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
223 --
224 --++ insert_curr_database_item
225 --++ ('TAX_UNIT_NAME',
226 --++ 'target.name',
227 --++ 'name of tax unit',
228 --++ 'T',
229 --++ 'N');
230 --
231 --++ insert_curr_database_item
232 --++ ('TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER',
233 --++ 'target.employer_identification_number',
234 --++ 'name of tax unit',
235 --++ 'T',
236 --++ 'N');
237 --
238 --++ insert_curr_database_item
239 --++ ('TAX_UNIT_ADDRESS_LINE_1',
240 --++ 'target.address_line_1',
241 --++ 'address line 1 of tax unit',
242 --++ 'T',
243 --++ 'Y');
244 --
245 --++ insert_curr_database_item
246 --++ ('TAX_UNIT_ADDRESS_LINE_2',
247 --++ 'target.address_line_2',
248 --++ 'address line 2 of tax unit',
249 --++ 'T',
250 --++ 'Y');
251 --
252 --++ insert_curr_database_item
253 --++ ('TAX_UNIT_ADDRESS_LINE_3',
254 --++ 'target.address_line_3',
255 --++ 'address line 3 of tax unit',
256 --++ 'T',
257 --++ 'Y');
258 --
259 --++ insert_curr_database_item
260 --++ ('TAX_UNIT_TOWN_OR_CITY',
261 --++ 'target.town_or_city',
262 --++ 'town or xity of tax unit',
263 --++ 'T',
264 --++ 'Y');
265 --
266 --++ insert_curr_database_item
267 --++ ('TAX_UNIT_STATE_CODE',
268 --++ 'target.state_code',
269 --++ 'state code of tax unit',
270 --++ 'T',
271 --++ 'Y');
272 --
273 --++ insert_curr_database_item
274 --++ ('TAX_UNIT_ZIP_CODE',
275 --++ 'target.zip_code',
276 --++ 'zip code of tax unit',
277 --++ 'T',
278 --++ 'Y');
279 --
280 --++ insert_curr_database_item
281 --++ ('TAX_UNIT_COUNTRY_CODE',
282 --++ 'target.country_code',
283 --++ 'country code of tax unit',
284 --++ 'T',
285 --++ 'Y');
286
287 --++ SELECT user_entity_id
288 --++ INTO l_user_entity_id
289 --++ FROM ff_user_entities
290 --++ WHERE user_entity_name = 'EMPLOYEE_PERSON_ADDRESS_DETAILS';
291
292 --++ insert_database_item
293 --++ ('PER_ADR_COUNTRY_CODE',
294 --++ l_user_entity_id,
295 --++ 'a.territory_code',
296 --++ 'Person''s country code',
297 --++ 'T',
298 --++ 'Y');
299
300
301
302 --++ select context_id
303 --++ into l_jurisdiction_code_context_id
304 --++ from ff_contexts
305 --++ where context_name = 'JURISDICTION_CODE';
306
307
308 --++ insert_route
309 --++ ('SEH_FIPS_CODE_US',
310 --++ 'Derives the fips_code based on Jurisdiction code',
311 --++ 'pay_state_rules rules WHERE substr(rules.jurisdiction_code, 1, 2) = substr(B1, 1, 2)');
312
313 --++ insert_route_context_usages
314 --++ (l_jurisdiction_code_context_id,1);
315
316 --++ insert_user_entity
317 --++ ('SEH_FIPS_CODE_US',
318 --++ 'Derives the fips_code based on Jurisdiction code');
319
320 --++ insert_curr_database_item
321 --++ ('FIPS_CODE_JD',
322 --++ 'rules.fips_code',
323 --++ 'fips code for a specific state',
324 --++ 'N',
325 --++ 'Y');
326
327 select context_id
328 into l_context1
329 from ff_contexts
330 where context_name = 'PAYROLL_ACTION_ID';
331
332 select context_id
333 into l_context2
334 from ff_contexts
335 where context_name = 'TAX_UNIT_ID';
336
337 insert_route
338 ('SEH_SQWL_NUM_EMPS_US',
339 'Derives the number of employees before you run the report',
340 'pay_assignment_actions paa
341 where paa.payroll_action_id = B1
342 and paa.tax_unit_id = B2');
343
344 insert_route_context_usages
345 (l_context1,1);
346
347 insert_route_context_usages
348 (l_context2,2);
349
350 insert_user_entity
351 ('SEH_SQWL_NUM_EMPS_US',
352 'Number of State Employees for a specific Quarter');
353
354 insert_curr_database_item
355 ('SQWL_NUM_EMPS_GRE_PACTID',
356 'count(*)',
357 'Number of State Employees for a specific Quarter',
358 'N',
359 'Y');
360
361
362
363 END create_dbi; -- ]
364
365 PROCEDURE create_archive_route is
366
367 cursor c_check_context(p_route_id number, p_context_id number) is
368 select 'Y'
369 from ff_route_context_usages frcu
370 where route_id = p_route_id
371 and context_id = p_context_id;
372
373 l_text long;
374 l_tax_unit_context_id number;
375 l_payroll_action_context_id number;
376 l_assignment_action_context_id number;
377 l_jurisdiction_context_id number;
378 l_user_entity_id number;
379 l_exists VARCHAR2(1);
380 l_route_id number;
381
382 begin -- [
383
384 -- find the context_ids
385 select context_id
386 into l_tax_unit_context_id
387 from ff_contexts
388 where context_name = 'TAX_UNIT_ID';
389
390 select context_id
391 into l_assignment_action_context_id
392 from ff_contexts
393 where context_name = 'ASSIGNMENT_ACTION_ID';
394
395 select context_id
396 into l_payroll_action_context_id
397 from ff_contexts
398 where context_name = 'PAYROLL_ACTION_ID';
399
400 /* For EOY */
401 select context_id
402 into l_jurisdiction_context_id
403 from ff_contexts
404 where context_name = 'JURISDICTION_CODE';
405
406 BEGIN
407
408 hr_utility.trace('setting the route text for EMPLOYER_ARCHIVE');
409
410 -- define the employer archive route
411 l_text :=
412 'ff_archive_item_contexts con2,
413 ff_contexts fc2,
414 ff_archive_items target
415 where target.user_entity_id = &U1
416 and target.context1 = to_char(&B1)
417 /* context of payroll action id */
418 and fc2.context_name = ''TAX_UNIT_ID''
419 and target.archive_item_id = con2.archive_item_id
420 and con2.context_id = fc2.context_id
421 and ltrim(rtrim(con2.context)) = to_char(&B2)
422 /* 2nd context of tax_unit_id */';
423
424 hr_utility.trace('selecting the route_id for EMPLOYER_ARCHIVE');
425
426 select route_id into l_route_id
427 from ff_routes where route_name = 'EMPLOYER_ARCHIVE';
428
429 hr_utility.trace('updating the route text for EMPLOYER_ARCHIVE');
430
431 update ff_routes
432 set text = l_text
433 where route_id = l_route_id;
434
435 hr_utility.trace('deleting ff_route_context_usages for EMPLOYER_ARCHIVE');
436
437 /* delete ff_route_context_usages
438 where route_id = l_route_id; */
439
440 open c_check_context(l_route_id,l_tax_unit_context_id);
441 fetch c_check_context into l_exists;
442 if c_check_context%NOTFOUND then
443
444 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
445
446 insert into ff_route_context_usages
447 (route_id,
448 context_id,
449 sequence_no)
450 values (l_route_id,
451 l_payroll_action_context_id,
452 1);
453
454 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
455
456 insert into ff_route_context_usages
457 (route_id,
458 context_id,
459 sequence_no)
460 values (l_route_id,
461 l_tax_unit_context_id,
462 2 );
463
464 end if;
465 close c_check_context;
466
467 EXCEPTION WHEN NO_DATA_FOUND THEN
468 hr_utility.trace('inserting ff_routes for EMPLOYER_ARCHIVE');
469
470 insert into ff_routes
471 (route_id,
472 route_name,
473 user_defined_flag,
474 description,
475 text,
476 last_update_date,
477 last_updated_by,
478 last_update_login,
479 created_by,
480 creation_date)
481 values (ff_routes_s.nextval,
482 'EMPLOYER_ARCHIVE',
483 'N',
487 0,
484 'sql to retrieve GRE based archived items',
485 l_text,
486 sysdate,
488 0,
489 0,
490 sysdate);
491
492 -- define the route_parameter
493 hr_utility.trace('inserting ff_route_parameters for EMPLOYER_ARCHIVE');
494
495 insert into ff_route_parameters
496 (ROUTE_PARAMETER_ID,
497 ROUTE_ID,
498 DATA_TYPE,
499 PARAMETER_NAME,
500 SEQUENCE_NO )
501 select ff_route_parameters_s.nextval,
502 ff_routes_s.currval,
503 'N',
504 'User Entity ID',
505 1 from dual;
506
507 -- define the route_context usage
508
509 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
510
511 insert into ff_route_context_usages
512 (route_id,
513 context_id,
514 sequence_no)
515 select ff_routes_s.currval,
516 l_payroll_action_context_id,
517 1
518 from dual;
519
520 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_ARCHIVE');
521
522 insert into ff_route_context_usages
523 (route_id,
524 context_id,
525 sequence_no)
526 select ff_routes_s.currval,
527 l_tax_unit_context_id,
528 2
529 from dual;
530
531 END;
532
533 BEGIN
534
535 -- define the employer archive route
536 l_text :=
537 'ff_archive_item_contexts con2,
538 ff_contexts fc2,
539 ff_archive_items target
540 where target.user_entity_id = &U1
541 and target.context1 = to_char(&B1)
542 /* context of payroll action id */
543 and fc2.context_name = ''TAX_UNIT_ID''
544 and target.archive_item_id = con2.archive_item_id
545 and con2.context_id = fc2.context_id
546 and ltrim(rtrim(con2.context)) = to_char(&B2)
547 /* 2nd context of tax_unit_id */';
548
549 select route_id into l_route_id
550 from ff_routes where route_name = 'EMPLOYER_ARCHIVE_DATE';
551
552 update ff_routes
553 set text = l_text
554 where route_id = l_route_id;
555
556 /* delete ff_route_context_usages
557 where route_id = l_route_id; */
558
559 open c_check_context(l_route_id,l_tax_unit_context_id);
560 fetch c_check_context into l_exists;
561 if c_check_context%NOTFOUND then
562
563 insert into ff_route_context_usages
564 (route_id,
565 context_id,
566 sequence_no)
567 values (l_route_id,
568 l_payroll_action_context_id,
569 1);
570
571 insert into ff_route_context_usages
572 (route_id,
573 context_id,
574 sequence_no)
575 values (l_route_id,
576 l_tax_unit_context_id,
577 2 );
578 end if;
579 close c_check_context;
580
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583
584 insert into ff_routes
585 (route_id,
586 route_name,
587 user_defined_flag,
588 description,
589 text,
590 last_update_date,
591 last_updated_by,
592 last_update_login,
593 created_by,
594 creation_date)
595 values (ff_routes_s.nextval,
596 'EMPLOYER_ARCHIVE_DATE',
597 'N',
598 'sql to retrieve GRE based date archived items',
599 l_text,
600 sysdate,
601 0,
602 0,
603 0,
604 sysdate);
605
606
607 -- define the route_parameter
608 insert into ff_route_parameters
609 (ROUTE_PARAMETER_ID,
610 ROUTE_ID,
611 DATA_TYPE,
612 PARAMETER_NAME,
613 SEQUENCE_NO )
614 select ff_route_parameters_s.nextval,
615 ff_routes_s.currval,
616 'N',
617 'User Entity ID',
618 1 from dual;
619
620 -- define the route_context usage
621
622 insert into ff_route_context_usages
623 (route_id,
624 context_id,
625 sequence_no)
626 select ff_routes_s.currval,
627 l_payroll_action_context_id,
628 1
629 from dual;
630
631 insert into ff_route_context_usages
632 (route_id,
633 context_id,
634 sequence_no)
635 select ff_routes_s.currval,
636 l_tax_unit_context_id,
637 2
638 from dual;
639
640 END;
641
642 BEGIN
643
644 -- define the employer archive route
645 l_text :=
646 'ff_archive_item_contexts con2,
647 ff_contexts fc2,
651 /* context of payroll action id */
648 ff_archive_items target
649 where target.user_entity_id = &U1
650 and target.context1 = to_char(&B1)
652 and fc2.context_name = ''TAX_UNIT_ID''
653 and target.archive_item_id = con2.archive_item_id
654 and con2.context_id = fc2.context_id
655 and trim(rtrim(con2.context)) = to_char(&B2)
656 /* 2nd context of tax_unit_id */';
657
658 select route_id into l_route_id
659 from ff_routes where route_name = 'EMPLOYER_ARCHIVE_NUMBER';
660
661 update ff_routes
662 set text = l_text
663 where route_id = l_route_id;
664
665 /* delete ff_route_context_usages
666 where route_id = l_route_id; */
667
668 open c_check_context(l_route_id,l_tax_unit_context_id);
669 fetch c_check_context into l_exists;
670 if c_check_context%NOTFOUND then
671
672
673 insert into ff_route_context_usages
674 (route_id,
675 context_id,
676 sequence_no)
677 values ( l_route_id,
678 l_payroll_action_context_id,
679 1);
680
681 insert into ff_route_context_usages
682 (route_id,
683 context_id,
684 sequence_no)
685 values (l_route_id,
686 l_tax_unit_context_id,
687 2 );
688 end if;
689 close c_check_context;
690
691
692 EXCEPTION
693 WHEN NO_DATA_FOUND THEN
694
695 insert into ff_routes
696 (route_id,
697 route_name,
698 user_defined_flag,
699 description,
700 text,
701 last_update_date,
702 last_updated_by,
703 last_update_login,
704 created_by,
705 creation_date)
706 values (ff_routes_s.nextval,
707 'EMPLOYER_ARCHIVE_NUMBER',
708 'N',
709 'sql to retrieve GRE based numeric archived items',
710 l_text,
711 sysdate,
712 0,
713 0,
714 0,
715 sysdate);
716
717
718 -- define the route_parameter
719 insert into ff_route_parameters
720 (ROUTE_PARAMETER_ID,
721 ROUTE_ID,
722 DATA_TYPE,
723 PARAMETER_NAME,
724 SEQUENCE_NO )
725 select ff_route_parameters_s.nextval,
726 ff_routes_s.currval,
727 'N',
728 'User Entity ID',
729 1 from dual;
730
731 -- define the route_context usage
732
733 insert into ff_route_context_usages
734 (route_id,
735 context_id,
736 sequence_no)
737 select ff_routes_s.currval,
738 l_payroll_action_context_id,
739 1
740 from dual;
741
742 insert into ff_route_context_usages
743 (route_id,
744 context_id,
745 sequence_no)
746 select ff_routes_s.currval,
747 l_tax_unit_context_id,
748 2
749 from dual;
750
751 END;
752
753 BEGIN
754
755 -- define the employer tax unit id archive route
756 -- Even though it is employer archive route, it
757 -- will accept the context of assignment_action_id and
758 -- for the given assignment_action_id , will get the
759 -- payroll_action_id. This is being done because the
760 -- mag reports will be setting up the assignment_action_id
761 -- context as that of year end pre-process but the payroll_
762 -- action_id that of the mag report.
763
764 hr_utility.trace('setting route text for EMPLOYER_TAX_UNIT_ARCHIVE_DATE');
765
766 l_text :=
767 'ff_archive_item_contexts con2,
768 ff_contexts fc2,
769 ff_archive_items target
770 where target.context1 = &B1
771 /* context of payroll_action_id */
772 and target.user_entity_id = &U1
773 and fc2.context_name = ''TAX_UNIT_ID''
774 and con2.archive_item_id = target.archive_item_id
775 and con2.context_id = fc2.context_id
776 and ltrim(rtrim(con2.context)) = to_char(&B2)
777 /* 2nd context of tax_unit_id */';
778
779 hr_utility.trace('selecting route id for EMPLOYER_TAX_UNIT_ARCHIVE_DATE');
780
781 select route_id into l_route_id
782 from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE';
783
784 hr_utility.trace('updating ff_routes for EMPLOYER_TAX_UNIT_ARCHIVE_DATE');
785
786 update ff_routes
787 set text = l_text
788 where route_id = l_route_id;
789
790 hr_utility.trace('deleting ff_routes for EMPLOYER_TAX_UNIT_ARCHIVE_DATE');
791
792 /* delete ff_route_context_usages
793 where route_id = l_route_id; */
794
795 open c_check_context(l_route_id,l_tax_unit_context_id);
796 fetch c_check_context into l_exists;
797 if c_check_context%NOTFOUND then
798
799 insert into ff_route_context_usages
800 (route_id,
804 l_payroll_action_context_id,
801 context_id,
802 sequence_no)
803 values (l_route_id,
805 1);
806
807 insert into ff_route_context_usages
808 (route_id,
809 context_id,
810 sequence_no)
811 values (l_route_id,
812 l_tax_unit_context_id,
813 2 );
814 end if;
815 close c_check_context;
816
817 EXCEPTION WHEN NO_DATA_FOUND THEN
818 insert into ff_routes
819 (route_id,
820 route_name,
821 user_defined_flag,
822 description,
823 text,
824 last_update_date,
825 last_updated_by,
826 last_update_login,
827 created_by,
828 creation_date)
829 values (ff_routes_s.nextval,
830 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE',
831 'N',
832 'sql to retrieve GRE based employer archived items',
833 l_text,
834 sysdate,
835 0,
836 0,
837 0,
838 sysdate);
839
840 -- define the route_parameter
841 insert into ff_route_parameters
842 (ROUTE_PARAMETER_ID,
843 ROUTE_ID,
844 DATA_TYPE,
845 PARAMETER_NAME,
846 SEQUENCE_NO )
847 select ff_route_parameters_s.nextval,
848 ff_routes_s.currval,
849 'N',
850 'User Entity ID',
851 1 from dual;
852
853 -- define the route_context usage
854
855 insert into ff_route_context_usages
856 (route_id,
857 context_id,
858 sequence_no)
859 select ff_routes_s.currval,
860 l_payroll_action_context_id,
861 1
862 from dual;
863
864 insert into ff_route_context_usages
865 (route_id,
866 context_id,
867 sequence_no)
868 select ff_routes_s.currval,
869 l_tax_unit_context_id,
870 2
871 from dual;
872
873 END;
874
875
876 BEGIN
877
878 -- define the employer tax unit id archive route
879 -- Even though it is employer archive route, it
880 -- will accept the context of assignment_action_id and
881 -- for the given assignment_action_id , will get the
882 -- payroll_action_id. This is being done because the
883 -- mag reports will be setting up the assignment_action_id
884 -- context as that of year end pre-process but the payroll_
885 -- action_id that of the mag report.
886
887 hr_utility.trace('setting route text for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
888
889 l_text :=
890 'ff_archive_item_contexts con2,
891 ff_contexts fc2,
892 ff_archive_items target
893 where target.context1 = &B1
894 /* context of payroll_action_id */
895 and target.user_entity_id = &U1
896 and fc2.context_name = ''TAX_UNIT_ID''
897 and con2.archive_item_id = target.archive_item_id
898 and con2.context_id = fc2.context_id
899 and ltrim(rtrim(con2.context)) = to_char(&B2)
900 /* 2nd context of tax_unit_id */';
901
902 hr_utility.trace('selecting route_id for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
903
904 select route_id into l_route_id
905 from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER';
906
907 hr_utility.trace('updating route_text for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
908
909
910 update ff_routes
911 set text = l_text
912 where route_id = l_route_id;
913
914 hr_utility.trace('deleting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
915
916 /* delete ff_route_context_usages
917 where route_id = l_route_id; */
918
919 open c_check_context(l_route_id,l_tax_unit_context_id);
920 fetch c_check_context into l_exists;
921 if c_check_context%NOTFOUND then
922
923
924 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
925
926 insert into ff_route_context_usages
927 (route_id,
928 context_id,
929 sequence_no)
930 values (l_route_id,
931 l_payroll_action_context_id,
932 1);
933
934 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
935
936 insert into ff_route_context_usages
937 (route_id,
938 context_id,
939 sequence_no)
940 values (l_route_id,
941 l_tax_unit_context_id,
942 2 );
943 end if;
944 close c_check_context;
945
946
947 EXCEPTION WHEN NO_DATA_FOUND THEN
948 hr_utility.trace('inserting ff_routes for EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER');
949
950 insert into ff_routes
951 (route_id,
955 text,
952 route_name,
953 user_defined_flag,
954 description,
956 last_update_date,
957 last_updated_by,
958 last_update_login,
959 created_by,
960 creation_date)
961 values (ff_routes_s.nextval,
962 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER',
963 'N',
964 'sql to retrieve GRE based employer archived items',
965 l_text,
966 sysdate,
967 0,
968 0,
969 0,
970 sysdate);
971
972 -- define the route_parameter
973 insert into ff_route_parameters
974 (ROUTE_PARAMETER_ID,
975 ROUTE_ID,
976 DATA_TYPE,
977 PARAMETER_NAME,
978 SEQUENCE_NO )
979 select ff_route_parameters_s.nextval,
980 ff_routes_s.currval,
981 'N',
982 'User Entity ID',
983 1 from dual;
984
985 -- define the route_context usage
986
987 insert into ff_route_context_usages
988 (route_id,
989 context_id,
990 sequence_no)
991 select ff_routes_s.currval,
992 l_payroll_action_context_id,
993 1
994 from dual;
995
996 insert into ff_route_context_usages
997 (route_id,
998 context_id,
999 sequence_no)
1000 select ff_routes_s.currval,
1001 l_tax_unit_context_id,
1002 2
1003 from dual;
1004
1005 END;
1006
1007 BEGIN
1008
1009 -- define the employer tax unit id archive route
1010 -- Even though it is employer archive route, it
1011 -- will accept the context of assignment_action_id and
1012 -- for the given assignment_action_id , will get the
1013 -- payroll_action_id. This is being done because the
1014 -- mag reports will be setting up the assignment_action_id
1015 -- context as that of year end pre-process but the payroll_
1016 -- action_id that of the mag report.
1017
1018 hr_utility.trace('setting route text for EMPLOYER_TAX_UNIT_ARCHIVE');
1019
1020 l_text :=
1021 'ff_archive_item_contexts con2,
1022 ff_contexts fc2,
1023 ff_archive_items target
1024 where target.context1 = &B1
1025 /* context of payroll_action_id */
1026 and target.user_entity_id = &U1
1027 and fc2.context_name = ''TAX_UNIT_ID''
1028 and con2.archive_item_id = target.archive_item_id
1029 and con2.context_id = fc2.context_id
1030 and ltrim(rtrim(con2.context)) = to_char(&B2)
1031 /* 2nd context of tax_unit_id */';
1032
1033 hr_utility.trace('selecting route_id for EMPLOYER_TAX_UNIT_ARCHIVE');
1034
1035 select route_id into l_route_id
1036 from ff_routes where route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE';
1037
1038 hr_utility.trace('updating route_text for EMPLOYER_TAX_UNIT_ARCHIVE');
1039
1040 update ff_routes
1041 set text = l_text
1042 where route_id = l_route_id;
1043
1044 hr_utility.trace('deleting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE');
1045
1046 /* delete ff_route_context_usages
1047 where route_id = l_route_id; */
1048
1049 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE');
1050 open c_check_context(l_route_id,l_tax_unit_context_id);
1051 fetch c_check_context into l_exists;
1052 if c_check_context%NOTFOUND then
1053
1054
1055 insert into ff_route_context_usages
1056 (route_id,
1057 context_id,
1058 sequence_no)
1059 values (l_route_id,
1060 l_payroll_action_context_id,
1061 1);
1062
1063 hr_utility.trace('inserting ff_route_context_usages for EMPLOYER_TAX_UNIT_ARCHIVE');
1064
1065 insert into ff_route_context_usages
1066 (route_id,
1067 context_id,
1068 sequence_no)
1069 values (l_route_id,
1070 l_tax_unit_context_id,
1071 2 );
1072 end if;
1073 close c_check_context;
1074
1075
1076 EXCEPTION WHEN NO_DATA_FOUND THEN
1077 insert into ff_routes
1078 (route_id,
1079 route_name,
1080 user_defined_flag,
1081 description,
1082 text,
1083 last_update_date,
1084 last_updated_by,
1085 last_update_login,
1086 created_by,
1087 creation_date)
1088 values (ff_routes_s.nextval,
1089 'EMPLOYER_TAX_UNIT_ARCHIVE',
1090 'N',
1091 'sql to retrieve GRE based employer archived items',
1092 l_text,
1093 sysdate,
1094 0,
1095 0,
1096 0,
1097 sysdate);
1098
1099 -- define the route_parameter
1100 insert into ff_route_parameters
1101 (ROUTE_PARAMETER_ID,
1105 SEQUENCE_NO )
1102 ROUTE_ID,
1103 DATA_TYPE,
1104 PARAMETER_NAME,
1106 select ff_route_parameters_s.nextval,
1107 ff_routes_s.currval,
1108 'N',
1109 'User Entity ID',
1110 1 from dual;
1111
1112 -- define the route_context usage
1113
1114 insert into ff_route_context_usages
1115 (route_id,
1116 context_id,
1117 sequence_no)
1118 select ff_routes_s.currval,
1119 l_payroll_action_context_id,
1120 1
1121 from dual;
1122
1123 insert into ff_route_context_usages
1124 (route_id,
1125 context_id,
1126 sequence_no)
1127 select ff_routes_s.currval,
1128 l_tax_unit_context_id,
1129 2
1130 from dual;
1131
1132 END;
1133
1134
1135 BEGIN
1136
1137 -- define the employer jurisdiction code archive route
1138 -- Even though it is employer archive route, it
1139 -- will accept the context of assignment_action_id and
1140 -- for the given assignment_action_id , will get the
1141 -- payroll_action_id. This is being done because the
1142 -- mag reports will be setting up the assignment_action_id
1143 -- context as that of year end pre-process but the payroll_
1144 -- action_id that of the mag report.
1145
1146 l_text :=
1147 'ff_archive_item_contexts con3,
1148 ff_archive_item_contexts con2,
1149 ff_contexts fc3,
1150 ff_contexts fc2,
1151 ff_archive_items target
1152 where target.context1 = &B1
1153 /* context of payroll_action_id */
1154 and target.user_entity_id = &U1
1155 and fc2.context_name = ''TAX_UNIT_ID''
1156 and con2.archive_item_id = target.archive_item_id
1157 and con2.context_id = fc2.context_id
1158 and ltrim(rtrim(con2.context)) = to_char(&B2)
1159 /* 2nd context of tax_unit_id */
1160 and fc3.context_name = ''JURISDICTION_CODE''
1161 and con3.archive_item_id = target.archive_item_id
1162 and con3.context_id = fc3.context_id
1163 and substr(ltrim(rtrim(con3.context)),1,&U2) = substr(&B3,1,&U2)
1164 /* 3rd context of jurisdiction code */';
1165
1166 select route_id into l_route_id
1167 from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE';
1168
1169 update ff_routes
1170 set text = l_text
1171 where route_id = l_route_id;
1172
1173 /* delete ff_route_context_usages
1174 where route_id = l_route_id; */
1175
1176 open c_check_context(l_route_id,l_tax_unit_context_id);
1177 fetch c_check_context into l_exists;
1178 if c_check_context%NOTFOUND then
1179
1180
1181 insert into ff_route_context_usages
1182 (route_id,
1183 context_id,
1184 sequence_no)
1185 values (l_route_id,
1186 l_payroll_action_context_id,
1187 1);
1188
1189 insert into ff_route_context_usages
1190 (route_id,
1191 context_id,
1192 sequence_no)
1193 values (l_route_id,
1194 l_tax_unit_context_id,
1195 2);
1196
1197 insert into ff_route_context_usages
1198 (route_id,
1199 context_id,
1200 sequence_no)
1201 values (l_route_id,
1202 l_jurisdiction_context_id,
1203 3 );
1204 end if;
1205 close c_check_context;
1206
1207
1208 EXCEPTION WHEN NO_DATA_FOUND THEN
1209 insert into ff_routes
1210 (route_id,
1211 route_name,
1212 user_defined_flag,
1213 description,
1214 text,
1215 last_update_date,
1216 last_updated_by,
1217 last_update_login,
1218 created_by,
1219 creation_date)
1220 values (ff_routes_s.nextval,
1221 'EMPLOYER_JURSD_ARCHIVE',
1222 'N',
1223 'sql to retrieve GRE based employer archived items',
1224 l_text,
1225 sysdate,
1226 0,
1227 0,
1228 0,
1229 sysdate);
1230
1231 -- define the route_parameter
1232 insert into ff_route_parameters
1233 (ROUTE_PARAMETER_ID,
1234 ROUTE_ID,
1235 DATA_TYPE,
1236 PARAMETER_NAME,
1237 SEQUENCE_NO )
1238 select ff_route_parameters_s.nextval,
1239 ff_routes_s.currval,
1240 'N',
1241 'User Entity ID',
1242 1 from dual;
1243
1244 -- define the route_context usage
1245
1246 insert into ff_route_context_usages
1247 (route_id,
1248 context_id,
1249 sequence_no)
1250 select ff_routes_s.currval,
1251 l_payroll_action_context_id,
1252 1
1253 from dual;
1254
1258 sequence_no)
1255 insert into ff_route_context_usages
1256 (route_id,
1257 context_id,
1259 select ff_routes_s.currval,
1260 l_tax_unit_context_id,
1261 2
1262 from dual;
1263
1264 insert into ff_route_context_usages
1265 (route_id,
1266 context_id,
1267 sequence_no)
1268 select ff_routes_s.currval,
1269 l_jurisdiction_context_id,
1270 3
1271 from dual;
1272 END;
1273
1274
1275 BEGIN
1276
1277 -- define the employer jurisdiction code archive route
1278 -- Even though it is employer archive route, it
1279 -- will accept the context of assignment_action_id and
1280 -- for the given assignment_action_id , will get the
1281 -- payroll_action_id. This is being done because the
1282 -- mag reports will be setting up the assignment_action_id
1283 -- context as that of year end pre-process but the payroll_
1284 -- action_id that of the mag report.
1285
1286 l_text :=
1287 'ff_archive_item_contexts con3,
1288 ff_archive_item_contexts con2,
1289 ff_contexts fc3,
1290 ff_contexts fc2,
1291 ff_archive_items target
1292 where target.context1 = &B1
1293 /* context of payroll_action_id */
1294 and target.user_entity_id = &U1
1295 and fc2.context_name = ''TAX_UNIT_ID''
1296 and con2.archive_item_id = target.archive_item_id
1297 and con2.context_id = fc2.context_id
1298 and ltrim(rtrim(con2.context)) = to_char(&B2)
1299 /* 2nd context of tax_unit_id */
1300 and fc3.context_name = ''JURISDICTION_CODE''
1301 and con3.archive_item_id = target.archive_item_id
1302 and con3.context_id = fc3.context_id
1303 and substr(ltrim(rtrim(con3.context)),1,&U2) = substr(&B3,1,&U2)
1304 /* 3rd context of jurisdiction code */';
1305
1306 select route_id into l_route_id
1307 from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE_DATE';
1308
1309 update ff_routes
1310 set text = l_text
1311 where route_id = l_route_id;
1312
1313 /* delete ff_route_context_usages
1314 where route_id = l_route_id; */
1315
1316 open c_check_context(l_route_id,l_tax_unit_context_id);
1317 fetch c_check_context into l_exists;
1318 if c_check_context%NOTFOUND then
1319
1320
1321 insert into ff_route_context_usages
1322 (route_id,
1323 context_id,
1324 sequence_no)
1325 values (l_route_id,
1326 l_payroll_action_context_id,
1327 1);
1328
1329 insert into ff_route_context_usages
1330 (route_id,
1331 context_id,
1332 sequence_no)
1333 values (l_route_id,
1334 l_tax_unit_context_id,
1335 2);
1336
1337 insert into ff_route_context_usages
1338 (route_id,
1339 context_id,
1340 sequence_no)
1341 values (l_route_id,
1342 l_jurisdiction_context_id,
1343 3 );
1344 end if;
1345 close c_check_context;
1346
1347
1348 EXCEPTION WHEN NO_DATA_FOUND THEN
1349 insert into ff_routes
1350 (route_id,
1351 route_name,
1352 user_defined_flag,
1353 description,
1354 text,
1355 last_update_date,
1356 last_updated_by,
1357 last_update_login,
1358 created_by,
1359 creation_date)
1360 values (ff_routes_s.nextval,
1361 'EMPLOYER_JURSD_ARCHIVE_DATE',
1362 'N',
1363 'sql to retrieve GRE based employer archived items',
1364 l_text,
1365 sysdate,
1366 0,
1367 0,
1368 0,
1369 sysdate);
1370
1371 -- define the route_parameter
1372 insert into ff_route_parameters
1373 (ROUTE_PARAMETER_ID,
1374 ROUTE_ID,
1375 DATA_TYPE,
1376 PARAMETER_NAME,
1377 SEQUENCE_NO )
1378 select ff_route_parameters_s.nextval,
1379 ff_routes_s.currval,
1380 'N',
1381 'User Entity ID',
1382 1 from dual;
1383
1384 -- define the route_context usage
1385
1386 insert into ff_route_context_usages
1387 (route_id,
1388 context_id,
1389 sequence_no)
1390 select ff_routes_s.currval,
1391 l_payroll_action_context_id,
1392 1
1393 from dual;
1394
1395 insert into ff_route_context_usages
1396 (route_id,
1397 context_id,
1398 sequence_no)
1399 select ff_routes_s.currval,
1400 l_tax_unit_context_id,
1401 2
1402 from dual;
1403
1404 insert into ff_route_context_usages
1405 (route_id,
1409 l_jurisdiction_context_id,
1406 context_id,
1407 sequence_no)
1408 select ff_routes_s.currval,
1410 3
1411 from dual;
1412 END;
1413
1414
1415 BEGIN
1416
1417 -- define the employer jurisdiction code archive route
1418 -- Even though it is employer archive route, it
1419 -- will accept the context of assignment_action_id and
1420 -- for the given assignment_action_id , will get the
1421 -- payroll_action_id. This is being done because the
1422 -- mag reports will be setting up the assignment_action_id
1423 -- context as that of year end pre-process but the payroll_
1424 -- action_id that of the mag report.
1425
1426 l_text :=
1427 'ff_archive_item_contexts con3,
1428 ff_archive_item_contexts con2,
1429 ff_contexts fc3,
1430 ff_contexts fc2,
1431 ff_archive_items target
1432 where target.context1 = &B1
1433 /* context of payroll_action_id */
1434 and target.user_entity_id = &U1
1435 and fc2.context_name = ''TAX_UNIT_ID''
1436 and con2.archive_item_id = target.archive_item_id
1437 and con2.context_id = fc2.context_id
1438 and ltrim(rtrim(con2.context)) = to_char(&B2)
1439 /* 2nd context of tax_unit_id */
1440 and fc3.context_name = ''JURISDICTION_CODE''
1441 and con3.archive_item_id = target.archive_item_id
1442 and con3.context_id = fc3.context_id
1443 and substr(ltrim(rtrim(con3.context)),1,&U2) = substr(&B3,1,&U2)
1444 /* 3rd context of jurisdiction code */';
1445
1446 select route_id into l_route_id
1447 from ff_routes where route_name = 'EMPLOYER_JURSD_ARCHIVE_NUMBER';
1448
1449 update ff_routes
1450 set text = l_text
1451 where route_id = l_route_id;
1452
1453 /* delete ff_route_context_usages
1454 where route_id = l_route_id; */
1455
1456 open c_check_context(l_route_id,l_tax_unit_context_id);
1457 fetch c_check_context into l_exists;
1458 if c_check_context%NOTFOUND then
1459
1460
1461 insert into ff_route_context_usages
1462 (route_id,
1463 context_id,
1464 sequence_no)
1465 values (l_route_id,
1466 l_payroll_action_context_id,
1467 1);
1468
1469 insert into ff_route_context_usages
1470 (route_id,
1471 context_id,
1472 sequence_no)
1473 values (l_route_id,
1474 l_tax_unit_context_id,
1475 2);
1476
1477 insert into ff_route_context_usages
1478 (route_id,
1479 context_id,
1480 sequence_no)
1481 values (l_route_id,
1482 l_jurisdiction_context_id,
1483 3 );
1484 end if;
1485 close c_check_context;
1486
1487
1488 EXCEPTION WHEN NO_DATA_FOUND THEN
1489 insert into ff_routes
1490 (route_id,
1491 route_name,
1492 user_defined_flag,
1493 description,
1494 text,
1495 last_update_date,
1496 last_updated_by,
1497 last_update_login,
1498 created_by,
1499 creation_date)
1500 values (ff_routes_s.nextval,
1501 'EMPLOYER_JURSD_ARCHIVE_NUMBER',
1502 'N',
1503 'sql to retrieve GRE based employer archived items',
1504 l_text,
1505 sysdate,
1506 0,
1507 0,
1508 0,
1509 sysdate);
1510
1511 -- define the route_parameter
1512 insert into ff_route_parameters
1513 (ROUTE_PARAMETER_ID,
1514 ROUTE_ID,
1515 DATA_TYPE,
1516 PARAMETER_NAME,
1517 SEQUENCE_NO )
1518 select ff_route_parameters_s.nextval,
1519 ff_routes_s.currval,
1520 'N',
1521 'User Entity ID',
1522 1 from dual;
1523
1524 -- define the route_context usage
1525
1526 insert into ff_route_context_usages
1527 (route_id,
1528 context_id,
1529 sequence_no)
1530 select ff_routes_s.currval,
1531 l_payroll_action_context_id,
1532 1
1533 from dual;
1534
1535 insert into ff_route_context_usages
1536 (route_id,
1537 context_id,
1538 sequence_no)
1539 select ff_routes_s.currval,
1540 l_tax_unit_context_id,
1541 2
1542 from dual;
1543
1544 insert into ff_route_context_usages
1545 (route_id,
1546 context_id,
1547 sequence_no)
1548 select ff_routes_s.currval,
1549 l_jurisdiction_context_id,
1550 3
1551 from dual;
1552 END;
1553
1554 BEGIN
1555
1556 -- define the assignment archive route
1557 l_text :=
1561 /* context of assignment action id */';
1558 'ff_archive_items target
1559 where target.user_entity_id = &U1
1560 and target.context1 = to_char(&B1)
1562
1563 select route_id into l_route_id
1564 from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE';
1565
1566 update ff_routes
1567 set text = l_text
1568 where route_id = l_route_id;
1569
1570 EXCEPTION
1571 WHEN NO_DATA_FOUND THEN
1572
1573 insert into ff_routes
1574 (route_id,
1575 route_name,
1576 user_defined_flag,
1577 description,
1578 text,
1579 last_update_date,
1580 last_updated_by,
1581 last_update_login,
1582 created_by,
1583 creation_date)
1584 values (ff_routes_s.nextval,
1585 'ASSIGNMENT_ARCHIVE',
1586 'N',
1587 'sql to retrieve Assignment based archived items',
1588 l_text,
1589 sysdate,
1590 0,
1591 0,
1592 0,
1593 sysdate);
1594
1595 -- define the route_parameter
1596 insert into ff_route_parameters
1597 (ROUTE_PARAMETER_ID,
1598 ROUTE_ID,
1599 DATA_TYPE,
1600 PARAMETER_NAME,
1601 SEQUENCE_NO )
1602 select ff_route_parameters_s.nextval,
1603 ff_routes_s.currval,
1604 'N',
1605 'User Entity ID',
1606 1 from dual;
1607
1608 -- define the route_context usage
1609
1610 insert into ff_route_context_usages
1611 (route_id,
1612 context_id,
1613 sequence_no)
1614 select ff_routes_s.currval,
1615 l_assignment_action_context_id,
1616 1
1617 from dual;
1618
1619 END;
1620
1621 BEGIN
1622
1623 -- define the assignment archive route
1624 l_text :=
1625 'ff_archive_items target
1626 where target.user_entity_id = &U1
1627 and target.context1 = to_char(&B1)
1628 /* context of assignment action id */';
1629
1630 select route_id into l_route_id
1631 from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE_DATE';
1632
1633 update ff_routes
1634 set text = l_text
1635 where route_id = l_route_id;
1636
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639
1640 insert into ff_routes
1641 (route_id,
1642 route_name,
1643 user_defined_flag,
1644 description,
1645 text,
1646 last_update_date,
1647 last_updated_by,
1648 last_update_login,
1649 created_by,
1650 creation_date)
1651 values (ff_routes_s.nextval,
1652 'ASSIGNMENT_ARCHIVE_DATE',
1653 'N',
1654 'sql to retrieve Assignment based archived items',
1655 l_text,
1656 sysdate,
1657 0,
1658 0,
1659 0,
1660 sysdate);
1661
1662 -- define the route_parameter
1663 insert into ff_route_parameters
1664 (ROUTE_PARAMETER_ID,
1665 ROUTE_ID,
1666 DATA_TYPE,
1667 PARAMETER_NAME,
1668 SEQUENCE_NO )
1669 select ff_route_parameters_s.nextval,
1670 ff_routes_s.currval,
1671 'N',
1672 'User Entity ID',
1673 1 from dual;
1674
1675 -- define the route_context usage
1676
1677 insert into ff_route_context_usages
1678 (route_id,
1679 context_id,
1680 sequence_no)
1681 select ff_routes_s.currval,
1682 l_assignment_action_context_id,
1683 1
1684 from dual;
1685
1686 END;
1687
1688 BEGIN
1689
1690 -- define the assignment archive route
1691 l_text :=
1692 'ff_archive_items target
1693 where target.user_entity_id = &U1
1694 and target.context1 = to_char(&B1)
1695 /* context of assignment action id */';
1696
1697 select route_id into l_route_id
1698 from ff_routes where route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
1699
1700 update ff_routes
1701 set text = l_text
1702 where route_id = l_route_id;
1703
1704 EXCEPTION
1705 WHEN NO_DATA_FOUND THEN
1706
1707 insert into ff_routes
1708 (route_id,
1709 route_name,
1710 user_defined_flag,
1711 description,
1712 text,
1713 last_update_date,
1714 last_updated_by,
1715 last_update_login,
1716 created_by,
1717 creation_date)
1718 values (ff_routes_s.nextval,
1719 'ASSIGNMENT_ARCHIVE_NUMBER', 'N',
1720 'sql to retrieve Assignment based archived items',
1721 l_text,
1725 0,
1722 sysdate,
1723 0,
1724 0,
1726 sysdate);
1727
1728 -- define the route_parameter
1729 insert into ff_route_parameters
1730 (ROUTE_PARAMETER_ID,
1731 ROUTE_ID,
1732 DATA_TYPE,
1733 PARAMETER_NAME,
1734 SEQUENCE_NO )
1735 select ff_route_parameters_s.nextval,
1736 ff_routes_s.currval,
1737 'N',
1738 'User Entity ID',
1739 1 from dual;
1740
1741 -- define the route_context usage
1742
1743 insert into ff_route_context_usages
1744 (route_id,
1745 context_id,
1746 sequence_no)
1747 select ff_routes_s.currval,
1748 l_assignment_action_context_id,
1749 1
1750 from dual;
1751 END;
1752
1753 /* For EOY */
1754
1755 BEGIN
1756
1757 /* Define the archive route for the db item having
1758 assignment action id and tax unit id (GRE) as
1759 contexts. */
1760
1761 l_text :=
1762 'ff_archive_item_contexts con2,
1763 ff_contexts fc2,
1764 ff_archive_items target
1765 where target.user_entity_id = &U1
1766 and target.context1 = to_char(&B1)
1767 /* context assignment action id */
1768 and fc2.context_name = ''TAX_UNIT_ID''
1769 and con2.archive_item_id = target.archive_item_id
1770 and con2.context_id = fc2.context_id
1771 and ltrim(rtrim(con2.context)) = to_char(&B2)
1772 /* 2nd context of tax_unit_id */';
1773
1774 select route_id into l_route_id
1775 from ff_routes where route_name = 'ASSIGNMENT_GRE_ARCHIVE';
1776
1777 update ff_routes
1778 set text = l_text
1779 where route_id = l_route_id;
1780
1781 EXCEPTION
1782 WHEN NO_DATA_FOUND THEN
1783 insert into ff_routes
1784 (route_id,
1785 route_name,
1786 user_defined_flag,
1787 description,
1788 text,
1789 last_update_date,
1790 last_updated_by,
1791 last_update_login,
1792 created_by,
1793 creation_date)
1794 values (ff_routes_s.nextval,
1795 'ASSIGNMENT_GRE_ARCHIVE',
1796 'N',
1797 'sql to retrieve Assignment and GRE based archived items',
1798 l_text,
1799 sysdate,
1800 0,
1801 0,
1802 0,
1803 sysdate);
1804
1805 -- define the route_parameter
1806 insert into ff_route_parameters
1807 (ROUTE_PARAMETER_ID,
1808 ROUTE_ID,
1809 DATA_TYPE,
1810 PARAMETER_NAME,
1811 SEQUENCE_NO )
1812 values (ff_route_parameters_s.nextval,
1813 ff_routes_s.currval,
1814 'N',
1815 'User Entity ID',
1816 1);
1817
1818 -- define the route_context usage
1819
1820 insert into ff_route_context_usages
1821 (route_id,
1822 context_id,
1823 sequence_no)
1824 values (ff_routes_s.currval,
1825 l_assignment_action_context_id,
1826 1);
1827
1828 insert into ff_route_context_usages
1829 (route_id,
1830 context_id,
1831 sequence_no)
1832 values (ff_routes_s.currval,
1833 l_tax_unit_context_id,
1834 2);
1835
1836 END;
1837
1838 /* For EOY */
1839
1840
1841 BEGIN
1842
1843 /* Define the archive route for the db item having
1844 assignment action id and jurisdiction code as
1845 contexts. */
1846
1847 l_text :=
1848 'ff_archive_item_contexts con2,
1849 ff_contexts fc2,
1850 ff_archive_items target
1851 where target.user_entity_id = &U1
1852 and target.context1 = to_char(&B1)
1853 /* context assignment action id */
1854 and fc2.context_name = ''JURISDICTION_CODE''
1855 and con2.archive_item_id = target.archive_item_id
1856 and con2.context_id = fc2.context_id
1857 and substr(con2.context,1,&U2) = substr(&B2,1,&U2)
1858 /* 2nd context of jurisdiction code */';
1859
1860 select route_id into l_route_id
1861 from ff_routes where route_name = 'ASSIGNMENT_JD_ARCHIVE';
1862
1863 update ff_routes
1864 set text = l_text
1865 where route_id = l_route_id;
1866
1867 EXCEPTION
1868 WHEN NO_DATA_FOUND THEN
1869 insert into ff_routes
1870 (route_id,
1871 route_name,
1872 user_defined_flag,
1873 description,
1874 text,
1875 last_update_date,
1876 last_updated_by,
1877 last_update_login,
1878 created_by,
1879 creation_date)
1883 'sql to retrieve Assignment and JD based archived items',
1880 values (ff_routes_s.nextval,
1881 'ASSIGNMENT_JD_ARCHIVE',
1882 'N',
1884 l_text,
1885 sysdate,
1886 0,
1887 0,
1888 0,
1889 sysdate);
1890
1891 -- define the route_parameter
1892 insert into ff_route_parameters
1893 (ROUTE_PARAMETER_ID,
1894 ROUTE_ID,
1895 DATA_TYPE,
1896 PARAMETER_NAME,
1897 SEQUENCE_NO )
1898 values (ff_route_parameters_s.nextval,
1899 ff_routes_s.currval,
1900 'N',
1901 'User Entity ID',
1902 1);
1903
1904 -- define the route_context usage
1905
1906 insert into ff_route_context_usages
1907 (route_id,
1908 context_id,
1909 sequence_no)
1910 values (ff_routes_s.currval,
1911 l_assignment_action_context_id,
1912 1);
1913
1914 insert into ff_route_context_usages
1915 (route_id,
1916 context_id,
1917 sequence_no)
1918 values (ff_routes_s.currval,
1919 l_jurisdiction_context_id,
1920 2);
1921
1922 END;
1923 /* For EOY */
1924
1925 BEGIN
1926
1927 /* Define the archive route for the db item having
1928 assignment action id, tax unit id (GRE) and
1929 city jurisdiction code as contexts. */
1930
1931 l_text :=
1932 'ff_archive_item_contexts con3,
1933 ff_archive_item_contexts con2,
1934 ff_contexts fc3,
1935 ff_contexts fc2,
1936 ff_archive_items target
1937 where target.user_entity_id = &U1
1938 and target.context1 = to_char(&B1)
1939 /* context assignment action id */
1940 and fc2.context_name = ''TAX_UNIT_ID''
1941 and con2.archive_item_id = target.archive_item_id
1942 and con2.context_id = fc2.context_id
1943 and ltrim(rtrim(con2.context)) = to_char(&B2)
1944 /* 2nd context of tax_unit_id */
1945 and fc3.context_name = ''JURISDICTION_CODE''
1946 and con3.archive_item_id = target.archive_item_id
1947 and con3.context_id = fc3.context_id
1948 and ltrim(rtrim(con3.context)) = &B3
1949 /* 3rd context of city jurisdiction_code*/';
1950
1951 select route_id into l_route_id
1952 from ff_routes where route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
1953
1954 update ff_routes
1955 set text = l_text
1956 where route_id = l_route_id;
1957
1958 EXCEPTION
1959 WHEN NO_DATA_FOUND THEN
1960
1961 insert into ff_routes
1962 (route_id,
1963 route_name,
1964 user_defined_flag,
1965 description,
1966 text,
1967 last_update_date,
1968 last_updated_by,
1969 last_update_login,
1970 created_by,
1971 creation_date)
1972 values (ff_routes_s.nextval,
1973 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE',
1974 'N',
1975 'sql to retrieve Assignment,GRE and city JD based archived items',
1976 l_text,
1977 sysdate,
1978 0,
1979 0,
1980 0,
1981 sysdate);
1982
1983 -- define the route_parameter
1984 insert into ff_route_parameters
1985 (ROUTE_PARAMETER_ID,
1986 ROUTE_ID,
1987 DATA_TYPE,
1988 PARAMETER_NAME,
1989 SEQUENCE_NO )
1990 values (ff_route_parameters_s.nextval,
1991 ff_routes_s.currval,
1992 'N',
1993 'User Entity ID',
1994 1);
1995
1996 -- define the route_context usage
1997
1998 insert into ff_route_context_usages
1999 (route_id,
2000 context_id,
2001 sequence_no)
2002 values (ff_routes_s.currval,
2003 l_assignment_action_context_id,
2004 1);
2005
2006 insert into ff_route_context_usages
2007 (route_id,
2008 context_id,
2009 sequence_no)
2010 values (ff_routes_s.currval,
2011 l_tax_unit_context_id,
2012 2);
2013
2014 insert into ff_route_context_usages
2015 (route_id,
2016 context_id,
2017 sequence_no)
2018 values (ff_routes_s.currval,
2019 l_jurisdiction_context_id,
2020 3);
2021
2022
2023 END;
2024
2025 BEGIN
2026
2027 /* Define the archive route for the db item having
2028 assignment action id, tax unit id (GRE) and
2029 county jurisdiction code as contexts. */
2030
2031 l_text :=
2032 'ff_archive_item_contexts con3,
2033 ff_archive_item_contexts con2,
2034 ff_contexts fc3,
2035 ff_contexts fc2,
2039 /* context assignment action id */
2036 ff_archive_items target
2037 where target.user_entity_id = &U1
2038 and target.context1 = to_char(&B1)
2040 and fc2.context_name = ''TAX_UNIT_ID''
2041 and con2.archive_item_id = target.archive_item_id
2042 and con2.context_id = fc2.context_id
2043 and ltrim(rtrim(con2.context)) = to_char(&B2)
2044 /* 2nd context of tax_unit_id */
2045 and fc3.context_name = ''JURISDICTION_CODE''
2046 and con3.archive_item_id = target.archive_item_id
2047 and con3.context_id = fc3.context_id
2048 and substr(con3.context,1,6) = substr(&B3,1,6)
2049 /* 3rd context of county jurisdiction_code*/';
2050
2051 select route_id into l_route_id
2052 from ff_routes where route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
2053
2054 update ff_routes
2055 set text = l_text
2056 where route_id = l_route_id;
2057
2058 EXCEPTION
2059 WHEN NO_DATA_FOUND THEN
2060
2061 insert into ff_routes
2062 (route_id,
2063 route_name,
2064 user_defined_flag,
2065 description,
2066 text,
2067 last_update_date,
2068 last_updated_by,
2069 last_update_login,
2070 created_by,
2071 creation_date)
2072 values (ff_routes_s.nextval,
2073 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE',
2074 'N',
2075 'sql to retrieve Assignment,GRE and county JD based archived items',
2076 l_text,
2077 sysdate,
2078 0,
2079 0,
2080 0,
2081 sysdate);
2082
2083 -- define the route_parameter
2084 insert into ff_route_parameters
2085 (ROUTE_PARAMETER_ID,
2086 ROUTE_ID,
2087 DATA_TYPE,
2088 PARAMETER_NAME,
2089 SEQUENCE_NO )
2090 values (ff_route_parameters_s.nextval,
2091 ff_routes_s.currval,
2092 'N',
2093 'User Entity ID',
2094 1);
2095
2096 -- define the route_context usage
2097
2098 insert into ff_route_context_usages
2099 (route_id,
2100 context_id,
2101 sequence_no)
2102 values (ff_routes_s.currval,
2103 l_assignment_action_context_id,
2104 1);
2105
2106 insert into ff_route_context_usages
2107 (route_id,
2108 context_id,
2109 sequence_no)
2110 values (ff_routes_s.currval,
2111 l_tax_unit_context_id,
2112 2);
2113
2114 insert into ff_route_context_usages
2115 (route_id,
2116 context_id,
2117 sequence_no)
2118 values (ff_routes_s.currval,
2119 l_jurisdiction_context_id,
2120 3);
2121
2122 END;
2123
2124 BEGIN
2125
2126 /* Define the archive route for the db item having
2127 assignment action id, tax unit id (GRE) and
2128 state jurisdiction code as contexts. */
2129
2130 l_text :=
2131 'ff_archive_item_contexts con3,
2132 ff_archive_item_contexts con2,
2133 ff_contexts fc3,
2134 ff_contexts fc2,
2135 ff_archive_items target
2136 where target.user_entity_id = &U1
2137 and target.context1 = to_char(&B1)
2138 /* context assignment action id */
2139 and fc2.context_name = ''TAX_UNIT_ID''
2140 and con2.archive_item_id = target.archive_item_id
2141 and con2.context_id = fc2.context_id
2142 and ltrim(rtrim(con2.context)) = to_char(&B2)
2143 /* 2nd context of tax_unit_id */
2144 and fc3.context_name = ''JURISDICTION_CODE''
2145 and con3.archive_item_id = target.archive_item_id
2146 and con3.context_id = fc3.context_id
2147 and substr(con3.context,1,2) = substr(&B3,1,2)
2148 /* 3rd context of state jurisdiction_code*/';
2149
2150 select route_id into l_route_id
2151 from ff_routes where route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
2152
2153 update ff_routes
2154 set text = l_text
2155 where route_id = l_route_id;
2156
2157 EXCEPTION
2158 WHEN NO_DATA_FOUND THEN
2159
2160 insert into ff_routes
2161 (route_id,
2162 route_name,
2163 user_defined_flag,
2164 description,
2165 text,
2166 last_update_date,
2167 last_updated_by,
2168 last_update_login,
2169 created_by,
2170 creation_date)
2171 values (ff_routes_s.nextval,
2172 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE',
2173 'N',
2174 'sql to retrieve Assignment,GRE and state JD based archived items',
2175 l_text,
2176 sysdate,
2177 0,
2178 0,
2179 0,
2180 sysdate);
2181
2182 -- define the route_parameter
2186 DATA_TYPE,
2183 insert into ff_route_parameters
2184 (ROUTE_PARAMETER_ID,
2185 ROUTE_ID,
2187 PARAMETER_NAME,
2188 SEQUENCE_NO )
2189 values (ff_route_parameters_s.nextval,
2190 ff_routes_s.currval,
2191 'N',
2192 'User Entity ID',
2193 1);
2194
2195 -- define the route_context usage
2196
2197 insert into ff_route_context_usages
2198 (route_id,
2199 context_id,
2200 sequence_no)
2201 values (ff_routes_s.currval,
2202 l_assignment_action_context_id,
2203 1);
2204
2205 insert into ff_route_context_usages
2206 (route_id,
2207 context_id,
2208 sequence_no)
2209 values (ff_routes_s.currval,
2210 l_tax_unit_context_id,
2211 2);
2212
2213 insert into ff_route_context_usages
2214 (route_id,
2215 context_id,
2216 sequence_no)
2217 values (ff_routes_s.currval,
2218 l_jurisdiction_context_id,
2219 3);
2220
2221 END;
2222
2223 BEGIN
2224
2225 /* Define the archive route for the db item having
2226 assignment action id, tax unit id (GRE) and
2227 school district jurisdiction code as contexts. */
2228
2229 l_text :=
2230 'ff_archive_item_contexts con3,
2231 ff_archive_item_contexts con2,
2232 ff_contexts fc3,
2233 ff_contexts fc2,
2234 ff_archive_items target
2235 where target.user_entity_id = &U1
2236 and target.context1 = to_char(&B1)
2237 /* context assignment action id */
2238 and fc2.context_name = ''TAX_UNIT_ID''
2239 and con2.archive_item_id = target.archive_item_id
2240 and con2.context_id = fc2.context_id
2241 and ltrim(rtrim(con2.context)) = to_char(&B2)
2242 /* 2nd context of tax_unit_id */
2243 and fc3.context_name = ''JURISDICTION_CODE''
2244 and con3.archive_item_id = target.archive_item_id
2245 and con3.context_id = fc3.context_id
2246 and substr(con3.context,1,8) = substr(&B3,1,8)
2247 /* 3rd context of scholl jurisdiction_code*/';
2248
2249 select route_id into l_route_id
2250 from ff_routes where route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
2251
2252 update ff_routes
2253 set text = l_text
2254 where route_id = l_route_id;
2255
2256 EXCEPTION
2257 WHEN NO_DATA_FOUND THEN
2258
2259 insert into ff_routes
2260 (route_id,
2261 route_name,
2262 user_defined_flag,
2263 description,
2264 text,
2265 last_update_date,
2266 last_updated_by,
2267 last_update_login,
2268 created_by,
2269 creation_date)
2270 values (ff_routes_s.nextval,
2271 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE',
2272 'N',
2273 'sql to retrieve Assignment,GRE and school JD based archived items',
2274 l_text,
2275 sysdate,
2276 0,
2277 0,
2278 0,
2279 sysdate);
2280
2281 -- define the route_parameter
2282 insert into ff_route_parameters
2283 (ROUTE_PARAMETER_ID,
2284 ROUTE_ID,
2285 DATA_TYPE,
2286 PARAMETER_NAME,
2287 SEQUENCE_NO )
2288 values (ff_route_parameters_s.nextval,
2289 ff_routes_s.currval,
2290 'N',
2291 'User Entity ID',
2292 1);
2293
2294 -- define the route_context usage
2295
2296 insert into ff_route_context_usages
2297 (route_id,
2298 context_id,
2299 sequence_no)
2300 values (ff_routes_s.currval,
2301 l_assignment_action_context_id,
2302 1);
2303
2304 insert into ff_route_context_usages
2305 (route_id,
2306 context_id,
2307 sequence_no)
2308 values (ff_routes_s.currval,
2309 l_tax_unit_context_id,
2310 2);
2311
2312 insert into ff_route_context_usages
2313 (route_id,
2314 context_id,
2315 sequence_no)
2316 values (ff_routes_s.currval,
2317 l_jurisdiction_context_id,
2318 3);
2319
2320
2321 END;
2322
2323 end create_archive_route; -- ]
2324
2325
2326 PROCEDURE create_archive_dbi ( p_item_name VARCHAR2 ) is
2327 -- find the attributes from the live database item and create an
2328 -- arcive version of it
2329 l_dbi_null_allowed_flag VARCHAR2(1);
2330 l_dbi_description VARCHAR2(240);
2331 l_dbi_data_type VARCHAR2(1);
2332 l_ue_notfound_allowed_flag VARCHAR2(1);
2333 l_ue_creator_type VARCHAR2(30);
2334 l_ue_entity_description VARCHAR2(240);
2338 l_route_id NUMBER;
2335 l_user_entity_seq NUMBER;
2336 l_user_entity_id NUMBER;
2337 l_route_parameter_id NUMBER;
2339 l_live_route_id NUMBER;
2340 l_er_archive_route_id NUMBER;
2341 l_er_archive_date_route_id NUMBER;
2342 l_er_archive_number_route_id NUMBER;
2343 l_ass_archive_route_id NUMBER;
2344 l_ass_archive_date_route_id NUMBER;
2345 l_ass_archive_number_route_id NUMBER;
2346 l_asg_count NUMBER;
2347 l_definition_text VARCHAR2(240);
2348
2349 begin -- [
2350 begin -- [
2351 select ue.notfound_allowed_flag,
2352 ue.creator_type,
2353 ue.entity_description,
2354 ue.route_id,
2355 dbi.null_allowed_flag,
2356 dbi.description ,
2357 dbi.data_type
2358 into l_ue_notfound_allowed_flag,
2359 l_ue_creator_type,
2360 l_ue_entity_description,
2361 l_live_route_id,
2362 l_dbi_null_allowed_flag,
2363 l_dbi_description,
2364 l_dbi_data_type
2365 from ff_database_items dbi,
2366 ff_user_entities ue
2367 where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
2368 and dbi.user_entity_id = ue.user_entity_id;
2369 -- and ue.business_group_id is null;
2370 end; -- ]
2371 --
2372 select count(1) into l_asg_count from ff_route_context_usages rc,
2373 ff_contexts c
2374 where rc.context_id = c.context_id
2375 and rc.route_id = l_live_route_id
2376 and context_name like 'ASSIGNMENT%';
2377
2378 select ff_user_entities_s.nextval into l_user_entity_seq
2379 from dual;
2380
2381
2382 select route_id into l_er_archive_route_id
2383 from ff_routes where
2384 route_name = 'EMPLOYER_ARCHIVE';
2385
2386 select route_id into l_er_archive_date_route_id
2387 from ff_routes where
2388 route_name = 'EMPLOYER_ARCHIVE_DATE';
2389
2390 select route_id into l_er_archive_number_route_id
2391 from ff_routes where
2392 route_name = 'EMPLOYER_ARCHIVE_NUMBER';
2393
2394 select route_id into l_ass_archive_route_id
2395 from ff_routes where
2396 route_name = 'ASSIGNMENT_ARCHIVE';
2397
2398 select route_id into l_ass_archive_date_route_id
2399 from ff_routes where
2400 route_name = 'ASSIGNMENT_ARCHIVE_DATE';
2401
2402 select route_id into l_ass_archive_number_route_id
2403 from ff_routes where
2404 route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
2405
2406 if l_dbi_data_type = 'N' then
2407 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
2408 if l_asg_count = 0 then
2409 l_route_id := l_er_archive_number_route_id;
2410 else l_route_id := l_ass_archive_number_route_id;
2411 end if;
2412 elsif l_dbi_data_type = 'D' then
2413 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
2414 if l_asg_count = 0 then
2415 l_route_id := l_er_archive_date_route_id;
2416 else l_route_id := l_ass_archive_date_route_id;
2417 end if;
2418 else l_definition_text := 'target.value';
2419 if l_asg_count = 0 then
2420 l_route_id := l_er_archive_route_id;
2421 else l_route_id := l_ass_archive_route_id;
2422 end if;
2423 end if;
2424
2425 select ROUTE_PARAMETER_ID into l_route_parameter_id
2426 from ff_route_parameters
2427 where parameter_name = 'User Entity ID'
2428 and route_id = l_route_id;
2429
2430 insert into ff_user_entities
2431 (user_entity_id,
2432 business_group_id,
2433 legislation_code,
2434 route_id,
2435 notfound_allowed_flag,
2436 user_entity_name,
2437 creator_id,
2438 creator_type,
2439 entity_description,
2440 last_update_date,
2441 last_updated_by,
2442 last_update_login,
2443 created_by,
2444 creation_date)
2445 values( l_user_entity_seq, /* user_entity_id */
2446 null, /* business_group_id */
2447 'US', /* legislation_code */
2448 l_route_id, /* route_id */
2449 l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
2450 p_item_name, /* user_entity_name */
2451 0, /* creator_id */
2452 'X', /* archive extract creator_type */
2453 substr('Archive of '||l_ue_creator_type||' entity '||
2454 l_ue_entity_description,1,240),/* entity_description */
2455 sysdate, /* last_update_date */
2456 0, /* last_updated_by */
2457 0, /* last_update_login */
2458 0, /* created_by */
2459 sysdate); /* creation_date */
2460
2461 insert into ff_route_parameter_values (
2462 route_parameter_id,
2463 user_entity_id,
2464 value,
2465 last_update_date,
2469 creation_date)
2466 last_updated_by,
2467 last_update_login,
2468 created_by,
2470 --
2471 values( l_route_parameter_id,
2472 l_user_entity_seq,
2473 l_user_entity_seq,
2474 sysdate,
2475 0,
2476 0,
2477 0,
2478 sysdate);
2479
2480 insert into ff_database_items (
2481 user_name,
2482 user_entity_id,
2483 data_type,
2484 definition_text,
2485 null_allowed_flag,
2486 description,
2487 last_update_date,
2488 last_updated_by,
2489 last_update_login,
2490 created_by,
2491 creation_date)
2492 --
2493 values( p_item_name,
2494 l_user_entity_seq,
2495 l_dbi_data_type,
2496 l_definition_text,
2497 l_dbi_null_allowed_flag,
2498 substr('Archive of item '||l_dbi_description,1,240),
2499 sysdate,
2500 0,
2501 0,
2502 0,
2503 sysdate);
2504
2505 end create_archive_dbi; -- ]
2506
2507
2508 PROCEDURE create_eoy_archive_dbi ( p_item_name VARCHAR2 ) is
2509
2510 /* Find the attributes from the live database item and create an
2511 archive version of it. */
2512
2513 l_dbi_null_allowed_flag VARCHAR2(1);
2514 l_dbi_description VARCHAR2(240);
2515 l_dbi_data_type VARCHAR2(1);
2516 l_ue_notfound_allowed_flag VARCHAR2(1);
2517 l_ue_creator_type VARCHAR2(30);
2518 l_ue_entity_description VARCHAR2(240);
2519 l_user_entity_seq NUMBER;
2520 l_user_entity_id NUMBER;
2521 l_route_parameter_id NUMBER;
2522 l_route_id NUMBER;
2523 l_exist_route_id NUMBER;
2524 l_live_route_id NUMBER;
2525 l_er_archive_route_id NUMBER;
2526 l_er_archive_date_route_id NUMBER;
2527 l_er_archive_number_route_id NUMBER;
2528 l_ass_archive_route_id NUMBER;
2529 l_ass_archive_date_route_id NUMBER;
2530 l_ass_archive_number_route_id NUMBER;
2531 l_asg_count NUMBER;
2532 l_definition_text VARCHAR2(240);
2533 l_context_name VARCHAR2(240);
2534 l_context_id NUMBER;
2535 l_sequence_no NUMBER;
2536 l_count NUMBER := 0;
2537 lt_context_name char240_data_type_table;
2538 lt_context_id numeric_data_type_table;
2539 lt_sequence_no numeric_data_type_table;
2540 l_ass_gre_archive_route_id NUMBER;
2541 l_ass_jd_archive_route_id NUMBER;
2542 l_ass_gre_jd_archive_route_id NUMBER;
2543 l_ass_gre_ct_jd_arch_route_id NUMBER;
2544 l_ass_gre_cn_jd_arch_route_id NUMBER;
2545 l_ass_gre_st_jd_arch_route_id NUMBER;
2546 l_ass_gre_sd_jd_arch_route_id NUMBER;
2547 l_found_asg_action BOOLEAN := FALSE;
2548 l_found_tax_unit BOOLEAN := FALSE;
2549 l_found_jursd BOOLEAN := FALSE;
2550 l_live_bal_db_id number;
2551 l_jursd_level number;
2552 l_er_tax_unit_arch_rid number;
2553 l_er_tax_unit_arch_date_rid number;
2554 l_er_tax_unit_arch_number_rid number;
2555 l_er_jursd_arch_rid number;
2556 l_er_jursd_arch_date_rid number;
2557 l_er_jursd_arch_number_rid number;
2558
2559 cursor get_live_db_details is
2560 select ue.notfound_allowed_flag,
2561 ue.creator_type,
2562 ue.entity_description,
2563 ue.route_id,
2564 dbi.null_allowed_flag,
2565 dbi.description ,
2566 dbi.data_type
2567 from ff_database_items dbi,
2568 ff_user_entities ue
2569 where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
2570 and dbi.user_entity_id = ue.user_entity_id;
2571
2572 cursor get_context is
2573 select c.context_name,
2574 rc.context_id,
2575 rc.sequence_no
2576 from ff_route_context_usages rc,
2577 ff_contexts c
2578 where rc.context_id = c.context_id
2579 and rc.route_id= l_live_route_id
2580 order by 3;
2581
2582 cursor get_user_entity is
2583 select user_entity_id,
2584 route_id
2585 from ff_user_entities
2586 where user_entity_name = p_item_name;
2587
2588 cursor csr_get_jursd_level (p_defined_balance_id number) is
2589 select jurisdiction_level
2590 from pay_balance_types pbt,
2591 pay_defined_balances pdb
2592 where pbt.balance_type_id = pdb.balance_type_id
2593 and pdb.defined_balance_id = p_defined_balance_id;
2594
2595 begin
2596
2597 -- hr_utility.trace_on(null,'ORACLE');
2598
2602 from ff_routes where
2599 hr_utility.trace('getting route ids');
2600
2601 select route_id into l_er_archive_route_id
2603 route_name = 'EMPLOYER_ARCHIVE';
2604
2605 select route_id into l_er_archive_date_route_id
2606 from ff_routes where
2607 route_name = 'EMPLOYER_ARCHIVE_DATE';
2608
2609 select route_id into l_er_archive_number_route_id
2610 from ff_routes where
2611 route_name = 'EMPLOYER_ARCHIVE_NUMBER';
2612
2613 select route_id into l_er_tax_unit_arch_rid
2614 from ff_routes where
2615 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE';
2616
2617 select route_id into l_er_tax_unit_arch_date_rid
2618 from ff_routes where
2619 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE';
2620
2621 select route_id into l_er_tax_unit_arch_number_rid
2622 from ff_routes where
2623 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER';
2624
2625 select route_id into l_er_jursd_arch_rid
2626 from ff_routes where
2627 route_name = 'EMPLOYER_JURSD_ARCHIVE';
2628
2629 select route_id into l_er_jursd_arch_date_rid
2630 from ff_routes where
2631 route_name = 'EMPLOYER_JURSD_ARCHIVE_DATE';
2632
2633 select route_id into l_er_jursd_arch_number_rid
2634 from ff_routes where
2635 route_name = 'EMPLOYER_JURSD_ARCHIVE_NUMBER';
2636
2637 select route_id into l_ass_archive_route_id
2638 from ff_routes where
2639 route_name = 'ASSIGNMENT_ARCHIVE';
2640
2641 select route_id into l_ass_archive_date_route_id
2642 from ff_routes where
2643 route_name = 'ASSIGNMENT_ARCHIVE_DATE';
2644
2645 select route_id into l_ass_archive_number_route_id
2646 from ff_routes where
2647 route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
2648
2649 select route_id into l_ass_gre_archive_route_id
2650 from ff_routes where
2651 route_name = 'ASSIGNMENT_GRE_ARCHIVE';
2652
2653 select route_id into l_ass_jd_archive_route_id
2654 from ff_routes where
2655 route_name = 'ASSIGNMENT_JD_ARCHIVE';
2656
2657 /*
2658 select route_id into l_ass_gre_jd_archive_route_id
2659 from ff_routes where
2660 route_name = 'ASSIGNMENT_GRE_JD_ARCHIVE';
2661 */
2662 select route_id into l_ass_gre_ct_jd_arch_route_id
2663 from ff_routes where
2664 route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
2665
2666 select route_id into l_ass_gre_cn_jd_arch_route_id
2667 from ff_routes where
2668 route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
2669
2670 select route_id into l_ass_gre_st_jd_arch_route_id
2671 from ff_routes where
2672 route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
2673
2674 select route_id into l_ass_gre_sd_jd_arch_route_id
2675 from ff_routes where
2676 route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
2677
2678 hr_utility.trace('got route');
2679
2680 open get_live_db_details;
2681 fetch get_live_db_details into
2682 l_ue_notfound_allowed_flag,
2683 l_ue_creator_type,
2684 l_ue_entity_description,
2685 l_live_route_id,
2686 l_dbi_null_allowed_flag,
2687 l_dbi_description,
2688 l_dbi_data_type ;
2689
2690 if get_live_db_details%NOTFOUND then
2691
2692 close get_live_db_details;
2693 hr_utility.trace('Live database item does not exist : ' ||
2694 SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2695 raise_application_error(-20008,'Live database item does not exist : '
2696 ||SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2697
2698 else
2699 hr_utility.trace('processing database item : ' || p_item_name);
2700
2701 open get_context;
2702 loop
2703 fetch get_context into l_context_name,
2704 l_context_id,
2705 l_sequence_no;
2706
2707 exit when get_context%NOTFOUND;
2708 if l_context_name = 'ASSIGNMENT_ACTION_ID' or
2709 l_context_name = 'ASSIGNMENT_ID' then
2710 hr_utility.trace ('Assignment action id context found');
2711 l_found_asg_action := TRUE;
2712 elsif l_context_name = 'TAX_UNIT_ID' then
2713 hr_utility.trace ('Tax Unit id context found');
2714 l_found_tax_unit := TRUE;
2715 elsif l_context_name = 'JURISDICTION_CODE' then
2716 hr_utility.trace ('Jurisdiction code context found');
2717 l_found_jursd := TRUE;
2718 end if;
2719 /*
2720 l_count := l_count + 1;
2721 lt_context_name(l_count) := l_context_name;
2722 lt_context_id(l_count) := l_context_id;
2723 lt_sequence_no(l_count) := l_sequence_no;
2724 */
2725 end loop;
2726 close get_context;
2727
2728
2729 /* Form the definition text depending upon the data type
2730 of the database item */
2731
2732 if l_dbi_data_type = 'N' then
2733 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
2737 l_definition_text := 'target.value';
2734 elsif l_dbi_data_type = 'D' then
2735 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
2736 else
2738 end if;
2739
2740 if l_found_asg_action and l_found_tax_unit and l_found_jursd then
2741
2742 /* get the defined balance id of the live database item */
2743
2744 l_live_bal_db_id := pay_us_magtape_reporting.bal_db_item(SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2745 /* Now get the jurisdiction level of the balance */
2746
2747 open csr_get_jursd_level(l_live_bal_db_id);
2748 fetch csr_get_jursd_level into l_jursd_level;
2749 if csr_get_jursd_level%NOTFOUND then
2750 l_jursd_level := 0;
2751 end if;
2752 close csr_get_jursd_level;
2753
2754 if l_jursd_level = 11 then
2755 /* city level jurisdiction */
2756 l_route_id := l_ass_gre_ct_jd_arch_route_id;
2757 elsif l_jursd_level = 6 then
2758 /* county level jurisdiction */
2759 l_route_id := l_ass_gre_cn_jd_arch_route_id;
2760 elsif l_jursd_level = 2 then
2761 /* city state jurisdiction */
2762 l_route_id := l_ass_gre_st_jd_arch_route_id;
2763 elsif l_jursd_level = 8 then
2764 /* school level jurisdiction */
2765 l_route_id := l_ass_gre_sd_jd_arch_route_id;
2766 end if;
2767
2768 elsif l_found_asg_action and l_found_tax_unit and
2769 not(l_found_jursd) then
2770
2771 l_route_id := l_ass_gre_archive_route_id;
2772
2773 elsif l_found_asg_action and not(l_found_tax_unit) and
2774 l_found_jursd then
2775
2776 l_route_id := l_ass_jd_archive_route_id;
2777
2778 elsif l_found_asg_action and not(l_found_tax_unit) and
2779 not(l_found_jursd) then
2780
2781 if l_dbi_data_type = 'N' then
2782 l_route_id := l_ass_archive_number_route_id;
2783 elsif l_dbi_data_type = 'D' then
2784 l_route_id := l_ass_archive_date_route_id;
2785 else
2786 l_route_id := l_ass_archive_route_id;
2787 end if;
2788
2789 elsif not(l_found_asg_action) and not(l_found_tax_unit) and
2790 not(l_found_jursd) then
2791
2792 if l_dbi_data_type = 'N' then
2793 l_route_id := l_er_archive_number_route_id;
2794 elsif l_dbi_data_type = 'D' then
2795 l_route_id := l_er_archive_date_route_id;
2796 else
2797 l_route_id := l_er_archive_route_id;
2798 end if;
2799
2800 elsif not(l_found_asg_action) and l_found_tax_unit and
2801 not(l_found_jursd) then
2802
2803 if l_dbi_data_type = 'N' then
2804 l_route_id := l_er_tax_unit_arch_number_rid;
2805 elsif l_dbi_data_type = 'D' then
2806 l_route_id := l_er_tax_unit_arch_date_rid;
2807 else
2808 l_route_id := l_er_tax_unit_arch_rid;
2809 end if;
2810
2811 elsif not(l_found_asg_action) and l_found_tax_unit and
2812 l_found_jursd then
2813
2814 if l_dbi_data_type = 'N' then
2815 l_route_id := l_er_jursd_arch_number_rid;
2816 elsif l_dbi_data_type = 'D' then
2817 l_route_id := l_er_jursd_arch_date_rid;
2818 else
2819 l_route_id := l_er_jursd_arch_rid;
2820 end if;
2821
2822 elsif not(l_found_asg_action) and not(l_found_tax_unit) and
2823 l_found_jursd then
2824
2825 if l_dbi_data_type = 'N' then
2826 l_route_id := l_er_jursd_arch_number_rid;
2827 elsif l_dbi_data_type = 'D' then
2828 l_route_id := l_er_jursd_arch_date_rid;
2829 else
2830 l_route_id := l_er_jursd_arch_rid;
2831 end if;
2832
2833 end if;
2834
2835
2836 hr_utility.trace('getting route parameter id for ' || to_char(l_route_id));
2837
2838 select ROUTE_PARAMETER_ID into l_route_parameter_id
2839 from ff_route_parameters
2840 where parameter_name = 'User Entity ID'
2841 and route_id = l_route_id;
2842
2843 hr_utility.trace('got route parameter id');
2844
2845
2846 open get_user_entity;
2847 fetch get_user_entity into l_user_entity_id,
2848 l_exist_route_id;
2849 if get_user_entity%FOUND then
2850
2851 /* Update the route id if required */
2852 if l_route_id <> l_exist_route_id then
2853
2854 hr_utility.trace ('Existing Route id : '||
2855 to_char(l_exist_route_id));
2856 hr_utility.trace ('New Route id : '||
2857 to_char(l_route_id));
2858 hr_utility.trace ('User Entity id : '||
2859 to_char(l_user_entity_id));
2860 update ff_user_entities
2864 update ff_route_parameter_values
2861 set route_id = l_route_id
2862 where user_entity_id = l_user_entity_id;
2863
2865 set route_parameter_id = l_route_parameter_id
2866 where user_entity_id = l_user_entity_id;
2867
2868 end if;
2869
2870 else
2871
2872 select ff_user_entities_s.nextval
2873 into l_user_entity_seq
2874 from dual;
2875
2876 insert into ff_user_entities
2877 (user_entity_id,
2878 business_group_id,
2879 legislation_code,
2880 route_id,
2881 notfound_allowed_flag,
2882 user_entity_name,
2883 creator_id,
2884 creator_type,
2885 entity_description,
2886 last_update_date,
2887 last_updated_by,
2888 last_update_login,
2889 created_by,
2890 creation_date)
2891 values( l_user_entity_seq, /* user_entity_id */
2892 null, /* business_group_id */
2893 'US', /* legislation_code */
2894 l_route_id, /* route_id */
2895 l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
2896 p_item_name, /* user_entity_name */
2897 0, /* creator_id */
2898 'X', /* archive extract creator_type */
2899 substr('Archive of '||l_ue_creator_type||' entity '||
2900 l_ue_entity_description,1,240),/* entity_description */
2901 sysdate, /* last_update_date */
2902 0, /* last_updated_by */
2903 0, /* last_update_login */
2904 0, /* created_by */
2905 sysdate); /* creation_date */
2906
2907 insert into ff_route_parameter_values (
2908 route_parameter_id,
2909 user_entity_id,
2910 value,
2911 last_update_date,
2912 last_updated_by,
2913 last_update_login,
2914 created_by,
2915 creation_date)
2916 values( l_route_parameter_id,
2917 l_user_entity_seq,
2918 l_user_entity_seq,
2919 sysdate,
2920 0,
2921 0,
2922 0,
2923 sysdate);
2924
2925 insert into ff_database_items (
2926 user_name,
2927 user_entity_id,
2928 data_type,
2929 definition_text,
2930 null_allowed_flag,
2931 description,
2932 last_update_date,
2933 last_updated_by,
2934 last_update_login,
2935 created_by,
2936 creation_date)
2937 values( p_item_name,
2938 l_user_entity_seq,
2939 l_dbi_data_type,
2940 l_definition_text,
2941 l_dbi_null_allowed_flag,
2942 substr('Archive of item '||l_dbi_description,1,240),
2943 sysdate,
2944 0,
2945 0,
2946 0,
2947 sysdate);
2948 end if;
2949 close get_user_entity;
2950
2951 end if;
2952
2953 end create_eoy_archive_dbi; -- ]
2954
2955 end py_w2_dbitems; --