[Home] [Help]
PACKAGE BODY: APPS.HR_CAL_ENTRY_VALUE_API
Source
1 Package Body HR_CAL_ENTRY_VALUE_API as
2 /* $Header: peenvapi.pkb 120.0 2005/05/31 08:10:04 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(23) := 'HR_CAL_ENTRY_VALUE_API.';
7 g_current_entry_id number(15);
8 g_current_osv_id number(15);
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< create_entry_value >---------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure create_entry_value
15 (p_validate in boolean default false
16 ,p_effective_date in date
17 ,p_calendar_entry_id in number
18 ,p_usage_flag in varchar2
19 ,p_hierarchy_node_id in number default null
20 ,p_value in varchar2 default null
21 ,p_org_structure_element_id in number default null
22 ,p_organization_id in number default null
23 ,p_override_name in varchar2 default null
24 ,p_override_type in varchar2 default null
25 ,p_parent_entry_value_id in number default null
26 ,p_identifier_key in varchar2 default null
27 ,p_cal_entry_value_id out nocopy number
28 ,p_object_version_number out nocopy number) IS
29
30 --
31 -- Declare cursors and local variables
32 --
33 l_proc varchar2(70) := g_package||'create_entry_value';
34 l_cal_entry_value_id number(15);
35 l_object_version_number number(15);
36 l_effective_date date;
37 --
38 begin
39 hr_utility.set_location('Entering:'|| l_proc, 10);
40 --
41 -- Issue a savepoint
42 --
43 savepoint create_cal_entry_value;
44 --
45 -- Truncate the time portion from all IN date parameters
46 --
47 l_effective_date := trunc(p_effective_date);
48 --
49 -- Call Before Process User Hook
50 --
51 begin
52 HR_CAL_ENTRY_VALUE_BK1.create_entry_value_b
53 (p_effective_date => l_effective_date
54 ,p_calendar_entry_id => p_calendar_entry_id
55 ,p_hierarchy_node_id => p_hierarchy_node_id
56 ,p_value => p_value
57 ,p_org_structure_element_id => p_org_structure_element_id
58 ,p_organization_id => p_organization_id
59 ,p_override_name => p_override_name
60 ,p_override_type => p_override_type
61 ,p_parent_entry_value_id => p_parent_entry_value_id
62 ,p_usage_flag => p_usage_flag
63 );
64 exception
65 when hr_api.cannot_find_prog_unit then
66 hr_api.cannot_find_prog_unit_error
67 (p_module_name => 'create_entry_value_b'
68 ,p_hook_type => 'BP'
69 );
70 end;
71 --
72 -- Validation in addition to Row Handlers
73 --
74 --
75 -- Process Logic
76 --
77 per_env_ins.ins
78 (p_effective_date => l_effective_date
79 ,p_calendar_entry_id => p_calendar_entry_id
80 ,p_hierarchy_node_id => p_hierarchy_node_id
81 ,p_value => p_value
82 ,p_org_structure_element_id => p_org_structure_element_id
83 ,p_organization_id => p_organization_id
84 ,p_override_name => p_override_name
85 ,p_override_type => p_override_type
86 ,p_parent_entry_value_id => p_parent_entry_value_id
87 ,p_usage_flag => p_usage_flag
88 ,p_identifier_key => p_identifier_key
89 ,p_cal_entry_value_id => l_cal_entry_value_id
90 ,p_object_version_number => l_object_version_number);
91 --
92 -- Call After Process User Hook
93 --
94 begin
95 HR_CAL_ENTRY_VALUE_BK1.create_entry_value_a
96 (p_effective_date => l_effective_date
97 ,p_calendar_entry_id => p_calendar_entry_id
98 ,p_hierarchy_node_id => p_hierarchy_node_id
99 ,p_value => p_value
100 ,p_org_structure_element_id => p_org_structure_element_id
101 ,p_organization_id => p_organization_id
102 ,p_override_name => p_override_name
103 ,p_override_type => p_override_type
104 ,p_parent_entry_value_id => p_parent_entry_value_id
105 ,p_usage_flag => p_usage_flag
106 ,p_cal_entry_value_id => l_cal_entry_value_id
107 ,p_object_version_number => l_object_version_number
108 );
109 exception
110 when hr_api.cannot_find_prog_unit then
111 hr_api.cannot_find_prog_unit_error
112 (p_module_name => 'create_entry_value_a'
113 ,p_hook_type => 'AP'
114 );
115 end;
116 --
117 -- When in validation only mode raise the Validate_Enabled exception
118 --
119 if p_validate then
120 raise hr_api.validate_enabled;
121 end if;
122 --
123 -- Set all output arguments
124 --
125 p_cal_entry_value_id := l_cal_entry_value_id;
126 p_object_version_number := l_object_version_number;
127 --
128 hr_utility.set_location(' Leaving:'||l_proc, 70);
129 exception
130 when hr_api.validate_enabled then
131 --
132 -- As the Validate_Enabled exception has been raised
133 -- we must rollback to the savepoint
134 --
135 rollback to create_cal_entry_value;
136 --
137 -- Only set output warning arguments
138 -- (Any key or derived arguments must be set to null
139 -- when validation only mode is being used.)
140 --
141 p_cal_entry_value_id := null;
142 p_object_version_number := null;
143 hr_utility.set_location(' Leaving:'||l_proc, 80);
144 when others then
145 --
146 -- A validation or unexpected error has occured
147 --
148 rollback to create_cal_entry_value;
149 hr_utility.set_location(' Leaving:'||l_proc, 90);
150 raise;
151 end create_entry_value;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |----------------------------< update_entry_value >------------------------|
155 -- ----------------------------------------------------------------------------
156 --
157 procedure update_entry_value
158 (p_validate in boolean default false
159 ,p_effective_date in date
160 ,p_cal_entry_value_id in number
161 ,p_object_version_number in out nocopy number
162 ,p_override_name in varchar2 default hr_api.g_varchar2
163 ,p_override_type in varchar2 default hr_api.g_varchar2
164 ,p_parent_entry_value_id in number default hr_api.g_number
165 ,p_usage_flag in varchar2 default hr_api.g_varchar2
166 ) IS
167
168 --
169 -- Declare cursors and local variables
170 --
171 l_proc varchar2(80) := g_package||'update_entry_value';
172 l_cal_entry_value_id per_cal_entry_values.cal_entry_value_id%TYPE;
173 l_object_version_number per_cal_entry_values.object_version_number%TYPE;
174 l_effective_date date;
175 --
176 begin
177 hr_utility.set_location('Entering:'|| l_proc, 10);
178 --
179 -- Issue a savepoint
180 --
181 savepoint update_cal_entry_value;
182 --
183 -- Store initial values for IN OUT parameters
184 --
185 l_object_version_number := p_object_version_number;
186 --
187 -- Truncate the time portion from all IN date parameters
188 --
189 l_effective_date := trunc(p_effective_date);
190 --
191 -- Call Before Process User Hook
192 --
193 begin
194 HR_CAL_ENTRY_VALUE_BK2.update_entry_value_b
195 (p_effective_date => l_effective_date
196 ,p_cal_entry_value_id => p_cal_entry_value_id
197 ,p_object_version_number => l_object_version_number
198 ,p_override_name => p_override_name
199 ,p_override_type => p_override_type
200 ,p_parent_entry_value_id => p_parent_entry_value_id
201 ,p_usage_flag => p_usage_flag
202 );
203 exception
204 when hr_api.cannot_find_prog_unit then
205 hr_api.cannot_find_prog_unit_error
206 (p_module_name => 'update_entry_value_b'
207 ,p_hook_type => 'BP'
208 );
209 end;
210 --
211 -- Validation in addition to Row Handlers
212 --
213 --
214 -- Process Logic
215 --
216 per_env_upd.upd
217 (p_effective_date => l_effective_date
218 ,p_cal_entry_value_id => p_cal_entry_value_id
219 ,p_object_version_number => l_object_version_number
220 ,p_override_name => p_override_name
221 ,p_override_type => p_override_type
222 ,p_parent_entry_value_id => p_parent_entry_value_id
223 ,p_usage_flag => p_usage_flag
224 );
225 --
226 --
227 begin
228 HR_CAL_ENTRY_VALUE_BK2.update_entry_value_a
229 (p_effective_date => l_effective_date
230 ,p_cal_entry_value_id => p_cal_entry_value_id
231 ,p_object_version_number => l_object_version_number
232 ,p_override_name => p_override_name
233 ,p_override_type => p_override_type
234 ,p_parent_entry_value_id => p_parent_entry_value_id
235 ,p_usage_flag => p_usage_flag
236 );
237
238 exception
239 when hr_api.cannot_find_prog_unit then
240 hr_api.cannot_find_prog_unit_error
241 (p_module_name => 'update_entry_value_a'
242 ,p_hook_type => 'AP'
243 );
244 end;
245 --
246 -- When in validation only mode raise the Validate_Enabled exception
247 --
248 if p_validate then
249 raise hr_api.validate_enabled;
250 end if;
251 --
252 -- Set all output arguments
253 --
254 p_object_version_number := l_object_version_number;
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 70);
257 exception
258 when hr_api.validate_enabled then
259 --
260 -- As the Validate_Enabled exception has been raised
261 -- we must rollback to the savepoint
262 --
263 rollback to update_cal_entry_value;
264 --
265 -- Only set output warning arguments
266 -- (Any key or derived arguments must be set to null
267 -- when validation only mode is being used.)
268 --
269 hr_utility.set_location(' Leaving:'||l_proc, 80);
270 when others then
271 --
272 -- A validation or unexpected error has occured
273 --
274 rollback to update_cal_entry_value;
275 hr_utility.set_location(' Leaving:'||l_proc, 90);
276 raise;
277 end update_entry_value;
278 --
279 --
280 -- ----------------------------------------------------------------------------
281 -- |------------------------< delete_entry_value >-------------------------|
282 -- ----------------------------------------------------------------------------
283 --
284 procedure delete_entry_value
285 (p_validate in boolean default false
286 ,p_cal_entry_value_id in number
287 ,p_object_version_number in number
288 ) IS
289 --
290 -- Declare cursors and local variables
291 --
292 l_proc varchar2(72) := g_package||'delete_entry_value';
293 --
294 begin
295 hr_utility.set_location('Entering:'|| l_proc, 5);
296 --
297 -- Issue a savepoint if operating in validation only mode.
298 --
299 savepoint delete_cal_entry_value;
300 --
301 -- Call Before Process User Hook
302 --
303 begin
304 HR_CAL_ENTRY_VALUE_BK3.delete_entry_value_b
305 (p_cal_entry_value_id => p_cal_entry_value_id,
306 p_object_version_number => p_object_version_number
307 );
308 exception
309 when hr_api.cannot_find_prog_unit then
310 hr_api.cannot_find_prog_unit_error
311 (p_module_name => 'delete_entry_value_b',
312 p_hook_type => 'BP'
313 );
314 end;
315 --
316 --
317 hr_utility.set_location(l_proc, 7);
318 --
319 -- Process Logic
320 --
321 per_env_del.del
322 (p_cal_entry_value_id => p_cal_entry_value_id
323 ,p_object_version_number => p_object_version_number
324 );
325 --
326 hr_utility.set_location(l_proc, 8);
327 --
328 --
329 -- Call After Process User Hook
330 begin
331 HR_CAL_ENTRY_VALUE_BK3.delete_entry_value_a
332 (p_cal_entry_value_id => p_cal_entry_value_id
333 ,p_object_version_number => p_object_version_number
334 );
335 exception
336 when hr_api.cannot_find_prog_unit then
337 hr_api.cannot_find_prog_unit_error
338 (p_module_name => 'delete_entry_value_a',
339 p_hook_type => 'AP'
340 );
341 end;
342 --
343 -- When in validation only mode raise the Validate_Enabled exception
344 --
345 if p_validate then
346 raise hr_api.validate_enabled;
347 end if;
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 11);
350 exception
351 when hr_api.validate_enabled then
352 --
353 -- As the Validate_Enabled exception has been raised
354 -- we must rollback to the savepoint
355 --
356 ROLLBACK TO delete_cal_entry_value;
357 --
358 hr_utility.set_location(' Leaving:'||l_proc, 12);
359 --
360 when others then
361 --
362 --
363 ROLLBACK TO delete_cal_entry_value;
364 --
365 raise;
366 --
367 end delete_entry_value;
368 --
369 --
370 FUNCTION get_display_value(p_entity_id IN VARCHAR2,
371 p_node_type IN VARCHAR2,
372 p_calendar_entry_id IN NUMBER,
373 p_vs_value_id IN VARCHAR2) RETURN VARCHAR2 IS
374 --
375
376 l_value_set_id number(15) := NULL;
377 -- get VS details for gen hier node EV..
378 CURSOR csr_VS IS
379 SELECT flex_value_set_id,validation_type
380 FROM fnd_flex_value_sets
381 WHERE flex_value_set_name = (SELECT pgt.child_value_set
382 FROM per_gen_hier_node_types pgt
383 WHERE pgt.child_node_type = p_node_type);
384
385 --
386 -- get VS details for stand-alone EV..
387 --
388 CURSOR csr_VS2 IS
389 SELECT value_set_id
390 FROM per_calendar_entries pce
391 WHERE pce.CALENDAR_ENTRY_ID = p_calendar_entry_id;
392
393 CURSOR csr_VS3 IS
394 SELECT description
395 FROM fnd_flex_values_vl
396 WHERE flex_value = p_entity_id
397 AND flex_value_set_id = l_value_set_id;
398 --
399 --
400
401 l_value_set_csr csr_VS%rowtype;
402 l_validation_type VARCHAR2(1);
403 l_table_name VARCHAR2(40);
404 l_column_id VARCHAR2(40);
405 l_column_name VARCHAR2(40);
406 l_where_clause VARCHAR2(4000);
407 l_sql_statement VARCHAR2(2000);
408 l_value_id VARCHAR2(255);
409 l_id VARCHAR2(255);
410 l_id_column VARCHAR2(200);
411 l_name VARCHAR2(2000) := 'NULL';
412 l_proc VARCHAR2(30) := 'get_display_value';
413 --
414 --
415 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
416 --
417 l_v_r fnd_vset.valueset_r;
418 l_v_dr fnd_vset.valueset_dr;
419 l_str varchar2(4000);
420 l_whr varchar2(4000);
421 l_ord varchar2(4000);
422 l_col varchar2(4000);
423 --
424 BEGIN
425 --
426 fnd_vset.get_valueset(valueset_id => p_vset_id ,
427 valueset => l_v_r,
428 format => l_v_dr);
429 --
430 IF l_v_r.table_info.table_name IS NULL THEN
431 --
432 l_str := '';
433 --
434 END IF;
435 --
436 IF l_v_r.table_info.id_column_name IS NULL THEN
437 --
438 l_str := '';
439 --
440 END IF;
441 --
442 IF l_v_r.table_info.value_column_name IS NULL THEN
443 --
444 l_str := '';
445 --
446 END IF;
447 --
448 l_whr := l_v_r.table_info.where_clause ;
449 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
450 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
451 ||' from '
452 ||l_v_r.table_info.table_name||' '||l_whr;
453 --
454 RETURN (l_str);
455 --
456 END get_sql_from_vset_id;
457 --
458 --
459 BEGIN
460
461 if (p_entity_id is not null and p_node_type is not null) then
462 -- open CSR1 to get the VS
463 open csr_VS;
464 fetch csr_VS into l_value_set_csr;
465 close csr_VS;
466
467 l_id := p_entity_id;
468 l_value_set_id := l_value_set_csr.flex_value_set_id;
469 l_validation_type := l_value_set_csr.validation_type;
470
471 elsif (p_calendar_entry_id is not null and p_vs_value_id is not null) then
472 -- open CSR2 to get the VS
473 open csr_VS2;
474 fetch csr_VS2 into l_value_set_id;
475 close csr_VS2;
476
477 l_id := p_vs_value_id;
478
479 end if;
480 --
481 if l_value_set_id is not null then
482 if l_validation_type = 'I' then
483 open csr_VS3;
484 fetch csr_VS3 into l_name;
485 close csr_vs3;
486
487 elsif l_validation_type = 'F' then
488
489 --
490 -- next evaluate the value_set for the entity_id supplied
491 hr_utility.set_location(l_proc, 30);
492 --
493 l_sql_statement := get_sql_from_vset_id(p_vset_id => l_value_set_id);
494 --
495 l_sql_statement := REPLACE(l_sql_statement
496 ,':$PROFILES$.PER_BUSINESS_GROUP_ID'
497 ,fnd_profile.value('PER_BUSINESS_GROUP_ID'));
498
499 --
500 l_id_column := SUBSTR(l_sql_statement,(INSTR(UPPER(l_sql_statement),'SELECT') +7)
501 ,INSTR(UPPER(l_sql_statement),',') -
502 (INSTR(UPPER(l_sql_statement),'SELECT')+ 7));
503
504 if INSTR(upper(l_sql_statement),'ORDER BY') > 0 then
505 l_sql_statement := SUBSTR(l_sql_statement,1,(INSTR(upper(l_sql_statement),'ORDER BY')-1));
506 end if;
507 --
508
509 if INSTR(upper(l_sql_statement),'WHERE') > 0 then
510 -- just append as AND clause...
511 l_sql_statement := l_sql_statement||' and '||l_id_column||' = :id ';
512 else
513 -- just append as WHERE clause...
514 l_sql_statement := l_sql_statement||' where '||l_id_column||' = :id ';
515 end if;
516 --
517 --
518 hr_utility.set_location(l_proc, 40);
519 --
520 BEGIN
521 --
522 EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING l_id;
523 --
524 EXCEPTION
525 --
526 WHEN OTHERS THEN
527 hr_utility.set_location(l_proc||l_sql_statement,45);
528 l_name := 'INVALID_VALUE_SET SQL: ' || l_sql_statement ||
529 ', vs_id : ' || l_value_set_id ||
530 ', l_value_id: ' || l_id;
531 --
532 END;
533 hr_utility.set_location(l_proc, 50);
534 --
535 end if;
536
537 end if;
538 --
539 hr_utility.set_location('Leaving :'||l_proc, 60);
540 --
541 RETURN l_name;
542 --
543 END get_display_value;
544 --
545 --
546 --
547 FUNCTION get_g_current_entry_id RETURN NUMBER IS
548 --
549 -- PURPOSE: return g_current_entry_id value.
550 --
551 BEGIN
552
553 RETURN g_current_entry_id;
554
555 END get_g_current_entry_id;
556 --
557 --
558 --
559 PROCEDURE set_g_current_entry_id (p_entry_id NUMBER) IS
560 --
561 -- PURPOSE: set g_current_entry_id value from the param supplied.
562 --
563 BEGIN
564 --
565 g_current_entry_id := p_entry_id;
566 --
567 END set_g_current_entry_id;
568 --
569 --
570 --
571 FUNCTION get_g_current_osv_id RETURN NUMBER IS
572 --
573 -- PURPOSE: return g_current_osv_id value.
574 --
575 BEGIN
576
577 RETURN g_current_osv_id;
578
579 END get_g_current_osv_id;
580 --
581 --
582 --
583 PROCEDURE set_g_current_osv_id (p_osv_id NUMBER) IS
584 --
585 -- PURPOSE: set g_current_osv_id value from the param supplied.
586 --
587 BEGIN
588 --
589 g_current_osv_id := p_osv_id;
590 --
591 END set_g_current_osv_id;
592
593 --
594
595
596 Function get_node_level (P_HIERARCHY_NODE_ID in NUMBER
597 ,P_HIERARCHY_VERSION_ID in NUMBER) RETURN VARCHAR2 IS
598
599 -- tree walk from supplied child gen hier node to top level
600 -- to obtain level of child within the hierarchy
601 -- This could utilise some form of data caching to improve performance,
602 -- possibly using the parent node to derive the current level
603 -- rather than tree-walking for all nodes...revisit
604 --
605 -- also returns . delimited list of the node's parent ids
606 --
607 CURSOR csr_get_level IS
608 select PARENT_HIERARCHY_NODE_ID from per_gen_hierarchy_nodes
609 where HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
610 connect by HIERARCHY_NODE_ID = prior PARENT_HIERARCHY_NODE_ID
611 AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
612 start with HIERARCHY_NODE_ID = P_HIERARCHY_NODE_ID;
613
614 l_level NUMBER := 0;
615 l_id_list VARCHAR2(2000) := null;
616 l_return varchar2(2000) := null;
617
618 BEGIN
619
620 for l_rec in csr_get_level loop
621 l_level := csr_get_level%rowcount;
622 l_id_list := to_char(l_rec.PARENT_HIERARCHY_NODE_ID)||'.'||l_id_list;
623 end loop;
624
625 l_return := to_char(l_level)||'.'||l_id_list;
626 RETURN l_return;
627
628 END get_node_level;
629 --
630 --
631 Function get_ele_level (P_ORG_STRUCTURE_ELEMENT_ID in NUMBER
632 ,P_ORG_STRUCTURE_VERSION_ID in NUMBER) RETURN VARCHAR2 IS
633
634 -- tree walk from supplied child link record (element) to top level link record
635 -- to obtain level of link child within the organisation hierarchy.
636 -- This could utilise some form of data caching to improve performance,
637 -- possibly using the parent node to derive the current level
638 -- rather than tree-walking for all nodes...revisit
639 --
640 -- also returns . delimited list of the link's parent link ids
641 --
642 CURSOR csr_get_level IS
643 select ORG_STRUCTURE_ELEMENT_ID from PER_ORG_STRUCTURE_ELEMENTS
644 where ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
645 connect by ORGANIZATION_ID_CHILD = prior ORGANIZATION_ID_PARENT
646 AND ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
647 start with ORG_STRUCTURE_ELEMENT_ID = P_ORG_STRUCTURE_ELEMENT_ID;
648
649 l_level NUMBER := 0;
650 l_id_list VARCHAR2(2000) := null;
651 l_return varchar2(2000) := null;
652
653 BEGIN
654
655 for l_rec in csr_get_level loop
656 l_level := csr_get_level%rowcount;
657 l_id_list := to_char(l_rec.ORG_STRUCTURE_ELEMENT_ID)||'.'||l_id_list;
658 end loop;
659
660 l_id_list := substr(l_id_list,1,instr(l_id_list,'.',-1,2));
661 l_return := to_char(l_level)||'..'||'-987123654.'||l_id_list;
662 RETURN l_return;
663
664 END get_ele_level;
665
666 -- ----------------------------------------------------------------------------
667 -- |--------------------------< get_sql_from_vset_id >------------------------|
668 -- ----------------------------------------------------------------------------
669 --
670 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
671 --
672 l_v_r fnd_vset.valueset_r;
673 l_v_dr fnd_vset.valueset_dr;
674 l_str varchar2(4000);
675 l_whr varchar2(4000);
676 l_ord varchar2(4000);
677 l_col varchar2(4000);
678 --
679 BEGIN
680 --
681 fnd_vset.get_valueset(valueset_id => p_vset_id ,
682 valueset => l_v_r,
683 format => l_v_dr);
684 --
685 IF l_v_r.table_info.table_name IS NULL THEN
686 --
687 l_str := '';
688 --
689 END IF;
690 --
691 IF l_v_r.table_info.id_column_name IS NULL THEN
692 --
693 l_str := '';
694 --
695 END IF;
696 --
697 IF l_v_r.table_info.value_column_name IS NULL THEN
698 --
699 l_str := '';
700 --
701 END IF;
702 --
703 l_whr := l_v_r.table_info.where_clause ;
704 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
705 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
706 ||' from '
707 ||l_v_r.table_info.table_name||' '||l_whr;
708
709 -- substitute the BG if required.
710 l_str := REPLACE(l_str,':$PROFILES$.PER_BUSINESS_GROUP_ID',fnd_profile.value('PER_BUSINESS_GROUP_ID'));
711 --
712 RETURN (l_str);
713 --
714 END get_sql_from_vset_id;
715 --
716 --
717 end HR_CAL_ENTRY_VALUE_API;