1 PACKAGE BODY ben_extract_seed AS
2 /* $Header: benextse.pkb 120.19.12000000.4 2007/02/27 19:44:05 tjesumic noship $ */
3
4 g_Ext_adv_crit_cmbn tbl_Ext_adv_crit_cmbn ;
5
6 procedure delete_crit_adv_conditon
7 (p_ext_crit_prfl_id in number ) is
8
9
10 cursor c1 is
11 select ecv.ext_crit_val_id ,
12 ecv.object_version_number ,
13 ecv.LEGISLATION_CODE
14 from ben_ext_crit_val ecv ,
15 ben_ext_crit_typ ect
16 where ect.ext_crit_prfl_id = p_ext_crit_prfl_id
17 and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
18 and ect.crit_typ_cd = 'ADV'
19 ;
20
21
22
23 cursor c2 (p_ext_crit_val_id number) is
24 select ext_crit_cmbn_id ,
25 object_version_number ,
26 LEGISLATION_CODE
27 from ben_ext_crit_cmbn
28 where ext_crit_val_id = p_ext_crit_val_id
29 ;
30
31 l_proc varchar2(100) := 'BEN_EXT_SEED.delete_crit_adv_conditon' ;
32 l_object_version_number number ;
33
34 Begin
35 hr_utility.set_location(' Entering ' || l_proc, 10);
36
37 for i in c1
38 Loop
39
40 for k in c2(i.ext_crit_val_id)
41 Loop
42 --- celete adv crit combn values
43 BEN_ext_crit_cmbn_API.delete_ext_crit_cmbn
44 (p_validate => FALSE
45 ,p_EXT_CRIT_CMBN_ID => k.EXT_CRIT_CMBN_ID
46 ,p_LEGISLATION_CODE => k.LEGISLATION_CODE
47 ,p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
48 ,p_effective_date => trunc(sysdate)
49 );
50
51 End Loop ;
52 --- delete the criteria values
53 BEN_ext_crit_val_API.delete_ext_crit_val
54 (p_validate => FALSE
55 ,p_EXT_CRIT_VAL_ID => I.EXT_CRIT_VAL_ID
56 ,p_LEGISLATION_CODE => I.LEGISLATION_CODE
57 ,p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
58 );
59
60 end Loop ;
61
62 --- delete the global collection
63 g_Ext_adv_crit_cmbn.delete ;
64 hr_utility.set_location(' Leaving ' || l_proc, 10);
65 end ;
66
67
68 procedure set_adv_cond_cmbn
69 ( p_old_ext_crit_val_id in number ,
70 p_new_ext_crit_val_id in number ) is
71
72 l_count number ;
73 l_found varchar2(1) ;
74 l_proc varchar2(100) := 'BEN_EXT_SEED.set_adv_cond_cmbn' ;
75 Begin
76 hr_utility.set_location(' Entering ' || l_proc, 10);
77
78 l_found := 'N' ;
79 l_count := 0 ;
80 if p_old_ext_crit_val_id is not null and p_new_ext_crit_val_id is not null then
81 l_count := g_Ext_adv_crit_cmbn.count ;
82 for i in 1 .. l_count
83 Loop
84 if g_Ext_adv_crit_cmbn(i).old_crit_val_id = p_old_ext_crit_val_id then
85 l_found := 'Y' ;
86 exit ;
87 end if ;
88 End loop ;
89
90 if l_found = 'N' then
91 g_Ext_adv_crit_cmbn(nvl(l_count,0) + 1 ).old_crit_val_id := p_old_ext_crit_val_id ;
92 g_Ext_adv_crit_cmbn(nvl(l_count,0) + 1 ).new_crit_val_id := p_new_ext_crit_val_id ;
93 end if ;
94 end if ;
95
96 End ;
97
98
99
100 function get_adv_cond_cmbn ( p_old_ext_crit_val_id in number )
101 return number is
102 l_count number ;
103 l_return_val number ;
104 Begin
105
106 l_count := g_Ext_adv_crit_cmbn.count ;
107 for i in 1 .. l_count
108 Loop
109 if g_Ext_adv_crit_cmbn(i).old_crit_val_id = p_old_ext_crit_val_id then
110 l_return_val := g_Ext_adv_crit_cmbn(i).new_crit_val_id ;
111 exit ;
112 end if ;
113 End loop ;
114 Return l_return_val ;
115
116 End ;
117
118
119
120 PROCEDURE write_err
121 (p_err_num in varchar2 default null,
122 p_err_msg in varchar2 default null,
123 p_typ_cd in varchar2 default null,
124 p_business_group_id in number default null
125 ) is
126
127 l_string varchar2(50) ;
128
129 Begin
130
131 if p_typ_cd = 'E' then
132 g_errors_count := g_errors_count + 1 ;
133 l_string := ' ERROR : ' ;
134 else
135 l_string := ' WARNING : ' ;
136 end if ;
137
138 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
139 if p_err_msg is not null then
140 fnd_file.put_line(fnd_file.log,l_string || p_err_msg);
141 elsif p_err_num is not null then
142 fnd_message.set_name(substr(p_err_num,1,3),p_err_num);
143 end if ;
144 end if ;
145
146
147 if g_errors_count > g_max_errors_allowed then
148 fnd_message.set_name('BEN','BEN_91947_EXT_MX_ERR_NUM');
149 fnd_file.put_line(fnd_file.log, fnd_message.get);
150 fnd_message.raise_error;
151 end if ;
152
153 End write_err ;
154
155
156 PROCEDURE validate_data(validate IN VARCHAR2 DEFAULT null )
157 is
158 Begin
159 if nvl(validate,'N') = 'Y' then
160 g_file_count := g_file_count + 1 ;
161 if g_file_count >= g_total_file then
162 ROLLBACK TO SUBMIT_EXIM_REQUEST;
163 g_file_count := 0 ;
164 end if ;
165 end if ;
166 end validate_data ;
167
168
169 Procedure load_business_group(p_owner IN VARCHAR2
170 ,p_legislation_code IN VARCHAR2
171 ,p_business_group in VARCHAR2
172 ,p_totalcount in VARCHAR2 default null
173 ,p_allow_override in VARCHAR2 default null
174 ) is
175
176 l_threads number;
177 l_chunk_size number;
178
179 Begin
180 g_business_group_id := null ;
181 if p_business_group is not null then
182 begin
183 select business_group_id
184 into g_business_group_id
185 from per_business_groups_perf
186 where name = p_business_group ;
187 exception
188 when no_data_found then
189 raise ;
190 end ;
191 end if ;
192 --- validating the businesss group id with type of extract
193 if p_owner = 'CUSTOM' then
194 -- if the extract is custom and and business group_id not provided error
195 if g_business_group_id is null then
196 fnd_message.set_name('BEN','BEN_91000_INVALID_BUS_GROUP');
197 fnd_message.raise_error;
198 end if ;
199 else
200 if g_business_group_id is not null then
201 fnd_message.set_name('BEN','BEN_93200_PDC_INVALID_BG_ER');
202 fnd_message.raise_error;
203 end if ;
204 end if ;
205
206 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
207 benutils.get_parameter
208 (p_business_group_id => g_business_group_id
209 ,p_batch_exe_cd => 'BENXEXP'
210 ,p_threads => l_threads
211 ,p_chunk_size => l_chunk_size
212 ,p_max_errors => g_max_errors_allowed);
213 end if ;
214 g_errors_count := 0 ;
215 if p_totalcount is not null then
216 g_total_file := to_number(p_totalcount) ;
217 end if ;
218 g_override := nvl(p_allow_override,'N') ;
219 savepoint SUBMIT_EXIM_REQUEST;
220 End load_business_group ;
221
222
223 -----------------------------------------------------
224 --- This function is added to get the lookup code
225 --- so far the ldt extract the name that is creating a issue when the lookup meaning are changed
226 --- this function also allow to extract the lookup code , the criteria is from hr lookups
227 -- if the criteria from master tables like person name then return null for the code
228 ------------------------------------------------------
229
230 Function get_lookup_code (p_crit_typ_cd in VARCHAR2
231 ,p_val in VARCHAR2
232 ,p_val_order IN VARCHAR2
233 ,p_bg_group_id IN NUMBER default null
234 )return varchar2 is
235
236
237
238 cursor find_lookup ( p_lookup_type in varchar2) is
239 select 'x' from hr_lookups
240 where lookup_type = p_lookup_type
241 and lookup_code = p_val
242 ;
243
244 l_return_val varchar2(30) ;
245 l_dummy varchar2(1) ;
246 l_lookup_type hr_lookups.lookup_type%type ;
247
248 Begin
249
250
251 if p_crit_typ_cd = 'PST' then
252 l_lookup_type := 'US_STATE' ;
253
254 elsif p_crit_typ_cd in ('BECLES', 'BERLES') then
255
256 l_lookup_type := 'BEN_PER_IN_LER_STAT' ;
257
258 elsif p_crit_typ_cd = 'BSE' then
259
260 l_lookup_type := 'BEN_EXT_SUSPEND' ;
261
262 elsif p_crit_typ_cd in ('BECMIS', 'BERMIS','BACMIS') then
263
264 l_lookup_type := 'BEN_EXT_CRIT_MISC' ;
265
266 --elsif then
267
268 -- l_lookup_type := 'BEN_ENRT_RSLT_MTHD' ;
269
270 elsif p_crit_typ_cd = 'BERSTA' then
271
272 l_lookup_type := 'BEN_PRTT_ENRT_RSLT_STAT' ;
273
274 elsif p_crit_typ_cd in ('BDTOR' , 'PASOR' , 'BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD','BERCDP','BERLUD',
275 'BERLND', 'BERLOD' , 'MTBSDT', 'MPCLUD', 'MPCPLUD', 'MSDT', 'CAD', 'CED','RPPEDT' ,
276 'EPMNYR','EPLDT' ) then
277
278 l_lookup_type := 'BEN_EXT_DT' ;
279
280 elsif p_crit_typ_cd in ('CCE' ) then
281
282 l_lookup_type := 'BEN_EXT_CHG_EVT' ;
283
284 --elsif then
285
286 -- l_lookup_type := 'BEN_EXT_TTL_COND_OPER' ;
287
288 elsif p_crit_typ_cd = 'PASU' then
289
290 l_lookup_type := 'BEN_EXT_ASMT_TO_USE' ;
291
292 elsif p_crit_typ_cd = 'BERENM' then
293
294 l_lookup_type := 'BEN_ENRT_MTHD' ;
295
296 elsif p_crit_typ_cd = 'PDL' then
297
298 l_lookup_type := 'BEN_EXT_PER_DATA_LINK' ;
299
300
301
302 End if ;
303
304
305 if l_lookup_type is not null then
306 open find_lookup(l_lookup_type) ;
307 fetch find_lookup into l_dummy ;
308 if find_lookup%found then
309 l_return_val := p_val ;
310 end if ;
311 close find_lookup ;
312 end if ;
313
314
315
316 Return l_return_val ;
317 End ;
318
319
320
321 FUNCTION get_value (p_crit_typ_cd in VARCHAR2
322 ,p_val in VARCHAR2
323 ,p_val_order IN VARCHAR2
324 ,p_bg_group_id IN NUMBER default null
325 )return varchar2 is
326 l_crit_typ_cd varchar2(30);
327 l_oper_cd varchar2(30);
328 l_val1 varchar2(200);
329 p_business_group_id number := p_bg_group_id ;
330 p_legislation_code varchar2(240); --utf8
331 l_val2 varchar2(200);
332 l_crit_cmbn varchar2(2000) := '';
333 --value varchar2(80) := ''; UTF8IK
334 value varchar2(240) := '';
335 cursor c1 is select meaning
336 from hr_lookups
337 where lookup_type = 'US_STATE'
338 and lookup_code = p_val;
339
340
341 cursor c2 is select name
342 from ben_benfts_grp
343 where benfts_grp_id = p_val;
344
345 cursor c3 is SELECT user_status from PER_ASSIGNMENT_STATUS_TYPES
346 WHERE active_flag ='Y'
347 and assignment_status_type_id = p_val;
348
349 cursor c4 is SELECT name from PER_ORGANIZATION_UNITS
350 WHERE internal_external_flag = 'INT'
351 and organization_id = p_val;
352
353 cursor c5 is select location_code from hr_locations
354 where trunc(sysdate) <=
355 nvl(inactive_date,to_date('31124712','DDMMYYYY'))
356 and location_id = p_val;
357
358 cursor c6 is SELECT name from hr_tax_units_v
359 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
360 = nvl(p_business_group_id,-1)
361 and trunc(sysdate)
362 between nvl(date_from,trunc(sysdate))
363 and nvl(date_to,trunc(sysdate))
364 and tax_unit_id = p_val;
365
366 cursor c7 is SELECT full_name||' '||national_identifier
367 ||' '||employee_number from per_all_people_f
368 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
369 = nvl(p_business_group_id,-1)
370 and trunc(sysdate)
371 between nvl(effective_start_date,trunc(sysdate))
372 and nvl(effective_end_date,trunc(sysdate))
373 and person_id = p_val;
374
375
376
377 cursor c8 is select formula_name
378 from ff_formulas_f
379 where --nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
380 -- = nvl(p_legislation_code,'~~nvl~~')
381 -- and nvl(business_group_id,nvl(p_business_group_id,-1))
382 -- = nvl(p_business_group_id,-1) and
383 trunc(sysdate) between effective_start_date
384 and effective_end_date
385 and formula_id = p_val;
386
387 cursor c9 is select name
388 from ben_ler_f
389 where nvl(business_group_id,nvl(p_business_group_id,-1))
390 = nvl(p_business_group_id,-1)
391 and trunc(sysdate) between effective_start_date
392 and effective_end_date
393 and ler_id = p_val;
394
395 cursor c10 is select user_person_type
396 from per_person_types
397 where nvl(business_group_id,nvl(p_business_group_id,-1))
398 = nvl(p_business_group_id,-1)
399 and active_flag = 'Y'
400 and person_type_id = p_val;
401
402 cursor ca is SELECT name from ben_cm_typ_f
403 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
404 = nvl(p_business_group_id,-1)
405 and cm_typ_id = p_val;
406
407
408 cursor cb is SELECT payroll_name from pay_all_payrolls_f
409 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
410 = nvl(p_business_group_id,-1)
411 and trunc(sysdate)
412 between nvl(effective_start_date,trunc(sysdate))
413 and nvl(effective_end_date,trunc(sysdate))
414 and payroll_id = p_val;
415
416
417 cursor cc is SELECT element_name from pay_element_types_f
418 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
419 = nvl(p_business_group_id,-1)
420 and element_type_id = p_val;
421
422
423 cursor cd is SELECT name from pay_input_values_f
424 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
425 = nvl(p_business_group_id,-1)
426 and input_value_id = p_val;
427
428
429 cursor ce is select name
430 from ben_pl_f
431 where business_group_id = p_business_group_id
432 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
433 and nvl(effective_end_date,trunc(sysdate))
434 and pl_id = p_val;
435 /*
436 cursor cf is select name
437 from ben_rptg_grp_v
438 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
439 = nvl(p_business_group_id,-1)
440 and rptg_grp_id = p_val;
441 */
442
443 cursor cf is select meaning
444 from hr_lookups
445 where lookup_type = 'BEN_PER_IN_LER_STAT'
446 and lookup_code = p_val;
447
448
449 cursor cg is select meaning
450 from hr_lookups
451 where lookup_type = 'BEN_EXT_SUSPEND'
452 and lookup_code = p_val;
453
454 cursor ch is select name
455 from ben_ler_v
456 where business_group_id = p_business_group_id
457 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
458 and nvl(effective_end_date,trunc(sysdate))
459 and ler_id = p_val;
460
461
462 cursor ci is select name
463 from ben_pgm_f
464 where business_group_id = p_business_group_id
465 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
466 and nvl(effective_end_date,trunc(sysdate))
467 and pgm_id = p_val;
468
469
470 cursor cj is select name
471 from ben_pl_f
472 where business_group_id = p_business_group_id
473 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
474 and nvl(effective_end_date,trunc(sysdate))
475 and pl_id = p_val;
476
477 cursor ck is select name
478 from ben_rptg_grp_v
479 where business_group_id = p_business_group_id
480 and rptg_grp_id = p_val;
481
482
483 cursor cl is select meaning
484 from hr_lookups
485 where lookup_type = 'BEN_EXT_CRIT_MISC'
486 and lookup_code = p_val;
487
488
489 cursor cm is select name
490 from ben_pl_typ_f
491 where business_group_id = p_business_group_id
492 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
493 and nvl(effective_end_date,trunc(sysdate))
494 and pl_typ_id = p_val;
495
496
497 cursor cn is select start_date||' - '||end_date
498 from ben_yr_perd
499 where business_group_id = p_business_group_id
500 and yr_perd_id = p_val;
501
502 cursor co is select meaning
503 from hr_lookups
504 where lookup_type = 'BEN_EXT_CRIT_MISC'
505 and lookup_code = p_val;
506
507 cursor cp is select meaning
508 from hr_lookups
509 where lookup_type = 'BEN_EXT_SUSPEND'
510 and lookup_code = p_val;
511
512 cursor cq is select meaning
513 from hr_lookups
514 where lookup_type = 'BEN_ENRT_RSLT_MTHD'
515 and lookup_code = p_val;
516
517 cursor cr is select meaning
518 from hr_lookups
519 where lookup_type = 'BEN_PRTT_ENRT_RSLT_STAT'
520 and lookup_code = p_val;
521
522 cursor cs is select meaning
523 from hr_lookups
524 where lookup_type = 'BEN_EXT_DT'
525 and lookup_code = p_val;
526
527 cursor ct is select name
528 from ben_actn_typ
529 where actn_typ_id = p_val ;
530
531
532 cursor cu is select meaning
533 from hr_lookups
534 where lookup_type = 'BEN_EXT_DT'
535 and lookup_code = p_val
536 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
537 and nvl(end_date_active, trunc(sysdate))
538 ;
539
540
541
542 cursor cv is select meaning
543 from hr_lookups
544 where lookup_type = 'BEN_EXT_DT'
545 and lookup_code = p_val
546 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
547 and nvl(end_date_active, trunc(sysdate))
548 ;
549
550 cursor cw is select meaning
551 from hr_lookups
552 where lookup_type = 'BEN_EXT_CHG_EVT'
553 and lookup_code = p_val
554 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
555 and nvl(end_date_active, trunc(sysdate)) ;
556
557 cursor cep is select event_group_name
558 from pay_event_groups
559 where event_group_id = p_val
560 ;
561
562 cursor cx is select user_name
563 from fnd_user
564 where user_id = p_val
565 and trunc(sysdate) between nvl(start_date, trunc(sysdate))
566 and nvl(end_date, trunc(sysdate));
567
568 cursor cy is select meaning
569 from hr_lookups
570 where lookup_type = 'BEN_EXT_DT'
571 and lookup_code = p_val
572 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
573 and nvl(end_date_active, trunc(sysdate))
574 ;
575
576 cursor cz is select meaning
577 from hr_lookups
578 where lookup_type = 'BEN_EXT_DT'
579 and lookup_code = p_val
580 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
581 and nvl(end_date_active, trunc(sysdate))
582 ;
583
584 cursor caa is select meaning
585 from hr_lookups
586 where lookup_type = 'BEN_EXT_DT'
587 and lookup_code = p_val
588 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
589 and nvl(end_date_active, trunc(sysdate))
590 ;
591
592 cursor cab is select meaning
593 from hr_lookups
594 where lookup_type = 'BEN_EXT_DT'
595 and lookup_code = p_val
596 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
597 and nvl(end_date_active, trunc(sysdate))
598 ;
599
600 cursor cac is select meaning
601 from hr_lookups
602 where lookup_type = 'BEN_EXT_DT'
603 and lookup_code = p_val
604 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
605 and nvl(end_date_active, trunc(sysdate))
606 ;
607
608 cursor cad is select meaning
609 from hr_lookups
610 where lookup_type = 'BEN_EXT_DT'
611 and lookup_code = p_val
612 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
613 and nvl(end_date_active, trunc(sysdate))
614 ;
615
616 cursor cae is select meaning
617 from hr_lookups
618 where lookup_type = 'BEN_EXT_DT'
619 and lookup_code = p_val
620 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
621 and nvl(end_date_active, trunc(sysdate))
622 ;
623
624 cursor caf is select meaning
625 from hr_lookups
626 where lookup_type = 'BEN_EXT_DT'
627 and lookup_code = p_val
628 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
629 and nvl(end_date_active, trunc(sysdate))
630 ;
631
632 cursor cag is select meaning
633 from hr_lookups
634 where lookup_type = 'BEN_EXT_DT'
635 and lookup_code = p_val
636 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
637 and nvl(end_date_active, trunc(sysdate))
638 ;
639
640
641 cursor cah is select meaning
642 from hr_lookups
643 where lookup_type = 'BEN_EXT_DT'
644 and lookup_code = p_val
645 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
646 and nvl(end_date_active, trunc(sysdate))
647 ;
648
649
650 cursor c_val1_dt
651 is
652 select meaning
653 from hr_lookups
654 where lookup_type = 'BEN_EXT_DT'
655 and lookup_code = p_val;
656
657 cursor c_val2_dt
658 is
659 select meaning
660 from hr_lookups
661 where lookup_type = 'BEN_EXT_DT'
662 and lookup_code = p_val;
663
664 cursor c_val1_chg_evt
665 is
666 select meaning
667 from hr_lookups
668 where lookup_type = 'BEN_EXT_CHG_EVT'
669 and lookup_code = p_val;
670
671 cursor c_val2_chg_evt
672 is
673 select meaning
674 from hr_lookups
675 where lookup_type = 'BEN_EXT_CHG_EVT'
676 and lookup_code = p_val;
677
678 cursor c_oper
679 is
680 select meaning
681 from hr_lookups
682 where lookup_type = 'BEN_EXT_TTL_COND_OPER'
683 and lookup_code = p_val; --p_oper_cd;
684
685
686 cursor c_pasu
687 is
688 select meaning
689 from hr_lookups
690 where lookup_type = 'BEN_EXT_ASMT_TO_USE'
691 and lookup_code = p_val; -- value for the criteria Person Assignment To Use
692
693 CURSOR Cai IS
694 select MEANING
695 from HR_LOOKUPS
696 where LOOKUP_CODE = p_crit_typ_cd
697 and lookup_type = 'BEN_EXT_CRIT_TYP'
698 and substr(lookup_code, 1, 1) = 'C'
699 and lookup_code not in ('CBU')
700 and trunc(sysdate) between
701 nvl(start_date_active, trunc(sysdate))
702 and nvl(end_date_active, trunc(sysdate))
703 ;
704
705 cursor c_berenm
706 is
707 select meaning
708 from hr_lookups
709 where lookup_type = 'BEN_ENRT_MTHD'
710 and lookup_code = p_val; -- value for the criteria Enrollment method
711
712
713 cursor c_enb is
714 select to_char(enp.strt_dt, 'DD-MON-RRRR') || ' - ' || to_char( enp.end_dt, 'DD-MON-RRRR')
715 from ben_enrt_perd enp
716 where ENRT_PERD_ID = p_val
717 ;
718
719
720 cursor c_lookup(p_type varchar2) is
721 select meaning
722 from hr_lookups
723 where lookup_type = p_type
724 and lookup_code = p_val; --
725
726 cursor c_job is
727 select name
728 from per_jobs_vl job
729 where job_id = p_val;
730
731 cursor c_bg is
732 select name
733 from per_business_groups_perf
734 where business_group_id = p_val;
735
736 cursor c_pos is
737 select name
738 from HR_ALL_POSITIONS_F job
739 where position_id = p_val
740 and trunc(sysdate) between
741 EFFECTIVE_START_DATE
742 and EFFECTIVE_END_DATE ;
743
744 cursor c_asgset is
745 select ASSIGNMENT_SET_NAME from
746 hr_assignment_sets
747 where ASSIGNMENT_SET_ID = p_val
748 ;
749
750 BEGIN
751 --
752 if p_crit_typ_cd = 'PST' then
753 open c1;
754 fetch c1 into value;
755 close c1;
756 elsif p_crit_typ_cd = 'PBG' then
757 open c2;
758 fetch c2 into value;
759 close c2;
760 elsif p_crit_typ_cd = 'PAS' then
761 open c3;
762 fetch c3 into value;
763 close c3;
764
765 elsif p_crit_typ_cd = 'POR' then
766 open c4;
767 fetch c4 into value;
768 close c4;
769 elsif p_crit_typ_cd = 'PLO' then
770 open c5;
771 fetch c5 into value;
772 close c5;
773 elsif p_crit_typ_cd = 'PLE' then
774 open c6;
775 fetch c6 into value;
776 close c6;
777 elsif p_crit_typ_cd = 'PID' then
778 open c7;
779 fetch c7 into value;
780 close c7;
781 elsif p_crit_typ_cd = 'PRL' then
782 open c8;
783 fetch c8 into value;
784 close c8;
785 elsif p_crit_typ_cd = 'PLV' then
786 open c9;
787 fetch c9 into value;
788 close c9;
789 elsif p_crit_typ_cd = 'PPT' then
790 open c10;
791 fetch c10 into value;
792 close c10;
793 elsif p_crit_typ_cd = 'MTP' then
794 open ca;
795 fetch ca into value;
796 close ca;
797 elsif p_crit_typ_cd = 'RRL' then
798 open cb;
799 fetch cb into value;
800 close cb;
801 elsif p_crit_typ_cd = 'REE' and p_val_order = 'VAL_2'then
802 open cc;
803 fetch cc into value;
804 close cc;
805 elsif p_crit_typ_cd = 'REE' and p_val_order = 'VAL_1'then
806 open cd;
807 fetch cd into value;
808 close cd;
809 elsif p_crit_typ_cd in ('BECLEN', 'BERLEN') then
810 open c9;
811 fetch c9 into value;
812 close c9;
813
814 elsif p_crit_typ_cd in ('BECLES', 'BERLES') then
815 open cf;
816 fetch cf into value;
817 close cf;
818
819 elsif p_crit_typ_cd in ('BECPLN', 'BPL') then
820 open ce;
821 fetch ce into value;
822 close ce;
823
824 elsif p_crit_typ_cd in ('BECRPG', 'BRG') then
825 open ck;
826 fetch ck into value;
827 close ck;
828
829 elsif p_crit_typ_cd in ('BECPGN', 'BERPGN') then
830 open ci;
831 fetch ci into value;
832 close ci;
833
834 elsif p_crit_typ_cd in ('BECPTN', 'BERPTN') then
835 open cm;
836 fetch cm into value;
837 close cm;
838
839 elsif p_crit_typ_cd = 'BECYRP' then
840 open cn;
841 fetch cn into value;
842 close cn;
843
844 elsif p_crit_typ_cd in ('BECMIS', 'BERMIS','BACMIS') then
845 open cl;
846 fetch cl into value;
847 close cl;
848
849
850 elsif p_crit_typ_cd = 'BSE' then
851 open cg;
852 fetch cg into value;
853 close cg;
854
855 elsif p_crit_typ_cd = 'BERENM' then
856 open c_berenm;
857 fetch c_berenm into value;
858 close c_berenm;
859
860 elsif p_crit_typ_cd = 'BERSTA' then
861 open cr;
862 fetch cr into value;
863 close cr;
864
865 /* elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD', 'BERCDP', 'BERLUD',
866 'BERLND', 'BERLOD') then
867 open cp;
868 fetch cp into value;
869 if cp%notfound then
870 value := p_val;
871 end if;
872 close cp;
873 */
874 elsif p_crit_typ_cd = 'BACN' then
875 open ct;
876 fetch ct into value;
877 close ct;
878 elsif p_crit_typ_cd in ('BDTOR') then
879 open cu;
880 fetch cu into value;
881 if cu%notfound then
882 value := p_val;
883 end if;
884 close cu;
885 elsif p_crit_typ_cd in ('PASOR') then
886 open cv;
887 fetch cv into value;
888 if cv%notfound then
889 value := p_val;
890 end if;
891 close cv;
892 elsif p_crit_typ_cd = 'CCE' then
893 open cw;
894 fetch cw into value;
895 close cw;
896 -- elsif p_crit_typ_cd = 'CEP' and p_val_order = 'VAL_2' then
897 elsif p_crit_typ_cd = 'CPE' and p_val_order = 'VAL_2' then -- anshghos
898 value := P_VAL ;
899 -- elsif p_crit_typ_cd = 'CEP' and p_val_order = 'VAL_1' then
900 elsif p_crit_typ_cd = 'CPE' and p_val_order = 'VAL_1' then -- anshghos
901 open CEP;
902 fetch CEP into value;
903 close CEP;
904
905 elsif p_crit_typ_cd = 'CBU' then
906 open cx;
907 fetch cx into value;
908 close cx;
909 elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD','BERCDP','BERLUD',
910 'BERLND', 'BERLOD') then
911 open cy;
912 fetch cy into value;
913 if cy%notfound then
914 value := p_val;
915 end if;
916 close cy;
917 /*
918 elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD', 'BERCDP', 'BERLUD',
919 'BERLND', 'BERLOD') then
920 open cz;
921 fetch cz into value;
922 if cz%notfound then
923 value := p_val;
924 end if;
925 close cz;
926 */
927 elsif p_crit_typ_cd in ('MTBSDT', 'MPCLUD', 'MPCPLUD', 'MSDT') then
928 open caa;
929 fetch caa into value;
930 if caa%notfound then
931 value := p_val;
932 end if;
933 close caa;
934 elsif p_crit_typ_cd in ('MTBSDT', 'MPCLUD', 'MPCPLUD', 'MSDT') then
935 open cab;
936 fetch cab into value;
937 if cab%notfound then
938 value := p_val;
939 end if;
940 close cab;
941 elsif p_crit_typ_cd in ('CAD', 'CED') then
942 open cac;
943 fetch cac into value;
944 if cac%notfound then
945 value := p_val;
946 end if;
947 close cac;
948 elsif p_crit_typ_cd in ('CAD', 'CED') then
949 open cad;
950 fetch cad into value;
951 if cad%notfound then
952 value := p_val;
953 end if;
954 close cad;
955 elsif p_crit_typ_cd is not null and p_crit_typ_cd = 'RPPEDT' then
956 open cag;
957 fetch cag into value;
958 if cag%notfound then
959 value := p_val;
960 end if;
961 close cag;
962 elsif p_crit_typ_cd is not null and p_crit_typ_cd = 'RPPEDT' then
963 open cah;
964 fetch cah into value;
965 if cah%notfound then
966 value := p_val;
967 end if;
968 close cah;
969
970
971 elsif p_crit_typ_cd in ( 'CAD','CED','EPMNYR','EPLDT' ) then
972 open c_val1_dt;
973 fetch c_val1_dt into value;
974 close c_val1_dt;
975 /*
976 open c_val2_dt;
977 fetch c_val2_dt into value;
978 close c_val2_dt;
979 */
980 elsif p_crit_typ_cd = 'CCE' then
981 open c_val1_chg_evt;
982 fetch c_val1_chg_evt into value;
983 close c_val1_chg_evt;
984 open c_val2_chg_evt;
985 fetch c_val2_chg_evt into value;
986 close c_val2_chg_evt;
987
988 elsif p_crit_typ_cd = 'PASU' then
989 open c_pasu;
990 fetch c_pasu into value;
991 close c_pasu;
992
993 elsif p_crit_typ_cd = 'HRL' then
994 open c8;
995 fetch c8 into value;
996 close c8;
997 elsif p_crit_typ_cd = 'RFFRL' then
998 open c8;
999 fetch c8 into value;
1000 close c8;
1001
1002 elsif p_crit_typ_cd = 'WPLPR' and p_val_order = 'VAL_2'then
1003 -- plan
1004 open cj;
1005 fetch cj into value;
1006 close cj;
1007 elsif p_crit_typ_cd = 'WPLPR' and p_val_order = 'VAL_1'then
1008 -- plan enrollment period
1009 open c_enb;
1010 fetch c_enb into value;
1011 close c_enb;
1012
1013 elsif p_crit_typ_cd = 'PDL' then
1014 Open c_lookup('BEN_EXT_PER_DATA_LINK') ;
1015 fetch c_lookup into value;
1016 close c_lookup;
1017
1018 elsif p_crit_typ_cd = 'HJOB' then
1019 open c_job;
1020 fetch c_job into value;
1021 close c_job;
1022 elsif p_crit_typ_cd = 'HORG' then
1023 open c4;
1024 fetch c4 into value;
1025 close c4;
1026 elsif p_crit_typ_cd = 'HPOS' then
1027 open c_pos;
1028 fetch c_pos into value;
1029 close c_pos;
1030 elsif p_crit_typ_cd = 'HPY' then
1031 open cb;
1032 fetch cb into value;
1033 close cb;
1034 elsif p_crit_typ_cd = 'HLOC' then
1035 open c5;
1036 fetch c5 into value;
1037 close c5;
1038 elsif p_crit_typ_cd = 'HBG' then
1039 open c_bg;
1040 fetch c_bg into value;
1041 close c_bg;
1042 elsif p_crit_typ_cd = 'PBGR' then
1043 open c_bg;
1044 fetch c_bg into value;
1045 close c_bg;
1046 elsif p_crit_typ_cd = 'PASGSET' then
1047
1048 open c_asgset ;
1049 fetch c_asgset into value ;
1050 close c_asgset ;
1051
1052 else
1053 value := p_val ;
1054
1055 end if;
1056
1057
1058
1059 /* OPEN Cai;
1060 FETCH Cai into value;
1061 CLOSE Cai;
1062 */
1063 return value;
1064 end;
1065
1066
1067 function decode_value (p_crit_typ_cd in VARCHAR2,
1068 p_meaning in VARCHAR2
1069 ,p_val_order IN VARCHAR2
1070 ,p_parent_meaning IN VARCHAR2
1071 )return varchar2 is
1072 l_crit_typ_cd varchar2(30);
1073 l_oper_cd varchar2(30);
1074 l_val1 varchar2(200);
1075 p_business_group_id number := g_business_group_id ;
1076 p_legislation_code varchar2(240); --utf8
1077 p_val_1 varchar2(200);
1078 p_val_2 varchar2(200);
1079 l_val2 varchar2(200);
1080 l_crit_cmbn varchar2(2000) := '';
1081 -- value varchar2(80) := ''; UTF8IK
1082 value varchar2(600):= '';
1083
1084 cursor c1 is select lookup_code
1085 from hr_lookups
1086 where lookup_type = 'US_STATE'
1087 and meaning = p_meaning
1088 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1089 and nvl(end_date_active, trunc(sysdate))
1090 ;
1091
1092
1093 cursor c2 is select benfts_grp_id
1094 from ben_benfts_grp
1095 where nvl(business_group_id,nvl(p_business_group_id,-1))
1096 = nvl(p_business_group_id,-1)
1097 and name = p_meaning;
1098
1099 cursor c3 is SELECT assignment_status_type_id
1100 from PER_ASSIGNMENT_STATUS_TYPES
1101 WHERE
1102 -- nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
1103 -- = nvl(p_legislation_code,'~~nvl~~') and
1104 nvl(business_group_id,nvl(p_business_group_id,-1))
1105 = nvl(p_business_group_id,-1)
1106 and active_flag ='Y'
1107 and user_status = p_meaning;
1108
1109 cursor c4 is SELECT organization_id from PER_ORGANIZATION_UNITS
1110 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
1111 = nvl(p_business_group_id,-1)
1112 and trunc(sysdate)
1113 between nvl(date_from,trunc(sysdate))
1114 and nvl(date_to,trunc(sysdate))
1115 and internal_external_flag = 'INT'
1116 and name = p_meaning;
1117
1118 cursor c5 is select location_id from hr_locations
1119 where trunc(sysdate) <=
1120 nvl(inactive_date,to_date('31124712','DDMMYYYY'))
1121 and location_code = p_meaning ;
1122
1123 cursor c6 is SELECT tax_unit_id from hr_tax_units_v
1124 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
1125 = nvl(p_business_group_id,-1)
1126 and trunc(sysdate)
1127 between nvl(date_from,trunc(sysdate))
1128 and nvl(date_to,trunc(sysdate))
1129 and name = p_meaning ;
1130
1131 cursor c7 is SELECT person_id
1132 from per_all_people_f
1133 WHERE nvl(business_group_id,nvl(p_business_group_id,-1))
1134 = nvl(p_business_group_id,-1)
1135 and trunc(sysdate)
1136 between nvl(effective_start_date,trunc(sysdate))
1137 and nvl(effective_end_date,trunc(sysdate))
1138 and full_name||' '||national_identifier||' '||employee_number = p_meaning
1139 and full_name like SUBSTR(p_meaning,1, INSTR(p_meaning,' ')-1)||'%'; -- 4300295. Perf fix.
1140
1141 cursor c8 is select formula_id --formula_name
1142 from ff_formulas_f
1143 where --nvl(legislation_code,nvl(p_legislation_code,'~~nvl~~'))
1144 -- = nvl(p_legislation_code,'~~nvl~~')
1145 -- and nvl(business_group_id,nvl(p_business_group_id,-1))
1146 -- = nvl(p_business_group_id,-1) and
1147 trunc(sysdate) between effective_start_date
1148 and effective_end_date
1149 and formula_name = p_meaning;
1150 cursor c9 is select ler_id
1151 from ben_ler_f
1152 where nvl(business_group_id,nvl(p_business_group_id,-1))
1153 = nvl(p_business_group_id,-1)
1154 and trunc(sysdate) between effective_start_date
1155 and effective_end_date
1156 and name = p_meaning;
1157
1158 cursor c10 is select person_type_id
1159 from per_person_types
1160 where nvl(business_group_id,nvl(p_business_group_id,-1))
1161 = nvl(p_business_group_id,-1)
1162 and active_flag = 'Y'
1163 and user_person_type = p_meaning;
1164
1165 cursor ca is SELECT cm_typ_id from ben_cm_typ_f
1166 WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
1167 = nvl(p_business_group_id,-1)
1168 and name = p_meaning ;
1169
1170
1171 cursor cb is SELECT payroll_id from pay_all_payrolls_f
1172 WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
1173 = nvl(p_business_group_id,-1)
1174 and trunc(sysdate)
1175 between nvl(effective_start_date,trunc(sysdate))
1176 and nvl(effective_end_date,trunc(sysdate))
1177 and payroll_name = p_meaning;
1178
1179
1180 cursor cc is SELECT element_type_id from pay_element_types_f
1181 WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
1182 = nvl(p_business_group_id,-1)
1183 and element_name = p_meaning;
1184
1185
1186 cursor cd is SELECT input_value_id from pay_input_values_f
1187 WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
1188 = nvl(p_business_group_id,-1)
1189 and name = p_meaning
1190 and element_type_id = (SELECT element_type_id from pay_element_types_f
1191 WHERE nvl(business_group_id ,nvl(p_business_group_id,-1))
1192 = nvl(p_business_group_id,-1)
1193 and element_name = p_parent_meaning);
1194
1195
1196 cursor ce is select pl_id
1197 from ben_pl_f
1198 where business_group_id = p_business_group_id
1199 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
1200 and nvl(effective_end_date,trunc(sysdate))
1201 and name = p_meaning ;
1202 /*
1203 cursor cf is select name
1204 from ben_rptg_grp_v
1205 where business_group_id = p_business_group_id
1206 and rptg_grp_id = p_val_1;
1207 */
1208
1209 cursor cf is select lookup_code
1210 from hr_lookups
1211 where lookup_type = 'BEN_PER_IN_LER_STAT'
1212 and meaning = p_meaning;
1213
1214 cursor cg is select lookup_code
1215 from hr_lookups
1216 where lookup_type = 'BEN_EXT_SUSPEND'
1217 and meaning = p_meaning;
1218
1219 cursor ch is select name
1220 from ben_ler_v
1221 where business_group_id = p_business_group_id
1222 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
1223 and nvl(effective_end_date,trunc(sysdate))
1224 and ler_id = p_val_1;
1225
1226
1227 cursor ci is select pgm_id
1228 from ben_pgm_f
1229 where business_group_id = p_business_group_id
1230 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
1231 and nvl(effective_end_date,trunc(sysdate))
1232 and name = p_meaning ;
1233
1234
1235 cursor cj is select pl_id
1236 from ben_pl_f
1237 where business_group_id = p_business_group_id
1238 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
1239 and nvl(effective_end_date,trunc(sysdate))
1240 and name = p_meaning ;
1241
1242 cursor ck is select rptg_grp_id
1243 from ben_rptg_grp_v
1244 where business_group_id = p_business_group_id
1245 and name = p_meaning ;
1246
1247 cursor cl is select lookup_code
1248 from hr_lookups
1249 where lookup_type = 'BEN_EXT_CRIT_MISC'
1250 and meaning = p_meaning;
1251
1252
1253 cursor cm is select pl_typ_id
1254 from ben_pl_typ_f
1255 where business_group_id = p_business_group_id
1256 and trunc(sysdate) between nvl(effective_start_date,trunc(sysdate))
1257 and nvl(effective_end_date,trunc(sysdate))
1258 and name = p_meaning;
1259
1260
1261 cursor cn is select yr_perd_id
1262 from ben_yr_perd
1263 where business_group_id = p_business_group_id
1264 and start_date||' - '||end_date = p_meaning;
1265
1266 cursor co is select lookup_code
1267 from hr_lookups
1268 where lookup_type = 'BEN_EXT_CRIT_MISC'
1269 and meaning = p_meaning;
1270
1271 cursor cp is select lookup_code
1272 from hr_lookups
1273 where lookup_type = 'BEN_EXT_SUSPEND'
1274 and meaning = p_meaning;
1275
1276 cursor cq is select lookup_code
1277 from hr_lookups
1278 where lookup_type = 'BEN_ENRT_RSLT_MTHD'
1279 and meaning = p_meaning;
1280
1281 cursor cr is select lookup_code
1282 from hr_lookups
1283 where lookup_type = 'BEN_PRTT_ENRT_RSLT_STAT'
1284 and meaning = p_meaning;
1285
1286 cursor cs is select lookup_code
1287 from hr_lookups
1288 where lookup_type = 'BEN_EXT_DT'
1289 and meaning = p_meaning;
1290
1291 cursor ct is select actn_typ_id
1292 from ben_actn_typ
1293 where name = p_meaning ;
1294
1295
1296
1297 cursor cu is select lookup_code
1298 from hr_lookups
1299 where lookup_type = 'BEN_EXT_DT'
1300 and meaning = p_meaning
1301 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1302 and nvl(end_date_active, trunc(sysdate))
1303 ;
1304
1305
1306
1307 cursor cv is select lookup_code
1308 from hr_lookups
1309 where lookup_type = 'BEN_EXT_DT'
1310 and meaning = p_meaning
1311 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1312 and nvl(end_date_active, trunc(sysdate))
1313 ;
1314
1315 cursor cw is select lookup_code
1316 from hr_lookups
1317 where lookup_type = 'BEN_EXT_CHG_EVT'
1318 and meaning = p_meaning
1319 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1320 and nvl(end_date_active, trunc(sysdate))
1321 ;
1322 cursor cep is select event_group_id
1323 from pay_event_groups
1324 where event_group_name = p_meaning
1325 and nvl(business_group_id,nvl(p_business_group_id,-1))
1326 = nvl(p_business_group_id,-1)
1327 ;
1328
1329 cursor cx is select user_id
1330 from fnd_user
1331 where user_name = p_meaning
1332 and trunc(sysdate) between nvl(start_date, trunc(sysdate))
1333 and nvl(end_date, trunc(sysdate));
1334
1335 cursor cy is select lookup_code
1336 from hr_lookups
1337 where lookup_type = 'BEN_EXT_DT'
1338 and meaning = p_meaning
1339 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1340 and nvl(end_date_active, trunc(sysdate))
1341 ;
1342
1343 cursor cz is select lookup_code
1344 from hr_lookups
1345 where lookup_type = 'BEN_EXT_DT'
1346 and meaning = p_meaning
1347 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1348 and nvl(end_date_active, trunc(sysdate))
1349 ;
1350
1351 cursor caa is select lookup_code
1352 from hr_lookups
1353 where lookup_type = 'BEN_EXT_DT'
1354 and meaning = p_meaning
1355 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1356 and nvl(end_date_active, trunc(sysdate))
1357 ;
1358
1359 cursor cab is select lookup_code
1360 from hr_lookups
1361 where lookup_type = 'BEN_EXT_DT'
1362 and meaning = p_meaning
1363 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1364 and nvl(end_date_active, trunc(sysdate))
1365 ;
1366
1367
1368 cursor cac is select lookup_code
1369 from hr_lookups
1370 where lookup_type = 'BEN_EXT_DT'
1371 and meaning = p_meaning
1372 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1373 and nvl(end_date_active, trunc(sysdate))
1374 ;
1375
1376 cursor cad is select lookup_code
1377 from hr_lookups
1378 where lookup_type = 'BEN_EXT_DT'
1379 and meaning = p_meaning
1380 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1381 and nvl(end_date_active, trunc(sysdate))
1382 ;
1383
1384 cursor cae is select lookup_code
1385 from hr_lookups
1386 where lookup_type = 'BEN_EXT_DT'
1387 and meaning = p_meaning
1388 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1389 and nvl(end_date_active, trunc(sysdate))
1390 ;
1391
1392 cursor caf is select lookup_code
1393 from hr_lookups
1394 where lookup_type = 'BEN_EXT_DT'
1395 and meaning = p_meaning
1396 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1397 and nvl(end_date_active, trunc(sysdate))
1398 ;
1399
1400 cursor cag is select lookup_code
1401 from hr_lookups
1402 where lookup_type = 'BEN_EXT_DT'
1403 and meaning = p_meaning
1404 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1405 and nvl(end_date_active, trunc(sysdate))
1406 ;
1407
1408
1409 cursor cah is select lookup_code
1410 from hr_lookups
1411 where lookup_type = 'BEN_EXT_DT'
1412 and meaning = p_meaning
1413 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1414 and nvl(end_date_active, trunc(sysdate))
1415 ;
1416
1417
1418 cursor c_val1_dt
1419 is
1420 select lookup_code
1421 from hr_lookups
1422 where lookup_type = 'BEN_EXT_DT'
1423 and meaning = p_meaning;
1424
1425 cursor c_val2_dt
1426 is
1427 select lookup_code
1428 from hr_lookups
1429 where lookup_type = 'BEN_EXT_DT'
1430 and meaning = p_meaning;
1431
1432 cursor c_val1_chg_evt
1433 is
1434 select lookup_code
1435 from hr_lookups
1436 where lookup_type = 'BEN_EXT_CHG_EVT'
1437 and meaning = p_meaning;
1438
1439 cursor c_val2_chg_evt
1440 is
1441 select lookup_code
1442 from hr_lookups
1443 where lookup_type = 'BEN_EXT_CHG_EVT'
1444 and meaning = p_meaning;
1445
1446 cursor c_oper
1447 is
1448 select lookup_code
1449 from hr_lookups
1450 where lookup_type = 'BEN_EXT_TTL_COND_OPER'
1451 and meaning = p_meaning; --p_oper_cd;
1452
1453
1454 CURSOR Cai IS
1455 select lookup_code
1456 from HR_LOOKUPS
1457 where LOOKUP_CODE = p_crit_typ_cd
1458 and lookup_type = 'BEN_EXT_CRIT_TYP'
1459 and substr(lookup_code, 1, 1) = 'C'
1460 and lookup_code not in ('CBU')
1461 and trunc(sysdate) between
1462 nvl(start_date_active, trunc(sysdate))
1463 and nvl(end_date_active, trunc(sysdate))
1464 ;
1465
1466 cursor c_pasu
1467 is
1468 select lookup_code
1469 from hr_lookups
1470 where lookup_type = 'BEN_EXT_ASMT_TO_USE'
1471 and meaning = p_meaning; -- code for the criteria Person Assignment To Use
1472
1473
1474 cursor c_berenm
1475 is
1476 select lookup_code
1477 from hr_lookups
1478 where lookup_type = 'BEN_ENRT_MTHD'
1479 and meaning = p_meaning; -- value for the criteria Enrollment method
1480
1481
1482 cursor c_enb is
1483 select ENRT_PERD_ID
1484 from ben_enrt_perd enp ,
1485 ben_pl_f pl,
1486 ben_popl_enrt_typ_cycl_f pet
1487 where to_char(enp.strt_dt, 'DD-MON-RRRR') || ' - ' || to_char( enp.end_dt, 'DD-MON-RRRR') = p_meaning
1488 and pl.name = p_parent_meaning
1489 and pl.pl_id = pet.pl_id
1490 and pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
1491 and trunc(sysdate) between nvl(pl.effective_start_date,trunc(sysdate))
1492 and nvl(pl.effective_end_date,trunc(sysdate))
1493 and enp.business_group_id = p_business_group_id
1494 ;
1495
1496
1497
1498 cursor c_lookup(p_type varchar2) is
1499 select lookup_code
1500 from hr_lookups
1501 where lookup_type = p_type
1502 and meaning = p_meaning
1503 ; --
1504
1505 cursor c_job is
1506 select job_id
1507 from per_jobs_vl job
1508 where name = p_meaning
1509 and business_group_id = p_business_group_id
1510 ;
1511
1512 cursor c_bg is
1513 select business_group_id
1514 from per_business_groups_perf
1515 where name = p_meaning
1516 ;
1517
1518 cursor c_pos is
1519 select position_id
1520 from HR_ALL_POSITIONS_F job
1521 where name = p_meaning
1522 and trunc(sysdate) between
1523 EFFECTIVE_START_DATE
1524 and EFFECTIVE_END_DATE
1525 and business_group_id = p_business_group_id
1526 ;
1527
1528
1529 cursor c_asgset is
1530 select ASSIGNMENT_SET_ID from
1531 hr_assignment_sets
1532 where ASSIGNMENT_SET_NAME = p_meaning
1533 and nvl(business_group_id,nvl(p_business_group_id,-1))
1534 = nvl(p_business_group_id,-1)
1535 ;
1536
1537
1538 BEGIN
1539 --
1540 if p_crit_typ_cd = 'PST' then
1541 open c1;
1542 fetch c1 into value;
1543 close c1;
1544
1545 elsif p_crit_typ_cd = 'PBG' then
1546 open c2;
1547 fetch c2 into value;
1548 close c2;
1549 elsif p_crit_typ_cd = 'PAS' then
1550 open c3;
1551 fetch c3 into value;
1552 close c3;
1553 elsif p_crit_typ_cd = 'POR' then
1554 open c4;
1555 fetch c4 into value;
1556 close c4;
1557 elsif p_crit_typ_cd = 'PLO' then
1558 open c5;
1559 fetch c5 into value;
1560 close c5;
1561 elsif p_crit_typ_cd = 'PLE' then
1562 open c6;
1563 fetch c6 into value;
1564 close c6;
1565 elsif p_crit_typ_cd = 'PID' then
1566 open c7;
1567 fetch c7 into value;
1568 close c7;
1569 elsif p_crit_typ_cd = 'PRL' then
1570 open c8;
1571 fetch c8 into value;
1572 close c8;
1573 elsif p_crit_typ_cd = 'PLV' then
1574 open c9;
1575 fetch c9 into value;
1576 close c9;
1577 elsif p_crit_typ_cd = 'PPT' then
1578 open c10;
1579 fetch c10 into value;
1580 close c10;
1581 elsif p_crit_typ_cd = 'MTP' then
1582 open ca;
1583 fetch ca into value;
1584 close ca;
1585 elsif p_crit_typ_cd = 'RRL' then
1586 open cb;
1587 fetch cb into value;
1588 close cb;
1589 elsif p_crit_typ_cd = 'REE' and p_val_order = 'VAL_2' then
1590 open cc;
1591 fetch cc into value;
1592 close cc;
1593 elsif p_crit_typ_cd = 'REE' and p_val_order = 'VAL_1' then
1594 open cd;
1595 fetch cd into value;
1596 close cd;
1597 elsif p_crit_typ_cd in ('BECLEN', 'BERLEN') then
1598 open c9;
1599 fetch c9 into value;
1600 close c9;
1601
1602 elsif p_crit_typ_cd in ('BECLES', 'BERLES') then
1603 open cf;
1604 fetch cf into value;
1605 close cf;
1606
1607 elsif p_crit_typ_cd in ('BECPLN', 'BPL') then
1608 open ce;
1609 fetch ce into value;
1610 close ce;
1611
1612 elsif p_crit_typ_cd in ('BECRPG', 'BRG') then
1613 open ck;
1614 fetch ck into value;
1615 close ck;
1616
1617 elsif p_crit_typ_cd in ('BECPGN', 'BERPGN') then
1618 open ci;
1619 fetch ci into value;
1620 close ci;
1621
1622 elsif p_crit_typ_cd in ('BECPTN', 'BERPTN') then
1623 open cm;
1624 fetch cm into value;
1625 close cm;
1626
1627 elsif p_crit_typ_cd = 'BECYRP' then
1628 open cn;
1629 fetch cn into value;
1630 close cn;
1631
1632 elsif p_crit_typ_cd in ('BECMIS', 'BERMIS','BACMIS') then
1633 open cl;
1634 fetch cl into value;
1635 close cl;
1636
1637
1638 elsif p_crit_typ_cd = 'BSE' then
1639 open cg;
1640 fetch cg into value;
1641 close cg;
1642
1643 elsif p_crit_typ_cd = 'BERENM' then
1644 open c_berenm;
1645 fetch c_berenm into value;
1646 close c_berenm;
1647
1648 elsif p_crit_typ_cd = 'BERSTA' then
1649 open cr;
1650 fetch cr into value;
1651 close cr;
1652 /*
1653 elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD', 'BERCDP', 'BERLUD',
1654 'BERLND', 'BERLOD') then
1655 open cp;
1656 fetch cp into value;
1657 if cp%notfound then
1658 value := p_val_1;
1659 end if;
1660 close cp;
1661 */
1662
1663 elsif p_crit_typ_cd = 'BACN' then
1664 open ct;
1665 fetch ct into value;
1666 close ct;
1667 elsif p_crit_typ_cd in ('BDTOR') then
1668 open cu;
1669 fetch cu into value;
1670 if cu%notfound then
1671 value := p_val_1;
1672 end if;
1673 close cu;
1674 elsif p_crit_typ_cd in ('PASOR') then
1675 open cv;
1676 fetch cv into value;
1677 if cv%notfound then
1678 value := p_val_1;
1679 end if;
1680 close cv;
1681 elsif p_crit_typ_cd = 'CCE' then
1682 open cw;
1683 fetch cw into value;
1684 close cw;
1685 -- elsif p_crit_typ_cd = 'CEP' and p_val_order = 'VAL_2' then
1686 elsif p_crit_typ_cd = 'CPE' and p_val_order = 'VAL_2' then -- anshghos
1687 value := P_meaning ;
1688 -- elsif p_crit_typ_cd = 'CEP' and p_val_order = 'VAL_1' then
1689 elsif p_crit_typ_cd = 'CPE' and p_val_order = 'VAL_1' then -- anshghos
1690 open CEP;
1691 fetch CEP into value;
1692 close CEP;
1693
1694 elsif p_crit_typ_cd = 'CBU' then
1695 open cx;
1696 fetch cx into value;
1697 close cx;
1698 elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD','BERCDP','BERLUD',
1699 'BERLND', 'BERLOD') then
1700 open cy;
1701 fetch cy into value;
1702 if cy%notfound then
1703 value := p_val_1;
1704 end if;
1705 close cy;
1706 /*
1707 elsif p_crit_typ_cd in ('BECESD', 'BECLUD','BECLND', 'BECLED', 'BERCSD', 'BERCDP', 'BERLUD',
1708 'BERLND', 'BERLOD') then
1709 open cz;
1710 fetch cz into value;
1711 if cz%notfound then
1712 value := p_val_2;
1713 end if;
1714 close cz;
1715 */
1716 elsif p_crit_typ_cd in ('MTBSDT', 'MPCLUD', 'MPCPLUD', 'MSDT') then
1717 open caa;
1718 fetch caa into value;
1719 if caa%notfound then
1720 value := p_val_1;
1721 end if;
1722 close caa;
1723 elsif p_crit_typ_cd in ('MTBSDT', 'MPCLUD', 'MPCPLUD', 'MSDT') then
1724 open cab;
1725 fetch cab into value;
1726 if cab%notfound then
1727 value := p_val_2;
1728 end if;
1729 close cab;
1730 elsif p_crit_typ_cd in ('CAD', 'CED') then
1731 open cac;
1732 fetch cac into value;
1733 if cac%notfound then
1734 value := p_val_1;
1735 end if;
1736 close cac;
1737 elsif p_crit_typ_cd in ('CAD', 'CED') then
1738 open cad;
1739 fetch cad into value;
1740 if cad%notfound then
1741 value := p_val_2;
1742 end if;
1743 close cad;
1744 elsif p_crit_typ_cd is not null and p_crit_typ_cd = 'RPPEDT' then
1745 open cag;
1746 fetch cag into value;
1747 if cag%notfound then
1748 value := p_val_1;
1749 end if;
1750 close cag;
1751 elsif p_crit_typ_cd is not null and p_crit_typ_cd = 'RPPEDT' then
1752 open cah;
1753 fetch cah into value;
1754 if cah%notfound then
1755 value := p_val_2;
1756 end if;
1757 close cah;
1758 /*open c_oper;
1759 fetch c_oper into value; --p_oper_cd_nonbase;
1760 close c_oper;
1761 */
1762
1763
1764 elsif p_crit_typ_cd in ( 'CAD','CED', 'EPMNYR','EPLDT' ) then
1765 open c_val1_dt;
1766 fetch c_val1_dt into value;
1767 close c_val1_dt;
1768 /*
1769 open c_val2_dt;
1770 fetch c_val2_dt into value;
1771 close c_val2_dt;
1772 */
1773
1774 elsif p_crit_typ_cd = 'CCE' then
1775 open c_val1_chg_evt;
1776 fetch c_val1_chg_evt into value;
1777 close c_val1_chg_evt;
1778 open c_val2_chg_evt;
1779 fetch c_val2_chg_evt into value;
1780 close c_val2_chg_evt;
1781 elsif p_crit_typ_cd = 'PASU' then
1782 open c_pasu;
1783 fetch c_pasu into value;
1784 close c_pasu;
1785
1786
1787 elsif p_crit_typ_cd = 'HRL' then
1788 open c8;
1789 fetch c8 into value;
1790 close c8;
1791
1792 elsif p_crit_typ_cd = 'RFFRL' then
1793 open c8;
1794 fetch c8 into value;
1795 close c8;
1796
1797 elsif p_crit_typ_cd = 'WPLPR' and p_val_order = 'VAL_2'then
1798 -- plan
1799 open cj;
1800 fetch cj into value;
1801 close cj;
1802 elsif p_crit_typ_cd = 'WPLPR' and p_val_order = 'VAL_1'then
1803 -- plan enrollment period
1804 open c_enb;
1805 fetch c_enb into value;
1806 close c_enb;
1807
1808 elsif p_crit_typ_cd = 'PDL' then
1809 Open c_lookup('BEN_EXT_PER_DATA_LINK') ;
1810 fetch c_lookup into value;
1811 close c_lookup;
1812
1813 elsif p_crit_typ_cd = 'HJOB' then
1814 open c_job;
1815 fetch c_job into value;
1816 close c_job;
1817 elsif p_crit_typ_cd = 'HORG' then
1818 open c4;
1819 fetch c4 into value;
1820 close c4;
1821 elsif p_crit_typ_cd = 'HPOS' then
1822 open c_pos;
1823 fetch c_pos into value;
1824 close c_pos;
1825 elsif p_crit_typ_cd = 'HPY' then
1826 open cb;
1827 fetch cb into value;
1828 close cb;
1829 elsif p_crit_typ_cd = 'HBG' then
1830 open c_bg;
1831 fetch c_bg into value;
1832 close c_bg;
1833 elsif p_crit_typ_cd = 'PBGR' then
1834 open c_bg;
1835 fetch c_bg into value;
1836 close c_bg;
1837 elsif p_crit_typ_cd = 'PASGSET' then
1838
1839 open c_asgset ;
1840 fetch c_asgset into value ;
1841 close c_asgset ;
1842
1843 else
1844 value := p_meaning ;
1845 end if;
1846 /* OPEN Cai;
1847 FETCH Cai into value;
1848 CLOSE Cai;
1849 */
1850 return value;
1851 end;
1852
1853 procedure get_who_values(p_owner IN VARCHAR2
1854 ,p_last_update_vc in VARCHAR2
1855 ,p_last_update_date OUT NOCOPY DATE
1856 ,p_last_updated_by OUT NOCOPY VARCHAR2
1857 ,p_legislation_code IN OUT NOCOPY VARCHAR2
1858 ,p_business_group in VARCHAR2
1859 ,p_business_group_id out NOCOPY NUMBER
1860 ) is
1861 begin
1862 p_last_update_date := TO_DATE(p_last_update_vc, 'YYYY/MM/DD HH24:MI:SS');
1863 IF p_owner = 'SEED'
1864 THEN
1865 p_last_updated_by := 1;
1866 ELSE
1867 p_last_updated_by := 0;
1868 END IF;
1869 if p_legislation_code = 'GLOBAL' then
1870 p_legislation_code := '';
1871 end if;
1872 -- the custom extract can not uploaded as seed ( bg id null )
1873 -- seeded extract can not be uploaded to a business group
1874
1875 if p_owner = 'CUSTOM' then
1876 if p_business_group is null then
1877 fnd_message.set_name('BEN','BEN_93272_PDC_SRC_BUSINESS_GRP');
1878 fnd_message.raise_error;
1879 end if ;
1880 else
1881 if p_business_group is not null then
1882 fnd_message.set_name('BEN','BEN_93209_PDC_INVALID_BG_ER');
1883 fnd_message.raise_error;
1884 end if ;
1885 /*
1886 -- there is a possibility of sharing legislative
1887 -- elements
1888 if g_business_group_id is not null then
1889 fnd_message.set_name('BEN','BEN_93200_PDC_INVALID_BG_ER');
1890 fnd_message.raise_error;
1891 end if ;
1892 */
1893 end if ;
1894 p_business_group_id := g_business_group_id ;
1895
1896 end get_who_values;
1897
1898
1899 procedure load_extract_group(p_file_name IN VARCHAR2
1900 ,p_ext_group_record in VARCHAR2
1901 ,p_ext_group_elmt1 in VARCHAR2
1902 ,p_ext_group_elmt2 in VARCHAR2
1903 ,p_owner IN VARCHAR2
1904 ,p_last_update_date IN VARCHAR2
1905 ,p_legislation_code IN VARCHAR2
1906 ,p_business_group in VARCHAR2
1907 ) is
1908 --
1909 l_ext_file_id NUMBER;
1910 l_ext_rcd_in_file_id NUMBER;
1911 l_ext_data_elmt_in_rcd_id1 NUMBER;
1912 l_ext_data_elmt_in_rcd_id2 NUMBER;
1913 l_ext_rcd_id NUMBER;
1914 l_object_version_number NUMBER;
1915 l_last_update_date DATE;
1916 l_last_updated_by NUMBER;
1917 l_sessionid NUMBER;
1918 l_new_business_group_id number ;
1919 l_ovn number ;
1920 l_xml_tag_name ben_ext_file.xml_tag_name%type ;
1921 l_legislation_code VARCHAR2(240);
1922
1923 --
1924 BEGIN
1925 l_legislation_code := p_legislation_code;
1926
1927 if p_ext_group_record is not null and p_ext_group_elmt1 is not null then
1928 --
1929 get_who_values(p_owner => p_owner
1930 ,p_last_update_vc => p_last_update_date
1931 ,p_last_update_date => l_last_update_date
1932 ,p_last_updated_by => l_last_updated_by
1933 ,p_legislation_code => l_legislation_code
1934 ,p_business_group => p_business_group
1935 ,p_business_group_id => l_new_business_group_id );
1936 BEGIN
1937 SELECT ext_file_id ,
1938 OBJECT_VERSION_NUMBER ,
1939 XML_TAG_NAME
1940 INTO l_ext_file_id ,
1941 l_ovn,
1942 l_xml_tag_name
1943 FROM ben_ext_file
1944 WHERE name = p_file_name
1945 AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
1946 AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
1947
1948
1949 select erf.ext_rcd_in_file_id,
1950 rcd.ext_rcd_id
1951 into l_ext_rcd_in_file_id ,
1952 l_ext_rcd_id
1953 from ben_Ext_rcd_in_file erf,
1954 ben_Ext_rcd rcd
1955 where rcd.name = p_ext_group_record
1956 and rcd.ext_rcd_id = erf.ext_rcd_id
1957 and erf.ext_file_id = l_ext_file_id
1958 ;
1959
1960
1961 select der.Ext_data_elmt_in_rcd_id
1962 into l_ext_data_elmt_in_rcd_id1
1963 from ben_Ext_data_elmt elmt ,
1964 ben_Ext_data_elmt_in_rcd der
1965 where der.ext_rcd_id = l_Ext_rcd_id
1966 and elmt.ext_data_elmt_id = der.ext_data_elmt_id
1967 and elmt.name = p_ext_group_elmt1
1968 ;
1969
1970 if p_ext_group_elmt2 is not null then
1971
1972 select der.Ext_data_elmt_in_rcd_id
1973 into l_ext_data_elmt_in_rcd_id2
1974 from ben_Ext_data_elmt elmt ,
1975 ben_Ext_data_elmt_in_rcd der
1976 where der.ext_rcd_id = l_Ext_rcd_id
1977 and elmt.ext_data_elmt_id = der.ext_data_elmt_id
1978 and elmt.name = p_ext_group_elmt2
1979 ;
1980
1981 end if ;
1982
1983
1984 ben_xfi_upd.upd
1985 (
1986 p_ext_file_id => l_ext_file_id
1987 ,p_name => p_file_name
1988 ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
1989 ,p_ext_data_elmt_in_rcd_id1 => l_ext_data_elmt_in_rcd_id1
1990 ,p_ext_data_elmt_in_rcd_id2 => l_ext_data_elmt_in_rcd_id2
1991 ,p_business_group_id => l_new_business_group_id
1992 ,p_legislation_code => l_legislation_code
1993 ,p_object_version_number => l_ovn
1994 );
1995
1996
1997
1998
1999 EXCEPTION
2000 WHEN NO_DATA_FOUND THEN
2001 null ;
2002 WHEN OTHERS THEN
2003 null ;
2004 END;
2005 end if ;
2006 END load_extract_group;
2007
2008
2009
2010
2011
2012 procedure load_extract(p_file_name IN VARCHAR2
2013 ,p_owner IN VARCHAR2
2014 ,p_last_update_date IN VARCHAR2
2015 ,p_legislation_code IN VARCHAR2
2016 ,p_business_group in VARCHAR2
2017 ,p_xml_tag_name in VARCHAR2
2018 ,p_ext_group_record in VARCHAR2
2019 ,p_ext_group_elmt1 in VARCHAR2
2020 ,p_ext_group_elmt2 in VARCHAR2
2021 ) is
2022 --
2023 l_ext_file_id NUMBER;
2024 l_object_version_number NUMBER;
2025 l_legislation_code VARCHAR2(240);
2026 l_temp VARCHAR2(1);
2027 l_last_update_date DATE;
2028 l_last_updated_by NUMBER;
2029 l_sessionid NUMBER;
2030 l_new_business_group_id number ;
2031 l_ovn number ;
2032 l_xml_tag_name ben_ext_file.xml_tag_name%type ;
2033
2034 l_proc varchar2(100) := 'BEN_EXT_SEED.load_extract' ;
2035
2036 --
2037 BEGIN
2038 hr_utility.set_location(' Entering ' || l_proc, 10);
2039 l_legislation_code := p_legislation_code;
2040
2041 g_group_record := p_ext_group_record ;
2042 g_group_elmt1 := p_ext_group_elmt1 ;
2043 g_group_elmt2 := p_ext_group_elmt2 ;
2044
2045 SELECT USERENV('SESSIONID')
2046 INTO l_sessionid
2047 FROM DUAL;
2048 --
2049
2050
2051 -- adding these conditions after checking with the code under
2052 If ( p_legislation_code = 'GLOBAL' or p_legislation_code is null)
2053 and p_business_group is null then
2054 hr_startup_data_api_support.enable_startup_mode(p_mode =>'GENERIC'
2055 ,p_startup_session_id =>l_sessionid);
2056 elsif p_business_group is null then
2057 hr_startup_data_api_support.enable_startup_mode(p_mode =>'STARTUP'
2058 ,p_startup_session_id =>l_sessionid);
2059 End If;
2060
2061 hr_startup_data_api_support.create_owner_definition('BEN',FALSE);
2062 --
2063 get_who_values(p_owner => p_owner
2064 ,p_last_update_vc => p_last_update_date
2065 ,p_last_update_date => l_last_update_date
2066 ,p_last_updated_by => l_last_updated_by
2067 ,p_legislation_code => l_legislation_code
2068 ,p_business_group => p_business_group
2069 ,p_business_group_id => l_new_business_group_id );
2070 BEGIN
2071 SELECT ext_file_id ,
2072 OBJECT_VERSION_NUMBER ,
2073 XML_TAG_NAME
2074 INTO l_ext_file_id ,
2075 l_ovn,
2076 l_xml_tag_name
2077 FROM ben_ext_file
2078 WHERE name = p_file_name
2079 AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
2080 AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
2081
2082 --for the business group extract dont allow to upload if the extract layout already exist
2083 if nvl(fnd_global.conc_request_id,-1) <> -1 and
2084 ( l_new_business_group_id is not null and g_override <> 'Y' ) then
2085 fnd_message.set_name('BEN','BEN_93741_EXT_FILE_EXISTS');
2086 fnd_file.put_line(fnd_file.log, fnd_message.get);
2087 fnd_message.raise_error;
2088 end if ;
2089 --- data exist and global and xml_tag name is not matching
2090 --- update the extract file layout
2091 if nvl(l_xml_tag_name, '-1') <> nvl( p_xml_tag_name,'-1') then
2092 ben_xfi_upd.upd
2093 (
2094 p_ext_file_id => l_ext_file_id
2095 ,p_name => p_file_name
2096 ,p_xml_tag_name => p_xml_tag_name
2097 ,p_business_group_id => l_new_business_group_id
2098 ,p_legislation_code => l_legislation_code
2099 ,p_object_version_number => l_ovn
2100 );
2101 end if ;
2102
2103 EXCEPTION
2104 WHEN NO_DATA_FOUND THEN
2105 ben_xfi_ins.ins(p_ext_file_id =>l_ext_file_id
2106 ,p_name => p_file_name
2107 ,p_business_group_id => l_new_business_group_id
2108 ,p_legislation_code =>l_legislation_code
2109 ,p_last_update_date => l_last_update_date
2110 ,p_creation_date => l_last_update_date
2111 ,p_last_update_login => 0
2112 ,p_created_by => l_last_updated_by
2113 ,p_last_updated_by => l_last_updated_by
2114 ,p_object_version_number => l_object_version_number
2115 ,p_xml_tag_name => p_xml_tag_name
2116 );
2117 WHEN OTHERS THEN
2118 RAISE;
2119 END;
2120 hr_utility.set_location(' Leaving ' || l_proc, 10);
2121 END load_extract;
2122 --
2123 PROCEDURE load_record(p_record_name IN VARCHAR2
2124 ,p_owner IN VARCHAR2
2125 ,p_last_update_date IN VARCHAR2
2126 ,p_rcd_type_cd IN VARCHAR2
2127 ,p_low_lvl_cd IN VARCHAR2
2128 ,p_legislation_code IN VARCHAR2
2129 ,p_business_group IN VARCHAR2
2130 ,p_xml_tag_name in VARCHAR2
2131 ) IS
2132 --
2133 l_ext_rcd_id number;
2134 l_object_version_number number;
2135 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
2136 l_temp VARCHAR2(1);
2137 l_last_update_date DATE;
2138 l_last_updated_by NUMBER;
2139 l_new_business_group_id number ;
2140 l_ovn number ;
2141 l_xml_tag_name ben_ext_rcd.xml_tag_name%type ;
2142 l_rcd_type_cd ben_ext_rcd.RCD_TYPE_CD%type ;
2143 l_low_lvl_cd ben_ext_rcd.LOW_LVL_CD%type ;
2144 l_proc varchar2(100) := 'BEN_EXT_SEED.load_record' ;
2145 BEGIN
2146 hr_utility.set_location(' Entering ' || l_proc, 10);
2147 get_who_values(p_owner => p_owner
2148 ,p_last_update_vc => p_last_update_date
2149 ,p_last_update_date => l_last_update_date
2150 ,p_last_updated_by => l_last_updated_by
2151 ,p_legislation_code => l_legislation_code
2152 ,p_business_group => p_business_group
2153 ,p_business_group_id => l_new_business_group_id );
2154
2155 BEGIN
2156 SELECT EXT_RCD_ID,
2157 OBJECT_VERSION_NUMBER,
2158 XML_TAG_NAME,
2159 RCD_TYPE_CD ,
2160 LOW_LVL_CD
2161 INTO l_ext_rcd_id,
2162 l_ovn,
2163 l_xml_tag_name,
2164 l_RCD_TYPE_CD,
2165 l_LOW_LVL_CD
2166 FROM ben_ext_rcd
2167 WHERE name = p_record_name
2168 AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
2169 AND nvl(legislation_code,'~NULL~') = nvl(l_legislation_code,'~NULL~');
2170
2171 -- when the extract is global and once of therecord value changed then update
2172 if ( l_new_business_group_id is null or g_override = 'Y') and
2173 ( nvl(l_xml_tag_name,'-1') <> nvl(p_xml_tag_name,'-1')
2174 OR l_RCD_TYPE_CD <> p_RCD_TYPE_CD
2175 OR nvl(l_LOW_LVL_CD,'-1') <> nvl(p_LOW_LVL_CD,'-1')
2176 ) then
2177
2178 ben_xrc_upd.upd(p_effective_date => l_last_update_date
2179 ,p_ext_rcd_id => l_ext_rcd_id
2180 ,p_name => p_record_name
2181 ,p_rcd_type_cd => p_rcd_type_cd
2182 ,p_low_lvl_cd => p_low_lvl_cd
2183 ,p_business_group_id => l_new_business_group_id
2184 ,p_legislation_code => l_legislation_code
2185 ,p_object_version_number => l_ovn
2186 ,p_xml_tag_name => p_xml_tag_name );
2187
2188 end if ;
2189
2190 EXCEPTION
2191 WHEN NO_DATA_FOUND THEN
2192 ben_xrc_ins.ins(p_effective_date => l_last_update_date
2193 ,p_ext_rcd_id => l_ext_rcd_id
2194 ,p_name => p_record_name
2195 ,p_rcd_type_cd => p_rcd_type_cd
2196 ,p_low_lvl_cd => p_low_lvl_cd
2197 ,p_business_group_id => l_new_business_group_id
2198 ,p_legislation_code => l_legislation_code
2199 ,p_last_update_date => l_last_update_date
2200 ,p_creation_date => l_last_update_date
2201 ,p_last_update_login => 0
2202 ,p_created_by => l_last_updated_by
2203 ,p_last_updated_by => l_last_updated_by
2204 ,p_object_version_number => l_object_version_number
2205 ,p_xml_tag_name => p_xml_tag_name );
2206 WHEN OTHERS THEN
2207 RAISE;
2208 END;
2209 hr_utility.set_location(' Leaving ' || l_proc, 10);
2210 END load_record;
2211
2212
2213
2214 PROCEDURE load_record_in_file(p_file_name IN VARCHAR2
2215 ,p_parent_record_name IN VARCHAR2
2216 ,p_owner IN VARCHAR2
2217 ,p_last_update_date IN VARCHAR2
2218 ,p_rqd_flag IN VARCHAR2 default 'N'
2219 ,p_hide_flag IN VARCHAR2 default 'N'
2220 ,p_CHG_RCD_UPD_FLAG IN VARCHAR2 default 'N'
2221 ,p_seq_num IN VARCHAR2
2222 ,p_sprs_cd IN VARCHAR2
2223 ,p_any_or_all_cd IN VARCHAR2 default 'N'
2224 ,p_sort1_element IN VARCHAR2 DEFAULT NULL
2225 ,p_sort2_element IN VARCHAR2 DEFAULT NULL
2226 ,p_sort3_element IN VARCHAR2 DEFAULT NULL
2227 ,p_sort4_element IN VARCHAR2 DEFAULT NULL
2228 ,p_legislation_code IN VARCHAR2
2229 ,p_business_group in VARCHAR2
2230 ) IS
2231 --
2232 l_ext_file_id NUMBER;
2233 l_ext_rcd_id NUMBER;
2234 l_rcd_in_file_id NUMBER;
2235 l_object_version_number NUMBER;
2236 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
2237 l_temp VARCHAR2(1);
2238 l_last_update_date DATE;
2239 l_last_updated_by NUMBER;
2240 l_seq_dup_id NUMBER;
2241 l_new_business_group_id number ;
2242 l_sort1_elm_in_rcd_id NUMBER;
2243 l_sort2_elm_in_rcd_id NUMBER;
2244 l_sort3_elm_in_rcd_id NUMBER;
2245 l_sort4_elm_in_rcd_id NUMBER;
2246 --rpinjala
2247 l_ext_rcd_in_file_id NUMBER;
2248 --rpinjala
2249
2250 cursor c_sort ( c_sort_element varchar2,
2251 c_ext_rcd_id number ,
2252 c_legislation_code varchar2 ,
2253 c_new_business_group_id number ) is
2254 SELECT EXT_DATA_ELMT_IN_RCD_ID
2255 from ben_ext_data_elmt_in_rcd eir , ben_ext_data_elmt elmt
2256 where eir.ext_rcd_id = c_ext_rcd_id
2257 and eir.ext_data_elmt_id = elmt.ext_data_elmt_id
2258 and elmt.name = c_sort_element
2259 and NVL(eir.legislation_code,'~NULL~') = NVL(c_legislation_code,'~NULL~')
2260 AND nvl( c_new_business_group_id, -1) = nvl(eir.business_group_id , -1)
2261 ;
2262
2263
2264
2265
2266 l_proc varchar2(100) := 'BEN_EXT_SEED.load_record_in_file' ;
2267 BEGIN
2268 hr_utility.set_location(' Entering ' || l_proc, 10);
2269
2270
2271 get_who_values(p_owner => p_owner
2272 ,p_last_update_vc => p_last_update_date
2273 ,p_last_update_date => l_last_update_date
2274 ,p_last_updated_by => l_last_updated_by
2275 ,p_legislation_code => l_legislation_code
2276 ,p_business_group => p_business_group
2277 ,p_business_group_id => l_new_business_group_id );
2278
2279
2280 BEGIN
2281 SELECT ext_file_id
2282 INTO l_ext_file_id
2283 FROM ben_ext_file
2284 WHERE name = p_file_name
2285 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
2286 AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1);
2287
2288 EXCEPTION
2289 WHEN NO_DATA_FOUND THEN
2290 RAISE;
2291 END;
2292
2293
2294 BEGIN
2295 SELECT ext_rcd_id
2296 INTO l_ext_rcd_id
2297 FROM ben_ext_rcd
2298 WHERE name = p_parent_record_name
2299 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
2300 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1);
2301
2302 EXCEPTION
2303 WHEN NO_DATA_FOUND THEN
2304 RAISE;
2305 END;
2306
2307
2308 --- sort1_data_elmt_in_rcd_id
2309
2310
2311 if l_ext_rcd_id is not null and p_sort1_element is not null then
2312
2313 l_sort1_elm_in_rcd_id := null ;
2314 open c_sort ( c_sort_element => p_sort1_element ,
2315 c_ext_rcd_id => l_ext_rcd_id ,
2316 c_legislation_code => l_legislation_code ,
2317 c_new_business_group_id => l_new_business_group_id ) ;
2318 fetch c_sort into l_sort1_elm_in_rcd_id ;
2319 close c_sort ;
2320
2321
2322 end if ;
2323
2324 --- sort2_data_elmt_in_rcd_id
2325 if l_ext_rcd_id is not null and p_sort2_element is not null then
2326 l_sort2_elm_in_rcd_id := null ;
2327
2328 open c_sort (c_sort_element => p_sort2_element ,
2329 c_ext_rcd_id => l_ext_rcd_id ,
2330 c_legislation_code => l_legislation_code ,
2331 c_new_business_group_id => l_new_business_group_id ) ;
2332 fetch c_sort into l_sort2_elm_in_rcd_id ;
2333 close c_sort ;
2334
2335
2336 end if ;
2337
2338 --- sort3_data_elmt_in_rcd_id
2339 if l_ext_rcd_id is not null and p_sort3_element is not null then
2340
2341 l_sort3_elm_in_rcd_id := null ;
2342
2343 open c_sort (c_sort_element => p_sort3_element ,
2344 c_ext_rcd_id => l_ext_rcd_id ,
2345 c_legislation_code => l_legislation_code ,
2346 c_new_business_group_id => l_new_business_group_id ) ;
2347 fetch c_sort into l_sort3_elm_in_rcd_id ;
2348 close c_sort ;
2349
2350
2351 end if ;
2352
2353 --- sort4_data_elmt_in_rcd_id
2354 if l_ext_rcd_id is not null and p_sort4_element is not null then
2355 l_sort4_elm_in_rcd_id := null ;
2356 open c_sort (c_sort_element => p_sort4_element ,
2357 c_ext_rcd_id => l_ext_rcd_id ,
2358 c_legislation_code => l_legislation_code ,
2359 c_new_business_group_id => l_new_business_group_id ) ;
2360 fetch c_sort into l_sort4_elm_in_rcd_id ;
2361 close c_sort ;
2362
2363 end if ;
2364
2365
2366 BEGIN
2367 --rpinjala
2368 SELECT ext_rcd_in_file_id, object_version_number
2369 INTO l_ext_rcd_in_file_id,l_object_version_number
2370 FROM ben_ext_rcd_in_file
2371 WHERE ext_file_id = l_ext_file_id
2372 AND ext_rcd_id = l_ext_rcd_id
2373 AND seq_num = p_seq_num
2374 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
2375 AND NVL( l_new_business_group_id, -1) = NVL(business_group_id , -1);
2376
2377 ben_xrf_upd.upd
2378 (p_effective_date => l_last_update_date
2379 ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
2380 ,p_seq_num => p_seq_num
2381 ,p_sprs_cd => p_sprs_cd
2382 ,p_ext_rcd_id => l_ext_rcd_id
2383 ,p_ext_file_id => l_ext_file_id
2384 ,p_business_group_id => l_new_business_group_id
2385 ,p_legislation_code => l_legislation_code
2386 ,p_last_update_date => l_last_update_date
2387 ,p_creation_date => l_last_update_date
2388 ,p_last_updated_by => l_last_updated_by
2389 ,p_last_update_login => 0
2390 ,p_created_by => l_last_updated_by
2391 ,p_object_version_number => l_object_version_number
2392 ,p_any_or_all_cd => p_any_or_all_cd
2393 ,p_hide_flag => p_hide_flag
2394 ,p_rqd_flag => p_rqd_flag
2395 ,p_CHG_RCD_UPD_FLAG => nvl(p_CHG_RCD_UPD_FLAG,'N')
2396 ,p_sort1_data_elmt_in_rcd_id => l_sort1_elm_in_rcd_id
2397 ,p_sort2_data_elmt_in_rcd_id => l_sort2_elm_in_rcd_id
2398 ,p_sort3_data_elmt_in_rcd_id => l_sort3_elm_in_rcd_id
2399 ,p_sort4_data_elmt_in_rcd_id => l_sort4_elm_in_rcd_id
2400 );
2401 --rpinjala
2402 EXCEPTION
2403 WHEN NO_DATA_FOUND THEN
2404
2405 /* If the Same Sequence Find Delete the Record for the Sequence */
2406 declare
2407 cursor c1 (c_ext_rcd_in_file_id number) is
2408 select 'x'
2409 from ben_Ext_where_clause
2410 where ext_rcd_in_file_id = c_ext_rcd_in_file_id ;
2411
2412 cursor c2 (c_ext_rcd_in_file_id number) is
2413 select 'x'
2414 from ben_ext_incl_chg
2415 where ext_rcd_in_file_id = c_ext_rcd_in_file_id ;
2416
2417 l_tmp varchar2(1) ;
2418 Begin
2419 select object_version_number,ext_rcd_in_file_id
2420 into l_object_version_number,l_rcd_in_file_id
2421 from ben_ext_rcd_in_file
2422 where ext_file_id = l_ext_file_id
2423 and ext_rcd_id <> nvl(l_ext_rcd_id,-1)
2424 and seq_num = p_seq_num
2425 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
2426 /* if the sequence found delete the old reco */
2427 -- this delete may error if it has child
2428 -- **** Before apply any delete keep in mind that every upload recrod_in_file is
2429 -- called twice , one for the record_in_file and one for the sort order
2430 -- this is done in such way to keep the backward copatibility ****
2431
2432 if ( l_new_business_group_id is null or g_override = 'Y' ) and
2433 l_sort1_elm_in_rcd_id is null and
2434 l_sort2_elm_in_rcd_id is null and
2435 l_sort4_elm_in_rcd_id is null then
2436
2437 -- make sure there us no wheere clause child
2438 open c1(l_rcd_in_file_id) ;
2439 fetch c1 into l_tmp ;
2440 if c1%notfound then
2441
2442 open c2(l_rcd_in_file_id) ;
2443 fetch c2 into l_tmp ;
2444 if c2%notfound then
2445
2446 ben_xrf_del.del(p_effective_date => l_last_update_date
2447 ,p_ext_rcd_in_file_id => l_rcd_in_file_id
2448 ,p_legislation_code => l_legislation_code
2449 ,p_object_version_number => l_object_version_number);
2450
2451 end if ;
2452 close c2 ;
2453 end if ;
2454 close c1 ;
2455 end if ;
2456 Exception
2457 WHEN NO_DATA_FOUND THEN
2458 null ;
2459 When Others then
2460 RAISE ;
2461 End ;
2462 /* Deleteion part is over for duplicate seq number */
2463
2464 ben_xrf_ins.ins(p_effective_date => l_last_update_date
2465 ,p_ext_rcd_in_file_id => l_rcd_in_file_id
2466 ,p_legislation_code => l_legislation_code
2467 ,p_ext_rcd_id => l_ext_rcd_id
2468 ,p_ext_file_id => l_ext_file_id
2469 ,p_business_group_id => l_new_business_group_id
2470 ,p_seq_num => p_seq_num
2471 ,p_sprs_cd => p_sprs_cd
2472 ,p_any_or_all_cd => p_any_or_all_cd
2473 ,p_hide_flag => p_hide_flag
2474 ,p_rqd_flag => p_rqd_flag
2475 ,p_chg_rcd_upd_flag => nvl(p_chg_rcd_upd_flag,'N')
2476 ,p_sort1_data_elmt_in_rcd_id => l_sort1_elm_in_rcd_id
2477 ,p_sort2_data_elmt_in_rcd_id => l_sort2_elm_in_rcd_id
2478 ,p_sort3_data_elmt_in_rcd_id => l_sort3_elm_in_rcd_id
2479 ,p_sort4_data_elmt_in_rcd_id => l_sort4_elm_in_rcd_id
2480 ,p_last_update_date => l_last_update_date
2481 ,p_creation_date => l_last_update_date
2482 ,p_last_update_login => 0
2483 ,p_created_by => l_last_updated_by
2484 ,p_last_updated_by => l_last_updated_by
2485 ,p_object_version_number => l_object_version_number);
2486 WHEN OTHERS THEN
2487 RAISE;
2488 END ;
2489 hr_utility.set_location(' Leaving ' || l_proc, 10);
2490 END load_record_in_file;
2491
2492
2493 PROCEDURE load_ext_data_elmt(p_data_elemt_name IN VARCHAR2
2494 ,p_parent_data_element IN VARCHAR2 DEFAULT NULL
2495 ,p_field_short_name IN VARCHAR2 DEFAULT NULL
2496 ,p_parent_record_name IN VARCHAR2 DEFAULT NULL
2497 ,p_owner IN VARCHAR2
2498 ,p_last_update_date IN VARCHAR2
2499 ,p_ttl_fnctn_cd IN VARCHAR2
2500 ,p_ttl_cond_oper_cd IN VARCHAR2
2501 ,p_ttl_cond_val IN VARCHAR2
2502 ,p_data_elmt_typ_cd IN VARCHAR2
2503 ,p_data_elmt_rl IN VARCHAR2
2504 ,p_frmt_mask_cd IN VARCHAR2
2505 ,p_string_val IN VARCHAR2
2506 ,p_dflt_val IN VARCHAR2
2507 ,p_max_length_num IN VARCHAR2
2508 ,p_just_cd IN VARCHAR2
2509 ,p_legislation_code IN VARCHAR2
2510 ,p_business_group in varchar2
2511 ,p_xml_tag_name in VARCHAR2
2512 ,p_defined_balance in VARCHAR2 DEFAULT NULL
2513 ) IS
2514
2515
2516 --
2517 l_ext_field_id NUMBER;
2518 l_ext_data_elmt_id NUMBER;
2519 l_ext_rcd_id NUMBER;
2520 l_parent_data_elmt_id NUMBER;
2521 l_formula_id NUMBER;
2522 l_object_version_number NUMBER;
2523 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
2524 l_temp VARCHAR2(1);
2525 l_last_update_date DATE;
2526 l_last_updated_by NUMBER;
2527 l_tmp_id number ;
2528 l_tmp_ovn NUMBER;
2529 l_new_business_group_id number ;
2530 l_string ben_ext_data_elmt.string_val%type ;
2531 l_defined_balance_id number ;
2532 --
2533 CURSOR c_dt_rule(p_data_elmt_rl VARCHAR2
2534 ,l_new_business_group_id NUMBER
2535 ,l_last_update_date in date ) IS
2536 SELECT formula_id
2537 FROM ff_formulas_f
2538 WHERE formula_name = p_data_elmt_rl
2539 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
2540 AND nvl(l_last_update_date,trunc(sysdate)) BETWEEN effective_start_date and effective_end_date ;
2541 --
2542 l_proc varchar2(100) := 'BEN_EXT_SEED.load_ext_data_elmt' ;
2543 BEGIN
2544 hr_utility.set_location(' Entering ' || l_proc, 10);
2545
2546 get_who_values(p_owner => p_owner
2547 ,p_last_update_vc => p_last_update_date
2548 ,p_last_update_date => l_last_update_date
2549 ,p_last_updated_by => l_last_updated_by
2550 ,p_legislation_code => l_legislation_code
2551 ,p_business_group => p_business_group
2552 ,p_business_group_id => l_new_business_group_id );
2553
2554 IF p_data_elmt_typ_cd IN ('R','S') THEN
2555 IF p_data_elmt_typ_cd = 'R' THEN
2556 BEGIN
2557 --OPEN c_dt_rule(p_data_elmt_rl, l_new_business_group_id , l_last_update_date );
2558 OPEN c_dt_rule(p_data_elmt_rl, l_new_business_group_id , trunc(sysdate) );
2559 FETCH c_dt_rule into l_formula_id;
2560 IF c_dt_rule%NOTFOUND THEN
2561 close c_dt_rule;
2562 raise_application_error(-20001,' no formula Data element '||
2563 p_data_elemt_name||' legislation code '||l_legislation_code);
2564 else
2565 close c_dt_rule;
2566 END IF;
2567
2568 END;
2569 BEGIN
2570 l_tmp_id := null ;
2571 l_tmp_ovn := null ;
2572 SELECT ext_data_elmt_id , object_version_number,string_val
2573 INTO l_tmp_id , l_tmp_ovn,l_string
2574 FROM ben_ext_data_elmt
2575 WHERE name = p_data_elemt_name
2576 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
2577 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2578 ( p_business_group is null and business_group_id is null ) ) ;
2579
2580 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
2581 --AND data_elmt_rl = l_formula_id;
2582
2583 -- UPDATE
2584 -- the element may be found there may be changes in data element attribute
2585 -- if the record found the the element is updated
2586 --- Dont update for the custome record used can changed the record
2587 -- more over thge record may be linked to some other extrcact defintion
2588 -- since the forword element reference fixed there is possible
2589 -- element created for forwored purpose, so update the element
2590 -- if element exisit and comming element is for forward regerence then dont update
2591 if (l_new_business_group_id is null or g_override = 'Y' or l_string = '$FORWARD$')
2592 and nvl(p_string_val,' ') <> '$FORWARD$' then
2593 ben_xel_upd.upd(
2594 p_effective_date => trunc(sysdate)
2595 ,p_ext_data_elmt_id => l_tmp_id
2596 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2597 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2598 ,p_ttl_cond_val => p_ttl_cond_val
2599 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2600 ,p_ext_fld_id => null
2601 ,p_data_elmt_rl => l_formula_id
2602 ,p_frmt_mask_cd => p_frmt_mask_cd
2603 ,p_string_val => p_string_val
2604 ,p_dflt_val => p_dflt_val
2605 ,p_max_length_num => p_max_length_num
2606 ,p_just_cd => p_just_cd
2607 ,p_legislation_code => l_legislation_code
2608 ,p_business_group_id => l_new_business_group_id
2609 ,p_last_update_date => l_last_update_date
2610 ,p_creation_date => l_last_update_date
2611 ,p_last_update_login => 0
2612 ,p_created_by => l_last_updated_by
2613 ,p_last_updated_by => l_last_updated_by
2614 ,p_object_version_number => l_tmp_ovn
2615 ,p_xml_tag_name => p_xml_tag_name );
2616 end if ;
2617
2618 EXCEPTION
2619 WHEN NO_DATA_FOUND THEN
2620 ben_xel_ins.ins(p_effective_date => trunc(sysdate)
2621 ,p_ext_data_elmt_id => l_ext_data_elmt_id
2622 ,p_name => p_data_elemt_name
2623 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2624 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2625 ,p_ttl_cond_val => p_ttl_cond_val
2626 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2627 ,p_ext_fld_id => null
2628 ,p_data_elmt_rl => l_formula_id
2629 ,p_frmt_mask_cd => p_frmt_mask_cd
2630 ,p_string_val => p_string_val
2631 ,p_dflt_val => p_dflt_val
2632 ,p_max_length_num => p_max_length_num
2633 ,p_just_cd => p_just_cd
2634 ,p_legislation_code => l_legislation_code
2635 ,p_business_group_id => l_new_business_group_id
2636 ,p_last_update_date => l_last_update_date
2637 ,p_creation_date => l_last_update_date
2638 ,p_last_update_login => 0
2639 ,p_created_by => l_last_updated_by
2640 ,p_last_updated_by => l_last_updated_by
2641 ,p_object_version_number => l_object_version_number
2642 ,p_xml_tag_name => p_xml_tag_name );
2643 END;
2644 ELSIF p_data_elmt_typ_cd = 'S' then
2645 BEGIN
2646 l_tmp_id := null ;
2647 l_tmp_ovn := null ;
2648 SELECT ext_data_elmt_id , object_version_number,string_val
2649 INTO l_tmp_id , l_tmp_ovn,l_string
2650 FROM ben_ext_data_elmt
2651 WHERE name = p_data_elemt_name
2652 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2653 ( p_business_group is null and business_group_id is null ) )
2654 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
2655 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2656 --AND string_val = p_string_val;
2657
2658
2659 -- UPDATE
2660 -- the element may be found there may be changes in data element attribute
2661 -- if the record found the the element is updated
2662 -- Dont update for the custome record used can changed the record
2663 -- more over thge record may be linked to some other extrcact defintion
2664 if (l_new_business_group_id is null or g_override = 'Y' or l_string = '$FORWARD$' )
2665 and nvl(p_string_val,' ') <> '$FORWARD$' then
2666 ben_xel_upd.upd(
2667 p_effective_date => l_last_update_date
2668 ,p_ext_data_elmt_id => l_tmp_id
2669 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2670 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2671 ,p_ttl_cond_val => p_ttl_cond_val
2672 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2673 ,p_frmt_mask_cd => p_frmt_mask_cd
2674 ,p_string_val => p_string_val
2675 ,p_dflt_val => p_dflt_val
2676 ,p_max_length_num => p_max_length_num
2677 ,p_just_cd => p_just_cd
2678 ,p_legislation_code => l_legislation_code
2679 ,p_business_group_id => l_new_business_group_id
2680 ,p_last_update_date => l_last_update_date
2681 ,p_creation_date => l_last_update_date
2682 ,p_last_update_login => 0
2683 ,p_created_by => l_last_updated_by
2684 ,p_last_updated_by => l_last_updated_by
2685 ,p_object_version_number => l_tmp_ovn
2686 ,p_xml_tag_name => p_xml_tag_name );
2687 end if ;
2688
2689 EXCEPTION
2690 WHEN NO_DATA_FOUND THEN
2691 ben_xel_ins.ins(p_effective_date => l_last_update_date
2692 ,p_ext_data_elmt_id => l_ext_data_elmt_id
2693 ,p_name => p_data_elemt_name
2694 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2695 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2696 ,p_ttl_cond_val => p_ttl_cond_val
2697 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2698 ,p_ext_fld_id => null
2699 ,p_data_elmt_rl => null
2700 ,p_frmt_mask_cd => p_frmt_mask_cd
2701 ,p_string_val => p_string_val
2702 ,p_dflt_val => p_dflt_val
2703 ,p_max_length_num => p_max_length_num
2704 ,p_just_cd => p_just_cd
2705 ,p_legislation_code => l_legislation_code
2706 ,p_business_group_id => l_new_business_group_id
2707 ,p_last_update_date => l_last_update_date
2708 ,p_creation_date => l_last_update_date
2709 ,p_last_update_login => 0
2710 ,p_created_by => l_last_updated_by
2711 ,p_last_updated_by => l_last_updated_by
2712 ,p_object_version_number => l_object_version_number
2713 ,p_xml_tag_name => p_xml_tag_name );
2714 END;
2715 END IF;
2716 ELSIF p_data_elmt_typ_cd in ( 'T','C') THEN
2717 BEGIN
2718 --- there is possible element creatd for forward reason
2719 if p_parent_record_name is not null then
2720 SELECT ext_rcd_id
2721 INTO l_ext_rcd_id
2722 FROM ben_ext_rcd
2723 WHERE name = p_parent_record_name
2724 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
2725 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2726 end if ;
2727 EXCEPTION
2728 WHEN NO_DATA_FOUND THEN
2729 -- RAISE;
2730 raise_application_error(-20001,'No parent record element '||p_data_elemt_name||
2731 ' legislation code '||l_legislation_code||' parent : '||p_parent_record_name);
2732 END;
2733 BEGIN
2734
2735 IF p_parent_data_element <> 'NULL' THEN
2736 SELECT ext_data_elmt_id
2737 INTO l_parent_data_elmt_id
2738 FROM ben_ext_data_elmt
2739 WHERE name = p_parent_data_element
2740 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2741 ( p_business_group is null and business_group_id is null ) )
2742 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
2743 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2744 ELSE
2745 l_parent_data_elmt_id := NULL;
2746 END IF;
2747
2748 EXCEPTION
2749 WHEN NO_DATA_FOUND THEN
2750 raise_application_error(-20001,'No parent data element '||p_data_elemt_name||
2751 ' legislation code '||l_legislation_code||' parent : '|| p_parent_data_element);
2752 END;
2753 BEGIN
2754 l_tmp_id := null ;
2755 l_tmp_ovn := null ;
2756 SELECT ext_data_elmt_id , object_version_number,string_val
2757 INTO l_tmp_id , l_tmp_ovn ,l_string
2758 FROM ben_ext_data_elmt
2759 WHERE name = p_data_elemt_name
2760 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2761 ( p_business_group is null and business_group_id is null ) )
2762 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2763
2764 --- UPDATE
2765 --- the element may be found there may be changes in data element attribute
2766 --- if the record found the the element is updated
2767 --- Dont update for the custome record used can changed the record
2768 -- more over thge record may be linked to some other extrcact defintion
2769 if (l_new_business_group_id is null or g_override = 'Y' or l_string = '$FORWARD$' )
2770 and nvl(p_string_val,' ') <> '$FORWARD$' then
2771
2772 ben_xel_upd.upd(
2773 p_effective_date => l_last_update_date
2774 ,p_ext_data_elmt_id => l_tmp_id
2775 ,p_ttl_cond_ext_data_elmt_id => l_ext_rcd_id
2776 ,p_ttl_sum_ext_data_elmt_id => l_parent_data_elmt_id
2777 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2778 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2779 ,p_ttl_cond_val => p_ttl_cond_val
2780 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2781 ,p_frmt_mask_cd => p_frmt_mask_cd
2782 ,p_dflt_val => p_dflt_val
2783 ,p_max_length_num => p_max_length_num
2784 ,p_just_cd => p_just_cd
2785 ,p_legislation_code => l_legislation_code
2786 ,p_business_group_id => l_new_business_group_id
2787 ,p_last_update_date => l_last_update_date
2788 ,p_creation_date => l_last_update_date
2789 ,p_last_update_login => 0
2790 ,p_created_by => l_last_updated_by
2791 ,p_last_updated_by => l_last_updated_by
2792 ,p_object_version_number => l_tmp_ovn
2793 ,p_xml_tag_name => p_xml_tag_name);
2794 end if ;
2795
2796 EXCEPTION WHEN NO_DATA_FOUND THEN
2797 ben_xel_ins.ins(p_effective_date => l_last_update_date
2798 ,p_ext_data_elmt_id => l_ext_data_elmt_id
2799 ,p_name => p_data_elemt_name
2800 ,p_ttl_cond_ext_data_elmt_id => l_ext_rcd_id
2801 ,p_ttl_sum_ext_data_elmt_id => l_parent_data_elmt_id
2802 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2803 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2804 ,p_ttl_cond_val => p_ttl_cond_val
2805 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2806 ,p_ext_fld_id => NULL
2807 ,p_data_elmt_rl => NULL
2808 ,p_frmt_mask_cd => p_frmt_mask_cd
2809 ,p_string_val => NULL
2810 ,p_dflt_val => p_dflt_val
2811 ,p_max_length_num => p_max_length_num
2812 ,p_just_cd => p_just_cd
2813 ,p_legislation_code => l_legislation_code
2814 ,p_business_group_id => l_new_business_group_id
2815 ,p_last_update_date => l_last_update_date
2816 ,p_creation_date => l_last_update_date
2817 ,p_last_update_login => 0
2818 ,p_created_by => l_last_updated_by
2819 ,p_last_updated_by => l_last_updated_by
2820 ,p_object_version_number => l_object_version_number
2821 ,p_xml_tag_name => p_xml_tag_name );
2822 WHEN OTHERS THEN
2823 RAISE;
2824 END;
2825 ELSIF p_data_elmt_typ_cd = 'P' THEN -- payroll balance
2826 if p_defined_balance is not null then
2827
2828 Declare
2829 cursor c_pay_bal is
2830 select c.defined_balance_id ID
2831 from pay_defined_balances c ,
2832 pay_balance_types a ,
2833 pay_balance_dimensions b
2834 where
2835 a.balance_type_id = c.balance_type_id
2836 and c.balance_dimension_id = b.balance_dimension_id
2837 and b.dimension_level in ( 'PER' ,'ASG')
2838 and a.balance_name || ' [ ' || b.dimension_name || ' ]' = p_defined_balance
2839 AND ( NVL(a.legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~') or l_legislation_code is null )
2840 AND ( ( nvl(l_new_business_group_id, -1) = nvl(a.business_group_id , nvl(l_new_business_group_id, -1))) or
2841 ( p_business_group is null and a.business_group_id is null ) ) ;
2842
2843
2844 Begin
2845
2846 open c_pay_bal ;
2847 fetch c_pay_bal into l_defined_balance_id ;
2848 if c_pay_bal%notfound then
2849 close c_pay_bal ;
2850
2851 raise_application_error(-20001,' No Payroll Defined Balance '||
2852 p_defined_balance ||' legislation code '||l_legislation_code);
2853 end if ;
2854
2855 l_tmp_id := null ;
2856 l_tmp_ovn := null ;
2857 SELECT ext_data_elmt_id , object_version_number ,string_val
2858 INTO l_tmp_id , l_tmp_ovn,l_string
2859 FROM ben_ext_data_elmt
2860 WHERE name = p_data_elemt_name
2861 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2862 ( p_business_group is null and business_group_id is null ) )
2863 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2864
2865
2866 -- UPDATE
2867 -- the element may be found there may be changes in data element attribute
2868 -- if the record found the the element is updated
2869 --- Dont update for the custome record used can changed the record
2870 -- more over thge record may be linked to some other extrcact defintion
2871 if (l_new_business_group_id is null or g_override = 'Y' or l_string = '$FORWARD$' )
2872 and nvl(p_string_val,' ') <> '$FORWARD$' then
2873 ben_xel_upd.upd(
2874 p_effective_date => l_last_update_date
2875 ,p_ext_data_elmt_id => l_tmp_id
2876 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2877 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2878 ,p_ttl_cond_val => p_ttl_cond_val
2879 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2880 ,p_ext_fld_id => l_ext_field_id
2881 ,p_string_val => p_string_val
2882 ,p_frmt_mask_cd => p_frmt_mask_cd
2883 ,p_dflt_val => p_dflt_val
2884 ,p_max_length_num => p_max_length_num
2885 ,p_just_cd => p_just_cd
2886 ,p_legislation_code => l_legislation_code
2887 ,p_business_group_id => l_new_business_group_id
2888 ,p_last_update_date => l_last_update_date
2889 ,p_creation_date => l_last_update_date
2890 ,p_last_update_login => 0
2891 ,p_created_by => l_last_updated_by
2892 ,p_last_updated_by => l_last_updated_by
2893 ,p_object_version_number => l_tmp_ovn
2894 ,p_defined_balance_id => l_defined_balance_id
2895 ,p_xml_tag_name => p_xml_tag_name);
2896
2897 end if ;
2898
2899 EXCEPTION
2900 WHEN NO_DATA_FOUND THEN
2901 ben_xel_ins.ins(p_effective_date => l_last_update_date
2902 ,p_ext_data_elmt_id => l_ext_data_elmt_id
2903 ,p_name => p_data_elemt_name
2904 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2905 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2906 ,p_ttl_cond_val => p_ttl_cond_val
2907 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2908 ,p_ext_fld_id => l_ext_field_id
2909 ,p_data_elmt_rl => null -- p_data_elmt_rl
2910 ,p_frmt_mask_cd => p_frmt_mask_cd
2911 ,p_string_val => p_string_val
2912 ,p_dflt_val => p_dflt_val
2913 ,p_max_length_num => p_max_length_num
2914 ,p_just_cd => p_just_cd
2915 ,p_legislation_code => l_legislation_code
2916 ,p_business_group_id => l_new_business_group_id
2917 ,p_last_update_date => l_last_update_date
2918 ,p_creation_date => l_last_update_date
2919 ,p_last_update_login => 0
2920 ,p_created_by => l_last_updated_by
2921 ,p_last_updated_by => l_last_updated_by
2922 ,p_object_version_number => l_object_version_number
2923 ,p_defined_balance_id => l_defined_balance_id
2924 ,p_xml_tag_name => p_xml_tag_name);
2925 WHEN OTHERS THEN
2926 RAISE;
2927 end ;
2928
2929 end if ; -- p_data_elmt_typ_cd
2930 ELSE
2931 BEGIN
2932 SELECT ext_fld_id
2933 INTO l_ext_field_id
2934 FROM ben_ext_fld
2935 WHERE short_name = p_field_short_name;
2936 EXCEPTION
2937 WHEN NO_DATA_FOUND THEN
2938 RAISE;
2939 END;
2940
2941 BEGIN
2942 l_tmp_id := null ;
2943 l_tmp_ovn := null ;
2944 SELECT ext_data_elmt_id , object_version_number ,string_val
2945 INTO l_tmp_id , l_tmp_ovn,l_string
2946 FROM ben_ext_data_elmt
2947 WHERE name = p_data_elemt_name
2948 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
2949 ( p_business_group is null and business_group_id is null ) )
2950 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
2951
2952
2953 -- UPDATE
2954 -- the element may be found there may be changes in data element attribute
2955 -- if the record found the the element is updated
2956 --- Dont update for the custome record used can changed the record
2957 -- more over thge record may be linked to some other extrcact defintion
2958 if (l_new_business_group_id is null or g_override = 'Y' or l_string = '$FORWARD$' )
2959 and nvl(p_string_val,' ') <> '$FORWARD$' then
2960 ben_xel_upd.upd(
2961 p_effective_date => l_last_update_date
2962 ,p_ext_data_elmt_id => l_tmp_id
2963 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2964 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2965 ,p_ttl_cond_val => p_ttl_cond_val
2966 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2967 ,p_ext_fld_id => l_ext_field_id
2968 ,p_string_val => p_string_val
2969 ,p_frmt_mask_cd => p_frmt_mask_cd
2970 ,p_dflt_val => p_dflt_val
2971 ,p_max_length_num => p_max_length_num
2972 ,p_just_cd => p_just_cd
2973 ,p_legislation_code => l_legislation_code
2974 ,p_business_group_id => l_new_business_group_id
2975 ,p_last_update_date => l_last_update_date
2976 ,p_creation_date => l_last_update_date
2977 ,p_last_update_login => 0
2978 ,p_created_by => l_last_updated_by
2979 ,p_last_updated_by => l_last_updated_by
2980 ,p_object_version_number => l_tmp_ovn
2981 ,p_xml_tag_name => p_xml_tag_name);
2982
2983 end if ;
2984
2985 EXCEPTION
2986 WHEN NO_DATA_FOUND THEN
2987 ben_xel_ins.ins(p_effective_date => l_last_update_date
2988 ,p_ext_data_elmt_id => l_ext_data_elmt_id
2989 ,p_name => p_data_elemt_name
2990 ,p_ttl_fnctn_cd => p_ttl_fnctn_cd
2991 ,p_ttl_cond_oper_cd => p_ttl_cond_oper_cd
2992 ,p_ttl_cond_val => p_ttl_cond_val
2993 ,p_data_elmt_typ_cd => p_data_elmt_typ_cd
2994 ,p_ext_fld_id => l_ext_field_id
2995 ,p_data_elmt_rl => null -- p_data_elmt_rl
2996 ,p_frmt_mask_cd => p_frmt_mask_cd
2997 ,p_string_val => p_string_val
2998 ,p_dflt_val => p_dflt_val
2999 ,p_max_length_num => p_max_length_num
3000 ,p_just_cd => p_just_cd
3001 ,p_legislation_code => l_legislation_code
3002 ,p_business_group_id => l_new_business_group_id
3003 ,p_last_update_date => l_last_update_date
3004 ,p_creation_date => l_last_update_date
3005 ,p_last_update_login => 0
3006 ,p_created_by => l_last_updated_by
3007 ,p_last_updated_by => l_last_updated_by
3008 ,p_object_version_number => l_object_version_number
3009 ,p_xml_tag_name => p_xml_tag_name);
3010 WHEN OTHERS THEN
3011 RAISE;
3012 END;
3013 END IF;
3014 hr_utility.set_location(' Leaving ' || l_proc, 10);
3015 END load_ext_data_elmt;
3016
3017 --
3018 PROCEDURE load_ext_data_elmt_in_rcd(p_data_element_name IN VARCHAR2
3019 ,p_record_name IN VARCHAR2
3020 ,p_owner IN VARCHAR2
3021 ,p_last_update_date IN VARCHAR2
3022 ,p_rqd_flag IN VARCHAR2
3023 ,p_hide_flag IN VARCHAR2
3024 ,p_seq_num IN VARCHAR2
3025 ,p_strt_pos IN VARCHAR2
3026 ,p_dlmtr_val IN VARCHAR2
3027 ,p_sprs_cd IN VARCHAR2
3028 ,p_any_or_all_cd IN VARCHAR2
3029 ,p_legislation_code IN VARCHAR2
3030 ,p_business_group in VARCHAR2
3031 ) IS
3032 --
3033 l_ext_rcd_id NUMBER;
3034 l_ext_data_elmt_id NUMBER;
3035 l_ext_data_elmt_in_rcd_id NUMBER;
3036 l_object_version_number NUMBER;
3037 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
3038 l_temp VARCHAR2(1);
3039 l_last_update_date DATE;
3040 l_last_updated_by NUMBER;
3041 l_new_business_group_id number ;
3042 l_ovn number ;
3043 l_STRT_POS ben_ext_data_elmt_in_rcd.STRT_POS%type ;
3044 l_DLMTR_VAL ben_ext_data_elmt_in_rcd.DLMTR_VAL%type ;
3045 l_HIDE_FLAG ben_ext_data_elmt_in_rcd.HIDE_FLAG%type ;
3046 l_RQD_FLAG ben_ext_data_elmt_in_rcd.RQD_FLAG%type ;
3047 l_sprs_cd ben_ext_data_elmt_in_rcd.sprs_cd%type ;
3048 l_proc varchar2(100) := 'BEN_EXT_SEED.load_ext_data_elmt_in_rcd' ;
3049 BEGIN
3050 hr_utility.set_location(' Entering ' || l_proc, 10);
3051 get_who_values(p_owner => p_owner
3052 ,p_last_update_vc => p_last_update_date
3053 ,p_last_update_date => l_last_update_date
3054 ,p_last_updated_by => l_last_updated_by
3055 ,p_legislation_code => l_legislation_code
3056 ,p_business_group => p_business_group
3057 ,p_business_group_id => l_new_business_group_id );
3058
3059 BEGIN
3060
3061 SELECT ext_rcd_id
3062 INTO l_ext_rcd_id
3063 FROM ben_ext_rcd
3064 WHERE name = p_record_name
3065 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3066 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
3067 EXCEPTION
3068 WHEN NO_DATA_FOUND THEN
3069 RAISE;
3070 END;
3071 BEGIN
3072 SELECT ext_data_elmt_id
3073 INTO l_ext_data_elmt_id
3074 FROM ben_ext_data_elmt
3075 WHERE name = p_data_element_name
3076 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
3077 ( p_business_group is null and business_group_id is null ) )
3078 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3079 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3080 EXCEPTION
3081 WHEN NO_DATA_FOUND THEN
3082 --RAISE;
3083 raise_application_error(-20001,'Data element '||p_data_element_name|| '
3084 legislation code '||l_legislation_code);
3085 END;
3086 BEGIN
3087 SELECT OBJECT_VERSION_NUMBER,
3088 EXT_DATA_ELMT_IN_RCD_ID,
3089 STRT_POS ,
3090 DLMTR_VAL,
3091 RQD_FLAG,
3092 HIDE_FLAG,
3093 SPRS_CD
3094 INTO l_ovn,
3095 l_EXT_DATA_ELMT_IN_RCD_ID,
3096 l_STRT_POS ,
3097 l_DLMTR_VAL,
3098 l_RQD_FLAG ,
3099 l_HIDE_FLAG,
3100 l_SPRS_CD
3101 FROM ben_ext_data_elmt_in_rcd
3102 WHERE ext_rcd_id = l_ext_rcd_id
3103 AND ext_data_elmt_id = l_ext_data_elmt_id
3104 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3105 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3106 AND NVL(seq_num,-987123654) = NVL(p_seq_num,-987123654);
3107
3108 --- whne the extract is global and delimiter or start postion changed
3109 --- then update the extract
3110
3111 if ( l_new_business_group_id is null or g_override = 'Y') and
3112 ( nvl(l_STRT_POS,'-1') <> nvl(p_STRT_POS,'-1') or
3113 nvl(l_DLMTR_VAL,'-1') <> nvl(p_DLMTR_VAL,'-1') or
3114 nvl(l_rqd_FLAG,'N') <> nvl(p_rqd_FLAG,'N') OR
3115 nvl(l_SPRS_CD,'-1') <> nvl(p_SPRS_CD,'-1') OR
3116 nvl(l_HIDE_FLAG,'N') <> nvl(p_HIDE_FLAG,'N')
3117 ) then
3118
3119
3120 ben_xer_upd.upd(p_effective_date => l_last_update_date
3121 ,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
3122 ,p_business_group_id => l_new_business_group_id
3123 ,p_legislation_code => l_legislation_code
3124 ,p_rqd_flag => p_rqd_flag
3125 ,p_hide_flag => p_hide_flag
3126 ,p_strt_pos => p_strt_pos
3127 ,p_dlmtr_val => p_dlmtr_val
3128 ,p_SPRS_CD => p_SPRS_CD
3129 ,p_object_version_number => l_ovn);
3130
3131
3132 end if ;
3133
3134 -- seq_num should not be null but since the column is nullable put nvl check
3135 -- what this change does mean is that if this data element does exist
3136 -- at the specified seq_num then no action will take place
3137 -- effectively we cannot update attributes of data element in rcd
3138 -- needs enhancement.
3139 EXCEPTION
3140 WHEN NO_DATA_FOUND THEN
3141
3142 /* If the Same Sequence Find Delete the Record for the Sequence */
3143
3144 Declare
3145
3146
3147 cursor c_ext_where_clause (p_ext_data_elmt_in_rcd_id number) is
3148 select ext_where_clause_id,object_version_number
3149 from ben_ext_where_clause
3150 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
3151
3152 cursor c_ext_incl_chg_id (p_ext_data_elmt_in_rcd_id number) is
3153 select ext_incl_chg_id,object_version_number
3154 from ben_ext_incl_chg
3155 where ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
3156
3157 l_obj_ver_number number ;
3158
3159 cursor c_elmt_del1 (c_ext_data_elmt_id number ,
3160 c_Ext_rcd_id number ) is
3161 select object_version_number,ext_data_elmt_in_rcd_id
3162 from ben_ext_data_elmt_in_rcd
3163 where ext_data_elmt_id <> nvl(c_ext_data_elmt_id,-1)
3164 and ext_rcd_id = c_ext_rcd_id
3165 and seq_num = p_seq_num ;
3166
3167
3168 Begin
3169
3170 open c_elmt_del1(l_ext_data_elmt_id,l_ext_rcd_id) ;
3171 fetch c_elmt_del1 into l_object_version_number,l_ext_data_elmt_in_rcd_id ;
3172 if c_elmt_del1%found then
3173 /* if the sequence found delete the old reco */
3174 -- before deleting make sure the chile in where and inclusion are deleted
3175
3176 if ( l_new_business_group_id is null or g_override = 'Y' ) then
3177 --- delete where clause of the data element in rcd
3178 for i in c_ext_where_clause( l_ext_data_elmt_in_rcd_id)
3179 Loop
3180 l_obj_ver_number := i.object_version_number ;
3181
3182 ben_xwc_del.del
3183 (
3184 p_effective_date => trunc(sysdate),
3185 p_ext_where_clause_id => i.ext_where_clause_id ,
3186 p_object_version_number => l_obj_ver_number
3187 ) ;
3188 end Loop ;
3189
3190 -- delete the inclusion element of the data element in rcd
3191 for i in c_ext_incl_chg_id(l_ext_data_elmt_in_rcd_id)
3192 Loop
3193 l_obj_ver_number := i.object_version_number ;
3194
3195 ben_xic_del.del
3196 (
3197 p_effective_date => trunc(sysdate),
3198 p_ext_incl_chg_id => i.ext_incl_chg_id ,
3199 p_object_version_number => l_obj_ver_number
3200 ) ;
3201 end Loop ;
3202
3203 -- delete the data elmt in rcd for order change
3204
3205 ben_xer_del.del(p_effective_date => l_last_update_date
3206 ,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
3207 ,p_object_version_number => l_object_version_number);
3208 end if ;
3209 end if ;
3210 close c_elmt_del1 ;
3211 Exception
3212 WHEN NO_DATA_FOUND THEN
3213 null ;
3214 When Others then
3215 RAISE ;
3216 End ;
3217 /* Deletion part is over for duplicate seq number */
3218
3219 ben_xer_ins.ins(p_effective_date => l_last_update_date
3220 ,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
3221 ,p_business_group_id => l_new_business_group_id
3222 ,p_ext_data_elmt_id => l_ext_data_elmt_id
3223 ,p_ext_rcd_id => l_ext_rcd_id
3224 ,p_legislation_code => l_legislation_code
3225 ,p_rqd_flag => p_rqd_flag
3226 ,p_hide_flag => p_hide_flag
3227 ,p_strt_pos => p_strt_pos
3228 ,p_dlmtr_val => p_dlmtr_val
3229 ,p_sprs_cd => p_sprs_cd
3230 ,p_seq_num => p_seq_num
3231 ,p_any_or_all_cd => p_any_or_all_cd
3232 ,p_last_update_date => l_last_update_date
3233 ,p_creation_date => l_last_update_date
3234 ,p_last_update_login => 0
3235 ,p_created_by => l_last_updated_by
3236 ,p_last_updated_by => l_last_updated_by
3237 ,p_object_version_number => l_object_version_number);
3238
3239 END;
3240 hr_utility.set_location(' Leaving ' || l_proc, 10);
3241 END load_ext_data_elmt_in_rcd;
3242
3243 PROCEDURE load_ext_where_clause(p_data_elmt_name IN VARCHAR2
3244 ,p_record_name IN VARCHAR2
3245 ,p_file_name IN VARCHAR2 DEFAULT NULL
3246 ,p_record_data_elmt_name IN VARCHAR2 DEFAULT NULL
3247 ,p_cond_ext_data_elmt_name IN VARCHAR2 DEFAULT NULL
3248 ,p_owner IN VARCHAR2
3249 ,p_last_update_date IN VARCHAR2
3250 ,p_seq_num IN VARCHAR2
3251 ,p_oper_cd IN VARCHAR2
3252 ,p_val IN VARCHAR2
3253 ,p_and_or_cd IN VARCHAR2
3254 ,p_legislation_code IN VARCHAR2
3255 ,p_business_group IN VARCHAR2
3256 ) is
3257 l_ext_file_id NUMBER;
3258 l_ext_rcd_id NUMBER;
3259 l_ext_data_elmt_id NUMBER;
3260 l_cond_ext_data_elmt_id NUMBER;
3261 l_ext_rcd_in_file_id NUMBER;
3262 l_ext_data_elmt_in_rcd_id NUMBER;
3263 l_cond_ext_data_elmt_in_rcd_id NUMBER;
3264 l_ext_where_clause_id NUMBER;
3265 l_object_version_number NUMBER;
3266 l_legislation_code VARCHAR2(240) := p_legislation_code; -- utf8
3267 l_temp VARCHAR2(1);
3268 l_last_update_date DATE;
3269 l_last_updated_by NUMBER;
3270 l_new_business_group_id NUMBER;
3271 l_record_data_elmt_name varchar2(600) ;
3272
3273 l_proc varchar2(100) := 'BEN_EXT_SEED.load_ext_where_clause' ;
3274 BEGIN
3275 hr_utility.set_location(' Entering ' || l_proc, 10);
3276 get_who_values(p_owner => p_owner
3277 ,p_last_update_vc => p_last_update_date
3278 ,p_last_update_date => l_last_update_date
3279 ,p_last_updated_by => l_last_updated_by
3280 ,p_legislation_code => l_legislation_code
3281 ,p_business_group => p_business_group
3282 ,p_business_group_id => l_new_business_group_id );
3283
3284 l_record_data_elmt_name := nvl(p_record_data_elmt_name,p_data_elmt_name);
3285
3286 if p_record_name is not null then
3287 BEGIN
3288 SELECT ext_rcd_id
3289 INTO l_ext_rcd_id
3290 FROM ben_ext_rcd
3291 WHERE name = p_record_name
3292 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3293 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3294 ;
3295 EXCEPTION
3296 WHEN NO_DATA_FOUND THEN
3297
3298 RAISE;
3299 END;
3300 end if ;
3301
3302 IF p_file_name IS NOT NULL THEN
3303 BEGIN
3304 SELECT ext_file_id
3305 INTO l_ext_file_id
3306 FROM ben_ext_file
3307 WHERE name = p_file_name
3308 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3309 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3310 ;
3311 EXCEPTION
3312 WHEN NO_DATA_FOUND THEN
3313 RAISE;
3314 END;
3315 BEGIN
3316 SELECT ext_rcd_in_file_id
3317 INTO l_ext_rcd_in_file_id
3318 FROM ben_ext_rcd_in_file
3319 WHERE ext_file_id = l_ext_file_id
3320 AND ext_rcd_id = l_ext_rcd_id
3321 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3322 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3323 EXCEPTION
3324 WHEN NO_DATA_FOUND THEN
3325 RAISE;
3326 END;
3327 ELSE
3328 l_ext_file_id := NULL;
3329 l_ext_rcd_in_file_id := NULL;
3330 END IF;
3331
3332 BEGIN
3333 SELECT ext_data_elmt_id
3334 INTO l_ext_data_elmt_id
3335 FROM ben_ext_data_elmt
3336 WHERE name = p_data_elmt_name
3337 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
3338 ( p_business_group is null and business_group_id is null ) )
3339 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
3340 ;
3341
3342 EXCEPTION
3343 WHEN NO_DATA_FOUND THEN
3344 RAISE;
3345 END;
3346
3347 if p_cond_ext_data_elmt_name is not null then
3348 BEGIN
3349 SELECT ext_data_elmt_id
3350 INTO l_cond_ext_data_elmt_id
3351 FROM ben_ext_data_elmt
3352 WHERE name = p_cond_ext_data_elmt_name
3353 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
3354 ( p_business_group is null and business_group_id is null ) )
3355 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3356 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~')
3357 ;
3358 EXCEPTION
3359 WHEN NO_DATA_FOUND THEN
3360 RAISE;
3361 END;
3362 end if ;
3363
3364 if p_record_name is not null then
3365
3366 BEGIN
3367 -- the element in record may attched to different element for advance conditions
3368 -- the first select get the element in record
3369 SELECT ext_data_elmt_in_rcd_id
3370 INTO l_ext_data_elmt_in_rcd_id
3371 FROM ben_ext_data_elmt_in_rcd rcd,
3372 ben_ext_data_elmt elmt
3373 WHERE rcd.ext_rcd_id = l_ext_rcd_id
3374 and elmt.name = l_record_data_elmt_name
3375 AND rcd.ext_data_elmt_id = elmt.ext_data_elmt_id
3376 AND nvl( l_new_business_group_id, -1) = nvl(rcd.business_group_id , -1)
3377 AND nvl( l_new_business_group_id, -1) = nvl(elmt.business_group_id , -1)
3378 AND NVL(rcd.legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3379 AND NVL(elmt.legislation_code,'~NVL~')= NVL(l_legislation_code,'~NVL~');
3380
3381 -- this select get the element in advance condition
3382
3383 SELECT ext_data_elmt_in_rcd_id
3384 INTO l_cond_ext_data_elmt_in_rcd_id
3385 FROM ben_ext_data_elmt_in_rcd
3386 WHERE ext_rcd_id = l_ext_rcd_id
3387 AND ext_data_elmt_id = l_ext_data_elmt_id
3388 AND nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)
3389 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
3390
3391
3392 EXCEPTION
3393 WHEN NO_DATA_FOUND THEN
3394 RAISE;
3395 END;
3396 end if ;
3397
3398
3399 BEGIN
3400 --- aded by tilek
3401 if p_file_name is not null then
3402 l_ext_data_elmt_id := null ;
3403 l_ext_data_elmt_in_rcd_id := null ;
3404
3405 SELECT 'Y'
3406 INTO l_temp
3407 FROM ben_ext_where_clause
3408 WHERE cond_ext_data_elmt_in_rcd_id = l_cond_ext_data_elmt_in_rcd_id
3409 AND ext_rcd_in_file_id = l_ext_rcd_in_file_id
3410 AND seq_num = p_seq_num
3411 --AND ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id
3412 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3413 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3414
3415
3416 elsif p_record_name is not null then
3417 l_ext_data_elmt_id := null ;
3418 SELECT 'Y'
3419 INTO l_temp
3420 FROM ben_ext_where_clause
3421 WHERE cond_ext_data_elmt_in_rcd_id = l_cond_ext_data_elmt_in_rcd_id
3422 AND seq_num = p_seq_num
3423 AND ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id
3424 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3425 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3426 elsif p_cond_ext_data_elmt_name is not null then
3427
3428 SELECT 'Y'
3429 INTO l_temp
3430 FROM ben_ext_where_clause
3431 WHERE ext_data_elmt_id = l_ext_data_elmt_id
3432 AND cond_ext_data_elmt_id = l_cond_ext_data_elmt_id
3433 AND seq_num = p_seq_num
3434 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3435 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3436
3437
3438 end if ;
3439
3440 EXCEPTION
3441 WHEN NO_DATA_FOUND
3442 THEN
3443
3444
3445 ---- Delete the Where condition if the same se exist with some othere values
3446 --- or the values changed fue to the reordering of element or record
3447
3448 Declare
3449
3450 cursor c_xwc_dup_order (p_ext_rcd_in_file_id number ,
3451 p_ext_data_elmt_in_rcd_id number,
3452 p_ext_data_elmt_id number ) is
3453 SELECT ext_where_clause_id, object_version_number
3454 FROM ben_ext_where_clause xwc
3455 WHERE ( business_group_id is null
3456 or business_group_id = l_new_business_group_id )
3457 and (legislation_code is null
3458 or legislation_code = l_legislation_code )
3459 and (ext_rcd_in_file_id = p_ext_rcd_in_file_id
3460 or p_ext_rcd_in_file_id is null )
3461 and (ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
3462 or p_ext_data_elmt_in_rcd_id is null)
3463 and (ext_data_elmt_id = p_ext_data_elmt_id
3464 or p_ext_data_elmt_id is null)
3465 and seq_num = p_seq_num
3466 ;
3467
3468
3469 l_obj_ver_number number ;
3470 Begin
3471
3472 for i in c_xwc_dup_order ( p_ext_rcd_in_file_id => l_ext_rcd_in_file_id ,
3473 p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id ,
3474 p_ext_data_elmt_id => l_ext_data_elmt_id )
3475 Loop
3476 l_obj_ver_number := i.object_version_number ;
3477 ben_xwc_del.del
3478 (
3479 p_effective_date => trunc(sysdate),
3480 p_ext_where_clause_id => i.ext_where_clause_id ,
3481 p_object_version_number => l_obj_ver_number
3482 ) ;
3483
3484 End Loop ;
3485
3486
3487 end ;
3488
3489
3490
3491
3492
3493 ben_xwc_ins.ins(p_effective_date => l_last_update_date
3494 ,p_ext_where_clause_id => l_ext_where_clause_id
3495 ,p_seq_num => p_seq_num
3496 ,p_oper_cd => p_oper_cd
3497 ,p_val => p_val
3498 ,p_and_or_cd => p_and_or_cd
3499 ,p_ext_data_elmt_id => l_ext_data_elmt_id
3500 ,p_cond_ext_data_elmt_id => l_cond_ext_data_elmt_id
3501 ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
3502 ,p_ext_data_elmt_in_rcd_id => l_ext_data_elmt_in_rcd_id
3503 ,p_cond_ext_data_elmt_in_rcd_id => l_cond_ext_data_elmt_in_rcd_id
3504 ,p_business_group_id => l_new_business_group_id
3505 ,p_legislation_code => l_legislation_code
3506 ,p_last_update_date => l_last_update_date
3507 ,p_creation_date => l_last_update_date
3508 ,p_last_updated_by => l_last_updated_by
3509 ,p_last_update_login => 0
3510 ,p_created_by => l_last_updated_by
3511 ,p_object_version_number => l_object_version_number);
3512 WHEN OTHERS THEN
3513 RAISE;
3514 END;
3515 hr_utility.set_location(' Leaving ' || l_proc, 10);
3516 END load_ext_where_clause;
3517
3518
3519
3520 PROCEDURE load_incl_chgs(p_data_elmt_name IN VARCHAR2 DEFAULT NULL
3521 ,p_record_name IN VARCHAR2
3522 ,p_file_name IN VARCHAR2 DEFAULT NULL
3523 ,p_chg_evt_cd IN VARCHAR2
3524 ,p_owner IN VARCHAR2
3525 ,p_last_update_date IN VARCHAR2
3526 ,p_legislation_code IN VARCHAR2
3527 ,p_business_group in VARCHAR2
3528 ,p_chg_evt_source IN VARCHAR2 DEFAULT NULL
3529 ) IS
3530
3531
3532 --
3533 cursor cw (c_code varchar2)
3534 is select 'x'
3535 from hr_lookups
3536 where lookup_type = 'BEN_EXT_CHG_EVT'
3537 and lookup_code = c_code
3538 and enabled_flag = 'Y'
3539 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
3540 and nvl(end_date_active, trunc(sysdate))
3541 ;
3542
3543 --
3544
3545 l_ext_file_id NUMBER;
3546 l_ext_rcd_id NUMBER;
3547 l_ext_data_elmt_id NUMBER;
3548 l_ext_rcd_in_file_id NUMBER;
3549 l_ext_data_elmt_in_rcd_id NUMBER;
3550 l_ext_incl_chg_id NUMBER;
3551 l_object_version_number NUMBER;
3552 l_legislation_code VARCHAR2(240) := p_legislation_code; -- utf8
3553 l_temp VARCHAR2(1);
3554 l_last_update_date DATE;
3555 l_last_updated_by NUMBER;
3556 l_proc varchar2(100) := 'BEN_EXT_SEED.load_incl_chgs' ;
3557 l_new_business_group_id number ;
3558 l_chg_evt_cd varchar2(80) ;
3559 BEGIN
3560 hr_utility.set_location(' Entering ' || l_proc, 10);
3561 l_chg_evt_cd := p_chg_evt_cd ;
3562 get_who_values(p_owner => p_owner
3563 ,p_last_update_vc => p_last_update_date
3564 ,p_last_update_date => l_last_update_date
3565 ,p_last_updated_by => l_last_updated_by
3566 ,p_legislation_code => l_legislation_code
3567 ,p_business_group => p_business_group
3568 ,p_business_group_id => l_new_business_group_id );
3569 BEGIN
3570 SELECT ext_rcd_id
3571 INTO l_ext_rcd_id
3572 FROM ben_ext_rcd
3573 WHERE name = p_record_name
3574 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3575 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
3576 EXCEPTION
3577 WHEN NO_DATA_FOUND THEN
3578 RAISE;
3579 END;
3580
3581 IF p_file_name IS NOT NULL THEN
3582 BEGIN
3583 SELECT ext_file_id
3584 INTO l_ext_file_id
3585 FROM ben_ext_file
3586 WHERE name = p_file_name
3587 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
3588 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
3589 EXCEPTION
3590 WHEN NO_DATA_FOUND THEN
3591 RAISE;
3592 END;
3593 BEGIN
3594 SELECT ext_rcd_in_file_id
3595 INTO l_ext_rcd_in_file_id
3596 FROM ben_ext_rcd_in_file
3597 WHERE ext_file_id = l_ext_file_id
3598 AND ext_rcd_id = l_ext_rcd_id
3599 AND NVL(legislation_code,'~NULL~')= NVL(l_legislation_code,'~NULL~')
3600 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1) ;
3601 EXCEPTION
3602 WHEN NO_DATA_FOUND THEN
3603 RAISE;
3604 END;
3605 ELSE
3606 l_ext_file_id := NULL;
3607 l_ext_rcd_in_file_id := NULL;
3608 END IF;
3609
3610 if P_DATA_ELMT_NAME is not null then
3611
3612 BEGIN
3613 SELECT ext_data_elmt_id
3614 INTO l_ext_data_elmt_id
3615 FROM ben_ext_data_elmt
3616 WHERE name = p_data_elmt_name
3617 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
3618 ( p_business_group is null and business_group_id is null ) )
3619 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3620 AND NVL(legislation_code,'~NULL~')= NVL(l_legislation_code,'~NULL~');
3621 EXCEPTION
3622 WHEN NO_DATA_FOUND THEN
3623 RAISE;
3624 END;
3625 BEGIN
3626 SELECT ext_data_elmt_in_rcd_id
3627 INTO l_ext_data_elmt_in_rcd_id
3628 FROM ben_ext_data_elmt_in_rcd
3629 WHERE ext_rcd_id = l_ext_rcd_id
3630 AND ext_data_elmt_id = l_ext_data_elmt_id
3631 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3632 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
3633 EXCEPTION
3634 WHEN NO_DATA_FOUND THEN
3635 RAISE;
3636 END;
3637 ELSE
3638 l_ext_data_elmt_id := null ;
3639 l_ext_data_elmt_in_rcd_id := null ;
3640 END IF ;
3641
3642 IF L_CHG_EVT_CD is not null then
3643 if p_chg_evt_source = 'PAY' then
3644 l_chg_evt_cd := set_chg_evt_cd(l_CHG_EVT_CD,p_chg_evt_source,l_new_business_group_id) ;
3645 if l_chg_evt_cd = p_chg_evt_cd then
3646 write_err
3647 (p_err_num => null,
3648 p_err_msg => 'Advance Condition Change Event :'|| P_CHG_EVT_CD || ' Not Enabled ' ,
3649 p_typ_cd => 'E' ,
3650 p_business_group_id => l_new_business_group_id
3651 );
3652 return ;
3653 end if ;
3654 else
3655 -- make sure the cahnge evt code is enabled
3656 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
3657 open cw(L_CHG_EVT_CD) ;
3658 fetch cw into l_temp ;
3659 if cw%notfound then
3660 close cw ;
3661 write_err
3662 (p_err_num => null,
3663 p_err_msg => 'Advance Condition Change Event :'|| P_CHG_EVT_CD || ' Not Enabled ' ,
3664 p_typ_cd => 'E' ,
3665 p_business_group_id => l_new_business_group_id
3666 );
3667 return ;
3668 end if ;
3669 close cw ;
3670 end if ;
3671 end if ;
3672 --
3673
3674 BEGIN
3675 --rpinjala
3676 SELECT 'Y'
3677 INTO l_temp
3678 FROM ben_ext_incl_chg
3679 WHERE (l_ext_rcd_in_file_id IS NULL OR
3680 ext_rcd_in_file_id = l_ext_rcd_in_file_id)
3681 AND (l_ext_data_elmt_in_rcd_id IS NULL OR
3682 ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id)
3683 AND chg_evt_cd = l_chg_evt_cd
3684 AND NVL(l_new_business_group_id, -1) = NVL(business_group_id , -1)
3685 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3686 --rpinjala
3687
3688 EXCEPTION
3689 WHEN TOO_MANY_ROWS then
3690 declare
3691 cursor c_incl is
3692 SELECT ext_incl_chg_id , object_version_number
3693 FROM ben_ext_incl_chg
3694 WHERE (l_ext_rcd_in_file_id IS NULL OR
3695 ext_rcd_in_file_id = l_ext_rcd_in_file_id)
3696 AND (l_ext_data_elmt_in_rcd_id IS NULL OR
3697 ext_data_elmt_in_rcd_id = l_ext_data_elmt_in_rcd_id)
3698 AND chg_evt_cd = l_chg_evt_cd
3699 AND NVL(l_new_business_group_id, -1) = NVL(business_group_id , -1)
3700 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
3701
3702 begin
3703 for i in c_incl loop
3704 l_object_version_number := i.object_version_number ;
3705 ben_xic_del.del(
3706 p_effective_date => l_last_update_date,
3707 p_ext_incl_chg_id => i.ext_incl_chg_id,
3708 p_object_version_number => l_object_version_number
3709 );
3710
3711 end loop ;
3712
3713 l_object_version_number := null ;
3714 hr_utility.set_location('calling ins for ' || l_chg_evt_cd , 10);
3715 ben_xic_ins.ins(p_effective_date => l_last_update_date
3716 ,p_ext_incl_chg_id => l_ext_incl_chg_id
3717 ,p_chg_evt_cd => l_chg_evt_cd
3718 ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
3719 ,p_ext_data_elmt_in_rcd_id=> l_ext_data_elmt_in_rcd_id
3720 ,p_business_group_id => l_new_business_group_id
3721 ,p_legislation_code => l_legislation_code
3722 ,p_last_update_date => l_last_update_date
3723 ,p_creation_date => l_last_update_date
3724 ,p_last_updated_by => l_last_updated_by
3725 ,p_last_update_login => 0
3726 ,p_created_by => l_last_updated_by
3727 ,p_object_version_number => l_object_version_number
3728 ,p_chg_evt_source => p_chg_evt_source );
3729
3730 end ;
3731
3732
3733 WHEN NO_DATA_FOUND THEN
3734 hr_utility.set_location('calling ins for ' || l_chg_evt_cd , 10);
3735 ben_xic_ins.ins(p_effective_date => l_last_update_date
3736 ,p_ext_incl_chg_id => l_ext_incl_chg_id
3737 ,p_chg_evt_cd => l_chg_evt_cd
3738 ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
3739 ,p_ext_data_elmt_in_rcd_id=> l_ext_data_elmt_in_rcd_id
3740 ,p_business_group_id => l_new_business_group_id
3741 ,p_legislation_code => l_legislation_code
3742 ,p_last_update_date => l_last_update_date
3743 ,p_creation_date => l_last_update_date
3744 ,p_last_updated_by => l_last_updated_by
3745 ,p_last_update_login => 0
3746 ,p_created_by => l_last_updated_by
3747 ,p_object_version_number => l_object_version_number
3748 ,p_chg_evt_source => p_chg_evt_source );
3749 WHEN OTHERS THEN
3750 RAISE;
3751 END;
3752 --ELSIF p_chg_evt_source = 'PAY' then
3753 -- we can not support payroll cahnge evnt logs not
3754 -- we should download the code for extract event and name for
3755 -- payroll process, till we determine the download part we dont support pay change event group - tilak
3756 Return ;
3757
3758 --END IF ;
3759
3760
3761 END IF ;
3762 hr_utility.set_location(' Leaving ' || l_proc, 10);
3763
3764 END load_incl_chgs;
3765
3766
3767
3768
3769 PROCEDURE load_profile(p_profile_name IN VARCHAR2
3770 ,p_owner IN VARCHAR2
3771 ,p_last_update_date IN VARCHAR2
3772 ,p_legislation_code IN VARCHAR2
3773 ,p_business_group in VARCHAR2
3774 ,p_ext_global_flag in VARCHAR2 default 'N'
3775 ) IS
3776 --
3777 l_ext_prfl_id NUMBER;
3778 l_object_version_number NUMBER;
3779 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
3780 l_temp VARCHAR2(1);
3781 l_last_update_date DATE;
3782 l_last_updated_by NUMBER;
3783 l_new_business_group_id number ;
3784 l_proc varchar2(100) := 'BEN_EXT_SEED.load_profile' ;
3785 BEGIN
3786
3787 hr_utility.set_location(' Entering ' || l_proc, 10);
3788 get_who_values(p_owner => p_owner
3789 ,p_last_update_vc => p_last_update_date
3790 ,p_last_update_date => l_last_update_date
3791 ,p_last_updated_by => l_last_updated_by
3792 ,p_legislation_code => l_legislation_code
3793 ,p_business_group => p_business_group
3794 ,p_business_group_id => l_new_business_group_id );
3795
3796 BEGIN
3797 SELECT ext_crit_prfl_id
3798 INTO l_ext_prfl_id
3799 FROM ben_ext_crit_prfl
3800 WHERE name = p_profile_name
3801 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3802 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
3803 EXCEPTION
3804 WHEN NO_DATA_FOUND THEN
3805 ben_xcr_ins.ins(p_ext_crit_prfl_id => l_ext_prfl_id
3806 ,p_name => p_profile_name
3807 ,p_business_group_id => l_new_business_group_id
3808 ,p_legislation_code => l_legislation_code
3809 ,p_last_update_date => l_last_update_date
3810 ,p_creation_date => l_last_update_date
3811 ,p_last_update_login => 0
3812 ,p_created_by => l_last_updated_by
3813 ,p_last_updated_by => l_last_updated_by
3814 ,p_ext_global_flag => nvl(p_ext_global_flag,'N')
3815 ,p_object_version_number => l_object_version_number
3816 );
3817 WHEN OTHERS THEN
3818 RAISE;
3819 END;
3820 --- delete the advace citeria and cmbn value
3821 delete_crit_adv_conditon
3822 (p_ext_crit_prfl_id => l_ext_prfl_id ) ;
3823 hr_utility.set_location(' Leaving ' || l_proc, 10);
3824 END load_profile;
3825
3826
3827 PROCEDURE load_criteria_type(p_profile_name IN VARCHAR2
3828 ,p_type_code IN VARCHAR2
3829 ,p_owner IN VARCHAR2
3830 ,p_last_update_date IN VARCHAR2
3831 ,p_crit_typ_cd IN VARCHAR2
3832 ,p_excld_flag IN VARCHAR2
3833 ,p_legislation_code IN VARCHAR2
3834 ,p_business_group IN VARCHAR2
3835 ) IS
3836 --
3837 l_ext_prfl_id NUMBER;
3838 l_ext_crit_typ_id NUMBER;
3839 l_object_version_number NUMBER;
3840 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
3841 l_temp VARCHAR2(1);
3842 l_last_update_date DATE;
3843 l_last_updated_by NUMBER;
3844 l_new_business_group_id number ;
3845 l_proc varchar2(100) := 'BEN_EXT_SEED.load_criteria_type' ;
3846 BEGIN
3847 hr_utility.set_location(' Entering ' || l_proc, 10);
3848 --- for advance criteria once the issues is fixed remove the condition
3849 /*
3850 if p_type_code = 'ADV' then
3851 write_err
3852 (p_err_num => null,
3853 p_err_msg => 'Advance Criteria is not uploaded' ,
3854 p_typ_cd => 'W' ,
3855 p_business_group_id => l_new_business_group_id
3856 );
3857 return ;
3858 end if ;
3859 */
3860
3861 ---
3862 get_who_values(p_owner => p_owner
3863 ,p_last_update_vc => p_last_update_date
3864 ,p_last_update_date => l_last_update_date
3865 ,p_last_updated_by => l_last_updated_by
3866 ,p_legislation_code => l_legislation_code
3867 ,p_business_group => p_business_group
3868 ,p_business_group_id => l_new_business_group_id );
3869
3870 BEGIN
3871 SELECT ext_crit_prfl_id
3872 INTO l_ext_prfl_id
3873 FROM ben_ext_crit_prfl
3874 WHERE name = p_profile_name
3875 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3876 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
3877 EXCEPTION
3878 WHEN NO_DATA_FOUND THEN
3879 RAISE;
3880 END;
3881 BEGIN
3882 SELECT 'Y'
3883 INTO l_temp
3884 FROM ben_ext_crit_typ
3885 WHERE ext_crit_prfl_id = l_ext_prfl_id
3886 AND crit_typ_cd = p_type_code
3887 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
3888 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
3889 EXCEPTION
3890 WHEN NO_DATA_FOUND THEN
3891 ben_xct_ins.ins(p_effective_date => l_last_update_date
3892 ,p_ext_crit_typ_id => l_ext_crit_typ_id
3893 ,p_crit_typ_cd => p_type_code
3894 ,p_excld_flag => p_excld_flag
3895 ,p_ext_crit_prfl_id => l_ext_prfl_id
3896 ,p_business_group_id => l_new_business_group_id
3897 ,p_legislation_code => l_legislation_code
3898 ,p_last_update_date => l_last_update_date
3899 ,p_creation_date => l_last_update_date
3900 ,p_last_update_login => 0
3901 ,p_created_by => l_last_updated_by
3902 ,p_last_updated_by => l_last_updated_by
3903 ,p_object_version_number => l_object_version_number);
3904 WHEN OTHERS THEN
3905 RAISE;
3906 END;
3907 hr_utility.set_location(' Leaving ' || l_proc, 10);
3908 END load_criteria_type;
3909 --
3910 PROCEDURE load_criteria_val(p_profile_name IN VARCHAR2
3911 ,p_type_code IN VARCHAR2
3912 ,p_val IN VARCHAR2
3913 ,p_owner IN VARCHAR2
3914 ,p_last_update_date IN VARCHAR2
3915 ,p_val2 IN VARCHAR2
3916 ,p_legislation_code IN VARCHAR2
3917 ,p_business_group IN VARCHAR2
3918 ,p_ext_crit_val_id in varchar2 default null
3919 ,p_lookup_code1 in varchar2 default null
3920 ,p_lookup_code2 in varchar2 default null
3921 ) IS
3922 --
3923 cursor cw (c_code varchar2)
3924 is select 'x'
3925 from hr_lookups
3926 where lookup_type = 'BEN_EXT_CHG_EVT'
3927 and lookup_code = c_code
3928 and enabled_flag = 'Y'
3929 and trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
3930 and nvl(end_date_active, trunc(sysdate))
3931 ;
3932
3933 --
3934 l_ext_prfl_id NUMBER;
3935 l_ext_crit_typ_id NUMBER;
3936 l_ext_crit_val_id NUMBER;
3937 l_object_version_number NUMBER;
3938 l_legislation_code VARCHAR2(240) := p_legislation_code; --utf8
3939 l_temp VARCHAR2(1);
3940 l_code VARCHAR2(30);
3941 l_dummy_code VARCHAR2(30);
3942 l_meaning VARCHAR2(2000);
3943 l_meaning2 VARCHAR2(2000);
3944 l_last_update_date DATE;
3945 l_last_updated_by NUMBER;
3946 l_value VARCHAR2(200);
3947 l_value2 VARCHAR2(200);
3948 l_new_business_group_id number ;
3949 l_proc varchar2(100) := 'BEN_EXT_SEED.load_criteria_val' ;
3950 BEGIN
3951 --- for advance criteria once the issues is fixed remove the condition
3952 /*
3953 if p_type_code = 'ADV' then
3954 return ;
3955 end if ;
3956 */
3957 hr_utility.set_location(' Entering ' || l_proc, 10);
3958
3959 get_who_values(p_owner => p_owner
3960 ,p_last_update_vc => p_last_update_date
3961 ,p_last_update_date => l_last_update_date
3962 ,p_last_updated_by => l_last_updated_by
3963 ,p_legislation_code => l_legislation_code
3964 ,p_business_group => p_business_group
3965 ,p_business_group_id => l_new_business_group_id );
3966
3967 l_code := p_type_code;
3968 l_meaning2 := p_val2;
3969
3970 -- if the code is comming as param , make sure the lookup exisit
3971 if p_lookup_code2 is not null then
3972 l_value2 := ben_extract_seed.get_lookup_code (l_code,p_lookup_code2 ,'VAL_2',null) ;
3973 end if ;
3974
3975 --- if the code is null or the code is not null and not exist get the code from name
3976 if l_value2 is null or p_lookup_code2 is null then
3977 l_value2 := ben_extract_seed.decode_value(l_code,l_meaning2,'VAL_2',null) ;
3978 end if ;
3979
3980 l_meaning := p_val;
3981
3982 if p_lookup_code1 is not null then
3983 l_value := ben_extract_seed.get_lookup_code (l_code,p_lookup_code1 ,'VAL_1',null) ;
3984 end if ;
3985
3986
3987
3988 if l_value is null or p_lookup_code1 is null then
3989 l_value := ben_extract_seed.decode_value(l_code,l_meaning,'VAL_1',l_meaning2) ;
3990 end if ;
3991
3992 --- since we are changing the meaning of TDRASG in passor make sure the lookup code
3993 --- ised even if the name is null
3994
3995 if l_value is NULL and l_code = 'PASOR' and l_meaning = 'Today Or Terminated Assignment End Date' then
3996 l_value := 'TDRASG' ;
3997 end if ;
3998
3999 if l_value is NULL THEN
4000 -- if the concurrent manger does the job log the message
4001 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
4002 write_err
4003 (p_err_num => null,
4004 p_err_msg => 'Criteria value not found : '||hr_general.decode_lookup('BEN_EXT_CRIT_TYP',l_code )||' : '||l_meaning,
4005 p_typ_cd => 'E' ,
4006 p_business_group_id => l_new_business_group_id
4007 );
4008 return ;
4009 else
4010 raise_application_error(-20001,'Criteria value not found : '||hr_general.decode_lookup('BEN_EXT_CRIT_TYP',l_code)||' : '
4011 ||l_meaning||':');
4012 end if ;
4013 END IF;
4014
4015
4016 -- if the type code is change event then make sure the change event is valid for business group
4017 if p_type_code = 'CCE' then
4018 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
4019 open cw(l_value) ;
4020 fetch cw into l_temp ;
4021 if cw%notfound then
4022 close cw ;
4023 write_err
4024 (p_err_num => null,
4025 p_err_msg => 'Criteria Change Event Code :'|| l_meaning || ' Not Enabled ' ,
4026 p_typ_cd => 'E' ,
4027 p_business_group_id => l_new_business_group_id
4028 );
4029 return ;
4030 end if ;
4031 close cw ;
4032 end if ;
4033 end if ;
4034
4035 BEGIN
4036 SELECT ext_crit_prfl_id
4037 INTO l_ext_prfl_id
4038 FROM ben_ext_crit_prfl
4039 WHERE name = p_profile_name
4040 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4041 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4042 EXCEPTION
4043 WHEN NO_DATA_FOUND THEN
4044 RAISE;
4045 END;
4046 BEGIN
4047 SELECT ext_crit_typ_id
4048 INTO l_ext_crit_typ_id
4049 FROM ben_ext_crit_typ
4050 WHERE ext_crit_prfl_id = l_ext_prfl_id
4051 AND crit_typ_cd = p_TYPE_CODE
4052 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4053 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4054 EXCEPTION
4055 WHEN NO_DATA_FOUND THEN
4056 RAISE;
4057 END;
4058 BEGIN
4059 if p_type_code <> 'ADV' then
4060 SELECT 'Y'
4061 INTO l_temp
4062 FROM ben_ext_crit_val
4063 WHERE ext_crit_typ_id = l_ext_crit_typ_id
4064 AND val_1 = l_value
4065 AND nvl(val_2,'~NVL~') = NVL(l_value2,'~NVL~')
4066 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4067 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
4068 ;
4069 else
4070 -- there is a possibility of more then once row with same value in adv condition
4071 Declare
4072
4073 cursor c1 is
4074 SELECT 'Y'
4075 FROM ben_ext_crit_val
4076 WHERE ext_crit_typ_id = l_ext_crit_typ_id
4077 AND val_1 = l_value
4078 AND nvl(val_2,'~NVL~') = NVL(l_value2,'~NVL~')
4079 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4080 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~')
4081 and val_1 = p_val ;
4082 Begin
4083
4084 open c1 ;
4085 fetch c1 into l_temp ;
4086 if c1%notFound then
4087 close c1;
4088 Raise NO_DATA_FOUND ;
4089 end if ;
4090 close c1;
4091
4092 -- when the type is adv validate the old id is exist
4093 -- if not creat a row
4094 -- this has to be revisted when the adv condtion update is fixed
4095 if p_type_code = 'ADV' then
4096 if nvl(get_adv_cond_cmbn( p_old_ext_crit_val_id => to_number(p_ext_crit_val_id)),0) = 0 then
4097 Raise NO_DATA_FOUND ;
4098 end if ;
4099 end if ;
4100 end ;
4101 end if ;
4102
4103 EXCEPTION
4104 WHEN NO_DATA_FOUND THEN
4105 ben_xcv_ins.ins(p_effective_date => trunc(sysdate)
4106 ,p_ext_crit_val_id => l_ext_crit_val_id
4107 ,p_val_1 => l_value
4108 ,p_val_2 => l_value2
4109 ,p_ext_crit_typ_id => l_ext_crit_typ_id
4110 ,p_business_group_id => l_new_business_group_id
4111 ,p_legislation_code => l_legislation_code
4112 ,p_last_update_date => l_last_update_date
4113 ,p_creation_date => l_last_update_date
4114 ,p_last_update_login => 0
4115 ,p_created_by => l_last_updated_by
4116 ,p_last_updated_by => l_last_updated_by
4117 ,p_object_version_number => l_object_version_number);
4118
4119 --- for advance condition it is allways create
4120 --- so set the old value and new value
4121 if p_type_code = 'ADV' then
4122 set_adv_cond_cmbn
4123 ( p_old_ext_crit_val_id => to_number(p_ext_crit_val_id) ,
4124 p_new_ext_crit_val_id => l_ext_crit_val_id ) ;
4125 end if ;
4126
4127 WHEN OTHERS THEN
4128 RAISE;
4129 END;
4130 hr_utility.set_location(' Leaving ' || l_proc, 10);
4131 end load_criteria_val;
4132 --
4133 PROCEDURE load_combination(p_profile_name IN VARCHAR2
4134 ,p_type_code IN VARCHAR2
4135 ,p_val IN VARCHAR2
4136 ,p_val_2 IN VARCHAR2
4137 ,p_crit_typ_cd IN VARCHAR2
4138 ,p_oper_cd IN VARCHAR2
4139 ,p_owner IN VARCHAR2
4140 ,p_last_update_date IN VARCHAR2
4141 ,p_legislation_code IN VARCHAR2
4142 ,p_business_group in VARCHAR2
4143 ,p_ext_crit_val_id in varchar2 default null
4144 ,p_lookup_code1 in varchar2 default null
4145 ,p_lookup_code2 in varchar2 default null
4146 ) IS
4147 --
4148 l_ext_prfl_id NUMBER;
4149 l_ext_crit_typ_id NUMBER;
4150 l_ext_crit_val_id NUMBER;
4151 l_ext_crit_cmbn_id NUMBER;
4152 l_object_version_number NUMBER;
4153 l_legislation_code VARCHAR2(240) := p_legislation_code;
4154 l_temp VARCHAR2(1);
4155 l_last_update_date DATE;
4156 l_last_updated_by NUMBER;
4157 l_new_business_group_id number ;
4158 l_code VARCHAR2(20000) ;
4159 l_val_1 VARCHAR2(20000) ;
4160 l_val_2 VARCHAR2(20000) ;
4161 l_meaning2 VARCHAR2(20000) ;
4162 l_meaning VARCHAR2(20000) ;
4163
4164 l_proc varchar2(100) := 'BEN_EXT_SEED.load_combination' ;
4165 BEGIN
4166
4167 --- for advance criteria once the issues is fixed remove the condition
4168 /*
4169 if p_type_code = 'ADV' then
4170 return ;
4171 end if ;
4172 */
4173 hr_utility.set_location(' Entering ' || l_proc, 10);
4174 ---
4175 get_who_values(p_owner => p_owner
4176 ,p_last_update_vc => p_last_update_date
4177 ,p_last_update_date => l_last_update_date
4178 ,p_last_updated_by => l_last_updated_by
4179 ,p_legislation_code => l_legislation_code
4180 ,p_business_group => p_business_group
4181 ,p_business_group_id => l_new_business_group_id );
4182
4183
4184
4185 l_code := p_crit_typ_cd;
4186 l_meaning2 := p_val_2;
4187
4188
4189
4190 -- if the code is comming as param , make sure the lookup exisit
4191 if p_lookup_code2 is not null then
4192 l_val_2 := ben_extract_seed.get_lookup_code (l_code,p_lookup_code2 ,'VAL_2',null) ;
4193 end if ;
4194
4195 if l_val_2 is null or p_lookup_code2 is null then
4196 l_val_2 := ben_extract_seed.decode_value(l_code,l_meaning2,'VAL_2',null) ;
4197 end if ;
4198
4199
4200
4201 l_meaning := p_val;
4202
4203 if p_lookup_code1 is not null then
4204 l_val_1 := ben_extract_seed.get_lookup_code (l_code,p_lookup_code1 ,'VAL_1',null) ;
4205 end if ;
4206
4207 if l_val_1 is null or p_lookup_code1 is null then
4208 l_val_1 := ben_extract_seed.decode_value(l_code,l_meaning,'VAL_1',l_meaning2) ;
4209 end if ;
4210
4211 if l_val_1 is NULL THEN
4212 -- if the concurrent manger does the job log the message
4213 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
4214 write_err
4215 (p_err_num => null,
4216 p_err_msg => 'Criteria value not found : '||hr_general.decode_lookup('BEN_EXT_CRIT_TYP',l_code)||' : '||l_meaning,
4217 p_typ_cd => 'E' ,
4218 p_business_group_id => l_new_business_group_id
4219 );
4220 return ;
4221 else
4222 raise_application_error(-20001,'Criteria value not found : '||hr_general.decode_lookup('BEN_EXT_CRIT_TYP',l_code)||' : '
4223 ||l_meaning||':');
4224 end if ;
4225
4226 END IF;
4227
4228
4229 BEGIN
4230 SELECT ext_crit_prfl_id
4231 INTO l_ext_prfl_id
4232 FROM ben_ext_crit_prfl
4233 WHERE name = p_profile_name
4234 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4235 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4236 EXCEPTION
4237 WHEN NO_DATA_FOUND THEN
4238 RAISE;
4239 END;
4240 BEGIN
4241
4242 -- if more then one criteria defined how do we differentiat them - tilak
4243 SELECT ext_crit_typ_id
4244 INTO l_ext_crit_typ_id
4245 FROM ben_ext_crit_typ
4246 WHERE ext_crit_prfl_id = l_ext_prfl_id
4247 AND crit_typ_cd = p_type_code
4248 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4249 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4250
4251
4252 EXCEPTION
4253 WHEN NO_DATA_FOUND THEN
4254 RAISE;
4255 END;
4256 BEGIN
4257 /*
4258 -- how do we differentiate whne more then one criteria val - tilak
4259 SELECT ext_crit_val_id
4260 INTO l_ext_crit_val_id
4261 FROM ben_ext_crit_val
4262 WHERE ext_crit_typ_id = l_ext_crit_typ_id
4263 --AND val_1 = p_val
4264 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4265 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4266 */
4267
4268
4269 l_ext_crit_val_id := get_adv_cond_cmbn
4270 ( p_old_ext_crit_val_id => to_number(p_ext_crit_val_id)
4271 ) ;
4272 if l_ext_crit_val_id is null then
4273 return ;
4274 end if ;
4275
4276 EXCEPTION
4277 WHEN NO_DATA_FOUND THEN
4278 RAISE;
4279 END;
4280 BEGIN
4281 SELECT 'Y'
4282 INTO l_temp
4283 FROM ben_ext_crit_cmbn
4284 WHERE ext_crit_val_id = l_ext_crit_val_id
4285 AND crit_typ_cd = p_crit_typ_cd
4286 and oper_cd = p_oper_cd
4287 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4288 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4289 EXCEPTION
4290 WHEN NO_DATA_FOUND THEN
4291 ben_xcc_ins.ins(p_effective_date => l_last_update_date
4292 ,p_ext_crit_cmbn_id => l_ext_crit_cmbn_id
4293 ,p_ext_crit_val_id => l_ext_crit_val_id
4294 ,p_crit_typ_cd => p_crit_typ_cd
4295 ,p_oper_cd => p_oper_cd
4296 ,p_val_1 => l_val_1
4297 ,p_val_2 => l_val_2
4298 ,p_business_group_id => l_new_business_group_id
4299 ,p_legislation_code => l_legislation_code
4300 ,p_last_update_date => l_last_update_date
4301 ,p_creation_date => l_last_update_date
4302 ,p_last_update_login => 0
4303 ,p_created_by => l_last_updated_by
4304 ,p_last_updated_by => l_last_updated_by
4305 ,p_object_version_number => l_object_version_number);
4306 WHEN OTHERS THEN
4307 RAISE;
4308 END;
4309 hr_utility.set_location(' Leaving ' || l_proc, 10);
4310 END load_combination;
4311 --
4312 PROCEDURE load_definition(p_definition_name IN VARCHAR2
4313 ,p_file_name IN VARCHAR2
4314 ,p_profile_name IN VARCHAR2
4315 ,p_owner IN VARCHAR2
4316 ,p_last_update_date IN VARCHAR2
4317 ,p_kickoff_wrt_prc_flag IN VARCHAR2
4318 ,p_apnd_rqst_id_flag IN VARCHAR2
4319 ,p_prmy_sort_cd IN VARCHAR2
4320 ,p_scnd_sort_cd IN VARCHAR2
4321 ,p_strt_dt IN VARCHAR2
4322 ,p_end_dt IN VARCHAR2
4323 ,p_spcl_hndl_flag IN VARCHAR2
4324 ,p_upd_cm_sent_dt_flag IN VARCHAR2
4325 ,p_use_eff_dt_for_chgs_flag IN VARCHAR2
4326 ,p_data_typ_cd IN VARCHAR2
4327 ,p_ext_typ_cd IN VARCHAR2
4328 ,p_drctry_name IN VARCHAR2
4329 ,p_output_name IN VARCHAR2
4330 ,p_post_processing_rule IN VARCHAR2
4331 ,p_legislation_code IN VARCHAR2
4332 ,p_business_group IN VARCHAR2
4333 ,p_xml_tag_name in VARCHAR2
4334 ,p_output_type in VARCHAR2
4335 ,p_xdo_template_name in VARCHAR2
4336 ,p_ext_global_flag in VARCHAR2 default 'N'
4337 ,p_cm_display_flag in VARCHAR2 default 'N'
4338 ) IS
4339 --
4340 l_ext_prfl_id NUMBER;
4341 l_ext_file_id NUMBER;
4342 l_ext_dfn_id NUMBER;
4343 l_ext_post_prcs_rl NUMBER;
4344 l_object_version_number NUMBER;
4345 l_legislation_code VARCHAR2(240) := p_legislation_code;
4346 l_temp VARCHAR2(1);
4347 l_last_update_date DATE;
4348 l_last_updated_by NUMBER;
4349 l_new_business_group_id number ;
4350 l_ovn number ;
4351 l_output_type varchar2(30) ;
4352 l_template_id number ;
4353 l_proc varchar2(100) := 'BEN_EXT_SEED.load_definition' ;
4354 BEGIN
4355
4356 hr_utility.set_location(' Entering ' || l_proc, 10);
4357 get_who_values(p_owner => p_owner
4358 ,p_last_update_vc => p_last_update_date
4359 ,p_last_update_date => l_last_update_date
4360 ,p_last_updated_by => l_last_updated_by
4361 ,p_legislation_code => l_legislation_code
4362 ,p_business_group => p_business_group
4363 ,p_business_group_id => l_new_business_group_id );
4364
4365
4366
4367
4368 BEGIN
4369 SELECT ext_file_id
4370 INTO l_ext_file_id
4371 FROM ben_ext_file
4372 WHERE name = p_file_name
4373 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4374 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4375 EXCEPTION
4376 WHEN NO_DATA_FOUND THEN
4377 RAISE;
4378 END;
4379 BEGIN
4380 IF p_post_processing_rule = 'NULL' THEN
4381 l_ext_post_prcs_rl := NULL;
4382 ELSE
4383 SELECT formula_id
4384 INTO l_ext_post_prcs_rl
4385 FROM ff_formulas_f
4386 WHERE formula_name = p_post_processing_rule
4387 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4388 AND trunc(sysdate) BETWEEN effective_start_date and effective_end_date ;
4389 END IF;
4390 EXCEPTION
4391 WHEN NO_DATA_FOUND
4392 THEN
4393 -- if the concurrent manger does the job log the message
4394 if g_business_group_id is not null and fnd_global.conc_request_id <> -1 then
4395 write_err
4396 (p_err_num => null,
4397 p_err_msg => ' No formula of name '||p_post_processing_rule||' Exists for Definition '||p_definition_name ,
4398 p_typ_cd => 'E' ,
4399 p_business_group_id => l_new_business_group_id
4400 );
4401 l_ext_post_prcs_rl := NULL;
4402 else
4403 raise_application_error(-20001,' No formula of name '||p_post_processing_rule||' Exists for Definition '||p_definition_name );
4404 end if ;
4405 END;
4406
4407 l_output_type := p_output_type ;
4408 if P_xdo_template_name is not null then
4409
4410 Declare
4411 cursor c is
4412 select xdo.template_id
4413 from xdo_templates_b xdo
4414 where xdo.template_code = P_xdo_template_name
4415 order by decode(xdo.application_id ,FND_GLOBAL.resp_appl_id,1,2)
4416 ;
4417
4418 Begin
4419 open c ;
4420 fetch c into l_template_id ;
4421 if c%notfound then
4422 l_output_type := null ;
4423 end if ;
4424 close c ;
4425 End ;
4426
4427 end if ;
4428
4429 if p_profile_name is not null then
4430 BEGIN
4431 SELECT ext_crit_prfl_id
4432 INTO l_ext_prfl_id
4433 FROM ben_ext_crit_prfl
4434 WHERE name = p_profile_name
4435 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4436 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4437 EXCEPTION
4438 WHEN NO_DATA_FOUND THEN
4439 RAISE;
4440 END;
4441 end if ;
4442
4443
4444 -- call file update to load group elemt
4445 if g_group_record is not null and g_group_elmt1 is not null then
4446
4447 load_extract_group(p_file_name => p_file_name
4448 ,p_ext_group_record => g_group_record
4449 ,p_ext_group_elmt1 => g_group_elmt1
4450 ,p_ext_group_elmt2 => g_group_elmt2
4451 ,p_owner => p_owner
4452 ,p_last_update_date => p_last_update_date
4453 ,p_legislation_code => p_legislation_code
4454 ,p_business_group => p_business_group
4455 ) ;
4456 g_group_record := null ;
4457 g_group_elmt1 := null ;
4458 end if ;
4459 --
4460
4461 BEGIN
4462 SELECT object_version_number ,ext_dfn_id
4463 INTO l_ovn,l_ext_dfn_id
4464 FROM ben_ext_dfn
4465 WHERE ext_file_id = l_ext_file_id
4466 AND name = p_definition_name
4467 AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4468 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4469 -- dont update for upload from concurrent manager
4470 if ( l_new_business_group_id is null or g_override = 'Y' ) then
4471
4472 ben_xdf_upd.upd (
4473 p_ext_dfn_id => l_ext_dfn_id
4474 ,p_name => p_definition_name
4475 ,p_xml_tag_name => p_xml_tag_name
4476 ,p_data_typ_cd => p_data_typ_cd
4477 ,p_ext_typ_cd => p_ext_typ_cd
4478 ,p_output_name => p_output_name
4479 ,p_output_type => l_output_type
4480 ,p_apnd_rqst_id_flag => p_apnd_rqst_id_flag
4481 ,p_prmy_sort_cd => p_prmy_sort_cd
4482 ,p_scnd_sort_cd => p_scnd_sort_cd
4483 ,p_strt_dt => p_strt_dt
4484 ,p_end_dt => p_end_dt
4485 ,p_ext_crit_prfl_id => l_ext_prfl_id
4486 ,p_ext_file_id => l_ext_file_id
4487 ,p_business_group_id => l_new_business_group_id
4488 ,p_legislation_code => l_legislation_code
4489 ,p_object_version_number => l_ovn
4490 ,p_drctry_name => p_drctry_name
4491 ,p_kickoff_wrt_prc_flag => p_kickoff_wrt_prc_flag
4492 ,p_upd_cm_sent_dt_flag => p_upd_cm_sent_dt_flag
4493 ,p_spcl_hndl_flag => p_spcl_hndl_flag
4494 ,p_use_eff_dt_for_chgs_flag => p_use_eff_dt_for_chgs_flag
4495 ,p_ext_post_prcs_rl => l_ext_post_prcs_rl
4496 ,p_effective_date => trunc(sysdate)
4497 ,p_xdo_template_id => l_template_id
4498 ,p_ext_global_flag => nvl(p_ext_global_flag, 'N')
4499 ,p_cm_display_flag => nvl(p_cm_display_flag, 'N')
4500 );
4501
4502 end if ;
4503
4504
4505 EXCEPTION
4506 WHEN NO_DATA_FOUND THEN
4507 ben_xdf_ins.ins(p_effective_date => trunc(sysdate)
4508 ,p_ext_dfn_id => l_ext_dfn_id
4509 ,p_name => p_definition_name
4510 ,p_ext_crit_prfl_id => l_ext_prfl_id
4511 ,p_ext_file_id => l_ext_file_id
4512 ,p_kickoff_wrt_prc_flag => p_kickoff_wrt_prc_flag
4513 ,p_apnd_rqst_id_flag => p_apnd_rqst_id_flag
4514 ,p_prmy_sort_cd => p_prmy_sort_cd
4515 ,p_scnd_sort_cd => p_scnd_sort_cd
4516 ,p_strt_dt => p_strt_dt
4517 ,p_end_dt => p_end_dt
4518 ,p_spcl_hndl_flag => p_spcl_hndl_flag
4519 ,p_upd_cm_sent_dt_flag => p_upd_cm_sent_dt_flag
4520 ,p_use_eff_dt_for_chgs_flag => p_use_eff_dt_for_chgs_flag
4521 ,p_data_typ_cd => p_data_typ_cd
4522 ,p_ext_typ_cd => p_ext_typ_cd
4523 ,p_drctry_name => p_drctry_name
4524 ,p_output_name => p_output_name
4525 ,p_business_group_id => l_new_business_group_id
4526 ,p_legislation_code => l_legislation_code
4527 ,p_last_update_date => l_last_update_date
4528 ,p_creation_date => l_last_update_date
4529 ,p_last_update_login => 0
4530 ,p_created_by => l_last_updated_by
4531 ,p_last_updated_by => l_last_updated_by
4532 ,p_ext_post_prcs_rl => l_ext_post_prcs_rl
4533 ,p_object_version_number => l_object_version_number
4534 ,p_xml_tag_name => p_xml_tag_name
4535 ,p_output_type => l_output_type
4536 ,p_xdo_template_id => l_template_id
4537 ,p_ext_global_flag => nvl(p_ext_global_flag, 'N')
4538 ,p_cm_display_flag => nvl(p_cm_display_flag, 'N')
4539 );
4540 WHEN OTHERS THEN
4541 RAISE;
4542 END;
4543 hr_utility.set_location(' Leaving ' || l_proc, 10);
4544 END load_definition;
4545 --
4546 PROCEDURE load_decode(p_element_name IN VARCHAR2
4547 ,p_owner IN VARCHAR2
4548 ,p_last_update_date IN VARCHAR2
4549 ,p_val IN VARCHAR2
4550 ,p_dcd_val IN VARCHAR2
4551 ,p_legislation_code IN VARCHAR2
4552 ,p_business_group IN VARCHAR2
4553 ,p_chg_evt_source in VARCHAR2 default null
4554 ) is
4555 l_ext_data_elmt_decd_id NUMBER;
4556 l_ext_data_elmt_id NUMBER;
4557 l_ext_fld_id number ;
4558 l_object_version_number NUMBER;
4559 l_legislation_code VARCHAR2(240) := p_legislation_code;
4560 l_temp VARCHAR2(1);
4561 l_last_update_date DATE;
4562 l_last_updated_by NUMBER;
4563 l_new_business_group_id number ;
4564
4565 cursor c_1 (p_ext_fld_id number ) is
4566 select decd_flag
4567 from ben_ext_fld
4568 where ext_fld_id = p_ext_fld_id ;
4569
4570 begin
4571
4572
4573 get_who_values(p_owner => p_owner
4574 ,p_last_update_vc => p_last_update_date
4575 ,p_last_update_date => l_last_update_date
4576 ,p_last_updated_by => l_last_updated_by
4577 ,p_legislation_code => l_legislation_code
4578 ,p_business_group => p_business_group
4579 ,p_business_group_id => l_new_business_group_id );
4580
4581 BEGIN
4582 SELECT ext_data_elmt_id,ext_fld_id
4583 INTO l_ext_data_elmt_id,l_ext_fld_id
4584 FROM ben_ext_data_elmt
4585 WHERE name = p_element_name
4586 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
4587 ( p_business_group is null and business_group_id is null ) )
4588 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4589 AND NVL(legislation_code,'~NULL~') = NVL(l_legislation_code,'~NULL~');
4590
4591
4592 -- when the ext_fld_id is not null check whether the decd flag is 'Y' id so
4593 -- check whether the decod falg is id (by number ), if so dont insert
4594 -- the decod is a data the id wont match with current environment
4595 if l_ext_fld_id is not null then
4596 open c_1(l_ext_fld_id) ;
4597 fetch c_1 into l_temp ;
4598 close c_1 ;
4599 if nvl(l_temp,'N') = 'Y' then
4600 -- check wheher it is number
4601 begin
4602 -- when the decode can not be inserted then raise the warning
4603 if ( to_number(p_val) ) is not null then
4604 write_err
4605 (p_err_num => null,
4606 p_err_msg => 'Element ' || p_element_name ||' Decode Value ' || p_dcd_val ||' not uploaded' ,
4607 p_typ_cd => 'W' ,
4608 p_business_group_id => l_new_business_group_id
4609 );
4610 return ;
4611 end if ;
4612 exception
4613 when others then null ;
4614 end ;
4615
4616 end if ;
4617 end if ;
4618 l_temp := null ;
4619 --
4620
4621 EXCEPTION
4622 WHEN NO_DATA_FOUND THEN
4623 --RAISE;
4624 raise_application_error(-20001,'Data element '||p_element_name||
4625 ' legislation code '||l_legislation_code);
4626 END;
4627 BEGIN
4628 SELECT 'Y'
4629 INTO l_temp
4630 FROM ben_ext_data_elmt_decd
4631 WHERE ext_data_elmt_id = l_ext_data_elmt_id
4632 AND val = p_val
4633 AND dcd_val = p_dcd_val
4634 AND ( ( nvl(l_new_business_group_id, -1) = nvl(business_group_id , -1)) or
4635 ( p_business_group is null and business_group_id is null ) )
4636 --AND nvl( l_new_business_group_id, -1) = nvl(business_group_id , -1)
4637 AND NVL(legislation_code,'~NVL~') = NVL(l_legislation_code,'~NVL~');
4638 EXCEPTION
4639 WHEN NO_DATA_FOUND THEN
4640
4641 ben_xdd_ins.ins(p_ext_data_elmt_decd_id => l_ext_data_elmt_decd_id
4642 ,p_val => p_val
4643 , p_dcd_val => p_dcd_val
4644 , p_ext_data_elmt_id => l_ext_data_elmt_id
4645 , p_business_group_id => l_new_business_group_id
4646 , p_legislation_code => l_legislation_code
4647 , p_last_update_date => l_last_update_date
4648 , p_creation_date => l_last_update_date
4649 , p_last_updated_by => l_last_updated_by
4650 , p_last_update_login => 0
4651 , p_created_by => l_last_updated_by
4652 , p_object_version_number => l_object_version_number
4653 , p_chg_evt_source => p_chg_evt_source );
4654 END;
4655 end load_decode;
4656
4657
4658
4659 function get_chg_evt_cd (p_CHG_EVT_CD varchar2 ,
4660 p_chg_evt_source varchar2,
4661 p_business_group_id number
4662 ) return varchar2 as
4663
4664 l_return varchar2(250) ;
4665
4666 cursor cep is select event_group_name
4667 from pay_event_groups
4668 where event_group_id = p_CHG_EVT_CD
4669
4670 ;
4671
4672 begin
4673
4674
4675 l_return := p_CHG_EVT_CD ;
4676 if p_chg_evt_source = 'PAY' then
4677 open cep ;
4678 fetch cep into l_return ;
4679 close cep ;
4680 end if ;
4681
4682 if l_return is null then
4683 l_return := p_CHG_EVT_CD ;
4684 end if ;
4685
4686 Return l_return ;
4687 end ;
4688
4689
4690
4691 function set_chg_evt_cd (p_CHG_EVT_CD varchar2 ,
4692 p_chg_evt_source varchar2,
4693 p_business_group_id number
4694 ) return varchar2 as
4695
4696 l_return varchar2(250) ;
4697
4698 cursor cep is select event_group_id
4699 from pay_event_groups
4700 where event_group_name = P_CHG_EVT_CD
4701 and nvl(business_group_id,nvl(p_business_group_id,-1))
4702 = nvl(p_business_group_id,-1)
4703 ;
4704
4705 begin
4706
4707 l_return := p_CHG_EVT_CD ;
4708 if p_chg_evt_source = 'PAY' then
4709 open cep ;
4710 fetch cep into l_return ;
4711 close cep ;
4712 end if ;
4713
4714 if l_return is null then
4715 l_return := p_CHG_EVT_CD ;
4716 end if ;
4717
4718 Return l_return ;
4719 end ;
4720
4721 END ben_extract_seed;
[Home] [Help]