[Home] [Help]
PACKAGE BODY: APPS.CN_RULES_DISP_PUB
Source
1 PACKAGE BODY cn_rules_disp_pub AS
2 --$Header: cnprulb.pls 115.6 2002/11/21 21:06:23 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_RULES_DISP_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnrulb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12
13 PROCEDURE get_rules
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
18
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 x_loading_status OUT NOCOPY VARCHAR2,
23
24 p_ruleset_id IN NUMBER ,
25 p_parent_id IN NUMBER ,
26 p_date IN DATE ,
27 p_start_record IN NUMBER := 1,
28 p_increment_count IN NUMBER,
29
30 x_rules_display_tbl OUT NOCOPY rls_dsp_tbl_type,
31 x_rules_count OUT NOCOPY NUMBER
32 )
33
34
35 IS
36
37 l_api_name CONSTANT VARCHAR2(30) := 'get_rules';
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_flag NUMBER := 0;
40 l_user_expression NUMBER := 0;
41 l_column_value NUMBER;
42 node_value VARCHAR2(2000);
43
44 -- variables for the lookups
45 l_when VARCHAR2(80);
46 l_is_bet VARCHAR2(80);
47 l_is_not_bet VARCHAR2(80);
48 l_and VARCHAR2(80);
49 l_desc VARCHAR2(80);
50 l_not_desc VARCHAR2(80);
51 l_hier VARCHAR2(80);
52
53 CURSOR rules_cur1(l_parent_rule_id NUMBER, l_ruleset_id NUMBER) IS
54 SELECT 0 rule_level, cnr.name rule_name, cnrc.name revenue_class,
55 cnr.rule_id rule_id
56 FROM cn_rules cnr, cn_revenue_classes cnrc
57 WHERE cnr.rule_id = Nvl(l_parent_rule_id,-1002)
58 AND cnr.ruleset_id = Nvl(l_ruleset_id,-1002)
59 AND cnr.revenue_class_id = cnrc.revenue_class_id (+)
60 AND cnr.rule_id IN (SELECT rule_id FROM cn_rules_hierarchy);
61
62 CURSOR rules_cur(l_parent_rule_id NUMBER, l_ruleset_id NUMBER) IS
63 SELECT a.rule_level rule_level,cnr.name rule_name,
64 cnrc.name revenue_class, a.rule_id rule_id
65 FROM
66 (
67 SELECT level rule_level, rule_id, parent_rule_id, sequence_number
68 FROM cn_rules_hierarchy
69 WHERE ruleset_id=Nvl(l_ruleset_id,-1002)
70 CONNECT BY PRIOR rule_id = parent_rule_id
71 START WITH parent_rule_id = Nvl(l_parent_rule_id, -1002)
72 ) a , cn_rules cnr, cn_revenue_classes cnrc
73 WHERE a.rule_id = cnr.rule_id
74 AND cnr.ruleset_id = Nvl(l_ruleset_id,-1002)
75 AND cnr.revenue_class_id = cnrc.revenue_class_id (+);
76
77
78 CURSOR expr_cur(l_rule_id NUMBER,l_ruleset_id NUMBER) IS
79 SELECT cnobj.user_name object_name,
80 cnh.name hierarchy_name, cnattr.column_value column_value,
81 cnattr.not_flag not_flag, cnattr.high_value high_value,
82 cnattr.low_value low_value , cnattr.dimension_hierarchy_id dimension_hierarchy_id
83 FROM cn_attribute_rules cnattr, cn_objects cnobj,
84 cn_head_hierarchies cnh
85 WHERE cnattr.rule_id = l_rule_id
86 AND cnattr.ruleset_id = l_ruleset_id
87 AND cnattr.column_id = cnobj.object_id (+)
88 AND cnattr.dimension_hierarchy_id = cnh.head_hierarchy_id(+);
89
90
91
92
93 l_record_Count NUMBER := 0;
94
95
96 BEGIN
97
98 --+
99 -- Standard call to check for call compatibility.
100 --+
101 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
102 p_api_version ,
103 l_api_name,
104 G_PKG_NAME )
105 THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109 --+
110 -- Initialize message list if p_init_msg_list is set to TRUE.
111 --+
112 IF FND_API.to_Boolean( p_init_msg_list ) THEN
113 FND_MSG_PUB.initialize;
114 END IF;
115
116 --+
117 -- Initialize API return status to success
118 --+
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 x_loading_status := 'CN_INSERTED';
121
122 --+
123 -- API body
124 --+
125
126 -- Get the lookup codes
127
128
129 l_when := cn_api.get_lkup_meaning('When','CLS_RULE_EXPRESSION');
130 l_is_bet := cn_api.get_lkup_meaning('is_in_bet','CLS_RULE_EXPRESSION');
131 l_is_not_bet := cn_api.get_lkup_meaning('is_not_in_bet','CLS_RULE_EXPRESSION');
132 l_and := cn_api.get_lkup_meaning('and','CLS_RULE_EXPRESSION');
133 l_desc := cn_api.get_lkup_meaning('is_decs_of','CLS_RULE_EXPRESSION');
134 l_not_desc := cn_api.get_lkup_meaning('is_not_decs_of','CLS_RULE_EXPRESSION');
135 l_hier := cn_api.get_lkup_meaning('in_hier','CLS_RULE_EXPRESSION');
136
137
138 -- l_when := ' When ';
139 -- l_is_bet := ' is between ';
140 -- l_is_not_bet := ' is not between ';
141 -- l_and := ' and ';
142 -- l_desc := ' is descendant of ' ;
143 -- l_not_desc := ' is not a descendent of ';
144 -- l_hier := ' in hierarchy ';
145
146 x_rules_count :=0;
147
148 FOR rules IN rules_cur(p_parent_id,p_ruleset_id) LOOP
149 l_record_count := rules_cur%ROWCOUNT;
150
151 x_rules_count := x_rules_count + 1;
152
153 IF (( p_increment_count = -9999) OR (x_rules_count BETWEEN p_start_record
154 AND (p_start_record + p_increment_count -1)))
155 THEN
156 x_rules_display_tbl(x_rules_count).rule_level := rules.rule_level;
157 x_rules_display_tbl(x_rules_count).rule_name := rules.rule_name;
158 x_rules_display_tbl(x_rules_count).rule_revenue_class := rules.revenue_class;
159
160 l_flag := 0;
161 l_user_expression :=0;
162
163 -- first check if the user has created any expression
164 SELECT COUNT(1)
165 INTO l_user_expression
166 FROM CN_RULE_ATTR_EXPRESSION
167 WHERE RULE_ID = rules.rule_id ;
168
169 IF (l_user_expression > 0) THEN
170
171 -- for user created expressions
172
173 SELECT DISTINCT expression
174 INTO x_rules_display_tbl(x_rules_count).rule_expression
175 FROM CN_ATTRIBUTE_RULES
176 WHERE RULE_ID = rules.rule_id ;
177
178 ELSE
179 -- for expression not 'created' by the user
180 FOR expr IN expr_cur(rules.rule_id,p_ruleset_id) LOOP
181 -- first decide whether this is the first expression or not.
182 IF l_flag = 0 THEN
183 x_rules_display_tbl(x_rules_count).rule_expression := ' ';
184 l_flag := 1;
185 ELSE -- not first expression, need to AND with the previous expression
186 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || l_and ;
187 END IF;
188
189 IF expr.dimension_hierarchy_id IS NOT NULL THEN
190 l_column_value := expr.column_value;
191 SELECT name INTO node_value
192 FROM cn_hierarchy_nodes
193 WHERE value_id=l_column_value;
194
195 IF expr.not_flag = 'N' THEN
196 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_desc || '''' || node_value || '''' || l_hier || '''' || expr.hierarchy_name || '''' || ' ' ;
197 ELSE
198 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_not_desc ||''''|| node_value || '''' || l_hier || '''' || expr.hierarchy_name || '''' || ' ' ;
199 END IF;
200
201 ELSE
202 IF expr.column_value IS NULL THEN
203 IF expr.not_flag = 'N' THEN
204 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_is_bet ||''''|| expr.high_value || '''' || l_and ||''''|| expr.low_value ||''''|| ' ';
205 ELSE
206 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_is_not_bet || '''' || expr.high_value || '''' || l_and || '''' || expr.low_value || '''' || ' ';
207 END IF;
208 ELSE
209 IF expr.not_flag = 'N' THEN
210 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || ' = ' || '''' || expr.column_value || ''''|| ' ';
211 ELSE
212 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || ' <> ' || '''' || expr.column_value ||''''|| ' ';
213 END IF;
214 END IF;
215 END IF;
216 END LOOP;
217
218 END IF; -- this is for the user created expressions
219 END IF; -- this is for the page scrolling
220 END LOOP;
221
222
223 if l_record_count = 0 or l_record_count is null Then
224
225
226 FOR rules IN rules_cur1(p_parent_id,p_ruleset_id) LOOP
227 l_record_count := rules_cur1%ROWCOUNT;
228
229 x_rules_count := x_rules_count + 1;
230
231 IF (( p_increment_count = -9999) OR (x_rules_count BETWEEN p_start_record
232 AND (p_start_record + p_increment_count -1)))
233 THEN
234 x_rules_display_tbl(x_rules_count).rule_level := rules.rule_level;
235 x_rules_display_tbl(x_rules_count).rule_name := rules.rule_name;
236 x_rules_display_tbl(x_rules_count).rule_revenue_class := rules.revenue_class;
237
238 l_flag := 0;
239 l_user_expression :=0;
240
241 -- first check if the user has created any expression
242 SELECT COUNT(1)
243 INTO l_user_expression
244 FROM CN_RULE_ATTR_EXPRESSION
245 WHERE RULE_ID = rules.rule_id ;
246
247 IF (l_user_expression > 0) THEN
248
249 -- for user created expressions
250
251 SELECT DISTINCT expression
252 INTO x_rules_display_tbl(x_rules_count).rule_expression
253 FROM CN_ATTRIBUTE_RULES
254 WHERE RULE_ID = rules.rule_id ;
255
256 ELSE
257 -- for expression not 'created' by the user
258 FOR expr IN expr_cur(rules.rule_id,p_ruleset_id) LOOP
259 -- first decide whether this is the first expression or not.
260 IF l_flag = 0 THEN
261 x_rules_display_tbl(x_rules_count).rule_expression := ' ';
262 l_flag := 1;
263 ELSE -- not first expression, need to AND with the previous expression
264 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || l_and ;
265 END IF;
266
267 IF expr.dimension_hierarchy_id IS NOT NULL THEN
268 l_column_value := expr.column_value;
269 SELECT name INTO node_value
270 FROM cn_hierarchy_nodes
271 WHERE value_id=l_column_value;
272
273 IF expr.not_flag = 'N' THEN
274 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_desc || '''' || node_value || '''' || l_hier || '''' || expr.hierarchy_name || '''' || ' ' ;
275 ELSE
276 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_not_desc ||''''|| node_value || '''' || l_hier || '''' || expr.hierarchy_name || '''' || ' ' ;
277 END IF;
278
279 ELSE
280 IF expr.column_value IS NULL THEN
281 IF expr.not_flag = 'N' THEN
282 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_is_bet ||''''|| expr.high_value || '''' || l_and ||''''|| expr.low_value ||''''|| ' ';
283 ELSE
284 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || l_is_not_bet || '''' || expr.high_value || '''' || l_and || '''' || expr.low_value || '''' || ' ';
285 END IF;
286 ELSE
287 IF expr.not_flag = 'N' THEN
288 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || ' = ' || '''' || expr.column_value || ''''|| ' ';
289 ELSE
290 x_rules_display_tbl(x_rules_count).rule_expression := x_rules_display_tbl(x_rules_count).rule_expression || expr.object_name || ' <> ' || '''' || expr.column_value ||''''|| ' ';
291 END IF;
292 END IF;
293 END IF;
294 END LOOP;
295
296 END IF; -- this is for the user created expressions
297 END IF; -- this is for the page scrolling
298 END LOOP;
299
300 end if;
301
302
303
304
305 EXCEPTION
306 WHEN FND_API.G_EXC_ERROR THEN
307 x_return_status := FND_API.G_RET_STS_ERROR ;
308 FND_MSG_PUB.Count_And_Get
309 (
310 p_count => x_msg_count ,
311 p_data => x_msg_data ,
312 p_encoded => FND_API.G_FALSE
313 );
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 x_loading_status := 'UNEXPECTED_ERR';
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
317 FND_MSG_PUB.Count_And_Get
318 (
319 p_count => x_msg_count ,
320 p_data => x_msg_data ,
321 p_encoded => FND_API.G_FALSE
322 );
323 WHEN OTHERS THEN
324 x_loading_status := 'UNEXPECTED_ERR';
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
326 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327 THEN
328 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
329 END IF;
330 FND_MSG_PUB.Count_And_Get
331 (
332 p_count => x_msg_count ,
333 p_data => x_msg_data ,
334 p_encoded => FND_API.G_FALSE
335 );
336
337 END;
338 END cn_rules_disp_pub;