[Home] [Help]
PACKAGE BODY: APPS.PON_UNSOL_VALIDATIONS_PKG
Source
1 PACKAGE BODY pon_unsol_validations_pkg AS
2 --$Header: PON_UNSOL_VALIDATIONS_PKG.plb 120.3.12020000.2 2013/02/09 09:09:53 hvutukur ship $
3
4 g_exp_date TIMESTAMP;
5 g_exp_days_offset CONSTANT NUMBER := 7;
6
7
8 PROCEDURE VALIDATE_UNSOL_LINES( p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
9 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
10 p_interface_type IN pon_interface_errors.interface_type%TYPE,
11 p_userid IN pon_interface_errors.created_by%TYPE,
12 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
13 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
14 p_spreadsheet IN VARCHAR2,
15 p_blanket IN VARCHAR2,
16 p_global IN VARCHAR2,
17 p_trans_view IN VARCHAR2,
18 p_rebid IN VARCHAR2,
19 p_full_qty_reqd IN VARCHAR2,
20 p_header_disp_pf IN VARCHAR2,
21 p_price_driven IN VARCHAR2,
22 p_percent_decr IN VARCHAR2,
23 p_bid_decr_method IN pon_auction_headers_all.bid_decrement_method%TYPE,
24 p_min_bid_decr IN pon_auction_headers_all.min_bid_decrement%TYPE,
25 p_min_bid_change IN pon_bid_headers.min_bid_change%TYPE,
26 p_rate IN pon_bid_headers.rate%TYPE,
27 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
28 p_amt_precision IN fnd_currencies.precision%TYPE,
29 p_bid_curr_code IN pon_bid_headers.bid_currency_code%TYPE,
30 p_suffix IN VARCHAR2,
31 p_batch_id IN pon_interface_errors.batch_id%TYPE,
32 p_request_id IN pon_interface_errors.request_id%TYPE,
33 p_two_part_tech_surrogate_flag IN VARCHAR2) IS
34
35 l_price_mask VARCHAR2(80);
36 BEGIN
37 l_price_mask := PON_BID_VALIDATIONS_PKG.get_mask(p_price_precision);
38 g_exp_date := SYSDATE + g_exp_days_offset;
39 INSERT ALL
40
41 -- Price and quantity are required fields if they are editable
42 WHEN p_blanket = 'N' AND s_price_editable = 'Y' AND s_price IS null
43 AND s_qty_editable = 'Y' AND s_bid_quantity IS null
44 THEN
45 INTO pon_interface_errors
46 (INTERFACE_TYPE,
47 TABLE_NAME,
48 BATCH_ID,
49 INTERFACE_LINE_ID,
50 ERROR_MESSAGE_NAME,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 REQUEST_ID,
56 ENTITY_TYPE,
57 ENTITY_ATTR_NAME,
58 ERROR_VALUE_DATATYPE,
59 AUCTION_HEADER_ID,
60 BID_NUMBER,
61 LINE_NUMBER,
62 EXPIRATION_DATE,
63 TOKEN1_NAME,
64 TOKEN1_VALUE,
65 WORKSHEET_NAME,
66 WORKSHEET_SEQUENCE_NUMBER,
67 ENTITY_MESSAGE_CODE)
68 VALUES
69 (p_interface_type,
70 'PON_BID_ITEM_PRICES',
71 p_batch_id,
72 s_interface_line_id,
73 'PON_BID_PRICE_QTY_REQD' || p_suffix,
74 p_userid,
75 sysdate,
76 p_userid,
77 sysdate,
78 p_request_id,
79 'BID_ITEMS',
80 'Price',
81 'TXT',
82 p_auc_header_id,
83 p_bid_number,
84 s_line_number,
85 g_exp_date,
86 'LINENUMBER',
87 --s_document_disp_line_number,
88 s_clm_line_num_display,
89 s_worksheet_name,
90 s_worksheet_sequence_number,
91 s_entity_message_code)
92
93 -- Price is a required field if it is editable
94 WHEN s_price_editable = 'Y' AND s_price IS null
95 THEN
96 INTO pon_interface_errors
97 (INTERFACE_TYPE,
98 TABLE_NAME,
99 COLUMN_NAME,
100 BATCH_ID,
101 INTERFACE_LINE_ID,
102 ERROR_MESSAGE_NAME,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 REQUEST_ID,
108 ENTITY_TYPE,
109 ENTITY_ATTR_NAME,
110 ERROR_VALUE_DATATYPE,
111 AUCTION_HEADER_ID,
112 BID_NUMBER,
113 LINE_NUMBER,
114 EXPIRATION_DATE,
115 TOKEN1_NAME,
116 TOKEN1_VALUE,
117 WORKSHEET_NAME,
118 WORKSHEET_SEQUENCE_NUMBER,
119 ENTITY_MESSAGE_CODE)
120 VALUES
121 (p_interface_type,
122 'PON_BID_ITEM_PRICES',
123 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
124 p_batch_id,
125 s_interface_line_id,
126 'PON_BID_PRICE_REQUIRED' || p_suffix,
127 p_userid,
128 sysdate,
129 p_userid,
130 sysdate,
131 p_request_id,
132 'BID_ITEMS',
133 'Price',
134 'TXT',
135 p_auc_header_id,
136 p_bid_number,
137 s_line_number,
138 g_exp_date,
139 'LINENUMBER',
140 --s_document_disp_line_number,
141 s_clm_line_num_display,
142 s_worksheet_name,
143 s_worksheet_sequence_number,
144 s_entity_message_code)
145
146 -- Quantity is a required field if it is editable
147 WHEN p_blanket = 'N' AND s_qty_editable = 'Y' AND s_bid_quantity IS null THEN
148 INTO pon_interface_errors
149 (INTERFACE_TYPE,
150 TABLE_NAME,
151 COLUMN_NAME,
152 BATCH_ID,
153 INTERFACE_LINE_ID,
154 ERROR_MESSAGE_NAME,
155 CREATED_BY,
156 CREATION_DATE,
157 LAST_UPDATED_BY,
158 LAST_UPDATE_DATE,
159 REQUEST_ID,
160 ENTITY_TYPE,
161 ENTITY_ATTR_NAME,
162 ERROR_VALUE_DATATYPE,
163 AUCTION_HEADER_ID,
164 BID_NUMBER,
165 LINE_NUMBER,
166 EXPIRATION_DATE,
167 TOKEN1_NAME,
168 TOKEN1_VALUE,
169 WORKSHEET_NAME,
170 WORKSHEET_SEQUENCE_NUMBER,
171 ENTITY_MESSAGE_CODE)
172 VALUES
173 (p_interface_type,
174 'PON_BID_ITEM_PRICES',
175 fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
176 p_batch_id,
177 s_interface_line_id,
178 'PON_BID_QUANTITY_REQUIRED' || p_suffix,
179 p_userid,
180 sysdate,
181 p_userid,
182 sysdate,
183 p_request_id,
184 'BID_ITEMS',
185 'Quantity',
186 'TXT',
187 p_auc_header_id,
188 p_bid_number,
189 s_line_number,
190 g_exp_date,
191 'LINENUMBER',
192 --s_document_disp_line_number,
193 s_clm_line_num_display,
194 s_worksheet_name,
195 s_worksheet_sequence_number,
196 s_entity_message_code)
197
198 -- Bug 7460446 Bid cannot be zero or negative
199 -- Doesn't apply for blanket agreements
200 WHEN p_blanket = 'N' AND s_bid_quantity <= 0 THEN
201 INTO pon_interface_errors
202 (INTERFACE_TYPE,
203 COLUMN_NAME,
204 TABLE_NAME,
205 BATCH_ID,
206 INTERFACE_LINE_ID,
207 ERROR_MESSAGE_NAME,
208 CREATED_BY,
209 CREATION_DATE,
210 LAST_UPDATED_BY,
211 LAST_UPDATE_DATE,
212 REQUEST_ID,
213 ENTITY_TYPE,
214 ENTITY_ATTR_NAME,
215 ERROR_VALUE_NUMBER,
216 ERROR_VALUE_DATATYPE,
217 AUCTION_HEADER_ID,
218 BID_NUMBER,
219 LINE_NUMBER,
220 EXPIRATION_DATE,
221 TOKEN1_NAME,
222 TOKEN1_VALUE,
223 WORKSHEET_NAME,
224 WORKSHEET_SEQUENCE_NUMBER,
225 ENTITY_MESSAGE_CODE)
226 VALUES
227 (p_interface_type,
228 fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
229 'PON_BID_ITEM_PRICES',
230 p_batch_id,
231 s_interface_line_id,
232 'PON_AUC_BIDQTY_NEG' || p_suffix,
233 p_userid,
234 sysdate,
235 p_userid,
236 sysdate,
237 p_request_id,
238 'BID_ITEMS',
239 'Quantity',
240 s_bid_quantity,
241 'NUM',
242 p_auc_header_id,
243 p_bid_number,
244 s_line_number,
245 g_exp_date,
246 'LINENUMBER',
247 --s_document_disp_line_number,
248 s_clm_line_num_display,
249 s_worksheet_name,
250 s_worksheet_sequence_number,
251 s_entity_message_code)
252
253 -- The promised date cannot be earlier than the current date
254 -- Does not apply to blanket agreements
255 WHEN p_blanket = 'N' AND s_promised_date < s_current_date
256 THEN
257 INTO pon_interface_errors
258 (INTERFACE_TYPE,
259 COLUMN_NAME,
260 TABLE_NAME,
261 BATCH_ID,
262 INTERFACE_LINE_ID,
263 ERROR_MESSAGE_NAME,
264 CREATED_BY,
265 CREATION_DATE,
266 LAST_UPDATED_BY,
267 LAST_UPDATE_DATE,
268 REQUEST_ID,
269 ENTITY_TYPE,
270 ENTITY_ATTR_NAME,
271 ERROR_VALUE_DATE,
272 ERROR_VALUE_DATATYPE,
273 AUCTION_HEADER_ID,
274 BID_NUMBER,
275 LINE_NUMBER,
276 EXPIRATION_DATE,
277 TOKEN1_NAME,
278 TOKEN1_VALUE,
279 WORKSHEET_NAME,
280 WORKSHEET_SEQUENCE_NUMBER,
281 ENTITY_MESSAGE_CODE)
282 VALUES
283 (p_interface_type,
284 fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
285 'PON_BID_ITEM_PRICES',
286 p_batch_id,
287 s_interface_line_id,
288 'PON_AUC_PDATE_TOO_EARLY',
289 p_userid,
290 sysdate,
291 p_userid,
292 sysdate,
293 p_request_id,
294 'BID_ITEMS',
295 'PromisedDate',
296 s_promised_date,
297 'DAT',
298 p_auc_header_id,
299 p_bid_number,
300 s_line_number,
301 g_exp_date,
302 'LINENUMBER',
303 --s_document_disp_line_number,
304 s_clm_line_num_display,
305 s_worksheet_name,
306 s_worksheet_sequence_number,
307 s_entity_message_code)
308
309 --CLM Bug : 10212430 : Enter either promise date or POP dates
310 WHEN p_blanket = 'N'
311 AND (s_promised_date is not null and (s_clm_pop_start_date is not null OR s_clm_pop_end_date is not null))
312 THEN
313 INTO pon_interface_errors
314 (INTERFACE_TYPE,
315 COLUMN_NAME,
316 TABLE_NAME,
317 BATCH_ID,
318 INTERFACE_LINE_ID,
319 ERROR_MESSAGE_NAME,
320 CREATED_BY,
321 CREATION_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_DATE,
324 REQUEST_ID,
325 ENTITY_TYPE,
326 ENTITY_ATTR_NAME,
327 ERROR_VALUE_DATE,
328 ERROR_VALUE_DATATYPE,
329 AUCTION_HEADER_ID,
330 BID_NUMBER,
331 LINE_NUMBER,
332 EXPIRATION_DATE,
333 TOKEN1_NAME,
334 TOKEN1_VALUE,
335 WORKSHEET_NAME,
336 WORKSHEET_SEQUENCE_NUMBER,
337 ENTITY_MESSAGE_CODE)
338 VALUES
339 (p_interface_type,
340 fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
341 'PON_BID_ITEM_PRICES',
342 p_batch_id,
343 s_interface_line_id,
344 'PON_CLM_PROMISE_POPDT_LINE_VAL',
345 p_userid,
346 sysdate,
347 p_userid,
348 sysdate,
349 p_request_id,
350 'BID_ITEMS',
351 'PromisedDate',
352 s_promised_date,
353 'DAT',
354 p_auc_header_id,
355 p_bid_number,
356 s_line_number,
357 g_exp_date,
358 'LINENUM',
359 s_clm_line_num_display,
360 s_worksheet_name,
361 s_worksheet_sequence_number,
362 s_entity_message_code)
363
364 --CLM Bug : 10212430 : If Promise date is null, then both POP Start date is entered,then
365 -- pop end date is mandatory and vice versa.
366 WHEN p_blanket = 'N'
367 AND (s_promised_date is null and (s_clm_pop_start_date is not null AND s_clm_pop_end_date is null))
368 -- AND p_two_part_tech_surrogate_flag = 'N'
369 THEN
370 INTO pon_interface_errors
371 (INTERFACE_TYPE,
372 COLUMN_NAME,
373 TABLE_NAME,
374 BATCH_ID,
375 INTERFACE_LINE_ID,
376 ERROR_MESSAGE_NAME,
377 CREATED_BY,
378 CREATION_DATE,
379 LAST_UPDATED_BY,
380 LAST_UPDATE_DATE,
381 REQUEST_ID,
382 ENTITY_TYPE,
383 ENTITY_ATTR_NAME,
384 ERROR_VALUE_DATATYPE,
385 AUCTION_HEADER_ID,
386 BID_NUMBER,
387 LINE_NUMBER,
388 EXPIRATION_DATE,
389 TOKEN1_NAME,
390 TOKEN1_VALUE,
391 WORKSHEET_NAME,
392 WORKSHEET_SEQUENCE_NUMBER,
393 ENTITY_MESSAGE_CODE)
394 VALUES
395 (p_interface_type,
396 fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_END_DATE'),
397 'PON_BID_ITEM_PRICES',
398 p_batch_id,
399 s_interface_line_id,
400 'PON_CLM_PROMISE_POP_ENDDT_REQ',
401 p_userid,
402 sysdate,
403 p_userid,
404 sysdate,
405 p_request_id,
406 'BID_ITEMS',
407 'PromisePopEndDate',
408 'TXT',
409 p_auc_header_id,
410 p_bid_number,
411 s_line_number,
412 g_exp_date,
413 'LINENUM',
414 s_clm_line_num_display,
415 s_worksheet_name,
416 s_worksheet_sequence_number,
417 s_entity_message_code)
418
419 --CLM Bug : 10212430 : If Promise date is null, then both POP Start date is entered,then
420 -- pop end date is mandatory and vice versa.
421 WHEN p_blanket = 'N'
422 AND (s_promised_date is null and (s_clm_pop_start_date is null AND s_clm_pop_end_date is not null))
423 --AND p_two_part_tech_surrogate_flag = 'N'
424 THEN
425 INTO pon_interface_errors
426 (INTERFACE_TYPE,
427 COLUMN_NAME,
428 TABLE_NAME,
429 BATCH_ID,
430 INTERFACE_LINE_ID,
431 ERROR_MESSAGE_NAME,
432 CREATED_BY,
433 CREATION_DATE,
434 LAST_UPDATED_BY,
435 LAST_UPDATE_DATE,
436 REQUEST_ID,
437 ENTITY_TYPE,
438 ENTITY_ATTR_NAME,
439 ERROR_VALUE_DATATYPE,
440 AUCTION_HEADER_ID,
441 BID_NUMBER,
442 LINE_NUMBER,
443 EXPIRATION_DATE,
444 TOKEN1_NAME,
445 TOKEN1_VALUE,
446 WORKSHEET_NAME,
447 WORKSHEET_SEQUENCE_NUMBER,
448 ENTITY_MESSAGE_CODE)
449 VALUES
450 (p_interface_type,
451 fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
452 'PON_BID_ITEM_PRICES',
453 p_batch_id,
454 s_interface_line_id,
455 'PON_CLM_PROMISE_POP_STDT_REQ',
456 p_userid,
457 sysdate,
458 p_userid,
459 sysdate,
460 p_request_id,
461 'BID_ITEMS',
462 'PromisePopStartDate',
463 'TXT',
464 p_auc_header_id,
465 p_bid_number,
466 s_line_number,
467 g_exp_date,
468 'LINENUM',
469 s_clm_line_num_display,
470 s_worksheet_name,
471 s_worksheet_sequence_number,
472 s_entity_message_code)
473
474 --CLM Bug : 10212430 : POP Start date should be less than POP end date.
475 WHEN p_blanket = 'N'
476 AND ( s_promised_date is null AND
477 (s_clm_pop_start_date is not null AND s_clm_pop_end_date is not null AND (s_clm_pop_end_date <= s_clm_pop_start_date))
478 )
479 --AND p_two_part_tech_surrogate_flag = 'N'
480 THEN
481 INTO pon_interface_errors
482 (INTERFACE_TYPE,
483 COLUMN_NAME,
484 TABLE_NAME,
485 BATCH_ID,
486 INTERFACE_LINE_ID,
487 ERROR_MESSAGE_NAME,
488 CREATED_BY,
489 CREATION_DATE,
490 LAST_UPDATED_BY,
491 LAST_UPDATE_DATE,
492 REQUEST_ID,
493 ENTITY_TYPE,
494 ENTITY_ATTR_NAME,
495 ERROR_VALUE_DATE,
496 ERROR_VALUE_DATATYPE,
497 AUCTION_HEADER_ID,
498 BID_NUMBER,
499 LINE_NUMBER,
500 EXPIRATION_DATE,
501 TOKEN1_NAME,
502 TOKEN1_VALUE,
503 WORKSHEET_NAME,
504 WORKSHEET_SEQUENCE_NUMBER,
505 ENTITY_MESSAGE_CODE)
506 VALUES
507 (p_interface_type,
508 fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
509 'PON_BID_ITEM_PRICES',
510 p_batch_id,
511 s_interface_line_id,
512 'PON_CLM_PROMISE_POP_DATE_VALX',
513 p_userid,
514 sysdate,
515 p_userid,
516 sysdate,
517 p_request_id,
518 'BID_ITEMS',
519 'PromisePopStartDate',
520 s_clm_pop_start_date,
521 'DAT',
522 p_auc_header_id,
523 p_bid_number,
524 s_line_number,
525 g_exp_date,
526 'LINENUM',
527 s_clm_line_num_display,
528 s_worksheet_name,
529 s_worksheet_sequence_number,
530 s_entity_message_code)
531 --CLM Bug : 10212430 : POP Start date should be less than current date.
532 WHEN p_blanket = 'N'
533 AND ( s_promised_date is null AND
534 (s_clm_pop_start_date is not null AND s_clm_pop_start_date < s_current_date)
535 )
536 --AND p_two_part_tech_surrogate_flag = 'N'
537 THEN
538 INTO pon_interface_errors
539 (INTERFACE_TYPE,
540 COLUMN_NAME,
541 TABLE_NAME,
542 BATCH_ID,
543 INTERFACE_LINE_ID,
544 ERROR_MESSAGE_NAME,
545 CREATED_BY,
546 CREATION_DATE,
547 LAST_UPDATED_BY,
548 LAST_UPDATE_DATE,
549 REQUEST_ID,
550 ENTITY_TYPE,
551 ENTITY_ATTR_NAME,
552 ERROR_VALUE_DATE,
553 ERROR_VALUE_DATATYPE,
554 AUCTION_HEADER_ID,
555 BID_NUMBER,
556 LINE_NUMBER,
557 EXPIRATION_DATE,
558 TOKEN1_NAME,
559 TOKEN1_VALUE,
560 WORKSHEET_NAME,
561 WORKSHEET_SEQUENCE_NUMBER,
562 ENTITY_MESSAGE_CODE)
563 VALUES
564 (p_interface_type,
565 fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
566 'PON_BID_ITEM_PRICES',
567 p_batch_id,
568 s_interface_line_id,
569 'PON_BID_POP_START_BEF_TODAY',
570 p_userid,
571 sysdate,
572 p_userid,
573 sysdate,
574 p_request_id,
575 'BID_ITEMS',
576 'PromisePopStartDate',
577 s_clm_pop_start_date,
578 'DAT',
579 p_auc_header_id,
580 p_bid_number,
581 s_line_number,
582 g_exp_date,
583 'LINE_NUM',
584 s_clm_line_num_display,
585 s_worksheet_name,
586 s_worksheet_sequence_number,
587 s_entity_message_code)
588 --CLM Bug : 10212430 : POP End date should be less than current date.
589 WHEN p_blanket = 'N'
590 AND ( s_promised_date is null AND
591 (s_clm_pop_end_date is not null AND s_clm_pop_end_date < s_current_date)
592 )
593 --AND p_two_part_tech_surrogate_flag = 'N'
594 THEN
595 INTO pon_interface_errors
596 (INTERFACE_TYPE,
597 COLUMN_NAME,
598 TABLE_NAME,
599 BATCH_ID,
600 INTERFACE_LINE_ID,
601 ERROR_MESSAGE_NAME,
602 CREATED_BY,
603 CREATION_DATE,
604 LAST_UPDATED_BY,
605 LAST_UPDATE_DATE,
606 REQUEST_ID,
607 ENTITY_TYPE,
608 ENTITY_ATTR_NAME,
609 ERROR_VALUE_DATE,
610 ERROR_VALUE_DATATYPE,
611 AUCTION_HEADER_ID,
612 BID_NUMBER,
613 LINE_NUMBER,
614 EXPIRATION_DATE,
615 TOKEN1_NAME,
616 TOKEN1_VALUE,
617 WORKSHEET_NAME,
618 WORKSHEET_SEQUENCE_NUMBER,
619 ENTITY_MESSAGE_CODE)
620 VALUES
621 (p_interface_type,
622 fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_END_DATE'),
623 'PON_BID_ITEM_PRICES',
624 p_batch_id,
625 s_interface_line_id,
626 'PON_BID_POP_END_BEF_TODAY',
627 p_userid,
628 sysdate,
629 p_userid,
630 sysdate,
631 p_request_id,
632 'BID_ITEMS',
633 'PromisePopEndDate',
634 s_clm_pop_end_date,
635 'DAT',
636 p_auc_header_id,
637 p_bid_number,
638 s_line_number,
639 g_exp_date,
640 'LINE_NUM',
641 s_clm_line_num_display,
642 s_worksheet_name,
643 s_worksheet_sequence_number,
644 s_entity_message_code)
645
646 --Event Based Delivery Project.
647 WHEN p_blanket = 'N'
648 AND (s_clm_del_event IS NOT NULL AND
649 (s_clm_promise_period IS NULL OR s_clm_promise_period_uom IS NULL OR s_clm_promise_period_uom = '') )
650 THEN
651 INTO pon_interface_errors
652 (INTERFACE_TYPE,
653 COLUMN_NAME,
654 TABLE_NAME,
655 BATCH_ID,
656 INTERFACE_LINE_ID,
657 ERROR_MESSAGE_NAME,
658 CREATED_BY,
659 CREATION_DATE,
660 LAST_UPDATED_BY,
661 LAST_UPDATE_DATE,
662 REQUEST_ID,
663 ENTITY_TYPE,
664 ENTITY_ATTR_NAME,
665 ERROR_VALUE_NUMBER,
666 ERROR_VALUE_DATATYPE,
667 AUCTION_HEADER_ID,
668 BID_NUMBER,
669 LINE_NUMBER,
670 EXPIRATION_DATE,
671 TOKEN1_NAME,
672 TOKEN1_VALUE,
673 WORKSHEET_NAME,
674 WORKSHEET_SEQUENCE_NUMBER,
675 ENTITY_MESSAGE_CODE)
676 VALUES
677 (p_interface_type,
678 fnd_message.get_string('PON', 'CLM_PROMISE_PERIOD'),
679 'PON_BID_ITEM_PRICES',
680 p_batch_id,
681 s_interface_line_id,
682 'PON_BID_PERIOD_REQUIRED',
683 p_userid,
684 sysdate,
685 p_userid,
686 sysdate,
687 p_request_id,
688 'BID_ITEMS',
689 'PromisePeriod',
690 s_clm_promise_period,
691 'NUM',
692 p_auc_header_id,
693 p_bid_number,
694 s_line_number,
695 g_exp_date,
696 'LINE_NUM',
697 s_clm_line_num_display,
698 s_worksheet_name,
699 s_worksheet_sequence_number,
700 s_entity_message_code)
701
702 WHEN p_blanket = 'N'
703 AND (s_clm_del_event IS NOT NULL AND
704 s_clm_promise_period IS NOT NULL AND
705 s_clm_promise_period <= 0 )
706 THEN
707 INTO pon_interface_errors
708 (INTERFACE_TYPE,
709 COLUMN_NAME,
710 TABLE_NAME,
711 BATCH_ID,
712 INTERFACE_LINE_ID,
713 ERROR_MESSAGE_NAME,
714 CREATED_BY,
715 CREATION_DATE,
716 LAST_UPDATED_BY,
717 LAST_UPDATE_DATE,
718 REQUEST_ID,
719 ENTITY_TYPE,
720 ENTITY_ATTR_NAME,
721 ERROR_VALUE_NUMBER,
722 ERROR_VALUE_DATATYPE,
723 AUCTION_HEADER_ID,
724 BID_NUMBER,
725 LINE_NUMBER,
726 EXPIRATION_DATE,
727 TOKEN1_NAME,
728 TOKEN1_VALUE,
729 WORKSHEET_NAME,
730 WORKSHEET_SEQUENCE_NUMBER,
731 ENTITY_MESSAGE_CODE)
732 VALUES
733 (p_interface_type,
734 fnd_message.get_string('PON', 'CLM_PROMISE_PERIOD'),
735 'PON_BID_ITEM_PRICES',
736 p_batch_id,
737 s_interface_line_id,
738 'PON_BID_INV_PERIOD',
739 p_userid,
740 sysdate,
741 p_userid,
742 sysdate,
743 p_request_id,
744 'BID_ITEMS',
745 'PromisePeriod',
746 s_clm_promise_period,
747 'NUM',
748 p_auc_header_id,
749 p_bid_number,
750 s_line_number,
751 g_exp_date,
752 'LINE_NUM',
753 s_clm_line_num_display,
754 s_worksheet_name,
755 s_worksheet_sequence_number,
756 s_entity_message_code)
757
758 WHEN p_blanket = 'N'
759 AND (s_clm_del_event IS NOT NULL AND
760 s_clm_pop_duration IS NOT NULL AND
761 s_clm_pop_duration <= 0 )
762 THEN
763 INTO pon_interface_errors
764 (INTERFACE_TYPE,
765 COLUMN_NAME,
766 TABLE_NAME,
767 BATCH_ID,
768 INTERFACE_LINE_ID,
769 ERROR_MESSAGE_NAME,
770 CREATED_BY,
771 CREATION_DATE,
772 LAST_UPDATED_BY,
773 LAST_UPDATE_DATE,
774 REQUEST_ID,
775 ENTITY_TYPE,
776 ENTITY_ATTR_NAME,
777 ERROR_VALUE_NUMBER,
778 ERROR_VALUE_DATATYPE,
779 AUCTION_HEADER_ID,
780 BID_NUMBER,
781 LINE_NUMBER,
782 EXPIRATION_DATE,
783 TOKEN1_NAME,
784 TOKEN1_VALUE,
785 WORKSHEET_NAME,
786 WORKSHEET_SEQUENCE_NUMBER,
787 ENTITY_MESSAGE_CODE)
788 VALUES
789 (p_interface_type,
790 fnd_message.get_string('PON', 'CLM_POP_DURATION'),
791 'PON_BID_ITEM_PRICES',
792 p_batch_id,
793 s_interface_line_id,
794 'PON_BID_INV_POP_DUR',
795 p_userid,
796 sysdate,
797 p_userid,
798 sysdate,
799 p_request_id,
800 'BID_ITEMS',
801 'PromisePOPDuration',
802 s_clm_pop_duration,
803 'NUM',
804 p_auc_header_id,
805 p_bid_number,
806 s_line_number,
807 g_exp_date,
808 'LINE_NUM',
809 s_clm_line_num_display,
810 s_worksheet_name,
811 s_worksheet_sequence_number,
812 s_entity_message_code)
813
814 -- po_bid_min_rel_amount should not be negative
815 WHEN p_blanket = 'Y' AND s_po_bid_min_rel_amount < 0 THEN
816 INTO pon_interface_errors
817 (INTERFACE_TYPE,
818 COLUMN_NAME,
819 TABLE_NAME,
820 BATCH_ID,
821 INTERFACE_LINE_ID,
822 ERROR_MESSAGE_NAME,
823 CREATED_BY,
824 CREATION_DATE,
825 LAST_UPDATED_BY,
826 LAST_UPDATE_DATE,
827 REQUEST_ID,
828 ENTITY_TYPE,
829 ENTITY_ATTR_NAME,
830 ERROR_VALUE_NUMBER,
831 ERROR_VALUE_DATATYPE,
832 AUCTION_HEADER_ID,
833 BID_NUMBER,
834 LINE_NUMBER,
835 EXPIRATION_DATE,
836 TOKEN1_NAME,
837 TOKEN1_VALUE,
838 WORKSHEET_NAME,
839 WORKSHEET_SEQUENCE_NUMBER,
840 ENTITY_MESSAGE_CODE)
841 VALUES
842 (p_interface_type,
843 fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
844 'PON_BID_ITEM_PRICES',
845 p_batch_id,
846 s_interface_line_id,
847 'PON_AUC_MINREL_POS_ZERO' || p_suffix,
848 p_userid,
849 sysdate,
850 p_userid,
851 sysdate,
852 p_request_id,
853 'BID_ITEMS',
854 'PoBidMinRelAmount',
855 s_po_bid_min_rel_amount,
856 'NUM',
857 p_auc_header_id,
858 p_bid_number,
859 s_line_number,
860 g_exp_date,
861 'LINENUMBER',
862 s_clm_line_num_display,
863 s_worksheet_name,
864 s_worksheet_sequence_number,
865 s_entity_message_code)
866
867 -- po_bid_min_rel_amount should not exceed currency precision
868 WHEN p_blanket = 'Y' AND
869 PON_BID_VALIDATIONS_PKG.validate_currency_precision(s_po_bid_min_rel_amount, p_amt_precision) = 'F' THEN
870 INTO pon_interface_errors
871 (INTERFACE_TYPE,
872 COLUMN_NAME,
873 TABLE_NAME,
874 BATCH_ID,
875 INTERFACE_LINE_ID,
876 ERROR_MESSAGE_NAME,
877 CREATED_BY,
878 CREATION_DATE,
879 LAST_UPDATED_BY,
880 LAST_UPDATE_DATE,
881 REQUEST_ID,
882 ENTITY_TYPE,
883 ENTITY_ATTR_NAME,
884 ERROR_VALUE_NUMBER,
885 ERROR_VALUE_DATATYPE,
886 AUCTION_HEADER_ID,
887 BID_NUMBER,
888 LINE_NUMBER,
889 EXPIRATION_DATE,
890 TOKEN1_NAME,
891 TOKEN1_VALUE,
892 WORKSHEET_NAME,
893 WORKSHEET_SEQUENCE_NUMBER,
894 ENTITY_MESSAGE_CODE)
895 VALUES
896 (p_interface_type,
897 fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
898 'PON_BID_ITEM_PRICES',
899 p_batch_id,
900 s_interface_line_id,
901 'PON_AUC_MINREL_MIN_PREC' || p_suffix,
902 p_userid,
903 sysdate,
904 p_userid,
905 sysdate,
906 p_request_id,
907 'BID_ITEMS',
908 'PoBidMinRelAmount',
909 s_po_bid_min_rel_amount,
910 'NUM',
911 p_auc_header_id,
912 p_bid_number,
913 s_line_number,
914 g_exp_date,
915 'LINENUMBER',
916 s_clm_line_num_display,
917 s_worksheet_name,
918 s_worksheet_sequence_number,
919 s_entity_message_code)
920
921 -- bid_currency_unit_price should be positive
922 -- bid_currency_unit_price only validated when header disp_pf_flag is Y
923 -- Suppress error if spreadsheet case and no price factors
924 WHEN p_header_disp_pf = 'Y' AND s_bid_currency_unit_price <= 0
925 AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
926 --AND p_two_part_tech_surrogate_flag = 'N'
927
928 THEN
929 INTO pon_interface_errors
930 (INTERFACE_TYPE,
931 COLUMN_NAME,
932 TABLE_NAME,
933 BATCH_ID,
934 INTERFACE_LINE_ID,
935 ERROR_MESSAGE_NAME,
936 CREATED_BY,
937 CREATION_DATE,
938 LAST_UPDATED_BY,
939 LAST_UPDATE_DATE,
940 REQUEST_ID,
941 ENTITY_TYPE,
942 ENTITY_ATTR_NAME,
943 ERROR_VALUE_NUMBER,
944 ERROR_VALUE_DATATYPE,
945 AUCTION_HEADER_ID,
946 BID_NUMBER,
947 LINE_NUMBER,
948 EXPIRATION_DATE,
949 TOKEN1_NAME,
950 TOKEN1_VALUE,
951 WORKSHEET_NAME,
952 WORKSHEET_SEQUENCE_NUMBER,
953 ENTITY_MESSAGE_CODE)
954 VALUES
955 (p_interface_type,
956 fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
957 'PON_BID_ITEM_PRICES',
958 p_batch_id,
959 s_interface_line_id,
960 'PON_LINEPRICE_MUST_BE_POS',
961 p_userid,
962 sysdate,
963 p_userid,
964 sysdate,
965 p_request_id,
966 'BID_ITEMS',
967 'BidCurrencyUnitPrice',
968 s_bid_currency_unit_price,
969 'NUM',
970 p_auc_header_id,
971 p_bid_number,
972 s_line_number,
973 g_exp_date,
974 'LINENUMBER',
975 s_clm_line_num_display,
976 s_worksheet_name,
977 s_worksheet_sequence_number,
978 s_entity_message_code)
979
980 -- bid_currency_unit_price only validated when header disp_pf_flag is Y
981 -- bid_currency_unit_price precision should not exceed price precision
982 -- Suppress error if spreadsheet case and no price factors
983 WHEN p_header_disp_pf = 'Y' AND
984 PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_unit_price, p_price_precision) = 'F'
985 AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
986 --AND p_two_part_tech_surrogate_flag = 'N'
987 THEN
988 INTO pon_interface_errors
989 (INTERFACE_TYPE,
990 COLUMN_NAME,
991 TABLE_NAME,
992 BATCH_ID,
993 INTERFACE_LINE_ID,
994 ERROR_MESSAGE_NAME,
995 CREATED_BY,
996 CREATION_DATE,
997 LAST_UPDATED_BY,
998 LAST_UPDATE_DATE,
999 REQUEST_ID,
1000 ENTITY_TYPE,
1001 ENTITY_ATTR_NAME,
1002 ERROR_VALUE_NUMBER,
1003 ERROR_VALUE_DATATYPE,
1004 AUCTION_HEADER_ID,
1005 BID_NUMBER,
1006 LINE_NUMBER,
1007 EXPIRATION_DATE,
1008 TOKEN1_NAME,
1009 TOKEN1_VALUE,
1010 WORKSHEET_NAME,
1011 WORKSHEET_SEQUENCE_NUMBER,
1012 ENTITY_MESSAGE_CODE)
1013 VALUES
1014 (p_interface_type,
1015 fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
1016 'PON_BID_ITEM_PRICES',
1017 p_batch_id,
1018 s_interface_line_id,
1019 'PON_LINEPRICE_MIN_PREC' || p_suffix,
1020 p_userid,
1021 sysdate,
1022 p_userid,
1023 sysdate,
1024 p_request_id,
1025 'BID_ITEMS',
1026 'BidCurrencyUnitPrice',
1027 s_bid_currency_unit_price,
1028 'NUM',
1029 p_auc_header_id,
1030 p_bid_number,
1031 s_line_number,
1032 g_exp_date,
1033 'LINENUMBER',
1034 s_clm_line_num_display,
1035 s_worksheet_name,
1036 s_worksheet_sequence_number,
1037 s_entity_message_code)
1038
1039 SELECT
1040 sysdate s_current_date,
1041 Decode(Nvl(bl.clm_info_flag,'N'),'Y','N',
1042 Decode(Nvl(bl.clm_cost_constraint,'X'),'NSP','N','NC','N','Y')) s_price_editable,
1043 Decode(Nvl(bl.clm_info_flag,'N'),'Y','N',
1044 decode(bl.order_type_lookup_code, 'AMOUNT', 'N',
1045 'RATE', 'N', 'FIXED PRICE', 'N', 'Y')) s_qty_editable,
1046 bl.quantity s_bid_quantity,
1047 bl.promised_date s_promised_date,
1048 bl.PROMISE_POP_START_DATE s_clm_pop_start_date,
1049 bl.PROMISE_POP_END_DATE s_clm_pop_end_date,
1050 bl.bid_currency_unit_price s_bid_currency_unit_price,
1051 bl.price s_price,
1052 bl.po_bid_min_rel_amount s_po_bid_min_rel_amount,
1053 bl.display_price_factors_flag s_display_price_factors_flag,
1054 bl.line_number s_line_number,
1055 decode(p_spreadsheet, g_xml_upload_mode, bl.interface_line_id,
1056 g_txt_upload_mode, bl.interface_line_id,
1057 to_number(null)) s_interface_line_id,
1058 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
1059 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
1060 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code,
1061 bl.line_num_display s_clm_line_num_display,
1062 -- Event Based Delivery Project
1063 bl.CLM_DELIVERY_EVENT_CODE s_clm_del_event,
1064 bl.CLM_PROMISE_PERIOD s_clm_promise_period,
1065 bl.CLM_PROMISE_PERIOD_UOM s_clm_promise_period_uom,
1066 bl.CLM_PROMISE_POP_DURATION s_clm_pop_duration
1067 FROM pon_bid_item_prices bl
1068 , pon_bid_headers pbh
1069 WHERE pbh.auction_header_id = p_auc_header_id
1070 AND pbh.bid_number = p_bid_number
1071 AND bl.bid_number = pbh.bid_number
1072 AND bl.auction_line_number = -1
1073 AND bl.is_changed_line_flag = 'Y'
1074 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
1075
1076
1077
1078 INSERT ALL
1079
1080 -- bid_currency_price should be positive
1081 WHEN (s_bid_currency_price <= 0 AND s_clm_cost_constraint NOT IN ('NSP', 'NC'))
1082 THEN
1083 INTO pon_interface_errors
1084 (INTERFACE_TYPE,
1085 COLUMN_NAME,
1086 TABLE_NAME,
1087 BATCH_ID,
1088 INTERFACE_LINE_ID,
1089 ERROR_MESSAGE_NAME,
1090 CREATED_BY,
1091 CREATION_DATE,
1092 LAST_UPDATED_BY,
1093 LAST_UPDATE_DATE,
1094 REQUEST_ID,
1095 ENTITY_TYPE,
1096 ENTITY_ATTR_NAME,
1097 ERROR_VALUE_NUMBER,
1098 ERROR_VALUE_DATATYPE,
1099 AUCTION_HEADER_ID,
1100 BID_NUMBER,
1101 LINE_NUMBER,
1102 EXPIRATION_DATE,
1103 TOKEN1_NAME,
1104 TOKEN1_VALUE,
1105 WORKSHEET_NAME,
1106 WORKSHEET_SEQUENCE_NUMBER,
1107 ENTITY_MESSAGE_CODE)
1108 VALUES
1109 (p_interface_type,
1110 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1111 'PON_BID_ITEM_PRICES',
1112 p_batch_id,
1113 s_interface_line_id,
1114 decode(p_trans_view, 'N',
1115 'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix,
1116 decode(s_display_price_factors_flag, 'Y',
1117 'PON_LINE_BIDPRICE_INVALID_2' || p_suffix,
1118 'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix)),
1119 p_userid,
1120 sysdate,
1121 p_userid,
1122 sysdate,
1123 p_request_id,
1124 'BID_ITEMS',
1125 'BidCurrencyPrice',
1126 s_bid_currency_price,
1127 'NUM',
1128 p_auc_header_id,
1129 p_bid_number,
1130 s_line_number,
1131 g_exp_date,
1132 'LINENUMBER',
1133 s_clm_line_num_display,
1134 s_worksheet_name,
1135 s_worksheet_sequence_number,
1136 s_entity_message_code)
1137
1138 -- bid_currency_price precision should not exceed price precision
1139 WHEN
1140 ( (s_order_type_lookup_code = 'QUANTITY') AND
1141 PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_price, p_price_precision) = 'F')
1142 THEN
1143 INTO pon_interface_errors
1144 (INTERFACE_TYPE,
1145 COLUMN_NAME,
1146 TABLE_NAME,
1147 BATCH_ID,
1148 INTERFACE_LINE_ID,
1149 ERROR_MESSAGE_NAME,
1150 CREATED_BY,
1151 CREATION_DATE,
1152 LAST_UPDATED_BY,
1153 LAST_UPDATE_DATE,
1154 REQUEST_ID,
1155 ENTITY_TYPE,
1156 ENTITY_ATTR_NAME,
1157 ERROR_VALUE_NUMBER,
1158 ERROR_VALUE_DATATYPE,
1159 AUCTION_HEADER_ID,
1160 BID_NUMBER,
1161 LINE_NUMBER,
1162 EXPIRATION_DATE,
1163 TOKEN1_NAME,
1164 TOKEN1_VALUE,
1165 WORKSHEET_NAME,
1166 WORKSHEET_SEQUENCE_NUMBER,
1167 ENTITY_MESSAGE_CODE)
1168 VALUES
1169 (p_interface_type,
1170 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1171 'PON_BID_ITEM_PRICES',
1172 p_batch_id,
1173 s_interface_line_id,
1174 'PON_AUCTS_BIDPRICE_MIN_PREC' || p_suffix,
1175 p_userid,
1176 sysdate,
1177 p_userid,
1178 sysdate,
1179 p_request_id,
1180 'BID_ITEMS',
1181 'BidCurrencyPrice',
1182 s_bid_currency_price,
1183 'NUM',
1184 p_auc_header_id,
1185 p_bid_number,
1186 s_line_number,
1187 g_exp_date,
1188 'LINENUMBER',
1189 --s_document_disp_line_number,
1190 s_clm_line_num_display,
1191 s_worksheet_name,
1192 s_worksheet_sequence_number,
1193 s_entity_message_code)
1194
1195 -- bid_currency_price precision should not exceed price precision
1196 WHEN
1197 ( s_order_type_lookup_code <> 'QUANTITY' AND
1198 PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_price, p_amt_precision) = 'F')
1199 THEN
1200 INTO pon_interface_errors
1201 (INTERFACE_TYPE,
1202 COLUMN_NAME,
1203 TABLE_NAME,
1204 BATCH_ID,
1205 INTERFACE_LINE_ID,
1206 ERROR_MESSAGE_NAME,
1207 CREATED_BY,
1208 CREATION_DATE,
1209 LAST_UPDATED_BY,
1210 LAST_UPDATE_DATE,
1211 REQUEST_ID,
1212 ENTITY_TYPE,
1213 ENTITY_ATTR_NAME,
1214 ERROR_VALUE_NUMBER,
1215 ERROR_VALUE_DATATYPE,
1216 AUCTION_HEADER_ID,
1217 BID_NUMBER,
1218 LINE_NUMBER,
1219 EXPIRATION_DATE,
1220 TOKEN1_NAME,
1221 TOKEN1_VALUE,
1222 WORKSHEET_NAME,
1223 WORKSHEET_SEQUENCE_NUMBER,
1224 ENTITY_MESSAGE_CODE)
1225 VALUES
1226 (p_interface_type,
1227 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1228 'PON_BID_ITEM_PRICES',
1229 p_batch_id,
1230 s_interface_line_id,
1231 'PON_AUCTS_BIDPRICE_MIN_PREC' || p_suffix,
1232 p_userid,
1233 sysdate,
1234 p_userid,
1235 sysdate,
1236 p_request_id,
1237 'BID_ITEMS',
1238 'BidCurrencyPrice',
1239 s_bid_currency_price,
1240 'NUM',
1241 p_auc_header_id,
1242 p_bid_number,
1243 s_line_number,
1244 g_exp_date,
1245 'LINENUMBER',
1246 --s_document_disp_line_number,
1247 s_clm_line_num_display,
1248 s_worksheet_name,
1249 s_worksheet_sequence_number,
1250 s_entity_message_code)
1251
1252
1253
1254 -- When rebidding, the new price must be <= old_price, if changed
1255 -- Does not apply to LOT_LINEs
1256 -- Will not apply if price is disabled for this line, or negotiation
1257 -- is not price driver
1258 WHEN p_rebid = 'Y' AND p_price_driven = 'Y'
1259 AND s_price <> s_old_price
1260 AND s_price > s_old_price
1261 -- added by Allen Yang for Surrogate Bid 2008/09/03
1262 ---------------------------------------------------
1263 AND p_two_part_tech_surrogate_flag = 'N'
1264 ---------------------------------------------------
1265 THEN
1266 INTO pon_interface_errors
1267 (INTERFACE_TYPE,
1268 COLUMN_NAME,
1269 TABLE_NAME,
1270 BATCH_ID,
1271 INTERFACE_LINE_ID,
1272 ERROR_MESSAGE_NAME,
1273 CREATED_BY,
1274 CREATION_DATE,
1275 LAST_UPDATED_BY,
1276 LAST_UPDATE_DATE,
1277 REQUEST_ID,
1278 ENTITY_TYPE,
1279 ENTITY_ATTR_NAME,
1280 ERROR_VALUE_NUMBER,
1281 ERROR_VALUE_DATATYPE,
1282 AUCTION_HEADER_ID,
1283 BID_NUMBER,
1284 LINE_NUMBER,
1285 EXPIRATION_DATE,
1286 TOKEN1_NAME,
1287 TOKEN1_VALUE,
1288 WORKSHEET_NAME,
1289 WORKSHEET_SEQUENCE_NUMBER,
1290 ENTITY_MESSAGE_CODE)
1291 VALUES
1292 (p_interface_type,
1293 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1294 'PON_BID_ITEM_PRICES',
1295 p_batch_id,
1296 s_interface_line_id,
1297 decode(p_bid_decr_method,'BEST_PRICE','PON_BESTBID_PRICE_LOWER','PON_BID_PRICE_LOWER') || p_suffix,
1298 p_userid,
1299 sysdate,
1300 p_userid,
1301 sysdate,
1302 p_request_id,
1303 'BID_ITEMS',
1304 'Price',
1305 s_price,
1306 'NUM',
1307 p_auc_header_id,
1308 p_bid_number,
1309 s_line_number,
1310 g_exp_date,
1311 'LINENUMBER',
1312 --s_document_disp_line_number,
1313 s_clm_line_num_display,
1314 s_worksheet_name,
1315 s_worksheet_sequence_number,
1316 s_entity_message_code)
1317
1318
1319
1320 -- There was a previous bid on this line which was deleted
1321 -- Not allowed to unbid when rebidding.
1322 /*WHEN p_rebid = 'Y' AND s_is_changed_line_flag = 'Y'
1323 AND s_has_bid_flag = 'N' THEN
1324 INTO pon_interface_errors
1325 (INTERFACE_TYPE,
1326 TABLE_NAME,
1327 BATCH_ID,
1328 INTERFACE_LINE_ID,
1329 ERROR_MESSAGE_NAME,
1330 CREATED_BY,
1331 CREATION_DATE,
1332 LAST_UPDATED_BY,
1333 LAST_UPDATE_DATE,
1334 REQUEST_ID,
1335 AUCTION_HEADER_ID,
1336 BID_NUMBER,
1337 LINE_NUMBER,
1338 EXPIRATION_DATE,
1339 TOKEN1_NAME,
1340 TOKEN1_VALUE,
1341 WORKSHEET_NAME,
1342 WORKSHEET_SEQUENCE_NUMBER,
1343 ENTITY_MESSAGE_CODE)
1344 VALUES
1345 (p_interface_type,
1346 'PON_BID_ITEM_PRICES',
1347 p_batch_id,
1348 s_interface_line_id,
1349 'PON_CANNOT_UNBID' || p_suffix,
1350 p_userid,
1351 sysdate,
1352 p_userid,
1353 sysdate,
1354 p_request_id,
1355 p_auc_header_id,
1356 p_bid_number,
1357 s_line_number,
1358 g_exp_date,
1359 'LINENUMBER',
1360 --s_document_disp_line_number,
1361 s_clm_line_num_display,
1362 s_worksheet_name,
1363 s_worksheet_sequence_number,
1364 s_entity_message_code)*/
1365
1366 SELECT
1367 bl.bid_currency_price s_bid_currency_price,
1368 bl.bid_currency_trans_price s_bid_currency_trans_price,
1369 bl.bid_currency_limit_price s_bid_currency_limit_price,
1370 bl.price s_price,
1371 bl.proxy_bid_limit_price s_proxy_bid_limit_price,
1372 bl.price_break_type s_price_break_type,
1373 bl.display_price_factors_flag s_display_price_factors_flag,
1374 bl.old_price s_old_price,
1375 bl.has_bid_flag s_has_bid_flag,
1376 bl.is_changed_line_flag s_is_changed_line_flag,
1377 bl.line_number s_line_number,
1378 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
1379 g_xml_upload_mode, bl.interface_line_id,
1380 to_number(null)) s_interface_line_id,
1381 bl.bid_start_price s_enforced_bid_start_price,
1382 bh.surrog_bid_flag s_surrog_bid_flag,
1383 sysdate s_current_date,
1384 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
1385 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
1386 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code,
1387 bh.rate s_rate,
1388 bh.number_price_decimals s_precision,
1389 bl.line_num_display s_clm_line_num_display,
1390 nvl(bl.clm_cost_constraint,'X') s_clm_cost_constraint, -- CLM QA Bug : 9835426
1391 bl.order_type_lookup_code s_order_type_lookup_code --For clm bug : 10136754
1392 FROM pon_bid_headers bh,
1393 pon_bid_item_prices bl
1394 WHERE bh.auction_header_id = p_auc_header_id
1395 AND bh.bid_number = p_bid_number
1396 AND bl.bid_number = bh.bid_number
1397 AND bl.auction_line_number = -1
1398 AND bl.is_changed_line_flag = 'Y'
1399 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
1400
1401
1402 END VALIDATE_UNSOL_LINES;
1403
1404 END PON_UNSOL_VALIDATIONS_PKG;