1 package ben_batch_utils as
2 /* $Header: benrptut.pkh 120.2.12010000.1 2008/07/29 12:30:40 appldev ship $ */
3 /* ===========================================================================
4 * Name:
5 * Batch_utils
6 * Purpose:
7 * This package is provide all batch utility and data structure to simply
8 * batch process.
9 * History:
10 * Date Who Version What?
11 * ----------- ------------ ------- ------------------------------------
12 * 19-NOV-1998 Hdang 115.0 Created.
13 * 11-DEC-1998 Hdang 115.1 Add more functions. ret_str(s).
14 * 16-DEC-1998 Hdang 115.2 Add parameter into end_process proc.
15 * 22-DEC-1998 Hdang 115.3 Add parameter into print_parameter.
16 * 23-DEC-1998 Hdang 115.4 Add Person cache to header from body.
17 * 23-DEC-1998 Hdang 115.5 Add Prtt_enrt_rslt_id in to comp struct
18 * 29-DEC-1998 Hdang 115.6 Add Actn_cd into comp_cache.
19 * 06-JAN-1999 Hdang 115.7 Added new procedure for generic reports
20 * 05-APR-1999 mhoyes 115.11 Un-datetrack of per_in_ler_f changes.
21 * 20-JUL-1999 Gperry 115.12 genutils -> benutils package rename.
22 * 27-JUL-1999 mhoyes 115.13 - Changed g_report_rec ref it ben_type.
23 * 03-Nov-1999 lmcdonal 115.14 Added non_person_cd to end_process,
24 * write_logfile, create_restart...
25 * 21-JAN-2002 aprabhak 115.15 added enrt_perd_id to print_parameters
26 * 12-Mar-2002 maagrawa 115.16 Added missing dbdrv command.
27 * 19-Mar-2002 ikasire 115.17 Bug 2271796 added commit
28 * 26-Dec-2002 rpillay 115.18 NOCOPY changes
29 * 02-Jun-2003 glingapp 115.19 Added function rows_exist. This is
30 * called to check for child records of
31 * derived factors.
32 * 20-Aug-2004 nhunur 115.20 Added a procedure for person selection rule
33 * with proper error handling.
34 * 02-Nov-2004 abparekh 115.21 Bug 3517604 - Added p_date_From to procedure
35 * standard_header
36 * 03-Nov-06 swjain 115.22 Bug 5331889 - Added input1 as additional param
37 * in person_selection_rule for future use
38 * 09-Aug-07 vvprabhu 115.23 Bug 5857493 - added g_audit_flag to
39 control person selection rule error logging
40 * =====================================================================================
41 */
42 --
43 g_audit_flag boolean :=false;
44 -- Types declaration.
45 --
46 Type g_cache_person_rec is record
47 (full_name per_people_f.full_name%type
48 ,date_of_birth per_people_f.date_of_birth%type
49 ,date_of_death per_people_f.date_of_death%type
50 ,benefit_group ben_benfts_grp.name%type
51 ,benefit_group_id per_people_f.benefit_group_id%type
52 ,postal_code per_addresses.postal_code%type
53 ,national_identifier per_people_f.national_identifier%type
54 ,person_has_type_emp varchar2(1)
55 ,assignment_id per_assignments_f.assignment_id%type
56 ,fte_value per_assignment_budget_values.value%type
57 ,total_fte_value per_assignment_budget_values.value%type
58 ,per_system_status per_assignment_status_types.per_system_status%type
59 ,date_start per_periods_of_service.date_start%type
60 ,adjusted_svc_date per_periods_of_service.adjusted_svc_date%type
61 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
62 ,pay_period_start_date per_time_periods.start_date%type
63 ,pay_period_end_date per_time_periods.end_date%type
64 ,pay_period_next_start_date per_time_periods.start_date%type
65 ,pay_period_next_end_date per_time_periods.end_date%type
66 ,grade_id per_assignments_f.grade_id%type
67 ,job_id per_assignments_f.job_id%type
68 ,pay_basis_id per_assignments_f.pay_basis_id%type
69 ,pay_basis per_pay_bases.pay_basis%type
70 ,payroll_id per_assignments_f.payroll_id%type
71 ,payroll_name pay_all_payrolls_f.payroll_name%type
72 ,location_id per_assignments_f.location_id%type
73 ,address_line_1 hr_locations.address_line_1%type
74 ,organization_id per_assignments_f.organization_id%type
75 ,normal_hours per_assignments_f.normal_hours%type
76 ,frequency per_assignments_f.frequency%type
77 ,bargaining_unit_code per_assignments_f.bargaining_unit_code%type
78 ,hourly_salaried_code per_assignments_f.hourly_salaried_code%type
79 ,labour_union_member_flag per_assignments_f.labour_union_member_flag%type
80 ,assignment_status_type_id per_assignments_f.assignment_status_type_id%type
81 ,change_reason per_assignments_f.change_reason%type
82 ,employment_category per_assignments_f.employment_category%type
83 ,org_information1 hr_organization_information.org_information1%type
84 ,bg_name hr_all_organization_units.name%type
85 ,org_id hr_all_organization_units.organization_id%type
86 ,org_name hr_all_organization_units.name%type
87 );
88 --
89 Type g_cache_person_types_object is record
90 (person_type_id per_person_type_usages_f.person_type_id%type
91 ,user_person_type per_person_types.user_person_type%type
92 ,system_person_type per_person_types.system_person_type%type
93 );
94 --
95 type g_cache_person_types_rec is table of g_cache_person_types_object
96 index by binary_integer;
97 --
98 Type g_process_information_rec is record
99 (start_date date
100 ,start_time_numeric number
101 ,num_persons_selected number := 0
102 ,num_persons_errored number := 0
103 ,num_persons_unprocessed number := 0
104 ,num_persons_processed_succ number := 0
105 ,num_persons_processed number := 0
106 );
107 --
108 Type g_comp_obj_rec is record
109 (pgm_id ben_pgm_f.pgm_id%type
110 ,pl_typ_id ben_pl_typ_f.pl_typ_id%type
111 ,pl_id ben_pl_f.pl_id%type
112 ,oipl_id ben_oipl_f.oipl_id%type
113 ,opt_id ben_opt_f.opt_id%type
114 ,prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type
115 ,bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%type
116 ,uom ben_prtt_enrt_rslt_f.uom%type
117 ,cst_amt number(15)
118 ,credit_amt number(15)
119 ,cvg_strt_dt date
120 ,cvg_thru_dt date
121 ,upd_flag Boolean := FALSE
122 ,ins_flag Boolean := FALSE
123 ,del_flag Boolean := FALSE
124 ,def_flag Boolean := FALSE
125 ,susp_flag Boolean := FALSE
126 ,actn_cd varchar2(30)
127 );
128 Type g_comp_obj_table is table of g_comp_obj_rec Index by binary_integer;
129 --
130 Type g_pgm_rec is record
131 (pgm_id ben_pgm_f.pgm_id%type
132 ,name ben_pgm_f.name%type
133 );
134 Type g_pgm_table is table of g_pgm_rec Index by binary_integer;
135 --
136 Type g_pl_rec is record
137 (pl_id ben_pl_f.pl_id%type
138 ,name ben_pl_f.name%type
139 );
140 Type g_pl_table is table of g_pl_rec Index by binary_integer;
141 --
142 Type g_pl_typ_rec is record
143 (pl_typ_id ben_pl_typ_f.pl_typ_id%type
144 ,name ben_pl_typ_f.name%type
145 );
146 Type g_pl_typ_table is table of g_pl_typ_rec Index by binary_integer;
147 --
148 Type g_opt_rec is record
149 (oipl_id ben_oipl_f.oipl_id%type
150 ,opt_id ben_opt_f.opt_id%type
151 ,name ben_opt_f.name%type
152 );
153 Type g_opt_table is table of g_opt_rec Index by binary_integer;
154 --
155 Type g_processes_table is table of number index by binary_integer;
156 --
157 Type g_ref_cursor is ref cursor;
158 --
159 -- Global variable
160 --
161 g_record_error Exception;
162 g_debug Boolean:= FALSE;
163 g_mx_binary_integer constant binary_integer := 2147483647;
164 g_num_processes number := 0;
165 g_processes_tbl g_processes_table;
166 g_cache_comp g_comp_obj_table;
167 g_cache_comp_cnt binary_integer := 0;
168 g_rec ben_type.g_report_rec;
169 g_cache_person g_cache_person_rec;
170 --
171 -- ============================================================================
172 -- Function Name:<<cache_comp_obj>>
173 -- Description:
174 -- Cache_comp objects data into memory
175 --.
176 -- ============================================================================
177 --
178 Procedure Cache_comp_obj(p_pgm_id in number Default NULL
179 ,p_pl_typ_id in Number Default NULL
180 ,p_pl_id in Number Default NULL
181 ,p_oipl_id in Number Default NULL
182 ,p_opt_id in number Default NULL
183 ,p_bnft_amt in number Default NULL
184 ,p_uom in varchar2 Default NULL
185 ,p_cst_amt in number Default NULL
186 ,p_credit_amt in number Default NULL
187 ,p_cvg_strt_dt in date Default NULL
188 ,p_cvg_thru_dt in date Default hr_api.g_eot
189 ,p_prtt_enrt_rslt_id in number default NULL
190 ,p_effective_date in date
191 ,P_actn_cd in varchar2
192 ,p_suspended in varchar2 default 'N'
193 );
194 --
195 -- ============================================================================
196 -- Function Name:<<write_comp>>
197 -- Description:
198 -- Write cache out to file from Cache_comp objects data memory.
199 --
200 -- ============================================================================
201 --
202 Procedure write_comp(p_business_group_id in number
203 ,p_effective_date in date
204 );
205 --
206 -- ============================================================================
207 -- Function Name:<<get_pgm_name>>
208 -- Description:
209 -- Return program name from cache or from database.
210 --.
211 -- ============================================================================
212 --
213 Function get_pgm_name
214 (p_pgm_id in number
215 ,p_business_group_id in number
216 ,p_effective_date in date
217 ,p_batch_flag in boolean default FALSE
218 ) return varchar2;
219 --
220 -- ============================================================================
221 -- Function Name:<<get_pl_typ_name>>
222 -- Description:
223 -- Return plan type name from cache or from database.
224 --.
225 -- ============================================================================
226 --
227 Function get_pl_typ_name
228 (p_pl_typ_id in number
229 ,p_business_group_id in number
230 ,p_effective_date in Date
231 ,p_batch_flag in boolean default FALSE
232 ) return varchar2;
233 --
234 -- ============================================================================
235 -- Function Name:<<get_pl_name>>
236 -- Description:
237 -- Return plan name from cache or from database.
238 --.
239 -- ============================================================================
240 --
241 Function get_pl_name
242 (p_pl_id in number
243 ,p_business_group_id in number
244 ,p_effective_date in date
245 ,p_batch_flag in boolean default FALSE
246 ) return varchar2;
247 --
248 -- ============================================================================
249 -- Function Name:<<get_opt_name>>
250 -- Description:
251 -- Return option name from cache or from database.
252 --.
253 -- ============================================================================
254 --
255 Function get_opt_name
256 (p_oipl_id in number
257 ,p_business_group_id in number
258 ,p_effective_date in date
259 ,p_batch_flag in boolean default FALSE
260 ) return varchar2;
261 --
262 -- ============================================================================
263 -- Procedure Name:<<cache_person_information>>
264 -- Description:
265 -- Cache person infor into person cache data structure.
266 --.
267 -- ============================================================================
268 --
269 procedure cache_person_information
270 (p_person_id in number
271 ,p_business_group_id in number
272 ,p_effective_date in date
273 ,p_cache_time_perd_flag in boolean default TRUE
274 ,p_cache_pay_perd_flag in boolean default TRUE
275 ,p_cache_total_fte_flag in boolean default TRUE
276 ) ;
277 --
278 -- ============================================================================
279 -- Procedure Name:<<Person_header>>
280 -- Description:
281 -- Procedure to print out the header of a person.
282 -- ============================================================================
283 --
284 Procedure person_header
285 (p_person_id in number default null
286 ,p_business_group_id in number
287 ,p_effective_date in date
288 ) ;
289 --
290 -- ============================================================================
291 -- Procedure Name:<<Ini>>
292 -- Description:
293 -- Procedure is to initialize the all batch_utils caches or individual
294 -- cache such as person. comp. obj, etc...
295 -- P_actn_cd: Null - All
296 -- Per - Person cache.
297 -- Comp - Comp object cache.
298 -- comp_name - comp object name cache.
299 -- proc_info - process information.
300 --
301 -- ============================================================================
302 --
303 Procedure ini(p_actn_cd in Varchar2 default hr_api.g_varchar2 );
304 --
305 -- ============================================================================
306 -- Procedure Name:<<rpt_error>>
307 -- Description:
308 -- Procedure is used to debug.
309 --
310 -- ============================================================================
311 --
312 procedure rpt_error (p_proc in varchar2
313 ,p_last_actn in varchar2
314 ,p_rpt_flag in boolean default FALSE
315 );
316 --
317 -- ============================================================================
318 -- Procedure Name:<<person_selection_rule>>
319 -- Description:
320 -- Function will return Y if rule is passed. and N if fail. If error,
321 -- then it will raise ben_batch_utils.g_record_error.
322 --
323 -- ============================================================================
324 --
325 Function person_selection_rule
326 (p_person_id in Number
327 ,p_business_group_id in Number
328 ,p_person_selection_rule_id in Number
329 ,p_effective_date in Date
330 ,p_batch_flag in Boolean default FALSE
331 ,p_input1 in varchar2 default null -- Bug 5331889
332 ,p_input1_value in varchar2 default null
333 ) return char;
334 --
335 -- ============================================================================
336 -- Procedure Name:<<print_parameters>>
337 -- Description:
338 -- procedure print out the parameter list
339 --
340 -- ============================================================================
341 --
342 procedure print_parameters
343 (p_thread_id in number
344 ,p_validate in varchar2
345 ,p_benefit_action_id in number
346 ,p_effective_date in date
347 ,p_business_group_id in number
348 ,p_pgm_id in number default hr_api.g_number
349 ,p_pl_id in number default hr_api.g_number
350 ,p_popl_enrt_typ_cycl_id in number default hr_api.g_number
351 ,p_person_id in number default hr_api.g_number
352 ,p_person_type_id in number default hr_api.g_number
353 ,p_ler_id in number default hr_api.g_number
354 ,p_organization_id in number default hr_api.g_number
355 ,p_benfts_grp_id in number default hr_api.g_number
356 ,p_location_id in number default hr_api.g_number
357 ,p_legal_entity_id in number default hr_api.g_number
358 ,p_payroll_id in number default hr_api.g_number
359 ,p_no_programs in varchar2 default hr_api.g_varchar2
360 ,p_no_plans in varchar2 default hr_api.g_varchar2
361 ,p_rptg_grp_id in number default hr_api.g_number
362 ,p_pl_typ_id in number default hr_api.g_number
363 ,p_opt_id in number default hr_api.g_number
364 ,p_eligy_prfl_id in number default hr_api.g_number
365 ,p_vrbl_rt_prfl_id in number default hr_api.g_number
366 ,p_mode in varchar2 default hr_api.g_varchar2
367 ,p_person_selection_rule_id in number default hr_api.g_number
368 ,p_comp_selection_rule_id in number default hr_api.g_number
369 ,p_enrt_perd_id in number default hr_api.g_number
370 ,p_derivable_factors in varchar2 default hr_api.g_varchar2
371 ,p_audit_log in varchar2 default hr_api.g_varchar2
372 );
373 --
374 -- ============================================================================
375 -- Procedure Name:<<Check_all_slaves_finished>>
376 -- Description:
377 -- Procedure will make sure all the slaves belong to the master process
378 -- completed before exit the loop.
379 --
380 -- ============================================================================
381 --
382 Procedure check_all_slaves_finished(p_rpt_flag Boolean default FALSE);
383 --
384 -- ============================================================================
385 -- Procedure Name:<<Write_Rec>>
386 -- Description:
387 -- Procedure write an report record into ben_report table.
388 --
389 -- ============================================================================
390 --
391 Procedure Write_rec(p_typ_cd in varchar2
392 ,p_text in varchar2 default NULL
393 ,p_err_cd in varchar2 default NULL
394 );
395 --
396 -- ============================================================================
397 -- Procedure Name:<<Write>>
398 -- Description:
399 -- Procedure write text directly into log file. If not run from con-
400 -- current manager, then it will run dbms_output to screen if debug
401 -- flag set to TRUE.
402 --
403 -- ============================================================================
404 --
405 Procedure write (p_text varchar2);
406 --
407 -- ============================================================================
408 -- Procedure Name:<<Write_logfile>>
409 -- Description:
410 -- Procedure write process info into report table.
411 --
412 -- ============================================================================
413 --
414 procedure write_logfile(p_num_pers_processed in number
415 ,p_num_pers_errored in number
416 ,p_non_person_cd in varchar2 default null
417 );
418
419 --
420 -- ============================================================================
421 -- Procedure Name:<<End Process>>
422 -- Description:
423 -- Procedure write process info into report table.
424 --
425 -- ============================================================================
426 --
427 Procedure End_process (p_benefit_action_id in number
428 ,p_person_selected in number
429 ,p_business_group_id in number default NULL
430 ,p_non_person_cd in varchar2 default null
431 );
432 --
433 -- ============================================================================
434 -- <<Create_restart_person_actions>>
435 -- ============================================================================
436 --
437 Procedure create_restart_person_actions
438 (p_benefit_action_id in number
439 ,p_effective_date in date
440 ,p_chunk_size in number
441 ,p_threads in number
442 ,p_num_ranges out nocopy number
443 ,p_num_persons out nocopy number
444 ,p_commit_data in varchar2 default 'Y'
445 ,p_non_person_cd in varchar2 default null
446 );
447 --
448 -- ============================================================================
449 -- <<Batch_report>>
450 -- ============================================================================
451 --
452 Procedure batch_report
453 (p_concurrent_request_id in number
454 ,p_program_name in varchar2
455 ,p_subtitle in varchar2 default NULL
456 ,p_request_id out nocopy number
457 );
458 --
459 -- ============================================================================
460 -- <<Write_error_rec>>
461 -- ============================================================================
462 --
463 Procedure write_error_rec ;
464 --
465 -- ============================================================================
466 -- <<Summary_by_action>>
467 -- ============================================================================
468 --
469 procedure summary_by_action
470 (p_concurrent_request_id in number
471 ,p_cd_1 in varchar2, p_val_1 out nocopy number
472 ,p_cd_2 in varchar2, p_val_2 out nocopy number
473 ,p_cd_3 in varchar2, p_val_3 out nocopy number
474 ,p_cd_4 in varchar2, p_val_4 out nocopy number
475 ,p_cd_5 in varchar2, p_val_5 out nocopy number
476 ,p_cd_6 in varchar2, p_val_6 out nocopy number
477 ,p_cd_7 in varchar2, p_val_7 out nocopy number
478 ,p_cd_8 in varchar2, p_val_8 out nocopy number
479 ,p_cd_9 in varchar2, p_val_9 out nocopy number
480 ,p_cd_10 in varchar2, p_val_10 out nocopy number
481 );
482 --
483 -- ============================================================================
484 -- <<Procedure: *get_rpt_header*>>
485 -- ============================================================================
486 --
487 Procedure get_rpt_header
488 (p_concurrent_request_id in number
489 ,p_cd_1 out nocopy varchar2
490 ,p_cd_2 out nocopy varchar2
491 ,p_cd_3 out nocopy varchar2
492 ,p_cd_4 out nocopy varchar2
493 ,p_cd_5 out nocopy varchar2
494 ,p_cd_6 out nocopy varchar2
495 ,p_cd_7 out nocopy varchar2
496 ,p_cd_8 out nocopy varchar2
497 ,p_cd_9 out nocopy varchar2
498 ,p_cd_10 out nocopy varchar2
499 ,p_cd_11 out nocopy varchar2
500 ,p_cd_12 out nocopy varchar2
501 ,p_cd_13 out nocopy varchar2
502 ,p_cd_14 out nocopy varchar2
503 ,p_cd_15 out nocopy varchar2
504 ,p_cd_16 out nocopy varchar2
505 ,p_cd_17 out nocopy varchar2
506 ,p_cd_18 out nocopy varchar2
507 ,p_cd_19 out nocopy varchar2
508 ,p_cd_20 out nocopy varchar2
509 );
510 --
511 -- ============================================================================
512 -- <<Function: standart_header>>
513 -- ============================================================================
514 --
515 Procedure standard_header
516 (p_concurrent_request_id in number,
517 p_concurrent_program_name out nocopy varchar2,
518 p_process_date out nocopy date,
519 p_mode out nocopy varchar2,
520 p_derivable_factors out nocopy varchar2,
521 p_validate out nocopy varchar2,
522 p_person out nocopy varchar2,
523 p_person_type out nocopy varchar2,
524 p_program out nocopy varchar2,
525 p_business_group out nocopy varchar2,
526 p_plan out nocopy varchar2,
527 p_popl_enrt_typ_cycl out nocopy varchar2,
528 p_plans_not_in_programs out nocopy varchar2,
529 p_just_programs out nocopy varchar2,
530 p_comp_object_selection_rule out nocopy varchar2,
531 p_person_selection_rule out nocopy varchar2,
532 p_life_event_reason out nocopy varchar2,
533 p_organization out nocopy varchar2,
534 p_postal_zip_range out nocopy varchar2,
535 p_reporting_group out nocopy varchar2,
536 p_plan_type out nocopy varchar2,
537 p_option out nocopy varchar2,
538 p_eligibility_profile out nocopy varchar2,
539 p_variable_rate_profile out nocopy varchar2,
540 p_legal_entity out nocopy varchar2,
541 p_payroll out nocopy varchar2,
542 p_debug_message out nocopy varchar2,
543 p_location out nocopy varchar2,
544 p_audit_log out nocopy varchar2,
545 p_benfts_group out nocopy varchar2,
546 p_date_from out nocopy date, /* Bug 3517604 */
547 p_status out nocopy varchar2);
548 --
549 -- ============================================================================
550 -- <<Function: Ret_str (Overload function)>>
551 -- ============================================================================
552 --
553 Function ret_str(p_str varchar2, p_len number default 30) return varchar2;
554 Function ret_str(p_num number, p_len number default 15) return varchar2;
555 Function ret_str(p_date date, p_len number default 12) return varchar2;
556 --
557
558 -- Bug 2978945 added function rows_exist.
559 -- ============================================================================
560 -- Function Name:<<rows_exist>>
561 -- Description:
562 -- Return true if one or more record exists in the table
563 -- for the given id. This is similar to the rows_exist function
564 -- in dt_api, but for records that are not date tracked.
565 -- ============================================================================
566 --
567 FUNCTION rows_exist
568 (p_base_table_name IN VARCHAR2,
569 p_base_key_column IN VARCHAR2,
570 p_base_key_value IN NUMBER
571 )
572 RETURN BOOLEAN;
573 --
574 procedure person_selection_rule
575 (p_person_id in Number
576 ,p_business_group_id in Number
577 ,p_person_selection_rule_id in Number
578 ,p_effective_date in Date
579 ,p_input1 in varchar2 default null -- Bug 5331889
580 ,p_input1_value in varchar2 default null
581 ,p_return in out nocopy varchar2
582 ,p_err_message in out nocopy varchar2 ) ;
583
584
585 end ben_batch_utils;
[Home] [Help]