1 PACKAGE BODY BEN_PDW_COPY_BEN_TO_STG AS
2 /* $Header: bepdwstg.pkb 120.20.12010000.1 2008/07/29 12:45:43 appldev ship $ */
3 g_package varchar2(30) :='BEN_PDW_COPY_BEN_TO_STG';
4 PROCEDURE get_txn_details (
5 p_copy_entity_txn_id IN NUMBER
6 ,p_business_group_id OUT NOCOPY NUMBER
7 ,p_effective_date OUT NOCOPY DATE
8 ) IS
9 Cursor csr_txn_details is
10 Select SRC_EFFECTIVE_DATE,CONTEXT_BUSINESS_GROUP_ID
11 From pqh_copy_entity_txns
12 Where copy_entity_txn_id=p_copy_entity_txn_id;
13 BEGIN
14 OPEN csr_txn_details;
15 FETCH csr_txn_details into p_effective_date, p_business_group_id ;
16 CLOSE csr_txn_details;
17 END get_txn_details ;
18
19 PROCEDURE copy_pl_typ_record
20 (p_pl_typ_id NUMBER,
21 p_effective_date DATE,
22 p_copy_entity_txn_id NUMBER,
23 p_business_group_id Number,
24 p_copy_entity_result_id OUT NOCOPY NUMBER) is
25 l_proc varchar2(72) := g_package||'.copy_pl_typ_record';
26 cursor c_ptp IS
27 select ptp.*
28 from BEN_PL_TYP_F ptp
29 where ptp.pl_typ_id = p_pl_typ_id
30 -- and p_effective_date between effective_start_date and effective_end_date
31 and NOT EXISTS (SELECT information1
32 FROM BEN_COPY_ENTITY_RESULTS cer
33 WHERE copy_entity_txn_id = p_copy_entity_txn_id
34 AND table_alias = 'PTP'
35 AND information1 = p_pl_typ_id
36 AND dml_operation = 'REUSE');
37
38 l_ptp_rec BEN_PL_TYP_F%ROWTYPE;
39 l_copy_entity_result_id NUMBER;
40 l_result_type_cd VARCHAR2(30) := 'DISPLAY';
41 l_mirror_src_entity_result_id NUMBER;
42 l_number_of_copies NUMBER := 0;
43 l_table_route_id NUMBER;
44 l_information5 VARCHAR2(600) := '';
45 l_object_version_number NUMBER;
46 -- l_ptp_rec c_ptp%ROWTYPE;
47
48
49 cursor c_table_route (c_table_alias VARCHAR) IS
50 select table_route_id
51 from pqh_table_route
52 WHERE table_alias = c_table_alias;
53
54
55
56 BEGIN
57 hr_utility.set_location('Entering: '||l_proc,10);
58 open c_table_route('PTP');
59 fetch c_table_route into l_table_route_id ;
60 close c_table_route ;
61
62
63
64 -- fetch c_ptp into l_ptp_rec;
65 for l_ptp_rec in c_ptp
66 loop
67 --IF C_PTP%FOUND THEN
68 ben_copy_entity_results_api.create_copy_entity_results(
69 p_copy_entity_result_id => l_copy_entity_result_id,
70 p_copy_entity_txn_id => p_copy_entity_txn_id,
71 p_result_type_cd => l_result_type_cd,
72 p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
73 p_parent_entity_result_id => l_mirror_src_entity_result_id,
74 p_number_of_copies => 1,
75 p_table_route_id => l_table_route_id,
76 p_table_alias => 'PTP',
77 p_dml_operation => 'REUSE',
78 P_INFORMATION1 => l_ptp_rec.PL_TYP_ID,
79 P_INFORMATION2 => l_ptp_rec.EFFECTIVE_START_DATE,
80 P_INFORMATION3 => l_ptp_rec.EFFECTIVE_END_DATE,
81 P_INFORMATION4 => l_ptp_rec.BUSINESS_GROUP_ID,
82 P_INFORMATION11 => l_ptp_rec.SHORT_CODE,
83 P_INFORMATION12 => l_ptp_rec.SHORT_NAME,
84 P_INFORMATION13 => l_ptp_rec.NO_MX_ENRL_NUM_DFND_FLAG,
85 P_INFORMATION14 => l_ptp_rec.NO_MN_ENRL_NUM_DFND_FLAG,
86 P_INFORMATION15 => l_ptp_rec.OPT_DSPLY_FMT_CD,
87 P_INFORMATION16 => l_ptp_rec.COMP_TYP_CD,
88 P_INFORMATION17 => l_ptp_rec.PL_TYP_STAT_CD,
89 P_INFORMATION18 => l_ptp_rec.OPT_TYP_CD,
90 P_INFORMATION110 => l_ptp_rec.PTP_ATTRIBUTE_CATEGORY,
91 P_INFORMATION111 => l_ptp_rec.PTP_ATTRIBUTE1,
92 P_INFORMATION112 => l_ptp_rec.PTP_ATTRIBUTE2,
93 P_INFORMATION113 => l_ptp_rec.PTP_ATTRIBUTE3,
94 P_INFORMATION114 => l_ptp_rec.PTP_ATTRIBUTE4,
95 P_INFORMATION115 => l_ptp_rec.PTP_ATTRIBUTE5,
96 P_INFORMATION116 => l_ptp_rec.PTP_ATTRIBUTE6,
97 P_INFORMATION117 => l_ptp_rec.PTP_ATTRIBUTE7,
98 P_INFORMATION118 => l_ptp_rec.PTP_ATTRIBUTE8,
99 P_INFORMATION119 => l_ptp_rec.PTP_ATTRIBUTE9,
100 P_INFORMATION120 => l_ptp_rec.PTP_ATTRIBUTE10,
101 P_INFORMATION121 => l_ptp_rec.PTP_ATTRIBUTE11,
102 P_INFORMATION122 => l_ptp_rec.PTP_ATTRIBUTE12,
103 P_INFORMATION123 => l_ptp_rec.PTP_ATTRIBUTE13,
104 P_INFORMATION124 => l_ptp_rec.PTP_ATTRIBUTE14,
105 P_INFORMATION125 => l_ptp_rec.PTP_ATTRIBUTE15,
106 P_INFORMATION126 => l_ptp_rec.PTP_ATTRIBUTE16,
107 P_INFORMATION127 => l_ptp_rec.PTP_ATTRIBUTE17,
108 P_INFORMATION128 => l_ptp_rec.PTP_ATTRIBUTE18,
109 P_INFORMATION129 => l_ptp_rec.PTP_ATTRIBUTE19,
110 P_INFORMATION130 => l_ptp_rec.PTP_ATTRIBUTE20,
111 P_INFORMATION131 => l_ptp_rec.PTP_ATTRIBUTE21,
112 P_INFORMATION132 => l_ptp_rec.PTP_ATTRIBUTE22,
113 P_INFORMATION133 => l_ptp_rec.PTP_ATTRIBUTE23,
114 P_INFORMATION134 => l_ptp_rec.PTP_ATTRIBUTE24,
115 P_INFORMATION135 => l_ptp_rec.PTP_ATTRIBUTE25,
116 P_INFORMATION136 => l_ptp_rec.PTP_ATTRIBUTE26,
117 P_INFORMATION137 => l_ptp_rec.PTP_ATTRIBUTE27,
118 P_INFORMATION138 => l_ptp_rec.PTP_ATTRIBUTE28,
119 P_INFORMATION139 => l_ptp_rec.PTP_ATTRIBUTE29,
120 P_INFORMATION140 => l_ptp_rec.PTP_ATTRIBUTE30,
121 P_INFORMATION141 => l_ptp_rec.IVR_IDENT,
122 P_INFORMATION170 => l_ptp_rec.NAME,
123 P_INFORMATION260 => l_ptp_rec.MX_ENRL_ALWD_NUM,
124 P_INFORMATION261 => l_ptp_rec.MN_ENRL_RQD_NUM,
125 P_INFORMATION265 => l_ptp_rec.OBJECT_VERSION_NUMBER,
126 p_object_version_number => l_object_version_number,
127 p_effective_date => p_effective_date
128 );
129 --
130 p_copy_entity_result_id := l_copy_entity_result_id ;
131 -- END IF;
132 end loop;
133 --CLOSE C_PTP;
134 -- mark the future data Exists column
135 mark_future_data_exists(p_copy_entity_txn_id);
136 hr_utility.set_location('Leaving: '||l_proc,20);
137 END;
138
139 FUNCTION Get_New_Enrt_Cd
140 (
141 p_Enrt_Cd Varchar2
142 ) Return varchar2
143 Is
144
145 NEW_CHOS varchar2(15) := 'CC';
146 NEW_NOTH varchar2(15) := 'NN';
147 CUR_KEEP_CHOS varchar2(15) := 'CCKC';
148 CUR_KEEP varchar2(15) := 'CCNC';
149 CUR_CHOS varchar2(15) := 'CCON';
150 CUR_LOSE varchar2(15) := 'CLON';
151
152 CUR_KEEP_CHOS_NEW_CHOS varchar2(15) := 'CCKCNCC';
153 CUR_KEEP_CHOS_NEW_NOTH varchar2(15) := 'CCKCNN';
154 CUR_CHOS_NEW_CHOS varchar2(15) := 'CCONCC';
155 CUR_CHOS_NEW_NOTH varchar2(15) := 'CCONN';
156 CUR_KEEP_NEW_CHOS varchar2(15) := 'CKNCC';
157 CUR_KEEP_NEW_NOTH varchar2(15) := 'CKNN';
158 CUR_LOSE_NEW_CHOS varchar2(15) := 'CLNCC';
159 CUR_LOSE_NEW_NOTH varchar2(15) := 'CLONN';
160
161 l_enrt_cd varchar2(15) := null;
162 Begin
163 if CUR_KEEP_CHOS_NEW_CHOS = p_enrt_cd then l_enrt_cd :=NEW_CHOS;
164 elsif CUR_KEEP_CHOS_NEW_NOTH = p_enrt_cd then l_enrt_cd :=NEW_NOTH;
165 elsif CUR_CHOS_NEW_CHOS = p_enrt_cd then l_enrt_cd :=NEW_CHOS;
166 elsif CUR_CHOS_NEW_NOTH = p_enrt_cd then l_enrt_cd :=NEW_NOTH ;
167 elsif CUR_KEEP_NEW_CHOS = p_enrt_cd then l_enrt_cd :=NEW_CHOS;
168 elsif CUR_KEEP_NEW_NOTH = p_enrt_cd then l_enrt_cd :=NEW_NOTH;
169 elsif CUR_LOSE_NEW_CHOS = p_enrt_cd then l_enrt_cd :=NEW_CHOS;
170 elsif CUR_LOSE_NEW_NOTH = p_enrt_cd then l_enrt_cd :=NEW_NOTH;
171 end if ;
172
173 return l_enrt_cd ;
174 End Get_New_Enrt_Cd ;
175
176 FUNCTION Get_Cur_Enrt_Cd
177 (
178 p_Enrt_Cd Varchar2
179 ) Return varchar2
180 Is
181 NEW_CHOS varchar2(15) := 'CC';
182 NEW_NOTH varchar2(15) := 'NN';
183 CUR_KEEP_CHOS varchar2(15) := 'CCKC';
184 CUR_KEEP varchar2(15) := 'CCNC';
185 CUR_CHOS varchar2(15) := 'CCON';
186 CUR_LOSE varchar2(15) := 'CLON';
187
188 CUR_KEEP_CHOS_NEW_CHOS varchar2(15) := 'CCKCNCC';
189 CUR_KEEP_CHOS_NEW_NOTH varchar2(15) := 'CCKCNN';
190 CUR_CHOS_NEW_CHOS varchar2(15) := 'CCONCC';
191 CUR_CHOS_NEW_NOTH varchar2(15) := 'CCONN';
192 CUR_KEEP_NEW_CHOS varchar2(15) := 'CKNCC';
193 CUR_KEEP_NEW_NOTH varchar2(15) := 'CKNN';
194 CUR_LOSE_NEW_CHOS varchar2(15) := 'CLNCC';
195 CUR_LOSE_NEW_NOTH varchar2(15) := 'CLONN';
196
197 l_enrt_cd varchar2(15) := null;
198 Begin
199 if CUR_KEEP_CHOS_NEW_CHOS = p_enrt_cd then l_enrt_cd :=CUR_KEEP_CHOS;
200 elsif CUR_KEEP_CHOS_NEW_NOTH = p_enrt_cd then l_enrt_cd :=CUR_KEEP_CHOS;
201 elsif CUR_CHOS_NEW_CHOS = p_enrt_cd then l_enrt_cd :=CUR_CHOS;
202 elsif CUR_CHOS_NEW_NOTH = p_enrt_cd then l_enrt_cd :=CUR_CHOS;
203 elsif CUR_KEEP_NEW_CHOS = p_enrt_cd then l_enrt_cd :=CUR_KEEP;
204 elsif CUR_KEEP_NEW_NOTH = p_enrt_cd then l_enrt_cd :=CUR_KEEP;
205 elsif CUR_LOSE_NEW_CHOS = p_enrt_cd then l_enrt_cd :=CUR_LOSE;
206 elsif CUR_LOSE_NEW_NOTH = p_enrt_cd then l_enrt_cd :=CUR_LOSE;
207 end if;
208
209 return l_enrt_cd ;
210 End Get_Cur_Enrt_Cd;
211
212 procedure populate_extra_mapping_ELP(
213 p_copy_entity_txn_id in Number,
214 p_effective_date in Date,
215 p_elig_prfl_id in Number
216 )
217 is
218 l_proc varchar2(72) := g_package||'.populate_extra_mapping_ELP';
219 cursor c_crit ( p_parent_entity_result_id Number )
220 is
221 select
222 information5 overview_Name,
223 table_alias,
224 information174,
225 information178,
226 information185,
227 information228, -- pl_typ_opt_typ_id
228 information258 -- oipl id
229 from
230 ben_copy_entity_results
231 where
232 copy_entity_txn_id = p_copy_entity_txn_id
233 and parent_entity_result_id = p_parent_entity_result_id
234 -- Inorder to populate mappings for rows outside effective date removing date track where clause
235 -- and p_effective_date between information2 and information3
236 and information170 is null
237 for update of
238 information170, information185;
239
240 l_name ben_copy_entity_results.information5%TYPE;
241 l_information185 ben_copy_entity_results.information170%TYPE;
242 l_parent_entity_result_id Number;
243 l_overview_name ben_copy_entity_results.information5%TYPE;
244 l_position Number;
245 l_business_group_id Number;
246
247 begin
248 hr_utility.set_location('Entering: '||l_proc,10);
249 select
250 context_business_group_id into l_business_group_id
251 from
252 pqh_copy_entity_txns
253 where
254 copy_entity_txn_id = p_copy_entity_txn_id;
255
256 select
257 copy_entity_result_id into l_parent_entity_result_id
258 from
259 ben_copy_entity_results
260 where
261 table_alias = 'ELP'
262 and copy_entity_txn_id = p_copy_entity_txn_id
263 and information1 = p_elig_prfl_id
264 and p_effective_date between information2 and information3;
265 for l_crit in c_crit(l_parent_entity_result_id)
266 loop
267 l_overview_name := l_crit.overview_name;
268 l_position := instr(l_overview_name,'(');
269 if l_position = 0
270 then
271 l_name := l_overview_name;
272 else
273 l_name := substr(l_overview_name,1,(l_position-2));
274 end if;
275 if l_crit.table_alias='ELR'
276 then
277 select
278 name into l_name
279 from
280 per_absence_attendance_types
281 where
282 absence_attendance_type_id = l_crit.information174
283 and business_group_id = l_business_group_id
284 and date_effective <= p_effective_date
285 and (date_end is null or date_end >= p_effective_date);
286
287
288 begin
289 select
290 meaning into l_information185
291 from
292 per_abs_attendance_reasons
293 ,hr_leg_lookups
294 where
295 business_group_id = l_business_group_id
296 and abs_attendance_reason_id = l_crit.information178
297 and name = lookup_code
298 and lookup_type = 'ABSENCE_REASON'
299 and (start_date_active is null or start_date_active <= p_effective_date)
300 and (end_date_active is null or end_date_active >= p_effective_date);
301 Exception when no_data_found then
302 l_information185 := null;
303 end;
304
305 update
306 ben_copy_entity_results
307 set
308 information170 = l_name,
309 information185 = l_information185
310 where current of c_crit;
311
312 elsif l_crit.table_alias='ECY'
313 then
314 select
315 name into l_name
316 from
317 per_competences_vl
318 where
319 (business_group_id is null or business_group_id = l_business_group_id)
320 and competence_id = l_crit.information174
321 and (date_from is null or date_from <= p_effective_date)
322 and (date_to is null or date_to >= p_effective_date);
323
324 begin
325 select
326 rtl.name into l_information185
327 from
328 per_rating_levels_vl rtl
329 where
330 (rtl.business_group_id is null or rtl.business_group_id=l_business_group_id)
331 and rtl.rating_level_id = l_crit.information178;
332 Exception when no_data_found then
333 l_information185 := null;
334 end;
335
336 update
337 ben_copy_entity_results
338 set
339 information170 = l_name,
340 information185 = l_information185
341 where current of c_crit;
342
343 elsif l_crit.table_alias='EHC'
344 then
345 /* mapping not required
346 select
347 ptp.name || ' - ' || opt.name name into l_name
348 from
349 ben_pl_typ_opt_typ_f pto, ben_pl_typ_f ptp, ben_opt_f opt
350 where
351 pto.business_group_id = l_business_group_id
352 and pto.pl_typ_opt_typ_id = l_crit.information228
353 and p_effective_date between pto.effective_start_date and pto.effective_end_date
354 and pto.pl_typ_id = ptp.pl_typ_id
355 and pto.business_group_id = ptp.business_group_id
356 and p_effective_date between ptp.effective_start_date and ptp.effective_end_date
357 and pto.opt_id = opt.opt_id
358 and pto.business_group_id = opt.business_group_id
359 and p_effective_date between opt.effective_start_date and opt.effective_end_date;
360 */
361
362 select
363 pln.name name into l_information185
364 from
365 ben_pl_typ_opt_typ_f pto, ben_oipl_f oipl, ben_pl_f pln
366 where
367 pto.business_group_id = l_business_group_id
368 and p_effective_date between pto.effective_start_date and pto.effective_end_date
369 and oipl.oipl_id = l_crit.information258
370 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
371 and pto.pl_typ_opt_typ_id = l_crit.information228
372 and pto.opt_id = oipl.opt_id
373 and pto.business_group_id = oipl.business_group_id
374 and oipl.pl_id = pln.pl_id
375 and oipl.business_group_id = pln.business_group_id
376 and p_effective_date between pln.effective_start_date and pln.effective_end_date;
377
378 update
379 ben_copy_entity_results
380 set
381 information185 = l_information185
382 where current of c_crit;
383 -- if Criteria is any of tbe below listed, we should not copy mappings since mappings could be more than 240 characters ( UTF - 8)
384 -- instead of copying mappings, we are showing the overview-name using join in Criteria Query
385 elsif not (l_crit.table_alias='EDT' or l_crit.table_alias='EDP' or l_crit.table_alias='EDI' or l_crit.table_alias='EDG'
386 or l_crit.table_alias='ETD' or l_crit.table_alias='EPP' or l_crit.table_alias='EOY'
387 or l_crit.table_alias='EEI' or l_crit.table_alias='EEP' or l_crit.table_alias='EET'
388 or l_crit.table_alias='EAI' or l_crit.table_alias='EEG' or l_crit.table_alias='ECQ' ) then
389
390 update
391 ben_copy_entity_results
392 set
393 information170 = l_name
394 where current of c_crit;
395 end if;
396 end loop;
397 hr_utility.set_location('Leaving: '||l_proc,20);
398 end populate_extra_mapping_ELP;
399
400 procedure populate_extra_mappings_ELP(
401 p_copy_entity_txn_id in Number,
402 p_effective_date in Date
403 )
404 is
405 l_proc varchar2(72) := g_package||'.populate_extra_mappings_ELP';
406 cursor c_elp (
407 p_copy_entity_txn_id Number,
408 p_effective_date Date
409 )
410 is
411 select
412 information1 elig_prfl_id
413 from
414 ben_copy_entity_results
415 where
416 table_alias = 'ELP'
417 and copy_entity_txn_id = p_copy_entity_txn_id
418 and p_effective_date between information2 and information3 ;
419 begin
420 hr_utility.set_location('Entering: '||l_proc,10);
421 for l_elp in c_elp(p_copy_entity_txn_id,p_effective_date)
422 loop
423 -- populate the extra mappings required for Criteria
424 populate_extra_mapping_elp(
425 p_copy_entity_txn_id => p_copy_entity_txn_id
426 ,p_effective_date => p_effective_date
427 ,p_elig_prfl_id => l_elp.elig_prfl_id);
428 end loop;
429 hr_utility.set_location('Leaving: '||l_proc,20);
430 end populate_extra_mappings_ELP;
431
432
433 PROCEDURE populate_extra_Mapping_PLN
434 (
435 p_effective_date DATE,
436 p_business_group_id NUMBER,
437 p_copy_entity_txn_id NUMBER,
438 p_copy_entity_result_id NUMBER
439 ) IS
440
441
442 l_opt_Typ_Cd Varchar2(15);
443 l_proc varchar2(72) := g_package||'.populate_extra_Mapping_PLN';
444 BEGIN
445 hr_utility.set_location('Entering: '||l_proc,10);
446 -- Update Information11 with extra mapping
447 Select
448 ptp.Information18 into l_opt_Typ_Cd
449 From
450 Ben_copy_entity_results ptp,
451 Ben_copy_entity_results pln
452 Where
453 ptp.copy_entity_txn_id = p_copy_entity_txn_id
454 And pln.copy_entity_txn_id = ptp.copy_entity_txn_id
455 And p_effective_date between ptp.information2 and ptp.information3
456 And p_effective_date between pln.information2 and pln.information3
457 And pln.table_alias='PLN'
458 And ptp.table_alias='PTP'
459 And ptp.information1=pln.Information248
460 And pln.status <>'DELETE'
461 And ptp.status <>'DELETE'
462 And pln.copy_entity_result_id = p_copy_entity_result_id;
463
464 Update
465 Ben_copy_entity_results pln
466 Set
467 Information11 = l_opt_Typ_Cd
468 Where
469 copy_entity_result_id = p_copy_entity_result_id;
470 hr_utility.set_location('Leaving: '||l_proc,20);
471
472 Exception When No_Data_Found Then
473 Null;
474 END populate_extra_Mapping_PLN;
475
476
477 PROCEDURE populate_extra_mappings_CPY
478 (
479 p_copy_entity_txn_id Number
480 ,p_business_group_id Number
481 ,p_effective_date Date
482 )
483 Is
484
485 l_start_dt date ;
486 l_end_dt date ;
487 l_type varchar2(25);
488 l_proc varchar2(72) := g_package||'.populate_extra_mappings_CPY';
489 --
490 -- Pick up All CPY for Pgm and Plan
491 cursor c_CPY(c_table_alias varchar2) is
492 select
493 cpy.*
494 from
495 BEN_COPY_ENTITY_RESULTS cpy
496 where
497 cpy.information4 = p_business_group_id
498 And cpy.copy_entity_txn_id = p_copy_entity_txn_id
499 And cpy.table_alias='CPY'
500 --And cpy.information260 is not null
501 and cpy.dml_operation <>'INSERT'
502 and cpy.information311 is null
503 for update ;
504 --
505
506 Begin
507 hr_utility.set_location('Entering: '||l_proc,10);
508 -- UPD CHANGE
509 -- For Pdw Update we need to update the CPY rows with the YRP start -end date
510 -- and year period type so that it gets shown in the UI
511
512 For l_CPY_rec in c_CPY('CPY') Loop
513 Begin
514 --
515 Select
516 Information309 ,
517 Information308 ,
518 Information12
519 into l_start_dt , l_end_dt , l_type
520 From
521 Ben_copy_entity_results yrp
522 Where
523 yrp.copy_entity_txn_id = p_copy_entity_txn_id
524 And yrp.table_alias='YRP'
525 And yrp.information1 = l_cpy_rec.Information240 ;
526
527 -- Update the Plan Year Periods Extra Mappings
528 Update
529 Ben_copy_entity_results cpy
530 set
531 information311 = l_start_dt,
532 information310 = l_end_dt,
533 information12 = l_type
534 where
535 current of c_CPY;
536 --
537 End ;
538 End Loop ;
539 -- END UPD CHANGE
540 hr_utility.set_location('Leaving: '||l_proc,20);
541 End populate_extra_mappings_CPY;
542
543 procedure populate_extra_Mappings_EPA(
544 p_copy_entity_txn_id Number,
545 p_effective_date Date
546 )
547 is
548 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_EPA';
549
550 cursor c_epa (c_copy_entity_txn_id Number, c_effective_date Date) is
551 select EPA.copy_entity_result_id,
552 EPA.information1 prtn_elig_id,
553 EPA.information260 PGM,
554 EPA.information259 CTP,
555 EPA.information256 CPP,
556 EPA.information261 PLN,
557 EPA.information258 COP
558 from ben_copy_entity_results EPA
559 where copy_entity_txn_id = c_copy_entity_txn_id
560 and table_alias = 'EPA'
561 and c_effective_date between information2 and information3
562 for update of information20, information272;
563
564 cursor c_cep (c_copy_entity_txn_id Number, c_effective_date Date, c_prtn_elig_id Number) is
565 select CEP.copy_entity_result_id
566 from ben_copy_entity_results CEP
567 where copy_entity_txn_id = c_copy_entity_txn_id
568 and table_alias = 'CEP'
569 and c_effective_date between information2 and information3
570 and information229 = c_prtn_elig_id
571 for update of information20, information272;
572
573 l_prtn_elig_id Number;
574 l_compobj_id Number;
575 l_compobj_type varchar2(30);
576
577 BEGIN
578 hr_utility.set_location('Entering: '||l_proc,10);
579 FOR l_epa in c_epa( p_copy_entity_txn_id, p_effective_date )
580 LOOP
581 IF (l_epa.PGM is not null) then
582 l_compobj_id := l_epa.PGM;
583 l_compobj_type := 'PGM';
584 elsif (l_epa.CTP is not null) then
585 l_compobj_id := l_epa.CTP;
586 l_compobj_type := 'CTP';
587 elsif (l_epa.CPP is not null) then
588
589 l_compobj_id := l_epa.CPP;
590 l_compobj_type := 'CPP';
591 elsif (l_epa.PLN is not null) then
592 l_compobj_id := l_epa.PLN;
593 l_compobj_type := 'PLN';
594 elsif (l_epa.COP is not null) then
595 l_compobj_id := l_epa.COP;
596 l_compobj_type := 'COP';
597 end if;
598 l_prtn_elig_id := l_epa.prtn_elig_id;
599
600 /*dbms_output.put_line('COMPId: '||l_compobj_id);
601 dbms_output.put_line('COMPTYPE: '||l_compobj_type);
602 dbms_output.put_line('COMP_ID: '||l_prtn_elig_id);
603 dbms_output.put_line('COMP_ID: '||l_epa.copy_entity_result_id);*/
604
605 update
606 ben_copy_entity_results
607 set
608 information20 = l_compobj_type,
609 information272 = l_compobj_id
610 Where Current Of c_epa ;
611 -- copy_entity_result_id = l_epa.copy_entity_result_id;
612
613 FOR l_cep in c_cep(p_copy_entity_txn_id, p_effective_date, l_prtn_elig_id)
614 LOOP
615
616 update
617 ben_copy_entity_results
618 set
619 information20 = l_compobj_type,
620 information272 = l_compobj_id
621 where current of c_cep;
622 END LOOP;
623 END LOOP;
624 -- mark the future data Exists column
625 mark_future_data_exists(p_copy_entity_txn_id);
626
627 hr_utility.set_location('Leaving: '||l_proc,20);
628 END populate_extra_Mappings_EPA;
629
630 procedure populate_extra_mappings_VPF(
631 p_copy_entity_txn_id Number,
632 p_effective_date Date)
633 is
634 l_proc varchar2(72) := g_package||'.populate_extra_mappings_VPF';
635 cursor c_vpf
636 is
637 select
638 copy_entity_result_id,
639 information1 vrbl_rt_prfl_id,
640 information266,
641 information186,
642 information2 effective_date
643 from
644 ben_copy_entity_results
645 where
646 table_alias = 'VPF'
647 and copy_entity_txn_id = p_copy_entity_txn_id
648 -- Inorder to populate mappings for rows outside effective date removing date track where clause
649 -- and p_effective_date between information2 and information3
650 and (information186 is null or information266 is null)
651 for update of information266, information186;
652
653 l_elig_prfl_id Number;
654 l_elig_prfl_name ben_copy_entity_results.information170%type;
655
656 begin
657 hr_utility.set_location('Entering: '||l_proc,10);
658 -- dbms_output.put_line('Before Cursor');
659 FOR l_vpf in c_vpf
660 LOOP
661 -- dbms_output.put_line('Modifying '||l_vpf.vrbl_rt_prfl_id);
662 Begin
663 select
664 elp.information1 , elp.information170
665 into l_elig_prfl_id, l_elig_prfl_name
666 from
667 ben_copy_entity_Results elp,
668 ben_copy_entity_results vep
669 where
670 elp.table_alias = 'ELP'
671 and elp.copy_entity_txn_id = p_copy_entity_Txn_id
672 and l_vpf.effective_date between elp.information2 and elp.information3
673 and vep.table_alias = 'VEP'
674 and vep.copy_entity_txn_id = elp.copy_entity_txn_id
675 and l_vpf.effective_date between vep.information2 and vep.information3
676 and vep.information263 = elp.information1
677 and vep.information262 = l_vpf.vrbl_rt_prfl_id;
678
679
680 update
681 ben_copy_entity_results
682 set
683 information266 = l_elig_prfl_id,
684 information186 = l_elig_prfl_name
685 where current of c_vpf;
686 Exception When No_Data_Found Then
687 Null;
688 end;
689 END LOOP;
690 hr_utility.set_location('Leaving: '||l_proc,20);
691 end populate_extra_mappings_VPF;
692
693
694 PROCEDURE populate_extra_Mapping_LEN
695 (
696 p_copy_entity_result_id Number,
697 p_effective_date Date
698 )
699 Is
700 l_proc varchar2(72) := g_package||'.populate_extra_Mapping_LEN';
701 Begin
702 hr_utility.set_location('Entering: '||l_proc,10);
703 Update
704 Ben_copy_entity_results len1
705 Set
706 Information170 = (
707 Select
708 ler.information170 Name
709 From
710 Ben_copy_entity_results len,
711 Ben_copy_entity_results ler
712 Where
713 len.copy_entity_result_id = p_copy_entity_result_id
714 And ler.copy_entity_txn_id = len.copy_entity_txn_id
715 And p_effective_date between len.information2 and len.information3
716 And p_effective_date between ler.information2 and ler.information3
717 and ler.table_alias='LER'
718 and len.table_alias='LEN'
719 and len.information257 = ler.information1
720 )
721 Where
722 len1.copy_entity_result_id = p_copy_entity_result_id ;
723 hr_utility.set_location('Leaving: '||l_proc,20);
724 End populate_extra_Mapping_LEN;
725
726 PROCEDURE populate_extra_Mappings_LEN
727 (
728 p_copy_entity_txn_id Number,
729 p_effective_date Date,
730 p_pgm_id Number
731 )
732 Is
733 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_LEN';
734 Cursor C_LEN is
735 Select
736 len.copy_entity_result_id
737 From
738 Ben_copy_entity_results len,
739 Ben_copy_entity_results pet
740 Where
741 len.copy_entity_txn_id = p_copy_entity_txn_id
742 And len.copy_entity_txn_id = pet.copy_entity_txn_id
743 And p_effective_date between len.information2 and len.information3
744 And p_effective_date between pet.information2 and pet.information3
745 and pet.table_alias='PET'
746 and len.table_alias='LEN'
747 and pet.information11='L'
748 and len.information232 = pet.information1
749 and pet.information260= p_pgm_id;
750
751 Begin
752 hr_utility.set_location('Entering: '||l_proc,10);
753 For l_LEN in c_LEN Loop
754
755 populate_extra_mapping_LEN(l_LEN.copy_entity_result_id,p_effective_date);
756 End Loop ;
757 hr_utility.set_location('Leaving: '||l_proc,20);
758 End populate_extra_Mappings_LEN;
759
760 FUNCTION Get_Dflt_New_Enrt_Cd
761 (
762 p_Enrt_Cd Varchar2
763 ) Return varchar2
764 Is
765
766 l_enrt_cd varchar2(15) := null;
767
768 Begin
769 if 'NDCN' = p_enrt_cd then l_enrt_cd := 'DFLT';
770 elsif 'NSDCSD' = p_enrt_cd then l_enrt_cd :='DFLT';
771 elsif 'NSDCS' = p_enrt_cd then l_enrt_cd :='DFLT';
772 elsif 'NDCSEDR'= p_enrt_cd then l_enrt_cd :='DFLT';
773
774 elsif 'NNCN' = p_enrt_cd then l_enrt_cd :='NODFLT';
775 elsif 'NNCD' = p_enrt_cd then l_enrt_cd :='NODFLT';
776 elsif 'NNCS' = p_enrt_cd then l_enrt_cd :='NODFLT';
777 elsif 'NNCSEDR'= p_enrt_cd then l_enrt_cd :='NODFLT';
778
779 end if ;
780
781 return l_enrt_cd ;
782
783
784 End Get_Dflt_New_Enrt_Cd ;
785
786 FUNCTION Get_Dflt_Old_Enrt_Cd
787 (
788 p_Enrt_Cd Varchar2
789 ) Return varchar2
790 Is
791
792 l_enrt_cd varchar2(15) := null;
793
794 Begin
795 if 'NDCN' = p_enrt_cd then l_enrt_cd := 'DB';
796 elsif 'NSDCSD' = p_enrt_cd then l_enrt_cd :='DFLT';
797 elsif 'NSDCS' = p_enrt_cd then l_enrt_cd :='SR';
798 elsif 'NDCSEDR'= p_enrt_cd then l_enrt_cd :='RR';
799
800 elsif 'NNCN' = p_enrt_cd then l_enrt_cd :='DB';
801 elsif 'NNCD' = p_enrt_cd then l_enrt_cd :='DFLT';
802 elsif 'NNCS' = p_enrt_cd then l_enrt_cd :='SR';
803 elsif 'NNCSEDR'= p_enrt_cd then l_enrt_cd :='RR';
804
805 end if ;
806
807 return l_enrt_cd ;
808
809
810 End Get_Dflt_Old_Enrt_Cd ;
811
812 PROCEDURE populate_extra_Mappings_LPR
813 (
814 p_copy_entity_txn_id Number,
815 p_effective_date Date,
816 p_pgm_id Number
817 )
818 Is
819 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_LPR';
820 -- Select All LPR records which have a default enrollment logic defined
821 --
822 -- Information15-> dflt logic , Information13 -dflt flag
823 Cursor C_LPR is
824 Select
825 LPR.copy_entity_result_id,
826 LPR.information15,
827 LPR.information13,
828 LPR.information261,
829 LPR.information256,
830 LPR.information257 ler_id,
831 LPR.information2 effective_date
832 From
833 Ben_copy_entity_results LPR
834 Where
835 LPR.copy_entity_txn_id = p_copy_entity_txn_id
836 And LPR.copy_entity_txn_id = LPR.copy_entity_txn_id
837 --And p_effective_date between LPR.information2 and LPR.information3
838 And LPR.table_alias='LPR1'
839 --And LPR.information260= p_pgm_id
840 And LPR.information15 is not null
841 And LPR.information103 is null -- not populated already
842 And LPR.dml_operation <>'DELETE'
843 For Update of LPR.Information103,LPR.information104;
844
845 cursor c_lpr1(
846 p_copy_entity_txn_id Number,
847 p_effective_date Date)
848 is
849 select
850 information16 ENRT_CD,
851 information101 NEW_ENRT_CD,
852 information102 CUR_ENRT_CD
853 from
854 ben_copy_entity_results
855 where
856 copy_entity_txn_id = p_copy_entity_txn_id
857 and table_alias = 'LPR1'
858 and information16 is not null
859 -- and p_effective_date between information2 and information3
860 and dml_operation <> 'DELETE'
861 for update of information101, information102;
862
863 l_new_enrt_cd ben_copy_entity_results.information101%type;
864 l_cur_enrt_cd ben_copy_entity_results.information102%type;
865
866 plipCopyEntityResultId ben_copy_entity_results.copy_entity_result_id%type;
867 l_pl_id ben_pl_f.pl_id%type ;
868
869 l_new_dflt_enrt_cd varchar2(15);
870 l_old_dflt_enrt_cd varchar2(15);
871 l_default_object_id Number;
872 --
873 Begin
874 hr_utility.set_location('Entering: '||l_proc,10);
875 --
876 --dbms_output.put_line(' pgm id '|| p_pgm_id|| ' txn id '|| to_char(p_copy_entity_txn_id));
877 For l_LPR in c_LPR Loop
878 --
879 --dbms_output.put_line(' here '||l_LPR.information15 );
880 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_LPR.information15);
881 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_LPR.information15);
882
883 -- Get plipCopyEntityResultId
884 -- Populate information162 with level copy result id
885 Select
886 copy_entity_result_id ,information261 into plipCopyEntityResultId,l_pl_id
887 From
888 ben_copy_entity_results
889 where
890 copy_entity_txn_id = p_copy_entity_txn_id
891 and l_LPR.effective_date between information2 and information3
892 and table_alias='CPP'
893 and information1 = l_LPR.information256;
894 --
895
896 -- populate default object copy result id for COP or CPP
897 -- Make a Try with the Options in this Plan
898 -- Does any of them have default object flag set ?
899 Begin
900 --
901 --
902 Select
903 cop.copy_entity_result_id into l_default_object_id
904 From
905 Ben_copy_entity_results lop,
906 Ben_copy_entity_results cop
907 Where
908 lop.copy_entity_txn_id = p_copy_entity_txn_id
909 And lop.copy_entity_txn_id = cop.copy_entity_txn_id
910 And l_LPR.effective_date between lop.information2 and lop.information3
911 And l_LPR.effective_date between cop.information2 and cop.information3
912 And lop.table_alias ='LOP'
913 And cop.table_alias = 'COP'
914 And cop.information1 = lop.information258
915 And cop.information261 = l_pl_id
916 And lop.information12 ='Y'
917 And lop.information257 = l_LPR.ler_id
918 And lop.dml_operation <>'DELETE'
919 and rownum=1;
920 --DBMS_OUTPUT.PUT_LINE(' DEFAULT OBJECT ID IS 1'||l_default_object_id);
921 --
922 Exception When No_Data_Found Then
923 --
924 -- Hard Luck - No options in plan level has default object set
925 --DBMS_OUTPUT.PUT_LINE(' DEFAULT OBJECT ID IS 2'||l_default_object_id ||' '||l_pl_id);
926 l_default_object_id := null ;
927 --
928 End ;
929 --DBMS_OUTPUT.PUT_LINE(' DEFAULT OBJECT ID IS '||l_default_object_id ||' EFF DT '||P_EFFECTIVE_DATE);
930
931 If l_LPR.information13 ='Y' and l_default_object_id is null then
932 --
933 -- Is this Plan the default object for this level ?
934 l_default_object_id := plipCopyEntityResultId;
935 --
936 End If ;
937
938 --dbms_output.put_line(' here '|| l_new_dflt_enrt_cd);
939
940 -- Update the New and Old Default enrollment Logic by parsing the combined dflt enrt logic
941 Update
942 Ben_copy_entity_results LPR1
943 Set
944 LPR1.information103 =l_new_dflt_enrt_cd,
945 LPR1.information104 =l_old_dflt_enrt_cd,
946 LPR1.information160 = l_default_object_id,
947 lpr1.information161 = lpr1.copy_entity_result_id,
948 lpr1.information162 = plipCopyEntityResultId
949 Where current of c_LPR;
950 --
951 End Loop ;
952 --
953 for l_lpr1 in c_lpr1(p_copy_entity_txn_id, p_effective_date)
954 loop
955 l_new_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_new_enrt_cd(l_lpr1.ENRT_CD);
956 l_cur_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_cur_enrt_cd(l_lpr1.ENRT_CD);
957 update
958 ben_copy_entity_results
959 set
960 information101 = l_new_enrt_cd,
961 information102 = l_cur_enrt_cd
962 where current of c_lpr1;
963 end loop;
964 hr_utility.set_location('Leaving: '||l_proc,20);
965 End populate_extra_Mappings_LPR;
966
967 PROCEDURE populate_extra_Mappings_LOP
968 (
969 p_copy_entity_txn_id Number,
970 p_effective_date Date,
971 p_pgm_id Number
972 )
973 Is
974 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_LOP';
975 -- Select All LOP records which have a default enrollment logic defined
976 --
977 -- Information16-> dflt logic , Information18 -dflt flag
978 Cursor C_LOP is
979 Select
980 LOP.copy_entity_result_id,
981 LOP.information16,
982 LOP.information258,
983 LOP.information2 effective_date
984 From
985 Ben_copy_entity_results LOP
986 Where
987 LOP.copy_entity_txn_id = p_copy_entity_txn_id
988 And LOP.copy_entity_txn_id = LOP.copy_entity_txn_id
989 --And p_effective_date between LOP.information2 and LOP.information3
990 And LOP.table_alias='LOP'
991 And LOP.information16 is not null
992 And LOP.information103 is null
993 And LOP.dml_operation <>'DELETE'
994 For Update of LOP.Information103,LOP.information104;
995
996 l_new_dflt_enrt_cd varchar2(15);
997 l_old_dflt_enrt_cd varchar2(15);
998
999 oiplCopyEntityResultId Number ;
1000 --
1001 Begin
1002 hr_utility.set_location('Entering: '||l_proc,10);
1003 --
1004 For l_LOP in c_LOP Loop
1005 --
1006 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_LOP.information16);
1007 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_LOP.information16);
1008
1009 -- Get oiplCopyEntityResultId
1010
1011 Select
1012 copy_entity_result_id into oiplCopyEntityResultId
1013 From
1014 ben_copy_entity_results
1015 where
1016 copy_entity_txn_id = p_copy_entity_txn_id
1017 and l_LOP.effective_date between information2 and information3
1018 and table_alias='COP'
1019 and information1 = l_LOP.information258;
1020 --
1021
1022 -- Update the New and Old Default enrollment Logic by parsing the combined dflt enrt logic
1023 Update
1024 Ben_copy_entity_results LOP1
1025 Set
1026 LOP1.information103 =l_new_dflt_enrt_cd,
1027 LOP1.information104 =l_old_dflt_enrt_cd,
1028 -- If OIpl has the defaults flag set then make this the default object for this level
1029 LOP1.Information160= decode(LOP1.Information12,'Y',oiplCopyEntityResultId,null),
1030 lOP1.information161 = lOP1.copy_entity_result_id,
1031 lop1.information162 = oiplCopyEntityResultId
1032 Where current of c_LOP;
1033
1034 --
1035 End Loop ;
1036 --
1037 hr_utility.set_location('Leaving: '||l_proc,20);
1038 End populate_extra_Mappings_LOP;
1039
1040 PROCEDURE populate_extra_Mappings_COP
1041 (
1042 p_copy_entity_txn_id Number,
1043 p_effective_date Date,
1044 p_pgm_id Number
1045 )
1046 Is
1047 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_COP';
1048 -- Select All COP records which have a default enrollment logic defined
1049 --
1050 Cursor C_COP is
1051 Select
1052 COP.copy_entity_result_id,
1053 COP.information26
1054 From
1055 Ben_copy_entity_results COP
1056 Where
1057 COP.copy_entity_txn_id = p_copy_entity_txn_id
1058 And COP.copy_entity_txn_id = COP.copy_entity_txn_id
1059 --And p_effective_date between COP.information2 and COP.information3
1060 And COP.table_alias='COP'
1061 And COP.information26 is not null
1062 And COP.information106 is null
1063 And COP.dml_operation <>'DELETE'
1064 For Update of COP.Information106,COP.information107;
1065
1066 l_new_dflt_enrt_cd varchar2(15);
1067 l_old_dflt_enrt_cd varchar2(15);
1068 --
1069 Begin
1070 hr_utility.set_location('Entering: '||l_proc,10);
1071 --
1072 For l_COP in c_COP Loop
1073 --
1074 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_COP.information26);
1075 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_COP.information26);
1076
1077 -- Update the New and Old Default enrollment Logic by parsing the combined dflt enrt logic
1078 Update
1079 Ben_copy_entity_results COP1
1080 Set
1081 COP1.information106 =l_new_dflt_enrt_cd,
1082 COP1.information107 =l_old_dflt_enrt_cd,
1083 -- If OIpl has the defaults flag set then make this the default object for this level
1084 COP1.Information160= decode(COP1.Information18,'Y',COP1.copy_entity_result_id,null)
1085 Where current of c_COP;
1086
1087 --
1088 End Loop ;
1089 --
1090 hr_utility.set_location('Leaving: '||l_proc,20);
1091 End populate_extra_Mappings_COP;
1092
1093
1094 PROCEDURE copy_pln_record_pcp(p_effective_date DATE,
1095 p_business_group_id NUMBER,
1096 p_copy_entity_txn_id NUMBER) IS
1097 p_object_version_number NUMBER;
1098 l_copy_entity_result_id NUMBER;
1099 l_business_group_id NUMBER;
1100 l_effective_date DATE;
1101 l_proc varchar2(72) := g_package||'.copy_pln_record_pcp';
1102
1103 CURSOR cur_new_ctp IS
1104 SELECT cpp.information261 pl_id
1105 FROM ben_copy_entity_results cpp
1106 WHERE cpp.copy_entity_txn_id = p_copy_entity_txn_id
1107 AND cpp.table_alias = TABLE_ALIAS_CPP
1108 AND cpp.information261 NOT IN
1109 (SELECT pln.information1
1110 FROM ben_copy_entity_results pln
1111 WHERE pln.copy_entity_txn_id = p_copy_entity_txn_id
1112 AND pln.table_alias = TABLE_ALIAS_PLN );
1113 BEGIN
1114 hr_utility.set_location('Entering: '||l_proc,10);
1115 FOR new_ctp IN cur_new_ctp
1116 LOOP
1117 hr_utility.set_location('copy plan: '||new_ctp.pl_id,20);
1118 /*
1119 BEN_PLAN_DESIGN_PLAN_MODULE.CREATE_PLAN_RESULT
1120 (p_copy_entity_result_id => l_copy_entity_result_id
1121 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1122 ,p_pl_id => new_ctp.pl_id
1123 ,p_business_group_id => p_business_group_id
1124 ,p_number_of_copies => 1
1125 ,p_object_version_number => p_object_version_number
1126 ,p_effective_date => p_effective_date
1127 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1128 ,p_plan_in_program => 'Y'
1129 );
1130 */
1131 copy_pln_record_pcp
1132 (
1133 p_effective_date =>p_effective_date,
1134 p_business_group_id => p_business_group_id,
1135 p_copy_entity_txn_id => p_copy_entity_txn_id,
1136 p_pl_Id => new_ctp.pl_id
1137 ) ;
1138 END LOOP;
1139
1140 hr_utility.set_location('delete duplicate rows ',30);
1141 /*kmullapu:
1142 We are using Plan Copy to fetch all child records of existing plan to staging.Now if we Attach Plan A
1143 and Plan B to a txn and if Child X is attached to both , it will get copied twice, leading to problems in
1144 other pages.Hence this delete.
1145
1146 get_txn_details (
1147 p_copy_entity_txn_id
1148 ,l_business_group_id
1149 ,l_effective_date
1150 );
1151 DELETE FROM ben_copy_entity_results
1152 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1153 AND copy_entity_result_id NOT IN
1154 ( SELECT MIN(copy_entity_result_id)
1155 FROM ben_copy_entity_results
1156 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1157 AND NVL(dml_operation, DML_OPER_REUSE) = DML_OPER_REUSE
1158 AND ( result_type_cd='DISPLAY' or
1159 l_effective_date between information2 and information3
1160 )
1161 GROUP BY table_alias, information1)
1162 AND NVL(DML_OPERATION, DML_OPER_REUSE) = DML_OPER_REUSE and
1163 TABLE_ALIAS <> 'BEN_PDW_TASK_LIST';
1164
1165 */
1166
1167 -- mark the future data Exists column
1168 mark_future_data_exists(p_copy_entity_txn_id);
1169 hr_utility.set_location('Leaving: '||l_proc,40);
1170 END copy_pln_record_pcp;
1171
1172
1173 PROCEDURE copy_pln_record_pcp(p_effective_date DATE,
1174 p_business_group_id NUMBER,
1175 p_copy_entity_txn_id NUMBER,
1176 p_pl_Id NUMBER) IS
1177 p_object_version_number NUMBER;
1178 l_copy_entity_result_id NUMBER;
1179 l_business_group_id NUMBER;
1180 l_effective_date DATE;
1181 l_opt_typ_cd Varchar2(15) ;
1182 l_proc varchar2(72) := g_package||'.copy_pln_record_pcp';
1183
1184 BEGIN
1185 hr_utility.set_location('Entering: '||l_proc,10);
1186
1187 hr_utility.set_location('copy plan: '||p_pl_id,20);
1188
1189 BEN_PLAN_DESIGN_PLAN_MODULE.CREATE_PLAN_RESULT
1190 (p_copy_entity_result_id => l_copy_entity_result_id
1191 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1192 ,p_pl_id => p_pl_Id
1193 ,p_business_group_id => p_business_group_id
1194 ,p_number_of_copies => 1
1195 ,p_object_version_number => p_object_version_number
1196 ,p_effective_date => p_effective_date
1197 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1198 ,p_plan_in_program => 'Y'
1199 );
1200
1201 populate_extra_Mapping_PLN
1202 (
1203 p_effective_date =>p_effective_date,
1204 p_business_group_id => p_business_group_id,
1205 p_copy_entity_txn_id => p_copy_entity_txn_id,
1206 p_copy_entity_result_id=> l_copy_entity_result_id
1207 );
1208
1209 -- Call Extyra Mappings for COP
1210 populate_extra_Mappings_COP
1211 (
1212 p_copy_entity_txn_id => p_copy_entity_txn_id,
1213 p_effective_date => p_effective_date,
1214 p_pgm_id => null
1215 );
1216
1217 -- Call Extra Mappings For EPA
1218 populate_extra_mappings_EPA
1219 (
1220 p_copy_entity_txn_id => p_copy_entity_txn_id,
1221 p_effective_date => p_effective_date
1222 );
1223
1224 populate_extra_mappings_CPY
1225 (
1226 p_copy_entity_txn_id => p_copy_entity_txn_id
1227 ,p_business_group_id => p_business_group_id
1228 ,p_effective_date => p_effective_date
1229 );
1230
1231 populate_extra_Mappings_LPR
1232 (
1233 p_copy_entity_txn_id =>p_copy_entity_txn_id,
1234 p_effective_date =>p_effective_date,
1235 p_pgm_id =>null
1236 );
1237
1238 populate_extra_Mappings_LOP
1239 (
1240 p_copy_entity_txn_id =>p_copy_entity_txn_id,
1241 p_effective_date =>p_effective_date,
1242 p_pgm_id =>null
1243 );
1244
1245 populate_extra_mappings_VPF(
1246 p_copy_entity_txn_id => p_copy_entity_txn_id,
1247 p_effective_date =>p_effective_date);
1248
1249 -- populate the extra mappings required for Criteria
1250 populate_extra_mappings_elp(
1251 p_copy_entity_txn_id => p_copy_entity_txn_id
1252 ,p_effective_date => p_effective_date
1253 );
1254
1255 -- mark the future data Exists column
1256 mark_future_data_exists(p_copy_entity_txn_id);
1257
1258 hr_utility.set_location('Leaving: '||l_proc,40);
1259 END copy_pln_record_pcp;
1260
1261 PROCEDURE remove_dpnt_rows
1262 (p_copy_entity_txn_id NUMBER,
1263 p_id NUMBER,
1264 p_table_alias VARCHAR2) IS
1265 l_proc varchar2(72) := g_package||'.remove_dpnt_rows';
1266
1267 BEGIN
1268 hr_utility.set_location('Entering: '||l_proc,10);
1269
1270 IF (p_table_alias = TABLE_ALIAS_CPP) THEN
1271
1272 hr_utility.set_location('Deleting CPP rows: '||p_id,20);
1273
1274 DELETE
1275 FROM ben_copy_entity_results
1276 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1277 AND information256 = p_id
1278 AND table_alias in (TABLE_ALIAS_LPR);
1279 END IF;
1280
1281 hr_utility.set_location('Leaving: '||l_proc,20);
1282
1283 END remove_dpnt_rows;
1284
1285 /*
1286
1287 PROCEDURE copy_pln_record_all (p_pl_id NUMBER,
1288 p_effective_date DATE,
1289 p_business_group_id NUMBER,
1290 p_copy_entity_txn_id NUMBER,
1291 p_copy_entity_result_id OUT NOCOPY NUMBER,
1292 p_ptp_copy_entity_result_id OUT NOCOPY NUMBER) IS
1293 cursor c_pln IS
1294 select pln.*
1295 from BEN_PL_F pln
1296 where pln.pl_id = p_pl_id
1297 and p_effective_date between effective_start_date and effective_end_date
1298 and NOT EXISTS (SELECT information1
1299 FROM BEN_COPY_ENTITY_RESULTS cer
1300 WHERE copy_entity_txn_id = p_copy_entity_txn_id
1301 AND table_alias = 'PLN'
1302 AND information1 = p_pl_id
1303 AND dml_operation = 'REUSE');
1304
1305 l_pln_rec BEN_PL_F%ROWTYPE;
1306 l_copy_entity_result_id NUMBER;
1307 l_ptp_copy_entity_result_id NUMBER;
1308 l_result_type_cd VARCHAR2(30) := 'HIDE';
1309 l_mirror_src_entity_result_id NUMBER;
1310 l_number_of_copies NUMBER := 0;
1311 l_table_route_id NUMBER;
1312 l_object_version_number NUMBER;
1313
1314 cursor c_table_route (c_table_alias VARCHAR) IS
1315 select table_route_id
1316 from pqh_table_route
1317 WHERE table_alias = c_table_alias;
1318 BEGIN
1319
1320
1321 open c_table_route('PLN');
1322 fetch c_table_route into l_table_route_id ;
1323 close c_table_route ;
1324
1325 open c_pln;
1326 fetch c_pln into l_pln_rec;
1327 IF c_pln%FOUND THEN
1328 ben_copy_entity_results_api.create_copy_entity_results(
1329 p_copy_entity_result_id => l_copy_entity_result_id,
1330 p_copy_entity_txn_id => p_copy_entity_txn_id,
1331 p_result_type_cd => l_result_type_cd,
1332 p_mirror_src_entity_result_id => l_mirror_src_entity_result_id,
1333 p_parent_entity_result_id => l_mirror_src_entity_result_id,
1334 p_number_of_copies => l_number_of_copies,
1335 p_table_route_id => l_table_route_id,
1336 p_table_alias => 'PLN',
1337 p_dml_operation => 'REUSE',
1338 P_INFORMATION1 => l_pln_rec.PL_ID,
1339 P_INFORMATION2 => l_pln_rec.EFFECTIVE_START_DATE,
1340 P_INFORMATION3 => l_pln_rec.EFFECTIVE_END_DATE,
1341 P_INFORMATION4 => l_pln_rec.BUSINESS_GROUP_ID,
1342 P_INFORMATION12 => l_pln_rec.NIP_DFLT_FLAG,
1343 P_INFORMATION13 => l_pln_rec.FRFS_DISTR_MTHD_CD,
1344 P_INFORMATION14 => l_pln_rec.PL_YR_NOT_APPLCBL_FLAG,
1345 P_INFORMATION15 => l_pln_rec.HC_SVC_TYP_CD,
1346 P_INFORMATION16 => l_pln_rec.NIP_ACTY_REF_PERD_CD,
1347 P_INFORMATION17 => l_pln_rec.ENRT_CD,
1348 P_INFORMATION18 => l_pln_rec.PRORT_PRTL_YR_CVG_RSTRN_CD,
1349 P_INFORMATION19 => l_pln_rec.PL_STAT_CD,
1350 P_INFORMATION20 => l_pln_rec.ENRT_CVG_STRT_DT_CD,
1351 P_INFORMATION21 => l_pln_rec.ENRT_CVG_END_DT_CD,
1352 P_INFORMATION22 => l_pln_rec.NIP_ENRT_INFO_RT_FREQ_CD,
1353 P_INFORMATION23 => l_pln_rec.VRFY_FMLY_MMBR_CD,
1354 P_INFORMATION24 => l_pln_rec.ALWS_TMPRY_ID_CRD_FLAG,
1355 P_INFORMATION25 => l_pln_rec.DRVBL_DPNT_ELIG_FLAG,
1356 P_INFORMATION26 => l_pln_rec.DRVBL_FCTR_PRTN_ELIG_FLAG,
1357 P_INFORMATION27 => l_pln_rec.DPNT_NO_CTFN_RQD_FLAG,
1358 P_INFORMATION28 => l_pln_rec.MAY_ENRL_PL_N_OIPL_FLAG,
1359 P_INFORMATION29 => l_pln_rec.DPNT_CVD_BY_OTHR_APLS_FLAG,
1360 P_INFORMATION30 => l_pln_rec.DPNT_ADRS_RQD_FLAG,
1361 P_INFORMATION31 => l_pln_rec.DPNT_LEG_ID_RQD_FLAG,
1362 P_INFORMATION32 => l_pln_rec.DPNT_DOB_RQD_FLAG,
1363 P_INFORMATION33 => l_pln_rec.DRVBL_FCTR_APLS_RTS_FLAG,
1364 P_INFORMATION34 => l_pln_rec.ELIG_APLS_FLAG,
1365 P_INFORMATION35 => l_pln_rec.NO_MX_OPTS_NUM_APLS_FLAG,
1366 P_INFORMATION36 => l_pln_rec.ALWS_QDRO_FLAG,
1367 P_INFORMATION37 => l_pln_rec.ALWS_QMCSO_FLAG,
1368 P_INFORMATION38 => l_pln_rec.HGHLY_CMPD_RL_APLS_FLAG,
1369 P_INFORMATION39 => l_pln_rec.ENRT_PL_OPT_FLAG,
1370 P_INFORMATION40 => l_pln_rec.FRFS_APLY_FLAG,
1371 P_INFORMATION41 => l_pln_rec.SVGS_PL_FLAG,
1372 P_INFORMATION42 => l_pln_rec.TRK_INELIG_PER_FLAG,
1373 P_INFORMATION43 => l_pln_rec.USE_ALL_ASNTS_ELIG_FLAG,
1374 P_INFORMATION44 => l_pln_rec.USE_ALL_ASNTS_FOR_RT_FLAG,
1375 P_INFORMATION45 => l_pln_rec.VSTG_APLS_FLAG,
1376 P_INFORMATION46 => l_pln_rec.PRTN_ELIG_OVRID_ALWD_FLAG,
1377 P_INFORMATION47 => l_pln_rec.HC_PL_SUBJ_HCFA_APRVL_FLAG,
1378 P_INFORMATION48 => l_pln_rec.WVBL_FLAG,
1379 P_INFORMATION49 => l_pln_rec.INVK_FLX_CR_PL_FLAG,
1380 P_INFORMATION50 => l_pln_rec.INVK_DCLN_PRTN_PL_FLAG,
1381 P_INFORMATION51 => l_pln_rec.ALWS_REIMBMTS_FLAG,
1382 P_INFORMATION52 => l_pln_rec.ALWS_UNRSTRCTD_ENRT_FLAG,
1383 P_INFORMATION53 => l_pln_rec.BNF_ADDL_INSTN_TXT_ALWD_FLAG,
1384 P_INFORMATION54 => l_pln_rec.BNF_ADRS_RQD_FLAG,
1385 P_INFORMATION55 => l_pln_rec.BNF_CTFN_RQD_FLAG,
1386 P_INFORMATION56 => l_pln_rec.BNF_CNTNGT_BNFS_ALWD_FLAG,
1387 P_INFORMATION57 => l_pln_rec.BNF_LEGV_ID_RQD_FLAG,
1388 P_INFORMATION58 => l_pln_rec.BNF_MAY_DSGT_ORG_FLAG,
1389 P_INFORMATION59 => l_pln_rec.BNF_QDRO_RL_APLS_FLAG,
1390 P_INFORMATION60 => l_pln_rec.BNF_DSGE_MNR_TTEE_RQD_FLAG,
1391 P_INFORMATION61 => l_pln_rec.NO_MN_CVG_AMT_APLS_FLAG,
1392 P_INFORMATION62 => l_pln_rec.NO_MX_CVG_AMT_APLS_FLAG,
1393 P_INFORMATION63 => l_pln_rec.NO_MN_CVG_INCR_APLS_FLAG,
1394 P_INFORMATION64 => l_pln_rec.NO_MX_CVG_INCR_APLS_FLAG,
1395 P_INFORMATION65 => l_pln_rec.NO_MN_OPTS_NUM_APLS_FLAG,
1396 P_INFORMATION66 => l_pln_rec.BNF_DOB_RQD_FLAG,
1397 P_INFORMATION67 => l_pln_rec.PL_CD,
1398 P_INFORMATION68 => l_pln_rec.CVG_INCR_R_DECR_ONLY_CD,
1399 P_INFORMATION69 => l_pln_rec.RQD_PERD_ENRT_NENRT_UOM,
1400 P_INFORMATION70 => l_pln_rec.SUBJ_TO_IMPTD_INCM_CD,
1401 P_INFORMATION71 => l_pln_rec.SUBJ_TO_IMPTD_INCM_TYP_CD,
1402 P_INFORMATION72 => l_pln_rec.UNSSPND_ENRT_CD,
1403 P_INFORMATION73 => l_pln_rec.IMPTD_INCM_CALC_CD,
1404 P_INFORMATION74 => l_pln_rec.RT_END_DT_CD,
1405 P_INFORMATION75 => l_pln_rec.RT_STRT_DT_CD,
1406 P_INFORMATION76 => l_pln_rec.PER_CVRD_CD,
1407 P_INFORMATION77 => l_pln_rec.BNFT_OR_OPTION_RSTRCTN_CD,
1408 P_INFORMATION78 => l_pln_rec.PCP_CD,
1409 P_INFORMATION79 => l_pln_rec.MX_WTG_PERD_PRTE_UOM,
1410 P_INFORMATION80 => l_pln_rec.MX_WTG_DT_TO_USE_CD,
1411 P_INFORMATION81 => l_pln_rec.NIP_PL_UOM,
1412 P_INFORMATION82 => l_pln_rec.BNF_DFLT_BNF_CD,
1413 P_INFORMATION83 => l_pln_rec.BNF_PCT_AMT_ALWD_CD,
1414 P_INFORMATION84 => l_pln_rec.CMPR_CLMS_TO_CVG_OR_BAL_CD,
1415 P_INFORMATION85 => l_pln_rec.DPNT_CVG_END_DT_CD,
1416 P_INFORMATION86 => l_pln_rec.DPNT_CVG_STRT_DT_CD,
1417 P_INFORMATION87 => l_pln_rec.DPNT_DSGN_CD,
1418 P_INFORMATION88 => l_pln_rec.NIP_DFLT_ENRT_CD,
1419 P_INFORMATION89 => l_pln_rec.BNF_DSGN_CD,
1420 P_INFORMATION90 => l_pln_rec.PRMRY_FNDG_MTHD_CD,
1421 P_INFORMATION91 => l_pln_rec.DFLT_TO_ASN_PNDG_CTFN_CD,
1422 P_INFORMATION92 => l_pln_rec.ENRT_MTHD_CD,
1423 P_INFORMATION93 => l_pln_rec.SHORT_CODE,
1424 P_INFORMATION94 => l_pln_rec.SHORT_NAME,
1425 P_INFORMATION95 => l_pln_rec.FUNCTION_CODE,
1426 P_INFORMATION96 => l_pln_rec.FRFS_CNTR_DET_CD,
1427 P_INFORMATION97 => l_pln_rec.FRFS_DISTR_DET_CD,
1428 P_INFORMATION98 => l_pln_rec.POST_TO_GL_FLAG,
1429 P_INFORMATION99 => l_pln_rec.FRFS_VAL_DET_CD,
1430 P_INFORMATION100 => l_pln_rec.FRFS_PORTION_DET_CD,
1431 P_INFORMATION101 => l_pln_rec.BNDRY_PERD_CD,
1432 P_INFORMATION110 => l_pln_rec.PLN_ATTRIBUTE_CATEGORY,
1433 P_INFORMATION111 => l_pln_rec.PLN_ATTRIBUTE1,
1434 P_INFORMATION112 => l_pln_rec.PLN_ATTRIBUTE2,
1435 P_INFORMATION113 => l_pln_rec.PLN_ATTRIBUTE3,
1436 P_INFORMATION114 => l_pln_rec.PLN_ATTRIBUTE4,
1437 P_INFORMATION115 => l_pln_rec.PLN_ATTRIBUTE5,
1438 P_INFORMATION116 => l_pln_rec.PLN_ATTRIBUTE6,
1439 P_INFORMATION117 => l_pln_rec.PLN_ATTRIBUTE7,
1440 P_INFORMATION118 => l_pln_rec.PLN_ATTRIBUTE8,
1441 P_INFORMATION119 => l_pln_rec.PLN_ATTRIBUTE9,
1442 P_INFORMATION120 => l_pln_rec.PLN_ATTRIBUTE10,
1443 P_INFORMATION121 => l_pln_rec.PLN_ATTRIBUTE11,
1444 P_INFORMATION122 => l_pln_rec.PLN_ATTRIBUTE12,
1445 P_INFORMATION123 => l_pln_rec.PLN_ATTRIBUTE13,
1446 P_INFORMATION124 => l_pln_rec.PLN_ATTRIBUTE14,
1447 P_INFORMATION125 => l_pln_rec.PLN_ATTRIBUTE15,
1448 P_INFORMATION126 => l_pln_rec.PLN_ATTRIBUTE16,
1449 P_INFORMATION127 => l_pln_rec.PLN_ATTRIBUTE17,
1450 P_INFORMATION128 => l_pln_rec.PLN_ATTRIBUTE18,
1451 P_INFORMATION129 => l_pln_rec.PLN_ATTRIBUTE19,
1452 P_INFORMATION130 => l_pln_rec.PLN_ATTRIBUTE20,
1453 P_INFORMATION131 => l_pln_rec.PLN_ATTRIBUTE21,
1454 P_INFORMATION132 => l_pln_rec.PLN_ATTRIBUTE22,
1455 P_INFORMATION133 => l_pln_rec.PLN_ATTRIBUTE23,
1456 P_INFORMATION134 => l_pln_rec.PLN_ATTRIBUTE24,
1457 P_INFORMATION135 => l_pln_rec.PLN_ATTRIBUTE25,
1458 P_INFORMATION136 => l_pln_rec.PLN_ATTRIBUTE26,
1459 P_INFORMATION137 => l_pln_rec.PLN_ATTRIBUTE27,
1460 P_INFORMATION138 => l_pln_rec.PLN_ATTRIBUTE28,
1461 P_INFORMATION139 => l_pln_rec.PLN_ATTRIBUTE29,
1462 P_INFORMATION140 => l_pln_rec.PLN_ATTRIBUTE30,
1463 P_INFORMATION141 => l_pln_rec.MAPPING_TABLE_NAME,
1464 P_INFORMATION142 => l_pln_rec.IVR_IDENT,
1465 P_INFORMATION170 => l_pln_rec.NAME,
1466 P_INFORMATION185 => l_pln_rec.URL_REF_NAME,
1467 P_INFORMATION235 => l_pln_rec.BNFT_PRVDR_POOL_ID,
1468 P_INFORMATION248 => l_pln_rec.PL_TYP_ID,
1469 P_INFORMATION250 => l_pln_rec.ACTL_PREM_ID,
1470 P_INFORMATION257 => l_pln_rec.FRFS_DISTR_MTHD_RL,
1471 P_INFORMATION258 => l_pln_rec.DPNT_CVG_END_DT_RL,
1472 P_INFORMATION259 => l_pln_rec.DPNT_CVG_STRT_DT_RL,
1473 P_INFORMATION260 => l_pln_rec.ENRT_CVG_END_DT_RL,
1474 P_INFORMATION262 => l_pln_rec.ENRT_CVG_STRT_DT_RL,
1475 P_INFORMATION263 => l_pln_rec.CR_DSTR_BNFT_PRVDR_POOL_ID,
1476 P_INFORMATION264 => l_pln_rec.VRFY_FMLY_MMBR_RL,
1477 P_INFORMATION265 => l_pln_rec.OBJECT_VERSION_NUMBER,
1478 P_INFORMATION266 => l_pln_rec.ORDR_NUM,
1479 P_INFORMATION267 => l_pln_rec.MX_CVG_WCFN_MLT_NUM,
1480 P_INFORMATION268 => l_pln_rec.PRORT_PRTL_YR_CVG_RSTRN_RL,
1481 P_INFORMATION269 => l_pln_rec.MN_OPTS_RQD_NUM,
1482 P_INFORMATION270 => l_pln_rec.MX_OPTS_ALWD_NUM,
1483 P_INFORMATION271 => l_pln_rec.MX_CVG_MLT_INCR_NUM,
1484 P_INFORMATION272 => l_pln_rec.DFLT_TO_ASN_PNDG_CTFN_RL,
1485 P_INFORMATION273 => l_pln_rec.MX_CVG_MLT_INCR_WCF_NUM,
1486 P_INFORMATION274 => l_pln_rec.ENRT_RL,
1487 P_INFORMATION275 => l_pln_rec.MX_WTG_DT_TO_USE_RL,
1488 P_INFORMATION276 => l_pln_rec.RQD_PERD_ENRT_NENRT_RL,
1489 P_INFORMATION277 => l_pln_rec.RT_END_DT_RL,
1490 P_INFORMATION278 => l_pln_rec.RT_STRT_DT_RL,
1491 P_INFORMATION279 => l_pln_rec.POSTELCN_EDIT_RL,
1492 P_INFORMATION280 => l_pln_rec.PLN_MN_CVG_ALWD_AMT,
1493 P_INFORMATION281 => l_pln_rec.AUTO_ENRT_MTHD_RL,
1494 P_INFORMATION282 => l_pln_rec.MX_WTG_PERD_RL,
1495 P_INFORMATION283 => l_pln_rec.MN_CVG_RL,
1496 P_INFORMATION284 => l_pln_rec.MX_CVG_RL,
1497 P_INFORMATION285 => l_pln_rec.COBRA_PYMT_DUE_DY_NUM,
1498 P_INFORMATION286 => l_pln_rec.NIP_DFLT_ENRT_DET_RL,
1499 P_INFORMATION287 => l_pln_rec.COST_ALLOC_KEYFLEX_1_ID,
1500 P_INFORMATION288 => l_pln_rec.COST_ALLOC_KEYFLEX_2_ID,
1501 P_INFORMATION289 => l_pln_rec.MX_WTG_PERD_PRTE_VAL,
1502 P_INFORMATION290 => l_pln_rec.BNF_MN_DSGNTBL_PCT_VAL,
1503 P_INFORMATION293 => l_pln_rec.BNF_PCT_INCRMT_VAL,
1504 P_INFORMATION294 => l_pln_rec.MAPPING_TABLE_PK_ID,
1505 P_INFORMATION295 => l_pln_rec.MX_CVG_WCFN_AMT,
1506 P_INFORMATION296 => l_pln_rec.MN_CVG_ALWD_AMT,
1507 P_INFORMATION297 => l_pln_rec.MX_CVG_INCR_ALWD_AMT,
1508 P_INFORMATION298 => l_pln_rec.MX_CVG_INCR_WCF_ALWD_AMT,
1509 P_INFORMATION299 => l_pln_rec.MX_CVG_ALWD_AMT,
1510 P_INFORMATION300 => l_pln_rec.MN_CVG_RQD_AMT,
1511 P_INFORMATION301 => l_pln_rec.RQD_PERD_ENRT_NENRT_VAL,
1512 P_INFORMATION302 => l_pln_rec.BNF_INCRMT_AMT,
1513 P_INFORMATION303 => l_pln_rec.BNF_MN_DSGNTBL_AMT,
1514 P_INFORMATION304 => l_pln_rec.FRFS_MX_CRYFWD_VAL,
1515 P_INFORMATION306 => l_pln_rec.INCPTN_DT,
1516 p_object_version_number => l_object_version_number,
1517 p_effective_date => p_effective_date
1518 );
1519 --
1520 p_copy_entity_result_id := l_copy_entity_result_id ;
1521
1522 -- COPY PL_TYP record
1523 copy_pl_typ_record (p_pl_typ_id => l_pln_rec.PL_TYP_ID,
1524 p_effective_date => p_effective_date,
1525 p_copy_entity_txn_id => p_copy_entity_txn_id,
1526 p_copy_entity_result_id => l_ptp_copy_entity_result_id);
1527
1528 p_ptp_copy_entity_result_id := l_ptp_copy_entity_result_id;
1529 END IF;
1530 CLOSE c_pln;
1531
1532
1533
1534 END;
1535
1536 */
1537
1538 Procedure create_ler_result
1539 (
1540 p_validate in number default 0 -- false
1541 ,p_copy_entity_result_id in number
1542 ,p_copy_entity_txn_id in number default null
1543 ,p_ler_id in number default null
1544 ,p_business_group_id in number default null
1545 ,p_number_of_copies in number default 0
1546 ,p_object_version_number out nocopy number
1547 ,p_effective_date in date
1548 )
1549 is
1550 l_proc varchar2(72) := g_package||'.create_ler_result';
1551 -- Summary of changes
1552 -- Added Table_alias and Removed typ_cd filter on LE Trigger
1553 -- Setting two who_columns
1554 Begin
1555 hr_utility.set_location('Entering: '||l_proc,10);
1556 ben_plan_design_plan_module.create_ler_result(
1557 p_validate => p_validate
1558 ,p_copy_entity_result_id => p_copy_entity_result_id
1559 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1560 ,p_ler_id => p_ler_id
1561 ,p_business_group_id => p_business_group_id
1562 ,p_number_of_copies => 1
1563 ,p_object_version_number => p_object_version_number
1564 ,p_effective_date => p_effective_date
1565 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1566 ) ;
1567
1568
1569
1570
1571 -- mark the future data Exists column
1572 mark_future_data_exists(p_copy_entity_txn_id);
1573
1574 hr_utility.set_location('Leaving: '||l_proc,20);
1575 End create_ler_result ;
1576
1577 /* This procedure is used to copy Delpro from ben to staging and is called when Delpro is directly viewed from the shuttle */
1578
1579 Procedure create_dep_elpro_result
1580 (
1581 p_copy_entity_txn_id in Number,
1582 p_effective_date in Date,
1583 p_business_group_id in Number,
1584 p_dep_elig_prfl_id in Number
1585 )
1586 is
1587 l_ovn_number Number;
1588 l_proc varchar2(72) := g_package||'.create_dep_elpro_result';
1589 begin
1590 hr_utility.set_location('Entering: '||l_proc,10);
1591 --
1592 --Call plan copy api to copy Profile and its criteria
1593 --
1594 ben_plan_design_elpro_module.create_dep_elig_prfl_results
1595 (
1596 p_copy_entity_txn_id => p_copy_entity_txn_id
1597 ,p_mirror_src_entity_result_id => null
1598 ,p_parent_entity_result_id => null
1599 ,p_dpnt_cvg_eligy_prfl_id => p_dep_elig_prfl_id
1600 ,p_business_group_id => p_business_group_id
1601 ,p_number_of_copies => 1
1602 ,p_object_version_number => l_ovn_number
1603 ,p_effective_date => p_effective_date
1604 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1605 );
1606
1607 -- mark the future data Exists column
1608 mark_future_data_exists(p_copy_entity_txn_id);
1609 hr_utility.set_location('Leaving: '||l_proc,20);
1610 End create_dep_elpro_result;
1611
1612 /* This procedure is used to copy Elpro from ben to staging and is called when elpro is directly viewed from the shuttle */
1613
1614 Procedure create_elpro_result
1615 (
1616 p_copy_entity_txn_id in Number,
1617 p_effective_date in Date,
1618 p_business_group_id in Number,
1619 p_elig_prfl_id in Number
1620 )
1621 is
1622 l_ovn_number Number;
1623 l_proc varchar2(72) := g_package||'.create_elpro_result';
1624 begin
1625 hr_utility.set_location('Entering: '||l_proc,10);
1626 --
1627 --Call plan copy api to copy Profile and its criteria
1628 --
1629 ben_plan_design_elpro_module.create_elig_prfl_results
1630 (
1631 p_copy_entity_txn_id => p_copy_entity_txn_id
1632 ,p_mirror_src_entity_result_id => null
1633 ,p_parent_entity_result_id => null
1634 ,p_eligy_prfl_id => p_elig_prfl_id
1635 ,p_business_group_id => p_business_group_id
1636 ,p_number_of_copies => 1
1637 ,p_object_version_number => l_ovn_number
1638 ,p_effective_date => p_effective_date
1639 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1640 );
1641
1642 -- populate the extra mappings required for Criteria
1643 populate_extra_mapping_elp(
1644 p_copy_entity_txn_id => p_copy_entity_txn_id
1645 ,p_effective_date => p_effective_date
1646 ,p_elig_prfl_id => p_elig_prfl_id);
1647
1648 -- mark the future data Exists column
1649 mark_future_data_exists(p_copy_entity_txn_id);
1650 hr_utility.set_location('Leaving: '||l_proc,20);
1651 End create_elpro_result;
1652
1653 /*-------------------------------------------------------------------------------------------------------
1654 +++++++++++++++++++++++++++++++ Prtn Elpro Procedures +++++++++++++++++++++++++++++++++++++++
1655 -------------------------------------------------------------------------------------------------------*/
1656 /*
1657 Private Methods For Eligibility Profiles
1658
1659 */
1660
1661
1662 FUNCTION get_prfl_name(
1663 p_eligy_prfl_id IN Number
1664 ,p_copy_entity_txn_id IN Number
1665 )
1666 RETURN VARCHAR2 IS
1667 Cursor csr_txn_prfl_name (
1668 c_eligy_prfl_id NUMBER
1669 ,c_copy_entity_txn_id NUMBER
1670 )
1671 IS
1672 Select information170 name
1673 from ben_copy_entity_results
1674 where table_alias='ELP'
1675 and copy_entity_txn_id=c_copy_entity_txn_id
1676 and information1=c_eligy_prfl_id;
1677 l_rec csr_txn_prfl_name%ROWTYPE;
1678
1679 BEGIN
1680
1681 OPEN csr_txn_prfl_name(p_eligy_prfl_id,p_copy_entity_txn_id );
1682 FETCH csr_txn_prfl_name into l_rec;
1683 CLOSE csr_txn_prfl_name;
1684 return l_rec.name;
1685
1686 END get_prfl_name;
1687 -- For a EPA record copy all eligy prfl in to staging
1688
1689 PROCEDURE create_elig_prfl_results(
1690 p_copy_entity_txn_id IN NUMBER
1691 ,p_prtn_elig_id IN NUMBER
1692
1693 ) IS
1694 l_proc varchar2(72) := g_package||'.create_elig_prfl_results';
1695
1696 --
1697 Cursor csr_txn_prfl(c_prtn_elig_id NUMBER) IS
1698 Select cep.information263 ELIGY_PRFL_ID,
1699 cep.information12 mndtry_flag ,
1700 cep.copy_entity_result_id
1701 From ben_copy_entity_results cep
1702 Where cep.copy_entity_txn_id=p_copy_entity_txn_id
1703 and cep.table_alias='CEP'
1704 and cep.INFORMATION229=c_prtn_elig_id;
1705
1706 --
1707 Cursor csr_chk_elp_exist (c_eligy_prfl_id NUMBER
1708 ,c_copy_txn_id NUMBER ) IS
1709 Select 1
1710 From ben_copy_entity_results
1711 Where table_alias='ELP'
1712 and copy_entity_txn_id=c_copy_txn_id
1713 and information1=c_eligy_prfl_id;
1714
1715 --
1716 l_dummy Varchar2(30);
1717 l_effective_date DATE;
1718 l_business_group_id NUMBER;
1719 l_ovn_number NUMBER;
1720
1721 --
1722 BEGIN
1723 hr_utility.set_location('Entering: '||l_proc,10);
1724 --
1725 get_txn_details (
1726 p_copy_entity_txn_id
1727 ,l_business_group_id
1728 ,l_effective_date
1729 );
1730 --
1731 FOR l_rec in csr_txn_prfl(p_prtn_elig_id)
1732 LOOP -- for each profile attached to this prtn_elig_id
1733 OPEN csr_chk_elp_exist(l_rec.ELIGY_PRFL_ID,p_copy_entity_txn_id);
1734 FETCH csr_chk_elp_exist into l_dummy;
1735 IF csr_chk_elp_exist%NOTFOUND -- if this profile is not already existing in staging
1736 THEN
1737 --
1738 --Call plan copy api to copy Profile and its criteria
1739 --
1740 ben_plan_design_elpro_module.create_elig_prfl_results
1741 (
1742 p_mirror_src_entity_result_id => l_rec.copy_entity_result_id
1743 ,p_parent_entity_result_id => l_rec.copy_entity_result_id
1744 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1745 ,p_eligy_prfl_id => l_rec.ELIGY_PRFL_ID
1746 ,p_mndtry_flag => l_rec.MNDTRY_FLAG
1747 ,p_business_group_id => l_business_group_id
1748 ,p_number_of_copies => 1
1749 ,p_object_version_number => l_ovn_number
1750 ,p_effective_date => l_effective_date
1751 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1752 );
1753 END IF;
1754 CLOSE csr_chk_elp_exist;
1755
1756 -- populate the extra mappings required for Criteria
1757 populate_extra_mapping_elp(
1758 p_copy_entity_txn_id => p_copy_entity_txn_id
1759 ,p_effective_date => l_effective_date
1760 ,p_elig_prfl_id => l_rec.ELIGY_PRFL_ID);
1761
1762 END LOOP;
1763
1764 -- mark the future data Exists column
1765 mark_future_data_exists(p_copy_entity_txn_id);
1766 hr_utility.set_location('Leaving: '||l_proc,20);
1767 END create_elig_prfl_results;
1768 ---
1769 ---Dumping all Eligibility Profiles in to Staging
1770 ---
1771 PROCEDURE dump_elig_prfls(
1772 p_copy_entity_txn_id IN NUMBER
1773 ) is
1774 --
1775 l_effective_date DATE;
1776 l_business_group_id NUMBER;
1777 l_ovn_number NUMBER;
1778 l_proc varchar2(72) := g_package||'.dump_elig_prfls';
1779
1780 --
1781 CURSOR get_bg_eligy_prfl IS
1782 Select eligy_prfl_id
1783 From ben_eligy_prfl_f
1784 where business_group_id =l_business_group_id
1785 and l_effective_date between effective_start_date and effective_end_date
1786 and stat_cd='A' and BNFT_CAGR_PRTN_CD='BNFT'
1787 and eligy_prfl_id not in (select information1
1788 from ben_copy_entity_results
1789 where copy_entity_txn_id=p_copy_entity_txn_id
1790 and table_alias='ELP'
1791 );
1792
1793 BEGIN
1794 hr_utility.set_location('Entering: '||l_proc,10);
1795 --
1796 get_txn_details (
1797 p_copy_entity_txn_id
1798 ,l_business_group_id
1799 ,l_effective_date
1800 );
1801 --
1802 FOR l_rec in get_bg_eligy_prfl
1803 LOOP -- for each profile not it staging
1804 --
1805 --Call plan copy api to copy Profile and its criteria
1806 --
1807 ben_plan_design_elpro_module.create_elig_prfl_results
1808 (
1809 p_mirror_src_entity_result_id => p_copy_entity_txn_id
1810 ,p_parent_entity_result_id => p_copy_entity_txn_id
1811 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1812 ,p_eligy_prfl_id => l_rec.ELIGY_PRFL_ID
1813 ,p_mndtry_flag => null
1814 ,p_business_group_id => l_business_group_id
1815 ,p_number_of_copies => 1
1816 ,p_object_version_number => l_ovn_number
1817 ,p_effective_date => l_effective_date
1818 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
1819 );
1820 END LOOP;
1821 hr_utility.set_location('Leaving: '||l_proc,20);
1822 END dump_elig_prfls;
1823
1824 ---
1825
1826 PROCEDURE create_vapro_results
1827 (
1828 p_copy_entity_txn_id IN NUMBER
1829 ,p_vrbl_cvg_rt_id IN NUMBER
1830 ,p_vrbl_usg_code IN VARCHAR2
1831 ) IS
1832 l_proc varchar2(72) := g_package||'.create_vapro_results';
1833
1834 Cursor csr_txn_prfl IS
1835 Select xyz.information262 VRBL_RT_PRFL_ID,
1836 xyz.copy_entity_result_id
1837 From ben_copy_entity_results xyz
1838 Where xyz.copy_entity_txn_id=p_copy_entity_txn_id
1839 and xyz.table_alias=decode(p_vrbl_usg_code,'CVG','BVR1','AVR')
1840 and decode(table_alias,'BVR1',information238,information253)=p_vrbl_cvg_rt_id
1841 and dml_operation <> 'DELETE';
1842
1843 Cursor csr_rate_row(l_effective_Date date) is
1844 select dml_operation,
1845 datetrack_mode,
1846 information32 uses_vrbl_rt_flag,
1847 future_data_exists
1848 from ben_copy_entity_results abr
1849 where abr.copy_entity_txn_id = p_copy_entity_txn_id
1850 and table_alias = 'ABR'
1851 and information1 = p_vrbl_cvg_rt_id
1852 and l_effective_date between information2 and information3
1853 and dml_operation <> 'DELETE';
1854 --
1855 l_rate_vpf_exits varchar2(1):='N';
1856 l_effective_date DATE;
1857 l_business_group_id NUMBER;
1858 l_ovn_number number;
1859 l_RT_ELIG_PRFL_FLAG varchar2(1):='N';
1860 l_elig_prfl_id NUMBER;
1861 l_elp_name varchar2(240);
1862 l_rate_row csr_rate_row%rowtype;
1863 --
1864 BEGIN
1865 hr_utility.set_location('Entering: '||l_proc,10);
1866 --
1867 get_txn_details (
1868 p_copy_entity_txn_id
1869 ,l_business_group_id
1870 ,l_effective_date
1871 );
1872 --
1873 FOR l_rec in csr_txn_prfl
1874 LOOP
1875 l_rate_vpf_exits:='Y';
1876 ben_pd_rate_and_cvg_module.create_vapro_results
1877 (
1878 P_COPY_ENTITY_RESULT_ID => l_rec.copy_entity_result_id
1879 ,P_COPY_ENTITY_TXN_ID => p_copy_entity_txn_id
1880 ,P_VRBL_RT_PRFL_ID => l_rec.VRBL_RT_PRFL_ID
1881 ,P_BUSINESS_GROUP_ID => l_business_group_id
1882 ,P_NUMBER_OF_COPIES => 1
1883 ,P_OBJECT_VERSION_NUMBER => l_ovn_number
1884 ,P_EFFECTIVE_DATE => l_effective_date
1885 ,P_PARENT_ENTITY_RESULT_ID => l_rec.copy_entity_result_id
1886 ,P_NO_DUP_RSLT => 'PDW_NO_DUP_RSLT'
1887 );
1888
1889 /* Below code copies the Elpro name attached to Vapro into information186 of
1890 * VPF row for those vapros which have Elpro attached, not Criteria attached */
1891 if(p_vrbl_usg_code='CVG')
1892 THEN
1893 Begin
1894 select
1895 information83 into l_RT_ELIG_PRFL_FLAG
1896 from
1897 ben_copy_entity_results
1898 where
1899 table_alias = 'VPF'
1900 and copy_entity_txn_id = p_copy_entity_txn_id
1901 and information1 = l_rec.VRBL_RT_PRFL_ID
1902 and l_effective_date between information2 and information3;
1903
1904 if(l_RT_ELIG_PRFL_FLAG = 'Y')
1905 THEN
1906 select
1907 elp.information1,
1908 elp.information170 into l_elig_prfl_id, l_elp_name
1909 from
1910 ben_copy_entity_results vpf,
1911 ben_copy_entity_results vep,
1912 ben_copy_entity_results elp
1913 where
1914 vpf.copy_entity_txn_id = elp.copy_entity_txn_id
1915 and vpf.copy_entity_txn_id = vep.copy_entity_txn_id
1916 and vpf.copy_entity_txn_id = p_copy_entity_txn_id
1917 and vpf.table_alias = 'VPF'
1918 and vep.table_alias = 'VEP'
1919 and elp.table_alias = 'ELP'
1920 and vpf.information1 = l_rec.VRBL_RT_PRFL_ID
1921 and vpf.information1 = vep.information262
1922 and elp.information1 = vep.information263
1923 and l_effective_date between vpf.information2 and vpf.information3
1924 and l_effective_date between vep.information2 and vep.information3
1925 and l_effective_date between elp.information2 and elp.information3;
1926
1927 update
1928 ben_copy_entity_results
1929 set
1930 INFORMATION266 = l_elig_prfl_id,
1931 INFORMATION186 = l_elp_name
1932 where
1933 copy_entity_txn_id = p_copy_entity_txn_id
1934 and table_alias = 'VPF'
1935 and information1 = l_rec.VRBL_RT_PRFL_ID
1936 and l_effective_date between information2 and information3;
1937 end if;
1938
1939 -- populate the extra mappings required for Criteria
1940 populate_extra_mapping_elp(
1941 p_copy_entity_txn_id => p_copy_entity_txn_id
1942 ,p_effective_date => l_effective_date
1943 ,p_elig_prfl_id => l_elig_prfl_id);
1944
1945 Exception When No_Data_Found Then
1946 Null;
1947 end;
1948 end if;
1949
1950 END LOOP;
1951
1952 if(p_vrbl_usg_code='RT') THEN
1953
1954 open csr_rate_row(l_effective_date);
1955 fetch csr_rate_row into l_rate_row;
1956 close csr_rate_row;
1957 -- if the dml_operation is reuse or update make it update
1958 -- if there exists some future row..make the date track mode as Correction else make it update'
1959
1960 if (l_rate_row.dml_operation = 'REUSE' or l_rate_row.dml_operation = 'UPDATE') and l_rate_row.uses_vrbl_rt_flag = l_rate_vpf_exits then
1961 -- then make no change since there is no change to rate row.
1962 null;
1963 elsif (l_rate_row.dml_operation = 'REUSE' or l_rate_row.dml_operation = 'UPDATE') and l_rate_row.uses_vrbl_rt_flag <> l_rate_vpf_exits then
1964 -- there can be two cases when future date may or may not exists
1965 -- if the future data exists we need to set datetrack mode to correction because we are not asking the question on page.
1966 if l_rate_row.future_data_exists = 'Y' then
1967 Update ben_copy_entity_results
1968 set INFORMATION32=l_rate_vpf_exits,
1969 dml_operation = 'UPDATE',
1970 datetrack_mode = 'CORRECTION'
1971 where copy_entity_txn_id=p_copy_entity_txn_id
1972 and table_alias='ABR'
1973 and information1= p_vrbl_cvg_rt_id
1974 and l_effective_date between information2 and information3;
1975 else
1976 Update ben_copy_entity_results
1977 set INFORMATION32=l_rate_vpf_exits,
1978 dml_operation = 'UPDATE',
1979 datetrack_mode = 'UPDATE'
1980 where copy_entity_txn_id=p_copy_entity_txn_id
1981 and table_alias='ABR'
1982 and information1= p_vrbl_cvg_rt_id
1983 and l_effective_date between information2 and information3;
1984 end if;
1985 else
1986 -- for create cases we just need to set the uses variable rate flag.
1987
1988 Update ben_copy_entity_results
1989 set INFORMATION32=l_rate_vpf_exits
1990 where copy_entity_txn_id=p_copy_entity_txn_id and table_alias='ABR'
1991 and information1= p_vrbl_cvg_rt_id
1992 and l_effective_date between information2 and information3;
1993 end if;
1994
1995 END IF;
1996
1997 -- mark the future data Exists column
1998 mark_future_data_exists(p_copy_entity_txn_id);
1999 hr_utility.set_location('Leaving: '||l_proc,20);
2000 END create_vapro_results;
2001
2002
2003 /* This procedure copies extra columns into VPF */
2004 procedure copy_vrbl_rt_prfl(
2005 p_copy_entity_txn_id Number,
2006 p_business_group_id Number,
2007 p_effective_date Date,
2008 p_vrbl_rt_prfl_id Number,
2009 p_parent_result_id Number)
2010 is
2011 l_proc varchar2(72) := g_package||'.copy_vrbl_rt_prfl';
2012
2013 cursor c_vpf (
2014 p_copy_entity_txn_id Number,
2015 p_effective_date Date,
2016 p_vrbl_rt_prfl_id Number)
2017 is
2018 select
2019 information186,
2020 information266,
2021 copy_entity_result_id
2022 from
2023 ben_copy_entity_results
2024 where
2025 table_alias = 'VPF'
2026 and copy_entity_txn_id = p_copy_entity_txn_id
2027 and information1 = p_vrbl_rt_prfl_id
2028 and p_effective_date between information2 and information3
2029 and (information266 is null or information186 is null)
2030 and dml_operation <> 'DELETE'
2031 and status='VALID'
2032 for update of information266, information186;
2033
2034 l_ovn_number Number;
2035 l_elig_prfl_id Number;
2036 l_elpro_name ben_copy_entity_results.information170%type;
2037 copy_extra_mappings varchar2(1);
2038
2039 begin
2040 hr_utility.set_location('Entering: '||l_proc,10);
2041 copy_extra_mappings := 'N';
2042
2043 ben_pd_rate_and_cvg_module.create_vapro_results
2044 (
2045 P_VALIDATE => 1
2046 ,P_COPY_ENTITY_RESULT_ID => null
2047 ,P_COPY_ENTITY_TXN_ID => p_copy_entity_txn_id
2048 ,P_VRBL_RT_PRFL_ID => p_vrbl_rt_prfl_id
2049 ,P_BUSINESS_GROUP_ID => p_business_group_id
2050 ,P_NUMBER_OF_COPIES => 1
2051 ,P_OBJECT_VERSION_NUMBER => l_ovn_number
2052 ,P_EFFECTIVE_DATE => p_effective_date
2053 ,P_PARENT_ENTITY_RESULT_ID => p_parent_result_id
2054 ,P_NO_DUP_RSLT => 'PDW_NO_DUP_RSLT'
2055 );
2056
2057
2058
2059 For l_vpf in c_vpf(p_copy_entity_txn_id,p_effective_date,p_vrbl_rt_prfl_id)
2060 LOOP
2061 Begin
2062 select
2063 elp.information1,
2064 elp.information170 into l_elig_prfl_id, l_elpro_name
2065 from
2066 ben_copy_entity_results vpf,
2067 ben_copy_entity_results vep,
2068 ben_copy_entity_results elp
2069 where
2070 vpf.copy_entity_txn_id = elp.copy_entity_txn_id
2071 and vpf.copy_entity_txn_id = vep.copy_entity_txn_id
2072 and vpf.copy_entity_txn_id = p_copy_entity_txn_id
2073 and vpf.table_alias = 'VPF'
2074 and vep.table_alias = 'VEP'
2075 and elp.table_alias = 'ELP'
2076 and vpf.information1 = p_VRBL_RT_PRFL_ID
2077 and vpf.information1 = vep.information262
2078 and elp.information1 = vep.information263
2079 and p_effective_date between vpf.information2 and vpf.information3
2080 and vpf.dml_operation <> 'DELETE' and vpf.status='VALID'
2081 and p_effective_date between vep.information2 and vep.information3
2082 and vep.dml_operation <> 'DELETE' and vep.status='VALID'
2083 and p_effective_date between elp.information2 and elp.information3
2084 and elp.dml_operation <> 'DELETE' and elp.status='VALID';
2085
2086 update
2087 ben_copy_entity_results
2088 set
2089 information266 = l_elig_prfl_id,
2090 information186 = l_elpro_name
2091 where
2092 current of c_vpf;
2093 Exception When No_Data_Found Then
2094 Null;
2095 end;
2096 end LOOP;
2097
2098 -- mark the future data Exists column
2099 mark_future_data_exists(p_copy_entity_txn_id);
2100 hr_utility.set_location('Leaving: '||l_proc,20);
2101 end copy_vrbl_rt_prfl;
2102
2103
2104
2105
2106 ---
2107 /* Dependant Elig Profiles
2108 The following procedures call plan copy apis to selectively copy delpro to staging area.
2109 Out of the following procedures we can probably ask plan copy to provide a public function create_dep_elpro_results
2110 which will just coy a Dpny Elig and its criteria
2111
2112
2113 */
2114 FUNCTION get_dpnt_prfl_name(
2115 p_eligy_prfl_id IN Number
2116 ,p_copy_entity_txn_id IN Number
2117 )
2118 RETURN VARCHAR2 IS
2119 Cursor csr_txn_prfl_name (
2120 c_eligy_prfl_id NUMBER
2121 ,c_copy_entity_txn_id NUMBER
2122 )
2123 IS
2124 Select information170 name
2125 from ben_copy_entity_results
2126 where table_alias='DCE'
2127 and copy_entity_txn_id=c_copy_entity_txn_id
2128 and information1=c_eligy_prfl_id;
2129 l_rec csr_txn_prfl_name%ROWTYPE;
2130 BEGIN
2131 OPEN csr_txn_prfl_name(p_eligy_prfl_id,p_copy_entity_txn_id );
2132 FETCH csr_txn_prfl_name into l_rec;
2133 CLOSE csr_txn_prfl_name;
2134 return l_rec.name;
2135 END get_dpnt_prfl_name;
2136 --------------------------------------------------------------------
2137
2138 procedure create_dep_elpro_results
2139 (
2140 p_copy_entity_txn_id in number
2141 ,p_dpnt_dsgn_object_id in number
2142 ,p_dpnt_dsgn_level_code in varchar2
2143 ) is
2144 l_proc varchar2(72) := g_package||'.create_dep_elpro_results';
2145 --
2146 Cursor csr_txn_prfl(c_dpnt_dsgn_object_id number ,c_dpnt_dsgn_level_code varchar2) IS
2147 Select ade.information255 ELIGY_PRFL_ID,
2148 ade.information11 mndtry_flag ,
2149 ade.copy_entity_result_id
2150 From ben_copy_entity_results ade
2151 Where ade.copy_entity_txn_id=p_copy_entity_txn_id
2152 and ade.table_alias='ADE'
2153 and decode(c_dpnt_dsgn_level_code,'PL',ade.information261,'PTIP',ade.information259)=c_dpnt_dsgn_object_id;
2154 --
2155 Cursor csr_chk_dce_exist (c_eligy_prfl_id NUMBER
2156 ,c_copy_txn_id NUMBER ) IS
2157 Select 1
2158 From ben_copy_entity_results
2159 Where table_alias='DCE'
2160 and copy_entity_txn_id=c_copy_txn_id
2161 and information1=c_eligy_prfl_id;
2162
2163 --
2164 l_dummy Varchar2(30);
2165 l_effective_date DATE;
2166 l_business_group_id NUMBER;
2167 l_ovn_number NUMBER;
2168
2169 --
2170 BEGIN
2171 hr_utility.set_location('Entering: '||l_proc,10);
2172 --
2173 get_txn_details (
2174 p_copy_entity_txn_id
2175 ,l_business_group_id
2176 ,l_effective_date
2177 );
2178 --
2179 FOR l_rec in csr_txn_prfl(p_dpnt_dsgn_object_id ,p_dpnt_dsgn_level_code )
2180 LOOP -- for each profile attached to this prtn_elig_id
2181 OPEN csr_chk_dce_exist(l_rec.ELIGY_PRFL_ID,p_copy_entity_txn_id);
2182 FETCH csr_chk_dce_exist into l_dummy;
2183 IF csr_chk_dce_exist%NOTFOUND -- if this profile is not already existing in staging
2184 THEN
2185 --
2186 --Call plan copy api to copy Profile and its criteria
2187 --
2188 ben_plan_design_elpro_module.create_dep_elig_prfl_results
2189 (
2190 p_mirror_src_entity_result_id => l_rec.copy_entity_result_id
2191 ,p_parent_entity_result_id => l_rec.copy_entity_result_id
2192 ,p_copy_entity_txn_id => p_copy_entity_txn_id
2193 ,p_dpnt_cvg_eligy_prfl_id => l_rec.ELIGY_PRFL_ID
2194 ,p_business_group_id => l_business_group_id
2195 ,p_number_of_copies => 1
2196 ,p_object_version_number => l_ovn_number
2197 ,p_effective_date => l_effective_date
2198 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
2199 );
2200
2201 /*
2202 -- populate the extra mappings required for Criteria
2203 populate_extra_mapping_elp(
2204 p_copy_entity_txn_id => p_copy_entity_txn_id
2205 ,p_effective_date => l_effective_date
2206 ,p_elig_prfl_id => l_rec.eligy_prfl_id);*/
2207
2208 END IF;
2209 CLOSE csr_chk_dce_exist;
2210 END LOOP;
2211 -- mark the future data Exists column
2212 mark_future_data_exists(p_copy_entity_txn_id);
2213 hr_utility.set_location('Leaving: '||l_proc,20);
2214 end create_dep_elpro_results;
2215
2216 procedure create_dep_elig_crtr_results
2217 (
2218 p_copy_entity_txn_id in number
2219 ,p_parent_entity_result_id in number
2220 ) IS
2221 l_proc varchar2(72) := g_package||'.create_dep_elig_crtr_results';
2222
2223 Cursor csr_dep_elig_criteria (c_parent_id NUMBER) IS
2224 Select table_alias,INFORMATION261, INFORMATION246
2225 From ben_copy_entity_results
2226 Where parent_entity_result_id=c_parent_id;
2227
2228 l_ovn_number NUMBER;
2229 l_effective_date DATE;
2230 l_business_group_id NUMBER;
2231
2232 BEGIN
2233 hr_utility.set_location('Entering: '||l_proc,10);
2234 get_txn_details (
2235 p_copy_entity_txn_id
2236 ,l_business_group_id
2237 ,l_effective_date
2238 );
2239 For l_rec in csr_dep_elig_criteria(p_parent_entity_result_id)
2240 LOOP
2241 IF l_rec.table_alias='EAC' THEN
2242 ben_pd_rate_and_cvg_module.create_drpar_results
2243 (
2244 p_copy_entity_result_id =>null
2245 ,p_copy_entity_txn_id => p_copy_entity_txn_id
2246 ,p_comp_lvl_fctr_id => null
2247 ,p_hrs_wkd_in_perd_fctr_id => null
2248 ,p_los_fctr_id => null
2249 ,p_pct_fl_tm_fctr_id => null
2250 ,p_age_fctr_id => l_rec.INFORMATION246
2251 ,p_cmbn_age_los_fctr_id => null
2252 ,p_business_group_id => null
2253 ,p_number_of_copies => null
2254 ,p_object_version_number => l_ovn_number
2255 ,p_effective_date => l_effective_date
2256 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
2257 );
2258 ELSE IF l_rec.table_alias='DPC' THEN
2259 copy_pln_record_pcp(l_effective_date
2260 ,l_business_group_id
2261 ,p_copy_entity_txn_id
2262 ,l_rec.INFORMATION261);
2263 END IF;
2264 END IF;
2265 END LOOP;
2266
2267 -- mark the future data Exists column
2268 mark_future_data_exists(p_copy_entity_txn_id);
2269 hr_utility.set_location('Leaving: '||l_proc,20);
2270 END create_dep_elig_crtr_results;
2271 -------------------------------------------------------------------------------------------
2272 FUNCTION staged_record_exists(
2273 p_table_alias IN VARCHAR2
2274 ,p_information1 IN NUMBER
2275 ,p_copy_entity_txn_id IN NUMBER
2276 )
2277 RETURN boolean IS
2278
2279 CURSOR csr_rec_exists IS
2280 Select 'Y'
2281 From BEN_COPY_ENTITY_RESULTS
2282 Where copy_entity_txn_id=p_copy_entity_txn_id
2283 AND table_alias=p_table_alias
2284 AND information1=p_information1
2285 AND result_type_cd='DISPLAY';
2286
2287 l_rec_exists BOOLEAN :=false;
2288 l_dummy VARCHAR2(1);
2289 BEGIN
2290 OPEN csr_rec_exists;
2291 FETCH csr_rec_exists into l_dummy;
2292 if csr_rec_exists%FOUND THEN l_rec_exists:=true; END IF;
2293 CLOSE csr_rec_exists;
2294 return l_rec_exists;
2295 END staged_record_exists;
2296
2297 procedure create_elig_crtr_results
2298 (
2299 p_copy_entity_txn_id in number
2300 ,p_parent_entity_result_id in number
2301 ) IS
2302 l_proc varchar2(72) := g_package||'.create_elig_crtr_results';
2303 --
2304 Cursor csr_elig_epg_criteria (c_parent_id NUMBER) IS
2305 Select table_alias,INFORMATION261,copy_entity_result_id,information5,information11
2306 From ben_copy_entity_results
2307 Where parent_entity_result_id=c_parent_id and TABLE_ALIAS='EPG';
2308 --
2309 Cursor csr_elig_criteria (c_parent_id NUMBER) IS
2310 Select table_alias, INFORMATION222,INFORMATION223,INFORMATION224,INFORMATION233,INFORMATION241,INFORMATION243,INFORMATION245,
2311 INFORMATION246,INFORMATION254,INFORMATION272
2312 From ben_copy_entity_results
2313 Where parent_entity_result_id=c_parent_id
2314 and TABLE_ALIAS in ('EAP','ECL','ECP','EHW','ELS','EPF','EBN','EPZ','ESA','ECV');
2315 --
2316
2317 Cursor key_id_flex_num(c_bg_id NUMBER) IS
2318 select org_information5
2319 from hr_organization_information org
2320 where org.organization_id =c_bg_id
2321 and org.org_information_context = 'Business Group Information';
2322 --
2323 l_ovn_number NUMBER;
2324 l_effective_date DATE;
2325 l_business_group_id NUMBER;
2326 l_ppl_flx VARCHAR2(240);
2327 l_table_alias VARCHAR2(30);
2328 l_information1 NUMBER;
2329 l_id_flex_num hr_organization_information.ORG_INFORMATION5%TYPE;
2330
2331 BEGIN
2332 hr_utility.set_location('Entering: '||l_proc,10);
2333 get_txn_details (
2334 p_copy_entity_txn_id
2335 ,l_business_group_id
2336 ,l_effective_date
2337 );
2338 /*
2339 *Copy concatenated People Group segment Values to Information1
2340 */
2341 For l_rec in csr_elig_epg_criteria(p_parent_entity_result_id)
2342 LOOP
2343 IF (l_rec.table_alias='EPG' and l_rec.INFORMATION5 is NULL) THEN
2344 IF l_id_FLEX_NUM is NULL THEN
2345 OPEN key_id_flex_num(l_business_group_id);
2346 FETCH key_id_flex_num into l_id_flex_num;
2347 CLOSE key_id_flex_num;
2348 END IF;
2349 hr_kflex_utility.UPD_OR_SEL_KEYFLEX_COMB
2350 (P_APPL_SHORT_NAME =>'PAY',
2351 P_FLEX_CODE =>'GRP',
2352 P_FLEX_NUM =>to_number(trunc(l_id_flex_num)),
2353 p_ccid=>l_rec.Information261,
2354 P_CONCAT_SEGMENTS_OUT =>l_ppl_flx
2355 );
2356 IF l_rec.information11='Y' THEN
2357 l_ppl_flx :=l_ppl_flx|| fnd_message.get_string('BEN','BEN_93294_PDC_EXCLUDE_FLAG');
2358 END IF;
2359 UPDATE ben_copy_entity_results set INFORMATION5= l_ppl_flx where copy_entity_result_id=l_rec.copy_entity_result_id;
2360 END IF;
2361 END LOOP;
2362 /*
2363 * For 6 drvd Factors + service Area + Bnft Group + Postal Codes we have to copy them to stage if they are used
2364 */
2365 For l_rec in csr_elig_criteria(p_parent_entity_result_id)
2366 LOOP
2367 l_table_alias :=null;
2368 IF l_rec.table_alias='EAP' THEN l_information1 :=l_rec.information246; l_table_alias :='AGF';
2369 elsif l_rec.table_alias='ECP' THEN l_information1 :=l_rec.information223; l_table_alias :='CLA';
2370 elsif l_rec.table_alias='ECL' THEN l_information1 :=l_rec.information254; l_table_alias :='CLF';
2371 elsif l_rec.table_alias='EHW' THEN l_information1 :=l_rec.information224; l_table_alias :='HWF';
2372 elsif l_rec.table_alias='ELS' THEN l_information1 :=l_rec.information243; l_table_alias :='LSF';
2373 elsif l_rec.table_alias='EPF' THEN l_information1 :=l_rec.information233; l_table_alias :='PFF';
2374 elsif l_rec.table_alias='ECV' THEN l_information1 :=l_rec.information272; l_table_alias :='EGL';
2375 END IF;
2376 IF l_table_alias is NOT NULL THEN
2377 copy_drvd_factor(p_copy_entity_txn_id ,l_table_alias ,l_information1);
2378 ELSIF l_rec.table_alias='EBN' THEN
2379 -- modified the table alias from BRG to BNG since rows of BRG are not created/copied
2380 IF (NOT staged_record_exists('BNG',l_rec.information222,p_copy_entity_txn_id)) THEN
2381 ben_pd_rate_and_cvg_module.create_bnft_group_results
2382 (
2383 p_copy_entity_result_id => null
2384 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
2385 ,p_benfts_grp_id =>l_rec.information222
2386 ,p_business_group_id =>l_business_group_id
2387 ,p_number_of_copies =>1
2388 ,p_object_version_number =>l_ovn_number
2389 ,p_effective_date =>l_effective_date
2390 ) ;
2391 END IF;
2392 --
2393 ELSIF l_rec.table_alias='ESA' THEN
2394 -- modified the table alias from SAR to SVA since rows of SAR are not created/copied
2395 IF (NOT staged_record_exists('SVA',l_rec.information241,p_copy_entity_txn_id)) THEN
2396 ben_pd_rate_and_cvg_module.create_service_results
2397 (
2398 p_copy_entity_result_id => null
2399 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
2400 ,p_svc_area_id =>l_rec.information241
2401 ,p_business_group_id =>l_business_group_id
2402 ,p_number_of_copies =>1
2403 ,p_object_version_number =>l_ovn_number
2404 ,p_effective_date =>l_effective_date
2405 ) ;
2406 END IF;
2407 --
2408 ELSIF l_rec.table_alias='EPZ' THEN
2409 IF (NOT staged_record_exists('RZR',l_rec.information245,p_copy_entity_txn_id)) THEN
2410 ben_pd_rate_and_cvg_module.create_postal_results
2411 (
2412 p_copy_entity_result_id => null
2413 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
2414 ,p_pstl_zip_rng_id =>l_rec.information245
2415 ,p_business_group_id =>l_business_group_id
2416 ,p_number_of_copies =>1
2417 ,p_object_version_number =>l_ovn_number
2418 ,p_effective_date =>l_effective_date
2419 ) ;
2420 END IF;
2421 END IF;
2422 END LOOP;
2423 -- mark the future data Exists column
2424 mark_future_data_exists(p_copy_entity_txn_id);
2425
2426 hr_utility.set_location('Leaving: '||l_proc,20);
2427 END create_elig_crtr_results;
2428
2429 PROCEDURE copy_bnft_bal(
2430 p_copy_entity_txn_id IN NUMBER,
2431 p_information1 IN NUMBER
2432 ) IS
2433 l_ovn_number NUMBER;
2434 l_effective_date DATE;
2435 l_business_group_id NUMBER;
2436 l_proc varchar2(72) := g_package||'.copy_bnft_bal';
2437 BEGIN
2438 hr_utility.set_location('Entering: '||l_proc,10);
2439 IF NOT staged_record_exists('BNB',p_information1,p_copy_entity_txn_id) THEN
2440 get_txn_details (p_copy_entity_txn_id ,l_business_group_id,l_effective_date);
2441 ben_pd_rate_and_cvg_module.create_bnft_bal_results
2442 (
2443 p_copy_entity_result_id => null
2444 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
2445 ,p_bnfts_bal_id =>p_information1
2446 ,p_business_group_id =>l_business_group_id
2447 ,p_number_of_copies =>1
2448 ,p_object_version_number =>l_ovn_number
2449 ,p_effective_date =>l_effective_date
2450 ) ;
2451 END IF;
2452 -- mark the future data Exists column
2453 mark_future_data_exists(p_copy_entity_txn_id);
2454 hr_utility.set_location('Leaving: '||l_proc,20);
2455 END copy_bnft_bal;
2456
2457 /* This function returns the relevant Criteria Name of the COBRA criteria
2458 The criteria Name could be Program or Program - PlanType Name
2459 The complexity is to always show the Pgm or PlanType name from Staging first
2460 and only if it is not present in Staging, show it from BEN */
2461 FUNCTION get_COBRA_criteria_name(
2462 p_copy_entity_txn_id Number,
2463 p_pgm_id Number,
2464 p_ctp_id Number
2465 )
2466 RETURN VARCHAR2 is
2467
2468 l_effective_date Date;
2469 l_business_group_id Number;
2470 l_overview_name ben_copy_entity_results.information5%type;
2471
2472 begin
2473 get_txn_details(p_copy_entity_txn_id,l_business_group_id,l_effective_date);
2474 l_overview_name := null;
2475 if(p_pgm_id is not null) then -- only Program is selected
2476 begin
2477 select -- if PGM is copied to staging, return the Pgm Name in Staging
2478 information170 into l_overview_name
2479 from
2480 ben_copy_entity_results
2481 where
2482 copy_entity_txn_id = p_copy_entity_txn_id
2483 and information1 = p_pgm_id
2484 and table_alias = 'PGM'
2485 and l_effective_date between information2 and information3;
2486 Exception when No_Data_Found Then
2487 begin
2488 select
2489 name into l_overview_name
2490 from
2491 ben_pgm_f pgm
2492 where
2493 pgm.business_group_id = l_business_group_id
2494 and pgm.pgm_id = p_pgm_id
2495 and l_effective_date between pgm.effective_start_date and pgm.effective_end_date;
2496 Exception when No_Data_Found Then
2497 -- We should ideally never reach here
2498 RAISE;
2499 end;
2500 end;
2501 elsif (p_ctp_id is not null) then -- PlanType in Pgm is chosen
2502 begin
2503 select -- if Pgm and Plan Type is copied to staging, then return their names in staging
2504 pgm.information170 || ' - ' || ptp.information170 into l_overview_name
2505 from
2506 ben_copy_entity_results pgm,
2507 ben_copy_entity_results ctp,
2508 ben_copy_entity_results ptp
2509 where
2510 ctp.copy_entity_txn_id = p_copy_entity_txn_id
2511 and ctp.table_alias = 'CTP'
2512 and ptp.copy_entity_txn_id = ctp.copy_entity_txn_id
2513 and ptp.table_alias = 'PTP'
2514 and pgm.copy_entity_txn_id = ctp.copy_entity_txn_id
2515 and pgm.table_alias = 'PGM'
2516 and ctp.information1 = p_ctp_id
2517 and l_effective_date between ctp.information2 and ctp.information3
2518 and ptp.information1 = ctp.information248
2519 and l_effective_date between ptp.information2 and ptp.information3
2520 and pgm.information1 = ctp.information260
2521 and l_effective_date between pgm.information2 and pgm.information3;
2522 Exception when No_Data_Found Then
2523 begin
2524 select
2525 pgm.name || ' - ' || ptp.name into l_overview_name
2526 from
2527 ben_pgm_f pgm,
2528 ben_ptip_f ctp,
2529 ben_pl_typ_f ptp
2530 where
2531 ctp.business_group_id = l_business_group_id
2532 and ctp.ptip_id = p_ctp_id
2533 and ctp.pgm_id = pgm.pgm_id
2534 and ptp.pl_typ_id = ctp.pl_typ_id
2535 and ctp.business_group_id = pgm.business_group_id
2536 and ptp.business_group_id = pgm.business_group_id
2537 and l_effective_date between pgm.effective_start_date and pgm.effective_end_date
2538 and l_effective_date between ptp.effective_start_date and ptp.effective_end_date
2539 and l_effective_date between ctp.effective_start_date and ctp.effective_end_date;
2540 Exception when No_Data_Found Then
2541 -- We should ideally never reach here
2542 RAISE;
2543 end;
2544 end;
2545 end if;
2546 return l_overview_name;
2547 end get_COBRA_criteria_name;
2548
2549 ------------------------------------------------------------------------------------------------------
2550
2551
2552 /* This Procedure will be called from Plan Design wizard pre-processor
2553 to
2554 1. Create Plan Year Periods from the existing Year periods attached to PGM
2555 This procedure will add all year periods to all the Plans in
2556 the Transaction
2557
2558 2. Sync the Sequence Numbers of Program Year Periods so that the sewuence numbers
2559 are ordered in PUI
2560 */
2561 --
2562 Procedure create_Plan_Yr_Periods
2563 (
2564 p_copy_entity_txn_id Number
2565 ,p_effective_Date Date
2566 ,p_business_group_id Number
2567 )
2568 is
2569 l_proc varchar2(72) := g_package||'.create_Plan_Yr_Periods';
2570 --
2571 Cursor C_PLN(p_copy_entity_txn_id number ,p_effective_date Date) is
2572 Select cpe.* from
2573 Ben_copy_entity_results cpe
2574 Where
2575 cpe.copy_Entity_txn_Id = p_copy_entity_txn_id
2576 And cpe.Table_Alias='PLN'
2577 --And cpe.Dml_operation='INSERT'
2578 And cpe.Dml_operation <> 'DELETE'
2579 And p_effective_date between cpe.Information2 And cpe.Information3 ;
2580
2581 Cursor C_CPY(p_copy_entity_txn_id number) is
2582 Select cpe.* from
2583 Ben_copy_entity_results cpe
2584 Where
2585 cpe.copy_Entity_txn_Id = p_copy_entity_txn_id
2586 And cpe.Table_Alias='CPY'
2587 --And cpe.Dml_operation='INSERT'
2588 And cpe.Dml_operation <> 'DELETE'
2589 And cpe.Information260 is not null
2590 Order by cpe.Information311,cpe.Information310
2591
2592 For Update of cpe.Information262 ;
2593
2594 l_copy_entity_result_id Number ;
2595 l_pkId Number ;
2596 l_object_version_number Number ;
2597 l_RESULT_TYPE_CD Varchar2(15) ;
2598 l_Sequence_Number Number(15) ;
2599 l_pgm_Yr_Perd_Sequence_Number Number(15) :=10 ;
2600 --
2601 Begin
2602 --
2603 hr_utility.set_location('Entering: '||l_proc,10);
2604 fnd_msg_pub.initialize ;
2605 l_RESULT_TYPE_CD :='DISPLAY' ;
2606
2607 /*
2608 -- Delete any existing CPY rows for PLN
2609 delete from ben_copy_entity_results
2610 where
2611 copy_entity_txn_id =p_copy_entity_txn_id
2612 And table_Alias='CPY'
2613 And information261 is not null ;
2614 -- end delete
2615
2616 -- Sync the Program Year Period Sequence Number
2617 --
2618 For l_CPY in c_CPY(p_copy_entity_txn_id) Loop
2619 --
2620 -- Sync the Sequence Numbers of PGM Yr Period so that they are in order
2621 -- --dbms_output.put_line(' pgm sequence '||l_pgm_Yr_Perd_Sequence_Number || ' for '||l_CPY.information311 ||'-'||l_CPY.information310);
2622
2623 Update Ben_Copy_Entity_Results
2624
2625 Set Information262 = l_pgm_Yr_Perd_Sequence_Number
2626
2627 Where Current Of c_CPY ;
2628
2629 l_pgm_Yr_Perd_Sequence_Number := l_pgm_Yr_Perd_Sequence_Number + 10 ;
2630
2631 End Loop ;
2632 -- End Sync the Program Year Period Sequence Number
2633 */
2634 --
2635
2636
2637
2638 -- Construct the Plan year Periods
2639
2640 -- Open and Create the CPY Rows for PLN
2641 For l_PLN in c_PLN(p_copy_entity_txn_id,p_effective_date) Loop
2642 --
2643 -- Get the Next Sequence Number from Ben
2644 -- It will start from 10 if there are no CPY records already existing
2645
2646 Select
2647 max(ordr_num)+10 into l_Sequence_Number
2648
2649 From
2650
2651 Ben_Popl_Yr_Perd cpy
2652
2653 Where
2654 cpy.pl_id = l_PLN.Information1
2655 And cpy.business_group_id = p_business_group_id;
2656
2657
2658 if l_sequence_number is null then
2659 l_Sequence_Number := 10 ;
2660 End If ;
2661
2662 For l_CPY in c_CPY(p_copy_entity_txn_id) Loop
2663 --
2664 l_copy_entity_result_id := null;
2665 l_object_version_number := null;
2666
2667
2668 Begin
2669 --
2670 -- Find out if this YRP is already attached to PLN via any CPY
2671 Select
2672 8 into l_pkId
2673
2674 From
2675
2676 Ben_Popl_Yr_Perd cpy,
2677 Ben_Yr_Perd yrp
2678 Where
2679 cpy.pl_id = l_PLN.Information1
2680 And cpy.business_group_id = p_business_group_id
2681 And cpy.business_group_id = yrp.business_group_id
2682 And cpy.yr_perd_id = yrp.yr_perd_id
2683 And yrp.start_date = l_CPY.information311
2684 And yrp.end_date = l_CPY.information310;
2685
2686 /*Select
2687 8 into l_pkId
2688
2689 From
2690
2691 Ben_copy_entity_results cpy
2692
2693 Where
2694 cpy.copy_entity_txn_id = p_copy_entity_txn_id
2695 And p_effective_date between cpy.information2 and cpy.information3
2696 And cpy.information261 = l_PLN.Information1
2697 And cpy.information311 =l_CPY.information311
2698 And cpy.information310 = l_CPY.information310
2699 And cpy.dml_operation <>'DELETE' ;*/
2700
2701
2702 Exception When No_Data_Found then
2703 --
2704 -- If there are No Records for this Year Period in Ben then create a CPY Row for
2705 -- a YRP
2706
2707 Select BEN_POPL_YR_PERD_S.nextval into l_pkId
2708 From dual ;
2709 -- dbms_output.put_line(' yrp id '||to_char(l_CPY.information1));
2710 ben_copy_entity_results_api.create_copy_entity_results
2711 (
2712 p_copy_entity_result_id => l_copy_entity_result_id,
2713 p_copy_entity_txn_id => p_copy_entity_txn_id,
2714 p_result_type_cd => l_result_type_cd,
2715 p_number_of_copies => 1,
2716 p_dml_operation =>'INSERT' ,
2717 p_table_Alias => 'CPY' ,
2718 p_information1 => l_pkId,
2719 p_information4 => l_CPY.information4,
2720 p_information5 => l_CPY.information311 ||'-'||l_CPY.information310, -- 9999 put name for h-grid
2721 --p_information265 => 0,
2722 --
2723 p_information261 => l_PLN.information1, -- Plan Id
2724 p_Information262 => l_Sequence_Number ,
2725 p_information240 => l_CPY.information240, -- Year Period Id
2726
2727 --
2728 p_object_version_number => l_object_version_number,
2729 p_effective_date => p_effective_date
2730
2731 );
2732 -- dbms_output.put_line(' RESULT ID '||L_Sequence_Number);
2733
2734 l_Sequence_Number := l_Sequence_Number + 10 ;
2735
2736 --
2737 --
2738 End ;
2739
2740 End Loop ;
2741 --
2742 End Loop ;
2743
2744 -- mark the future data Exists column
2745 mark_future_data_exists(p_copy_entity_txn_id);
2746
2747 hr_utility.set_location('Leaving: '||l_proc,20);
2748
2749 --
2750 End create_Plan_Yr_Periods ;
2751
2752 /*
2753 FUNCTION decode_Person_Change(
2754 Name varchar2
2755 ) Return varchar2
2756
2757 Is
2758 l_Code varchar2(15);
2759
2760 Begin
2761
2762 if(Name ='Any Value' ) then
2763 return 'OABANY';
2764 elsIf ( Name = 'No Value') then
2765 return 'NULL';
2766 elsIf (Name = 'Ex-employee') then
2767 return 'EMP';
2768 elsIf (Name = 'Employee') then
2769 return 'EX_EMP';
2770 elsIf (Name ='Fulltime-Regular') then
2771 return 'FR';
2772
2773 elsIf (Name ='Parttime-Regular') then
2774 return 'PR';
2775 elsIf (Name ='Marriage') then
2776 return 'M' ;
2777 elsIf (Name ='Birth of a Child') then
2778 return 'BC';
2779 elsIf (Name ='Applicant') then
2780 return 'APL' ;
2781
2782
2783 End If;
2784
2785 return null ;
2786 End ;
2787
2788
2789 */
2790
2791
2792 Function chkName
2793 (
2794 p_effective_date Date
2795 ,p_Name Varchar2
2796 ,p_business_group_id Number
2797 )
2798 Return Varchar2
2799 Is
2800 l_name Varchar2(500);
2801 l_Temp Varchar2(500);
2802
2803 Begin
2804 l_name := p_name ;
2805 l_name := hr_general.decode_lookup('BEN_PDW_SEEDED_LE_TRIGGERS',l_name);
2806
2807 -- Chk if this Trigger is already existing
2808 Begin
2809
2810 Select psl.Name into l_temp
2811 From
2812 BEN_PER_INFO_CHG_CS_LER_F psl
2813 Where
2814 psl.business_group_id = p_business_group_id
2815 And p_effective_date between psl.effective_start_date and psl.effective_end_date
2816 And psl.Name = l_name ;
2817
2818 Exception
2819 When No_Data_Found then
2820 -- Name does not exist
2821 l_temp :=Null ;
2822
2823 When Others then
2824 -- Multiple Rows with Same Name
2825 l_Temp := 'EX';
2826
2827 End ;
2828
2829 -- If a Trigger exists with this name then Create a Trigger
2830 -- with Name with _1 appended
2831 If (l_temp is Not Null) then
2832
2833 l_name := l_name || hr_general.decode_lookup('BEN_PDW_DPNT_BNF_HGRID_LABEL','DUP') ;
2834
2835 End If ;
2836 return l_name ;
2837 End ;
2838
2839
2840 /*
2841 This procedure is used to decode the person id for PER_PERSON_TYPES from lookup code
2842 NB: There can be multiple user names for a given system name. For decoding we will have
2843 to limit the rows to 1. We can use the default one as specified by DEFAULT_FLAG ='Y'
2844 */
2845 --
2846 Function decode_Value
2847 (
2848 p_business_group_id Number,
2849 p_val Varchar2 ,
2850 p_ler_trigger_code Varchar2
2851 )
2852 return varchar2
2853
2854 Is
2855 l_Val Varchar2(100);
2856
2857 Begin
2858 --
2859 l_Val := p_val ;
2860
2861 If p_ler_trigger_code in ('NEWHIRENE','NEWHIREAE','REHIRE') then
2862 --
2863 Begin
2864 --
2865 Select
2866
2867 to_char(PERSON_TYPE_ID) into l_val
2868 From
2869 Per_Person_Types ppt
2870 Where
2871 ppt.System_Person_Type =p_val
2872 And ppt.ACTIVE_FLAG='Y'
2873 And ppt.Business_Group_Id = p_business_group_id
2874 And ppt.default_flag='Y' ;
2875 Exception When No_Data_Found Then
2876 Null ;
2877 End ;
2878 --
2879 End If ;
2880
2881 return l_Val ;
2882
2883 --
2884 End decode_Value;
2885 --
2886 Procedure create_Life_Event_Triggers
2887 (
2888
2889 p_copy_entity_txn_id Number
2890 ,p_business_group_id Number
2891 ,p_effective_date Date
2892 ,p_effective_end_date Date
2893 )
2894 is
2895 l_proc varchar2(72) := g_package||'.create_Life_Event_Triggers';
2896
2897 --
2898 -- All psl in Ben for duplicate Check
2899 Cursor c_PSL(p_business_group_id Number ,
2900 p_effective_date Date,
2901 p_source_table varchar2,
2902 p_source_column varchar2,
2903 p_old_Val varchar2,
2904 P_new_val varchar2 ) is
2905
2906 Select
2907 psl.*
2908
2909 from
2910 BEN_PER_INFO_CHG_CS_LER_F psl
2911 Where
2912 psl.business_group_id = p_business_group_id
2913 And p_effective_date between psl.effective_start_date and psl.effective_end_date
2914 And
2915 (
2916 Upper(psl.SOURCE_TABLE) = upper(p_source_table)
2917 And upper(psl.Source_column)=upper(p_source_column)
2918 And psl.Old_Val = p_old_val
2919 And NVL(psl.New_Val,-1) = p_new_Val
2920 ) ;
2921
2922 -- All PSL in Txn which have a LPL attached to it
2923 Cursor c_CPE is
2924 Select cpe.*
2925 from
2926 Ben_copy_entity_Results cpe,
2927 Ben_copy_entity_Results lpl
2928 Where
2929 cpe.copy_entity_txn_id=0
2930 And cpe.Table_Alias ='PSL'
2931 And lpl.copy_entity_txn_id=p_copy_entity_txn_id
2932 And lpl.Table_Alias='LPL'
2933 And p_effective_date between lpl.Information2 and lpl.Information3
2934 And lpl.Information258 = cpe.copy_entity_result_id
2935 And lpl.dml_operation='INSERT' ;
2936
2937
2938
2939 l_per_info_chg_cs_ler_id Number ;
2940 l_source_table varchar2(200);
2941 l_source_column varchar2(200);
2942 l_Object_Version_Number Number ;
2943 l_count Number ;
2944 l_name Varchar2(500);
2945 l_copy_entity_result_id Number ;
2946 l_pkId Number;
2947 l_temp Varchar2(500);
2948 l_effective_Start_Date Date ;
2949 l_effective_End_Date Date ;
2950 l_result_type_cd Varchar2(15) ;
2951 --
2952
2953
2954 Begin
2955 hr_utility.set_location('Entering: '||l_proc,10);
2956 l_result_type_cd := 'DISPLAY';
2957 -- check if triggers are existing
2958
2959 For l_CPE in c_CPE Loop
2960 --
2961 l_name := l_CPE.information15 ;
2962 l_per_info_chg_cs_ler_id := null;
2963 For l_PSL_rec in c_PSL
2964 (p_business_group_id,p_effective_date,l_CPE.INFORMATION11,l_CPE.INFORMATION12,
2965 l_CPE.INFORMATION13,l_CPE.INFORMATION14) Loop
2966
2967 -- When the Trigger is existing get the PSL Id
2968 l_per_info_chg_cs_ler_id := l_PSL_rec.per_info_chg_cs_ler_id ;
2969
2970 -- dbms_output.put_line('existing is '||l_psl_rec.name);
2971 /** Manual Change - Replace this with Plan Copy
2972 Copy PSL Row to Staging
2973 */
2974
2975 ben_copy_entity_results_api.create_copy_entity_results(
2976 p_copy_entity_result_id => l_copy_entity_result_id,
2977 p_copy_entity_txn_id => p_copy_entity_txn_id,
2978 p_result_type_cd => 'DISPLAY',
2979 p_number_of_copies => 1,
2980 p_table_alias => 'PSL',
2981 p_Dml_Operation =>'REUSE',
2982 p_information1 => l_psl_rec.per_info_chg_cs_ler_id,
2983 p_information2 => l_psl_rec.EFFECTIVE_START_DATE,
2984 p_information3 => l_psl_rec.EFFECTIVE_END_DATE,
2985 p_information4 => l_psl_rec.business_group_id,
2986 p_information5 => null , -- 9999 put name for h-grid
2987 p_information218 => l_psl_rec.name,
2988 p_information186 => l_psl_rec.new_val,
2989 p_information185 => l_psl_rec.old_val,
2990 p_information260 => l_psl_rec.per_info_chg_cs_ler_rl,
2991 p_information111 => l_psl_rec.psl_attribute1,
2992 p_information120 => l_psl_rec.psl_attribute10,
2993 p_information121 => l_psl_rec.psl_attribute11,
2994 p_information122 => l_psl_rec.psl_attribute12,
2995 p_information123 => l_psl_rec.psl_attribute13,
2996 p_information124 => l_psl_rec.psl_attribute14,
2997 p_information125 => l_psl_rec.psl_attribute15,
2998 p_information126 => l_psl_rec.psl_attribute16,
2999 p_information127 => l_psl_rec.psl_attribute17,
3000 p_information128 => l_psl_rec.psl_attribute18,
3001 p_information129 => l_psl_rec.psl_attribute19,
3002 p_information112 => l_psl_rec.psl_attribute2,
3003 p_information130 => l_psl_rec.psl_attribute20,
3004 p_information131 => l_psl_rec.psl_attribute21,
3005 p_information132 => l_psl_rec.psl_attribute22,
3006 p_information133 => l_psl_rec.psl_attribute23,
3007 p_information134 => l_psl_rec.psl_attribute24,
3008 p_information135 => l_psl_rec.psl_attribute25,
3009 p_information136 => l_psl_rec.psl_attribute26,
3010 p_information137 => l_psl_rec.psl_attribute27,
3011 p_information138 => l_psl_rec.psl_attribute28,
3012 p_information139 => l_psl_rec.psl_attribute29,
3013 p_information113 => l_psl_rec.psl_attribute3,
3014 p_information140 => l_psl_rec.psl_attribute30,
3015 p_information114 => l_psl_rec.psl_attribute4,
3016 p_information115 => l_psl_rec.psl_attribute5,
3017 p_information116 => l_psl_rec.psl_attribute6,
3018 p_information117 => l_psl_rec.psl_attribute7,
3019 p_information118 => l_psl_rec.psl_attribute8,
3020 p_information119 => l_psl_rec.psl_attribute9,
3021 p_information110 => l_psl_rec.psl_attribute_category,
3022 p_information141 => l_psl_rec.source_column,
3023 p_information142 => l_psl_rec.source_table,
3024 p_information219 => l_psl_rec.whatif_lbl_txt,
3025 p_information187 => null,
3026 p_information188 => null,
3027 p_information265 => l_psl_rec.object_version_number,
3028 p_object_version_number => l_object_version_number,
3029 p_effective_date => p_effective_date );
3030
3031 /** Manual Change - Replace this with Plan Copy
3032 Copy PSL Row to Staging
3033 */
3034 --
3035
3036 --
3037 End Loop;
3038
3039 --dbms_output.put_line('ID IS '||l_per_info_chg_cs_ler_id);
3040
3041 If l_per_info_chg_cs_ler_id is null Then
3042 --
3043 -- Create This Trigger in Staging Table as it does not exist
3044 --
3045 --
3046 -- Get the Name of Life Event Trigger from Lookup
3047 --l_mirror_name :=l_name ;
3048
3049 --dbms_output.put_line('name is '||l_name);
3050 l_per_info_chg_cs_ler_id :=null ;
3051 Select
3052 BEN_PER_INFO_CHG_CS_LER_F_S.nextVal into
3053 l_per_info_chg_cs_ler_id
3054 From dual ;
3055
3056 -- Get the Name for the Ler Trigger
3057 l_name := chkName(p_effective_date,l_CPE.Information15,p_business_group_id);
3058
3059
3060 -- Create the Life event Triggers in Staging
3061 ben_copy_entity_results_api.create_copy_entity_results
3062 (
3063 p_copy_entity_result_id => l_copy_entity_result_id,
3064 p_copy_entity_txn_id => p_copy_entity_txn_id,
3065 p_result_type_cd => l_result_type_cd,
3066 p_information2 => p_effective_date,
3067 p_information3 => p_effective_end_date,
3068
3069 p_number_of_copies => 1,
3070 p_dml_operation =>'INSERT' ,
3071 p_table_Alias => 'PSL' ,
3072 p_information1 => l_per_info_chg_cs_ler_id,
3073 p_information4 => p_business_group_id,
3074 p_information11 =>'N',
3075 p_information141 => upper(l_CPE.INFORMATION12), --SOURCE COLUMN
3076 p_information142 => upper(l_CPE.INFORMATION11), --SOURCE TABLE
3077 p_Information185 => decode_Value( p_business_group_id,
3078 l_CPE.INFORMATION13,
3079 l_CPE.Information15), -- OLD_VAL
3080 p_INFORMATION186 => decode_Value( p_business_group_id,
3081 l_CPE.INFORMATION14,
3082 l_CPE.Information15), -- NEW_VAL
3083 p_INFORMATION218 => l_Name,
3084 --
3085 p_object_version_number => l_object_version_number,
3086 p_effective_date => p_effective_date
3087
3088 );
3089 --
3090 --
3091 End If ;
3092
3093
3094 -- Update the Foreign Key to PSL Table in LPL with appropriate Foreign Key Id
3095
3096 Update Ben_Copy_Entity_Results
3097 Set
3098 INFORMATION258 = l_per_info_chg_cs_ler_id -- PER_INFO_CHG_CS_LER_ID
3099 Where
3100 Copy_Entity_Txn_Id= p_copy_entity_txn_id
3101 --And p_effective_date between Information2 And Information3
3102 And Table_Alias='LPL'
3103 And Information258 = l_CPE.Copy_entity_result_id ;
3104 --And Information258= 369;
3105
3106 --
3107 End Loop;
3108
3109 -- mark the future data Exists column
3110 mark_future_data_exists(p_copy_entity_txn_id);
3111
3112 hr_utility.set_location('Leaving: '||l_proc,20);
3113 End create_Life_Event_Triggers ;
3114
3115
3116
3117
3118 FUNCTION Interim_Coverage_Lookup (lookupField in varchar2, lookupCd in varchar2) RETURN varchar2 IS
3119 BEGIN
3120 IF (lookupField like 'ApplIntrmCvgList2') then
3121 IF ( (lookupCd like 'CASDFNDF') or
3122 (lookupCd like 'CASDFNMN') or
3123 (lookupCd like 'CASDFNNL') or
3124 (lookupCd like 'CASDFNNT') or
3125 (lookupCd like 'CSEDFNDF') or
3126 (lookupCd like 'CSEDFNMN') or
3127 (lookupCd like 'CSEDFNNL') or
3128 (lookupCd like 'CSEDFNNT')) then return 'DEC';
3129
3130 end if;
3131
3132
3133 IF ( (lookupCd like 'CASMNNDF') or
3134 (lookupCd like 'CASMNNMN') or
3135 (lookupCd like 'CASMNNNL') or
3136 (lookupCd like 'CASMNNNT') or
3137 (lookupCd like 'CSEMNNDF') or
3138 (lookupCd like 'CSEMNNMN') or
3139 (lookupCd like 'CSEMNNNL') or
3140 (lookupCd like 'CSEMNNNT')) then return 'LLEMC';
3141
3142 end if;
3143
3144 IF ( (lookupCd like 'CASNLNDF') or
3145 (lookupCd like 'CASNLNMN') or
3146 (lookupCd like 'CASNLNNL') or
3147 (lookupCd like 'CASNLNNT') or
3148 (lookupCd like 'CSENLNDF') or
3149 (lookupCd like 'CSENLNNL') or
3150 (lookupCd like 'CSENLNMN') or
3151 (lookupCd like 'CSENLNNT')) then return 'OLLCE';
3152
3153 end if;
3154
3155 IF ( (lookupCd like 'CASNTNDF') or
3156 (lookupCd like 'CASNTNMN') or
3157 (lookupCd like 'CASNTNNL') or
3158 (lookupCd like 'CASNTNNT') or
3159 (lookupCd like 'CSENTNDF') or
3160 (lookupCd like 'CSENTNMN') or
3161 (lookupCd like 'CSENTNNL') or
3162 (lookupCd like 'CSENTNNT')) then return 'NONE';
3163
3164 end if;
3165
3166 IF ( (lookupCd like 'CASSMNDF') or
3167 (lookupCd like 'CASSMNMN') or
3168 (lookupCd like 'CASSMNNL') or
3169 (lookupCd like 'CASSMNNT') or
3170 (lookupCd like 'CSESMNDF') or
3171 (lookupCd like 'CSESMNMN') or
3172 (lookupCd like 'CSESMNNL') or
3173 (lookupCd like 'CSESMNNT')) then return 'KPPRVCVG';
3174
3175 end if;
3176 end if;
3177
3178
3179
3180 IF (lookupField like 'IntrmCvgCndtn1')
3181 then
3182 IF ( (lookupCd like 'CASDFNDF') or
3183 (lookupCd like 'CASDFNMN') or
3184 (lookupCd like 'CASDFNNL') or
3185 (lookupCd like 'CASDFNNT') or
3186 (lookupCd like 'CASMNNDF') or
3187 (lookupCd like 'CASMNNMN') or
3188 (lookupCd like 'CASMNNNL') or
3189 (lookupCd like 'CASMNNNT') or
3190 (lookupCd like 'CASNLNDF') or
3191 (lookupCd like 'CASNLNMN') or
3192 (lookupCd like 'CASNLNNL') or
3193 (lookupCd like 'CASNLNNT') or
3194 (lookupCd like 'CASNTNDF') or
3195 (lookupCd like 'CASNTNMN') or
3196 (lookupCd like 'CASNTNNL') or
3197 (lookupCd like 'CASNTNNT') or
3198 (lookupCd like 'CASSMNDF') or
3199 (lookupCd like 'CASSMNMN') or
3200 (lookupCd like 'CASSMNNL') or
3201 (lookupCd like 'CASSMNNT')) then return 'PRTTENRLDINPLTYP';
3202
3203 end if;
3204
3205 IF((lookupCd like 'CSEDFNDF') or
3206 (lookupCd like 'CSEDFNMN') or
3207 (lookupCd like 'CSEDFNNL') or
3208 (lookupCd like 'CSEDFNNT') or
3209 (lookupCd like 'CSEMNNDF') or
3210 (lookupCd like 'CSEMNNMN') or
3211 (lookupCd like 'CSEMNNNL') or
3212 (lookupCd like 'CSEMNNNT') or
3213 (lookupCd like 'CSENLNDF') or
3214 (lookupCd like 'CSENLNMN') or
3215 (lookupCd like 'CSENLNNL') or
3216 (lookupCd like 'CSENLNNT') or
3217 (lookupCd like 'CSENTNDF') or
3218 (lookupCd like 'CSENTNMN') or
3219 (lookupCd like 'CSENTNNL') or
3220 (lookupCd like 'CSENTNNT') or
3221 (lookupCd like 'CSESMNDF') or
3222 (lookupCd like 'CSESMNMN') or
3223 (lookupCd like 'CSESMNNL') or
3224 (lookupCd like 'CSESMNNT')) then return 'PRTTENRLDPLINPLTYP';
3225
3226 end if;
3227 end if; -- IntrmCondtn1
3228
3229 IF (lookupField like 'ApplIntrmCvgList1')
3230 then
3231 IF ((lookupCd like 'CASDFNDF') or
3232 (lookupCd like 'CASMNNDF') or
3233 (lookupCd like 'CASNLNDF') or
3234 (lookupCd like 'CASNTNDF') or
3235 (lookupCd like 'CASSMNDF') or
3236 (lookupCd like 'CSEDFNDF') or
3237 (lookupCd like 'CSEMNNDF') or
3238 (lookupCd like 'CSENLNDF') or
3239 (lookupCd like 'CSENTNDF') or
3240 (lookupCd like 'CSESMNDF')) then return 'DEC' ;
3241
3242 end if;
3243
3244 IF((lookupCd like 'CASDFNMN') or
3245 (lookupCd like 'CASMNNMN') or
3246 (lookupCd like 'CASNLNMN') or
3247 (lookupCd like 'CASNTNMN') or
3248 (lookupCd like 'CASSMNMN') or
3249 (lookupCd like 'CSEDFNMN') or
3250 (lookupCd like 'CSEMNNMN') or
3251 (lookupCd like 'CSENLNMN') or
3252 (lookupCd like 'CSENTNMN') or
3253 (lookupCd like 'CSESMNMN')) then return 'LLEMC';
3254
3255 end if;
3256
3257 IF ((lookupCd like 'CASDFNNL') or
3258 (lookupCd like 'CASMNNNL') or
3259 (lookupCd like 'CASNLNNL') or
3260 (lookupCd like 'CASNTNNL') or
3261 (lookupCd like 'CASSMNNL') or
3262 (lookupCd like 'CSEDFNNL') or
3263 (lookupCd like 'CSEMNNNL') or
3264 (lookupCd like 'CSENLNNL') or
3265 (lookupCd like 'CSENTNNL') or
3266 (lookupCd like 'CSESMNNL')) then return 'OLLCE';
3267
3268 end if;
3269
3270 IF ((lookupCd like 'CASDFNNT') or
3271 (lookupCd like 'CASMNNNT') or
3272 (lookupCd like 'CASNLNNT') or
3273 (lookupCd like 'CASNTNNT') or
3274 (lookupCd like 'CASSMNNT') or
3275 (lookupCd like 'CSEDFNNT') or
3276 (lookupCd like 'CSEMNNNT') or
3277 (lookupCd like 'CSENLNNT') or
3278 (lookupCd like 'CSENTNNT') or
3279 (lookupCd like 'CSESMNNT')) then return 'NONE';
3280
3281 end if;
3282 end if; -- ApplIntCvgCd2
3283
3284 IF (lookupField like 'ApplIntrmCvgList4')
3285 then
3286 IF ((lookupCd like 'CASDFNDF') or
3287 (lookupCd like 'CASDFNMN') or
3288 (lookupCd like 'CASDFNNL') or
3289 (lookupCd like 'CASDFNNT') or
3290 (lookupCd like 'CSODFNDF') or
3291 (lookupCd like 'CSODFNMN') or
3292 (lookupCd like 'CSODFNNL') or
3293 (lookupCd like 'CSODFNNT') or
3294 (lookupCd like 'CSEDFNDF') or
3295 (lookupCd like 'CSEDFNMN') or
3296 (lookupCd like 'CSEDFNNL') or
3297 (lookupCd like 'CSEDFNNT')) then return 'DEC';
3298
3299 end if;
3300
3301 IF ((lookupCd like 'CASMNNDF') or
3302 (lookupCd like 'CASMNNMN') or
3303 (lookupCd like 'CASMNNNL') or
3304 (lookupCd like 'CASMNNNT') or
3305 (lookupCd like 'CSOMNNDF') or
3306 (lookupCd like 'CSOMNNMN') or
3307 (lookupCd like 'CSOMNNNL') or
3308 (lookupCd like 'CSOMNNNT') or
3309 (lookupCd like 'CSEMNNDF') or
3310 (lookupCd like 'CSEMNNMN') or
3311 (lookupCd like 'CSEMNNNT') or
3312 (lookupCd like 'CSEMNNNL')) then return 'LLEMC';
3313
3314 end if;
3315
3316 IF ((lookupCd like 'CASNLNDF') or
3317 (lookupCd like 'CASNLNMN') or
3318 (lookupCd like 'CASNLNNL') or
3319 (lookupCd like 'CASNLNNT') or
3320 (lookupCd like 'CSONLNNL') or
3321 (lookupCd like 'CSONLNNT') or
3322 (lookupCd like 'CSONLNDF') or
3323 (lookupCd like 'CSONLNMN') or
3324 (lookupCd like 'CSENLNDF') or
3325 (lookupCd like 'CSENLNMN') or
3326 (lookupCd like 'CSENLNNL') or
3327 (lookupCd like 'CSENLNNT')) then return 'OLLCE';
3328
3329 end if;
3330
3331
3332 IF ((lookupCd like 'CASNTNDF') or
3333 (lookupCd like 'CASNTNMN') or
3334 (lookupCd like 'CASNTNNL') or
3335 (lookupCd like 'CASNTNNT') or
3336 (lookupCd like 'CSONTNDF') or
3337 (lookupCd like 'CSONTNMN') or
3338 (lookupCd like 'CSONTNNL') or
3339 (lookupCd like 'CSONTNNT') or
3340 (lookupCd like 'CSENTNDF') or
3341 (lookupCd like 'CSENTNMN') or
3342 (lookupCd like 'CSENTNNL') or
3343 (lookupCd like 'CSENTNNT')) then return 'NONE';
3344
3345 end if;
3346
3347 IF ((lookupCd like 'CASSMNDF') or
3348 (lookupCd like 'CASSMNMN') or
3349 (lookupCd like 'CASSMNNL') or
3350 (lookupCd like 'CASSMNNT') or
3351 (lookupCd like 'CSOSMNDF') or
3352 (lookupCd like 'CSOSMNMN') or
3353 (lookupCd like 'CSOSMNNL') or
3354 (lookupCd like 'CSOSMNNT') or
3355 (lookupCd like 'CSESMNDF') or
3356 (lookupCd like 'CSESMNMN') or
3357 (lookupCd like 'CSESMNNL') or
3358 (lookupCd like 'CSESMNNT')) then return 'KPPRVCVG';
3359
3360 end if;
3361 end if;
3362
3363 IF (lookupField like 'IntrmCvgCndtn2')
3364 then
3365 IF((lookupCd like 'CASDFNDF') or
3366 (lookupCd like 'CASDFNMN') or
3367 (lookupCd like 'CASDFNNL') or
3368 (lookupCd like 'CASDFNNT') or
3369 (lookupCd like 'CASMNNDF') or
3370 (lookupCd like 'CASMNNMN') or
3371 (lookupCd like 'CASMNNNL') or
3372 (lookupCd like 'CASMNNNT') or
3373 (lookupCd like 'CASNLNDF') or
3374 (lookupCd like 'CASNLNMN') or
3375 (lookupCd like 'CASNLNNL') or
3376 (lookupCd like 'CASNLNNT') or
3377 (lookupCd like 'CASNTNDF') or
3378 (lookupCd like 'CASNTNMN') or
3379 (lookupCd like 'CASNTNNL') or
3380 (lookupCd like 'CASNTNNT') or
3381 (lookupCd like 'CASSMNDF') or
3382 (lookupCd like 'CASSMNMN') or
3383 (lookupCd like 'CASSMNNL') or
3384 (lookupCd like 'CASSMNNT')) then return 'PRTTENRLDINPLTYP';
3385
3386 end if;
3387
3388 IF ((lookupCd like 'CSODFNDF') or
3389 (lookupCd like 'CSODFNMN') or
3390 (lookupCd like 'CSODFNNL') or
3391 (lookupCd like 'CSODFNNT') or
3392 (lookupCd like 'CSOMNNDF') or
3393 (lookupCd like 'CSOMNNMN') or
3394 (lookupCd like 'CSOMNNNL') or
3395 (lookupCd like 'CSOMNNNT') or
3396 (lookupCd like 'CSONLNDF') or
3397 (lookupCd like 'CSONLNMN') or
3398 (lookupCd like 'CSONLNNL') or
3399 (lookupCd like 'CSONLNNT') or
3400 (lookupCd like 'CSONTNDF') or
3401 (lookupCd like 'CSONTNMN') or
3402 (lookupCd like 'CSONTNNL') or
3403 (lookupCd like 'CSONTNNT') or
3404 (lookupCd like 'CSOSMNDF') or
3405 (lookupCd like 'CSOSMNMN') or
3406 (lookupCd like 'CSOSMNNL') or
3407 (lookupCd like 'CSOSMNNT')) then return 'PRTTENRLDOPTINPL';
3408
3409 end if;
3410
3411 IF((lookupCd like 'CSEDFNDF') or
3412 (lookupCd like 'CSEDFNMN') or
3413 (lookupCd like 'CSEDFNNL') or
3414 (lookupCd like 'CSEDFNNT') or
3415 (lookupCd like 'CSEMNNDF') or
3416 (lookupCd like 'CSEMNNMN') or
3417 (lookupCd like 'CSEMNNNL') or
3418 (lookupCd like 'CSEMNNNT') or
3419 (lookupCd like 'CSENLNDF') or
3420 (lookupCd like 'CSENLNMN') or
3421 (lookupCd like 'CSENLNNL') or
3422 (lookupCd like 'CSENLNNT') or
3423 (lookupCd like 'CSENTNDF') or
3424 (lookupCd like 'CSENTNMN') or
3425 (lookupCd like 'CSENTNNL') or
3426 (lookupCd like 'CSENTNNT') or
3427 (lookupCd like 'CSESMNDF') or
3428 (lookupCd like 'CSESMNMN') or
3429 (lookupCd like 'CSESMNNL') or
3430 (lookupCd like 'CSESMNNT')) then return 'PRTTENRLDPLINPLTYP';
3431
3432 end if;
3433 end if;
3434
3435
3436 IF (lookupField like 'ApplIntrmCvgList3')
3437 then
3438 IF((lookupCd like 'CASDFNDF') or (lookupCd like 'CASMNNDF') or (lookupCd like 'CASNLNDF') or
3439 (lookupCd like 'CASNTNDF') or (lookupCd like 'CASSMNDF') or (lookupCd like 'CSODFNDF') or
3440 (lookupCd like 'CSOMNNDF') or (lookupCd like 'CSONLNDF') or (lookupCd like 'CSONTNDF') or
3441 (lookupCd like 'CSOSMNDF') or (lookupCd like 'CSEDFNDF') or (lookupCd like 'CSEMNNDF') or
3442 (lookupCd like 'CSENLNDF') or (lookupCd like 'CSENTNDF') or (lookupCd like 'CSESMNDF') )
3443 then return 'DEC';
3444
3445 end if;
3446
3447 IF ((lookupCd like 'CASDFNMN') or
3448 (lookupCd like 'CASMNNMN') or
3449 (lookupCd like 'CASNLNMN') or
3450 (lookupCd like 'CASNTNMN') or
3451 (lookupCd like 'CASSMNMN') or
3452 (lookupCd like 'CSODFNMN') or
3453 (lookupCd like 'CSOMNNMN') or
3454 (lookupCd like 'CSONLNMN') or
3455 (lookupCd like 'CSONTNMN') or
3456 (lookupCd like 'CSOSMNMN') or
3457 (lookupCd like 'CSEDFNMN') or
3458 (lookupCd like 'CSEMNNMN') or
3459 (lookupCd like 'CSENLNMN') or
3460 (lookupCd like 'CSENTNMN') or
3461 (lookupCd like 'CSESMNMN') ) then return 'LLEMC';
3462
3463 end if;
3464
3465 IF ((lookupCd like 'CASDFNNL') or
3466 (lookupCd like 'CASMNNNL') or
3467 (lookupCd like 'CASNLNNL') or
3468 (lookupCd like 'CASNTNNL') or
3469 (lookupCd like 'CASSMNNL') or
3470 (lookupCd like 'CSODFNNL') or
3471 (lookupCd like 'CSOMNNNL') or
3472 (lookupCd like 'CSONLNNL') or
3473 (lookupCd like 'CSONTNNL') or
3474 (lookupCd like 'CSOSMNNL') or
3475 (lookupCd like 'CSEDFNNL') or
3476 (lookupCd like 'CSEMNNNL') or
3477 (lookupCd like 'CSENLNNL') or
3478 (lookupCd like 'CSENTNNL') or
3479 (lookupCd like 'CSESMNNL')) then return 'OLLCE';
3480
3481 end if;
3482
3483 IF ((lookupCd like 'CASDFNNT') or
3484 (lookupCd like 'CASMNNNT') or
3485 (lookupCd like 'CASNLNNT') or
3486 (lookupCd like 'CASNTNNT') or
3487 (lookupCd like 'CASSMNNT') or
3488 (lookupCd like 'CSODFNNT') or
3489 (lookupCd like 'CSOMNNNT') or
3490 (lookupCd like 'CSONLNNT') or
3491 (lookupCd like 'CSONTNNT') or
3492 (lookupCd like 'CSOSMNNT') or
3493 (lookupCd like 'CSEDFNNT') or
3494 (lookupCd like 'CSEMNNNT') or
3495 (lookupCd like 'CSENLNNT') or
3496 (lookupCd like 'CSENTNNT') or
3497 (lookupCd like 'CSESMNNT')) then return 'NONE';
3498
3499 end if; -- ApplIntrmCvgList4
3500 end if;
3501 return null;
3502
3503 END Interim_Coverage_Lookup;
3504
3505 /* This procedure copies all the Postal Zip and Benefits Grp factor in business group in to Staging. This is required
3506 since we allow modification of existing PoastalZip/ Bnfts Grp by displaying all of them at a time in the Factor Page */
3507 Procedure copy_PostalZip_Bnft_Grp(
3508 p_copy_entity_txn_id in Number
3509 ) is
3510
3511 cursor c_RZR(p_business_group_id Number) is
3512 select
3513 PSTL_ZIP_RNG_ID
3514 from
3515 BEN_PSTL_ZIP_RNG_F
3516 where
3517 business_group_id = p_business_group_id;
3518
3519 cursor c_BNG(p_business_group_id Number) is
3520 select
3521 BENFTS_GRP_ID
3522 from
3523 BEN_BENFTS_GRP
3524 where business_group_id = p_business_group_id;
3525
3526 l_business_group_id NUMBER;
3527 l_ovn_number NUMBER;
3528 l_effective_date DATE;
3529 l_proc varchar2(72) := g_package||'.copy_PostalZip_Bnft_Grp';
3530
3531 begin
3532 hr_utility.set_location('Entering: '||l_proc,10);
3533 get_txn_details(
3534 p_copy_entity_txn_id,
3535 l_business_group_id,
3536 l_effective_date
3537 );
3538
3539 FOR l_RZR in c_RZR(l_business_group_id)
3540 Loop
3541 IF (NOT staged_record_exists('RZR',l_RZR.PSTL_ZIP_RNG_ID,p_copy_entity_txn_id)) THEN
3542 ben_pd_rate_and_cvg_module.create_postal_results(
3543 p_copy_entity_result_id => null
3544 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
3545 ,p_pstl_zip_rng_id =>l_RZR.PSTL_ZIP_RNG_ID
3546 ,p_business_group_id =>l_business_group_id
3547 ,p_number_of_copies =>1
3548 ,p_object_version_number =>l_ovn_number
3549 ,p_effective_date =>l_effective_date
3550 ) ;
3551 end if;
3552 end loop;
3553
3554 FOR l_BNG in c_BNG(l_business_group_id)
3555 Loop
3556 IF (NOT staged_record_exists('BNG',l_BNG.BENFTS_GRP_ID,p_copy_entity_txn_id)) THEN
3557 ben_pd_rate_and_cvg_module.create_bnft_group_results(
3558 p_copy_entity_result_id => null
3559 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
3560 ,p_benfts_grp_id =>l_BNG.BENFTS_GRP_ID
3561 ,p_business_group_id =>l_business_group_id
3562 ,p_number_of_copies =>1
3563 ,p_object_version_number =>l_ovn_number
3564 ,p_effective_date =>l_effective_date
3565 ) ;
3566 END if;
3567 END Loop;
3568 commit;
3569 hr_utility.set_location('Leaving: '||l_proc,20);
3570 end copy_PostalZip_Bnft_Grp;
3571
3572 Procedure Create_YRP_Result
3573 (
3574
3575 p_copy_entity_txn_id Number
3576 ,p_business_group_id Number
3577 ,p_effective_date Date
3578
3579 )
3580
3581 Is
3582 l_proc varchar2(72) := g_package||'.Create_YRP_Result';
3583
3584
3585 ---------------------------------------------------------------
3586 -- START OF BEN_YR_PERD ----------------------
3587 ---------------------------------------------------------------
3588
3589 cursor c_yrp(c_table_alias varchar2) is
3590 select yrp.*
3591 from BEN_YR_PERD yrp
3592 where
3593
3594 yrp.business_group_id = p_business_group_id
3595 and not exists (
3596 select null
3597 from ben_copy_entity_results cpe,
3598 pqh_table_route trt
3599 where copy_entity_txn_id = p_copy_entity_txn_id
3600 and trt.table_route_id = cpe.table_route_id
3601
3602 and trt.table_alias = c_table_alias
3603 and information1 = yrp.yr_perd_id
3604 and information4 = yrp.business_group_id
3605 );
3606
3607 cursor c_table_route(c_parent_table_alias varchar2) is
3608 select table_route_id
3609 from pqh_table_route trt
3610 where
3611 trt.table_alias = c_parent_table_alias ;
3612
3613
3614 l_out_yrp_result_id Number(15);
3615 l_copy_entity_result_id Number ;
3616 l_Object_Version_Number Number;
3617 l_TABLE_ROUTE_ID Number ;
3618 l_INFORMATION5 Varchar2(500);
3619 l_RESULT_TYPE_CD varchar2(10);
3620
3621 ---------------------------------------------------------------
3622 -- END OF BEN_YR_PERD ----------------------
3623 ---------------------------------------------------------------
3624
3625 Begin
3626 hr_utility.set_location('Entering: '||l_proc,10);
3627
3628 for l_yrp_rec in c_yrp('YRP') loop
3629 --
3630 --
3631 l_table_route_id := null ;
3632 open c_table_route('YRP');
3633 fetch c_table_route into l_table_route_id ;
3634 close c_table_route ;
3635 --
3636 l_information5 := TO_CHAR(l_yrp_rec.start_date,'DD-Mon-YYYY')||' - '||
3637 TO_CHAR(l_yrp_rec.end_date,'DD-Mon-YYYY'); --'Intersection';
3638 --
3639
3640 l_result_type_cd := 'DISPLAY';
3641 --
3642 l_copy_entity_result_id := null;
3643 l_object_version_number := null;
3644
3645 -- Call Plan Copy api for copying yrp rows during pdw insert
3646 /*ben_plan_design_plan_module.create_yr_perd_result
3647 (
3648 p_copy_entity_txn_id => p_copy_entity_txn_id
3649 ,p_effective_date => p_effective_date
3650 ,p_version_number => l_object_version_number
3651 ,p_copy_entity_result_id=> l_copy_entity_result_id
3652 );*/
3653
3654 -- added param "p_no_dup_rslt => 'PDW_NO_DUP_RSLT'"
3655 ben_plan_design_plan_module.create_yr_perd_result
3656 (
3657 p_copy_entity_result_id => l_copy_entity_result_id
3658 ,p_copy_entity_txn_id => p_copy_entity_txn_id
3659 ,p_yr_perd_id => l_YRP_rec.yr_perd_id
3660 ,p_business_group_id => p_business_group_id
3661 ,p_number_of_copies => 1
3662 ,p_object_version_number => l_object_version_number
3663 ,p_effective_date => p_effective_date
3664 ,p_parent_entity_result_id => l_copy_entity_result_id
3665 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
3666 );
3667
3668
3669 -- commented out custom create_yrp implementation
3670
3671 end loop;
3672
3673 populate_extra_mappings_CPY
3674 (
3675 p_copy_entity_txn_id => p_copy_entity_txn_id
3676 ,p_business_group_id => p_business_group_id
3677 ,p_effective_date => p_effective_date
3678 );
3679
3680 -- woraround - for Now Dump All Elpros into staging
3681 -- we are now dumping elpros only in Criteria Set page where it is required
3682 -- dumping them here makes the program page very ineffecient
3683 -- dump_elig_prfls(p_copy_entity_txn_id);
3684
3685 -- This is to copy all the PZips and BnftsGrps in BG to staging
3686 -- copy_PostalZip_Bnft_Grp(p_copy_entity_txn_id);
3687 --
3688 -- mark the future data Exists column
3689 mark_future_data_exists(p_copy_entity_txn_id);
3690 hr_utility.set_location('Leaving: '||l_proc,20);
3691 End Create_YRP_Result;
3692
3693 --
3694 -- This Function implements the Pre-Processor for Plan Design Wizard. It will do the following
3695 -- * Attach all Year Periods to All Plans in Txn
3696 -- * Create Seeded Life Event Triggers if not already there
3697 --
3698
3699 PROCEDURE pre_Processor(
3700 p_validate Number
3701 , p_copy_entity_txn_id Number
3702 ,p_business_group_id Number
3703 ,p_effective_date Date
3704 ,p_exception OUT NOCOPY Varchar2
3705
3706 )
3707
3708 is
3709
3710 --pragma AUTONOMOUS_TRANSACTION;
3711
3712 l_Temp Varchar2(500);
3713 l_proc varchar2(72) := g_package||'.pre_Processor';
3714 l_max_sequence Number;
3715 l_sequence Number;
3716
3717 cursor c_cpp_sequence is
3718 select
3719 information263 ordr_num
3720 from
3721 ben_copy_entity_results
3722 where
3723 copy_entity_txn_id = p_copy_entity_txn_id
3724 and table_alias = 'CPP'
3725 and p_effective_date between information2 and information3
3726 and information263 is null
3727 for update of information263;
3728
3729 cursor c_ctp_sequence is
3730 select
3731 information268 ordr_num
3732 from
3733 ben_copy_entity_results
3734 where
3735 copy_entity_txn_id = p_copy_entity_txn_id
3736 and table_alias = 'CTP'
3737 and p_effective_date between information2 and information3
3738 and information268 is null
3739 for update of information268;
3740
3741 Begin
3742 hr_utility.set_location('Entering: '||l_proc,10);
3743 --
3744 l_temp:=Null ;
3745 fnd_msg_pub.initialize ;
3746
3747 -- Create Plan Year Periods
3748 create_Plan_Yr_Periods
3749 (
3750 p_copy_entity_txn_id => p_copy_entity_txn_id
3751 ,p_effective_date => p_effective_date
3752 ,p_business_group_id => p_business_group_id
3753 );
3754
3755
3756 -- Create Life Event Triggers
3757 create_Life_Event_Triggers
3758 (
3759 p_copy_entity_txn_id => p_copy_entity_txn_id
3760 ,p_business_group_id => p_business_group_id
3761 ,p_effective_date => p_effective_date
3762 ,p_effective_end_date => to_date('31-12-4712','DD-MM-YYYY')
3763 );
3764
3765 -- Fill the sequence number for Imputed Shell Plan and Imputed Shell Plan Type
3766 -- All CPP and CTP records will already have sequence numbers entered
3767 -- CPP and CTP records with no sequence numbers will be of Imputed Shell plan / plantypes
3768 max_sequence(
3769 p_copy_entity_txn_id,
3770 p_effective_date,
3771 'CPP',
3772 NULL,
3773 l_max_sequence);
3774 l_sequence := (round((l_max_sequence/10),0)+1)*10;
3775 for p_cpp in c_cpp_sequence loop
3776 update
3777 ben_copy_entity_results
3778 set
3779 information263 = l_sequence
3780 where current of c_cpp_sequence;
3781 l_sequence := l_sequence + 10;
3782 end loop;
3783
3784 max_sequence(
3785 p_copy_entity_txn_id,
3786 p_effective_date,
3787 'CTP',
3788 NULL,
3789 l_max_sequence);
3790 l_sequence := (round((l_max_sequence/10),0)+1)*10;
3791 for p_ctp in c_ctp_sequence loop
3792 update
3793 ben_copy_entity_results
3794 set
3795 information268 = l_sequence
3796 where current of c_ctp_sequence;
3797 l_sequence := l_sequence + 10;
3798 end loop;
3799
3800 p_Exception :=Null ;
3801 -- if p_validate = 0 then
3802 -- commit ;
3803 -- else
3804 -- rollback ;
3805 -- End If ;
3806 --
3807 hr_utility.set_location('Leaving: '||l_proc,20);
3808 Exception When Others Then
3809 p_Exception := sqlerrm ;
3810 rollback;
3811 raise ;
3812
3813 End pre_Processor;
3814
3815 FUNCTION GET_BALANCE_NAME( p_balance_id IN Number,
3816 p_bnft_balance_id IN NUMBER,
3817 p_business_group_id IN Number,
3818 p_copy_entity_txn_id IN NUMBER,
3819 p_effective_date IN DATE
3820 )
3821 RETURN VARCHAR2 IS
3822 Cursor csr_balance(c_balance_id NUMBER,c_bg_id NUMBER) IS
3823 select pbt.balance_name||' - '||pbd.dimension_name name
3824 from pay_balance_types pbt,pay_balance_dimensions pbd, pay_defined_balances pdb
3825 where (pdb.business_group_id is null or pdb.business_group_id = c_bg_id )
3826 and pdb.balance_type_id = pbt.balance_type_id
3827 and pdb.balance_dimension_id = pbd.balance_dimension_id
3828 and pdb.defined_balance_id=c_balance_id;
3829
3830 Cursor csr_bnft_balance( c_balance_id NUMBER,c_bg_id NUMBER,c_effective_date DATE) IS
3831 select name
3832 from ben_bnfts_bal_f
3833 where business_group_id = c_bg_id
3834 and c_effective_date between effective_start_date and effective_end_date
3835 and bnfts_bal_id =c_balance_id;
3836
3837 Cursor csr_new_bnft_balance(c_balance_id NUMBER,c_txn_id NUMBER,c_effective_date DATE) IS
3838 select information170
3839 from ben_copy_entity_results where
3840 table_alias='BNB' and copy_entity_txn_id=c_txn_id
3841 and information1=c_balance_id and
3842 c_effective_date between information2 and information3;
3843
3844 l_name varchar2(240);
3845 BEGIN
3846 IF p_balance_id is NOT NULL
3847 THEN
3848 OPEN csr_balance(p_balance_id,p_business_group_id);
3849 FETCH csr_balance INTO l_name;
3850 CLOSE csr_balance;
3851 ELSE
3852 OPEN csr_bnft_balance(p_bnft_balance_id,p_business_group_id,p_effective_date);
3853 FETCH csr_bnft_balance INTO l_name;
3854 CLOSE csr_bnft_balance;
3855 IF l_name is null THEN
3856 OPEN csr_new_bnft_balance(p_bnft_balance_id,p_copy_entity_txn_id,p_effective_date);
3857 FETCH csr_new_bnft_balance INTO l_name;
3858 CLOSE csr_new_bnft_balance;
3859 END IF;
3860 END IF;
3861 RETURN l_name;
3862 END GET_BALANCE_NAME;
3863
3864 FUNCTION GET_CURRENCY(p_currency_code IN VARCHAR2,p_effective_date IN DATE)
3865 RETURN VARCHAR2 IS
3866 CURSOR csr_currency (c_currency_code VARCHAR2, c_effective_date DATE) IS
3867 select name
3868 from fnd_currencies_vl
3869 where (start_date_active is null or start_date_active <=c_effective_date)
3870 and (end_date_active is null or end_date_active >= c_effective_date)
3871 and enabled_flag = 'Y' and currency_code=c_currency_code;
3872
3873 l_name fnd_currencies_vl.NAME%TYPE;
3874 BEGIN
3875 OPEN csr_currency (p_currency_code ,p_effective_date );
3876 FETCH csr_currency into l_name;
3877 CLOSE csr_currency;
3878 RETURN l_name;
3879 END GET_CURRENCY;
3880 /*
3881 * Generic Function to get Information170 column..to be used in VO's
3882 */
3883 Function get_stage_object_Name(
3884 p_copy_entity_txn_id IN NUMBER
3885 ,p_table_alias IN VARCHAR2
3886 ,p_information1 IN NUMBER
3887 )
3888 Return VARCHAR2 IS
3889 Cursor csr_stage_obj(
3890 p_effective_date Date
3891 )
3892 IS
3893 Select information170
3894 From ben_copy_entity_results
3895 Where copy_entity_txn_id=p_copy_entity_txn_id
3896 and table_alias=p_table_alias
3897 and information1=p_information1
3898 and p_effective_date between nvl(information2,p_effective_date) and nvl(information3,p_effective_date);
3899
3900 l_name ben_copy_entity_results.information170%TYPE;
3901 l_table_name pqh_table_route.where_clause%TYPE;
3902 l_effective_date pqh_copy_entity_txns.SRC_EFFECTIVE_DATE%TYPE;
3903 l_bg_id pqh_copy_entity_txns.CONTEXT_BUSINESS_GROUP_ID%TYPE;
3904 Begin
3905 get_txn_details(
3906 p_copy_entity_txn_id,
3907 l_bg_id,
3908 l_effective_date
3909 );
3910
3911 if p_table_alias='PFF'
3912 then
3913 begin
3914 select information218 into l_name
3915 from ben_copy_entity_results
3916 where copy_entity_txn_id=p_copy_entity_txn_id
3917 and table_alias=p_table_alias
3918 and information1=p_information1;
3919 Exception when no_data_found then
3920 l_name := null;
3921 end;
3922 return l_name;
3923 elsif p_table_alias='RZR'
3924 then
3925 begin
3926 select information142||' - '|| information141 into l_name
3927 from ben_copy_entity_results
3928 where copy_entity_txn_id=p_copy_entity_txn_id
3929 and table_alias=p_table_alias
3930 and information1=p_information1
3931 and rownum=1;
3932 Exception when no_data_found then
3933 l_name := null;
3934 end;
3935 Return l_name;
3936 else
3937 Open csr_stage_obj(l_effective_date);
3938 Fetch csr_stage_obj into l_name;
3939 -- The below code is to ensure that if the Plan is not found in staging, the Plan name is retrieved from the ben table
3940 IF (csr_stage_obj%NOTFOUND and p_table_alias ='PLN') then
3941 begin
3942
3943 select
3944 name into l_name
3945 from
3946 ben_pl_f
3947 where
3948 pl_id = p_information1
3949 and business_group_id = l_bg_id
3950 and l_effective_date between effective_start_date and effective_end_date;
3951
3952 Exception when no_data_found then
3953 null;
3954 end;
3955 end if;
3956
3957 Close csr_stage_obj;
3958 Return l_name;
3959 end if;
3960 End get_stage_object_Name;
3961
3962 /*
3963 *Procedure to copy drvd factors
3964 */
3965 PROCEDURE copy_drvd_factor(
3966 p_copy_entity_txn_id IN NUMBER
3967 ,p_table_alias IN VARCHAR2
3968 ,p_information1 IN NUMBER
3969 ) IS
3970 l_proc varchar2(72) := g_package||'.copy_drvd_factor';
3971
3972 Cursor csr_rec_exists is
3973 Select 'Y'
3974 From BEN_COPY_ENTITY_RESULTS
3975 Where copy_entity_txn_id=p_copy_entity_txn_id
3976 And table_alias=p_table_alias
3977 And information1=p_information1
3978 And result_type_cd='DISPLAY';
3979 --
3980
3981 l_dummy VARCHAR2(1);
3982 l_comp_lvl_fctr_id NUMBER;
3983 l_hrs_wkd_in_perd_fctr_id NUMBER;
3984 l_los_fctr_id NUMBER;
3985 l_pct_fl_tm_fctr_id NUMBER;
3986 l_age_fctr_id NUMBER;
3987 l_cmbn_age_los_fctr_id NUMBER;
3988 l_business_group_id NUMBER;
3989 l_ovn_number NUMBER;
3990 l_effective_date DATE;
3991 BEGIN
3992 hr_utility.set_location('Entering: '||l_proc,10);
3993 OPEN csr_rec_exists;
3994 FETCH csr_rec_exists into l_dummy;
3995 IF csr_rec_exists%NOTFOUND THEN
3996 --
3997 get_txn_details (
3998 p_copy_entity_txn_id
3999 ,l_business_group_id
4000 ,l_effective_date
4001 );
4002
4003 IF p_table_alias='BNG' THEN
4004 IF (NOT staged_record_exists('BNG',p_information1,p_copy_entity_txn_id)) THEN
4005 ben_pd_rate_and_cvg_module.create_bnft_group_results
4006 (
4007 p_copy_entity_result_id => null
4008 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
4009 ,p_benfts_grp_id =>p_information1
4010 ,p_business_group_id =>l_business_group_id
4011 ,p_number_of_copies =>1
4012 ,p_object_version_number =>l_ovn_number
4013 ,p_effective_date =>l_effective_date
4014 ) ;
4015 RETURN;
4016 END IF;
4017 --
4018 ELSIF p_table_alias='SVA' THEN
4019 IF (NOT staged_record_exists('SVA',p_information1,p_copy_entity_txn_id)) THEN
4020 -- setting g_pdw_allow_dup_rlst to ensure that duplicat Postal Zip values are not copied in to staging
4021 ben_plan_design_program_module.g_pdw_allow_dup_rslt := ben_plan_design_program_module.g_pdw_no_dup_rslt;
4022 ben_pd_rate_and_cvg_module.create_service_results
4023 (
4024 p_copy_entity_result_id => null
4025 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
4026 ,p_svc_area_id =>p_information1
4027 ,p_business_group_id =>l_business_group_id
4028 ,p_number_of_copies =>1
4029 ,p_object_version_number =>l_ovn_number
4030 ,p_effective_date =>l_effective_date
4031 ) ;
4032 ben_plan_design_program_module.g_pdw_allow_dup_rslt := NULL ;
4033 RETURN;
4034 END IF;
4035 --
4036 ELSIF p_table_alias='RZR' THEN
4037 IF (NOT staged_record_exists('RZR',p_information1,p_copy_entity_txn_id)) THEN
4038 ben_pd_rate_and_cvg_module.create_postal_results
4039 (
4040 p_copy_entity_result_id => null
4041 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
4042 ,p_pstl_zip_rng_id =>p_information1
4043 ,p_business_group_id =>l_business_group_id
4044 ,p_number_of_copies =>1
4045 ,p_object_version_number =>l_ovn_number
4046 ,p_effective_date =>l_effective_date
4047 ) ;
4048 RETURN;
4049 END IF;
4050 ELSIF p_table_alias='EGL' THEN
4051 IF (NOT staged_record_exists('EGL',p_information1,p_copy_entity_txn_id)) THEN
4052 ben_plan_design_elpro_module.create_eligy_criteria_result
4053 (
4054 p_copy_entity_result_id => null
4055 ,p_copy_entity_txn_id => p_copy_entity_txn_id
4056 ,p_eligy_criteria_id => p_information1
4057 ,p_business_group_id => l_business_group_id
4058 ,p_number_of_copies => 1
4059 ,p_object_version_number => l_ovn_number
4060 ,p_effective_date => l_effective_date
4061 ,p_parent_entity_result_id => null
4062 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
4063 );
4064 END IF;
4065 END IF;
4066 --
4067 if p_table_alias='AGF' THEN l_age_fctr_id :=p_information1;
4068 elsif p_table_alias='CLA' THEN l_cmbn_age_los_fctr_id :=p_information1;
4069 elsif p_table_alias='CLF' THEN l_comp_lvl_fctr_id :=p_information1;
4070 elsif p_table_alias='HWF' THEN l_hrs_wkd_in_perd_fctr_id :=p_information1;
4071 elsif p_table_alias='LSF' THEN l_los_fctr_id :=p_information1;
4072 elsif p_table_alias='PFF' THEN l_pct_fl_tm_fctr_id :=p_information1;
4073 end if;
4074 --
4075 ben_pd_rate_and_cvg_module.create_drpar_results
4076 (
4077 p_copy_entity_result_id =>null
4078 ,p_copy_entity_txn_id => p_copy_entity_txn_id
4079 ,p_comp_lvl_fctr_id => l_comp_lvl_fctr_id
4080 ,p_hrs_wkd_in_perd_fctr_id => l_hrs_wkd_in_perd_fctr_id
4081 ,p_los_fctr_id => l_los_fctr_id
4082 ,p_pct_fl_tm_fctr_id => l_pct_fl_tm_fctr_id
4083 ,p_age_fctr_id => l_age_fctr_id
4084 ,p_cmbn_age_los_fctr_id => l_cmbn_age_los_fctr_id
4085 ,p_business_group_id => l_business_group_id
4086 ,p_number_of_copies => 1
4087 ,p_object_version_number => l_ovn_number
4088 ,p_effective_date => l_effective_date
4089 ,p_no_dup_rslt => 'PDW_NO_DUP_RSLT'
4090 );
4091 END IF;
4092 CLOSE csr_rec_exists;
4093 -- mark the future data Exists column
4094 mark_future_data_exists(p_copy_entity_txn_id);
4095 hr_utility.set_location('Leaving: '||l_proc,20);
4096 END copy_drvd_factor;
4097 -- This should return the max sequence based on past present and future rows
4098 procedure max_sequence(
4099 p_copy_entity_txn_id IN Number,
4100 p_effective_date IN Date,
4101 p_table_alias IN varchar2,
4102 p_plan_id IN Number,
4103 p_max_sequence OUT NOCOPY Number) is
4104 l_proc varchar2(72) := g_package||'.max_sequence';
4105 cursor max_cpp_sequence(c_copy_entity_txn_id Number, c_effective_date Date) is
4106 select max(information263)
4107 from ben_copy_entity_results
4108 where table_alias = 'CPP'
4109 and copy_entity_txn_id = c_copy_entity_txn_id;
4110 -- and c_effective_date between information2 and information3;
4111
4112 cursor max_ctp_sequence(c_copy_entity_txn_id Number, c_effective_date Date) is
4113 select max(information268)
4114 from ben_copy_entity_results
4115 where table_alias = 'CTP'
4116 and copy_entity_txn_id = c_copy_entity_txn_id;
4117 -- and c_effective_date between information2 and information3;
4118
4119
4120 cursor max_cop_sequence(c_copy_entity_txn_id Number, c_effective_date Date, c_plan_id Number) is
4121 select max(information263)
4122 from ben_copy_entity_results
4123 where table_alias = 'COP'
4124 and copy_entity_txn_id = c_copy_entity_txn_id
4125 and information261 = c_plan_id;
4126 -- and c_effective_date between information2 and information3;
4127 begin
4128 hr_utility.set_location('Entering: '||l_proc,10);
4129 if p_table_alias = 'CPP' then
4130 open max_cpp_sequence(p_copy_entity_txn_id,p_effective_date);
4131 fetch max_cpp_sequence into p_max_sequence;
4132 close max_cpp_sequence;
4133 elsif p_table_alias = 'CTP' then
4134 open max_ctp_sequence(p_copy_entity_txn_id,p_effective_date);
4135 fetch max_ctp_sequence into p_max_sequence;
4136 close max_ctp_sequence;
4137 elsif p_table_alias = 'COP' then
4138 open max_cop_sequence(p_copy_entity_txn_id,p_effective_date,p_plan_id);
4139 fetch max_cop_sequence into p_max_sequence;
4140 close max_cop_sequence;
4141 end if;
4142 hr_utility.set_location('Entering: '||l_proc,10);
4143 end;
4144
4145 FUNCTION fetch_drvd_factor_result
4146 (
4147 p_copy_entity_txn_id IN NUMBER
4148 ,p_table_alias IN VARCHAR2
4149 ,p_information1 IN NUMBER
4150 )
4151 RETURN NUMBER IS
4152 Cursor csr_drvd_result IS
4153 Select COPY_ENTITY_RESULT_ID From BEN_COPY_ENTITY_RESULTS
4154 Where copy_entity_txn_id=p_copy_entity_txn_id
4155 And table_alias=p_table_alias
4156 And information1=p_information1
4157 And result_type_cd='DISPLAY';
4158
4159 l_copy_entity_result_id NUMBER;
4160 BEGIN
4161 copy_drvd_factor(p_copy_entity_txn_id ,p_table_alias ,p_information1);
4162 OPEN csr_drvd_result;
4163 FETCH csr_drvd_result into l_copy_entity_result_id;
4164 CLOSE csr_drvd_result;
4165 RETURN l_copy_entity_result_id;
4166 END fetch_drvd_factor_result;
4167
4168
4169 PROCEDURE populate_extra_Mapping_ENP
4170 (
4171 p_copy_entity_result_id Number,
4172 p_effective_date Date
4173 )
4174 Is
4175 --
4176 l_Strt_Dt Date ;
4177 l_End_Dt Date ;
4178 l_enp_name ben_copy_entity_results.information5%type;
4179 l_proc varchar2(72) := g_package||'.populate_extra_Mapping_ENP';
4180
4181 Cursor c_ENP is
4182 Select
4183 yrp.information309 strt_dt,
4184 yrp.information308 end_dt
4185 From
4186 Ben_copy_entity_results yrp,
4187 Ben_copy_entity_results enp
4188 Where
4189 ENP.copy_entity_result_id = p_copy_entity_result_id
4190 And yrp.copy_entity_txn_id = ENP.copy_entity_txn_id
4191 and yrp.table_alias='YRP'
4192 and ENP.table_alias='ENP'
4193 and ENP.information240 = yrp.information1 ;
4194 --
4195 Begin
4196 hr_utility.set_location('Entering: '||l_proc,10);
4197 --
4198 For l_ENP in c_ENP Loop
4199 --
4200 -- The below code is to populate information5 of ENP in a format required
4201 -- for displaying on Enrollment Requirements Cvg and Rate Hgrids
4202 Begin
4203 select
4204 meaning||' '||to_char(enp.information318,'yyyy-mm-dd')||' '||to_char(enp.information317,'yyyy-mm-dd') into l_enp_name
4205 from
4206 hr_lookups,
4207 ben_copy_entity_results enp,
4208 ben_copy_entity_results pet
4209 where
4210 lookup_type = 'BEN_ENRT_TYP_CYCL'
4211 and enp.copy_entity_result_id = p_copy_entity_result_id
4212 and pet.copy_entity_txn_id = enp.copy_entity_txn_id
4213 and pet.table_alias = 'PET'
4214 and enp.information232 = pet.information1
4215 and lookup_code = pet.information11
4216 and p_effective_date between pet.information2 and pet.information3;
4217
4218
4219 Update
4220 Ben_copy_entity_results ENP1
4221 Set
4222 ENP1.Information310 = l_ENP.strt_dt,
4223 ENP1.Information311 = l_ENP.end_dt,
4224 ENP1.Information5 = l_enp_name
4225 Where
4226 ENP1.copy_entity_result_id = p_copy_entity_result_id ;
4227
4228 Exception When No_Data_Found Then
4229 l_enp_name := null;
4230 End;
4231 --
4232 End Loop ;
4233
4234 --
4235 hr_utility.set_location('Leaving: '||l_proc,20);
4236 End populate_extra_Mapping_ENP;
4237
4238 PROCEDURE populate_extra_Mappings_ENP
4239 (
4240 p_copy_entity_txn_id Number,
4241 p_effective_date Date,
4242 p_pgm_id Number
4243 )
4244 Is
4245 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_ENP';
4246 Cursor C_ENP is
4247 Select
4248 ENP.copy_entity_result_id
4249 From
4250 Ben_copy_entity_results ENP,
4251 Ben_copy_entity_results pet
4252 Where
4253 ENP.copy_entity_txn_id = p_copy_entity_txn_id
4254 And ENP.copy_entity_txn_id = pet.copy_entity_txn_id
4255 And p_effective_date between pet.information2 and pet.information3
4256 and pet.table_alias='PET'
4257 and ENP.table_alias='ENP'
4258 and pet.information11 in ('O','A')
4259 and ENP.information232 = pet.information1
4260 and pet.information260= p_pgm_id;
4261
4262 Begin
4263 hr_utility.set_location('Entering: '||l_proc,10);
4264 For l_ENP in c_ENP Loop
4265
4266 populate_extra_mapping_ENP(l_ENP.copy_entity_result_id,p_effective_date);
4267
4268 End Loop ;
4269 hr_utility.set_location('Leaving: '||l_proc,20);
4270 End populate_extra_Mappings_ENP;
4271
4272
4273 PROCEDURE populate_extra_Mappings_CTP
4274 (
4275 p_copy_entity_txn_id Number,
4276 p_effective_date Date,
4277 p_pgm_id Number
4278 )
4279 Is
4280 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_CTP';
4281 -- Select All CTP records which have a default enrollment logic defined
4282 --
4283 Cursor C_CTP is
4284 Select
4285 ctp.copy_entity_result_id,
4286 ctp.information45,
4287 ctp.information248,
4288 ctp.information2 effective_date -- Add effective Date for populating mappings
4289 From
4290 Ben_copy_entity_results ctp
4291 Where
4292 ctp.copy_entity_txn_id = p_copy_entity_txn_id
4293 And ctp.copy_entity_txn_id = ctp.copy_entity_txn_id
4294 --And p_effective_date between ctp.information2 and ctp.information3
4295 And ctp.table_alias='CTP'
4296 And ctp.information260= p_pgm_id
4297 And ctp.information45 is not null
4298 And ctp.information106 is null -- not populated already
4299 And ctp.dml_operation <>'DELETE'
4300 For Update of ctp.Information106,ctp.information107;
4301
4302 -- Select All CTP records which have a Enrollment Code defined
4303 Cursor C_Enrt_CTP is
4304 select
4305 information44 ENRT_CD,
4306 information101 NEW_ENRT_CD,
4307 information102 CUR_ENRT_CD
4308 from
4309 ben_copy_entity_results
4310 where
4311 copy_entity_txn_id = p_copy_entity_txn_id
4312 and table_alias = 'CTP'
4313 and information44 is not null
4314 -- and p_effective_date between information2 and information3
4315 and dml_operation <> 'DELETE'
4316 for update of information101, information102;
4317
4318 l_new_dflt_enrt_cd varchar2(15);
4319 l_old_dflt_enrt_cd varchar2(15);
4320 l_new_enrt_cd ben_copy_entity_results.information101%type;
4321 l_cur_enrt_cd ben_copy_entity_results.information102%type;
4322 l_default_object_id Number;
4323 --
4324 Begin
4325 hr_utility.set_location('Entering: '||l_proc,10);
4326 --
4327 For l_CTP in c_CTP Loop
4328 --
4329 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_CTP.information45);
4330 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_CTP.information45);
4331
4332 --
4333 -- Find any plans under this ptp which has default flag set
4334 Begin
4335 --
4336 Select
4337 cpp.copy_entity_result_id into l_default_object_id
4338 From
4339 ben_copy_entity_results cpp
4340 ,ben_copy_entity_results pln
4341 Where
4342 cpp.copy_entity_txn_id = p_copy_entity_txn_id
4343 -- Take Effective Date from the cursor above
4344 And l_CTP.effective_date between cpp.information2 and cpp.information3
4345 And cpp.information13 ='Y'
4346 And cpp.information260= p_pgm_id
4347 And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
4348 And l_CTP.effective_date between pln.information2 and pln.information3
4349 And pln.information248 = l_CTP.information248
4350 And pln.information1 = cpp.information261
4351 And cpp.table_alias='CPP'
4352 And pln.table_alias='PLN'
4353 And cpp.dml_operation <>'DELETE'
4354 And pln.dml_operation <>'DELETE'
4355 and rownum =1 ;
4356
4357 --
4358 Exception When No_Data_Found Then
4359 l_default_object_id := null;
4360 End ;
4361
4362 if l_default_Object_id is null then
4363 --
4364 Begin
4365 Select
4366 cop.copy_entity_result_id into l_default_object_id
4367 From
4368 ben_copy_entity_results pln
4369 ,ben_copy_entity_results cop
4370 Where
4371 pln.copy_entity_txn_id = p_copy_entity_txn_id
4372 And l_CTP.effective_date between pln.information2 and pln.information3
4373 And cop.copy_entity_txn_id=pln.copy_entity_txn_id
4374 And l_CTP.effective_date between cop.information2 and cop.information3
4375 And pln.information1 = cop.information261
4376 And pln.information248= l_CTP.information248
4377 And cop.information18 ='Y'
4378 And pln.table_alias='PLN'
4379 And cop.table_alias='COP'
4380 And pln.dml_operation <>'DELETE'
4381 And cop.dml_operation <>'DELETE'
4382 and rownum =1 ;
4383 Exception When No_Data_Found Then
4384 l_default_object_id := null;
4385 End ;
4386
4387 --
4388 End If;
4389
4390
4391 Update
4392 Ben_copy_entity_results ctp1
4393 Set
4394 ctp1.information106 =l_new_dflt_enrt_cd ,
4395 ctp1.information107 =l_old_dflt_enrt_cd ,
4396 ctp1.information160 = l_default_object_Id
4397 Where current of c_CTP;
4398 --
4399 End Loop ;
4400 --
4401 -- Now update the New and Cur Enrt Codes from Enrt_CD
4402 For L_Enrt_Ctp in C_Enrt_Ctp
4403 loop
4404 l_new_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_new_enrt_cd(L_Enrt_Ctp.ENRT_CD);
4405 l_cur_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_cur_enrt_cd(L_Enrt_Ctp.ENRT_CD);
4406
4407 update
4408 ben_copy_entity_results
4409 set
4410 information101 = l_new_enrt_cd,
4411 information102 = l_cur_enrt_cd
4412 where current of C_Enrt_Ctp;
4413 End Loop;
4414 hr_utility.set_location('Leaving: '||l_proc,20);
4415 End populate_extra_Mappings_CTP;
4416
4417 PROCEDURE populate_extra_Mappings_LCT
4418 (
4419 p_copy_entity_txn_id Number,
4420 p_effective_date Date,
4421 p_pgm_id Number
4422 )
4423 Is
4424 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_LCT';
4425 -- Select All LCT records which have a default enrollment logic defined
4426 --
4427 -- Information12-> dflt logic , Information11 -dflt flag
4428 Cursor C_LCT is
4429 Select
4430 ctp.copy_entity_result_id,
4431 ctp.information12,
4432 ctp.information248,
4433 ctp.information259,
4434 ctp.information257 ler_id,
4435 ctp.information2 effective_date
4436 From
4437 Ben_copy_entity_results ctp
4438 Where
4439
4440 ctp.copy_entity_txn_id = p_copy_entity_txn_id
4441 And ctp.copy_entity_txn_id = ctp.copy_entity_txn_id
4442 --And p_effective_date between ctp.information2 and ctp.information3
4443 And ctp.table_alias='LCT'
4444 --And ctp.information260= p_pgm_id
4445 And ctp.information12 is not null
4446 And ctp.information103 is null
4447 And ctp.dml_operation <>'DELETE'
4448 For Update of ctp.Information103,ctp.information104;
4449
4450 -- Pick up all the LCT's for which Enrollment Code has been defined
4451 cursor c_enrt_lct
4452 is
4453 select
4454 information14 ENRT_CD,
4455 information101 NEW_ENRT_CD,
4456 information102 CUR_ENRT_CD
4457 from
4458 ben_copy_entity_results
4459 where
4460 copy_entity_txn_id = p_copy_entity_txn_id
4461 and table_alias = 'LCT'
4462 and information14 is not null
4463 -- and p_effective_date between information2 and information3
4464 and dml_operation <> 'DELETE'
4465 for update of information101, information102;
4466
4467 l_new_enrt_cd ben_copy_entity_results.information101%type;
4468 l_cur_enrt_cd ben_copy_entity_results.information102%type;
4469 ptipCopyEntityResultId ben_copy_entity_results.copy_entity_result_id%type;
4470 l_ptp_id ben_pl_typ_f.pl_typ_id%type;
4471 l_new_dflt_enrt_cd varchar2(15);
4472 l_old_dflt_enrt_cd varchar2(15);
4473 l_default_object_id Number;
4474 --
4475 Begin
4476 hr_utility.set_location('Entering: '||l_proc,10);
4477 --
4478
4479 For l_LCT in c_LCT Loop
4480 --
4481 -- Get ptipCopyEntityResultId
4482
4483 Select
4484 copy_entity_result_id,information248 into ptipCopyEntityResultId,l_ptp_id
4485 From
4486 ben_copy_entity_results
4487 where
4488 copy_entity_txn_id = p_copy_entity_txn_id
4489 and l_LCT.effective_date between information2 and information3
4490 and table_alias='CTP'
4491 and information1 = l_LCT.information259;
4492
4493 --dbms_output.put_line('ptp id is '||l_ptp_id);
4494 --
4495 -- Find any plans under this lct which has default flag set
4496
4497 Begin
4498 --
4499 Select
4500 cpp.copy_entity_result_id into l_default_object_id
4501 From
4502 ben_copy_entity_results lpr1
4503 ,ben_copy_entity_results cpp
4504 ,ben_copy_entity_results pln
4505 ,ben_copy_entity_results ctp
4506 Where
4507 lpr1.copy_entity_txn_id = p_copy_entity_txn_id
4508 And ctp.copy_entity_txn_id = lpr1.copy_entity_txn_id
4509 And cpp.copy_entity_txn_id = lpr1.copy_entity_txn_id
4510 And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
4511
4512 And l_LCT.effective_date between pln.information2 and pln.information3
4513 And l_LCT.effective_date between ctp.information2 and ctp.information3
4514 And l_LCT.effective_date between lpr1.information2 and lpr1.information3
4515 And l_LCT.effective_date between cpp.information2 and cpp.information3
4516
4517 And ctp.information248 = l_ptp_id
4518 And lpr1.information256 = cpp.information1
4519 And lpr1.information13 ='Y'
4520 And lpr1.information257 = l_LCT.ler_id
4521 --And cpp.information260 = p_pgm_id
4522 And pln.information248 = ctp.information248
4523 And pln.information1 = cpp.information261
4524
4525 And lpr1.table_alias = 'LPR1'
4526 And ctp.table_alias = 'CTP'
4527 And cpp.table_alias = 'CPP'
4528 And pln.table_alias = 'PLN'
4529
4530 And lpr1.dml_operation <> 'DELETE'
4531 And ctp.dml_operation <> 'DELETE'
4532 And cpp.dml_operation <> 'DELETE'
4533 And pln.dml_operation <> 'DELETE'
4534
4535 and rownum = 1 ;
4536 --
4537 Exception When No_Data_Found Then
4538 l_default_object_id := null;
4539 End ;
4540
4541 -- Bad Luck No Plan has default flag set
4542 -- We need to check for oipl's which have default flag under this lct
4543 if l_default_Object_id is null then
4544 Begin
4545 --
4546 Select
4547 cop.copy_entity_result_id into l_default_object_id
4548 From
4549 ben_copy_entity_results lop
4550 ,ben_copy_entity_results cop
4551 ,ben_copy_entity_results cpp
4552 ,ben_copy_entity_results pln
4553 ,ben_copy_entity_results ctp
4554 Where
4555 lop.copy_entity_txn_id = p_copy_entity_txn_id
4556 And ctp.copy_entity_txn_id = lop.copy_entity_txn_id
4557 And cpp.copy_entity_txn_id = lop.copy_entity_txn_id
4558 And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
4559 And cop.copy_entity_txn_id = pln.copy_entity_txn_id
4560
4561 And l_LCT.effective_date between pln.information2 and pln.information3
4562 And l_LCT.effective_date between ctp.information2 and ctp.information3
4563 And l_LCT.effective_date between lop.information2 and lop.information3
4564 And l_LCT.effective_date between cop.information2 and cop.information3
4565 And l_LCT.effective_date between cpp.information2 and cpp.information3
4566
4567 And ctp.information248 = l_ptp_id
4568 And lop.information258 = cop.information1
4569 And lop.information12 ='Y'
4570 And lop.information257 = l_LCT.ler_id
4571 --And cpp.information260 = p_pgm_id
4572 And pln.information248 = ctp.information248
4573 And pln.information1 = cpp.information261
4574
4575 And cpp.information261 = cop.information261
4576
4577
4578 And lop.table_alias = 'LOP'
4579 And ctp.table_alias = 'CTP'
4580 And cpp.table_alias = 'CPP'
4581 And pln.table_alias = 'PLN'
4582 And cop.table_alias = 'COP'
4583
4584 And lop.dml_operation <> 'DELETE'
4585 And ctp.dml_operation <> 'DELETE'
4586 And cpp.dml_operation <> 'DELETE'
4587 And pln.dml_operation <> 'DELETE'
4588 And cop.dml_operation <> 'DELETE'
4589
4590 and rownum = 1 ;
4591
4592 --
4593 Exception When No_Data_Found Then
4594 l_default_object_id := null;
4595 End ;
4596 End If;
4597
4598 --
4599 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_LCT.information12);
4600 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_LCT.information12);
4601
4602 --
4603
4604 -- populate the extra mappings namely new and old dflt enrt codes
4605 -- also populate the composite id to that of the row copy entity result id
4606 -- so copied lct records will not show any groupings but wil be simply one to one
4607 -- grouping will hamper performance
4608 Update
4609 Ben_copy_entity_results ctp1
4610 Set
4611 ctp1.information103 =l_new_dflt_enrt_cd,
4612 ctp1.information104 =l_old_dflt_enrt_cd,
4613 ctp1.information160 = l_default_object_id,
4614 ctp1.information161 = ctp1.copy_entity_result_id,
4615 ctp1.information162 = ptipCopyEntityResultId
4616 Where current of c_LCT;
4617 --
4618 End Loop ;
4619 --
4620 for l_lct in c_enrt_lct
4621 loop
4622 l_new_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_new_enrt_cd(l_lct.ENRT_CD);
4623 l_cur_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_cur_enrt_cd(l_lct.ENRT_CD);
4624 update
4625 ben_copy_entity_results
4626 set
4627 information101 = l_new_enrt_cd,
4628 information102 = l_cur_enrt_cd
4629 where current of c_enrt_lct;
4630 end loop;
4631 hr_utility.set_location('Leaving: '||l_proc,20);
4632 End populate_extra_Mappings_LCT;
4633
4634 PROCEDURE populate_extra_Mappings_CPP
4635 (
4636 p_copy_entity_txn_id Number,
4637 p_effective_date Date,
4638 p_pgm_id Number
4639 )
4640 Is
4641 l_proc varchar2(72) := g_package||'.populate_extra_Mappings_CPP';
4642 -- Select All CPP records which have a default enrollment logic defined
4643 --
4644 Cursor C_CPP is
4645 Select
4646 CPP.copy_entity_result_id,
4647 CPP.information21,
4648 CPP.information13,
4649 CPP.information261,
4650 CPP.information2 effective_date
4651 From
4652 Ben_copy_entity_results CPP
4653 Where
4654 CPP.copy_entity_txn_id = p_copy_entity_txn_id
4655 And CPP.copy_entity_txn_id = CPP.copy_entity_txn_id
4656 --And p_effective_date between CPP.information2 and CPP.information3
4657 And CPP.table_alias='CPP'
4658 And CPP.information260= p_pgm_id
4659 And CPP.information21 is not null
4660 And CPP.information106 is null -- not populated already
4661 And CPP.dml_operation <>'DELETE'
4662 For Update of CPP.Information106,CPP.information107;
4663
4664 -- Select All CPP records which have enrollment code defined
4665 Cursor C_Enrt_CPP is
4666 select
4667 information22 ENRT_CD,
4668 information101 NEW_ENRT_CD,
4669 information102 CUR_ENRT_CD
4670 from
4671 ben_copy_entity_results
4672 where
4673 copy_entity_Txn_id = p_copy_entity_txn_id
4674 and table_alias = 'CPP'
4675 -- and p_effective_date between information2 and information3
4676 and dml_operation <> 'DELETE'
4677 for update of information101, information102;
4678
4679 l_new_enrt_cd ben_copy_entity_results.information101%type;
4680 l_cur_enrt_cd ben_copy_entity_results.information102%type;
4681 l_new_dflt_enrt_cd varchar2(15);
4682 l_old_dflt_enrt_cd varchar2(15);
4683 l_default_object_id Number;
4684 --
4685 Begin
4686 hr_utility.set_location('Entering: '||l_proc,10);
4687 --
4688 For l_CPP in c_CPP Loop
4689 --
4690 l_new_dflt_enrt_cd := get_Dflt_New_Enrt_cd(l_CPP.information21);
4691 l_old_dflt_enrt_cd := get_Dflt_Old_Enrt_cd(l_CPP.information21);
4692
4693 If l_CPP.information13 ='Y' then
4694 --
4695 l_default_object_id := l_CPP.copy_entity_result_id;
4696 --
4697 Else
4698 --
4699 Begin
4700 --
4701 Select
4702 cop.copy_entity_result_id into l_default_object_id
4703 From
4704 Ben_copy_entity_results cop
4705 Where
4706 cop.copy_entity_txn_id = p_copy_entity_txn_id
4707 And l_CPP.effective_date between cop.information2 and cop.information3
4708 And cop.table_alias = 'COP'
4709 And cop.information261 = l_CPP.information261
4710 And cop.information18 ='Y'
4711 and rownum =1;
4712 --
4713 Exception When No_Data_Found Then
4714 --
4715 l_default_object_id := null ;
4716 --
4717 End ;
4718 --
4719 End If ;
4720
4721 -- Update the New and Old Default enrollment Logic by parsing the combined dflt enrt logic
4722 Update
4723 Ben_copy_entity_results CPP1
4724 Set
4725 CPP1.information106 =l_new_dflt_enrt_cd,
4726 CPP1.information107 =l_old_dflt_enrt_cd,
4727 CPP1.information160 = l_default_object_id
4728 Where current of c_CPP;
4729 --
4730 End Loop ;
4731 --
4732
4733 -- Now update the New and Cur Enrt Codes from Enrt_CD
4734 for l_cpp in C_Enrt_CPP loop
4735 l_new_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_new_enrt_cd(L_Cpp.ENRT_CD);
4736 l_cur_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_cur_enrt_cd(L_Cpp.ENRT_CD);
4737
4738 update
4739 ben_copy_entity_results
4740 set
4741 information101 = l_new_enrt_cd,
4742 information102 = l_cur_enrt_cd
4743 where current of C_Enrt_Cpp;
4744 End Loop;
4745
4746
4747 hr_utility.set_location('Leaving: '||l_proc,20);
4748 End populate_extra_Mappings_CPP;
4749
4750 PROCEDURE populate_extra_Mapping_PGM
4751 (
4752 p_copy_entity_txn_id Number,
4753 p_effective_date Date
4754 )
4755 Is
4756 l_proc varchar2(72) := g_package||'.populate_extra_Mapping_PGM';
4757 --
4758 cursor c_PGM is
4759 Select pgm.*
4760 from
4761 Ben_copy_entity_results pgm
4762 Where
4763 pgm.copy_entity_txn_id = p_copy_entity_txn_id
4764 And pgm.table_alias='PGM'
4765 for update of pgm.information101,pgm.information102;
4766
4767 l_new_enrt_cd varchar2(15);
4768 l_cur_enrt_cd varchar2(15);
4769 --
4770 Begin
4771 hr_utility.set_location('Entering: '||l_proc,10);
4772 --
4773 For l_PGM in c_PGM Loop
4774 --
4775 l_new_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_new_enrt_cd(l_PGM.information51) ;
4776 l_cur_enrt_cd := BEN_PDW_COPY_BEN_TO_STG.get_cur_enrt_cd(l_PGM.information51) ;
4777
4778 Update
4779 Ben_copy_entity_results pgm1
4780 Set
4781 information101= l_new_enrt_cd,
4782 information102= l_cur_enrt_cd
4783 Where current of c_PGM ;
4784 --
4785 End Loop ;
4786 --
4787 hr_utility.set_location('Leaving: '||l_proc,20);
4788 End populate_extra_Mapping_PGM;
4789
4790
4791 PROCEDURE create_program_result
4792 (
4793 p_copy_entity_result_id NUMBER
4794 ,p_copy_entity_txn_id NUMBER
4795 ,p_pgm_id NUMBER
4796 ,p_business_group_id NUMBER
4797 ,p_number_of_copies NUMBER
4798 ,p_object_version_number NUMBER
4799 ,p_effective_date DATE
4800 ,p_no_dup_rslt VARCHAR2
4801 ) IS
4802 l_proc varchar2(72) := g_package||'.create_program_result';
4803 --
4804 Cursor C_CPP is
4805 Select
4806 pln.copy_entity_result_id
4807 From
4808 Ben_copy_entity_results cpp,
4809 Ben_copy_entity_results pln
4810 Where
4811 cpp.copy_entity_txn_id = p_copy_entity_txn_id
4812 And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
4813 And p_effective_date between cpp.information2 and cpp.information3
4814 And p_effective_date between pln.information2 and pln.information3
4815 And cpp.table_alias ='CPP'
4816 And pln.table_alias ='PLN'
4817 And cpp.information260 = p_pgm_id
4818 And pln.status<>'DELETE'
4819 And cpp.status<>'DELETE'
4820 And cpp.information261 = pln.information1 ;
4821 /*
4822 Cursor C_CPP is
4823 Select
4824 pln.copy_entity_result_id
4825 From
4826 Ben_copy_entity_results cpp,
4827 Ben_copy_entity_results pln
4828 Where
4829 cpp.copy_entity_txn_id = 229
4830 And pln.copy_entity_txn_id = cpp.copy_entity_txn_id
4831 And sysdate between cpp.information2 and cpp.information3
4832 And sysdate between pln.information2 and pln.information3
4833 And cpp.table_alias ='CPP'
4834 And pln.table_alias ='PLN'
4835 And cpp.information260 = 310
4836 And pln.status<>'DELETE'
4837 And cpp.status<>'DELETE'
4838 And cpp.information261 = pln.information1 ; */
4839 --
4840 l_copy_entity_result_id Number ;
4841 l_object_version_number Number ;
4842 --
4843
4844 BEGIN
4845 hr_utility.set_location('Entering: '||l_proc,10);
4846 --
4847 --dbms_output.put_line('CALLING PGM COPY1');
4848 -- Call PCP API
4849 ben_plan_design_program_module.create_program_result (
4850 p_copy_entity_result_id =>l_copy_entity_result_id
4851 ,p_copy_entity_txn_id =>p_copy_entity_txn_id
4852 ,p_pgm_id =>p_pgm_id
4853 ,p_business_group_id =>p_business_group_id
4854 ,p_number_of_copies =>p_number_of_copies
4855 ,p_object_version_number =>l_object_version_number
4856 ,p_effective_date =>p_effective_date
4857 ,p_no_dup_rslt =>p_no_dup_rslt
4858 );
4859
4860 -- This is to copy all the PZips and BnftsGrps in BG to staging
4861 copy_PostalZip_Bnft_Grp(p_copy_entity_txn_id);
4862
4863 --
4864 -- Add Call to Extra Mappings here
4865
4866 -- extra mappings for PGM
4867 populate_extra_Mapping_PGM(p_copy_entity_txn_id,p_effective_date);
4868
4869 populate_extra_mappings_CPY
4870 (
4871 p_copy_entity_txn_id => p_copy_entity_txn_id
4872 ,p_business_group_id => p_business_group_id
4873 ,p_effective_date => p_effective_date
4874 );
4875
4876 --dbms_output.put_line('AFTER POPULATE OPGM');
4877
4878 -- Call Extra Mappings For PLN
4879 For l_CPP in C_CPP Loop
4880 --
4881 populate_extra_Mapping_PLN
4882 (
4883 p_effective_date => p_effective_date,
4884 p_business_group_id => p_business_group_id,
4885 p_copy_entity_txn_id => p_copy_entity_txn_id,
4886 p_copy_entity_result_id => l_CPP.copy_entity_result_id
4887 );
4888 --
4889 End Loop ;
4890
4891 -- Call Extyra Mappings for CTP
4892
4893 populate_extra_Mappings_CTP
4894 (
4895 p_copy_entity_txn_id => p_copy_entity_txn_id,
4896 p_effective_date => p_effective_date,
4897 p_pgm_id => p_pgm_id
4898 );
4899 -- Call Extyra Mappings for CPP
4900
4901 populate_extra_Mappings_CPP
4902 (
4903 p_copy_entity_txn_id => p_copy_entity_txn_id,
4904 p_effective_date => p_effective_date,
4905 p_pgm_id => p_pgm_id
4906 );
4907 -- Call Extyra Mappings for COP
4908 populate_extra_Mappings_COP
4909 (
4910 p_copy_entity_txn_id => p_copy_entity_txn_id,
4911 p_effective_date => p_effective_date,
4912 p_pgm_id => p_pgm_id
4913 );
4914
4915 -- Call Extyra Mappings for CTP
4916 populate_extra_Mappings_LCT
4917 (
4918 p_copy_entity_txn_id => p_copy_entity_txn_id,
4919 p_effective_date => p_effective_date,
4920 p_pgm_id => p_pgm_id
4921 );
4922 -- Call Extyra Mappings for CPP
4923 populate_extra_Mappings_LPR
4924 (
4925 p_copy_entity_txn_id => p_copy_entity_txn_id,
4926 p_effective_date => p_effective_date,
4927 p_pgm_id => p_pgm_id
4928 );
4929 -- Call Extyra Mappings for COP
4930 populate_extra_Mappings_LOP
4931 (
4932 p_copy_entity_txn_id => p_copy_entity_txn_id,
4933 p_effective_date => p_effective_date,
4934 p_pgm_id => p_pgm_id
4935 );
4936
4937
4938 -- Call Extra Mappings For EAP
4939
4940 populate_extra_mappings_EPA
4941 (
4942 p_copy_entity_txn_id => p_copy_entity_txn_id,
4943 p_effective_date => p_effective_date
4944 );
4945 -- Call Extra Mappings for VPF
4946 populate_extra_mappings_VPF
4947 (
4948 p_copy_entity_txn_id => p_copy_entity_txn_id,
4949 p_effective_date => p_effective_date
4950 );
4951
4952 -- populate the extra mappings required for Criteria
4953 populate_extra_mappings_elp(
4954 p_copy_entity_txn_id => p_copy_entity_txn_id
4955 ,p_effective_date => p_effective_date
4956 );
4957
4958
4959 -- Call Extra Mappings for LEN
4960 populate_extra_Mappings_LEN
4961 (
4962 p_copy_entity_txn_id => p_copy_entity_txn_id,
4963 p_effective_date => p_effective_date,
4964 p_pgm_id => p_pgm_id
4965 );
4966
4967 populate_extra_Mappings_ENP
4968 (
4969 p_copy_entity_txn_id => p_copy_entity_txn_id,
4970 p_effective_date => p_effective_date,
4971 p_pgm_id => p_pgm_id
4972 );
4973
4974
4975 -- Dump All elpros in Staging . This will get shown in Criteria Set Hgrid in Cvg, Rates, Imputed Income
4976 -- we are now dumping elpros only in Criteria Set page where it is required
4977 -- dumping them here makes the program page very ineffecient
4978 -- enabling it again after enabling the concurrent process
4979
4980 dump_elig_prfls(p_copy_entity_txn_id);
4981
4982 -- mark the future data Exists column
4983 mark_future_data_exists(p_copy_entity_txn_id);
4984
4985 hr_utility.set_location('Leaving: '||l_proc,20);
4986 --
4987 Exception When Others then
4988 raise ;
4989 --
4990 --
4991 END create_program_result;
4992
4993 PROCEDURE mark_future_data_exists(p_copy_entity_txn_id in NUMBER)
4994 AS
4995
4996 l_context pqh_copy_entity_txns.context%type;
4997 -- PRAGMA AUTONOMOUS_TRANSACTION;
4998 BEGIN
4999
5000 select context into l_context
5001 from pqh_copy_entity_txns
5002 where copy_entity_txn_id = p_copy_entity_txn_id ;
5003
5004 -- If it is "GSP" context (Eligibility integration with GSP)
5005 -- donot execute Mark Future Data exists code
5006 -- Deleting duplicate rows and converting result_type_cd
5007 -- from NO DISPLAY to DISPLAY is undesirable for GSP
5008 -- For now, GSP will mark the column FUTURE_DATA_EXISTS with Y
5009 if ( l_context <> 'GSP')
5010 then
5011 -- first delete the duplicate rows
5012 delete from ben_copy_entity_results
5013 where rowid in ( select min(rowid)
5014 from ben_copy_entity_results
5015 where copy_entity_txn_id = p_copy_entity_txn_id
5016 and information1 is not null
5017 group by table_alias,information1, information2, information3
5018 having count( table_alias) > 1 );
5019
5020 -- update the selected one to Y
5021
5022 update ben_copy_entity_results a
5023 set future_data_exists ='Y'
5024 where a.copy_entity_txn_id = p_copy_entity_txn_id
5025 and a.future_data_exists is null
5026 and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
5027 and exists
5028 ( select 'Y' from ben_copy_entity_results b
5029 where b.copy_entity_txn_id = a.copy_entity_txn_id
5030 and b.table_alias = a.table_alias
5031 and b.information1 = a.information1
5032 and b.information2 = a.information3+1);
5033 -- update all others to N
5034
5035 update ben_copy_entity_results
5036 set future_data_exists = nvl(future_Data_exists,'N'),
5037 result_type_cd = 'DISPLAY'
5038 where copy_entity_txn_id = p_copy_entity_txn_id;
5039 end if;
5040
5041 -- COMMIT;
5042 EXCEPTION
5043 WHEN OTHERS THEN
5044 RAISE;
5045 END mark_future_data_exists;
5046 --
5047 --- CALL TO COPY OF THE FORMULAS
5048 --
5049 PROCEDURE Create_Formula_FF_Result
5050 (
5051 p_validate IN Number
5052 ,p_copy_entity_result_id IN Number
5053 ,p_copy_entity_txn_id IN Number
5054 ,p_formula_id IN Number
5055 ,p_business_group_id IN Number
5056 ,p_number_of_copies IN Number
5057 ,p_object_version_number OUT nocopy Number
5058 ,p_effective_date IN Date)Is
5059 l_proc varchar2(72) := g_package||'.Create_Formula_FF_Result';
5060 begin
5061 hr_utility.set_location('Entering: '||l_proc,10);
5062 --
5063 --Call plan copy api to copy Formula
5064 --
5065 ben_plan_design_program_module.create_formula_result
5066 (
5067 p_validate => p_validate
5068 ,p_copy_entity_result_id => p_copy_entity_result_id
5069 ,p_copy_entity_txn_id => p_copy_entity_txn_id
5070 ,p_formula_id => p_formula_id
5071 ,p_business_group_id => p_business_group_id
5072 ,p_number_of_copies => p_number_of_copies
5073 ,p_object_version_number => p_object_version_number
5074 ,p_effective_date => p_effective_date
5075 ,p_copy_to_clob => 'y'
5076 );
5077
5078 mark_future_data_exists(p_copy_entity_txn_id);
5079 -- Commit after copying the Fast Formula so that rows donot remain locked
5080 -- in the transaction
5081 commit;
5082 hr_utility.set_location('Leaving: '||l_proc,20);
5083 End Create_Formula_FF_Result;
5084
5085 FUNCTION get_rule_name(p_copy_entity_txn_id IN Number
5086 ,p_id IN Number
5087 ,p_table_alias IN VARCHAR2)
5088 RETURN VARCHAR2 IS
5089 l_business_group_id Number;
5090 l_effective_date Date;
5091 l_rule_name ben_copy_entity_results.information170%type;
5092 BEGIN
5093 get_txn_details (p_copy_entity_txn_id ,l_business_group_id,l_effective_date);
5094 if (p_id is not null) THEN
5095 begin
5096 Select fff.information112 into l_rule_name
5097 from ben_copy_entity_results fff,
5098 ben_copy_entity_results ben
5099 where fff.table_alias='FFF'
5100 and fff.copy_entity_txn_id=p_copy_entity_txn_id
5101 and ben.copy_entity_txn_id=p_copy_entity_txn_id
5102 and fff.information1=decode(p_table_alias,'CTP',ben.INFORMATION277,'CPP',ben.INFORMATION264,'COP',ben.INFORMATION266,'PLN',ben.information272,'LPR1',ben.INFORMATION263,'LOP',ben.INFORMATION264,'LCT',ben.INFORMATION13,'CCM',ben.INFORMATION266)
5103 and ben.information1=p_id
5104 and l_effective_date between ben.information2 and ben.information3
5105 and l_effective_date between fff.information2 and fff.information3;
5106 Exception when No_Data_Found Then
5107 RAISE;
5108 end;
5109 end if;
5110 return l_rule_name;
5111 END get_rule_name;
5112
5113 procedure update_task_list_row(p_copy_entity_txn_id Number,p_effective_date Date)
5114 is
5115 cursor c_pgm is select information1 pgm_id, information170 name, information36 Alws_Unrstrctd_Enrt_Flag,information50 pgm_uom
5116 from ben_copy_entity_results
5117 where copy_entity_txn_id = p_copy_entity_txn_id
5118 and table_alias = 'PGM'
5119 and p_effective_date between information2 and information3;
5120
5121 l_pgmrow c_pgm%rowtype;
5122
5123 begin
5124 -- update a tasklist row.
5125 open c_pgm;
5126 fetch c_pgm into l_pgmrow;
5127 close c_pgm;
5128 if(l_pgmrow.pgm_id is not null) then
5129 update ben_copy_entity_results
5130 set INFORMATION260 = l_pgmrow.pgm_id /*SAVED_TASK_PGMID*/
5131 ,INFORMATION185 = l_pgmrow.name /* SAVED_PROGRAM_NAME*/
5132 ,INFORMATION14 = l_pgmrow.Alws_Unrstrctd_Enrt_Flag /* PGM_ALWS_UNRSTRCTD*/
5133 ,INFORMATION15 = l_pgmrow.pgm_uom /*PGM_UOM */
5134 ,INFORMATION100 = 'Y' -- PROGRAM_TASK,
5135 ,INFORMATION101 = 'Y' -- PLAN_AND_OPTIONS_TASK,
5136 ,INFORMATION102 = 'Y' -- SCHEDULING_TASK,
5137 ,INFORMATION103 = 'Y' -- ENROLLMENT_REQUIREMENTS_TASK,
5138 ,INFORMATION104 = 'Y' -- ELIGIBILITY_PROFILE_TASK,
5139 ,INFORMATION105 = 'Y' -- DEFAULT_ENROLLMENT_TASK,
5140 ,INFORMATION106 = 'Y' -- REVIEW_AND_SUBMIT_TASK
5141 where copy_entity_txn_id = p_copy_entity_txn_id
5142 and table_alias = 'BEN_PDW_TASK_LIST' ;
5143 end if;
5144
5145 exception
5146 when others then
5147 rollback;
5148 raise;
5149 end update_task_list_row;
5150
5151
5152 PROCEDURE create_program_result
5153 ( p_copy_entity_result_id NUMBER
5154 ,p_copy_entity_txn_id NUMBER
5155 ,p_pgm_id NUMBER
5156 ,p_business_group_id NUMBER
5157 ,p_number_of_copies NUMBER
5158 ,p_object_version_number NUMBER
5159 ,p_effective_date DATE
5160 ,p_no_dup_rslt VARCHAR2
5161 ,p_copy_mode IN VARCHAR2
5162 ,p_request_id OUT NOCOPY NUMBER
5163 ) IS
5164 l_proc varchar2(72) := g_package||'.create_program_result';
5165 begin
5166
5167 if p_copy_mode = 'ONLINE' then
5168 create_program_result
5169 (p_copy_entity_result_id => p_copy_entity_result_id
5170 ,p_copy_entity_txn_id => p_copy_entity_txn_id
5171 ,p_pgm_id => p_pgm_id
5172 ,p_business_group_id => p_business_group_id
5173 ,p_number_of_copies => p_number_of_copies
5174 ,p_object_version_number => p_object_version_number
5175 ,p_effective_date => p_effective_date
5176 ,p_no_dup_rslt => p_no_dup_rslt
5177 );
5178 elsif p_copy_mode = 'CONCUR' then
5179 -- call the concurrent process
5180 p_request_id := fnd_request.submit_request
5181 (application => 'BEN'
5182 ,program => 'BEPDWSTG'
5183 ,description => NULL
5184 ,sub_request => FALSE
5185 ,argument1 => p_copy_entity_result_id
5186 ,argument2 => p_copy_entity_txn_id
5187 ,argument3 => p_pgm_id
5188 ,argument4 => p_business_group_id
5189 ,argument5 => p_number_of_copies
5190 ,argument6 => p_object_version_number
5191 ,argument7 => fnd_date.date_to_canonical(p_effective_date)
5192 ,argument8 => p_no_dup_rslt);
5193
5194 update pqh_copy_entity_txns
5195 set status = 'COPYING_IN_PROGRESS'
5196 ,start_with = null
5197 where copy_entity_txn_id = p_copy_entity_txn_id;
5198 end if;
5199 exception
5200 when others then
5201 rollback;
5202 raise;
5203 end create_program_result;
5204
5205 procedure process (
5206 errbuf OUT NOCOPY VARCHAR2
5207 ,retcode OUT NOCOPY NUMBER
5208 ,p_copy_entity_result_id IN NUMBER DEFAULT NULL
5209 ,p_copy_entity_txn_id IN NUMBER
5210 ,p_pgm_id IN NUMBER
5211 ,p_business_group_id IN NUMBER
5212 ,p_number_of_copies IN NUMBER
5213 ,p_object_version_number IN NUMBER DEFAULT NULL
5214 ,p_effective_date IN VARCHAR2
5215 ,p_no_dup_rslt IN VARCHAR2
5216 ) is
5217
5218 begin
5219 create_program_result
5220 (p_copy_entity_result_id => p_copy_entity_result_id
5221 ,p_copy_entity_txn_id => p_copy_entity_txn_id
5222 ,p_pgm_id => p_pgm_id
5223 ,p_business_group_id => p_business_group_id
5224 ,p_number_of_copies => p_number_of_copies
5225 ,p_object_version_number => p_object_version_number
5226 ,p_effective_date => fnd_date.canonical_to_date(p_effective_date)
5227 ,p_no_dup_rslt => p_no_dup_rslt
5228 );
5229 -- update the tasklist row
5230 update_task_list_row(p_copy_entity_txn_id,fnd_date.canonical_to_date(p_effective_date));
5231 -- update the status of the row.
5232 update pqh_copy_entity_txns
5233 set status = 'COPIED' /* To disable View Log Icon */
5234 ,start_with = 'BEN_PDW_PLN_OVVW_FUNC' /*enable the continue icon*/
5235 where copy_entity_txn_id = p_copy_entity_txn_id;
5236
5237 -- finally commit
5238 commit;
5239
5240 exception
5241 when others then
5242
5243 rollback;
5244 -- update the txn row
5245 update pqh_copy_entity_txns
5246 set status = 'ERROR' /* To disable View Log Icon */
5247 ,start_with = null /*disable the continue icon*/
5248 where copy_entity_txn_id = p_copy_entity_txn_id;
5249 commit;
5250 raise;
5251 end process;
5252
5253 -- this row needs to be created for the proper functioning of Plan Design Wizard.
5254 procedure copy_elig_pzip_bnftgrp( p_copy_entity_txn_id IN NUMBER)
5255 is
5256 begin
5257 dump_elig_prfls(p_copy_entity_txn_id);
5258 copy_PostalZip_Bnft_Grp(p_copy_entity_txn_id);
5259 -- mark the future data Exists column
5260 mark_future_data_exists(p_copy_entity_txn_id);
5261 exception
5262 when others then
5263 rollback;
5264 raise;
5265 end;
5266
5267
5268 PROCEDURE copy_elig_pzip_bnft_to_stg
5269 ( p_copy_entity_txn_id IN NUMBER
5270 ,p_copy_mode IN VARCHAR2
5271 ,p_request_id OUT NOCOPY NUMBER
5272 ) IS
5273 l_proc varchar2(72) := g_package||'.copy_elig_pzip_bnftgrp';
5274
5275 begin
5276
5277 if p_copy_mode = 'ONLINE' then
5278
5279 copy_elig_pzip_bnftgrp(p_copy_entity_txn_id);
5280
5281 elsif p_copy_mode = 'CONCUR' then
5282 -- call the concurrent process
5283 p_request_id := fnd_request.submit_request
5284 (application => 'BEN'
5285 ,program => 'BEPDWELG'
5286 ,description => NULL
5287 ,sub_request => FALSE
5288 ,argument1 => p_copy_entity_txn_id);
5289 update pqh_copy_entity_txns
5290 set status = 'COPYING_IN_PROGRESS'
5291 ,start_with = null
5292 where copy_entity_txn_id = p_copy_entity_txn_id;
5293 end if;
5294 exception
5295 when others then
5296 rollback;
5297 raise;
5298 end copy_elig_pzip_bnft_to_stg;
5299
5300
5301 procedure copy_elig_pzip_bnftgrp (
5302 errbuf OUT NOCOPY VARCHAR2
5303 ,retcode OUT NOCOPY NUMBER
5304 ,p_copy_entity_txn_id IN NUMBER
5305 ) is
5306
5307 begin
5308 -- copy all the eligibility profiles, zip code and benefit groups in this process.
5309 copy_elig_pzip_bnftgrp(p_copy_entity_txn_id);
5310 -- update the status of the row.
5311 update pqh_copy_entity_txns
5312 set status = 'COPIED' /* To disable View Log Icon */
5313 ,start_with = 'BEN_PDW_PLN_OVVW_FUNC' /*show the continue icon*/
5314 where copy_entity_txn_id = p_copy_entity_txn_id;
5315
5316 commit;
5317 exception
5318 when others then
5319 rollback;
5320 -- update the txn row
5321 update pqh_copy_entity_txns
5322 set status = 'ERROR' /* To disable View Log Icon */
5323 ,start_with = null /*disable the continue icon*/
5324 where copy_entity_txn_id = p_copy_entity_txn_id;
5325 commit;
5326 raise;
5327 end copy_elig_pzip_bnftgrp;
5328 END BEN_PDW_COPY_BEN_TO_STG;
[Home] [Help]