1 PACKAGE BODY paywsdyg_pkg AS
2 -- $Header: pydygpkg.pkb 120.7.12020000.4 2012/12/14 05:27:02 nvankadh 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 like translate(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 -- Bug 15882261 : Modified the cursor definition to refer to dba_tab_columns
1487 -- instead of all_tab_columns
1488
1489 CURSOR get_null(cp_table IN VARCHAR2) IS
1490 SELECT tab.nullable
1491 FROM dba_tab_columns tab, user_synonyms syn
1492 WHERE tab.column_name = 'BUSINESS_GROUP_ID'
1493 AND tab.table_name = syn.table_name
1494 AND tab.owner = syn.table_owner
1495 AND syn.synonym_name = p_table
1496 AND tab.owner = g_pay_schema;
1497
1498
1499 --
1500 l_rc VARCHAR2(1);
1501 BEGIN
1502 --
1503 -- Fetch Schema name if required
1504 --
1505 g_pay_schema := get_table_owner(p_table);
1506 --
1507 -- Fetch column information from the RDBMS data dictionary
1508 OPEN get_null(p_table);
1509 FETCH get_null INTO l_rc;
1510 IF get_null%NOTFOUND THEN
1511 l_rc := 'Y';
1512 END IF;
1513 CLOSE get_null;
1514 --
1515 -- Return the boolean equivalent to the NOT NULL flag
1516 RETURN (l_rc = 'N');
1517 END table_has_business_group;
1518 --
1519 -- +---------------------------------------------------------------------------+
1520 -- | NAME : table_has_payroll |
1521 -- | SCOPE : PUBLIC |
1522 -- | DESCRIPTION: See header |
1523 -- +---------------------------------------------------------------------------+
1524 FUNCTION table_has_payroll(p_table IN VARCHAR2) RETURN BOOLEAN IS
1525 --
1526 -- Cursor to fetch the nullable flag
1527 -- CURSOR get_null(cp_table IN VARCHAR2) IS
1528 -- SELECT atc.nullable
1529 -- FROM all_tab_columns atc
1530 -- WHERE atc.column_name = 'PAYROLL_ID'
1531 -- AND atc.table_name = p_table
1532 -- AND atc.owner = g_pay_schema;
1533
1534
1535 -- Bug 15882261 : Modified the cursor definition to refer to dba_tab_columns
1536 -- instead of all_tab_columns
1537
1538 CURSOR get_null(cp_table IN VARCHAR2) IS
1539 SELECT tab.nullable
1540 FROM dba_tab_columns tab, user_synonyms syn
1541 WHERE tab.column_name = 'PAYROLL_ID'
1542 AND tab.table_name = syn.table_name
1543 AND tab.owner = syn.table_owner
1544 AND syn.synonym_name = p_table
1545 AND tab.owner = g_pay_schema;
1546
1547 --
1548 l_rc VARCHAR2(1);
1549 BEGIN
1550 --
1551 -- Fetch Schema name if required
1552 --
1553 g_pay_schema := get_table_owner(p_table);
1554 --
1555 --
1556 -- Fetch the nullable flag from the data dictionary
1557 OPEN get_null(p_table);
1558 FETCH get_null INTO l_rc;
1559 IF get_null%NOTFOUND THEN
1560 l_rc := 'Y';
1561 END IF;
1562 CLOSE get_null;
1563 --
1564 -- Return the boolean equivalent of the NOT NULL flag
1565 RETURN (l_rc = 'N');
1566 END table_has_payroll;
1567 --
1568 -- +---------------------------------------------------------------------------+
1569 -- | NAME : default_declarations |
1570 -- | SCOPE : PRIVATE |
1571 -- | DESCRIPTION: Create the default declaration definitions that a trigger |
1572 -- | will need if it's created against a table with a business |
1573 -- | group ID or a payroll ID |
1574 -- | PARAMETERS : p_has_bus - Previously derived flag to indicate the presence|
1575 -- | of a manadatory business_group_id in the table |
1576 -- | p_has_pay - Previously derived flag to indicate the presence|
1577 -- | of a manadatory payroll_id column in the table |
1578 -- | p_event_id - The primary key of the event which will own |
1579 -- | this trigger |
1580 -- | RETURNS : None |
1581 -- | RAISES : None |
1582 -- +---------------------------------------------------------------------------+
1583 PROCEDURE default_declarations(
1584 p_has_bus IN BOOLEAN,
1585 p_has_pay IN BOOLEAN,
1586 p_event_id IN NUMBER
1587 ) IS
1588 l_rc NUMBER;
1589 BEGIN
1590 IF p_has_bus THEN
1591 l_rc := insert_declarations(p_event_id,'business_group_id','N');
1592 l_rc := insert_declarations(p_event_id,'legislation_code','C',30);
1593 END IF;
1594 --
1595 IF p_has_pay THEN
1596 l_rc := insert_declarations(p_event_id,'payroll_id','N');
1597 END IF;
1598 END default_declarations;
1599 --
1600 -- +---------------------------------------------------------------------------+
1601 -- | NAME : default_initialisations |
1602 -- | SCOPE : PRIVATE |
1603 -- | DESCRIPTION: Create the default initialisation definitions that a trigger |
1604 -- | will need if it's created against a table with a business |
1605 -- | group ID or a payroll ID |
1606 -- | PARAMETERS : p_has_bus - Previously derived flag to indicate the presence|
1607 -- | of a manadatory business_group_id in the table |
1608 -- | p_has_pay - Previously derived flag to indicate the presence|
1609 -- | of a manadatory payroll_id column in the table |
1610 -- | p_event_id - The primary key of the event which will own |
1611 -- | this trigger |
1612 -- | p_type - The type of trigger, I, U or D for Insert, |
1613 -- | Update or Delete |
1614 -- | p_bus_id - The primary key of the newly created business |
1615 -- | group id initialisation record |
1616 -- | p_leg_id - Primary key of new legislation ID initialisation|
1617 -- | record |
1618 -- | p_pay_id - Primary key of new payroll ID initialisation |
1619 -- | record |
1620 -- | RETURNS : The primary keys of the newly created initialisation IDs, |
1621 -- | via OUT parameters, if there are any, otherwise these values |
1622 -- | will be NULL |
1623 -- | RAISES : None |
1624 -- +---------------------------------------------------------------------------+
1625 PROCEDURE default_initialisations(
1626 p_has_bus IN BOOLEAN,
1627 p_has_pay IN BOOLEAN,
1628 p_event_id IN NUMBER,
1629 p_type IN VARCHAR2,
1630 p_bus_id OUT NOCOPY NUMBER,
1631 p_leg_id OUT NOCOPY NUMBER,
1632 p_pay_id OUT NOCOPY NUMBER
1633 ) IS
1634 --
1635 l_age VARCHAR2(10);
1636 --
1637 BEGIN
1638 --
1639 -- Use the :old or :new record depending on the trigger type
1640 IF p_type = 'D' THEN
1641 l_age := 'OLD';
1642 ELSE
1643 l_age := 'NEW';
1644 END IF;
1645 --
1646 -- Create the business_group_id and legislation_code initialisations if
1647 -- the table has a mandatory business group ID column
1648 IF p_has_bus THEN
1649 p_bus_id := insert_initialisations(
1650 p_event_id,
1651 -20,
1652 '$'||l_age||'_BUSINESS_GROUP_ID$',
1653 'A'
1654 );
1655 p_leg_id := insert_initialisations(
1656 p_event_id,
1657 -10,
1658 'SELECT legislation_code '||
1659 'FROM per_business_groups '||
1660 'WHERE business_group_id = $L_BUSINESS_GROUP_ID$',
1661 'S'
1662 );
1663 END IF;
1664 --
1665 -- Create the payroll_id initialisation if the table has a manadatory
1666 -- payroll_id column
1667 IF p_has_pay THEN
1668 p_pay_id := insert_initialisations(
1669 p_event_id,
1670 -30,
1671 '$'||l_age||'_PAYROLL_ID$',
1672 'A'
1673 );
1674 END IF;
1675 --
1676 END default_initialisations;
1677 --
1678 -- +---------------------------------------------------------------------------+
1679 -- | NAME : default_parameters |
1680 -- | SCOPE : PRIVATE |
1681 -- | DESCRIPTION: Create the parameter mappings for the default modules |
1682 -- | belonging to an event (initialisations and components) |
1683 -- | PARAMETERS : p_event_id - The primary key of the event which will own |
1684 -- | the trigger that needs these parameters |
1685 -- | RETURNS : None |
1686 -- | RAISES : None |
1687 -- +---------------------------------------------------------------------------+
1688 PROCEDURE default_parameters(
1689 p_event_id IN NUMBER
1690 ) IS
1691 BEGIN
1692 --
1693 -- Simply call the auto-mapper for all modules in this event, nothing
1694 -- apart from the defaults should have been created at this point
1695 -- Currently no default components are created so just call the automapper
1696 -- for initialisations
1697 automap_parameters(p_event_id,'I',NULL);
1698 END default_parameters;
1699 --
1700 -- +---------------------------------------------------------------------------+
1701 -- | NAME : flag_to_boolean |
1702 -- | SCOPE : PRIVATE |
1703 -- | DESCRIPTION: Simple helper function to convert a Y/N flag to a boolean |
1704 -- | PARAMETERS : p_flag - The text flag, should be Y or N |
1705 -- | RETURNS : TRUE if the flag is 'Y', FALSE otherwise |
1706 -- | RAISES : None |
1707 -- +---------------------------------------------------------------------------+
1708 FUNCTION flag_to_boolean(p_flag IN VARCHAR2) RETURN BOOLEAN IS
1709 BEGIN
1710 -- Could do; IF p_flag = 'Y' THEN... But I think this way's neater
1711 RETURN (NVL(p_flag,'N') = 'Y');
1712 END flag_to_boolean;
1713 --
1714 -- +---------------------------------------------------------------------------+
1715 -- | NAME : generate_trigger |
1716 -- | SCOPE : PUBLIC |
1717 -- | DESCRIPTION: See header |
1718 -- +---------------------------------------------------------------------------+
1719 PROCEDURE generate_trigger(
1720 p_id IN NUMBER,
1721 p_name IN OUT NOCOPY VARCHAR2,
1722 p_ok OUT NOCOPY BOOLEAN
1723 ) IS
1724 --
1725 -- Cursor to get the information about the trigger from the database
1726 CURSOR csr_name(cp_id IN NUMBER) IS
1727 SELECT pte.table_name, pte.triggering_action,
1728 pte.generated_flag, pte.enabled_flag,
1729 pdt.dated_table_id, nvl(pdt.dyn_trigger_type,'T'),
1730 pdt.dyn_trigger_package_name
1731 FROM pay_dated_tables pdt,
1732 pay_trigger_events pte
1733 WHERE pte.table_name = pdt.table_name(+)
1734 AND pte.event_id = cp_id;
1735 --
1736 l_table VARCHAR2(30);
1737 l_trigger VARCHAR2(30);
1738 l_action VARCHAR2(1);
1739 l_gen_flag VARCHAR2(1);
1740 l_enb_flag VARCHAR2(1);
1741 l_tab_id NUMBER;
1742 l_dyn_type VARCHAR2(1);
1743 l_dyt_pkg VARCHAR2(80);
1744 --
1745 l_sql VARCHAR2(32767);
1746 l_rc NUMBER;
1747 --
1748 l_proc VARCHAR2(30) := 'generate_trigger';
1749 BEGIN
1750 hr_utility.set_location(' Entering: '||l_proc,10);
1751 --Calling from SINGLE (Dynamic Triggers Form)
1752 --
1753 -- Fetch the trigger event information
1754 OPEN csr_name(p_id);
1755 FETCH csr_name INTO l_table,l_action,
1756 l_gen_flag,l_enb_flag,
1757 l_tab_id,l_dyn_type,l_dyt_pkg;
1758 CLOSE csr_name;
1759
1760 -- TEST TO SEE HOW USER WISHES TO HANDLE DYN TRIGGERS
1761 -- Eg Set up to store as db triggers, as package code or both
1762 -- Added by jford 1-OCT-02 as part of cont calc
1763 --
1764 IF (l_dyn_type = 'P' or l_dyn_type = 'B') THEN
1765 hr_utility.trace(' Handle Dynamic Triggers as Package Style.');
1766 -- dyn trigger code should be handled as package
1767 -- >> GENERATE PACKAGE
1768 -- generate code FOR ALL DYT's ON TABLE eg many dyn_triggers
1769 gen_dyt_pkg_full_code(l_tab_id,p_ok);
1770 p_name := l_dyt_pkg;
1771 ELSE
1772 -- type is just original 'T' Trigger mechanism so use existing code
1773 -- >> GENERATE DBMS TRIGGER
1774 hr_utility.trace(' Handle Dynamic Triggers as Individual Database Trigger Style.');
1775 -- Get the trigger name in the standard format
1776 --if weve got an old crap dyt_pkg, then dump it
1777 if (l_dyt_pkg is not null) then
1778 drop_package(l_table,l_dyt_pkg);
1779 update pay_dated_tables set dyn_trigger_package_name = null
1780 where table_name = l_dyt_pkg and dated_table_id = l_tab_id;
1781 end if;
1782 l_trigger := get_trigger_name(p_id,l_table,l_action);
1783 p_name := l_trigger;
1784 --
1785 -- If we should be generating the trigger then lets do it
1786 IF flag_to_boolean(l_gen_flag) THEN
1787 --
1788 -- Generate the PL/SQL block that the trigger will use
1789 generate_code(p_id,l_sql);
1790 --
1791 -- Create the trigger using the generated PL/SQL and the AOL routines
1792 create_trigger(l_trigger,l_table,l_action,l_sql);
1793 p_ok := module_ok(l_trigger,'TRIGGER');
1794 if (p_ok) then
1795 hr_utility.trace(' Database Trigger '||p_name||' created with success');
1796 else
1797 hr_utility.trace(' Database Trigger '||p_name||' created with failure');
1798 update pay_trigger_events
1799 set generated_flag = 'N', enabled_flag = 'N'
1800 where event_id = p_id;
1801 l_enb_flag := 'N';
1802 end if;
1803 --
1804 -- Enable the new trigger as required
1805 enable_trigger(l_trigger,flag_to_boolean(l_enb_flag));
1806 --
1807 ELSE
1808 --
1809 -- Otherwise, drop it to make sure it definitely doesn't exist
1810 drop_trigger(l_trigger);
1811 p_ok := TRUE;
1812 hr_utility.trace(' Database Trigger '||p_name||' dropped with success.');
1813 --
1814 END IF;
1815 END IF;
1816
1817 hr_utility.set_location(' Leaving: '||l_proc,900);
1818 END generate_trigger;
1819 --
1820 -- +---------------------------------------------------------------------------+
1821 -- | NAME : compile_package_indirect |
1822 -- | SCOPE : PUBLIC |
1823 -- | DESCRIPTION: See header |
1824 -- +---------------------------------------------------------------------------+
1825 PROCEDURE compile_package_indirect(p_id IN NUMBER) IS
1826 --
1827 -- Cursor to get all we need to know about the event, since we only get
1828 -- passed the primary key of the support package
1829 CURSOR get_info(cp_id IN NUMBER) IS
1830 SELECT pte.event_id,
1831 pte.table_name,
1832 pts.header_code,
1833 pts.body_code
1834 FROM pay_trigger_events pte,
1835 pay_trigger_support pts
1836 WHERE pte.event_id = pts.event_id
1837 AND pts.support_id = cp_id;
1838 --
1839 l_id NUMBER;
1840 l_name VARCHAR2(30);
1841 l_pname VARCHAR2(30);
1842 l_head_ok BOOLEAN;
1843 l_body_ok BOOLEAN;
1844 l_head CLOB;
1845 l_body CLOB;
1846 BEGIN
1847 --
1848 -- Fetch the information we need
1849 OPEN get_info(p_id);
1850 FETCH get_info INTO l_id,l_name,l_head,l_body;
1851 CLOSE get_info;
1852 --
1853 -- Compile the package using the direct method, passing the info we found
1854 compile_package(
1855 l_id,
1856 l_name,
1857 lob_to_varchar2(l_head),
1858 lob_to_varchar2(l_body),
1859 l_pname,
1860 l_head_ok,
1861 l_body_ok
1862 );
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 -- Output a trrace message if we fail
1866 hr_utility.set_location('COMPILE_PACKAGE_INDIRECT',ABS(SQLCODE));
1867 END compile_package_indirect;
1868 --
1869 -- +---------------------------------------------------------------------------+
1870 -- | NAME : create_defaults |
1871 -- | SCOPE : PUBLIC |
1872 -- | DESCRIPTION: See header |
1873 -- +---------------------------------------------------------------------------+
1874 PROCEDURE create_defaults(p_id IN NUMBER) IS
1875 --
1876 -- Fetch to get some more information that we need
1877 CURSOR csr_name(cp_id IN NUMBER) IS
1878 SELECT pte.table_name,
1879 pte.triggering_action
1880 FROM pay_trigger_events pte
1881 WHERE pte.event_id = cp_id;
1882 --
1883 l_table VARCHAR2(30);
1884 l_action VARCHAR2(1);
1885 l_bus_id NUMBER;
1886 l_leg_id NUMBER;
1887 l_pay_id NUMBER;
1888 l_rc NUMBER;
1889 l_has_bus BOOLEAN;
1890 l_has_pay BOOLEAN;
1891 --
1892 BEGIN
1893 --
1894 -- Get the additional information
1895 OPEN csr_name(p_id);
1896 FETCH csr_name INTO l_table,l_action;
1897 CLOSE csr_name;
1898 --
1899 -- See if the table's got a business group ID and/or a payroll ID
1900 l_has_bus := table_has_business_group(l_table);
1901 l_has_pay := table_has_payroll(l_table);
1902 --
1903 -- Create the default declarations and initialisations that we need for
1904 -- a business group or payroll context trigger, then call the automapper
1905 -- to create the necessary parameter mappings for these initialisations
1906 default_declarations(l_has_bus,l_has_pay,p_id);
1907 default_initialisations(
1908 l_has_bus,
1909 l_has_pay,
1910 p_id,
1911 l_action,
1912 l_bus_id,
1913 l_leg_id,
1914 l_pay_id
1915 );
1916 default_parameters(p_id);
1917 --
1918 -- Insert some dummy code for the support package to give the user a hint
1919 -- then compile it (for what it's worth :-)
1920 l_rc := insert_support(
1921 p_id,
1922 '/* Add your support package header code here */',
1923 '/* Add your support package body code here */'
1924 );
1925 compile_package_indirect(l_rc);
1926 --
1927 END create_defaults;
1928 --
1929 -- +---------------------------------------------------------------------------+
1930 -- | NAME : add_declarations |
1931 -- | SCOPE : PRIVATE |
1932 -- | DESCRIPTION: Add the declaration section to the PL/SQL code |
1933 -- | PARAMETERS : p_id - The primary key value of the event that the code is |
1934 -- | being generated for |
1935 -- | p_sql - The current PL/SQL code that the declaration section |
1936 -- | will be added to |
1937 -- | RETURNS : The PL/SQL code with the declaration section added, via the |
1938 -- | IN/OUT parameter |
1939 -- | RAISES : None |
1940 -- +---------------------------------------------------------------------------+
1941 PROCEDURE add_declarations(p_id IN NUMBER,p_sql IN OUT NOCOPY VARCHAR2) IS
1942 --
1943 -- Get all the declarations defined for this event, decoding the type code
1944 CURSOR get_decls(cp_id IN NUMBER) IS
1945 SELECT 'l_'||variable_name variable_name,
1946 DECODE(data_type,
1947 'C','VARCHAR2',
1948 'D','DATE',
1949 'N','NUMBER'
1950 ) data_type,
1951 data_type data_type_code,
1952 variable_size
1953 FROM pay_trigger_declarations
1954 WHERE event_id = cp_id;
1955 --
1956 BEGIN
1957 --JFORD 15-SEP-03 Due to data inconsistencies the owness is now on the
1958 --calling code to be responsible for adding the DECLARE keyword
1959 --
1960 -- Process all the declarations
1961 FOR l_rec IN get_decls(p_id) LOOP
1962 --
1963 -- Add the variable declaration, padding it out with spaces to make the
1964 -- formatting look nice.
1965 p_sql := p_sql||' '||RPAD(l_rec.variable_name,30)||' '||l_rec.data_type;
1966 --
1967 IF l_rec.data_type_code = 'C' THEN
1968 p_sql := p_sql||'('||LTRIM(RTRIM(TO_CHAR(l_rec.variable_size)))||')';
1969 END IF;
1970 p_sql := p_sql||';'||g_eol;
1971 END LOOP;
1972 END add_declarations;
1973 --
1974 -- +---------------------------------------------------------------------------+
1975 -- | NAME : add_initialisations |
1976 -- | SCOPE : PRIVATE |
1977 -- | DESCRIPTION: Add the PL/SQL code that will initialise the declarations |
1978 -- | PARAMETERS : p_id - The primary key of the event that we are |
1979 -- | generating PL/SQL code for |
1980 -- | p_sql - The PL/SQL code that will have the initialisation |
1981 -- | code added to it |
1982 -- | p_table - The table that this trigger will get created on, |
1983 -- | needed to work out which bits of information we |
1984 -- | can send to the functional area checking routine |
1985 -- | RETURNS : The modified PL/SQL code with the initialisation section |
1986 -- | added via the IN OUT parameter and a flag indicating if any |
1987 -- | initialisations were written (not sure this is good practice)|
1988 -- | RAISES : None |
1989 -- +---------------------------------------------------------------------------+
1990 FUNCTION add_initialisations(
1991 p_id IN NUMBER,
1992 p_sql IN OUT NOCOPY VARCHAR2,
1993 p_table IN VARCHAR2
1994 ) RETURN BOOLEAN IS
1995 --
1996 -- Cursor to fetch all the initialisations that this event uses
1997 CURSOR get_inits(cp_id IN NUMBER) IS
1998 SELECT initialisation_id,
1999 plsql_code,
2000 process_type,
2001 process_order
2002 FROM pay_trigger_initialisations
2003 WHERE event_id = cp_id
2004 ORDER BY process_order;
2005 --
2006 -- Get the name of the variable that the return value of a function or
2007 -- assignment will be returned into
2008 CURSOR get_outs(cp_id IN NUMBER) IS
2009 SELECT parameter_name,
2010 value_name
2011 FROM pay_trigger_parameters
2012 WHERE parameter_type = 'R'
2013 AND usage_id = cp_id
2014 AND usage_type = 'I'
2015 ORDER BY parameter_id;
2016 --
2017 -- Get the mappings for the parameter list of a function or procedure
2018 CURSOR get_parms(cp_id IN NUMBER) IS
2019 SELECT parameter_name,
2020 value_name
2021 FROM pay_trigger_parameters
2022 WHERE parameter_type IN ('I','O')
2023 AND value_name IS NOT NULL
2024 AND usage_id = cp_id
2025 AND usage_type = 'I'
2026 ORDER BY parameter_id;
2027 --
2028 l_inits BOOLEAN := FALSE;
2029 l_fgt BOOLEAN := FALSE;
2030 --
2031 l_par VARCHAR2(30);
2032 l_val VARCHAR2(35);
2033 l_plsql VARCHAR2(32767);
2034 l_sel VARCHAR2(32767);
2035 l_int VARCHAR2(32767);
2036 l_cnt NUMBER;
2037 --
2038 l_bus BOOLEAN := NOT no_business_context(p_table,p_id);
2039 l_pay BOOLEAN := NOT no_payroll_context(p_table,p_id);
2040 --
2041 BEGIN
2042 --
2043 -- Add an explanatory comment
2044 p_sql := p_sql||' /* Initialising local variables */'||g_eol;
2045 --
2046 -- Process all the initialisations
2047 FOR l_rec IN get_inits(p_id) LOOP
2048 l_inits := TRUE;
2049 --
2050 -- If the initialisation is an assignment or a function then
2051 -- indent, add the return variable and the assignment operator
2052 IF l_rec.process_type IN ('A','F') THEN
2053 OPEN get_outs(l_rec.initialisation_id);
2054 FETCH get_outs INTO l_par,l_val;
2055 CLOSE get_outs;
2056 --
2057 p_sql := p_sql||' '||l_val||' := ';
2058 --
2059 -- Just indent if the initialisation is a procedure
2060 ELSIF l_rec.process_type IN ('P') THEN
2061 p_sql := p_sql||' ';
2062 END IF;
2063 --
2064 -- Fetch the actual code into a local variable we can modify
2065 l_plsql := l_rec.plsql_code;
2066 --
2067 -- If it's a SQL select or an assignment then we need to replace any
2068 -- $<TYPE>_<NAME>$ placeholders with the variables that they represent
2069 IF l_rec.process_type IN ('S','A') THEN
2070 replace_placeholders(l_plsql,l_rec.initialisation_id,NULL);
2071 END IF;
2072 --
2073 -- If it's an assignment, function or procedure then add the code
2074 -- from the initialisation definition
2075 IF l_rec.process_type IN ('A','F','P') THEN
2076 p_sql := p_sql||l_plsql;
2077 --
2078 -- If it's an assignment then that's all we need to do so add the
2079 -- end of line terminator
2080 IF l_rec.process_type = 'A' THEN
2081 p_sql := p_sql||';'||g_eol;
2082 END IF;
2083 END IF;
2084 --
2085 -- If it's a SQL select statement then...
2086 IF l_rec.process_type = 'S' THEN
2087 --
2088 -- Initialise the select and into list
2089 l_sel := '';
2090 l_int := '';
2091 --
2092 -- Loop round all the output mappings for this initialisation
2093 FOR l_inner IN get_outs(l_rec.initialisation_id) LOOP
2094 --
2095 -- If it's not the first mapping then add the correct padding
2096 IF get_outs%ROWCOUNT > 1 THEN
2097 l_sel := l_sel||', '||g_eol||' ';
2098 l_int := l_int||', '||g_eol||' ';
2099 END IF;
2100 --
2101 -- Add the parameter name to the select list and the return
2102 -- variable to the 'into' clause
2103 l_sel := l_sel||l_inner.parameter_name;
2104 l_int := l_int||l_inner.value_name;
2105 END LOOP;
2106 --
2107 -- Concatenate the select list, into list and the remainder of the
2108 -- select statement (the FROM clause onwards) to the PL/SQL code
2109 -- N.B. This code relies on the SQL select only bringing back 1
2110 -- record, otherwise it will fail at runtime, could be changed to
2111 -- generate a cursor, but that's quite a big change
2112 p_sql := p_sql||' SELECT '||l_sel||g_eol||' INTO '||l_int||g_eol;
2113 p_sql := p_sql||' '||
2114 SUBSTR(l_plsql,INSTR(UPPER(l_plsql),' FROM '))||'; '||g_eol;
2115 END IF;
2116 --
2117 -- If the initialisation's a function or a procedure then...
2118 IF l_rec.process_type IN ('F','P') THEN
2119 --
2120 -- Get the parameter mappings for the function or procedure
2121 l_cnt := 0;
2122 FOR l_inner IN get_parms(l_rec.initialisation_id) LOOP
2123 --
2124 -- Process the first/not first parameter in the list in
2125 -- different ways so that the code is formatted correctly
2126 IF get_parms%ROWCOUNT > 1 THEN
2127 p_sql := p_sql||','||g_eol;
2128 ELSE
2129 p_sql := p_sql||'('||g_eol;
2130 END IF;
2131 --
2132 -- Add the parameter to the list in full 'parameter => value' notation
2133 -- to allow the user to omit parameters from the call
2134 p_sql := p_sql||' '||
2135 RPAD(l_inner.parameter_name,30)||' => '||l_inner.value_name;
2136 l_cnt := l_cnt + 1;
2137 END LOOP;
2138 --
2139 -- Finish off the statement by closing the parameter list bracket
2140 IF l_cnt > 0 THEN
2141 p_sql := p_sql||g_eol||' ); '||g_eol;
2142 END IF;
2143 END IF;
2144 --
2145 -- Add a final comment line to delimit the initialisation block
2146 p_sql := p_sql||' --'||g_eol;
2147 END LOOP;
2148 --
2149 -- Always write the Functional Grouping of Triggers check
2150 -- This could be made more efficient by placing it just after the required
2151 -- variables (legislation, business group and payroll) have been initialised
2152 -- so that any other unnecessary inititialisations aren't carried out if the
2153 -- trigger isn't supposed to be firing
2154 IF NOT l_fgt THEN
2155 paywsfgt_pkg.write_fgt_check(p_id,p_sql,l_bus,l_pay);
2156 END IF;
2157 --
2158 -- Let the caller know if we wrote any initialisations or not
2159 RETURN l_inits;
2160 END add_initialisations;
2161 --
2162 -- +---------------------------------------------------------------------------+
2163 -- | NAME : write_parameters |
2164 -- | SCOPE : PRIVATE |
2165 -- | DESCRIPTION: Add the parameter list for a component procedure call to the |
2166 -- | specified PL/SQL text |
2167 -- | PARAMETERS : p_id - The primary key of the component being processed |
2168 -- | p_sql - The current PL/SQL code that the parameter list |
2169 -- | will be added to |
2170 -- | p_extra - Any extra text to add before the parameter list |
2171 -- | item, used to indent the line correctly |
2172 -- | RETURNS : The modified PL/SQL code via the OUT parameter |
2173 -- | RAISES : None |
2174 -- +---------------------------------------------------------------------------+
2175 PROCEDURE write_parameters(
2176 p_id IN NUMBER,
2177 p_sql IN OUT NOCOPY VARCHAR2,
2178 p_extra IN VARCHAR2
2179 ) IS
2180 --
2181 -- Cursor to fetch the parameter mappings for the specified module
2182 CURSOR get_parms(cp_id IN NUMBER) IS
2183 SELECT parameter_name,
2184 value_name
2185 FROM pay_trigger_parameters
2186 WHERE parameter_type IN ('I','O')
2187 AND value_name IS NOT NULL
2188 AND usage_id = cp_id
2189 AND usage_type = 'C'
2190 ORDER BY parameter_id;
2191 --
2192 l_num NUMBER := 0;
2193 --
2194 BEGIN
2195 --
2196 -- Process each parameter in turn
2197 FOR l_rec IN get_parms(p_id) LOOP
2198 l_num := l_num + 1;
2199 --
2200 -- Open the bracket for the first row, otherwise add a comma
2201 IF get_parms%ROWCOUNT > 1 THEN
2202 p_sql := p_sql||','||g_eol;
2203 ELSE
2204 p_sql := p_sql||'('||g_eol;
2205 END IF;
2206 --
2207 -- Add the parameter mapping in full 'parameter => value' notation
2208 p_sql := p_sql||' '||p_extra||
2209 RPAD(l_rec.parameter_name,30)||' => '||l_rec.value_name;
2210 END LOOP;
2211 --
2212 -- Add a closing bracket, only if one or more parameters was written
2213 IF l_num > 0 THEN
2214 p_sql := p_sql||g_eol||p_extra||')';
2215 END IF;
2216 END write_parameters;
2217 --
2218 -- +---------------------------------------------------------------------------+
2219 -- | NAME : add_components |
2220 -- | SCOPE : PRIVATE |
2221 -- | DESCRIPTION: Add the components (the procedure calls that actually do some|
2222 -- | work) to the PL/SQL code |
2223 -- | PARAMETERS : p_id - The primary key of the event we're processing |
2224 -- | p_sql - The current PL/SQL block that the component calls |
2225 -- | will get added to |
2226 -- | RETURNS : The modified PL/SQL block via the OUT parameter and a flag |
2227 -- | indicating if any components were written (not sure this is |
2228 -- | good programming practice) |
2229 -- | RAISES : None |
2230 -- +---------------------------------------------------------------------------+
2231 FUNCTION add_components(
2232 p_id IN NUMBER,
2233 p_sql IN OUT NOCOPY VARCHAR2
2234 ) RETURN BOOLEAN IS
2235 --
2236 -- Get global component calls, ones that always get called, i.e. they
2237 -- aren't specific to any legislation, business group or payroll
2238 CURSOR get_globals(cp_id IN NUMBER) IS
2239 SELECT component_id,
2240 module_name
2241 FROM pay_trigger_components
2242 WHERE legislation_code IS NULL
2243 AND business_group_id IS NULL
2244 AND payroll_id IS NULL
2245 AND enabled_flag = 'Y'
2246 AND event_id = cp_id;
2247 --
2248 -- Legislation specific components, only get called if the legislation
2249 -- code of the current record matches the component definition
2250 CURSOR get_legs(cp_id IN NUMBER) IS
2251 SELECT component_id,
2252 legislation_code,
2253 module_name
2254 FROM pay_trigger_components
2255 WHERE legislation_code IS NOT NULL
2256 AND payroll_id IS NULL
2257 AND business_group_id IS NULL
2258 AND enabled_flag = 'Y'
2259 AND event_id = cp_id
2260 ORDER BY legislation_code;
2261 --
2262 -- Business group specific components, only get called if the business
2263 -- group of the current record matches the component definition
2264 CURSOR get_buss(cp_id IN NUMBER) IS
2265 SELECT component_id,
2266 business_group_id,
2267 module_name
2268 FROM pay_trigger_components
2269 WHERE legislation_code IS NULL
2270 AND payroll_id IS NULL
2271 AND business_group_id IS NOT NULL
2272 AND enabled_flag = 'Y'
2273 AND event_id = cp_id
2274 ORDER BY business_group_id;
2275 --
2276 -- Payroll specific components, only get called if the payroll
2277 -- id of the current record matches that of the component definition
2278 CURSOR get_pays(cp_id IN NUMBER) IS
2279 SELECT component_id,
2280 payroll_id,
2281 module_name
2282 FROM pay_trigger_components
2283 WHERE legislation_code IS NULL
2284 AND business_group_id IS NULL
2285 AND payroll_id IS NOT NULL
2286 AND enabled_flag = 'Y'
2287 AND event_id = cp_id
2288 ORDER BY payroll_id;
2289 --
2290 l_comps BOOLEAN := FALSE;
2291 l_ifs NUMBER := 0;
2292 --
2293 -- Remember the last code we wrote so we know when to end/start IF blocks
2294 l_oldleg VARCHAR2(30) := '$NO_LEGISLATION_CODE$';
2295 l_oldbus NUMBER := -65536;
2296 l_oldpay NUMBER := -65536;
2297 --
2298 BEGIN
2299 --
2300 -- Fetch and write details of all the global components
2301 p_sql := p_sql||' /* Global component calls */'||g_eol;
2302 FOR l_rec IN get_globals(p_id) LOOP
2303 l_comps := TRUE;
2304 --
2305 p_sql := p_sql||' '||l_rec.module_name;
2306 write_parameters(l_rec.component_id,p_sql,' ');
2307 p_sql := p_sql||';'||g_eol;
2308 END LOOP;
2309 p_sql := p_sql||' --'||g_eol;
2310 --
2311 -- Fetch and write details of legislation specific components
2312 p_sql := p_sql||' /* Legislation specific component calls */'||g_eol;
2313 l_ifs := 0;
2314 FOR l_rec IN get_legs(p_id) LOOP
2315 l_comps := TRUE;
2316 --
2317 -- If the legislation code has changed then add a new IF clause
2318 IF l_rec.legislation_code <> l_oldleg THEN
2319 IF get_legs%ROWCOUNT > 1 THEN
2320 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2321 l_ifs := l_ifs - 1;
2322 END IF;
2323 p_sql := p_sql||' IF l_legislation_code = '''||
2324 l_rec.legislation_code||''' THEN'||g_eol;
2325 l_ifs := l_ifs + 1;
2326 --
2327 l_oldleg := l_rec.legislation_code;
2328 END IF;
2329 --
2330 -- Write the component call information
2331 p_sql := p_sql||' '||l_rec.module_name;
2332 write_parameters(l_rec.component_id,p_sql,' ');
2333 p_sql := p_sql||';'||g_eol;
2334 END LOOP;
2335 --
2336 -- Close the final IF clause (if we wrote any)
2337 IF l_ifs > 0 THEN
2338 p_sql := p_sql||' END IF; '||g_eol;
2339 END IF;
2340 p_sql := p_sql||' --'||g_eol;
2341 --
2342 -- Fetch and write details of legislation specific components
2343 p_sql := p_sql||' /* Business group specific component calls */'||g_eol;
2344 l_ifs := 0;
2345 FOR l_rec IN get_buss(p_id) LOOP
2346 l_comps := TRUE;
2347 --
2348 -- Write a new IF clause if the business group changed
2349 IF l_rec.business_group_id <> l_oldbus THEN
2350 IF get_buss%ROWCOUNT > 1 THEN
2351 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2352 l_ifs := l_ifs - 1;
2353 END IF;
2354 p_sql := p_sql||' IF l_business_group_id = '||
2355 TO_CHAR(l_rec.business_group_id)||' THEN'||g_eol;
2356 l_ifs := l_ifs + 1;
2357 --
2358 l_oldbus := l_rec.business_group_id;
2359 END IF;
2360 --
2361 -- Write the component call details
2362 p_sql := p_sql||' '||l_rec.module_name;
2363 write_parameters(l_rec.component_id,p_sql,' ');
2364 p_sql := p_sql||';'||g_eol;
2365 END LOOP;
2366 --
2367 -- Close the final IF statement if needed
2368 IF l_ifs > 0 THEN
2369 p_sql := p_sql||' END IF; '||g_eol;
2370 END IF;
2371 p_sql := p_sql||' --'||g_eol;
2372 --
2373 -- Write payroll specific component calls
2374 p_sql := p_sql||' /* Payroll specific component calls */'||g_eol;
2375 l_ifs := 0;
2376 FOR l_rec IN get_pays(p_id) LOOP
2377 l_comps := TRUE;
2378 --
2379 -- Add a new IF clause if the payroll ID changes
2380 IF l_rec.payroll_id <> l_oldpay THEN
2381 IF get_pays%ROWCOUNT > 1 THEN
2382 p_sql := p_sql||' END IF; '||g_eol||' --'||g_eol;
2383 l_ifs := l_ifs - 1;
2384 END IF;
2385 p_sql := p_sql||' IF l_payroll_id = '||
2386 TO_CHAR(l_rec.payroll_id)||' THEN'||g_eol;
2387 l_ifs := l_ifs + 1;
2388 --
2389 l_oldpay := l_rec.payroll_id;
2390 END IF;
2391 --
2392 -- Write the component module call
2393 p_sql := p_sql||' '||l_rec.module_name;
2394 write_parameters(l_rec.component_id,p_sql,' ');
2395 p_sql := p_sql||';'||g_eol;
2396 END LOOP;
2397 --
2398 -- Close the final IF if required
2399 IF l_ifs > 0 THEN
2400 p_sql := p_sql||' END IF; '||g_eol;
2401 END IF;
2402 p_sql := p_sql||' --'||g_eol;
2403 --
2404 -- Let the caller know whether or not we wrote and components
2405 RETURN l_comps;
2406 END add_components;
2407 -- +---------------------------------------------------------------------------+
2408 -- | NAME : generate_code |
2409 -- | SCOPE : PUBLIC |
2410 -- | DESCRIPTION: See header |
2411 -- +---------------------------------------------------------------------------+
2412 PROCEDURE generate_code(p_id IN NUMBER,p_sql IN OUT NOCOPY VARCHAR2) IS
2413 --
2414 l_inits BOOLEAN;
2415 l_comps BOOLEAN;
2416 l_table pay_trigger_events.table_name%TYPE;
2417 l_name pay_trigger_events.short_name%TYPE;
2418 l_type pay_trigger_events.triggering_action%TYPE;
2419 l_desc VARCHAR2(80);
2420 l_info pay_trigger_events.description%TYPE;
2421
2422 --
2423 -- Get some extra information about the event we're generating code for
2424 CURSOR csr_name(cp_id IN NUMBER) IS
2425 SELECT pte.table_name,
2426 pte.short_name,
2427 pte.triggering_action,
2428 DECODE(pte.triggering_action,
2429 'I','Insert',
2430 'U','Update',
2431 'D','Delete'
2432 ),
2433 pte.description
2434 FROM pay_trigger_events pte
2435 WHERE pte.event_id = cp_id;
2436 --
2437 BEGIN
2438 --
2439 -- Fetch the additional information we need before we can generate
2440 OPEN csr_name(p_id);
2441 FETCH csr_name INTO l_table,l_name,l_type,l_desc,l_info;
2442 CLOSE csr_name;
2443 --
2444
2445 -- Initialise the statement PL/SQL code
2446 p_sql := '';
2447 --
2448
2449 -- Add DECLARE before calling add_declarations
2450 p_sql := p_sql||'DECLARE '||g_eol;
2451 p_sql := p_sql||' /* Local variable declarations */'||g_eol;
2452
2453 -- Add the seeded declaration section
2454 add_declarations(p_id,p_sql);
2455
2456 -- Add any hard-coded declarations
2457 p_sql := p_sql||' l_mode varchar2(80);'||g_eos;
2458 --
2459 -- Add an initial comment section to the trigger code
2460 p_sql := p_sql||'BEGIN'||g_eol;
2461 p_sql := p_sql||'/*'||g_eol;
2462 p_sql := p_sql||' ================================================'||g_eol;
2463 p_sql := p_sql||' This is a dynamically generated database trigger'||g_eol;
2464 p_sql := p_sql||' ================================================'||g_eol;
2465 p_sql := p_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
2466 p_sql := p_sql||' ------------------------------------------------'||g_eol;
2467 p_sql := p_sql||' Table: '||l_table||g_eol;
2468 p_sql := p_sql||' Action: '||l_desc||g_eol;
2469 p_sql := p_sql||' Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
2470 p_sql := p_sql||' Name: '||l_name||g_eol;
2471 p_sql := p_sql||' Info.: '||l_info||g_eol;
2472 p_sql := p_sql||' ================================================'||g_eol;
2473 p_sql := p_sql||'*/'||g_eol||'--'||g_eol;
2474 --
2475
2476 --Add our system to let future processes know whether this is existing or new dyt
2477 p_sql := p_sql||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
2478 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := pay_dyn_triggers.g_dbms_dyt;'||g_eol;
2479
2480 -- Add the data migrator check - Bug 1885557
2481 p_sql := p_sql||'IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
2482 p_sql := p_sql||' RETURN;'||g_eol;
2483 p_sql := p_sql||'END IF;'||g_eol;
2484 --
2485 -- Add the initialisation and component PL/SQL code
2486 l_inits := add_initialisations(p_id,p_sql,l_table);
2487 l_comps := add_components(p_id,p_sql);
2488 --
2489 -- If we didn't add any initialisations or components then add a NULL
2490 -- operation to prevent compilation errors when triggers without any
2491 -- default initialisations are first created
2492 IF NOT l_inits AND NOT l_comps THEN
2493 p_sql := p_sql||' NULL;'||g_eol;
2494 END IF;
2495
2496
2497 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2498
2499
2500 --
2501 -- Add a default exception block to catch all errors and write the
2502 -- trigger name and error text to the standard Oracle Apps error logging
2503 -- mechanism
2504 p_sql := p_sql||'EXCEPTION'||g_eol;
2505 p_sql := p_sql||' WHEN OTHERS THEN'||g_eol;
2506 p_sql := p_sql||' hr_utility.set_location('''||
2507 get_trigger_name(p_id,l_table,l_type)||''',ABS(SQLCODE));'||g_eol;
2508 p_sql := p_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
2509 p_sql := p_sql||' RAISE;'||g_eol;
2510 p_sql := p_sql||' --'||g_eol;
2511 p_sql := p_sql||'END;'||g_eol;
2512 END generate_code;
2513 --
2514 --
2515 -- +---------------------------------------------------------------------------+
2516 -- | NAME : delete_event_children |
2517 -- | SCOPE : PUBLIC |
2518 -- | DESCRIPTION: See header |
2519 -- +---------------------------------------------------------------------------+
2520 PROCEDURE delete_event_children(p_id IN NUMBER) IS
2521 --
2522 -- Get some more information we need before we can delete the event
2523 CURSOR get_trigger(cp_id IN NUMBER) IS
2524 SELECT pte.table_name,
2525 pte.triggering_action
2526 FROM pay_trigger_events pte
2527 WHERE pte.event_id = cp_id;
2528 --
2529 -- Get all the initialisations that this event uses
2530 CURSOR get_inits(cp_id IN NUMBER) IS
2531 SELECT initialisation_id
2532 FROM pay_trigger_initialisations
2533 WHERE event_id = cp_id;
2534 --
2535 -- Get all the components that this event uses
2536 CURSOR get_comps(cp_id IN NUMBER) IS
2537 SELECT component_id
2538 FROM pay_trigger_components
2539 WHERE event_id = cp_id;
2540 --
2541 l_tname VARCHAR2(30);
2542 l_action VARCHAR2(1);
2543 l_name VARCHAR2(30);
2544 --
2545 BEGIN
2546 --
2547 -- Get the table name and triggering action of the event we're deleting
2548 OPEN get_trigger(p_id);
2549 FETCH get_trigger INTO l_tname,l_action;
2550 CLOSE get_trigger;
2551 --
2552 -- Work out the trigger name according to the standard format
2553 l_name := get_trigger_name(p_id,l_tname,l_action);
2554 --
2555 -- Drop the trigger (uses AOL routines)
2556 drop_trigger(l_name);
2557 --
2558 -- Fetch all the initialisations, delete their children and finally
2559 -- the initialisations themselves
2560 FOR l_rec IN get_inits(p_id) LOOP
2561 delete_initialisation_children(l_rec.initialisation_id);
2562 END LOOP;
2563 DELETE
2564 FROM pay_trigger_initialisations
2565 WHERE event_id = p_id;
2566 --
2567 -- Fetch all the components, delete their children and finally
2568 -- the components themselves
2569 FOR l_rec IN get_comps(p_id) LOOP
2570 delete_component_children(l_rec.component_id);
2571 END LOOP;
2572 DELETE
2573 FROM pay_trigger_components
2574 WHERE event_id = p_id;
2575 --
2576 -- Delete the local variable declarations that the trigger uses
2577 DELETE
2578 FROM pay_trigger_declarations
2579 WHERE event_id = p_id;
2580 --
2581 -- Drop the support package and delete it's definition
2582 drop_package(l_tname,get_package_name(p_id,l_tname));
2583 DELETE
2584 FROM pay_trigger_support
2585 WHERE event_id = p_id;
2586 --
2587 -- Don't delete the actual event, the caller (e.g. Forms) must do this
2588 END delete_event_children;
2589 --
2590 -- +---------------------------------------------------------------------------+
2591 -- | NAME : delete_initialisation_children |
2592 -- | SCOPE : PUBLIC |
2593 -- | DESCRIPTION: See header |
2594 -- +---------------------------------------------------------------------------+
2595 PROCEDURE delete_initialisation_children(p_id IN NUMBER) IS
2596 BEGIN
2597 --
2598 -- Delete all the parameters that the requested initialisation uses
2599 DELETE
2600 FROM pay_trigger_parameters
2601 WHERE usage_type = 'I'
2602 AND usage_id = p_id;
2603 --
2604 -- The caller must delete the initialisation itself
2605 END delete_initialisation_children;
2606 --
2607 -- +---------------------------------------------------------------------------+
2608 -- | NAME : delete_component_children |
2609 -- | SCOPE : PUBLIC |
2610 -- | DESCRIPTION: See header |
2611 -- +---------------------------------------------------------------------------+
2612 PROCEDURE delete_component_children(p_id IN NUMBER) IS
2613 BEGIN
2614 --
2615 -- Delete all the parameters that the requested component uses
2616 DELETE
2617 FROM pay_trigger_parameters
2618 WHERE usage_type = 'C'
2619 AND usage_id = p_id;
2620 --
2621 -- The caller must delete the component itself
2622 END delete_component_children;
2623
2624 -- +---------------------------------------------------------------------------+
2625 -- | NAME : delete_parameters_directly |
2626 -- | SCOPE : PUBLIC |
2627 -- | DESCRIPTION: See header |
2628 -- +---------------------------------------------------------------------------+
2629 PROCEDURE delete_parameters_directly(p_param_id IN NUMBER) IS
2630 BEGIN
2631 --
2632 -- Delete a parameter directly, used by table event updates form
2633 DELETE
2634 FROM pay_trigger_parameters
2635 WHERE parameter_id = p_param_id;
2636 --
2637 -- The caller must delete the initialisation itself
2638 END delete_parameters_directly;
2639
2640 --
2641 --
2642 -- +---------------------------------------------------------------------------+
2643 -- | NAME : get_dyt_pkg_params_tbl |
2644 -- | SCOPE : PRIVATE |
2645 -- | DESCRIPTION: See header |
2646 -- +---------------------------------------------------------------------------+
2647 PROCEDURE get_dyt_pkg_params_tbl(p_tab_id IN NUMBER
2648 ,p_tab_name IN VARCHAR2
2649 ,p_params IN OUT NOCOPY g_params_tab_type) IS
2650 --
2651 cursor csr_params (cp_tab_id in number)
2652 is
2653 select parameter_name, usage_type, value_name
2654 from pay_trigger_parameters
2655 where usage_type in ('PI','PU','PD')
2656 and usage_id = cp_tab_id
2657 order by parameter_name;
2658
2659
2660
2661 -- Bug 15882261 : Modified the cursor definition to refer to dba_tab_columns
2662 -- instead of all_tab_columns
2663
2664 cursor csr_col_type (cp_tab_name varchar2, cp_col_name varchar2)
2665 is
2666 select tab.data_type
2667 from dba_tab_columns tab, user_synonyms syn
2668 where syn.synonym_name = cp_tab_name
2669 and tab.table_name = syn.table_name
2670 and tab.owner = syn.table_owner
2671 and tab.column_name like cp_col_name
2672 and tab.owner = g_pay_schema
2673 and rownum = 1; --Assuming if the params been truc'd to 30 then its the only col
2674 --on the base table with these first 30chars
2675
2676 -- nb ORDERED hint for performance fix 3110997
2677 cursor csr_type_from_pkg (cp_tab_name varchar2, cp_param_name varchar2)
2678 is
2679 SELECT /*+ ORDERED */ a.pls_type
2680 FROM (select /*+ NO_MERGE */
2681 DISTINCT hook_package
2682 from hr_api_hooks h,
2683 hr_api_modules m
2684 where m.api_module_id = h.api_module_id
2685 and m.module_name = cp_tab_name) V,
2686 USER_OBJECTS B,
2687 SYS.ARGUMENT$ A
2688 WHERE A.OBJ# = B.OBJECT_ID
2689 AND B.OBJECT_NAME = V.hook_package
2690 AND A.LEVEL# = 0
2691 AND A.argument = cp_param_name
2692 AND B.object_type = 'PACKAGE'
2693 AND rownum = 1;
2694 -- Commented for bug fix 7006158.
2695 -- SELECT /*+ ORDERED */ a.pls_type
2696 -- FROM USER_OBJECTS B,
2697 -- SYS.ARGUMENT$ A
2698 -- WHERE A.OBJ# = B.OBJECT_ID
2699 -- AND B.OBJECT_NAME in (select hook_package
2700 -- from hr_api_hooks h, hr_api_modules m
2701 -- where m.api_module_id = h.api_module_id
2702 -- and m.module_name = cp_tab_name)
2703 -- AND A.LEVEL# = 0
2704 -- AND A.argument = cp_param_name
2705 -- AND B.object_type = 'PACKAGE'
2706 -- AND rownum = 1;
2707
2708 i number := 0;
2709
2710 BEGIN
2711 --
2712 -- Fetch Schema name if required
2713 --
2714 g_pay_schema := get_table_owner(p_tab_name);
2715 --
2716 --get all mappings of dyn-trigger-package-procedure params, eg all names
2717 -- of dyn-trigger local vars into a record with info on I,U,D --given table id
2718 FOR param_rec in csr_params(p_tab_id) LOOP
2719 p_params(i).local_form := param_rec.parameter_name;
2720 p_params(i).usage_type := param_rec.usage_type;
2721 p_params(i).value_name := param_rec.value_name;
2722 IF SUBSTR(p_params(i).local_form,1,2) = 'l_' THEN
2723 p_params(i).param_form := 'p_l_'||SUBSTR(p_params(i).local_form,3);
2724 ELSIF SUBSTR(p_params(i).local_form,1,5) = ':old.' THEN
2725 p_params(i).param_form := 'p_old_'||SUBSTR(p_params(i).local_form,6);
2726 ELSIF SUBSTR(p_params(i).local_form,1,5) = ':new.' THEN
2727 p_params(i).param_form := 'p_new_'||SUBSTR(p_params(i).local_form,6);
2728 ELSE
2729 p_params(i).param_form := NULL;
2730 END IF;
2731
2732 --Get col type using the text after the '.' in local form(plus % for like in csr)
2733 --
2734 open csr_col_type(p_tab_name,upper(substr(p_params(i).local_form,instr(p_params(i).local_form,'.')+1))||'%');
2735 fetch csr_col_type into p_params(i).data_type;
2736 close csr_col_type;
2737
2738 -- if we didnt get a data_type (eg name different than base col.)
2739 -- then have to resort to the inefficient approach,
2740 -- getting the type from the user hook pkg definition, on which these parameter
2741 -- mappings are based. (Because essentially all these dynamic procedures do
2742 -- is act as the call package from the hook pkg.)
2743 if (p_params(i).data_type is null) then
2744 open csr_type_from_pkg(p_tab_name,
2745 upper('p_'||SUBSTR(p_params(i).local_form,6)));
2746 fetch csr_type_from_pkg into p_params(i).data_type;
2747 close csr_type_from_pkg;
2748 end if;
2749 --If still null try one last hack in case we're working on the ALL version
2750 if (p_params(i).data_type is null) then
2751 open csr_type_from_pkg(replace(p_tab_name, '_ALL_','_'),
2752 upper('p_'||SUBSTR(p_params(i).local_form,6)));
2753 fetch csr_type_from_pkg into p_params(i).data_type;
2754 close csr_type_from_pkg;
2755 end if;
2756 --If still null try one last hack, cos know all should have this
2757 --and if no hook we might miss it (eg pay_element_entry_val)
2758 if (p_params(i).data_type is null
2759 and p_params(i).value_name = 'P_DATETRACK_MODE') then
2760 p_params(i).data_type := 'VARCHAR2';
2761 end if;
2762
2763 -- if still null then maybe raise a better error?
2764
2765
2766 --All param versions must be less than 30 chars as used in dbms triggers
2767 p_params(i).param_form := substr(p_params(i).param_form,0,30);
2768 i := i+1;
2769 END LOOP;
2770 END get_dyt_pkg_params_tbl;
2771
2772 --
2773 -- +---------------------------------------------------------------------------+
2774 -- | NAME : get_dyt_rhi_params |
2775 -- | SCOPE : PRIVATE |
2776 -- | DESCRIPTION: See header |
2777 -- +---------------------------------------------------------------------------+
2778 PROCEDURE get_dyt_rhi_params(p_tab_id IN NUMBER
2779 ,p_tab_name IN VARCHAR2
2780 ,p_params IN OUT NOCOPY g_params_tab_type) IS
2781
2782 -- Local variables to catch the values returned from
2783 -- hr_general.describe_procedure
2784 --
2785 l_overload dbms_describe.number_table;
2786 l_position dbms_describe.number_table;
2787 l_level dbms_describe.number_table;
2788 l_argument_name dbms_describe.varchar2_table;
2789 l_datatype dbms_describe.number_table;
2790 l_default_value dbms_describe.number_table;
2791 l_in_out dbms_describe.number_table;
2792 l_length dbms_describe.number_table;
2793 l_precision dbms_describe.number_table;
2794 l_scale dbms_describe.number_table;
2795 l_radix dbms_describe.number_table;
2796 l_spare dbms_describe.number_table;
2797
2798 l_datatype_str varchar2(20); -- String equivalent of
2799 -- l_datatype number.
2800 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
2801 --
2802 c_dtype_undefined constant number default 0;
2803 c_dtype_varchar2 constant number default 1;
2804 c_dtype_number constant number default 2;
2805 c_dtype_long constant number default 8;
2806 c_dtype_date constant number default 12;
2807 c_dtype_boolean constant number default 252;
2808
2809 cursor csr_hooks (cp_tab_name varchar2)
2810 is
2811 select hook_package pkg, hook_procedure proc
2812 ,decode(hook_procedure, 'AFTER_INSERT', 'PI',
2813 'AFTER_UPDATE', 'PU',
2814 'AFTER_DELETE', 'PD') usage_type
2815 from hr_api_hooks h, hr_api_modules m
2816 where m.api_module_id = h.api_module_id
2817 and m.module_name = cp_tab_name;
2818 --
2819
2820 i number := 1;
2821 j number := 0; --p_params, our local store, starts from 0
2822
2823 l_prefix varchar2(15); --Local Form prefix
2824 l_o varchar2(15) := 'p_old_'; --Old Style Local Form prefix
2825 l_n varchar2(15) := 'p_new_'; --Old Style Local Form prefix
2826 l_value_name varchar2(80);
2827
2828 l_proc varchar2(80) := 'get_dyt_rhi_params';
2829
2830 BEGIN
2831
2832 -- Want an exhaustive list of params, whatever is available to hook
2833 --should be passed
2834 --In terms of this internal table structure, want
2835 --NB names based on form param screen, NOT what makes logical sense!
2836 -- param_form - ':old.column_name'
2837 -- local_form - 'p_column_name_o'
2838 -- value_name - 'P_old_column_name'
2839 << HOOK_MODULES >>
2840 FOR hook_rec in csr_hooks(p_tab_name) LOOP
2841
2842 hr_general.describe_procedure
2843 (object_name => hook_rec.pkg || '.' || hook_rec.proc
2844 ,reserved1 => null
2845 ,reserved2 => null
2846 ,overload => l_overload
2847 ,position => l_position
2848 ,level => l_level
2849 ,argument_name => l_argument_name
2850 ,datatype => l_datatype
2851 ,default_value => l_default_value
2852 ,in_out => l_in_out
2853 ,length => l_length
2854 ,precision => l_precision
2855 ,scale => l_scale
2856 ,radix => l_radix
2857 ,spare => l_spare
2858 );
2859
2860
2861 << ONE_PROC_PARAM_LOOP >>
2862 FOR i in 1..l_position.COUNT loop
2863 --hr_utility.trace(' Found parameter '||l_argument_name(i)||' type '||l_ --
2864 if l_datatype(i) <> c_dtype_varchar2 and
2865 l_datatype(i) <> c_dtype_number and
2866 l_datatype(i) <> c_dtype_date and
2867 l_datatype(i) <> c_dtype_boolean and
2868 l_datatype(i) <> c_dtype_long then
2869 -- Error: In a hook package procedure all the parameter datatypes
2870 -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2871 -- module will not execute until this problem has been resolved.
2872 hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2873 hr_utility.set_location(l_proc, 80);
2874 else
2875 -- Set the datatype string with the corresponding word value
2876 if l_datatype(i) = c_dtype_varchar2 then
2877 l_datatype_str := 'VARCHAR2';
2878 elsif l_datatype(i) = c_dtype_number then
2879 l_datatype_str := 'NUMBER';
2880 elsif l_datatype(i) = c_dtype_date then
2881 l_datatype_str := 'DATE';
2882 elsif l_datatype(i) = c_dtype_boolean then
2883 l_datatype_str := 'BOOLEAN';
2884 else
2885 l_datatype_str := 'LONG';
2886 end if;
2887
2888 p_params(j).local_form := 'NOT CALCULATED';
2889 p_params(j).param_form := 'NOT CALCULATED';
2890 p_params(j).value_name := substr(l_argument_name(i),0,30);
2891 p_params(j).usage_type := hook_rec.usage_type;
2892 p_params(j).data_type := l_datatype_str;
2893 j := j+1;
2894 end if;
2895
2896 END LOOP ONE_PROC_PARAM_LOOP;
2897
2898 END LOOP HOOK_MODULES;
2899 --Debug Output all the paramaters in my table form
2900 --FOR j in 0..(p_params.COUNT - 1) loop
2901 --hr_utility.trace(j||' '||p_params(j).value_name||' '||p_params(j).usage_type||' '||p_params(j).data_type);
2902 --end loop;
2903
2904 hr_utility.trace('Total RHI params '||p_params.count);
2905
2906 -- If we didnt get any hook params then there might not be a hook pkg!
2907 -- So just base the params on what we actually need, i.e. the param mappings
2908 if ( p_params.count = 0 ) then
2909 hr_utility.trace('No hook params => build params from what is required from components');
2910
2911 get_dyt_pkg_params_tbl(p_tab_id,p_tab_name,p_params);
2912 --On top of the ones we need in dyt call, we also know we need p_datetrack
2913 --in both after_delete and after_update.
2914 i := p_params.count;
2915 p_params(i).local_form := 'NOT CALCULATED';
2916 p_params(i).param_form := 'NOT CALCULATED';
2917 p_params(i).value_name := 'P_DATETRACK_MODE';
2918 p_params(i).usage_type := 'PU';
2919 p_params(i).data_type := 'VARCHAR2';
2920 p_params(i).local_form := 'NOT CALCULATED';
2921 p_params(i+1).param_form := 'NOT CALCULATED';
2922 p_params(i+1).value_name := 'P_DATETRACK_MODE';
2923 p_params(i+1).usage_type := 'PD';
2924 p_params(i+1).data_type := 'VARCHAR2';
2925
2926 hr_utility.trace('Total RHI params '||p_params.count);
2927 -- Example of structure of p_params from get_dyt_pkg_params_tbl
2928 --local_form - :new.EFFECTIVE_END_DATE
2929 --param_form - p_new_EFFECTIVE_END_DATE
2930 --value_name - P_EFFECTIVE_END_DATE
2931
2932 end if;
2933
2934 END get_dyt_rhi_params;
2935
2936 --
2937 -- +---------------------------------------------------------------------------+
2938 -- | NAME : get_dyt_pkg_version_of_code |
2939 -- | SCOPE : PRIVATE |
2940 -- | DESCRIPTION: This procedure takes an extract of dynamically generated code|
2941 -- | relevant for dbms triggers and turns it in to code useful for|
2942 -- | code that resides in packages. Main use to convert code |
2943 -- | generated by |
2944 -- | + add_declarations |
2945 -- | + add_initialisations |
2946 -- | + add_components |
2947 -- | in to code being built as part of + Gen_dyt_pkg_full_code |
2948 -- +---------------------------------------------------------------------------+
2949 PROCEDURE get_dyt_pkg_version_of_code(p_sql IN OUT NOCOPY varchar2,
2950 p_tab_name IN varchar2,
2951 p_usage_type IN varchar2) IS
2952 --
2953 l_pos number;
2954 l_col_name varchar2(32767);
2955 l_new_col_name varchar2(2000);
2956
2957 cursor csr_missing_params (cp_table_name in varchar2, cp_type in varchar2) is
2958 /* Component calls use this , ignore locals cos these will always be made*/
2959 SELECT upper(value_name) name
2960 FROM pay_trigger_parameters
2961 WHERE parameter_type IN ('I','O')
2962 AND value_name IS NOT NULL
2963 AND usage_id in (select component_id
2964 from pay_trigger_events te, pay_trigger_components tc
2965 where te.event_id = tc.event_id
2966 and triggering_action = cp_type
2967 and te.table_name = cp_table_name)
2968 AND usage_type = 'C'
2969 AND value_name like ':%'
2970 MINUS
2971 /* aru params use this */
2972 SELECT upper(parameter_name)
2973 FROM pay_trigger_parameters
2974 WHERE usage_type = 'P'||cp_type
2975 AND usage_id = (select dated_table_id
2976 from pay_dated_tables
2977 where table_name = cp_table_name);
2978
2979 l_missing varchar2(60);
2980 l_missing_rep varchar2(60);
2981 l_extract varchar2(32767);
2982 l_extract_new varchar2(32767);
2983 l_pos_next number;
2984 i number;
2985
2986 BEGIN
2987 --Make life easier by getting sql in caps
2988 p_sql := upper(p_sql);
2989
2990 -- >>> 1. Do some funky stuff to overcome major headaches.
2991 --
2992 --OLD SKOOL
2993 -- DB TRIG -> CC PKG
2994 --NEW SKOOL
2995 -- USER_HOOK_PKG -> USER_CALL_PKG(DYT_PKG) -> CC_PKG
2996 --
2997 -- The original dyt's as db triggers had access to ALL values on the table
2998 -- old and new, no worries, so CC pkg code could be built expecting ALL values.
2999 -- However, now the CC pkg is called by a dyt_pkg which is referred to as an
3000 -- user hook call pkg, as is called by the user hook pkg. Trouble is what
3001 -- parameters the user hook package has access to is in the lap of the gods
3002 -- aka the developers who build them. It is acceptable for example to not pass
3003 -- the new version of values if they are non-updateable, i.e. they are defo the same
3004 -- so why bother passing?
3005 -- BUT, out dyt_pkg's need to call the CC pkg and this is still expecting ALL values
3006 -- old and new for each column.
3007 -- Therefore we do a crafty trick/hack, getting those values that are referenced in
3008 -- component code and are not passed in by the hook. We make the assumption,
3009 -- fairly solid, that we can safely replace the missing value with the same
3010 -- existing value. EG If business_group_id is non-updateable (often) then the value
3011 -- p_business_group_id wont exist in the scope of our hook pkg and thus not be
3012 -- in our call-package, however p_business_group_id_o will be passed in.
3013 -- So we replace all instances of :new.business_group_id (because everything
3014 -- is still in old-skool db style) and replace it with :old.business_group_id.!
3015 -- et voila, the CC pkg
3016 -- gets all the values, just sometimes the same value for old and new.
3017
3018 --missing_param defo upper
3019 for missing_param in csr_missing_params(p_tab_name,p_usage_type) loop
3020 --Most of the time missing new value, cos non-updateable
3021 --eg turn :new.business_group_id to :old.business_group_id
3022 l_missing := missing_param.name;
3023 if (substr(l_missing,1,4) = ':NEW' ) then
3024 l_missing_rep := replace(missing_param.name,'NEW','OLD');
3025
3026 --But as developer has control, may be some the other way eg one off dates
3027 --and id's where arbitrary which is passed, and not changed
3028 elsif (substr(l_missing,1,4) = ':OLD' ) then
3029 l_missing_rep := replace(missing_param.name,'OLD','NEW');
3030 else
3031 l_missing_rep := l_missing||'xxx'; --no change so will fall over
3032 end if;
3033 --hr_utility.trace('Missing param: '||l_missing||' replace with: '||l_missing_rep);
3034 p_sql := replace(p_sql,l_missing,l_missing_rep);
3035
3036 end loop;
3037
3038 -- >>> 2. Turn any references to :old. style notation to param style
3039 -- EG. :old.cost_allocation_keyflex_id => p_old_cost_allocation_keyflex
3040 --
3041 -- Find first occurences of :old.
3042 l_pos := instr(p_sql,':OLD.');
3043 -- Loop through all occurences
3044 while (l_pos > 0) loop
3045
3046 --Get extract from this instance of :new to next :new instance
3047 --
3048 l_pos_next := instr(p_sql,':OLD.',l_pos+1);
3049 --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3050 if (l_pos_next <> 0 ) then
3051 l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3052 else
3053 l_extract := substr(p_sql,l_pos);
3054 l_pos_next := length(p_sql)+1;
3055 end if;
3056
3057 l_col_name := translate(l_extract,' ),|;'||g_eol,'*****');
3058 l_col_name := substr(l_col_name,1,instr(l_col_name,'*')-1);
3059
3060 -- Amend the prefix and make sure we're not over the 30 char limit
3061 l_new_col_name := substr(replace(l_col_name,':OLD.','P_OLD_'),1,30);
3062
3063 --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3064
3065 l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3066 p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3067
3068 --Find next occurence ,will be first as just done previous first,
3069 --but go from l_pos as quicker
3070 l_pos := instr(p_sql,':OLD.',l_pos);
3071
3072 end loop;
3073
3074 -- >>> 3. Turn any references to :new. style notation to param style
3075 --
3076
3077 -- Find first occurences of :new.
3078 l_pos := instr(p_sql,':NEW.');
3079 -- Loop through all occurences
3080 while (l_pos > 0) loop
3081
3082 --Get extract from this instance of :new to next
3083 --
3084 l_pos_next := instr(p_sql,':NEW.',l_pos+1);
3085 --hr_utility.trace(l_pos||' <- l_pos -> '||l_pos_next);
3086 if (l_pos_next <> 0 ) then
3087 l_extract := substr(p_sql,l_pos,l_pos_next - l_pos);
3088 else
3089 l_extract := substr(p_sql,l_pos);
3090 l_pos_next := length(p_sql)+1;
3091 end if;
3092
3093 l_col_name := translate(l_extract,' ),|;'||g_eol,'*****');
3094 l_col_name := substr(l_col_name,1,instr(l_col_name,'*')-1);
3095
3096 -- Amend the prefix and make sure we're not over the 30 char limit
3097 l_new_col_name := substr(replace(l_col_name,':NEW.','P_NEW_'),1,30);
3098
3099 --hr_utility.trace(l_pos||' Replace- '||l_col_name||' with- '||l_new_col_name);
3100
3101 l_extract_new := replace(l_extract,l_col_name,l_new_col_name);
3102 p_sql := substr(p_sql,0,l_pos-1)||l_extract_new||substr(p_sql,l_pos_next);
3103
3104 --Find next occurence ,will be first as just done previous first,
3105 --but go from l_pos as quicker
3106 l_pos := instr(p_sql,':NEW.',l_pos);
3107
3108 end loop;
3109
3110 -- >>> 4. Remove the instances of DECLARE
3111 -- --now the calling code adds the DECLARE explicitly, as opposed to add_declarations
3112 --this should actually not find any DECLARE chars
3113 p_sql := replace(p_sql,'DECLARE');
3114 end get_dyt_pkg_version_of_code;
3115
3116 --
3117 -- +---------------------------------------------------------------------------+
3118 -- | NAME : get_dyt_proc_name |
3119 -- | SCOPE : PRIVATE |
3120 -- | DESCRIPTION: Would like to use full dynamic trigger name simply as proc |
3121 -- | name but sadly some are too long, so strip down, but keep the|
3122 -- | last few chars as these are often the useful identifiers. |
3123 -- +---------------------------------------------------------------------------+
3124 FUNCTION get_dyt_proc_name(p_dyt_name IN VARCHAR2) RETURN varchar2 IS
3125
3126 l_suffix pay_trigger_events.short_name%TYPE;
3127 l_proc_name varchar2(30);
3128 BEGIN
3129
3130 --Strip off the useful suffix (make sure its max 30chars)
3131 l_suffix := substr( substr(p_dyt_name,instr(p_dyt_name,'_',-1)) , 1 , 30);
3132
3133 --First version of proc_name is as many chars at start of p_dyt_name
3134 l_proc_name := substr(p_dyt_name,1,30 - length(l_suffix));
3135 --Full version is first||suffix
3136 l_proc_name := l_proc_name||l_suffix;
3137
3138 --hr_utility.trace(' Got DYT pkg procedure name: '||l_proc_name);
3139 return l_proc_name;
3140 END;
3141
3142 --
3143 -- +---------------------------------------------------------------------------+
3144 -- | NAME : gen_dyt_pkg_full_code |
3145 -- | SCOPE : PUBLIC |
3146 -- | DESCRIPTION: See header |
3147 -- +---------------------------------------------------------------------------+
3148 PROCEDURE gen_dyt_pkg_full_code(p_tab_id IN NUMBER,
3149 p_ok IN OUT NOCOPY BOOLEAN) IS
3150 --
3151
3152 l_tab_id pay_dated_tables.dated_table_id%TYPE;
3153 l_tab_name pay_dated_tables.table_name%TYPE;
3154 l_tab_dyt_types pay_dated_tables.dyn_trigger_type%TYPE;
3155 l_tab_dyt_pkg_name pay_dated_tables.dyn_trigger_package_name%TYPE;
3156 l_datetracked_table varchar2(5);
3157
3158 --
3159 l_hs varchar2(32000); --Used as header sql placeholders
3160 l_bs varchar2(32000); --Used as body sql placeholder
3161 l_hsql varchar2(32000); --Used as header
3162 l_bsql varchar2(32767); --Used as body
3163
3164 l_dyt_params g_params_tab_type; --hold params to build dyt in dyt_pkg
3165 l_hok_params g_params_tab_type; --hold params to build rhi-hook wrapper
3166 l_dbt_name varchar2(80);
3167
3168 l_head_ok BOOLEAN;
3169 l_body_ok BOOLEAN;
3170 l_flag varchar2(1);
3171
3172 l_dyt_pkg_head_tbl t_varchar2_32k_tbl;
3173 l_dyt_pkg_body_tbl t_varchar2_32k_tbl;
3174
3175 --
3176 --Given id, get table info
3177 CURSOR csr_table_info (cp_tab_id in NUMBER) IS
3178 SELECT pdt.dated_table_id,
3179 pdt.table_name,
3180 nvl(pdt.dyn_trigger_type,'T'),
3181 pdt.dyn_trigger_package_name,
3182 decode(start_date_name,
3183 null, 'N',
3184 'Y')
3185 FROM pay_dated_tables pdt
3186 WHERE pdt.dated_table_id = (cp_tab_id);
3187
3188 -- Get details of all dyn-triggers on given table
3189 CURSOR csr_dyts_on_table(cp_name in VARCHAR2) IS
3190 SELECT pte.event_id, pte.short_name,
3191 pte.triggering_action, pte.description,
3192 DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info,
3193 enabled_flag,generated_flag
3194 FROM pay_trigger_events pte
3195 WHERE pte.table_name = (cp_name)
3196 AND nvl(pte.protected_flag,'N') <> 'Y';
3197
3198 --
3199 l_proc varchar2(35) := 'gen_dyt_pkg_full_code';
3200 BEGIN
3201 hr_utility.set_location(' Entering: '||l_proc,10);
3202 -- The package to hold the code is created with two sections.
3203 -- a. The main trigger code, built using info from the dynamic triggers screen
3204 -- b. The after_update, after_insert, after_delete standard called by rhi,
3205 -- these act as wrappers calling (a)'s.
3206 --
3207 -- These parts are all built dynamically
3208 -- At this time, this code may have been called for the generation of a
3209 -- SINGLE trigger, However, we generate a dynamic trigger package (dyt_pkg)
3210 -- representing code for ALL triggers on that table.
3211
3212 -- NB. It is important to note that there is a subtle difference between
3213 -- database triggers and dynamic triggers. The latter are defined on site giving
3214 -- great flexibility, i.e. what customer-specific calls need to be made as part of
3215 -- this process. How the code is actually stored provides more flexibility.
3216 -- This is table specific and defined in pay_dated_tables, eg the dyn-trigger
3217 -- code can be stored as database triggers, in a separate package or in both of these.
3218 -- ...
3219 -- Since continuous calc we are moving toward the idea of no database triggers,
3220 -- (negative issues with maintenance) and toward stored code as a package.
3221 -- Indeed, many seeded offerings on core tables will have this behaviour. However,
3222 -- we allow greater customer flexibilty by leaving database triggers as an option.
3223 -- (Especially useful for non-API supported customer database tables.)
3224 --
3225 -- Fetch the table information, given this passed id
3226 OPEN csr_table_info(p_tab_id);
3227 FETCH csr_table_info INTO l_tab_id, l_tab_name,
3228 l_tab_dyt_types, l_tab_dyt_pkg_name,
3229 l_datetracked_table;
3230 CLOSE csr_table_info;
3231
3232
3233 -- Create table of varchar2's representing the full head and body code.
3234 -- Initialise the holders of PL/SQL code
3235 --
3236 -- >>> 1. Add the start of this 'dynamic-trigger package code', incl comments
3237 --
3238 l_hs := l_hs||'/*'||g_eol;
3239 l_hs := l_hs||' =================================================='||g_eol;
3240 l_hs := l_hs||' This is a dynamically generated database package '||g_eol;
3241 l_hs := l_hs||' containing code to support the use of dynamic '||g_eol;
3242 l_hs := l_hs||' triggers. '||g_eol;
3243 l_hs := l_hs||' Preference of package Vs dbms triggers supporting '||g_eol;
3244 l_hs := l_hs||' dyn'' triggers is made via the dated table form. '||g_eol;
3245 l_hs := l_hs||' . '||g_eol;
3246 l_hs := l_hs||' This code will be called implicitly by table rhi '||g_eol;
3247 l_hs := l_hs||' and explictly from non-API packages that maintain '||g_eol;
3248 l_hs := l_hs||' data on the relevant table. '||g_eol;
3249 l_hs := l_hs||' =================================================='||g_eol;
3250 l_hs := l_hs||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3251 l_hs := l_hs||' --------------------------------------------------'||g_eol;
3252 l_hs := l_hs||' Package Name: '||l_tab_dyt_pkg_name||g_eol;
3253 l_hs := l_hs||' Base Table: '||l_tab_name||g_eol;
3254 l_hs := l_hs||' Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3255 l_hs := l_hs||' =================================================='||g_eol;
3256 l_hs := l_hs||'*/'||g_eos;
3257
3258 -- Add first line of code to two main placeholders, and add the comments
3259 --
3260 l_hsql := l_hsql||'Create or replace package '||l_tab_dyt_pkg_name||g_eol
3261 ||'AS'||g_eos;
3262 l_hsql := l_hsql||l_hs;
3263 l_bsql := l_bsql||'Create or replace package body '||l_tab_dyt_pkg_name||g_eol
3264 ||'IS'||g_eos;
3265 l_bsql := l_bsql||l_hs;
3266 l_hs :='';
3267 --
3268
3269 -- Shove this first chunk of code into holder
3270 l_dyt_pkg_head_tbl(0) := l_hsql;
3271 l_dyt_pkg_body_tbl(0) := l_bsql;
3272
3273 -- >>> 2. Get all parameters defined for this table in to table of records for future
3274 -- manipulation. This table holds the following info:
3275 hr_utility.set_location(' -Get parameters DYT : '||l_proc,20);
3276
3277 -- i. local name --entered in form
3278 -- ii. generated pkg param version of i-used for internal pkg proc
3279 -- iii. usage type -- eg PI Insert, PU Update, PD Delete
3280 -- iv. type of col
3281 --
3282 get_dyt_pkg_params_tbl(l_tab_id, l_tab_name, l_dyt_params);
3283 hr_utility.set_location(' -Get parameters after_xxx : '||l_proc,25);
3284 get_dyt_rhi_params(l_tab_id, l_tab_name, l_hok_params);
3285
3286 -- >>> 3. Add the dynamic trigger code as separate public procedures
3287 -- loop for each dyn trig, calling a procedure to create appropriate code
3288 --
3289 hr_utility.set_location(' -Create dyt procedure code: '||l_proc,30);
3290 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3291 gen_dyt_pkg_proc(dyt_rec.event_id,dyt_rec.short_name,
3292 l_tab_name,dyt_rec.triggering_action,
3293 dyt_rec.description,dyt_rec.info,
3294 l_dyt_params,l_hs,l_bs);
3295 l_dyt_pkg_head_tbl(csr_dyts_on_table%ROWCOUNT) := l_hs;
3296 l_dyt_pkg_body_tbl(csr_dyts_on_table%ROWCOUNT) := l_bs;
3297
3298 l_hs := '';l_bs :='';
3299
3300 END LOOP;
3301 --
3302 -- >>> 4. Add the wrapper procedures for row handler entry points
3303 -- Create the wrapper procedures that the rhi will call, these simply call
3304 -- the newly created, dynamic trigger code procedures
3305 -- Three times, one for each trigger type
3306 --
3307 hr_utility.set_location(' -Create hook entry point procedure code: '||l_proc,40);
3308 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);
3309 l_hs := ''; l_bs := '';
3310 --
3311 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);
3312 l_hs := ''; l_bs := '';
3313 --
3314 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);
3315 l_hs := ''; l_bs := '';
3316
3317
3318 -- >>> 5. Complete the package code
3319 --
3320 l_hsql := ''; l_bsql := '';
3321 l_hsql := l_hsql||'END '||l_tab_dyt_pkg_name||';'||g_eol;
3322 l_bsql := l_bsql||'/* END_PACKAGE */'||g_eol;
3323 l_bsql := l_bsql||'END '||l_tab_dyt_pkg_name||';'||g_eol;
3324 l_dyt_pkg_head_tbl(l_dyt_pkg_head_tbl.last + 1) := l_hsql;
3325 l_dyt_pkg_body_tbl(l_dyt_pkg_body_tbl.last + 1) := l_bsql;
3326
3327
3328 -- This is the first time we will be adding to the g_dyt_pkg_head and g_dyt_pkg_head tables
3329 -- so empty first then add
3330 init_dyt_pkg;
3331 add_to_dyt_pkg(l_dyt_pkg_head_tbl,FALSE);
3332 add_to_dyt_pkg(l_dyt_pkg_body_tbl,TRUE);
3333
3334
3335 -- >>> 6. Generate and compile this new dynamic package
3336 --
3337 hr_utility.set_location(' -Generate database package: '||l_proc,60);
3338 build_dyt_pkg_from_tbl( g_dyt_pkg_head,g_dyt_pkg_hindex,FALSE);
3339 build_dyt_pkg_from_tbl( g_dyt_pkg_body,g_dyt_pkg_bindex,TRUE);
3340
3341 -- >>> 7. Create database trigger equivalent of dyn-trigger if required
3342 -- Go through recently cached cursor again
3343 p_ok := TRUE; --assume ok first, then test each as built
3344 --
3345 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3346 l_dbt_name := get_trigger_name(dyt_rec.event_id,l_tab_name,dyt_rec.triggering_action);
3347 --
3348 IF ( l_tab_dyt_types = 'B') THEN
3349 gen_dyt_db_trig(dyt_rec.event_id,dyt_rec.short_name
3350 ,l_tab_name, dyt_rec.triggering_action
3351 ,dyt_rec.description,dyt_rec.info
3352 ,l_dyt_params,l_tab_dyt_pkg_name);
3353 --Set db trigger to enabled as dependent on the dyn trigger value
3354 enable_trigger( l_dbt_name,flag_to_boolean(dyt_rec.enabled_flag) );
3355
3356 --if so far all is well perform check on this new db trigger
3357 if (p_ok) then
3358 p_ok := module_ok(l_dbt_name,'TRIGGER');
3359 end if;
3360
3361 ELSE
3362 --Package only then drop db trigs if they exist.
3363 drop_trigger(l_dbt_name);
3364 p_ok := TRUE;
3365 END IF;
3366
3367 END LOOP;
3368
3369
3370 -- >>> 8. Test how well package/triggers have been created and return some kind of status
3371 --
3372 -- pkg
3373 l_head_ok := module_ok(l_tab_dyt_pkg_name,'PACKAGE');
3374 l_body_ok := module_ok(l_tab_dyt_pkg_name,'PACKAGE BODY');
3375
3376 --Final return status is true only if all parts are success
3377 if (l_head_ok and l_body_ok and p_ok) then
3378 p_ok := TRUE;
3379 l_flag := 'Y';
3380 else
3381 p_ok :=FALSE;
3382 l_flag := 'N';
3383 end if;
3384
3385 --hr_utility.set_location(' -Status of database package: '||l_flag||l_proc,80);
3386 --Complete FOR WHOLE TABLE, so mark pkg gen, and all dyt as generated + enabled or vice versa
3387 -- All or nothing, either all dyt's are success, or mark all as failure
3388 update pay_dated_tables
3389 set dyn_trig_pkg_generated = l_flag
3390 where table_name = l_tab_name
3391 and dated_table_id = l_tab_id;
3392 --
3393 FOR dyt_rec in csr_dyts_on_table(l_tab_name) LOOP
3394 if (p_ok) then --success so back to original
3395 update pay_trigger_events
3396 set generated_flag = l_flag,
3397 enabled_flag = dyt_rec.enabled_flag
3398 where event_id = dyt_rec.event_id;
3399 else
3400 update pay_trigger_events --failure so disabled
3401 set generated_flag = l_flag,
3402 enabled_flag = l_flag
3403 where event_id = dyt_rec.event_id;
3404 end if;
3405
3406 END LOOP;
3407 commit; --make sure updates are saved
3408 hr_utility.set_location(' Leaving: '||l_proc,900);
3409 END gen_dyt_pkg_full_code;
3410
3411 -- +-----------------------------------------------------------------------------+
3412 -- | NAME : gen_dyt_pkg_proc |
3413 -- | SCOPE : PRIVATE |
3414 -- | DESCRIPTION: This procedure generates the code representing a single dynamic|
3415 -- | trigger. All information declared in the dynamic triggers form is used|
3416 -- | to build up a package version of this trigger. Parameter info is got |
3417 -- | from the table_event_updates form. |
3418 -- | The actual components are built up using existing procedures |
3419 -- | Namely, add_components,add_initialisations, but we have to modify the |
3420 -- | resulting code slightly. |
3421 -- +-----------------------------------------------------------------------------+
3422 PROCEDURE gen_dyt_pkg_proc(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
3423 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
3424 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
3425 ,p_dyn_pkg_params IN g_params_tab_type
3426 ,p_hs IN OUT NOCOPY VARCHAR2
3427 ,p_bs IN OUT NOCOPY VARCHAR2) IS
3428 --
3429 l_inits BOOLEAN;
3430 l_comps BOOLEAN;
3431 l_tab_name pay_dated_tables.table_name%TYPE := p_tab_name;
3432 l_dyt_id pay_trigger_events.event_id%TYPE := p_dyt_id;
3433 l_dyt_name pay_trigger_events.short_name%TYPE := get_dyt_proc_name(p_dyt_name);
3434 l_dyt_act pay_trigger_events.triggering_action%TYPE := p_dyt_act;
3435 l_dyt_info varchar2(30) := p_dyt_info;
3436 l_dyt_desc pay_trigger_events.description%TYPE := p_dyt_desc ;
3437
3438 l_sql varchar2(32000); --Used as a temp holder.
3439 i number;
3440 j number;
3441 delim varchar2(15) := ' ';
3442 --
3443 BEGIN
3444 --
3445 -- Initialise the statement PL/SQL code
3446 p_hs := ''; p_bs := '';
3447 --
3448 --
3449 -- Add an initial comment section to the trigger code
3450
3451 l_sql := l_sql||'/*'||g_eol;
3452 l_sql := l_sql||' ================================================'||g_eol;
3453 l_sql := l_sql||' This is a dynamically generated package procedure'||g_eol;
3454 l_sql := l_sql||' with code representing a dynamic trigger '||g_eol;
3455 l_sql := l_sql||' ================================================'||g_eol;
3456 l_sql := l_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3457 l_sql := l_sql||' ------------------------------------------------'||g_eol;
3458 l_sql := l_sql||' Name: '||l_dyt_name||g_eol;
3459 l_sql := l_sql||' Table: '||l_tab_name||g_eol;
3460 l_sql := l_sql||' Action: '||l_dyt_info||g_eol;
3461 l_sql := l_sql||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3462 l_sql := l_sql||' Description: '||l_dyt_desc||g_eol;
3463 l_sql := l_sql||' Full trigger name: '||p_dyt_name||g_eol;
3464 l_sql := l_sql||' ================================================'||g_eol;
3465 l_sql := l_sql||'*/'||g_eol||'--'||g_eol;
3466 --
3467 l_sql := l_sql||'PROCEDURE '||l_dyt_name||g_eol;
3468 -- Complete procedure definition, (still same for header and body)
3469 --
3470 --
3471 i := 0;
3472 while i < p_dyn_pkg_params.count LOOP
3473 --only need to print params that are relevant
3474 if (substr(p_dyn_pkg_params(i).usage_type,2,1) = l_dyt_act) then
3475 -- quick formatting option
3476 if (length(p_dyn_pkg_params(i).param_form) > 40) then
3477 j := 80; else j := 40;
3478 end if;
3479 --if first param then add opening bracket
3480 if (delim = ' ') then l_sql := l_sql || '('||g_eol;
3481 end if;
3482 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,j,' ')
3483 ||' in '||p_dyn_pkg_params(i).data_type||g_eol;
3484 delim := ',';
3485 end if;
3486 i := i+1;
3487 end loop;
3488
3489 --Only close bracket if had params
3490 if(delim = ',') then l_sql := l_sql ||' )'; end if;
3491 --End the header text, then continue with body, now create two distinct strings
3492 p_hs := l_sql||'; -- End of procedure definition for '||l_dyt_name||g_eos;
3493 p_bs := l_sql ||' IS '||g_eos;
3494
3495 --
3496 -- Add the declaration section
3497 -- Do not need DECLARE before calling add_declarations
3498 p_bs := p_bs||' /* Local variable declarations */'||g_eol;
3499
3500 -- Add the seeded declaration section
3501 add_declarations(l_dyt_id,p_bs);
3502
3503 -- Add any hard-coded declarations
3504 -- n/a
3505
3506 p_bs := p_bs||'BEGIN'||g_eol;
3507 --
3508 p_bs := p_bs||' hr_utility.trace('' >DYT: Execute procedure version of Dynamic Trigger: '||p_dyt_name||''');'||g_eol;
3509
3510
3511 --
3512 -- Add the data migrator check - Bug 1885557
3513 p_bs := p_bs||'IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
3514 p_bs := p_bs||' RETURN;'||g_eol;
3515 p_bs := p_bs||'END IF;'||g_eol;
3516 -- Add the initialisation and component PL/SQL code
3517 l_inits := add_initialisations(l_dyt_id,p_bs,l_tab_name);
3518 l_comps := add_components(l_dyt_id,p_bs);
3519 --
3520 -- If we didn't add any initialisations or components then add a NULL
3521 -- operation to prevent compilation errors when triggers without any
3522 -- default initialisations are first created
3523 IF NOT l_inits AND NOT l_comps THEN
3524 p_bs := p_bs||' NULL;'||g_eol;
3525 END IF;
3526 --
3527
3528 -- Add a default exception block to catch all errors and write the
3529 -- trigger name and error text to the standard Oracle Apps error logging
3530 -- mechanism
3531 p_bs := p_bs||'EXCEPTION'||g_eol;
3532 p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3533 p_bs := p_bs||' hr_utility.set_location('''||
3534 l_dyt_name||''',ABS(SQLCODE));'||g_eol;
3535 p_bs := p_bs||' RAISE;'||g_eol;
3536 p_bs := p_bs||' --'||g_eol;
3537 p_bs := p_bs||'END '||l_dyt_name||';'||g_eos;
3538
3539 --Before we return the sql, replace any instances of :new, :old; this is because
3540 --we have used the existing mechanism to get initialisations and components
3541 --and these may well rely on 'dbms trigger' notation, however we are using a dyt pkg
3542 get_dyt_pkg_version_of_code(p_bs,p_tab_name,p_dyt_act);
3543 END gen_dyt_pkg_proc;
3544
3545 -- +---------------------------------------------------------------------------+
3546 -- | NAME : gen_dyt_pkg_rhi_proc |
3547 -- | SCOPE : PUBLIC |
3548 -- | DESCRIPTION: See header |
3549 -- +---------------------------------------------------------------------------+
3550 PROCEDURE gen_dyt_pkg_rhi_proc( p_tab_name IN VARCHAR2
3551 ,p_dyt_act IN VARCHAR2 ,p_dyt_info IN VARCHAR2
3552 ,p_hok_params IN g_params_tab_type
3553 ,p_hs IN OUT NOCOPY VARCHAR2
3554 ,p_bs IN OUT NOCOPY VARCHAR2
3555 ,p_dyt_params IN g_params_tab_type
3556 ,p_dyt_pkg_head_tbl IN OUT NOCOPY t_varchar2_32k_tbl
3557 ,p_dyt_pkg_body_tbl IN OUT NOCOPY t_varchar2_32k_tbl
3558 ,p_datetracked_table in VARCHAR2) IS
3559 --
3560 -- Get details of all dyn-triggers on given table,
3561 CURSOR csr_dyts_on_tab(cp_name in VARCHAR2,cp_action in VARCHAR2) IS
3562 SELECT pte.event_id, pte.short_name,
3563 pte.triggering_action, pte.description,
3564 DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info
3565 FROM pay_trigger_events pte
3566 WHERE pte.table_name = (cp_name)
3567 AND pte.triggering_action = (cp_action)
3568 AND nvl(pte.protected_flag,'N') <> 'Y';
3569
3570
3571 i number;
3572 j number;
3573 delim varchar2(15) := ' ';
3574
3575 BEGIN
3576 --
3577 -- Add an initial comment section to the trigger code
3578
3579 p_hs := p_hs||'/*'||g_eol;
3580 p_hs := p_hs||' ================================================'||g_eol;
3581 p_hs := p_hs||' This is a dynamically generated procedure. '||g_eol;
3582 p_hs := p_hs||' Will be called by API. '||g_eol;
3583 p_hs := p_hs||' ================================================'||g_eol;
3584 p_hs := p_hs||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3585 p_hs := p_hs||' ------------------------------------------------'||g_eol;
3586 p_hs := p_hs||' Name: AFTER_'||upper(p_dyt_info)||g_eol;
3587 p_hs := p_hs||' Table: '||p_tab_name||g_eol;
3588 p_hs := p_hs||' Action: '||p_dyt_info||g_eol;
3589 p_hs := p_hs||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3590 p_hs := p_hs||' Description: Called as part of '||p_dyt_info||' process'||g_eol;
3591 p_hs := p_hs||' ================================================'||g_eol;
3592 p_hs := p_hs||'*/'||g_eos;
3593 --
3594 p_hs := p_hs||'PROCEDURE AFTER_'||upper(p_dyt_info)||g_eol;
3595 -- Complete procedure definition, (still same for header and body)
3596 --
3597 --
3598 i := 0;
3599
3600 while i < p_hok_params.count LOOP
3601 --only need to print params that are relevant
3602 if (substr(p_hok_params(i).usage_type,2,1) = p_dyt_act) then
3603 -- quick formatting option
3604 if (length(p_hok_params(i).value_name) > 40) then
3605 j := 80; else j := 40;
3606 end if;
3607 if (delim = ' ') then p_hs := p_hs || '('||g_eol;
3608 end if;
3609
3610 p_hs := p_hs ||' '||delim||rpad(p_hok_params(i).value_name,j,' ')
3611 ||' in '||p_hok_params(i).data_type||g_eol;
3612 delim := ',';
3613
3614 end if;
3615 i := i+1;
3616 end loop;
3617
3618 p_hs := p_hs ||' )';
3619
3620 --End the header text, then continue with body, create two distinct strings
3621 p_bs := p_hs ||' IS '||g_eol;
3622 p_bs := p_bs ||' l_mode varchar2(80);'||g_eos;
3623 p_bs := p_bs ||' BEGIN'||g_eos;
3624
3625 p_bs := p_bs||' hr_utility.trace('' >DYT: Main entry point from row handler, AFTER_'||p_dyt_info||''');'||g_eol;
3626
3627 p_hs := p_hs||'; -- End of procedure definition for AFTER_'||upper(p_dyt_info)||g_eos;
3628
3629 --Create main body code, i.e. call to newly created dyn-trigger procedure(s).
3630 --
3631
3632 p_bs := p_bs ||' /* Mechanism for event capture to know whats occurred */'||g_eol;
3633 p_bs := p_bs ||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
3634 if (upper(p_dyt_info) = 'INSERT') then
3635 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_insert;'||g_eos;
3636 else
3637 if (p_datetracked_table = 'Y') then
3638 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := p_datetrack_mode;'||g_eos;
3639 else
3640 if (upper(p_dyt_info) = 'UPDATE') then
3641 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_correction;'||g_eos;
3642 else
3643 p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_zap;'||g_eos;
3644 end if;
3645 end if;
3646 end if;
3647 --
3648 -- Save the header details.
3649 p_dyt_pkg_head_tbl(p_dyt_pkg_head_tbl.last + 1) := p_hs;
3650 p_hs := '';
3651 --
3652 delim := 'X'; --will be reset if we have any dyt's
3653 FOR dyt_rec IN csr_dyts_on_tab(p_tab_name,p_dyt_act) LOOP
3654 delim :=' '; i := 0;--reset counters
3655
3656 -- Save the body details
3657 p_dyt_pkg_body_tbl(p_dyt_pkg_body_tbl.last + 1) := p_bs;
3658 p_bs := '';
3659 --
3660 p_bs := ' if (paywsdyg_pkg.trigger_enabled('''
3661 ||dyt_rec.short_name||''')) then'||g_eol;
3662 p_bs := p_bs||' '||get_dyt_proc_name(dyt_rec.short_name)||'('||g_eol;
3663
3664 -- build up params for call to newly created procedure
3665 WHILE i < p_dyt_params.count LOOP
3666 --only need to print params that are relevant for this action
3667 --though may not be strictly relevant for this dyn trig as we just pass all
3668 if (substr(p_dyt_params(i).usage_type,2,1) = p_dyt_act) then
3669 --
3670 if (length(p_dyt_params(i).param_form) > 40) then -- quick formatting option
3671 j := 80; else j := 40;
3672 end if;
3673 p_bs := p_bs ||' '||delim||rpad(p_dyt_params(i).param_form,j,' ')
3674 ||' => '||p_dyt_params(i).value_name||g_eol;
3675 delim := ',';
3676 end if;
3677
3678 i := i+1;
3679 END LOOP;
3680 p_bs := p_bs ||' );'||g_eol||' end if;'||g_eos;
3681 END LOOP;
3682 -- Written all calls to newly created relevant dyn-trigger, if none add null
3683 --
3684 if (delim = 'X') then
3685 p_bs := p_bs ||' /* no calls => no dynamic triggers of this type on this table */';
3686 p_bs := p_bs ||g_eol||' null;'||g_eos;
3687 end if;
3688 --Reset the flag
3689 p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eos;
3690
3691 -- Add a default exception block to catch all errors and write the
3692 -- trigger name and error text to the standard Oracle Apps error log
3693 p_bs := p_bs||'EXCEPTION'||g_eol;
3694 p_bs := p_bs||' WHEN OTHERS THEN'||g_eol;
3695 p_bs := p_bs||' hr_utility.set_location('''||
3696 'AFTER_'||upper(p_dyt_info)||''',ABS(SQLCODE));'||g_eol;
3697 p_bs := p_bs||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3698 p_bs := p_bs||' RAISE;'||g_eol;
3699 p_bs := p_bs||' --'||g_eol;
3700
3701 p_bs := p_bs ||'END AFTER_'||upper(p_dyt_info)||';'||g_eos;
3702
3703 -- Save the body details
3704 p_dyt_pkg_body_tbl(p_dyt_pkg_body_tbl.last + 1) := p_bs;
3705 p_bs := '';
3706
3707 END gen_dyt_pkg_rhi_proc;
3708
3709 -- +---------------------------------------------------------------------------+
3710 -- | NAME : gen_dyt_db_trig |
3711 -- | SCOPE : PUBLIC |
3712 -- | DESCRIPTION: See header |
3713 -- +---------------------------------------------------------------------------+
3714 PROCEDURE gen_dyt_db_trig(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
3715 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
3716 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
3717 ,p_dyn_pkg_params IN g_params_tab_type
3718 ,p_tab_dyt_pkg_name in varchar2
3719 ) IS
3720 --
3721 l_dyt_name pay_trigger_events.short_name%TYPE := get_dyt_proc_name(p_dyt_name);
3722 l_sql varchar2(32000); --Used as a temp holder.
3723 l_dbt_name varchar2(80);
3724 i number;
3725 j number;
3726 delim varchar2(15) := ' ';
3727
3728 BEGIN
3729 l_dbt_name := get_trigger_name(p_dyt_id,p_tab_name,p_dyt_act);
3730 --
3731 -- Add an initial comment section to the trigger code
3732
3733 l_sql := l_sql||'/*'||g_eol;
3734 l_sql := l_sql||' ================================================'||g_eol;
3735 l_sql := l_sql||' This is a dynamically generated database trigger'||g_eol;
3736 l_sql := l_sql||' ================================================'||g_eol;
3737 l_sql := l_sql||' ** DO NOT CHANGE MANUALLY ** '||g_eol;
3738 l_sql := l_sql||' ------------------------------------------------'||g_eol;
3739 l_sql := l_sql||' Trigger: '||l_dbt_name||g_eol;
3740 l_sql := l_sql||' Table: '||p_tab_name||g_eol;
3741 l_sql := l_sql||' Action: '||p_dyt_info||g_eol;
3742 l_sql := l_sql||' Generated Date: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||g_eol;
3743 l_sql := l_sql||' Description: If enabled, this trigger will be '||g_eol;
3744 l_sql := l_sql||' called as part of '||p_dyt_info||' process. '||g_eol;
3745 l_sql := l_sql||' ================================================'||g_eol;
3746 l_sql := l_sql||'*/'||g_eos;
3747 l_sql := l_sql||'DECLARE'||g_eol;
3748 l_sql := l_sql||' l_mode varchar2(80);'||g_eol;
3749 l_sql := l_sql||'BEGIN'||g_eol;
3750 --
3751 l_sql := l_sql||' l_mode := pay_dyn_triggers.g_dyt_mode;'||g_eol;
3752 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := pay_dyn_triggers.g_dbms_dyt;'||g_eol;
3753
3754 l_sql := l_sql||' IF NOT (hr_general.g_data_migrator_mode <> ''Y'') THEN'||g_eol;
3755 l_sql := l_sql||' RETURN;'||g_eol;
3756 l_sql := l_sql||' END IF;'||g_eos;
3757
3758 -- Code call to newly created procedure
3759 --
3760 l_sql := l_sql||p_tab_dyt_pkg_name||'.'||l_dyt_name||'('||g_eol;
3761 --
3762 -- build up params from those entered in dated tables form
3763 i := 0; delim :=' ';
3764
3765 while i < p_dyn_pkg_params.count LOOP
3766 --only need to print params that are relevant
3767 if (substr(p_dyn_pkg_params(i).usage_type,2,1) = p_dyt_act) then
3768 --Need to create the mock rhi-hook control params
3769 --NB think P_NEW_EFFECTIVE_DATE is now redundant, but leave in as no overhead
3770 --
3771 if (upper(p_dyn_pkg_params(i).param_form) = 'P_NEW_EFFECTIVE_DATE') then
3772 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,40,' ')
3773 ||' => :new.effective_start_date'||g_eol;
3774 delim := ',';
3775 elsif (upper(p_dyn_pkg_params(i).param_form) = 'P_NEW_DATETRACK_MODE') then
3776 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,40,' ')
3777 ||' => pay_dyn_triggers.g_dbms_dyt'||g_eol;
3778 delim := ',';
3779 else --MAIN useful db cols
3780 -- quick formatting option
3781 if (length(p_dyn_pkg_params(i).param_form) > 40) then
3782 j := 80; else j := 40;
3783 end if;
3784 l_sql := l_sql ||' '||delim||rpad(p_dyn_pkg_params(i).param_form,j,' ')
3785 ||' => '||p_dyn_pkg_params(i).local_form||g_eol;
3786 delim := ',';
3787 end if;
3788 end if;
3789
3790 i := i+1;
3791 end loop;
3792
3793 --End the trigger text
3794 l_sql := l_sql||'); -- End of call to dynamic trigger code stored in package '||g_eos;
3795 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3796
3797 -- Add a default exception block to catch all errors and write the
3798 -- trigger name and error text to the standard Oracle Apps error log
3799 l_sql := l_sql||'EXCEPTION'||g_eol;
3800 l_sql := l_sql||' WHEN OTHERS THEN'||g_eol;
3801 l_sql := l_sql||' hr_utility.set_location('''||
3802 l_dbt_name||''',ABS(SQLCODE));'||g_eol;
3803 l_sql := l_sql||' pay_dyn_triggers.g_dyt_mode := l_mode;'||g_eol;
3804 l_sql := l_sql||' RAISE;'||g_eol;
3805 l_sql := l_sql||' --'||g_eol;
3806
3807 l_sql := l_sql||'END;'||g_eol;
3808
3809 --Use common procedure to create trigger
3810 --
3811 create_trigger(l_dbt_name,p_tab_name,p_dyt_act,l_sql);
3812 end gen_dyt_db_trig;
3813
3814 -- +---------------------------------------------------------------------------+
3815 -- | NAME : trigger_enabled |
3816 -- | SCOPE : PUBLIC |
3817 -- | DESCRIPTION: Simply returns boolean to see if dyn trigger is enabled. |
3818 -- | Called by dynamically created rhi proc in dynamic package |
3819 -- | PARAMETERS : p_dyt - The dynamic trigger name |
3820 -- | RETURNS : TRUE if trigger is enabled, FALSE otherwise |
3821 -- | RAISES : None |
3822 -- +---------------------------------------------------------------------------+
3823 FUNCTION trigger_enabled(p_dyt varchar2) return BOOLEAN
3824 IS
3825 cursor csr_enabled(cp_dyt varchar2) is
3826 SELECT enabled_flag
3827 FROM pay_trigger_events
3828 where short_name = cp_dyt;
3829
3830 l_flag varchar2(1);
3831
3832 BEGIN
3833 open csr_enabled(p_dyt);
3834 fetch csr_enabled into l_flag;
3835 close csr_enabled;
3836 return (flag_to_boolean(l_flag));
3837
3838 END trigger_enabled;
3839
3840 -- +---------------------------------------------------------------------------+
3841 -- | name : convert_tab_style |
3842 -- | scope : public |
3843 -- | description: there are times when the seeded behaviour needs to be altered|
3844 -- | usually as a result of release issues. this procedure provides a quick |
3845 -- | wrapper utility to change a dated table from dbms_dyt to dyt_pkg and vice|
3846 -- | versa.
3847 -- | parameters : p_table_name - the dated table name |
3848 -- | : p_dyt_type - eg t<dbms trigger> p<ackage> b<oth> |
3849 -- | returns : none
3850 -- | raises : none |
3851 -- +---------------------------------------------------------------------------+
3852 procedure convert_tab_style(p_table_name in varchar2,p_dyt_type in varchar2)
3853 is
3854
3855 l_name varchar2(240);
3856 l_hooks varchar2(15);
3857 l_ok boolean;
3858 l_api_module_id number;
3859
3860 cursor csr_dyt_ids(cp_table_name in varchar2) is
3861 select event_id,short_name from pay_trigger_events
3862 where table_name = cp_table_name;
3863 cursor csr_get_id is
3864 select api_module_id
3865 from hr_api_modules
3866 where module_name = p_table_name;
3867
3868 begin
3869 hr_utility.trace('>>> set table '||p_table_name||' to be style '||p_dyt_type);
3870 --
3871 update pay_dated_tables
3872 set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
3873 where table_name = p_table_name;
3874
3875 hr_utility.trace(' creating dyt triggers...');
3876 --
3877 for dyt_record in csr_dyt_ids(p_table_name) loop
3878 paywsdyg_pkg.generate_trigger(
3879 dyt_record.event_id,
3880 l_name,
3881 l_ok);
3882 hr_utility.trace(' just created a dynamic trigger for id: '||dyt_record.event_id||', dyt_name: '||dyt_record.short_name||', into: '||l_name);
3883 if (p_dyt_type = 'P') then exit; end if;
3884 end loop;
3885 hr_utility.trace('>>> completed trigger building for table '||p_table_name);
3886
3887
3888 if (p_dyt_type = 'P') then
3889 l_hooks := 'Y';
3890 else
3891 l_hooks := 'N';
3892 end if;
3893
3894 update hr_api_hook_calls
3895 set enabled_flag = l_hooks
3896 where api_hook_call_id in (
3897 select api_hook_call_id
3898 from hr_api_hook_calls ahc,
3899 hr_api_hooks ah,
3900 hr_api_modules am
3901 where ahc.api_hook_id = ah.api_hook_id
3902 and ah.api_module_id = am.api_module_id
3903 and ahc.call_package = (select dyn_trigger_package_name
3904 from pay_dated_tables
3905 where table_name = am.module_name)
3906 and am.module_name = p_table_name );
3907
3908 for module in csr_get_id loop
3909 --Rebuild Hooks Packages
3910 hr_api_user_hooks_utility.create_hooks_add_report(l_api_module_id);
3911
3912 end loop;
3913
3914 end convert_tab_style;
3915
3916 -- +---------------------------------------------------------------------------+
3917 -- | name : confirm_dyt_data |
3918 -- | scope : public (Use cautiously, designed as dev util) |
3919 -- | description: there are times when the seeded behaviour needs to be altered|
3920 -- | usually as a result of release issues. this procedure checks the data
3921 -- | for a given table and depending on the main switch (hook calls to DYT_PKG)
3922 -- | rebuilds the data for DYT_PKG behaviour (if calls existed) or DBMS dynamic
3923 -- | triggers (if no calls existed)
3924 -- | parameters : p_table_name - the dated table name |
3925 -- | returns : none
3926 -- | raises : none |
3927 -- +---------------------------------------------------------------------------+
3928 PROCEDURE confirm_dyt_data(p_table_name in varchar2) is
3929
3930 l_reqd_format varchar2(1); --EG will be set to 'T' dbms Trigger, 'P' Package
3931 l_current_format varchar2(1);
3932 l_dyt_pkg_exists varchar2(1); --EG will be set to 'Y' or 'N'
3933
3934
3935 cursor csr_dyt_pkg_hook is
3936 select count(*)
3937 from hr_api_hook_calls ahc,
3938 hr_api_hooks ah,
3939 hr_api_modules am
3940 where ahc.api_hook_id = ah.api_hook_id
3941 and ah.api_module_id = am.api_module_id
3942 and ahc.call_package = (select dyn_trigger_package_name
3943 from pay_dated_tables
3944 where table_name = am.module_name)
3945 and am.module_name = p_table_name;
3946 l_hook_count number;
3947
3948 cursor csr_tab_details is
3949 select dated_table_id,dyn_trigger_type,dyn_trigger_package_name,dyn_trig_pkg_generated
3950 from pay_dated_tables
3951 where table_name = p_table_name;
3952 l_dt_id pay_dated_tables.dated_table_id%type;
3953 l_dt_dyt_type pay_dated_tables.dyn_trigger_type%type;
3954 l_dt_pkg_name pay_dated_tables.dyn_trigger_package_name%type;
3955 l_dt_pkg_gen pay_dated_tables.dyn_trig_pkg_generated%type;
3956
3957 cursor csr_pkg_exist(cp_pkg in varchar2) is
3958 select status from user_objects
3959 where object_type = 'PACKAGE BODY'
3960 and object_name = cp_pkg;
3961 l_pkg_status all_objects.status%type;
3962
3963 l_need_rebuild_flag varchar2(15) := 'N';
3964 l_result boolean;
3965 l_prod_status varchar2(1);
3966 l_industry varchar2(1);
3967
3968 l_proc varchar2(240) := g_package||'.confirm_dyt_data';
3969 BEGIN
3970 hr_utility.set_location(l_proc,10);
3971 --
3972 -- >>> PHASE 1: Decide what is the reqd format for this table for dynamic trigger
3973 --
3974 open csr_dyt_pkg_hook;
3975 fetch csr_dyt_pkg_hook into l_hook_count;
3976 close csr_dyt_pkg_hook;
3977
3978 hr_utility.trace( '- Decision on what is the required behaviour based on enabled hook count.');
3979 if (l_hook_count > 0) then
3980 l_reqd_format := 'P';
3981 else
3982 l_reqd_format := 'T';
3983 end if;
3984 hr_utility.trace( '- Hook count is '||l_hook_count||' so REQD behaviour is '||l_reqd_format);
3985
3986
3987 -- >>> PHASE 2: Get the exisitng information for this table
3988 --
3989 open csr_tab_details;
3990 fetch csr_tab_details into l_dt_id, l_dt_dyt_type, l_dt_pkg_name, l_dt_pkg_gen;
3991 close csr_tab_details;
3992 hr_utility.trace( '- Dated table id '||l_dt_id||' has SEEDED behaviour '||l_dt_dyt_type);
3993
3994 open csr_pkg_exist(l_dt_pkg_name);
3995 fetch csr_pkg_exist into l_pkg_status;
3996 close csr_pkg_exist;
3997
3998 if (l_pkg_status is null) then l_pkg_status := 'NONE';
3999 end if;
4000 --hr_utility.trace( '- DYT_PKG '||l_dt_pkg_name||' has status '||l_pkg_status);
4001
4002 --Now do phase 3 if l_reqd_format = T, phase 4 if its = P
4003 if (l_reqd_format = 'T') then
4004 -- >>> PHASE 3: Deal with situation where we wish for NO dyt_pkg
4005 --
4006 -- || The DYT_PKG exists || No DYT_PKG exists ||
4007 --++===============||===============================================||
4008 -- Table is also || [A] bit odd || [B] Perfect ||
4009 -- set to T || no danger || Behaviour ||
4010 -- eg dbms_dyt || => nothing || ||
4011 --++===============||=======================||======================||
4012 -- Table is NOT || [C] Bad || [D] Very Bad ||
4013 -- set to T || => change table to || => change table to ||
4014 -- eg dyt_pkg || be dbms style || be dbms style ||
4015 --++===============++===============================================++
4016 hr_utility.trace(' NO HOOKS so desired DBMS style dynamic triggers.');
4017 hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4018 if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4019 l_need_rebuild_flag := 'N'; --odd but not terminal
4020
4021 elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4022 hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4023 l_need_rebuild_flag := 'N'; --odd but not terminal
4024 --
4025 elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4026 l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
4027 --
4028 elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
4029 l_need_rebuild_flag := 'Y'; -- BAD so change to dbms triggers
4030 --
4031 end if;
4032 elsif (l_reqd_format = 'P') then
4033 -- >>> PHASE 4: Deal with situation where we wish for a dyt_pkg
4034 --
4035 -- || The DYT_PKG exists || No DYT_PKG exists ||
4036 --++===============||===============================================||
4037 -- Table is also || [A] bit odd || [B] Bad ||
4038 -- set to T || pkg exists and calls || hooks will fail ||
4039 -- eg dbms_dyt || => change to dyt_pkg || => change to dyt_pkg ||
4040 --++===============||=======================||======================||
4041 -- Table is NOT || [C] Perfect || [D] Very Bad ||
4042 -- set to T || || hooks will fail ||
4043 -- eg dyt_pkg || || => change to dyt_pkg ||
4044 --++===============++===============================================++
4045
4046 hr_utility.trace(' HOOKS EXIST so desired DYT_PKG style dynamic triggers.');
4047 hr_utility.trace( p_table_name||' has style set to '||l_dt_dyt_type||' and the dyt_pkg is '||l_pkg_status);
4048 if (l_dt_dyt_type = 'T' and l_pkg_status = 'VALID') then
4049 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4050 --
4051 elsif (l_dt_dyt_type = 'T' and l_pkg_status <> 'VALID') then
4052 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4053 --
4054 elsif (l_dt_dyt_type = 'P' and l_pkg_status = 'VALID') then
4055 l_need_rebuild_flag := 'N';
4056 hr_utility.trace( p_table_name||' has perfect dynamic trigger data');
4057 --
4058 elsif (l_dt_dyt_type = 'P' and l_pkg_status <> 'VALID') then
4059 l_need_rebuild_flag := 'Y'; -- BAD so change to dyt_pkg
4060 --
4061 end if;
4062 end if;
4063
4064 if (l_need_rebuild_flag = 'Y') then
4065 paywsdyg_pkg.convert_tab_style(
4066 p_table_name => p_table_name,
4067 p_dyt_type => l_reqd_format);
4068 end if;
4069
4070 -- To cover the exception to the rule...namely pay_element_entry_values_f
4071 -- which does not have hooks and thus this procedure should never be called for
4072 -- this table. If this is called for pay_element_entries_f then make the
4073 -- vals table have the same behaviour.
4074 if (p_table_name = 'PAY_ELEMENT_ENTRIES_F') then
4075 paywsdyg_pkg.convert_tab_style(
4076 p_table_name => 'PAY_ELEMENT_ENTRY_VALUES_F',
4077 p_dyt_type => l_reqd_format);
4078 end if;
4079
4080 hr_utility.set_location(l_proc,900);
4081 END confirm_dyt_data;
4082
4083 --
4084 --
4085 -- does declaration exist for the specified variable
4086 function is_not_declared(p_id in number,p_name in varchar2) return boolean is
4087 --
4088 cursor csr_pay(cp_id in number,cp_name in varchar2) is
4089 select 'x'
4090 from pay_trigger_declarations
4091 where variable_name = cp_name
4092 and event_id = cp_id;
4093 --
4094 l_cx varchar2(1);
4095 l_rc boolean;
4096 begin
4097 open csr_pay(p_id,p_name);
4098 fetch csr_pay into l_cx;
4099 if csr_pay%notfound then
4100 l_rc := true;
4101 else
4102 l_rc := false;
4103 end if;
4104 close csr_pay;
4105 --
4106 return l_rc;
4107 end is_not_declared;
4108
4109 --
4110 -- if the table's got a business group id then that's fine,
4111 -- otherwise check if the user has defined their own local
4112 -- variable that we can use
4113 function no_business_context(p_table in varchar2,p_id in number) return boolean is
4114 l_rc boolean;
4115 begin
4116 if table_has_business_group(p_table) then
4117 l_rc := false;
4118 else
4119 l_rc := is_not_declared(p_id,'business_group_id');
4120 end if;
4121 --
4122 return l_rc;
4123 end no_business_context;
4124 --
4125 -- if the table's got a business group id then that's fine,
4126 -- otherwise check if the user has defined their own local
4127 -- variable that we can use
4128 function no_legislation_context(p_table in varchar2,p_id in number) return boolean is
4129 l_rc boolean;
4130 begin
4131 if table_has_business_group(p_table) then
4132 l_rc := false;
4133 else
4134 l_rc := is_not_declared(p_id,'legislation_code');
4135 end if;
4136 --
4137 return l_rc;
4138 end no_legislation_context;
4139 --
4140 -- if the table's got a payroll id then that's fine,
4141 -- otherwise check if the user has defined their own local
4142 -- variable that we can use
4143 function no_payroll_context(p_table in varchar2,p_id in number) return boolean is
4144 --
4145 l_rc boolean;
4146 begin
4147 if table_has_payroll(p_table) then
4148 l_rc := false;
4149 else
4150 l_rc := is_not_declared(p_id,'payroll_id');
4151 end if;
4152 --
4153 return l_rc;
4154 end no_payroll_context;
4155 --
4156 procedure ins(
4157 p_event_id in number,
4158 p_table_name in varchar2,
4159 p_short_name in varchar2,
4160 p_description in varchar2,
4161 p_generated_flag in varchar2,
4162 p_enabled_flag in varchar2,
4163 p_protected_flag in varchar2,
4164 p_triggering_action in varchar2,
4165 p_last_update_date in date,
4166 p_last_updated_by in number,
4167 p_last_update_login in number,
4168 p_created_by in number,
4169 p_creation_date in date
4170 ) is
4171 begin
4172 insert into pay_trigger_events (
4173 event_id,
4174 table_name,
4175 short_name,
4176 description,
4177 generated_flag,
4178 enabled_flag,
4179 protected_flag,
4180 triggering_action,
4181 last_update_date,
4182 last_updated_by,
4183 last_update_login,
4184 created_by,
4185 creation_date
4186 ) values (
4187 p_event_id,
4188 p_table_name,
4189 p_short_name,
4190 p_description,
4191 p_generated_flag,
4192 p_enabled_flag,
4193 p_protected_flag,
4194 p_triggering_action,
4195 p_last_update_date,
4196 p_last_updated_by,
4197 p_last_update_login,
4198 p_created_by,
4199 p_creation_date
4200 );
4201 end ins;
4202 --
4203 procedure upd(
4204 p_event_id in number,
4205 p_table_name in varchar2,
4206 p_short_name in varchar2,
4207 p_description in varchar2,
4208 p_generated_flag in varchar2,
4209 p_enabled_flag in varchar2,
4210 p_protected_flag in varchar2,
4211 p_triggering_action in varchar2,
4212 p_last_update_date in date,
4213 p_last_updated_by in number,
4214 p_last_update_login in number,
4215 p_created_by in number,
4216 p_creation_date in date
4217 ) is
4218 begin
4219 update pay_trigger_events
4220 set table_name = p_table_name,
4221 short_name = p_short_name,
4222 description = p_description,
4223 generated_flag = p_generated_flag,
4224 enabled_flag = p_enabled_flag,
4225 protected_flag = p_protected_flag,
4226 triggering_action = p_triggering_action,
4227 last_update_date = p_last_update_date,
4228 last_updated_by = p_last_updated_by,
4229 last_update_login = p_last_update_login,
4230 created_by = p_created_by,
4231 creation_date = p_creation_date
4232 where event_id = p_event_id;
4233 end upd;
4234 --
4235 procedure del(
4236 p_event_id in number
4237 ) is
4238 begin
4239 delete from pay_trigger_events
4240 where event_id = p_event_id;
4241 end del;
4242 --
4243 procedure lck(
4244 p_event_id in number
4245 ) is
4246 cursor c_sel1 is
4247 select *
4248 from pay_trigger_events
4249 where event_id = p_event_id
4250 for update nowait;
4251 l_old_rec c_sel1%rowtype;
4252 --
4253 begin
4254 --
4255 open c_sel1;
4256 fetch c_sel1 into l_old_rec;
4257 if c_sel1%notfound then
4258 close c_sel1;
4259 --
4260 -- the primary key is invalid therefore we must error
4261 --
4262 fnd_message.set_name('pay', 'hr_7220_invalid_primary_key');
4263 fnd_message.raise_error;
4264 end if;
4265 close c_sel1;
4266 --
4267 --
4268 -- we need to trap the ora lock exception
4269 --
4270 exception
4271 when hr_api.object_locked then
4272 --
4273 -- the object is locked therefore we need to supply a meaningful
4274 -- error message.
4275 --
4276 fnd_message.set_name('pay', 'hr_7165_object_locked');
4277 fnd_message.set_token('table_name', 'pay_trigger_events');
4278 fnd_message.raise_error;
4279 end lck;
4280 --
4281 FUNCTION is_table_valid(p_table IN VARCHAR2) RETURN VARCHAR2 IS
4282 --
4283 l_rc NUMBER(15);
4284 --
4285 CURSOR csr_chk_tabname IS
4286 SELECT 1
4287 FROM dual
4288 WHERE EXISTS (
4289 SELECT 1
4290 FROM fnd_tables tab
4291 WHERE tab.table_name = p_table
4292 AND (tab.application_id BETWEEN 800 AND 810
4293 OR tab.application_id IN (8301,453,8302,8303,8403,203))
4294 );
4295 --
4296 CURSOR csr_get_tabapp IS
4297 SELECT application_id
4298 FROM fnd_tables
4299 WHERE table_name = p_table
4300 AND ((application_id < 800 OR application_id > 810)
4301 AND application_id NOT IN (8301,453,8302,8303,8403,203));
4302 --
4303 BEGIN
4304 --
4305 OPEN csr_chk_tabname;
4306 FETCH csr_chk_tabname INTO l_rc;
4307 IF csr_chk_tabname%NOTFOUND THEN
4308 CLOSE csr_chk_tabname;
4309 --
4310 -- Not in normal range get the app id and check the profile
4311 OPEN csr_get_tabapp;
4312 FETCH csr_get_tabapp INTO l_rc;
4313 IF csr_get_tabapp%NOTFOUND THEN
4314 CLOSE csr_get_tabapp;
4315 RETURN 'N';
4316 END IF;
4317 CLOSE csr_get_tabapp;
4318 --
4319 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4320 application_id=>l_rc)
4321 THEN
4322 RETURN 'Y';
4323 END IF;
4324 --
4325 RETURN 'N';
4326 END IF;
4327 --
4328 CLOSE csr_chk_tabname;
4329 RETURN 'Y';
4330 END is_table_valid;
4331 --
4332 FUNCTION is_table_column_valid(p_table IN VARCHAR2,p_column IN VARCHAR2) RETURN VARCHAR2 IS
4333 --
4334 l_rc NUMBER(15);
4335 --
4336 CURSOR csr_chk_column IS
4337 SELECT 1
4338 FROM dual
4339 WHERE EXISTS (
4340 SELECT 1
4341 FROM fnd_columns col,fnd_tables tab
4342 WHERE tab.table_name = p_table
4343 AND col.table_id = tab.table_id
4344 AND col.application_id = tab.application_id
4345 AND col.column_name = p_column
4346 AND (tab.application_id BETWEEN 800 AND 810
4347 OR tab.application_id IN (8301,453,8302,8303,8403,203))
4348 );
4349 --
4350 CURSOR csr_get_colapp IS
4351 SELECT tab.application_id
4352 FROM fnd_columns col,fnd_tables tab
4353 WHERE tab.table_name = p_table
4354 AND col.table_id = tab.table_id
4355 AND col.application_id = tab.application_id
4356 AND ((tab.application_id < 800 OR tab.application_id > 810)
4357 AND tab.application_id NOT IN (8301,453,8302,8303,8403,203))
4358 AND col.column_name = p_column;
4359 --
4360 BEGIN
4361 --
4362 OPEN csr_chk_column;
4363 FETCH csr_chk_column INTO l_rc;
4364 IF csr_chk_column%NOTFOUND THEN
4365 CLOSE csr_chk_column;
4366 --
4367 -- Not in normal range get the app id and check the profile
4368 OPEN csr_get_colapp;
4369 FETCH csr_get_colapp INTO l_rc;
4370 IF csr_get_colapp%NOTFOUND THEN
4371 CLOSE csr_get_colapp;
4372 RETURN 'N';
4373 END IF;
4374 CLOSE csr_get_colapp;
4375 --
4376 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4377 application_id=>l_rc)
4378 THEN
4379 RETURN 'Y';
4380 END IF;
4381 --
4382 RETURN 'N';
4383 END IF;
4384 --
4385 CLOSE csr_chk_column;
4386 RETURN 'Y';
4387 END is_table_column_valid;
4388 --
4389 FUNCTION is_table_owner_valid(p_table IN VARCHAR2,p_owner IN VARCHAR2) RETURN VARCHAR2 IS
4390 --
4391 l_rc NUMBER(15);
4392 --
4393 CURSOR csr_chk_owner IS
4394 SELECT 1
4395 FROM dual
4396 WHERE EXISTS (
4397 SELECT 1
4398 FROM fnd_tables tab,
4399 fnd_product_installations prd,
4400 fnd_oracle_userid usr
4401 WHERE tab.table_name = p_table
4402 AND tab.application_id = prd.application_id
4403 AND (prd.application_id BETWEEN 800 AND 810
4404 OR prd.application_id IN (8301,453,8302,8303,8403,203))
4405 AND usr.oracle_id = prd.oracle_id
4406 AND usr.oracle_username = p_owner
4407 );
4408 --
4409 CURSOR csr_get_ownapp IS
4410 SELECT prd.application_id
4411 FROM fnd_tables tab,
4412 fnd_product_installations prd,
4413 fnd_oracle_userid usr
4414 WHERE tab.table_name = p_table
4415 AND tab.application_id = prd.application_id
4416 AND ((prd.application_id < 800 OR prd.application_id > 810)
4417 AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
4418 AND usr.oracle_id = prd.oracle_id
4419 AND usr.oracle_username = p_owner;
4420 --
4421 BEGIN
4422 OPEN csr_chk_owner;
4423 FETCH csr_chk_owner INTO l_rc;
4424 IF csr_chk_owner%NOTFOUND THEN
4425 CLOSE csr_chk_owner;
4426 --
4427 -- Not in normal range, get app id and check profile
4428 OPEN csr_get_ownapp;
4429 FETCH csr_get_ownapp INTO l_rc;
4430 IF csr_get_ownapp%NOTFOUND THEN
4431 CLOSE csr_get_ownapp;
4432 RETURN 'N';
4433 END IF;
4434 CLOSE csr_get_ownapp;
4435 --
4436 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4437 application_id=>l_rc)
4438 THEN
4439 RETURN 'Y';
4440 END IF;
4441 --
4442 RETURN 'N';
4443 --
4444 END IF;
4445 CLOSE csr_chk_owner;
4446 --
4447 RETURN 'Y';
4448 END is_table_owner_valid;
4449 --
4450 FUNCTION get_table_owner(p_table IN VARCHAR2) RETURN VARCHAR2 IS
4451 --
4452 l_schema VARCHAR2(30);
4453 l_app NUMBER(15);
4454 --
4455 CURSOR csr_get_owner IS
4456 SELECT usr.oracle_username
4457 FROM fnd_tables tab,
4458 fnd_product_installations prd,
4459 fnd_oracle_userid usr
4460 WHERE tab.table_name = p_table
4461 AND tab.application_id = prd.application_id
4462 AND (prd.application_id BETWEEN 800 AND 810
4463 OR prd.application_id IN (8301,453,8302,8303,8403,203))
4464 AND usr.oracle_id = prd.oracle_id;
4465 --
4466 CURSOR csr_get_ownex IS
4467 SELECT usr.oracle_username,
4468 prd.application_id
4469 FROM fnd_tables tab,
4470 fnd_product_installations prd,
4471 fnd_oracle_userid usr
4472 WHERE tab.table_name = p_table
4473 AND tab.application_id = prd.application_id
4474 AND ((prd.application_id < 800 OR prd.application_id > 810)
4475 AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
4476 AND usr.oracle_id = prd.oracle_id;
4477 --
4478 BEGIN
4479 OPEN csr_get_owner;
4480 FETCH csr_get_owner INTO l_schema;
4481 IF csr_get_owner%NOTFOUND THEN
4482 CLOSE csr_get_owner;
4483 --
4484 OPEN csr_get_ownex;
4485 FETCH csr_get_ownex INTO l_schema,l_app;
4486 IF csr_get_ownex%NOTFOUND THEN
4487 CLOSE csr_get_ownex;
4488 RETURN NULL;
4489 END IF;
4490 CLOSE csr_get_ownex;
4491 --
4492 IF 'Y' = Fnd_Profile.Value_Specific(name=>'PAY_ENABLE_DYNAMIC_TRIGGERS',
4493 application_id=>l_app)
4494 THEN
4495 RETURN l_schema;
4496 END IF;
4497 RETURN NULL;
4498 END IF;
4499 CLOSE csr_get_owner;
4500 --
4501 RETURN l_schema;
4502 END get_table_owner;
4503 --
4504 end paywsdyg_pkg;
[Home] [Help]