[Home] [Help]
PACKAGE BODY: APPS.POA_SAVINGS_SAV
Source
1 PACKAGE BODY poa_savings_sav AS
2 /* $Header: poasvp4b.pls 115.20 2004/01/22 12:24:35 sdiwakar ship $ */
3
4 /*
5 NAME
6 get_lowest_possible_price
7 DESCRIPTION
8 procedure for calculating lowest amount we could have
9 purchased an item in a potential contract for had we
10 used a blanket.
11 */
12 --
13 FUNCTION get_lowest_possible_price(p_creation_date IN DATE,
14 p_quantity IN NUMBER,
15 p_unit_meas_lookup_code IN VARCHAR2,
16 p_currency_code IN VARCHAR2,
17 p_item_id IN NUMBER,
18 p_item_revision IN VARCHAR2,
19 p_category_id IN NUMBER,
20 p_ship_to_location_id IN NUMBER,
21 p_need_by_date IN DATE,
22 p_org_id IN NUMBER,
23 p_ship_to_organization_id IN NUMBER,
24 p_ship_to_ou IN NUMBER,
25 p_rate_date IN DATE,
26 p_edw_global_rate_type IN VARCHAR2,
27 p_edw_global_currency_code IN VARCHAR2)
28 RETURN NUMBER
29
30 IS
31
32 v_cum_lowest_price NUMBER := 0;
33 v_ncum_lowest_price NUMBER := 0;
34 v_lowest_price NUMBER := 0;
35
36 v_buf VARCHAR2(240) := NULL;
37 BEGIN
38
39 POA_LOG.debug_line('Get_lowest_possible_price: entered');
40
41 v_ncum_lowest_price := get_lowest_ncum_price(p_creation_date,
42 p_quantity,
43 p_unit_meas_lookup_code,
44 p_currency_code,
45 p_item_id,
46 p_item_revision,
47 p_category_id,
48 p_ship_to_location_id,
49 p_need_by_date,
50 p_org_id,
51 p_ship_to_organization_id,
52 p_ship_to_ou,
53 p_rate_date,
54 p_edw_global_rate_type,
55 p_edw_global_currency_code);
56
57 v_cum_lowest_price := get_lowest_cum_price(p_creation_date,
58 p_quantity,
59 p_unit_meas_lookup_code,
60 p_currency_code,
61 p_item_id,
62 p_item_revision,
63 p_category_id,
64 p_ship_to_location_id,
65 p_org_id,
66 p_ship_to_organization_id,
67 p_ship_to_ou,
68 p_rate_date,
69 p_edw_global_rate_type,
70 p_edw_global_currency_code);
71
72 if v_ncum_lowest_price is null and v_cum_lowest_price is null then
73 SELECT min(plc.unit_price *
74 decode(sign(poa_savings_np.get_currency_conv_rate(phc.currency_code,
75 p_edw_global_currency_code,
76 p_rate_date,
77 p_edw_global_rate_type
78 )
79 ),-1,null,
80 poa_savings_np.get_currency_conv_rate(phc.currency_code,
81 p_edw_global_currency_code,
82 p_rate_date,
83 p_edw_global_rate_type
84 )
85 )
86 )
87 INTO v_lowest_price
88 FROM po_headers_all phc
89 , po_lines_all plc
90 WHERE phc.type_lookup_code = 'BLANKET'
91 and phc.po_header_id = plc.po_header_id
92 and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
93 and p_creation_date between nvl(phc.start_date, p_creation_date)
94 and nvl(phc.end_date, p_creation_date)
95 and plc.item_id = p_item_id
96 and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
97 = nvl(p_item_revision, '-1')
98 and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
99 and (
100 (nvl(phc.global_agreement_flag,'N') = 'N'
101 and phc.org_id = p_ship_to_ou
102 )
103 or
104 (phc.global_agreement_flag = 'Y'
105 and exists
106 (select 'enabled'
107 from po_ga_org_assignments poga
108 where poga.po_header_id = phc.po_header_id
109 and poga.enabled_flag = 'Y'
110 and ((poga.purchasing_org_id in
111 (select tfh.start_org_id
112 from mtl_procuring_txn_flow_hdrs_v tfh,
113 financials_system_params_all fsp1,
114 financials_system_params_all fsp2
115 where p_creation_date between nvl(tfh.start_date,p_creation_date)
116 and nvl(tfh.end_date,p_creation_date)
117 and fsp1.org_id = tfh.start_org_id
118 and fsp1.purch_encumbrance_flag = 'N'
119 and fsp2.org_id = tfh.end_org_id
120 and fsp2.purch_encumbrance_flag = 'N'
121 and (
122 (tfh.qualifier_code is null) or
123 (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
124 )
125 and tfh.end_org_id = p_ship_to_ou
126 and (
127 (tfh.organization_id = p_ship_to_organization_id) or
128 (tfh.organization_id is null)
129 )
130 )
131 )
132 or poga.purchasing_org_id = p_ship_to_ou
133 )
134 )
135 )
136 );
137 -- if v_ncum_lowest_price is null then v_ncum_lowest_price := v_lowest_price; end if;
138 -- if v_cum_lowest_price is null then v_cum_lowest_price := v_lowest_price; end if;
139 end if;
140 /*
141 IF (v_ncum_lowest_price < v_cum_lowest_price) THEN
142 v_lowest_price := v_ncum_lowest_price;
143 POA_LOG.debug_line(' v_ncum_lowest_price: ' || v_ncum_lowest_price);
144
145 ELSE
146 v_lowest_price := v_cum_lowest_price;
147 POA_LOG.debug_line(' v_cum_lowest_price: ' || v_ncum_lowest_price);
148 END IF;
149 POA_LOG.debug_line(' v_lowest_price: ' || v_lowest_price);
150 POA_LOG.debug_line(' p_item_id: ' || p_item_id);
151 */
152 IF v_ncum_lowest_price IS NULL AND v_cum_lowest_price IS NOT NULL THEN
153 RETURN v_cum_lowest_price;
154 ELSIF v_ncum_lowest_price IS NOT NULL AND v_cum_lowest_price IS NULL THEN
155 RETURN v_ncum_lowest_price;
156 ELSIF v_ncum_lowest_price IS NOT NULL AND v_cum_lowest_price IS NOT NULL THEN
157 RETURN Least(v_ncum_lowest_price, v_cum_lowest_price);
158 else
159 RETURN v_lowest_price;
160 END IF;
161 EXCEPTION
162 WHEN others THEN
163 v_buf := 'Lowest possible price function: internal error';
164 ROLLBACK;
165
166 POA_LOG.put_line(v_buf);
167 POA_LOG.put_line(' ');
168
169 RAISE;
170 END get_lowest_possible_price;
171
172
173 /* NAME
174 get_lowest_ncum_price
175 DESCRIPTION
176 procedure for calculating lowest amount we could have
177 purchased an item in a potential contract for had we
178 used a blanket if the item has a non-cumulative price break
179 */
180 --
181 FUNCTION get_lowest_ncum_price(p_creation_date IN DATE,
182 p_quantity IN NUMBER,
183 p_unit_meas_lookup_code IN VARCHAR2,
184 p_currency_code IN VARCHAR2,
185 p_item_id IN NUMBER,
186 p_item_revision IN VARCHAR2,
187 p_category_id IN NUMBER,
188 p_ship_to_location_id IN NUMBER,
189 p_need_by_date IN DATE,
190 p_org_id IN NUMBER,
191 p_ship_to_organization_id IN NUMBER,
192 p_ship_to_ou IN NUMBER,
193 p_rate_date IN DATE,
194 p_edw_global_rate_type IN VARCHAR2,
195 p_edw_global_currency_code IN VARCHAR2)
196 RETURN NUMBER
197
198 IS
199
200 TYPE T_FLEXREF IS REF CURSOR;
201
202 v_lowest_price NUMBER := -1;
203 v_count BINARY_INTEGER := 0;
204
205 v_unit_price NUMBER;
206 v_quantity NUMBER;
207 v_price_override NUMBER;
208 v_price_override_null NUMBER;
209 v_lowest_price_null NUMBER := -1;
210 v_ship_to_location_id NUMBER;
211 v_line_id NUMBER;
212
213 x_progress VARCHAR2(3) := NULL;
214
215 v_cursor T_FLEXREF;
216 v_buf VARCHAR2(240) := NULL;
217 v_conv_rate GL_DAILY_RATES.CONVERSION_RATE%TYPE;
218
219 CURSOR cur_line_id IS
220 SELECT po_line_id,
221 poa_savings_np.get_currency_conv_rate(phc.currency_code,
222 p_edw_global_currency_code,
223 p_rate_date,
224 p_edw_global_rate_type
225 ) conv_rate
226 FROM po_lines_all plc,
227 po_headers_all phc
228 WHERE phc.po_header_id = plc.po_header_id
229 and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
230 and p_creation_date between nvl(phc.start_date, p_creation_date)
231 and nvl(phc.end_date, p_creation_date)
232 and plc.item_id = p_item_id
233 and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
234 = nvl(p_item_revision, '-1')
235 and plc.price_break_lookup_code = 'NON CUMULATIVE'
236 and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
237 and (
238 (nvl(phc.global_agreement_flag,'N') = 'N'
239 and phc.org_id = p_ship_to_ou
240 )
241 or
242 (phc.global_agreement_flag = 'Y'
243 and exists
244 (select 'enabled'
245 from po_ga_org_assignments poga
246 where poga.po_header_id = phc.po_header_id
247 and poga.enabled_flag = 'Y'
248 and ((poga.purchasing_org_id in
249 (select tfh.start_org_id
250 from mtl_procuring_txn_flow_hdrs_v tfh,
251 financials_system_params_all fsp1,
252 financials_system_params_all fsp2
253 where p_creation_date between nvl(tfh.start_date,p_creation_date)
254 and nvl(tfh.end_date,p_creation_date)
255 and fsp1.org_id = tfh.start_org_id
256 and fsp1.purch_encumbrance_flag = 'N'
257 and fsp2.org_id = tfh.end_org_id
258 and fsp2.purch_encumbrance_flag = 'N'
259 and (
260 (tfh.qualifier_code is null) or
261 (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
262 )
263 and tfh.end_org_id = p_ship_to_ou
264 and (
265 (tfh.organization_id = p_ship_to_organization_id) or
266 (tfh.organization_id is null)
267 )
268 )
269 )
270 or poga.purchasing_org_id = p_ship_to_ou
271 )
272 )
273 )
274 );
275
276 BEGIN
277
278 POA_LOG.debug_line('Get_lowest_ncum_price: entered');
279 x_progress := '001';
280
281 -- Get all blanket agreements matching ship_to_location that
282 -- we could have released this item on.
283
284 OPEN cur_line_id;
285
286 FETCH cur_line_id INTO
287 v_line_id, v_conv_rate;
288
289 IF cur_line_id%NOTFOUND THEN
290 close cur_line_id;
291 return NULL;
292 END IF;
293
294 LOOP
295
296 SELECT MIN(price_override) INTO v_price_override FROM (
297 SELECT price_override
298 FROM po_line_locations_all psc
299 WHERE psc.shipment_type = 'PRICE BREAK'
300 and psc.po_line_id = v_line_id
301 and psc.ship_to_location_id = p_ship_to_location_id
302 and psc.po_release_id is null
303 and Nvl(psc.quantity,0) <= p_quantity
304 and nvl(trunc(p_need_by_date), p_creation_date)
305 between nvl(psc.start_date, nvl(p_need_by_date, p_creation_date))
306 and nvl(psc.end_date, nvl(p_need_by_date, p_creation_date))
307 order by Nvl(psc.quantity,0) desc, Trunc(psc.creation_date) desc,
308 psc.price_override ASC) WHERE ROWNUM = 1;
309
310 SELECT MIN(price_override) INTO v_price_override_null FROM (
311 SELECT price_override
312 FROM po_line_locations_all psc
313 WHERE psc.shipment_type = 'PRICE BREAK'
314 and psc.po_line_id = v_line_id
315 and psc.ship_to_location_id IS null
316 and psc.po_release_id is null
317 and Nvl(psc.quantity,0) <= p_quantity
318 and nvl(trunc(p_need_by_date), p_creation_date)
319 between nvl(psc.start_date, nvl(p_need_by_date, p_creation_date))
320 and nvl(psc.end_date, nvl(p_need_by_date, p_creation_date))
321 order by Nvl(psc.quantity,0) desc, Trunc(psc.creation_date) desc,
322 psc.price_override ASC) WHERE ROWNUM = 1;
323
324 if (v_conv_rate < 0) then
325 v_price_override := null;
326 else
327 v_price_override := v_price_override * v_conv_rate;
328 end if;
329 IF(v_price_override < v_lowest_price) THEN
330 v_lowest_price := v_price_override;
331 ELSIF(v_lowest_price < 0) THEN
332 v_lowest_price := v_price_override;
333 END IF;
334
335 if (v_conv_rate < 0) then
336 v_price_override_null := null;
337 else
338 v_price_override_null := v_price_override_null * v_conv_rate;
339 end if;
340 IF(v_price_override_null < v_lowest_price_null) THEN
341 v_lowest_price_null := v_price_override_null;
342 ELSIF(v_lowest_price_null < 0) THEN
343 v_lowest_price_null := v_price_override_null;
344 END IF;
345
346 FETCH cur_line_id INTO v_line_id, v_conv_rate;
347
348 EXIT WHEN cur_line_id%NOTFOUND;
349
350 END LOOP;
351
352 CLOSE cur_line_id;
353
354 RETURN nvl(v_lowest_price, v_lowest_price_null);
355
356 EXCEPTION
357 WHEN others THEN
358 v_buf := 'Get lowest cumulative price: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
359 ROLLBACK;
360 POA_LOG.put_line(v_buf);
361 POA_LOG.put_line(' ');
362
363 RAISE;
364 END get_lowest_ncum_price;
365
366
367 /*
368 NAME
369 get_lowest_cum_price
370 DESCRIPTION
371 procedure for calculating lowest amount we could have
372 purchased an item in a potential contract for had we
373 used a blanket if the item has a cumulative price break
374 */
375 --
376
377 FUNCTION get_lowest_cum_price(p_creation_date IN DATE,
378 p_quantity IN NUMBER,
379 p_unit_meas_lookup_code IN VARCHAR2,
380 p_currency_code IN VARCHAR2,
381 p_item_id IN NUMBER,
382 p_item_revision IN VARCHAR2,
383 p_category_id IN NUMBER,
384 p_ship_to_location_id IN NUMBER,
385 p_org_id IN NUMBER,
386 p_ship_to_organization_id IN NUMBER,
387 p_ship_to_ou IN NUMBER,
388 p_rate_date IN DATE,
389 p_edw_global_rate_type IN VARCHAR2,
390 p_edw_global_currency_code IN VARCHAR2)
391 RETURN NUMBER
392
393 IS
394
395 TYPE T_FLEXREF IS REF CURSOR;
396
397 v_cursor_blk T_FLEXREF;
398 v_cursor_po_line T_FLEXREF;
399
400 v_lowest_price NUMBER := 0;
401 v_min_price NUMBER := 0;
402 v_min_price_global NUMBER := 0;
403 v_count BINARY_INTEGER := 0;
404 v_blanket_id NUMBER;
405 v_total_qty_released NUMBER := 0;
406 v_po_line_id NUMBER;
407 v_cursor_set BOOLEAN := TRUE;
408 v_buf VARCHAR2(240) := NULL;
409
410 x_progress VARCHAR2(3) := NULL;
411 x_iteration_outer NUMBER := 0;
412 x_iteration_inner NUMBER := 0;
413 v_conv_rate NUMBER := 0;
414
415 v_match_location BOOLEAN := true;
416 v_currency_code VARCHAR2(15);
417 BEGIN
418
419 open v_cursor_blk for
420 SELECT distinct psc.po_header_id, phc.currency_code
421 FROM po_headers_all phc
422 , po_lines_all plc
423 , po_line_locations_all psc
424 WHERE psc.shipment_type = 'PRICE BREAK'
425 and phc.po_header_id = plc.po_header_id
426 and plc.po_line_id = psc.po_line_id
427 and psc.po_header_id = phc.po_header_id
428 and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
429 and p_creation_date between nvl(phc.start_date, p_creation_date)
430 and nvl(phc.end_date, p_creation_date)
431 and plc.item_id = p_item_id
432 and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
433 = nvl(p_item_revision, '-1')
434 and psc.ship_to_location_id = p_ship_to_location_id
435 and psc.po_release_id is null
436 and plc.price_break_lookup_code = 'CUMULATIVE'
437 and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
438 and nvl(phc.global_agreement_flag, 'N') = 'N'
439 and phc.org_id = p_ship_to_ou;
440
441 fetch v_cursor_blk into v_blanket_id, v_currency_code;
442 if v_cursor_blk%NOTFOUND then
443 close v_cursor_blk;
444 v_match_location := FALSE;
445
446 OPEN v_cursor_blk FOR
447 SELECT distinct psc.po_header_id, phc.currency_code
448 FROM po_headers_all phc
449 , po_lines_all plc
450 , po_line_locations_all psc
451 WHERE psc.shipment_type = 'PRICE BREAK'
452 and phc.po_header_id = plc.po_header_id
453 and plc.po_line_id = psc.po_line_id
454 and psc.po_header_id = phc.po_header_id
455 and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
456 and p_creation_date between nvl(phc.start_date, p_creation_date)
457 and nvl(phc.end_date, p_creation_date)
458 and nvl(phc.currency_code, nvl(p_currency_code, '-1'))
459 = nvl(p_currency_code, '-1')
460 and plc.item_id = p_item_id
461 and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
462 = nvl(p_item_revision, '-1')
463 and psc.ship_to_location_id is null
464 and psc.po_release_id is null
465 and plc.price_break_lookup_code = 'CUMULATIVE'
466 and nvl(phc.global_agreement_flag, 'N') = 'N'
467 and phc.org_id = p_ship_to_ou;
468
469 FETCH v_cursor_blk INTO v_blanket_id, v_currency_code;
470 if v_cursor_blk%NOTFOUND then
471 close v_cursor_blk;
472 return NULL;
473 end if;
474 end if;
475
476 LOOP
477 -- POA_LOG.debug_line(' fetched v_cursor_blk row no. ' || x_iteration_outer);
478 -- POA_LOG.debug_line(' v_blanket_id: ' || v_blanket_id);
479
480 -- Get the po_line_id from the blanket agreement that
481 -- matches the item.
482
483 x_progress := '070';
484 OPEN v_cursor_po_line for
485 SELECT plc.po_line_id
486 FROM po_lines_all plc
487 WHERE plc.po_header_id = v_blanket_id
488 and plc.item_id = p_item_id
489 and plc.price_break_lookup_code = 'CUMULATIVE'
490 and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
491 and nvl(plc.item_revision, nvl(p_item_revision, '-1')) = nvl(p_item_revision, '-1');
492
493 x_iteration_inner := 0;
494 LOOP
495 x_iteration_inner := x_iteration_inner + 1;
496 FETCH v_cursor_po_line INTO v_po_line_id;
497 EXIT WHEN v_cursor_po_line%NOTFOUND;
498
499 -- POA_LOG.debug_line(' v_po_line_id: ' || v_po_line_id);
500
501 -- Get the total quantity already released
502 x_progress := '080';
503 SELECT sum(nvl(pod.quantity_ordered,0)) INTO v_total_qty_released
504 FROM po_releases_all por
505 , po_distributions_all pod
506 WHERE por.po_header_id = v_blanket_id
507 and pod.po_release_id = por.po_release_id
508 and pod.po_line_id = v_po_line_id
509 and pod.creation_date < p_creation_date
510 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
511
512 -- POA_LOG.debug_line(' v_total_qty_released: ' || v_total_qty_released);
513
514 x_progress := '090';
515
516 BEGIN
517 IF (v_match_location) then
518 SELECT min(psc.price_override) INTO v_lowest_price
519 FROM po_line_locations_all psc
520 WHERE Nvl(psc.quantity,0) <= Nvl(v_total_qty_released,0) + p_quantity
521 and psc.po_release_id is null
522 AND psc.ship_to_location_id = p_ship_to_location_id
523 and psc.po_line_id = v_po_line_id;
524 else
525 SELECT min(psc.price_override) INTO v_lowest_price
526 FROM po_line_locations_all psc
527 WHERE Nvl(psc.quantity,0) <= Nvl(v_total_qty_released,0) + p_quantity
528 and psc.po_release_id is null
529 AND psc.ship_to_location_id IS NULL
530 and psc.po_line_id = v_po_line_id;
531 END IF;
532 EXCEPTION
533 WHEN OTHERS THEN -- should never happen
534 x_progress := '110';
535 v_lowest_price := NULL;
536 /*
537 SELECT min(plc.unit_price) INTO v_lowest_price
538 FROM po_lines_all plc
539 WHERE plc.po_line_id = v_po_line_id;
540 */
541 END;
542
543 IF ((v_min_price = 0) or (v_lowest_price < v_min_price)) THEN
544 v_min_price := v_lowest_price;
545 END IF;
546 END LOOP;
547 CLOSE v_cursor_po_line;
548
549 -- POA_LOG.debug_line(' v_cursor_po_line closed');
550 select poa_savings_np.get_currency_conv_rate(v_currency_code,
551 p_edw_global_currency_code,
552 p_rate_date,
553 p_edw_global_rate_type
554 )
555 into v_conv_rate
556 from dual;
557 if (v_conv_rate < 0) then
558 v_min_price := null;
559 else
560 v_min_price := v_min_price * v_conv_rate;
561 end if;
562 if (v_min_price is not null) then
563 if (v_min_price_global > v_min_price or v_min_price_global = 0) then
564 v_min_price_global := v_min_price;
565 end if;
566 end if;
567 v_min_price := 0;
568 x_iteration_outer := x_iteration_outer + 1;
569 FETCH v_cursor_blk INTO v_blanket_id, v_currency_code;
570 EXIT WHEN v_cursor_blk%NOTFOUND;
571 END LOOP;
572 CLOSE v_cursor_blk;
573
574 -- POA_LOG.debug_line(' v_cursor_blk closed');
575
576 RETURN v_min_price_global;
577 POA_LOG.debug_line('Get_lowest_cum_price exit');
578 EXCEPTION
579 WHEN others THEN
580 v_buf := 'Get lowest cumulative price: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
581
582 ROLLBACK;
583
584 POA_LOG.put_line(v_buf);
585 POA_LOG.put_line(' ');
586
587 RAISE;
588 END get_lowest_cum_price;
589
590 END poa_savings_sav;