1 PACKAGE paywsdyg_pkg AS
2 -- $Header: pydygpkg.pkh 120.0 2005/05/29 04:25:40 appldev noship $
3 --
4 --
5 -- +---------------------------------------------------------------------------+
6 -- | Global Constants |
7 -- +---------------------------------------------------------------------------+
8 type t_varchar2_32k_tbl is table of varchar2(32767) index by binary_integer;
9 --
10 -- +---------------------------------------------------------------------------+
11 -- | NAME : insert_parameters |
12 -- | DESCRIPTION: Helper procedure to maintain parameters |
13 -- | Inserts new rows if none exist, otherwise updates the |
14 -- | existing row if it's 'automatic' flag is 'not 'N' ('Y'or null) |
15 -- | PARAMETERS : p_usage_type - Value for the usage_type column |
16 -- | p_usage_id - Value for the usage_id column |
17 -- | p_parameter_type - Value for the parameter_type column |
18 -- | p_parameter_name - Value for the parameter_name column |
19 -- | p_value_name - Value for the value_name column |
20 -- | p_automatic - Value for the automatic column |
21 -- | RETURNS : The primary key of the new or existing row |
22 -- | RAISES : None |
23 -- +---------------------------------------------------------------------------+
24 FUNCTION insert_parameters(
25 p_usage_type IN VARCHAR2 DEFAULT NULL,
26 p_usage_id IN NUMBER DEFAULT NULL,
27 p_parameter_type IN VARCHAR2 DEFAULT NULL,
28 p_parameter_name IN VARCHAR2 DEFAULT NULL,
29 p_value_name IN VARCHAR2 DEFAULT NULL,
30 p_automatic IN VARCHAR2 DEFAULT NULL
31 ) RETURN NUMBER;
32
33
34 -- +---------------------------------------------------------------------------+
35 -- | NAME : drop_trigger |
36 -- | DESCRIPTION: Drops the database trigger with the name specified. First |
37 -- | checks that the trigger specified exists, then uses the AOL |
38 -- | procedure ad_ddl.do_ddl to execute a DROP TRIGGER command. |
39 -- | This will drop any trigger (assuming that the executing user |
40 -- | has the correct permissions) not just those created by the |
41 -- | Dynamic Trigger Generation system. |
42 -- | PARAMETERS : p_name - The name of the trigger to drop, case insensitive |
43 -- | RETURNS : None |
44 -- | RAISES : None - The AOL routine traps and suppresses any exceptions |
45 -- +---------------------------------------------------------------------------+
46 PROCEDURE drop_trigger(p_name IN VARCHAR2);
47 --
48 -- +---------------------------------------------------------------------------+
49 -- | NAME : drop_trigger_indirect |
50 -- | DESCRIPTION: Drops the database trigger based only on it's primary key. |
51 -- | PARAMETERS : p_id - The primary key of the trigger to drop. |
52 -- | RETURNS : None |
53 -- | RAISES : None - The AOL routine traps and suppresses any exceptions |
54 -- +---------------------------------------------------------------------------+
55 PROCEDURE drop_trigger_indirect(p_id IN NUMBER);
56 --
57 -- +---------------------------------------------------------------------------+
58 -- | NAME : create_trigger |
59 -- | DESCRIPTION: Creates (or replaces) the trigger with the specified name |
60 -- | on the required table. |
61 -- | The trigger is always created as an 'After each row' type |
62 -- | trigger, the triggering action is specified via the |
63 -- | abbreviation I, U or D. |
64 -- | The trigger is created using the supplied PL/SQL block which |
65 -- | must not contain the CREATE TRIGGER clause (or the CREATE |
66 -- | statement will raise an exception since this procedure adds |
67 -- | that code itself). The trigger name |
68 -- | should have been obtained via a call to "get_trigger_name" |
69 -- | so that it is in the standard format for Dynamically |
70 -- | Generated triggers, but that isn't vaildated here. |
71 -- | PARAMETERS : p_trigger - Name of the trigger to create |
72 -- | p_table - Table to add the trigger to |
73 -- | p_action - Triggering action, I U or D for Insert Update or |
74 -- | Delete respectively |
75 -- | p_sql - PL/SQL block to use for the trigger body |
76 -- | RETURNS : None |
77 -- | RAISES : Standard - Any errors encountered by the trigger creation DDL|
78 -- +---------------------------------------------------------------------------+
79 PROCEDURE create_trigger(
80 p_trigger IN VARCHAR2,
81 p_table IN VARCHAR2,
82 p_action IN VARCHAR2,
83 p_sql IN VARCHAR2
84 );
85 -- +---------------------------------------------------------------------------+
86 -- | NAME : get_trigger_name |
87 -- | DESCRIPTION: Generates the trigger name so that they're always created |
88 -- | in the same format |
89 -- | PARAMETERS : p_id - Primary key of the event that owns the trigger |
90 -- | p_table - Table that the trigger will be created against |
91 -- | p_action - The triggering action I, U or D for Insert, Update|
92 -- | or Delete |
93 -- | RETURNS : The generated trigger name |
94 -- | RAISES : Nothing - n/a |
95 -- +---------------------------------------------------------------------------+
96 FUNCTION get_trigger_name(
97 p_id IN NUMBER,
98 p_table IN VARCHAR2,
99 p_action IN VARCHAR2
100 ) RETURN VARCHAR2;
101 --
102 -- +---------------------------------------------------------------------------+
103 -- | NAME : enable_trigger |
104 -- | DESCRIPTION: Enables or disables the specified trigger. Uses the standard |
105 -- | DDL 'ALTER TRIGGER ... ENABLE/DISABLE' command (executed |
106 -- | using the AOL do_ddl procedure). Any trigger can be enabled |
107 -- | or disabled, if the user has correct permissions, not just |
108 -- | those generated dynamically. |
109 -- | PARAMETERS : p_trigger - Trigger name |
110 -- | p_enabled - TRUE to enable the trigger, FALSE to disable |
111 -- | RETURNS : None |
112 -- | RAISES : Standard - Any errors encountered by the ALTER TRIGGER |
113 -- | statement DDL |
114 -- +---------------------------------------------------------------------------+
115 PROCEDURE enable_trigger(p_trigger IN VARCHAR2,p_enabled IN BOOLEAN);
116 --
117 -- +---------------------------------------------------------------------------+
118 -- | NAME : map_parameter_list |
119 -- | DESCRIPTION: Describes the PL/SQL stored module (packaged procedure, |
120 -- | function or standalone module) and attempts to create |
121 -- | mappings between the module parameters and the trigger code. |
122 -- | Also checks that the module is valid for the type of |
123 -- | operation it is being included in. |
124 -- | Will not create the parameters if p_validate_only is TRUE, |
125 -- | only the validation operations are carried out. |
126 -- | This procedure could be used during seed data creation by; |
127 -- | creating an event record, creating a component child of this |
128 -- | event, calling this routine passing the details of the new |
129 -- | component record. Default mappings for the parameters will |
130 -- | be created but this DOES NOT MEAN that the trigger can be |
131 -- | generated and compiled successfully. The AutoMapper may (in |
132 -- | it's current state) create mappings to local variables that |
133 -- | do not exist. It is the developer's responsibility (just as |
134 -- | it is the user's when using the Forms front end) to create |
135 -- | the necessary local declaration records before attempting to |
136 -- | generate and enable the trigger. |
137 -- | PARAMETERS : p_id - The primary key of the associated component|
138 -- | p_module - Name of module to be called |
139 -- | p_type - Type of module F or P for Function or |
140 -- | Procedure, C type usages can only be of |
141 -- | type P, although this is not enforced here |
142 -- | p_usage - The way in which the module will be used, |
143 -- | I for initialisation or C for component |
144 -- | p_validate_only - Set to TRUE to stop parameters being |
145 -- | created but still raise any errors that |
146 -- | occur, used during Forms validation. |
147 -- | Defaulted to FALSE so that, by default the |
148 -- | parameter mappings will be created. |
149 -- | RETURNS : None |
150 -- | RAISES : could_not_describe_module - Usually because the module |
151 -- | does not exist |
152 -- | module_is_overloaded - The Dynamic Trigger Generator |
153 -- | cannot use overloaded modules |
154 -- | unsupported_parameter_type - Only VARCHAR2, NUMBER and DATE |
155 -- | type parameters can be used |
156 -- | incompatible_parameter_mode - The IN/OUT mode of one or more |
157 -- | parameters is not compatible |
158 -- | with the way in which the |
159 -- | module is being used |
160 -- +---------------------------------------------------------------------------+
161 PROCEDURE map_parameter_list(
162 p_id IN NUMBER,
163 p_module IN VARCHAR2,
164 p_type IN VARCHAR2,
165 p_usage IN VARCHAR2,
166 p_validate_only IN BOOLEAN DEFAULT FALSE
167 );
168 --
169 -- +---------------------------------------------------------------------------+
170 -- | NAME : automap_parameters |
171 -- | DESCRIPTION: Automatically map the parameters for the modules used |
172 -- | by the supplied event. An optional component (or |
173 -- | initialisation) ID can be specified so that only the |
174 -- | parameters of this module are mapped. Just a helper routine |
175 -- | really to wrap up the complexities of mapping the parameters |
176 -- | for different types of initialisation or component. Can be |
177 -- | used instead of 'map_parameter_list' if you know the event |
178 -- | primary key and the component or initialisation primary key |
179 -- | and want to auto map the parameters but don't know (or can't |
180 -- | be bothered to find out) it's name or type. Can also be used |
181 -- | during dataload/seeding type operations to automap the |
182 -- | parameters for SQL select or assignment type initialisations.|
183 -- | PARAMETERS : p_id - The primary key of the event to process |
184 -- | p_usage - The type of usages to process, I for |
185 -- | initialisations or C for components |
186 -- | p_comp_id - The specific component (or initialisation) to |
187 -- | process, defaults to NULL for all components |
188 -- | RETURNS : None |
189 -- | RAISES : Standard - Anything that is raised by modules it calls, e.g. |
190 -- | custom errors raised by map_parameter_list. |
191 -- +---------------------------------------------------------------------------+
192 PROCEDURE automap_parameters(
193 p_id IN NUMBER,
194 p_usage IN VARCHAR2,
195 p_comp_id IN NUMBER DEFAULT NULL
196 );
197 --
198 -- +---------------------------------------------------------------------------+
199 -- | NAME : replace_placeholders |
200 -- | DESCRIPTION: Replace the placeholders (e.g. $NEW_PERSON_ID$) with the |
201 -- | correct variable name or bind variable as specified in the |
202 -- | parameter mappings table. Should never need to call this |
203 -- | directly but used by the Form (and internally) so needs to be|
204 -- | declared public.
205 -- | PARAMETERS : p_sql - The PL/SQL or SQL code to which contains the |
206 -- | placeholders to be replaced |
207 -- | p_id - The ID of the initialisation which this SQL is |
208 -- | associated with |
209 -- | p_extra - Any extra text to place before the replaced name, |
210 -- | used by the SQL select statement verifier to |
211 -- | "fool" the parser into correctly processing the |
212 -- | statement by pretending that local (to the trigger)|
213 -- | variables are bind variables. |
214 -- | RETURNS : The modified SQL statement via the IN/OUT parameter |
215 -- | RAISES : None - Should never :-) raise any exceptions |
216 -- +---------------------------------------------------------------------------+
217 PROCEDURE replace_placeholders(
218 p_sql IN OUT NOCOPY VARCHAR2,
219 p_id IN NUMBER,
220 p_extra IN VARCHAR2 DEFAULT NULL
221 );
222 --
223 -- +---------------------------------------------------------------------------+
224 -- | NAME : lob_to_varchar2 |
225 -- | DESCRIPTION: Converts a character large object (CLOB) into a VARCHAR2 so |
226 -- | that it can be more easily processed. |
227 -- | N.B. This means that the largest piece of code (which is |
228 -- | what is stored in these CLOBS) that can be handled by the |
229 -- | trigger generation module is 32Kb even though the CLOB itself|
230 -- | can hold something like 2Gb. |
231 -- | PARAMETERS : return - The CLOB converted to a VARCHAR2 |
232 -- | p_clob - The CLOB to be converted |
233 -- | RETURNS : The CLOB converted to a VARCHAR2 |
234 -- | RAISES : Standard - May raise errors if the data in the CLOB will not |
235 -- | fit into the VARCHAR2. Should only happen if data |
236 -- | has been populated other than through the front |
237 -- | end Form. The Form will prevent more than 32Kb |
238 -- | being entered by the user. |
239 -- +---------------------------------------------------------------------------+
240 FUNCTION lob_to_varchar2(p_clob IN OUT NOCOPY CLOB) RETURN VARCHAR2;
241 --
242 -- +---------------------------------------------------------------------------+
243 -- | NAME : get_reverted |
244 -- | DESCRIPTION: Retrieves the last saved version of a supporting package's |
245 -- | code, to provide a kind of primitive 'Undo' functionality. |
246 -- | In the support package maintenance portion of the Dynamic |
247 -- | Trigger generation form, when the user attempts to save the |
248 -- | record the Form tries to compile the code, if it fails and |
249 -- | the user Cancels the error message list box then the code |
253 -- | functionality. |
250 -- | will not be saved. They could then press the Revert button to|
251 -- | retrieve the last saved version of the code which is (or |
252 -- | should be) valid. It is this procedure that supports that |
254 -- | PARAMETERS : p_id - Primary key of the supporting package to retrieve |
255 -- | p_head - Populated with the last saved package header |
256 -- | p_body - Populated with the last saved package body code |
257 -- | RETURNS : Package header and body source code via the OUT parameters |
258 -- | RAISES : None - n/a |
259 -- +---------------------------------------------------------------------------+
260 PROCEDURE get_reverted(
261 p_id IN NUMBER,
262 p_head OUT NOCOPY VARCHAR2,
263 p_body OUT NOCOPY VARCHAR2
264 );
265 --
266 -- +---------------------------------------------------------------------------+
267 -- | NAME : compile_package |
268 -- | DESCRIPTION: Compile the supporting package for the specified event using |
269 -- | the AOL create_package procedure to ensure that it is created|
270 -- | in the 'proper' way. The package is named according to the |
271 -- | standard format <TABLE_NAME>_<ID>_DYG where; <TABLE_NAME> is |
272 -- | the name of the table that the owning event's trigger will be|
273 -- | created against, with underscores removed and truncated so |
274 -- | that the package name does not exceed 30 characters and <ID> |
275 -- | is the primary key of the owning event. |
276 -- | PARAMETERS : p_event - The primary key of the event that the supporting |
277 -- | package belongs to |
278 -- | p_table - The table that the event's trigger will be |
279 -- | created on |
280 -- | p_header - The package header code without the CREATE clause|
281 -- | or the final END statement |
282 -- | p_body - The package body code without the CREATE clause |
283 -- | or the final END statement |
284 -- | p_name - Populated with the generated package name, any |
285 -- | value that is present in this variable on input |
286 -- | is ignored. |
287 -- | p_head_ok - Populated with a flag to indicate whether or not |
288 -- | the package header was compiled without errors |
289 -- | p_body_ok - Populated with a flag indicating whether the |
290 -- | package body was successfully compiled or not |
291 -- | RETURNS : The package name and compilation success flags via IN/OUT |
292 -- | parameters. |
293 -- | RAISES : None - All errors are trapped, the caller should examine the |
294 -- | p_head_ok and p_body_ok flags to determine whether or not the|
295 -- | package compiled successfully. |
296 -- +---------------------------------------------------------------------------+
297 PROCEDURE compile_package(
298 p_event IN NUMBER,
299 p_table IN VARCHAR2,
300 p_header IN VARCHAR2,
301 p_body IN VARCHAR2,
302 p_name IN OUT NOCOPY VARCHAR2,
303 p_head_ok IN OUT NOCOPY BOOLEAN,
304 p_body_ok IN OUT NOCOPY BOOLEAN
305 );
306 --
307 -- +---------------------------------------------------------------------------+
308 -- | NAME : compile_package_indirect |
309 -- | DESCRIPTION: Silently compiles the specified supporting package. Only the |
310 -- | primary key of the supporting package is required. Calls the |
311 -- | compile_package procedure but wraps it in a simpler interface|
312 -- | so that the developer does not need to determine the |
313 -- | information required themselves. All errors are trapped so |
314 -- | the developer is responsible for determining whether or not |
315 -- | the package compiled, if this is required. |
316 -- | PARAMETERS : p_id - The primary key of the supporting package to compile |
317 -- | RETURNS : None |
318 -- | RAISES : None - All exceptions are trapped and a diagnostic message is|
319 -- | written to the hr_utility trace pipe before returning |
320 -- | to the caller normally. |
321 -- +---------------------------------------------------------------------------+
322 PROCEDURE compile_package_indirect(p_id IN NUMBER);
323 --
324 -- +---------------------------------------------------------------------------+
325 -- | NAME : create_defaults |
326 -- | DESCRIPTION: Create the default declarations and initialisations that are |
327 -- | required in triggers created against tables with business |
328 -- | group or payroll IDs. This procedure is used by the front end|
329 -- | Form when a new event record is created to populate some |
330 -- | defaults into the child tables.\nAny dataload/seeding process|
331 -- | could utilise this in the same way, i.e. create a record in |
332 -- | the pay_trigger_events table, then call this routine passing |
336 -- | will be created for local variables l_business_group_id and |
333 -- | the primary key of the record you just created.\nIf the table|
334 -- | on which the event's trigger will be created contains a |
335 -- | mandatory business_group_id column then declaration records |
337 -- | l_legislation_code. Initialisations will be created to |
338 -- | populate l_business_group_id from the old or new table record|
339 -- | (an assignment type initialisation) and to populate the |
340 -- | legislation code by selecting it's value from the business |
341 -- | groups table.\nIf the table contains a mandatory payroll_id |
342 -- | column then a declaration record is created or the local |
343 -- | variable l_payroll_id and an initialisation record is created|
344 -- | (assignment type) to populate this variable from the old or |
345 -- | new table row record.\n |
346 -- | These defaults are created so that components can be created |
347 -- | which are only executed in specific circumstances, i.e. if |
348 -- | the payroll_id, legislation_code or business_group of the |
349 -- | record causing the trigger to fire matches the criteria |
350 -- | defined against the component. |
351 -- | PARAMETERS : p_id - The primary key of the event |
352 -- | RETURNS : None |
353 -- | RAISES : None - All error trapping and handling is done by the various|
354 -- | modules that this procedure calls. |
355 -- +---------------------------------------------------------------------------+
356 PROCEDURE create_defaults(p_id IN NUMBER);
357 --
358 -- +---------------------------------------------------------------------------+
359 -- | NAME : generate_trigger |
360 -- | DESCRIPTION: Generates the database trigger for the specified event. The |
361 -- | event definition record, it's declarations, initialisations |
362 -- | components and all relevant parameter mappings must be |
363 -- | defined. One call to this procedure with the correct event |
364 -- | primary key ID will generate the code for the trigger, create|
365 -- | the trigger based on the trigger definition and the generated|
366 -- | code, and enable the trigger if required by the event |
367 -- | definition. The name of the generated trigger and a flag to |
368 -- | indicate success or failure are returned via out parameters. |
369 -- | The generated trigger code is passed to the 'create_trigger' |
370 -- | procedure which uses the AOL routines to 'properly' create |
371 -- | the required database trigger. |
372 -- | PARAMETERS : p_id - The primary key of the event |
373 -- | p_name - Populated with the name of the generated trigger |
374 -- | p_ok - Populated with a flag to indicated whether or not |
375 -- | the trigger was compiled successfully |
376 -- | RETURNS : The trigger name and success flag via the OUT parameters |
377 -- | RAISES : None - All errors are trapped, the p_ok flag should be |
378 -- | examined when the procedure returns in order to determine |
379 -- | whether or not the trigger was created successfully. |
380 -- +---------------------------------------------------------------------------+
381 PROCEDURE generate_trigger(
382 p_id IN NUMBER,
383 p_name IN OUT NOCOPY VARCHAR2,
384 p_ok OUT NOCOPY BOOLEAN
385 );
386 --
387 -- +---------------------------------------------------------------------------+
388 -- | NAME : generate_code |
389 -- | DESCRIPTION: Creates the PL/SQL code that will be used to generate the |
390 -- | trigger, without the CREATE TRIGGER clause. |
391 -- | Used internally (by generate_trigger) and by the client-side |
392 -- | Form (so that the user can view the source of the trigger) |
393 -- | this procedure uses the definitions stored in the |
394 -- | declarations, initialisations, components and parameters |
395 -- | tables to build PL/SQL code.\n |
396 -- | First the declarations are added |
397 -- | to define local variables, then the initialisations which |
398 -- | populate these variables are added.\n |
399 -- | Within the initialisations block (immediately after the |
400 -- | automatic initialisations for business group, payroll and |
401 -- | legislation code and before any user-defined initialisations)|
402 -- | a call is made to paywsfat_pkg.i_am_disabled to determine |
403 -- | whether or not the trigger should be executing for the |
404 -- | triggering row's context.\n |
405 -- | Global components are |
406 -- | added, procedures which are always executed, whenever the |
407 -- | trigger fires. These are followed by legislation, business |
408 -- | group, and finally payroll specific components. These are |
409 -- | placed in IF...THEN conditions, components with the same |
413 -- | specific components).\nComments and an exception handler are |
410 -- | criteria are all placed in one IF statement (so, for example |
411 -- | there won't be lots of IF l_legislation_code = 'GB' THEN |
412 -- | statements, there will be only one containing all the UK |
414 -- | also added to make the code more readable and robust. |
415 -- | PARAMETERS : p_id - The primary key of the event |
416 -- | p_sql - Will be populated with the trigger source code |
417 -- | RETURNS : The generated code via the IN OUT parameter |
418 -- | RAISES : None - Shouldn't really raise errors, it will just create |
419 -- | invalid code, i.e. code that won't compile. |
420 -- +---------------------------------------------------------------------------+
421 PROCEDURE generate_code(p_id IN NUMBER,p_sql IN OUT NOCOPY VARCHAR2);
422 --
423 -- +---------------------------------------------------------------------------+
424 -- | NAME : delete_event_children |
425 -- | DESCRIPTION: Deletes the children (e.g. support package, declarations) |
426 -- | of the specified event and drops any associated database |
427 -- | objects like packages and triggers. Doesn't delete the |
428 -- | actual event definition |
429 -- | PARAMETERS : p_id - The primary key of the event |
430 -- | RETURNS : None |
431 -- | RAISES : Standard - Any Database (e.g. constraint) errors raised by |
432 -- | the RDBMS. |
433 -- +---------------------------------------------------------------------------+
434 PROCEDURE delete_event_children(p_id IN NUMBER);
435 --
436 -- +---------------------------------------------------------------------------+
437 -- | NAME : delete_initialisation_children |
438 -- | DESCRIPTION: Deletes the children of the specified initialisation (i.e. |
439 -- | the parameter mappings that the initialisation uses) |
440 -- | PARAMETERS : p_id - The initialisation primary key |
441 -- | RETURNS : None |
442 -- | RAISES : Standard - Any Database (e.g. constraint) errors raised by |
443 -- | the RDBMS. |
444 -- +---------------------------------------------------------------------------+
445 PROCEDURE delete_initialisation_children(p_id IN NUMBER);
446 --
447 -- +---------------------------------------------------------------------------+
448 -- | NAME : delete_component_children |
449 -- | DESCRIPTION: Deletes the children of the specified component (i.e. the |
450 -- | parameter mappings that the component uses) |
451 -- | PARAMETERS : p_id - The component primary key |
452 -- | RETURNS : None |
453 -- | RAISES : Standard - Any Database (e.g. constraint) errors raised by |
454 -- | the RDBMS. |
455 -- +---------------------------------------------------------------------------+
456 PROCEDURE delete_component_children(p_id IN NUMBER);
457
458 -- +---------------------------------------------------------------------------+
459 -- | NAME : delete_parameters_directly |
460 -- | DESCRIPTION: Deletes an individual parameter given its ID. |
461 -- | used by table event updates form where user delets individual|
465 -- | the RDBMS. |
462 -- | PARAMETERS : p_id - The component primary key |
463 -- | RETURNS : None |
464 -- | RAISES : Standard - Any Database (e.g. constraint) errors raised by |
466 -- +---------------------------------------------------------------------------+
467 PROCEDURE delete_parameters_directly(p_param_id IN NUMBER);
468
469 --
470 -- +---------------------------------------------------------------------------+
471 -- | NAME : table_has_business_group |
472 -- | DESCRIPTION: Checks to see if the specified table has a mandatory |
473 -- | business_group_id column |
474 -- | PARAMETERS : return - TRUE if table has manadatory business group ID |
475 -- | p_table - The name of the table to check |
476 -- | RETURNS : A boolean value indicating if the table has a business group |
477 -- | RAISES : None - No errors should be raised. |
478 -- +---------------------------------------------------------------------------+
479
480 FUNCTION table_has_business_group(p_table IN VARCHAR2) RETURN BOOLEAN;
481 --
482 -- +---------------------------------------------------------------------------+
483 -- | NAME : table_has_payroll |
484 -- | DESCRIPTION: Checks to see if the specified table has a mandatory |
485 -- | payroll_id column |
486 -- | PARAMETERS : return - TRUE if table has manadatory payroll ID |
487 -- | p_table - The name of the table to check |
488 -- | RETURNS : A boolean value indicating if the table has a payroll |
489 -- | RAISES : None - No errors should be raised. |
490 -- +---------------------------------------------------------------------------+
491 FUNCTION table_has_payroll(p_table IN VARCHAR2) RETURN BOOLEAN;
492 --
493 -- +---------------------------------------------------------------------------+
494 -- | NAME : validate_select |
495 -- | DESCRIPTION: Checks that the SQL select statement is valid for the way |
496 -- | in which it is being used. This should only really be used |
497 -- | internally by the front-end Form to validate user input. |
498 -- | Any seed data or dataload information should be correct and |
499 -- | hence should not need validating. |
500 -- | PARAMETERS : p_id - The primary key of the event which owns the code |
501 -- | p_code - The SQL select statement to validate |
502 -- | p_type - The type of usage, should always be 'S' |
503 -- | RETURNS : None |
504 -- | RAISES : could_not_analyse_query - If the select statement could not |
505 -- | be 'described' |
506 -- +---------------------------------------------------------------------------+
507 PROCEDURE validate_select(
508 p_id IN NUMBER,
509 p_code IN VARCHAR2,
510 p_type IN VARCHAR2
511 );
512 --
513 FUNCTION no_business_context(p_table IN VARCHAR2,p_id IN NUMBER) RETURN BOOLEAN;
514 FUNCTION no_legislation_context(p_table IN VARCHAR2,p_id IN NUMBER) RETURN BOOLEAN;
515 FUNCTION no_payroll_context(p_table IN VARCHAR2,p_id IN NUMBER) RETURN BOOLEAN;
516 --
517 --
518 -- +---------------------------------------------------------------------------+
519 -- | NAME : g_param_rec_type |
520 -- | DESCRIPTION: Useful to store the entire set of parameters associated with |
521 -- | a table in a standard format, so future code generation can |
522 -- | simply loop through this table. |
523 -- +---------------------------------------------------------------------------+
524 TYPE g_param_rec_type is RECORD
525 (local_form pay_trigger_parameters.parameter_name%type,
526 usage_type pay_trigger_parameters.usage_type%type,
527 param_form pay_trigger_parameters.parameter_name%type,
528 value_name pay_trigger_parameters.value_name%type,
529 data_type all_tab_columns.data_type%type);
530 --
531 TYPE g_params_tab_type is TABLE of g_param_rec_type
532 index by binary_integer;
533
534 --
535 -- +---------------------------------------------------------------------------+
536 -- | NAME : gen_dyt_pkg_full_code |
537 -- | DESCRIPTION: This is the main entry point for generating the dynamic code |
538 -- | that represents ALL of the dynamic triggers on a table INTO PACKAGE |
539 -- | format. This dynamic trigger package (dyt_pkg) contains a procedure|
540 -- | representing each dyt, and also three standard interfaces for the API|
541 -- | strategy, (namely after_update, after_insert, after_delete) which |
542 -- | call the former types as required. |
543 -- | |
544 -- | For each dated table, the users preference of dyt storage type is |
545 -- | noted, T = dbms triggers and therefore this procedure should never |
546 -- | be called. P= package, so dyt's should reside in a pkg, built by |
550 -- | triggers are not the same as those created by the existing methods as|
547 -- | this pkg. B = Both, this means the pkg will be created and dbms |
548 -- | triggers will also be created calling this pkg code. This procedure |
549 -- | will create pkg and dbms triggers if required. (These latter dbms |
551 -- | mentioned when preference = T.) |
552 -- | PARAMETERS : p_tab_id - Primary key of the table on which the dyt's exist |
553 -- | p_ok - Boolean indicating overall success of generation |
554 -- | RETURNS : p_ok - The resulting value |
555 -- | RAISES : Standard sql errors. |
556 -- +---------------------------------------------------------------------------+
557 PROCEDURE gen_dyt_pkg_full_code(p_tab_id IN NUMBER,p_ok IN OUT NOCOPY BOOLEAN);
558 --
559 -- +---------------------------------------------------------------------------+
560 -- | NAME : gen_dyt_pkg_proc |
561 -- | SCOPE : PUBLIC |
562 -- | DESCRIPTION: This procedure creates the single procedure representing a |
563 -- | single dyt. |
564 -- | PARAMETERS : p_dyt_id - Primary key of the dynamic trigger |
565 -- | p_dyt_name - Dynamic Trigger Name |
566 -- | p_tab_name - Table on which dyt exists |
567 -- | p_dyt_act - Triggering action of dyt, I U or D |
568 -- | p_dyt_desc - Description of dyt |
569 -- | p_dyt_info - Full version of trig act, eg INSERT |
570 -- | p_dyn_pkg_params - Tbl containing all parameter details |
571 -- | p_hs - Placeholder for header code |
572 -- | p_bs - Placeholder for body code, passed in and returned |
573 -- | RETURNS : p_hs - The resulting header code |
574 -- | p_bs - The resulting body code |
575 -- | RAISES : Standard sql errors. |
576 -- +---------------------------------------------------------------------------+
577 PROCEDURE gen_dyt_pkg_proc(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
578 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
579 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
580 ,p_dyn_pkg_params IN g_params_tab_type
581 ,p_hs IN OUT NOCOPY VARCHAR2
582 ,p_bs IN OUT NOCOPY VARCHAR2);
583 --
584 -- +---------------------------------------------------------------------------+
585 -- | NAME : gen_dyt_pkg_rhi_proc |
586 -- | SCOPE : PUBLIC |
587 -- | DESCRIPTION: This procedure creates a single procedure representing either|
588 -- | after_insert,after_update or ater_delete as decided by the p_dyt_act |
589 -- | parameter. This generated code contains calls to all dyt procedures |
590 -- | of the same action type. |
591 -- | PARAMETERS : p_tab_name - Table on which dyt exists |
592 -- | p_dyt_act - Triggering action of dyt, I U or D |
593 -- | p_dyt_info - Full version of trig act, eg INSERT |
594 -- | p_hok_params - Tbl containing all hook parameter details |
595 -- | p_hs - Placeholder for header code |
596 -- | p_bs - Placeholder for body code, passed in and returned |
597 -- | p_dyt_params - Tbl containing all dyt parameter details |
598 -- | p_datetracked_table - Y or N is the table dated |
599 -- | RETURNS : p_hs - The resulting header code |
600 -- | p_bs - The resulting body code |
601 -- | p_dyt_pkg_head_tbl - Table 32k header code |
602 -- | p_dyt_pkg_body_tbl - Table 32k body code |
603 -- | RAISES : Standard sql errors. |
604 -- +---------------------------------------------------------------------------+
605 PROCEDURE gen_dyt_pkg_rhi_proc(p_tab_name IN VARCHAR2
606 ,p_dyt_act IN VARCHAR2 ,p_dyt_info IN VARCHAR2
607 ,p_hok_params IN g_params_tab_type
608 ,p_hs IN OUT NOCOPY VARCHAR2
609 ,p_bs IN OUT NOCOPY VARCHAR2
610 ,p_dyt_params IN g_params_tab_type
611 ,p_dyt_pkg_head_tbl IN OUT NOCOPY t_varchar2_32k_tbl
612 ,p_dyt_pkg_body_tbl IN OUT NOCOPY t_varchar2_32k_tbl
613 ,p_datetracked_table in VARCHAR2);
614 --
615 -- +---------------------------------------------------------------------------+
616 -- | NAME : gen_dyt_db_trig |
617 -- | SCOPE : PUBLIC |
618 -- | DESCRIPTION: This procedure generates the dbms triggers that accompany the|
619 -- | dyt package. Code for a single dyt is created and the db trigger is |
620 -- | created on the database. The trigger contains calls to the newly |
621 -- | created procedures representing dyt within the dyt pkg. |
622 -- | PARAMETERS : p_dyt_id - Primary key of the dynamic trigger |
623 -- | p_dyt_name - Dynamic Trigger Name |
627 -- | p_dyt_info - Full version of trig act, eg INSERT |
624 -- | p_tab_name - Table on which dyt exists |
625 -- | p_dyt_act - Triggering action of dyt, I U or D |
626 -- | p_dyt_desc - Description of dyt |
628 -- | p_dyn_pkg_params - Tbl containing all parameter details |
629 -- | p_tab_dyt_pkg_name - Name of the dyt pkg |
630 -- | RETURNS : none |
631 -- | RAISES : Standard sql errors. |
632 -- +---------------------------------------------------------------------------+
633 PROCEDURE gen_dyt_db_trig(p_dyt_id IN NUMBER,p_dyt_name IN VARCHAR2
634 ,p_tab_name IN VARCHAR2, p_dyt_act IN VARCHAR2
635 ,p_dyt_desc IN VARCHAR2,p_dyt_info IN VARCHAR2
636 ,p_dyn_pkg_params IN g_params_tab_type
637 ,p_tab_dyt_pkg_name IN VARCHAR2);
638 --
639 -- +---------------------------------------------------------------------------+
640 -- | NAME : trigger_enabled |
641 -- | SCOPE : PUBLIC |
642 -- | DESCRIPTION: Simply returns boolean to see if dyn trigger is enabled. |
643 -- | Called by dynamically created rhi proc in dynamic package |
644 -- | PARAMETERS : p_dyt - The dynamic trigger name |
645 -- | RETURNS : TRUE if trigger is enabled, FALSE otherwise |
646 -- | RAISES : None |
647 -- +---------------------------------------------------------------------------+
648 FUNCTION trigger_enabled(p_dyt varchar2) return BOOLEAN;
649 --
650 -- +---------------------------------------------------------------------------+
651 -- | name : convert_tab_style |
652 -- | scope : public |
653 -- | description: there are times when the seeded behaviour needs to be altered|
654 -- | usually as a result of release issues. this procedure provides a quick |
655 -- | wrapper utility to change a dated table from dbms_dyt to dyt_pkg and vice|
656 -- | versa.
657 -- | parameters : p_table_name - the dated table name |
658 -- | : p_dyt_type - eg t<dbms trigger> p<ackage> b<oth> |
659 -- | returns : none
660 -- | raises : none |
661 -- +---------------------------------------------------------------------------+
662 PROCEDURE convert_tab_style(p_table_name in varchar2,p_dyt_type in varchar2);
663 --
664 -- +---------------------------------------------------------------------------+
665 -- | name : confirm_dyt_data |
666 -- | scope : public |
667 -- | description: there are times when the seeded behaviour needs to be altered|
668 -- | usually as a result of release issues. this procedure checks the data
669 -- | for a given table and depending on the main switch (hook calls to DYT_PKG)
670 -- | rebuilds the data for DYT_PKG behaviour (if calls existed) or DBMS dynamic
671 -- | triggers (if no calls existed)
672 -- | parameters : p_table_name - the dated table name |
673 -- | returns : none
674 -- | raises : none |
675 -- +---------------------------------------------------------------------------+
676 PROCEDURE confirm_dyt_data(p_table_name in varchar2);
677 --
681 -- | this is to emulate the API row handler behaviour, even though|
678 -- +---------------------------------------------------------------------------+
679 -- | NAME : ins, upd, del, lck |
680 -- | DESCRIPTION: Insert, Update, Delete and Lock rows in pay_trigger_events, |
682 -- | there isn't actually a proper API for this table. |
683 -- | PARAMETERS : Same as the columns in the table for Insert and Update. Just |
684 -- | the primary key for Delete and Lock. |
685 -- | RETURNS : None |
686 -- | RAISES : HR_7220_INVALID_PRIMARY_KEY |
690 p_event_id IN NUMBER,
687 -- | HR_7165_OBJECT_LOCKED |
688 -- +---------------------------------------------------------------------------+
689 PROCEDURE ins(
691 p_table_name IN VARCHAR2,
692 p_short_name IN VARCHAR2,
693 p_description IN VARCHAR2,
694 p_generated_flag IN VARCHAR2,
695 p_enabled_flag IN VARCHAR2,
696 p_protected_flag IN VARCHAR2,
697 p_triggering_action IN VARCHAR2,
698 p_last_update_date IN DATE,
699 p_last_updated_by IN NUMBER,
700 p_last_update_login IN NUMBER,
701 p_created_by IN NUMBER,
702 p_creation_date IN DATE
703 );
704 --
705 PROCEDURE upd(
706 p_event_id IN NUMBER,
707 p_table_name IN VARCHAR2,
708 p_short_name IN VARCHAR2,
709 p_description IN VARCHAR2,
710 p_generated_flag IN VARCHAR2,
711 p_enabled_flag IN VARCHAR2,
712 p_protected_flag IN VARCHAR2,
713 p_triggering_action IN VARCHAR2,
714 p_last_update_date IN DATE,
718 p_creation_date IN DATE
715 p_last_updated_by IN NUMBER,
716 p_last_update_login IN NUMBER,
717 p_created_by IN NUMBER,
719 );
720 --
721 PROCEDURE del(
722 p_event_id IN NUMBER
723 );
724 --
725 PROCEDURE lck(
726 p_event_id IN NUMBER
727 );
728 --
729 -- +---------------------------------------------------------------------------+
730 -- | NAME : is_table_valid |
731 -- | is_table_column_valid |
732 -- | is_table_owner_valid |
733 -- | get_table_owner |
734 -- | DESCRIPTION: Helper functions to extend the use of the dynamic triggers |
735 -- | so that they can be used on tables which aren't in the PAY |
736 -- | schema. |
737 -- | PARAMETERS : The table name, column name, table owner, variously. |
738 -- | RETURNS : 'Y', 'N', or the name of the owner of a table. |
739 -- | RAISES : None. |
740 -- +---------------------------------------------------------------------------+
741 FUNCTION is_table_valid(p_table IN VARCHAR2) RETURN VARCHAR2;
742 FUNCTION is_table_column_valid(p_table IN VARCHAR2
743 ,p_column IN VARCHAR2) RETURN VARCHAR2;
744 FUNCTION is_table_owner_valid(p_table IN VARCHAR2
745 ,p_owner IN VARCHAR2) RETURN VARCHAR2;
746 --
747 FUNCTION get_table_owner(p_table IN VARCHAR2) RETURN VARCHAR2;
748 --
749 END paywsdyg_pkg;
[Home] [Help]