1 PACKAGE BODY PON_BID_DEFAULTING_PKG AS
2 --$Header: PONBDDFB.pls 120.52.12010000.3 2009/01/07 12:10:50 amundhra 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 SEQUENCE_NUMBER,
462 CREATION_DATE,
463 CREATED_BY,
464 LAST_UPDATE_DATE,
465 LAST_UPDATED_BY,
466 PF_TYPE
467 )
468 (SELECT
469 p_bid_number, -- BID_NUMBER
470 apf.line_number, -- LINE_NUMBER
471 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
472 p_auc_header_id, -- AUCTION_HEADER_ID
473 apf.pricing_basis, -- PRICING_BASIS
474 apf.sequence_number, -- SEQUENCE_NUMBER
475 sysdate, -- CREATION_DATE
476 p_userid, -- CREATED_BY
477 sysdate, -- LAST_UPDATE_DATE
478 p_userid, -- LAST_UPDATED_BY
479 apf.pf_type -- PF_TYPE
480 FROM pon_price_elements apf
481 WHERE apf.auction_header_id = p_auc_header_id
482 AND apf.pf_type = 'SUPPLIER' -- only supplier price factors
483 AND apf.line_number BETWEEN p_batch_start AND p_batch_end
484 AND NOT EXISTS
488 AND pbpe.line_number = apf.line_number
485 (SELECT pbpe.price_element_type_id
486 FROM pon_bid_price_elements pbpe
487 WHERE pbpe.bid_number = p_bid_number
489 AND pbpe.price_element_type_id = apf.price_element_type_id));
490
491
492 END IF;
493
494
495
496 /************************************************************
497 ** STEP 6c: Check for buyer price factors - if this supplier
498 ** is invited to the negotiation.
499 *************************************************************/
500
501 -- Insert missing BUYER price factors if applicable
502 IF (p_supp_seq_number IS NOT null) THEN
503
504 INSERT INTO pon_bid_price_elements
505 (
506 BID_NUMBER,
507 LINE_NUMBER,
508 PRICE_ELEMENT_TYPE_ID,
509 AUCTION_HEADER_ID,
510 PRICING_BASIS,
511 AUCTION_CURRENCY_VALUE,
512 BID_CURRENCY_VALUE,
513 SEQUENCE_NUMBER,
514 CREATION_DATE,
515 CREATED_BY,
516 LAST_UPDATE_DATE,
517 LAST_UPDATED_BY,
518 PF_TYPE
519 )
520 (SELECT
521 p_bid_number, -- BID_NUMBER
522 apf.line_number, -- LINE_NUMBER
523 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
524 p_auc_header_id, -- AUCTION_HEADER_ID
525 apf.pricing_basis, -- PRICING_BASIS
526 pf.value, -- AUCTION_CURRENCY_VALUE
527 decode(apf.pricing_basis,
528 'PER_UNIT', round(pf.value * p_rate, p_price_prec),
529 'FIXED_AMOUNT', round(pf.value * p_rate, p_curr_prec),
530 'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
531 apf.sequence_number, -- SEQUENCE_NUMBER
532 sysdate, -- CREATION_DATE
533 p_userid, -- CREATED_BY
534 sysdate, -- LAST_UPDATE_DATE
535 p_userid, -- LAST_UPDATED_BY
536 apf.pf_type -- PF_TYPE
537 FROM pon_price_elements apf,
538 pon_pf_supplier_values pf,
539 pon_bid_item_prices bl
540 WHERE apf.auction_header_id = p_auc_header_id
541 AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
542 AND apf.display_to_suppliers_flag = 'Y'
543 AND bl.bid_number = p_bid_number
544 AND bl.line_number = apf.line_number
545 AND bl.display_price_factors_flag = 'Y'
546 AND pf.auction_header_id = apf.auction_header_id
547 AND pf.line_number = apf.line_number
548 AND pf.pf_seq_number = apf.sequence_number
549 AND pf.supplier_seq_number = p_supp_seq_number
550 AND nvl(pf.value, 0) <> 0
551 AND apf.line_number BETWEEN p_batch_start AND p_batch_end
552 AND NOT EXISTS
553 (SELECT pbpe.price_element_type_id
554 FROM pon_bid_price_elements pbpe
555 WHERE pbpe.bid_number = p_bid_number
556 AND pbpe.line_number = apf.line_number
557 AND pbpe.price_element_type_id = apf.price_element_type_id));
558
559 END IF;
560 END IF;
561
562 -- Insert missing shipments for all non-negotiable shipments
563
564
565 /************************************************************
566 ** STEP 7: Insert all the missing non-negotiable or required
567 ** or mandatory shipments/price breaks.
568 *************************************************************/
569
570 /*
571 * Price Tiers Enhancements
572 * Quantity tiers are negotiable shipments so no need to copy the max_quantity field here
573 */
574
575 INSERT INTO pon_bid_shipments
576 (
577 BID_NUMBER,
578 LINE_NUMBER,
579 SHIPMENT_NUMBER,
580 AUCTION_HEADER_ID,
581 AUCTION_LINE_NUMBER,
582 AUCTION_SHIPMENT_NUMBER,
583 SHIPMENT_TYPE,
584 SHIP_TO_ORGANIZATION_ID,
585 SHIP_TO_LOCATION_ID,
586 QUANTITY,
587 PRICE_TYPE,
588 PRICE,
589 EFFECTIVE_START_DATE,
590 EFFECTIVE_END_DATE,
591 CREATION_DATE,
592 CREATED_BY,
593 LAST_UPDATE_DATE,
594 LAST_UPDATED_BY,
595 LAST_UPDATE_LOGIN,
596 HAS_PRICE_DIFFERENTIALS_FLAG
597 )
598 (SELECT
599 p_bid_number, -- BID_NUMBER
600 apb.line_number, -- LINE_NUMBER
601 apb.shipment_number+1, -- SHIPMENT_NUMBER
602 p_auc_header_id, -- AUCTION_HEADER_ID
603 apb.line_number, -- AUCTION_LINE_NUMBER
604 apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
605 apb.shipment_type, -- SHIPMENT_TYPE
606 apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
607 apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
608 apb.quantity, -- QUANTITY
609 'PRICE', -- PRICE_TYPE
610 apb.price, -- PRICE
611 apb.effective_start_date, -- EFFECTIVE_START_DATE
612 apb.effective_end_date, -- EFFECTIVE_END_DATE
613 sysdate, -- CREATION_DATE
614 p_userid, -- CREATED_BY
615 sysdate, -- LAST_UPDATE_DATE
616 p_userid, -- LAST_UPDATED_BY
617 null, -- LAST_UPDATE_LOGIN
618 apb.has_price_differentials_flag -- HAS_PRICE_DIFFERENTIALS_FLAG
619 FROM pon_auction_shipments_all apb, pon_auction_item_prices_all aip
620 WHERE apb.auction_header_id = p_auc_header_id
621 AND apb.line_number BETWEEN p_batch_start AND p_batch_end
622 AND aip.auction_header_id = apb.auction_header_id
623 AND aip.line_number = apb.line_number
624 AND nvl(aip.price_break_neg_flag, 'Y') = 'N'
628 WHERE pbs.bid_number = p_bid_number
625 AND NOT EXISTS
626 (SELECT pbs.auction_shipment_number
627 FROM pon_bid_shipments pbs
629 AND pbs.line_number = apb.line_number
630 AND pbs.auction_shipment_number = apb.shipment_number));
631
632 /************************************************************
633 ** STEP 8: Insert all the missing line-level as well as shipment
634 ** level price breaks in a single insert statement.
635 *************************************************************/
636
637 INSERT INTO pon_bid_price_differentials
638 (
639 AUCTION_HEADER_ID,
640 BID_NUMBER,
641 LINE_NUMBER,
642 SHIPMENT_NUMBER,
643 PRICE_DIFFERENTIAL_NUMBER,
644 PRICE_TYPE,
645 CREATION_DATE,
646 CREATED_BY,
647 LAST_UPDATE_DATE,
648 LAST_UPDATED_BY,
649 LAST_UPDATE_LOGIN
650 )
651 (SELECT
652 p_auc_header_id, -- AUCTION_HEADER_ID
653 p_bid_number, -- BID_NUMBER
654 apd.line_number, -- LINE_NUMBER
655 decode(apd.shipment_number, -1, -1, apd.shipment_number+1), -- SHIPMENT_NUMBER
656 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
657 apd.price_type, -- PRICE_TYPE
658 sysdate, -- CREATION_DATE
659 p_userid, -- CREATED_BY
660 sysdate, -- LAST_UPDATE_DATE
661 p_userid, -- LAST_UPDATED_BY
662 null -- LAST_UPDATE_LOGIN
663 FROM pon_price_differentials apd
664 WHERE apd.auction_header_id = p_auc_header_id
665 AND apd.line_number BETWEEN p_batch_start AND p_batch_end
666 AND NOT EXISTS
667 (SELECT pbpd.price_differential_number
668 FROM pon_bid_price_differentials pbpd
669 WHERE pbpd.bid_number = p_bid_number
670 AND pbpd.line_number = apd.line_number
671 AND pbpd.price_differential_number = apd.price_differential_number));
672
673 END insert_auction_lines;
674
675 -- ======================================================================
676 -- PROCEDURE: POPULATE_OLD_VALUE_COLUMNS PRIVATE
677 -- PARAMETERS:
678 -- p_bid_number IN new bid number
679 -- p_source_bid_num IN source bid number
680 --
681 -- COMMENT: Populate old value columns for a bid
682 -- ======================================================================
683 PROCEDURE populate_old_value_columns
684 (
685 p_bid_number IN pon_bid_headers.bid_number%TYPE,
686 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
687 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
688 p_batch_end IN pon_bid_item_prices.line_number%TYPE
689 ) IS
690 BEGIN
691
692 -- Update line level old value columns
693 UPDATE pon_bid_item_prices bl
694 SET (bl.old_price,
695 bl.old_bid_currency_unit_price,
696 bl.old_bid_currency_price,
697 bl.old_bid_currency_limit_price,
698 bl.old_po_bid_min_rel_amount,
699 bl.old_quantity,
700 bl.old_publish_date,
701 bl.old_promised_date,
702 bl.old_note_to_auction_owner,
703 bl.old_bid_curr_advance_amount,
704 bl.old_recoupment_rate_percent,
705 bl.old_progress_pymt_rate_percent,
706 bl.old_retainage_rate_percent,
707 bl.old_bid_curr_max_retainage_amt) =
708 (SELECT
709 old_bl.price,
710 old_bl.bid_currency_unit_price,
711 old_bl.bid_currency_price,
712 old_bl.bid_currency_limit_price,
713 old_bl.po_bid_min_rel_amount,
714 old_bl.quantity,
715 old_bl.publish_date,
716 old_bl.promised_date,
717 old_bl.note_to_auction_owner,
718 old_bl.bid_curr_advance_amount,
719 old_bl.recoupment_rate_percent,
720 old_bl.progress_pymt_rate_percent,
721 old_bl.retainage_rate_percent,
722 old_bl.bid_curr_max_retainage_amt
723 FROM pon_bid_item_prices old_bl
724 WHERE old_bl.bid_number = p_source_bid_num
725 AND old_bl.line_number = bl.line_number)
726 WHERE bl.bid_number = p_bid_number
727 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
728
729 -- Update attribute old value columns
730 UPDATE pon_bid_attribute_values ba
731 SET ba.old_value =
732 (SELECT old_ba.value
733 FROM pon_bid_attribute_values old_ba
734 WHERE old_ba.bid_number = p_source_bid_num
735 AND old_ba.line_number = ba.line_number
736 AND old_ba.attribute_name = ba.attribute_name)
737 WHERE ba.bid_number = p_bid_number
738 AND ba.line_number BETWEEN p_batch_start AND p_batch_end;
739
740 -- Update SUPPLIER price factor old value columns
741 UPDATE pon_bid_price_elements bpf
742 SET bpf.old_bid_currency_value =
743 (SELECT old_bpf.bid_currency_value
744 FROM pon_bid_price_elements old_bpf
745 WHERE old_bpf.bid_number = p_source_bid_num
746 AND old_bpf.line_number = bpf.line_number
747 AND old_bpf.price_element_type_id = bpf.price_element_type_id)
748 WHERE bpf.bid_number = p_bid_number
749 AND bpf.pf_type = 'SUPPLIER'
750 AND bpf.line_number BETWEEN p_batch_start AND p_batch_end;
751
752 -- Update price break old value columns
753 UPDATE pon_bid_shipments bpb
754 SET (bpb.old_bid_currency_unit_price,
755 bpb.old_bid_currency_price,
756 bpb.old_price_discount,
757 bpb.old_ship_to_org_id,
758 bpb.old_ship_to_loc_id,
759 bpb.old_effective_start_date,
760 bpb.old_effective_end_date,
761 bpb.old_quantity,
762 bpb.old_max_quantity,
766 old_bpb.bid_currency_price,
763 bpb.old_price_type) =
764 (SELECT
765 old_bpb.bid_currency_unit_price,
767 old_bpb.price_discount,
768 old_bpb.ship_to_organization_id,
769 old_bpb.ship_to_location_id,
770 old_bpb.effective_start_date,
771 old_bpb.effective_end_date,
772 old_bpb.quantity,
773 old_bpb.max_quantity,
774 old_bpb.price_type
775 FROM pon_bid_shipments old_bpb
776 WHERE old_bpb.bid_number = p_source_bid_num
777 AND old_bpb.line_number = bpb.line_number
778 AND old_bpb.shipment_number = bpb.shipment_number)
779 WHERE bpb.bid_number = p_bid_number
780 AND bpb.line_number BETWEEN p_batch_start AND p_batch_end;
781
782 -- Update price differential old value columns
783 UPDATE pon_bid_price_differentials bpd
784 SET bpd.old_multiplier =
785 (SELECT old_bpd.multiplier
786 FROM pon_bid_price_differentials old_bpd
787 WHERE old_bpd.bid_number = p_source_bid_num
788 AND old_bpd.line_number = bpd.line_number
789 AND old_bpd.shipment_number = bpd.shipment_number
790 AND old_bpd.price_differential_number = bpd.price_differential_number)
791 WHERE bpd.bid_number = p_bid_number
792 AND bpd.line_number BETWEEN p_batch_start AND p_batch_end;
793
794 END populate_old_value_columns;
795
796 -- ======================================================================
797 -- PROCEDURE: HANDLE_PROXY PRIVATE
798 -- PARAMETERS:
799 -- p_auc_header_id IN the auction header id
800 -- p_draft_bid_num IN bid number to update proxy for
801 -- p_tpid IN trading partner id of supplier
802 -- p_tpcid IN trading partner contact id of supplier
803 -- p_vensid IN vendor site bid is placed on
804 -- x_prev_bid_number OUT returned backing bid number
805 -- x_rebid_flag OUT Y/N if the current bid is a rebid/not a rebid
806 --
807 -- COMMENT: updates price, limit_price, and copy_price_for_proxy_flag
808 -- First finds the backing ACTIVE bid, if it exists and determine
809 -- if the bid was a rebid
810 -- ======================================================================
811 PROCEDURE handle_proxy
812 (
813 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
814 p_draft_bid_num IN pon_bid_headers.bid_number%TYPE,
815 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
816 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
817 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
818 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
819 x_rebid_flag OUT NOCOPY VARCHAR2
820 ) IS
821 l_max_line_number pon_bid_item_prices.line_number%TYPE;
822 l_batch_start pon_bid_item_prices.line_number%TYPE;
823 l_batch_end pon_bid_item_prices.line_number%TYPE;
824 BEGIN
825
826 -- Determine the latest ACTIVE bid and set rebid_flag to Y
827 -- Since there can only exist a single ACTIVE bid on an amendment for
828 -- a particular user on a site, we use the rownum = 1 optimisation
829
830 -- It is possible that another supplier from the same supplier company
831 -- is modifying the draft. So we can't use the login in supplier's tpcid,
832 -- we should use the tpcid of the user who creates the draft
833 SELECT bh.bid_number, 'Y'
834 INTO x_prev_bid_number, x_rebid_flag
835 FROM pon_bid_headers bh
836 WHERE bh.auction_header_id = p_auc_header_id
837 AND bh.trading_partner_id = p_tpid
838 AND bh.trading_partner_contact_id =
839 (SELECT trading_partner_contact_id
840 FROM pon_bid_headers bh2
841 WHERE bh2.bid_number = p_draft_bid_num)
842 AND bh.vendor_site_id = p_vensid
843 AND bh.bid_status = 'ACTIVE'
844 AND rownum = 1
845 ORDER BY bh.publish_date DESC;
846
847 -- Update old_bid_number to new source bid
848 UPDATE pon_bid_headers bh
849 SET bh.old_bid_number = x_prev_bid_number
850 WHERE bh.bid_number = p_draft_bid_num;
851
852 -- START BATCHING
853
854 -- Determine the maximum line number for the negotiation
855 SELECT ah.max_internal_line_num
856 INTO l_max_line_number
857 FROM pon_auction_headers_all ah
858 WHERE ah.auction_header_id = p_auc_header_id;
859
860 -- Define the initial batch range (line numbers are indexed from 1)
861 l_batch_start := 1;
862 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
863 l_batch_end := l_max_line_number;
864 ELSE
865 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
866 END IF;
867
868 WHILE (l_batch_start <= l_max_line_number) LOOP
869
870 -- Copy over price columns and set copy_price_for_proxy_flag
871 -- If the proxy has been exhausted, copy_price_for_proxy flag changed to N
872 UPDATE pon_bid_item_prices bl
873 SET (bl.price, bl.proxy_bid_limit_price, bl.bid_currency_price,
874 bl.bid_currency_limit_price, bl.bid_currency_trans_price,
875 bl.unit_price, bl.bid_currency_unit_price, bl.copy_price_for_proxy_flag,
876 bl.old_price, bl.old_bid_currency_unit_price, bl.old_bid_currency_price,
877 bl.old_bid_currency_limit_price) =
878 (SELECT old_bl.price, old_bl.proxy_bid_limit_price, old_bl.bid_currency_price,
879 old_bl.bid_currency_limit_price, old_bl.bid_currency_trans_price,
880 old_bl.unit_price, old_bl.bid_currency_unit_price,
881 decode(sign(old_bl.proxy_bid_limit_price - old_bl.price),
882 0, 'N', 'Y'),
883 old_bl.price, old_bl.bid_currency_unit_price, old_bl.bid_currency_price, old_bl.bid_currency_limit_price
884 FROM pon_bid_item_prices old_bl
885 WHERE old_bl.bid_number = x_prev_bid_number
889 AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
886 AND old_bl.line_number = bl.line_number)
887 WHERE bl.bid_number = p_draft_bid_num
888 AND bl.copy_price_for_proxy_flag = 'Y'
890
891 -- Copy over the rank for all lines
892 UPDATE pon_bid_item_prices bl
893 SET rank =
894 (SELECT old_bl.rank
895 FROM pon_bid_item_prices old_bl
896 WHERE old_bl.bid_number = x_prev_bid_number
897 AND old_bl.line_number = bl.line_number)
898 WHERE bl.bid_number = p_draft_bid_num
899 AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
900
901 -- Find the new batch range
902 l_batch_start := l_batch_end + 1;
903 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
904 l_batch_end := l_max_line_number;
905 ELSE
906 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
907 END IF;
908
909 END LOOP;
910
911 -- END BATCHING
912
913 EXCEPTION
914 -- No ACTIVE bids on the current amendment
915 WHEN NO_DATA_FOUND THEN
916 SELECT bh.old_bid_number
917 INTO x_prev_bid_number
918 FROM pon_bid_headers bh
919 WHERE bh.bid_number = p_draft_bid_num;
920 x_rebid_flag := 'N';
921
922 END handle_proxy;
923
924 -- ======================================================================
925 -- PROCEDURE: INSERT_INTO_BID_HEADER PRIVATE
926 -- PARAMETERS:
927 -- p_auc_header_id IN auction_header_id of negotiation
928 -- p_source_bid_num IN source_bid to default from
929 -- p_tpid IN trading partner id of supplier
930 -- p_tpname IN trading partner name of supplier
931 -- p_tpcid IN trading partner contact id of supplier
932 -- p_tpcname IN trading partner contact name of supplier
933 -- p_userid IN userid of bid creator
934 -- p_venid IN vendor id
935 -- p_vensid IN vendor site id to place bid for
936 -- p_venscode IN vendor site code to place bid for
937 -- p_auctpid IN buyers trading partner id
938 -- p_auctpcid IN buyers trading partner contact id
939 -- p_buyer_user IN flag indicating surrogate bid or not
940 -- p_rebid_flag IN flag indicating rebid or not
941 -- x_bid_number OUT bid number of the new bid
942 --
943 -- COMMENT: inserts a bid header for the new bid. Also generates the bid number
944 -- ======================================================================
945 PROCEDURE insert_into_bid_header
946 (
947 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
948 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
949 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
950 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
951 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
952 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
953 p_userid IN pon_bid_headers.created_by%TYPE,
954 p_venid IN pon_bid_headers.vendor_id%TYPE,
955 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
956 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
957 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
958 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
959 p_buyer_user IN VARCHAR2,
960 p_rebid_flag IN VARCHAR2,
961 p_prev_bid_disq IN VARCHAR2,
962 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
963 ) IS
964 l_old_min_bid_change_type pon_auction_headers_all.min_bid_change_type%TYPE;
965 l_old_min_bid_change pon_auction_headers_all.min_bid_decrement%TYPE;
966 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
967 ----------------------------------------------------------------
968 l_two_part_flag pon_auction_headers_all.TWO_PART_FLAG%TYPE;
969 l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%TYPE;
970 ----------------------------------------------------------------
971 BEGIN
972
973 IF p_source_bid_num IS NOT NULL AND p_source_bid_num <> 0 THEN
974 SELECT pah.min_bid_change_type,
975 pah.min_bid_decrement
976 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
977 -------------------------------------------------------
978 , pah.TWO_PART_FLAG
979 , pah.TECHNICAL_EVALUATION_STATUS
980 -------------------------------------------------------
981 INTO l_old_min_bid_change_type,
982 l_old_min_bid_change
983 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
984 -------------------------------------------------------
985 , l_two_part_flag
986 , l_technical_evaluation_status
987 -------------------------------------------------------
988 FROM PON_AUCTION_HEADERS_ALL pah,
989 PON_BID_HEADERS pbh
990 WHERE pah.auction_header_id = pbh.auction_header_id
991 AND pbh.bid_number = p_source_bid_num;
992 END IF;
993
994 -- Generate next bid number
995 SELECT pon_bid_headers_s.nextval INTO x_bid_number
996 FROM dual;
997
998 INSERT INTO pon_bid_headers
999 (
1000 BID_NUMBER,
1001 AUCTION_HEADER_ID,
1002 BIDDERS_BID_NUMBER,
1003 BID_TYPE,
1004 CONTRACT_TYPE,
1005 TRADING_PARTNER_CONTACT_NAME,
1006 TRADING_PARTNER_CONTACT_ID,
1007 TRADING_PARTNER_NAME,
1008 TRADING_PARTNER_ID,
1009 BID_STATUS,
1010 BID_EFFECTIVE_DATE,
1014 CARRIER_CODE,
1011 BID_EXPIRATION_DATE,
1012 DISQUALIFY_REASON,
1013 FREIGHT_TERMS_CODE,
1015 FOB_CODE,
1016 NOTE_TO_AUCTION_OWNER,
1017 LANGUAGE_CODE,
1018 CREATION_DATE,
1019 CREATED_BY,
1020 LAST_UPDATE_DATE,
1021 LAST_UPDATED_BY,
1022 AUCTION_CREATION_DATE,
1023 BID_CURRENCY_CODE,
1024 RATE,
1025 MIN_BID_CHANGE ,
1026 PROXY_BID_FLAG ,
1027 NUMBER_PRICE_DECIMALS,
1028 DOCTYPE_ID,
1029 VENDOR_ID,
1030 VENDOR_SITE_ID ,
1031 RATE_DSP,
1032 INITIATE_APPROVAL,
1033 DRAFT_LOCKED,
1034 DRAFT_LOCKED_BY,
1035 DRAFT_LOCKED_BY_CONTACT_ID,
1036 DRAFT_LOCKED_DATE,
1037 VENDOR_SITE_CODE,
1038 SHORTLIST_FLAG,
1039 ATTRIBUTE_LINE_NUMBER,
1040 NOTE_TO_SUPPLIER,
1041 SURROG_BID_CREATED_TP_ID,
1042 SURROG_BID_CREATED_CONTACT_ID,
1043 --SURROG_BID_RECEIPT_DATE,
1044 SURROG_BID_ONLINE_ENTRY_DATE,
1045 SURROG_BID_FLAG,
1046 COLOR_SEQUENCE_ID,
1047 OLD_NOTE_TO_AUCTION_OWNER,
1048 OLD_BIDDERS_BID_NUMBER,
1049 OLD_BID_EXPIRATION_DATE,
1050 OLD_MIN_BID_CHANGE,
1051 OLD_BID_STATUS,
1052 OLD_SURROG_BID_RECEIPT_DATE,
1053 REL12_DRAFT_FLAG,
1054 OLD_BID_NUMBER
1055 --added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1056 --------------------------------------------------------------
1057 , SUBMIT_STAGE
1058 --------------------------------------------------------------
1059 )
1060 (SELECT
1061 x_bid_number, -- BID_NUMBER
1062 ah.auction_header_id, -- AUCTION_HEADER_ID
1063 bh.bidders_bid_number, -- BIDDERS_BID_NUMBER
1064 'REVERSE', -- BID_TYPE
1065 ah.contract_type, -- CONTRACT_TYPE
1066 p_tpcname, -- TRADING_PARTNER_CONTACT_NAME
1067 p_tpcid, -- TRADING_PARTNER_CONTACT_ID
1068 p_tpname, -- TRADING_PARTNER_NAME
1069 p_tpid, -- TRADING_PARTNER_ID
1070 'DRAFT', -- BID_STATUS
1071 bh.bid_effective_date, -- BID_EFFECTIVE_DATE
1072 bh.bid_expiration_date, -- BID_EXPIRATION_DATE
1073 decode(p_prev_bid_disq, 'Y', bh.disqualify_reason, null), -- DISQUALIFY_REASON
1074 ah.freight_terms_code, -- FREIGHT_TERMS_CODE
1075 ah.carrier_code, -- CARRIER_CODE
1076 ah.fob_code, -- FOB_CODE
1077 bh.note_to_auction_owner, -- NOTE_TO_AUCTION_OWNER
1078 userenv('LANG'), -- LANGUAGE_CODE
1079 SYSDATE, -- CREATION_DATE
1080 p_userid, -- CREATED_BY
1081 SYSDATE, -- LAST_UPDATE_DATE
1082 p_userid, -- LAST_UPDATED_BY
1083 ah.creation_date, -- AUCTION_CREATION_DATE
1084 nvl(bh.bid_currency_code, ah.currency_code), -- BID_CURRENCY_CODE
1085 nvl(bh.rate, 1), -- RATE
1086 decode(ah.min_bid_change_type, l_old_min_bid_change_type,
1087 decode(ah.min_bid_decrement, l_old_min_bid_change, bh.min_bid_change, null), null), -- MIN_BID_CHANGE
1088 'N', -- PROXY_BID_FLAG
1089 nvl(bh.number_price_decimals, ah.number_price_decimals), -- NUMBER_PRICE_DECIMALS
1090 ah.doctype_id, -- DOCTYPE_ID
1091 p_venid, -- VENDOR_ID
1092 p_vensid, -- VENDOR_SITE_ID
1093 nvl(bh.rate_dsp, 1), -- RATE_DSP
1094 bh.initiate_approval, -- INITIATE_APPROVAL
1095 'Y', -- DRAFT_LOCKED
1096 decode(p_buyer_user, 'Y', p_auctpid, p_tpid), -- DRAFT_LOCKED_BY
1097 decode(p_buyer_user, 'Y', p_auctpcid, p_tpcid), -- DRAFT_LOCKED_BY_CONTACT_ID
1098 SYSDATE, -- DRAFT_LOCKED_DATE
1099 p_venscode, -- VENDOR_SITE_CODE
1100 'N', -- SHORTLIST_FLAG
1101 -1, -- ATTRIBUTE_LINE_NUMBER
1102 ah.note_to_bidders, -- NOTE_TO_SUPPLIER
1103 decode(p_buyer_user, 'Y', p_auctpid, null), -- SURROG_BID_CREATED_TP_ID
1104 decode(p_buyer_user, 'Y', p_auctpcid, null), -- SURROG_BID_CREATED_CONTACT_ID
1105 --decode(p_buyer_user, 'Y', bh.surrog_bid_receipt_date, null), -- SURROG_BID_RECEIPT_DATE
1106 decode(p_buyer_user, 'Y', sysdate, null), -- SURROG_BID_ONLINE_ENTRY_DATE
1107 p_buyer_user, -- SURROG_BID_FLAG
1108 bh.color_sequence_id, -- COLOR_SEQUENCE_ID
1109 decode(p_rebid_flag, 'Y', bh.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
1110 decode(p_rebid_flag, 'Y', bh.bidders_bid_number, null), -- OLD_BIDDERS_BID_NUMBER
1111 decode(p_rebid_flag, 'Y', bh.bid_expiration_date, null), -- OLD_BID_EXPIRATION_DATE
1112 decode(p_rebid_flag, 'Y', bh.min_bid_change, null), -- OLD_MIN_BID_CHANGE
1113 decode(p_rebid_flag, 'Y', bh.bid_status, null), -- OLD_BID_STATUS
1114 decode(p_rebid_flag, 'Y', bh.surrog_bid_receipt_date, null), -- OLD_SURROG_BID_RECEIPT_DATE
1115 'Y', -- REL12_DRAFT_FLAG
1116 decode(p_source_bid_num, 0, null, p_source_bid_num) -- OLD_BID_NUMBER
1117 -- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
1118 -----------------------------------------------------------------------------
1119 -- set submit_stage to TECHNICAL when requoting in commercial stage
1120 , decode(p_rebid_flag, 'Y', -- SUBMIT_STAGE
1121 decode(l_two_part_flag, 'Y',
1122 decode(p_buyer_user, 'Y',
1123 decode(l_technical_evaluation_status, 'COMPLETED', 'TECHNICAL', null), null), null), null)
1124 -----------------------------------------------------------------------------
1125 FROM pon_auction_headers_all ah, pon_bid_headers bh
1126 WHERE ah.auction_header_id = p_auc_header_id
1127 AND bh.bid_number (+) = p_source_bid_num
1128 AND ah.auction_header_id >= bh.auction_header_id (+));
1129
1130 END insert_into_bid_header;
1134 -- PARAMETERS:
1131
1132 -- ======================================================================
1133 -- PROCEDURE: INSERT_INTO_BID_ITEMS PRIVATE
1135 -- p_auc_header_id IN auction_header_id of negotiation
1136 -- p_bid_number IN bid number to insert lines for
1137 -- p_source_bid_num IN source_bid to default from
1138 -- p_tpid IN trading partner id of supplier
1139 -- p_userid IN userid of bid creator
1140 -- p_vensid IN vendor site id to place bid for
1141 -- p_rebid_flag IN flag indicating rebid or not
1142 -- p_restricted_flag IN flag indicating whether certain lines may be restricted
1143 --
1144 -- COMMENT: inserts lines for the new bid, defualting as necessary
1145 -- ======================================================================
1146 PROCEDURE insert_into_bid_items
1147 (
1148 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1149 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1150 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1151 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
1152 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
1153 p_userid IN pon_bid_headers.created_by%TYPE,
1154 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
1155 p_rebid_flag IN VARCHAR2,
1156 p_restricted_flag IN VARCHAR2,
1157 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1158 p_batch_end IN pon_bid_item_prices.line_number%TYPE,
1159 p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE
1160 ) IS
1161 l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
1162 l_blanket VARCHAR2(1);
1163 l_full_qty VARCHAR2(1);
1164 l_enforce_prevrnd_price_flag VARCHAR2(1);
1165 l_prev_rnd_active_bid_number NUMBER;
1166 l_auction_header_id_prev_round NUMBER;
1167 l_unit_price NUMBER;
1168 l_quantity NUMBER;
1169 l_is_paused pon_auction_headers_all.is_paused%TYPE;
1170 l_last_pause_date pon_auction_headers_all.last_pause_date%TYPE;
1171 l_closed_compare_date DATE;
1172 BEGIN
1173
1174 SELECT ah.trading_partner_id,
1175 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
1176 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
1177 enforce_prevrnd_bid_price_flag,
1178 auction_header_id_prev_round,
1179 is_paused,
1180 last_pause_date
1181 INTO l_auctpid,
1182 l_blanket,
1183 l_full_qty,
1184 l_enforce_prevrnd_price_flag,
1185 l_auction_header_id_prev_round,
1186 l_is_paused,
1187 l_last_pause_date
1188 FROM pon_auction_headers_all ah
1189 WHERE ah.auction_header_id = p_auc_header_id;
1190
1191 INSERT INTO pon_bid_item_prices
1192 (
1193 AUCTION_HEADER_ID,
1194 AUCTION_LINE_NUMBER,
1195 BID_NUMBER,
1196 LINE_NUMBER,
1197 ITEM_DESCRIPTION,
1198 CATEGORY_ID,
1199 CATEGORY_NAME,
1200 UOM,
1201 QUANTITY,
1202 PRICE,
1203 MINIMUM_BID_PRICE,
1204 PROMISED_DATE,
1205 NOTE_TO_AUCTION_OWNER,
1206 LANGUAGE_CODE,
1207 CREATION_DATE,
1208 CREATED_BY,
1209 LAST_UPDATE_DATE,
1210 LAST_UPDATED_BY,
1211 AUCTION_CREATION_DATE,
1212 SHIP_TO_LOCATION_ID,
1213 PUBLISH_DATE,
1214 PROXY_BID_LIMIT_PRICE,
1215 PROXY_BID_LIMIT_PRICE_DATE,
1216 BID_CURRENCY_PRICE,
1217 BID_CURRENCY_LIMIT_PRICE,
1218 PROXY_BID_FLAG,
1219 FIRST_BID_PRICE,
1220 UNIT_OF_MEASURE,
1221 HAS_ATTRIBUTES_FLAG,
1222 FREIGHT_TERMS_CODE,
1223 TBD_PRICING_FLAG,
1224 AUC_TRADING_PARTNER_ID,
1225 BID_TRADING_PARTNER_ID,
1226 TOTAL_WEIGHTED_SCORE,
1227 RANK,
1228 PO_MIN_REL_AMOUNT,
1229 PO_BID_MIN_REL_AMOUNT,
1230 PRICE_BREAK_TYPE,
1231 HAS_SHIPMENTS_FLAG,
1232 IS_CHANGED_LINE_FLAG,
1233 HAS_PRICE_DIFFERENTIALS_FLAG,
1234 PRICE_DIFF_SHIPMENT_NUMBER,
1235 BID_CURRENCY_TRANS_PRICE,
1236 UNIT_PRICE,
1237 BID_CURRENCY_UNIT_PRICE,
1238 GROUP_AMOUNT,
1239 HAS_BID_PAYMENTS_FLAG,
1240 ADVANCE_AMOUNT,
1241 BID_CURR_ADVANCE_AMOUNT,
1242 RECOUPMENT_RATE_PERCENT,
1243 PROGRESS_PYMT_RATE_PERCENT,
1244 RETAINAGE_RATE_PERCENT,
1245 MAX_RETAINAGE_AMOUNT,
1246 BID_CURR_MAX_RETAINAGE_AMT,
1247 OLD_NO_OF_PAYMENTS,
1248 OLD_PRICE,
1249 OLD_BID_CURRENCY_UNIT_PRICE,
1250 OLD_BID_CURRENCY_PRICE,
1251 OLD_BID_CURRENCY_LIMIT_PRICE,
1252 OLD_PO_BID_MIN_REL_AMOUNT,
1253 OLD_QUANTITY,
1254 OLD_PUBLISH_DATE,
1255 OLD_PROMISED_DATE,
1256 OLD_NOTE_TO_AUCTION_OWNER,
1257 HAS_BID_FLAG,
1258 OLD_BID_CURR_ADVANCE_AMOUNT,
1259 OLD_RECOUPMENT_RATE_PERCENT,
1260 OLD_PROGRESS_PYMT_RATE_PERCENT,
1261 OLD_RETAINAGE_RATE_PERCENT,
1262 OLD_BID_CURR_MAX_RETAINAGE_AMT,
1263 COPY_PRICE_FOR_PROXY_FLAG,
1264 BID_START_PRICE,
1265 HAS_QUANTITY_TIERS
1266 )
1267 (SELECT
1268 al.auction_header_id, -- AUCTION_HEADER_ID
1269 al.line_number, -- AUCTION_LINE_NUMBER
1270 p_bid_number, -- BID_NUMBER
1271 al.line_number, -- LINE_NUMBER
1272 al.item_description, -- ITEM_DESCRIPTION
1273 al.category_id, -- CATEGORY_ID
1274 al.category_name, -- CATEGORY_NAME
1275 al.uom_code, -- UOM
1276 decode(al.modified_date-old_al.modified_date,
1277 0, bl.quantity, decode(l_blanket, 'Y', null,
1278 decode(l_full_qty, 'Y', al.quantity,
1282 decode(al.modified_date-old_al.modified_date,
1279 decode(al.group_type, 'LOT_LINE', al.quantity,
1280 decode(al.order_type_lookup_code, 'AMOUNT',
1281 al.quantity, null))))), -- QUANTITY
1283 0, bl.price, null), -- PRICE
1284 decode(al.modified_date-old_al.modified_date,
1285 0, bl.minimum_bid_price, null), -- MINIMUM_BID_PRICE
1286 decode(al.modified_date-old_al.modified_date,
1287 0, bl.promised_date, null), -- PROMISED_DATE
1288 decode(al.modified_date-old_al.modified_date,
1289 0, bl.note_to_auction_owner, null), -- NOTE_TO_AUCTION_OWNER
1290 userenv('LANG'), -- LANGUAGE_CODE
1291 SYSDATE, -- CREATION_DATE
1292 p_userid, -- CREATED_BY
1293 SYSDATE, -- LAST_UPDATE_DATE
1294 p_userid, -- LAST_UPDATED_BY
1295 al.auction_creation_date, -- AUCTION_CREATION_DATE
1296 al.ship_to_location_id, -- SHIP_TO_LOCATION_ID
1297 decode(al.modified_date-old_al.modified_date,
1298 0, bl.publish_date, null), -- PUBLISH_DATE
1299 decode(al.modified_date-old_al.modified_date,
1300 0, bl.proxy_bid_limit_price, null), -- PROXY_BID_LIMIT_PRICE
1301 decode(al.modified_date-old_al.modified_date,
1302 0, bl.proxy_bid_limit_price_date, null), -- PROXY_BID_LIMIT_PRICE_DATE
1303 decode(al.modified_date-old_al.modified_date,
1304 0, bl.bid_currency_price, null), -- BID_CURRENCY_PRICE
1305 decode(al.modified_date-old_al.modified_date,
1306 0, bl.bid_currency_limit_price, null), -- BID_CURRENCY_LIMIT_PRICE
1307 'N', -- PROXY_BID_FLAG
1308 decode(al.modified_date-old_al.modified_date,
1309 0, bl.first_bid_price, null), -- FIRST_BID_PRICE
1310 al.unit_of_measure, -- UNIT_OF_MEASURE
1311 al.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
1312 al.freight_terms_code, -- FREIGHT_TERMS_CODE
1313 'N', -- TBD_PRICING_FLAG
1314 l_auctpid, -- AUC_TRADING_PARTNER_ID
1315 p_tpid, -- BID_TRADING_PARTNER_ID
1316 decode(al.modified_date-old_al.modified_date,
1317 0, bl.total_weighted_score, null), -- TOTAL_WEIGHTED_SCORE
1318 decode(p_rebid_flag, 'Y', bl.rank, null), -- RANK
1319 decode(al.modified_date-old_al.modified_date,
1320 0, bl.po_min_rel_amount, null), -- PO_MIN_REL_AMOUNT
1321 decode(al.modified_date-old_al.modified_date,
1322 0, bl.po_bid_min_rel_amount, null), -- PO_BID_MIN_REL_AMOUNT
1323 al.price_break_type, -- PRICE_BREAK_TYPE
1324 decode(al.modified_date-old_al.modified_date,
1325 0, bl.has_shipments_flag, al.has_shipments_flag), -- HAS_SHIPMENTS_FLAG
1326 -- Rebid: set changed_line to N
1327 -- Otherwise it is the same as the has_bid_flag
1328 decode(p_rebid_flag, 'Y', 'N',
1329 decode(al.modified_date-old_al.modified_date, 0,
1330 nvl(bl.has_bid_flag, 'N'), 'N')), -- IS_CHANGED_LINE_FLAG
1331 al.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
1332 al.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
1333 decode(al.modified_date-old_al.modified_date,
1334 0, bl.bid_currency_trans_price, null), -- BID_CURRENCY_TRANS_PRICE
1335 decode(al.modified_date-old_al.modified_date,
1336 0, bl.unit_price, null), -- UNIT_PRICE
1337 decode(al.modified_date-old_al.modified_date,
1338 0, bl.bid_currency_unit_price, null), -- BID_CURRENCY_UNIT_PRICE
1339 decode(al.modified_date-old_al.modified_date,
1340 0, bl.group_amount, null), -- GROUP_AMOUNT
1341 decode(g_copy_only_from_auc, 'Y', al.has_payments_flag,
1342 decode(al.modified_date-old_al.modified_date,0,bl.has_bid_payments_flag,al.has_payments_flag
1343 )
1344 ),--HAS_BID_PAYMENTS_FLAG
1345 decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.advance_amount,al.advance_amount
1346 )
1347 , al.advance_amount
1348 ),--ADVANCE_AMOUNT
1349 decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.bid_curr_advance_amount
1350 ,round(al.advance_amount * g_bid_rate, g_curr_prec)
1351 )
1352 , round(al.advance_amount * g_bid_rate, g_curr_prec)
1353 ),--BID_CURR_ADVANCE_AMOUNT
1354 decode(al.modified_date-old_al.modified_date, 0,decode(g_recoupment_negotiable,'Y',bl.recoupment_rate_percent
1355 ,al.recoupment_rate_percent
1356 )
1357 , al.recoupment_rate_percent
1358 ),--RECOUPMENT_RATE_PERCENT
1359 decode(al.modified_date-old_al.modified_date, 0,decode(g_prog_pymt_negotiable,'Y',bl.progress_pymt_rate_percent
1360 ,al.progress_pymt_rate_percent
1361 )
1362 , al.progress_pymt_rate_percent
1363 ),--PROGRESS_PYMT_RATE_PERCENT
1364 decode(al.modified_date-old_al.modified_date, 0,decode(g_rtng_negotiable,'Y',bl.retainage_rate_percent,al.retainage_rate_percent
1365 )
1366 , al.retainage_rate_percent
1367 ),--RETAINAGE_RATE_PERCENT
1368 decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.max_retainage_amount,al.max_retainage_amount
1369 )
1370 , al.max_retainage_amount
1371 ),--MAX_RETAINAGE_AMOUNT
1375 , round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
1372 decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.bid_curr_max_retainage_amt
1373 , round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
1374 )
1376 ),--BID_CURR_MAX_RETAINAGE_AMT
1377 decode(p_rebid_flag, 'Y', (select count(1) from pon_bid_payments_shipments
1378 where bid_number=bl.bid_number and bid_line_number=bl.line_number)
1379 ,null
1380 ), --OLD_NO_OF_PAYMENTS
1381 decode(p_rebid_flag, 'Y', bl.price, null), -- OLD_PRICE
1382 decode(p_rebid_flag, 'Y', bl.bid_currency_unit_price, null),-- OLD_BID_CURRENCY_UNIT_PRICE
1383 decode(p_rebid_flag, 'Y', bl.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
1384 decode(p_rebid_flag, 'Y', bl.bid_currency_limit_price, null), -- OLD_BID_CURRENCY_LIMIT_PRICE
1385 decode(p_rebid_flag, 'Y', bl.po_bid_min_rel_amount, null), -- OLD_PO_BID_MIN_REL_AMOUNT
1386 decode(p_rebid_flag, 'Y', bl.quantity, null), -- OLD_QUANTITY
1387 decode(p_rebid_flag, 'Y', bl.publish_Date, null), -- OLD_PUBLISH_DATE
1388 decode(p_rebid_flag, 'Y', bl.promised_Date, null), -- OLD_PROMISED_DATE
1389 decode(p_rebid_flag, 'Y', bl.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
1390 -- If the line was modified, set to N, else set to source has_bid_flag
1391 -- If the source has_bid_flag is null, set to N since there was no source bid
1392 decode(al.modified_date-old_al.modified_date, 0,
1393 nvl(bl.has_bid_flag, 'N'), 'N'), -- HAS_BID_FLAG
1394 decode(p_rebid_flag, 'Y', bl.bid_curr_advance_amount, null), -- OLD_BID_CURR_ADVANCE_AMOUNT
1395 decode(p_rebid_flag, 'Y', bl.recoupment_rate_percent, null), -- OLD_RECOUPMENT_RATE_PERCENT
1396 decode(p_rebid_flag, 'Y', bl.progress_pymt_rate_percent, null), -- OLD_PROGRESS_PYMT_RATE_PERCENT
1397 decode(p_rebid_flag, 'Y', bl.retainage_rate_percent, null), -- OLD_RETAINAGE_RATE_PERCENT
1398 decode(p_rebid_flag, 'Y', bl.bid_curr_max_retainage_amt, null), -- OLD_BID_CURR_MAX_RETAINAGE_AMT
1399 decode(p_rebid_flag, 'Y',
1400 decode(sign(bl.proxy_bid_limit_price-bl.price), -1, 'Y', 'N'), 'N'), -- COPY_PRICE_FOR_PROXY_FLAG
1401 al.bid_start_price,
1402 decode(al.modified_date-old_al.modified_date,
1403 0, bl.has_quantity_tiers, al.has_quantity_tiers) -- HAS_quantity_tiers
1404 FROM pon_auction_item_prices_all al,
1405 pon_auction_item_prices_all old_al,
1406 pon_bid_item_prices bl
1407 WHERE al.auction_header_id = p_auc_header_id
1408 AND bl.bid_number(+) = p_source_bid_num
1409 AND bl.line_number(+) = al.line_number
1410 AND old_al.auction_header_id (+) = bl.auction_header_id
1411 AND old_al.line_number (+) = bl.line_number
1412 AND al.line_number BETWEEN p_batch_start AND p_batch_end);
1413
1414 -- determine if there exists an active bid in the previous round
1415 -- for the supplier/contact/site
1416 -- this will be used to populate the bid_start_price column
1417 IF (l_enforce_prevrnd_price_flag = 'Y') THEN
1418 BEGIN
1419 SELECT MAX(bid_number)
1420 INTO l_prev_rnd_active_bid_number
1421 FROM pon_bid_headers bh
1422 WHERE bh.auction_header_id = l_auction_header_id_prev_round
1423 AND bh.trading_partner_id = p_tpid
1424 AND bh.trading_partner_contact_id = p_tpcid
1425 AND bh.bid_status ='ACTIVE'
1426 AND NVL(bh.vendor_site_id, -1) = NVL(p_vensid, -1);
1427
1428 IF l_prev_rnd_active_bid_number IS NOT NULL THEN
1429 -- if active bid exists then
1430 -- update the bid_start_price for the current bid lines
1431 -- using values from the previous round auction/bid
1432 UPDATE pon_bid_item_prices bl
1433 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)
1434 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl1
1435 WHERE al.auction_header_id = l_auction_header_id_prev_round
1436 AND al.line_number = bl.line_number
1437 AND al.line_number = bl1.line_number
1438 AND bl1.bid_number = l_prev_rnd_active_bid_number), bid_start_price)
1439 WHERE bl.bid_number = p_bid_number;
1440 END IF;
1441
1442 EXCEPTION
1443 WHEN NO_DATA_FOUND THEN
1444 l_enforce_prevrnd_price_flag := 'N';
1445 END;
1446 END IF;
1447 -- end 'start price for multi round negotiations' code
1448
1449 -- Delete excluded lines
1450 IF (p_restricted_flag = 'Y') THEN
1451
1452 DELETE FROM pon_bid_item_prices bl
1453 WHERE bl.bid_number = p_bid_number
1454 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
1455 AND EXISTS
1456 (SELECT le.line_number
1457 FROM pon_party_line_exclusions le, pon_auction_item_prices_all al
1458 WHERE al.auction_header_id = p_auc_header_id
1459 AND al.line_number = bl.line_number
1460 AND le.auction_header_id = al.auction_header_id
1461 AND le.line_number = nvl(al.parent_line_number, al.line_number)
1462 AND le.trading_partner_id = p_tpid
1463 AND le.vendor_site_id = p_vensid);
1464 END IF;
1465
1466
1467 -- In case of a non-rebid and non surrogate bid do not copy over
1468 -- closed lines.
1472 ', l_closed_compare_date = ' || to_char (l_closed_compare_date, 'dd-mon-yyyy hh24:mi:ss') ||
1469 log_message ('insert_into_bid_items', 'p_rebid_flag = ' || p_rebid_flag ||
1470 ', p_surrog_bid_flag = ' || p_surrog_bid_flag ||
1471 ', l_is_paused = ' || l_is_paused ||
1473 ', l_last_pause_date = ' || to_char (l_last_pause_date, 'dd-mon-yyyy hh24:mi:ss'));
1474
1475 IF ( nvl (p_rebid_flag, 'N') = 'N' AND nvl (p_surrog_bid_flag, 'N') = 'N') THEN
1476
1477 log_message ('insert_into_bid_items', 'This is not a rebid and this is not a surrogate bid.');
1478
1479 IF (nvl (l_is_paused, 'N') = 'Y') THEN
1480 l_closed_compare_date := l_last_pause_date;
1481 ELSE
1482 l_closed_compare_date := sysdate;
1483 END IF;
1484
1485 DELETE FROM pon_bid_item_prices bl
1486 WHERE bl.bid_number = p_bid_number
1487 AND bl.line_number BETWEEN p_batch_start AND p_batch_end
1488 AND EXISTS (SELECT al.line_number
1489 FROM pon_auction_item_prices_all al
1490 WHERE al.auction_header_id = p_auc_header_id
1491 AND al.line_number = bl.line_number
1492 AND al.close_bidding_date < l_closed_compare_date);
1493 END IF;
1494
1495 END insert_into_bid_items;
1496
1497 -- ======================================================================
1498 -- PROCEDURE: INSERT_INTO_HEADER_ATTRIBUTES PRIVATE
1499 -- PARAMETERS:
1500 -- p_auc_header_id IN auction_header_id of negotiation
1501 -- p_bid_number IN bid number to insert attributes for
1502 -- p_source_bid_num IN source_bid to default from
1503 -- p_userid IN userid of bid creator
1504 -- p_rebid_flag IN flag indicating rebid or not
1505 --
1506 -- COMMENT: inserts header attributes for the new bid, defualting as necessary
1507 -- ======================================================================
1508 PROCEDURE insert_into_header_attributes
1509 (
1510 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1511 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1512 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1513 p_userid IN pon_bid_headers.created_by%TYPE,
1514 p_rebid_flag IN VARCHAR2
1515 ) IS
1516
1517 l_has_scoring_teams_flag pon_auction_headers_all.has_scoring_teams_flag%TYPE;
1518
1519 BEGIN
1520
1521 -- Bug 5046909 - Determine if team scoring is enabled
1522 -- If team scoring is enabled, we do not copy the score over from the
1523 -- earlier bid. If team scoring is not enabled, we copy the score over
1524 -- for a header attribute only if the attribute has not been modified.
1525 -- In both cases, scores by individual scorers are not copied over -
1526 -- only the final one is
1527 -- Adding this SELECT from auction_headers_all - ideally, this should
1528 -- be combined and only one call made for the entire flow...will log a
1529 -- tracking bug for this
1530
1531
1532 SELECT has_scoring_teams_flag
1533 INTO l_has_scoring_teams_flag
1534 FROM pon_auction_headers_all
1535 WHERE auction_header_id = p_auc_header_id;
1536
1537 -- Insert header attributes
1538 INSERT INTO pon_bid_attribute_values
1539 (
1540 AUCTION_HEADER_ID,
1541 AUCTION_LINE_NUMBER,
1542 BID_NUMBER,
1543 LINE_NUMBER,
1544 ATTRIBUTE_NAME,
1545 DATATYPE,
1546 VALUE,
1547 CREATION_DATE,
1548 CREATED_BY,
1549 LAST_UPDATE_DATE,
1550 LAST_UPDATED_BY,
1551 SCORE,
1552 SEQUENCE_NUMBER,
1553 ATTR_LEVEL,
1554 WEIGHTED_SCORE,
1555 ATTR_GROUP_SEQ_NUMBER,
1556 ATTR_DISP_SEQ_NUMBER,
1557 OLD_VALUE
1558 )
1559 (SELECT
1560 aa.auction_header_id, -- AUCTION_HEADER_ID
1561 aa.line_number, -- AUCTION_LINE_NUMBER
1562 p_bid_number, -- BID_NUMBER
1563 aa.line_number, -- LINE_NUMBER
1564 aa.attribute_name, -- ATTRIBUTE_NAME
1565 aa.datatype, -- DATATYPE
1566 decode(aa.modified_date-old_aa.modified_date,
1567 0, ba.value, null), -- VALUE
1568 sysdate, -- CREATION_DATE
1569 p_userid, -- CREATED_BY
1570 sysdate, -- LAST_UPDATE_DATE
1571 p_userid, -- LAST_UPDATED_BY
1572 DECODE(l_has_scoring_teams_flag, 'N',decode(aa.modified_date-old_aa.modified_date,
1573 0, ba.score, null),'Y', NULL), -- SCORE
1574 aa.sequence_number, -- SEQUENCE_NUMBER
1575 aa.attr_level, -- ATTR_LEVEL
1576 decode(l_has_scoring_teams_flag, 'N', decode(aa.modified_date-old_aa.modified_date,
1577 0, ba.weighted_score, null), 'Y', NULL), -- WEIGHTED_SCORE
1578 aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
1579 aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
1580 decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
1581 FROM pon_auction_attributes aa,
1582 pon_bid_attribute_values ba,
1583 pon_auction_attributes old_aa
1584 WHERE aa.auction_header_id = p_auc_header_id
1585 AND aa.line_number= -1
1586 AND ba.bid_number (+) = p_source_bid_num
1587 AND ba.line_number (+) = aa.line_number
1588 AND ba.sequence_number (+) = aa.sequence_number
1589 AND ba.auction_header_id = old_aa.auction_header_id (+)
1590 AND ba.line_number = old_aa.line_number (+)
1591 AND ba.sequence_number = old_aa.sequence_number (+));
1592
1593 END insert_into_header_attributes;
1594
1598 -- p_auc_header_id IN auction_header_id of negotiation
1595 -- ======================================================================
1596 -- PROCEDURE: INSERT_INTO_LINE_ATTRIBUTES PRIVATE
1597 -- PARAMETERS:
1599 -- p_bid_number IN bid number to insert attributes for
1600 -- p_source_bid_num IN source_bid to default from
1601 -- p_userid IN userid of bid creator
1602 -- p_rebid_flag IN flag indicating rebid or not
1603 --
1604 -- COMMENT: inserts line attributes for the new bid, defualting as necessary
1605 -- ======================================================================
1606 PROCEDURE insert_into_line_attributes
1607 (
1608 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1609 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1610 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1611 p_userid IN pon_bid_headers.created_by%TYPE,
1612 p_rebid_flag IN VARCHAR2,
1613 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1614 p_batch_end IN pon_bid_item_prices.line_number%TYPE
1615 ) IS
1616 BEGIN
1617
1618 -- Insert line attributes
1619 INSERT INTO pon_bid_attribute_values
1620 (
1621 AUCTION_HEADER_ID,
1622 AUCTION_LINE_NUMBER,
1623 BID_NUMBER,
1624 LINE_NUMBER,
1625 ATTRIBUTE_NAME,
1626 DATATYPE,
1627 VALUE,
1628 CREATION_DATE,
1629 CREATED_BY,
1630 LAST_UPDATE_DATE,
1631 LAST_UPDATED_BY,
1632 SCORE,
1633 SEQUENCE_NUMBER,
1634 ATTR_LEVEL,
1635 WEIGHTED_SCORE,
1636 ATTR_GROUP_SEQ_NUMBER,
1637 ATTR_DISP_SEQ_NUMBER,
1638 OLD_VALUE
1639 )
1640 -- NOTE: we check the has_bid_flag because it is 'N' if
1641 -- the line has been modified since the defaulting happened
1642 (SELECT
1643 aa.auction_header_id, -- AUCTION_HEADER_ID
1644 aa.line_number, -- AUCTION_LINE_NUMBER
1645 p_bid_number, -- BID_NUMBER
1646 aa.line_number, -- LINE_NUMBER
1647 aa.attribute_name, -- ATTRIBUTE_NAME
1648 aa.datatype, -- DATATYPE
1649 decode(bl.has_bid_flag, 'Y', ba.value, null), -- VALUE
1650 sysdate, -- CREATION_DATE
1651 p_userid, -- CREATED_BY
1652 sysdate, -- LAST_UPDATE_DATE
1653 p_userid, -- LAST_UPDATED_BY
1654 decode(bl.has_bid_flag, 'Y', ba.score, null), -- SCORE
1655 aa.sequence_number, -- SEQUENCE_NUMBER
1656 aa.attr_level, -- ATTR_LEVEL
1657 decode(bl.has_bid_flag, 'Y', ba.weighted_score, null), -- WEIGHTED_SCORE
1658 aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
1659 aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
1660 decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
1661 FROM pon_auction_attributes aa,
1662 pon_bid_attribute_values ba,
1663 pon_bid_item_prices bl
1664 WHERE aa.auction_header_id = p_auc_header_id
1665 AND aa.line_number > 0
1666 AND bl.bid_number = p_bid_number
1667 AND bl.line_number = aa.line_number
1668 AND ba.bid_number (+) = p_source_bid_num
1669 AND ba.line_number (+) = aa.line_number
1670 AND ba.sequence_number (+) = aa.sequence_number
1671 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
1672
1673 END insert_into_line_attributes;
1674
1675 -- ======================================================================
1676 -- PROCEDURE: INSERT_INTO_PRICE_FACTORS PRIVATE
1677 -- PARAMETERS:
1678 -- p_auc_header_id IN auction_header_id of negotiation
1679 -- p_bid_number IN bid number to insert price factors for
1680 -- p_source_bid_num IN source_bid to default from
1681 -- p_userid IN userid of bid creator
1682 -- p_supp_seq_number IN sequence number if supplier was invited
1683 -- p_rebid_flag IN flag indicating rebid or not
1684 --
1685 -- COMMENT: insert price factors for the new bid, defualting as necessary
1686 -- ======================================================================
1687 PROCEDURE insert_into_price_factors
1688 (
1689 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1690 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1691 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1692 p_userid IN pon_bid_headers.created_by%TYPE,
1693 p_supp_seq_number IN pon_pf_supplier_values.supplier_seq_number%TYPE,
1694 p_rebid_flag IN VARCHAR2,
1695 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1696 p_batch_end IN pon_bid_item_prices.line_number%TYPE
1697 ) IS
1698 l_price_prec pon_bid_headers.number_price_decimals%TYPE;
1699 l_curr_prec fnd_currencies.precision%TYPE;
1700 l_rate pon_bid_headers.rate%TYPE;
1701 l_supplier_view pon_auction_headers_all.supplier_view_type%TYPE;
1702 l_pf_type pon_auction_headers_all.pf_type_allowed%TYPE;
1703 BEGIN
1704
1705 -- Get bid currency precisions and rate
1706 SELECT bh.number_price_decimals,
1707 cu.precision,
1708 bh.rate
1709 INTO l_price_prec,
1710 l_curr_prec,
1711 l_rate
1712 FROM pon_bid_headers bh,
1713 fnd_currencies cu
1714 WHERE bh.bid_number = p_bid_number
1715 AND cu.currency_code = bh.bid_currency_code;
1716
1717 -- Get the price factor type info
1718 SELECT ah.supplier_view_type, ah.pf_type_allowed
1719 INTO l_supplier_view, l_pf_type
1720 FROM pon_auction_headers_all ah
1721 WHERE ah.auction_header_id = p_auc_header_id;
1722
1723 -- copy over all supplier price factors, including line price
1724 -- do not copy line price pf for lines with display_price_factors_flag = N
1725 IF (l_supplier_view <> 'UNTRANSFORMED') THEN
1726
1727 INSERT INTO pon_bid_price_elements
1728 (
1729 BID_NUMBER,
1730 LINE_NUMBER,
1731 PRICE_ELEMENT_TYPE_ID,
1735 BID_CURRENCY_VALUE,
1732 AUCTION_HEADER_ID,
1733 PRICING_BASIS,
1734 AUCTION_CURRENCY_VALUE,
1736 SEQUENCE_NUMBER,
1737 CREATION_DATE,
1738 CREATED_BY,
1739 LAST_UPDATE_DATE,
1740 LAST_UPDATED_BY,
1741 PF_TYPE,
1742 OLD_BID_CURRENCY_VALUE
1743 )
1744 (SELECT
1745 p_bid_number, -- BID_NUMBER
1746 apf.line_number, -- LINE_NUMBER
1747 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
1748 p_auc_header_id, -- AUCTION_HEADER_ID
1749 apf.pricing_basis, -- PRICING_BASIS
1750 decode(bl.has_bid_flag, 'Y',
1751 bpf.auction_currency_value, null), -- AUCTION_CURRENCY_VALUE
1752 decode(bl.has_bid_flag, 'Y',
1753 bpf.bid_currency_value, null), -- BID_CURRENCY_VALUE
1754 apf.sequence_number, -- SEQUENCE_NUMBER
1755 sysdate, -- CREATION_DATE
1756 p_userid, -- CREATED_BY
1757 sysdate, -- LAST_UPDATE_DATE
1758 p_userid, -- LAST_UPDATED_BY
1759 apf.pf_type, -- PF_TYPE
1760 decode(p_rebid_flag, 'Y', bpf.bid_currency_value, null) -- OLD_BID_CURRENCY_VALUE
1761 FROM pon_price_elements apf,
1762 pon_bid_price_elements bpf,
1763 pon_bid_item_prices bl
1764 WHERE apf.auction_header_id = p_auc_header_id
1765 AND apf.pf_type = 'SUPPLIER' -- only for supplier price factors
1766 AND bl.bid_number = p_bid_number
1767 AND bl.line_number = apf.line_number
1768 AND bl.display_price_factors_flag = 'Y' -- only for lines with price factors
1769 AND bpf.bid_number (+) = p_source_bid_num
1770 AND bpf.line_number (+) = apf.line_number
1771 AND bpf.price_element_type_id (+) = apf.price_element_type_id
1772 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
1773 END IF;
1774
1775 -- copy over all buyer price factors that can be displayed to the supplier
1776 -- display flag checked and have nonzero value
1777 -- need to populate bid_currency_value, rounding as necessary
1778 IF (p_supp_seq_number IS NOT null AND l_pf_type <> 'SUPPLIER') THEN
1779
1780 INSERT INTO pon_bid_price_elements
1781 (
1782 BID_NUMBER,
1783 LINE_NUMBER,
1784 PRICE_ELEMENT_TYPE_ID,
1785 AUCTION_HEADER_ID,
1786 PRICING_BASIS,
1787 AUCTION_CURRENCY_VALUE,
1788 BID_CURRENCY_VALUE,
1789 SEQUENCE_NUMBER,
1790 CREATION_DATE,
1791 CREATED_BY,
1792 LAST_UPDATE_DATE,
1793 LAST_UPDATED_BY,
1794 PF_TYPE,
1795 OLD_BID_CURRENCY_VALUE
1796 )
1797 (SELECT
1798 p_bid_number, -- BID_NUMBER
1799 apf.line_number, -- LINE_NUMBER
1800 apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
1801 p_auc_header_id, -- AUCTION_HEADER_ID
1802 apf.pricing_basis, -- PRICING_BASIS
1803 pf.value, -- AUCTION_CURRENCY_VALUE
1804 decode(apf.pricing_basis,
1805 'PER_UNIT', round(pf.value * l_rate, l_price_prec),
1806 'FIXED_AMOUNT', round(pf.value * l_rate, l_curr_prec),
1807 'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
1808 apf.sequence_number, -- SEQUENCE_NUMBER
1809 sysdate, -- CREATION_DATE
1810 p_userid, -- CREATED_BY
1811 sysdate, -- LAST_UPDATE_DATE
1812 p_userid, -- LAST_UPDATED_BY
1813 apf.pf_type, -- PF_TYPE
1814 null -- OLD_BID_CURRENCY_VALUE
1815 FROM pon_price_elements apf,
1816 pon_pf_supplier_values pf,
1817 pon_bid_item_prices bl
1818 WHERE apf.auction_header_id = p_auc_header_id
1819 AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
1820 AND apf.display_to_suppliers_flag = 'Y'
1821 AND bl.bid_number = p_bid_number
1822 AND bl.line_number = apf.line_number
1823 AND bl.display_price_factors_flag = 'Y'
1824 AND pf.auction_header_id = apf.auction_header_id
1825 AND pf.line_number = apf.line_number
1826 AND pf.pf_seq_number = apf.sequence_number
1827 AND pf.supplier_seq_number = p_supp_seq_number
1828 AND nvl(pf.value, 0) <> 0
1829 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
1830 END IF;
1831
1832 END insert_into_price_factors;
1833
1834 -- ======================================================================
1835 -- PROCEDURE: INSERT_INTO_PRICE_TIERS PRIVATE
1836 -- PARAMETERS:
1837 -- p_auc_header_id IN auction_header_id of negotiation
1838 -- p_bid_number IN bid number to insert price breaks for
1839 -- p_source_bid_num IN source_bid to default from
1840 -- p_userid IN userid of bid creator
1841 -- p_rebid_flag IN flag indicating rebid or not
1842 --
1843 -- COMMENT: inserts price tiers for the new bid, defualting as necessary
1844 -- ======================================================================
1845 PROCEDURE insert_into_price_tiers
1846 (
1847 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1848 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1849 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
1850 p_userid IN pon_bid_headers.created_by%TYPE,
1851 p_rebid_flag IN VARCHAR2,
1852 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
1853 p_batch_end IN pon_bid_item_prices.line_number%TYPE
1854 ) IS
1855 BEGIN
1856
1857 -- Get all auction side price breaks for modified lines that had a bid
1858 -- or for unmodified lines that had no bid
1859 INSERT INTO pon_bid_shipments
1860 (
1861 BID_NUMBER,
1862 LINE_NUMBER,
1863 SHIPMENT_NUMBER,
1864 AUCTION_HEADER_ID,
1865 AUCTION_LINE_NUMBER,
1866 AUCTION_SHIPMENT_NUMBER,
1867 SHIPMENT_TYPE,
1868 SHIP_TO_ORGANIZATION_ID,
1872 PRICE_TYPE,
1869 SHIP_TO_LOCATION_ID,
1870 QUANTITY,
1871 MAX_QUANTITY,
1873 PRICE,
1874 BID_CURRENCY_PRICE,
1875 PRICE_DISCOUNT,
1876 EFFECTIVE_START_DATE,
1877 EFFECTIVE_END_DATE,
1878 CREATION_DATE,
1879 CREATED_BY,
1880 LAST_UPDATE_DATE,
1881 LAST_UPDATED_BY,
1882 LAST_UPDATE_LOGIN,
1883 HAS_PRICE_DIFFERENTIALS_FLAG,
1884 UNIT_PRICE,
1885 BID_CURRENCY_UNIT_PRICE
1886 )
1887 (SELECT
1888 p_bid_number, -- BID_NUMBER
1889 apb.line_number, -- LINE_NUMBER
1890 apb.shipment_number+1, -- SHIPMENT_NUMBER
1891 p_auc_header_id, -- AUCTION_HEADER_ID
1892 apb.line_number, -- AUCTION_LINE_NUMBER
1893 apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
1894 apb.shipment_type, -- SHIPMENT_TYPE
1895 apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
1896 apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
1897 apb.quantity, -- QUANTITY
1898 apb.max_quantity, --- MAX_QUANTITY
1899 'PRICE', -- PRICE_TYPE
1900 apb.price, -- PRICE
1901 null, -- BID_CURRENCY_PRICE
1902 null, -- PRICE_DISCOUNT
1903 apb.effective_start_date, -- EFFECTIVE_START_DATE
1904 apb.effective_end_date, -- EFFECTIVE_END_DATE
1905 sysdate, -- CREATION_DATE
1906 p_userid, -- CREATED_BY
1907 sysdate, -- LAST_UPDATE_DATE
1908 p_userid, -- LAST_UPDATED_BY
1909 null, -- LAST_UPDATE_LOGIN
1910 apb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
1911 null, -- UNIT_PRICE
1912 null -- BID_CURRENCY_UNIT_PRICE
1913 FROM pon_auction_shipments_all apb,
1914 pon_bid_item_prices bl
1915 WHERE apb.auction_header_id = p_auc_header_id
1916 AND bl.bid_number = p_bid_number
1917 AND bl.line_number = apb.line_number
1918 -- we only insert those price breaks for which the line was modified
1919 -- or had no previosu bid on it - has_bid_flag = N in both cases
1920 AND bl.has_bid_flag = 'N'
1921 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
1922
1923 -- Get only-bid-side price breaks for unmodified lines
1924 INSERT INTO pon_bid_shipments
1925 (
1926 BID_NUMBER,
1927 LINE_NUMBER,
1928 SHIPMENT_NUMBER,
1929 AUCTION_HEADER_ID,
1930 AUCTION_LINE_NUMBER,
1931 AUCTION_SHIPMENT_NUMBER,
1932 SHIPMENT_TYPE,
1933 SHIP_TO_ORGANIZATION_ID,
1934 SHIP_TO_LOCATION_ID,
1935 QUANTITY,
1936 MAX_QUANTITY,
1937 PRICE_TYPE,
1938 PRICE,
1939 BID_CURRENCY_PRICE,
1940 PRICE_DISCOUNT,
1941 EFFECTIVE_START_DATE,
1942 EFFECTIVE_END_DATE,
1943 CREATION_DATE,
1944 CREATED_BY,
1945 LAST_UPDATE_DATE,
1946 LAST_UPDATED_BY,
1947 LAST_UPDATE_LOGIN,
1948 HAS_PRICE_DIFFERENTIALS_FLAG,
1949 UNIT_PRICE,
1950 BID_CURRENCY_UNIT_PRICE,
1951 OLD_BID_CURRENCY_UNIT_PRICE,
1952 OLD_BID_CURRENCY_PRICE,
1953 OLD_PRICE_DISCOUNT,
1954 OLD_SHIP_TO_ORG_ID,
1955 OLD_SHIP_TO_LOC_ID,
1956 OLD_EFFECTIVE_START_DATE,
1957 OLD_EFFECTIVE_END_DATE,
1958 OLD_QUANTITY,
1959 OLD_MAX_QUANTITY,
1960 OLD_PRICE_TYPE
1961 )
1962 (SELECT
1963 p_bid_number, -- BID_NUMBER
1964 bpb.line_number, -- LINE_NUMBER
1965 bpb.shipment_number, -- SHIPMENT_NUMBER
1966 p_auc_header_id, -- AUCTION_HEADER_ID
1967 bpb.line_number, -- AUCTION_LINE_NUMBER
1968 bpb.auction_shipment_number,-- AUCTION_SHIPMENT_NUMBER
1969 bpb.shipment_type, -- SHIPMENT_TYPE
1970 bpb.ship_to_organization_id,-- SHIP_TO_ORGANIZATION_ID
1971 bpb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
1972 bpb.quantity, -- QUANTITY
1973 bpb.max_quantity, -- MAX_QUANTITY
1974 bpb.price_type, -- PRICE_TYPE
1975 bpb.price, -- PRICE
1976 bpb.bid_currency_price, -- BID_CURRENCY_PRICE
1977 bpb.price_discount, -- PRICE_DISCOUNT
1978 bpb.effective_start_date, -- EFFECTIVE_START_DATE
1979 bpb.effective_end_date, -- EFFECTIVE_END_DATE
1980 sysdate, -- CREATION_DATE
1981 p_userid, -- CREATED_BY
1982 sysdate, -- LAST_UPDATE_DATE
1983 p_userid, -- LAST_UPDATED_BY
1984 null, -- LAST_UPDATE_LOGIN
1985 bpb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
1986 bpb.unit_price, -- UNIT_PRICE
1987 bpb.bid_currency_unit_price,-- BID_CURRENCY_UNIT_PRICE
1988 decode(p_rebid_flag, 'Y', bpb.bid_currency_unit_price, null), -- OLD_BID_CURRENCY_UNIT_PRICE
1989 decode(p_rebid_flag, 'Y', bpb.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
1990 decode(p_rebid_flag, 'Y', bpb.price_discount, null), -- OLD_PRICE_DISCOUNT
1991 decode(p_rebid_flag, 'Y', bpb.ship_to_organization_id, null), -- OLD_SHIP_TO_ORG_ID
1992 decode(p_rebid_flag, 'Y', bpb.ship_to_location_id, null), -- OLD_SHIP_TO_LOC_ID
1993 decode(p_rebid_flag, 'Y', bpb.effective_start_date, null), -- OLD_EFFECTIVE_START_DATE
1994 decode(p_rebid_flag, 'Y', bpb.effective_end_date, null), -- OLD_EFFECTIVE_END_DATE
1995 decode(p_rebid_flag, 'Y', bpb.quantity, null), -- OLD_QUANTITY
1996 decode(p_rebid_flag, 'Y', bpb.max_quantity, null), -- OLD_MAX_QUANTITY
1997 decode(p_rebid_flag, 'Y', bpb.price_type, null) -- OLD_PRICE_TYPE
1998 FROM pon_bid_shipments bpb,
1999 pon_bid_item_prices bl
2000 WHERE bpb.bid_number = p_source_bid_num
2001 AND bl.bid_number = p_bid_number
2002 AND bl.line_number = bpb.line_number
2003 -- only unmodified lines with bids
2007 END insert_into_price_tiers;
2004 AND bl.has_bid_flag = 'Y'
2005 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2006
2008
2009 -- ======================================================================
2010 -- PROCEDURE: INSERT_INTO_PRICE_DIFF PRIVATE
2011 -- PARAMETERS:
2012 -- p_auc_header_id IN auction_header_id of negotiation
2013 -- p_bid_number IN bid number to insert price differentials for
2014 -- p_source_bid_num IN source_bid to default from
2015 -- p_userid IN userid of bid creator
2016 -- p_rebid_flag IN flag indicating rebid or not
2017 --
2018 -- COMMENT: inserts price differentials for the new bid, defualting as necessary
2019 -- ======================================================================
2020 PROCEDURE insert_into_price_diff
2021 (
2022 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2023 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2024 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2025 p_userid IN pon_bid_headers.created_by%TYPE,
2026 p_rebid_flag IN VARCHAR2,
2027 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2028 p_batch_end IN pon_bid_item_prices.line_number%TYPE
2029 ) IS
2030 BEGIN
2031
2032 INSERT INTO pon_bid_price_differentials
2033 (
2034 AUCTION_HEADER_ID,
2035 BID_NUMBER,
2036 LINE_NUMBER,
2037 SHIPMENT_NUMBER,
2038 PRICE_DIFFERENTIAL_NUMBER,
2039 PRICE_TYPE,
2040 MULTIPLIER,
2041 CREATION_DATE,
2042 CREATED_BY,
2043 LAST_UPDATE_DATE,
2044 LAST_UPDATED_BY,
2045 LAST_UPDATE_LOGIN,
2046 OLD_MULTIPLIER
2047 )
2048 (
2049 -- Insert all line level price differentials
2050 (SELECT
2051 p_auc_header_id, -- AUCTION_HEADER_ID
2052 p_bid_number, -- BID_NUMBER
2053 apd.line_number, -- LINE_NUMBER
2054 apd.shipment_number, -- SHIPMENT_NUMBER
2055 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
2056 apd.price_type, -- PRICE_TYPE
2057 decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
2058 sysdate, -- CREATION_DATE
2059 p_userid, -- CREATED_BY
2060 sysdate, -- LAST_UPDATE_DATE
2061 p_userid, -- LAST_UPDATED_BY
2062 null, -- LAST_UPDATE_LOGIN
2063 decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
2064 FROM pon_price_differentials apd,
2065 pon_bid_price_differentials bpd,
2066 pon_bid_item_prices bl
2067 WHERE apd.auction_header_id = p_auc_header_id
2068 AND apd.shipment_number = -1 -- only line level differentials
2069 AND bl.auction_header_id = apd.auction_header_id
2070 AND bl.bid_number = p_bid_number
2071 AND bl.line_number = apd.line_number
2072 AND bpd.bid_number (+) = p_source_bid_num
2073 AND bpd.line_number (+) = apd.line_number
2074 AND bpd.shipment_number (+) = apd.shipment_number
2075 AND bpd.price_differential_number (+) = apd.price_differential_number
2076 AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
2077
2078 UNION
2079
2080 -- Insert shipment level price differentials, but only for
2081 -- those shipments that got copied over
2082 (SELECT
2083 p_auc_header_id, -- AUCTION_HEADER_ID
2084 p_bid_number, -- BID_NUMBER
2085 apd.line_number, -- LINE_NUMBER
2086 apd.shipment_number+1, -- SHIPMENT_NUMBER
2087 apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
2088 apd.price_type, -- PRICE_TYPE
2089 decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
2090 sysdate, -- CREATION_DATE
2091 p_userid, -- CREATED_BY
2092 sysdate, -- LAST_UPDATE_DATE
2093 p_userid, -- LAST_UPDATED_BY
2094 null, -- LAST_UPDATE_LOGIN
2095 decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
2096 FROM pon_price_differentials apd,
2097 pon_bid_price_differentials bpd,
2098 pon_bid_shipments bpb,
2099 pon_bid_item_prices bl
2100 WHERE apd.auction_header_id = p_auc_header_id
2101 AND apd.shipment_number <> -1 -- only shipment differentials
2102 AND bl.bid_number = p_bid_number
2103 AND bl.line_number = apd.line_number
2104 AND bpb.bid_number = p_bid_number
2105 AND bpb.line_number = apd.line_number
2106 AND bpb.shipment_type = 'PRICE BREAK'
2107 AND bpb.shipment_number = apd.shipment_number + 1
2108 AND bpd.bid_number (+) = p_source_bid_num
2109 AND bpd.line_number (+) = apd.line_number
2110 AND bpd.shipment_number (+) = apd.shipment_number + 1
2111 AND bpd.price_differential_number (+) = apd.price_differential_number
2112 AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
2113 );
2114
2115 END insert_into_price_diff;
2116
2117 -- ======================================================================
2118 -- PROCEDURE: insert_into_payments PRIVATE
2119 -- PARAMETERS:
2120 -- p_auc_header_id IN auction_header_id of negotiation
2121 -- p_bid_number IN bid number to insert price factors for
2122 -- p_source_bid_num IN source_bid to default from
2123 -- p_copy_only_from_auc, IN copy all the payments from negotiation only
2124 -- p_supplier_flag, IN Flag indication if supplier allowed to enter payments
2125 -- p_userid IN userid of bid creator
2126 -- p_rebid_flag IN flag indicating rebid or not
2127 -- p_new_round_or_amended IN flag indicating if defaulting result of amend or new round
2128 -- COMMENT: insert payments for the new bid, defualting as necessary
2129 -- ======================================================================
2130 -- Create and default payments
2131 PROCEDURE insert_into_payments
2132 (
2136 p_copy_only_from_auc IN VARCHAR2,
2133 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2134 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2135 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2137 p_supplier_flag IN pon_auction_headers_all.SUPPLIER_ENTERABLE_PYMT_FLAG%TYPE,
2138 p_userid IN pon_bid_headers.created_by%TYPE,
2139 p_rebid_flag IN VARCHAR2,
2140 p_new_round_or_amended IN VARCHAR2,
2141 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2142 p_batch_end IN pon_bid_item_prices.line_number%TYPE
2143 ) IS
2144
2145
2146
2147 CURSOR c_bid_pymnts_attach IS
2148 SELECT
2149 DISTINCT
2150 source_pay.bid_number source_bid_number,
2151 source_pay.bid_line_number source_bid_line_number,
2152 source_pay.bid_payment_id source_bid_payment_id,
2153 dest_pay.bid_number dest_bid_number,
2154 dest_pay.bid_line_number dest_bid_line_number,
2155 dest_pay.bid_payment_id dest_bid_payment_id
2156 FROM
2157 PON_BID_PAYMENTS_SHIPMENTS source_pay,
2158 FND_ATTACHED_DOCUMENTS fnd,
2159 PON_BID_PAYMENTS_SHIPMENTS dest_pay,
2160 PON_BID_ITEM_PRICES bl
2161 WHERE bl.auction_header_id = p_auc_header_id
2162 AND bl.bid_number = p_bid_number
2163 AND bl.has_bid_flag = 'Y'
2164 AND dest_pay.bid_number = bl.bid_number
2165 AND dest_pay.bid_line_number = bl.line_number
2166 AND source_pay.bid_number = p_source_bid_num
2167 AND dest_pay.bid_line_number = source_pay.bid_line_number
2168 AND dest_pay.payment_display_number = source_pay.payment_display_number
2169 AND fnd.pk1_value = source_pay.bid_number
2170 AND fnd.pk2_value = source_pay.bid_line_number
2171 AND fnd.pk3_value = source_pay.bid_payment_id
2172 AND fnd.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS'
2173 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
2174
2175 l_module CONSTANT VARCHAR2(35) := 'Insert_into_payments';
2176 BEGIN
2177 IF (g_debug_mode = 'Y') THEN
2178 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2179 FND_LOG.string(log_level => FND_LOG.level_statement,
2180 module => g_module_prefix || l_module,
2181 message => 'entering insert_into_payments' );
2182 END IF;
2183 END IF;
2184 IF (p_copy_only_from_auc = 'Y') THEN
2185 IF (g_debug_mode = 'Y') THEN
2186 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2187 FND_LOG.string(log_level => FND_LOG.level_statement,
2188 module => g_module_prefix || l_module,
2189 message => 'In if p_copy_only_from_auc is Y' );
2190 END IF;
2191 END IF;
2192 --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
2193 INSERT INTO pon_bid_payments_shipments
2194 (
2195 BID_NUMBER,
2196 BID_LINE_NUMBER,
2197 BID_PAYMENT_ID,
2198 AUCTION_HEADER_ID,
2199 AUCTION_LINE_NUMBER,
2200 CREATION_DATE,
2201 CREATED_BY,
2202 LAST_UPDATE_DATE,
2203 LAST_UPDATED_BY,
2204 LAST_UPDATE_LOGIN,
2205 PAYMENT_DISPLAY_NUMBER,
2206 PAYMENT_DESCRIPTION,
2207 AUCTION_PAYMENT_ID,
2208 PAYMENT_TYPE_CODE,
2209 QUANTITY,
2210 UOM_CODE,
2211 PROMISED_DATE
2212 )
2213 (SELECT
2214 p_bid_number, --BID_NUMBER,
2215 bl.line_number, --BID_LINE_NUMBER,
2216 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2217 p_auc_header_id, --AUCTION_HEADER_ID,
2218 bl.line_number, --AUCTION_LINE_NUMBER,
2219 sysdate, --CREATION_DATE,
2220 p_userid, --CREATED_BY,
2221 sysdate, --LAST_UPDATE_DATE,
2222 p_userid, --LAST_UPDATED_BY,
2223 fnd_global.login_id, --LAST_UPDATE_LOGIN
2224 apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2225 apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2226 apmt.PAYMENT_ID, --AUCTION_PAYMENT_ID,
2227 apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2228 apmt.QUANTITY, --QUANTITY,
2229 apmt.UOM_CODE, --UOM_CODE,
2230 bl.promised_date --PROMISED_DATE,
2231 FROM pon_bid_item_prices bl,pon_auc_payments_shipments apmt
2232 WHERE bl.auction_header_id = p_auc_header_id
2233 AND bl.bid_number = p_bid_number
2234 AND bl.auction_header_id = apmt.auction_header_id
2235 AND bl.line_number = apmt.line_number
2236 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2237
2238
2239 ELSE --the following should execute if rebid/disqualified/new round or amend with supplier-enterable_flag Y/
2240 -- new round or amend with supplier flag N in this and previous version
2241
2242
2243 IF (g_debug_mode = 'Y') THEN
2247 message => 'In else of p_copy_only_from_auc is Y' );
2244 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2245 FND_LOG.string(log_level => FND_LOG.level_statement,
2246 module => g_module_prefix || l_module,
2248 END IF;
2249 END IF;
2250
2251 IF (g_debug_mode = 'Y') THEN
2252 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2253 FND_LOG.string(log_level => FND_LOG.level_statement,
2254 module => g_module_prefix || l_module,
2255 message => 'Insert for lines not bid' );
2256 END IF;
2257 END IF;
2258 --copy all the payments for the lines from auction payments for lines that have not been bid
2259 INSERT INTO pon_bid_payments_shipments
2260 (
2261 BID_NUMBER,
2262 BID_LINE_NUMBER,
2263 BID_PAYMENT_ID,
2264 AUCTION_HEADER_ID,
2265 AUCTION_LINE_NUMBER,
2266 CREATION_DATE,
2267 CREATED_BY,
2268 LAST_UPDATE_DATE,
2269 LAST_UPDATED_BY,
2270 LAST_UPDATE_LOGIN,
2271 PAYMENT_DISPLAY_NUMBER,
2272 PAYMENT_DESCRIPTION,
2273 AUCTION_PAYMENT_ID,
2274 PAYMENT_TYPE_CODE,
2275 QUANTITY,
2276 UOM_CODE,
2277 PROMISED_DATE
2278 )
2279 (SELECT
2280 p_bid_number, --BID_NUMBER,
2281 bl.line_number, --BID_LINE_NUMBER,
2282 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2283 p_auc_header_id, --AUCTION_HEADER_ID,
2284 bl.line_number, --AUCTION_LINE_NUMBER,
2285 sysdate, --CREATION_DATE,
2286 p_userid, --CREATED_BY,
2287 sysdate, --LAST_UPDATE_DATE,
2288 p_userid, --LAST_UPDATED_BY,
2289 fnd_global.login_id, --LAST_UPDATE_LOGIN
2290 apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2291 apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2292 decode(p_supplier_flag, 'N',apmt.PAYMENT_ID,null), --AUCTION_PAYMENT_ID,
2293 apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2294 apmt.QUANTITY, --QUANTITY,
2295 apmt.UOM_CODE, --UOM_CODE,
2296 bl.promised_date --PROMISED_DATE,
2297 FROM pon_bid_item_prices bl,
2298 pon_auc_payments_shipments apmt
2299 WHERE bl.auction_header_id = p_auc_header_id
2300 AND bl.bid_number = p_bid_number
2301 AND bl.auction_header_id = apmt.auction_header_id
2302 AND bl.line_number = apmt.line_number
2303 AND bl.has_bid_flag = 'N'
2304 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2305
2306 IF (g_debug_mode = 'Y') THEN
2307 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2308 FND_LOG.string(log_level => FND_LOG.level_statement,
2309 module => g_module_prefix || l_module,
2310 message => 'Insert for lines that were bid' );
2311 END IF;
2312 END IF;
2313 --copy all the payments for the lines from bid payments for lines that have been bid
2314 INSERT INTO pon_bid_payments_shipments
2315 (
2316 BID_NUMBER,
2317 BID_LINE_NUMBER,
2318 BID_PAYMENT_ID,
2319 AUCTION_HEADER_ID,
2320 AUCTION_LINE_NUMBER,
2321 CREATION_DATE,
2322 CREATED_BY,
2323 LAST_UPDATE_DATE,
2324 LAST_UPDATED_BY,
2325 LAST_UPDATE_LOGIN,
2326 PAYMENT_DISPLAY_NUMBER,
2327 PAYMENT_DESCRIPTION,
2328 AUCTION_PAYMENT_ID,
2329 PAYMENT_TYPE_CODE,
2330 QUANTITY,
2331 UOM_CODE,
2332 PRICE,
2333 BID_CURRENCY_PRICE,
2334 PROMISED_DATE,
2335 OLD_PAYMENT_DISPLAY_NUMBER,
2336 OLD_PAYMENT_TYPE_CODE,
2337 OLD_PAYMENT_DESCRIPTION,
2338 OLD_QUANTITY,
2339 OLD_UOM_CODE,
2340 OLD_BID_CURRENCY_PRICE,
2341 OLD_PROMISED_DATE
2342 )
2343 (SELECT
2344 p_bid_number, --BID_NUMBER,
2345 bl.line_number, --BID_LINE_NUMBER,
2346 pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
2347 p_auc_header_id, --AUCTION_HEADER_ID,
2348 bl.line_number, --AUCTION_LINE_NUMBER,
2349 sysdate, --CREATION_DATE,
2350 p_userid, --CREATED_BY,
2351 sysdate, --LAST_UPDATE_DATE,
2352 p_userid, --LAST_UPDATED_BY,
2353 fnd_global.login_id, --LAST_UPDATE_LOGIN
2354 bpmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
2355 bpmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
2356 decode(p_new_round_or_amended , 'Y' ,
2360 AND payment_display_number= bpmt.PAYMENT_DISPLAY_NUMBER
2357 decode(p_supplier_flag ,'N',(select payment_id from pon_auc_payments_shipments
2358 where auction_header_id=p_auc_header_id
2359 AND line_number = bl.line_number
2361 )
2362 , null
2363 ),bpmt.AUCTION_PAYMENT_ID
2364 ), --AUCTION_PAYMENT_ID,
2365 bpmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
2366 bpmt.QUANTITY, --QUANTITY,
2367 bpmt.UOM_CODE, --UOM_CODE,
2368 bpmt.PRICE, --PRICE,
2369 bpmt.BID_CURRENCY_PRICE, --BID_CURRENCY_PRICE,
2370 bpmt.promised_date, --PROMISED_DATE,
2371 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DISPLAY_NUMBER,null), --OLD_PAYMENT_DISPLAY_NUMBER,
2372 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_TYPE_CODE,null), --OLD_PAYMENT_TYPE_CODE,
2373 decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DESCRIPTION,null), --OLD_PAYMENT_DESCRIPTION,
2374 decode(p_rebid_flag, 'Y',bpmt.QUANTITY,null), --OLD_QUANTITY,
2375 decode(p_rebid_flag, 'Y',bpmt.UOM_CODE, null), --OLD_UOM_CODE,
2376 decode(p_rebid_flag, 'Y',bpmt.BID_CURRENCY_PRICE, null), --OLD_BID_CURRENCY_PRICE,
2377 decode(p_rebid_flag, 'Y',bpmt.PROMISED_DATE, null) --OLD_PROMISED_DATE
2378 FROM pon_bid_item_prices bl,
2379 pon_bid_payments_shipments bpmt
2380 WHERE bl.auction_header_id = p_auc_header_id
2381 AND bl.bid_number = p_bid_number
2382 AND bpmt.bid_number = p_source_bid_num
2383 AND bpmt.bid_line_number = bl.line_number
2384 AND bl.has_bid_flag = 'Y'
2385 AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
2386
2387 IF (g_debug_mode = 'Y') THEN
2388 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2389 FND_LOG.string(log_level => FND_LOG.level_statement,
2390 module => g_module_prefix || l_module,
2391 message => 'copy attachements from bid' );
2392 END IF;
2393 END IF;
2394 --copy the attachments for those payments which came from bid
2395 FOR payment_rec in c_bid_pymnts_attach LOOP
2396 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
2397 X_from_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
2398 X_from_pk1_value => to_char(payment_rec.source_bid_number),
2399 X_from_pk2_value => to_char(payment_rec.source_bid_line_number),
2400 X_from_pk3_value => to_char(payment_rec.source_bid_payment_id),
2401 X_to_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
2402 X_to_pk1_value => to_char(payment_rec.dest_bid_number),
2403 X_to_pk2_value => to_char(payment_rec.dest_bid_line_number),
2404 X_to_pk3_value => to_char(payment_rec.dest_bid_payment_id),
2405 X_created_by => p_userid,
2406 X_last_update_login => fnd_global.login_id);
2407 END LOOP;
2408 END IF; --p_copy_only_from_auc
2409 IF (g_debug_mode = 'Y') THEN
2410 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2411 FND_LOG.string(log_level => FND_LOG.level_statement,
2412 module => g_module_prefix || l_module,
2413 message => 'exiting insert_into_payments' );
2414 END IF;
2415 END IF;
2416 END insert_into_payments;
2417
2418 -- ======================================================================
2419 -- PROCEDURE: COPY_LINE_ATTACHMENTS PRIVATE
2420 -- PARAMETERS:
2421 -- p_auc_header_id IN auction_header_id of negotiation
2422 -- p_bid_number IN bid number to copy attachments to
2423 -- p_source_header_id IN auction_header_id of source bids negotiation
2424 -- p_source_bid_num IN source_bid to copy attachments from
2425 -- p_userid IN userid of bid creator
2426 --
2427 -- COMMENT: copies over line attachments from source bid
2428 -- ======================================================================
2429 PROCEDURE copy_line_attachments
2430 (
2431 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
2432 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2433 p_source_header_id IN pon_bid_headers.auction_header_id%TYPE,
2434 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2435 p_userid IN pon_bid_headers.created_by%TYPE,
2436 p_batch_start IN pon_bid_item_prices.line_number%TYPE,
2437 p_batch_end IN pon_bid_item_prices.line_number%TYPE,
2438 p_to_category_id IN NUMBER,
2439 p_change_categ_id IN VARCHAR2
2440 ) IS
2441
2442 CURSOR bid_lines_with_attachments IS
2443 SELECT DISTINCT ad.pk3_value
2444 FROM fnd_attached_documents ad, pon_bid_item_prices bl
2445 WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
2446 AND ad.pk1_value = p_source_header_id
2447 AND ad.pk2_value = p_source_bid_num
2448 AND ad.pk3_value IS NOT null
2449 AND bl.bid_number = p_bid_number
2453
2450 AND bl.line_number = to_number(ad.pk3_value)
2451 AND bl.has_bid_flag = 'Y'
2452 AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
2454 BEGIN
2455 IF p_change_categ_id = 'Y' then
2456 -- Copy the line's attachments only for unmodified lines, with new target category.
2457 FOR line IN bid_lines_with_attachments LOOP
2458 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2459 (x_from_entity_name => 'PON_BID_ITEM_PRICES',
2460 x_from_pk1_value => p_source_header_id,
2461 x_from_pk2_value => p_source_bid_num,
2462 x_from_pk3_value => line.pk3_value,
2463 x_to_entity_name => 'PON_BID_ITEM_PRICES',
2464 x_to_pk1_value => p_auc_header_id,
2465 x_to_pk2_value => p_bid_number,
2466 x_to_pk3_value => line.pk3_value,
2467 x_created_by => p_userid,
2468 x_last_update_login => fnd_global.login_id,
2469 x_to_category_id => p_to_category_id);
2470 END LOOP;
2471 ELSE
2472 -- Copy the line's attachments only for unmodified lines
2473 FOR line IN bid_lines_with_attachments LOOP
2474 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2475 (x_from_entity_name => 'PON_BID_ITEM_PRICES',
2476 x_from_pk1_value => p_source_header_id,
2477 x_from_pk2_value => p_source_bid_num,
2478 x_from_pk3_value => line.pk3_value,
2479 x_to_entity_name => 'PON_BID_ITEM_PRICES',
2480 x_to_pk1_value => p_auc_header_id,
2481 x_to_pk2_value => p_bid_number,
2482 x_to_pk3_value => line.pk3_value,
2483 x_created_by => p_userid,
2484 x_last_update_login => fnd_global.login_id);
2485 END LOOP;
2486 END IF; -- }
2487
2488 END copy_line_attachments;
2489
2490 -- ======================================================================
2491 -- PROCEDURE: POPULATE_HAS_BID_FLAG PRIVATE
2492 -- PARAMETERS:
2493 -- p_auc_header_id IN auction_header_id of negotiation
2494 -- p_bid_number IN bid number to populate has_bid_flag for
2495 --
2496 -- COMMENT: populates has_bid_flag - used when defualting from pre-release 12 draft
2497 -- ======================================================================
2498 PROCEDURE populate_has_bid_flag
2499 (
2500 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2501 p_bid_number IN pon_bid_headers.bid_number%TYPE
2502 ) IS
2503 l_full_qty_reqd VARCHAR2(1);
2504 BEGIN
2505
2506 -- Determine if the auction is full quantity required
2507 SELECT decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N')
2508 INTO l_full_qty_reqd
2509 FROM pon_auction_headers_all ah
2510 WHERE ah.auction_header_id = p_auc_header_id;
2511
2512 -- First set has_bid_flag to N
2513 UPDATE pon_bid_item_prices bl
2514 SET bl.has_bid_flag = 'N'
2515 WHERE bl.bid_number = p_bid_number;
2516
2517 -- Then, determine which lines have a bid
2518 UPDATE pon_bid_item_prices bl
2519 SET bl.has_bid_flag = 'Y'
2520 WHERE bl.bid_number = p_bid_number
2521 AND ((bl.promised_date IS NOT null
2522 OR bl.price IS NOT null
2523 OR bl.proxy_bid_limit_price IS NOT null
2524 OR bl.po_bid_min_rel_amount IS NOT null
2525 OR bl.note_to_auction_owner IS NOT null)
2526
2527 OR EXISTS
2528
2529 -- Check auction side to check quantity
2530 (SELECT al.line_number
2531 FROM pon_auction_item_prices_all al
2532 WHERE al.auction_header_id = bl.auction_header_id
2533 AND al.line_number = bl.line_number
2534 AND (l_full_qty_reqd <> 'Y'
2535 AND al.order_type_lookup_code <> 'AMOUNT'
2536 AND al.group_type <> 'LOT_LINE'
2537 AND bl.quantity IS NOT null))
2538
2539 OR EXISTS
2540
2541 -- Check attributes
2542 (SELECT ba.line_number
2543 FROM pon_bid_attribute_values ba
2544 WHERE ba.bid_number = bl.bid_number
2545 AND ba.line_number = bl.line_number
2546 AND ba.value IS NOT null
2547 AND rownum = 1)
2548
2549 OR EXISTS
2550
2551 -- Check price factors
2552 (SELECT bpf.line_number
2553 FROM pon_bid_price_elements bpf
2554 WHERE bpf.bid_number = bl.bid_number
2555 AND bpf.line_number = bl.line_number
2556 AND bpf.pf_type = 'SUPPLIER'
2557 AND bpf.bid_currency_value IS NOT null
2558 AND rownum = 1)
2559
2560 OR EXISTS
2561
2562 -- Check shipments
2563 (SELECT bs.line_number
2564 FROM pon_bid_shipments bs
2565 WHERE bs.bid_number = bl.bid_number
2566 AND bs.line_number = bl.line_number
2567 AND (bs.auction_shipment_number IS null
2568 OR bs.price_type = 'PRICE' AND bs.bid_currency_unit_price IS NOT null
2569 OR bs.price_type = 'PRICE DISCOUNT' AND bs.price_discount IS NOT null
2570 OR bs.bid_currency_price IS NOT null)
2571 AND rownum = 1)
2572
2573 OR EXISTS
2574
2578 WHERE bpd.bid_number = bl.bid_number
2575 -- Check price differentials, including shipment price differentials
2576 (SELECT bpd.line_number
2577 FROM pon_bid_price_differentials bpd
2579 AND bpd.line_number = bl.line_number
2580 AND bpd.multiplier IS NOT null
2581 AND rownum = 1)
2582 );
2583
2584 END populate_has_bid_flag;
2585
2586 -- ======================================================================
2587 -- PROCEDURE: CREATE_NEW_DRAFT PRIVATE
2588 -- PARAMETERS:
2589 -- p_auc_header_id IN auction_header_id of negotiation
2590 -- p_source_bid_num IN source_bid to default from
2591 -- p_tpid IN trading partner id of supplier
2592 -- p_tpcid IN trading partner contact id of supplier
2593 -- p_tpname IN trading partner name of supplier
2594 -- p_tpcname IN trading partner contact name of supplier
2595 -- p_userid IN userid of bid creator
2596 -- p_venid IN vendor id
2597 -- p_vensid IN vendor site id to place bid for
2598 -- p_venscode IN vendor site code to place bid for
2599 -- p_auctpid IN buyers trading partner id
2600 -- p_auctcpid IN buyers trading partner contact id
2601 -- p_buyer_user IN flag indicating surrogate bid or not
2602 -- p_new_round_or_amended IN flag indicating, if bid defualt is happening for new round or amend
2603 -- p_rebid_flag IN flag indicating rebid or not
2604 -- x_bid_number OUT the bid number of the created bid
2605 --
2606 -- COMMENT: creates a new draft bid for the specified supplier/site
2607 -- combination on the specified site. Inserts values into all bid
2608 -- side tables and copies attachments
2609 -- ======================================================================
2610 PROCEDURE create_new_draft_bid
2611 (
2612 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2613 p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
2614 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
2615 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
2616 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
2617 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
2618 p_userid IN pon_bid_headers.created_by%TYPE,
2619 p_venid IN pon_bid_headers.vendor_id%TYPE,
2620 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
2621 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
2622 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
2623 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
2624 p_buyer_user IN VARCHAR2,
2625 p_new_round_or_amended IN VARCHAR2,
2626 p_rebid_flag IN VARCHAR2,
2627 p_prev_bid_disq IN VARCHAR2,
2628 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
2629 x_return_status OUT NOCOPY NUMBER,
2630 x_return_code OUT NOCOPY VARCHAR2
2631 ) IS
2632 l_source_header_id pon_auction_headers_all.auction_header_id%TYPE;
2633 l_restricted_flag VARCHAR2(1);
2634 l_rel12_draft VARCHAR2(1);
2635 l_source_bid_status pon_bid_headers.bid_status%TYPE;
2636 l_supp_seq_number pon_bidding_parties.sequence%TYPE;
2637
2638 l_max_line_number pon_bid_item_prices.line_number%TYPE;
2639 l_batch_start pon_bid_item_prices.line_number%TYPE;
2640 l_batch_end pon_bid_item_prices.line_number%TYPE;
2641 l_skip_pf_for_batch VARCHAR2(1);
2642 l_supplier_flag pon_auction_headers_all.SUPPLIER_ENTERABLE_PYMT_FLAG%TYPE;
2643 l_payment_type pon_auction_headers_all.PROGRESS_PAYMENT_TYPE%TYPE;
2644 l_contract_type pon_auction_headers_all.CONTRACT_TYPE%TYPE;
2645 l_module CONSTANT VARCHAR2(35) := 'create_new_draft_bid';
2646 l_other_draft_bid_number pon_bid_headers.bid_number%TYPE;
2647 l_surrog_bid_flag pon_bid_headers.surrog_bid_flag%TYPE;
2648 l_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%TYPE;
2649
2650
2651 -- Two-part RFQ related variables
2652 l_is_new_round VARCHAR2(1) := 'N'; -- to store Y for new rounds (default N)
2653 l_to_category_id NUMBER; -- to store destination category id
2654 l_prev_two_part VARCHAR2(1); -- Y if previous round was two-part, else N or null
2655 l_curr_two_part VARCHAR2(1); -- Y if current round is two-part, else N or null
2656 l_categ_id_supp NUMBER; -- to store category id of "FromSupplier"
2657 l_categ_id_supp_tech NUMBER; -- to store category id of "FromSupplierTechnical"
2658 l_change_categ_id VARCHAR2(1) := 'N'; -- change category id ?
2659 BEGIN
2660
2661 BEGIN
2662 -- Check if the supplier has restricted lines, and get sequence number
2663 SELECT decode(bp.access_type, 'RESTRICTED', 'Y', 'N'), bp.sequence
2664 INTO l_restricted_flag, l_supp_seq_number
2665 FROM pon_bidding_parties bp
2666 WHERE bp.auction_header_id = p_auc_header_id
2667 AND bp.trading_partner_id = p_tpid
2668 AND nvl(bp.vendor_site_id, -1) = p_vensid;
2669 EXCEPTION
2670 WHEN NO_DATA_FOUND THEN
2671 l_restricted_flag := 'N';
2672 l_supp_seq_number := null;
2673 END;
2674
2675 -- Create and default bid header and get the bid number
2676 insert_into_bid_header
2677 (p_auc_header_id,
2678 p_source_bid_num,
2679 p_tpid,
2680 p_tpname,
2681 p_tpcid,
2682 p_tpcname,
2683 p_userid,
2684 p_venid,
2685 p_vensid,
2686 p_venscode,
2687 p_auctpid,
2688 p_auctpcid,
2689 p_buyer_user,
2690 p_rebid_flag,
2694 -- Create header attributes
2691 p_prev_bid_disq,
2692 x_bid_number);
2693
2695 insert_into_header_attributes
2696 (p_auc_header_id,
2697 x_bid_number,
2698 p_source_bid_num,
2699 p_userid,
2700 p_rebid_flag);
2701
2702 -- Copy over header attachments
2703 IF (p_source_bid_num IS NOT null) THEN
2704
2705 SELECT bh.auction_header_id
2706 INTO l_source_header_id
2707 FROM pon_bid_headers bh
2708 WHERE bh.bid_number = p_source_bid_num;
2709
2710 -- get value of two-part flags for current and prev rounds
2711 select decode(pah.auction_header_id_prev_round, null, 'N', 'Y'),
2712 nvl(pah.two_part_flag, 'N'),
2713 (select nvl(two_part_flag,'N') from pon_auction_headers_all
2714 where auction_header_id = pah.auction_header_id_prev_round)
2715 into l_is_new_round, l_curr_two_part, l_prev_two_part
2716 from pon_auction_headers_all pah
2717 where auction_header_id = p_auc_header_id;
2718
2719 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);
2720
2721 -- if it is a new round, and two-part flag has changed...
2722 if (l_is_new_round = 'Y' AND (l_curr_two_part <> l_prev_two_part)) THEN -- {
2723 -- target category id needs to be changed
2724 -- fetch target category ids.
2725 select (select category_id from fnd_document_categories
2726 where name = pon_auction_pkg.g_supplier_attachment),
2727 (select category_id from fnd_document_categories
2728 where name = pon_auction_pkg.g_technical_attachment)
2729 into l_categ_id_supp, l_categ_id_supp_tech
2730 from fnd_document_categories
2731 where ROWNUM = 1;
2732
2733 -- if prev round was two part, copy attachments to "FromSupplier"
2734 if (l_prev_two_part = 'Y') THEN -- {
2735 l_to_category_id := l_categ_id_supp;
2736 l_change_categ_id := 'Y';
2737 -- else copy to "FromSupplierTechnical"
2738 else
2739 l_to_category_id := l_categ_id_supp_tech;
2740 l_change_categ_id := 'Y';
2741 end if; -- }
2742
2743 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);
2744
2745 -- Copy header attachments to new target category
2746 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2747 (x_from_entity_name => 'PON_BID_HEADERS',
2748 x_from_pk1_value => l_source_header_id,
2749 x_from_pk2_value => p_source_bid_num,
2750 x_to_entity_name => 'PON_BID_HEADERS',
2751 x_to_pk1_value => p_auc_header_id,
2752 x_to_pk2_value => x_bid_number,
2753 x_created_by => p_userid,
2754 x_last_update_login => fnd_global.login_id,
2755 x_to_category_id => l_to_category_id);
2756
2757 ELSE
2758 -- Copy header level attachments without changing categories
2759 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments
2760 (x_from_entity_name => 'PON_BID_HEADERS',
2761 x_from_pk1_value => l_source_header_id,
2762 x_from_pk2_value => p_source_bid_num,
2763 x_to_entity_name => 'PON_BID_HEADERS',
2764 x_to_pk1_value => p_auc_header_id,
2765 x_to_pk2_value => x_bid_number,
2766 x_created_by => p_userid,
2767 x_last_update_login => fnd_global.login_id);
2768 END IF; -- }
2769 END IF;
2770
2771 --get the one time values needed for complex work here to avoid reexcution of
2772 --query with every batch
2773 g_copy_only_from_auc := 'N';
2774 IF (g_debug_mode = 'Y') THEN
2775 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2776 FND_LOG.string(log_level => FND_LOG.level_statement,
2777 module => g_module_prefix || l_module,
2778 message => 'initializing negotiation values for complex work');
2779 END IF;
2780 END IF;
2781 select nvl(ah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'N'), nvl(progress_payment_type,'NONE'), contract_type,
2782 nvl(ADVANCE_NEGOTIABLE_FLAG,'N'),nvl(RECOUPMENT_NEGOTIABLE_FLAG,'N'),nvl(PROGRESS_PYMT_NEGOTIABLE_FLAG,'N'),
2783 nvl(MAX_RETAINAGE_NEGOTIABLE_FLAG,'N'),nvl(RETAINAGE_NEGOTIABLE_FLAG,'N')
2784 into l_supplier_flag, l_payment_type, l_contract_type,
2785 g_advance_negotiable,g_recoupment_negotiable,g_prog_pymt_negotiable,g_max_rtng_negotiable,g_rtng_negotiable
2786 FROM pon_auction_headers_all ah where ah.auction_header_id=p_auc_header_id;
2787 IF (g_debug_mode = 'Y') THEN
2788 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2789 FND_LOG.string(log_level => FND_LOG.level_statement,
2790 module => g_module_prefix || l_module,
2794 -- Get bid currency precisions and rate and surrogate bid flag
2791 message => 'initializing currency stuff for complex work');
2792 END IF;
2793 END IF;
2795 SELECT cu.precision,
2796 bh.rate,
2797 bh.surrog_bid_flag
2798 INTO g_curr_prec,
2799 g_bid_rate,
2800 l_surrog_bid_flag
2801 FROM pon_bid_headers bh,
2802 fnd_currencies cu
2803 WHERE bh.bid_number = x_bid_number
2804 AND cu.currency_code = bh.bid_currency_code;
2805
2806 --do the following only if complex work neg
2807 IF (p_new_round_or_amended = 'Y' and l_supplier_flag = 'N'
2808 AND l_payment_type <> 'NONE' AND l_contract_type = 'STANDARD') THEN
2809 IF (g_debug_mode = 'Y') THEN
2810 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2811 FND_LOG.string(log_level => FND_LOG.level_statement,
2812 module => g_module_prefix || l_module,
2813 message => 'within if for complex work');
2814 END IF;
2815 END IF;
2816 -- check from where should a payment defualt if supplier flag was
2817 --toggled in new round or amend
2818 -- Here default payments from previous bid if SUPPLIER_ENTERABLE_PYMT_FLAG for
2819 -- new negotiation is Y. but if SUPPLIER_ENTERABLE_PYMT_FLAG is N
2820 -- then if previous neg had SUPPLIER_ENTERABLE_PYMT_FLAG as Y then
2821 -- we need to default payments from neg and not from bid.
2822 select decode(oldah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'Y','Y', 'N')
2823 into g_copy_only_from_auc
2824 FROM pon_auction_headers_all oldah
2825 WHERE oldah.auction_header_id = l_source_header_id;
2826
2827 IF (g_debug_mode = 'Y') THEN
2828 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2829 FND_LOG.string(log_level => FND_LOG.level_statement,
2830 module => g_module_prefix || l_module,
2831 message => 'after selecting supplier flag copy only from auc value is' || g_copy_only_from_auc );
2832 END IF;
2833 END IF;
2834
2835 END IF; --p_new_round... ontract_type STANDARD and payment_type <> NONE
2836 IF (g_debug_mode = 'Y') THEN
2837 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2838 FND_LOG.string(log_level => FND_LOG.level_statement,
2839 module => g_module_prefix || l_module,
2840 message => 'Finished initializing negotiation values for complex work' );
2841 END IF;
2842 END IF;
2843 --end values needed for complex work
2844
2845 -- START BATCHING
2846
2847 -- Determine the maximum line number for the negotiation
2848 SELECT ah.max_internal_line_num ,ah.price_tiers_indicator
2849 INTO l_max_line_number,l_price_tiers_indicator
2850 FROM pon_auction_headers_all ah
2851 WHERE ah.auction_header_id = p_auc_header_id;
2852
2853 -- Define the initial range (line numbers are indexed from 1)
2854 l_batch_start := 1;
2855 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
2856 l_batch_end := l_max_line_number;
2857 ELSE
2858 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
2859 END IF;
2860
2861 WHILE (l_batch_start <= l_max_line_number) LOOP
2862
2863 -- Create and default bid lines
2864 insert_into_bid_items
2865 (p_auc_header_id,
2866 x_bid_number,
2867 p_source_bid_num,
2868 p_tpid,
2869 p_tpcid,
2870 p_userid,
2871 p_vensid,
2872 p_rebid_flag,
2873 l_restricted_flag,
2874 l_batch_start,
2875 l_batch_end,
2876 l_surrog_bid_flag);
2877
2878 -- Copy over line attachments
2879 IF (p_source_bid_num IS NOT null) THEN
2880
2881 copy_line_attachments
2882 (p_auc_header_id,
2883 x_bid_number,
2884 l_source_header_id,
2885 p_source_bid_num,
2886 p_userid,
2887 l_batch_start,
2888 l_batch_end,
2889 l_to_category_id,
2890 l_change_categ_id);
2891 END IF;
2892
2893 -- Create and default header and line attributes
2894 insert_into_line_attributes
2895 (p_auc_header_id,
2896 x_bid_number,
2897 p_source_bid_num,
2898 p_userid,
2899 p_rebid_flag,
2900 l_batch_start,
2901 l_batch_end);
2902
2903 -- Populate display_price_factors_flag
2904 populate_display_pf_flag
2905 (p_auc_header_id,
2906 x_bid_number,
2907 l_supp_seq_number,
2908 l_batch_start,
2909 l_batch_end,
2910 l_skip_pf_for_batch);
2911
2912 IF (l_skip_pf_for_batch = 'N') THEN
2913
2914 -- Create and defualt price factors
2915 insert_into_price_factors
2916 (p_auc_header_id,
2917 x_bid_number,
2918 p_source_bid_num,
2919 p_userid,
2920 l_supp_seq_number,
2921 p_rebid_flag,
2922 l_batch_start,
2923 l_batch_end);
2924 END IF;
2925
2929 l_price_tiers_indicator <> 'NONE') THEN
2926 --Create and default price tiers only if the price tiers indicator
2927 -- is non null and not NONE
2928 IF (l_price_tiers_indicator is NOT NULL AND
2930 insert_into_price_tiers
2931 (p_auc_header_id,
2932 x_bid_number,
2933 p_source_bid_num,
2934 p_userid,
2935 p_rebid_flag,
2936 l_batch_start,
2937 l_batch_end);
2938 END IF;
2939
2940 -- Create and default price differentials
2941 insert_into_price_diff
2942 (p_auc_header_id,
2943 x_bid_number,
2944 p_source_bid_num,
2945 p_userid,
2946 p_rebid_flag,
2947 l_batch_start,
2948 l_batch_end);
2949
2950 --complex work
2951 IF (l_payment_type <> 'NONE' AND l_contract_type = 'STANDARD' ) THEN
2952
2953 IF (g_debug_mode = 'Y') THEN
2954 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2955 FND_LOG.string(log_level => FND_LOG.level_statement,
2956 module => g_module_prefix || l_module,
2957 message => 'before calling insert_into_payments' );
2958 END IF;
2959 END IF;
2960 -- Create and default payments
2961 insert_into_payments
2962 (p_auc_header_id,
2963 x_bid_number,
2964 p_source_bid_num,
2965 g_copy_only_from_auc,
2966 l_supplier_flag,
2967 p_userid,
2968 p_rebid_flag,
2969 p_new_round_or_amended,
2970 l_batch_start,
2971 l_batch_end);
2972 IF (g_debug_mode = 'Y') THEN
2973 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2974 FND_LOG.string(log_level => FND_LOG.level_statement,
2975 module => g_module_prefix || l_module,
2976 message => 'after calling insert_into_payments' );
2977 END IF;
2978 END IF;
2979 END IF; --contract_type STANDARD and payment_type <> NONE
2980
2981
2982 -- here should commit the batch
2983 -- before commit, check whether this user already has a draft
2984 -- created. Maybe from a different session
2985 BEGIN
2986 select bid_number
2987 into l_other_draft_bid_number
2988 from pon_bid_headers
2989 where auction_header_id = p_auc_header_id
2990 and bid_number <> x_bid_number
2991 and bid_status = 'DRAFT'
2992 and trading_partner_id = p_tpid
2993 and trading_partner_contact_id = p_tpcid
2994 and nvl(vendor_site_id, -1) = nvl(p_vensid, -1);
2995 EXCEPTION
2996 WHEN NO_DATA_FOUND THEN
2997 l_other_draft_bid_number := null;
2998 END;
2999
3000 IF (l_other_draft_bid_number IS NOT null) THEN
3001 x_return_status := 1;
3002 x_return_code := 'MULTIPLE_REBID';
3003 ROLLBACK;
3004 ELSE
3005 x_return_status := 0;
3006 x_return_code := 'SUCCESS';
3007 COMMIT;
3008 END IF;
3009
3010 -- Find the new range
3011 l_batch_start := l_batch_end + 1;
3012 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
3013 l_batch_end := l_max_line_number;
3014 ELSE
3015 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3016 END IF;
3017
3018 END LOOP;
3019
3020 -- END BATCHING
3021
3022 -- Populate header level display_price_factors_flag
3023 -- It is 'Y' if any line has the line level flag set
3024 UPDATE pon_bid_headers bh
3025 SET bh.display_price_factors_flag =
3026 nvl((SELECT 'Y'
3027 FROM pon_bid_item_prices bl
3028 WHERE bl.bid_number = bh.bid_number
3029 AND bl.display_price_factors_flag = 'Y'
3030 AND rownum = 1), 'N')
3031 WHERE bh.bid_number = x_bid_number;
3032
3033 -- Handle proxy bidding: done in check_and_load_bid
3034
3035 END create_new_draft_bid;
3036
3037 -- ======================================================================
3038 -- PROCEDURE: EXPAND_DRAFT PRIVATE
3039 -- PARAMETERS:
3040 -- p_bid_number IN bid_number to expand
3041 -- x_rebid_flag OUT Y if bid expanded is a rebid
3042 --
3043 -- COMMENT: pre-release 12 draft bids do not have lines without bids.
3044 -- As such, they need to be expanded to include them
3045 -- ======================================================================
3046 PROCEDURE expand_draft
3047 (
3048 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3049 x_rebid_flag OUT NOCOPY VARCHAR2
3050 ) IS
3051 l_source_bid_num pon_bid_headers.bid_number%TYPE;
3052
3053 l_auc_header_id pon_auction_headers_all.auction_header_id%TYPE;
3054 l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
3055 l_tpid pon_bid_headers.trading_partner_id%TYPE;
3056 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3060 l_has_pe_flag VARCHAR2(1);
3057 l_userid pon_bid_headers.created_by%TYPE;
3058 l_vensid pon_bid_headers.vendor_site_id%TYPE;
3059 l_supp_seq_number pon_bidding_parties.sequence%TYPE;
3061 l_blanket VARCHAR2(1);
3062 l_full_qty VARCHAR2(1);
3063 l_rate pon_bid_headers.rate%TYPE;
3064 l_price_prec pon_bid_headers.number_price_decimals%TYPE;
3065 l_curr_prec fnd_currencies.precision%TYPE;
3066
3067 l_max_line_number pon_bid_item_prices.line_number%TYPE;
3068 l_batch_start pon_bid_item_prices.line_number%TYPE;
3069 l_batch_end pon_bid_item_prices.line_number%TYPE;
3070 l_skip_pf_for_batch VARCHAR2(1);
3071 BEGIN
3072
3073 -- Need to get auction_header_id and supplier info
3074 SELECT ah.auction_header_id,
3075 ah.trading_partner_id,
3076 decode(ah.has_price_elements, 'Y', 'Y', 'N'),
3077 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
3078 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
3079 bh.trading_partner_id,
3080 bh.trading_partner_contact_id,
3081 bh.vendor_site_id,
3082 bh.created_by,
3083 bh.rate,
3084 bh.number_price_decimals,
3085 cu.precision
3086 INTO l_auc_header_id,
3087 l_auctpid,
3088 l_has_pe_flag,
3089 l_blanket,
3090 l_full_qty,
3091 l_tpid,
3092 l_tpcid,
3093 l_vensid,
3094 l_userid,
3095 l_rate,
3096 l_price_prec,
3097 l_curr_prec
3098 FROM pon_bid_headers bh, pon_auction_headers_all ah, fnd_currencies cu
3099 WHERE bh.bid_number = p_bid_number
3100 AND ah.auction_header_id = bh.auction_header_id
3101 AND cu.currency_code = bh.bid_currency_code;
3102
3103 BEGIN
3104 -- Get the supplier sequence number
3105 SELECT bp.sequence
3106 INTO l_supp_seq_number
3107 FROM pon_bidding_parties bp
3108 WHERE bp.auction_header_id = l_auc_header_id
3109 AND bp.trading_partner_id = l_tpid
3110 AND bp.vendor_site_id = l_vensid;
3111
3112 EXCEPTION
3113 WHEN NO_DATA_FOUND THEN
3114
3115 -- if the current supplier is not invited, then treat this
3116 -- sequence_number to be null
3117
3118 l_supp_seq_number := null;
3119 END;
3120
3121
3122 BEGIN
3123
3124 -- Determine the latest ACTIVE bid and set rebid_flag to Y
3125 -- Since there can only exist a single ACTIVE bid on an amendment for
3126 -- a particular user on a site, we use the rownum = 1 optimisation
3127 SELECT bh.bid_number, 'Y'
3128 INTO l_source_bid_num, x_rebid_flag
3129 FROM pon_bid_headers bh
3130 WHERE bh.auction_header_id = l_auc_header_id
3131 AND bh.trading_partner_id = l_tpid
3132 AND bh.trading_partner_contact_id = l_tpcid
3133 AND bh.vendor_site_id = l_vensid
3134 AND bh.bid_status = 'ACTIVE'
3135 AND rownum = 1
3136 ORDER BY bh.publish_date DESC;
3137
3138 EXCEPTION
3139 -- there is no old bid to get old value columns
3140 WHEN NO_DATA_FOUND THEN
3141 x_rebid_flag := 'N';
3142 END;
3143
3144 -- START BATCHING
3145
3146 -- Determine the maximum line number for the negotiation
3147 SELECT ah.max_internal_line_num
3148 INTO l_max_line_number
3149 FROM pon_auction_headers_all ah
3150 WHERE ah.auction_header_id = l_auc_header_id;
3151
3152 -- Define the initial range (line numbers are indexed from 1)
3153 l_batch_start := 1;
3154 IF (l_max_line_number < PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE) THEN
3155 l_batch_end := l_max_line_number;
3156 ELSE
3157 l_batch_end := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3158 END IF;
3159
3160 WHILE (l_batch_start <= l_max_line_number) LOOP
3161
3162 -- We need to insert those auction side lines which haven't recieved bids
3163 -- line level display pf flag also populated
3164 insert_auction_lines
3165 (l_auc_header_id,
3166 p_bid_number,
3167 l_userid,
3168 l_auctpid,
3169 l_tpid,
3170 l_vensid,
3171 l_has_pe_flag,
3172 l_blanket,
3173 l_full_qty,
3174 l_supp_seq_number,
3175 l_rate,
3176 l_price_prec,
3177 l_curr_prec,
3178 l_batch_start,
3179 l_batch_end);
3180
3181 -- If it as a rebid, we need to populate old_value columns
3182 IF (x_rebid_flag = 'Y') THEN
3183 -- special case for pre-rel12 draft - check
3184 populate_old_value_columns
3185 (p_bid_number,
3186 l_source_bid_num,
3187 l_batch_start,
3188 l_batch_end);
3189 END IF;
3190
3191 -- Find the new range
3192 l_batch_start := l_batch_end + 1;
3193 IF (l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE > l_max_line_number) THEN
3194 l_batch_end := l_max_line_number;
3195 ELSE
3196 l_batch_end := l_batch_end + PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
3197 END IF;
3198
3199 END LOOP;
3200
3201 -- END BATCHING
3202
3203 -- Populate header level display_price_factors_flag
3204 -- It is 'Y' if any line has the line level flag set
3205 UPDATE pon_bid_headers bh
3206 SET bh.display_price_factors_flag =
3207 nvl((SELECT 'Y'
3208 FROM pon_bid_item_prices bl
3209 WHERE bl.bid_number = bh.bid_number
3210 AND bl.display_price_factors_flag = 'Y'
3211 AND rownum = 1), 'N')
3212 WHERE bh.bid_number = p_bid_number;
3213
3214 -- Populate has_bid_flag as it is a rel12 column
3215 populate_has_bid_flag
3216 (l_auc_header_id,
3217 p_bid_number);
3218
3219 /* once we have finished upgrading the draft, reset the flag to Y */
3223
3220 update pon_bid_headers
3221 set rel12_draft_flag = 'Y'
3222 where bid_number = p_bid_number;
3224 -- handle_proxy will be called in check_and_load_bid
3225
3226 END expand_draft;
3227
3228 -- ======================================================================
3229 -- PROCEDURE: LOCK_DRAFT PRIVATE
3230 -- PARAMETERS:
3231 -- p_bid_number IN bid number to lock
3232 -- p_tpid IN trading partner id to lock with
3233 -- p_tpcid IN trading partner contact id to lock with
3234 -- x_return_status OUT 0 for success, 1 for error
3235 -- x_return_code OUT returned error code, or SUCCESS
3236 --
3237 -- COMMENT: If the bid is not locked by another user, it is locked.
3238 -- ======================================================================
3239 PROCEDURE lock_draft
3240 (
3241 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3242 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
3243 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
3244 x_return_status OUT NOCOPY NUMBER,
3245 x_return_code OUT NOCOPY VARCHAR2
3246 ) IS
3247 l_draft_locked pon_bid_headers.draft_locked%TYPE;
3248 l_tpid pon_bid_headers.trading_partner_id%TYPE;
3249 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3250 BEGIN
3251
3252 -- pull up draft lock info
3253 SELECT draft_locked, draft_locked_by, draft_locked_by_contact_id
3254 INTO l_draft_locked, l_tpid, l_tpcid
3255 FROM pon_bid_headers
3256 WHERE bid_number = p_bid_number;
3257
3258 -- If the draft is locked by another user, return an error
3259 IF (l_draft_locked = 'Y' AND (p_tpid <> l_tpid OR p_tpcid <> l_tpcid)) THEN
3260
3261 x_return_status := 1;
3262 x_return_code := 'DRAFT_LOCK_ERR';
3263 RETURN;
3264
3265 -- If the draft is not locked, lock it
3266 ELSIF (l_draft_locked = 'N') THEN
3267
3268 UPDATE pon_bid_headers
3269 SET draft_locked = 'Y',
3270 draft_locked_by = p_tpid,
3271 draft_locked_by_contact_id = p_tpcid,
3272 draft_locked_date = sysdate
3273 WHERE bid_number = p_bid_number;
3274
3275 END IF;
3276
3277 x_return_status := 0;
3278 x_return_code := 'SUCCESS';
3279
3280 END lock_draft;
3281
3282 -- ======================================================================
3283 -- PROCEDURE: CHECK_AMENDMENTS_ACKED PRIVATE
3284 -- PARAMETERS:
3285 -- p_auc_header_id IN auction header id of negotiation
3286 -- p_tpid IN trading partner id of supplier
3287 -- p_tpcid IN trading partner contact id of supplier
3288 -- x_return_status OUT 0 for success, 1 for error
3289 -- x_return_code OUT returned error code, or SUCCESS
3290 --
3291 -- COMMENT: Determines if all amendments on the current round have been acknowledged
3292 -- ======================================================================
3293 PROCEDURE check_amendments_acked
3294 (
3295 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3296 p_tpid IN pon_auction_headers_all.trading_partner_id%TYPE,
3297 p_tpcid IN pon_auction_headers_all.trading_partner_contact_id%TYPE,
3298 x_return_status OUT NOCOPY NUMBER,
3299 x_return_code OUT NOCOPY VARCHAR2
3300 ) IS
3301 l_orig_amend_id pon_auction_headers_all.auction_header_id_orig_amend%TYPE;
3302 l_amend_not_acked VARCHAR2(1);
3303 BEGIN
3304
3305 -- Get the original amendments auction header id
3306 SELECT ah.auction_header_id_orig_amend
3307 INTO l_orig_amend_id
3308 FROM pon_auction_headers_all ah
3309 WHERE ah.auction_header_id = p_auc_header_id;
3310
3311 -- There must be as many acknowledgements as amendments
3312 SELECT decode(count(rownum), 0, 'N', 'Y')
3313 INTO l_amend_not_acked
3314 FROM pon_auction_headers_all ah, pon_acknowledgements ac
3315 WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
3316 AND ah.auction_status IN ('AMENDED', 'ACTIVE')
3317 -- ignore the original amendment
3318 AND ah.amendment_number > 0
3319 AND ac.auction_header_id (+) = ah.auction_header_id
3320 AND ac.trading_partner_id (+) = p_tpid
3321 AND ac.trading_partner_contact_id (+)= p_tpcid
3322 AND ac.acknowledgement_response IS null;
3323
3324 IF (l_amend_not_acked = 'Y') THEN
3325 x_return_status := 1;
3326 x_return_code := 'NEED_ACKNOWLEDGE';
3327 RETURN;
3328 END IF;
3329
3330 x_return_status := 0;
3331 x_return_code := 'SUCCESS';
3332
3333 END check_amendments_acked;
3334
3335 -- ======================================================================
3336 -- PROCEDURE: VALIDATE_SITE PRIVATE
3337 -- PARAMETERS:
3338 -- p_auc_header_id IN auction header id of negotiation
3339 -- p_tpid IN trading partner id of supplier
3340 -- p_vensid IN vendor site to place a bid for
3341 -- p_venscode IN corresponding vendor site code
3342 -- x_return_status OUT 0 for success, 1 for error
3343 -- x_return_code OUT returned error code, or SUCCESS
3344 --
3345 -- COMMENT: Determine if the vensid, venscode combination can bid on the negotiation
3346 -- ======================================================================
3347 PROCEDURE validate_site
3348 (
3349 p_auc_header_id IN pon_bidding_parties.auction_header_id%TYPE,
3350 p_tpid IN pon_bidding_parties.trading_partner_id%TYPE,
3351 p_vensid IN pon_bidding_parties.vendor_site_id%TYPE,
3352 p_venscode IN pon_bidding_parties.vendor_site_code%TYPE,
3356 CURSOR sites IS
3353 x_return_status OUT NOCOPY NUMBER,
3354 x_return_code OUT NOCOPY VARCHAR2
3355 ) IS
3357 SELECT vendor_site_id id, vendor_site_code code
3358 FROM pon_bidding_parties
3359 WHERE auction_header_id = p_auc_header_id
3360 AND trading_partner_id = p_tpid;
3361
3362 l_sites_invited BOOLEAN DEFAULT false;
3363 l_site_valid BOOLEAN DEFAULT false;
3364
3365 BEGIN
3366
3367 -- Look through the invited sites for this auction
3368 FOR site IN sites LOOP
3369 IF (site.id > 0) THEN
3370 l_sites_invited := true;
3371 IF (p_vensid = site.id AND p_venscode = site.code) THEN
3372 l_site_valid := true;
3373 END IF;
3374 END IF;
3375 END LOOP;
3376
3377 -- If multiple sites were invited then, if a site id was specified,
3378 -- return an error if it was not invited. Else, indicate that
3379 -- a site id needs to be specified
3380 IF (l_sites_invited) THEN
3381 IF (p_vensid > 0) THEN
3382 IF (NOT l_site_valid) THEN
3383 x_return_status := 1;
3384 x_return_code := 'INVALID_VENDOR_SITE';
3385 RETURN;
3386 END IF;
3387 ELSE
3388 x_return_status := 1;
3389 x_return_code := 'PICK_VENDOR_SITE';
3390 RETURN;
3391 END IF;
3392 END IF;
3393
3394 x_return_status := 0;
3395 x_return_code := 'SITE_VALID';
3396
3397 END validate_site;
3398
3399 -- ======================================================================
3400 -- PROCEDURE: IS_BIDDING_ALLOWED PUBLIC
3401 -- PARAMETERS:
3402 -- p_auc_header_id IN auction header id of negotiation
3403 -- p_tpid IN trading partner id of supplier
3404 -- p_tpcid IN trading partner contact id of supplier
3405 -- p_vensid IN vendor site to place a bid for
3406 -- p_venscode IN corresponding vendor site code
3407 -- p_buyer_user IN determines if surrogate bid
3408 -- p_action_code IN determines if certain validation should be suppressed
3409 -- x_return_status OUT 0 for success, 1 for error
3410 -- x_return_code OUT returned error code, or SUCCESS
3411 --
3412 -- COMMENT: Determine if the bidding action specified by action code can
3413 -- be completed at this time.
3414 -- ======================================================================
3415 PROCEDURE is_bidding_allowed
3416 (
3417 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3418 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
3419 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
3420 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
3421 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
3422 p_buyer_user IN VARCHAR2,
3423 p_action_code IN VARCHAR2,
3424 x_return_status OUT NOCOPY NUMBER,
3425 x_return_code OUT NOCOPY VARCHAR2
3426 ) IS
3427 l_current_date TIMESTAMP;
3428 l_bid_number NUMBER;
3429 l_view_by_date pon_auction_headers_all.view_by_date%TYPE;
3430 l_open_date pon_auction_headers_all.open_bidding_date%TYPE;
3431 l_close_date pon_auction_headers_all.close_bidding_date%TYPE;
3432 l_auction_status pon_auction_headers_all.auction_status%TYPE;
3433 l_award_status pon_auction_headers_all.award_status%TYPE;
3434 l_bid_list_type pon_auction_headers_all.bid_list_type%TYPE;
3435 l_invited_flag VARCHAR2(1);
3436 l_bid_freq_code pon_auction_headers_all.bid_frequency_code%TYPE;
3437 l_has_draft_flag VARCHAR2(1);
3438 l_has_bid_flag VARCHAR2(1);
3439 l_has_surrog_flag VARCHAR2(1);
3440 l_auction_paused VARCHAR2(1);
3441 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
3442 l_supp_end_date TIMESTAMP;
3443 BEGIN
3444
3445 -- Verify that a valid site id has been specified if not loading a bid
3446 IF (p_action_code <> 'LOAD_BID' AND
3447 p_action_code <> 'LOAD_DRAFT') THEN
3448 validate_site(p_auc_header_id, p_tpid, p_vensid, p_venscode, x_return_status, x_return_code);
3449
3450 IF (x_return_status = 1) THEN
3451 -- return status and code already set
3452 RETURN;
3453 END IF;
3454 END IF;
3455
3456 BEGIN
3457 -- select the various values
3458 SELECT sysdate,
3459 nvl(ah.view_by_date, ah.open_bidding_date),
3460 ah.open_bidding_date,
3461 ah.close_bidding_date,
3462 ah.auction_status,
3463 nvl(ah.award_status, 'NO'),
3464 ah.bid_list_type,
3465 ah.bid_frequency_code,
3466 ah.auction_header_id_orig_amend,
3467 nvl(ah.is_paused, 'N')
3468 INTO l_current_date,
3469 l_view_by_date,
3470 l_open_date,
3471 l_close_date,
3472 l_auction_status,
3473 l_award_status,
3474 l_bid_list_type,
3475 l_bid_freq_code,
3476 l_orig_amend_id,
3477 l_auction_paused
3478 FROM pon_auction_headers_all ah
3479 WHERE auction_header_id = p_auc_header_id;
3480
3481 EXCEPTION
3482 WHEN NO_DATA_FOUND THEN
3483 x_return_status := 1;
3484 x_return_code := 'INVALID_HEADER_ID';
3485 RETURN;
3486 END;
3487
3488
3489 -- View by date (or open date if null) should be reached
3490 IF (l_current_date < l_view_by_date) THEN
3491 x_return_status := 1;
3492 x_return_code := 'AUCTION_NOT_VIEW';
3493 RETURN;
3494 END IF;
3495
3496 -- Auction should not be cancelled
3497 IF (l_auction_status = 'CANCELLED') THEN
3498 x_return_status := 1;
3499 x_return_code := 'AUCTION_CANCELLED';
3500 RETURN;
3501 END IF;
3502
3503 -- Auction should not have been deleted
3507 RETURN;
3504 IF (l_auction_status = 'DELETED') THEN
3505 x_return_status := 1;
3506 x_return_code := 'AUCTION_DELETED';
3508 END IF;
3509
3510 -- When saving bid
3511 IF (p_action_code = 'SAVE_BID') THEN
3512
3513 -- Auction should be open
3514 IF (l_current_date < l_open_date) THEN
3515 x_return_status := 1;
3516 x_return_code := 'AUCTION_NOT_OPEN';
3517 RETURN;
3518 END IF;
3519
3520 -- Auction should not be paused
3521 IF (l_auction_status = 'PAUSED') THEN
3522 x_return_status := 1;
3523 x_return_code := 'AUCTION_PAUSED';
3524 RETURN;
3525 END IF;
3526
3527 -- make sure there is a draft that we're trying
3528 -- to publish
3529 BEGIN
3530 SELECT bid_number
3531 INTO l_bid_number
3532 FROM pon_bid_headers bh
3533 WHERE bh.auction_header_id = p_auc_header_id
3534 AND bh.trading_partner_id = p_tpid
3535 AND bh.trading_partner_contact_id = p_tpcid
3536 AND bh.vendor_site_id = p_vensid
3537 AND bh.bid_status = 'DRAFT';
3538
3539 EXCEPTION
3540 WHEN NO_DATA_FOUND THEN
3541 x_return_status := 1;
3542 x_return_code := 'NO_DRAFT_BID';
3543 RETURN;
3544 END;
3545
3546 -- Supplier should not be invalid
3547 BEGIN
3548 SELECT nvl(pov.end_date_active, sysdate)
3549 INTO l_supp_end_date
3550 FROM pon_bid_headers bh, po_vendors pov
3551 WHERE bh.auction_header_id = p_auc_header_id
3552 AND bh.trading_partner_id = p_tpid
3553 AND bh.trading_partner_contact_id = p_tpcid
3554 AND bh.vendor_site_id = p_vensid
3555 AND bh.bid_status = 'DRAFT'
3556 AND pov.vendor_id = bh.vendor_id;
3557
3558 EXCEPTION
3559 WHEN NO_DATA_FOUND THEN
3560 l_supp_end_date := null;
3561 END;
3562
3563 IF (l_supp_end_date IS null OR l_supp_end_date < l_current_date) THEN
3564 x_return_status := 1;
3565 x_return_code := 'INACTIVE_SUPPLIER_ERROR';
3566 RETURN;
3567 END IF;
3568
3569 END IF;
3570
3571 -- Check auction close date (unless the auction is paused)
3572 IF (l_auction_paused <> 'Y' AND l_close_date < l_current_date) THEN
3573
3574 -- Auction should not be closed if not a buyer
3575 IF (p_buyer_user = 'N') THEN
3576 x_return_status := 1;
3577 x_return_code := 'AUCTION_CLOSED';
3578 RETURN;
3579 -- Award process should not be started if buyer
3580 ELSIF (l_award_status <> 'NO') THEN
3581 x_return_status := 1;
3582 x_return_code := 'AUCTION_AWARD_STARTED';
3583 RETURN;
3584 END IF;
3585 END IF;
3586
3587 -- If private auction, check if supplier is invited
3588 IF (l_bid_list_type = 'PRIVATE_BID_LIST') THEN
3589
3590 -- Do an existence check
3591 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
3592 INTO l_invited_flag
3593 FROM pon_bidding_parties
3594 WHERE auction_header_id = p_auc_header_id
3595 AND trading_partner_id = p_tpid;
3596
3597 IF (l_invited_flag = 'N') THEN
3598 x_return_status := 1;
3599 x_return_code := 'NOT_INVITED';
3600 RETURN;
3601 END IF;
3602 END IF;
3603
3604 -- Check if single best bid auction
3605 IF (l_bid_freq_code = 'SINGLE_BID_ONLY') THEN
3606
3607 -- Do an existence check
3608 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
3609 INTO l_has_bid_flag
3610 FROM pon_bid_headers
3611 WHERE auction_header_id = p_auc_header_id
3612 AND trading_partner_id = p_tpid
3613 AND vendor_site_id = p_vensid
3614 AND bid_status = 'ACTIVE';
3615
3616 IF (l_has_bid_flag = 'Y') THEN
3617 x_return_status := 1;
3618 x_return_code := 'SINGLE_BEST_BID';
3619 RETURN;
3620 END IF;
3621
3622 -- Do an existence check
3623 -- bug 5041654
3624 -- if we are loading a bid, we do not need
3625 -- to check whether some other user already has a draft bid
3626 -- in case of a single-best bid auction, as it is quite rare occurence
3627 -- in R12 that 2 users from same company will click 'create bid' at the same
3628 -- time. We will have this check during submit bid anyways
3629
3630 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
3631 INTO l_has_draft_flag
3632 FROM pon_bid_headers
3633 WHERE auction_header_id = p_auc_header_id
3634 AND trading_partner_id = p_tpid
3635 AND trading_partner_contact_id <> p_tpcid
3636 AND vendor_site_id = p_vensid
3637 AND bid_status = 'DRAFT';
3638
3639 IF (l_has_draft_flag = 'Y' AND
3640 p_action_code <> 'LOAD_DRAFT') THEN
3641 x_return_status := 1;
3642 x_return_code := 'OTHER_USERS_DRAFT_SBB';
3643 RETURN;
3644 END IF;
3645 END IF;
3646
3647 -- Check if a buyer/supplier has already placed a bid on the round
3648 -- I.e. check for surrog bid if supplier, or a supplier bid if buyer
3649 SELECT decode(count(bh.auction_header_id), 0, 'N', 'Y')
3650 INTO l_has_surrog_flag
3651 FROM pon_bid_headers bh, pon_auction_headers_all ah
3652 WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
3653 AND bh.auction_header_id = ah.auction_header_id
3654 AND bh.trading_partner_id = p_tpid
3655 AND bh.trading_partner_contact_id = p_tpcid
3659 IF (p_buyer_user = 'Y') THEN
3656 AND nvl(bh.surrog_bid_flag, 'N') = decode(p_buyer_user, 'Y', 'N', 'Y');
3657
3658 IF (l_has_surrog_flag = 'Y') THEN
3660 x_return_status := 1;
3661 x_return_code := 'SURROG_BID_ERROR_BUYER';
3662 ELSE
3663 x_return_status := 1;
3664 x_return_code := 'SURROG_BID_ERROR_SUPPLIER';
3665 END IF;
3666 RETURN;
3667 END IF;
3668
3669 x_return_status := 0;
3670 x_return_code := 'SUCCESS';
3671
3672 END is_bidding_allowed;
3673
3674 -- ======================================================================
3675 -- PROCEDURE: GET_SOURCE_BID PRIVATE
3676 -- PARAMETERS:
3677 -- p_auc_header_id IN auction header id of negotiation
3678 -- p_tpid IN trading partner id of supplier
3679 -- p_tpcid IN trading partner contact id of supplier
3680 -- p_vensid IN vendor site to place a bid for
3681 -- p_action_code IN determines if certain validation should be suppressed
3682 -- x_rebid_flag OUT flag determining if rebid or not
3683 -- x_bid_number OUT bid number of exisiting draft on current amendment
3684 -- x_prev_bid_number OUT source bid number
3685 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
3686 -- x_round_bid_def OUT Y if source bid is on a previous round
3687 -- x_prev_bid_disq OUT Y is source bid was disqualified
3688 -- x_return_status OUT 0 for success, 1 for error
3689 -- x_return_code OUT returned error code, or SUCCESS
3690 --
3691 -- COMMENT: Determine if a draft already exists on the current amendment.
3692 -- If not, determines which bid to default from or to create a new draft.
3693 -- Also checks if another user has a draft on the current amendment.
3694 -- ======================================================================
3695 PROCEDURE get_source_bid
3696 (
3697 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3698 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
3699 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
3700 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
3701 p_action_code IN VARCHAR2,
3702
3703 x_rebid_flag OUT NOCOPY VARCHAR2,
3704 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
3705 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
3706 x_amend_bid_def OUT NOCOPY VARCHAR2,
3707 x_round_bid_def OUT NOCOPY VARCHAR2,
3708 x_prev_bid_disq OUT NOCOPY VARCHAR2,
3709 x_return_status OUT NOCOPY NUMBER,
3710 x_return_code OUT NOCOPY VARCHAR2
3711 ) IS
3712 l_source_header_id pon_auction_headers_all.auction_header_id%TYPE;
3713 l_status_order NUMBER;
3714 l_rel12_draft pon_bid_headers.rel12_draft_flag%TYPE;
3715
3716 l_prev_round_id pon_auction_headers_all.auction_header_id%TYPE;
3717 l_orig_amend_id pon_auction_headers_all.auction_header_id%TYPE;
3718
3719 l_terms_cond_apply VARCHAR2(1);
3720
3721 l_ignored_return pon_bid_headers.bid_number%TYPE;
3722
3723 -- select bids on all amendments/previous round by the current user for the current site
3724 CURSOR current_users_bids IS
3725 SELECT bh.bid_number,
3726 bh.auction_header_id,
3727 decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
3728 'RESUBMISSION', 3, 'DISQUALIFIED', 4) status_order,
3729 decode(bh.bid_status, 'ACTIVE',
3730 decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
3731 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
3732 decode(ah.auction_header_id, p_auc_header_id, 'N',
3733 decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
3734
3735 decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
3736 nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag
3737 FROM pon_bid_headers bh, pon_auction_headers_all ah
3738 WHERE
3739 -- look at all amendments on the current round
3740 (ah.auction_header_id_orig_amend = l_orig_amend_id
3741 -- look at all amendments on the previous round
3742 OR ah.auction_header_id_orig_amend = l_prev_round_id)
3743 AND bh.auction_header_id = ah.auction_header_id
3744 AND bh.trading_partner_id = p_tpid
3745 AND bh.trading_partner_contact_id = p_tpcid
3746 AND nvl(bh.vendor_site_id, -1) = p_vensid
3747 -- we ignore DRAFT bids on previous rounds
3748 AND ((bh.bid_status = 'DRAFT'
3749 AND ah.auction_header_id_orig_amend = l_orig_amend_id)
3750 OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED'))
3751 ORDER BY nvl(ah.auction_round_number, 1) DESC,
3752 ah.amendment_number DESC, status_order ASC, bh.publish_date DESC;
3753
3754 -- select bids on the current amendment/previous rounds by the current user for the current site
3755 CURSOR other_users_bids IS
3756 SELECT 1 return_status,
3757 decode(bh.auction_header_id, p_auc_header_id,
3758 decode(ah.bid_frequency_code, 'SINGLE_BID_ONLY',
3759 decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT_SBB',
3760 'ACTIVE', 'SINGLE_BEST_BID'),
3761 decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT',
3762 'ACTIVE', 'OTHER_USERS_ACTIVE')),
3763 'OTHER_USERS_PREV_ROUND') return_code
3764 FROM pon_bid_headers bh, pon_auction_headers_all ah
3765 WHERE
3766 -- look at the current amendment
3767 (ah.auction_header_id = p_auc_header_id
3768 AND bh.bid_status IN ('DRAFT', 'ACTIVE')
3769 -- look at the previous round
3770 OR ah.auction_header_id_orig_amend = l_prev_round_id
3771 AND bh.bid_status = 'ACTIVE')
3772 AND bh.auction_header_id = ah.auction_header_id
3773 AND bh.trading_partner_id = p_tpid
3777 decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2) ASC, bh.publish_date DESC;
3774 AND bh.trading_partner_contact_id <> p_tpcid
3775 AND nvl(bh.vendor_site_id, -1) = p_vensid
3776 ORDER BY nvl(ah.auction_round_number, 1) DESC, ah.amendment_number DESC,
3778
3779 BEGIN
3780
3781 -- Get the original amendment id's for the current and prev rounds.
3782 -- Also check if contracts have been installed
3783 SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend,
3784 nvl2(ah.contract_id, 'Y', 'N')
3785 INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply
3786 FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
3787 WHERE ah.auction_header_id = p_auc_header_id
3788 and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
3789
3790 -- Retrieve the backing bid info from the cursor
3791 -- We only need the first such bid
3792 OPEN current_users_bids;
3793 FETCH current_users_bids
3794 INTO x_prev_bid_number, l_source_header_id, l_status_order,
3795 x_rebid_flag, x_round_bid_def, x_amend_bid_def,
3796 x_prev_bid_disq, l_rel12_draft;
3797 CLOSE current_users_bids;
3798
3799 -- If the current user has a previous bid
3800 IF (x_prev_bid_number IS NOT null) THEN
3801
3802 -- had a backing DRAFT bid
3803 IF (l_status_order = 1) THEN
3804 -- Check if the draft is on the current amendment
3805 IF (l_source_header_id = p_auc_header_id) THEN
3806
3807 -- If it is a pre-release 12 draft, need to insert missing lines
3808 -- NOTE: expand_draft is batched
3809 IF (l_rel12_draft <> 'Y') THEN
3810 expand_draft(x_prev_bid_number, x_rebid_flag);
3811 END IF;
3812
3813 -- Set return bid number. No defaulting required
3814 x_bid_number := x_prev_bid_number;
3815 x_prev_bid_number := NULL;
3816 x_return_status := 0;
3817 x_return_code := 'DRAFT';
3818
3819 -- draft is on a previous amendment/round
3820 ELSE
3821 -- Need to archive the previous amend/round DRAFT bid
3822 UPDATE pon_bid_headers
3823 SET bid_status = 'ARCHIVED_DRAFT',
3824 last_update_date = sysdate
3825 WHERE bid_number = x_prev_bid_number;
3826
3827 -- If pre-release 12, call handle_proxy to update price, limit_price
3828 -- set has_bid_flag
3829 IF (l_rel12_draft <> 'Y') THEN
3830
3831 handle_proxy
3832 (p_auc_header_id,
3833 x_prev_bid_number,
3834 p_tpid,
3835 p_tpcid,
3836 p_vensid,
3837 l_ignored_return,
3838 x_rebid_flag);
3839
3840 populate_has_bid_flag(p_auc_header_id, x_prev_bid_number);
3841 END IF;
3842
3843 -- All flags are set; indicate defaulting is necessary
3844 x_return_status := 0;
3845 x_return_code := 'DEFAULT';
3846 END IF;
3847
3848 -- had a backing ACTIVE, RESUBMISSION, or DISQUALIFIED bid
3849 ELSE
3850 -- All flags are set; indicate that defaulting is necessary
3851 x_return_status := 0;
3852 x_return_code := 'DEFAULT';
3853 END IF;
3854 ELSE
3855
3856 -- Retrieve other users bid info from the cursor
3857 -- We only need the first bid
3858 OPEN other_users_bids;
3859 FETCH other_users_bids
3860 INTO x_return_status, x_return_code;
3861 CLOSE other_users_bids;
3862
3863 IF (x_return_status = 1) THEN
3864 RETURN;
3865 END IF;
3866
3867 -- Creating fresh bid.
3868 -- User must accept terms and conditions if contracts installed.
3869 IF (l_terms_cond_apply = 'Y') THEN
3870 x_return_status := 1;
3871 x_return_code := 'TO_TERMS_COND';
3872 ELSE
3873 x_return_status := 0;
3874 x_return_code := 'CREATE_NEW_DRAFT';
3875 END IF;
3876 END IF;
3877
3878 END get_source_bid;
3879
3880 -- ======================================================================
3881 -- PROCEDURE: CREATE_DEFAULTED_BID PUBLIC
3882 -- PARAMETERS:
3883 -- p_new_header_id IN auction header id of negotiation
3884 -- p_source_bid IN the bid to default from
3885 -- x_bid_number OUT bid number of draft loaded or created
3886 -- x_return_status OUT 0 for success, 1 for error
3887 -- x_return_code OUT returned error code, or SUCCESS
3888 --
3889 -- COMMENT: create a new draft on p_auc_header_id, defaulting from
3890 -- p_source_bid
3891 -- ======================================================================
3892 PROCEDURE create_defaulted_draft
3893 (
3894 p_new_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
3895 p_source_bid IN pon_bid_headers.bid_number%TYPE,
3896 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
3897 ) IS
3898 l_tpid pon_bid_headers.trading_partner_id%TYPE;
3899 l_tpcid pon_bid_headers.trading_partner_contact_id%TYPE;
3900 l_tpname pon_bid_headers.trading_partner_name%TYPE;
3901 l_tpcname pon_bid_headers.trading_partner_contact_name%TYPE;
3902 l_userid pon_bid_headers.created_by%TYPE;
3903 l_venid pon_bid_headers.vendor_id%TYPE;
3904 l_vensid pon_bid_headers.vendor_site_id%TYPE;
3905 l_venscode pon_bid_headers.vendor_site_code%TYPE;
3906 l_buyer_user VARCHAR2(1);
3907 l_auctpid pon_bid_headers.surrog_bid_created_tp_id%TYPE;
3908 l_auctpcid pon_bid_headers.surrog_bid_created_contact_id%TYPE;
3909
3910 l_rebid_flag VARCHAR2(1);
3911 l_prev_bid_disq VARCHAR2(1);
3912 l_new_round_or_amended VARCHAR2(1);
3916
3913 l_return_status NUMBER;
3914 l_return_code VARCHAR2(30);
3915 BEGIN
3917 -- The bid the user is currently working with has been obsoleted
3918 -- so create a new draft, defaulting from the previous bid.
3919 -- Eg. If the user clicks bid by spreadsheet from the manage draft
3920 -- responses page, but a new amendment has been created.
3921
3922 l_rebid_flag := 'N';
3923 l_prev_bid_disq := 'N';
3924
3925 -- The following flag is needed for payments copy
3926 -- This sets to y if defaulting is happening because the
3927 -- negotiation being amended or new round started
3928 l_new_round_or_amended := 'Y';
3929
3930 -- Select out the header values from the previous draft
3931 SELECT bh.trading_partner_id,
3932 bh.trading_partner_contact_id,
3933 bh.trading_partner_name,
3934 bh.trading_partner_contact_name,
3935 bh.created_by,
3936 bh.vendor_id,
3937 bh.vendor_site_id,
3938 bh.vendor_site_code,
3939 bh.surrog_bid_created_tp_id,
3940 bh.surrog_bid_created_contact_id,
3941 bh.surrog_bid_flag
3942 INTO l_tpid,
3943 l_tpcid,
3944 l_tpname,
3945 l_tpcname,
3946 l_userid,
3947 l_venid,
3948 l_vensid,
3949 l_venscode,
3950 l_auctpid,
3951 l_auctpcid,
3952 l_buyer_user
3953 FROM pon_bid_headers bh
3954 WHERE bh.bid_number = p_source_bid;
3955
3956 -- Create the new bid
3957 create_new_draft_bid
3958 (p_new_header_id,
3959 p_source_bid,
3960 l_tpid,
3961 l_tpcid,
3962 l_tpname,
3963 l_tpcname,
3964 l_userid,
3965 l_venid,
3966 l_vensid,
3967 l_venscode,
3968 l_auctpid,
3969 l_auctpcid,
3970 l_buyer_user,
3971 l_new_round_or_amended,
3972 l_rebid_flag,
3973 l_prev_bid_disq,
3974 x_bid_number,
3975 l_return_status,
3976 l_return_code);
3977
3978 -- Update the status of the previous bid
3979 -- NOTE: if this procedure is used for cases other than spreadsheet upload
3980 -- on a DRAFT after an amendment, the status will need to be set correctly
3981 UPDATE pon_bid_headers bh
3982 SET bh.bid_status = decode('DRAFT', 'ARCHIVED_DRAFT', bh.bid_status)
3983 WHERE bh.bid_number = p_source_bid;
3984
3985 END create_defaulted_draft;
3986
3987 -- ======================================================================
3988 -- PROCEDURE: CHECK_AND_LOAD_BID PUBLIC
3989 -- PARAMETERS:
3990 -- p_auc_header_id IN auction header id of negotiation
3991 -- p_draft_number IN non-null if a specific draft is to be loaded
3992 -- or if the action code is CREATE_NEW_AMEND_DRAFT
3993 -- p_tpid IN trading partner id of supplier
3994 -- p_tpcid IN trading partner contact id of supplier
3995 -- p_tpname IN trading partner name of supplier
3996 -- p_tpcname IN trading partner contact name of supplier
3997 -- p_userid IN userid of bid creator
3998 -- p_venid IN vendor id
3999 -- p_vensid IN vendor site to place a bid for
4000 -- p_venscode IN corresponding vendor site code
4001 -- p_buyer_user IN determines if surrogate bid
4002 -- p_auctpid IN trading partner id of buyer if surrogate bid
4003 -- p_auctpcid IN trading partner contact id of buyer if surrogate bid
4004
4005 -- x_bid_number OUT bid number of draft loaded or created
4006 -- x_rebid_flag OUT flag determining if rebid or not
4007 -- x_prev_bid_number OUT source bid number
4008 -- x_amend_bid_def OUT Y if source bid is on a previous amendment
4009 -- x_round_bid_def OUT Y if source bid is on a previous round
4010 -- x_prev_bid_disq OUT Y if source bid was disqualified
4011 -- x_edit_draft OUT Y if we loaded an existing draft
4012
4013 -- p_action_code IN determine if a special action needs to be taken
4014 -- x_return_status OUT 0 for success, 1 for error
4015 -- x_return_code OUT returned error code, or SUCCESS
4016 --
4017 -- COMMENT: Main procedure which determines whether a new or defaulted bid
4018 -- is to be created. Or whether a draft already exists
4019 -- ======================================================================
4020 PROCEDURE check_and_load_bid
4021 (
4022 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
4023 p_draft_number IN pon_bid_headers.bid_number%TYPE,
4024 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
4025 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
4026 p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
4027 p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
4028 p_userid IN pon_bid_headers.created_by%TYPE,
4029 p_venid IN pon_bid_headers.vendor_id%TYPE,
4030 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
4031 p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
4032 p_buyer_user IN VARCHAR2,
4033 p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
4034 p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
4035
4036 x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4037 x_rebid_flag OUT NOCOPY VARCHAR2,
4038 x_prev_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE,
4039 x_amend_bid_def OUT NOCOPY VARCHAR2,
4040 x_round_bid_def OUT NOCOPY VARCHAR2,
4041 x_prev_bid_disq OUT NOCOPY VARCHAR2,
4042 x_edit_draft OUT NOCOPY VARCHAR2,
4043
4044 p_action_code IN VARCHAR2,
4045 x_return_status OUT NOCOPY NUMBER,
4046 x_return_code OUT NOCOPY VARCHAR2
4047 ) IS
4051 l_temp_num NUMBER;
4048 l_rel12_draft pon_bid_headers.rel12_draft_flag%TYPE;
4049 l_new_round_or_amended VARCHAR2(1);
4050
4052 BEGIN
4053
4054 -- The user indicated that they want to create a fresh bid
4055 IF (p_action_code = 'CREATE_NEW_DRAFT') THEN
4056
4057 x_rebid_flag := 'N';
4058 x_prev_bid_number := NULL;
4059 x_amend_bid_def := 'N';
4060 x_round_bid_def := 'N';
4061 x_prev_bid_disq := 'N';
4062 x_edit_draft := 'N';
4063
4064 -- The following flag is needed for payments copy
4065 -- This sets to y if defaulting is happening because the
4066 -- negotiation being amended or new round started
4067 l_new_round_or_amended := 'N';
4068
4069 -- Create the new bid
4070 create_new_draft_bid
4071 (p_auc_header_id,
4072 x_prev_bid_number,
4073 p_tpid,
4074 p_tpcid,
4075 p_tpname,
4076 p_tpcname,
4077 p_userid,
4078 p_venid,
4079 p_vensid,
4080 p_venscode,
4081 p_auctpid,
4082 p_auctpcid,
4083 p_buyer_user,
4084 l_new_round_or_amended,
4085 x_rebid_flag,
4086 x_prev_bid_disq,
4087 x_bid_number,
4088 x_return_status,
4089 x_return_code);
4090
4091 RETURN;
4092
4093 -- If we already know which draft to work with
4094 ELSIF (p_draft_number IS NOT null AND p_draft_number > 0) THEN
4095
4096 -- Check that the draft is not locked by another user and lock it
4097 IF (p_buyer_user = 'Y') THEN
4098 lock_draft
4099 (p_draft_number,
4100 p_auctpid,
4101 p_auctpcid,
4102 x_return_status,
4103 x_return_code);
4104 ELSE
4105 lock_draft
4106 (p_draft_number,
4107 p_tpid,
4108 p_tpcid,
4109 x_return_status,
4110 x_return_code);
4111 END IF;
4112
4113 IF (x_return_status = 1) THEN
4114 RETURN;
4115 END IF;
4116
4117 -- If it is a pre-release 12 draft, we need to expand it
4118 SELECT nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag
4119 INTO l_rel12_draft
4120 FROM pon_bid_headers bh
4121 WHERE bh.bid_number = p_draft_number;
4122
4123 -- If the draft is pre-release 12 we need to fill in the missing lines
4124 IF (l_rel12_draft <> 'Y') THEN
4125 expand_draft(p_draft_number, x_rebid_flag);
4126 END IF;
4127
4128 -- Finally, handle proxy bidding and copy rank
4129 handle_proxy
4130 (p_auc_header_id,
4131 p_draft_number,
4132 p_tpid,
4133 p_tpcid,
4134 p_vensid,
4135 x_prev_bid_number,
4136 x_rebid_flag);
4137
4138 -- set flags and return values before returning
4139 x_bid_number := p_draft_number;
4140 x_amend_bid_def := 'N';
4141 x_round_bid_def := 'N';
4142 x_prev_bid_disq := 'N';
4143 x_edit_draft := 'Y';
4144 x_return_status := 0;
4145 x_return_code := 'SUCCESS';
4146 RETURN;
4147 END IF;
4148
4149 -- We are unsure whether a draft exists or we're creating a new bid
4150
4151 -- Check if all amendments have been acknowledged
4152 check_amendments_acked(p_auc_header_id, p_tpid, p_tpcid,
4153 x_return_status, x_return_code);
4154
4155 IF (x_return_status = 1) THEN
4156 RETURN;
4157 END IF;
4158
4159 -- Get the source bid number and other flags
4160 get_source_bid
4161 (p_auc_header_id,
4162 p_tpid,
4163 p_tpcid,
4164 p_vensid,
4165 p_action_code,
4166 x_rebid_flag,
4167 x_bid_number,
4168 x_prev_bid_number,
4169 x_amend_bid_def,
4170 x_round_bid_def,
4171 x_prev_bid_disq,
4172 x_return_status,
4173 x_return_code);
4174
4175 IF (x_return_status = 1) THEN
4176 RETURN;
4177 END IF;
4178
4179 -- If a draft exists, check that it is not locked by another user and lock it
4180 IF (x_return_code = 'DRAFT') THEN
4181 IF (p_buyer_user = 'Y') THEN
4182 lock_draft(x_bid_number, p_auctpid, p_auctpcid,
4183 x_return_status, x_return_code);
4184 ELSE
4185 lock_draft(x_bid_number, p_tpid, p_tpcid,
4186 x_return_status, x_return_code);
4187 END IF;
4188
4189 IF (x_return_status = 1) THEN
4190 RETURN;
4191 END IF;
4192
4193 -- set return status
4194 x_edit_draft := 'Y';
4195 x_return_status := 0;
4196 x_return_code := 'SUCCESS';
4197
4198 -- Default the bid if necessary
4199 ELSIF (x_return_code = 'DEFAULT') THEN
4200
4201 -- The following flag is needed for payments copy
4202 -- This sets to y if defaulting is happening because the
4203 -- negotiation being amended or new round started
4204
4205 IF (x_amend_bid_def = 'Y' OR x_round_bid_def = 'Y') THEN
4206 l_new_round_or_amended := 'Y';
4207 ELSE
4208 l_new_round_or_amended := 'N';
4209 END IF;
4210
4211 create_new_draft_bid
4212 (p_auc_header_id,
4213 x_prev_bid_number,
4214 p_tpid,
4215 p_tpcid,
4216 p_tpname,
4217 p_tpcname,
4218 p_userid,
4219 p_venid,
4220 p_vensid,
4221 p_venscode,
4222 p_auctpid,
4223 p_auctpcid,
4224 p_buyer_user,
4225 l_new_round_or_amended,
4226 x_rebid_flag,
4227 x_prev_bid_disq,
4228 x_bid_number,
4229 x_return_status,
4230 x_return_code);
4231
4232 -- set return status
4233 x_edit_draft := 'N';
4234
4235 -- There is already a draft created for this user. Maybe
4236 -- through a different session. In this case, return
4237 -- error MULTIPLE_REBID
4238 IF (x_return_status = 1) THEN
4239 RETURN;
4240 END IF;
4241
4242 -- Create a fresh bid if necessary
4243 ELSIF (x_return_code = 'CREATE_NEW_DRAFT') THEN
4244
4245 x_rebid_flag := 'N';
4246 x_prev_bid_number := NULL;
4247 x_amend_bid_def := 'N';
4248 x_round_bid_def := 'N';
4249 x_prev_bid_disq := 'N';
4250 x_edit_draft := 'N';
4251
4252 -- The following flag is needed for payments copy
4253 -- This sets to y if defaulting is happening because the
4254 -- negotiation being amended or new round started
4255 l_new_round_or_amended := 'N';
4256
4257 -- Create the new bid
4258 create_new_draft_bid
4259 (p_auc_header_id,
4260 x_prev_bid_number,
4261 p_tpid,
4262 p_tpcid,
4263 p_tpname,
4264 p_tpcname,
4265 p_userid,
4266 p_venid,
4267 p_vensid,
4268 p_venscode,
4269 p_auctpid,
4270 p_auctpcid,
4271 p_buyer_user,
4272 l_new_round_or_amended,
4273 x_rebid_flag,
4274 x_prev_bid_disq,
4275 x_bid_number,
4276 x_return_status,
4277 x_return_code);
4278 RETURN;
4279
4280 END IF;
4281
4282 -- Finally, handle proxy bidding and copy rank
4283 handle_proxy
4284 (p_auc_header_id,
4285 x_bid_number,
4286 p_tpid,
4287 p_tpcid,
4288 p_vensid,
4289 l_temp_num,
4290 x_rebid_flag);
4291
4292 -- We get the returned bid_number into l_temp_num because it will null
4293 -- out a x_prev_bid_number if it is not the rebidding case
4294 IF (x_prev_bid_number IS null) THEN
4295 x_prev_bid_number := l_temp_num;
4296 END IF;
4297
4298 END check_and_load_bid;
4299
4300 -- ======================================================================
4301 -- FUNCTION: GET_SOURCE_BID_FOR_SPREADSHEET
4302 -- PARAMETERS:
4303 -- p_auc_header_id IN auction header id of negotiation
4304 -- p_prev_round_auc_header_id IN auction header id of prev round negotiation
4305 -- p_tpid IN trading partner id of supplier
4306 -- p_tpcid IN trading partner contact id of supplier
4307 -- p_auc_header_id_orig_amend IN auction header id of original amendment
4308 -- p_amendment_number IN amendment number
4309 -- p_vensid IN vendor site to place a bid for
4310 --
4311 -- COMMENT: This function is only used in spreadsheet export case.
4312 -- Determine whether there are any bids existing for the current amendment.
4313 -- If not, determines whether there are any bids in previous amendment
4314 -- of current round; If still not, check whether there is an active bid
4315 -- from previous round
4316 -- ======================================================================
4317 FUNCTION get_source_bid_for_spreadsheet
4318 (
4319 p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
4320 p_prev_round_auc_header_id IN pon_auction_headers_all.auction_header_id_prev_round%TYPE,
4321 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
4322 p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
4323 p_auc_header_id_orig_amend IN pon_auction_headers_all.auction_header_id_orig_amend%TYPE,
4324 p_amendment_number IN pon_auction_headers_all.amendment_number%TYPE,
4325 p_vensid IN pon_bid_headers.vendor_site_id%TYPE
4326
4327 ) RETURN NUMBER IS
4328
4329 CURSOR current_amendment_bids IS
4330 select bid_number
4331 from pon_bid_headers
4335 and nvl(vendor_site_id, -1) = nvl(p_vensid, -1)
4332 where auction_header_id = p_auc_header_id
4333 and trading_partner_id = p_tpid
4334 and trading_partner_contact_id = p_tpcid
4336 and bid_status in ('DRAFT', 'ACTIVE', 'DISQUALIFIED')
4337 order by decode(bid_status, 'DRAFT', 3,
4338 'ACTIVE', 2,
4339 'DISQUALIFIED', 1) desc, publish_date desc;
4340
4341 CURSOR previous_amendments_bids IS
4342 select bh.bid_number
4343 from
4344 pon_bid_headers bh,
4345 pon_auction_headers_all ah
4346 where
4347 bh.auction_header_id = ah.auction_header_id
4348 and ah.auction_header_id_orig_amend = p_auc_header_id_orig_amend
4349 and bh.trading_partner_id = p_tpid
4350 and bh.trading_partner_contact_id = p_tpcid
4351 and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1)
4352 and bh.bid_status in ('DRAFT', 'RESUBMISSION', 'DISQUALIFIED')
4353 order by ah.amendment_number desc,
4354 decode(bh.bid_status, 'DRAFT', 3,
4355 'RESUBMISSION' , 2,
4356 'DISQUALIFIED', 1) desc,
4357 bh.publish_date desc;
4358
4359 x_prev_bid_number NUMBER := -1;
4360
4361 BEGIN
4362
4363 OPEN current_amendment_bids;
4364 FETCH current_amendment_bids into x_prev_bid_number;
4365
4366 IF (current_amendment_bids%NOTFOUND) THEN
4367
4368 -- try to find a previous bid from previous amendments in the current round
4369 IF (p_amendment_number is not null AND p_amendment_number >=1) THEN
4370
4371 OPEN previous_amendments_bids;
4372 FETCH previous_amendments_bids into x_prev_bid_number;
4373 IF (previous_amendments_bids%NOTFOUND) THEN
4374 x_prev_bid_number := -1;
4375 END IF;
4376 CLOSE previous_amendments_bids;
4377
4378 ELSIF (p_prev_round_auc_header_id is not null) THEN
4379
4380 -- try to find an active bid from previous round
4381
4382 select max(bid_number) prev_round_bid
4383 into x_prev_bid_number
4384 from pon_bid_headers bh,
4385 pon_auction_headers_all ah,
4386 pon_auction_headers_all ah2
4387 where bh.auction_header_id = ah.auction_header_id
4388 and ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
4389 and ah2.auction_header_id = p_prev_round_auc_header_id
4390 and bh.trading_partner_id = p_tpid
4391 and bh.trading_partner_contact_id = p_tpcid
4392 and bh.bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
4393 and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1);
4394
4395 END IF;
4396 END IF;
4397
4398 CLOSE current_amendment_bids;
4399
4400 IF (x_prev_bid_number is null) THEN
4401 x_prev_bid_number := -1;
4402 END IF;
4403
4404 return x_prev_bid_number;
4405
4406 END GET_SOURCE_BID_FOR_SPREADSHEET;
4407
4408 --------------------------------------------------------------------------------
4409 -- can_supplier_create_payments --
4410 --------------------------------------------------------------------------------
4411 -- Start of Comments
4412 --
4413 -- API Name: can_supplier_create_payments
4414 --
4415 -- Type : Private
4416 --
4417 -- Pre-reqs: None
4418 --
4419 -- Function: This API is called by the Response Import Spreadsheet page.
4420 -- It determines if there are any lines in the RFQ that can have payments.
4421 -- If yes, then the "Pay Items" will be one of the option in the Import
4422 -- and Export poplists
4423 --
4424 --
4425 -- Parameters:
4426 --
4427 -- p_auction_header_id IN NUMBER
4428 -- Auction header id - required
4429 -- p_bid_number IN NUMBER
4430 -- Bid Number - required
4431 -- p_po_style_id IN NUMBER
4432 -- PO Style Id - required
4433 --
4434 --
4435 -- x_can_create_payments OUT VARCHAR2
4436 -- Returns Y if payments can be created for atleast one of the
4437 -- line to which supplier has access. Otherwise Returns N
4438 --
4439 --
4440 -- End of Comments
4441 --------------------------------------------------------------------------------
4442 -----
4443 PROCEDURE can_supplier_create_payments(
4444 p_auction_header_id IN NUMBER,
4445 p_bid_number IN NUMBER,
4446 x_can_create_payments OUT NOCOPY VARCHAR2) IS
4447 BEGIN
4448 x_can_create_payments := 'N';
4449
4450 -- Check if there are any lines OTHER THAN GROUP, LOT_LINE and to which supplier does not have access
4451 -- If there are lines then l_can_create_payment = 'Y'
4452 -- Else l_can_create_payment = 'N';
4453
4454 SELECT 'Y'
4455 INTO x_can_create_payments
4456 FROM dual
4457 WHERE EXISTS (SELECT 1
4458 FROM PON_AUCTION_ITEM_PRICES_ALL pai,
4459 PON_BID_ITEM_PRICES pbi,
4460 PON_AUCTION_HEADERS_ALL pah
4461 WHERE pai.auction_header_id = p_auction_header_id
4462 AND pai.group_type NOT IN ('GROUP','LOT_LINE')
4463 AND pbi.auction_header_id = pai.auction_header_id
4464 AND pbi.line_number = pai.line_number
4465 AND pbi.bid_number = p_bid_number
4469 EXCEPTION
4466 AND pah.auction_header_id = pai.auction_header_id
4467 AND pah.progress_payment_type <> 'NONE'
4468 AND pah.contract_type = 'STANDARD');
4470 WHEN NO_DATA_FOUND
4471 THEN
4472 x_can_create_payments := 'N';
4473 WHEN OTHERS THEN
4474 RAISE;
4475 END can_supplier_create_payments;
4476
4477 --------------------------------------------------------------------------------
4478 -- apply_price_factors --
4479 --------------------------------------------------------------------------------
4480 /**
4481 * This function calculates the total price on a line including the
4482 * buyer and the supplier price factors in auction currency.
4483 *
4484 * This function will be used in view objects to display supplier's
4485 * previous round price as the start price for this line instead of the
4486 * auction line start price.
4487 *
4488 * This is as per Cendant requirement to enforce upon suppliers to
4489 * bid lower than their bid on the previous round of the negotiation
4490 *
4491 * Currently anticipated usage of this function are on View Bid Page
4492 * (ViewBidItemsVO), Negotiation Summary page (AuctionItemPricesAllVO)
4493 * and bid creation page (ResponseAMImpl)
4494 *
4495 * p_auction_header_id - current round auction header id
4496 * p_prev_auc_active_bid_number - bid number on the previous round
4497 * p_line_number - current line number
4498 * p_unit_price - bid line price in auction currency
4499 * p_quantity - bid quantity for the current line
4500 */
4501 FUNCTION apply_price_factors(p_auction_header_id IN NUMBER,
4502 p_prev_auc_active_bid_number IN NUMBER,
4503 p_line_number IN NUMBER,
4504 p_unit_price IN NUMBER,
4505 p_quantity IN NUMBER
4506 )
4507 RETURN NUMBER IS
4508
4509 l_api_name CONSTANT VARCHAR2(30) := 'apply_price_factors';
4510 l_progress VARCHAR2(100) := '0';
4511
4512 l_total_price NUMBER;
4513 l_bid_line_pf_unit_price NUMBER;
4514 l_auc_pf_unit_price NUMBER;
4515
4516 l_contract_type pon_auction_headers_all.contract_type%TYPE;
4517 l_supplier_view_type pon_auction_headers_all.supplier_view_type%TYPE;
4518
4519 l_bid_auction_curr_unit_price pon_bid_item_prices.unit_price%TYPE;
4520 l_bid_quantity pon_bid_item_prices.quantity%TYPE;
4521
4522 l_is_spo_transformed VARCHAR2(1);
4523
4524 BEGIN
4525
4526 -- auction information that we need
4527
4528 l_progress := '10: fetch auction information';
4529
4530 SELECT contract_type,
4531 supplier_view_type
4532 INTO l_contract_type,
4533 l_supplier_view_type
4534 FROM pon_auction_headers_all
4535 WHERE auction_header_id = p_auction_header_id;
4536
4537
4538
4539 l_progress := '20: perform SPO/TRANSFORMED check';
4540
4541 IF (l_supplier_view_type = 'TRANSFORMED' AND
4542 l_contract_type = 'STANDARD') THEN
4543 l_is_spo_transformed := 'Y';
4544 ELSE
4545 l_is_spo_transformed := 'N';
4546 END IF;
4547
4548 -- calculate the buyer price factors
4549
4550 l_progress := '30: calculate unit price plus buyer price factors';
4551
4552 BEGIN
4553
4554 SELECT (p_unit_price * ppsf.percentage) +
4555 ppsf.unit_price +
4556 ppsf.fixed_amount/decode(l_is_spo_transformed,
4557 'Y', nvl(p_quantity, 1),
4558 nvl(aip.quantity, 1)
4559 )
4560 INTO l_auc_pf_unit_price
4561 FROM pon_pf_supplier_formula ppsf,
4562 pon_auction_item_prices_all aip,
4563 pon_bid_headers pbh
4564 WHERE ppsf.auction_header_id = p_auction_header_id
4565 AND ppsf.line_number = p_line_number
4566 AND ppsf.trading_partner_id = pbh.trading_partner_id
4567 AND ppsf.vendor_site_id = pbh.vendor_site_id
4568 AND pbh.bid_number = p_prev_auc_active_bid_number
4569 AND aip.auction_header_id = ppsf.auction_header_id
4570 AND aip.line_number = ppsf.line_number;
4571
4572 EXCEPTION
4573
4574 WHEN NO_DATA_FOUND THEN
4575 l_auc_pf_unit_price := p_unit_price;
4576
4577 END;
4578
4579 -- calculate the supplier price factors
4580
4581 l_progress := '40: calculate supplier price factors';
4582
4583 SELECT nvl(sum(decode(spf.pricing_basis,
4584 'PER_UNIT', spf.auction_currency_value,
4585 'PERCENTAGE', spf.auction_currency_value/100 * p_unit_price,
4586 (spf.auction_currency_value / decode(l_is_spo_transformed,
4587 'Y', nvl(p_quantity, 1),
4588 nvl(aip.quantity, 1)
4589 )
4590 )
4591 )
4592 )
4593 ,0)
4594 INTO l_bid_line_pf_unit_price
4595 FROM pon_bid_price_elements spf,
4596 pon_auction_item_prices_all aip
4597 WHERE spf.bid_number = p_prev_auc_active_bid_number
4598 AND spf.line_number = p_line_number
4599 AND spf.sequence_number <> -10
4600 AND spf.pf_type = 'SUPPLIER'
4601 AND aip.auction_header_id = spf.auction_header_id
4602 AND aip.line_number = spf.line_number;
4603
4604 -- total price in auction currency
4605 l_progress := '60: return total price in auction currency';
4606 l_total_price := l_bid_line_pf_unit_price + l_auc_pf_unit_price;
4607
4608 RETURN l_total_price;
4609
4610 EXCEPTION
4611
4612 WHEN OTHERS THEN
4613 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)THEN
4614 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
4615 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4616 fnd_log.string(log_level => fnd_log.level_unexpected
4617 ,module => g_pkg_name||'.'||l_api_name
4618 ,message => l_progress || ': ' || SQLERRM);
4619 fnd_log.string(log_level=>fnd_log.level_unexpected,
4620 module =>g_pkg_name ||'.'||l_api_name,
4621 message => 'Input parameter list: ' );
4622 fnd_log.string(log_level=>fnd_log.level_unexpected,
4623 module =>g_pkg_name ||'.'||l_api_name,
4624 message => 'Auction Header Id = ' || p_auction_header_id);
4625 fnd_log.string(log_level=>fnd_log.level_unexpected,
4626 module =>g_pkg_name ||'.'||l_api_name,
4627 message => 'Previous Round Active Bid Number = ' || p_prev_auc_active_bid_number);
4628 fnd_log.string(log_level=>fnd_log.level_unexpected,
4629 module =>g_pkg_name ||'.'||l_api_name,
4630 message => 'Line Number = ' || p_line_number);
4631
4632 END IF;
4633 END IF;
4634 return NULL;
4635 END apply_price_factors;
4636 END PON_BID_DEFAULTING_PKG;
[Home] [Help]