[Home] [Help]
PACKAGE BODY: APPS.PAY_DYN_TRIGGERS
Source
1 package body pay_dyn_triggers as
2 /* $Header: pydyntrg.pkb 120.0.12000000.2 2007/03/01 12:08:09 mshingan noship $ */
3
4 /*
5 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
6
7 Description : Package and procedure to build sql for payroll processes.
8
9 Change List
10 -----------
11 Date Name Vers Bug No Description
12 ----------- ---------- ------ ------- -----------------------------------
13 01-MAR-2007 mshingan 115.15 3620365 Modified create_trigger_event to deliver
14 dynamic triggers as Generated and Enabled
15 08-SEP-2003 mreid 115.14 3131252 Changed length of l_sql
16 17-JUN-2003 jford 115.13 Added return_dated_table_name
17 13-FEB-2003 prsundar 115.12 2510643 Added procedure create_func_usage.
18 Also added owner parameter to proc
19 create_func_trigger.
20 05-FEB-2003 jford 115.11 create_trg_parameter, allows dyt_pkg params
21 05-OCT-2002 jford 115.10 Modified generate_trigger_event to allow
22 call to new dy'Trig pkg maker code. To
23 support new dyt_pkg methodology introduced
24 as part of cont calc 2.
28 25-JAN-2002 nbristow 115.5 Changed create_trg_parameter to
25 19-FEB-2002 nbristow 115.7 Added uppers when checking
26 parameter name checking.
27 25-JAN-2002 nbristow 115.6 Added dbdrv statements.
29 update value_name, rather than
30 insert a new row.
31 25-JUL-2001 nbristow 115.4 Added enable_functional_area and
32 gen_functional_area
33 25-JUL-2001 nbristow 115.3 Now nvl the protected flag.
34 12-APR-2001 exjones 115.2 1731598 Take out code in create_trg_compon-
35 ents which blindly updates the
36 enabled flag to what's in the ldt
37 and replace with code to check if
38 the component is already there, if
39 so, do nothing otherwise create the
40 component.
41 Need to replace with code using who
42 columns to check for and update
43 seeded data, but not that changed
44 by anyone other than seed, to allow
45 us to update seeded data.
46 28-JUN-2000 nbristow 115.1 Added proctected flag.
47 27-JUN-2000 nbristow 115.0 Created.
48
49 */
50 /* Global definitions */
51 --
52 ----------------------------- enable_functional_area --------------------------
53 /*
54 NAME
55 enable_functional_area
56 NOTES
57 Generates and enables all the triggers in a functional area.
58 */
59 procedure enable_functional_area(p_short_name varchar2)
60 is
61 cursor get_trg is
62 select pte.short_name,
63 pte.event_id
64 from pay_functional_areas pfa,
65 pay_functional_triggers pft,
66 pay_trigger_events pte
67 where pte.event_id = pft.event_id
68 and pft.area_id = pfa.area_id
69 and pfa.short_name = p_short_name;
70 --
71 begin
72 --
73 for trgrec in get_trg loop
74 --
75 update pay_trigger_components
76 set enabled_flag = 'Y'
77 where event_id = trgrec.event_id;
78 update pay_trigger_events
79 set generated_flag = 'Y',
80 enabled_flag = 'Y'
81 where event_id = trgrec.event_id;
82 --
83 pay_dyn_triggers.generate_trigger_event(
84 trgrec.short_name
85 );
86 --
87 end loop;
88 --
89 end enable_functional_area;
90 --
91 ----------------------------- gen_functional_area --------------------------
92 /*
93 NAME
94 gen_functional_area
95 NOTES
96 Generates all the triggers in a functional area that are maked as
97 generated.
98 ADDENDUM 10-nov-02
99 For the record I believe this code is now redundant , doesnt appear to be called.
100 The main form to control functional areas, forms/US/PAYWSFGT now contains logic
101 to rebuild all triggers and display appropriate error messages. Any call to this
102 procedure should still work, but no feedback on results will be given.
103 */
104 procedure gen_functional_area(p_short_name varchar2)
105 is
106 cursor get_trg is
107 select distinct pte.short_name
108 from pay_functional_areas pfa,
109 pay_functional_triggers pft,
110 pay_trigger_events pte
111 where pte.event_id = pft.event_id
112 and pft.area_id = pfa.area_id
113 and pfa.short_name like p_short_name;
114 begin
115 for trgrec in get_trg loop
116 pay_dyn_triggers.generate_trigger_event(
117 trgrec.short_name
118 );
119 end loop;
120 end gen_functional_area;
121 --
122 ----------------------------- generate_trigger_event --------------------------
123 /*
124 NAME
125 generate_trigger_event
126 NOTES
127 Generates and enables the trigger if the relevent flags are set.
128 */
129 procedure generate_trigger_event(p_short_name varchar2)
130 is
131 l_table_name pay_trigger_events.table_name%TYPE;
132 l_triggering_action pay_trigger_events.triggering_action%TYPE;
133 l_protected_flag pay_trigger_events.protected_flag%TYPE;
134 l_generated_flag pay_trigger_events.generated_flag%TYPE;
135 l_enabled_flag pay_trigger_events.enabled_flag%TYPE;
136 l_event_id pay_trigger_events.event_id%TYPE;
137 l_trigger_name varchar2(50);
138 l_sql varchar2(32767);
139
140 l_dyt_type pay_dated_tables.dyn_trigger_type%TYPE;
141 l_tab_id pay_dated_tables.dated_table_id%TYPE;
142 l_ok boolean;
143
144 begin
145 select pte.generated_flag,
146 pte.enabled_flag,
147 pte.event_id,
148 pte.table_name,
149 pte.triggering_action,
150 nvl(pte.protected_flag, 'N'),
151 nvl(pdt.dyn_trigger_type,'T'),
152 pdt.dated_table_id
153 into l_generated_flag,
154 l_enabled_flag,
158 l_protected_flag,
155 l_event_id,
156 l_table_name,
157 l_triggering_action,
159 l_dyt_type,
160 l_tab_id
161 from pay_trigger_events pte,
162 pay_dated_tables pdt
163 where pte.table_name = pdt.table_name(+)
164 and pte.short_name = p_short_name;
165 --
166 if (l_protected_flag <> 'Y') then
167 --NEED TO SEE HOW USER WISHES DYN TRIGGERS TO BE HANDLED
168 --
169 -- Added by jford 1-OCT-02 as part of cont calc
170 --
171 IF (l_dyt_type = 'P' or l_dyt_type = 'B') THEN
172 -- dyn trigger code should be handled as package
173 -- >> GENERATE PACKAGE
174 -- generate code FOR ALL TABLE eg many dyn_triggers
175 paywsdyg_pkg.gen_dyt_pkg_full_code(l_tab_id,l_ok);
176 ELSE
177
178 if (l_generated_flag = 'Y') then
179 -- Need to generate trigger
180 l_trigger_name := paywsdyg_pkg.get_trigger_name
181 (l_event_id,
182 l_table_name,
183 l_triggering_action);
184 paywsdyg_pkg.generate_code(l_event_id, l_sql);
185 paywsdyg_pkg.create_trigger(l_trigger_name,
186 l_table_name,
187 l_triggering_action,
188 l_sql);
189 --
190 if (l_enabled_flag = 'Y') then
191 paywsdyg_pkg.enable_trigger(l_trigger_name,
192 TRUE);
193 else
194 paywsdyg_pkg.enable_trigger(l_trigger_name,
195 FALSE);
196 end if;
197 end if;
198 END IF;
199 end if;
200 end generate_trigger_event;
201 --
202 ----------------------------- create_trigger_event --------------------------
203 /*
204 NAME
205 create_trigger_event
206 NOTES
207 Inserts/Updates the PAY_TRIGGER_EVENTS table.
208 */
209 procedure create_trigger_event (
210 p_short_name varchar2,
211 p_table_name varchar2,
212 p_description varchar2,
213 p_generated_flag varchar2,
214 p_enabled_flag varchar2,
215 p_triggering_action varchar2,
216 p_owner varchar2,
217 p_protected_flag varchar2 default 'N'
218 )
219 is
220 l_generated_flag pay_trigger_events.generated_flag%TYPE;
221 l_enabled_flag pay_trigger_events.enabled_flag%TYPE;
222 l_event_id pay_trigger_events.event_id%TYPE;
223 l_trigger_name varchar2(50);
224 begin
225 --
226 begin
227 --
228 select generated_flag,
229 enabled_flag,
230 event_id
231 into l_generated_flag,
232 l_enabled_flag,
233 l_event_id
234 from pay_trigger_events
235 where short_name = p_short_name;
236 --
237 if (l_generated_flag = 'Y') then
238 -- Need to drop trigger
239 l_trigger_name := paywsdyg_pkg.get_trigger_name
240 (l_event_id,
241 p_table_name,
242 p_triggering_action);
243 paywsdyg_pkg.drop_trigger(l_trigger_name);
244 end if;
245 --
246 update pay_trigger_events
247 set
248 table_name = p_table_name,
249 triggering_action = p_triggering_action,
250 description = p_description,
251 generated_flag = p_generated_flag,
252 enabled_flag = p_enabled_flag,
253 protected_flag = p_protected_flag
254 where short_name = p_short_name;
255 --
256 exception
257 when no_data_found then
258 insert into pay_trigger_events
259 (
260 event_id,
261 table_name,
262 description,
263 generated_flag,
264 enabled_flag,
265 triggering_action,
266 short_name,
267 protected_flag
268 )
269 select
270 pay_trigger_events_s.nextval,
271 p_table_name,
272 p_description,
273 p_generated_flag,
274 p_enabled_flag,
275 p_triggering_action,
276 p_short_name,
277 p_protected_flag
278 from sys.dual;
279 end;
280 --
281 end create_trigger_event;
282 --
283 ----------------------------- create_trg_declaration --------------------------
284 /*
285 NAME
286 create_trg_declaration
287 NOTES
288 Inserts/Updates the PAY_TRIGGER_DECLARATIONS table.
289 */
290 procedure create_trg_declaration (
291 p_short_name varchar2,
295 p_owner varchar2
292 p_variable_name varchar2,
293 p_data_type varchar2,
294 p_variable_size number,
296 )
297 is
298 l_event_id number;
299 begin
300 --
301 select event_id
302 into l_event_id
303 from pay_trigger_events
304 where short_name = p_short_name;
305 --
306 update pay_trigger_declarations
307 set data_type = p_data_type,
308 variable_size = p_variable_size
309 where event_id = l_event_id
310 and variable_name = p_variable_name;
311 --
312 if (SQL%notfound) then
313 insert into pay_trigger_declarations
314 (
315 declaration_id,
316 event_id,
317 variable_name,
318 data_type,
319 variable_size
320 )
321 select
322 pay_trigger_declarations_s.nextval,
323 l_event_id,
324 p_variable_name,
325 p_data_type,
326 p_variable_size
327 from sys.dual;
328 --
329 end if;
330 --
331 end create_trg_declaration;
332 --
333 ----------------------------- create_trg_initialisation --------------------------
334 /*
335 NAME
336 create_trg_initialisation
337 NOTES
338 Inserts/Updates the PAY_TRIGGER_INITIALISATIONS table.
339 */
340 procedure create_trg_initialisation (
341 p_short_name varchar2,
342 p_process_order varchar2,
343 p_plsql_code varchar2,
344 p_process_type varchar2,
345 p_owner varchar2
346 )
347 is
348 l_event_id number;
349 begin
350 --
351 select event_id
352 into l_event_id
353 from pay_trigger_events
354 where short_name = p_short_name;
355 --
356 update pay_trigger_initialisations
357 set plsql_code = p_plsql_code,
358 process_type = p_process_type
359 where event_id = l_event_id
360 and process_order = p_process_order;
361 --
362 if (SQL%notfound) then
363 insert into pay_trigger_initialisations
364 (
365 initialisation_id,
366 event_id,
367 process_order,
368 plsql_code,
369 process_type
370 )
371 select
372 pay_trigger_initialisations_s.nextval,
373 l_event_id,
374 p_process_order,
375 p_plsql_code,
376 p_process_type
377 from sys.dual;
378 --
379 end if;
380 --
381 end create_trg_initialisation;
382 --
383 ----------------------------- create_trg_components --------------------------
384 /*
385 NAME
386 create_trg_components
387 NOTES
388 Inserts/Updates the PAY_TRIGGER_COMPONENTS table.
389 */
390 procedure create_trg_components (
391 p_short_name varchar2,
392 p_legislative_code varchar2,
393 p_business_group varchar2,
394 p_payroll_name varchar2,
395 p_module_name varchar2,
396 p_enabled_flag varchar2,
397 p_owner varchar2
398 )
399 is
400 l_event_id number;
401 l_bus_grp_id number;
402 l_payroll_id number;
403 l_enabled pay_trigger_components.enabled_flag%TYPE;
404 begin
405 --
406 if (p_business_group is null) then
407 l_bus_grp_id := null;
408 l_payroll_id := null;
409 else
410 --
411 select business_group_id
412 into l_bus_grp_id
413 from per_business_groups
414 where name = p_business_group;
415 --
416 -- Now setup the payroll.
417 if (p_payroll_name is null) then
418 l_payroll_id := null;
419 else
420 --
421 select distinct payroll_id
422 into l_payroll_id
423 from pay_payrolls_f
424 where business_group_id = l_bus_grp_id
425 and payroll_name = p_payroll_name;
426 end if;
427 end if;
428 --
429 select event_id
430 into l_event_id
431 from pay_trigger_events
432 where short_name = p_short_name;
433 --
434 l_enabled := 'X'; -- Default
435 begin
436 -- Check if the component we want to create already exists
437 select enabled_flag
438 into l_enabled
439 from pay_trigger_components
440 where event_id = l_event_id
441 and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
442 and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
443 and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
444 and p_module_name = module_name;
445 exception
446 when no_data_found then l_enabled := 'C'; -- Not found, need to create
447 when others then null; -- Catch everything, leave enabled as default
448 end;
452 set enabled_flag = p_enabled_flag
449 --
450 /* Removed 12-Apr-2001 by exjones
451 update pay_trigger_components
453 where event_id = l_event_id
454 and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
455 and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
456 and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
457 and p_module_name = module_name;
458 */
459 --
460 /* if (SQL%notfound) then */
461 -- Need to create this component
462 if (l_enabled = 'C') then
463 insert into pay_trigger_components
464 (
465 component_id,
466 event_id,
467 legislation_code,
468 business_group_id,
469 payroll_id,
470 module_name,
471 enabled_flag
472 )
473 select
474 pay_trigger_components_s.nextval,
475 l_event_id,
476 p_legislative_code,
477 l_bus_grp_id,
478 l_payroll_id,
479 p_module_name,
480 p_enabled_flag
481 from sys.dual;
482 --
483 end if;
484 --
485 end create_trg_components;
486 --
487 ----------------------------- create_trg_components --------------------------
488 /*
489 NAME
490 create_trg_components
491 NOTES
492 Inserts/Updates the PAY_TRIGGER_PARAMETERS table.
493 */
494 procedure create_trg_parameter (p_short_name varchar2,
495 p_process_order varchar2,
496 p_legislative_code varchar2,
497 p_business_group varchar2,
498 p_payroll_name varchar2,
499 p_module_name varchar2,
500 p_usage_type varchar2,
501 p_parameter_type varchar2,
502 p_parameter_name varchar2,
503 p_value_name varchar2,
504 p_automatic varchar2,
505 p_owner varchar2
506 )
507 is
508 l_event_id number;
509 l_bus_grp_id number;
510 l_payroll_id number;
511 l_usage_id number;
512 begin
513 --
514 -- If this is a component get the component id
515 IF (p_usage_type = 'C') then
516
517 select event_id
518 into l_event_id
519 from pay_trigger_events
520 where short_name = p_short_name;
521
522 if (p_business_group is null) then
523 l_bus_grp_id := null;
524 l_payroll_id := null;
525 else
526 --
527 select business_group_id
528 into l_bus_grp_id
529 from per_business_groups
530 where name = p_business_group;
531 --
532 -- Now setup the payroll.
533 if (p_payroll_name is null) then
534 l_payroll_id := null;
535 else
536 --
537 select distinct payroll_id
538 into l_payroll_id
539 from pay_payrolls_f
540 where business_group_id = l_bus_grp_id
541 and payroll_name = p_payroll_name;
542 end if;
543 end if;
544 --
545 select component_id
546 into l_usage_id
547 from pay_trigger_components
548 where event_id = l_event_id
549 and nvl(p_legislative_code, 'CORE') = nvl (legislation_code, 'CORE')
550 and nvl(l_bus_grp_id, -1) = nvl(business_group_id, -1)
551 and nvl(l_payroll_id, -1) = nvl(payroll_id, -1)
552 and p_module_name = module_name;
553 --
554 ELSIF (p_usage_type = 'I') then
555 --
556 -- It must be an initialisation.
557 --
558 select event_id
559 into l_event_id
560 from pay_trigger_events
561 where short_name = p_short_name;
562
563 select initialisation_id
564 into l_usage_id
565 from pay_trigger_initialisations
566 where event_id = l_event_id
567 and process_order = p_process_order;
568 --
569 ELSE
570 -- It must be a parameter for a dynamic trigger package
571 -- Eg type in PI, PU, PD
572
573 select dated_table_id
574 into l_usage_id
575 from pay_dated_tables
576 where table_name = p_short_name;
577
578 END IF;
579 --
580 update pay_trigger_parameters
581 set automatic = p_automatic,
582 value_name = p_value_name
583 where usage_id = l_usage_id
584 and usage_type = p_usage_type
585 and parameter_type = p_parameter_type
586 and upper(nvl(parameter_name, 'NULL')) =
587 upper(nvl(p_parameter_name, 'NULL'));
588 --
589 if (SQL%notfound) then
590 insert into pay_trigger_parameters
591 (
592 parameter_id,
593 usage_type,
594 usage_id,
595 parameter_type,
596 parameter_name,
597 value_name,
598 automatic
599 )
600 select
601 pay_trigger_parameters_s.nextval,
605 p_parameter_name,
602 p_usage_type,
603 l_usage_id,
604 p_parameter_type,
606 p_value_name,
607 p_automatic
608 from sys.dual;
609 --
610 end if;
611 --
612 end create_trg_parameter;
613 --
614 --
615 ----------------------------- create_func_area --------------------------
616 /*
617 NAME
618 create_func_area
619 NOTES
620 Inserts/Updates the PAY_FUNCTIONAL_AREAS table.
621 */
622 procedure create_func_area (p_area_name varchar2,
623 p_description varchar2
624 )
625 is
626 begin
627 --
628 update pay_functional_areas
629 set
630 description = p_description
631 where short_name = p_area_name;
632 --
633 if (SQL%notfound) then
634 insert into pay_functional_areas
635 (
636 area_id,
637 short_name,
638 description
639 )
640 select
641 pay_functional_areas_s.nextval,
642 p_area_name,
643 p_description
644 from sys.dual;
645 --
646 end if;
647 --
648 end create_func_area;
649 --
650 ----------------------------- create_func_trigger --------------------------
651 /*
652 NAME
653 create_func_trigger
654 NOTES
655 Inserts/Updates the PAY_FUNCTIONAL_TRIGGERS table.
656 */
657 procedure create_func_trigger (p_area_name varchar2
658 ,p_short_name varchar2
659 ,p_owner varchar2
660 )
661 is
662 --
663 l_event_id number;
664 l_area_id number;
665 begin
666 --
667 select event_id
668 into l_event_id
669 from pay_trigger_events
670 where short_name = p_short_name;
671 --
672 select area_id
673 into l_area_id
674 from pay_functional_areas
675 where short_name = p_area_name;
676 --
677 if(p_OWNER='SEED') then
678 hr_general2.init_fndload(800,1);
679 else
680 hr_general2.init_fndload(800,-1);
681 end if;
682
683 insert into pay_functional_triggers
684 (
685 trigger_id,
686 area_id,
687 event_id
688 )
689 select
690 pay_functional_triggers_s.nextval,
691 l_area_id,
692 l_event_id
693 from sys.dual
694 where not exists (select ''
695 from pay_functional_triggers
696 where area_id = l_area_id
697 and event_id = l_event_id);
698 --
699 end create_func_trigger;
700 --
701 ----------------------------- create_event_update --------------------------
702 /*
703 NAME
704 create_event_update
705 NOTES
706 Inserts/Updates the PAY_EVENT_UPDATES table.
707 */
708 procedure create_event_update (p_table_name varchar2,
709 p_column_name varchar2,
710 p_business_group_name varchar2,
711 p_legislation_code varchar2,
712 p_change_type varchar2
713 )
714 is
715 l_business_group_id number;
716 begin
717 --
718 if (p_business_group_name is not null) then
719 select business_group_id
720 into l_business_group_id
721 from per_business_groups
722 where name = p_business_group_name;
723 else
724 l_business_group_id := null;
725 end if;
726 --
727 insert into pay_event_updates
728 (event_update_id,
729 table_name,
730 column_name,
731 business_group_id,
732 legislation_code,
733 change_type
734 )
735 select pay_event_updates_s.nextval,
736 p_table_name,
737 p_column_name,
738 l_business_group_id,
739 p_legislation_code,
740 p_change_type
741 from sys.dual
742 where not exists (select ''
743 from pay_event_updates
744 where table_name = p_table_name
745 and column_name = p_column_name
746 and nvl(business_group_id, -999) = nvl(l_business_group_id, -999)
747 and nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE')
748 and p_change_type = change_type);
749 --
750 end create_event_update;
751 --
752
753 ----------------------------- create_func_usage --------------------------
754 /*
755 NAME
756 create_func_usage
757 NOTES
758 Inserts/Updates the PAY_FUNCTIONAL_USAGES table.
759 */
760 procedure create_func_usage (p_area_name varchar2
761 ,p_usage_id varchar2
762 ,p_business_group_name varchar2
763 ,p_legislation_code varchar2
767 is
764 ,p_payroll_name varchar2
765 ,p_owner varchar2
766 )
768 --
769 cursor csr_sel_area_id(p_area_name varchar2) is
770 select area_id
771 from pay_functional_areas
772 where upper(short_name) = upper(p_area_name);
773
774 cursor csr_sel_payroll_id(p_payroll_name varchar2) is
775 select payroll_id
776 from pay_payrolls_f
777 where upper(payroll_name)= upper(p_payroll_name);
778
779 cursor csr_sel_bgid(p_business_group_name varchar2) is
780 select business_group_id
781 from per_business_groups
782 where upper(name) = upper(p_business_group_name);
783
784 l_area_id number;
785 l_business_group_id number;
786 l_payroll_id number;
787
788 begin
789 --
790 open csr_sel_area_id(p_area_name);
791 fetch csr_sel_area_id into l_area_id;
792 if(csr_sel_area_id%notfound) then
793 null;
794 else
795 if (p_payroll_name is not null) then
796 open csr_sel_payroll_id(p_payroll_name);
797 fetch csr_sel_payroll_id into l_payroll_id;
798 else
799 l_payroll_id :=NULL;
800 end if;
801
802 if(p_business_group_name is not null) then
803 open csr_sel_bgid(p_business_group_name);
804 fetch csr_sel_bgid into l_business_group_id;
805 else
806 l_business_group_id :=NULL;
807 end if;
808
809 if(p_OWNER='SEED') then
810 hr_general2.init_fndload(800,1);
811 else
812 hr_general2.init_fndload(800,-1);
813 end if;
814
815 insert into pay_functional_usages
816 (
817 usage_id,
818 area_id,
819 business_group_id,
820 legislation_code,
821 payroll_id
822 )
823 select
824 to_number(p_usage_id),
825 l_area_id,
826 l_business_group_id,
827 p_legislation_code,
828 l_payroll_id
829 from sys.dual
830 where not exists( select 'X'
831 from pay_functional_usages
832 where area_id = l_area_id
833 and usage_id =to_number(p_usage_id)
834 );
835
836 if(p_business_group_name is not null) then
837 close csr_sel_bgid;
838 end if;
839
840 if(p_payroll_name is not null) then
841 close csr_sel_payroll_id;
842 end if;
843
844 end if;
845 close csr_sel_area_id;
846 --
847 end create_func_usage;
848 --
849
850 --------------------------------
851 -- Get the dated table name given ID, used in view PAY_DATETRACKED_EVENTS_V
852 --
853 function RETURN_DATED_TABLE_NAME (p_dated_table_id number) return varchar2 is
854
855 l_table_name varchar2(120);
856 cursor get_dt_name is
857 select table_name
858 from pay_dated_tables
859 where dated_table_id = p_dated_table_id;
860
861 begin
862 open get_dt_name;
863 fetch get_dt_name into l_table_name;
864 close get_dt_name;
865
866 return l_table_name;
867 end RETURN_DATED_TABLE_NAME;
868
869 end pay_dyn_triggers;