1 Package Body hr_dflex_utility as
2 /* $Header: hrdfutil.pkb 120.1.12000000.1 2007/01/22 14:45:33 appldev ship $ */
3 --
4 -- PLSQL table of rows types
5 --
6 -- Varchar2 record type
7 --
8 Type Var2Rectype is record
9 (col1 varchar2(30)
10 ,col2 varchar2(255)
11 );
12 --
13 Type Var2TabType is table of Var2Rectype index by binary_integer;
14 --
15 -- Package Variables
16 --
17 g_package varchar2(33) := 'hr_dflex_utility.';
18 g_dfcode_structure l_ignore_dfcode_varray := l_ignore_dfcode_varray();
19 -- Bug fix 1218702.
20 g_debug boolean := hr_utility.debug_enabled;
21 --
22 -- ----------------------------------------------------------------------------
23 -- |------------------------< find_error_segment >-------------------|
24 -- ----------------------------------------------------------------------------
25 --
26 procedure find_error_segment(p_appl_short_name IN varchar2,
27 p_flexfield_name IN varchar2,
28 p_context_code IN varchar2,
29 p_error_seg_num IN number,
30 p_application_col_name OUT NOCOPY varchar2,
31 p_form_left_prompt OUT NOCOPY varchar2,
32 p_table_name OUT NOCOPY varchar2
33 )is
34 --
35 -- Cursors
36 --
37 cursor c_context_valid(p_appl_short_name in VARCHAR2,
38 p_flexfield_name in VARCHAR2,
39 p_context in VARCHAR2) is
40 select 'Y'
41 from FND_APPLICATION a,
42 FND_DESCR_FLEX_CONTEXTS dfc
43 where a.application_short_name = p_appl_short_name
44 and a.application_id = dfc.application_id
45 and dfc.DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
46 and dfc.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context;
47 --
48 -- Local Variables
49 --
50 -- Procedure Name
51 l_proc varchar2(72) := g_package||'find_error_segment';
52 -- Structure for holding the flexfield information from AOL
53 l_flexfield fnd_dflex.dflex_r;
54 -- Structure for holding information about the flexfield from AOL
55 l_flexinfo fnd_dflex.dflex_dr;
56 -- Structure for holding context information about 'Global Data Elements'
57 l_global_context fnd_dflex.context_r;
58 -- Structure for holding context information about the context
59 l_context fnd_dflex.context_r;
60 -- Structure for holding the segments corresponding to the Global Data
61 -- Elements
62 l_global_segments fnd_dflex.segments_dr;
63 -- The number of global data element segments
64 l_global_count number :=0;
65 -- Structure for holding the segments corresponding to the passed context
66 l_segments fnd_dflex.segments_dr;
67 -- Structure for holding the composite Global Data Element and passed
68 -- context information
69 l_segment_info var2tabtype;
70 -- General counter variable
71 l_counter number :=0;
72 -- A boolean determining whether we need to check for invalid segments
73 l_check_segments BOOLEAN := TRUE;
74 -- General exists variable, used as an 'INTO' variable for the cursor
75 l_exists varchar2(2);
76 -- Variable to hold the actual error segment, discounting the context field
77 l_error_seg_num number;
78 begin
79
80 -- Obtain the application column name corresponding to the error segment
81 -- in an AOL supported way. It would be much faster to perform a direct
82 -- access on the table FND_DESCR_FLEX_COLUMN_USAGES but this SQL would
83 -- not be supported by AOL.
84 --
85 -- First get the flexfield information
86 --
87 -- Bug fix 1218702.Debug check added to improve performance.
88 if g_debug then
89 hr_utility.set_location('Entering:'|| l_proc, 10);
90 end if;
91 fnd_dflex.get_flexfield(appl_short_name => p_appl_short_name,
92 flexfield_name => p_flexfield_name,
93 flexfield => l_flexfield,
94 flexinfo => l_flexinfo);
95 --
96 -- Use l_flexfield in calls that follow to identify the flexfield.
97 -- Next check that the context is valid, otherwise return the context
98 -- column name and prompt
99 --
100 if g_debug then
101 hr_utility.set_location(l_proc||'>'||to_char(p_error_seg_num)||'<', 20);
102 end if;
103 --
104 if (p_error_seg_num is null) then
105 --
106 -- The context is in error, and the context is not one of the global
107 -- data elements. In this case, we should simply set the application
108 -- column name and the context prompt to those associated with the
109 -- context information defined for this flexfield.
110 --
111 if g_debug then
112 hr_utility.set_location(l_proc, 30);
113 end if;
114 --
115 p_application_col_name := l_flexinfo.context_column_name;
116 p_form_left_prompt := l_flexinfo.form_context_prompt;
117 p_table_name := l_flexinfo.table_name;
118 --
119 -- Since we know that the context was invalid, we don't have to check
120 -- the segments.
121 --
122 l_check_segments := FALSE;
123 --
124 else
125 --
126 -- Now, we could still have an invalid context, but we don't know whether
127 -- the error segment corresponds to a segment or context. If the context
128 -- is not one of the flex field segments, and p_error_seg_num is 1, then
129 -- we know that the context is in error. However, the context could be one
130 -- of the global data elements, in which case segment 1 will be one of the
131 -- global segments, and the invalid context could be any of the others.
132 -- This means that we can't rely on p_error_seg_num value of 1 to indicate
133 -- an incorrect context, because it could correspond to one of the Global
134 -- data elements. That in itself is OK, because a context global segment
135 -- is still a segment, i.e. the segment code which follows will still
136 -- find the error, however, if p_error_seg_num is 1, it could correspond to
137 -- a context field that is not one of the segments, in which case, if we
138 -- just executed the code below we would display an invalid segment error,
139 -- corresponding to a valid segment.
140 --
141 -- This implies that the only way to be sure whether the context is in error,
142 -- is to check the list of contexts defined for this flex, and see if the
143 -- passed context is present in this list. If the context is not present,
144 -- then it is correct to assume that the context is invalid, otherwise, it
145 -- must be a segment.
146 --
147 -- Fetch the appropriate list of contexts
148 --
149 -- Don't bother to check NULL context, as this is always valid - e.g.
150 -- for Global Data elements, or no segments at all...
151 --
152 if p_context_code is not null then
153 open c_context_valid(p_appl_short_name, p_flexfield_name, p_context_code);
154 fetch c_context_valid into l_exists;
155 --
156 -- If this cursor did not return a record, we have an invalid context.
157 --
158 if c_context_valid%NOTFOUND then
159 p_application_col_name := l_flexinfo.context_column_name;
160 p_form_left_prompt := l_flexinfo.form_context_prompt;
161 p_table_name := l_flexinfo.table_name;
162 --
163 -- Since we know we have an invalid context, we need not check the segments.
164 --
165 l_check_segments := FALSE;
166 end if;
167 close c_context_valid;
168 end if; -- Not null context code
169 end if;
170 --
171 -- Do we have to check for an invalid segment?
172 --
173 if l_check_segments then
174
175 --
176 -- It is an invalid segment (even if that segment is the
177 -- context driver, e.g. in Global Data Elements).
178 --
179 -- The AOL Segments in the server flex engine are set up as follows:
180 --
181 -- GLOBAL DATA ELEMENTS (ordered by col seq num)
182 -- CONTEXT field
183 -- CONTEXT Specific SEGMENTS (ordered by col seq num)
184 -- We need to recreate this list, in order to ensure the
185 -- correct segment is shown in error when the
186 --
187 -- The segment in error corresponds to a number based on the complete
188 -- flex definition, i.e. we must also include the global data elements,
189 -- and furthermore consider those first, as they appear first in the segment
190 -- sorted list that the flex engine uses when validation occurs (details
191 -- from Gursat Olgun, AOL flex team Oct 1999)
192 --
193 if g_debug then
194 hr_utility.set_location(l_proc, 32);
195 end if;
196 --
197 -- First set up the Global Data Elements flexfield context, which is
198 -- always called 'Global Data Elements', as is not translated.
199 --
200 l_global_context := fnd_dflex.make_context(flexfield => l_flexfield,
201 context_code => 'Global Data Elements');
202 if g_debug then
203 hr_utility.set_location(l_proc, 34);
204 end if;
205 --
206 -- Fetch all enabled segments associated with this context. The flex server
207 -- engine will ignore non-enabled segments - see
208 -- /usr/home/arundell/11bugs/1033593/test_case/test.info
209 --
210 fnd_dflex.get_segments(context => l_global_context,
211 segments => l_global_segments,
212 enabled_only => true);
213 if g_debug then
214 hr_utility.set_location(l_proc, 36);
215 end if;
216 --
217 -- Store the global set of segments in a structure for later use.
218 -- col1 corresponds to the application column name
219 -- col2 corresponds to the segment prompt
220 -- We need only do this if there are global data elements for this
221 -- flexfield.
222 --
223
224 if (l_global_segments.application_column_name.count > 0) then
225
226 for l_counter in l_global_segments.application_column_name.first..
227 l_global_segments.application_column_name.last loop
228
229 -- Bug fix 1218702.
230 if g_debug then
231 hr_utility.set_location(l_proc||' - '||to_char(l_counter), 37);
232 end if;
233 l_segment_info(l_counter).col1 :=
234 l_global_segments.application_column_name(l_counter);
235 l_segment_info(l_counter).col2 := l_global_segments.row_prompt(l_counter);
236 l_global_count := l_global_count+1;
237
238 -- Bug fix 1218702.
239 if g_debug then
240 hr_utility.set_location(l_global_segments.application_column_name(l_counter)||
241 ' - '||to_char(l_counter), 38);
242 end if;
243 end loop;
244 else
245 l_global_count := 0;
246 end if;
247 --
248 -- Add information about the context column
249 --
250 l_global_count := l_global_count +1;
251
252 l_segment_info(l_global_count).col1 := l_flexinfo.context_column_name;
253 l_segment_info(l_global_count).col2 := l_flexinfo.form_context_prompt;
254 --
255 -- Next get the specific information if the context is not global data elements
256 --
257 if (p_context_code is not null) then
258 if g_debug then
259 hr_utility.set_location(l_proc, 40);
260 end if;
261 --
262 l_context := fnd_dflex.make_context(flexfield => l_flexfield,
263 context_code => p_context_code);
264 --
265 -- Retrieve the segment information for this context
266 --
267 if g_debug then
268 hr_utility.set_location(l_proc, 50);
269 end if;
270 --
271 fnd_dflex.get_segments(context => l_context,
272 segments => l_segments,
273 enabled_only => true);
274 --
275 -- Append the specific context segment information to the Global Segment Information
276 -- again, checking that there is information to obtain
277 --
278 if (l_segments.application_column_name.count > 0) then
279 for l_counter in l_segments.application_column_name.first..
280 l_segments.application_column_name.last loop
281 l_segment_info(l_counter+l_global_count).col1 :=
282 l_segments.application_column_name(l_counter);
283 l_segment_info(l_counter+l_global_count).col2 :=
284 l_segments.row_prompt(l_counter);
285 -- Bug fix 1218702.Debug check added to improve performance.
286 if g_debug then
287 hr_utility.set_location(l_segments.application_column_name(l_counter)||' - '||
288 to_char(l_global_count+l_counter), 52);
289 end if;
290 end loop;
291 end if;
292 end if;
293 if g_debug then
294 hr_utility.set_location(l_proc, 55);
295 end if;
296
297 --
298 -- Next retrieve the application column name corresponding to the segment
299 -- in error. We know where this is in the table because the sequence is
300 -- used as the order key. This should be the same sequence as is used
301 -- inside the FND_FLEX_DESCVAL procedures.
302 --
303 p_application_col_name := l_segment_info(p_error_seg_num).col1;
304 p_form_left_prompt := l_segment_info(p_error_seg_num).col2;
305 p_table_name := l_flexinfo.table_name;
306 --
307 end if;
308 --
309 if g_debug then
310 hr_utility.set_location('Leaving: '||l_proc, 60);
311 end if;
312 --
313 end find_error_segment;
314 -----------------------------------------------------------------------------
315 -- ignore validation procedures using varry of flexcodes to ignore.
316 -------------------------------------------------------------------------------
317 -- create it
318 -------------------------------------------------------------------------------
319 --
320 procedure create_ignore_df_validation(p_rec in out nocopy l_ignore_dfcode_varray) is
321 l_proc varchar2(72) := 'create_ignore_df_validation';
322 begin
323 if g_debug then
324 hr_utility.set_location('Entering:'|| l_proc, 10);
325 end if;
326 if p_rec.count <> 0
327 then
328 g_dfcode_structure := p_rec;
329 end if;
330 if g_debug then
331 hr_utility.set_location('Leaving:'|| l_proc, 20);
332 end if;
333 end create_ignore_df_validation;
334 --
335 -- user calls a population script to populate this array
336 ----------------------------------------------------------------------------
337 -- check it
338 ----------------------------------------------------------------------------
339 function check_ignore_df_varray(p_structure in varchar2) return boolean is
340 l_proc varchar2(72) := 'check_ignore_df_varray';
341 begin
342 if g_debug then
343 hr_utility.set_location('Entering:'|| l_proc, 10);
344 end if;
345 if g_dfcode_structure.count <> 0
346 then
347 for l_count in g_dfcode_structure.first..g_dfcode_structure.last
348 loop
349 if g_dfcode_structure(l_count) = p_structure
350 then
351 return true;
352 end if;
353 end loop;
354 end if;
355 if g_debug then
356 hr_utility.set_location('Leaving:'|| l_proc, 20);
357 end if;
358 return false;
359 end;
360 --
361 ----------------------------------------------------------------------------
362 -- delete it
363 ----------------------------------------------------------------------------
364 procedure remove_ignore_df_validation is
365 l_proc varchar2(72) := 'remove_ignore_df_validation';
366 begin
367 if g_debug then
368 hr_utility.set_location('Entering:'|| l_proc, 10);
369 end if;
370 g_dfcode_structure.delete;
371 if g_debug then
372 hr_utility.set_location('Leaving:'|| l_proc, 20);
373 end if;
374 end;
375 --
376 --
377 -- ----------------------------------------------------------------------------
378 -- |------------------------< ins_or_upd_descflex_attribs >-------------------|
379 -- ----------------------------------------------------------------------------
380 --
381 procedure ins_or_upd_descflex_attribs
382 (p_appl_short_name in varchar2
383 ,p_descflex_name in varchar2
384 ,p_attribute_category in varchar2
385 ,p_attribute1_name in varchar2 default null
386 ,p_attribute1_value in varchar2 default null
387 ,p_attribute2_name in varchar2 default null
388 ,p_attribute2_value in varchar2 default null
389 ,p_attribute3_name in varchar2 default null
390 ,p_attribute3_value in varchar2 default null
391 ,p_attribute4_name in varchar2 default null
392 ,p_attribute4_value in varchar2 default null
393 ,p_attribute5_name in varchar2 default null
394 ,p_attribute5_value in varchar2 default null
395 ,p_attribute6_name in varchar2 default null
396 ,p_attribute6_value in varchar2 default null
397 ,p_attribute7_name in varchar2 default null
398 ,p_attribute7_value in varchar2 default null
399 ,p_attribute8_name in varchar2 default null
400 ,p_attribute8_value in varchar2 default null
401 ,p_attribute9_name in varchar2 default null
402 ,p_attribute9_value in varchar2 default null
403 ,p_attribute10_name in varchar2 default null
404 ,p_attribute10_value in varchar2 default null
405 ,p_attribute11_name in varchar2 default null
406 ,p_attribute11_value in varchar2 default null
407 ,p_attribute12_name in varchar2 default null
408 ,p_attribute12_value in varchar2 default null
409 ,p_attribute13_name in varchar2 default null
410 ,p_attribute13_value in varchar2 default null
411 ,p_attribute14_name in varchar2 default null
412 ,p_attribute14_value in varchar2 default null
413 ,p_attribute15_name in varchar2 default null
414 ,p_attribute15_value in varchar2 default null
415 ,p_attribute16_name in varchar2 default null
416 ,p_attribute16_value in varchar2 default null
417 ,p_attribute17_name in varchar2 default null
418 ,p_attribute17_value in varchar2 default null
419 ,p_attribute18_name in varchar2 default null
420 ,p_attribute18_value in varchar2 default null
421 ,p_attribute19_name in varchar2 default null
422 ,p_attribute19_value in varchar2 default null
423 ,p_attribute20_name in varchar2 default null
424 ,p_attribute20_value in varchar2 default null
425 ,p_attribute21_name in varchar2 default null
426 ,p_attribute21_value in varchar2 default null
427 ,p_attribute22_name in varchar2 default null
428 ,p_attribute22_value in varchar2 default null
429 ,p_attribute23_name in varchar2 default null
430 ,p_attribute23_value in varchar2 default null
431 ,p_attribute24_name in varchar2 default null
432 ,p_attribute24_value in varchar2 default null
433 ,p_attribute25_name in varchar2 default null
434 ,p_attribute25_value in varchar2 default null
435 ,p_attribute26_name in varchar2 default null
436 ,p_attribute26_value in varchar2 default null
437 ,p_attribute27_name in varchar2 default null
438 ,p_attribute27_value in varchar2 default null
439 ,p_attribute28_name in varchar2 default null
440 ,p_attribute28_value in varchar2 default null
441 ,p_attribute29_name in varchar2 default null
442 ,p_attribute29_value in varchar2 default null
443 ,p_attribute30_name in varchar2 default null
444 ,p_attribute30_value in varchar2 default null
445 )
446 is
447 --
448 l_proc varchar2(72) := g_package||'ins_or_upd_descflex_attribs';
449 --
450 l_attr_set Var2TabType;
451 l_attr_set_cnt binary_integer;
452 l_segment_set Var2TabType;
453 l_segment_cnt binary_integer;
454 l_seg_tor_cnt binary_integer;
455 l_ne_attr_set Var2TabType;
456 --
457 l_seg_column_name varchar2(30);
458 l_seg_value varchar2(255);
459 l_ne_column_name varchar2(30);
460 l_ne_column_value varchar2(255);
461 l_attr_name varchar2(30);
462 l_attr_value varchar2(255);
463 l_enab_seg_count number;
464 l_first_enab_segnum number;
465 l_error_seg number;
466 i number;
467 l_app_col_name FND_DESCR_FLEX_COLUMN_USAGES.APPLICATION_COLUMN_NAME%TYPE;
468 l_table_name varchar2(60);
469 l_flex_code varchar2(40) := NULL; -- wwb 2256742
470 l_error boolean := false;
471
472 -- l_id_in varchar2(255);
473 -- l_id_out varchar2(255);
474 -- l_context boolean;
475 -- start for fix of 5132936
476 l_effective_date date;
477
478 cursor date_set is
479 select EFFECTIVE_DATE from fnd_sessions
480 where session_id=userenv('sessionid');
481 -- end for fix of 5132936
482 --
483 -- ARR: 1999/10/26 Version 110.9
484 -- Note, no particular column to base this variable on, since it is a generic
485 -- flex column. Picked person DF as this is likely to be the one that is increased
486 -- in line with any others...
487 -- If PL/SQL value errors reported, look here first!
488 --
489 l_value PER_ALL_PEOPLE_F.ATTRIBUTE1%TYPE;
490 l_flex_seg_error_prompt FND_DESCR_FLEX_COL_USAGE_TL.FORM_LEFT_PROMPT%TYPE;
491 --
492 -- --------------------------------------------------------------------------
493 -- |-----------------------< AttributeSet_AddAttrDets >---------------------|
494 -- --------------------------------------------------------------------------
495 --
496 -- Add attribute details to a attribute set
497 --
498 procedure AttributeSet_AddAttrDets
499 (p_attr_set in out nocopy Var2TabType
500 ,p_attr_set_rw_num in out nocopy number
501 ,p_attr_name in varchar2
502 ,p_attr_value in varchar2
503 )
504 is
505 --
506 l_proc varchar2(80)
507 := g_package||'AttributeSet_AddAttrDets';
508 --
509 begin
510 --
511 -- Check mandatory parameters have been set
512 --
513 --5132936 start
514 l_effective_date:=to_date(null);
515 open date_set;
516 fetch date_set into l_effective_date;
517 if date_set%notfound then
518 if g_debug then
519 hr_utility.set_location(l_proc,13);
520 end if;
521 l_effective_date := trunc(sysdate);
522 close date_set;
523 else
524 if g_debug then
525 hr_utility.set_location(l_proc,15);
526 end if;
527 close date_set;
528 end if;
529 --5132936 end
530
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'p_attr_set_rw_num'
534 ,p_argument_value => p_attr_set_rw_num
535 );
536 --
537 -- Check if the attribute name is set
538 --
539 if p_attr_name is not null then
540 --
541 p_attr_set(p_attr_set_rw_num).col1 := p_attr_name;
542 p_attr_set(p_attr_set_rw_num).col2 := p_attr_value;
543 --
544 p_attr_set_rw_num := p_attr_set_rw_num + 1;
545 --
546 elsif p_attr_value is not null then
547 --
548 hr_utility.set_message(800, 'HR_52993_NULL_DF_SEG_NAME');
549 hr_utility.raise_error;
550 --
551 end if;
552 --
553 end AttributeSet_AddAttrDets;
554 --
555 -- --------------------------------------------------------------------------
556 -- |-----------------------------< GetNonExistRows >------------------------|
557 -- --------------------------------------------------------------------------
558 --
559 -- Get PLSQL TOR rows which exist in TOR1 but not TOR2
560 --
561 procedure GetNonExistRows
562 (p_plsqltor1 in Var2TabType
563 ,p_plsqltor2 in Var2TabType
564 --
565 ,p_ne_tor_rws out nocopy Var2TabType
566 )
567 is
568 --
569 -- l_proc varchar2(80) := g_package||'GetNonExistRows';
570 --
571 l_tor1_row Var2Rectype;
572 --
573 l_tor1_count binary_integer;
574 l_tor2_count binary_integer;
575 l_ne_rw_count binary_integer;
576 l_tor1_ele_value varchar2(255);
577 l_tor2_ele_value varchar2(255);
578 l_match_count number;
579 --
580 begin
581 --
582 -- Check if TOR 1 contains rows
583 --
584 if p_plsqltor1.count > 0 then
585 --
586 -- Loop through TOR rows
587 --
588 l_ne_rw_count := 0;
589 --
590 for l_tor1_count in p_plsqltor1.first .. p_plsqltor1.last loop
591 --
592 l_tor1_ele_value := p_plsqltor1(l_tor1_count).col1;
593 --
594 -- Check if TOR 2 contains rows
595 --
596 if p_plsqltor2.count > 0 then
597 --
598 -- Loop through TOR rows
599 --
600 l_match_count := 0;
601 --
602 for l_tor2_count in p_plsqltor2.first .. p_plsqltor2.last loop
603 --
604 l_tor2_ele_value := p_plsqltor2(l_tor2_count).col1;
605 --
606 -- Check for a value match
607 --
608 if l_tor1_ele_value = l_tor2_ele_value then
609 --
610 l_match_count := l_match_count + 1;
611 exit;
612 --
613 end if;
614 --
615 end loop;
616 --
617 -- Check for a non existant value
618 --
619 if l_match_count = 0 then
620 --
621 -- Set the NE row to a local row
622 --
623 l_tor1_row := p_plsqltor1(l_tor1_count);
624 --
625 -- Add the NE row to the NE TOR
626 --
627 p_ne_tor_rws(l_ne_rw_count) := l_tor1_row;
628 l_ne_rw_count := l_ne_rw_count + 1;
629 --
630 end if;
631 --
632 end if;
633 --
634 end loop;
635 --
636 end if;
637 --
638 end GetNonExistRows;
639 --
640 begin
641 if g_debug then
642 hr_utility.set_location('Entering:'|| l_proc, 10);
643 end if;
644 --
645 -- Check mandatory parameters have been set
646 --
647 hr_api.mandatory_arg_error
648 (p_api_name => l_proc
649 ,p_argument => 'appl_short_name'
650 ,p_argument_value => p_appl_short_name
651 );
652 --
653 hr_api.mandatory_arg_error
654 (p_api_name => l_proc
655 ,p_argument => 'descflex_name'
656 ,p_argument_value => p_descflex_name
657 );
658 if g_debug then
659 hr_utility.set_location(l_proc, 20);
660 end if;
661 --
662 -- 2256742 ignore desc flex validation if flexcode exists in varray:
663 --
664 l_flex_code := p_descflex_name;
665 --
666 if check_ignore_df_varray(l_flex_code)
667 then
668 if g_debug then
669 hr_utility.set_location(l_proc, 25);
670 end if;
671 -- 2256742 no validation required, exit procedure
672 return;
673 else
674 -- 2256742 go ahead and validate if flex code is not in varray.
675 --
676 --
677 -- Build the DF Attribute Set
678 --
679 l_attr_set_cnt := 0;
680 --
681 -- Add Attributes to the Attribute Set
682 --
683 AttributeSet_AddAttrDets
684 (p_attr_set => l_attr_set
685 ,p_attr_set_rw_num => l_attr_set_cnt
686 ,p_attr_name => p_attribute1_name
687 ,p_attr_value => p_attribute1_value
688 );
689 --
690 AttributeSet_AddAttrDets
691 (p_attr_set => l_attr_set
692 ,p_attr_set_rw_num => l_attr_set_cnt
693 ,p_attr_name => p_attribute2_name
694 ,p_attr_value => p_attribute2_value
695 );
696 --
697 AttributeSet_AddAttrDets
698 (p_attr_set => l_attr_set
699 ,p_attr_set_rw_num => l_attr_set_cnt
700 ,p_attr_name => p_attribute3_name
701 ,p_attr_value => p_attribute3_value
702 );
703 --
704 AttributeSet_AddAttrDets
705 (p_attr_set => l_attr_set
706 ,p_attr_set_rw_num => l_attr_set_cnt
707 ,p_attr_name => p_attribute4_name
708 ,p_attr_value => p_attribute4_value
709 );
710 --
711 AttributeSet_AddAttrDets
712 (p_attr_set => l_attr_set
713 ,p_attr_set_rw_num => l_attr_set_cnt
714 ,p_attr_name => p_attribute5_name
715 ,p_attr_value => p_attribute5_value
716 );
717 --
718 AttributeSet_AddAttrDets
719 (p_attr_set => l_attr_set
720 ,p_attr_set_rw_num => l_attr_set_cnt
721 ,p_attr_name => p_attribute6_name
722 ,p_attr_value => p_attribute6_value
723 );
724 --
725 AttributeSet_AddAttrDets
726 (p_attr_set => l_attr_set
727 ,p_attr_set_rw_num => l_attr_set_cnt
728 ,p_attr_name => p_attribute7_name
729 ,p_attr_value => p_attribute7_value
730 );
731 --
732 AttributeSet_AddAttrDets
733 (p_attr_set => l_attr_set
734 ,p_attr_set_rw_num => l_attr_set_cnt
735 ,p_attr_name => p_attribute8_name
736 ,p_attr_value => p_attribute8_value
737 );
738 --
739 AttributeSet_AddAttrDets
740 (p_attr_set => l_attr_set
741 ,p_attr_set_rw_num => l_attr_set_cnt
742 ,p_attr_name => p_attribute9_name
743 ,p_attr_value => p_attribute9_value
744 );
745 --
746 AttributeSet_AddAttrDets
747 (p_attr_set => l_attr_set
748 ,p_attr_set_rw_num => l_attr_set_cnt
749 ,p_attr_name => p_attribute10_name
750 ,p_attr_value => p_attribute10_value
751 );
752 --
753 AttributeSet_AddAttrDets
754 (p_attr_set => l_attr_set
755 ,p_attr_set_rw_num => l_attr_set_cnt
756 ,p_attr_name => p_attribute11_name
757 ,p_attr_value => p_attribute11_value
758 );
759 --
760 AttributeSet_AddAttrDets
761 (p_attr_set => l_attr_set
762 ,p_attr_set_rw_num => l_attr_set_cnt
763 ,p_attr_name => p_attribute12_name
764 ,p_attr_value => p_attribute12_value
765 );
766 --
767 AttributeSet_AddAttrDets
768 (p_attr_set => l_attr_set
769 ,p_attr_set_rw_num => l_attr_set_cnt
770 ,p_attr_name => p_attribute13_name
771 ,p_attr_value => p_attribute13_value
772 );
773 --
774 AttributeSet_AddAttrDets
775 (p_attr_set => l_attr_set
776 ,p_attr_set_rw_num => l_attr_set_cnt
777 ,p_attr_name => p_attribute14_name
778 ,p_attr_value => p_attribute14_value
779 );
780 --
781 AttributeSet_AddAttrDets
782 (p_attr_set => l_attr_set
783 ,p_attr_set_rw_num => l_attr_set_cnt
784 ,p_attr_name => p_attribute15_name
785 ,p_attr_value => p_attribute15_value
786 );
787 --
788 AttributeSet_AddAttrDets
789 (p_attr_set => l_attr_set
790 ,p_attr_set_rw_num => l_attr_set_cnt
791 ,p_attr_name => p_attribute16_name
792 ,p_attr_value => p_attribute16_value
793 );
794 --
795 AttributeSet_AddAttrDets
796 (p_attr_set => l_attr_set
797 ,p_attr_set_rw_num => l_attr_set_cnt
798 ,p_attr_name => p_attribute17_name
799 ,p_attr_value => p_attribute17_value
800 );
801 --
802 AttributeSet_AddAttrDets
803 (p_attr_set => l_attr_set
804 ,p_attr_set_rw_num => l_attr_set_cnt
805 ,p_attr_name => p_attribute18_name
806 ,p_attr_value => p_attribute18_value
807 );
808 --
809 AttributeSet_AddAttrDets
810 (p_attr_set => l_attr_set
811 ,p_attr_set_rw_num => l_attr_set_cnt
812 ,p_attr_name => p_attribute19_name
813 ,p_attr_value => p_attribute19_value
814 );
815 --
816 AttributeSet_AddAttrDets
817 (p_attr_set => l_attr_set
818 ,p_attr_set_rw_num => l_attr_set_cnt
819 ,p_attr_name => p_attribute20_name
820 ,p_attr_value => p_attribute20_value
821 );
822 --
823 AttributeSet_AddAttrDets
824 (p_attr_set => l_attr_set
825 ,p_attr_set_rw_num => l_attr_set_cnt
826 ,p_attr_name => p_attribute21_name
827 ,p_attr_value => p_attribute21_value
828 );
829 --
830 AttributeSet_AddAttrDets
831 (p_attr_set => l_attr_set
832 ,p_attr_set_rw_num => l_attr_set_cnt
833 ,p_attr_name => p_attribute22_name
834 ,p_attr_value => p_attribute22_value
835 );
836 --
837 AttributeSet_AddAttrDets
838 (p_attr_set => l_attr_set
839 ,p_attr_set_rw_num => l_attr_set_cnt
840 ,p_attr_name => p_attribute23_name
841 ,p_attr_value => p_attribute23_value
842 );
843 --
844 AttributeSet_AddAttrDets
845 (p_attr_set => l_attr_set
846 ,p_attr_set_rw_num => l_attr_set_cnt
847 ,p_attr_name => p_attribute24_name
848 ,p_attr_value => p_attribute24_value
849 );
850 --
851 AttributeSet_AddAttrDets
852 (p_attr_set => l_attr_set
853 ,p_attr_set_rw_num => l_attr_set_cnt
854 ,p_attr_name => p_attribute25_name
855 ,p_attr_value => p_attribute25_value
856 );
857 --
858 AttributeSet_AddAttrDets
859 (p_attr_set => l_attr_set
860 ,p_attr_set_rw_num => l_attr_set_cnt
861 ,p_attr_name => p_attribute26_name
862 ,p_attr_value => p_attribute26_value
863 );
864 --
865 AttributeSet_AddAttrDets
866 (p_attr_set => l_attr_set
867 ,p_attr_set_rw_num => l_attr_set_cnt
868 ,p_attr_name => p_attribute27_name
869 ,p_attr_value => p_attribute27_value
870 );
871 --
872 AttributeSet_AddAttrDets
873 (p_attr_set => l_attr_set
874 ,p_attr_set_rw_num => l_attr_set_cnt
875 ,p_attr_name => p_attribute28_name
876 ,p_attr_value => p_attribute28_value
877 );
878 --
879 AttributeSet_AddAttrDets
880 (p_attr_set => l_attr_set
881 ,p_attr_set_rw_num => l_attr_set_cnt
882 ,p_attr_name => p_attribute29_name
883 ,p_attr_value => p_attribute29_value
884 );
885 --
886 AttributeSet_AddAttrDets
887 (p_attr_set => l_attr_set
888 ,p_attr_set_rw_num => l_attr_set_cnt
889 ,p_attr_name => p_attribute30_name
890 ,p_attr_value => p_attribute30_value
891 );
892 if g_debug then
893 hr_utility.set_location(l_proc, 30);
894 end if;
895 --
896 -- Populate the AOL details
897 --
898 -- Set the descriptive flex context value in AOL
899 --
900 fnd_flex_descval.set_context_value
901 (p_attribute_category
902 );
903 if g_debug then
904 hr_utility.set_location(l_proc, 40);
905 end if;
906 --
907 -- Set the descriptive flex column details
908 --
909 -- Check if any DF attribute details exist
910 --
911 if l_attr_set.count > 0 then
912 --
913 -- Loop through the attribute set
914 --
915 for l_attr_set_cnt in l_attr_set.first .. l_attr_set.last loop
916 --
917 l_attr_name := l_attr_set(l_attr_set_cnt).col1;
918 l_attr_value := l_attr_set(l_attr_set_cnt).col2;
919 --
920 -- Add attribute details to AOL DF column details
921 --
922 fnd_flex_descval.set_column_value
923 (column_name => l_attr_name
924 ,column_value => l_attr_value
925 );
926 --
927 end loop;
928 --
929 end if;
930 if g_debug then
931 hr_utility.set_location(l_proc, 40);
932 end if;
933 --
934 -- Validate DF column details passed to AOL
935 --
936 if FND_FLEX_DESCVAL.validate_desccols
937 (appl_short_name => p_appl_short_name
938 ,desc_flex_name => p_descflex_name
939 --5132936 start here
940 ,validation_date=>l_effective_date
941 --5132936 ends here
942 ,VALUES_OR_IDS => 'I'
943 )
944 then
945 --
946 -- AOL DF validation succeeds - do nothing
947 --
948 hr_utility.set_location(l_proc, 50);
949 -- Bug fix 1218702.Debug check added to improve performance.
950 if g_debug then
951 FOR i IN 1..20 loop
952 hr_utility.set_location(substr(to_char(i)||'...'
953 ||nvl(FND_FLEX_DESCVAL.segment_column_name(i),'NULL VALUE'),1,70),980);
954 END LOOP;
955 end if;
956 --
957 else
958 -- Bug fix 1218702.Debug check added to improve performance.
959 if g_debug then
960 hr_utility.set_location(substr('--====== FLEX VALUES =====--',1,70),69);
961 hr_utility.set_location(substr('Error segment:'
962 ||nvl(to_char(fnd_flex_descval.error_segment),'CONTEXT'),1,70),970);
963 FOR i IN 1..20 loop
964 hr_utility.set_location(substr(to_char(i)||'...'
965 ||nvl(FND_FLEX_DESCVAL.segment_column_name(i),'NULL VALUE'),1,70),980);
966 END LOOP;
967 end if;
968 --
969 -- Raise or trap the AOL error
970 --
971 hr_utility.set_location(l_proc, 60);
972 --
973 -- The FND FLEX DESCVAL package makes a call to FND MESSAGE
974 -- get encoded
975 hr_message.parse_encoded(p_encoded_error =>
976 FND_FLEX_DESCVAL.encoded_error_message);
977 hr_utility.set_location(l_proc, 62);
978 --
979 if ((hr_message.last_message_app = 'FND') and
980 (hr_message.last_message_name = 'FLEX-VALUE NOT FOUND')) then
981 --
982 if g_debug then
983 hr_utility.set_location(l_proc, 64);
984 end if;
985 --
986 -- In this case, there was an invalid segment, or context.
987 -- We must allow for the possibility that the context value
988 -- was not valid.
989 -- Should trap this error
990 -- and replace with a more user friendly message
991 -- First, work out which segment is in error, and change the
992 -- error message to indicate which of these segments is
993 -- is incorrect.
994 --
995 l_error_seg := FND_FLEX_DESCVAL.error_segment;
996 find_error_segment(p_appl_short_name => p_appl_short_name,
997 p_flexfield_name => p_descflex_name,
998 p_context_code => p_attribute_category,
999 p_error_seg_num => l_error_seg,
1000 p_application_col_name => l_app_col_name,
1001 p_form_left_prompt => l_flex_seg_error_prompt,
1002 p_table_name => l_table_name
1003 );
1004 if g_debug then
1005 hr_utility.set_location(l_proc || l_flex_seg_error_prompt, 66);
1006 end if;
1007 l_value := hr_message.get_token_value(p_token_name => 'VALUE');
1008 --
1009 -- Now have the additional error information, raise a different error
1010 -- using this information.
1011 --
1012 fnd_message.set_name('PER','HR_FLEX_VALUE_INVALID');
1013 fnd_message.set_token('COLUMN',l_app_col_name);
1014 fnd_message.set_token('VALUE',l_value);
1015 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
1016 hr_multi_message.add
1017 (p_associated_column1 => l_table_name ||'.'||l_app_col_name);
1018 l_error := true;
1019 --
1020 elsif (hr_message.last_message_app = 'FND') and
1021 (hr_message.last_message_name = 'FLEX-NULL SEGMENT') then
1022 if g_debug then
1023 hr_utility.set_location(l_proc, 67);
1024 end if;
1025 --
1026 -- In this case, there was a missing mandatory segment
1027 -- First, work out which segment is in error, and change the
1028 -- error message to indicate which of these segments is
1029 -- is incorrect.
1030 --
1031 l_error_seg := FND_FLEX_DESCVAL.error_segment;
1032 find_error_segment(p_appl_short_name => p_appl_short_name,
1033 p_flexfield_name => p_descflex_name,
1034 p_context_code => p_attribute_category,
1035 p_error_seg_num => l_error_seg,
1036 p_application_col_name => l_app_col_name,
1037 p_form_left_prompt => l_flex_seg_error_prompt,
1038 p_table_name => l_table_name
1039 );
1040 if g_debug then
1041 hr_utility.set_location(l_proc, 68);
1042 end if;
1043 --
1044 -- Now have the additional error information, raise a different error
1045 -- using this information.
1046 --
1047 fnd_message.set_name('PER','HR_FLEX_VALUE_MISSING');
1048 fnd_message.set_token('COLUMN',l_app_col_name);
1049 fnd_message.set_token('PROMPT',l_flex_seg_error_prompt);
1050 hr_multi_message.add
1051 (p_associated_column1 => l_table_name || '.' || l_app_col_name);
1052 l_error := true;
1053 --
1054 else
1055 --
1056 -- Some other non-expected error, simply raise the error in the
1057 -- previous fashion.
1058 --
1059 -- Bug fix: 2504004
1060 hr_utility.set_location(l_proc, 69);
1061 -- dbms_standard.raise_application_error
1062 -- (-20001
1063 -- ,fnd_flex_descval.error_message
1064 -- );
1065 fnd_message.set_encoded
1066 (fnd_flex_descval.encoded_error_message);
1067 fnd_message.raise_error;
1068 --
1069 end if;
1070 --
1071 end if; -- FND_FLEX_DESCVAL.validate_desccols
1072 if g_debug then
1073 hr_utility.set_location(l_proc, 70);
1074 end if;
1075 --
1076 -- Check if error has been added to multi_message, and if so, do not
1077 -- proceed with following logic
1078 IF not l_error
1079 THEN
1080 --
1081 -- Build the segment set
1082 --
1083 -- Note: We start at segment 2 because AOL store the context value
1084 -- as segment column 1
1085 --
1086 l_seg_tor_cnt := 0;
1087 --
1088 -- Check if the attribute category is set
1089 --
1090 --
1091 -- ARR 5/7/99
1092 -- 110.6 Change, always start counting the enabled segments
1093 -- from the first one, it doesn't matter if 'ATTRIBUTE_CATEGORY'
1094 -- appears in the list, as the context, or lack of it, is
1095 -- correctly validated by the AOL routines.
1096 --
1097 l_first_enab_segnum := 1;
1098 --
1099 -- Get the enabled segment count
1100 --
1101 l_enab_seg_count := fnd_flex_descval.segment_count;
1102 --
1103 for l_segment_cnt in l_first_enab_segnum..l_enab_seg_count loop
1104 --
1105 -- Get the segment column name
1106 --
1107 l_seg_column_name := fnd_flex_descval.segment_column_name(l_segment_cnt);
1108 --
1109 -- Check if the column name is set
1110 --
1111 if l_seg_column_name is not null then
1112 /* Commented out fix 1230951 due to bug 1919660.
1113 --
1114 -- fix #1230951
1115 --
1116 l_context := FALSE;
1117 if l_seg_column_name = p_attribute1_name then
1118 l_id_in := p_attribute1_value;
1119 elsif l_seg_column_name = p_attribute2_name then
1120 l_id_in := p_attribute2_value;
1121 elsif l_seg_column_name = p_attribute3_name then
1122 l_id_in := p_attribute3_value;
1123 elsif l_seg_column_name = p_attribute4_name then
1124 l_id_in := p_attribute4_value;
1125 elsif l_seg_column_name = p_attribute5_name then
1126 l_id_in := p_attribute5_value;
1127 elsif l_seg_column_name = p_attribute6_name then
1128 l_id_in := p_attribute6_value;
1129 elsif l_seg_column_name = p_attribute7_name then
1130 l_id_in := p_attribute7_value;
1131 elsif l_seg_column_name = p_attribute8_name then
1132 l_id_in := p_attribute8_value;
1133 elsif l_seg_column_name = p_attribute9_name then
1134 l_id_in := p_attribute9_value;
1135 elsif l_seg_column_name = p_attribute10_name then
1136 l_id_in := p_attribute10_value;
1137 elsif l_seg_column_name = p_attribute11_name then
1138 l_id_in := p_attribute11_value;
1139 elsif l_seg_column_name = p_attribute12_name then
1140 l_id_in := p_attribute12_value;
1141 elsif l_seg_column_name = p_attribute13_name then
1142 l_id_in := p_attribute13_value;
1143 elsif l_seg_column_name = p_attribute14_name then
1144 l_id_in := p_attribute14_value;
1145 elsif l_seg_column_name = p_attribute15_name then
1146 l_id_in := p_attribute15_value;
1147 elsif l_seg_column_name = p_attribute16_name then
1148 l_id_in := p_attribute16_value;
1149 elsif l_seg_column_name = p_attribute17_name then
1150 l_id_in := p_attribute17_value;
1151 elsif l_seg_column_name = p_attribute18_name then
1152 l_id_in := p_attribute18_value;
1153 elsif l_seg_column_name = p_attribute19_name then
1154 l_id_in := p_attribute19_value;
1155 elsif l_seg_column_name = p_attribute20_name then
1156 l_id_in := p_attribute20_value;
1157 elsif l_seg_column_name = p_attribute21_name then
1158 l_id_in := p_attribute21_value;
1159 elsif l_seg_column_name = p_attribute22_name then
1160 l_id_in := p_attribute22_value;
1161 elsif l_seg_column_name = p_attribute23_name then
1162 l_id_in := p_attribute23_value;
1163 elsif l_seg_column_name = p_attribute24_name then
1164 l_id_in := p_attribute24_value;
1165 elsif l_seg_column_name = p_attribute25_name then
1166 l_id_in := p_attribute25_value;
1167 elsif l_seg_column_name = p_attribute26_name then
1168 l_id_in := p_attribute26_value;
1169 elsif l_seg_column_name = p_attribute27_name then
1170 l_id_in := p_attribute27_value;
1171 elsif l_seg_column_name = p_attribute28_name then
1172 l_id_in := p_attribute28_value;
1173 elsif l_seg_column_name = p_attribute29_name then
1174 l_id_in := p_attribute29_value;
1175 elsif l_seg_column_name = p_attribute30_name then
1176 l_id_in := p_attribute30_value;
1177 else
1178 l_context := TRUE;
1179 end if;
1180 --
1181 if not l_context then
1182 l_id_out := fnd_flex_descval.segment_id(l_segment_cnt);
1183 if l_id_in <> l_id_out
1184 or l_id_in IS NULL and l_id_out IS NOT NULL
1185 or l_id_in IS NOT NULL and l_id_out IS NULL then
1186 -- call find_error_segment to find table name
1187 find_error_segment(p_appl_short_name => p_appl_short_name,
1188 p_flexfield_name => p_descflex_name,
1189 p_context_code => p_attribute_category,
1190 p_error_seg_num => null,
1191 p_application_col_name => l_app_col_name,
1192 p_form_left_prompt => l_flex_seg_error_prompt,
1193 p_table_name => l_table_name
1194 );
1195 -- raise error
1196 hr_utility.set_message(800,'HR_52763_INV_FORMAT');
1197 hr_utility.set_message_token('VALUE_IN',l_id_in);
1198 hr_utility.set_message_token('VALUE_OUT',l_id_out);
1199 hr_utility.set_message_token('SEGMENT',l_seg_column_name);
1200 hr_multi_message.add
1201 (p_associated_column1 => l_table_name || '.' || l_seg_column_name);
1202 l_error := true;
1203 end if;
1204 end if;
1205 -- end fix;
1206 */
1207 --
1208 -- Populate the segment TOR
1209 IF not l_error THEN
1210 l_segment_set(l_seg_tor_cnt).col1 := l_seg_column_name;
1211 l_seg_tor_cnt := l_seg_tor_cnt + 1;
1212 ELSE
1213 l_error := false;
1214 END IF;
1215 end if;
1216 --
1217 end loop;
1218 if g_debug then
1219 hr_utility.set_location(l_proc, 80);
1220 end if;
1221 --
1222 -- Get Non Enabled attribute names
1223 --
1224 GetNonExistRows
1225 (p_plsqltor1 => l_attr_set
1226 ,p_plsqltor2 => l_segment_set
1227 --
1228 ,p_ne_tor_rws => l_ne_attr_set
1229 );
1230 if g_debug then
1231 hr_utility.set_location(l_proc, 90);
1232 end if;
1233 --
1234 -- Check if non enabled attributes have been provided
1235 --
1236 if l_ne_attr_set.count > 0 then
1237 for x in l_ne_attr_set.first..l_ne_attr_set.last loop
1238 --
1239 -- Set the non enabled column name
1240 --
1241 l_ne_column_name := l_ne_attr_set(x).col1;
1242 l_ne_column_value := l_ne_attr_set(x).col2;
1243 --
1244 -- Check if the value is set for the non enabled column
1245 --
1246 if l_ne_column_value is not null then
1247 --
1248 -- Raise the AOL error
1249 --
1250 if g_debug then
1251 hr_utility.set_location(l_proc, 100);
1252 end if;
1253 --
1254 hr_utility.set_message(800, 'HR_52994_NON_EXIST_SEG_NAME');
1255 hr_utility.set_message_token('SEGMENT',l_ne_column_name);
1256 hr_utility.set_message_token('VALUE',l_ne_column_value);
1257 hr_multi_message.add
1258 (p_associated_column1 => l_table_name || '.' || l_ne_column_name);
1259 --
1260 end if;
1261 --
1262 end loop;
1263 end if;
1264 end if;
1265 end if; -- end if descflex code exists in ignore varray
1266 --
1267 exception
1268 when app_exception.application_exception then
1269 if hr_multi_message.exception_add then
1270 hr_utility.set_location('Leaving '||l_proc, 110);
1271 raise;
1272 end if;
1273 hr_utility.set_location('Leaving '||l_proc, 120);
1274 end ins_or_upd_descflex_attribs;
1275 --
1276 end hr_dflex_utility;
[Home] [Help]