1 PACKAGE BODY pon_transform_bidding_pkg AS
2 -- $Header: PONTFBDB.pls 120.11.12020000.2 2013/02/09 10:49:09 hvutukur ship $
3 --
4
5 --
6 -- BID TOTAL ERROR CODES
7 --
8 BID_TOTAL_WARNING NUMBER := -1;
9 --
10 --
11
12 -- LOGGING FEATURE
13 --
14 -- global variables used for logging
15 --
16 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
17 g_module_prefix CONSTANT VARCHAR2(35) := 'pon.plsql.transformBiddingPkg.';
18 --
19 --private helper procedure for logging
20 PROCEDURE print_log(p_module IN VARCHAR2,
21 p_message IN VARCHAR2)
22 IS
23 BEGIN
24 IF (g_fnd_debug = 'Y') THEN
25 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
26 FND_LOG.string(log_level => FND_LOG.level_statement,
27 module => g_module_prefix || p_module,
28 message => p_message);
29 END IF;
30 END IF;
31 END;
32
33
34 -- ------------------------------------------------------------------------
35 -- find_user_site
36 --
37 -- given auction_header_id, tpid, tpcid, this will find the site
38 -- to use for transformation.
39 --
40 -- question: what if this supplier was not invited to the negotiation?
41 -- he will not have any price factor values to use...
42 -- ------------------------------------------------------------------------
43
44 FUNCTION find_user_site
45 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
46 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
47 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE)
48 RETURN NUMBER
49 IS
50 v_site_id pon_bid_headers.vendor_site_id%TYPE;
51 v_num_invited_sites NUMBER;
52 BEGIN
53
54 SELECT vendor_site_id
55 INTO v_site_id
56 FROM (SELECT vendor_site_id
57 FROM pon_bidding_parties
58 WHERE auction_header_id = p_auction_header_id
59 AND trading_partner_id = p_tpid
60 ORDER BY vendor_site_code)
61 WHERE rownum=1;
62
63 RETURN v_site_id;
64
65 EXCEPTION
66
67 WHEN NO_DATA_FOUND THEN
68 RETURN NULL;
69
70 END find_user_site;
71
72
73 -- -----------------------------------------------------
74 -- check_est_qty_on_all_bid_lines
75 --
76 -- Indirectly used to check whether bid total can be computed for blanket negotiations
77 --
78 -- Estimated quantity needs to be set for all lines that were bidded on to
79 -- compute the bid total
80 -- ----------------------------------------------------------------
81
82 FUNCTION check_est_qty_on_all_bid_lines
83 (p_auction_header_id IN NUMBER,
84 p_bid_number IN NUMBER) RETURN VARCHAR2
85
86 IS
87
88 v_has_est_qty_on_all_bid_lines VARCHAR2(1);
89
90 BEGIN
91
92 v_has_est_qty_on_all_bid_lines := 'Y';
93
94 /*SELECT 'N'
95 INTO v_has_est_qty_on_all_bid_lines
96 FROM dual
97 WHERE EXISTS (SELECT null
98 FROM pon_bid_item_prices pbip,
99 pon_auction_item_prices_all paip
100 WHERE pbip.auction_header_id = p_auction_header_id AND
101 pbip.bid_number = p_bid_number AND
102 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
103 pbip.auction_header_id = paip.auction_header_id AND
104 pbip.line_number = paip.line_number AND
105 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE') AND
106 paip.order_type_lookup_code <> 'FIXED PRICE' AND
107 paip.quantity is null);*/
108
109 /* Unsolicited Lines Project : Commented code above.
110 * Along with negotiation lines, Check if all unsolicited lines has quantity or not.
111 */
112 SELECT 'N'
113 INTO v_has_est_qty_on_all_bid_lines
114 FROM dual
115 WHERE EXISTS ((SELECT null
116 FROM pon_bid_item_prices pbip,
117 pon_auction_item_prices_all paip
118 WHERE pbip.auction_header_id = p_auction_header_id AND
119 pbip.bid_number = p_bid_number AND
120 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
121 pbip.auction_header_id = paip.auction_header_id AND
122 pbip.line_number = paip.line_number AND
123 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE') AND
124 paip.order_type_lookup_code <> 'FIXED PRICE' AND
125 paip.quantity is null)
126 UNION
127 (SELECT null
128 FROM pon_bid_item_prices pbip1
129 WHERE pbip1.auction_header_id = p_auction_header_id AND
130 pbip1.bid_number = p_bid_number AND
131 pbip1.auction_line_number = -1 AND
132 nvl(pbip1.has_bid_flag, 'N') = 'Y' AND
133 pbip1.quantity IS null));
134
135 RETURN v_has_est_qty_on_all_bid_lines;
136
137 EXCEPTION
138
139 WHEN NO_DATA_FOUND THEN
140 RETURN 'Y';
141
142
143 END check_est_qty_on_all_bid_lines;
144
145
146 /* Unsolicited Lines : Calculate Unsolicited Lines total */
147 FUNCTION calculate_unsol_line_total(p_auction_header_id IN NUMBER,
148 p_bid_number IN NUMBER) RETURN NUMBER
149 IS
150 v_unsol_line_total NUMBER;
151 BEGIN
152 --Fix for bug 13565622 - To handle unsol lines with out quantity in case of BPA
153 SELECT sum(pbip.quantity * pbip.price)
154 + decode(Min(nvl(pbip.quantity, -9999)), -9999, NULL, 0)
155 into v_unsol_line_total
156 FROM PON_BID_ITEM_PRICES pbip
157 WHERE
158 pbip.bid_number = p_bid_number and
159 pbip.auction_line_number = -1 and
160 nvl(pbip.clm_info_flag,'N') <> 'Y';
161
162 RETURN v_unsol_line_total;
163
164 END calculate_unsol_line_total;
165
166 -- --------------------------------------------------------------
167 --
168 -- calculate_supplier_bid_total
169 --
170 -- Calculates the bid total in the buyer's currency for a supplier's view
171 --
172 -- The buyer_bid_total can be used when a bid has been submitted for a
173 -- TRANSFORMED auction
174 --
175 -- -------------------------------------------------------------
176
177 FUNCTION calculate_supplier_bid_total
178 (p_auction_header_id IN NUMBER,
179 p_bid_number IN NUMBER,
180 p_outcome IN pon_auction_headers_all.contract_type%TYPE,
181 p_supplier_view_type IN pon_auction_headers_all.supplier_view_type%TYPE,
182 p_tpid IN NUMBER,
183 p_site IN NUMBER) RETURN NUMBER
184
185 IS
186
187 v_bid_total NUMBER;
188 v_unsol_line_total NUMBER; --Unsolicited Lines Project
189 v_unsol_line_count NUMBER;
190 v_total NUMBER;
191
192 BEGIN
193
194 IF (p_supplier_view_type = 'TRANSFORMED') THEN
195
196 SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
197 decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
198 nvl(pbip.price,0)) bid_total
199 INTO v_bid_total
200 FROM pon_bid_item_prices pbip,
201 pon_auction_item_prices_all paip
202 WHERE pbip.auction_header_id = p_auction_header_id AND
203 pbip.bid_number = p_bid_number AND
204 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
205 pbip.auction_header_id = paip.auction_header_id AND
206 pbip.line_number = paip.line_number AND
207 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
208
209 ELSE -- UNTRANSFORMED
210
211 SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
212 decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
213 nvl(untransform_one_price(paip.auction_header_id, paip.line_number, pbip.price,
214 paip.quantity, p_tpid, p_site),0)) bid_total
215 INTO v_bid_total
216 FROM pon_bid_item_prices pbip,
217 pon_auction_item_prices_all paip
218 WHERE pbip.auction_header_id = p_auction_header_id AND
219 pbip.bid_number = p_bid_number AND
220 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
221 pbip.auction_header_id = paip.auction_header_id AND
222 pbip.line_number = paip.line_number AND
223 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
224
225 END IF;
226
227 -- Unsolicited Lines Project : Calculate unsolicited lines total.
228
229 --IF v_bid_total is not null then
230 BEGIN
231 SELECT count(*) INTO v_unsol_line_count
232 FROM pon_bid_item_prices
233 WHERE bid_number = p_bid_number
234 AND auction_line_number = -1
235 AND Nvl(clm_info_flag, 'N') = 'N'
236 AND Nvl(clm_cost_constraint, 'X') NOT IN ('NSP', 'NC');
237
238 EXCEPTION
239 WHEN No_Data_Found THEN
240 v_unsol_line_count := 0;
241 WHEN OTHERS THEN
242 v_unsol_line_count := 0;
243 END;
244
245 IF v_unsol_line_count > 0 THEN
246 v_unsol_line_total := calculate_unsol_line_total(p_auction_header_id, p_bid_number);
247 ELSE
248 v_unsol_line_total := 0;
249 END IF;
250
251 --If only unsol lines with out quantity are added in offer then donot display offer totals
252 --else if unsol total is null and there is non zero bid total still donot display offer totals
253 --else if all both unsol and sol line totals are not null display the sum
254 IF v_bid_total IS NULL THEN
255 IF(v_unsol_line_count > 0 AND v_unsol_line_total IS NULL) THEN
256 RETURN BID_TOTAL_WARNING;
257 ELSE
258 RETURN v_unsol_line_total;
259 END IF;
260 ELSIF v_unsol_line_total IS NULL THEN
261 RETURN BID_TOTAL_WARNING;
262 ELSE
263 v_total:=v_bid_total+v_unsol_line_total;
264 RETURN v_total;
265 END IF;
266
267 --end if;
268
269 --RETURN (v_bid_total + v_unsol_line_total);
270
271 END calculate_supplier_bid_total;
272
273 --------------------------------------------
274 --
275 -- returns the bid total in the buyer's currency
276 --
277 -------------------------------------------
278
279
280 FUNCTION calculate_bid_total
281 (p_auction_header_id IN NUMBER,
282 p_bid_number IN NUMBER,
283 p_tpid IN NUMBER,
284 p_site IN NUMBER) RETURN NUMBER
285
286 IS
287
288 v_buyer_tpid NUMBER;
289 v_outcome pon_auction_headers_all.contract_type%TYPE;
290 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
291 v_bid_status pon_bid_headers.bid_status%TYPE;
292 v_buyer_bid_total NUMBER;
293 v_has_est_qty_on_all_bid_lines VARCHAR2(1);
294 v_doctype_group_name VARCHAR2(80);
295
296 BEGIN
297
298 IF (p_bid_number is NULL) THEN
299 RETURN NULL;
300 END IF;
301
302 SELECT auh.trading_partner_id, auh.contract_type, auh.supplier_view_type,dt.doctype_group_name
303 INTO v_buyer_tpid, v_outcome, v_supplier_view_type,v_doctype_group_name
304 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
305 WHERE auction_header_id = p_auction_header_id
306 AND auh.doctype_id = dt.doctype_id;
307
308
309 SELECT bid_status, buyer_bid_total
310 INTO v_bid_status, v_buyer_bid_total
311 FROM pon_bid_headers
312 WHERE auction_header_id = p_auction_header_id AND
313 bid_number = p_bid_number;
314
315 -- Check to see if we can use buyer_bid_total
316 -- 1) The buyer_bid_total column is set at publish time...
317 -- and thus is only useful after publish time
318 -- 2) buyer_bid_total is the bid's total in the buyer's view
319 -- 3) A buyer and supplier will see the same total whe the supplier view is TRANSFORMED
320 -- 4) A buyer can only see the total for non-draft bids
321
322 IF ( (p_tpid = v_buyer_tpid) OR
323 (v_bid_status <> 'DRAFT' AND v_supplier_view_type = 'TRANSFORMED') ) THEN
324
325 IF (v_buyer_bid_total is NULL) THEN
326 RETURN BID_TOTAL_WARNING;
327 ELSE
328 RETURN v_buyer_bid_total;
329 END IF;
330
331 END IF;
332
333
334 IF (v_bid_status = 'DRAFT') THEN
335
336 IF ((v_doctype_group_name = 'REQUEST_FOR_INFORMATION') OR (v_outcome = 'BLANKET' OR v_outcome = 'CONTRACT')) THEN
337
338 v_has_est_qty_on_all_bid_lines := check_est_qty_on_all_bid_lines(p_auction_header_id, p_bid_number);
339
340 IF (v_has_est_qty_on_all_bid_lines = 'N') THEN
341 RETURN BID_TOTAL_WARNING;
342 END IF;
343
344 END IF;
345
346
347 RETURN calculate_supplier_bid_total(p_auction_header_id,
348 p_bid_number,
349 v_outcome,
350 v_supplier_view_type,
351 p_tpid,
352 p_site);
353
354 ELSE -- Submitted bid in an UNTRANSFORMED auction
355
356 IF (v_buyer_bid_total is NULL) THEN
357 RETURN BID_TOTAL_WARNING;
358 ELSE
359 RETURN calculate_supplier_bid_total(p_auction_header_id,
360 p_bid_number,
361 v_outcome,
362 v_supplier_view_type,
363 p_tpid,
364 p_site);
365 END IF;
366
367 END IF;
368
369 END calculate_bid_total;
370
371 -- ------------------------------------------------------------------------
372 -- calculate_price
373 --
374 -- called from the VO sql query itself, this will takes one price
375 -- and transforms it if necessary.
376 -- ------------------------------------------------------------------------
377 FUNCTION calculate_price
378 (p_auction_header_id IN NUMBER,
379 p_line_number IN NUMBER,
380 p_price IN NUMBER,
381 p_quantity IN NUMBER,
382 p_tpid IN NUMBER,
383 p_site IN NUMBER)
384 RETURN NUMBER
385 IS
386 BEGIN
387 return calculate_price(p_auction_header_id, p_line_number, p_price,
388 p_quantity, p_tpid, -1, p_site, null);
389 END calculate_price;
390
391
392 FUNCTION calculate_price
393 (p_auction_header_id IN NUMBER,
394 p_line_number IN NUMBER,
395 p_price IN NUMBER,
396 p_quantity IN NUMBER,
397 p_tpid IN NUMBER,
398 p_tpcid IN NUMBER,
399 p_site IN NUMBER)
400 RETURN NUMBER
401 IS
402 BEGIN
403 return calculate_price(p_auction_header_id, p_line_number, p_price,
404 p_quantity, p_tpid, p_tpcid, p_site, null);
405 END calculate_price;
406
407
408 FUNCTION calculate_price
409 (p_auction_header_id IN NUMBER,
410 p_line_number IN NUMBER,
411 p_price IN NUMBER,
412 p_quantity IN NUMBER,
413 p_tpid IN NUMBER,
414 p_tpcid IN NUMBER,
415 p_site IN NUMBER,
416 p_requested_supplier_id IN NUMBER)
417 RETURN NUMBER
418 IS
419 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
420 v_buyer_tpid pon_auction_headers_all.trading_partner_id %TYPE;
421
422 BEGIN
423
424 -- QUESTION: do I have to worry about the case where the following query
425 -- returns NO rows??
426
427 SELECT supplier_view_type, trading_partner_id
428 INTO v_supplier_view_type, v_buyer_tpid
429 FROM pon_auction_headers_all
430 WHERE auction_header_id = p_auction_header_id;
431
432 IF v_supplier_view_type = 'TRANSFORMED' OR
433 v_buyer_tpid = p_tpid OR
434 p_site IS NULL THEN
435 RETURN p_price;
436
437 ELSE
438 RETURN untransform_one_price(p_auction_header_id, p_line_number,
439 p_price, p_quantity, p_tpid, p_site, p_requested_supplier_id);
440 END IF;
441
442 END calculate_price;
443
444
445 -- ------------------------------------------------------------------------
446 -- untransform_one_price
447 --
448 -- this utility function just transforms one price
449 -- ------------------------------------------------------------------------
450
451 FUNCTION untransform_one_price
452 (p_auction_header_id IN NUMBER,
453 p_line_number IN NUMBER,
454 p_price IN NUMBER,
455 p_quantity IN NUMBER,
456 p_tpid IN NUMBER,
457 p_site_id IN NUMBER)
458 RETURN NUMBER
459 IS
460 BEGIN
461 return untransform_one_price(p_auction_header_id, p_line_number, p_price,
462 p_quantity, p_tpid, p_site_id, null);
463 END untransform_one_price;
464
465
466 FUNCTION untransform_one_price
467 (p_auction_header_id IN NUMBER,
468 p_line_number IN NUMBER,
469 p_price IN NUMBER,
470 p_quantity IN NUMBER,
471 p_tpid IN NUMBER,
472 p_site_id IN NUMBER,
473 p_requested_supplier_id IN NUMBER)
474 RETURN NUMBER
475 IS
476 v_percentage pon_pf_supplier_formula.percentage%TYPE;
477 v_unit pon_pf_supplier_formula.unit_price%TYPE;
478 v_amount pon_pf_supplier_formula.fixed_amount%TYPE;
479 v_quantity NUMBER;
480
481 BEGIN
482
483 SELECT unit_price, fixed_amount, percentage
484 INTO v_unit, v_amount, v_percentage
485 FROM pon_pf_supplier_formula
486 WHERE auction_header_id = p_auction_header_id
487 AND line_number = p_line_number
488 AND ((trading_partner_id = p_tpid AND
489 vendor_site_id = p_site_id)
490 OR requested_supplier_id = p_requested_supplier_id);
491
492 v_quantity := p_quantity;
493 IF v_quantity IS NULL OR
494 v_quantity = 0 THEN
495 v_quantity := 1;
496 END IF;
497
498 RETURN ((p_price - v_unit - v_amount/v_quantity) / v_percentage);
499
500 EXCEPTION
501
502 WHEN NO_DATA_FOUND THEN
503 RETURN p_price;
504
505 END untransform_one_price;
506 --
507 --
508 -- ------------------------------------------------------------------------
509 -- calculate_quote_amount
510 -- In case of Regular line, lot, group line, lot line:
511 -- calculated as sum of line totals, which are quantity * bid price for each line
512 -- In case of GROUP:
513 -- calculated as sum of all group line totals,
514 -- which are group line quantity * group line bid price for each group lineline
515 -- ------------------------------------------------------------------------
516
517 FUNCTION calculate_quote_amount
518 (p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
519 p_line_number IN pon_auction_item_prices_all.line_number%TYPE,
520 p_bid_number IN pon_bid_headers.bid_number%TYPE,
521 p_supplier_view_type IN pon_auction_headers_all.supplier_view_type%TYPE,
522 p_buyer_tpid IN pon_auction_headers_all.trading_partner_id%TYPE,
523 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
524 p_site IN pon_bid_headers.vendor_site_id%TYPE)
525 RETURN NUMBER
526 IS
527 v_quote_amount NUMBER;
528 v_group_type pon_auction_item_prices_all.group_type%TYPE;
529 BEGIN
530 SELECT group_type
531 INTO v_group_type
532 FROM pon_auction_item_prices_all
533 WHERE auction_header_id = p_auction_header_id
534 AND line_number = p_line_number;
535
536 -- Transformed View: no need to reverse-transform prices
537 IF (p_supplier_view_type = 'TRANSFORMED' OR
538 p_buyer_tpid = p_tpid OR
539 p_site IS NULL) THEN
540
541
542 SELECT DECODE (al.group_type, 'GROUP', bl.group_amount,
543 nvl(bl.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity)) * bl.price)
544 INTO v_quote_amount
545 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
546 WHERE bl.bid_number = p_bid_number
547 AND bl.line_number = p_line_number
548 AND bl.auction_header_id = al.auction_header_id
549 AND bl.line_number = al.line_number;
550
551 -- Untransformed View: we must reverse-transform prices
552 ELSE
553
554 -- Untransformed View and GROUP Line Type
555 IF (v_group_type = 'GROUP') THEN
556 -- Supplier does not see group amount in untransformed view
557 v_quote_amount := NULL;
558 -- Untransformed View and LOT or LINE Line Type
559 ELSE
560 SELECT
561 nvl(bl.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))
562 * nvl(PON_TRANSFORM_BIDDING_PKG.untransform_one_price(p_auction_header_id,
563 bl.line_number, bl.price, al.quantity, p_tpid, p_site), 0)
564 INTO v_quote_amount
565 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
566 WHERE bl.bid_number = p_bid_number
567 AND bl.line_number = p_line_number
568 AND al.auction_header_id = bl.auction_header_id
569 AND al.line_number = bl.line_number;
570
571 END IF;
572 END IF;
573
574 RETURN v_quote_amount;
575
576 END calculate_quote_amount;
577 --
578 --
579 -- -----------------------------------------------------------------------
580 -- has_pf_values_defined
581 --
582 -- given a buyer price factor, determines if values have been entered for this trading partner/site
583 --
584 -- ----------------------------------------------------------------------
585
586 FUNCTION has_pf_values_defined
587 (p_auction_header_id IN NUMBER,
588 p_line_number IN NUMBER,
589 p_pf_seq_number IN NUMBER,
590 p_trading_partner_id IN NUMBER,
591 p_vendor_site_id IN NUMBER) RETURN VARCHAR2
592 IS
593 BEGIN
594 return has_pf_values_defined(p_auction_header_id, p_line_number,
595 p_pf_seq_number, p_trading_partner_id, p_vendor_site_id, null);
596 END has_pf_values_defined;
597
598
599 FUNCTION has_pf_values_defined
600 (p_auction_header_id IN NUMBER,
601 p_line_number IN NUMBER,
602 p_pf_seq_number IN NUMBER,
603 p_trading_partner_id IN NUMBER,
604 p_vendor_site_id IN NUMBER,
605 p_requested_supplier_id IN NUMBER) RETURN VARCHAR2
606 IS
607 v_supplier_seq_number NUMBER;
608 v_has_pf_values_defined VARCHAR2(1);
609
610 BEGIN
611
612 select sequence
613 into v_supplier_seq_number
614 from pon_bidding_parties
615 where auction_header_id = p_auction_header_id and
616 ((trading_partner_id = p_trading_partner_id and
617 vendor_site_id = p_vendor_site_id) OR
618 requested_supplier_id = p_requested_supplier_id);
619
620 select 'Y'
621 into v_has_pf_values_defined
622 from pon_pf_supplier_values
623 where auction_header_id = p_auction_header_id and
624 line_number = p_line_number and
625 pf_seq_number = p_pf_seq_number and
626 supplier_seq_number = v_supplier_seq_number;
627
628
629 RETURN v_has_pf_values_defined;
630
631 EXCEPTION
632
633 WHEN NO_DATA_FOUND THEN
634 RETURN 'N';
635
636 END has_pf_values_defined;
637 --
638 --
639 PROCEDURE GET_DISPLAY_CURRENCY_INFO (p_auction_header_id IN NUMBER,
640 p_trading_partner_id IN NUMBER,
641 p_vendor_site_id IN NUMBER,
642 p_trading_partner_contact_id IN NUMBER,
643 p_is_buyer IN VARCHAR2,
644 x_currency OUT NOCOPY VARCHAR2,
645 x_rate OUT NOCOPY NUMBER,
646 x_precision OUT NOCOPY NUMBER,
647 x_currency_precision OUT NOCOPY NUMBER,
648 x_site_id OUT NOCOPY NUMBER,
649 x_bid_number OUT NOCOPY NUMBER,
650 x_bid_status OUT NOCOPY VARCHAR2) IS
651
652 BEGIN
653
654
655 --
656 -- Default currency information from negotiation
657 --
658
659
660 select ah.currency_code, 1 rate, ah.number_price_decimals, fc.precision, -1 site_id, -1 bid_number, null
661 into x_currency, x_rate, x_precision, x_currency_precision, x_site_id, x_bid_number, x_bid_status
662 from pon_auction_headers_all ah,
663 fnd_currencies fc
664 where ah.auction_header_id = p_auction_header_id and
665 ah.currency_code = fc.currency_code;
666
667
668 --
669 -- For suppliers, check if we can get currency information from a bid (query goes across all amendments)
670 --
671
672
673 IF (p_is_buyer = 'N') THEN
674
675
676 BEGIN
677
678 select vendor_site_id, bid_currency_code, rate, number_price_decimals, precision
679 into x_site_id, x_currency, x_rate, x_precision, x_currency_precision
680 from (select bh.vendor_site_id, bh.bid_currency_code, bh.rate, bh.number_price_decimals, fc.precision
681 from pon_bid_headers bh,
682 pon_auction_headers_all ah,
683 fnd_currencies fc
684 where ah.auction_header_id_orig_amend = (select auction_header_id_orig_amend
685 from pon_auction_headers_all
686 where auction_header_id = p_auction_header_id) and
687 bh.auction_header_id = ah.auction_header_id and
688 bh.trading_partner_id = p_trading_partner_id and
689 bh.vendor_site_id like nvl(to_char(p_vendor_site_id), '%') and
690 bh.trading_partner_contact_id = p_trading_partner_contact_id and
691 bh.bid_currency_code = fc.currency_code
692 order by ah.amendment_number desc,
693 decode(bh.bid_status, 'DRAFT', 9, 'ACTIVE', 8, 'RESUBMISSION' , 7, 'DISQUALIFIED', 6, 'ARCHIVED', 4, 'ARCHIVED_DRAFT', 3) desc,
694 bh.publish_date desc)
695 where rownum = 1;
696
697 EXCEPTION
698
699 --
700 -- If there is no bid for a supplier, the negotiation's currency will be used
701 -- Also, we will select the default site
702 --
703 WHEN NO_DATA_FOUND THEN
704
705 if (p_vendor_site_id is null) then -- look in invitees table, if there is a site
706 x_site_id := PON_TRANSFORM_BIDDING_PKG.FIND_USER_SITE(p_auction_header_id, p_trading_partner_id, p_trading_partner_contact_id);
707
708 else
709 x_site_id := p_vendor_site_id;
710
711 end if;
712
713 if (x_site_id is null) then -- if still null (can happen when supplier was not invited)
714 x_site_id := -1;
715 end if;
716
717 END;
718
719
720 BEGIN
721
722 select bid_number, bid_status
723 into x_bid_number, x_bid_status
724 from (select bh.bid_number, bh.bid_status
725 from pon_bid_headers bh,
726 pon_auction_headers_all ah
727 where ah.auction_header_id_orig_amend = (select auction_header_id_orig_amend
728 from pon_auction_headers_all
729 where auction_header_id = p_auction_header_id) and
730 bh.auction_header_id = ah.auction_header_id and
731 bh.trading_partner_id = p_trading_partner_id and
732 bh.vendor_site_id = x_site_id
733 order by ah.amendment_number desc,
734 decode(bh.bid_status, 'DRAFT', 9, 'ACTIVE', 8, 'RESUBMISSION' , 7, 'DISQUALIFIED', 6, 'ARCHIVED', 4, 'ARCHIVED_DRAFT', 3) desc,
735 bh.publish_date desc)
736 where rownum = 1;
737
738 EXCEPTION
739
740 WHEN NO_DATA_FOUND THEN
741 x_bid_number := -1;
742 x_bid_status := null;
743 END;
744
745
746
747 END IF;
748
749 END GET_DISPLAY_CURRENCY_INFO;
750
751 FUNCTION calc_btotal_with_out_ol
752 (p_auction_header_id IN NUMBER,
753 p_bid_number IN NUMBER,
754 p_tpid IN NUMBER,
755 p_site IN NUMBER) RETURN NUMBER
756
757 IS
758
759 v_buyer_tpid NUMBER;
760 v_outcome pon_auction_headers_all.contract_type%TYPE;
761 v_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
762 v_bid_status pon_bid_headers.bid_status%TYPE;
763 v_buyer_bid_total NUMBER;
764 v_has_est_qty_on_all_bid_lines VARCHAR2(1);
765 v_doctype_group_name VARCHAR2(80);
766
767 BEGIN
768
769 IF (p_bid_number is NULL) THEN
770 RETURN NULL;
771 END IF;
772
773 SELECT auh.trading_partner_id, auh.contract_type, auh.supplier_view_type,dt.doctype_group_name
774 INTO v_buyer_tpid, v_outcome, v_supplier_view_type,v_doctype_group_name
775 FROM pon_auction_headers_all auh, pon_auc_doctypes dt
776 WHERE auction_header_id = p_auction_header_id
777 AND auh.doctype_id = dt.doctype_id;
778
779
780 SELECT bid_status, buyer_bid_total
781 INTO v_bid_status, v_buyer_bid_total
782 FROM pon_bid_headers
783 WHERE auction_header_id = p_auction_header_id AND
784 bid_number = p_bid_number;
785
786 -- Check to see if we can use buyer_bid_total
787 -- 1) The buyer_bid_total column is set at publish time...
788 -- and thus is only useful after publish time
789 -- 2) buyer_bid_total is the bid's total in the buyer's view
790 -- 3) A buyer and supplier will see the same total whe the supplier view is TRANSFORMED
791 -- 4) A buyer can only see the total for non-draft bids
792
793 IF ( (p_tpid = v_buyer_tpid) OR
794 (v_bid_status <> 'DRAFT' AND v_supplier_view_type = 'TRANSFORMED') ) THEN
795
796 IF (v_buyer_bid_total is NULL) THEN
797 RETURN BID_TOTAL_WARNING;
798 ELSE
799 RETURN calc_sup_btotal_with_out_ol(p_auction_header_id,
800 p_bid_number,
801 v_outcome,
802 v_supplier_view_type,
803 p_tpid,
804 p_site);
805 END IF;
806
807 END IF;
808
809
810 IF (v_bid_status = 'DRAFT') THEN
811
812 IF ((v_doctype_group_name = 'REQUEST_FOR_INFORMATION') OR (v_outcome = 'BLANKET' OR v_outcome = 'CONTRACT')) THEN
813
814 v_has_est_qty_on_all_bid_lines := check_est_qty_on_all_bid_lines(p_auction_header_id, p_bid_number);
815
816 IF (v_has_est_qty_on_all_bid_lines = 'N') THEN
817 RETURN BID_TOTAL_WARNING;
818 END IF;
819
820 END IF;
821
822
823 RETURN calc_sup_btotal_with_out_ol(p_auction_header_id,
824 p_bid_number,
825 v_outcome,
826 v_supplier_view_type,
827 p_tpid,
828 p_site);
829
830 ELSE -- Submitted bid in an UNTRANSFORMED auction
831
832 IF (v_buyer_bid_total is NULL) THEN
833 RETURN BID_TOTAL_WARNING;
834 ELSE
835 RETURN calc_sup_btotal_with_out_ol(p_auction_header_id,
836 p_bid_number,
837 v_outcome,
838 v_supplier_view_type,
839 p_tpid,
840 p_site);
841 END IF;
842
843 END IF;
844
845 END calc_btotal_with_out_ol;
846
847 FUNCTION calc_sup_btotal_with_out_ol
848 (p_auction_header_id IN NUMBER,
849 p_bid_number IN NUMBER,
850 p_outcome IN pon_auction_headers_all.contract_type%TYPE,
851 p_supplier_view_type IN pon_auction_headers_all.supplier_view_type%TYPE,
852 p_tpid IN NUMBER,
853 p_site IN NUMBER) RETURN NUMBER
854
855 IS
856
857 v_bid_total NUMBER;
858 v_unsol_line_total number;
859 v_unsol_line_count number;
860 v_total NUMBER;
861
862 BEGIN
863
864
865
866 IF (p_supplier_view_type = 'TRANSFORMED') THEN
867
868
869
870
871 SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
872 decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
873 nvl(pbip.price,0)) bid_total
874 INTO v_bid_total
875 FROM pon_bid_item_prices pbip,
876 pon_auction_item_prices_all paip
877 WHERE pbip.auction_header_id = p_auction_header_id AND
878 pbip.bid_number = p_bid_number AND
879 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
880 pbip.auction_header_id = paip.auction_header_id AND
881 nvl(paip.CLM_OPTION_INDICATOR,'-1')<>'O' AND
882 pbip.line_number = paip.line_number AND
883 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
884
885
886
887 ELSE -- UNTRANSFORMED
888
889
890
891
892 SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1,
893 decode(p_outcome, 'STANDARD', nvl(pbip.quantity, 0), paip.quantity)) *
894 nvl(untransform_one_price(paip.auction_header_id, paip.line_number, pbip.price,
895 paip.quantity, p_tpid, p_site),0)) bid_total
896 INTO v_bid_total
897 FROM pon_bid_item_prices pbip,
898 pon_auction_item_prices_all paip
899 WHERE pbip.auction_header_id = p_auction_header_id AND
900 pbip.bid_number = p_bid_number AND
901 nvl(pbip.has_bid_flag, 'N') = 'Y' AND
902 pbip.auction_header_id = paip.auction_header_id AND
903 nvl(paip.CLM_OPTION_INDICATOR,'-1')<>'O' AND
904 pbip.line_number = paip.line_number AND
905 paip.group_type in ('LOT', 'LINE', 'GROUP_LINE');
906
907
908
909 END IF;
910
911 -- Unsolicited Lines Project : Calculate unsolicited lines total.
912 --IF v_bid_total is not null then
913 BEGIN
914 SELECT count(*) INTO v_unsol_line_count
915 FROM pon_bid_item_prices
916 WHERE bid_number = p_bid_number
917 AND auction_line_number = -1
918 AND Nvl(clm_info_flag, 'N') = 'N'
919 AND Nvl(clm_cost_constraint, 'X') NOT IN ('NSP', 'NC');
920
921 EXCEPTION
922 WHEN No_Data_Found THEN
923 v_unsol_line_count := 0;
924 WHEN OTHERS THEN
925 v_unsol_line_count := 0;
926 END;
927
928 IF v_unsol_line_count > 0 THEN
929 v_unsol_line_total := calculate_unsol_line_total(p_auction_header_id, p_bid_number);
930 ELSE
931 v_unsol_line_total := 0;
932 END IF;
933
934
935 --end if;
936
937 --Fix for bug 13565622
938 --If only unsol lines with out quantity are added in offer then donot display offer totals
939 --else if unsol total is null and there is non zero bid total still donot display offer totals
940 --else if all both unsol and sol line totals are not null display the sum
941 IF v_bid_total IS NULL THEN
942 IF(v_unsol_line_count > 0 AND v_unsol_line_total IS NULL) THEN
943 RETURN BID_TOTAL_WARNING;
944 ELSE
945 RETURN v_unsol_line_total;
946 END IF;
947 ELSIF v_unsol_line_total IS NULL THEN
948 RETURN BID_TOTAL_WARNING;
949 ELSE
950 v_total:=v_bid_total+v_unsol_line_total;
951 RETURN v_total;
952 END IF;
953
954
955
956
957 END calc_sup_btotal_with_out_ol;
958
959 /*FUNCTION without_unsol_lines_total(p_auction_header_id IN NUMBER,
960 p_bid_number IN NUMBER) RETURN NUMBER
961 IS
962 v_unsol_line_total NUMBER;
963 BEGIN
964 SELECT SUM(nvl(pbip.quantity,0) * nvl(pbip.price,0))
965 into v_unsol_line_total
966 FROM PON_BID_ITEM_PRICES pbip
967 WHERE
968 pbip.bid_number = p_bid_number and
969 pbip.auction_line_number <> -1 and
970 nvl(pbip.clm_info_flag,'N') <> 'Y';
971
972 RETURN v_unsol_line_total;
973
974 END without_unsol_lines_total;*/
975
976 --Staggered Awards Project
977
978 FUNCTION get_unawarded_bid_total(p_auction_header_id NUMBER,
979 p_bid_number NUMBER) RETURN NUMBER
980
981 IS
982
983 v_unawarded_bid_total NUMBER;
984
985 BEGIN
986
987 SELECT Sum(Nvl(quantity, 0) * Nvl(price, 0))
988 INTO v_unawarded_bid_total
989 FROM pon_bid_item_prices
990 WHERE auction_header_id = p_auction_header_id
991 AND bid_number = p_bid_number
992 AND auction_line_number IN (SELECT line_number
993 FROM pon_auction_item_prices_all
994 WHERE auction_header_id = p_auction_header_id
995 AND Nvl(award_status, 'NO') <> 'COMPLETED');
996
997 RETURN v_unawarded_bid_total;
998 END get_unawarded_bid_total;
999 --
1000 --
1001 END PON_TRANSFORM_BIDDING_PKG;
[Home] [Help]