[Home] [Help]
PACKAGE BODY: APPS.POA_WH_SUPPLIER_CONS_PK
Source
1 PACKAGE BODY poa_wh_supplier_cons_pk AS
2 /* $Header: poaspc1b.pls 115.5 2004/02/26 13:56:50 apalorka ship $ */
3
4 ALL_SUPPLIER CONSTANT INTEGER := -1;
5 NO_PREF_SUPPLIER CONSTANT INTEGER := -1;
6 PRICE_TYPE CONSTANT INTEGER := 1;
7 QUALITY_TYPE CONSTANT INTEGER := 2;
8 DELIVERY_TYPE CONSTANT INTEGER := 3;
9 TOTAL_TYPE CONSTANT INTEGER := 4;
10
11 NULL_VALUE CONSTANT INTEGER := -23453;
12 MAGIC_STRING CONSTANT VARCHAR2(10) := '734jkhJK24';
13 BUFFER_SIZE_LEN CONSTANT INTEGER := 1000000;
14
15
16 -- ========================================================================
17 --
18 -- Calculate the potential savings from consolidating supplier(s) to a
19 -- preferred supplier.
20 -- This procedure is called from Supplier Performance workbook.
21 --
22 -- ========================================================================
23
24 FUNCTION calc_savings(
25 p_item_name IN VARCHAR2,
26 p_pref_supplier_name IN VARCHAR2,
27 p_cons_supplier_name IN VARCHAR2,
28 p_defect_cost IN NUMBER,
29 p_del_excp_cost IN NUMBER,
30 p_start_date IN DATE,
31 p_end_date IN DATE,
32 p_return_type IN NUMBER)
33 RETURN NUMBER
34 IS
35
36 VERSION CONSTANT CHAR(80) :=
37 '$Header: poaspc1b.pls 115.5 2004/02/26 13:56:50 apalorka 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_received NUMBER; -- Qty received
44 v_cons_qty_rejected NUMBER; -- Qty rejected
45 v_cons_qty_del_excp NUMBER; -- Qty of delivery exception
46 v_cons_date_fk NUMBER; -- Date dimension
47
48 -- Preferred Supplier info
49 v_pref_purch_price NUMBER; -- Avg price
50 /*
51 v_pref_blanket_price NUMBER; -- Blanket agreement price
52 v_pref_blanket_price2 NUMBER; -- Blanket agreement price
53 */
54 v_pref_pct_defect NUMBER; -- Avg % of defect
55 v_pref_pct_del_excp NUMBER; -- Avg % of delivery exception
56
57 -- General
58 v_price_savings NUMBER := 0;
59 v_quality_savings NUMBER := 0;
60 v_delivery_savings NUMBER := 0;
61 v_total_savings NUMBER := 0;
62 v_ret_value VARCHAR2(200) := NULL;
63 x_progress VARCHAR2(3) := NULL;
64
65 --
66 -- Select the information for the CONSOLIDATED supplier(s).
67 -- It can either be from a single supplier (specified in the parameter)
68 -- or from all available suppliers within the period window.
69 --
70 CURSOR c_cons_supplier IS
71 SELECT psp.sup_perf_pk_key,
72 NVL(psp.price_g, 0),
73 NVL(psp.qty_ordered_b - psp.qty_cancelled_b, 0),
74 NVL(psp.qty_received_b, 0),
75 NVL(psp.qty_rejected_b, 0),
76 NVL((psp.qty_late_receipt_b + psp.qty_early_receipt_b +
77 psp.qty_past_due_b), 0),
78 psp.date_dim_fk_key
79 FROM edw_time_m cal,
80 edw_items_m item,
81 edw_trd_partner_m tp,
82 poa_edw_sup_perf_f psp
83 WHERE psp.item_fk_key = item.irev_item_revision_pk_key
84 AND item.item_item_name = p_item_name
85 AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
86 AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
87 AND (tp.tprt_name = p_cons_supplier_name
88 OR 'ALL' = UPPER(p_cons_supplier_name))
89 AND cal.cday_cal_day_pk <> 'NA_EDW'
90 AND cal.day_julian_day IS NOT NULL
91 AND cal.day_julian_day <> 0
92 AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;
93
94 /* ==================================================================
95
96 FS: Comment out the code for blankets since blanket price breaks
97 are not available in the WH yet.
98
99 --
100 -- Get the blanket price for the preferred supplier
101 --
102 CURSOR c_blanket_break IS
103 SELECT psc2.price_override *
104 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
105 p_currency_code,
106 NVL(phc2.rate_date, phc2.creation_date),
107 NVL(phc2.rate_type, 'Corporate')),
108 'Y',
109 gl_currency_api.get_rate(phc2.currency_code,
110 p_currency_code,
111 NVL(phc2.rate_date, phc2.creation_date),
112 NVL(phc2.rate_type, 'Corporate')),
113 1) blanket_price
114 FROM po_headers_all phc2,
115 po_headers_all phc1,
116 po_lines_all plc2,
117 po_lines_all plc1,
118 po_line_locations_all psc2,
119 po_line_locations_all psc1
120 WHERE psc1.line_location_id = v_cons_shipment_id
121 AND plc1.po_line_id = psc1.po_line_id
122 AND plc1.po_header_id = psc1.po_header_id
123 AND phc1.po_header_id = psc1.po_header_id
124 AND plc2.item_id = plc1.item_id
125 AND NVL(plc2.item_revision, NULL_VALUE) =
126 NVL(plc1.item_revision, NULL_VALUE)
127 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
128 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
129 AND phc2.po_header_id = plc2.po_header_id
130 AND phc2.vendor_id = p_pref_supplier_id
131 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
132 AND NVL(phc2.end_date, v_cons_date)
133 AND NVL(phc2.currency_code, MAGIC_STRING) =
134 NVL(phc1.currency_code, MAGIC_STRING)
135 AND psc2.po_line_id = plc2.po_line_id
136 AND psc2.po_header_id = plc2.po_header_id
137 AND psc2.shipment_type = 'PRICE BREAK'
138 AND psc2.po_release_id IS NULL
139 AND psc2.quantity <= psc1.quantity
140 AND (psc2.ship_to_location_id = psc1.ship_to_location_id
141 OR psc2.ship_to_location_id IS NULL)
142 ORDER BY psc2.quantity desc, blanket_price asc;
143
144
145 CURSOR c_blanket_nobreak IS
146 SELECT plc2.unit_price *
147 DECODE(gl_currency_api.rate_exists(phc2.currency_code,
148 p_currency_code,
149 NVL(phc2.rate_date, phc2.creation_date),
150 NVL(phc2.rate_type, 'Corporate')),
151 'Y',
152 gl_currency_api.get_rate(phc2.currency_code,
153 p_currency_code,
154 NVL(phc2.rate_date, phc2.creation_date),
155 NVL(phc2.rate_type, 'Corporate')),
156 1) blanket_price
157 FROM po_headers_all phc2,
158 po_headers_all phc1,
159 po_lines_all plc2,
160 po_lines_all plc1,
161 po_line_locations_all psc1
162 WHERE psc1.line_location_id = v_cons_shipment_id
163 AND plc1.po_line_id = psc1.po_line_id
164 AND plc1.po_header_id = psc1.po_header_id
165 AND phc1.po_header_id = psc1.po_header_id
166 AND plc2.item_id = plc1.item_id
167 AND NVL(plc2.item_revision, NULL_VALUE) =
168 NVL(plc1.item_revision, NULL_VALUE)
169 AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
170 NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
171 AND phc2.po_header_id = plc2.po_header_id
172 AND phc2.type_lookup_code = 'BLANKET'
173 AND phc2.vendor_id = p_pref_supplier_id
174 AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
175 AND NVL(phc2.end_date, v_cons_date)
176 AND NVL(phc2.currency_code, MAGIC_STRING) =
177 NVL(phc1.currency_code, MAGIC_STRING)
178 ORDER BY blanket_price;
179
180 ================================================================== */
181
182 BEGIN
183
184 -- ------------------------------------------------------------------------
185 -- Get PREFERRED supplier info.
186 -- The average is calculated for the whole period window.
187 -- These will be compared to the ones from the consolidated supplier to
188 -- calculate the savings.
189 -- ------------------------------------------------------------------------
190
191 BEGIN
192 x_progress := '001';
193
194 -- Select the average price and
195 -- the average percentage of delivery exception
196
197 SELECT SUM(psp.price_g * (psp.qty_ordered_b - psp.qty_cancelled_b)) /
198 SUM(psp.qty_ordered_b - psp.qty_cancelled_b),
199 SUM(psp.qty_early_receipt_b + psp.qty_late_receipt_b +
200 psp.qty_past_due_b) / SUM(psp.qty_ordered_b - psp.qty_cancelled_b)
201 INTO v_pref_purch_price,
202 v_pref_pct_del_excp
203 FROM edw_time_m cal,
204 edw_items_m item,
205 edw_trd_partner_m tp,
206 poa_edw_sup_perf_f psp
207 WHERE psp.item_fk_key = item.irev_item_revision_pk_key
208 AND item.item_item_name = p_item_name
209 AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
210 AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
211 AND tp.tprt_name = p_pref_supplier_name
212 AND NVL(psp.qty_ordered_b - psp.qty_cancelled_b, 0) <> 0
213 AND cal.cday_cal_day_pk <> 'NA_EDW'
214 AND cal.day_julian_day IS NOT NULL
215 AND cal.day_julian_day <> 0
216 AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;
217
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 v_pref_purch_price := NO_PREF_SUPPLIER;
221 v_pref_pct_del_excp := NO_PREF_SUPPLIER;
222
223 WHEN OTHERS THEN
224 RAISE;
225 RETURN(v_ret_value);
226 END;
227
228 BEGIN
229 x_progress := '002';
230
231 -- Select the average percentage of defect
232
233 SELECT SUM(psp.qty_rejected_b)/SUM(psp.qty_received_b)
234 INTO v_pref_pct_defect
235 FROM edw_time_m cal,
236 edw_items_m item,
237 edw_trd_partner_m tp,
238 poa_edw_sup_perf_f psp
239 WHERE psp.item_fk_key = item.irev_item_revision_pk_key
240 AND item.item_item_name = p_item_name
241 AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
242 AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
243 AND tp.tprt_name = p_pref_supplier_name
244 AND NVL(psp.qty_received_b, 0) <> 0
245 AND cal.cday_cal_day_pk <> 'NA_EDW'
246 AND cal.day_julian_day IS NOT NULL
247 AND cal.day_julian_day <> 0
248 AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;
249
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 v_pref_pct_defect := NO_PREF_SUPPLIER;
253
254 WHEN OTHERS THEN
255 RAISE;
256 RETURN(v_ret_value);
257 END;
258
259 --
260 -- No records for the preferred supplier in the period window?
261 --
262 -- Since the previous SQL statements is a SUM, a record w/ NULL values will
263 -- still be returned even if there is no record in the base table that
264 -- satisfies the conditions.
265 -- So, check again here.
266 --
267 IF (v_pref_purch_price IS NULL) THEN
268 v_pref_purch_price := NO_PREF_SUPPLIER;
269 END IF;
270
271 IF (v_pref_pct_defect IS NULL) THEN
272 v_pref_pct_defect := NO_PREF_SUPPLIER;
273 END IF;
274
275 IF (v_pref_pct_del_excp IS NULL) THEN
276 v_pref_pct_del_excp := NO_PREF_SUPPLIER;
277 END IF;
278
279 -- ------------------------------------------------------------------------
280 -- Get consolidated supplier(s) info and calculate savings
281 -- ------------------------------------------------------------------------
282 x_progress := '004';
283
284 OPEN c_cons_supplier;
285
286 LOOP
287 x_progress := '005';
288 FETCH c_cons_supplier INTO v_cons_shipment_id,
289 v_cons_purch_price,
290 v_cons_qty_purchased,
291 v_cons_qty_received,
292 v_cons_qty_rejected,
293 v_cons_qty_del_excp,
294 v_cons_date_fk;
295
296 EXIT WHEN c_cons_supplier%NOTFOUND;
297
298 /* ==================================================================
299
300 FS: Comment out the code for blankets since blanket price breaks
301 are not available in the WH yet.
302
303 --
304 -- If there's no average price for the preferred supplier, get
305 -- blanket price.
306 --
307 -- First look at those blankets w/ price breaks,
308 -- then look at those blankets w/out price breaks
309 -- Because of the ORDER BY clause in the SELECT statements we only need
310 -- to fetch the first record returned.
311 --
312 IF (v_pref_purch_price = NO_PREF_SUPPLIER) THEN
313 v_pref_blanket_price := NO_PREF_SUPPLIER;
314
315 -- Get blankets w/ price breaks
316
317 x_progress := '006';
318
319 OPEN c_blanket_break;
320 FETCH c_blanket_break INTO v_pref_blanket_price;
321
322 IF c_blanket_break%NOTFOUND THEN
323 v_pref_blanket_price := NO_PREF_SUPPLIER;
324 END IF;
325
326 CLOSE c_blanket_break;
327
328 -- Get blankets w/out price breaks
329
330 x_progress := '007';
331
332 OPEN c_blanket_nobreak;
333 FETCH c_blanket_nobreak INTO v_pref_blanket_price2;
334
335 IF c_blanket_nobreak%NOTFOUND THEN
336 v_pref_blanket_price2 := NO_PREF_SUPPLIER;
337 END IF;
338
339 CLOSE c_blanket_nobreak;
340
341 -- Pick the lower blanket price
342
343 IF (v_pref_blanket_price2 <> NO_PREF_SUPPLIER) THEN
344
345 IF (v_pref_blanket_price = NO_PREF_SUPPLIER) THEN
346 v_pref_blanket_price := v_pref_blanket_price2;
347 ELSIF (v_pref_blanket_price > v_pref_blanket_price2) THEN
348 v_pref_blanket_price := v_pref_blanket_price2;
349 END IF;
350
351 END IF;
352
353 -- Comment out the following dbms_output calls to prevent
354 -- buffer overflow. Keep for debugging purposes.
355
356 -- dbms_output.put_line('-- Preferred supplier blanket info --');
357 -- dbms_output.put_line('Date dimension : ' || v_cons_date_fk);
358 -- dbms_output.put_line('Blanket price : ' || v_pref_blanket_price);
359
360 END IF;
361
362 ================================================================== */
363
364 --
365 -- Price Savings = (purch price [consolidated] - avg price [preferred]) *
366 -- qty purchased [consolidated]
367 --
371 v_cons_qty_purchased);
368 IF (v_pref_purch_price <> NO_PREF_SUPPLIER) THEN
369 v_price_savings := v_price_savings +
370 ((v_cons_purch_price - v_pref_purch_price) *
372 /*
373 ELSIF (v_pref_blanket_price <> NO_PREF_SUPPLIER) THEN
374 v_price_savings := v_price_savings +
375 ((v_cons_purch_price - v_pref_blanket_price) *
376 v_cons_qty_purchased);
377 */
378 END IF;
379
380 --
381 -- Quality Savings = (% of defect [consolidated] -
382 -- avg % of defect [preferred]) *
383 -- qty received [consolidated] * cost per defect
384 --
385 IF (v_pref_pct_defect <> NO_PREF_SUPPLIER) AND
386 (v_cons_qty_received <> 0) THEN
387 v_quality_savings := v_quality_savings +
388 (((v_cons_qty_rejected / v_cons_qty_received) -
389 v_pref_pct_defect) * v_cons_qty_received *
390 p_defect_cost);
391 END IF;
392
393 --
394 -- Delivery Savings = (% of delivery exception [consolidated] -
395 -- avg % of delivery exception [preferred]) *
396 -- qty purchased [consolidated] *
397 -- cost per delivery exception
398 --
399 IF (v_pref_pct_del_excp <> NO_PREF_SUPPLIER) AND
400 (v_cons_qty_purchased <> 0) THEN
401 v_delivery_savings := v_delivery_savings +
402 (((v_cons_qty_del_excp / v_cons_qty_purchased) -
403 v_pref_pct_del_excp) * v_cons_qty_purchased *
404 p_del_excp_cost);
405 END IF;
406
407 END LOOP;
408
409 CLOSE c_cons_supplier;
410
411 v_total_savings := v_price_savings + v_quality_savings +
412 v_delivery_savings;
413
414 -- dbms_output.put_line('-- Savings --');
415 -- dbms_output.put_line('Price : ' || v_price_savings);
416 -- dbms_output.put_line('Quality : ' || v_quality_savings);
417 -- dbms_output.put_line('Delivery : ' || v_delivery_savings);
418 -- dbms_output.put_line('Total : ' || v_total_savings);
419
420 IF (p_return_type = PRICE_TYPE) THEN
421 RETURN(v_price_savings);
422 ELSIF (p_return_type = QUALITY_TYPE) THEN
423 RETURN(v_quality_savings);
424 ELSIF (p_return_type = DELIVERY_TYPE) THEN
425 RETURN(v_delivery_savings);
426 ELSE
427 RETURN(v_total_savings);
428 END IF;
429
430
431 EXCEPTION
432 WHEN OTHERS THEN
433
434 IF c_cons_supplier%ISOPEN THEN
435 CLOSE c_cons_supplier;
436 END IF;
437
438 /*
439 IF c_blanket_break%ISOPEN THEN
440 CLOSE c_blanket_break;
441 END IF;
442
443 IF c_blanket_nobreak%ISOPEN THEN
444 CLOSE c_blanket_nobreak;
445 END IF;
446 */
447
448 RAISE;
449 RETURN(v_total_savings);
450
451 END calc_savings;
452
453
454 END poa_wh_supplier_cons_pk;