[Home] [Help]
PACKAGE BODY: APPS.PY_W2_DBITEMS
Source
1 package body py_w2_dbitems as
2 /* $Header: pymagdbi.pkb 120.1.12020000.3 2012/07/24 14:04:42 pracagra ship $ */
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',
484 'sql to retrieve GRE based archived items',
485 l_text,
486 sysdate,
487 0,
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,
648 ff_archive_items target
649 where target.user_entity_id = &U1
650 and target.context1 = to_char(&B1)
651 /* context of payroll action id */
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,
801 context_id,
802 sequence_no)
803 values (l_route_id,
804 l_payroll_action_context_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,
952 route_name,
953 user_defined_flag,
954 description,
955 text,
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,
1102 ROUTE_ID,
1103 DATA_TYPE,
1104 PARAMETER_NAME,
1105 SEQUENCE_NO )
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
1255 insert into ff_route_context_usages
1256 (route_id,
1257 context_id,
1258 sequence_no)
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,
1406 context_id,
1407 sequence_no)
1408 select ff_routes_s.currval,
1409 l_jurisdiction_context_id,
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 :=
1558 'ff_archive_items target
1559 where target.user_entity_id = &U1
1560 and target.context1 = to_char(&B1)
1561 /* context of assignment action id */';
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,
1722 sysdate,
1723 0,
1724 0,
1725 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)
1880 values (ff_routes_s.nextval,
1881 'ASSIGNMENT_JD_ARCHIVE',
1882 'N',
1883 'sql to retrieve Assignment and JD based archived items',
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 and length(con3.context) = 11 -- Added for bug 11712075
1950 /* 3rd context of city jurisdiction_code*/';
1951
1952 select route_id into l_route_id
1953 from ff_routes where route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
1954
1955 update ff_routes
1956 set text = l_text
1957 where route_id = l_route_id;
1958
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961
1962 insert into ff_routes
1963 (route_id,
1964 route_name,
1965 user_defined_flag,
1966 description,
1967 text,
1968 last_update_date,
1969 last_updated_by,
1970 last_update_login,
1971 created_by,
1972 creation_date)
1973 values (ff_routes_s.nextval,
1974 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE',
1975 'N',
1976 'sql to retrieve Assignment,GRE and city JD based archived items',
1977 l_text,
1978 sysdate,
1979 0,
1980 0,
1981 0,
1982 sysdate);
1983
1984 -- define the route_parameter
1985 insert into ff_route_parameters
1986 (ROUTE_PARAMETER_ID,
1987 ROUTE_ID,
1988 DATA_TYPE,
1989 PARAMETER_NAME,
1990 SEQUENCE_NO )
1991 values (ff_route_parameters_s.nextval,
1992 ff_routes_s.currval,
1993 'N',
1994 'User Entity ID',
1995 1);
1996
1997 -- define the route_context usage
1998
1999 insert into ff_route_context_usages
2000 (route_id,
2001 context_id,
2002 sequence_no)
2003 values (ff_routes_s.currval,
2004 l_assignment_action_context_id,
2005 1);
2006
2007 insert into ff_route_context_usages
2008 (route_id,
2009 context_id,
2010 sequence_no)
2011 values (ff_routes_s.currval,
2012 l_tax_unit_context_id,
2013 2);
2014
2015 insert into ff_route_context_usages
2016 (route_id,
2017 context_id,
2018 sequence_no)
2019 values (ff_routes_s.currval,
2020 l_jurisdiction_context_id,
2021 3);
2022
2023
2024 END;
2025
2026 BEGIN
2027
2028 /* Define the archive route for the db item having
2029 assignment action id, tax unit id (GRE) and
2030 county jurisdiction code as contexts. */
2031
2032 l_text :=
2033 'ff_archive_item_contexts con3,
2034 ff_archive_item_contexts con2,
2035 ff_contexts fc3,
2036 ff_contexts fc2,
2037 ff_archive_items target
2038 where target.user_entity_id = &U1
2039 and target.context1 = to_char(&B1)
2040 /* context assignment action id */
2041 and fc2.context_name = ''TAX_UNIT_ID''
2042 and con2.archive_item_id = target.archive_item_id
2043 and con2.context_id = fc2.context_id
2044 and ltrim(rtrim(con2.context)) = to_char(&B2)
2045 /* 2nd context of tax_unit_id */
2046 and fc3.context_name = ''JURISDICTION_CODE''
2047 and con3.archive_item_id = target.archive_item_id
2048 and con3.context_id = fc3.context_id
2049 and substr(con3.context,1,6) = substr(&B3,1,6)
2050 and length(con3.context) = 11 -- Added for bug 11712075
2051 /* 3rd context of county jurisdiction_code*/';
2052
2053 select route_id into l_route_id
2054 from ff_routes where route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
2055
2056 update ff_routes
2057 set text = l_text
2058 where route_id = l_route_id;
2059
2060 EXCEPTION
2061 WHEN NO_DATA_FOUND THEN
2062
2063 insert into ff_routes
2064 (route_id,
2065 route_name,
2066 user_defined_flag,
2067 description,
2068 text,
2069 last_update_date,
2070 last_updated_by,
2071 last_update_login,
2072 created_by,
2073 creation_date)
2074 values (ff_routes_s.nextval,
2075 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE',
2076 'N',
2077 'sql to retrieve Assignment,GRE and county JD based archived items',
2078 l_text,
2079 sysdate,
2080 0,
2081 0,
2082 0,
2083 sysdate);
2084
2085 -- define the route_parameter
2086 insert into ff_route_parameters
2087 (ROUTE_PARAMETER_ID,
2088 ROUTE_ID,
2089 DATA_TYPE,
2090 PARAMETER_NAME,
2091 SEQUENCE_NO )
2092 values (ff_route_parameters_s.nextval,
2093 ff_routes_s.currval,
2094 'N',
2095 'User Entity ID',
2096 1);
2097
2098 -- define the route_context usage
2099
2100 insert into ff_route_context_usages
2101 (route_id,
2102 context_id,
2103 sequence_no)
2104 values (ff_routes_s.currval,
2105 l_assignment_action_context_id,
2106 1);
2107
2108 insert into ff_route_context_usages
2109 (route_id,
2110 context_id,
2111 sequence_no)
2112 values (ff_routes_s.currval,
2113 l_tax_unit_context_id,
2114 2);
2115
2116 insert into ff_route_context_usages
2117 (route_id,
2118 context_id,
2119 sequence_no)
2120 values (ff_routes_s.currval,
2121 l_jurisdiction_context_id,
2122 3);
2123
2124 END;
2125
2126 BEGIN
2127
2128 /* Define the archive route for the db item having
2129 assignment action id, tax unit id (GRE) and
2130 state jurisdiction code as contexts. */
2131
2132 l_text :=
2133 'ff_archive_item_contexts con3,
2134 ff_archive_item_contexts con2,
2135 ff_contexts fc3,
2136 ff_contexts fc2,
2137 ff_archive_items target
2138 where target.user_entity_id = &U1
2139 and target.context1 = to_char(&B1)
2140 /* context assignment action id */
2141 and fc2.context_name = ''TAX_UNIT_ID''
2142 and con2.archive_item_id = target.archive_item_id
2143 and con2.context_id = fc2.context_id
2144 and ltrim(rtrim(con2.context)) = to_char(&B2)
2145 /* 2nd context of tax_unit_id */
2146 and fc3.context_name = ''JURISDICTION_CODE''
2147 and con3.archive_item_id = target.archive_item_id
2148 and con3.context_id = fc3.context_id
2149 and substr(con3.context,1,2) = substr(&B3,1,2)
2150 and length(con3.context) = 11 -- Added for bug 11712075
2151 /* 3rd context of state jurisdiction_code*/';
2152
2153 select route_id into l_route_id
2154 from ff_routes where route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
2155
2156 update ff_routes
2157 set text = l_text
2158 where route_id = l_route_id;
2159
2160 EXCEPTION
2161 WHEN NO_DATA_FOUND THEN
2162
2163 insert into ff_routes
2164 (route_id,
2165 route_name,
2166 user_defined_flag,
2167 description,
2168 text,
2169 last_update_date,
2170 last_updated_by,
2171 last_update_login,
2172 created_by,
2173 creation_date)
2174 values (ff_routes_s.nextval,
2175 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE',
2176 'N',
2177 'sql to retrieve Assignment,GRE and state JD based archived items',
2178 l_text,
2179 sysdate,
2180 0,
2181 0,
2182 0,
2183 sysdate);
2184
2185 -- define the route_parameter
2186 insert into ff_route_parameters
2187 (ROUTE_PARAMETER_ID,
2188 ROUTE_ID,
2189 DATA_TYPE,
2190 PARAMETER_NAME,
2191 SEQUENCE_NO )
2192 values (ff_route_parameters_s.nextval,
2193 ff_routes_s.currval,
2194 'N',
2195 'User Entity ID',
2196 1);
2197
2198 -- define the route_context usage
2199
2200 insert into ff_route_context_usages
2201 (route_id,
2202 context_id,
2203 sequence_no)
2204 values (ff_routes_s.currval,
2205 l_assignment_action_context_id,
2206 1);
2207
2208 insert into ff_route_context_usages
2209 (route_id,
2210 context_id,
2211 sequence_no)
2212 values (ff_routes_s.currval,
2213 l_tax_unit_context_id,
2214 2);
2215
2216 insert into ff_route_context_usages
2217 (route_id,
2218 context_id,
2219 sequence_no)
2220 values (ff_routes_s.currval,
2221 l_jurisdiction_context_id,
2222 3);
2223
2224 END;
2225
2226 BEGIN
2227
2228 /* Define the archive route for the db item having
2229 assignment action id, tax unit id (GRE) and
2230 school district jurisdiction code as contexts. */
2231
2232 l_text :=
2233 'ff_archive_item_contexts con3,
2234 ff_archive_item_contexts con2,
2235 ff_contexts fc3,
2236 ff_contexts fc2,
2237 ff_archive_items target
2238 where target.user_entity_id = &U1
2239 and target.context1 = to_char(&B1)
2240 /* context assignment action id */
2241 and fc2.context_name = ''TAX_UNIT_ID''
2242 and con2.archive_item_id = target.archive_item_id
2243 and con2.context_id = fc2.context_id
2244 and ltrim(rtrim(con2.context)) = to_char(&B2)
2245 /* 2nd context of tax_unit_id */
2246 and fc3.context_name = ''JURISDICTION_CODE''
2247 and con3.archive_item_id = target.archive_item_id
2248 and con3.context_id = fc3.context_id
2249 and substr(con3.context,1,8) = substr(&B3,1,8)
2250 and length(con3.context) = 8 -- Added for bug 11712075
2251 /* 3rd context of scholl jurisdiction_code*/';
2252
2253 select route_id into l_route_id
2254 from ff_routes where route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
2255
2256 update ff_routes
2257 set text = l_text
2258 where route_id = l_route_id;
2259
2260 EXCEPTION
2261 WHEN NO_DATA_FOUND THEN
2262
2263 insert into ff_routes
2264 (route_id,
2265 route_name,
2266 user_defined_flag,
2267 description,
2268 text,
2269 last_update_date,
2270 last_updated_by,
2271 last_update_login,
2272 created_by,
2273 creation_date)
2274 values (ff_routes_s.nextval,
2275 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE',
2276 'N',
2277 'sql to retrieve Assignment,GRE and school JD based archived items',
2278 l_text,
2279 sysdate,
2280 0,
2281 0,
2282 0,
2283 sysdate);
2284
2285 -- define the route_parameter
2286 insert into ff_route_parameters
2287 (ROUTE_PARAMETER_ID,
2288 ROUTE_ID,
2289 DATA_TYPE,
2290 PARAMETER_NAME,
2291 SEQUENCE_NO )
2292 values (ff_route_parameters_s.nextval,
2293 ff_routes_s.currval,
2294 'N',
2295 'User Entity ID',
2296 1);
2297
2298 -- define the route_context usage
2299
2300 insert into ff_route_context_usages
2301 (route_id,
2302 context_id,
2303 sequence_no)
2304 values (ff_routes_s.currval,
2305 l_assignment_action_context_id,
2306 1);
2307
2308 insert into ff_route_context_usages
2309 (route_id,
2310 context_id,
2311 sequence_no)
2312 values (ff_routes_s.currval,
2313 l_tax_unit_context_id,
2314 2);
2315
2316 insert into ff_route_context_usages
2317 (route_id,
2318 context_id,
2319 sequence_no)
2320 values (ff_routes_s.currval,
2321 l_jurisdiction_context_id,
2322 3);
2323
2324
2325 END;
2326
2327 /* Changes for bug 11712075 - Start*/
2328
2329 BEGIN
2330
2331 /* Define the archive route for the db item having
2332 assignment action id, tax unit id (GRE) and
2333 PSD jurisdiction code as contexts. */
2334
2335 l_text :=
2336 'ff_archive_item_contexts con3,
2337 ff_archive_item_contexts con2,
2338 ff_contexts fc3,
2339 ff_contexts fc2,
2340 ff_archive_items target
2341 where target.user_entity_id = &U1
2342 and target.context1 = to_char(&B1)
2343 /* context assignment action id */
2344 and fc2.context_name = ''TAX_UNIT_ID''
2345 and con2.archive_item_id = target.archive_item_id
2346 and con2.context_id = fc2.context_id
2347 and ltrim(rtrim(con2.context)) = to_char(&B2)
2348 /* 2nd context of tax_unit_id */
2349 and fc3.context_name = ''JURISDICTION_CODE''
2350 and con3.archive_item_id = target.archive_item_id
2351 and con3.context_id = fc3.context_id
2352 and ltrim(rtrim(con3.context)) = &B3
2353 and length(con3.context) = 16
2354 /* 3rd context of scholl jurisdiction_code*/';
2355
2356 select route_id into l_route_id
2357 from ff_routes where route_name = 'ASSIGNMENT_GRE_PSD_JD_ARCHIVE';
2358
2359 update ff_routes
2360 set text = l_text
2361 where route_id = l_route_id;
2362
2363 EXCEPTION
2364 WHEN NO_DATA_FOUND THEN
2365
2366 insert into ff_routes
2367 (route_id,
2368 route_name,
2369 user_defined_flag,
2370 description,
2371 text,
2372 last_update_date,
2373 last_updated_by,
2374 last_update_login,
2375 created_by,
2376 creation_date)
2377 values (ff_routes_s.nextval,
2378 'ASSIGNMENT_GRE_PSD_JD_ARCHIVE',
2379 'N',
2380 'sql to retrieve Assignment,GRE and PSD JD based archived items',
2381 l_text,
2382 sysdate,
2383 0,
2384 0,
2385 0,
2386 sysdate);
2387
2388 -- define the route_parameter
2389 insert into ff_route_parameters
2390 (ROUTE_PARAMETER_ID,
2391 ROUTE_ID,
2392 DATA_TYPE,
2393 PARAMETER_NAME,
2394 SEQUENCE_NO )
2395 values (ff_route_parameters_s.nextval,
2396 ff_routes_s.currval,
2397 'N',
2398 'User Entity ID',
2399 1);
2400
2401 -- define the route_context usage
2402
2403 insert into ff_route_context_usages
2404 (route_id,
2405 context_id,
2406 sequence_no)
2407 values (ff_routes_s.currval,
2408 l_assignment_action_context_id,
2409 1);
2410
2411 insert into ff_route_context_usages
2412 (route_id,
2413 context_id,
2414 sequence_no)
2415 values (ff_routes_s.currval,
2416 l_tax_unit_context_id,
2417 2);
2418
2419 insert into ff_route_context_usages
2420 (route_id,
2421 context_id,
2422 sequence_no)
2423 values (ff_routes_s.currval,
2424 l_jurisdiction_context_id,
2425 3);
2426
2427
2428 END;
2429
2430 /* Changes for bug 11712075 - End*/
2431
2432 end create_archive_route; -- ]
2433
2434
2435 PROCEDURE create_archive_dbi ( p_item_name VARCHAR2 ) is
2436 -- find the attributes from the live database item and create an
2437 -- arcive version of it
2438 l_dbi_null_allowed_flag VARCHAR2(1);
2439 l_dbi_description VARCHAR2(240);
2440 l_dbi_data_type VARCHAR2(1);
2441 l_ue_notfound_allowed_flag VARCHAR2(1);
2442 l_ue_creator_type VARCHAR2(30);
2443 l_ue_entity_description VARCHAR2(240);
2444 l_user_entity_seq NUMBER;
2445 l_user_entity_id NUMBER;
2446 l_route_parameter_id NUMBER;
2447 l_route_id NUMBER;
2448 l_live_route_id NUMBER;
2449 l_er_archive_route_id NUMBER;
2450 l_er_archive_date_route_id NUMBER;
2451 l_er_archive_number_route_id NUMBER;
2452 l_ass_archive_route_id NUMBER;
2453 l_ass_archive_date_route_id NUMBER;
2454 l_ass_archive_number_route_id NUMBER;
2455 l_asg_count NUMBER;
2456 l_definition_text VARCHAR2(240);
2457
2458 begin -- [
2459 begin -- [
2460 select ue.notfound_allowed_flag,
2461 ue.creator_type,
2462 ue.entity_description,
2463 ue.route_id,
2464 dbi.null_allowed_flag,
2465 dbi.description ,
2466 dbi.data_type
2467 into l_ue_notfound_allowed_flag,
2468 l_ue_creator_type,
2469 l_ue_entity_description,
2470 l_live_route_id,
2471 l_dbi_null_allowed_flag,
2472 l_dbi_description,
2473 l_dbi_data_type
2474 from ff_database_items dbi,
2475 ff_user_entities ue
2476 where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
2477 and dbi.user_entity_id = ue.user_entity_id;
2478 -- and ue.business_group_id is null;
2479 end; -- ]
2480 --
2481 select count(1) into l_asg_count from ff_route_context_usages rc,
2482 ff_contexts c
2483 where rc.context_id = c.context_id
2484 and rc.route_id = l_live_route_id
2485 and context_name like 'ASSIGNMENT%';
2486
2487 select ff_user_entities_s.nextval into l_user_entity_seq
2488 from dual;
2489
2490
2491 select route_id into l_er_archive_route_id
2492 from ff_routes where
2493 route_name = 'EMPLOYER_ARCHIVE';
2494
2495 select route_id into l_er_archive_date_route_id
2496 from ff_routes where
2497 route_name = 'EMPLOYER_ARCHIVE_DATE';
2498
2499 select route_id into l_er_archive_number_route_id
2500 from ff_routes where
2501 route_name = 'EMPLOYER_ARCHIVE_NUMBER';
2502
2503 select route_id into l_ass_archive_route_id
2504 from ff_routes where
2505 route_name = 'ASSIGNMENT_ARCHIVE';
2506
2507 select route_id into l_ass_archive_date_route_id
2508 from ff_routes where
2509 route_name = 'ASSIGNMENT_ARCHIVE_DATE';
2510
2511 select route_id into l_ass_archive_number_route_id
2512 from ff_routes where
2513 route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
2514
2515 if l_dbi_data_type = 'N' then
2516 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
2517 if l_asg_count = 0 then
2518 l_route_id := l_er_archive_number_route_id;
2519 else l_route_id := l_ass_archive_number_route_id;
2520 end if;
2521 elsif l_dbi_data_type = 'D' then
2522 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
2523 if l_asg_count = 0 then
2524 l_route_id := l_er_archive_date_route_id;
2525 else l_route_id := l_ass_archive_date_route_id;
2526 end if;
2527 else l_definition_text := 'target.value';
2528 if l_asg_count = 0 then
2529 l_route_id := l_er_archive_route_id;
2530 else l_route_id := l_ass_archive_route_id;
2531 end if;
2532 end if;
2533
2534 select ROUTE_PARAMETER_ID into l_route_parameter_id
2535 from ff_route_parameters
2536 where parameter_name = 'User Entity ID'
2537 and route_id = l_route_id;
2538
2539 insert into ff_user_entities
2540 (user_entity_id,
2541 business_group_id,
2542 legislation_code,
2543 route_id,
2544 notfound_allowed_flag,
2545 user_entity_name,
2546 creator_id,
2547 creator_type,
2548 entity_description,
2549 last_update_date,
2550 last_updated_by,
2551 last_update_login,
2552 created_by,
2553 creation_date)
2554 values( l_user_entity_seq, /* user_entity_id */
2555 null, /* business_group_id */
2556 'US', /* legislation_code */
2557 l_route_id, /* route_id */
2558 l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
2559 p_item_name, /* user_entity_name */
2560 0, /* creator_id */
2561 'X', /* archive extract creator_type */
2562 substr('Archive of '||l_ue_creator_type||' entity '||
2563 l_ue_entity_description,1,240),/* entity_description */
2564 sysdate, /* last_update_date */
2565 0, /* last_updated_by */
2566 0, /* last_update_login */
2567 0, /* created_by */
2568 sysdate); /* creation_date */
2569
2570 insert into ff_route_parameter_values (
2571 route_parameter_id,
2572 user_entity_id,
2573 value,
2574 last_update_date,
2575 last_updated_by,
2576 last_update_login,
2577 created_by,
2578 creation_date)
2579 --
2580 values( l_route_parameter_id,
2581 l_user_entity_seq,
2582 l_user_entity_seq,
2583 sysdate,
2584 0,
2585 0,
2586 0,
2587 sysdate);
2588
2589 insert into ff_database_items (
2590 user_name,
2591 user_entity_id,
2592 data_type,
2593 definition_text,
2594 null_allowed_flag,
2595 description,
2596 last_update_date,
2597 last_updated_by,
2598 last_update_login,
2599 created_by,
2600 creation_date)
2601 --
2602 values( p_item_name,
2603 l_user_entity_seq,
2604 l_dbi_data_type,
2605 l_definition_text,
2606 l_dbi_null_allowed_flag,
2607 substr('Archive of item '||l_dbi_description,1,240),
2608 sysdate,
2609 0,
2610 0,
2611 0,
2612 sysdate);
2613
2614 end create_archive_dbi; -- ]
2615
2616
2617 PROCEDURE create_eoy_archive_dbi ( p_item_name VARCHAR2 ) is
2618
2619 /* Find the attributes from the live database item and create an
2620 archive version of it. */
2621
2622 l_dbi_null_allowed_flag VARCHAR2(1);
2623 l_dbi_description VARCHAR2(240);
2624 l_dbi_data_type VARCHAR2(1);
2625 l_ue_notfound_allowed_flag VARCHAR2(1);
2626 l_ue_creator_type VARCHAR2(30);
2627 l_ue_entity_description VARCHAR2(240);
2628 l_user_entity_seq NUMBER;
2629 l_user_entity_id NUMBER;
2630 l_route_parameter_id NUMBER;
2631 l_route_id NUMBER;
2632 l_exist_route_id NUMBER;
2633 l_live_route_id NUMBER;
2634 l_er_archive_route_id NUMBER;
2635 l_er_archive_date_route_id NUMBER;
2636 l_er_archive_number_route_id NUMBER;
2637 l_ass_archive_route_id NUMBER;
2638 l_ass_archive_date_route_id NUMBER;
2639 l_ass_archive_number_route_id NUMBER;
2640 l_asg_count NUMBER;
2641 l_definition_text VARCHAR2(240);
2642 l_context_name VARCHAR2(240);
2643 l_context_id NUMBER;
2644 l_sequence_no NUMBER;
2645 l_count NUMBER := 0;
2646 lt_context_name char240_data_type_table;
2647 lt_context_id numeric_data_type_table;
2648 lt_sequence_no numeric_data_type_table;
2649 l_ass_gre_archive_route_id NUMBER;
2650 l_ass_jd_archive_route_id NUMBER;
2651 l_ass_gre_jd_archive_route_id NUMBER;
2652 l_ass_gre_ct_jd_arch_route_id NUMBER;
2653 l_ass_gre_cn_jd_arch_route_id NUMBER;
2654 l_ass_gre_st_jd_arch_route_id NUMBER;
2655 l_ass_gre_sd_jd_arch_route_id NUMBER;
2656 l_ass_gre_psd_jd_arch_route_id NUMBER; -- Added for bug 11712075
2657 l_found_asg_action BOOLEAN := FALSE;
2658 l_found_tax_unit BOOLEAN := FALSE;
2659 l_found_jursd BOOLEAN := FALSE;
2660 l_live_bal_db_id number;
2661 l_jursd_level number;
2662 l_er_tax_unit_arch_rid number;
2663 l_er_tax_unit_arch_date_rid number;
2664 l_er_tax_unit_arch_number_rid number;
2665 l_er_jursd_arch_rid number;
2666 l_er_jursd_arch_date_rid number;
2667 l_er_jursd_arch_number_rid number;
2668
2669 cursor get_live_db_details is
2670 select ue.notfound_allowed_flag,
2671 ue.creator_type,
2672 ue.entity_description,
2673 ue.route_id,
2674 dbi.null_allowed_flag,
2675 dbi.description ,
2676 dbi.data_type
2677 from ff_database_items dbi,
2678 ff_user_entities ue
2679 where dbi.user_name = SUBSTR(p_item_name,3,LENGTH(p_item_name)-2)
2680 and dbi.user_entity_id = ue.user_entity_id;
2681
2682 cursor get_context is
2683 select c.context_name,
2684 rc.context_id,
2685 rc.sequence_no
2686 from ff_route_context_usages rc,
2687 ff_contexts c
2688 where rc.context_id = c.context_id
2689 and rc.route_id= l_live_route_id
2690 order by 3;
2691
2692 cursor get_user_entity is
2693 select user_entity_id,
2694 route_id
2695 from ff_user_entities
2696 where user_entity_name = p_item_name;
2697
2698 cursor csr_get_jursd_level (p_defined_balance_id number) is
2699 select jurisdiction_level
2700 from pay_balance_types pbt,
2701 pay_defined_balances pdb
2702 where pbt.balance_type_id = pdb.balance_type_id
2703 and pdb.defined_balance_id = p_defined_balance_id;
2704
2705 begin
2706
2707 -- hr_utility.trace_on(null,'ORACLE');
2708
2709 hr_utility.trace('getting route ids');
2710
2711 select route_id into l_er_archive_route_id
2712 from ff_routes where
2713 route_name = 'EMPLOYER_ARCHIVE';
2714
2715 select route_id into l_er_archive_date_route_id
2716 from ff_routes where
2717 route_name = 'EMPLOYER_ARCHIVE_DATE';
2718
2719 select route_id into l_er_archive_number_route_id
2720 from ff_routes where
2721 route_name = 'EMPLOYER_ARCHIVE_NUMBER';
2722
2723 select route_id into l_er_tax_unit_arch_rid
2724 from ff_routes where
2725 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE';
2726
2727 select route_id into l_er_tax_unit_arch_date_rid
2728 from ff_routes where
2729 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_DATE';
2730
2731 select route_id into l_er_tax_unit_arch_number_rid
2732 from ff_routes where
2733 route_name = 'EMPLOYER_TAX_UNIT_ARCHIVE_NUMBER';
2734
2735 select route_id into l_er_jursd_arch_rid
2736 from ff_routes where
2737 route_name = 'EMPLOYER_JURSD_ARCHIVE';
2738
2739 select route_id into l_er_jursd_arch_date_rid
2740 from ff_routes where
2741 route_name = 'EMPLOYER_JURSD_ARCHIVE_DATE';
2742
2743 select route_id into l_er_jursd_arch_number_rid
2744 from ff_routes where
2745 route_name = 'EMPLOYER_JURSD_ARCHIVE_NUMBER';
2746
2747 select route_id into l_ass_archive_route_id
2748 from ff_routes where
2749 route_name = 'ASSIGNMENT_ARCHIVE';
2750
2751 select route_id into l_ass_archive_date_route_id
2752 from ff_routes where
2753 route_name = 'ASSIGNMENT_ARCHIVE_DATE';
2754
2755 select route_id into l_ass_archive_number_route_id
2756 from ff_routes where
2757 route_name = 'ASSIGNMENT_ARCHIVE_NUMBER';
2758
2759 select route_id into l_ass_gre_archive_route_id
2760 from ff_routes where
2761 route_name = 'ASSIGNMENT_GRE_ARCHIVE';
2762
2763 select route_id into l_ass_jd_archive_route_id
2764 from ff_routes where
2765 route_name = 'ASSIGNMENT_JD_ARCHIVE';
2766
2767 /*
2768 select route_id into l_ass_gre_jd_archive_route_id
2769 from ff_routes where
2770 route_name = 'ASSIGNMENT_GRE_JD_ARCHIVE';
2771 */
2772 select route_id into l_ass_gre_ct_jd_arch_route_id
2773 from ff_routes where
2774 route_name = 'ASSIGNMENT_GRE_CITY_JD_ARCHIVE';
2775
2776 select route_id into l_ass_gre_cn_jd_arch_route_id
2777 from ff_routes where
2778 route_name = 'ASSIGNMENT_GRE_COUNTY_JD_ARCHIVE';
2779
2780 select route_id into l_ass_gre_st_jd_arch_route_id
2781 from ff_routes where
2782 route_name = 'ASSIGNMENT_GRE_STATE_JD_ARCHIVE';
2783
2784 select route_id into l_ass_gre_sd_jd_arch_route_id
2785 from ff_routes where
2786 route_name = 'ASSIGNMENT_GRE_SCHOOL_JD_ARCHIVE';
2787
2788 /* Changes for bug 11712075 - Start*/
2789 select route_id into l_ass_gre_psd_jd_arch_route_id
2790 from ff_routes where
2791 route_name = 'ASSIGNMENT_GRE_PSD_JD_ARCHIVE';
2792 /* Changes for bug 11712075 - End*/
2793
2794 hr_utility.trace('got route');
2795
2796 open get_live_db_details;
2797 fetch get_live_db_details into
2798 l_ue_notfound_allowed_flag,
2799 l_ue_creator_type,
2800 l_ue_entity_description,
2801 l_live_route_id,
2802 l_dbi_null_allowed_flag,
2803 l_dbi_description,
2804 l_dbi_data_type ;
2805
2806 if get_live_db_details%NOTFOUND then
2807
2808 close get_live_db_details;
2809 hr_utility.trace('Live database item does not exist : ' ||
2810 SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2811 raise_application_error(-20008,'Live database item does not exist : '
2812 ||SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2813
2814 else
2815 hr_utility.trace('processing database item : ' || p_item_name);
2816
2817 open get_context;
2818 loop
2819 fetch get_context into l_context_name,
2820 l_context_id,
2821 l_sequence_no;
2822
2823 exit when get_context%NOTFOUND;
2824 if l_context_name = 'ASSIGNMENT_ACTION_ID' or
2825 l_context_name = 'ASSIGNMENT_ID' then
2826 hr_utility.trace ('Assignment action id context found');
2827 l_found_asg_action := TRUE;
2828 elsif l_context_name = 'TAX_UNIT_ID' then
2829 hr_utility.trace ('Tax Unit id context found');
2830 l_found_tax_unit := TRUE;
2831 elsif l_context_name = 'JURISDICTION_CODE' then
2832 hr_utility.trace ('Jurisdiction code context found');
2833 l_found_jursd := TRUE;
2834 end if;
2835 /*
2836 l_count := l_count + 1;
2837 lt_context_name(l_count) := l_context_name;
2838 lt_context_id(l_count) := l_context_id;
2839 lt_sequence_no(l_count) := l_sequence_no;
2840 */
2841 end loop;
2842 close get_context;
2843
2844
2845 /* Form the definition text depending upon the data type
2846 of the database item */
2847
2848 if l_dbi_data_type = 'N' then
2849 l_definition_text := 'fnd_number.canonical_to_number(target.value)';
2850 elsif l_dbi_data_type = 'D' then
2851 l_definition_text := 'fnd_date.canonical_to_date(target.value)';
2852 else
2853 l_definition_text := 'target.value';
2854 end if;
2855
2856 if l_found_asg_action and l_found_tax_unit and l_found_jursd then
2857
2858 /* get the defined balance id of the live database item */
2859
2860 l_live_bal_db_id := pay_us_magtape_reporting.bal_db_item(SUBSTR(p_item_name,3,LENGTH(p_item_name)-2));
2861 /* Now get the jurisdiction level of the balance */
2862
2863 open csr_get_jursd_level(l_live_bal_db_id);
2864 fetch csr_get_jursd_level into l_jursd_level;
2865 if csr_get_jursd_level%NOTFOUND then
2866 l_jursd_level := 0;
2867 end if;
2868 close csr_get_jursd_level;
2869
2870 if l_jursd_level = 11 then
2871 /* city level jurisdiction */
2872 l_route_id := l_ass_gre_ct_jd_arch_route_id;
2873 elsif l_jursd_level = 6 then
2874 /* county level jurisdiction */
2875 l_route_id := l_ass_gre_cn_jd_arch_route_id;
2876 elsif l_jursd_level = 2 then
2877 /* city state jurisdiction */
2878 l_route_id := l_ass_gre_st_jd_arch_route_id;
2879 elsif l_jursd_level = 8 then
2880 /* school level jurisdiction */
2881 l_route_id := l_ass_gre_sd_jd_arch_route_id;
2882 elsif l_jursd_level = 16 then -- Added for bug 11712075
2883 /* PSD level jurisdiction */
2884 l_route_id := l_ass_gre_psd_jd_arch_route_id;
2885 end if;
2886
2887 elsif l_found_asg_action and l_found_tax_unit and
2888 not(l_found_jursd) then
2889
2890 l_route_id := l_ass_gre_archive_route_id;
2891
2892 elsif l_found_asg_action and not(l_found_tax_unit) and
2893 l_found_jursd then
2894
2895 l_route_id := l_ass_jd_archive_route_id;
2896
2897 elsif l_found_asg_action and not(l_found_tax_unit) and
2898 not(l_found_jursd) then
2899
2900 if l_dbi_data_type = 'N' then
2901 l_route_id := l_ass_archive_number_route_id;
2902 elsif l_dbi_data_type = 'D' then
2903 l_route_id := l_ass_archive_date_route_id;
2904 else
2905 l_route_id := l_ass_archive_route_id;
2906 end if;
2907
2908 elsif not(l_found_asg_action) and not(l_found_tax_unit) and
2909 not(l_found_jursd) then
2910
2911 if l_dbi_data_type = 'N' then
2912 l_route_id := l_er_archive_number_route_id;
2913 elsif l_dbi_data_type = 'D' then
2914 l_route_id := l_er_archive_date_route_id;
2915 else
2916 l_route_id := l_er_archive_route_id;
2917 end if;
2918
2919 elsif not(l_found_asg_action) and l_found_tax_unit and
2920 not(l_found_jursd) then
2921
2922 if l_dbi_data_type = 'N' then
2923 l_route_id := l_er_tax_unit_arch_number_rid;
2924 elsif l_dbi_data_type = 'D' then
2925 l_route_id := l_er_tax_unit_arch_date_rid;
2926 else
2927 l_route_id := l_er_tax_unit_arch_rid;
2928 end if;
2929
2930 elsif not(l_found_asg_action) and l_found_tax_unit and
2931 l_found_jursd then
2932
2933 if l_dbi_data_type = 'N' then
2934 l_route_id := l_er_jursd_arch_number_rid;
2935 elsif l_dbi_data_type = 'D' then
2936 l_route_id := l_er_jursd_arch_date_rid;
2937 else
2938 l_route_id := l_er_jursd_arch_rid;
2939 end if;
2940
2941 elsif not(l_found_asg_action) and not(l_found_tax_unit) and
2942 l_found_jursd then
2943
2944 if l_dbi_data_type = 'N' then
2945 l_route_id := l_er_jursd_arch_number_rid;
2946 elsif l_dbi_data_type = 'D' then
2947 l_route_id := l_er_jursd_arch_date_rid;
2948 else
2949 l_route_id := l_er_jursd_arch_rid;
2950 end if;
2951
2952 end if;
2953
2954
2955 hr_utility.trace('getting route parameter id for ' || to_char(l_route_id));
2956
2957 select ROUTE_PARAMETER_ID into l_route_parameter_id
2958 from ff_route_parameters
2959 where parameter_name = 'User Entity ID'
2960 and route_id = l_route_id;
2961
2962 hr_utility.trace('got route parameter id');
2963
2964
2965 open get_user_entity;
2966 fetch get_user_entity into l_user_entity_id,
2967 l_exist_route_id;
2968 if get_user_entity%FOUND then
2969
2970 /* Update the route id if required */
2971 if l_route_id <> l_exist_route_id then
2972
2973 hr_utility.trace ('Existing Route id : '||
2974 to_char(l_exist_route_id));
2975 hr_utility.trace ('New Route id : '||
2976 to_char(l_route_id));
2977 hr_utility.trace ('User Entity id : '||
2978 to_char(l_user_entity_id));
2979 update ff_user_entities
2980 set route_id = l_route_id
2981 where user_entity_id = l_user_entity_id;
2982
2983 update ff_route_parameter_values
2984 set route_parameter_id = l_route_parameter_id
2985 where user_entity_id = l_user_entity_id;
2986
2987 end if;
2988
2989 else
2990
2991 select ff_user_entities_s.nextval
2992 into l_user_entity_seq
2993 from dual;
2994
2995 insert into ff_user_entities
2996 (user_entity_id,
2997 business_group_id,
2998 legislation_code,
2999 route_id,
3000 notfound_allowed_flag,
3001 user_entity_name,
3002 creator_id,
3003 creator_type,
3004 entity_description,
3005 last_update_date,
3006 last_updated_by,
3007 last_update_login,
3008 created_by,
3009 creation_date)
3010 values( l_user_entity_seq, /* user_entity_id */
3011 null, /* business_group_id */
3012 'US', /* legislation_code */
3013 l_route_id, /* route_id */
3014 l_ue_notfound_allowed_flag, /* notfound_allowed_flag */
3015 p_item_name, /* user_entity_name */
3016 0, /* creator_id */
3017 'X', /* archive extract creator_type */
3018 substr('Archive of '||l_ue_creator_type||' entity '||
3019 l_ue_entity_description,1,240),/* entity_description */
3020 sysdate, /* last_update_date */
3021 0, /* last_updated_by */
3022 0, /* last_update_login */
3023 0, /* created_by */
3024 sysdate); /* creation_date */
3025
3026 insert into ff_route_parameter_values (
3027 route_parameter_id,
3028 user_entity_id,
3029 value,
3030 last_update_date,
3031 last_updated_by,
3032 last_update_login,
3033 created_by,
3034 creation_date)
3035 values( l_route_parameter_id,
3036 l_user_entity_seq,
3037 l_user_entity_seq,
3038 sysdate,
3039 0,
3040 0,
3041 0,
3042 sysdate);
3043
3044 insert into ff_database_items (
3045 user_name,
3046 user_entity_id,
3047 data_type,
3048 definition_text,
3049 null_allowed_flag,
3050 description,
3051 last_update_date,
3052 last_updated_by,
3053 last_update_login,
3054 created_by,
3055 creation_date)
3056 values( p_item_name,
3057 l_user_entity_seq,
3058 l_dbi_data_type,
3059 l_definition_text,
3060 l_dbi_null_allowed_flag,
3061 substr('Archive of item '||l_dbi_description,1,240),
3062 sysdate,
3063 0,
3064 0,
3065 0,
3066 sysdate);
3067 end if;
3068 close get_user_entity;
3069
3070 end if;
3071
3072 end create_eoy_archive_dbi; -- ]
3073
3074 end py_w2_dbitems; --