DBA Data[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;