[Home] [Help]
PACKAGE BODY: APPS.PON_BID_VALIDATIONS_PKG
Source
1 PACKAGE BODY PON_BID_VALIDATIONS_PKG AS
2 --$Header: PONBDVLB.pls 120.58.12010000.5 2009/02/03 07:38:20 haoyang ship $
3
4 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_module_prefix CONSTANT VARCHAR2(35) := 'pon.plsql.bidValidationsPkg.';
6
7 g_exp_date TIMESTAMP;
8 g_exp_days_offset CONSTANT NUMBER := 7;
9
10 g_null_int CONSTANT NUMBER := -9999;
11
12 g_number_mask CONSTANT VARCHAR2(255) := '9999999999999999999999999999999999999999999999D9999999999999999';
13
14 ----------------------------------------------------------------
15 -- Formats the number based on the precision pased. If the --
16 -- precision is passed as any then no formatting is done. --
17 ----------------------------------------------------------------
18 FUNCTION GET_MASK(p_precision in NUMBER) return VARCHAR2 is
19 l_mask varchar2(80);
20
21 BEGIN
22 -- precision is ANY
23 if (p_precision = 10000) then
24 l_mask := 'FM999G999G999G999G999G999G999G990D0999999999'; -- consider a big mask to accomodate big numbers
25 return l_mask;
26 elsif (p_precision = 0) then
27 -- For 0 precision we need to hide the decimal seperator
28 l_mask := 'FM999G999G999G999G999G999G999G999'; -- consider a big mask to accomodate big numbers
29 return l_mask;
30 else
31 l_mask := 'FM999G999G999G999G999G999G999G990D'; -- consider a big mask to accomodate big numbers
32 l_mask := rpad(l_mask, (length(l_mask) + p_precision), '0');
33 return l_mask;
34 end if;
35 END;
36
37 ----------------------------------------------------------------
38 -- Formats the price passed based on the format passed. --
39 -- If the price does have a decimal part then the decimal --
40 -- separator will not be displayed. If the price is less --
41 -- that 0 then 0 will be displayed before the decimal --
42 -- separator --
43 ----------------------------------------------------------------
44 FUNCTION FORMAT_PRICE
45 ( p_price in NUMBER,
46 p_format_mask in VARCHAR2,
47 p_precision IN NUMBER
48 ) return VARCHAR2 is
49 l_mask varchar2(80);
50 BEGIN
51 if (p_price is null) then
52 return null;
53 elsif ((ceil(p_price) - p_price) =0 and p_precision = 10000) then
54 -- if price does not have decimal seperator and precision is 'Any' then
55 -- the decimal will not be displayed
56 l_mask := 'FM999G999G999G999G999G999G999G999'; -- consider a big mask to accomodate big numbers
57 else
58 l_mask := p_format_mask; -- consider the original mask
59 end if;
60
61 return to_char(p_price,l_mask);
62
63 END;
64
65
66 -- ======================================================================
67 -- PROCEDURE: VALIDATE_PRICE_PRECISION PUBLIC
68 -- PARAMETERS:
69 -- p_number IN number to validate
70 -- p_precision IN desired precision
71 --
72 -- RETURN: T if number's precision is within p_precision decimals. F if not
73 --
74 -- COMMENT: determines if number's precision is withint p_precision decimals
75 -- ======================================================================
76 FUNCTION validate_price_precision
77 (
78 p_number IN NUMBER,
79 p_precision IN NUMBER
80 ) RETURN VARCHAR2 IS
81 BEGIN
82
83 IF (p_number IS null) THEN
84 RETURN 'T';
85 END IF;
86
87 IF p_precision = 10000
88 OR (MOD(MOD(ABS(p_number), 1) * POWER(10, p_precision), 1) = 0) THEN
89 RETURN 'T';
90 ELSE
91 RETURN 'F';
92 END IF;
93
94 END validate_price_precision;
95
96 -- ======================================================================
97 -- PROCEDURE: VALIDATE_CURRENCY_PRECISION PUBLIC
98 -- PARAMETERS:
99 -- p_number IN number to validate
100 -- p_precision IN desired precision
101 --
102 -- RETURN: T if number's precision is within p_precision decimals. F if not
103 --
104 -- COMMENT: determines if number's precision is withint p_precision decimals
105 -- ======================================================================
106 FUNCTION validate_currency_precision
107 (
108 p_number IN NUMBER,
109 p_precision IN NUMBER
110 ) RETURN VARCHAR2 IS
111 BEGIN
112
113 IF (p_number IS null) THEN
114 RETURN 'T';
115 END IF;
116
117 IF (MOD(MOD(ABS(p_number), 1) * POWER(10, p_precision), 1) = 0) THEN
118 RETURN 'T';
119 ELSE
120 RETURN 'F';
121 END IF;
122
123 END validate_currency_precision;
124
125
126
127
128 PROCEDURE populate_has_bid_changed_line
129 (
130 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
131 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
132 p_source_bid IN pon_bid_item_prices.bid_number%TYPE,
133 p_batch_id IN pon_bid_item_prices.batch_id%TYPE,
134 p_batch_start IN NUMBER,
135 p_batch_end IN NUMBER,
136 p_rebid_flag IN VARCHAR2,
137 p_blanket IN VARCHAR2,
138 p_use_batch_id IN VARCHAR2,
139 p_spreadsheet IN VARCHAR
140 ) IS
141 BEGIN
142
143 -- spreadsheet case:
144 -- Blindly set has_bid_flag to Y for all bidded lines in the spreadsheet
145 -- (any line with batch_id = p_batch_id will have a bid.)
146 -- If rebid, then is_changed line logic will be run, so initialize
147 -- it to 'N' for all lines
148 -- If not rebid, then we won't be running the is_changed_line logic below
149 -- so set the flag to 'Y' for all lines
150 --
151 -- online case:
152 -- for power bidding, don't mess with has_bid_flag.
153 -- since power bidding is always a rebid, we will always be
154 -- initializing all is_changed_line_flag to 'N' and then selectively
155 -- setting them to 'Y' with the is_changed_line logic below
156
157 UPDATE pon_bid_item_prices bl
158 SET bl.has_bid_flag = decode(p_use_batch_id, 'Y', 'Y', bl.has_bid_flag),
159 bl.is_changed_line_flag = decode(p_rebid_flag, 'N', 'Y', 'N')
160 WHERE bl.bid_number = p_bid_number
161 AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
162 OR (p_use_batch_id = 'N'
163 AND bl.line_number >= p_batch_start
164 AND bl.line_number <= p_batch_end));
165
166 -- no need to run the is_changed_line logic below
167 -- if we're doing spreadsheet upload and this is not a rebid
168 IF (p_use_batch_id = 'Y' AND p_rebid_flag = 'N') THEN
169 RETURN;
170 END IF;
171
172 UPDATE pon_bid_item_prices bl
173 SET bl.is_changed_line_flag = 'Y'
174 WHERE bl.bid_number = p_bid_number
175 AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
176 OR (p_use_batch_id = 'N'
177 AND bl.line_number >= p_batch_start
178 AND bl.line_number <= p_batch_end))
179 AND ((NVL(bl.bid_currency_unit_price, g_null_int) <> NVL(bl.old_bid_currency_unit_price, g_null_int)
180 OR NVL(bl.bid_currency_price, g_null_int) <> NVL(bl.old_bid_currency_price, g_null_int)
181 OR bl.proxy_bid_limit_price IS NOT NULL
182 AND NVL(bl.bid_currency_limit_price, g_null_int) <> NVL(bl.old_bid_currency_limit_price, g_null_int)
183 OR bl.promised_date IS NULL AND bl.old_promised_date IS NOT NULL
184 OR bl.promised_date IS NOT NULL AND bl.old_promised_date IS NULL
185 OR bl.promised_date <> bl.old_promised_date
186 OR NVL(bl.po_bid_min_rel_amount, g_null_int) <> NVL(bl.old_po_bid_min_rel_amount, g_null_int)
187 OR bl.note_to_auction_owner IS NULL AND bl.old_note_to_auction_owner IS NOT NULL
188 OR bl.note_to_auction_owner IS NOT NULL AND bl.old_note_to_auction_owner IS NULL
189 OR bl.note_to_auction_owner <> bl.old_note_to_auction_owner
190 OR bl.old_quantity IS NOT NULL AND NVL(bl.quantity, g_null_int) <> bl.old_quantity
191 OR NVL(bl.bid_curr_advance_amount, g_null_int) <> NVL(bl.old_bid_curr_advance_amount, g_null_int)
192 OR NVL(bl.recoupment_rate_percent, g_null_int) <> NVL(bl.old_recoupment_rate_percent, g_null_int)
193 OR NVL(bl.progress_pymt_rate_percent, g_null_int) <> NVL(bl.old_progress_pymt_rate_percent, g_null_int)
194 OR NVL(bl.retainage_rate_percent, g_null_int) <> NVL(bl.old_retainage_rate_percent, g_null_int)
195 OR NVL(bl.bid_curr_max_retainage_amt, g_null_int) <> NVL(bl.old_bid_curr_max_retainage_amt, g_null_int)
196 )
197
198 OR (bl.has_attributes_flag = 'Y'
199 AND EXISTS
200
201 -- Check attributes
202 (SELECT ba.line_number
203 FROM pon_bid_attribute_values ba
204 WHERE ba.bid_number = bl.bid_number
205 AND ba.line_number = bl.line_number
206 AND (ba.value IS NULL AND ba.old_value IS NOT NULL OR
207 ba.value IS NOT NULL AND ba.old_value IS NULL OR
208 ba.value <> ba.old_value)
209 AND rownum = 1))
210
211 OR EXISTS
212
213 -- Check price factors
214 (SELECT bpf.line_number
215 FROM pon_bid_price_elements bpf
216 WHERE bpf.bid_number = bl.bid_number
217 AND bpf.line_number = bl.line_number
218 AND bpf.pf_type = 'SUPPLIER'
219 AND NVL(bpf.bid_currency_value, g_null_int) <> NVL(old_bid_currency_value, g_null_int)
220 AND rownum = 1)
221 OR EXISTS
222
223 -- Check payments for xml spreadsheet upload case
224 (SELECT bps.bid_line_number
225 FROM pon_bid_payments_shipments bps
226 WHERE p_spreadsheet = g_xml_upload_mode
227 AND bps.bid_number = bl.bid_number
228 AND bps.bid_line_number = bl.line_number
229 AND (NVL(bps.payment_display_number, g_null_int) <> NVL(old_payment_display_number, g_null_int)
230 OR bps.payment_type_code IS NULL AND bps.old_payment_type_code IS NOT NULL
231 OR bps.payment_type_code IS NOT NULL AND bps.old_payment_type_code IS NULL
232 OR bps.payment_type_code <> bps.old_payment_type_code
233 OR bps.payment_description IS NULL AND bps.old_payment_description IS NOT NULL
234 OR bps.payment_description IS NOT NULL AND bps.old_payment_description IS NULL
235 OR bps.payment_description <> bps.old_payment_description
236 OR NVL(bps.quantity, g_null_int) <> NVL(old_quantity, g_null_int)
237 OR NVL(bps.uom_code, g_null_int) <> NVL(old_uom_code, g_null_int)
238 OR NVL(bps.bid_currency_price, g_null_int) <> NVL(old_bid_currency_price, g_null_int)
239 OR bps.promised_date IS NULL AND bps.old_promised_date IS NOT NULL
240 OR bps.promised_date IS NOT NULL AND bps.old_promised_date IS NULL
241 OR bps.promised_date <> bps.old_promised_date)
242 AND rownum = 1)
243
244 -- Check if any payments were deleted
245 OR (nvl(bl.old_no_of_payments,0) <> (select count(payment_display_number)
246 from pon_bid_payments_shipments bps
247 where bps.bid_number = bl.bid_number
248 and bps.bid_line_number = bl.line_number))
249
250 OR ( (bl.has_shipments_flag = 'Y' or bl.has_quantity_tiers = 'Y')
251 AND EXISTS
252
253 -- Check shipments
254 -- If auction_shipment_number is null then it is user defined
255 -- so we must check all possible values that can be changed
256 (SELECT bpb.line_number
257 FROM pon_bid_shipments bpb
258 WHERE bpb.bid_number = bl.bid_number
259 AND bpb.line_number = bl.line_number
260 AND (bpb.auction_shipment_number IS null
261 AND (NVL(bpb.ship_to_organization_id, g_null_int) <> NVL(bpb.old_ship_to_org_id, g_null_int)
262 OR NVL(bpb.ship_to_location_id, g_null_int) <> NVL(bpb.old_ship_to_loc_id, g_null_int)
263 OR bpb.effective_start_date IS NULL AND bpb.old_effective_start_date IS NOT NULL
264 OR bpb.effective_start_date IS NOT NULL AND bpb.old_effective_start_date IS NULL
265 OR bpb.effective_start_date <> bpb.old_effective_start_date
266 OR bpb.effective_end_date IS NULL AND bpb.old_effective_end_date IS NOT NULL
267 OR bpb.effective_end_date IS NOT NULL AND bpb.old_effective_end_date IS NULL
268 OR bpb.effective_end_date <> bpb.old_effective_end_date
269 OR NVL(bpb.quantity, g_null_int) <> NVL(bpb.old_quantity, g_null_int)
270 OR NVL(bpb.max_quantity, g_null_int) <> NVL(bpb.old_max_quantity, g_null_int)
271 OR bpb.price_type IS NULL AND bpb.old_price_type IS NOT NULL
272 OR bpb.price_type IS NOT NULL AND bpb.old_price_type IS NULL
273 OR bpb.price_type <> bpb.old_price_type)
274 OR bpb.price_type = 'PRICE' AND NVL(bpb.bid_currency_unit_price, g_null_int) <> NVL(bpb.old_bid_currency_unit_price, g_null_int)
275 OR bpb.price_type = 'PRICE DISCOUNT' AND NVL(bpb.price_discount, g_null_int) <> NVL(bpb.old_price_discount, g_null_int)
276 OR NVL(bpb.bid_currency_price, g_null_int) <> NVL(bpb.old_bid_currency_price, g_null_int))
277 AND rownum = 1))
278
279 OR (bl.has_price_differentials_flag = 'Y'
280 AND EXISTS
281
282 -- Check price differentials, including shipment price differentials
283 (SELECT bpd.line_number
284 FROM pon_bid_price_differentials bpd
285 WHERE bpd.bid_number = bl.bid_number
286 AND bpd.line_number = bl.line_number
287 AND NVL(bpd.multiplier, g_null_int) <> NVL(bpd.old_multiplier, g_null_int)
288 AND rownum = 1))
289 );
290
291
292 -- process groups
293 -- when a group line has been changed, change the group too.
294 UPDATE pon_bid_item_prices pbip
295 SET pbip.is_changed_line_flag = 'Y'
296 WHERE pbip.bid_number = p_bid_number
297 AND pbip.line_number IN
298 (SELECT al.parent_line_number
299 FROM pon_bid_item_prices bl,
300 pon_auction_item_prices_all al
301 WHERE bl.bid_number = p_bid_number
302 AND bl.auction_header_id = al.auction_header_id
303 AND bl.line_number = al.line_number
304 AND al.group_type = 'GROUP_LINE'
305 AND bl.is_changed_line_flag = 'Y'
306 AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
307 OR (p_use_batch_id = 'N'
308 AND bl.line_number >= p_batch_start
309 AND bl.line_number <= p_batch_end)));
310
311
312
313
314
315 -- We need some special checks for price breaks because
316 -- a user could have added or removed them.
317 -- Only consider the case of a rebid where the line had a previous bid
318 -- and only for blanket agreements.
319
320 -- Check all unmodified lines against source_bid if the source_bid is valid
321 IF (p_spreadsheet = g_xml_upload_mode and p_blanket = 'Y' AND p_rebid_flag = 'Y' AND p_source_bid > 0) THEN
322
323 UPDATE pon_bid_item_prices bl
324 SET bl.is_changed_line_flag = 'Y'
325 WHERE bl.bid_number = p_bid_number
326 AND bl.batch_id = p_batch_id
327 AND (EXISTS
328
329 -- Check if a shipment was deleted
330 (SELECT old_bpb.line_number
331 FROM pon_bid_shipments bpb, pon_bid_shipments old_bpb
332 WHERE old_bpb.bid_number = p_source_bid
333 AND old_bpb.line_number = bl.line_number
334 AND bpb.bid_number (+) = p_bid_number
335 AND bpb.line_number (+) = old_bpb.line_number
336 AND bpb.shipment_number (+) = old_bpb.shipment_number
337 AND bpb.shipment_number IS null
338 AND rownum = 1)
339
340 OR EXISTS
341
342 -- Check if a shipment was added
343 (SELECT bpb.line_number
344 FROM pon_bid_shipments bpb, pon_bid_shipments old_bpb
345 WHERE bpb.bid_number = p_bid_number
346 AND bpb.line_number = bl.line_number
347 AND old_bpb.bid_number (+) = p_source_bid
348 AND old_bpb.line_number (+) = bpb.line_number
349 AND old_bpb.shipment_number (+) = bpb.shipment_number
350 AND old_bpb.shipment_number IS null
351 AND rownum = 1)
352 );
353 END IF;
354
355
356 END populate_has_bid_changed_line;
357
361 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
358 -- wrapper for spreadsheet case
359 PROCEDURE populate_has_bid_changed_line
360 (
362 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
363 p_source_bid IN pon_bid_item_prices.bid_number%TYPE,
364 p_batch_id IN pon_bid_item_prices.batch_id%TYPE,
365 p_rebid_flag VARCHAR2,
366 p_blanket VARCHAR2,
367 p_spreadsheet IN VARCHAR2
368 )
369 IS
370 BEGIN
371 populate_has_bid_changed_line(p_auc_header_id, p_bid_number, p_source_bid,
372 p_batch_id, -1, -1, p_rebid_flag, p_blanket,
373 'Y', p_spreadsheet);
374
375 END populate_has_bid_changed_line;
376
377
378 -- wrapper for online case
379 PROCEDURE populate_has_bid_changed_line
380 (
381 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
382 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
383 p_source_bid IN pon_bid_item_prices.bid_number%TYPE,
384 p_batch_start IN NUMBER,
385 p_batch_end IN NUMBER,
386 p_rebid_flag IN VARCHAR2,
387 p_blanket IN VARCHAR2,
388 p_use_batch_id IN VARCHAR2
389 ) IS
390 BEGIN
391 populate_has_bid_changed_line(p_auc_header_id, p_bid_number, p_source_bid,
392 -1, p_batch_start, p_batch_end, p_rebid_flag,
393 p_blanket, 'N', g_online_mode);
394 END populate_has_bid_changed_line;
395
396
397
398 PROCEDURE calc_total_weighted_score
399 (
400 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
401 p_batch_id IN pon_bid_item_prices.batch_id%TYPE
402 ) IS
403 BEGIN
404
405 -- First populate the score for each attribute
406 -- The match we make is dependent on the attributes datatype
407 -- TXT - we match value against value
408 -- NUM - we check value between from_range and to_range,
409 -- converting varchar to number first
410 -- DAT - we check value between from_range and to_range,
411 -- converting varchar to date first (timestamp for need_by_date)
412 UPDATE pon_bid_attribute_values ba
413 SET ba.score =
414 nvl((SELECT s.score
415 FROM pon_attribute_scores s
416 WHERE s.auction_header_id = ba.auction_header_id
417 AND s.line_number = ba.line_number
418 AND s.attribute_sequence_number = ba.sequence_number
419 AND (datatype = 'TXT' AND ba.value = s.value
420 OR datatype = 'NUM' AND to_number(ba.value, g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
421 BETWEEN to_number(nvl(s.from_range, ba.value), g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
422 AND to_number(nvl(s.to_range, ba.value), g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
423 OR datatype = 'DAT'
424 AND (ba.sequence_number = -10 AND
425 to_date(ba.value, 'dd-mm-yyyy hh24:mi:ss') BETWEEN
426 to_date(nvl(s.from_range, ba.value), 'dd-mm-yyyy hh24:mi:ss')
427 AND to_date(nvl(s.to_range, ba.value), 'dd-mm-yyyy hh24:mi:ss')
428 OR ba.sequence_number <> -10 AND
429 to_date(ba.value, 'dd-mm-yyyy') BETWEEN
430 to_date(nvl(s.from_range, ba.value), 'dd-mm-yyyy')
431 AND to_date(nvl(s.to_range, ba.value), 'dd-mm-yyyy')))),
432 nvl2(ba.value, 0, null))
433 WHERE ba.bid_number = p_bid_number
434 AND EXISTS (SELECT bl.line_number
435 FROM pon_bid_item_prices bl
436 WHERE bl.bid_number = p_bid_number
437 AND bl.line_number = ba.line_number
438 AND bl.batch_id = p_batch_id);
439
440
441 UPDATE pon_bid_attribute_values ba
442 SET ba.weighted_score =
443 (SELECT decode(nvl(aa.scoring_type, 'NONE'),
444 'NONE', null,
445 aa.weight / 100.0 * nvl(ba.score, 0))
446 FROM pon_auction_attributes aa
447 WHERE aa.auction_header_id = ba.auction_header_id
448 AND aa.line_number = ba.line_number
449 AND aa.sequence_number = ba.sequence_number)
450 WHERE ba.bid_number = p_bid_number
451 AND EXISTS (SELECT bl.line_number
452 FROM pon_bid_item_prices bl
453 WHERE bl.bid_number = p_bid_number
454 AND bl.line_number = ba.line_number
455 AND bl.batch_id = p_batch_id);
456
457
458 -- Conditions to check with total_weighted_score:
459 -- If there were no scored attributes, set to 100.
460 -- If there was a scored attribute that didn't recieve a bid, set to null
461 -- Ignore all unscored (weight not > 0) attributes
462 -- LOGIC: decode(# scored attr,
463 -- null, if line has bid then 100 else null,
464 -- 0, if line has bid then 100 else null,
465 -- # scored attr with bids,
466 -- score, null)
467
468 UPDATE pon_bid_item_prices bl
469 SET bl.total_weighted_score =
470 (SELECT decode(sum(sign(aa.weight)),
471 null, decode(bl.has_bid_flag, 'Y', 100, null),
472 0, decode(bl.has_bid_flag, 'Y', 100, null),
473 sum(decode(sign(aa.weight), 1, nvl2(ba.value, 1, 0), 0)),
474 sum(aa.weight / 100.0 * nvl(ba.score, 0)), null)
475 FROM pon_bid_attribute_values ba, pon_auction_attributes aa
476 WHERE ba.bid_number = bl.bid_number
477 AND ba.line_number = bl.line_number
478 AND aa.auction_header_id = ba.auction_header_id
479 AND aa.line_number = ba.line_number
480 AND aa.sequence_number = ba.sequence_number)
484 END calc_total_weighted_score;
481 WHERE bl.bid_number = p_bid_number
482 AND bl.batch_id = p_batch_id;
483
485
486 PROCEDURE validate_bids_placed
487 (
488 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
489 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
490 p_interface_type IN pon_interface_errors.interface_type%TYPE,
491 p_userid IN pon_interface_errors.created_by%TYPE,
492 p_rebid IN VARCHAR2,
493 p_bid_all_lines IN VARCHAR2,
494 p_auc_has_items IN VARCHAR2,
495 p_suffix IN VARCHAR2,
496 p_batch_id IN pon_interface_errors.batch_id%TYPE,
497 p_request_id IN pon_interface_errors.request_id%TYPE
498 ) IS
499 l_header_modified VARCHAR2(1);
500 BEGIN
501
502 -- Determine if the bid header was modified
503 SELECT decode(count(bh.bid_number), 0, 'N', 'Y')
504 INTO l_header_modified
505 FROM pon_bid_headers bh
506 WHERE bh.bid_number = p_bid_number
507 AND ((bh.old_note_to_auction_owner IS null
508 AND bh.note_to_auction_owner IS NOT null
509 OR bh.old_note_to_auction_owner IS NOT null
510 AND bh.note_to_auction_owner IS null
511 OR bh.old_note_to_auction_owner <> bh.note_to_auction_owner)
512 OR nvl(bh.old_bidders_bid_number, -1) <> nvl(bh.bidders_bid_number, -1)
513 OR (bh.old_bid_expiration_date IS null
514 AND bh.bid_expiration_date IS NOT null
515 OR bh.old_bid_expiration_date IS NOT null
516 AND bh.bid_expiration_date IS null
517 OR bh.old_bid_expiration_date <> bh.bid_expiration_date)
518 OR nvl(bh.old_min_bid_change, -1) <> nvl(bh.min_bid_change, -1)
519 -- surrog bid receipt date must be non-null if surrogate bid
520 OR bh.surrog_bid_flag = 'Y'
521 AND bh.old_surrog_bid_receipt_date <> bh.surrog_bid_receipt_date
522 -- Check if a header attribute was modified
523 OR EXISTS
524 (SELECT null
525 FROM pon_bid_attribute_values ba
526 WHERE ba.bid_number = p_bid_number
527 AND ba.line_number = -1
528 AND (ba.old_value <> ba.value
529 OR ba.old_value IS null AND ba.value IS NOT null
530 OR ba.value IS null AND ba.old_value IS NOT null)));
531
532 INSERT FIRST
533
534 -- When rebidding, the header or one line must be changed
535 WHEN p_rebid = 'Y' AND l_header_modified = 'N' AND s_changed_line_count = 0 THEN
536 INTO pon_interface_errors
537 (INTERFACE_TYPE,
538 TABLE_NAME,
539 BATCH_ID,
540 ERROR_MESSAGE_NAME,
541 CREATED_BY,
542 CREATION_DATE,
543 LAST_UPDATED_BY,
544 LAST_UPDATE_DATE,
545 REQUEST_ID,
546 AUCTION_HEADER_ID,
547 BID_NUMBER,
548 EXPIRATION_DATE)
549 VALUES
550 (p_interface_type,
551 'PON_BID_HEADERS',
552 p_batch_id,
553 'PON_BID_NO_CHANGES',
554 p_userid,
555 sysdate,
556 p_userid,
557 sysdate,
558 p_request_id,
559 p_auc_header_id,
560 p_bid_number,
561 g_exp_date)
562
563 -- All lines must have recieved a bid if that is a requirement
564 WHEN p_bid_all_lines = 'Y' AND s_avail_bidded_lines <> s_bidded_lines THEN
565 INTO pon_interface_errors
566 (INTERFACE_TYPE,
567 TABLE_NAME,
568 BATCH_ID,
569 ERROR_MESSAGE_NAME,
570 CREATED_BY,
571 CREATION_DATE,
572 LAST_UPDATED_BY,
573 LAST_UPDATE_DATE,
574 REQUEST_ID,
575 AUCTION_HEADER_ID,
576 BID_NUMBER,
577 EXPIRATION_DATE)
578 VALUES
579 (p_interface_type,
580 'PON_BID_HEADERS',
581 p_batch_id,
582 'PON_MUST_BIDALL_ITEMS' || p_suffix,
583 p_userid,
584 sysdate,
585 p_userid,
586 sysdate,
587 p_request_id,
588 p_auc_header_id,
589 p_bid_number,
590 g_exp_date)
591
592 -- At least one line must have recieved a bid if the auction has lines
593 WHEN p_rebid = 'N' AND p_auc_has_items = 'Y' AND s_has_bid_count = 0 THEN
594 INTO pon_interface_errors
595 (INTERFACE_TYPE,
596 TABLE_NAME,
597 BATCH_ID,
598 ERROR_MESSAGE_NAME,
599 CREATED_BY,
600 CREATION_DATE,
601 LAST_UPDATED_BY,
602 LAST_UPDATE_DATE,
603 REQUEST_ID,
604 AUCTION_HEADER_ID,
605 BID_NUMBER,
606 EXPIRATION_DATE)
607 VALUES
608 (p_interface_type,
609 'PON_BID_HEADERS',
610 p_batch_id,
611 'PON_MUST_BID_ATLEAST_ONE' || p_suffix,
612 p_userid,
613 sysdate,
614 p_userid,
615 sysdate,
616 p_request_id,
617 p_auc_header_id,
618 p_bid_number,
619 g_exp_date)
620
621 SELECT
622 count(bl.line_number) s_line_count,
623 -- modified by Allen Yang for surrogate bid bug #7703665, #8220778 2009/02/03
624 ----------------------------------------------------------------------------
625 ---sum(decode(bl.has_bid_flag, 'Y', 1, 0)) s_has_bid_count,
626 sum(decode(bl.has_bid_flag, 'Y', 1,
627 decode(NVL(paha.FULL_QUANTITY_BID_CODE, 'FULL_QTY_BIDS_REQD'), 'FULL_QTY_BIDS_REQD',
628 decode(NVL(paha.Two_Part_Flag, 'N'), 'Y',
629 decode(NVL(paha.TECHNICAL_EVALUATION_STATUS, 'NOT_COMPLETED'), 'NOT_COMPLETED',
630 decode(NVL(pbh.surrog_bid_flag, 'N'), 'Y', 1, 0), 0), 0), 0))) s_has_bid_count,
631 sum(decode(bl.is_changed_line_flag, 'Y', 1, 0)) s_changed_line_count,
635 decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0),
632 sum(decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0)) s_avail_bidded_lines,
633 --sum(decode(bl.has_bid_flag, 'Y', decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0), 0)) s_bidded_lines
634 sum(decode(bl.has_bid_flag, 'Y',
636 decode(NVL(paha.FULL_QUANTITY_BID_CODE, 'FULL_QTY_BIDS_REQD'), 'FULL_QTY_BIDS_REQD',
637 decode(NVL(paha.Two_Part_Flag, 'N'), 'Y',
638 decode(NVL(paha.TECHNICAL_EVALUATION_STATUS, 'NOT_COMPLETED'), 'NOT_COMPLETED',
639 decode(NVL(pbh.surrog_bid_flag, 'N'), 'Y', 1, 0), 0), 0), 0))) s_bidded_lines
640 ----------------------------------------------------------------------------
641 FROM pon_bid_item_prices bl,
642 pon_auction_item_prices_all paip
643 -- added by Allen Yang for surrogate bid bug 7703665, #8220778 2009/02/03
644 ---------------------------------------------------------------
645 , pon_auction_headers_all paha,
646 pon_bid_headers pbh
647 ---------------------------------------------------------------
648 WHERE bl.bid_number = p_bid_number and
649 bl.auction_header_id = paip.auction_header_id and
650 bl.line_number = paip.line_number
651 -- added by Allen Yang for surrogate bid bug 7703665, #8220778 2009/02/03
652 ---------------------------------------------------------------
653 and bl.bid_number = pbh.bid_number
654 and pbh.auction_header_id = paha.auction_header_id
655 ---------------------------------------------------------------
656 ;
657
658 END validate_bids_placed;
659
660 PROCEDURE validate_lots_and_groups
661 (
662 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
663 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
664 p_interface_type IN pon_interface_errors.interface_type%TYPE,
665 p_userid IN pon_interface_errors.created_by%TYPE,
666 p_spreadsheet IN VARCHAR2,
667 p_batch_id IN pon_interface_errors.batch_id%TYPE,
668 p_request_id IN pon_interface_errors.request_id%TYPE
669 ) IS
670 BEGIN
671
672 INSERT ALL
673
674 -- All lines in a GROUP must be bid on simultaneously
675 WHEN s_group_type = 'GROUP' AND
676 (SELECT decode(sum(decode(bl.has_bid_flag, 'Y', 1, 0)), 0, 'OK',
677 count(bl.bid_number), 'OK', 'N')
678 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
679 WHERE bl.bid_number = p_bid_number
680 AND al.auction_header_id = bl.auction_header_id
681 AND al.line_number = bl.line_number
682 AND al.auction_header_id = p_auc_header_id
683 AND al.parent_line_number = s_line_number) = 'N' THEN
684 INTO pon_interface_errors
685 (INTERFACE_TYPE,
686 TABLE_NAME,
687 BATCH_ID,
688 INTERFACE_LINE_ID,
689 ERROR_MESSAGE_NAME,
690 CREATED_BY,
691 CREATION_DATE,
692 LAST_UPDATED_BY,
693 LAST_UPDATE_DATE,
694 REQUEST_ID,
695 AUCTION_HEADER_ID,
696 BID_NUMBER,
697 LINE_NUMBER,
698 EXPIRATION_DATE,
699 TOKEN1_NAME,
700 TOKEN1_VALUE,
701 WORKSHEET_NAME,
702 WORKSHEET_SEQUENCE_NUMBER,
703 ENTITY_MESSAGE_CODE)
704 VALUES
705 (p_interface_type,
706 'PON_BID_ITEM_PRICES',
707 p_batch_id,
708 s_interface_line_id,
709 'PON_GROUP_PARTIAL_BID_ERR',
710 p_userid,
711 sysdate,
712 p_userid,
713 sysdate,
714 p_request_id,
715 p_auc_header_id,
716 p_bid_number,
717 s_line_number,
718 g_exp_date,
719 'LINENUMBER',
720 s_document_disp_line_number,
721 s_worksheet_name,
722 s_worksheet_sequence_number,
723 s_entity_message_code)
724
725 -- If a lot has no bid, none of it's children can be bid on
726 WHEN s_group_type = 'LOT' AND s_has_bid_flag = 'N' AND EXISTS
727 (SELECT bl.bid_number
728 FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
729 WHERE bl.bid_number = p_bid_number
730 AND al.auction_header_id = bl.auction_header_id
731 AND al.line_number = bl.line_number
732 AND al.auction_header_id = p_auc_header_id
733 AND al.parent_line_number = s_line_number
734 AND bl.has_bid_flag = 'Y'
735 AND rownum = 1) THEN
736 INTO pon_interface_errors
737 (INTERFACE_TYPE,
738 TABLE_NAME,
739 BATCH_ID,
740 INTERFACE_LINE_ID,
741 ERROR_MESSAGE_NAME,
742 CREATED_BY,
743 CREATION_DATE,
744 LAST_UPDATED_BY,
745 LAST_UPDATE_DATE,
746 REQUEST_ID,
747 AUCTION_HEADER_ID,
748 BID_NUMBER,
749 LINE_NUMBER,
750 EXPIRATION_DATE,
751 TOKEN1_NAME,
752 TOKEN1_VALUE,
753 WORKSHEET_NAME,
754 WORKSHEET_SEQUENCE_NUMBER,
755 ENTITY_MESSAGE_CODE)
756 VALUES
757 (p_interface_type,
758 'PON_BID_ITEM_PRICES',
759 p_batch_id,
760 s_interface_line_id,
761 'PON_LOT_NOT_BID_ERR',
762 p_userid,
763 sysdate,
764 p_userid,
765 sysdate,
766 p_request_id,
767 p_auc_header_id,
768 p_bid_number,
772 s_document_disp_line_number,
769 s_line_number,
770 g_exp_date,
771 'LINENUMBER',
773 s_worksheet_name,
774 s_worksheet_sequence_number,
775 s_entity_message_code)
776 SELECT
777 bl.line_number s_line_number,
778 bl.has_bid_flag s_has_bid_flag,
779 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
780 g_xml_upload_mode, bl.interface_line_id,
781 to_number(null)) s_interface_line_id,
782 al.group_type s_group_type,
783 al.document_disp_line_number s_document_disp_line_number,
784 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name,
785 to_char(null)) s_worksheet_name,
786 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number,
787 to_number(null)) s_worksheet_sequence_number,
788 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code
789 FROM pon_bid_item_prices bl
790 , pon_auction_item_prices_all al
791 , pon_bid_headers pbh
792 WHERE bl.bid_number = p_bid_number
793 AND al.auction_header_id = bl.auction_header_id
794 AND al.line_number = bl.line_number
795 AND al.auction_header_id = p_auc_header_id
796 AND al.group_type IN ('GROUP', 'LOT')
797 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
798 AND pbh.bid_number = bl.bid_number
799 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
800
801 END validate_lots_and_groups;
802
803 PROCEDURE validate_lines
804 (
805 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
806 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
807 p_interface_type IN pon_interface_errors.interface_type%TYPE,
808 p_userid IN pon_interface_errors.created_by%TYPE,
809 p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
810 p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
811 p_spreadsheet IN VARCHAR2,
812 p_blanket IN VARCHAR2,
813 p_global IN VARCHAR2,
814 p_trans_view IN VARCHAR2,
815 p_rebid IN VARCHAR2,
816 p_full_qty_reqd IN VARCHAR2,
817 p_header_disp_pf IN VARCHAR2,
818 p_price_driven IN VARCHAR2,
819 p_percent_decr IN VARCHAR2,
820 p_bid_decr_method IN pon_auction_headers_all.bid_decrement_method%TYPE,
821 p_min_bid_decr IN pon_auction_headers_all.min_bid_decrement%TYPE,
822 p_min_bid_change IN pon_bid_headers.min_bid_change%TYPE,
823 p_rate IN pon_bid_headers.rate%TYPE,
824 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
825 p_amt_precision IN fnd_currencies.precision%TYPE,
826 p_bid_curr_code IN pon_bid_headers.bid_currency_code%TYPE,
827 p_suffix IN VARCHAR2,
828 p_batch_id IN pon_interface_errors.batch_id%TYPE,
829 p_request_id IN pon_interface_errors.request_id%TYPE
830 --added by Allen Yang for Surrogate Bid 2008/09/03
831 --------------------------------------------------
832 , p_two_part_tech_surrogate_flag IN VARCHAR2
833 --------------------------------------------------
834 ) IS
835
836 l_price_mask VARCHAR2(80);
837 BEGIN
838
839 -- get the price mask according to price precision
840 l_price_mask := get_mask(p_price_precision);
841
842 -- STEP 1: pre-release 12 validations.
843 -- currently 26 validations. This statement has 1-13.
844 INSERT ALL
845
846 -- Header level field min_bid_change is required if there is a proxy line
847 WHEN p_min_bid_change IS null AND s_proxy_bid_limit_price IS NOT null THEN
848 INTO pon_interface_errors
849 (INTERFACE_TYPE,
850 COLUMN_NAME,
851 TABLE_NAME,
852 BATCH_ID,
853 INTERFACE_LINE_ID,
854 ERROR_MESSAGE_NAME,
855 CREATED_BY,
856 CREATION_DATE,
857 LAST_UPDATED_BY,
858 LAST_UPDATE_DATE,
859 REQUEST_ID,
860 ENTITY_TYPE,
861 ENTITY_ATTR_NAME,
862 ERROR_VALUE_NUMBER,
863 ERROR_VALUE_DATATYPE,
864 AUCTION_HEADER_ID,
865 BID_NUMBER,
866 LINE_NUMBER,
867 EXPIRATION_DATE,
868 TOKEN1_NAME,
869 TOKEN1_VALUE,
870 WORKSHEET_NAME,
871 WORKSHEET_SEQUENCE_NUMBER,
872 ENTITY_MESSAGE_CODE)
873 VALUES
874 (p_interface_type,
875 decode(p_spreadsheet, g_xml_upload_mode,
876 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' || p_suffix),
877 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
878 'PON_BID_ITEM_PRICES',
879 p_batch_id,
880 s_interface_line_id,
881 'PON_AUCTS_MIN_DEC_NULL' || p_suffix,
882 p_userid,
883 sysdate,
884 p_userid,
885 sysdate,
886 p_request_id,
887 'BID_ITEMS',
888 'ProxyBidLimitPrice',
889 s_proxy_bid_limit_price,
890 'NUM',
891 p_auc_header_id,
892 p_bid_number,
893 s_line_number,
894 g_exp_date,
895 'LINENUMBER',
896 s_document_disp_line_number,
897 s_worksheet_name,
898 s_worksheet_sequence_number,
899 s_entity_message_code)
900 -- Price and quantity are required fields if they are editable
901 WHEN p_blanket = 'N' AND s_price_editable = 'Y' AND s_price IS null
902 AND s_qty_editable = 'Y' AND s_bid_quantity IS null
906 ---------------------------------------------------
903 -- added by Allen Yang for Surrogate Bid 2008/09/03
904 ---------------------------------------------------
905 AND p_two_part_tech_surrogate_flag = 'N'
907 THEN
908 INTO pon_interface_errors
909 (INTERFACE_TYPE,
910 TABLE_NAME,
911 BATCH_ID,
912 INTERFACE_LINE_ID,
913 ERROR_MESSAGE_NAME,
914 CREATED_BY,
915 CREATION_DATE,
916 LAST_UPDATED_BY,
917 LAST_UPDATE_DATE,
918 REQUEST_ID,
919 ENTITY_TYPE,
920 ENTITY_ATTR_NAME,
921 ERROR_VALUE_DATATYPE,
922 AUCTION_HEADER_ID,
923 BID_NUMBER,
924 LINE_NUMBER,
925 EXPIRATION_DATE,
926 TOKEN1_NAME,
927 TOKEN1_VALUE,
928 WORKSHEET_NAME,
929 WORKSHEET_SEQUENCE_NUMBER,
930 ENTITY_MESSAGE_CODE)
931 VALUES
932 (p_interface_type,
933 'PON_BID_ITEM_PRICES',
934 p_batch_id,
935 s_interface_line_id,
936 'PON_BID_PRICE_QTY_REQD' || p_suffix,
937 p_userid,
938 sysdate,
939 p_userid,
940 sysdate,
941 p_request_id,
942 'BID_ITEMS',
943 'Price',
944 'TXT',
945 p_auc_header_id,
946 p_bid_number,
947 s_line_number,
948 g_exp_date,
949 'LINENUMBER',
950 s_document_disp_line_number,
951 s_worksheet_name,
952 s_worksheet_sequence_number,
953 s_entity_message_code)
954
955 -- Price is a required field if it is editable
956 WHEN s_price_editable = 'Y' AND s_price IS null
957 -- added by Allen Yang for Surrogate Bid 2008/09/03
958 ---------------------------------------------------
959 AND p_two_part_tech_surrogate_flag = 'N'
960 ---------------------------------------------------
961 THEN
962 INTO pon_interface_errors
963 (INTERFACE_TYPE,
964 TABLE_NAME,
965 COLUMN_NAME,
966 BATCH_ID,
967 INTERFACE_LINE_ID,
968 ERROR_MESSAGE_NAME,
969 CREATED_BY,
970 CREATION_DATE,
971 LAST_UPDATED_BY,
972 LAST_UPDATE_DATE,
973 REQUEST_ID,
974 ENTITY_TYPE,
975 ENTITY_ATTR_NAME,
976 ERROR_VALUE_DATATYPE,
977 AUCTION_HEADER_ID,
978 BID_NUMBER,
979 LINE_NUMBER,
980 EXPIRATION_DATE,
981 TOKEN1_NAME,
982 TOKEN1_VALUE,
983 WORKSHEET_NAME,
984 WORKSHEET_SEQUENCE_NUMBER,
985 ENTITY_MESSAGE_CODE)
986 VALUES
987 (p_interface_type,
988 'PON_BID_ITEM_PRICES',
989 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
990 p_batch_id,
991 s_interface_line_id,
992 'PON_BID_PRICE_REQUIRED' || p_suffix,
993 p_userid,
994 sysdate,
995 p_userid,
996 sysdate,
997 p_request_id,
998 'BID_ITEMS',
999 'Price',
1000 'TXT',
1001 p_auc_header_id,
1002 p_bid_number,
1003 s_line_number,
1004 g_exp_date,
1005 'LINENUMBER',
1006 s_document_disp_line_number,
1007 s_worksheet_name,
1008 s_worksheet_sequence_number,
1009 s_entity_message_code)
1010
1011 -- Quantity is a required field if it is editable
1012 WHEN p_blanket = 'N' AND s_qty_editable = 'Y' AND s_bid_quantity IS null THEN
1013 INTO pon_interface_errors
1014 (INTERFACE_TYPE,
1015 TABLE_NAME,
1016 COLUMN_NAME,
1017 BATCH_ID,
1018 INTERFACE_LINE_ID,
1019 ERROR_MESSAGE_NAME,
1020 CREATED_BY,
1021 CREATION_DATE,
1022 LAST_UPDATED_BY,
1023 LAST_UPDATE_DATE,
1024 REQUEST_ID,
1025 ENTITY_TYPE,
1026 ENTITY_ATTR_NAME,
1027 ERROR_VALUE_DATATYPE,
1028 AUCTION_HEADER_ID,
1029 BID_NUMBER,
1030 LINE_NUMBER,
1031 EXPIRATION_DATE,
1032 TOKEN1_NAME,
1033 TOKEN1_VALUE,
1034 WORKSHEET_NAME,
1035 WORKSHEET_SEQUENCE_NUMBER,
1036 ENTITY_MESSAGE_CODE)
1037 VALUES
1038 (p_interface_type,
1039 'PON_BID_ITEM_PRICES',
1040 fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
1041 p_batch_id,
1042 s_interface_line_id,
1043 'PON_BID_QUANTITY_REQUIRED' || p_suffix,
1044 p_userid,
1045 sysdate,
1046 p_userid,
1047 sysdate,
1048 p_request_id,
1049 'BID_ITEMS',
1050 'Quantity',
1051 'TXT',
1052 p_auc_header_id,
1053 p_bid_number,
1054 s_line_number,
1055 g_exp_date,
1056 'LINENUMBER',
1057 s_document_disp_line_number,
1058 s_worksheet_name,
1059 s_worksheet_sequence_number,
1060 s_entity_message_code)
1061
1062 -- Bug 7460446 Bid cannot be zero or negative
1063 -- Doesn't apply for blanket agreements
1064 WHEN p_blanket = 'N' AND s_bid_quantity <= 0 THEN
1065 INTO pon_interface_errors
1066 (INTERFACE_TYPE,
1067 COLUMN_NAME,
1068 TABLE_NAME,
1069 BATCH_ID,
1070 INTERFACE_LINE_ID,
1071 ERROR_MESSAGE_NAME,
1072 CREATED_BY,
1076 REQUEST_ID,
1073 CREATION_DATE,
1074 LAST_UPDATED_BY,
1075 LAST_UPDATE_DATE,
1077 ENTITY_TYPE,
1078 ENTITY_ATTR_NAME,
1079 ERROR_VALUE_NUMBER,
1080 ERROR_VALUE_DATATYPE,
1081 AUCTION_HEADER_ID,
1082 BID_NUMBER,
1083 LINE_NUMBER,
1084 EXPIRATION_DATE,
1085 TOKEN1_NAME,
1086 TOKEN1_VALUE,
1087 WORKSHEET_NAME,
1088 WORKSHEET_SEQUENCE_NUMBER,
1089 ENTITY_MESSAGE_CODE)
1090 VALUES
1091 (p_interface_type,
1092 fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
1093 'PON_BID_ITEM_PRICES',
1094 p_batch_id,
1095 s_interface_line_id,
1096 'PON_AUC_BIDQTY_NEG' || p_suffix,
1097 p_userid,
1098 sysdate,
1099 p_userid,
1100 sysdate,
1101 p_request_id,
1102 'BID_ITEMS',
1103 'Quantity',
1104 s_bid_quantity,
1105 'NUM',
1106 p_auc_header_id,
1107 p_bid_number,
1108 s_line_number,
1109 g_exp_date,
1110 'LINENUMBER',
1111 s_document_disp_line_number,
1112 s_worksheet_name,
1113 s_worksheet_sequence_number,
1114 s_entity_message_code)
1115
1116 -- Bid quantity should not exceed auction quantity
1117 -- Does not apply for blanket agreements
1118 WHEN p_blanket = 'N' AND s_bid_quantity > s_auc_quantity THEN
1119 INTO pon_interface_errors
1120 (INTERFACE_TYPE,
1121 COLUMN_NAME,
1122 TABLE_NAME,
1123 BATCH_ID,
1124 INTERFACE_LINE_ID,
1125 ERROR_MESSAGE_NAME,
1126 CREATED_BY,
1127 CREATION_DATE,
1128 LAST_UPDATED_BY,
1129 LAST_UPDATE_DATE,
1130 REQUEST_ID,
1131 ENTITY_TYPE,
1132 ENTITY_ATTR_NAME,
1133 ERROR_VALUE_NUMBER,
1134 ERROR_VALUE_DATATYPE,
1135 AUCTION_HEADER_ID,
1136 BID_NUMBER,
1137 LINE_NUMBER,
1138 EXPIRATION_DATE,
1139 TOKEN1_NAME,
1140 TOKEN1_VALUE,
1141 WORKSHEET_NAME,
1142 WORKSHEET_SEQUENCE_NUMBER,
1143 ENTITY_MESSAGE_CODE)
1144 VALUES
1145 (p_interface_type,
1146 fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
1147 'PON_BID_ITEM_PRICES',
1148 p_batch_id,
1149 s_interface_line_id,
1150 'PON_INVALID_BID_QTY' || p_suffix,
1151 p_userid,
1152 sysdate,
1153 p_userid,
1154 sysdate,
1155 p_request_id,
1156 'BID_ITEMS',
1157 'Quantity',
1158 s_bid_quantity,
1159 'NUM',
1160 p_auc_header_id,
1161 p_bid_number,
1162 s_line_number,
1163 g_exp_date,
1164 'LINENUMBER',
1165 s_document_disp_line_number,
1166 s_worksheet_name,
1167 s_worksheet_sequence_number,
1168 s_entity_message_code)
1169
1170 -- If the need_by_date is scored, then promised date is required
1171 -- Does not apply for blanket agreements
1172 WHEN p_blanket = 'N' AND s_need_by_date_scored = 'Y'
1173 AND s_promised_date IS null
1174 -- added by Allen Yang for Surrogate Bid 2008/09/03
1175 ---------------------------------------------------
1176 AND p_two_part_tech_surrogate_flag = 'N'
1177 ---------------------------------------------------
1178 THEN
1179 INTO pon_interface_errors
1180 (INTERFACE_TYPE,
1181 COLUMN_NAME,
1182 TABLE_NAME,
1183 BATCH_ID,
1184 INTERFACE_LINE_ID,
1185 ERROR_MESSAGE_NAME,
1186 CREATED_BY,
1187 CREATION_DATE,
1188 LAST_UPDATED_BY,
1189 LAST_UPDATE_DATE,
1190 REQUEST_ID,
1191 ENTITY_TYPE,
1192 ENTITY_ATTR_NAME,
1193 ERROR_VALUE_DATATYPE,
1194 AUCTION_HEADER_ID,
1195 BID_NUMBER,
1196 LINE_NUMBER,
1197 EXPIRATION_DATE,
1198 TOKEN1_NAME,
1199 TOKEN1_VALUE,
1200 WORKSHEET_NAME,
1201 WORKSHEET_SEQUENCE_NUMBER,
1202 ENTITY_MESSAGE_CODE)
1203 VALUES
1204 (p_interface_type,
1205 fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
1206 'PON_BID_ITEM_PRICES',
1207 p_batch_id,
1208 s_interface_line_id,
1209 'PON_AUC_PROMISED_DATE_REQ',
1210 p_userid,
1211 sysdate,
1212 p_userid,
1213 sysdate,
1214 p_request_id,
1215 'BID_ITEMS',
1216 'PromisedDate',
1217 'TXT',
1218 p_auc_header_id,
1219 p_bid_number,
1220 s_line_number,
1221 g_exp_date,
1222 'LINENUMBER',
1223 s_document_disp_line_number,
1224 s_worksheet_name,
1225 s_worksheet_sequence_number,
1226 s_entity_message_code)
1227
1228 -- The promised date cannot be earlier than the current date
1229 -- Does not apply to blanket agreements
1230 WHEN p_blanket = 'N' AND s_promised_date < s_current_date
1231 -- added by Allen Yang for Surrogate Bid 2008/09/03
1232 ---------------------------------------------------
1233 AND p_two_part_tech_surrogate_flag = 'N'
1234 ---------------------------------------------------
1235 THEN
1236 INTO pon_interface_errors
1237 (INTERFACE_TYPE,
1238 COLUMN_NAME,
1239 TABLE_NAME,
1240 BATCH_ID,
1241 INTERFACE_LINE_ID,
1242 ERROR_MESSAGE_NAME,
1243 CREATED_BY,
1244 CREATION_DATE,
1245 LAST_UPDATED_BY,
1246 LAST_UPDATE_DATE,
1250 ERROR_VALUE_DATE,
1247 REQUEST_ID,
1248 ENTITY_TYPE,
1249 ENTITY_ATTR_NAME,
1251 ERROR_VALUE_DATATYPE,
1252 AUCTION_HEADER_ID,
1253 BID_NUMBER,
1254 LINE_NUMBER,
1255 EXPIRATION_DATE,
1256 TOKEN1_NAME,
1257 TOKEN1_VALUE,
1258 WORKSHEET_NAME,
1259 WORKSHEET_SEQUENCE_NUMBER,
1260 ENTITY_MESSAGE_CODE)
1261 VALUES
1262 (p_interface_type,
1263 fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
1264 'PON_BID_ITEM_PRICES',
1265 p_batch_id,
1266 s_interface_line_id,
1267 'PON_AUC_PDATE_TOO_EARLY',
1268 p_userid,
1269 sysdate,
1270 p_userid,
1271 sysdate,
1272 p_request_id,
1273 'BID_ITEMS',
1274 'PromisedDate',
1275 s_promised_date,
1276 'DAT',
1277 p_auc_header_id,
1278 p_bid_number,
1279 s_line_number,
1280 g_exp_date,
1281 'LINENUMBER',
1282 s_document_disp_line_number,
1283 s_worksheet_name,
1284 s_worksheet_sequence_number,
1285 s_entity_message_code)
1286
1287 -- po_bid_min_rel_amount should not be negative
1288 WHEN p_blanket = 'Y' AND s_po_bid_min_rel_amount < 0 THEN
1289 INTO pon_interface_errors
1290 (INTERFACE_TYPE,
1291 COLUMN_NAME,
1292 TABLE_NAME,
1293 BATCH_ID,
1294 INTERFACE_LINE_ID,
1295 ERROR_MESSAGE_NAME,
1296 CREATED_BY,
1297 CREATION_DATE,
1298 LAST_UPDATED_BY,
1299 LAST_UPDATE_DATE,
1300 REQUEST_ID,
1301 ENTITY_TYPE,
1302 ENTITY_ATTR_NAME,
1303 ERROR_VALUE_NUMBER,
1304 ERROR_VALUE_DATATYPE,
1305 AUCTION_HEADER_ID,
1306 BID_NUMBER,
1307 LINE_NUMBER,
1308 EXPIRATION_DATE,
1309 TOKEN1_NAME,
1310 TOKEN1_VALUE,
1311 WORKSHEET_NAME,
1312 WORKSHEET_SEQUENCE_NUMBER,
1313 ENTITY_MESSAGE_CODE)
1314 VALUES
1315 (p_interface_type,
1316 fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
1317 'PON_BID_ITEM_PRICES',
1318 p_batch_id,
1319 s_interface_line_id,
1320 'PON_AUC_MINREL_POS_ZERO' || p_suffix,
1321 p_userid,
1322 sysdate,
1323 p_userid,
1324 sysdate,
1325 p_request_id,
1326 'BID_ITEMS',
1327 'PoBidMinRelAmount',
1328 s_po_bid_min_rel_amount,
1329 'NUM',
1330 p_auc_header_id,
1331 p_bid_number,
1332 s_line_number,
1333 g_exp_date,
1334 'LINENUMBER',
1335 s_document_disp_line_number,
1336 s_worksheet_name,
1337 s_worksheet_sequence_number,
1338 s_entity_message_code)
1339
1340 -- po_bid_min_rel_amount should not exceed currency precision
1341 WHEN p_blanket = 'Y' AND validate_currency_precision(
1342 s_po_bid_min_rel_amount, p_amt_precision) = 'F' THEN
1343 INTO pon_interface_errors
1344 (INTERFACE_TYPE,
1345 COLUMN_NAME,
1346 TABLE_NAME,
1347 BATCH_ID,
1348 INTERFACE_LINE_ID,
1349 ERROR_MESSAGE_NAME,
1350 CREATED_BY,
1351 CREATION_DATE,
1352 LAST_UPDATED_BY,
1353 LAST_UPDATE_DATE,
1354 REQUEST_ID,
1355 ENTITY_TYPE,
1356 ENTITY_ATTR_NAME,
1357 ERROR_VALUE_NUMBER,
1358 ERROR_VALUE_DATATYPE,
1359 AUCTION_HEADER_ID,
1360 BID_NUMBER,
1361 LINE_NUMBER,
1362 EXPIRATION_DATE,
1363 TOKEN1_NAME,
1364 TOKEN1_VALUE,
1365 WORKSHEET_NAME,
1366 WORKSHEET_SEQUENCE_NUMBER,
1367 ENTITY_MESSAGE_CODE)
1368 VALUES
1369 (p_interface_type,
1370 fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
1371 'PON_BID_ITEM_PRICES',
1372 p_batch_id,
1373 s_interface_line_id,
1374 'PON_AUC_MINREL_MIN_PREC' || p_suffix,
1375 p_userid,
1376 sysdate,
1377 p_userid,
1378 sysdate,
1379 p_request_id,
1380 'BID_ITEMS',
1381 'PoBidMinRelAmount',
1382 s_po_bid_min_rel_amount,
1383 'NUM',
1384 p_auc_header_id,
1385 p_bid_number,
1386 s_line_number,
1387 g_exp_date,
1388 'LINENUMBER',
1389 s_document_disp_line_number,
1390 s_worksheet_name,
1391 s_worksheet_sequence_number,
1392 s_entity_message_code)
1393
1394 -- bid_currency_unit_price should be positive
1395 -- bid_currency_unit_price only validated when header disp_pf_flag is Y
1396 -- Suppress error if spreadsheet case and no price factors
1397 WHEN p_header_disp_pf = 'Y' AND s_bid_currency_unit_price <= 0
1398 AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
1399 -- added by Allen Yang for Surrogate Bid 2008/09/03
1400 ---------------------------------------------------
1401 AND p_two_part_tech_surrogate_flag = 'N'
1402 ---------------------------------------------------
1403 THEN
1404 INTO pon_interface_errors
1405 (INTERFACE_TYPE,
1406 COLUMN_NAME,
1407 TABLE_NAME,
1408 BATCH_ID,
1409 INTERFACE_LINE_ID,
1410 ERROR_MESSAGE_NAME,
1411 CREATED_BY,
1412 CREATION_DATE,
1413 LAST_UPDATED_BY,
1414 LAST_UPDATE_DATE,
1418 ERROR_VALUE_NUMBER,
1415 REQUEST_ID,
1416 ENTITY_TYPE,
1417 ENTITY_ATTR_NAME,
1419 ERROR_VALUE_DATATYPE,
1420 AUCTION_HEADER_ID,
1421 BID_NUMBER,
1422 LINE_NUMBER,
1423 EXPIRATION_DATE,
1424 TOKEN1_NAME,
1425 TOKEN1_VALUE,
1426 WORKSHEET_NAME,
1427 WORKSHEET_SEQUENCE_NUMBER,
1428 ENTITY_MESSAGE_CODE)
1429 VALUES
1430 (p_interface_type,
1431 fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
1432 'PON_BID_ITEM_PRICES',
1433 p_batch_id,
1434 s_interface_line_id,
1435 'PON_LINEPRICE_MUST_BE_POS',
1436 p_userid,
1437 sysdate,
1438 p_userid,
1439 sysdate,
1440 p_request_id,
1441 'BID_ITEMS',
1442 'BidCurrencyUnitPrice',
1443 s_bid_currency_unit_price,
1444 'NUM',
1445 p_auc_header_id,
1446 p_bid_number,
1447 s_line_number,
1448 g_exp_date,
1449 'LINENUMBER',
1450 s_document_disp_line_number,
1451 s_worksheet_name,
1452 s_worksheet_sequence_number,
1453 s_entity_message_code)
1454
1455 -- bid_currency_unit_price only validated when header disp_pf_flag is Y
1456 -- bid_currency_unit_price precision should not exceed price precision
1457 -- Suppress error if spreadsheet case and no price factors
1458 WHEN p_header_disp_pf = 'Y' AND validate_price_precision(
1459 s_bid_currency_unit_price, p_price_precision) = 'F'
1460 AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
1461 -- added by Allen Yang for Surrogate Bid 2008/09/03
1462 ---------------------------------------------------
1463 AND p_two_part_tech_surrogate_flag = 'N'
1464 ---------------------------------------------------
1465 THEN
1466 INTO pon_interface_errors
1467 (INTERFACE_TYPE,
1468 COLUMN_NAME,
1469 TABLE_NAME,
1470 BATCH_ID,
1471 INTERFACE_LINE_ID,
1472 ERROR_MESSAGE_NAME,
1473 CREATED_BY,
1474 CREATION_DATE,
1475 LAST_UPDATED_BY,
1476 LAST_UPDATE_DATE,
1477 REQUEST_ID,
1478 ENTITY_TYPE,
1479 ENTITY_ATTR_NAME,
1480 ERROR_VALUE_NUMBER,
1481 ERROR_VALUE_DATATYPE,
1482 AUCTION_HEADER_ID,
1483 BID_NUMBER,
1484 LINE_NUMBER,
1485 EXPIRATION_DATE,
1486 TOKEN1_NAME,
1487 TOKEN1_VALUE,
1488 WORKSHEET_NAME,
1489 WORKSHEET_SEQUENCE_NUMBER,
1490 ENTITY_MESSAGE_CODE)
1491 VALUES
1492 (p_interface_type,
1493 fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
1494 'PON_BID_ITEM_PRICES',
1495 p_batch_id,
1496 s_interface_line_id,
1497 'PON_LINEPRICE_MIN_PREC' || p_suffix,
1498 p_userid,
1499 sysdate,
1500 p_userid,
1501 sysdate,
1502 p_request_id,
1503 'BID_ITEMS',
1504 'BidCurrencyUnitPrice',
1505 s_bid_currency_unit_price,
1506 'NUM',
1507 p_auc_header_id,
1508 p_bid_number,
1509 s_line_number,
1510 g_exp_date,
1511 'LINENUMBER',
1512 s_document_disp_line_number,
1513 s_worksheet_name,
1514 s_worksheet_sequence_number,
1515 s_entity_message_code)
1516
1517 -- If the line has price factors, the line price is required
1518 WHEN s_display_price_factors_flag = 'Y'
1519 AND s_bid_currency_unit_price IS null
1520 -- added by Allen Yang for Surrogate Bid 2008/09/03
1521 ---------------------------------------------------
1522 AND p_two_part_tech_surrogate_flag = 'N'
1523 ---------------------------------------------------
1524 THEN
1525 INTO pon_interface_errors
1526 (INTERFACE_TYPE,
1527 COLUMN_NAME,
1528 TABLE_NAME,
1529 BATCH_ID,
1530 INTERFACE_LINE_ID,
1531 ERROR_MESSAGE_NAME,
1532 CREATED_BY,
1533 CREATION_DATE,
1534 LAST_UPDATED_BY,
1535 LAST_UPDATE_DATE,
1536 REQUEST_ID,
1537 ENTITY_TYPE,
1538 ENTITY_ATTR_NAME,
1539 ERROR_VALUE_DATATYPE,
1540 AUCTION_HEADER_ID,
1541 BID_NUMBER,
1542 LINE_NUMBER,
1543 EXPIRATION_DATE,
1544 TOKEN1_NAME,
1545 TOKEN1_VALUE,
1546 WORKSHEET_NAME,
1547 WORKSHEET_SEQUENCE_NUMBER,
1548 ENTITY_MESSAGE_CODE)
1549 VALUES
1550 (p_interface_type,
1551 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1552 'PON_BID_ITEM_PRICES',
1553 p_batch_id,
1554 s_interface_line_id,
1555 'PON_BID_PRICE_REQUIRED' || p_suffix,
1556 p_userid,
1557 sysdate,
1558 p_userid,
1559 sysdate,
1560 p_request_id,
1561 'BID_ITEMS',
1562 'BidCurrencyUnitPrice',
1563 'TXT',
1564 p_auc_header_id,
1565 p_bid_number,
1566 s_line_number,
1567 g_exp_date,
1568 'LINENUMBER',
1569 s_document_disp_line_number,
1570 s_worksheet_name,
1571 s_worksheet_sequence_number,
1572 s_entity_message_code)
1573 SELECT
1574 sysdate s_current_date,
1575 decode(al.price_disabled_flag, 'Y', 'N',
1576 decode(bl.display_price_factors_flag, 'Y', 'N',
1580 decode(al.group_type, 'GROUP', 'N',
1577 decode(al.group_type, 'GROUP', 'N', 'Y'))) s_price_editable,
1578 decode(p_full_qty_reqd, 'Y', 'N',
1579 decode(al.quantity_disabled_flag, 'Y', 'N',
1581 decode(al.order_type_lookup_code, 'AMOUNT', 'N',
1582 'RATE', 'N', 'FIXED PRICE', 'N', 'Y')))) s_qty_editable,
1583 bl.quantity s_bid_quantity,
1584 bl.promised_date s_promised_date,
1585 bl.bid_currency_unit_price s_bid_currency_unit_price,
1586 bl.price s_price,
1587 bl.po_bid_min_rel_amount s_po_bid_min_rel_amount,
1588 bl.proxy_bid_limit_price s_proxy_bid_limit_price,
1589 bl.display_price_factors_flag s_display_price_factors_flag,
1590 al.is_need_by_date_scored s_need_by_date_scored,
1591 al.quantity s_auc_quantity,
1592 bl.line_number s_line_number,
1593 decode(p_spreadsheet, g_xml_upload_mode, bl.interface_line_id,
1594 g_txt_upload_mode, bl.interface_line_id,
1595 to_number(null)) s_interface_line_id,
1596 al.document_disp_line_number s_document_disp_line_number,
1597 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
1598 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
1599 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code
1600 FROM pon_auction_item_prices_all al
1601 , pon_bid_item_prices bl
1602 , pon_bid_headers pbh
1603 WHERE al.auction_header_id = p_auc_header_id
1604 AND bl.bid_number = p_bid_number
1605 AND al.line_number = bl.line_number
1606 AND bl.is_changed_line_flag = 'Y'
1607 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
1608 AND pbh.bid_number = bl.bid_number
1609 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
1610
1611
1612 -- STEP 2: pre-release 12 validations 14-26
1613 INSERT ALL
1614
1615 -- bid_currency_price should be positive
1616 WHEN s_bid_currency_price <= 0
1617 -- added by Allen Yang for Surrogate Bid 2008/09/03
1618 ---------------------------------------------------
1619 AND p_two_part_tech_surrogate_flag = 'N'
1620 ---------------------------------------------------
1621 THEN
1622 INTO pon_interface_errors
1623 (INTERFACE_TYPE,
1624 COLUMN_NAME,
1625 TABLE_NAME,
1626 BATCH_ID,
1627 INTERFACE_LINE_ID,
1628 ERROR_MESSAGE_NAME,
1629 CREATED_BY,
1630 CREATION_DATE,
1631 LAST_UPDATED_BY,
1632 LAST_UPDATE_DATE,
1633 REQUEST_ID,
1634 ENTITY_TYPE,
1635 ENTITY_ATTR_NAME,
1636 ERROR_VALUE_NUMBER,
1637 ERROR_VALUE_DATATYPE,
1638 AUCTION_HEADER_ID,
1639 BID_NUMBER,
1640 LINE_NUMBER,
1641 EXPIRATION_DATE,
1642 TOKEN1_NAME,
1643 TOKEN1_VALUE,
1644 WORKSHEET_NAME,
1645 WORKSHEET_SEQUENCE_NUMBER,
1646 ENTITY_MESSAGE_CODE)
1647 VALUES
1648 (p_interface_type,
1649 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1650 'PON_BID_ITEM_PRICES',
1651 p_batch_id,
1652 s_interface_line_id,
1653 decode(p_trans_view, 'N',
1654 'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix,
1655 decode(s_display_price_factors_flag, 'Y',
1656 'PON_LINE_BIDPRICE_INVALID_2' || p_suffix,
1657 'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix)),
1658 p_userid,
1659 sysdate,
1660 p_userid,
1661 sysdate,
1662 p_request_id,
1663 'BID_ITEMS',
1664 'BidCurrencyPrice',
1665 s_bid_currency_price,
1666 'NUM',
1667 p_auc_header_id,
1668 p_bid_number,
1669 s_line_number,
1670 g_exp_date,
1671 'LINENUMBER',
1672 s_document_disp_line_number,
1673 s_worksheet_name,
1674 s_worksheet_sequence_number,
1675 s_entity_message_code)
1676
1677 -- bid_currency_price precision should not exceed price precision
1678 WHEN
1679 -- added by Allen Yang for Surrogate Bid 2008/09/03
1680 ---------------------------------------------------
1681 p_two_part_tech_surrogate_flag = 'N' AND
1682 ---------------------------------------------------
1683 validate_price_precision(s_bid_currency_price, p_price_precision) = 'F'
1684 THEN
1685 INTO pon_interface_errors
1686 (INTERFACE_TYPE,
1687 COLUMN_NAME,
1688 TABLE_NAME,
1689 BATCH_ID,
1690 INTERFACE_LINE_ID,
1691 ERROR_MESSAGE_NAME,
1692 CREATED_BY,
1693 CREATION_DATE,
1694 LAST_UPDATED_BY,
1695 LAST_UPDATE_DATE,
1696 REQUEST_ID,
1697 ENTITY_TYPE,
1698 ENTITY_ATTR_NAME,
1699 ERROR_VALUE_NUMBER,
1700 ERROR_VALUE_DATATYPE,
1701 AUCTION_HEADER_ID,
1702 BID_NUMBER,
1703 LINE_NUMBER,
1704 EXPIRATION_DATE,
1705 TOKEN1_NAME,
1706 TOKEN1_VALUE,
1707 WORKSHEET_NAME,
1708 WORKSHEET_SEQUENCE_NUMBER,
1709 ENTITY_MESSAGE_CODE)
1710 VALUES
1711 (p_interface_type,
1712 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1713 'PON_BID_ITEM_PRICES',
1714 p_batch_id,
1715 s_interface_line_id,
1716 'PON_AUCTS_BIDPRICE_MIN_PREC' || p_suffix,
1717 p_userid,
1718 sysdate,
1719 p_userid,
1720 sysdate,
1721 p_request_id,
1725 'NUM',
1722 'BID_ITEMS',
1723 'BidCurrencyPrice',
1724 s_bid_currency_price,
1726 p_auc_header_id,
1727 p_bid_number,
1728 s_line_number,
1729 g_exp_date,
1730 'LINENUMBER',
1731 s_document_disp_line_number,
1732 s_worksheet_name,
1733 s_worksheet_sequence_number,
1734 s_entity_message_code)
1735
1736 -- In untransformed view, bid_currency_trans_price should be positive
1737 -- since bid_currency_price is the same as bid_currency_unit_price
1738 -- Do not report the error if one will be reported for bid_currency_price
1739 WHEN p_trans_view = 'Y' AND s_bid_currency_price > 0
1740 AND s_bid_currency_trans_price <= 0
1741 -- added by Allen Yang for Surrogate Bid 2008/09/03
1742 ---------------------------------------------------
1743 AND p_two_part_tech_surrogate_flag = 'N'
1744 ---------------------------------------------------
1745 THEN
1746 INTO pon_interface_errors
1747 (INTERFACE_TYPE,
1748 COLUMN_NAME,
1749 TABLE_NAME,
1750 BATCH_ID,
1751 INTERFACE_LINE_ID,
1752 ERROR_MESSAGE_NAME,
1753 CREATED_BY,
1754 CREATION_DATE,
1755 LAST_UPDATED_BY,
1756 LAST_UPDATE_DATE,
1757 REQUEST_ID,
1758 ENTITY_TYPE,
1759 ENTITY_ATTR_NAME,
1760 ERROR_VALUE_NUMBER,
1761 ERROR_VALUE_DATATYPE,
1762 AUCTION_HEADER_ID,
1763 BID_NUMBER,
1764 LINE_NUMBER,
1765 EXPIRATION_DATE,
1766 TOKEN1_NAME,
1767 TOKEN1_VALUE,
1768 WORKSHEET_NAME,
1769 WORKSHEET_SEQUENCE_NUMBER,
1770 ENTITY_MESSAGE_CODE)
1771 VALUES
1772 (p_interface_type,
1773 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1774 'PON_BID_ITEM_PRICES',
1775 p_batch_id,
1776 s_interface_line_id,
1777 'PON_LINE_BIDPRICE_INVALID_1' || p_suffix,
1778 p_userid,
1779 sysdate,
1780 p_userid,
1781 sysdate,
1782 p_request_id,
1783 'BID_ITEMS',
1784 'BidCurrencyTransPrice',
1785 s_bid_currency_trans_price,
1786 'NUM',
1787 p_auc_header_id,
1788 p_bid_number,
1789 s_line_number,
1790 g_exp_date,
1791 'LINENUMBER',
1792 s_document_disp_line_number,
1793 s_worksheet_name,
1794 s_worksheet_sequence_number,
1795 s_entity_message_code)
1796
1797 -- bid_currency_limit_price should be positive
1798 WHEN s_bid_currency_limit_price <= 0
1799 -- added by Allen Yang for Surrogate Bid 2008/09/03
1800 ---------------------------------------------------
1801 AND p_two_part_tech_surrogate_flag = 'N'
1802 ---------------------------------------------------
1803 THEN
1804 INTO pon_interface_errors
1805 (INTERFACE_TYPE,
1806 COLUMN_NAME,
1807 TABLE_NAME,
1808 BATCH_ID,
1809 INTERFACE_LINE_ID,
1810 ERROR_MESSAGE_NAME,
1811 CREATED_BY,
1812 CREATION_DATE,
1813 LAST_UPDATED_BY,
1814 LAST_UPDATE_DATE,
1815 REQUEST_ID,
1816 ENTITY_TYPE,
1817 ENTITY_ATTR_NAME,
1818 ERROR_VALUE_NUMBER,
1819 ERROR_VALUE_DATATYPE,
1820 AUCTION_HEADER_ID,
1821 BID_NUMBER,
1822 LINE_NUMBER,
1823 EXPIRATION_DATE,
1824 TOKEN1_NAME,
1825 TOKEN1_VALUE,
1826 WORKSHEET_NAME,
1827 WORKSHEET_SEQUENCE_NUMBER,
1828 ENTITY_MESSAGE_CODE)
1829 VALUES
1830 (p_interface_type,
1831 decode(p_spreadsheet,
1832 g_xml_upload_mode,
1833 fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
1834 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
1835 'PON_BID_ITEM_PRICES',
1836 p_batch_id,
1837 s_interface_line_id,
1838 'PON_AUC_LIMPRICE_MUST_BE_POS',
1839 p_userid,
1840 sysdate,
1841 p_userid,
1842 sysdate,
1843 p_request_id,
1844 'BID_ITEMS',
1845 'BidCurrencyLimitPrice',
1846 s_bid_currency_limit_price,
1847 'NUM',
1848 p_auc_header_id,
1849 p_bid_number,
1850 s_line_number,
1851 g_exp_date,
1852 'LINENUMBER',
1853 s_document_disp_line_number,
1854 s_worksheet_name,
1855 s_worksheet_sequence_number,
1856 s_entity_message_code)
1857
1858 -- bid_currency_limit_price precision should not exceed price precision
1859 WHEN
1860 -- added by Allen Yang for Surrogate Bid 2008/09/03
1861 ---------------------------------------------------
1862 p_two_part_tech_surrogate_flag = 'N' AND
1863 ---------------------------------------------------
1864 validate_price_precision(s_bid_currency_limit_price, p_price_precision) = 'F' THEN
1865 INTO pon_interface_errors
1866 (INTERFACE_TYPE,
1867 COLUMN_NAME,
1868 TABLE_NAME,
1869 BATCH_ID,
1870 INTERFACE_LINE_ID,
1871 ERROR_MESSAGE_NAME,
1872 CREATED_BY,
1873 CREATION_DATE,
1874 LAST_UPDATED_BY,
1875 LAST_UPDATE_DATE,
1876 REQUEST_ID,
1877 ENTITY_TYPE,
1878 ENTITY_ATTR_NAME,
1879 ERROR_VALUE_NUMBER,
1880 ERROR_VALUE_DATATYPE,
1881 AUCTION_HEADER_ID,
1882 BID_NUMBER,
1883 LINE_NUMBER,
1887 WORKSHEET_NAME,
1884 EXPIRATION_DATE,
1885 TOKEN1_NAME,
1886 TOKEN1_VALUE,
1888 WORKSHEET_SEQUENCE_NUMBER,
1889 ENTITY_MESSAGE_CODE)
1890 VALUES
1891 (p_interface_type,
1892 decode(p_spreadsheet,
1893 g_xml_upload_mode,
1894 fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
1895 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
1896 'PON_BID_ITEM_PRICES',
1897 p_batch_id,
1898 s_interface_line_id,
1899 'PON_AUCTS_LIMIT_MIN_PREC' || p_suffix,
1900 p_userid,
1901 sysdate,
1902 p_userid,
1903 sysdate,
1904 p_request_id,
1905 'BID_ITEMS',
1906 'BidCurrencyLimitPrice',
1907 s_bid_currency_limit_price,
1908 'NUM',
1909 p_auc_header_id,
1910 p_bid_number,
1911 s_line_number,
1912 g_exp_date,
1913 'LINENUMBER',
1914 s_document_disp_line_number,
1915 s_worksheet_name,
1916 s_worksheet_sequence_number,
1917 s_entity_message_code)
1918
1919 -- price should be lower then the line start price.
1920 -- Will not apply if price is disabled for this line
1921 -- bug 5701482
1922 -- need use the bid currency price and do round up
1923 -- to precision to avoid the small gap caused by
1924 -- currency conversion
1925 -- If the precision is ANY, round up to 10 decimal points
1926 WHEN s_price_disabled_flag = 'N' AND
1927 s_bid_currency_price > ROUND(s_enforced_bid_start_price*s_rate, decode(s_precision, 10000, 10, s_precision))
1928 -- added by Allen Yang for Surrogate Bid 2008/09/03
1929 ---------------------------------------------------
1930 AND p_two_part_tech_surrogate_flag = 'N'
1931 ---------------------------------------------------
1932 THEN
1933 INTO pon_interface_errors
1934 (INTERFACE_TYPE,
1935 COLUMN_NAME,
1936 TABLE_NAME,
1937 BATCH_ID,
1938 INTERFACE_LINE_ID,
1939 ERROR_MESSAGE_NAME,
1940 CREATED_BY,
1941 CREATION_DATE,
1942 LAST_UPDATED_BY,
1943 LAST_UPDATE_DATE,
1944 REQUEST_ID,
1945 ENTITY_TYPE,
1946 ENTITY_ATTR_NAME,
1947 ERROR_VALUE_NUMBER,
1948 ERROR_VALUE_DATATYPE,
1949 AUCTION_HEADER_ID,
1950 BID_NUMBER,
1951 LINE_NUMBER,
1952 EXPIRATION_DATE,
1953 TOKEN1_NAME,
1954 TOKEN1_VALUE,
1955 TOKEN2_NAME,
1956 TOKEN2_VALUE,
1957 TOKEN3_NAME,
1958 TOKEN3_VALUE,
1959 WORKSHEET_NAME,
1960 WORKSHEET_SEQUENCE_NUMBER,
1961 ENTITY_MESSAGE_CODE)
1962 VALUES
1963 (p_interface_type,
1964 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
1965 'PON_BID_ITEM_PRICES',
1966 p_batch_id,
1967 s_interface_line_id,
1968 'PON_BP_LTE_BIDSTARTPRICE' || p_suffix,
1969 p_userid,
1970 sysdate,
1971 p_userid,
1972 sysdate,
1973 p_request_id,
1974 'BID_ITEMS',
1975 'BidCurrencyPrice',
1976 s_bid_currency_price,
1977 'NUM',
1978 p_auc_header_id,
1979 p_bid_number,
1980 s_line_number,
1981 g_exp_date,
1982 'BIDPRICE',
1983 format_price(s_bid_currency_price, l_price_mask, p_price_precision),
1984 'STARTPRICE',
1985 format_price(decode(p_trans_view, 'Y',
1986 s_enforced_bid_start_price,
1987 PON_TRANSFORM_BIDDING_PKG.untransform_one_price( p_auc_header_id,
1988 s_line_number,
1989 s_enforced_bid_start_price,
1990 s_auc_quantity,
1991 p_tpid,
1992 p_vensid)) * p_rate, l_price_mask, p_price_precision),
1993 'LINENUMBER',
1994 s_document_disp_line_number,
1995 s_worksheet_name,
1996 s_worksheet_sequence_number,
1997 s_entity_message_code)
1998
1999 -- When rebidding, the new price must be lower than the old_price
2000 -- by the specified minimum bid decrement, if it was changed.
2001 -- Does not apply to LOT_LINEs
2002 -- Will not apply if price is disabled for this line, or negotiation
2003 -- is not price driver
2004 WHEN s_price_disabled_flag = 'N' AND p_rebid = 'Y' AND p_price_driven = 'Y'
2005 AND p_bid_decr_method <> 'BEST_PRICE'
2006 AND s_price <> s_old_price AND s_group_type <> 'LOT_LINE'
2007 AND s_price + (nvl(s_min_bid_decr,0)/p_rate) > s_old_price
2008 AND s_min_bid_decr IS NOT NULL
2009 -- added by Allen Yang for Surrogate Bid 2008/09/03
2010 ---------------------------------------------------
2011 AND p_two_part_tech_surrogate_flag = 'N'
2012 ---------------------------------------------------
2013 THEN
2014 INTO pon_interface_errors
2015 (INTERFACE_TYPE,
2016 COLUMN_NAME,
2017 TABLE_NAME,
2018 BATCH_ID,
2019 INTERFACE_LINE_ID,
2020 ERROR_MESSAGE_NAME,
2021 CREATED_BY,
2022 CREATION_DATE,
2023 LAST_UPDATED_BY,
2024 LAST_UPDATE_DATE,
2025 REQUEST_ID,
2026 ENTITY_TYPE,
2027 ENTITY_ATTR_NAME,
2028 ERROR_VALUE_NUMBER,
2029 ERROR_VALUE_DATATYPE,
2030 AUCTION_HEADER_ID,
2031 BID_NUMBER,
2032 LINE_NUMBER,
2036 TOKEN2_NAME,
2033 EXPIRATION_DATE,
2034 TOKEN1_NAME,
2035 TOKEN1_VALUE,
2037 TOKEN2_VALUE,
2038 WORKSHEET_NAME,
2039 WORKSHEET_SEQUENCE_NUMBER,
2040 ENTITY_MESSAGE_CODE
2041 )
2042 VALUES
2043 (p_interface_type,
2044 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
2045 'PON_BID_ITEM_PRICES',
2046 p_batch_id,
2047 s_interface_line_id,
2048 'PON_MIN_BID_DECREMENT' || p_suffix,
2049 p_userid,
2050 sysdate,
2051 p_userid,
2052 sysdate,
2053 p_request_id,
2054 'BID_ITEMS',
2055 'Price',
2056 s_price,
2057 'NUM',
2058 p_auc_header_id,
2059 p_bid_number,
2060 s_line_number,
2061 g_exp_date,
2062 'LINENUMBER',
2063 s_document_disp_line_number,
2064 'BIDDECREMENT',
2065 format_price(s_min_bid_decr, l_price_mask, p_price_precision) || ' '||p_bid_curr_code,
2066 s_worksheet_name,
2067 s_worksheet_sequence_number,
2068 s_entity_message_code
2069 )
2070
2071 -- When rebidding, the new price must be lower than the best_bid_price
2072 -- by the specified minimum bid decrement, if suppliers are
2073 -- supposed to reduce by best response price
2074 -- Does not apply to LOT_LINEs
2075 -- Will not apply if price is disabled for this line, or negotiation
2076 -- is not price driven
2077 WHEN s_price_disabled_flag = 'N' AND p_bid_decr_method = 'BEST_PRICE' AND p_price_driven = 'Y'
2078 AND s_group_type <> 'LOT_LINE'
2079 AND p_rebid = 'Y'
2080 AND s_auc_best_bid_price IS NOT NULL
2081 AND s_price <> s_old_price
2082 AND s_price + (nvl(s_min_bid_decr,0)/p_rate) > s_best_bid_price
2083 AND s_min_bid_decr IS NOT NULL
2084 -- added by Allen Yang for Surrogate Bid 2008/09/03
2085 ---------------------------------------------------
2086 AND p_two_part_tech_surrogate_flag = 'N'
2087 ---------------------------------------------------
2088 THEN
2089 INTO pon_interface_errors
2090 (INTERFACE_TYPE,
2091 COLUMN_NAME,
2092 TABLE_NAME,
2093 BATCH_ID,
2094 INTERFACE_LINE_ID,
2095 ERROR_MESSAGE_NAME,
2096 CREATED_BY,
2097 CREATION_DATE,
2098 LAST_UPDATED_BY,
2099 LAST_UPDATE_DATE,
2100 REQUEST_ID,
2101 ENTITY_TYPE,
2102 ENTITY_ATTR_NAME,
2103 ERROR_VALUE_NUMBER,
2104 ERROR_VALUE_DATATYPE,
2105 AUCTION_HEADER_ID,
2106 BID_NUMBER,
2107 LINE_NUMBER,
2108 EXPIRATION_DATE,
2109 TOKEN1_NAME,
2110 TOKEN1_VALUE,
2111 TOKEN2_NAME,
2112 TOKEN2_VALUE,
2113 WORKSHEET_NAME,
2114 WORKSHEET_SEQUENCE_NUMBER,
2115 ENTITY_MESSAGE_CODE
2116 )
2117 VALUES
2118 (p_interface_type,
2119 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
2120 'PON_BID_ITEM_PRICES',
2121 p_batch_id,
2122 s_interface_line_id,
2123 'PON_MIN_BESTBID_DECREMENT' || p_suffix,
2124 p_userid,
2125 sysdate,
2126 p_userid,
2127 sysdate,
2128 p_request_id,
2129 'BID_ITEMS',
2130 'Price',
2131 s_price,
2132 'NUM',
2133 p_auc_header_id,
2134 p_bid_number,
2135 s_line_number,
2136 g_exp_date,
2137 'LINENUMBER',
2138 s_document_disp_line_number,
2139 'BIDDECREMENT',
2140 format_price(s_min_bid_decr, l_price_mask, p_price_precision) || ' '||p_bid_curr_code,
2141 s_worksheet_name,
2142 s_worksheet_sequence_number,
2143 s_entity_message_code
2144 )
2145
2146 -- When rebidding, the new price must be <= old_price, if changed
2147 -- Does not apply to LOT_LINEs
2148 -- Will not apply if price is disabled for this line, or negotiation
2149 -- is not price driver
2150 WHEN s_price_disabled_flag = 'N' AND p_rebid = 'Y' AND p_price_driven = 'Y'
2151 AND s_price <> s_old_price AND s_group_type <> 'LOT_LINE'
2152 AND s_price > decode(p_bid_decr_method,'BEST_PRICE',s_best_bid_price,s_old_price)
2153 AND s_min_bid_decr IS NULL
2154 -- added by Allen Yang for Surrogate Bid 2008/09/03
2155 ---------------------------------------------------
2156 AND p_two_part_tech_surrogate_flag = 'N'
2157 ---------------------------------------------------
2158 THEN
2159 INTO pon_interface_errors
2160 (INTERFACE_TYPE,
2161 COLUMN_NAME,
2162 TABLE_NAME,
2163 BATCH_ID,
2164 INTERFACE_LINE_ID,
2165 ERROR_MESSAGE_NAME,
2166 CREATED_BY,
2167 CREATION_DATE,
2168 LAST_UPDATED_BY,
2169 LAST_UPDATE_DATE,
2170 REQUEST_ID,
2171 ENTITY_TYPE,
2172 ENTITY_ATTR_NAME,
2173 ERROR_VALUE_NUMBER,
2174 ERROR_VALUE_DATATYPE,
2175 AUCTION_HEADER_ID,
2176 BID_NUMBER,
2177 LINE_NUMBER,
2178 EXPIRATION_DATE,
2179 TOKEN1_NAME,
2180 TOKEN1_VALUE,
2181 WORKSHEET_NAME,
2182 WORKSHEET_SEQUENCE_NUMBER,
2183 ENTITY_MESSAGE_CODE)
2184 VALUES
2185 (p_interface_type,
2186 fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
2187 'PON_BID_ITEM_PRICES',
2188 p_batch_id,
2192 sysdate,
2189 s_interface_line_id,
2190 decode(p_bid_decr_method,'BEST_PRICE','PON_BESTBID_PRICE_LOWER','PON_BID_PRICE_LOWER') || p_suffix,
2191 p_userid,
2193 p_userid,
2194 sysdate,
2195 p_request_id,
2196 'BID_ITEMS',
2197 'Price',
2198 s_price,
2199 'NUM',
2200 p_auc_header_id,
2201 p_bid_number,
2202 s_line_number,
2203 g_exp_date,
2204 'LINENUMBER',
2205 s_document_disp_line_number,
2206 s_worksheet_name,
2207 s_worksheet_sequence_number,
2208 s_entity_message_code)
2209
2210 -- proxy_bid_limit_price must be lower than price by the bid minimum
2211 -- bid change (or auction minimum bid change if that is null)
2212 -- Don't do this validation when buyer tries to place a
2213 -- surrogate bid after auction is closed
2214 WHEN (NOT (s_surrog_bid_flag = 'Y' AND
2215 s_close_bidding_date < s_current_date) AND
2216 (s_proxy_bid_limit_price + s_min_bid_change > s_price))
2217 -- added by Allen Yang for Surrogate Bid 2008/09/03
2218 ---------------------------------------------------
2219 AND p_two_part_tech_surrogate_flag = 'N'
2220 ---------------------------------------------------
2221 THEN
2222 INTO pon_interface_errors
2223 (INTERFACE_TYPE,
2224 COLUMN_NAME,
2225 TABLE_NAME,
2226 BATCH_ID,
2227 INTERFACE_LINE_ID,
2228 ERROR_MESSAGE_NAME,
2229 CREATED_BY,
2230 CREATION_DATE,
2231 LAST_UPDATED_BY,
2232 LAST_UPDATE_DATE,
2233 REQUEST_ID,
2234 ENTITY_TYPE,
2235 ENTITY_ATTR_NAME,
2236 ERROR_VALUE_NUMBER,
2237 ERROR_VALUE_DATATYPE,
2238 AUCTION_HEADER_ID,
2239 BID_NUMBER,
2240 LINE_NUMBER,
2241 EXPIRATION_DATE,
2242 TOKEN1_NAME,
2243 TOKEN1_VALUE,
2244 WORKSHEET_NAME,
2245 WORKSHEET_SEQUENCE_NUMBER,
2246 ENTITY_MESSAGE_CODE)
2247 VALUES
2248 (p_interface_type,
2249 decode(p_spreadsheet,
2250 g_xml_upload_mode,
2251 fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
2252 fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
2253 'PON_BID_ITEM_PRICES',
2254 p_batch_id,
2255 s_interface_line_id,
2256 'PON_AUCTS_LIMIT_MIN_LESS_PRI' || p_suffix,
2257 p_userid,
2258 sysdate,
2259 p_userid,
2260 sysdate,
2261 p_request_id,
2262 'BID_ITEMS',
2263 'BidCurrencyLimitPrice',
2264 s_proxy_bid_limit_price,
2265 'NUM',
2266 p_auc_header_id,
2267 p_bid_number,
2268 s_line_number,
2269 g_exp_date,
2270 'LINENUMBER',
2271 s_document_disp_line_number,
2272 s_worksheet_name,
2273 s_worksheet_sequence_number,
2274 s_entity_message_code)
2275
2276 -- There was a previous bid on this line which was deleted
2277 -- Not allowed to unbid when rebidding.
2278 WHEN p_rebid = 'Y' AND s_is_changed_line_flag = 'Y'
2279 AND s_has_bid_flag = 'N' THEN
2280 INTO pon_interface_errors
2281 (INTERFACE_TYPE,
2282 TABLE_NAME,
2283 BATCH_ID,
2284 INTERFACE_LINE_ID,
2285 ERROR_MESSAGE_NAME,
2286 CREATED_BY,
2287 CREATION_DATE,
2288 LAST_UPDATED_BY,
2289 LAST_UPDATE_DATE,
2290 REQUEST_ID,
2291 AUCTION_HEADER_ID,
2292 BID_NUMBER,
2293 LINE_NUMBER,
2294 EXPIRATION_DATE,
2295 TOKEN1_NAME,
2296 TOKEN1_VALUE,
2297 WORKSHEET_NAME,
2298 WORKSHEET_SEQUENCE_NUMBER,
2299 ENTITY_MESSAGE_CODE)
2300 VALUES
2301 (p_interface_type,
2302 'PON_BID_ITEM_PRICES',
2303 p_batch_id,
2304 s_interface_line_id,
2305 'PON_CANNOT_UNBID' || p_suffix,
2306 p_userid,
2307 sysdate,
2308 p_userid,
2309 sysdate,
2310 p_request_id,
2311 p_auc_header_id,
2312 p_bid_number,
2313 s_line_number,
2314 g_exp_date,
2315 'LINENUMBER',
2316 s_document_disp_line_number,
2317 s_worksheet_name,
2318 s_worksheet_sequence_number,
2319 s_entity_message_code)
2320
2321 -- CUMULATIVE price breaks not allowed in blanket agreements
2322 WHEN p_blanket = 'Y' AND p_global = 'Y'
2323 AND s_price_break_type = 'CUMULATIVE'
2324 -- added by Allen Yang for Surrogate Bid 2008/09/03
2325 ---------------------------------------------------
2326 AND p_two_part_tech_surrogate_flag = 'N'
2327 ---------------------------------------------------
2328 THEN
2329 INTO pon_interface_errors
2330 (INTERFACE_TYPE,
2331 TABLE_NAME,
2332 BATCH_ID,
2333 INTERFACE_LINE_ID,
2334 ERROR_MESSAGE_NAME,
2335 CREATED_BY,
2336 CREATION_DATE,
2337 LAST_UPDATED_BY,
2338 LAST_UPDATE_DATE,
2339 REQUEST_ID,
2340 ENTITY_TYPE,
2341 ENTITY_ATTR_NAME,
2342 ERROR_VALUE,
2343 ERROR_VALUE_DATATYPE,
2344 AUCTION_HEADER_ID,
2345 BID_NUMBER,
2346 LINE_NUMBER,
2347 EXPIRATION_DATE,
2348 TOKEN1_NAME,
2349 TOKEN1_VALUE,
2353 VALUES
2350 WORKSHEET_NAME,
2351 WORKSHEET_SEQUENCE_NUMBER,
2352 ENTITY_MESSAGE_CODE)
2354 (p_interface_type,
2355 'PON_BID_ITEM_PRICES',
2356 p_batch_id,
2357 s_interface_line_id,
2358 'PON_AUC_BAD_PBTYPE_GLOBAL',
2359 p_userid,
2360 sysdate,
2361 p_userid,
2362 sysdate,
2363 p_request_id,
2364 'BID_ITEMS',
2365 'PriceBreakType',
2366 s_price_break_type,
2367 'TXT',
2368 p_auc_header_id,
2369 p_bid_number,
2370 s_line_number,
2371 g_exp_date,
2372 'LINENUMBER',
2373 s_document_disp_line_number,
2374 s_worksheet_name,
2375 s_worksheet_sequence_number,
2376 s_entity_message_code)
2377
2378 -- The line is not allowed to have shipments if price_break_type is NONE
2379 WHEN
2380 -- added by Allen Yang for Surrogate Bid 2008/09/03
2381 ---------------------------------------------------
2382 p_two_part_tech_surrogate_flag = 'N' AND
2383 ---------------------------------------------------
2384 s_price_break_type = 'NONE' AND
2385 EXISTS (SELECT bpb.shipment_number
2386 FROM pon_bid_shipments bpb
2387 WHERE bpb.bid_number = p_bid_number
2388 AND bpb.line_number = s_line_number
2389 AND bpb.shipment_type = 'PRICE BREAK')
2390 THEN
2391 INTO pon_interface_errors
2392 (INTERFACE_TYPE,
2393 TABLE_NAME,
2394 BATCH_ID,
2395 INTERFACE_LINE_ID,
2396 ERROR_MESSAGE_NAME,
2397 CREATED_BY,
2398 CREATION_DATE,
2399 LAST_UPDATED_BY,
2400 LAST_UPDATE_DATE,
2401 REQUEST_ID,
2402 ENTITY_TYPE,
2403 ENTITY_ATTR_NAME,
2404 ERROR_VALUE,
2405 ERROR_VALUE_DATATYPE,
2406 AUCTION_HEADER_ID,
2407 BID_NUMBER,
2408 LINE_NUMBER,
2409 EXPIRATION_DATE,
2410 TOKEN1_NAME,
2411 TOKEN1_VALUE,
2412 WORKSHEET_NAME,
2413 WORKSHEET_SEQUENCE_NUMBER,
2414 ENTITY_MESSAGE_CODE)
2415 VALUES
2416 (p_interface_type,
2417 'PON_BID_ITEM_PRICES',
2418 p_batch_id,
2419 s_interface_line_id,
2420 'PON_AUC_BAD_PBTYPE_SHIPS',
2421 p_userid,
2422 sysdate,
2423 p_userid,
2424 sysdate,
2425 p_request_id,
2426 'BID_ITEMS',
2427 'PriceBreakType',
2428 s_price_break_type,
2429 'TXT',
2430 p_auc_header_id,
2431 p_bid_number,
2432 s_line_number,
2433 g_exp_date,
2434 'LINENUMBER',
2435 s_document_disp_line_number,
2436 s_worksheet_name,
2437 s_worksheet_sequence_number,
2438 s_entity_message_code)
2439 SELECT
2440 decode(p_percent_decr, 'Y',
2441 p_min_bid_decr * (bl.price)/100, --bug 7601028:calculating the minimum decrement amount on the basis of previous bid
2442 p_min_bid_decr) s_min_bid_decr,
2443 decode(p_percent_decr, 'Y',
2444 nvl(p_min_bid_change, p_min_bid_decr) * bl.price/100, --bug 7601028:calculating the minimum change amount on the basis of previous bid
2445 nvl(p_min_bid_change, p_min_bid_decr)) s_min_bid_change,
2446 al.group_type s_group_type,
2447 bl.bid_currency_price s_bid_currency_price,
2448 bl.bid_currency_trans_price s_bid_currency_trans_price,
2449 bl.bid_currency_limit_price s_bid_currency_limit_price,
2450 bl.price s_price,
2451 bl.proxy_bid_limit_price s_proxy_bid_limit_price,
2452 bl.price_break_type s_price_break_type,
2453 bl.display_price_factors_flag s_display_price_factors_flag,
2454 bl.old_price s_old_price,
2455 bl.has_bid_flag s_has_bid_flag,
2456 bl.is_changed_line_flag s_is_changed_line_flag,
2457 al.quantity s_auc_quantity,
2458 al.bid_start_price s_bid_start_price,
2459 nvl(al.price_disabled_flag, 'N') s_price_disabled_flag,
2460 bl.line_number s_line_number,
2461 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
2462 g_xml_upload_mode, bl.interface_line_id,
2463 to_number(null)) s_interface_line_id,
2464 al.document_disp_line_number s_document_disp_line_number,
2465 nvl(al.best_bid_price,0) s_best_bid_price,
2466 al.best_bid_price s_auc_best_bid_price,
2467 bl.bid_start_price s_enforced_bid_start_price,
2468 bh.surrog_bid_flag s_surrog_bid_flag,
2469 al.close_bidding_date s_close_bidding_date,
2470 sysdate s_current_date,
2471 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
2472 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
2473 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code,
2474 bh.rate s_rate,
2475 bh.number_price_decimals s_precision
2476 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl,
2477 pon_bid_headers bh
2478 WHERE al.auction_header_id = p_auc_header_id
2479 AND bh.bid_number = p_bid_number
2480 AND bl.bid_number = p_bid_number
2481 AND al.line_number = bl.line_number
2482 AND bl.is_changed_line_flag = 'Y'
2483 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
2487 -- STEP 3: perform rel 12 and post rel 12 validations.
2484 AND bh.bid_number = bl.bid_number
2485 AND (bh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
2486
2488 -- Validations 1-10 in this statement
2489 INSERT ALL
2490
2491 -- Retainage rate should be between 0 and 100
2492 WHEN
2493 -- added by Allen Yang for Surrogate Bid 2008/09/03
2494 ---------------------------------------------------
2495 p_two_part_tech_surrogate_flag = 'N' AND
2496 ---------------------------------------------------
2497 retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
2498 INTO pon_interface_errors
2499 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2500 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2501 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2502 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2503 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2504 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2505 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2506 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2507 VALUES
2508 (p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
2509 p_batch_id, s_interface_line_id, 'PON_RTNG_RATE_WRONG_L', --2
2510 p_userid, sysdate, p_userid, --3
2511 sysdate, p_request_id, 'BID_ITEMS', --4
2512 'RetainageRatePercent', retainage_rate_percent, 'NUM', --5
2513 p_auc_header_id, p_bid_number, s_line_number, --6
2514 g_exp_date, 'LINENUM', s_document_disp_line_number,
2515 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2516 -- bid_curr_max_retainage_amt should be greater than equal to 0
2517 WHEN
2518 -- added by Allen Yang for Surrogate Bid 2008/09/03
2519 ---------------------------------------------------
2520 p_two_part_tech_surrogate_flag = 'N' AND
2521 ---------------------------------------------------
2522 bid_curr_max_retainage_amt IS NOT NULL AND bid_curr_max_retainage_amt < 0 THEN
2523 INTO pon_interface_errors
2524 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2525 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2526 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2527 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2528 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2529 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2530 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2531 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2532 VALUES
2533 (p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
2534 p_batch_id, s_interface_line_id, 'PON_MAX_RTNG_WRONG_L', --2
2535 p_userid, sysdate, p_userid, --3
2536 sysdate, p_request_id, 'BID_ITEMS', --4
2537 'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'NUM', --5
2538 p_auc_header_id, p_bid_number, s_line_number, --6
2539 g_exp_date, 'LINENUM', s_document_disp_line_number,
2540 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2541 -- bid_curr_advance_amount should be greater than equal to 0
2542 WHEN
2543 -- added by Allen Yang for Surrogate Bid 2008/09/03
2544 ---------------------------------------------------
2545 p_two_part_tech_surrogate_flag = 'N' AND
2546 ---------------------------------------------------
2547 bid_curr_advance_amount IS NOT NULL AND bid_curr_advance_amount < 0 THEN
2548 INTO pon_interface_errors
2549 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2550 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2551 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2552 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2553 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2554 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2555 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2556 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2557 VALUES
2558 (p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
2559 p_batch_id, s_interface_line_id, 'PON_ADV_AMT_WRONG_L', --2
2560 p_userid, sysdate, p_userid, --3
2561 sysdate, p_request_id, 'BID_ITEMS', --4
2562 'BidCurrAdvanceAmount', bid_curr_advance_amount, 'NUM', --5
2563 p_auc_header_id, p_bid_number, s_line_number, --6
2564 g_exp_date, 'LINENUM', s_document_disp_line_number,
2565 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2569 ---------------------------------------------------
2566 -- progress_pymt_rate_percent should be between 0 and 100
2567 WHEN
2568 -- added by Allen Yang for Surrogate Bid 2008/09/03
2570 p_two_part_tech_surrogate_flag = 'N' AND
2571 ---------------------------------------------------
2572 progress_pymt_rate_percent IS NOT NULL AND
2573 (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) THEN
2574 INTO pon_interface_errors
2575 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2576 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2577 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2578 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2579 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2580 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2581 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2582 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2583 VALUES
2584 (p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
2585 p_batch_id, s_interface_line_id, 'PON_PROG_PYMT_RATE_WRONG_L', --2
2586 p_userid, sysdate, p_userid, --3
2587 sysdate, p_request_id, 'BID_ITEMS', --4
2588 'ProgressPymtRatePercent', progress_pymt_rate_percent, 'NUM', --5
2589 p_auc_header_id, p_bid_number, s_line_number, --6
2590 g_exp_date, 'LINENUM', s_document_disp_line_number,
2591 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2592 -- recoupment_rate_percent should be between 0 and 100
2593 WHEN
2594 -- added by Allen Yang for Surrogate Bid 2008/09/03
2595 ---------------------------------------------------
2596 p_two_part_tech_surrogate_flag = 'N' AND
2597 ---------------------------------------------------
2598 recoupment_rate_percent IS NOT NULL AND
2599 (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
2600 INTO pon_interface_errors
2601 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2602 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2603 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2604 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2605 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2606 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2607 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2608 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2609 VALUES
2610 (p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
2611 p_batch_id, s_interface_line_id, 'PON_RECOUP_RATE_WRONG', --2
2612 p_userid, sysdate, p_userid, --3
2613 sysdate, p_request_id, 'BID_ITEMS', --4
2614 'RecoupmentRatePercent', recoupment_rate_percent, 'NUM', --5
2615 p_auc_header_id, p_bid_number, s_line_number, --6
2616 g_exp_date, 'LINENUM', s_document_disp_line_number,
2617 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2618 -- progress_pymt_rate_percent is mandatory for progress_payment_type = FINANCE
2619 WHEN
2620 -- added by Allen Yang for Surrogate Bid 2008/09/03
2621 ---------------------------------------------------
2622 p_two_part_tech_surrogate_flag = 'N' AND
2623 ---------------------------------------------------
2624 progress_payment_type = 'FINANCE' AND contract_type='STANDARD'
2625 AND s_group_type NOT IN ('GROUP','LOT_LINE') AND progress_pymt_rate_percent IS NULL THEN
2626 INTO pon_interface_errors
2627 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2628 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2629 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2630 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2631 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2632 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2633 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2634 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
2635 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2636 VALUES
2637 (p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
2638 p_batch_id, s_interface_line_id, 'PON_PROG_PYMT_NEEDED_L', --2
2639 p_userid, sysdate, p_userid, --3
2640 sysdate, p_request_id, 'BID_ITEMS', --4
2641 'ProgressPymtRatePercent', progress_pymt_rate_percent, 'TXT', --5
2642 p_auc_header_id, p_bid_number, s_line_number, --6
2643 NULL, g_exp_date, 'LINENUM', --7
2644 s_document_disp_line_number, null, null,
2648 WHEN
2645 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2646
2647 -- retainage_rate_percent is mandatory if retainage is negotiable
2649 -- added by Allen Yang for Surrogate Bid 2008/09/03
2650 ---------------------------------------------------
2651 p_two_part_tech_surrogate_flag = 'N' AND
2652 ---------------------------------------------------
2653 retainage_negotiable_flag = 'Y'
2654 AND s_group_type NOT IN ('GROUP','LOT_LINE') AND retainage_rate_percent IS NULL THEN
2655 INTO pon_interface_errors
2656 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2657 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2658 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2659 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2660 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2661 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2662 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2663 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2664 VALUES
2665 (p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
2666 p_batch_id, s_interface_line_id, 'PON_RETAINAGE_NEEDED_L', --2
2667 p_userid, sysdate, p_userid, --3
2668 sysdate, p_request_id, 'BID_ITEMS', --4
2669 'RetainageRatePercent', retainage_rate_percent, 'TXT', --5
2670 p_auc_header_id, p_bid_number, s_line_number, --6
2671 NULL, g_exp_date, 'LINENUM', --7
2672 s_document_disp_line_number, null, null,
2673 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2674 -- bid_curr_max_retainage_amount is mandatory if maximum retainage amount is negotiable
2675 WHEN
2676 -- added by Allen Yang for Surrogate Bid 2008/09/03
2677 ---------------------------------------------------
2678 p_two_part_tech_surrogate_flag = 'N' AND
2679 ---------------------------------------------------
2680 max_retainage_negotiable_flag = 'Y'
2681 AND s_group_type NOT IN ('GROUP','LOT_LINE') AND bid_curr_max_retainage_amt IS NULL THEN
2682 INTO pon_interface_errors
2683 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2684 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2685 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2686 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2687 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2688 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2689 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2690 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
2691 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2692 VALUES
2693 (p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
2694 p_batch_id, s_interface_line_id, 'PON_MAX_RETAINAGE_NEEDED_L', --2
2695 p_userid, sysdate, p_userid, --3
2696 sysdate, p_request_id, 'BID_ITEMS', --4
2697 'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'TXT', --5
2698 p_auc_header_id, p_bid_number, s_line_number, --6
2699 NULL, g_exp_date, 'LINENUM', --7
2700 s_document_disp_line_number, null, null,
2701 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2702
2703 -- recoupment_rate_percent is mandatory if recoupment rate is negotiable
2704 WHEN
2705 -- added by Allen Yang for Surrogate Bid 2008/09/03
2706 ---------------------------------------------------
2707 p_two_part_tech_surrogate_flag = 'N' AND
2708 ---------------------------------------------------
2709 (s_group_type NOT IN ('GROUP','LOT_LINE') AND
2710 ((recoupment_negotiable_flag = 'Y' AND recoupment_rate_percent IS NULL) OR
2711 ((progress_pymt_rate_percent IS NOT NULL OR bid_curr_advance_amount IS NOT NULL) AND
2712 recoupment_rate_percent IS NULL)))
2713 THEN
2714 INTO pon_interface_errors
2715 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2716 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2717 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2718 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2719 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2720 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2721 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2722 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2723 VALUES
2724 (p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
2728 'RecoupmentRatePercent', recoupment_rate_percent, 'TXT', --5
2725 p_batch_id, s_interface_line_id, 'PON_RECUP_RATE_NEEDED_L', --2
2726 p_userid, sysdate, p_userid, --3
2727 sysdate, p_request_id, 'BID_ITEMS', --4
2729 p_auc_header_id, p_bid_number, s_line_number, --6
2730 NULL, g_exp_date, 'LINENUM', --7
2731 s_document_disp_line_number, null, null,
2732 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2733 -- bid_curr_advance_amount is mandatory if advance amount is negotiable
2734 WHEN
2735 -- added by Allen Yang for Surrogate Bid 2008/09/03
2736 ---------------------------------------------------
2737 p_two_part_tech_surrogate_flag = 'N' AND
2738 ---------------------------------------------------
2739 advance_negotiable_flag = 'Y'
2740 AND s_group_type NOT IN ('GROUP','LOT_LINE')
2741 AND bid_curr_advance_amount IS NULL THEN
2742 INTO pon_interface_errors
2743 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2744 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2745 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2746 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2747 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2748 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2749 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2750 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2751 VALUES
2752 (p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
2753 p_batch_id, s_interface_line_id, 'PON_ADVANCE_AMT_NEEDED_L', --2
2754 p_userid, sysdate, p_userid, --3
2755 sysdate, p_request_id, 'BID_ITEMS', --4
2756 'BidCurrAdvanceAmount', bid_curr_advance_amount, 'TXT', --5
2757 p_auc_header_id, p_bid_number, s_line_number, --6
2758 NULL, g_exp_date, 'LINENUM', --7
2759 s_document_disp_line_number, null, null, s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2760
2761 SELECT
2762 al.group_type s_group_type,
2763 bl.quantity s_bid_quantity,
2764 bl.bid_currency_unit_price s_bid_currency_unit_price,
2765 bl.bid_curr_advance_amount,
2766 bl.bid_curr_max_retainage_amt,
2767 bl.recoupment_rate_percent,
2768 bl.retainage_rate_percent,
2769 bl.progress_pymt_rate_percent,
2770 bl.has_bid_payments_flag,
2771 bl.line_number s_line_number,
2772 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
2773 g_xml_upload_mode, bl.interface_line_id,
2774 to_number(null)) s_interface_line_id,
2775 al.document_disp_line_number s_document_disp_line_number,
2776 al.order_type_lookup_code s_order_type_lookup_code,
2777 al.quantity s_auc_quantity,
2778 pah.progress_payment_type,
2779 pah.advance_negotiable_flag,
2780 pah.retainage_negotiable_flag,
2781 pah.max_retainage_negotiable_flag,
2782 pah.recoupment_negotiable_flag,
2783 pah.progress_pymt_negotiable_flag,
2784 pah.contract_type,
2785 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
2786 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
2787 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code
2788 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl, pon_auction_headers_all pah
2789 WHERE al.auction_header_id = p_auc_header_id
2790 AND bl.bid_number = p_bid_number
2791 AND pah.auction_header_id = p_auc_header_id
2792 AND al.line_number = bl.line_number
2793 AND bl.is_changed_line_flag = 'Y'
2794 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
2795
2796 -- STEP 4: rel 12 and post rel 12 validations
2797 -- validations 11-20
2798 INSERT ALL
2799
2800 WHEN
2801 -- added by Allen Yang for Surrogate Bid 2008/09/03
2802 ---------------------------------------------------
2803 p_two_part_tech_surrogate_flag = 'N' AND
2804 ---------------------------------------------------
2805 bid_curr_advance_amount IS NOT NULL AND s_bid_currency_unit_price IS NOT NULL AND
2806 (bid_curr_advance_amount > (NVL(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
2807 INTO pon_interface_errors
2808 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2809 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2810 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2811 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2812 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2813 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2814 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, --7
2815 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2816 VALUES
2820 sysdate, p_request_id, 'BID_ITEMS', --4
2817 (p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
2818 p_batch_id, s_interface_line_id, 'PON_ADV_AMT_MORE_L', --2
2819 p_userid, sysdate, p_userid, --3
2821 NULL, bid_curr_advance_amount, 'NUM', --5
2822 p_auc_header_id, p_bid_number, s_line_number, --6
2823 g_exp_date, 'LINENUM', s_document_disp_line_number, --7
2824 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2825 WHEN
2826 -- added by Allen Yang for Surrogate Bid 2008/09/03
2827 ---------------------------------------------------
2828 p_two_part_tech_surrogate_flag = 'N' AND
2829 ---------------------------------------------------
2830 (p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'FINANCE' AND
2831 recoupment_rate_percent IS NOT NULL AND progress_pymt_rate_percent IS NOT NULL AND
2832 s_bid_currency_unit_price IS NOT NULL AND
2833 recoupment_rate_percent < (((((progress_pymt_rate_percent/100) * (SELECT nvl(sum(nvl(bid_currency_price,0)*nvl(quantity,nvl(s_bid_quantity,1))),0)
2834 FROM PON_BID_PAYMENTS_SHIPMENTS p_bps
2835 WHERE p_bps.auction_header_id=p_auc_header_id
2836 AND p_bps.bid_line_number=s_line_number
2837 AND p_bps.bid_number=p_bid_number))
2838 + nvl(bid_curr_advance_amount,0)) * 100)/((nvl(s_bid_quantity, 1) * s_bid_currency_unit_price))) THEN
2839 INTO pon_interface_errors
2840 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2841 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2842 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2843 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2844 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2845 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2846 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,--7
2847 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2848 VALUES
2849 (p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
2850 p_batch_id, s_interface_line_id, 'PON_RECOUP_LESS_THAN_PYMT_L', --2
2851 p_userid, sysdate, p_userid, --3
2852 sysdate, p_request_id, 'BID_ITEMS', --4
2853 NULL, recoupment_rate_percent, 'NUM', --5
2854 p_auc_header_id, p_bid_number, s_line_number, --6
2855 g_exp_date, 'LINENUM', s_document_disp_line_number,--7
2856 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2857 WHEN
2858 -- added by Allen Yang for Surrogate Bid 2008/09/03
2859 ---------------------------------------------------
2860 p_two_part_tech_surrogate_flag = 'N' AND
2861 ---------------------------------------------------
2862 (p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'ACTUAL' AND
2863 recoupment_rate_percent IS NOT NULL AND bid_curr_advance_amount IS NOT NULL AND
2864 s_bid_currency_unit_price IS NOT NULL AND
2865 recoupment_rate_percent <((bid_curr_advance_amount * 100)/(nvl(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
2866 INTO pon_interface_errors
2867 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2868 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2869 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2870 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2871 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2872 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2873 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,--7
2874 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2875 VALUES
2876 (p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
2877 p_batch_id, s_interface_line_id, 'PON_RECOUP_LESS_THAN_ADV_L', --2
2878 p_userid, sysdate, p_userid, --3
2879 sysdate, p_request_id, 'BID_ITEMS', --4
2880 NULL, recoupment_rate_percent, 'NUM', --5
2881 p_auc_header_id, p_bid_number, s_line_number, --6
2882 g_exp_date, 'LINENUM', s_document_disp_line_number,--7
2883 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2884 WHEN
2885 -- added by Allen Yang for Surrogate Bid 2008/09/03
2886 ---------------------------------------------------
2887 p_two_part_tech_surrogate_flag = 'N' AND
2888 ---------------------------------------------------
2889 (p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'ACTUAL' AND
2890 s_group_type NOT IN ('GROUP','LOT_LINE') AND
2891 has_bid_payments_flag = 'Y' AND
2892 s_bid_currency_unit_price IS NOT NULL AND
2896 AND pbps.bid_number = p_bid_number
2893 (nvl(s_bid_quantity,1)* s_bid_currency_unit_price) <> (SELECT nvl(sum(NVL(pbps.bid_currency_price,0) * NVL(pbps.quantity,nvl(s_bid_quantity,1))),0)
2894 FROM pon_bid_payments_shipments pbps
2895 WHERE pbps.auction_header_id = p_auc_header_id
2897 AND pbps.bid_line_number = s_line_number) THEN
2898 INTO pon_interface_errors
2899 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2900 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2901 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2902 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2903 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2904 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2905 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2906 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2907 VALUES
2908 (p_interface_type, NULL, 'PON_BID_ITEM_PRICES', --1
2909 p_batch_id, s_interface_line_id, 'PON_PYMNT_AMT_MORE_ACTUAL', --2
2910 p_userid, sysdate, p_userid, --3
2911 sysdate, p_request_id, 'BID_ITEMS', --4
2912 NULL, NULL, 'TXT', --5
2913 p_auc_header_id, p_bid_number, s_line_number, --6
2914 g_exp_date, 'LINENUM', s_document_disp_line_number,
2915 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2916 WHEN
2917 -- added by Allen Yang for Surrogate Bid 2008/09/03
2918 ---------------------------------------------------
2919 p_two_part_tech_surrogate_flag = 'N' AND
2920 ---------------------------------------------------
2921 (p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode ) AND progress_payment_type = 'FINANCE' AND
2922 has_bid_payments_flag = 'Y' AND
2923 s_group_type NOT IN ('GROUP','LOT_LINE') AND
2924 s_bid_currency_unit_price IS NOT NULL AND
2925 (nvl(s_bid_quantity,1)* s_bid_currency_unit_price)- nvl(bid_curr_advance_amount,0) < (SELECT nvl(sum(nvl(bid_currency_price,0) * DECODE(s_order_type_lookup_code,'GOODS',NVL(s_auc_quantity,1),NVL(quantity,1))),0)
2926 FROM pon_bid_payments_shipments
2927 WHERE auction_header_id = p_auc_header_id
2928 AND bid_number = p_bid_number
2929 AND bid_line_number = s_line_number) THEN
2930 INTO pon_interface_errors
2931 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2932 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2933 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2934 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2935 ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
2936 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2937 EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
2938 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
2939 VALUES
2940 (p_interface_type, NULL, 'PON_BID_ITEM_PRICES', --1
2941 p_batch_id, s_interface_line_id, 'PON_PYMNT_AMT_MORE_FINANCE', --2
2942 p_userid, sysdate, p_userid, --3
2943 sysdate, p_request_id, 'BID_ITEMS', --4
2944 NULL, NULL, 'TXT', --5
2945 p_auc_header_id, p_bid_number, s_line_number, --6
2946 g_exp_date, 'LINENUM', s_document_disp_line_number,
2947 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
2948 -- bid_curr_advance_amount should not exceed currency precision
2949 WHEN
2950 -- added by Allen Yang for Surrogate Bid 2008/09/03
2951 ---------------------------------------------------
2952 p_two_part_tech_surrogate_flag = 'N' AND
2953 ---------------------------------------------------
2954 bid_curr_advance_amount IS NOT NULL AND
2955 validate_currency_precision(bid_curr_advance_amount, p_amt_precision) = 'F' THEN
2956 INTO pon_interface_errors
2957 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2958 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2959 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2960 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2961 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2962 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2963 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2964 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
2965 WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2966 VALUES
2967 (p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
2968 p_batch_id, s_interface_line_id, 'PON_LINEAMT_INVALID_PRECISION', --2
2969 p_userid, sysdate, p_userid, --3
2973 NULL, g_exp_date, 'LINENUM', --7
2970 sysdate, p_request_id, 'BID_ITEMS', --4
2971 'BidCurrAdvanceAmount', bid_curr_advance_amount, 'NUM', --5
2972 p_auc_header_id, p_bid_number, s_line_number, --6
2974 s_document_disp_line_number, 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'),
2975 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
2976 -- bid_curr_max_retainage_amt should not exceed currency precision
2977 WHEN
2978 -- added by Allen Yang for Surrogate Bid 2008/09/03
2979 ---------------------------------------------------
2980 p_two_part_tech_surrogate_flag = 'N' AND
2981 ---------------------------------------------------
2982 bid_curr_max_retainage_amt IS NOT NULL AND
2983 validate_currency_precision(bid_curr_max_retainage_amt, p_amt_precision) = 'F' THEN
2984 INTO pon_interface_errors
2985 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
2986 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
2987 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
2988 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
2989 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
2990 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
2991 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
2992 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
2993 VALUES
2994 (p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
2995 p_batch_id, s_interface_line_id, 'PON_LINEAMT_INVALID_PRECISION', --2
2996 p_userid, sysdate, p_userid, --3
2997 sysdate, p_request_id, 'BID_ITEMS', --4
2998 'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'NUM', --5
2999 p_auc_header_id, p_bid_number, s_line_number, --6
3000 NULL, g_exp_date, 'LINENUM', --7
3001 s_document_disp_line_number, 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'),
3002 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
3003 -- progress_pymt_rate_percent should not exceed currency precision
3004 WHEN
3005 -- added by Allen Yang for Surrogate Bid 2008/09/03
3006 ---------------------------------------------------
3007 p_two_part_tech_surrogate_flag = 'N' AND
3008 ---------------------------------------------------
3009 progress_pymt_rate_percent IS NOT NULL AND
3010 validate_currency_precision(progress_pymt_rate_percent, 2) = 'F' THEN
3011 INTO pon_interface_errors
3012 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3013 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3014 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3015 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3016 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
3017 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
3018 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
3019 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
3020 VALUES
3021 (p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
3022 p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
3023 p_userid, sysdate, p_userid, --3
3024 sysdate, p_request_id, 'BID_ITEMS', --4
3025 'ProgressPymtRatePercent', progress_pymt_rate_percent, 'NUM', --5
3026 p_auc_header_id, p_bid_number, s_line_number, --6
3027 NULL, g_exp_date, 'LINENUM', --7
3028 s_document_disp_line_number, 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'),
3029 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
3030 -- retainage_rate_percent should not exceed currency precision
3031 WHEN
3032 -- added by Allen Yang for Surrogate Bid 2008/09/03
3033 ---------------------------------------------------
3034 p_two_part_tech_surrogate_flag = 'N' AND
3035 ---------------------------------------------------
3036 retainage_rate_percent IS NOT NULL AND
3037 validate_currency_precision(retainage_rate_percent, 2) = 'F' THEN
3038 INTO pon_interface_errors
3039 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3040 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3041 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3042 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3043 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
3044 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
3045 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
3049 p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
3046 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
3047 VALUES
3048 (p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
3050 p_userid, sysdate, p_userid, --3
3051 sysdate, p_request_id, 'BID_ITEMS', --4
3052 'RetainageRatePercent', retainage_rate_percent, 'NUM', --5
3053 p_auc_header_id, p_bid_number, s_line_number, --6
3054 NULL, g_exp_date, 'LINENUM', --7
3055 s_document_disp_line_number, 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RETAINAGE_RATE'),
3056 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
3057 -- recoupment_rate_percent should not exceed currency precision
3058 WHEN
3059 -- added by Allen Yang for Surrogate Bid 2008/09/03
3060 ---------------------------------------------------
3061 p_two_part_tech_surrogate_flag = 'N' AND
3062 ---------------------------------------------------
3063 recoupment_rate_percent IS NOT NULL AND
3064 validate_currency_precision(recoupment_rate_percent, 2) = 'F' THEN
3065 INTO pon_interface_errors
3066 (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3067 BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3068 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3069 LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3070 ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
3071 AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
3072 BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
3073 TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME,WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
3074 VALUES
3075 (p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
3076 p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
3077 p_userid, sysdate, p_userid, --3
3078 sysdate, p_request_id, 'BID_ITEMS', --4
3079 'RecoupmentRatePercent', recoupment_rate_percent, 'NUM', --5
3080 p_auc_header_id, p_bid_number, s_line_number, --6
3081 NULL, g_exp_date, 'LINENUM', --7
3082 s_document_disp_line_number, 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RECOUPMENT_RATE'),
3083 s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
3084 SELECT
3085 al.group_type s_group_type,
3086 bl.quantity s_bid_quantity,
3087 bl.bid_currency_unit_price s_bid_currency_unit_price,
3088 bl.bid_curr_advance_amount,
3089 bl.bid_curr_max_retainage_amt,
3090 bl.recoupment_rate_percent,
3091 bl.retainage_rate_percent,
3092 bl.progress_pymt_rate_percent,
3093 bl.has_bid_payments_flag,
3094 bl.line_number s_line_number,
3095 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
3096 g_xml_upload_mode, bl.interface_line_id,
3097 to_number(null)) s_interface_line_id,
3098 al.document_disp_line_number s_document_disp_line_number,
3099 al.order_type_lookup_code s_order_type_lookup_code,
3100 al.quantity s_auc_quantity,
3101 pah.progress_payment_type,
3102 pah.advance_negotiable_flag,
3103 pah.retainage_negotiable_flag,
3104 pah.max_retainage_negotiable_flag,
3105 pah.recoupment_negotiable_flag,
3106 pah.progress_pymt_negotiable_flag,
3107 pah.contract_type,
3108 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
3109 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
3110 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code
3111 FROM pon_auction_item_prices_all al, pon_bid_item_prices bl, pon_auction_headers_all pah
3112 WHERE al.auction_header_id = p_auc_header_id
3113 AND bl.bid_number = p_bid_number
3114 AND pah.auction_header_id = p_auc_header_id
3115 AND al.line_number = bl.line_number
3116 AND bl.is_changed_line_flag = 'Y'
3117 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
3118
3119 END validate_lines;
3120
3121 PROCEDURE validate_requirements
3122 (
3123 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3124 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3125 p_interface_type IN pon_interface_errors.interface_type%TYPE,
3126 p_userid IN pon_interface_errors.created_by%TYPE,
3127 p_spreadsheet IN VARCHAR2,
3128 p_suffix IN VARCHAR2,
3129 p_batch_id IN pon_interface_errors.batch_id%TYPE,
3130 p_request_id IN pon_interface_errors.request_id%TYPE
3131 -- added by Allen Yang for Surrogate Bid 2008/09/03
3132 ---------------------------------------------------
3133 , p_two_part_tech_surrogate_flag IN VARCHAR2
3134 ---------------------------------------------------
3135 ) IS
3136 BEGIN
3137
3138 INSERT FIRST
3139
3140 -- value must be entered if it is a required requirement
3144 AND p_two_part_tech_surrogate_flag = 'N') OR (s_mandatory_flag = 'Y'
3141 WHEN (s_mandatory_flag = 'Y' AND s_value IS null
3142 --added by Allen Yang for Surrogate Bid 2008/09/03
3143 -------------------------------------------------------------------
3145 AND s_value IS null AND p_two_part_tech_surrogate_flag = 'Y'
3146 AND s_two_part_section_type = 'TECHNICAL')
3147 -------------------------------------------------------------------
3148 THEN
3149 INTO pon_interface_errors
3150 (INTERFACE_TYPE,
3151 COLUMN_NAME,
3152 TABLE_NAME,
3153 BATCH_ID,
3154 INTERFACE_LINE_ID,
3155 ERROR_MESSAGE_NAME,
3156 CREATED_BY,
3157 CREATION_DATE,
3158 LAST_UPDATED_BY,
3159 LAST_UPDATE_DATE,
3160 REQUEST_ID,
3161 ENTITY_TYPE,
3162 ENTITY_ATTR_NAME,
3163 ERROR_VALUE_DATATYPE,
3164 AUCTION_HEADER_ID,
3165 BID_NUMBER,
3166 ATTRIBUTE_NAME,
3167 EXPIRATION_DATE,
3168 TOKEN1_NAME,
3169 TOKEN1_VALUE,
3170 WORKSHEET_NAME,
3171 WORKSHEET_SEQUENCE_NUMBER,
3172 ENTITY_MESSAGE_CODE)
3173 VALUES
3174 (p_interface_type,
3175 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
3176 'PON_BID_ATTRIBUTE_VALUES',
3177 p_batch_id,
3178 s_interface_line_id,
3179 'PON_AUC_HDR_ATTR_REQ' || p_suffix,
3180 p_userid,
3181 sysdate,
3182 p_userid,
3183 sysdate,
3184 p_request_id,
3185 'BID_ATTRS',
3186 'Value',
3187 'TXT',
3188 p_auc_header_id,
3189 p_bid_number,
3190 s_attribute_name,
3191 g_exp_date,
3192 'ATTRIBUTENAME',
3193 s_trunc_attr_name,
3194 s_worksheet_name,
3195 s_worksheet_sequence_number,
3196 s_entity_message_code)
3197
3198 -- value must be among buyer specified values if type is LOV
3199 WHEN p_spreadsheet in (g_txt_upload_mode, g_xml_upload_mode) AND s_scoring_type = 'LOV' AND s_value is not NULL AND NOT EXISTS
3200 (SELECT bs.score
3201 FROM pon_attribute_scores bs
3202 WHERE bs.auction_header_id = p_auc_header_id
3203 AND bs.line_number = s_line_number
3204 AND bs.attribute_sequence_number = s_sequence_number
3205 AND bs.value = s_value) THEN
3206 INTO pon_interface_errors
3207 (INTERFACE_TYPE,
3208 COLUMN_NAME,
3209 TABLE_NAME,
3210 BATCH_ID,
3211 INTERFACE_LINE_ID,
3212 ERROR_MESSAGE_NAME,
3213 CREATED_BY,
3214 CREATION_DATE,
3215 LAST_UPDATED_BY,
3216 LAST_UPDATE_DATE,
3217 REQUEST_ID,
3218 ENTITY_TYPE,
3219 ENTITY_ATTR_NAME,
3220 ERROR_VALUE,
3221 ERROR_VALUE_DATATYPE,
3222 AUCTION_HEADER_ID,
3223 BID_NUMBER,
3224 LINE_NUMBER,
3225 ATTRIBUTE_NAME,
3226 EXPIRATION_DATE,
3227 WORKSHEET_NAME,
3228 WORKSHEET_SEQUENCE_NUMBER,
3229 ENTITY_MESSAGE_CODE)
3230 VALUES
3231 (p_interface_type,
3232 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
3233 'PON_BID_ATTRIBUTE_VALUES',
3234 p_batch_id,
3235 s_interface_line_id,
3236 'PON_AUC_INVALID_ATTR_VALUE' || p_suffix,
3237 p_userid,
3238 sysdate,
3239 p_userid,
3240 sysdate,
3241 p_request_id,
3242 'BID_ATTRS',
3243 'Value',
3244 s_value,
3245 'TXT',
3246 p_auc_header_id,
3247 p_bid_number,
3248 s_line_number,
3249 s_attribute_name,
3250 g_exp_date,
3251 s_worksheet_name,
3252 s_worksheet_sequence_number,
3253 s_entity_message_code)
3254
3255 SELECT
3256 ba.value s_value,
3257 ba.line_number s_line_number,
3258 ba.sequence_number s_sequence_number,
3259 ba.attribute_name s_attribute_name,
3260 substr(ba.attribute_name, 0, 2000) s_trunc_attr_name,
3261 aa.mandatory_flag s_mandatory_flag,
3262 aa.scoring_type s_scoring_type,
3263 decode(p_spreadsheet, g_xml_upload_mode, ba.interface_line_id, null) s_interface_line_id,
3264 decode(p_spreadsheet, g_xml_upload_mode, ba.worksheet_name, null) s_worksheet_name,
3265 decode(p_spreadsheet, g_xml_upload_mode, ba.worksheet_sequence_number, null) s_worksheet_sequence_number,
3266 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_REQUIREMENTS', null) s_entity_message_code
3267 --added by Allen Yang for Surrogate Bid 2008/09/03
3268 ---------------------------------------------------
3269 , pas.two_part_section_type s_two_part_section_type
3270 ---------------------------------------------------
3271 FROM pon_bid_attribute_values ba, pon_auction_attributes aa
3272 --added by Allen Yang for Surrogate Bid 2008/09/03
3273 ---------------------------------------------------
3274 , pon_auction_sections pas
3278 AND aa.auction_header_id = ba.auction_header_id
3275 ---------------------------------------------------
3276 WHERE ba.bid_number = p_bid_number
3277 AND ba.line_number = -1
3279 AND aa.line_number = ba.line_number
3280 AND aa.sequence_number = ba.sequence_number
3281 AND (p_spreadsheet = g_online_mode OR ba.batch_id = p_batch_id)
3282 --added by Allen Yang for Surrogate Bid 2008/09/03
3283 ---------------------------------------------------
3284 AND pas.auction_header_id = aa.auction_header_id
3285 AND pas.section_name = aa.section_name
3286 ---------------------------------------------------
3287 ;
3288
3289 END validate_requirements;
3290
3291
3292 PROCEDURE validate_attributes
3293 (
3294 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3295 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3296 p_interface_type IN pon_interface_errors.interface_type%TYPE,
3297 p_userid IN pon_interface_errors.created_by%TYPE,
3298 p_spreadsheet IN VARCHAR2,
3299 p_suffix IN VARCHAR2,
3300 p_batch_id IN pon_interface_errors.batch_id%TYPE,
3301 p_request_id IN pon_interface_errors.request_id%TYPE
3302 ) IS
3303 BEGIN
3304
3305 INSERT FIRST
3306
3307 -- value must be entered if it is a required attribute
3308 WHEN s_mandatory_flag = 'Y' AND s_value IS null THEN
3309 INTO pon_interface_errors
3310 (INTERFACE_TYPE,
3311 COLUMN_NAME,
3312 TABLE_NAME,
3313 BATCH_ID,
3314 INTERFACE_LINE_ID,
3315 ERROR_MESSAGE_NAME,
3316 CREATED_BY,
3317 CREATION_DATE,
3318 LAST_UPDATED_BY,
3319 LAST_UPDATE_DATE,
3320 REQUEST_ID,
3321 ENTITY_TYPE,
3322 ENTITY_ATTR_NAME,
3323 ERROR_VALUE_DATATYPE,
3324 AUCTION_HEADER_ID,
3325 BID_NUMBER,
3326 LINE_NUMBER,
3327 ATTRIBUTE_NAME,
3328 EXPIRATION_DATE,
3329 TOKEN1_NAME,
3330 TOKEN1_VALUE,
3331 TOKEN2_NAME,
3332 TOKEN2_VALUE,
3333 WORKSHEET_NAME,
3334 WORKSHEET_SEQUENCE_NUMBER,
3335 ENTITY_MESSAGE_CODE)
3336 VALUES
3337 (p_interface_type,
3338 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
3339 'PON_BID_ATTRIBUTE_VALUES',
3340 p_batch_id,
3341 s_interface_line_id,
3342 'PON_AUC_ATTR_VALUE_REQ' || p_suffix,
3343 p_userid,
3344 sysdate,
3345 p_userid,
3346 sysdate,
3347 p_request_id,
3348 'BID_ATTRS',
3349 'Value',
3350 'TXT',
3351 p_auc_header_id,
3352 p_bid_number,
3353 s_line_number,
3354 s_attribute_name,
3355 g_exp_date,
3356 'LINENUMBER',
3357 s_document_disp_line_number,
3358 'ATTRIBUTENAME',
3359 s_trunc_attr_name,
3360 s_worksheet_name,
3361 s_worksheet_sequence_number,
3362 s_entity_message_code)
3363
3364 -- value must be among buyer specified values if type is LOV
3365 WHEN p_spreadsheet in (g_txt_upload_mode, g_xml_upload_mode) AND s_scoring_type = 'LOV' AND s_value is not NULL AND NOT EXISTS
3366 (SELECT bs.score
3367 FROM pon_attribute_scores bs
3368 WHERE bs.auction_header_id = p_auc_header_id
3369 AND bs.line_number = s_line_number
3370 AND bs.attribute_sequence_number = s_sequence_number
3371 AND bs.value = s_value) THEN
3372 INTO pon_interface_errors
3373 (INTERFACE_TYPE,
3374 COLUMN_NAME,
3375 TABLE_NAME,
3376 BATCH_ID,
3377 INTERFACE_LINE_ID,
3378 ERROR_MESSAGE_NAME,
3379 CREATED_BY,
3380 CREATION_DATE,
3381 LAST_UPDATED_BY,
3382 LAST_UPDATE_DATE,
3383 REQUEST_ID,
3384 ENTITY_TYPE,
3385 ENTITY_ATTR_NAME,
3386 ERROR_VALUE,
3387 ERROR_VALUE_DATATYPE,
3388 AUCTION_HEADER_ID,
3389 BID_NUMBER,
3390 LINE_NUMBER,
3391 ATTRIBUTE_NAME,
3392 EXPIRATION_DATE,
3393 WORKSHEET_NAME,
3394 WORKSHEET_SEQUENCE_NUMBER,
3395 ENTITY_MESSAGE_CODE)
3396 VALUES
3397 (p_interface_type,
3398 fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
3399 'PON_BID_ATTRIBUTE_VALUES',
3400 p_batch_id,
3401 s_interface_line_id,
3402 'PON_AUC_INVALID_ATTR_VALUE' || p_suffix,
3403 p_userid,
3404 sysdate,
3405 p_userid,
3406 sysdate,
3407 p_request_id,
3408 'BID_ATTRS',
3409 'Value',
3410 s_value,
3411 'TXT',
3412 p_auc_header_id,
3413 p_bid_number,
3414 s_line_number,
3415 s_attribute_name,
3416 g_exp_date,
3417 s_worksheet_name,
3418 s_worksheet_sequence_number,
3419 s_entity_message_code)
3420
3421 SELECT
3422 ba.value s_value,
3423 ba.line_number s_line_number,
3424 ba.sequence_number s_sequence_number,
3425 ba.attribute_name s_attribute_name,
3426 substr(ba.attribute_name, 0, 2000) s_trunc_attr_name,
3427 aa.mandatory_flag s_mandatory_flag,
3428 aa.scoring_type s_scoring_type,
3432 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_sequence_number,
3429 al.document_disp_line_number s_document_disp_line_number,
3430 decode(p_spreadsheet, g_xml_upload_mode, ba.interface_line_id, g_txt_upload_mode, bl.interface_line_id, null) s_interface_line_id,
3431 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
3433 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ATTRIBUTES', null) s_entity_message_code
3434 FROM pon_bid_attribute_values ba
3435 , pon_auction_attributes aa
3436 , pon_auction_item_prices_all al
3437 , pon_bid_item_prices bl
3438 , pon_bid_headers pbh
3439 WHERE ba.bid_number = p_bid_number
3440 AND ba.line_number <> -1
3441 AND aa.auction_header_id = ba.auction_header_id
3442 AND aa.line_number = ba.line_number
3443 AND aa.sequence_number = ba.sequence_number
3444 AND al.auction_header_id = ba.auction_header_id
3445 AND al.line_number = ba.line_number
3446 AND bl.bid_number = ba.bid_number
3447 AND bl.line_number = ba.line_number
3448 AND nvl(bl.is_changed_line_flag, 'Y') = 'Y'
3449 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
3450 AND pbh.bid_number = bl.bid_number
3451 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
3452
3453 END validate_attributes;
3454
3455 PROCEDURE validate_cost_factors
3456 (
3457 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3458 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3459 p_interface_type IN pon_interface_errors.interface_type%TYPE,
3460 p_userid IN pon_interface_errors.created_by%TYPE,
3461 p_spreadsheet IN VARCHAR2,
3462 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
3463 p_amt_precision IN fnd_currencies.precision%TYPE,
3464 p_suffix IN VARCHAR2,
3465 p_batch_id IN pon_interface_errors.batch_id%TYPE,
3466 p_request_id IN pon_interface_errors.request_id%TYPE
3467 ) IS
3468 BEGIN
3469
3470 -- The following validations are performed only for SUPPLIER cost factors
3471 INSERT FIRST
3472
3473 -- bid_currency_value must recieve a bid
3474 WHEN s_bid_currency_value IS null THEN
3475 INTO pon_interface_errors
3476 (INTERFACE_TYPE,
3477 COLUMN_NAME,
3478 TABLE_NAME,
3479 BATCH_ID,
3480 INTERFACE_LINE_ID,
3481 ERROR_MESSAGE_NAME,
3482 CREATED_BY,
3483 CREATION_DATE,
3484 LAST_UPDATED_BY,
3485 LAST_UPDATE_DATE,
3486 REQUEST_ID,
3487 ENTITY_TYPE,
3488 ENTITY_ATTR_NAME,
3489 ERROR_VALUE_DATATYPE,
3490 AUCTION_HEADER_ID,
3491 BID_NUMBER,
3492 LINE_NUMBER,
3493 PRICE_ELEMENT_TYPE_ID,
3494 EXPIRATION_DATE,
3495 TOKEN1_NAME,
3496 TOKEN1_VALUE,
3497 TOKEN2_NAME,
3498 TOKEN2_VALUE,
3499 WORKSHEET_NAME,
3500 WORKSHEET_SEQUENCE_NUMBER,
3501 ENTITY_MESSAGE_CODE)
3502 VALUES
3503 (p_interface_type,
3504 s_column_name,
3505 'PON_BID_PRICE_ELEMENTS',
3506 p_batch_id,
3507 s_interface_line_id,
3508 'PON_AUC_PE_VALUE_REQ' || p_suffix,
3509 p_userid,
3510 sysdate,
3511 p_userid,
3512 sysdate,
3513 p_request_id,
3514 'BID_PFS',
3515 'BidCurrencyValue',
3516 'TXT',
3517 p_auc_header_id,
3518 p_bid_number,
3519 s_line_number,
3520 s_price_element_type_id,
3521 g_exp_date,
3522 'LINENUMBER',
3523 s_document_disp_line_number,
3524 'PRICEELEMENTNAME',
3525 s_name,
3526 s_worksheet_name,
3527 s_worksheet_sequence_number,
3528 s_entity_message_code)
3529
3530 -- Line price cost factor must be positive
3531 WHEN s_sequence_number = -10 AND s_bid_currency_value <= 0 THEN
3532 INTO pon_interface_errors
3533 (INTERFACE_TYPE,
3534 COLUMN_NAME,
3535 TABLE_NAME,
3536 BATCH_ID,
3537 INTERFACE_LINE_ID,
3538 ERROR_MESSAGE_NAME,
3539 CREATED_BY,
3540 CREATION_DATE,
3541 LAST_UPDATED_BY,
3542 LAST_UPDATE_DATE,
3543 REQUEST_ID,
3544 ENTITY_TYPE,
3545 ENTITY_ATTR_NAME,
3546 ERROR_VALUE_NUMBER,
3547 ERROR_VALUE_DATATYPE,
3548 AUCTION_HEADER_ID,
3549 BID_NUMBER,
3550 LINE_NUMBER,
3551 PRICE_ELEMENT_TYPE_ID,
3552 EXPIRATION_DATE,
3553 TOKEN1_NAME,
3554 TOKEN1_VALUE,
3555 TOKEN2_NAME,
3556 TOKEN2_VALUE,
3557 WORKSHEET_NAME,
3558 WORKSHEET_SEQUENCE_NUMBER,
3559 entity_message_code)
3560 VALUES
3561 (p_interface_type,
3562 s_column_name,
3563 'PON_BID_PRICE_ELEMENTS',
3564 p_batch_id,
3565 s_interface_line_id,
3566 'PON_AUC_PE_MUST_BE_POS' || p_suffix,
3567 p_userid,
3568 sysdate,
3569 p_userid,
3570 sysdate,
3571 p_request_id,
3572 'BID_PFS',
3573 'BidCurrencyValue',
3574 s_bid_currency_value,
3575 'NUM',
3576 p_auc_header_id,
3577 p_bid_number,
3578 s_line_number,
3579 s_price_element_type_id,
3580 g_exp_date,
3581 'LINENUMBER',
3582 s_document_disp_line_number,
3583 'PRICEELEMENTNAME',
3584 s_name,
3585 s_worksheet_name,
3586 s_worksheet_sequence_number,
3587 s_entity_message_code)
3588
3589 -- Cost factor value must be postive or zero if not line price
3593 COLUMN_NAME,
3590 WHEN s_sequence_number <> -10 AND s_bid_currency_value < 0 THEN
3591 INTO pon_interface_errors
3592 (INTERFACE_TYPE,
3594 TABLE_NAME,
3595 BATCH_ID,
3596 INTERFACE_LINE_ID,
3597 ERROR_MESSAGE_NAME,
3598 CREATED_BY,
3599 CREATION_DATE,
3600 LAST_UPDATED_BY,
3601 LAST_UPDATE_DATE,
3602 REQUEST_ID,
3603 ENTITY_TYPE,
3604 ENTITY_ATTR_NAME,
3605 ERROR_VALUE_NUMBER,
3606 ERROR_VALUE_DATATYPE,
3607 AUCTION_HEADER_ID,
3608 BID_NUMBER,
3609 LINE_NUMBER,
3610 PRICE_ELEMENT_TYPE_ID,
3611 EXPIRATION_DATE,
3612 TOKEN1_NAME,
3613 TOKEN1_VALUE,
3614 TOKEN2_NAME,
3615 TOKEN2_VALUE,
3616 WORKSHEET_NAME,
3617 WORKSHEET_SEQUENCE_NUMBER,
3618 ENTITY_MESSAGE_CODE)
3619 VALUES
3620 (p_interface_type,
3621 s_column_name,
3622 'PON_BID_PRICE_ELEMENTS',
3623 p_batch_id,
3624 s_interface_line_id,
3625 'PON_AUC_PE_MUST_BE_POS_ZERO' || p_suffix,
3626 p_userid,
3627 sysdate,
3628 p_userid,
3629 sysdate,
3630 p_request_id,
3631 'BID_PFS',
3632 'BidCurrencyValue',
3633 s_bid_currency_value,
3634 'NUM',
3635 p_auc_header_id,
3636 p_bid_number,
3637 s_line_number,
3638 s_price_element_type_id,
3639 g_exp_date,
3640 'LINENUMBER',
3641 s_document_disp_line_number,
3642 'PRICEELEMENTNAME',
3643 s_name,
3644 s_worksheet_name,
3645 s_worksheet_sequence_number,
3646 s_entity_message_code)
3647
3648 -- bid_currency_precision must not exceed price precision for PER_UNIT cf
3649 WHEN s_pricing_basis = 'PER_UNIT'
3650 AND validate_price_precision(s_bid_currency_value,
3651 p_price_precision) = 'F' THEN
3652 INTO pon_interface_errors
3653 (INTERFACE_TYPE,
3654 COLUMN_NAME,
3655 TABLE_NAME,
3656 BATCH_ID,
3657 INTERFACE_LINE_ID,
3658 ERROR_MESSAGE_NAME,
3659 CREATED_BY,
3660 CREATION_DATE,
3661 LAST_UPDATED_BY,
3662 LAST_UPDATE_DATE,
3663 REQUEST_ID,
3664 ENTITY_TYPE,
3665 ENTITY_ATTR_NAME,
3666 ERROR_VALUE_NUMBER,
3667 ERROR_VALUE_DATATYPE,
3668 AUCTION_HEADER_ID,
3669 BID_NUMBER,
3670 LINE_NUMBER,
3671 PRICE_ELEMENT_TYPE_ID,
3672 EXPIRATION_DATE,
3673 TOKEN1_NAME,
3674 TOKEN1_VALUE,
3675 TOKEN2_NAME,
3676 TOKEN2_VALUE,
3677 WORKSHEET_NAME,
3678 WORKSHEET_SEQUENCE_NUMBER,
3679 ENTITY_MESSAGE_CODE)
3680 VALUES
3681 (p_interface_type,
3682 s_column_name,
3683 'PON_BID_PRICE_ELEMENTS',
3684 p_batch_id,
3685 s_interface_line_id,
3686 'PON_AUC_PE_INVALID_BID_PREC' || p_suffix,
3687 p_userid,
3688 sysdate,
3689 p_userid,
3690 sysdate,
3691 p_request_id,
3692 'BID_PFS',
3693 'BidCurrencyValue',
3694 s_bid_currency_value,
3695 'NUM',
3696 p_auc_header_id,
3697 p_bid_number,
3698 s_line_number,
3699 s_price_element_type_id,
3700 g_exp_date,
3701 'LINENUMBER',
3702 s_document_disp_line_number,
3703 'PRICEELEMENTNAME',
3704 s_name,
3705 s_worksheet_name,
3706 s_worksheet_sequence_number,
3707 s_entity_message_code)
3708
3709 -- bid_currency_precision must not exceed currency precision for FIXED_AMOUNT cf
3710 WHEN s_pricing_basis = 'FIXED_AMOUNT'
3711 AND validate_currency_precision(s_bid_currency_value,
3712 p_amt_precision) = 'F' THEN
3713 INTO pon_interface_errors
3714 (INTERFACE_TYPE,
3715 COLUMN_NAME,
3716 TABLE_NAME,
3717 BATCH_ID,
3718 INTERFACE_LINE_ID,
3719 ERROR_MESSAGE_NAME,
3720 CREATED_BY,
3721 CREATION_DATE,
3722 LAST_UPDATED_BY,
3723 LAST_UPDATE_DATE,
3724 REQUEST_ID,
3725 ENTITY_TYPE,
3726 ENTITY_ATTR_NAME,
3727 ERROR_VALUE_NUMBER,
3728 ERROR_VALUE_DATATYPE,
3729 AUCTION_HEADER_ID,
3730 BID_NUMBER,
3731 LINE_NUMBER,
3732 PRICE_ELEMENT_TYPE_ID,
3733 EXPIRATION_DATE,
3734 TOKEN1_NAME,
3735 TOKEN1_VALUE,
3736 TOKEN2_NAME,
3737 TOKEN2_VALUE,
3738 WORKSHEET_NAME,
3739 WORKSHEET_SEQUENCE_NUMBER,
3740 entity_message_code)
3741 VALUES
3742 (p_interface_type,
3743 s_column_name,
3744 'PON_BID_PRICE_ELEMENTS',
3745 p_batch_id,
3746 s_interface_line_id,
3747 'PON_AUC_PE_INVALID_CURR_PREC' || p_suffix,
3748 p_userid,
3749 sysdate,
3750 p_userid,
3751 sysdate,
3752 p_request_id,
3753 'BID_PFS',
3754 'BidCurrencyValue',
3755 s_bid_currency_value,
3756 'NUM',
3757 p_auc_header_id,
3758 p_bid_number,
3759 s_line_number,
3760 s_price_element_type_id,
3761 g_exp_date,
3762 'LINENUMBER',
3763 s_document_disp_line_number,
3764 'PRICEELEMENTNAME',
3765 s_name,
3766 s_worksheet_name,
3767 s_worksheet_sequence_number,
3768 s_entity_message_code)
3769 SELECT
3770 bpf.bid_currency_value s_bid_currency_value,
3771 bpf.sequence_number s_sequence_number,
3772 bpf.pricing_basis s_pricing_basis,
3773 bpf.line_number s_line_number,
3777 g_xml_upload_mode, bpf.interface_line_id,
3774 bpf.price_element_type_id s_price_element_type_id,
3775 al.document_disp_line_number s_document_disp_line_number,
3776 decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
3778 to_number(null)) s_interface_line_id,
3779 pft.name s_name,
3780 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
3781 decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_PRICE_ELEMENTS', to_char(null)) s_entity_message_code,
3782 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
3783 decode(p_spreadsheet, g_xml_upload_mode,
3784 fnd_message.get_string('PON', 'PON_AUCTS_ATTR_BID_VALUE' || p_suffix),
3785 fnd_message.get_string('PON', 'PON_AUC_PE_BID_VALUE_REQ' || p_suffix)) s_column_name
3786 FROM pon_bid_price_elements bpf
3787 , pon_auction_item_prices_all al
3788 , pon_bid_item_prices bl
3789 , pon_price_element_types_tl pft
3790 , pon_bid_headers pbh
3791 WHERE bpf.bid_number = p_bid_number
3792 AND bpf.pf_type = 'SUPPLIER' -- only validate SUPPLIER cost factors
3793 AND al.auction_header_id = bpf.auction_header_id
3794 AND al.line_number = bpf.line_number
3795 AND bl.bid_number = bpf.bid_number
3796 AND bl.line_number= bpf.line_number
3797 AND pft.price_element_type_id = bpf.price_element_type_id
3798 AND pft.language = userenv('LANG')
3799 AND bl.is_changed_line_flag = 'Y'
3800 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
3801 AND pbh.bid_number = bl.bid_number
3802 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
3803
3804 END validate_cost_factors;
3805
3806 PROCEDURE validate_price_breaks
3807 (
3808 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3809 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3810 p_interface_type IN pon_interface_errors.interface_type%TYPE,
3811 p_userid IN pon_interface_errors.created_by%TYPE,
3812 p_spreadsheet IN VARCHAR2,
3813 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
3814 p_trans_view IN VARCHAR2,
3815 p_blanket IN VARCHAR2,
3816 p_header_disp_pf IN VARCHAR2,
3817 p_po_start_date IN pon_auction_headers_all.po_start_date%TYPE,
3818 p_po_end_date IN pon_auction_headers_all.po_end_date%TYPE,
3819 p_auc_close_date IN pon_auction_headers_all.close_bidding_date%TYPE,
3820 p_suffix IN VARCHAR2,
3821 p_batch_id IN pon_interface_errors.batch_id%TYPE,
3822 p_request_id IN pon_interface_errors.request_id%TYPE
3823 ) IS
3824 BEGIN
3825
3826 INSERT ALL
3827
3828 -- quantity must be positive or zero
3829 WHEN s_quantity < 0 THEN
3830 INTO pon_interface_errors
3831 (INTERFACE_TYPE,
3832 TABLE_NAME,
3833 COLUMN_NAME,
3834 BATCH_ID,
3835 INTERFACE_LINE_ID,
3836 ERROR_MESSAGE_NAME,
3837 CREATED_BY,
3838 CREATION_DATE,
3839 LAST_UPDATED_BY,
3840 LAST_UPDATE_DATE,
3841 REQUEST_ID,
3842 ENTITY_TYPE,
3843 ENTITY_ATTR_NAME,
3844 ERROR_VALUE_NUMBER,
3845 ERROR_VALUE_DATATYPE,
3846 AUCTION_HEADER_ID,
3847 BID_NUMBER,
3848 LINE_NUMBER,
3849 SHIPMENT_NUMBER,
3850 EXPIRATION_DATE,
3851 TOKEN1_NAME,
3852 TOKEN1_VALUE,
3853 WORKSHEET_NAME,
3854 WORKSHEET_SEQUENCE_NUMBER,
3855 ENTITY_MESSAGE_CODE)
3856 VALUES
3857 (p_interface_type,
3858 'PON_BID_SHIPMENTS',
3859 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_QUANTITY'), null ),
3860 p_batch_id,
3861 s_interface_line_id,
3862 'PON_AUCTS_PB_QUANTITY_POSITIVE',
3863 p_userid,
3864 sysdate,
3865 p_userid,
3866 sysdate,
3867 p_request_id,
3868 'BID_PBS',
3869 'Quantity',
3870 s_quantity,
3871 'NUM',
3872 p_auc_header_id,
3873 p_bid_number,
3874 s_line_number,
3875 s_shipment_number,
3876 g_exp_date,
3877 'LINENUM',
3878 s_document_disp_line_number,
3879 s_worksheet_name,
3880 s_worksheet_seq_num,
3881 s_entity_name)
3882
3883 -- bid_currency_unit_price must be positive
3884 -- Only applier if price_type is PRICE
3885 WHEN s_price_type = 'PRICE' AND s_bid_currency_unit_price < 0 THEN
3886 INTO pon_interface_errors
3887 (INTERFACE_TYPE,
3888 TABLE_NAME,
3889 COLUMN_NAME,
3890 BATCH_ID,
3891 INTERFACE_LINE_ID,
3892 ERROR_MESSAGE_NAME,
3893 CREATED_BY,
3894 CREATION_DATE,
3895 LAST_UPDATED_BY,
3896 LAST_UPDATE_DATE,
3897 REQUEST_ID,
3898 ENTITY_TYPE,
3899 ENTITY_ATTR_NAME,
3900 ERROR_VALUE_NUMBER,
3901 ERROR_VALUE_DATATYPE,
3902 AUCTION_HEADER_ID,
3903 BID_NUMBER,
3904 LINE_NUMBER,
3905 SHIPMENT_NUMBER,
3906 EXPIRATION_DATE,
3907 TOKEN1_NAME,
3908 TOKEN1_VALUE,
3909 WORKSHEET_NAME,
3910 WORKSHEET_SEQUENCE_NUMBER,
3911 ENTITY_MESSAGE_CODE)
3912 VALUES
3913 (p_interface_type,
3914 'PON_BID_SHIPMENTS',
3915 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
3916 p_batch_id,
3917 s_interface_line_id,
3918 'PON_AUC_PB_POS_OR_ZERO',
3919 p_userid,
3920 sysdate,
3921 p_userid,
3922 sysdate,
3923 p_request_id,
3924 'BID_PBS',
3928 p_auc_header_id,
3925 'BidCurrencyUnitPrice',
3926 s_bid_currency_unit_price,
3927 'NUM',
3929 p_bid_number,
3930 s_line_number,
3931 s_shipment_number,
3932 g_exp_date,
3933 'LINENUMBER',
3934 s_document_disp_line_number,
3935 s_worksheet_name,
3936 s_worksheet_seq_num,
3937 s_entity_name)
3938
3939 -- bid_currency_unit_price precision must not exceed price precision
3940 -- Only applier if price_type is PRICE
3941 WHEN s_price_type = 'PRICE' AND validate_price_precision(
3942 s_bid_currency_unit_price, p_price_precision) = 'F' THEN
3943 INTO pon_interface_errors
3944 (INTERFACE_TYPE,
3945 TABLE_NAME,
3946 COLUMN_NAME,
3947 BATCH_ID,
3948 INTERFACE_LINE_ID,
3949 ERROR_MESSAGE_NAME,
3950 CREATED_BY,
3951 CREATION_DATE,
3952 LAST_UPDATED_BY,
3953 LAST_UPDATE_DATE,
3954 REQUEST_ID,
3955 ENTITY_TYPE,
3956 ENTITY_ATTR_NAME,
3957 ERROR_VALUE_NUMBER,
3958 ERROR_VALUE_DATATYPE,
3959 AUCTION_HEADER_ID,
3960 BID_NUMBER,
3961 LINE_NUMBER,
3962 SHIPMENT_NUMBER,
3963 EXPIRATION_DATE,
3964 TOKEN1_NAME,
3965 TOKEN1_VALUE,
3966 WORKSHEET_NAME,
3967 WORKSHEET_SEQUENCE_NUMBER,
3968 ENTITY_MESSAGE_CODE)
3969 VALUES
3970 (p_interface_type,
3971 'PON_BID_SHIPMENTS',
3972 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
3973 p_batch_id,
3974 s_interface_line_id,
3975 'PON_AUC_PB_INVALID_BID_PREC' || p_suffix,
3976 p_userid,
3977 sysdate,
3978 p_userid,
3979 sysdate,
3980 p_request_id,
3981 'BID_PBS',
3982 'BidCurrencyUnitPrice',
3983 s_bid_currency_unit_price,
3984 'NUM',
3985 p_auc_header_id,
3986 p_bid_number,
3987 s_line_number,
3988 s_shipment_number,
3989 g_exp_date,
3990 'LINENUMBER',
3991 s_document_disp_line_number,
3992 s_worksheet_name,
3993 s_worksheet_seq_num,
3994 s_entity_name)
3995
3996 -- The transformed price should be positive
3997 -- Since bid_currency_price is the same as bid_currency_unit_price
3998 -- in untransformed view, we can use s_price (which is the
3999 -- transformed price in auction currency)
4000 -- NOTE: rate conversion is unneccessary since we only check sign
4001 WHEN p_trans_view = 'N' AND s_price < 0 THEN
4002 INTO pon_interface_errors
4003 (INTERFACE_TYPE,
4004 TABLE_NAME,
4005 BATCH_ID,
4006 INTERFACE_LINE_ID,
4007 ERROR_MESSAGE_NAME,
4008 CREATED_BY,
4009 CREATION_DATE,
4010 LAST_UPDATED_BY,
4011 LAST_UPDATE_DATE,
4012 REQUEST_ID,
4013 ENTITY_TYPE,
4014 ENTITY_ATTR_NAME,
4015 ERROR_VALUE_NUMBER,
4016 ERROR_VALUE_DATATYPE,
4017 AUCTION_HEADER_ID,
4018 BID_NUMBER,
4019 LINE_NUMBER,
4020 SHIPMENT_NUMBER,
4021 EXPIRATION_DATE,
4022 TOKEN1_NAME,
4023 TOKEN1_VALUE,
4024 WORKSHEET_NAME,
4025 WORKSHEET_SEQUENCE_NUMBER,
4026 ENTITY_MESSAGE_CODE)
4027 VALUES
4028 (p_interface_type,
4029 'PON_BID_SHIPMENTS',
4030 p_batch_id,
4031 s_interface_line_id,
4032 'PON_PB_BIDPRICE_INVALID_1',
4033 p_userid,
4034 sysdate,
4035 p_userid,
4036 sysdate,
4037 p_request_id,
4038 'BID_PBS',
4039 'BidCurrencyPrice',
4040 s_bid_currency_price,
4041 'NUM',
4042 p_auc_header_id,
4043 p_bid_number,
4044 s_line_number,
4045 s_shipment_number,
4046 g_exp_date,
4047 'LINENUMBER',
4048 s_document_disp_line_number,
4049 s_worksheet_name,
4050 s_worksheet_seq_num,
4051 s_entity_name)
4052
4053 -- bid_currency_price must be positive
4054 WHEN p_trans_view = 'Y' AND s_bid_currency_price < 0 THEN
4055 INTO pon_interface_errors
4056 (INTERFACE_TYPE,
4057 TABLE_NAME,
4058 BATCH_ID,
4059 INTERFACE_LINE_ID,
4060 ERROR_MESSAGE_NAME,
4061 CREATED_BY,
4062 CREATION_DATE,
4063 LAST_UPDATED_BY,
4064 LAST_UPDATE_DATE,
4065 REQUEST_ID,
4066 ENTITY_TYPE,
4067 ENTITY_ATTR_NAME,
4068 ERROR_VALUE_NUMBER,
4069 ERROR_VALUE_DATATYPE,
4070 AUCTION_HEADER_ID,
4071 BID_NUMBER,
4072 LINE_NUMBER,
4073 SHIPMENT_NUMBER,
4074 EXPIRATION_DATE,
4075 TOKEN1_NAME,
4076 TOKEN1_VALUE,
4077 WORKSHEET_NAME,
4078 WORKSHEET_SEQUENCE_NUMBER,
4079 ENTITY_MESSAGE_CODE)
4080 VALUES
4081 (p_interface_type,
4082 'PON_BID_SHIPMENTS',
4083 p_batch_id,
4084 s_interface_line_id,
4085 decode(p_header_disp_pf, 'Y',
4086 'PON_PB_BIDPRICE_INVALID_2' || p_suffix,
4087 'PON_AUC_PB_POS_OR_ZERO'),
4088 p_userid,
4089 sysdate,
4090 p_userid,
4091 sysdate,
4092 p_request_id,
4093 'BID_PBS',
4094 'BidCurrencyPrice',
4095 s_bid_currency_price,
4096 'NUM',
4097 p_auc_header_id,
4098 p_bid_number,
4099 s_line_number,
4100 s_shipment_number,
4101 g_exp_date,
4102 'LINENUMBER',
4103 s_document_disp_line_number,
4104 s_worksheet_name,
4105 s_worksheet_seq_num,
4106 s_entity_name)
4107
4111 INTO pon_interface_errors
4108 -- bid_currency_price precision must not exceed price precision
4109 WHEN validate_price_precision(
4110 s_bid_currency_price, p_price_precision) = 'F' THEN
4112 (INTERFACE_TYPE,
4113 TABLE_NAME,
4114 BATCH_ID,
4115 INTERFACE_LINE_ID,
4116 ERROR_MESSAGE_NAME,
4117 CREATED_BY,
4118 CREATION_DATE,
4119 LAST_UPDATED_BY,
4120 LAST_UPDATE_DATE,
4121 REQUEST_ID,
4122 ENTITY_TYPE,
4123 ENTITY_ATTR_NAME,
4124 ERROR_VALUE_NUMBER,
4125 ERROR_VALUE_DATATYPE,
4126 AUCTION_HEADER_ID,
4127 BID_NUMBER,
4128 LINE_NUMBER,
4129 SHIPMENT_NUMBER,
4130 EXPIRATION_DATE,
4131 TOKEN1_NAME,
4132 TOKEN1_VALUE,
4133 WORKSHEET_NAME,
4134 WORKSHEET_SEQUENCE_NUMBER,
4135 ENTITY_MESSAGE_CODE)
4136 VALUES
4137 (p_interface_type,
4138 'PON_BID_SHIPMENTS',
4139 p_batch_id,
4140 s_interface_line_id,
4141 'PON_AUC_PB_INVALID_BID_PREC' || p_suffix,
4142 p_userid,
4143 sysdate,
4144 p_userid,
4145 sysdate,
4146 p_request_id,
4147 'BID_PBS',
4148 'BidCurrencyPrice',
4149 s_bid_currency_price,
4150 'NUM',
4151 p_auc_header_id,
4152 p_bid_number,
4153 s_line_number,
4154 s_shipment_number,
4155 g_exp_date,
4156 'LINENUMBER',
4157 s_document_disp_line_number,
4158 s_worksheet_name,
4159 s_worksheet_seq_num,
4160 s_entity_name)
4161
4162 -- price_discount must be 0 to 100
4163 -- Only applies if price_type is PRICE DISCOUNT
4164 WHEN s_price_type = 'PRICE DISCOUNT'
4165 AND (s_price_discount < 0 OR s_price_discount > 100) THEN
4166 INTO pon_interface_errors
4167 (INTERFACE_TYPE,
4168 TABLE_NAME,
4169 COLUMN_NAME,
4170 BATCH_ID,
4171 INTERFACE_LINE_ID,
4172 ERROR_MESSAGE_NAME,
4173 CREATED_BY,
4174 CREATION_DATE,
4175 LAST_UPDATED_BY,
4176 LAST_UPDATE_DATE,
4177 REQUEST_ID,
4178 ENTITY_TYPE,
4179 ENTITY_ATTR_NAME,
4180 ERROR_VALUE_NUMBER,
4181 ERROR_VALUE_DATATYPE,
4182 AUCTION_HEADER_ID,
4183 BID_NUMBER,
4184 LINE_NUMBER,
4185 SHIPMENT_NUMBER,
4186 EXPIRATION_DATE,
4187 TOKEN1_NAME,
4188 TOKEN1_VALUE,
4189 WORKSHEET_NAME,
4190 WORKSHEET_SEQUENCE_NUMBER,
4191 ENTITY_MESSAGE_CODE)
4192 VALUES
4193 (p_interface_type,
4194 'PON_BID_SHIPMENTS',
4195 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
4196 p_batch_id,
4197 s_interface_line_id,
4198 'PON_INVALID_PRICE_DISCOUNT',
4199 p_userid,
4200 sysdate,
4201 p_userid,
4202 sysdate,
4203 p_request_id,
4204 'BID_PBS',
4205 'PriceDiscount',
4206 s_price_discount,
4207 'NUM',
4208 p_auc_header_id,
4209 p_bid_number,
4210 s_line_number,
4211 s_shipment_number,
4212 g_exp_date,
4213 'LINENUMBER',
4214 s_document_disp_line_number,
4215 s_worksheet_name,
4216 s_worksheet_seq_num,
4217 s_entity_name)
4218
4219 -- effective_start_date must be past the current date
4220 WHEN s_effective_start_date < s_current_date THEN
4221 INTO pon_interface_errors
4222 (INTERFACE_TYPE,
4223 TABLE_NAME,
4224 COLUMN_NAME,
4225 BATCH_ID,
4226 INTERFACE_LINE_ID,
4227 ERROR_MESSAGE_NAME,
4228 CREATED_BY,
4229 CREATION_DATE,
4230 LAST_UPDATED_BY,
4231 LAST_UPDATE_DATE,
4232 REQUEST_ID,
4233 ENTITY_TYPE,
4234 ENTITY_ATTR_NAME,
4235 ERROR_VALUE_DATE,
4236 ERROR_VALUE_DATATYPE,
4237 AUCTION_HEADER_ID,
4238 BID_NUMBER,
4239 LINE_NUMBER,
4240 SHIPMENT_NUMBER,
4241 EXPIRATION_DATE,
4242 TOKEN1_NAME,
4243 TOKEN1_VALUE,
4244 WORKSHEET_NAME,
4245 WORKSHEET_SEQUENCE_NUMBER,
4246 ENTITY_MESSAGE_CODE)
4247 VALUES
4248 (p_interface_type,
4249 'PON_BID_SHIPMENTS',
4250 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
4251 p_batch_id,
4252 s_interface_line_id,
4253 'PON_AUC_FROMDATE_AFTER_CURDATE',
4254 p_userid,
4255 sysdate,
4256 p_userid,
4257 sysdate,
4258 p_request_id,
4259 'BID_PBS',
4260 'EffectiveStartDate',
4261 s_effective_start_date,
4262 'DAT',
4263 p_auc_header_id,
4264 p_bid_number,
4265 s_line_number,
4266 s_shipment_number,
4267 g_exp_date,
4268 'LINENUMBER',
4269 s_document_disp_line_number,
4270 s_worksheet_name,
4271 s_worksheet_seq_num,
4272 s_entity_name)
4273 -- effective_start_date must be after po start date
4274 WHEN s_effective_start_date < p_po_start_date THEN
4275 INTO pon_interface_errors
4276 (INTERFACE_TYPE,
4277 TABLE_NAME,
4278 COLUMN_NAME,
4279 BATCH_ID,
4280 INTERFACE_LINE_ID,
4281 ERROR_MESSAGE_NAME,
4282 CREATED_BY,
4283 CREATION_DATE,
4284 LAST_UPDATED_BY,
4285 LAST_UPDATE_DATE,
4286 REQUEST_ID,
4287 ENTITY_TYPE,
4288 ENTITY_ATTR_NAME,
4289 ERROR_VALUE_DATE,
4290 ERROR_VALUE_DATATYPE,
4291 AUCTION_HEADER_ID,
4295 EXPIRATION_DATE,
4292 BID_NUMBER,
4293 LINE_NUMBER,
4294 SHIPMENT_NUMBER,
4296 TOKEN1_NAME,
4297 TOKEN1_VALUE,
4298 WORKSHEET_NAME,
4299 WORKSHEET_SEQUENCE_NUMBER,
4300 ENTITY_MESSAGE_CODE)
4301 VALUES
4302 (p_interface_type,
4303 'PON_BID_SHIPMENTS',
4304 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
4305 p_batch_id,
4306 s_interface_line_id,
4307 'PON_AUC_BAD_PB_EFF_FDATE_2',
4308 p_userid,
4309 sysdate,
4310 p_userid,
4311 sysdate,
4312 p_request_id,
4313 'BID_PBS',
4314 'EffectiveStartDate',
4315 s_effective_start_date,
4316 'DAT',
4317 p_auc_header_id,
4318 p_bid_number,
4319 s_line_number,
4320 s_shipment_number,
4321 g_exp_date,
4322 'LINENUMBER',
4323 s_document_disp_line_number,
4324 s_worksheet_name,
4325 s_worksheet_seq_num,
4326 s_entity_name)
4327
4328 -- effective_start_date must be before po end date
4329 WHEN s_effective_start_date > p_po_end_date THEN
4330 INTO pon_interface_errors
4331 (INTERFACE_TYPE,
4332 TABLE_NAME,
4333 COLUMN_NAME,
4334 BATCH_ID,
4335 INTERFACE_LINE_ID,
4336 ERROR_MESSAGE_NAME,
4337 CREATED_BY,
4338 CREATION_DATE,
4339 LAST_UPDATED_BY,
4340 LAST_UPDATE_DATE,
4341 REQUEST_ID,
4342 ENTITY_TYPE,
4343 ENTITY_ATTR_NAME,
4344 ERROR_VALUE_DATE,
4345 ERROR_VALUE_DATATYPE,
4346 AUCTION_HEADER_ID,
4347 BID_NUMBER,
4348 LINE_NUMBER,
4349 SHIPMENT_NUMBER,
4350 EXPIRATION_DATE,
4351 TOKEN1_NAME,
4352 TOKEN1_VALUE,
4353 WORKSHEET_NAME,
4354 WORKSHEET_SEQUENCE_NUMBER,
4355 ENTITY_MESSAGE_CODE)
4356 VALUES
4357 (p_interface_type,
4358 'PON_BID_SHIPMENTS',
4359 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
4360 p_batch_id,
4361 s_interface_line_id,
4362 'PON_AUC_BAD_PB_EFF_FDATE_3',
4363 p_userid,
4364 sysdate,
4365 p_userid,
4366 sysdate,
4367 p_request_id,
4368 'BID_PBS',
4369 'EffectiveStartDate',
4370 s_effective_start_date,
4371 'DAT',
4372 p_auc_header_id,
4373 p_bid_number,
4374 s_line_number,
4375 s_shipment_number,
4376 g_exp_date,
4377 'LINENUMBER',
4378 s_document_disp_line_number,
4379 s_worksheet_name,
4380 s_worksheet_seq_num,
4381 s_entity_name)
4382
4383 -- effective start date must be after auction close date
4384 WHEN s_effective_start_date < p_auc_close_date THEN
4385 INTO pon_interface_errors
4386 (INTERFACE_TYPE,
4387 TABLE_NAME,
4388 COLUMN_NAME,
4389 BATCH_ID,
4390 INTERFACE_LINE_ID,
4391 ERROR_MESSAGE_NAME,
4392 CREATED_BY,
4393 CREATION_DATE,
4394 LAST_UPDATED_BY,
4395 LAST_UPDATE_DATE,
4396 REQUEST_ID,
4397 ENTITY_TYPE,
4398 ENTITY_ATTR_NAME,
4399 ERROR_VALUE_DATE,
4400 ERROR_VALUE_DATATYPE,
4401 AUCTION_HEADER_ID,
4402 BID_NUMBER,
4403 LINE_NUMBER,
4404 SHIPMENT_NUMBER,
4405 EXPIRATION_DATE,
4406 TOKEN1_NAME,
4407 TOKEN1_VALUE,
4408 WORKSHEET_NAME,
4409 WORKSHEET_SEQUENCE_NUMBER,
4410 ENTITY_MESSAGE_CODE)
4411 VALUES
4412 (p_interface_type,
4413 'PON_BID_SHIPMENTS',
4414 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
4415 p_batch_id,
4416 s_interface_line_id,
4417 'PON_AUC_BAD_PB_EFF_FDATE_1',
4418 p_userid,
4419 sysdate,
4420 p_userid,
4421 sysdate,
4422 p_request_id,
4423 'BID_PBS',
4424 'EffectiveStartDate',
4425 s_effective_start_date,
4426 'DAT',
4427 p_auc_header_id,
4428 p_bid_number,
4429 s_line_number,
4430 s_shipment_number,
4431 g_exp_date,
4432 'LINENUMBER',
4433 s_document_disp_line_number,
4434 s_worksheet_name,
4435 s_worksheet_seq_num,
4436 s_entity_name)
4437 -- effective_end_date must be after current date
4438 WHEN s_effective_end_date < s_current_date THEN
4439 INTO pon_interface_errors
4440 (INTERFACE_TYPE,
4441 TABLE_NAME,
4442 COLUMN_NAME,
4443 BATCH_ID,
4444 INTERFACE_LINE_ID,
4445 ERROR_MESSAGE_NAME,
4446 CREATED_BY,
4447 CREATION_DATE,
4448 LAST_UPDATED_BY,
4449 LAST_UPDATE_DATE,
4450 REQUEST_ID,
4451 ENTITY_TYPE,
4452 ENTITY_ATTR_NAME,
4453 ERROR_VALUE_DATE,
4454 ERROR_VALUE_DATATYPE,
4455 AUCTION_HEADER_ID,
4456 BID_NUMBER,
4457 LINE_NUMBER,
4458 SHIPMENT_NUMBER,
4459 EXPIRATION_DATE,
4460 TOKEN1_NAME,
4461 TOKEN1_VALUE,
4462 WORKSHEET_NAME,
4463 WORKSHEET_SEQUENCE_NUMBER,
4464 ENTITY_MESSAGE_CODE)
4465 VALUES
4466 (p_interface_type,
4467 'PON_BID_SHIPMENTS',
4471 'PON_AUC_TODATE_AFTER_CURDATE',
4468 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
4469 p_batch_id,
4470 s_interface_line_id,
4472 p_userid,
4473 sysdate,
4474 p_userid,
4475 sysdate,
4476 p_request_id,
4477 'BID_PBS',
4478 'EffectiveEndDate',
4479 s_effective_end_date,
4480 'DAT',
4481 p_auc_header_id,
4482 p_bid_number,
4483 s_line_number,
4484 s_shipment_number,
4485 g_exp_date,
4486 'LINENUMBER',
4487 s_document_disp_line_number,
4488 s_worksheet_name,
4489 s_worksheet_seq_num,
4490 s_entity_name)
4491
4492 -- effective_end_date must be after po start date
4493 WHEN s_effective_end_date < p_po_start_date THEN
4494 INTO pon_interface_errors
4495 (INTERFACE_TYPE,
4496 TABLE_NAME,
4497 COLUMN_NAME,
4498 BATCH_ID,
4499 INTERFACE_LINE_ID,
4500 ERROR_MESSAGE_NAME,
4501 CREATED_BY,
4502 CREATION_DATE,
4503 LAST_UPDATED_BY,
4504 LAST_UPDATE_DATE,
4505 REQUEST_ID,
4506 ENTITY_TYPE,
4507 ENTITY_ATTR_NAME,
4508 ERROR_VALUE_DATE,
4509 ERROR_VALUE_DATATYPE,
4510 AUCTION_HEADER_ID,
4511 BID_NUMBER,
4512 LINE_NUMBER,
4513 SHIPMENT_NUMBER,
4514 EXPIRATION_DATE,
4515 TOKEN1_NAME,
4516 TOKEN1_VALUE,
4517 WORKSHEET_NAME,
4518 WORKSHEET_SEQUENCE_NUMBER,
4519 ENTITY_MESSAGE_CODE)
4520 VALUES
4521 (p_interface_type,
4522 'PON_BID_SHIPMENTS',
4523 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
4524 p_batch_id,
4525 s_interface_line_id,
4526 'PON_AUC_BAD_PB_EFF_TDATE_2',
4527 p_userid,
4528 sysdate,
4529 p_userid,
4530 sysdate,
4531 p_request_id,
4532 'BID_PBS',
4533 'EffectiveEndDate',
4534 s_effective_end_date,
4535 'DAT',
4536 p_auc_header_id,
4537 p_bid_number,
4538 s_line_number,
4539 s_shipment_number,
4540 g_exp_date,
4541 'LINENUMBER',
4542 s_document_disp_line_number,
4543 s_worksheet_name,
4544 s_worksheet_seq_num,
4545 s_entity_name)
4546
4547 -- effective_end_date must be before po end date
4548 WHEN s_effective_end_date > p_po_end_date THEN
4549 INTO pon_interface_errors
4550 (INTERFACE_TYPE,
4551 TABLE_NAME,
4552 COLUMN_NAME,
4553 BATCH_ID,
4554 INTERFACE_LINE_ID,
4555 ERROR_MESSAGE_NAME,
4556 CREATED_BY,
4557 CREATION_DATE,
4558 LAST_UPDATED_BY,
4559 LAST_UPDATE_DATE,
4560 REQUEST_ID,
4561 ENTITY_TYPE,
4562 ENTITY_ATTR_NAME,
4563 ERROR_VALUE_DATE,
4564 ERROR_VALUE_DATATYPE,
4565 AUCTION_HEADER_ID,
4566 BID_NUMBER,
4567 LINE_NUMBER,
4568 SHIPMENT_NUMBER,
4569 EXPIRATION_DATE,
4570 TOKEN1_NAME,
4571 TOKEN1_VALUE,
4572 WORKSHEET_NAME,
4573 WORKSHEET_SEQUENCE_NUMBER,
4574 ENTITY_MESSAGE_CODE)
4575 VALUES
4576 (p_interface_type,
4577 'PON_BID_SHIPMENTS',
4578 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
4579 p_batch_id,
4580 s_interface_line_id,
4581 'PON_AUC_BAD_PB_EFF_TDATE_3',
4582 p_userid,
4583 sysdate,
4584 p_userid,
4585 sysdate,
4586 p_request_id,
4587 'BID_PBS',
4588 'EffectiveEndDate',
4589 s_effective_end_date,
4590 'DAT',
4591 p_auc_header_id,
4592 p_bid_number,
4593 s_line_number,
4594 s_shipment_number,
4595 g_exp_date,
4596 'LINENUMBER',
4597 s_document_disp_line_number,
4598 s_worksheet_name,
4599 s_worksheet_seq_num,
4600 s_entity_name)
4601
4602 -- effective_end_date must be after auction close date
4603 WHEN s_effective_end_date < p_auc_close_date THEN
4604 INTO pon_interface_errors
4605 (INTERFACE_TYPE,
4606 TABLE_NAME,
4607 COLUMN_NAME,
4608 BATCH_ID,
4609 INTERFACE_LINE_ID,
4610 ERROR_MESSAGE_NAME,
4611 CREATED_BY,
4612 CREATION_DATE,
4613 LAST_UPDATED_BY,
4614 LAST_UPDATE_DATE,
4615 REQUEST_ID,
4616 ENTITY_TYPE,
4617 ENTITY_ATTR_NAME,
4618 ERROR_VALUE_DATE,
4619 ERROR_VALUE_DATATYPE,
4620 AUCTION_HEADER_ID,
4621 BID_NUMBER,
4622 LINE_NUMBER,
4623 SHIPMENT_NUMBER,
4624 EXPIRATION_DATE,
4625 TOKEN1_NAME,
4626 TOKEN1_VALUE,
4627 WORKSHEET_NAME,
4628 WORKSHEET_SEQUENCE_NUMBER,
4629 ENTITY_MESSAGE_CODE)
4630 VALUES
4631 (p_interface_type,
4632 'PON_BID_SHIPMENTS',
4633 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
4634 p_batch_id,
4635 s_interface_line_id ,
4636 'PON_AUC_BAD_PB_EFF_TDATE_1',
4637 p_userid,
4638 sysdate,
4639 p_userid,
4640 sysdate,
4641 p_request_id,
4642 'BID_PBS',
4646 p_auc_header_id,
4643 'EffectiveEndDate',
4644 s_effective_end_date,
4645 'DAT',
4647 p_bid_number,
4648 s_line_number,
4649 s_shipment_number,
4650 g_exp_date,
4651 'LINENUMBER',
4652 s_document_disp_line_number,
4653 s_worksheet_name,
4654 s_worksheet_seq_num,
4655 s_entity_name)
4656 -- To be considered valid, the price break must have one of the following:
4657 -- ship_to_organization_id, ship_location_id, quantity,
4658 -- effective_start_date, effective_end_date
4659 WHEN s_ship_to_organization_id IS null
4660 AND s_ship_to_location_id IS null
4661 AND s_quantity IS null
4662 AND s_effective_start_date IS null
4663 AND s_effective_end_date IS null THEN
4664 INTO pon_interface_errors
4665 (INTERFACE_TYPE,
4666 TABLE_NAME,
4667 BATCH_ID,
4668 INTERFACE_LINE_ID,
4669 ERROR_MESSAGE_NAME,
4670 CREATED_BY,
4671 CREATION_DATE,
4672 LAST_UPDATED_BY,
4673 LAST_UPDATE_DATE,
4674 REQUEST_ID,
4675 AUCTION_HEADER_ID,
4676 BID_NUMBER,
4677 LINE_NUMBER,
4678 SHIPMENT_NUMBER,
4679 ERROR_VALUE_DATATYPE,
4680 ERROR_VALUE,
4681 EXPIRATION_DATE,
4682 TOKEN1_NAME,
4683 TOKEN1_VALUE,
4684 WORKSHEET_NAME,
4685 WORKSHEET_SEQUENCE_NUMBER,
4686 ENTITY_MESSAGE_CODE)
4687 VALUES
4688 (p_interface_type,
4689 'PON_BID_SHIPMENTS',
4690 p_batch_id,
4691 s_interface_line_id,
4692 'PON_AUCTS_PB_MUST_BE_ENTERED',
4693 p_userid,
4694 sysdate,
4695 p_userid,
4696 sysdate,
4697 p_request_id,
4698 p_auc_header_id,
4699 p_bid_number,
4700 s_line_number,
4701 s_shipment_number,
4702 'TXT',
4703 '',
4704 g_exp_date,
4705 'LINENUMBER',
4706 s_document_disp_line_number,
4707 s_worksheet_name,
4708 s_worksheet_seq_num,
4709 s_entity_name)
4710 -- effective_start_date must be before effective_end_date
4711 WHEN s_effective_start_date > s_effective_end_date THEN
4712 INTO pon_interface_errors
4713 (INTERFACE_TYPE,
4714 TABLE_NAME,
4715 COLUMN_NAME,
4716 BATCH_ID,
4717 INTERFACE_LINE_ID,
4718 ERROR_MESSAGE_NAME,
4719 CREATED_BY,
4720 CREATION_DATE,
4721 LAST_UPDATED_BY,
4722 LAST_UPDATE_DATE,
4723 REQUEST_ID,
4724 ENTITY_TYPE,
4725 ENTITY_ATTR_NAME,
4726 ERROR_VALUE_DATE,
4727 ERROR_VALUE_DATATYPE,
4728 AUCTION_HEADER_ID,
4729 BID_NUMBER,
4730 LINE_NUMBER,
4731 SHIPMENT_NUMBER,
4732 EXPIRATION_DATE,
4733 TOKEN1_NAME,
4734 TOKEN1_VALUE,
4735 WORKSHEET_NAME,
4736 WORKSHEET_SEQUENCE_NUMBER,
4737 ENTITY_MESSAGE_CODE)
4738 VALUES
4739 (p_interface_type,
4740 'PON_BID_SHIPMENTS',
4741 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
4742 p_batch_id,
4743 s_interface_line_id,
4744 'PON_AUC_INVALID_EFF_DATES',
4745 p_userid,
4746 sysdate,
4747 p_userid,
4748 sysdate,
4749 p_request_id,
4750 'BID_PBS',
4751 'EffectiveEndDate',
4752 s_effective_end_date,
4753 'DAT',
4754 p_auc_header_id,
4755 p_bid_number,
4756 s_line_number,
4757 s_shipment_number,
4758 g_exp_date,
4759 'LINENUMBER',
4760 s_document_disp_line_number,
4761 s_worksheet_name,
4762 s_worksheet_seq_num,
4763 s_entity_name)
4764
4765 -- Either bid_currency_unit_price or price_discount must have a value
4766 WHEN s_bid_currency_unit_price IS null AND s_price_discount IS null THEN
4767 INTO pon_interface_errors
4768 (INTERFACE_TYPE,
4769 TABLE_NAME,
4770 COLUMN_NAME,
4771 BATCH_ID,
4772 INTERFACE_LINE_ID,
4773 ERROR_MESSAGE_NAME,
4774 CREATED_BY,
4775 CREATION_DATE,
4776 LAST_UPDATED_BY,
4777 LAST_UPDATE_DATE,
4778 REQUEST_ID,
4779 AUCTION_HEADER_ID,
4780 BID_NUMBER,
4781 LINE_NUMBER,
4782 SHIPMENT_NUMBER,
4783 ERROR_VALUE_DATATYPE,
4784 ERROR_VALUE,
4785 EXPIRATION_DATE,
4786 TOKEN1_NAME,
4787 TOKEN1_VALUE,
4788 WORKSHEET_NAME,
4789 WORKSHEET_SEQUENCE_NUMBER,
4790 ENTITY_MESSAGE_CODE)
4791 VALUES
4792 (p_interface_type,
4793 'PON_BID_SHIPMENTS',
4794 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
4795 p_batch_id,
4796 s_interface_line_id,
4797 'PON_AUC_PB_PRICE_REQ',
4798 p_userid,
4799 sysdate,
4800 p_userid,
4801 sysdate,
4802 p_request_id,
4803 p_auc_header_id,
4804 p_bid_number,
4805 s_line_number,
4806 s_shipment_number,
4807 'TXT',
4808 '',
4809 g_exp_date,
4810 'LINENUMBER',
4811 s_document_disp_line_number,
4812 s_worksheet_name,
4813 s_worksheet_seq_num,
4814 s_entity_name)
4815
4816 -- CUMULATIVE price breaks cannot have start and end dates
4820 INTO pon_interface_errors
4817 WHEN s_price_break_type = 'CUMULATIVE'
4818 AND (s_effective_start_date IS NOT null
4819 OR s_effective_end_date IS NOT null) THEN
4821 (INTERFACE_TYPE,
4822 TABLE_NAME,
4823 BATCH_ID,
4824 INTERFACE_LINE_ID,
4825 ERROR_MESSAGE_NAME,
4826 CREATED_BY,
4827 CREATION_DATE,
4828 LAST_UPDATED_BY,
4829 LAST_UPDATE_DATE,
4830 REQUEST_ID,
4831 ENTITY_TYPE,
4832 ENTITY_ATTR_NAME,
4833 ERROR_VALUE_DATE,
4834 ERROR_VALUE_DATATYPE,
4835 AUCTION_HEADER_ID,
4836 BID_NUMBER,
4837 LINE_NUMBER,
4838 SHIPMENT_NUMBER,
4839 EXPIRATION_DATE,
4840 TOKEN1_NAME,
4841 TOKEN1_VALUE,
4842 WORKSHEET_NAME,
4843 WORKSHEET_SEQUENCE_NUMBER,
4844 ENTITY_MESSAGE_CODE)
4845 VALUES
4846 (p_interface_type,
4847 'PON_BID_SHIPMENTS',
4848 p_batch_id,
4849 s_interface_line_id,
4850 'PON_AUCTS_PB_CUMM_EFF_DATES',
4851 p_userid,
4852 sysdate,
4853 p_userid,
4854 sysdate,
4855 p_request_id,
4856 'BID_PBS',
4857 nvl2(s_effective_start_date, 'EffectiveEndDate',
4858 'EffectiveStartDate'),
4859 nvl(s_effective_end_date, s_effective_end_date),
4860 'DAT',
4861 p_auc_header_id,
4862 p_bid_number,
4863 s_line_number,
4864 s_shipment_number,
4865 g_exp_date,
4866 'LINENUMBER',
4867 s_document_disp_line_number,
4868 s_worksheet_name,
4869 s_worksheet_seq_num,
4870 s_entity_name)
4871
4872 SELECT
4873 sysdate s_current_date,
4874 bpb.ship_to_organization_id s_ship_to_organization_id,
4875 bpb.ship_to_location_id s_ship_to_location_id,
4876 bpb.quantity s_quantity,
4877 bpb.price s_price,
4878 bpb.price_type s_price_type,
4879 bpb.bid_currency_price s_bid_currency_price,
4880 bpb.bid_currency_unit_price s_bid_currency_unit_price,
4881 bpb.price_discount s_price_discount,
4882 bpb.effective_start_date s_effective_start_date,
4883 bpb.effective_end_date s_effective_end_date,
4884 bpb.line_number s_line_number,
4885 bpb.shipment_number s_shipment_number,
4886 decode(p_spreadsheet, g_xml_upload_mode,
4887 bpb.interface_line_id,
4888 bl.interface_line_id
4889 ) s_interface_line_id,
4890 al.document_disp_line_number s_document_disp_line_number,
4891 al.price_break_type s_price_break_type,
4892 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
4893 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
4894 decode(p_spreadsheet, g_xml_upload_mode,
4895 'PON_AUCTS_PRICE_BREAKS',
4896 null
4897 ) s_entity_name
4898 FROM pon_bid_shipments bpb
4899 , pon_bid_item_prices bl
4900 , pon_auction_item_prices_all al
4901 , pon_bid_headers pbh
4902 WHERE bpb.bid_number = p_bid_number
4903 AND bl.bid_number = bpb.bid_number
4904 AND bl.line_number = bpb.line_number
4905 AND al.auction_header_id = bpb.auction_header_id
4906 AND al.line_number = bpb.line_number
4907 AND bl.is_changed_line_flag = 'Y'
4908 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
4909 AND pbh.bid_number = bl.bid_number
4910 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
4911
4912 END validate_price_breaks;
4913
4914
4915
4916 /*========================================================*
4917 * The following validations are performed
4918 * 1. Min Quantity should not be null or negative
4919 * 2. Max Quantity should not be null or negative
4920 * 3. Max quantity should be greater or equal to the min quantity
4921 * 4. The ranges of min-max quantities should not overlap across tiers for a given line
4922 * 5. The price tier price should not be null and negative
4923 * 6. Precision of the price entered should be less than the auction currency precision
4924 *.7. In case of an SPO the quantity entered at the line level must be equal to the
4925 * maximum quantity of all the price tiers.
4926 *.8. In case of an SPO the price entered at the line level must be equal to the price
4927 * corresponding to the maximum quantity of all the price tiers.
4928 * =======================================================*/
4929
4930 PROCEDURE VALIDATE_QTY_BASED_PRICE_TIERS
4931 (
4932 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
4933 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
4934 p_interface_type IN pon_interface_errors.interface_type%TYPE,
4935 p_userid IN pon_interface_errors.created_by%TYPE,
4936 p_spreadsheet IN VARCHAR2,
4937 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4938 p_batch_id IN pon_interface_errors.batch_id%TYPE,
4939 p_request_id IN pon_interface_errors.request_id%TYPE,
4940 p_contract_type IN pon_bid_headers.contract_type%TYPE
4941 ) IS
4942
4943 BEGIN
4944
4945 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
4946 FND_LOG.string(log_level => FND_LOG.level_procedure,
4947 module => g_module_prefix || 'VALIDATE_QTY_BASED_PRICE_TIERS',
4951 END IF; --}
4948 message => 'Entering Procedure' || ', p_auc_header_id = ' || p_auc_header_id || ' , p_bid_number = ' ||p_bid_number ||' , p_interface_type = '
4949 ||p_interface_type || ' , p_userid = '||p_userid||' ,p_spreadsheet = '||p_spreadsheet||' ,p_price_precision = '||p_price_precision
4950 ||' ,p_batch_id = '||p_batch_id||' ,p_request_id = '||p_request_id || ' , p_contract_type = ' || p_contract_type );
4952
4953
4954 INSERT ALL
4955
4956 -- The min quantity is a required field. If the min quantity is null,
4957 -- we insert rows into the interface errors table.
4958
4959 WHEN
4960 (
4961 s_min_quantity IS NULL
4962 OR
4963 s_min_quantity = g_null_int
4964 )
4965 THEN
4966 INTO pon_interface_errors
4967 (INTERFACE_TYPE,
4968 TABLE_NAME,
4969 COLUMN_NAME,
4970 BATCH_ID,
4971 INTERFACE_LINE_ID,
4972 ERROR_MESSAGE_NAME,
4973 CREATED_BY,
4974 CREATION_DATE,
4975 LAST_UPDATED_BY,
4976 LAST_UPDATE_DATE,
4977 REQUEST_ID,
4978 ENTITY_TYPE,
4979 ENTITY_ATTR_NAME,
4980 ERROR_VALUE_NUMBER,
4981 ERROR_VALUE_DATATYPE,
4982 AUCTION_HEADER_ID,
4983 BID_NUMBER,
4984 LINE_NUMBER,
4985 SHIPMENT_NUMBER,
4986 EXPIRATION_DATE,
4987 TOKEN1_NAME,
4988 TOKEN1_VALUE,
4989 WORKSHEET_NAME,
4990 WORKSHEET_SEQUENCE_NUMBER,
4991 ENTITY_MESSAGE_CODE)
4992 VALUES
4993 (p_interface_type,
4994 'PON_BID_SHIPMENTS',
4995 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
4996 p_batch_id,
4997 s_interface_line_id,
4998 'PON_AUCTS_PT_MIN_QUANTITY_REQ',
4999 p_userid,
5000 sysdate,
5001 p_userid,
5002 sysdate,
5003 p_request_id,
5004 'BID_PTS',
5005 'Quantity',
5006 s_min_quantity,
5007 'NUM',
5008 p_auc_header_id,
5009 p_bid_number,
5010 s_line_number,
5011 s_shipment_number,
5012 g_exp_date,
5013 'LINENUM',
5014 s_document_disp_line_number,
5015 s_worksheet_name,
5016 s_worksheet_seq_num,
5017 s_entity_name
5018 )
5019
5020 -- The max quantity is a required field. If the min quantity is null,
5021 -- we insert rows into the interface errors table.
5022
5023 WHEN
5024 (
5025 s_max_quantity IS NULL
5026 OR
5027 s_max_quantity = g_null_int
5028 )
5029 THEN
5030 INTO pon_interface_errors
5031 (INTERFACE_TYPE,
5032 TABLE_NAME,
5033 COLUMN_NAME,
5034 BATCH_ID,
5035 INTERFACE_LINE_ID,
5036 ERROR_MESSAGE_NAME,
5037 CREATED_BY,
5038 CREATION_DATE,
5039 LAST_UPDATED_BY,
5040 LAST_UPDATE_DATE,
5041 REQUEST_ID,
5042 ENTITY_TYPE,
5043 ENTITY_ATTR_NAME,
5044 ERROR_VALUE_NUMBER,
5045 ERROR_VALUE_DATATYPE,
5046 AUCTION_HEADER_ID,
5047 BID_NUMBER,
5048 LINE_NUMBER,
5049 SHIPMENT_NUMBER,
5050 EXPIRATION_DATE,
5051 TOKEN1_NAME,
5052 TOKEN1_VALUE,
5053 WORKSHEET_NAME,
5054 WORKSHEET_SEQUENCE_NUMBER,
5055 ENTITY_MESSAGE_CODE
5056 )
5057 VALUES
5058 (p_interface_type,
5059 'PON_BID_SHIPMENTS',
5060 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
5061 p_batch_id,
5062 s_interface_line_id,
5063 'PON_AUCTS_PT_MAX_QUANTITY_REQ',
5064 p_userid,
5065 sysdate,
5066 p_userid,
5067 sysdate,
5068 p_request_id,
5069 'BID_PTS',
5070 'MaxQuantity',
5071 s_max_quantity,
5072 'NUM',
5073 p_auc_header_id,
5074 p_bid_number,
5075 s_line_number,
5076 s_shipment_number,
5077 g_exp_date,
5078 'LINENUM',
5079 s_document_disp_line_number,
5080 s_worksheet_name,
5081 s_worksheet_seq_num,
5082 s_entity_name
5083 )
5084
5088 (
5085 -- The min quantity should be a positive number. i.e. strictly greater than zero.
5086
5087 WHEN
5089 (s_min_quantity IS NOT NULL AND
5090 s_min_quantity <= 0 AND
5091 s_min_quantity <> g_null_int)
5092 )
5093 THEN
5094 INTO pon_interface_errors
5095 (INTERFACE_TYPE,
5096 TABLE_NAME,
5097 COLUMN_NAME,
5098 BATCH_ID,
5099 INTERFACE_LINE_ID,
5100 ERROR_MESSAGE_NAME,
5101 CREATED_BY,
5102 CREATION_DATE,
5103 LAST_UPDATED_BY,
5104 LAST_UPDATE_DATE,
5105 REQUEST_ID,
5106 ENTITY_TYPE,
5107 ENTITY_ATTR_NAME,
5108 ERROR_VALUE_NUMBER,
5109 ERROR_VALUE_DATATYPE,
5110 AUCTION_HEADER_ID,
5111 BID_NUMBER,
5112 LINE_NUMBER,
5113 SHIPMENT_NUMBER,
5114 EXPIRATION_DATE,
5115 TOKEN1_NAME,
5116 TOKEN1_VALUE,
5117 WORKSHEET_NAME,
5118 WORKSHEET_SEQUENCE_NUMBER,
5119 ENTITY_MESSAGE_CODE
5120 )
5121 VALUES
5122 (p_interface_type,
5123 'PON_BID_SHIPMENTS',
5124 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
5125 p_batch_id,
5126 s_interface_line_id,
5127 'PON_AUCTS_PT_QUANTITY_POSITIVE',
5128 p_userid,
5129 sysdate,
5130 p_userid,
5131 sysdate,
5132 p_request_id,
5133 'BID_PTS',
5134 'Quantity',
5135 s_min_quantity,
5136 'NUM',
5137 p_auc_header_id,
5138 p_bid_number,
5139 s_line_number,
5140 s_shipment_number,
5141 g_exp_date,
5142 'LINENUM',
5143 s_document_disp_line_number,
5144 s_worksheet_name,
5145 s_worksheet_seq_num,
5146 s_entity_name
5147 )
5148
5149 -- The max quantity should be a positive number. i.e. strictly greater than zero.
5150
5151 WHEN
5152 (
5153 (s_max_quantity IS NOT NULL AND
5154 s_max_quantity <= 0 AND
5155 s_max_quantity <> g_null_int)
5156 )
5157 THEN
5158 INTO pon_interface_errors
5159 (INTERFACE_TYPE,
5160 TABLE_NAME,
5161 COLUMN_NAME,
5162 BATCH_ID,
5163 INTERFACE_LINE_ID,
5164 ERROR_MESSAGE_NAME,
5165 CREATED_BY,
5166 CREATION_DATE,
5167 LAST_UPDATED_BY,
5168 LAST_UPDATE_DATE,
5169 REQUEST_ID,
5170 ENTITY_TYPE,
5171 ENTITY_ATTR_NAME,
5172 ERROR_VALUE_NUMBER,
5173 ERROR_VALUE_DATATYPE,
5174 AUCTION_HEADER_ID,
5175 BID_NUMBER,
5176 LINE_NUMBER,
5177 SHIPMENT_NUMBER,
5178 EXPIRATION_DATE,
5179 TOKEN1_NAME,
5180 TOKEN1_VALUE,
5181 WORKSHEET_NAME,
5182 WORKSHEET_SEQUENCE_NUMBER,
5183 ENTITY_MESSAGE_CODE
5184 )
5185 VALUES
5186 (p_interface_type,
5187 'PON_BID_SHIPMENTS',
5188 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
5189 p_batch_id,
5190 s_interface_line_id,
5191 'PON_AUCTS_PT_QUANTITY_POSITIVE',
5192 p_userid,
5193 sysdate,
5194 p_userid,
5195 sysdate,
5196 p_request_id,
5197 'BID_PTS',
5198 'MaxQuantity',
5199 s_max_quantity,
5200 'NUM',
5201 p_auc_header_id,
5202 p_bid_number,
5203 s_line_number,
5204 s_shipment_number,
5205 g_exp_date,
5206 'LINENUM',
5207 s_document_disp_line_number,
5208 s_worksheet_name,
5209 s_worksheet_seq_num,
5210 s_entity_name
5211 )
5212
5213 -- max quantity should be greater or equal to the min quantity. i.e if min quantity should not
5214 -- be greater than max quantity
5215
5216 WHEN
5217 (
5218 s_min_quantity > s_max_quantity
5219 )
5220 THEN
5221 INTO pon_interface_errors
5222 (INTERFACE_TYPE,
5223 TABLE_NAME,
5224 COLUMN_NAME,
5225 BATCH_ID,
5226 INTERFACE_LINE_ID,
5227 ERROR_MESSAGE_NAME,
5228 CREATED_BY,
5229 CREATION_DATE,
5230 LAST_UPDATED_BY,
5231 LAST_UPDATE_DATE,
5232 REQUEST_ID,
5236 ERROR_VALUE_DATATYPE,
5233 ENTITY_TYPE,
5234 ENTITY_ATTR_NAME,
5235 ERROR_VALUE_NUMBER,
5237 AUCTION_HEADER_ID,
5238 BID_NUMBER,
5239 LINE_NUMBER,
5240 SHIPMENT_NUMBER,
5241 EXPIRATION_DATE,
5242 TOKEN1_NAME,
5243 TOKEN1_VALUE,
5244 WORKSHEET_NAME,
5245 WORKSHEET_SEQUENCE_NUMBER,
5246 ENTITY_MESSAGE_CODE
5247 )
5248 VALUES
5249 (p_interface_type,
5250 'PON_BID_SHIPMENTS',
5251 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
5252 p_batch_id,
5253 s_interface_line_id,
5254 'PON_QT_MAX_MIN_QTY_ERR',
5255 p_userid,
5256 sysdate,
5257 p_userid,
5258 sysdate,
5259 p_request_id,
5260 'BID_PTS',
5261 'MaxQuantity',
5262 s_max_quantity,
5263 'NUM',
5264 p_auc_header_id,
5265 p_bid_number,
5266 s_line_number,
5267 s_shipment_number,
5268 g_exp_date,
5269 'LINENUM',
5270 s_document_disp_line_number,
5271 s_worksheet_name,
5272 s_worksheet_seq_num,
5273 s_entity_name
5274 )
5275
5276 WHEN (
5277 p_spreadsheet = g_xml_upload_mode AND
5278 EXISTS (
5279 SELECT 'Y' FROM pon_bid_shipments bpb1 WHERE
5280 bpb1.auction_header_id = p_auc_header_id AND
5281 bpb1.bid_number = p_bid_number AND
5282 bpb1.line_number = s_line_number AND
5283 bpb1.SHIPMENT_NUMBER <> s_shipment_number AND
5284 (bpb1.quantity <= s_min_quantity AND
5285 s_min_quantity <= bpb1.max_quantity)
5286 )
5287 )
5288
5289 THEN
5290 INTO pon_interface_errors
5291 (INTERFACE_TYPE,
5292 TABLE_NAME,
5293 COLUMN_NAME,
5294 BATCH_ID,
5295 INTERFACE_LINE_ID,
5296 ERROR_MESSAGE_NAME,
5297 CREATED_BY,
5298 CREATION_DATE,
5299 LAST_UPDATED_BY,
5300 LAST_UPDATE_DATE,
5301 REQUEST_ID,
5302 ENTITY_TYPE,
5303 ENTITY_ATTR_NAME,
5304 ERROR_VALUE_NUMBER,
5305 ERROR_VALUE_DATATYPE,
5306 AUCTION_HEADER_ID,
5307 BID_NUMBER,
5308 LINE_NUMBER,
5309 SHIPMENT_NUMBER,
5310 EXPIRATION_DATE,
5311 TOKEN1_NAME,
5312 TOKEN1_VALUE,
5313 WORKSHEET_NAME,
5314 WORKSHEET_SEQUENCE_NUMBER,
5315 ENTITY_MESSAGE_CODE)
5316 VALUES
5317 (p_interface_type,
5318 'PON_BID_SHIPMENTS',
5319 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
5320 p_batch_id,
5321 s_interface_line_id,
5322 'PON_AUC_OVERLAP_RANGES_QT',
5323 p_userid,
5324 sysdate,
5325 p_userid,
5326 sysdate,
5327 p_request_id,
5328 'BID_PTS',
5329 'Quantity',
5330 s_min_quantity,
5331 'NUM',
5332 p_auc_header_id,
5333 p_bid_number,
5334 s_line_number,
5335 s_shipment_number,
5336 g_exp_date,
5337 'LINENUM',
5338 s_document_disp_line_number,
5339 s_worksheet_name,
5340 s_worksheet_seq_num,
5341 s_entity_name)
5342
5343 --when max quantity is in some other range
5344 --but min quantity is not in some other range
5345 --For case like: row1 is 5-10, row2 is 1-100
5346 --The error message only show up once.
5347 WHEN (
5348 p_spreadsheet = g_xml_upload_mode AND
5349 EXISTS (
5350 SELECT 'Y' FROM pon_bid_shipments bpb1 WHERE
5351 bpb1.auction_header_id = p_auc_header_id AND
5352 bpb1.bid_number = p_bid_number AND
5353 bpb1.line_number = s_line_number AND
5354 bpb1.SHIPMENT_NUMBER <> s_shipment_number AND
5355 ( NOT(bpb1.quantity <= s_min_quantity AND
5356 s_min_quantity <= bpb1.max_quantity)
5357 AND
5358 (bpb1.quantity <= s_max_quantity AND
5359 s_max_quantity <= bpb1.max_quantity))
5360 )
5361 )
5362
5363 THEN
5364 INTO pon_interface_errors
5365 (INTERFACE_TYPE,
5366 TABLE_NAME,
5370 ERROR_MESSAGE_NAME,
5367 COLUMN_NAME,
5368 BATCH_ID,
5369 INTERFACE_LINE_ID,
5371 CREATED_BY,
5372 CREATION_DATE,
5373 LAST_UPDATED_BY,
5374 LAST_UPDATE_DATE,
5375 REQUEST_ID,
5376 ENTITY_TYPE,
5377 ENTITY_ATTR_NAME,
5378 ERROR_VALUE_NUMBER,
5379 ERROR_VALUE_DATATYPE,
5380 AUCTION_HEADER_ID,
5381 BID_NUMBER,
5382 LINE_NUMBER,
5383 SHIPMENT_NUMBER,
5384 EXPIRATION_DATE,
5385 TOKEN1_NAME,
5386 TOKEN1_VALUE,
5387 WORKSHEET_NAME,
5388 WORKSHEET_SEQUENCE_NUMBER,
5389 ENTITY_MESSAGE_CODE)
5390 VALUES
5391 (p_interface_type,
5392 'PON_BID_SHIPMENTS',
5393 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
5394 p_batch_id,
5395 s_interface_line_id,
5396 'PON_AUC_OVERLAP_RANGES_QT',
5397 p_userid,
5398 sysdate,
5399 p_userid,
5400 sysdate,
5401 p_request_id,
5402 'BID_PTS',
5403 'MaxQuantity',
5404 s_max_quantity,
5405 'NUM',
5406 p_auc_header_id,
5407 p_bid_number,
5408 s_line_number,
5409 s_shipment_number,
5410 g_exp_date,
5411 'LINENUM',
5412 s_document_disp_line_number,
5413 s_worksheet_name,
5414 s_worksheet_seq_num,
5415 s_entity_name)
5416
5417 -- bid_currency_unit_price must not be null
5418 WHEN (s_bid_currency_unit_price IS NULL
5419 OR
5420 s_bid_currency_unit_price = g_null_int) THEN
5421
5422 INTO pon_interface_errors
5423 (INTERFACE_TYPE,
5424 TABLE_NAME,
5425 COLUMN_NAME,
5426 BATCH_ID,
5427 INTERFACE_LINE_ID,
5428 ERROR_MESSAGE_NAME,
5429 CREATED_BY,
5430 CREATION_DATE,
5431 LAST_UPDATED_BY,
5432 LAST_UPDATE_DATE,
5433 REQUEST_ID,
5434 ENTITY_TYPE,
5435 ENTITY_ATTR_NAME,
5436 ERROR_VALUE_NUMBER,
5437 ERROR_VALUE_DATATYPE,
5438 AUCTION_HEADER_ID,
5439 BID_NUMBER,
5440 LINE_NUMBER,
5441 SHIPMENT_NUMBER,
5442 EXPIRATION_DATE,
5443 TOKEN1_NAME,
5444 TOKEN1_VALUE,
5445 WORKSHEET_NAME,
5446 WORKSHEET_SEQUENCE_NUMBER,
5447 ENTITY_MESSAGE_CODE
5448 )
5449 VALUES
5450 (p_interface_type,
5451 'PON_BID_SHIPMENTS',
5452 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
5453 p_batch_id,
5454 s_interface_line_id,
5455 'PON_AUC_PT_PRICE_REQ',
5456 p_userid,
5457 sysdate,
5458 p_userid,
5459 sysdate,
5460 p_request_id,
5461 'BID_PTS',
5462 'BidCurrencyUnitPrice',
5463 s_bid_currency_unit_price,
5464 'NUM',
5465 p_auc_header_id,
5466 p_bid_number,
5467 s_line_number,
5468 s_shipment_number,
5469 g_exp_date,
5470 'LINENUM',
5471 s_document_disp_line_number,
5472 s_worksheet_name,
5473 s_worksheet_seq_num,
5474 s_entity_name
5475 )
5476
5477 -- bid_currency_unit_price must be positive
5478
5479 WHEN
5480 (s_bid_currency_unit_price IS NOT NULL AND
5481 s_bid_currency_unit_price <> g_null_int AND
5482 s_bid_currency_unit_price <= 0)
5483 THEN
5484 INTO pon_interface_errors
5485 (INTERFACE_TYPE,
5486 TABLE_NAME,
5487 COLUMN_NAME,
5488 BATCH_ID,
5489 INTERFACE_LINE_ID,
5490 ERROR_MESSAGE_NAME,
5491 CREATED_BY,
5492 CREATION_DATE,
5493 LAST_UPDATED_BY,
5494 LAST_UPDATE_DATE,
5495 REQUEST_ID,
5496 ENTITY_TYPE,
5497 ENTITY_ATTR_NAME,
5498 ERROR_VALUE_NUMBER,
5499 ERROR_VALUE_DATATYPE,
5500 AUCTION_HEADER_ID,
5501 BID_NUMBER,
5502 LINE_NUMBER,
5503 SHIPMENT_NUMBER,
5504 EXPIRATION_DATE,
5505 TOKEN1_NAME,
5506 TOKEN1_VALUE,
5507 WORKSHEET_NAME,
5508 WORKSHEET_SEQUENCE_NUMBER,
5509 ENTITY_MESSAGE_CODE
5510 )
5511 VALUES
5512 (p_interface_type,
5513 'PON_BID_SHIPMENTS',
5514 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
5515 p_batch_id,
5516 s_interface_line_id,
5517 'PON_AUCTS_QT_PRICE_POSITIVE',
5518 p_userid,
5519 sysdate,
5520 p_userid,
5521 sysdate,
5522 p_request_id,
5526 'NUM',
5523 'BID_PTS',
5524 'BidCurrencyUnitPrice',
5525 s_bid_currency_unit_price,
5527 p_auc_header_id,
5528 p_bid_number,
5529 s_line_number,
5530 s_shipment_number,
5531 g_exp_date,
5532 'LINENUM',
5533 s_document_disp_line_number,
5534 s_worksheet_name,
5535 s_worksheet_seq_num,
5536 s_entity_name
5537 )
5538
5539 -- bid_currency_unit_price precision must not exceed price precision
5540
5541 WHEN validate_price_precision(s_bid_currency_unit_price, p_price_precision) = 'F' THEN
5542 INTO pon_interface_errors
5543 (INTERFACE_TYPE,
5544 TABLE_NAME,
5545 COLUMN_NAME,
5546 BATCH_ID,
5547 INTERFACE_LINE_ID,
5548 ERROR_MESSAGE_NAME,
5549 CREATED_BY,
5550 CREATION_DATE,
5551 LAST_UPDATED_BY,
5552 LAST_UPDATE_DATE,
5553 REQUEST_ID,
5554 ENTITY_TYPE,
5555 ENTITY_ATTR_NAME,
5556 ERROR_VALUE_NUMBER,
5557 ERROR_VALUE_DATATYPE,
5558 AUCTION_HEADER_ID,
5559 BID_NUMBER,
5560 LINE_NUMBER,
5561 SHIPMENT_NUMBER,
5562 EXPIRATION_DATE,
5563 TOKEN1_NAME,
5564 TOKEN1_VALUE,
5565 WORKSHEET_NAME,
5566 WORKSHEET_SEQUENCE_NUMBER,
5567 ENTITY_MESSAGE_CODE
5568 )
5569 VALUES
5570 (p_interface_type,
5571 'PON_BID_SHIPMENTS',
5572 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
5573 p_batch_id,
5574 s_interface_line_id,
5575 'PON_AUC_QT_INVALID_BID_PREC',
5576 p_userid,
5577 sysdate,
5578 p_userid,
5579 sysdate,
5580 p_request_id,
5581 'BID_PTS',
5582 'BidCurrencyUnitPrice',
5583 s_bid_currency_unit_price,
5584 'NUM',
5585 p_auc_header_id,
5586 p_bid_number,
5587 s_line_number,
5588 s_shipment_number,
5589 g_exp_date,
5590 'LINENUMBER',
5591 s_document_disp_line_number,
5592 s_worksheet_name,
5593 s_worksheet_seq_num,
5594 s_entity_name
5595 )
5596
5597 -- in case of an SPO the quantity entered at the line level must be equal to the
5598 -- maximum quantity of all the price tiers.
5599
5600 WHEN
5601 (
5602 p_contract_type = 'STANDARD'
5603 and s_max_ship_qty = s_max_quantity
5604 and s_max_ship_qty <> s_bid_quantity
5605 )
5606 THEN
5607 INTO pon_interface_errors
5608 (INTERFACE_TYPE,
5609 TABLE_NAME,
5610 COLUMN_NAME,
5611 BATCH_ID,
5612 INTERFACE_LINE_ID,
5613 ERROR_MESSAGE_NAME,
5614 CREATED_BY,
5615 CREATION_DATE,
5616 LAST_UPDATED_BY,
5617 LAST_UPDATE_DATE,
5618 REQUEST_ID,
5619 ENTITY_TYPE,
5620 ENTITY_ATTR_NAME,
5621 ERROR_VALUE_NUMBER,
5622 ERROR_VALUE_DATATYPE,
5623 AUCTION_HEADER_ID,
5624 BID_NUMBER,
5625 LINE_NUMBER,
5626 SHIPMENT_NUMBER,
5627 EXPIRATION_DATE,
5628 TOKEN1_NAME,
5629 TOKEN1_VALUE,
5630 WORKSHEET_NAME,
5631 WORKSHEET_SEQUENCE_NUMBER,
5632 ENTITY_MESSAGE_CODE
5633 )
5634 VALUES
5635 (p_interface_type,
5636 'PON_BID_ITEM_PRICES',
5637 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
5638 p_batch_id,
5639 s_interface_line_id,
5640 'PON_BID_QTY_NOT_WITHIN_TIERS',
5641 p_userid,
5642 sysdate,
5643 p_userid,
5644 sysdate,
5645 p_request_id,
5646 'BID_PTS',
5647 'MaxQuantity',
5648 s_max_quantity,
5649 'NUM',
5650 p_auc_header_id,
5651 p_bid_number,
5652 s_line_number,
5653 s_shipment_number,
5654 g_exp_date,
5655 'LINENUM',
5656 s_document_disp_line_number,
5657 s_worksheet_name,
5658 s_worksheet_seq_num,
5659 s_entity_name
5660 )
5661
5662
5663 -- in case of an SPO the price entered at the line level must be equal to the price
5664 -- corresponding to the maximum quantity of all the price tiers.
5665 WHEN (p_contract_type = 'STANDARD'
5666 and s_max_ship_qty = s_bid_quantity
5667 and s_max_ship_qty = s_max_quantity
5668 and s_bid_price <> s_shipments_price) THEN
5669 INTO pon_interface_errors
5670 (INTERFACE_TYPE,
5671 TABLE_NAME,
5672 COLUMN_NAME,
5673 BATCH_ID,
5674 INTERFACE_LINE_ID,
5675 ERROR_MESSAGE_NAME,
5679 LAST_UPDATE_DATE,
5676 CREATED_BY,
5677 CREATION_DATE,
5678 LAST_UPDATED_BY,
5680 REQUEST_ID,
5681 ENTITY_TYPE,
5682 ENTITY_ATTR_NAME,
5683 ERROR_VALUE_NUMBER,
5684 ERROR_VALUE_DATATYPE,
5685 AUCTION_HEADER_ID,
5686 BID_NUMBER,
5687 LINE_NUMBER,
5688 SHIPMENT_NUMBER,
5689 EXPIRATION_DATE,
5690 TOKEN1_NAME,
5691 TOKEN1_VALUE,
5692 WORKSHEET_NAME,
5693 WORKSHEET_SEQUENCE_NUMBER,
5694 ENTITY_MESSAGE_CODE
5695 )
5696 VALUES
5697 (p_interface_type,
5698 'PON_BID_ITEM_PRICES',
5699 decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
5700 p_batch_id,
5701 s_interface_line_id,
5702 'PON_BID_PRICE_NOT_WITHIN_TIERS',
5703 p_userid,
5704 sysdate,
5705 p_userid,
5706 sysdate,
5707 p_request_id,
5708 'BID_PTS',
5709 'BidCurrencyUnitPrice',
5710 s_bid_currency_unit_price,
5711 'NUM',
5712 p_auc_header_id,
5713 p_bid_number,
5714 s_line_number,
5715 s_shipment_number,
5716 g_exp_date,
5717 'LINENUM',
5718 s_document_disp_line_number,
5719 s_worksheet_name,
5720 s_worksheet_seq_num,
5721 s_entity_name
5722 )
5723
5724 SELECT bpb.quantity s_min_quantity,
5725 bpb.max_quantity s_max_quantity,
5726 bpb.price s_shipments_price,
5727 bl.quantity s_bid_quantity,
5728 bl.price s_bid_price,
5729 bpb.bid_currency_unit_price s_bid_currency_unit_price,
5730 bpb.line_number s_line_number,
5731 bpb.shipment_number s_shipment_number,
5732 decode(p_spreadsheet, g_xml_upload_mode, bpb.interface_line_id, bl.interface_line_id) s_interface_line_id,
5733 al.document_disp_line_number s_document_disp_line_number,
5734 max_bid_shipments.max_quantity s_max_ship_qty,
5735 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
5736 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
5737 decode(p_spreadsheet, g_xml_upload_mode,
5738 'PON_AUCTS_PRICE_TIERS',
5739 null
5740 ) s_entity_name
5741 FROM pon_bid_shipments bpb,
5742 pon_bid_item_prices bl,
5743 pon_auction_item_prices_all al,
5744 (SELECT MAX(shipments.max_quantity) max_quantity,
5745 shipments.bid_number,
5746 shipments.line_number
5747 FROM pon_bid_shipments shipments
5748 WHERE shipments.bid_number = p_bid_number
5749 GROUP BY shipments.bid_number,
5750 shipments.line_number) max_bid_shipments
5751 , pon_bid_headers pbh
5752 WHERE bpb.bid_number = p_bid_number
5753 AND bpb.bid_number = max_bid_shipments.bid_number
5754 AND bpb.line_number = max_bid_shipments.line_number
5755 AND bl.bid_number = bpb.bid_number
5756 AND bl.line_number = bpb.line_number
5757 AND al.auction_header_id = bpb.auction_header_id
5758 AND al.line_number = bpb.line_number
5759 AND bl.is_changed_line_flag = 'Y'
5760 AND(p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
5761 AND pbh.bid_number = bl.bid_number
5762 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
5763
5764
5765 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
5766 FND_LOG.string(log_level => FND_LOG.level_procedure,
5767 module => g_module_prefix || 'VALIDATE_QTY_BASED_PRICE_TIERS',
5768 message => 'Performing overlapping tiers validation for online bidding flow.');
5769 END IF; --}
5770
5771
5772 Insert INTO pon_interface_errors
5773 (INTERFACE_TYPE,
5774 TABLE_NAME,
5775 BATCH_ID,
5776 INTERFACE_LINE_ID,
5777 ERROR_MESSAGE_NAME,
5778 CREATED_BY,
5779 CREATION_DATE,
5780 LAST_UPDATED_BY,
5781 LAST_UPDATE_DATE,
5782 REQUEST_ID,
5783 ENTITY_TYPE,
5784 ENTITY_ATTR_NAME,
5785 ERROR_VALUE_DATATYPE,
5786 AUCTION_HEADER_ID,
5787 BID_NUMBER,
5788 LINE_NUMBER,
5789 EXPIRATION_DATE,
5790 TOKEN1_NAME,
5791 TOKEN1_VALUE)
5792 SELECT
5793 p_interface_type,
5794 'PON_BID_SHIPMENTS',
5795 p_batch_id,
5796 bl.interface_line_id,
5797 'PON_AUC_OVERLAP_RANGES_QT',
5798 p_userid,
5799 sysdate,
5800 p_userid,
5801 sysdate,
5802 p_request_id,
5803 'BID_PTS',
5804 'Quantity',
5805 'NUM',
5806 p_auc_header_id,
5807 p_bid_number,
5811 al.document_disp_line_number
5808 bl.line_number,
5809 g_exp_date,
5810 'LINENUM',
5812 From pon_bid_item_prices bl
5813 , pon_auction_item_prices_all al
5814 , pon_bid_headers pbh
5815 where bl.bid_number = p_bid_number
5816 AND al.auction_header_id = bl.auction_header_id
5817 AND al.line_number = bl.line_number
5818 AND bl.is_changed_line_flag = 'Y'
5819 AND p_spreadsheet = g_online_mode
5820 and bl.line_number in
5821 ( Select distinct pbs.line_number
5822 FROM pon_bid_shipments pbs,
5823 pon_bid_shipments pbs1
5824 WHERE pbs.bid_number = p_bid_number
5825 and pbs1.bid_number = p_bid_number
5826 AND pbs.line_number = pbs1.line_number
5827 AND pbs.shipment_number <> pbs1.shipment_number
5828 AND pbs1.quantity <= pbs.quantity
5829 AND pbs.quantity <= pbs1.max_quantity)
5830 AND pbh.bid_number = bl.bid_number
5831 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
5832
5833 IF (FND_LOG.level_procedure>= FND_LOG.g_current_runtime_level) THEN --{
5834 FND_LOG.string(log_level => FND_LOG.level_procedure,
5835 module => g_module_prefix || 'VALIDATE_QTY_BASED_PRICE_TIERS',
5836 message => 'All the validations are performed. Exiting the method');
5837 END IF; --}
5838
5839 END VALIDATE_QTY_BASED_PRICE_TIERS;
5840
5841 PROCEDURE validate_price_differentials
5842 (
5843 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
5844 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
5845 p_interface_type IN pon_interface_errors.interface_type%TYPE,
5846 p_userid IN pon_interface_errors.created_by%TYPE,
5847 p_spreadsheet IN VARCHAR2,
5848 p_suffix IN VARCHAR2,
5849 p_batch_id IN pon_interface_errors.batch_id%TYPE,
5850 p_request_id IN pon_interface_errors.request_id%TYPE
5851 ) IS
5852 BEGIN
5853
5854 INSERT FIRST
5855
5856 -- Line price differential validation:
5857 -- multiplier should be entered for REQUIRED price differentials
5858 WHEN s_shipment_number = -1 AND s_multiplier IS null
5859 AND s_differential_response_type = 'REQUIRED' THEN
5860 INTO pon_interface_errors
5861 (INTERFACE_TYPE,
5862 COLUMN_NAME,
5863 TABLE_NAME,
5864 BATCH_ID,
5865 INTERFACE_LINE_ID,
5866 ERROR_MESSAGE_NAME,
5867 CREATED_BY,
5868 CREATION_DATE,
5869 LAST_UPDATED_BY,
5870 LAST_UPDATE_DATE,
5871 REQUEST_ID,
5872 ENTITY_TYPE,
5873 ENTITY_ATTR_NAME,
5874 ERROR_VALUE_DATATYPE,
5875 AUCTION_HEADER_ID,
5876 BID_NUMBER,
5877 LINE_NUMBER,
5878 SHIPMENT_NUMBER,
5879 PRICE_DIFFERENTIAL_NUMBER,
5880 EXPIRATION_DATE,
5881 TOKEN1_NAME,
5882 TOKEN1_VALUE,
5883 TOKEN2_NAME,
5884 TOKEN2_VALUE,
5885 WORKSHEET_NAME,
5886 WORKSHEET_SEQUENCE_NUMBER,
5887 ENTITY_MESSAGE_CODE)
5888 VALUES
5889 (p_interface_type,
5890 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
5891 'PON_BID_PRICE_DIFFERENTIALS',
5892 p_batch_id,
5893 s_interface_line_id,
5894 'PON_AUC_PD_VALUE_REQ' || p_suffix,
5895 p_userid,
5896 sysdate,
5897 p_userid,
5898 sysdate,
5899 p_request_id,
5900 'BID_PDS',
5901 'Multiplier',
5902 'TXT',
5903 p_auc_header_id,
5904 p_bid_number,
5905 s_line_number,
5906 s_shipment_number,
5907 s_price_differential_number,
5908 g_exp_date,
5909 'LINENUMBER',
5910 s_document_disp_line_number,
5911 'PRICEDIFFERENTIALNAME',
5912 s_price_differential_name,
5913 s_worksheet_name,
5914 s_worksheet_seq_num,
5915 s_entity_name)
5916
5917 -- Line price differential validation:
5918 -- multiplier should not be entered for DISPLAY_ONLY price differentials
5919 WHEN s_shipment_number = -1 AND s_multiplier IS NOT null
5920 AND s_differential_response_type = 'DISPLAY_ONLY' THEN
5921 INTO pon_interface_errors
5922 (INTERFACE_TYPE,
5923 COLUMN_NAME,
5924 TABLE_NAME,
5925 BATCH_ID,
5926 INTERFACE_LINE_ID,
5927 ERROR_MESSAGE_NAME,
5928 CREATED_BY,
5929 CREATION_DATE,
5930 LAST_UPDATED_BY,
5931 LAST_UPDATE_DATE,
5932 REQUEST_ID,
5933 ENTITY_TYPE,
5934 ENTITY_ATTR_NAME,
5935 ERROR_VALUE_NUMBER,
5936 ERROR_VALUE_DATATYPE,
5937 AUCTION_HEADER_ID,
5938 BID_NUMBER,
5939 LINE_NUMBER,
5940 SHIPMENT_NUMBER,
5941 PRICE_DIFFERENTIAL_NUMBER,
5942 EXPIRATION_DATE,
5943 TOKEN1_NAME,
5944 TOKEN1_VALUE,
5945 TOKEN2_NAME,
5946 TOKEN2_VALUE,
5947 WORKSHEET_NAME,
5948 WORKSHEET_SEQUENCE_NUMBER,
5949 ENTITY_MESSAGE_CODE)
5950 VALUES
5951 (p_interface_type,
5952 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
5956 'PON_AUC_PD_VAL_NONENTERABLE',
5953 'PON_BID_PRICE_DIFFERENTIALS',
5954 p_batch_id,
5955 s_interface_line_id ,
5957 p_userid,
5958 sysdate,
5959 p_userid,
5960 sysdate,
5961 p_request_id,
5962 'BID_PDS',
5963 'Multiplier',
5964 s_multiplier,
5965 'NUM',
5966 p_auc_header_id,
5967 p_bid_number,
5968 s_line_number,
5969 s_shipment_number,
5970 s_price_differential_number,
5971 g_exp_date,
5972 'LINENUMBER',
5973 s_document_disp_line_number,
5974 'PRICEDIFFERENTIALNAME',
5975 s_price_differential_name,
5976 s_worksheet_name,
5977 s_worksheet_seq_num,
5978 s_entity_name)
5979
5980 -- Line price differential validation:
5981 -- multiplier should be greater than the target multiplier
5982 WHEN s_shipment_number = -1 AND s_multiplier < s_target_multiplier THEN
5983 INTO pon_interface_errors
5984 (INTERFACE_TYPE,
5985 COLUMN_NAME,
5986 TABLE_NAME,
5987 BATCH_ID,
5988 INTERFACE_LINE_ID,
5989 ERROR_MESSAGE_NAME,
5990 CREATED_BY,
5991 CREATION_DATE,
5992 LAST_UPDATED_BY,
5993 LAST_UPDATE_DATE,
5994 REQUEST_ID,
5995 ENTITY_TYPE,
5996 ENTITY_ATTR_NAME,
5997 ERROR_VALUE_NUMBER,
5998 ERROR_VALUE_DATATYPE,
5999 AUCTION_HEADER_ID,
6000 BID_NUMBER,
6001 LINE_NUMBER,
6002 SHIPMENT_NUMBER,
6003 PRICE_DIFFERENTIAL_NUMBER,
6004 EXPIRATION_DATE,
6005 TOKEN1_NAME,
6006 TOKEN1_VALUE,
6007 TOKEN2_NAME,
6008 TOKEN2_VALUE,
6009 WORKSHEET_NAME,
6010 WORKSHEET_SEQUENCE_NUMBER,
6011 ENTITY_MESSAGE_CODE)
6012 VALUES
6013 (p_interface_type,
6014 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
6015 'PON_BID_PRICE_DIFFERENTIALS',
6016 p_batch_id,
6017 s_interface_line_id,
6018 'PON_AUC_PD_INVALID_MULT' || p_suffix,
6019 p_userid,
6020 sysdate,
6021 p_userid,
6022 sysdate,
6023 p_request_id,
6024 'BID_PDS',
6025 'Multiplier',
6026 s_multiplier,
6027 'NUM',
6028 p_auc_header_id,
6029 p_bid_number,
6030 s_line_number,
6031 s_shipment_number,
6032 s_price_differential_number,
6033 g_exp_date,
6034 'LINENUMBER',
6035 s_document_disp_line_number,
6036 'PRICEDIFFERENTIALNAME',
6037 s_price_differential_name,
6038 s_worksheet_name,
6039 s_worksheet_seq_num,
6040 s_entity_name
6041 )
6042
6043 -- Shipment price differential validation:
6044 -- multiplier should be entered for REQUIRED price differentials
6045 WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6046 and s_shipment_number <> -1 AND s_multiplier IS null
6047 AND s_differential_response_type = 'REQUIRED' THEN
6048 INTO pon_interface_errors
6049 (INTERFACE_TYPE,
6050 COLUMN_NAME,
6051 TABLE_NAME,
6052 BATCH_ID,
6053 INTERFACE_LINE_ID,
6054 ERROR_MESSAGE_NAME,
6055 CREATED_BY,
6056 CREATION_DATE,
6057 LAST_UPDATED_BY,
6058 LAST_UPDATE_DATE,
6059 REQUEST_ID,
6060 ENTITY_TYPE,
6061 ENTITY_ATTR_NAME,
6062 AUCTION_HEADER_ID,
6063 BID_NUMBER,
6064 LINE_NUMBER,
6065 SHIPMENT_NUMBER,
6066 PRICE_DIFFERENTIAL_NUMBER,
6067 ERROR_VALUE_DATATYPE,
6068 ERROR_VALUE,
6069 EXPIRATION_DATE,
6070 TOKEN1_NAME,
6071 TOKEN1_VALUE,
6072 TOKEN2_NAME,
6073 TOKEN2_VALUE,
6074 WORKSHEET_NAME,
6075 WORKSHEET_SEQUENCE_NUMBER,
6076 ENTITY_MESSAGE_CODE)
6077 VALUES
6078 (p_interface_type,
6079 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
6080 'PON_BID_PRICE_DIFFERENTIALS',
6081 p_batch_id,
6082 s_interface_line_id,
6083 'PON_AUC_PB_PD_VALUE_REQ' || p_suffix,
6084 p_userid,
6085 sysdate,
6086 p_userid,
6087 sysdate,
6088 p_request_id,
6089 'BID_PDS',
6090 'Multiplier',
6091 p_auc_header_id,
6092 p_bid_number,
6093 s_line_number,
6094 s_shipment_number,
6095 s_price_differential_number,
6096 'TXT',
6097 '',
6098 g_exp_date,
6099 'LINENUMBER',
6100 s_document_disp_line_number,
6101 'PRICEDIFFERENTIALNAME',
6102 s_price_differential_name,
6103 s_worksheet_name,
6104 s_worksheet_seq_num,
6105 s_entity_name
6106 )
6107 -- Shipment price differential validation:
6108 -- multiplier should not be entered for DISPLAY_ONLY price differentials
6109 WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6110 and s_shipment_number <> -1 AND s_multiplier IS NOT null
6111 AND s_differential_response_type = 'DISPLAY_ONLY' THEN
6112 INTO pon_interface_errors
6113 (INTERFACE_TYPE,
6114 COLUMN_NAME,
6115 TABLE_NAME,
6116 BATCH_ID,
6117 INTERFACE_LINE_ID,
6118 ERROR_MESSAGE_NAME,
6119 CREATED_BY,
6120 CREATION_DATE,
6121 LAST_UPDATED_BY,
6122 LAST_UPDATE_DATE,
6126 ERROR_VALUE_NUMBER,
6123 REQUEST_ID,
6124 ENTITY_TYPE,
6125 ENTITY_ATTR_NAME,
6127 ERROR_VALUE_DATATYPE,
6128 AUCTION_HEADER_ID,
6129 BID_NUMBER,
6130 LINE_NUMBER,
6131 SHIPMENT_NUMBER,
6132 PRICE_DIFFERENTIAL_NUMBER,
6133 EXPIRATION_DATE,
6134 TOKEN1_NAME,
6135 TOKEN1_VALUE,
6136 TOKEN2_NAME,
6137 TOKEN2_VALUE,
6138 WORKSHEET_NAME,
6139 WORKSHEET_SEQUENCE_NUMBER,
6140 ENTITY_MESSAGE_CODE)
6141 VALUES
6142 (p_interface_type,
6143 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
6144 'PON_BID_PRICE_DIFFERENTIALS',
6145 p_batch_id,
6146 s_interface_line_id,
6147 'PON_AUC_PB_PD_VAL_NONENTERABLE',
6148 p_userid,
6149 sysdate,
6150 p_userid,
6151 sysdate,
6152 p_request_id,
6153 'BID_PDS',
6154 'Multiplier',
6155 s_multiplier,
6156 'NUM',
6157 p_auc_header_id,
6158 p_bid_number,
6159 s_line_number,
6160 s_shipment_number,
6161 s_price_differential_number,
6162 g_exp_date,
6163 'LINENUMBER',
6164 s_document_disp_line_number,
6165 'PRICEDIFFERENTIALNAME',
6166 s_price_differential_name,
6167 s_worksheet_name,
6168 s_worksheet_seq_num,
6169 s_entity_name
6170 )
6171
6172 -- Shipment price differential validation:
6173 -- multiplier should be greater than target multiplier
6174 WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6175 and s_shipment_number <> -1
6176 AND s_multiplier < s_target_multiplier THEN
6177 INTO pon_interface_errors
6178 (INTERFACE_TYPE,
6179 COLUMN_NAME,
6180 TABLE_NAME,
6181 BATCH_ID,
6182 INTERFACE_LINE_ID,
6183 ERROR_MESSAGE_NAME,
6184 CREATED_BY,
6185 CREATION_DATE,
6186 LAST_UPDATED_BY,
6187 LAST_UPDATE_DATE,
6188 REQUEST_ID,
6189 ENTITY_TYPE,
6190 ENTITY_ATTR_NAME,
6191 ERROR_VALUE_NUMBER,
6192 ERROR_VALUE_DATATYPE,
6193 AUCTION_HEADER_ID,
6194 BID_NUMBER,
6195 LINE_NUMBER,
6196 SHIPMENT_NUMBER,
6197 PRICE_DIFFERENTIAL_NUMBER,
6198 EXPIRATION_DATE,
6199 TOKEN1_NAME,
6200 TOKEN1_VALUE,
6201 TOKEN2_NAME,
6202 TOKEN2_VALUE,
6203 WORKSHEET_NAME,
6204 WORKSHEET_SEQUENCE_NUMBER,
6205 ENTITY_MESSAGE_CODE
6206 )
6207 VALUES
6208 (p_interface_type,
6209 fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
6210 'PON_BID_PRICE_DIFFERENTIALS',
6211 p_batch_id,
6212 s_interface_line_id,
6213 'PON_AUC_PB_PD_INVALID_MULT' || p_suffix,
6214 p_userid,
6215 sysdate,
6216 p_userid,
6217 sysdate,
6218 p_request_id,
6219 'BID_PDS',
6220 'Multiplier',
6221 s_multiplier,
6222 'NUM',
6223 p_auc_header_id,
6224 p_bid_number,
6225 s_line_number,
6226 s_shipment_number,
6227 s_price_differential_number,
6228 g_exp_date,
6229 'LINENUMBER',
6230 s_document_disp_line_number,
6231 'PRICEDIFFERENTIALNAME',
6232 s_price_differential_name,
6233 s_worksheet_name,
6234 s_worksheet_seq_num,
6235 s_entity_name
6236 )
6237 SELECT
6238 bpd.multiplier s_multiplier,
6239 apd.multiplier s_target_multiplier,
6240 nvl(apb.differential_response_type, al.differential_response_type)
6241 s_differential_response_type,
6242 bpd.line_number s_line_number,
6243 bpd.shipment_number s_shipment_number,
6244 bpd.price_differential_number s_price_differential_number,
6245 al.document_disp_line_number s_document_disp_line_number,
6246 decode(p_spreadsheet, g_xml_upload_mode,
6247 bpd.interface_line_id,
6248 bl.interface_line_id
6249 ) s_interface_line_id,
6250 pdl.price_differential_dsp s_price_differential_name,
6251 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
6252 decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
6253 decode(p_spreadsheet, g_xml_upload_mode,
6254 'PON_PRICE_DIFFERENTIALS',
6255 null
6256 ) s_entity_name
6257 FROM pon_bid_price_differentials bpd
6258 , pon_bid_item_prices bl
6259 , pon_auction_item_prices_all al
6260 , pon_price_differentials apd
6261 , pon_auction_shipments_all apb
6262 , po_price_diff_lookups_v pdl
6263 , pon_bid_headers pbh
6264 WHERE bpd.bid_number = p_bid_number
6265 AND bl.bid_number = bpd.bid_number
6266 AND bl.line_number = bpd.line_number
6267 AND al.auction_header_id = bpd.auction_header_id
6268 AND al.line_number = bpd.line_number
6269 AND apd.auction_header_id = bpd.auction_header_id
6270 AND apd.line_number = bpd.line_number
6271 AND apd.shipment_number = decode(bpd.shipment_number, -1, -1, bpd.shipment_number - 1)
6272 AND apd.price_differential_number = bpd.price_differential_number
6273 AND apb.auction_header_id (+) = bpd.auction_header_id
6274 AND apb.line_number (+) = bpd.line_number
6275 AND apb.shipment_number (+) = decode(bpd.shipment_number, -1, -1, bpd.shipment_number - 1)
6279 AND pbh.bid_number = bl.bid_number
6276 AND pdl.price_differential_type = bpd.price_type
6277 AND bl.is_changed_line_flag = 'Y'
6278 AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
6280 AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
6281
6282 END validate_price_differentials;
6283
6284
6285
6286 PROCEDURE validate_payments
6287 (
6288 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
6289 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
6290 p_interface_type IN pon_interface_errors.interface_type%TYPE,
6291 p_userid IN pon_interface_errors.created_by%TYPE,
6292 p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
6293 p_batch_id IN pon_interface_errors.batch_id%TYPE,
6294 p_request_id IN pon_interface_errors.request_id%TYPE
6295 ) IS
6296 l_loginid NUMBER;
6297 l_exp_date DATE;
6298 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_PAYMENTS';
6299 l_progress varchar2(200);
6300 BEGIN
6301
6302 l_loginid := fnd_global.login_id;
6303 l_exp_date := SYSDATE + 7;
6304
6305 /* IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
6306 print_debug_log(l_module,'VALIDATE_PAYMENTS START p_batch_id = '||p_batch_id);
6307 END IF;
6308 */
6309
6310 BEGIN
6311 --To check for duplicate pay item number
6312 INSERT INTO pon_interface_errors
6313 (INTERFACE_TYPE,
6314 COLUMN_NAME,
6315 TABLE_NAME,
6316 BATCH_ID,
6317 ERROR_MESSAGE_NAME,
6318 CREATED_BY,
6319 CREATION_DATE,
6320 LAST_UPDATED_BY,
6321 LAST_UPDATE_DATE,
6322 REQUEST_ID,
6323 ENTITY_TYPE,
6324 ERROR_VALUE_NUMBER,
6325 ERROR_VALUE_DATATYPE,
6326 AUCTION_HEADER_ID,
6327 BID_NUMBER,
6328 LINE_NUMBER,
6329 EXPIRATION_DATE,
6330 TOKEN1_NAME,
6331 TOKEN1_VALUE,
6332 TOKEN2_NAME,
6333 TOKEN2_VALUE)
6334 SELECT
6335 p_interface_type,
6336 fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'),
6337 'PON_BID_ITEM_PRICES',
6338 p_batch_id,
6339 'PON_PYMT_NUM_NOT_UNQ',
6340 p_userid,
6341 sysdate,
6342 p_userid,
6343 sysdate,
6344 p_request_id,
6345 'BID_ITEMS',
6346 NULL,
6347 'TXT',
6348 p_auc_header_id,
6349 p_bid_number,
6350 pbps.bid_line_number,
6351 g_exp_date,
6352 'LINENUM',
6353 pai.document_disp_line_number,
6354 'PAYITEMNUM',
6355 pbps.payment_display_number
6356 FROM PON_BID_PAYMENTS_SHIPMENTS pbps,
6357 PON_AUCTION_ITEM_PRICES_ALL pai
6358 WHERE pbps.auction_header_id= pai.auction_header_id
6359 AND pbps.auction_header_id=p_auc_header_id
6360 AND pbps.bid_line_number = pai.line_number
6361 AND pbps.bid_number = p_bid_number
6362 GROUP BY pbps.bid_number, pbps.bid_line_number,
6363 pbps.payment_display_number, pai.document_disp_line_number
6364 HAVING count(*) > 1;
6365 EXCEPTION
6366 WHEN OTHERS THEN
6367 Raise;
6368
6369 END;
6370
6371
6372 INSERT ALL
6373 WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
6374 INTO pon_interface_errors
6375 (
6376 request_id, entity_attr_name, error_message_name, -- 1
6377 error_value_datatype, error_value_number, error_value_date, -- 2
6378 token1_name, token1_value, token2_name, -- 3
6379 token2_value, interface_type, table_name, -- 4
6380 batch_id, interface_line_id, entity_type, -- 5
6381 auction_header_id, line_number, bid_payment_id, -- 6
6382 expiration_date, created_by, creation_date, -- 7
6383 last_updated_by, last_update_date, last_update_login -- 8
6384 )
6385 VALUES
6386 (
6387 p_request_id, 'PaymentDisplayNumber', 'PON_PYMT_NUM_WRONG', -- 1
6388 'NUM', payment_display_number, NULL, -- 2
6389 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6390 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6391 p_batch_id, NULL, 'BID_PYMTS', -- 5
6392 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6393 l_exp_date, p_userid, SYSDATE, -- 7
6394 p_userid, SYSDATE, l_loginid -- 8
6395 )
6396 WHEN payment_type_code = 'RATE' AND quantity < 0 THEN
6397 INTO pon_interface_errors
6398 (
6399 request_id, entity_attr_name, error_message_name, -- 1
6400 error_value_datatype, error_value_number, error_value_date, -- 2
6401 token1_name, token1_value, token2_name, -- 3
6405 expiration_date, created_by, creation_date, -- 7
6402 token2_value, interface_type, table_name, -- 4
6403 batch_id, interface_line_id, entity_type, -- 5
6404 auction_header_id, line_number, bid_payment_id, -- 6
6406 last_updated_by, last_update_date, last_update_login -- 8
6407 )
6408 VALUES
6409 (
6410 p_request_id, 'Quantity', 'PON_PYMT_QTY_WRONG', -- 1
6411 'NUM', quantity, NULL, -- 2
6412 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6413 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6414 p_batch_id, NULL, 'BID_PYMTS', -- 5
6415 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6416 l_exp_date, p_userid, SYSDATE, -- 7
6417 p_userid, SYSDATE, l_loginid -- 8
6418 )
6419 WHEN bid_currency_price IS NOT NULL AND bid_currency_price < 0 THEN
6420 INTO pon_interface_errors
6421 (
6422 request_id, entity_attr_name, error_message_name, -- 1
6423 error_value_datatype, error_value_number, error_value_date, -- 2
6424 token1_name, token1_value, token2_name, -- 3
6425 token2_value, interface_type, table_name, -- 4
6426 batch_id, interface_line_id, entity_type, -- 5
6427 auction_header_id, line_number, bid_payment_id, -- 6
6428 expiration_date, created_by, creation_date, -- 7
6429 last_updated_by, last_update_date, last_update_login -- 8
6430 )
6431 VALUES
6432 (
6433 p_request_id, 'BidCurrencyPrice', 'PON_PYMT_PRICE_WRONG', -- 1
6434 'NUM', bid_currency_price, NULL, -- 2
6435 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6436 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6437 p_batch_id, NULL, 'BID_PYMTS', -- 5
6438 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6439 l_exp_date, p_userid, SYSDATE, -- 7
6440 p_userid, SYSDATE, l_loginid -- 8
6441 )
6442 WHEN payment_display_number IS NULL THEN
6443 INTO pon_interface_errors
6444 (
6445 request_id, entity_attr_name, error_message_name, -- 1
6446 error_value_datatype, error_value, error_value_date, -- 2
6447 token1_name, token1_value, token2_name, -- 3
6448 token2_value, interface_type, table_name, -- 4
6449 batch_id, interface_line_id, entity_type, -- 5
6450 auction_header_id, line_number, bid_payment_id, -- 6
6451 expiration_date, created_by, creation_date, -- 7
6452 last_updated_by, last_update_date, last_update_login -- 8
6453 )
6454 VALUES
6455 (
6456 p_request_id, 'PaymentDisplayNumber', 'PON_PYMT_NUM_MISSING', -- 1
6457 'TXT', payment_display_number, NULL, -- 2
6458 'LINENUM', document_disp_line_number, NULL, -- 3
6459 NULL, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6460 p_batch_id, NULL, 'BID_PYMTS', -- 5
6461 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6462 l_exp_date, p_userid, SYSDATE, -- 7
6463 p_userid, SYSDATE, l_loginid -- 8
6464 )
6465 WHEN payment_type_code IS NULL THEN
6466 INTO pon_interface_errors
6467 (
6468 request_id, entity_attr_name, error_message_name, -- 1
6469 error_value_datatype, error_value_number, error_value, -- 2
6470 token1_name, token1_value, token2_name, -- 3
6471 token2_value, interface_type, table_name, -- 4
6472 batch_id, interface_line_id, entity_type, -- 5
6473 auction_header_id, line_number, bid_payment_id, -- 6
6474 expiration_date, created_by, creation_date, -- 7
6478 (
6475 last_updated_by, last_update_date, last_update_login -- 8
6476 )
6477 VALUES
6479 p_request_id, 'PaymentTypeCode', 'PON_PYMT_TYPE_NULL', -- 1
6480 'TXT', NULL, NULL, -- 2
6481 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6482 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6483 p_batch_id, NULL, 'BID_PYMTS', -- 5
6484 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6485 l_exp_date, p_userid, SYSDATE, -- 7
6486 p_userid, SYSDATE, l_loginid -- 8
6487 )
6488 WHEN payment_description IS NULL THEN
6489 INTO pon_interface_errors
6490 (
6491 request_id, entity_attr_name, error_message_name, -- 1
6492 error_value_datatype, error_value_number, error_value, -- 2
6493 token1_name, token1_value, token2_name, -- 3
6494 token2_value, interface_type, table_name, -- 4
6495 batch_id, interface_line_id, entity_type, -- 5
6496 auction_header_id, line_number, bid_payment_id, -- 6
6497 expiration_date, created_by, creation_date, -- 7
6498 last_updated_by, last_update_date, last_update_login -- 8
6499 )
6500 VALUES
6501 (
6502 p_request_id, 'PaymentDescription', 'PON_PYMT_DESC_NULL', -- 1
6503 'TXT', NULL, NULL, -- 2
6504 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6505 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6506 p_batch_id, NULL, 'BID_PYMTS', -- 5
6507 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6508 l_exp_date, p_userid, SYSDATE, -- 7
6509 p_userid, SYSDATE, l_loginid -- 8
6510 )
6511 WHEN payment_type_code = 'RATE' AND quantity IS NULL THEN
6512 INTO pon_interface_errors
6513 (
6514 request_id, entity_attr_name, error_message_name, -- 1
6515 error_value_datatype, error_value, error_value_date, -- 2
6516 token1_name, token1_value, token2_name, -- 3
6517 token2_value, interface_type, table_name, -- 4
6518 batch_id, interface_line_id, entity_type, -- 5
6519 auction_header_id, line_number, bid_payment_id, -- 6
6520 expiration_date, created_by, creation_date, -- 7
6521 last_updated_by, last_update_date, last_update_login -- 8
6522 )
6523 VALUES
6524 (
6525 p_request_id, 'Quantity', 'PON_PYMT_QTY_NULL', -- 1
6526 'TXT', NULL, NULL, -- 2
6527 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6528 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6529 p_batch_id, NULL, 'BID_PYMTS', -- 5
6530 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6531 l_exp_date, p_userid, SYSDATE, -- 7
6532 p_userid, SYSDATE, l_loginid -- 8
6533 )
6534 WHEN payment_type_code = 'RATE' AND uom_code IS NULL THEN
6535 INTO pon_interface_errors
6536 (
6537 request_id, entity_attr_name, error_message_name, -- 1
6538 error_value_datatype, error_value, error_value_date, -- 2
6539 token1_name, token1_value, token2_name, -- 3
6540 token2_value, interface_type, table_name, -- 4
6541 batch_id, interface_line_id, entity_type, -- 5
6542 auction_header_id, line_number, bid_payment_id, -- 6
6543 expiration_date, created_by, creation_date, -- 7
6544 last_updated_by, last_update_date, last_update_login -- 8
6545 )
6546 VALUES
6547 (
6548 p_request_id, 'UomCode', 'PON_PYMT_UOM_NULL', -- 1
6549 'TXT', NULL, NULL, -- 2
6550 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6551 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6552 p_batch_id, NULL, 'BID_PYMTS', -- 5
6553 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6554 l_exp_date, p_userid, SYSDATE, -- 7
6555 p_userid, SYSDATE, l_loginid -- 8
6556 )
6557 WHEN bid_currency_price IS NULL THEN
6558 INTO pon_interface_errors
6559 (
6560 request_id, entity_attr_name, error_message_name, -- 1
6561 error_value_datatype, error_value, error_value_date, -- 2
6562 token1_name, token1_value, token2_name, -- 3
6563 token2_value, interface_type, table_name, -- 4
6564 batch_id, interface_line_id, entity_type, -- 5
6565 auction_header_id, line_number, bid_payment_id, -- 6
6566 expiration_date, created_by, creation_date, -- 7
6567 last_updated_by, last_update_date, last_update_login -- 8
6568 )
6569 VALUES
6570 (
6571 p_request_id, 'BidCurrencyPrice', 'PON_PYMT_BID_PRICE_NULL', -- 1
6572 'TXT', bid_currency_price, NULL, -- 2
6573 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6574 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6575 p_batch_id, NULL, 'BID_PYMTS', -- 5
6576 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6577 l_exp_date, p_userid, SYSDATE, -- 7
6578 p_userid, SYSDATE, l_loginid -- 8
6579 )
6580 WHEN promised_date IS NOT NULL AND promised_date <= close_bidding_date THEN
6581 INTO pon_interface_errors
6582 (
6583 request_id, entity_attr_name, error_message_name, -- 1
6584 error_value_datatype, error_value_number, error_value_date, -- 2
6585 token1_name, token1_value, token2_name, -- 3
6586 token2_value, interface_type, table_name, -- 4
6587 batch_id, interface_line_id, entity_type, -- 5
6588 auction_header_id, line_number, bid_payment_id, -- 6
6589 expiration_date, created_by, creation_date, -- 7
6590 last_updated_by, last_update_date, last_update_login -- 8
6591 )
6592 VALUES
6593 (
6594 p_request_id, 'PromisedDate', 'PON_PYMT_PDATE_LESS_CDATE', -- 1
6595 'DAT', NULL, promised_date, -- 2
6596 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6597 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6598 p_batch_id, NULL, 'BID_PYMTS', -- 5
6599 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6600 l_exp_date, p_userid, SYSDATE, -- 7
6601 p_userid, SYSDATE, l_loginid -- 8
6602 )
6603 WHEN bid_currency_price IS NOT NULL
6604 AND validate_price_precision(bid_currency_price, p_price_precision) = 'F' THEN
6605 INTO pon_interface_errors
6606 (
6607 request_id, entity_attr_name, error_message_name, -- 1
6608 error_value_datatype, error_value_number, error_value_date, -- 2
6609 token1_name, token1_value, token2_name, -- 3
6613 expiration_date, created_by, creation_date, -- 7
6610 token2_value, interface_type, table_name, -- 4
6611 batch_id, interface_line_id, entity_type, -- 5
6612 auction_header_id, line_number, bid_payment_id, -- 6
6614 last_updated_by, last_update_date, last_update_login -- 8
6615 )
6616 VALUES
6617 (
6618 p_request_id, 'BidCurrencyPrice', 'PON_QUOTEPRICE_INVALID_PREC_P', -- 1
6619 'NUM', bid_currency_price, NULL, -- 2
6620 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
6621 payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
6622 p_batch_id, NULL, 'BID_PYMTS', -- 5
6623 auction_header_id, document_disp_line_number, bid_payment_id, -- 6
6624 l_exp_date, p_userid, SYSDATE, -- 7
6625 p_userid, SYSDATE, l_loginid -- 8
6626 )
6627 SELECT
6628 pbp.payment_display_number,
6629 pbp.payment_type_code,
6630 pbp.uom_code,
6631 pbp.payment_description,
6632 pbp.auction_header_id auction_header_id,
6633 pai.document_disp_line_number,
6634 pbp.bid_currency_price,
6635 pbp.quantity,
6636 pbp.promised_date,
6637 pai.line_number auction_line_number,
6638 pai.close_bidding_date,
6639 pbp.bid_payment_id,
6640 pbi.line_number bid_line_number
6641 FROM PON_BID_PAYMENTS_SHIPMENTS pbp,
6642 PON_AUCTION_ITEM_PRICES_ALL pai,
6643 PON_BID_ITEM_PRICES pbi
6644 WHERE pbp.auction_header_id = pai.auction_header_id
6645 AND pbp.auction_line_number = pai.line_number
6646 AND pbi.auction_header_id = pai.auction_header_id
6647 AND pbi.line_number = pai.line_number
6648 AND pbp.bid_number = p_bid_number
6649 AND pbi.bid_number = pbp.bid_number
6650 AND nvl(pbi.has_bid_flag,'N') = 'Y';
6651
6652 /* IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
6653 print_debug_log(l_module,'After Insert all for validate_payments p_batch_id = '||p_batch_id);
6654 END IF;
6655 */
6656
6657 EXCEPTION
6658 WHEN OTHERS THEN
6659 /*
6660 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
6661 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' Error Code=' || SQLCODE || ' SQLERRM=' || SQLERRM);
6662 END if;
6663 */
6664 RAISE;
6665 END validate_payments;
6666
6667 PROCEDURE perform_all_validations
6668 (
6669 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
6670 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
6671 p_interface_type IN pon_interface_errors.interface_type%TYPE,
6672 p_userid IN pon_interface_errors.created_by%TYPE,
6673 p_batch_id IN pon_interface_errors.batch_id%TYPE,
6674 p_request_id IN pon_interface_errors.request_id%TYPE,
6675 p_spreadsheet IN VARCHAR2,
6676 x_return_status OUT NOCOPY NUMBER,
6677 x_return_code OUT NOCOPY VARCHAR2
6678 ) IS
6679 l_rate pon_bid_headers.rate%TYPE;
6680 l_price_precision pon_bid_headers.number_price_decimals%TYPE;
6681 l_amt_precision fnd_currencies.precision%TYPE;
6682
6683 l_source_bid pon_bid_headers.bid_number%TYPE;
6684 l_min_bid_change pon_bid_headers.min_bid_change%TYPE;
6685 l_min_bid_decr pon_auction_headers_all.min_bid_decrement%TYPE;
6686 l_percent_decr VARCHAR2(1);
6687 l_bid_decr_method pon_auction_headers_all.bid_decrement_method%TYPE;
6688
6689 l_po_start_date pon_auction_headers_all.po_start_date%TYPE;
6690 l_po_end_date pon_auction_headers_all.po_end_date%TYPE;
6691 l_auc_close_date pon_auction_headers_all.close_bidding_date%TYPE;
6692 l_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE;
6693 l_contract_type pon_auction_headers_all.contract_type%TYPE;
6694
6695 l_tpid pon_bid_headers.trading_partner_id%TYPE;
6696 l_vensid pon_bid_headers.vendor_site_id%TYPE;
6697 l_bid_curr_code pon_bid_headers.bid_currency_code%TYPE;
6698
6699 l_mas VARCHAR2(1);
6700 l_blanket VARCHAR2(1);
6701 l_global VARCHAR2(1);
6702 l_trans_view VARCHAR2(1);
6703 l_header_disp_pf VARCHAR2(1);
6704 l_full_qty_reqd VARCHAR2(1);
6705 l_spo_trans_view VARCHAR2(1);
6706 l_price_driven VARCHAR2(1);
6707 l_rebid VARCHAR2(1);
6708 l_bid_all_lines VARCHAR2(1);
6709 l_auc_has_items VARCHAR2(1);
6710 l_suffix VARCHAR2(2);
6711
6712 l_has_errors VARCHAR2(1);
6713 l_price_tiers_indicator pon_auction_headers_all.PRICE_TIERS_INDICATOR%TYPE;
6714 --added by Allen Yang for Surrogate Bid 2008/09/03
6715 --------------------------------------------------------------------------------------
6716 l_two_part_flag pon_auction_headers_all.two_part_flag%TYPE;
6717 l_technical_evaluation_status pon_auction_headers_all.TECHNICAL_EVALUATION_STATUS%TYPE;
6718 l_surrogate_bid_flag pon_bid_headers.SURROG_BID_FLAG%TYPE;
6719 l_two_part_tech_surrogate_flag VARCHAR2(1);
6720 --------------------------------------------------------------------------------------
6721 BEGIN
6722
6723 -- Select auction data
6724 SELECT sysdate + g_exp_days_offset,
6725 bh.rate,
6726 bh.number_price_decimals,
6727 fc.precision,
6728 decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
6729 decode(ah.contract_type, 'STANDARD',
6730 decode(ah.supplier_view_type, 'TRANSFORMED', 'Y', 'N'), 'N'),
6731 decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
6732 nvl(ah.global_agreement_flag, 'N'),
6736 nvl(ah.price_driven_auction_flag, 'Y'),
6733 decode(ah.supplier_view_type, 'TRANSFORMED', 'Y', 'N'),
6734 bh.display_price_factors_flag,
6735 decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
6737 bh.min_bid_change,
6738 ah.min_bid_decrement * bh.rate, -- convert to bid currency
6739 decode(ah.min_bid_change_type, 'PERCENTAGE', 'Y', 'N'),
6740 nvl(ah.bid_decrement_method, 'PREVIOUS_PRICE'),
6741 ah.po_start_date,
6742 ah.po_end_date,
6743 ah.close_bidding_date,
6744 decode(ah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'Y', 'N'),
6745 ah.has_items_flag,
6746 bh.trading_partner_id,
6747 bh.vendor_site_id,
6748 bh.bid_currency_code,
6749 decode(old_bh.bid_status, 'ACTIVE', 'Y', 'N'),
6750 nvl(old_bh.bid_number, 0),
6751 ah.contract_type,
6752 ah.progress_payment_type,
6753 ah.price_tiers_indicator
6754 --added by Allen Yang for Surrogate Bid 2008/09/03
6755 --------------------------------------------------
6756 , ah.TWO_PART_FLAG,
6757 ah.TECHNICAL_EVALUATION_STATUS,
6758 bh.SURROG_BID_FLAG
6759 --------------------------------------------------
6760 INTO g_exp_date,
6761 l_rate,
6762 l_price_precision,
6763 l_amt_precision,
6764 l_mas,
6765 l_spo_trans_view,
6766 l_blanket,
6767 l_global,
6768 l_trans_view,
6769 l_header_disp_pf,
6770 l_full_qty_reqd,
6771 l_price_driven,
6772 l_min_bid_change,
6773 l_min_bid_decr,
6774 l_percent_decr,
6775 l_bid_decr_method,
6776 l_po_start_date,
6777 l_po_end_date,
6778 l_auc_close_date,
6779 l_bid_all_lines,
6780 l_auc_has_items,
6781 l_tpid,
6782 l_vensid,
6783 l_bid_curr_code,
6784 l_rebid,
6785 l_source_bid,
6786 l_contract_type,
6787 l_progress_payment_type,
6788 l_price_tiers_indicator
6789 --added by Allen Yang for Surrogate Bid 2008/09/03
6790 --------------------------------------------------
6791 , l_two_part_flag,
6792 l_technical_evaluation_status,
6793 l_surrogate_bid_flag
6794 --------------------------------------------------
6795 FROM pon_auction_headers_all ah, pon_bid_headers bh,
6796 fnd_currencies fc, pon_bid_headers old_bh
6797 WHERE ah.auction_header_id = p_auc_header_id
6798 AND ah.auction_header_id = bh.auction_header_id
6799 AND bh.bid_number = p_bid_number
6800 AND fc.currency_code = bh.bid_currency_code
6801 AND old_bh.bid_number (+) = bh.old_bid_number;
6802
6803 l_suffix := PON_LARGE_AUCTION_UTIL_PKG.get_doctype_suffix(p_auc_header_id);
6804 --added by Allen Yang for Surrogate Bid 2008/09/03
6805 --------------------------------------------------
6806 l_two_part_tech_surrogate_flag := 'N';
6807 IF (l_two_part_flag = 'Y' AND l_technical_evaluation_status = 'NOT_COMPLETED' AND l_surrogate_bid_flag = 'Y')
6808 THEN
6809 l_two_part_tech_surrogate_flag := 'Y';
6810 END IF;
6811 --------------------------------------------------
6812
6813 IF (p_spreadsheet = g_online_mode) THEN
6814
6815 validate_bids_placed
6816 (p_auc_header_id,
6817 p_bid_number,
6818 p_interface_type,
6819 p_userid,
6820 l_rebid,
6821 l_bid_all_lines,
6822 l_auc_has_items,
6823 l_suffix,
6824 p_batch_id,
6825 p_request_id);
6826
6827 -- Check if there were any errors
6828 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
6829 INTO l_has_errors
6830 FROM pon_interface_errors
6831 WHERE (batch_id = p_batch_id OR request_id = p_request_id)
6832 AND rownum = 1;
6833
6834 IF (l_has_errors = 'Y') THEN
6835 x_return_status := 1;
6836 x_return_code := 'ERROR';
6837 RETURN;
6838 END IF;
6839 ELSE
6840 -- For spreadsheet upload, we need to first populate has bid
6841 -- and changed lines flags before performing validations.
6842 populate_has_bid_changed_line
6843 (p_auc_header_id,
6844 p_bid_number,
6845 l_source_bid,
6846 p_batch_id,
6847 l_rebid,
6848 l_blanket,
6849 p_spreadsheet);
6850 END IF;
6851
6852 validate_lots_and_groups
6853 (p_auc_header_id,
6854 p_bid_number,
6855 p_interface_type,
6856 p_userid,
6857 p_spreadsheet,
6858 p_batch_id,
6859 p_request_id);
6860
6861 validate_lines
6862 (p_auc_header_id,
6863 p_bid_number,
6864 p_interface_type,
6865 p_userid,
6866 l_tpid,
6867 l_vensid,
6868 p_spreadsheet,
6869 l_blanket,
6870 l_global,
6871 l_trans_view,
6872 l_rebid,
6873 l_full_qty_reqd,
6874 l_header_disp_pf,
6875 l_price_driven,
6876 l_percent_decr,
6877 l_bid_decr_method,
6878 l_min_bid_decr,
6879 l_min_bid_change,
6880 l_rate,
6881 l_price_precision,
6882 l_amt_precision,
6883 l_bid_curr_code,
6884 l_suffix,
6885 p_batch_id,
6886 p_request_id
6887 --added by Allen Yang for Surrogate Bid 2008/09/03
6888 --------------------------------------------------
6889 ,l_two_part_tech_surrogate_flag
6890 --------------------------------------------------
6891 );
6892
6893
6894 IF (p_spreadsheet in (g_online_mode, g_xml_upload_mode)) THEN
6895 validate_requirements
6896 (p_auc_header_id,
6897 p_bid_number,
6898 p_interface_type,
6899 p_userid,
6900 p_spreadsheet,
6901 l_suffix,
6902 p_batch_id,
6903 p_request_id
6904 --added by Allen Yang for Surrogate Bid 2008/09/03
6905 --------------------------------------------------
6909 END IF;
6906 , l_two_part_tech_surrogate_flag
6907 --------------------------------------------------
6908 );
6910
6911 -- If spreadsheet upload, calculate the total weighted score for MAS negotiations
6912 IF (p_spreadsheet in (g_txt_upload_mode, g_xml_upload_mode) AND l_mas = 'Y') THEN
6913 calc_total_weighted_score(p_bid_number, p_batch_id);
6914 END IF;
6915
6916 validate_attributes
6917 (p_auc_header_id,
6918 p_bid_number,
6919 p_interface_type,
6920 p_userid,
6921 p_spreadsheet,
6922 l_suffix,
6923 p_batch_id,
6924 p_request_id);
6925
6926 IF (l_header_disp_pf = 'Y')
6927 --added by Allen Yang for Surrogate Bid 2008/09/03
6928 --------------------------------------------------
6929 AND l_two_part_tech_surrogate_flag = 'N'
6930 --------------------------------------------------
6931 THEN
6932 validate_cost_factors
6933 (p_auc_header_id,
6934 p_bid_number,
6935 p_interface_type,
6936 p_userid,
6937 p_spreadsheet,
6938 l_price_precision,
6939 l_amt_precision,
6940 l_suffix,
6941 p_batch_id,
6942 p_request_id);
6943 END IF;
6944
6945 IF (p_spreadsheet in ( g_online_mode, g_xml_upload_mode)) THEN
6946 IF( l_price_tiers_indicator = 'PRICE_BREAKS')
6947 --added by Allen Yang for Surrogate Bid 2008/09/03
6948 --------------------------------------------------
6949 AND l_two_part_tech_surrogate_flag = 'N'
6950 --------------------------------------------------
6951 THEN
6952
6953 validate_price_breaks
6954 (p_auc_header_id,
6955 p_bid_number,
6956 p_interface_type,
6957 p_userid,
6958 p_spreadsheet,
6959 l_price_precision,
6960 l_trans_view,
6961 l_blanket,
6962 l_header_disp_pf,
6963 l_po_start_date,
6964 l_po_end_date,
6965 l_auc_close_date,
6966 l_suffix,
6967 p_batch_id,
6968 p_request_id);
6969
6970 ELSIF( l_price_tiers_indicator = 'QUANTITY_BASED')
6971 --added by Allen Yang for Surrogate Bid 2008/09/03
6972 --------------------------------------------------
6973 AND l_two_part_tech_surrogate_flag = 'N'
6974 --------------------------------------------------
6975 THEN
6976
6977 validate_qty_based_price_tiers
6978 (p_auc_header_id,
6979 p_bid_number,
6980 p_interface_type,
6981 p_userid,
6982 p_spreadsheet,
6983 l_price_precision,
6984 p_batch_id,
6985 p_request_id,
6986 l_contract_type);
6987
6988 END IF;
6989 END IF;
6990
6991 --modified by Allen Yang for Surrogate Bid 2008/09/03
6992 -----------------------------------------------------
6993 --validate_price_differentials
6994 -- (p_auc_header_id,
6995 -- p_bid_number,
6996 -- p_interface_type,
6997 -- p_userid,
6998 -- p_spreadsheet,
6999 -- l_suffix,
7000 -- p_batch_id,
7001 -- p_request_id);
7002 IF (l_two_part_tech_surrogate_flag = 'N') THEN
7003 validate_price_differentials
7004 (p_auc_header_id,
7005 p_bid_number,
7006 p_interface_type,
7007 p_userid,
7008 p_spreadsheet,
7009 l_suffix,
7010 p_batch_id,
7011 p_request_id);
7012 END IF;
7013 -----------------------------------------------------
7014
7015 IF
7016 --added by Allen Yang for Surrogate Bid 2009/09/03
7017 --------------------------------------------------
7018 l_two_part_tech_surrogate_flag = 'N' AND
7019 --------------------------------------------------
7020 l_progress_payment_type <> 'NONE' AND l_contract_type = 'STANDARD' AND NVL(p_spreadsheet,g_online_mode) = g_online_mode
7021 THEN
7022 validate_payments
7023 (
7024 p_auc_header_id => p_auc_header_id,
7025 p_bid_number => p_bid_number,
7026 p_interface_type => p_interface_type,
7027 p_userid => p_userid,
7028 p_price_precision => l_price_precision,
7029 p_batch_id => p_batch_id,
7030 p_request_id => p_request_id);
7031 END IF;
7032
7033 -- Check if any errors were present
7034 SELECT decode(count(auction_header_id), 0, 'N', 'Y')
7035 INTO l_has_errors
7036 FROM pon_interface_errors
7037 WHERE (batch_id = p_batch_id OR request_id = p_request_id)
7038 AND rownum = 1;
7039
7040 IF (l_has_errors = 'Y') THEN
7041 x_return_status := 1;
7042 x_return_code := 'ERRORS';
7043 ELSE
7044 x_return_status := 0;
7045 x_return_code := 'SUCCESS';
7046 END IF;
7047
7048 END perform_all_validations;
7049
7050 PROCEDURE validate_bid
7051 (
7052 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7053 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7054 p_interface_type IN pon_interface_errors.interface_type%TYPE,
7055 p_userid IN pon_interface_errors.created_by%TYPE,
7056 p_batch_id IN pon_interface_errors.batch_id%TYPE,
7057 p_request_id IN pon_interface_errors.request_id%TYPE,
7058 x_return_status OUT NOCOPY NUMBER,
7059 x_return_code OUT NOCOPY VARCHAR2
7060 ) IS
7061 BEGIN
7062
7063 perform_all_validations(p_auc_header_id, p_bid_number, p_interface_type,
7064 p_userid, p_batch_id, p_request_id, g_online_mode, x_return_status, x_return_code);
7065
7066 END validate_bid;
7067
7068 PROCEDURE validate_spreadsheet_upload
7069 (
7070 p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7071 p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7072 p_interface_type IN pon_interface_errors.interface_type%TYPE,
7073 p_spreadsheet_type IN VARCHAR2,
7074 p_userid IN pon_interface_errors.created_by%TYPE,
7075 p_batch_id IN pon_interface_errors.batch_id%TYPE,
7076 p_request_id IN pon_interface_errors.request_id%TYPE,
7077 x_return_status OUT NOCOPY NUMBER,
7078 x_return_code OUT NOCOPY VARCHAR2
7079 ) IS
7080 BEGIN
7081
7082 perform_all_validations(p_auc_header_id, p_bid_number, p_interface_type,
7083 p_userid, p_batch_id, p_request_id, p_spreadsheet_type, x_return_status, x_return_code);
7084
7085 END validate_spreadsheet_upload;
7086
7087 FUNCTION GET_VENDOR_SITE_CODE(p_vendor_site_id IN NUMBER) RETURN VARCHAR2 IS
7088 l_vendor_site_code PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
7089 BEGIN
7090 IF (p_vendor_site_id = -1) THEN
7091 RETURN '';
7092 ELSE
7093 BEGIN
7094 SELECT vendor_site_code
7095 INTO l_vendor_site_code
7096 FROM PO_VENDOR_SITES_ALL
7097 WHERE vendor_site_id = p_vendor_site_id;
7098 EXCEPTION
7099 WHEN NO_DATA_FOUND THEN
7100 l_vendor_site_code := '';
7101 END;
7102 END IF;
7103
7104 return l_vendor_site_code;
7105
7106 END GET_VENDOR_SITE_CODE;
7107
7108 END PON_BID_VALIDATIONS_PKG;