eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CN_SCA_DENORM_RULES_PVT dependencies on CN_SCA_DENORM_RULES

Line 1: PACKAGE BODY CN_SCA_DENORM_RULES_PVT AS

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 |

Line 10: -- CN_SCA_DENORM_RULES_PVT

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

Line 48: FROM cn_sca_denorm_rules dr

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;

Line 56: FROM cn_sca_denorm_rules

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: --

Line 104: --+ Reset RULE_ATTR_COMB_ID in CN_SCA_DENORM_RULES and update with

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

Line 108: UPDATE cn_sca_denorm_rules

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: --

Line 123: --+ in cn_sca_denorm_rules table.

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:

Line 128: UPDATE cn_sca_denorm_rules

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:

Line 140: UPDATE cn_sca_denorm_rules dr

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

Line 145: FROM cn_sca_denorm_rules idr,

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

Line 196: FROM cn_sca_denorm_rules dr

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)

Line 260: FROM cn_sca_denorm_rules csdr

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'

Line 336: DELETE FROM cn_sca_denorm_rules

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');

Line 360: DELETE FROM cn_sca_denorm_rules csdr

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'

Line 380: INSERT INTO cn_sca_denorm_rules(

376: --+
377: --+ Insert rules without parents and their entire hierarchy.
378: --+
379:
380: INSERT INTO cn_sca_denorm_rules(
381: sca_credit_rule_id,
382: ancestor_rule_id,
383: start_date,
384: end_date,

Line 429: INSERT INTO cn_sca_denorm_rules(

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,

Line 472: UPDATE cn_sca_denorm_rules

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

Line 475: FROM cn_sca_denorm_rules r1,

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

Line 476: cn_sca_denorm_rules r2

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

Line 484: FROM cn_sca_denorm_rules r,

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

Line 536: cn_sca_denorm_rules_pvt.find_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: