[Home] [Help]
PACKAGE BODY: APPS.POA_SUPPLIER_CONSOLIDATION_PK
Source
1 PACKAGE BODY poa_supplier_consolidation_pk AS
2 /* $Header: poaspcob.pls 115.8 2002/12/27 21:29:36 iali ship $ */
3
4 ALL_SUPPLIER CONSTANT INTEGER := -9999;
5 NO_PREF_SUPPLIER CONSTANT INTEGER := -1;
6
7 NULL_VALUE CONSTANT INTEGER := -23453;
8 MAGIC_STRING CONSTANT VARCHAR2(10) := '734jkhJK24';
9 BUFFER_SIZE_LEN CONSTANT INTEGER := 1000000;
10
11
12 -- ========================================================================
13 --
14 -- Calculate the potential savings from consolidating supplier(s) to a
15 -- preferred supplier.
16 -- This procedure is called from Supplier Consolidation.
17 --
18 -- ========================================================================
19
20 PROCEDURE calculate_savings(
21 p_item_id IN NUMBER,
22 p_pref_supplier_id IN NUMBER,
23 p_cons_supplier_id IN NUMBER,
24 p_defect_cost IN NUMBER,
25 p_del_excp_cost IN NUMBER,
26 p_currency_code IN VARCHAR2,
27 p_start_date IN DATE,
28 p_end_date IN DATE,
29 p_user_id IN NUMBER,
30 p_bucket_type IN NUMBER,
31 p_price_savings OUT NOCOPY NUMBER,
32 p_quality_savings OUT NOCOPY NUMBER,
33 p_delivery_savings OUT NOCOPY NUMBER,
34 p_total_savings OUT NOCOPY NUMBER) IS
35
36 VERSION CONSTANT CHAR(80) :=
37 '$Header: poaspcob.pls 115.8 2002/12/27 21:29:36 iali ship $';
38
39 -- Consolidated Supplier(s) info
40 v_cons_shipment_id NUMBER; -- Shipment id
41 v_cons_purch_price NUMBER; -- Purchase price
42 v_cons_qty_purchased NUMBER; -- Qty purchased
43 v_cons_qty_ordered NUMBER; -- Qty ordered
44 v_cons_qty_received NUMBER; -- Qty received
45 v_cons_qty_rejected NUMBER; -- Qty rejected
46 v_cons_qty_del_excp NUMBER; -- Qty of delivery exception
47 v_cons_date DATE; -- Date dimension
48
49 -- Preferred Supplier info
50 v_pref_purch_price NUMBER; -- Avg price
51 v_pref_blanket_price NUMBER; -- Blanket agreement price
52 v_pref_blanket_price2 NUMBER; -- Blanket agreement price
53 v_pref_pct_defect NUMBER; -- Avg % of defect
54 v_pref_pct_del_excp NUMBER; -- Avg % of delivery exception
55
56 -- General
57 v_price_savings NUMBER := 0;
58 v_quality_savings NUMBER := 0;
59 v_delivery_savings NUMBER := 0;
60 x_progress VARCHAR2(3) := NULL;
61
62 --
63 -- Select the information for the CONSOLIDATED supplier(s).
64 -- It can either be from a single supplier (specified in the parameter)
65 -- or from all available suppliers within the period window.
66 --
67 CURSOR c_cons_supplier IS
68 SELECT psp.po_shipment_id,
69 NVL(psp.purchase_price, 0) *
70 DECODE(gl_currency_api.rate_exists(psp.currency_code,
71 p_currency_code, psp.rate_date,
72 psp.rate_type),
73 'Y',
74 gl_currency_api.get_rate(psp.currency_code, p_currency_code,
75 psp.rate_date, psp.rate_type),
76 1),
77 NVL(psp.quantity_purchased, 0), NVL(psp.quantity_ordered, 0),
78 NVL(psp.quantity_received, 0), NVL(psp.quantity_rejected, 0),
79 NVL((psp.quantity_received_late + psp.quantity_received_early +
80 psp.quantity_past_due), 0),
81 psp.date_dimension
82 FROM poa_bis_supplier_performance psp
83 WHERE psp.item_id = p_item_id
84 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
85 AND (psp.supplier_id = p_cons_supplier_id
86 OR ALL_SUPPLIER = p_cons_supplier_id)
87 ORDER BY psp.currency_code;
88
89 CURSOR c_cons_supplier_glb_sec IS
90 SELECT psp.po_shipment_id,
91 NVL(psp.purchase_price, 0) *
92 DECODE(gl_currency_api.rate_exists(psp.currency_code,
93 p_currency_code, psp.rate_date,
94 psp.rate_type),
95 'Y',
96 gl_currency_api.get_rate(psp.currency_code, p_currency_code,
97 psp.rate_date, psp.rate_type),
98 1),
99 NVL(psp.quantity_purchased, 0), NVL(psp.quantity_ordered, 0),
100 NVL(psp.quantity_received, 0), NVL(psp.quantity_rejected, 0),
101 NVL((psp.quantity_received_late + psp.quantity_received_early +
102 psp.quantity_past_due), 0),
103 psp.date_dimension
104 FROM poa_bis_supplier_performance psp
105 WHERE psp.item_id = p_item_id
106 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
107 AND (psp.supplier_id = p_cons_supplier_id
108 OR ALL_SUPPLIER = p_cons_supplier_id)
109 AND (psp.org_id in (SELECT id FROM bis_operating_units_v
110 WHERE responsibility_id in
111 (SELECT responsibility_id
112 FROM fnd_user_resp_groups
113 WHERE user_id = p_user_id
114 AND sysdate BETWEEN start_date
115 AND NVL(end_date, sysdate+1)))
116 OR psp.org_id IS NULL)
117 ORDER BY psp.currency_code;
118
119 --
120 -- Get the blanket price for the preferred supplier
121 --
122 CURSOR c_blanket_break IS
123 SELECT psc2.price_override *
124 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
125 p_currency_code,
126 NVL(phc2.rate_date, phc2.creation_date),
127 NVL(phc2.rate_type, 'Corporate')),
128 'Y',
129 gl_currency_api.get_rate(phc2.currency_code,
130 p_currency_code,
131 NVL(phc2.rate_date, phc2.creation_date),
132 NVL(phc2.rate_type, 'Corporate')),
133 1) blanket_price
134 FROM po_headers_all phc2,
135 po_headers_all phc1,
136 po_lines_all plc2,
137 po_lines_all plc1,
138 po_line_locations_all psc2,
139 po_line_locations_all psc1
140 WHERE psc1.line_location_id = v_cons_shipment_id
141 AND plc1.po_line_id = psc1.po_line_id
142 AND plc1.po_header_id = psc1.po_header_id
143 AND phc1.po_header_id = psc1.po_header_id
144 AND plc2.item_id = plc1.item_id
145 AND NVL(plc2.item_revision, NULL_VALUE) =
146 NVL(plc1.item_revision, NULL_VALUE)
147 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
148 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
149 AND phc2.po_header_id = plc2.po_header_id
150 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
151 AND NVL(phc2.end_date, v_cons_date)
152 AND NVL(phc2.currency_code, MAGIC_STRING) =
153 NVL(phc1.currency_code, MAGIC_STRING)
154 AND psc2.po_line_id = plc2.po_line_id
155 AND psc2.po_header_id = plc2.po_header_id
156 AND psc2.shipment_type = 'PRICE BREAK'
157 AND psc2.po_release_id IS NULL
158 AND psc2.quantity <= psc1.quantity
159 AND (psc2.ship_to_location_id = psc1.ship_to_location_id
160 OR psc2.ship_to_location_id IS NULL)
161 AND phc2.vendor_id = p_pref_supplier_id
162 ORDER BY psc2.quantity desc, blanket_price asc;
163
164 CURSOR c_blanket_break_glb_sec IS
165 SELECT psc2.price_override *
166 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
167 p_currency_code,
168 NVL(phc2.rate_date, phc2.creation_date),
169 NVL(phc2.rate_type, 'Corporate')),
170 'Y',
171 gl_currency_api.get_rate(phc2.currency_code,
172 p_currency_code,
173 NVL(phc2.rate_date, phc2.creation_date),
174 NVL(phc2.rate_type, 'Corporate')),
175 1) blanket_price
176 FROM po_headers_all phc2,
177 po_headers_all phc1,
178 po_lines_all plc2,
179 po_lines_all plc1,
180 po_line_locations_all psc2,
181 po_line_locations_all psc1
182 WHERE psc1.line_location_id = v_cons_shipment_id
183 AND plc1.po_line_id = psc1.po_line_id
184 AND plc1.po_header_id = psc1.po_header_id
185 AND phc1.po_header_id = psc1.po_header_id
186 AND plc2.item_id = plc1.item_id
187 AND NVL(plc2.item_revision, NULL_VALUE) =
188 NVL(plc1.item_revision, NULL_VALUE)
189 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
190 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
191 AND phc2.po_header_id = plc2.po_header_id
192 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
193 AND NVL(phc2.end_date, v_cons_date)
194 AND NVL(phc2.currency_code, MAGIC_STRING) =
195 NVL(phc1.currency_code, MAGIC_STRING)
196 AND psc2.po_line_id = plc2.po_line_id
197 AND psc2.po_header_id = plc2.po_header_id
198 AND psc2.shipment_type = 'PRICE BREAK'
199 AND psc2.po_release_id IS NULL
200 AND psc2.quantity <= psc1.quantity
201 AND (psc2.ship_to_location_id = psc1.ship_to_location_id
202 OR psc2.ship_to_location_id IS NULL)
203 AND phc2.vendor_id = p_pref_supplier_id
204 AND (phc2.org_id in (SELECT id FROM bis_operating_units_v
205 WHERE responsibility_id in
206 (SELECT responsibility_id
207 FROM fnd_user_resp_groups
208 WHERE user_id = p_user_id
209 AND sysdate BETWEEN start_date
210 AND NVL(end_date, sysdate+1)))
211 OR phc2.org_id IS NULL)
212 ORDER BY psc2.quantity desc, blanket_price asc;
213
214 CURSOR c_blanket_nobreak IS
215 SELECT plc2.unit_price *
216 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
217 p_currency_code,
218 NVL(phc2.rate_date, phc2.creation_date),
219 NVL(phc2.rate_type, 'Corporate')),
220 'Y',
221 gl_currency_api.get_rate(phc2.currency_code,
222 p_currency_code,
223 NVL(phc2.rate_date, phc2.creation_date),
224 NVL(phc2.rate_type, 'Corporate')),
225 1) blanket_price
226 FROM po_headers_all phc2,
227 po_headers_all phc1,
228 po_lines_all plc2,
229 po_lines_all plc1,
230 po_line_locations_all psc1
231 WHERE psc1.line_location_id = v_cons_shipment_id
232 AND plc1.po_line_id = psc1.po_line_id
233 AND plc1.po_header_id = psc1.po_header_id
234 AND phc1.po_header_id = psc1.po_header_id
235 AND plc2.item_id = plc1.item_id
236 AND NVL(plc2.item_revision, NULL_VALUE) =
237 NVL(plc1.item_revision, NULL_VALUE)
238 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
239 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
240 AND phc2.po_header_id = plc2.po_header_id
241 AND phc2.type_lookup_code = 'BLANKET'
242 AND phc2.vendor_id = p_pref_supplier_id
243 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
244 AND NVL(phc2.end_date, v_cons_date)
245 AND NVL(phc2.currency_code, MAGIC_STRING) =
246 NVL(phc1.currency_code, MAGIC_STRING)
247 ORDER BY blanket_price;
248
249 CURSOR c_blanket_nobreak_glb_sec IS
250 SELECT plc2.unit_price *
251 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
252 p_currency_code,
253 NVL(phc2.rate_date, phc2.creation_date),
254 NVL(phc2.rate_type, 'Corporate')),
255 'Y',
256 gl_currency_api.get_rate(phc2.currency_code,
257 p_currency_code,
258 NVL(phc2.rate_date, phc2.creation_date),
259 NVL(phc2.rate_type, 'Corporate')),
260 1) blanket_price
261 FROM po_headers_all phc2,
262 po_headers_all phc1,
263 po_lines_all plc2,
264 po_lines_all plc1,
265 po_line_locations_all psc1
266 WHERE psc1.line_location_id = v_cons_shipment_id
267 AND plc1.po_line_id = psc1.po_line_id
268 AND plc1.po_header_id = psc1.po_header_id
269 AND phc1.po_header_id = psc1.po_header_id
270 AND plc2.item_id = plc1.item_id
271 AND NVL(plc2.item_revision, NULL_VALUE) =
272 NVL(plc1.item_revision, NULL_VALUE)
273 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
274 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
275 AND phc2.po_header_id = plc2.po_header_id
276 AND phc2.type_lookup_code = 'BLANKET'
277 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
278 AND NVL(phc2.end_date, v_cons_date)
279 AND NVL(phc2.currency_code, MAGIC_STRING) =
280 NVL(phc1.currency_code, MAGIC_STRING)
281 AND phc2.vendor_id = p_pref_supplier_id
282 AND (phc2.org_id in (SELECT id FROM bis_operating_units_v
283 WHERE responsibility_id in
284 (SELECT responsibility_id
285 FROM fnd_user_resp_groups
286 WHERE user_id = p_user_id
287 AND sysdate BETWEEN start_date
288 AND NVL(end_date, sysdate+1)))
289 OR phc2.org_id IS NULL)
290 ORDER BY blanket_price;
291
292
293 BEGIN
294 -- dbms_output.enable(BUFFER_SIZE_LEN);
295
296 p_price_savings := 0;
297 p_quality_savings := 0;
298 p_delivery_savings := 0;
299 p_total_savings := 0;
300
301 -- ------------------------------------------------------------------------
302 -- Get PREFERRED supplier info.
303 -- The average is calculated for the whole period window.
304 -- These will be compared to the ones from the consolidated supplier to
305 -- calculate the savings.
306 -- ------------------------------------------------------------------------
307
308 BEGIN
309 x_progress := '001';
310
311 -- Select the average price
312
313 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
314 SELECT SUM(psp.purchase_price *
315 DECODE(gl_currency_api.rate_exists(psp.currency_code,
316 p_currency_code,
317 psp.rate_date,
318 psp.rate_type),
319 'Y',
320 gl_currency_api.get_rate(psp.currency_code,
321 p_currency_code,
322 psp.rate_date, psp.rate_type),
323 1) *
324 psp.quantity_purchased) /
325 SUM(psp.quantity_purchased)
326 INTO v_pref_purch_price
327 FROM poa_bis_supplier_performance psp
328 WHERE psp.item_id = p_item_id
329 AND psp.supplier_id = p_pref_supplier_id
330 AND NVL(psp.quantity_purchased, 0) <> 0
331 AND psp.date_dimension BETWEEN p_start_date AND p_end_date;
332 ELSE
333 SELECT SUM(psp.purchase_price *
334 DECODE(gl_currency_api.rate_exists(psp.currency_code,
335 p_currency_code,
336 psp.rate_date,
337 psp.rate_type),
338 'Y',
339 gl_currency_api.get_rate(psp.currency_code,
340 p_currency_code,
341 psp.rate_date, psp.rate_type),
342 1) *
343 psp.quantity_purchased) /
344 SUM(psp.quantity_purchased)
345 INTO v_pref_purch_price
346 FROM poa_bis_supplier_performance psp
347 WHERE psp.item_id = p_item_id
348 AND NVL(psp.quantity_purchased, 0) <> 0
349 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
350 AND psp.supplier_id = p_pref_supplier_id
351 AND (psp.org_id in (SELECT id FROM bis_operating_units_v
352 WHERE responsibility_id in
353 (SELECT responsibility_id
354 FROM fnd_user_resp_groups
355 WHERE user_id = p_user_id
356 AND sysdate BETWEEN start_date
357 AND NVL(end_date, sysdate+1)))
358 OR psp.org_id IS NULL);
359 END IF;
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 v_pref_purch_price := NO_PREF_SUPPLIER;
364
365 WHEN OTHERS THEN
366 -- dbms_output.put_line('calculate_savings - ' || x_progress
367 -- || ': ' || sqlerrm);
368 po_message_s.sql_error('calculate_savings', x_progress, sqlerrm);
369 RAISE;
370 RETURN;
371 END calculate_savings;
372
373 BEGIN
374 x_progress := '002';
375
376 -- Select the average percentage of defect
377
378 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
379 SELECT SUM(psp.quantity_rejected)/SUM(psp.quantity_received)
380 INTO v_pref_pct_defect
381 FROM poa_bis_supplier_performance psp
382 WHERE psp.item_id = p_item_id
383 AND NVL(psp.quantity_received, 0) <> 0
384 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
385 AND psp.supplier_id = p_pref_supplier_id;
386 ELSE
387 SELECT SUM(psp.quantity_rejected)/SUM(psp.quantity_received)
388 INTO v_pref_pct_defect
389 FROM poa_bis_supplier_performance psp
390 WHERE psp.item_id = p_item_id
391 AND NVL(psp.quantity_received, 0) <> 0
392 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
393 AND psp.supplier_id = p_pref_supplier_id
394 AND (psp.org_id in (SELECT id FROM bis_operating_units_v
395 WHERE responsibility_id in
396 (SELECT responsibility_id
397 FROM fnd_user_resp_groups
398 WHERE user_id = p_user_id
399 AND sysdate BETWEEN start_date
400 AND NVL(end_date, sysdate+1)))
401 OR psp.org_id IS NULL);
402 END IF;
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 v_pref_pct_defect := NO_PREF_SUPPLIER;
406
407 WHEN OTHERS THEN
408 -- dbms_output.put_line('calculate_savings - ' || x_progress
409 -- || ': ' || sqlerrm);
410 po_message_s.sql_error('calculate_savings', x_progress, sqlerrm);
411 RAISE;
412 RETURN;
413 END calculate_savings;
414
415 BEGIN
416 x_progress := '003';
417
418 -- Select the average percentage of delivery exception
419
420 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
421 SELECT SUM(psp.quantity_received_late + psp.quantity_received_early +
422 psp.quantity_past_due)/SUM(psp.quantity_purchased)
423 INTO v_pref_pct_del_excp
424 FROM poa_bis_supplier_performance psp
425 WHERE psp.item_id = p_item_id
426 AND NVL(psp.quantity_purchased, 0) <> 0
427 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
428 AND psp.supplier_id = p_pref_supplier_id;
429 ELSE
430 SELECT SUM(psp.quantity_received_late + psp.quantity_received_early +
431 psp.quantity_past_due)/SUM(psp.quantity_purchased)
432 INTO v_pref_pct_del_excp
433 FROM poa_bis_supplier_performance psp
434 WHERE psp.item_id = p_item_id
435 AND NVL(psp.quantity_purchased, 0) <> 0
436 AND psp.date_dimension BETWEEN p_start_date AND p_end_date
437 AND psp.supplier_id = p_pref_supplier_id
438 AND (psp.org_id in (SELECT id FROM bis_operating_units_v
439 WHERE responsibility_id in
440 (SELECT responsibility_id
441 FROM fnd_user_resp_groups
442 WHERE user_id = p_user_id
443 AND sysdate BETWEEN start_date
444 AND NVL(end_date, sysdate+1)))
445 OR psp.org_id IS NULL);
446 END IF;
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 v_pref_pct_del_excp := NO_PREF_SUPPLIER;
451
452 WHEN OTHERS THEN
453 -- dbms_output.put_line('calculate_savings - ' || x_progress
454 -- || ': ' || sqlerrm);
455 po_message_s.sql_error('calculate_savings', x_progress, sqlerrm);
456 RAISE;
457 RETURN;
458 END calculate_savings;
459
460 --
461 -- No records for the preferred supplier in the period window?
462 --
463 -- Since the previous SQL statements is a SUM, a record w/ NULL values will
464 -- still be returned even if there is no record in the base table that
465 -- satisfies the conditions.
466 -- So, check again here.
467 --
468 IF (v_pref_purch_price IS NULL) THEN
469 v_pref_purch_price := NO_PREF_SUPPLIER;
470 END IF;
471
472 IF (v_pref_pct_defect IS NULL) THEN
473 v_pref_pct_defect := NO_PREF_SUPPLIER;
474 END IF;
475
476 IF (v_pref_pct_del_excp IS NULL) THEN
477 v_pref_pct_del_excp := NO_PREF_SUPPLIER;
478 END IF;
479
480
481 -- dbms_output.put_line('-- Preferred supplier info --');
482 -- dbms_output.put_line('Avg purch price: ' || v_pref_purch_price);
483 -- dbms_output.put_line('Pct of defect : ' || v_pref_pct_defect);
484 -- dbms_output.put_line('Pct of del excp: ' || v_pref_pct_del_excp);
485
486 -- ------------------------------------------------------------------------
487 -- Get consolidated supplier(s) info and calculate savings
488 -- ------------------------------------------------------------------------
489 x_progress := '004';
490
491 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
492 OPEN c_cons_supplier;
493 ELSE
494 OPEN c_cons_supplier_glb_sec;
495 END IF;
496
497 LOOP
498 x_progress := '005';
499
500 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
501 FETCH c_cons_supplier INTO v_cons_shipment_id,
502 v_cons_purch_price,
503 v_cons_qty_purchased,
504 v_cons_qty_ordered,
505 v_cons_qty_received,
506 v_cons_qty_rejected,
507 v_cons_qty_del_excp,
508 v_cons_date;
509 EXIT WHEN c_cons_supplier%NOTFOUND;
510 ELSE
511 FETCH c_cons_supplier_glb_sec INTO v_cons_shipment_id,
512 v_cons_purch_price,
513 v_cons_qty_purchased,
514 v_cons_qty_ordered,
515 v_cons_qty_received,
516 v_cons_qty_rejected,
517 v_cons_qty_del_excp,
518 v_cons_date;
519 EXIT WHEN c_cons_supplier_glb_sec%NOTFOUND;
520 END IF;
521
522
523 --
524 -- If there's no average price for the preferred supplier, get
525 -- blanket price.
526 --
527 -- First look at those blankets w/ price breaks,
528 -- then look at those blankets w/out price breaks
529 -- Because of the ORDER BY clause in the SELECT statements we only need
530 -- to fetch the first record returned.
531 --
532 IF (v_pref_purch_price = NO_PREF_SUPPLIER) THEN
533 v_pref_blanket_price := NO_PREF_SUPPLIER;
534
535 -- Get blankets w/ price breaks
536
537 x_progress := '006';
538
539 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
540 OPEN c_blanket_break;
541 FETCH c_blanket_break INTO v_pref_blanket_price;
542
543 IF c_blanket_break%NOTFOUND THEN
544 v_pref_blanket_price := NO_PREF_SUPPLIER;
545 END IF;
546
547 CLOSE c_blanket_break;
548 ELSE
549 OPEN c_blanket_break_glb_sec;
550 FETCH c_blanket_break_glb_sec INTO v_pref_blanket_price;
551
552 IF c_blanket_break_glb_sec%NOTFOUND THEN
553 v_pref_blanket_price := NO_PREF_SUPPLIER;
554 END IF;
555
556 CLOSE c_blanket_break_glb_sec;
557 END IF;
558
559 -- Get blankets w/out price breaks
560
561 x_progress := '007';
562
563 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
564 OPEN c_blanket_nobreak;
565 FETCH c_blanket_nobreak INTO v_pref_blanket_price2;
566
567 IF c_blanket_nobreak%NOTFOUND THEN
568 v_pref_blanket_price2 := NO_PREF_SUPPLIER;
569 END IF;
570
571 CLOSE c_blanket_nobreak;
572 ELSE
573 OPEN c_blanket_nobreak_glb_sec;
574 FETCH c_blanket_nobreak_glb_sec INTO v_pref_blanket_price2;
575
576 IF c_blanket_nobreak_glb_sec%NOTFOUND THEN
577 v_pref_blanket_price2 := NO_PREF_SUPPLIER;
578 END IF;
579
580 CLOSE c_blanket_nobreak_glb_sec;
581 END IF;
582
583 -- Pick the lower blanket price
584
585 IF (v_pref_blanket_price2 <> NO_PREF_SUPPLIER) THEN
586
587 IF (v_pref_blanket_price = NO_PREF_SUPPLIER) THEN
588 v_pref_blanket_price := v_pref_blanket_price2;
589 ELSIF (v_pref_blanket_price > v_pref_blanket_price2) THEN
590 v_pref_blanket_price := v_pref_blanket_price2;
591 END IF;
592
593 END IF;
594
595 -- Comment out the following dbms_output calls to prevent
596 -- buffer overflow. Keep for debugging purposes.
597
598 -- dbms_output.put_line('-- Preferred supplier blanket info --');
599 -- dbms_output.put_line('Date dimension : ' || v_cons_date);
600 -- dbms_output.put_line('Blanket price : ' || v_pref_blanket_price);
601
602 END IF;
603
604 --
605 -- Price Savings = (purch price [consolidated] - avg price [preferred]) *
606 -- qty purchased [consolidated]
607 --
608 IF (v_pref_purch_price <> NO_PREF_SUPPLIER) THEN
609 v_price_savings := v_price_savings +
610 ((v_cons_purch_price - v_pref_purch_price) *
611 v_cons_qty_purchased);
612 ELSIF (v_pref_blanket_price <> NO_PREF_SUPPLIER) THEN
613 v_price_savings := v_price_savings +
614 ((v_cons_purch_price - v_pref_blanket_price) *
615 v_cons_qty_purchased);
616 END IF;
617
618 --
619 -- Quality Savings = (% of defect [consolidated] -
620 -- avg % of defect [preferred]) *
621 -- qty received [consolidated] * cost per defect
622 --
623 IF (v_pref_pct_defect <> NO_PREF_SUPPLIER) AND
624 (v_cons_qty_received <> 0) THEN
625 v_quality_savings := v_quality_savings +
626 (((v_cons_qty_rejected / v_cons_qty_received) -
627 v_pref_pct_defect) * v_cons_qty_received *
628 p_defect_cost);
629 END IF;
630
631 --
632 -- Delivery Savings = (% of delivery exception [consolidated] -
633 -- avg % of delivery exception [preferred]) *
634 -- qty purchased [consolidated] *
635 -- cost per delivery exception
636 --
637 IF (v_pref_pct_del_excp <> NO_PREF_SUPPLIER) AND
638 (v_cons_qty_purchased <> 0) THEN
639 v_delivery_savings := v_delivery_savings +
640 (((v_cons_qty_del_excp / v_cons_qty_purchased) -
641 v_pref_pct_del_excp) * v_cons_qty_purchased *
642 p_del_excp_cost);
643 END IF;
644
645 END LOOP;
646
647 IF (fnd_profile.value('POA_GLOBAL_SECURITY') = 'Y') THEN
648 CLOSE c_cons_supplier;
649 ELSE
650 CLOSE c_cons_supplier_glb_sec;
651 END IF;
652
653 p_price_savings := v_price_savings;
654 p_quality_savings := v_quality_savings;
655 p_delivery_savings := v_delivery_savings;
656
657 p_total_savings := p_price_savings + p_quality_savings +
658 p_delivery_savings;
659
660 -- dbms_output.put_line('-- Savings --');
661 -- dbms_output.put_line('Price : ' || p_price_savings);
662 -- dbms_output.put_line('Quality : ' || p_quality_savings);
663 -- dbms_output.put_line('Delivery : ' || p_delivery_savings);
664 -- dbms_output.put_line('Total : ' || p_total_savings);
665
666 RETURN;
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 -- dbms_output.put_line('calculate_savings - ' || x_progress
671 -- || ': ' || sqlerrm);
672 po_message_s.sql_error('calculate_savings', x_progress, sqlerrm);
673
674 IF c_cons_supplier%ISOPEN THEN
675 CLOSE c_cons_supplier;
676 END IF;
677
678 IF c_cons_supplier_glb_sec%ISOPEN THEN
679 CLOSE c_cons_supplier_glb_sec;
680 END IF;
681
682 IF c_blanket_break%ISOPEN THEN
683 CLOSE c_blanket_break;
684 END IF;
685
686 IF c_blanket_break_glb_sec%ISOPEN THEN
687 CLOSE c_blanket_break_glb_sec;
688 END IF;
689
690 IF c_blanket_nobreak%ISOPEN THEN
691 CLOSE c_blanket_nobreak;
692 END IF;
693
694 IF c_blanket_nobreak_glb_sec%ISOPEN THEN
695 CLOSE c_blanket_nobreak_glb_sec;
696 END IF;
697
698 RAISE;
699 RETURN;
700 END calculate_savings;
701
702
703 END poa_supplier_consolidation_pk;