[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_UPLOAD_DK
Source
1 PACKAGE BODY ben_dm_upload_dk AS
2 /* $Header: benfdmuddk.pkb 120.0 2006/05/04 04:52:15 nkkrishn noship $ */
3
4 --
5 -- Package Variables
6 --
7 g_package varchar2(33) := 'ben_dm_upload_dk.';
8
9 --
10 -- Function to get target ID for given resolve mapping id.
11 --
12 function get_target_id_for_mapping(p_resolve_mapping_id in NUMBER)
13 return NUMBER is
14
15 l_target_id number;
16
17 begin
18
19 select target_id
20 into l_target_id
21 from ben_dm_resolve_mappings
22 where resolve_mapping_id = p_resolve_mapping_id;
23
24 return l_target_id;
25
26 end;
27
28 --
29 -- DK Resolve for Table BEN_ACTL_PREM_F
30 --
31 procedure get_dk_frm_apr is
32
33 --
34 -- cursor to Fetch the DK for BEN_ACTL_PREM_F.ACTL_PREM_ID.
35 --
36
37 cursor csr_get_dk_apr (c_business_group_id number, c_source_key varchar2) is
38 select actl_prem_id
39 from ben_actl_prem_f apr
40 where business_group_id = c_business_group_id
41 and name = c_source_key;
42
43 cursor csr_get_all_apr is
44 select *
45 from ben_dm_resolve_mappings cm
46 where table_name = 'BEN_ACTL_PREM_F'
47 and target_id is null;
48
49 -- Declare local variables
50 l_proc varchar2(72) := g_package || 'get_dk_frm_apr' ;
51 l_row_fetched boolean := FALSE;
52 l_table_rec csr_get_dk_apr%rowtype;
53 l_table_rec_all csr_get_all_apr%rowtype;
54 l_bg_id number;
55
56 begin
57 --
58 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_ACTL_PREM_F.
59 --
60 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
61 for x in csr_get_all_apr loop
62
63 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
64
65 open csr_get_dk_apr(c_source_key => x.source_key
66 ,c_business_group_id => l_bg_id);
67 fetch csr_get_dk_apr into l_table_rec;
68 if csr_get_dk_apr%notfound then
69 close csr_get_dk_apr;
70 l_row_fetched := FALSE;
71 else
72 l_row_fetched := TRUE;
73 end if;
74 close csr_get_dk_apr;
75 --
76 -- if no row fetched then exit the loop
77 --
78 if not l_row_fetched then
79 -- <<RAISE Some kind of Exception>>
80 null;
81 else
82 --
83 -- Seed DK data into BEN_DM_RESOLVE_MAPPINGS table.
84 --
85 -- ben_dm_utility.message('PARA','(p_dk_apr - ' || l_table_rec.name ||')', 10);
86 ben_dm_data_util.update_pk_mapping
87 (p_resolve_mapping_id => x.resolve_mapping_id
88 ,p_target_id => l_table_rec.actl_prem_id);
89 end if;
90 --
91 end loop;
92 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
93
94 end;
95 --
96 -- DK Resolve For Table BEN_ACTN_TYP
97 --
98 procedure get_dk_frm_eat is
99 --
100 -- cursor to Fetch the DK for BEN_ACTN_TYP.ACTN_TYP_ID
101 --
102 cursor csr_get_dk_eat (c_business_group_id number, c_source_key varchar2) is
103 select actn_typ_id
104 from ben_actn_typ
105 where name = c_source_key
106 and business_group_id = c_business_group_id
107 ;
108
109 cursor csr_get_all_eat is
110 select *
111 from ben_dm_resolve_mappings
112 where table_name = 'BEN_ACTN_TYP'
113 and target_id is null;
114
115 -- Declare local variables
116 l_proc varchar2(72) := g_package || 'get_dk_frm_eat';
117 l_row_fetched boolean := FALSE;
118 l_table_rec csr_get_dk_eat%rowtype;
119 l_table_rec_all csr_get_all_eat%rowtype;
120 l_bg_id number(15);
121
122 begin
123
124 --
125 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_ACTN_TYP
126 --
127 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
128 for x in csr_get_all_eat loop
129
130 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
131 ben_dm_utility.message('INFO','bg : ' || l_bg_id,5) ;
132 open csr_get_dk_eat (c_source_key => x.source_key
133 ,c_business_group_id => l_bg_id);
134 fetch csr_get_dk_eat into l_table_rec;
135 if csr_get_dk_eat%notfound then
136 l_row_fetched := FALSE;
137 else
138 l_row_fetched := TRUE;
139 end if;
140 close csr_get_dk_eat;
141
142 --
143 -- if no row fetched then raise exception
144 --
145 if not l_row_fetched then
146 -- <<RAISE Some kind of Exception>>
147 null;
148 else
149 --
150 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
151 --
152 ben_dm_utility.message('INFO','target : ' || l_table_rec.actn_typ_id,5) ;
153 ben_dm_utility.message('INFO','mapping : ' || x.resolve_mapping_id,5) ;
154
155
156 ben_dm_data_util.update_pk_mapping
157 (p_resolve_mapping_id => x.resolve_mapping_id
158 ,p_target_id => l_table_rec.actn_typ_id);
159
160 end if;
161 --
162 end loop;
163 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
164 end;
165 --
166 -- DK Resolve For Table BEN_ACTY_BASE_RT_F
167 --
168 procedure get_dk_frm_abr is
169 --
170 -- cursor to Fetch the DK for BEN_ACTY_BASE_RT_F.ACTY_BASE_RT_ID
171 --
172 cursor csr_get_dk_abr (c_business_group_id number, c_source_key varchar2) is
173 select acty_base_rt_id
174 from ben_acty_base_rt_f
175 where name = c_source_key
176 and business_group_id = c_business_group_id;
177
178 cursor csr_get_all_abr is
179 select *
180 from ben_dm_resolve_mappings
181 where table_name = 'BEN_ACTY_BASE_RT_F'
182 and target_id is null;
183
184 -- Declare local variables
185 l_proc varchar2(72) := g_package || 'get_dk_frm_abr';
186 l_row_fetched boolean := FALSE;
187 l_table_rec csr_get_dk_abr%rowtype;
188 l_table_rec_all csr_get_all_abr%rowtype;
189 l_bg_id number(15);
190
191 begin
192
193 --
194 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_ACTY_BASE_RT_F
195 --
196 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
197 for x in csr_get_all_abr loop
198 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
199 open csr_get_dk_abr (c_source_key => x.source_key
200 ,c_business_group_id => l_bg_id);
201 fetch csr_get_dk_abr into l_table_rec;
202 if csr_get_dk_abr%notfound then
203 l_row_fetched := FALSE;
204 else
205 l_row_fetched := TRUE;
206 end if;
207 close csr_get_dk_abr;
208
209 --
210 -- if no row fetched then raise exception
211 --
212 if not l_row_fetched then
213 -- <<RAISE Some kind of Exception>>
214 null;
215 else
216 --
217 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
218 --
219 ben_dm_data_util.update_pk_mapping
220 (p_resolve_mapping_id => x.resolve_mapping_id
221 ,p_target_id => l_table_rec.acty_base_rt_id);
222 end if;
223 --
224 end loop;
225 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
226 end;
227 --
228 -- DK Resolve For Table BEN_BENFTS_GRP
229 --
230 procedure get_dk_frm_bng is
231 --
232 -- cursor to Fetch the DK for BEN_BENFTS_GRP.BENFTS_GRP_ID
233 --
234 cursor csr_get_dk_bng (c_business_group_id number, c_source_key varchar2) is
235 select benfts_grp_id
236 from ben_benfts_grp
237 where name = c_source_key
238 ;
239
240 cursor csr_get_all_bng is
241 select *
242 from ben_dm_resolve_mappings
243 where table_name = 'BEN_BENFTS_GRP'
244 and target_id is null;
245
246 -- Declare local variables
247 l_proc varchar2(72) := g_package || 'get_dk_frm_bng';
248 l_row_fetched boolean := FALSE;
249 l_table_rec csr_get_dk_bng%rowtype;
250 l_table_rec_all csr_get_all_bng%rowtype;
251 l_bg_id number(15);
252
253 begin
254
255 --
256 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_BENFTS_GRP
257 --
258 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
259 for x in csr_get_all_bng loop
260 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
261 open csr_get_dk_bng (c_source_key => x.source_key
262 ,c_business_group_id => l_bg_id);
263 fetch csr_get_dk_bng into l_table_rec;
264 if csr_get_dk_bng%notfound then
265 l_row_fetched := FALSE;
266 else
267 l_row_fetched := TRUE;
268 end if;
269 close csr_get_dk_bng;
270
271 --
272 -- if no row fetched then raise exception
273 --
274 if not l_row_fetched then
275 -- <<RAISE Some kind of Exception>>
276 null;
277 else
278 --
279 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
280 --
281 ben_dm_data_util.update_pk_mapping
282 (p_resolve_mapping_id => x.resolve_mapping_id
283 ,p_target_id => l_table_rec.benfts_grp_id);
284 end if;
285 --
286 end loop;
287 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
288 end;
289
290 --
291 -- DK Resolve For Table PAY_ELEMENT_LINKS_F
292 --
293 procedure get_dk_frm_pll is
294 --
295 -- cursor to Fetch the DK for PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID
296 --
297 cursor csr_get_dk_pll (c_business_group_id number, c_source_id number) is
298 select element_Link_id
299 from pay_element_links_f
300 where element_type_id = c_source_id
301 and business_group_id = c_business_group_id;
302
303 cursor csr_get_all_pll is
304 select *
305 from ben_dm_resolve_mappings
306 where table_name = 'PAY_ELEMENT_LINKS_F'
307 and target_id is null;
308
309 -- Declare local variables
310 l_proc varchar2(72) := g_package || 'get_dk_frm_pll';
311 l_row_fetched boolean := FALSE;
312 l_table_rec csr_get_dk_pll%rowtype;
313 l_table_rec_all csr_get_all_pll%rowtype;
314 l_bg_id number(15);
315 l_target_id1 number(15);
316
317 begin
318
319 --
320 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PAY_ELEMENT_LINKS_F
321 --
322 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
323 for x in csr_get_all_pll loop
324 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
325 -- Get element_type_id
326 ben_dm_utility.message('INFO','resolve_mapping_id1 '||x.resolve_mapping_id1,5) ;
327 if x.resolve_mapping_id1 is not null then
328 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
329 end if;
330 ben_dm_utility.message('INFO','l_target_id1 '||l_target_id1,5) ;
331 ben_dm_utility.message('INFO','l_bg_id '||l_bg_id,5) ;
332
333 open csr_get_dk_pll (c_source_id => l_target_id1
334 ,c_business_group_id => l_bg_id);
335 fetch csr_get_dk_pll into l_table_rec;
336 if csr_get_dk_pll%notfound then
337 l_row_fetched := FALSE;
338 else
339 l_row_fetched := TRUE;
340 end if;
341 close csr_get_dk_pll;
342
343 --
344 -- if no row fetched then raise exception
345 --
346 if not l_row_fetched then
347 -- <<RAISE Some kind of Exception>>
348 null;
349 else
350 --
351 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
352 --
353 ben_dm_data_util.update_pk_mapping
354 (p_resolve_mapping_id => x.resolve_mapping_id
355 ,p_target_id => l_table_rec.element_link_id);
356 end if;
357 --
358 end loop;
359 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
360 end;
361
362 --
363 -- DK Resolve For Table BEN_BNFTS_BAL_F
364 --
365 procedure get_dk_frm_bnb is
366 --
367 -- cursor to Fetch the DK for BEN_BNFTS_BAL_F.BNFTS_BAL_ID
368 --
369 cursor csr_get_dk_bnb (c_business_group_id number, c_source_key varchar2) is
370 select bnfts_bal_id
371 from ben_bnfts_bal_f
372 where name = c_source_key
373 and business_group_id = c_business_group_id;
374
375 cursor csr_get_all_bnb is
376 select *
377 from ben_dm_resolve_mappings
378 where table_name = 'BEN_BNFTS_BAL_F'
379 and target_id is null;
380
381 -- Declare local variables
382 l_proc varchar2(72) := g_package || 'get_dk_frm_bnb';
383 l_row_fetched boolean := FALSE;
384 l_table_rec csr_get_dk_bnb%rowtype;
385 l_table_rec_all csr_get_all_bnb%rowtype;
386 l_bg_id number(15);
387
388 begin
389
390 --
391 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_BNFTS_BAL_F
392 --
393 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
394 for x in csr_get_all_bnb loop
395 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
396 open csr_get_dk_bnb (c_source_key => x.source_key
397 ,c_business_group_id => l_bg_id);
398 fetch csr_get_dk_bnb into l_table_rec;
399 if csr_get_dk_bnb%notfound then
400 l_row_fetched := FALSE;
401 else
402 l_row_fetched := TRUE;
403 end if;
404 close csr_get_dk_bnb;
405
406 --
407 -- if no row fetched then raise exception
408 --
409 if not l_row_fetched then
410 -- <<RAISE Some kind of Exception>>
411 null;
412 else
413 --
414 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
415 --
416 ben_dm_data_util.update_pk_mapping
417 (p_resolve_mapping_id => x.resolve_mapping_id
418 ,p_target_id => l_table_rec.bnfts_bal_id);
419 end if;
420 --
421 end loop;
422 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
423 end;
424
425 --
426 -- DK Resolve For Table BEN_BNFT_PRVDR_POOL_F
427 --
428 procedure get_dk_frm_bpp is
429 --
430 -- cursor to Fetch the DK for BEN_BNFT_PRVDR_POOL_F.BNFT_PRVDR_POOL_ID
431 --
432 cursor csr_get_dk_bpp (c_business_group_id number, c_source_key varchar2) is
433 select bnft_prvdr_pool_id
434 from ben_bnft_prvdr_pool_f
435 where name = c_source_key
436 and business_group_id = c_business_group_id;
437
438 cursor csr_get_all_bpp is
439 select *
440 from ben_dm_resolve_mappings
441 where table_name = 'BEN_BNFT_PRVDR_POOL_F'
442 and target_id is null;
443
444 -- Declare local variables
445 l_proc varchar2(72) := g_package || 'get_dk_frm_bpp';
446 l_row_fetched boolean := FALSE;
447 l_table_rec csr_get_dk_bpp%rowtype;
448 l_table_rec_all csr_get_all_bpp%rowtype;
449 l_bg_id number(15);
450
451 begin
452
453 --
454 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_BNFT_PRVDR_POOL_F
455 --
456 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
457 for x in csr_get_all_bpp loop
458 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
459 open csr_get_dk_bpp (c_source_key => x.source_key
460 ,c_business_group_id => l_bg_id);
461 fetch csr_get_dk_bpp into l_table_rec;
462 if csr_get_dk_bpp%notfound then
463 l_row_fetched := FALSE;
464 else
465 l_row_fetched := TRUE;
466 end if;
467 close csr_get_dk_bpp;
468
469 --
470 -- if no row fetched then raise exception
471 --
472 if not l_row_fetched then
473 -- <<RAISE Some kind of Exception>>
474 null;
475 else
476 --
477 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
478 --
479 ben_dm_data_util.update_pk_mapping
480 (p_resolve_mapping_id => x.resolve_mapping_id
481 ,p_target_id => l_table_rec.bnft_prvdr_pool_id);
482 end if;
483 --
484 end loop;
485 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
486 end;
487
488 --
489 -- DK Resolve For Table BEN_CMBN_PLIP_F
490 --
491 procedure get_dk_frm_cpl is
492 --
493 -- cursor to Fetch the DK for BEN_CMBN_PLIP_F.CMBN_PLIP_ID
494 --
495 cursor csr_get_dk_cpl (c_business_group_id number, c_source_key varchar2) is
496 select cmbn_plip_id
497 from ben_cmbn_plip_f
498 where name = c_source_key
499 and business_group_id = c_business_group_id;
500
501 cursor csr_get_all_cpl is
502 select *
503 from ben_dm_resolve_mappings
504 where table_name = 'BEN_CMBN_PLIP_F'
505 and target_id is null;
506
507 -- Declare local variables
508 l_proc varchar2(72) := g_package || 'get_dk_frm_cpl';
509 l_row_fetched boolean := FALSE;
510 l_table_rec csr_get_dk_cpl%rowtype;
511 l_table_rec_all csr_get_all_cpl%rowtype;
512 l_bg_id number(15);
513
514 begin
515
516 --
517 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CMBN_PLIP_F
518 --
519 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
520 for x in csr_get_all_cpl loop
521 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
522 open csr_get_dk_cpl (c_source_key => x.source_key
523 ,c_business_group_id => l_bg_id);
524 fetch csr_get_dk_cpl into l_table_rec;
525 if csr_get_dk_cpl%notfound then
526 l_row_fetched := FALSE;
527 else
528 l_row_fetched := TRUE;
529 end if;
530 close csr_get_dk_cpl;
531
532 --
533 -- if no row fetched then raise exception
534 --
535 if not l_row_fetched then
536 -- <<RAISE Some kind of Exception>>
537 null;
538 else
539 --
540 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
541 --
542 ben_dm_data_util.update_pk_mapping
543 (p_resolve_mapping_id => x.resolve_mapping_id
544 ,p_target_id => l_table_rec.cmbn_plip_id);
545 end if;
546 --
547 end loop;
548 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
549 end;
550
551 --
552 -- DK Resolve For Table BEN_CMBN_PTIP_F
553 --
554 procedure get_dk_frm_cbp is
555 --
556 -- cursor to Fetch the DK for BEN_CMBN_PTIP_F.CMBN_PTIP_ID
557 --
558 cursor csr_get_dk_cbp (c_business_group_id number, c_source_key varchar2) is
559 select cmbn_ptip_id
560 from ben_cmbn_ptip_f
561 where name = c_source_key
562 and business_group_id = c_business_group_id;
563
564 cursor csr_get_all_cbp is
565 select *
566 from ben_dm_resolve_mappings
567 where table_name = 'BEN_CMBN_PTIP_F'
568 and target_id is null;
569
570 -- Declare local variables
571 l_proc varchar2(72) := g_package || 'get_dk_frm_cbp';
572 l_row_fetched boolean := FALSE;
573 l_table_rec csr_get_dk_cbp%rowtype;
574 l_table_rec_all csr_get_all_cbp%rowtype;
575 l_bg_id number(15);
576
577 begin
578
579 --
580 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CMBN_PTIP_F
581 --
582 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
583 for x in csr_get_all_cbp loop
584 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
585 open csr_get_dk_cbp (c_source_key => x.source_key
586 ,c_business_group_id => l_bg_id);
587 fetch csr_get_dk_cbp into l_table_rec;
588 if csr_get_dk_cbp%notfound then
589 l_row_fetched := FALSE;
590 else
591 l_row_fetched := TRUE;
592 end if;
593 close csr_get_dk_cbp;
594
595 --
596 -- if no row fetched then raise exception
597 --
598 if not l_row_fetched then
599 -- <<RAISE Some kind of Exception>>
600 null;
601 else
602 --
603 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
604 --
605 ben_dm_data_util.update_pk_mapping
606 (p_resolve_mapping_id => x.resolve_mapping_id
607 ,p_target_id => l_table_rec.cmbn_ptip_id);
608 end if;
609 --
610 end loop;
611 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
612 end;
613
614 --
615 -- DK Resolve For Table BEN_CMBN_PTIP_OPT_F
616 --
617 procedure get_dk_frm_cpt is
618 --
619 -- cursor to Fetch the DK for BEN_CMBN_PTIP_OPT_F.CMBN_PTIP_OPT_ID
620 --
621 cursor csr_get_dk_cpt (c_business_group_id number, c_source_key varchar2) is
622 select cmbn_ptip_opt_id
623 from ben_cmbn_ptip_opt_f
624 where name = c_source_key
625 and business_group_id = c_business_group_id;
626
627 cursor csr_get_all_cpt is
628 select *
629 from ben_dm_resolve_mappings
630 where table_name = 'BEN_CMBN_PTIP_OPT_F'
631 and target_id is null;
632
633 -- Declare local variables
634 l_proc varchar2(72) := g_package || 'get_dk_frm_cpt';
635 l_row_fetched boolean := FALSE;
636 l_table_rec csr_get_dk_cpt%rowtype;
637 l_table_rec_all csr_get_all_cpt%rowtype;
638 l_bg_id number(15);
639
640 begin
641
642 --
643 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CMBN_PTIP_OPT_F
644 --
645 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
646 for x in csr_get_all_cpt loop
647 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
648 open csr_get_dk_cpt (c_source_key => x.source_key
649 ,c_business_group_id => l_bg_id);
650 fetch csr_get_dk_cpt into l_table_rec;
651 if csr_get_dk_cpt%notfound then
652 l_row_fetched := FALSE;
653 else
654 l_row_fetched := TRUE;
655 end if;
656 close csr_get_dk_cpt;
657
658 --
659 -- if no row fetched then raise exception
660 --
661 if not l_row_fetched then
662 -- <<RAISE Some kind of Exception>>
663 null;
664 else
665 --
666 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
667 --
668 ben_dm_data_util.update_pk_mapping
669 (p_resolve_mapping_id => x.resolve_mapping_id
670 ,p_target_id => l_table_rec.cmbn_ptip_opt_id);
671 end if;
672 --
673 end loop;
674 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
675 end;
676
677 --
678 -- DK Resolve For Table BEN_CM_TRGR
679 --
680 procedure get_dk_frm_bcr is
681 --
682 -- cursor to Fetch the DK for BEN_CM_TRGR.CM_TRGR_ID
683 --
684 cursor csr_get_dk_bcr (c_business_group_id number, c_source_key varchar2) is
685 select cm_trgr_id
686 from ben_cm_trgr
687 where cm_trgr_src_cd||cm_trgr_typ_cd||proc_cd = c_source_key
688 ;
689
690 cursor csr_get_all_bcr is
691 select *
692 from ben_dm_resolve_mappings
693 where table_name = 'BEN_CM_TRGR'
694 and target_id is null;
695
696 -- Declare local variables
697 l_proc varchar2(72) := g_package || 'get_dk_frm_bcr';
698 l_row_fetched boolean := FALSE;
699 l_table_rec csr_get_dk_bcr%rowtype;
700 l_table_rec_all csr_get_all_bcr%rowtype;
701 l_bg_id number(15);
702
703 begin
704
705 --
706 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CM_TRGR
707 --
708 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
709 for x in csr_get_all_bcr loop
710 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
711 open csr_get_dk_bcr (c_source_key => x.source_key
712 ,c_business_group_id => l_bg_id);
713 fetch csr_get_dk_bcr into l_table_rec;
714 if csr_get_dk_bcr%notfound then
715 l_row_fetched := FALSE;
716 else
717 l_row_fetched := TRUE;
718 end if;
719 close csr_get_dk_bcr;
720
721 --
722 -- if no row fetched then raise exception
723 --
724 if not l_row_fetched then
725 -- <<RAISE Some kind of Exception>>
726 null;
727 else
728 --
729 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
730 --
731 ben_dm_data_util.update_pk_mapping
732 (p_resolve_mapping_id => x.resolve_mapping_id
733 ,p_target_id => l_table_rec.cm_trgr_id);
734 end if;
735 --
736 end loop;
737 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
738 end;
739
740 --
741 -- DK Resolve For Table BEN_CM_TYP_F
742 --
743 procedure get_dk_frm_cct is
744 --
745 -- cursor to Fetch the DK for BEN_CM_TYP_F.CM_TYP_ID
746 --
747 cursor csr_get_dk_cct (c_business_group_id number, c_source_key varchar2) is
748 select cm_typ_id
749 from ben_cm_typ_f
750 where name = c_source_key
751 and business_group_id = c_business_group_id;
752
753 cursor csr_get_all_cct is
754 select *
755 from ben_dm_resolve_mappings
756 where table_name = 'BEN_CM_TYP_F'
757 and target_id is null;
758
759 -- Declare local variables
760 l_proc varchar2(72) := g_package || 'get_dk_frm_cct';
761 l_row_fetched boolean := FALSE;
762 l_table_rec csr_get_dk_cct%rowtype;
763 l_table_rec_all csr_get_all_cct%rowtype;
764 l_bg_id number(15);
765
766 begin
767
768 --
769 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CM_TYP_F
770 --
771 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
772 for x in csr_get_all_cct loop
773 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
774 open csr_get_dk_cct (c_source_key => x.source_key
775 ,c_business_group_id => l_bg_id);
776 fetch csr_get_dk_cct into l_table_rec;
777 if csr_get_dk_cct%notfound then
778 l_row_fetched := FALSE;
779 else
780 l_row_fetched := TRUE;
781 end if;
782 close csr_get_dk_cct;
783
784 --
785 -- if no row fetched then raise exception
786 --
787 if not l_row_fetched then
788 -- <<RAISE Some kind of Exception>>
789 null;
790 else
791 --
792 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
793 --
794 ben_dm_data_util.update_pk_mapping
795 (p_resolve_mapping_id => x.resolve_mapping_id
796 ,p_target_id => l_table_rec.cm_typ_id);
797 end if;
798 --
799 end loop;
800 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
801 end;
802
803 --
804 -- DK Resolve For Table BEN_COMP_LVL_FCTR
805 --
806 procedure get_dk_frm_clf is
807 --
808 -- cursor to Fetch the DK for BEN_COMP_LVL_FCTR.COMP_LVL_FCTR_ID
809 --
810 cursor csr_get_dk_clf (c_business_group_id number, c_source_key varchar2) is
811 select comp_lvl_fctr_id
812 from ben_comp_lvl_fctr
813 where name = c_source_key
814 and business_group_id = c_business_group_id
815 ;
816
817 cursor csr_get_all_clf is
818 select *
819 from ben_dm_resolve_mappings
820 where table_name = 'BEN_COMP_LVL_FCTR'
821 and target_id is null;
822
823 -- Declare local variables
824 l_proc varchar2(72) := g_package || 'get_dk_frm_clf';
825 l_row_fetched boolean := FALSE;
826 l_table_rec csr_get_dk_clf%rowtype;
827 l_table_rec_all csr_get_all_clf%rowtype;
828 l_bg_id number(15);
829
830 begin
831
832 --
833 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_COMP_LVL_FCTR
834 --
835 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
836 for x in csr_get_all_clf loop
837 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
838 open csr_get_dk_clf (c_source_key => x.source_key
839 ,c_business_group_id => l_bg_id);
840 fetch csr_get_dk_clf into l_table_rec;
841 if csr_get_dk_clf%notfound then
842 l_row_fetched := FALSE;
843 else
844 l_row_fetched := TRUE;
845 end if;
846 close csr_get_dk_clf;
847
848 --
849 -- if no row fetched then raise exception
850 --
851 if not l_row_fetched then
852 -- <<RAISE Some kind of Exception>>
853 null;
854 else
855 --
856 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
857 --
858 ben_dm_data_util.update_pk_mapping
859 (p_resolve_mapping_id => x.resolve_mapping_id
860 ,p_target_id => l_table_rec.comp_lvl_fctr_id);
861 end if;
862 --
863 end loop;
864 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
865 end;
866
867 --
868 -- DK Resolve For Table BEN_CVG_AMT_CALC_MTHD_F
869 --
870 procedure get_dk_frm_ccm is
871 --
872 -- cursor to Fetch the DK for BEN_CVG_AMT_CALC_MTHD_F.CVG_AMT_CALC_MTHD_ID
873 --
874 cursor csr_get_dk_ccm (c_business_group_id number, c_source_key varchar2) is
875 select cvg_amt_calc_mthd_id
876 from ben_cvg_amt_calc_mthd_f
877 where name = c_source_key
878 and business_group_id = c_business_group_id;
879
880 cursor csr_get_all_ccm is
881 select *
882 from ben_dm_resolve_mappings
883 where table_name = 'BEN_CVG_AMT_CALC_MTHD_F'
884 and target_id is null;
885
886 -- Declare local variables
887 l_proc varchar2(72) := g_package || 'get_dk_frm_ccm';
888 l_row_fetched boolean := FALSE;
889 l_table_rec csr_get_dk_ccm%rowtype;
890 l_table_rec_all csr_get_all_ccm%rowtype;
891 l_bg_id number(15);
892
893 begin
894
895 --
896 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_CVG_AMT_CALC_MTHD_F
897 --
898 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
899 for x in csr_get_all_ccm loop
900 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
901 open csr_get_dk_ccm (c_source_key => x.source_key
902 ,c_business_group_id => l_bg_id);
903 fetch csr_get_dk_ccm into l_table_rec;
904 if csr_get_dk_ccm%notfound then
905 l_row_fetched := FALSE;
906 else
907 l_row_fetched := TRUE;
908 end if;
909 close csr_get_dk_ccm;
910
911 --
912 -- if no row fetched then raise exception
913 --
914 if not l_row_fetched then
915 -- <<RAISE Some kind of Exception>>
916 null;
917 else
918 --
919 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
920 --
921 ben_dm_data_util.update_pk_mapping
922 (p_resolve_mapping_id => x.resolve_mapping_id
923 ,p_target_id => l_table_rec.cvg_amt_calc_mthd_id);
924 end if;
925 --
926 end loop;
927 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
928 end;
929
930 --
931 -- DK Resolve For Table BEN_ENRT_PERD
932 --
933 procedure get_dk_frm_enp is
934 --
935 -- cursor to Fetch the DK for BEN_ENRT_PERD.ENRT_PERD_ID
936 --
937 cursor csr_get_dk_enp (c_business_group_id number, c_source_key varchar2
938 ,c_target_id1 number, c_target_id2 number) is
939 select enrt_perd_id
940 from ben_enrt_perd
941 where strt_dt = to_date(c_source_key,'dd-mm-rrrr')
942 and yr_perd_id = c_target_id1
943 and popl_enrt_typ_cycl_id = c_target_id2
944 and business_group_id = c_business_group_id
945 ;
946
947 cursor csr_get_all_enp is
948 select *
949 from ben_dm_resolve_mappings
950 where table_name = 'BEN_ENRT_PERD'
951 and target_id is null;
952
953 cursor csr_get_dk_pop (c_pgm_id number, c_pl_id number, c_business_group_id number) is
954 select *
955 from ben_popl_enrt_typ_cycl_f
956 where nvl(pgm_id,-1) = nvl(c_pgm_id,-1)
957 and nvl(pl_id,-1) = nvl(c_pl_id,-1)
958 and business_group_id = c_business_group_id
959 order by decode(enrt_typ_cycl_cd,'O',1,2) asc;
960
961 -- Declare local variables
962 l_proc varchar2(72) := g_package || 'get_dk_frm_enp';
963 l_row_fetched boolean := FALSE;
964 l_table_rec csr_get_dk_enp%rowtype;
965 l_table_rec_pop csr_get_dk_pop%rowtype;
966 l_table_rec_all csr_get_all_enp%rowtype;
967 l_bg_id number(15);
968 l_target_id1 number(15);
969 l_target_id2 number(15);
970 l_target_id3 number(15);
971
972 begin
973
974 --
975 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_ENRT_PERD
976 --
977 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
978 for x in csr_get_all_enp loop
979 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
980 -- Get YR_PERD_ID
981 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
982 -- Get PGM_ID
983 if x.resolve_mapping_id2 is not null then
984 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
985 end if;
986 -- Get PL_ID
987 if x.resolve_mapping_id3 is not null then
988 l_target_id3 := get_target_id_for_mapping(x.resolve_mapping_id3);
989 end if;
990 open csr_get_dk_pop (c_business_group_id => l_bg_id
991 ,c_pgm_id => l_target_id2
992 ,c_pl_id => l_target_id3);
993 fetch csr_get_dk_pop into l_table_rec_pop;
994 if csr_get_dk_pop%notfound then
995 l_row_fetched := FALSE;
996 -- <<RAISE EXCEPTION>>
997 else
998 l_row_fetched := TRUE;
999 end if;
1000 close csr_get_dk_pop;
1001
1002 open csr_get_dk_enp (c_source_key => x.source_key
1003 ,c_business_group_id => l_bg_id
1004 ,c_target_id1 => l_target_id1
1005 ,c_target_id2 => l_table_rec_pop.popl_enrt_typ_cycl_id);
1006 fetch csr_get_dk_enp into l_table_rec;
1007 if csr_get_dk_enp%notfound then
1008 l_row_fetched := FALSE;
1009 else
1010 l_row_fetched := TRUE;
1011 end if;
1012 close csr_get_dk_enp;
1013 --
1014 -- if no row fetched then raise exception
1015 --
1016 if not l_row_fetched then
1017 -- <<RAISE Some kind of Exception>>
1018 null;
1019 else
1020 --
1021 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1022 --
1023 ben_dm_data_util.update_pk_mapping
1024 (p_resolve_mapping_id => x.resolve_mapping_id
1025 ,p_target_id => l_table_rec.enrt_perd_id);
1026 end if;
1027 --
1028 end loop;
1029 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1030 end;
1031 --
1032 -- DK Resolve For Table BEN_LEE_RSN_F
1033 --
1034 procedure get_dk_frm_len is
1035 --
1036 -- cursor to Fetch the DK for BEN_LEE_RSN_F.LEE_RSN_ID
1037 --
1038 cursor csr_get_dk_len (c_business_group_id number,
1039 c_source_key varchar2,
1040 c_ler_id number,
1041 c_popl_enrt_typ_cycl_id number) is
1042 select lee_rsn_id
1043 from ben_lee_rsn_f
1044 where effective_start_date = to_date(c_source_key,'dd-mm-rrrr')
1045 and ler_id = c_ler_id
1046 and popl_enrt_typ_cycl_id = c_popl_enrt_typ_cycl_id
1047 and business_group_id = c_business_group_id;
1048
1049 cursor csr_get_all_len is
1050 select *
1051 from ben_dm_resolve_mappings
1052 where table_name = 'BEN_LEE_RSN_F'
1053 and target_id is null;
1054
1055 cursor csr_get_dk_pop (c_pgm_id number, c_pl_id number, c_business_group_id number) is
1056 select *
1057 from ben_popl_enrt_typ_cycl_f
1058 where nvl(pgm_id,-1) = nvl(c_pgm_id,-1)
1059 and nvl(pl_id,-1) = nvl(c_pl_id,-1)
1060 and business_group_id = c_business_group_id
1061 and enrt_typ_cycl_cd = 'L';
1062
1063 -- Declare local variables
1064 l_proc varchar2(72) := g_package || 'get_dk_frm_len';
1065 l_row_fetched boolean := FALSE;
1066 l_table_rec csr_get_dk_len%rowtype;
1067 l_table_rec_pop csr_get_dk_pop%rowtype;
1068 l_bg_id number(15);
1069 l_target_id1 number(15);
1070 l_target_id2 number(15);
1071 l_target_id3 number(15);
1072
1073 begin
1074
1075 --
1076 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_ENRT_PERD
1077 --
1078 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1079 for x in csr_get_all_len loop
1080 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1081 -- Get LER_ID
1082 if x.resolve_mapping_id1 is not null then
1083 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
1084 end if;
1085 -- Get PGM_ID
1086 if x.resolve_mapping_id2 is not null then
1087 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
1088 end if;
1089 -- Get PL_ID
1090 if x.resolve_mapping_id3 is not null then
1091 l_target_id3 := get_target_id_for_mapping(x.resolve_mapping_id3);
1092 end if;
1093 open csr_get_dk_pop (c_business_group_id => l_bg_id
1094 ,c_pgm_id => l_target_id2
1095 ,c_pl_id => l_target_id3);
1096 fetch csr_get_dk_pop into l_table_rec_pop;
1097 if csr_get_dk_pop%notfound then
1098 l_row_fetched := FALSE;
1099 -- <<RAISE EXCEPTION>>
1100 else
1101 l_row_fetched := TRUE;
1102 end if;
1103 close csr_get_dk_pop;
1104
1105 open csr_get_dk_len (c_business_group_id => l_bg_id
1106 ,c_source_key => x.source_key
1107 ,c_ler_id => l_target_id1
1108 ,c_popl_enrt_typ_cycl_id => l_table_rec_pop.popl_enrt_typ_cycl_id);
1109 fetch csr_get_dk_len into l_table_rec;
1110 if csr_get_dk_len%notfound then
1111 l_row_fetched := FALSE;
1112 else
1113 l_row_fetched := TRUE;
1114 end if;
1115 close csr_get_dk_len;
1116 --
1117 -- if no row fetched then raise exception
1118 --
1119 if not l_row_fetched then
1120 -- <<RAISE Some kind of Exception>>
1121 null;
1122 else
1123 --
1124 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1125 --
1126 ben_dm_data_util.update_pk_mapping
1127 (p_resolve_mapping_id => x.resolve_mapping_id
1128 ,p_target_id => l_table_rec.lee_rsn_id);
1129 end if;
1130 --
1131 end loop;
1132 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1133 end;
1134
1135 --
1136 -- DK Resolve For Table BEN_LER_F
1137 --
1138 procedure get_dk_frm_ler is
1139 --
1140 -- cursor to Fetch the DK for BEN_LER_F.LER_ID
1141 --
1142 cursor csr_get_dk_ler (c_business_group_id number, c_source_key varchar2) is
1143 select ler_id
1144 from ben_ler_f
1145 where name = c_source_key
1146 and business_group_id = c_business_group_id;
1147
1148 cursor csr_get_all_ler is
1149 select *
1150 from ben_dm_resolve_mappings
1151 where table_name = 'BEN_LER_F'
1152 and target_id is null;
1153
1154 -- Declare local variables
1155 l_proc varchar2(72) := g_package || 'get_dk_frm_ler';
1156 l_row_fetched boolean := FALSE;
1157 l_table_rec csr_get_dk_ler%rowtype;
1158 l_table_rec_all csr_get_all_ler%rowtype;
1159 l_bg_id number(15);
1160
1161 begin
1162
1163 --
1164 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_LER_F
1165 --
1166 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1167 for x in csr_get_all_ler loop
1168 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1169 open csr_get_dk_ler (c_source_key => x.source_key
1170 ,c_business_group_id => l_bg_id);
1171 fetch csr_get_dk_ler into l_table_rec;
1172 if csr_get_dk_ler%notfound then
1173 l_row_fetched := FALSE;
1174 else
1175 l_row_fetched := TRUE;
1176 end if;
1177 close csr_get_dk_ler;
1178
1179 --
1180 -- if no row fetched then raise exception
1181 --
1182 if not l_row_fetched then
1183 -- <<RAISE Some kind of Exception>>
1184 null;
1185 else
1186 --
1187 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1188 --
1189 ben_dm_data_util.update_pk_mapping
1190 (p_resolve_mapping_id => x.resolve_mapping_id
1191 ,p_target_id => l_table_rec.ler_id);
1192 end if;
1193 --
1194 end loop;
1195 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1196 end;
1197
1198 --
1199 -- DK Resolve For Table BEN_OIPLIP_F
1200 --
1201 procedure get_dk_frm_boi is
1202 --
1203 -- cursor to Fetch the DK for BEN_OIPLIP_F.OIPLIP_ID
1204 --
1205 cursor csr_get_dk_boi (c_business_group_id number, c_source_key varchar2
1206 ,c_target_id1 number, c_target_id2 number) is
1207 select oiplip_id
1208 from ben_oiplip_f
1209 where oipl_id = c_target_id1
1210 and plip_id = c_target_id2;
1211
1212 cursor csr_get_all_boi is
1213 select *
1214 from ben_dm_resolve_mappings
1215 where table_name = 'BEN_OIPLIP_F'
1216 and target_id is null;
1217
1218 -- Declare local variables
1219 l_proc varchar2(72) := g_package || 'get_dk_frm_boi';
1220 l_row_fetched boolean := FALSE;
1221 l_table_rec csr_get_dk_boi%rowtype;
1222 l_table_rec_all csr_get_all_boi%rowtype;
1223 l_bg_id number(15);
1224 l_target_id1 number(15);
1225 l_target_id2 number(15);
1226
1227 begin
1228
1229 --
1230 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_OIPLIP_F
1231 --
1232 for x in csr_get_all_boi loop
1233 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1234 -- Get OIPL_ID
1235 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
1236 -- Get PLIP_ID
1237 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
1238 open csr_get_dk_boi (c_source_key => x.source_key
1239 ,c_business_group_id => l_bg_id
1240 ,c_target_id1 => l_target_id1
1241 ,c_target_id2 => l_target_id2);
1242 fetch csr_get_dk_boi into l_table_rec;
1243 if csr_get_dk_boi%notfound then
1244 l_row_fetched := FALSE;
1245 else
1246 l_row_fetched := TRUE;
1247 end if;
1248 close csr_get_dk_boi;
1249
1250 --
1251 -- if no row fetched then raise exception
1252 --
1253 if not l_row_fetched then
1254 -- <<RAISE Some kind of Exception>>
1255 null;
1256 else
1257 --
1258 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1259 --
1260 ben_dm_data_util.update_pk_mapping
1261 (p_resolve_mapping_id => x.resolve_mapping_id
1262 ,p_target_id => l_table_rec.oiplip_id);
1263 end if;
1264 --
1265 end loop;
1266 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1267 end;
1268
1269 --
1270 -- DK Resolve For Table BEN_OIPL_F
1271 --
1272 procedure get_dk_frm_cop is
1273 --
1274 -- cursor to Fetch the DK for BEN_OIPL_F.OIPL_ID
1275 --
1276 cursor csr_get_dk_cop (c_business_group_id number, c_source_key varchar2
1277 ,c_target_id1 number, c_target_id2 number) is
1278 select oipl_id
1279 from ben_oipl_f
1280 where pl_id = c_target_id1
1281 and opt_id = c_target_id2
1282 and business_group_id = c_business_group_id;
1283
1284 cursor csr_get_all_cop is
1285 select *
1286 from ben_dm_resolve_mappings
1287 where table_name = 'BEN_OIPL_F'
1288 and target_id is null;
1289
1290 -- Declare local variables
1291 l_proc varchar2(72) := g_package || 'get_dk_frm_cop';
1292 l_row_fetched boolean := FALSE;
1293 l_table_rec csr_get_dk_cop%rowtype;
1294 l_table_rec_all csr_get_all_cop%rowtype;
1295 l_bg_id number(15);
1296 l_target_id1 number(15);
1297 l_target_id2 number(15);
1298
1299 begin
1300
1301 --
1302 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_OIPL_F
1303 --
1304 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1305 for x in csr_get_all_cop loop
1306 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1307 -- Get PL_ID
1308 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
1309 -- Get OPT_ID
1310 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
1311 open csr_get_dk_cop (c_source_key => x.source_key
1312 ,c_business_group_id => l_bg_id
1313 ,c_target_id1 => l_target_id1
1314 ,c_target_id2 => l_target_id2);
1315 fetch csr_get_dk_cop into l_table_rec;
1316 if csr_get_dk_cop%notfound then
1317 l_row_fetched := FALSE;
1318 else
1319 l_row_fetched := TRUE;
1320 end if;
1321 close csr_get_dk_cop;
1322
1323 --
1324 -- if no row fetched then raise exception
1325 --
1326 if not l_row_fetched then
1327 -- <<RAISE Some kind of Exception>>
1328 null;
1329 else
1330 --
1331 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1332 --
1333 ben_dm_data_util.update_pk_mapping
1334 (p_resolve_mapping_id => x.resolve_mapping_id
1335 ,p_target_id => l_table_rec.oipl_id);
1336 end if;
1337 --
1338 end loop;
1339 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1340 end;
1341
1342 --
1343 -- DK Resolve For Table BEN_OPT_F
1344 --
1345 procedure get_dk_frm_opt is
1346 --
1347 -- cursor to Fetch the DK for BEN_OPT_F.OPT_ID
1348 --
1349 cursor csr_get_dk_opt (c_business_group_id number, c_source_key varchar2) is
1350 select opt_id
1351 from ben_opt_f
1352 where name = c_source_key
1353 and business_group_id = c_business_group_id;
1354
1355 cursor csr_get_all_opt is
1356 select *
1357 from ben_dm_resolve_mappings
1358 where table_name = 'BEN_OPT_F'
1359 and target_id is null;
1360
1361 -- Declare local variables
1362 l_proc varchar2(72) := g_package || 'get_dk_frm_opt';
1363 l_row_fetched boolean := FALSE;
1364 l_table_rec csr_get_dk_opt%rowtype;
1365 l_table_rec_all csr_get_all_opt%rowtype;
1366 l_bg_id number(15);
1367
1368 begin
1369
1370 --
1371 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_OPT_F
1372 --
1373 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1374 for x in csr_get_all_opt loop
1375 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1376 open csr_get_dk_opt (c_source_key => x.source_key
1377 ,c_business_group_id => l_bg_id);
1378 fetch csr_get_dk_opt into l_table_rec;
1379 if csr_get_dk_opt%notfound then
1380 l_row_fetched := FALSE;
1381 else
1382 l_row_fetched := TRUE;
1383 end if;
1384 close csr_get_dk_opt;
1385
1386 --
1387 -- if no row fetched then raise exception
1388 --
1389 if not l_row_fetched then
1390 -- <<RAISE Some kind of Exception>>
1391 null;
1392 else
1393 --
1394 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1395 --
1396 ben_dm_data_util.update_pk_mapping
1397 (p_resolve_mapping_id => x.resolve_mapping_id
1398 ,p_target_id => l_table_rec.opt_id);
1399 end if;
1400 --
1401 end loop;
1402 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1403 end;
1404
1405 --
1406 -- DK Resolve For Table BEN_PGM_F
1407 --
1408 procedure get_dk_frm_pgm is
1409 --
1410 -- cursor to Fetch the DK for BEN_PGM_F.PGM_ID
1411 --
1412 cursor csr_get_dk_pgm (c_business_group_id number, c_source_key varchar2) is
1413 select pgm_id
1414 from ben_pgm_f
1415 where name = c_source_key
1416 and business_group_id = c_business_group_id;
1417
1418 cursor csr_get_all_pgm is
1419 select *
1420 from ben_dm_resolve_mappings
1421 where table_name = 'BEN_PGM_F'
1422 and target_id is null;
1423
1424 -- Declare local variables
1425 l_proc varchar2(72) := g_package || 'get_dk_frm_pgm';
1426 l_row_fetched boolean := FALSE;
1427 l_table_rec csr_get_dk_pgm%rowtype;
1428 l_table_rec_all csr_get_all_pgm%rowtype;
1429 l_bg_id number(15);
1430
1431 begin
1432
1433 --
1434 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_PGM_F
1435 --
1436 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1437 for x in csr_get_all_pgm loop
1438 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1439 open csr_get_dk_pgm (c_source_key => x.source_key
1440 ,c_business_group_id => l_bg_id);
1441 fetch csr_get_dk_pgm into l_table_rec;
1442 if csr_get_dk_pgm%notfound then
1443 l_row_fetched := FALSE;
1444 else
1445 l_row_fetched := TRUE;
1446 end if;
1447 close csr_get_dk_pgm;
1448
1449 --
1450 -- if no row fetched then raise exception
1451 --
1452 if not l_row_fetched then
1453 -- <<RAISE Some kind of Exception>>
1454 null;
1455 else
1456 --
1457 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1458 --
1459 ben_dm_data_util.update_pk_mapping
1460 (p_resolve_mapping_id => x.resolve_mapping_id
1461 ,p_target_id => l_table_rec.pgm_id);
1462 end if;
1463 --
1464 end loop;
1465 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1466 end;
1467
1468 --
1469 -- DK Resolve For Table BEN_PLIP_F
1470 --
1471 procedure get_dk_frm_cpp is
1472 --
1473 -- cursor to Fetch the DK for BEN_PLIP_F.PLIP_ID
1474 --
1475 cursor csr_get_dk_cpp (c_business_group_id number, c_source_key varchar2
1476 ,c_target_id1 number, c_target_id2 number) is
1477 select plip_id
1478 from ben_plip_f
1479 where pgm_id = c_target_id1
1480 and pl_id = c_target_id2
1481 and business_group_id = c_business_group_id;
1482
1483 cursor csr_get_all_cpp is
1484 select *
1485 from ben_dm_resolve_mappings
1486 where table_name = 'BEN_PLIP_F'
1487 and target_id is null;
1488
1489 -- Declare local variables
1490 l_proc varchar2(72) := g_package || 'get_dk_frm_cpp';
1491 l_row_fetched boolean := FALSE;
1492 l_table_rec csr_get_dk_cpp%rowtype;
1493 l_table_rec_all csr_get_all_cpp%rowtype;
1494 l_bg_id number(15);
1495 l_target_id1 number(15);
1496 l_target_id2 number(15);
1497
1498 begin
1499
1500 --
1501 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_PLIP_F
1502 --
1503 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1504 for x in csr_get_all_cpp loop
1505 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1506 -- Get PGM_ID
1507 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
1508 -- Get PL_ID
1509 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
1510 open csr_get_dk_cpp (c_source_key => x.source_key
1511 ,c_business_group_id => l_bg_id
1512 ,c_target_id1 => l_target_id1
1513 ,c_target_id2 => l_target_id2);
1514 fetch csr_get_dk_cpp into l_table_rec;
1515 if csr_get_dk_cpp%notfound then
1516 l_row_fetched := FALSE;
1517 else
1518 l_row_fetched := TRUE;
1519 end if;
1520 close csr_get_dk_cpp;
1521
1522 --
1523 -- if no row fetched then raise exception
1524 --
1525 if not l_row_fetched then
1526 -- <<RAISE Some kind of Exception>>
1527 null;
1528 else
1529 --
1530 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1531 --
1532 ben_dm_data_util.update_pk_mapping
1533 (p_resolve_mapping_id => x.resolve_mapping_id
1534 ,p_target_id => l_table_rec.plip_id);
1535 end if;
1536 --
1537 end loop;
1538 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1539 end;
1540
1541 --
1542 -- DK Resolve For Table BEN_PL_F
1543 --
1544 procedure get_dk_frm_pln is
1545 --
1546 -- cursor to Fetch the DK for BEN_PL_F.PL_ID
1547 --
1548 cursor csr_get_dk_pln (c_business_group_id number, c_source_key varchar2) is
1549 select pl_id
1550 from ben_pl_f
1551 where name = c_source_key
1552 and business_group_id = c_business_group_id;
1553
1554 cursor csr_get_all_pln is
1555 select *
1556 from ben_dm_resolve_mappings
1557 where table_name = 'BEN_PL_F'
1558 and target_id is null;
1559
1560 -- Declare local variables
1561 l_proc varchar2(72) := g_package || 'get_dk_frm_pln';
1562 l_row_fetched boolean := FALSE;
1563 l_table_rec csr_get_dk_pln%rowtype;
1564 l_table_rec_all csr_get_all_pln%rowtype;
1565 l_bg_id number(15);
1566
1567 begin
1568
1569 --
1570 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_PL_F
1571 --
1572 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1573 for x in csr_get_all_pln loop
1574 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1575 open csr_get_dk_pln (c_source_key => x.source_key
1576 ,c_business_group_id => l_bg_id);
1577 fetch csr_get_dk_pln into l_table_rec;
1578 if csr_get_dk_pln%notfound then
1579 l_row_fetched := FALSE;
1580 else
1581 l_row_fetched := TRUE;
1582 end if;
1583 close csr_get_dk_pln;
1584
1585 --
1586 -- if no row fetched then raise exception
1587 --
1588 if not l_row_fetched then
1589 -- <<RAISE Some kind of Exception>>
1590 null;
1591 else
1592 --
1593 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1594 --
1595 ben_dm_data_util.update_pk_mapping
1596 (p_resolve_mapping_id => x.resolve_mapping_id
1597 ,p_target_id => l_table_rec.pl_id);
1598 end if;
1599 --
1600 end loop;
1601 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1602 end;
1603
1604 --
1605 -- DK Resolve For Table BEN_PL_TYP_F
1606 --
1607 procedure get_dk_frm_ptp is
1608 --
1609 -- cursor to Fetch the DK for BEN_PL_TYP_F.PL_TYP_ID
1610 --
1611 cursor csr_get_dk_ptp (c_business_group_id number, c_source_key varchar2) is
1612 select pl_typ_id
1613 from ben_pl_typ_f
1614 where name = c_source_key
1615 and business_group_id = c_business_group_id;
1616
1617 cursor csr_get_all_ptp is
1618 select *
1619 from ben_dm_resolve_mappings
1620 where table_name = 'BEN_PL_TYP_F'
1621 and target_id is null;
1622
1623 -- Declare local variables
1624 l_proc varchar2(72) := g_package || 'get_dk_frm_ptp';
1625 l_row_fetched boolean := FALSE;
1626 l_table_rec csr_get_dk_ptp%rowtype;
1627 l_table_rec_all csr_get_all_ptp%rowtype;
1628 l_bg_id number(15);
1629
1630 begin
1631
1632 --
1633 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_PL_TYP_F
1634 --
1635 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1636 for x in csr_get_all_ptp loop
1637 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1638 open csr_get_dk_ptp (c_source_key => x.source_key
1639 ,c_business_group_id => l_bg_id);
1640 fetch csr_get_dk_ptp into l_table_rec;
1641 if csr_get_dk_ptp%notfound then
1642 l_row_fetched := FALSE;
1643 else
1644 l_row_fetched := TRUE;
1645 end if;
1646 close csr_get_dk_ptp;
1647
1648 --
1649 -- if no row fetched then raise exception
1650 --
1651 if not l_row_fetched then
1652 -- <<RAISE Some kind of Exception>>
1653 null;
1654 else
1655 --
1656 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1657 --
1658 ben_dm_data_util.update_pk_mapping
1659 (p_resolve_mapping_id => x.resolve_mapping_id
1660 ,p_target_id => l_table_rec.pl_typ_id);
1661 end if;
1662 --
1663 end loop;
1664 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1665 end;
1666
1667 --
1668 -- DK Resolve For Table BEN_PTIP_F
1669 --
1670 procedure get_dk_frm_ctp is
1671 --
1672 -- cursor to Fetch the DK for BEN_PTIP_F.PTIP_ID
1673 --
1674 cursor csr_get_dk_ctp (c_business_group_id number, c_source_key varchar2
1675 ,c_target_id1 number, c_target_id2 number) is
1676 select ptip_id
1677 from ben_ptip_f
1678 where pgm_id = c_target_id1
1679 and pl_typ_id = c_target_id2
1680 and business_group_id = c_business_group_id;
1681
1682 cursor csr_get_all_ctp is
1683 select *
1684 from ben_dm_resolve_mappings
1685 where table_name = 'BEN_PTIP_F'
1686 and target_id is null;
1687
1688 -- Declare local variables
1689 l_proc varchar2(72) := g_package || 'get_dk_frm_ctp';
1690 l_row_fetched boolean := FALSE;
1691 l_table_rec csr_get_dk_ctp%rowtype;
1692 l_table_rec_all csr_get_all_ctp%rowtype;
1693 l_bg_id number(15);
1694 l_target_id1 number(15);
1695 l_target_id2 number(15);
1696
1697 begin
1698
1699 --
1700 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_PTIP_F
1701 --
1702 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1703 for x in csr_get_all_ctp loop
1704 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1705 -- Get PGM_ID
1706 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
1707 -- Get PL_TYP_ID
1708 l_target_id2 := get_target_id_for_mapping(x.resolve_mapping_id2);
1709 open csr_get_dk_ctp (c_source_key => x.source_key
1710 ,c_business_group_id => l_bg_id
1711 ,c_target_id1 => l_target_id1
1712 ,c_target_id2 => l_target_id2);
1713 fetch csr_get_dk_ctp into l_table_rec;
1714 if csr_get_dk_ctp%notfound then
1715 l_row_fetched := FALSE;
1716 else
1717 l_row_fetched := TRUE;
1718 end if;
1719 close csr_get_dk_ctp;
1720
1721 --
1722 -- if no row fetched then raise exception
1723 --
1724 if not l_row_fetched then
1725 -- <<RAISE Some kind of Exception>>
1726 null;
1727 else
1728 --
1729 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1730 --
1731 ben_dm_data_util.update_pk_mapping
1732 (p_resolve_mapping_id => x.resolve_mapping_id
1733 ,p_target_id => l_table_rec.ptip_id);
1734 end if;
1735 --
1736 end loop;
1737 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1738 end;
1739
1740 --
1741 -- DK Resolve For Table BEN_YR_PERD
1742 --
1743 procedure get_dk_frm_yrp is
1744 --
1745 -- cursor to Fetch the DK for BEN_YR_PERD.YR_PERD_ID
1746 --
1747 cursor csr_get_dk_yrp (c_business_group_id number, c_source_key varchar2) is
1748 select yr_perd_id
1749 from ben_yr_perd
1750 where to_char(END_DATE,'YYYYMMDD:HH24:mi:ss')||'-'||to_char(START_DATE,'DD-MON-YYYY')||'-'||PERD_TYP_CD = c_source_key
1751 and business_group_id = c_business_group_id;
1752
1753 cursor csr_get_all_yrp is
1754 select *
1755 from ben_dm_resolve_mappings
1756 where table_name = 'BEN_YR_PERD'
1757 and target_id is null;
1758
1759 -- Declare local variables
1760 l_proc varchar2(72) := g_package || 'get_dk_frm_yrp';
1761 l_row_fetched boolean := FALSE;
1762 l_table_rec csr_get_dk_yrp%rowtype;
1763 l_table_rec_all csr_get_all_yrp%rowtype;
1764 l_bg_id number(15);
1765
1766 begin
1767
1768 --
1769 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table BEN_YR_PERD
1770 --
1771 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1772 for x in csr_get_all_yrp loop
1773 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1774 open csr_get_dk_yrp (c_source_key => x.source_key
1775 ,c_business_group_id => l_bg_id);
1776 fetch csr_get_dk_yrp into l_table_rec;
1777 if csr_get_dk_yrp%notfound then
1778 l_row_fetched := FALSE;
1779 else
1780 l_row_fetched := TRUE;
1781 end if;
1782 close csr_get_dk_yrp;
1783
1784 --
1785 -- if no row fetched then raise exception
1786 --
1787 if not l_row_fetched then
1788 -- <<RAISE Some kind of Exception>>
1789 null;
1790 else
1791 --
1792 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1793 --
1794 ben_dm_data_util.update_pk_mapping
1795 (p_resolve_mapping_id => x.resolve_mapping_id
1796 ,p_target_id => l_table_rec.yr_perd_id);
1797 end if;
1798 --
1799 end loop;
1800 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1801 end;
1802
1803 --
1804 -- DK Resolve For Table FF_FORMULAS_F
1805 --
1806 procedure get_dk_frm_fra is
1807 --
1808 -- cursor to Fetch the DK for FF_FORMULAS_F.FORMULA_ID
1809 --
1810 cursor csr_get_dk_fra (c_business_group_id number, c_source_key varchar2) is
1811 select formula_id
1812 from ff_formulas_f
1813 where formula_name = c_source_key
1814 and business_group_id = c_business_group_id;
1815
1816 cursor csr_get_all_fra is
1817 select *
1818 from ben_dm_resolve_mappings
1819 where table_name = 'FF_FORMULAS_F'
1820 and target_id is null;
1821
1822 -- Declare local variables
1823 l_proc varchar2(72) := g_package || 'get_dk_frm_fra';
1824 l_row_fetched boolean := FALSE;
1825 l_table_rec csr_get_dk_fra%rowtype;
1826 l_table_rec_all csr_get_all_fra%rowtype;
1827 l_bg_id number(15);
1828
1829 begin
1830
1831 --
1832 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table FF_FORMULAS_F
1833 --
1834 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1835 for x in csr_get_all_fra loop
1836 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1837 open csr_get_dk_fra (c_source_key => x.source_key
1838 ,c_business_group_id => l_bg_id);
1839 fetch csr_get_dk_fra into l_table_rec;
1840 if csr_get_dk_fra%notfound then
1841 l_row_fetched := FALSE;
1842 else
1843 l_row_fetched := TRUE;
1844 end if;
1845 close csr_get_dk_fra;
1846
1847 --
1848 -- if no row fetched then raise exception
1849 --
1850 if not l_row_fetched then
1851 -- <<RAISE Some kind of Exception>>
1852 null;
1853 else
1854 --
1855 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1856 --
1857 ben_dm_data_util.update_pk_mapping
1858 (p_resolve_mapping_id => x.resolve_mapping_id
1859 ,p_target_id => l_table_rec.formula_id);
1860 end if;
1861 --
1862 end loop;
1863 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1864 end;
1865
1866 --
1867 -- DK Resolve For Table FND_ID_FLEX_STRUCTURES_VL
1868 --
1869 procedure get_dk_frm_fit is
1870 --
1871 -- cursor to Fetch the DK for FND_ID_FLEX_STRUCTURES_VL.ID_FLEX_NUM
1872 --
1873 cursor csr_get_dk_fit (c_business_group_id number, c_source_key varchar2) is
1874 select id_flex_num
1875 from fnd_id_flex_structures_vl
1876 where id_flex_structure_name = c_source_key
1877 ;
1878
1879 cursor csr_get_all_fit is
1880 select *
1881 from ben_dm_resolve_mappings
1882 where table_name = 'FND_ID_FLEX_STRUCTURES_VL'
1883 and target_id is null;
1884
1885 -- Declare local variables
1886 l_proc varchar2(72) := g_package || 'get_dk_frm_fit';
1887 l_row_fetched boolean := FALSE;
1888 l_table_rec csr_get_dk_fit%rowtype;
1889 l_table_rec_all csr_get_all_fit%rowtype;
1890 l_bg_id number(15);
1891
1892 begin
1893
1894 --
1895 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table FND_ID_FLEX_STRUCTURES_VL
1896 --
1897 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1898 for x in csr_get_all_fit loop
1899 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1900 open csr_get_dk_fit (c_source_key => x.source_key
1901 ,c_business_group_id => l_bg_id);
1902 fetch csr_get_dk_fit into l_table_rec;
1903 if csr_get_dk_fit%notfound then
1904 l_row_fetched := FALSE;
1905 else
1906 l_row_fetched := TRUE;
1907 end if;
1908 close csr_get_dk_fit;
1909
1910 --
1911 -- if no row fetched then raise exception
1912 --
1913 if not l_row_fetched then
1914 -- <<RAISE Some kind of Exception>>
1915 null;
1916 else
1917 --
1918 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1919 --
1920 ben_dm_data_util.update_pk_mapping
1921 (p_resolve_mapping_id => x.resolve_mapping_id
1922 ,p_target_id => l_table_rec.id_flex_num);
1923 end if;
1924 --
1925 end loop;
1926 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1927 end;
1928
1929 --
1930 -- DK Resolve For Table FND_USER
1931 --
1932 procedure get_dk_frm_fus is
1933 --
1934 -- cursor to Fetch the DK for FND_USER.USER_ID
1935 --
1936 cursor csr_get_dk_fus (c_business_group_id number, c_source_key varchar2) is
1937 select user_id
1938 from fnd_user
1939 where user_name = c_source_key
1940 ;
1941
1942 cursor csr_get_all_fus is
1943 select *
1944 from ben_dm_resolve_mappings
1945 where table_name = 'FND_USER'
1946 and target_id is null;
1947
1948 -- Declare local variables
1949 l_proc varchar2(72) := g_package || 'get_dk_frm_fus';
1950 l_row_fetched boolean := FALSE;
1951 l_table_rec csr_get_dk_fus%rowtype;
1952 l_table_rec_all csr_get_all_fus%rowtype;
1953 l_bg_id number(15);
1954
1955 begin
1956
1957 --
1958 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table FND_USER
1959 --
1960 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
1961 for x in csr_get_all_fus loop
1962 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
1963 open csr_get_dk_fus (c_source_key => x.source_key
1964 ,c_business_group_id => l_bg_id);
1965 fetch csr_get_dk_fus into l_table_rec;
1966 if csr_get_dk_fus%notfound then
1967 l_row_fetched := FALSE;
1968 else
1969 l_row_fetched := TRUE;
1970 end if;
1971 close csr_get_dk_fus;
1972
1973 --
1974 -- if no row fetched then raise exception
1975 --
1976 if not l_row_fetched then
1977 -- <<RAISE Some kind of Exception>>
1978 null;
1979 else
1980 --
1981 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
1982 --
1983 ben_dm_data_util.update_pk_mapping
1984 (p_resolve_mapping_id => x.resolve_mapping_id
1985 ,p_target_id => l_table_rec.user_id);
1986 end if;
1987 --
1988 end loop;
1989 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
1990 end;
1991
1992 --
1993 -- DK Resolve For Table HR_ALL_ORGANIZATION_UNITS
1994 --
1995 procedure get_dk_frm_aou is
1996 --
1997 -- cursor to Fetch the DK for HR_ALL_ORGANIZATION_UNITS.PERSON_ID
1998 --
1999 cursor csr_get_dk_aou (c_business_group_id number, c_source_key varchar2) is
2000 select organization_id
2001 from hr_all_organization_units
2002 where name = c_source_key
2003 and business_group_id = c_business_group_id;
2004
2005 cursor csr_get_all_aou is
2006 select *
2007 from ben_dm_resolve_mappings
2008 where table_name = 'HR_ALL_ORGANIZATION_UNITS'
2009 and target_id is null;
2010
2011 -- Declare local variables
2012 l_proc varchar2(72) := g_package || 'get_dk_frm_aou';
2013 l_row_fetched boolean := FALSE;
2014 l_table_rec csr_get_dk_aou%rowtype;
2015 l_table_rec_all csr_get_all_aou%rowtype;
2016 l_bg_id number(15);
2017
2018 begin
2019
2020 --
2021 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table HR_ALL_ORGANIZATION_UNITS
2022 --
2023 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2024 for x in csr_get_all_aou loop
2025 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2026 open csr_get_dk_aou (c_source_key => x.source_key
2027 ,c_business_group_id => l_bg_id);
2028 fetch csr_get_dk_aou into l_table_rec;
2029 if csr_get_dk_aou%notfound then
2030 l_row_fetched := FALSE;
2031 else
2032 l_row_fetched := TRUE;
2033 end if;
2034 close csr_get_dk_aou;
2035
2036 --
2037 -- if no row fetched then raise exception
2038 --
2039 if not l_row_fetched then
2040 -- <<RAISE Some kind of Exception>>
2041 null;
2042 else
2043 --
2044 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2045 --
2046 ben_dm_data_util.update_pk_mapping
2047 (p_resolve_mapping_id => x.resolve_mapping_id
2048 ,p_target_id => l_table_rec.organization_id);
2049 end if;
2050 --
2051 end loop;
2052 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2053 end;
2054
2055
2056
2057
2058 -- DK Resolve For Table HR_ALL_ORGANIZATION_UNITS
2059 -- for business greoup
2060 --
2061 procedure get_dk_frm_ori is
2062 --
2063 -- cursor to Fetch the DK for HR_ALL_ORGANIZATION_UNITS.PERSON_ID
2064 --
2065 cursor csr_get_dk_ori (c_business_group_id number, c_source_key varchar2) is
2066 select organization_id
2067 from hr_all_organization_units
2068 where name = c_source_key
2069 and business_group_id = c_business_group_id;
2070
2071 cursor csr_get_all_ori is
2072 select *
2073 from ben_dm_resolve_mappings
2074 where table_name = 'HR_ORGANIZATION_INFORMATION'
2075 and target_id is null;
2076
2077 -- Declare local variables
2078 l_proc varchar2(72) := g_package || 'get_dk_frm_aou';
2079 l_row_fetched boolean := FALSE;
2080 l_table_rec csr_get_dk_ori%rowtype;
2081 l_table_rec_all csr_get_all_ori%rowtype;
2082 l_bg_id number(15);
2083
2084
2085 begin
2086
2087 --
2088 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table HR_ALL_ORGANIZATION_UNITS
2089 --
2090 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2091 for x in csr_get_all_ori loop
2092 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2093 open csr_get_dk_ori (c_source_key => x.source_key
2094 ,c_business_group_id => l_bg_id);
2095 fetch csr_get_dk_ori into l_table_rec;
2096 if csr_get_dk_ori%notfound then
2097 l_row_fetched := FALSE;
2098 else
2099 l_row_fetched := TRUE;
2100 end if;
2101 close csr_get_dk_ori;
2102
2103 --
2104 -- if no row fetched then raise exception
2105 --
2106 if not l_row_fetched then
2107 -- <<RAISE Some kind of Exception>>
2108 null;
2109 else
2110 --
2111 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2112 --
2113 ben_dm_data_util.update_pk_mapping
2114 (p_resolve_mapping_id => x.resolve_mapping_id
2115 ,p_target_id => l_table_rec.organization_id);
2116 end if;
2117 --
2118 end loop;
2119 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2120 end;
2121
2122
2123
2124
2125 --
2126 -- DK Resolve For Table HR_LOCATIONS_ALL
2127 --
2128 procedure get_dk_frm_loc is
2129 --
2130 -- cursor to Fetch the DK for HR_LOCATIONS_ALL.LOCATION_ID
2131 --
2132 cursor csr_get_dk_loc (c_business_group_id number, c_source_key varchar2) is
2133 select location_id
2134 from hr_locations_all
2135 where location_code = c_source_key
2136 ;
2137
2138 cursor csr_get_all_loc is
2139 select *
2140 from ben_dm_resolve_mappings
2141 where table_name = 'HR_LOCATIONS_ALL'
2142 and target_id is null;
2143
2144 -- Declare local variables
2145 l_proc varchar2(72) := g_package || 'get_dk_frm_loc';
2146 l_row_fetched boolean := FALSE;
2147 l_table_rec csr_get_dk_loc%rowtype;
2148 l_table_rec_all csr_get_all_loc%rowtype;
2149 l_bg_id number(15);
2150
2151 begin
2152
2153 --
2154 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table HR_LOCATIONS_ALL
2155 --
2156 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2157 for x in csr_get_all_loc loop
2158 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2159 open csr_get_dk_loc (c_source_key => x.source_key
2160 ,c_business_group_id => l_bg_id);
2161 fetch csr_get_dk_loc into l_table_rec;
2162 if csr_get_dk_loc%notfound then
2163 l_row_fetched := FALSE;
2164 else
2165 l_row_fetched := TRUE;
2166 end if;
2167 close csr_get_dk_loc;
2168
2169 --
2170 -- if no row fetched then raise exception
2171 --
2172 if not l_row_fetched then
2173 -- <<RAISE Some kind of Exception>>
2174 null;
2175 else
2176 --
2177 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2178 --
2179 ben_dm_data_util.update_pk_mapping
2180 (p_resolve_mapping_id => x.resolve_mapping_id
2181 ,p_target_id => l_table_rec.location_id);
2182 end if;
2183 --
2184 end loop;
2185 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2186 end;
2187
2188 --
2189 -- DK Resolve For Table PAY_ALL_PAYROLLS_F
2190 --
2191 procedure get_dk_frm_prl is
2192 --
2193 -- cursor to Fetch the DK for PAY_ALL_PAYROLLS_F.PAYROLL_ID
2194 --
2195 cursor csr_get_dk_prl (c_business_group_id number, c_source_key varchar2) is
2196 select payroll_id
2197 from pay_all_payrolls_f
2198 where payroll_name = c_source_key
2199 and business_group_id = c_business_group_id;
2200
2201 cursor csr_get_all_prl is
2202 select *
2203 from ben_dm_resolve_mappings
2204 where table_name = 'PAY_ALL_PAYROLLS_F'
2205 and target_id is null;
2206
2207 -- Declare local variables
2208 l_proc varchar2(72) := g_package || 'get_dk_frm_prl';
2209 l_row_fetched boolean := FALSE;
2210 l_table_rec csr_get_dk_prl%rowtype;
2211 l_table_rec_all csr_get_all_prl%rowtype;
2212 l_bg_id number(15);
2213
2214 begin
2215
2216 --
2217 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PAY_ALL_PAYROLLS_F
2218 --
2219 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2220 for x in csr_get_all_prl loop
2221 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2222 open csr_get_dk_prl (c_source_key => x.source_key
2223 ,c_business_group_id => l_bg_id);
2224 fetch csr_get_dk_prl into l_table_rec;
2225 if csr_get_dk_prl%notfound then
2226 l_row_fetched := FALSE;
2227 else
2228 l_row_fetched := TRUE;
2229 end if;
2230 close csr_get_dk_prl;
2231
2232 --
2233 -- if no row fetched then raise exception
2234 --
2235 if not l_row_fetched then
2236 -- <<RAISE Some kind of Exception>>
2237 null;
2238 else
2239 --
2240 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2241 --
2242 ben_dm_data_util.update_pk_mapping
2243 (p_resolve_mapping_id => x.resolve_mapping_id
2244 ,p_target_id => l_table_rec.payroll_id);
2245 end if;
2246 --
2247 end loop;
2248 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2249 end;
2250
2251 --
2252 -- DK Resolve For Table PAY_ELEMENT_TYPES_F
2253 --
2254 procedure get_dk_frm_pet is
2255 --
2256 -- cursor to Fetch the DK for PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
2257 --
2258 cursor csr_get_dk_pet (c_business_group_id number, c_source_key varchar2) is
2259 select element_type_id
2260 from pay_element_types_f
2261 where element_name = c_source_key
2262 and (business_group_id = c_business_group_id or business_group_id is null)
2263 and (LEGISLATION_CODE = 'US' or LEGISLATION_CODE is null);
2264
2265 cursor csr_get_all_pet is
2266 select *
2267 from ben_dm_resolve_mappings
2268 where table_name = 'PAY_ELEMENT_TYPES_F'
2269 and target_id is null;
2270
2271 -- Declare local variables
2272 l_proc varchar2(72) := g_package || 'get_dk_frm_pet';
2273 l_row_fetched boolean := FALSE;
2274 l_table_rec csr_get_dk_pet%rowtype;
2275 l_table_rec_all csr_get_all_pet%rowtype;
2276 l_bg_id number(15);
2277
2278 begin
2279
2280 --
2281 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PAY_ELEMENT_TYPES_F
2282 --
2283 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2284 for x in csr_get_all_pet loop
2285 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2286 open csr_get_dk_pet (c_source_key => x.source_key
2287 ,c_business_group_id => l_bg_id);
2288 fetch csr_get_dk_pet into l_table_rec;
2289 if csr_get_dk_pet%notfound then
2290 l_row_fetched := FALSE;
2291 else
2292 l_row_fetched := TRUE;
2293 end if;
2294 close csr_get_dk_pet;
2295
2296 --
2297 -- if no row fetched then raise exception
2298 --
2299 if not l_row_fetched then
2300 -- <<RAISE Some kind of Exception>>
2301 null;
2302 else
2303 --
2304 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2305 --
2306 ben_dm_data_util.update_pk_mapping
2307 (p_resolve_mapping_id => x.resolve_mapping_id
2308 ,p_target_id => l_table_rec.element_type_id);
2309 end if;
2310 --
2311 end loop;
2312 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2313 end;
2314
2315 --
2316 -- DK Resolve For Table PAY_INPUT_VALUES_F
2317 --
2318 procedure get_dk_frm_ipv is
2319 --
2320 -- cursor to Fetch the DK for PAY_INPUT_VALUES_F.INPUT_VALUE_ID
2321 --
2322 cursor csr_get_dk_ipv (c_business_group_id number, c_source_key varchar2
2323 ,c_target_id1 number) is
2324 select input_value_id
2325 from pay_input_values_f
2326 where name = c_source_key
2327 and element_type_id = c_target_id1
2328 and (business_group_id = c_business_group_id or business_group_id is null)
2329 and (LEGISLATION_CODE = 'US' or LEGISLATION_CODE is null);
2330
2331 cursor csr_get_all_ipv is
2332 select *
2333 from ben_dm_resolve_mappings
2334 where table_name = 'PAY_INPUT_VALUES_F'
2335 and target_id is null;
2336
2337 -- Declare local variables
2338 l_proc varchar2(72) := g_package || 'get_dk_frm_ipv';
2339 l_row_fetched boolean := FALSE;
2340 l_table_rec csr_get_dk_ipv%rowtype;
2341 l_table_rec_all csr_get_all_ipv%rowtype;
2342 l_bg_id number(15);
2343 l_target_id1 number(15);
2344
2345
2346 begin
2347
2348 --
2349 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PAY_INPUT_VALUES_F
2350 --
2351 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2352 for x in csr_get_all_ipv loop
2353 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2354 -- Get ELEMENT_TYPE_ID
2355 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
2356 --
2357 open csr_get_dk_ipv (c_source_key => x.source_key
2358 ,c_business_group_id => l_bg_id
2359 ,c_target_id1 => l_target_id1);
2360 fetch csr_get_dk_ipv into l_table_rec;
2361 if csr_get_dk_ipv%notfound then
2362 l_row_fetched := FALSE;
2363 else
2364 l_row_fetched := TRUE;
2365 end if;
2366 close csr_get_dk_ipv;
2367
2368 --
2369 -- if no row fetched then raise exception
2370 --
2371 if not l_row_fetched then
2372 -- <<RAISE Some kind of Exception>>
2373 null;
2374 else
2375 --
2376 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2377 --
2378 ben_dm_data_util.update_pk_mapping
2379 (p_resolve_mapping_id => x.resolve_mapping_id
2380 ,p_target_id => l_table_rec.input_value_id);
2381 end if;
2382 --
2383 end loop;
2384 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2385 end;
2386
2387 --
2388 -- DK Resolve For Table PER_ASSIGNMENT_STATUS_TYPES
2389 --
2390 procedure get_dk_frm_ast is
2391 --
2392 -- cursor to Fetch the DK for PER_ASSIGNMENT_STATUS_TYPES.ASSIGNMENT_STATUS_TYPE_ID
2393 --
2394 cursor csr_get_dk_ast (c_user_status varchar2,
2395 c_leg_code varchar2,
2396 c_bg_id number) is
2397 select assignment_status_type_id
2398 from per_assignment_status_types
2399 where user_status = c_user_status
2400 and nvl(legislation_code,'-x') = c_leg_code
2401 and nvl(business_group_id,-1) = nvl(c_bg_id,-1) ;
2402
2403 cursor csr_get_all_ast is
2404 select *
2405 from ben_dm_resolve_mappings
2406 where table_name = 'PER_ASSIGNMENT_STATUS_TYPES'
2407 and target_id is null;
2408
2409 -- Declare local variables
2410 l_proc varchar2(72) := g_package || 'get_dk_frm_ast';
2411 l_row_fetched boolean := FALSE;
2412 l_table_rec csr_get_dk_ast%rowtype;
2413 l_table_rec_all csr_get_all_ast%rowtype;
2414 l_bg_id number(15);
2415 l_user_status varchar2(255);
2416 l_legislation_code varchar2(30);
2417 l_business_group_id number(15);
2418 l_pos1 number(15);
2419 l_pos2 number(15);
2420
2421 begin
2422
2423 --
2424 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_ASSIGNMENT_STATUS_TYPES
2425 --
2426 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2427 for x in csr_get_all_ast loop
2428 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2429 l_pos1 := instr(x.source_key,'-',1);
2430 l_pos2 := instr(x.source_key,'-',-1,1);
2431 l_user_status := substr(x.source_key,1,l_pos1-1);
2432 l_legislation_code := substr(x.source_key,l_pos1+1,l_pos2-l_pos1-1);
2433 if substr(x.source_key,l_pos2+1) = 'Y' then
2434 l_business_group_id := l_bg_id;
2435 else
2436 l_business_group_id := null;
2437 end if;
2438
2439 ben_dm_utility.message('INFO','l_pos1 '||l_pos1,5) ;
2440 ben_dm_utility.message('INFO','l_pos2 '||l_pos2,5) ;
2441 ben_dm_utility.message('INFO','l_user_status '||l_user_status,5) ;
2442 ben_dm_utility.message('INFO','l_legislation_code '||l_legislation_code,5) ;
2443 ben_dm_utility.message('INFO','l_business_group_id '||l_business_group_id,5) ;
2444 open csr_get_dk_ast (c_user_status => l_user_status
2445 ,c_leg_code => l_legislation_code
2446 ,c_bg_id => l_business_group_id);
2447 fetch csr_get_dk_ast into l_table_rec;
2448 if csr_get_dk_ast%notfound then
2449 l_row_fetched := FALSE;
2450 else
2451 l_row_fetched := TRUE;
2452 end if;
2453 close csr_get_dk_ast;
2454
2455 --
2456 -- if no row fetched then raise exception
2457 --
2458 if not l_row_fetched then
2459 -- <<RAISE Some kind of Exception>>
2460 null;
2461 else
2462 --
2463 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2464 --
2465 ben_dm_data_util.update_pk_mapping
2466 (p_resolve_mapping_id => x.resolve_mapping_id
2467 ,p_target_id => l_table_rec.assignment_status_type_id);
2468 end if;
2469 --
2470 end loop;
2471 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2472 end;
2473
2474 --
2475 -- DK Resolve For Table PER_GRADES
2476 --
2477 procedure get_dk_frm_gra is
2478 --
2479 -- cursor to Fetch the DK for PER_GRADES.GRADE_ID
2480 --
2481 cursor csr_get_dk_gra (c_business_group_id number, c_source_key varchar2) is
2482 select grade_id
2483 from per_grades
2484 where name = c_source_key
2485 ;
2486
2487 cursor csr_get_all_gra is
2488 select *
2489 from ben_dm_resolve_mappings
2490 where table_name = 'PER_GRADES'
2491 and target_id is null;
2492
2493 -- Declare local variables
2494 l_proc varchar2(72) := g_package || 'get_dk_frm_gra';
2495 l_row_fetched boolean := FALSE;
2496 l_table_rec csr_get_dk_gra%rowtype;
2497 l_table_rec_all csr_get_all_gra%rowtype;
2498 l_bg_id number(15);
2499
2500 begin
2501
2502 --
2503 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_GRADES
2504 --
2505 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2506 for x in csr_get_all_gra loop
2507 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2508 open csr_get_dk_gra (c_source_key => x.source_key
2509 ,c_business_group_id => l_bg_id);
2510 fetch csr_get_dk_gra into l_table_rec;
2511 if csr_get_dk_gra%notfound then
2512 l_row_fetched := FALSE;
2513 else
2514 l_row_fetched := TRUE;
2515 end if;
2516 close csr_get_dk_gra;
2517
2518 --
2519 -- if no row fetched then raise exception
2520 --
2521 if not l_row_fetched then
2522 -- <<RAISE Some kind of Exception>>
2523 null;
2524 else
2525 --
2526 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2527 --
2528 ben_dm_data_util.update_pk_mapping
2529 (p_resolve_mapping_id => x.resolve_mapping_id
2530 ,p_target_id => l_table_rec.grade_id);
2531 end if;
2532 --
2533 end loop;
2534 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2535 end;
2536
2537 --
2538 -- DK Resolve For Table PER_JOBS
2539 --
2540 procedure get_dk_frm_job is
2541 --
2542 -- cursor to Fetch the DK for PER_JOBS.JOB_ID
2543 --
2544 cursor csr_get_dk_job (c_business_group_id number, c_source_key varchar2) is
2545 select job_id
2546 from per_jobs
2547 where name = c_source_key
2548 ;
2549
2550 cursor csr_get_all_job is
2551 select *
2552 from ben_dm_resolve_mappings
2553 where table_name = 'PER_JOBS'
2554 and target_id is null;
2555
2556 -- Declare local variables
2557 l_proc varchar2(72) := g_package || 'get_dk_frm_job';
2558 l_row_fetched boolean := FALSE;
2559 l_table_rec csr_get_dk_job%rowtype;
2560 l_table_rec_all csr_get_all_job%rowtype;
2561 l_bg_id number(15);
2562
2563 begin
2564
2565 --
2566 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_JOBS
2567 --
2568 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2569 for x in csr_get_all_job loop
2570 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2571 open csr_get_dk_job (c_source_key => x.source_key
2572 ,c_business_group_id => l_bg_id);
2573 fetch csr_get_dk_job into l_table_rec;
2574 if csr_get_dk_job%notfound then
2575 l_row_fetched := FALSE;
2576 else
2577 l_row_fetched := TRUE;
2578 end if;
2579 close csr_get_dk_job;
2580
2581 --
2582 -- if no row fetched then raise exception
2583 --
2584 if not l_row_fetched then
2585 -- <<RAISE Some kind of Exception>>
2586 null;
2587 else
2588 --
2589 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2590 --
2591 ben_dm_data_util.update_pk_mapping
2592 (p_resolve_mapping_id => x.resolve_mapping_id
2593 ,p_target_id => l_table_rec.job_id);
2594 end if;
2595 --
2596 end loop;
2597 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2598 end;
2599
2600 --
2601 -- DK Resolve For Table PER_PAY_BASES
2602 --
2603 procedure get_dk_frm_pyb is
2604 --
2605 -- cursor to Fetch the DK for PER_PAY_BASES.PAY_BASIS_ID
2606 --
2607 cursor csr_get_dk_pyb (c_business_group_id number, c_source_key varchar2) is
2608 select pay_basis_id
2609 from per_pay_bases
2610 where name = c_source_key
2611 ;
2612
2613 cursor csr_get_all_pyb is
2614 select *
2615 from ben_dm_resolve_mappings
2616 where table_name = 'PER_PAY_BASES'
2617 and target_id is null;
2618
2619 -- Declare local variables
2620 l_proc varchar2(72) := g_package || 'get_dk_frm_pyb';
2621 l_row_fetched boolean := FALSE;
2622 l_table_rec csr_get_dk_pyb%rowtype;
2623 l_table_rec_all csr_get_all_pyb%rowtype;
2624 l_bg_id number(15);
2625
2626 begin
2627
2628 --
2629 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_PAY_BASES
2630 --
2631 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2632 for x in csr_get_all_pyb loop
2633 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2634 open csr_get_dk_pyb (c_source_key => x.source_key
2635 ,c_business_group_id => l_bg_id);
2636 fetch csr_get_dk_pyb into l_table_rec;
2637 if csr_get_dk_pyb%notfound then
2638 l_row_fetched := FALSE;
2639 else
2640 l_row_fetched := TRUE;
2641 end if;
2642 close csr_get_dk_pyb;
2643
2644 --
2645 -- if no row fetched then raise exception
2646 --
2647 if not l_row_fetched then
2648 -- <<RAISE Some kind of Exception>>
2649 null;
2650 else
2651 --
2652 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2653 --
2654 ben_dm_data_util.update_pk_mapping
2655 (p_resolve_mapping_id => x.resolve_mapping_id
2656 ,p_target_id => l_table_rec.pay_basis_id);
2657 end if;
2658 --
2659 end loop;
2660 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2661 end;
2662
2663 --
2664 -- DK Resolve For Table PER_PERSON_TYPES
2665 --
2666 procedure get_dk_frm_prt is
2667 --
2668 -- cursor to Fetch the DK for PER_PERSON_TYPES.PERSON_TYPE_ID
2669 --
2670 cursor csr_get_dk_prt (c_business_group_id number, c_source_key varchar2) is
2671 select person_type_id
2672 from per_person_types
2673 where user_person_type = c_source_key
2674 and business_group_id = c_business_group_id ;
2675
2676 cursor csr_get_all_prt is
2677 select *
2678 from ben_dm_resolve_mappings
2679 where table_name = 'PER_PERSON_TYPES'
2680 and target_id is null;
2681
2682 -- Declare local variables
2683 l_proc varchar2(72) := g_package || 'get_dk_frm_prt';
2684 l_row_fetched boolean := FALSE;
2685 l_table_rec csr_get_dk_prt%rowtype;
2686 l_table_rec_all csr_get_all_prt%rowtype;
2687 l_bg_id number(15);
2688
2689 begin
2690
2691 --
2692 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_PERSON_TYPES
2693 --
2694 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2695 for x in csr_get_all_prt loop
2696 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2697 open csr_get_dk_prt (c_source_key => x.source_key
2698 ,c_business_group_id => l_bg_id);
2699 fetch csr_get_dk_prt into l_table_rec;
2700 if csr_get_dk_prt%notfound then
2701 l_row_fetched := FALSE;
2702 else
2703 l_row_fetched := TRUE;
2704 end if;
2705 close csr_get_dk_prt;
2706
2707 --
2708 -- if no row fetched then raise exception
2709 --
2710 if not l_row_fetched then
2711 -- <<RAISE Some kind of Exception>>
2712 null;
2713 else
2714 --
2715 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2716 --
2717 ben_dm_data_util.update_pk_mapping
2718 (p_resolve_mapping_id => x.resolve_mapping_id
2719 ,p_target_id => l_table_rec.person_type_id);
2720 end if;
2721 --
2722 end loop;
2723 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2724 end;
2725 --
2726 -- DK Resolve For Table PER_ABSENCE_ATTENDANCE_TYPES
2727 --
2728 procedure get_dk_frm_aat is
2729 --
2730 -- cursor to Fetch the DK for PER_ABSENCE_ATTENDANCE_TYPES.ABSENCE_ATTENDANCE_TYPE_ID
2731 --
2732 cursor csr_get_dk_aat (c_source_key varchar2,
2733 c_business_group_id number) is
2734 select absence_attendance_type_id
2735 from per_absence_attendance_types
2736 where name = c_source_key
2737 and business_group_id = c_business_group_id;
2738
2739 cursor csr_get_all_aat is
2740 select *
2741 from ben_dm_resolve_mappings
2742 where table_name = 'PER_ABSENCE_ATTENDANCE_TYPES'
2743 and target_id is null;
2744
2745 -- Declare local variables
2746 l_proc varchar2(72) := g_package || 'get_dk_frm_aat';
2747 l_row_fetched boolean := FALSE;
2748 l_table_rec csr_get_dk_aat%rowtype;
2749 l_table_rec_all csr_get_all_aat%rowtype;
2750 l_business_group_id number(15);
2751
2752 begin
2753
2754 --
2755 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_ABSENCE_ATTENDANCE_TYPES
2756 --
2757 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2758 for x in csr_get_all_aat loop
2759 l_business_group_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2760 ben_dm_utility.message('INFO','l_business_group_id '||l_business_group_id,5) ;
2761
2762 open csr_get_dk_aat (c_source_key => x.source_key
2763 ,c_business_group_id => l_business_group_id);
2764 fetch csr_get_dk_aat into l_table_rec;
2765 if csr_get_dk_aat%notfound then
2766 l_row_fetched := FALSE;
2767 else
2768 l_row_fetched := TRUE;
2769 end if;
2770 close csr_get_dk_aat;
2771
2772 --
2773 -- if no row fetched then raise exception
2774 --
2775 if not l_row_fetched then
2776 -- <<RAISE Some kind of Exception>>
2777 null;
2778 else
2779 --
2780 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2781 --
2782 ben_dm_data_util.update_pk_mapping
2783 (p_resolve_mapping_id => x.resolve_mapping_id
2784 ,p_target_id => l_table_rec.absence_attendance_type_id);
2785 end if;
2786 --
2787 end loop;
2788 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2789 end;
2790 --
2791 -- DK Resolve For Table PER_ABS_ATTENDANCE_REASONS
2792 --
2793 procedure get_dk_frm_aar is
2794 --
2795 -- cursor to Fetch the DK for PER_ABS_ATTENDANCE_REASONS.ABS_ATTENDANCE_REASON_ID
2796 --
2797 cursor csr_get_dk_aar (c_source_key varchar2,
2798 c_business_group_id number,
2799 c_target_id1 number) is
2800 select abs_attendance_reason_id
2801 from per_abs_attendance_reasons
2802 where name = c_source_key
2803 and absence_attendance_type_id = c_target_id1
2804 and business_group_id = c_business_group_id;
2805
2806 cursor csr_get_all_aar is
2807 select *
2808 from ben_dm_resolve_mappings
2809 where table_name = 'PER_ABS_ATTENDANCE_REASONS'
2810 and target_id is null;
2811
2812 -- Declare local variables
2813 l_proc varchar2(72) := g_package || 'get_dk_frm_aar';
2814 l_row_fetched boolean := FALSE;
2815 l_table_rec csr_get_dk_aar%rowtype;
2816 l_table_rec_all csr_get_all_aar%rowtype;
2817 l_business_group_id number(15);
2818 l_target_id1 number(15);
2819 --
2820 begin
2821
2822 --
2823 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PER_ABS_ATTENDANCE_REASONS
2824 --
2825 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2826 for x in csr_get_all_aar loop
2827 l_business_group_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2828 ben_dm_utility.message('INFO','l_business_group_id '||l_business_group_id,5) ;
2829 --
2830 l_target_id1 := get_target_id_for_mapping(x.resolve_mapping_id1);
2831 --
2832 open csr_get_dk_aar (c_source_key => x.source_key
2833 ,c_business_group_id => l_business_group_id
2834 ,c_target_id1 => l_target_id1 );
2835 fetch csr_get_dk_aar into l_table_rec;
2836 if csr_get_dk_aar%notfound then
2837 l_row_fetched := FALSE;
2838 else
2839 l_row_fetched := TRUE;
2840 end if;
2841 close csr_get_dk_aar;
2842
2843 --
2844 -- if no row fetched then raise exception
2845 --
2846 if not l_row_fetched then
2847 -- <<RAISE Some kind of Exception>>
2848 null;
2849 else
2850 --
2851 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2852 --
2853 ben_dm_data_util.update_pk_mapping
2854 (p_resolve_mapping_id => x.resolve_mapping_id
2855 ,p_target_id => l_table_rec.abs_attendance_reason_id);
2856 end if;
2857 --
2858 end loop;
2859 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2860 end;
2861 --
2862 --
2863 -- DK Resolve For Table HR_SOFT_CODING_KEYFLEX
2864 --
2865 procedure get_dk_frm_scl is
2866 --
2867 -- cursor to Fetch the DK for HR_SOFT_CODING_KEYFLEX
2868 --
2869 cursor csr_get_dk_scl (c_entity_result_id number) is
2870 select *
2871 from ben_dm_entity_results
2872 where entity_result_id = c_entity_result_id;
2873
2874 cursor csr_get_all_scl is
2875 select *
2876 from ben_dm_resolve_mappings
2877 where table_name = 'HR_SOFT_CODING_KEYFLEX'
2878 and target_id is null;
2879
2880 -- Declare local variables
2881 l_proc varchar2(72) := g_package || 'get_dk_frm_scl';
2882 l_row_fetched boolean := FALSE;
2883 l_table_rec csr_get_dk_scl%rowtype;
2884 l_table_rec_all csr_get_all_scl%rowtype;
2885 l_bg_id number(15);
2886 l_target_id number(15);
2887 l_concatenated_segments varchar2(700);
2888
2889
2890 begin
2891
2892 --
2893 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table HR_SOFT_CODING_KEYFLEX
2894 --
2895 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
2896 for x in csr_get_all_scl loop
2897 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
2898 open csr_get_dk_scl (c_entity_result_id => x.resolve_mapping_id1);
2899 fetch csr_get_dk_scl into l_table_rec;
2900 if csr_get_dk_scl%notfound then
2901 l_row_fetched := FALSE;
2902 else
2903 l_row_fetched := TRUE;
2904 end if;
2905 close csr_get_dk_scl;
2906
2907 --
2908 -- if no row fetched then raise exception
2909 --
2910 if not l_row_fetched then
2911 -- <<RAISE Some kind of Exception>>
2912 null;
2913 else
2914 --
2915 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
2916 --
2917
2918 if l_table_rec.INFORMATION4 is not null then
2919 l_table_rec.INFORMATION4 := l_bg_id;
2920 end if;
2921
2922 hr_scl_ins.ins_or_sel
2923 ( P_SEGMENT1 => l_table_rec.INFORMATION4
2924 ,P_SEGMENT2 => l_table_rec.INFORMATION5
2925 ,P_SEGMENT3 => l_table_rec.INFORMATION6
2926 ,P_SEGMENT4 => l_table_rec.INFORMATION7
2927 ,P_SEGMENT5 => l_table_rec.INFORMATION8
2928 ,P_SEGMENT6 => l_table_rec.INFORMATION9
2929 ,P_SEGMENT7 => l_table_rec.INFORMATION10
2930 ,P_SEGMENT8 => l_table_rec.INFORMATION11
2931 ,P_SEGMENT9 => l_table_rec.INFORMATION12
2932 ,P_SEGMENT10 => l_table_rec.INFORMATION13
2933 ,P_SEGMENT11 => l_table_rec.INFORMATION14
2934 ,P_SEGMENT12 => l_table_rec.INFORMATION15
2935 ,P_SEGMENT13 => l_table_rec.INFORMATION16
2936 ,P_SEGMENT14 => l_table_rec.INFORMATION17
2937 ,P_SEGMENT15 => l_table_rec.INFORMATION18
2938 ,P_SEGMENT16 => l_table_rec.INFORMATION19
2939 ,P_SEGMENT17 => l_table_rec.INFORMATION20
2940 ,P_SEGMENT18 => l_table_rec.INFORMATION21
2941 ,P_SEGMENT19 => l_table_rec.INFORMATION22
2942 ,P_SEGMENT20 => l_table_rec.INFORMATION23
2943 ,P_SEGMENT21 => l_table_rec.INFORMATION24
2944 ,P_SEGMENT22 => l_table_rec.INFORMATION25
2945 ,P_SEGMENT23 => l_table_rec.INFORMATION26
2946 ,P_SEGMENT24 => l_table_rec.INFORMATION27
2947 ,P_SEGMENT25 => l_table_rec.INFORMATION28
2948 ,P_SEGMENT26 => l_table_rec.INFORMATION29
2949 ,P_SEGMENT27 => l_table_rec.INFORMATION30
2950 ,P_SEGMENT28 => l_table_rec.INFORMATION31
2951 ,P_SEGMENT29 => l_table_rec.INFORMATION32
2952 ,P_SEGMENT30 => l_table_rec.INFORMATION33
2953 ,p_business_group_id => l_bg_id
2954 ,p_soft_coding_keyflex_id => l_target_id
2955 ,p_concatenated_segments => l_concatenated_segments
2956 ,p_validate => FALSE);
2957
2958 ben_dm_data_util.update_pk_mapping
2959 (p_resolve_mapping_id => x.resolve_mapping_id
2960 ,p_target_id => l_target_id);
2961 end if;
2962 --
2963 end loop;
2964 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
2965 end;
2966
2967 --
2968 -- DK Resolve For Table PAY_PEOPLE_GROUPS
2969 --
2970 procedure get_dk_frm_peg is
2971 --
2972 -- cursor to Fetch the DK for PAY_PEOPLE_GROUPS
2973 --
2974 cursor csr_get_dk_peg (c_entity_result_id number) is
2975 select *
2976 from ben_dm_entity_results
2977 where entity_result_id = c_entity_result_id;
2978
2979 cursor csr_get_all_peg is
2980 select *
2981 from ben_dm_resolve_mappings
2982 where table_name = 'PAY_PEOPLE_GROUPS'
2983 and target_id is null;
2984
2985 -- Declare local variables
2986 l_proc varchar2(72) := g_package || 'get_dk_frm_peg';
2987 l_row_fetched boolean := FALSE;
2988 l_table_rec csr_get_dk_peg%rowtype;
2989 l_table_rec_all csr_get_all_peg%rowtype;
2990 l_bg_id number(15);
2991 l_target_id number(15);
2992 l_group_name varchar2(700);
2993 l_people_group_structure number(15);
2994
2995 begin
2996
2997 --
2998 -- Select all rows from BEN_DM_RESOLVE_MAPPINGS for table PAY_PEOPLE_GROUPS
2999 --
3000 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
3001 for x in csr_get_all_peg loop
3002
3003 hr_general.g_data_migrator_mode := 'Y';
3004
3005 l_bg_id := ben_dm_data_util.get_bg_id(x.business_group_name);
3006
3007 open csr_get_dk_peg (c_entity_result_id => x.resolve_mapping_id1);
3008 fetch csr_get_dk_peg into l_table_rec;
3009 if csr_get_dk_peg%notfound then
3010 l_row_fetched := FALSE;
3011 else
3012 l_row_fetched := TRUE;
3013 end if;
3014 close csr_get_dk_peg;
3015
3016 --
3017 -- if no row fetched then raise exception
3018 --
3019 if not l_row_fetched then
3020 -- <<RAISE Some kind of Exception>>
3021 null;
3022 else
3023 --
3024 -- Update DK data into BEN_DM_RESOLVE_MAPPINGS table.
3025 --
3026
3027 select people_group_structure
3028 into l_people_group_structure
3029 from per_business_groups
3030 where business_group_id = l_bg_id;
3031
3032 hr_kflex_utility.INS_OR_SEL_KEYFLEX_COMB
3033 ( P_SEGMENT1 => l_table_rec.INFORMATION4
3034 ,P_SEGMENT2 => l_table_rec.INFORMATION5
3035 ,P_SEGMENT3 => l_table_rec.INFORMATION6
3036 ,P_SEGMENT4 => l_table_rec.INFORMATION7
3037 ,P_SEGMENT5 => l_table_rec.INFORMATION8
3038 ,P_SEGMENT6 => l_table_rec.INFORMATION9
3039 ,P_SEGMENT7 => l_table_rec.INFORMATION10
3040 ,P_SEGMENT8 => l_table_rec.INFORMATION11
3041 ,P_SEGMENT9 => l_table_rec.INFORMATION12
3042 ,P_SEGMENT10 => l_table_rec.INFORMATION13
3043 ,P_SEGMENT11 => l_table_rec.INFORMATION14
3044 ,P_SEGMENT12 => l_table_rec.INFORMATION15
3045 ,P_SEGMENT13 => l_table_rec.INFORMATION16
3046 ,P_SEGMENT14 => l_table_rec.INFORMATION17
3047 ,P_SEGMENT15 => l_table_rec.INFORMATION18
3048 ,P_SEGMENT16 => l_table_rec.INFORMATION19
3049 ,P_SEGMENT17 => l_table_rec.INFORMATION20
3050 ,P_SEGMENT18 => l_table_rec.INFORMATION21
3051 ,P_SEGMENT19 => l_table_rec.INFORMATION22
3052 ,P_SEGMENT20 => l_table_rec.INFORMATION23
3053 ,P_SEGMENT21 => l_table_rec.INFORMATION24
3054 ,P_SEGMENT22 => l_table_rec.INFORMATION25
3055 ,P_SEGMENT23 => l_table_rec.INFORMATION26
3056 ,P_SEGMENT24 => l_table_rec.INFORMATION27
3057 ,P_SEGMENT25 => l_table_rec.INFORMATION28
3058 ,P_SEGMENT26 => l_table_rec.INFORMATION29
3059 ,P_SEGMENT27 => l_table_rec.INFORMATION30
3060 ,P_SEGMENT28 => l_table_rec.INFORMATION31
3061 ,P_SEGMENT29 => l_table_rec.INFORMATION32
3062 ,P_SEGMENT30 => l_table_rec.INFORMATION33
3063 ,p_appl_short_name => 'PAY'
3064 ,p_flex_code => 'GRP'
3065 ,p_flex_num => l_people_group_structure
3066 ,p_concat_segments_in => null
3067 ,p_ccid => l_target_id
3068 ,p_concat_segments_out => l_group_name);
3069
3070
3071 /*
3072
3073 pay_pgp_ins.ins_or_sel
3074 ( P_SEGMENT1 => l_table_rec.INFORMATION4
3075 ,P_SEGMENT2 => l_table_rec.INFORMATION5
3076 ,P_SEGMENT3 => l_table_rec.INFORMATION6
3077 ,P_SEGMENT4 => l_table_rec.INFORMATION7
3078 ,P_SEGMENT5 => l_table_rec.INFORMATION8
3079 ,P_SEGMENT6 => l_table_rec.INFORMATION9
3080 ,P_SEGMENT7 => l_table_rec.INFORMATION10
3081 ,P_SEGMENT8 => l_table_rec.INFORMATION11
3082 ,P_SEGMENT9 => l_table_rec.INFORMATION12
3083 ,P_SEGMENT10 => l_table_rec.INFORMATION13
3084 ,P_SEGMENT11 => l_table_rec.INFORMATION14
3085 ,P_SEGMENT12 => l_table_rec.INFORMATION15
3086 ,P_SEGMENT13 => l_table_rec.INFORMATION16
3087 ,P_SEGMENT14 => l_table_rec.INFORMATION17
3088 ,P_SEGMENT15 => l_table_rec.INFORMATION18
3089 ,P_SEGMENT16 => l_table_rec.INFORMATION19
3090 ,P_SEGMENT17 => l_table_rec.INFORMATION20
3091 ,P_SEGMENT18 => l_table_rec.INFORMATION21
3092 ,P_SEGMENT19 => l_table_rec.INFORMATION22
3093 ,P_SEGMENT20 => l_table_rec.INFORMATION23
3094 ,P_SEGMENT21 => l_table_rec.INFORMATION24
3095 ,P_SEGMENT22 => l_table_rec.INFORMATION25
3096 ,P_SEGMENT23 => l_table_rec.INFORMATION26
3097 ,P_SEGMENT24 => l_table_rec.INFORMATION27
3098 ,P_SEGMENT25 => l_table_rec.INFORMATION28
3099 ,P_SEGMENT26 => l_table_rec.INFORMATION29
3100 ,P_SEGMENT27 => l_table_rec.INFORMATION30
3101 ,P_SEGMENT28 => l_table_rec.INFORMATION31
3102 ,P_SEGMENT29 => l_table_rec.INFORMATION32
3103 ,P_SEGMENT30 => l_table_rec.INFORMATION33
3104 ,p_business_group_id => l_bg_id
3105 ,p_people_group_id => l_target_id
3106 ,p_group_name => l_group_name
3107 ,p_validate => FALSE);
3108
3109 */
3110 ben_dm_data_util.update_pk_mapping
3111 (p_resolve_mapping_id => x.resolve_mapping_id
3112 ,p_target_id => l_target_id);
3113 end if;
3114 --
3115 end loop;
3116 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
3117 end;
3118 --
3119 -- Genegeral procedure to call all other dk procedire
3120
3121 Procedure get_dk_frm_all is
3122
3123 cursor csr_get_no_target is
3124 select *
3125 from ben_dm_resolve_mappings
3126 where target_id is null;
3127
3128
3129 l_proc varchar2(75) ;
3130 l_unresolved_dk boolean := FALSE;
3131 no_dk exception;
3132
3133 Begin
3134 l_proc := g_package|| 'get_dk_frm_all' ;
3135 ben_dm_utility.message('INFO','Entering : ' || l_proc,5) ;
3136
3137 hr_general.g_data_migrator_mode := 'Y';
3138
3139 -- DK Resolve for Table BEN_ACTL_PREM_F
3140 get_dk_frm_apr;
3141
3142 -- DK Resolve for Table BEN_ACTN_TYP
3143 get_dk_frm_eat;
3144
3145 -- DK Resolve for Table BEN_ACTY_BASE_RT_F
3146 get_dk_frm_abr;
3147
3148 -- DK Resolve for Table BEN_BENFTS_GRP
3149 get_dk_frm_bng;
3150
3151 -- DK Resolve for Table BEN_BNFTS_BAL_F
3152 get_dk_frm_bnb;
3153
3154 -- DK Resolve for Table BEN_BNFT_PRVDR_POOL_F
3155 get_dk_frm_bpp;
3156
3157 -- DK Resolve for Table BEN_CMBN_PLIP_F
3158 get_dk_frm_cpl;
3159
3160 -- DK Resolve for Table BEN_CMBN_PTIP_F
3161 get_dk_frm_cbp;
3162
3163 -- DK Resolve for Table BEN_CMBN_PTIP_OPT_F
3164 get_dk_frm_cpt;
3165
3166 -- DK Resolve for Table BEN_CM_TRGR
3167 get_dk_frm_bcr;
3168
3169 -- DK Resolve for Table BEN_CM_TYP_F
3170 get_dk_frm_cct;
3171
3172 -- DK Resolve for Table BEN_COMP_LVL_FCTR
3173 get_dk_frm_clf;
3174
3175 -- DK Resolve for Table BEN_CVG_AMT_CALC_MTHD_F
3176 get_dk_frm_ccm;
3177
3178 -- DK Resolve for Table BEN_LER_F
3179 get_dk_frm_ler;
3180
3181 -- DK Resolve for Table BEN_OPT_F
3182 get_dk_frm_opt;
3183
3184 -- DK Resolve for Table BEN_PGM_F
3185 get_dk_frm_pgm;
3186
3187 -- DK Resolve for Table BEN_PL_F
3188 get_dk_frm_pln;
3189
3190 -- DK Resolve for Table BEN_PL_TYP_F
3191 get_dk_frm_ptp;
3192
3193 -- DK Resolve for Table BEN_YR_PERD
3194 get_dk_frm_yrp;
3195
3196 -- DK Resolve for Table BEN_LEE_RSN_F
3197 get_dk_frm_len;
3198
3199 -- DK Resolve for Table FF_FORMULAS_F
3200 get_dk_frm_fra;
3201
3202 -- DK Resolve for Table FND_ID_FLEX_STRUCTURES_VL
3203 get_dk_frm_fit;
3204
3205 -- DK Resolve for Table FND_USER
3206 get_dk_frm_fus;
3207
3208 -- DK Resolve for Table HR_ALL_ORGANIZATION_UNITS
3209 get_dk_frm_aou;
3210
3211 get_dk_frm_ori ;
3212
3213 -- DK Resolve for Table HR_LOCATIONS_ALL
3214 get_dk_frm_loc;
3215
3216 -- DK Resolve for Table PAY_ALL_PAYROLLS_F
3217 get_dk_frm_prl;
3218
3219 -- DK Resolve for Table PAY_ELEMENT_TYPES_F
3220 get_dk_frm_pet;
3221
3222 -- DK Resolve for Table PAY_INPUT_VALUES_F
3223 get_dk_frm_ipv;
3224
3225 -- DK Resolve for Table PAY_ELEMENT_LINKS_F
3226 get_dk_frm_pll;
3227
3228 -- DK Resolve for Table PER_ASSIGNMENT_STATUS_TYPES
3229 get_dk_frm_ast;
3230
3231 -- DK Resolve for Table PER_GRADES
3232 get_dk_frm_gra;
3233
3234 -- DK Resolve for Table PER_JOBS
3235 get_dk_frm_job;
3236
3237 -- DK Resolve for Table PER_PAY_BASES
3238 get_dk_frm_pyb;
3239
3240 -- DK Resolve for Table PER_PERSON_TYPES
3241 get_dk_frm_prt;
3242
3243 -- DK Resolve for Table HR_SOFT_CODING_KEYFLEX
3244 get_dk_frm_scl;
3245
3246 -- DK Resolve for Table PAY_PEOPLE_GROUPS
3247 get_dk_frm_peg;
3248
3249 -- DK Resolve for Table BEN_ENRT_PERD
3250 get_dk_frm_enp;
3251
3252 -- DK Resolve for Table BEN_OIPL_F
3253 get_dk_frm_cop;
3254
3255 -- DK Resolve for Table BEN_PLIP_F
3256 get_dk_frm_cpp;
3257
3258 -- DK Resolve for Table BEN_PTIP_F
3259 get_dk_frm_ctp;
3260
3261 -- DK Resolve for Table BEN_OIPLIP_F
3262 get_dk_frm_boi;
3263
3264 -- DK Resolve for Table PER_ABSENCE_ATTENDANCE_TYPES
3265 get_dk_frm_aat;
3266
3267 -- DK Resolve for Table PER_ABS_ATTENDANCE_REASONS
3268 get_dk_frm_aar;
3269
3270 --
3271
3272 ben_dm_utility.message('INFO','Start of Report for Unresolved Developer Keys',5) ;
3273 for x in csr_get_no_target loop
3274 l_unresolved_dk := TRUE;
3275
3276 ben_dm_utility.message('INFO','Resolve_Mapping_Id : ' || x.resolve_mapping_id,5);
3277 ben_dm_utility.message('INFO','Table_Name : ' || x.table_name,5);
3278 ben_dm_utility.message('INFO','Column_Name : ' || x.column_name,5);
3279 ben_dm_utility.message('INFO','Source_Key : ' || x.source_key,5);
3280 ben_dm_utility.message('INFO','Business_Group_name : ' || x.business_group_name,5);
3281 --
3282 end loop;
3283 ben_dm_utility.message('INFO','End of Report for Unresolved Developer Keys',5);
3284
3285 if l_unresolved_dk then
3286 ben_dm_utility.message('INFO','Error : Unresolved Developer Keys',5) ;
3287 raise no_dk;
3288 end if;
3289
3290 ben_dm_utility.message('INFO','Leaving : ' || l_proc,5) ;
3291
3292 Exception
3293 when others then
3294 ben_dm_utility.message('INFO','Error : ' || substr(sqlerrm,1,100) ,5) ;
3295 raise ;
3296 end get_dk_frm_all ;
3297
3298
3299 end ben_dm_upload_dk;