1 PACKAGE BODY paywsdyg_pkg AS
2 -- $Header: pydygpkg.pkb 120.2.12010000.1 2008/07/27 22:28:37 appldev ship $
3 --
4 -- +---------------------------------------------------------------------------+
5 -- | Global Constants |
6 -- +---------------------------------------------------------------------------+
7 g_package varchar2(80) := 'paywsdyg_pkg';
8 --
9 -- The end-of-line character to use in generated PL/SQL
10 --
11 g_eol CONSTANT VARCHAR2(10) := fnd_global.newline;
12
13 -- A quick command for formatting ease, end of section.
14 --
15 g_eos CONSTANT VARCHAR2(10) := g_eol||g_eol||'--'||g_eol;
16
17
18
19 -- To store the dynamic triggers as a package use array so as to overcome
20 -- the limit of 32767
21 g_dyt_pkg_head dbms_sql.varchar2s;
22 g_dyt_pkg_body dbms_sql.varchar2s;
23 g_dyt_pkg_hindex number := 0;
24 g_dyt_pkg_bindex number := 0;
25
26 -- Global for PAY schema name
27 g_pay_schema varchar2(30) := null;
28
29 --
30 -- +---------------------------------------------------------------------------+
31 -- | NAME : init_dyt_pkg |
32 -- | SCOPE : PRIVATE |
33 -- | DESCRIPTION: The dynamic generation will build up code and store it in an |
34 -- | an array of varchar2(32767). Before starting these global place- |
35 -- | holders will need to be emptied and indexes reset. This is what is |
36 -- | done here. |
37 -- | PARAMETERS : none |
38 -- | RETURNS : None, simply clears global placeholder |
39 -- | RAISES : None |
40 -- +---------------------------------------------------------------------------+
41
42 procedure init_dyt_pkg is
43 l_index number := 0;
44 l_proc varchar2(30) := g_package||'.init_dyt_pkg';
45 begin
46 hr_utility.set_location(l_proc,10);
47
48 -- delete all elements from package head pl/sql table.
49 l_index := g_dyt_pkg_head.first;
50 while l_index is not null loop
51 g_dyt_pkg_head.delete(l_index);
52 l_index := g_dyt_pkg_head.next(l_index);
53 end loop;
54 -- delete all elements from package body pl/sql table.
55 l_index := g_dyt_pkg_body.first;
56 while l_index is not null loop
57 g_dyt_pkg_body.delete(l_index);
58 l_index := g_dyt_pkg_body.next(l_index);
59 end loop;
60
61 --initialize the index
62 g_dyt_pkg_hindex := 0;
63 g_dyt_pkg_bindex := 0;
64
65 hr_utility.set_location(l_proc,900);
66 exception
67 when others then
68 hr_utility.trace('Unhandled Error: '||l_proc);
69 hr_utility.set_location(l_proc,1000);
70 raise;
71 end init_dyt_pkg;
72
73 --
74 -- +---------------------------------------------------------------------------+
75 -- | NAME : add_to_dyt_pkg |
76 -- | SCOPE : PRIVATE |
77 -- | DESCRIPTION: The dynamic generation will build up code and store it in an |
78 -- | an array of varchar2(32767). The task of this procedure is to split|
79 -- | the above array elements into array elements of size 254. This is |
80 -- | required so as to the package body of more than 32 K size can be |
81 -- | parsed using dbms_sql procedure. |
82 -- | PARAMETERS : p_new_code_tbl - A big fat table containing rows of new code |
83 -- | p_body - Flag, false => package header, true => body |
84 -- | RETURNS : None, simply sets global placeholder |
85 -- | RAISES : None |
86 -- +---------------------------------------------------------------------------+
87
88 procedure add_to_dyt_pkg
89 (
90 p_new_code_tbl t_varchar2_32k_tbl
91 ,p_body BOOLEAN
92 ) is
93
94 l_code_index number := p_new_code_tbl.first;
95 l_line varchar2(254);
96 l_start number;
97 l_end number;
98 l_next varchar2(1);
99 l_proc varchar2(80) := g_package||'.add_to_dyt_pkg';
100 begin
101 hr_utility.set_location(l_proc,10);
102 <<ROW_OF_CODE_LOOP>>
103 while l_code_index is not null loop
104 l_start := 0;
105 -- read the string of the passed on code, chop it into the array element
106 -- size of 254 and store it in global package placeholders.
107 <<CHAR254_LOOP>>
108 while substrb(p_new_code_tbl(l_code_index),l_start,254) is not null LOOP
109 l_line := substrb(p_new_code_tbl(l_code_index),l_start ,254);
110 -- Find a proposed end point for this set of max 254chars
111 l_end := greatest(instr(l_line,' ',-1),instr(l_line,g_eol,-1));
112
113 -- If the next char is ok, or no space/returns at all then we use the max
114 --
115 l_next := substrb(p_new_code_tbl(l_code_index),l_start + 254 ,1);
116 if (l_next = ' ' or l_next = g_eol or l_next is null or l_end = 0) then
117 l_end := 254;
118 end if;
119
120 --Get correct subset (so not in mid word)
121 l_line := substrb(p_new_code_tbl(l_code_index),l_start ,l_end);
122
123 -- add the new code to either header or body as appropriate
124 if (p_body) then
125 g_dyt_pkg_bindex := g_dyt_pkg_bindex + 1;
126 g_dyt_pkg_body (g_dyt_pkg_bindex) := l_line;
127 else
128 g_dyt_pkg_hindex := g_dyt_pkg_hindex + 1;
129 g_dyt_pkg_head (g_dyt_pkg_hindex) := l_line;
130 end if;
131 --
132 -- Start next chunk from where we have taken code up to
133 l_start := l_start + l_end;
134 end loop char254_loop;
135
136 l_code_index := p_new_code_tbl.next(l_code_index);
137 end loop row_of_code_loop;
138 hr_utility.set_location(l_proc,900);
139 exception
140 when others then
141 hr_utility.trace('Unhandled Error: '||l_proc);
142 hr_utility.trace(l_proc ||' g_dyt_pkg_hindex - ' || g_dyt_pkg_hindex
143 ||' g_dyt_pkg_bindex - ' || g_dyt_pkg_bindex );
144 raise;
145 end ADD_TO_DYT_PKG;
146 --============================================
147 --
148 -- +---------------------------------------------------------------------------+
149 -- | NAME : insert_parameters |
150 -- | SCOPE : PUBLIC |
151 -- | DESCRIPTION: Helper procedure to maintain parameters |
152 -- | Inserts new rows if none exist, otherwise updates the |
153 -- | existing row if it's 'automatic' flag is set to 'Y'or null |
154 -- | PARAMETERS : p_usage_type - Value for the usage_type column |
155 -- | p_usage_id - Value for the usage_id column |
156 -- | p_parameter_type - Value for the parameter_type column |
157 -- | p_parameter_name - Value for the parameter_name column |
158 -- | p_value_name - Value for the value_name column |
159 -- | p_automatic - Value for the automatic column |
160 -- | RETURNS : The primary key of the new or existing row |
161 -- | RAISES : None |
162 -- +---------------------------------------------------------------------------+
163 FUNCTION insert_parameters(
164 p_usage_type IN VARCHAR2 DEFAULT NULL,
165 p_usage_id IN NUMBER DEFAULT NULL,
166 p_parameter_type IN VARCHAR2 DEFAULT NULL,
167 p_parameter_name IN VARCHAR2 DEFAULT NULL,
168 p_value_name IN VARCHAR2 DEFAULT NULL,
169 p_automatic IN VARCHAR2 DEFAULT NULL
170 ) RETURN NUMBER IS
171 --
172 -- Get the next primary key value from the database sequence
173 CURSOR get_id IS
174 SELECT pay_trigger_parameters_s.NEXTVAL
175 FROM dual;
176 --
177 -- Find any existing parameter matching the name, type and usage ID
178 CURSOR get_existing(
179 cp_id IN NUMBER,
180 cp_type IN VARCHAR2,
181 cp_name IN VARCHAR2
182 ) IS
183 SELECT parameter_id,automatic
184 FROM pay_trigger_parameters
185 WHERE UPPER(parameter_name) = UPPER(cp_name)
186 AND usage_type = cp_type
187 AND usage_id = cp_id;
188 --
189 l_rc NUMBER;
190 l_auto VARCHAR2(1);
191 --
192 BEGIN
193 --
194 -- Find any existing parameter
195 OPEN get_existing(p_usage_id,p_usage_type,p_parameter_name);
196 FETCH get_existing INTO l_rc,l_auto;
197 IF get_existing%NOTFOUND THEN
198 l_auto := 'X';
199 END IF;
200 CLOSE get_existing;
201 --
202 -- If l_auto is 'X' then the parameter doesn't exist
203 IF l_auto = 'X' THEN
204 --
205 -- Get a new primary key
206 OPEN get_id;
207 FETCH get_id INTO l_rc;
208 CLOSE get_id;
209 --
210 -- Insert the new row
211 INSERT INTO pay_trigger_parameters(
212 parameter_id,
213 usage_type,
214 usage_id,
215 parameter_type,
216 parameter_name,
217 value_name,
218 automatic
219 ) VALUES (
220 l_rc,
221 p_usage_type,
222 p_usage_id,
223 p_parameter_type,
224 p_parameter_name,
225 p_value_name,
226 p_automatic
227 );
228 --
229 -- If l_auto is 'Y' then the parameter
230 -- can be updated it with new values, if the parameter is not automatic
231 -- then the user has modified it in some way so we can't change it
232 ELSIF (l_auto = 'Y') THEN
233 UPDATE pay_trigger_parameters
234 SET usage_type = p_usage_type,
235 usage_id = p_usage_id,
236 parameter_type = p_parameter_type,
237 parameter_name = p_parameter_name,
238 value_name = p_value_name,
239 automatic = p_automatic
240 WHERE parameter_id = l_rc;
241 END IF;
242 --
243 -- Return the primary key we found or generated
244 RETURN l_rc;
245 END insert_parameters;
246 --
247 -- +---------------------------------------------------------------------------+
248 -- | NAME : insert_declarations |
249 -- | SCOPE : PRIVATE |
250 -- | DESCRIPTION: Simply inserts values into the PAY_TRIGGER_DECLARATIONS table|
251 -- | PARAMETERS : p_event_id - The event_id column |
252 -- | p_variable_name - The variable_name column |
253 -- | p_data_type - The data_type column |
254 -- | p_variable_size - The variable_size column |
255 -- | RETURNS : Primary key of the inserted row |
256 -- | RAISES : None |
257 -- +---------------------------------------------------------------------------+
258 FUNCTION insert_declarations(
259 p_event_id IN NUMBER DEFAULT NULL,
260 p_variable_name IN VARCHAR2 DEFAULT NULL,
261 p_data_type IN VARCHAR2 DEFAULT NULL,
262 p_variable_size IN VARCHAR2 DEFAULT NULL
263 ) RETURN NUMBER IS
264 --
265 -- Get the next value from the primary key sequence
266 CURSOR get_id IS
267 SELECT pay_trigger_declarations_s.NEXTVAL
268 FROM dual;
269 --
270 l_rc NUMBER;
271 --
272 BEGIN
273 --
274 -- Fetch the new primary key
275 OPEN get_id;
276 FETCH get_id INTO l_rc;
277 CLOSE get_id;
278 --
279 -- Insert the new row
280 INSERT INTO pay_trigger_declarations(
281 declaration_id,
282 event_id,
283 variable_name,
284 data_type,
285 variable_size
286 ) VALUES (
287 l_rc,
288 p_event_id,
289 p_variable_name,
290 p_data_type,
291 p_variable_size
292 );
293 --
294 -- Return the new primary key
295 RETURN l_rc;
296 END insert_declarations;
297 --
298 -- +---------------------------------------------------------------------------+
299 -- | NAME : insert_initialisations |
300 -- | SCOPE : PRIVATE |
301 -- | DESCRIPTION: Just inserts a new row into the initialisations table |
302 -- | PARAMETERS : p_event_id - Value for the event_id column |
303 -- | p_process_order - Value for the process_order column |
304 -- | p_plsql_code - Value for the plsql_code column |
305 -- | p_process_type - Value for the process_type column |
306 -- | RETURNS : Primary key of the new row |
307 -- | RAISES : None |
308 -- +---------------------------------------------------------------------------+
309 FUNCTION insert_initialisations(
310 p_event_id IN NUMBER DEFAULT NULL,
311 p_process_order IN NUMBER DEFAULT NULL,
312 p_plsql_code IN VARCHAR2 DEFAULT NULL,
313 p_process_type IN VARCHAR2 DEFAULT NULL
314 ) RETURN NUMBER IS
315 --
316 -- Fetch the next primary key value from the database sequence
317 CURSOR get_id IS
318 SELECT pay_trigger_initialisations_s.NEXTVAL
319 FROM dual;
320 --
321 l_rc NUMBER;
322 --
323 BEGIN
324 --
325 -- Fetch the primary key value
326 OPEN get_id;
327 FETCH get_id INTO l_rc;
328 CLOSE get_id;
329 --
330 -- Insert the new row
331 INSERT INTO pay_trigger_initialisations(
332 initialisation_id,
333 event_id,
334 process_order,
335 plsql_code,
336 process_type
337 ) VALUES (
338 l_rc,
339 p_event_id,
340 p_process_order,
341 p_plsql_code,
342 p_process_type
343 );
344 --
345 -- Return the new primary key value
346 RETURN l_rc;
347 END insert_initialisations;
348 --
349 -- +---------------------------------------------------------------------------+
350 -- | NAME : insert_support |
351 -- | SCOPE : PRIVATE |
352 -- | DESCRIPTION: Just insert a new row into the supporting package table |
353 -- | PARAMETERS : p_event_id - The value for the event_id column |
354 -- | p_header_code - The value for the header_code column |
355 -- | p_body_code - The value for the body_code column |
356 -- | RETURNS : The new primary key value |
357 -- | RAISES : None |
358 -- +---------------------------------------------------------------------------+
359 FUNCTION insert_support(
360 p_event_id IN NUMBER DEFAULT NULL,
361 p_header_code IN VARCHAR2 DEFAULT NULL,
362 p_body_code IN VARCHAR2 DEFAULT NULL
363 ) RETURN NUMBER IS
364 --
365 -- Fetch the next value from the primary key generating sequence
366 CURSOR get_id IS
367 SELECT pay_trigger_support_s.NEXTVAL
368 FROM dual;
369 --
370 l_rc NUMBER;
371 --
372 BEGIN
373 --
374 -- Get a new primary key value
375 OPEN get_id;
376 FETCH get_id INTO l_rc;
377 CLOSE get_id;
378 --
379 -- Insert the data into the table
380 INSERT INTO pay_trigger_support(
381 support_id,
382 event_id,
383 header_code,
384 body_code
385 ) VALUES (
386 l_rc,
387 p_event_id,
388 p_header_code,
389 p_body_code
390 );
391 --
392 -- Return the primary key
393 RETURN l_rc;
394 END insert_support;
395 --
396 -- +---------------------------------------------------------------------------+
397 -- | NAME : get_applsys_user |
398 -- | SCOPE : PRIVATE |
399 -- | DESCRIPTION: Determine the Oracle username that the applsys (FND) user |
400 -- | is created under. Needed for the ad_ddl calls. |
401 -- | PARAMETERS : None |
402 -- | RETURNS : The Oracle user name |
403 -- | RAISES : NO_DATA_FOUND - If we can't work out who the FND user is |
404 -- +---------------------------------------------------------------------------+
405 FUNCTION get_applsys_user RETURN VARCHAR2 IS
406 --
407 -- Fetch the username from foundation (AOL) tables
408 CURSOR get_user(cp_appl IN VARCHAR2) IS
409 SELECT fou.oracle_username
410 FROM fnd_oracle_userid fou,
411 fnd_product_installations fpi,
412 fnd_application fa
413 WHERE fou.oracle_id = fpi.oracle_id
414 AND fpi.application_id = fa.application_id
415 AND fa.application_short_name = cp_appl;
416 --
417 l_user VARCHAR2(30);
418 --
419 BEGIN
420 --
421 -- Try to get the username
422 -- Raise NO_DATA_FOUND if (surprisingly enough) no data was found
423 OPEN get_user('FND');
424 FETCH get_user INTO l_user;
425 IF get_user%NOTFOUND THEN
426 CLOSE get_user;
427 RAISE NO_DATA_FOUND;
428 END IF;
429 CLOSE get_user;
430 --
431 -- Send back the username
432 RETURN l_user;
433 END get_applsys_user;
434 --
435 -- +---------------------------------------------------------------------------+
436 -- | NAME : get_table_product |
437 -- | SCOPE : PRIVATE |
438 -- | DESCRIPTION: Get the product that a table belongs to (PAY, PER, etc) |
439 -- | needed for calls to ad_ddl |
440 -- | PARAMETERS : p_table - The name of the table to find |
441 -- | RETURNS : The product (application) short name |
442 -- | RAISES : NO_DATA_FOUND - If the table isn't listed as belonging to |
443 -- | any particular application |
444 -- +---------------------------------------------------------------------------+
445 FUNCTION get_table_product(p_table IN VARCHAR2) RETURN VARCHAR2 IS
446 --
447 -- Get the application short name from the foundation tables
448 CURSOR get_table(cp_name IN VARCHAR2) IS
449 SELECT fa.application_short_name
450 FROM fnd_application fa,
451 fnd_tables ft
452 WHERE fa.application_id = ft.application_id
453 AND ft.table_name = cp_name;
454 --
455 l_appl VARCHAR2(50);
456 --
457 BEGIN
458 --
459 -- Fetch the data, crash and burn if none was found
460 OPEN get_table(p_table);
461 FETCH get_table INTO l_appl;
462 IF get_table%NOTFOUND THEN
463 CLOSE get_table;
464 RAISE NO_DATA_FOUND;
465 END IF;
466 CLOSE get_table;
467 --
468 -- Send back the application short name
469 RETURN l_appl;
470 END get_table_product;
471 --
472 -- +---------------------------------------------------------------------------+
473 -- | NAME : get_table_from_trigger |
474 -- | SCOPE : PRIVATE |
475 -- | DESCRIPTION: Find out which table a specified trigger belongs to, |
476 -- | we need this 'cos the trigger name sort of contains the |
477 -- | table name but we mangle it up a bit to fit it in. |
478 -- | PARAMETERS : p_trigger - The name of the trigger |
479 -- | RETURNS : The table name |
480 -- | RAISES : NO_DATA_FOUND if the trigger name is bobbins so we couldn't |
481 -- | find the table |
482 -- +---------------------------------------------------------------------------+
483 FUNCTION get_table_from_trigger(p_trigger IN VARCHAR2) RETURN VARCHAR2 IS
484 --
485 -- Fetch the table name from from RDBMS data dictionary views
486 CURSOR get_trigger(cp_name IN VARCHAR2) IS
487 SELECT atr.table_name
488 FROM user_triggers atr
489 WHERE trigger_name = cp_name;
490 --
491 l_tabl VARCHAR2(50);
492 --
493 BEGIN
494 --
495 -- Get the data, spoon up if we couldn't find the table
496 OPEN get_trigger(p_trigger);
497 FETCH get_trigger INTO l_tabl;
498 IF get_trigger%NOTFOUND THEN
499 CLOSE get_trigger;
500 RAISE NO_DATA_FOUND;
501 END IF;
502 CLOSE get_trigger;
503 --
504 -- Send back the table name
505 RETURN l_tabl;
506 END get_table_from_trigger;
507 --
508 -- +---------------------------------------------------------------------------+
509 -- | NAME : get_trigger_name |
510 -- | SCOPE : PUBLIC |
511 -- | DESCRIPTION: See Header |
512 -- +---------------------------------------------------------------------------+
513 FUNCTION get_trigger_name(
514 p_id IN NUMBER,
515 p_table IN VARCHAR2,
516 p_action IN VARCHAR2
517 ) RETURN VARCHAR2 IS
518 --
519 l_newtab VARCHAR2(30);
520 l_id VARCHAR2(30);
521 --
522 BEGIN
523 --
524 -- The ID portion is the primary key of the event, the triggering action
525 -- and the text 'DYT' to mark this as a DYnamically generated Trigger
526 l_id := '_'||LTRIM(RTRIM(TO_CHAR(p_id)))||p_action||'_DYT';
527 --
528 -- The new table name is the original with the underscores removed (so
529 -- we can fit more of it in) chopped off so we can fit the ID on the
530 -- end
531 l_newtab := SUBSTR(REPLACE(p_table,'_'),1,30-LENGTH(l_id));
532 --
533 -- Concatenate the new table name and the ID for the trigger name
534 RETURN (l_newtab||l_id);
535 END get_trigger_name;
536 --
537 -- +---------------------------------------------------------------------------+
538 -- | NAME : trigger_exists |
539 -- | SCOPE : PRIVATE |
540 -- | DESCRIPTION: Check to see if the specified trigger exists or not |
541 -- | PARAMETERS : p_name - The name of the |
542 -- | RETURNS : Boolean flag, TRUE if the trigger exists, FALSE otherwise |
543 -- | RAISES : None |
544 -- +---------------------------------------------------------------------------+
545 FUNCTION trigger_exists(p_name IN VARCHAR2) RETURN BOOLEAN IS
546 --
547 -- Get trigger information from the Oracle data dictionary
548 CURSOR get_trigger(cp_name IN VARCHAR2) IS
549 SELECT 'Y'
550 FROM user_triggers
551 WHERE trigger_name = cp_name;
552 --
553 l_rc VARCHAR2(1);
554 --
555 BEGIN
556 --
557 -- Fetch the data, switch the flag manually if the trigger wasn't found
558 OPEN get_trigger(p_name);
559 FETCH get_trigger INTO l_rc;
560 IF get_trigger%NOTFOUND THEN
561 l_rc := 'N';
562 END IF;
563 CLOSE get_trigger;
564 --
565 -- Send back the boolean version of the flag
566 RETURN (l_rc = 'Y');
567 END trigger_exists;
568 --
569 -- +---------------------------------------------------------------------------+
570 -- | NAME : drop_trigger |
571 -- | SCOPE : PUBLIC |
572 -- | DESCRIPTION: See header |
573 -- +---------------------------------------------------------------------------+
574 PROCEDURE drop_trigger(p_name IN VARCHAR2) IS
575 BEGIN
576 IF trigger_exists(p_name) THEN
577 ad_ddl.do_ddl(
578 get_applsys_user,
579 get_table_product(get_table_from_trigger(p_name)),
580 ad_ddl.drop_trigger,
581 'DROP TRIGGER '||p_name,
582 p_name
583 );
584 END IF;
585 END drop_trigger;
586 --
587 -- +---------------------------------------------------------------------------+
588 -- | NAME : drop_trigger_indirect |
589 -- | SCOPE : PUBLIC |
590 -- | DESCRIPTION: See header |
591 -- +---------------------------------------------------------------------------+
592 PROCEDURE drop_trigger_indirect(p_id IN NUMBER) IS
593 --
594 CURSOR get_info(cp_id IN NUMBER) IS
595 SELECT table_name,
596 triggering_action
597 FROM pay_trigger_events
598 WHERE event_id = cp_id;
599 --
600 l_table VARCHAR2(30);
601 l_mode VARCHAR2(1);
602 BEGIN
603 OPEN get_info(p_id);
604 FETCH get_info INTO l_table,l_mode;
605 CLOSE get_info;
606 --
607 drop_trigger(get_trigger_name(p_id,l_table,l_mode));
608 END drop_trigger_indirect;
609 --
610 -- +---------------------------------------------------------------------------+
611 -- | NAME : create_trigger |
612 -- | SCOPE : PUBLIC |
613 -- | DESCRIPTION: See header |
614 -- +---------------------------------------------------------------------------+
615 PROCEDURE create_trigger(
616 p_trigger IN VARCHAR2,
617 p_table IN VARCHAR2,
618 p_action IN VARCHAR2,
619 p_sql IN VARCHAR2
620 ) IS
621 --
622 l_mode VARCHAR2(30) := 'INVALID';
623 BEGIN
624 IF p_action = 'I' THEN
625 l_mode := 'INSERT';
626 ELSIF p_action = 'U' THEN
627 l_mode := 'UPDATE';
628 ELSIF p_action = 'D' THEN
629 l_mode := 'DELETE';
630 END IF;
631 --
632 ad_ddl.do_ddl(
633 get_applsys_user,
634 get_table_product(p_table),
635 ad_ddl.create_trigger,
636 'CREATE OR REPLACE TRIGGER '||p_trigger||' '||
637 'AFTER '||l_mode||' '||
638 'ON '||p_table||' FOR EACH ROW '||
639 p_sql,
640 p_trigger
641 );
642
643 END create_trigger;
644 --
645 --
646 -- +---------------------------------------------------------------------------+
647 -- | NAME : enable_trigger |
648 -- | SCOPE : PUBLIC |
649 -- | DESCRIPTION: See header |
650 -- +---------------------------------------------------------------------------+
651 PROCEDURE enable_trigger(p_trigger IN VARCHAR2,p_enabled IN BOOLEAN) IS
652 --
653 l_mode VARCHAR2(30);
654 --
655 BEGIN
656 --
657 -- Can only do this if the trigger exists
658 IF trigger_exists(p_trigger) THEN
659 --
660 -- Turn the boolean flag into DDL that the database understands
661 IF p_enabled THEN
662 l_mode := 'ENABLE';
663 ELSE
664 l_mode := 'DISABLE';
665 END IF;
666 --
667 -- Use AOL calls to do the DDL 'properly' (although I don't see how you
668 -- could do this 'improperly' :-)
669 ad_ddl.do_ddl(
670 get_applsys_user,
671 get_table_product(get_table_from_trigger(p_trigger)),
672 ad_ddl.alter_trigger,
673 'ALTER TRIGGER '||p_trigger||' '||l_mode,
674 p_trigger
675 );
676 END IF;
677 END enable_trigger;
678 --
679 -- +---------------------------------------------------------------------------+
680 -- | NAME : replace_placeholders |
681 -- | SCOPE : PUBLIC |
682 -- | DESCRIPTION: See header |
683 -- +---------------------------------------------------------------------------+
684 PROCEDURE replace_placeholders(
685 p_sql IN OUT NOCOPY VARCHAR2,
686 p_id IN NUMBER,
687 p_extra IN VARCHAR2 DEFAULT NULL
688 ) IS
689 --
690 -- Get the mappings that the user has defined, or have been automatically
691 -- generated, only initialisations can use placeholders and placeholders
692 -- are always inputs
693 CURSOR get_places(cp_id IN NUMBER) IS
694 SELECT parameter_name,
695 value_name
696 FROM pay_trigger_parameters
697 WHERE usage_type = 'I'
698 AND parameter_type = 'I'
699 AND usage_id = cp_id;
700 --
701 l_replace VARCHAR2(60);
702 --
703 BEGIN
704 --
705 -- Get all the mappings
706 FOR l_rec IN get_places(p_id) LOOP
707 l_replace := l_rec.value_name;
708 --
709 -- Add any extra text (used by the statement verifier)
710 IF p_extra IS NOT NULL THEN
711 IF SUBSTR(l_replace,1,LENGTH(p_extra)) <> p_extra THEN
712 l_replace := p_extra||l_replace;
713 END IF;
714 END IF;
715 --
716 -- Modify the SQL statement
717 p_sql := REPLACE(p_sql,l_rec.parameter_name,l_replace);
718 END LOOP;
719 END replace_placeholders;
720 --
721 -- +---------------------------------------------------------------------------+
722 -- | NAME : map_select_list |
723 -- | SCOPE : PRIVATE |
724 -- | DESCRIPTION: Describe the supplied SQL select statement, create parameter |
725 -- | mappings if the validate only parameter is FALSE |
726 -- | PARAMETERS : p_id - The primary key of the initialisation that |
727 -- | owns this statement |
728 -- | p_code - The SQL select statement |
729 -- | p_validate_only - Set to TRUE if you only want to check that |
730 -- | the SQL is OK. |
731 -- | RETURNS : None |
732 -- | RAISES : could_not_analyse_query - If the Parse or Describe_Columns |
733 -- | calls fail, probably because your |
734 -- | SQL statement is pants |
735 -- +---------------------------------------------------------------------------+
736 PROCEDURE map_select_list(
737 p_id IN NUMBER,
738 p_code IN VARCHAR2,
739 p_validate_only IN BOOLEAN DEFAULT FALSE
740 ) IS
741 --
742 l_csr INTEGER;
743 l_cols INTEGER;
744 l_desc Dbms_Sql.Desc_Tab;
745 l_name VARCHAR2(35);
746 l_code VARCHAR2(32767) := p_code;
747 l_rc NUMBER;
748 --
749 BEGIN
750 --
751 -- Open a dynamic SQL cursor for parsing and describing
752 l_csr := Dbms_Sql.Open_Cursor;
753 --
754 -- Replace the placeholders but add a colon to the start of local
755 -- variable names (turning them into bind variables) so that the
756 -- statement will parse OK
757 replace_placeholders(l_code,p_id,':');
758 --
759 BEGIN
760 --
761 -- Parse and describe the statement
762 Dbms_Sql.Parse(l_csr,l_code,Dbms_Sql.Native);
763 Dbms_Sql.Describe_Columns(l_csr,l_cols,l_desc);
764 Dbms_Sql.Close_Cursor(l_csr);
765 EXCEPTION
766 WHEN OTHERS THEN
767 --
768 -- Turn all errors into one of our custom errors
769 Dbms_Sql.Close_Cursor(l_csr);
770 fnd_message.set_name('PAY','PAY_DYG_CANNOT_ANALYSE_QUERY');
771 app_exception.raise_exception;
772 END;
773 --
774 -- Process all the columns that the Describe told us about
775 -- N.B. This DOES NOT WORK CORRECTLY for aliased columns, boo!
776 FOR l_cnt IN 1..l_cols LOOP
777 l_name := 'l_'||LOWER(l_desc(l_cnt).col_name);
778 -- We should probably validate l_name here but not doing it gives
779 -- users a bit more flexibility with the way in which they use the form
780 --
781 IF NOT p_validate_only THEN
782 --
783 -- If we're not just validating the statement then insert (or update,
784 -- see the 'insert_parameters' description) the parameter mapping
785 l_rc := insert_parameters(
786 'I',
787 p_id,
788 'R',
789 LOWER(l_desc(l_cnt).col_name),
790 l_name,
791 'Y'
792 );
793 END IF;
794 END LOOP;
795 --
796 END map_select_list;
797 --
798 -- +---------------------------------------------------------------------------+
799 -- | NAME : scan_for_placeholders |
800 -- | SCOPE : PRIVATE |
801 -- | DESCRIPTION: Find the placeholders in the supplied code. Optionally |
802 -- | add default parameter mappings to the parameter table |
803 -- | PARAMETERS : p_id - The primary key of the initialisation that |
804 -- | owns this statement |
805 -- | p_code - The SQL or PL/SQL code |
806 -- | p_validate_only - Set to TRUE if you only want to check that |
807 -- | the statement is OK, i.e. not create maps |
808 -- | RETURNS : None |
809 -- | RAISES : None |
810 -- +---------------------------------------------------------------------------+
811 PROCEDURE scan_for_placeholders(
812 p_id IN NUMBER,
813 p_code IN VARCHAR2,
814 p_type IN VARCHAR2,
815 p_validate_only IN BOOLEAN DEFAULT FALSE
816 ) IS
817 --
818 l_st NUMBER;
819 l_en NUMBER;
820 l_name VARCHAR2(35);
821 l_holder VARCHAR2(35);
822 --
823 l_pl NUMBER := 0;
824 l_rc NUMBER;
825 --
826 BEGIN
827 --
828 -- Scan all through the code
829 FOR l_cnt IN 1..LENGTH(p_code) LOOP
830 --
831 -- If we're not currently looking at a placeholder then see if we're on
832 -- the start of one and remember the position if we are
833 IF l_st IS NULL THEN
834 IF UPPER(SUBSTR(p_code,l_cnt,3)) = '$L_' OR
835 UPPER(SUBSTR(p_code,l_cnt,5)) IN ('$OLD_','$NEW_')
836 THEN
837 l_st := l_cnt;
838 END IF;
839 ELSE
840 --
841 -- Otherwise see if we're on the end of the placeholder,
842 -- if we are then remember the position
843 IF SUBSTR(p_code,l_cnt,1) = '$' THEN
844 l_en := l_cnt;
845 --
846 -- Work out the placeholder name
847 l_holder := LOWER(REPLACE(SUBSTR(p_code,l_st,(l_en-l_st)+1),'$'));
848 --
849 -- Turn the placeholder into the variable name
850 IF SUBSTR(l_holder,1,2) = 'l_' THEN
851 l_name := l_holder;
852 ELSIF SUBSTR(l_holder,1,4) = 'old_' THEN
853 l_name := REPLACE(l_holder,'old_',':old.');
854 ELSIF SUBSTR(l_holder,1,4) = 'new_' THEN
855 l_name := REPLACE(l_holder,'new_',':new.');
856 ELSE
857 l_name := NULL;
858 END IF;
859 --
860 IF l_name IS NOT NULL THEN
861 -- Check that the variable we're going to use in l_value is valid
862 -- Might implement this at a later date, the form will work without
863 -- it but people can generata code that won't compile
864 NULL;
865 END IF;
866 --
867 IF NOT p_validate_only THEN
868 --
869 -- Create or update the parameter mapping if we don't want to just
870 -- validate the code
871 l_rc := insert_parameters(
872 'I',
873 p_id,
874 'I',
875 SUBSTR(p_code,l_st,(l_en-l_st)+1),
876 l_name,
877 'Y'
878 );
879 END IF;
880 l_pl := l_pl + 1;
881 --
882 -- Null out the starting position 'cos we've just got to the end
883 -- of a placeholder, so we'll now look for a new one
884 l_st := NULL;
885 END IF;
886 END IF;
887 END LOOP;
888 --
889 -- If it's an Assignment type initialisation (and we're not just validating
890 -- the code) then create a default mapping for the return value.
891 IF l_pl = 1 AND l_name IS NOT NULL AND p_type = 'A' THEN
892 IF SUBSTR(l_name,1,5) IN (':old.',':new.') THEN
893 l_name := 'l_'||SUBSTR(l_name,6);
894 ELSE
895 l_name := 'return_variable';
896 END IF;
897 --
898 IF NOT p_validate_only THEN
899 l_rc := insert_parameters('I',p_id,'R',NULL,l_name,'Y');
900 END IF;
901 END IF;
902 --
903 END scan_for_placeholders;
904 --
905 -- +---------------------------------------------------------------------------+
906 -- | NAME : validate_select |
907 -- | SCOPE : PUBLIC |
908 -- | DESCRIPTION: See header |
909 -- +---------------------------------------------------------------------------+
910 PROCEDURE validate_select(
911 p_id IN NUMBER,
912 p_code IN VARCHAR2,
913 p_type IN VARCHAR2
914 ) IS
915 BEGIN
916 -- Should the validate only flag be set on these?
917 -- I think it probably should, I'll check that later.
918 scan_for_placeholders(p_id,p_code,p_type);
919 map_select_list(p_id,p_code);
920 END validate_select;
921 --
922 -- +---------------------------------------------------------------------------+
923 -- | NAME : map_parameter_list |
924 -- | SCOPE : PUBLIC |
925 -- | DESCRIPTION: See header |
926 -- +---------------------------------------------------------------------------+
927 PROCEDURE map_parameter_list(
928 p_id IN NUMBER,
929 p_module IN VARCHAR2,
930 p_type IN VARCHAR2,
931 p_usage IN VARCHAR2,
932 p_validate_only IN BOOLEAN DEFAULT FALSE
933 ) IS
934 --
935 l_overload Dbms_Describe.Number_Table;
936 l_position Dbms_Describe.Number_Table;
937 l_c_level Dbms_Describe.Number_Table;
938 l_arg_name Dbms_Describe.Varchar2_Table;
939 l_dty Dbms_Describe.Number_Table;
940 l_def_val Dbms_Describe.Number_Table;
941 l_p_mode Dbms_Describe.Number_Table;
942 l_length Dbms_Describe.Number_Table;
943 l_precision Dbms_Describe.Number_Table;
944 l_scale Dbms_Describe.Number_Table;
945 l_radix Dbms_Describe.Number_Table;
946 l_spare Dbms_Describe.Number_Table;
947 --
948 l_params INTEGER := 0;
949 l_overloaded BOOLEAN := FALSE;
950 l_weirdtype BOOLEAN := FALSE;
951 l_cackmode BOOLEAN := FALSE;
952 l_name VARCHAR2(2000);
953 l_mode VARCHAR2(1);
954 l_arg VARCHAR2(60);
955 l_rc NUMBER;
956 --
957 BEGIN
958 BEGIN
959 --
960 -- Lovely Oracle supplied package to describe a stored procedure
961 -- into the tables we declared earlier
962 Dbms_Describe.Describe_Procedure(
963 p_module,
964 null,
965 null,
966 l_overload,
967 l_position,
968 l_c_level,
969 l_arg_name,
970 l_dty,
971 l_def_val,
972 l_p_mode,
973 l_length,
974 l_precision,
975 l_scale,
976 l_radix,
977 l_spare
978 );
979 EXCEPTION
980 WHEN OTHERS THEN
981 fnd_message.set_name('PAY','PAY_DYG_CANNOT_DESC_MODULE');
982 fnd_message.set_token('MODULE_NAME',UPPER(p_module),FALSE);
983 app_exception.raise_exception;
984 END;
985 --
986 -- Now we've described the module we'll flip through the parameters
987 -- we were told about and check that they're all OK.
988 BEGIN
989 LOOP
990 l_params := l_params + 1;
991 --
992 -- Set the overloaded flag
993 IF l_overload(l_params) > 1 THEN
994 l_overloaded := TRUE;
995 END IF;
996 --
997 -- This one uses a type we don't support, flag that up guv'nor
998 IF l_dty(l_params) NOT IN (1,2,12) THEN
999 l_weirdtype := TRUE;
1000 END IF;
1001 --
1002 -- Complicated bit to check that the parameter's being used in the
1003 -- right way, here we go;
1004 -- * IN OUT parameters (l_p_mode = 2) aren't ever allowed
1005 -- * If it's being used in a function type initialisation
1006 -- (p_usage = 'I' AND p_type = 'F') and it's an OUT parameter
1007 -- (l_p_mode = 1) and it's not the return value (l_position = 0)
1008 -- then it's wrong
1009 -- * For component usages (p_usage = 'C') OUT parameters
1010 -- (l_p_mode = 1) aren't ever allowed.
1011 -- If we find any of these conditions then set the flag
1012 IF (l_p_mode(l_params) = 2) OR
1013 (p_usage = 'I' AND
1014 p_type = 'F' AND
1015 l_p_mode(l_params) = 1 AND
1016 l_position(l_params) <> 0) OR
1017 (p_usage = 'C' AND l_p_mode(l_params) = 1)
1018 THEN
1019 l_cackmode := TRUE;
1020 END IF;
1021 --
1022 END LOOP;
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 --
1026 -- The describe procedure doesn't tell us how many rows it
1027 -- put into the return tables so use this to quit the loop
1028 -- when we flop over the end of the table
1029 l_params := l_params - 1;
1030 END;
1031 --
1032 -- Raise an error 'cos we can't deal with overloaded stuff
1033 IF l_overloaded THEN
1034 fnd_message.set_name('PAY','PAY_DYG_OVERLOADS_EXIST');
1035 fnd_message.set_token('MODULE_NAME',UPPER(p_module),FALSE);
1036 app_exception.raise_exception;
1037 END IF;
1038 --
1039 -- Raise an error 'cos we can't have weird types
1040 IF l_weirdtype THEN
1041 fnd_message.set_name('PAY','PAY_DYG_UNSUPPORTED_TYPE');
1042 fnd_message.set_token('MODULE_NAME',UPPER(p_module),FALSE);
1043 app_exception.raise_exception;
1044 END IF;
1045 --
1046 -- Raise an error if the parameters are being used in the wrong way
1047 IF l_cackmode THEN
1048 fnd_message.set_name('PAY','PAY_DYG_INCOMPATIBLE_MODE');
1049 fnd_message.set_token('MODULE_NAME',UPPER(p_module),FALSE);
1050 app_exception.raise_exception;
1051 END IF;
1052 --
1053 -- Set up the default parameter mappings if we didn't just want to validate
1054 -- the code
1055 IF NOT p_validate_only THEN
1056 FOR l_cnt IN 1..l_params LOOP
1057 --
1058 -- Is it the return value, or IN or OUT (shake it all about)?
1059 IF l_position(l_cnt) = 0 THEN
1060 l_mode := 'R';
1061 ELSE
1062 IF l_p_mode(l_cnt) = 0 THEN
1063 l_mode := 'I';
1064 ELSIF l_p_mode(l_cnt) = 1 THEN
1065 l_mode := 'O';
1066 END IF;
1067 END IF;
1068 --
1069 -- If it's the return value then just create a dummy return that the
1070 -- user'll have to change, we've got no way of working out a sensible
1071 -- default mapping for this
1072 IF l_mode = 'R' THEN
1073 l_rc := insert_parameters(
1074 p_usage,
1075 p_id,
1076 l_mode,
1077 NULL,
1078 'return_variable',
1079 'Y'
1080 );
1081 ELSE
1082 l_arg := LOWER(l_arg_name(l_cnt));
1083 --
1084 -- Turn the parameter name into a local variable name
1085 -- or a bind variable.
1086 -- At some point we should also check that bind variables aren't
1087 -- being mapped to OUT parameters, but not here.
1088 IF SUBSTR(l_arg,1,4) = 'p_l_' THEN
1089 l_name := 'l_'||SUBSTR(l_arg,5);
1090 ELSIF SUBSTR(l_arg,1,6) = 'p_old_' THEN
1091 l_name := ':old.'||SUBSTR(l_arg,7);
1092 ELSIF SUBSTR(l_arg,1,6) = 'p_new_' THEN
1093 l_name := ':new.'||SUBSTR(l_arg,7);
1094 ELSE
1095 l_name := NULL;
1096 END IF;
1097 --
1098 -- Create or update the parameter mapping
1099 l_rc := insert_parameters(p_usage,p_id,l_mode,l_arg,l_name,'Y');
1100 END IF;
1101 END LOOP;
1102 END IF;
1103 END map_parameter_list;
1104 --
1105 -- +---------------------------------------------------------------------------+
1106 -- | NAME : automap_parameters |
1107 -- | SCOPE : PUBLIC |
1108 -- | DESCRIPTION: See header |
1109 -- +---------------------------------------------------------------------------+
1110 PROCEDURE automap_parameters(
1111 p_id IN NUMBER,
1112 p_usage IN VARCHAR2,
1113 p_comp_id IN NUMBER DEFAULT NULL
1114 ) IS
1115 --
1116 -- Get the initialisation we asked for, or all of them if the ID's NULL
1117 CURSOR get_inits(cp_id IN NUMBER,cp_comp IN NUMBER) IS
1118 SELECT initialisation_id,
1119 plsql_code,
1120 process_type
1121 FROM pay_trigger_initialisations
1122 WHERE (initialisation_id = cp_comp OR cp_comp IS NULL)
1123 AND (event_id = cp_id OR cp_id IS NULL);
1124 --
1125 -- Get the component we asked for, or all of them if the ID's NULL
1126 CURSOR get_comps(cp_id IN NUMBER,cp_comp IN NUMBER) IS
1127 SELECT component_id,
1128 module_name
1129 FROM pay_trigger_components
1130 WHERE (component_id = cp_comp OR cp_comp IS NULL)
1131 AND (event_id = cp_id OR cp_id IS NULL);
1132 --
1133 BEGIN
1134 --
1135 -- We want to map parameters for initialisations
1136 IF p_usage = 'I' THEN
1137 FOR init_rec IN get_inits(p_id,p_comp_id) LOOP
1138 --
1139 -- Scan SQL select or assignment type initialisations for placeholders
1140 IF init_rec.process_type IN ('S','A') THEN
1141 scan_for_placeholders(
1142 init_rec.initialisation_id,
1143 init_rec.plsql_code,
1144 init_rec.process_type
1145 );
1146 END IF;
1147 --
1148 -- Map the select list of SQL statements
1149 IF init_rec.process_type = 'S' THEN
1150 map_select_list(init_rec.initialisation_id,init_rec.plsql_code);
1151 END IF;
1152 --
1153 -- Map the parameter list for function or procedure type initialisations
1154 IF init_rec.process_type IN ('F','P') THEN
1155 map_parameter_list(
1156 init_rec.initialisation_id,
1157 init_rec.plsql_code,
1158 init_rec.process_type,
1159 'I'
1160 );
1161 END IF;
1162 --
1163 END LOOP;
1164 END IF;
1165 --
1166 -- If we want to map the parameters of component modules...
1167 IF p_usage = 'C' THEN
1168 FOR comp_rec IN get_comps(p_id,p_comp_id) LOOP
1169 --
1170 -- They're always procedures, so we always need to map the parameters
1171 map_parameter_list(comp_rec.component_id,comp_rec.module_name,'P','C');
1172 END LOOP;
1173 END IF;
1174 END automap_parameters;
1175 --
1176 -- +---------------------------------------------------------------------------+
1177 -- | NAME : lob_to_varchar2 |
1178 -- | SCOPE : PUBLIC |
1179 -- | DESCRIPTION: See header |
1180 -- +---------------------------------------------------------------------------+
1181 FUNCTION lob_to_varchar2(p_clob IN OUT NOCOPY CLOB) RETURN VARCHAR2 IS
1182 l_vc2 VARCHAR2(32767);
1183 l_s BINARY_INTEGER;
1184 BEGIN
1185 --
1186 -- Use the supplied package to read the data out of the CLOB and dump it
1187 -- into a VARCHAR2, which are easier to manipulate.
1188 -- N.B. CLOBs can hold something like 2Gb, this VARCHAR2 can only hold
1189 -- about 32Kb. Ooer. Shouldn't really be a problem, 32Kb is quite
1190 -- a big bit of PL/SQL, just don't put millions of comments in :-)
1191 Dbms_Lob.Open(p_clob,Dbms_Lob.Lob_Readonly);
1192 l_s := Dbms_Lob.Getlength(p_clob);
1193 Dbms_Lob.Read(p_clob,l_s,1,l_vc2);
1194 Dbms_Lob.Close(p_clob);
1195 --
1196 -- Send back the text
1197 RETURN l_vc2;
1198 END lob_to_varchar2;
1199 --
1200 -- +---------------------------------------------------------------------------+
1201 -- | NAME : get_reverted |
1202 -- | SCOPE : PUBLIC |
1203 -- | DESCRIPTION: See header |
1204 -- +---------------------------------------------------------------------------+
1205 PROCEDURE get_reverted(
1206 p_id IN NUMBER,
1207 p_head OUT NOCOPY VARCHAR2,
1208 p_body OUT NOCOPY VARCHAR2
1209 ) IS
1210 --
1211 -- Fetch the last saved version of the code from the database table
1212 CURSOR get_old(cp_id IN NUMBER) IS
1213 SELECT header_code,body_code
1214 FROM pay_trigger_support
1215 WHERE support_id = cp_id;
1216 --
1217 l_h CLOB;
1218 l_b CLOB;
1219 --
1220 BEGIN
1221 --
1222 -- Grab the data into the two CLOBS
1223 OPEN get_old(p_id);
1224 FETCH get_old INTO l_h,l_b;
1225 CLOSE get_old;
1226 --
1227 -- Turn the CLOBs into VARCHAR2s to send them back. Forms went all weird
1228 -- when I tried to pass CLOBs back and forth, something to do with RPC
1229 -- (or whatever it's called) perchance?
1230 p_head := lob_to_varchar2(l_h);
1231 p_body := lob_to_varchar2(l_b);
1232 --
1233 END get_reverted;
1234 --
1235 -- +---------------------------------------------------------------------------+
1236 -- | NAME : get_package_name |
1237 -- | SCOPE : PRIVATE |
1238 -- | DESCRIPTION: Use this so that the supporting package name is always |
1239 -- | generated the same way |
1240 -- | PARAMETERS : p_event - The primary key of the event that this supporting |
1241 -- | package supports |
1242 -- | p_table - The table that the owning trigger will get created |
1243 -- | against (to save us having to work it out) |
1244 -- | RETURNS : The generated package name that should be used |
1245 -- | RAISES : None |
1246 -- +---------------------------------------------------------------------------+
1247 FUNCTION get_package_name(
1248 p_event IN NUMBER,
1249 p_table IN VARCHAR2
1250 ) RETURN VARCHAR2 IS
1251 --
1252 l_newtab VARCHAR2(30);
1253 l_id VARCHAR2(30);
1254 --
1255 BEGIN
1256 --
1257 -- The ID will be the event's primary key followed by 'DYG' to denote
1258 -- that the package has been DYnamically Generated
1259 l_id := '_'||LTRIM(RTRIM(TO_CHAR(p_event)))||'_DYG';
1260 --
1261 -- Fiddle about with the table name in a similar fashion to what we do
1262 -- when we're creating a trigger name
1263 l_newtab := SUBSTR(REPLACE(p_table,'_'),1,30-LENGTH(l_id));
1264 --
1265 -- Send back the two bits joined together
1266 RETURN LOWER(l_newtab||l_id);
1267 END get_package_name;
1268 --
1269 -- +---------------------------------------------------------------------------+
1270 -- | NAME : drop_package |
1271 -- | SCOPE : PRIVATE |
1272 -- | DESCRIPTION: Drop the specified database package |
1273 -- | PARAMETERS : p_table - The table that the owning event's trigger will be |
1274 -- | created against, needed so we can work out what |
1275 -- | application (PAY, PER) the trigger belongs to |
1276 -- | p_name - The name of the package |
1277 -- | RETURNS : None |
1278 -- | RAISES : None |
1279 -- +---------------------------------------------------------------------------+
1280 PROCEDURE drop_package(p_table IN VARCHAR2,p_name IN VARCHAR2) IS
1281 BEGIN
1282 --
1283 -- Use the AOL DLL routine
1284 ad_ddl.do_ddl(
1285 get_applsys_user,
1286 get_table_product(p_table),
1287 ad_ddl.drop_package,
1288 'DROP PACKAGE '||p_name,
1289 p_name
1290 );
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 --
1294 -- This shouldn't ever occur 'cos do_ddl seems to trap everything
1295 hr_utility.set_location('DROP PACKAGE '||p_name,ABS(SQLCODE));
1296 END drop_package;
1297 --
1298 FUNCTION module_ok(p_name IN VARCHAR2,p_type IN VARCHAR2) RETURN BOOLEAN IS
1299 --
1300 CURSOR get_errors(cp_name IN VARCHAR2,cp_type IN VARCHAR2) IS
1301 SELECT 'SOME'
1302 FROM user_errors
1303 WHERE name = UPPER(cp_name)
1304 AND type = cp_type;
1305 --
1306 l_cnt VARCHAR2(10);
1307 --
1308 BEGIN
1309 --
1310 -- Try and fetch some errors
1311 OPEN get_errors(UPPER(p_name),p_type);
1312 FETCH get_errors INTO l_cnt;
1313 IF get_errors%NOTFOUND THEN
1314 l_cnt := 'NONE';
1315 END IF;
1316 CLOSE get_errors;
1317 --
1318 RETURN (l_cnt = 'NONE');
1319 END module_ok;
1320
1321 -- -------------------- build_dyt_pkg_from_tbl ----------------------------
1322 -- Description:
1323 -- Runs a SQL statement using the dbms_sql package. No bind variables
1324 -- allowed. This procedure uses pl/sql table of varchar2 as an input
1325 -- and hence is suitable to compile very large packages i.e more than
1326 -- 32767 char.
1327 -- ------------------------------------------------------------------------
1328 procedure build_dyt_pkg_from_tbl(
1329 p_package_body dbms_sql.varchar2s,
1330 p_package_index number,
1331 p_body boolean )
1332 is
1333 l_csr_sql integer;
1334 l_rows number;
1335 l_proc varchar2(80) := g_package||'.build_dyt_pkg_from_tbl';
1336
1337 begin
1338 hr_utility.set_location(l_proc,10);
1339 hr_utility.trace('p_package_index - '||p_package_index);
1340
1341 --
1342 l_csr_sql := dbms_sql.open_cursor;
1343 dbms_sql.parse( l_csr_sql, p_package_body,1,p_package_index,p_body, dbms_sql.v7 );
1344 l_rows := dbms_sql.execute( l_csr_sql );
1345 dbms_sql.close_cursor( l_csr_sql );
1346 --
1347
1348 hr_utility.set_location(l_proc,900);
1349 exception
1350 when others then
1351 hr_utility.trace('Unhandled Exception: '||l_proc);
1352 raise;
1353 end build_dyt_pkg_from_tbl;
1354
1355 --
1356 -- +---------------------------------------------------------------------------+
1357 -- | NAME : execute_by_chopping_up |
1358 -- | SCOPE : PRIVATE |
1359 -- | DESCRIPTION: We store the supporting packages as CLOBs (which get |
1360 -- | converted back and forth into VARCHAR2s when we need to) but |
1361 -- | the AOL routine to create a package needs it in a PL/SQL |
1362 -- | table. This routine chops up the package code, sticks it into|
1363 -- | the right kind of table and creates the package. |
1364 -- | PARAMETERS : p_name - The name of the package to create, the AOL create |
1365 -- | routine seems to trap all the errors so we need |
1366 -- | this to check for errors |
1367 -- | p_table - The table that the owning event's trigger will be |
1368 -- | created against, needed to work out what product |
1369 -- | this package belongs to |
1370 -- | p_sql - The PL/SQL package creation code |
1371 -- | p_body - A flag, 'TRUE' if this is the body, else 'FALSE' |
1372 -- | RETURNS : Flag to indicate whether the code compiled OK or not |
1373 -- | RAISES : None |
1374 -- +---------------------------------------------------------------------------+
1375 FUNCTION execute_by_chopping_up(
1376 p_name IN VARCHAR2,
1377 p_table IN VARCHAR2,
1378 p_sql IN VARCHAR2,
1379 p_body IN VARCHAR2
1380 ) RETURN BOOLEAN IS
1381 --
1382 l_num NUMBER := 1;
1383 l_pos NUMBER := 1;
1384 l_offs NUMBER := 1;
1385 l_line VARCHAR2(254);
1386 l_user VARCHAR2(30) := get_applsys_user;
1387 l_prod VARCHAR2(50) := get_table_product(p_table);
1388 l_type VARCHAR2(20);
1389 --
1390 BEGIN
1391 --
1392 -- Loop round all the code
1393 LOOP
1394 --
1395 -- Find the next end of line (relies on lines being less than 254
1396 -- characters long :-) or just use whatever's left in the string
1397 l_pos := INSTR(p_sql,g_eol,l_offs);
1398 IF l_pos > 0 THEN
1399 l_line := SUBSTR(p_sql,l_offs,l_pos-l_offs);
1400 l_offs := l_pos + 1;
1401 ELSE
1402 l_line := SUBSTR(p_sql,l_offs);
1403 l_offs := LENGTH(p_sql) + 1;
1404 END IF;
1405 --
1406 -- Shove the line we found into the PL/SQL table
1407 ad_ddl.build_package(l_line,l_num);
1408 l_num := l_num + 1;
1409 --
1410 -- Bail when we're past the end of the string
1411 EXIT WHEN l_offs > LENGTH(p_sql);
1412 END LOOP;
1413 --
1414 -- Call the AOL routine to create the package in the 'right' way
1415 ad_ddl.create_package(
1416 l_user,
1417 l_prod,
1418 UPPER(p_name),
1419 p_body,
1420 1,
1421 l_num - 1
1422 );
1423 --
1424 -- Tell the caller whether the procedure created OK or not
1425 IF p_body = 'TRUE' THEN
1426 l_type := 'PACKAGE BODY';
1427 ELSE
1428 l_type := 'PACKAGE';
1429 END IF;
1430 --
1431 RETURN (module_ok(p_name,l_type));
1432 END execute_by_chopping_up;
1433 --
1434 -- +---------------------------------------------------------------------------+
1435 -- | NAME : compile_package |
1436 -- | SCOPE : PUBLIC |
1437 -- | DESCRIPTION: See header |
1438 -- +---------------------------------------------------------------------------+
1439 PROCEDURE compile_package(
1440 p_event IN NUMBER,
1441 p_table IN VARCHAR2,
1442 p_header IN VARCHAR2,
1443 p_body IN VARCHAR2,
1444 p_name IN OUT NOCOPY VARCHAR2,
1445 p_head_ok IN OUT NOCOPY BOOLEAN,
1446 p_body_ok IN OUT NOCOPY BOOLEAN
1447 ) IS
1448 l_sql VARCHAR2(32767);
1449 BEGIN
1450 --
1451 -- Get the name of the package according to the standard
1452 p_name := get_package_name(p_event,p_table);
1453 --
1454 -- Make up the full CREATE statement then execute using the AOL routines
1455 l_sql := 'CREATE OR REPLACE PACKAGE '||
1456 p_name||' AS '||g_eol||p_header||g_eol||
1457 'END '||p_name||';';
1458 p_head_ok := execute_by_chopping_up(p_name,p_table,l_sql,p_body=>'FALSE');
1459 p_body_ok := p_head_ok;
1460 --
1461 -- Make up the full BODY CREATE statement then execute using the AD routines
1462 IF p_head_ok THEN
1463 l_sql := 'CREATE OR REPLACE PACKAGE BODY '||
1464 p_name||' AS '||g_eol||p_body||g_eol||
1465 'END '||p_name||';';
1466 p_body_ok := execute_by_chopping_up(p_name,p_table,l_sql,p_body=>'TRUE');
1467 END IF;
1468 --
1469 END compile_package;
1470 --
1471 -- +---------------------------------------------------------------------------+
1472 -- | NAME : table_has_business_group |
1473 -- | SCOPE : PUBLIC |
1474 -- | DESCRIPTION: See header |
1475 -- +---------------------------------------------------------------------------+
1476 FUNCTION table_has_business_group(p_table IN VARCHAR2) RETURN BOOLEAN IS
1477 --
1478 -- Cursor to fetch the nullable flag
1479 CURSOR get_null(cp_table IN VARCHAR2) IS
1480 SELECT atc.nullable
1481 FROM all_tab_columns atc
1482 WHERE atc.column_name = 'BUSINESS_GROUP_ID'
1483 AND atc.table_name = p_table
1484 AND atc.owner = g_pay_schema;
1485 --
1486 l_rc VARCHAR2(1);
1487 BEGIN
1488 --
1489 -- Fetch Schema name if required
1490 --
1491 g_pay_schema := get_table_owner(p_table);
1492 --
1493 -- Fetch column information from the RDBMS data dictionary
1494 OPEN get_null(p_table);
1495 FETCH get_null INTO l_rc;
1496 IF get_null%NOTFOUND THEN
1497 l_rc := 'Y';
1498 END IF;
1499 CLOSE get_null;
1500 --
1501 -- Return the boolean equivalent to the NOT NULL flag
1502 RETURN (l_rc = 'N');
1503 END table_has_business_group;
1504 --
1505 -- +---------------------------------------------------------------------------+
1506 -- | NAME : table_has_payroll |
1507 -- | SCOPE : PUBLIC |
1508 -- | DESCRIPTION: See header |
1509 -- +---------------------------------------------------------------------------+
1510 FUNCTION table_has_payroll(p_table IN VARCHAR2) RETURN BOOLEAN IS
1511 --
1512 -- Cursor to fetch the nullable flag
1513 CURSOR get_null(cp_table IN VARCHAR2) IS
1514 SELECT atc.nullable
1515 FROM all_tab_columns atc
1516 WHERE atc.column_name = 'PAYROLL_ID'
1517 AND atc.table_name = p_table
1518 AND atc.owner = g_pay_schema;
1519 --
1520 l_rc VARCHAR2(1);
1521 BEGIN
1522 --
1523 -- Fetch Schema name if required
1524 --
1525 g_pay_schema := get_table_owner(p_table);
1526 --
1527 --
1528 -- Fetch the nullable flag from the data dictionary
1529 OPEN get_null(p_table);
1530 FETCH get_null INTO l_rc;
1531 IF get_null%NOTFOUND THEN
1532 l_rc := 'Y';
1533 END IF;
1534 CLOSE get_null;
1535 --
1536 -- Return the boolean equivalent of the NOT NULL flag
1537 RETURN (l_rc = 'N');
1538 END table_has_payroll;
1539 --
1540 -- +---------------------------------------------------------------------------+
1541 -- | NAME : default_declarations |
1542 -- | SCOPE : PRIVATE |
1543 -- | DESCRIPTION: Create the default declaration definitions that a trigger |
1544 -- | will need if it's created against a table with a business |
1545 -- | group ID or a payroll ID |
1546 -- | PARAMETERS : p_has_bus - Previously derived flag to indicate the presence|
1547 -- | of a manadatory business_group_id in the table |
1548 -- | p_has_pay - Previously derived flag to indicate the presence|
1549 -- | of a manadatory payroll_id column in the table |
1550 -- | p_event_id - The primary key of the event which will own |
1551 -- | this trigger |
1552 -- | RETURNS : None |
1553 -- | RAISES : None |
1554 -- +---------------------------------------------------------------------------+
1555 PROCEDURE default_declarations(
1556 p_has_bus IN BOOLEAN,
1557 p_has_pay IN BOOLEAN,
1558 p_event_id IN NUMBER
1559 ) IS
1560 l_rc NUMBER;
1561 BEGIN
1562 IF p_has_bus THEN
1563 l_rc := insert_declarations(p_event_id,'business_group_id','N');
1564 l_rc := insert_declarations(p_event_id,'legislation_code','C',30);
1565 END IF;
1566 --
1567 IF p_has_pay THEN
1568 l_rc := insert_declarations(p_event_id,'payroll_id','N');
1569 END IF;
1570 END default_declarations;
1571 --
1572 -- +---------------------------------------------------------------------------+
1573 -- | NAME : default_initialisations |
1574 -- | SCOPE : PRIVATE |
1575 -- | DESCRIPTION: Create the default initialisation definitions that a trigger |
1576 -- | will need if it's created against a table with a business |
1577 -- | group ID or a payroll ID |
1578 -- | PARAMETERS : p_has_bus - Previously derived flag to indicate the presence|
1579 -- | of a manadatory business_group_id in the table |
1580 -- | p_has_pay - Previously derived flag to indicate the presence|
1581 -- | of a manadatory payroll_id column in the table |
1582 -- | p_event_id - The primary key of the event which will own |
1583 -- | this trigger |
1584 -- | p_type - The type of trigger, I, U or D for Insert, |
1585 -- | Update or Delete |
1586 -- | p_bus_id - The primary key of the newly created business |
1587 -- | group id initialisation record |
1588 -- | p_leg_id - Primary key of new legislation ID initialisation|
1589 -- | record |
1590 -- | p_pay_id - Primary key of new payroll ID initialisation |
1591 -- | record |
1592 -- | RETURNS : The primary keys of the newly created initialisation IDs, |
1593 -- | via OUT parameters, if there are any, otherwise these values |
1594 -- | will be NULL |
1595 -- | RAISES : None |
1596 -- +---------------------------------------------------------------------------+
1597 PROCEDURE default_initialisations(
1598 p_has_bus IN BOOLEAN,
1599 p_has_pay IN BOOLEAN,
1600 p_event_id IN NUMBER,
1601 p_type IN VARCHAR2,
1602 p_bus_id OUT NOCOPY NUMBER,
1603 p_leg_id OUT NOCOPY NUMBER,
1604 p_pay_id OUT NOCOPY NUMBER
1605 ) IS
1606 --
1607 l_age VARCHAR2(10);
1608 --
1609 BEGIN
1610 --
1611 -- Use the :old or :new record depending on the trigger type
1612 IF p_type = 'D' THEN
1613 l_age := 'OLD';
1614 ELSE
1615 l_age := 'NEW';
1616 END IF;
1617 --
1618 -- Create the business_group_id and legislation_code initialisations if
1619 -- the table has a mandatory business group ID column
1620 IF p_has_bus THEN
1621 p_bus_id := insert_initialisations(
1622 p_event_id,
1623 -20,
1624 '$'||l_age||'_BUSINESS_GROUP_ID$',
1625 'A'
1626 );
1627 p_leg_id := insert_initialisations(
1628 p_event_id,
1629 -10,
1630 'SELECT legislation_code '||
1631 'FROM per_business_groups '||
1632 'WHERE business_group_id = $L_BUSINESS_GROUP_ID$',
1633 'S'
1634 );
1635 END IF;
1636 --
1637 -- Create the payroll_id initialisation if the table has a manadatory
1638 -- payroll_id column
1639 IF p_has_pay THEN
1640 p_pay_id := insert_initialisations(
1641 p_event_id,
1642 -30,
1643 '$'||l_age||'_PAYROLL_ID$',
1644 'A'
1645 );
1646 END IF;
1647 --
1648 END default_initialisations;
1649 --
1650 -- +---------------------------------------------------------------------------+
1651 -- | NAME : default_parameters |
1652 -- | SCOPE : PRIVATE |
1653 -- | DESCRIPTION: Create the parameter mappings for the default modules |
1654 -- | belonging to an event (initialisations and components) |
1655 -- | PARAMETERS : p_event_id - The primary key of the event which will own |
1656 -- | the trigger that needs these parameters |
1657 -- | RETURNS : None |
1658 -- | RAISES : None |
1659 -- +---------------------------------------------------------------------------+
1660 PROCEDURE default_parameters(
1661 p_event_id IN NUMBER
1662 ) IS
1663 BEGIN
1664 --
1665 -- Simply call the auto-mapper for all modules in this event, nothing
1666 -- apart from the defaults should have been created at this point
1667 -- Currently no default components are created so just call the automapper
1668 -- for initialisations
1669 automap_parameters(p_event_id,'I',NULL);
1670 END default_parameters;
1671 --
1672 -- +---------------------------------------------------------------------------+
1673 -- | NAME : flag_to_boolean |
1674 -- | SCOPE : PRIVATE |
1675 -- | DESCRIPTION: Simple helper function to convert a Y/N flag to a boolean |
1676 -- | PARAMETERS : p_flag - The text flag, should be Y or N |
1677 -- | RETURNS : TRUE if the flag is 'Y', FALSE otherwise |
1678 -- | RAISES : None |
1679 -- +---------------------------------------------------------------------------+
1680 FUNCTION flag_to_boolean(p_flag IN VARCHAR2) RETURN BOOLEAN IS
1681 BEGIN
1682 -- Could do; IF p_flag = 'Y' THEN... But I think this way's neater
1683 RETURN (NVL(p_flag,'N') = 'Y');
1684 END flag_to_boolean;
1685 --
1686 -- +---------------------------------------------------------------------------+
1687 -- | NAME : generate_trigger |
1688 -- | SCOPE : PUBLIC |
1689 -- | DESCRIPTION: See header |
1690 -- +---------------------------------------------------------------------------+
1691 PROCEDURE generate_trigger(
1692 p_id IN NUMBER,
1693 p_name IN OUT NOCOPY VARCHAR2,
1694 p_ok OUT NOCOPY BOOLEAN
1695 ) IS
1696 --
1697 -- Cursor to get the information about the trigger from the database
1698 CURSOR csr_name(cp_id IN NUMBER) IS
1699 SELECT pte.table_name, pte.triggering_action,
1700 pte.generated_flag, pte.enabled_flag,
1701 pdt.dated_table_id, nvl(pdt.dyn_trigger_type,'T'),
1702 pdt.dyn_trigger_package_name
1703 FROM pay_dated_tables pdt,
1704 pay_trigger_events pte
1705 WHERE pte.table_name = pdt.table_name(+)
1706 AND pte.event_id = cp_id;
1707 --
1708 l_table VARCHAR2(30);
1709 l_trigger VARCHAR2(30);
1710 l_action VARCHAR2(1);
1711 l_gen_flag VARCHAR2(1);
1712 l_enb_flag VARCHAR2(1);
1713 l_tab_id NUMBER;
1714 l_dyn_type VARCHAR2(1);
1715 l_dyt_pkg VARCHAR2(80);
1716 --
1717 l_sql VARCHAR2(32767);
1718 l_rc NUMBER;
1719 --
1720 l_proc VARCHAR2(30) := 'generate_trigger';
1721 BEGIN
1722 hr_utility.set_location(' Entering: '||l_proc,10);
1723 --Calling from SINGLE (Dynamic Triggers Form)
1724 --
1725 -- Fetch the trigger event information
1726 OPEN csr_name(p_id);
1727 FETCH csr_name INTO l_table,l_action,
1728 l_gen_flag,l_enb_flag,
1729 l_tab_id,l_dyn_type,l_dyt_pkg;
1730 CLOSE csr_name;
1731 -- TEST TO SEE HOW USER WISHES TO HANDLE DYN TRIGGERS
1732 -- Eg Set up to store as db triggers, as package code or both
1733 -- Added by jford 1-OCT-02 as part of cont calc
1734 --
1735 IF (l_dyn_type = 'P' or l_dyn_type = 'B') THEN
1736 hr_utility.trace(' Handle Dynamic Triggers as Package Style.');
1737 -- dyn trigger code should be handled as package
1738 -- >> GENERATE PACKAGE
1739 -- generate code FOR ALL DYT's ON TABLE eg many dyn_triggers
1740 gen_dyt_pkg_full_code(l_tab_id,p_ok);
1741 p_name := l_dyt_pkg;
1742 ELSE
1743 -- type is just original 'T' Trigger mechanism so use existing code
1744 -- >> GENERATE DBMS TRIGGER
1745 hr_utility.trace(' Handle Dynamic Triggers as Individual Database Trigger Style.');
1746 -- Get the trigger name in the standard format
1747 --if weve got an old crap dyt_pkg, then dump it
1748 if (l_dyt_pkg is not null) then
1749 drop_package(l_table,l_dyt_pkg);
1750 update pay_dated_tables set dyn_trigger_package_name = null
1751 where table_name = l_dyt_pkg and dated_table_id = l_tab_id;
1752 end if;
1753 l_trigger := get_trigger_name(p_id,l_table,l_action);
1754 p_name := l_trigger;
1755 --
1756 -- If we should be generating the trigger then lets do it
1757 IF flag_to_boolean(l_gen_flag) THEN
1758 --
1759 -- Generate the PL/SQL block that the trigger will use
1760 generate_code(p_id,l_sql);
1761 --
1762 -- Create the trigger using the generated PL/SQL and the AOL routines
1763 create_trigger(l_trigger,l_table,l_action,l_sql);
1764 p_ok := module_ok(l_trigger,'TRIGGER');
1765 if (p_ok) then
1766 hr_utility.trace(' Database Trigger '||p_name||' created with success');
1767 else
1768 hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1769 update pay_trigger_events
1770 set generated_flag = 'N', enabled_flag = 'N'
1771 where event_id = p_id;
1772 l_enb_flag := 'N';
1773 end if;
1774 --
1775 -- Enable the new trigger as required
1776 enable_trigger(l_trigger,flag_to_boolean(l_enb_flag));
1777 --
1778 ELSE
1779 --
1780 -- Otherwise, drop it to make sure it definitely doesn't exist
1781 drop_trigger(l_trigger);
1782 p_ok := TRUE;
1783 hr_utility.trace(' Database Trigger '||p_name||' dropped with success.');
1784 --
1785 END IF;
1786 END IF;
1787
1788 hr_utility.set_location(' Leaving: '||l_proc,900);
1789 END generate_trigger;
1790 --
1791 -- +---------------------------------------------------------------------------+
1792 -- | NAME : compile_package_indirect |
1793 -- | SCOPE : PUBLIC |
1794 -- | DESCRIPTION: See header |
1795 -- +---------------------------------------------------------------------------+
1796 PROCEDURE compile_package_indirect(p_id IN NUMBER) IS
1797 --
1798 -- Cursor to get all we need to know about the event, since we only get
1799 -- passed the primary key of the support package
1800 CURSOR get_info(cp_id IN NUMBER) IS
1801 SELECT pte.event_id,
1802 pte.table_name,
1803 pts.header_code,
1804 pts.body_code
1805 FROM pay_trigger_events pte,
1806 pay_trigger_support pts
1807 WHERE pte.event_id = pts.event_id
1808 AND pts.support_id = cp_id;
1809 --
1810 l_id NUMBER;
1811 l_name VARCHAR2(30);
1812 l_pname VARCHAR2(30);
1813 l_head_ok BOOLEAN;
1814 l_body_ok BOOLEAN;
1815 l_head CLOB;
1816 l_body CLOB;
1817 BEGIN
1818 --
1819 -- Fetch the information we need
1820 OPEN get_info(p_id);
1821 FETCH get_info INTO l_id,l_name,l_head,l_body;
1822 CLOSE get_info;
1823 --
1824 -- Compile the package using the direct method, passing the info we found
1825 compile_package(
1826 l_id,
1827 l_name,
1828 lob_to_varchar2(l_head),
1829 lob_to_varchar2(l_body),
1830 l_pname,
1831 l_head_ok,
1832 l_body_ok
1833 );
1834 EXCEPTION
1835 WHEN OTHERS THEN
1836 -- Output a trrace message if we fail
1837 hr_utility.set_location('COMPILE_PACKAGE_INDIRECT',ABS(SQLCODE));
1838 END compile_package_indirect;
1839 --
1840 -- +---------------------------------------------------------------------------+
1841 -- | NAME : create_defaults |
1842 -- | SCOPE : PUBLIC |
1843 -- | DESCRIPTION: See header |
1844 -- +---------------------------------------------------------------------------+
1845 PROCEDURE create_defaults(p_id IN NUMBER) IS
1846 --
1847 -- Fetch to get some more information that we need
1848 CURSOR csr_name(cp_id IN NUMBER) IS
1849 SELECT pte.table_name,
1850 pte.triggering_action
1851 FROM pay_trigger_events pte
1852 WHERE pte.event_id = cp_id;
1853 --
1854 l_table VARCHAR2(30);
1855 l_action VARCHAR2(1);
1856 l_bus_id NUMBER;
1857 l_leg_id NUMBER;
1858 l_pay_id NUMBER;
1859 l_rc NUMBER;
1860 l_has_bus BOOLEAN;
1861 l_has_pay BOOLEAN;
1862 --
1863 BEGIN
1864 --
1865 -- Get the additional information
1866 OPEN csr_name(p_id);
1867 FETCH csr_name INTO l_table,l_action;
1868 CLOSE csr_name;
1869 --
1870 -- See if the table's got a business group ID and/or a payroll ID
1871 l_has_bus := table_has_business_group(l_table);
1872 l_has_pay := table_has_payroll(l_table);
1873 --
1874 -- Create the default declarations and initialisations that we need for
1875 -- a business group or payroll context trigger, then call the automapper
1876 -- to create the necessary parameter mappings for these initialisations
1877 default_declarations(l_has_bus,l_has_pay,p_id);
1878 default_initialisations(
1879 l_has_bus,
1880 l_has_pay,
1881 p_id,
1882 l_action,
1883 l_bus_id,
1884 l_leg_id,
1885 l_pay_id
1886 );
1887 default_parameters(p_id);
1888 --
1889 -- Insert some dummy code for the support package to give the user a hint
1890 -- then compile it (for what it's worth :-)
1891 l_rc := insert_support(
1892 p_id,
1893 '/* Add your support package header code here */',
1894 '/* Add your support package body code here */'
1895 );
1896 compile_package_indirect(l_rc);
1897 --
1898 END create_defaults;
1899 --
1900 -- +---------------------------------------------------------------------------+
1901 -- | NAME : add_declarations |
1902 -- | SCOPE : PRIVATE |
1903 -- | DESCRIPTION: Add the declaration section to the PL/SQL code |
1904 -- | PARAMETERS : p_id - The primary key value of the event that the code is |
1905 -- | being generated for |
1906 -- | p_sql - The current PL/SQL code that the declaration section |
1907 -- | will be added to |
1908 -- | RETURNS : The PL/SQL code with the declaration section added, via the |
1909 -- | IN/OUT parameter |
1910 -- | RAISES : None |
1911 -- +---------------------------------------------------------------------------+
1912 PROCEDURE add_declarations(p_id IN NUMBER,p_sql IN OUT NOCOPY VARCHAR2) IS
1913 --
1914 -- Get all the declarations defined for this event, decoding the type code
1915 CURSOR get_decls(cp_id IN NUMBER) IS
1916 SELECT 'l_'||variable_name variable_name,
1917 DECODE(data_type,
1918 'C','VARCHAR2',
1919 'D','DATE',
1920 'N','NUMBER'
1921 ) data_type,
1922 data_type data_type_code,
1923 variable_size
1924 FROM pay_trigger_declarations
1925 WHERE event_id = cp_id;
1926 --
1927 BEGIN
1928 --JFORD 15-SEP-03 Due to data inconsistencies the owness is now on the
1929 --calling code to be responsible for adding the DECLARE keyword
1930 --
1931 -- Process all the declarations
1932 FOR l_rec IN get_decls(p_id) LOOP
1933 --
1934 -- Add the variable declaration, padding it out with spaces to make the
1935 -- formatting look nice.
1936 p_sql := p_sql||' '||RPAD(l_rec.variable_name,30)||' '||l_rec.data_type;
1937 --
1938 IF l_rec.data_type_code = 'C' THEN
1939 p_sql := p_sql||'('||LTRIM(RTRIM(TO_CHAR(l_rec.variable_size)))||')';
1940 END IF;
1941 p_sql := p_sql||';'||g_eol;
1942 END LOOP;
1943 END add_declarations;
1944 --
1945 -- +---------------------------------------------------------------------------+
1946 -- | NAME : add_initialisations |
1947 -- | SCOPE : PRIVATE |
1948 -- | DESCRIPTION: Add the PL/SQL code that will initialise the declarations |
1949 -- | PARAMETERS : p_id - The primary key of the event that we are |
1950 -- | generating PL/SQL code for |
1951 -- | p_sql - The PL/SQL code that will have the initialisation |
1952 -- | code added to it |
1953 -- | p_table - The table that this trigger will get created on, |
1954 -- | needed to work out which bits of information we |
1955 -- | can send to the functional area checking routine |
1956 -- | RETURNS : The modified PL/SQL code with the initialisation section |
1957 -- | added via the IN OUT parameter and a flag indicating if any |
1958 -- | initialisations were written (not sure this is good practice)|
1959 -- | RAISES : None |
1960 -- +---------------------------------------------------------------------------+
1961 FUNCTION add_initialisations(
1962 p_id IN NUMBER,
1963 p_sql IN OUT NOCOPY VARCHAR2,
1964 p_table IN VARCHAR2
1965 ) RETURN BOOLEAN IS
1966 --
1967 -- Cursor to fetch all the initialisations that this event uses
1968 CURSOR get_inits(cp_id IN NUMBER) IS
1969 SELECT initialisation_id,
1970 plsql_code,
1971 process_type,
1972 process_order
1973 FROM pay_trigger_initialisations
1974 WHERE event_id = cp_id
1975 ORDER BY process_order;
1976 --
1977 -- Get the name of the variable that the return value of a function or
1978 -- assignment will be returned into
1979 CURSOR get_outs(cp_id IN NUMBER) IS
1980 SELECT parameter_name,
1981 value_name
1982 FROM pay_trigger_parameters
1983 WHERE parameter_type = 'R'
1984 AND usage_id = cp_id
1985 AND usage_type = 'I'
1986 ORDER BY parameter_id;
1987 --
1988 -- Get the mappings for the parameter list of a function or procedure
1989 CURSOR get_parms(cp_id IN NUMBER) IS
1990 SELECT parameter_name,
1991 value_name
1992 FROM pay_trigger_parameters
1993 WHERE parameter_type IN ('I','O')
1994 AND value_name IS NOT NULL
1995 AND usage_id = cp_id
1996 AND usage_type = 'I'
1997 ORDER BY parameter_id;
1998 --
1999 l_inits BOOLEAN := FALSE;
2000 l_fgt BOOLEAN := FALSE;
2001 --
2002 l_par VARCHAR2(30);
2003 l_val VARCHAR2(35);
2004 l_plsql VARCHAR2(32767);
2005 l_sel VARCHAR2(32767);
2006 l_int VARCHAR2(32767);
2007 l_cnt NUMBER;
2008 --
2009 l_bus BOOLEAN := NOT no_business_context(p_table,p_id);
2010 l_pay BOOLEAN := NOT no_payroll_context(p_table,p_id);
2011 --
2012 BEGIN
2013 --
2014 -- Add an explanatory comment
2015 p_sql := p_sql||' /* Initialising local variables */'||g_eol;
2016 --
2017 -- Process all the initialisations
2018 FOR l_rec IN get_inits(p_id) LOOP
2019 l_inits := TRUE;
2020 --
2021 -- If the initialisation is an assignment or a function then
2022 -- indent, add the return variable and the assignment operator
2023 IF l_rec.process_type IN ('A','F') THEN
2024 OPEN get_outs(l_rec.initialisation_id);
2025 FETCH get_outs INTO l_par,l_val;
2026 CLOSE get_outs;
2027 --
2028 p_sql := p_sql||' '||l_val||' := ';
2029 --
2030 -- Just indent if the initialisation is a procedure
2031 ELSIF l_rec.process_type IN ('P') THEN
2032 p_sql := p_sql||' ';
2033 END IF;
2034 --
2035 -- Fetch the actual code into a local variable we can modify
2036 l_plsql := l_rec.plsql_code;
2037 --
2038 -- If it's a SQL select or an assignment then we need to replace any
2039 -- $<TYPE>_<NAME>$ placeholders with the variables that they represent
2040 IF l_rec.process_type IN ('S','A') THEN
2041 replace_placeholders(l_plsql,l_rec.initialisation_id,NULL);
2042 END IF;
2043 --
2044 -- If it's an assignment, function or procedure then add the code
2045 -- from the initialisation definition
2046 IF l_rec.process_type IN ('A','F','P') THEN
2047 p_sql := p_sql||l_plsql;
2048 --
2049 -- If it's an assignment then that's all we need to do so add the
2050 -- end of line terminator
2051 IF l_rec.process_type = 'A' THEN
2052 p_sql := p_sql||';'||g_eol;
2053 END IF;
2054 END IF;
2055 --
2056 -- If it's a SQL select statement then...
2057 IF l_rec.process_type = 'S' THEN
2058 --
2059 -- Initialise the select and into list
2060 l_sel := '';
2061 l_int := '';
2062 --
2063 -- Loop round all the output mappings for this initialisation
2064 FOR l_inner IN get_outs(l_rec.initialisation_id) LOOP
2065 --
2066 -- If it's not the first mapping then add the correct padding
2067 IF get_outs%ROWCOUNT > 1 THEN
2068 l_sel := l_sel||', '||g_eol||' ';
2069 l_int := l_int||', '||g_eol||' ';
2070 END IF;
2071 --
2072 -- Add the parameter name to the select list and the return
2073 -- variable to the 'into' clause
2074 l_sel := l_sel||l_inner.parameter_name;
2075 l_int := l_int||l_inner.value_name;
2076 END LOOP;
2077 --
2078 -- Concatenate the select list, into list and the remainder of the
2079 -- select statement (the FROM clause onwards) to the PL/SQL code
2080 -- N.B. This code relies on the SQL select only bringing back 1
2081 -- record, otherwise it will fail at runtime, could be changed to
2082 -- generate a cursor, but that's quite a big change
2083 p_sql := p_sql||' SELECT '||l_sel||g_eol||' INTO '||l_int||g_eol;
2084 p_sql := p_sql||' '||
2085 SUBSTR(l_plsql,INSTR(UPPER(l_plsql),' FROM '))||'; '||g_eol;
2086 END IF;
2087 --
2088 -- If the initialisation's a function or a procedure then...
2089 IF l_rec.process_type IN ('F','P') THEN
2090 --
2091 -- Get the parameter mappings for the function or procedure
2092 l_cnt := 0;
2093 FOR l_inner IN get_parms(l_rec.initialisation_id) LOOP
2094 --
2095 -- Process the first/not first parameter in the list in
2096 -- different ways so that the code is formatted correctly
2097 IF get_parms%ROWCOUNT > 1 THEN
2098 p_sql := p_sql||','||g_eol;
2099 ELSE
2100 p_sql := p_sql||'('||g_eol;
2101 END IF;
2102 --
2103 -- Add the parameter to the list in full 'parameter => value' notation
2104 -- to allow the user to omit parameters from the call
2105 p_sql := p_sql||' '||
2106 RPAD(l_inner.parameter_name,30)||' => '||l_inner.value_name;
2107 l_cnt := l_cnt + 1;
2108 END LOOP;
2109 --
2110 -- Finish off the statement by closing the parameter list bracket
2111 IF l_cnt > 0 THEN
2112 p_sql := p_sql||g_eol||' ); '||g_eol;
2113 END IF;
2114 END IF;
2115 --
2116 -- Add a final comment line to delimit the initialisation block
2117 p_sql := p_sql||' --'||g_eol;
2118 END LOOP;
2119 --
2120 -- Always write the Functional Grouping of Triggers check
2121 -- This could be made more efficient by placing it just after the required
2122 -- variables (legislation, business group and payroll) have been initialised
2123 -- so that any other unnecessary inititialisations aren't carried out if the
2124 -- trigger isn't supposed to be firing
2125 IF NOT l_fgt THEN
2126 paywsfgt_pkg.write_fgt_check(p_id,p_sql,l_bus,l_pay);
2127 END IF;
2128 --
2129 -- Let the caller know if we wrote any initialisations or not
2130 RETURN l_inits;
2131 END add_initialisations;
2132 --
2133 -- +---------------------------------------------------------------------------+
2134 -- | NAME : write_parameters |
2135 -- | SCOPE : PRIVATE |
2136 -- | DESCRIPTION: Add the parameter list for a component procedure call to the |
2137 -- | specified PL/SQL text |
2138 -- | PARAMETERS : p_id - The primary key of the component being processed |
2139 -- | p_sql - The current PL/SQL code that the parameter list |
2140 -- | will be added to |
2141 -- | p_extra - Any extra text to add before the parameter list |
2142 -- | item, used to indent the line correctly |
2143 -- | RETURNS : The modified PL/SQL code via the OUT parameter |
2144 -- | RAISES : None |
2145 -- +---------------------------------------------------------------------------+
2146 PROCEDURE write_parameters(
2147 p_id IN NUMBER,
2148 p_sql IN OUT NOCOPY VARCHAR2,
2149 p_extra IN VARCHAR2
2150 ) IS
2151 --
2152 -- Cursor to fetch the parameter mappings for the specified module
2153 CURSOR get_parms(cp_id IN NUMBER) IS
2154 SELECT parameter_name,
2155 value_name
2156 FROM pay_trigger_parameters
2157 WHERE parameter_type IN ('I','O')
2158 AND value_name IS NOT NULL
2159 AND usage_id = cp_id
2160 AND usage_type = 'C'
2161 ORDER BY parameter_id;
2162 --
2163 l_num NUMBER := 0;
2164 --
2165 BEGIN
2166 --
2167 -- Process each parameter in turn
2168 FOR l_rec IN get_parms(p_id) LOOP
2169 l_num := l_num + 1;
2170 --
2171 -- Open the bracket for the first row, otherwise add a comma
2172 IF get_parms%ROWCOUNT > 1 THEN
2173 p_sql := p_sql||','||g_eol;
2174 ELSE
2175 p_sql := p_sql||'('||g_eol;
2176 END IF;
2177 --
2178 -- Add the parameter mapping in full 'parameter => value' notation
2179 p_sql := p_sql||' '||p_extra||
2180 RPAD(l_rec.parameter_name,30)||' => '||l_rec.value_name;
2181 END LOOP;
2182 --
2183 -- Add a closing bracket, only if one or more parameters was written
2184 IF l_num > 0 THEN
2185 p_sql := p_sql||g_eol||p_extra||')';
2186 END IF;
2187 END write_parameters;
2188 --
2189 -- +---------------------------------------------------------------------------+
2190 -- | NAME : add_components |
2191 -- | SCOPE : PRIVATE |
2192 -- | DESCRIPTION: Add the components (the procedure calls that actually do some|
2193 -- | work) to the PL/SQL code |
2194 -- | PARAMETERS : p_id - The primary key of the event we're processing |
2195 -- | p_sql - The current PL/SQL block that the component calls |
2196 -- | will get added to |
2197 -- | RETURNS : The modified PL/SQL block via the OUT parameter and a flag |
2198 -- | indicating if any components were written (not sure this is |
2199 -- | good programming practice) |
2200 -- | RAISES : None |
2201 -- +---------------------------------------------------------------------------+
2202 FUNCTION add_components(
2203 p_id IN NUMBER,
2204 p_sql IN OUT NOCOPY VARCHAR2
2205 ) RETURN BOOLEAN IS
2206 --
2207 -- Get global component calls, ones that always get called, i.e. they
2208 -- aren't specific to any legislation, business group or payroll
2209 CURSOR get_globals(cp_id IN NUMBER) IS
2210 SELECT component_id,
2211 module_name
2212 FROM pay_trigger_components
2213 WHERE legislation_code IS NULL
2214 AND business_group_id IS NULL
2215 AND payroll_id IS NULL
2216 AND enabled_flag = 'Y'
2217 AND event_id = cp_id;
2218 --
2219 -- Legislation specific components, only get called if the legislation
2220 -- code of the current record matches the component definition
2221 CURSOR get_legs(cp_id IN NUMBER) IS
2222 SELECT component_id,
2223 legislation_code,
2224 module_name
2225 FROM pay_trigger_components
2226 WHERE legislation_code IS NOT NULL
2227 AND payroll_id IS NULL
2228 AND business_group_id IS NULL
2229 AND enabled_flag = 'Y'
2230 AND event_id = cp_id
2231 ORDER BY legislation_code;
2232 --
2233 -- Business group specific components, only get called if the business
2234 -- group of the current record matches the component definition
2235 CURSOR get_buss(cp_id IN NUMBER) IS
2236 SELECT component_id,
2237 business_group_id,
2238 module_name
2239 FROM pay_trigger_components
2240 WHERE legislation_code IS NULL
2241 AND payroll_id IS NULL
2242 AND business_group_id IS NOT NULL
2243 AND enabled_flag = 'Y'
2244 AND event_id = cp_id
2245 ORDER BY business_group_id;
2246 --
2247 -- Payroll specific components, only get called if the payroll
2248 -- id of the current record matches that of the component definition
2249 CURSOR get_pays(cp_id IN NUMBER) IS
2250 SELECT component_id,
2251 payroll_id,
2252 module_name
2253 FROM pay_trigger_components
2254 WHERE legislation_code IS NULL
2255 AND business_group_id IS NULL
2256 AND payroll_id IS NOT NULL
2257 AND enabled_flag = 'Y'
2258 AND event_id = cp_id
2259 ORDER BY payroll_id;
2260 --
2261 l_comps BOOLEAN := FALSE;
2262 l_ifs NUMBER := 0;
2263 --
2264 -- Remember the last code we wrote so we know when to end/start IF blocks
2265 l_oldleg VARCHAR2(30) := '$NO_LEGISLATION_CODE$';
2266 l_oldbus NUMBER := -65536;
2267 l_oldpay NUMBER := -65536;
2268 --
2269 BEGIN
2270 --
2271 -- Fetch and write details of all the global components
2272 p_sql := p_sql||' /* Global component calls */'||g_eol;
2273 FOR l_rec IN get_globals(p_id) LOOP
2274 l_comps := TRUE;
2275 --
2276 p_sql := p_sql||' '||l_rec.module_name;
2277 write_parameters(l_rec.component_id,p_sql,' ');
2278 p_sql := p_sql||';'||g_eol;
2279 END LOOP;
2280 p_sql := p_sql||' --'||g_eol;
2281 --
2282 -- Fetch and write details of legislation specific components
2283 p_sql := p_sql||' /* Legislation specific component calls */'||g_eol;
2284 l_ifs := 0;
2285 FOR l_rec IN get_legs(p_id) LOOP
2286 l_comps := TRUE;
2287 --
2288 -- If the legislation code has changed then add a new IF clause
2289 IF l_rec.legislation_code <> l_oldleg THEN
2290 IF get_legs%ROWCOUNT > 1 THEN
2291 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2292 l_ifs := l_ifs - 1;
2293 END IF;
2294 p_sql := p_sql||' IF l_legislation_code = '''||
2295 l_rec.legislation_code||''' THEN'||g_eol;
2296 l_ifs := l_ifs + 1;
2297 --
2298 l_oldleg := l_rec.legislation_code;
2299 END IF;
2300 --
2301 -- Write the component call information
2302 p_sql := p_sql||' '||l_rec.module_name;
2303 write_parameters(l_rec.component_id,p_sql,' ');
2304 p_sql := p_sql||';'||g_eol;
2305 END LOOP;
2306 --
2307 -- Close the final IF clause (if we wrote any)
2308 IF l_ifs > 0 THEN
2309 p_sql := p_sql||' END IF; '||g_eol;
2310 END IF;
2311 p_sql := p_sql||' --'||g_eol;
2312 --
2313 -- Fetch and write details of legislation specific components
2314 p_sql := p_sql||' /* Business group specific component calls */'||g_eol;
2315 l_ifs := 0;
2316 FOR l_rec IN get_buss(p_id) LOOP
2317 l_comps := TRUE;
2318 --
2319 -- Write a new IF clause if the business group changed
2320 IF l_rec.business_group_id <> l_oldbus THEN
2321 IF get_buss%ROWCOUNT > 1 THEN
2322 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2323 l_ifs := l_ifs - 1;
2324 END IF;
2325 p_sql := p_sql||' IF l_business_group_id = '||
2326 TO_CHAR(l_rec.business_group_id)||' THEN'||g_eol;
2327 l_ifs := l_ifs + 1;
2328 --
2329 l_oldbus := l_rec.business_group_id;
2330 END IF;
2331 --
2332 -- Write the component call details
2333 p_sql := p_sql||' '||l_rec.module_name;
2334 write_parameters(l_rec.component_id,p_sql,' ');
2335 p_sql := p_sql||';'||g_eol;
2336 END LOOP;
2337 --
2338 -- Close the final IF statement if needed
2339 IF l_ifs > 0 THEN
2340 p_sql := p_sql||' END IF; '||g_eol;
2341 END IF;
2342 p_sql := p_sql||' --'||g_eol;
2343 --
2344 -- Write payroll specific component calls
2345 p_sql := p_sql||' /* Payroll specific component calls */'||g_eol;
2346 l_ifs := 0;
2347 FOR l_rec IN get_pays(p_id) LOOP
2348 l_comps := TRUE;
2349 --
2350 -- Add a new IF clause if the payroll ID changes
2351 IF l_rec.payroll_id <> l_oldpay THEN
2352 IF get_pays%ROWCOUNT > 1 THEN
2353 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2354 l_ifs := l_ifs - 1;
2355 END IF;
2356 p_sql := p_sql||' IF l_payroll_id = '||
2357 TO_CHAR(l_rec.payroll_id)||' THEN'||g_eol;
2358 l_ifs := l_ifs + 1;
2359 --
2360 l_oldpay := l_rec.payroll_id;
2361 END IF;
2362 --
2363 -- Write the component module call
2364 p_sql := p_sql||' '||l_rec.module_name;
2365 write_parameters(l_rec.component_id,p_sql,' ');
2366 p_sql := p_sql||';'||g_eol;
2367 END LOOP;
2368 --
2369 -- Close the final IF if required
2370 IF l_ifs > 0 THEN
2371 p_sql := p_sql||' END IF; '||g_eol;
2372 END IF;
2373 p_sql := p_sql||' --'||g_eol;
2374 --
2375 -- Let the caller know whether or not we wrote and components
2376 RETURN l_comps;
2377 END add_components;
2378 -- +---------------------------------------------------------------------------+
2379 -- | NAME : generate_code |
2380 -- | SCOPE : PUBLIC |
2381 -- | DESCRIPTION: See header |
2382 -- +---------------------------------------------------------------------------+
2383 PROCEDURE generate_code(p_id IN NUMBER,p_sql IN OUT NOCOPY VARCHAR2) IS
2384 --
2385 l_inits BOOLEAN;
2386 l_comps BOOLEAN;
2387 l_table pay_trigger_events.table_name%TYPE;
2388 l_name pay_trigger_events.short_name%TYPE;
2389 l_type pay_trigger_events.triggering_action%TYPE;
2390 l_desc VARCHAR2(80);
2391 l_info pay_trigger_events.description%TYPE;
2392
2393 --
2394 -- Get some extra information about the event we're generating code for
2395 CURSOR csr_name(cp_id IN NUMBER) IS
2396 SELECT pte.table_name,
2397 pte.short_name,
2398 pte.triggering_action,
2399 DECODE(pte.triggering_action,
2400 'I','Insert',
2401 'U','Update',
2402 'D','Delete'
2403 ),
2404 pte.description
2405 FROM pay_trigger_events pte
2406 WHERE pte.event_id = cp_id;
2407 --
2408 BEGIN
2409 --
2410 -- Fetch the additional information we need before we can generate
2411 OPEN csr_name(p_id);
2412 FETCH csr_name INTO l_table,l_name,l_type,l_desc,l_info;
2413 CLOSE csr_name;
2414 --
2415 -- Initialise the statement PL/SQL code
2416 p_sql := '';
2417 --
2418
2419 -- Add DECLARE before calling add_declarations
2420 p_sql := p_sql||'DECLARE '||g_eol;
2421 p_sql := p_sql||' /* Local variable declarations */'||g_eol;
2422
2423 -- Add the seeded declaration section
2424 add_declarations(p_id,p_sql);
2425
2426 -- Add any hard-coded declarations
2427 p_sql := p_sql||' l_mode varchar2(80);'||g_eos;
2428 --
2429 -- Add an initial comment section to the trigger code
2430 p_sql := p_sql||'BEGIN'||g_eol;
2431 p_sql := p_sql||'/*'||g_eol;
2432 p_sql := p_sql||' ================================================'||g_eol;
2433 p_sql := p_sql||' This is a dynamically generated database trigger'||g_eol;
2434 p_sql := p_sql||' ================================================'||g_eol;
2435 p_sql := p_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
2436 p_sql := p_sql||' ------------------------------------------------'||g_eol;
2437 p_sql := p_sql||' Table: '||l_table||g_eol;
2438 p_sql := p_sql||' Action: '||l_desc||g_eol;
2439 p_sql := p_sql||' Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
2440 p_sql := p_sql||' Name: '||l_name||g_eol;
2441 p_sql := p_sql||' Info.: '||l_info||g_eol;
2442 p_sql := p_sql||' ================================================'||g_eol;
2443 p_sql := p_sql||'*/'||g_eol||'--'||g_eol;
2444 --
2445
2446 --Add our system to let future processes know whether this is existing or new dyt
2447 p_sql := p_sql||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
2448 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := pay_dyn_triggers.g_dbms_dyt;'||g_eol;
2449
2450 -- Add the data migrator check - Bug 1885557
2451 p_sql := p_sql||'IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
2452 p_sql := p_sql||' RETURN;'||g_eol;
2453 p_sql := p_sql||'END IF;'||g_eol;
2454 --
2455 -- Add the initialisation and component PL/SQL code
2456 l_inits := add_initialisations(p_id,p_sql,l_table);
2457 l_comps := add_components(p_id,p_sql);
2458 --
2459 -- If we didn't add any initialisations or components then add a NULL
2460 -- operation to prevent compilation errors when triggers without any
2461 -- default initialisations are first created
2462 IF NOT l_inits AND NOT l_comps THEN
2463 p_sql := p_sql||' NULL;'||g_eol;
2464 END IF;
2465
2466
2467 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2468
2469
2470 --
2471 -- Add a default exception block to catch all errors and write the
2472 -- trigger name and error text to the standard Oracle Apps error logging
2473 -- mechanism
2474 p_sql := p_sql||'EXCEPTION'||g_eol;
2475 p_sql := p_sql||' WHEN OTHERS THEN'||g_eol;
2476 p_sql := p_sql||' hr_utility.set_location('''||
2477 get_trigger_name(p_id,l_table,l_type)||''',ABS(SQLCODE));'||g_eol;
2478 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2479 p_sql := p_sql||' RAISE;'||g_eol;
2480 p_sql := p_sql||' --'||g_eol;
2481 p_sql := p_sql||'END;'||g_eol;
2482 END generate_code;
2483 --
2484 --
2485 -- +---------------------------------------------------------------------------+
2486 -- | NAME : delete_event_children |
2487 -- | SCOPE : PUBLIC |
2488 -- | DESCRIPTION: See header |
2489 -- +---------------------------------------------------------------------------+
2490 PROCEDURE delete_event_children(p_id IN NUMBER) IS
2491 --
2492 -- Get some more information we need before we can delete the event
2493 CURSOR get_trigger(cp_id IN NUMBER) IS
2494 SELECT pte.table_name,
2495 pte.triggering_action
2496 FROM pay_trigger_events pte
2497 WHERE pte.event_id = cp_id;
2498 --
2499 -- Get all the initialisations that this event uses
2500 CURSOR get_inits(cp_id IN NUMBER) IS
2501 SELECT initialisation_id
2502 FROM pay_trigger_initialisations
2503 WHERE event_id = cp_id;
2504 --
2505 -- Get all the components that this event uses
2506 CURSOR get_comps(cp_id IN NUMBER) IS
2507 SELECT component_id
2508 FROM pay_trigger_components
2509 WHERE event_id = cp_id;
2510 --
2511 l_tname VARCHAR2(30);
2512 l_action VARCHAR2(1);
2513 l_name VARCHAR2(30);
2514 --
2515 BEGIN
2516 --
2517 -- Get the table name and triggering action of the event we're deleting
2518 OPEN get_trigger(p_id);
2519 FETCH get_trigger INTO l_tname,l_action;
2520 CLOSE get_trigger;
2521 --
2522 -- Work out the trigger name according to the standard format
2523 l_name := get_trigger_name(p_id,l_tname,l_action);
2524 --
2525 -- Drop the trigger (uses AOL routines)
2526 drop_trigger(l_name);
2527 --
2528 -- Fetch all the initialisations, delete their children and finally
2529 -- the initialisations themselves
2530 FOR l_rec IN get_inits(p_id) LOOP
2531 delete_initialisation_children(l_rec.initialisation_id);
2532 END LOOP;
2533 DELETE
2534 FROM pay_trigger_initialisations
2535 WHERE event_id = p_id;
2536 --
2537 -- Fetch all the components, delete their children and finally
2538 -- the components themselves
2539 FOR l_rec IN get_comps(p_id) LOOP
2540 delete_component_children(l_rec.component_id);
2541 END LOOP;
2542 DELETE
2543 FROM pay_trigger_components
2544 WHERE event_id = p_id;
2545 --
2546 -- Delete the local variable declarations that the trigger uses
2547 DELETE
2548 FROM pay_trigger_declarations
2549 WHERE event_id = p_id;
2550 --
2551 -- Drop the support package and delete it's definition
2552 drop_package(l_tname,get_package_name(p_id,l_tname));
2553 DELETE
2554 FROM pay_trigger_support
2555 WHERE event_id = p_id;
2556 --
2557 -- Don't delete the actual event, the caller (e.g. Forms) must do this
2558 END delete_event_children;
2559 --
2560 -- +---------------------------------------------------------------------------+
2561 -- | NAME : delete_initialisation_children |
2562 -- | SCOPE : PUBLIC |
2563 -- | DESCRIPTION: See header |
2564 -- +---------------------------------------------------------------------------+
2565 PROCEDURE delete_initialisation_children(p_id IN NUMBER) IS
2566 BEGIN
2567 --
2568 -- Delete all the parameters that the requested initialisation uses
2569 DELETE
2570 FROM pay_trigger_parameters
2571 WHERE usage_type = 'I'
2572 AND usage_id = p_id;
2573 --
2574 -- The caller must delete the initialisation itself
2575 END delete_initialisation_children;
2576 --
2577 -- +---------------------------------------------------------------------------+
2578 -- | NAME : delete_component_children |
2579 -- | SCOPE : PUBLIC |
2580 -- | DESCRIPTION: See header |
2581 -- +---------------------------------------------------------------------------+
2582 PROCEDURE delete_component_children(p_id IN NUMBER) IS
2583 BEGIN
2584 --
2585 -- Delete all the parameters that the requested component uses
2586 DELETE
2587 FROM pay_trigger_parameters
2588 WHERE usage_type = 'C'
2589 AND usage_id = p_id;
2590 --
2591 -- The caller must delete the component itself
2592 END delete_component_children;
2593
2594 -- +---------------------------------------------------------------------------+
2595 -- | NAME : delete_parameters_directly |
2596 -- | SCOPE : PUBLIC |
2597 -- | DESCRIPTION: See header |
2598 -- +---------------------------------------------------------------------------+
2599 PROCEDURE delete_parameters_directly(p_param_id IN NUMBER) IS
2600 BEGIN
2601 --
2602 -- Delete a parameter directly, used by table event updates form
2603 DELETE
2604 FROM pay_trigger_parameters
2605 WHERE parameter_id = p_param_id;
2606 --
2607 -- The caller must delete the initialisation itself
2608 END delete_parameters_directly;
2609
2610 --
2611 --
2612 -- +---------------------------------------------------------------------------+
2613 -- | NAME : get_dyt_pkg_params_tbl |
2614 -- | SCOPE : PRIVATE |
2615 -- | DESCRIPTION: See header |
2616 -- +---------------------------------------------------------------------------+
2617 PROCEDURE get_dyt_pkg_params_tbl(p_tab_id IN NUMBER
2618 ,p_tab_name IN VARCHAR2
2619 ,p_params IN OUT NOCOPY g_params_tab_type) IS
2620 --
2621 cursor csr_params (cp_tab_id in number)
2622 is
2623 select parameter_name, usage_type, value_name
2624 from pay_trigger_parameters
2625 where usage_type in ('PI','PU','PD')
2626 and usage_id = cp_tab_id
2627 order by parameter_name;
2628
2629 cursor csr_col_type (cp_tab_name varchar2, cp_col_name varchar2)
2630 is
2631 select data_type
2632 from all_tab_columns
2633 where table_name = cp_tab_name
2634 and column_name like cp_col_name
2635 and owner = g_pay_schema
2636 and rownum = 1; --Assuming if the params been truc'd to 30 then its the only col
2637 --on the base table with these first 30chars
2638
2639 -- nb ORDERED hint for performance fix 3110997
2640 cursor csr_type_from_pkg (cp_tab_name varchar2, cp_param_name varchar2)
2641 is
2642 SELECT /*+ ORDERED */ a.pls_type
2643 FROM (select /*+ NO_MERGE */
2644 DISTINCT hook_package
2645 from hr_api_hooks h,
2646 hr_api_modules m
2647 where m.api_module_id = h.api_module_id
2648 and m.module_name = cp_tab_name) V,
2649 USER_OBJECTS B,
2650 SYS.ARGUMENT$ A
2651 WHERE A.OBJ# = B.OBJECT_ID
2652 AND B.OBJECT_NAME = V.hook_package
2653 AND A.LEVEL# = 0
2654 AND A.argument = cp_param_name
2655 AND B.object_type = 'PACKAGE'
2656 AND rownum = 1;
2657 -- Commented for bug fix 7006158.
2658 -- SELECT /*+ ORDERED */ a.pls_type
2659 -- FROM USER_OBJECTS B,
2660 -- SYS.ARGUMENT$ A
2661 -- WHERE A.OBJ# = B.OBJECT_ID
2662 -- AND B.OBJECT_NAME in (select hook_package
2663 -- from hr_api_hooks h, hr_api_modules m
2664 -- where m.api_module_id = h.api_module_id
2665 -- and m.module_name = cp_tab_name)
2666 -- AND A.LEVEL# = 0
2667 -- AND A.argument = cp_param_name
2668 -- AND B.object_type = 'PACKAGE'
2669 -- AND rownum = 1;
2670
2671 i number := 0;
2672
2673 BEGIN
2674 --
2675 -- Fetch Schema name if required
2676 --
2677 g_pay_schema := get_table_owner(p_tab_name);
2678 --
2679 --get all mappings of dyn-trigger-package-procedure params, eg all names
2680 -- of dyn-trigger local vars into a record with info on I,U,D --given table id
2681 FOR param_rec in csr_params(p_tab_id) LOOP
2682 p_params(i).local_form := param_rec.parameter_name;
2683 p_params(i).usage_type := param_rec.usage_type;
2684 p_params(i).value_name := param_rec.value_name;
2685 IF SUBSTR(p_params(i).local_form,1,2) = 'l_' THEN
2686 p_params(i).param_form := 'p_l_'||SUBSTR(p_params(i).local_form,3);
2687 ELSIF SUBSTR(p_params(i).local_form,1,5) = ':old.' THEN
2688 p_params(i).param_form := 'p_old_'||SUBSTR(p_params(i).local_form,6);
2689 ELSIF SUBSTR(p_params(i).local_form,1,5) = ':new.' THEN
2690 p_params(i).param_form := 'p_new_'||SUBSTR(p_params(i).local_form,6);
2691 ELSE
2692 p_params(i).param_form := NULL;
2693 END IF;
2694
2695 --Get col type using the text after the '.' in local form(plus % for like in csr)
2696 --
2697 open csr_col_type(p_tab_name,upper(substr(p_params(i).local_form,instr(p_params(i).local_form,'.')+1))||'%');
2698 fetch csr_col_type into p_params(i).data_type;
2699 close csr_col_type;
2700
2701 -- if we didnt get a data_type (eg name different than base col.)
2702 -- then have to resort to the inefficient approach,
2703 -- getting the type from the user hook pkg definition, on which these parameter
2704 -- mappings are based. (Because essentially all these dynamic procedures do
2705 -- is act as the call package from the hook pkg.)
2706 if (p_params(i).data_type is null) then
2707 open csr_type_from_pkg(p_tab_name,
2708 upper('p_'||SUBSTR(p_params(i).local_form,6)));
2709 fetch csr_type_from_pkg into p_params(i).data_type;
2710 close csr_type_from_pkg;
2711 end if;
2712 --If still null try one last hack in case we're working on the ALL version
2713 if (p_params(i).data_type is null) then
2714 open csr_type_from_pkg(replace(p_tab_name, '_ALL_','_'),
2715 upper('p_'||SUBSTR(p_params(i).local_form,6)));
2716 fetch csr_type_from_pkg into p_params(i).data_type;
2717 close csr_type_from_pkg;
2718 end if;
2719 --If still null try one last hack, cos know all should have this
2720 --and if no hook we might miss it (eg pay_element_entry_val)
2721 if (p_params(i).data_type is null
2722 and p_params(i).value_name = 'P_DATETRACK_MODE') then
2723 p_params(i).data_type := 'VARCHAR2';
2724 end if;
2725
2726 -- if still null then maybe raise a better error?
2727
2728
2729 --All param versions must be less than 30 chars as used in dbms triggers
2730 p_params(i).param_form := substr(p_params(i).param_form,0,30);
2731 i := i+1;
2732 END LOOP;
2733 END get_dyt_pkg_params_tbl;
2734
2735 --
2736 -- +---------------------------------------------------------------------------+
2737 -- | NAME : get_dyt_rhi_params |
2738 -- | SCOPE : PRIVATE |
2739 -- | DESCRIPTION: See header |
2740 -- +---------------------------------------------------------------------------+
2741 PROCEDURE get_dyt_rhi_params(p_tab_id IN NUMBER
2742 ,p_tab_name IN VARCHAR2
2743 ,p_params IN OUT NOCOPY g_params_tab_type) IS
2744
2745 -- Local variables to catch the values returned from
2746 -- hr_general.describe_procedure
2747 --
2748 l_overload dbms_describe.number_table;
2749 l_position dbms_describe.number_table;
2750 l_level dbms_describe.number_table;
2751 l_argument_name dbms_describe.varchar2_table;
2752 l_datatype dbms_describe.number_table;
2753 l_default_value dbms_describe.number_table;
2754 l_in_out dbms_describe.number_table;
2755 l_length dbms_describe.number_table;
2756 l_precision dbms_describe.number_table;
2757 l_scale dbms_describe.number_table;
2758 l_radix dbms_describe.number_table;
2759 l_spare dbms_describe.number_table;
2760
2761 l_datatype_str varchar2(20); -- String equivalent of
2762 -- l_datatype number.
2763 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
2764 --
2765 c_dtype_undefined constant number default 0;
2766 c_dtype_varchar2 constant number default 1;
2767 c_dtype_number constant number default 2;
2768 c_dtype_long constant number default 8;
2769 c_dtype_date constant number default 12;
2770 c_dtype_boolean constant number default 252;
2771
2772 cursor csr_hooks (cp_tab_name varchar2)
2773 is
2774 select hook_package pkg, hook_procedure proc
2775 ,decode(hook_procedure, 'AFTER_INSERT', 'PI',
2776 'AFTER_UPDATE', 'PU',
2777 'AFTER_DELETE', 'PD') usage_type
2778 from hr_api_hooks h, hr_api_modules m
2779 where m.api_module_id = h.api_module_id
2780 and m.module_name = cp_tab_name;
2781 --
2782
2783 i number := 1;
2784 j number := 0; --p_params, our local store, starts from 0
2785
2786 l_prefix varchar2(15); --Local Form prefix
2787 l_o varchar2(15) := 'p_old_'; --Old Style Local Form prefix
2788 l_n varchar2(15) := 'p_new_'; --Old Style Local Form prefix
2789 l_value_name varchar2(80);
2790
2791 l_proc varchar2(80) := 'get_dyt_rhi_params';
2792
2793 BEGIN
2794
2795 -- Want an exhaustive list of params, whatever is available to hook
2796 --should be passed
2797 --In terms of this internal table structure, want
2798 --NB names based on form param screen, NOT what makes logical sense!
2799 -- param_form - ':old.column_name'
2800 -- local_form - 'p_column_name_o'
2801 -- value_name - 'P_old_column_name'
2802 << HOOK_MODULES >>
2803 FOR hook_rec in csr_hooks(p_tab_name) LOOP
2804
2805 hr_general.describe_procedure
2806 (object_name => hook_rec.pkg || '.' || hook_rec.proc
2807 ,reserved1 => null
2808 ,reserved2 => null
2809 ,overload => l_overload
2810 ,position => l_position
2811 ,level => l_level
2812 ,argument_name => l_argument_name
2813 ,datatype => l_datatype
2814 ,default_value => l_default_value
2815 ,in_out => l_in_out
2816 ,length => l_length
2817 ,precision => l_precision
2818 ,scale => l_scale
2819 ,radix => l_radix
2820 ,spare => l_spare
2821 );
2822
2823
2824 << ONE_PROC_PARAM_LOOP >>
2825 FOR i in 1..l_position.COUNT loop
2826 --hr_utility.trace(' Found parameter '||l_argument_name(i)||' type '||l_ --
2827 if l_datatype(i) <> c_dtype_varchar2 and
2828 l_datatype(i) <> c_dtype_number and
2829 l_datatype(i) <> c_dtype_date and
2830 l_datatype(i) <> c_dtype_boolean and
2831 l_datatype(i) <> c_dtype_long then
2832 -- Error: In a hook package procedure all the parameter datatypes
2833 -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2834 -- module will not execute until this problem has been resolved.
2835 hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2836 hr_utility.set_location(l_proc, 80);
2837 else
2838 -- Set the datatype string with the corresponding word value
2839 if l_datatype(i) = c_dtype_varchar2 then
2840 l_datatype_str := 'VARCHAR2';
2841 elsif l_datatype(i) = c_dtype_number then
2842 l_datatype_str := 'NUMBER';
2843 elsif l_datatype(i) = c_dtype_date then
2844 l_datatype_str := 'DATE';
2845 elsif l_datatype(i) = c_dtype_boolean then
2846 l_datatype_str := 'BOOLEAN';
2847 else
2848 l_datatype_str := 'LONG';
2849 end if;
2850
2851 p_params(j).local_form := 'NOT CALCULATED';
2852 p_params(j).param_form := 'NOT CALCULATED';
2853 p_params(j).value_name := substr(l_argument_name(i),0,30);
2854 p_params(j).usage_type := hook_rec.usage_type;
2855 p_params(j).data_type := l_datatype_str;
2856 j := j+1;
2857 end if;
2858
2859 END LOOP ONE_PROC_PARAM_LOOP;
2860
2861 END LOOP HOOK_MODULES;
2862 --Debug Output all the paramaters in my table form
2863 --FOR j in 0..(p_params.COUNT - 1) loop
2864 --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2865 --end loop;
2866
2867 hr_utility.trace('Total RHI params '||p_params.count);
2868
2869 -- If we didnt get any hook params then there might not be a hook pkg!
2870 -- So just base the params on what we actually need, i.e. the param mappings
2871 if ( p_params.count = 0 ) then
2872 hr_utility.trace('No hook params => build params from what is required from components');
2873
2874 get_dyt_pkg_params_tbl(p_tab_id,p_tab_name,p_params);
2875 --On top of the ones we need in dyt call, we also know we need p_datetrack
2876 --in both after_delete and after_update.
2877 i := p_params.count;
2878 p_params(i).local_form := 'NOT CALCULATED';
2879 p_params(i).param_form := 'NOT CALCULATED';
2880 p_params(i).value_name := 'P_DATETRACK_MODE';
2881 p_params(i).usage_type := 'PU';
2882 p_params(i).data_type := 'VARCHAR2';
2883 p_params(i).local_form := 'NOT CALCULATED';
2884 p_params(i+1).param_form := 'NOT CALCULATED';
2885 p_params(i+1).value_name := 'P_DATETRACK_MODE';
2886 p_params(i+1).usage_type := 'PD';
2887 p_params(i+1).data_type := 'VARCHAR2';
2888
2889 hr_utility.trace('Total RHI params '||p_params.count);
2890 -- Example of structure of p_params from get_dyt_pkg_params_tbl
2891 --local_form - :new.EFFECTIVE_END_DATE
2892 --param_form - p_new_EFFECTIVE_END_DATE
2893 --value_name - P_EFFECTIVE_END_DATE
2894
2895 end if;
2896
2897 END get_dyt_rhi_params;
2898
2899 --
2900 -- +---------------------------------------------------------------------------+
2901 -- | NAME : get_dyt_pkg_version_of_code |
2902 -- | SCOPE : PRIVATE |
2903 -- | DESCRIPTION: This procedure takes an extract of dynamically generated code|
2904 -- | relevant for dbms triggers and turns it in to code useful for|
2905 -- | code that resides in packages. Main use to convert code |
2906 -- | generated by |
2907 -- | + add_declarations |
2908 -- | + add_initialisations |
2909 -- | + add_components |
2910 -- | in to code being built as part of + Gen_dyt_pkg_full_code |
2911 -- +---------------------------------------------------------------------------+
2912 PROCEDURE get_dyt_pkg_version_of_code(p_sql IN OUT NOCOPY varchar2,
2913 p_tab_name IN varchar2,
2914 p_usage_type IN varchar2) IS
2915 --
2916 l_pos number;
2917 l_col_name varchar2(32767);
2918 l_new_col_name varchar2(2000);
2919
2920 cursor csr_missing_params (cp_table_name in varchar2, cp_type in varchar2) is
2921 /* Component calls use this , ignore locals cos these will always be made*/
2922 SELECT upper(value_name) name
2923 FROM pay_trigger_parameters
2924 WHERE parameter_type IN ('I','O')
2925 AND value_name IS NOT NULL
2926 AND usage_id in (select component_id
2927 from pay_trigger_events te, pay_trigger_components tc
2928 where te.event_id = tc.event_id
2929 and triggering_action = cp_type
2930 and te.table_name = cp_table_name)
2931 AND usage_type = 'C'
2932 AND value_name like ':%'
2933 MINUS
2934 /* aru params use this */
2935 SELECT upper(parameter_name)
2936 FROM pay_trigger_parameters
2937 WHERE usage_type = 'P'||cp_type
2938 AND usage_id = (select dated_table_id
2939 from pay_dated_tables
2940 where table_name = cp_table_name);
2941
2942 l_missing varchar2(60);
2943 l_missing_rep varchar2(60);
2944 l_extract varchar2(32767);
2945 l_extract_new varchar2(32767);
2946 l_pos_next number;
2947 i number;
2948
2949 BEGIN
2950 --Make life easier by getting sql in caps
2951 p_sql := upper(p_sql);
2952
2953 -- >>> 1. Do some funky stuff to overcome major headaches.
2954 --
2955 --OLD SKOOL
2956 -- DB TRIG -> CC PKG
2957 --NEW SKOOL
2958 -- USER_HOOK_PKG -> USER_CALL_PKG(DYT_PKG) -> CC_PKG
2959 --
2960 -- The original dyt's as db triggers had access to ALL values on the table
2961 -- old and new, no worries, so CC pkg code could be built expecting ALL values.
2962 -- However, now the CC pkg is called by a dyt_pkg which is referred to as an
2963 -- user hook call pkg, as is called by the user hook pkg. Trouble is what
2964 -- parameters the user hook package has access to is in the lap of the gods
2965 -- aka the developers who build them. It is acceptable for example to not pass
2966 -- the new version of values if they are non-updateable, i.e. they are defo the same
2967 -- so why bother passing?
2968 -- BUT, out dyt_pkg's need to call the CC pkg and this is still expecting ALL values
2969 -- old and new for each column.
2970 -- Therefore we do a crafty trick/hack, getting those values that are referenced in
2971 -- component code and are not passed in by the hook. We make the assumption,
2972 -- fairly solid, that we can safely replace the missing value with the same
2973 -- existing value. EG If business_group_id is non-updateable (often) then the value
2974 -- p_business_group_id wont exist in the scope of our hook pkg and thus not be
2975 -- in our call-package, however p_business_group_id_o will be passed in.
2976 -- So we replace all instances of :new.business_group_id (because everything
2977 -- is still in old-skool db style) and replace it with :old.business_group_id.!
2978 -- et voila, the CC pkg
2979 -- gets all the values, just sometimes the same value for old and new.
2980
2981 --missing_param defo upper
2982 for missing_param in csr_missing_params(p_tab_name,p_usage_type) loop
2983 --Most of the time missing new value, cos non-updateable
2984 --eg turn :new.business_group_id to :old.business_group_id
2985 l_missing := missing_param.name;
2986 if (substr(l_missing,1,4) = ':NEW' ) then
2987 l_missing_rep := replace(missing_param.name,'NEW','OLD');
2988
2989 --But as developer has control, may be some the other way eg one off dates
2990 --and id's where arbitrary which is passed, and not changed
2991 elsif (substr(l_missing,1,4) = ':OLD' ) then
2992 l_missing_rep := replace(missing_param.name,'OLD','NEW');
2993 else
2994 l_missing_rep := l_missing||'xxx'; --no change so will fall over
2995 end if;
2996 --hr_utility.trace('Missing param: '||l_missing||' replace with: '||l_missing_rep);
2997 p_sql := replace(p_sql,l_missing,l_missing_rep);
2998
2999 end loop;
3000
3001 -- >>> 2. Turn any references to :old. style notation to param style
3002 -- EG. :old.cost_allocation_keyflex_id => p_old_cost_allocation_keyflex
3003 --
3004 -- Find first occurences of :old.
3005 l_pos := instr(p_sql,':OLD.');
3006 -- Loop through all occurences
3007 while (l_pos > 0) loop
3008
3009 --Get extract from this instance of :new to next :new instance
3010 --
3011 l_pos_next := instr(p_sql,':OLD.',l_pos+1);
3012 --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3013 if (l_pos_next <> 0 ) then
3014 l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3015 else
3016 l_extract := substr(p_sql,l_pos);
3017 l_pos_next := length(p_sql)+1;
3018 end if;
3019
3020 l_col_name := translate(l_extract,' ),|;'||g_eol,'*****');
3021 l_col_name := substr(l_col_name,1,instr(l_col_name,'*')-1);
3022
3023 -- Amend the prefix and make sure we're not over the 30 char limit
3024 l_new_col_name := substr(replace(l_col_name,':OLD.','P_OLD_'),1,30);
3025
3026 --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3027
3028 l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3029 p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3030
3031 --Find next occurence ,will be first as just done previous first,
3032 --but go from l_pos as quicker
3033 l_pos := instr(p_sql,':OLD.',l_pos);
3034
3035 end loop;
3036
3037 -- >>> 3. Turn any references to :new. style notation to param style
3038 --
3039
3040 -- Find first occurences of :new.
3041 l_pos := instr(p_sql,':NEW.');
3042 -- Loop through all occurences
3043 while (l_pos > 0) loop
3044
3045 --Get extract from this instance of :new to next
3046 --
3047 l_pos_next := instr(p_sql,':NEW.',l_pos+1);
3048 --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3049 if (l_pos_next <> 0 ) then
3050 l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3051 else
3052 l_extract := substr(p_sql,l_pos);
3053 l_pos_next := length(p_sql)+1;
3054 end if;
3055
3056 l_col_name := translate(l_extract,' ),|;'||g_eol,'*****');
3057 l_col_name := substr(l_col_name,1,instr(l_col_name,'*')-1);
3058
3059 -- Amend the prefix and make sure we're not over the 30 char limit
3060 l_new_col_name := substr(replace(l_col_name,':NEW.','P_NEW_'),1,30);
3061
3062 --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3063
3064 l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3065 p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3066
3067 --Find next occurence ,will be first as just done previous first,
3068 --but go from l_pos as quicker
3069 l_pos := instr(p_sql,':NEW.',l_pos);
3070
3071 end loop;
3072
3073 -- >>> 4. Remove the instances of DECLARE
3074 -- --now the calling code adds the DECLARE explicitly, as opposed to add_declarations
3075 --this should actually not find any DECLARE chars
3076 p_sql := replace(p_sql,'DECLARE');
3077 end get_dyt_pkg_version_of_code;
3078
3079 --
3080 -- +---------------------------------------------------------------------------+
3081 -- | NAME : get_dyt_proc_name |
3082 -- | SCOPE : PRIVATE |
3083 -- | DESCRIPTION: Would like to use full dynamic trigger name simply as proc |
3084 -- | name but sadly some are too long, so strip down, but keep the|
3085 -- | last few chars as these are often the useful identifiers. |
3086 -- +---------------------------------------------------------------------------+
3087 FUNCTION get_dyt_proc_name(p_dyt_name IN VARCHAR2) RETURN varchar2 IS
3088
3089 l_suffix pay_trigger_events.short_name%TYPE;
3090 l_proc_name varchar2(30);
3091 BEGIN
3092
3093 --Strip off the useful suffix (make sure its max 30chars)
3094 l_suffix := substr( substr(p_dyt_name,instr(p_dyt_name,'_',-1)) , 1 , 30);
3095
3096 --First version of proc_name is as many chars at start of p_dyt_name
3097 l_proc_name := substr(p_dyt_name,1,30 - length(l_suffix));
3098 --Full version is first||suffix
3099 l_proc_name := l_proc_name||l_suffix;
3100
3101 --hr_utility.trace(' Got DYT pkg procedure name: '||l_proc_name);
3102 return l_proc_name;
3103 END;
3104
3105 --
3106 -- +---------------------------------------------------------------------------+
3107 -- | NAME : gen_dyt_pkg_full_code |
3108 -- | SCOPE : PUBLIC |
3109 -- | DESCRIPTION: See header |
3110 -- +---------------------------------------------------------------------------+
3111 PROCEDURE gen_dyt_pkg_full_code(p_tab_id IN NUMBER,
3112 p_ok IN OUT NOCOPY BOOLEAN) IS
3113 --
3114
3115 l_tab_id pay_dated_tables.dated_table_id%TYPE;
3116 l_tab_name pay_dated_tables.table_name%TYPE;
3117 l_tab_dyt_types pay_dated_tables.dyn_trigger_type%TYPE;
3118 l_tab_dyt_pkg_name pay_dated_tables.dyn_trigger_package_name%TYPE;
3119 l_datetracked_table varchar2(5);
3120
3121 --
3122 l_hs varchar2(32000); --Used as header sql placeholders
3123 l_bs varchar2(32000); --Used as body sql placeholder
3124 l_hsql varchar2(32000); --Used as header
3125 l_bsql varchar2(32767); --Used as body
3126
3127 l_dyt_params g_params_tab_type; --hold params to build dyt in dyt_pkg
3128 l_hok_params g_params_tab_type; --hold params to build rhi-hook wrapper
3129 l_dbt_name varchar2(80);
3130
3131 l_head_ok BOOLEAN;
3132 l_body_ok BOOLEAN;
3133 l_flag varchar2(1);
3134
3135 l_dyt_pkg_head_tbl t_varchar2_32k_tbl;
3136 l_dyt_pkg_body_tbl t_varchar2_32k_tbl;
3137
3138 --
3139 --Given id, get table info
3140 CURSOR csr_table_info (cp_tab_id in NUMBER) IS
3141 SELECT pdt.dated_table_id,
3142 pdt.table_name,
3143 nvl(pdt.dyn_trigger_type,'T'),
3144 pdt.dyn_trigger_package_name,
3145 decode(start_date_name,
3146 null, 'N',
3147 'Y')
3148 FROM pay_dated_tables pdt
3149 WHERE pdt.dated_table_id = (cp_tab_id);
3150
3151 -- Get details of all dyn-triggers on given table
3152 CURSOR csr_dyts_on_table(cp_name in VARCHAR2) IS
3153 SELECT pte.event_id, pte.short_name,
3154 pte.triggering_action, pte.description,
3155 DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info,
3156 enabled_flag,generated_flag
3157 FROM pay_trigger_events pte
3158 WHERE pte.table_name = (cp_name)
3159 AND nvl(pte.protected_flag,'N') <> 'Y';
3160
3161 --
3162 l_proc varchar2(35) := 'gen_dyt_pkg_full_code';
3163 BEGIN
3164 hr_utility.set_location(' Entering: '||l_proc,10);
3165 -- The package to hold the code is created with two sections.
3166 -- a. The main trigger code, built using info from the dynamic triggers screen
3167 -- b. The after_update, after_insert, after_delete standard called by rhi,
3168 -- these act as wrappers calling (a)'s.
3169 --
3170 -- These parts are all built dynamically
3171 -- At this time, this code may have been called for the generation of a
3172 -- SINGLE trigger, However, we generate a dynamic trigger package (dyt_pkg)
3173 -- representing code for ALL triggers on that table.
3174
3175 -- NB. It is important to note that there is a subtle difference between
3176 -- database triggers and dynamic triggers. The latter are defined on site giving
3177 -- great flexibility, i.e. what customer-specific calls need to be made as part of
3178 -- this process. How the code is actually stored provides more flexibility.
3179 -- This is table specific and defined in pay_dated_tables, eg the dyn-trigger
3180 -- code can be stored as database triggers, in a separate package or in both of these.
3181 -- ...
3182 -- Since continuous calc we are moving toward the idea of no database triggers,
3183 -- (negative issues with maintenance) and toward stored code as a package.
3184 -- Indeed, many seeded offerings on core tables will have this behaviour. However,
3185 -- we allow greater customer flexibilty by leaving database triggers as an option.
3186 -- (Especially useful for non-API supported customer database tables.)
3187 --
3188 -- Fetch the table information, given this passed id
3189 OPEN csr_table_info(p_tab_id);
3190 FETCH csr_table_info INTO l_tab_id, l_tab_name,
3191 l_tab_dyt_types, l_tab_dyt_pkg_name,
3192 l_datetracked_table;
3193 CLOSE csr_table_info;
3194
3195 -- Create table of varchar2's representing the full head and body code.
3196 -- Initialise the holders of PL/SQL code
3197 --
3198 -- >>> 1. Add the start of this 'dynamic-trigger package code', incl comments
3199 --
3200 l_hs := l_hs||'/*'||g_eol;
3201 l_hs := l_hs||' =================================================='||g_eol;
3202 l_hs := l_hs||' This is a dynamically generated database package '||g_eol;
3203 l_hs := l_hs||' containing code to support the use of dynamic '||g_eol;
3204 l_hs := l_hs||' triggers. '||g_eol;
3205 l_hs := l_hs||' Preference of package Vs dbms triggers supporting '||g_eol;
3206 l_hs := l_hs||' dyn'' triggers is made via the dated table form. '||g_eol;
3207 l_hs := l_hs||' . '||g_eol;
3208 l_hs := l_hs||' This code will be called implicitly by table rhi '||g_eol;
3209 l_hs := l_hs||' and explictly from non-API packages that maintain '||g_eol;
3210 l_hs := l_hs||' data on the relevant table. '||g_eol;
3211 l_hs := l_hs||' =================================================='||g_eol;
3212 l_hs := l_hs||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3213 l_hs := l_hs||' --------------------------------------------------'||g_eol;
3214 l_hs := l_hs||' Package Name: '||l_tab_dyt_pkg_name||g_eol;
3215 l_hs := l_hs||' Base Table: '||l_tab_name||g_eol;
3216 l_hs := l_hs||' Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3217 l_hs := l_hs||' =================================================='||g_eol;
3218 l_hs := l_hs||'*/'||g_eos;
3219
3220 -- Add first line of code to two main placeholders, and add the comments
3221 --
3222 l_hsql := l_hsql||'Create or replace package '||l_tab_dyt_pkg_name||g_eol
3223 ||'AS'||g_eos;
3224 l_hsql := l_hsql||l_hs;
3225 l_bsql := l_bsql||'Create or replace package body '||l_tab_dyt_pkg_name||g_eol
3226 ||'IS'||g_eos;
3227 l_bsql := l_bsql||l_hs;
3228 l_hs :='';
3229 --
3230
3231 -- Shove this first chunk of code into holder
3232 l_dyt_pkg_head_tbl(0) := l_hsql;
3233 l_dyt_pkg_body_tbl(0) := l_bsql;
3234
3235 -- >>> 2. Get all parameters defined for this table in to table of records for future
3236 -- manipulation. This table holds the following info:
3237 hr_utility.set_location(' -Get parameters DYT : '||l_proc,20);
3238
3239 -- i. local name --entered in form
3240 -- ii. generated pkg param version of i-used for internal pkg proc
3241 -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3242 -- iv. type of col
3243 --
3244 get_dyt_pkg_params_tbl(l_tab_id, l_tab_name, l_dyt_params);
3245 hr_utility.set_location(' -Get parameters after_xxx : '||l_proc,25);
3246 get_dyt_rhi_params(l_tab_id, l_tab_name, l_hok_params);
3247
3248 -- >>> 3. Add the dynamic trigger code as separate public procedures
3249 -- loop for each dyn trig, calling a procedure to create appropriate code
3250 --
3251 hr_utility.set_location(' -Create dyt procedure code: '||l_proc,30);
3252 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3253 gen_dyt_pkg_proc(dyt_rec.event_id,dyt_rec.short_name,
3254 l_tab_name,dyt_rec.triggering_action,
3255 dyt_rec.description,dyt_rec.info,
3256 l_dyt_params,l_hs,l_bs);
3257 l_dyt_pkg_head_tbl(csr_dyts_on_table%ROWCOUNT) := l_hs;
3258 l_dyt_pkg_body_tbl(csr_dyts_on_table%ROWCOUNT) := l_bs;
3259
3260 l_hs := '';l_bs :='';
3261
3262 END LOOP;
3263 --
3264 -- >>> 4. Add the wrapper procedures for row handler entry points
3265 -- Create the wrapper procedures that the rhi will call, these simply call
3266 -- the newly created, dynamic trigger code procedures
3267 -- Three times, one for each trigger type
3268 --
3269 hr_utility.set_location(' -Create hook entry point procedure code: '||l_proc,40);
3270 gen_dyt_pkg_rhi_proc(l_tab_name,'I','INSERT',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
3271 l_hs := ''; l_bs := '';
3272 --
3273 gen_dyt_pkg_rhi_proc(l_tab_name,'U','UPDATE',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
3274 l_hs := ''; l_bs := '';
3275 --
3276 gen_dyt_pkg_rhi_proc(l_tab_name,'D','DELETE',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
3277 l_hs := ''; l_bs := '';
3278
3279
3280 -- >>> 5. Complete the package code
3281 --
3282 l_hsql := ''; l_bsql := '';
3283 l_hsql := l_hsql||'END '||l_tab_dyt_pkg_name||';'||g_eol;
3284 l_bsql := l_bsql||'/* END_PACKAGE */'||g_eol;
3285 l_bsql := l_bsql||'END '||l_tab_dyt_pkg_name||';'||g_eol;
3286 l_dyt_pkg_head_tbl(l_dyt_pkg_head_tbl.last + 1) := l_hsql;
3287 l_dyt_pkg_body_tbl(l_dyt_pkg_body_tbl.last + 1) := l_bsql;
3288
3289
3290 -- This is the first time we will be adding to the g_dyt_pkg_head and g_dyt_pkg_head tables
3291 -- so empty first then add
3292 init_dyt_pkg;
3293 add_to_dyt_pkg(l_dyt_pkg_head_tbl,FALSE);
3294 add_to_dyt_pkg(l_dyt_pkg_body_tbl,TRUE);
3295
3296
3297 -- >>> 6. Generate and compile this new dynamic package
3298 --
3299 hr_utility.set_location(' -Generate database package: '||l_proc,60);
3300 build_dyt_pkg_from_tbl( g_dyt_pkg_head,g_dyt_pkg_hindex,FALSE);
3301 build_dyt_pkg_from_tbl( g_dyt_pkg_body,g_dyt_pkg_bindex,TRUE);
3302
3303 -- >>> 7. Create database trigger equivalent of dyn-trigger if required
3304 -- Go through recently cached cursor again
3305 p_ok := TRUE; --assume ok first, then test each as built
3306 --
3307 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3308 l_dbt_name := get_trigger_name(dyt_rec.event_id,l_tab_name,dyt_rec.triggering_action);
3309 --
3310 IF ( l_tab_dyt_types = 'B') THEN
3311 gen_dyt_db_trig(dyt_rec.event_id,dyt_rec.short_name
3312 ,l_tab_name, dyt_rec.triggering_action
3313 ,dyt_rec.description,dyt_rec.info
3314 ,l_dyt_params,l_tab_dyt_pkg_name);
3315 --Set db trigger to enabled as dependent on the dyn trigger value
3316 enable_trigger( l_dbt_name,flag_to_boolean(dyt_rec.enabled_flag) );
3317
3318 --if so far all is well perform check on this new db trigger
3319 if (p_ok) then
3320 p_ok := module_ok(l_dbt_name,'TRIGGER');
3321 end if;
3322
3323 ELSE
3324 --Package only then drop db trigs if they exist.
3325 drop_trigger(l_dbt_name);
3326 p_ok := TRUE;
3327 END IF;
3328
3329 END LOOP;
3330
3331
3332 -- >>> 8. Test how well package/triggers have been created and return some kind of status
3333 --
3334 -- pkg
3335 l_head_ok := module_ok(l_tab_dyt_pkg_name,'PACKAGE');
3336 l_body_ok := module_ok(l_tab_dyt_pkg_name,'PACKAGE BODY');
3337
3338 --Final return status is true only if all parts are success
3339 if (l_head_ok and l_body_ok and p_ok) then
3340 p_ok := TRUE;
3341 l_flag := 'Y';
3342 else
3343 p_ok :=FALSE;
3344 l_flag := 'N';
3345 end if;
3346
3347 --hr_utility.set_location(' -Status of database package: '||l_flag||l_proc,80);
3348 --Complete FOR WHOLE TABLE, so mark pkg gen, and all dyt as generated + enabled or vice versa
3349 -- All or nothing, either all dyt's are success, or mark all as failure
3350 update pay_dated_tables
3351 set dyn_trig_pkg_generated = l_flag
3352 where table_name = l_tab_name
3353 and dated_table_id = l_tab_id;
3354 --
3355 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3356 if (p_ok) then --success so back to original
3357 update pay_trigger_events
3358 set generated_flag = l_flag,
3359 enabled_flag = dyt_rec.enabled_flag
3360 where event_id = dyt_rec.event_id;
3361 else
3362 update pay_trigger_events --failure so disabled
3363 set generated_flag = l_flag,
3364 enabled_flag = l_flag
3365 where event_id = dyt_rec.event_id;
3366 end if;
3367
3368 END LOOP;
3369 commit; --make sure updates are saved
3370 hr_utility.set_location(' Leaving: '||l_proc,900);
3371 END gen_dyt_pkg_full_code;
3372
3373 -- +-----------------------------------------------------------------------------+
3374 -- | NAME : gen_dyt_pkg_proc |
3375 -- | SCOPE : PRIVATE |
3376 -- | DESCRIPTION: This procedure generates the code representing a single dynamic|
3377 -- | trigger. All information declared in the dynamic triggers form is used|
3378 -- | to build up a package version of this trigger. Parameter info is got |
3379 -- | from the table_event_updates form. |
3380 -- | The actual components are built up using existing procedures |
3381 -- | Namely, add_components,add_initialisations, but we have to modify the |
3382 -- | resulting code slightly. |
3383 -- +-----------------------------------------------------------------------------+
3384 PROCEDURE gen_dyt_pkg_proc(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
3385 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
3386 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
3387 ,p_dyn_pkg_params IN g_params_tab_type
3388 ,p_hs IN OUT NOCOPY VARCHAR2
3389 ,p_bs IN OUT NOCOPY VARCHAR2) IS
3390 --
3391 l_inits BOOLEAN;
3392 l_comps BOOLEAN;
3393 l_tab_name pay_dated_tables.table_name%TYPE := p_tab_name;
3394 l_dyt_id pay_trigger_events.event_id%TYPE := p_dyt_id;
3395 l_dyt_name pay_trigger_events.short_name%TYPE := get_dyt_proc_name(p_dyt_name);
3396 l_dyt_act pay_trigger_events.triggering_action%TYPE := p_dyt_act;
3397 l_dyt_info varchar2(30) := p_dyt_info;
3398 l_dyt_desc pay_trigger_events.description%TYPE := p_dyt_desc ;
3399
3400 l_sql varchar2(32000); --Used as a temp holder.
3401 i number;
3402 j number;
3403 delim varchar2(15) := ' ';
3404 --
3405 BEGIN
3406 --
3407 -- Initialise the statement PL/SQL code
3408 p_hs := ''; p_bs := '';
3409 --
3410 --
3411 -- Add an initial comment section to the trigger code
3412
3413 l_sql := l_sql||'/*'||g_eol;
3414 l_sql := l_sql||' ================================================'||g_eol;
3415 l_sql := l_sql||' This is a dynamically generated package procedure'||g_eol;
3416 l_sql := l_sql||' with code representing a dynamic trigger '||g_eol;
3417 l_sql := l_sql||' ================================================'||g_eol;
3418 l_sql := l_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3419 l_sql := l_sql||' ------------------------------------------------'||g_eol;
3420 l_sql := l_sql||' Name: '||l_dyt_name||g_eol;
3421 l_sql := l_sql||' Table: '||l_tab_name||g_eol;
3422 l_sql := l_sql||' Action: '||l_dyt_info||g_eol;
3423 l_sql := l_sql||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3424 l_sql := l_sql||' Description: '||l_dyt_desc||g_eol;
3425 l_sql := l_sql||' Full trigger name: '||p_dyt_name||g_eol;
3426 l_sql := l_sql||' ================================================'||g_eol;
3427 l_sql := l_sql||'*/'||g_eol||'--'||g_eol;
3428 --
3429 l_sql := l_sql||'PROCEDURE '||l_dyt_name||g_eol;
3430 -- Complete procedure definition, (still same for header and body)
3431 --
3432 --
3433 i := 0;
3434 while i < p_dyn_pkg_params.count LOOP
3435 --only need to print params that are relevant
3436 if (substr(p_dyn_pkg_params(i).usage_type,2,1) = l_dyt_act) then
3437 -- quick formatting option
3438 if (length(p_dyn_pkg_params(i).param_form) > 40) then
3439 j := 80; else j := 40;
3440 end if;
3441 --if first param then add opening bracket
3442 if (delim = ' ') then l_sql := l_sql || '('||g_eol;
3443 end if;
3444 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,j,' ')
3445 ||' in '||p_dyn_pkg_params(i).data_type||g_eol;
3446 delim := ',';
3447 end if;
3448 i := i+1;
3449 end loop;
3450
3451 --Only close bracket if had params
3452 if(delim = ',') then l_sql := l_sql ||' )'; end if;
3453 --End the header text, then continue with body, now create two distinct strings
3454 p_hs := l_sql||'; -- End of procedure definition for '||l_dyt_name||g_eos;
3455 p_bs := l_sql ||' IS '||g_eos;
3456
3457 --
3458 -- Add the declaration section
3459 -- Do not need DECLARE before calling add_declarations
3460 p_bs := p_bs||' /* Local variable declarations */'||g_eol;
3461
3462 -- Add the seeded declaration section
3463 add_declarations(l_dyt_id,p_bs);
3464
3465 -- Add any hard-coded declarations
3466 -- n/a
3467
3468 p_bs := p_bs||'BEGIN'||g_eol;
3469 --
3470 p_bs := p_bs||' hr_utility.trace('' >DYT: Execute procedure version of Dynamic Trigger: '||p_dyt_name||''');'||g_eol;
3471
3472
3473 --
3474 -- Add the data migrator check - Bug 1885557
3475 p_bs := p_bs||'IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
3476 p_bs := p_bs||' RETURN;'||g_eol;
3477 p_bs := p_bs||'END IF;'||g_eol;
3478 -- Add the initialisation and component PL/SQL code
3479 l_inits := add_initialisations(l_dyt_id,p_bs,l_tab_name);
3480 l_comps := add_components(l_dyt_id,p_bs);
3481 --
3482 -- If we didn't add any initialisations or components then add a NULL
3483 -- operation to prevent compilation errors when triggers without any
3484 -- default initialisations are first created
3485 IF NOT l_inits AND NOT l_comps THEN
3486 p_bs := p_bs||' NULL;'||g_eol;
3487 END IF;
3488 --
3489
3490 -- Add a default exception block to catch all errors and write the
3491 -- trigger name and error text to the standard Oracle Apps error logging
3492 -- mechanism
3493 p_bs := p_bs||'EXCEPTION'||g_eol;
3494 p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3495 p_bs := p_bs||' hr_utility.set_location('''||
3496 l_dyt_name||''',ABS(SQLCODE));'||g_eol;
3497 p_bs := p_bs||' RAISE;'||g_eol;
3498 p_bs := p_bs||' --'||g_eol;
3499 p_bs := p_bs||'END '||l_dyt_name||';'||g_eos;
3500
3501 --Before we return the sql, replace any instances of :new, :old; this is because
3502 --we have used the existing mechanism to get initialisations and components
3503 --and these may well rely on 'dbms trigger' notation, however we are using a dyt pkg
3504 get_dyt_pkg_version_of_code(p_bs,p_tab_name,p_dyt_act);
3505 END gen_dyt_pkg_proc;
3506
3507 -- +---------------------------------------------------------------------------+
3508 -- | NAME : gen_dyt_pkg_rhi_proc |
3509 -- | SCOPE : PUBLIC |
3510 -- | DESCRIPTION: See header |
3511 -- +---------------------------------------------------------------------------+
3512 PROCEDURE gen_dyt_pkg_rhi_proc( p_tab_name IN VARCHAR2
3513 ,p_dyt_act IN VARCHAR2 ,p_dyt_info IN VARCHAR2
3514 ,p_hok_params IN g_params_tab_type
3515 ,p_hs IN OUT NOCOPY VARCHAR2
3516 ,p_bs IN OUT NOCOPY VARCHAR2
3517 ,p_dyt_params IN g_params_tab_type
3518 ,p_dyt_pkg_head_tbl IN OUT NOCOPY t_varchar2_32k_tbl
3519 ,p_dyt_pkg_body_tbl IN OUT NOCOPY t_varchar2_32k_tbl
3520 ,p_datetracked_table in VARCHAR2) IS
3521 --
3522 -- Get details of all dyn-triggers on given table,
3523 CURSOR csr_dyts_on_tab(cp_name in VARCHAR2,cp_action in VARCHAR2) IS
3524 SELECT pte.event_id, pte.short_name,
3525 pte.triggering_action, pte.description,
3526 DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info
3527 FROM pay_trigger_events pte
3528 WHERE pte.table_name = (cp_name)
3529 AND pte.triggering_action = (cp_action)
3530 AND nvl(pte.protected_flag,'N') <> 'Y';
3531
3532
3533 i number;
3534 j number;
3535 delim varchar2(15) := ' ';
3536
3537 BEGIN
3538 --
3539 -- Add an initial comment section to the trigger code
3540
3541 p_hs := p_hs||'/*'||g_eol;
3542 p_hs := p_hs||' ================================================'||g_eol;
3543 p_hs := p_hs||' This is a dynamically generated procedure. '||g_eol;
3544 p_hs := p_hs||' Will be called by API. '||g_eol;
3545 p_hs := p_hs||' ================================================'||g_eol;
3546 p_hs := p_hs||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3547 p_hs := p_hs||' ------------------------------------------------'||g_eol;
3548 p_hs := p_hs||' Name: AFTER_'||upper(p_dyt_info)||g_eol;
3549 p_hs := p_hs||' Table: '||p_tab_name||g_eol;
3550 p_hs := p_hs||' Action: '||p_dyt_info||g_eol;
3551 p_hs := p_hs||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3552 p_hs := p_hs||' Description: Called as part of '||p_dyt_info||' process'||g_eol;
3553 p_hs := p_hs||' ================================================'||g_eol;
3554 p_hs := p_hs||'*/'||g_eos;
3555 --
3556 p_hs := p_hs||'PROCEDURE AFTER_'||upper(p_dyt_info)||g_eol;
3557 -- Complete procedure definition, (still same for header and body)
3558 --
3559 --
3560 i := 0;
3561
3562 while i < p_hok_params.count LOOP
3563 --only need to print params that are relevant
3564 if (substr(p_hok_params(i).usage_type,2,1) = p_dyt_act) then
3565 -- quick formatting option
3566 if (length(p_hok_params(i).value_name) > 40) then
3567 j := 80; else j := 40;
3568 end if;
3569 if (delim = ' ') then p_hs := p_hs || '('||g_eol;
3570 end if;
3571
3572 p_hs := p_hs ||' '||delim||rpad(p_hok_params(i).value_name,j,' ')
3573 ||' in '||p_hok_params(i).data_type||g_eol;
3574 delim := ',';
3575
3576 end if;
3577 i := i+1;
3578 end loop;
3579
3580 p_hs := p_hs ||' )';
3581
3582 --End the header text, then continue with body, create two distinct strings
3583 p_bs := p_hs ||' IS '||g_eol;
3584 p_bs := p_bs ||' l_mode varchar2(80);'||g_eos;
3585 p_bs := p_bs ||' BEGIN'||g_eos;
3586
3587 p_bs := p_bs||' hr_utility.trace('' >DYT: Main entry point from row handler, AFTER_'||p_dyt_info||''');'||g_eol;
3588
3589 p_hs := p_hs||'; -- End of procedure definition for AFTER_'||upper(p_dyt_info)||g_eos;
3590
3591 --Create main body code, i.e. call to newly created dyn-trigger procedure(s).
3592 --
3593
3594 p_bs := p_bs ||' /* Mechanism for event capture to know whats occurred */'||g_eol;
3595 p_bs := p_bs ||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
3596 if (upper(p_dyt_info) = 'INSERT') then
3597 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_insert;'||g_eos;
3598 else
3599 if (p_datetracked_table = 'Y') then
3600 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := p_datetrack_mode;'||g_eos;
3601 else
3602 if (upper(p_dyt_info) = 'UPDATE') then
3603 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_correction;'||g_eos;
3604 else
3605 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_zap;'||g_eos;
3606 end if;
3607 end if;
3608 end if;
3609 --
3610 -- Save the header details.
3611 p_dyt_pkg_head_tbl(p_dyt_pkg_head_tbl.last + 1) := p_hs;
3612 p_hs := '';
3613 --
3614 delim := 'X'; --will be reset if we have any dyt's
3615 FOR dyt_rec IN csr_dyts_on_tab(p_tab_name,p_dyt_act) LOOP
3616 delim :=' '; i := 0;--reset counters
3617
3618 -- Save the body details
3619 p_dyt_pkg_body_tbl(p_dyt_pkg_body_tbl.last + 1) := p_bs;
3620 p_bs := '';
3621 --
3622 p_bs := ' if (paywsdyg_pkg.trigger_enabled('''
3623 ||dyt_rec.short_name||''')) then'||g_eol;
3624 p_bs := p_bs||' '||get_dyt_proc_name(dyt_rec.short_name)||'('||g_eol;
3625
3626 -- build up params for call to newly created procedure
3627 WHILE i < p_dyt_params.count LOOP
3628 --only need to print params that are relevant for this action
3629 --though may not be strictly relevant for this dyn trig as we just pass all
3630 if (substr(p_dyt_params(i).usage_type,2,1) = p_dyt_act) then
3631 --
3632 if (length(p_dyt_params(i).param_form) > 40) then -- quick formatting option
3633 j := 80; else j := 40;
3634 end if;
3635 p_bs := p_bs ||' '||delim||rpad(p_dyt_params(i).param_form,j,' ')
3636 ||' => '||p_dyt_params(i).value_name||g_eol;
3637 delim := ',';
3638 end if;
3639
3640 i := i+1;
3641 END LOOP;
3642 p_bs := p_bs ||' );'||g_eol||' end if;'||g_eos;
3643 END LOOP;
3644 -- Written all calls to newly created relevant dyn-trigger, if none add null
3645 --
3646 if (delim = 'X') then
3647 p_bs := p_bs ||' /* no calls => no dynamic triggers of this type on this table */';
3648 p_bs := p_bs ||g_eol||' null;'||g_eos;
3649 end if;
3650 --Reset the flag
3651 p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eos;
3652
3653 -- Add a default exception block to catch all errors and write the
3654 -- trigger name and error text to the standard Oracle Apps error log
3655 p_bs := p_bs||'EXCEPTION'||g_eol;
3656 p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3657 p_bs := p_bs||' hr_utility.set_location('''||
3658 'AFTER_'||upper(p_dyt_info)||''',ABS(SQLCODE));'||g_eol;
3659 p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3660 p_bs := p_bs||' RAISE;'||g_eol;
3661 p_bs := p_bs||' --'||g_eol;
3662
3663 p_bs := p_bs ||'END AFTER_'||upper(p_dyt_info)||';'||g_eos;
3664
3665 -- Save the body details
3666 p_dyt_pkg_body_tbl(p_dyt_pkg_body_tbl.last + 1) := p_bs;
3667 p_bs := '';
3668
3669 END gen_dyt_pkg_rhi_proc;
3670
3671 -- +---------------------------------------------------------------------------+
3672 -- | NAME : gen_dyt_db_trig |
3673 -- | SCOPE : PUBLIC |
3674 -- | DESCRIPTION: See header |
3675 -- +---------------------------------------------------------------------------+
3676 PROCEDURE gen_dyt_db_trig(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
3677 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
3678 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
3679 ,p_dyn_pkg_params IN g_params_tab_type
3680 ,p_tab_dyt_pkg_name in varchar2
3681 ) IS
3682 --
3683 l_dyt_name pay_trigger_events.short_name%TYPE := get_dyt_proc_name(p_dyt_name);
3684 l_sql varchar2(32000); --Used as a temp holder.
3685 l_dbt_name varchar2(80);
3686 i number;
3687 j number;
3688 delim varchar2(15) := ' ';
3689
3690 BEGIN
3691 l_dbt_name := get_trigger_name(p_dyt_id,p_tab_name,p_dyt_act);
3692 --
3693 -- Add an initial comment section to the trigger code
3694
3695 l_sql := l_sql||'/*'||g_eol;
3696 l_sql := l_sql||' ================================================'||g_eol;
3697 l_sql := l_sql||' This is a dynamically generated database trigger'||g_eol;
3698 l_sql := l_sql||' ================================================'||g_eol;
3699 l_sql := l_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3700 l_sql := l_sql||' ------------------------------------------------'||g_eol;
3701 l_sql := l_sql||' Trigger: '||l_dbt_name||g_eol;
3702 l_sql := l_sql||' Table: '||p_tab_name||g_eol;
3703 l_sql := l_sql||' Action: '||p_dyt_info||g_eol;
3704 l_sql := l_sql||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3705 l_sql := l_sql||' Description: If enabled, this trigger will be '||g_eol;
3706 l_sql := l_sql||' called as part of '||p_dyt_info||' process. '||g_eol;
3707 l_sql := l_sql||' ================================================'||g_eol;
3708 l_sql := l_sql||'*/'||g_eos;
3709 l_sql := l_sql||'DECLARE'||g_eol;
3710 l_sql := l_sql||' l_mode varchar2(80);'||g_eol;
3711 l_sql := l_sql||'BEGIN'||g_eol;
3712 --
3713 l_sql := l_sql||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
3714 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := pay_dyn_triggers.g_dbms_dyt;'||g_eol;
3715
3716 l_sql := l_sql||' IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
3717 l_sql := l_sql||' RETURN;'||g_eol;
3718 l_sql := l_sql||' END IF;'||g_eos;
3719
3720 -- Code call to newly created procedure
3721 --
3722 l_sql := l_sql||p_tab_dyt_pkg_name||'.'||l_dyt_name||'('||g_eol;
3723 --
3724 -- build up params from those entered in dated tables form
3725 i := 0; delim :=' ';
3726
3727 while i < p_dyn_pkg_params.count LOOP
3728 --only need to print params that are relevant
3729 if (substr(p_dyn_pkg_params(i).usage_type,2,1) = p_dyt_act) then
3730 --Need to create the mock rhi-hook control params
3731 --NB think P_NEW_EFFECTIVE_DATE is now redundant, but leave in as no overhead
3732 --
3733 if (upper(p_dyn_pkg_params(i).param_form) = 'P_NEW_EFFECTIVE_DATE') then
3734 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,40,' ')
3735 ||' => :new.effective_start_date'||g_eol;
3736 delim := ',';
3737 elsif (upper(p_dyn_pkg_params(i).param_form) = 'P_NEW_DATETRACK_MODE') then
3738 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,40,' ')
3739 ||' => pay_dyn_triggers.g_dbms_dyt'||g_eol;
3740 delim := ',';
3741 else --MAIN useful db cols
3742 -- quick formatting option
3743 if (length(p_dyn_pkg_params(i).param_form) > 40) then
3744 j := 80; else j := 40;
3745 end if;
3746 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,j,' ')
3747 ||' => '||p_dyn_pkg_params(i).local_form||g_eol;
3748 delim := ',';
3749 end if;
3750 end if;
3751
3752 i := i+1;
3753 end loop;
3754
3755 --End the trigger text
3756 l_sql := l_sql||'); -- End of call to dynamic trigger code stored in package '||g_eos;
3757 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3758
3759 -- Add a default exception block to catch all errors and write the
3760 -- trigger name and error text to the standard Oracle Apps error log
3761 l_sql := l_sql||'EXCEPTION'||g_eol;
3762 l_sql := l_sql||' WHEN OTHERS THEN'||g_eol;
3763 l_sql := l_sql||' hr_utility.set_location('''||
3764 l_dbt_name||''',ABS(SQLCODE));'||g_eol;
3765 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3766 l_sql := l_sql||' RAISE;'||g_eol;
3767 l_sql := l_sql||' --'||g_eol;
3768
3769 l_sql := l_sql||'END;'||g_eol;
3770
3771 --Use common procedure to create trigger
3772 --
3773 create_trigger(l_dbt_name,p_tab_name,p_dyt_act,l_sql);
3774 end gen_dyt_db_trig;
3775
3776 -- +---------------------------------------------------------------------------+
3777 -- | NAME : trigger_enabled |
3778 -- | SCOPE : PUBLIC |
3779 -- | DESCRIPTION: Simply returns boolean to see if dyn trigger is enabled. |
3780 -- | Called by dynamically created rhi proc in dynamic package |
3781 -- | PARAMETERS : p_dyt - The dynamic trigger name |
3782 -- | RETURNS : TRUE if trigger is enabled, FALSE otherwise |
3783 -- | RAISES : None |
3784 -- +---------------------------------------------------------------------------+
3785 FUNCTION trigger_enabled(p_dyt varchar2) return BOOLEAN
3786 IS
3787 cursor csr_enabled(cp_dyt varchar2) is
3788 SELECT enabled_flag
3789 FROM pay_trigger_events
3790 where short_name = cp_dyt;
3791
3792 l_flag varchar2(1);
3793
3794 BEGIN
3795 open csr_enabled(p_dyt);
3796 fetch csr_enabled into l_flag;
3797 close csr_enabled;
3798 return (flag_to_boolean(l_flag));
3799
3800 END trigger_enabled;
3801
3802 -- +---------------------------------------------------------------------------+
3803 -- | name : convert_tab_style |
3804 -- | scope : public |
3805 -- | description: there are times when the seeded behaviour needs to be altered|
3806 -- | usually as a result of release issues. this procedure provides a quick |
3807 -- | wrapper utility to change a dated table from dbms_dyt to dyt_pkg and vice|
3808 -- | versa.
3809 -- | parameters : p_table_name - the dated table name |
3810 -- | : p_dyt_type - eg t<dbms trigger> p<ackage> b<oth> |
3811 -- | returns : none
3812 -- | raises : none |
3813 -- +---------------------------------------------------------------------------+
3814 procedure convert_tab_style(p_table_name in varchar2,p_dyt_type in varchar2)
3815 is
3816
3817 l_name varchar2(240);
3818 l_hooks varchar2(15);
3819 l_ok boolean;
3820 l_api_module_id number;
3821
3822 cursor csr_dyt_ids(cp_table_name in varchar2) is
3823 select event_id,short_name from pay_trigger_events
3824 where table_name = cp_table_name;
3825 cursor csr_get_id is
3826 select api_module_id
3827 from hr_api_modules
3828 where module_name = p_table_name;
3829
3830 begin
3831 hr_utility.trace('>>> set table '||p_table_name||' to be style '||p_dyt_type);
3832 --
3833 update pay_dated_tables
3834 set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3835 where table_name = p_table_name;
3836
3837 hr_utility.trace(' creating dyt triggers...');
3838 --
3839 for dyt_record in csr_dyt_ids(p_table_name) loop
3840 paywsdyg_pkg.generate_trigger(
3841 dyt_record.event_id,
3842 l_name,
3843 l_ok);
3844 hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3845 if (p_dyt_type = 'P') then exit; end if;
3846 end loop;
3847 hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3848
3849
3850 if (p_dyt_type = 'P') then
3851 l_hooks := 'Y';
3852 else
3853 l_hooks := 'N';
3854 end if;
3855
3856 update hr_api_hook_calls
3857 set enabled_flag = l_hooks
3858 where api_hook_call_id in (
3859 select api_hook_call_id
3860 from hr_api_hook_calls ahc,
3861 hr_api_hooks ah,
3862 hr_api_modules am
3863 where ahc.api_hook_id = ah.api_hook_id
3864 and ah.api_module_id = am.api_module_id
3865 and ahc.call_package = (select dyn_trigger_package_name
3866 from pay_dated_tables
3867 where table_name = am.module_name)
3868 and am.module_name = p_table_name );
3869
3870 for module in csr_get_id loop
3871 --Rebuild Hooks Packages
3872 hr_api_user_hooks_utility.create_hooks_add_report(l_api_module_id);
3873
3874 end loop;
3875
3876 end convert_tab_style;
3877
3878 -- +---------------------------------------------------------------------------+
3879 -- | name : confirm_dyt_data |
3880 -- | scope : public (Use cautiously, designed as dev util) |
3881 -- | description: there are times when the seeded behaviour needs to be altered|
3882 -- | usually as a result of release issues. this procedure checks the data
3883 -- | for a given table and depending on the main switch (hook calls to DYT_PKG)
3884 -- | rebuilds the data for DYT_PKG behaviour (if calls existed) or DBMS dynamic
3885 -- | triggers (if no calls existed)
3886 -- | parameters : p_table_name - the dated table name |
3887 -- | returns : none
3888 -- | raises : none |
3889 -- +---------------------------------------------------------------------------+
3890 PROCEDURE confirm_dyt_data(p_table_name in varchar2) is
3891
3892 l_reqd_format varchar2(1); --EG will be set to 'T' dbms Trigger, 'P' Package
3893 l_current_format varchar2(1);
3894 l_dyt_pkg_exists varchar2(1); --EG will be set to 'Y' or 'N'
3895
3896
3897 cursor csr_dyt_pkg_hook is
3898 select count(*)
3899 from hr_api_hook_calls ahc,
3900 hr_api_hooks ah,
3901 hr_api_modules am
3902 where ahc.api_hook_id = ah.api_hook_id
3903 and ah.api_module_id = am.api_module_id
3904 and ahc.call_package = (select dyn_trigger_package_name
3905 from pay_dated_tables
3906 where table_name = am.module_name)
3907 and am.module_name = p_table_name;
3908 l_hook_count number;
3909
3910 cursor csr_tab_details is
3911 select dated_table_id,dyn_trigger_type,dyn_trigger_package_name,dyn_trig_pkg_generated
3912 from pay_dated_tables
3913 where table_name = p_table_name;
3914 l_dt_id pay_dated_tables.dated_table_id%type;
3915 l_dt_dyt_type pay_dated_tables.dyn_trigger_type%type;
3916 l_dt_pkg_name pay_dated_tables.dyn_trigger_package_name%type;
3917 l_dt_pkg_gen pay_dated_tables.dyn_trig_pkg_generated%type;
3918
3919 cursor csr_pkg_exist(cp_pkg in varchar2) is
3920 select status from user_objects
3921 where object_type = 'PACKAGE BODY'
3922 and object_name = cp_pkg;
3923 l_pkg_status all_objects.status%type;
3924
3925 l_need_rebuild_flag varchar2(15) := 'N';
3926 l_result boolean;
3927 l_prod_status varchar2(1);
3928 l_industry varchar2(1);
3929
3930 l_proc varchar2(240) := g_package||'.confirm_dyt_data';
3931 BEGIN
3932 hr_utility.set_location(l_proc,10);
3933 --
3934 -- >>> PHASE 1: Decide what is the reqd format for this table for dynamic trigger
3935 --
3936 open csr_dyt_pkg_hook;
3937 fetch csr_dyt_pkg_hook into l_hook_count;
3938 close csr_dyt_pkg_hook;
3939
3940 hr_utility.trace( '- Decision on what is the required behaviour based on enabled hook count.');
3941 if (l_hook_count > 0) then
3942 l_reqd_format := 'P';
3943 else
3944 l_reqd_format := 'T';
3945 end if;
3946 hr_utility.trace( '- Hook count is '||l_hook_count||' so REQD behaviour is '||l_reqd_format);
3947
3948
3949 -- >>> PHASE 2: Get the exisitng information for this table
3950 --
3951 open csr_tab_details;
3952 fetch csr_tab_details into l_dt_id, l_dt_dyt_type, l_dt_pkg_name, l_dt_pkg_gen;
3953 close csr_tab_details;
3954 hr_utility.trace( '- Dated table id '||l_dt_id||' has SEEDED behaviour '||l_dt_dyt_type);
3955
3956 open csr_pkg_exist(l_dt_pkg_name);
3957 fetch csr_pkg_exist into l_pkg_status;
3958 close csr_pkg_exist;
3959
3960 if (l_pkg_status is null) then l_pkg_status := 'NONE';
3961 end if;
3962 --hr_utility.trace( '- DYT_PKG '||l_dt_pkg_name||' has status '||l_pkg_status);
3963
3964 --Now do phase 3 if l_reqd_format = T, phase 4 if its = P
3965 if (l_reqd_format = 'T') then
3966 -- >>> PHASE 3: Deal with situation where we wish for NO dyt_pkg
3967 --
3968 -- || The DYT_PKG exists || No DYT_PKG exists ||
3969 --++===============||===============================================||
3970 -- Table is also || [A] bit odd || [B] Perfect ||
3971 -- set to T || no danger || Behaviour ||
3972 -- eg dbms_dyt || => nothing || ||
3973 --++===============||=======================||======================||
3974 -- Table is NOT || [C] Bad || [D] Very Bad ||
3975 -- set to T || => change table to || => change table to ||
3976 -- eg dyt_pkg || be dbms style || be dbms style ||
3977 --++===============++===============================================++
3978 hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
3979 hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
3980 if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
3981 l_need_rebuild_flag := 'N'; --odd but not terminal
3982
3983 elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
3984 hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
3985 l_need_rebuild_flag := 'N'; --odd but not terminal
3986 --
3987 elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
3988 l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
3989 --
3990 elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
3991 l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
3992 --
3993 end if;
3994 elsif (l_reqd_format = 'P') then
3995 -- >>> PHASE 4: Deal with situation where we wish for a dyt_pkg
3996 --
3997 -- || The DYT_PKG exists || No DYT_PKG exists ||
3998 --++===============||===============================================||
3999 -- Table is also || [A] bit odd || [B] Bad ||
4000 -- set to T || pkg exists and calls || hooks will fail ||
4001 -- eg dbms_dyt || => change to dyt_pkg || => change to dyt_pkg ||
4002 --++===============||=======================||======================||
4003 -- Table is NOT || [C] Perfect || [D] Very Bad ||
4004 -- set to T || || hooks will fail ||
4005 -- eg dyt_pkg || || => change to dyt_pkg ||
4006 --++===============++===============================================++
4007
4008 hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4009 hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4010 if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4011 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4012 --
4013 elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4014 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4015 --
4016 elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4017 l_need_rebuild_flag := 'N';
4018 hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4019 --
4020 elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
4021 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4022 --
4023 end if;
4024 end if;
4025
4026 if (l_need_rebuild_flag = 'Y') then
4027 paywsdyg_pkg.convert_tab_style(
4028 p_table_name => p_table_name,
4029 p_dyt_type => l_reqd_format);
4030 end if;
4031
4032 -- To cover the exception to the rule...namely pay_element_entry_values_f
4033 -- which does not have hooks and thus this procedure should never be called for
4034 -- this table. If this is called for pay_element_entries_f then make the
4035 -- vals table have the same behaviour.
4036 if (p_table_name = 'PAY_ELEMENT_ENTRIES_F') then
4037 paywsdyg_pkg.convert_tab_style(
4038 p_table_name => 'PAY_ELEMENT_ENTRY_VALUES_F',
4039 p_dyt_type => l_reqd_format);
4040 end if;
4041
4042 hr_utility.set_location(l_proc,900);
4043 END confirm_dyt_data;
4044
4045 --
4046 --
4047 -- does declaration exist for the specified variable
4048 function is_not_declared(p_id in number,p_name in varchar2) return boolean is
4049 --
4050 cursor csr_pay(cp_id in number,cp_name in varchar2) is
4051 select 'x'
4052 from pay_trigger_declarations
4053 where variable_name = cp_name
4054 and event_id = cp_id;
4055 --
4056 l_cx varchar2(1);
4057 l_rc boolean;
4058 begin
4059 open csr_pay(p_id,p_name);
4060 fetch csr_pay into l_cx;
4061 if csr_pay%notfound then
4062 l_rc := true;
4063 else
4064 l_rc := false;
4065 end if;
4066 close csr_pay;
4067 --
4068 return l_rc;
4069 end is_not_declared;
4070
4071 --
4072 -- if the table's got a business group id then that's fine,
4073 -- otherwise check if the user has defined their own local
4074 -- variable that we can use
4075 function no_business_context(p_table in varchar2,p_id in number) return boolean is
4076 l_rc boolean;
4077 begin
4078 if table_has_business_group(p_table) then
4079 l_rc := false;
4080 else
4081 l_rc := is_not_declared(p_id,'business_group_id');
4082 end if;
4083 --
4084 return l_rc;
4085 end no_business_context;
4086 --
4087 -- if the table's got a business group id then that's fine,
4088 -- otherwise check if the user has defined their own local
4089 -- variable that we can use
4090 function no_legislation_context(p_table in varchar2,p_id in number) return boolean is
4091 l_rc boolean;
4092 begin
4093 if table_has_business_group(p_table) then
4094 l_rc := false;
4095 else
4096 l_rc := is_not_declared(p_id,'legislation_code');
4097 end if;
4098 --
4099 return l_rc;
4100 end no_legislation_context;
4101 --
4102 -- if the table's got a payroll id then that's fine,
4103 -- otherwise check if the user has defined their own local
4104 -- variable that we can use
4105 function no_payroll_context(p_table in varchar2,p_id in number) return boolean is
4106 --
4107 l_rc boolean;
4108 begin
4109 if table_has_payroll(p_table) then
4110 l_rc := false;
4111 else
4112 l_rc := is_not_declared(p_id,'payroll_id');
4113 end if;
4114 --
4115 return l_rc;
4116 end no_payroll_context;
4117 --
4118 procedure ins(
4119 p_event_id in number,
4120 p_table_name in varchar2,
4121 p_short_name in varchar2,
4122 p_description in varchar2,
4123 p_generated_flag in varchar2,
4124 p_enabled_flag in varchar2,
4125 p_protected_flag in varchar2,
4126 p_triggering_action in varchar2,
4127 p_last_update_date in date,
4128 p_last_updated_by in number,
4129 p_last_update_login in number,
4130 p_created_by in number,
4131 p_creation_date in date
4132 ) is
4133 begin
4134 insert into pay_trigger_events (
4135 event_id,
4136 table_name,
4137 short_name,
4138 description,
4139 generated_flag,
4140 enabled_flag,
4141 protected_flag,
4142 triggering_action,
4143 last_update_date,
4144 last_updated_by,
4145 last_update_login,
4146 created_by,
4147 creation_date
4148 ) values (
4149 p_event_id,
4150 p_table_name,
4151 p_short_name,
4152 p_description,
4153 p_generated_flag,
4154 p_enabled_flag,
4155 p_protected_flag,
4156 p_triggering_action,
4157 p_last_update_date,
4158 p_last_updated_by,
4159 p_last_update_login,
4160 p_created_by,
4161 p_creation_date
4162 );
4163 end ins;
4164 --
4165 procedure upd(
4166 p_event_id in number,
4167 p_table_name in varchar2,
4168 p_short_name in varchar2,
4169 p_description in varchar2,
4170 p_generated_flag in varchar2,
4171 p_enabled_flag in varchar2,
4172 p_protected_flag in varchar2,
4173 p_triggering_action in varchar2,
4174 p_last_update_date in date,
4175 p_last_updated_by in number,
4176 p_last_update_login in number,
4177 p_created_by in number,
4178 p_creation_date in date
4179 ) is
4180 begin
4181 update pay_trigger_events
4182 set table_name = p_table_name,
4183 short_name = p_short_name,
4184 description = p_description,
4185 generated_flag = p_generated_flag,
4186 enabled_flag = p_enabled_flag,
4187 protected_flag = p_protected_flag,
4188 triggering_action = p_triggering_action,
4189 last_update_date = p_last_update_date,
4190 last_updated_by = p_last_updated_by,
4191 last_update_login = p_last_update_login,
4192 created_by = p_created_by,
4193 creation_date = p_creation_date
4194 where event_id = p_event_id;
4195 end upd;
4196 --
4197 procedure del(
4198 p_event_id in number
4199 ) is
4200 begin
4201 delete from pay_trigger_events
4202 where event_id = p_event_id;
4203 end del;
4204 --
4205 procedure lck(
4206 p_event_id in number
4207 ) is
4208 cursor c_sel1 is
4209 select *
4210 from pay_trigger_events
4211 where event_id = p_event_id
4212 for update nowait;
4213 l_old_rec c_sel1%rowtype;
4214 --
4215 begin
4216 --
4217 open c_sel1;
4218 fetch c_sel1 into l_old_rec;
4219 if c_sel1%notfound then
4220 close c_sel1;
4221 --
4222 -- the primary key is invalid therefore we must error
4223 --
4224 fnd_message.set_name('pay', 'hr_7220_invalid_primary_key');
4225 fnd_message.raise_error;
4226 end if;
4227 close c_sel1;
4228 --
4229 --
4230 -- we need to trap the ora lock exception
4231 --
4232 exception
4233 when hr_api.object_locked then
4234 --
4235 -- the object is locked therefore we need to supply a meaningful
4236 -- error message.
4237 --
4238 fnd_message.set_name('pay', 'hr_7165_object_locked');
4239 fnd_message.set_token('table_name', 'pay_trigger_events');
4240 fnd_message.raise_error;
4241 end lck;
4242 --
4243 FUNCTION is_table_valid(p_table IN VARCHAR2) RETURN VARCHAR2 IS
4244 --
4245 l_rc NUMBER(15);
4246 --
4247 CURSOR csr_chk_tabname IS
4248 SELECT 1
4249 FROM dual
4250 WHERE EXISTS (
4251 SELECT 1
4252 FROM fnd_tables tab
4253 WHERE tab.table_name = p_table
4254 AND (tab.application_id BETWEEN 800 AND 810
4255 OR tab.application_id IN (8301,453,8302,8303,8403,203))
4256 );
4257 --
4258 CURSOR csr_get_tabapp IS
4259 SELECT application_id
4260 FROM fnd_tables
4261 WHERE table_name = p_table
4262 AND ((application_id < 800 OR application_id > 810)
4263 AND application_id NOT IN (8301,453,8302,8303,8403,203));
4264 --
4265 BEGIN
4266 --
4267 OPEN csr_chk_tabname;
4268 FETCH csr_chk_tabname INTO l_rc;
4269 IF csr_chk_tabname%NOTFOUND THEN
4270 CLOSE csr_chk_tabname;
4271 --
4272 -- Not in normal range get the app id and check the profile
4273 OPEN csr_get_tabapp;
4274 FETCH csr_get_tabapp INTO l_rc;
4275 IF csr_get_tabapp%NOTFOUND THEN
4276 CLOSE csr_get_tabapp;
4277 RETURN 'N';
4278 END IF;
4279 CLOSE csr_get_tabapp;
4280 --
4281 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4282 application_id=>l_rc)
4283 THEN
4284 RETURN 'Y';
4285 END IF;
4286 --
4287 RETURN 'N';
4288 END IF;
4289 --
4290 CLOSE csr_chk_tabname;
4291 RETURN 'Y';
4292 END is_table_valid;
4293 --
4294 FUNCTION is_table_column_valid(p_table IN VARCHAR2,p_column IN VARCHAR2) RETURN VARCHAR2 IS
4295 --
4296 l_rc NUMBER(15);
4297 --
4298 CURSOR csr_chk_column IS
4299 SELECT 1
4300 FROM dual
4301 WHERE EXISTS (
4302 SELECT 1
4303 FROM fnd_columns col,fnd_tables tab
4304 WHERE tab.table_name = p_table
4305 AND col.table_id = tab.table_id
4306 AND col.application_id = tab.application_id
4307 AND col.column_name = p_column
4308 AND (tab.application_id BETWEEN 800 AND 810
4309 OR tab.application_id IN (8301,453,8302,8303,8403,203))
4310 );
4311 --
4312 CURSOR csr_get_colapp IS
4313 SELECT tab.application_id
4314 FROM fnd_columns col,fnd_tables tab
4315 WHERE tab.table_name = p_table
4316 AND col.table_id = tab.table_id
4317 AND col.application_id = tab.application_id
4318 AND ((tab.application_id < 800 OR tab.application_id > 810)
4319 AND tab.application_id NOT IN (8301,453,8302,8303,8403,203))
4320 AND col.column_name = p_column;
4321 --
4322 BEGIN
4323 --
4324 OPEN csr_chk_column;
4325 FETCH csr_chk_column INTO l_rc;
4326 IF csr_chk_column%NOTFOUND THEN
4327 CLOSE csr_chk_column;
4328 --
4329 -- Not in normal range get the app id and check the profile
4330 OPEN csr_get_colapp;
4331 FETCH csr_get_colapp INTO l_rc;
4332 IF csr_get_colapp%NOTFOUND THEN
4333 CLOSE csr_get_colapp;
4334 RETURN 'N';
4335 END IF;
4336 CLOSE csr_get_colapp;
4337 --
4338 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4339 application_id=>l_rc)
4340 THEN
4341 RETURN 'Y';
4342 END IF;
4343 --
4344 RETURN 'N';
4345 END IF;
4346 --
4347 CLOSE csr_chk_column;
4348 RETURN 'Y';
4349 END is_table_column_valid;
4350 --
4351 FUNCTION is_table_owner_valid(p_table IN VARCHAR2,p_owner IN VARCHAR2) RETURN VARCHAR2 IS
4352 --
4353 l_rc NUMBER(15);
4354 --
4355 CURSOR csr_chk_owner IS
4356 SELECT 1
4357 FROM dual
4358 WHERE EXISTS (
4359 SELECT 1
4360 FROM fnd_tables tab,
4361 fnd_product_installations prd,
4362 fnd_oracle_userid usr
4363 WHERE tab.table_name = p_table
4364 AND tab.application_id = prd.application_id
4365 AND (prd.application_id BETWEEN 800 AND 810
4366 OR prd.application_id IN (8301,453,8302,8303,8403,203))
4367 AND usr.oracle_id = prd.oracle_id
4368 AND usr.oracle_username = p_owner
4369 );
4370 --
4371 CURSOR csr_get_ownapp IS
4372 SELECT prd.application_id
4373 FROM fnd_tables tab,
4374 fnd_product_installations prd,
4375 fnd_oracle_userid usr
4376 WHERE tab.table_name = p_table
4377 AND tab.application_id = prd.application_id
4378 AND ((prd.application_id < 800 OR prd.application_id > 810)
4379 AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
4380 AND usr.oracle_id = prd.oracle_id
4381 AND usr.oracle_username = p_owner;
4382 --
4383 BEGIN
4384 OPEN csr_chk_owner;
4385 FETCH csr_chk_owner INTO l_rc;
4386 IF csr_chk_owner%NOTFOUND THEN
4387 CLOSE csr_chk_owner;
4388 --
4389 -- Not in normal range, get app id and check profile
4390 OPEN csr_get_ownapp;
4391 FETCH csr_get_ownapp INTO l_rc;
4392 IF csr_get_ownapp%NOTFOUND THEN
4393 CLOSE csr_get_ownapp;
4394 RETURN 'N';
4395 END IF;
4396 CLOSE csr_get_ownapp;
4397 --
4398 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4399 application_id=>l_rc)
4400 THEN
4401 RETURN 'Y';
4402 END IF;
4403 --
4404 RETURN 'N';
4405 --
4406 END IF;
4407 CLOSE csr_chk_owner;
4408 --
4409 RETURN 'Y';
4410 END is_table_owner_valid;
4411 --
4412 FUNCTION get_table_owner(p_table IN VARCHAR2) RETURN VARCHAR2 IS
4413 --
4414 l_schema VARCHAR2(30);
4415 l_app NUMBER(15);
4416 --
4417 CURSOR csr_get_owner IS
4418 SELECT usr.oracle_username
4419 FROM fnd_tables tab,
4420 fnd_product_installations prd,
4421 fnd_oracle_userid usr
4422 WHERE tab.table_name = p_table
4423 AND tab.application_id = prd.application_id
4424 AND (prd.application_id BETWEEN 800 AND 810
4425 OR prd.application_id IN (8301,453,8302,8303,8403,203))
4426 AND usr.oracle_id = prd.oracle_id;
4427 --
4428 CURSOR csr_get_ownex IS
4429 SELECT usr.oracle_username,
4430 prd.application_id
4431 FROM fnd_tables tab,
4432 fnd_product_installations prd,
4433 fnd_oracle_userid usr
4434 WHERE tab.table_name = p_table
4435 AND tab.application_id = prd.application_id
4436 AND ((prd.application_id < 800 OR prd.application_id > 810)
4437 AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
4438 AND usr.oracle_id = prd.oracle_id;
4439 --
4440 BEGIN
4441 OPEN csr_get_owner;
4442 FETCH csr_get_owner INTO l_schema;
4443 IF csr_get_owner%NOTFOUND THEN
4444 CLOSE csr_get_owner;
4445 --
4446 OPEN csr_get_ownex;
4447 FETCH csr_get_ownex INTO l_schema,l_app;
4448 IF csr_get_ownex%NOTFOUND THEN
4449 CLOSE csr_get_ownex;
4450 RETURN NULL;
4451 END IF;
4452 CLOSE csr_get_ownex;
4453 --
4454 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4455 application_id=>l_app)
4456 THEN
4457 RETURN l_schema;
4458 END IF;
4459 RETURN NULL;
4460 END IF;
4461 CLOSE csr_get_owner;
4462 --
4463 RETURN l_schema;
4464 END get_table_owner;
4465 --
4466 end paywsdyg_pkg;
[Home] [Help]