[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_RULES_BATCH_GEN_PVT
Source
1 PACKAGE BODY CN_SCA_RULES_BATCH_GEN_PVT AS
2 -- $Header: cnvscabb.pls 120.10 2011/08/11 08:14:05 srguntur ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_SCA_RULES_BATCH_GEN_PVT
11 -- Purpose
12 -- This package is a public API for processing Credit Rules and associated
13 -- allocation percentages.
14 -- History
15 -- 06/26/03 Rao.Chenna Created
16 --
17 -- Nov 17, 2005 vensrini Added org_id checks to populate_matches proc
18
19
20
21
22
23 --
24 -- Global Variables
25 g_package_name cn_obj_packages_v.name%TYPE;
26 g_org_id cn_sca_credit_rules.org_id%TYPE;
27 --
28 PROCEDURE get_min ( p_min IN OUT NOCOPY number, p_max number) IS
29 BEGIN
30 IF p_min =0 and p_max <> 0 THEN
31 p_min := p_max;
32 ELSIF p_min <> 0 and p_max <> 0 THEN
33 IF p_min > p_max THEN
34 p_min := p_max;
35 END IF;
36 END IF;
37 END;
38
39 -- search the next occurence of delimiter '+ - * / ( ) '
40 -- in sql_select portion and return the position
41 FUNCTION search_delimiter_select(
42 p_input_str varchar2,
43 p_start number)
44 RETURN number IS
45 l_position_min NUMBER ;
46 l_position NUMBER;
47 BEGIN
48 l_position_min := instr( p_input_str, '*', p_start) ;
49 l_position := instr(p_input_str, '-', p_start);
50 get_min(l_position_min, l_position);
51
52 l_position := instr(p_input_str, '+', p_start);
53 get_min(l_position_min, l_position);
54
55 l_position := instr(p_input_str, '/', p_start);
56 get_min(l_position_min, l_position);
57
58 l_position := instr(p_input_str, '(', p_start);
59 get_min(l_position_min, l_position);
60
61 l_position := instr(p_input_str, ')', p_start);
62 get_min(l_position_min, l_position);
63
64 l_position := instr(p_input_str, ',', p_start);
65 get_min(l_position_min, l_position);
66
67 return l_position_min;
68 END;
69
70
71 -- search the next occurence of delimiter ', ' in sql_from portion and return the position
72 FUNCTION search_delimiter_from ( p_input_str varchar2, p_start number) RETURN
73 NUMBER IS
74 l_position_min number :=0;
75 l_position NUMBER;
76 BEGIN
77 l_position := instr(p_input_str, ',', p_start);
78 get_min(l_position_min, l_position);
79 return l_position_min;
80 END;
81
82 -- search the next occurence of delimiter 'and ' in sql_where portion and return the position
83 FUNCTION search_delimiter_where ( p_input_str varchar2, p_start number)
84 RETURN number IS
85 l_position_min number :=0;
86 l_position NUMBER;
87 BEGIN
88 l_position := instr(p_input_str, 'and', p_start);
89 get_min(l_position_min, l_position);
90 return l_position_min;
91 END;
92
93 -- search the next occurence of delimiter empty space in COMMENT and return the position
94 FUNCTION search_delimiter_comment ( p_input_str varchar2, p_start number)
95 RETURN number IS
96 l_position_min number :=0;
97 l_position NUMBER;
98 BEGIN
99 l_position := instr(p_input_str, ' ', p_start);
100 get_min(l_position_min, l_position);
101 return l_position_min;
102 END search_delimiter_comment ;
103
104
108 sql_type VARCHAR2 )
105 -- split the long sql statement into pieces less than 80 characters and return the position
106 PROCEDURE split_long_sql ( body_code IN OUT NOCOPY cn_utils.code_type,
107 p_input_str VARCHAR2 ,
109 IS
110 l_length NUMBER; /* total length of input string */
111 l_start NUMBER; /* the start position of current split */
112 l_next NUMBER; /* position of next delimiter */
113 l_next_prev NUMBER; /* position of previous delimiter */
114 l_limit NUMBER; /* the upper boundary of current split */
115
116 l_sql_segment_length NUMBER := 80;
117 BEGIN
118
119 DBMS_APPLICATION_INFO.SET_ACTION('inside SPLIT LONG ' );
120 l_start := 1;
121 l_limit := l_start + l_sql_segment_length;
122
123 l_length := Length(p_input_str);
124 l_next := l_start;
125 l_next_prev := l_start;
126
127 WHILE l_limit < l_length LOOP
128 WHILE l_next < l_limit LOOP
129
130 /* the postion of l_next delimiter is not beyong the upper boudaryyet */
131 l_next_prev := l_next;
132
133 IF sql_type = 'SELECT' THEN
134 l_next := search_delimiter_select(p_input_str, l_next_prev+1 );
135 ELSIF sql_type = 'FROM' THEN
136 l_next := NVL(search_delimiter_from(p_input_str, l_next_prev+1 ),0);
137 ELSIF sql_type = 'WHERE' THEN
138 l_next := search_delimiter_where(p_input_str, l_next_prev+1 );
139 ELSIF sql_type = 'COMMENT' THEN
140 l_next := search_delimiter_comment(p_input_str, l_next_prev+1 );
141 END IF;
142
143 IF l_next = 0 THEN /* no more delimiter */
144 EXIT;
145 END IF;
146
147 IF l_next >= l_limit THEN
148 l_next_prev := l_next;
149 END IF;
150
151
152 END LOOP;
153
154 IF sql_type = 'COMMENT' THEN
155 cn_utils.appindcr(body_code, '-- ' || substr(p_input_str, l_start,
156 l_next_prev - l_start) );
157 ELSE
158 cn_utils.appindcr(body_code, substr(p_input_str, l_start,
159 l_next_prev - l_start));
160 END IF;
161
162 l_start := l_next_prev ;
163 l_limit := l_start + l_sql_segment_length;
164
165 IF l_next = 0 THEN /* no more delimiter */
166 EXIT;
167 END IF;
168 END LOOP;
169
170 IF sql_type = 'COMMENT' THEN
171 cn_utils.appindcr(body_code, '--' || substr(p_input_str, l_start,
172 l_length - l_start + 1));
173 ELSE
174 cn_utils.appindcr(body_code, substr(p_input_str, l_start,
175 l_length - l_start + 1));
176 END IF;
177 END split_long_sql;
178
179
180
181 PROCEDURE check_create_object(
182 x_name cn_objects.name%TYPE,
183 x_object_type cn_objects.object_type%TYPE,
184 x_object_id IN OUT NOCOPY cn_objects.object_id%TYPE,
185 x_repository_id cn_repositories.repository_id%TYPE) IS
186 --
187 dummy NUMBER;
188 x_rowid ROWID;
189 --
190 BEGIN
191 -- Check whether this package exists in the cn_objects or not.
192 SELECT COUNT(1)
193 INTO dummy
194 FROM cn_objects
195 WHERE name = x_name
196 AND object_type = x_object_type;
197 --
198 IF (dummy = 0) THEN
199 --
200 x_object_id := cn_utils.get_object_id;
201 cn_objects_pkg.insert_row( x_rowid => x_rowid,
202 x_object_id => x_object_id,
203 x_org_id => g_org_id,
204 x_dependency_map_complete => 'N',
205 x_name => x_name,
206 x_description => null,
207 x_object_type => x_object_type,
208 x_repository_id => X_repository_id,
209 x_next_synchronization_date => null,
210 x_synchronization_frequency => null,
211 x_object_status => 'A',
212 x_object_value => NULL );
213 --
214 ELSIF (dummy = 1) THEN
215 SELECT object_id
216 INTO x_object_id
217 FROM cn_objects
218 WHERE name = x_name
219 AND object_type = x_object_type;
220 END IF;
221 --
222 EXCEPTION WHEN OTHERS THEN
223 cn_message_pkg.debug('IN check_create_object Exception handler name is '||
224 x_name ||' object_type is '|| x_object_type ||
225 ' object_id is '|| x_object_id );
226 RAISE;
227 END check_create_object;
228 --
229 PROCEDURE pkg_init_boilerplate (
230 code IN OUT NOCOPY cn_utils.code_type,
231 package_name IN cn_obj_packages_v.name%TYPE,
232 description IN cn_obj_packages_v.description%TYPE,
233 object_type IN VARCHAR2)
234 IS
235 x_userid VARCHAR2(20);
236 BEGIN
237 SELECT user
238 INTO x_userid
239 FROM sys.dual;
240
241 cn_utils.appendcr(code, '--+============================================================================+');
242 cn_utils.appendcr(code, '-- Copyright (c) 1993 Oracle Corporation');
243 cn_utils.appendcr(code, '-- Redwood Shores, California, USA');
244 cn_utils.appendcr(code, '-- All rights reserved.');
248 cn_utils.appendcr(code, '-- Purpose');
245 cn_utils.appendcr(code, '--+============================================================================+');
246 cn_utils.appendcr(code, '-- Package Name');
247 cn_utils.appendcr(code, '-- '||package_name);
249 cn_utils.appendcr(code, '-- '||description);
250 cn_utils.appendcr(code, '-- History');
251 cn_utils.appendcr(code, '-- '||SYSDATE||' '||x_userid ||' Created');
252 cn_utils.appendcr(code, '--+============================================================================+');
253
254 ----+
255 -- Check For Package type, based on PKS(spec) or PKB(body) generate init section
256 -- Of your code accordingly
257 ----+
258 IF (object_type = 'PKS')
259 THEN
260 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' ||package_name||' AS');
261 ELSE
262 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' ||package_name||' AS');
263 END IF;
264
265 cn_utils.appendcr(code);
266
267 END pkg_init_boilerplate;
268 --
269 PROCEDURE pkg_init (
270 module_id cn_modules.module_id%TYPE,
271 package_name cn_obj_packages_v.name%TYPE,
272 package_spec_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
273 package_body_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
274 package_spec_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
275 package_body_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
276 spec_code IN OUT NOCOPY cn_utils.code_type,
277 body_code IN OUT NOCOPY cn_utils.code_type) IS
278
279 x_rowid ROWID;
280 null_id NUMBER;
281
282 BEGIN
283 -- Find the package objects
284 cn_utils.find_object(package_name,'PKS',package_spec_id, package_spec_desc, g_org_id);
285 cn_utils.find_object(package_name,'PKB',package_body_id, package_body_desc, g_org_id);
286
287 -- Delete module source code from cn_source
288 -- Delete module object dependencies for this module
289 cn_utils.delete_module(module_id, package_spec_id, package_body_id, g_org_id);
290
291 cn_utils.init_code (package_spec_id, spec_code);
292 cn_utils.init_code (package_body_id, body_code);
293
294 pkg_init_boilerplate(spec_code, package_name, package_spec_desc, 'PKS');
295 pkg_init_boilerplate(body_code, package_name, package_body_desc, 'PKB');
296
297 cn_utils.indent(spec_code, 1);
298 cn_utils.indent(body_code, 1);
299
300 END pkg_init;
301 --
302 -- initialize the procedure boilerplate
303 PROCEDURE proc_init_boilerplate (code IN OUT NOCOPY cn_utils.code_type,
304 procedure_name cn_obj_procedures_v.name%TYPE,
305 description cn_obj_procedures_v.description%TYPE)
306 IS
307 X_userid VARCHAR2(20);
308 BEGIN
309 SELECT user INTO X_userid FROM sys.dual;
310
311 cn_utils.appendcr(code, '--');
312 cn_utils.appendcr(code, '-- Procedure Name');
313 cn_utils.appendcr(code, '-- ' || procedure_name);
314 cn_utils.appendcr(code, '-- Purpose');
315 split_long_sql(code, description, 'COMMENT');
316 cn_utils.appendcr(code, '-- History');
317 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
318 cn_utils.appendcr(code, '--');
319 END proc_init_boilerplate;
320
321 -- initialize the procedure
322 PROCEDURE proc_init(procedure_name cn_obj_procedures_v.name%TYPE,
323 description cn_obj_procedures_v.description%TYPE,
324 parameter_list cn_obj_procedures_v.parameter_list%TYPE,
325 procedure_type cn_obj_procedures_v.procedure_type%TYPE,
326 return_type cn_obj_procedures_v.return_type%TYPE,
327 package_id cn_obj_procedures_v.package_id%TYPE,
328 repository_id cn_obj_procedures_v.repository_id%TYPE,
329 spec_code IN OUT NOCOPY cn_utils.code_type,
330 body_code IN OUT NOCOPY cn_utils.code_type) IS
331 BEGIN
332 -- Generate boilerplate comments
333 proc_init_boilerplate(spec_code, procedure_name, description);
334 proc_init_boilerplate(body_code, procedure_name, description);
335
336 -- Generate procedure header and parameters in both spec and body
337 IF (procedure_type = 'P') THEN
338 IF (parameter_list IS NOT NULL) THEN
339 split_long_sql(spec_code, 'PROCEDURE ' || procedure_name ||
340 ' (' || parameter_list || ')', 'FROM');
341 split_long_sql(body_code, 'PROCEDURE ' || procedure_name ||
342 ' (' || parameter_list || ')', 'FROM');
343 ELSE
344 cn_utils.appendcr(spec_code, 'PROCEDURE ' || procedure_name);
345 cn_utils.appendcr(body_code, 'PROCEDURE ' || procedure_name);
346 END IF;
347 ELSIF (procedure_type = 'F') THEN
348 IF (parameter_list IS NOT NULL) THEN
349 split_long_sql(spec_code, 'FUNCTION ' || procedure_name ||
350 ' (' || parameter_list || ')', 'FROM');
351 split_long_sql(body_code, 'FUNCTION ' || procedure_name ||
352 ' (' || parameter_list || ')', 'FROM');
353 ELSE
354 cn_utils.appendcr(spec_code, 'FUNCTION ' || procedure_name);
355 cn_utils.appendcr(body_code, 'FUNCTION ' || procedure_name);
356 END IF;
357 END IF;
358
359 IF (procedure_type = 'F') THEN
360 cn_utils.appendcr(spec_code, ' RETURN ' || return_type);
361 cn_utils.appendcr(body_code, ' RETURN ' || return_type);
362 END IF;
363
364 cn_utils.appendcr(spec_code, ';');
365 cn_utils.appendcr(spec_code);
366 cn_utils.appendcr(body_code, ' IS');
367 END proc_init;
371 PROCEDURE populate_matches (
368
369
370 -- create the code of the procedure get_winning rule
372 spec_code IN OUT NOCOPY cn_utils.code_type,
373 body_code IN OUT NOCOPY cn_utils.code_type ,
374 x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE) IS
375 --
376 procedure_name cn_obj_procedures_v.name%TYPE;
377 procedure_desc cn_obj_procedures_v.description%TYPE;
378 parameter_list cn_obj_procedures_v.parameter_list%TYPE;
379 package_spec_id cn_obj_packages_v.package_id%TYPE;
380 x_repository_id cn_repositories.repository_id%TYPE;
381 l_attr_counter NUMBER := 1;
382 l_rule_counter NUMBER := 1;
383 l_operator_counter NUMBER := 1;
384 l_comb_counter NUMBER := 1;
385 l_if_counter NUMBER := 1;
386 l_row_num NUMBER := 0;
387 --
388 -- Cursors Section
389 CURSOR comb_cur IS
390 SELECT distinct rule_attr_comb_value
391 FROM cn_sca_combinations sc
392 WHERE transaction_source = x_transaction_source
393 AND org_id = g_org_id; -- vensrini
394 --
395 CURSOR rule_attr_cur(l_comb_value NUMBER) IS
396 SELECT ra.sca_rule_attribute_id,
397 ra.src_column_name,
398 ra.datatype
399 FROM cn_sca_combinations sc,
400 cn_sca_rule_attributes ra
401 WHERE sc.sca_rule_attribute_id = ra.sca_rule_attribute_id
402 AND sc.org_id = ra.org_id --vensrini
403 AND sc.org_id = g_org_id -- vensrini
404 AND rule_attr_comb_value = l_comb_value
405 AND sc.transaction_source = x_transaction_source
406 order by sc.sca_rule_attribute_id;
407 --
408 CURSOR operator_cur(l_attribute_id NUMBER) IS
409 SELECT lookup_code,meaning
410 FROM cn_lookups cl
411 WHERE lookup_type = 'SCA_OPERATORS'
412 AND EXISTS (
413 SELECT 'S'
414 FROM cn_sca_conditions csc,
415 cn_sca_cond_details cscd
416 WHERE csc.sca_condition_id = cscd.sca_condition_id
417 AND csc.org_id = cscd.org_id -- vensrini
418 AND csc.org_id = g_org_id -- vensrini
419 AND cscd.OPERATOR_ID = cl.lookup_code
420 AND csc.sca_rule_attribute_id = l_attribute_id);
421 --
422 BEGIN
423 procedure_name := 'populate_matches';
424 procedure_desc := 'This procedure is to get matching rules.';
425 parameter_list := 'p_start_date IN DATE,'||
426 'p_end_date IN DATE,'||
427 'p_start_id IN NUMBER,'||
428 'p_end_id IN NUMBER,'||
429 'p_physical_batch_id IN NUMBER,'||
430 'p_transaction_source IN VARCHAR2,'||
431 'p_org_id IN VARCHAR2,'||
432 'x_return_status OUT NOCOPY VARCHAR2,'||
433 'x_msg_count OUT NOCOPY NUMBER,' ||
434 'x_msg_data OUT NOCOPY VARCHAR2';
435 --
436 proc_init(procedure_name, procedure_desc, parameter_list,'P', 'NUMBER' ,
437 package_spec_id, x_repository_id,spec_code, body_code);
438 --
439 cn_utils.appendcr(body_code, '--+');
440 cn_utils.appendcr(body_code, '-- Variables Section');
441 cn_utils.appendcr(body_code, '--+');
442 cn_utils.appendcr(body_code, ' l_comb_counter NUMBER := 0;');
443 cn_utils.appendcr(body_code, ' l_comb_value_id NUMBER := 0;');
444 --cn_utils.appendcr(body_code, ' l_row_num NUMBER := 0;');
445 --cn_utils.appendcr(body_code, ' l_rule_attribute_id NUMBER := 0;');
446 cn_utils.appendcr(body_code, '--+');
447 cn_utils.appendcr(body_code, '-- PL/SQL Tables/Records Section');
448 cn_utils.appendcr(body_code, '--+');
449 cn_utils.appendcr(body_code, ' TYPE comb_value_tbl_type IS ');
450 cn_utils.appendcr(body_code, ' TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER; ');
451 cn_utils.appendcr(body_code, ' l_comb_value comb_value_tbl_type; ');
452
453 cn_utils.appendcr(body_code, '--+');
454 cn_utils.appendcr(body_code, '-- Cursor to get Rule Attribute Combinations');
455 cn_utils.appendcr(body_code, '--+');
456 cn_utils.appendcr(body_code, ' CURSOR comb_cur IS ');
457 cn_utils.appendcr(body_code, ' SELECT DISTINCT rule_attr_comb_value ');
458 cn_utils.appendcr(body_code, ' FROM cn_sca_combinations sc ');
459 cn_utils.appendcr(body_code, ' WHERE transaction_source = p_transaction_source');
460 cn_utils.appendcr(body_code, ' AND org_id = p_org_id;'); -- vensrini
461 --
462 cn_utils.appendcr(body_code, 'BEGIN ');
463 --
464 cn_utils.appendcr(body_code, ' --+');
465 cn_utils.appendcr(body_code, ' -- Populate Combinations into a PL/SQL Table');
466 cn_utils.appendcr(body_code, ' --+');
467 cn_utils.appendcr(body_code, ' FOR rec IN comb_cur ');
468 cn_utils.appendcr(body_code, ' LOOP ');
469 cn_utils.appendcr(body_code, ' l_comb_counter := l_comb_counter + 1; ');
470 cn_utils.appendcr(body_code, ' l_comb_value(l_comb_counter) := rec.rule_attr_comb_value; ');
471 cn_utils.appendcr(body_code, ' END LOOP; ');
472 cn_utils.appendcr(body_code, ' --+');
473 cn_utils.appendcr(body_code, ' -- For Each Rule Attribute Combination, execute the appropriate SQL');
474 cn_utils.appendcr(body_code, ' --+');
475 cn_utils.appendcr(body_code, ' FOR i IN 1..l_comb_value.COUNT ');
476 cn_utils.appendcr(body_code, ' LOOP');
477
478 FOR comb_rec IN comb_cur
479 LOOP
480 l_row_num := 0;
481 --
482 IF (l_if_counter = 1) THEN
483 cn_utils.appendcr(body_code, ' IF (l_comb_value(i)='||
484 comb_rec.rule_attr_comb_value||') THEN');
485 ELSE
486 cn_utils.appendcr(body_code, ' ELSIF (l_comb_value(i)='||
490 cn_utils.appendcr(body_code, 'l_comb_value_id := l_comb_value(i);');
487 comb_rec.rule_attr_comb_value||') THEN');
488 END IF;
489 --
491 cn_utils.appendcr(body_code, 'INSERT /*+ APPEND */ INTO cn_sca_matches(');
492 cn_utils.appendcr(body_code, ' sca_credit_rule_id,');
493 cn_utils.appendcr(body_code, ' sca_headers_interface_id,');
494 cn_utils.appendcr(body_code, ' process_date,');
495 cn_utils.appendcr(body_code, ' rank,');
496 cn_utils.appendcr(body_code, ' calculated_rank,');
497 cn_utils.appendcr(body_code, ' org_id,');
498 cn_utils.appendcr(body_code, ' created_by,');
499 cn_utils.appendcr(body_code, ' creation_date,');
500 cn_utils.appendcr(body_code, ' last_updated_by,');
501 cn_utils.appendcr(body_code, ' last_update_date,');
502 cn_utils.appendcr(body_code, ' last_update_login)');
503 cn_utils.appendcr(body_code, 'SELECT result.sca_credit_rule_id,');
504 cn_utils.appendcr(body_code, ' result.sca_headers_interface_id,');
505 cn_utils.appendcr(body_code, ' result.processed_date,');
506 cn_utils.appendcr(body_code, ' result.rank,');
507 cn_utils.appendcr(body_code, ' result.calculated_rank,');
508 cn_utils.appendcr(body_code, ' result.org_id,');
509 cn_utils.appendcr(body_code, ' fnd_global.user_id,');
510 cn_utils.appendcr(body_code, ' SYSDATE,');
511 cn_utils.appendcr(body_code, ' fnd_global.user_id,');
512 cn_utils.appendcr(body_code, ' SYSDATE,');
513 cn_utils.appendcr(body_code, ' fnd_global.login_id');
514 cn_utils.appendcr(body_code, ' FROM (');
515 --
516 FOR rule_attr_rec IN rule_attr_cur(comb_rec.rule_attr_comb_value)
517 LOOP
518 --
519 IF (l_attr_counter > 1) THEN
520 cn_utils.appendcr(body_code, ' UNION ALL');
521 END IF;
522 l_row_num := l_row_num + 1;
523 --
524 cn_utils.appendcr(body_code, ' SELECT b.sca_credit_rule_id, ');
525 cn_utils.appendcr(body_code, ' c.sca_headers_interface_id, ');
526 cn_utils.appendcr(body_code, ' c.processed_date, ');
527 cn_utils.appendcr(body_code, ' b.calculated_rank, ');
528 cn_utils.appendcr(body_code, ' a.rank, ');
529 cn_utils.appendcr(body_code, ' a.org_id ');
530 cn_utils.appendcr(body_code, ' FROM ');
531 cn_utils.appendcr(body_code, ' cn_sca_denorm_rules a, ');
532 cn_utils.appendcr(body_code, ' cn_sca_rule_cond_vals_mv b, ');
533 cn_utils.appendcr(body_code, ' cn_sca_headers_interface c ');
534 cn_utils.appendcr(body_code, ' WHERE a.sca_credit_rule_id = b.sca_credit_rule_id ');
535 cn_utils.appendcr(body_code, ' AND a.sca_credit_rule_id = a.ancestor_rule_id ');
536 cn_utils.appendcr(body_code, ' AND a.rule_attr_comb_value = l_comb_value_id');
537 cn_utils.appendcr(body_code, ' AND a.transaction_source = p_transaction_source ');
538 cn_utils.appendcr(body_code, ' AND a.org_id = p_org_id ');
539 cn_utils.appendcr(body_code, ' AND a.org_id = c.org_id ');
540 cn_utils.appendcr(body_code, ' AND c.process_status = ''SCA_UNPROCESSED'' ');
541 cn_utils.appendcr(body_code, ' AND c.processed_date BETWEEN p_start_date AND p_end_date ');
542 cn_utils.appendcr(body_code, ' AND c.processed_date BETWEEN a.start_date AND NVL(a.end_date,c.processed_date) ');
543 cn_utils.appendcr(body_code, ' AND c.sca_headers_interface_id BETWEEN p_start_id AND p_end_id ');
544 cn_utils.appendcr(body_code, ' AND b.sca_rule_attribute_id = (');
545 cn_utils.appendcr(body_code, ' SELECT DISTINCT sca_rule_attribute_id ');
546 cn_utils.appendcr(body_code, ' FROM ( SELECT a.*, rownum rnum ');
547 cn_utils.appendcr(body_code, ' FROM ( SELECT sca_rule_attribute_id ');
548 cn_utils.appendcr(body_code, ' FROM cn_sca_combinations sc ');
549 cn_utils.appendcr(body_code, ' WHERE sc.rule_attr_comb_value = l_comb_value_id ');
550 cn_utils.appendcr(body_code, ' AND sc.org_id = p_org_id ');
551 cn_utils.appendcr(body_code, ' ORDER BY sc.sca_rule_attribute_id) a ');
552 cn_utils.appendcr(body_code, ' WHERE rownum <= '||l_row_num||') ');
553 cn_utils.appendcr(body_code, ' WHERE rnum >= '||l_row_num||') ');
554 FOR operator_rec IN operator_cur(rule_attr_rec.sca_rule_attribute_id)
555 LOOP
556 --
557 IF (l_operator_counter = 1) THEN
558 cn_utils.appendcr(body_code, ' AND (');
559 END IF;
560 --
561 IF (l_operator_counter > 1) THEN
562 cn_utils.appendcr(body_code, ' OR ');
563 END IF;
564 --
565 IF (operator_rec.lookup_code = 'EQUAL') THEN
566 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
567 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
568 cn_utils.appendcr(body_code, ' = b.value_char_min AND ');
569 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
570 cn_utils.appendcr(body_code, ' = b.value_num_min AND ');
571 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
572 cn_utils.appendcr(body_code, ' = b.value_date_min AND ');
573 END IF;
574 cn_utils.appendcr(body_code, ' b.operator_id = ''EQUAL'')');
575 ELSIF (operator_rec.lookup_code = 'LIKE') THEN
576 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
577 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
581 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
578 cn_utils.appendcr(body_code, ' LIKE b.value_char_min AND ');
579 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
580 cn_utils.appendcr(body_code, ' LIKE b.value_num_min AND ');
582 cn_utils.appendcr(body_code, ' LIKE b.value_date_min AND ');
583 END IF;
584 cn_utils.appendcr(body_code, ' b.operator_id = ''LIKE'')');
585 ELSIF (operator_rec.lookup_code = 'BETWEEN') THEN
586 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
587 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
588 cn_utils.appendcr(body_code, ' BETWEEN b.VALUE_CHAR_MIN AND b.VALUE_CHAR_MAX AND');
589 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
590 cn_utils.appendcr(body_code, ' BETWEEN b.VALUE_NUM_MIN AND b.VALUE_NUM_MAX AND');
591 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
592 cn_utils.appendcr(body_code, ' BETWEEN b.VALUE_DATE_MIN AND b.VALUE_DATE_MAX AND');
593 END IF;
594 cn_utils.appendcr(body_code, ' b.operator_id = ''BETWEEN'')');
595 ELSIF (operator_rec.lookup_code = 'GRE') THEN
596 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
597 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
598 cn_utils.appendcr(body_code, ' >= b.VALUE_CHAR_MIN AND ');
599 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
600 cn_utils.appendcr(body_code, ' >= b.VALUE_NUM_MIN AND ');
601 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
602 cn_utils.appendcr(body_code, ' >= b.VALUE_DATE_MIN AND ');
603 END IF;
604 cn_utils.appendcr(body_code, ' b.operator_id = ''GRE'')');
605 ELSIF (operator_rec.lookup_code = 'GT') THEN
606 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
607 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
608 cn_utils.appendcr(body_code, ' > b.VALUE_CHAR_MIN AND ');
609 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
610 cn_utils.appendcr(body_code, ' > b.VALUE_NUM_MIN AND ');
611 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
612 cn_utils.appendcr(body_code, ' > b.VALUE_DATE_MIN AND ');
613 END IF;
614 cn_utils.appendcr(body_code, ' b.operator_id = ''GT'')');
615 ELSIF (operator_rec.lookup_code = 'LTE') THEN
616 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
617 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
618 cn_utils.appendcr(body_code, ' <= b.VALUE_CHAR_MIN AND ');
619 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
620 cn_utils.appendcr(body_code, ' <= b.VALUE_NUM_MIN AND ');
621 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
622 cn_utils.appendcr(body_code, ' <= b.VALUE_DATE_MIN AND ');
623 END IF;
624 cn_utils.appendcr(body_code, ' b.operator_id = ''LTE'')');
625 ELSIF (operator_rec.lookup_code = 'LT') THEN
626 cn_utils.appendcr(body_code, ' (c.'||rule_attr_rec.src_column_name);
627 IF (rule_attr_rec.datatype = 'ALPHANUMERIC') THEN
628 cn_utils.appendcr(body_code, ' < b.VALUE_CHAR_MIN AND ');
629 ELSIF (rule_attr_rec.datatype = 'NUMERIC') THEN
630 cn_utils.appendcr(body_code, ' < b.VALUE_NUM_MIN AND ');
631 ELSIF (rule_attr_rec.datatype = 'DATE') THEN
632 cn_utils.appendcr(body_code, ' < b.VALUE_DATE_MIN AND ');
633 END IF;
634 cn_utils.appendcr(body_code, ' b.operator_id = ''LT'') ');
635 END IF;
636 --
637 l_operator_counter := l_operator_counter + 1;
638 --
639 END LOOP;
640 --
641 IF (l_operator_counter > 1) THEN
642 cn_utils.appendcr(body_code, ' )');
643 END IF;
644 --
645 cn_utils.appendcr(body_code, ' GROUP BY b.sca_credit_rule_id, c.sca_headers_interface_id, ');
646 cn_utils.appendcr(body_code, ' c.processed_date, b.calculated_rank, a.rank, a.org_id ');
647 --
648 l_operator_counter := 1;
649 l_attr_counter := l_attr_counter + 1;
650 --
651 END LOOP;
652 l_if_counter := l_if_counter + 1;
653 l_attr_counter := 1;
654 cn_utils.appendcr(body_code, ') result ');
655 cn_utils.appendcr(body_code, 'GROUP BY result.sca_credit_rule_id, ');
656 cn_utils.appendcr(body_code, ' result.sca_headers_interface_id, ');
657 cn_utils.appendcr(body_code, ' result.processed_date, ');
658 cn_utils.appendcr(body_code, ' result.calculated_rank, ');
659 cn_utils.appendcr(body_code, ' result.rank, ');
660 cn_utils.appendcr(body_code, ' result.org_id ');
661 cn_utils.appendcr(body_code, 'HAVING (result.sca_credit_rule_id,count(1)) = ( ');
662 cn_utils.appendcr(body_code, ' SELECT r.sca_credit_rule_id,r.num_rule_attributes ');
663 cn_utils.appendcr(body_code, ' FROM cn_sca_denorm_rules r ');
664 cn_utils.appendcr(body_code, ' WHERE r.sca_credit_rule_id = result.sca_credit_rule_id ');
665 cn_utils.appendcr(body_code, ' AND r.org_id = p_org_id ');
666 cn_utils.appendcr(body_code, ' AND r.ancestor_rule_id = result.sca_credit_rule_id ');
667 cn_utils.appendcr(body_code, ' AND r.transaction_source = p_transaction_source);');
668 cn_utils.appendcr(body_code, 'COMMIT work;');
669 END LOOP;
670
674 cn_utils.appindcr(body_code, 'EXCEPTION ');
671 cn_utils.appendcr(body_code, ' END IF;');
672 cn_utils.appendcr(body_code, ' END LOOP;');
673
675 cn_utils.appindcr(body_code, ' WHEN OTHERS THEN ');
676 cn_utils.appindcr(body_code, ' cn_message_pkg.debug(''EXCEPTION IN populate_matches, '' || sqlerrm); ');
677 cn_utils.appindcr(body_code, ' raise; ');
678
679 cn_utils.appendcr(body_code, 'END;');
680
681 EXCEPTION when others then
682 cn_message_pkg.debug('IN get_perf exception handler, error is '||sqlcode||' '||sqlerrm);
683 RAISE;
684 END populate_matches;
685
686 FUNCTION create_sca_rules_batch_dyn (
687 x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE)
688 RETURN BOOLEAN IS
689 -- Variables Section
690 package_name cn_obj_packages_v.name%TYPE;
691 package_spec_id cn_obj_packages_v.package_id%TYPE;
692 package_body_id cn_obj_packages_v.package_id%TYPE;
693 package_spec_desc cn_obj_packages_v.description%TYPE;
694 package_body_desc cn_obj_packages_v.description%TYPE;
695 spec_code cn_utils.code_type;
696 body_code cn_utils.code_type;
697 dummy NUMBER(7);
698 l_module_id number(15);
699 l_repository_id cn_repositories.repository_id%TYPE;
700 l_org_id NUMBER;
701 --
702 BEGIN
703
704 cn_utils.set_org_id(g_org_id);
705
706 select repository_id into l_repository_id
707 from cn_repositories where org_id = g_org_id;
708
709
710 package_name := 'cn_sca_batch_'||lower(x_transaction_source)||'_'||
711 abs(g_org_id)||'_pkg';
712 g_package_name := package_name;
713
714 --dbms_output.put_line('package_name '||package_name);
715 check_create_object(package_name, 'PKS', package_spec_id, l_repository_id);
716 check_create_object(package_name, 'PKB', package_body_id, l_repository_id);
717
718 --dbms_output.put_line('package_spec_id '||package_spec_id);
719 --dbms_output.put_line('package_body_id '||package_body_id);
720 --
721 pkg_init(
722 module_id => l_module_id,
723 package_name => package_name,
724 package_spec_id => package_spec_id,
725 package_body_id => package_body_id,
726 package_spec_desc => package_spec_desc,
727 package_body_desc => package_body_desc,
728 spec_code => spec_code,
729 body_code => body_code);
730 --DBMS_OUTPUT.PUT_LINE('pkg init called');
731 --
732 populate_matches(spec_code, body_code,x_transaction_source);
733 --
734 cn_utils.pkg_end(package_name, spec_code, body_code);
735 --
736 cn_utils.unset_org_id;
737 --
738 RETURN TRUE;
739 END;
740 --
741 PROCEDURE gen_sca_rules_batch_dyn(
742 p_api_version IN NUMBER,
743 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
744 p_commit IN VARCHAR2 := FND_API.G_FALSE,
745 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
746 p_org_id IN NUMBER,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_msg_count OUT NOCOPY NUMBER,
749 x_msg_data OUT NOCOPY VARCHAR2,
750 x_transaction_source IN cn_sca_rule_attributes.transaction_source%TYPE)IS
751
752 l_api_name CONSTANT VARCHAR2(30) := 'gen_sca_rules_batch_dyn';
753 l_api_version CONSTANT NUMBER :=1.0;
754
755 l_creation_status BOOLEAN;
756 l_request_id NUMBER;
757 l_file_name VARCHAR2(200);
758 l_call_status BOOLEAN;
759 l_dummy VARCHAR2(500);
760 l_dev_phase VARCHAR2(80);
761 l_dev_status VARCHAR2(80) := 'INCOMPLETE';
762 l_status BOOLEAN;
763 sqlstring dbms_sql.varchar2s;
764 empty_sqlstring dbms_sql.varchar2s;
765 cursor1 INTEGER;
766 i INTEGER;
767 j INTEGER;
768 new_line_flag BOOLEAN:=TRUE;
769 retval NUMBER;
770 l_pkg_object_id NUMBER(15);
771 l_error_count NUMBER;
772 l_pkg_name VARCHAR2(100);
773 l_org_id NUMBER;
774
775 BEGIN
776 --codeCheck: For a concurrent program this kind of code is not required.
777 --codeCheck: I may need to change this one.
778
779 -- Standard call to check for call compatibility.
780 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
781 p_api_version ,
782 l_api_name ,
783 G_PKG_NAME )
784 THEN
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786 END IF;
787
788 -- Initialize message list if p_init_msg_list is set to TRUE.
789 IF FND_API.to_Boolean( p_init_msg_list ) THEN
790 FND_MSG_PUB.initialize;
791 END IF;
792
793 -- Initialize API return status to success
794 x_return_status := FND_API.G_RET_STS_SUCCESS;
795
796
797 -- Codes start here
798 -- SELECT org_id INTO l_org_id FROM cn_repositories;
799 g_org_id := p_org_id;
800
801 l_pkg_name := 'cn_sca_batch_'||lower(x_transaction_source)||'_'||
802 abs(g_org_id)||'_pkg';
803
804 l_status := create_sca_rules_batch_dyn(x_transaction_source);
805
806 --dbms_output.put_line('l_pkg_name :'||l_pkg_name);
807
808 IF l_status THEN /* created successfully. Continue to install it. */
809 SELECT co.object_id
810 INTO l_pkg_object_id
811 FROM cn_objects co
812 WHERE co.name = l_pkg_name
813 AND co.object_type = 'PKS'
814 AND co.org_id = g_org_id;
815
816 SELECT cs.text bulk collect INTO sqlstring
817 FROM cn_source cs
818 WHERE cs.object_id = l_pkg_object_id
819 AND cs.org_id = g_org_id
820 ORDER BY cs.line_no ;
821
822 --dbms_output.put_line('pkg id is '|| l_pkg_object_id );
823
824 i:=1;
825 j:= sqlstring.count;
826
827 cursor1:=DBMS_SQL.OPEN_CURSOR;
828 DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7);
829 retval:=DBMS_SQL.EXECUTE(cursor1);
830 DBMS_SQL.CLOSE_CURSOR(cursor1);
831
832 sqlstring := empty_sqlstring;
833
834 SELECT co.object_id
835 INTO l_pkg_object_id
836 FROM cn_objects co
837 WHERE co.name = l_pkg_name
838 AND co.object_type = 'PKB'
839 AND co.org_id = g_org_id;
840
841 --dbms_output.put_line('pkb id is '|| l_pkg_object_id );
842
843 SELECT cs.text bulk collect INTO sqlstring
844 FROM cn_source cs
845 WHERE cs.object_id = l_pkg_object_id
846 AND cs.org_id = g_org_id
847 ORDER BY cs.line_no ;
848
849 i:= 1;
850 j:= sqlstring.count;
851
852 cursor1:=DBMS_SQL.OPEN_CURSOR;
853 DBMS_SQL.PARSE(cursor1,sqlstring,i,j,new_line_flag,DBMS_SQL.V7);
854 retval:=DBMS_SQL.EXECUTE(cursor1);
855 DBMS_SQL.CLOSE_CURSOR(cursor1);
856
857 cn_message_pkg.debug('The rule dynamic package is created successfully. Continue to intall the package. ');
858 fnd_file.put_line(fnd_file.Log, 'The rule dynamic package is created successfully. Continue to intall the package.');
859 --dbms_output.put_line('The rule dynamic package is created successfully. ' );
860
861 -- check whether package is installed successfully
862 SELECT COUNT(*)
863 INTO l_error_count
864 FROM user_errors
865 WHERE name = upper(l_pkg_name)
866 AND TYPE IN ('PACKAGE', 'PACKAGE BODY');
867
868 IF l_error_count = 0 THEN
869 NULL;
870 --dbms_output.put_line('sucess compilation');
871 ELSE
872 x_return_status := FND_API.g_ret_sts_error;
873
874 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
875 THEN
876 fnd_message.set_name('CN', 'CN_DYN_PKG_COMPILE_ERR');
877 FND_MSG_PUB.ADD;
878 END IF;
879 END IF;
880 ELSE
881 x_return_status := FND_API.g_ret_sts_error;
882
883 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
884 THEN
885 fnd_message.set_name('CN', 'CN_DYN_PKG_COMPILE_ERR');
886 FND_MSG_PUB.ADD;
887 END IF;
888 END IF;
889
890 IF FND_API.To_Boolean( p_commit ) THEN
891 COMMIT WORK;
892 END IF;
893
894 EXCEPTION
895 WHEN FND_API.G_EXC_ERROR THEN
896 -- codeCheck: This needs to be changed
897 --ROLLBACK TO generate_formula;
898 x_return_status := FND_API.G_RET_STS_ERROR ;
899 FND_MSG_PUB.Count_And_Get
900 (p_count => x_msg_count ,
901 p_data => x_msg_data ,
902 p_encoded => FND_API.G_FALSE
903 );
904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
905 --ROLLBACK TO generate_formula;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 FND_MSG_PUB.Count_And_Get
908 (p_count => x_msg_count ,
909 p_data => x_msg_data ,
910 p_encoded => FND_API.G_FALSE
911 );
912 WHEN OTHERS THEN
913 --ROLLBACK TO generate_formula;
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
915 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
916 THEN
917 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
918 END IF;
919 FND_MSG_PUB.Count_And_Get
920 (p_count => x_msg_count ,
921 p_data => x_msg_data ,
922 p_encoded => FND_API.G_FALSE
923 );
924 END gen_sca_rules_batch_dyn;
925 --
926 END cn_sca_rules_batch_gen_pvt;