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