1 package body MSC_PMF_PKG as
2 /* $Header: MSCXPMFB.pls 120.1 2007/10/10 10:42:41 hbinjola ship $ */
3
4
5 procedure process_pmf_thresholds(p_multiple_measures in boolean,
6 p_measure_short_name in varchar2);
7
8
9 /* *****************************************************************
10 get_threshold (exception_type)
11 used for both seeded as well as user-defined exceptions
12
13 */
14
15 function get_threshold(p_exception_type in number,
16 p_company_id in number,
17 p_company_site_id in number,
18 p_inventory_item_id in number,
19 p_supplier_id in number,
20 p_supplier_site_id in number,
21 p_customer_id in number,
22 p_customer_site_id in number,
23 p_excp_time in date)
24 return number
25
26 is
27
28 l_thresh_value number;
29 rec_found boolean;
30
31 begin
32
33 --dbms_output.put_line('get_threshold:exception_type');
34
35 if ((p_exception_type is null) or (p_company_id is null)) then
36 -- TODO flag error
37 return 0;
38 end if;
39
40 /*
41 supplier-facing seeded exceptions order of processing:
42 (specific to general)
43 1. <company, company-site, item, supplier, supplier-site>
44 2. <company, company-site, item, supplier, null>
45 3. <company, null, item, supplier, null>
46 4. <company, null, null, supplier, null>
47 5. <company, null, null, null, null>
48
49
50 customer-facing seeded exceptions order of processing:
51 (specific to general)
52 1. <company, company-site, item, customer, customer-site>
53 2. <company, company-site, item, customer, null>
54 3. <company, null, item, customer, null>
55 4. <company, null, null, customer, null>
56 5. <company, null, null, null, null>
57
58 Since in the api call it is not specified whether the call is for a
59 customer-facing or supplier-facing exception, we combine the above as
60 follows:
61
62 seeded exceptions order of processing: (specific to general)
63
64 1. <company, company-site, item, supplier, supplier-site> or
65 <company, company-site, item, customer, customer-site>
66
67 2. <company, company-site, item, supplier, null> or
68 <company, company-site, item, customer, null>
69
70 3. <company, null, item, supplier, null> or
71 <company, null, item, customer, null>
72
73 4. <company, null, null, supplier, null> or
74 <company, null, null, customer, null>
75
76 5. <company, null, null, null, null> or
77 <company, null, null, null, null>
78
79 */
80
81
82 l_thresh_value := 0;
83 rec_found := false;
84
85 /* case 1
86 <company, company-site, item, supplier, supplier-site> or
87 <company, company-site, item, customer, customer-site>
88 */
89
90 --dbms_output.put_line('get_threshold:case 1');
91
92 if ((p_company_site_id is not null) and
93 (p_inventory_item_id is not null) and
94 (p_supplier_id is not null) and
95 (p_supplier_site_id is not null)) then
96
97 begin
98 rec_found := true;
99
100 select value
101 into l_thresh_value
102 from msc_exception_threshold_values
103 where exception_type = p_exception_type
104 and company_id = p_company_id
105 and company_site_id = p_company_site_id
106 and inventory_item_id = p_inventory_item_id
107 and supplier_id = p_supplier_id
108 and supplier_site_id = p_supplier_site_id;
109
110 exception
111 when no_data_found then
112 rec_found := false;
113 when too_many_rows then
114 --TODO error processing
115 null;
116 end;
117
118 elsif ((p_company_site_id is not null) and
119 (p_inventory_item_id is not null) and
120 (p_customer_id is not null) and
121 (p_customer_site_id is not null)) then
122
123 begin
124 rec_found := true;
125
126 select value
127 into l_thresh_value
128 from msc_exception_threshold_values
129 where exception_type = p_exception_type
130 and company_id = p_company_id
131 and company_site_id = p_company_site_id
132 and inventory_item_id = p_inventory_item_id
133 and customer_id = p_customer_id
134 and customer_site_id = p_customer_site_id;
135
136 exception
137 when no_data_found then
138 rec_found := false;
139 when too_many_rows then
140 --TODO error processing
141 null;
142 end;
143
144 end if;
145
146
147 --dbms_output.put_line('get_threshold: done case 1');
148
149 if (rec_found = false) then
150
151 --dbms_output.put_line('get_threshold: case 2');
152 /* case 2:
153 <company, company-site, item, supplier, null> or
154 <company, company-site, item, customer, null>
155 */
156
157 if ((p_company_site_id is not null) and
158 (p_inventory_item_id is not null) and
159 (p_supplier_id is not null)) then
160
161 begin
162 rec_found := true;
163
164 select value
165 into l_thresh_value
166 from msc_exception_threshold_values
167 where exception_type = p_exception_type
168 and company_id = p_company_id
169 and company_site_id = p_company_site_id
170 and inventory_item_id = p_inventory_item_id
171 and supplier_id = p_supplier_id;
172
173 exception
174 when no_data_found then
175 rec_found := false;
176 when too_many_rows then
177 --TODO error processing
178 null;
179 end;
180
181 elsif ((p_company_site_id is not null) and
182 (p_inventory_item_id is not null) and
183 (p_customer_id is not null)) then
184
185 begin
186 rec_found := true;
187
188 select value
189 into l_thresh_value
190 from msc_exception_threshold_values
191 where exception_type = p_exception_type
192 and company_id = p_company_id
193 and company_site_id = p_company_site_id
194 and inventory_item_id = p_inventory_item_id
195 and customer_id = p_customer_id;
196
197 exception
198 when no_data_found then
199 rec_found := false;
200 when too_many_rows then
201 --TODO error processing
202 null;
203 end;
204
205 end if;
206 end if;
207
208 --dbms_output.put_line('get_threshold: done case 2');
209
210 if (rec_found = false) then
211
212 --dbms_output.put_line('get_threshold: case 3');
213
214 /* case 3:
215 <company, null, item, supplier, null> or
216 <company, null, item, customer, null>
217 */
218
219 if ((p_inventory_item_id is not null) and
220 (p_supplier_id is not null)) then
221
222 begin
223 rec_found := true;
224
225 select value
226 into l_thresh_value
227 from msc_exception_threshold_values
228 where exception_type = p_exception_type
229 and company_id = p_company_id
230 and inventory_item_id = p_inventory_item_id
231 and supplier_id = p_supplier_id;
232
233 exception
234 when no_data_found then
235 rec_found := false;
236 when too_many_rows then
237 --TODO error processing
238 null;
239 end;
240
241 elsif ((p_inventory_item_id is not null) and
242 (p_customer_id is not null)) then
243
244 begin
245 rec_found := true;
246
247 select value
248 into l_thresh_value
249 from msc_exception_threshold_values
250 where exception_type = p_exception_type
251 and company_id = p_company_id
252 and inventory_item_id = p_inventory_item_id
253 and customer_id = p_customer_id;
254
255 exception
256 when no_data_found then
257 rec_found := false;
258 when too_many_rows then
259 --TODO error processing
260 null;
261 end;
262
263 end if;
264 end if;
265
266 --dbms_output.put_line('get_threshold: done case 3');
267
268 if (rec_found = false) then
269
270 --dbms_output.put_line('get_threshold: case 4');
271
272 /* case 4:
273 <company, null, null, supplier, null> or
274 <company, null, null, customer, null>
275 */
276
277 if (p_supplier_id is not null) then
278
279 begin
280 rec_found := true;
281
282 select value
283 into l_thresh_value
284 from msc_exception_threshold_values
285 where exception_type = p_exception_type
286 and company_id = p_company_id
287 and supplier_id = p_supplier_id;
288
289 exception
290 when no_data_found then
291 rec_found := false;
292 when too_many_rows then
293 --TODO error processing
294 null;
295 end;
296
297 elsif (p_customer_id is not null) then
298
299 begin
300 rec_found := true;
301
302 select value
303 into l_thresh_value
304 from msc_exception_threshold_values
305 where exception_type = p_exception_type
306 and company_id = p_company_id
307 and customer_id = p_customer_id;
308
309 exception
310 when no_data_found then
311 rec_found := false;
312 when too_many_rows then
313 --TODO error processing
314 null;
315 end;
316
317 end if;
318 end if;
319
320 --dbms_output.put_line('get_threshold: done case 4');
321
322 if (rec_found = false) then
323
324 --dbms_output.put_line('get_threshold: case 5');
325
326 /* case 5:
327 <company, null, null, null, null> or
328 <company, null, null, null, null>
329 */
330
331 begin
332 rec_found := true;
333
334 select value
335 into l_thresh_value
336 from msc_exception_threshold_values
337 where exception_type = p_exception_type
338 and company_id = p_company_id
339 and company_site_id is NULL
340 and inventory_item_id is NULL
341 and supplier_id is NULL
342 and supplier_site_id is NULL
343 and customer_id is NULL
344 and customer_site_id is NULL; ---Added for FP-bug#6472941;;
345
346 exception
347 when no_data_found then
348 rec_found := false;
349 when too_many_rows then
350 --TODO error processing
351 null;
352 end;
353
354 end if;
355
356 --dbms_output.put_line('get_threshold: done case 5');
357
358 --dbms_output.put_line('get_threshold:thresh_value=' || l_thresh_value);
359
360 return l_thresh_value;
361
362 end;
363
364
365 /* *****************************************************************
366 process_pmf_thresholds - deprecated
367
368 */
369
370
371 procedure process_pmf_thresholds
372
373 is
374
375 begin
376
377 process_pmf_thresholds(true, null);
378
379 end;
380
381
382
383 /* *****************************************************************
384 process_pmf_thresholds - deprecated
385
386 */
387
388 procedure process_pmf_thresholds(p_multiple_measures in boolean,
389 p_measure_short_name in varchar2)
390 is
391
392
393 begin
394
395 null;
396
397 end;
398
399
400
401 /* *****************************************************************
402 get_threshold (measure_short_name) - deprecated
403
404 */
405
406
407 function get_threshold(p_measure_short_name in varchar2,
408 p_company_id in number,
409 p_company_site_id in number,
410 p_inventory_item_id in number,
411 p_supplier_id in number,
412 p_supplier_site_id in number,
413 p_customer_id in number,
414 p_customer_site_id in number,
415 p_excp_time in date)
416 return number
417
418 is
419
420 begin
421
422 --dbms_output.put_line('get_threshold:measure_short_name');
423
424 return 0;
425
426 end;
427
428
429
430
431 /* *****************************************************************
432 get_threshold (measure_short_name) - deprecated
433
434 */
435
436
437 function get_threshold2(p_measure_short_name in varchar2,
438 p_company_id in number,
439 p_company_site_id in number,
440 p_inventory_item_id in number,
441 p_supplier_id in number,
442 p_supplier_site_id in number,
443 p_customer_id in number,
444 p_customer_site_id in number,
445 p_excp_time in date)
446 return number
447 is
448
449 begin
450 --dbms_output.put_line('get_threshold2:measure_name');
451
452 process_pmf_thresholds(false, p_measure_short_name);
453
454 return 0;
455
456 end;
457
458
459 /* *****************************************************************
460 get_threshold (exception_type) - deprecated
461
462 */
463
464
465 function get_threshold2(p_exception_type in number,
466 p_company_id in number,
467 p_company_site_id in number,
468 p_inventory_item_id in number,
469 p_supplier_id in number,
470 p_supplier_site_id in number,
471 p_customer_id in number,
472 p_customer_site_id in number,
473 p_excp_time in date)
474 return number
475
476 is
477
478 begin
479 --dbms_output.put_line('get_threshold2:exception_type');
480
481 --process_pmf_thresholds(false, l_indicator_short_name);
482
483 return 0;
484
485 end;
486
487
488
489
490 end MSC_PMF_PKG;
[Home] [Help]