1 package hrdyndbi as
2 /* $Header: pydyndbi.pkh 120.6.12010000.1 2008/07/27 22:28:48 appldev ship $ */
3 --
4 --
5 /* Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved */
6 /*
7 PRODUCT
8 Oracle*Payroll
9 --
10 NAME
11 pydyndbi.pkh
12 --
13 DESCRIPTION
14 Package headers for the procedures used to create and delete database
15 items in the data dictionary.
16 --
17 MODIFIED (DD-MON-YYYY)
18 divicker 01-JUN-2006 Merge 11511 branch
19 arashid 11-MAY-2006 Added p_localization parameter to
20 process_pay_dyndbi_changes.
21 arashid 27-JAN-2006 Changed process_pay_dyndbi_changes to a
22 concurrent processing interface with VARCHAR2
23 parameters.
24 arashid 01-NOV-2005 Made process_pay_dyndbi_changes multi-threaded.
25 arashid 24-OCT-2005 Added new interfaces for translated dynamic
26 database items:
27 * process_pay_dyndbi_changes
28 * update_defined_balance
29 * update_element_type
30 * update_input_value
31 Note: updates to FP.K and earlier should be
32 made on branches to 115.18.
33 divicker 15-MAR-2005 Need insert_mthread_pps public so can call
34 from hrrbdeib
35 divicker 01-FEB-2004 Add proc reib_all
36 divicker 18-NOV-2004 Mthread support
37 divicker 09-DEC-2003 Same as 115.10 and 115.12 (no mthread)
38 divicker 24-SEP-2003 Same as 115.10 (no mthread routines)
39 scchakra 30-APR-2003 Created procedure recreate_defined_balance.
40 Bug - 2450195.
41 alogue 18-DEC-2002 - NOCOPY changes. Bug 2692195.
42 RThirlby 17-OCT-2002 Added new global g_trigger_dfb_ari boolean. Used
43 in new_defined_balance to determine whether to
44 call code to set save_run_balance flag. Prevents
45 mutating table error.
46 divicker 19-JUL-2002 Leg code striping support
47 rthirlby 06-MAR-2002 Added dbdrv commands.
48 divicker 30-OCT-2000 Added global for saying we have disabled some
49 trigger validation and that this validation is
50 moved inside pydyndbi.pkb
51 divicker 04-OCT-2000 Added disable/enable trigger procedures for
52 performance enhancements in rebuild_ele_input_bal
53 tbattoo 24-FEB-2000 Bug 1207273, if a user entity alredy exists when
54 you insert the db item use the id for the
55 existing entity and not the currval in the seq
56 alogue 09-MAR-1998 - Change insert_user_entity to
57 insert_user_entity_main
58 to overload insert_user_entity by 2 calls -
59 one with p_record_inserted and one without.
60 amyers 13-JAN-1998 Amended procedure insert_user_entitiy to:
61 i. only insert data if it doesn't already exist
62 ii. return a value in a new parameter indicating
63 whether the insert has happened to determine
64 the creation of underlying parameter values
65 and database items.
66 This change comes from bug 602851, where in an
67 R11 upgrade database items and entities were not
68 created, so in driver hr11gn.drv we need to run
69 refresh_grade_spine_rates to ensure this doesn't
70 happen. New version is 110.2.
71 alogue 13-AUG-1997 - Business_group_id passed to delete_keyflex_dict
72 to fix bug 513364.
73 mwcallag 26-APR-1995 - Entity name passed to delete_keyflex_dict to
74 fix bug 278064.
75 mwcallag 28-JUL-1994 - Optional commit parameter added to procedure
76 rebuild_ele_input_bal.
77 mwcallag 13-JUN-1994 - G916 Procedure 'rebuild_ele_input_bal' added.
78 mwcallag 20-JAN-1994 - Legislation code passed to delete_keyflex_dict,
79 procedure delete_compiled_formula added (G516).
80 mwcallag 08-DEC-1993 - G323 Context parameter passed to the procedure
81 delete_flexfield_dict.
82 mwcallag 23-NOV-1993 - G161 Simplified the calls to generate DB items for
83 external use.
84 mwcallag 28-SEP-1993 - legislation code parameter removed from procedure
85 'insert_database_item'
86 mwcallag 01-SEP-1993 - Procedure for converting element DB items from the
87 context of date earned to date paid added.
88 mwcallag 23-AUG-1993 - Organization payment methods, external
89 accounts and legal company SCL DB items added.
90 mwcallag 03-AUG-1993 - Developer Descriptive flexfield and SCL flexfield
91 procedures added.
92 mwcallag 14-JUN-1993 - application id removed from both
93 delete_flexfield_dict and create_flexfield_dict
94 mwcallag 03-JUN-1993 - application id passed to delete_flexfield_dict
95 mwcallag 07-MAY-1993 - spine DB creation added to grade procedure.
96 DB creation procedure for key flexfield.
97 mwcallag 30-APR-1993 - grade rates extended, descriptive flexs and
98 absence types added.
99 mwcallag 26-APR-1993 - procedures for input values, element types
100 and grade rate database items added.
101 Abraae 06-APR-1993 - created
102 */
103 --
104 -- first declare general routines that are called from within the database
105 -- item creation procedures. These should only be called by these procedures.
106 --
107 -- Holder for whether we have come thru rebuild_ele_input_bal()
108 g_triggers_altered BOOLEAN := FALSE;
109 g_debug_cnt number;
110 --
111 -- Holder for whether we have come through trigger pay_defined_balances_ari
112 --
113 g_trigger_dfb_ari boolean := false;
114 --
115 PROCEDURE insert_mthread_pps (p_stage in number,
116 p_worker_id in number,
117 p_leg_code in varchar2 default 'ZZ');
118 --
119 ------------------------- insert_parameter_value -------------------------
120 --
121 procedure insert_parameter_value
122 (
123 p_value in varchar2,
124 p_sequence_no in number
125 );
126 --
127 ------------------------- insert_database_item -------------------------
128 --
129 procedure insert_database_item
130 (
131 p_entity_name in varchar2,
132 p_item_name in varchar2,
133 p_data_type in varchar2,
134 p_definition_text in varchar2,
135 p_null_allowed_flag in varchar2,
136 p_description in varchar2,
137 p_user_entity_id in number DEFAULT NULL
138 );
139 --
140 ------------------------- insert_user_entity -------------------------
141 --
142 procedure insert_user_entity
143 (
144 p_route_name in varchar2,
145 p_user_entity_name in varchar2,
146 p_entity_description in varchar2,
147 p_not_found_flag in varchar2,
148 p_creator_type in varchar2,
149 p_creator_id in number,
150 p_business_group_id in number,
151 p_legislation_code in varchar2,
152 p_created_by in number,
153 p_last_login in number,
154 p_record_inserted out nocopy boolean
155 );
156 --
157 procedure insert_user_entity
158 (
159 p_route_name in varchar2,
160 p_user_entity_name in varchar2,
161 p_entity_description in varchar2,
162 p_not_found_flag in varchar2,
163 p_creator_type in varchar2,
164 p_creator_id in number,
165 p_business_group_id in number,
166 p_legislation_code in varchar2,
167 p_created_by in number,
168 p_last_login in number
169 );
170 --
171 procedure insert_user_entity_main
172 (
173 p_route_name in varchar2,
174 p_user_entity_name in varchar2,
175 p_entity_description in varchar2,
176 p_not_found_flag in varchar2,
177 p_creator_type in varchar2,
178 p_creator_id in number,
179 p_business_group_id in number,
180 p_legislation_code in varchar2,
181 p_created_by in number,
182 p_last_login in number,
183 p_record_inserted out nocopy boolean
184 );
185 --
186 ------------------------- delete_compiled_formula -------------------------
187 --
188 procedure delete_compiled_formula
189 (
190 p_creator_id in number,
191 p_creator_type in varchar2,
192 p_user_entity_name in varchar2,
193 p_leg_code in varchar2
194 );
195 --
196 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
197 -- +
198 -- The following routines create / delete database items: +
199 -- +
200 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
201 --
202 procedure new_defined_balance (p_defined_balance_id in number,
203 p_balance_dimension_id in number,
204 p_balance_type_id in number,
205 p_business_group_id in number,
206 p_legislation_code in varchar2);
207 --
208 procedure refresh_defined_balances(p_worker_id in number default 0,
209 p_maxworkers in number default 1);
210 procedure refresh_defined_balances(p_leg_code in varchar2,
211 p_worker_id in number default 0,
212 p_maxworkers in number default 1);
213 procedure recreate_defined_balance(p_defined_balance_id in number,
214 p_balance_dimension_id in number,
215 p_balance_type_id in number,
216 p_business_group_id in number,
217 p_legislation_code in varchar2);
218 --
219 ------------------------- delete_element_type_dict -------------------------
220 --
221 -- delete database items for a given element type
222 --
223 procedure delete_element_type_dict
224 (
225 p_element_type_id in number
226 );
227 --
228 ------------------------- create_element_type -------------------------
229 --
230 -- create Database items with a context of either date earned or date paid.
231 --
232 procedure create_element_type
233 (
234 p_element_type_id in number,
235 p_effective_date in date,
236 p_date_p in varchar2
237 );
238 --
239 ------------------------- create_element_type_dict -------------------------
240 --
241 -- create database items for a given element type, with a context of date
242 -- earned. This procedure calls 'create_element_type'.
243 --
244 procedure create_element_type_dict
245 (
246 p_element_type_id in number,
247 p_effective_date in date
248 );
249 --
250 ------------------------- create_element_type_dp_dict -------------------------
251 --
252 -- create database items for a given element type, with a context of date
253 -- paid. This procedure calls 'create_element_type'.
254 --
255 procedure create_element_type_dp_dict
256 (
257 p_element_type_id in number
258 );
259 --
260 ------------------------- delete_input_value_dict -------------------------
261 --
262 -- delete database items for a given input value
263 --
264 procedure delete_input_value_dict
265 (
266 p_input_value_id in number
267 );
268 --
269 ------------------------- create_input_value -------------------------
270 --
271 -- create database items for a given input value with a context of either
272 -- date earned or date paid.
273 --
274 procedure create_input_value
275 (
276 p_input_value_id in number,
277 p_effective_date in date,
278 p_date_p in varchar2
279 );
280 --
281 ------------------------- create_input_value_dict -------------------------
282 --
283 -- create database items for a given input value
284 --
285 procedure create_input_value_dict
286 (
287 p_input_value_id in number,
288 p_effective_date in date
289 );
290 --
291 ------------------------- refresh_element_types -------------------------
292 --
293 -- create all the element type and input value database items
294 --
295 procedure refresh_element_types(p_worker_id in number default 0,
296 p_maxworkers in number default 1);
297 --
298 ------------------------- delete_element_types -------------------------
299 --
300 -- delete all the element type and input value database items
301 --
302 procedure delete_element_types(p_worker_id in number default 0,
303 p_maxworkers in number default 1);
304 --
305 ------------------------- rebuild_ele_input_bal -------------------------
306 --
307 -- delete and re-create element, input value and balance DB items
308 --
309 procedure rebuild_ele_input_bal
310 (
311 p_commit in varchar2 default 'N',
312 p_worker_id in number default 0,
313 p_maxworkers in number default 1
314 );
315 procedure rebuild_ele_input_bal
316 (
317 p_commit in varchar2 default 'N',
318 p_leg_code in varchar2,
319 p_worker_id in number default 0,
320 p_maxworkers in number default 1
321 );
322 procedure reib_all;
323 --
324 ------------------------- delete_grade_spine_dict -------------------------
325 --
326 -- delete database items for a given grade / spine rate
327 --
328 procedure delete_grade_spine_dict
329 (
330 p_rate_id in number
331 );
332 --
333 ------------------------- create_grade_spine_dict -------------------------
334 --
335 -- create database items for a given grade/ spine rate
336 --
337 procedure create_grade_spine_dict
338 (
339 p_rate_id in number
340 );
341 --
342 ------------------------- refresh_grade_spine_rates ------------------------
343 --
344 -- create all the grade rate and spine rate database items
345 --
346 procedure refresh_grade_spine_rates;
347 --
348 ------------------------- delete_grade_spine_rates -------------------------
349 --
350 -- delete all the grade and spine rate database items
351 --
352 procedure delete_grade_spine_rates;
353 --
354 ------------------------- create_desc_flex -------------------------
355 --
356 -- General routine to create Descriptive flexfields.
357 --
358 procedure create_desc_flex
359 (
360 p_title in varchar2,
361 p_table_name in varchar2,
362 p_route_name in varchar2,
363 p_entity_name in varchar2,
364 p_context in varchar2,
365 p_global_flag in varchar2,
366 p_param_value in varchar2,
367 p_leg_code in varchar2
368 );
369 ------------------------- create_flexfield_dict -------------------------
370 --
371 -- create the flexfield database items for a given title. Note that title
375 (
372 -- may be set to '%' in order to create all flexfield database items
373 --
374 procedure create_flexfield_dict
376 p_title in varchar2
377 );
378 --
379 ------------------------- create_dev_desc_flex_dict -------------------------
380 --
381 -- create the developer descriptive flexfield database items for a given title
382 -- and context. This will be used by either the Person Developer DF report
383 -- or the Organization Developer DF report.
384 --
385 procedure create_dev_desc_flex_dict
386 (
387 p_title in varchar2,
388 p_context in varchar2
389 );
390 --
391 ------------------------- create_org_pay_flex_dict -------------------------
392 --
393 -- Create Organization Payment descriptive flexfield database items for a
394 -- given payment id.
395 --
396 procedure create_org_pay_flex_dict
397 (
398 p_payment_id in number
399 );
400 --
401 ------------------------- delete_flexfield_dict -------------------------
402 --
403 -- delete the specified descriptive flexfield database items depending on their
404 -- title, context and legislation code
405 --
406 procedure delete_flexfield_dict
407 (
408 p_title in varchar2,
409 p_context in varchar2,
410 p_leg_code in varchar2
411 );
412 --
413 ------------------------- create_absence_dict -------------------------
414 --
415 -- create database items for a given absence type
416 --
417 procedure create_absence_dict
418 (
419 p_absence_type_id in number
420 );
421 --
422 ------------------------- delete_absence_dict -------------------------
423 --
424 -- delete database items for a given absence type
425 --
426 procedure delete_absence_dict
427 (
428 p_absence_type_id in number
429 );
430 --
431 ------------------------- refresh_absence_types -------------------------
432 --
433 -- create all the absence type database items
434 --
435 procedure refresh_absence_types;
436 --
437 ------------------------- delete_absence_types -------------------------
438 --
439 -- delete all the absence type database items
440 --
441 procedure delete_absence_types;
442 --
443 ------------------------- create_key_flex -------------------------
444 --
445 -- General routine to create Key flexfield Database items
446 --
447 procedure create_key_flex
448 (
449 p_applic_id in number,
450 p_business_group in number,
451 p_id_flex_num in number,
452 p_id_flex_code in varchar2,
453 p_entity_name in varchar2,
454 p_leg_code in varchar2,
455 p_route_name in varchar2,
456 p_table_name in varchar2
457 );
458 --
459 ------------------------- create_keyflex_dict -------------------------
460 --
461 -- create the key flexfield database items for a given business group id.
462 -- Note that the name may be set to '%' in order to create all key flexfield
463 -- database items (see body documentation for more details).
464 --
465 procedure create_keyflex_dict
466 (
467 p_business_group_id in number,
468 p_keyflex_name in varchar2
469 );
470 --
471 ------------------------- delete_keyflex_dict -------------------------
472 --
473 -- delete the key flexfield database items for a given business group id.
474 -- Note that the name may be set to '%' in order to delete all key flexfield
475 -- database items (see body documentation for more details).
476 --
477 procedure delete_keyflex_dict
478 (
479 p_creator_id in number,
480 p_entity_name in varchar2,
481 p_leg_code in varchar2,
482 p_business_group_id in number
483 );
484 --
485 ---------------------- create_ext_acc_keyflex_dict -----------------------
486 --
487 -- Create Personal and Organization External Account Keyflexs
488 --
489 procedure create_ext_acc_keyflex_dict
490 (
491 p_id_flex_num in number
492 );
493 --
494 ------------------------- create_scl_flex_dict -------------------------
495 --
496 -- create the SCL key flexfield database items for a given id flex number.
497 --
498 procedure create_scl_flex_dict
499 (
500 p_id_flex_num in number
501 );
502 --
503 ------------------------- disable_ffue_cascade_trig -------------------------
504 --
505 -- disables the triggers fired off when delete from ff_user_entities which
506 -- gives a performance enhancement. As we have cleared ff_fdi_usages_f
507 -- previously, the validation here is unnecessary
508 --
509 procedure disable_ffue_cascade_trig;
510 --
511 ------------------------- enable_ffue_cascade_trig -------------------------
512 --
513 -- re-enables the same triggers above to allow for validation processing
514 --
515 procedure enable_ffue_cascade_trig;
516 --
517 ------------------------- disable_refbal_trig -------------------------
518 --
519 -- disables the triggers fired off when delete from ff_user_entities which
520 -- gives a performance enhancement. As we have cleared ff_fdi_usages_f
521 -- previously, the validation here is unnecessary
522 --
523 procedure disable_refbal_trig;
524 --
525 ------------------------- enable_refbal_trig -------------------------
526 --
527 -- re-enables the same triggers above to allow for validation processing
528 --
529 procedure enable_refbal_trig;
530 --
531 ------------------------- truncate_fcomp_info -------------------------
532 --
533 -- deletes all formula compilation info
534 --
535 procedure truncate_fcomp_info;
536 --
537 ----------------------- update_defined_balance -----------------------
538 --
542 --
539 -- Update a defined balance's database items for a set of languages.
540 -- The P_LANGUAGES list's indexes must start at 1 and go up in
541 -- increments of 1.
543 -- Notes: This is unsuitable for PAY_BALANCE_TYPES_F_TL or
544 -- PAY_BALANCE_DIMENSIONS_TL trigger calls because it builds the
545 -- database item names by fetching from these tables.
546 --
547 procedure update_defined_balance
548 (p_defined_balance_id in number
549 ,p_languages in dbms_sql.varchar2s
550 );
551 --
552 ------------------------- update_element_type ------------------------
553 --
554 -- Update an element type's database items for a set of languages.
555 -- The P_LANGUAGES list's indexes must start at 1 and go up in
556 -- increments of 1.
557 --
558 -- Notes: This is unsuitable for PAY_ELEMENT_TYPES_F_TL trigger call
559 -- because it builds names by fetching from PAY_ELEMENT_TYPES_F_TL.
560 --
561 procedure update_element_type
562 (p_element_type_id in number
563 ,p_effective_date in date
564 ,p_languages in dbms_sql.varchar2s
565 );
566 --
567 ------------------------- update_input_value -------------------------
568 --
569 -- Update an input value's database items for a set of languages.
570 -- The P_LANGUAGES list's indexes must start at 1 and go up in
571 -- increments of 1.
572 --
573 -- Notes: This is unsuitable for PAY_INPUT_VALUES_F_TL trigger call
574 -- because it builds names by fetching from PAY_INPUT_VALUES_F_TL.
575 --
576 procedure update_input_value
577 (p_input_value_id in number
578 ,p_effective_date in date
579 ,p_languages in dbms_sql.varchar2s
580 );
581 --------------------- process_pay_dyndbi_changes -------------------------
582 --
583 -- Process rows in PAY_DYNDBI_CHANGES to generate new translated
584 -- dynamic database item names at the end of translation patch
585 -- application.
586 --
590 ,retcode out nocopy number
587 --
588 procedure process_pay_dyndbi_changes
589 (errbuf out nocopy varchar2
591 ,p_element_types in varchar2
592 ,p_input_values in varchar2
593 ,p_defined_balances in varchar2
594 ,p_localization in varchar2
595 );
596 --
597 end hrdyndbi;
[Home] [Help]