[Home] [Help]
PACKAGE BODY: APPS.PON_RESPONSE_IMPORT_PKG
Source
1 PACKAGE BODY PON_RESPONSE_IMPORT_PKG AS
2 --$Header: PONRIMPB.pls 120.37.12010000.2 2008/10/30 10:05:29 jianliu ship $
3
4 g_exp_date TIMESTAMP;
5 g_exp_days_offset CONSTANT NUMBER := 7;
6
7 g_null_int CONSTANT NUMBER := -9999;
8 g_error_int CONSTANT NUMBER := -9998;
9 g_skip_int CONSTANT NUMBER := -9997;
10 g_closed_int CONSTANT NUMBER := -9996;
11
12 g_pb_required CONSTANT NUMBER := 1;
13 g_pb_optional CONSTANT NUMBER := 2;
14 g_pb_new CONSTANT NUMBER := 3;
15 g_pb_delete CONSTANT NUMBER := 4;
16 g_pb_optional_updated CONSTANT NUMBER := 5;
17
18 g_pt_indicator_pricebreak CONSTANT VARCHAR2(30) := 'PRICE_BREAKS';
19 g_pt_indicator_quantitybased CONSTANT VARCHAR2(30) := 'QUANTITY_BASED';
20 g_shipment_type_pricebreak CONSTANT VARCHAR2(30) := 'PRICE BREAK';
21 g_shipment_type_quantitybased CONSTANT VARCHAR2(30) := 'QUANTITY BASED';
22
23 -- constants for requirements/attributes XML upload
24
25 g_xml_decimal_separator_char CONSTANT VARCHAR2(1) := '.';
26
27 g_xml_number_mask CONSTANT VARCHAR2(255) := '9999999999999999999999999999999999999999999999D9999999999999999';
28
29 g_xml_char_no_prec_mask CONSTANT VARCHAR2(255) := 'FM999999999999999999999999999999999999999999999999999999999999999';
30 g_xml_char_prec_mask CONSTANT VARCHAR2(255) := 'FM9999999999999999999999999999999999999999999999D9999999999999999';
31
32 g_xml_date_mask CONSTANT VARCHAR2(25) := 'yyyy-mm-dd';
33 g_xml_date_time_mask CONSTANT VARCHAR2(25) := 'yyyy-mm-dd hh24:mi:ss';
34
35 g_pon_date_mask CONSTANT VARCHAR2(25) := 'dd-mm-yyyy';
36 g_pon_date_time_mask CONSTANT VARCHAR2(25) := 'dd-mm-yyyy hh24:mi:ss';
37
38 g_attr_need_by_date_seq CONSTANT NUMBER := -10;
39
40 -- These will be used for debugging the code
41 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
42 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_RESPONSE_IMPORT_PKG';
43 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
44
45
46 PROCEDURE validate_xml_req_values
47 (
48 p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
49 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
50 p_user_id IN pon_interface_errors.created_by%TYPE,
51 p_suffix IN VARCHAR2,
52 p_batch_id IN pon_interface_errors.batch_id%TYPE,
53 p_request_id IN pon_interface_errors.request_id%TYPE);
54
55
56 PROCEDURE validate_xml_attr_values
57 (
58 p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
59 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
60 p_user_id IN pon_interface_errors.created_by%TYPE,
61 p_suffix IN VARCHAR2,
62 p_batch_id IN pon_interface_errors.batch_id%TYPE,
63 p_request_id IN pon_interface_errors.request_id%TYPE);
64
65
66
67 FUNCTION get_message_1_token
68 (
69 p_message IN VARCHAR2,
70 p_token1_name IN VARCHAR2,
71 p_token1_value IN VARCHAR2
72 ) RETURN VARCHAR2 IS
73 BEGIN
74
75 fnd_message.clear;
76 fnd_message.set_name('PON', p_message);
77 fnd_message.set_token(p_token1_name, p_token1_value);
78
79 RETURN fnd_message.get;
80
81 END get_message_1_token;
82
83 PROCEDURE validate_close_bidding_date
84 (
85 p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
86 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
87 p_request_id IN pon_bid_headers.request_id%TYPE,
88 p_userid IN pon_interface_errors.created_by%TYPE,
89 p_suffix IN VARCHAR2
90 ) IS
91 l_is_paused VARCHAR2(1);
92 l_paused_date TIMESTAMP;
93 BEGIN
94
95 -- PRECONDITIONS:
96 -- line_number correctly set for lines
97
98 SELECT nvl(ah.is_paused, 'N'), ah.last_pause_date
99 INTO l_is_paused, l_paused_date
100 FROM pon_auction_headers_all ah
101 WHERE ah.auction_header_id = p_auc_header_id;
102
103 -- Flag all lines with expired close bidding dates
104 UPDATE pon_bid_item_prices_interface bli
105 SET bli.line_number = g_closed_int
106 WHERE bli.batch_id = p_batch_id
107 AND bli.line_number <> g_error_int
108 AND bli.line_number <> g_skip_int
109 AND sysdate >
110 (SELECT decode(l_is_paused, 'N', al.close_bidding_date,
111 al.close_bidding_date + (sysdate - l_paused_date))
112 FROM pon_auction_item_prices_all al
113 WHERE al.auction_header_id = bli.auction_header_id
114 AND al.line_number = bli.line_number);
115
116 -- Report errors for all closed lines
117 INSERT INTO pon_interface_errors
118 (INTERFACE_TYPE,
119 COLUMN_NAME,
120 BATCH_ID,
121 INTERFACE_LINE_ID,
122 ERROR_MESSAGE_NAME,
123 CREATED_BY,
124 CREATION_DATE,
125 LAST_UPDATED_BY,
126 LAST_UPDATE_DATE,
127 REQUEST_ID,
128 ERROR_VALUE,
129 ERROR_VALUE_DATATYPE,
130 AUCTION_HEADER_id,
131 BID_NUMBER,
132 LINE_NUMBER,
133 EXPIRATION_DATE)
134 (SELECT
135 'BIDBYSPREADSHEET',
136 fnd_message.get_string('PON', 'PON_AUCTS_AUCTION_LINE' || p_suffix),
137 p_batch_id,
138 bli.interface_line_id,
139 'PON_AUCTION_LINE_CLOSED' || p_suffix,
140 p_userid,
141 sysdate,
142 p_userid,
143 sysdate,
144 p_request_id,
145 bli.document_disp_line_number,
146 'TXT',
147 bli.auction_header_id,
148 bli.bid_number,
149 bli.line_number,
150 g_exp_date
151 FROM pon_bid_item_prices_interface bli
152 WHERE bli.batch_id = p_batch_id
153 AND bli.line_number = g_closed_int);
154
155 END validate_close_bidding_date;
156
157 PROCEDURE determine_skipped_lines
158 (
159 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
160 p_full_qty IN VARCHAR2
161 ) IS
162 BEGIN
163
164 -- PRECONDITIONS:
165 -- line_number are set for lines; it need not be set for children
166
167 -- Determine if any lines can be ignored
168 UPDATE pon_bid_item_prices_interface bli
169 SET bli.line_number = g_skip_int
170 WHERE bli.batch_id = p_batch_id
171 AND EXISTS
172 (SELECT 'Y'
173 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
174 WHERE bl.bid_number = bli.bid_number
175 AND bl.line_number = bli.line_number
176 AND al.auction_header_id = bl.auction_header_id
177 AND al.line_number = bl.line_number
178 AND (
179 -- GROUPs ignored
180 al.group_type = 'GROUP'
181
182 -- proxy lines ignored
183 OR bl.copy_price_for_proxy_flag = 'Y'
184
185 -- empty lines ignored
186 OR (al.price_disabled_flag = 'Y'
187 OR bli.bid_currency_price IS null)
188 AND (al.quantity_disabled_flag = 'Y'
189 OR bli.quantity IS null
190 OR (p_full_qty = 'Y' OR al.group_type = 'LOT_LINE'
191 OR al.order_type_lookup_code = 'AMOUNT'))
192 AND bli.note_to_auction_owner IS null
193 AND bli.attachment_desc IS null
194 AND bli.attachment_url IS null
195 AND bli.promised_date IS null
196 AND bli.recoupment_rate_percent IS null
197 AND bli.bid_curr_advance_amount IS null
198 AND bli.bid_curr_max_retainage_amt IS null
199 AND bli.retainage_rate_percent IS null
200 AND bli.progress_pymt_rate_percent IS null
201 -- No price elements
202 AND(bl.display_price_factors_flag = 'N'
203 OR NOT EXISTS
204 (SELECT bpfi.price_element_type_id
205 FROM pon_bid_price_elements_int bpfi
206 WHERE bpfi.batch_id = bli.batch_id
207 AND bpfi.interface_line_id = bli.interface_line_id
208 AND bpfi.bid_currency_value IS NOT null))
209 -- No price differentials
210 AND (al.has_price_differentials_flag = 'N'
211 OR NOT EXISTS
212 (SELECT bpdi.sequence_number
213 FROM pon_bid_price_differ_int bpdi
214 WHERE bpdi.batch_id = bli.batch_id
215 AND bpdi.auction_line_number = bli.line_number
216 AND bpdi.multiplier IS NOT null))
217 -- No attributes
218 AND (al.has_attributes_flag = 'N'
219 OR NOT EXISTS -- no attributes
220 (SELECT bai.attribute_name
221 FROM pon_bid_attr_values_interface bai
222 WHERE bai.batch_id = bli.batch_id
223 AND bai.interface_line_id = bli.interface_line_id
224 AND bai.value IS NOT null))));
225
226 END determine_skipped_lines;
227
228 PROCEDURE remove_invalid_skipped_lines
229 (
230 p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
231 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
232 p_request_id IN pon_bid_headers.request_id%TYPE,
233 p_userid IN pon_interface_errors.created_by%TYPE,
234 p_full_qty IN VARCHAR2,
235 p_buyer_user IN VARCHAR2,
236 p_suffix IN VARCHAR2
237 ) IS
238 BEGIN
239
240 -- 1. LINES
241
242 -- document_disp_line_number in pon_bid_item_prices_interface is the
243 -- line number specified in the spreadsheet. Since we do not lookup
244 -- the internal line number in the middle tier any longer, we
245 -- need to do that now and populate the children interface tables.
246 -- NOTE: if the line has an invalid document number, line_number is
247 -- set to g_error_int to indicate this
248 UPDATE pon_bid_item_prices_interface bli
249 SET bli.line_number =
250 nvl((SELECT al.line_number
251 FROM pon_auction_item_prices_all al
252 WHERE al.auction_header_id = bli.auction_header_id
253 AND al.document_disp_line_number = bli.document_disp_line_number),
254 g_error_int)
255 WHERE bli.batch_id = p_batch_id;
256
257 -- Report errors for invalid lines (invalid document_disp_line_number's)
258 INSERT INTO pon_interface_errors
259 (INTERFACE_TYPE,
260 COLUMN_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 ERROR_VALUE,
270 ERROR_VALUE_DATATYPE,
271 AUCTION_HEADER_ID,
272 BID_NUMBER,
273 EXPIRATION_DATE)
274 (SELECT
275 'BIDBYSPREADSHEET',
276 fnd_message.get_string('PON', 'PON_AUCTS_AUCTION_LINE' || p_suffix),
277 p_batch_id,
278 bli.interface_line_id,
279 'PON_AUC_INVALID_LINE_NUMBER' || p_suffix,
280 p_userid,
281 sysdate,
282 p_userid,
283 sysdate,
284 p_request_id,
285 bli.document_disp_line_number,
286 'TXT',
287 bli.auction_header_id,
288 bli.bid_number,
289 g_exp_date
290 FROM pon_bid_item_prices_interface bli
291 WHERE bli.batch_id = p_batch_id
292 AND bli.line_number = g_error_int);
293
294 -- Determine if there are any lines to be skipped, mark them so.
295 determine_skipped_lines(p_batch_id, p_full_qty);
296
297 -- Mark closed lines as closed and report an error - only for suppliers
298 IF (p_buyer_user = 'N') THEN
299 validate_close_bidding_date
300 (p_auc_header_id,
301 p_batch_id,
302 p_request_id,
303 p_userid,
304 p_suffix);
305 END IF;
306
307 -- 2. REMOVE INVALIDS FROM INTERFACE
308
309 -- Remove all invalid lines, closed lines and lines to be skipped
310 -- from the interface tables, along with their children
311 -- flag 'N' indicates that only erroneous records are to be purged
312
313 -- Delete from attributes interface table
314 DELETE FROM pon_bid_attr_values_interface bai
315 WHERE bai.batch_id = p_batch_id
316 AND bai.interface_line_id in (
317 select bli.interface_line_id
318 from pon_bid_item_prices_interface bli
319 where bli.batch_id = p_batch_id
320 AND (bli.line_number = g_error_int
321 OR bli.line_number = g_skip_int));
322
323 -- Delete from price elements interface table
324 DELETE FROM pon_bid_price_elements_int bpfi
325 WHERE bpfi.batch_id = p_batch_id
326 AND bpfi.interface_line_id in (
327 select bli.interface_line_id
328 from pon_bid_item_prices_interface bli
329 where bli.batch_id = p_batch_id
330 AND (bli.line_number = g_error_int
331 OR bli.line_number = g_skip_int));
332
333 -- Delete from price differentials interface table
334 DELETE FROM pon_bid_price_differ_int bpdi
335 WHERE bpdi.batch_id = p_batch_id
336 AND bpdi.interface_line_id in (
337 select bli.interface_line_id
338 from pon_bid_item_prices_interface bli
339 where bli.batch_id = p_batch_id
340 AND (bli.line_number = g_error_int
341 OR bli.line_number = g_skip_int));
342
343 -- Delete from lines interface table
344
345 DELETE FROM pon_bid_item_prices_interface bli
346 WHERE bli.batch_id = p_batch_id
347 AND (bli.line_number = g_error_int
348 OR bli.line_number = g_skip_int);
349
350 -- 3. ATTRIBUTES
351
352 -- Update attributes' internal line numbers's
353 -- NOTE: we also update line_number for those attributes with
354 -- valid line_numbers
355 UPDATE pon_bid_attr_values_interface bai
356 SET bai.line_number =
357 (SELECT bli.line_number
358 FROM pon_bid_item_prices_interface bli
359 WHERE bli.batch_id = bai.batch_id
360 AND bli.interface_line_id = bai.interface_line_id)
361 WHERE bai.batch_id = p_batch_id;
362
363 -- 4. PRICE ELEMENTS/COST FACTORS
364
365 -- Update price elements' internal line numbers's
366 -- NOTE: we also update line_number for those price elements with
367 -- valid line_numbers
368 UPDATE pon_bid_price_elements_int bpfi
369 SET bpfi.line_number =
370 (SELECT bli.line_number
371 FROM pon_bid_item_prices_interface bli
372 WHERE bli.batch_id = bpfi.batch_id
373 AND bli.interface_line_id = bpfi.interface_line_id)
374 WHERE bpfi.batch_id = p_batch_id;
375
376 -- 5. PRICE DIFFERENTIALS
377
378 -- Update price differentials' internal line numbers's
379 -- NOTE: we also update line_number for those price differentials with
380 -- valid line_numbers
381 UPDATE pon_bid_price_differ_int bpdi
382 SET bpdi.auction_line_number =
383 (SELECT bli.line_number
384 FROM pon_bid_item_prices_interface bli
385 WHERE bli.batch_id = bpdi.batch_id
386 AND bli.interface_line_id = bpdi.interface_line_id)
387 WHERE bpdi.batch_id = p_batch_id;
388
389 END remove_invalid_skipped_lines;
390
391 PROCEDURE validate_attribute_datatypes
392 (
393 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
394 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
395 p_userid IN pon_interface_errors.created_by%TYPE,
396 p_suffix IN VARCHAR2,
397 p_batch_id IN pon_interface_errors.batch_id%TYPE,
398 p_request_id IN pon_interface_errors.request_id%TYPE
399 ) IS
400 TYPE intLineTab IS TABLE of pon_bid_attr_values_interface.interface_line_id%TYPE;
401 TYPE lineNumberTab IS TABLE of pon_bid_attr_values_interface.line_number%TYPE;
402 TYPE attrNameTab IS TABLE of pon_bid_attr_values_interface.attribute_name%TYPE;
403 TYPE datatypeTab IS TABLE of pon_bid_attr_values_interface.datatype%TYPE;
404 TYPE valueTab IS TABLE of pon_bid_attr_values_interface.value%TYPE;
405 TYPE docLineNumTab IS TABLE of pon_auction_item_prices_all.document_disp_line_number%TYPE;
406
407 l_int_lines intLineTab;
408 l_line_numbers lineNumberTab;
409 l_attr_names attrNameTab;
410 l_datatypes datatypeTab;
411 l_values valueTab;
412 l_disp_line_numbers docLineNumTab;
413
414 l_num_errors NUMBER;
415 l_index NUMBER;
416
417 l_date_format VARCHAR2(15);
418 l_numeric_characters VARCHAR2(22);
419 l_decimal_separator_character VARCHAR2(1);
420 l_grouping_separator_character VARCHAR2(1);
421 BEGIN
422
423 -- Bulk collect all the interface attributes
424 SELECT
425 bai.interface_line_id,
426 bai.line_number,
427 bai.attribute_name,
428 bai.datatype,
429 bai.value,
430 al.document_disp_line_number
431 BULK COLLECT INTO
432 l_int_lines,
433 l_line_numbers,
434 l_attr_names,
435 l_datatypes,
436 l_values,
437 l_disp_line_numbers
438 FROM pon_bid_attr_values_interface bai,
439 pon_auction_item_prices_all al
440 WHERE bai.batch_id = p_batch_id
441 AND al.auction_header_id = bai.auction_header_id
442 AND al.line_number = bai.line_number;
443
444 -- Mark every attribute as invalid
445 -- The following statement will re-validate each attribute
446 UPDATE pon_bid_attr_values_interface bai
447 SET bai.line_number = g_error_int
448 WHERE bai.batch_id = p_batch_id;
449
450 l_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
451 l_numeric_characters := fnd_profile.value('ICX_NUMERIC_CHARACTERS');
452 l_decimal_separator_character := SUBSTR(l_numeric_characters, 1, 1);
453 l_grouping_separator_character := SUBSTR(l_numeric_characters, 2, 1);
454
455 -- Attempt the datatype conversions
456 FORALL i IN l_int_lines.FIRST..l_int_lines.LAST SAVE EXCEPTIONS
457 UPDATE pon_bid_attr_values_interface bai
458 SET bai.value = decode(l_datatypes(i),
459 'TXT', l_values(i),
460 'NUM', to_char(to_number(replace(l_values(i), l_grouping_separator_character), 'FM9999999999999999999999999999999999999999999999D9999999999999999', 'NLS_NUMERIC_CHARACTERS=''' || l_numeric_characters || ''''),
461 decode(instr(l_values(i), l_decimal_separator_character),
462 0,
463 'FM999999999999999999999999999999999999999999999999999999999999999',
464 'FM9999999999999999999999999999999999999999999999D9999999999999999'),
465 'NLS_NUMERIC_CHARACTERS=''.,'''),
466 'DAT', to_char(to_date(l_values(i), l_date_format), 'DD-MM-RRRR'),
467 'URL', l_values(i)),
468 bai.line_number = l_line_numbers(i)
469 WHERE bai.batch_id = p_batch_id
470 AND bai.interface_line_id = l_int_lines(i)
471 AND bai.attribute_name = l_attr_names(i);
472
473 -- NOTE: calling procedure should purge invalid attributes
474
475 EXCEPTION
476
477 -- FIX THIS - should this be OTHERS?
478 WHEN OTHERS THEN
479 l_num_errors := SQL%BULK_EXCEPTIONS.COUNT;
480
481 -- Insert errors for each erroneous attribute
482 FOR i IN 1..l_num_errors LOOP
483
484 l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
485
486 INSERT INTO pon_interface_errors
487 (INTERFACE_TYPE,
488 COLUMN_NAME,
489 TABLE_NAME,
490 BATCH_ID,
491 INTERFACE_LINE_ID,
492 ERROR_MESSAGE_NAME,
493 CREATED_BY,
494 CREATION_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_DATE,
497 REQUEST_ID,
498 ERROR_VALUE,
499 ERROR_VALUE_DATATYPE,
500 AUCTION_HEADER_ID,
501 BID_NUMBER,
502 LINE_NUMBER,
503 EXPIRATION_DATE,
504 TOKEN1_NAME,
505 TOKEN1_VALUE,
506 TOKEN2_NAME,
507 TOKEN2_VALUE)
508 VALUES
509 ('BIDBYSPREADSHEET',
510 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
511 'PON_BID_ATTR_VALUES',
512 p_batch_id,
513 l_int_lines(l_index),
514 'PON_AUC_ATTR_INVALID_TARGET' || p_suffix,
515 p_userid,
516 sysdate,
517 p_userid,
518 sysdate,
519 p_request_id,
520 l_values(l_index),
521 'TXT',
522 p_auc_header_id,
523 p_bid_number,
524 l_line_numbers(l_index),
525 g_exp_date,
526 'LINENUMBER',
527 l_disp_line_numbers(l_index),
528 'ATTRIBUTENAME',
529 l_attr_names(l_index));
530
531 END LOOP;
532
533 END validate_attribute_datatypes;
534
535 PROCEDURE validate_children
536 (
537 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
538 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
539 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
540 p_request_id IN pon_bid_headers.request_id%TYPE,
541 p_userid IN pon_interface_errors.created_by%TYPE,
542 p_has_pe IN VARCHAR2,
543 p_suffix IN VARCHAR2
544 ) IS
545 --added by Allen Yang for Surrogate Bid 2008/10/27
546 --------------------------------------------------
547 l_two_part_flag pon_auction_headers_all.two_part_flag%type;
548 l_surrogate_bid_flag pon_bid_headers.surrog_bid_flag%type;
549 l_tech_evaluation_status pon_auction_headers_all.technical_evaluation_status%type;
550 --------------------------------------------------
551 BEGIN
552 --added by Allen Yang for Surrogate Bid 2008/10/27
553 --------------------------------------------------
554 SELECT
555 paha.two_part_flag,
556 paha.technical_evaluation_status,
557 pbh.surrog_bid_flag
558 INTO
559 l_two_part_flag,
560 l_tech_evaluation_status,
561 l_surrogate_bid_flag
562 FROM pon_bid_headers pbh, pon_auction_headers_all paha
563 WHERE pbh.bid_number = p_bid_number
564 AND paha.auction_header_id = pbh.auction_header_id;
565 --------------------------------------------------
566
567 -- Since children are uploaded by name and not internal keys
568 -- the first step is to determine if the children are valid
569 -- by looking up the internal keys.
570
571 -- 1. ATTRIBUTES
572
573 -- Determine if there are any invalid attributes.
574 -- line_number is assigned a sentinel indicating an error
575 -- instead of attribute_name since attribute_name is a string.
576 UPDATE pon_bid_attr_values_interface bai
577 SET bai.line_number =
578 nvl((SELECT ba.line_number
579 FROM pon_bid_attribute_values ba
580 WHERE ba.bid_number = bai.bid_number
581 AND ba.line_number = bai.line_number
582 AND ba.attribute_name = bai.attribute_name), g_error_int),
583 bai.datatype =
584 nvl((SELECT ba.datatype
585 FROM pon_bid_attribute_values ba
586 WHERE ba.bid_number = bai.bid_number
587 AND ba.line_number = bai.line_number
588 AND ba.attribute_name = bai.attribute_name), 'N/A'),
589 bai.sequence_number =
590 nvl((SELECT ba.sequence_number
591 FROM pon_bid_attribute_values ba
592 WHERE ba.bid_number = bai.bid_number
593 AND ba.line_number = bai.line_number
594 AND ba.attribute_name = bai.attribute_name), g_error_int)
595
596
597 WHERE bai.batch_id = p_batch_id;
598
599 --bug 5166407: ignore the display only attributes
600 DELETE FROM pon_bid_attr_values_interface bai
601 WHERE bai.batch_id = p_batch_id
602 AND (bai.bid_number, bai.line_number, bai.sequence_number) in
603 (
604 select bh.bid_number, bh.line_number, aa.sequence_number
605 from pon_auction_attributes aa
606 , pon_bid_item_prices bh
607 where bh.bid_number = bai.bid_number
608 and bh.line_number = bai.line_number
609 and aa.auction_header_id = bh.auction_header_id
610 and aa.line_number = bh.line_number
611 and aa.sequence_number = bai.sequence_number
612 and aa.display_only_flag = 'Y'
613 );
614
615 -- Report errors for invalid attributes (invalid attr_name)
616 INSERT INTO pon_interface_errors
617 (INTERFACE_TYPE,
618 COLUMN_NAME,
619 BATCH_ID,
620 INTERFACE_LINE_ID,
621 ERROR_MESSAGE_NAME,
622 CREATED_BY,
623 CREATION_DATE,
624 LAST_UPDATED_BY,
625 LAST_UPDATE_DATE,
626 REQUEST_ID,
627 ERROR_VALUE,
628 ERROR_VALUE_DATATYPE,
629 AUCTION_HEADER_ID,
630 BID_NUMBER,
631 LINE_NUMBER,
632 EXPIRATION_DATE)
633 (SELECT
634 'BIDBYSPREADSHEET',
635 get_message_1_token('PON_AUC_ATTRIBUTE_ATTRNAME',
636 'ATTRNAME', bai.attribute_name),
637 p_batch_id,
638 bai.interface_line_id,
639 'PON_INVALID_ATTR_NAME' || p_suffix,
640 p_userid,
641 sysdate,
642 p_userid,
643 sysdate,
644 p_request_id,
645 bai.attribute_name,
646 'TXT',
647 bai.auction_header_id,
648 bai.bid_number,
649 bai.line_number,
650 g_exp_date
651 FROM pon_bid_attr_values_interface bai
652 WHERE bai.batch_id = p_batch_id
653 AND bai.line_number = g_error_int);
654
655 -- Remove all invalid attributes before validating datatypes
656
657 -- Delete from attributes interface table
658 DELETE FROM pon_bid_attr_values_interface bai
659 WHERE bai.batch_id = p_batch_id
660 AND bai.line_number = g_error_int;
661
662 -- Validate attributes' datatypes
663 validate_attribute_datatypes
664 (p_auc_header_id,
665 p_bid_number,
666 p_userid,
667 p_suffix,
668 p_batch_id,
669 p_request_id);
670
671 -- Delete from attributes interface table
672 DELETE FROM pon_bid_attr_values_interface bai
673 WHERE bai.batch_id = p_batch_id
674 AND bai.line_number = g_error_int;
675
676 -- 2. PRICE ELEMENTS/COST FACTORS
677
678 -- Update price_element_type_id since the user specifies price
679 -- elements by name and not price_element_type_id
680 UPDATE pon_bid_price_elements_int bpfi
681 SET bpfi.price_element_type_id =
682 nvl((SELECT pft.price_element_type_id
683 FROM pon_price_element_types_tl pft
684 WHERE pft.name = bpfi.price_element_name
685 AND pft.language = userenv('LANG')), g_error_int)
686 WHERE bpfi.batch_id = p_batch_id;
687
688 -- Report an error if not all SUPPLIER pf's on a line were found
689 -- Error is reported per line. Price factor errors reported below.
690 -- NOTE: this error check is performed before that for BUYER pf's
691
692 -- modified by Allen Yang for Surrogate Bid 2008/10/27
693 ------------------------------------------------------
694 --IF (p_has_pe = 'Y') THEN
695 IF (p_has_pe = 'Y' AND NOT (l_two_part_flag = 'Y' AND l_tech_evaluation_status = 'NOT_COMPLETED' AND l_surrogate_bid_flag = 'Y')) THEN
696 ------------------------------------------------------
697 INSERT INTO pon_interface_errors
698 (INTERFACE_TYPE,
699 COLUMN_NAME,
700 BATCH_ID,
701 INTERFACE_LINE_ID,
702 ERROR_MESSAGE_NAME,
703 CREATED_BY,
704 CREATION_DATE,
705 LAST_UPDATED_BY,
706 LAST_UPDATE_DATE,
707 REQUEST_ID,
708 ERROR_VALUE_DATATYPE,
709 AUCTION_HEADER_ID,
710 BID_NUMBER,
711 LINE_NUMBER,
712 EXPIRATION_DATE)
713 (SELECT
714 'BIDBYSPREADSHEET',
715 fnd_message.get_string('PON', 'PON_AUC_PRICE_ELEMENT'),
716 p_batch_id,
717 bli.interface_line_id,
718 'PON_AUC_NOT_ALL_BID_PE',
719 p_userid,
720 sysdate,
721 p_userid,
722 sysdate,
723 p_request_id,
724 'TXT',
725 bli.auction_header_id,
726 bli.bid_number,
727 bli.line_number,
728 g_exp_date
729 FROM pon_bid_item_prices_interface bli,
730 pon_bid_item_prices bip
731 WHERE bli.batch_id = p_batch_id
732 AND bli.bid_number = bip.bid_number
733 AND bli.line_number = bip.line_number
734 AND bip.display_price_factors_flag = 'Y'
735 AND EXISTS
736 (SELECT bpfi.price_element_name
737 FROM pon_bid_price_elements_int bpfi, pon_price_elements apf
738 WHERE apf.auction_header_id = p_auc_header_id
739 AND apf.line_number = bli.line_number
740 AND apf.pf_type = 'SUPPLIER'
741 AND bpfi.batch_id (+) = bli.batch_id
742 AND bpfi.line_number (+) = apf.line_number
743 AND bpfi.price_element_type_id (+) = apf.price_element_type_id
744 AND bpfi.price_element_type_id IS null
745 AND rownum = 1));
746
747 END IF;
748
749 -- BUYER price factors are not allowed to be uploaded
750 UPDATE pon_bid_price_elements_int bpfi
751 SET bpfi.price_element_type_id = g_error_int
752 WHERE bpfi.batch_id = p_batch_id
753 AND bpfi.price_element_type_id <> g_error_int
754 AND 'BUYER' =
755 (SELECT apf.pf_type
756 FROM pon_price_elements apf
757 WHERE apf.auction_header_id = bpfi.auction_header_id
758 AND apf.line_number = bpfi.line_number
759 AND apf.price_element_type_id = bpfi.price_element_type_id);
760
761 -- Report errors for invalid price elements
762 -- (invalid price_element_name or BUYER price factor)
763 INSERT INTO pon_interface_errors
764 (INTERFACE_TYPE,
765 COLUMN_NAME,
766 BATCH_ID,
767 INTERFACE_LINE_ID,
768 ERROR_MESSAGE_NAME,
769 CREATED_BY,
770 CREATION_DATE,
771 LAST_UPDATED_BY,
772 LAST_UPDATE_DATE,
773 REQUEST_ID,
774 ERROR_VALUE,
775 ERROR_VALUE_DATATYPE,
776 AUCTION_HEADER_ID,
777 BID_NUMBER,
778 LINE_NUMBER,
779 EXPIRATION_DATE)
780 (SELECT
781 'BIDBYSPREADSHEET',
782 get_message_1_token('PON_AUC_PRICE_ELEMENT_PENAME',
783 'PENAME', bpfi.price_element_name),
784 p_batch_id,
785 bpfi.interface_line_id,
786 'PON_AUC_INVALID_PRICE_NAME' || p_suffix,
787 p_userid,
788 sysdate,
789 p_userid,
790 sysdate,
791 p_request_id,
792 bpfi.price_element_name,
793 'TXT',
794 bpfi.auction_header_id,
795 bpfi.bid_number,
796 bpfi.line_number,
797 g_exp_date
798 FROM pon_bid_price_elements_int bpfi
799 WHERE bpfi.batch_id = p_batch_id
800 AND bpfi.price_element_type_id = g_error_int);
801
802 -- Delete from price elements interface table
803 DELETE FROM pon_bid_price_elements_int bpfi
804 WHERE bpfi.batch_id = p_batch_id
805 AND bpfi.price_element_type_id = g_error_int;
806
807
808 -- 3. PRICE DIFFERENTIALS
809
810 -- Update sequence_number since it is internal and user specifies
811 -- price differentials by price differential name
812 -- price differentials on lines with differential_response_type
813 -- as DISPLAY_ONLY are marked as skipped so they will get purged
814 UPDATE pon_bid_price_differ_int bpdi
815 SET bpdi.sequence_number =
816 nvl((SELECT decode(al.differential_response_type, 'DISPLAY_ONLY',
817 g_skip_int, bpd.price_differential_number)
818 FROM pon_bid_price_differentials bpd, po_price_diff_lookups_v pdl,
819 pon_auction_item_prices_all al
820 WHERE pdl.price_differential_dsp = bpdi.price_type
821 AND bpd.bid_number = bpdi.bid_number
822 AND bpd.line_number = bpdi.auction_line_number
823 AND bpd.price_type = pdl.price_differential_type
824 AND al.auction_header_id = bpd.auction_header_id
825 AND al.line_number = bpd.line_number
826 AND al.price_diff_shipment_number = bpd.shipment_number), g_error_int)
827 WHERE bpdi.batch_id = p_batch_id;
828
829 -- Report errors for invalid price differentials (invalid price_type)
830 INSERT INTO pon_interface_errors
831 (INTERFACE_TYPE,
832 COLUMN_NAME,
833 BATCH_ID,
834 INTERFACE_LINE_ID,
835 ERROR_MESSAGE_NAME,
836 CREATED_BY,
837 CREATION_DATE,
838 LAST_UPDATED_BY,
839 LAST_UPDATE_DATE,
840 REQUEST_ID,
841 ERROR_VALUE,
842 ERROR_VALUE_DATATYPE,
843 AUCTION_HEADER_ID,
844 BID_NUMBER,
845 LINE_NUMBER,
846 EXPIRATION_DATE)
847 (SELECT
848 'BIDBYSPREADSHEET',
849 get_message_1_token('PON_PRICE_DIFF_TYPE_NAME',
850 'PDNAME', bpdi.price_type),
851 p_batch_id,
852 bpdi.interface_line_id,
853 'PON_INVALID_PRICE_DIFF_TYPE' || p_suffix,
854 p_userid,
855 sysdate,
856 p_userid,
857 sysdate,
858 p_request_id,
859 bpdi.price_type,
860 'TXT',
861 bpdi.auction_header_id,
862 bpdi.bid_number,
863 bpdi.auction_line_number,
864 g_exp_date
865 FROM pon_bid_price_differ_int bpdi
866 WHERE bpdi.batch_id = p_batch_id
867 AND bpdi.sequence_number = g_error_int);
868
869 -- Delete from price differentials interface table
870 DELETE FROM pon_bid_price_differ_int bpdi
871 WHERE bpdi.batch_id = p_batch_id
872 AND bpdi.sequence_number = g_error_int;
873
874 END validate_children;
875
876 PROCEDURE default_from_auction
877 (
878 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
879 p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
880 p_bid_number IN pon_bid_headers.bid_number%TYPE,
881 p_full_qty IN VARCHAR2,
882 p_blanket IN VARCHAR2,
883 p_auc_closed IN VARCHAR2
884 ) IS
885 BEGIN
886
887 -- If it is a super large negotiation, then proxy is not allowed
888 IF (pon_large_auction_util_pkg.is_super_large_neg(p_auc_header_id)) THEN
889
890 UPDATE pon_bid_item_prices_interface
891 SET bid_currency_limit_price = null
892 WHERE batch_id = p_batch_id;
893
894 END IF;
895
896 -- Default some values in the interface table for each line
897 MERGE INTO pon_bid_item_prices_interface bli
898 USING
899 (SELECT bl.bid_number,
900 bl.line_number,
901 bl.display_price_factors_flag,
902 bl.copy_price_for_proxy_flag,
903 al.quantity,
904 al.quantity_disabled_flag,
905 al.price_disabled_flag,
906 al.group_type,
907 al.order_type_lookup_code
908 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
909 WHERE bl.bid_number = p_bid_number
910 AND al.auction_header_id = bl.auction_header_id
911 AND al.line_number = bl.line_number) lines
912 ON (bli.bid_number = lines.bid_number
913 AND bli.line_number = lines.line_number)
914 WHEN MATCHED THEN
915 UPDATE SET
916 -- price does not apply if item has price factors
917 -- or if price is disabled
918 bli.bid_currency_price =
919 decode(lines.display_price_factors_flag, 'Y', null,
920 decode(lines.price_disabled_flag, 'Y', null,
921 bli.bid_currency_price)),
922
923 -- quantity := auction quantity if full qty reqd, LOT_LINE
924 -- or AMOUNT/FIXED PRICE line (1/null)
925 -- NOTE: quantity := null if blanket
926 bli.quantity =
927 decode(p_blanket, 'Y', null,
928 decode(lines.quantity_disabled_flag, 'Y', null,
929 decode(p_full_qty, 'Y', lines.quantity,
930 decode(lines.group_type, 'LOT_LINE', lines.quantity,
931 decode(lines.order_type_lookup_code,
932 'AMOUNT', lines.quantity,
933 'FIXED PRICE', lines.quantity, bli.quantity))))),
934
935 -- null proxy fields for lot lines or if the auction is closed
936 -- proxy fields also do not apply if the line has price factors
937 -- and if an active proxy already exists
938 bli.bid_currency_limit_price =
939 decode(lines.group_type, 'LOT_LINE', null,
940 decode(p_auc_closed, 'Y', null,
941 decode(lines.display_price_factors_flag, 'Y', null,
942 decode(lines.copy_price_for_proxy_flag, 'Y', null,
943 bli.bid_currency_limit_price))));
944
945 END default_from_auction;
946
947 PROCEDURE copy_interface_to_txn_tables
948 (
949 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
950 p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
951 p_bid_number IN pon_bid_headers.bid_number%TYPE,
952 p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
953 p_hdr_disp_pf IN VARCHAR2,
954 p_blanket IN VARCHAR2,
955 p_mas IN VARCHAR2,
956 p_progress_payment_type IN VARCHAR2,
957 p_spreadsheet_type IN VARCHAR2,
958 p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
959 p_price_tiers_indicator IN pon_auction_headers_all.PRICE_TIERS_INDICATOR%type
960 ) IS
961 l_module CONSTANT VARCHAR2(32) := 'copy_interface_to_txn_tables';
962 l_result VARCHAR2(1);
963 l_error_code VARCHAR2(200);
964 l_error_message VARCHAR2(2000);
965
966 BEGIN
967
968 -- Update lines table with values in the interface table
969 MERGE INTO pon_bid_item_prices bl
970 USING
971 (SELECT
972 pbip.bid_number,
973 pbip.line_number,
974 pbip.batch_id,
975 pbip.interface_line_id,
976 pbip.quantity,
977 pbip.bid_currency_price,
978 pbip.note_to_auction_owner,
979 pbip.promised_date,
980 pbip.bid_currency_limit_price,
981 pbip.po_bid_min_rel_amount,
982 pbip.bid_curr_advance_amount,
983 pbip.recoupment_rate_percent,
984 pbip.progress_pymt_rate_percent,
985 pbip.retainage_rate_percent,
986 pbip.bid_curr_max_retainage_amt,
987 pah.progress_pymt_negotiable_flag,
988 pah.advance_negotiable_flag,
989 pah.retainage_negotiable_flag,
990 pah.max_retainage_negotiable_flag,
991 pah.recoupment_negotiable_flag,
992 pbip.worksheet_name,
993 pbip.worksheet_sequence_number
994 FROM pon_bid_item_prices_interface pbip,
995 pon_auction_headers_all pah
996 WHERE batch_id = p_batch_id
997 AND pah.auction_header_id = pbip.auction_header_id) bli
998 ON (bl.bid_number = bli.bid_number
999 AND bl.line_number = bli.line_number)
1000 WHEN MATCHED THEN
1001 UPDATE SET
1002 bl.batch_id = bli.batch_id,
1003 bl.interface_line_id = bli.interface_line_id,
1004 bl.quantity = bli.quantity,
1005 -- NOTE: we copy into bid_currency_unit_price, NOT bid_currency_price
1006 -- Later, the bid_currency_unit_price column is used to calculate
1007 -- the other price columns since bid_currency_price doesn't always
1008 -- have the same meaning for different price factors views
1009 bl.bid_currency_unit_price = bli.bid_currency_price,
1010 bl.note_to_auction_owner = bli.note_to_auction_owner,
1011 bl.promised_date = bli.promised_date,
1012 bl.bid_currency_limit_price = bli.bid_currency_limit_price,
1013 bl.po_bid_min_rel_amount = bli.po_bid_min_rel_amount,
1014 bl.bid_curr_advance_amount = DECODE(bli.advance_negotiable_flag,'Y',bli.bid_curr_advance_amount,bl.bid_curr_advance_amount),
1015 bl.recoupment_rate_percent = DECODE(bli.recoupment_negotiable_flag,'Y',bli.recoupment_rate_percent,bl.recoupment_rate_percent),
1016 bl.progress_pymt_rate_percent = DECODE(bli.progress_pymt_negotiable_flag,'Y',bli.progress_pymt_rate_percent,bl.progress_pymt_rate_percent),
1017 bl.retainage_rate_percent = DECODE(bli.retainage_negotiable_flag,'Y',bli.retainage_rate_percent,bl.retainage_rate_percent),
1018 bl.bid_curr_max_retainage_amt = DECODE(bli.max_retainage_negotiable_flag,'Y',bli.bid_curr_max_retainage_amt,bl.bid_curr_max_retainage_amt),
1019 bl.last_update_date = sysdate,
1020 bl.last_updated_by = p_userid,
1021 bl.worksheet_name = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_name,to_char(null)),
1022 bl.worksheet_sequence_number = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_sequence_number,to_number(null));
1023
1024 -- When a GROUP's child recieved a bid, we mark that group as part of the batch
1025 UPDATE pon_bid_item_prices bl
1026 SET batch_id = p_batch_id
1027 WHERE bl.bid_number = p_bid_number
1028 AND (SELECT al.group_type
1029 FROM pon_auction_item_prices_all al
1030 WHERE al.auction_header_id = bl.auction_header_id
1031 AND al.line_number = bl.line_number) = 'GROUP'
1032 AND EXISTS
1033 (SELECT bl2.line_number
1034 FROM pon_bid_item_prices bl2, pon_auction_item_prices_all al2
1035 WHERE bl2.bid_number = p_bid_number
1036 AND al2.auction_header_id = bl2.auction_header_id
1037 AND al2.line_number = bl2.line_number
1038 AND al2.parent_line_number = bl.line_number
1039 AND bl2.batch_id = p_batch_id);
1040
1041 -- Update requirements/attributes table from interface
1042 MERGE INTO pon_bid_attribute_values ba
1043 USING
1044 (SELECT
1045 auction_header_id,
1046 bid_number,
1047 line_number,
1048 batch_id,
1049 interface_line_id,
1050 sequence_number,
1051 value,
1052 worksheet_name,
1053 worksheet_sequence_number
1054 FROM pon_bid_attr_values_interface
1055 WHERE auction_header_id = p_auction_header_id
1056 AND bid_number = p_bid_number
1057 AND batch_id = p_batch_id) bai
1058 ON (ba.auction_header_id = bai.auction_header_id
1059 AND ba.bid_number = bai.bid_number
1060 AND ba.line_number = bai.line_number
1061 AND ba.sequence_number= bai.sequence_number)
1062 WHEN MATCHED THEN
1063 UPDATE SET
1064 ba.batch_id = bai.batch_id,
1065 ba.interface_line_id = bai.interface_line_id,
1066 ba.value = bai.value,
1067 ba.worksheet_name = bai.worksheet_name,
1068 ba.worksheet_sequence_number = bai.worksheet_sequence_number,
1069 ba.last_update_date = sysdate,
1070 ba.last_updated_by = p_userid;
1071
1072 -- For MAS, quantity or promised date are scored, they need to
1073 -- be updated in the bid attributes transaction table
1074 IF (p_mas = 'Y' and p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_txt_upload_mode) THEN
1075 -- Update promised_date
1076 UPDATE pon_bid_attribute_values ba
1077 SET value =
1078 nvl((SELECT to_char(bl.promised_date, 'dd-mm-yyyy hh24:mi:ss')
1079 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1080 WHERE bl.bid_number = ba.bid_number
1081 AND bl.line_number = ba.line_number
1082 AND al.auction_header_id = bl.auction_header_id
1083 AND al.line_number = bl.line_number
1084 AND al.is_need_by_date_scored = 'Y'), ba.value)
1085 WHERE ba.bid_number = p_bid_number
1086 AND ba.sequence_number = -10;
1087
1088 -- Update quantity
1089 UPDATE pon_bid_attribute_values ba
1090 SET value =
1091 nvl((SELECT to_char(bl.quantity)
1092 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1093 WHERE bl.bid_number = ba.bid_number
1094 AND bl.line_number = ba.line_number
1095 AND al.auction_header_id = bl.auction_header_id
1096 AND al.line_number = bl.line_number
1097 AND al.is_quantity_scored = 'Y'), ba.value)
1098 WHERE ba.bid_number = p_bid_number
1099 AND ba.sequence_number = -20;
1100
1101 END IF;
1102
1103 -- IMPLEMENT: step 4(C) from DLD
1104
1105 -- Copy price factors only if they exist
1106 IF (p_hdr_disp_pf = 'Y') THEN
1107
1108 -- Update price elements transaction table from interface table
1109 -- only set bid currency value. The auction currency value
1110 -- will be recalculated later in
1111 -- recalculate_auc_curr_prices
1112 MERGE INTO pon_bid_price_elements bpf
1113 USING
1114 (SELECT
1115 batch_id,
1116 bid_number,
1117 line_number,
1118 price_element_type_id,
1119 bid_currency_value,
1120 interface_line_id
1121 FROM pon_bid_price_elements_int
1122 WHERE batch_id = p_batch_id
1123 AND auction_header_id = p_auction_header_id
1124 AND bid_number = p_bid_number) bpfi
1125 ON (bpf.bid_number = bpfi.bid_number
1126 AND bpf.line_number = bpfi.line_number
1127 AND bpf.price_element_type_id = bpfi.price_element_type_id)
1128 WHEN MATCHED THEN
1129 UPDATE SET
1130 bpf.batch_id = bpfi.batch_id,
1131 bpf.bid_currency_value = bpfi.bid_currency_value,
1132 bpf.last_update_date = sysdate,
1133 bpf.last_updated_by = p_userid,
1134 bpf.interface_line_id = bpfi.interface_line_id;
1135
1136 -- Sync bid_currency_unit_price from price factors to lines table
1137 UPDATE pon_bid_item_prices bl
1138 SET bl.bid_currency_unit_price =
1139 (SELECT bpf.bid_currency_value
1140 FROM pon_bid_price_elements bpf
1141 WHERE bpf.bid_number = bl.bid_number
1142 AND bpf.line_number = bl.line_number
1143 AND bpf.price_element_type_id = -10)
1144 WHERE bl.bid_number = p_bid_number
1145 AND bl.display_price_factors_flag = 'Y'
1146 AND bl.batch_id = p_batch_id;
1147
1148 END IF;
1149
1150 IF (p_blanket = 'Y') THEN
1151
1152 IF p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_xml_upload_mode THEN
1153 -- Copy over all Line level price differentials to transaction table
1154 MERGE INTO pon_bid_price_differentials bpd
1155 USING
1156 (SELECT
1157 bid_number,
1158 auction_line_number,
1159 auction_shipment_number,
1160 sequence_number,
1161 interface_line_id,
1162 multiplier
1163 FROM pon_bid_price_differ_int
1164 WHERE batch_id = p_batch_id
1165 and auction_shipment_number = -1) bpdi
1166 ON (bpd.bid_number = bpdi.bid_number
1167 AND bpd.line_number = bpdi.auction_line_number
1168 AND bpd.shipment_number = bpdi.auction_shipment_number
1169 AND bpd.price_differential_number = bpdi.sequence_number)
1170 WHEN MATCHED THEN
1171 UPDATE SET
1172 bpd.multiplier = bpdi.multiplier,
1173 bpd.interface_line_id = bpdi.interface_line_id,
1174 bpd.last_update_date = sysdate,
1175 bpd.last_updated_by = p_userid;
1176
1177 --copy over price break level price differentials to transaction table
1178 --only copy when this is price break
1179 IF (p_price_tiers_indicator = g_pt_indicator_pricebreak) THEN
1180
1181 MERGE INTO pon_bid_price_differentials bpd
1182 USING
1183 ( select bpdi.bid_number,
1184 bpdi.auction_line_number,
1185 bsh.shipment_number,
1186 bpdi.sequence_number,
1187 bpdi.interface_line_id ,
1188 bpdi.multiplier
1189 from pon_bid_price_differ_int bpdi,
1190 pon_bid_shipments bsh
1191 where bpdi.batch_id = p_batch_id
1192 and bpdi.auction_header_id = bsh.auction_header_id
1193 and bpdi.bid_number = bsh.bid_number
1194 and bpdi.auction_line_number = bsh.line_number
1195 and bpdi.auction_shipment_number = bsh.auction_shipment_number
1196 and bpdi.auction_shipment_number <> -1 ) bpdi2
1197 ON (bpd.bid_number = bpdi2.bid_number
1198 AND bpd.line_number = bpdi2.auction_line_number
1199 AND bpd.shipment_number = bpdi2.shipment_number
1200 AND bpd.price_differential_number = bpdi2.sequence_number)
1201 WHEN MATCHED THEN
1202 UPDATE SET
1203 bpd.interface_line_id = bpdi2.interface_line_id,
1204 bpd.multiplier = bpdi2.multiplier,
1205 bpd.last_update_date = sysdate,
1206 bpd.last_updated_by = p_userid;
1207
1208 --Process Price Break, this method does followings:
1209 -- 1. Update existing Price Breaks from Interface table to Transaction table
1210 -- 2. Insert new Price Break to Transaction table
1211 -- 3. Delete Price Breaks from Transaction table for those that are intended to be deleted
1212 -- 4. Update Price Breaks from Buyer defined to Supplier owned for those that has structure changes
1213 -- 5. Delete Price Differential associated with the deleted or structure changed Price Breaks
1214 copy_shipment_interface_to_txn(
1215 p_batch_id=>p_batch_id,
1216 p_bid_number=>p_bid_number,
1217 p_userid =>p_userid,
1218 p_bid_currency_precision=> p_bid_currency_precision,
1219 p_shipment_type => g_shipment_type_pricebreak
1220 );
1221 END IF;-- end of IF (p_price_tiers_indicator = g_pt_indicator_pricebreak)
1222 ELSE
1223 MERGE INTO pon_bid_price_differentials bpd
1224 USING
1225 (SELECT
1226 bid_number,
1227 auction_line_number,
1228 sequence_number,
1229 multiplier
1230 FROM pon_bid_price_differ_int
1231 WHERE batch_id = p_batch_id) bpdi
1232 ON (bpd.bid_number = bpdi.bid_number
1233 AND bpd.line_number = bpdi.auction_line_number
1234 AND bpd.shipment_number = -1
1235 AND bpd.price_differential_number = bpdi.sequence_number)
1236 WHEN MATCHED THEN
1237 UPDATE SET
1238 bpd.multiplier = bpdi.multiplier,
1239 bpd.last_update_date = sysdate,
1240 bpd.last_updated_by = p_userid;
1241 END IF;
1242
1243
1244 END IF;
1245
1246 IF (p_price_tiers_indicator = g_pt_indicator_quantitybased and p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_xml_upload_mode) THEN
1247 copy_shipment_interface_to_txn(
1248 p_batch_id=>p_batch_id,
1249 p_bid_number=>p_bid_number,
1250 p_userid =>p_userid,
1251 p_bid_currency_precision=> p_bid_currency_precision,
1252 p_shipment_type => g_shipment_type_quantitybased
1253 );
1254 END IF;
1255
1256 IF p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_xml_upload_mode THEN
1257 --Update bid header table with Xml Spreadsheet header info
1258 BEGIN
1259 UPDATE PON_BID_HEADERS pbh
1260 SET (pbh.SURROG_BID_RECEIPT_DATE,
1261 pbh.BIDDERS_BID_NUMBER,
1262 pbh.BID_EXPIRATION_DATE,
1263 pbh.NOTE_TO_AUCTION_OWNER,
1264 pbh.MIN_BID_CHANGE) =
1265 (SELECT decode(pbh.surrog_bid_flag, 'N', pbh.SURROG_BID_RECEIPT_DATE, pbhi.SURROG_BID_RECEIPT_DATE),
1266 pbhi.BIDDERS_BID_NUMBER,
1267 pbhi.BID_EXPIRATION_DATE,
1268 pbhi.NOTE_TO_AUCTION_OWNER,
1269 pbhi.MIN_BID_CHANGE
1270 FROM PON_BID_HEADERS_INTERFACE pbhi
1271 WHERE batch_id = p_batch_id)
1272 WHERE pbh.bid_number = p_bid_number;
1273
1274 --Delete from header interface
1275 DELETE FROM PON_BID_HEADERS_INTERFACE WHERE batch_id = p_batch_id;
1276
1277 -- Copy payments to transaction tables
1278 IF p_progress_payment_type <> 'NONE' THEN
1279 PON_VALIDATE_PAYMENTS_INT.COPY_PAYMENTS_FROM_INT_TO_TXN(p_batch_id,
1280 PON_BID_VALIDATIONS_PKG.g_xml_upload_mode,
1281 p_bid_number,
1282 p_auction_header_id,
1283 l_result,
1284 l_error_code,
1285 l_error_message);
1286 END IF;
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1291 FND_LOG.string(log_level => FND_LOG.level_procedure,
1292 module => g_module_prefix || l_module,
1293 message => ' Error Code=' || SQLCODE || ' SQLERRM=' || SQLERRM);
1294 END if;
1295 END;
1296 END IF;
1297
1298
1299 -- Remove all records for the current batch from the interface table
1300 DELETE FROM pon_bid_item_prices_interface bli
1301 WHERE bli.batch_id = p_batch_id;
1302
1303 DELETE FROM pon_bid_attr_values_interface bai
1304 WHERE bai.batch_id = p_batch_id;
1305
1306 DELETE FROM pon_bid_price_elements_int bpfi
1307 WHERE bpfi.batch_id = p_batch_id;
1308
1309 DELETE FROM pon_bid_price_differ_int bpdi
1310 WHERE bpdi.batch_id = p_batch_id;
1311
1312 DELETE FROM pon_bid_shipments_int bshi
1313 WHERE bshi.batch_id = p_batch_id;
1314
1315 END copy_interface_to_txn_tables;
1316
1317 PROCEDURE create_url_attachments
1318 (
1319 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1320 p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
1321 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1322 p_userid IN pon_bid_headers.created_by%TYPE
1323 ) IS
1324 l_seq_num NUMBER;
1325 l_target_category FND_DOCUMENT_CATEGORIES.NAME%type := pon_auction_pkg.g_supplier_attachment; -- target attachment category, default 'FromSupplier'
1326 l_two_part_flag PON_AUCTION_HEADERS_ALL.TWO_PART_FLAG%type := 'N';
1327
1328 CURSOR attachment_lines IS
1329 SELECT bli.attachment_url,
1330 bli.attachment_desc,
1331 bli.line_number
1332 FROM pon_bid_item_prices_interface bli
1333 WHERE bli.batch_id = p_batch_id
1334 AND bli.attachment_desc IS NOT null
1335 AND bli.attachment_url IS NOT null;
1336 BEGIN
1337 -- get two_part_flag for RFQ
1338 select nvl(two_part_flag, 'N')
1339 into l_two_part_flag
1340 from pon_auction_headers_all
1341 where auction_header_id = p_auc_header_id;
1342
1343 -- change target attachment category for two-part RFQ.
1344 IF l_two_part_flag = 'Y' THEN -- {
1345 -- change to From Supplier: Technical
1346 l_target_category := pon_auction_pkg.g_technical_attachment;
1347 END IF; -- }
1348
1349 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN -- {
1350 FND_LOG.string(log_level => FND_LOG.level_statement,
1351 module => g_module_prefix || 'create_url_attachments',
1352 message => 'l_two_part_flag: ' || l_two_part_flag || '; l_target_category: ' || l_target_category);
1353 END IF; -- }
1354
1355 -- Call our API to create a long text attachment
1356 FOR line IN attachment_lines LOOP
1357
1358 SELECT nvl(max(seq_num), 0) + 1
1359 INTO l_seq_num
1360 FROM fnd_attached_documents
1361 WHERE entity_name = 'PON_BID_ITEM_PRICES'
1362 AND pk1_value = p_auc_header_id
1363 AND pk2_value = p_bid_number
1364 AND pk3_value = line.line_number;
1365
1366 PON_OA_UTIL_PKG.create_url_attachment
1367 (l_seq_num,
1368 l_target_category,
1369 line.attachment_desc,
1370 5,
1371 line.attachment_url,
1372 'PON_BID_ITEM_PRICES',
1373 p_auc_header_id,
1374 p_bid_number,
1375 line.line_number,
1376 null,
1377 null);
1378 END LOOP;
1379
1380 END create_url_attachments;
1381
1382 PROCEDURE process_spreadsheet_data
1383 (
1384 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1385 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1386 p_request_id IN pon_bid_headers.request_id%TYPE,
1387 p_userid IN pon_interface_errors.created_by%TYPE,
1388 x_return_status OUT NOCOPY VARCHAR2,
1389 x_return_code OUT NOCOPY VARCHAR2
1390 ) IS
1391 l_auc_header_id pon_auction_headers_all.auction_header_id%TYPE;
1392
1393 l_header_disp_pf VARCHAR2(1);
1394 l_blanket VARCHAR2(1);
1395 l_mas VARCHAR2(1);
1396 l_full_qty VARCHAR2(1);
1397 l_auc_closed VARCHAR2(1);
1398 l_buyer_user VARCHAR2(1);
1399 l_supplier_user VARCHAR2(1);
1400 l_has_pe VARCHAR2(1);
1401
1402 l_suffix VARCHAR2(2);
1403 l_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE;
1404 l_price_precision pon_bid_headers.number_price_decimals%TYPE;
1405 BEGIN
1406
1407 -- Determine some negotiation flags
1408 SELECT sysdate + g_exp_days_offset,
1409 ah.auction_header_id,
1410 bh.display_price_factors_flag,
1411 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
1412 decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
1413 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
1414 decode(ah.auction_status, 'AUCTION_CLOSED', 'Y', 'N'),
1415 bh.surrog_bid_flag,
1416 decode(bh.surrog_bid_flag, 'Y', 'N', 'Y'),
1417 nvl(ah.has_price_elements, 'N'),
1418 ah.progress_payment_type,
1419 bh.number_price_decimals
1420 INTO g_exp_date,
1421 l_auc_header_id,
1422 l_header_disp_pf,
1423 l_blanket,
1424 l_mas,
1425 l_full_qty,
1426 l_auc_closed,
1427 l_buyer_user,
1428 l_supplier_user,
1429 l_has_pe,
1430 l_progress_payment_type,
1431 l_price_precision
1432 FROM pon_bid_headers bh, pon_auction_headers_all ah
1433 WHERE bh.bid_number = p_bid_number
1434 AND ah.auction_header_id = bh.auction_header_id;
1435
1436 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.get_doctype_suffix(l_auc_header_id);
1437
1438 -- Determine if there are any invalid lines or lines to be skipped
1439 remove_invalid_skipped_lines
1440 (l_auc_header_id,
1441 p_batch_id,
1442 p_request_id,
1443 p_userid,
1444 l_full_qty,
1445 l_buyer_user,
1446 l_suffix);
1447
1448 -- Validate line children
1449 validate_children
1450 (l_auc_header_id,
1451 p_bid_number,
1452 p_batch_id,
1453 p_request_id,
1454 p_userid,
1455 l_has_pe,
1456 l_suffix);
1457
1458 -- Default certain fields from the auction side
1459 default_from_auction
1460 (p_batch_id,
1461 l_auc_header_id,
1462 p_bid_number,
1463 l_full_qty,
1464 l_blanket,
1465 l_auc_closed);
1466
1467 -- For all the valid lines, create the URL attachments
1468 -- This will be rolled back in the middle tier if necessary
1469 -- Ensure that fnd doesn't add a commit in here
1470 create_url_attachments
1471 (p_batch_id,
1472 l_auc_header_id,
1473 p_bid_number,
1474 p_userid);
1475
1476 -- Push the data to the transaction tables so we can validate it
1477 copy_interface_to_txn_tables
1478 (p_batch_id,
1479 l_auc_header_id,
1480 p_bid_number,
1481 p_userid,
1482 l_header_disp_pf,
1483 l_blanket,
1484 l_mas,
1485 l_progress_payment_type,
1486 'TXT',
1487 l_price_precision,
1488 --p_price_tiers_indicator in copy_interface_to_txn_tables() only used for xml upload mode, so it doesn't matter what this value is.
1489 '');
1490
1491 -- Update auction currency columns for the current batch
1492 PON_RESPONSE_PVT.recalculate_auc_curr_prices(p_bid_number, 'N', p_batch_id);
1493
1494 -- Update group amounts for the current batch
1495 -- NOTE: group amount is only calculated at the time of publish
1496 -- PON_RESPONSE_PVT.calculate_group_amounts(p_bid_number, l_supplier_user, 'N', p_batch_id);
1497
1498 -- Validate the data once it has been copied to the transaction tables
1499 PON_BID_VALIDATIONS_PKG.validate_spreadsheet_upload
1500 (l_auc_header_id,
1501 p_bid_number,
1502 'BIDBYSPREADSHEET',
1503 PON_BID_VALIDATIONS_PKG.g_txt_upload_mode,
1504 p_userid,
1505 p_batch_id,
1506 p_request_id,
1507 x_return_status,
1508 x_return_code);
1509
1510 END process_spreadsheet_data;
1511
1512 /*
1513 To validate Xml spreadsheet header entity
1514 */
1515
1516 PROCEDURE VALIDATE_HEADER
1517 (
1518 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1519 p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1520 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1521 p_request_id IN pon_bid_item_prices.request_id%TYPE,
1522 p_user_id IN pon_interface_errors.created_by%TYPE
1523 ) IS
1524
1525 l_loginid NUMBER;
1526 l_interface_type pon_interface_errors.interface_type%TYPE;
1527 l_lines_worksheet_sequence NUMBER;
1528 l_header_worksheet_sequence NUMBER;
1529 l_suffix VARCHAR2(2);
1530 BEGIN
1531
1532 l_loginid := fnd_global.login_id;
1533 l_interface_type := 'BIDBYSPREADSHEET';
1534 l_header_worksheet_sequence := 1;
1535 l_lines_worksheet_sequence := 2;
1536
1537 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.get_doctype_suffix(p_auction_header_id);
1538
1539 INSERT ALL
1540 --1
1541 WHEN s_min_bid_change IS NOT NULL
1542 AND s_min_bid_change_type <> 'PERCENTAGE'
1543 AND PON_BID_VALIDATIONS_PKG.validate_price_precision(
1544 s_min_bid_change, s_bid_price_precision) = 'F' THEN
1545 INTO pon_interface_errors
1546 (INTERFACE_TYPE,
1547 COLUMN_NAME,
1548 BATCH_ID,
1549 INTERFACE_LINE_ID,
1550 ERROR_MESSAGE_NAME,
1551 CREATED_BY,
1552 CREATION_DATE,
1553 LAST_UPDATED_BY,
1554 LAST_UPDATE_DATE,
1555 LAST_UPDATE_LOGIN,
1556 REQUEST_ID,
1557 ERROR_VALUE_NUMBER,
1558 ERROR_VALUE_DATATYPE,
1559 AUCTION_HEADER_ID,
1560 BID_NUMBER,
1561 EXPIRATION_DATE,
1562 WORKSHEET_NAME,
1563 WORKSHEET_SEQUENCE_NUMBER
1564 )
1565 VALUES
1566 (
1567 l_interface_type,
1568 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
1569 p_batch_id,
1570 s_proxy_bid_row,
1571 'PON_AUC_MINDEC_INVALID_PREC' || l_suffix,
1572 p_user_id,
1573 sysdate,
1574 p_user_id,
1575 sysdate,
1576 l_loginid,
1577 p_request_id,
1578 s_min_bid_change,
1579 'NUM',
1580 s_auction_header_id,
1581 s_bid_number,
1582 g_exp_date,
1583 s_lines_worksheet_name,
1584 l_lines_worksheet_sequence
1585 )
1586 --2
1587 WHEN s_min_bid_change IS NOT NULL
1588 AND s_min_bid_change <= 0 THEN
1589 INTO pon_interface_errors
1590 (INTERFACE_TYPE,
1591 COLUMN_NAME,
1592 BATCH_ID,
1593 INTERFACE_LINE_ID,
1594 ERROR_MESSAGE_NAME,
1595 CREATED_BY,
1596 CREATION_DATE,
1597 LAST_UPDATED_BY,
1598 LAST_UPDATE_DATE,
1599 LAST_UPDATE_LOGIN,
1600 REQUEST_ID,
1601 ERROR_VALUE_NUMBER,
1602 ERROR_VALUE_DATATYPE,
1603 AUCTION_HEADER_ID,
1604 BID_NUMBER,
1605 EXPIRATION_DATE,
1606 WORKSHEET_NAME,
1607 WORKSHEET_SEQUENCE_NUMBER
1608 )
1609 VALUES
1610 (
1611 l_interface_type,
1612 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
1613 p_batch_id,
1614 s_proxy_bid_row,
1615 'PON_AUC_MINDEC_POS' || l_suffix,
1616 p_user_id,
1617 sysdate,
1618 p_user_id,
1619 sysdate,
1620 l_loginid,
1621 p_request_id,
1622 s_min_bid_change,
1623 'NUM',
1624 s_auction_header_id,
1625 s_bid_number,
1626 g_exp_date,
1627 s_lines_worksheet_name,
1628 l_lines_worksheet_sequence
1629 )
1630 --3
1631 WHEN s_min_bid_change IS NOT NULL
1632 AND s_min_bid_change < s_auc_min_bid_decrement THEN
1633 INTO pon_interface_errors
1634 (INTERFACE_TYPE,
1635 COLUMN_NAME,
1636 BATCH_ID,
1637 INTERFACE_LINE_ID,
1638 ERROR_MESSAGE_NAME,
1639 CREATED_BY,
1640 CREATION_DATE,
1641 LAST_UPDATED_BY,
1642 LAST_UPDATE_DATE,
1643 LAST_UPDATE_LOGIN,
1644 REQUEST_ID,
1645 ERROR_VALUE_NUMBER,
1646 ERROR_VALUE_DATATYPE,
1647 AUCTION_HEADER_ID,
1648 BID_NUMBER,
1649 EXPIRATION_DATE,
1650 WORKSHEET_NAME,
1651 WORKSHEET_SEQUENCE_NUMBER
1652 )
1653 VALUES
1654 (
1655 l_interface_type,
1656 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
1657 p_batch_id,
1658 s_proxy_bid_row,
1659 'PON_AUCTS_MIN_DEC_LOWER' || l_suffix,
1660 p_user_id,
1661 sysdate,
1662 p_user_id,
1663 sysdate,
1664 l_loginid,
1665 p_request_id,
1666 s_min_bid_change,
1667 'NUM',
1668 s_auction_header_id,
1669 s_bid_number,
1670 g_exp_date,
1671 s_lines_worksheet_name,
1672 l_lines_worksheet_sequence
1673 )
1674 --4
1675 WHEN s_surrogate_bid_flag = 'Y'
1676 AND s_response_recvd_time IS NULL THEN
1677 INTO pon_interface_errors
1678 (INTERFACE_TYPE,
1679 COLUMN_NAME,
1680 BATCH_ID,
1681 INTERFACE_LINE_ID,
1682 ERROR_MESSAGE_NAME,
1683 CREATED_BY,
1684 CREATION_DATE,
1685 LAST_UPDATED_BY,
1686 LAST_UPDATE_DATE,
1687 LAST_UPDATE_LOGIN,
1688 REQUEST_ID,
1689 ERROR_VALUE,
1690 ERROR_VALUE_DATATYPE,
1691 AUCTION_HEADER_ID,
1692 BID_NUMBER,
1693 EXPIRATION_DATE,
1694 WORKSHEET_NAME,
1695 WORKSHEET_SEQUENCE_NUMBER
1696 )
1697 VALUES
1698 (
1699 l_interface_type,
1700 fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
1701 p_batch_id,
1702 s_response_recvd_row,
1703 'PON_AUCTS_BAD_SURROG_1' || l_suffix,
1704 p_user_id,
1705 sysdate,
1706 p_user_id,
1707 sysdate,
1708 l_loginid,
1709 p_request_id,
1710 '',
1711 'TXT',
1712 s_auction_header_id,
1713 s_bid_number,
1714 g_exp_date,
1715 s_header_worksheet_name,
1716 l_header_worksheet_sequence
1717 )
1718 --5
1719 WHEN s_surrogate_bid_flag = 'Y'
1720 AND s_response_recvd_time IS NOT NULL
1721 AND ((s_response_recvd_time > sysdate)
1722 OR (s_open_bidding_date IS NOT NULL AND s_response_recvd_time < s_open_bidding_date)
1723 OR (s_response_recvd_time > s_close_bidding_date)) THEN
1724 INTO pon_interface_errors
1725 (INTERFACE_TYPE,
1726 COLUMN_NAME,
1727 BATCH_ID,
1728 INTERFACE_LINE_ID,
1729 ERROR_MESSAGE_NAME,
1730 CREATED_BY,
1731 CREATION_DATE,
1732 LAST_UPDATED_BY,
1733 LAST_UPDATE_DATE,
1734 LAST_UPDATE_LOGIN,
1735 REQUEST_ID,
1736 ERROR_VALUE_DATE,
1737 ERROR_VALUE_DATATYPE,
1738 AUCTION_HEADER_ID,
1739 BID_NUMBER,
1740 EXPIRATION_DATE,
1741 WORKSHEET_NAME,
1742 WORKSHEET_SEQUENCE_NUMBER
1743 )
1744 VALUES
1745 (
1746 l_interface_type,
1747 fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
1748 p_batch_id,
1749 s_response_recvd_row,
1750 'PON_AUCTS_BAD_SURROG_2' || l_suffix,
1751 p_user_id,
1752 sysdate,
1753 p_user_id,
1754 sysdate,
1755 l_loginid,
1756 p_request_id,
1757 s_response_recvd_time,
1758 'TIM',
1759 s_auction_header_id,
1760 s_bid_number,
1761 g_exp_date,
1762 s_header_worksheet_name,
1763 l_header_worksheet_sequence
1764 )
1765 --6
1766 WHEN s_surrogate_bid_flag = 'Y'
1767 AND s_old_response_recvd_time IS NOT NULL
1768 AND s_response_recvd_time IS NOT NULL
1769 AND s_response_recvd_time < s_old_response_recvd_time THEN
1770 INTO pon_interface_errors
1771 (INTERFACE_TYPE,
1772 COLUMN_NAME,
1773 BATCH_ID,
1774 INTERFACE_LINE_ID,
1775 ERROR_MESSAGE_NAME,
1776 CREATED_BY,
1777 CREATION_DATE,
1778 LAST_UPDATED_BY,
1779 LAST_UPDATE_DATE,
1780 LAST_UPDATE_LOGIN,
1781 REQUEST_ID,
1782 ERROR_VALUE_DATE,
1783 ERROR_VALUE_DATATYPE,
1784 AUCTION_HEADER_ID,
1785 BID_NUMBER,
1786 EXPIRATION_DATE,
1787 WORKSHEET_NAME,
1788 WORKSHEET_SEQUENCE_NUMBER
1789 )
1790 VALUES
1791 (
1792 l_interface_type,
1793 fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
1794 p_batch_id,
1795 s_response_recvd_row,
1796 'PON_AUCTS_BAD_SURROG_3' || l_suffix,
1797 p_user_id,
1798 sysdate,
1799 p_user_id,
1800 sysdate,
1801 l_loginid,
1802 p_request_id,
1803 s_response_recvd_time,
1804 'TIM',
1805 s_auction_header_id,
1806 s_bid_number,
1807 g_exp_date,
1808 s_header_worksheet_name,
1809 l_header_worksheet_sequence
1810 )
1811 --7
1812 WHEN s_bid_valid_until IS NOT NULL
1813 AND s_bid_valid_until < s_close_bidding_date THEN
1814 INTO pon_interface_errors
1815 (INTERFACE_TYPE,
1816 COLUMN_NAME,
1817 BATCH_ID,
1818 INTERFACE_LINE_ID,
1819 ERROR_MESSAGE_NAME,
1820 CREATED_BY,
1821 CREATION_DATE,
1822 LAST_UPDATED_BY,
1823 LAST_UPDATE_DATE,
1824 LAST_UPDATE_LOGIN,
1825 REQUEST_ID,
1826 ERROR_VALUE_DATE,
1827 ERROR_VALUE_DATATYPE,
1828 AUCTION_HEADER_ID,
1829 BID_NUMBER,
1830 EXPIRATION_DATE,
1831 WORKSHEET_NAME,
1832 WORKSHEET_SEQUENCE_NUMBER
1833 )
1834 VALUES
1835 (
1836 l_interface_type,
1837 fnd_message.get_string('PON', 'PON_AUC_BID_VALID_UNTIL' ||l_suffix),
1838 p_batch_id,
1839 s_reference_number_row,
1840 'PON_AUCTS_BAD_BID_CLOSE' || l_suffix,
1841 p_user_id,
1842 sysdate,
1843 p_user_id,
1844 sysdate,
1845 l_loginid,
1846 p_request_id,
1847 s_bid_valid_until,
1848 'DAT',
1849 s_auction_header_id,
1850 s_bid_number,
1851 g_exp_date,
1852 s_header_worksheet_name,
1853 l_header_worksheet_sequence
1854 )
1855 SELECT
1856 pbhi.auction_header_id s_auction_header_id,
1857 pbhi.bid_number s_bid_number,
1858 pbhi.BID_EXPIRATION_DATE s_bid_valid_until,
1859 pbhi.SURROG_BID_RECEIPT_DATE s_response_recvd_time,
1860 pbhi.MIN_BID_CHANGE s_min_bid_change,
1861 pbhi.PROXY_BID_ROW s_proxy_bid_row,
1862 pbhi.REFERENCE_NUMBER_ROW s_reference_number_row,
1863 (pbhi.REFERENCE_NUMBER_ROW -1) s_response_recvd_row,
1864 pbhi.HEADER_WORKSHEET_NAME s_header_worksheet_name,
1865 pbhi.LINES_WORKSHEET_NAME s_lines_worksheet_name,
1866 nvl(pah.min_bid_change_type,'AMOUNT') s_min_bid_change_type,
1867 pah.min_bid_decrement s_auc_min_bid_decrement,
1868 pah.open_bidding_date s_open_bidding_date,
1869 pah.close_bidding_date s_close_bidding_date,
1870 pbh.old_surrog_bid_receipt_date s_old_response_recvd_time,
1871 pbh.surrog_bid_flag s_surrogate_bid_flag,
1872 pbh.number_price_decimals s_bid_price_precision
1873 FROM PON_BID_HEADERS_INTERFACE pbhi,
1874 PON_AUCTION_HEADERS_ALL pah,
1875 PON_BID_HEADERS pbh
1876 WHERE pbhi.batch_id = p_batch_id
1877 AND pbhi.bid_number = p_bid_number
1878 AND pbh.bid_number = pbhi.bid_number
1879 AND pah.auction_header_id = pbh.auction_header_id;
1880
1881 END VALIDATE_HEADER;
1882
1883 PROCEDURE validate_xml_price_breaks
1884 (
1885 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1886 p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1887 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1888 p_request_id IN pon_bid_item_prices.request_id%TYPE,
1889 p_user_id IN pon_interface_errors.created_by%TYPE
1890 )
1891 IS
1892 l_interface_type pon_interface_errors.interface_type%TYPE;
1893
1894 BEGIN
1895 l_interface_type := 'BIDBYSPREADSHEET';
1896 INSERT ALL
1897
1898 -- Price Type can not be null. This secnario only possible for Xml Spreadsheet, which is not exist
1899 -- for online case.
1900 WHEN s_price_type is null THEN
1901 INTO pon_interface_errors
1902 (INTERFACE_TYPE,
1903 TABLE_NAME,
1904 COLUMN_NAME,
1905 BATCH_ID,
1906 INTERFACE_LINE_ID,
1907 ERROR_MESSAGE_NAME,
1908 CREATED_BY,
1909 CREATION_DATE,
1910 LAST_UPDATED_BY,
1911 LAST_UPDATE_DATE,
1912 REQUEST_ID,
1913 ENTITY_TYPE,
1914 ENTITY_ATTR_NAME,
1915 ERROR_VALUE_NUMBER,
1916 ERROR_VALUE_DATATYPE,
1917 AUCTION_HEADER_ID,
1918 BID_NUMBER,
1919 LINE_NUMBER,
1920 SHIPMENT_NUMBER,
1921 EXPIRATION_DATE,
1922 TOKEN1_NAME,
1923 TOKEN1_VALUE,
1924 WORKSHEET_NAME,
1925 WORKSHEET_SEQUENCE_NUMBER,
1926 ENTITY_MESSAGE_CODE)
1927 VALUES
1928 (l_interface_type,
1929 'PON_BID_SHIPMENTS',
1930 fnd_message.get_string('PON', 'PON_BID_PRICE_OR_DISCOUNT'),
1931 p_batch_id,
1932 s_interface_line_id,
1933 'PON_FIELD_MUST_BE_ENTERED',
1934 p_user_id,
1935 sysdate,
1936 p_user_id,
1937 sysdate,
1938 p_request_id,
1939 'BID_PBS',
1940 'PriceType',
1941 s_price_type,
1942 'TXT',
1943 p_auction_header_id,
1944 p_bid_number,
1945 s_line_number,
1946 s_shipment_number,
1947 g_exp_date,
1948 'LINENUMBER',
1949 s_document_disp_line_number,
1950 s_worksheet_name,
1951 s_worksheet_seq_num,
1952 s_entity_name)
1953 select bshi.price_type s_price_type,
1954 bshi.line_number s_line_number,
1955 bshi.bid_shipment_number s_shipment_number,
1956 bshi.interface_line_id s_interface_line_id,
1957 bipi.document_disp_line_number s_document_disp_line_number,
1958 bipi.worksheet_name s_worksheet_name,
1959 bipi.worksheet_sequence_number s_worksheet_seq_num,
1960 'PON_AUCTS_PRICE_BREAKS' s_entity_name
1961 from
1962 pon_bid_item_prices_interface bipi,
1963 pon_bid_shipments_int bshi
1964 where bshi.batch_id = p_batch_id
1965 and bshi.bid_number = p_bid_number
1966 and bshi.action in (g_pb_required, g_pb_optional, g_pb_new)
1967 and bshi.batch_id = bipi.batch_id
1968 and bshi.bid_number = bipi.bid_number
1969 and bshi.line_number = bipi.line_number;
1970
1971 END validate_xml_price_breaks;
1972
1973
1974
1975 PROCEDURE VALIDATE_XML
1976 (
1977 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1978 p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1979 p_bid_number IN pon_bid_headers.bid_number%TYPE,
1980 p_request_id IN pon_bid_headers.request_id%TYPE,
1981 p_user_id IN pon_bid_item_prices.last_updated_by%TYPE,
1982 p_suffix IN VARCHAR2,
1983 p_has_pe IN pon_auction_item_prices_all.HAS_PRICE_ELEMENTS_FLAG%TYPE,
1984 l_attr_enabled_flag IN pon_auction_headers_all.line_attribute_enabled_flag%TYPE,
1985 l_req_enabled_flag IN pon_auction_headers_all.hdr_attribute_enabled_flag%TYPE,
1986 l_has_hdr_attr_flag IN pon_auction_headers_all.has_hdr_attr_flag%TYPE,
1987 p_progress_payment_type IN pon_auction_headers_all.progress_payment_type%TYPE,
1988 p_blanket IN varchar2,
1989 p_price_tiers_indicator IN pon_auction_headers_all.PRICE_TIERS_INDICATOR%type
1990 ) IS
1991 BEGIN
1992
1993 --Validate header fields
1994 VALIDATE_HEADER(p_batch_id, p_auction_header_id, p_bid_number, p_request_id, p_user_id);
1995
1996 IF (l_req_enabled_flag = 'Y' and l_has_hdr_attr_flag = 'Y') THEN
1997
1998 -- Validate bid values against datatype
1999 validate_xml_req_values(p_auction_header_id, p_bid_number, p_user_id, p_suffix, p_batch_id, p_request_id);
2000
2001 END IF;
2002
2003
2004 --Validate payments fields
2005 IF p_progress_payment_type <> 'NONE' THEN
2006 PON_VALIDATE_PAYMENTS_INT.VALIDATE_RESPONSE(PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, p_batch_Id, p_bid_number, p_auction_header_id, p_request_id);
2007 END IF;
2008
2009 IF (l_attr_enabled_flag = 'Y') THEN
2010
2011 -- Validate bid values against datatype
2012 validate_xml_attr_values(p_auction_header_id, p_bid_number, p_user_id, p_suffix, p_batch_id, p_request_id);
2013
2014 END IF;
2015
2016 IF (p_blanket = 'Y' and p_price_tiers_indicator = g_pt_indicator_pricebreak) THEN
2017 validate_xml_price_breaks(p_batch_id, p_auction_header_id, p_bid_number, p_request_id, p_user_id);
2018 END IF;
2019 --Continue other entity validations if any
2020
2021 /*
2022 -- commented out validation for supplier cost factors
2023 -- need to think further whether such a situation will arise
2024 -- in case of XML upload as the workbook will be locked
2025 --1. validate price elements or cost factors
2026 -- Report an error if not all SUPPLIER pf's on a line were found
2027 -- Error is reported per line. Price factor errors reported below.
2028 -- NOTE: this error check is performed before that for BUYER pf's
2029 IF (p_has_pe = 'Y') THEN
2030
2031 INSERT INTO pon_interface_errors
2032 (INTERFACE_TYPE,
2033 COLUMN_NAME,
2034 BATCH_ID,
2035 INTERFACE_LINE_ID,
2036 ERROR_MESSAGE_NAME,
2037 CREATED_BY,
2038 CREATION_DATE,
2039 LAST_UPDATED_BY,
2040 LAST_UPDATE_DATE,
2041 REQUEST_ID,
2042 ERROR_VALUE_DATATYPE,
2043 AUCTION_HEADER_ID,
2044 BID_NUMBER,
2045 LINE_NUMBER,
2046 EXPIRATION_DATE,
2047 WORKSHEET_NAME,
2048 WORKSHEET_SEQUENCE_NUMBER)
2049 (SELECT
2050 'BIDBYSPREADSHEET',
2051 fnd_message.get_string('PON', 'PON_AUC_PRICE_ELEMENT'),
2052 p_batch_id,
2053 bpei.interface_line_id,
2054 'PON_AUC_NOT_ALL_BID_PE',
2055 p_user_id,
2056 sysdate,
2057 p_user_id,
2058 sysdate,
2059 p_request_id,
2060 'TXT',
2061 bli.auction_header_id,
2062 bli.bid_number,
2063 bli.line_number,
2064 g_exp_date,
2065 bpei.worksheet_name,
2066 bpei.worksheet_sequence_number
2067 FROM pon_bid_item_prices_interface bli,
2068 pon_bid_item_prices bip,
2069 pon_bid_price_elements_int bpei
2070 WHERE bli.batch_id = p_batch_id
2071 AND bli.bid_number = bip.bid_number
2072 AND bli.line_number = bip.line_number
2073 AND bip.display_price_factors_flag = 'Y'
2074 AND bpei.batch_id = bli.batch_id
2075 AND bpei.line_number = bli.line_number
2076 AND EXISTS
2077 (SELECT bpfi.price_element_type_id
2078 FROM pon_bid_price_elements_int bpfi,
2079 pon_price_elements apf
2080 WHERE apf.auction_header_id = p_auction_header_id
2081 AND apf.line_number = bli.line_number
2082 AND apf.pf_type = 'SUPPLIER'
2083 AND bpfi.batch_id (+) = bli.batch_id
2084 AND bpfi.line_number (+) = apf.line_number
2085 AND bpfi.price_element_type_id (+) = apf.price_element_type_id
2086 AND rownum = 1));
2087
2088 END IF;
2089 */
2090
2091 END VALIDATE_XML;
2092
2093
2094 -- Changes on determine skipped line.
2095 -- 1) Change that is common for existing child entities: Joining key of interface_line_id should be changed to line ID
2096 -- 2) New logic should be added for new entities like Price Break, Price Break Level PD, Payment etc,
2097 PROCEDURE determine_xml_skipped_lines
2098 (
2099 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2100 p_full_qty IN VARCHAR2
2101 ) IS
2102 BEGIN
2103
2104 -- PRECONDITIONS:
2105 -- line_number are set for lines; it need not be set for children
2106
2107 -- Determine if any lines can be ignored
2108 UPDATE pon_bid_item_prices_interface bli
2109 SET bli.interface_line_id = g_skip_int
2110 WHERE bli.batch_id = p_batch_id
2111 AND EXISTS
2112 (SELECT 'Y'
2113 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
2114 WHERE bl.bid_number = bli.bid_number
2115 AND bl.line_number = bli.line_number
2116 AND al.auction_header_id = bl.auction_header_id
2117 AND al.line_number = bl.line_number
2118 AND (
2119 -- GROUPs ignored - do not skip a group
2120 -- al.group_type = 'GROUP'
2121
2122 -- proxy lines ignored
2123 --OR
2124 bl.copy_price_for_proxy_flag = 'Y'
2125
2126 -- empty lines ignored
2127 OR ( (al.price_disabled_flag = 'Y'
2128 OR bli.bid_currency_price IS null)
2129 AND (al.quantity_disabled_flag = 'Y'
2130 OR bli.quantity IS null
2131 OR (p_full_qty = 'Y' OR al.group_type = 'LOT_LINE'
2132 OR al.order_type_lookup_code = 'AMOUNT'))
2133 AND bli.note_to_auction_owner IS null
2134 AND bli.promised_date IS null
2135 -- If the values of these columns are NOT same as that in the transaction table
2136 -- (pon_bid_item_prices) then DO NOT skip.
2137 -- If the values are same as that in the transaction table then skip.
2138 -- You do not have to check about optional or required pay items.
2139 AND NVL(bli.recoupment_rate_percent, -9999) = NVL(bl.recoupment_rate_percent, -9999)
2140 AND NVL(bli.bid_curr_advance_amount, -9999) = NVL(bl.bid_curr_advance_amount, -9999)
2141 AND NVL(bli.bid_curr_max_retainage_amt, -9999) = NVL(bl.bid_curr_max_retainage_amt, -9999)
2142 AND NVL(bli.retainage_rate_percent, -9999) = NVL(bl.retainage_rate_percent,-9999)
2143 AND NVL(bli.progress_pymt_rate_percent, -9999) = NVL(bl.progress_pymt_rate_percent, -9999)
2144
2145 -- No price elements
2146 AND(bl.display_price_factors_flag = 'N'
2147 OR NOT EXISTS
2148 (SELECT bpfi.price_element_type_id
2149 FROM pon_bid_price_elements_int bpfi
2150 WHERE bpfi.batch_id = bli.batch_id
2151 AND bpfi.line_number = bli.line_number
2152 AND bpfi.bid_currency_value IS NOT null))
2153 -- No line / Price Break level price differentials
2154 AND ( NOT EXISTS
2155 (SELECT bpdi.sequence_number
2156 FROM pon_bid_price_differ_int bpdi
2157 WHERE bpdi.batch_id = bli.batch_id
2158 and bpdi.auction_line_number = bli.line_number
2159 --and bpdi.shipment_number = -1
2160 AND bpdi.multiplier IS NOT null))
2161 -- No Price Breaks - tricky part.
2162 -- According to ECO:
2163 -- If there is no value enterred for Price/Discount, user is intended to delete this PB.
2164 -- This action will over write all other actions like update, Currently it is also decided that
2165 -- the new PB will also driven by this Price/Discount column, if no value entered for this field
2166 -- just consider intended to delete it, or do not insert it at all.
2167 -- Thus, when determine skipped line, do not consider those that are intended to be deleted.
2168 -- For Price Tier, only consider Price, Price Discount is always null.
2169 AND ( NOT EXISTS
2170 (SELECT bshi.line_number
2171 FROM pon_bid_shipments_int bshi
2172 WHERE bshi.batch_id = bli.batch_id
2173 and bshi.line_number = bli.line_number
2174 and bshi.action in ( g_pb_required, g_pb_optional, g_pb_new )
2175 and (bshi.bid_currency_unit_price IS NOT null
2176 or bshi.price_discount IS NOT null )) )
2177 -- No attributes
2178 AND (al.has_attributes_flag = 'N'
2179 OR NOT EXISTS -- no attributes
2180 (SELECT bai.attribute_name
2181 FROM pon_bid_attr_values_interface bai
2182 WHERE bai.batch_id = bli.batch_id
2183 AND bai.line_number = bli.line_number
2184 AND bai.value IS NOT null))
2185 -- No payments
2186 AND (NOT EXISTS -- no payments
2187 (SELECT pbpi.interface_line_id
2188 FROM pon_bid_payments_interface pbpi
2189 WHERE pbpi.batch_id = bli.batch_id
2190 AND pbpi.document_disp_line_number = al.document_disp_line_number
2191 AND (pbpi.bid_currency_price IS NOT NULL
2192 OR pbpi.promised_date IS NOT NULL)
2193 ))
2194 )));
2195
2196 END determine_xml_skipped_lines;
2197
2198
2199
2200
2201 PROCEDURE remove_xml_skipped_lines
2202 (
2203 p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
2204 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2205 p_request_id IN pon_bid_headers.request_id%TYPE,
2206 p_userid IN pon_interface_errors.created_by%TYPE,
2207 p_full_qty IN VARCHAR2,
2208 p_buyer_user IN VARCHAR2,
2209 p_suffix IN VARCHAR2
2210 ) IS
2211 BEGIN
2212
2213 -- Determine if there are any lines to be skipped, mark them so.
2214 determine_xml_skipped_lines(p_batch_id, p_full_qty);
2215
2216 -- Mark closed lines as closed and report an error - only for suppliers
2217 -- do not validate any closed lines as we skip such closed lines
2218 -- while downloading the XML itself
2219 -- IF (p_buyer_user = 'N') THEN
2220 -- determine_xml_closed_lines(p_auc_header_id, p_batch_id, p_request_id, p_userid, p_suffix);
2221 -- end if;
2222
2223 -- delete bid attribute values from interface tables
2224 DELETE FROM pon_bid_attr_values_interface bai
2225 WHERE bai.batch_id = p_batch_id
2226 AND bai.line_number in (
2227 select bli.line_number
2228 from pon_bid_item_prices_interface bli
2229 where bli.batch_id = p_batch_id
2230 AND bli.interface_line_id = g_skip_int);
2231
2232 -- Delete from price elements interface table
2233 DELETE FROM pon_bid_price_elements_int bpfi
2234 WHERE bpfi.batch_id = p_batch_id
2235 AND bpfi.line_number in (
2236 select bli.line_number
2237 from pon_bid_item_prices_interface bli
2238 where bli.batch_id = p_batch_id
2239 AND bli.interface_line_id = g_skip_int);
2240
2241 -- Delete from price differentials interface table
2242 DELETE FROM pon_bid_price_differ_int bpdi
2243 WHERE bpdi.batch_id = p_batch_id
2244 AND bpdi.auction_line_number in (
2245 select bli.line_number
2246 from pon_bid_item_prices_interface bli
2247 where bli.batch_id = p_batch_id
2248 AND bli.interface_line_id = g_skip_int);
2249
2250 -- Delete from price breaks / price tiers interface table
2251 DELETE FROM pon_bid_shipments_int bshi
2252 WHERE bshi.batch_id = p_batch_id
2253 AND bshi.line_number in (
2254 select bli.line_number
2255 from pon_bid_item_prices_interface bli
2256 where bli.batch_id = p_batch_id
2257 AND bli.interface_line_id = g_skip_int);
2258
2259 -- Delete from payments interface table
2260 DELETE FROM pon_bid_payments_interface pbpi
2261 WHERE pbpi.batch_id = p_batch_id
2262 AND pbpi.document_disp_line_number in (
2263 select pai.document_disp_line_number
2264 from pon_bid_item_prices_interface bli,
2265 pon_auction_item_prices_all pai
2266 where bli.batch_id = p_batch_id
2267 AND bli.interface_line_id = g_skip_int
2268 AND pai.auction_header_id = bli.auction_header_id
2269 AND pai.line_number = bli.line_number);
2270
2271 -- Delete all bid lines from interface table
2272 DELETE FROM pon_bid_item_prices_interface bli
2273 WHERE bli.batch_id = p_batch_id
2274 AND bli.interface_line_id = g_skip_int;
2275
2276
2277 END remove_xml_skipped_lines;
2278
2279 PROCEDURE validate_xml_req_values
2280 (
2281 p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2282 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2283 p_user_id IN pon_interface_errors.created_by%TYPE,
2284 p_suffix IN VARCHAR2,
2285 p_batch_id IN pon_interface_errors.batch_id%TYPE,
2286 p_request_id IN pon_interface_errors.request_id%TYPE
2287 ) IS
2288 -- Define table definitions
2289 TYPE lineNumberTable IS TABLE of pon_auction_item_prices_all.line_number%TYPE;
2290 TYPE intLineTable IS TABLE of pon_bid_attr_values_interface.interface_line_id%TYPE;
2291 TYPE attrNameTable IS TABLE of pon_auction_attributes.attribute_name%TYPE;
2292 TYPE seqNumTable IS TABLE of pon_auction_attributes.sequence_number%TYPE;
2293 TYPE datatypeTable IS TABLE of pon_auction_attributes.datatype%TYPE;
2294 TYPE valueTable IS TABLE of pon_bid_attr_values_interface.value%TYPE;
2295 TYPE worksheetTable IS TABLE of pon_bid_attr_values_interface.worksheet_name%TYPE;
2296 TYPE worksheetSeqNumTable IS TABLE of pon_bid_attr_values_interface.worksheet_sequence_number%TYPE;
2297
2298 -- Local table variables
2299 l_line_numbers lineNumberTable;
2300 l_int_lines intLineTable;
2301 l_attr_names attrNameTable;
2302 l_sequence_numbers seqNumTable;
2303 l_datatypes datatypeTable;
2304 l_values valueTable;
2305 l_worksheet_names worksheetTable;
2306 l_worksheet_seq_numbers worksheetSeqNumTable;
2307
2308 -- Person party id
2309 l_person_party_id fnd_user.person_party_id%TYPE;
2310
2311 -- Timezone variables
2312 l_oex_timezone VARCHAR2(80);
2313 l_timezone VARCHAR2(80);
2314 l_is_valid_timezone VARCHAR2(1);
2315
2316 -- Error variables
2317 l_num_errors NUMBER;
2318 l_index NUMBER;
2319
2320
2321
2322 BEGIN
2323
2324 select person_party_id
2325 into l_person_party_id
2326 from fnd_user
2327 where user_id = p_user_id;
2328
2329 -- Set timezone variables
2330 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
2331
2332 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_person_party_id);
2333 if (l_timezone is null or l_timezone = '') then
2334 l_timezone := l_oex_timezone;
2335 end if;
2336
2337 if (PON_OEX_TIMEZONE_PKG.valid_zone(l_timezone) = 1) then
2338 l_is_valid_timezone := 'Y';
2339 else
2340 l_is_valid_timezone := 'N';
2341 end if;
2342
2343 -- Bulk collect all the interface requirements
2344 SELECT paa.line_number,
2345 pbai.interface_line_id,
2346 paa.attribute_name,
2347 paa.sequence_number,
2348 paa.datatype,
2349 pbai.value,
2350 pbai.worksheet_name,
2351 pbai.worksheet_sequence_number
2352 BULK COLLECT INTO
2353 l_line_numbers,
2354 l_int_lines,
2355 l_attr_names,
2356 l_sequence_numbers,
2357 l_datatypes,
2358 l_values,
2359 l_worksheet_names,
2360 l_worksheet_seq_numbers
2361 FROM
2362 pon_bid_attr_values_interface pbai,
2363 pon_auction_attributes paa
2364 WHERE
2365 pbai.auction_header_id = p_auction_header_id
2366 AND pbai.bid_number = p_bid_number
2367 AND pbai.batch_id = p_batch_id
2368 AND pbai.line_number = -1
2369 AND pbai.auction_header_id = paa.auction_header_id
2370 AND pbai.line_number = paa.line_number
2371 AND pbai.sequence_number = paa.sequence_number;
2372
2373 -- Attempt the datatype conversions
2374 FORALL i IN l_int_lines.FIRST..l_int_lines.LAST SAVE EXCEPTIONS
2375 UPDATE pon_bid_attr_values_interface pbai
2376 SET pbai.value = decode(l_datatypes(i),
2377 'TXT', l_values(i),
2378 'NUM', to_char(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,'''),
2379 decode(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''') - floor(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')),
2380 0,
2381 g_xml_char_no_prec_mask,
2382 g_xml_char_prec_mask),
2383 'NLS_NUMERIC_CHARACTERS=''.,'''),
2384 'DAT', decode(l_sequence_numbers(i), g_attr_need_by_date_seq,
2385 to_char(decode(l_is_valid_timezone, 'Y',
2386 PON_OEX_TIMEZONE_PKG.convert_time(to_date(l_values(i), g_xml_date_time_mask),
2387 l_timezone, l_oex_timezone),
2388 to_date(l_values(i), g_xml_date_time_mask)),
2389 g_pon_date_time_mask),
2390 to_char(to_date(l_values(i), g_xml_date_mask), g_pon_date_mask)),
2391 'URL', l_values(i))
2392 WHERE pbai.auction_header_id = p_auction_header_id
2393 AND pbai.bid_number = p_bid_number
2394 AND pbai.batch_id = p_batch_id
2395 AND pbai.line_number = l_line_numbers(i)
2396 AND pbai.sequence_number = l_sequence_numbers(i);
2397
2398 -- NOTE: calling procedure should purge invalid requirements
2399
2400 EXCEPTION
2401
2402 WHEN OTHERS THEN
2403 l_num_errors := SQL%BULK_EXCEPTIONS.COUNT;
2404
2405 -- Insert errors for each erroneous requirement
2406 FOR i IN 1..l_num_errors LOOP
2407
2408 l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
2409
2410 INSERT INTO pon_interface_errors
2411 (INTERFACE_TYPE,
2412 COLUMN_NAME,
2413 TABLE_NAME,
2414 BATCH_ID,
2415 INTERFACE_LINE_ID,
2416 ERROR_MESSAGE_NAME,
2417 CREATED_BY,
2418 CREATION_DATE,
2419 LAST_UPDATED_BY,
2420 LAST_UPDATE_DATE,
2421 REQUEST_ID,
2422 ERROR_VALUE,
2423 ERROR_VALUE_DATATYPE,
2424 AUCTION_HEADER_ID,
2425 BID_NUMBER,
2426 LINE_NUMBER,
2427 EXPIRATION_DATE,
2428 WORKSHEET_NAME,
2429 WORKSHEET_SEQUENCE_NUMBER,
2430 ENTITY_MESSAGE_CODE)
2431 VALUES
2432 ('BIDBYSPREADSHEET',
2433 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
2434 'PON_BID_ATTR_VALUES',
2435 p_batch_id,
2436 l_int_lines(l_index),
2437 'PON_AUCTS_ATTR_INVALID_VALUE' || p_suffix,
2438 p_user_id,
2439 sysdate,
2440 p_user_id,
2441 sysdate,
2442 p_request_id,
2443 l_values(l_index),
2444 'TXT',
2445 p_auction_header_id,
2446 p_bid_number,
2447 l_line_numbers(l_index),
2448 g_exp_date,
2449 l_worksheet_names(l_index),
2450 l_worksheet_seq_numbers(l_index),
2451 'PON_AUC_REQUIREMENTS');
2452
2453 UPDATE
2454 pon_bid_attr_values_interface pbai
2455 SET
2456 pbai.value = null
2457 WHERE
2458 pbai.auction_header_id = p_auction_header_id
2459 AND pbai.bid_number = p_bid_number
2460 AND pbai.batch_id = p_batch_id
2461 AND pbai.line_number = l_line_numbers(l_index)
2462 AND pbai.sequence_number = l_sequence_numbers(l_index);
2463
2464 END LOOP;
2465
2466 END validate_xml_req_values;
2467
2468
2469
2470 PROCEDURE validate_xml_attr_values
2471 (
2472 p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2473 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2474 p_user_id IN pon_interface_errors.created_by%TYPE,
2475 p_suffix IN VARCHAR2,
2476 p_batch_id IN pon_interface_errors.batch_id%TYPE,
2477 p_request_id IN pon_interface_errors.request_id%TYPE
2478 ) IS
2479 -- Define table definitions
2480 TYPE docDispLineNumTable IS TABLE of pon_auction_item_prices_all.document_disp_line_number%TYPE;
2481 TYPE lineNumberTable IS TABLE of pon_auction_item_prices_all.line_number%TYPE;
2482 TYPE intLineTable IS TABLE of pon_bid_attr_values_interface.interface_line_id%TYPE;
2483 TYPE attrNameTable IS TABLE of pon_auction_attributes.attribute_name%TYPE;
2484 TYPE seqNumTable IS TABLE of pon_auction_attributes.sequence_number%TYPE;
2485 TYPE datatypeTable IS TABLE of pon_auction_attributes.datatype%TYPE;
2486 TYPE valueTable IS TABLE of pon_bid_attr_values_interface.value%TYPE;
2487 TYPE worksheetTable IS TABLE of pon_bid_attr_values_interface.worksheet_name%TYPE;
2488 TYPE worksheetSeqNumTable IS TABLE of pon_bid_attr_values_interface.worksheet_sequence_number%TYPE;
2489
2490 -- Local table variables
2491 l_disp_line_numbers docDispLineNumTable;
2492 l_line_numbers lineNumberTable;
2493 l_int_lines intLineTable;
2494 l_attr_names attrNameTable;
2495 l_sequence_numbers seqNumTable;
2496 l_datatypes datatypeTable;
2497 l_values valueTable;
2498 l_worksheet_names worksheetTable;
2499 l_worksheet_seq_numbers worksheetSeqNumTable;
2500
2501 -- Person party id
2502 l_person_party_id fnd_user.person_party_id%TYPE;
2503
2504 -- Timezone variables
2505 l_oex_timezone VARCHAR2(80);
2506 l_timezone VARCHAR2(80);
2507 l_is_valid_timezone VARCHAR2(1);
2508
2509 -- Error variables
2510 l_num_errors NUMBER;
2511 l_index NUMBER;
2512
2513
2514
2515 BEGIN
2516
2517 select person_party_id
2518 into l_person_party_id
2519 from fnd_user
2520 where user_id = p_user_id;
2521
2522 -- Set timezone variables
2523 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
2524
2525 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(l_person_party_id);
2526 if (l_timezone is null or l_timezone = '') then
2527 l_timezone := l_oex_timezone;
2528 end if;
2529
2530 if (PON_OEX_TIMEZONE_PKG.valid_zone(l_timezone) = 1) then
2531 l_is_valid_timezone := 'Y';
2532 else
2533 l_is_valid_timezone := 'N';
2534 end if;
2535
2536 -- Bulk collect all the interface attributes
2537 SELECT
2538 paip.document_disp_line_number,
2539 paa.line_number,
2540 pbai.interface_line_id,
2541 paa.attribute_name,
2542 paa.sequence_number,
2543 paa.datatype,
2544 pbai.value,
2545 pbai.worksheet_name,
2546 pbai.worksheet_sequence_number
2547 BULK COLLECT INTO
2548 l_disp_line_numbers,
2549 l_line_numbers,
2550 l_int_lines,
2551 l_attr_names,
2552 l_sequence_numbers,
2553 l_datatypes,
2554 l_values,
2555 l_worksheet_names,
2556 l_worksheet_seq_numbers
2557 FROM
2558 pon_bid_attr_values_interface pbai,
2559 pon_auction_attributes paa,
2560 pon_auction_item_prices_all paip
2561 WHERE
2562 pbai.auction_header_id = p_auction_header_id
2563 AND pbai.bid_number = p_bid_number
2564 AND pbai.batch_id = p_batch_id
2565 AND pbai.line_number <> -1
2566 AND pbai.auction_header_id = paa.auction_header_id
2567 AND pbai.line_number = paa.line_number
2568 AND pbai.sequence_number = paa.sequence_number
2569 AND paa.auction_header_id = paip.auction_header_id
2570 AND paa.line_number = paip.line_number;
2571
2572 -- Attempt the datatype conversions
2573 FORALL i IN l_int_lines.FIRST..l_int_lines.LAST SAVE EXCEPTIONS
2574 UPDATE pon_bid_attr_values_interface pbai
2575 SET pbai.value = decode(l_datatypes(i),
2576 'TXT', l_values(i),
2577 'NUM', to_char(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,'''),
2578 decode(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''') - floor(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')),
2579 0,
2580 g_xml_char_no_prec_mask,
2581 g_xml_char_prec_mask),
2582 'NLS_NUMERIC_CHARACTERS=''.,'''),
2583 'DAT', decode(l_sequence_numbers(i), g_attr_need_by_date_seq,
2584 to_char(decode(l_is_valid_timezone, 'Y',
2585 PON_OEX_TIMEZONE_PKG.convert_time(to_date(l_values(i), g_xml_date_time_mask),
2586 l_timezone, l_oex_timezone),
2587 to_date(l_values(i), g_xml_date_time_mask)),
2588 g_pon_date_time_mask),
2589 to_char(to_date(l_values(i), g_xml_date_mask), g_pon_date_mask)),
2590 'URL', l_values(i))
2591 WHERE pbai.auction_header_id = p_auction_header_id
2592 AND pbai.bid_number = p_bid_number
2593 AND pbai.batch_id = p_batch_id
2594 AND pbai.line_number = l_line_numbers(i)
2595 AND pbai.sequence_number = l_sequence_numbers(i);
2596
2597 -- NOTE: calling procedure should purge invalid attributes
2598
2599 EXCEPTION
2600
2601 WHEN OTHERS THEN
2602 l_num_errors := SQL%BULK_EXCEPTIONS.COUNT;
2603
2604 -- Insert errors for each erroneous attribute
2605 FOR i IN 1..l_num_errors LOOP
2606
2607 l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
2608
2609 INSERT INTO pon_interface_errors
2610 (INTERFACE_TYPE,
2611 COLUMN_NAME,
2612 TABLE_NAME,
2613 BATCH_ID,
2614 INTERFACE_LINE_ID,
2615 ERROR_MESSAGE_NAME,
2616 CREATED_BY,
2617 CREATION_DATE,
2618 LAST_UPDATED_BY,
2619 LAST_UPDATE_DATE,
2620 REQUEST_ID,
2621 ERROR_VALUE,
2622 ERROR_VALUE_DATATYPE,
2623 AUCTION_HEADER_ID,
2624 BID_NUMBER,
2625 LINE_NUMBER,
2626 EXPIRATION_DATE,
2627 TOKEN1_NAME,
2628 TOKEN1_VALUE,
2629 TOKEN2_NAME,
2630 TOKEN2_VALUE,
2631 WORKSHEET_NAME,
2632 WORKSHEET_SEQUENCE_NUMBER,
2633 ENTITY_MESSAGE_CODE)
2634 VALUES
2635 ('BIDBYSPREADSHEET',
2636 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
2637 'PON_BID_ATTR_VALUES',
2638 p_batch_id,
2639 l_int_lines(l_index),
2640 'PON_AUC_ATTR_INVALID_TARGET' || p_suffix,
2641 p_user_id,
2642 sysdate,
2643 p_user_id,
2644 sysdate,
2645 p_request_id,
2646 l_values(l_index),
2647 'TXT',
2648 p_auction_header_id,
2649 p_bid_number,
2650 l_line_numbers(l_index),
2651 g_exp_date,
2652 'LINENUMBER',
2653 l_disp_line_numbers(l_index),
2654 'ATTRIBUTENAME',
2655 l_attr_names(l_index),
2656 l_worksheet_names(l_index),
2657 l_worksheet_seq_numbers(l_index),
2658 'PON_AUC_ATTRIBUTES');
2659
2660 UPDATE
2661 pon_bid_attr_values_interface pbai
2662 SET
2663 pbai.value = null
2664 WHERE
2665 pbai.auction_header_id = p_auction_header_id
2666 AND pbai.bid_number = p_bid_number
2667 AND pbai.batch_id = p_batch_id
2668 AND pbai.line_number = l_line_numbers(l_index)
2669 AND pbai.sequence_number = l_sequence_numbers(l_index);
2670
2671
2672 END LOOP;
2673
2674 END validate_xml_attr_values;
2675
2676
2677
2678
2679 PROCEDURE copy_shipment_interface_to_txn
2680 (
2681 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2682 p_bid_number IN pon_bid_headers.bid_number%TYPE,
2683 p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
2684 p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
2685 p_shipment_type IN pon_bid_shipments.shipment_type%TYPE
2686 )
2687 is
2688 l_module CONSTANT VARCHAR2(32) := 'copy_shipment_interface_to_txn';
2689 l_line_number_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2690 l_shipment_number_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2691 l_auction_header_id_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2692 l_interface_line_id_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2693 l_ship_to_organization_id_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2694 l_ship_to_location_id_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2695 l_quantity_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2696 l_price_type_col PON_NEG_COPY_DATATYPES_GRP.VARCHAR50_TYPE;
2697 l_bid_currency_unit_price_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2698 l_price_discount_col PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
2699 l_effective_start_date_col PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
2700 l_effective_end_date_col PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
2701
2702 l_counter number;
2703 l_curr_line_number number;
2704 BEGIN
2705
2706 --1. Populate Bid_shipment_number in Price Break Interface table
2707 -- Pupulate bid_shipment_number for those that were unknown ( fresh bid when there is no draft case ).
2708 update pon_bid_shipments_int bshi
2709 set bid_shipment_number =
2710 NVL( (select bsh.shipment_number
2711 from pon_bid_shipments bsh
2712 where bsh.bid_number = bshi.bid_number
2713 and bsh.line_number = bshi.line_number
2714 and bsh.auction_shipment_number = bshi.auction_shipment_number),
2715 bid_shipment_number)
2716 where bshi.batch_id = p_batch_id
2717 and bshi.bid_number =p_bid_number
2718 and bshi.action in ( g_pb_required, g_pb_optional, g_pb_delete);
2719
2720
2721 --2. Update Price Break / Price Tier Transaction based on bid_shipment_number
2722 --Notes. The calculation between bid_currency_unit_price and Discount relies on the line's bid_currency_unit_price,
2723 --thus this merge should happened after line entity has updated bid_currency_unit_price.
2724 MERGE INTO pon_bid_shipments bsh
2725 USING
2726 (SELECT
2727 bsi.action,
2728 bsi.bid_number,
2729 bsi.auction_header_id,
2730 bsi.line_number,
2731 bsi.batch_id,
2732 bsi.interface_line_id,
2733 bsi.bid_shipment_number,
2734 bsi.auction_shipment_number,
2735 bsi.ship_to_organization_id,
2736 bsi.ship_to_location_id,
2737 bsi.quantity,
2738 bsi.max_quantity,
2739 bsi.effective_start_date,
2740 bsi.effective_end_date,
2741 bsi.price_type,
2742 bsi.price_discount,
2743 bsi.bid_currency_unit_price,
2744 bip.bid_currency_unit_price item_price
2745 FROM pon_bid_shipments_int bsi,
2746 pon_bid_item_prices bip
2747 WHERE bsi.batch_id = p_batch_id
2748 and bsi.bid_number = p_bid_number
2749 and bsi.bid_number = bip.bid_number
2750 and bsi.auction_header_id = bip.auction_header_id
2751 and bsi.line_number = bip.line_number
2752 and bsi.action in ( g_pb_required, g_pb_optional, g_pb_new)
2753 ) bshi
2754 ON ( bsh.bid_number = bshi.bid_number
2755 and bsh.line_number = bshi.line_number
2756 and bsh.shipment_number = bshi.bid_shipment_number )
2757 WHEN MATCHED THEN
2758 UPDATE SET
2759 bsh.interface_line_id = bshi.interface_line_id,
2760 bsh.price_type = bshi.price_type,
2761 bsh.ship_to_organization_id = decode ( bshi.action, g_pb_required, bsh.ship_to_organization_id, --No changes for required PB
2762 bshi.ship_to_organization_id),
2763 bsh.ship_to_location_id = decode ( bshi.action, g_pb_required, bsh.ship_to_location_id, --No changes for Required PB
2764 bshi.ship_to_location_id),
2765 bsh.quantity = decode ( bshi.action, g_pb_required, bsh.quantity, -- No changes for Required PB
2766 bshi.quantity),
2767 bsh.max_quantity = bshi.max_quantity,
2768 bsh.effective_start_date = decode ( bshi.action, g_pb_required, bsh.effective_start_date, -- No changes for Required PB
2769 bshi.effective_start_date),
2770 bsh.effective_end_date = decode ( bshi.action, g_pb_required, bsh.effective_end_date, -- No changes for Required PB
2771 bshi.effective_end_date),
2772
2773 bsh.bid_currency_unit_price =
2774 NVL2(bshi.item_price,
2775 -- If item_price is not null,
2776 -- if it is PRICE_DISCOUNT type, caculate the bid_currency_unit
2777 -- price based on the item_price and price_discount
2778 -- and round it up based on bid currency precision.
2779 decode(bshi.price_type, 'PRICE DISCOUNT',
2780 nvl2(bshi.price_discount,
2781 round(bshi.item_price*(1-bshi.price_discount/100),
2782 P_bid_currency_precision),
2783 null),
2784 -- if it is PRICE type, copy bid_currency_unit_price
2785 -- directly from interface table to transaction table
2786 'PRICE',
2787 bshi.bid_currency_unit_price,
2788 null
2789 ),
2790 -- if item_price is null,
2791 -- if the price type is price_discount, then set bid_currency_unit_price as null
2792 -- else if the price type is price, then set price_discount as null
2793 -- else just copy as is. -- Notes, it is possible that hte price_type is null for Xml Spreadsheet.
2794 decode(bshi.price_type,'PRICE DISCOUNT',
2795 null,
2796 'PRICE',
2797 bshi.bid_currency_unit_price,
2798 null
2799 )
2800 ),
2801 bsh.price_discount=
2802 decode(p_shipment_type, g_shipment_type_quantitybased, null,
2803 NVL2(bshi.item_price,
2804 -- If item Price is not null
2805 -- if it is "DISCOUNT" type, copy discount from interface to transaction table
2806 decode(bshi.price_type, 'PRICE DISCOUNT',
2807 bshi.price_discount,
2808 -- if it is "PRICE" type, and
2809 -- item_Price != 0 and Bid_currency_unit_price < item_price
2810 -- sets the price discount to 1-(bid_currency_unit_price/item_price)
2811 'PRICE',
2812 nvl2(bshi.bid_currency_unit_price,
2813 case when ( bshi.bid_currency_unit_price>=bshi.item_price)
2814 then null
2815 when ( bshi.bid_currency_unit_price<>0)
2816 then (1- bshi.bid_currency_unit_price/bshi.item_price)*100
2817 else null
2818 end,
2819 null
2820 ),
2821 bshi.price_discount
2822 ),
2823 -- If item_price is null, just copy discount as is, set Price as null.
2824 decode(bshi.price_type,'PRICE',
2825 null,
2826 'PRICE DISCOUNT',
2827 bshi.price_discount,
2828 null)
2829 )
2830 ),
2831 bsh.last_update_date = sysdate,
2832 bsh.last_updated_by = P_userid
2833 -- 3. Insert new rows if not match for new PBs
2834 WHEN NOT MATCHED THEN
2835 INSERT
2836 ( bid_number,
2837 line_number,
2838 shipment_number,
2839 auction_header_id,
2840 auction_line_number,
2841 auction_shipment_number,
2842 shipment_type,
2843 ship_to_organization_id,
2844 ship_to_location_id,
2845 quantity,
2846 max_quantity,
2847 price_type,
2848 bid_currency_unit_price,
2849 price_discount,
2850 effective_start_date,
2851 effective_end_date,
2852 creation_date,
2853 created_by,
2854 last_update_date,
2855 last_updated_by,
2856 has_price_differentials_flag,
2857 interface_line_id )
2858 values
2859 (
2860 p_bid_number,
2861 bshi.line_number,
2862 bshi.bid_shipment_number,
2863 bshi.auction_header_id,
2864 bshi.line_number,
2865 null, -- set auction_shipment_number as null since it is Supplier owned PB
2866 p_shipment_type,
2867 bshi.ship_to_organization_id,
2868 bshi.ship_to_location_id,
2869 bshi.quantity,
2870 bshi.max_quantity,
2871 bshi.price_type,
2872 NVL2(bshi.item_price,
2873 -- If item_price is not null,
2874 -- if it is PRICE_DISCOUNT type, caculate the bid_currency_unit
2875 -- price based on the item_price and price_discount
2876 -- and round it up based on bid currency precision.
2877 decode(bshi.price_type, 'PRICE DISCOUNT',
2878 nvl2(bshi.price_discount,
2879 round(bshi.item_price*(1-bshi.price_discount/100),
2880 P_bid_currency_precision),
2881 null),
2882 -- if it is PRICE type, copy bid_currency_unit_price
2883 -- directly from interface table to transaction table
2884 'PRICE',
2885 bshi.bid_currency_unit_price,
2886 null
2887 ),
2888 -- if item_price is null,
2889 -- if the price type is price_discount, then set bid_currency_unit_price as null
2890 -- else if the price type is price, then set price_discount as null
2891 -- else just copy as is. -- Notes, it is possible that hte price_type is null for Xml Spreadsheet.
2892 decode(bshi.price_type,'PRICE DISCOUNT',
2893 null,
2894 'PRICE',
2895 bshi.bid_currency_unit_price,
2896 null
2897 )
2898 ),
2899 decode(p_shipment_type, g_shipment_type_quantitybased, null,
2900 NVL2(bshi.item_price,
2901 -- If item Price is not null
2902 -- if it is "DISCOUNT" type, copy discount from interface to transaction table
2903 decode(bshi.price_type, 'PRICE DISCOUNT',
2904 bshi.price_discount,
2905 -- if it is "PRICE" type, and
2906 -- item_Price != 0 and Bid_currency_unit_price < item_price
2907 -- sets the price discount to 1-(bid_currency_unit_price/item_price)
2908 'PRICE',
2909 nvl2(bshi.bid_currency_unit_price,
2910 case when ( bshi.bid_currency_unit_price>=bshi.item_price)
2911 then null
2912 when ( bshi.bid_currency_unit_price<>0)
2913 then (1- bshi.bid_currency_unit_price/bshi.item_price)*100
2914 else null
2915 end,
2916 null
2917 ),
2918 bshi.price_discount
2919 ),
2920 -- If item_price is null, just copy discount as is, set Price as null.
2921 decode(bshi.price_type,'PRICE',
2922 null,
2923 'PRICE DISCOUNT',
2924 bshi.price_discount,
2925 null)
2926 )
2927 ),
2928 bshi.effective_start_date,
2929 bshi.effective_end_date,
2930 sysdate,
2931 p_userId,
2932 sysdate,
2933 p_userId,
2934 'N',
2935 bshi.interface_line_id)
2936 where
2937 bshi.action = g_pb_new;
2938
2939
2940 --4. Delete data from transaction table for those Price Breaks that are flagged to be deleted based on
2941 delete from pon_bid_shipments bsh
2942 where bsh.bid_number = p_bid_number
2943 and exists
2944 (
2945 select 1
2946 from pon_bid_shipments_int bsi
2947 where bsi.batch_id= p_batch_id
2948 and bsi.bid_number = P_BID_NUMBER
2949 and bsi.action = g_pb_delete
2950 and bsi.bid_number = bsh.bid_number
2951 and bsi.line_number = bsh.line_number
2952 and bsi.bid_shipment_number = bsh.shipment_number
2953 and rownum = 1 );
2954
2955 --Step 5 and 6 are only for price break.
2956 IF (p_shipment_type = g_shipment_type_pricebreak) THEN
2957
2958 --5 Mark Supplier owned Price Break
2959 -- 5.1 Mark Supplier owned Price Break set auction_shipment_number="" and has_price_differentials_flag='N' by comparing the 5 columns in PON_BID_SHIPMENTS with PON_AUCTION_SHIPMENTS_ALL
2960 -- Notes, if need to use g_pb_optionals_updated flag to determine skipped line, move this part before determin_xml_skipped_line
2961 update
2962 pon_bid_shipments_int bsi
2963 set bsi.action= g_pb_optional_updated
2964 where bsi.batch_id = p_batch_id
2965 and bsi.BID_NUMBER = p_bid_number
2966 and bsi.action = g_pb_optional
2967 and bsi.auction_shipment_number is not null
2968 and exists(
2969 select 1
2970 from
2971 pon_auction_shipments_all ash
2972 where
2973 ash.auction_header_id = bsi.auction_header_id
2974 and ash.line_number = bsi.line_number
2975 and ash.shipment_number = bsi.auction_shipment_number
2976 and ( nvl(ash.ship_to_organization_id, g_null_int) <> nvl(bsi.ship_to_organization_id, g_null_int)
2977 or
2978 nvl(ash.ship_to_location_id,g_null_int) <> nvl(bsi.ship_to_location_id,g_null_int)
2979 or
2980 nvl(ash.quantity,g_null_int) <> nvl(bsi.quantity,g_null_int)
2981 or
2982 nvl(ash.effective_start_date,sysdate) <> nvl(bsi.effective_start_date,sysdate)
2983 or
2984 nvl(ash.effective_end_date,sysdate) <> nvl(bsi.effective_end_date,sysdate)
2985 )
2986 and rownum = 1
2987 );
2988
2989 -- 5.2 Update shipment transaction table for supplier owned shipments.
2990 update
2991 pon_bid_shipments bsh
2992 set bsh.auction_shipment_number = null,
2993 bsh.has_price_differentials_flag = 'N'
2994 where bsh.bid_number = p_bid_number
2995 and exists
2996 ( select 1
2997 from
2998 pon_bid_shipments_int bshi
2999 where bshi.batch_id = p_batch_id
3000 and bshi.bid_number = p_bid_number
3001 and bshi.action = g_pb_optional_updated
3002 and bshi.auction_header_id = bsh.auction_header_id
3003 and bshi.bid_number = bsh.bid_number
3004 and bshi.line_number = bsh.line_number
3005 and bshi.bid_shipment_number = bsh.shipment_number );
3006
3007 --6. Remove Price Differentials from transaction table
3008 --Remove Price Differentials from PON_BID_PRICE_DIFFERENTIALS which associated supplier owned Price Break
3009 -- and deleted Price Break.
3010 delete from pon_bid_price_differentials bsd
3011 where
3012 bsd.shipment_number<>-1
3013 and bsd.bid_number=p_bid_number
3014 and bsd.line_number in
3015 (select bip.line_number
3016 from pon_bid_item_prices bip
3017 where bip.batch_id=p_batch_id
3018 and bip.bid_number=p_bid_number
3019 )
3020 and
3021 (bsd.bid_number, bsd.line_number, bsd.shipment_number)
3022 not in
3023 (
3024 select bsh.bid_number, bsh.line_number, bsh.shipment_number
3025 from pon_bid_shipments bsh,pon_bid_item_prices bip
3026 where bip.batch_id = p_batch_id
3027 and bip.bid_number = p_bid_number
3028 and bip.bid_number = bsh.bid_number
3029 and bip.line_number = bsh.line_number
3030 and (bsh.auction_shipment_number is not null
3031 and bsh.has_price_differentials_flag='Y')
3032 );
3033 END IF; -- end of IF (p_shipment_type = g_shipment_type_pricebreak
3034
3035
3036
3037 END copy_shipment_interface_to_txn;
3038
3039
3040 PROCEDURE process_xml_spreadsheet_data
3041 (
3042 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
3043 p_bid_number IN pon_bid_headers.bid_number%TYPE,
3044 p_request_id IN pon_bid_headers.request_id%TYPE,
3045 p_user_id IN pon_interface_errors.created_by%TYPE,
3046 x_return_status OUT NOCOPY NUMBER,
3047 x_return_code OUT NOCOPY VARCHAR2
3048 ) IS
3049 l_auc_header_id pon_auction_headers_all.auction_header_id%TYPE;
3050
3051 l_header_disp_pf VARCHAR2(1);
3052 l_blanket VARCHAR2(1);
3053 l_mas VARCHAR2(1);
3054 l_full_qty VARCHAR2(1);
3055 l_auc_closed VARCHAR2(1);
3056 l_buyer_user VARCHAR2(1);
3057 l_supplier_user VARCHAR2(1);
3058 l_has_pe VARCHAR2(1);
3059 l_attr_enabled_flag VARCHAR2(1);
3060 l_req_enabled_flag VARCHAR2(1);
3061 l_has_hdr_attr_flag VARCHAR2(1);
3062 l_suffix VARCHAR2(2);
3063 l_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE;
3064 l_price_precision pon_bid_headers.number_price_decimals%TYPE;
3065 l_price_tiers_indicator pon_auction_headers_all.PRICE_TIERS_INDICATOR%TYPE;
3066 BEGIN
3067
3068 SELECT sysdate + g_exp_days_offset,
3069 ah.auction_header_id,
3070 bh.display_price_factors_flag,
3071 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
3072 decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
3073 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
3074 decode(ah.auction_status, 'AUCTION_CLOSED', 'Y', 'N'),
3075 nvl(ah.line_attribute_enabled_flag, 'N'),
3076 nvl(ah.hdr_attribute_enabled_flag, 'N'),
3077 nvl(ah.has_hdr_attr_flag, 'N'),
3078 nvl(ah.progress_payment_type,'NONE'),
3079 bh.number_price_decimals,
3080 bh.surrog_bid_flag,
3081 decode(bh.surrog_bid_flag, 'Y', 'N', 'Y'),
3082 nvl(bh.display_price_factors_flag, 'N'),
3083 ah.PRICE_TIERS_INDICATOR
3084 INTO g_exp_date,
3085 l_auc_header_id,
3086 l_header_disp_pf,
3087 l_blanket,
3088 l_mas,
3089 l_full_qty,
3090 l_auc_closed,
3091 l_attr_enabled_flag,
3092 l_req_enabled_flag,
3093 l_has_hdr_attr_flag,
3094 l_progress_payment_type,
3095 l_price_precision,
3096 l_buyer_user,
3097 l_supplier_user,
3098 l_has_pe,
3099 l_price_tiers_indicator
3100 FROM pon_bid_headers bh, pon_auction_headers_all ah
3101 WHERE bh.bid_number = p_bid_number
3102 AND ah.auction_header_id = bh.auction_header_id;
3103
3104 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.get_doctype_suffix(l_auc_header_id);
3105
3106 -- Determine if there are any invalid lines or lines to be skipped
3107 remove_xml_skipped_lines
3108 (l_auc_header_id,
3109 p_batch_id,
3110 p_request_id,
3111 p_user_id,
3112 l_full_qty,
3113 l_buyer_user,
3114 l_suffix);
3115
3116 validate_xml(
3117 p_batch_id,
3118 l_auc_header_id,
3119 p_bid_number,
3120 p_request_id,
3121 p_user_id,
3122 l_suffix,
3123 l_has_pe,
3124 l_attr_enabled_flag,
3125 l_req_enabled_flag,
3126 l_has_hdr_attr_flag,
3127 l_progress_payment_type,
3128 l_blanket,
3129 l_price_tiers_indicator);
3130
3131
3132 -- Default certain fields from the auction side
3133 -- essentially nullify the price columns when not applicable -> this need not be done
3134 -- for XML as we will make all such fields read-only
3135 -- Note to Rohit: please verify
3136 default_from_auction
3137 (p_batch_id,
3138 l_auc_header_id,
3139 p_bid_number,
3140 l_full_qty,
3141 l_blanket,
3142 l_auc_closed);
3143
3144
3145 copy_interface_to_txn_tables
3146 (p_batch_id,
3147 l_auc_header_id,
3148 p_bid_number,
3149 p_user_id,
3150 l_header_disp_pf,
3151 l_blanket,
3152 l_mas,
3153 l_progress_payment_type,
3154 PON_BID_VALIDATIONS_PKG.g_xml_upload_mode,
3155 l_price_precision,
3156 l_price_tiers_indicator);
3157
3158
3159 -- Update auction currency columns for the current batch
3160 PON_RESPONSE_PVT.recalculate_auc_curr_prices(p_bid_number, 'N', p_batch_id);
3161
3162 -- Update group amounts for the current batch
3163 -- NOTE: group amount is only calculated at the time of publish
3164 PON_RESPONSE_PVT.calculate_group_amounts(p_bid_number, l_supplier_user, 'N', p_batch_id);
3165
3166
3167 -- Validate the data once it has been copied to the transaction tables
3168 PON_BID_VALIDATIONS_PKG.validate_spreadsheet_upload
3169 (l_auc_header_id,
3170 p_bid_number,
3171 'BIDBYSPREADSHEET',
3172 PON_BID_VALIDATIONS_PKG.g_xml_upload_mode,
3173 p_user_id,
3174 p_batch_id,
3175 p_request_id,
3176 x_return_status,
3177 x_return_code);
3178
3179
3180 END process_xml_spreadsheet_data;
3181
3182
3183 END PON_RESPONSE_IMPORT_PKG;