[Home] [Help]
PACKAGE BODY: APPS.XDO_DGF_RULE_PKG
Source
1 PACKAGE BODY xdo_dgf_rule_pkg AS
2 /* $Header: XDODGFRLB.pls 120.2 2008/01/22 18:40:28 bgkim noship $ */
3 g_current_runtime_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
5 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
7 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 g_error_buffer VARCHAR2(100);
9
10
11 -- procedure definitions
12 PROCEDURE evaluate_rules(p_rule_table IN OUT NOCOPY XDO_DGF_RPT_PKG.RULE_TABLE_TYPE)
13 IS
14 type arg_table_type is table of varchar2(4000) index by binary_integer;
15 i integer;
16 l_arg_table arg_table_type;
17 l_statement varchar2(32000);
18 l_cur_handler integer;
19 k integer;
20 l_return_char varchar2(4000);
21 l_ret number;
22 BEGIN
23 IF (g_level_statement >= g_current_runtime_level ) THEN
24 FND_LOG.STRING(g_level_statement,
25 'xdo_dgf_rule_pkg.evaluate_rules',
26 'start:p_rule_table.count = ' || p_rule_table.count );
27 END IF;
28 i:= p_rule_table.first;
29 LOOP
30
31 IF (g_level_statement >= g_current_runtime_level ) THEN
32 FND_LOG.STRING(g_level_statement,
33 'xdo_dgf_rule_pkg.evaluate_rules',
34 p_rule_table(i).rule_variable || ' ' ||
35 p_rule_table(i).rule_operator || ' ' ||
36 p_rule_table(i).rule_values);
37 END IF;
38
39 IF p_rule_table(i).rule_type IN ('F','P') THEN
40
41 p_rule_table(i).return_value := eval_simple_rule(
42 p_rule_table(i).rule_variable,
43 p_rule_table(i).rule_operator,
44 p_rule_table(i).rule_values,
45 p_rule_table(i).rule_values_datatype);
46 ELSIF p_rule_table(i).rule_type = 'D' THEN
47
48 IF (g_level_statement >= g_current_runtime_level ) THEN
49 FND_LOG.STRING(g_level_statement,
50 'xdo_dgf_rule_pkg.evaluate_rules',
51 'rule_type=''D''');
52 END IF;
53
54 l_arg_table(1) := p_rule_table(i).arg01;
55 l_arg_table(2) := p_rule_table(i).arg02;
56 l_arg_table(3) := p_rule_table(i).arg03;
57 l_arg_table(4) := p_rule_table(i).arg04;
58 l_arg_table(5) := p_rule_table(i).arg05;
59 l_arg_table(6) := p_rule_table(i).arg06;
60 l_arg_table(7) := p_rule_table(i).arg07;
61 l_arg_table(8) := p_rule_table(i).arg08;
62 l_arg_table(9) := p_rule_table(i).arg09;
63 l_arg_table(10) := p_rule_table(i).arg10;
64 l_statement := 'begin :1 := ' || p_rule_table(i).db_function;
65
66 IF p_rule_table(i).arg_number > 0 THEN
67
68 IF (g_level_statement >= g_current_runtime_level ) THEN
69 FND_LOG.STRING(g_level_statement,
70 'xdo_dgf_rule_pkg.evaluate_rules',
71 'arg_number > 0');
72 END IF;
73
74 l_statement := l_statement || '(';
75 -- for k in 2..(p_rule_table(i).arg_number + 1)
76 k := 2;
77 LOOP
78 l_statement := l_statement || ':' || k;
79 EXIT WHEN k = p_rule_table(i).arg_number + 1;
80 k := k + 1;
81 l_statement := l_statement || ',';
82 END LOOP;
83 l_statement := l_statement || ')';
84 END IF;
85 l_statement := l_statement || '; end;';
86
87 IF (g_level_statement >= g_current_runtime_level ) THEN
88 FND_LOG.STRING(g_level_statement,
89 'xdo_dgf_rule_pkg.evaluate_rules',
90 'before open cursor: l_statement = '
91 || l_statement);
92 END IF;
93
94 l_cur_handler := dbms_sql.open_cursor;
95 dbms_sql.parse(l_cur_handler,l_statement,2);
96 FOR j IN 2..(p_rule_table(i).arg_number + 1)
97 LOOP
98 dbms_sql.bind_variable(l_cur_handler,j ||'',l_arg_table(j-1));
99 END LOOP;
100 dbms_sql.bind_variable(l_cur_handler,'1', l_return_char, 2000);
101 l_ret := dbms_sql.execute(l_cur_handler);
102 dbms_sql.variable_value(l_cur_handler,'1', l_return_char);
103 dbms_sql.close_cursor(l_cur_handler);
104
105 IF (g_level_statement >= g_current_runtime_level ) THEN
106 FND_LOG.STRING(g_level_statement,
107 'xdo_dgf_rule_pkg.evaluate_rules',
108 'Cursor closed. l_return_char = ' || l_return_char);
109 END IF;
110
111 p_rule_table(i).return_value := eval_simple_rule(l_return_char,
112 p_rule_table(i).rule_operator,
113 p_rule_table(i).rule_values,
114 p_rule_table(i).rule_values_datatype);
115
116 END IF; -- ELSIF p_rule_table(i).rule_type = 'D' THEN
117 EXIT WHEN i = p_rule_table.last;
118 i := p_rule_table.next(i);
119 END LOOP;
120
121 IF (g_level_statement >= g_current_runtime_level ) THEN
122 FND_LOG.STRING(g_level_statement,
123 'xdo_dgf_rule_pkg.evaluate_rules',
124 'END: p_rule_table.count = ' || p_rule_table.count);
125 END IF;
126
127 END;
128
129
130 FUNCTION eval_simple_rule(p_rule_var IN varchar2,
131 p_rule_operator IN varchar2,
132 p_rule_values IN varchar2,
133 p_rule_values_datatype IN varchar2)
134
135 RETURN boolean
136 IS
137 l_return_value boolean := false;
138 l_list_value varchar2(4000) := p_rule_values;
139 l_single_value varchar2(4000);
140 l_separator_index integer;
141 l_rule_var number; -- for the p_rule_values_datatype = 'N'
142
143 BEGIN
144 CASE p_rule_operator
145 WHEN '=' THEN
146 IF p_rule_values_datatype = 'C' THEN
147 l_return_value := p_rule_var = p_rule_values;
148 ELSIF p_rule_values_datatype = 'N' THEN
149 l_return_value := to_number(p_rule_var) = to_number(p_rule_values);
150 END IF;
151 WHEN 'LIKE' THEN
152 l_return_value := p_rule_var LIKE p_rule_values;
153 WHEN 'IN' THEN
154 IF p_rule_values_datatype = 'C' THEN
155 LOOP
156 l_separator_index := instr(l_list_value, fnd_global.local_chr(10));
157 IF l_separator_index = 0 THEN
158 l_return_value := p_rule_var = l_list_value;
159 ELSE l_return_value := p_rule_var =
160 rtrim(substr(l_list_value,1,l_separator_index - 1 ), fnd_global.local_chr(13));
161 l_list_value := substr(l_list_value,l_separator_index + 1);
162 END IF;
163 EXIT WHEN l_separator_index = 0 or l_return_value;
164 END LOOP;
165 ELSIF p_rule_values_datatype = 'N' THEN
166 l_rule_var := to_number(p_rule_var);
167 LOOP
168 l_separator_index := instr(l_list_value, fnd_global.local_chr(10));
169 IF l_separator_index = 0 THEN
170 l_return_value := l_rule_var = to_number(l_list_value);
171 ELSE l_return_value := l_rule_var =
172 to_number(rtrim(substr(l_list_value,1,l_separator_index - 1 ),fnd_global.local_chr(13)));
173 l_list_value := substr(l_list_value,l_separator_index + 1);
174 END IF;
175 EXIT WHEN l_separator_index = 0 or l_return_value;
176 END LOOP;
177 END IF;
178 WHEN '<' THEN
179 IF p_rule_values_datatype = 'N' THEN
180 l_return_value := to_number(p_rule_var) < to_number(p_rule_values);
181 ELSIF p_rule_values_datatype = 'C' THEN
182 l_return_value := p_rule_var < p_rule_values;
183 END IF;
184 WHEN '<=' THEN
185 IF p_rule_values_datatype = 'N' THEN
186 l_return_value := to_number(p_rule_var) <= to_number(p_rule_values);
187 ELSIF p_rule_values_datatype = 'C' THEN
188 l_return_value := p_rule_var <= p_rule_values;
189 END IF;
190 WHEN '>' THEN
191 IF p_rule_values_datatype = 'N' THEN
192 l_return_value := to_number(p_rule_var) > to_number(p_rule_values);
193 ELSIF p_rule_values_datatype = 'C' THEN
194 l_return_value := p_rule_var > p_rule_values;
195 END IF;
196 WHEN '>=' THEN
197 IF p_rule_values_datatype = 'N' THEN
198 l_return_value := to_number(p_rule_var) >= to_number(p_rule_values);
199 ELSIF p_rule_values_datatype = 'C' THEN
200 l_return_value := p_rule_var >= p_rule_values;
201 END IF;
202 WHEN '<>' THEN
203 IF p_rule_values_datatype = 'C' THEN
204 l_return_value := p_rule_var <> p_rule_values;
205 ELSIF p_rule_values_datatype = 'N' THEN
206 l_return_value := to_number(p_rule_var) <> to_number(p_rule_values);
207 END IF;
208 END CASE;
209
210 RETURN l_return_value;
211 END;
212
213 FUNCTION evaluate_rules(p_rule_table IN XDO_DGF_RPT_PKG.RULE_TABLE_TYPE)
214 RETURN XDO_DGF_RPT_PKG.RULE_TABLE_TYPE
215 IS
216 l_rule_table XDO_DGF_RPT_PKG.RULE_TABLE_TYPE;
217 BEGIN
218 l_rule_table := p_rule_table;
219 evaluate_rules(l_rule_table);
220 RETURN l_rule_table;
221 END;
222 END xdo_dgf_rule_pkg;