[Home] [Help]
PACKAGE BODY: APPS.BEN_BENXLAYT_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENXLAYT_XMLP_PKG AS
2 /* $Header: BENXLAYTB.pls 120.1 2007/12/10 08:40:43 vjaganat noship $ */
3
4 function commentsformula(total_function in varchar2, data_element_type in varchar2, string_value in varchar2, condition_data_element in number, ext_data_element in number, sum_data_element in number) return varchar2 is
5 cursor get_function is
6 SELECT meaning
7 FROM hr_lookups
8 WHERE lookup_code = total_function
9 AND lookup_type = 'BEN_EXT_TTL_FNCTN';
10 l_function hr_lookups.meaning%type;
11
12 l_operator hr_lookups.meaning%type;
13 cursor get_data_element
14 (p_ext_data_elmt_id number) is
15 SELECT name
16 FROM ben_ext_data_elmt
17 WHERE ext_data_elmt_id = p_ext_data_elmt_id ;
18
19
20 cursor get_where_clause
21 (p_ext_data_elmt_id number) is
22 SELECT cond_ext_data_elmt_id ,oper_cd,val,and_or_cd
23 FROM ben_ext_where_clause where ext_data_elmt_id = p_ext_data_elmt_id ;
24
25 l_sum_data_elmt ben_ext_data_elmt.name%type;
26 l_cond_data_elmt ben_ext_data_elmt.name%type;
27
28 l_where_flag boolean :=FALSE;
29 l_where_clause varchar2(2000);
30 l_return varchar2(2000);
31 begin
32 if data_element_type = 'S' then
33 l_return := 'String Value = ' || string_value;
34 elsif data_element_type = 'D' then
35 l_return := 'Values are:';
36 elsif data_element_type = 'T' then
37 open get_function;
38 fetch get_function into l_function;
39 close get_function;
40 end if;
41 if total_function = 'CNT' then
42 l_return := l_function || ' all ' || fnd_global.local_chr(10);
43 if condition_data_element is not null then
44 FOR where_clause IN get_where_clause(ext_data_element) LOOP
45 open get_data_element(where_clause.cond_ext_data_elmt_id);
46 fetch get_data_element into l_cond_data_elmt;
47 close get_data_element;
48 l_where_flag := TRUE ;
49 l_where_clause := l_where_clause || l_cond_data_elmt || ' ' || hr_general.decode_lookup('BEN_EXT_OPER',where_clause.oper_cd)
50 || ' ' || where_clause.val || ' ' || hr_general.decode_lookup('BEN_EXT_AND_OR',where_clause.and_or_cd)
51 || fnd_global.local_chr(10);
52 END LOOP;
53
54 if l_where_flag then
55 l_return := l_return || ' where ' || l_where_clause;
56 end if;
57
58 end if;
59 elsif total_function = 'SUM' then
60
61 open get_data_element(sum_data_element);
62 fetch get_data_element into l_sum_data_elmt;
63 close get_data_element;
64 l_return := l_function || ' ' || l_sum_data_elmt || fnd_global.local_chr(10);
65 if condition_data_element is not null then
66 FOR where_clause IN get_where_clause(ext_data_element) LOOP
67 open get_data_element(where_clause.cond_ext_data_elmt_id);
68 fetch get_data_element into l_cond_data_elmt;
69 close get_data_element;
70 l_where_flag := TRUE ;
71 l_where_clause := l_where_clause || l_cond_data_elmt || ' ' || hr_general.decode_lookup('BEN_EXT_OPER',where_clause.oper_cd)
72 || ' ' || where_clause.val || ' ' || hr_general.decode_lookup('BEN_EXT_AND_OR',where_clause.and_or_cd)
73 || fnd_global.local_chr(10);
74 END LOOP;
75
76 if l_where_flag then
77 l_return := l_return || ' where ' || l_where_clause;
78 end if;
79
80 end if;
81 end if; return (l_return);
82 end;
83
84 function BeforeReport return boolean is
85 begin
86 --hr_standard.event('BEFORE REPORT');
87 return (TRUE);
88 end;
89
90 function AfterReport return boolean is
91 begin
92 --hr_standard.event('AFTER REPORT');
93 return (TRUE);
94 end;
95
96 --Functions to refer Oracle report placeholders--
97
98 END BEN_BENXLAYT_XMLP_PKG ;