1 PACKAGE BODY msc_gen_priorities AS
2 /* $Header: MSCPRIRB.pls 120.3 2007/01/17 19:34:28 hulu ship $ */
3
4 Procedure gen_priorities(
5 p_rule_set_id in number) is
6
7 CURSOR dmd_type_c IS
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 (+)
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,
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
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
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
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;
69
70 l_user_id number := fnd_global.user_id;
71 v_priority number :=0;
72 v_dmd_type_rec dmd_type_c%ROWTYPE;
73 v_prev_type_pri number := 0;
74 v_prev_class_pri number := 0;
75 begin
76
77 delete msc_drp_pri_rules_calc
78 where rule_set_id = p_rule_set_id;
79
80 Open dmd_type_c;
81 LOOP
82 Fetch dmd_type_c Into v_dmd_type_rec;
83 EXIT when dmd_type_c%NOTFOUND;
84 if v_dmd_type_rec.type_pri <> v_prev_type_pri or
85 v_dmd_type_rec.class_pri <> v_prev_class_pri then
86 v_priority := v_priority+1;
87 end if;
88 --dbms_output.put_line('v_priority='||v_priority||','||v_dmd_type_rec.demand_class||','||v_dmd_type_rec.demand_type);
89 insert into msc_drp_pri_rules_calc
90 (
91 rule_set_id,
92 demand_type,
93 demand_class,
94 priority,
95 last_update_date,
96 last_updated_by,
97 creation_date,
98 created_by,
99 last_update_login
100 )
101 values(
102 p_rule_set_id,
103 v_dmd_type_rec.demand_type,
104 v_dmd_type_rec.demand_class,
105 v_priority,
106 sysdate,
107 l_user_id,
108 sysdate,
109 l_user_id,
110 l_user_id
111 );
112 v_prev_type_pri := v_dmd_type_rec.type_pri;
113 v_prev_class_pri := v_dmd_type_rec.class_pri;
114
115 END LOOP;
116 CLOSE dmd_type_c;
117
118 -- add safety stock,
119 for a in 4..6 loop
120 v_priority := v_priority +1;
121 insert into msc_drp_pri_rules_calc
122 (
123 rule_set_id,
124 demand_type,
125 demand_class,
126 priority,
127 last_update_date,
128 last_updated_by,
129 creation_date,
130 created_by,
131 last_update_login
132 )
133 values(
134 p_rule_set_id,
135 a,
136 '-1',
137 v_priority,
138 sysdate,
139 l_user_id,
140 sysdate,
141 l_user_id,
142 l_user_id
143 );
144
145 end loop;
146 end gen_priorities;
147
148 FUNCTION all_defined(p_rule_set_id in number) return boolean IS
149
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;
159
160 v_dmd_type number;
161 v_dmd_class varchar2(100);
162 v_cum varchar2(10);
163 BEGIN
164 /* rule type
165 1 Demand Type
166 2 Demand Class
167 3 Demand Type - Demand Class
168
169 demand type
170 1 Sales Orders
171 2 Over-Consumed Sales Orders
172 3 Forecast
173 */
174
175 /* return true if
176 a. rule_type =1 and all three demand type defined, or
177 b. rule_type = 3 and all other demand class defined
178 */
179
180 p_rule_type := 1; -- Demand Type
181 OPEN defined_types_c;
182 LOOP
183 FETCH defined_types_c INTO v_dmd_type, v_dmd_class;
184 EXIT WHEN defined_types_c%NOTFOUND;
185 v_cum := v_cum||v_dmd_type;
186 END LOOP;
187 CLOSE defined_types_c;
188 FOR a in 1..3 LOOP
189 IF instr(v_cum, a) <= 0 then
190 -- no dmd type rule defined for dmd type a
191 -- for dmd type - class rule type, need to have all other dmd class
192 p_rule_type := 3; -- Demand Type - Demand Class
193 p_demand_type := a;
194 OPEN defined_types_c;
195 LOOP
196 FETCH defined_types_c INTO v_dmd_type, v_dmd_class;
197 EXIT WHEN defined_types_c%NOTFOUND;
198 if v_dmd_class = '-1' then -- all other
199 v_cum := v_cum ||a;
200 exit;
201 end if;
202 END LOOP;
203 CLOSE defined_types_c;
204 END IF; -- IF instr(v_cum, a) <= 0 then
205 END LOOP; -- FOR a in 1..3 LOOP
206
207 if v_cum is null then
208 -- only rule_type 2 is defined
209 return false;
210 end if;
211
212 FOR a in 1..3 LOOP
213 IF instr(v_cum, a) <= 0 then
214 -- still no demand type defined
215 return false;
216 END IF;
217 END LOOP;
218
219 -- v_cum has all three dmd types
220 return true;
221
222 END all_defined;
223
224 end msc_gen_priorities;
[Home] [Help]