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