[Home] [Help]
PACKAGE BODY: APPS.HXC_LOAD_PKG
Source
1 PACKAGE BODY hxc_load_pkg AS
2 /* $Header: hxcload.pkb 120.3.12020000.2 2012/12/10 10:26:21 jnerella ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 FUNCTION get_pref_def_name ( p_pref_definition_id NUMBER ) RETURN VARCHAR2 IS
7
8 l_name fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE;
9
10 CURSOR csr_get_name IS
11 SELECT df.descriptive_flex_context_name
12 FROM fnd_descr_flex_contexts_vl df
13 , hxc_pref_definitions pd
14 WHERE pd.pref_definition_id = p_pref_definition_id
15 AND df.application_id =809
16 AND df.descriptive_flexfield_name = 'OTC PREFERENCES'
17 AND df.descriptive_flex_context_code = pd.code;
18
19 BEGIN
20
21 OPEN csr_get_name;
22 FETCH csr_get_name INTO l_name;
23 CLOSE csr_get_name;
24
25 RETURN l_name;
26
27 END get_pref_def_name;
28
29
30 FUNCTION get_attribute ( p_attribute_category VARCHAR2
31 , p_attribute VARCHAR2
32 , p_attribute_name IN VARCHAR2 DEFAULT null
33 ) RETURN VARCHAR2 IS
34
35 l_real_value VARCHAR2(150);
36
37 CURSOR csr_get_retrieval_rule IS
38 SELECT name
39 FROM hxc_retrieval_rules
40 WHERE RETRIEVAL_RULE_ID = TO_NUMBER(p_attribute);
41
42 CURSOR csr_get_approval_style IS
43 SELECT name
44 FROM hxc_approval_styles
45 WHERE approval_style_id = TO_NUMBER(p_attribute);
46
47 CURSOR csr_get_application_set IS
48 SELECT aps.application_set_name
49 FROM (SELECT heg.rowid row_id ,heg.ENTITY_GROUP_ID application_set_id ,heg.NAME application_set_name ,heg.OBJECT_VERSION_NUMBER ,heg.CREATED_BY ,
50 heg.CREATION_DATE ,heg.LAST_UPDATED_BY ,heg.LAST_UPDATE_DATE ,heg.LAST_UPDATE_LOGIN
51 FROM hxc_entity_groups heg
52 WHERE heg.entity_type = 'TIME_RECIPIENTS') APS
53 WHERE aps.application_set_id = TO_NUMBER(p_attribute);
54
55 CURSOR csr_get_retrieval_rule_grp IS
56 SELECT ret.retrieval_rule_group_name
57 FROM (SELECT heg.rowid row_id ,heg.ENTITY_GROUP_ID retrieval_rule_group_id ,heg.NAME retrieval_rule_group_name ,heg.OBJECT_VERSION_NUMBER ,heg.CREATED_BY ,heg.CREATION_DATE ,
58 heg.LAST_UPDATED_BY ,heg.LAST_UPDATE_DATE ,heg.LAST_UPDATE_LOGIN
59 FROM hxc_entity_groups heg
60 WHERE heg.entity_type = 'RETRIEVAL_RULES') RET
61 WHERE ret.retrieval_rule_group_id = TO_NUMBER(p_attribute);
62
63 CURSOR csr_get_public_template_group IS
64 select heg.name FROM hxc_entity_groups heg where
65 heg.entity_type = 'PUBLIC_TEMPLATE_GROUP' and
66 heg.ENTITY_GROUP_ID= TO_NUMBER(p_attribute);
67
68 CURSOR csr_get_alias IS
69 SELECT alias_definition_name
70 FROM hxc_alias_definitions
71 WHERE alias_definition_id = TO_NUMBER(p_attribute);
72
73 CURSOR csr_get_recurring_period IS
74 SELECT rp.name
75 FROM hxc_recurring_periods rp
76 WHERE recurring_period_id = TO_NUMBER(p_attribute);
77
78 CURSOR csr_get_approval_period IS
79 SELECT aps.name
80 FROM hxc_approval_period_sets aps
81 WHERE approval_period_set_id = TO_NUMBER(p_attribute);
82
83 CURSOR csr_get_layout IS
84 SELECT layout_name
85 FROM hxc_layouts
86 WHERE layout_id = TO_NUMBER(p_attribute);
87
88 CURSOR csr_get_date_format IS
89 SELECT egc.attribute2
90 FROM hxc_entity_group_comps egc
91 WHERE egc.attribute1 = p_attribute
92 AND EXISTS ( SELECT 'x'
93 FROM hxc_entity_groups eg
94 WHERE eg.entity_group_id = egc.entity_group_id
95 AND eg.entity_type = 'HXC_SS_TC_DATE_FORMATS' );
96
97 CURSOR
98 csr_get_time_entry_rule_name IS
99 SELECT ter.time_entry_rule_group_name
100 FROM (SELECT heg.rowid row_id ,heg.ENTITY_GROUP_ID time_entry_rule_group_id ,heg.NAME time_entry_rule_Group_name ,
101 heg.OBJECT_VERSION_NUMBER ,heg.CREATED_BY ,heg.CREATION_DATE ,heg.LAST_UPDATED_BY ,heg.LAST_UPDATE_DATE ,heg.LAST_UPDATE_LOGIN
102 FROM hxc_entity_groups heg
103 WHERE heg.entity_type = 'TIME_ENTRY_RULES') TER
104 WHERE ter.time_entry_rule_group_id = TO_NUMBER(p_attribute);
105
106 BEGIN
107
108 IF ( p_attribute_category = 'TS_PER_APPROVAL_STYLE' )
109 THEN
110
111 OPEN csr_get_approval_style;
112 FETCH csr_get_approval_style INTO l_real_Value;
113 CLOSE csr_get_approval_style;
114
115 ELSIF ( p_attribute_category = 'TS_PER_APPLICATION_SET' )
116 THEN
117
118 OPEN csr_get_application_set;
119 FETCH csr_get_application_set INTO l_real_Value;
120 CLOSE csr_get_application_set;
121
122 ELSIF ( p_attribute_category = 'TS_PER_RETRIEVAL_RULES' )
123 THEN
124
125 OPEN csr_get_retrieval_rule_grp;
126 FETCH csr_get_retrieval_rule_grp INTO l_real_Value;
127 CLOSE csr_get_retrieval_rule_grp;
128
129 ELSIF ( p_attribute_category = 'TS_PER_APPROVAL_PERIODS' )
130 THEN
131
132 OPEN csr_get_approval_period;
133 FETCH csr_get_approval_period INTO l_real_Value;
134 CLOSE csr_get_approval_period;
135
136 ELSIF ( p_attribute_category = 'TC_W_TCRD_PERIOD' )
137 THEN
138
139 OPEN csr_get_recurring_period;
140 FETCH csr_get_recurring_period INTO l_real_value;
141 CLOSE csr_get_recurring_period;
142
143 ELSIF ( p_attribute_category = 'TC_W_TCRD_ALIASES' )
144 THEN
145
146 -- GPM: 115.9
147 -- commented in cursor now that we have alias preference.
148
149 OPEN csr_get_alias;
150 FETCH csr_get_alias INTO l_real_value;
151 CLOSE csr_get_alias;
152
153 ELSIF ( p_attribute_category = 'TC_W_TCRD_LAYOUT' )
154 THEN
155
156 OPEN csr_get_layout;
157 FETCH csr_get_layout INTO l_real_value;
158 CLOSE csr_get_layout;
159 ELSIF ( p_attribute_category = 'TC_W_PUBLIC_TEMPLATE' )
160 THEN
161
162 OPEN csr_get_public_template_group;
163 FETCH csr_get_public_template_group INTO l_real_value;
164 CLOSE csr_get_public_template_group;
165
166 ELSIF ( p_attribute_category = 'TC_W_DATE_FORMATS' )
167 THEN
168
169 OPEN csr_get_date_format;
170 FETCH csr_get_date_format INTO l_real_value;
171 CLOSE csr_get_date_format;
172
173 ELSIF ( p_attribute_category = 'TS_PER_AUDIT_REQUIREMENTS' )
174 THEN
175 OPEN csr_get_time_entry_rule_name;
176 FETCH csr_get_time_entry_rule_name INTO l_real_value;
177 CLOSE csr_get_time_entry_rule_name;
178 ELSIF ( p_attribute_category = 'TC_W_RULES_EVALUATION' ) --115.3
179 THEN
180 IF (p_attribute_name = 'ATTRIBUTE2') then
181 OPEN csr_get_retrieval_rule;
182 FETCH csr_get_retrieval_rule INTO l_real_value;
183 CLOSE csr_get_retrieval_rule;
184 elsif (p_attribute_name = 'ATTRIBUTE3') then
185 OPEN csr_get_recurring_period;
186 FETCH csr_get_recurring_period INTO l_real_value;
187 CLOSE csr_get_recurring_period;
188 end if;
189 ELSE
190
191 l_real_value := p_attribute;
192
193 END IF;
194
195 RETURN l_real_value;
196
197 END get_attribute;
198
199 -- ----------------------------------------------------------------------------
200 -- |----------------------------< get_value_set_sql >-------------------------|
201 -- ----------------------------------------------------------------------------
202 --
203 -- public function
204 -- get_value_set_sql
205 --
206 -- description
207 -- get the SQL associated with a particular value set
208
209
210 FUNCTION get_value_set_sql
211 (p_flex_value_set_id IN NUMBER,
212 p_session_date IN DATE ) RETURN LONG
213 is
214 --
215 -- Declare local variables
216 --
217 l_sql_text LONG;
218 l_sql_text_id LONG;
219 l_valueset_r fnd_vset.valueset_r;
220 l_valueset_dr fnd_vset.valueset_dr;
221 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
222 l_order_by_start NUMBER;
223 l_from_start NUMBER;
224 l_additional_and_clause VARCHAR2(2000);
225 l_from_where VARCHAR2(2000);
226 l_select_clause VARCHAR2(2000);
227 l_dep_parent_column_name fnd_columns.column_name%TYPE;
228 --
229 begin -- get_value_set_sql
230
231 l_value_set_id := p_flex_value_set_id;
232
233 fnd_vset.get_valueset(l_value_set_id,l_valueset_r,l_valueset_dr);
234
235 --
236 -- Initailize the SQL text columns.
237 --
238 l_sql_text := '';
239 l_sql_text_id := '';
240 --
241 -- Ok next build the SQL text that can be used to build a pop-list
242 -- for this segment, if this is a table validated or independant
243 -- validated value set - i.e. it has an associated list of values.
244 -- We are going to build two versions of the SQL. One can be used
245 -- to define the list of values associated with this segment(SQL_TEXT), the
246 -- other is used to converted a value (ID) stored on the database into a
247 -- a description (VALUE) (SQL_DESCR_TXT).
248 --
249 IF l_valueset_r.validation_type = 'F'
250 THEN
251 -- TABLE validated
252
253 select 'SELECT ' ||
254 l_valueset_r.table_info.value_column_name ||
255 decode(l_valueset_r.table_info.meaning_column_name,null,',NULL ',
256 ','||l_valueset_r.table_info.meaning_column_name)||
257 decode(l_valueset_r.table_info.id_column_name,null,',NULL ',
258 ','||l_valueset_r.table_info.id_column_name)||
259 ' FROM ' ||
260 l_valueset_r.table_info.table_name || ' ' ||
261 l_valueset_r.table_info.where_clause
262 into l_sql_text
263 from dual;
264
265 l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
266 l_from_start := INSTR(upper(l_sql_text),'FROM');
267
268 -- Build the SQL for the FROM clause
269
270 if(l_order_by_start >0) then
271 l_from_where := substr(l_sql_text,l_from_start,(
272 l_order_by_start-l_from_start));
273 else
274 l_from_where := substr(l_sql_text,l_from_start);
275 end if;
276 --
277
278 if(l_valueset_r.table_info.meaning_column_name is not null) then
279 l_select_clause := 'SELECT '||l_valueset_r.table_info.
280 meaning_column_name||' ';
281 else
282 l_select_clause := 'SELECT '||l_valueset_r.table_info.
283 value_column_name||' ';
284 end if;
285
286 l_sql_text_id := l_select_clause||l_from_where;
287
288 IF ( INSTR( UPPER(l_sql_text_id) , 'WHERE') = 0 )
289 THEN
290
291 l_sql_text_id := l_select_clause||l_from_where ||'WHERE '||l_valueset_r.table_info.id_column_name||' = ';
292
293 ELSE
294
295 l_sql_text_id := l_select_clause||l_from_where ||'and '||l_valueset_r.table_info.id_column_name||' = ';
296
297 END IF;
298
299
300 elsif l_valueset_r.validation_type = 'I' then
301
302 --
303 -- We can hard code the DESC SQL this time, since we know explicitly
304 -- how independant value sets are built. This should be changed once
305 -- we have the procedure from AOL.
306 --
307 l_sql_text_id := 'SELECT FLEX_VALUE'||
308 ' FROM FND_FLEX_VALUES_VL'||
309 ' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
310 ' AND ENABLED_FLAG = ''Y'''||
311 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
312 ' NVL(START_DATE_ACTIVE,'''||
313 P_SESSION_DATE||''')'||
314 ' AND NVL(END_DATE_ACTIVE,'''||
315 P_SESSION_DATE||''')'||
316 ' AND FLEX_VALUE = ';
317
318
319 end if; -- validation type
320
321 RETURN l_sql_text_id;
322
323 end get_value_set_sql;
324
325 -- ----------------------------------------------------------------------------
326 -- |----------------------------< get_parent >-------------------------|
327 -- ----------------------------------------------------------------------------
328 FUNCTION get_parent ( p_pref_top_node VARCHAR2
329 , p_pref_node VARCHAR2
330 , p_pref_level NUMBER
331 , p_count NUMBER ) RETURN VARCHAR2 IS
332
333 l_level_one hxc_pref_hierarchies.name%TYPE := NULL;
334 l_level_two hxc_pref_hierarchies.name%TYPE := NULL;
335 l_level_three hxc_pref_hierarchies.name%TYPE := NULL;
336 l_level_four hxc_pref_hierarchies.name%TYPE := NULL;
337 l_level_five hxc_pref_hierarchies.name%TYPE := NULL;
338
339 l_full_name VARCHAR2(500);
340
341 l_last_level NUMBER(1) := 1;
342
343 CURSOR get_pref_hierarchy ( p_top_node VARCHAR2 ) IS
344 SELECT name
345 , level
346 , rownum cnt
347 from hxc_pref_hierarchies
348 start with name = p_top_node
349 connect by prior pref_hierarchy_id = parent_pref_hierarchy_id;
350
351 BEGIN
352
353 FOR t IN get_pref_hierarchy ( p_pref_top_node )
354 LOOP
355
356 IF ( l_last_level > t.level )
357 THEN
358 IF ( t.level = 2 )
359 THEN
360 l_level_two := NULL;
361 l_level_three := NULL;
362 l_level_four := NULL;
363 l_level_five := NULL;
364
365 ELSIF ( t.level = 3 )
366 THEN
367 l_level_three := NULL;
368 l_level_four := NULL;
369 l_level_five := NULL;
370
371 ELSIF ( t.level = 4 )
372 THEN
373 l_level_four := NULL;
374 l_level_five := NULL;
375 END IF;
376 END IF;
377
378
379 IF ( p_pref_level = t.level AND p_pref_node = t.name AND p_count = t.cnt )
380 THEN
381
382 l_full_name := l_level_one||l_level_two||l_level_three||l_level_four||l_level_five;
383
384 RETURN LTRIM(RTRIM(l_full_name));
385 END IF;
386
387 IF ( t.level = 1 AND t.level <> p_pref_level )
388 THEN
389 l_level_one := t.name;
390
391 ELSIF ( t.level = 2 AND t.level <> p_pref_level )
392 THEN
393 l_level_two := '.'||t.name;
394
395 ELSIF ( t.level = 3 AND t.level <> p_pref_level )
396 THEN
397 l_level_three := '.'||t.name;
398
399 ELSIF ( t.level = 4 AND t.level <> p_pref_level )
400 THEN
401 l_level_four := '.'||t.name;
402
403 ELSIF ( t.level = 5 AND t.level <> p_pref_level )
404 THEN
405 l_level_five := '.'||t.name;
406
407 END IF;
408
409 l_last_level := t.level;
410
411 END LOOP;
412
413 END get_parent;
414
415 -- ----------------------------------------------------------------------------
416 -- |----------------------------< get_flex_value >-------------------------|
417 -- ----------------------------------------------------------------------------
418
419 FUNCTION get_flex_value ( p_flex_value_set_id NUMBER
420 , p_id VARCHAR2 ) RETURN VARCHAR2 IS
421
422 l_sql LONG;
423 l_description VARCHAR2(150) := NULL;
424
425 -- GPM v115.26
426
427 CURSOR csr_get_element_name ( p_element_type_id VARCHAR2 ) IS
428 select pett.element_name Display_Value
429 from pay_element_types_f_tl pett
430 where pett.element_type_id = p_element_type_id
431 and pett.language = USERENV('LANG');
432
433 l_csr INTEGER;
434
435 BEGIN
436
437 IF ( p_flex_value_set_id = -1 )
438 THEN
439
440 -- no value set therefore at the moment is 'Dummy Element Context'
441
442 OPEN csr_get_element_name ( p_id );
443 FETCH csr_get_element_name INTO l_description;
444 CLOSE csr_get_element_name;
445
446 ELSIF ( p_flex_value_set_id = -2 )
447 THEN
448
449 -- no value set at all -free form text Valeu = Value_Id
450
451 l_description := p_id;
452
453 ELSE
454
455 l_sql := get_value_set_sql (
456 p_flex_value_set_id => p_flex_value_set_id
457 , p_session_date => sysdate );
458
459
460 BEGIN
461
462 execute immediate l_sql||''''||p_id||'''' INTO l_description;
463
464 EXCEPTION WHEN OTHERS THEN
465
466 -- GPM v115.12 WWB 3254482
467 -- for customers who modify the value sets
468 -- which allow duplicate entries !!!
469
470 IF SQLCODE = -1422 -- exact fetch returns more then one row
471 THEN
472 null;
473 ELSE
474 raise;
475 END IF;
476 END;
477
478 END IF;
479
480 RETURN l_description;
481
482 END get_flex_value;
483
484
485 -- ----------------------------------------------------------------------------
486 -- |----------------------------< upgrade_custom_tcs>-------------------------|
487 -- ----------------------------------------------------------------------------
488 PROCEDURE upgrade_custom_tcs ( p_time_category_id NUMBER ) IS
489
490 BEGIN
491
492 null;
493
494 END upgrade_custom_tcs;
495
496 -- ----------------------------------------------------------------------------
497 -- |--------------------------< chk_tc_ref_integrity>-------------------------|
498 -- ----------------------------------------------------------------------------
499
500 FUNCTION chk_tc_ref_integrity ( p_time_category_id NUMBER ) RETURN BOOLEAN IS
501
502 l_referenced BOOLEAN := TRUE;
503
504 l_exists r_ter_record;
505
506 BEGIN
507
508 OPEN csr_chk_ref_integ ( p_time_category_id );
509 FETCH csr_chk_ref_integ INTO l_exists;
510
511 IF ( csr_chk_ref_integ%FOUND )
512 THEN
513
514 l_referenced := FALSE;
515
516 END IF;
517
518 CLOSE csr_chk_ref_integ;
519
520 RETURN l_referenced;
521
522 END chk_tc_ref_integrity;
523
524 -- ----------------------------------------------------------------------------
525 -- |------------------< get_tc_ref_integrity_list >---------------------------|
526 -- ----------------------------------------------------------------------------
527
528 -- Description:
529
530 -- SEE DESCRIPTION IN PACKAGE HEADER
531
532 -- ----------------------------------------------------------------------------
533
534 FUNCTION get_tc_ref_integrity_list ( p_time_category_id NUMBER ) RETURN t_ter_table IS
535
536 l_ter_list t_ter_table;
537 l_index BINARY_INTEGER := 1;
538
539 BEGIN
540
541 OPEN csr_chk_ref_integ ( p_time_category_id );
542 FETCH csr_chk_ref_integ INTO l_ter_list(l_index);
543
544 IF ( csr_chk_ref_integ%FOUND )
545 THEN
546
547 l_index := l_index + 1;
548
549 FETCH csr_chk_ref_integ INTO l_ter_list(l_index);
550
551 END IF;
552
553 CLOSE csr_chk_ref_integ;
554
555 RETURN l_ter_list;
556
557 END get_tc_ref_integrity_list;
558
559 -- ----------------------------------------------------------------------------
560 -- |--------------------------< get_node_data >-------------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure get_node_data
563 (
564 p_preference_full_name in varchar2
565 ,p_name in varchar2
566 ,p_business_group_id in number
567 ,p_legislation_code in varchar2
568 ,p_mode out nocopy varchar2
569 ,p_pref_hierarchy_id out nocopy number
570 ,p_parent_pref_hierarchy_id out nocopy number
571 ,p_object_version_number out nocopy number
572 ) IS
573 --
574
575
576 l_period number;
577 l_next_period number;
578 l_name varchar2(80);
579
580 l_parent_pref_hierarchy_id number := null;
581 l_pref_hierarchy_id number := null;
582 l_object_version_number number := null;
583 l_mode varchar2(50) := null;
584
585 cursor c_top_node(l_name varchar2) is
586 SELECT pref_hierarchy_id,object_version_number
587 FROM hxc_pref_hierarchies
588 WHERE parent_pref_hierarchy_id is null
589 AND name = l_name;
590
591 cursor c_child_nodes(l_parent_pref_hierarchy_id number,l_name varchar2) is
592 SELECT pref_hierarchy_id,object_version_number
593 FROM hxc_pref_hierarchies
594 WHERE parent_pref_hierarchy_id = l_parent_pref_hierarchy_id
595 AND name = l_name;
596
597 --
598 begin
599 --
600 if p_preference_full_name is not null then
601
602
603
604 -- Consider preference_full_name A.B.C being passed.In this case the ID of node
605 -- C needs to be calculated.
606 -- Loop till the instr function,which gives the position of the next period in
607 -- the string,returns 0 implying that the end of the string is reached.
608
609 l_period := 0;
610
611 -- This loop gives the parent_pref_hierarchy_id for new node to be created
612
613 WHILE l_period <> (length(p_preference_full_name) + 1) LOOP
614
615 -- find the position of the delimiter
616
617 l_next_period := instr(p_preference_full_name,'.',l_period + 1,1);
618
619 -- if l_next_period is 0,i.e.,another delimiter could not be found,implies
620 -- that end of the sring is reached.
621
622 if (l_next_period = 0) then
623 l_next_period := length(p_preference_full_name) + 1;
624 end if;
625
626 -- get the name of the preference(i.e., the text between the two delimiters)
627
628 l_name := substr(p_preference_full_name,l_period + 1,l_next_period
629 - (l_period + 1));
630
631 -- get the id of the preference with this name(l_name)
632
633 if (l_parent_pref_hierarchy_id is null) then
634
635 open c_top_node(l_name);
636 fetch c_top_node into l_parent_pref_hierarchy_id,l_object_version_number;
637 close c_top_node;
638
639 else
640
641 open c_child_nodes(l_parent_pref_hierarchy_id,l_name);
642 fetch c_child_nodes into l_parent_pref_hierarchy_id,l_object_version_number;
643 close c_child_nodes;
644
645 end if;
646
647 l_period := l_next_period;
648
649 end loop;
650
651 open c_child_nodes(l_parent_pref_hierarchy_id,p_name);
652 fetch c_child_nodes into l_pref_hierarchy_id,l_object_version_number;
653
654 -- set the OUT parameter
655 if c_child_nodes%found then
656 l_mode := 'UPDATE';
657 else
658 l_mode := 'INSERT';
659 l_pref_hierarchy_id := null;
660 l_object_version_number := null;
661 end if;
662 close c_child_nodes;
663
664 elsif p_preference_full_name is null then
665
666 l_name := p_name;
667 open c_top_node(l_name);
668 fetch c_top_node into l_pref_hierarchy_id,l_object_version_number;
669
670 if l_pref_hierarchy_id is null then
671 l_mode := 'INSERT';
672 else
673 l_mode := 'UPDATE';
674 end if;
675 close c_top_node;
676
677 end if;
678
679 p_mode := l_mode;
680 p_pref_hierarchy_id := l_pref_hierarchy_id;
681 p_object_version_number := l_object_version_number;
682 p_parent_pref_hierarchy_id := l_parent_pref_hierarchy_id;
683
684
685
686 end get_node_data;
687 --
688
689 -- ----------------------------------------------------------------------------
690 -- |--------------------------< get_ter_attributes >-------------------------|
691 -- ----------------------------------------------------------------------------
692
693 FUNCTION get_ter_attributes(p_formula_id IN NUMBER,
694 p_attribute_name IN VARCHAR2,
695 p_attrubute_val IN VARCHAR2
696 )
697 RETURN VARCHAR2 IS
698
699 cursor c_formula is
700 select formula_name
701 from ff_formulas_f
702 where formula_id=p_formula_id;
703
704 cursor c_flex_attribute(p_formula_name VARCHAR2) is
705 select FLEX_VALUE_SET_ID
706 from fnd_descr_flex_col_usage_vl
707 where descriptive_flexfield_name = 'OTL Formulas'
708 and application_id = 809
709 and descriptive_flex_context_code =p_formula_name
710 and enabled_flag = 'Y'
711 and APPLICATION_COLUMN_NAME =p_attribute_name;
712
713 l_formula_name VARCHAR2(80);
714 l_vset_id NUMBER;
715 l_vset_sql LONG;
716
717 l_return_value VARCHAR2(250);
718
719 BEGIN
720
721 open c_formula;
722 fetch c_formula into l_formula_name;
723 close c_formula;
724
725 IF l_formula_name is null then
726 -- no formula so we can return the same id
727 RETURN p_attrubute_val;
728 ELSE
729
730 open c_flex_attribute(l_formula_name);
731 fetch c_flex_attribute into l_vset_id;
732 close c_flex_attribute;
733
734 IF l_vset_id is null then
735 RETURN p_attrubute_val;
736 ELSE
737
738 l_vset_sql :=get_value_set_sql(l_vset_id,sysdate);
739
740 IF l_vset_sql is null then
741 RETURN p_attrubute_val;
742 ELSE
743
744 l_vset_sql :=l_vset_sql ||':1';
745
746 EXECUTE IMMEDIATE l_vset_sql into l_return_value using p_attrubute_val;
747
748 RETURN l_return_value;
749 END IF;
750
751 END IF;
752
753 END IF;
754
755 EXCEPTION
756
757 when others then
758 return p_attrubute_val;
759 END;
760
761 -- ----------------------------------------------------------------------------
762 -- |--------------------------< get_id_set_sql >-------------------------|
763 -- ----------------------------------------------------------------------------
764
765 FUNCTION get_id_set_sql
766 (p_flex_value_set_id IN NUMBER,
767 p_session_date IN DATE ) RETURN LONG
768 is
769 --
770 -- Declare local variables
771 --
772 l_sql_text LONG;
773 l_sql_text_id LONG;
774 l_valueset_r fnd_vset.valueset_r;
775 l_valueset_dr fnd_vset.valueset_dr;
776 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
777 l_order_by_start NUMBER;
778 l_from_start NUMBER;
779 l_additional_and_clause VARCHAR2(2000);
780 l_from_where VARCHAR2(2000);
781 l_select_clause VARCHAR2(2000);
782 l_dep_parent_column_name fnd_columns.column_name%TYPE;
783 --
784 begin -- get_value_set_sql
785
786 l_value_set_id := p_flex_value_set_id;
787
788 fnd_vset.get_valueset(l_value_set_id,l_valueset_r,l_valueset_dr);
789
790 --
791 -- Initailize the SQL text columns.
792 --
793 l_sql_text := '';
794 l_sql_text_id := '';
795 --
796 -- Ok next build the SQL text that can be used to build a pop-list
797 -- for this segment, if this is a table validated or independant
798 -- validated value set - i.e. it has an associated list of values.
799 -- We are going to build two versions of the SQL. One can be used
800 -- to define the list of values associated with this segment(SQL_TEXT), the
801 -- other is used to converted a value (ID) stored on the database into a
802 -- a description (VALUE) (SQL_DESCR_TXT).
803 --
804 IF l_valueset_r.validation_type = 'F'
805 THEN
806 -- TABLE validated
807
808 select 'SELECT ' ||
809 l_valueset_r.table_info.value_column_name ||
810 decode(l_valueset_r.table_info.meaning_column_name,null,',NULL ',
811 ','||l_valueset_r.table_info.meaning_column_name)||
812 decode(l_valueset_r.table_info.id_column_name,null,',NULL ',
813 ','||l_valueset_r.table_info.id_column_name)||
814 ' FROM ' ||
815 l_valueset_r.table_info.table_name || ' ' ||
816 l_valueset_r.table_info.where_clause
817 into l_sql_text
818 from dual;
819
820 l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
821 l_from_start := INSTR(upper(l_sql_text),'FROM');
822
823 -- Build the SQL for the FROM clause
824
825 if(l_order_by_start >0) then
826 l_from_where := substr(l_sql_text,l_from_start,(
827 l_order_by_start-l_from_start));
828 else
829 l_from_where := substr(l_sql_text,l_from_start);
830 end if;
831 --
832
833 l_select_clause := 'SELECT '||l_valueset_r.table_info.
834 id_column_name||' ';
835
836 /*
837 if(l_valueset_r.table_info.meaning_column_name is not null) then
838 l_select_clause := 'SELECT '||l_valueset_r.table_info.
839 meaning_column_name||' ';
840 else
841 l_select_clause := 'SELECT '||l_valueset_r.table_info.
842 value_column_name||' ';
843 end if;
844 */
845 l_sql_text_id := l_select_clause||l_from_where;
846
847
848 IF ( INSTR( UPPER(l_sql_text_id) , 'WHERE') = 0 )
849 THEN
850
851 l_sql_text_id := l_select_clause||l_from_where ||'WHERE '||l_valueset_r.table_info.value_column_name||' = ';
852
853 ELSE
854
855 l_sql_text_id := l_select_clause||l_from_where ||'and '||l_valueset_r.table_info.value_column_name||' = ';
856
857 END IF;
858
859
860 elsif l_valueset_r.validation_type = 'I' then
861
862 --
863 -- We can hard code the DESC SQL this time, since we know explicitly
864 -- how independant value sets are built. This should be changed once
865 -- we have the procedure from AOL.
866 --
867 l_sql_text_id := 'SELECT FLEX_VALUE'||
868 ' FROM FND_FLEX_VALUES_VL'||
869 ' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
870 ' AND ENABLED_FLAG = ''Y'''||
871 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
872 ' NVL(START_DATE_ACTIVE,'''||
873 P_SESSION_DATE||''')'||
874 ' AND NVL(END_DATE_ACTIVE,'''||
875 P_SESSION_DATE||''')'||
876 ' AND FLEX_VALUE = ';
877
878 end if; -- validation type
879
880 RETURN l_sql_text_id;
881
882 end get_id_set_sql;
883
884 -- ----------------------------------------------------------------------------
885 -- |--------------------------< get_ter_attribute_id >-------------------------|
886 -- ----------------------------------------------------------------------------
887
888 FUNCTION get_ter_attribute_id(p_formula_name IN VARCHAR2,
889 p_attribute_name IN VARCHAR2,
890 p_attrubute_val IN VARCHAR2
891 )
892 RETURN VARCHAR2 IS
893
894 cursor c_flex_attribute(p_formula_name VARCHAR2) is
895 select FLEX_VALUE_SET_ID
896 from fnd_descr_flex_col_usage_vl
897 where descriptive_flexfield_name = 'OTL Formulas'
898 and application_id = 809
899 and descriptive_flex_context_code =p_formula_name
900 and enabled_flag = 'Y'
901 and APPLICATION_COLUMN_NAME =p_attribute_name;
902
903 l_formula_name VARCHAR2(80);
904 l_vset_id NUMBER;
905 l_vset_sql LONG;
906
907 l_return_value VARCHAR2(250);
908
909 BEGIN
910
911 IF p_formula_name is null then
912 -- no formula so we can return the same id
913 RETURN p_attrubute_val;
914 ELSE
915
916 open c_flex_attribute(p_formula_name);
917 fetch c_flex_attribute into l_vset_id;
918 close c_flex_attribute;
919
920 IF l_vset_id is null then
921 RETURN p_attrubute_val;
922 ELSE
923
924 l_vset_sql :=get_id_set_sql(l_vset_id,sysdate);
925
926 IF l_vset_sql is null then
927 RETURN p_attrubute_val;
928 ELSE
929
930 l_vset_sql :=l_vset_sql ||':1';
931
932 EXECUTE IMMEDIATE l_vset_sql into l_return_value using p_attrubute_val;
933
934 RETURN l_return_value;
935 END IF;
936
937 END IF;
938
939 END IF;
940
941 EXCEPTION
942
943 when others then
944 return p_attrubute_val;
945 END;
946
947 -- ----------------------------------------------------------------------------
948 -- |--------------------------< set_dynamic_sql_string >-------------------------|
949 -- ----------------------------------------------------------------------------
950
951 procedure set_dynamic_sql_string ( p_time_category_id NUMBER ) IS
952
953 CURSOR csr_get_operator ( p_time_category_id NUMBER ) IS
954 SELECT operator
955 FROM hxc_time_categories
956 WHERE time_category_id = p_time_category_id;
957
958
959 CURSOR csr_get_category_comps ( p_time_category_id NUMBER ) IS
960 SELECT
961 bbit.bld_blk_info_type context
962 , bbit.bld_blk_info_type_id
963 , mpc.segment
964 , NVL(tcc.value_id, DECODE(tcc.is_null, 'N', '<WILDCARD>', '<IS NULL>')) value_id
965 , tcc.ref_time_category_id
966 , tcc.flex_value_set_id
967 , tcc.equal_to
968 FROM
969 hxc_bld_blk_info_types bbit
970 , hxc_mapping_components mpc
971 , hxc_time_category_comps tcc
972 WHERE tcc.time_category_id = p_time_category_id AND
973 tcc.type = 'MC'
974 AND
975 mpc.mapping_component_id (+) = tcc.component_type_id
976 AND
977 bbit.bld_blk_info_type_id (+) = mpc.bld_blk_info_type_id;
978
979
980 l_time_sql CLOB; -- Bug 15977687
981 l_time_category_id hxc_time_Categories.time_category_id%TYPE;
982 l_operator hxc_time_categories.operator%TYPE;
983
984 l_last_update_date DATE;
985 l_first_time_round BOOLEAN;
986 l_comps_r csr_get_category_comps%ROWTYPE;
987
988
989 -- ----------------------------------------------------------------------------
990 -- |--------------------------< validate_time_category_sql >-------------------------|
991 -- ----------------------------------------------------------------------------
992
993 PROCEDURE validate_time_category_sql ( p_sql_string IN LONG ) IS
994
995 l_sql LONG := 'select distinct ta.bb_id from hxc_tmp_atts ta where '||p_sql_string;
996
997 t_bb_id dbms_sql.Number_Table;
998
999 l_csr INTEGER;
1000 l_rows_fetched INTEGER;
1001 l_dummy INTEGER;
1002
1003 BEGIN
1004
1005 -- the SQL MUST returns rows to show all possible errors
1006 -- particularly implicit character to number and vice
1007 -- versa
1008
1009 INSERT INTO hxc_tmp_atts (
1010 ta_id
1011 , bb_id
1012 , attribute1
1013 , attribute2
1014 , attribute3
1015 , attribute4
1016 , attribute5
1017 , attribute6
1018 , attribute7
1019 , attribute8
1020 , attribute9
1021 , attribute10
1022 , attribute11
1023 , attribute12
1024 , attribute13
1025 , attribute14
1026 , attribute15
1027 , attribute16
1028 , attribute17
1029 , attribute18
1030 , attribute19
1031 , attribute20
1032 , attribute21
1033 , attribute22
1034 , attribute23
1035 , attribute24
1036 , attribute25
1037 , attribute26
1038 , attribute27
1039 , attribute28
1040 , attribute29
1041 , attribute30
1042 , bld_blk_info_type_id
1043 , attribute_category )
1044 VALUES (
1045 1
1046 , 2
1047 , 'Dummy'
1048 , 'Dummy'
1049 , 'Dummy'
1050 , 'Dummy'
1051 , 'Dummy'
1052 , 'Dummy'
1053 , 'Dummy'
1054 , 'Dummy'
1055 , 'Dummy'
1056 , 'Dummy'
1057 , 'Dummy'
1058 , 'Dummy'
1059 , 'Dummy'
1060 , 'Dummy'
1061 , 'Dummy'
1062 , 'Dummy'
1063 , 'Dummy'
1064 , 'Dummy'
1065 , 'Dummy'
1066 , 'Dummy'
1067 , 'Dummy'
1068 , 'Dummy'
1069 , 'Dummy'
1070 , 'Dummy'
1071 , 'Dummy'
1072 , 'Dummy'
1073 , 'Dummy'
1074 , 'Dummy'
1075 , 'Dummy'
1076 , 'Dummy'
1077 , 1
1078 , 'Dummy' );
1079
1080
1081 BEGIN
1082
1083 l_rows_fetched := 100;
1084
1085 l_csr := dbms_sql.open_cursor;
1086
1087 dbms_sql.parse ( l_csr, l_sql, dbms_sql.native );
1088
1089 dbms_sql.define_array (
1090 c => l_csr
1091 , position => 1
1092 , n_tab => t_bb_id
1093 , cnt => l_rows_fetched
1094 , lower_bound => 1 );
1095
1096 l_dummy := dbms_sql.execute ( l_csr );
1097
1098 -- loop to ensure we fetch all the rows
1099
1100 WHILE ( l_rows_fetched = 100 )
1101 LOOP
1102
1103 l_rows_fetched := dbms_sql.fetch_rows ( l_csr );
1104
1105 IF ( l_rows_fetched > 0 )
1106 THEN
1107
1108 dbms_sql.column_value (
1109 c => l_csr
1110 , position => 1
1111 , n_tab => t_bb_id );
1112
1113 t_bb_id.DELETE;
1114
1115 END IF;
1116
1117 END LOOP;
1118
1119 dbms_sql.close_cursor ( l_csr );
1120
1121 -- execute immediate l_sql INTO l_dummy;
1122
1123 EXCEPTION WHEN NO_DATA_FOUND THEN
1124
1125 null;
1126
1127 WHEN OTHERS THEN
1128
1129 fnd_message.set_name('HXC', 'HXC_HTC_INVALID_SQL');
1130 fnd_message.set_token('ERROR', SQLERRM );
1131 fnd_message.raise_error;
1132
1133 END;
1134
1135 END validate_time_category_sql;
1136
1137
1138 -- ----------------------------------------------------------------------------
1139 -- |--------------------------< get_dyn_sql >-------------------------|
1140 -- ----------------------------------------------------------------------------
1141 PROCEDURE get_dyn_sql ( p_time_sql IN OUT NOCOPY CLOB -- Bug 15977687
1142 , p_comps_r IN csr_get_category_comps%ROWTYPE
1143 , p_operator IN VARCHAR2
1144 , p_an_sql IN BOOLEAN DEFAULT FALSE
1145 , p_vs_sql IN BOOLEAN DEFAULT FALSE ) IS
1146
1147 l_proc varchar2(72);
1148 l_dyn_sql CLOB; -- Bug 15977687
1149 l_ref_dyn_sql CLOB; -- Bug 15977687
1150
1151 l_value_string VARCHAR2(150);
1152 l_string_start VARCHAR2(30) := '( ta.bld_blk_info_type_id = ';
1153 l_string_and VARCHAR2(10) := ' AND ta.';
1154
1155
1156 BEGIN
1157
1158 IF ( g_debug ) THEN
1159 l_proc := g_package||'get_dyn_sql';
1160 hr_utility.trace('get dyn sql params are ....');
1161 hr_utility.trace('dyn sql is '||p_time_sql);
1162 END IF;
1163
1164 -- we want the dynamic sql string
1165
1166 l_dyn_sql := p_time_sql;
1167
1168 l_ref_dyn_sql := NULL;
1169
1170 IF ( p_comps_r.context = 'Dummy Element Context' AND p_comps_r.flex_value_set_id = -1 AND
1171 p_comps_r.value_id <> '<WILDCARD>' )
1172 THEN
1173 l_value_string := 'ELEMENT - '||p_comps_r.value_id;
1174 ELSE
1175 l_value_string := p_comps_r.value_id;
1176 END IF;
1177
1178
1179 IF ( l_first_time_round )
1180 THEN
1181
1182 -- set string for an sql
1183 IF ( p_an_sql )
1184 THEN
1185 l_string_and := ' AND ( ta.';
1186 END IF;
1187 IF ( p_comps_r.segment IS NOT NULL )
1188 THEN
1189 IF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1190 THEN
1191 IF ( p_an_sql )
1192 THEN
1193 l_dyn_sql := l_dyn_sql
1194 ||l_string_start||p_comps_r.bld_blk_info_type_id
1195 ||l_string_and ||p_comps_r.segment
1196 ||' IS NOT NULL ';
1197 ELSE
1198 l_dyn_sql := l_dyn_sql
1199 ||l_string_start||p_comps_r.bld_blk_info_type_id
1200 ||l_string_and ||p_comps_r.segment
1201 ||' IS NOT NULL )';
1202 END IF;
1203
1204 ELSIF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'N' ) )
1205 THEN
1206 IF ( g_debug ) THEN
1207 hr_utility.trace('GAZ - INVALID COMBO');
1208 END IF;
1209
1210 fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1211 fnd_message.raise_error;
1212
1213 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1214 THEN
1215 IF ( p_an_sql )
1216 THEN
1217 l_dyn_sql := l_dyn_sql
1218 ||l_string_start||p_comps_r.bld_blk_info_type_id
1219 ||l_string_and ||p_comps_r.segment
1220 ||' IS NULL ';
1221 ELSE
1222 l_dyn_sql := l_dyn_sql
1223 ||l_string_start||p_comps_r.bld_blk_info_type_id
1224 ||l_string_and ||p_comps_r.segment
1225 ||' IS NULL )';
1226 END IF;
1227
1228 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'N' ) )
1229 THEN
1230 IF ( p_an_sql )
1231 THEN
1232 l_dyn_sql := l_dyn_sql
1233 ||l_string_start||p_comps_r.bld_blk_info_type_id
1234 ||l_string_and ||p_comps_r.segment
1235 ||' IS NOT NULL ';
1236 ELSE
1237 l_dyn_sql := l_dyn_sql
1238 ||l_string_start||p_comps_r.bld_blk_info_type_id
1239 ||l_string_and ||p_comps_r.segment
1240 ||' IS NOT NULL )';
1241 END IF;
1242
1243 ELSIF ( p_comps_r.equal_to = 'Y' )
1244 THEN
1245 IF ( p_an_sql )
1246 THEN
1247 l_dyn_sql := l_dyn_sql
1248 ||l_string_start||p_comps_r.bld_blk_info_type_id
1249 ||l_string_and ||p_comps_r.segment
1250 ||' = '''||l_value_string||''' ';
1251 ELSIF ( p_vs_sql )
1252 THEN
1253 l_dyn_sql := l_dyn_sql
1254 ||l_string_start||p_comps_r.bld_blk_info_type_id
1255 ||l_string_and ||p_comps_r.segment
1256 ||' IN ( '||l_value_string||' ) ';
1257 ELSE
1258 l_dyn_sql := l_dyn_sql
1259 ||l_string_start||p_comps_r.bld_blk_info_type_id
1260 ||l_string_and ||p_comps_r.segment
1261 ||' = '''||l_value_string||''' )';
1262 END IF;
1263 ELSE
1264 IF ( p_an_sql )
1265 THEN
1266 l_dyn_sql := l_dyn_sql
1267 ||l_string_start||p_comps_r.bld_blk_info_type_id
1268 ||l_string_and ||p_comps_r.segment
1269 ||' <> '''||l_value_string||''' ';
1270 ELSIF ( p_vs_sql )
1271 THEN
1272 l_dyn_sql := l_dyn_sql
1273 ||l_string_start||p_comps_r.bld_blk_info_type_id
1274 ||l_string_and ||p_comps_r.segment
1275 ||' NOT IN ( '||l_value_string||' ) ';
1276 ELSE
1277 l_dyn_sql := l_dyn_sql
1278 ||l_string_start||p_comps_r.bld_blk_info_type_id
1279 ||l_string_and ||p_comps_r.segment
1280 ||' <> '''||l_value_string||''' )';
1281 END IF;
1282 END IF;
1283 ELSE
1284 -- Ignore these TC components
1285 -- EAch Time Category SQL to be evaluated seperately from
1286 -- now on so combining of TIME_SQL not necessary
1287
1288 IF ( g_debug ) THEN
1289 hr_utility.trace('GAZ - another TC !!!!');
1290 END IF;
1291 END IF;
1292
1293 ELSE
1294
1295 IF ( g_debug ) THEN
1296 hr_utility.trace('not first time round');
1297 hr_utility.trace('sql is '||l_dyn_sql);
1298 END IF;
1299
1300 -- set l_string_start for the case when generating SQL for alernate name
1301 IF ( p_comps_r.segment IS NOT NULL )
1302 THEN
1303 IF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1304 THEN
1305 IF ( p_an_sql )
1306 THEN
1307 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1308 ||p_comps_r.segment||' IS NOT NULL ';
1309 ELSE
1310 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1311 ||l_string_start||p_comps_r.bld_blk_info_type_id
1312 ||l_string_and ||p_comps_r.segment
1313 ||' IS NOT NULL )';
1314 END IF;
1315
1316 ELSIF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'N' ) )
1317 THEN
1318 IF ( g_debug ) THEN
1319 hr_utility.trace('GAZ - INVALID COMBO');
1320 END IF;
1321
1322 fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1323 fnd_message.raise_error;
1324
1325 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1326 THEN
1327 IF ( p_an_sql )
1328 THEN
1329 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1330 ||p_comps_r.segment||' IS NULL ';
1331 ELSE
1332 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1333 ||l_string_start||p_comps_r.bld_blk_info_type_id
1334 ||l_string_and ||p_comps_r.segment
1335 ||' IS NULL )';
1336 END IF;
1337
1338 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'N' ) )
1339 THEN
1340 IF ( p_an_sql )
1341 THEN
1342 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1343 ||p_comps_r.segment||' IS NOT NULL ';
1344 ELSE
1345 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1346 ||l_string_start||p_comps_r.bld_blk_info_type_id
1347 ||l_string_and ||p_comps_r.segment
1348 ||' IS NOT NULL )';
1349 END IF;
1350
1351 ELSIF ( p_comps_r.equal_to = 'Y' )
1352 THEN
1353 IF ( p_an_sql )
1354 THEN
1355 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1356 ||p_comps_r.segment||' = '''||l_value_string||''' ';
1357 ELSE
1358 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1359 ||l_string_start||p_comps_r.bld_blk_info_type_id
1360 ||l_string_and ||p_comps_r.segment
1361 ||' = '''||l_value_string||''' )';
1362 END IF;
1363 ELSE
1364 IF ( p_an_sql )
1365 THEN
1366 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1367 ||p_comps_r.segment||' <> '''||l_value_string||''' ';
1368 ELSE
1369 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1370 ||l_string_start||p_comps_r.bld_blk_info_type_id
1371 ||l_string_and ||p_comps_r.segment
1372 ||' <> '''||l_value_string||''' )';
1373 END IF;
1374 END IF;
1375 ELSE
1376 -- Ignore these TC components
1377 -- EAch Time Category SQL to be evaluated seperately from
1378 -- now on so combining of TIME_SQL not necessary
1379
1380 IF ( g_debug ) THEN
1381 hr_utility.trace('GAZ - another TC !!!!');
1382 END IF;
1383 END IF;
1384 END IF;
1385
1386 IF ( g_debug ) THEN
1387 hr_utility.trace('dyn sql is '||l_dyn_sql);
1388 END IF;
1389
1390 p_time_sql := l_dyn_sql;
1391
1392 END get_dyn_sql;
1393
1394 -- ----------------------------------------------------------------------------
1395 -- |--------------------------< mapping_component_string >-------------------------|
1396 -- ----------------------------------------------------------------------------
1397 PROCEDURE mapping_component_string ( p_time_category_id NUMBER
1398 , p_time_sql IN OUT NOCOPY CLOB ) IS -- Bug 15977687
1399
1400
1401
1402 l_proc varchar2(72);
1403
1404 l_dynamic_sql CLOB; -- Bug 15977687
1405 l_ref_dyn_sql CLOB; -- Bug 15977687
1406
1407
1408 BEGIN -- mapping_component_string
1409
1410 g_debug := hr_utility.debug_enabled;
1411
1412 l_first_time_round := TRUE;
1413
1414 -- ***************************************
1415 -- MAPPING_COMPONENT_STRING
1416 -- ***************************************
1417
1418 IF ( g_debug ) THEN
1419 l_proc := g_package||'mapping_component_string';
1420 hr_utility.set_location('Processing '||l_proc, 10);
1421
1422 hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1423 END IF;
1424
1425 -- get the time category operator
1426
1427 OPEN csr_get_operator ( p_time_category_id);
1428 FETCH csr_get_operator INTO l_operator;
1429 CLOSE csr_get_operator;
1430
1431 -- check for cached value first
1432
1433 -- maintain index value
1434
1435 OPEN csr_get_category_comps ( p_time_category_id );
1436 FETCH csr_get_category_comps INTO l_comps_r;
1437
1438 IF ( g_debug ) THEN
1439 hr_utility.set_location('Processing '||l_proc, 20);
1440 END IF;
1441
1442 WHILE csr_get_category_comps%FOUND
1443 LOOP
1444
1445 IF ( g_debug ) THEN
1446 hr_utility.set_location('Processing '||l_proc, 30);
1447 END IF;
1448
1449 get_dyn_sql ( p_time_sql => l_dynamic_sql
1450 , p_comps_r => l_comps_r
1451 , p_operator => l_operator );
1452
1453 IF ( g_debug ) THEN
1454 hr_utility.set_location('Processing '||l_proc, 60);
1455 END IF;
1456
1457 FETCH csr_get_category_comps INTO l_comps_r;
1458
1459 l_first_time_round := FALSE;
1460
1461 END LOOP;
1462
1463 IF ( g_debug ) THEN
1464 hr_utility.set_location('Processing '||l_proc, 70);
1465 END IF;
1466
1467 CLOSE csr_get_category_comps;
1468
1469 IF ( g_debug ) THEN
1470 hr_utility.set_location('Processing '||l_proc, 80);
1471 END IF;
1472
1473 IF ( l_dynamic_sql IS NOT NULL )
1474 THEN
1475
1476 l_dynamic_sql := ' ( '||l_dynamic_sql||' ) ';
1477 validate_time_category_sql ( l_dynamic_sql );
1478
1479 END IF;
1480
1481 p_time_sql := l_dynamic_sql;
1482
1483 IF ( g_debug ) THEN
1484 hr_utility.trace('Final dyn sql is '||NVL(p_time_sql,'Empty'));
1485 END IF;
1486
1487
1488 END mapping_component_string;
1489
1490 -- ----------------------------------------------------------------------------
1491 -- |-------------< BEGIN OF set_dynamic_sql_string >-------------------------|
1492 -- ----------------------------------------------------------------------------
1493
1494
1495 BEGIN
1496
1497 g_debug := hr_utility.debug_enabled;
1498
1499 mapping_component_string (
1500 p_time_category_id => p_time_category_id
1501 , p_time_sql => l_time_sql );
1502
1503 if g_debug then
1504 hr_utility.trace('set dyn sql string string is '||l_time_sql);
1505 end if;
1506
1507 UPDATE hxc_time_categories
1508 SET time_sql = l_time_sql
1509 WHERE time_category_id = p_time_category_id;
1510
1511 exception when others then
1512
1513 if g_debug then
1514 hr_utility.trace('exception is '||SQLERRM);
1515 end if;
1516
1517 raise;
1518
1519 END set_dynamic_sql_string;
1520
1521 END hxc_load_pkg;