[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_DENORM_RULES_PVT
Source
1 PACKAGE BODY CN_SCA_DENORM_RULES_PVT AS
2 -- $Header: cnvscadb.pls 120.5 2006/03/31 04:16:25 rrshetty noship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_SCA_DENORM_RULES_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 --
18 -- Global Variables
19 --
20 PROCEDURE debugmsg(msg VARCHAR2) IS
21 BEGIN
22
23 IF g_cn_debug = 'Y' THEN
24 cn_message_pkg.debug(substr(msg,1,254));
25 fnd_file.put_line(fnd_file.Log, msg);
26 END IF;
27
28 END debugmsg;
29 --
30 PROCEDURE find_combinations(
31 p_transaction_source IN VARCHAR2,
32 p_org_id IN NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2) IS
34
35 --+
36 --+ Cursors Section
37 --+
38
39 CURSOR rule_attr_cur IS
40 SELECT mv.sca_rule_attribute_id,
41 ra.src_column_name
42 FROM cn_sca_rule_cond_vals_mv mv,
43 cn_sca_rule_attributes ra
44 WHERE mv.sca_rule_attribute_id = ra.sca_rule_attribute_id
45 AND mv.transaction_source = p_transaction_source
46 AND EXISTS(
47 SELECT 'X'
48 FROM cn_sca_denorm_rules dr
49 WHERE mv.sca_credit_rule_id = dr.sca_credit_rule_id
50 AND dr.transaction_source = p_transaction_source
51 AND dr.org_id = p_org_id)
52 GROUP BY mv.sca_rule_attribute_id, ra.src_column_name;
53 --
54 CURSOR get_attr_cur IS
55 SELECT rule_attr_comb_value
56 FROM cn_sca_denorm_rules
57 WHERE transaction_source = p_transaction_source
58 AND org_id = p_org_id
59 GROUP BY rule_attr_comb_value;
60 --
61 CURSOR operator_cur(l_rule_attr_id NUMBER) IS
62 SELECT mv.operator_id
63 FROM cn_sca_rule_cond_vals_mv mv
64 WHERE mv.sca_rule_attribute_id = l_rule_attr_id
65 GROUP BY mv.operator_id;
66
67 --+
68 --+ Variables Section
69 --+
70
71 l_attr_prime_tbl attr_prime_tbl_type;
72 l_attr_operator_tbl attr_operator_tbl_type;
73 l_op_counter NUMBER := 1;
74 l_start_loc NUMBER := 1;
75 l_end_loc NUMBER;
76 l_string VARCHAR2(2000);
77 l_update_flag VARCHAR2(1) := 'Y';
78 l_delete_flag VARCHAR2(1) := 'N';
79
80 BEGIN
81
82 debugmsg('Find Comb : Beginning of the find_combinations procedure');
83
84 --+
85 --+ Assign a PRIME number for each ATTRIBUTE in a PL/SQL Table.
86 --+
87
88 l_string := '2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61,67,71,73,'||
89 '79,83,89,97,101,103,107,109,113,127,131,137,139,149,151,157,'||
90 '163,167,173,179,181,191,193,197,199,211,223,227,229,233,239,'||
91 '241,251,257,263,269,271,277,281,283,293,307,311,313,317,331,'||
92 '337,347,349,353,359,367,373,379,383,389,397,401,409,419,421,'||
93 '431,433,439,443,449,457,461,463,467,479,487,491,499,503,509,'||
94 '521,523,541,';
95 FOR i IN 1..100 LOOP
96 l_end_loc := INSTR(l_string,',',l_start_loc) - 1;
97 l_attr_prime_tbl(i).prime_number
98 := SUBSTR(l_string,l_start_loc,((l_end_loc - l_start_loc)+1));
99 l_start_loc := l_end_loc + 2;
100 l_attr_prime_tbl(i).attribute_name := 'ATTRIBUTE'||i;
101 END LOOP;
102
103 --+
104 --+ Reset RULE_ATTR_COMB_ID in CN_SCA_DENORM_RULES and update with
105 --+ Combinations.
106 --+
107
108 UPDATE cn_sca_denorm_rules
109 SET rule_attr_comb_value = 1
110 WHERE transaction_source = p_transaction_source
111 AND org_id = p_org_id;
112 --
113
114 FOR rule_attr_rec IN rule_attr_cur
115 LOOP
116 FOR i IN 1..l_attr_prime_tbl.COUNT
117 LOOP
118 --
119 IF (l_attr_prime_tbl(i).attribute_name =
120 rule_attr_rec.src_column_name) THEN
121 --+
122 --+ For the first occurance, I need to update the rule_attr_comb_value
123 --+ in cn_sca_denorm_rules table.
124 --+
125
126 IF (l_update_flag = 'Y') THEN
127
128 UPDATE cn_sca_denorm_rules
129 SET rule_attr_comb_value = 1
130 WHERE transaction_source = p_transaction_source
131 AND org_id = p_org_id;
132
133 --debugmsg('Find Comb : rule_attr_comb_value reset to 1 for '||SQL%ROWCOUNT);
134 l_update_flag := 'N';
135
136 END IF;
137
138 BEGIN
139
140 UPDATE cn_sca_denorm_rules dr
141 SET dr.rule_attr_comb_value =
142 dr.rule_attr_comb_value * l_attr_prime_tbl(i).prime_number
143 WHERE dr.sca_credit_rule_id IN(
144 SELECT idr.sca_credit_rule_id
145 FROM cn_sca_denorm_rules idr,
146 cn_sca_conditions c
147 WHERE idr.ancestor_rule_id = c.sca_credit_rule_id
148 AND c.sca_rule_attribute_id = rule_attr_rec.sca_rule_attribute_id
149 AND idr.transaction_source = p_transaction_source
150 AND idr.org_id = p_org_id)
151 AND dr.transaction_source = p_transaction_source;
152
153 --debugmsg('Find Comb : rule_attr_rec.sca_rule_attribute_id :'||rule_attr_rec.sca_rule_attribute_id);
154 --debugmsg('Find Comb : Total Recs Updated in Denorm Table :'||SQL%ROWCOUNT);
155
156 END;
157 l_delete_flag := 'Y';
158 END IF;
159 --
160 END LOOP;
161 --
162 END LOOP;
163 --
164 -- Populate Rule Attributes associated with each distinct rule_attr_comb_id
165 -- into cn_sca_combinations
166 --
167 IF (l_delete_flag = 'Y') THEN
168 DELETE FROM cn_sca_combinations
169 WHERE transaction_source = p_transaction_source
170 AND org_id = p_org_id;
171 --
172 FOR get_attr_rec IN get_attr_cur
173 LOOP
174 INSERT INTO cn_sca_combinations(
175 sca_rule_attribute_id,
176 rule_attr_comb_value,
177 transaction_source,
178 created_by,
179 creation_date,
180 last_updated_by,
181 last_update_date,
182 last_update_login,
183 org_id)
184 SELECT sca_rule_attribute_id,
185 get_attr_rec.rule_attr_comb_value,
186 p_transaction_source,
187 fnd_global.user_id,
188 SYSDATE,
189 fnd_global.user_id,
190 SYSDATE,
191 fnd_global.login_id,
192 p_org_id
193 FROM cn_sca_rule_cond_vals_mv mv
194 WHERE mv.sca_credit_rule_id IN (
195 SELECT sca_credit_rule_id
196 FROM cn_sca_denorm_rules dr
197 WHERE rule_attr_comb_value = get_attr_rec.rule_attr_comb_value
198 AND dr.transaction_source = p_transaction_source
199 AND dr.org_id = p_org_id
200 AND rownum = 1)
201 GROUP BY sca_rule_attribute_id;
202 END LOOP;
203 x_return_status := 'S';
204 ELSE
205 x_return_status := 'F';
206 END IF;
207 debugmsg('Find Comb : End of the find_combinations procedure');
208
209 EXCEPTION
210
211 WHEN OTHERS THEN
212 debugmsg('Find Comb : Exception');
213 x_return_status := 'F';
214
215 END;
216 --
217 PROCEDURE populate_rule_denorm (
218 errbuf OUT NOCOPY VARCHAR2,
219 retcode OUT NOCOPY NUMBER,
220 p_txn_src IN VARCHAR2) IS
221
222 --+
223 --+ PL/SQL Tables and Records
224 --+
225
226 TYPE credit_rule_id_tbl_type
227 IS TABLE OF cn_sca_credit_rules.sca_credit_rule_id%TYPE;
228
229 l_credit_rule_id_tbl credit_rule_id_tbl_type;
230
231 --+
232 --+ Local Variables Section
233 --+
234
235 l_max_rank NUMBER;
236 l_process_audit_id NUMBER;
237 conc_status BOOLEAN;
238 l_api_version CONSTANT NUMBER :=1.0;
239 l_return_status VARCHAR2(50);
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(2000);
242 l_continue VARCHAR2(1) := 'N';
243 l_invalid_rules NUMBER := 0;
244 l_temp_org_id NUMBER;
245 p_org_id NUMBER;
246
247 --+
248 --+ Exceptions
249 --+
250
251 ex_invalid_rules EXCEPTION;
252 l_no_rule_ex EXCEPTION;
253
254 --+
255 --+ Cursors Section
256 --+
257
258 CURSOR c1 IS
259 SELECT *
260 FROM cn_sca_denorm_rules csdr
261 WHERE transaction_source = p_txn_src
262 AND org_id = p_org_id
263 AND EXISTS (
264 SELECT 'S'
265 FROM cn_sca_credit_rules csca
266 WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id
267 AND transaction_source = p_txn_src
268 AND NVL(IS_DENORMED,'N') = 'N');
269
270 CURSOR c2 IS
271 SELECT csca.sca_credit_rule_id
272 FROM cn_sca_credit_rules csca
273 WHERE transaction_source = p_txn_src
274 AND org_id = p_org_id
275 AND NVL(IS_DENORMED,'N') = 'N';
276 --
277
278 BEGIN
279
280 p_org_id := mo_global.get_current_org_id();
281
282 cn_message_pkg.begin_batch (
283 x_process_type => 'SCA Rules Sync',
284 x_parent_proc_audit_id => null,
285 x_process_audit_id => l_process_audit_id,
286 x_request_id => fnd_global.conc_request_id,
287 p_org_id => p_org_id);
288
289 debugmsg('Rules Sync : Start of the Rules Synchronization');
290 debugmsg('Rules Sync : l_process_audit_id - '||l_process_audit_id);
291 debugmsg('Rules Sync: mo_global.get_current_org_id is - ' || p_org_id);
292
293 --dbms_output.put_line('Rules Sync : l_process_audit_id - '||l_process_audit_id);
294
295 --+
296 --+ First Check whether any Credit Rules Exists without a valid
297 --+ Credit Rule Attribute. If exists, then dynamic PL/SQL package
298 --+ should not be recreated.
299 --+
300
301 SELECT count(1)
302 INTO l_invalid_rules
303 FROM cn_sca_conditions a,
304 cn_sca_credit_rules b
305 WHERE a.sca_credit_rule_id = b.sca_credit_rule_id
306 AND b.org_id = p_org_id
307 AND NOT EXISTS(
308 SELECT 'X'
309 FROM cn_sca_rule_attributes c
310 WHERE a.sca_rule_attribute_id = c.sca_rule_attribute_id)
311 AND b.transaction_source = p_txn_src;
312
313 IF (l_invalid_rules > 0) THEN
314
315 debugmsg('Rules Sync : Rules Exists without Rule Attributes');
316 debugmsg('Rules Sync : Invalid Rule Count :'||l_invalid_rules);
317 RAISE ex_invalid_rules;
318
319 END IF;
320
321 --+
322 --+ First Check whether Credit Rules are available or not. This l_max_rank
323 --+ will be used to find the calculated rank.
324 --+
325
326 BEGIN
327 SELECT max(rank)
328 INTO l_max_rank
329 FROM cn_sca_credit_rules
330 WHERE transaction_source = p_txn_src
331 AND org_id = p_org_id;
332
333 IF (l_max_rank IS NULL) THEN
334 debugmsg('Rules Sync : Base table rules not found. Deleting data from denorm table');
335 BEGIN
336 DELETE FROM cn_sca_denorm_rules
337 WHERE transaction_source = p_txn_src
338 AND org_id = p_org_id;
339 debugmsg('Rules Sync : Deleted denorm rule count : '||SQL%ROWCOUNT);
340 debugmsg('Rules Sync : Refreshing MV');
341 DBMS_MVIEW.REFRESH('CN_SCA_RULE_COND_VALS_MV','C','',TRUE,FALSE,0,4,0,TRUE);
342 debugmsg('Rules Sync : Refreshing MV completed');
343 COMMIT;
344 EXCEPTION
345 WHEN OTHERS THEN
346 debugmsg('Results Transfer : Unexpected exception while refreshing MV');
347 END;
348 RAISE l_no_rule_ex;
349 END IF;
350 END;
351
352 --+
353 --+ Delete existing records in the denorm table corresponds to
354 --+ given transaction source
355 --+
356
357 debugmsg('Rules Sync : Deleting Existing Rules Based on the Flag');
358
359 DELETE FROM cn_sca_denorm_rules csdr
360 WHERE transaction_source = p_txn_src
361 AND org_id = p_org_id
362 AND (EXISTS
363 (SELECT 'S'
364 FROM cn_sca_credit_rules csca
365 WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id
366 AND NVL(IS_DENORMED,'N') = 'N')
367 OR NOT EXISTS
368 (SELECT 'S'
369 FROM cn_sca_credit_rules csca
370 WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id));
371
372 debugmsg('Rules Sync : Total Rules Deleted - '||SQL%ROWCOUNT);
373 debugmsg('Rules Sync : l_max_rank - '||l_max_rank);
374
375 --+
376 --+ Insert rules without parents and their entire hierarchy.
377 --+
378
379 INSERT INTO cn_sca_denorm_rules(
380 sca_credit_rule_id,
381 ancestor_rule_id,
382 start_date,
383 end_date,
384 rank,
385 level_from_root,
386 relative_rank,
387 root_flag,
388 transaction_source,
389 created_by,
390 creation_date,
391 last_updated_by,
392 last_update_date,
393 last_update_login,
394 org_id)
395 SELECT sca_credit_rule_id,
396 sca_credit_rule_id,
397 start_date,
398 end_date,
399 rank,
400 level,
401 1/(NVL(DECODE(rank,0,0.1,rank),l_max_rank)*POWER(l_max_rank,level)), -- relative rank
402 DECODE(NVL(parent_rule_id,0),0,'Y','N'), -- root flag
403 p_txn_src,
404 fnd_global.user_id,
405 SYSDATE,
406 fnd_global.user_id,
407 SYSDATE,
408 fnd_global.login_id,
409 p_org_id
413 AND NVL(IS_DENORMED,'N') = 'N'
410 FROM cn_sca_credit_rules cscr
411 WHERE transaction_source = p_txn_src
412 AND org_id = p_org_id
414 CONNECT BY PRIOR sca_credit_rule_id = parent_rule_id
415 START WITH parent_rule_id IS NULL AND transaction_source = p_txn_src;
416
417 debugmsg('Rules Sync : Parent Rules Insert Completed :'||SQL%ROWCOUNT);
418
419 --+
420 --+ Take each rule from denorm table and find all its ancestors
421 --+
422
423 IF (SQL%ROWCOUNT > 0) THEN
424 debugmsg('Rules Sync : Identifying children for each parent');
425
426 FOR c1_rec IN c1
427 LOOP
428 INSERT INTO cn_sca_denorm_rules(
429 sca_credit_rule_id,
430 ancestor_rule_id,
431 start_date,
432 end_date,
433 rank,
434 level_from_root,
435 relative_rank,
436 root_flag,
437 transaction_source,
438 created_by,
439 creation_date,
440 last_updated_by,
441 last_update_date,
442 last_update_login,
443 org_id)
444 SELECT c1_rec.sca_credit_rule_id,
445 sca_credit_rule_id,
446 c1_rec.start_date,
447 c1_rec.end_date,
448 c1_rec.rank,
449 c1_rec.level_from_root,
450 c1_rec.relative_rank,
451 c1_rec.root_flag,
452 p_txn_src,
453 fnd_global.user_id,
454 SYSDATE,
455 fnd_global.user_id,
456 SYSDATE,
457 fnd_global.login_id,
458 p_org_id
459 FROM cn_sca_credit_rules
460 WHERE sca_credit_rule_id <> c1_rec.sca_credit_rule_id
461 AND transaction_source = p_txn_src
462 AND org_id = p_org_id
463 CONNECT BY PRIOR parent_rule_id = sca_credit_rule_id
464 START WITH sca_credit_rule_id = c1_rec.sca_credit_rule_id
465 AND transaction_source = p_txn_src;
466
467 --+
468 --+ Update calculated rank based on the relative ranks of the ancestors
469 --+
470
471 UPDATE cn_sca_denorm_rules
472 SET calculated_rank = (
473 SELECT SUM(r2.relative_rank)
474 FROM cn_sca_denorm_rules r1,
475 cn_sca_denorm_rules r2
476 WHERE r1.transaction_source = p_txn_src
477 AND r2.transaction_source = p_txn_src
478 AND r1.sca_credit_rule_id = c1_rec.sca_credit_rule_id
479 AND r2.sca_credit_rule_id = r1.ancestor_rule_id
480 AND r2.ancestor_rule_id = r1.ancestor_rule_id),
481 num_rule_attributes = (
482 SELECT count(distinct c.sca_rule_attribute_id)
483 FROM cn_sca_denorm_rules r,
484 cn_sca_credit_rules s,
485 cn_sca_conditions c
486 WHERE r.transaction_source = p_txn_src
487 AND s.transaction_source = p_txn_src
488 AND r.sca_credit_rule_id = c1_rec.sca_credit_rule_id
489 AND r.ancestor_rule_id = s.sca_credit_rule_id
490 AND s.sca_credit_rule_id = c.sca_credit_rule_id)
491 WHERE transaction_source = p_txn_src
492 AND sca_credit_rule_id = c1_rec.sca_credit_rule_id
493 AND org_id = p_org_id;
494
495 --+
496 --+ Resetting the is_denormed flag
497 --+
498
499 UPDATE cn_sca_credit_rules
500 SET is_denormed = 'Y'
501 WHERE sca_credit_rule_id = c1_rec.sca_credit_rule_id
502 AND transaction_source = p_txn_src
503 AND org_id = p_org_id;
504
505 END LOOP;
506 END IF;
507
508 --+
509 --+ Refresh the MVs
510 --+
511
512 debugmsg('Rules Sync : Start of the MV Refresh');
513
514 BEGIN
515 DBMS_MVIEW.REFRESH('CN_SCA_RULE_COND_VALS_MV','C','',TRUE,FALSE,0,4,0,TRUE);
516 EXCEPTION
517 WHEN OTHERS THEN
518 debugmsg('Results Transfer : Unexpected exception');
519 RAISE;
520 END;
521
522 debugmsg('Rules Sync : End of the MV Refresh');
523
524 --+
525 --+ Update Rule Attribute Combinations
526 --+
527
528 debugmsg('Rules Sync : Start of Updating Rule Combination ID');
529
530 cn_sca_denorm_rules_pvt.find_combinations(
531 p_txn_src,
532 p_org_id,
533 l_return_status);
534
535 IF (l_return_status <> 'S') THEN
536 debugmsg('Rules Sync : Error While Updating Rule Combinations');
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 debugmsg('Rules Sync : End of Updating Rule Combination ID');
541
542 --+
543 --+ Creating Dynamic SQL Packages for Online and Batch Mode
544 --+
545
546 debugmsg('Rules Sync : Starting Batch Mode Dynamic Package Creation');
547
548 cn_sca_rules_batch_gen_pvt.gen_sca_rules_batch_dyn(
549 p_api_version => l_api_version,
550 x_return_status => l_return_status,
551 x_msg_count => l_msg_count,
552 x_msg_data => l_msg_data,
553 x_transaction_source => p_txn_src,
554 p_org_id => p_org_id);
555 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
556 debugmsg('SCA Rules Sync: Error While Creating Batch Dynamic Package');
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559
560 debugmsg('Rules Sync : Starting Online Mode Dynamic Package Creation');
561
562 cn_sca_rules_online_gen_pvt.gen_sca_rules_onln_dyn(
563 p_api_version => l_api_version,
564 x_return_status => l_return_status,
565 x_msg_count => l_msg_count,
566 x_msg_data => l_msg_data,
567 x_transaction_source => p_txn_src,
568 p_org_id => p_org_id);
569 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
570 debugmsg('Rules Sync : Error While Creating Online Dynamic Package');
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573
574 debugmsg('Rules Sync : Ending Online Mode Dynamic Package Creation');
575 --
576 COMMIT;
577 --
578 retcode := 0;
579 errbuf := 'Rules Synchronization Completed Successfully';
580 debugmsg('Rules Synchronization Completed Successfully');
581 cn_message_pkg.end_batch(l_process_audit_id);
582 --
583
584 EXCEPTION
585
586 WHEN ex_invalid_rules THEN
587 ROLLBACK;
588 cn_message_pkg.end_batch(l_process_audit_id);
589 debugmsg('Rules Sync : Exception: ex_invalid_rules');
590 conc_status := fnd_concurrent.set_completion_status(
591 status => 'ERROR',
592 message => '');
593
594 WHEN l_no_rule_ex THEN
595 ROLLBACK;
596 debugmsg('Rules Sync : No Rules available in CN_SCA_CREDIT_RULES table');
597 cn_message_pkg.end_batch(l_process_audit_id);
598 conc_status := fnd_concurrent.set_completion_status(
599 status => 'ERROR',
600 message => '');
601
602 WHEN FND_API.G_EXC_ERROR THEN
603 ROLLBACK;
604 debugmsg('Rules Sync : Execution Error');
605 cn_message_pkg.end_batch(l_process_audit_id);
606 conc_status := fnd_concurrent.set_completion_status(
607 status => 'ERROR',
608 message => '');
609
610 WHEN OTHERS THEN
611 ROLLBACK;
612 debugmsg('Rules Sync : Unexpected exception');
613 debugmsg('Oracle Error: '||SQLERRM);
614 cn_message_pkg.end_batch(l_process_audit_id);
615 conc_status := fnd_concurrent.set_completion_status(
616 status => 'ERROR',
617 message => '');
618 END;
619 --
620 END;