[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.8.12020000.2 2012/08/23 07:08:40 swpoddar 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_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 --+
71 l_attr_prime_tbl attr_prime_tbl_type;
68 --+ Variables Section
69 --+
70
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
224 --+
221
222 --+
223 --+ PL/SQL Tables and Records
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 : 12.2 - MV is converted to view - no refresh process');
341 /* bug 13504898 DBMS_MVIEW.REFRESH('CN_SCA_RULE_COND_VALS_MV','C','',TRUE,FALSE,0,4,0,TRUE);
342 debugmsg('Rules Sync : Refreshing MV completed');
343 */
344 COMMIT;
345 EXCEPTION
346 WHEN OTHERS THEN
347 debugmsg('Results Transfer : Unexpected exception while refreshing MV');
348 END;
349 RAISE l_no_rule_ex;
350 END IF;
351 END;
352
353 --+
354 --+ Delete existing records in the denorm table corresponds to
355 --+ given transaction source
356 --+
357
358 debugmsg('Rules Sync : Deleting Existing Rules Based on the Flag');
359
360 DELETE FROM cn_sca_denorm_rules csdr
361 WHERE transaction_source = p_txn_src
362 AND org_id = p_org_id
363 AND (EXISTS
364 (SELECT 'S'
365 FROM cn_sca_credit_rules csca
366 WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id
367 AND NVL(IS_DENORMED,'N') = 'N')
368 OR NOT EXISTS
369 (SELECT 'S'
370 FROM cn_sca_credit_rules csca
371 WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id));
372
373 debugmsg('Rules Sync : Total Rules Deleted - '||SQL%ROWCOUNT);
374 debugmsg('Rules Sync : l_max_rank - '||l_max_rank);
375
376 --+
377 --+ Insert rules without parents and their entire hierarchy.
378 --+
379
380 INSERT INTO cn_sca_denorm_rules(
384 end_date,
381 sca_credit_rule_id,
382 ancestor_rule_id,
383 start_date,
385 rank,
386 level_from_root,
387 relative_rank,
388 root_flag,
389 transaction_source,
390 created_by,
391 creation_date,
392 last_updated_by,
393 last_update_date,
394 last_update_login,
395 org_id)
396 SELECT sca_credit_rule_id,
397 sca_credit_rule_id,
398 start_date,
399 end_date,
400 rank,
401 level,
402 1/(NVL(DECODE(rank,0,0.1,rank),l_max_rank)*POWER(l_max_rank,level)), -- relative rank
403 DECODE(NVL(parent_rule_id,0),0,'Y','N'), -- root flag
404 p_txn_src,
405 fnd_global.user_id,
406 SYSDATE,
407 fnd_global.user_id,
408 SYSDATE,
409 fnd_global.login_id,
410 p_org_id
411 FROM cn_sca_credit_rules cscr
412 WHERE transaction_source = p_txn_src
413 AND org_id = p_org_id
414 AND NVL(IS_DENORMED,'N') = 'N'
415 CONNECT BY PRIOR sca_credit_rule_id = parent_rule_id
416 START WITH parent_rule_id IS NULL AND transaction_source = p_txn_src;
417
418 debugmsg('Rules Sync : Parent Rules Insert Completed :'||SQL%ROWCOUNT);
419
420 --+
421 --+ Take each rule from denorm table and find all its ancestors
422 --+
423
424 IF (SQL%ROWCOUNT > 0) THEN
425 debugmsg('Rules Sync : Identifying children for each parent');
426
427 FOR c1_rec IN c1
428 LOOP
429 INSERT INTO cn_sca_denorm_rules(
430 sca_credit_rule_id,
431 ancestor_rule_id,
432 start_date,
433 end_date,
434 rank,
435 level_from_root,
436 relative_rank,
437 root_flag,
438 transaction_source,
439 created_by,
440 creation_date,
441 last_updated_by,
442 last_update_date,
443 last_update_login,
444 org_id)
445 SELECT c1_rec.sca_credit_rule_id,
446 sca_credit_rule_id,
447 c1_rec.start_date,
448 c1_rec.end_date,
449 c1_rec.rank,
450 c1_rec.level_from_root,
451 c1_rec.relative_rank,
452 c1_rec.root_flag,
453 p_txn_src,
454 fnd_global.user_id,
455 SYSDATE,
456 fnd_global.user_id,
457 SYSDATE,
458 fnd_global.login_id,
459 p_org_id
460 FROM cn_sca_credit_rules
461 WHERE sca_credit_rule_id <> c1_rec.sca_credit_rule_id
462 AND transaction_source = p_txn_src
463 AND org_id = p_org_id
464 CONNECT BY PRIOR parent_rule_id = sca_credit_rule_id
465 START WITH sca_credit_rule_id = c1_rec.sca_credit_rule_id
466 AND transaction_source = p_txn_src;
467
468 --+
469 --+ Update calculated rank based on the relative ranks of the ancestors
470 --+
471
472 UPDATE cn_sca_denorm_rules
473 SET calculated_rank = (
474 SELECT SUM(r2.relative_rank)
475 FROM cn_sca_denorm_rules r1,
476 cn_sca_denorm_rules r2
477 WHERE r1.transaction_source = p_txn_src
478 AND r2.transaction_source = p_txn_src
479 AND r1.sca_credit_rule_id = c1_rec.sca_credit_rule_id
480 AND r2.sca_credit_rule_id = r1.ancestor_rule_id
481 AND r2.ancestor_rule_id = r1.ancestor_rule_id),
482 num_rule_attributes = (
483 SELECT count(distinct c.sca_rule_attribute_id)
484 FROM cn_sca_denorm_rules r,
485 cn_sca_credit_rules s,
486 cn_sca_conditions c
487 WHERE r.transaction_source = p_txn_src
488 AND s.transaction_source = p_txn_src
489 AND r.sca_credit_rule_id = c1_rec.sca_credit_rule_id
490 AND r.ancestor_rule_id = s.sca_credit_rule_id
491 AND s.sca_credit_rule_id = c.sca_credit_rule_id)
492 WHERE transaction_source = p_txn_src
493 AND sca_credit_rule_id = c1_rec.sca_credit_rule_id
494 AND org_id = p_org_id;
495
496 --+
497 --+ Resetting the is_denormed flag
498 --+
499
500 UPDATE cn_sca_credit_rules
501 SET is_denormed = 'Y'
502 WHERE sca_credit_rule_id = c1_rec.sca_credit_rule_id
503 AND transaction_source = p_txn_src
504 AND org_id = p_org_id;
505
506 END LOOP;
507 END IF;
508
509 --+
510 --+ Refresh the MVs
511 --+
512
513 --+debugmsg('Rules Sync : For 12.2 MV is converted to view - no refresh process');
514 debugmsg('Rules Sync : Check for environment type before refreshing MV');
515 /* Check introduced for bug14374323 */
516
517 IF (ad_zd.get_edition('PATCH') IS NULL) THEN
518 BEGIN
519 DBMS_MVIEW.REFRESH('CN_SCA_RULE_COND_VALS_MV','C','',TRUE,FALSE,0,4,0,TRUE);
520 EXCEPTION
521 WHEN OTHERS THEN
522 debugmsg('Results Transfer : Unexpected exception');
523 RAISE;
524 END;
525
526 END IF;
527 debugmsg('Rules Sync : End of the MV Refresh');
528
529
530 --+
531 --+ Update Rule Attribute Combinations
532 --+
533
534 debugmsg('Rules Sync : Start of Updating Rule Combination ID');
535
536 cn_sca_denorm_rules_pvt.find_combinations(
537 p_txn_src,
538 p_org_id,
539 l_return_status);
540
541 IF (l_return_status <> 'S') THEN
542 debugmsg('Rules Sync : Error While Updating Rule Combinations');
543 RAISE FND_API.G_EXC_ERROR;
544 END IF;
545
546 debugmsg('Rules Sync : End of Updating Rule Combination ID');
547
548 --+
549 --+ Creating Dynamic SQL Packages for Online and Batch Mode
550 --+
551
552 debugmsg('Rules Sync : Starting Batch Mode Dynamic Package Creation');
553
554 cn_sca_rules_batch_gen_pvt.gen_sca_rules_batch_dyn(
555 p_api_version => l_api_version,
556 x_return_status => l_return_status,
557 x_msg_count => l_msg_count,
558 x_msg_data => l_msg_data,
559 x_transaction_source => p_txn_src,
560 p_org_id => p_org_id);
561 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
562 debugmsg('SCA Rules Sync: Error While Creating Batch Dynamic Package');
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565
566 debugmsg('Rules Sync : Starting Online Mode Dynamic Package Creation');
567
568 cn_sca_rules_online_gen_pvt.gen_sca_rules_onln_dyn(
569 p_api_version => l_api_version,
570 x_return_status => l_return_status,
571 x_msg_count => l_msg_count,
572 x_msg_data => l_msg_data,
573 x_transaction_source => p_txn_src,
574 p_org_id => p_org_id);
575 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
576 debugmsg('Rules Sync : Error While Creating Online Dynamic Package');
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579
580 debugmsg('Rules Sync : Ending Online Mode Dynamic Package Creation');
581 --
582 COMMIT;
583 --
584 retcode := 0;
585 errbuf := 'Rules Synchronization Completed Successfully';
586 debugmsg('Rules Synchronization Completed Successfully');
587 cn_message_pkg.end_batch(l_process_audit_id);
588 --
589
590 EXCEPTION
591
592 WHEN ex_invalid_rules THEN
593 ROLLBACK;
594 cn_message_pkg.end_batch(l_process_audit_id);
595 debugmsg('Rules Sync : Exception: ex_invalid_rules');
596 conc_status := fnd_concurrent.set_completion_status(
597 status => 'ERROR',
598 message => '');
599
600 WHEN l_no_rule_ex THEN
601 ROLLBACK;
602 debugmsg('Rules Sync : No Rules available in CN_SCA_CREDIT_RULES table');
603 cn_message_pkg.end_batch(l_process_audit_id);
604 conc_status := fnd_concurrent.set_completion_status(
605 status => 'ERROR',
606 message => '');
607
608 WHEN FND_API.G_EXC_ERROR THEN
609 ROLLBACK;
610 debugmsg('Rules Sync : Execution Error');
611 cn_message_pkg.end_batch(l_process_audit_id);
612 conc_status := fnd_concurrent.set_completion_status(
613 status => 'ERROR',
614 message => '');
615
616 WHEN OTHERS THEN
617 ROLLBACK;
618 debugmsg('Rules Sync : Unexpected exception');
619 debugmsg('Oracle Error: '||SQLERRM);
620 cn_message_pkg.end_batch(l_process_audit_id);
621 conc_status := fnd_concurrent.set_completion_status(
622 status => 'ERROR',
623 message => '');
624 END;
625 --
626 END;