[Home] [Help]
PACKAGE BODY: APPS.PON_BID_DEFAULTING_PKG
Source
1 PACKAGE BODY pon_bid_defaulting_pkg AS
2 --$Header: PONBDDFB.pls 120.88.12020000.3 2013/02/09 05:16:27 hvutukur ship $
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_BID_DEFAULTING_PKG';
5 g_debug_mode CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 g_module_prefix CONSTANT VARCHAR2(35) := 'pon.plsql.bidDefaultingPkg.';
7 g_bid_rate PON_BID_HEADERS.RATE%TYPE;
8 g_curr_prec FND_CURRENCIES.PRECISION%TYPE;
9 g_advance_negotiable PON_AUCTION_HEADERS_ALL.ADVANCE_NEGOTIABLE_FLAG%TYPE;
10 g_recoupment_negotiable PON_AUCTION_HEADERS_ALL.RECOUPMENT_NEGOTIABLE_FLAG%TYPE;
11 g_prog_pymt_negotiable PON_AUCTION_HEADERS_ALL.PROGRESS_PYMT_NEGOTIABLE_FLAG%TYPE;
12 g_max_rtng_negotiable PON_AUCTION_HEADERS_ALL.MAX_RETAINAGE_NEGOTIABLE_FLAG%TYPE;
13 g_rtng_negotiable PON_AUCTION_HEADERS_ALL.RETAINAGE_NEGOTIABLE_FLAG%TYPE;
14 g_copy_only_from_auc VARCHAR2(1);
15
16
17 -- ======================================================================
18 -- PROCEDURE: LOG_MESSAGE PRIVATE
19 -- PARAMETERS:
20 -- p_module IN Pass the module name
21 -- p_message IN the string to be logged
22 --
23 -- COMMENT: Common procedure to log messages in FND_LOG.
24 -- ======================================================================
25 PROCEDURE log_message
26 (
27 p_module IN VARCHAR2,
28 p_message IN VARCHAR2
29 ) IS
30 BEGIN
31 IF (g_debug_mode = 'Y') THEN
32 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
33 FND_LOG.string(log_level => FND_LOG.level_statement,
34 module => g_module_prefix || p_module,
35 message => p_message);
36 END IF;
37 END IF;
38 END log_message;
39
40 -- ======================================================================
41 -- PROCEDURE: POPULATE_DISPLAY_PF_FLAG PRIVATE
42 -- PARAMETERS:
43 -- p_auc_header_id IN auction_header_id of negotiation
44 -- p_bid_number IN bid number to populate pf flags for
45 -- p_supp_seq_number IN sequence number of current supplier
46 --
47 -- COMMENT: populate line and header display_price_factors_flag
48 -- ======================================================================
49 PROCEDURE populate_display_pf_flag
50 (
51 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
52 p_bid_number IN pon_bid_headers.bid_number%TYPE,
53 p_supp_seq_number IN pon_bidding_parties.sequence%TYPE,
54 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
55 p_batch_end IN pon_bid_item_prices.line_number%TYPE,
56 x_skip_pf_for_batch OUT NOCOPY VARCHAR2
57 ) IS
58 l_supplier_view pon_auction_headers_all.supplier_view_type%TYPE;
59 l_pf_type pon_auction_headers_all.pf_type_allowed%TYPE;
60 l_has_pfs pon_auction_headers_all.has_price_elements%TYPE;
61 BEGIN
62
63
64 -- Get the price factor type info
65 SELECT ah.supplier_view_type,
66 ah.pf_type_allowed,
67 ah.has_price_elements
68 INTO l_supplier_view,
69 l_pf_type,
70 l_has_pfs
71 FROM pon_auction_headers_all ah
72 WHERE ah.auction_header_id = p_auc_header_id;
73
74 -- blindly set to N if pf type is NONE, there are no price factors
75 -- or the view is untransformed (BUYER pf only)
76 IF (l_pf_type = 'NONE' OR l_has_pfs = 'N'
77 OR l_supplier_view = 'UNTRANSFORMED') THEN
78
79 UPDATE pon_bid_item_prices
80 SET display_price_factors_flag = 'N'
81 WHERE bid_number = p_bid_number
82 AND line_number BETWEEN p_batch_start AND p_batch_end;
83
84 x_skip_pf_for_batch := 'Y';
85
86 RETURN;
87 END IF;
88
89 -- Populate line level display_price_factors_flag
90 -- Y if there is a supplier price factor (besides line price)
91 -- N if not (buyer price factors handled by next sql)
92 UPDATE pon_bid_item_prices bl
93 SET bl.display_price_factors_flag =
94 nvl((SELECT 'Y'
95 FROM pon_price_elements apf
96 WHERE apf.auction_header_id = p_auc_header_id
97 AND apf.line_number = bl.line_number
98 AND apf.pf_type = 'SUPPLIER'
99 AND apf.price_element_type_id <> -10
100 AND rownum = 1), 'N')
101 WHERE bl.bid_number = p_bid_number
102 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
103
104 -- The display_price_factors_flag is also set if the line
105 -- has a buyer price factor with a value, regardless of
106 -- whether it is to be displayed to the supplier
107 -- Buyer price factors will not apply in the following cases:
108 -- 1. Supplier/site not on invitation list
109 -- 2. The negotiation only allows SUPPLIER price factors
110 IF (p_supp_seq_number IS NOT null
111 AND l_pf_type <> 'SUPPLIER') THEN
112
113 UPDATE pon_bid_item_prices bl
114 SET bl.display_price_factors_flag =
115 nvl((SELECT 'Y'
116 FROM pon_pf_supplier_values pfv
117 WHERE pfv.auction_header_id = p_auc_header_id
118 AND pfv.line_number = bl.line_number
119 AND pfv.supplier_seq_number = p_supp_seq_number
120 AND nvl(pfv.value, 0) <> 0
121 AND rownum = 1), 'N')
122 WHERE bl.bid_number = p_bid_number
123 -- no need to update lines with supplier price factors
124 AND bl.display_price_factors_flag = 'N'
125 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
126 END IF;
127
128 -- Determine if there are price factors in this batch
129 SELECT decode(count(bl.bid_number), 0, 'Y', 'N')
130 INTO x_skip_pf_for_batch
131 FROM pon_bid_item_prices bl
132 WHERE bl.bid_number = p_bid_number
133 AND bl.display_price_factors_flag = 'Y'
134 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
135 AND rownum = 1;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 x_skip_pf_for_batch := 'N';
140 END populate_display_pf_flag;
141
142 -- ======================================================================
143 -- PROCEDURE: INSERT_AUCTION_LINES PRIVATE
144 -- PARAMETERS:
145 -- p_auc_header_id IN auction header id of negotiation
146 -- p_bid_number IN new bid number
147 -- p_userid IN userid of bid creator
148 -- p_auctpid IN auction creators trading partner id
149 -- p_tpid IN trading partner id of supplier
150 -- p_has_pe_flag IN flag to indicate of auction has price elements
151 -- p_supp_seq_number IN sequence number of supplier for price elements
152 -- p_rate IN rate for bid to auction currency
153 -- p_price_prec IN auction bid currency precision
154 -- p_curr_prec IN bid currency precision
155 --
156 -- COMMENT: Insert missing auction side lines into a draft bid
157 -- ======================================================================
158 PROCEDURE insert_auction_lines
159 (
160 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
161 p_bid_number IN pon_bid_headers.bid_number%TYPE,
162 p_userid IN pon_bid_headers.created_by%TYPE,
163 p_auctpid IN pon_auction_headers_all.trading_partner_id%TYPE,
164 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
165 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
166 p_has_pe_flag IN VARCHAR2,
167 p_blanket IN VARCHAR2,
168 p_full_qty IN VARCHAR2,
169 p_supp_seq_number IN pon_bidding_parties.sequence%TYPE,
170 p_rate IN pon_bid_headers.rate%TYPE,
171 p_price_prec IN pon_bid_headers.number_price_decimals%TYPE,
172 p_curr_prec IN fnd_currencies.precision%TYPE,
173 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
174 p_batch_end IN pon_bid_item_prices.line_number%TYPE
175 ) IS
176 l_skip_pf_for_batch VARCHAR2(1);
177 l_restricted_flag VARCHAR2(1);
178 l_supp_seq_number pon_bidding_parties.sequence%TYPE;
179 BEGIN
180
181
182 /************************************************************
183 * STEP 1: For lines with negotiable shipments, insert all the
184 ** missing bid shipments for only those lines that the supplier
185 ** never attempted to bid on. We need to insert these shipments
186 ** before we actually insert the bid lines so that the "not
187 ** exists" clause doesn't cause data corruption.
188 *************************************************************/
189
190 -- Insert missing shipments for all non-negotiable shipments
191 INSERT INTO pon_bid_shipments
192 (
193 BID_NUMBER,
194 LINE_NUMBER,
195 SHIPMENT_NUMBER,
196 AUCTION_HEADER_ID,
197 AUCTION_LINE_NUMBER,
198 AUCTION_SHIPMENT_NUMBER,
199 SHIPMENT_TYPE,
200 SHIP_TO_ORGANIZATION_ID,
201 SHIP_TO_LOCATION_ID,
202 QUANTITY,
203 MAX_QUANTITY,
204 PRICE_TYPE,
205 PRICE,
206 EFFECTIVE_START_DATE,
207 EFFECTIVE_END_DATE,
208 CREATION_DATE,
209 CREATED_BY,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN,
213 HAS_PRICE_DIFFERENTIALS_FLAG
214 )
215 (SELECT
216 p_bid_number, -- BID_NUMBER
217 apb.line_number, -- LINE_NUMBER
218 apb.shipment_number+1, -- SHIPMENT_NUMBER
219 p_auc_header_id, -- AUCTION_HEADER_ID
220 apb.line_number, -- AUCTION_LINE_NUMBER
221 apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
222 apb.shipment_type, -- SHIPMENT_TYPE
223 apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
224 apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
225 apb.quantity, -- QUANTITY
226 apb.max_quantity, -- MAX_QUANTITY
227 'PRICE', -- PRICE_TYPE
228 apb.price, -- PRICE
229 apb.effective_start_date, -- EFFECTIVE_START_DATE
230 apb.effective_end_date, -- EFFECTIVE_END_DATE
231 sysdate, -- CREATION_DATE
232 p_userid, -- CREATED_BY
233 sysdate, -- LAST_UPDATE_DATE
234 p_userid, -- LAST_UPDATED_BY
235 null, -- LAST_UPDATE_LOGIN
236 apb.has_price_differentials_flag -- HAS_PRICE_DIFFERENTIALS_FLAG
237 FROM pon_auction_shipments_all apb, pon_auction_item_prices_all aip
238 WHERE apb.auction_header_id = p_auc_header_id
239 AND aip.auction_header_id = apb.auction_header_id
240 AND apb.line_number BETWEEN p_batch_start AND p_batch_end
241 AND aip.line_number = apb.line_number
242 AND nvl(aip.price_break_neg_flag, 'N') = 'Y'
243 AND NOT EXISTS
244 (SELECT pbip.line_number
245 FROM pon_bid_item_prices pbip
246 WHERE pbip.bid_number = p_bid_number
247 AND pbip.line_number = apb.line_number));
248
249
250 /************************************************************
251 * STEP 2: Check whether the current supplier was excluded from
252 ** bidding oncertain lines. Set the flags in local variables.
253 ** Logic used is to blindly insert all the lines and then
254 ** delete the lines that the current supplier was excluded from.
255 *************************************************************/
256
257 BEGIN
258 -- Check if the supplier has restricted lines, and get sequence number
259 SELECT decode(bp.access_type, 'RESTRICTED', 'Y', 'N'), bp.sequence
260 INTO l_restricted_flag, l_supp_seq_number
261 FROM pon_bidding_parties bp
262 WHERE bp.auction_header_id = p_auc_header_id
263 AND bp.trading_partner_id = p_tpid
264 AND nvl(bp.vendor_site_id, -1) = p_vensid;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 l_restricted_flag := 'N';
268 l_supp_seq_number := null;
269 END;
270
271
272 /************************************************************
273 * STEP 3: Insert missing auction lines. pon_bid_item_prices
274 ** has an index on (bid_number, line_number) so the EXISTS
275 ** clause does not result in a full table scan.
276 *************************************************************/
277
278 INSERT INTO pon_bid_item_prices
279 (
280 AUCTION_HEADER_ID,
281 AUCTION_LINE_NUMBER,
282 BID_NUMBER,
283 LINE_NUMBER,
284 ITEM_DESCRIPTION,
285 CATEGORY_ID,
286 CATEGORY_NAME,
287 UOM,
288 QUANTITY,
289 LANGUAGE_CODE,
290 CREATION_DATE,
291 CREATED_BY,
292 LAST_UPDATE_DATE,
293 LAST_UPDATED_BY,
294 AUCTION_CREATION_DATE,
295 SHIP_TO_LOCATION_ID,
296 PROXY_BID_FLAG,
297 UNIT_OF_MEASURE,
298 HAS_ATTRIBUTES_FLAG,
299 FREIGHT_TERMS_CODE,
300 TBD_PRICING_FLAG,
301 AUC_TRADING_PARTNER_ID,
302 BID_TRADING_PARTNER_ID,
303 PRICE_BREAK_TYPE,
304 HAS_SHIPMENTS_FLAG,
305 IS_CHANGED_LINE_FLAG,
306 HAS_PRICE_DIFFERENTIALS_FLAG,
307 PRICE_DIFF_SHIPMENT_NUMBER,
308 HAS_BID_FLAG,
309 HAS_BID_PAYMENTS_FLAG,
310 BID_START_PRICE,
311 HAS_QUANTITY_TIERS
312
313 )
314 (SELECT
315 al.auction_header_id, -- AUCTION_HEADER_ID
316 al.line_number, -- AUCTION_LINE_NUMBER
317 p_bid_number, -- BID_NUMBER
318 al.line_number, -- LINE_NUMBER
319 al.item_description, -- ITEM_DESCRIPTION
320 al.category_id, -- CATEGORY_ID
321 al.category_name, -- CATEGORY_NAME
322 al.uom_code, -- UOM
323 decode(p_blanket, 'Y', null,
324 decode(p_full_qty, 'Y', al.quantity,
325 decode(al.group_type, 'LOT_LINE', al.quantity,
326 decode(al.order_type_lookup_code, 'AMOUNT',
327 al.quantity, null)))), -- QUANTITY
328 userenv('LANG'), -- LANGUAGE_CODE
329 SYSDATE, -- CREATION_DATE
330 p_userid, -- CREATED_BY
331 SYSDATE, -- LAST_UPDATE_DATE
332 p_userid, -- LAST_UPDATED_BY
333 al.auction_creation_date, -- AUCTION_CREATION_DATE
334 al.ship_to_location_id, -- SHIP_TO_LOCATION_ID
335 'N', -- PROXY_BID_FLAG
336 al.unit_of_measure, -- UNIT_OF_MEASURE
337 al.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
338 al.freight_terms_code, -- FREIGHT_TERMS_CODE
339 'N', -- TBD_PRICING_FLAG
340 p_auctpid, -- AUC_TRADING_PARTNER_ID
341 p_tpid, -- BID_TRADING_PARTNER_ID
342 al.price_break_type, -- PRICE_BREAK_TYPE
343 al.has_shipments_flag, -- HAS_SHIPMENTS_FLAG
344 'N', -- IS_CHANGED_LINE_FLAG
345 al.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
346 al.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
347 'N', -- HAS_BID_FLAG
348 'N', -- HAS_BID_PAYMENTS_FLAG
349 al.bid_start_price, -- BID_START_PRICE
350 al.has_quantity_tiers -- HAS_QUANTITY_TIERS
351 FROM pon_auction_item_prices_all al
352 WHERE al.auction_header_id = p_auc_header_id
353 AND al.line_number BETWEEN p_batch_start AND p_batch_end
354 AND NOT EXISTS
355 (SELECT bl.line_number
356 FROM pon_bid_item_prices bl
357 WHERE bl.bid_number = p_bid_number
358 AND bl.line_number = al.line_number));
359
360 /************************************************************
361 ** STEP 4: Delete all the excluded lines.
362 *************************************************************/
363
364 IF (l_restricted_flag = 'Y') THEN
365
366 DELETE FROM pon_bid_item_prices bl
367 WHERE bl.bid_number = p_bid_number
368 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
369 AND EXISTS
370 (SELECT le.line_number
371 FROM pon_party_line_exclusions le, pon_auction_item_prices_all al
372 WHERE al.auction_header_id = p_auc_header_id
373 AND al.line_number = bl.line_number
374 AND le.auction_header_id = al.auction_header_id
375 AND le.line_number = nvl(al.parent_line_number, al.line_number)
376 AND le.trading_partner_id = p_tpid
377 AND le.vendor_site_id = p_vensid);
378 END IF;
379
380
381 /************************************************************
382 ** STEP 5: Insert missing line attributes
383 *************************************************************/
384
385 INSERT INTO pon_bid_attribute_values
386 (
387 AUCTION_HEADER_ID,
388 AUCTION_LINE_NUMBER,
389 BID_NUMBER,
390 LINE_NUMBER,
391 ATTRIBUTE_NAME,
392 DATATYPE,
393 CREATION_DATE,
394 CREATED_BY,
395 LAST_UPDATE_DATE,
396 LAST_UPDATED_BY,
397 SEQUENCE_NUMBER,
398 ATTR_LEVEL,
399 ATTR_GROUP_SEQ_NUMBER,
400 ATTR_DISP_SEQ_NUMBER
401 )
402 (SELECT
403 aa.auction_header_id, -- AUCTION_HEADER_ID
404 aa.line_number, -- AUCTION_LINE_NUMBER
405 p_bid_number, -- BID_NUMBER
406 aa.line_number, -- LINE_NUMBER
407 aa.attribute_name, -- ATTRIBUTE_NAME
408 aa.datatype, -- DATATYPE
409 sysdate, -- CREATION_DATE
410 p_userid, -- CREATED_BY
411 sysdate, -- LAST_UPDATE_DATE
412 p_userid, -- LAST_UPDATED_BY
413 aa.sequence_number, -- SEQUENCE_NUMBER
414 aa.attr_level, -- ATTR_LEVEL
415 aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
416 aa.attr_disp_seq_number -- ATTR_DISP_SEQ_NUMBER
417 FROM pon_auction_attributes aa
418 WHERE aa.auction_header_id = p_auc_header_id
419 AND aa.line_number BETWEEN p_batch_start AND p_batch_end
420 AND NOT EXISTS
421 (SELECT pbav.attribute_name
422 FROM pon_bid_attribute_values pbav
423 WHERE pbav.bid_number = p_bid_number
424 AND pbav.line_number = aa.line_number
425 AND pbav.sequence_number = aa.sequence_number));
426
427 /************************************************************
428 ** STEP 6: Insert missing bid cost factors or price elements
429 ** or price factors
430 *************************************************************/
431
432 /************************************************************
433 ** STEP 6a: Populate display_price_factors flag as it is a
434 ** rel12 column
435 *************************************************************/
436
437 populate_display_pf_flag (p_auc_header_id,
438 p_bid_number,
439 p_supp_seq_number,
440 p_batch_start,
441 p_batch_end,
442 l_skip_pf_for_batch);
443
444 /************************************************************
445 ** STEP 6b: Batching enabled inserts - if we are not supposed to
446 ** skip this set of lines in the batch, then go ahead with
447 ** inserts
448 *************************************************************/
449
450 IF (l_skip_pf_for_batch = 'N') THEN
451
452 -- Insert missing SUPPLIER price factors only if they exist
453 IF (p_has_pe_flag = 'Y') THEN
454 INSERT INTO pon_bid_price_elements
455 (
456 BID_NUMBER,
457 LINE_NUMBER,
458 PRICE_ELEMENT_TYPE_ID,
459 AUCTION_HEADER_ID,
460 PRICING_BASIS,
461 NEGATIVE_COST_FACTOR_FLAG,
462 SEQUENCE_NUMBER,
463 CREATION_DATE,
464 CREATED_BY,
465 LAST_UPDATE_DATE,
466 LAST_UPDATED_BY,
467 PF_TYPE
468 )
469 (SELECT
470 p_bid_number, -- BID_NUMBER
471 apf.line_number, -- LINE_NUMBER
472 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
473 p_auc_header_id, -- AUCTION_HEADER_ID
474 apf.pricing_basis, -- PRICING_BASIS
475 apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
476 apf.sequence_number, -- SEQUENCE_NUMBER
477 sysdate, -- CREATION_DATE
478 p_userid, -- CREATED_BY
479 sysdate, -- LAST_UPDATE_DATE
480 p_userid, -- LAST_UPDATED_BY
481 apf.pf_type -- PF_TYPE
482 FROM pon_price_elements apf
483 WHERE apf.auction_header_id = p_auc_header_id
484 AND apf.pf_type = 'SUPPLIER' -- only supplier price factors
485 AND apf.line_number BETWEEN p_batch_start AND p_batch_end
486 AND NOT EXISTS
487 (SELECT pbpe.price_element_type_id
488 FROM pon_bid_price_elements pbpe
489 WHERE pbpe.bid_number = p_bid_number
490 AND pbpe.line_number = apf.line_number
491 AND pbpe.price_element_type_id = apf.price_element_type_id));
492
493
494 END IF;
495
496
497
498 /************************************************************
499 ** STEP 6c: Check for buyer price factors - if this supplier
500 ** is invited to the negotiation.
501 *************************************************************/
502
503 -- Insert missing BUYER price factors if applicable
504 IF (p_supp_seq_number IS NOT null) THEN
505
506 INSERT INTO pon_bid_price_elements
507 (
508 BID_NUMBER,
509 LINE_NUMBER,
510 PRICE_ELEMENT_TYPE_ID,
511 AUCTION_HEADER_ID,
512 PRICING_BASIS,
513 NEGATIVE_COST_FACTOR_FLAG,
514 AUCTION_CURRENCY_VALUE,
515 BID_CURRENCY_VALUE,
516 SEQUENCE_NUMBER,
517 CREATION_DATE,
518 CREATED_BY,
519 LAST_UPDATE_DATE,
520 LAST_UPDATED_BY,
521 PF_TYPE
522 )
523 (SELECT
524 p_bid_number, -- BID_NUMBER
525 apf.line_number, -- LINE_NUMBER
526 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
527 p_auc_header_id, -- AUCTION_HEADER_ID
528 apf.pricing_basis, -- PRICING_BASIS
529 apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
530 pf.value, -- AUCTION_CURRENCY_VALUE
531 decode(apf.pricing_basis,
532 'PER_UNIT', round(pf.value * p_rate, p_price_prec),
533 'FIXED_AMOUNT', round(pf.value * p_rate, p_curr_prec),
534 'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
535 apf.sequence_number, -- SEQUENCE_NUMBER
536 sysdate, -- CREATION_DATE
537 p_userid, -- CREATED_BY
538 sysdate, -- LAST_UPDATE_DATE
539 p_userid, -- LAST_UPDATED_BY
540 apf.pf_type -- PF_TYPE
541 FROM pon_price_elements apf,
542 pon_pf_supplier_values pf,
543 pon_bid_item_prices bl
544 WHERE apf.auction_header_id = p_auc_header_id
545 AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
546 AND apf.display_to_suppliers_flag = 'Y'
547 AND bl.bid_number = p_bid_number
548 AND bl.line_number = apf.line_number
549 AND bl.display_price_factors_flag = 'Y'
550 AND pf.auction_header_id = apf.auction_header_id
551 AND pf.line_number = apf.line_number
552 AND pf.pf_seq_number = apf.sequence_number
553 AND pf.supplier_seq_number = p_supp_seq_number
554 AND nvl(pf.value, 0) <> 0
555 AND apf.line_number BETWEEN p_batch_start AND p_batch_end
556 AND NOT EXISTS
557 (SELECT pbpe.price_element_type_id
558 FROM pon_bid_price_elements pbpe
559 WHERE pbpe.bid_number = p_bid_number
560 AND pbpe.line_number = apf.line_number
561 AND pbpe.price_element_type_id = apf.price_element_type_id));
562
563 END IF;
564 END IF;
565
566 -- Insert missing shipments for all non-negotiable shipments
567
568
569 /************************************************************
570 ** STEP 7: Insert all the missing non-negotiable or required
571 ** or mandatory shipments/price breaks.
572 *************************************************************/
573
574 /*
575 * Price Tiers Enhancements
576 * Quantity tiers are negotiable shipments so no need to copy the max_quantity field here
577 */
578
579 INSERT INTO pon_bid_shipments
580 (
581 BID_NUMBER,
582 LINE_NUMBER,
583 SHIPMENT_NUMBER,
584 AUCTION_HEADER_ID,
585 AUCTION_LINE_NUMBER,
586 AUCTION_SHIPMENT_NUMBER,
587 SHIPMENT_TYPE,
588 SHIP_TO_ORGANIZATION_ID,
589 SHIP_TO_LOCATION_ID,
590 QUANTITY,
591 PRICE_TYPE,
592 PRICE,
593 EFFECTIVE_START_DATE,
594 EFFECTIVE_END_DATE,
595 CREATION_DATE,
596 CREATED_BY,
597 LAST_UPDATE_DATE,
598 LAST_UPDATED_BY,
599 LAST_UPDATE_LOGIN,
600 HAS_PRICE_DIFFERENTIALS_FLAG
601 )
602 (SELECT
603 p_bid_number, -- BID_NUMBER
604 apb.line_number, -- LINE_NUMBER
605 apb.shipment_number+1, -- SHIPMENT_NUMBER
606 p_auc_header_id, -- AUCTION_HEADER_ID
607 apb.line_number, -- AUCTION_LINE_NUMBER
608 apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
609 apb.shipment_type, -- SHIPMENT_TYPE
610 apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
611 apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
612 apb.quantity, -- QUANTITY
613 'PRICE', -- PRICE_TYPE
614 apb.price, -- PRICE
615 apb.effective_start_date, -- EFFECTIVE_START_DATE
616 apb.effective_end_date, -- EFFECTIVE_END_DATE
617 sysdate, -- CREATION_DATE
618 p_userid, -- CREATED_BY
619 sysdate, -- LAST_UPDATE_DATE
620 p_userid, -- LAST_UPDATED_BY
621 null, -- LAST_UPDATE_LOGIN
622 apb.has_price_differentials_flag -- HAS_PRICE_DIFFERENTIALS_FLAG
623 FROM pon_auction_shipments_all apb, pon_auction_item_prices_all aip
624 WHERE apb.auction_header_id = p_auc_header_id
625 AND apb.line_number BETWEEN p_batch_start AND p_batch_end
626 AND aip.auction_header_id = apb.auction_header_id
627 AND aip.line_number = apb.line_number
628 AND nvl(aip.price_break_neg_flag, 'Y') = 'N'
629 AND NOT EXISTS
630 (SELECT pbs.auction_shipment_number
631 FROM pon_bid_shipments pbs
632 WHERE pbs.bid_number = p_bid_number
633 AND pbs.line_number = apb.line_number
634 AND pbs.auction_shipment_number = apb.shipment_number));
635
636 /************************************************************
637 ** STEP 8: Insert all the missing line-level as well as shipment
638 ** level price breaks in a single insert statement.
639 *************************************************************/
640
641 INSERT INTO pon_bid_price_differentials
642 (
643 AUCTION_HEADER_ID,
644 BID_NUMBER,
645 LINE_NUMBER,
646 SHIPMENT_NUMBER,
647 PRICE_DIFFERENTIAL_NUMBER,
648 PRICE_TYPE,
649 CREATION_DATE,
650 CREATED_BY,
651 LAST_UPDATE_DATE,
652 LAST_UPDATED_BY,
653 LAST_UPDATE_LOGIN
654 )
655 (SELECT
656 p_auc_header_id, -- AUCTION_HEADER_ID
657 p_bid_number, -- BID_NUMBER
658 apd.line_number, -- LINE_NUMBER
659 decode(apd.shipment_number, -1, -1, apd.shipment_number+1), -- SHIPMENT_NUMBER
660 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
661 apd.price_type, -- PRICE_TYPE
662 sysdate, -- CREATION_DATE
663 p_userid, -- CREATED_BY
664 sysdate, -- LAST_UPDATE_DATE
665 p_userid, -- LAST_UPDATED_BY
666 null -- LAST_UPDATE_LOGIN
667 FROM pon_price_differentials apd
668 WHERE apd.auction_header_id = p_auc_header_id
669 AND apd.line_number BETWEEN p_batch_start AND p_batch_end
670 AND NOT EXISTS
671 (SELECT pbpd.price_differential_number
672 FROM pon_bid_price_differentials pbpd
673 WHERE pbpd.bid_number = p_bid_number
674 AND pbpd.line_number = apd.line_number
675 AND pbpd.price_differential_number = apd.price_differential_number));
676
677 END insert_auction_lines;
678
679 -- ======================================================================
680 -- PROCEDURE: POPULATE_OLD_VALUE_COLUMNS PRIVATE
681 -- PARAMETERS:
682 -- p_bid_number IN new bid number
683 -- p_source_bid_num IN source bid number
684 --
685 -- COMMENT: Populate old value columns for a bid
686 -- ======================================================================
687 PROCEDURE populate_old_value_columns
688 (
689 p_bid_number IN pon_bid_headers.bid_number%TYPE,
690 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
691 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
692 p_batch_end IN pon_bid_item_prices.line_number%TYPE
693 ) IS
694 BEGIN
695
696 -- Update line level old value columns
697 UPDATE pon_bid_item_prices bl
698 SET (bl.old_price,
699 bl.old_bid_currency_unit_price,
700 bl.old_bid_currency_price,
701 bl.old_bid_currency_limit_price,
702 bl.old_po_bid_min_rel_amount,
703 bl.old_quantity,
704 bl.old_publish_date,
705 bl.old_promised_date,
706 bl.old_note_to_auction_owner,
707 bl.old_bid_curr_advance_amount,
708 bl.old_recoupment_rate_percent,
709 bl.old_progress_pymt_rate_percent,
710 bl.old_retainage_rate_percent,
711 bl.old_bid_curr_max_retainage_amt) =
712 (SELECT
713 old_bl.price,
714 old_bl.bid_currency_unit_price,
715 old_bl.bid_currency_price,
716 old_bl.bid_currency_limit_price,
717 old_bl.po_bid_min_rel_amount,
718 old_bl.quantity,
719 old_bl.publish_date,
720 old_bl.promised_date,
721 old_bl.note_to_auction_owner,
722 old_bl.bid_curr_advance_amount,
723 old_bl.recoupment_rate_percent,
724 old_bl.progress_pymt_rate_percent,
725 old_bl.retainage_rate_percent,
726 old_bl.bid_curr_max_retainage_amt
727 FROM pon_bid_item_prices old_bl
728 WHERE old_bl.bid_number = p_source_bid_num
729 AND old_bl.line_number = bl.line_number)
730 WHERE bl.bid_number = p_bid_number
731 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
732
733 -- Update attribute old value columns
734 UPDATE pon_bid_attribute_values ba
735 SET ba.old_value =
736 (SELECT old_ba.value
737 FROM pon_bid_attribute_values old_ba
738 WHERE old_ba.bid_number = p_source_bid_num
739 AND old_ba.line_number = ba.line_number
740 AND old_ba.attribute_name = ba.attribute_name)
741 WHERE ba.bid_number = p_bid_number
742 AND ba.line_number BETWEEN p_batch_start AND p_batch_end;
743
744 -- Update SUPPLIER price factor old value columns
745 UPDATE pon_bid_price_elements bpf
746 SET bpf.old_bid_currency_value =
747 (SELECT old_bpf.bid_currency_value
748 FROM pon_bid_price_elements old_bpf
749 WHERE old_bpf.bid_number = p_source_bid_num
750 AND old_bpf.line_number = bpf.line_number
751 AND old_bpf.price_element_type_id = bpf.price_element_type_id)
752 WHERE bpf.bid_number = p_bid_number
753 AND bpf.pf_type = 'SUPPLIER'
754 AND bpf.line_number BETWEEN p_batch_start AND p_batch_end;
755
756 -- Update price break old value columns
757 UPDATE pon_bid_shipments bpb
758 SET (bpb.old_bid_currency_unit_price,
759 bpb.old_bid_currency_price,
760 bpb.old_price_discount,
761 bpb.old_ship_to_org_id,
762 bpb.old_ship_to_loc_id,
763 bpb.old_effective_start_date,
764 bpb.old_effective_end_date,
765 bpb.old_quantity,
766 bpb.old_max_quantity,
767 bpb.old_price_type) =
768 (SELECT
769 old_bpb.bid_currency_unit_price,
770 old_bpb.bid_currency_price,
771 old_bpb.price_discount,
772 old_bpb.ship_to_organization_id,
773 old_bpb.ship_to_location_id,
774 old_bpb.effective_start_date,
775 old_bpb.effective_end_date,
776 old_bpb.quantity,
777 old_bpb.max_quantity,
778 old_bpb.price_type
779 FROM pon_bid_shipments old_bpb
780 WHERE old_bpb.bid_number = p_source_bid_num
781 AND old_bpb.line_number = bpb.line_number
782 AND old_bpb.shipment_number = bpb.shipment_number)
783 WHERE bpb.bid_number = p_bid_number
784 AND bpb.line_number BETWEEN p_batch_start AND p_batch_end;
785
786 -- Update price differential old value columns
787 UPDATE pon_bid_price_differentials bpd
788 SET bpd.old_multiplier =
789 (SELECT old_bpd.multiplier
790 FROM pon_bid_price_differentials old_bpd
791 WHERE old_bpd.bid_number = p_source_bid_num
792 AND old_bpd.line_number = bpd.line_number
793 AND old_bpd.shipment_number = bpd.shipment_number
794 AND old_bpd.price_differential_number = bpd.price_differential_number)
795 WHERE bpd.bid_number = p_bid_number
796 AND bpd.line_number BETWEEN p_batch_start AND p_batch_end;
797
798 END populate_old_value_columns;
799
800 -- ======================================================================
801 -- PROCEDURE: HANDLE_PROXY PRIVATE
802 -- PARAMETERS:
803 -- p_auc_header_id IN the auction header id
804 -- p_draft_bid_num IN bid number to update proxy for
805 -- p_tpid IN trading partner id of supplier
806 -- p_tpcid IN trading partner contact id of supplier
807 -- p_vensid IN vendor site bid is placed on
808 -- p_evaluator_id IN evaluator user id
809 -- p_eval_flag IN flag indicating if the response is an evaluation
810 -- x_prev_bid_number OUT returned backing bid number
811 -- x_rebid_flag OUT Y/N if the current bid is a rebid/not a rebid
812 --
813 -- COMMENT: updates price, limit_price, and copy_price_for_proxy_flag
814 -- First finds the backing ACTIVE bid, if it exists and determine
815 -- if the bid was a rebid
816 -- ======================================================================
817 PROCEDURE handle_proxy
818 (
819 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
820 p_draft_bid_num IN pon_bid_headers.bid_number%TYPE,
821 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
822 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
823 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
824 ------------ Supplier Management: Supplier Evaluation ------------
825 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
826 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
827 ------------------------------------------------------------------
828 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
829 x_rebid_flag OUT NOCOPY VARCHAR2
830 ) IS
831 l_max_line_number pon_bid_item_prices.line_number%TYPE;
832 l_batch_start pon_bid_item_prices.line_number%TYPE;
833 l_batch_end pon_bid_item_prices.line_number%TYPE;
834 BEGIN
835
836 -- Determine the latest ACTIVE bid and set rebid_flag to Y
837 -- Since there can only exist a single ACTIVE bid on an amendment for
838 -- a particular user on a site, we use the rownum = 1 optimisation
839
840 -- It is possible that another supplier from the same supplier company
841 -- is modifying the draft. So we can't use the login in supplier's tpcid,
842 -- we should use the tpcid of the user who creates the draft
843
844 ------- Supplier Management: Supplier Evaluation -------
845 -- Add evaluator_id and evaluation_flag to the query --
846 --------------------------------------------------------
847 SELECT bh.bid_number, 'Y'
848 INTO x_prev_bid_number, x_rebid_flag
849 FROM pon_bid_headers bh
850 WHERE bh.auction_header_id = p_auc_header_id
851 AND bh.trading_partner_id = p_tpid
852 AND bh.trading_partner_contact_id =
853 (SELECT trading_partner_contact_id
854 FROM pon_bid_headers bh2
855 WHERE bh2.bid_number = p_draft_bid_num)
856 AND bh.vendor_site_id = p_vensid
857 AND bh.bid_status = 'ACTIVE'
858 AND nvl(bh.evaluator_id, -1) = nvl(p_evaluator_id, -1)
859 AND nvl(bh.evaluation_flag, 'N') = p_eval_flag
860 AND rownum = 1
861 ORDER BY bh.publish_date DESC;
862
863 -- Update old_bid_number to new source bid
864 UPDATE pon_bid_headers bh
865 SET bh.old_bid_number = x_prev_bid_number
866 WHERE bh.bid_number = p_draft_bid_num;
867
868 -- START BATCHING
869
870 -- Determine the maximum line number for the negotiation
871 SELECT ah.max_internal_line_num
872 INTO l_max_line_number
873 FROM pon_auction_headers_all ah
874 WHERE ah.auction_header_id = p_auc_header_id;
875
876 -- Define the initial batch range (line numbers are indexed from 1)
877 l_batch_start := 1;
878 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
879 l_batch_end := l_max_line_number;
880 ELSE
881 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
882 END IF;
883
884 WHILE (l_batch_start <= l_max_line_number) LOOP
885
886 -- Copy over price columns and set copy_price_for_proxy_flag
887 -- If the proxy has been exhausted, copy_price_for_proxy flag changed to N
888 UPDATE pon_bid_item_prices bl
889 SET (bl.price, bl.proxy_bid_limit_price, bl.bid_currency_price,
890 bl.bid_currency_limit_price, bl.bid_currency_trans_price,
891 bl.unit_price, bl.bid_currency_unit_price, bl.copy_price_for_proxy_flag,
892 bl.old_price, bl.old_bid_currency_unit_price, bl.old_bid_currency_price,
893 bl.old_bid_currency_limit_price) =
894 (SELECT old_bl.price, old_bl.proxy_bid_limit_price, old_bl.bid_currency_price,
895 old_bl.bid_currency_limit_price, old_bl.bid_currency_trans_price,
896 old_bl.unit_price, old_bl.bid_currency_unit_price,
897 decode(sign(old_bl.proxy_bid_limit_price - old_bl.price),
898 0, 'N', 'Y'),
899 old_bl.price, old_bl.bid_currency_unit_price, old_bl.bid_currency_price, old_bl.bid_currency_limit_price
900 FROM pon_bid_item_prices old_bl
901 WHERE old_bl.bid_number = x_prev_bid_number
902 AND old_bl.line_number = bl.line_number)
903 WHERE bl.bid_number = p_draft_bid_num
904 AND bl.copy_price_for_proxy_flag = 'Y'
905 AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
906
907 -- Copy over the rank for all lines
908 UPDATE pon_bid_item_prices bl
909 SET rank =
910 (SELECT old_bl.rank
911 FROM pon_bid_item_prices old_bl
912 WHERE old_bl.bid_number = x_prev_bid_number
913 AND old_bl.line_number = bl.line_number)
914 WHERE bl.bid_number = p_draft_bid_num
915 AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
916
917 -- Find the new batch range
918 l_batch_start := l_batch_end + 1;
919 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
920 l_batch_end := l_max_line_number;
921 ELSE
922 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
923 END IF;
924
925 END LOOP;
926
927 -- END BATCHING
928
929 EXCEPTION
930 -- No ACTIVE bids on the current amendment
931 WHEN NO_DATA_FOUND THEN
932 SELECT bh.old_bid_number
933 INTO x_prev_bid_number
934 FROM pon_bid_headers bh
935 WHERE bh.bid_number = p_draft_bid_num;
936 x_rebid_flag := 'N';
937
938 END handle_proxy;
939
940 -- ======================================================================
941 -- PROCEDURE: INSERT_INTO_BID_HEADER PRIVATE
942 -- PARAMETERS:
943 -- p_auc_header_id IN auction_header_id of negotiation
944 -- p_source_bid_num IN source_bid to default from
945 -- p_tpid IN trading partner id of supplier
946 -- p_tpname IN trading partner name of supplier
947 -- p_tpcid IN trading partner contact id of supplier
948 -- p_tpcname IN trading partner contact name of supplier
949 -- p_userid IN userid of bid creator
950 -- p_venid IN vendor id
951 -- p_vensid IN vendor site id to place bid for
952 -- p_venscode IN vendor site code to place bid for
953 -- p_auctpid IN buyers trading partner id
954 -- p_auctpcid IN buyers trading partner contact id
955 -- p_buyer_user IN flag indicating surrogate bid or not
956 -- p_evaluator_id IN evaluator user id
957 -- p_eval_flag IN flag indicating if the response is an evaluation
958 -- p_rebid_flag IN flag indicating rebid or not
959 -- x_bid_number OUT bid number of the new bid
960 --
961 -- COMMENT: inserts a bid header for the new bid. Also generates the bid number
962 -- ======================================================================
963 PROCEDURE insert_into_bid_header
964 (
965 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
966 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
967 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
968 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
969 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
970 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
971 p_userid IN pon_bid_headers.created_by%TYPE,
972 p_venid IN pon_bid_headers.vendor_id%TYPE,
973 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
974 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
975 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
976 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
977 p_buyer_user IN VARCHAR2,
978 ----------- Supplier Management: Supplier Evaluation -----------
979 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
980 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
981 ----------------------------------------------------------------
982 p_rebid_flag IN VARCHAR2,
983 p_prev_bid_disq IN VARCHAR2,
984 p_prev_bid_wthd IN VARCHAR2,
985 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
986 ) IS
987 l_old_min_bid_change_type pon_auction_headers_all.min_bid_change_type%TYPE;
988 l_old_min_bid_change pon_auction_headers_all.min_bid_decrement%TYPE;
989 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
990 ----------------------------------------------------------------
991 l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%TYPE;
992 l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%TYPE;
993 ----------------------------------------------------------------
994 BEGIN
995
996 IF p_source_bid_num IS NOT NULL AND p_source_bid_num <> 0 THEN
997 SELECT pah.min_bid_change_type,
998 pah.min_bid_decrement
999 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1000 -------------------------------------------------------
1001 , pah.TWO_PART_FLAG
1002 , pah.TECHNICAL_EVALUATION_STATUS
1003 -------------------------------------------------------
1004 INTO l_old_min_bid_change_type,
1005 l_old_min_bid_change
1006 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1007 -------------------------------------------------------
1008 , l_two_part_flag
1009 , l_technical_evaluation_status
1010 -------------------------------------------------------
1011 FROM PON_AUCTION_HEADERS_ALL pah,
1012 PON_BID_HEADERS pbh
1013 WHERE pah.auction_header_id = pbh.auction_header_id
1014 AND pbh.bid_number = p_source_bid_num;
1015 END IF;
1016
1017 -- Generate next bid number
1018 SELECT pon_bid_headers_s.nextval INTO x_bid_number
1019 FROM dual;
1020
1021 INSERT INTO pon_bid_headers
1022 (
1023 BID_NUMBER,
1024 AUCTION_HEADER_ID,
1025 BIDDERS_BID_NUMBER,
1026 BID_TYPE,
1027 CONTRACT_TYPE,
1028 TRADING_PARTNER_CONTACT_NAME,
1029 TRADING_PARTNER_CONTACT_ID,
1030 TRADING_PARTNER_NAME,
1031 TRADING_PARTNER_ID,
1032 BID_STATUS,
1033 BID_EFFECTIVE_DATE,
1034 BID_EXPIRATION_DATE,
1035 DISQUALIFY_REASON,
1036 FREIGHT_TERMS_CODE,
1037 CARRIER_CODE,
1038 FOB_CODE,
1039 NOTE_TO_AUCTION_OWNER,
1040 LANGUAGE_CODE,
1041 CREATION_DATE,
1042 CREATED_BY,
1043 LAST_UPDATE_DATE,
1044 LAST_UPDATED_BY,
1045 AUCTION_CREATION_DATE,
1046 BID_CURRENCY_CODE,
1047 RATE,
1048 MIN_BID_CHANGE ,
1049 PROXY_BID_FLAG ,
1050 NUMBER_PRICE_DECIMALS,
1051 DOCTYPE_ID,
1052 VENDOR_ID,
1053 VENDOR_SITE_ID ,
1054 RATE_DSP,
1055 INITIATE_APPROVAL,
1056 DRAFT_LOCKED,
1057 DRAFT_LOCKED_BY,
1058 DRAFT_LOCKED_BY_CONTACT_ID,
1059 DRAFT_LOCKED_DATE,
1060 VENDOR_SITE_CODE,
1061 SHORTLIST_FLAG,
1062 ATTRIBUTE_LINE_NUMBER,
1063 NOTE_TO_SUPPLIER,
1064 SURROG_BID_CREATED_TP_ID,
1065 SURROG_BID_CREATED_CONTACT_ID,
1066 SURROG_BID_RECEIPT_DATE,
1067 SURROG_BID_ONLINE_ENTRY_DATE,
1068 SURROG_BID_FLAG,
1069 COLOR_SEQUENCE_ID,
1070 OLD_NOTE_TO_AUCTION_OWNER,
1071 OLD_BIDDERS_BID_NUMBER,
1072 OLD_BID_EXPIRATION_DATE,
1073 OLD_MIN_BID_CHANGE,
1074 OLD_BID_STATUS,
1075 OLD_SURROG_BID_RECEIPT_DATE,
1076 REL12_DRAFT_FLAG,
1077 OLD_BID_NUMBER
1078 --added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1079 --------------------------------------------------------------
1080 , SUBMIT_STAGE
1081 --------------------------------------------------------------
1082 ---- Supplier Management: Supplier Evaluation ----
1083 ,EVALUATOR_ID
1084 ,EVALUATION_FLAG
1085 --------------------------------------------------
1086 )
1087 (SELECT
1088 x_bid_number, -- BID_NUMBER
1089 ah.auction_header_id, -- AUCTION_HEADER_ID
1090 bh.bidders_bid_number, -- BIDDERS_BID_NUMBER
1091 'REVERSE', -- BID_TYPE
1092 ah.contract_type, -- CONTRACT_TYPE
1093 p_tpcname, -- TRADING_PARTNER_CONTACT_NAME
1094 p_tpcid, -- TRADING_PARTNER_CONTACT_ID
1095 p_tpname, -- TRADING_PARTNER_NAME
1096 p_tpid, -- TRADING_PARTNER_ID
1097 'DRAFT', -- BID_STATUS
1098 bh.bid_effective_date, -- BID_EFFECTIVE_DATE
1099 bh.bid_expiration_date, -- BID_EXPIRATION_DATE
1100 decode(p_prev_bid_disq, 'Y', bh.disqualify_reason, null), -- DISQUALIFY_REASON
1101 ah.freight_terms_code, -- FREIGHT_TERMS_CODE
1102 ah.carrier_code, -- CARRIER_CODE
1103 ah.fob_code, -- FOB_CODE
1104 bh.note_to_auction_owner, -- NOTE_TO_AUCTION_OWNER
1105 userenv('LANG'), -- LANGUAGE_CODE
1106 SYSDATE, -- CREATION_DATE
1107 p_userid, -- CREATED_BY
1108 SYSDATE, -- LAST_UPDATE_DATE
1109 p_userid, -- LAST_UPDATED_BY
1110 ah.creation_date, -- AUCTION_CREATION_DATE
1111 nvl(bh.bid_currency_code, ah.currency_code), -- BID_CURRENCY_CODE
1112 nvl(bh.rate, 1), -- RATE
1113 decode(ah.min_bid_change_type, l_old_min_bid_change_type,
1114 decode(ah.min_bid_decrement, l_old_min_bid_change, bh.min_bid_change, null), null), -- MIN_BID_CHANGE
1115 'N', -- PROXY_BID_FLAG
1116 nvl(bh.number_price_decimals, ah.number_price_decimals), -- NUMBER_PRICE_DECIMALS
1117 ah.doctype_id, -- DOCTYPE_ID
1118 p_venid, -- VENDOR_ID
1119 p_vensid, -- VENDOR_SITE_ID
1120 nvl(bh.rate_dsp, 1), -- RATE_DSP
1121 bh.initiate_approval, -- INITIATE_APPROVAL
1122 'Y', -- DRAFT_LOCKED
1123 decode(p_buyer_user, 'Y', p_auctpid, p_tpid), -- DRAFT_LOCKED_BY
1124 -- Begin Supplier Management: Supplier Evaluation
1125 -- Modified the following:
1126 decode(p_buyer_user, 'Y', p_auctpcid, decode(p_eval_flag, 'Y', p_evaluator_id, p_tpcid)), -- DRAFT_LOCKED_BY_CONTACT_ID
1127 -- End Supplier Management: Supplier Evaluation
1128 SYSDATE, -- DRAFT_LOCKED_DATE
1129 p_venscode, -- VENDOR_SITE_CODE
1130 'N', -- SHORTLIST_FLAG
1131 -1, -- ATTRIBUTE_LINE_NUMBER
1132 ah.note_to_bidders, -- NOTE_TO_SUPPLIER
1133 decode(p_buyer_user, 'Y', p_auctpid, null), -- SURROG_BID_CREATED_TP_ID
1134 decode(p_buyer_user, 'Y', p_auctpcid, null), -- SURROG_BID_CREATED_CONTACT_ID
1135 decode(p_buyer_user, 'Y',
1136 Decode((SELECT TWO_PART_FLAG FROM pon_auction_headers_all WHERE AUCTION_HEADER_ID = bh.AUCTION_HEADER_ID),'Y',
1137 Decode(bh.SUBMIT_STAGE,'COMMERCIAL',bh.surrog_bid_receipt_date, null),NULL),NULL),
1138 --decode(p_buyer_user, 'Y', bh.surrog_bid_receipt_date, null), -- SURROG_BID_RECEIPT_DATE
1139 decode(p_buyer_user, 'Y', sysdate, null), -- SURROG_BID_ONLINE_ENTRY_DATE
1140 p_buyer_user, -- SURROG_BID_FLAG
1141 bh.color_sequence_id, -- COLOR_SEQUENCE_ID
1142 decode(p_rebid_flag, 'Y', bh.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
1143 decode(p_rebid_flag, 'Y', bh.bidders_bid_number, null), -- OLD_BIDDERS_BID_NUMBER
1144 decode(p_rebid_flag, 'Y', bh.bid_expiration_date, null), -- OLD_BID_EXPIRATION_DATE
1145 decode(p_rebid_flag, 'Y', bh.min_bid_change, null), -- OLD_MIN_BID_CHANGE
1146 decode(p_rebid_flag, 'Y', bh.bid_status, null), -- OLD_BID_STATUS
1147 decode(p_rebid_flag, 'Y', bh.surrog_bid_receipt_date, null), -- OLD_SURROG_BID_RECEIPT_DATE
1148 'Y', -- REL12_DRAFT_FLAG
1149 decode(p_source_bid_num, 0, null, p_source_bid_num) -- OLD_BID_NUMBER
1150 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1151 -----------------------------------------------------------------------------
1152 -- set submit_stage to TECHNICAL when requoting in commercial stage
1153 , decode(p_rebid_flag, 'Y', -- SUBMIT_STAGE
1154 decode(l_two_part_flag, 'Y',
1155 decode(p_buyer_user, 'Y',
1156 decode(l_technical_evaluation_status, 'COMPLETED', 'TECHNICAL', null), null), null), null)
1157 -----------------------------------------------------------------------------
1158 ---------------- Supplier Management: Supplier Evaluation ----------------
1159 ,decode(p_eval_flag, 'Y', p_evaluator_id, null) -- EVALUATOR_ID
1160 ,p_eval_flag -- EVALUATION_FLAG
1161 --------------------------------------------------------------------------
1162 FROM pon_auction_headers_all ah, pon_bid_headers bh
1163 WHERE ah.auction_header_id = p_auc_header_id
1164 AND bh.bid_number (+) = p_source_bid_num
1165 AND ah.auction_header_id >= bh.auction_header_id (+));
1166
1167 END insert_into_bid_header;
1168
1169 -- ======================================================================
1170 -- PROCEDURE: INSERT_INTO_BID_ITEMS PRIVATE
1171 -- PARAMETERS:
1172 -- p_auc_header_id IN auction_header_id of negotiation
1173 -- p_bid_number IN bid number to insert lines for
1174 -- p_source_bid_num IN source_bid to default from
1175 -- p_tpid IN trading partner id of supplier
1176 -- p_userid IN userid of bid creator
1177 -- p_vensid IN vendor site id to place bid for
1178 -- p_rebid_flag IN flag indicating rebid or not
1179 -- p_restricted_flag IN flag indicating whether certain lines may be restricted
1180 --
1181 -- COMMENT: inserts lines for the new bid, defualting as necessary
1182 -- ======================================================================
1183 PROCEDURE insert_into_bid_items
1184 (
1185 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1186 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1187 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1188 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
1189 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
1190 p_userid IN pon_bid_headers.created_by%TYPE,
1191 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
1192 p_rebid_flag IN VARCHAR2,
1193 p_restricted_flag IN VARCHAR2,
1194 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1195 p_batch_end IN pon_bid_item_prices.line_number%TYPE,
1196 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE
1197 ) IS
1198 l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
1199 l_blanket VARCHAR2(1);
1200 l_full_qty VARCHAR2(1);
1201 l_enforce_prevrnd_price_flag VARCHAR2(1);
1202 l_prev_rnd_active_bid_number NUMBER;
1203 l_auction_header_id_prev_round NUMBER;
1204 l_unit_price NUMBER;
1205 l_quantity NUMBER;
1206 l_is_paused pon_auction_headers_all.is_paused%TYPE;
1207 l_last_pause_date pon_auction_headers_all.last_pause_date%TYPE;
1208 l_closed_compare_date DATE;
1209 /***********************************************************
1210 * CLM - Complex Pricing project Changes Start.
1211 *********************************************************/
1212 l_return_status VARCHAR2(100);
1213 l_err_msg VARCHAR2(100);
1214 l_uda_template_id NUMBER;
1215 l_doctype_id pon_auction_headers_all.DOCTYPE_ID%TYPE;
1216 /***********************************************************
1217 * CLM - Complex Pricing project Changes End.
1218 *********************************************************/
1219 l_is_federal NUMBER; --ELIN project
1220 l_org_id pon_auction_headers_all.org_id%TYPE; --ELINs project
1221
1222 BEGIN
1223
1224
1225 /***********************************************************
1226 * CLM - Complex Pricing project Changes Start.
1227 *********************************************************/
1228 BEGIN
1229 l_uda_template_id := null;
1230
1231 --ELINs project : Get org_id
1232 select doctype_id, org_id
1233 into l_doctype_id, l_org_id
1234 from pon_auction_headers_all
1235 where auction_header_id = p_auc_header_id;
1236
1237 --ELIN project
1238 l_is_federal := PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => l_doctype_id);
1239
1240 IF PON_CLM_UTIL_PKG.IS_UDA_ENABLED(p_doc_type_id => l_doctype_id) = 1 THEN
1241 l_uda_template_id := po_uda_data_util.get_template_id(
1242 p_functional_area => 'SOURCING',
1243 p_document_type => 'OFFER',
1244 p_document_style_id => NULL,
1245 p_document_level => 'LINE',
1246 p_input_date => sysdate,
1247 x_return_status => l_return_status,
1248 x_err_msg => l_err_msg);
1249 END IF;
1250 END;
1251 /***********************************************************
1252 * CLM - Complex Pricing project Changes End.
1253 *********************************************************/
1254 SELECT ah.trading_partner_id,
1255 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
1256 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
1257 enforce_prevrnd_bid_price_flag,
1258 auction_header_id_prev_round,
1259 is_paused,
1260 last_pause_date
1261 INTO l_auctpid,
1262 l_blanket,
1263 l_full_qty,
1264 l_enforce_prevrnd_price_flag,
1265 l_auction_header_id_prev_round,
1266 l_is_paused,
1267 l_last_pause_date
1268 FROM pon_auction_headers_all ah
1269 WHERE ah.auction_header_id = p_auc_header_id;
1270
1271 INSERT INTO pon_bid_item_prices
1272 (
1273 AUCTION_HEADER_ID,
1274 AUCTION_LINE_NUMBER,
1275 BID_NUMBER,
1276 LINE_NUMBER,
1277 ITEM_DESCRIPTION,
1278 CATEGORY_ID,
1279 CATEGORY_NAME,
1280 UOM,
1281 QUANTITY,
1282 PRICE,
1283 MINIMUM_BID_PRICE,
1284 PROMISED_DATE,
1285 NOTE_TO_AUCTION_OWNER,
1286 LANGUAGE_CODE,
1287 CREATION_DATE,
1288 CREATED_BY,
1289 LAST_UPDATE_DATE,
1290 LAST_UPDATED_BY,
1291 AUCTION_CREATION_DATE,
1292 SHIP_TO_LOCATION_ID,
1293 PUBLISH_DATE,
1294 PROXY_BID_LIMIT_PRICE,
1295 PROXY_BID_LIMIT_PRICE_DATE,
1296 BID_CURRENCY_PRICE,
1297 BID_CURRENCY_LIMIT_PRICE,
1298 PROXY_BID_FLAG,
1299 FIRST_BID_PRICE,
1300 UNIT_OF_MEASURE,
1301 HAS_ATTRIBUTES_FLAG,
1302 FREIGHT_TERMS_CODE,
1303 TBD_PRICING_FLAG,
1304 AUC_TRADING_PARTNER_ID,
1305 BID_TRADING_PARTNER_ID,
1306 TOTAL_WEIGHTED_SCORE,
1307 RANK,
1308 PO_MIN_REL_AMOUNT,
1309 PO_BID_MIN_REL_AMOUNT,
1310 PRICE_BREAK_TYPE,
1311 HAS_SHIPMENTS_FLAG,
1312 IS_CHANGED_LINE_FLAG,
1313 HAS_PRICE_DIFFERENTIALS_FLAG,
1314 PRICE_DIFF_SHIPMENT_NUMBER,
1315 BID_CURRENCY_TRANS_PRICE,
1316 UNIT_PRICE,
1317 BID_CURRENCY_UNIT_PRICE,
1318 GROUP_AMOUNT,
1319 HAS_BID_PAYMENTS_FLAG,
1320 ADVANCE_AMOUNT,
1321 BID_CURR_ADVANCE_AMOUNT,
1322 RECOUPMENT_RATE_PERCENT,
1323 PROGRESS_PYMT_RATE_PERCENT,
1324 RETAINAGE_RATE_PERCENT,
1325 MAX_RETAINAGE_AMOUNT,
1326 BID_CURR_MAX_RETAINAGE_AMT,
1327 OLD_NO_OF_PAYMENTS,
1328 OLD_PRICE,
1329 OLD_BID_CURRENCY_UNIT_PRICE,
1330 OLD_BID_CURRENCY_PRICE,
1331 OLD_BID_CURRENCY_LIMIT_PRICE,
1332 OLD_PO_BID_MIN_REL_AMOUNT,
1333 OLD_QUANTITY,
1334 OLD_PUBLISH_DATE,
1335 OLD_PROMISED_DATE,
1336 OLD_NOTE_TO_AUCTION_OWNER,
1337 HAS_BID_FLAG,
1338 OLD_BID_CURR_ADVANCE_AMOUNT,
1339 OLD_RECOUPMENT_RATE_PERCENT,
1340 OLD_PROGRESS_PYMT_RATE_PERCENT,
1341 OLD_RETAINAGE_RATE_PERCENT,
1342 OLD_BID_CURR_MAX_RETAINAGE_AMT,
1343 COPY_PRICE_FOR_PROXY_FLAG,
1344 BID_START_PRICE,
1345 HAS_QUANTITY_TIERS,
1346 /***********************************************************
1347 * CLM - Clin Slin project Changes Start.
1348 *********************************************************/
1349 --- default the CLM attributes
1350 LINE_NUM_DISPLAY ,
1351 GROUP_LINE_ID,
1352 CLM_INFO_FLAG,
1353 CLM_OPTION_INDICATOR,
1354 CLM_BASE_LINE_NUM,
1355 CLM_OPTION_NUM,
1356 CLM_OPTION_FROM_DATE,
1357 CLM_OPTION_TO_DATE,
1358 CLM_FUNDED_FLAG,
1359
1360 /***********************************************************
1361 * CLM - Clin Slin project Changes End.
1362 *********************************************************/
1363 /***********************************************************
1364 * CLM - Complex Pricing project Changes Start.
1365 *********************************************************/
1366 CLM_COST_CONSTRAINT,
1367 CLM_CONTRACT_TYPE,
1368 CLM_IDC_TYPE,
1369 UDA_TEMPLATE_ID,
1370 /***********************************************************
1371 * CLM - Complex Pricing project Changes End.
1372 *********************************************************/
1373 HAS_USER_BID_FLAG, --ELINs project
1374 IS_REBID_ON_LINE_FLAG, --ELINs project
1375 -- Event Based Delivery Project
1376 CLM_PROMISE_PERIOD,
1377 CLM_PROMISE_PERIOD_UOM,
1378 CLM_PROMISE_POP_DURATION,
1379 CLM_PROMISE_POP_DURATION_UOM
1380
1381 )
1382 (SELECT
1383 al.auction_header_id, -- AUCTION_HEADER_ID
1384 al.line_number, -- AUCTION_LINE_NUMBER
1385 p_bid_number, -- BID_NUMBER
1386 al.line_number, -- LINE_NUMBER
1387 al.item_description, -- ITEM_DESCRIPTION
1388 al.category_id, -- CATEGORY_ID
1389 al.category_name, -- CATEGORY_NAME
1390 al.uom_code, -- UOM
1391 decode(al.modified_date-old_al.modified_date,
1392 0, bl.quantity, decode(l_blanket, 'Y', null,
1393 decode(l_full_qty, 'Y', al.quantity,
1394 decode(al.group_type, 'LOT_LINE', al.quantity,
1395 decode(al.order_type_lookup_code, 'AMOUNT',
1396 al.quantity, null))))), -- QUANTITY
1397 decode(al.modified_date-old_al.modified_date,
1398 0, bl.price, null), -- PRICE
1399 decode(al.modified_date-old_al.modified_date,
1400 0, bl.minimum_bid_price, null), -- MINIMUM_BID_PRICE
1401 decode(al.modified_date-old_al.modified_date,
1402 0, bl.promised_date, null), -- PROMISED_DATE
1403 decode(al.modified_date-old_al.modified_date,
1404 0, bl.note_to_auction_owner, null), -- NOTE_TO_AUCTION_OWNER
1405 userenv('LANG'), -- LANGUAGE_CODE
1406 SYSDATE, -- CREATION_DATE
1407 p_userid, -- CREATED_BY
1408 SYSDATE, -- LAST_UPDATE_DATE
1409 p_userid, -- LAST_UPDATED_BY
1410 al.auction_creation_date, -- AUCTION_CREATION_DATE
1411 al.ship_to_location_id, -- SHIP_TO_LOCATION_ID
1412 decode(al.modified_date-old_al.modified_date,
1413 0, bl.publish_date, null), -- PUBLISH_DATE
1414 decode(al.modified_date-old_al.modified_date,
1415 0, bl.proxy_bid_limit_price, null), -- PROXY_BID_LIMIT_PRICE
1416 decode(al.modified_date-old_al.modified_date,
1417 0, bl.proxy_bid_limit_price_date, null), -- PROXY_BID_LIMIT_PRICE_DATE
1418 decode(al.modified_date-old_al.modified_date,
1419 0, bl.bid_currency_price, null), -- BID_CURRENCY_PRICE
1420 decode(al.modified_date-old_al.modified_date,
1421 0, bl.bid_currency_limit_price, null), -- BID_CURRENCY_LIMIT_PRICE
1422 'N', -- PROXY_BID_FLAG
1423 decode(al.modified_date-old_al.modified_date,
1424 0, bl.first_bid_price, null), -- FIRST_BID_PRICE
1425 al.unit_of_measure, -- UNIT_OF_MEASURE
1426 al.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
1427 al.freight_terms_code, -- FREIGHT_TERMS_CODE
1428 'N', -- TBD_PRICING_FLAG
1429 l_auctpid, -- AUC_TRADING_PARTNER_ID
1430 p_tpid, -- BID_TRADING_PARTNER_ID
1431 decode(al.modified_date-old_al.modified_date,
1432 0, bl.total_weighted_score, null), -- TOTAL_WEIGHTED_SCORE
1433 decode(p_rebid_flag, 'Y', bl.rank, null), -- RANK
1434 decode(al.modified_date-old_al.modified_date,
1435 0, bl.po_min_rel_amount, null), -- PO_MIN_REL_AMOUNT
1436 decode(al.modified_date-old_al.modified_date,
1437 0, bl.po_bid_min_rel_amount, null), -- PO_BID_MIN_REL_AMOUNT
1438 al.price_break_type, -- PRICE_BREAK_TYPE
1439 decode(al.modified_date-old_al.modified_date,
1440 0, bl.has_shipments_flag, al.has_shipments_flag), -- HAS_SHIPMENTS_FLAG
1441 -- Rebid: set changed_line to N
1442 -- Otherwise it is the same as the has_bid_flag
1443 --CLM QA Bug : 9835426 : NC,NSP lines, assume as always modified
1444 /* Decode(Nvl(al.clm_cost_constraint,'X'),'NC','Y',
1445 decode(nvl(al.clm_cost_constraint,'X'),'NSP','Y',
1446 decode(p_rebid_flag, 'Y', 'N',
1447 decode(al.modified_date-old_al.modified_date, 0,
1448 nvl(bl.has_bid_flag, 'N'), 'N')))),*/
1449 decode(p_rebid_flag, 'Y', 'N',
1450 decode(al.modified_date-old_al.modified_date, 0,
1451 nvl(bl.has_bid_flag, 'N'), 'N')), -- IS_CHANGED_LINE_FLAG
1452 al.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
1453 al.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
1454 decode(al.modified_date-old_al.modified_date,
1455 0, bl.bid_currency_trans_price, null), -- BID_CURRENCY_TRANS_PRICE
1456 decode(al.modified_date-old_al.modified_date,
1457 0, bl.unit_price, null), -- UNIT_PRICE
1458 decode(al.modified_date-old_al.modified_date,
1459 0, bl.bid_currency_unit_price, null), -- BID_CURRENCY_UNIT_PRICE
1460 decode(al.modified_date-old_al.modified_date,
1461 0, bl.group_amount, null), -- GROUP_AMOUNT
1462 decode(g_copy_only_from_auc, 'Y', al.has_payments_flag,
1463 decode(al.modified_date-old_al.modified_date,0,bl.has_bid_payments_flag,al.has_payments_flag
1464 )
1465 ),--HAS_BID_PAYMENTS_FLAG
1466 decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.advance_amount,al.advance_amount
1467 )
1468 , al.advance_amount
1469 ),--ADVANCE_AMOUNT
1470 decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.bid_curr_advance_amount
1471 ,round(al.advance_amount * g_bid_rate, g_curr_prec)
1472 )
1473 , round(al.advance_amount * g_bid_rate, g_curr_prec)
1474 ),--BID_CURR_ADVANCE_AMOUNT
1475 decode(al.modified_date-old_al.modified_date, 0,decode(g_recoupment_negotiable,'Y',bl.recoupment_rate_percent
1476 ,al.recoupment_rate_percent
1477 )
1478 , al.recoupment_rate_percent
1479 ),--RECOUPMENT_RATE_PERCENT
1480 decode(al.modified_date-old_al.modified_date, 0,decode(g_prog_pymt_negotiable,'Y',bl.progress_pymt_rate_percent
1481 ,al.progress_pymt_rate_percent
1482 )
1483 , al.progress_pymt_rate_percent
1484 ),--PROGRESS_PYMT_RATE_PERCENT
1485 decode(al.modified_date-old_al.modified_date, 0,decode(g_rtng_negotiable,'Y',bl.retainage_rate_percent,al.retainage_rate_percent
1486 )
1487 , al.retainage_rate_percent
1488 ),--RETAINAGE_RATE_PERCENT
1489 decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.max_retainage_amount,al.max_retainage_amount
1490 )
1491 , al.max_retainage_amount
1492 ),--MAX_RETAINAGE_AMOUNT
1493 decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.bid_curr_max_retainage_amt
1494 , round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
1495 )
1496 , round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
1497 ),--BID_CURR_MAX_RETAINAGE_AMT
1498 decode(p_rebid_flag, 'Y', (select count(1) from pon_bid_payments_shipments
1499 where bid_number=bl.bid_number and bid_line_number=bl.line_number)
1500 ,null
1501 ), --OLD_NO_OF_PAYMENTS
1502 decode(p_rebid_flag, 'Y', bl.price, null), -- OLD_PRICE
1503 decode(p_rebid_flag, 'Y', bl.bid_currency_unit_price, null),-- OLD_BID_CURRENCY_UNIT_PRICE
1504 decode(p_rebid_flag, 'Y', bl.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
1505 decode(p_rebid_flag, 'Y', bl.bid_currency_limit_price, null), -- OLD_BID_CURRENCY_LIMIT_PRICE
1506 decode(p_rebid_flag, 'Y', bl.po_bid_min_rel_amount, null), -- OLD_PO_BID_MIN_REL_AMOUNT
1507 decode(p_rebid_flag, 'Y', bl.quantity, null), -- OLD_QUANTITY
1508 decode(p_rebid_flag, 'Y', bl.publish_Date, null), -- OLD_PUBLISH_DATE
1509 decode(p_rebid_flag, 'Y', bl.promised_Date, null), -- OLD_PROMISED_DATE
1510 decode(p_rebid_flag, 'Y', bl.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
1511 -- If the line was modified, set to N, else set to source has_bid_flag
1512 -- If the source has_bid_flag is null, set to N since there was no source bid
1513 --CLM QA Bug : 9835426 : For NC, NSP lines assume as always modified
1514 /* decode(nvl(al.clm_cost_constraint,'X'),'NC','Y',
1515 decode(nvl(al.clm_cost_constraint,'X'),'NSP','Y',
1516 decode(al.modified_date-old_al.modified_date, 0,
1517 nvl(bl.has_bid_flag, 'N'), 'N'))), */
1518 decode(al.modified_date-old_al.modified_date, 0,
1519 nvl(bl.has_bid_flag, 'N'), 'N'), -- HAS_BID_FLAG
1520 decode(p_rebid_flag, 'Y', bl.bid_curr_advance_amount, null), -- OLD_BID_CURR_ADVANCE_AMOUNT
1521 decode(p_rebid_flag, 'Y', bl.recoupment_rate_percent, null), -- OLD_RECOUPMENT_RATE_PERCENT
1522 decode(p_rebid_flag, 'Y', bl.progress_pymt_rate_percent, null), -- OLD_PROGRESS_PYMT_RATE_PERCENT
1523 decode(p_rebid_flag, 'Y', bl.retainage_rate_percent, null), -- OLD_RETAINAGE_RATE_PERCENT
1524 decode(p_rebid_flag, 'Y', bl.bid_curr_max_retainage_amt, null), -- OLD_BID_CURR_MAX_RETAINAGE_AMT
1525 decode(p_rebid_flag, 'Y',
1526 decode(sign(bl.proxy_bid_limit_price-bl.price), -1, 'Y', 'N'), 'N'), -- COPY_PRICE_FOR_PROXY_FLAG
1527 -- if re bid set the start price as source bid start price
1528 decode(p_rebid_flag, 'Y', bl.bid_start_price, al.bid_start_price),
1529 decode(al.modified_date-old_al.modified_date,
1530 0, bl.has_quantity_tiers, al.has_quantity_tiers), -- HAS_quantity_tiers
1531 /***********************************************************
1532 * CLM - Clin Slin project Changes Start.
1533 *********************************************************/
1534 al.LINE_NUM_DISPLAY ,
1535 al.GROUP_LINE_ID,
1536 al.CLM_INFO_FLAG,
1537 al.CLM_OPTION_INDICATOR,
1538 al.CLM_BASE_LINE_NUM,
1539 al.CLM_OPTION_NUM,
1540 al.CLM_OPTION_FROM_DATE,
1541 al.CLM_OPTION_TO_DATE,
1542 al.CLM_FUNDED_FLAG,
1543
1544 /***********************************************************
1545 * CLM - Clin Slin project Changes End.
1546 *********************************************************/
1547 /***********************************************************
1548 * CLM - Complex Pricing project Changes Start.
1549 *********************************************************/
1550 al.CLM_COST_CONSTRAINT,
1551 al.CLM_CONTRACT_TYPE,
1552 al.CLM_IDC_TYPE,
1553 -- Decode(al.CLM_CONTRACT_TYPE, NULL, NULL, l_uda_template_id)
1554 l_uda_template_id,
1555 /***********************************************************
1556 * CLM - Complex Pricing project Changes End.
1557 *********************************************************/
1558 --ELINs project
1559 decode(al.modified_date-old_al.modified_date, 0,
1560 nvl(bl.has_user_bid_flag, 'N'), 'N'), -- HAS_USER_BID_FLAG
1561 Decode(l_is_federal, 1,
1562 Decode(p_rebid_flag, 'Y',Nvl(bl.has_bid_flag,'N'), 'N'), ''), --IS_REBID_ON_LINE_FLAG
1563 -- Event Based Delivery Project
1564 decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_PERIOD, null), -- CLM_PROMISE_PERIOD
1565 decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_PERIOD_UOM, null), -- CLM_PROMISE_PERIOD_UOM
1566 decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_POP_DURATION, null), -- CLM_PROMISE_POP_DURATION
1567 decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_POP_DURATION_UOM, null) -- CLM_PROMISE_POP_DURATION_UOM
1568 FROM pon_auction_item_prices_all al,
1569 pon_auction_item_prices_all old_al,
1570 pon_bid_item_prices bl
1571 WHERE al.auction_header_id = p_auc_header_id
1572 AND bl.bid_number(+) = p_source_bid_num
1573 AND bl.line_number(+) = al.line_number
1574 AND old_al.auction_header_id (+) = bl.auction_header_id
1575 AND old_al.line_number (+) = bl.line_number
1576 AND al.line_number BETWEEN p_batch_start AND p_batch_end);
1577
1578 -- determine if there exists an active bid in the previous round
1579 -- for the supplier/contact/site
1580 -- this will be used to populate the bid_start_price column
1581 -- added condition for p_rebid_flag to ensure that for a new response only start price will get calculated
1582 IF (l_enforce_prevrnd_price_flag = 'Y' AND p_rebid_flag <> 'Y') THEN
1583 BEGIN
1584 SELECT MAX(bid_number)
1585 INTO l_prev_rnd_active_bid_number
1586 FROM pon_bid_headers bh
1587 WHERE bh.auction_header_id = l_auction_header_id_prev_round
1588 AND bh.trading_partner_id = p_tpid
1589 AND bh.trading_partner_contact_id = p_tpcid
1590 AND bh.bid_status ='ACTIVE'
1591 AND NVL(bh.vendor_site_id, -1) = NVL(p_vensid, -1);
1592
1593 IF l_prev_rnd_active_bid_number IS NOT NULL THEN
1594 -- if active bid exists then
1595 -- update the bid_start_price for the current bid lines
1596 -- using values from the previous round auction/bid
1597 UPDATE pon_bid_item_prices bl
1598 SET bid_start_price = nvl((SELECT pon_bid_defaulting_pkg.apply_price_factors(p_auc_header_id, l_prev_rnd_active_bid_number, al.line_number, bl1.unit_price, bl1.quantity)
1599 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl1
1600 WHERE al.auction_header_id = l_auction_header_id_prev_round
1601 AND al.line_number = bl.line_number
1602 AND al.line_number = bl1.line_number
1603 AND bl1.bid_number = l_prev_rnd_active_bid_number), bid_start_price)
1604 WHERE bl.bid_number = p_bid_number
1605 AND bl.has_bid_flag = 'Y';
1606 END IF;
1607
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610 l_enforce_prevrnd_price_flag := 'N';
1611 END;
1612 END IF;
1613 -- end 'start price for multi round negotiations' code
1614
1615 -- Delete excluded lines
1616 IF (p_restricted_flag = 'Y') THEN
1617
1618 DELETE FROM pon_bid_item_prices bl
1619 WHERE bl.bid_number = p_bid_number
1620 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
1621 AND EXISTS
1622 (SELECT le.line_number
1623 FROM pon_party_line_exclusions le, pon_auction_item_prices_all al
1624 WHERE al.auction_header_id = p_auc_header_id
1625 AND al.line_number = bl.line_number
1626 AND le.auction_header_id = al.auction_header_id
1627 AND le.line_number = coalesce(al.parent_line_number, al.group_line_id, al.clm_base_line_num,al.line_number)
1628 AND le.trading_partner_id = p_tpid
1629 AND le.vendor_site_id = p_vensid);
1630
1631 /* ELINs project : If exhibits are enabled, delete elins based on
1632 * restriction on associated line.
1633 * Delete elins associated to slins of a restricted clin.
1634 * Delete elins associated to option lines of a restricted line.
1635 */
1636 IF PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(l_org_id, l_doctype_id) = 'Y' THEN
1637
1638 DELETE FROM pon_bid_item_prices bl_outer
1639 WHERE bl_outer.bid_number = p_bid_number
1640 AND bl_outer.line_number BETWEEN p_batch_start AND p_batch_end
1641 AND bl_outer.line_number IN
1642 (SELECT al_outer.line_number
1643 FROM pon_auction_exhibit_details exh_outer,
1644 (SELECT al.line_number line_number
1645 FROM pon_auction_item_prices_all al
1646 WHERE al.auction_header_id = p_auc_header_id
1647 AND NOT EXISTS
1648 (SELECT bl.line_number
1649 FROM pon_bid_item_prices bl
1650 WHERE bl.auction_header_id = al.auction_header_id
1651 AND bl.bid_number = p_bid_number
1652 AND bl.line_number = al.line_number)) missing_bid_lines,
1653 pon_auction_item_prices_all al_outer
1654 WHERE exh_outer.auction_header_id = p_auc_header_id
1655 AND Nvl(exh_outer.is_cdrl, 'N') = 'N'
1656 AND exh_outer.associated_to_line = missing_bid_lines.line_number
1657 AND al_outer.auction_header_id = exh_outer.auction_header_id
1658 AND al_outer.exhibit_number IS NOT NULL
1659 AND al_outer.exhibit_number = exh_outer.exhibit_number);
1660
1661 END IF; --End if
1662 END IF;
1663
1664
1665 -- In case of a non-rebid and non surrogate bid do not copy over
1666 -- closed lines.
1667 log_message ('insert_into_bid_items', 'p_rebid_flag = ' || p_rebid_flag ||
1668 ', p_surrog_bid_flag = ' || p_surrog_bid_flag ||
1669 ', l_is_paused = ' || l_is_paused ||
1670 ', l_closed_compare_date = ' || to_char (l_closed_compare_date, 'dd-mon-yyyy hh24:mi:ss') ||
1671 ', l_last_pause_date = ' || to_char (l_last_pause_date, 'dd-mon-yyyy hh24:mi:ss'));
1672
1673 IF ( nvl (p_rebid_flag, 'N') = 'N' AND nvl (p_surrog_bid_flag, 'N') = 'N') THEN
1674
1675 log_message ('insert_into_bid_items', 'This is not a rebid and this is not a surrogate bid.');
1676
1677 IF (nvl (l_is_paused, 'N') = 'Y') THEN
1678 l_closed_compare_date := l_last_pause_date;
1679 ELSE
1680 l_closed_compare_date := sysdate;
1681 END IF;
1682
1683 DELETE FROM pon_bid_item_prices bl
1684 WHERE bl.bid_number = p_bid_number
1685 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
1686 AND EXISTS (SELECT al.line_number
1687 FROM pon_auction_item_prices_all al
1688 WHERE al.auction_header_id = p_auc_header_id
1689 AND al.line_number = bl.line_number
1690 AND al.close_bidding_date < l_closed_compare_date);
1691 END IF;
1692
1693 END insert_into_bid_items;
1694
1695 -- ======================================================================
1696 -- PROCEDURE: INSERT_INTO_HEADER_ATTRIBUTES PRIVATE
1697 -- PARAMETERS:
1698 -- p_auc_header_id IN auction_header_id of negotiation
1699 -- p_bid_number IN bid number to insert attributes for
1700 -- p_source_bid_num IN source_bid to default from
1701 -- p_userid IN userid of bid creator
1702 -- p_evaluator_id IN evaluator user id
1703 -- p_eval_flag IN flag indicating if the response is an evaluation
1704 -- p_rebid_flag IN flag indicating rebid or not
1705 --
1706 -- COMMENT: inserts header attributes for the new bid, defualting as necessary
1707 -- ======================================================================
1708 PROCEDURE insert_into_header_attributes
1709 (
1710 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1711 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1712 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1713 p_userid IN pon_bid_headers.created_by%TYPE,
1714 ----------- Supplier Management: Supplier Evaluation -----------
1715 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
1716 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
1717 ----------------------------------------------------------------
1718 p_rebid_flag IN VARCHAR2
1719 ) IS
1720
1721 l_has_scoring_teams_flag pon_auction_headers_all.has_scoring_teams_flag%TYPE;
1722
1723 BEGIN
1724
1725 -- Bug 5046909 - Determine if team scoring is enabled
1726 -- If team scoring is enabled, we do not copy the score over from the
1727 -- earlier bid. If team scoring is not enabled, we copy the score over
1728 -- for a header attribute only if the attribute has not been modified.
1729 -- In both cases, scores by individual scorers are not copied over -
1730 -- only the final one is
1731 -- Adding this SELECT from auction_headers_all - ideally, this should
1732 -- be combined and only one call made for the entire flow...will log a
1733 -- tracking bug for this
1734
1735
1736 SELECT has_scoring_teams_flag
1737 INTO l_has_scoring_teams_flag
1738 FROM pon_auction_headers_all
1739 WHERE auction_header_id = p_auc_header_id;
1740
1741 -- Insert header attributes
1742 INSERT INTO pon_bid_attribute_values
1743 (
1744 AUCTION_HEADER_ID,
1745 AUCTION_LINE_NUMBER,
1746 BID_NUMBER,
1747 LINE_NUMBER,
1748 ATTRIBUTE_NAME,
1749 DATATYPE,
1750 VALUE,
1751 CREATION_DATE,
1752 CREATED_BY,
1753 LAST_UPDATE_DATE,
1754 LAST_UPDATED_BY,
1755 SCORE,
1756 SEQUENCE_NUMBER,
1757 ATTR_LEVEL,
1758 WEIGHTED_SCORE,
1759 ATTR_GROUP_SEQ_NUMBER,
1760 ATTR_DISP_SEQ_NUMBER,
1761 OLD_VALUE
1762 )
1763 (SELECT
1764 aa.auction_header_id, -- AUCTION_HEADER_ID
1765 aa.line_number, -- AUCTION_LINE_NUMBER
1766 p_bid_number, -- BID_NUMBER
1767 aa.line_number, -- LINE_NUMBER
1768 aa.attribute_name, -- ATTRIBUTE_NAME
1769 aa.datatype, -- DATATYPE
1770 decode(aa.modified_date-old_aa.modified_date,
1771 0, ba.value, null), -- VALUE
1772 sysdate, -- CREATION_DATE
1773 p_userid, -- CREATED_BY
1774 sysdate, -- LAST_UPDATE_DATE
1775 p_userid, -- LAST_UPDATED_BY
1776 DECODE(l_has_scoring_teams_flag, 'N',decode(aa.modified_date-old_aa.modified_date,
1777 0, ba.score, null),'Y', NULL), -- SCORE
1778 aa.sequence_number, -- SEQUENCE_NUMBER
1779 aa.attr_level, -- ATTR_LEVEL
1780 decode(l_has_scoring_teams_flag, 'N', decode(aa.modified_date-old_aa.modified_date,
1781 0, ba.weighted_score, null), 'Y', NULL), -- WEIGHTED_SCORE
1782 aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
1783 aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
1784 decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
1785 FROM pon_auction_attributes aa,
1786 pon_bid_attribute_values ba,
1787 pon_auction_attributes old_aa
1788 WHERE aa.auction_header_id = p_auc_header_id
1789 AND aa.line_number= -1
1790 AND ba.bid_number (+) = p_source_bid_num
1791 AND ba.line_number (+) = aa.line_number
1792 AND ba.sequence_number (+) = aa.sequence_number
1793 AND ba.auction_header_id = old_aa.auction_header_id (+)
1794 AND ba.line_number = old_aa.line_number (+)
1795 AND ba.sequence_number = old_aa.sequence_number (+));
1796
1797 -- Begin Supplier Management: Bug 12369949
1798 -- For evaluation response, need to clear value and score for sections
1799 -- not currently assigned to the evaluator
1800 IF (p_eval_flag = 'Y') THEN
1801 UPDATE pon_bid_attribute_values
1802 SET value = NULL,
1803 score = NULL,
1804 weighted_score = NULL
1805 WHERE auction_header_id = p_auc_header_id
1806 AND line_number = -1
1807 AND bid_number = p_bid_number
1808 AND attr_group_seq_number NOT IN
1809 (SELECT pas.attr_group_seq_number
1810 FROM pon_auction_sections pas,
1811 pon_evaluation_team_sections pets,
1812 pon_evaluation_team_members petm,
1813 fnd_user fu
1814 WHERE pas.auction_header_id = p_auc_header_id
1815 AND pets.auction_header_id = p_auc_header_id
1816 AND petm.auction_header_id = p_auc_header_id
1817 AND pas.section_id = pets.section_id
1818 AND pets.team_id = petm.team_id
1819 AND petm.user_id = fu.user_id
1820 AND fu.person_party_id = p_evaluator_id
1821 );
1822 END IF;
1823 -- End Supplier Management: Bug 12369949
1824
1825 END insert_into_header_attributes;
1826
1827 -- ======================================================================
1828 -- PROCEDURE: INSERT_INTO_LINE_ATTRIBUTES PRIVATE
1829 -- PARAMETERS:
1830 -- p_auc_header_id IN auction_header_id of negotiation
1831 -- p_bid_number IN bid number to insert attributes for
1832 -- p_source_bid_num IN source_bid to default from
1833 -- p_userid IN userid of bid creator
1834 -- p_rebid_flag IN flag indicating rebid or not
1835 --
1836 -- COMMENT: inserts line attributes for the new bid, defualting as necessary
1837 -- ======================================================================
1838 PROCEDURE insert_into_line_attributes
1839 (
1840 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1841 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1842 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1843 p_userid IN pon_bid_headers.created_by%TYPE,
1844 p_rebid_flag IN VARCHAR2,
1845 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1846 p_batch_end IN pon_bid_item_prices.line_number%TYPE
1847 ) IS
1848 BEGIN
1849
1850 -- Insert line attributes
1851 INSERT INTO pon_bid_attribute_values
1852 (
1853 AUCTION_HEADER_ID,
1854 AUCTION_LINE_NUMBER,
1855 BID_NUMBER,
1856 LINE_NUMBER,
1857 ATTRIBUTE_NAME,
1858 DATATYPE,
1859 VALUE,
1860 CREATION_DATE,
1861 CREATED_BY,
1862 LAST_UPDATE_DATE,
1863 LAST_UPDATED_BY,
1864 SCORE,
1865 SEQUENCE_NUMBER,
1866 ATTR_LEVEL,
1867 WEIGHTED_SCORE,
1868 ATTR_GROUP_SEQ_NUMBER,
1869 ATTR_DISP_SEQ_NUMBER,
1870 OLD_VALUE
1871 )
1872 -- NOTE: we check the has_bid_flag because it is 'N' if
1873 -- the line has been modified since the defaulting happened
1874 (SELECT
1875 aa.auction_header_id, -- AUCTION_HEADER_ID
1876 aa.line_number, -- AUCTION_LINE_NUMBER
1877 p_bid_number, -- BID_NUMBER
1878 aa.line_number, -- LINE_NUMBER
1879 aa.attribute_name, -- ATTRIBUTE_NAME
1880 aa.datatype, -- DATATYPE
1881 decode(bl.has_bid_flag, 'Y', ba.value, null), -- VALUE
1882 sysdate, -- CREATION_DATE
1883 p_userid, -- CREATED_BY
1884 sysdate, -- LAST_UPDATE_DATE
1885 p_userid, -- LAST_UPDATED_BY
1886 decode(bl.has_bid_flag, 'Y', ba.score, null), -- SCORE
1887 aa.sequence_number, -- SEQUENCE_NUMBER
1888 aa.attr_level, -- ATTR_LEVEL
1889 decode(bl.has_bid_flag, 'Y', ba.weighted_score, null), -- WEIGHTED_SCORE
1890 aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
1891 aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
1892 decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
1893 FROM pon_auction_attributes aa,
1894 pon_bid_attribute_values ba,
1895 pon_bid_item_prices bl
1896 WHERE aa.auction_header_id = p_auc_header_id
1897 AND aa.line_number > 0
1898 AND bl.bid_number = p_bid_number
1899 AND bl.line_number = aa.line_number
1900 AND ba.bid_number (+) = p_source_bid_num
1901 AND ba.line_number (+) = aa.line_number
1902 AND ba.sequence_number (+) = aa.sequence_number
1903 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
1904
1905 END insert_into_line_attributes;
1906
1907 -- ======================================================================
1908 -- PROCEDURE: INSERT_INTO_PRICE_FACTORS PRIVATE
1909 -- PARAMETERS:
1910 -- p_auc_header_id IN auction_header_id of negotiation
1911 -- p_bid_number IN bid number to insert price factors for
1912 -- p_source_bid_num IN source_bid to default from
1913 -- p_userid IN userid of bid creator
1914 -- p_supp_seq_number IN sequence number if supplier was invited
1915 -- p_rebid_flag IN flag indicating rebid or not
1916 --
1917 -- COMMENT: insert price factors for the new bid, defualting as necessary
1918 -- ======================================================================
1919 PROCEDURE insert_into_price_factors
1920 (
1921 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1922 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1923 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1924 p_userid IN pon_bid_headers.created_by%TYPE,
1925 p_supp_seq_number IN pon_pf_supplier_values.supplier_seq_number%TYPE,
1926 p_rebid_flag IN VARCHAR2,
1927 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1928 p_batch_end IN pon_bid_item_prices.line_number%TYPE
1929 ) IS
1930 l_price_prec pon_bid_headers.number_price_decimals%TYPE;
1931 l_curr_prec fnd_currencies.precision%TYPE;
1932 l_rate pon_bid_headers.rate%TYPE;
1933 l_supplier_view pon_auction_headers_all.supplier_view_type%TYPE;
1934 l_pf_type pon_auction_headers_all.pf_type_allowed%TYPE;
1935 BEGIN
1936
1937 -- Get bid currency precisions and rate
1938 SELECT bh.number_price_decimals,
1939 cu.precision,
1940 bh.rate
1941 INTO l_price_prec,
1942 l_curr_prec,
1943 l_rate
1944 FROM pon_bid_headers bh,
1945 fnd_currencies cu
1946 WHERE bh.bid_number = p_bid_number
1947 AND cu.currency_code = bh.bid_currency_code;
1948
1949 -- Get the price factor type info
1950 SELECT ah.supplier_view_type, ah.pf_type_allowed
1951 INTO l_supplier_view, l_pf_type
1952 FROM pon_auction_headers_all ah
1953 WHERE ah.auction_header_id = p_auc_header_id;
1954
1955 -- copy over all supplier price factors, including line price
1956 -- do not copy line price pf for lines with display_price_factors_flag = N
1957 IF (l_supplier_view <> 'UNTRANSFORMED') THEN
1958
1959 INSERT INTO pon_bid_price_elements
1960 (
1961 BID_NUMBER,
1962 LINE_NUMBER,
1963 PRICE_ELEMENT_TYPE_ID,
1964 AUCTION_HEADER_ID,
1965 PRICING_BASIS,
1966 NEGATIVE_COST_FACTOR_FLAG,
1967 AUCTION_CURRENCY_VALUE,
1968 BID_CURRENCY_VALUE,
1969 SEQUENCE_NUMBER,
1970 CREATION_DATE,
1971 CREATED_BY,
1972 LAST_UPDATE_DATE,
1973 LAST_UPDATED_BY,
1974 PF_TYPE,
1975 OLD_BID_CURRENCY_VALUE
1976 )
1977 (SELECT
1978 p_bid_number, -- BID_NUMBER
1979 apf.line_number, -- LINE_NUMBER
1980 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
1981 p_auc_header_id, -- AUCTION_HEADER_ID
1982 apf.pricing_basis, -- PRICING_BASIS
1983 apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
1984 decode(bl.has_bid_flag, 'Y',
1985 bpf.auction_currency_value, null), -- AUCTION_CURRENCY_VALUE
1986 decode(bl.has_bid_flag, 'Y',
1987 bpf.bid_currency_value, null), -- BID_CURRENCY_VALUE
1988 apf.sequence_number, -- SEQUENCE_NUMBER
1989 sysdate, -- CREATION_DATE
1990 p_userid, -- CREATED_BY
1991 sysdate, -- LAST_UPDATE_DATE
1992 p_userid, -- LAST_UPDATED_BY
1993 apf.pf_type, -- PF_TYPE
1994 decode(p_rebid_flag, 'Y', bpf.bid_currency_value, null) -- OLD_BID_CURRENCY_VALUE
1995 FROM pon_price_elements apf,
1996 pon_bid_price_elements bpf,
1997 pon_bid_item_prices bl
1998 WHERE apf.auction_header_id = p_auc_header_id
1999 AND apf.pf_type = 'SUPPLIER' -- only for supplier price factors
2000 AND bl.bid_number = p_bid_number
2001 AND bl.line_number = apf.line_number
2002 AND bl.display_price_factors_flag = 'Y' -- only for lines with price factors
2003 AND bpf.bid_number (+) = p_source_bid_num
2004 AND bpf.line_number (+) = apf.line_number
2005 AND bpf.price_element_type_id (+) = apf.price_element_type_id
2006 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2007 END IF;
2008
2009 -- copy over all buyer price factors that can be displayed to the supplier
2010 -- display flag checked and have nonzero value
2011 -- need to populate bid_currency_value, rounding as necessary
2012 IF (p_supp_seq_number IS NOT null AND l_pf_type <> 'SUPPLIER') THEN
2013
2014 INSERT INTO pon_bid_price_elements
2015 (
2016 BID_NUMBER,
2017 LINE_NUMBER,
2018 PRICE_ELEMENT_TYPE_ID,
2019 AUCTION_HEADER_ID,
2020 PRICING_BASIS,
2021 NEGATIVE_COST_FACTOR_FLAG,
2022 AUCTION_CURRENCY_VALUE,
2023 BID_CURRENCY_VALUE,
2024 SEQUENCE_NUMBER,
2025 CREATION_DATE,
2026 CREATED_BY,
2027 LAST_UPDATE_DATE,
2028 LAST_UPDATED_BY,
2029 PF_TYPE,
2030 OLD_BID_CURRENCY_VALUE
2031 )
2032 (SELECT
2033 p_bid_number, -- BID_NUMBER
2034 apf.line_number, -- LINE_NUMBER
2035 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
2036 p_auc_header_id, -- AUCTION_HEADER_ID
2037 apf.pricing_basis, -- PRICING_BASIS
2038 apf.NEGATIVE_COST_FACTOR_FLAG, --NEGATIVE_COST_FACTOR_FLAG
2039 pf.value, -- AUCTION_CURRENCY_VALUE
2040 decode(apf.pricing_basis,
2041 'PER_UNIT', round(pf.value * l_rate, l_price_prec),
2042 'FIXED_AMOUNT', round(pf.value * l_rate, l_curr_prec),
2043 'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
2044 apf.sequence_number, -- SEQUENCE_NUMBER
2045 sysdate, -- CREATION_DATE
2046 p_userid, -- CREATED_BY
2047 sysdate, -- LAST_UPDATE_DATE
2048 p_userid, -- LAST_UPDATED_BY
2049 apf.pf_type, -- PF_TYPE
2050 null -- OLD_BID_CURRENCY_VALUE
2051 FROM pon_price_elements apf,
2052 pon_pf_supplier_values pf,
2053 pon_bid_item_prices bl
2054 WHERE apf.auction_header_id = p_auc_header_id
2055 AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
2056 AND apf.display_to_suppliers_flag = 'Y'
2057 AND bl.bid_number = p_bid_number
2058 AND bl.line_number = apf.line_number
2059 AND bl.display_price_factors_flag = 'Y'
2060 AND pf.auction_header_id = apf.auction_header_id
2061 AND pf.line_number = apf.line_number
2062 AND pf.pf_seq_number = apf.sequence_number
2063 AND pf.supplier_seq_number = p_supp_seq_number
2064 AND nvl(pf.value, 0) <> 0
2065 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2066 END IF;
2067
2068 END insert_into_price_factors;
2069
2070 -- ======================================================================
2071 -- PROCEDURE: INSERT_INTO_PRICE_TIERS PRIVATE
2072 -- PARAMETERS:
2073 -- p_auc_header_id IN auction_header_id of negotiation
2074 -- p_bid_number IN bid number to insert price breaks for
2075 -- p_source_bid_num IN source_bid to default from
2076 -- p_userid IN userid of bid creator
2077 -- p_rebid_flag IN flag indicating rebid or not
2078 --
2079 -- COMMENT: inserts price tiers for the new bid, defualting as necessary
2080 -- ======================================================================
2081 PROCEDURE insert_into_price_tiers
2082 (
2083 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2084 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2085 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2086 p_userid IN pon_bid_headers.created_by%TYPE,
2087 p_rebid_flag IN VARCHAR2,
2088 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2089 p_batch_end IN pon_bid_item_prices.line_number%TYPE
2090 ) IS
2091 BEGIN
2092
2093 -- Get all auction side price breaks for modified lines that had a bid
2094 -- or for unmodified lines that had no bid
2095 INSERT INTO pon_bid_shipments
2096 (
2097 BID_NUMBER,
2098 LINE_NUMBER,
2099 SHIPMENT_NUMBER,
2100 AUCTION_HEADER_ID,
2101 AUCTION_LINE_NUMBER,
2102 AUCTION_SHIPMENT_NUMBER,
2103 SHIPMENT_TYPE,
2104 SHIP_TO_ORGANIZATION_ID,
2105 SHIP_TO_LOCATION_ID,
2106 QUANTITY,
2107 MAX_QUANTITY,
2108 PRICE_TYPE,
2109 PRICE,
2110 BID_CURRENCY_PRICE,
2111 PRICE_DISCOUNT,
2112 EFFECTIVE_START_DATE,
2113 EFFECTIVE_END_DATE,
2114 CREATION_DATE,
2115 CREATED_BY,
2116 LAST_UPDATE_DATE,
2117 LAST_UPDATED_BY,
2118 LAST_UPDATE_LOGIN,
2119 HAS_PRICE_DIFFERENTIALS_FLAG,
2120 UNIT_PRICE,
2121 BID_CURRENCY_UNIT_PRICE
2122 )
2123 (SELECT
2124 p_bid_number, -- BID_NUMBER
2125 apb.line_number, -- LINE_NUMBER
2126 apb.shipment_number+1, -- SHIPMENT_NUMBER
2127 p_auc_header_id, -- AUCTION_HEADER_ID
2128 apb.line_number, -- AUCTION_LINE_NUMBER
2129 apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
2130 apb.shipment_type, -- SHIPMENT_TYPE
2131 apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
2132 apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
2133 apb.quantity, -- QUANTITY
2134 apb.max_quantity, --- MAX_QUANTITY
2135 'PRICE', -- PRICE_TYPE
2136 apb.price, -- PRICE
2137 null, -- BID_CURRENCY_PRICE
2138 null, -- PRICE_DISCOUNT
2139 apb.effective_start_date, -- EFFECTIVE_START_DATE
2140 apb.effective_end_date, -- EFFECTIVE_END_DATE
2141 sysdate, -- CREATION_DATE
2142 p_userid, -- CREATED_BY
2143 sysdate, -- LAST_UPDATE_DATE
2144 p_userid, -- LAST_UPDATED_BY
2145 null, -- LAST_UPDATE_LOGIN
2146 apb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
2147 null, -- UNIT_PRICE
2148 null -- BID_CURRENCY_UNIT_PRICE
2149 FROM pon_auction_shipments_all apb,
2150 pon_bid_item_prices bl
2151 WHERE apb.auction_header_id = p_auc_header_id
2152 AND bl.bid_number = p_bid_number
2153 AND bl.line_number = apb.line_number
2154 -- we only insert those price breaks for which the line was modified
2155 -- or had no previosu bid on it - has_bid_flag = N in both cases
2156 AND bl.has_bid_flag = 'N'
2157 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2158
2159 -- Get only-bid-side price breaks for unmodified lines
2160 INSERT INTO pon_bid_shipments
2161 (
2162 BID_NUMBER,
2163 LINE_NUMBER,
2164 SHIPMENT_NUMBER,
2165 AUCTION_HEADER_ID,
2166 AUCTION_LINE_NUMBER,
2167 AUCTION_SHIPMENT_NUMBER,
2168 SHIPMENT_TYPE,
2169 SHIP_TO_ORGANIZATION_ID,
2170 SHIP_TO_LOCATION_ID,
2171 QUANTITY,
2172 MAX_QUANTITY,
2173 PRICE_TYPE,
2174 PRICE,
2175 BID_CURRENCY_PRICE,
2176 PRICE_DISCOUNT,
2177 EFFECTIVE_START_DATE,
2178 EFFECTIVE_END_DATE,
2179 CREATION_DATE,
2180 CREATED_BY,
2181 LAST_UPDATE_DATE,
2182 LAST_UPDATED_BY,
2183 LAST_UPDATE_LOGIN,
2184 HAS_PRICE_DIFFERENTIALS_FLAG,
2185 UNIT_PRICE,
2186 BID_CURRENCY_UNIT_PRICE,
2187 OLD_BID_CURRENCY_UNIT_PRICE,
2188 OLD_BID_CURRENCY_PRICE,
2189 OLD_PRICE_DISCOUNT,
2190 OLD_SHIP_TO_ORG_ID,
2191 OLD_SHIP_TO_LOC_ID,
2192 OLD_EFFECTIVE_START_DATE,
2193 OLD_EFFECTIVE_END_DATE,
2194 OLD_QUANTITY,
2195 OLD_MAX_QUANTITY,
2196 OLD_PRICE_TYPE
2197 )
2198 (SELECT
2199 p_bid_number, -- BID_NUMBER
2200 bpb.line_number, -- LINE_NUMBER
2201 bpb.shipment_number, -- SHIPMENT_NUMBER
2202 p_auc_header_id, -- AUCTION_HEADER_ID
2203 bpb.line_number, -- AUCTION_LINE_NUMBER
2204 bpb.auction_shipment_number,-- AUCTION_SHIPMENT_NUMBER
2205 bpb.shipment_type, -- SHIPMENT_TYPE
2206 bpb.ship_to_organization_id,-- SHIP_TO_ORGANIZATION_ID
2207 bpb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
2208 bpb.quantity, -- QUANTITY
2209 bpb.max_quantity, -- MAX_QUANTITY
2210 bpb.price_type, -- PRICE_TYPE
2211 bpb.price, -- PRICE
2212 bpb.bid_currency_price, -- BID_CURRENCY_PRICE
2213 bpb.price_discount, -- PRICE_DISCOUNT
2214 bpb.effective_start_date, -- EFFECTIVE_START_DATE
2215 bpb.effective_end_date, -- EFFECTIVE_END_DATE
2216 sysdate, -- CREATION_DATE
2217 p_userid, -- CREATED_BY
2218 sysdate, -- LAST_UPDATE_DATE
2219 p_userid, -- LAST_UPDATED_BY
2220 null, -- LAST_UPDATE_LOGIN
2221 bpb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
2222 bpb.unit_price, -- UNIT_PRICE
2223 bpb.bid_currency_unit_price,-- BID_CURRENCY_UNIT_PRICE
2224 decode(p_rebid_flag, 'Y', bpb.bid_currency_unit_price, null), -- OLD_BID_CURRENCY_UNIT_PRICE
2225 decode(p_rebid_flag, 'Y', bpb.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
2226 decode(p_rebid_flag, 'Y', bpb.price_discount, null), -- OLD_PRICE_DISCOUNT
2227 decode(p_rebid_flag, 'Y', bpb.ship_to_organization_id, null), -- OLD_SHIP_TO_ORG_ID
2228 decode(p_rebid_flag, 'Y', bpb.ship_to_location_id, null), -- OLD_SHIP_TO_LOC_ID
2229 decode(p_rebid_flag, 'Y', bpb.effective_start_date, null), -- OLD_EFFECTIVE_START_DATE
2230 decode(p_rebid_flag, 'Y', bpb.effective_end_date, null), -- OLD_EFFECTIVE_END_DATE
2231 decode(p_rebid_flag, 'Y', bpb.quantity, null), -- OLD_QUANTITY
2232 decode(p_rebid_flag, 'Y', bpb.max_quantity, null), -- OLD_MAX_QUANTITY
2233 decode(p_rebid_flag, 'Y', bpb.price_type, null) -- OLD_PRICE_TYPE
2234 FROM pon_bid_shipments bpb,
2235 pon_bid_item_prices bl
2236 WHERE bpb.bid_number = p_source_bid_num
2237 AND bl.bid_number = p_bid_number
2238 AND bl.line_number = bpb.line_number
2239 -- only unmodified lines with bids
2240 AND bl.has_bid_flag = 'Y'
2241 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2242
2243 END insert_into_price_tiers;
2244
2245 -- ======================================================================
2246 -- PROCEDURE: INSERT_INTO_PRICE_DIFF PRIVATE
2247 -- PARAMETERS:
2248 -- p_auc_header_id IN auction_header_id of negotiation
2249 -- p_bid_number IN bid number to insert price differentials for
2250 -- p_source_bid_num IN source_bid to default from
2251 -- p_userid IN userid of bid creator
2252 -- p_rebid_flag IN flag indicating rebid or not
2253 --
2254 -- COMMENT: inserts price differentials for the new bid, defualting as necessary
2255 -- ======================================================================
2256 PROCEDURE insert_into_price_diff
2257 (
2258 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2259 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2260 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2261 p_userid IN pon_bid_headers.created_by%TYPE,
2262 p_rebid_flag IN VARCHAR2,
2263 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2264 p_batch_end IN pon_bid_item_prices.line_number%TYPE
2265 ) IS
2266 BEGIN
2267
2268 INSERT INTO pon_bid_price_differentials
2269 (
2270 AUCTION_HEADER_ID,
2271 BID_NUMBER,
2272 LINE_NUMBER,
2273 SHIPMENT_NUMBER,
2274 PRICE_DIFFERENTIAL_NUMBER,
2275 PRICE_TYPE,
2276 MULTIPLIER,
2277 CREATION_DATE,
2278 CREATED_BY,
2279 LAST_UPDATE_DATE,
2280 LAST_UPDATED_BY,
2281 LAST_UPDATE_LOGIN,
2282 OLD_MULTIPLIER
2283 )
2284 (
2285 -- Insert all line level price differentials
2286 (SELECT
2287 p_auc_header_id, -- AUCTION_HEADER_ID
2288 p_bid_number, -- BID_NUMBER
2289 apd.line_number, -- LINE_NUMBER
2290 apd.shipment_number, -- SHIPMENT_NUMBER
2291 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
2292 apd.price_type, -- PRICE_TYPE
2293 decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
2294 sysdate, -- CREATION_DATE
2295 p_userid, -- CREATED_BY
2296 sysdate, -- LAST_UPDATE_DATE
2297 p_userid, -- LAST_UPDATED_BY
2298 null, -- LAST_UPDATE_LOGIN
2299 decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
2300 FROM pon_price_differentials apd,
2301 pon_bid_price_differentials bpd,
2302 pon_bid_item_prices bl
2303 WHERE apd.auction_header_id = p_auc_header_id
2304 AND apd.shipment_number = -1 -- only line level differentials
2305 AND bl.auction_header_id = apd.auction_header_id
2306 AND bl.bid_number = p_bid_number
2307 AND bl.line_number = apd.line_number
2308 AND bpd.bid_number (+) = p_source_bid_num
2309 AND bpd.line_number (+) = apd.line_number
2310 AND bpd.shipment_number (+) = apd.shipment_number
2311 AND bpd.price_differential_number (+) = apd.price_differential_number
2312 AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
2313
2314 UNION
2315
2316 -- Insert shipment level price differentials, but only for
2317 -- those shipments that got copied over
2318 (SELECT
2319 p_auc_header_id, -- AUCTION_HEADER_ID
2320 p_bid_number, -- BID_NUMBER
2321 apd.line_number, -- LINE_NUMBER
2322 apd.shipment_number+1, -- SHIPMENT_NUMBER
2323 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
2324 apd.price_type, -- PRICE_TYPE
2325 decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
2326 sysdate, -- CREATION_DATE
2327 p_userid, -- CREATED_BY
2328 sysdate, -- LAST_UPDATE_DATE
2329 p_userid, -- LAST_UPDATED_BY
2330 null, -- LAST_UPDATE_LOGIN
2331 decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
2332 FROM pon_price_differentials apd,
2333 pon_bid_price_differentials bpd,
2334 pon_bid_shipments bpb,
2335 pon_bid_item_prices bl
2336 WHERE apd.auction_header_id = p_auc_header_id
2337 AND apd.shipment_number <> -1 -- only shipment differentials
2338 AND bl.bid_number = p_bid_number
2339 AND bl.line_number = apd.line_number
2340 AND bpb.bid_number = p_bid_number
2341 AND bpb.line_number = apd.line_number
2342 AND bpb.shipment_type = 'PRICE BREAK'
2343 AND bpb.shipment_number = apd.shipment_number + 1
2344 AND bpd.bid_number (+) = p_source_bid_num
2345 AND bpd.line_number (+) = apd.line_number
2346 AND bpd.shipment_number (+) = apd.shipment_number + 1
2347 AND bpd.price_differential_number (+) = apd.price_differential_number
2348 AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
2349 );
2350
2351 END insert_into_price_diff;
2352
2353 -- ======================================================================
2354 -- PROCEDURE: insert_into_payments PRIVATE
2355 -- PARAMETERS:
2356 -- p_auc_header_id IN auction_header_id of negotiation
2357 -- p_bid_number IN bid number to insert price factors for
2358 -- p_source_bid_num IN source_bid to default from
2359 -- p_copy_only_from_auc, IN copy all the payments from negotiation only
2360 -- p_supplier_flag, IN Flag indication if supplier allowed to enter payments
2361 -- p_userid IN userid of bid creator
2362 -- p_rebid_flag IN flag indicating rebid or not
2363 -- p_new_round_or_amended IN flag indicating if defaulting result of amend or new round
2364 -- COMMENT: insert payments for the new bid, defualting as necessary
2365 -- ======================================================================
2366 -- Create and default payments
2367 PROCEDURE insert_into_payments
2368 (
2369 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2370 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2371 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2372 p_copy_only_from_auc IN VARCHAR2,
2373 p_supplier_flag IN pon_auction_headers_all.SUPPLIER_ENTERABLE_PYMT_FLAG%TYPE,
2374 p_userid IN pon_bid_headers.created_by%TYPE,
2375 p_rebid_flag IN VARCHAR2,
2376 p_new_round_or_amended IN VARCHAR2,
2377 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2378 p_batch_end IN pon_bid_item_prices.line_number%TYPE
2379 ) IS
2380
2381
2382
2383 CURSOR c_bid_pymnts_attach IS
2384 SELECT
2385 DISTINCT
2386 source_pay.bid_number source_bid_number,
2387 source_pay.bid_line_number source_bid_line_number,
2388 source_pay.bid_payment_id source_bid_payment_id,
2389 dest_pay.bid_number dest_bid_number,
2390 dest_pay.bid_line_number dest_bid_line_number,
2391 dest_pay.bid_payment_id dest_bid_payment_id
2392 FROM
2393 PON_BID_PAYMENTS_SHIPMENTS source_pay,
2394 FND_ATTACHED_DOCUMENTS fnd,
2395 PON_BID_PAYMENTS_SHIPMENTS dest_pay,
2396 PON_BID_ITEM_PRICES bl
2397 WHERE bl.auction_header_id = p_auc_header_id
2398 AND bl.bid_number = p_bid_number
2399 AND bl.has_bid_flag = 'Y'
2400 AND dest_pay.bid_number = bl.bid_number
2401 AND dest_pay.bid_line_number = bl.line_number
2402 AND source_pay.bid_number = p_source_bid_num
2403 AND dest_pay.bid_line_number = source_pay.bid_line_number
2404 AND dest_pay.payment_display_number = source_pay.payment_display_number
2405 AND fnd.pk1_value = source_pay.bid_number
2406 AND fnd.pk2_value = source_pay.bid_line_number
2407 AND fnd.pk3_value = source_pay.bid_payment_id
2408 AND fnd.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS'
2409 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
2410
2411 l_module CONSTANT VARCHAR2(35) := 'Insert_into_payments';
2412 BEGIN
2413 IF (g_debug_mode = 'Y') THEN
2414 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2415 FND_LOG.string(log_level => FND_LOG.level_statement,
2416 module => g_module_prefix || l_module,
2417 message => 'entering insert_into_payments' );
2418 END IF;
2419 END IF;
2420 IF (p_copy_only_from_auc = 'Y') THEN
2421 IF (g_debug_mode = 'Y') THEN
2422 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2423 FND_LOG.string(log_level => FND_LOG.level_statement,
2424 module => g_module_prefix || l_module,
2425 message => 'In if p_copy_only_from_auc is Y' );
2426 END IF;
2427 END IF;
2428 --copy all the payments from auction payments only as supplier_enterable flag toggled from Y in prev version to N in new round or amend
2429 INSERT INTO pon_bid_payments_shipments
2430 (
2431 BID_NUMBER,
2432 BID_LINE_NUMBER,
2433 BID_PAYMENT_ID,
2434 AUCTION_HEADER_ID,
2435 AUCTION_LINE_NUMBER,
2436 CREATION_DATE,
2437 CREATED_BY,
2438 LAST_UPDATE_DATE,
2439 LAST_UPDATED_BY,
2440 LAST_UPDATE_LOGIN,
2441 PAYMENT_DISPLAY_NUMBER,
2442 PAYMENT_DESCRIPTION,
2443 AUCTION_PAYMENT_ID,
2444 PAYMENT_TYPE_CODE,
2445 QUANTITY,
2446 UOM_CODE,
2447 PROMISED_DATE
2448 )
2449 (SELECT
2450 p_bid_number, --BID_NUMBER,
2451 bl.line_number, --BID_LINE_NUMBER,
2452 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2453 p_auc_header_id, --AUCTION_HEADER_ID,
2454 bl.line_number, --AUCTION_LINE_NUMBER,
2455 sysdate, --CREATION_DATE,
2456 p_userid, --CREATED_BY,
2457 sysdate, --LAST_UPDATE_DATE,
2458 p_userid, --LAST_UPDATED_BY,
2459 fnd_global.login_id, --LAST_UPDATE_LOGIN
2460 apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2461 apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2462 apmt.PAYMENT_ID, --AUCTION_PAYMENT_ID,
2463 apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2464 apmt.QUANTITY, --QUANTITY,
2465 apmt.UOM_CODE, --UOM_CODE,
2466 bl.promised_date --PROMISED_DATE,
2467 FROM pon_bid_item_prices bl,pon_auc_payments_shipments apmt
2468 WHERE bl.auction_header_id = p_auc_header_id
2469 AND bl.bid_number = p_bid_number
2470 AND bl.auction_header_id = apmt.auction_header_id
2471 AND bl.line_number = apmt.line_number
2472 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2473
2474
2475 ELSE --the following should execute if rebid/disqualified/new round or amend with supplier-enterable_flag Y/
2476 -- new round or amend with supplier flag N in this and previous version
2477
2478
2479 IF (g_debug_mode = 'Y') THEN
2480 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2481 FND_LOG.string(log_level => FND_LOG.level_statement,
2482 module => g_module_prefix || l_module,
2483 message => 'In else of p_copy_only_from_auc is Y' );
2484 END IF;
2485 END IF;
2486
2487 IF (g_debug_mode = 'Y') THEN
2488 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2489 FND_LOG.string(log_level => FND_LOG.level_statement,
2490 module => g_module_prefix || l_module,
2491 message => 'Insert for lines not bid' );
2492 END IF;
2493 END IF;
2494 --copy all the payments for the lines from auction payments for lines that have not been bid
2495 INSERT INTO pon_bid_payments_shipments
2496 (
2497 BID_NUMBER,
2498 BID_LINE_NUMBER,
2499 BID_PAYMENT_ID,
2500 AUCTION_HEADER_ID,
2501 AUCTION_LINE_NUMBER,
2502 CREATION_DATE,
2503 CREATED_BY,
2504 LAST_UPDATE_DATE,
2505 LAST_UPDATED_BY,
2506 LAST_UPDATE_LOGIN,
2507 PAYMENT_DISPLAY_NUMBER,
2508 PAYMENT_DESCRIPTION,
2509 AUCTION_PAYMENT_ID,
2510 PAYMENT_TYPE_CODE,
2511 QUANTITY,
2512 UOM_CODE,
2513 PROMISED_DATE
2514 )
2515 (SELECT
2516 p_bid_number, --BID_NUMBER,
2517 bl.line_number, --BID_LINE_NUMBER,
2518 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2519 p_auc_header_id, --AUCTION_HEADER_ID,
2520 bl.line_number, --AUCTION_LINE_NUMBER,
2521 sysdate, --CREATION_DATE,
2522 p_userid, --CREATED_BY,
2523 sysdate, --LAST_UPDATE_DATE,
2524 p_userid, --LAST_UPDATED_BY,
2525 fnd_global.login_id, --LAST_UPDATE_LOGIN
2526 apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2527 apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2528 decode(p_supplier_flag, 'N',apmt.PAYMENT_ID,null), --AUCTION_PAYMENT_ID,
2529 apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2530 apmt.QUANTITY, --QUANTITY,
2531 apmt.UOM_CODE, --UOM_CODE,
2532 bl.promised_date --PROMISED_DATE,
2533 FROM pon_bid_item_prices bl,
2534 pon_auc_payments_shipments apmt
2535 WHERE bl.auction_header_id = p_auc_header_id
2536 AND bl.bid_number = p_bid_number
2537 AND bl.auction_header_id = apmt.auction_header_id
2538 AND bl.line_number = apmt.line_number
2539 AND bl.has_bid_flag = 'N'
2540 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2541
2542 IF (g_debug_mode = 'Y') THEN
2543 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2544 FND_LOG.string(log_level => FND_LOG.level_statement,
2545 module => g_module_prefix || l_module,
2546 message => 'Insert for lines that were bid' );
2547 END IF;
2548 END IF;
2549 --copy all the payments for the lines from bid payments for lines that have been bid
2550 INSERT INTO pon_bid_payments_shipments
2551 (
2552 BID_NUMBER,
2553 BID_LINE_NUMBER,
2554 BID_PAYMENT_ID,
2555 AUCTION_HEADER_ID,
2556 AUCTION_LINE_NUMBER,
2557 CREATION_DATE,
2558 CREATED_BY,
2559 LAST_UPDATE_DATE,
2560 LAST_UPDATED_BY,
2561 LAST_UPDATE_LOGIN,
2562 PAYMENT_DISPLAY_NUMBER,
2563 PAYMENT_DESCRIPTION,
2564 AUCTION_PAYMENT_ID,
2565 PAYMENT_TYPE_CODE,
2566 QUANTITY,
2567 UOM_CODE,
2568 PRICE,
2569 BID_CURRENCY_PRICE,
2570 PROMISED_DATE,
2571 OLD_PAYMENT_DISPLAY_NUMBER,
2572 OLD_PAYMENT_TYPE_CODE,
2573 OLD_PAYMENT_DESCRIPTION,
2574 OLD_QUANTITY,
2575 OLD_UOM_CODE,
2576 OLD_BID_CURRENCY_PRICE,
2577 OLD_PROMISED_DATE
2578 )
2579 (SELECT
2580 p_bid_number, --BID_NUMBER,
2581 bl.line_number, --BID_LINE_NUMBER,
2582 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2583 p_auc_header_id, --AUCTION_HEADER_ID,
2584 bl.line_number, --AUCTION_LINE_NUMBER,
2585 sysdate, --CREATION_DATE,
2586 p_userid, --CREATED_BY,
2587 sysdate, --LAST_UPDATE_DATE,
2588 p_userid, --LAST_UPDATED_BY,
2589 fnd_global.login_id, --LAST_UPDATE_LOGIN
2590 bpmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2591 bpmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2592 decode(p_new_round_or_amended , 'Y' ,
2593 decode(p_supplier_flag ,'N',(select payment_id from pon_auc_payments_shipments
2594 where auction_header_id=p_auc_header_id
2595 AND line_number = bl.line_number
2596 AND payment_display_number= bpmt.PAYMENT_DISPLAY_NUMBER
2597 )
2598 , null
2599 ),bpmt.AUCTION_PAYMENT_ID
2600 ), --AUCTION_PAYMENT_ID,
2601 bpmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2602 bpmt.QUANTITY, --QUANTITY,
2603 bpmt.UOM_CODE, --UOM_CODE,
2604 bpmt.PRICE, --PRICE,
2605 bpmt.BID_CURRENCY_PRICE, --BID_CURRENCY_PRICE,
2606 bpmt.promised_date, --PROMISED_DATE,
2607 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DISPLAY_NUMBER,null), --OLD_PAYMENT_DISPLAY_NUMBER,
2608 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_TYPE_CODE,null), --OLD_PAYMENT_TYPE_CODE,
2609 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DESCRIPTION,null), --OLD_PAYMENT_DESCRIPTION,
2610 decode(p_rebid_flag, 'Y',bpmt.QUANTITY,null), --OLD_QUANTITY,
2611 decode(p_rebid_flag, 'Y',bpmt.UOM_CODE, null), --OLD_UOM_CODE,
2612 decode(p_rebid_flag, 'Y',bpmt.BID_CURRENCY_PRICE, null), --OLD_BID_CURRENCY_PRICE,
2613 decode(p_rebid_flag, 'Y',bpmt.PROMISED_DATE, null) --OLD_PROMISED_DATE
2614 FROM pon_bid_item_prices bl,
2615 pon_bid_payments_shipments bpmt
2616 WHERE bl.auction_header_id = p_auc_header_id
2617 AND bl.bid_number = p_bid_number
2618 AND bpmt.bid_number = p_source_bid_num
2619 AND bpmt.bid_line_number = bl.line_number
2620 AND bl.has_bid_flag = 'Y'
2621 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2622
2623 IF (g_debug_mode = 'Y') THEN
2624 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2625 FND_LOG.string(log_level => FND_LOG.level_statement,
2626 module => g_module_prefix || l_module,
2627 message => 'copy attachements from bid' );
2628 END IF;
2629 END IF;
2630 --copy the attachments for those payments which came from bid
2631 FOR payment_rec in c_bid_pymnts_attach LOOP
2632 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
2633 X_from_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
2634 X_from_pk1_value => to_char(payment_rec.source_bid_number),
2635 X_from_pk2_value => to_char(payment_rec.source_bid_line_number),
2636 X_from_pk3_value => to_char(payment_rec.source_bid_payment_id),
2637 X_to_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
2638 X_to_pk1_value => to_char(payment_rec.dest_bid_number),
2639 X_to_pk2_value => to_char(payment_rec.dest_bid_line_number),
2640 X_to_pk3_value => to_char(payment_rec.dest_bid_payment_id),
2641 X_created_by => p_userid,
2642 X_last_update_login => fnd_global.login_id);
2643 END LOOP;
2644 END IF; --p_copy_only_from_auc
2645 IF (g_debug_mode = 'Y') THEN
2646 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2647 FND_LOG.string(log_level => FND_LOG.level_statement,
2648 module => g_module_prefix || l_module,
2649 message => 'exiting insert_into_payments' );
2650 END IF;
2651 END IF;
2652 END insert_into_payments;
2653
2654 -- ======================================================================
2655 -- PROCEDURE: COPY_LINE_ATTACHMENTS PRIVATE
2656 -- PARAMETERS:
2657 -- p_auc_header_id IN auction_header_id of negotiation
2658 -- p_bid_number IN bid number to copy attachments to
2659 -- p_source_header_id IN auction_header_id of source bids negotiation
2660 -- p_source_bid_num IN source_bid to copy attachments from
2661 -- p_userid IN userid of bid creator
2662 --
2663 -- COMMENT: copies over line attachments from source bid
2664 -- ======================================================================
2665 PROCEDURE copy_line_attachments
2666 (
2667 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
2668 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2669 p_source_header_id IN pon_bid_headers.auction_header_id%TYPE,
2670 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2671 p_userid IN pon_bid_headers.created_by%TYPE,
2672 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2673 p_batch_end IN pon_bid_item_prices.line_number%TYPE,
2674 p_to_category_id IN NUMBER,
2675 p_change_categ_id IN VARCHAR2
2676 ) IS
2677
2678 CURSOR bid_lines_with_attachments IS
2679 SELECT DISTINCT ad.pk3_value
2680 FROM fnd_attached_documents ad, pon_bid_item_prices bl
2681 WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
2682 AND ad.pk1_value = p_source_header_id
2683 AND ad.pk2_value = p_source_bid_num
2684 AND ad.pk3_value IS NOT null
2685 AND bl.bid_number = p_bid_number
2686 AND bl.line_number = to_number(ad.pk3_value)
2687 AND bl.has_bid_flag = 'Y'
2688 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
2689
2690 BEGIN
2691 IF p_change_categ_id = 'Y' then
2692 -- Copy the line's attachments only for unmodified lines, with new target category.
2693 FOR line IN bid_lines_with_attachments LOOP
2694 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2695 (x_from_entity_name => 'PON_BID_ITEM_PRICES',
2696 x_from_pk1_value => p_source_header_id,
2697 x_from_pk2_value => p_source_bid_num,
2698 x_from_pk3_value => line.pk3_value,
2699 x_to_entity_name => 'PON_BID_ITEM_PRICES',
2700 x_to_pk1_value => p_auc_header_id,
2701 x_to_pk2_value => p_bid_number,
2702 x_to_pk3_value => line.pk3_value,
2703 x_created_by => p_userid,
2704 x_last_update_login => fnd_global.login_id,
2705 x_to_category_id => p_to_category_id);
2706 END LOOP;
2707 ELSE
2708 -- Copy the line's attachments only for unmodified lines
2709 FOR line IN bid_lines_with_attachments LOOP
2710 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2711 (x_from_entity_name => 'PON_BID_ITEM_PRICES',
2712 x_from_pk1_value => p_source_header_id,
2713 x_from_pk2_value => p_source_bid_num,
2714 x_from_pk3_value => line.pk3_value,
2715 x_to_entity_name => 'PON_BID_ITEM_PRICES',
2716 x_to_pk1_value => p_auc_header_id,
2717 x_to_pk2_value => p_bid_number,
2718 x_to_pk3_value => line.pk3_value,
2719 x_created_by => p_userid,
2720 x_last_update_login => fnd_global.login_id);
2721 END LOOP;
2722 END IF; -- }
2723
2724 END copy_line_attachments;
2725
2726 -- ======================================================================
2727 -- PROCEDURE: POPULATE_HAS_BID_FLAG PRIVATE
2728 -- PARAMETERS:
2729 -- p_auc_header_id IN auction_header_id of negotiation
2730 -- p_bid_number IN bid number to populate has_bid_flag for
2731 --
2732 -- COMMENT: populates has_bid_flag - used when defualting from pre-release 12 draft
2733 -- ======================================================================
2734 PROCEDURE populate_has_bid_flag
2735 (
2736 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2737 p_bid_number IN pon_bid_headers.bid_number%TYPE
2738 ) IS
2739 l_full_qty_reqd VARCHAR2(1);
2740 BEGIN
2741
2742 -- Determine if the auction is full quantity required
2743 SELECT decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N')
2744 INTO l_full_qty_reqd
2745 FROM pon_auction_headers_all ah
2746 WHERE ah.auction_header_id = p_auc_header_id;
2747
2748 -- First set has_bid_flag to N
2749 UPDATE pon_bid_item_prices bl
2750 SET bl.has_bid_flag = 'N'
2751 WHERE bl.bid_number = p_bid_number;
2752
2753 -- Then, determine which lines have a bid
2754 UPDATE pon_bid_item_prices bl
2755 SET bl.has_bid_flag = 'Y'
2756 WHERE bl.bid_number = p_bid_number
2757 AND ((bl.promised_date IS NOT null
2758 OR bl.price IS NOT null
2759 OR bl.proxy_bid_limit_price IS NOT null
2760 OR bl.po_bid_min_rel_amount IS NOT null
2761 OR bl.note_to_auction_owner IS NOT null)
2762
2763 OR EXISTS
2764
2765 -- Check auction side to check quantity
2766 (SELECT al.line_number
2767 FROM pon_auction_item_prices_all al
2768 WHERE al.auction_header_id = bl.auction_header_id
2769 AND al.line_number = bl.line_number
2770 AND (l_full_qty_reqd <> 'Y'
2771 AND al.order_type_lookup_code <> 'AMOUNT'
2772 AND al.group_type <> 'LOT_LINE'
2773 AND bl.quantity IS NOT null))
2774
2775 OR EXISTS
2776
2777 -- Check attributes
2778 (SELECT ba.line_number
2779 FROM pon_bid_attribute_values ba
2780 WHERE ba.bid_number = bl.bid_number
2781 AND ba.line_number = bl.line_number
2782 AND ba.value IS NOT null
2783 AND rownum = 1)
2784
2785 OR EXISTS
2786
2787 -- Check price factors
2788 (SELECT bpf.line_number
2789 FROM pon_bid_price_elements bpf
2790 WHERE bpf.bid_number = bl.bid_number
2791 AND bpf.line_number = bl.line_number
2792 AND bpf.pf_type = 'SUPPLIER'
2793 AND bpf.bid_currency_value IS NOT null
2794 AND rownum = 1)
2795
2796 OR EXISTS
2797
2798 -- Check shipments
2799 (SELECT bs.line_number
2800 FROM pon_bid_shipments bs
2801 WHERE bs.bid_number = bl.bid_number
2802 AND bs.line_number = bl.line_number
2803 AND (bs.auction_shipment_number IS null
2804 OR bs.price_type = 'PRICE' AND bs.bid_currency_unit_price IS NOT null
2805 OR bs.price_type = 'PRICE DISCOUNT' AND bs.price_discount IS NOT null
2806 OR bs.bid_currency_price IS NOT null)
2807 AND rownum = 1)
2808
2809 OR EXISTS
2810
2811 -- Check price differentials, including shipment price differentials
2812 (SELECT bpd.line_number
2813 FROM pon_bid_price_differentials bpd
2814 WHERE bpd.bid_number = bl.bid_number
2815 AND bpd.line_number = bl.line_number
2816 AND bpd.multiplier IS NOT null
2817 AND rownum = 1)
2818 );
2819
2820 END populate_has_bid_flag;
2821
2822 -- ======================================================================
2823 -- PROCEDURE: CREATE_NEW_DRAFT PRIVATE
2824 -- PARAMETERS:
2825 -- p_auc_header_id IN auction_header_id of negotiation
2826 -- p_source_bid_num IN source_bid to default from
2827 -- p_tpid IN trading partner id of supplier
2828 -- p_tpcid IN trading partner contact id of supplier
2829 -- p_tpname IN trading partner name of supplier
2830 -- p_tpcname IN trading partner contact name of supplier
2831 -- p_userid IN userid of bid creator
2832 -- p_venid IN vendor id
2833 -- p_vensid IN vendor site id to place bid for
2834 -- p_venscode IN vendor site code to place bid for
2835 -- p_auctpid IN buyers trading partner id
2836 -- p_auctcpid IN buyers trading partner contact id
2837 -- p_buyer_user IN flag indicating surrogate bid or not
2838 -- p_evaluator_id IN evaluator user id
2839 -- p_eval_flag IN flag indicating if the response is an evaluation
2840 -- p_new_round_or_amended IN flag indicating, if bid defualt is happening for new round or amend
2841 -- p_rebid_flag IN flag indicating rebid or not
2842 -- x_bid_number OUT the bid number of the created bid
2843 --
2844 -- COMMENT: creates a new draft bid for the specified supplier/site
2845 -- combination on the specified site. Inserts values into all bid
2846 -- side tables and copies attachments
2847 -- ======================================================================
2848 PROCEDURE create_new_draft_bid
2849 (
2850 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2851 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2852 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
2853 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
2854 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
2855 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
2856 p_userid IN pon_bid_headers.created_by%TYPE,
2857 p_venid IN pon_bid_headers.vendor_id%TYPE,
2858 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
2859 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
2860 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
2861 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
2862 p_buyer_user IN VARCHAR2,
2863 ----------- Supplier Management: Supplier Evaluation -----------
2864 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
2865 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
2866 ----------------------------------------------------------------
2867 p_new_round_or_amended IN VARCHAR2,
2868 p_rebid_flag IN VARCHAR2,
2869 p_prev_bid_disq IN VARCHAR2,
2870 p_prev_bid_wthd IN VARCHAR2,
2871 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
2872 x_return_status OUT NOCOPY NUMBER,
2873 x_return_code OUT NOCOPY VARCHAR2
2874 ) IS
2875 l_source_header_id pon_auction_headers_all.auction_header_id%TYPE;
2876 l_restricted_flag VARCHAR2(1);
2877 l_rel12_draft VARCHAR2(1);
2878 l_source_bid_status pon_bid_headers.bid_status%TYPE;
2879 l_supp_seq_number pon_bidding_parties.sequence%TYPE;
2880
2881 l_max_line_number pon_bid_item_prices.line_number%TYPE;
2882 l_batch_start pon_bid_item_prices.line_number%TYPE;
2883 l_batch_end pon_bid_item_prices.line_number%TYPE;
2884 l_skip_pf_for_batch VARCHAR2(1);
2885 l_supplier_flag pon_auction_headers_all.SUPPLIER_ENTERABLE_PYMT_FLAG%TYPE;
2886 l_payment_type pon_auction_headers_all.PROGRESS_PAYMENT_TYPE%TYPE;
2887 l_contract_type pon_auction_headers_all.CONTRACT_TYPE%TYPE;
2888 l_module CONSTANT VARCHAR2(35) := 'create_new_draft_bid';
2889 l_other_draft_bid_number pon_bid_headers.bid_number%TYPE;
2890 l_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
2891 l_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%TYPE;
2892
2893
2894 -- Two-part RFQ related variables
2895 l_is_new_round VARCHAR2(1) := 'N'; -- to store Y for new rounds (default N)
2896 l_to_category_id NUMBER; -- to store destination category id
2897 l_prev_two_part VARCHAR2(1); -- Y if previous round was two-part, else N or null
2898 l_curr_two_part VARCHAR2(1); -- Y if current round is two-part, else N or null
2899 l_categ_id_supp NUMBER; -- to store category id of "FromSupplier"
2900 l_categ_id_supp_tech NUMBER; -- to store category id of "FromSupplierTechnical"
2901 l_change_categ_id VARCHAR2(1) := 'N'; -- change category id ?
2902
2903 -- Variables for bug 12607365 (FP of 11665610)
2904 l_display_pf_flag pon_bid_item_prices.DISPLAY_PRICE_FACTORS_FLAG%TYPE;
2905 l_bid_quantity pon_bid_item_prices.quantity%TYPE;
2906 l_bid_curr_unit_price pon_bid_item_prices.bid_currency_unit_price%TYPE;
2907 l_line_number pon_bid_item_prices.line_number%TYPE;
2908
2909 l_order_type_lookup_type pon_auction_item_prices_all.order_type_lookup_code%TYPE;
2910 l_auc_quantity pon_auction_item_prices_all.quantity%TYPE;
2911
2912 l_rate PON_BID_HEADERS.RATE%TYPE;
2913 l_tp_id PON_BID_HEADERS.TRADING_PARTNER_ID%TYPE;
2914 l_vendor_site_id PON_BID_HEADERS.VENDOR_SITE_ID%TYPE;
2915 l_precision PON_BID_HEADERS.NUMBER_PRICE_DECIMALS%TYPE;
2916
2917 l_full_quan_req VARCHAR(1); -- Y is the pon_auction_headers_all.full_quantity_bid_code has code FULL_QTY_BIDS_REQD
2918 l_supplier_view PON_AUCTION_HEADERS_ALL.supplier_view_type%TYPE;
2919
2920 l_unit pon_pf_supplier_formula.unit_price%TYPE;
2921 l_amount pon_pf_supplier_formula.fixed_amount%TYPE;
2922 l_percentage pon_pf_supplier_formula.percentage%TYPE;
2923
2924 l_pf_value NUMBER;
2925 l_pf_unit_price NUMBER;
2926 l_total_price NUMBER; --double in java
2927 l_unit_pf_amt NUMBER; --double in java
2928 l_transformed_price NUMBER;
2929 l_truncated_value NUMBER;
2930 l_trans_price NUMBER;
2931 l_bid_curr_trans_price NUMBER;
2932 l_price NUMBER;
2933
2934 CURSOR bid_values IS
2935 SELECT pbip.DISPLAY_PRICE_FACTORS_FLAG, pbip.quantity AS bid_quantity, pbip.bid_currency_unit_price, pbip.line_number, paip.order_type_lookup_code, paip.quantity AS auc_quantity
2936 FROM PON_BID_ITEM_PRICES pbip, pon_auction_item_prices_all paip
2937 WHERE paip.auction_header_id = p_auc_header_id
2938 AND pbip.bid_number = x_bid_number
2939 AND pbip.line_number(+) = paip.line_number;
2940
2941 CURSOR supplier_pf_curr(v_line_number pon_bid_item_prices.line_number%TYPE) IS
2942 SELECT PRICING_BASIS, BID_CURRENCY_VALUE, PRICE_ELEMENT_TYPE_ID
2943 FROM PON_BID_PRICE_ELEMENTS
2944 WHERE AUCTION_HEADER_ID = p_auc_header_id
2945 AND BID_NUMBER = x_bid_number
2946 AND LINE_NUMBER = v_line_number
2947 AND PF_TYPE <> 'BUYER';
2948
2949 /* <UDA Project> */
2950 l_doctype_id pon_auction_headers_all.DOCTYPE_ID%TYPE;
2951 l_msg_count NUMBER;
2952 l_msg_data VARCHAR2(32767);
2953 l_uda_template_id pon_bid_item_prices.UDA_TEMPLATE_ID%TYPE;
2954 l_is_uda_enabled NUMBER;
2955 l_err_msg VARCHAR2(100);
2956 l_return_status VARCHAR2(1);
2957 /* <End UDA Project> */
2958 l_surrogate_bid_flag VARCHAR2(1);
2959
2960 l_allow_unsol_lines VARCHAR2(1); --Unsolicited Lines Project
2961 l_can_copy_unsol_lines VARCHAR2(1); --Unsolicited Lines Project
2962
2963 BEGIN
2964
2965 BEGIN
2966 -- Check if the supplier has restricted lines, and get sequence number
2967 SELECT decode(bp.access_type, 'RESTRICTED', 'Y', 'N'), bp.sequence
2968 INTO l_restricted_flag, l_supp_seq_number
2969 FROM pon_bidding_parties bp
2970 WHERE bp.auction_header_id = p_auc_header_id
2971 AND bp.trading_partner_id = p_tpid
2972 AND nvl(bp.vendor_site_id, -1) = p_vensid;
2973 EXCEPTION
2974 WHEN NO_DATA_FOUND THEN
2975 l_restricted_flag := 'N';
2976 l_supp_seq_number := null;
2977 END;
2978
2979 -- Create and default bid header and get the bid number
2980 insert_into_bid_header
2981 (p_auc_header_id,
2982 p_source_bid_num,
2983 p_tpid,
2984 p_tpname,
2985 p_tpcid,
2986 p_tpcname,
2987 p_userid,
2988 p_venid,
2989 p_vensid,
2990 p_venscode,
2991 p_auctpid,
2992 p_auctpcid,
2993 p_buyer_user,
2994 ---- Supplier Management: Supplier Evaluation ----
2995 p_evaluator_id,
2996 p_eval_flag,
2997 --------------------------------------------------
2998 p_rebid_flag,
2999 p_prev_bid_disq,
3000 p_prev_bid_wthd,
3001 x_bid_number);
3002
3003 -- Create header attributes
3004 insert_into_header_attributes
3005 (p_auc_header_id,
3006 x_bid_number,
3007 p_source_bid_num,
3008 p_userid,
3009 ---- Supplier Management: Supplier Evaluation ----
3010 p_evaluator_id,
3011 p_eval_flag,
3012 --------------------------------------------------
3013 p_rebid_flag);
3014
3015 -- Copy over header attachments
3016 IF (p_source_bid_num IS NOT null) THEN
3017
3018 SELECT bh.auction_header_id
3019 INTO l_source_header_id
3020 FROM pon_bid_headers bh
3021 WHERE bh.bid_number = p_source_bid_num;
3022
3023 -- get value of two-part flags for current and prev rounds
3024 select decode(pah.auction_header_id_prev_round, null, 'N', 'Y'),
3025 nvl(pah.two_part_flag, 'N'),
3026 (select nvl(two_part_flag,'N') from pon_auction_headers_all
3027 where auction_header_id = pah.auction_header_id_prev_round)
3028 into l_is_new_round, l_curr_two_part, l_prev_two_part
3029 from pon_auction_headers_all pah
3030 where auction_header_id = p_auc_header_id;
3031
3032 log_message(l_module, 'Two-Part related variables: l_is_new_round: ' || l_is_new_round || '; l_curr_two_part: '||l_curr_two_part||'; l_prev_two_part: ' || l_prev_two_part);
3033
3034 -- if it is a new round, and two-part flag has changed...
3035 if (l_is_new_round = 'Y' AND (l_curr_two_part <> l_prev_two_part)) THEN -- {
3036 -- target category id needs to be changed
3037 -- fetch target category ids.
3038 select (select category_id from fnd_document_categories
3039 where name = pon_auction_pkg.g_supplier_attachment),
3040 (select category_id from fnd_document_categories
3041 where name = pon_auction_pkg.g_technical_attachment)
3042 into l_categ_id_supp, l_categ_id_supp_tech
3043 from fnd_document_categories
3044 where ROWNUM = 1;
3045
3046 -- if prev round was two part, copy attachments to "FromSupplier"
3047 if (l_prev_two_part = 'Y') THEN -- {
3048 l_to_category_id := l_categ_id_supp;
3049 l_change_categ_id := 'Y';
3050 -- else copy to "FromSupplierTechnical"
3051 else
3052 l_to_category_id := l_categ_id_supp_tech;
3053 l_change_categ_id := 'Y';
3054 end if; -- }
3055
3056 log_message(l_module, 'Two-Part related variables: l_to_category_id: ' || l_to_category_id || '; l_categ_id_supp_tech: '||l_categ_id_supp_tech||'; l_categ_id_supp: ' || l_categ_id_supp);
3057
3058 -- Copy header attachments to new target category
3059 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
3060 (x_from_entity_name => 'PON_BID_HEADERS',
3061 x_from_pk1_value => l_source_header_id,
3062 x_from_pk2_value => p_source_bid_num,
3063 x_to_entity_name => 'PON_BID_HEADERS',
3064 x_to_pk1_value => p_auc_header_id,
3065 x_to_pk2_value => x_bid_number,
3066 x_created_by => p_userid,
3067 x_last_update_login => fnd_global.login_id,
3068 x_to_category_id => l_to_category_id);
3069
3070 ELSE
3071 -- Copy header level attachments without changing categories
3072 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
3073 (x_from_entity_name => 'PON_BID_HEADERS',
3074 x_from_pk1_value => l_source_header_id,
3075 x_from_pk2_value => p_source_bid_num,
3076 x_to_entity_name => 'PON_BID_HEADERS',
3077 x_to_pk1_value => p_auc_header_id,
3078 x_to_pk2_value => x_bid_number,
3079 x_created_by => p_userid,
3080 x_last_update_login => fnd_global.login_id);
3081 END IF; -- }
3082 END IF;
3083
3084 --get the one time values needed for complex work here to avoid reexcution of
3085 --query with every batch
3086 g_copy_only_from_auc := 'N';
3087 IF (g_debug_mode = 'Y') THEN
3088 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3089 FND_LOG.string(log_level => FND_LOG.level_statement,
3090 module => g_module_prefix || l_module,
3091 message => 'initializing negotiation values for complex work');
3092 END IF;
3093 END IF;
3094 select nvl(ah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'N'), nvl(progress_payment_type,'NONE'), contract_type,
3095 nvl(ADVANCE_NEGOTIABLE_FLAG,'N'),nvl(RECOUPMENT_NEGOTIABLE_FLAG,'N'),nvl(PROGRESS_PYMT_NEGOTIABLE_FLAG,'N'),
3096 nvl(MAX_RETAINAGE_NEGOTIABLE_FLAG,'N'),nvl(RETAINAGE_NEGOTIABLE_FLAG,'N')
3097 into l_supplier_flag, l_payment_type, l_contract_type,
3098 g_advance_negotiable,g_recoupment_negotiable,g_prog_pymt_negotiable,g_max_rtng_negotiable,g_rtng_negotiable
3099 FROM pon_auction_headers_all ah where ah.auction_header_id=p_auc_header_id;
3100 IF (g_debug_mode = 'Y') THEN
3101 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3102 FND_LOG.string(log_level => FND_LOG.level_statement,
3103 module => g_module_prefix || l_module,
3104 message => 'initializing currency stuff for complex work');
3105 END IF;
3106 END IF;
3107 -- Get bid currency precisions and rate and surrogate bid flag
3108 SELECT cu.precision,
3109 bh.rate,
3110 bh.surrog_bid_flag
3111 INTO g_curr_prec,
3112 g_bid_rate,
3113 l_surrog_bid_flag
3114 FROM pon_bid_headers bh,
3115 fnd_currencies cu
3116 WHERE bh.bid_number = x_bid_number
3117 AND cu.currency_code = bh.bid_currency_code;
3118
3119 --do the following only if complex work neg
3120 IF (p_new_round_or_amended = 'Y' and l_supplier_flag = 'N'
3121 AND l_payment_type <> 'NONE' AND l_contract_type = 'STANDARD') THEN
3122 IF (g_debug_mode = 'Y') THEN
3123 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3124 FND_LOG.string(log_level => FND_LOG.level_statement,
3125 module => g_module_prefix || l_module,
3126 message => 'within if for complex work');
3127 END IF;
3128 END IF;
3129 -- check from where should a payment defualt if supplier flag was
3130 --toggled in new round or amend
3131 -- Here default payments from previous bid if SUPPLIER_ENTERABLE_PYMT_FLAG for
3132 -- new negotiation is Y. but if SUPPLIER_ENTERABLE_PYMT_FLAG is N
3133 -- then if previous neg had SUPPLIER_ENTERABLE_PYMT_FLAG as Y then
3134 -- we need to default payments from neg and not from bid.
3135 select decode(oldah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'Y','Y', 'N')
3136 into g_copy_only_from_auc
3137 FROM pon_auction_headers_all oldah
3138 WHERE oldah.auction_header_id = l_source_header_id;
3139
3140 IF (g_debug_mode = 'Y') THEN
3141 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3142 FND_LOG.string(log_level => FND_LOG.level_statement,
3143 module => g_module_prefix || l_module,
3144 message => 'after selecting supplier flag copy only from auc value is' || g_copy_only_from_auc );
3145 END IF;
3146 END IF;
3147
3148 END IF; --p_new_round... ontract_type STANDARD and payment_type <> NONE
3149 IF (g_debug_mode = 'Y') THEN
3150 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3151 FND_LOG.string(log_level => FND_LOG.level_statement,
3152 module => g_module_prefix || l_module,
3153 message => 'Finished initializing negotiation values for complex work' );
3154 END IF;
3155 END IF;
3156 --end values needed for complex work
3157
3158 /* <UDA Project> If UDA is enabled Copy UDA's */
3159 BEGIN
3160 select doctype_id
3161 into l_doctype_id
3162 from pon_auction_headers_all
3163 where auction_header_id = p_auc_header_id;
3164
3165 l_is_uda_enabled := PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id);
3166 IF l_is_uda_enabled = 1 THEN
3167 l_uda_template_id := po_uda_data_util.get_template_id(
3168 p_functional_area => 'SOURCING',
3169 p_document_type => 'OFFER',
3170 p_document_style_id => NULL,
3171 p_document_level => 'LINE',
3172 p_input_date => sysdate,
3173 x_return_status => l_return_status,
3174 x_err_msg => l_err_msg);
3175 END IF;
3176 END;
3177 /* End UDA Project */
3178
3179 -- START BATCHING
3180
3181 -- Determine the maximum line number for the negotiation
3182 SELECT ah.max_internal_line_num ,ah.price_tiers_indicator, Nvl(ALLOW_UNSOL_OFFER_LINES,'N') --Unsolicited Lines Project
3183 INTO l_max_line_number,l_price_tiers_indicator,l_allow_unsol_lines --Unsolicited Lines Project
3184 FROM pon_auction_headers_all ah
3185 WHERE ah.auction_header_id = p_auc_header_id;
3186
3187 -- Define the initial range (line numbers are indexed from 1)
3188 l_batch_start := 1;
3189 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
3190 l_batch_end := l_max_line_number;
3191 ELSE
3192 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3193 END IF;
3194
3195 WHILE (l_batch_start <= l_max_line_number) LOOP
3196
3197 -- Create and default bid lines
3198 insert_into_bid_items
3199 (p_auc_header_id,
3200 x_bid_number,
3201 p_source_bid_num,
3202 p_tpid,
3203 p_tpcid,
3204 p_userid,
3205 p_vensid,
3206 p_rebid_flag,
3207 l_restricted_flag,
3208 l_batch_start,
3209 l_batch_end,
3210 l_surrog_bid_flag);
3211
3212 -- Copy over line attachments
3213 IF (p_source_bid_num IS NOT null) THEN
3214
3215 copy_line_attachments
3216 (p_auc_header_id,
3217 x_bid_number,
3218 l_source_header_id,
3219 p_source_bid_num,
3220 p_userid,
3221 l_batch_start,
3222 l_batch_end,
3223 l_to_category_id,
3224 l_change_categ_id);
3225 END IF;
3226
3227 -- Create and default header and line attributes
3228 insert_into_line_attributes
3229 (p_auc_header_id,
3230 x_bid_number,
3231 p_source_bid_num,
3232 p_userid,
3233 p_rebid_flag,
3234 l_batch_start,
3235 l_batch_end);
3236
3237 -- Populate display_price_factors_flag
3238 populate_display_pf_flag
3239 (p_auc_header_id,
3240 x_bid_number,
3241 l_supp_seq_number,
3242 l_batch_start,
3243 l_batch_end,
3244 l_skip_pf_for_batch);
3245
3246 IF (l_skip_pf_for_batch = 'N') THEN
3247
3248 -- Create and defualt price factors
3249 insert_into_price_factors
3250 (p_auc_header_id,
3251 x_bid_number,
3252 p_source_bid_num,
3253 p_userid,
3254 l_supp_seq_number,
3255 p_rebid_flag,
3256 l_batch_start,
3257 l_batch_end);
3258 END IF;
3259
3260 --Create and default price tiers only if the price tiers indicator
3261 -- is non null and not NONE
3262 IF (l_price_tiers_indicator is NOT NULL AND
3263 l_price_tiers_indicator <> 'NONE') THEN
3264 insert_into_price_tiers
3265 (p_auc_header_id,
3266 x_bid_number,
3267 p_source_bid_num,
3268 p_userid,
3269 p_rebid_flag,
3270 l_batch_start,
3271 l_batch_end);
3272 END IF;
3273
3274 -- Create and default price differentials
3275 insert_into_price_diff
3276 (p_auc_header_id,
3277 x_bid_number,
3278 p_source_bid_num,
3279 p_userid,
3280 p_rebid_flag,
3281 l_batch_start,
3282 l_batch_end);
3283
3284 /* UDA Project : Copy UDA's if uda's are enabled */
3285 IF(l_is_uda_enabled = 1) then
3286
3287 -- Call method to copy uda's
3288 PON_COPY_UDAS_GRP.FETCH_BID_LINES_COPY_UDA(
3289 p_auction_header_id => p_auc_header_id,
3290 p_bid_number => x_bid_number,
3291 p_source_bid_num => p_source_bid_num,
3292 p_bid_template_id => l_uda_template_id,
3293 p_batch_start => l_batch_start,
3294 p_batch_end => l_batch_end,
3295 x_return_status => l_return_status,
3296 x_msg_count => l_msg_count,
3297 x_msg_data => l_msg_data );
3298 END IF;
3299 /* End UDA Project */
3300
3301 --complex work
3302 IF (l_payment_type <> 'NONE' AND l_contract_type = 'STANDARD' ) THEN
3303
3304 IF (g_debug_mode = 'Y') THEN
3305 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3306 FND_LOG.string(log_level => FND_LOG.level_statement,
3307 module => g_module_prefix || l_module,
3308 message => 'before calling insert_into_payments' );
3309 END IF;
3310 END IF;
3311 -- Create and default payments
3312 insert_into_payments
3313 (p_auc_header_id,
3314 x_bid_number,
3315 p_source_bid_num,
3316 g_copy_only_from_auc,
3317 l_supplier_flag,
3318 p_userid,
3319 p_rebid_flag,
3320 p_new_round_or_amended,
3321 l_batch_start,
3322 l_batch_end);
3323 IF (g_debug_mode = 'Y') THEN
3324 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3325 FND_LOG.string(log_level => FND_LOG.level_statement,
3326 module => g_module_prefix || l_module,
3327 message => 'after calling insert_into_payments' );
3328 END IF;
3329 END IF;
3330 END IF; --contract_type STANDARD and payment_type <> NONE
3331
3332 --Code for cost factors change during bid copy - bug 12607365 (FP of 11665610)
3333 --Need this logic only if the current bid is copied from previous bid
3334 IF(p_source_bid_num IS NOT NULL) THEN
3335
3336 SELECT RATE, TRADING_PARTNER_ID, VENDOR_SITE_ID, NUMBER_PRICE_DECIMALS
3337 INTO l_rate, l_tp_id, l_vendor_site_id, l_precision
3338 FROM PON_BID_HEADERS
3339 WHERE BID_NUMBER = x_bid_number
3340 AND AUCTION_HEADER_ID = p_auc_header_id
3341 AND BID_STATUS = 'DRAFT';
3342
3343 SELECT decode(full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'), supplier_view_type
3344 INTO l_full_quan_req, l_supplier_view
3345 FROM PON_AUCTION_HEADERS_ALL
3346 WHERE AUCTION_HEADER_ID = p_auc_header_id;
3347
3348 --Loop through the bid lines and re calculate the price based on current price factors
3349 FOR bid_values_rec IN bid_values
3350 LOOP
3351 l_display_pf_flag := bid_values_rec.DISPLAY_PRICE_FACTORS_FLAG;
3352 l_bid_quantity := bid_values_rec.bid_quantity;
3353 l_bid_curr_unit_price := bid_values_rec.bid_currency_unit_price;
3354 l_line_number := bid_values_rec.line_number;
3355 l_order_type_lookup_type := bid_values_rec.order_type_lookup_code;
3356 l_auc_quantity := bid_values_rec.auc_quantity;
3357
3358 BEGIN
3359 SELECT unit_price, fixed_amount, percentage
3360 INTO l_unit, l_amount, l_percentage
3361 FROM pon_pf_supplier_formula
3362 WHERE auction_header_id = p_auc_header_id
3363 AND line_number = l_line_number
3364 AND (trading_partner_id = l_tp_id AND
3365 vendor_site_id = l_vendor_site_id);
3366
3367 EXCEPTION
3368 WHEN OTHERS THEN
3369 l_unit := 0;
3370 l_amount := 0;
3371 l_percentage := 1;
3372 END;
3373
3374 --Calcualte considering supplier and buyer price factors to display the transformed price
3375 IF(l_display_pf_flag IS NOT NULL AND l_display_pf_flag = 'Y') THEN
3376 -- Applying supplier price factors
3377 FOR supp_pf_rec IN supplier_pf_curr(l_line_number)
3378 LOOP
3379 l_total_price := 0;
3380 l_pf_value := supp_pf_rec.BID_CURRENCY_VALUE;
3381
3382 IF(supp_pf_rec.PRICING_BASIS = 'PER_UNIT') THEN
3383 l_pf_unit_price := l_pf_value;
3384 ELSIF(supp_pf_rec.PRICING_BASIS = 'PERCENTAGE') THEN
3385 l_pf_unit_price := (l_pf_value * l_bid_curr_unit_price/100);
3386 ELSIF(supp_pf_rec.PRICING_BASIS = 'FIXED_AMOUNT') THEN
3387 IF(l_order_type_lookup_type = 'FIXED PRICE') THEN
3388 l_pf_unit_price := l_pf_value;
3389 ELSIF(l_order_type_lookup_type = 'RATE' OR l_order_type_lookup_type = 'AMOUNT') THEN
3390 l_pf_unit_price := l_pf_value/l_auc_quantity;
3391 ELSE
3392 IF(l_contract_type = 'BLANKET' OR l_contract_type = 'CONTRACT' OR l_full_quan_req = 'Y') THEN
3393 l_pf_unit_price := l_pf_value/l_auc_quantity;
3394 ELSE
3395 l_pf_unit_price := l_pf_value/l_bid_quantity;
3396 END IF;
3397 END IF;
3398 END IF;
3399
3400 --Donot add the price factor if it is unit price
3401 IF(l_pf_unit_price IS NOT NULL AND supp_pf_rec.PRICE_ELEMENT_TYPE_ID <> -10) THEN
3402 l_total_price := l_total_price + l_pf_unit_price;
3403 END IF;
3404 END LOOP;
3405
3406 --Calculate with buyer price factors
3407 IF(l_amount IS NULL OR l_amount = 0) THEN
3408 l_unit_pf_amt := 0;
3409 ELSE
3410 IF(l_order_type_lookup_type = 'FIXED PRICE') THEN
3411 l_unit_pf_amt := l_amount;
3412 ELSIF(l_order_type_lookup_type = 'RATE' OR l_order_type_lookup_type = 'AMOUNT') THEN
3413 l_unit_pf_amt := l_amount/l_auc_quantity;
3414 ELSE
3415 IF(l_contract_type = 'BLANKET' OR l_contract_type = 'CONTRACT' OR l_full_quan_req = 'Y') THEN
3416 l_unit_pf_amt := l_amount/l_auc_quantity;
3417 ELSE
3418 l_unit_pf_amt := l_amount/l_bid_quantity;
3419 END IF;
3420 END IF;
3421 END IF;
3422
3423 l_transformed_price := ((Nvl(l_unit,0)) * l_rate) + (l_unit_pf_amt * l_rate) + (l_percentage * l_bid_curr_unit_price);
3424
3425 IF(l_transformed_price IS NOT NULL) THEN
3426 l_total_price := l_total_price + l_transformed_price;
3427 END IF;
3428
3429 select ROUND(l_total_price, decode(l_precision, 10000, 10, l_precision)) INTO l_truncated_value FROM dual;
3430
3431 IF(l_supplier_view = 'UNTRANSFORMED') THEN
3432 IF(l_amount IS NULL OR l_amount = 0) THEN
3433 l_unit_pf_amt := 0;
3434 ELSE
3435 IF(l_order_type_lookup_type = 'FIXED PRICE') THEN
3436 l_unit_pf_amt := l_amount;
3437 ELSIF(l_order_type_lookup_type = 'RATE' OR l_order_type_lookup_type = 'AMOUNT') THEN
3438 l_unit_pf_amt := l_amount/l_auc_quantity;
3439 ELSE
3440 IF(l_contract_type = 'BLANKET' OR l_contract_type = 'CONTRACT' OR l_full_quan_req = 'Y') THEN
3441 l_unit_pf_amt := l_amount/l_auc_quantity;
3442 ELSE
3443 l_unit_pf_amt := l_amount/l_bid_quantity;
3444 END IF;
3445 END IF;
3446 END IF;
3447
3448 l_trans_price := ((Nvl(l_unit,0)) * l_rate) + (l_unit_pf_amt * l_rate) + (l_percentage * l_transformed_price);
3449 ELSIF(l_supplier_view = 'TRANSFORMED') THEN
3450 l_trans_price := l_transformed_price;
3451 END IF;
3452
3453 SELECT ROUND(l_trans_price, decode(l_precision, 10000, 10, l_precision)) INTO l_bid_curr_trans_price FROM dual;
3454
3455 l_price := l_trans_price/l_rate;
3456
3457 UPDATE PON_BID_ITEM_PRICES
3458 SET BID_CURRENCY_PRICE = l_truncated_value,
3459 BID_CURRENCY_TRANS_PRICE = l_bid_curr_trans_price,
3460 PRICE = l_price
3461 WHERE AUCTION_HEADER_ID = p_auc_header_id
3462 AND BID_NUMBER = x_bid_number
3463 AND LINE_NUMBER = l_line_number;
3464
3465 ELSE -- Calculate transformed price based on buyer price factors
3466 IF(l_supplier_view = 'UNTRANSFORMED') THEN
3467 IF(l_amount IS NULL OR l_amount = 0) THEN
3468 l_unit_pf_amt := 0;
3469 ELSE
3470 IF(l_order_type_lookup_type = 'FIXED PRICE') THEN
3471 l_unit_pf_amt := l_amount;
3472 ELSIF(l_order_type_lookup_type = 'RATE' OR l_order_type_lookup_type = 'AMOUNT') THEN
3473 l_unit_pf_amt := l_amount/l_auc_quantity;
3474 ELSE
3475 IF(l_contract_type = 'BLANKET' OR l_contract_type = 'CONTRACT' OR l_full_quan_req = 'Y') THEN
3476 l_unit_pf_amt := l_amount/l_auc_quantity;
3477 ELSE
3478 l_unit_pf_amt := l_amount/l_bid_quantity;
3479 END IF;
3480 END IF;
3481 END IF;
3482
3483 l_trans_price := ((Nvl(l_unit,0)) * l_rate) + (l_unit_pf_amt * l_rate) + (l_percentage * l_bid_curr_unit_price);
3484
3485 ELSIF(l_supplier_view = 'TRANSFORMED') THEN
3486 l_trans_price := l_bid_curr_unit_price;
3487 END IF;
3488
3489 SELECT ROUND(l_trans_price, decode(l_precision, 10000, 10, l_precision)) INTO l_bid_curr_trans_price FROM dual;
3490
3491 l_price := l_trans_price/l_rate;
3492
3493 UPDATE PON_BID_ITEM_PRICES
3494 SET BID_CURRENCY_PRICE = l_bid_curr_unit_price,
3495 BID_CURRENCY_TRANS_PRICE = l_bid_curr_trans_price,
3496 PRICE = l_price
3497 WHERE AUCTION_HEADER_ID = p_auc_header_id
3498 AND BID_NUMBER = x_bid_number
3499 AND LINE_NUMBER = l_line_number;
3500
3501 END IF;
3502 END LOOP;
3503 END IF;
3504 -- Code for cost factors Ends - bug 12607365 (FP of 11665610)
3505
3506 /* Multiple Active Offers : Perform this validation when multiple active offers
3507 * are not enabled.
3508 */
3509 IF PON_MULTIPLE_RESPONSE_PKG.multiple_resp_enabled(p_auc_header_id,p_buyer_user) <> 'Y' then
3510 -- here should commit the batch
3511 -- before commit, check whether this user already has a draft
3512 -- created. Maybe from a different session
3513 BEGIN
3514 /* Offer Enhancement Chnages Start */
3515
3516 select bid_number,nvl(surrog_bid_flag, 'N')
3517 into l_other_draft_bid_number,l_surrogate_bid_flag
3518 from pon_bid_headers
3519 where auction_header_id = p_auc_header_id
3520 and bid_number <> x_bid_number
3521 and bid_status = 'DRAFT'
3522 and trading_partner_id = p_tpid
3523 and trading_partner_contact_id = p_tpcid
3524 and nvl(vendor_site_id, -1) = nvl(p_vensid, -1);
3525
3526 if l_other_draft_bid_number is not null and l_other_draft_bid_number >0 then
3527
3528 if (p_buyer_user='Y') then
3529
3530 if l_surrogate_bid_flag='N' then
3531 l_other_draft_bid_number := null;
3532 end if;
3533 else
3534 if l_surrogate_bid_flag='Y' then
3535 l_other_draft_bid_number := null;
3536 end if;
3537 end if;
3538 end if;
3539
3540 /* Offer Enhancement Chnages End */
3541 EXCEPTION
3542 WHEN NO_DATA_FOUND THEN
3543 l_other_draft_bid_number := null;
3544 END;
3545 END IF; --Multiple offers end
3546
3547 IF (l_other_draft_bid_number IS NOT null) THEN
3548 x_return_status := 1;
3549 x_return_code := 'MULTIPLE_REBID';
3550 ROLLBACK;
3551 ELSE
3552 x_return_status := 0;
3553 x_return_code := 'SUCCESS';
3554 COMMIT;
3555 END IF;
3556
3557 -- Find the new range
3558 l_batch_start := l_batch_end + 1;
3559 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
3560 l_batch_end := l_max_line_number;
3561 ELSE
3562 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3563 END IF;
3564
3565 END LOOP;
3566
3567 /* Unsolicited Lines Project : Copy unsolicited lines only when defaulting
3568 from bid on current document and unsolcited lines are allowed.
3569 */
3570 IF p_source_bid_num IS NOT null AND l_allow_unsol_lines = 'Y' THEN
3571
3572 SELECT Decode(pbh.auction_header_id, p_auc_header_id, 'Y', 'N')
3573 INTO l_can_copy_unsol_lines
3574 FROM pon_bid_headers pbh
3575 WHERE pbh.bid_number = p_source_bid_num;
3576
3577 IF l_can_copy_unsol_lines = 'Y' THEN
3578 -- Call the copy api.
3579 PON_UNSOL_UTIL_PKG.COPY_UNSOL_OFFER_LINES(p_auction_id => p_auc_header_id,
3580 p_source_bid_num => p_source_bid_num,
3581 p_bid_number => x_bid_number,
3582 p_uda_template_id => l_uda_template_id,
3583 p_tpid => p_tpid,
3584 p_tpcid => p_tpcid,
3585 p_userid => p_userid,
3586 p_vensid => p_vensid);
3587 END IF;
3588 END IF;
3589
3590 -- END BATCHING
3591
3592 -- Populate header level display_price_factors_flag
3593 -- It is 'Y' if any line has the line level flag set
3594 UPDATE pon_bid_headers bh
3595 SET bh.display_price_factors_flag =
3596 nvl((SELECT 'Y'
3597 FROM pon_bid_item_prices bl
3598 WHERE bl.bid_number = bh.bid_number
3599 AND bl.display_price_factors_flag = 'Y'
3600 AND rownum = 1), 'N')
3601 WHERE bh.bid_number = x_bid_number;
3602
3603 -- Handle proxy bidding: done in check_and_load_bid
3604
3605 END create_new_draft_bid;
3606
3607 -- ======================================================================
3608 -- PROCEDURE: EXPAND_DRAFT PRIVATE
3609 -- PARAMETERS:
3610 -- p_bid_number IN bid_number to expand
3611 -- x_rebid_flag OUT Y if bid expanded is a rebid
3612 --
3613 -- COMMENT: pre-release 12 draft bids do not have lines without bids.
3614 -- As such, they need to be expanded to include them
3615 -- ======================================================================
3616 PROCEDURE expand_draft
3617 (
3618 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3619 x_rebid_flag OUT NOCOPY VARCHAR2
3620 ) IS
3621 l_source_bid_num pon_bid_headers.bid_number%TYPE;
3622
3623 l_auc_header_id pon_auction_headers_all.auction_header_id%TYPE;
3624 l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
3625 l_tpid pon_bid_headers.trading_partner_id%TYPE;
3626 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3627 l_userid pon_bid_headers.created_by%TYPE;
3628 l_vensid pon_bid_headers.vendor_site_id%TYPE;
3629 l_supp_seq_number pon_bidding_parties.sequence%TYPE;
3630 l_has_pe_flag VARCHAR2(1);
3631 l_blanket VARCHAR2(1);
3632 l_full_qty VARCHAR2(1);
3633 l_rate pon_bid_headers.rate%TYPE;
3634 l_price_prec pon_bid_headers.number_price_decimals%TYPE;
3635 l_curr_prec fnd_currencies.precision%TYPE;
3636
3637 l_max_line_number pon_bid_item_prices.line_number%TYPE;
3638 l_batch_start pon_bid_item_prices.line_number%TYPE;
3639 l_batch_end pon_bid_item_prices.line_number%TYPE;
3640 l_skip_pf_for_batch VARCHAR2(1);
3641 BEGIN
3642
3643 -- Need to get auction_header_id and supplier info
3644 SELECT ah.auction_header_id,
3645 ah.trading_partner_id,
3646 decode(ah.has_price_elements, 'Y', 'Y', 'N'),
3647 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
3648 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
3649 bh.trading_partner_id,
3650 bh.trading_partner_contact_id,
3651 bh.vendor_site_id,
3652 bh.created_by,
3653 bh.rate,
3654 bh.number_price_decimals,
3655 cu.precision
3656 INTO l_auc_header_id,
3657 l_auctpid,
3658 l_has_pe_flag,
3659 l_blanket,
3660 l_full_qty,
3661 l_tpid,
3662 l_tpcid,
3663 l_vensid,
3664 l_userid,
3665 l_rate,
3666 l_price_prec,
3667 l_curr_prec
3668 FROM pon_bid_headers bh, pon_auction_headers_all ah, fnd_currencies cu
3669 WHERE bh.bid_number = p_bid_number
3670 AND ah.auction_header_id = bh.auction_header_id
3671 AND cu.currency_code = bh.bid_currency_code;
3672
3673 BEGIN
3674 -- Get the supplier sequence number
3675 SELECT bp.sequence
3676 INTO l_supp_seq_number
3677 FROM pon_bidding_parties bp
3678 WHERE bp.auction_header_id = l_auc_header_id
3679 AND bp.trading_partner_id = l_tpid
3680 AND bp.vendor_site_id = l_vensid;
3681
3682 EXCEPTION
3683 WHEN NO_DATA_FOUND THEN
3684
3685 -- if the current supplier is not invited, then treat this
3686 -- sequence_number to be null
3687
3688 l_supp_seq_number := null;
3689 END;
3690
3691
3692 BEGIN
3693
3694 -- Determine the latest ACTIVE bid and set rebid_flag to Y
3695 -- Since there can only exist a single ACTIVE bid on an amendment for
3696 -- a particular user on a site, we use the rownum = 1 optimisation
3697 SELECT bh.bid_number, 'Y'
3698 INTO l_source_bid_num, x_rebid_flag
3699 FROM pon_bid_headers bh
3700 WHERE bh.auction_header_id = l_auc_header_id
3701 AND bh.trading_partner_id = l_tpid
3702 AND bh.trading_partner_contact_id = l_tpcid
3703 AND bh.vendor_site_id = l_vensid
3704 AND bh.bid_status = 'ACTIVE'
3705 AND rownum = 1
3706 ORDER BY bh.publish_date DESC;
3707
3708 EXCEPTION
3709 -- there is no old bid to get old value columns
3710 WHEN NO_DATA_FOUND THEN
3711 x_rebid_flag := 'N';
3712 END;
3713
3714 -- START BATCHING
3715
3716 -- Determine the maximum line number for the negotiation
3717 SELECT ah.max_internal_line_num
3718 INTO l_max_line_number
3719 FROM pon_auction_headers_all ah
3720 WHERE ah.auction_header_id = l_auc_header_id;
3721
3722 -- Define the initial range (line numbers are indexed from 1)
3723 l_batch_start := 1;
3724 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
3725 l_batch_end := l_max_line_number;
3726 ELSE
3727 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3728 END IF;
3729
3730 WHILE (l_batch_start <= l_max_line_number) LOOP
3731
3732 -- We need to insert those auction side lines which haven't recieved bids
3733 -- line level display pf flag also populated
3734 insert_auction_lines
3735 (l_auc_header_id,
3736 p_bid_number,
3737 l_userid,
3738 l_auctpid,
3739 l_tpid,
3740 l_vensid,
3741 l_has_pe_flag,
3742 l_blanket,
3743 l_full_qty,
3744 l_supp_seq_number,
3745 l_rate,
3746 l_price_prec,
3747 l_curr_prec,
3748 l_batch_start,
3749 l_batch_end);
3750
3751 -- If it as a rebid, we need to populate old_value columns
3752 IF (x_rebid_flag = 'Y') THEN
3753 -- special case for pre-rel12 draft - check
3754 populate_old_value_columns
3755 (p_bid_number,
3756 l_source_bid_num,
3757 l_batch_start,
3758 l_batch_end);
3759 END IF;
3760
3761 -- Find the new range
3762 l_batch_start := l_batch_end + 1;
3763 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
3764 l_batch_end := l_max_line_number;
3765 ELSE
3766 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3767 END IF;
3768
3769 END LOOP;
3770
3771 -- END BATCHING
3772
3773 -- Populate header level display_price_factors_flag
3774 -- It is 'Y' if any line has the line level flag set
3775 UPDATE pon_bid_headers bh
3776 SET bh.display_price_factors_flag =
3777 nvl((SELECT 'Y'
3778 FROM pon_bid_item_prices bl
3779 WHERE bl.bid_number = bh.bid_number
3780 AND bl.display_price_factors_flag = 'Y'
3781 AND rownum = 1), 'N')
3782 WHERE bh.bid_number = p_bid_number;
3783
3784 -- Populate has_bid_flag as it is a rel12 column
3785 populate_has_bid_flag
3786 (l_auc_header_id,
3787 p_bid_number);
3788
3789 /* once we have finished upgrading the draft, reset the flag to Y */
3790 update pon_bid_headers
3791 set rel12_draft_flag = 'Y'
3792 where bid_number = p_bid_number;
3793
3794 -- handle_proxy will be called in check_and_load_bid
3795
3796 END expand_draft;
3797
3798 -- ======================================================================
3799 -- PROCEDURE: LOCK_DRAFT PRIVATE
3800 -- PARAMETERS:
3801 -- p_bid_number IN bid number to lock
3802 -- p_tpid IN trading partner id to lock with
3803 -- p_tpcid IN trading partner contact id to lock with
3804 -- x_return_status OUT 0 for success, 1 for error
3805 -- x_return_code OUT returned error code, or SUCCESS
3806 --
3807 -- COMMENT: If the bid is not locked by another user, it is locked.
3808 -- ======================================================================
3809 PROCEDURE lock_draft
3810 (
3811 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3812 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
3813 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
3814 x_return_status OUT NOCOPY NUMBER,
3815 x_return_code OUT NOCOPY VARCHAR2
3816 ) IS
3817 l_draft_locked pon_bid_headers.draft_locked%TYPE;
3818 l_tpid pon_bid_headers.trading_partner_id%TYPE;
3819 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3820 BEGIN
3821
3822 -- pull up draft lock info
3823 SELECT draft_locked, draft_locked_by, draft_locked_by_contact_id
3824 INTO l_draft_locked, l_tpid, l_tpcid
3825 FROM pon_bid_headers
3826 WHERE bid_number = p_bid_number;
3827
3828 -- If the draft is locked by another user, return an error
3829 IF (l_draft_locked = 'Y' AND (p_tpid <> l_tpid OR p_tpcid <> l_tpcid)) THEN
3830
3831 x_return_status := 1;
3832 x_return_code := 'DRAFT_LOCK_ERR';
3833 RETURN;
3834
3835 -- If the draft is not locked, lock it
3836 ELSIF (l_draft_locked = 'N') THEN
3837
3838 UPDATE pon_bid_headers
3839 SET draft_locked = 'Y',
3840 draft_locked_by = p_tpid,
3841 draft_locked_by_contact_id = p_tpcid,
3842 draft_locked_date = sysdate
3843 WHERE bid_number = p_bid_number;
3844
3845 END IF;
3846
3847 x_return_status := 0;
3848 x_return_code := 'SUCCESS';
3849
3850 END lock_draft;
3851
3852 -- ======================================================================
3853 -- PROCEDURE: CHECK_AMENDMENTS_ACKED PRIVATE
3854 -- PARAMETERS:
3855 -- p_auc_header_id IN auction header id of negotiation
3856 -- p_tpid IN trading partner id of supplier
3857 -- p_tpcid IN trading partner contact id of supplier
3858 -- x_return_status OUT 0 for success, 1 for error
3859 -- x_return_code OUT returned error code, or SUCCESS
3860 --
3861 -- COMMENT: Determines if all amendments on the current round have been acknowledged
3862 -- ======================================================================
3863 PROCEDURE check_amendments_acked
3864 (
3865 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3866 p_tpid IN pon_auction_headers_all.trading_partner_id%TYPE,
3867 p_tpcid IN pon_auction_headers_all.trading_partner_contact_id%TYPE,
3868 x_return_status OUT NOCOPY NUMBER,
3869 x_return_code OUT NOCOPY VARCHAR2
3870 ) IS
3871 l_orig_amend_id pon_auction_headers_all.auction_header_id_orig_amend%TYPE;
3872 l_amend_not_acked VARCHAR2(1);
3873 BEGIN
3874
3875 -- Get the original amendments auction header id
3876 SELECT ah.auction_header_id_orig_amend
3877 INTO l_orig_amend_id
3878 FROM pon_auction_headers_all ah
3879 WHERE ah.auction_header_id = p_auc_header_id;
3880
3881 -- There must be as many acknowledgements as amendments
3882 SELECT decode(count(rownum), 0, 'N', 'Y')
3883 INTO l_amend_not_acked
3884 FROM pon_auction_headers_all ah, pon_acknowledgements ac
3885 WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
3886 AND ah.auction_status IN ('AMENDED', 'ACTIVE')
3887 -- ignore the original amendment
3888 AND ah.amendment_number > 0
3889 AND ac.auction_header_id (+) = ah.auction_header_id
3890 AND ac.trading_partner_id (+) = p_tpid
3891 AND ac.trading_partner_contact_id (+)= p_tpcid
3892 AND ac.acknowledgement_response IS null;
3893
3894 IF (l_amend_not_acked = 'Y') THEN
3895 x_return_status := 1;
3896 x_return_code := 'NEED_ACKNOWLEDGE';
3897 RETURN;
3898 END IF;
3899
3900 x_return_status := 0;
3901 x_return_code := 'SUCCESS';
3902
3903 END check_amendments_acked;
3904
3905 -- ======================================================================
3906 -- PROCEDURE: VALIDATE_SITE PRIVATE
3907 -- PARAMETERS:
3908 -- p_auc_header_id IN auction header id of negotiation
3909 -- p_tpid IN trading partner id of supplier
3910 -- p_vensid IN vendor site to place a bid for
3911 -- p_venscode IN corresponding vendor site code
3912 -- x_return_status OUT 0 for success, 1 for error
3913 -- x_return_code OUT returned error code, or SUCCESS
3914 --
3915 -- COMMENT: Determine if the vensid, venscode combination can bid on the negotiation
3916 -- ======================================================================
3917 PROCEDURE validate_site
3918 (
3919 p_auc_header_id IN pon_bidding_parties.auction_header_id%TYPE,
3920 p_tpid IN pon_bidding_parties.trading_partner_id%TYPE,
3921 p_vensid IN pon_bidding_parties.vendor_site_id%TYPE,
3922 p_venscode IN pon_bidding_parties.vendor_site_code%TYPE,
3923 x_return_status OUT NOCOPY NUMBER,
3924 x_return_code OUT NOCOPY VARCHAR2
3925 ) IS
3926 CURSOR sites IS
3927 SELECT vendor_site_id id, vendor_site_code code
3928 FROM pon_bidding_parties
3929 WHERE auction_header_id = p_auc_header_id
3930 AND trading_partner_id = p_tpid;
3931
3932 l_sites_invited BOOLEAN DEFAULT false;
3933 l_site_valid BOOLEAN DEFAULT false;
3934
3935 BEGIN
3936
3937 -- Look through the invited sites for this auction
3938 FOR site IN sites LOOP
3939 IF (site.id > 0) THEN
3940 l_sites_invited := true;
3941 IF (p_vensid = site.id AND p_venscode = site.code) THEN
3942 l_site_valid := true;
3943 END IF;
3944 END IF;
3945 END LOOP;
3946
3947 -- If multiple sites were invited then, if a site id was specified,
3948 -- return an error if it was not invited. Else, indicate that
3949 -- a site id needs to be specified
3950 IF (l_sites_invited) THEN
3951 IF (p_vensid > 0) THEN
3952 IF (NOT l_site_valid) THEN
3953 x_return_status := 1;
3954 x_return_code := 'INVALID_VENDOR_SITE';
3955 RETURN;
3956 END IF;
3957 ELSE
3958 x_return_status := 1;
3959 x_return_code := 'PICK_VENDOR_SITE';
3960 RETURN;
3961 END IF;
3962 END IF;
3963
3964 x_return_status := 0;
3965 x_return_code := 'SITE_VALID';
3966
3967 END validate_site;
3968
3969 -- ======================================================================
3970 -- PROCEDURE: IS_BIDDING_ALLOWED PUBLIC
3971 -- PARAMETERS:
3972 -- p_auc_header_id IN auction header id of negotiation
3973 -- p_tpid IN trading partner id of supplier
3974 -- p_tpcid IN trading partner contact id of supplier
3975 -- p_vensid IN vendor site to place a bid for
3976 -- p_venscode IN corresponding vendor site code
3977 -- p_buyer_user IN determines if surrogate bid
3978 -- p_evaluator_id IN evaluator user id
3979 -- p_eval_flag IN flag indicating if the response is an evaluation
3980 -- p_action_code IN determines if certain validation should be suppressed
3981 -- x_return_status OUT 0 for success, 1 for error
3982 -- x_return_code OUT returned error code, or SUCCESS
3983 --
3984 -- COMMENT: Determine if the bidding action specified by action code can
3985 -- be completed at this time.
3986 -- ======================================================================
3987 PROCEDURE is_bidding_allowed
3988 (
3989 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3990 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
3991 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
3992 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
3993 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
3994 p_buyer_user IN VARCHAR2,
3995 ---------- Supplier Management: Supplier Evaluation ----------
3996 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
3997 p_eval_flag IN VARCHAR2,
3998 --------------------------------------------------------------
3999 p_action_code IN VARCHAR2,
4000 x_return_status OUT NOCOPY NUMBER,
4001 x_return_code OUT NOCOPY VARCHAR2
4002 ) IS
4003 l_current_date TIMESTAMP;
4004 l_bid_number NUMBER;
4005 l_view_by_date pon_auction_headers_all.view_by_date%TYPE;
4006 l_open_date pon_auction_headers_all.open_bidding_date%TYPE;
4007 l_close_date pon_auction_headers_all.close_bidding_date%TYPE;
4008 l_auction_status pon_auction_headers_all.auction_status%TYPE;
4009 l_award_status pon_auction_headers_all.award_status%TYPE;
4010 l_bid_list_type pon_auction_headers_all.bid_list_type%TYPE;
4011 l_invited_flag VARCHAR2(1);
4012 l_bid_freq_code pon_auction_headers_all.bid_frequency_code%TYPE;
4013 l_has_draft_flag VARCHAR2(1);
4014 l_has_bid_flag VARCHAR2(1);
4015 l_has_surrog_flag VARCHAR2(1);
4016 l_auction_paused VARCHAR2(1);
4017 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
4018 l_supp_end_date TIMESTAMP;
4019 l_eval_flag pon_bid_headers.evaluation_flag%TYPE := nvl(p_eval_flag,'N');
4020
4021 BEGIN
4022
4023 ----------------------- Supplier Management: Supplier Evaluation -----------------------
4024 -- Modified the if condition to only do the validate_site for non-evaluation response --
4025 ----------------------------------------------------------------------------------------
4026 -- Verify that a valid site id has been specified if not loading a bid
4027 IF (p_action_code <> 'LOAD_BID' AND
4028 p_action_code <> 'LOAD_DRAFT' AND
4029 l_eval_flag = 'N') THEN
4030 validate_site(p_auc_header_id, p_tpid, p_vensid, p_venscode, x_return_status, x_return_code);
4031
4032 IF (x_return_status = 1) THEN
4033 -- return status and code already set
4034 RETURN;
4035 END IF;
4036 END IF;
4037
4038 BEGIN
4039 -- select the various values
4040 SELECT sysdate,
4041 nvl(ah.view_by_date, ah.open_bidding_date),
4042 ah.open_bidding_date,
4043 ah.close_bidding_date,
4044 ah.auction_status,
4045 nvl(ah.award_status, 'NO'),
4046 ah.bid_list_type,
4047 ah.bid_frequency_code,
4048 ah.auction_header_id_orig_amend,
4049 nvl(ah.is_paused, 'N')
4050 INTO l_current_date,
4051 l_view_by_date,
4052 l_open_date,
4053 l_close_date,
4054 l_auction_status,
4055 l_award_status,
4056 l_bid_list_type,
4057 l_bid_freq_code,
4058 l_orig_amend_id,
4059 l_auction_paused
4060 FROM pon_auction_headers_all ah
4061 WHERE auction_header_id = p_auc_header_id;
4062
4063 EXCEPTION
4064 WHEN NO_DATA_FOUND THEN
4065 x_return_status := 1;
4066 x_return_code := 'INVALID_HEADER_ID';
4067 RETURN;
4068 END;
4069
4070
4071 -- View by date (or open date if null) should be reached
4072 IF (l_current_date < l_view_by_date) THEN
4073 x_return_status := 1;
4074 x_return_code := 'AUCTION_NOT_VIEW';
4075 RETURN;
4076 END IF;
4077
4078 -- Auction should not be cancelled
4079 IF (l_auction_status = 'CANCELLED') THEN
4080 x_return_status := 1;
4081 x_return_code := 'AUCTION_CANCELLED';
4082 RETURN;
4083 END IF;
4084
4085 -- Auction should not have been deleted
4086 IF (l_auction_status = 'DELETED') THEN
4087 x_return_status := 1;
4088 x_return_code := 'AUCTION_DELETED';
4089 RETURN;
4090 END IF;
4091
4092 -- When saving bid
4093 IF (p_action_code = 'SAVE_BID') THEN
4094
4095 -- Auction should be open
4096 IF (l_current_date < l_open_date) THEN
4097 x_return_status := 1;
4098 x_return_code := 'AUCTION_NOT_OPEN';
4099 RETURN;
4100 END IF;
4101
4102 -- Auction should not be paused
4103 IF (l_auction_status = 'PAUSED') THEN
4104 x_return_status := 1;
4105 x_return_code := 'AUCTION_PAUSED';
4106 RETURN;
4107 END IF;
4108
4109 -- make sure there is a draft that we're trying
4110 -- to publish
4111 BEGIN
4112
4113 if (p_buyer_user='Y')
4114 then
4115 SELECT bid_number
4116 INTO l_bid_number
4117 FROM pon_bid_headers bh
4118 WHERE bh.auction_header_id = p_auc_header_id
4119 AND bh.trading_partner_id = p_tpid
4120 --AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4121 AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4122 (l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4123 AND bh.vendor_site_id = p_vensid
4124 AND bh.bid_status = 'DRAFT'
4125 AND nvl(bh.surrog_bid_flag,'N')='Y'
4126 AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4127 AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
4128 else
4129 SELECT bid_number
4130 INTO l_bid_number
4131 FROM pon_bid_headers bh
4132 WHERE bh.auction_header_id = p_auc_header_id
4133 AND bh.trading_partner_id = p_tpid
4134 --AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4135 AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4136 (l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4137 AND bh.vendor_site_id = p_vensid
4138 AND bh.bid_status = 'DRAFT'
4139 AND nvl(bh.surrog_bid_flag,'N')='N'
4140 AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4141 AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
4142 end if;
4143
4144 EXCEPTION
4145 WHEN NO_DATA_FOUND THEN
4146 x_return_status := 1;
4147 x_return_code := 'NO_DRAFT_BID';
4148 RETURN;
4149 END;
4150
4151 -- Supplier should not be invalid
4152 BEGIN
4153 if (p_buyer_user='Y')
4154 then
4155 SELECT nvl(pov.end_date_active, sysdate)
4156 INTO l_supp_end_date
4157 FROM pon_bid_headers bh, po_vendors pov
4158 WHERE bh.auction_header_id = p_auc_header_id
4159 AND bh.trading_partner_id = p_tpid
4160 --AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4161 AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4162 (l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4163 AND bh.vendor_site_id = p_vensid
4164 AND bh.bid_status = 'DRAFT'
4165 AND nvl(bh.surrog_bid_flag,'N')='Y'
4166 AND pov.vendor_id = bh.vendor_id
4167 AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4168 AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
4169 else
4170 SELECT nvl(pov.end_date_active, sysdate)
4171 INTO l_supp_end_date
4172 FROM pon_bid_headers bh, po_vendors pov
4173 WHERE bh.auction_header_id = p_auc_header_id
4174 AND bh.trading_partner_id = p_tpid
4175 --AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4176 AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4177 (l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4178 AND bh.vendor_site_id = p_vensid
4179 AND bh.bid_status = 'DRAFT'
4180 AND nvl(bh.surrog_bid_flag,'N')='N'
4181 AND pov.vendor_id = bh.vendor_id
4182 AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4183 AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
4184 end if;
4185
4186 EXCEPTION
4187 WHEN NO_DATA_FOUND THEN
4188 l_supp_end_date := null;
4189 END;
4190
4191 ------------------------ Supplier Management: Supplier Evaluation ------------------------
4192
4193 -- If l_supp_end_date is null, bid might be for prospective supplier
4194 IF (l_supp_end_date IS null) THEN
4195 BEGIN
4196 SELECT sysdate
4197 INTO l_supp_end_date
4198 FROM pon_bid_headers bh, pos_supplier_registrations psr
4199 WHERE bh.auction_header_id = p_auc_header_id
4200 AND bh.trading_partner_id = p_tpid
4201 AND psr.supplier_reg_id = bh.trading_partner_id
4202 AND psr.registration_status in ('RIF_SUPPLIER', 'PENDING_APPROVAL') -- Bug 9037236
4203 AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4204 (l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4205 AND bh.bid_status = 'DRAFT'
4206 AND bh.vendor_id = -1
4207 AND bh.vendor_site_id = -1
4208 AND nvl(bh.evaluation_flag, 'N') = l_eval_flag
4209 AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
4210 EXCEPTION
4211 WHEN NO_DATA_FOUND THEN
4212 l_supp_end_date := null;
4213 END;
4214 END IF;
4215 ------------------------------------------------------------------------------------------
4216
4217 IF (l_supp_end_date IS null OR l_supp_end_date < l_current_date) THEN
4218 x_return_status := 1;
4219 x_return_code := 'INACTIVE_SUPPLIER_ERROR';
4220 RETURN;
4221 END IF;
4222
4223 END IF;
4224
4225 -- Check auction close date (unless the auction is paused)
4226 IF (l_auction_paused <> 'Y' AND l_close_date < l_current_date) THEN
4227
4228 -- Auction should not be closed if not a buyer
4229 -- Bug 8992891: Modify the if condition so that Enter Evaluation is still available after closing RFI
4230 IF (p_buyer_user = 'N' AND l_eval_flag = 'N') THEN
4231 x_return_status := 1;
4232 x_return_code := 'AUCTION_CLOSED';
4233 RETURN;
4234 -- Award process should not be started if buyer
4235 ELSIF (l_award_status <> 'NO') THEN
4236 x_return_status := 1;
4237 x_return_code := 'AUCTION_AWARD_STARTED';
4238 RETURN;
4239 END IF;
4240 END IF;
4241
4242 -- If private auction, check if supplier is invited
4243 IF (l_bid_list_type = 'PRIVATE_BID_LIST') THEN
4244
4245 -- Do an existence check
4246 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
4247 INTO l_invited_flag
4248 FROM pon_bidding_parties
4249 WHERE auction_header_id = p_auc_header_id
4250 -- AND trading_partner_id = p_tpid; -- Modified for ER: Supplier Management: Supplier Evaluation
4251 AND ((trading_partner_id IS NOT NULL AND trading_partner_id = p_tpid) OR
4252 (trading_partner_id IS NULL AND requested_supplier_id = p_tpid));
4253
4254 IF (l_invited_flag = 'N') THEN
4255 x_return_status := 1;
4256 x_return_code := 'NOT_INVITED';
4257 RETURN;
4258 END IF;
4259 END IF;
4260
4261 -- Check if single best bid auction
4262 IF (l_bid_freq_code = 'SINGLE_BID_ONLY' AND l_eval_flag = 'N') THEN -- Modified for ER: Supplier Management: Supplier Evaluation
4263
4264 -- Do an existence check
4265 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
4266 INTO l_has_bid_flag
4267 FROM pon_bid_headers
4268 WHERE auction_header_id = p_auc_header_id
4269 AND trading_partner_id = p_tpid
4270 AND vendor_site_id = p_vensid
4271 AND bid_status = 'ACTIVE'
4272 AND nvl(evaluation_flag, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
4273
4274 IF (l_has_bid_flag = 'Y') THEN
4275 x_return_status := 1;
4276 x_return_code := 'SINGLE_BEST_BID';
4277 RETURN;
4278 END IF;
4279
4280 -- Do an existence check
4281 -- bug 5041654
4282 -- if we are loading a bid, we do not need
4283 -- to check whether some other user already has a draft bid
4284 -- in case of a single-best bid auction, as it is quite rare occurence
4285 -- in R12 that 2 users from same company will click 'create bid' at the same
4286 -- time. We will have this check during submit bid anyways
4287
4288 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
4289 INTO l_has_draft_flag
4290 FROM pon_bid_headers
4291 WHERE auction_header_id = p_auc_header_id
4292 AND trading_partner_id = p_tpid
4293 AND trading_partner_contact_id <> p_tpcid
4294 AND vendor_site_id = p_vensid
4295 AND bid_status = 'DRAFT'
4296 AND nvl(evaluation_flag, 'N') = 'N' -- Added for ER: Supplier Management: Supplier Evaluation
4297 /*Offer Enhancements : If buyer, check only surrogate bids.
4298 If supplier, check only supplier bids */
4299 AND ( (p_buyer_user = 'Y' AND Nvl(surrog_bid_flag,'N') = 'Y')
4300 OR (p_buyer_user = 'N' AND Nvl(surrog_bid_flag,'N') = 'N'));
4301
4302 IF (l_has_draft_flag = 'Y' AND
4303 p_action_code <> 'LOAD_DRAFT') THEN
4304 x_return_status := 1;
4305 x_return_code := 'OTHER_USERS_DRAFT_SBB';
4306 RETURN;
4307 END IF;
4308 END IF;
4309
4310 ------------------------- Supplier Management: Supplier Evaluation -------------------------
4311 -- Added the if condition to only do the following validation for non-evaluation response --
4312 --------------------------------------------------------------------------------------------
4313
4314 /* Offer Enahncement , As part of it added condition AND bh.bid_status='ACTIVE'
4315 to allow buyer to enter surrogate offer even if a draft offer exists at the supplier end */
4316
4317 IF (l_eval_flag = 'N') THEN
4318 -- Check if a buyer/supplier has already placed a bid on the round
4319 -- I.e. check for surrog bid if supplier, or a supplier bid if buyer
4320 SELECT decode(count(bh.auction_header_id), 0, 'N', 'Y')
4321 INTO l_has_surrog_flag
4322 FROM pon_bid_headers bh, pon_auction_headers_all ah
4323 WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
4324 AND bh.auction_header_id = ah.auction_header_id
4325 AND bh.trading_partner_id = p_tpid
4326 AND bh.trading_partner_contact_id = p_tpcid
4327 AND bh.vendor_site_id = p_vensid
4328 AND bh.bid_status='ACTIVE'
4329 AND nvl(bh.surrog_bid_flag, 'N') = decode(p_buyer_user, 'Y', 'N', 'Y')
4330 AND nvl(bh.evaluation_flag, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
4331
4332 IF (l_has_surrog_flag = 'Y') THEN
4333 IF (p_buyer_user = 'Y') THEN
4334 x_return_status := 1;
4335 x_return_code := 'SURROG_BID_ERROR_BUYER';
4336 ELSE
4337 x_return_status := 1;
4338 x_return_code := 'SURROG_BID_ERROR_SUPPLIER';
4339 END IF;
4340 RETURN;
4341 END IF;
4342 END IF;
4343
4344 x_return_status := 0;
4345 x_return_code := 'SUCCESS';
4346
4347 END is_bidding_allowed;
4348
4349 -- ======================================================================
4350 -- PROCEDURE: GET_SOURCE_BID PRIVATE
4351 -- PARAMETERS:
4352 -- p_auc_header_id IN auction header id of negotiation
4353 -- p_tpid IN trading partner id of supplier
4354 -- p_tpcid IN trading partner contact id of supplier
4355 -- p_vensid IN vendor site to place a bid for
4356 -- p_evaluator_id IN evaluator user id
4357 -- p_eval_flag IN flag indicating if the response is an evaluation
4358 -- p_action_code IN determines if certain validation should be suppressed
4359 -- x_rebid_flag OUT flag determining if rebid or not
4360 -- x_bid_number OUT bid number of exisiting draft on current amendment
4361 -- x_prev_bid_number OUT source bid number
4362 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
4363 -- x_round_bid_def OUT Y if source bid is on a previous round
4364 -- x_prev_bid_disq OUT Y is source bid was disqualified
4365 -- x_prev_bid_wthd OUT Y is source bid was Withdrawn
4366 -- x_withdraw_reason source bid Withdraw Reason
4367 -- x_return_status OUT 0 for success, 1 for error
4368 -- x_return_code OUT returned error code, or SUCCESS
4369 --
4370 -- COMMENT: Determine if a draft already exists on the current amendment.
4371 -- If not, determines which bid to default from or to create a new draft.
4372 -- Also checks if another user has a draft on the current amendment.
4373 -- ======================================================================
4374 PROCEDURE get_source_bid
4375 (
4376 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
4377 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
4378 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
4379 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
4380 ---------- Supplier Management: Supplier Evaluation ----------
4381 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
4382 p_eval_flag IN VARCHAR2,
4383 --------------------------------------------------------------
4384 p_action_code IN VARCHAR2,
4385 p_buyer_user IN VARCHAR2,
4386
4387 x_rebid_flag OUT NOCOPY VARCHAR2,
4388 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4389 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4390 x_amend_bid_def OUT NOCOPY VARCHAR2,
4391 x_round_bid_def OUT NOCOPY VARCHAR2,
4392 x_prev_bid_disq OUT NOCOPY VARCHAR2,
4393 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
4394 x_withdraw_reason OUT NOCOPY VARCHAR2,
4395 x_return_status OUT NOCOPY NUMBER,
4396 x_return_code OUT NOCOPY VARCHAR2
4397 ) IS
4398 l_source_header_id pon_auction_headers_all.auction_header_id%TYPE;
4399 l_status_order NUMBER;
4400 l_rel12_draft pon_bid_headers.rel12_draft_flag%TYPE;
4401
4402 l_prev_round_id pon_auction_headers_all.auction_header_id%TYPE;
4403 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
4404
4405 l_terms_cond_apply VARCHAR2(1);
4406
4407 l_ignored_return pon_bid_headers.bid_number%TYPE;
4408 -- for the bug 13984944
4409 l_org_contract_status pon_contracts.contract_status%TYPE;
4410 l_enabled_flag pon_contracts.enabled_flag%TYPE;
4411 l_org_id pon_auction_headers_all.org_id%TYPE;
4412 l_count_org NUMBER;
4413
4414 -- select bids on all amendments/previous round by the current user for the current site
4415 CURSOR current_users_bids_byr IS
4416 SELECT bh.bid_number,
4417 bh.auction_header_id,
4418 decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
4419 'RESUBMISSION', 3, 'ARCHIVED' , '4' ,'DISQUALIFIED', 5) status_order,
4420 decode(bh.bid_status, 'ACTIVE',
4421 decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
4422 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
4423 decode(ah.auction_header_id, p_auc_header_id, 'N',
4424 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
4425
4426 decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
4427 nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag,
4428 decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_wthd,
4429 bh.withdraw_reason
4430 FROM pon_bid_headers bh, pon_auction_headers_all ah
4431 WHERE
4432 -- look at all amendments on the current round
4433 (ah.auction_header_id_orig_amend = l_orig_amend_id
4434 -- look at all amendments on the previous round
4435 OR ah.auction_header_id_orig_amend = l_prev_round_id)
4436 AND bh.auction_header_id = ah.auction_header_id
4437 AND nvl(bh.surrog_bid_flag,'N')='Y'
4438 AND bh.trading_partner_id = p_tpid
4439 -- AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4440 AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4441 (p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4442 AND nvl(bh.vendor_site_id, -1) = p_vensid
4443 -- we ignore DRAFT bids on previous rounds
4444 AND ((bh.bid_status = 'DRAFT'
4445 AND ah.auction_header_id_orig_amend = l_orig_amend_id)
4446 OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
4447 OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
4448 AND nvl(bh.evaluation_flag, 'N') = p_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4449 ORDER BY nvl(ah.auction_round_number, 1) DESC,
4450 ah.amendment_number DESC, status_order ASC, bh.publish_date DESC;
4451
4452 /* Offer Enhancements Changes Start */
4453
4454 CURSOR current_users_bids_sup IS
4455 SELECT bh.bid_number,
4456 bh.auction_header_id,
4457 decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
4458 'RESUBMISSION', 3,'ARCHIVED' , '4', 'DISQUALIFIED', 5) status_order,
4459 decode(bh.bid_status, 'ACTIVE',
4460 decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
4461 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
4462 decode(ah.auction_header_id, p_auc_header_id, 'N',
4463 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
4464
4465 decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
4466 nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag,
4467
4468 decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_wthd,
4469 bh.withdraw_reason
4470 FROM pon_bid_headers bh, pon_auction_headers_all ah
4471 WHERE
4472 -- look at all amendments on the current round
4473 (ah.auction_header_id_orig_amend = l_orig_amend_id
4474 -- look at all amendments on the previous round
4475 OR ah.auction_header_id_orig_amend = l_prev_round_id)
4476 AND bh.auction_header_id = ah.auction_header_id
4477 AND nvl(bh.surrog_bid_flag,'N')='N'
4478 AND bh.trading_partner_id = p_tpid
4479 -- AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
4480 AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
4481 (p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
4482 AND nvl(bh.vendor_site_id, -1) = p_vensid
4483 -- we ignore DRAFT bids on previous rounds
4484 AND ((bh.bid_status = 'DRAFT'
4485 AND ah.auction_header_id_orig_amend = l_orig_amend_id)
4486 OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
4487 OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
4488 AND nvl(bh.evaluation_flag, 'N') = p_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
4489 ORDER BY nvl(ah.auction_round_number, 1) DESC,
4490 ah.amendment_number DESC, status_order ASC, bh.publish_date DESC;
4491
4492 /* Offer Enhancements Changes End */
4493
4494 -- select bids on the current amendment/previous rounds by the current user for the current site
4495 CURSOR other_users_bids IS
4496 SELECT 1 return_status,
4497 decode(bh.auction_header_id, p_auc_header_id,
4498 decode(ah.bid_frequency_code, 'SINGLE_BID_ONLY',
4499 decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT_SBB',
4500 'ACTIVE', 'SINGLE_BEST_BID'),
4501 decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT',
4502 'ACTIVE', 'OTHER_USERS_ACTIVE')),
4503 'OTHER_USERS_PREV_ROUND') return_code
4504 FROM pon_bid_headers bh, pon_auction_headers_all ah
4505 WHERE
4506 -- look at the current amendment, remove DRAFT status as part of Offer Enhancement ER
4507 -- Offer Enhancement : We need to consider draft bids
4508 (ah.auction_header_id = p_auc_header_id
4509 AND bh.bid_status IN ('DRAFT','ACTIVE')
4510 -- look at the previous round
4511 OR ah.auction_header_id_orig_amend = l_prev_round_id
4512 AND bh.bid_status = 'ACTIVE')
4513 AND bh.auction_header_id = ah.auction_header_id
4514 AND bh.trading_partner_id = p_tpid
4515 AND bh.trading_partner_contact_id <> p_tpcid
4516 AND nvl(bh.vendor_site_id, -1) = p_vensid
4517 AND nvl(bh.evaluation_flag, 'N') = 'N' -- Added for ER: Supplier Management: Supplier Evaluation
4518 /* Offer Enhancements ER : If buyer user, consider only surrogate bids.
4519 Else consider only supplier bids.
4520 */
4521 AND ( (p_buyer_user = 'Y' AND Nvl(bh.surrog_bid_flag,'N') = 'Y')
4522 OR (p_buyer_user = 'N' AND Nvl(bh.surrog_bid_flag,'N') = 'N'))
4523 ORDER BY nvl(ah.auction_round_number, 1) DESC, ah.amendment_number DESC,
4524 decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2) ASC, bh.publish_date DESC;
4525
4526 BEGIN
4527
4528 -- Get the original amendment id's for the current and prev rounds.
4529 -- Also check if contracts have been installed
4530 SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend,
4531 nvl2(ah.contract_id, 'Y', 'N'), ah.org_id
4532 INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply,l_org_id
4533 FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
4534 WHERE ah.auction_header_id = p_auc_header_id
4535 and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
4536
4537 -- Retrieve the backing bid info from the cursor
4538 -- We only need the first such bid
4539 IF p_buyer_user='Y'
4540 then
4541 OPEN current_users_bids_byr;
4542 FETCH current_users_bids_byr
4543 INTO x_prev_bid_number, l_source_header_id, l_status_order,
4544 x_rebid_flag, x_round_bid_def, x_amend_bid_def,
4545 x_prev_bid_disq, l_rel12_draft,x_prev_bid_wthd,x_withdraw_reason;
4546 CLOSE current_users_bids_byr;
4547 else
4548 OPEN current_users_bids_sup;
4549 FETCH current_users_bids_sup
4550 INTO x_prev_bid_number, l_source_header_id, l_status_order,
4551 x_rebid_flag, x_round_bid_def, x_amend_bid_def,
4552 x_prev_bid_disq, l_rel12_draft,x_prev_bid_wthd,x_withdraw_reason;
4553 CLOSE current_users_bids_sup;
4554 end if;
4555
4556 -- If the current user has a previous bid
4557 IF (x_prev_bid_number IS NOT null) THEN
4558
4559 -- had a backing DRAFT bid
4560 IF (l_status_order = 1) THEN
4561 -- Check if the draft is on the current amendment
4562 IF (l_source_header_id = p_auc_header_id) THEN
4563
4564 -- If it is a pre-release 12 draft, need to insert missing lines
4565 -- NOTE: expand_draft is batched
4566 IF (l_rel12_draft <> 'Y') THEN
4567 expand_draft(x_prev_bid_number, x_rebid_flag);
4568 END IF;
4569
4570 -- Set return bid number. No defaulting required
4571 x_bid_number := x_prev_bid_number;
4572 x_prev_bid_number := NULL;
4573 x_return_status := 0;
4574 x_return_code := 'DRAFT';
4575
4576 -- draft is on a previous amendment/round
4577 ELSE
4578 -- Need to archive the previous amend/round DRAFT bid
4579 UPDATE pon_bid_headers
4580 SET bid_status = 'ARCHIVED_DRAFT',
4581 last_update_date = sysdate
4582 WHERE bid_number = x_prev_bid_number;
4583
4584 -- If pre-release 12, call handle_proxy to update price, limit_price
4585 -- set has_bid_flag
4586 IF (l_rel12_draft <> 'Y') THEN
4587
4588 handle_proxy
4589 (p_auc_header_id,
4590 x_prev_bid_number,
4591 p_tpid,
4592 p_tpcid,
4593 p_vensid,
4594 ---- Supplier Management: Supplier Evaluation ----
4595 p_evaluator_id,
4596 p_eval_flag,
4597 --------------------------------------------------
4598 l_ignored_return,
4599 x_rebid_flag);
4600
4601 populate_has_bid_flag(p_auc_header_id, x_prev_bid_number);
4602 END IF;
4603
4604 -- All flags are set; indicate defaulting is necessary
4605 x_return_status := 0;
4606 x_return_code := 'DEFAULT';
4607 END IF;
4608
4609 -- Begin Supplier Management: Bug 12369949
4610 ELSIF (p_eval_flag = 'Y' AND l_status_order = 2) THEN
4611
4612 IF (l_source_header_id = p_auc_header_id) THEN
4613
4614 -- Update Evaluation flow
4615
4616 UPDATE pon_bid_headers
4617 SET bid_status = 'DRAFT',
4618 last_update_date = sysdate
4619 WHERE bid_number = x_prev_bid_number;
4620
4621 x_bid_number := x_prev_bid_number;
4622 x_prev_bid_number := NULL;
4623 x_return_status := 0;
4624 x_return_code := 'DRAFT';
4625 ELSE
4626 x_return_status := 0;
4627 x_return_code := 'DEFAULT';
4628 END IF;
4629
4630 -- End Supplier Management: Bug 12369949
4631
4632 -- had a backing ACTIVE, RESUBMISSION, or DISQUALIFIED bid
4633 ELSE
4634 -- All flags are set; indicate that defaulting is necessary
4635 x_return_status := 0;
4636 x_return_code := 'DEFAULT';
4637 END IF;
4638 -- Begin Supplier Management: Supplier Evaluation
4639 ELSIF (p_eval_flag = 'Y') THEN
4640 x_return_status := 0;
4641 x_return_code := 'CREATE_NEW_DRAFT';
4642 -- End Supplier Management: Supplier Evaluation
4643 ELSE
4644
4645 -- Retrieve other users bid info from the cursor
4646 -- We only need the first bid
4647 OPEN other_users_bids;
4648 FETCH other_users_bids
4649 INTO x_return_status, x_return_code;
4650 CLOSE other_users_bids;
4651
4652 IF (x_return_status = 1) THEN
4653 RETURN;
4654 END IF;
4655
4656 -- for the bug 13984944
4657 -- add NVL condition to org_id & contract_status columns
4658 SELECT Count(*)
4659 INTO l_count_org
4660 FROM pon_contracts
4661 WHERE NVL(org_id,-1) = l_org_id ;
4662
4663 -- if terms and conditions not defined for the current operating unit set l_org_id to global.
4664 IF(l_count_org = 0) THEN l_org_id := -1;
4665 ELSE
4666 -- get the contract status for the current Operating Unit.
4667 SELECT NVL(contract_status,'ACTIVE')
4668 INTO l_org_contract_status
4669 FROM pon_contracts
4670 WHERE NVL(org_id,-1) = l_org_id
4671 AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
4672
4673 -- If the status is not Active set l_org_id global
4674 IF(l_org_contract_status <> 'ACTIVE') THEN
4675 l_org_id := -1;
4676 END IF;
4677 END IF;
4678
4679 -- check for any rows existance in pon_contracts table for l_org_id
4680
4681 SELECT Count(*)
4682 INTO l_count_org
4683 FROM pon_contracts
4684 WHERE NVL(org_id,-1) = l_org_id ;
4685
4686 IF(l_count_org = 0) THEN l_enabled_flag := 'N';
4687 ELSE
4688 -- Check the enbled_flag for the Max Version Num of l_org_id
4689 SELECT enabled_flag
4690 INTO l_enabled_flag
4691 FROM pon_contracts pc
4692 WHERE NVL(org_id,-1) = l_org_id
4693 AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
4694 END IF;
4695
4696 -- Creating fresh bid.
4697 -- User must accept terms and conditions if contracts installed.
4698 IF ((l_enabled_flag = 'Y' OR l_enabled_flag = 'X') AND (p_eval_flag='Y' OR is_accepted_terms_cond(p_auc_header_id,l_orig_amend_id,p_tpid,p_tpcid)='N')) THEN
4699 x_return_status := 1;
4700 x_return_code := 'TO_TERMS_COND';
4701 ELSE
4702 x_return_status := 0;
4703 x_return_code := 'CREATE_NEW_DRAFT';
4704 END IF;
4705 END IF;
4706
4707 END get_source_bid;
4708
4709 -- ======================================================================
4710 -- PROCEDURE: CREATE_DEFAULTED_BID PUBLIC
4711 -- PARAMETERS:
4712 -- p_new_header_id IN auction header id of negotiation
4713 -- p_source_bid IN the bid to default from
4714 -- x_bid_number OUT bid number of draft loaded or created
4715 -- x_return_status OUT 0 for success, 1 for error
4716 -- x_return_code OUT returned error code, or SUCCESS
4717 --
4718 -- COMMENT: create a new draft on p_auc_header_id, defaulting from
4719 -- p_source_bid
4720 -- ======================================================================
4721 PROCEDURE create_defaulted_draft
4722 (
4723 p_new_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
4724 p_source_bid IN pon_bid_headers.bid_number%TYPE,
4725 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
4726 ) IS
4727 l_tpid pon_bid_headers.trading_partner_id%TYPE;
4728 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
4729 l_tpname pon_bid_headers.trading_partner_name%TYPE;
4730 l_tpcname pon_bid_headers.trading_partner_contact_name%TYPE;
4731 l_userid pon_bid_headers.created_by%TYPE;
4732 l_venid pon_bid_headers.vendor_id%TYPE;
4733 l_vensid pon_bid_headers.vendor_site_id%TYPE;
4734 l_venscode pon_bid_headers.vendor_site_code%TYPE;
4735 l_buyer_user VARCHAR2(1);
4736 l_auctpid pon_bid_headers.surrog_bid_created_tp_id%TYPE;
4737 l_auctpcid pon_bid_headers.surrog_bid_created_contact_id%TYPE;
4738
4739 ---------- Supplier Management: Supplier Evaluation ----------
4740 l_evaluator_id pon_bid_headers.evaluator_id%TYPE;
4741 l_eval_flag pon_bid_headers.evaluation_flag%TYPE;
4742 --------------------------------------------------------------
4743
4744 l_rebid_flag VARCHAR2(1);
4745 l_prev_bid_disq VARCHAR2(1);
4746 l_prev_bid_wthd VARCHAR2(1);
4747 l_new_round_or_amended VARCHAR2(1);
4748 l_return_status NUMBER;
4749 l_return_code VARCHAR2(30);
4750 BEGIN
4751
4752 -- The bid the user is currently working with has been obsoleted
4753 -- so create a new draft, defaulting from the previous bid.
4754 -- Eg. If the user clicks bid by spreadsheet from the manage draft
4755 -- responses page, but a new amendment has been created.
4756
4757 l_rebid_flag := 'N';
4758 l_prev_bid_disq := 'N';
4759 l_prev_bid_wthd := 'N';
4760
4761 -- The following flag is needed for payments copy
4762 -- This sets to y if defaulting is happening because the
4763 -- negotiation being amended or new round started
4764 l_new_round_or_amended := 'Y';
4765
4766 -- Select out the header values from the previous draft
4767 SELECT bh.trading_partner_id,
4768 bh.trading_partner_contact_id,
4769 bh.trading_partner_name,
4770 bh.trading_partner_contact_name,
4771 bh.created_by,
4772 bh.vendor_id,
4773 bh.vendor_site_id,
4774 bh.vendor_site_code,
4775 bh.surrog_bid_created_tp_id,
4776 bh.surrog_bid_created_contact_id,
4777 bh.surrog_bid_flag,
4778 ---- Supplier Management: Supplier Evaluation ----
4779 bh.evaluator_id,
4780 bh.evaluation_flag
4781 --------------------------------------------------
4782 INTO l_tpid,
4783 l_tpcid,
4784 l_tpname,
4785 l_tpcname,
4786 l_userid,
4787 l_venid,
4788 l_vensid,
4789 l_venscode,
4790 l_auctpid,
4791 l_auctpcid,
4792 l_buyer_user,
4793 ---- Supplier Management: Supplier Evaluation ----
4794 l_evaluator_id,
4795 l_eval_flag
4796 --------------------------------------------------
4797 FROM pon_bid_headers bh
4798 WHERE bh.bid_number = p_source_bid;
4799
4800 -- Create the new bid
4801 create_new_draft_bid
4802 (p_new_header_id,
4803 p_source_bid,
4804 l_tpid,
4805 l_tpcid,
4806 l_tpname,
4807 l_tpcname,
4808 l_userid,
4809 l_venid,
4810 l_vensid,
4811 l_venscode,
4812 l_auctpid,
4813 l_auctpcid,
4814 l_buyer_user,
4815 ---- Supplier Management: Supplier Evaluation ----
4816 l_evaluator_id,
4817 l_eval_flag,
4818 --------------------------------------------------
4819 l_new_round_or_amended,
4820 l_rebid_flag,
4821 l_prev_bid_disq,
4822 l_prev_bid_wthd,
4823 x_bid_number,
4824 l_return_status,
4825 l_return_code);
4826
4827 -- Update the status of the previous bid
4828 -- NOTE: if this procedure is used for cases other than spreadsheet upload
4829 -- on a DRAFT after an amendment, the status will need to be set correctly
4830 UPDATE pon_bid_headers bh
4831 SET bh.bid_status = decode('DRAFT', 'ARCHIVED_DRAFT', bh.bid_status)
4832 WHERE bh.bid_number = p_source_bid;
4833
4834 END create_defaulted_draft;
4835
4836 -- ======================================================================
4837 -- PROCEDURE: CHECK_AND_LOAD_BID PUBLIC
4838 -- PARAMETERS:
4839 -- p_auc_header_id IN auction header id of negotiation
4840 -- p_draft_number IN non-null if a specific draft is to be loaded
4841 -- or if the action code is CREATE_NEW_AMEND_DRAFT
4842 -- p_tpid IN trading partner id of supplier
4843 -- p_tpcid IN trading partner contact id of supplier
4844 -- p_tpname IN trading partner name of supplier
4845 -- p_tpcname IN trading partner contact name of supplier
4846 -- p_userid IN userid of bid creator
4847 -- p_venid IN vendor id
4848 -- p_vensid IN vendor site to place a bid for
4849 -- p_venscode IN corresponding vendor site code
4850 -- p_buyer_user IN determines if surrogate bid
4851 -- p_auctpid IN trading partner id of buyer if surrogate bid
4852 -- p_auctpcid IN trading partner contact id of buyer if surrogate bid
4853
4854 -- p_evaluator_id IN evaluator user id
4855 -- p_eval_flag IN flag indicating if the response is an evaluation
4856
4857 -- x_bid_number OUT bid number of draft loaded or created
4858 -- x_rebid_flag OUT flag determining if rebid or not
4859 -- x_prev_bid_number OUT source bid number
4860 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
4861 -- x_round_bid_def OUT Y if source bid is on a previous round
4862 -- x_prev_bid_disq OUT Y if source bid was disqualified
4863 -- x_prev_bid_wthd OUT Y if source bid was Withdrawn
4864 -- x_withdraw_reason source bid Withdraw Reason
4865 -- x_edit_draft OUT Y if we loaded an existing draft
4866
4867 -- p_action_code IN determine if a special action needs to be taken
4868 -- x_return_status OUT 0 for success, 1 for error
4869 -- x_return_code OUT returned error code, or SUCCESS
4870 --
4871 -- COMMENT: Main procedure which determines whether a new or defaulted bid
4872 -- is to be created. Or whether a draft already exists
4873 -- ======================================================================
4874 PROCEDURE check_and_load_bid
4875 (
4876 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
4877 p_draft_number IN pon_bid_headers.bid_number%TYPE,
4878 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
4879 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
4880 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
4881 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
4882 p_userid IN pon_bid_headers.created_by%TYPE,
4883 p_venid IN pon_bid_headers.vendor_id%TYPE,
4884 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
4885 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
4886 p_buyer_user IN VARCHAR2,
4887 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
4888 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
4889
4890 ----------- Supplier Management: Supplier Evaluation -----------
4891 p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
4892 p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
4893 ----------------------------------------------------------------
4894
4895 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4896 x_rebid_flag OUT NOCOPY VARCHAR2,
4897 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4898 x_amend_bid_def OUT NOCOPY VARCHAR2,
4899 x_round_bid_def OUT NOCOPY VARCHAR2,
4900 x_prev_bid_disq OUT NOCOPY VARCHAR2,
4901 x_prev_bid_wthd OUT NOCOPY VARCHAR2,
4902 x_withdraw_reason OUT NOCOPY VARCHAR2,
4903 x_edit_draft OUT NOCOPY VARCHAR2,
4904
4905 p_action_code IN VARCHAR2,
4906 x_return_status OUT NOCOPY NUMBER,
4907 x_return_code OUT NOCOPY VARCHAR2
4908 ) IS
4909 l_rel12_draft pon_bid_headers.rel12_draft_flag%TYPE;
4910 l_new_round_or_amended VARCHAR2(1);
4911
4912 l_temp_num NUMBER;
4913 l_orig_amend_id pon_auction_headers_all.auction_header_id_orig_amend%type;
4914 L_SURROGATE_BID_FLAG VARCHAR2(1);
4915 l_eval_flag pon_bid_headers.evaluation_flag%TYPE := nvl(p_eval_flag,'N');
4916 BEGIN
4917
4918 -- The user indicated that they want to create a fresh bid
4919 IF (p_action_code = 'CREATE_NEW_DRAFT') THEN
4920
4921 x_rebid_flag := 'N';
4922 x_prev_bid_number := NULL;
4923 x_amend_bid_def := 'N';
4924 x_round_bid_def := 'N';
4925 x_prev_bid_disq := 'N';
4926 x_prev_bid_wthd := 'N';
4927 x_edit_draft := 'N';
4928
4929 -- The following flag is needed for payments copy
4930 -- This sets to y if defaulting is happening because the
4931 -- negotiation being amended or new round started
4932 l_new_round_or_amended := 'N';
4933
4934 -- Create the new bid
4935 create_new_draft_bid
4936 (p_auc_header_id,
4937 x_prev_bid_number,
4938 p_tpid,
4939 p_tpcid,
4940 p_tpname,
4941 p_tpcname,
4942 p_userid,
4943 p_venid,
4944 p_vensid,
4945 p_venscode,
4946 p_auctpid,
4947 p_auctpcid,
4948 p_buyer_user,
4949 ---- Supplier Management: Supplier Evaluation ----
4950 p_evaluator_id,
4951 l_eval_flag,
4952 --------------------------------------------------
4953 l_new_round_or_amended,
4954 x_rebid_flag,
4955 x_prev_bid_disq,
4956 x_prev_bid_wthd,
4957 x_bid_number,
4958 x_return_status,
4959 x_return_code);
4960
4961 RETURN;
4962
4963 -- If we already know which draft to work with
4964 ELSIF (p_draft_number IS NOT null AND p_draft_number > 0) THEN
4965
4966 -- Check that the draft is not locked by another user and lock it
4967 IF (p_buyer_user = 'Y') THEN
4968 lock_draft
4969 (p_draft_number,
4970 p_auctpid,
4971 p_auctpcid,
4972 x_return_status,
4973 x_return_code);
4974 -- Begin Supplier Management: Supplier Evaluation
4975 ELSIF (l_eval_flag = 'Y') THEN
4976 lock_draft
4977 (p_draft_number,
4978 p_tpid,
4979 p_evaluator_id,
4980 x_return_status,
4981 x_return_code);
4982 -- End Supplier Management: Supplier Evaluation
4983 ELSE
4984 lock_draft
4985 (p_draft_number,
4986 p_tpid,
4987 p_tpcid,
4988 x_return_status,
4989 x_return_code);
4990 END IF;
4991
4992 IF (x_return_status = 1) THEN
4993 RETURN;
4994 END IF;
4995
4996 -- If it is a pre-release 12 draft, we need to expand it
4997 SELECT nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag
4998 INTO l_rel12_draft
4999 FROM pon_bid_headers bh
5000 WHERE bh.bid_number = p_draft_number;
5001
5002 -- If the draft is pre-release 12 we need to fill in the missing lines
5003 IF (l_rel12_draft <> 'Y') THEN
5004 expand_draft(p_draft_number, x_rebid_flag);
5005 END IF;
5006
5007 -- Finally, handle proxy bidding and copy rank
5008 handle_proxy
5009 (p_auc_header_id,
5010 p_draft_number,
5011 p_tpid,
5012 p_tpcid,
5013 p_vensid,
5014 ---- Supplier Management: Supplier Evaluation ----
5015 p_evaluator_id,
5016 l_eval_flag,
5017 --------------------------------------------------
5018 x_prev_bid_number,
5019 x_rebid_flag);
5020
5021 -- set flags and return values before returning
5022 x_bid_number := p_draft_number;
5023 x_amend_bid_def := 'N';
5024 x_round_bid_def := 'N';
5025 x_prev_bid_disq := 'N';
5026 x_edit_draft := 'Y';
5027 x_return_status := 0;
5028 x_return_code := 'SUCCESS';
5029 RETURN;
5030 END IF;
5031
5032 -- We are unsure whether a draft exists or we're creating a new bid
5033
5034 -- Check if all amendments have been acknowledged
5035 -- Bug 10027124 - only do the check if it's not evaluation
5036 IF (l_eval_flag = 'N') THEN
5037
5038 -- Supplier Management: Bug 10378806
5039 --
5040 -- The trading_partner_contact_id is not applicable for
5041 -- prospective supplier, and it is populated with the negated
5042 -- trading_partner_id.
5043
5044 IF (p_venid = -1) THEN
5045 check_amendments_acked(p_auc_header_id, p_tpid, -p_tpid,
5046 x_return_status, x_return_code);
5047 ELSE
5048 check_amendments_acked(p_auc_header_id, p_tpid, p_tpcid,
5049 x_return_status, x_return_code);
5050 END IF;
5051 END IF;
5052
5053 IF (x_return_status = 1) THEN
5054 RETURN;
5055 END IF;
5056
5057 -- Get the source bid number and other flags
5058 get_source_bid
5059 (p_auc_header_id,
5060 p_tpid,
5061 p_tpcid,
5062 p_vensid,
5063 ---- Supplier Management: Supplier Evaluation ----
5064 p_evaluator_id,
5065 l_eval_flag,
5066 --------------------------------------------------
5067 p_action_code,
5068 p_buyer_user,
5069 x_rebid_flag,
5070 x_bid_number,
5071 x_prev_bid_number,
5072 x_amend_bid_def,
5073 x_round_bid_def,
5074 x_prev_bid_disq,
5075 x_prev_bid_wthd,
5076 x_withdraw_reason,
5077 x_return_status,
5078 x_return_code);
5079
5080 /* Offer Enhancement Changes Start */
5081
5082 IF x_bid_number IS NOT NULL AND x_bid_number>0
5083 THEN
5084
5085 BEGIN
5086 SELECT SURROG_BID_FLAG INTO L_SURROGATE_BID_FLAG
5087 FROM
5088 PON_BID_HEADERS BH
5089 WHERE
5090 bh.auction_header_id = p_auc_header_id
5091 AND BH.BID_NUMBER = x_bid_number
5092 AND bh.trading_partner_id = p_tpid
5093 AND bh.trading_partner_contact_id = p_tpcid
5094 AND bh.bid_status='DRAFT'
5095 AND nvl(bh.evaluation_flag, 'N') = 'N';
5096
5097 IF (p_buyer_user = 'Y') THEN
5098 IF (Nvl(L_SURROGATE_BID_FLAG,'Y')='N') THEN
5099 x_bid_number:=NULL;
5100 x_return_code:='CREATE_NEW_DRAFT';
5101 END IF;
5102 ELSE
5103 IF (Nvl(L_SURROGATE_BID_FLAG,'Y')='Y') THEN
5104 x_bid_number:=NULL;
5105 x_return_code:='CREATE_NEW_DRAFT';
5106 END IF;
5107 END IF;
5108
5109 EXCEPTION
5110 WHEN NO_DATA_FOUND THEN
5111 L_SURROGATE_BID_FLAG:=NULL;
5112 END;
5113
5114 END IF;
5115
5116 /* Offer Enhancement Changes End */
5117
5118 IF (x_return_status = 1) THEN
5119 RETURN;
5120 END IF;
5121
5122 -- If a draft exists, check that it is not locked by another user and lock it
5123 IF (x_return_code = 'DRAFT') THEN
5124 IF (p_buyer_user = 'Y') THEN
5125 lock_draft(x_bid_number, p_auctpid, p_auctpcid,
5126 x_return_status, x_return_code);
5127 -- Begin Supplier Management: Supplier Evaluation
5128 ELSIF (l_eval_flag = 'Y') THEN
5129 lock_draft(x_bid_number, p_tpid, p_evaluator_id,
5130 x_return_status, x_return_code);
5131 -- End Supplier Management: Supplier Evaluation
5132 ELSE
5133 lock_draft(x_bid_number, p_tpid, p_tpcid,
5134 x_return_status, x_return_code);
5135 END IF;
5136
5137 IF (x_return_status = 1) THEN
5138 RETURN;
5139 END IF;
5140
5141 -- Begin Supplier Management: Bug 12369949
5142 IF (l_eval_flag = 'Y') THEN
5143 DELETE FROM pon_mng_eval_bid_sections
5144 WHERE bid_number = x_bid_number
5145 AND status_code = 'A';
5146 END IF;
5147 -- End Supplier Management: Bug 12369949
5148
5149 -- set return status
5150 x_edit_draft := 'Y';
5151 x_return_status := 0;
5152 x_return_code := 'SUCCESS';
5153
5154 -- Default the bid if necessary
5155 ELSIF (x_return_code = 'DEFAULT') THEN
5156
5157 -- The following flag is needed for payments copy
5158 -- This sets to y if defaulting is happening because the
5159 -- negotiation being amended or new round started
5160
5161 IF (x_amend_bid_def = 'Y' OR x_round_bid_def = 'Y') THEN
5162 l_new_round_or_amended := 'Y';
5163 ELSE
5164 l_new_round_or_amended := 'N';
5165 END IF;
5166
5167 create_new_draft_bid
5168 (p_auc_header_id,
5169 x_prev_bid_number,
5170 p_tpid,
5171 p_tpcid,
5172 p_tpname,
5173 p_tpcname,
5174 p_userid,
5175 p_venid,
5176 p_vensid,
5177 p_venscode,
5178 p_auctpid,
5179 p_auctpcid,
5180 p_buyer_user,
5181 ---- Supplier Management: Supplier Evaluation ----
5182 p_evaluator_id,
5183 l_eval_flag,
5184 --------------------------------------------------
5185 l_new_round_or_amended,
5186 x_rebid_flag,
5187 x_prev_bid_disq,
5188 x_prev_bid_wthd,
5189 x_bid_number,
5190 x_return_status,
5191 x_return_code);
5192
5193 -- set return status
5194 x_edit_draft := 'N';
5195
5196 -- There is already a draft created for this user. Maybe
5197 -- through a different session. In this case, return
5198 -- error MULTIPLE_REBID
5199 IF (x_return_status = 1) THEN
5200 RETURN;
5201 END IF;
5202
5203 -- Create a fresh bid if necessary
5204 ELSIF (x_return_code = 'CREATE_NEW_DRAFT') THEN
5205
5206 x_rebid_flag := 'N';
5207 x_prev_bid_number := NULL;
5208 x_amend_bid_def := 'N';
5209 x_round_bid_def := 'N';
5210 x_prev_bid_disq := 'N';
5211 x_prev_bid_wthd := 'N';
5212 x_edit_draft := 'N';
5213
5214 -- The following flag is needed for payments copy
5215 -- This sets to y if defaulting is happening because the
5216 -- negotiation being amended or new round started
5217 l_new_round_or_amended := 'N';
5218
5219 -- Create the new bid
5220 create_new_draft_bid
5221 (p_auc_header_id,
5222 x_prev_bid_number,
5223 p_tpid,
5224 p_tpcid,
5225 p_tpname,
5226 p_tpcname,
5227 p_userid,
5228 p_venid,
5229 p_vensid,
5230 p_venscode,
5231 p_auctpid,
5232 p_auctpcid,
5233 p_buyer_user,
5234 ---- Supplier Management: Supplier Evaluation ----
5235 p_evaluator_id,
5236 l_eval_flag,
5237 --------------------------------------------------
5238 l_new_round_or_amended,
5239 x_rebid_flag,
5240 x_prev_bid_disq,
5241 x_prev_bid_wthd,
5242 x_bid_number,
5243 x_return_status,
5244 x_return_code);
5245 RETURN;
5246
5247 END IF;
5248
5249 -- Finally, handle proxy bidding and copy rank
5250 handle_proxy
5251 (p_auc_header_id,
5252 x_bid_number,
5253 p_tpid,
5254 p_tpcid,
5255 p_vensid,
5256 ---- Supplier Management: Supplier Evaluation ----
5257 p_evaluator_id,
5258 l_eval_flag,
5259 --------------------------------------------------
5260 l_temp_num,
5261 x_rebid_flag);
5262
5263 -- We get the returned bid_number into l_temp_num because it will null
5264 -- out a x_prev_bid_number if it is not the rebidding case
5265 IF (x_prev_bid_number IS null) THEN
5266 x_prev_bid_number := l_temp_num;
5267 END IF;
5268
5269 END check_and_load_bid;
5270
5271 -- ======================================================================
5272 -- FUNCTION: GET_SOURCE_BID_FOR_SPREADSHEET
5273 -- PARAMETERS:
5274 -- p_auc_header_id IN auction header id of negotiation
5275 -- p_prev_round_auc_header_id IN auction header id of prev round negotiation
5276 -- p_tpid IN trading partner id of supplier
5277 -- p_tpcid IN trading partner contact id of supplier
5278 -- p_auc_header_id_orig_amend IN auction header id of original amendment
5279 -- p_amendment_number IN amendment number
5280 -- p_vensid IN vendor site to place a bid for
5281 --
5282 -- COMMENT: This function is only used in spreadsheet export case.
5283 -- Determine whether there are any bids existing for the current amendment.
5284 -- If not, determines whether there are any bids in previous amendment
5285 -- of current round; If still not, check whether there is an active bid
5286 -- from previous round
5287 -- ======================================================================
5288 FUNCTION get_source_bid_for_spreadsheet
5289 (
5290 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
5291 p_prev_round_auc_header_id IN pon_auction_headers_all.auction_header_id_prev_round%TYPE,
5292 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
5293 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
5294 p_auc_header_id_orig_amend IN pon_auction_headers_all.auction_header_id_orig_amend%TYPE,
5295 p_amendment_number IN pon_auction_headers_all.amendment_number%TYPE,
5296 p_vensid IN pon_bid_headers.vendor_site_id%TYPE
5297
5298 ) RETURN NUMBER IS
5299
5300 CURSOR current_amendment_bids IS
5301 select bid_number
5302 from pon_bid_headers
5303 where auction_header_id = p_auc_header_id
5304 and trading_partner_id = p_tpid
5305 and trading_partner_contact_id = p_tpcid
5306 and nvl(vendor_site_id, -1) = nvl(p_vensid, -1)
5307 and bid_status in ('DRAFT', 'ACTIVE', 'DISQUALIFIED')
5308 order by decode(bid_status, 'DRAFT', 3,
5309 'ACTIVE', 2,
5310 'DISQUALIFIED', 1) desc, publish_date desc;
5311
5312 CURSOR previous_amendments_bids IS
5313 select bh.bid_number
5314 from
5315 pon_bid_headers bh,
5316 pon_auction_headers_all ah
5317 where
5318 bh.auction_header_id = ah.auction_header_id
5319 and ah.auction_header_id_orig_amend = p_auc_header_id_orig_amend
5320 and bh.trading_partner_id = p_tpid
5321 and bh.trading_partner_contact_id = p_tpcid
5322 and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1)
5323 and bh.bid_status in ('DRAFT', 'RESUBMISSION', 'DISQUALIFIED')
5324 order by ah.amendment_number desc,
5325 decode(bh.bid_status, 'DRAFT', 3,
5326 'RESUBMISSION' , 2,
5327 'DISQUALIFIED', 1) desc,
5328 bh.publish_date desc;
5329
5330 x_prev_bid_number NUMBER := -1;
5331
5332 BEGIN
5333
5334 OPEN current_amendment_bids;
5335 FETCH current_amendment_bids into x_prev_bid_number;
5336
5337 IF (current_amendment_bids%NOTFOUND) THEN
5338
5339 -- try to find a previous bid from previous amendments in the current round
5340 IF (p_amendment_number is not null AND p_amendment_number >=1) THEN
5341
5342 OPEN previous_amendments_bids;
5343 FETCH previous_amendments_bids into x_prev_bid_number;
5344 IF (previous_amendments_bids%NOTFOUND) THEN
5345 x_prev_bid_number := -1;
5346 END IF;
5347 CLOSE previous_amendments_bids;
5348
5349 ELSIF (p_prev_round_auc_header_id is not null) THEN
5350
5351 -- try to find an active bid from previous round
5352
5353 select max(bid_number) prev_round_bid
5354 into x_prev_bid_number
5355 from pon_bid_headers bh,
5356 pon_auction_headers_all ah,
5357 pon_auction_headers_all ah2
5358 where bh.auction_header_id = ah.auction_header_id
5359 and ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
5360 and ah2.auction_header_id = p_prev_round_auc_header_id
5361 and bh.trading_partner_id = p_tpid
5362 and bh.trading_partner_contact_id = p_tpcid
5363 and bh.bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
5364 and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1);
5365
5366 END IF;
5367 END IF;
5368
5369 CLOSE current_amendment_bids;
5370
5371 IF (x_prev_bid_number is null) THEN
5372 x_prev_bid_number := -1;
5373 END IF;
5374
5375 return x_prev_bid_number;
5376
5377 END GET_SOURCE_BID_FOR_SPREADSHEET;
5378
5379 --------------------------------------------------------------------------------
5380 -- can_supplier_create_payments --
5381 --------------------------------------------------------------------------------
5382 -- Start of Comments
5383 --
5384 -- API Name: can_supplier_create_payments
5385 --
5386 -- Type : Private
5387 --
5388 -- Pre-reqs: None
5389 --
5390 -- Function: This API is called by the Response Import Spreadsheet page.
5391 -- It determines if there are any lines in the RFQ that can have payments.
5392 -- If yes, then the "Pay Items" will be one of the option in the Import
5393 -- and Export poplists
5394 --
5395 --
5396 -- Parameters:
5397 --
5398 -- p_auction_header_id IN NUMBER
5399 -- Auction header id - required
5400 -- p_bid_number IN NUMBER
5401 -- Bid Number - required
5402 -- p_po_style_id IN NUMBER
5403 -- PO Style Id - required
5404 --
5405 --
5406 -- x_can_create_payments OUT VARCHAR2
5407 -- Returns Y if payments can be created for atleast one of the
5408 -- line to which supplier has access. Otherwise Returns N
5409 --
5410 --
5411 -- End of Comments
5412 --------------------------------------------------------------------------------
5413 -----
5414 PROCEDURE can_supplier_create_payments(
5415 p_auction_header_id IN NUMBER,
5416 p_bid_number IN NUMBER,
5417 x_can_create_payments OUT NOCOPY VARCHAR2) IS
5418 BEGIN
5419 x_can_create_payments := 'N';
5420
5421 -- Check if there are any lines OTHER THAN GROUP, LOT_LINE and to which supplier does not have access
5422 -- If there are lines then l_can_create_payment = 'Y'
5423 -- Else l_can_create_payment = 'N';
5424
5425 SELECT 'Y'
5426 INTO x_can_create_payments
5427 FROM dual
5428 WHERE EXISTS (SELECT 1
5429 FROM PON_AUCTION_ITEM_PRICES_ALL pai,
5430 PON_BID_ITEM_PRICES pbi,
5431 PON_AUCTION_HEADERS_ALL pah
5432 WHERE pai.auction_header_id = p_auction_header_id
5433 AND pai.group_type NOT IN ('GROUP','LOT_LINE')
5434 AND pbi.auction_header_id = pai.auction_header_id
5435 AND pbi.line_number = pai.line_number
5436 AND pbi.bid_number = p_bid_number
5437 AND pah.auction_header_id = pai.auction_header_id
5438 AND pah.progress_payment_type <> 'NONE'
5439 AND pah.contract_type = 'STANDARD');
5440 EXCEPTION
5441 WHEN NO_DATA_FOUND
5442 THEN
5443 x_can_create_payments := 'N';
5444 WHEN OTHERS THEN
5445 RAISE;
5446 END can_supplier_create_payments;
5447
5448 --------------------------------------------------------------------------------
5449 -- apply_price_factors --
5450 --------------------------------------------------------------------------------
5451 /**
5452 * This function calculates the total price on a line including the
5453 * buyer and the supplier price factors in auction currency.
5454 *
5455 * This function will be used in view objects to display supplier's
5456 * previous round price as the start price for this line instead of the
5457 * auction line start price.
5458 *
5459 * This is as per Cendant requirement to enforce upon suppliers to
5460 * bid lower than their bid on the previous round of the negotiation
5461 *
5462 * Currently anticipated usage of this function are on View Bid Page
5463 * (ViewBidItemsVO), Negotiation Summary page (AuctionItemPricesAllVO)
5464 * and bid creation page (ResponseAMImpl)
5465 *
5466 * p_auction_header_id - current round auction header id
5467 * p_prev_auc_active_bid_number - bid number on the previous round
5468 * p_line_number - current line number
5469 * p_unit_price - bid line price in auction currency
5470 * p_quantity - bid quantity for the current line
5471 */
5472 FUNCTION apply_price_factors(p_auction_header_id IN NUMBER,
5473 p_prev_auc_active_bid_number IN NUMBER,
5474 p_line_number IN NUMBER,
5475 p_unit_price IN NUMBER,
5476 p_quantity IN NUMBER
5477 )
5478 RETURN NUMBER IS
5479
5480 l_api_name CONSTANT VARCHAR2(30) := 'apply_price_factors';
5481 l_progress VARCHAR2(100) := '0';
5482
5483 l_total_price NUMBER;
5484 l_bid_line_pf_unit_price NUMBER;
5485 l_auc_pf_unit_price NUMBER;
5486
5487 l_contract_type pon_auction_headers_all.contract_type%TYPE;
5488 l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
5489
5490 l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
5491 l_bid_quantity pon_bid_item_prices.quantity%TYPE;
5492
5493 l_is_spo_transformed VARCHAR2(1);
5494
5495 BEGIN
5496
5497 -- auction information that we need
5498
5499 l_progress := '10: fetch auction information';
5500
5501 SELECT contract_type,
5502 supplier_view_type
5503 INTO l_contract_type,
5504 l_supplier_view_type
5505 FROM pon_auction_headers_all
5506 WHERE auction_header_id = p_auction_header_id;
5507
5508
5509
5510 l_progress := '20: perform SPO/TRANSFORMED check';
5511
5512 IF (l_supplier_view_type = 'TRANSFORMED' AND
5513 l_contract_type = 'STANDARD') THEN
5514 l_is_spo_transformed := 'Y';
5515 ELSE
5516 l_is_spo_transformed := 'N';
5517 END IF;
5518
5519 -- calculate the buyer price factors
5520
5521 l_progress := '30: calculate unit price plus buyer price factors';
5522
5523 BEGIN
5524
5525 SELECT (p_unit_price * ppsf.percentage) +
5526 ppsf.unit_price +
5527 ppsf.fixed_amount/decode(l_is_spo_transformed,
5528 'Y', nvl(p_quantity, 1),
5529 nvl(aip.quantity, 1)
5530 )
5531 INTO l_auc_pf_unit_price
5532 FROM pon_pf_supplier_formula ppsf,
5533 pon_auction_item_prices_all aip,
5534 pon_bid_headers pbh
5535 WHERE ppsf.auction_header_id = p_auction_header_id
5536 AND ppsf.line_number = p_line_number
5537 AND ppsf.trading_partner_id = pbh.trading_partner_id
5538 AND ppsf.vendor_site_id = pbh.vendor_site_id
5539 AND pbh.bid_number = p_prev_auc_active_bid_number
5540 AND aip.auction_header_id = ppsf.auction_header_id
5541 AND aip.line_number = ppsf.line_number;
5542
5543 EXCEPTION
5544
5545 WHEN NO_DATA_FOUND THEN
5546 l_auc_pf_unit_price := p_unit_price;
5547
5548 END;
5549
5550 -- calculate the supplier price factors
5551
5552 l_progress := '40: calculate supplier price factors';
5553
5554 SELECT nvl(sum(decode(spf.pricing_basis,
5555 'PER_UNIT', spf.auction_currency_value,
5556 'PERCENTAGE', spf.auction_currency_value/100 * p_unit_price,
5557 (spf.auction_currency_value / decode(l_is_spo_transformed,
5558 'Y', nvl(p_quantity, 1),
5559 nvl(aip.quantity, 1)
5560 )
5561 )
5562 )
5563 )
5564 ,0)
5565 INTO l_bid_line_pf_unit_price
5566 FROM pon_bid_price_elements spf,
5567 pon_auction_item_prices_all aip
5568 WHERE spf.bid_number = p_prev_auc_active_bid_number
5569 AND spf.line_number = p_line_number
5570 AND spf.sequence_number <> -10
5571 AND spf.pf_type = 'SUPPLIER'
5572 AND aip.auction_header_id = spf.auction_header_id
5573 AND aip.line_number = spf.line_number;
5574
5575 -- total price in auction currency
5576 l_progress := '60: return total price in auction currency';
5577 l_total_price := l_bid_line_pf_unit_price + l_auc_pf_unit_price;
5578
5579 RETURN l_total_price;
5580
5581 EXCEPTION
5582
5583 WHEN OTHERS THEN
5584 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)THEN
5585 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
5586 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
5587 fnd_log.string(log_level => fnd_log.level_unexpected
5588 ,module => g_pkg_name||'.'||l_api_name
5589 ,message => l_progress || ': ' || SQLERRM);
5590 fnd_log.string(log_level=>fnd_log.level_unexpected,
5591 module =>g_pkg_name ||'.'||l_api_name,
5592 message => 'Input parameter list: ' );
5593 fnd_log.string(log_level=>fnd_log.level_unexpected,
5594 module =>g_pkg_name ||'.'||l_api_name,
5595 message => 'Auction Header Id = ' || p_auction_header_id);
5596 fnd_log.string(log_level=>fnd_log.level_unexpected,
5597 module =>g_pkg_name ||'.'||l_api_name,
5598 message => 'Previous Round Active Bid Number = ' || p_prev_auc_active_bid_number);
5599 fnd_log.string(log_level=>fnd_log.level_unexpected,
5600 module =>g_pkg_name ||'.'||l_api_name,
5601 message => 'Line Number = ' || p_line_number);
5602
5603 END IF;
5604 END IF;
5605 return NULL;
5606 END apply_price_factors;
5607
5608 /* Bug 10052785 - Start */
5609 /* Added cost constraint to the procedure variables. This variable value
5610 is used to update pon_bid_item_prices table's data. */
5611
5612 PROCEDURE update_bid_line_to_po_line(p_po_line_id IN po_lines_all.po_line_id%TYPE,
5613 p_po_contract_type IN po_lines_all.contract_type%TYPE,
5614 p_po_unit_price IN po_lines_all.unit_price%TYPE,
5615 p_po_clm_idc_type IN po_lines_all.clm_idc_type%TYPE,
5616 p_po_cost_constraint IN po_lines_all.cost_constraint%TYPE,
5617 p_po_quantity IN po_lines_all.cost_constraint%TYPE,
5618 p_po_type IN po_lines_all.order_type_lookup_code%TYPE,
5619 p_bid_number IN NUMBER,
5620 p_auction_header_id IN NUMBER,
5621 p_bid_line_number IN NUMBER) IS
5622
5623 l_bid_curr_unit_price pon_bid_item_prices.bid_currency_unit_price%TYPE;
5624 l_price_prec pon_bid_headers.number_price_decimals%TYPE;
5625 l_curr_prec fnd_currencies.precision%TYPE;
5626 l_rate pon_bid_headers.rate%TYPE;
5627 BEGIN
5628 SELECT bh.number_price_decimals,
5629 cu.precision,
5630 bh.rate
5631 INTO l_price_prec,
5632 l_curr_prec,
5633 l_rate
5634 FROM pon_bid_headers bh,
5635 fnd_currencies cu
5636 WHERE bh.bid_number = p_bid_number
5637 AND cu.currency_code = bh.bid_currency_code;
5638
5639 IF p_po_type = 'QUANTITY' THEN
5640 l_bid_curr_unit_price := round(p_po_unit_price * l_rate, l_price_prec);
5641 ELSE
5642 l_bid_curr_unit_price := round(p_po_quantity * l_rate, l_price_prec);
5643 END IF;
5644
5645 UPDATE pon_bid_item_prices
5646 SET
5647 idv_line_id = p_po_line_id,
5648 clm_contract_type = p_po_contract_type,
5649 price = Decode(p_po_type,'QUANTITY',p_po_unit_price,p_po_quantity),
5650 unit_price = Decode(p_po_type,'QUANTITY',p_po_unit_price,p_po_quantity),
5651 Bid_currency_unit_price = l_bid_curr_unit_price,
5652 bid_currency_price = l_bid_curr_unit_price,
5653 clm_idc_type = p_po_clm_idc_type,
5654 clm_cost_constraint = p_po_cost_constraint,
5655 quantity = NVL((SELECT quantity FROM pon_auction_item_prices_all
5656 WHERE auction_header_id = p_auction_header_id
5657 AND line_number = p_bid_line_number),quantity)
5658 WHERE auction_header_id = p_auction_header_id
5659 AND bid_number = p_bid_number
5660 AND line_number = p_bid_line_number;
5661
5662 END update_bid_line_to_po_line;
5663
5664
5665
5666 /*CLM proceudre to default idv line id*/
5667
5668
5669 /* Bug 10052785 - Start */
5670 /* Fetch cost constraint and pass it to update_bid_line_to_po_line procedure.
5671 This variable value is used to update pon_bid_item_prices table's data.
5672 Update IS_CHANGED_LINE_FLAG to 'Y' whenever IDV reference lines matches with
5673 Offer lines. */
5674
5675 PROCEDURE default_idv_line_id (p_po_header_id IN NUMBER,
5676 p_auction_header_id IN NUMBER,
5677 p_bid_number IN NUMBER)
5678 IS
5679
5680 TYPE bid_info_record_type IS RECORD
5681 (line_number pon_bid_item_prices.line_number%TYPE,
5682 description pon_bid_item_prices.item_description%TYPE,
5683 category_id pon_bid_item_prices.category_id%TYPE,
5684 line_type_id pon_auction_item_prices_all.line_type_id%TYPE);
5685
5686 TYPE po_line_info_record_type IS RECORD
5687 (po_line_id po_lines_all.po_line_id%TYPE,
5688 description po_lines_all.item_description%TYPE,
5689 category_id po_lines_all.category_id%TYPE,
5690 line_type_id po_lines_all.line_type_id%TYPE,
5691 --base_uom po_lines_all.base_uom%TYPE,
5692 contract_type po_lines_all.contract_type%TYPE,
5693 unit_price po_lines_all.unit_price%TYPE,
5694 clm_idc_type po_lines_all.clm_idc_type%TYPE,
5695 cost_constraint po_lines_all.cost_constraint%TYPE,
5696 quantity po_lines_all.quantity%TYPE,
5697 order_type_lookup_code po_lines_all.order_type_lookup_code%TYPE);
5698
5699 TYPE bid_info_table_type is table of bid_info_record_type index by binary_integer;
5700 bid_info_table bid_info_table_type;
5701
5702 TYPE po_line_info_table_type is table of po_line_info_record_type index by binary_integer;
5703 po_line_info_table po_line_info_table_type;
5704
5705 l_return_status VARCHAR2(5);
5706 l_msg_count NUMBER;
5707 l_msg_data VARCHAR2(1000);
5708
5709 BEGIN
5710
5711 select po_line_id,item_description,category_id,line_type_id,
5712 contract_type,unit_price,clm_idc_type,cost_constraint,
5713 quantity, order_type_lookup_code
5714 bulk collect into po_line_info_table
5715 from po_lines_all
5716 where po_header_id = p_po_header_id;
5717
5718 select bid.line_number,bid.item_description,
5719 bid.category_id,auction.line_type_id
5720 bulk collect into bid_info_table
5721 from
5722 pon_bid_item_prices bid,
5723 pon_auction_item_prices_all auction
5724 where bid.bid_number = p_bid_number
5725 and bid.auction_header_id = auction.auction_header_id;
5726
5727 IF po_line_info_table.first > 0 AND bid_info_table.first > 0 THEN
5728
5729 FOR i IN bid_info_table.first..bid_info_table.last
5730 LOOP
5731 FOR j IN po_line_info_table.first..po_line_info_table.last
5732 LOOP
5733
5734 IF (bid_info_table(i).category_id = po_line_info_table(j).category_id
5735 AND bid_info_table(i).description = po_line_info_table(j).description
5736 AND bid_info_table(i).line_type_id = po_line_info_table(j).line_type_id) THEN
5737
5738 update_bid_line_to_po_line(po_line_info_table(j).po_line_id,
5739 po_line_info_table(j).contract_type,
5740 po_line_info_table(j).unit_price,
5741 po_line_info_table(j).clm_idc_type,
5742 po_line_info_table(j).cost_constraint,
5743 po_line_info_table(j).quantity,
5744 po_line_info_table(j).order_type_lookup_code,
5745 p_bid_number,
5746 p_auction_header_id,
5747 bid_info_table(i).line_number);
5748
5749 UPDATE pon_bid_item_prices
5750 SET IS_CHANGED_LINE_FLAG = 'Y',
5751 HAS_USER_BID_FLAG = 'Y'
5752 WHERE auction_header_id = p_auction_header_id
5753 AND bid_number = p_bid_number
5754 AND line_number = bid_info_table(i).line_number
5755 -- ELINs project
5756 AND ((Nvl(USER_MARKED_FLAG,'N') = 'N')
5757 OR (Nvl(USER_MARKED_FLAG,'N') = 'Y' AND Nvl(HAS_USER_BID_FLAG, 'N') = 'Y')) ;
5758
5759 PON_COPY_UDAS_GRP.COPY_PO_LINE_UDA(p_bid_header_id => p_bid_number,
5760 p_bid_line_number => bid_info_table(i).line_number,
5761 p_po_line_id => po_line_info_table(j).po_line_id,
5762 x_return_status => l_return_status,
5763 x_msg_count => l_msg_count,
5764 x_msg_data => l_msg_data);
5765 EXIT;
5766 END IF;
5767 END LOOP;
5768 END LOOP;
5769 END IF;
5770 exception when others then
5771 null;
5772 END default_idv_line_id;
5773
5774 PROCEDURE default_idv_pricing_details(p_source_line_id NUMBER,
5775 p_dest_bid_number NUMBER) IS
5776 BEGIN
5777 null;
5778 END default_idv_pricing_details;
5779
5780 /* Bug 10052785 - Start */
5781 /* Added cost constraint to update_bid_line_to_po_line procedure variables.
5782 This variable value is used to update pon_bid_item_prices table's data. */
5783
5784 /* CLM : This is a wrapper procedure that calls uda copy procedure and
5785 * updates some bid columns for po line.
5786 */
5787 PROCEDURE COPY_PO_LINE_TO_BID_LINE(p_bid_header_id IN NUMBER,
5788 p_bid_line_number IN NUMBER,
5789 p_po_line_id IN NUMBER,
5790 x_return_status OUT NOCOPY VARCHAR2,
5791 x_msg_count OUT NOCOPY NUMBER,
5792 x_msg_data OUT NOCOPY VARCHAR2) IS
5793
5794 l_contract_type po_lines_all.contract_type%TYPE;
5795 l_unit_price po_lines_all.unit_price%TYPE;
5796 l_clm_idc_type po_lines_all.clm_idc_type%TYPE;
5797 l_auction_header_id pon_auction_headers_all.auction_header_id%TYPE;
5798 l_cost_constraint po_lines_all.cost_constraint%TYPE;
5799 l_quantity po_lines_all.quantity%TYPE;
5800 l_order_type_lookup_code po_lines_all.order_type_lookup_code%TYPE;
5801
5802 BEGIN
5803 select auction_header_id
5804 into l_auction_header_id
5805 from pon_bid_headers
5806 where bid_number = p_bid_header_id;
5807
5808 select contract_type,unit_price,clm_idc_type,cost_constraint,quantity,order_type_lookup_code
5809 into l_contract_type,l_unit_price,l_clm_idc_type,l_cost_constraint,l_quantity,l_order_type_lookup_code
5810 from po_lines_all
5811 where po_line_id = p_po_line_id;
5812
5813 update_bid_line_to_po_line(p_po_line_id,
5814 l_contract_type,
5815 l_unit_price,
5816 l_clm_idc_type,
5817 l_cost_constraint,
5818 l_quantity,
5819 l_order_type_lookup_code,
5820 p_bid_header_id,
5821 l_auction_header_id,
5822 p_bid_line_number);
5823
5824 PON_COPY_UDAS_GRP.COPY_PO_LINE_UDA(p_bid_header_id => p_bid_header_id,
5825 p_bid_line_number => p_bid_line_number,
5826 p_po_line_id => p_po_line_id,
5827 x_return_status => x_return_status,
5828 x_msg_count => x_msg_count,
5829 x_msg_data => x_msg_data);
5830
5831 END COPY_PO_LINE_TO_BID_LINE;
5832
5833 /* Bug 10052785 - Start */
5834 /* The following procedure default the pricing and UDA date from Soliciation to
5835 the offer.
5836 If condition - This defaults data for all offer lines.
5837 Else condition - This defaults data for specific offer line. */
5838
5839 /* 1) Update IS_CHANGED_LINE_FLAG, idv_line_id, unit_price, price,
5840 Bid_currency_unit_price,bid_currency_price, clm_contract_type,
5841 clm_idc_type, clm_cost_constraint values of pon_bid_item_prices.
5842 2) Copy UDA data for each line or specific line by calling
5843 PON_COPY_UDAS_GRP.COPY_BID_LINE_UDA procedure.
5844 */
5845
5846 PROCEDURE default_line_from_sol (p_auction_header_id IN NUMBER,
5847 p_auction_line_number IN NUMBER DEFAULT NULL,
5848 p_bid_number IN NUMBER)
5849
5850 IS
5851
5852 l_auction_template_id NUMBER;
5853 l_doctype_id NUMBER;
5854 l_uda_template_id NUMBER;
5855 l_is_uda_enabled NUMBER;
5856 l_contract_type pon_auction_item_prices_all.clm_contract_type%TYPE;
5857 l_clm_idc_type pon_auction_item_prices_all.clm_idc_type%TYPE;
5858 l_cost_constraint pon_auction_item_prices_all.clm_cost_constraint%TYPE;
5859 l_err_msg VARCHAR2(100);
5860 l_return_status VARCHAR2(1);
5861 l_msg_count NUMBER;
5862 l_msg_data VARCHAR2(1000);
5863
5864 TYPE pon_line_info_record_type IS RECORD
5865 (contract_type pon_auction_item_prices_all.clm_contract_type%TYPE,
5866 clm_idc_type pon_auction_item_prices_all.clm_idc_type%TYPE,
5867 cost_constraint pon_auction_item_prices_all.clm_cost_constraint%TYPE,
5868 line_number pon_auction_item_prices_all.line_number%TYPE);
5869
5870 TYPE pon_line_info_table_type is table of pon_line_info_record_type index by binary_integer;
5871 pon_line_info_table pon_line_info_table_type;
5872
5873 BEGIN
5874 IF p_auction_line_number IS NULL THEN
5875 UPDATE pon_bid_item_prices
5876 SET IS_CHANGED_LINE_FLAG = 'N',
5877 HAS_USER_BID_FLAG = 'N'
5878 WHERE auction_header_id = p_auction_header_id AND
5879 bid_number = p_bid_number AND
5880 idv_line_id IS NOT NULL AND
5881 --ELINs project
5882 NOT (Nvl(user_marked_flag,'N') = 'Y' AND Nvl(has_user_bid_flag,'N') = 'Y');
5883
5884 UPDATE pon_bid_item_prices
5885 SET idv_line_id = null,
5886 unit_price = null,
5887 price = null,
5888 quantity=null,
5889 Bid_currency_unit_price = null,
5890 bid_currency_price = null
5891 WHERE auction_header_id = p_auction_header_id AND
5892 bid_number = p_bid_number;
5893
5894 select clm_contract_type, clm_idc_type, clm_cost_constraint, line_number
5895 bulk collect into pon_line_info_table
5896 from pon_auction_item_prices_all
5897 where auction_header_id = p_auction_header_id;
5898
5899
5900 FOR i IN pon_line_info_table.first..pon_line_info_table.last
5901 LOOP
5902 UPDATE pon_bid_item_prices
5903 SET clm_contract_type = pon_line_info_table(i).contract_type,
5904 clm_idc_type = pon_line_info_table(i).clm_idc_type,
5905 clm_cost_constraint = pon_line_info_table(i).cost_constraint
5906 WHERE auction_header_id = p_auction_header_id AND
5907 bid_number = p_bid_number and
5908 auction_line_number = pon_line_info_table(i).line_number;
5909
5910 SELECT uda_template_id INTO l_auction_template_id
5911 FROM pon_auction_item_prices_all
5912 WHERE auction_header_id = p_auction_header_id AND
5913 line_number = pon_line_info_table(i).line_number;
5914
5915 BEGIN
5916 select doctype_id
5917 into l_doctype_id
5918 from pon_auction_headers_all
5919 WHERE auction_header_id = p_auction_header_id;
5920
5921 l_is_uda_enabled := PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id);
5922 IF l_is_uda_enabled = 1 THEN
5923 l_uda_template_id := po_uda_data_util.get_template_id(p_functional_area => 'SOURCING',
5924 p_document_type => 'OFFER',
5925 p_document_style_id => NULL,
5926 p_document_level => 'LINE',
5927 p_input_date => sysdate,
5928 x_return_status => l_return_status,
5929 x_err_msg => l_err_msg);
5930 END IF;
5931 END;
5932
5933 PON_COPY_UDAS_GRP.COPY_BID_LINE_UDA(p_auction_header_id => p_auction_header_id,
5934 p_bid_number => p_bid_number,
5935 p_source_bid_number => null,
5936 p_line_number => pon_line_info_table(i).line_number,
5937 p_auction_template_id => l_auction_template_id,
5938 p_bid_template_id => l_uda_template_id,
5939 p_copyfrom => 'A',
5940 x_return_status => l_return_status,
5941 x_msg_count => l_msg_count,
5942 x_msg_data => l_msg_data);
5943 END LOOP;
5944 ELSE
5945 select clm_contract_type, clm_idc_type, clm_cost_constraint, uda_template_id
5946 into l_contract_type, l_clm_idc_type, l_cost_constraint, l_auction_template_id
5947 from pon_auction_item_prices_all
5948 where auction_header_id = p_auction_header_id AND
5949 line_number = p_auction_line_number;
5950
5951 UPDATE pon_bid_item_prices
5952 SET idv_line_id = null,
5953 unit_price = null,
5954 price = null,
5955 Bid_currency_unit_price = null,
5956 bid_currency_price = null,
5957 clm_contract_type = l_contract_type,
5958 clm_idc_type = l_clm_idc_type,
5959 clm_cost_constraint = l_cost_constraint
5960 WHERE auction_header_id = p_auction_header_id AND
5961 bid_number = p_bid_number AND
5962 auction_line_number = p_auction_line_number ;
5963
5964 BEGIN
5965 select doctype_id
5966 into l_doctype_id
5967 from pon_auction_headers_all
5968 where auction_header_id = p_auction_header_id;
5969
5970 l_is_uda_enabled := PON_CLM_UTIL_PKG.IS_UDA_ENABLED(l_doctype_id);
5971 IF l_is_uda_enabled = 1 THEN
5972 l_uda_template_id := po_uda_data_util.get_template_id(p_functional_area => 'SOURCING',
5973 p_document_type => 'OFFER',
5974 p_document_style_id => NULL,
5975 p_document_level => 'LINE',
5976 p_input_date => sysdate,
5977 x_return_status => l_return_status,
5978 x_err_msg => l_err_msg);
5979 END IF;
5980 END;
5981
5982 PON_COPY_UDAS_GRP.COPY_BID_LINE_UDA(p_auction_header_id => p_auction_header_id,
5983 p_bid_number => p_bid_number,
5984 p_source_bid_number => null,
5985 p_line_number => p_auction_line_number,
5986 p_auction_template_id => l_auction_template_id,
5987 p_bid_template_id => l_uda_template_id,
5988 p_copyfrom => 'A',
5989 x_return_status => l_return_status,
5990 x_msg_count => l_msg_count,
5991 x_msg_data => l_msg_data);
5992 END IF;
5993
5994 EXCEPTION WHEN OTHERS THEN
5995 RAISE;
5996 END default_line_from_sol;
5997
5998 /* Bug 10052785 - End */
5999
6000 /*Added for Unsolcited Lines Project
6001 PROCEDURE : get_max_internal_and_doc_line_num
6002 PARAMETERS: 1. p_auction_header_id
6003 2. p_bid_number - Bid Number
6004 3. x_max_internal_line_num - The maximum internal line
6005 number in all the rounds
6006 4. x_max_document_line_num - The maximum subline sequence
6007 number in all the rounds
6008 5. x_result - return status.
6009 6. x_error_code - error code
6010 7. x_error_message - The actual error message
6011 COMMENT : This procedure will return the maximum value of the
6012 LINE_NUMBER and SUB_LINE_SEQUENCE_NUMBER columns in all
6013 the rounds
6014 ======================================================================*/
6015
6016 PROCEDURE GET_MAX_INTERNAL_AND_DOC_NUM (
6017 p_auction_header_id IN NUMBER,
6018 p_bid_number IN NUMBER,
6019 x_max_internal_line_num OUT NOCOPY NUMBER,
6020 x_max_document_line_num OUT NOCOPY NUMBER,
6021 x_max_disp_line_num OUT NOCOPY NUMBER,
6022 x_result OUT NOCOPY VARCHAR2,
6023 x_error_code OUT NOCOPY VARCHAR2,
6024 x_error_message OUT NOCOPY VARCHAR2) IS
6025
6026 l_number_of_lines NUMBER;
6027 BEGIN
6028
6029 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
6030 FND_LOG.string (log_level => FND_LOG.level_statement,
6031 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
6032 message => 'Entered procedure with p_auction_header_id = ' || p_auction_header_id);
6033 END IF;
6034
6035 x_result := FND_API.G_RET_STS_SUCCESS;
6036 SELECT
6037 NVL (number_of_lines, 0)
6038 --NVL (max_internal_line_num, 0),
6039 --NVL (max_document_line_num, 0)
6040 INTO
6041 l_number_of_lines
6042 --x_max_internal_line_num,
6043 --x_max_document_line_num
6044 FROM
6045 pon_auction_headers_all
6046 WHERE
6047 auction_header_id = p_auction_header_id;
6048
6049 IF (l_number_of_lines > 0) THEN
6050
6051 SELECT
6052 GREATEST (NVL(MAX(bid_items.line_number),0), NVL(MAX(items.line_number),0))
6053 --GREATEST (x_max_document_line_num, NVL(MAX(DECODE (items.group_type, 'LOT_LINE', 0, 'GROUP_LINE', 0, items.sub_line_sequence_number)),0)),
6054 --NVL (MAX(items.disp_line_number), 0)
6055 INTO
6056 x_max_internal_line_num
6057 --x_max_document_line_num,
6058 --x_max_disp_line_num
6059 FROM
6060 pon_auction_item_prices_all items,
6061 pon_bid_item_prices bid_items
6062 WHERE
6063 items.auction_header_id = p_auction_header_id
6064 and bid_items.bid_number = p_bid_number
6065 and items.auction_header_id = bid_items.auction_header_id;
6066
6067 END IF;
6068 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
6069 FND_LOG.string (log_level => FND_LOG.level_statement,
6070 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
6071 message => 'Returning x_max_internal_line_num = ' || x_max_internal_line_num ||
6072 ', x_max_document_line_num = ' || x_max_document_line_num ||
6073 ', x_max_disp_line_num = ' || x_max_disp_line_num);
6074 END IF;
6075
6076 EXCEPTION
6077 WHEN NO_DATA_FOUND THEN
6078 x_max_disp_line_num := 0;
6079 x_max_document_line_num := 0;
6080 x_max_internal_line_num := 0;
6081
6082 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.g_current_runtime_level) THEN
6083 FND_LOG.string (log_level => FND_LOG.level_statement,
6084 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
6085 message => 'Returning x_max_internal_line_num = ' || x_max_internal_line_num ||
6086 ', x_max_document_line_num = ' || x_max_document_line_num ||
6087 ', x_max_disp_line_num = ' || x_max_disp_line_num);
6088 END IF;
6089 WHEN OTHERS THEN
6090 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
6091 x_error_code := SQLCODE;
6092 x_error_message := SUBSTR(SQLERRM, 1, 100);
6093
6094 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.g_current_runtime_level) THEN
6095 FND_LOG.string (log_level => FND_LOG.level_exception,
6096 module => g_module_prefix || 'GET_MAX_INTERNAL_AND_DOC_NUM',
6097 message => 'Exception occured while getting the sequences'
6098 || ' Error Number :' || SQLCODE || ' Exception Message :' || SUBSTR(SQLERRM, 1, 200) );
6099 END IF;
6100
6101
6102 END GET_MAX_INTERNAL_AND_DOC_NUM;
6103
6104 PROCEDURE NULL_OUT_UNSOL_IDV_LINE_ID( p_auction_header_id IN NUMBER,
6105 p_bid_number IN NUMBER) IS
6106
6107 BEGIN
6108
6109 UPDATE pon_bid_item_prices
6110 SET idv_line_id = NULL
6111 WHERE auction_header_id = p_auction_header_id
6112 AND bid_number = p_bid_number
6113 AND auction_line_number = -1;
6114
6115 EXCEPTION WHEN No_Data_Found THEN
6116 --There may or may not be unsolicited lines, so we don't need to do anything
6117 NULL;
6118
6119 END NULL_OUT_UNSOL_IDV_LINE_ID;
6120
6121 FUNCTION is_invalid_site_for_bid(p_auction_header_id IN NUMBER, p_tp_id IN NUMBER)
6122 RETURN NUMBER IS
6123
6124 is_single_bid_freq VARCHAR2(1);
6125 is_public VARCHAR2(1);
6126 site_id NUMBER;
6127
6128 BEGIN
6129 --In case the vendor is not in the invited, return -1 and let the old flow work.
6130 BEGIN
6131 SELECT vendor_site_id
6132 INTO site_id
6133 FROM pon_bidding_parties
6134 WHERE auction_header_id = p_auction_header_id
6135 AND trading_partner_id = p_tp_id
6136 AND ROWNUM = 1;
6137
6138 EXCEPTION WHEN No_Data_Found THEN
6139
6140 RETURN -1;
6141
6142 END;
6143
6144 SELECT Decode(bid_frequency_code, 'SINGLE_BID_ONLY', 'Y', 'N' ), Decode (BID_LIST_TYPE ,'PUBLIC_BID_LIST', 'Y', 'N')
6145 INTO is_single_bid_freq, is_public
6146 FROM pon_auction_headers_all
6147 WHERE auction_header_id = p_auction_header_id;
6148
6149 IF(is_single_bid_freq = 'Y') THEN
6150
6151 BEGIN
6152 SELECT vendor_site_id
6153 INTO site_id
6154 FROM pon_bidding_parties
6155 WHERE auction_header_id = p_auction_header_id
6156 AND trading_partner_id = p_tp_id
6157 AND vendor_site_id NOT IN
6158 (SELECT vendor_site_id
6159 FROM pon_bid_headers
6160 WHERE auction_header_id = p_auction_header_id
6161 AND trading_partner_id = p_tp_id
6162 AND bid_status = 'ACTIVE')
6163 AND ROWNUM = 1;
6164
6165 EXCEPTION WHEN No_Data_Found THEN
6166
6167 SELECT vendor_site_id
6168 INTO site_id
6169 FROM pon_bidding_parties
6170 WHERE auction_header_id = p_auction_header_id
6171 AND trading_partner_id = p_tp_id
6172 AND ROWNUM = 1;
6173
6174 RETURN site_id;
6175
6176 END;
6177 END IF;
6178
6179 RETURN -1;
6180
6181 END is_invalid_site_for_bid;
6182
6183 FUNCTION is_accepted_terms_cond(p_auction_header_id IN NUMBER,
6184 p_auction_header_id_orig_amend IN NUMBER,
6185 p_trading_partner_id number,
6186 p_trading_partner_contact_id number
6187 )
6188 RETURN VARCHAR2 IS
6189 l_count NUMBER;
6190
6191 BEGIN
6192
6193 SELECT Count(*) INTO l_count
6194 FROM pon_supplier_activities
6195 WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
6196 AND last_activity_code = 'ACCEPT_TERMSCOND'
6197 AND trading_partner_id = p_trading_partner_id
6198 AND trading_partner_contact_id = p_trading_partner_contact_id;
6199
6200 IF (l_count>0) THEN
6201 RETURN 'Y';
6202 ELSE
6203 RETURN 'N';
6204 END IF;
6205
6206 EXCEPTION
6207
6208 WHEN OTHERS THEN
6209 RETURN 'N';
6210
6211 END is_accepted_terms_cond;
6212
6213 END PON_BID_DEFAULTING_PKG;