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