8: select d_type.priority type_pri,
9: nvl(d_class.priority,d_type.priority) class_pri,
10: d_type.demand_type,
11: nvl(d_class.demand_class, d_type.demand_class) demand_class
12: from msc_drp_pri_rules_specified d_type,
13: msc_drp_pri_rules_specified d_class
14: where d_type.rule_set_id = p_rule_set_id
15: and d_type.rule_type =1 -- demand type
16: and d_type.rule_set_id = d_class.rule_set_id (+)
9: nvl(d_class.priority,d_type.priority) class_pri,
10: d_type.demand_type,
11: nvl(d_class.demand_class, d_type.demand_class) demand_class
12: from msc_drp_pri_rules_specified d_type,
13: msc_drp_pri_rules_specified d_class
14: where d_type.rule_set_id = p_rule_set_id
15: and d_type.rule_type =1 -- demand type
16: and d_type.rule_set_id = d_class.rule_set_id (+)
17: and d_class.rule_type(+) =2 -- demand class
15: and d_type.rule_type =1 -- demand type
16: and d_type.rule_set_id = d_class.rule_set_id (+)
17: and d_class.rule_type(+) =2 -- demand class
18: and not exists (select 1
19: from msc_drp_pri_rules_specified
20: where rule_type = 3 -- demand type-demand class
21: and rule_set_id = d_type.rule_set_id
22: and demand_type = d_type.demand_type
23: and demand_class = d_class.demand_class)
24: union select d_type.priority type_pri,
25: d_type.priority class_pri,
26: d_type.demand_type,
27: d_type.demand_class
28: from msc_drp_pri_rules_specified d_type
29: where d_type.rule_set_id = p_rule_set_id
30: and d_type.rule_type =3
31: union select d_type.priority type_pri,
32: 999 class_pri,
30: and d_type.rule_type =3
31: union select d_type.priority type_pri,
32: 999 class_pri,
33: d_type.demand_type, '-1'
34: from msc_drp_pri_rules_specified d_type
35: where d_type.rule_set_id = p_rule_set_id
36: and d_type.rule_type =1
37: and exists (select 1
38: from msc_drp_pri_rules_specified
34: from msc_drp_pri_rules_specified d_type
35: where d_type.rule_set_id = p_rule_set_id
36: and d_type.rule_type =1
37: and exists (select 1
38: from msc_drp_pri_rules_specified
39: where rule_type = 2
40: and rule_set_id = d_type.rule_set_id)
41: and not exists (select 1
42: from msc_drp_pri_rules_specified
38: from msc_drp_pri_rules_specified
39: where rule_type = 2
40: and rule_set_id = d_type.rule_set_id)
41: and not exists (select 1
42: from msc_drp_pri_rules_specified
43: where rule_type = 2
44: and rule_set_id = d_type.rule_set_id
45: and demand_class = '-1')
46: and not exists (select 1
43: where rule_type = 2
44: and rule_set_id = d_type.rule_set_id
45: and demand_class = '-1')
46: and not exists (select 1
47: from msc_drp_pri_rules_specified
48: where rule_type = 3 -- demand type-demand class
49: and rule_set_id = d_type.rule_set_id
50: and demand_type = d_type.demand_type
51: and demand_class = '-1')
52: union select d_class.priority type_pri, --5762540,
53: d_class.priority class_pri, -- when FC, SO, OverConsumed is not
54: d_type.demand_type, -- defined in demand_type,
55: d_class.demand_class demand_class -- but in demand_type/demand_class
56: from msc_drp_pri_rules_specified d_type,
57: msc_drp_pri_rules_specified d_class
58: where d_type.rule_set_id = p_rule_set_id
59: and d_type.rule_type =3 -- demand type-demand class
60: and d_type.rule_set_id = d_class.rule_set_id
53: d_class.priority class_pri, -- when FC, SO, OverConsumed is not
54: d_type.demand_type, -- defined in demand_type,
55: d_class.demand_class demand_class -- but in demand_type/demand_class
56: from msc_drp_pri_rules_specified d_type,
57: msc_drp_pri_rules_specified d_class
58: where d_type.rule_set_id = p_rule_set_id
59: and d_type.rule_type =3 -- demand type-demand class
60: and d_type.rule_set_id = d_class.rule_set_id
61: and d_class.rule_type =2 -- demand class
60: and d_type.rule_set_id = d_class.rule_set_id
61: and d_class.rule_type =2 -- demand class
62: and d_type.demand_class <> d_class.demand_class
63: and not exists (select 1
64: from msc_drp_pri_rules_specified
65: where rule_type = 1 -- demand type
66: and rule_set_id = d_type.rule_set_id
67: and demand_type = d_type.demand_type)
68: order by type_pri, class_pri;
150: p_rule_type number;
151: p_demand_type number;
152: cursor defined_types_c is
153: select demand_type, demand_class
154: from msc_drp_pri_rules_specified
155: where rule_set_id = p_rule_set_id
156: and rule_type = p_rule_type
157: and demand_type = nvl(p_demand_type, demand_type)
158: order by demand_type, demand_class;