[Home] [Help]
PACKAGE BODY: APPS.BEN_PLAN_DESIGN_TXNS_API
Source
1 Package Body BEN_PLAN_DESIGN_TXNS_API as
2 /* $Header: becetapi.pkb 120.5 2008/05/15 06:25:10 pvelvano noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' BEN_PLAN_DESIGN_TXN_APIS.';
7 g_debug boolean := hr_utility.debug_enabled;
8
9 --
10 -- Private procedure to update the cer with target details
11 --
12 procedure update_cer_with_target(p_copy_entity_txn_id number)
13 is
14 l_counter number;
15 begin
16 l_counter := nvl(ben_pd_copy_to_ben_one.g_pk_tbl.LAST, 0);
17 if l_counter > 0 then
18 for i in 1..l_counter loop
19 update ben_copy_entity_results
20 set information9 = ben_pd_copy_to_ben_one.g_pk_tbl(i).copy_reuse_type||'-'||ben_pd_copy_to_ben_one.g_pk_tbl(i).new_value
21 where copy_entity_txn_id = p_copy_entity_txn_id
22 and table_route_id = ben_pd_copy_to_ben_one.g_pk_tbl(i).table_route_id
23 and information1 = ben_pd_copy_to_ben_one.g_pk_tbl(i).old_value ;
24 end loop;
25 end if;
26
27 /* Using FORALL gives compilation error -
28 ** Cannot reference fields of BULK In-BIND table of records
29 */
30
31 /*
32 forall i in ben_pd_copy_to_ben_one.g_pk_tbl.FIRST..ben_pd_copy_to_ben_one.g_pk_tbl.LAST
33 update ben_copy_entity_results
34 set information9 = ben_pd_copy_to_ben_one.g_pk_tbl(i).copy_reuse_type||'-'||ben_pd_copy_to_ben_one.g_pk_tbl(i).new_value
35 where copy_entity_txn_id = p_copy_entity_txn_id
36 and table_route_id = ben_pd_copy_to_ben_one.g_pk_tbl(i).table_route_id
37 and information1 = ben_pd_copy_to_ben_one.g_pk_tbl(i).old_value ;
38 */
39
40 end update_cer_with_target ;
41
42 --
43 -- ----------------------------------------------------------------------------
44 -- |------------------------< create_PLAN_DESIGN_TXN >----------------------|
45 -- ----------------------------------------------------------------------------
46 --
47 procedure create_PLAN_DESIGN_TXN
48 (
49 p_validate in number default 0 -- false
50 ,p_copy_entity_txn_id out nocopy number
51 ,p_transaction_category_id in number default null
52 ,p_txn_category_attribute_id in number default null
53 ,p_context_business_group_id in number default null
54 ,p_datetrack_mode in varchar2 default null
55 ,p_proc_typ_cd in varchar2 default null -- Transaction category Short Name
56 ,p_action_date in date default null
57 ,p_src_effective_date in date default null
58 ,p_number_of_copies in number default null
59 ,p_process_name in varchar2 default null
60 ,p_replacement_type_cd in varchar2 default null
61 ,p_sfl_step_name in varchar2 default null
62 ,p_increment_by in number default null
63 ,p_status in varchar2 default null
64 ,p_cet_object_version_number out nocopy number
65 ,p_effective_date in date
66 ,p_copy_entity_attrib_id out nocopy number
67 ,p_row_type_cd in varchar2 default null
68 ,p_information_category in varchar2 default null
69 ,p_prefix_suffix_text in varchar2 default null
70 ,p_export_file_name in varchar2 default null
71 ,p_target_typ_cd in varchar2 default null
72 ,p_reuse_object_flag in varchar2 default null
73 ,p_target_business_group_id in varchar2 default null
74 ,p_search_by_cd1 in varchar2 default null
75 ,p_search_value1 in varchar2 default null
76 ,p_search_by_cd2 in varchar2 default null
77 ,p_search_value2 in varchar2 default null
78 ,p_search_by_cd3 in varchar2 default null
79 ,p_search_value3 in varchar2 default null
80 ,p_prefix_suffix_cd in varchar2 default null
81 ,p_information13 in varchar2 default null
82 ,p_information14 in varchar2 default null
83 ,p_information15 in varchar2 default null
84 ,p_information16 in varchar2 default null
85 ,p_information17 in varchar2 default null
86 ,p_information18 in varchar2 default null
87 ,p_information19 in varchar2 default null
88 ,p_information20 in varchar2 default null
89 ,p_information21 in varchar2 default null
90 ,p_information22 in varchar2 default null
91 ,p_information23 in varchar2 default null
92 ,p_information24 in varchar2 default null
93 ,p_information25 in varchar2 default null
94 ,p_information26 in varchar2 default null
95 ,p_information27 in varchar2 default null
96 ,p_information28 in varchar2 default null
97 ,p_information29 in varchar2 default null
98 ,p_information30 in varchar2 default null
99 ,p_cea_object_version_number out nocopy number
100 ) is
101 --
102 -- Declare cursors and local variables
103 --
104 l_copy_entity_txn_id pqh_copy_entity_txns.copy_entity_txn_id%TYPE;
105 l_copy_entity_attrib_id pqh_copy_entity_attribs.copy_entity_attrib_id%TYPE;
106 l_proc varchar2(72) := g_package||'create_PLAN_DESIGN_TXN';
107 l_object_version_number pqh_copy_entity_txns.object_version_number%TYPE;
108 l_cetobject_version_number pqh_copy_entity_txns.object_version_number%TYPE;
109
110 --
111 cursor txn_cat_c is
112 select transaction_category_id
113 from pqh_transaction_categories
114 where short_name = p_proc_typ_cd ;
115 --
116 l_transaction_category_id pqh_transaction_categories.transaction_category_id%type;
117 --
118 cursor c_db is select name from v$database ;
119 --
120 l_db_name varchar2(30);
121 --
122 cursor c_bg(v_bg_id number) is
123 select name from per_business_groups
124 where business_group_id = v_bg_id ;
125 --
126 l_context_bg_name per_business_groups.name%type;
127 --
128 begin
129 --
130 hr_utility.set_location('Entering:'|| l_proc, 10);
131 --
132 -- Issue a savepoint if operating in validation only mode
133 --
134 savepoint create_PLAN_DESIGN_TXN;
135 --
136 hr_utility.set_location(l_proc, 20);
137 --
138 fnd_msg_pub.initialize;
139 --
140
141 -- Get the context Database Name
142 begin
143 open c_db ;
144 fetch c_db into l_db_name ;
145 close c_db ;
146 exception when others then
147 null ;
148 end ;
149 --
150 begin
151 --
152 --l_context_bg_name := fnd_global.PER_BUSINESS_GROUP_ID ;
153 open c_bg(p_context_business_group_id) ;
154 fetch c_bg into l_context_bg_name ;
155 close c_bg ;
156 --
157 exception when others then
158 null ;
159 end ;
160 -- Need to create a row into pqh_transaction_category(tct) table
161 --
162 hr_utility.set_location('Before PQH_COPY_ENTITY_TXNS_APIS.create_COPY_ENTITY_TXN ', 60);
163
164 --
165 open txn_cat_c;
166 fetch txn_cat_c into l_transaction_category_id;
167 close txn_cat_c;
168 --
169 hr_utility.set_location('l_transaction_category_id '||l_transaction_category_id, 60);
170 --
171 PQH_COPY_ENTITY_TXNS_API.create_COPY_ENTITY_TXN
172 (
173 p_validate => false
174 , p_copy_entity_txn_id => l_copy_entity_txn_id
175 ,p_transaction_category_id => l_transaction_category_id
176 ,p_txn_category_attribute_id => p_txn_category_attribute_id -- 999 what are these values.
177 ,p_context_business_group_id => p_context_business_group_id
178 -- ,p_datetrack_mode => p_datetrack_mode
179 ,p_context => 'BEN_PDWIZ'
180 ,p_action_date => p_action_date
181 ,p_src_effective_date => p_src_effective_date
182 ,p_number_of_copies => p_number_of_copies
183 ,p_display_name => p_process_name
184 ,p_replacement_type_cd => p_replacement_type_cd
185 ,p_start_with => p_sfl_step_name
186 ,p_increment_by => p_increment_by
187 ,p_status => p_status
188 ,p_object_version_number => l_cetobject_version_number
189 ,p_effective_date => trunc(p_effective_date)
190 );
191 --
192 hr_utility.set_location('After PQH_COPY_ENTITY_TXNS_APIS.create_COPY_ENTITY_TXN ', 60);
193
194
195 --
196 -- Set all output arguments
197 --
198 p_copy_entity_txn_id := l_copy_entity_txn_id;
199 p_cet_object_version_number := l_cetobject_version_number;
200 --
201 pqh_copy_entity_attribs_api.create_copy_entity_attrib
202 (
203 p_validate => false
204 ,p_copy_entity_attrib_id => l_copy_entity_attrib_id
205 ,p_copy_entity_txn_id => p_copy_entity_txn_id
206 ,p_row_type_cd => p_row_type_cd
207 ,p_information_category => p_information_category
208 ,p_information1 => p_prefix_suffix_text
209 ,p_information2 => p_export_file_name
210 ,p_information3 => p_target_typ_cd
211 ,p_information4 => p_reuse_object_flag
212 ,p_information5 => p_target_business_group_id
213 ,p_information6 => p_search_by_cd1
214 ,p_information7 => p_search_value1
215 ,p_information8 => p_search_by_cd2
216 ,p_information9 => p_search_value2
217 ,p_information10 => p_search_by_cd3
218 ,p_information11 => p_search_value3
219 ,p_information12 => p_prefix_suffix_cd
220 ,p_information13 => p_information13
221 ,p_information14 => p_information14
222 ,p_information15 => p_information15
223 ,p_information16 => p_information16
224 ,p_information17 => p_information17
225 ,p_information18 => p_information18
226 ,p_information19 => p_information19
227 ,p_information20 => p_information20
228 ,p_information21 => p_information21
229 ,p_information22 => p_information22
230 ,p_information23 => p_information23
231 ,p_information24 => p_information24
232 ,p_information25 => p_information25
233 ,p_information26 => p_information26
234 ,p_information27 => p_information27
235 ,p_information28 => p_information28
236 ,p_information29 => l_db_name -- p_information29
237 ,p_information30 => l_context_bg_name -- p_information30
238 ,p_object_version_number => l_object_version_number
239 ,p_effective_date => p_effective_date
240 );
241
242 --
243 -- Set all output arguments
244 --
245 p_copy_entity_attrib_id := l_copy_entity_attrib_id;
246 p_cea_object_version_number := l_object_version_number;
247 --
248 -- When in validation only mode raise the Validate_Enabled exception
249 --
250 if p_validate = 1 then
251 raise hr_API.validate_enabled;
252 end if;
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 70);
255 --
256 exception
257 --
258 when hr_API.validate_enabled then
259 --
260 -- As the Validate_Enabled exception has been raised
261 -- we must rollback to the savepoint
262 --
263 ROLLBACK TO create_PLAN_DESIGN_TXN;
264 --
265 -- Only set output warning arguments
266 -- (Any key or derived arguments must be set to null
267 -- when validation only mode is being used.)
268 --
269 p_copy_entity_txn_id := null;
270 p_cea_object_version_number := null;
271 hr_utility.set_location(' Leaving:'||l_proc, 80);
272 hr_utility.set_location(' hr_API.validate_enabled:'||l_proc, 80);
273 --
274 when app_exception.application_exception then
275 p_copy_entity_txn_id := null;
276 p_cea_object_version_number := null;
277
278 fnd_msg_pub.add;
279 hr_utility.set_location(' app_exception.application_exception:'||l_proc, 80);
280
281 --
282 when others then
283 --
284 -- A validation or unexpected error has occured
285 --
286 ROLLBACK TO create_PLAN_DESIGN_TXN;
287 p_copy_entity_txn_id := null;
288 p_cea_object_version_number := null;
289
290 hr_utility.set_location(' when others:'||l_proc, 80);
291 raise;
292 --
293 end create_PLAN_DESIGN_TXN;
294
295 -- ----------------------------------------------------------------------------
296 -- |------------------------< update_PLAN_DESIGN_TXN >--- ------------------|
297 -- ----------------------------------------------------------------------------
298 --
299 procedure update_PLAN_DESIGN_TXN
300 (p_validate in number default 0 --boolean default false
301 ,p_copy_entity_txn_id in number
302 ,p_transaction_category_id in number default hr_API.g_number
303 ,p_txn_category_attribute_id in number default hr_API.g_number
304 ,p_context_business_group_id in number default hr_api.g_number
305 ,p_datetrack_mode in varchar2 default hr_api.g_varchar2
306 ,p_proc_typ_cd in varchar2 default hr_API.g_varchar2
307 ,p_action_date in date default hr_API.g_date
308 ,p_src_effective_date in date default hr_API.g_date
309 ,p_number_of_copies in number default hr_API.g_number
310 ,p_process_name in varchar2 default hr_API.g_varchar2
311 ,p_replacement_type_cd in varchar2 default hr_API.g_varchar2
312 ,p_sfl_step_name in varchar2 default hr_API.g_varchar2
313 ,p_increment_by in number default hr_API.g_number
314 ,p_status in varchar2 default hr_API.g_varchar2
315 ,p_cet_object_version_number in out nocopy number
316 ,p_effective_date in date
317 ,p_copy_entity_attrib_id in number
318 ,p_row_type_cd in varchar2 default hr_api.g_varchar2
319 ,p_information_category in varchar2 default hr_api.g_varchar2
320 ,p_prefix_suffix_text in varchar2 default hr_api.g_varchar2
321 ,p_export_file_name in varchar2 default hr_api.g_varchar2
322 ,p_target_typ_cd in varchar2 default hr_api.g_varchar2
323 ,p_reuse_object_flag in varchar2 default hr_api.g_varchar2
324 ,p_target_business_group_id in varchar2 default hr_api.g_varchar2
325 ,p_search_by_cd1 in varchar2 default hr_api.g_varchar2
326 ,p_search_value1 in varchar2 default hr_api.g_varchar2
327 ,p_search_by_cd2 in varchar2 default hr_api.g_varchar2
328 ,p_search_value2 in varchar2 default hr_api.g_varchar2
329 ,p_search_by_cd3 in varchar2 default hr_api.g_varchar2
330 ,p_search_value3 in varchar2 default hr_api.g_varchar2
331 ,p_prefix_suffix_cd in varchar2 default hr_api.g_varchar2
332 ,p_information13 in varchar2 default hr_api.g_varchar2
333 ,p_information14 in varchar2 default hr_api.g_varchar2
334 ,p_information15 in varchar2 default hr_api.g_varchar2
335 ,p_information16 in varchar2 default hr_api.g_varchar2
336 ,p_information17 in varchar2 default hr_api.g_varchar2
337 ,p_information18 in varchar2 default hr_api.g_varchar2
338 ,p_information19 in varchar2 default hr_api.g_varchar2
339 ,p_information20 in varchar2 default hr_api.g_varchar2
340 ,p_information21 in varchar2 default hr_api.g_varchar2
341 ,p_information22 in varchar2 default hr_api.g_varchar2
342 ,p_information23 in varchar2 default hr_api.g_varchar2
343 ,p_information24 in varchar2 default hr_api.g_varchar2
344 ,p_information25 in varchar2 default hr_api.g_varchar2
345 ,p_information26 in varchar2 default hr_api.g_varchar2
346 ,p_information27 in varchar2 default hr_api.g_varchar2
347 ,p_information28 in varchar2 default hr_api.g_varchar2
348 ,p_information29 in varchar2 default hr_api.g_varchar2
349 ,p_information30 in varchar2 default hr_api.g_varchar2
350 ,p_upd_record_type in varchar2 default null
351 ,p_cea_object_version_number in out nocopy number
352 ) is
353 --
354 -- Declare cursors and local variables
355 --
356 l_proc varchar2(72) := g_package||'update_PLAN_DESIGN_TXN';
357 l_object_version_number pqh_copy_entity_txns.object_version_number%TYPE;
358 l_cet_object_version_number pqh_copy_entity_txns.object_version_number%TYPE;
359 l_cea_object_version_number pqh_copy_entity_txns.object_version_number%TYPE;
360 --
361 cursor chk_trgt_bgid(p_copy_entity_txn_id number,
362 p_target_business_group_id varchar2,
363 p_copy_entity_attrib_id number) is
364 select information5
365 from pqh_copy_entity_attribs
366 where copy_entity_attrib_id = p_copy_entity_attrib_id
367 -- and information5 <> p_target_business_group_id
368 and copy_entity_txn_id = p_copy_entity_txn_id ;
369 --
370 cursor c_unmapped_rows is
371 select unique table_route_id
372 from ben_copy_entity_results
373 where copy_entity_txn_id = p_copy_entity_txn_id
374 -- Only take unmapped rows.
375 and (information176 is null or
376 (information180 is not null and information176 is null));
377 --
378 cursor c_leg is
379 select bg.legislation_code
380 from per_business_groups bg
381 where bg.business_group_id = p_target_business_group_id; -- 9999
382 --
383 l_legislation_code varchar2(150);
384 --
385 cursor c_pln_tr is
386 select table_route_id
387 from pqh_table_route tr
388 where tr.table_alias = 'PLN' and where_clause = 'BEN_PL_F' ;
389 --
390 l_table_route_id number ;
391 l_ret varchar2(1000);
392 --
393 l_ben_start_date DATE;
394 l_icx_date_format_mask VARCHAR2(30);
395 --
396 begin
397 --
398 hr_utility.set_location('Entering:'|| l_proc, 10);
399 --
400 -- Issue a savepoint if operating in validation only mode
401 --
402 savepoint update_PLAN_DESIGN_TXN;
403 --
404 fnd_msg_pub.initialize;
405 --
406 hr_utility.set_location(l_proc, 20);
407 --
408 -- Bug 3001617
409 -- This is the benchmark date for Benefits object to start, particularly
410 -- because all of BEN delivered lookups start from 01-Jan-1951
411 --
412 l_ben_start_date := to_date('01-01-1951', 'DD-MM-YYYY');
413 --
414 if p_action_date <> hr_api.g_date and
415 p_action_date < l_ben_start_date
416 then
417 --
418 fnd_profile.get( NAME => 'ICX_DATE_FORMAT_MASK'
419 ,VAL => l_icx_date_format_mask );
420 --
421 fnd_message.set_name('BEN', 'BEN_94216_EFF_DATE_INCORRECT');
422 fnd_message.set_token('DATE', to_char(l_ben_start_date, l_icx_date_format_mask));
423 fnd_message.raise_error;
424 --
425 end if;
426 --
427 -- Bug 3001617
428 --
429 -- Process Logic
430 --
431 l_cet_object_version_number := p_cet_object_version_number;
432 --
433 if p_upd_record_type in ('CET', 'CET_CEA')
434 then
435 --
436 pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
437 (
438 p_copy_entity_txn_id => p_copy_entity_txn_id
439 ,p_transaction_category_id => p_transaction_category_id
440 ,p_txn_category_attribute_id => p_txn_category_attribute_id
441 ,p_context_business_group_id => p_context_business_group_id
442 ,p_datetrack_mode => p_datetrack_mode
443 -- ,p_context => p_proc_typ_cd
444 ,p_action_date => p_action_date
445 ,p_src_effective_date => p_src_effective_date
446 ,p_number_of_copies => p_number_of_copies
447 ,p_display_name => p_process_name
448 ,p_replacement_type_cd => p_replacement_type_cd
449 ,p_start_with => p_sfl_step_name
450 ,p_increment_by => p_increment_by
451 ,p_status => p_status
452 ,p_object_version_number => l_cet_object_version_number
453 ,p_effective_date => trunc(p_effective_date)
454 );
455 --
456 end if;
457 --
458 hr_utility.set_location(l_proc, 60);
459 --
460 l_cea_object_version_number := p_cea_object_version_number;
461 --
462 if p_upd_record_type in ('CEA', 'CET_CEA')
463 then
464 --
465 open chk_trgt_bgid(p_copy_entity_txn_id,p_target_business_group_id,p_copy_entity_attrib_id);
466 fetch chk_trgt_bgid into l_ret;
467 --
468 if ((chk_trgt_bgid%found and
469 l_ret <> p_target_business_group_id)
470 or ( p_target_typ_cd = 'BEN_PDFILE')) then
471 --
472 open c_pln_tr ;
473 fetch c_pln_tr into l_table_route_id ;
474 close c_pln_tr ;
475 --
476 /* No need to update all the rows
477 update ben_copy_entity_results
478 set information175 = null,
479 information176 = null,
480 information179 = null,
481 information180 = null
482 where copy_entity_txn_id = p_copy_entity_txn_id
483 and table_route_id <> l_table_route_id ; */
484 --
485 update ben_copy_entity_results
486 set information175 = null,
487 information176 = null,
488 information179 = null,
489 information180 = null
490 where copy_entity_txn_id = p_copy_entity_txn_id
491 and (information175 is not null
492 or information176 is not null
493 or information179 is not null
494 or information180 is not null)
495 and table_route_id <> l_table_route_id ;
496 end if;
497 --
498 close chk_trgt_bgid;
499 --
500
501 pqh_copy_entity_attribs_api.update_copy_entity_attrib
502 (p_copy_entity_attrib_id => p_copy_entity_attrib_id
503 ,p_copy_entity_txn_id => p_copy_entity_txn_id
504 ,p_row_type_cd => p_row_type_cd
505 ,p_information_category => p_information_category
506 ,p_information1 => p_prefix_suffix_text
507 ,p_information2 => p_export_file_name
508 ,p_information3 => p_target_typ_cd
509 ,p_information4 => p_reuse_object_flag
510 ,p_information5 => p_target_business_group_id
511 ,p_information6 => p_search_by_cd1
512 ,p_information7 => p_search_value1
513 ,p_information8 => p_search_by_cd2
514 ,p_information9 => p_search_value2
515 ,p_information10 => p_search_by_cd3
516 ,p_information11 => p_search_value3
517 ,p_information12 => p_prefix_suffix_cd
518 ,p_information13 => p_information13
519 ,p_information14 => p_information14
520 ,p_information15 => p_information15
521 ,p_information16 => p_information16
522 ,p_information17 => p_information17
523 ,p_information18 => p_information18
524 ,p_information19 => p_information19
525 ,p_information20 => p_information20
526 ,p_information21 => p_information21
527 ,p_information22 => p_information22
528 ,p_information23 => p_information23
529 ,p_information24 => p_information24
530 ,p_information25 => p_information25
531 ,p_information26 => p_information26
532 ,p_information27 => p_information27
533 ,p_information28 => p_information28
534 ,p_information29 => p_information29
535 ,p_information30 => p_information30
536 ,p_object_version_number => l_cea_object_version_number
537 ,p_effective_date => p_effective_date
538 );
539 --
540 -- Support automapping without user intervention
541 -- as part of target details selection page
542 --
543 if (( p_target_typ_cd in ('BEN_PDDFBG','BEN_PDIMPT')) and
544 (p_sfl_step_name = 'BEN_PDC_TRGT_DTL_PAGE')
545 )
546 then
547 --
548 -- call automapping code
549 --
550 open c_leg;
551 fetch c_leg into l_legislation_code;
552 close c_leg;
553 --
554 for l_rec in c_unmapped_rows loop
555 --
556 auto_mapping(
557 p_copy_entity_txn_id => p_copy_entity_txn_id
558 ,p_table_route_id => l_rec.table_route_id
559 ,p_table_route_id2 => null
560 ,p_legislation_code => l_legislation_code
561 ,p_target_business_group_id => p_target_business_group_id
562 ,p_effective_date => p_src_effective_date
563 ,p_effective_date_to_copy => p_action_date
564 );
565 --
566 end loop;
567 end if;
568 --
569 end if;
570 --
571 -- When in validation only mode raise the Validate_Enabled exception
572 --
573 if p_validate = 1 then -- check what is 0
574 raise hr_API.validate_enabled;
575 end if;
576 --
577 -- Set all output arguments
578 --
579 p_cet_object_version_number := l_cet_object_version_number;
580 p_cea_object_version_number := l_cea_object_version_number;
581 --
582 hr_utility.set_location(' Leaving:'||l_proc, 70);
583 --
584 exception
585 --
586 when hr_API.validate_enabled then
587 --
588 -- As the Validate_Enabled exception has been raised
589 -- we must rollback to the savepoint
590 --
591 ROLLBACK TO update_PLAN_DESIGN_TXN;
592 --
593 -- Only set output warning arguments
594 -- (Any key or derived arguments must be set to null
595 -- when validation only mode is being used.)
596 --
597 hr_utility.set_location(' Leaving:'||l_proc, 80);
598 --
599 when app_exception.application_exception then
600
601 fnd_msg_pub.add;
602 hr_utility.set_location(' app_exception.application_exception:'||l_proc, 80);
603
604 --
605 when others then
606 --
607 -- A validation or unexpected error has occured
608 --
609 ROLLBACK TO update_PLAN_DESIGN_TXN;
610 p_cea_object_version_number := l_object_version_number ;
611 raise;
612 --
613 end update_PLAN_DESIGN_TXN;
614 -- ----------------------------------------------------------------------------
615 -- |------------------------< delete_PLAN_DESIGN_TXN >----------------------|
616 -- ----------------------------------------------------------------------------
617 --
618 procedure delete_PLAN_DESIGN_TXN
619 (p_validate in number default 0 -- default false
620 ,p_copy_entity_txn_id in number
621 ,p_cet_object_version_number in number
622 ,p_effective_date in date
623 ,p_retain_log in varchar2 default 'N' -- Bug No 4281567
624 ) is
625 --
626 -- Declare cursors and local variables
627 --
628 l_proc varchar2(72) := g_package||'delete_PLAN_DESIGN_TXN';
629 l_object_version_number pqh_copy_entity_txns.object_version_number%TYPE;
630 --
631 cursor c_copy_entity_attrib is
632 select copy_entity_attrib_id,cea_object_version_number
633 from ben_copy_entity_txns_vw
634 where copy_entity_txn_id = p_copy_entity_txn_id;
635 --
636 cursor c_cer is
637 select cer.*
638 from ben_copy_entity_results cer
639 where COPY_ENTITY_TXN_ID = p_COPY_ENTITY_TXN_ID;
640 --
641 l_copy_entity_attrib_id pqh_copy_entity_attribs.copy_entity_attrib_id%TYPE;
642 l_cea_object_version_number pqh_copy_entity_attribs.object_version_number%TYPE;
643 l_cer_object_version_number pqh_copy_entity_attribs.object_version_number%TYPE;
644
645 begin
646 --
647 hr_utility.set_location('Entering:'|| l_proc, 10);
648 --
649 -- Issue a savepoint if operating in validation only mode
650 --
651 pqh_cet_bus.chk_completed_target_err ( p_copy_entity_txn_id );
652 --
653 savepoint delete_PLAN_DESIGN_TXN;
654 --
655 hr_utility.set_location(l_proc, 20);
656 --
657 -- Process Logic
658 --
659 l_object_version_number := p_cet_object_version_number;
660 --
661 BEN_PLAN_DESIGN_TXNS_API.delete_plan_design_result
662 ( p_validate => p_validate
663 ,p_copy_entity_txn_id => p_copy_entity_txn_id
664 ,p_effective_date => p_effective_date
665 );
666 --
667 -- Bug No 4281567 Check retain log, if 'Y', then retain the log, header and attribs record
668 --
669 if p_retain_log = 'N' then
670 --
671 open c_copy_entity_attrib;
672 fetch c_copy_entity_attrib
673 into l_copy_entity_attrib_id,l_cea_object_version_number;
674 close c_copy_entity_attrib;
675 --
676 PQH_COPY_ENTITY_ATTRIBS_api.delete_COPY_ENTITY_ATTRIB
677 (p_validate => false
678 ,p_copy_entity_attrib_id => l_copy_entity_attrib_id
679 ,p_object_version_number => l_cea_object_version_number
680 ,p_effective_date => p_effective_date
681 );
682 --
683 -- Delete Log records
684 delete from pqh_process_log
685 where txn_id = p_copy_entity_txn_id
686 and module_cd = 'PDC_CP';
687
688 PQH_COPY_ENTITY_TXNS_api.delete_COPY_ENTITY_TXN
689 (p_validate => false
690 ,p_copy_entity_txn_id => p_copy_entity_txn_id
691 ,p_object_version_number => l_object_version_number
692 ,p_effective_date => p_effective_date
693 );
694 hr_utility.set_location(l_proc, 40);
695 --
696 else
697 --
698 -- Update the records in PQH_COPY_ENTITY_TXNS table with status as 'Purged'
699 --
700 PQH_COPY_ENTITY_TXNS_api.update_COPY_ENTITY_TXN
701 (p_validate => false
702 ,p_datetrack_mode => hr_api.g_correction
703 ,p_copy_entity_txn_id => p_copy_entity_txn_id
704 ,p_start_with => null
705 ,p_status => 'PURGED'
706 ,p_object_version_number => l_object_version_number
707 ,p_effective_date => sysdate
708 );
709 hr_utility.set_location(l_proc, 50);
710 end if;
711 --
712 hr_utility.set_location(l_proc, 60);
713 --
714 -- When in validation only mode raise the Validate_Enabled exception
715 --
716 if p_validate = 1 then
717 raise hr_API.validate_enabled;
718 end if;
719 --
720 hr_utility.set_location(' Leaving:'||l_proc, 70);
721 --
722 exception
723 --
724 when hr_API.validate_enabled then
725 --
726 -- As the Validate_Enabled exception has been raised
727 -- we must rollback to the savepoint
728 --
729 ROLLBACK TO delete_PLAN_DESIGN_TXN;
730 --
731 -- Only set output warning arguments
732 -- (Any key or derived arguments must be set to null
733 -- when validation only mode is being used.)
734 --
735 --
736 when others then
737 --
738 -- A validation or unexpected error has occured
739 --
740 ROLLBACK TO delete_PLAN_DESIGN_TXN;
741 raise;
742 --
743 end delete_PLAN_DESIGN_TXN;
744 --
745 procedure create_plan_design_result
746 (
747 p_validate in number default 0 -- false
748 ,p_copy_entity_result_id out nocopy number
749 ,p_copy_entity_txn_id in number
750 ,p_pl_id in number default null
751 ,p_pgm_id in number default null
752 ,p_business_group_id in number default null
753 ,p_number_of_copies in number default 0
754 ,p_object_version_number out nocopy number
755 ,p_effective_date in date
756 ,p_no_dup_rslt in varchar2 default null
757 )
758 is
759 l_proc varchar2(72) := g_package||'create_plan_design_result';
760 begin
761 hr_utility.set_location(' Entering :'||l_proc, 10);
762
763 if p_pgm_id is not null then
764 -- Call the Program routine
765 ben_plan_design_program_module.create_program_result
766 ( p_validate => p_validate
767 ,p_copy_entity_result_id => p_copy_entity_result_id
768 ,p_copy_entity_txn_id => p_copy_entity_txn_id
769 ,p_pgm_id => p_pgm_id
770 ,p_business_group_id => p_business_group_id
771 ,p_number_of_copies => p_number_of_copies
772 ,p_object_version_number => p_object_version_number
773 ,p_effective_date => p_effective_date
774 ,p_no_dup_rslt => p_no_dup_rslt
775 ) ;
776 --
777 elsif p_pl_id is not null then
778 -- Call the Plan routine
779 ben_plan_design_plan_module.create_plan_result
780 ( p_validate => p_validate
781 ,p_copy_entity_result_id => p_copy_entity_result_id
782 ,p_copy_entity_txn_id => p_copy_entity_txn_id
783 ,p_pl_id => p_pl_id
784 ,p_plip_id => null
785 ,p_business_group_id => p_business_group_id
786 ,p_number_of_copies => p_number_of_copies
787 ,p_object_version_number => p_object_version_number
788 ,p_effective_date => p_effective_date
789 ,p_no_dup_rslt => p_no_dup_rslt
790 );
791 --
792 else
793 -- don't do anything
794 return;
795 --
796 end if;
797
798 -- Create all Action Types for the Business Group
799 if p_number_of_copies = 1 then
800
801 ben_plan_design_program_module.create_actn_typ_result
802 (
803 p_validate => p_validate
804 ,p_copy_entity_txn_id => p_copy_entity_txn_id
805 ,p_business_group_id => p_business_group_id
806 ,p_number_of_copies => p_number_of_copies
807 ,p_effective_date => p_effective_date
808 );
809 end if;
810
811 --
812 --
813 hr_utility.set_location(' Leaving :'||l_proc, 10);
814 end create_plan_design_result;
815 --
816 -- Overloaded create_plan_design_result for Plan Design Wizard
817 -- This has been overloaded to allow copying Plans to staging area
818 -- without setting information8 to PLNIP
819 --
820 procedure create_plan_design_result
821 (
822 p_validate in number default 0 -- false
823 ,p_copy_entity_result_id out nocopy number
824 ,p_copy_entity_txn_id in number
825 ,p_pl_id in number default null
826 ,p_pgm_id in number default null
827 ,p_business_group_id in number default null
828 ,p_number_of_copies in number default 0
829 ,p_object_version_number out nocopy number
830 ,p_effective_date in date
831 ,p_no_dup_rslt in varchar2 default null
832 ,p_plan_in_program in varchar2
833 )
834 is
835 l_proc varchar2(72) := g_package||'create_plan_design_result';
836 l_copy_entity_result_id number;
837 l_object_version_number number;
838 begin
839 hr_utility.set_location(' Entering :'||l_proc, 10);
840 --
841 create_plan_design_result
842 (
843 p_validate => p_validate
844 ,p_copy_entity_result_id => l_copy_entity_result_id
845 ,p_copy_entity_txn_id => p_copy_entity_txn_id
846 ,p_pl_id => p_pl_id
847 ,p_pgm_id => p_pgm_id
848 ,p_business_group_id => p_business_group_id
849 ,p_number_of_copies => p_number_of_copies
850 ,p_object_version_number => l_object_version_number
851 ,p_effective_date => p_effective_date
852 ,p_no_dup_rslt => p_no_dup_rslt
853 );
854 --
855 if p_pl_id is not null and p_plan_in_program = 'Y' then
856 update ben_copy_entity_results
857 set information8 = NULL
858 where information1 = p_pl_id
859 and copy_entity_txn_id = p_copy_entity_txn_id
860 and table_alias = 'PLN';
861 end if;
862
863 -- Set out variables
864 p_copy_entity_result_id := l_copy_entity_result_id;
865 p_object_version_number := l_object_version_number;
866
867 hr_utility.set_location(' Leaving :'||l_proc, 10);
868 end create_plan_design_result;
869 --
870
871 procedure update_child_object_selection(
872 p_mirror_entity_result_id in number
873 ,p_copy_entity_txn_id in number
874 ,p_number_of_copies in number ) is
875
876 cursor c_child_object is
877 select /*+ INDEX ( cer, ben_copy_entity_results_fk1) */
878 cer.copy_entity_result_id
879 ,cer.mirror_entity_result_id
880 from ben_copy_entity_results cer
881 where cer.mirror_src_entity_result_id = p_mirror_entity_result_id
882 and cer.copy_entity_txn_id = p_copy_entity_txn_id;
883
884 begin
885 for l_child_object_rec in c_child_object
886 loop
887
888 update ben_copy_entity_results
889 set number_of_copies = p_number_of_copies
890 where copy_entity_result_id = l_child_object_rec.copy_entity_result_id;
891
892 update_child_object_selection(
893 p_mirror_entity_result_id => l_child_object_rec.mirror_entity_result_id
894 ,p_copy_entity_txn_id => p_copy_entity_txn_id
895 ,p_number_of_copies => p_number_of_copies);
896
897 end loop;
898 --
899 end update_child_object_selection;
900 --
901
902 procedure update_hgrid_child_selection(
903 p_copy_entity_result_id in number
904 ,p_mirror_entity_result_id in number
905 ,p_copy_entity_txn_id in number
906 ,p_number_of_copies in number
907 ,p_table_route_id in number) is
908
909 cursor c_table_name is
910 select where_clause
911 from pqh_table_route
912 where table_route_id = p_table_route_id;
913
914 l_table_name pqh_table_route.where_clause%type;
915 begin
916
917 open c_table_name;
918 fetch c_table_name into l_table_name;
919 close c_table_name;
920
921 if l_table_name = 'BEN_PLIP_F' then
922 if p_number_of_copies in (0,2) then -- If Plip is de-selected update Plip and child records
923
924 update ben_copy_entity_results
925 set number_of_copies = p_number_of_copies
926 where copy_entity_result_id = p_copy_entity_result_id;
927
928 update_child_object_selection(
929 p_mirror_entity_result_id => p_mirror_entity_result_id
930 ,p_copy_entity_txn_id => p_copy_entity_txn_id
931 ,p_number_of_copies => p_number_of_copies);
932
933 else --Plip can be selected only if Program is selected
934 null;
935 end if;
936 end if;
937
938 if l_table_name <> 'BEN_PLIP_F' then
939
940 update_child_object_selection(
941 p_mirror_entity_result_id => p_mirror_entity_result_id
942 ,p_copy_entity_txn_id => p_copy_entity_txn_id
943 ,p_number_of_copies => p_number_of_copies);
944
945 end if;
946 --
947 end update_hgrid_child_selection;
948 --
949
950 procedure update_plan_design_result
951 (
952 p_validate in number default 0 -- false
953 ,p_copy_entity_result_id in number
954 ,p_copy_entity_txn_id in number
955 ,p_business_group_id in number default hr_api.g_number
956 ,p_number_of_copies in number default hr_api.g_number
957 ,p_object_version_number in out nocopy number
958 ,p_effective_date in date
959 ,p_information1 in varchar2 default hr_api.g_varchar2
960 ,p_information8 in varchar2 default hr_api.g_varchar2
961 ,p_information175 in varchar2 default hr_api.g_varchar2
962 ,p_information176 in varchar2 default hr_api.g_varchar2
963 ,p_information177 in varchar2 default hr_api.g_varchar2
964 ,p_information178 in varchar2 default hr_api.g_varchar2
965 ,p_information179 in varchar2 default hr_api.g_varchar2
966 ,p_information180 in varchar2 default hr_api.g_varchar2
967 ,p_called_from in varchar2 default hr_api.g_varchar2
968 ,p_mirror_entity_result_id in number default hr_api.g_number
969 ) is
970 l_number_of_copies number(15);
971 l_proc varchar2(72) := g_package||'update_plan_design_result';
972 l_copy_entity_result_id number(15);
973 l_object_version_number number(15);
974 l_pgm_id number(15) default null;
975 l_pl_id number(15) default null;
976 --
977 cursor c_child_exists_for_pgm(c_pgm_id varchar2,c_copy_entity_txn_id number ) is
978 select null
979 from ben_copy_entity_results cer,
980 pqh_table_route trt
981 where cer.information1 = c_pgm_id
982 and cer.copy_entity_txn_id = c_copy_entity_txn_id
983 and cer.table_route_id = trt.table_route_id
984 and trt.where_clause = 'BEN_PGM_F'
985 and trt.from_clause = 'OAB'
986 and exists ( select /*+ INDEX ( cer1, ben_copy_entity_results_fk1) */ null
987 from
988 ben_copy_entity_results cer1
989 where cer1.mirror_src_entity_result_id = cer.copy_entity_result_id
990 and cer1.copy_entity_txn_id = cer.copy_entity_txn_id ) ;
991 --
992 cursor c_child_exists_for_pl(c_pl_id varchar2,c_copy_entity_txn_id number ) is
993 select null
994 from ben_copy_entity_results cer,
995 pqh_table_route trt
996 where cer.information1 = c_pl_id
997 and cer.copy_entity_txn_id = c_copy_entity_txn_id
998 and cer.table_route_id = trt.table_route_id
999 and trt.where_clause = 'BEN_PL_F'
1000 and trt.from_clause = 'OAB'
1001 and exists ( select /*+ INDEX ( cer1, ben_copy_entity_results_fk1) */ null from
1002 ben_copy_entity_results cer1
1003 where cer1.mirror_src_entity_result_id = cer.copy_entity_result_id
1004 and cer1.copy_entity_txn_id = cer.copy_entity_txn_id ) ;
1005
1006 l_dummy varchar2(30) ;
1007 l_child_exists boolean default false;
1008
1009 cursor c_object_version_number(c_copy_entity_result_id in number) is
1010 select cer.object_version_number
1011 from ben_copy_entity_results cer
1012 where cer.copy_entity_result_id = c_copy_entity_result_id;
1013
1014 l_cer_object_version_number number(15);
1015
1016 cursor c_non_dsply_recs(c_information1 in varchar2,
1017 c_table_name in varchar2,
1018 c_copy_entity_txn_id in number) is
1019 select cer.copy_entity_result_id,cer.object_version_number
1020 from ben_copy_entity_results cer,
1021 pqh_table_route trt
1022 where cer.information1 = c_information1
1023 and trt.where_clause = c_table_name
1024 and cer.copy_entity_txn_id = c_copy_entity_txn_id
1025 and cer.table_route_id = trt.table_route_id
1026 and cer.result_type_cd <> 'DISPLAY' ;
1027
1028 l_table_name pqh_table_route.where_clause%type;
1029
1030 begin
1031 --
1032 hr_utility.set_location(' Entering :'||l_proc, 10);
1033 l_number_of_copies := p_number_of_copies ;
1034 --
1035
1036 if p_called_from = 'HGRID' then
1037 open c_object_version_number(p_copy_entity_result_id);
1038 fetch c_object_version_number into l_cer_object_version_number;
1039 close c_object_version_number;
1040 else
1041 l_cer_object_version_number := p_object_version_number;
1042 end if;
1043
1044 ben_copy_entity_results_api.update_copy_entity_results
1045 ( p_validate => false
1046 ,p_copy_entity_result_id => p_copy_entity_result_id
1047 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1048 ,p_number_of_copies => l_number_of_copies
1049 ,p_object_version_number => l_cer_object_version_number
1050 ,p_effective_date => p_effective_date
1051 ,p_information323 => null
1052 );
1053 hr_utility.set_location(' Leaving :'||l_proc, 10);
1054 --
1055
1056 -- Update number_of_copies for Non Displayed
1057 -- Top level (Program or Plan )records
1058 if p_information8 is not null then -- Top level record
1059 if p_information8 = 'PGM' then
1060 l_table_name := 'BEN_PGM_F';
1061 else
1062 l_table_name := 'BEN_PL_F';
1063 end if;
1064
1065 for r_non_dsply_recs in c_non_dsply_recs
1066 (p_information1,
1067 l_table_name,
1068 p_copy_entity_txn_id)
1069 loop
1070 ben_copy_entity_results_api.update_copy_entity_results
1071 ( p_validate => false
1072 ,p_copy_entity_result_id => r_non_dsply_recs.copy_entity_result_id
1073 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1074 ,p_number_of_copies => l_number_of_copies
1075 ,p_object_version_number => r_non_dsply_recs.object_version_number
1076 ,p_effective_date => p_effective_date
1077 ,p_information323 => null
1078 );
1079 end loop;
1080 end if;
1081
1082 if p_called_from = 'SELECTION' then
1083
1084 if l_number_of_copies = 1 then
1085 --
1086 if p_information8 = 'PGM' then
1087 --
1088 l_pgm_id := p_information1;
1089 open c_child_exists_for_pgm(p_information1,p_copy_entity_txn_id ) ;
1090 fetch c_child_exists_for_pgm into l_dummy ;
1091 if c_child_exists_for_pgm%found then
1092 l_child_exists := true;
1093 end if;
1094 close c_child_exists_for_pgm ;
1095 --
1096 else
1097 --
1098 l_pl_id := p_information1;
1099 open c_child_exists_for_pl(p_information1,p_copy_entity_txn_id ) ;
1100 fetch c_child_exists_for_pl into l_dummy ;
1101 if c_child_exists_for_pl%found then
1102 l_child_exists := true;
1103 end if;
1104 close c_child_exists_for_pl;
1105 --
1106 end if;
1107 --
1108 if not l_child_exists then
1109 create_plan_design_result
1110 (
1111 p_validate => p_validate
1112 ,p_copy_entity_result_id => l_copy_entity_result_id
1113 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1114 ,p_pl_id => l_pl_id
1115 ,p_pgm_id => l_pgm_id
1116 ,p_business_group_id => p_business_group_id
1117 ,p_number_of_copies => p_number_of_copies
1118 ,p_object_version_number => l_object_version_number
1119 ,p_effective_date => p_effective_date
1120 );
1121 else
1122 update_child_object_selection(
1123 p_mirror_entity_result_id => p_mirror_entity_result_id
1124 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1125 ,p_number_of_copies => p_number_of_copies);
1126 end if;
1127
1128 else -- number_of_copies = 0
1129
1130 update_child_object_selection(
1131 p_mirror_entity_result_id => p_mirror_entity_result_id
1132 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1133 ,p_number_of_copies => p_number_of_copies);
1134
1135 end if;
1136 end if;
1137 --
1138 --
1139 -- Set all output arguments
1140 --
1141 p_object_version_number := l_cer_object_version_number;
1142 --
1143
1144 end update_plan_design_result ;
1145 --
1146 procedure delete_plan_design_result
1147 (
1148 p_validate in number default 0 -- false
1149 ,p_copy_entity_txn_id in number
1150 ,p_effective_date in date
1151 ) is
1152 l_proc varchar2(72) := g_package||'delete_plan_design_result';
1153 --
1154 cursor c_cpe(c_copy_entity_txn_id number) is
1155 select copy_entity_result_id,
1156 object_version_number
1157 from ben_copy_entity_results cpe
1158 where cpe.copy_entity_txn_id = c_copy_entity_txn_id;
1159
1160
1161 cursor c_cer(c_copy_entity_txn_id number) is
1162 select copy_entity_result_id,
1163 object_version_number
1164 from pqh_copy_entity_results cer
1165 where cer.copy_entity_txn_id = c_copy_entity_txn_id;
1166 --
1167 --
1168 begin
1169 hr_utility.set_location(' Entering :'||l_proc, 10);
1170 --
1171
1172 --
1173 -- delete from pqh_copy_entity_results
1174 --
1175 for l_cer in c_cer(p_copy_entity_txn_id) loop
1176 --
1177 pqh_copy_entity_results_api.delete_copy_entity_result
1178 (p_validate => false
1179 ,p_copy_entity_result_id => l_cer.copy_entity_result_id
1180 ,p_object_version_number => l_cer.object_version_number
1181 ,p_effective_date => p_effective_date
1182 ) ;
1183 --
1184 end loop ;
1185 --
1186
1187 --
1188 -- delete from ben_copy_entity_results
1189 --
1190 for l_cpe in c_cpe(p_copy_entity_txn_id) loop
1191 --
1192 ben_copy_entity_results_api.delete_copy_entity_results
1193 (p_validate => false
1194 ,p_copy_entity_result_id => l_cpe.copy_entity_result_id
1195 ,p_object_version_number => l_cpe.object_version_number
1196 ,p_effective_date => p_effective_date
1197 ) ;
1198 --
1199 end loop ;
1200
1201 hr_utility.set_location(' Leaving :'||l_proc, 10);
1202 --
1203 end delete_plan_design_result;
1204 --
1205 procedure get_effective_dates(
1206 p_process_effective_date in date
1207 ,p_start_date1 in date default null
1208 ,p_start_date2 in date default null
1209 ,p_effective_date_to_copy in date default null
1210 ,p_effective_date1 out nocopy date
1211 ,p_effective_date2 out nocopy date
1212 ) is
1213 l_out_effective_date1 date;
1214 l_out_effective_date2 date;
1215 begin
1216
1217 -- Default Effective_Date to Process Effective Date
1218 l_out_effective_date1 := p_process_effective_date;
1219 l_out_effective_date2 := p_process_effective_date;
1220
1221 -- If Object Start Dates in the source instance are available
1222 -- then Set Effective_Date = Object Start Date
1223 if p_start_date1 is not null then
1224 l_out_effective_date1 := p_start_date1;
1225 end if;
1226
1227 if p_start_date2 is not null then
1228 l_out_effective_date2 := p_start_date2;
1229 end if;
1230
1231 -- If Effective Date to Copy is entered and is
1232 -- greater than the Object Start Dates, then
1233 -- set Effective_Date = Effective Date to Copy
1234
1235 if p_effective_date_to_copy is not null then
1236
1237 if p_effective_date_to_copy > l_out_effective_date1 then
1238 l_out_effective_date1 := p_effective_date_to_copy;
1239 end if;
1240
1241 if p_effective_date_to_copy > l_out_effective_date2 then
1242 l_out_effective_date2 := p_effective_date_to_copy;
1243 end if;
1244
1245 end if;
1246
1247 -- Set out variables
1248 p_effective_date1 := l_out_effective_date1;
1249 p_effective_date2 := l_out_effective_date2;
1250
1251 end get_effective_dates;
1252 --
1253 procedure auto_mapping(
1254 p_validate in number default 0 -- false
1255 ,p_copy_entity_txn_id in number
1256 ,p_table_route_id in number
1257 ,p_table_route_id2 in number
1258 ,p_legislation_code in varchar2
1259 ,p_target_business_group_id in number default hr_api.g_number
1260 ,p_effective_date in date default null
1261 ,p_effective_date_to_copy in date default null
1262 ) is
1263 l_proc varchar2(72) := g_package||'auto_mapping';
1264 --
1265 cursor c_source_data(p_table_route_id number) is
1266 select information173,
1267 information174,
1268 information175,
1269 information176,
1270 information177,
1271 information178,
1272 information179,
1273 information180,
1274 information166,
1275 information306
1276 from ben_copy_entity_results
1277 where table_route_id = p_table_route_id
1278 and copy_entity_txn_id = p_copy_entity_txn_id
1279 -- Only take unmapped rows.
1280 and (information176 is null or
1281 (information180 is not null and information176 is null));
1282 --
1283 cursor c_table_name (p_table_route_id number)is
1284 select WHERE_CLAUSE
1285 from pqh_table_route
1286 where table_route_id = p_table_route_id;
1287
1288 --
1289 cursor c_AssignmentSetIdNameLovVO(p_source_name1 varchar2) is
1290 select assignment_set_name name, assignment_set_id id
1291 from hr_assignment_sets
1292 where business_group_id = p_target_business_group_id
1293 and assignment_set_name = p_source_name1;
1294 --
1295 cursor c_AttendenceReasonNameLovVO(p_source_name1 varchar2,
1296 p_absence_attendance_type_id number,
1297 cv_effective_date date) is
1298 select hl.meaning name,
1299 abr.abs_attendance_reason_id id
1300 from per_abs_attendance_reasons abr,
1301 hr_leg_lookups hl
1302 where abr.name = hl.lookup_code and
1303 hl.lookup_type = 'ABSENCE_REASON' and
1304 hl.enabled_flag = 'Y'
1305 and meaning = p_source_name1
1306 and absence_attendance_type_id = p_absence_attendance_type_id
1307 and trunc(cv_effective_date)
1308 between Start_Date_Active and
1309 nvl(End_Date_Active, trunc(cv_effective_date) )
1310 and business_group_id = p_target_business_group_id;
1311 --
1312 cursor c_AttendenceTypeNameLovVO(p_source_name1 varchar2,
1313 cv_effective_date date) is
1314 select abt.name Name,
1315 abt.absence_attendance_type_id id
1316 from per_absence_attendance_types abt
1317 where trunc(cv_effective_date)
1318 between Date_Effective and nvl(Date_end, trunc(cv_effective_date))
1319 and business_group_id = p_target_business_group_id
1320 and abt.name = p_source_name1;
1321 --
1322 cursor c_CompetenceIdNameLovVO(p_source_name1 varchar2,
1323 cv_effective_date date) is
1324 select name name
1325 ,competence_id id
1326 from per_competences_vl
1327 where trunc(cv_effective_date)
1328 between Date_from and nvl(Date_to, trunc(cv_effective_date) )
1329 and nvl(business_group_id , p_target_business_group_id) = p_target_business_group_id
1330 and name = p_source_name1;
1331 --
1332 cursor c_DefinedBalanceIdLovVO(p_source_name1 varchar2) is
1333 select pbt.balance_name||' - '||pbd.dimension_name name ,
1334 pdb.defined_balance_id id
1335 from pay_balance_types pbt,
1336 pay_balance_dimensions pbd,
1337 pay_defined_balances pdb
1338 where pdb.balance_type_id = pbt.balance_type_id
1339 and pdb.balance_dimension_id = pbd.balance_dimension_id
1340 and nvl(pdb.business_group_id, p_target_business_group_id) = p_target_business_group_id
1341 and pbt.balance_name||' - '||pbd.dimension_name = p_source_name1;
1342
1343 --
1344 cursor c_EmployeeStatusLovVO(p_source_name1 varchar2) is
1345 select nvl(atl.user_status, stl.user_status) name
1346 , s.assignment_status_type_id id
1347 from per_assignment_status_types s,
1348 per_ass_status_type_amends a,
1349 per_assignment_status_types_tl stl,
1350 per_ass_status_type_amends_tl atl
1351 where a.assignment_status_type_id (+) = s.assignment_status_type_id
1352 and a.business_group_id (+) = p_target_business_group_id
1353 and nvl(s.business_group_id, p_target_business_group_id) = p_target_business_group_id
1354 and nvl(s.legislation_code, p_legislation_code) = p_legislation_code
1355 and nvl(a.active_flag, s.active_flag) = 'Y'
1356 and atl.ass_status_type_amend_id (+) = a.ass_status_type_amend_id
1357 and atl.language (+) = userenv('LANG')
1358 and stl.assignment_status_type_id = s.assignment_status_type_id
1359 and stl.language = userenv('LANG')
1360 and nvl(atl.user_status, stl.user_status) = p_source_name1;
1361 --
1362 cursor c_InputValueLovVO(p_source_name1 varchar2,
1363 p_element_type_id number,
1364 cv_effective_date date) is
1365 select pivt.name name,
1366 pivt.input_value_id id
1367 from pay_input_values_f piv,
1368 pay_input_values_f_tl pivt
1369 where trunc(cv_effective_date)
1370 between piv.effective_start_date and piv.effective_end_date
1371 and ((piv.business_group_id is null and nvl(piv.legislation_code, p_legislation_code)
1372 = p_legislation_code)or piv.business_group_id = p_target_business_group_id )
1373 and piv.input_value_id = pivt.input_value_id
1374 and pivt.language = userenv('LANG')
1375 and pivt.name = p_source_name1
1376 and element_type_id = p_element_type_id;
1377 --
1378 cursor c_JobGroupIdLovVO(p_source_name1 varchar2) is
1379 select displayed_name name,
1380 job_group_id id
1381 from per_job_groups
1382 where business_group_id = p_target_business_group_id
1383 and displayed_name = p_source_name1;
1384 --
1385 cursor c_JobIdLovVO(p_source_name1 varchar2, cv_effective_date date) is
1386 select jobtl.name name ,
1387 job.job_id id
1388 from per_jobs job
1389 ,per_jobs_tl jobtl
1390 where trunc(cv_effective_date)
1391 between Date_from and nvl(Date_to, trunc(cv_effective_date))
1392 and job.business_group_id + 0 = p_target_business_group_id
1393 and job.job_id = jobtl.job_id
1394 and jobtl.language = userenv('LANG')
1395 and jobtl.name = p_source_name1;
1396
1397 --
1398 cursor c_LegalEntityNameLovVO(p_source_name1 varchar2, cv_effective_date date) is
1399 select txu.name name,
1400 txu.tax_unit_id id
1401 from hr_tax_units_v txu
1402 where trunc(cv_effective_date)
1403 between Date_from and nvl(Date_to, trunc(cv_effective_date))
1404 and p_target_business_group_id = txu.business_group_id
1405 and txu.name = p_source_name1;
1406 --
1407 cursor c_LocationNameLovVO(p_source_name1 varchar2, cv_effective_date date) is
1408 select loc.location_code name,
1409 loc.location_id id
1410 from hr_locations loc
1411 where trunc(cv_effective_date) <= nvl( loc.inactive_date, trunc(cv_effective_date))
1412 and nvl(business_group_id,p_target_business_group_id) = p_target_business_group_id
1413 and loc.location_code = p_source_name1;
1414 --
1415 cursor c_GroupOptionIdLovVO( cv_name varchar2, cv_effective_date date) is
1416 select
1417 opt.name Name ,
1418 Opt.opt_id
1419 from ben_opt_f opt
1420 where opt.opt_id = opt.group_opt_id
1421 and opt.name = cv_name
1422 and trunc(cv_effective_date) between
1423 opt.effective_start_date and opt.effective_end_date;
1424
1425 --
1426 cursor c_GroupPlanLovVO( cv_name varchar2, cv_effective_date date) is
1427 select
1428 bp.name name,
1429 bp.pl_id id
1430 FROM
1431 ben_pl_f bp,
1432 ben_pl_typ_f bpt
1433 WHERE
1434 bp.pl_id = bp.group_pl_id
1435 and bp.name = cv_name
1436 and bp.pl_typ_id = bpt.pl_typ_id
1437 and bpt.opt_typ_cd = 'CWB'
1438 and trunc(cv_effective_date) between bp.effective_start_date and bp.effective_end_date
1439 and trunc(cv_effective_date) between bpt.effective_start_date and bpt.effective_end_date
1440 and bp.business_group_id = bpt.business_group_id;
1441
1442 --
1443 cursor c_OrganizationLovVO(p_source_name1 varchar2, cv_effective_date date) is
1444 select orgtl.name name,
1445 org.organization_id id
1446 from hr_all_organization_units org,
1447 hr_all_organization_units_tl orgtl
1448 where org.business_group_id = p_target_business_group_id
1449 and orgtl.organization_id = org.organization_id
1450 and orgtl.language = userenv('LANG')
1451 and org.internal_external_flag = 'INT'
1452 and trunc(cv_effective_date)
1453 between nvl(org.date_from, trunc(cv_effective_date))
1454 and nvl(org.date_to, trunc(cv_effective_date))
1455 and orgtl.name = p_source_name1;
1456
1457 --
1458 cursor c_PayBasisNameLovVO(p_source_name1 varchar2) is
1459 select name,
1460 pay_basis_id id
1461 from per_pay_bases
1462 where business_group_id = p_target_business_group_id
1463 and name = p_source_name1;
1464 --
1465 cursor c_PayrollNameLovVO(p_source_name1 varchar2, cv_effective_date date) is
1466 select prl.payroll_name name /* cg$fk */ ,
1467 prl.payroll_id id
1468 from pay_all_payrolls_f prl
1469 where trunc(cv_effective_date)
1470 between prl.effective_start_date and prl.effective_end_date
1471 and prl.business_group_id + 0 = p_target_business_group_id
1472 and prl.payroll_name = p_source_name1;
1473
1474 --
1475 cursor c_PersonTypeNameLovVO(p_source_name1 varchar2) is
1476 select ptl.user_person_type name,
1477 ppt.person_type_id id
1478 from per_person_types ppt,
1479 hr_leg_lookups hrlkup,
1480 per_person_types_tl ptl
1481 where active_flag = 'Y' and
1482 hrlkup.lookup_type = 'PERSON_TYPE'
1483 and hrlkup.lookup_code = ppt.system_person_type
1484 and ppt.active_flag = 'Y'
1485 and business_group_id = p_target_business_group_id
1486 and ppt.person_type_id = ptl.person_type_id
1487 and ptl.language = userenv('LANG')
1488 and ptl.user_person_type = p_source_name1;
1489
1490 --
1491 cursor c_PoplOrganizationLovVO(p_source_name1 varchar2, cv_effective_date date) is
1492 select org.name name,
1493 org.organization_id id
1494 from hr_organization_units org
1495 where trunc(cv_effective_date)
1496 between Date_from and nvl(Date_to, trunc(cv_effective_date))
1497 and org.business_group_id +0 = p_target_business_group_id
1498 and org.name = p_source_name1;
1499 --
1500 cursor c_PositionNameLovVO(p_source_name1 varchar2, cv_effective_date date) is
1501 select name,
1502 position_id id
1503 from per_positions
1504 where trunc(cv_effective_date) >= date_effective
1505 and business_group_id = p_target_business_group_id
1506 and name = p_source_name1;
1507 --
1508 cursor c_PositionStructureNameLovVO(p_source_name1 varchar2, cv_effective_date date) is
1509 select pos.NAME name,
1510 POS_STRUCTURE_VERSION_ID id
1511 from PER_POSITION_STRUCTURES pos,
1512 PER_POS_STRUCTURE_VERSIONS pov
1513 where pos.POSITION_STRUCTURE_ID = pov.POSITION_STRUCTURE_ID
1514 and trunc(cv_effective_date)
1515 between POV.DATE_FROM and nvl(POV.DATE_TO, trunc(cv_effective_date))
1516 and pos.business_group_id = p_target_business_group_id
1517 and pos.NAME = p_source_name1;
1518 --
1519 cursor c_QualificationNmaeLovVO(p_source_name1 varchar2) is
1520 select name, qualification_type_id id
1521 from per_qualification_types_tl pqttl
1522 where pqttl.language = userenv('LANG')
1523 and name = p_source_name1;
1524 --
1525 cursor c_RatingLevelIdNameLovVO(p_source_name1 varchar2
1526 ,p_competence_id number) is
1527 select rtl.name,
1528 rtl.rating_level_id id
1529 from per_rating_levels_vl rtl, /* MLS Changes*/
1530 per_competences pct
1531 where (rtl.competence_id = pct.competence_id
1532 or rtl.rating_scale_id = pct.rating_scale_id )
1533 and pct.competence_id = p_competence_id
1534 and nvl(rtl.business_group_id, p_target_business_group_id ) = p_target_business_group_id
1535 and rtl.name = p_source_name1;
1536 --
1537 cursor c_ElementTypeLovVO(p_source_name1 varchar2, cv_effective_date date) is
1538 select pett.element_name name
1539 ,pett.element_type_id id
1540 from pay_element_types_f pet,
1541 pay_element_types_f_tl pett
1542 where trunc(cv_effective_date)
1543 between nvl(pet.effective_start_date,trunc(cv_effective_date)) and nvl
1544 (pet.effective_end_date,trunc(cv_effective_date ))
1545 and pet.element_type_id=pett.element_type_id
1546 and pett.language = userenv('LANG')
1547 and ( (pet.business_group_id is null and nvl(pet.legislation_code, p_legislation_code) = p_legislation_code)
1548 or pet.business_group_id = p_target_business_group_id)
1549 and pett.element_name = p_source_name1;
1550 --
1551
1552 cursor c_GradeIdLovVO(p_source_name1 varchar2, cv_effective_date date) is
1553 select gra.name name
1554 ,gra.grade_id id
1555 from per_grades_vl gra /*MLS Changes*/
1556 where trunc(cv_effective_date)
1557 between Date_from and nvl(Date_to, trunc(cv_effective_date) )
1558 and business_group_id + 0 = p_target_business_group_id
1559 and gra.name = p_source_name1;
1560 --
1561 l_table_route_name pqh_table_route.WHERE_CLAUSE%type;
1562 --
1563 l_effective_date1 date;
1564 l_effective_date2 date;
1565 begin
1566 --
1567 hr_utility.set_location(' Entering :'||l_proc, 10);
1568 --
1569 open c_table_name(p_table_route_id);
1570 fetch c_table_name into l_table_route_name;
1571 close c_table_name;
1572 --
1573 if (l_table_route_name = 'BEN_ACTY_BASE_RT_F') then
1574 --
1575 for l_ret in c_source_data (p_table_route_id)
1576 loop
1577 --
1578 get_effective_dates(
1579 p_process_effective_date => p_effective_date
1580 ,p_start_date1 => l_ret.information166
1581 ,p_start_date2 => l_ret.information306
1582 ,p_effective_date_to_copy => p_effective_date_to_copy
1583 ,p_effective_date1 => l_effective_date1
1584 ,p_effective_date2 => l_effective_date2);
1585
1586 open c_ElementTypeLovVO(l_ret.information173, l_effective_date1);
1587 fetch c_ElementTypeLovVO into l_ret.information175,
1588 l_ret.information176;
1589 close c_ElementTypeLovVO;
1590 --
1591 open c_InputValueLovVO(l_ret.information177,l_ret.information176, l_effective_date2);
1592 fetch c_InputValueLovVO into l_ret.information179,
1593 l_ret.information180;
1594 close c_InputValueLovVO;
1595 --
1596 update_mapping_target_data (
1597 p_table_route_id =>p_table_route_id
1598 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1599 , p_source_id1 =>l_ret.information174
1600 , p_target_value1 =>l_ret.information175
1601 , p_target_id1 =>l_ret.information176
1602 , p_source_id2 =>l_ret.information178
1603 , p_target_value2 =>l_ret.information179
1604 , p_target_id2 =>l_ret.information180
1605 );
1606 --
1607 end loop;
1608 elsif (l_table_route_name = 'BEN_COMP_LVL_FCTR'
1609 or l_table_route_name = 'BEN_HRS_WKD_IN_PERD_FCTR'
1610 or l_table_route_name = 'BEN_HRS_WKD_IN_PERD_RT_F'
1611 or l_table_route_name = 'BEN_COMP_LVL_RT_F') then
1612 --
1613 for l_ret in c_source_data (p_table_route_id)
1614 loop
1615 --
1616 open c_DefinedBalanceIdLovVO(l_ret.information173);
1617 fetch c_DefinedBalanceIdLovVO into l_ret.information175,l_ret.information176;
1618 close c_DefinedBalanceIdLovVO;
1619 --
1620 update_mapping_target_data (
1621 p_table_route_id =>p_table_route_id
1622 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1623 , p_source_id1 =>l_ret.information174
1624 , p_target_value1 =>l_ret.information175
1625 , p_target_id1 =>l_ret.information176
1626 , p_source_id2 =>l_ret.information178
1627 , p_target_value2 =>l_ret.information179
1628 , p_target_id2 =>l_ret.information180
1629 );
1630 --
1631 end loop;
1632 elsif (l_table_route_name = 'BEN_ASNT_SET_RT_F'
1633 or l_table_route_name = 'BEN_ELIG_ASNT_SET_PRTE_F') then
1634 --
1635 for l_ret in c_source_data (p_table_route_id)
1636 loop
1637 --
1638 open c_AssignmentSetIdNameLovVO(l_ret.information173);
1639 fetch c_AssignmentSetIdNameLovVO into l_ret.information175,l_ret.information176;
1640 close c_AssignmentSetIdNameLovVO;
1641 --
1642 update_mapping_target_data (
1643 p_table_route_id =>p_table_route_id
1644 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1645 , p_source_id1 =>l_ret.information174
1646 , p_target_value1 =>l_ret.information175
1647 , p_target_id1 =>l_ret.information176
1648 , p_source_id2 =>l_ret.information178
1649 , p_target_value2 =>l_ret.information179
1650 , p_target_id2 =>l_ret.information180
1651 );
1652 --
1653 end loop;
1654 elsif (l_table_route_name = 'BEN_COMPTNCY_RT_F'
1655 or l_table_route_name = 'BEN_ELIG_COMPTNCY_PRTE_F')then
1656 --
1657 for l_ret in c_source_data (p_table_route_id)
1658 loop
1659
1660 get_effective_dates(
1661 p_process_effective_date => p_effective_date
1662 ,p_start_date1 => l_ret.information166
1663 ,p_start_date2 => l_ret.information306
1664 ,p_effective_date_to_copy => p_effective_date_to_copy
1665 ,p_effective_date1 => l_effective_date1
1666 ,p_effective_date2 => l_effective_date2);
1667
1668 --
1669 open c_CompetenceIdNameLovVO(l_ret.information173, l_effective_date1);
1670 fetch c_CompetenceIdNameLovVO into l_ret.information175,l_ret.information176;
1671 close c_CompetenceIdNameLovVO;
1672 --
1673 open c_RatingLevelIdNameLovVO(l_ret.information177,l_ret.information176);
1674 fetch c_RatingLevelIdNameLovVO into l_ret.information179,l_ret.information180;
1675 close c_RatingLevelIdNameLovVO;
1676 --
1677 update_mapping_target_data (
1678 p_table_route_id =>p_table_route_id
1679 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1680 , p_source_id1 =>l_ret.information174
1681 , p_target_value1 =>l_ret.information175
1682 , p_target_id1 =>l_ret.information176
1683 , p_source_id2 =>l_ret.information178
1684 , p_target_value2 =>l_ret.information179
1685 , p_target_id2 =>l_ret.information180
1686 );
1687 --
1688 end loop;
1689 elsif (l_table_route_name = 'BEN_EE_STAT_RT_F'
1690 or l_table_route_name = 'BEN_ELIG_EE_STAT_PRTE_F')then
1691 --
1692 for l_ret in c_source_data (p_table_route_id)
1693 loop
1694 --
1695 open c_EmployeeStatusLovVO(l_ret.information173);
1696 fetch c_EmployeeStatusLovVO into l_ret.information175,l_ret.information176;
1697 close c_EmployeeStatusLovVO;
1698 --
1699 update_mapping_target_data (
1700 p_table_route_id =>p_table_route_id
1701 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1702 , p_source_id1 =>l_ret.information174
1703 , p_target_value1 =>l_ret.information175
1704 , p_target_id1 =>l_ret.information176
1705 , p_source_id2 =>l_ret.information178
1706 , p_target_value2 =>l_ret.information179
1707 , p_target_id2 =>l_ret.information180
1708 );
1709 --
1710 end loop;
1711 elsif (l_table_route_name = 'BEN_ELIG_GRD_PRTE_F'
1712 or l_table_route_name = 'BEN_GRADE_RT_F')then
1713 --
1714 for l_ret in c_source_data (p_table_route_id)
1715 loop
1716
1717 get_effective_dates(
1718 p_process_effective_date => p_effective_date
1719 ,p_start_date1 => l_ret.information166
1720 ,p_start_date2 => l_ret.information306
1721 ,p_effective_date_to_copy => p_effective_date_to_copy
1722 ,p_effective_date1 => l_effective_date1
1723 ,p_effective_date2 => l_effective_date2);
1724
1725 --
1726 open c_GradeIdLovVO(l_ret.information173, l_effective_date1);
1727 fetch c_GradeIdLovVO into l_ret.information175,l_ret.information176;
1728 close c_GradeIdLovVO;
1729 --
1730 update_mapping_target_data (
1731 p_table_route_id =>p_table_route_id
1732 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1733 , p_source_id1 =>l_ret.information174
1734 , p_target_value1 =>l_ret.information175
1735 , p_target_id1 =>l_ret.information176
1736 , p_source_id2 =>l_ret.information178
1737 , p_target_value2 =>l_ret.information179
1738 , p_target_id2 =>l_ret.information180
1739 );
1740 --
1741 end loop;
1742 elsif (l_table_route_name = 'BEN_ELIG_JOB_PRTE_F'
1743 or l_table_route_name = 'BEN_JOB_RT_F')then
1744 --
1745 for l_ret in c_source_data (p_table_route_id)
1746 loop
1747
1748 get_effective_dates(
1749 p_process_effective_date => p_effective_date
1750 ,p_start_date1 => l_ret.information166
1751 ,p_start_date2 => l_ret.information306
1752 ,p_effective_date_to_copy => p_effective_date_to_copy
1753 ,p_effective_date1 => l_effective_date1
1754 ,p_effective_date2 => l_effective_date2);
1755
1756 --
1757 open c_JobIdLovVO(l_ret.information173, l_effective_date1);
1758 fetch c_JobIdLovVO into l_ret.information175,l_ret.information176;
1759 close c_JobIdLovVO;
1760 --
1761 update_mapping_target_data (
1762 p_table_route_id =>p_table_route_id
1763 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1764 , p_source_id1 =>l_ret.information174
1765 , p_target_value1 =>l_ret.information175
1766 , p_target_id1 =>l_ret.information176
1767 , p_source_id2 =>l_ret.information178
1768 , p_target_value2 =>l_ret.information179
1769 , p_target_id2 =>l_ret.information180
1770 );
1771 --
1772 end loop;
1773 elsif (l_table_route_name = 'BEN_ELIG_ORG_UNIT_PRTE_F'
1774 or l_table_route_name = 'BEN_ORG_UNIT_RT_F')then
1775 --
1776 for l_ret in c_source_data (p_table_route_id)
1777 loop
1778 --
1779
1780 get_effective_dates(
1781 p_process_effective_date => p_effective_date
1782 ,p_start_date1 => l_ret.information166
1783 ,p_start_date2 => l_ret.information306
1784 ,p_effective_date_to_copy => p_effective_date_to_copy
1785 ,p_effective_date1 => l_effective_date1
1786 ,p_effective_date2 => l_effective_date2);
1787
1788 open c_OrganizationLovVO(l_ret.information173, l_effective_date1);
1789 fetch c_OrganizationLovVO into l_ret.information175,l_ret.information176;
1790 close c_OrganizationLovVO;
1791 --
1792 update_mapping_target_data (
1793 p_table_route_id =>p_table_route_id
1794 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1795 , p_source_id1 =>l_ret.information174
1796 , p_target_value1 =>l_ret.information175
1797 , p_target_id1 =>l_ret.information176
1798 , p_source_id2 =>l_ret.information178
1799 , p_target_value2 =>l_ret.information179
1800 , p_target_id2 =>l_ret.information180
1801 );
1802 --
1803 end loop;
1804 elsif (l_table_route_name = 'BEN_ELIG_LOA_RSN_PRTE_F'
1805 or l_table_route_name = 'BEN_LOA_RSN_RT_F')then
1806 --
1807 for l_ret in c_source_data (p_table_route_id)
1808 loop
1809
1810 get_effective_dates(
1811 p_process_effective_date => p_effective_date
1812 ,p_start_date1 => l_ret.information166
1813 ,p_start_date2 => l_ret.information306
1814 ,p_effective_date_to_copy => p_effective_date_to_copy
1815 ,p_effective_date1 => l_effective_date1
1816 ,p_effective_date2 => l_effective_date2);
1817
1818 --
1819 open c_AttendenceTypeNameLovVO(l_ret.information173, l_effective_date1);
1820 fetch c_AttendenceTypeNameLovVO into l_ret.information175,l_ret.information176;
1821 close c_AttendenceTypeNameLovVO;
1822 --
1823 open c_AttendenceReasonNameLovVO(l_ret.information177,l_ret.information176, l_effective_date2);
1824 fetch c_AttendenceReasonNameLovVO into l_ret.information179,l_ret.information180;
1825 close c_AttendenceReasonNameLovVO;
1826 --
1827 update_mapping_target_data (
1828 p_table_route_id =>p_table_route_id
1829 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1830 , p_source_id1 =>l_ret.information174
1831 , p_target_value1 =>l_ret.information175
1832 , p_target_id1 =>l_ret.information176
1833 , p_source_id2 =>l_ret.information178
1834 , p_target_value2 =>l_ret.information179
1835 , p_target_id2 =>l_ret.information180
1836 );
1837 --
1838 end loop;
1839 elsif (l_table_route_name = 'BEN_ELIG_PER_TYP_PRTE_F'
1840 or l_table_route_name = 'BEN_PER_TYP_RT_F')then
1841 --
1842 for l_ret in c_source_data (p_table_route_id)
1843 loop
1844 --
1845 open c_PersonTypeNameLovVO(l_ret.information173);
1846 fetch c_PersonTypeNameLovVO into l_ret.information175,l_ret.information176;
1847 close c_PersonTypeNameLovVO;
1848 --
1849 update_mapping_target_data (
1850 p_table_route_id =>p_table_route_id
1851 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1852 , p_source_id1 =>l_ret.information174
1853 , p_target_value1 =>l_ret.information175
1854 , p_target_id1 =>l_ret.information176
1855 , p_source_id2 =>l_ret.information178
1856 , p_target_value2 =>l_ret.information179
1857 , p_target_id2 =>l_ret.information180
1858 );
1859 --
1860 end loop;
1861 elsif (l_table_route_name = 'BEN_ELIG_PSTN_PRTE_F'
1862 or l_table_route_name = 'BEN_PSTN_RT_F')then
1863 --
1864 for l_ret in c_source_data (p_table_route_id)
1865 loop
1866
1867 get_effective_dates(
1868 p_process_effective_date => p_effective_date
1869 ,p_start_date1 => l_ret.information166
1870 ,p_start_date2 => l_ret.information306
1871 ,p_effective_date_to_copy => p_effective_date_to_copy
1872 ,p_effective_date1 => l_effective_date1
1873 ,p_effective_date2 => l_effective_date2);
1874
1875 --
1876 open c_PositionNameLovVO(l_ret.information173, l_effective_date1);
1877 fetch c_PositionNameLovVO into l_ret.information175,l_ret.information176;
1878 close c_PositionNameLovVO;
1879 --
1880 update_mapping_target_data (
1881 p_table_route_id =>p_table_route_id
1882 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1883 , p_source_id1 =>l_ret.information174
1884 , p_target_value1 =>l_ret.information175
1885 , p_target_id1 =>l_ret.information176
1886 , p_source_id2 =>l_ret.information178
1887 , p_target_value2 =>l_ret.information179
1888 , p_target_id2 =>l_ret.information180
1889 );
1890 --
1891 end loop;
1892 elsif (l_table_route_name = 'BEN_ELIG_PYRL_PRTE_F'
1893 or l_table_route_name = 'BEN_PYRL_RT_F')then
1894 --
1895 for l_ret in c_source_data (p_table_route_id)
1896 loop
1897
1898 get_effective_dates(
1899 p_process_effective_date => p_effective_date
1900 ,p_start_date1 => l_ret.information166
1901 ,p_start_date2 => l_ret.information306
1902 ,p_effective_date_to_copy => p_effective_date_to_copy
1903 ,p_effective_date1 => l_effective_date1
1904 ,p_effective_date2 => l_effective_date2);
1905
1906 --
1907 open c_PayrollNameLovVO(l_ret.information173, l_effective_date1);
1908 fetch c_PayrollNameLovVO into l_ret.information175,l_ret.information176;
1909 close c_PayrollNameLovVO;
1910 --
1911 update_mapping_target_data (
1912 p_table_route_id =>p_table_route_id
1913 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1914 , p_source_id1 =>l_ret.information174
1915 , p_target_value1 =>l_ret.information175
1916 , p_target_id1 =>l_ret.information176
1917 , p_source_id2 =>l_ret.information178
1918 , p_target_value2 =>l_ret.information179
1919 , p_target_id2 =>l_ret.information180
1920 );
1921 --
1922 end loop;
1923 elsif (l_table_route_name = 'BEN_ELIG_PY_BSS_PRTE_F'
1924 or l_table_route_name = 'BEN_PY_BSS_RT_F')then
1925 --
1926 for l_ret in c_source_data (p_table_route_id)
1927 loop
1928 --
1929 open c_PayBasisNameLovVO(l_ret.information173);
1930 fetch c_PayBasisNameLovVO into l_ret.information175,l_ret.information176;
1931 close c_PayBasisNameLovVO;
1932 --
1933 update_mapping_target_data (
1934 p_table_route_id =>p_table_route_id
1935 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1936 , p_source_id1 =>l_ret.information174
1937 , p_target_value1 =>l_ret.information175
1938 , p_target_id1 =>l_ret.information176
1939 , p_source_id2 =>l_ret.information178
1940 , p_target_value2 =>l_ret.information179
1941 , p_target_id2 =>l_ret.information180
1942 );
1943 --
1944 end loop;
1945 elsif (l_table_route_name = 'BEN_ELIG_QUAL_TITL_PRTE_F'
1946 or l_table_route_name = 'BEN_QUAL_TITL_RT_F')then
1947 --
1948 for l_ret in c_source_data (p_table_route_id)
1949 loop
1950 --
1951 open c_QualificationNmaeLovVO(l_ret.information173);
1952 fetch c_QualificationNmaeLovVO into l_ret.information175,l_ret.information176;
1953 close c_QualificationNmaeLovVO;
1954 --
1955 update_mapping_target_data (
1956 p_table_route_id =>p_table_route_id
1957 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1958 , p_source_id1 =>l_ret.information174
1959 , p_target_value1 =>l_ret.information175
1960 , p_target_id1 =>l_ret.information176
1961 , p_source_id2 =>l_ret.information178
1962 , p_target_value2 =>l_ret.information179
1963 , p_target_id2 =>l_ret.information180
1964 );
1965 --
1966 end loop;
1967 elsif l_table_route_name = 'BEN_ELIG_SUPPL_ROLE_PRTE_F' then
1968 --
1969 for l_ret in c_source_data (p_table_route_id)
1970 loop
1971
1972 get_effective_dates(
1973 p_process_effective_date => p_effective_date
1974 ,p_start_date1 => l_ret.information166
1975 ,p_start_date2 => l_ret.information306
1976 ,p_effective_date_to_copy => p_effective_date_to_copy
1977 ,p_effective_date1 => l_effective_date1
1978 ,p_effective_date2 => l_effective_date2);
1979
1980 --
1981 open c_JobGroupIdLovVO(l_ret.information173);
1982 fetch c_JobGroupIdLovVO into l_ret.information175,l_ret.information176;
1983 close c_JobGroupIdLovVO;
1984 --
1985 open c_JobIdLovVO(l_ret.information177, l_effective_date2);
1986 fetch c_JobIdLovVO into l_ret.information179,l_ret.information180;
1987 close c_JobIdLovVO;
1988 --
1989 update_mapping_target_data (
1990 p_table_route_id =>p_table_route_id
1991 , p_copy_entity_txn_id =>p_copy_entity_txn_id
1992 , p_source_id1 =>l_ret.information174
1993 , p_target_value1 =>l_ret.information175
1994 , p_target_id1 =>l_ret.information176
1995 , p_source_id2 =>l_ret.information178
1996 , p_target_value2 =>l_ret.information179
1997 , p_target_id2 =>l_ret.information180
1998 );
1999 --
2000 end loop;
2001 elsif (l_table_route_name = 'BEN_PL_F') then
2002 --
2003 for l_ret in c_source_data (p_table_route_id)
2004 loop
2005 --
2006 open c_GroupPlanLovVO (l_ret.information173, p_effective_date);
2007 fetch c_GroupPlanLovVO into l_ret.information175,l_ret.information176;
2008 close c_GroupPlanLovVO;
2009 --
2010 update_mapping_target_data (
2011 p_table_route_id =>p_table_route_id
2012 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2013 , p_source_id1 =>l_ret.information174
2014 , p_target_value1 =>l_ret.information175
2015 , p_target_id1 =>l_ret.information176
2016 , p_source_id2 =>l_ret.information178
2017 , p_target_value2 =>l_ret.information179
2018 , p_target_id2 =>l_ret.information180
2019 );
2020 --
2021 end loop;
2022 elsif (l_table_route_name = 'BEN_OPT_F') then
2023 --
2024 for l_ret in c_source_data (p_table_route_id)
2025 loop
2026 --
2027 open c_GroupOptionIdLovVO (l_ret.information173, p_effective_date);
2028 fetch c_GroupOptionIdLovVO into l_ret.information175,l_ret.information176;
2029 close c_GroupOptionIdLovVO;
2030 --
2031 update_mapping_target_data (
2032 p_table_route_id =>p_table_route_id
2033 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2034 , p_source_id1 =>l_ret.information174
2035 , p_target_value1 =>l_ret.information175
2036 , p_target_id1 =>l_ret.information176
2037 , p_source_id2 =>l_ret.information178
2038 , p_target_value2 =>l_ret.information179
2039 , p_target_id2 =>l_ret.information180
2040 );
2041 --
2042 end loop;
2043 elsif (l_table_route_name = 'BEN_ELIG_WK_LOC_PRTE_F'
2044 or l_table_route_name = 'BEN_WK_LOC_RT_F') then
2045 --
2046 for l_ret in c_source_data (p_table_route_id)
2047 loop
2048
2049 get_effective_dates(
2050 p_process_effective_date => p_effective_date
2051 ,p_start_date1 => l_ret.information166
2052 ,p_start_date2 => l_ret.information306
2053 ,p_effective_date_to_copy => p_effective_date_to_copy
2054 ,p_effective_date1 => l_effective_date1
2055 ,p_effective_date2 => l_effective_date2);
2056
2057 --
2058 open c_LocationNameLovVO(l_ret.information173, l_effective_date1);
2059 fetch c_LocationNameLovVO into l_ret.information175,l_ret.information176;
2060 close c_LocationNameLovVO;
2061 --
2062 update_mapping_target_data (
2063 p_table_route_id =>p_table_route_id
2064 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2065 , p_source_id1 =>l_ret.information174
2066 , p_target_value1 =>l_ret.information175
2067 , p_target_id1 =>l_ret.information176
2068 , p_source_id2 =>l_ret.information178
2069 , p_target_value2 =>l_ret.information179
2070 , p_target_id2 =>l_ret.information180
2071 );
2072 --
2073 end loop;
2074 elsif (l_table_route_name = 'BEN_ENRT_PERD')then
2075 --
2076 for l_ret in c_source_data (p_table_route_id)
2077 loop
2078
2079 get_effective_dates(
2080 p_process_effective_date => p_effective_date
2081 ,p_start_date1 => l_ret.information166
2082 ,p_start_date2 => l_ret.information306
2083 ,p_effective_date_to_copy => p_effective_date_to_copy
2084 ,p_effective_date1 => l_effective_date1
2085 ,p_effective_date2 => l_effective_date2);
2086
2087 --
2088 open c_PositionStructureNameLovVO(l_ret.information173, l_effective_date1);
2089 fetch c_PositionStructureNameLovVO into l_ret.information175,l_ret.information176;
2090 close c_PositionStructureNameLovVO;
2091 --
2092 update_mapping_target_data (
2093 p_table_route_id =>p_table_route_id
2094 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2095 , p_source_id1 =>l_ret.information174
2096 , p_target_value1 =>l_ret.information175
2097 , p_target_id1 =>l_ret.information176
2098 , p_source_id2 =>l_ret.information178
2099 , p_target_value2 =>l_ret.information179
2100 , p_target_id2 =>l_ret.information180
2101 );
2102 --
2103 end loop;
2104 elsif (l_table_route_name = 'BEN_ELIG_LGL_ENTY_PRTE_F'
2105 or l_table_route_name = 'BEN_LGL_ENTY_RT_F')then
2106 --
2107 for l_ret in c_source_data (p_table_route_id)
2108 loop
2109
2110 get_effective_dates(
2111 p_process_effective_date => p_effective_date
2112 ,p_start_date1 => l_ret.information166
2113 ,p_start_date2 => l_ret.information306
2114 ,p_effective_date_to_copy => p_effective_date_to_copy
2115 ,p_effective_date1 => l_effective_date1
2116 ,p_effective_date2 => l_effective_date2);
2117
2118 --
2119 open c_LegalEntityNameLovVO(l_ret.information173, l_effective_date1);
2120 fetch c_LegalEntityNameLovVO into l_ret.information175,l_ret.information176;
2121 close c_LegalEntityNameLovVO;
2122 --
2123 update_mapping_target_data (
2124 p_table_route_id =>p_table_route_id
2125 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2126 , p_source_id1 =>l_ret.information174
2127 , p_target_value1 =>l_ret.information175
2128 , p_target_id1 =>l_ret.information176
2129 , p_source_id2 =>l_ret.information178
2130 , p_target_value2 =>l_ret.information179
2131 , p_target_id2 =>l_ret.information180
2132 );
2133 --
2134 end loop;
2135 elsif (l_table_route_name = 'BEN_POPL_ORG_F') then
2136 --
2137 for l_ret in c_source_data (p_table_route_id)
2138 loop
2139
2140 get_effective_dates(
2141 p_process_effective_date => p_effective_date
2142 ,p_start_date1 => l_ret.information166
2143 ,p_start_date2 => l_ret.information306
2144 ,p_effective_date_to_copy => p_effective_date_to_copy
2145 ,p_effective_date1 => l_effective_date1
2146 ,p_effective_date2 => l_effective_date2);
2147
2148 --
2149 open c_PoplOrganizationLovVO(l_ret.information173, l_effective_date1);
2150 fetch c_PoplOrganizationLovVO into l_ret.information175,l_ret.information176;
2151 close c_PoplOrganizationLovVO;
2152 --
2153 update_mapping_target_data (
2154 p_table_route_id =>p_table_route_id
2155 , p_copy_entity_txn_id =>p_copy_entity_txn_id
2156 , p_source_id1 =>l_ret.information174
2157 , p_target_value1 =>l_ret.information175
2158 , p_target_id1 =>l_ret.information176
2159 , p_source_id2 =>l_ret.information178
2160 , p_target_value2 =>l_ret.information179
2161 , p_target_id2 =>l_ret.information180
2162 );
2163 --
2164 end loop;
2165 end if;
2166 --
2167 hr_utility.set_location(' Leaving :'||l_proc, 10);
2168 --
2169 end auto_mapping;
2170 --
2171
2172 procedure update_mapping_target_data(
2173 p_validate in number default 0 -- false
2174 ,p_copy_entity_txn_id in number
2175 ,p_table_route_id in number
2176 ,p_source_id1 in number
2177 ,p_target_value1 in varchar2
2178 ,p_target_id1 in number
2179 ,p_source_id2 in number
2180 ,p_target_value2 in varchar2
2181 ,p_target_id2 in number
2182 ,p_business_group_id in number default hr_api.g_number
2183 ,p_effective_date in date default null
2184 ) is
2185 --
2186 cursor c_result_set(p_copy_entity_txn_id number, p_table_route_id number) is
2187 select COPY_ENTITY_RESULT_ID,object_version_number
2188 from ben_copy_entity_results
2189 where copy_entity_txn_id = p_copy_entity_txn_id
2190 and table_route_id = p_table_route_id;
2191 --
2192 l_object_version_number number;
2193 begin
2194 --
2195 /*
2196 for l_rec in c_result_set(p_copy_entity_txn_id,p_table_route_id) loop
2197 --
2198 update_plan_design_result(
2199 p_validate => p_validate
2200 ,p_copy_entity_result_id => l_rec.copy_entity_result_id
2201 ,p_copy_entity_txn_id => p_copy_entity_txn_id
2202 ,p_business_group_id => p_business_group_id
2203 ,p_object_version_number => l_rec.object_version_number
2204 ,p_effective_date => p_effective_date
2205 ,p_information175 => p_target_value1
2206 ,p_information176 => p_target_id1
2207 ,p_information179 => p_target_value2
2208 ,p_information180 => p_target_id2
2209 );
2210 --
2211 end loop;
2212 */
2213
2214 update ben_copy_entity_results
2215 set information175 = p_target_value1
2216 ,information176 = p_target_id1
2217 ,information179 = p_target_value2
2218 ,information180 = p_target_id2
2219 where copy_entity_txn_id = p_copy_entity_txn_id
2220 and table_route_id = p_table_route_id
2221 and information174 = p_source_id1
2222 and nvl(information178,-1) = nvl(p_source_id2,-1);
2223
2224 --
2225 end update_mapping_target_data;
2226 --
2227
2228 function get_mapping_info( p_mapping_info varchar2,
2229 p_table_route_id number,
2230 p_entity_txn_id number) return varchar2 is
2231 l_ret varchar2(1000) := 'No';
2232 --
2233 cursor is_maping_completed(p_entity_txn_id number,p_table_route_id number) is
2234 select cer.information175
2235 from ben_copy_entity_results cer,
2236 pqh_copy_entity_txns cet
2237 where cer.copy_entity_txn_id = p_entity_txn_id
2238 and cer.copy_entity_txn_id = cet.copy_entity_txn_id
2239 and cer.information176 is null
2240 and cer.information174 is not null
2241 and cer.NUMBER_OF_COPIES =1
2242 and cer.table_route_id = p_table_route_id
2243 and ( (cet.action_date is null) or
2244 (cer.information3 is null) or
2245 (cet.action_date is not null and
2246 information3 >= cet.action_date)
2247 );
2248
2249 --
2250 cursor c_mapping_required_table(p_table_route_id number)is
2251 select table_route_id
2252 from pqh_table_route
2253 where table_route_id = p_table_route_id
2254 and where_clause in('BEN_HRS_WKD_IN_PERD_FCTR',
2255 --'BEN_PL_F',
2256 --'BEN_OPT_F',
2257 --'BEN_POPL_ORG_F',
2258 'BEN_COMP_LVL_FCTR',
2259 'BEN_ENRT_PERD'); -- Bug No 4498668
2260 --
2261 cursor c_table_name (p_table_route_id number) is
2262 select display_name
2263 from pqh_table_route
2264 where table_route_id = p_table_route_id;
2265
2266 l_table_name pqh_table_route.display_name%type;
2267 begin
2268 if(p_mapping_info = 'CompletedInfo') then
2269 --
2270 open is_maping_completed(p_entity_txn_id ,p_table_route_id );
2271 fetch is_maping_completed into l_ret;
2272 --
2273 if is_maping_completed%found then
2274 l_ret := 'NotCompleted';
2275 else
2276 l_ret := 'Completed';
2277 end if;
2278 --
2279 close is_maping_completed;
2280 --
2281 elsif(p_mapping_info = 'RequiredInfo') then
2282 --
2283 open c_mapping_required_table(p_table_route_id);
2284 fetch c_mapping_required_table into l_ret;
2285 --
2286 if c_mapping_required_table%found then
2287 l_ret := 'Yes';
2288 end if;
2289 --
2290 close c_mapping_required_table;
2291 --
2292 elsif(p_mapping_info = 'TableNameInfo') then
2293 --
2294 open c_mapping_required_table(p_table_route_id);
2295 fetch c_mapping_required_table into l_ret;
2296 --
2297 close c_mapping_required_table;
2298 --
2299 end if;
2300 return l_ret;
2301 --
2302 end get_mapping_info;
2303
2304 --
2305
2306 procedure get_user_business_group_ids(
2307 p_user_id in number,
2308 p_business_group_ids out nocopy varchar2
2309 ) is
2310 --
2311 /* Bug 3170928 Changes
2312 cursor c_get_sec_prf_bg_id(p_user_id number) is
2313 select distinct(business_group_id) business_group_id
2314 from per_sec_profile_assignments_v
2315 where user_id = p_user_id
2316 and trunc(sysdate) between START_DATE
2317 and nvl(END_DATE, trunc(sysdate));
2318 */
2319 cursor c_get_sec_prf_bg_id(p_user_id number) is
2320 select distinct(business_group_id) business_group_id
2321 from per_sec_profile_assignments_v
2322 where user_id = p_user_id
2323 and trunc(sysdate) between START_DATE
2324 and nvl(END_DATE, trunc(sysdate))
2325 and business_group_id is not null ;
2326 /* Bug 3170928 Changes
2327 cursor c_get_user_resp_value(p_user_id number) is
2328 select optval.PROFILE_OPTION_VALUE
2329 from FND_PROFILE_OPTION_VALUES optval,
2330 fnd_profile_options_vl opt,
2331 FND_USER_RESP_GROUPS resp
2332 where opt.profile_option_id = optval.profile_option_id
2333 and optval.level_value = resp.RESPONSIBILITY_ID
2334 and resp.user_id = p_user_id
2335 and opt.profile_option_name like 'PER_BUSINESS_GROUP_ID'
2336 and trunc(sysdate) between resp.START_DATE
2337 and nvl(resp.END_DATE, trunc(sysdate));
2338 */
2339 --
2340 cursor c_get_user_resp_value(p_user_id number) is
2341 select resp.user_id,
2342 resp.responsibility_id,
2343 resp.responsibility_application_id application_id
2344 from fnd_user_resp_groups resp
2345 where resp.user_id = p_user_id;
2346 --
2347 l_business_group_id varchar2(30) ;
2348 --
2349 begin
2350 --
2351 p_business_group_ids := '''-1''';
2352 --
2353 /* Bug 3170928 Changes
2354 for l_rec in c_get_user_resp_value(p_user_id) loop
2355 --
2356 p_business_group_ids := p_business_group_ids||','''||l_rec.PROFILE_OPTION_VALUE ||'''';
2357 --
2358 end loop;
2359 */
2360 --
2361 for l_rec in c_get_user_resp_value(p_user_id) loop
2362 --
2363 p_business_group_ids := p_business_group_ids||','''||FND_PROFILE.VALUE_SPECIFIC(
2364 NAME => 'PER_BUSINESS_GROUP_ID'
2365 ,USER_ID => l_rec.user_id
2366 ,RESPONSIBILITY_ID => l_rec.responsibility_id
2367 ,APPLICATION_ID => l_rec.application_id
2368 )||'''';
2369 --
2370 end loop;
2371 --
2372 -- incase if the data base has set to access Multiple Security Group
2373 -- ( "one responsibility can attach to more than one business group")
2374 -- then the PER_BUSINESS_GROUP_ID will be null.
2375 --
2376 for l_rec in c_get_sec_prf_bg_id(p_user_id) loop
2377 --
2378 p_business_group_ids := p_business_group_ids||','''||l_rec.business_group_id ||'''';
2379 --
2380 end loop;
2381 --
2382 --
2383 end get_user_business_group_ids;
2384
2385 --
2386 procedure create_process_log
2387 (p_module_cd in varchar2
2388 ,p_txn_id in number
2389 ,p_message_text in varchar2
2390 ,p_message_type_cd in varchar2
2391 ) is
2392 begin
2393 insert into pqh_process_log
2394 ( process_log_id,
2395 module_cd,
2396 txn_id,
2397 message_text,
2398 message_type_cd,
2399 object_version_number
2400 )
2401 Values
2402 ( pqh_process_log_s.nextval,
2403 p_module_cd,
2404 p_txn_id,
2405 p_message_text,
2406 p_message_type_cd,
2407 1
2408 );
2409 end;
2410 --
2411
2412 procedure create_log
2413 ( p_copy_entity_txn_id in number
2414 ) is
2415
2416 cursor c_copy_entity_txn(c_copy_entity_txn_id in number) is
2417 select cet.process_name
2418 ,cet.src_effective_date
2419 ,cet.context_business_group_id
2420 ,cet.target_business_group_id
2421 ,cet.prefix_suffix_text
2422 ,cet.prefix_suffix_cd
2423 ,cet.reuse_object_flag
2424 ,cet.target_typ_cd
2425 ,cet.information30 source_business_group_name
2426 ,cet.action_date
2427 ,tcg.short_name
2428 from ben_copy_entity_txns_vw cet,
2429 pqh_transaction_categories tcg
2430 where cet.copy_entity_txn_id = c_copy_entity_txn_id
2431 and cet.transaction_category_id = tcg.transaction_category_id;
2432
2433 cursor c_business_group_name(c_business_group_id in number) is
2434 select name
2435 from per_business_groups
2436 where business_group_id = c_business_group_id;
2437
2438 cursor c_lookup (c_lookup_type in varchar2
2439 ,c_lookup_code in varchar2) is
2440 select meaning
2441 from hr_lookups
2442 where lookup_type = c_lookup_type
2443 and lookup_code = c_lookup_code;
2444
2445 l_icx_date_format_mask varchar2(30);
2446
2447 cursor c_run_details is
2448 select to_char(trunc(sysdate), l_icx_date_format_mask) run_date
2449 ,to_char(sysdate,'HH24:MI:SS') run_time
2450 from dual ;
2451
2452 cursor c_run_by(c_user_id in number) is
2453 select user_name
2454 from fnd_user
2455 where user_id = c_user_id;
2456
2457 l_copy_entity_txn c_copy_entity_txn%rowtype;
2458 l_target_business_group_name per_business_groups.name%type;
2459 l_reuse_option hr_lookups.meaning%type;
2460 l_prefix_suffix_option hr_lookups.meaning%type;
2461 l_run_date varchar2(50);
2462 l_run_time varchar2(50);
2463 l_run_by fnd_user.user_name%type;
2464
2465 cursor c_table_route is
2466 select table_alias
2467 ,display_name
2468 from pqh_table_route_vl trt
2469 where trt.table_alias in
2470 ('PGM', 'PLN', 'OPT', 'PTP', 'EAT'
2471 ,'BNB', 'CLF', 'HWF', 'AGF', 'LSF'
2472 ,'PFF', 'CLA', 'REG', 'BNR', 'BPP'
2473 ,'LER', 'ELP', 'DCE', 'GOS', 'BNG'
2474 ,'PDL', 'SVA', 'CPL', 'CBP', 'CPT'
2475 ,'FFF', 'ABR', 'APR', 'VPF', 'CCM'
2476 ,'ACP', 'PSL', 'EGL');
2477
2478 cursor c_cer(c_copy_entity_txn_id in number
2479 ,c_information8 in varchar2) is
2480 select information5 name
2481 from ben_copy_entity_results cer
2482 where cer.copy_entity_txn_id = c_copy_entity_txn_id
2483 and cer.number_of_copies = 1
2484 and cer.result_type_cd = 'DISPLAY'
2485 and cer.information8 = c_information8;
2486
2487 cursor c_selection_count(c_copy_entity_txn_id in number) is
2488 select count(1) from (
2489 select distinct information1,information2,information3,table_route_id
2490 from ben_copy_entity_results cer
2491 where copy_entity_txn_id = c_copy_entity_txn_id
2492 and number_of_copies = 1);
2493
2494 cursor c_copied_reused_count(c_copy_entity_txn_id in number
2495 ,c_copied_reused_type in varchar2) is
2496 select count(1) from (
2497 select distinct information1,information2,information3,table_route_id
2498 from ben_copy_entity_results
2499 where copy_entity_txn_id = c_copy_entity_txn_id
2500 and number_of_copies = 1
2501 and information9 like c_copied_reused_type);
2502
2503 cursor c_not_copied_count(c_copy_entity_txn_id in number) is
2504 select count(1) from (
2505 select distinct information1,information2,information3,table_route_id
2506 from ben_copy_entity_results
2507 where copy_entity_txn_id = c_copy_entity_txn_id
2508 and number_of_copies = 1
2509 and information9 is null);
2510
2511 cursor c_items_to_ignore_count(c_copy_entity_txn_id in number) is
2512 select count(1) from (
2513 select distinct information1,information2,information3,cer.table_route_id
2514 from ben_copy_entity_results cer
2515 -- pqh_table_route tre
2516 where copy_entity_txn_id = c_copy_entity_txn_id
2517 and number_of_copies = 1
2518 and cer.table_alias = 'EAT'
2519 -- and cer.table_route_id = tre.table_route_id
2520 );
2521
2522 l_selection_count number;
2523 l_copied_count number;
2524 l_reused_count number;
2525 l_not_copied_count number;
2526 l_items_to_ignore_count number;
2527
2528 l_pgm_label pqh_table_route_vl.display_name%type;
2529 l_pln_label pqh_table_route_vl.display_name%type;
2530 l_opt_label pqh_table_route_vl.display_name%type;
2531 l_ptp_label pqh_table_route_vl.display_name%type;
2532 l_eat_label pqh_table_route_vl.display_name%type;
2533 l_bnb_label pqh_table_route_vl.display_name%type;
2534 l_clf_label pqh_table_route_vl.display_name%type;
2535 l_hwf_label pqh_table_route_vl.display_name%type;
2536 l_agf_label pqh_table_route_vl.display_name%type;
2537 l_lsf_label pqh_table_route_vl.display_name%type;
2538 l_pff_label pqh_table_route_vl.display_name%type;
2539 l_cla_label pqh_table_route_vl.display_name%type;
2540 l_reg_label pqh_table_route_vl.display_name%type;
2541 l_bnr_label pqh_table_route_vl.display_name%type;
2542 l_bpp_label pqh_table_route_vl.display_name%type;
2543 l_ler_label pqh_table_route_vl.display_name%type;
2544 l_psl_label pqh_table_route_vl.display_name%type;
2545 l_elp_label pqh_table_route_vl.display_name%type;
2546 l_dce_label pqh_table_route_vl.display_name%type;
2547 l_gos_label pqh_table_route_vl.display_name%type;
2548 l_bng_label pqh_table_route_vl.display_name%type;
2549 l_pdl_label pqh_table_route_vl.display_name%type;
2550 l_sva_label pqh_table_route_vl.display_name%type;
2551 l_cpl_label pqh_table_route_vl.display_name%type;
2552 l_cbp_label pqh_table_route_vl.display_name%type;
2553 l_cpt_label pqh_table_route_vl.display_name%type;
2554 l_fff_label pqh_table_route_vl.display_name%type;
2555 l_abr_label pqh_table_route_vl.display_name%type;
2556 l_apr_label pqh_table_route_vl.display_name%type;
2557 l_vpf_label pqh_table_route_vl.display_name%type;
2558 l_ccm_label pqh_table_route_vl.display_name%type;
2559 l_acp_label pqh_table_route_vl.display_name%type;
2560 l_egl_label pqh_table_route_vl.display_name%type; /* Bug 4169120 Rate By Criteria */
2561
2562 l_selected_for_copy_lbl fnd_new_messages.message_text%type;
2563 l_created_objects_lbl fnd_new_messages.message_text%type;
2564 l_reused_objects_lbl fnd_new_messages.message_text%type;
2565 l_process_summary_lbl fnd_new_messages.message_text%type;
2566
2567 l_para_spacer varchar2(50) := ' ';
2568 l_single_spacer varchar2(10) := ' ';
2569
2570 l_header_start_tag varchar2(50) := '<B><U>';
2571 l_header_end_tag varchar2(50) := '</U></B>';
2572
2573 l_label_start_tag varchar2(50) := '<B><SMALL>';
2574 l_label_end_tag varchar2(50) := '</SMALL></B>';
2575
2576 l_value_start_tag varchar2(50) := '<SMALL>';
2577 l_value_end_tag varchar2(50) := '</SMALL>';
2578
2579 l_blank_line varchar2(50) := '<BR/>';
2580 l_table_start_tag varchar2(10) := '<TABLE>';
2581 l_table_end_tag varchar2(10) := '</TABLE>';
2582
2583 l_row_start_tag varchar2(10) := '<TR>';
2584 l_row_end_tag varchar2(10) := '</TR>';
2585
2586 l_cell_start_tag varchar2(10) := '<TD>';
2587 l_cell_end_tag varchar2(10) := '</TD>';
2588
2589 l_label varchar2(10) := 'LABEL';
2590 l_value varchar2(10) := 'VALUE';
2591 l_spacer varchar2(10) := 'SPACER';
2592 l_module_cd varchar2(10) := 'PDC_CP';
2593
2594 l_print_label boolean := true;
2595 begin
2596
2597 -- Update target details to ben_copy_entity_results_table
2598 update_cer_with_target(p_copy_entity_txn_id);
2599
2600 -- Bug 4317567
2601 fnd_profile.get( NAME => 'ICX_DATE_FORMAT_MASK',
2602 VAL => l_icx_date_format_mask );
2603 --
2604 l_icx_date_format_mask := nvl(l_icx_date_format_mask, 'DD/MM/YYYY');
2605 -- Bug 4317567
2606
2607 -- Fetch Copy process details
2608 open c_copy_entity_txn(p_copy_entity_txn_id);
2609 fetch c_copy_entity_txn into l_copy_entity_txn;
2610 close c_copy_entity_txn;
2611
2612 -- Fetch Target Business Group Name
2613 open c_business_group_name(l_copy_entity_txn.target_business_group_id);
2614 fetch c_business_group_name into l_target_business_group_name;
2615 close c_business_group_name;
2616
2617 -- Fetch Reuse Option
2618 open c_lookup('BEN_PD_REUSE_OBJECTS',l_copy_entity_txn.reuse_object_flag);
2619 fetch c_lookup into l_reuse_option;
2620 close c_lookup;
2621
2622 -- Fetch Prefix Suffx Option
2623 open c_lookup('BEN_PD_PREFIX_SUFFIX',l_copy_entity_txn.prefix_suffix_cd);
2624 fetch c_lookup into l_prefix_suffix_option;
2625 close c_lookup;
2626
2627 -- Fetch Run Details
2628 open c_run_details;
2629 fetch c_run_details into l_run_date,l_run_time;
2630 close c_run_details;
2631
2632 -- Fetch Run By
2633 -- Bug 4278495
2634 if ICX_SEC.G_USER_ID <> -1
2635 then
2636 -- This is the case if Log gets created through SS
2637 open c_run_by(ICX_SEC.G_USER_ID);
2638 fetch c_run_by into l_run_by;
2639 close c_run_by;
2640 else
2641 -- This is the case if Log gets created through Concurrent Program BEPDCPRC
2642 open c_run_by(FND_GLOBAL.USER_ID);
2643 fetch c_run_by into l_run_by;
2644 close c_run_by;
2645 end if;
2646 -- Fetch Rows Selected count
2647 open c_selection_count(p_copy_entity_txn_id);
2648 fetch c_selection_count into l_selection_count;
2649 close c_selection_count;
2650
2651 -- Fetch Rows Copied count
2652 open c_copied_reused_count(p_copy_entity_txn_id,'COPIED%');
2653 fetch c_copied_reused_count into l_copied_count;
2654 close c_copied_reused_count;
2655
2656 -- Fetch Rows Reused count
2657 open c_copied_reused_count(p_copy_entity_txn_id,'REUSED%');
2658 fetch c_copied_reused_count into l_reused_count;
2659 close c_copied_reused_count;
2660
2661 -- Fetch Rows Not Copied count
2662 open c_not_copied_count(p_copy_entity_txn_id);
2663 fetch c_not_copied_count into l_not_copied_count;
2664 close c_not_copied_count;
2665
2666 -- Fetch Items to Ignore count
2667 -- Currently Action Types (EAT) count needs to be reduced
2668 -- from the Selection and Reused Counts
2669 -- as we download all Action Types for the Business Group
2670 -- and mark them as Reused
2671 open c_items_to_ignore_count(p_copy_entity_txn_id);
2672 fetch c_items_to_ignore_count into l_items_to_ignore_count;
2673 close c_items_to_ignore_count;
2674
2675 l_selection_count := l_selection_count - l_items_to_ignore_count ;
2676 l_reused_count := l_reused_count - l_items_to_ignore_count;
2677
2678
2679 for r_table_route in c_table_route
2680 loop
2681 if r_table_route.table_alias = 'PGM' then
2682 l_pgm_label := r_table_route.display_name;
2683 elsif r_table_route.table_alias = 'PLN' then
2684 l_pln_label := r_table_route.display_name;
2685 elsif r_table_route.table_alias = 'OPT' then
2686 l_opt_label := r_table_route.display_name;
2687 elsif r_table_route.table_alias = 'PTP' then
2688 l_ptp_label := r_table_route.display_name;
2689 elsif r_table_route.table_alias = 'EAT' then
2690 l_eat_label := r_table_route.display_name;
2691 elsif r_table_route.table_alias = 'BNB' then
2692 l_bnb_label := r_table_route.display_name;
2693 elsif r_table_route.table_alias = 'CLF' then
2694 l_clf_label := r_table_route.display_name;
2695 elsif r_table_route.table_alias = 'HWF' then
2696 l_hwf_label := r_table_route.display_name;
2697 elsif r_table_route.table_alias = 'AGF' then
2698 l_agf_label := r_table_route.display_name;
2699 elsif r_table_route.table_alias = 'LSF' then
2700 l_lsf_label := r_table_route.display_name;
2701 elsif r_table_route.table_alias = 'PFF' then
2702 l_pff_label := r_table_route.display_name;
2703 elsif r_table_route.table_alias = 'CLA' then
2704 l_cla_label := r_table_route.display_name;
2705 elsif r_table_route.table_alias = 'REG' then
2706 l_reg_label := r_table_route.display_name;
2707 elsif r_table_route.table_alias = 'BNR' then
2708 l_bnr_label := r_table_route.display_name;
2709 elsif r_table_route.table_alias = 'BPP' then
2710 l_bpp_label := r_table_route.display_name;
2711 elsif r_table_route.table_alias = 'LER' then
2712 l_ler_label := r_table_route.display_name;
2713 elsif r_table_route.table_alias = 'PSL' then
2714 l_psl_label := r_table_route.display_name;
2715 elsif r_table_route.table_alias = 'ELP' then
2716 l_elp_label := r_table_route.display_name;
2717 elsif r_table_route.table_alias = 'DCE' then
2718 l_dce_label := r_table_route.display_name;
2719 elsif r_table_route.table_alias = 'GOS' then
2720 l_gos_label := r_table_route.display_name;
2721 elsif r_table_route.table_alias = 'BNG' then
2722 l_bng_label := r_table_route.display_name;
2723 elsif r_table_route.table_alias = 'PDL' then
2724 l_pdl_label := r_table_route.display_name;
2725 elsif r_table_route.table_alias = 'SVA' then
2726 l_sva_label := r_table_route.display_name;
2727 elsif r_table_route.table_alias = 'CPL' then
2728 l_cpl_label := r_table_route.display_name;
2729 elsif r_table_route.table_alias = 'CBP' then
2730 l_cbp_label := r_table_route.display_name;
2731 elsif r_table_route.table_alias = 'CPT' then
2732 l_cpt_label := r_table_route.display_name;
2733 elsif r_table_route.table_alias = 'FFF' then
2734 l_fff_label := r_table_route.display_name;
2735 elsif r_table_route.table_alias = 'ABR' then
2736 l_abr_label := r_table_route.display_name;
2737 elsif r_table_route.table_alias = 'APR' then
2738 l_apr_label := r_table_route.display_name;
2739 elsif r_table_route.table_alias = 'VPF' then
2740 l_vpf_label := r_table_route.display_name;
2741 elsif r_table_route.table_alias = 'CCM' then
2742 l_ccm_label := r_table_route.display_name;
2743 elsif r_table_route.table_alias = 'ACP' then
2744 l_acp_label := r_table_route.display_name;
2745 elsif r_table_route.table_alias = 'EGL' then /* Bug 4169120 : Rate By Criteria */
2746 l_egl_label := r_table_route.display_name;
2747 end if;
2748 end loop;
2749
2750 -- Delete Old Log Data
2751 delete from pqh_process_log
2752 where txn_id = p_copy_entity_txn_id
2753 and module_cd = 'PDC_CP';
2754
2755 -- Insert Summary information
2756
2757 -- Insert Process Summary
2758 l_process_summary_lbl := l_header_start_tag
2759 ||fnd_message.get_string('BEN','BEN_93269_PDC_PROCESS_SUMMARY')
2760 ||l_header_end_tag;
2761
2762 create_process_log
2763 (p_module_cd => l_module_cd
2764 ,p_txn_id => p_copy_entity_txn_id
2765 ,p_message_text => l_process_summary_lbl
2766 ,p_message_type_cd => l_label
2767 );
2768
2769 -- Create Spacer Line
2770 create_process_log
2771 (p_module_cd => l_module_cd
2772 ,p_txn_id => p_copy_entity_txn_id
2773 ,p_message_text => l_blank_line
2774 ,p_message_type_cd => l_spacer
2775 );
2776
2777 create_process_log
2778 (p_module_cd => l_module_cd
2779 ,p_txn_id => p_copy_entity_txn_id
2780 ,p_message_text => l_table_start_tag ||l_row_start_tag
2781 ||l_cell_start_tag||l_value_start_tag
2782 ||fnd_message.get_string('BEN','BEN_93270_PDC_PROCESS_NAME')
2783 ||l_value_end_tag||l_cell_end_tag
2784 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2785 ||l_cell_start_tag||l_value_start_tag||l_copy_entity_txn.process_name
2786 ||l_value_end_tag||l_cell_end_tag
2787 ||l_row_end_tag
2788 ,p_message_type_cd => l_label
2789 );
2790
2791 create_process_log
2792 (p_module_cd => l_module_cd
2793 ,p_txn_id => p_copy_entity_txn_id
2794 ,p_message_text => l_row_start_tag
2795 ||l_cell_start_tag||l_value_start_tag
2796 ||fnd_message.get_string('BEN','BEN_93271_PDC_EFFECTIVE_DATE')
2797 ||l_value_end_tag||l_cell_end_tag
2798 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2799 ||l_cell_start_tag||l_value_start_tag||to_char(l_copy_entity_txn.src_effective_date, l_icx_date_format_mask)
2800 ||l_value_end_tag||l_cell_end_tag
2801 ||l_row_end_tag
2802 ,p_message_type_cd => l_label
2803 );
2804
2805 -- Hide for Plan Design Wizard
2806 IF l_copy_entity_txn.short_name <> 'BEN_PDCRWZ' THEN
2807
2808 create_process_log
2809 (p_module_cd => l_module_cd
2810 ,p_txn_id => p_copy_entity_txn_id
2811 ,p_message_text => l_row_start_tag
2812 ||l_cell_start_tag||l_value_start_tag
2813 ||fnd_message.get_string('BEN','BEN_93272_PDC_SRC_BUSINESS_GRP')
2814 ||l_value_end_tag||l_cell_end_tag
2815 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2816 ||l_cell_start_tag||l_value_start_tag||l_copy_entity_txn.source_business_group_name
2817 ||l_value_end_tag||l_cell_end_tag
2818 ||l_row_end_tag
2819 ,p_message_type_cd => l_label
2820 );
2821
2822 if l_copy_entity_txn.target_typ_cd = 'BEN_PDSMBG' then
2823 create_process_log
2824 (p_module_cd => l_module_cd
2825 ,p_txn_id => p_copy_entity_txn_id
2826 ,p_message_text => l_row_start_tag
2827 ||l_cell_start_tag||l_value_start_tag
2828 ||fnd_message.get_string('BEN','BEN_93273_PDC_TGT_BUSINESS_GRP')
2829 ||l_value_end_tag||l_cell_end_tag
2830 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2831 ||l_cell_start_tag||l_value_start_tag||l_copy_entity_txn.source_business_group_name
2832 ||l_value_end_tag||l_cell_end_tag
2833 ||l_row_end_tag
2834 ,p_message_type_cd => l_label
2835 );
2836 else
2837 create_process_log
2838 (p_module_cd => l_module_cd
2839 ,p_txn_id => p_copy_entity_txn_id
2840 ,p_message_text => l_row_start_tag
2841 ||l_cell_start_tag||l_value_start_tag
2842 ||fnd_message.get_string('BEN','BEN_93273_PDC_TGT_BUSINESS_GRP')
2843 ||l_value_end_tag||l_cell_end_tag
2844 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2845 ||l_cell_start_tag||l_value_start_tag||l_target_business_group_name
2846 ||l_value_end_tag||l_cell_end_tag
2847 ||l_row_end_tag
2848 ,p_message_type_cd => l_label
2849 );
2850 end if;
2851
2852 ELSE
2853 -- For Plan Design Wizard
2854
2855 create_process_log
2856 (p_module_cd => l_module_cd
2857 ,p_txn_id => p_copy_entity_txn_id
2858 ,p_message_text => l_row_start_tag
2859 ||l_cell_start_tag||l_value_start_tag
2860 ||fnd_message.get_string('BEN','BEN_93809_PDC_BUSINESS_GROUP')
2861 ||l_value_end_tag||l_cell_end_tag
2862 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2863 ||l_cell_start_tag||l_value_start_tag||l_target_business_group_name
2864 ||l_value_end_tag||l_cell_end_tag
2865 ||l_row_end_tag
2866 ,p_message_type_cd => l_label
2867 );
2868
2869 END IF;
2870
2871
2872 -- Hide for Plan Design Wizard
2873 IF l_copy_entity_txn.short_name <> 'BEN_PDCRWZ' THEN
2874
2875 if l_copy_entity_txn.action_date is not null then
2876 create_process_log
2877 (p_module_cd => l_module_cd
2878 ,p_txn_id => p_copy_entity_txn_id
2879 ,p_message_text => l_row_start_tag
2880 ||l_cell_start_tag||l_value_start_tag
2881 ||fnd_message.get_string('BEN','BEN_93422_PDC_EFF_DATE_TO_COPY')
2882 ||l_value_end_tag||l_cell_end_tag
2883 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2884 ||l_cell_start_tag||l_value_start_tag|| to_char(l_copy_entity_txn.action_date, l_icx_date_format_mask)
2885 ||l_value_end_tag||l_cell_end_tag
2886 ||l_row_end_tag
2887 ,p_message_type_cd => l_label
2888 );
2889
2890 end if;
2891
2892 create_process_log
2893 (p_module_cd => l_module_cd
2894 ,p_txn_id => p_copy_entity_txn_id
2895 ,p_message_text => l_row_start_tag
2896 ||l_cell_start_tag||l_value_start_tag
2897 ||fnd_message.get_string('BEN','BEN_93274_PDC_REUSE_OPTION')
2898 ||l_value_end_tag||l_cell_end_tag
2899 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2900 ||l_cell_start_tag||l_value_start_tag
2901 ||l_reuse_option
2902 ||l_value_end_tag||l_cell_end_tag
2903 ||l_row_end_tag
2904 ,p_message_type_cd => l_label
2905 );
2906
2907 create_process_log
2908 (p_module_cd => l_module_cd
2909 ,p_txn_id => p_copy_entity_txn_id
2910 ,p_message_text => l_row_start_tag
2911 ||l_cell_start_tag||l_value_start_tag
2912 ||fnd_message.get_string('BEN','BEN_93275_PDC_PREFIX_SUFFIX')
2913 ||l_value_end_tag||l_cell_end_tag
2914 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2915 ||l_cell_start_tag||l_value_start_tag
2916 ||l_copy_entity_txn.prefix_suffix_text
2917 ||l_value_end_tag||l_cell_end_tag
2918 ||l_row_end_tag
2919 ,p_message_type_cd => l_label
2920 );
2921
2922 create_process_log
2923 (p_module_cd => l_module_cd
2924 ,p_txn_id => p_copy_entity_txn_id
2925 ,p_message_text => l_row_start_tag
2926 ||l_cell_start_tag||l_value_start_tag
2927 ||fnd_message.get_string('BEN','BEN_93276_PDC_ROWS_SELECTED')
2928 ||l_value_end_tag||l_cell_end_tag
2929 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2930 ||l_cell_start_tag||l_value_start_tag
2931 ||l_selection_count
2932 ||l_value_end_tag||l_cell_end_tag
2933 ||l_row_end_tag
2934 ,p_message_type_cd => l_label
2935 );
2936
2937 create_process_log
2938 (p_module_cd => l_module_cd
2939 ,p_txn_id => p_copy_entity_txn_id
2940 ,p_message_text => l_row_start_tag
2941 ||l_cell_start_tag||l_value_start_tag
2942 ||fnd_message.get_string('BEN','BEN_93277_PDC_ROWS_CREATED')
2943 ||l_value_end_tag||l_cell_end_tag
2944 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2945 ||l_cell_start_tag||l_value_start_tag
2946 ||l_copied_count
2947 ||l_value_end_tag||l_cell_end_tag
2948 ||l_row_end_tag
2949 ,p_message_type_cd => l_label
2950 );
2951
2952 create_process_log
2953 (p_module_cd => l_module_cd
2954 ,p_txn_id => p_copy_entity_txn_id
2955 ,p_message_text => l_row_start_tag
2956 ||l_cell_start_tag||l_value_start_tag
2957 ||fnd_message.get_string('BEN','BEN_93278_PDC_ROWS_REUSED')
2958 ||l_value_end_tag||l_cell_end_tag
2959 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2960 ||l_cell_start_tag||l_value_start_tag
2961 ||l_reused_count
2962 ||l_value_end_tag||l_cell_end_tag
2963 ||l_row_end_tag
2964 ,p_message_type_cd => l_label
2965 );
2966
2967 create_process_log
2968 (p_module_cd => l_module_cd
2969 ,p_txn_id => p_copy_entity_txn_id
2970 ,p_message_text => l_row_start_tag
2971 ||l_cell_start_tag||l_value_start_tag
2972 ||fnd_message.get_string('BEN','BEN_93279_PDC_ROWS_NOT_COPIED')
2973 ||l_value_end_tag||l_cell_end_tag
2974 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2975 ||l_cell_start_tag||l_value_start_tag
2976 ||l_not_copied_count
2977 ||l_value_end_tag||l_cell_end_tag
2978 ||l_row_end_tag
2979 ,p_message_type_cd => l_label
2980 );
2981
2982 END IF;
2983
2984 create_process_log
2985 (p_module_cd => l_module_cd
2986 ,p_txn_id => p_copy_entity_txn_id
2987 ,p_message_text => l_row_start_tag
2988 ||l_cell_start_tag||l_value_start_tag
2989 ||fnd_message.get_string('BEN','BEN_93280_PDC_RUN_DATE')
2990 ||l_value_end_tag||l_cell_end_tag
2991 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
2992 ||l_cell_start_tag||l_value_start_tag
2993 ||l_run_date
2994 ||l_value_end_tag||l_cell_end_tag
2995 ||l_row_end_tag
2996 ,p_message_type_cd => l_label
2997 );
2998
2999 create_process_log
3000 (p_module_cd => l_module_cd
3001 ,p_txn_id => p_copy_entity_txn_id
3002 ,p_message_text => l_row_start_tag
3003 ||l_cell_start_tag||l_value_start_tag
3004 ||fnd_message.get_string('BEN','BEN_93281_PDC_RUN_TIME')
3005 ||l_value_end_tag||l_cell_end_tag
3006 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
3007 ||l_cell_start_tag||l_value_start_tag
3008 ||l_run_time
3009 ||l_value_end_tag||l_cell_end_tag
3010 ||l_row_end_tag
3011 ,p_message_type_cd => l_label
3012 );
3013
3014 create_process_log
3015 (p_module_cd => l_module_cd
3016 ,p_txn_id => p_copy_entity_txn_id
3017 ,p_message_text => l_row_start_tag
3018 ||l_cell_start_tag||l_value_start_tag
3019 ||fnd_message.get_string('BEN','BEN_93282_PDC_RUN_BY')
3020 ||l_value_end_tag||l_cell_end_tag
3021 ||l_cell_start_tag||l_single_spacer||l_cell_end_tag
3022 ||l_cell_start_tag||l_value_start_tag
3023 ||l_run_by
3024 ||l_value_end_tag||l_cell_end_tag
3025 ||l_row_end_tag || l_table_end_tag
3026 ,p_message_type_cd => l_label
3027 );
3028
3029 -- Create Spacer Line
3030 create_process_log
3031 (p_module_cd => l_module_cd
3032 ,p_txn_id => p_copy_entity_txn_id
3033 ,p_message_text => l_blank_line
3034 ,p_message_type_cd => l_spacer
3035 );
3036
3037 -- Hide for Plan Design Wizard
3038 IF l_copy_entity_txn.short_name <> 'BEN_PDCRWZ' THEN
3039
3040 -- Insert Selected for Copy
3041 l_selected_for_copy_lbl := l_header_start_tag
3042 ||fnd_message.get_string('BEN','BEN_93283_PDC_SLCTD_FOR_COPY')
3043 ||l_header_end_tag;
3044
3045 create_process_log
3046 (p_module_cd => l_module_cd
3047 ,p_txn_id => p_copy_entity_txn_id
3048 ,p_message_text => l_selected_for_copy_lbl
3049 ,p_message_type_cd => l_label
3050 );
3051
3052
3053 -- Create Spacer Line
3054 create_process_log
3055 (p_module_cd => l_module_cd
3056 ,p_txn_id => p_copy_entity_txn_id
3057 ,p_message_text => l_blank_line||l_blank_line
3058 ,p_message_type_cd => l_spacer
3059 );
3060
3061
3062 l_print_label := true;
3063 for r_cer in c_cer(p_copy_entity_txn_id,'PGM') loop
3064 if l_print_label = true then
3065 -- Programs
3066 create_process_log
3067 (p_module_cd => l_module_cd
3068 ,p_txn_id => p_copy_entity_txn_id
3069 ,p_message_text => l_label_start_tag||l_pgm_label||l_label_end_tag||l_blank_line
3070 ,p_message_type_cd => l_label
3071 );
3072 l_print_label := false;
3073 end if;
3074
3075 create_process_log
3076 (p_module_cd => l_module_cd
3077 ,p_txn_id => p_copy_entity_txn_id
3078 ,p_message_text => l_value_start_tag ||l_para_spacer || r_cer.name ||l_value_end_tag||l_blank_line
3079 ,p_message_type_cd => l_value
3080 );
3081 end loop;
3082
3083 l_print_label := true;
3084 for r_cer in c_cer(p_copy_entity_txn_id,'PLNIP') loop
3085 if l_print_label = true then
3086 -- Plans
3087 create_process_log
3088 (p_module_cd => l_module_cd
3089 ,p_txn_id => p_copy_entity_txn_id
3090 ,p_message_text => l_label_start_tag||l_pln_label||l_label_end_tag||l_blank_line
3091 ,p_message_type_cd => l_label
3092 );
3093 l_print_label := false;
3094 end if;
3095
3096 create_process_log
3097 (p_module_cd => l_module_cd
3098 ,p_txn_id => p_copy_entity_txn_id
3099 ,p_message_text => l_value_start_tag ||l_para_spacer || r_cer.name ||l_value_end_tag||l_blank_line
3100 ,p_message_type_cd => l_value
3101 );
3102 end loop;
3103
3104 -- Create Spacer Line
3105 create_process_log
3106 (p_module_cd => l_module_cd
3107 ,p_txn_id => p_copy_entity_txn_id
3108 ,p_message_text => l_blank_line
3109 ,p_message_type_cd => l_spacer
3110 );
3111
3112 END IF;
3113
3114 -- Insert Created the Following Objects
3115 l_created_objects_lbl := l_header_start_tag
3116 ||fnd_message.get_string('BEN','BEN_93284_PDC_CREATED_OBJECTS')
3117 ||l_header_end_tag;
3118
3119 create_process_log
3120 (p_module_cd => l_module_cd
3121 ,p_txn_id => p_copy_entity_txn_id
3122 ,p_message_text => l_created_objects_lbl
3123 ,p_message_type_cd => l_label
3124 );
3125
3126 -- Create Spacer Line
3127 create_process_log
3128 (p_module_cd => l_module_cd
3129 ,p_txn_id => p_copy_entity_txn_id
3130 ,p_message_text => l_blank_line||l_blank_line
3131 ,p_message_type_cd => l_spacer
3132 );
3133
3134
3135 -- Programs
3136 if ben_pd_copy_to_ben_one.g_pgm_tbl_copied_count > 0 then
3137 create_process_log
3138 (p_module_cd => l_module_cd
3139 ,p_txn_id => p_copy_entity_txn_id
3140 ,p_message_text => l_label_start_tag||l_pgm_label||l_label_end_tag||l_blank_line
3141 ,p_message_type_cd => l_label
3142 );
3143
3144 for i in ben_pd_copy_to_ben_one.g_pgm_tbl_copied.first .. ben_pd_copy_to_ben_one.g_pgm_tbl_copied.last
3145 loop
3146 create_process_log
3147 (p_module_cd => l_module_cd
3148 ,p_txn_id => p_copy_entity_txn_id
3149 ,p_message_text => l_value_start_tag ||l_para_spacer
3150 ||ben_pd_copy_to_ben_one.g_pgm_tbl_copied(i).new_name
3151 || l_value_end_tag||l_blank_line
3152 ,p_message_type_cd => l_value
3153 );
3154 end loop;
3155 end if;
3156
3157 -- Plan Types
3158 if ben_pd_copy_to_ben_one.g_ptp_tbl_copied_count > 0 then
3159 create_process_log
3160 (p_module_cd => l_module_cd
3161 ,p_txn_id => p_copy_entity_txn_id
3162 ,p_message_text => l_label_start_tag||l_ptp_label||l_label_end_tag||l_blank_line
3163 ,p_message_type_cd => l_label
3164 );
3165
3166 for i in ben_pd_copy_to_ben_one.g_ptp_tbl_copied.first .. ben_pd_copy_to_ben_one.g_ptp_tbl_copied.last
3167 loop
3168 create_process_log
3169 (p_module_cd => l_module_cd
3170 ,p_txn_id => p_copy_entity_txn_id
3171 ,p_message_text => l_value_start_tag ||l_para_spacer
3172 ||ben_pd_copy_to_ben_one.g_ptp_tbl_copied(i).new_name
3173 ||l_value_end_tag||l_blank_line
3174 ,p_message_type_cd => l_value
3175 );
3176 end loop;
3177 end if;
3178
3179 -- Plans
3180 if ben_pd_copy_to_ben_one.g_pln_tbl_copied_count > 0 then
3181 create_process_log
3182 (p_module_cd => l_module_cd
3183 ,p_txn_id => p_copy_entity_txn_id
3184 ,p_message_text => l_label_start_tag||l_pln_label||l_label_end_tag||l_blank_line
3185 ,p_message_type_cd => l_label
3186 );
3187
3188 for i in ben_pd_copy_to_ben_one.g_pln_tbl_copied.first .. ben_pd_copy_to_ben_one.g_pln_tbl_copied.last
3189 loop
3190 create_process_log
3191 (p_module_cd => l_module_cd
3192 ,p_txn_id => p_copy_entity_txn_id
3193 ,p_message_text => l_value_start_tag ||l_para_spacer
3194 ||ben_pd_copy_to_ben_one.g_pln_tbl_copied(i).new_name
3195 ||l_value_end_tag||l_blank_line
3196 ,p_message_type_cd => l_value
3197 );
3198 end loop;
3199 end if;
3200
3201 -- Options
3202 if ben_pd_copy_to_ben_one.g_opt_tbl_copied_count > 0 then
3203 create_process_log
3204 (p_module_cd => l_module_cd
3205 ,p_txn_id => p_copy_entity_txn_id
3206 ,p_message_text => l_label_start_tag||l_opt_label||l_label_end_tag||l_blank_line
3207 ,p_message_type_cd => l_label
3208 );
3209
3210 for i in ben_pd_copy_to_ben_one.g_opt_tbl_copied.first .. ben_pd_copy_to_ben_one.g_opt_tbl_copied.last
3211 loop
3212 create_process_log
3213 (p_module_cd => l_module_cd
3214 ,p_txn_id => p_copy_entity_txn_id
3215 ,p_message_text => l_value_start_tag ||l_para_spacer
3216 ||ben_pd_copy_to_ben_one.g_opt_tbl_copied(i).new_name
3217 ||l_value_end_tag||l_blank_line
3218 ,p_message_type_cd => l_value
3219 );
3220 end loop;
3221 end if;
3222
3223 -- Rates
3224 if ben_pd_copy_to_ben_one.g_abr_tbl_copied_count > 0 then
3225 create_process_log
3226 (p_module_cd => l_module_cd
3227 ,p_txn_id => p_copy_entity_txn_id
3228 ,p_message_text => l_label_start_tag||l_abr_label||l_label_end_tag||l_blank_line
3229 ,p_message_type_cd => l_label
3230 );
3231
3232
3233 for i in ben_pd_copy_to_ben_one.g_abr_tbl_copied.first .. ben_pd_copy_to_ben_one.g_abr_tbl_copied.last
3234 loop
3235 create_process_log
3236 (p_module_cd => l_module_cd
3237 ,p_txn_id => p_copy_entity_txn_id
3238 ,p_message_text => l_value_start_tag ||l_para_spacer
3239 ||ben_pd_copy_to_ben_one.g_abr_tbl_copied(i).new_name
3240 ||l_value_end_tag||l_blank_line
3241 ,p_message_type_cd => l_value
3242 );
3243 end loop;
3244
3245 --Added for Bug 6881417
3246 if( nvl(length(ben_abr_bus.g_ssben_var),0) > 0) then
3247 ben_abr_bus.g_ssben_var := substr(ben_abr_bus.g_ssben_var,0,length(ben_abr_bus.g_ssben_var)-1);
3248 create_process_log
3249 (p_module_cd => l_module_cd
3250 ,p_txn_id => p_copy_entity_txn_id
3251 ,p_message_text => l_value_start_tag ||l_para_spacer
3252 ||'Element and Input Value not copied for Rate Defintion(s) '||ben_abr_bus.g_ssben_var
3253 ||l_value_end_tag||l_blank_line
3254 ,p_message_type_cd => l_value
3255 );
3256 end if;
3257 --End of Code for Bug 6881417
3258
3259 end if;
3260
3261 -- Benefit Pools
3262 if ben_pd_copy_to_ben_one.g_bpp_tbl_copied_count > 0 then
3263 create_process_log
3264 (p_module_cd => l_module_cd
3265 ,p_txn_id => p_copy_entity_txn_id
3266 ,p_message_text => l_label_start_tag||l_bpp_label||l_label_end_tag||l_blank_line
3267 ,p_message_type_cd => l_label
3268 );
3269
3270 for i in ben_pd_copy_to_ben_one.g_bpp_tbl_copied.first .. ben_pd_copy_to_ben_one.g_bpp_tbl_copied.last
3271 loop
3272 create_process_log
3273 (p_module_cd => l_module_cd
3274 ,p_txn_id => p_copy_entity_txn_id
3275 ,p_message_text => l_value_start_tag ||l_para_spacer
3276 ||ben_pd_copy_to_ben_one.g_bpp_tbl_copied(i).new_name
3277 ||l_value_end_tag||l_blank_line
3278 ,p_message_type_cd => l_value
3279 );
3280 end loop;
3281 end if;
3282
3283 -- Actual Premiums
3284 if ben_pd_copy_to_ben_one.g_apr_tbl_copied_count > 0 then
3285 create_process_log
3286 (p_module_cd => l_module_cd
3287 ,p_txn_id => p_copy_entity_txn_id
3288 ,p_message_text => l_label_start_tag||l_apr_label||l_label_end_tag||l_blank_line
3289 ,p_message_type_cd => l_label
3290 );
3291
3292 for i in ben_pd_copy_to_ben_one.g_apr_tbl_copied.first .. ben_pd_copy_to_ben_one.g_apr_tbl_copied.last
3293 loop
3294 create_process_log
3295 (p_module_cd => l_module_cd
3296 ,p_txn_id => p_copy_entity_txn_id
3297 ,p_message_text => l_value_start_tag ||l_para_spacer
3298 ||ben_pd_copy_to_ben_one.g_apr_tbl_copied(i).new_name
3299 ||l_value_end_tag||l_blank_line
3300 ,p_message_type_cd => l_value
3301 );
3302 end loop;
3303 end if;
3304
3305 -- Coverages
3306 if ben_pd_copy_to_ben_one.g_ccm_tbl_copied_count > 0 then
3307 create_process_log
3308 (p_module_cd => l_module_cd
3309 ,p_txn_id => p_copy_entity_txn_id
3310 ,p_message_text => l_label_start_tag||l_ccm_label||l_label_end_tag||l_blank_line
3311 ,p_message_type_cd => l_label
3312 );
3313
3314 for i in ben_pd_copy_to_ben_one.g_ccm_tbl_copied.first .. ben_pd_copy_to_ben_one.g_ccm_tbl_copied.last
3315 loop
3316 create_process_log
3317 (p_module_cd => l_module_cd
3318 ,p_txn_id => p_copy_entity_txn_id
3319 ,p_message_text => l_value_start_tag ||l_para_spacer
3320 ||ben_pd_copy_to_ben_one.g_ccm_tbl_copied(i).new_name
3321 ||l_value_end_tag||l_blank_line
3322 ,p_message_type_cd => l_value
3323 );
3324 end loop;
3325 end if;
3326
3327 -- Coverage across Plan Types
3328 if ben_pd_copy_to_ben_one.g_acp_tbl_copied_count > 0 then
3329 create_process_log
3330 (p_module_cd => l_module_cd
3331 ,p_txn_id => p_copy_entity_txn_id
3332 ,p_message_text => l_label_start_tag||l_acp_label||l_label_end_tag||l_blank_line
3333 ,p_message_type_cd => l_label
3334 );
3335
3336 for i in ben_pd_copy_to_ben_one.g_acp_tbl_copied.first .. ben_pd_copy_to_ben_one.g_acp_tbl_copied.last
3337 loop
3338 create_process_log
3339 (p_module_cd => l_module_cd
3340 ,p_txn_id => p_copy_entity_txn_id
3341 ,p_message_text => l_value_start_tag ||l_para_spacer
3342 ||ben_pd_copy_to_ben_one.g_acp_tbl_copied(i).new_name
3343 ||l_value_end_tag||l_blank_line
3344 ,p_message_type_cd => l_value
3345 );
3346 end loop;
3347 end if;
3348
3349
3350 -- Life Events
3351 if ben_pd_copy_to_ben_one.g_ler_tbl_copied_count > 0 then
3352 create_process_log
3353 (p_module_cd => l_module_cd
3354 ,p_txn_id => p_copy_entity_txn_id
3355 ,p_message_text => l_label_start_tag||l_ler_label||l_label_end_tag||l_blank_line
3356 ,p_message_type_cd => l_label
3357 );
3358
3359 for i in ben_pd_copy_to_ben_one.g_ler_tbl_copied.first .. ben_pd_copy_to_ben_one.g_ler_tbl_copied.last
3360 loop
3361 create_process_log
3362 (p_module_cd => l_module_cd
3363 ,p_txn_id => p_copy_entity_txn_id
3364 ,p_message_text => l_value_start_tag ||l_para_spacer
3365 ||ben_pd_copy_to_ben_one.g_ler_tbl_copied(i).new_name
3366 ||l_value_end_tag||l_blank_line
3367 ,p_message_type_cd => l_value
3368 );
3369 end loop;
3370 end if;
3371
3372 -- Life Events(Person Change)
3373 if ben_pd_copy_to_ben_one.g_psl_tbl_copied_count > 0 then
3374 create_process_log
3375 (p_module_cd => l_module_cd
3376 ,p_txn_id => p_copy_entity_txn_id
3377 ,p_message_text => l_label_start_tag||l_psl_label||l_label_end_tag||l_blank_line
3378 ,p_message_type_cd => l_label
3379 );
3380
3381 for i in ben_pd_copy_to_ben_one.g_psl_tbl_copied.first .. ben_pd_copy_to_ben_one.g_psl_tbl_copied.last
3382 loop
3383 create_process_log
3384 (p_module_cd => l_module_cd
3385 ,p_txn_id => p_copy_entity_txn_id
3386 ,p_message_text => l_value_start_tag ||l_para_spacer
3387 ||ben_pd_copy_to_ben_one.g_psl_tbl_copied(i).new_name
3388 ||l_value_end_tag||l_blank_line
3389 ,p_message_type_cd => l_value
3390 );
3391 end loop;
3392 end if;
3393
3394 -- Eligibility profiles
3395 if ben_pd_copy_to_ben_one.g_elp_tbl_copied_count > 0 then
3396 create_process_log
3397 (p_module_cd => l_module_cd
3398 ,p_txn_id => p_copy_entity_txn_id
3399 ,p_message_text => l_label_start_tag||l_elp_label||l_label_end_tag||l_blank_line
3400 ,p_message_type_cd => l_label
3401 );
3402
3403 for i in ben_pd_copy_to_ben_one.g_elp_tbl_copied.first .. ben_pd_copy_to_ben_one.g_elp_tbl_copied.last
3404 loop
3405 create_process_log
3406 (p_module_cd => l_module_cd
3407 ,p_txn_id => p_copy_entity_txn_id
3408 ,p_message_text => l_value_start_tag ||l_para_spacer
3409 ||ben_pd_copy_to_ben_one.g_elp_tbl_copied(i).new_name
3410 ||l_value_end_tag||l_blank_line
3411 ,p_message_type_cd => l_value
3412 );
3413 end loop;
3414 end if;
3415
3416 -- Dependent Eligibility profiles
3417 if ben_pd_copy_to_ben_one.g_dce_tbl_copied_count > 0 then
3418 create_process_log
3419 (p_module_cd => l_module_cd
3420 ,p_txn_id => p_copy_entity_txn_id
3421 ,p_message_text => l_label_start_tag||l_dce_label||l_label_end_tag||l_blank_line
3422 ,p_message_type_cd => l_label
3423 );
3424
3425 for i in ben_pd_copy_to_ben_one.g_dce_tbl_copied.first .. ben_pd_copy_to_ben_one.g_dce_tbl_copied.last
3426 loop
3427 create_process_log
3428 (p_module_cd => l_module_cd
3429 ,p_txn_id => p_copy_entity_txn_id
3430 ,p_message_text => l_value_start_tag ||l_para_spacer
3431 ||ben_pd_copy_to_ben_one.g_dce_tbl_copied(i).new_name
3432 ||l_value_end_tag||l_blank_line
3433 ,p_message_type_cd => l_value
3434 );
3435 end loop;
3436 end if;
3437
3438 -- Variable Rate Profiles
3439 if ben_pd_copy_to_ben_one.g_vpf_tbl_copied_count > 0 then
3440 create_process_log
3441 (p_module_cd => l_module_cd
3442 ,p_txn_id => p_copy_entity_txn_id
3443 ,p_message_text => l_label_start_tag||l_vpf_label||l_label_end_tag||l_blank_line
3444 ,p_message_type_cd => l_label
3445 );
3446
3447 for i in ben_pd_copy_to_ben_one.g_vpf_tbl_copied.first .. ben_pd_copy_to_ben_one.g_vpf_tbl_copied.last
3448 loop
3449 create_process_log
3450 (p_module_cd => l_module_cd
3451 ,p_txn_id => p_copy_entity_txn_id
3452 ,p_message_text => l_value_start_tag ||l_para_spacer
3453 ||ben_pd_copy_to_ben_one.g_vpf_tbl_copied(i).new_name
3454 ||l_value_end_tag||l_blank_line
3455 ,p_message_type_cd => l_value
3456 );
3457 end loop;
3458 end if;
3459 --
3460 -- Bug 4169120 : Rate By Criteria
3461 -- Eligibility Criteria
3462 --
3463 if ben_pd_copy_to_ben_one.g_egl_tbl_copied_count > 0 then
3464 create_process_log
3465 (p_module_cd => l_module_cd
3466 ,p_txn_id => p_copy_entity_txn_id
3467 ,p_message_text => l_label_start_tag||l_egl_label||l_label_end_tag||l_blank_line
3468 ,p_message_type_cd => l_label
3469 );
3470
3471 for i in ben_pd_copy_to_ben_one.g_egl_tbl_copied.first .. ben_pd_copy_to_ben_one.g_egl_tbl_copied.last
3472 loop
3473 create_process_log
3474 (p_module_cd => l_module_cd
3475 ,p_txn_id => p_copy_entity_txn_id
3476 ,p_message_text => l_value_start_tag ||l_para_spacer
3477 ||ben_pd_copy_to_ben_one.g_egl_tbl_copied(i).new_name
3478 ||l_value_end_tag||l_blank_line
3479 ,p_message_type_cd => l_value
3480 );
3481 end loop;
3482 end if;
3483 --
3484 -- Benefit Balances
3485 if ben_pd_copy_to_ben_one.g_bnb_tbl_copied_count > 0 then
3486 create_process_log
3487 (p_module_cd => l_module_cd
3488 ,p_txn_id => p_copy_entity_txn_id
3489 ,p_message_text => l_label_start_tag||l_bnb_label||l_label_end_tag||l_blank_line
3490 ,p_message_type_cd => l_label
3491 );
3492
3493 for i in ben_pd_copy_to_ben_one.g_bnb_tbl_copied.first .. ben_pd_copy_to_ben_one.g_bnb_tbl_copied.last
3494 loop
3495 create_process_log
3496 (p_module_cd => l_module_cd
3497 ,p_txn_id => p_copy_entity_txn_id
3498 ,p_message_text => l_value_start_tag ||l_para_spacer
3499 ||ben_pd_copy_to_ben_one.g_bnb_tbl_copied(i).new_name
3500 ||l_value_end_tag||l_blank_line
3501 ,p_message_type_cd => l_value
3502 );
3503 end loop;
3504 end if;
3505
3506 -- Comp Level Factor
3507 if ben_pd_copy_to_ben_one.g_clf_tbl_copied_count > 0 then
3508 create_process_log
3509 (p_module_cd => l_module_cd
3510 ,p_txn_id => p_copy_entity_txn_id
3511 ,p_message_text => l_label_start_tag||l_clf_label||l_label_end_tag||l_blank_line
3512 ,p_message_type_cd => l_label
3513 );
3514
3515 for i in ben_pd_copy_to_ben_one.g_clf_tbl_copied.first .. ben_pd_copy_to_ben_one.g_clf_tbl_copied.last
3516 loop
3517 create_process_log
3518 (p_module_cd => l_module_cd
3519 ,p_txn_id => p_copy_entity_txn_id
3520 ,p_message_text => l_value_start_tag ||l_para_spacer
3521 ||ben_pd_copy_to_ben_one.g_clf_tbl_copied(i).new_name
3522 ||l_value_end_tag||l_blank_line
3523 ,p_message_type_cd => l_value
3524 );
3525 end loop;
3526 end if;
3527
3528 -- Hours Worked Factor
3529 if ben_pd_copy_to_ben_one.g_hwf_tbl_copied_count > 0 then
3530 create_process_log
3531 (p_module_cd => l_module_cd
3532 ,p_txn_id => p_copy_entity_txn_id
3533 ,p_message_text => l_label_start_tag||l_hwf_label||l_label_end_tag||l_blank_line
3534 ,p_message_type_cd => l_label
3535 );
3536
3537 for i in ben_pd_copy_to_ben_one.g_hwf_tbl_copied.first .. ben_pd_copy_to_ben_one.g_hwf_tbl_copied.last
3538 loop
3539 create_process_log
3540 (p_module_cd => l_module_cd
3541 ,p_txn_id => p_copy_entity_txn_id
3542 ,p_message_text => l_value_start_tag ||l_para_spacer
3543 ||ben_pd_copy_to_ben_one.g_hwf_tbl_copied(i).new_name
3544 ||l_value_end_tag||l_blank_line
3545 ,p_message_type_cd => l_value
3546 );
3547 end loop;
3548 end if;
3549
3550 -- Age Factor
3551 if ben_pd_copy_to_ben_one.g_agf_tbl_copied_count > 0 then
3552 create_process_log
3553 (p_module_cd => l_module_cd
3554 ,p_txn_id => p_copy_entity_txn_id
3555 ,p_message_text => l_label_start_tag||l_agf_label||l_label_end_tag||l_blank_line
3556 ,p_message_type_cd => l_label
3557 );
3558
3559 for i in ben_pd_copy_to_ben_one.g_agf_tbl_copied.first .. ben_pd_copy_to_ben_one.g_agf_tbl_copied.last
3560 loop
3561 create_process_log
3562 (p_module_cd => l_module_cd
3563 ,p_txn_id => p_copy_entity_txn_id
3564 ,p_message_text => l_value_start_tag ||l_para_spacer
3565 ||ben_pd_copy_to_ben_one.g_agf_tbl_copied(i).new_name
3566 ||l_value_end_tag||l_blank_line
3567 ,p_message_type_cd => l_value
3568 );
3569 end loop;
3570 end if;
3571
3572 -- Length of Service Factor
3573 if ben_pd_copy_to_ben_one.g_lsf_tbl_copied_count > 0 then
3574 create_process_log
3575 (p_module_cd => l_module_cd
3576 ,p_txn_id => p_copy_entity_txn_id
3577 ,p_message_text => l_label_start_tag||l_lsf_label||l_label_end_tag||l_blank_line
3578 ,p_message_type_cd => l_label
3579 );
3580
3581 for i in ben_pd_copy_to_ben_one.g_lsf_tbl_copied.first .. ben_pd_copy_to_ben_one.g_lsf_tbl_copied.last
3582 loop
3583 create_process_log
3584 (p_module_cd => l_module_cd
3585 ,p_txn_id => p_copy_entity_txn_id
3586 ,p_message_text => l_value_start_tag ||l_para_spacer
3587 ||ben_pd_copy_to_ben_one.g_lsf_tbl_copied(i).new_name
3588 ||l_value_end_tag||l_blank_line
3589 ,p_message_type_cd => l_value
3590 );
3591 end loop;
3592 end if;
3593
3594 -- Percent Full Time factor
3595 if ben_pd_copy_to_ben_one.g_pff_tbl_copied_count > 0 then
3596 create_process_log
3597 (p_module_cd => l_module_cd
3598 ,p_txn_id => p_copy_entity_txn_id
3599 ,p_message_text => l_label_start_tag||l_pff_label||l_label_end_tag||l_blank_line
3600 ,p_message_type_cd => l_label
3601 );
3602
3603 for i in ben_pd_copy_to_ben_one.g_pff_tbl_copied.first .. ben_pd_copy_to_ben_one.g_pff_tbl_copied.last
3604 loop
3605 create_process_log
3606 (p_module_cd => l_module_cd
3607 ,p_txn_id => p_copy_entity_txn_id
3608 ,p_message_text => l_value_start_tag ||l_para_spacer
3609 ||ben_pd_copy_to_ben_one.g_pff_tbl_copied(i).new_name
3610 ||l_value_end_tag||l_blank_line
3611 ,p_message_type_cd => l_value
3612 );
3613 end loop;
3614 end if;
3615
3616 -- Combined LOS and Age Factor
3617 if ben_pd_copy_to_ben_one.g_cla_tbl_copied_count > 0 then
3618 create_process_log
3619 (p_module_cd => l_module_cd
3620 ,p_txn_id => p_copy_entity_txn_id
3621 ,p_message_text => l_label_start_tag||l_cla_label||l_label_end_tag||l_blank_line
3622 ,p_message_type_cd => l_label
3623 );
3624
3625 for i in ben_pd_copy_to_ben_one.g_cla_tbl_copied.first .. ben_pd_copy_to_ben_one.g_cla_tbl_copied.last
3626 loop
3627 create_process_log
3628 (p_module_cd => l_module_cd
3629 ,p_txn_id => p_copy_entity_txn_id
3630 ,p_message_text => l_value_start_tag ||l_para_spacer
3631 ||ben_pd_copy_to_ben_one.g_cla_tbl_copied(i).new_name
3632 ||l_value_end_tag||l_blank_line
3633 ,p_message_type_cd => l_value
3634 );
3635 end loop;
3636 end if;
3637
3638 -- Regulations
3639 if ben_pd_copy_to_ben_one.g_reg_tbl_copied_count > 0 then
3640 create_process_log
3641 (p_module_cd => l_module_cd
3642 ,p_txn_id => p_copy_entity_txn_id
3643 ,p_message_text => l_label_start_tag||l_reg_label||l_label_end_tag||l_blank_line
3644 ,p_message_type_cd => l_label
3645 );
3646
3647 for i in ben_pd_copy_to_ben_one.g_reg_tbl_copied.first .. ben_pd_copy_to_ben_one.g_reg_tbl_copied.last
3648 loop
3649 create_process_log
3650 (p_module_cd => l_module_cd
3651 ,p_txn_id => p_copy_entity_txn_id
3652 ,p_message_text => l_value_start_tag ||l_para_spacer
3653 ||ben_pd_copy_to_ben_one.g_reg_tbl_copied(i).new_name
3654 ||l_value_end_tag||l_blank_line
3655 ,p_message_type_cd => l_value
3656 );
3657 end loop;
3658 end if;
3659
3660 -- Reporting Groups
3661 if ben_pd_copy_to_ben_one.g_bnr_tbl_copied_count > 0 then
3662 create_process_log
3663 (p_module_cd => l_module_cd
3664 ,p_txn_id => p_copy_entity_txn_id
3665 ,p_message_text => l_label_start_tag||l_bnr_label||l_label_end_tag||l_blank_line
3666 ,p_message_type_cd => l_label
3667 );
3668
3669 for i in ben_pd_copy_to_ben_one.g_bnr_tbl_copied.first .. ben_pd_copy_to_ben_one.g_bnr_tbl_copied.last
3670 loop
3671 create_process_log
3672 (p_module_cd => l_module_cd
3673 ,p_txn_id => p_copy_entity_txn_id
3674 ,p_message_text => l_value_start_tag ||l_para_spacer
3675 ||ben_pd_copy_to_ben_one.g_bnr_tbl_copied(i).new_name
3676 ||l_value_end_tag||l_blank_line
3677 ,p_message_type_cd => l_value
3678 );
3679 end loop;
3680 end if;
3681
3682 -- Benefits Group
3683 if ben_pd_copy_to_ben_one.g_bng_tbl_copied_count > 0 then
3684 create_process_log
3685 (p_module_cd => l_module_cd
3686 ,p_txn_id => p_copy_entity_txn_id
3687 ,p_message_text => l_label_start_tag||l_bng_label||l_label_end_tag||l_blank_line
3688 ,p_message_type_cd => l_label
3689 );
3690
3691 for i in ben_pd_copy_to_ben_one.g_bng_tbl_copied.first .. ben_pd_copy_to_ben_one.g_bng_tbl_copied.last
3692 loop
3693 create_process_log
3694 (p_module_cd => l_module_cd
3695 ,p_txn_id => p_copy_entity_txn_id
3696 ,p_message_text => l_value_start_tag ||l_para_spacer
3697 ||ben_pd_copy_to_ben_one.g_bng_tbl_copied(i).new_name
3698 ||l_value_end_tag||l_blank_line
3699 ,p_message_type_cd => l_value
3700 );
3701 end loop;
3702 end if;
3703
3704 -- Goods or services
3705 if ben_pd_copy_to_ben_one.g_gos_tbl_copied_count > 0 then
3706 create_process_log
3707 (p_module_cd => l_module_cd
3708 ,p_txn_id => p_copy_entity_txn_id
3709 ,p_message_text => l_label_start_tag||l_gos_label||l_label_end_tag||l_blank_line
3710 ,p_message_type_cd => l_label
3711 );
3712
3713 for i in ben_pd_copy_to_ben_one.g_gos_tbl_copied.first .. ben_pd_copy_to_ben_one.g_gos_tbl_copied.last
3714 loop
3715 create_process_log
3716 (p_module_cd => l_module_cd
3717 ,p_txn_id => p_copy_entity_txn_id
3718 ,p_message_text => l_value_start_tag ||l_para_spacer
3719 ||ben_pd_copy_to_ben_one.g_gos_tbl_copied(i).new_name
3720 ||l_value_end_tag||l_blank_line
3721 ,p_message_type_cd => l_value
3722 );
3723 end loop;
3724 end if;
3725
3726 -- Period To Date Limits
3727 if ben_pd_copy_to_ben_one.g_pdl_tbl_copied_count > 0 then
3728 create_process_log
3729 (p_module_cd => l_module_cd
3730 ,p_txn_id => p_copy_entity_txn_id
3731 ,p_message_text => l_label_start_tag||l_pdl_label||l_label_end_tag||l_blank_line
3732 ,p_message_type_cd => l_label
3733 );
3734
3735 for i in ben_pd_copy_to_ben_one.g_pdl_tbl_copied.first .. ben_pd_copy_to_ben_one.g_pdl_tbl_copied.last
3736 loop
3737 create_process_log
3738 (p_module_cd => l_module_cd
3739 ,p_txn_id => p_copy_entity_txn_id
3740 ,p_message_text => l_value_start_tag ||l_para_spacer
3741 ||ben_pd_copy_to_ben_one.g_pdl_tbl_copied(i).new_name
3742 ||l_value_end_tag||l_blank_line
3743 ,p_message_type_cd => l_value
3744 );
3745 end loop;
3746 end if;
3747
3748 -- Service Area
3749 if ben_pd_copy_to_ben_one.g_sva_tbl_copied_count > 0 then
3750 create_process_log
3751 (p_module_cd => l_module_cd
3752 ,p_txn_id => p_copy_entity_txn_id
3753 ,p_message_text => l_label_start_tag||l_sva_label||l_label_end_tag||l_blank_line
3754 ,p_message_type_cd => l_label
3755 );
3756
3757 for i in ben_pd_copy_to_ben_one.g_sva_tbl_copied.first .. ben_pd_copy_to_ben_one.g_sva_tbl_copied.last
3758 loop
3759 create_process_log
3760 (p_module_cd => l_module_cd
3761 ,p_txn_id => p_copy_entity_txn_id
3762 ,p_message_text => l_value_start_tag ||l_para_spacer
3763 ||ben_pd_copy_to_ben_one.g_sva_tbl_copied(i).new_name
3764 ||l_value_end_tag||l_blank_line
3765 ,p_message_type_cd => l_value
3766 );
3767 end loop;
3768 end if;
3769
3770
3771 /*
3772 -- Action Types
3773 if ben_pd_copy_to_ben_one.g_eat_tbl_copied_count > 0 then
3774 create_process_log
3775 (p_module_cd => l_module_cd
3776 ,p_txn_id => p_copy_entity_txn_id
3777 ,p_message_text => l_label_start_tag||l_eat_label||l_label_end_tag||l_blank_line
3778 ,p_message_type_cd => l_label
3779 );
3780
3781 for i in ben_pd_copy_to_ben_one.g_eat_tbl_copied.first .. ben_pd_copy_to_ben_one.g_eat_tbl_copied.last
3782 loop
3783 create_process_log
3784 (p_module_cd => l_module_cd
3785 ,p_txn_id => p_copy_entity_txn_id
3786 ,p_message_text => l_value_start_tag ||l_para_spacer
3787 ||ben_pd_copy_to_ben_one.g_eat_tbl_copied(i).new_name
3788 ||l_value_end_tag||l_blank_line
3789 ,p_message_type_cd => l_value
3790 );
3791 end loop;
3792 end if;
3793 */
3794
3795 -- Combination PLIP
3796 if ben_pd_copy_to_ben_one.g_cpl_tbl_copied_count > 0 then
3797 create_process_log
3798 (p_module_cd => l_module_cd
3799 ,p_txn_id => p_copy_entity_txn_id
3800 ,p_message_text => l_label_start_tag||l_cpl_label||l_label_end_tag||l_blank_line
3801 ,p_message_type_cd => l_label
3802 );
3803
3804 for i in ben_pd_copy_to_ben_one.g_cpl_tbl_copied.first .. ben_pd_copy_to_ben_one.g_cpl_tbl_copied.last
3805 loop
3806 create_process_log
3807 (p_module_cd => l_module_cd
3808 ,p_txn_id => p_copy_entity_txn_id
3809 ,p_message_text => l_value_start_tag ||l_para_spacer
3810 ||ben_pd_copy_to_ben_one.g_cpl_tbl_copied(i).new_name
3811 ||l_value_end_tag||l_blank_line
3812 ,p_message_type_cd => l_value
3813 );
3814 end loop;
3815 end if;
3816
3817 -- Combination PTIP
3818 if ben_pd_copy_to_ben_one.g_cbp_tbl_copied_count > 0 then
3819 create_process_log
3820 (p_module_cd => l_module_cd
3821 ,p_txn_id => p_copy_entity_txn_id
3822 ,p_message_text => l_label_start_tag||l_cbp_label||l_label_end_tag||l_blank_line
3823 ,p_message_type_cd => l_label
3824 );
3825
3826 for i in ben_pd_copy_to_ben_one.g_cbp_tbl_copied.first .. ben_pd_copy_to_ben_one.g_cbp_tbl_copied.last
3827 loop
3828 create_process_log
3829 (p_module_cd => l_module_cd
3830 ,p_txn_id => p_copy_entity_txn_id
3831 ,p_message_text => l_value_start_tag ||l_para_spacer
3832 ||ben_pd_copy_to_ben_one.g_cbp_tbl_copied(i).new_name
3833 ||l_value_end_tag||l_blank_line
3834 ,p_message_type_cd => l_value
3835 );
3836 end loop;
3837 end if;
3838
3839 -- Combination PTIP OPT
3840 if ben_pd_copy_to_ben_one.g_cpt_tbl_copied_count > 0 then
3841 create_process_log
3842 (p_module_cd => l_module_cd
3843 ,p_txn_id => p_copy_entity_txn_id
3844 ,p_message_text => l_label_start_tag||l_cpt_label||l_label_end_tag||l_blank_line
3845 ,p_message_type_cd => l_label
3846 );
3847
3848 for i in ben_pd_copy_to_ben_one.g_cpt_tbl_copied.first .. ben_pd_copy_to_ben_one.g_cpt_tbl_copied.last
3849 loop
3850 create_process_log
3851 (p_module_cd => l_module_cd
3852 ,p_txn_id => p_copy_entity_txn_id
3853 ,p_message_text => l_value_start_tag ||l_para_spacer
3854 ||ben_pd_copy_to_ben_one.g_cpt_tbl_copied(i).new_name
3855 ||l_value_end_tag||l_blank_line
3856 ,p_message_type_cd => l_value
3857 );
3858 end loop;
3859 end if;
3860
3861 -- Fast Formulas
3862 if ben_pd_copy_to_ben_one.g_fff_tbl_copied_count > 0 then
3863 create_process_log
3864 (p_module_cd => l_module_cd
3865 ,p_txn_id => p_copy_entity_txn_id
3866 ,p_message_text => l_label_start_tag||l_fff_label||l_label_end_tag||l_blank_line
3867 ,p_message_type_cd => l_label
3868 );
3869
3870 for i in ben_pd_copy_to_ben_one.g_fff_tbl_copied.first .. ben_pd_copy_to_ben_one.g_fff_tbl_copied.last
3871 loop
3872 create_process_log
3873 (p_module_cd => l_module_cd
3874 ,p_txn_id => p_copy_entity_txn_id
3875 ,p_message_text => l_value_start_tag ||l_para_spacer
3876 ||ben_pd_copy_to_ben_one.g_fff_tbl_copied(i).new_name
3877 ||l_value_end_tag||l_blank_line
3878 ,p_message_type_cd => l_value
3879 );
3880 end loop;
3881 end if;
3882
3883 -- Create Spacer Line
3884 create_process_log
3885 (p_module_cd => l_module_cd
3886 ,p_txn_id => p_copy_entity_txn_id
3887 ,p_message_text => l_blank_line
3888 ,p_message_type_cd => l_spacer
3889 );
3890
3891 -- Hide for Plan Design Wizard
3892 IF l_copy_entity_txn.short_name <> 'BEN_PDCRWZ' THEN
3893
3894 -- Insert 'Reused the Following Objects'
3895 l_reused_objects_lbl := l_header_start_tag || fnd_message.get_string('BEN','BEN_93285_PDC_REUSED_OBJECTS')
3896 ||l_header_end_tag;
3897
3898 create_process_log
3899 (p_module_cd => l_module_cd
3900 ,p_txn_id => p_copy_entity_txn_id
3901 ,p_message_text => l_reused_objects_lbl
3902 ,p_message_type_cd => l_label
3903 );
3904
3905 -- Create Spacer Line
3906 create_process_log
3907 (p_module_cd => l_module_cd
3908 ,p_txn_id => p_copy_entity_txn_id
3909 ,p_message_text => l_blank_line||l_blank_line
3910 ,p_message_type_cd => l_spacer
3911 );
3912
3913 -- Programs
3914 if ben_pd_copy_to_ben_one.g_pgm_tbl_reused_count > 0 then
3915 create_process_log
3916 (p_module_cd => l_module_cd
3917 ,p_txn_id => p_copy_entity_txn_id
3918 ,p_message_text => l_label_start_tag||l_pgm_label||l_label_end_tag||l_blank_line
3919 ,p_message_type_cd => l_label
3920 );
3921
3922 for i in ben_pd_copy_to_ben_one.g_pgm_tbl_reused.first .. ben_pd_copy_to_ben_one.g_pgm_tbl_reused.last
3923 loop
3924 create_process_log
3925 (p_module_cd => l_module_cd
3926 ,p_txn_id => p_copy_entity_txn_id
3927 ,p_message_text => l_value_start_tag ||l_para_spacer
3928 ||ben_pd_copy_to_ben_one.g_pgm_tbl_reused(i).new_name
3929 ||l_value_end_tag||l_blank_line
3930 ,p_message_type_cd => l_value
3931 );
3932 end loop;
3933 end if;
3934
3935 -- Plan Types
3936 if ben_pd_copy_to_ben_one.g_ptp_tbl_reused_count > 0 then
3937 create_process_log
3938 (p_module_cd => l_module_cd
3939 ,p_txn_id => p_copy_entity_txn_id
3940 ,p_message_text => l_label_start_tag||l_ptp_label||l_label_end_tag||l_blank_line
3941 ,p_message_type_cd => l_label
3942 );
3943
3944 for i in ben_pd_copy_to_ben_one.g_ptp_tbl_reused.first .. ben_pd_copy_to_ben_one.g_ptp_tbl_reused.last
3945 loop
3946 create_process_log
3947 (p_module_cd => l_module_cd
3948 ,p_txn_id => p_copy_entity_txn_id
3949 ,p_message_text => l_value_start_tag ||l_para_spacer
3950 ||ben_pd_copy_to_ben_one.g_ptp_tbl_reused(i).new_name
3951 ||l_value_end_tag||l_blank_line
3952 ,p_message_type_cd => l_value
3953 );
3954 end loop;
3955 end if;
3956
3957 -- Plans
3958 if ben_pd_copy_to_ben_one.g_pln_tbl_reused_count > 0 then
3959 create_process_log
3960 (p_module_cd => l_module_cd
3961 ,p_txn_id => p_copy_entity_txn_id
3962 ,p_message_text => l_label_start_tag||l_pln_label||l_label_end_tag||l_blank_line
3963 ,p_message_type_cd => l_label
3964 );
3965
3966 for i in ben_pd_copy_to_ben_one.g_pln_tbl_reused.first .. ben_pd_copy_to_ben_one.g_pln_tbl_reused.last
3967 loop
3968 create_process_log
3969 (p_module_cd => l_module_cd
3970 ,p_txn_id => p_copy_entity_txn_id
3971 ,p_message_text => l_value_start_tag ||l_para_spacer
3972 ||ben_pd_copy_to_ben_one.g_pln_tbl_reused(i).new_name
3973 ||l_value_end_tag||l_blank_line
3974 ,p_message_type_cd => l_value
3975 );
3976 end loop;
3977 end if;
3978
3979 -- Options
3980 if ben_pd_copy_to_ben_one.g_opt_tbl_reused_count > 0 then
3981 create_process_log
3982 (p_module_cd => l_module_cd
3983 ,p_txn_id => p_copy_entity_txn_id
3984 ,p_message_text => l_label_start_tag||l_opt_label||l_label_end_tag||l_blank_line
3985 ,p_message_type_cd => l_label
3986 );
3987
3988 for i in ben_pd_copy_to_ben_one.g_opt_tbl_reused.first .. ben_pd_copy_to_ben_one.g_opt_tbl_reused.last
3989 loop
3990 create_process_log
3991 (p_module_cd => l_module_cd
3992 ,p_txn_id => p_copy_entity_txn_id
3993 ,p_message_text => l_value_start_tag ||l_para_spacer
3994 ||ben_pd_copy_to_ben_one.g_opt_tbl_reused(i).new_name
3995 ||l_value_end_tag||l_blank_line
3996 ,p_message_type_cd => l_value
3997 );
3998 end loop;
3999 end if;
4000
4001 -- Rates
4002 if ben_pd_copy_to_ben_one.g_abr_tbl_reused_count > 0 then
4003 create_process_log
4004 (p_module_cd => l_module_cd
4005 ,p_txn_id => p_copy_entity_txn_id
4006 ,p_message_text => l_label_start_tag||l_abr_label||l_label_end_tag||l_blank_line
4007 ,p_message_type_cd => l_label
4008 );
4009
4010 for i in ben_pd_copy_to_ben_one.g_abr_tbl_reused.first .. ben_pd_copy_to_ben_one.g_abr_tbl_reused.last
4011 loop
4012 create_process_log
4013 (p_module_cd => l_module_cd
4014 ,p_txn_id => p_copy_entity_txn_id
4015 ,p_message_text => l_value_start_tag ||l_para_spacer
4016 ||ben_pd_copy_to_ben_one.g_abr_tbl_reused(i).new_name
4017 ||l_value_end_tag||l_blank_line
4018 ,p_message_type_cd => l_value
4019 );
4020 end loop;
4021 end if;
4022
4023 -- Benefit Pools
4024 if ben_pd_copy_to_ben_one.g_bpp_tbl_reused_count > 0 then
4025 create_process_log
4026 (p_module_cd => l_module_cd
4027 ,p_txn_id => p_copy_entity_txn_id
4028 ,p_message_text => l_label_start_tag||l_bpp_label||l_label_end_tag||l_blank_line
4029 ,p_message_type_cd => l_label
4030 );
4031
4032 for i in ben_pd_copy_to_ben_one.g_bpp_tbl_reused.first .. ben_pd_copy_to_ben_one.g_bpp_tbl_reused.last
4033 loop
4034 create_process_log
4035 (p_module_cd => l_module_cd
4036 ,p_txn_id => p_copy_entity_txn_id
4037 ,p_message_text => l_value_start_tag ||l_para_spacer
4038 ||ben_pd_copy_to_ben_one.g_bpp_tbl_reused(i).new_name
4039 ||l_value_end_tag||l_blank_line
4040 ,p_message_type_cd => l_value
4041 );
4042 end loop;
4043 end if;
4044
4045 -- Actual Premiums
4046 if ben_pd_copy_to_ben_one.g_apr_tbl_reused_count > 0 then
4047 create_process_log
4048 (p_module_cd => l_module_cd
4049 ,p_txn_id => p_copy_entity_txn_id
4050 ,p_message_text => l_label_start_tag||l_apr_label||l_label_end_tag||l_blank_line
4051 ,p_message_type_cd => l_label
4052 );
4053
4054 for i in ben_pd_copy_to_ben_one.g_apr_tbl_reused.first .. ben_pd_copy_to_ben_one.g_apr_tbl_reused.last
4055 loop
4056 create_process_log
4057 (p_module_cd => l_module_cd
4058 ,p_txn_id => p_copy_entity_txn_id
4059 ,p_message_text => l_value_start_tag ||l_para_spacer
4060 ||ben_pd_copy_to_ben_one.g_apr_tbl_reused(i).new_name
4061 ||l_value_end_tag||l_blank_line
4062 ,p_message_type_cd => l_value
4063 );
4064 end loop;
4065 end if;
4066
4067 -- Coverages
4068 if ben_pd_copy_to_ben_one.g_ccm_tbl_reused_count > 0 then
4069 create_process_log
4070 (p_module_cd => l_module_cd
4071 ,p_txn_id => p_copy_entity_txn_id
4072 ,p_message_text => l_label_start_tag||l_ccm_label||l_label_end_tag||l_blank_line
4073 ,p_message_type_cd => l_label
4074 );
4075
4076 for i in ben_pd_copy_to_ben_one.g_ccm_tbl_reused.first .. ben_pd_copy_to_ben_one.g_ccm_tbl_reused.last
4077 loop
4078 create_process_log
4079 (p_module_cd => l_module_cd
4080 ,p_txn_id => p_copy_entity_txn_id
4081 ,p_message_text => l_value_start_tag ||l_para_spacer
4082 ||ben_pd_copy_to_ben_one.g_ccm_tbl_reused(i).new_name
4083 ||l_value_end_tag||l_blank_line
4084 ,p_message_type_cd => l_value
4085 );
4086 end loop;
4087 end if;
4088
4089 -- Coverage across Plan Types
4090 if ben_pd_copy_to_ben_one.g_acp_tbl_reused_count > 0 then
4091 create_process_log
4092 (p_module_cd => l_module_cd
4093 ,p_txn_id => p_copy_entity_txn_id
4094 ,p_message_text => l_label_start_tag||l_acp_label||l_label_end_tag||l_blank_line
4095 ,p_message_type_cd => l_label
4096 );
4097
4098 for i in ben_pd_copy_to_ben_one.g_acp_tbl_reused.first .. ben_pd_copy_to_ben_one.g_acp_tbl_reused.last
4099 loop
4100 create_process_log
4101 (p_module_cd => l_module_cd
4102 ,p_txn_id => p_copy_entity_txn_id
4103 ,p_message_text => l_value_start_tag ||l_para_spacer
4104 ||ben_pd_copy_to_ben_one.g_acp_tbl_reused(i).new_name
4105 ||l_value_end_tag||l_blank_line
4106 ,p_message_type_cd => l_value
4107 );
4108 end loop;
4109 end if;
4110
4111 -- Life Events
4112 if ben_pd_copy_to_ben_one.g_ler_tbl_reused_count > 0 then
4113 create_process_log
4114 (p_module_cd => l_module_cd
4115 ,p_txn_id => p_copy_entity_txn_id
4116 ,p_message_text => l_label_start_tag||l_ler_label||l_label_end_tag||l_blank_line
4117 ,p_message_type_cd => l_label
4118 );
4119
4120 for i in ben_pd_copy_to_ben_one.g_ler_tbl_reused.first .. ben_pd_copy_to_ben_one.g_ler_tbl_reused.last
4121 loop
4122 create_process_log
4123 (p_module_cd => l_module_cd
4124 ,p_txn_id => p_copy_entity_txn_id
4125 ,p_message_text => l_value_start_tag ||l_para_spacer
4126 ||ben_pd_copy_to_ben_one.g_ler_tbl_reused(i).new_name
4127 ||l_value_end_tag||l_blank_line
4128 ,p_message_type_cd => l_value
4129 );
4130 end loop;
4131 end if;
4132
4133
4134 -- Life Events(Person Changes)
4135 if ben_pd_copy_to_ben_one.g_psl_tbl_reused_count > 0 then
4136 create_process_log
4137 (p_module_cd => l_module_cd
4138 ,p_txn_id => p_copy_entity_txn_id
4139 ,p_message_text => l_label_start_tag||l_psl_label||l_label_end_tag||l_blank_line
4140 ,p_message_type_cd => l_label
4141 );
4142
4143 for i in ben_pd_copy_to_ben_one.g_psl_tbl_reused.first .. ben_pd_copy_to_ben_one.g_psl_tbl_reused.last
4144 loop
4145 create_process_log
4146 (p_module_cd => l_module_cd
4147 ,p_txn_id => p_copy_entity_txn_id
4148 ,p_message_text => l_value_start_tag ||l_para_spacer
4149 ||ben_pd_copy_to_ben_one.g_psl_tbl_reused(i).new_name
4150 ||l_value_end_tag||l_blank_line
4151 ,p_message_type_cd => l_value
4152 );
4153 end loop;
4154 end if;
4155
4156 -- Eligibility profiles
4157 if ben_pd_copy_to_ben_one.g_elp_tbl_reused_count > 0 then
4158 create_process_log
4159 (p_module_cd => l_module_cd
4160 ,p_txn_id => p_copy_entity_txn_id
4161 ,p_message_text => l_label_start_tag||l_elp_label||l_label_end_tag||l_blank_line
4162 ,p_message_type_cd => l_label
4163 );
4164
4165 for i in ben_pd_copy_to_ben_one.g_elp_tbl_reused.first .. ben_pd_copy_to_ben_one.g_elp_tbl_reused.last
4166 loop
4167 create_process_log
4168 (p_module_cd => l_module_cd
4169 ,p_txn_id => p_copy_entity_txn_id
4170 ,p_message_text => l_value_start_tag ||l_para_spacer
4171 ||ben_pd_copy_to_ben_one.g_elp_tbl_reused(i).new_name
4172 ||l_value_end_tag||l_blank_line
4173 ,p_message_type_cd => l_value
4174 );
4175 end loop;
4176 end if;
4177
4178 -- Dependent Eligibility profiles
4179 if ben_pd_copy_to_ben_one.g_dce_tbl_reused_count > 0 then
4180 create_process_log
4181 (p_module_cd => l_module_cd
4182 ,p_txn_id => p_copy_entity_txn_id
4183 ,p_message_text => l_label_start_tag||l_dce_label||l_label_end_tag||l_blank_line
4184 ,p_message_type_cd => l_label
4185 );
4186
4187 for i in ben_pd_copy_to_ben_one.g_dce_tbl_reused.first .. ben_pd_copy_to_ben_one.g_dce_tbl_reused.last
4188 loop
4189 create_process_log
4190 (p_module_cd => l_module_cd
4191 ,p_txn_id => p_copy_entity_txn_id
4192 ,p_message_text => l_value_start_tag ||l_para_spacer
4193 ||ben_pd_copy_to_ben_one.g_dce_tbl_reused(i).new_name
4194 ||l_value_end_tag||l_blank_line
4195 ,p_message_type_cd => l_value
4196 );
4197 end loop;
4198 end if;
4199
4200 -- Variable Rate Profiles
4201 if ben_pd_copy_to_ben_one.g_vpf_tbl_reused_count > 0 then
4202 create_process_log
4203 (p_module_cd => l_module_cd
4204 ,p_txn_id => p_copy_entity_txn_id
4205 ,p_message_text => l_label_start_tag||l_vpf_label||l_label_end_tag||l_blank_line
4206 ,p_message_type_cd => l_label
4207 );
4208
4209 for i in ben_pd_copy_to_ben_one.g_vpf_tbl_reused.first .. ben_pd_copy_to_ben_one.g_vpf_tbl_reused.last
4210 loop
4211 create_process_log
4212 (p_module_cd => l_module_cd
4213 ,p_txn_id => p_copy_entity_txn_id
4214 ,p_message_text => l_value_start_tag ||l_para_spacer
4215 ||ben_pd_copy_to_ben_one.g_vpf_tbl_reused(i).new_name
4216 ||l_value_end_tag||l_blank_line
4217 ,p_message_type_cd => l_value
4218 );
4219 end loop;
4220 end if;
4221 --
4222 -- Bug 4169120 : Rate By Criteria
4223 -- Eligibility Criteria
4224 --
4225 if ben_pd_copy_to_ben_one.g_egl_tbl_reused_count > 0 then
4226 create_process_log
4227 (p_module_cd => l_module_cd
4228 ,p_txn_id => p_copy_entity_txn_id
4229 ,p_message_text => l_label_start_tag||l_egl_label||l_label_end_tag||l_blank_line
4230 ,p_message_type_cd => l_label
4231 );
4232
4233 for i in ben_pd_copy_to_ben_one.g_egl_tbl_reused.first .. ben_pd_copy_to_ben_one.g_egl_tbl_reused.last
4234 loop
4235 create_process_log
4236 (p_module_cd => l_module_cd
4237 ,p_txn_id => p_copy_entity_txn_id
4238 ,p_message_text => l_value_start_tag ||l_para_spacer
4239 ||ben_pd_copy_to_ben_one.g_egl_tbl_reused(i).new_name
4240 ||l_value_end_tag||l_blank_line
4241 ,p_message_type_cd => l_value
4242 );
4243 end loop;
4244 end if;
4245 --
4246 -- Benefit Balances
4247 if ben_pd_copy_to_ben_one.g_bnb_tbl_reused_count > 0 then
4248 create_process_log
4249 (p_module_cd => l_module_cd
4250 ,p_txn_id => p_copy_entity_txn_id
4251 ,p_message_text => l_label_start_tag||l_bnb_label||l_label_end_tag||l_blank_line
4252 ,p_message_type_cd => l_label
4253 );
4254
4255 for i in ben_pd_copy_to_ben_one.g_bnb_tbl_reused.first .. ben_pd_copy_to_ben_one.g_bnb_tbl_reused.last
4256 loop
4257 create_process_log
4258 (p_module_cd => l_module_cd
4259 ,p_txn_id => p_copy_entity_txn_id
4260 ,p_message_text => l_value_start_tag ||l_para_spacer
4261 ||ben_pd_copy_to_ben_one.g_bnb_tbl_reused(i).new_name
4262 ||l_value_end_tag||l_blank_line
4263 ,p_message_type_cd => l_value
4264 );
4265 end loop;
4266 end if;
4267
4268 -- Comp Level Factor
4269 if ben_pd_copy_to_ben_one.g_clf_tbl_reused_count > 0 then
4270 create_process_log
4271 (p_module_cd => l_module_cd
4272 ,p_txn_id => p_copy_entity_txn_id
4273 ,p_message_text => l_label_start_tag||l_clf_label||l_label_end_tag||l_blank_line
4274 ,p_message_type_cd => l_label
4275 );
4276
4277 for i in ben_pd_copy_to_ben_one.g_clf_tbl_reused.first .. ben_pd_copy_to_ben_one.g_clf_tbl_reused.last
4278 loop
4279 create_process_log
4280 (p_module_cd => l_module_cd
4281 ,p_txn_id => p_copy_entity_txn_id
4282 ,p_message_text => l_value_start_tag ||l_para_spacer
4283 ||ben_pd_copy_to_ben_one.g_clf_tbl_reused(i).new_name
4284 ||l_value_end_tag||l_blank_line
4285 ,p_message_type_cd => l_value
4286 );
4287 end loop;
4288 end if;
4289
4290 -- Hours Worked Factor
4291 if ben_pd_copy_to_ben_one.g_hwf_tbl_reused_count > 0 then
4292 create_process_log
4293 (p_module_cd => l_module_cd
4294 ,p_txn_id => p_copy_entity_txn_id
4295 ,p_message_text => l_label_start_tag||l_hwf_label||l_label_end_tag||l_blank_line
4296 ,p_message_type_cd => l_label
4297 );
4298
4299 for i in ben_pd_copy_to_ben_one.g_hwf_tbl_reused.first .. ben_pd_copy_to_ben_one.g_hwf_tbl_reused.last
4300 loop
4301 create_process_log
4302 (p_module_cd => l_module_cd
4303 ,p_txn_id => p_copy_entity_txn_id
4304 ,p_message_text => l_value_start_tag ||l_para_spacer
4305 ||ben_pd_copy_to_ben_one.g_hwf_tbl_reused(i).new_name
4306 ||l_value_end_tag||l_blank_line
4307 ,p_message_type_cd => l_value
4308 );
4309 end loop;
4310 end if;
4311
4312 -- Age Factor
4313 if ben_pd_copy_to_ben_one.g_agf_tbl_reused_count > 0 then
4314 create_process_log
4315 (p_module_cd => l_module_cd
4316 ,p_txn_id => p_copy_entity_txn_id
4317 ,p_message_text => l_label_start_tag||l_agf_label||l_label_end_tag||l_blank_line
4318 ,p_message_type_cd => l_label
4319 );
4320
4321 for i in ben_pd_copy_to_ben_one.g_agf_tbl_reused.first .. ben_pd_copy_to_ben_one.g_agf_tbl_reused.last
4322 loop
4323 create_process_log
4324 (p_module_cd => l_module_cd
4325 ,p_txn_id => p_copy_entity_txn_id
4326 ,p_message_text => l_value_start_tag ||l_para_spacer
4327 ||ben_pd_copy_to_ben_one.g_agf_tbl_reused(i).new_name
4328 ||l_value_end_tag||l_blank_line
4329 ,p_message_type_cd => l_value
4330 );
4331 end loop;
4332 end if;
4333
4334 -- Length of Service Factor
4335 if ben_pd_copy_to_ben_one.g_lsf_tbl_reused_count > 0 then
4336 create_process_log
4337 (p_module_cd => l_module_cd
4338 ,p_txn_id => p_copy_entity_txn_id
4339 ,p_message_text => l_label_start_tag||l_lsf_label||l_label_end_tag||l_blank_line
4340 ,p_message_type_cd => l_label
4341 );
4342
4343 for i in ben_pd_copy_to_ben_one.g_lsf_tbl_reused.first .. ben_pd_copy_to_ben_one.g_lsf_tbl_reused.last
4344 loop
4345 create_process_log
4346 (p_module_cd => l_module_cd
4347 ,p_txn_id => p_copy_entity_txn_id
4348 ,p_message_text => l_value_start_tag ||l_para_spacer
4349 ||ben_pd_copy_to_ben_one.g_lsf_tbl_reused(i).new_name
4350 ||l_value_end_tag||l_blank_line
4351 ,p_message_type_cd => l_value
4352 );
4353 end loop;
4354 end if;
4355
4356 -- Percent Full Time factor
4357 if ben_pd_copy_to_ben_one.g_pff_tbl_reused_count > 0 then
4358 create_process_log
4359 (p_module_cd => l_module_cd
4360 ,p_txn_id => p_copy_entity_txn_id
4361 ,p_message_text => l_label_start_tag||l_pff_label||l_label_end_tag||l_blank_line
4362 ,p_message_type_cd => l_label
4363 );
4364
4365 for i in ben_pd_copy_to_ben_one.g_pff_tbl_reused.first .. ben_pd_copy_to_ben_one.g_pff_tbl_reused.last
4366 loop
4367 create_process_log
4368 (p_module_cd => l_module_cd
4369 ,p_txn_id => p_copy_entity_txn_id
4370 ,p_message_text => l_value_start_tag ||l_para_spacer
4371 ||ben_pd_copy_to_ben_one.g_pff_tbl_reused(i).new_name
4372 ||l_value_end_tag||l_blank_line
4373 ,p_message_type_cd => l_value
4374 );
4375 end loop;
4376 end if;
4377
4378 -- Combined LOS and Age Factor
4379 if ben_pd_copy_to_ben_one.g_cla_tbl_reused_count > 0 then
4380 create_process_log
4381 (p_module_cd => l_module_cd
4382 ,p_txn_id => p_copy_entity_txn_id
4383 ,p_message_text => l_label_start_tag||l_cla_label||l_label_end_tag||l_blank_line
4384 ,p_message_type_cd => l_label
4385 );
4386
4387 for i in ben_pd_copy_to_ben_one.g_cla_tbl_reused.first .. ben_pd_copy_to_ben_one.g_cla_tbl_reused.last
4388 loop
4389 create_process_log
4390 (p_module_cd => l_module_cd
4391 ,p_txn_id => p_copy_entity_txn_id
4392 ,p_message_text => l_value_start_tag ||l_para_spacer
4393 ||ben_pd_copy_to_ben_one.g_cla_tbl_reused(i).new_name
4394 ||l_value_end_tag||l_blank_line
4395 ,p_message_type_cd => l_value
4396 );
4397 end loop;
4398 end if;
4399
4400 -- Regulations
4401 if ben_pd_copy_to_ben_one.g_reg_tbl_reused_count > 0 then
4402 create_process_log
4403 (p_module_cd => l_module_cd
4404 ,p_txn_id => p_copy_entity_txn_id
4405 ,p_message_text => l_label_start_tag||l_reg_label||l_label_end_tag||l_blank_line
4406 ,p_message_type_cd => l_label
4407 );
4408
4409 for i in ben_pd_copy_to_ben_one.g_reg_tbl_reused.first .. ben_pd_copy_to_ben_one.g_reg_tbl_reused.last
4410 loop
4411 create_process_log
4412 (p_module_cd => l_module_cd
4413 ,p_txn_id => p_copy_entity_txn_id
4414 ,p_message_text => l_value_start_tag ||l_para_spacer
4415 ||ben_pd_copy_to_ben_one.g_reg_tbl_reused(i).new_name
4416 ||l_value_end_tag||l_blank_line
4417 ,p_message_type_cd => l_value
4418 );
4419 end loop;
4420 end if;
4421
4422 -- Reporting Groups
4423 if ben_pd_copy_to_ben_one.g_bnr_tbl_reused_count > 0 then
4424 create_process_log
4425 (p_module_cd => l_module_cd
4426 ,p_txn_id => p_copy_entity_txn_id
4427 ,p_message_text => l_label_start_tag||l_bnr_label||l_label_end_tag||l_blank_line
4428 ,p_message_type_cd => l_label
4429 );
4430
4431 for i in ben_pd_copy_to_ben_one.g_bnr_tbl_reused.first .. ben_pd_copy_to_ben_one.g_bnr_tbl_reused.last
4432 loop
4433 create_process_log
4434 (p_module_cd => l_module_cd
4435 ,p_txn_id => p_copy_entity_txn_id
4436 ,p_message_text => l_value_start_tag ||l_para_spacer
4437 ||ben_pd_copy_to_ben_one.g_bnr_tbl_reused(i).new_name
4438 ||l_value_end_tag||l_blank_line
4439 ,p_message_type_cd => l_value
4440 );
4441 end loop;
4442 end if;
4443
4444 -- Benefits Group
4445 if ben_pd_copy_to_ben_one.g_bng_tbl_reused_count > 0 then
4446 create_process_log
4447 (p_module_cd => l_module_cd
4448 ,p_txn_id => p_copy_entity_txn_id
4449 ,p_message_text => l_label_start_tag||l_bng_label||l_label_end_tag||l_blank_line
4450 ,p_message_type_cd => l_label
4451 );
4452
4453 for i in ben_pd_copy_to_ben_one.g_bng_tbl_reused.first .. ben_pd_copy_to_ben_one.g_bng_tbl_reused.last
4454 loop
4455 create_process_log
4456 (p_module_cd => l_module_cd
4457 ,p_txn_id => p_copy_entity_txn_id
4458 ,p_message_text => l_value_start_tag ||l_para_spacer
4459 ||ben_pd_copy_to_ben_one.g_bng_tbl_reused(i).new_name
4460 ||l_value_end_tag||l_blank_line
4461 ,p_message_type_cd => l_value
4462 );
4463 end loop;
4464 end if;
4465
4466 -- Goods or services
4467 if ben_pd_copy_to_ben_one.g_gos_tbl_reused_count > 0 then
4468 create_process_log
4469 (p_module_cd => l_module_cd
4470 ,p_txn_id => p_copy_entity_txn_id
4471 ,p_message_text => l_label_start_tag||l_gos_label||l_label_end_tag||l_blank_line
4472 ,p_message_type_cd => l_label
4473 );
4474
4475 for i in ben_pd_copy_to_ben_one.g_gos_tbl_reused.first .. ben_pd_copy_to_ben_one.g_gos_tbl_reused.last
4476 loop
4477 create_process_log
4478 (p_module_cd => l_module_cd
4479 ,p_txn_id => p_copy_entity_txn_id
4480 ,p_message_text => l_value_start_tag ||l_para_spacer
4481 ||ben_pd_copy_to_ben_one.g_gos_tbl_reused(i).new_name
4482 ||l_value_end_tag||l_blank_line
4483 ,p_message_type_cd => l_value
4484 );
4485 end loop;
4486 end if;
4487
4488 -- Period To Date Limits
4489 if ben_pd_copy_to_ben_one.g_pdl_tbl_reused_count > 0 then
4490 create_process_log
4491 (p_module_cd => l_module_cd
4492 ,p_txn_id => p_copy_entity_txn_id
4493 ,p_message_text => l_label_start_tag||l_pdl_label||l_label_end_tag||l_blank_line
4494 ,p_message_type_cd => l_label
4495 );
4496
4497 for i in ben_pd_copy_to_ben_one.g_pdl_tbl_reused.first .. ben_pd_copy_to_ben_one.g_pdl_tbl_reused.last
4498 loop
4499 create_process_log
4500 (p_module_cd => l_module_cd
4501 ,p_txn_id => p_copy_entity_txn_id
4502 ,p_message_text => l_value_start_tag ||l_para_spacer
4503 ||ben_pd_copy_to_ben_one.g_pdl_tbl_reused(i).new_name
4504 ||l_value_end_tag||l_blank_line
4505 ,p_message_type_cd => l_value
4506 );
4507 end loop;
4508 end if;
4509
4510 -- Service Area
4511 if ben_pd_copy_to_ben_one.g_sva_tbl_reused_count > 0 then
4512 create_process_log
4513 (p_module_cd => l_module_cd
4514 ,p_txn_id => p_copy_entity_txn_id
4515 ,p_message_text => l_label_start_tag||l_sva_label||l_label_end_tag||l_blank_line
4516 ,p_message_type_cd => l_label
4517 );
4518
4519 for i in ben_pd_copy_to_ben_one.g_sva_tbl_reused.first .. ben_pd_copy_to_ben_one.g_sva_tbl_reused.last
4520 loop
4521 create_process_log
4522 (p_module_cd => l_module_cd
4523 ,p_txn_id => p_copy_entity_txn_id
4524 ,p_message_text => l_value_start_tag ||l_para_spacer
4525 ||ben_pd_copy_to_ben_one.g_sva_tbl_reused(i).new_name
4526 ||l_value_end_tag||l_blank_line
4527 ,p_message_type_cd => l_value
4528 );
4529 end loop;
4530 end if;
4531
4532
4533 /*
4534 -- Action Types
4535 if ben_pd_copy_to_ben_one.g_eat_tbl_reused_count > 0 then
4536 create_process_log
4537 (p_module_cd => l_module_cd
4538 ,p_txn_id => p_copy_entity_txn_id
4539 ,p_message_text => l_label_start_tag||l_eat_label||l_label_end_tag||l_blank_line
4540 ,p_message_type_cd => l_label
4541 );
4542
4543 for i in ben_pd_copy_to_ben_one.g_eat_tbl_reused.first .. ben_pd_copy_to_ben_one.g_eat_tbl_reused.last
4544 loop
4545 create_process_log
4546 (p_module_cd => l_module_cd
4547 ,p_txn_id => p_copy_entity_txn_id
4548 ,p_message_text => l_value_start_tag ||l_para_spacer
4549 ||ben_pd_copy_to_ben_one.g_eat_tbl_reused(i).new_name
4550 ||l_value_end_tag||l_blank_line
4551 ,p_message_type_cd => l_value
4552 );
4553 end loop;
4554 end if;
4555 */
4556
4557 -- Combination PLIP
4558 if ben_pd_copy_to_ben_one.g_cpl_tbl_reused_count > 0 then
4559 create_process_log
4560 (p_module_cd => l_module_cd
4561 ,p_txn_id => p_copy_entity_txn_id
4562 ,p_message_text => l_label_start_tag||l_cpl_label||l_label_end_tag||l_blank_line
4563 ,p_message_type_cd => l_label
4564 );
4565
4566 for i in ben_pd_copy_to_ben_one.g_cpl_tbl_reused.first .. ben_pd_copy_to_ben_one.g_cpl_tbl_reused.last
4567 loop
4568 create_process_log
4569 (p_module_cd => l_module_cd
4570 ,p_txn_id => p_copy_entity_txn_id
4571 ,p_message_text => l_value_start_tag ||l_para_spacer
4572 ||ben_pd_copy_to_ben_one.g_cpl_tbl_reused(i).new_name
4573 ||l_value_end_tag||l_blank_line
4574 ,p_message_type_cd => l_value
4575 );
4576 end loop;
4577 end if;
4578
4579 -- Combination PTIP
4580 if ben_pd_copy_to_ben_one.g_cbp_tbl_reused_count > 0 then
4581 create_process_log
4582 (p_module_cd => l_module_cd
4583 ,p_txn_id => p_copy_entity_txn_id
4584 ,p_message_text => l_label_start_tag||l_cbp_label||l_label_end_tag||l_blank_line
4585 ,p_message_type_cd => l_label
4586 );
4587
4588 for i in ben_pd_copy_to_ben_one.g_cbp_tbl_reused.first .. ben_pd_copy_to_ben_one.g_cbp_tbl_reused.last
4589 loop
4590 create_process_log
4591 (p_module_cd => l_module_cd
4592 ,p_txn_id => p_copy_entity_txn_id
4593 ,p_message_text => l_value_start_tag ||l_para_spacer
4594 ||ben_pd_copy_to_ben_one.g_cbp_tbl_reused(i).new_name
4595 ||l_value_end_tag||l_blank_line
4596 ,p_message_type_cd => l_value
4597 );
4598 end loop;
4599 end if;
4600
4601 -- Combination PTIP OPT
4602 if ben_pd_copy_to_ben_one.g_cpt_tbl_reused_count > 0 then
4603 create_process_log
4604 (p_module_cd => l_module_cd
4605 ,p_txn_id => p_copy_entity_txn_id
4606 ,p_message_text => l_label_start_tag||l_cpt_label||l_label_end_tag||l_blank_line
4607 ,p_message_type_cd => l_label
4608 );
4609
4610 for i in ben_pd_copy_to_ben_one.g_cpt_tbl_reused.first .. ben_pd_copy_to_ben_one.g_cpt_tbl_reused.last
4611 loop
4612 create_process_log
4613 (p_module_cd => l_module_cd
4614 ,p_txn_id => p_copy_entity_txn_id
4615 ,p_message_text => l_value_start_tag ||l_para_spacer
4616 ||ben_pd_copy_to_ben_one.g_cpt_tbl_reused(i).new_name
4617 ||l_value_end_tag||l_blank_line
4618 ,p_message_type_cd => l_value
4619 );
4620 end loop;
4621 end if;
4622
4623 -- Fast Formulas
4624 if ben_pd_copy_to_ben_one.g_fff_tbl_reused_count > 0 then
4625 create_process_log
4626 (p_module_cd => l_module_cd
4627 ,p_txn_id => p_copy_entity_txn_id
4628 ,p_message_text => l_label_start_tag||l_fff_label||l_label_end_tag||l_blank_line
4629 ,p_message_type_cd => l_label
4630 );
4631
4632 for i in ben_pd_copy_to_ben_one.g_fff_tbl_reused.first .. ben_pd_copy_to_ben_one.g_fff_tbl_reused.last
4633 loop
4634 create_process_log
4635 (p_module_cd => l_module_cd
4636 ,p_txn_id => p_copy_entity_txn_id
4637 ,p_message_text => l_value_start_tag ||l_para_spacer
4638 ||ben_pd_copy_to_ben_one.g_fff_tbl_reused(i).new_name
4639 ||l_value_end_tag||l_blank_line
4640 ,p_message_type_cd => l_value
4641 );
4642 end loop;
4643 end if;
4644
4645 -- Create Spacer Line
4646 create_process_log
4647 (p_module_cd => l_module_cd
4648 ,p_txn_id => p_copy_entity_txn_id
4649 ,p_message_text => l_blank_line
4650 ,p_message_type_cd => l_spacer
4651 );
4652
4653 -- LOG NOMAPPING data
4654
4655 if ben_pd_copy_to_ben_three.g_not_copied_tbl_count > 0 then
4656 create_process_log
4657 (p_module_cd => l_module_cd
4658 ,p_txn_id => p_copy_entity_txn_id
4659 ,p_message_text => l_header_start_tag
4660 ||fnd_message.get_string('BEN','BEN_93286_PDC_NOT_COPIED_OBJ')
4661 ||l_header_end_tag
4662 ,p_message_type_cd => l_label
4663 );
4664
4665 -- Create Spacer Line
4666 create_process_log
4667 (p_module_cd => l_module_cd
4668 ,p_txn_id => p_copy_entity_txn_id
4669 ,p_message_text => l_blank_line||l_blank_line
4670 ,p_message_type_cd => l_spacer
4671 );
4672
4673
4674 for i in ben_pd_copy_to_ben_three.g_not_copied_tbl.first .. ben_pd_copy_to_ben_three.g_not_copied_tbl.last
4675 loop
4676 create_process_log
4677 (p_module_cd => l_module_cd
4678 ,p_txn_id => p_copy_entity_txn_id
4679 ,p_message_text => l_value_start_tag
4680 ||ben_pd_copy_to_ben_three.g_not_copied_tbl(i).text
4681 ||l_value_end_tag||l_blank_line
4682 ,p_message_type_cd => l_value
4683 );
4684 end loop;
4685 end if;
4686
4687 -- Create Spacer Line
4688 create_process_log
4689 (p_module_cd => l_module_cd
4690 ,p_txn_id => p_copy_entity_txn_id
4691 ,p_message_text => l_blank_line
4692 ,p_message_type_cd => l_spacer
4693 );
4694
4695 END IF;
4696
4697 end create_log;
4698
4699 -- 5097567 Added the following procedure
4700 -- ----------------------------------------------------------------------------
4701 -- |--------------------< submit_pd_and_compile_ff >--------------------------|
4702 -- ----------------------------------------------------------------------------
4703 -- This procedure is called in PDW flow to submit the Request Set
4704 -- First Stage, submit 'Plan Copy Submit Process'.
4705 -- Second Stage, submit 'Fast Formula Compilation Process'.
4706 --
4707 PROCEDURE submit_pd_and_compile_ff (
4708 p_validate IN NUMBER DEFAULT 0,
4709 p_copy_entity_txn_id IN NUMBER,
4710 p_effective_date IN VARCHAR2,
4711 p_prefix_suffix_text IN VARCHAR2 DEFAULT NULL,
4712 p_reuse_object_flag IN VARCHAR2 DEFAULT NULL,
4713 p_target_business_group_id IN VARCHAR2 DEFAULT NULL,
4714 p_prefix_suffix_cd IN VARCHAR2 DEFAULT NULL,
4715 p_effective_date_to_copy IN VARCHAR2 DEFAULT NULL,
4716 p_request_id OUT NOCOPY NUMBER
4717 )
4718 IS
4719 --
4720 l_success boolean;
4721 l_submit_failed exception;
4722 l_proc varchar2(80) := g_package ||'submit_pd_and_compile_ff';
4723 l_request_id number;
4724 --
4725 BEGIN
4726 --
4727 g_debug := hr_utility.debug_enabled;
4728 if (g_debug) then
4729 hr_utility.set_location(' Entering ' ||l_proc,10);
4730 hr_utility.set_location(' submit req.set BENPDSBMTCMPFF',10);
4731 end if;
4732 l_request_id := p_request_id;
4733 --
4734 l_success := fnd_submit.set_request_set
4735 (application => 'BEN',
4736 request_set => 'BENPDSBMTCMPFF');
4737 --
4738 if l_success then
4739 --
4740 if (g_debug) then
4741 hr_utility.set_location(' submit pgm BEPDCPRC',10);
4742 end if;
4743 --
4744 l_success := fnd_submit.submit_program
4745 (application => 'BEN'
4746 ,program => 'BEPDCPRC'
4747 ,stage => 'STAGE10'
4748 ,argument1 => p_validate
4749 ,argument2 => p_copy_entity_txn_id
4750 ,argument3 => p_effective_date
4751 ,argument4 => p_prefix_suffix_text
4752 ,argument5 => p_reuse_object_flag
4753 ,argument6 => p_target_business_group_id
4754 ,argument7 => p_prefix_suffix_cd
4755 ,argument8 => p_effective_date_to_copy
4756 );
4757 --
4758 if not l_success then
4759 raise l_submit_failed;
4760 end if;
4761 --
4762 if (g_debug) then
4763 hr_utility.set_location(' submit pgm BEPDCCF',10);
4764 end if;
4765 --
4766 l_success := fnd_submit.submit_program
4767 (application => 'BEN'
4768 ,program => 'BEPDCMFF'
4769 ,stage => 'STAGE20'
4770 ,argument1 => p_copy_entity_txn_id
4771 ,argument2 => p_effective_date
4772 );
4773 --
4774 if not l_success then
4775 raise l_submit_failed;
4776 end if;
4777 --
4778 if (g_debug) then
4779 hr_utility.set_location(' submit set',10);
4780 end if;
4781 --
4782 l_request_id := fnd_submit.submit_set(null,FALSE);
4783 --
4784 end if;
4785 --
4786 p_request_id := l_request_id;
4787 --
4788 EXCEPTION
4789 WHEN l_submit_failed THEN
4790 fnd_message.set_name('BEN','BEN_94215_PDC_ERR_CONC_PROG');
4791 fnd_message.raise_error ;
4792 --
4793 WHEN others THEN
4794 fnd_message.set_name('PER','PER_IN_ORACLE_GENERIC_ERROR');
4795 fnd_message.set_token('FUNCTION',l_proc);
4796 fnd_message.set_token('SQLERRM',SQLERRM);
4797 fnd_message.raise_error ;
4798 --
4799 END submit_pd_and_compile_ff;
4800 --
4801 --
4802 procedure submit_copy_request
4803 (
4804 p_validate in number default 0 -- false
4805 ,p_copy_entity_txn_id in number
4806 ,p_request_id out nocopy number
4807 ) is
4808
4809 cursor c_copy_entity_txn is
4810 select cet.process_name process_name
4811 ,to_char(cet.src_effective_date,'DD/MM/YYYY') src_effective_date
4812 ,cet.src_effective_date effective_date
4813 ,cet.target_typ_cd target_typ_cd
4814 ,cet.row_type_cd row_type_cd /* Bug 4278495 */
4815 ,cet.export_file_name
4816 ,cet.prefix_suffix_text
4817 ,cet.prefix_suffix_cd
4818 ,cet.reuse_object_flag
4819 ,cet.target_business_group_id
4820 ,cet.cet_object_version_number
4821 ,cet.action_date
4822 ,cet.sfl_step_name
4823 ,tcg.short_name
4824 from ben_copy_entity_txns_vw cet,
4825 pqh_transaction_categories tcg
4826 where cet.copy_entity_txn_id = p_copy_entity_txn_id
4827 and cet.transaction_category_id = tcg.transaction_category_id;
4828
4829 cursor c_chk_selection(c_copy_entity_txn_id in number
4830 ,c_number_of_copies in number)
4831 is
4832 select null
4833 from ben_copy_entity_results cer
4834 where copy_entity_txn_id = c_copy_entity_txn_id
4835 and information8 is not null
4836 and number_of_copies = c_number_of_copies
4837 and rownum = 1;
4838 --
4839 cursor c_fff is
4840 select null
4841 from ben_copy_entity_results cpe
4842 where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4843 and cpe.table_alias = 'FFF'
4844 and cpe.number_of_copies = 1
4845 and cpe.dml_operation in ('INSERT','UPDATE')
4846 and (cpe.datetrack_mode IN ('INSERT','CORRECTION')
4847 or cpe.datetrack_mode like 'UPDATE%');
4848
4849 --
4850 cursor c_cea is
4851 select copy_entity_attrib_id, object_version_number
4852 from pqh_copy_entity_attribs cea
4853 where copy_entity_txn_id = p_copy_entity_txn_id;
4854 --
4855
4856 l_copy_entity_txn c_copy_entity_txn%rowtype;
4857 l_process_name ben_copy_entity_txns_vw.process_name%type;
4858 l_target_type_cd ben_copy_entity_txns_vw.target_typ_cd%type;
4859 l_src_effective_date varchar2(15);
4860 l_export_file_name ben_copy_entity_txns_vw.export_file_name%type;
4861
4862 l_request_id number := null;
4863 l_mode_param varchar2(50) := 'DOWNLOAD';
4864 l_loader_config_param varchar2(50) := '@ben:/patch/115/import/bencer.lct';
4865 l_entity_param varchar2(50) := 'PQH_COPY_ENTITY_TXNS';
4866 l_proc varchar2(72) := g_package||'submit_copy_request';
4867 l_display_name_param varchar2(50) := 'DISPLAY_NAME=';
4868 l_src_effective_date_param varchar2(50) := 'SRC_EFFECTIVE_DATE=';
4869 l_dummy varchar2(1);
4870 --
4871 l_encoded_message varchar2(2000);
4872 l_app_short_name varchar2(2000);
4873 l_message_name varchar2(2000);
4874 -- REUSE
4875 l_reuse_object_flag varchar2(30) := null;
4876 l_second_request_id number ;
4877 --
4878 l_start_with pqh_copy_entity_txns.start_with%type;
4879 l_status varchar2(50) := null;
4880
4881 --TCS PDW Integration ENH
4882 l_errbuff varchar2(2000);
4883 l_return_cd number;
4884 --TCS PDW Integration ENH
4885 l_cea c_cea%rowtype;
4886 l_compile_ff boolean := false;
4887 --
4888 begin
4889 --
4890 fnd_msg_pub.initialize;
4891 --
4892 hr_utility.set_location('Entering:'|| l_proc, 10);
4893 --
4894 -- Issue a savepoint if operating in validation only mode
4895 --
4896 savepoint SUBMIT_COPY_REQUEST;
4897 --
4898 hr_utility.set_location(l_proc, 20);
4899 --
4900
4901 --Perform the following check only for PDW or PDC
4902 --TCS PDW Integration ENH
4903 if l_copy_entity_txn.ROW_TYPE_CD in ('PDW','PDC') then
4904 open c_chk_selection(p_copy_entity_txn_id,1);
4905 fetch c_chk_selection into l_dummy;
4906 if c_chk_selection%notfound then
4907 close c_chk_selection;
4908 fnd_message.set_name('BEN','BEN_93211_PDC_SELECT_OBJECT_ER');
4909 fnd_message.raise_error;
4910 end if;
4911 close c_chk_selection;
4912 end if;
4913 --TCS PDW Integration ENH
4914
4915 open c_copy_entity_txn;
4916 fetch c_copy_entity_txn into l_copy_entity_txn ;
4917 close c_copy_entity_txn;
4918
4919 if l_copy_entity_txn.target_typ_cd = 'BEN_PDFILE' then -- Export to File
4920 hr_utility.set_location('Entering:'|| l_proc, 10);
4921 --
4922 l_request_id := fnd_request.submit_request
4923 (application => 'BEN'
4924 ,program => 'BENPDCDL'
4925 ,description => NULL
4926 ,sub_request => FALSE
4927 ,argument1 => l_mode_param
4928 ,argument2 => l_loader_config_param
4929 ,argument3 => l_copy_entity_txn.export_file_name
4930 ,argument4 => l_entity_param
4931 ,argument5 => l_display_name_param||l_copy_entity_txn.process_name
4932 ,argument6 => l_src_effective_date_param||l_copy_entity_txn.src_effective_date);
4933 --
4934 -- p_request_id := l_request_id;
4935 -- Update status call here
4936 l_second_request_id := fnd_request.submit_request
4937 (application => 'BEN'
4938 ,program => 'BENPDDLS'
4939 ,description => NULL
4940 ,sub_request => FALSE
4941 ,argument1 => l_request_id
4942 ,argument2 => p_copy_entity_txn_id );
4943 --
4944 elsif l_copy_entity_txn.target_typ_cd in ('BEN_PDDFBG','BEN_PDSMBG','BEN_PDIMPT') then
4945
4946 --
4947 -- Copy to Same/Diff Business Group
4948 --
4949 --
4950 -- REUSE ENHANCEMENT
4951 l_reuse_object_flag := l_copy_entity_txn.reuse_object_flag;
4952 if l_copy_entity_txn.reuse_object_flag = 'YO' then
4953 --
4954 g_pgm_pl_prefix_suffix_text := l_copy_entity_txn.prefix_suffix_text;
4955 l_copy_entity_txn.prefix_suffix_text := null;
4956 --
4957 l_reuse_object_flag := 'Y';
4958 end if;
4959 --
4960
4961 if l_copy_entity_txn.ROW_TYPE_CD in ('PDW','PDC') then
4962
4963 --
4964 -- PDC:Change the status of transaction to "Copy In Progress" to prevent user from
4965 -- continuing / restarting the process before Conc Prog finishes which will set
4966 -- the status to COMPLETE / ERROR
4967 --
4968 -- PDW:Change the status of transaction to "Submit In Progress" to prevent user from
4969 -- continuing / restarting the process before Conc Prog finishes which will set
4970 -- the status to COMPLETE / ERROR
4971 l_compile_ff := false;
4972
4973 if l_copy_entity_txn.ROW_TYPE_CD = 'PDC' then
4974 l_status := 'COPY_IN_PROGRESS';
4975 elsif l_copy_entity_txn.ROW_TYPE_CD = 'PDW' then
4976 --
4977 l_status := 'SUBMIT_IN_PROGRESS';
4978 -- 5097567 Check if FFF rows exist that need to be compiled
4979 open c_fff;
4980 fetch c_fff into l_dummy;
4981 if c_fff%found then
4982 l_compile_ff := true;
4983 end if;
4984 end if;
4985 --
4986 pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
4987 (p_copy_entity_txn_id => p_copy_entity_txn_id
4988 ,p_datetrack_mode => hr_api.g_correction
4989 ,p_status => l_status
4990 ,p_start_with => NULL /* To disable Continue Icon */
4991 ,p_object_version_number => l_copy_entity_txn.cet_object_version_number
4992 ,p_effective_date => trunc(l_copy_entity_txn.effective_date)
4993 );
4994 --
4995 if (l_copy_entity_txn.ROW_TYPE_CD = 'PDC' OR NOT l_compile_ff) then
4996 --
4997 -- Submit Conc Prog "Copy Plan Design Process"
4998 --
4999 l_request_id := fnd_request.submit_request
5000 (application => 'BEN'
5001 ,program => 'BEPDCPRC'
5002 ,description => NULL
5003 ,sub_request => FALSE
5004 ,argument1 => p_validate
5005 ,argument2 => p_copy_entity_txn_id
5006 ,argument3 => to_char(l_copy_entity_txn.effective_date, 'DD-MM-YYYY')
5007 ,argument4 => l_copy_entity_txn.prefix_suffix_text
5008 ,argument5 => l_reuse_object_flag
5009 ,argument6 => l_copy_entity_txn.target_business_group_id
5010 ,argument7 => l_copy_entity_txn.prefix_suffix_cd
5011 ,argument8 => to_char(l_copy_entity_txn.action_date, 'DD-MM-YYYY')
5012 );
5013 --
5014 else
5015 -- 5097567 If FF Exists, compile the FF after succesful copying.
5016 --
5017 submit_pd_and_compile_ff
5018 (p_validate => p_validate
5019 ,p_copy_entity_txn_id => p_copy_entity_txn_id
5020 ,p_effective_date => to_char(l_copy_entity_txn.effective_date, 'DD-MM-YYYY')
5021 ,p_prefix_suffix_text => l_copy_entity_txn.prefix_suffix_text
5022 ,p_reuse_object_flag => l_reuse_object_flag
5023 ,p_target_business_group_id => l_copy_entity_txn.target_business_group_id
5024 ,p_prefix_suffix_cd => l_copy_entity_txn.prefix_suffix_cd
5025 ,p_effective_date_to_copy => to_char(l_copy_entity_txn.action_date, 'DD-MM-YYYY')
5026 ,p_request_id => l_request_id
5027 );
5028 --
5029 -- 5097567 Changes End
5030 --
5031 end if;
5032 --
5033 if l_request_id = 0
5034 then
5035 --
5036 fnd_message.set_name('BEN', 'BEN_94215_PDC_ERR_CONC_PROG');
5037 fnd_message.raise_error;
5038 --
5039 end if;
5040 --
5041 --TCS PDW Integration ENH
5042 elsif(l_copy_entity_txn.ROW_TYPE_CD = 'ELP') then
5043
5044 BEN_PLAN_DESIGN_COPY_PROCESS.process (
5045 errbuf => l_errbuff,
5046 retcode => l_return_cd,
5047 p_copy_entity_txn_id => p_copy_entity_txn_id,
5048 p_effective_date => to_char(l_copy_entity_txn.effective_date, 'DD-MM-YYYY'),
5049 p_prefix_suffix_text => l_copy_entity_txn.prefix_suffix_text,
5050 p_reuse_object_flag => l_reuse_object_flag,
5051 p_target_business_group_id => l_copy_entity_txn.target_business_group_id,
5052 p_prefix_suffix_cd => l_copy_entity_txn.prefix_suffix_cd,
5053 p_effective_date_to_copy => to_char(l_copy_entity_txn.action_date, 'DD-MM-YYYY')
5054 );
5055 --
5056 l_compile_ff := false;
5057 --
5058 open c_fff;
5059 fetch c_fff into l_dummy;
5060 if c_fff%found then
5061 l_compile_ff := true;
5062 end if;
5063 --
5064 if (l_compile_ff) then
5065 --
5066 l_request_id := fnd_request.submit_request
5067 (application => 'BEN'
5068 ,program => 'BEPDCMFF'
5069 ,description => NULL
5070 ,sub_request => FALSE
5071 ,argument1 => p_copy_entity_txn_id
5072 ,argument2 => to_char(l_copy_entity_txn.effective_date, 'DD-MM-YYYY'));
5073 --
5074
5075 end if;
5076
5077 --TCS PDW Integration ENH
5078 end if;
5079 --
5080 if (l_request_id <> 0) then
5081 --
5082 open c_cea;
5083 fetch c_cea into l_cea ;
5084 if c_cea%found then
5085 --
5086 hr_utility.set_location('l_cea.copy_entity_attrib_id ' || l_cea.copy_entity_attrib_id, 10);
5087 hr_utility.set_location('l_cea.ovn' || l_cea.object_version_number, 10);
5088 --
5089 -- Update Request Set Id into Information14 column of PQH_COPY_ENTITY_ATTRIBS table.
5090 pqh_copy_entity_attribs_api.update_copy_entity_attrib
5091 (p_copy_entity_attrib_id => l_cea.copy_entity_attrib_id
5092 ,p_object_version_number => l_cea.object_version_number
5093 ,p_effective_date => trunc(l_copy_entity_txn.effective_date)
5094 ,p_information14 => TO_CHAR(l_request_id)
5095 );
5096 end if;
5097 close c_cea;
5098 commit;
5099 --
5100 end if;
5101 -- Bug 4278495
5102 --
5103 hr_utility.set_location('After call to CET Update',200);
5104 --
5105 g_pgm_pl_prefix_suffix_text := null;
5106 --
5107 end if;
5108
5109 p_request_id := l_request_id;
5110
5111 -- When in validation only mode raise the Validate_Enabled exception
5112 --
5113 if p_validate = 1 then -- p_validate is true
5114 raise hr_API.validate_enabled;
5115 end if;
5116 --
5117 hr_utility.set_location(' Leaving:'||l_proc, 70);
5118 --
5119 exception
5120 --
5121 when hr_API.validate_enabled then
5122 --
5123 -- As the Validate_Enabled exception has been raised
5124 -- we must rollback to the savepoint
5125 --
5126 ROLLBACK TO SUBMIT_COPY_REQUEST;
5127 --
5128 -- Only set output warning arguments
5129 -- (Any key or derived arguments must be set to null
5130 -- when validation only mode is being used.)
5131 --
5132 p_request_id := null;
5133
5134 hr_utility.set_location(' Leaving:'||l_proc, 80);
5135 --
5136 when app_exception.application_exception then
5137
5138 fnd_msg_pub.add;
5139
5140 --
5141 when others then
5142 --
5143 -- A validation or unexpected error has occured
5144 --
5145 ROLLBACK TO SUBMIT_COPY_REQUEST;
5146 raise;
5147 --
5148 end submit_copy_request;
5149 --
5150
5151 --
5152 --
5153 --Mapping Page
5154 --
5155 procedure get_required_mapping_completed(p_copy_entity_txn_id in number
5156 ,p_required_mapping out nocopy varchar2) is
5157
5158 required_mapping varchar2(10) :='true';
5159 cursor c_required_mapping_completed(p_copy_entity_txn_id number) is
5160 select table_route_id
5161 from ben_copy_entity_results
5162 where copy_entity_txn_id = p_copy_entity_txn_id
5163 and information174 is not null
5164 and table_route_id in (select table_route_id
5165 from pqh_table_route
5166 where where_clause in('BEN_HRS_WKD_IN_PERD_FCTR',
5167 -- 'BEN_POPL_ORG_F',
5168 'BEN_COMP_LVL_FCTR',
5169 'BEN_ENRT_PERD')); -- Bug No 4498668
5170 --
5171 cursor c_table_name (p_table_route_id number) is
5172 select display_name
5173 from pqh_table_route
5174 where table_route_id = p_table_route_id;
5175
5176 l_table_name pqh_table_route.display_name%type;
5177 --
5178 begin
5179 p_required_mapping := ' ';
5180 --
5181 for l_rec in c_required_mapping_completed(p_copy_entity_txn_id)
5182 --
5183 loop
5184 --
5185 if (ben_plan_design_txns_api.get_mapping_info
5186 ('CompletedInfo',
5187 l_rec.table_route_id,
5188 p_copy_entity_txn_id)='NotCompleted') then
5189 open c_table_name(l_rec.table_route_id);
5190 fetch c_table_name into l_table_name;
5191 close c_table_name;
5192 --
5193 if(instr( p_required_mapping,l_table_name)=0) then
5194 p_required_mapping := p_required_mapping || l_table_name || ', ';
5195 end if;
5196 --
5197 end if;
5198 --
5199 end loop;
5200 --
5201 p_required_mapping := substr(p_required_mapping,1,length(p_required_mapping)-2);
5202 end get_required_mapping_completed;
5203
5204 --
5205 procedure get_mapping_column_name(p_table_route_id in number
5206 ,p_mapping_colum_name1 out nocopy varchar2
5207 ,p_mapping_colum_name2 out nocopy varchar2
5208 ,p_copy_entity_txn_id in number)is
5209 --
5210 cursor c_column_name(p_table_route_id number)is
5211 select attribute_name,refresh_col_name -- 3330990
5212 from pqh_attributes_vl att
5213 where enable_flag = 'Y'
5214 and master_table_route_id = p_table_route_id;
5215
5216 --
5217 begin
5218 --
5219 --Set out variables
5220 --
5221 for l_rec in c_column_name (p_table_route_id) loop
5222 --
5223 if(l_rec.refresh_col_name ='N' or l_rec.refresh_col_name is null) then
5224 --if(l_rec.refresh_col_name ='N' ) then
5225 p_mapping_colum_name1 := l_rec.attribute_name;
5226 else
5227 p_mapping_colum_name2 := l_rec.attribute_name;
5228 end if;
5229 --
5230 end loop;
5231 --
5232 end get_mapping_column_name;
5233 --
5234 procedure update_download_status(
5235 errbuf out nocopy varchar2
5236 ,retcode out nocopy number
5237 ,p_request_id in number
5238 ,p_copy_entity_txn_id in number
5239 )
5240 is
5241 l_phase varchar2(240);
5242 l_status varchar2(240);
5243 l_dev_phase varchar2(100);
5244 l_dev_status varchar2(100);
5245 l_message varchar2(2000);
5246 l_outcome boolean ;
5247 l_txn_status varchar2(100);
5248 l_ovn number ;
5249 l_effective_date date;
5250 cursor c_copy_entity_txn is
5251 select cet.src_effective_date effective_date
5252 ,cet.cet_object_version_number
5253 ,cet.sfl_step_name
5254 from ben_copy_entity_txns_vw cet
5255 where cet.copy_entity_txn_id = p_copy_entity_txn_id ;
5256 --
5257 l_start_with pqh_copy_entity_txns.start_with%type;
5258 begin
5259 --
5260 l_outcome :=
5261 fnd_concurrent.wait_for_request(
5262 request_id => p_request_id
5263 ,interval => 5
5264 ,max_wait => 36000 -- 10 Minutes
5265 ,phase => l_phase
5266 ,status => l_status
5267 ,dev_phase => l_dev_phase
5268 ,dev_status => l_dev_status
5269 ,message => l_message
5270 );
5271 --
5272 hr_utility.set_location(' p_request_id '||p_request_id,99);
5273 hr_utility.set_location(' p_copy_entity_txn_id '||p_copy_entity_txn_id,99);
5274 hr_utility.set_location(' l_phase '||l_phase,99);
5275 hr_utility.set_location(' l_status '||l_status,99);
5276 hr_utility.set_location(' l_dev_status '||l_dev_status,99);
5277 hr_utility.set_location(' l_dev_phase '||l_dev_phase ,99);
5278 hr_utility.set_location(' l_message '||l_message,99);
5279 --
5280
5281 open c_copy_entity_txn ;
5282 fetch c_copy_entity_txn into l_effective_date,l_ovn,l_start_with;
5283 close c_copy_entity_txn ;
5284
5285 if l_dev_status = 'ERROR' then
5286 --
5287 l_txn_status := l_dev_status ;
5288 --
5289 elsif l_dev_status = 'NORMAL' then
5290 --
5291 l_txn_status := 'COMPLETE' ;
5292 l_start_with := 'BEN_PDC_SLCT_TRGT_PAGE';
5293 --
5294 elsif l_dev_status in ( 'CANCELLED' , 'TERMINATED','DELETED' ) then
5295 --
5296 l_txn_status := 'INTERRUPTED' ;
5297 --
5298 else
5299 --
5300 l_txn_status := 'ERROR' ;
5301 --
5302 end if;
5303 --
5304 hr_utility.set_location(' l_txn_status '||l_txn_status,99);
5305 --
5306 --
5307 hr_utility.set_location('Before call to CET Update',100);
5308 --
5309 pqh_copy_entity_txns_api.update_COPY_ENTITY_TXN
5310 (
5311 p_copy_entity_txn_id => p_copy_entity_txn_id
5312 ,p_datetrack_mode => hr_api.g_correction
5313 ,p_status => l_txn_status
5314 ,p_start_with => l_start_with
5315 ,p_object_version_number => l_ovn
5316 ,p_effective_date => l_effective_date
5317 );
5318 --
5319 hr_utility.set_location('After call to CET Update',200);
5320 --
5321 end update_download_status ;
5322 --
5323
5324 function get_log_display(p_copy_entity_txn_id in number
5325 ,p_status in varchar2
5326 ,p_target_typ_cd in varchar2)
5327 return varchar2 is
5328
5329 cursor c_log_exists(c_copy_entity_txn_id number) is
5330 select null
5331 from pqh_process_log
5332 where txn_id = c_copy_entity_txn_id
5333 and module_cd = 'PDC_CP'
5334 and rownum = 1;
5335
5336 l_dummy varchar2(1);
5337 l_return_val varchar2(100);
5338 --
5339 begin
5340
5341 if p_status in ('COMPLETE', 'PURGED') and p_target_typ_cd <> 'BEN_PDFILE' then
5342 /* Bug 4306331 Added PURGED */
5343 open c_log_exists(p_copy_entity_txn_id);
5344 fetch c_log_exists into l_dummy;
5345 if c_log_exists%notfound then
5346 l_return_val := 'LogDisabled';
5347 else
5348 l_return_val := 'LogEnabled';
5349 end if;
5350
5351 else
5352 l_return_val := 'LogDisabled';
5353 end if;
5354 return l_return_val;
5355 --
5356 end get_log_display;
5357
5358 procedure write_txn_table_route(p_copy_entity_txn_id in number)
5359 is
5360 pragma AUTONOMOUS_TRANSACTION;
5361 cursor tr is select table_route_id,display_name,table_alias
5362 from pqh_table_route
5363 where from_clause ='OAB'
5364 and table_alias in ( select distinct table_alias from ben_copy_entity_results
5365 where copy_entity_txn_id = p_copy_entity_txn_id
5366 and table_route_id is null);
5367
5368 begin
5369 for i in tr loop
5370 update ben_copy_entity_results
5371 set table_route_id = i.table_route_id
5372 where copy_entity_txn_id = p_copy_entity_txn_id
5373 and table_alias = i.table_alias;
5374 end loop;
5375 -- commit the autonomous transaction
5376 commit;
5377 exception
5378 when others then
5379 rollback;
5380 raise;
5381 end write_txn_table_route;
5382
5383 procedure pdw_submit_copy_request(
5384 p_validate in number default 0 -- false
5385 ,p_copy_entity_txn_id in number
5386 ,p_request_id out nocopy number
5387 )
5388 is
5389 begin
5390 -- write the table_route_id
5391 write_txn_table_route(p_copy_entity_txn_id);
5392
5393 submit_copy_request(
5394 p_validate => p_validate
5395 ,p_copy_entity_txn_id => p_copy_entity_txn_id
5396 ,p_request_id => p_request_id
5397 );
5398
5399 end pdw_submit_copy_request;
5400
5401 --
5402 -- Bug 4281567 Procedure to purge PDC processes from concurrent request
5403 --
5404 procedure purge_plan_design_process(
5405 errbuf out nocopy varchar2 --needed by concurrent manager.
5406 ,retcode out nocopy number --needed by concurrent manager.
5407 ,p_process_id in number default null
5408 ,p_validate in varchar2
5409 ,p_effective_date in varchar2
5410 ,p_status in varchar2 default null
5411 ,p_transaction_short_name in varchar2
5412 ,p_retain_log in varchar2
5413 ,p_business_group_id in number
5414 )
5415 is
5416 cursor get_purge_processes (p_copy_entity_txn_id number
5417 ,p_status varchar2
5418 ,p_transaction_short_name varchar2
5419 ,p_effective_date date
5420 ,p_business_group_id number)
5421 is
5422 SELECT cet.copy_entity_txn_id, cet.process_name,
5423 cet.cet_object_version_number,
5424 cet.status, cet.src_effective_date,
5425 cet.target_typ_cd,
5426 decode(ben_plan_design_txns_api.get_log_display
5427 (cet.copy_entity_txn_id
5428 ,cet.status
5429 ,cet.target_typ_cd),
5430 'LogEnabled','Y',
5431 'LogDisabled','N', 'N') log_status
5432 FROM ben_copy_entity_txns_vw cet
5433 WHERE cet.transaction_category_id =
5434 (SELECT ptc.transaction_category_id
5435 FROM PQH_TRANSACTION_CATEGORIES ptc
5436 WHERE ptc.short_name = p_transaction_short_name)
5437 AND cet.copy_entity_txn_id = NVL (p_copy_entity_txn_id, cet.copy_entity_txn_id)
5438 AND cet.status = NVL (p_status, cet.status)
5439 AND cet.src_effective_date <= p_effective_date
5440 AND cet.context_business_group_id = p_business_group_id;
5441
5442 l_purge_processes get_purge_processes%rowtype;
5443 l_copy_entity_txn_id PQH_COPY_ENTITY_TXNS.copy_entity_txn_id%type;
5444 l_effective_date date;
5445 l_count number;
5446 l_retain_log varchar2(10);
5447 --
5448 begin
5449 ben_batch_utils.write('Start of conc prog - PURGE PLAN DESIGN COPY PROCESS -');
5450 --
5451 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
5452 l_retain_log := p_retain_log;
5453 --
5454 ben_batch_utils.write(' ');
5455 ben_batch_utils.write('Input Parameters are :-');
5456 ben_batch_utils.write('Validate :'||hr_general.decode_lookup ('BEN_DB_UPD_MD',p_validate));
5457 ben_batch_utils.write('Process Id :'||p_process_id);
5458 ben_batch_utils.write('Effective Date :'||l_effective_date);
5459 ben_batch_utils.write('Status :'||hr_general.decode_lookup ('BEN_PD_STATUS',p_status));
5460 ben_batch_utils.write('Transaction short Name :'||p_transaction_short_name);
5461 ben_batch_utils.write('Retain Log :'||p_retain_log);
5462 ben_batch_utils.write('Business Group Id :'||p_business_group_id);
5463 ben_batch_utils.write(' ');
5464 --
5465 l_count := 1;
5466 --
5467 open get_purge_processes(p_process_id
5468 ,p_status
5469 ,p_transaction_short_name
5470 ,l_effective_date
5471 ,p_business_group_id);
5472 loop
5473 --
5474 fetch get_purge_processes into l_purge_processes;
5475 exit when get_purge_processes%notfound;
5476 --
5477 -- Bug No 4349302 Log only processes with target_typ_cd <> 'BEN_PDVIEW'
5478 --
5479 if(nvl(l_purge_processes.target_typ_cd,'XXX') <> 'BEN_PDVIEW') then
5480 if (l_count = 1) then
5481 ben_batch_utils.write('============== List of Purged Processes =====================');
5482 ben_batch_utils.write(' ');
5483 end if;
5484 ben_batch_utils.write(l_count||') Process Name : '|| l_purge_processes.process_name
5485 || '(' || l_purge_processes.copy_entity_txn_id || ')');
5486 ben_batch_utils.write(' Status (Before Purge) : '||l_purge_processes.status);
5487 ben_batch_utils.write(' Effective Date : '|| l_purge_processes.src_effective_date);
5488 l_count := l_count + 1;
5489 ben_batch_utils.write(' ');
5490 end if;
5491 --
5492 /* Only if validate mode is Commit ('N'), delete the txns,
5493 for mode = rollback ('Y'), no need of actually calling delete,
5494 just list the processes to be deleted */
5495 if (p_validate = 'N') then
5496
5497 /* Check if log is maintained for this copy_entity_txn_id, if not, then
5498 no point in retaining the log and header record */
5499 --
5500 if(l_purge_processes.log_status = 'N') then
5501 l_retain_log := 'N';
5502 end if;
5503 --
5504 delete_PLAN_DESIGN_TXN
5505 (p_copy_entity_txn_id => l_purge_processes.copy_entity_txn_id
5506 ,p_cet_object_version_number => l_purge_processes.cet_object_version_number
5507 ,p_effective_date => l_effective_date
5508 ,p_retain_log => l_retain_log
5509 );
5510 --
5511 end if;
5512 --
5513 end loop;
5514 --
5515 close get_purge_processes;
5516 --
5517 if (l_count = 1) then
5518 ben_batch_utils.write('No processes found which match the given criteria.');
5519 ben_batch_utils.write(' ');
5520 end if;
5521 ben_batch_utils.write('Completed - PURGE PLAN DESIGN COPY PROCESS -');
5522 --
5523 end purge_plan_design_process;
5524
5525 end BEN_PLAN_DESIGN_TXNS_API;