[Home] [Help]
PACKAGE BODY: APPS.BEN_COPY_EXTRACT
Source
1 PACKAGE BODY ben_copy_extract AS
2 /* $Header: bexcpapi.pkb 120.5 2006/03/21 19:03:40 tjesumic noship $ */
3
4 -- Type Declaration
5
6 TYPE IdTyp is RECORD (
7 curr_Id NUMBER(15)
8 ,new_Id NUMBER(15)
9 );
10
11 TYPE ExtId IS TABLE OF IdTyp;
12
13 -- Package Variables
14 --
15 t_Formulas ben_copy_extract.FormulaID;
16 t_DinRId ExtId;
17 t_RinFId ExtId;
18
19 g_package VARCHAR2(33) := ' BEN_COPY_EXTRACT.';
20
21 g_truncated BOOLEAN; -- Has an entity name been truncated while prefixing?
22
23 g_msg_app varchar2(30) ; -- bug 2459050
24
25 --Package constants
26
30 FIX_NAME_LENGTH :
27 /* If the max length value of any of the following constants or any
28 new constants is more than 200(c_ExtName_Maxlen), please also make
29 the following change in the declaration section of the function
31 l_new_name VARCHAR2(<max length value of all the constants below>);
32
33 e.g.: As currently the maximum length value is 200, the declaration should be
34 l_new_name VARCHAR2(200);
35 */
36 c_FFName_Maxlen CONSTANT NUMBER := 80; -- Max length of FF Names
37 c_UDTName_Maxlen CONSTANT NUMBER := 80; -- Max length of UDT Names
38 -- Changed max length of extract entity names for UTF8
39 c_ExtName_Maxlen CONSTANT NUMBER := 600; -- Max length of Extract Entity Names
40
41
42 -- ----------------------------------------------------------------------------
43 -- |------------------------< ADD_FORMULA_ID >-------------------------------|
44 -- ----------------------------------------------------------------------------
45 PROCEDURE add_formula_id(p_formula_id IN NUMBER) IS
46 l_proc VARCHAR2(72) := g_package||'add_formula_id';
47 BEGIN
48 hr_utility.set_location('Entering:'|| l_proc, 10);
49 IF t_Formulas IS NULL THEN
50 t_Formulas(1) := p_formula_id;
51 ELSE
52 t_Formulas(t_Formulas.COUNT+1) := p_formula_id;
53 END IF;
54 hr_utility.set_location('Leaving:'|| l_proc, 20);
55 END; -- add_formula_id
56
57 -- ----------------------------------------------------------------------------
58 -- |------------------------< ADD_DinR_ID >-------------------------------|
59 -- ----------------------------------------------------------------------------
60 PROCEDURE add_DinR_id(p_curr_DinR_id IN NUMBER
61 ,p_new_DinR_id IN NUMBER) IS
62
63 l_proc VARCHAR2(72) := g_package||'add_DinR_id';
64 r_DinR IdTyp;
65
66 BEGIN
67 hr_utility.set_location('Entering:'|| l_proc, 10);
68
69 -- Assign values into record variable
70 r_DinR.curr_Id := p_curr_DinR_id;
71 r_DinR.new_Id := p_new_DinR_id;
72
73 IF t_DinRId IS NULL THEN
74 t_DinRId := ExtId(r_DinR);
75 ELSE
76 t_DinRId.EXTEND;
77 t_DinRId(t_DinRId.COUNT) := r_DinR;
78 END IF;
79
80 hr_utility.set_location('Leaving:'|| l_proc, 20);
81 END; -- add_DinR_id
82
83 -- ----------------------------------------------------------------------------
84 -- |------------------------< add_RinF_Id >----------------------------------|
85 -- ----------------------------------------------------------------------------
86 PROCEDURE add_RinF_id(p_curr_RinF_id IN NUMBER
87 ,p_new_RinF_id IN NUMBER) IS
88
89 l_proc VARCHAR2(72) := g_package||'add_RinF_Id';
90 r_RinF IdTyp;
91
92 BEGIN
93 hr_utility.set_location('Entering:'|| l_proc, 10);
94
95 -- Assign values into record variable
96 r_RinF.curr_Id := p_curr_RinF_id;
97 r_RinF.new_Id := p_new_RinF_id;
98
99 IF t_RinFId IS NULL THEN
100 t_RinFId := ExtId(r_RinF);
101 ELSE
102 t_RinFId.EXTEND;
103 t_RinFId(t_RinFId.COUNT) := r_RinF;
104 END IF;
105
106 hr_utility.set_location('Leaving:'|| l_proc, 20);
107 END; -- add_DinR_id
108
109 -- ----------------------------------------------------------------------------
110 -- |------------------------< GET_NEW_DATA_ELMT_ID >--------------------------|
111 -- ----------------------------------------------------------------------------
112 FUNCTION fix_name_length(p_curr_name IN VARCHAR2
113 ,p_name_maxlen IN NUMBER
114 ) RETURN VARCHAR2 IS
115
116 l_new_name ben_Ext_data_elmt.name%type ;
117
118 BEGIN
119
120 if length(nvl(p_curr_name,0)) > p_name_maxlen then
121 l_new_name := substr(p_curr_name,1,p_name_maxlen);
122 g_truncated := TRUE;
123 else
124 l_new_name := p_curr_name;
125 end if;
126
127 RETURN l_new_name;
128
129 END; -- fix_name_length
130
131 -- ----------------------------------------------------------------------------
132 -- |------------------------< GET_MSG_NAME >---------------------------------|
133 -- ----------------------------------------------------------------------------
134 FUNCTION get_msg_name RETURN VARCHAR2 IS
135
136 l_encoded_msg VARCHAR2(3000);
137 l_msg_name VARCHAR2(30);
138 l_msg_app VARCHAR2(50);
139 l_proc VARCHAR2(72) := g_package||'get_msg_name';
140
141 BEGIN
142 hr_utility.set_location('Entering:'|| l_proc, 10);
143
144 l_encoded_msg := fnd_message.get_encoded();
145 fnd_message.parse_encoded(
146 encoded_message => l_encoded_msg
147 ,app_short_name => l_msg_app -- OUT
148 ,message_name => l_msg_name -- OUT
149 );
150
151 g_msg_app := l_msg_app ; -- bug 2459050
152
153 hr_utility.set_location('Leaving:'|| l_proc, 20);
154
155 RETURN l_msg_name;
156
157 END; -- get_msg_name
158
159 -- ----------------------------------------------------------------------------
160 -- |------------------------< GET_NEW_DATA_ELMT_ID >--------------------------|
161 -- ----------------------------------------------------------------------------
162 FUNCTION get_new_data_elmt_id(p_curr_data_elmt_id IN NUMBER
163 ,p_new_extract_name IN VARCHAR2
164 ,p_business_group_id IN NUMBER
165 ) RETURN NUMBER IS
166
167 CURSOR c_new_data_elmt_id IS
168 SELECT ext_data_elmt_id
169 FROM ben_ext_data_elmt
170 WHERE (p_business_group_id is null
171 or p_business_group_id = business_group_id )
172 and name = (SELECT fix_name_length(p_new_extract_name||' '||name
173 ,c_ExtName_Maxlen)
174 FROM ben_ext_data_elmt
175 WHERE ext_data_elmt_id = p_curr_data_elmt_id);
176
177 l_new_data_elmt_id NUMBER(15) := NULL;
178 l_proc VARCHAR2(72) := g_package||'get_new_data_elmt_id';
179
180 BEGIN
181
182 hr_utility.set_location('Entering:'|| l_proc, 10);
183
184 OPEN c_new_data_elmt_id;
185 FETCH c_new_data_elmt_id into l_new_data_elmt_id;
186 CLOSE c_new_data_elmt_id;
187
188 hr_utility.set_location('Leaving:'|| l_proc, 20);
189
190 RETURN l_new_data_elmt_id;
191
192 END; -- get_new_data_elmt_id
193
194 -- ----------------------------------------------------------------------------
195 -- |------------------------< GET_NEW_WCDINR_DATA_ELMT_IN_RCD_ID >------------|
196 -- ----------------------------------------------------------------------------
197 FUNCTION get_new_WCDInR_DInR_id(p_curr_cond_DinR_id IN NUMBER
198 ,p_new_ext_rcd_id IN NUMBER
199 ,p_new_extract_name IN VARCHAR2
200 ,p_business_group_id in number
201 ) RETURN NUMBER IS
202 CURSOR c_new_data_elmt_in_rcd_id IS
203 SELECT ext_data_elmt_in_rcd_id
204 FROM ben_ext_data_elmt_in_rcd
205 WHERE ext_rcd_id = p_new_ext_rcd_id
206 AND ext_data_elmt_id = (SELECT ext_data_elmt_id
207 FROM ben_ext_data_elmt
208 WHERE (p_business_group_id is null or
209 p_business_group_id = business_group_id )
210 and name =
211 (SELECT fix_name_length(p_new_extract_name||' '||De.name
212 ,c_ExtName_Maxlen)
213 FROM ben_ext_data_elmt De
214 ,ben_ext_data_elmt_in_rcd DinR
215 WHERE DinR.ext_data_elmt_in_rcd_id = p_curr_cond_DinR_id
216 AND DinR.ext_data_elmt_id = De.ext_data_elmt_id
217 )
218 );
219
220 l_new_data_elmt_in_rcd_id NUMBER(15) := NULL;
221 l_proc VARCHAR2(72) := g_package||'get_new_WCDInR_DInR_id';
222
223 BEGIN
224
225 hr_utility.set_location('Entering:'|| l_proc, 10);
226
227 OPEN c_new_data_elmt_in_rcd_id;
228 FETCH c_new_data_elmt_in_rcd_id INTO l_new_data_elmt_in_rcd_id;
229 CLOSE c_new_data_elmt_in_rcd_id;
230
231 hr_utility.set_location('Leaving:'|| l_proc, 20);
232
233 RETURN l_new_data_elmt_in_rcd_id;
234
235
236 END; -- get_new_WCDInR_DInR_id
237
238 -- ----------------------------------------------------------------------------
239 -- |------------------------< get_new_WCRInF_DInR_id >------------------------|
240 -- ----------------------------------------------------------------------------
241 FUNCTION get_new_WCRInF_DInR_id(p_curr_cond_DinR_id IN NUMBER
242 ,p_new_rcd_in_file_id IN NUMBER
243 ,p_new_extract_name IN VARCHAR2
244 ) RETURN NUMBER IS
245
246 CURSOR c_new_data_elmt_in_rcd_id IS
247 SELECT ext_data_elmt_in_rcd_id
248 FROM ben_ext_data_elmt_in_rcd
249 WHERE ext_rcd_id = (SELECT ext_rcd_id
250 FROM ben_ext_rcd_in_file
251 WHERE ext_rcd_in_file_id = p_new_rcd_in_file_id
252 )
253 AND ext_data_elmt_id = (SELECT ext_data_elmt_id
254 FROM ben_ext_data_elmt
255 WHERE name =
256 (SELECT fix_name_length(p_new_extract_name||' '||De.name
257 ,c_ExtName_Maxlen)
258 FROM ben_ext_data_elmt De
259 ,ben_ext_data_elmt_in_rcd DinR
260 WHERE DinR.ext_data_elmt_in_rcd_id = p_curr_cond_DinR_id
261 AND DinR.ext_data_elmt_id = De.ext_data_elmt_id
262 )
263 );
264
265 l_new_data_elmt_in_rcd_id NUMBER(15) := NULL;
266 l_proc VARCHAR2(72) := g_package||'get_new_WCRInF_DInR_id';
267
268 BEGIN
269
270 hr_utility.set_location('Entering:'|| l_proc, 10);
271
272 OPEN c_new_data_elmt_in_rcd_id;
273 FETCH c_new_data_elmt_in_rcd_id INTO l_new_data_elmt_in_rcd_id;
274 CLOSE c_new_data_elmt_in_rcd_id;
275
276 hr_utility.set_location('Leaving:'|| l_proc, 20);
277
278 RETURN l_new_data_elmt_in_rcd_id;
279
280
281 END; -- get_new_WCRInF_DInR_id
282
283 -- ----------------------------------------------------------------------------
284 -- |------------------------< GET_NEW_RCD_ID >--------------------------------|
285 -- ----------------------------------------------------------------------------
286 FUNCTION get_new_rcd_id(p_curr_rcd_id IN NUMBER
287 ,p_new_extract_name IN VARCHAR2
288 ,p_business_group_id in number default null
289 ) RETURN NUMBER IS
290
291 CURSOR c_new_rcd_id IS
292 SELECT ext_rcd_id
293 FROM ben_ext_rcd
294 WHERE (p_business_group_id is null
295 or p_business_group_id = business_group_id)
296 and name = (SELECT fix_name_length(p_new_extract_name||' '||name
297 ,c_ExtName_Maxlen)
298 FROM ben_ext_rcd
299 WHERE ext_rcd_id = p_curr_rcd_id);
300
301 l_new_rcd_id NUMBER(15) := NULL;
302 l_proc VARCHAR2(72) := g_package||'get_new_rcd_id';
303
304 BEGIN
305
306 hr_utility.set_location('Entering:'|| l_proc, 10);
307
308 OPEN c_new_rcd_id;
309 FETCH c_new_rcd_id into l_new_rcd_id;
310 CLOSE c_new_rcd_id;
311
312 hr_utility.set_location('Leaving:'|| l_proc, 20);
313 RETURN l_new_rcd_id;
314
315 END; -- get_new_rcd_id
316
317 -- ----------------------------------------------------------------------------
318 -- |------------------------< GET_NEW_FORMULA_ID >----------------------------|
319 -- ----------------------------------------------------------------------------
320 FUNCTION get_new_formula_id(p_new_formula_name IN VARCHAR2
321 ,p_business_group_id IN NUMBER
322 ) RETURN NUMBER IS
323
324 CURSOR c_formula_id IS
325 SELECT formula_id
326 FROM ff_formulas_f
327 WHERE formula_name = p_new_formula_name
328 AND business_group_id = p_business_group_id
329 AND legislation_code IS NULL;
330
331 l_formula_id NUMBER(9);
332 l_proc VARCHAR2(72) := g_package||'get_new_formula_id';
333
334 BEGIN
335
336 hr_utility.set_location('Entering:'|| l_proc, 10);
337
338 OPEN c_formula_id;
339 FETCH c_formula_id INTO l_formula_id;
340 CLOSE c_formula_id;
341
342 hr_utility.set_location('Leaving:'|| l_proc, 20);
343
344 RETURN l_formula_id;
345
346 END; -- get_new_formula_id
347
348 -- ----------------------------------------------------------------------------
349 -- |------------------------< COPY_FORMULA >----------------------------------|
350 -- ----------------------------------------------------------------------------
351 FUNCTION copy_formula(p_curr_formula_id IN NUMBER
352 ,p_new_extract_name IN VARCHAR2
353 ,p_business_group_id IN NUMBER
354 ,p_legislation_code IN VARCHAR2
355 ) RETURN NUMBER IS
356
357 CURSOR c_formula IS
358 SELECT *
359 FROM ff_formulas_f
360 WHERE formula_id = p_curr_formula_id
361 AND ((business_group_id IS NULL AND legislation_code IS NULL)
362 OR (legislation_code IS NOT NULL
363 AND legislation_code = p_legislation_code)
364 OR (business_group_id IS NOT NULL
365 AND business_group_id = p_business_group_id)
366 );
367
368 -- Local Record Variables
369 r_curr_formula c_formula%ROWTYPE;
370
371 -- Local Variables
372 l_new_formula_id NUMBER(15);
373 l_new_formula_name ff_formulas_f.formula_name%TYPE;
374 l_new_row_id ROWID;
375 l_new_last_update_date DATE;
376 l_proc VARCHAR2(72) := g_package||'copy_formula';
377
378 l_msg_name varchar2(80); -- bug 2459050
379 l_FF93_FORMULA_txt varchar2(80);
380
381 BEGIN
382
383 hr_utility.set_location('Entering:'|| l_proc, 10);
384
385 OPEN c_formula;
386 FETCH c_formula INTO r_curr_formula;
387 CLOSE c_formula;
388
389 l_new_row_id := NULL;
390 l_new_formula_id := NULL;
391
392 -- Changed for UTF8
393 -- l_new_formula_name := upper(p_new_extract_name)||'_'||r_curr_formula.formula_name;
394 -- l_new_formula_name := fix_name_length(l_new_formula_name, c_FFName_Maxlen);
395 l_new_formula_name := fix_name_length(upper(p_new_extract_name)||'_'||r_curr_formula.formula_name
396 ,c_FFName_Maxlen
397 );
398 l_new_last_update_date := r_curr_formula.last_update_date;
399
400
401 BEGIN -- Insert into FF_FORMULAS_F using Row Handler
402 ff_formulas_f_pkg.insert_Row(
403 X_Rowid => l_new_row_id -- IN OUT
404 ,X_Formula_Id => l_new_formula_id -- IN OUT
405 ,X_Effective_Start_Date => r_curr_formula.effective_start_date
406 ,X_Effective_End_Date => r_curr_formula.effective_end_date
407 ,X_Business_Group_Id => p_business_group_id
408 ,X_Legislation_Code => NULL
409 ,X_Formula_Type_Id => r_curr_formula.formula_type_id
410 ,X_Formula_Name => l_new_formula_name -- IN OUT
411 ,X_Description => r_curr_formula.description
412 ,X_Formula_Text => r_curr_formula.formula_text
413 ,X_Sticky_Flag => r_curr_formula.sticky_Flag
414 ,X_Last_Update_Date => l_new_last_update_date -- IN OUT
415 );
416
417 -- Add the new formula id to list
418 add_formula_id(l_new_formula_id);
419
420 EXCEPTION
421
422 WHEN OTHERS THEN
423 --
424 -- bug 2459050 - this error needs to be reported, hence replacing RAISE with
425 -- fnd_message.raise_error
426 --
427 l_msg_name := get_msg_name();
428 IF l_msg_name <> 'FF52_NAME_ALREADY_USED' THEN
429 --
430 fnd_message.set_name ('FF', 'FF93_FORMULA');
431 l_FF93_FORMULA_txt := fnd_message.get;
432 --
433 fnd_message.set_name (g_msg_app, l_msg_name);
434 if (l_msg_name = 'FFHR_6016_ALL_RES_WORDS') then
435 fnd_message.set_token('VALUE_NAME', nvl(l_new_formula_name, l_FF93_FORMULA_txt) );
436 end if;
437 fnd_message.raise_error;
438 -- RAISE;
439 -- end fix 2459050
440 ELSE
441 -- Formula already created, find new formula Id here
442 l_new_formula_id := get_new_formula_id(l_new_formula_name
443 ,p_business_group_id
444 );
445 END IF; -- get_msg_name() <> 'FF52_NAME_ALREADY_USED'
446
447 END; -- Insert into FF_FORMULAS_F using Row Handler
448
449 hr_utility.set_location('Leaving:'|| l_proc, 20);
450
451 RETURN l_new_formula_id;
452
453 END; -- copy_formula
454
455 -- ----------------------------------------------------------------------------
456 -- |------------------------< COPY_CRITERIA_DEFINITION >----------------------|
457 -- ----------------------------------------------------------------------------
458 PROCEDURE copy_criteria_definition(p_ext_crit_prfl_id IN NUMBER
459 ,p_new_extract_name IN VARCHAR2
460 ,p_business_group_id IN NUMBER
461 ,p_legislation_code IN VARCHAR2
462 ,p_effective_date IN DATE
463 ,p_new_ext_crit_prfl_id OUT NOCOPY NUMBER
464 ) IS
465
466 CURSOR c_ext_crit_prfl IS
467 SELECT *
468 FROM ben_ext_crit_prfl
469 WHERE ext_crit_prfl_id = p_ext_crit_prfl_id
470 AND ((business_group_id IS NULL AND legislation_code IS NULL)
471 OR (legislation_code IS NOT NULL
472 AND legislation_code = p_legislation_code)
473 OR (business_group_id IS NOT NULL
474 AND business_group_id = p_business_group_id)
475 );
476
477
478 CURSOR c_ext_crit_typ(p_ext_crit_prfl_id IN NUMBER) IS
479 SELECT *
480 FROM ben_ext_crit_typ
481 WHERE ext_crit_prfl_id = p_ext_crit_prfl_id
482 AND ((business_group_id IS NULL AND legislation_code IS NULL)
483 OR (legislation_code IS NOT NULL
484 AND legislation_code = p_legislation_code)
485 OR (business_group_id IS NOT NULL
486 AND business_group_id = p_business_group_id)
487 );
488
489 CURSOR c_ext_crit_val(p_ext_crit_typ_id IN NUMBER) IS
490 SELECT *
491 FROM ben_ext_crit_val
492 WHERE ext_crit_typ_id = p_ext_crit_typ_id
493 AND ((business_group_id IS NULL AND legislation_code IS NULL)
494 OR (legislation_code IS NOT NULL
495 AND legislation_code = p_legislation_code)
496 OR (business_group_id IS NOT NULL
497 AND business_group_id = p_business_group_id)
498 );
499
500 CURSOR c_ext_crit_cmbn(p_ext_crit_val_id IN NUMBER) IS
501 SELECT *
502 FROM ben_ext_crit_cmbn
503 WHERE ext_crit_val_id = p_ext_crit_val_id
504 AND ((business_group_id IS NULL AND legislation_code IS NULL)
505 OR (legislation_code IS NOT NULL
506 AND legislation_code = p_legislation_code)
507 OR (business_group_id IS NOT NULL
511 -- Local Record Variables
508 AND business_group_id = p_business_group_id)
509 );
510
512 r_curr_ext_crit_prlf c_ext_crit_prfl%ROWTYPE;
513 r_curr_ext_crit_typ c_ext_crit_typ%ROWTYPE;
514 r_curr_ext_crit_val c_ext_crit_val%ROWTYPE;
515 r_curr_ext_crit_cmbn c_ext_crit_cmbn%ROWTYPE;
516
517
518 -- Local Variables
519 l_new_ext_crit_prfl_id NUMBER(15);
520 l_new_ext_crit_typ_id NUMBER(15);
521 l_new_ext_crit_val_id NUMBER(15);
522 l_new_ext_crit_cmbn_id NUMBER(15);
523 l_new_val_1 ben_ext_crit_val.val_1%type ;
524
525
526 l_new_object_version_number NUMBER(9);
527 l_proc VARCHAR2(72) := g_package||'copy_criteria_definition';
528
529 BEGIN
530
531 hr_utility.set_location('Entering:'|| l_proc, 10);
532
533 FOR r_curr_ext_crit_prfl IN c_ext_crit_prfl
534 LOOP -- 2 Get Criteria Profile data for current EXT_DFN_ID
535
536 -- Insert into BEN_EXT_CRIT_PRFL using Row Handler
537 ben_xcr_ins.ins
538 (
539 p_ext_crit_prfl_id => l_new_ext_crit_prfl_id -- OUT
540 ,p_name => fix_name_length
541 (p_new_extract_name||' '||r_curr_ext_crit_prfl.name
542 ,c_ExtName_Maxlen)
543 ,p_business_group_id => p_business_group_id
544 ,p_legislation_code => NULL
545 ,p_xcr_attribute_category => r_curr_ext_crit_prfl.xcr_attribute_category
546 ,p_xcr_attribute1 => r_curr_ext_crit_prfl.xcr_attribute1
547 ,p_xcr_attribute2 => r_curr_ext_crit_prfl.xcr_attribute2
548 ,p_xcr_attribute3 => r_curr_ext_crit_prfl.xcr_attribute3
549 ,p_xcr_attribute4 => r_curr_ext_crit_prfl.xcr_attribute4
550 ,p_xcr_attribute5 => r_curr_ext_crit_prfl.xcr_attribute5
551 ,p_xcr_attribute6 => r_curr_ext_crit_prfl.xcr_attribute6
552 ,p_xcr_attribute7 => r_curr_ext_crit_prfl.xcr_attribute7
553 ,p_xcr_attribute8 => r_curr_ext_crit_prfl.xcr_attribute8
554 ,p_xcr_attribute9 => r_curr_ext_crit_prfl.xcr_attribute9
555 ,p_xcr_attribute10 => r_curr_ext_crit_prfl.xcr_attribute10
556 ,p_xcr_attribute11 => r_curr_ext_crit_prfl.xcr_attribute11
557 ,p_xcr_attribute12 => r_curr_ext_crit_prfl.xcr_attribute12
558 ,p_xcr_attribute13 => r_curr_ext_crit_prfl.xcr_attribute13
559 ,p_xcr_attribute14 => r_curr_ext_crit_prfl.xcr_attribute14
560 ,p_xcr_attribute15 => r_curr_ext_crit_prfl.xcr_attribute15
561 ,p_xcr_attribute16 => r_curr_ext_crit_prfl.xcr_attribute16
562 ,p_xcr_attribute17 => r_curr_ext_crit_prfl.xcr_attribute17
563 ,p_xcr_attribute18 => r_curr_ext_crit_prfl.xcr_attribute18
564 ,p_xcr_attribute19 => r_curr_ext_crit_prfl.xcr_attribute19
565 ,p_xcr_attribute20 => r_curr_ext_crit_prfl.xcr_attribute20
566 ,p_xcr_attribute21 => r_curr_ext_crit_prfl.xcr_attribute21
567 ,p_xcr_attribute22 => r_curr_ext_crit_prfl.xcr_attribute22
568 ,p_xcr_attribute23 => r_curr_ext_crit_prfl.xcr_attribute23
569 ,p_xcr_attribute24 => r_curr_ext_crit_prfl.xcr_attribute24
570 ,p_xcr_attribute25 => r_curr_ext_crit_prfl.xcr_attribute25
571 ,p_xcr_attribute26 => r_curr_ext_crit_prfl.xcr_attribute26
572 ,p_xcr_attribute27 => r_curr_ext_crit_prfl.xcr_attribute27
573 ,p_xcr_attribute28 => r_curr_ext_crit_prfl.xcr_attribute28
574 ,p_xcr_attribute29 => r_curr_ext_crit_prfl.xcr_attribute29
575 ,p_xcr_attribute30 => r_curr_ext_crit_prfl.xcr_attribute30
576 ,p_ext_global_flag => nvl(r_curr_ext_crit_prfl.ext_global_flag, 'N')
577 ,p_object_version_number => l_new_object_version_number -- OUT
578 );
579
580 FOR r_curr_ext_crit_typ IN c_ext_crit_typ(r_curr_ext_crit_prfl.ext_crit_prfl_id)
581 LOOP -- 3 Get Criteria Type data for current EXT_CRIT_PRFL_ID
582
583 -- Insert into BEN_EXT_CRIT_TYP using Row Handler
584 ben_xct_ins.ins
585 (
586 p_ext_crit_typ_id => l_new_ext_crit_typ_id -- OUT
587 ,p_crit_typ_cd => r_curr_ext_crit_typ.crit_typ_cd
588 ,p_ext_crit_prfl_id => l_new_ext_crit_prfl_id
589 ,p_business_group_id => p_business_group_id
590 ,p_legislation_code => NULL
591 ,p_object_version_number => l_new_object_version_number -- OUT
592 ,p_effective_date => p_effective_date
593 ,p_excld_flag => r_curr_ext_crit_typ.excld_flag
594 );
595
596 FOR r_curr_ext_crit_val IN c_ext_crit_val(r_curr_ext_crit_typ.ext_crit_typ_id)
597 LOOP -- 4 Get Criteria Value data for current EXT_CRIT_TYP_ID
598
599 /* IF Data Element Type = RULE then
600 Copy Formula and obtain new DATA_ELMT_RL */
601 l_new_val_1 := NULL;
602
603 IF r_curr_ext_crit_typ.crit_typ_cd = 'PRL' THEN
604
605 l_new_val_1 :=
606 copy_formula(
607 p_curr_formula_id => r_curr_ext_crit_val.val_1
608 ,p_new_extract_name => p_new_extract_name
609 ,p_business_group_id => p_business_group_id
610 ,p_legislation_code => p_legislation_code
611 );
612
613 ELSE
614 l_new_val_1 := r_curr_ext_crit_val.val_1;
615 END IF; -- r_curr_ext_crit_typ.crit_typ_cd = 'PRL'
616
617 -- Insert into BEN_EXT_CRIT_VAL using Row Handler
618 ben_xcv_ins.ins
619 (
620 p_effective_date => p_effective_date
621 ,p_ext_crit_val_id => l_new_ext_crit_val_id -- OUT
622 ,p_val_1 => l_new_val_1
623 ,p_val_2 => r_curr_ext_crit_val.val_2
624 ,p_ext_crit_typ_id => l_new_ext_crit_typ_id
625 ,p_business_group_id => p_business_group_id
626 ,p_legislation_code => NULL
627 ,p_ext_crit_bg_id => r_curr_ext_crit_val.ext_crit_bg_id
628 ,p_object_version_number => l_new_object_version_number -- OUT
629 );
630
631 FOR r_curr_ext_crit_cmbn IN c_ext_crit_cmbn(r_curr_ext_crit_val.ext_crit_val_id)
632 LOOP -- 5 Get Criteria Combination data for current EXT_CRIT_VAL_ID
633
634 -- Insert into BEN_EXT_CRIT_CMBN using Row Handler
635 ben_xcc_ins.ins
636 (
637 p_ext_crit_cmbn_id => l_new_ext_crit_cmbn_id -- OUT
638 ,p_crit_typ_cd => r_curr_ext_crit_cmbn.crit_typ_cd
639 ,p_oper_cd => r_curr_ext_crit_cmbn.oper_cd
640 ,p_val_1 => r_curr_ext_crit_cmbn.val_1
641 ,p_val_2 => r_curr_ext_crit_cmbn.val_2
642 ,p_ext_crit_val_id => l_new_ext_crit_val_id
643 ,p_business_group_id => p_business_group_id
644 ,p_legislation_code => NULL
645 ,p_object_version_number => l_new_object_version_number -- OUT
646 ,p_effective_date => p_effective_date
647 );
648
649 END LOOP; -- 5
650
651 END LOOP; -- 4
652
653 END LOOP; -- 3
654
655 END LOOP; -- 2
656
657 -- Assign the New Extract Criteria Profile Id to return variable
658 p_new_ext_crit_prfl_id := l_new_ext_crit_prfl_id;
659
660 hr_utility.set_location('Leaving:'|| l_proc, 20);
661
662 END copy_criteria_definition; -- copy_criteria_definition
663
664
665 -- ----------------------------------------------------------------------------
666 -- |------------------------< COPY_FILE_LAYOUT >------------------------------|
667 -- ----------------------------------------------------------------------------
668 PROCEDURE copy_file_layout(p_ext_file_id IN NUMBER
669 ,p_new_extract_name IN VARCHAR2
670 ,p_business_group_id IN NUMBER
671 ,p_legislation_code IN VARCHAR2
672 ,p_effective_date IN DATE
673 ,p_new_ext_file_id OUT NOCOPY NUMBER
674 ) IS
675
676 CURSOR c_ext_file IS
677 SELECT *
678 FROM ben_ext_file
679 WHERE ext_file_id = p_ext_file_id
680 AND ((business_group_id IS NULL AND legislation_code IS NULL)
681 OR (legislation_code IS NOT NULL
682 AND legislation_code = p_legislation_code)
683 OR (business_group_id IS NOT NULL
684 AND business_group_id = p_business_group_id)
685 );
686
687 CURSOR c_ext_rcd_in_file(p_ext_file_id IN NUMBER) IS
688 SELECT RinF.*
689 FROM ben_ext_rcd_in_file RinF, ben_ext_rcd Rcd
690 WHERE RinF.ext_file_id = p_ext_file_id
691 AND RinF.ext_rcd_id = Rcd.ext_rcd_id
692 AND ((RinF.business_group_id IS NULL AND RinF.legislation_code IS NULL)
693 OR (RinF.legislation_code IS NOT NULL
694 AND RinF.legislation_code = p_legislation_code)
695 OR (RinF.business_group_id IS NOT NULL
696 AND RinF.business_group_id = p_business_group_id)
697 )
698 ORDER BY decode(Rcd.rcd_type_cd,'D',1,'H',2,'T',3)
699 ,RinF.seq_num;
700
701 CURSOR c_ext_rcd(p_ext_rcd_id IN NUMBER) IS
702 SELECT *
703 FROM ben_ext_rcd
704 WHERE ext_rcd_id = p_ext_rcd_id
705 AND ((business_group_id IS NULL AND legislation_code IS NULL)
706 OR (legislation_code IS NOT NULL
707 AND legislation_code = p_legislation_code)
708 OR (business_group_id IS NOT NULL
709 AND business_group_id = p_business_group_id)
710 );
711
712 CURSOR c_RinF_where_clause(p_ext_rcd_in_file_id IN NUMBER) IS
713 SELECT *
714 FROM ben_ext_where_clause
715 WHERE ext_rcd_in_file_id = p_ext_rcd_in_file_id
716 AND ((business_group_id IS NULL AND legislation_code IS NULL)
717 OR (legislation_code IS NOT NULL
718 AND legislation_code = p_legislation_code)
719 OR (business_group_id IS NOT NULL
720 AND business_group_id = p_business_group_id)
721 );
725 WHERE ext_rcd_in_file_id = p_ext_rcd_in_file_id
722
723 CURSOR c_RinF_incl_chg(p_ext_rcd_in_file_id IN NUMBER) IS
724 SELECT * from ben_ext_incl_chg
726 AND ((business_group_id IS NULL AND legislation_code IS NULL)
727 OR (legislation_code IS NOT NULL
728 AND legislation_code = p_legislation_code)
729 OR (business_group_id IS NOT NULL
730 AND business_group_id = p_business_group_id)
731 );
732
733 CURSOR c_ext_data_elmt_in_rcd(p_ext_rcd_id IN NUMBER) IS
734 SELECT DinR.*
735 FROM ben_ext_data_elmt_in_rcd DinR, ben_ext_data_elmt DElmt
736 WHERE DinR.ext_rcd_id = p_ext_rcd_id
737 AND Dinr.ext_data_elmt_id = DElmt.ext_data_elmt_id
738 AND ((DinR.business_group_id IS NULL AND DinR.legislation_code IS NULL)
739 OR (DinR.legislation_code IS NOT NULL
740 AND DinR.legislation_code = p_legislation_code)
741 OR (DinR.business_group_id IS NOT NULL
742 AND DinR.business_group_id = p_business_group_id)
743 )
744 ORDER BY decode(DElmt.data_elmt_typ_cd,'T',1,'C',2,0), DinR.seq_num;
745
746 CURSOR c_ext_data_elmt(p_ext_data_elmt_id IN NUMBER) IS
747 SELECT *
748 FROM ben_ext_data_elmt
749 WHERE ext_data_elmt_id = p_ext_data_elmt_id
750 AND ((business_group_id IS NULL AND legislation_code IS NULL)
751 OR (legislation_code IS NOT NULL
752 AND legislation_code = p_legislation_code)
753 OR (business_group_id IS NOT NULL
754 AND business_group_id = p_business_group_id)
755 );
756
757 CURSOR c_ext_data_elmt_decd(p_ext_data_elmt_id IN NUMBER) IS
758 SELECT *
759 FROM ben_ext_data_elmt_decd
760 WHERE ext_data_elmt_id = p_ext_data_elmt_id
761 AND ((business_group_id IS NULL AND legislation_code IS NULL)
762 OR (legislation_code IS NOT NULL
763 AND legislation_code = p_legislation_code)
764 OR (business_group_id IS NOT NULL
765 AND business_group_id = p_business_group_id)
766 );
767
768 CURSOR c_DElmt_where_clause(p_ext_data_elmt_id IN NUMBER) IS
769 SELECT *
770 FROM ben_ext_where_clause
771 WHERE ext_data_elmt_id = p_ext_data_elmt_id
772 AND ((business_group_id IS NULL AND legislation_code IS NULL)
773 OR (legislation_code IS NOT NULL
774 AND legislation_code = p_legislation_code)
775 OR (business_group_id IS NOT NULL
776 AND business_group_id = p_business_group_id)
777 );
778
779 CURSOR c_DinR_where_clause(p_ext_data_elmt_in_rcd_id IN NUMBER) IS
780 SELECT *
781 FROM ben_ext_where_clause
782 WHERE ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
783 AND ((business_group_id IS NULL AND legislation_code IS NULL)
784 OR (legislation_code IS NOT NULL
785 AND legislation_code = p_legislation_code)
786 OR (business_group_id IS NOT NULL
787 AND business_group_id = p_business_group_id)
788 );
789
790 CURSOR c_DinR_incl_chg(p_ext_data_elmt_in_rcd_id IN NUMBER) IS
791 SELECT * from ben_ext_incl_chg
792 WHERE ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
793 AND ((business_group_id IS NULL AND legislation_code IS NULL)
794 OR (legislation_code IS NOT NULL
795 AND legislation_code = p_legislation_code)
796 OR (business_group_id IS NOT NULL
797 AND business_group_id = p_business_group_id)
798 );
799
800 -- Local Record Variables
801
802 r_curr_ext_file c_ext_file%ROWTYPE;
803 r_curr_ext_rcd_in_file c_ext_rcd_in_file%ROWTYPE;
804 r_curr_ext_rcd c_ext_rcd%ROWTYPE;
805 r_curr_RinF_where_clause c_RinF_where_clause%ROWTYPE;
806 r_curr_RinF_incl_chg c_RinF_incl_chg%ROWTYPE;
807 r_curr_data_elmt_in_rcd c_ext_data_elmt_in_rcd%ROWTYPE;
808 r_curr_ext_data_elmt c_ext_data_elmt%ROWTYPE;
809 r_curr_ext_data_elmt_decd c_ext_data_elmt_decd%ROWTYPE;
810 r_curr_DElmt_where_clause c_DElmt_where_clause%ROWTYPE;
811 r_curr_DinR_where_clause c_DinR_where_clause%ROWTYPE;
812 r_curr_DinR_incl_chg c_DinR_incl_chg%ROWTYPE;
813
814 r_DinRId IdTyp;
815 r_RinFId IdTyp;
816
817 -- Local Variables
818 l_new_ext_file_id NUMBER(15);
819 l_new_ext_rcd_id NUMBER(15);
820 l_new_ext_rcd_in_file_id NUMBER(15);
821 l_new_cond_data_elmt_in_rcd_id NUMBER(15);
822 l_new_RinF_where_clause_id NUMBER(15);
823 l_new_RinF_incl_chg_id NUMBER(15);
824 l_new_ext_data_elmt_id NUMBER(15);
825 l_new_ext_data_elmt_in_rcd_id NUMBER(15);
826 l_new_ttl_cond_data_elmt_id NUMBER(15);
827 l_new_ttl_sum_ext_data_elmt_id NUMBER(15);
828 l_new_ext_data_elmt_decd_id NUMBER(15);
829 l_new_DElmt_where_clause_id NUMBER(15);
830 l_new_cond_ext_data_elmt_id NUMBER(15);
831 l_new_DinR_where_clause_id NUMBER(15);
832 l_new_DinR_incl_chg_id NUMBER(15);
833 l_new_data_elmt_rl NUMBER(15);
834
835
836 l_rcd_present BOOLEAN;
837 l_data_elmt_present BOOLEAN;
838
839 l_new_object_version_number NUMBER;
840 l_proc VARCHAR2(72) := g_package||'copy_file_layout';
841
842 BEGIN
843
844 hr_utility.set_location('Entering:'|| l_proc, 10);
845
846 FOR r_curr_ext_file IN c_ext_file
847 LOOP -- 6 Get File Layout data for current EXT_DFN_ID
848
849 -- Insert into BEN_EXT_FILE using Row Handler
850 ben_xfi_ins.ins
851 (
852 p_ext_file_id => l_new_ext_file_id -- OUT
853 ,p_name => fix_name_length
854 (p_new_extract_name||' '||r_curr_ext_file.name
855 ,c_ExtName_Maxlen)
859 ,p_xfi_attribute_category => r_curr_ext_file.xfi_attribute_category
856 ,p_xml_tag_name => r_curr_ext_file.xml_tag_name
857 ,p_business_group_id => p_business_group_id
858 ,p_legislation_code => NULL
860 ,p_xfi_attribute1 => r_curr_ext_file.xfi_attribute1
861 ,p_xfi_attribute2 => r_curr_ext_file.xfi_attribute2
862 ,p_xfi_attribute3 => r_curr_ext_file.xfi_attribute3
863 ,p_xfi_attribute4 => r_curr_ext_file.xfi_attribute4
864 ,p_xfi_attribute5 => r_curr_ext_file.xfi_attribute5
865 ,p_xfi_attribute6 => r_curr_ext_file.xfi_attribute6
866 ,p_xfi_attribute7 => r_curr_ext_file.xfi_attribute7
867 ,p_xfi_attribute8 => r_curr_ext_file.xfi_attribute8
868 ,p_xfi_attribute9 => r_curr_ext_file.xfi_attribute9
869 ,p_xfi_attribute10 => r_curr_ext_file.xfi_attribute10
870 ,p_xfi_attribute11 => r_curr_ext_file.xfi_attribute11
871 ,p_xfi_attribute12 => r_curr_ext_file.xfi_attribute12
872 ,p_xfi_attribute13 => r_curr_ext_file.xfi_attribute13
873 ,p_xfi_attribute14 => r_curr_ext_file.xfi_attribute14
874 ,p_xfi_attribute15 => r_curr_ext_file.xfi_attribute15
875 ,p_xfi_attribute16 => r_curr_ext_file.xfi_attribute16
876 ,p_xfi_attribute17 => r_curr_ext_file.xfi_attribute17
877 ,p_xfi_attribute18 => r_curr_ext_file.xfi_attribute18
878 ,p_xfi_attribute19 => r_curr_ext_file.xfi_attribute19
879 ,p_xfi_attribute20 => r_curr_ext_file.xfi_attribute20
880 ,p_xfi_attribute21 => r_curr_ext_file.xfi_attribute21
881 ,p_xfi_attribute22 => r_curr_ext_file.xfi_attribute22
882 ,p_xfi_attribute23 => r_curr_ext_file.xfi_attribute23
883 ,p_xfi_attribute24 => r_curr_ext_file.xfi_attribute24
884 ,p_xfi_attribute25 => r_curr_ext_file.xfi_attribute25
885 ,p_xfi_attribute26 => r_curr_ext_file.xfi_attribute26
886 ,p_xfi_attribute27 => r_curr_ext_file.xfi_attribute27
887 ,p_xfi_attribute28 => r_curr_ext_file.xfi_attribute28
888 ,p_xfi_attribute29 => r_curr_ext_file.xfi_attribute29
889 ,p_xfi_attribute30 => r_curr_ext_file.xfi_attribute30
890 ,p_object_version_number => l_new_object_version_number -- OUT
891 );
892
893 -- Reset collection for Data Element in Record
894 t_RinFId := NULL;
895
896 FOR r_curr_ext_rcd_in_file IN c_ext_rcd_in_file(r_curr_ext_file.ext_file_id)
897 LOOP /* 7 Get Record in File data for current EXT_FILE_ID
898 order by Record Type(Detail, then Header, then Trailer) */
899 FOR r_curr_ext_rcd IN c_ext_rcd(r_curr_ext_rcd_in_file.ext_rcd_id)
900 LOOP -- 8 Get Record data for current EXT_RCD_ID
901
902 BEGIN -- Insert into BEN_EXT_RCD using Row Handler
903 l_rcd_present := FALSE;
904 ben_xrc_ins.ins
905 (
906 p_ext_rcd_id => l_new_ext_rcd_id -- OUT
907 ,p_name => fix_name_length
908 (p_new_extract_name||' '||r_curr_ext_rcd.name
909 ,c_ExtName_Maxlen)
910 ,p_xml_tag_name => r_curr_ext_rcd.xml_tag_name
911 ,p_rcd_type_cd => r_curr_ext_rcd.rcd_type_cd
912 ,p_low_lvl_cd => r_curr_ext_rcd.low_lvl_cd
913 ,p_business_group_id => p_business_group_id
914 ,p_legislation_code => NULL
915 ,p_xrc_attribute_category => r_curr_ext_rcd.xrc_attribute_category
916 ,p_xrc_attribute1 => r_curr_ext_rcd.xrc_attribute1
917 ,p_xrc_attribute2 => r_curr_ext_rcd.xrc_attribute2
918 ,p_xrc_attribute3 => r_curr_ext_rcd.xrc_attribute3
919 ,p_xrc_attribute4 => r_curr_ext_rcd.xrc_attribute4
920 ,p_xrc_attribute5 => r_curr_ext_rcd.xrc_attribute5
921 ,p_xrc_attribute6 => r_curr_ext_rcd.xrc_attribute6
922 ,p_xrc_attribute7 => r_curr_ext_rcd.xrc_attribute7
923 ,p_xrc_attribute8 => r_curr_ext_rcd.xrc_attribute8
924 ,p_xrc_attribute9 => r_curr_ext_rcd.xrc_attribute9
925 ,p_xrc_attribute10 => r_curr_ext_rcd.xrc_attribute10
926 ,p_xrc_attribute11 => r_curr_ext_rcd.xrc_attribute11
927 ,p_xrc_attribute12 => r_curr_ext_rcd.xrc_attribute12
928 ,p_xrc_attribute13 => r_curr_ext_rcd.xrc_attribute13
929 ,p_xrc_attribute14 => r_curr_ext_rcd.xrc_attribute14
930 ,p_xrc_attribute15 => r_curr_ext_rcd.xrc_attribute15
931 ,p_xrc_attribute16 => r_curr_ext_rcd.xrc_attribute16
932 ,p_xrc_attribute17 => r_curr_ext_rcd.xrc_attribute17
933 ,p_xrc_attribute18 => r_curr_ext_rcd.xrc_attribute18
934 ,p_xrc_attribute19 => r_curr_ext_rcd.xrc_attribute19
935 ,p_xrc_attribute20 => r_curr_ext_rcd.xrc_attribute20
936 ,p_xrc_attribute21 => r_curr_ext_rcd.xrc_attribute21
937 ,p_xrc_attribute22 => r_curr_ext_rcd.xrc_attribute22
938 ,p_xrc_attribute23 => r_curr_ext_rcd.xrc_attribute23
939 ,p_xrc_attribute24 => r_curr_ext_rcd.xrc_attribute24
940 ,p_xrc_attribute25 => r_curr_ext_rcd.xrc_attribute25
941 ,p_xrc_attribute26 => r_curr_ext_rcd.xrc_attribute26
942 ,p_xrc_attribute27 => r_curr_ext_rcd.xrc_attribute27
946 ,p_object_version_number => l_new_object_version_number -- OUT
943 ,p_xrc_attribute28 => r_curr_ext_rcd.xrc_attribute28
944 ,p_xrc_attribute29 => r_curr_ext_rcd.xrc_attribute29
945 ,p_xrc_attribute30 => r_curr_ext_rcd.xrc_attribute30
947 ,p_effective_date => trunc(p_effective_date)
948 );
949 EXCEPTION
950 WHEN OTHERS THEN
951
952 IF get_msg_name() <> 'BEN_91009_NAME_NOT_UNIQUE' THEN
953 RAISE;
954 ELSE
955 l_rcd_present := TRUE;
956 l_new_ext_rcd_id := get_new_rcd_id(
957 r_curr_ext_rcd.ext_rcd_id
958 ,p_new_extract_name
959 ,p_business_group_id
960 );
961 END IF;
962
963 END; -- Insert into BEN_EXT_RCD using Row Handler
964
965 -- Insert into BEN_EXT_RCD_IN_FILE using Row Handler
966 ben_xrf_ins.ins
967 (
968 p_ext_rcd_in_file_id => l_new_ext_rcd_in_file_id -- OUT
969 ,p_seq_num => r_curr_ext_rcd_in_file.seq_num
970 ,p_sprs_cd => r_curr_ext_rcd_in_file.sprs_cd
971 ,p_sort1_data_elmt_in_rcd_id => r_curr_ext_rcd_in_file.sort1_data_elmt_in_rcd_id
972 ,p_sort2_data_elmt_in_rcd_id => r_curr_ext_rcd_in_file.sort2_data_elmt_in_rcd_id
973 ,p_sort3_data_elmt_in_rcd_id => r_curr_ext_rcd_in_file.sort3_data_elmt_in_rcd_id
974 ,p_sort4_data_elmt_in_rcd_id => r_curr_ext_rcd_in_file.sort4_data_elmt_in_rcd_id
975 ,p_ext_rcd_id => l_new_ext_rcd_id
976 ,p_ext_file_id => l_new_ext_file_id
977 ,p_business_group_id => p_business_group_id
978 ,p_legislation_code => NULL
979 ,p_object_version_number => l_new_object_version_number -- OUT
980 ,p_any_or_all_cd => r_curr_ext_rcd_in_file.any_or_all_cd
981 ,p_hide_flag => r_curr_ext_rcd_in_file.hide_flag
982 ,p_rqd_flag => r_curr_ext_rcd_in_file.rqd_flag
983 ,p_CHG_RCD_UPD_FLAG => r_curr_ext_rcd_in_file.CHG_RCD_UPD_FLAG
984 ,p_effective_date => trunc(p_effective_date)
985 );
986
987 IF NOT l_rcd_present THEN
988
989 -- Reset collection for Data Element in Record
990 t_DinRId := NULL;
991
992 FOR r_curr_data_elmt_in_rcd IN c_ext_data_elmt_in_rcd(r_curr_ext_rcd.ext_rcd_id)
993 LOOP /* 9 Get Data Element in Record data for current EXT_RCD_ID
994 order by Data element Type(Total Type last) */
995 FOR r_curr_data_elmt IN c_ext_data_elmt(r_curr_data_elmt_in_rcd.ext_data_elmt_id)
996 LOOP -- 10 Get Data Element data for current EXT_DATA_ELMT_ID
997
998 /* IF Data Element Type = RULE then
999 Copy Formula and obtain new DATA_ELMT_RL */
1000 l_new_data_elmt_rl := NULL;
1001
1002 IF r_curr_data_elmt.data_elmt_typ_cd = 'R' THEN
1003
1004 l_new_data_elmt_rl :=
1005 copy_formula(
1006 p_curr_formula_id => r_curr_data_elmt.data_elmt_rl
1007 ,p_new_extract_name => p_new_extract_name
1008 ,p_business_group_id => p_business_group_id
1009 ,p_legislation_code => p_legislation_code
1010 );
1011
1012 END IF; -- r_curr_data_elmt.data_elmt_typ_cd = 'R'
1013
1014 /* IF Data Element Type = TOTAL then
1015 Find new TTL_COND_EXT_DATA_ELMT_ID and maybe TTL_SUM_EXT_DATA_ELMT_ID */
1016
1017 l_new_ttl_cond_data_elmt_id := NULL;
1018 l_new_ttl_sum_ext_data_elmt_id := NULL;
1019
1020 IF r_curr_data_elmt.data_elmt_typ_cd in( 'T','C') THEN
1021
1022 -- Find new EXT_RCD_ID
1023 l_new_ttl_cond_data_elmt_id :=
1024 get_new_rcd_id(
1025 r_curr_data_elmt.ttl_cond_ext_data_elmt_id
1026 ,p_new_extract_name
1027 ,p_business_group_id
1028 );
1029
1030 -- Find new TTL_SUM_EXT_DATA_ELMT_ID only if current is not null
1031 IF r_curr_data_elmt.ttl_sum_ext_data_elmt_id IS NOT NULL THEN
1032 l_new_ttl_sum_ext_data_elmt_id :=
1033 get_new_data_elmt_id(
1034 r_curr_data_elmt.ttl_sum_ext_data_elmt_id
1035 ,p_new_extract_name
1036 ,p_business_group_id
1037 );
1038 END IF; -- r_curr_data_elmt.ttl_sum_ext_data_elmt_id IS NOT NULL
1039
1040 END IF; -- r_curr_data_elmt.data_elmt_typ_cd = 'T'
1041
1042 BEGIN -- Insert new record in BEN_EXT_DATA_ELMT with New Extract Name Prefixed
1043 l_data_elmt_present := FALSE;
1044 ben_xel_ins.ins
1045 (
1046 p_ext_data_elmt_id => l_new_ext_data_elmt_id -- OUT
1047 ,p_name => fix_name_length
1048 (p_new_extract_name||' '||r_curr_data_elmt.name
1049 ,c_ExtName_Maxlen)
1050 ,p_xml_tag_name => r_curr_data_elmt.xml_tag_name
1051 ,p_data_elmt_typ_cd => r_curr_data_elmt.data_elmt_typ_cd
1052 ,p_data_elmt_rl => l_new_data_elmt_rl
1056 ,p_max_length_num => r_curr_data_elmt.max_length_num
1053 ,p_frmt_mask_cd => r_curr_data_elmt.frmt_mask_cd
1054 ,p_string_val => r_curr_data_elmt.string_val
1055 ,p_dflt_val => r_curr_data_elmt.dflt_val
1057 ,p_just_cd => r_curr_data_elmt.just_cd
1058 ,p_ttl_fnctn_cd => r_curr_data_elmt.ttl_fnctn_cd
1059 ,p_ttl_cond_oper_cd => r_curr_data_elmt.ttl_cond_oper_cd
1060 ,p_ttl_cond_val => r_curr_data_elmt.ttl_cond_val
1061 ,p_ttl_sum_ext_data_elmt_id => l_new_ttl_sum_ext_data_elmt_id
1062 ,p_ttl_cond_ext_data_elmt_id => l_new_ttl_cond_data_elmt_id
1063 ,p_ext_fld_id => r_curr_data_elmt.ext_fld_id
1064 ,p_business_group_id => p_business_group_id
1065 ,p_legislation_code => NULL
1066 ,p_xel_attribute_category => r_curr_data_elmt.xel_attribute_category
1067 ,p_xel_attribute1 => r_curr_data_elmt.xel_attribute1
1068 ,p_xel_attribute2 => r_curr_data_elmt.xel_attribute2
1069 ,p_xel_attribute3 => r_curr_data_elmt.xel_attribute3
1070 ,p_xel_attribute4 => r_curr_data_elmt.xel_attribute4
1071 ,p_xel_attribute5 => r_curr_data_elmt.xel_attribute5
1072 ,p_xel_attribute6 => r_curr_data_elmt.xel_attribute6
1073 ,p_xel_attribute7 => r_curr_data_elmt.xel_attribute7
1074 ,p_xel_attribute8 => r_curr_data_elmt.xel_attribute8
1075 ,p_xel_attribute9 => r_curr_data_elmt.xel_attribute9
1076 ,p_xel_attribute10 => r_curr_data_elmt.xel_attribute10
1077 ,p_xel_attribute11 => r_curr_data_elmt.xel_attribute11
1078 ,p_xel_attribute12 => r_curr_data_elmt.xel_attribute12
1079 ,p_xel_attribute13 => r_curr_data_elmt.xel_attribute13
1080 ,p_xel_attribute14 => r_curr_data_elmt.xel_attribute14
1081 ,p_xel_attribute15 => r_curr_data_elmt.xel_attribute15
1082 ,p_xel_attribute16 => r_curr_data_elmt.xel_attribute16
1083 ,p_xel_attribute17 => r_curr_data_elmt.xel_attribute17
1084 ,p_xel_attribute18 => r_curr_data_elmt.xel_attribute18
1085 ,p_xel_attribute19 => r_curr_data_elmt.xel_attribute19
1086 ,p_xel_attribute20 => r_curr_data_elmt.xel_attribute20
1087 ,p_xel_attribute21 => r_curr_data_elmt.xel_attribute21
1088 ,p_xel_attribute22 => r_curr_data_elmt.xel_attribute22
1089 ,p_xel_attribute23 => r_curr_data_elmt.xel_attribute23
1090 ,p_xel_attribute24 => r_curr_data_elmt.xel_attribute24
1091 ,p_xel_attribute25 => r_curr_data_elmt.xel_attribute25
1092 ,p_xel_attribute26 => r_curr_data_elmt.xel_attribute26
1093 ,p_xel_attribute27 => r_curr_data_elmt.xel_attribute27
1094 ,p_xel_attribute28 => r_curr_data_elmt.xel_attribute28
1095 ,p_xel_attribute29 => r_curr_data_elmt.xel_attribute29
1096 ,p_xel_attribute30 => r_curr_data_elmt.xel_attribute30
1097 ,p_object_version_number => l_new_object_version_number -- OUT
1098 ,p_effective_date => trunc(p_effective_date)
1099 ,p_defined_balance_id => r_curr_data_elmt.defined_balance_id
1100 );
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 IF get_msg_name() <> 'BEN_91009_NAME_NOT_UNIQUE' THEN
1104 RAISE;
1105 ELSE
1106 l_data_elmt_present := TRUE;
1107 l_new_ext_data_elmt_id := get_new_data_elmt_id(
1108 r_curr_data_elmt.ext_data_elmt_id
1109 ,p_new_extract_name
1110 ,p_business_group_id
1111 );
1112 END IF;
1113 END; -- Insert into BEN_EXT_DATA_ELMT using Row Handler
1114
1115 -- Insert new record in BEN_EXT_DATA_ELMT_IN_RCD using Row Handler
1116 ben_xer_ins.ins
1117 (
1118 p_ext_data_elmt_in_rcd_id => l_new_ext_data_elmt_in_rcd_id -- OUT
1119 ,p_seq_num => r_curr_data_elmt_in_rcd.seq_num
1120 ,p_strt_pos => r_curr_data_elmt_in_rcd.strt_pos
1121 ,p_dlmtr_val => r_curr_data_elmt_in_rcd.dlmtr_val
1122 ,p_rqd_flag => r_curr_data_elmt_in_rcd.rqd_flag
1123 ,p_sprs_cd => r_curr_data_elmt_in_rcd.sprs_cd
1124 ,p_any_or_all_cd => r_curr_data_elmt_in_rcd.any_or_all_cd
1125 ,p_ext_data_elmt_id => l_new_ext_data_elmt_id
1126 ,p_ext_rcd_id => l_new_ext_rcd_id
1127 ,p_business_group_id => p_business_group_id
1128 ,p_legislation_code => NULL
1129 ,p_object_version_number => l_new_object_version_number -- OUT
1130 ,p_hide_flag => r_curr_data_elmt_in_rcd.hide_flag
1131 ,p_effective_date => trunc(p_effective_date)
1132 );
1133
1134 IF NOT l_data_elmt_present THEN
1135
1136 -- IF Data Element Type = DECODE then copy data in BEN_EXT_DATA_ELMT_DECD
1137 IF r_curr_data_elmt.data_elmt_typ_cd = 'D' THEN
1138
1139 FOR r_curr_ext_data_elmt_decd IN c_ext_data_elmt_decd(r_curr_data_elmt.ext_data_elmt_id)
1140 LOOP -- 11 Get Data Element Decode data for current EXT_DATA_ELMT_ID
1141
1142 -- Insert new record in BEN_EXT_DATA_ELMT_DECD using Row Handler
1143 ben_xdd_ins.ins
1144 (
1145 p_ext_data_elmt_decd_id => l_new_ext_data_elmt_decd_id -- OUT
1146 ,p_val => r_curr_ext_data_elmt_decd.val
1147 ,p_dcd_val => r_curr_ext_data_elmt_decd.dcd_val
1148 ,p_ext_data_elmt_id => l_new_ext_data_elmt_id
1149 ,p_business_group_id => p_business_group_id
1150 ,p_legislation_code => NULL
1151 ,p_object_version_number => l_new_object_version_number -- OUT
1152 ,p_chg_evt_source => r_curr_ext_data_elmt_decd.chg_evt_source
1153 );
1154
1155 END LOOP; -- 11
1156 END IF; -- r_curr_data_elmt.data_elmt_typ_cd = 'D'
1157
1158 -- IF Data Element Type = TOTAL then copy WHERE CLAUSE data
1159 IF r_curr_data_elmt.data_elmt_typ_cd in( 'T','C') THEN
1160
1161 FOR r_curr_DElmt_where_clause IN c_DElmt_where_clause(r_curr_data_elmt.ext_data_elmt_id)
1162 LOOP -- 12 Get Where Clause data for current EXT_DATA_ELMT_ID
1163
1164 /* For the data element in CONDITION, find the EX_DATA_ELMT_ID of
1165 the NEW data element created */
1166 l_new_cond_ext_data_elmt_id :=
1167 get_new_data_elmt_id(
1168 r_curr_DElmt_where_clause.cond_ext_data_elmt_id
1169 ,p_new_extract_name
1170 ,p_business_group_id
1171 );
1172
1173 -- Insert new record in BEN_EXT_WHERE_CLAUSE using Row Handler
1174 ben_xwc_ins.ins
1175 (
1176 p_ext_where_clause_id => l_new_DElmt_where_clause_id -- OUT
1177 ,p_seq_num => r_curr_DElmt_where_clause.seq_num
1178 ,p_oper_cd => r_curr_DElmt_where_clause.oper_cd
1179 ,p_val => r_curr_DElmt_where_clause.val
1180 ,p_and_or_cd => r_curr_DElmt_where_clause.and_or_cd
1181 ,p_ext_data_elmt_id => l_new_ext_data_elmt_id
1182 ,p_cond_ext_data_elmt_id => l_new_cond_ext_data_elmt_id
1183 ,p_ext_rcd_in_file_id => NULL -- p_ext_rcd_in_file_id
1184 ,p_ext_data_elmt_in_rcd_id => NULL -- p_ext_data_elmt_in_rcd_id
1185 ,p_business_group_id => p_business_group_id
1186 ,p_legislation_code => NULL
1187 ,p_object_version_number => l_new_object_version_number -- OUT
1188 ,p_cond_ext_data_elmt_in_rcd_id => NULL -- p_cond_ext_data_elmt_in_rcd_id
1189 ,p_effective_date => trunc(p_effective_date)
1190 );
1191
1192 END LOOP; -- 12
1193
1194 END IF; -- r_curr_data_elmt.data_elmt_typ_cd = 'T'
1195
1196 END IF; -- NOT l_data_elmt_present
1197
1198 END LOOP; -- 10
1199
1200 /* Add current EXT_DATA_ELMT_IN_RCD_ID to collection for
1201 processing of WHERE CLAUSE for Data Elemnt in Record */
1202 add_DinR_id(r_curr_data_elmt_in_rcd.ext_data_elmt_in_rcd_id
1203 ,l_new_ext_data_elmt_in_rcd_id);
1204
1205
1206 FOR r_curr_DinR_incl_chg IN c_DinR_incl_chg(r_curr_data_elmt_in_rcd.ext_data_elmt_in_rcd_id)
1207 LOOP -- 16 Get Inclusion on Change Event data for current EXT_DATA_ELMT_IN_RCD_ID
1208
1209 -- Insert into BEN_EXT_INCL_CHG using Row Handler
1210 ben_xic_ins.ins
1211 (
1212 p_ext_incl_chg_id => l_new_DinR_incl_chg_id -- OUT
1213 ,p_chg_evt_cd => r_curr_DinR_incl_chg.chg_evt_cd
1214 ,p_ext_rcd_in_file_id => NULL
1215 ,p_ext_data_elmt_in_rcd_id => l_new_ext_data_elmt_in_rcd_id
1216 ,p_business_group_id => p_business_group_id
1217 ,p_legislation_code => NULL
1218 ,p_object_version_number => l_new_object_version_number -- OUT
1219 ,p_effective_date => trunc(p_effective_date)
1220 ,p_chg_evt_source => r_curr_DinR_incl_chg.chg_evt_source
1221 );
1222
1223 END LOOP; -- 16
1224
1225 END LOOP; -- 9
1226
1227 IF t_DinRId IS NOT NULL THEN
1228
1229 FOR l_DinRId_Indx IN t_DinRId.FIRST..t_DinRId.LAST
1230 LOOP -- 17 Process all Data_Elmt_In_Rcd_Id for Where Clause data
1231
1232 r_DinRId := t_DinRId(l_DinRId_Indx);
1233
1234 FOR r_curr_DinR_where_clause IN c_DinR_where_clause(r_DinRId.curr_Id)
1235 LOOP -- 13 Get Where Clause data for current EXT_DATA_ELMT_IN_RCD_ID
1236
1237 /* For the data element in CONDITION, find the EXT_DATA_ELMT_ID of
1238 the NEW data element created */
1239 l_new_cond_data_elmt_in_rcd_id :=
1240 get_new_WCDInR_DInR_id(
1241 r_curr_DinR_where_clause.cond_ext_data_elmt_in_rcd_id
1242 ,l_new_ext_rcd_id
1243 ,p_new_extract_name
1244 ,p_business_group_id
1245 );
1246
1247 l_new_cond_ext_data_elmt_id := null ;
1248 if r_curr_DinR_where_clause.cond_ext_data_elmt_id is not null then
1249 l_new_cond_ext_data_elmt_id :=
1250 get_new_data_elmt_id(
1251 r_curr_DinR_where_clause.cond_ext_data_elmt_id
1252 ,p_new_extract_name
1253 ,p_business_group_id
1254 );
1255 end if ;
1256
1257
1258 -- Insert into BEN_EXT_WHERE_CLAUSE using Row Handler
1259 ben_xwc_ins.ins
1260 (
1261 p_ext_where_clause_id => l_new_DinR_where_clause_id -- OUT
1262 ,p_seq_num => r_curr_DinR_where_clause.seq_num
1263 ,p_oper_cd => r_curr_DinR_where_clause.oper_cd
1264 ,p_val => r_curr_DinR_where_clause.val
1265 ,p_and_or_cd => r_curr_DinR_where_clause.and_or_cd
1266 ,p_ext_data_elmt_id => NULL -- p_ext_data_elmt_id
1267 ,p_cond_ext_data_elmt_id => l_new_cond_ext_data_elmt_id
1268 ,p_ext_rcd_in_file_id => NULL -- p_ext_rcd_in_file_id
1269 ,p_ext_data_elmt_in_rcd_id => r_DinRId.new_Id
1270 ,p_business_group_id => p_business_group_id
1271 ,p_legislation_code => NULL
1272 ,p_object_version_number => l_new_object_version_number -- OUT
1273 ,p_cond_ext_data_elmt_in_rcd_id => l_new_cond_data_elmt_in_rcd_id
1274 ,p_effective_date => trunc(p_effective_date)
1275 );
1276
1277 END LOOP; -- 13
1278
1279 END LOOP; -- 17
1280
1281 -- Reset collection for Data Element in Record
1282 t_DinRId := NULL;
1283
1284 END IF; -- t_DinRId IS NOT NULL THEN
1285
1286 END IF; -- NOT l_rcd_present
1287
1288 END LOOP; -- 8
1289
1290 /* Add current EXT_RCD_IN_FILE_ID to collection for
1291 processing of WHERE CLAUSE for Record in File */
1292 add_RinF_id(r_curr_ext_rcd_in_file.ext_rcd_in_file_id
1293 ,l_new_ext_rcd_in_file_id);
1294
1295 FOR r_curr_RinF_incl_chg IN c_RinF_incl_chg(r_curr_ext_rcd_in_file.ext_rcd_in_file_id)
1296 LOOP -- 15 Get Inclusion on Change Event data for current EXT_RCD_IN_FILE_ID
1297
1298 -- Insert into BEN_EXT_INCL_CHG using Row Handler
1299 ben_xic_ins.ins
1300 (
1301 p_ext_incl_chg_id => l_new_RinF_incl_chg_id -- OUT
1302 ,p_chg_evt_cd => r_curr_RinF_incl_chg.chg_evt_cd
1303 ,p_ext_rcd_in_file_id => l_new_ext_rcd_in_file_id
1304 ,p_ext_data_elmt_in_rcd_id => NULL
1305 ,p_business_group_id => p_business_group_id
1306 ,p_legislation_code => NULL
1307 ,p_object_version_number => l_new_object_version_number -- OUT
1308 ,p_effective_date => trunc(p_effective_date)
1309 ,p_chg_evt_source => r_curr_RinF_incl_chg.chg_evt_source
1310 );
1311
1312 END LOOP; -- 15
1313
1314 END LOOP; -- 7
1315
1316 IF t_RinFId IS NOT NULL THEN
1317
1318 FOR l_RinFId_Indx IN t_RinFId.FIRST..t_RinFId.LAST
1319 LOOP -- 18 Process all Rcd_In_File_Id for Where Clause data
1320
1321 r_RinFId := t_RinFId(l_RinFId_Indx);
1322
1323 FOR r_curr_RinF_where_clause IN c_RinF_where_clause(r_RinFId.curr_id)
1324 LOOP -- 14 Get Where Clause data for current EXT_RCD_IN_FILE_ID
1325
1326 /* For the data element in CONDITION, find the EX_DATA_ELMT_ID of
1327 the NEW data element created */
1328 l_new_cond_data_elmt_in_rcd_id :=
1329 get_new_WCRInF_DInR_id(
1330 r_curr_RinF_where_clause.cond_ext_data_elmt_in_rcd_id
1331 ,r_RinFId.new_id
1332 ,p_new_extract_name
1333 );
1334
1335
1336 l_new_cond_ext_data_elmt_id := null ;
1337 if r_curr_RinF_where_clause.cond_ext_data_elmt_id is not null then
1338 l_new_cond_ext_data_elmt_id :=
1339 get_new_data_elmt_id(
1340 r_curr_RinF_where_clause.cond_ext_data_elmt_id
1341 ,p_new_extract_name
1342 ,p_business_group_id
1343 );
1344 end if ;
1345
1346
1347 -- Insert into BEN_EXT_WHERE_CLAUSE using Row Handler
1348 ben_xwc_ins.ins
1349 (
1350 p_ext_where_clause_id => l_new_RinF_where_clause_id -- OUT
1351 ,p_seq_num => r_curr_RinF_where_clause.seq_num
1352 ,p_oper_cd => r_curr_RinF_where_clause.oper_cd
1353 ,p_val => r_curr_RinF_where_clause.val
1354 ,p_and_or_cd => r_curr_RinF_where_clause.and_or_cd
1355 ,p_ext_data_elmt_id => NULL -- p_ext_data_elmt_id
1356 ,p_cond_ext_data_elmt_id => l_new_cond_ext_data_elmt_id
1357 ,p_ext_rcd_in_file_id => r_RinFId.new_id
1358 ,p_ext_data_elmt_in_rcd_id => NULL -- p_ext_data_elmt_in_rcd_id
1359 ,p_business_group_id => p_business_group_id
1360 ,p_legislation_code => NULL
1361 ,p_object_version_number => l_new_object_version_number -- OUT
1362 ,p_cond_ext_data_elmt_in_rcd_id => l_new_cond_data_elmt_in_rcd_id
1363 ,p_effective_date => trunc(p_effective_date)
1364 );
1365
1366 END LOOP; -- 14
1367
1368 END LOOP; -- 18
1369
1370 -- Reset collection for Data Element in Record
1371 t_RinFId := NULL;
1372
1373 END IF; -- t_RinFId IS NOT NULL
1374
1375
1376 -- Assign the New File Id to return variable
1377 p_new_ext_file_id := l_new_ext_file_id;
1378
1379 END LOOP; -- 6
1380
1381 hr_utility.set_location('Leaving:'|| l_proc, 20);
1382
1383 END copy_file_layout; -- copy_file_layout
1384
1385 -- ----------------------------------------------------------------------------
1386 -- |------------------------< COPY_EXTRACT >----------------------------------|
1387 -- ----------------------------------------------------------------------------
1388 PROCEDURE copy_extract(p_extract_dfn_id IN NUMBER
1389 ,p_new_extract_name IN VARCHAR2
1390 ,p_business_group_id IN NUMBER
1391 ,p_legislation_code IN VARCHAR2
1392 ,p_effective_date IN DATE
1393 ,p_formulas OUT NOCOPY FormulaID
1394 ) IS
1395
1396 CURSOR c_ext_dfn IS
1397 SELECT *
1398 FROM ben_ext_dfn
1399 WHERE ext_dfn_id = p_extract_dfn_id
1400 AND ((business_group_id IS NULL AND legislation_code IS NULL)
1401 OR (legislation_code IS NOT NULL
1402 AND legislation_code = p_legislation_code)
1403 OR (business_group_id IS NOT NULL
1404 AND business_group_id = p_business_group_id)
1405 );
1406
1407 -- Local Record Variables
1408 r_curr_ext_dfn c_ext_dfn%ROWTYPE;
1409
1410 -- Local Variables
1411 l_new_ext_crit_prfl_id NUMBER(15);
1412 l_new_ext_file_id NUMBER(15);
1413 l_new_ext_dfn_id NUMBER(15);
1414 l_new_object_version_number NUMBER(9);
1415 l_new_post_prcs_rl NUMBER(15);
1416 l_proc VARCHAR2(72) := g_package||'copy_extract';
1417
1418 BEGIN
1419
1420 hr_utility.set_location('Entering:'|| l_proc, 10);
1421
1422 IF p_new_extract_name IS NULL THEN
1423 fnd_message.set_name('PQP', 'PQP_230541_EXTRACT_PREFIX_NULL');
1424 fnd_message.raise_error;
1425 END IF;
1426
1427 -- Set the truncation flag to false
1428 g_truncated := FALSE;
1429
1430 FOR r_curr_ext_dfn in c_ext_dfn
1431 LOOP -- 1 Get Extract Definition data for p_ext_dfn_id
1432
1433 -- Creteria Definition
1434 copy_criteria_definition(p_ext_crit_prfl_id => r_curr_ext_dfn.ext_crit_prfl_id
1435 ,p_new_extract_name => p_new_extract_name
1436 ,p_business_group_id => p_business_group_id
1437 ,p_legislation_code => p_legislation_code
1438 ,p_effective_date => p_effective_date
1442 -- File Layouts
1439 ,p_new_ext_crit_prfl_id => l_new_ext_crit_prfl_id -- OUT
1440 );
1441
1443 copy_file_layout(p_ext_file_id => r_curr_ext_dfn.ext_file_id
1444 ,p_new_extract_name => p_new_extract_name
1445 ,p_business_group_id => p_business_group_id
1446 ,p_legislation_code => p_legislation_code
1447 ,p_effective_date => p_effective_date
1448 ,p_new_ext_file_id => l_new_ext_file_id -- OUT
1449 );
1450
1451 -- If Post Processing Rule exists then copy formula and obtain new formula id
1452 l_new_post_prcs_rl := NULL;
1453
1454 IF r_curr_ext_dfn.ext_post_prcs_rl IS NOT NULL THEN
1455
1456 l_new_post_prcs_rl :=
1457 copy_formula(
1458 p_curr_formula_id => r_curr_ext_dfn.ext_post_prcs_rl
1459 ,p_new_extract_name => p_new_extract_name
1460 ,p_business_group_id => p_business_group_id
1461 ,p_legislation_code => p_legislation_code
1462 );
1463
1464 END IF; -- r_curr_ext_dfn.ext_post_prcs_rl IS NOT NULL
1465
1466
1467 -- Insert into BEN_EXT_DFN using Row Handler
1468 ben_xdf_ins.ins
1469 (p_ext_dfn_id => l_new_ext_dfn_id -- OUT
1470 ,p_name => fix_name_length
1471 (p_new_extract_name||' '||r_curr_ext_dfn.name
1472 ,c_ExtName_Maxlen)
1473 ,p_xml_tag_name => r_curr_ext_dfn.xml_tag_name
1474 ,p_data_typ_cd => r_curr_ext_dfn.data_typ_cd
1475 ,p_ext_typ_cd => r_curr_ext_dfn.ext_typ_cd
1476 ,p_output_name => r_curr_ext_dfn.output_name
1477 ,p_output_type => r_curr_ext_dfn.output_type
1478 ,p_apnd_rqst_id_flag => r_curr_ext_dfn.apnd_rqst_id_flag
1479 ,p_prmy_sort_cd => r_curr_ext_dfn.prmy_sort_cd
1480 ,p_scnd_sort_cd => r_curr_ext_dfn.scnd_sort_cd
1481 ,p_strt_dt => r_curr_ext_dfn.strt_dt
1482 ,p_end_dt => r_curr_ext_dfn.end_dt
1483 ,p_ext_crit_prfl_id => l_new_ext_crit_prfl_id
1484 ,p_ext_file_id => l_new_ext_file_id
1485 ,p_business_group_id => p_business_group_id
1486 ,p_legislation_code => NULL
1487 ,p_xdf_attribute_category => r_curr_ext_dfn.xdf_attribute_category
1488 ,p_xdf_attribute1 => r_curr_ext_dfn.xdf_attribute1
1489 ,p_xdf_attribute2 => r_curr_ext_dfn.xdf_attribute2
1490 ,p_xdf_attribute3 => r_curr_ext_dfn.xdf_attribute3
1491 ,p_xdf_attribute4 => r_curr_ext_dfn.xdf_attribute4
1492 ,p_xdf_attribute5 => r_curr_ext_dfn.xdf_attribute5
1493 ,p_xdf_attribute6 => r_curr_ext_dfn.xdf_attribute6
1494 ,p_xdf_attribute7 => r_curr_ext_dfn.xdf_attribute7
1495 ,p_xdf_attribute8 => r_curr_ext_dfn.xdf_attribute8
1496 ,p_xdf_attribute9 => r_curr_ext_dfn.xdf_attribute9
1497 ,p_xdf_attribute10 => r_curr_ext_dfn.xdf_attribute10
1498 ,p_xdf_attribute11 => r_curr_ext_dfn.xdf_attribute11
1499 ,p_xdf_attribute12 => r_curr_ext_dfn.xdf_attribute12
1500 ,p_xdf_attribute13 => r_curr_ext_dfn.xdf_attribute13
1501 ,p_xdf_attribute14 => r_curr_ext_dfn.xdf_attribute14
1502 ,p_xdf_attribute15 => r_curr_ext_dfn.xdf_attribute15
1503 ,p_xdf_attribute16 => r_curr_ext_dfn.xdf_attribute16
1504 ,p_xdf_attribute17 => r_curr_ext_dfn.xdf_attribute17
1505 ,p_xdf_attribute18 => r_curr_ext_dfn.xdf_attribute18
1506 ,p_xdf_attribute19 => r_curr_ext_dfn.xdf_attribute19
1507 ,p_xdf_attribute20 => r_curr_ext_dfn.xdf_attribute20
1508 ,p_xdf_attribute21 => r_curr_ext_dfn.xdf_attribute21
1509 ,p_xdf_attribute22 => r_curr_ext_dfn.xdf_attribute22
1510 ,p_xdf_attribute23 => r_curr_ext_dfn.xdf_attribute23
1511 ,p_xdf_attribute24 => r_curr_ext_dfn.xdf_attribute24
1512 ,p_xdf_attribute25 => r_curr_ext_dfn.xdf_attribute25
1513 ,p_xdf_attribute26 => r_curr_ext_dfn.xdf_attribute26
1514 ,p_xdf_attribute27 => r_curr_ext_dfn.xdf_attribute27
1515 ,p_xdf_attribute28 => r_curr_ext_dfn.xdf_attribute28
1516 ,p_xdf_attribute29 => r_curr_ext_dfn.xdf_attribute29
1517 ,p_xdf_attribute30 => r_curr_ext_dfn.xdf_attribute30
1518 ,p_object_version_number => l_new_object_version_number -- OUT
1519 ,p_drctry_name => r_curr_ext_dfn.drctry_name
1520 ,p_kickoff_wrt_prc_flag => r_curr_ext_dfn.kickoff_wrt_prc_flag
1521 ,p_upd_cm_sent_dt_flag => r_curr_ext_dfn.upd_cm_sent_dt_flag
1522 ,p_spcl_hndl_flag => r_curr_ext_dfn.spcl_hndl_flag
1523 ,p_use_eff_dt_for_chgs_flag => r_curr_ext_dfn.use_eff_dt_for_chgs_flag
1524 ,p_ext_post_prcs_rl => l_new_post_prcs_rl
1525 ,p_effective_date => p_effective_date
1526 ,p_XDO_TEMPLATE_ID => r_curr_ext_dfn.XDO_TEMPLATE_ID
1527 ,p_ext_global_flag => nvl(r_curr_ext_dfn.ext_global_flag,'N')
1528 ,p_cm_display_flag => nvl(r_curr_ext_dfn.cm_display_flag,'N')
1529 );
1530
1531 -- Copy Extract Attributes
1532 pqp_copy_eat.copy_extract_attributes
1533 (p_curr_ext_dfn_id => p_extract_dfn_id
1534 ,p_new_ext_dfn_id => l_new_ext_dfn_id
1535 ,p_ext_prefix => p_new_extract_name
1536 ,p_business_group_id => p_business_group_id
1537 );
1538 END LOOP; -- 1
1539
1540 -- Assign collection of formula ids to return variable
1541 p_formulas := t_Formulas;
1542
1543 hr_utility.set_location('Leaving:'|| l_proc, 20);
1544
1545 END copy_extract;
1546
1547 END ben_copy_extract;