[Home] [Help]
PACKAGE BODY: APPS.HXC_HPH_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_hph_upload_pkg AS
2 /* $Header: hxchphupl.pkb 115.26 2003/09/22 10:05:51 mvilrokx noship $ */
3
4 PROCEDURE load_hph_row (
5 p_name VARCHAR2
6 , p_legislation_code VARCHAR2
7 , p_parent_name varchar2
8 , p_type varchar2
9 , p_edit_allowed varchar2
10 , p_displayed varchar2
11 , p_pref_def_name varchar2
12 , p_attribute_category varchar2
13 , p_attribute1 varchar2
14 , p_attribute2 varchar2
15 , p_attribute3 varchar2
16 , p_attribute4 varchar2
17 , p_attribute5 varchar2
18 , p_attribute6 varchar2
19 , p_attribute7 varchar2
20 , p_attribute8 varchar2
21 , p_attribute9 varchar2
22 , p_attribute10 varchar2
23 , p_attribute11 varchar2
24 , p_attribute12 varchar2
25 , p_attribute13 varchar2
26 , p_attribute14 varchar2
27 , p_attribute15 varchar2
28 , p_attribute16 varchar2
29 , p_attribute17 varchar2
30 , p_attribute18 varchar2
31 , p_attribute19 varchar2
32 , p_attribute20 varchar2
33 , p_attribute21 varchar2
34 , p_attribute22 varchar2
35 , p_attribute23 varchar2
36 , p_attribute24 varchar2
37 , p_attribute25 varchar2
38 , p_attribute26 varchar2
39 , p_attribute27 varchar2
40 , p_attribute28 varchar2
41 , p_attribute29 varchar2
42 , p_attribute30 varchar2
43 , p_owner VARCHAR2
44 , p_custom_mode VARCHAR2 ) IS
45
46 l_pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE;
47 l_parent_pref_hierarchy_id hxc_pref_hierarchies.parent_pref_hierarchy_id%TYPE;
48 l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
49
50 l_attribute1 hxc_pref_hierarchies.attribute1%TYPE;
51 l_attribute2 hxc_pref_hierarchies.attribute1%TYPE;
52 l_attribute3 hxc_pref_hierarchies.attribute1%TYPE;
53 l_attribute4 hxc_pref_hierarchies.attribute1%TYPE;
54 l_attribute5 hxc_pref_hierarchies.attribute1%TYPE;
55 l_attribute6 hxc_pref_hierarchies.attribute1%TYPE;
56
57 l_ovn hxc_pref_hierarchies.object_version_number%TYPE := NULL;
58 l_owner VARCHAR2(6);
59 l_dummy VARCHAR2(6);
60
61
62 CURSOR csr_get_owner ( p_pref_hierarchy_id NUMBER ) IS
63 SELECT DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
64 FROM hxc_pref_hierarchies;
65
66 FUNCTION get_pref_def_id ( p_pref_def_name VARCHAR2 ) RETURN NUMBER IS
67
68 l_pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE;
69
70 CURSOR csr_get_pref_def_id IS
71 SELECT pd.pref_definition_id
72 FROM hxc_pref_definitions pd
73 , fnd_descr_flex_contexts_vl df
74 WHERE df.application_id = 809
75 AND df.descriptive_flexfield_name = 'OTC PREFERENCES'
76 AND df.descriptive_flex_context_name = p_pref_def_name
77 AND df.descriptive_flex_context_code = pd.code;
78
79 BEGIN
80
81 OPEN csr_get_pref_def_id;
82 FETCH csr_get_pref_def_id INTO l_pref_definition_id;
83 CLOSE csr_get_pref_def_id;
84
85 RETURN l_pref_definition_id;
86
87 END get_pref_def_id;
88
89
90 FUNCTION get_attribute_id ( p_attribute_category VARCHAR2
91 , p_attribute VARCHAR2 ) RETURN VARCHAR2 IS
92
93 l_attribute_id hxc_entity_group_comps.attribute1%TYPE;
94
95 CURSOR csr_get_approval_style_id IS
96 SELECT TO_CHAR(approval_style_id)
97 FROM hxc_approval_styles
98 WHERE name = p_attribute;
99
100 CURSOR csr_get_alias_id IS
101 SELECT TO_CHAR(alias_definition_id)
102 FROM hxc_alias_definitions
103 WHERE alias_definition_name = p_attribute;
104
105 CURSOR csr_get_recurring_period_id IS
106 SELECT TO_CHAR(rp.recurring_period_id)
107 FROM hxc_recurring_periods rp
108 WHERE rp.name = p_attribute;
109
110 CURSOR csr_get_application_set_id IS
111 SELECT TO_CHAR(aps.application_set_id)
112 FROM hxc_application_sets_v aps
113 WHERE aps.application_set_name = p_attribute;
114
115 CURSOR csr_get_retrieval_rule_grp_id IS
116 SELECT TO_CHAR(rrg.retrieval_rule_group_id)
117 FROM hxc_retrieval_rule_groups_v rrg
118 WHERE rrg.retrieval_rule_group_name = p_attribute;
119
120 CURSOR csr_get_approval_period_id IS
121 SELECT TO_CHAR(aps.approval_period_set_id)
122 FROM hxc_approval_period_sets aps
123 WHERE aps.name = p_attribute;
124
125 CURSOR csr_get_layout_id IS
126 SELECT layout_id
127 FROM hxc_layouts
128 WHERE layout_name = p_attribute;
129
130 CURSOR csr_get_date_format IS
131 SELECT egc.attribute1
132 FROM hxc_entity_group_comps egc
133 WHERE egc.attribute2 = p_attribute
134 AND EXISTS ( SELECT 'x'
135 FROM hxc_entity_groups eg
136 WHERE eg.entity_group_id = egc.entity_group_id
137 AND eg.entity_type = 'HXC_SS_TC_DATE_FORMATS' );
138
139 CURSOR csr_get_rule_group_id IS
140 SELECT time_entry_rule_group_id
141 FROM hxc_time_entry_rule_groups_v
142 WHERE time_entry_rule_group_name = p_attribute1;
143
144 BEGIN
145
146 IF ( p_attribute_category = 'TS_PER_APPROVAL_STYLE' )
147 THEN
148
149 OPEN csr_get_approval_style_id;
150 FETCH csr_get_approval_style_id INTO l_attribute_id;
151 CLOSE csr_get_approval_style_id;
152
153 ELSIF ( p_attribute_category = 'TC_W_TCRD_PERIOD' )
154 THEN
155
156 OPEN csr_get_recurring_period_id;
157 FETCH csr_get_recurring_period_id INTO l_attribute_id;
158 CLOSE csr_get_recurring_period_id;
159
160 --
161 -- ARR: 115.7
162 -- Get value from cursor now, as there is a recurring period
163 -- loader.
164 --
165 -- l_attribute_id := p_attribute;
166 --
167
168 ELSIF ( p_attribute_category = 'TC_W_TCRD_ALIASES' )
169 THEN
170
171 OPEN csr_get_alias_id;
172 FETCH csr_get_alias_id INTO l_attribute_id;
173 CLOSE csr_get_alias_id;
174
175 --
176 -- GPM: 115.9
177 -- Commented out because now we are seeding aliases
178 --
179 -- l_attribute_id := p_attribute;
180
181 ELSIF ( p_attribute_category = 'TS_PER_APPLICATION_SET' )
182 THEN
183
184 OPEN csr_get_application_set_id;
185 FETCH csr_get_application_set_id INTO l_attribute_id;
186 CLOSE csr_get_application_set_id;
187
188 ELSIF ( p_attribute_category = 'TS_PER_RETRIEVAL_RULES' )
189 THEN
190
191 OPEN csr_get_retrieval_rule_grp_id;
192 FETCH csr_get_retrieval_rule_grp_id INTO l_attribute_id;
193 CLOSE csr_get_retrieval_rule_grp_id;
194
195 ELSIF ( p_attribute_category = 'TS_PER_APPROVAL_PERIODS' )
196 THEN
197
198 OPEN csr_get_approval_period_id;
199 FETCH csr_get_approval_period_id INTO l_attribute_id;
200 CLOSE csr_get_approval_period_id;
201
202 ELSIF ( p_attribute_category = 'TC_W_TCRD_LAYOUT' )
203 THEN
204
205 OPEN csr_get_layout_id;
206 FETCH csr_get_layout_id INTO l_attribute_id;
207 CLOSE csr_get_layout_id;
208
209 ELSIF ( p_attribute_category = 'TC_W_DATE_FORMATS' )
210 THEN
211
212 OPEN csr_get_date_format;
213 FETCH csr_get_date_format INTO l_attribute_id;
214 CLOSE csr_get_date_format;
215
216 ELSIF ( p_attribute_category = 'TS_PER_AUDIT_REQUIREMENTS')
217 THEN
218
219 OPEN csr_get_rule_group_id;
220 FETCH csr_get_rule_group_id into l_attribute_id;
221 CLOSE csr_get_rule_group_id;
222 ELSE
223
224 l_attribute_id := p_attribute;
225
226 END IF;
227
228 RETURN ltrim(rtrim(l_attribute_id));
229
230 END get_attribute_id;
231
232 BEGIN -- load_hph_row
233
234 l_pref_definition_id := get_pref_def_id ( p_pref_def_name );
235
236 l_attribute2 := p_attribute2;
237 l_attribute3 := p_attribute3;
238 l_attribute4 := p_attribute4;
239 l_attribute5 := p_attribute5;
240 l_attribute6 := p_attribute6;
241
242 IF ( p_attribute_category = 'TS_PER_APPROVAL_STYLE' )
243 THEN
244 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
245 l_attribute2 := get_attribute_id ( p_attribute_category, p_attribute2 );
246
247 ELSIF ( p_attribute_category = 'TC_W_TCRD_ALIASES' )
248 THEN
249 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
250
251 ELSIF ( p_attribute_category = 'TS_PER_APPLICATION_SET' )
252 THEN
253 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
254
255 ELSIF ( p_attribute_category = 'TS_PER_APPROVAL_PERIODS' )
256 THEN
257 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
258
259 ELSIF ( p_attribute_category = 'TS_PER_RETRIEVAL_RULES' )
260 THEN
261 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
262
263 ELSIF ( p_attribute_category = 'TC_W_TCRD_LAYOUT' )
264 THEN
265 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
266 l_attribute2 := get_attribute_id ( p_attribute_category, p_attribute2 );
267 l_attribute3 := get_attribute_id ( p_attribute_category, p_attribute3 );
268 l_attribute4 := get_attribute_id ( p_attribute_category, p_attribute4 );
269 l_attribute5 := get_attribute_id ( p_attribute_category, p_attribute5 );
270 l_attribute6 := get_attribute_id ( p_attribute_category, p_attribute6 );
271
272 ELSIF ( p_attribute_category = 'TC_W_TCRD_PERIOD' )
273 THEN
274 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
275
276 ELSIF ( p_attribute_category = 'TC_W_DATE_FORMATS' )
277 THEN
278 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1 );
279 l_attribute2 := get_attribute_id ( p_attribute_category, p_attribute2 );
280 l_attribute3 := get_attribute_id ( p_attribute_category, p_attribute3 );
281
282 ELSIF (p_attribute_category = 'TS_PER_AUDIT_REQUIREMENTS' )
283 THEN
284 l_attribute1 := get_attribute_id ( p_attribute_category, p_attribute1);
285
286 ELSE
287 l_attribute1 := p_attribute1;
288 END IF;
289
290 -- check to see if the preference we are about to load already exists
291
292 hxc_pref_hierarchies_api.get_node_data (
293 p_preference_full_name => p_parent_name
294 , p_name => p_name
295 , p_business_group_id => null
296 , p_legislation_code => p_legislation_code
297 , p_pref_hierarchy_id => l_pref_hierarchy_id
298 , p_parent_pref_hierarchy_id => l_parent_pref_hierarchy_id
299 , p_object_version_number => l_ovn
300 , p_mode => l_dummy );
301
302 IF ( l_pref_hierarchy_id IS NOT NULL )
303 THEN
304
305 OPEN csr_get_owner ( l_pref_hierarchy_id );
306 FETCH csr_get_owner INTO l_owner;
307 CLOSE csr_get_owner;
308
309 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
310 THEN
311
312 hxc_pref_hierarchies_api.update_pref_hierarchies (
313 p_effective_date => sysdate
314 , p_name => p_name
315 , p_business_group_id => null
316 , p_legislation_code => p_legislation_code
317 , p_pref_hierarchy_id => l_pref_hierarchy_id
318 , p_parent_pref_hierarchy_id => l_parent_pref_hierarchy_id
319 , p_type => p_type
320 , p_edit_allowed => p_edit_allowed
321 , p_displayed => p_displayed
322 , p_pref_definition_id => l_pref_definition_id
323 , p_attribute_category => p_attribute_category
324 , p_attribute1 => l_attribute1
325 , p_attribute2 => l_attribute2
326 , p_attribute3 => l_attribute3
327 , p_attribute4 => l_attribute4
328 , p_attribute5 => l_attribute5
329 , p_attribute6 => l_attribute6
330 , p_attribute7 => p_attribute7
331 , p_attribute8 => p_attribute8
332 , p_attribute9 => p_attribute9
333 , p_attribute10 => p_attribute10
334 , p_attribute11 => p_attribute11
335 , p_attribute12 => p_attribute12
336 , p_attribute13 => p_attribute13
337 , p_attribute14 => p_attribute14
338 , p_attribute15 => p_attribute15
339 , p_attribute16 => p_attribute16
340 , p_attribute17 => p_attribute17
341 , p_attribute18 => p_attribute18
342 , p_attribute19 => p_attribute19
343 , p_attribute20 => p_attribute20
344 , p_attribute21 => p_attribute21
345 , p_attribute22 => p_attribute22
346 , p_attribute23 => p_attribute23
347 , p_attribute24 => p_attribute24
348 , p_attribute25 => p_attribute25
349 , p_attribute26 => p_attribute26
350 , p_attribute27 => p_attribute27
351 , p_attribute28 => p_attribute28
352 , p_attribute29 => p_attribute29
353 , p_attribute30 => p_attribute30
354 , p_object_version_number => l_ovn );
355
356 END IF;
357
358 ELSE
359
360 hxc_pref_hierarchies_api.create_pref_hierarchies (
361 p_effective_date => sysdate
362 , p_name => p_name
363 , p_business_group_id => null
364 , p_legislation_code => p_legislation_code
365 , p_pref_hierarchy_id => l_pref_hierarchy_id
366 , p_parent_pref_hierarchy_id => l_parent_pref_hierarchy_id
367 , p_type => p_type
368 , p_edit_allowed => p_edit_ALLOWED
369 , p_displayed => p_displayed
370 , p_pref_definition_id => l_pref_definition_id
371 , p_attribute_category => p_attribute_category
372 , p_attribute1 => l_attribute1
373 , p_attribute2 => l_attribute2
374 , p_attribute3 => l_attribute3
375 , p_attribute4 => l_attribute4
376 , p_attribute5 => l_attribute5
377 , p_attribute6 => l_attribute6
378 , p_attribute7 => p_attribute7
379 , p_attribute8 => p_attribute8
380 , p_attribute9 => p_attribute9
381 , p_attribute10 => p_attribute10
382 , p_attribute11 => p_attribute11
383 , p_attribute12 => p_attribute12
384 , p_attribute13 => p_attribute13
385 , p_attribute14 => p_attribute14
386 , p_attribute15 => p_attribute15
387 , p_attribute16 => p_attribute16
388 , p_attribute17 => p_attribute17
389 , p_attribute18 => p_attribute18
390 , p_attribute19 => p_attribute19
391 , p_attribute20 => p_attribute20
392 , p_attribute21 => p_attribute21
393 , p_attribute22 => p_attribute22
394 , p_attribute23 => p_attribute23
395 , p_attribute24 => p_attribute24
396 , p_attribute25 => p_attribute25
397 , p_attribute26 => p_attribute26
398 , p_attribute27 => p_attribute27
399 , p_attribute28 => p_attribute28
400 , p_attribute29 => p_attribute29
401 , p_attribute30 => p_attribute30
402 , p_object_version_number => l_ovn );
403
404 END IF; -- l_ovn check
405
406 END load_hph_row;
407
408 FUNCTION get_pref_def_name ( p_pref_definition_id NUMBER ) RETURN VARCHAR2 IS
409
410 l_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
411
412 CURSOR csr_get_name IS
413 SELECT df.descriptive_flex_context_name
414 FROM fnd_descr_flex_contexts_vl df
415 , hxc_pref_definitions pd
416 WHERE pd.pref_definition_id = p_pref_definition_id
417 AND df.application_id =809
418 AND df.descriptive_flexfield_name = 'OTC PREFERENCES'
419 AND df.descriptive_flex_context_code = pd.code;
420
421 BEGIN
422
423 OPEN csr_get_name;
424 FETCH csr_get_name INTO l_name;
425 CLOSE csr_get_name;
426
427 RETURN l_name;
428
429 END get_pref_def_name;
430
431
432 FUNCTION get_attribute ( p_attribute_category VARCHAR2
433 , p_attribute VARCHAR2 ) RETURN VARCHAR2 IS
434
435 l_real_value VARCHAR2(150);
436
437 CURSOR csr_get_approval_style IS
438 SELECT name
439 FROM hxc_approval_styles
440 WHERE approval_style_id = TO_NUMBER(p_attribute);
441
442 CURSOR csr_get_application_set IS
443 SELECT application_set_name
444 FROM hxc_application_sets_v
445 WHERE application_set_id = TO_NUMBER(p_attribute);
446
447 CURSOR csr_get_retrieval_rule_grp IS
448 SELECT retrieval_rule_group_name
449 FROM hxc_retrieval_rule_groups_v
450 WHERE retrieval_rule_group_id = TO_NUMBER(p_attribute);
451
452 CURSOR csr_get_alias IS
453 SELECT alias_definition_name
454 FROM hxc_alias_definitions
455 WHERE alias_definition_id = TO_NUMBER(p_attribute);
456
457 CURSOR csr_get_recurring_period IS
458 SELECT rp.name
459 FROM hxc_recurring_periods rp
460 WHERE recurring_period_id = TO_NUMBER(p_attribute);
461
462 CURSOR csr_get_approval_period IS
463 SELECT aps.name
464 FROM hxc_approval_period_sets aps
465 WHERE approval_period_set_id = TO_NUMBER(p_attribute);
466
467 CURSOR csr_get_layout IS
468 SELECT layout_name
469 FROM hxc_layouts
470 WHERE layout_id = TO_NUMBER(p_attribute);
471
472 CURSOR csr_get_date_format IS
473 SELECT egc.attribute2
474 FROM hxc_entity_group_comps egc
475 WHERE egc.attribute1 = p_attribute
476 AND EXISTS ( SELECT 'x'
477 FROM hxc_entity_groups eg
478 WHERE eg.entity_group_id = egc.entity_group_id
479 AND eg.entity_type = 'HXC_SS_TC_DATE_FORMATS' );
480
481 CURSOR csr_get_time_entry_rule_name IS
482 SELECT time_entry_rule_group_name
483 FROM hxc_time_entry_rule_groups_v
484 WHERE time_entry_rule_group_id = TO_NUMBER(p_attribute);
485
486 BEGIN
487
488 IF ( p_attribute_category = 'TS_PER_APPROVAL_STYLE' )
489 THEN
490
491 OPEN csr_get_approval_style;
492 FETCH csr_get_approval_style INTO l_real_Value;
493 CLOSE csr_get_approval_style;
494
495 ELSIF ( p_attribute_category = 'TS_PER_APPLICATION_SET' )
496 THEN
497
498 OPEN csr_get_application_set;
499 FETCH csr_get_application_set INTO l_real_Value;
500 CLOSE csr_get_application_set;
501
502 ELSIF ( p_attribute_category = 'TS_PER_RETRIEVAL_RULES' )
503 THEN
504
505 OPEN csr_get_retrieval_rule_grp;
506 FETCH csr_get_retrieval_rule_grp INTO l_real_Value;
507 CLOSE csr_get_retrieval_rule_grp;
508
509 ELSIF ( p_attribute_category = 'TS_PER_APPROVAL_PERIODS' )
510 THEN
511
512 OPEN csr_get_approval_period;
513 FETCH csr_get_approval_period INTO l_real_Value;
514 CLOSE csr_get_approval_period;
515
516 ELSIF ( p_attribute_category = 'TC_W_TCRD_PERIOD' )
517 THEN
518
519 OPEN csr_get_recurring_period;
520 FETCH csr_get_recurring_period INTO l_real_value;
521 CLOSE csr_get_recurring_period;
522
523 ELSIF ( p_attribute_category = 'TC_W_TCRD_ALIASES' )
524 THEN
525
526 -- GPM: 115.9
527 -- commented in cursor now that we have alias preference.
528
529 OPEN csr_get_alias;
530 FETCH csr_get_alias INTO l_real_value;
531 CLOSE csr_get_alias;
532
533 ELSIF ( p_attribute_category = 'TC_W_TCRD_LAYOUT' )
534 THEN
535
536 OPEN csr_get_layout;
537 FETCH csr_get_layout INTO l_real_value;
538 CLOSE csr_get_layout;
539
540 ELSIF ( p_attribute_category = 'TC_W_DATE_FORMATS' )
541 THEN
542
543 OPEN csr_get_date_format;
544 FETCH csr_get_date_format INTO l_real_value;
545 CLOSE csr_get_date_format;
546
547 ELSIF ( p_attribute_category = 'TS_PER_AUDIT_REQUIREMENTS' )
548 THEN
549 OPEN csr_get_time_entry_rule_name;
550 FETCH csr_get_time_entry_rule_name INTO l_real_value;
551 CLOSE csr_get_time_entry_rule_name;
552
553 ELSE
554
555 l_real_value := p_attribute;
556
557 END IF;
558
559 RETURN l_real_value;
560
561 END get_attribute;
562
563 FUNCTION get_parent ( p_pref_top_node VARCHAR2
564 , p_pref_node VARCHAR2
565 , p_pref_level NUMBER
566 , p_count NUMBER ) RETURN VARCHAR2 IS
567
568 l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
569 l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
570 l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
571 l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
572 l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
573
574 l_full_name VARCHAR2(500);
575
576 l_last_level NUMBER(1) := 1;
577
578 CURSOR get_pref_hierarchy ( p_top_node VARCHAR2 ) IS
579 SELECT name
580 , level
581 , rownum cnt
582 from hxc_pref_hierarchies
583 start with name = p_top_node
584 connect by prior pref_hierarchy_id = parent_pref_hierarchy_id;
585
586 BEGIN
587
588 FOR t IN get_pref_hierarchy ( p_pref_top_node )
589 LOOP
590
591 IF ( l_last_level > t.level )
592 THEN
593 IF ( t.level = 2 )
594 THEN
595 l_level_two := NULL;
596 l_level_three := NULL;
597 l_level_four := NULL;
598 l_level_five := NULL;
599
600 ELSIF ( t.level = 3 )
601 THEN
602 l_level_three := NULL;
603 l_level_four := NULL;
604 l_level_five := NULL;
605
606 ELSIF ( t.level = 4 )
607 THEN
608 l_level_four := NULL;
609 l_level_five := NULL;
610 END IF;
611 END IF;
612
613
614 IF ( p_pref_level = t.level AND p_pref_node = t.name AND p_count = t.cnt )
615 THEN
616
617 l_full_name := l_level_one||l_level_two||l_level_three||l_level_four||l_level_five;
618
619 RETURN LTRIM(RTRIM(l_full_name));
620 END IF;
621
622 IF ( t.level = 1 AND t.level <> p_pref_level )
623 THEN
624 l_level_one := t.name;
625
626 ELSIF ( t.level = 2 AND t.level <> p_pref_level )
627 THEN
628 l_level_two := '.'||t.name;
629
630 ELSIF ( t.level = 3 AND t.level <> p_pref_level )
631 THEN
632 l_level_three := '.'||t.name;
633
634 ELSIF ( t.level = 4 AND t.level <> p_pref_level )
635 THEN
636 l_level_four := '.'||t.name;
637
638 ELSIF ( t.level = 5 AND t.level <> p_pref_level )
639 THEN
640 l_level_five := '.'||t.name;
641
642 END IF;
643
644 l_last_level := t.level;
645
646 END LOOP;
647
648 END get_parent;
649
650 END hxc_hph_upload_pkg;