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