[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_RULES_ONLINE_GEN_PVT
Source
1 PACKAGE BODY CN_SCA_RULES_ONLINE_GEN_PVT AS
2 -- $Header: cnvscagb.pls 120.4 2006/03/31 04:24:58 rrshetty noship $
3
4 g_package_name cn_obj_packages_v.name%TYPE;
5 g_org_id cn_sca_credit_rules.org_id%TYPE;
6
7 PROCEDURE get_min ( p_min IN OUT NOCOPY number, p_max number) IS
8 BEGIN
9 IF p_min =0 and p_max <> 0 THEN
10 p_min := p_max;
11 ELSIF p_min <> 0 and p_max <> 0 THEN
12 IF p_min > p_max THEN
13 p_min := p_max;
14 END IF;
15 END IF;
16 END;
17
18 -- search the next occurence of delimiter '+ - * / ( ) ' in sql_select portion and return the position
19 FUNCTION search_delimiter_select ( p_input_str varchar2, p_start number)
20 RETURN number IS
21 l_position_min NUMBER ;
22 l_position NUMBER;
23 BEGIN
24 l_position_min := instr( p_input_str, '*', p_start) ;
25 l_position := instr(p_input_str, '-', p_start);
26 get_min(l_position_min, l_position);
27
28 l_position := instr(p_input_str, '+', p_start);
29 get_min(l_position_min, l_position);
30
31 l_position := instr(p_input_str, '/', p_start);
32 get_min(l_position_min, l_position);
33
34 l_position := instr(p_input_str, '(', p_start);
35 get_min(l_position_min, l_position);
36
37 l_position := instr(p_input_str, ')', p_start);
38 get_min(l_position_min, l_position);
39
40 l_position := instr(p_input_str, ',', p_start);
41 get_min(l_position_min, l_position);
42
43 return l_position_min;
44 END;
45
46
47 -- search the next occurence of delimiter ', ' in sql_from portion and return the position
48 FUNCTION search_delimiter_from ( p_input_str varchar2, p_start number) RETURN
49 NUMBER IS
50 l_position_min number :=0;
51 l_position NUMBER;
52 BEGIN
53 l_position := instr(p_input_str, ',', p_start);
54 get_min(l_position_min, l_position);
55 return l_position_min;
56 END;
57
58 -- search the next occurence of delimiter 'and ' in sql_where portion and return the position
59 FUNCTION search_delimiter_where ( p_input_str varchar2, p_start number)
60 RETURN number IS
61 l_position_min number :=0;
62 l_position NUMBER;
63 BEGIN
64 l_position := instr(p_input_str, 'and', p_start);
65 get_min(l_position_min, l_position);
66 return l_position_min;
67 END;
68
69 -- search the next occurence of delimiter empty space in COMMENT and return the position
70 FUNCTION search_delimiter_comment ( p_input_str varchar2, p_start number)
71 RETURN number IS
72 l_position_min number :=0;
73 l_position NUMBER;
74 BEGIN
75 l_position := instr(p_input_str, ' ', p_start);
76 get_min(l_position_min, l_position);
77 return l_position_min;
78 END search_delimiter_comment ;
79
80
81 -- split the long sql statement into pieces less than 80 characters and return the position
82 PROCEDURE split_long_sql ( body_code IN OUT NOCOPY cn_utils.code_type,
83 p_input_str VARCHAR2 ,
84 sql_type VARCHAR2 )
85 IS
86 l_length NUMBER; /* total length of input string */
87 l_start NUMBER; /* the start position of current split */
88 l_next NUMBER; /* position of next delimiter */
89 l_next_prev NUMBER; /* position of previous delimiter */
90 l_limit NUMBER; /* the upper boundary of current split */
91
92 l_sql_segment_length NUMBER := 80;
93 BEGIN
94
95 DBMS_APPLICATION_INFO.SET_ACTION('inside SPLIT LONG ' );
96 l_start := 1;
97 l_limit := l_start + l_sql_segment_length;
98
99 l_length := Length(p_input_str);
100 l_next := l_start;
101 l_next_prev := l_start;
102
103 WHILE l_limit < l_length LOOP
104 WHILE l_next < l_limit LOOP
105
106 /* the postion of l_next delimiter is not beyong the upper boudaryyet */
107 l_next_prev := l_next;
108
109 IF sql_type = 'SELECT' THEN
110 l_next := search_delimiter_select(p_input_str, l_next_prev+1 );
111 ELSIF sql_type = 'FROM' THEN
112 l_next := NVL(search_delimiter_from(p_input_str, l_next_prev+1 ),0);
113 ELSIF sql_type = 'WHERE' THEN
114 l_next := search_delimiter_where(p_input_str, l_next_prev+1 );
115 ELSIF sql_type = 'COMMENT' THEN
116 l_next := search_delimiter_comment(p_input_str, l_next_prev+1 );
117 END IF;
118
119 IF l_next = 0 THEN /* no more delimiter */
120 EXIT;
121 END IF;
122
123 IF l_next >= l_limit THEN
124 l_next_prev := l_next;
125 END IF;
126
127
128 END LOOP;
129
130 IF sql_type = 'COMMENT' THEN
131 cn_utils.appindcr(body_code, '-- ' || substr(p_input_str, l_start,
132 l_next_prev - l_start) );
133 ELSE
134 cn_utils.appindcr(body_code, substr(p_input_str, l_start,
135 l_next_prev - l_start));
136 END IF;
137
138 l_start := l_next_prev ;
139 l_limit := l_start + l_sql_segment_length;
140
141 IF l_next = 0 THEN /* no more delimiter */
142 EXIT;
143 END IF;
144 END LOOP;
145
146 IF sql_type = 'COMMENT' THEN
147 cn_utils.appindcr(body_code, '--' || substr(p_input_str, l_start,
148 l_length - l_start + 1));
149 ELSE
150 cn_utils.appindcr(body_code, substr(p_input_str, l_start,
151 l_length - l_start + 1));
152 END IF;
153 END split_long_sql;
154
155
156
157 -- Get the object_id in cn_objects for formula, if not exist, create it.
158 PROCEDURE check_create_object(x_name cn_objects.name%TYPE,
159 x_object_type cn_objects.object_type%TYPE,
160 x_object_id IN OUT NOCOPY cn_objects.object_id%TYPE,
161 x_repository_id cn_repositories.repository_id%TYPE)
162 IS
163 dummy NUMBER;
164 x_rowid ROWID;
165 BEGIN
166 -- check whether formula package exist in cn_objects
167 SELECT COUNT(*)
168 INTO dummy
169 FROM cn_objects
170 WHERE name = x_name
171 AND object_type = x_object_type
172 AND org_id = g_org_id;
173
174 IF dummy = 0 THEN
175 x_object_id := cn_utils.get_object_id;
176
177 cn_objects_pkg.insert_row( x_rowid => x_rowid,
178 x_object_id => x_object_id,
179 x_org_id => g_org_id,
180 x_dependency_map_complete => 'N',
181 x_name => x_name,
182 x_description => null,
183 x_object_type => x_object_type,
184 x_repository_id => X_repository_id,
185 x_next_synchronization_date => null,
186 x_synchronization_frequency => null,
187 x_object_status => 'A',
188 x_object_value => NULL );
189
190 ELSIF dummy = 1 THEN
191 SELECT object_id INTO x_object_id
192 FROM cn_objects
193 WHERE name = x_name
194 AND object_type = x_object_type
195 AND org_id = g_org_id;
196 END IF;
197 EXCEPTION WHEN OTHERS THEN
198 cn_message_pkg.debug('IN check_create_object Exception handler name is ' || x_name ||
199 ' object_type is ' || x_object_type || ' object_id is ' || x_object_id );
200 RAISE;
201 END check_create_object;
202
203 -- initialize the procedure boilerplate
204 PROCEDURE proc_init_boilerplate (code IN OUT NOCOPY cn_utils.code_type,
205 procedure_name cn_obj_procedures_v.name%TYPE,
206 description cn_obj_procedures_v.description%TYPE)
207 IS
208 X_userid VARCHAR2(20);
209 BEGIN
210 SELECT user INTO X_userid FROM sys.dual;
211
212 cn_utils.appendcr(code, '--');
213 cn_utils.appendcr(code, '-- Procedure Name');
214 cn_utils.appendcr(code, '-- ' || procedure_name);
215 cn_utils.appendcr(code, '-- Purpose');
216 split_long_sql(code, description, 'COMMENT');
217 cn_utils.appendcr(code, '-- History');
218 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
219 cn_utils.appendcr(code, '--');
220 END proc_init_boilerplate;
221
222 -- initialize the procedure
223 PROCEDURE proc_init(procedure_name cn_obj_procedures_v.name%TYPE,
224 description cn_obj_procedures_v.description%TYPE,
225 parameter_list cn_obj_procedures_v.parameter_list%TYPE,
226 procedure_type cn_obj_procedures_v.procedure_type%TYPE,
227 return_type cn_obj_procedures_v.return_type%TYPE,
228 package_id cn_obj_procedures_v.package_id%TYPE,
229 repository_id cn_obj_procedures_v.repository_id%TYPE,
230 spec_code IN OUT NOCOPY cn_utils.code_type,
231 body_code IN OUT NOCOPY cn_utils.code_type) IS
232 BEGIN
233 -- Generate boilerplate comments
234 proc_init_boilerplate(spec_code, procedure_name, description);
235 proc_init_boilerplate(body_code, procedure_name, description);
236
237 -- Generate procedure header and parameters in both spec and body
238 IF (procedure_type = 'P') THEN
239 IF (parameter_list IS NOT NULL) THEN
240 split_long_sql(spec_code, 'PROCEDURE ' || procedure_name ||
241 ' (' || parameter_list || ')', 'FROM');
242 split_long_sql(body_code, 'PROCEDURE ' || procedure_name ||
243 ' (' || parameter_list || ')', 'FROM');
244 ELSE
245 cn_utils.appendcr(spec_code, 'PROCEDURE ' || procedure_name);
246 cn_utils.appendcr(body_code, 'PROCEDURE ' || procedure_name);
247 END IF;
248 ELSIF (procedure_type = 'F') THEN
249 IF (parameter_list IS NOT NULL) THEN
250 split_long_sql(spec_code, 'FUNCTION ' || procedure_name ||
251 ' (' || parameter_list || ')', 'FROM');
252 split_long_sql(body_code, 'FUNCTION ' || procedure_name ||
253 ' (' || parameter_list || ')', 'FROM');
254 ELSE
255 cn_utils.appendcr(spec_code, 'FUNCTION ' || procedure_name);
256 cn_utils.appendcr(body_code, 'FUNCTION ' || procedure_name);
257 END IF;
258 END IF;
259
260 IF (procedure_type = 'F') THEN
261 cn_utils.appendcr(spec_code, ' RETURN ' || return_type);
262 cn_utils.appendcr(body_code, ' RETURN ' || return_type);
263 END IF;
264
265 cn_utils.appendcr(spec_code, ';');
266 cn_utils.appendcr(spec_code);
267 cn_utils.appendcr(body_code, ' IS');
268 END proc_init;
269
270
271 -- create the code of the procedure get_winning rule
272 PROCEDURE get_winning_rule (spec_code IN OUT NOCOPY cn_utils.code_type,
273 body_code IN OUT NOCOPY cn_utils.code_type ,
274 x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE)
275 IS
276 procedure_name cn_obj_procedures_v.name%TYPE;
277 procedure_desc cn_obj_procedures_v.description%TYPE;
278 parameter_list cn_obj_procedures_v.parameter_list%TYPE;
279 package_spec_id cn_obj_packages_v.package_id%TYPE;
280 x_repository_id cn_repositories.repository_id%TYPE;
281 l_attrib_counter NUMBER := 1;
282 l_rule_counter NUMBER := 1;
283
284 -- ganesh uncomment out enabled flag .
285 CURSOR cn_sca_rule_attributes IS
286 SELECT *
287 FROM cn_sca_rule_attributes csra
288 WHERE transaction_source = x_transaction_source
289 -- codeCheck: This condition is not required when inner has it.
290 AND org_id = g_org_id
291 -- AND enabled_flag = 'Y'
292 AND EXISTS (SELECT 'S'
293 FROM cn_sca_conditions csc
294 WHERE csc.sca_rule_attribute_id = csra.sca_rule_attribute_id
295 AND csc.org_id = g_org_id) ;
296
297 CURSOR cn_operators (a_sca_rule_attribute_id NUMBER) IS
298 SELECT lookup_code,meaning
299 FROM cn_lookups cl
300 WHERE lookup_type = 'SCA_OPERATORS'
301 AND EXISTS (SELECT 'x'
302 FROM cn_sca_conditions csc ,
303 cn_sca_cond_details cscd
304 WHERE csc.sca_condition_id = cscd.sca_condition_id
305 AND csc.sca_rule_attribute_id = a_sca_rule_attribute_id
306 AND csc.org_id = g_org_id
307 AND cscd.OPERATOR_ID = cl.lookup_code);
308
309
310 BEGIN
311 procedure_name := 'get_winning_rule';
312 procedure_desc := 'This procedure is to get matching rules.';
313 parameter_list := 'x_sca_batch_id IN NUMBER,' ||
314 'p_org_id IN NUMBER,'||
315 'x_return_status OUT NOCOPY VARCHAR2,'||
316 'x_msg_count OUT NOCOPY NUMBER,' ||
317 'x_msg_data OUT NOCOPY VARCHAR2';
318
319 proc_init(procedure_name, procedure_desc, parameter_list,'P', 'NUMBER' ,
320 package_spec_id, x_repository_id,spec_code, body_code);
321
322
323 cn_utils.appendcr(body_code, ' l_stmt VARCHAR2(32000); ');
324 cn_utils.appendcr(body_code, ' BEGIN ');
325 cn_utils.appendcr(body_code, ' x_return_status := FND_API.G_RET_STS_SUCCESS;');
326 cn_utils.appendcr(body_code, ' l_stmt := ');
327 cn_utils.appendcr(body_code, '''INSERT INTO cn_sca_winning_rules_gtt (sca_batch_id , ''||');
328 cn_utils.appendcr(body_code, ''' sca_credit_rule_id, ''|| ');
329 cn_utils.appendcr(body_code, ''' sca_headers_interface_id, ''|| ');
330 cn_utils.appendcr(body_code, ''' calculated_rank) ''|| ');
331 /*
332 cn_utils.appendcr(body_code, 'SELECT sca_batch_id, ');
333 cn_utils.appendcr(body_code, ' matching_rules.sca_credit_rule_id, ');
334 cn_utils.appendcr(body_code, ' sca_headers_interface_id, ');
335 cn_utils.appendcr(body_code, ' rule.calculated_rank ');
336 cn_utils.appendcr(body_code, ' FROM cn_sca_denorm_rules_all rule, ( ');
337 */
338 cn_utils.appendcr(body_code, ''' SELECT sca_batch_id , ''||');
339 cn_utils.appendcr(body_code, ''' sca_credit_rule_id , ''||');
340 cn_utils.appendcr(body_code, ''' sca_headers_interface_id, ''||');
341 cn_utils.appendcr(body_code, ''' calculated_rank ''||');
342 cn_utils.appendcr(body_code, ''' FROM ( ''||');
343 cn_utils.appendcr(body_code, ''' SELECT ''||x_sca_batch_id||'' sca_batch_id, ''||');
344 cn_utils.appendcr(body_code, ''' result.sca_credit_rule_id , ''||');
345 cn_utils.appendcr(body_code, ''' result.sca_headers_interface_id, ''||');
346 cn_utils.appendcr(body_code, ''' result.calculated_rank , ''||');
347 cn_utils.appendcr(body_code, ''' rank() over (partition by result.sca_headers_interface_id ''||');
348 cn_utils.appendcr(body_code, ''' order by result.calculated_rank desc,result.sca_credit_rule_id asc) as rule_rank ''||');
349 cn_utils.appendcr(body_code, ''' FROM ( ''||');
350 cn_utils.appendcr(body_code, ''' SELECT cshi.sca_headers_interface_id, ''||');
351 cn_utils.appendcr(body_code, ''' b.sca_credit_rule_id, ''||');
352 cn_utils.appendcr(body_code, ''' b.calculated_rank ''||');
356 cn_utils.appendcr(body_code, ''' FROM cn_sca_denorm_rules_all ''||');
353 cn_utils.appendcr(body_code, ''' FROM ''||');
354 cn_utils.appendcr(body_code, ''' (SELECT sca_credit_rule_id, ''||');
355 cn_utils.appendcr(body_code, ''' start_date,end_date ''||');
357 cn_utils.appendcr(body_code, ''' WHERE sca_credit_rule_id = ancestor_rule_id ''||');
358 cn_utils.appendcr(body_code, ''' AND org_id = ''||p_org_id||'' ''||');
359 cn_utils.appendcr(body_code, ''' AND transaction_source = '''''||x_transaction_source||''''') a, ''||');
360 cn_utils.appendcr(body_code, ''' cn_sca_rule_cond_vals_mv b, ''||');
361 cn_utils.appendcr(body_code, ''' cn_sca_headers_interface_gtt cshi ''|| ');
362 cn_utils.appendcr(body_code, ''' WHERE a.sca_credit_rule_id = ''||');
363 cn_utils.appendcr(body_code, ''' b.sca_credit_rule_id ''||');
364 cn_utils.appendcr(body_code, ''' AND cshi.sca_batch_id = ''||x_sca_batch_id ||'' ''||');
365 cn_utils.appendcr(body_code, ''' AND cshi.TRANSACTION_SOURCE = '''''||x_transaction_source||''''' ''||');
366 cn_utils.appendcr(body_code, ''' AND cshi.processed_date ''||');
367 cn_utils.appendcr(body_code, ''' BETWEEN a.start_date AND NVL(a.end_date,cshi.processed_date) ''||');
368 cn_utils.appendcr(body_code, ''' AND ( ''||');
369
370 -- loop for all attributes and generate the necessary or condition.
371 FOR attributes in cn_sca_rule_attributes LOOP
372 IF l_attrib_counter > 1 THEN
373 cn_utils.appendcr(body_code, ''' OR ''||');
374 END IF;
375
376 cn_utils.appendcr(body_code, ' '' ( b.sca_rule_attribute_id ='||attributes.SCA_RULE_ATTRIBUTE_ID||'''||');
377 cn_utils.appendcr(body_code,' '' AND ( ''||');
378 l_rule_counter := 1;
379 -- For each attribute loop thru the operators and build the
380 -- the operator or conditions.
381 FOR operator in cn_operators(attributes.SCA_RULE_ATTRIBUTE_ID) LOOP
382 IF l_rule_counter > 1 THEN
383 cn_utils.appendcr(body_code, ''' OR ''||');
384 END IF;
385 IF operator.lookup_code = 'EQUAL' THEN
386 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
387 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
388 cn_utils.appendcr(body_code, ' '' = b.VALUE_CHAR_MIN AND ''||');
389 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
390 cn_utils.appendcr(body_code, ' '' = b.VALUE_NUM_MIN AND ''||');
391 ELSIF attributes.DATATYPE = 'DATE' THEN
392 cn_utils.appendcr(body_code, ' '' = b.VALUE_DATE_MIN AND ''||');
393 END IF;
394 cn_utils.appendcr(body_code, ''' b.operator_id = ''''EQUAL'''') ''||');
395 ELSIF operator.lookup_code = 'LIKE' THEN
396 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
397 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
398 cn_utils.appendcr(body_code, ' '' LIKE b.VALUE_CHAR_MIN AND ''||');
399 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
400 cn_utils.appendcr(body_code, ' '' LIKE b.VALUE_NUM_MIN AND ''||');
401 ELSIF attributes.DATATYPE = 'DATE' THEN
402 cn_utils.appendcr(body_code, ' '' LIKE b.VALUE_DATE_MIN AND ''||');
403 END IF;
404 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''LIKE'''') ''||');
405 ELSIF operator.lookup_code = 'BETWEEN' THEN
406 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
407 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
408 cn_utils.appendcr(body_code, ' '' BETWEEN b.VALUE_CHAR_MIN AND b.VALUE_CHAR_MAX AND ''||');
409 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
410 cn_utils.appendcr(body_code, ' '' BETWEEN b.VALUE_NUM_MIN AND b.VALUE_NUM_MAX AND ''||');
411 ELSIF attributes.DATATYPE = 'DATE' THEN
412 cn_utils.appendcr(body_code, ' '' BETWEEN b.VALUE_DATE_MIN AND b.VALUE_DATE_MAX AND ''||');
413 END IF;
414 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''BETWEEN'''') ''||');
415 ELSIF operator.lookup_code = 'GRE' THEN
416 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
417 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
418 cn_utils.appendcr(body_code, ' '' >= b.VALUE_CHAR_MIN AND ''||');
422 cn_utils.appendcr(body_code, ' '' >= b.VALUE_DATE_MIN AND ''||');
419 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
420 cn_utils.appendcr(body_code, ' '' >= b.VALUE_NUM_MIN AND ''||');
421 ELSIF attributes.DATATYPE = 'DATE' THEN
423 END IF;
424 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''GRE'''') ''||');
425 ELSIF operator.lookup_code = 'GT' THEN
426 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
427 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
428 cn_utils.appendcr(body_code, ' '' > b.VALUE_CHAR_MIN AND ''||');
429 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
430 cn_utils.appendcr(body_code, ' '' > b.VALUE_NUM_MIN AND ''||');
431 ELSIF attributes.DATATYPE = 'DATE' THEN
432 cn_utils.appendcr(body_code, ' '' > b.VALUE_DATE_MIN AND ''||');
433 END IF;
434 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''GT'''') ''||');
435 ELSIF operator.lookup_code = 'LT' THEN
436 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
437 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
438 cn_utils.appendcr(body_code, ' '' < b.VALUE_CHAR_MIN AND ''||');
439 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
440 cn_utils.appendcr(body_code, ' '' < b.VALUE_NUM_MIN AND ''||');
441 ELSIF attributes.DATATYPE = 'DATE' THEN
442 cn_utils.appendcr(body_code, ' '' < b.VALUE_DATE_MIN AND ''||');
443 END IF;
444 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''LT'''') ''||');
445 ELSIF operator.lookup_code = 'LTE' THEN
446 cn_utils.appendcr(body_code, ' '' (cshi.'||attributes.SRC_COLUMN_NAME||'''||');
447 IF attributes.DATATYPE = 'ALPHANUMERIC' THEN
448 cn_utils.appendcr(body_code, ' '' <= b.VALUE_CHAR_MIN AND ''||');
449 ELSIF attributes.DATATYPE = 'NUMERIC' THEN
450 cn_utils.appendcr(body_code, ' '' <= b.VALUE_NUM_MIN AND ''||');
451 ELSIF attributes.DATATYPE = 'DATE' THEN
452 cn_utils.appendcr(body_code, ' '' <= b.VALUE_DATE_MIN AND ''||');
453 END IF;
454 cn_utils.appendcr(body_code, ' '' b.operator_id = ''''LTE'''') ''||');
455 END IF;
456 l_rule_counter := l_rule_counter +1;
457 END LOOP;
458
459 cn_utils.appendcr(body_code, ' '' )''||');
460 cn_utils.appendcr(body_code, ' '' )''||');
461 l_attrib_counter := l_attrib_counter+1;
462 END LOOP;
463
464 IF l_attrib_counter = 1 THEN
465 cn_utils.appendcr(body_code, ''' (1 = 1)''||');
466 END IF;
467
468 cn_utils.appendcr(body_code, ''' )) result''||');
469 cn_utils.appendcr(body_code, ''' GROUP BY result.sca_headers_interface_id,result.sca_credit_rule_id, ''||');
470 cn_utils.appendcr(body_code, ''' result.calculated_rank ''||');
471 cn_utils.appendcr(body_code, ''' HAVING (count(1)) >= ( ''||');
472 cn_utils.appendcr(body_code, ''' SELECT r.num_rule_attributes ''||');
473 cn_utils.appendcr(body_code, ''' FROM cn_sca_denorm_rules r ''||');
474 cn_utils.appendcr(body_code, ''' WHERE r.sca_credit_rule_id = result.sca_credit_rule_id ''||');
475 cn_utils.appendcr(body_code, ''' AND r.ancestor_rule_id = result.sca_credit_rule_id ''||');
476 cn_utils.appendcr(body_code, ''' AND r.transaction_source = '''''||x_transaction_source||''''') ''||');
477 cn_utils.appendcr(body_code, ''') result1 where rule_rank = 1'';');
478
479
480 cn_utils.appendcr(body_code, ' EXECUTE IMMEDIATE l_stmt;');
481
482 cn_utils.appindcr(body_code, ' EXCEPTION WHEN OTHERS THEN ');
483 cn_utils.appindcr(body_code, ' x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ; ');
484 cn_utils.appindcr(body_code, ' IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)');
485 cn_utils.appindcr(body_code, ' THEN ');
486 cn_utils.appindcr(body_code, ' FND_MSG_PUB.Add_Exc_Msg( '''||g_package_name||''' ,''get_winning_rule'');');
487 cn_utils.appindcr(body_code, ' END IF; ');
488 cn_utils.appindcr(body_code, ' FND_MSG_PUB.Count_And_Get ');
489 cn_utils.appindcr(body_code, ' (p_count => x_msg_count , ');
490 cn_utils.appindcr(body_code, ' p_data => x_msg_data , ');
491 cn_utils.appindcr(body_code, ' p_encoded => FND_API.G_FALSE ');
495
492 cn_utils.appindcr(body_code, ' ); ');
493 cn_utils.appindcr(body_code, 'cn_message_pkg.debug(''EXCEPTION IN get_winning_rule, '' || sqlerrm);');
494 cn_utils.proc_end( procedure_name, 'N', body_code );
496 EXCEPTION when others then
497 cn_message_pkg.debug('IN get_perf exception handler, error is '||sqlcode||' '||sqlerrm);
498 RAISE;
499
500 END get_winning_rule;
501
502
503
504 FUNCTION create_sca_rules_online_dyn
505 (x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE)
506 RETURN BOOLEAN IS
507 package_name cn_obj_packages_v.name%TYPE;
508 package_type cn_obj_packages_v.package_type%TYPE := 'FML';
509 package_spec_id cn_obj_packages_v.package_id%TYPE;
510 package_body_id cn_obj_packages_v.package_id%TYPE;
511 package_spec_desc cn_obj_packages_v.description%TYPE;
512 package_body_desc cn_obj_packages_v.description%TYPE;
513 spec_code cn_utils.code_type;
514 body_code cn_utils.code_type;
515 dummy NUMBER(7);
516 l_module_id number(15);
517 l_repository_id cn_repositories.repository_id%TYPE;
518 l_org_id NUMBER;
519 BEGIN
520
521 --SELECT repository_id, org_id
522 --INTO l_repository_id, l_org_id FROM cn_repositories;
523
524 cn_utils.set_org_id(g_org_id);
525
526 SELECT repository_id INTO l_repository_id FROM cn_repositories;
527
528 package_name := 'cn_sca_rodyn_'|| substr(lower(x_transaction_source),1,8) || '_' || abs(g_org_id) || '_pkg';
529 g_package_name := package_name;
530
531
532 check_create_object(package_name, 'PKS', package_spec_id, l_repository_id);
533 check_create_object(package_name, 'PKB', package_body_id, l_repository_id);
534
535
536 cn_utils.pkg_init(l_module_id, package_name, null, package_type, 'FORMULA',
537 package_spec_id, package_body_id, package_spec_desc,
538 package_body_desc, spec_code, body_code);
539 get_winning_rule(spec_code, body_code,x_transaction_source);
540 cn_utils.pkg_end(package_name, spec_code, body_code);
541
542 cn_utils.unset_org_id;
543
544 RETURN TRUE;
545 END;
546
547 PROCEDURE gen_sca_rules_onln_dyn
548 ( p_api_version IN NUMBER,
549 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
550 p_commit IN VARCHAR2 := FND_API.G_FALSE,
551 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
552 p_org_id IN NUMBER,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_msg_count OUT NOCOPY NUMBER,
555 x_msg_data OUT NOCOPY VARCHAR2,
556 x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE
557 ) IS
558
559 l_api_name CONSTANT VARCHAR2(30) := 'gen_sca_rules_onln_dyn';
560 l_api_version CONSTANT NUMBER :=1.0;
561 l_creation_status BOOLEAN;
562 l_request_id NUMBER;
563 l_file_name VARCHAR2(200);
564 l_call_status BOOLEAN;
565 l_dummy VARCHAR2(500);
566 l_dev_phase VARCHAR2(80);
567 l_dev_status VARCHAR2(80) := 'INCOMPLETE';
568 l_status BOOLEAN;
569 sqlstring dbms_sql.varchar2s;
570 empty_sqlstring dbms_sql.varchar2s;
571 cursor1 INTEGER;
572 i INTEGER;
573 j INTEGER;
574 new_line_flag BOOLEAN:=TRUE;
575 retval NUMBER;
576 l_pkg_object_id NUMBER(15);
577 l_error_count NUMBER;
578 l_pkg_name VARCHAR2(100);
579 l_org_id NUMBER;
580
581 BEGIN
582 -- Standard call to check for call compatibility.
583 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
584 p_api_version ,
585 l_api_name ,
586 G_PKG_NAME )
587 THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590
591 -- Initialize message list if p_init_msg_list is set to TRUE.
592 IF FND_API.to_Boolean( p_init_msg_list ) THEN
593 FND_MSG_PUB.initialize;
594 END IF;
595
596 -- Initialize API return status to success
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598
599 -- Codes start here
600
601 g_org_id := p_org_id;
602
603 -- SELECT org_id INTO l_org_id FROM cn_repositories;
604
605 l_pkg_name := 'cn_sca_rodyn_'|| substr(lower(x_transaction_source),1,8) || '_' || abs(g_org_id) || '_pkg';
606
607 l_status := create_sca_rules_online_dyn(x_transaction_source);
608
609
610 IF l_status THEN /* created successfully. Continue to install it. */
611 SELECT co.object_id
612 INTO l_pkg_object_id
613 FROM cn_objects co
614 WHERE co.name = l_pkg_name
615 AND co.object_type = 'PKS'
616 AND co.org_id = g_org_id;
617
618 SELECT cs.text bulk collect INTO sqlstring
619 FROM cn_source cs
620 WHERE cs.object_id = l_pkg_object_id
621 AND cs.org_id = g_org_id
622 ORDER BY cs.line_no ;
623
624
625
626 i:=1;
627 j:= sqlstring.count;
628
629 cursor1:=DBMS_SQL.OPEN_CURSOR;
630 DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7);
631 retval:=DBMS_SQL.EXECUTE(cursor1);
632 DBMS_SQL.CLOSE_CURSOR(cursor1);
633
634 sqlstring := empty_sqlstring;
635
636 SELECT co.object_id
637 INTO l_pkg_object_id
638 FROM cn_objects co
639 WHERE co.name = l_pkg_name --'cn_formula_'|| p_formula_id || '_pkg'
640 AND co.object_type = 'PKB'
641 AND co.org_id = g_org_id;
642
643
644
645 SELECT cs.text bulk collect INTO sqlstring
646 FROM cn_source cs
647 WHERE cs.object_id = l_pkg_object_id
648 AND cs.org_id = g_org_id
649 ORDER BY cs.line_no ;
650
651 i:= 1;
652 j:= sqlstring.count;
653
654 cursor1:=DBMS_SQL.OPEN_CURSOR;
655 DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7);
656 retval:=DBMS_SQL.EXECUTE(cursor1);
657 DBMS_SQL.CLOSE_CURSOR(cursor1);
658
659 cn_message_pkg.debug('The rule dynamic package is created successfully. Continue to intall the package. ');
660 fnd_file.put_line(fnd_file.Log, 'The rule dynamic package is created successfully. Continue to intall the package.');
661
662 -- check whether package is installed successfully
663 SELECT COUNT(*)
664 INTO l_error_count
665 FROM user_errors
666 WHERE name = upper(l_pkg_name)
667 AND TYPE IN ('PACKAGE', 'PACKAGE BODY');
668
669 IF l_error_count = 0 THEN
670 NULL;
671 ELSE
672 x_return_status := FND_API.g_ret_sts_error;
673
674 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
675 THEN
676 fnd_message.set_name('CN', 'CN_DYN_PKG_COMPILE_ERR');
677 FND_MSG_PUB.ADD;
678 FND_MSG_PUB.Count_And_Get
679 (p_count => x_msg_count ,
680 p_data => x_msg_data ,
681 p_encoded => FND_API.G_FALSE
682 );
683
684 END IF;
685 END IF;
686 ELSE
687 x_return_status := FND_API.g_ret_sts_error;
688
689 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
690 THEN
691 fnd_message.set_name('CN', 'CN_DYN_PKG_COMPILE_ERR');
692 FND_MSG_PUB.ADD;
693 FND_MSG_PUB.Count_And_Get
694 (p_count => x_msg_count ,
695 p_data => x_msg_data ,
696 p_encoded => FND_API.G_FALSE
697 );
698 END IF;
699 END IF;
700
701 IF FND_API.To_Boolean( p_commit ) THEN
702 COMMIT WORK;
703 END IF;
704
705 EXCEPTION
706 WHEN FND_API.G_EXC_ERROR THEN
707
708 x_return_status := FND_API.G_RET_STS_ERROR ;
709 FND_MSG_PUB.Count_And_Get
710 (p_count => x_msg_count ,
711 p_data => x_msg_data ,
712 p_encoded => FND_API.G_FALSE
713 );
714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
716 FND_MSG_PUB.Count_And_Get
717 (p_count => x_msg_count ,
718 p_data => x_msg_data ,
719 p_encoded => FND_API.G_FALSE
720 );
721 WHEN OTHERS THEN
722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
723 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
724 THEN
725 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
726 END IF;
727 FND_MSG_PUB.Count_And_Get
728 (p_count => x_msg_count ,
729 p_data => x_msg_data ,
730 p_encoded => FND_API.G_FALSE
731 );
732
733 END gen_sca_rules_onln_dyn;
734 END cn_sca_rules_online_gen_pvt;