[Home] [Help]
PACKAGE BODY: APPS.PON_VALIDATE_ITEM_PRICES_INT
Source
1 PACKAGE BODY pon_validate_item_prices_int as
2 -- $Header: PONVAIPB.pls 120.31.12010000.3 2008/10/29 10:16:14 anagoel ship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_VALIDATE_ITEM_PRICES_INT.';
5
6 PROCEDURE validateAwardBid(p_batch_Id IN NUMBER,
7 p_spreadsheet_type IN VARCHAR2);
8
9 PROCEDURE validateAwardBidXML(p_batch_id NUMBER,
10 x_return_status OUT NOCOPY NUMBER,
11 x_return_code OUT NOCOPY VARCHAR2) IS
12
13 l_has_errors VARCHAR2(1);
14 l_num_of_award_lines NUMBER;
15
16 BEGIN
17
18 select count(*)
19 into l_num_of_award_lines
20 from pon_award_items_interface
21 where batch_id = p_batch_id
22 and rownum = 1;
23
24 IF (l_num_of_award_lines = 0) THEN
25
26 x_return_status := 0;
27 x_return_code := 'SUCCESS';
28
29 RETURN;
30
31 END IF;
32
33 validateAwardBid(p_batch_id, PON_AWARD_PKG.g_xml_upload_mode);
34
35 -- Check if any errors were present
36 SELECT decode(count(interface_type), 0, 'N', 'Y')
37 INTO l_has_errors
38 FROM pon_interface_errors
39 WHERE batch_id = p_batch_id
40 AND rownum = 1;
41
42 IF (l_has_errors = 'Y') THEN
43 x_return_status := 1;
44 x_return_code := 'ERRORS';
45 ELSE
46 x_return_status := 0;
47 x_return_code := 'SUCCESS';
48 END IF;
49
50
51 END validateAwardBidXML;
52
53
54 PROCEDURE validate_bids (p_source VARCHAR2, p_batch_Id NUMBER, p_trading_partner_id number) IS
55 --
56 BEGIN
57 null;
58 END validate_bids;
59
60 --
61
62 Function getDoctypeMessageSuffix(p_auction_id number) return varchar2 is
63 l_suffix varchar2(2);
64 begin
65
66 SELECT '_' || dt.message_suffix
67 INTO l_suffix
68 FROM pon_auc_doctypes dt,
69 pon_auction_headers_all ah
70 WHERE dt.doctype_id = ah.doctype_id
71 AND ah.auction_header_id = p_auction_id
72 AND rownum =1;
73
74 return l_suffix;
75
76 end getDoctypeMessageSuffix;
77
78
79
80 FUNCTION is_valid_rule( p_doctype_Id NUMBER
81 , p_bizrule_name VARCHAR2
82 )
83 RETURN BOOLEAN IS
84 --
85 l_valid_flag pon_auc_doctype_rules.VALIDITY_FLAG%TYPE;
86 --
87 BEGIN
88 --
89 SELECT pon_auc_doctype_rules.VALIDITY_FLAG
90 INTO l_valid_flag
91 FROM
92 PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
93 , PON_AUC_BIZRULES pon_auc_bizrules
94 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
95 AND pon_auc_doctype_rules.DOCTYPE_ID = p_doctype_Id
96 AND pon_auc_bizrules.NAME = p_bizrule_name;
97 --
98 IF (l_valid_flag = 'Y') THEN
99 RETURN TRUE;
100 END IF;
101 --
102 RETURN FALSE;
103 --
104 END is_valid_rule;
105 --
106 --
107 FUNCTION is_required_rule( p_doctype_Id NUMBER
108 , p_bizrule_name VARCHAR2
109 )
110 RETURN BOOLEAN IS
111 --
112 l_required_flag pon_auc_doctype_rules.REQUIRED_FLAG%TYPE;
113 --
114 BEGIN
115 --
116 SELECT pon_auc_doctype_rules.REQUIRED_FLAG
117 INTO l_required_flag
118 FROM
119 PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
120 , PON_AUC_BIZRULES pon_auc_bizrules
121 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
122 AND pon_auc_doctype_rules.DOCTYPE_ID = p_doctype_Id
123 AND pon_auc_bizrules.NAME = p_bizrule_name;
124 --
125 IF (l_required_flag = 'Y') THEN
126 RETURN TRUE;
127 END IF;
128 --
129 RETURN FALSE;
130 --
131 END is_required_rule;
132 --
133 --
134
135
136 PROCEDURE get_default_uom(p_language VARCHAR2
137 ,p_trading_partner_id NUMBER
138 ,p_amount_based_uom OUT NOCOPY VARCHAR2
139 ,p_amount_based_unit_of_measure OUT NOCOPY VARCHAR2
140 ) AS
141 BEGIN
142 SELECT
143 m.uom_code, m.unit_of_measure_tl
144 INTO
145 p_amount_based_uom,
146 p_amount_based_unit_of_measure
147 FROM
148 mtl_units_of_measure_tl m
149 ,pon_party_preferences p
150 WHERE
151 p.PARTY_ID = p_trading_partner_id
152 and PREFERENCE_NAME = 'AMOUNT_BASED_UOM'
153 and m.language = p_language
154 and m.uom_code = p.PREFERENCE_VALUE;
155
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 p_amount_based_uom := '';
160 p_amount_based_unit_of_measure := '';
161 END get_default_uom;
162 --
163 --
164 PROCEDURE get_inventory_org_id(p_org_id NUMBER
165 ,p_inventory_org OUT NOCOPY NUMBER) AS
166
167 BEGIN
168
169 SELECT inventory_organization_id
170 INTO p_inventory_org
171 FROM financials_system_params_all
172 WHERE nvl(org_id, -9999) = nvl(p_org_id, -9999);
173
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 p_inventory_org := p_org_id;
178
179 END get_inventory_org_id;
180 --
181
182 PROCEDURE validateAwardBid(p_batch_Id IN NUMBER,
183 p_spreadsheet_type IN VARCHAR2) IS
184 --
185 l_auction_id PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE;
186 l_request_id PON_AUCTION_HEADERS_ALL.request_id%TYPE;
187 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
188 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.price_tiers_indicator%TYPE;
189 l_suffix VARCHAR2(3);
190 l_user_id NUMBER;
191 l_login_id NUMBER;
192 l_exp_date DATE;
193 l_num_of_award_lines NUMBER;
194
195 BEGIN
196
197 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN --{
198 FND_LOG.string(log_level => FND_LOG.level_statement,
199 module => g_module_prefix || 'validateAwardBid',
200 message => 'Entering the procedure for batch id = ' || p_batch_id || ' ; p_spreadsheet_type = '|| p_spreadsheet_type);
201 END IF; --}
202
203 select count(*)
204 into l_num_of_award_lines
205 from pon_award_items_interface
206 where batch_id = p_batch_id
207 and rownum = 1;
208
209 IF (l_num_of_award_lines = 0) THEN
210
211 RETURN;
212
213 END IF;
214
215 l_user_id := fnd_global.user_id;
216 l_login_id := fnd_global.login_id;
217 l_exp_date := SYSDATE+7;
218
219 select paha.auction_header_id , paha.request_id, paha.contract_type, paha.price_tiers_indicator
220 into l_auction_id, l_request_id, l_contract_type, l_price_tiers_indicator
221 from pon_auction_headers_all paha,
222 pon_award_items_interface paii
223 where paii.auction_header_id = paha.auction_header_id
224 and paii.batch_id = p_batch_id
225 and rownum = 1;
226
227 l_suffix := getDoctypeMessageSuffix(l_auction_id);
228
229 --
230 -- For Quantity Tiers
231 -- Setting the award_shipment_number = -1 by default.
232 -- Then updating it to the shipment number as per the award quantity
233 --
234
235 IF (p_spreadsheet_type = g_txt_upload_mode) THEN --{
236
237 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN --{
238 FND_LOG.string(log_level => FND_LOG.level_statement,
239 module => g_module_prefix || 'validateAwardBid',
240 message => 'updating the award shipment number PON_AWARD_ITEMS_INTERFACE for batch id = ' || p_batch_id || ' ; p_spreadsheet_type = '|| p_spreadsheet_type);
241 END IF; --}
242
243 UPDATE PON_AWARD_ITEMS_INTERFACE PAII
244 set AWARD_SHIPMENT_NUMBER = ( select -1
245 from pon_bid_item_prices pbip
246 where pbip.bid_number = PAII.bid_number
247 and pbip.auction_line_number = PAII.auction_line_number
248 and pbip.has_quantity_tiers = 'Y'
249 )
250 WHERE PAII.batch_id = p_batch_id
251 AND PAII.award_status = 'Y';
252
253 UPDATE PON_AWARD_ITEMS_INTERFACE PAII
254 set AWARD_SHIPMENT_NUMBER = (select nvl(( select shipment_number
255 from pon_bid_shipments pbs
256 where pbs.bid_number = PAII.bid_number
257 and pbs.auction_line_number = PAII.AUCTION_LINE_NUMBER
258 and PAII.award_quantity >= pbs.quantity
259 and PAII.award_quantity <= pbs.max_quantity ),-1)
260 from dual)
261 WHERE PAII.batch_id = p_batch_id
262 AND PAII.award_status = 'Y'
263 AND PAII.AWARD_SHIPMENT_NUMBER = -1;
264
265 END IF;--}
266
267
268 INSERT ALL
269 -- VALIDATION #1:
270 -- Check that the bid number is valid for this auction and this line number
271 WHEN NOT EXISTS (SELECT 'Y'
272 FROM pon_bid_item_prices bp
273 WHERE s_auction_header_id = bp.auction_header_id
274 AND s_auction_line_number = bp.line_number
275 AND s_bid_number = bp.bid_number)
276 THEN
277 INTO PON_INTERFACE_ERRORS
278 ( interface_type
279 , column_name
280 , error_message_name
281 , error_value
282 , table_name
283 , batch_id
284 , worksheet_name
285 , worksheet_sequence_number
286 , entity_message_code
287 , interface_line_id
288 , request_id
289 , expiration_date
290 , created_by
291 , creation_date
292 , last_updated_by
293 , last_update_date
294 , last_update_login
295 )
296 VALUES ( 'AWARDBID'
297 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
298 , 'PON_AUC_BID_NUMBER_INVALID' || l_suffix
299 , s_bid_number
300 , 'PON_AWARD_ITEMS_INTERFACE'
301 , s_batch_id
302 , s_worksheet_name
303 , s_worksheet_sequence_number
304 , s_entity_message_code
305 , s_interface_line_id
306 , l_request_id
307 , l_exp_date
308 , l_user_id
309 , sysdate
310 , l_user_id
311 , sysdate
312 , l_login_id
313 )
314 -- VALIDATION #2:
315 -- Check that there is no award decision made on this auction and this line number
316 WHEN (s_bid_line_award_status = 'Y' OR
317 s_award_quantity > 0 OR
318 s_awardreject_reason is not null)
319 AND s_line_award_status = 'COMPLETED'
320 THEN
321 INTO PON_INTERFACE_ERRORS
322 ( interface_type
323 , column_name
324 , error_message_name
325 , table_name
326 , batch_id
327 , worksheet_name
328 , worksheet_sequence_number
329 , entity_message_code
330 , interface_line_id
331 , request_id
332 , expiration_date
333 , created_by
334 , creation_date
335 , last_updated_by
336 , last_update_date
337 , last_update_login
338 )
339 VALUES ( 'AWARDBID'
340 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
341 , 'PON_AUC_ITEM_AWARDED'
342 , 'PON_AWARD_ITEMS_INTERFACE'
343 , s_batch_id
344 , s_worksheet_name
345 , s_worksheet_sequence_number
346 , s_entity_message_code
347 , s_interface_line_id
348 , l_request_id
349 , l_exp_date
350 , l_user_id
351 , sysdate
352 , l_user_id
353 , sysdate
354 , l_login_id
355 )
356 -- VALIDATION #3
357 -- Check if any award reco made for NOT shortlisted bids
358 WHEN (s_bid_line_award_status = 'Y' OR
359 s_award_quantity > 0 OR
360 s_awardreject_reason is not null)
361 AND s_shortlist_flag = 'N'
362 THEN
363 INTO PON_INTERFACE_ERRORS
364 ( interface_type
365 , column_name
366 , error_message_name
367 , error_value
368 , table_name
369 , batch_id
370 , worksheet_name
371 , worksheet_sequence_number
372 , entity_message_code
373 , interface_line_id
374 , request_id
375 , expiration_date
376 , created_by
377 , creation_date
378 , last_updated_by
379 , last_update_date
380 , last_update_login
381 )
382 VALUES ( 'AWARDBID'
383 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
384 , 'PON_AWARD_EXCLUDE_SHLIST_ERR' || l_suffix
385 , s_bid_number
386 , 'PON_AWARD_ITEMS_INTERFACE'
387 , s_batch_id
388 , s_worksheet_name
389 , s_worksheet_sequence_number
390 , s_entity_message_code
391 , s_interface_line_id
392 , l_request_id
393 , l_exp_date
394 , l_user_id
395 , sysdate
396 , l_user_id
397 , sysdate
398 , l_login_id
399 )
400 -- VALIDATION #4
401 -- Check if bid is active
402 WHEN (s_bid_line_award_status = 'Y' OR
403 s_award_quantity > 0 OR
404 s_awardreject_reason is not null)
405 AND s_bid_status <> 'ACTIVE'
406 THEN
407 INTO PON_INTERFACE_ERRORS
408 ( interface_type
409 , column_name
410 , error_message_name
411 , error_value
412 , table_name
413 , batch_id
414 , worksheet_name
415 , worksheet_sequence_number
416 , entity_message_code
417 , interface_line_id
418 , request_id
419 , expiration_date
420 , created_by
421 , creation_date
422 , last_updated_by
423 , last_update_date
424 , last_update_login
425 )
426 VALUES ( 'AWARDBID'
427 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
428 , 'PON_AWARD_BID_NOT_ACTIVE' || l_suffix
429 , s_bid_number
430 , 'PON_AWARD_ITEMS_INTERFACE'
431 , s_batch_id
432 , s_worksheet_name
433 , s_worksheet_sequence_number
434 , s_entity_message_code
435 , s_interface_line_id
436 , l_request_id
437 , l_exp_date
438 , l_user_id
439 , sysdate
440 , l_user_id
441 , sysdate
442 , l_login_id
443 )
444 -- VALIDATION #5
445 -- Check if supplier is active
446 WHEN (s_bid_line_award_status = 'Y' OR
447 s_award_quantity > 0 OR
448 s_awardreject_reason is not null)
449 AND s_end_date_active is not null and s_end_date_active <= trunc(sysdate)
450 THEN
451 INTO PON_INTERFACE_ERRORS
452 ( interface_type
453 , column_name
454 , error_message_name
455 , error_value
456 , table_name
457 , batch_id
458 , worksheet_name
459 , worksheet_sequence_number
460 , entity_message_code
461 , interface_line_id
462 , request_id
463 , expiration_date
464 , created_by
465 , creation_date
466 , last_updated_by
467 , last_update_date
468 , last_update_login
469 )
470 VALUES ( 'AWARDBID'
471 , decode(p_spreadsheet_type, PON_AWARD_PKG.g_xml_upload_mode, fnd_message.get_string('PON','PON_ACCTS_SUPPLIER'), fnd_message.get_string('PON','PON_BIDS_BIDDER' || l_suffix))
472 , 'PON_AWARD_INACTIVE_SUPPLIER'
473 , s_trading_partner_name
474 , 'PON_AWARD_ITEMS_INTERFACE'
475 , s_batch_id
476 , s_worksheet_name
477 , s_worksheet_sequence_number
478 , s_entity_message_code
479 , s_interface_line_id
480 , l_request_id
481 , l_exp_date
482 , l_user_id
483 , sysdate
484 , l_user_id
485 , sysdate
486 , l_login_id
487 )
488 -- VALIDATION #6
489 -- Check if award quantity is > 0 when uploading an XML file
490 WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
491 AND s_award_quantity < 0
492 THEN
493 INTO PON_INTERFACE_ERRORS
494 ( interface_type
495 , column_name
496 , error_message_name
497 , error_value_number
498 , error_value_datatype
499 , table_name
500 , batch_id
501 , worksheet_name
502 , worksheet_sequence_number
503 , entity_message_code
504 , interface_line_id
505 , request_id
506 , expiration_date
507 , created_by
508 , creation_date
509 , last_updated_by
510 , last_update_date
511 , last_update_login
512 )
513 VALUES ( 'AWARDBID'
514 , decode(l_contract_type, 'STANDARD', fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY'), fnd_message.get_string('PON','PON_AUCTS_AGREED_QUANTITY'))
515 , decode(l_contract_type, 'STANDARD', 'PON_AUC_AWARD_QTY_COL_NEG', 'PON_AUC_QTY_AGREED_COL_NEG')
516 , s_award_quantity
517 , 'NUM'
518 , 'PON_AWARD_ITEMS_INTERFACE'
519 , s_batch_id
520 , s_worksheet_name
521 , s_worksheet_sequence_number
522 , s_entity_message_code
523 , s_interface_line_id
524 , l_request_id
525 , l_exp_date
526 , l_user_id
527 , sysdate
528 , l_user_id
529 , sysdate
530 , l_login_id
531 )
532 -- VALIDATION #7
533 -- Check if award quantity is entered when document is of type SPO, award status is Y and
534 -- an XML file is being uploaded
535 WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
536 AND l_contract_type = 'STANDARD'
537 AND s_order_type_lookup_code = 'QUANTITY'
538 AND s_bid_line_award_status = 'Y'
539 AND s_award_quantity is null
540 THEN
541 INTO PON_INTERFACE_ERRORS
542 ( interface_type
543 , column_name
544 , error_message_name
545 , table_name
546 , batch_id
547 , worksheet_name
548 , worksheet_sequence_number
549 , entity_message_code
550 , interface_line_id
551 , request_id
552 , expiration_date
553 , created_by
554 , creation_date
555 , last_updated_by
556 , last_update_date
557 , last_update_login
558 )
559 VALUES ( 'AWARDBID'
560 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
561 , 'PON_AUCTS_MUST_AWARD'
562 , 'PON_AWARD_ITEMS_INTERFACE'
563 , s_batch_id
564 , s_worksheet_name
565 , s_worksheet_sequence_number
566 , s_entity_message_code
567 , s_interface_line_id
568 , l_request_id
569 , l_exp_date
570 , l_user_id
571 , sysdate
572 , l_user_id
573 , sysdate
574 , l_login_id
575 )
576
577 -- VALIDATION #8
578 -- Awarded Qty should fall within Qty tiers provided on the bid, by supplier
579 -- for tab-delimited spreasheet
580
581 WHEN l_price_tiers_indicator = 'QUANTITY_BASED'
582 AND s_bid_line_award_status = 'Y'
583 AND s_award_shipment_number = -1
584 AND p_spreadsheet_type = g_txt_upload_mode
585 AND nvl(s_award_quantity , 0) > 0
586 THEN
587 INTO PON_INTERFACE_ERRORS
588 ( interface_type
589 , column_name
590 , error_message_name
591 , table_name
592 , error_value
593 , batch_id
594 , worksheet_name
595 , worksheet_sequence_number
596 , entity_message_code
597 , interface_line_id
598 , request_id
599 , expiration_date
600 , created_by
601 , creation_date
602 , last_updated_by
603 , last_update_date
604 , last_update_login
605 , TOKEN1_NAME
606 , TOKEN1_VALUE
607 )
608 VALUES ( 'AWARDBID'
609 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
610 , 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
611 , 'PON_AWARD_ITEMS_INTERFACE'
612 , s_award_quantity
613 , s_batch_id
614 , s_worksheet_name
615 , s_worksheet_sequence_number
616 , s_entity_message_code
617 , s_interface_line_id
618 , l_request_id
619 , l_exp_date
620 , l_user_id
621 , sysdate
622 , l_user_id
623 , sysdate
624 , l_login_id
625 , 'BID_NUM'
626 , s_bid_number
627 )
628
629 SELECT
630 ap.batch_id AS s_batch_id
631 , ap.auction_header_id AS s_auction_header_id
632 , ap.bid_number AS s_bid_number
633 , ap.auction_line_number AS s_auction_line_number
634 , ap.worksheet_name AS s_worksheet_name
635 , ap.worksheet_sequence_number AS s_worksheet_sequence_number
636 , 'PON_AUC_ITEMS' AS s_entity_message_code
637 , ap.interface_line_id AS s_interface_line_id
638 , ap.award_status AS s_bid_line_award_status
639 , ap.award_quantity AS s_award_quantity
640 , ap.awardreject_reason AS s_awardreject_reason
641 , ai.award_status AS s_line_award_status
642 , ai.order_type_lookup_code AS s_order_type_lookup_code
643 , bh.trading_partner_name AS s_trading_partner_name
644 , bh.shortlist_flag AS s_shortlist_flag
645 , bh.bid_status AS s_bid_status
646 , pv.end_date_active AS s_end_date_active
647 , ap.award_shipment_number as s_award_shipment_number
648 FROM pon_award_items_interface ap
649 , pon_auction_item_prices_all ai
650 , pon_bid_headers bh
651 , po_vendors pv
652 WHERE ap.batch_id = p_batch_id
653 AND ap.auction_header_id = ai.auction_header_id
654 AND ap.auction_line_number = ai.line_number
655 AND ap.bid_number = bh.bid_number (+)
656 AND bh.vendor_id = pv.vendor_id (+);
657
658
659 --
660 INSERT INTO PON_INTERFACE_ERRORS
661 ( interface_type
662 , column_name
663 , error_message_name
664 , error_value
665 , table_name
666 , batch_id
667 , worksheet_name
668 , worksheet_sequence_number
669 , entity_message_code
670 , interface_line_id
671 , request_id
672 , expiration_date
673 , created_by
674 , creation_date
675 , last_updated_by
676 , last_update_date
677 , last_update_login
678 , TOKEN1_NAME
679 , TOKEN1_VALUE
680 )
681 SELECT
682 'AWARDBID'
683 , fnd_message.get_string('PON','PON_AUC_LINE_TYPE')
684 , 'PON_AWARD_FIXED_PRICE'|| l_suffix
685 , pltt.line_type
686 , 'PON_AWARD_ITEMS_INTERFACE'
687 , paii.BATCH_ID
688 , paii.worksheet_name
689 , paii.worksheet_sequence_number
690 , 'PON_AUC_ITEMS' entity_message_code
691 , to_number(null) interface_line_id
692 , l_request_id
693 , l_exp_date
694 , l_user_id
695 , sysdate
696 , l_user_id
697 , sysdate
698 , l_login_id
699 , 'LINE_NUMBER'
700 , ai.document_disp_line_number
701 FROM PON_AWARD_ITEMS_INTERFACE paii,
702 pon_auction_item_prices_all ai,
703 pon_auction_headers_all ah,
704 po_line_types_tl pltt
705 WHERE paii.batch_id = p_batch_id
706 AND paii.award_status = 'Y'
707 AND ah.auction_header_id = paii.auction_header_id
708 AND ah.contract_type = 'STANDARD'
709 AND ai.line_number = paii.auction_line_number
710 AND ai.auction_header_id = paii.auction_header_id
711 AND ai.order_type_lookup_code = 'FIXED PRICE'
712 AND ai.line_type_id = pltt.line_type_id (+)
713 AND pltt.language (+) = userenv('LANG')
714 GROUP BY paii.batch_id,
715 paii.worksheet_name,
716 paii.worksheet_sequence_number,
717 ai.document_disp_line_number,
718 pltt.line_type
719 HAVING count(paii.award_status) >1;
720
721
722 END validateAwardBid;
723 --
724
725 PROCEDURE validate_complexwork(p_batch_id IN NUMBER
726 ,p_progress_payment_type IN VARCHAR2
727 ,p_contract_type IN VARCHAR2
728 ,p_advance_negotiable_flag IN VARCHAR2
729 ,p_recoupment_negotiable_flag IN VARCHAR2
730 )
731 IS
732 l_userid NUMBER;
733 l_loginid NUMBER;
734 l_exp_date DATE;
735 CURSOR l_proj_cursor IS
736 SELECT pipi.interface_line_id, pipi.document_disp_line_number, pipi.auction_line_number,
737 pipi.project_id, pipi.project_task_id, pipi.project_expenditure_type,
738 pipi.project_exp_organization_id, pipi.project_expenditure_item_date,
739 pipi.auction_header_id, pipi.interface_type
740 FROM PON_ITEM_PRICES_INTERFACE pipi
741 WHERE pipi.batch_id=p_batch_id
742 AND pipi.project_id IS NOT NULL
743 AND pipi.project_task_id IS NOT NULL
744 AND pipi.project_expenditure_type IS NOT NULL
745 AND pipi.project_exp_organization_id IS NOT NULL
746 AND pipi.project_expenditure_item_date IS NOT NULL;
747
748 BEGIN
749 l_userid := fnd_global.user_id;
750 l_loginid := fnd_global.login_id;
751 l_exp_date := SYSDATE+7;
752
753
754 IF p_contract_type = 'CONTRACT' THEN
755 INSERT ALL
756 WHEN (p_progress_payment_type <> 'NONE' AND
757 line_type_id IS NOT NULL AND
758 NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
759 (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
760 OR po_outside_operation_flag = 'Y' THEN
761 INTO pon_interface_errors
762 (
763 interface_type, column_name, table_name, -- 1
764 error_value, error_message_name, batch_id, -- 2
765 interface_line_id, auction_header_id, line_number, -- 3
766 token1_name, token1_value, expiration_date, -- 4
767 created_by, creation_date, last_updated_by, -- 5
768 last_update_date, last_update_login -- 6
769 )
770 VALUES
771 (
772 interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
773 line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
774 interface_line_id, auction_header_id, s_line_number, -- 3
775 NULL, NULL, l_exp_date, -- 4
776 l_userid, SYSDATE, l_userid, -- 5
777 SYSDATE, l_loginid -- 6
778 )
779 SELECT
780 pipi.BATCH_ID,
781 pipi.INTERFACE_LINE_ID,
782 pipi.INTERFACE_TYPE,
783 pipi.AUCTION_HEADER_ID,
784 pipi.DOCUMENT_DISP_LINE_NUMBER,
785 pipi.PURCHASE_BASIS,
786 pipi.ORDER_TYPE_LOOKUP_CODE,
787 pipi.auction_line_number s_line_number,
788 plt.outside_operation_flag po_outside_operation_flag,
789 plt.line_type_id,
790 plt.line_type
791 FROM PON_ITEM_PRICES_INTERFACE pipi,
792 PO_LINE_TYPES plt
793 WHERE batch_id = p_batch_id
794 AND pipi.line_type_id = plt.line_type_id (+)
795 AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
796
797 ELSIF p_contract_type = 'STANDARD' THEN
798
799 INSERT ALL
800 WHEN retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
801 INTO pon_interface_errors
802 (
803 interface_type, column_name, table_name, -- 1
804 error_value, error_message_name, batch_id, -- 2
805 interface_line_id, auction_header_id, line_number, -- 3
806 token1_name, token1_value, expiration_date, -- 4
807 created_by, creation_date, last_updated_by, -- 5
808 last_update_date, last_update_login -- 6
809 )
810 VALUES
811 (
812 interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
813 retainage_rate_percent, 'PON_RTNG_RATE_WRONG', batch_id, -- 2
814 interface_line_id, auction_header_id, s_line_number, -- 3
815 NULL, NULL, l_exp_date, -- 4
816 l_userid, SYSDATE, l_userid, -- 5
817 SYSDATE, l_loginid -- 6
818 )
819
820 WHEN max_retainage_amount IS NOT NULL AND max_retainage_amount < 0 THEN
821 INTO pon_interface_errors
822 (
823 interface_type, column_name, table_name, -- 1
824 error_value, error_message_name, batch_id, -- 2
825 interface_line_id, auction_header_id, line_number, -- 3
826 token1_name, token1_value, expiration_date, -- 4
827 created_by, creation_date, last_updated_by, -- 5
828 last_update_date, last_update_login -- 6
829 )
830 VALUES
831 (
832 interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
833 max_retainage_amount , 'PON_MAX_RTNG_WRONG', batch_id, -- 2
834 interface_line_id, auction_header_id, s_line_number, -- 3
835 NULL, NULL, l_exp_date, -- 4
836 l_userid, SYSDATE, l_userid, -- 5
837 SYSDATE, l_loginid -- 6
838 )
839
840 WHEN advance_amount IS NOT NULL AND advance_amount < 0 THEN
841 INTO pon_interface_errors
842 (
843 interface_type, column_name, table_name, -- 1
844 error_value, error_message_name, batch_id, -- 2
845 interface_line_id, auction_header_id, line_number, -- 3
846 token1_name, token1_value, expiration_date, -- 4
847 created_by, creation_date, last_updated_by, -- 5
848 last_update_date, last_update_login -- 6
849 )
850 VALUES
851 (
852 interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
853 advance_amount, 'PON_ADV_AMT_WRONG', batch_id, -- 2
854 interface_line_id, auction_header_id, s_line_number, -- 3
855 NULL, NULL, l_exp_date, -- 4
856 l_userid, SYSDATE, l_userid, -- 5
857 SYSDATE, l_loginid -- 6
858 )
859
860 WHEN progress_pymt_rate_percent IS NOT NULL AND (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) then
861 INTO pon_interface_errors
862 (
863 interface_type, column_name, table_name, -- 1
864 error_value, error_message_name, batch_id, -- 2
865 interface_line_id, auction_header_id, line_number, -- 3
866 token1_name, token1_value, expiration_date, -- 4
867 created_by, creation_date, last_updated_by, -- 5
868 last_update_date, last_update_login -- 6
869 )
870 VALUES
871 (
872 interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
873 progress_pymt_rate_percent, 'PON_PROG_PYMT_RATE_WRONG', batch_id, -- 2
874 interface_line_id, auction_header_id, s_line_number, -- 3
875 NULL, NULL, l_exp_date, -- 4
876 l_userid, SYSDATE, l_userid, -- 5
877 SYSDATE, l_loginid -- 6
878 )
879 WHEN recoupment_rate_percent IS NOT NULL AND (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
880 INTO pon_interface_errors
881 (
882 interface_type, column_name, table_name, -- 1
883 error_value, error_message_name, batch_id, -- 2
884 interface_line_id, auction_header_id, line_number, -- 3
885 token1_name, token1_value, expiration_date, -- 4
886 created_by, creation_date, last_updated_by, -- 5
887 last_update_date, last_update_login -- 6
888 )
889 VALUES
890 (
891 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
892 recoupment_rate_percent, 'PON_RECOUP_RATE_WRONG', batch_id, -- 2
893 interface_line_id, auction_header_id, s_line_number, -- 3
894 NULL, NULL, l_exp_date, -- 4
895 l_userid, SYSDATE, l_userid, -- 5
896 SYSDATE, l_loginid -- 6
897 )
898
899 WHEN p_progress_payment_type = 'FINANCE' AND progress_pymt_rate_percent IS NULL THEN
900 INTO pon_interface_errors
901 (
902 interface_type, column_name, table_name, -- 1
903 error_value, error_message_name, batch_id, -- 2
904 interface_line_id, auction_header_id, line_number, -- 3
905 token1_name, token1_value, expiration_date, -- 4
906 created_by, creation_date, last_updated_by, -- 5
907 last_update_date, last_update_login -- 6
908 )
909 VALUES
910 (
911 interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
912 progress_pymt_rate_percent, 'PON_FIELD_MUST_BE_ENTERED', batch_id, -- 2
913 interface_line_id, auction_header_id, s_line_number, -- 3
914 NULL, NULL, l_exp_date, -- 4
915 l_userid, SYSDATE, l_userid, -- 5
916 SYSDATE, l_loginid -- 6
917 )
918
919
920 WHEN progress_pymt_rate_percent IS NOT NULL AND
921 recoupment_rate_percent IS NULL AND
922 p_recoupment_negotiable_flag = 'N' THEN
923 INTO pon_interface_errors
924 (
925 interface_type, column_name, table_name, -- 1
926 error_value, error_message_name, batch_id, -- 2
927 interface_line_id, auction_header_id, line_number, -- 3
928 token1_name, token1_value, expiration_date, -- 4
929 created_by, creation_date, last_updated_by, -- 5
930 last_update_date, last_update_login -- 6
931 )
932 VALUES
933 (
934 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
935 recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_PPRATE', batch_id, -- 2
936 interface_line_id, auction_header_id, s_line_number, -- 3
937 NULL, NULL, l_exp_date, -- 4
938 l_userid, SYSDATE, l_userid, -- 5
939 SYSDATE, l_loginid -- 6
940 )
941
942 WHEN ((advance_amount IS NOT NULL OR p_advance_negotiable_flag = 'Y') AND
943 (recoupment_rate_percent IS NULL AND p_recoupment_negotiable_flag = 'N')) THEN
944 INTO pon_interface_errors
945 (
946 interface_type, column_name, table_name, -- 1
947 error_value, error_message_name, batch_id, -- 2
948 interface_line_id, auction_header_id, line_number, -- 3
949 token1_name, token1_value, expiration_date, -- 4
950 created_by, creation_date, last_updated_by, -- 5
951 last_update_date, last_update_login -- 6
952 )
953 VALUES
954 (
955 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
956 recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_ADVAMT', batch_id, -- 2
957 interface_line_id, auction_header_id, s_line_number, -- 3
958 NULL, NULL, l_exp_date, -- 4
959 l_userid, SYSDATE, l_userid, -- 5
960 SYSDATE, l_loginid -- 6
961 )
962
963 WHEN target_price IS NOT NULL AND advance_amount IS NOT NULL
964 AND (advance_amount > nvl(s_quantity,1) * target_price) THEN
965 INTO pon_interface_errors
966 (
967 interface_type, column_name, table_name, -- 1
968 error_value, error_message_name, batch_id, -- 2
969 interface_line_id, auction_header_id, line_number, -- 3
970 token1_name, token1_value, expiration_date, -- 4
971 created_by, creation_date, last_updated_by, -- 5
972 last_update_date, last_update_login -- 6
973 )
974 VALUES
975 (
976 interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
977 advance_amount, 'PON_ADV_AMT_MORE', batch_id, -- 2
978 interface_line_id, auction_header_id, s_line_number, -- 3
979 NULL, NULL, l_exp_date, -- 4
980 l_userid, SYSDATE, l_userid, -- 5
981 SYSDATE, l_loginid -- 6
982 )
983
984 WHEN p_progress_payment_type <> 'NONE' AND recoupment_rate_percent IS NOT NULL
985 AND advance_amount IS NOT NULL AND target_price IS NOT NULL
986 AND (recoupment_rate_percent < (advance_amount * 100)/(nvl(s_quantity,1) * target_price)) THEN
987 INTO pon_interface_errors
988 (
989 interface_type, column_name, table_name, -- 1
990 error_value, error_message_name, batch_id, -- 2
991 interface_line_id, auction_header_id, line_number, -- 3
992 token1_name, token1_value, expiration_date, -- 4
993 created_by, creation_date, last_updated_by, -- 5
994 last_update_date, last_update_login -- 6
995 )
996 VALUES
997 (
998 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
999 recoupment_rate_percent, 'PON_RECOUP_LESS_THAN_ADV', batch_id, -- 2
1000 interface_line_id, auction_header_id, s_line_number, -- 3
1001 NULL, NULL, l_exp_date, -- 4
1002 l_userid, SYSDATE, l_userid, -- 5
1003 SYSDATE, l_loginid -- 6
1004 )
1005
1006 WHEN (p_progress_payment_type <> 'NONE' AND
1007 line_type_id IS NOT NULL AND
1008 NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
1009 (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
1010 OR po_outside_operation_flag = 'Y' THEN
1011 INTO pon_interface_errors
1012 (
1013 interface_type, column_name, table_name, -- 1
1014 error_value, error_message_name, batch_id, -- 2
1015 interface_line_id, auction_header_id, line_number, -- 3
1016 token1_name, token1_value, expiration_date, -- 4
1017 created_by, creation_date, last_updated_by, -- 5
1018 last_update_date, last_update_login -- 6
1019 )
1020 VALUES
1021 (
1022 interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1023 line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
1024 interface_line_id, auction_header_id, s_line_number, -- 3
1025 NULL, NULL, l_exp_date, -- 4
1026 l_userid, SYSDATE, l_userid, -- 5
1027 SYSDATE, l_loginid -- 6
1028 )
1029
1030 WHEN line_origination_code <> 'REQUISITION' AND project_number IS NOT NULL AND pro_project_id IS NULL THEN
1031 INTO pon_interface_errors
1032 (
1033 interface_type, column_name, table_name, -- 1
1034 error_value, error_message_name, batch_id, -- 2
1035 interface_line_id, auction_header_id, line_number, -- 3
1036 token1_name, token1_value, expiration_date, -- 4
1037 created_by, creation_date, last_updated_by, -- 5
1038 last_update_date, last_update_login -- 6
1039 )
1040 VALUES
1041 (
1042 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1043 project_number, 'PON_PROJ_NUM_INVALID', batch_id, -- 2
1044 interface_line_id, auction_header_id, s_line_number, -- 3
1045 NULL, NULL, l_exp_date, -- 4
1046 l_userid, SYSDATE, l_userid, -- 5
1047 SYSDATE, l_loginid -- 6
1048 )
1049
1050 WHEN line_origination_code <> 'REQUISITION'
1051 AND pro_project_id IS NOT NULL
1052 AND project_task_number IS NOT NULL
1053 AND NOT EXISTS (SELECT 1
1054 FROM PA_TASKS_EXPEND_V task
1055 WHERE task.project_id = pro_project_id AND task.task_number = project_task_number) THEN
1056 INTO pon_interface_errors
1057 (
1058 interface_type, column_name, table_name, -- 1
1059 error_value, error_message_name, batch_id, -- 2
1060 interface_line_id, auction_header_id, line_number, -- 3
1061 token1_name, token1_value, expiration_date, -- 4
1062 created_by, creation_date, last_updated_by, -- 5
1063 last_update_date, last_update_login -- 6
1064 )
1065 VALUES
1066 (
1067 interface_type, fnd_message.get_string('PON','PON_AUCTS_TASK'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1068 project_task_number, 'PON_PROJ_TASK_INVALID', batch_id, -- 2
1069 interface_line_id, auction_header_id, s_line_number, -- 3
1070 NULL, NULL, l_exp_date, -- 4
1071 l_userid, SYSDATE, l_userid, -- 5
1072 SYSDATE, l_loginid -- 6
1073 )
1074
1075 WHEN line_origination_code <> 'REQUISITION'
1076 AND pro_project_id IS NOT NULL
1077 AND project_task_number IS NOT NULL
1078 AND project_award_number IS NOT NULL
1079 AND NOT EXISTS (SELECT 1
1080 FROM GMS_AWARDS_BASIC_V award,
1081 PA_TASKS_EXPEND_V task
1082 WHERE award.project_id = pro_project_id
1083 AND task.task_number = project_task_number
1084 AND award.task_id = task.task_id
1085 AND task.project_id = pro_project_id) THEN
1086 INTO pon_interface_errors
1087 (
1088 interface_type, column_name, table_name, -- 1
1089 error_value, error_message_name, batch_id, -- 2
1090 interface_line_id, auction_header_id, line_number, -- 3
1091 token1_name, token1_value, expiration_date, -- 4
1092 created_by, creation_date, last_updated_by, -- 5
1093 last_update_date, last_update_login -- 6
1094 )
1095 VALUES
1096 (
1097 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1098 project_award_number, 'PON_PROJ_AWARD_INVALID', batch_id, -- 2
1099 interface_line_id, auction_header_id, s_line_number, -- 3
1100 NULL, NULL, l_exp_date, -- 4
1101 l_userid, SYSDATE, l_userid, -- 5
1102 SYSDATE, l_loginid -- 6
1103 )
1104
1105 WHEN line_origination_code <> 'REQUISITION' AND project_exp_organization_name IS NOT NULL
1106 AND porg_proj_exp_organization_id IS NULL THEN
1107 INTO pon_interface_errors
1108 (
1109 interface_type, column_name, table_name, -- 1
1110 error_value, error_message_name, batch_id, -- 2
1111 interface_line_id, auction_header_id, line_number, -- 3
1112 token1_name, token1_value, expiration_date, -- 4
1113 created_by, creation_date, last_updated_by, -- 5
1114 last_update_date, last_update_login -- 6
1115 )
1116 VALUES
1117 (
1118 interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1119 project_exp_organization_name,'PON_PROJ_EXPORG_INVALID', batch_id, -- 2
1120 interface_line_id, auction_header_id, s_line_number, -- 3
1121 NULL, NULL, l_exp_date, -- 4
1122 l_userid, SYSDATE, l_userid, -- 5
1123 SYSDATE, l_loginid -- 6
1124 )
1125 WHEN s_project_exp_type IS NOT NULL
1126 AND NOT EXISTS (SELECT 1
1127 FROM pa_expenditure_types_expend_v exptype
1128 WHERE system_linkage_function = 'VI'
1129 AND exptype.expenditure_type = s_project_exp_type
1130 AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
1131 AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
1132 AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
1133 AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
1134
1135 INTO pon_interface_errors
1136 (
1137 interface_type, column_name, table_name, -- 1
1138 error_value, error_message_name, batch_id, -- 2
1139 interface_line_id, auction_header_id, line_number, -- 3
1140 token1_name, token1_value, expiration_date, -- 4
1141 created_by, creation_date, last_updated_by, -- 5
1142 last_update_date, last_update_login -- 6
1143 )
1144 VALUES
1145 (
1146 interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1147 s_project_exp_type, 'PON_PROJ_EXPTYPE_INVALID', batch_id, -- 2
1148 interface_line_id, auction_header_id, s_line_number, -- 3
1149 NULL, NULL, l_exp_date, -- 4
1150 l_userid, SYSDATE, l_userid, -- 5
1151 SYSDATE, l_loginid -- 6
1152 )
1153
1154 WHEN pro_project_id IS NOT NULL
1155 AND project_award_number IS NULL
1156 AND PON_NEGOTIATION_PUBLISH_PVT.IS_PROJECT_SPONSORED(pro_project_id) = 'Y' THEN
1157 INTO pon_interface_errors
1158 (
1159 interface_type, column_name, table_name, -- 1
1160 error_value, error_message_name, batch_id, -- 2
1161 interface_line_id, auction_header_id, line_number, -- 3
1162 token1_name, token1_value, expiration_date, -- 4
1163 created_by, creation_date, last_updated_by, -- 5
1164 last_update_date, last_update_login -- 6
1165 )
1166 VALUES
1167 (
1168 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1169 project_award_number, 'PON_PROJ_AWARD_NULL', batch_id, -- 2
1170 interface_line_id, auction_header_id, s_line_number, -- 3
1171 NULL, NULL, l_exp_date, -- 4
1172 l_userid, SYSDATE, l_userid, -- 5
1173 SYSDATE, l_loginid -- 6
1174 )
1175 WHEN line_origination_code <> 'REQUISITION' AND
1176 ((project_number IS NULL OR project_task_number IS NULL OR s_project_exp_type IS NULL
1177 OR project_exp_organization_name IS NULL OR project_expenditure_item_date IS NULL) AND
1178 (project_number IS NOT NULL OR project_task_number IS NOT NULL OR s_project_exp_type IS NOT NULL
1179 OR project_exp_organization_name IS NOT NULL OR project_expenditure_item_date IS NOT NULL)) THEN
1180 INTO pon_interface_errors
1181 (
1182 interface_type, column_name, table_name, -- 1
1183 error_value, error_message_name, batch_id, -- 2
1184 interface_line_id, auction_header_id, line_number, -- 3
1185 token1_name, token1_value, expiration_date, -- 4
1186 created_by, creation_date, last_updated_by, -- 5
1187 last_update_date, last_update_login -- 6
1188 )
1189 VALUES
1190 (
1191 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1192 NULL, 'PON_PROJ_INFO_INCOMPLETE', batch_id, -- 2
1193 interface_line_id, auction_header_id, s_line_number, -- 3
1194 NULL, NULL, l_exp_date, -- 4
1195 l_userid, SYSDATE, l_userid, -- 5
1196 SYSDATE, l_loginid -- 6
1197 )
1198
1199 WHEN work_approver_user_name IS NOT NULL
1200 AND NOT EXISTS (SELECT 1
1201 FROM PER_WORKFORCE_CURRENT_X peo,
1202 FND_USER fu
1203 WHERE fu.user_name = work_approver_user_name
1204 AND fu.employee_id = peo.person_id
1205 AND SYSDATE >= nvl(fu.start_date, SYSDATE)
1206 AND SYSDATE <= nvl(fu.end_date, SYSDATE) )
1207 THEN
1208 INTO pon_interface_errors
1209 (
1210 interface_type, column_name, table_name, -- 1
1211 error_value, error_message_name, batch_id, -- 2
1212 interface_line_id, auction_header_id, line_number, -- 3
1213 token1_name, token1_value, expiration_date, -- 4
1214 created_by, creation_date, last_updated_by, -- 5
1215 last_update_date, last_update_login -- 6
1216 )
1217 VALUES
1218 (
1219 interface_type, fnd_message.get_string('PON','PON_DEFAULT_OWNER'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1220 NULL, 'PON_LIN_OWNER_INVALID', batch_id, -- 2
1221 interface_line_id, auction_header_id, s_line_number, -- 3
1222 NULL, NULL, l_exp_date, -- 4
1223 l_userid, SYSDATE, l_userid, -- 5
1224 SYSDATE, l_loginid -- 6
1225 )
1226 SELECT
1227 pipi.BATCH_ID,
1228 pipi.INTERFACE_LINE_ID,
1229 pipi.INTERFACE_TYPE,
1230 pipi.AUCTION_HEADER_ID,
1231 pipi.DOCUMENT_DISP_LINE_NUMBER,
1232 pipi.ADVANCE_AMOUNT,
1233 pipi.RECOUPMENT_RATE_PERCENT,
1234 pipi.PROGRESS_PYMT_RATE_PERCENT,
1235 pipi.RETAINAGE_RATE_PERCENT,
1236 pipi.MAX_RETAINAGE_AMOUNT,
1237 pipi.WORK_APPROVER_USER_NAME,
1238 pipi.PROJECT_NUMBER,
1239 pipi.PROJECT_TASK_NUMBER,
1240 pipi.PROJECT_AWARD_NUMBER,
1241 pipi.PROJECT_EXPENDITURE_TYPE s_project_exp_type,
1242 pipi.PROJECT_EXP_ORGANIZATION_NAME,
1243 pipi.PROJECT_EXPENDITURE_ITEM_DATE,
1244 pipi.PURCHASE_BASIS,
1245 pipi.ORDER_TYPE_LOOKUP_CODE,
1246 NVL(pipi.LINE_ORIGINATION_CODE,'-9997') LINE_ORIGINATION_CODE,
1247 pipi.auction_line_number s_line_number,
1248 pipi.target_price,
1249 pipi.quantity s_quantity,
1250 pro.project_id pro_project_id,
1251 porg.organization_id porg_proj_exp_organization_id,
1252 plt.outside_operation_flag po_outside_operation_flag,
1253 plt.line_type_id,
1254 plt.line_type
1255 FROM PON_ITEM_PRICES_INTERFACE pipi,
1256 PA_PROJECTS_EXPEND_V pro,
1257 PA_ORGANIZATIONS_EXPEND_V porg,
1258 PO_LINE_TYPES plt
1259 WHERE batch_id = p_batch_id
1260 AND pipi.project_number = pro.project_number (+)
1261 AND pipi.project_exp_organization_name = porg.name(+)
1262 AND pipi.line_type_id = plt.line_type_id (+)
1263 AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
1264
1265 --Derive id columns and update the interface table
1266 UPDATE PON_ITEM_PRICES_INTERFACE pipi1
1267 SET (PROJECT_ID, PROJECT_TASK_ID, PROJECT_AWARD_ID, PROJECT_EXP_ORGANIZATION_ID) =
1268 (SELECT pro.project_id, task.task_id, award.award_id, porg.organization_id
1269 FROM PA_PROJECTS_ALL pro,
1270 PA_TASKS task,
1271 GMS_AWARDS_ALL award,
1272 HR_ALL_ORGANIZATION_UNITS porg,
1273 PON_ITEM_PRICES_INTERFACE pipi
1274 WHERE pipi.project_number = pro.segment1
1275 AND pipi.project_task_number = task.task_number
1276 AND pro.project_id = task.project_id
1277 AND pipi.project_award_number = award.award_number(+)
1278 AND pipi.project_exp_organization_name = porg.name
1279 AND pipi.batch_id = pipi1.batch_id
1280 AND pipi.interface_line_id = pipi1.interface_line_id)
1281 WHERE pipi1.batch_id = p_batch_id;
1282
1283 UPDATE PON_ITEM_PRICES_INTERFACE pipi
1284 SET (WORK_APPROVER_USER_ID) =
1285 (SELECT fu.user_id
1286 FROM FND_USER fu
1287 WHERE pipi.work_approver_user_name = fu.user_name)
1288 WHERE batch_id = p_batch_id;
1289
1290 END IF; -- End of if p_contract_type = 'STANDARD'
1291
1292 --Validate project fields with PATC
1293 FOR l_proj_record IN l_proj_cursor LOOP
1294 PON_NEGOTIATION_PUBLISH_PVT.VALIDATE_PROJECTS_DETAILS (
1295 p_project_id => l_proj_record.project_id,
1296 p_task_id => l_proj_record.project_task_id,
1297 p_expenditure_date => l_proj_record.project_expenditure_item_date,
1298 p_expenditure_type => l_proj_record.project_expenditure_type,
1299 p_expenditure_org => l_proj_record.project_exp_organization_id,
1300 p_person_id => null,
1301 p_auction_header_id => l_proj_record.auction_header_id,
1302 p_line_number => l_proj_record.auction_line_number,
1303 p_document_disp_line_number => l_proj_record.document_disp_line_number,
1304 p_payment_id => null,
1305 p_interface_line_id => l_proj_record.interface_line_id,
1306 p_payment_display_number => null,
1307 p_batch_id => p_batch_id,
1308 p_table_name => 'PON_ITEM_PRICES_INTERFACE',
1309 p_interface_type => l_proj_record.interface_type,
1310 p_entity_type => null,
1311 p_called_from => 'LINES_SP');
1312 END LOOP;
1313
1314
1315 END validate_complexwork;
1316
1317
1318 -- The procedure loads necessary po style settings for validation.
1319 -- Current usage including line type validation in line upload.
1320
1321 -- Params: p_batch_id batch id (used to get auction header id)
1322 -- x_po_style_id po style id for the negotiation
1323 -- x_line_type_restriction the associated po style setting (line type)
1324
1325 PROCEDURE get_po_style_settings (p_batch_id IN NUMBER,
1326 x_po_style_id OUT NOCOPY NUMBER,
1327 x_line_type_restriction OUT NOCOPY VARCHAR2) IS
1328 l_dummy1 VARCHAR2(240);
1329 l_dummy2 VARCHAR2(240);
1330 l_dummy3 VARCHAR2(30);
1331 l_dummy4 VARCHAR2(30);
1332 l_dummy5 VARCHAR2(1);
1333 l_dummy6 VARCHAR2(1);
1334 l_dummy7 VARCHAR2(1);
1335 l_dummy8 VARCHAR2(1);
1336 l_dummy9 VARCHAR2(1);
1337 l_dummy10 VARCHAR2(1);
1338
1339
1340 BEGIN
1341
1342 BEGIN
1343
1344 select ah.po_style_id
1345 into x_po_style_id
1346 from pon_auction_headers_all ah,
1347 pon_item_prices_interface ipi
1348 where ipi.batch_id = p_batch_id
1349 and ipi.auction_header_id = ah.auction_header_id
1350 and rownum = 1;
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 x_po_style_id := NULL;
1355 END;
1356
1357
1358 -- invoke po api to get settings for the po style
1359 IF ( x_po_style_id is not null) THEN -- RFI has no po style, so add the check
1360
1361 BEGIN
1362 PO_DOC_STYLE_GRP.GET_DOCUMENT_STYLE_SETTINGS(
1363 P_API_VERSION => '1.0',
1364 P_STYLE_ID => x_po_style_id,
1365 X_STYLE_NAME => l_dummy1,
1366 X_STYLE_DESCRIPTION => l_dummy2,
1367 X_STYLE_TYPE => l_dummy3,
1368 X_STATUS => l_dummy4,
1369 X_ADVANCES_FLAG => l_dummy5,
1370 X_RETAINAGE_FLAG => l_dummy6,
1371 X_PRICE_BREAKS_FLAG => l_dummy7,
1372 X_PRICE_DIFFERENTIALS_FLAG => l_dummy8,
1373 X_PROGRESS_PAYMENT_FLAG=> l_dummy9,
1374 X_CONTRACT_FINANCING_FLAG=> l_dummy10,
1375 X_LINE_TYPE_ALLOWED => x_line_type_restriction);
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 x_line_type_restriction := 'ALL'; -- no restriction
1380 END;
1381
1382 END IF;
1383
1384 END get_po_style_settings;
1385
1386 --
1387 PROCEDURE validate (p_source IN VARCHAR2,
1388 p_batch_Id IN NUMBER,
1389 p_doctype_Id IN NUMBER,
1390 p_user_Id IN NUMBER,
1391 p_trading_partner_id IN NUMBER,
1392 p_trading_partner_contact_id IN NUMBER,
1393 p_language IN VARCHAR2,
1394 p_contract_type IN VARCHAR2,
1395 p_global_flag IN VARCHAR2,
1396 p_org_id IN NUMBER) IS
1397 --
1398 CURSOR C_ship_to (c_batch_id NUMBER) IS
1399 SELECT
1400 INTERFACE_LINE_ID
1401 , SHIP_TO_LOCATION_ID
1402 FROM PON_ITEM_PRICES_INTERFACE
1403 WHERE BATCH_ID = c_batch_id
1404 AND SHIP_TO_LOCATION_ID <> -1
1405 AND SHIP_TO_LOCATION <> 'SHIP_NONE_ENTERED';
1406 --
1407 l_country VARCHAR2(60);
1408 l_address1 VARCHAR2(240);
1409 l_address2 VARCHAR2(240);
1410 l_address3 VARCHAR2(240);
1411 l_address4 VARCHAR2(240);
1412 l_city VARCHAR2(60);
1413 l_postal_code VARCHAR2(60);
1414 l_state VARCHAR2(60);
1415 l_province VARCHAR2(60);
1416 l_ship_site_use_type_id NUMBER;
1417 l_transaction_type VARCHAR2(25);
1418 --
1419 -- params to projects api
1420 --l_project_number VARCHAR2(25);
1421 --l_project_name VARCHAR2(100);
1422 --l_project_id NUMBER;
1423 --l_task_number VARCHAR2(25);
1424 --l_task_name VARCHAR2(100);
1425 --l_task_id NUMBER;
1426 l_return_status VARCHAR2(10);
1427 l_msg_count NUMBER;
1428 l_msg_data VARCHAR2(100);
1429 l_return_status2 VARCHAR2(10);
1430 l_line_type_id NUMBER;
1431 l_line_type VARCHAR2(25);
1432 l_line_lookup_code VARCHAR2(25);
1433 l_deafult_line_type_category VARCHAR2(122);
1434 l_default_line_type_uom VARCHAR2(25);
1435 l_amount_based_uom VARCHAR2(25);
1436 l_amount_based_unit_of_measure VARCHAR2(25);
1437 l_item_number_delimiter varchar2(1);
1438 l_inventory_org_id number;
1439 l_progress_payment_type PON_AUCTION_HEADERS_ALL.progress_payment_type%TYPE;
1440 l_advance_negotiable_flag PON_AUCTION_HEADERS_ALL.advance_negotiable_flag%TYPE;
1441 l_recoupment_negotiable_flag PON_AUCTION_HEADERS_ALL.recoupment_negotiable_flag%TYPE;
1442 l_po_style_id PON_AUCTION_HEADERS_ALL.po_style_id%TYPE;
1443 l_line_type_restriction VARCHAR2(30);
1444 l_auction_header_id NUMBER;
1445 l_auction_round_number NUMBER;
1446 --
1447 BEGIN
1448 -- DBMS_OUTPUT.PUT_LINE('> validate()');
1449 --
1450 -- AWARDBID specific validation
1451 IF p_source = 'AWARDBID' THEN
1452 validateAwardBid(p_batch_id, g_txt_upload_mode);
1453 RETURN;
1454 END IF;
1455 --
1456 --
1457 --
1458 -- the rest of the validation only for importItems not for importBids
1459 IF p_source <> 'SBID' THEN
1460 --
1461 -- Category name has reference to category id --
1462 -- AUCTION and BID --
1463 --
1464 -- First of all get the default UOM for the amount based line type.
1465 get_default_uom(p_language,p_trading_partner_id,l_amount_based_uom,l_amount_based_unit_of_measure);
1466
1467 -- Retrive the inventory org id
1468 get_inventory_org_id(p_org_id, l_inventory_org_id);
1469
1470 -- load po style settings
1471 get_po_style_settings(p_batch_id, l_po_style_id, l_line_type_restriction);
1472
1473 -- If a negotiation document allows a buyer to select whether price and quantity are applicable for a line,
1474 -- and the buyer specifies that it is NOT applicable, but still enters a quantity and price(s), we need to
1475 -- report these errors
1476
1477
1478 select auction_header_id
1479 into l_auction_header_id
1480 from pon_item_prices_interface
1481 where batch_id = p_batch_id
1482 and auction_header_id is not null
1483 and rownum =1;
1484
1485 SELECT nvl(auction_round_number,0),
1486 progress_payment_type,
1487 advance_negotiable_flag,
1488 recoupment_negotiable_flag
1489 INTO l_auction_round_number,
1490 l_progress_payment_type,
1491 l_advance_negotiable_flag,
1492 l_recoupment_negotiable_flag
1493 FROM
1494 pon_auction_headers_all
1495 WHERE
1496 auction_header_id = l_auction_header_id;
1497
1498 if(l_auction_round_number > 1) then
1499 pon_cp_intrfac_to_transaction.default_prev_round_amend_lines(l_auction_header_id,p_batch_id);
1500 END if;
1501
1502 IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
1503
1504 IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
1505 -- Quantity should be empty
1506 insert into PON_INTERFACE_ERRORS
1507 (interface_type,
1508 column_name,
1509 error_value,
1510 error_message_name,
1511 table_name,
1512 batch_id,
1513 interface_line_id)
1514 select interface_type,
1515 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
1516 quantity,
1517 'PON_AUCTS_PR_QT_NOT_APPLY',
1518 'PON_ITEM_PRICES_INTERFACE',
1519 batch_id,
1520 interface_line_id
1521 from pon_item_prices_interface
1522 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1523 quantity is not null and
1524 batch_id = p_batch_id;
1525 END IF;
1526
1527 IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
1528 -- UOM should be empty
1529 insert into PON_INTERFACE_ERRORS
1530 (interface_type,
1531 column_name,
1532 error_value,
1533 error_message_name,
1534 table_name,
1535 batch_id,
1536 interface_line_id)
1537 select interface_type,
1538 fnd_message.get_string('PON','PON_AUCTION_UOM'),
1539 unit_of_measure,
1540 'PON_AUCTS_PR_QT_NOT_APPLY',
1541 'PON_ITEM_PRICES_INTERFACE',
1542 batch_id,
1543 interface_line_id
1544 from pon_item_prices_interface
1545 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1546 (unit_of_measure is not null and unit_of_measure <> 'UOM_NONE_ENTERED') and
1547 batch_id = p_batch_id;
1548 END IF;
1549
1550 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
1551 -- Target Price should be empty
1552 insert into PON_INTERFACE_ERRORS
1553 (interface_type,
1554 column_name,
1555 error_value,
1556 error_message_name,
1557 table_name,
1558 batch_id,
1559 interface_line_id)
1560 select interface_type,
1561 fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
1562 target_price,
1563 'PON_AUCTS_PR_QT_NOT_APPLY',
1564 'PON_ITEM_PRICES_INTERFACE',
1565 batch_id,
1566 interface_line_id
1567 from pon_item_prices_interface
1568 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1569 target_price is not null and
1570 batch_id = p_batch_id;
1571 END IF;
1572
1573 IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
1574 -- Current Price should be empty
1575 insert into PON_INTERFACE_ERRORS
1576 (interface_type,
1577 column_name,
1578 error_value,
1579 error_message_name,
1580 table_name,
1581 batch_id,
1582 interface_line_id)
1583 select interface_type,
1584 fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
1585 current_price,
1586 'PON_AUCTS_PR_QT_NOT_APPLY',
1587 'PON_ITEM_PRICES_INTERFACE',
1588 batch_id,
1589 interface_line_id
1590 from pon_item_prices_interface
1591 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1592 current_price is not null and
1593 batch_id = p_batch_id;
1594 END IF;
1595
1596 END IF;
1597
1598 -- For Line Type Check.
1599 insert into PON_INTERFACE_ERRORS
1600 (interface_type,
1601 column_name,
1602 error_message_name,
1603 table_name,
1604 batch_id,
1605 interface_line_id)
1606 select interface_type,
1607 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1608 'PON_FIELD_MUST_BE_ENTERED',
1609 'PON_ITEM_PRICES_INTERFACE',
1610 batch_id,
1611 interface_line_id
1612 from pon_item_prices_interface
1613 where line_type = 'LINE_TYPE_NONE_ENTERED'
1614 and batch_id = p_batch_id
1615 and group_type <> 'GROUP';
1616
1617 --update the pon_item_prices_interface table
1618
1619 update pon_item_prices_interface p1
1620 set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1621 (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1622 FROM po_line_types_vl po2 WHERE upper(p1.line_type) = upper(po2.line_type(+))
1623 and (po2.inactive_date is null or po2.inactive_date > sysdate))
1624 where batch_id = p_batch_id
1625 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1626 and line_type is not null;
1627
1628
1629 insert into PON_INTERFACE_ERRORS
1630 (interface_type,
1631 column_name,
1632 error_value,
1633 error_message_name,
1634 table_name,
1635 batch_id,
1636 interface_line_id)
1637 select interface_type,
1638 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1639 line_type,
1640 'PON_AUC_LINE_TYPE_ERR',
1641 'PON_ITEM_PRICES_INTERFACE',
1642 batch_id,
1643 interface_line_id
1644 from pon_item_prices_interface
1645 where line_type_id is null
1646 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1647 and batch_id = p_batch_id;
1648
1649
1650 -- perform the following check if po style has restricted line types
1651 if (l_line_type_restriction = 'SPECIFIED') then
1652 insert into PON_INTERFACE_ERRORS
1653 (interface_type,
1654 column_name,
1655 error_value,
1656 error_message_name,
1657 table_name,
1658 batch_id,
1659 interface_line_id)
1660 select interface_type,
1661 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1662 line_type,
1663 'PON_AUC_LINE_TYPE_ERR',
1664 'PON_ITEM_PRICES_INTERFACE',
1665 batch_id,
1666 interface_line_id
1667 from pon_item_prices_interface
1668 where line_type_id not in ( select line_type_id
1669 from po_style_enabled_line_types
1670 where style_id = l_po_style_id)
1671 and line_type_id is not null
1672 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1673 and batch_id = p_batch_id;
1674 end if;
1675
1676 -- Bug 4722286.
1677 -- perform the following check if po style has restricted purchase basis
1678
1679 if (l_line_type_restriction = 'ALL') then
1680 insert into PON_INTERFACE_ERRORS
1681 (interface_type,
1682 column_name,
1683 error_value,
1684 error_message_name,
1685 table_name,
1686 batch_id,
1687 interface_line_id)
1688 select interface_type,
1689 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1690 line_type,
1691 'PON_AUC_LINE_TYPE_ERR',
1692 'PON_ITEM_PRICES_INTERFACE',
1693 batch_id,
1694 interface_line_id
1695 from pon_item_prices_interface
1696 where purchase_basis not in ( select purchase_basis
1697 from po_style_enabled_pur_bases
1698 where style_id = l_po_style_id)
1699 and line_type_id is not null
1700 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1701 and batch_id = p_batch_id;
1702 end if;
1703
1704 --
1705 -- Begin major services lines validation
1706 --
1707 -- First, make sure no services lines
1708 -- are on a standard outcome document
1709 --
1710 IF(p_contract_type = 'STANDARD') THEN
1711 --
1712 INSERT INTO pon_interface_errors
1713 (interface_type,
1714 column_name,
1715 error_value,
1716 error_message_name,
1717 table_name,
1718 batch_id,
1719 interface_line_id)
1720 SELECT interface_type,
1721 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1722 line_type,
1723 'PON_STANDARD_LINE_TYPES',
1724 'PON_ITEM_PRICES_INTERFACE',
1725 batch_id,
1726 interface_line_id
1727 FROM pon_item_prices_interface
1728 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1729 AND batch_id = p_batch_id
1730 AND purchase_basis = 'TEMP LABOR';
1731 END IF;
1732 --
1733 IF(p_contract_type = 'BLANKET' OR p_contract_type = 'CONTRACT') AND
1734 (p_global_flag = 'N') THEN
1735 INSERT INTO pon_interface_errors
1736 (interface_type,
1737 column_name,
1738 error_value,
1739 error_message_name,
1740 table_name,
1741 batch_id,
1742 interface_line_id)
1743 SELECT interface_type,
1744 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1745 line_type,
1746 'PON_GLOBAL_LINE_TYPES',
1747 'PON_ITEM_PRICES_INTERFACE',
1748 batch_id,
1749 interface_line_id
1750 FROM pon_item_prices_interface
1751 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1752 AND batch_id = p_batch_id
1753 AND purchase_basis = 'TEMP LABOR';
1754 END IF;
1755 --
1756 -- Do not allow Outside Processing lines for Global Agreements
1757 --
1758 IF(p_contract_type = 'BLANKET' and p_global_flag = 'Y') THEN
1759 insert into pon_interface_errors
1760 (interface_type,
1761 column_name,
1762 error_value,
1763 error_message_name,
1764 table_name,
1765 batch_id,
1766 interface_line_id)
1767 SELECT interface_type,
1768 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1769 line_type,
1770 'PON_AUC_GLOBAL_OP_LINE',
1771 'PON_ITEM_PRICES_INTERFACE',
1772 batch_id,
1773 interface_line_id
1774 FROM pon_item_prices_interface
1775 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1776 AND batch_id = p_batch_id
1777 AND outside_operation_flag = 'Y';
1778 END IF;
1779 --
1780 -- For Outside Processing lines, the Item is required
1781 --
1782 insert into pon_interface_errors
1783 (interface_type,
1784 column_name,
1785 error_value,
1786 error_message_name,
1787 table_name,
1788 batch_id,
1789 interface_line_id)
1790 SELECT interface_type,
1791 fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1792 null,
1793 'PON_AUC_OPL_ITEM_REQ',
1794 'PON_ITEM_PRICES_INTERFACE',
1795 batch_id,
1796 interface_line_id
1797 FROM pon_item_prices_interface
1798 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1799 AND item_number = 'ITEM_NUMBER_NONE_ENTERED'
1800 AND batch_id = p_batch_id
1801 AND outside_operation_flag = 'Y';
1802 --
1803 -- For temp labor lines, the job is required
1804 --
1805 INSERT INTO PON_INTERFACE_ERRORS
1806 (interface_type,
1807 column_name,
1808 error_value,
1809 error_message_name,
1810 table_name,
1811 batch_id,
1812 interface_line_id)
1813 SELECT interface_type,
1814 fnd_message.get_string('PON','PON_ITEM_JOB'),
1815 '',
1816 'PON_LINE_TYPE_JOB_REQ',
1817 'PON_ITEM_PRICES_INTERFACE',
1818 batch_id,
1819 interface_line_id
1820 FROM pon_item_prices_interface
1821 WHERE batch_id = p_batch_id
1822 AND purchase_basis = 'TEMP LABOR'
1823 AND item_number = 'ITEM_NUMBER_NONE_ENTERED';
1824 --
1825 -- Get job information for Services lines for valid jobs
1826 -- We are ignoring whatever the user entered for the description and category columns
1827 -- because there are not enterable on the UI.
1828 --
1829 -- First just get the job_id...
1830 --
1831 UPDATE pon_item_prices_interface p1
1832 SET job_id =
1833 (SELECT nvl(max(poj.job_id),-1)
1834 FROM po_job_associations poj,
1835 per_jobs pj,
1836 per_jobs_vl pjvl
1837 WHERE pjvl.name = p1.item_number AND
1838 pjvl.job_id = pj.job_id AND
1839 pj.job_id = poj.job_id AND
1840 sysdate < nvl(poj.inactive_date, sysdate + 1) AND
1841 sysdate between pj.date_from and nvl(pj.date_to, sysdate + 1))
1842 WHERE batch_id = p_batch_id AND
1843 purchase_basis = 'TEMP LABOR' AND
1844 item_number <> 'ITEM_NUMBER_NONE_ENTERED';
1845 --
1846 -- Update the rest of the information for the service lines with job id's
1847 --
1848 UPDATE pon_item_prices_interface p1
1849 SET (item_description, category_name) =
1850 (SELECT poj.job_long_description,
1851 FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
1852 FROM po_job_associations poj,
1853 mtl_categories_kfv cat
1854 WHERE cat.category_id = poj.category_id AND
1855 poj.job_id = p1.job_id)
1856 WHERE batch_id = p_batch_id AND
1857 purchase_basis = 'TEMP LABOR' AND
1858 item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
1859 job_id <> -1;
1860 --
1861 -- Validate the job
1862 --
1863 INSERT INTO PON_INTERFACE_ERRORS (
1864 interface_type,
1865 column_name,
1866 error_value,
1867 error_message_name,
1868 table_name,
1869 batch_id,
1870 interface_line_id)
1871 SELECT interface_type,
1872 fnd_message.get_string('PON','PON_ITEM_JOB'),
1873 item_number,
1874 'PON_JOB_INVALID',
1875 'PON_ITEM_PRICES_INTERFACE',
1876 batch_id,
1877 interface_line_id
1878 FROM pon_item_prices_interface p1
1879 WHERE batch_id = p_batch_id AND
1880 purchase_basis = 'TEMP LABOR' AND
1881 item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
1882 job_id = -1;
1883 --
1884 -- Set quantity to null for general services lines
1885 --
1886 UPDATE pon_item_prices_interface p1
1887 SET quantity = NULL,
1888 unit_of_measure = NULL
1889 WHERE batch_id = p_batch_id
1890 AND order_type_lookup_code = 'FIXED PRICE'
1891 AND purchase_basis = 'SERVICES';
1892 --
1893 -- Set quantity to null for fixed price temp labor lines
1894 --
1895 UPDATE pon_item_prices_interface p1
1896 SET quantity = null
1897 WHERE batch_id = p_batch_id
1898 AND order_type_lookup_code = 'FIXED PRICE'
1899 AND purchase_basis = 'TEMP LABOR';
1900 --
1901 -- Default category, job_description,
1902 -- additional_job_details for temp labor lines
1903 --
1904 UPDATE pon_item_prices_interface p1
1905 SET (item_description, additional_job_details, category_id, category_name) =
1906 (SELECT poj.job_description,
1907 decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
1908 cat.category_id,
1909 FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
1910 FROM po_job_associations poj,
1911 mtl_categories_kfv cat
1912 WHERE poj.job_id = p1.job_id
1913 AND cat.category_id = poj.category_id)
1914 WHERE batch_id = p_batch_id AND
1915 purchase_basis = 'TEMP LABOR' AND
1916 job_id <> -1;
1917 --
1918 -- Validate differential response type
1919 --
1920 insert into PON_INTERFACE_ERRORS
1921 (interface_type,
1922 column_name,
1923 error_value,
1924 error_message_name,
1925 table_name,
1926 batch_id,
1927 interface_line_id)
1928 select interface_type,
1929 fnd_message.get_string('PON','PON_PRICE_DIFF_RESPONSE'),
1930 differential_response_type,
1931 'PON_INVALID_DIFF_RESPONSE',
1932 'PON_ITEM_PRICES_INTERFACE',
1933 batch_id,
1934 interface_line_id
1935 from pon_item_prices_interface p1
1936 where batch_id = p_batch_id and
1937 purchase_basis = 'TEMP LABOR' and
1938 differential_response_type not in ('DIFF_NONE_ENTERED',
1939 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
1940 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
1941 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'));
1942 --
1943 -- clear out the differential response type
1944 -- if it does not apply
1945 update pon_item_prices_interface
1946 set differential_response_type = null
1947 where batch_id = p_batch_id and
1948 (purchase_basis = 'TEMP LABOR' and
1949 differential_response_type not in (fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
1950 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
1951 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'))) or
1952 (purchase_basis <> 'TEMP LABOR');
1953 --
1954 -- End major services validation
1955 --
1956 -- checks for invalid item numbers
1957 -- ignore inventory item related fields for amount based lines
1958
1959 l_item_number_delimiter := '.';
1960
1961 insert into PON_INTERFACE_ERRORS
1962 (interface_type,
1963 column_name,
1964 error_value,
1965 error_message_name,
1966 table_name,
1967 batch_id,
1968 interface_line_id)
1969 select interface_type,
1970 fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1971 item_number,
1972 'PON_AUCTS_SS_INVALID_INV_NUM',
1973 'PON_ITEM_PRICES_INTERFACE',
1974 batch_id,
1975 interface_line_id
1976 from pon_item_prices_interface p1
1977 where batch_id = p_batch_id and
1978 item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
1979 upper(order_type_lookup_code) <> upper('AMOUNT') and
1980 purchase_basis <> 'TEMP LABOR' and
1981 order_type_lookup_code <> 'FIXED PRICE' and
1982 not exists (SELECT '1'
1983 FROM mtl_system_items_kfv msi,
1984 mtl_default_sets_view mdsv,
1985 mtl_item_categories mic,
1986 mtl_categories_kfv mck
1987 WHERE msi.concatenated_segments = p1.item_number and
1988 msi.organization_id = l_inventory_org_id and
1989 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
1990 msi.purchasing_enabled_flag = 'Y' and
1991 mdsv.functional_area_id = 2 and
1992 mic.inventory_item_id = msi.inventory_item_id and
1993 mic.organization_id = msi.organization_id and
1994 mic.category_set_id = mdsv.category_set_id and
1995 mck.category_id = mic.category_id
1996 and mck.enabled_flag = 'Y'
1997 and sysdate between nvl(mck.start_date_active, sysdate) and
1998 nvl(mck.end_date_active, sysdate)
1999 and nvl(mck.disable_date, sysdate + 1) > sysdate
2000 and (mdsv.validate_flag='Y' and mck.category_id in (select mcsv.category_id from mtl_category_set_valid_cats mcsv where mcsv.category_set_id = mdsv.category_set_id) or mdsv.validate_flag <> 'Y'));
2001
2002 -- set item number and revision to null if this is a group
2003 update pon_item_prices_interface p1
2004 set item_number = null,
2005 item_revision = null
2006 where batch_id = p_batch_id and
2007 item_number = 'ITEM_NUMBER_NONE_ENTERED' and
2008 group_type = 'GROUP';
2009
2010 -- sets item number and revision to null if item number is invalid or line type is amount based
2011 -- by setting the item number to null, sourcing one-time item validation will occur for
2012 -- unit of measure, category, etc..
2013
2014 update pon_item_prices_interface p1
2015 set item_number = null,
2016 item_revision = null
2017 where batch_id = p_batch_id AND
2018 purchase_basis = 'SERVICES' or
2019 (purchase_basis = 'GOODS' AND
2020 (item_number = 'ITEM_NUMBER_NONE_ENTERED') OR
2021 (item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
2022 not exists (select '1'
2023 from mtl_system_items_kfv msi,
2024 mtl_default_sets_view mdsv,
2025 mtl_item_categories mic,
2026 mtl_categories_kfv mck
2027 where msi.concatenated_segments = p1.item_number and
2028 msi.organization_id = l_inventory_org_id and
2029 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
2030 msi.purchasing_enabled_flag = 'Y' and
2031 mdsv.functional_area_id = 2 and
2032 mic.inventory_item_id = msi.inventory_item_id and
2033 mic.organization_id = msi.organization_id and
2034 mic.category_set_id = mdsv.category_set_id and
2035 mck.category_id = mic.category_id
2036 and mck.enabled_flag = 'Y'
2037 and sysdate between nvl(mck.start_date_active, sysdate) and
2038 nvl(mck.end_date_active, sysdate)
2039 and nvl(mck.disable_date, sysdate + 1) > sysdate
2040 and (mdsv.validate_flag='Y' and mck.category_id in (select mcsv.category_id from mtl_category_set_valid_cats mcsv where mcsv.category_set_id = mdsv.category_set_id) or mdsv.validate_flag <> 'Y'))));
2041
2042
2043 -- set inventory item id, the description update flag, and default the description and unit of measure
2044 -- it not entered for inventory items
2045
2046 update pon_item_prices_interface p1
2047 set (item_id, item_description, allow_item_desc_update_flag, unit_of_measure) =
2048 (select msi.inventory_item_id,
2049 decode(p1.item_description, 'ITEM_NONE_ENTERED', msitl.description, p1.item_description),
2050 msi.allow_item_desc_update_flag,
2051 decode(p1.unit_of_measure, 'UOM_NONE_ENTERED', uom.unit_of_measure_tl, p1.unit_of_measure)
2052 from mtl_system_items_kfv msi,
2053 mtl_system_items_tl msitl,
2054 mtl_units_of_measure_tl uom
2055 where msi.concatenated_segments = p1.item_number and
2056 msi.organization_id = l_inventory_org_id and
2057 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
2058 msi.purchasing_enabled_flag = 'Y' and
2059 msi.inventory_item_id = msitl.inventory_item_id and
2060 msi.organization_id = msitl.organization_id and
2061 msitl.language = p_language and
2062 msi.primary_uom_code = uom.uom_code and
2063 uom.language = p_language)
2064 where batch_id = p_batch_id and
2065 item_number is not NULL and
2066 purchase_basis = 'GOODS';
2067
2068 -- default the category if not entered for valid item numbers
2069
2070 update pon_item_prices_interface p1
2071 set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2072 from mtl_default_sets_view mdsv,
2073 mtl_item_categories mic,
2074 mtl_categories_kfv mck
2075 where mdsv.functional_area_id = 2 and
2076 mic.inventory_item_id = p1.item_id and
2077 mic.organization_id = l_inventory_org_id and
2078 mic.category_set_id = mdsv.category_set_id and
2079 mck.category_id = mic.category_id)
2080 where batch_id = p_batch_id and
2081 purchase_basis <> 'TEMP LABOR' and
2082 item_number is not null and
2083 (category_name = 'CAT_NONE_ENTERED' or category_name is null);
2084
2085 -- validate description for inventory items where the modified flag is set to false
2086
2087 insert into PON_INTERFACE_ERRORS
2088 (interface_type,
2089 column_name,
2090 error_value,
2091 error_message_name,
2092 table_name,
2093 batch_id,
2094 interface_line_id)
2095 select interface_type,
2096 fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2097 item_description,
2098 'PON_AUCTS_INVALID_INV_DESC',
2099 'PON_ITEM_PRICES_INTERFACE',
2100 batch_id,
2101 interface_line_id
2102 from pon_item_prices_interface p1
2103 where batch_id = p_batch_id and
2104 purchase_basis <> 'TEMP LABOR' and
2105 item_number is not null and
2106 allow_item_desc_update_flag = 'N' and
2107 item_description <> (select msitl.description
2108 from mtl_system_items_kfv msi,
2109 mtl_system_items_tl msitl
2110 where msi.inventory_item_id = p1.item_id and
2111 msi.organization_id = l_inventory_org_id and
2112 msi.inventory_item_id = msitl.inventory_item_id and
2113 msi.organization_id = msitl.organization_id and
2114 msitl.language = p_language);
2115
2116 -- validate revision for inventory items
2117
2118 insert into PON_INTERFACE_ERRORS
2119 (interface_type,
2120 column_name,
2121 error_value,
2122 error_message_name,
2123 table_name,
2124 batch_id,
2125 interface_line_id)
2126 select interface_type,
2127 fnd_message.get_string('PON','PON_AUCTS_REVISION'),
2128 item_revision,
2129 'PON_AUCTS_INVALID_INV_REV',
2130 'PON_ITEM_PRICES_INTERFACE',
2131 batch_id,
2132 interface_line_id
2133 from pon_item_prices_interface p1
2134 where batch_id = p_batch_id and
2135 purchase_basis <> 'TEMP LABOR' and
2136 item_number is not null and
2137 item_revision not in (select revision
2138 from mtl_item_revisions_all_v
2139 where inventory_item_id = p1.item_id and
2140 organization_id = l_inventory_org_id);
2141
2142
2143 update pon_item_prices_interface p1
2144 set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2145 from
2146 MTL_CATEGORIES_KFV mck
2147 ,PO_LINE_TYPES plt
2148 where
2149 plt.line_type_id = p1.line_type_id
2150 and plt.category_id = mck.category_id)
2151 where batch_id = p_batch_id
2152 and ((CATEGORY_NAME = 'CAT_NONE_ENTERED') or (CATEGORY_NAME is null));
2153
2154 update pon_item_prices_interface p1
2155 set UNIT_OF_MEASURE = nvl(l_amount_based_unit_of_measure,'UOM_NONE_ENTERED')
2156 where batch_id = p_batch_id
2157 and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2158 and ( upper(order_type_lookup_code) = upper('AMOUNT'));
2159
2160 update pon_item_prices_interface p1
2161 set UNIT_OF_MEASURE = (select plt.unit_of_measure
2162 from
2163 PO_LINE_TYPES plt
2164 where
2165 plt.line_type_id = p1.line_type_id)
2166 where batch_id = p_batch_id
2167 and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null));
2168
2169
2170 INSERT INTO PON_INTERFACE_ERRORS
2171 ( interface_type
2172 , column_name
2173 , error_value
2174 , error_message_name
2175 , table_name
2176 , batch_id
2177 , interface_line_id
2178 )
2179 SELECT INTERFACE_TYPE
2180 , fnd_message.get_string('PON','PON_AUCTION_UOM')
2181 , unit_of_measure
2182 , 'PON_AUC_LINE_UOM_ERR'
2183 , 'PON_ITEM_PRICES_INTERFACE'
2184 , BATCH_ID
2185 , INTERFACE_LINE_ID
2186 FROM pon_item_prices_interface
2187 where batch_id = p_batch_id
2188 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2189 and order_type_lookup_code = 'AMOUNT'
2190 and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
2191 (upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_unit_of_measure) )) ;
2192
2193 INSERT INTO PON_INTERFACE_ERRORS
2194 ( interface_type
2195 , column_name
2196 , error_value
2197 , error_message_name
2198 , table_name
2199 , batch_id
2200 , interface_line_id
2201 )
2202 SELECT
2203 INTERFACE_TYPE
2204 , fnd_message.get_string('PON',decode(p_contract_type,'STANDARD','PON_AUCTS_QUANTITY','PON_AUCTS_EST_QUANTITY'))
2205 , quantity
2206 , 'PON_AUC_LINE_QUAN_ERR'
2207 , 'PON_ITEM_PRICES_INTERFACE'
2208 , BATCH_ID
2209 , INTERFACE_LINE_ID
2210 FROM pon_item_prices_interface
2211 where batch_id = p_batch_id
2212 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2213 and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2214 and ( NOT(nvl(quantity,-1) = 1)) ;
2215
2216 -- END Line Type Check
2217
2218 IF (is_valid_rule(p_doctype_Id, 'CATEGORY')) THEN
2219 insert into PON_INTERFACE_ERRORS
2220 (interface_type,
2221 column_name,
2222 error_message_name,
2223 table_name,
2224 batch_id,
2225 interface_line_id)
2226 select interface_type,
2227 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2228 'PON_FIELD_MUST_BE_ENTERED',
2229 'PON_ITEM_PRICES_INTERFACE',
2230 batch_id,
2231 interface_line_id
2232 from pon_item_prices_interface
2233 where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2234 AND batch_id = p_batch_id
2235 and group_type <> 'GROUP';
2236
2237 -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2238 update pon_item_prices_interface p
2239 set category_id = (select Nvl(MAX(MCK.category_id),-1)
2240 FROM (select category_id,
2241 FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2242 ENABLED_FLAG,
2243 START_DATE_ACTIVE,
2244 END_DATE_ACTIVE,
2245 STRUCTURE_ID,
2246 DISABLE_DATE
2247 from MTL_CATEGORIES_KFV) MCK,
2248 MTL_CATEGORY_SETS MCS,
2249 MTL_DEFAULT_CATEGORY_SETS MDCS,
2250 MTL_CATEGORIES MC
2251 WHERE MCK.CONCATENATED_SEGMENTS = p.category_name
2252 AND MCK.ENABLED_FLAG = 'Y'
2253 AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
2254 NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
2255 MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
2256 MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
2257 AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
2258 AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
2259 (SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
2260 MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
2261 AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
2262 where batch_id = p_batch_id
2263 and category_name <> 'CAT_NON_ENTERED';
2264
2265 -- For those which were not caught in the previous SQL
2266 update pon_item_prices_interface p
2267 set category_id = (select Nvl(MAX(MCK.category_id),-1)
2268 FROM (select category_id,
2269 FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2270 ENABLED_FLAG,
2271 START_DATE_ACTIVE,
2272 END_DATE_ACTIVE,
2273 STRUCTURE_ID,
2274 DISABLE_DATE
2275 from MTL_CATEGORIES_KFV) MCK,
2276 MTL_CATEGORY_SETS MCS,
2277 MTL_DEFAULT_CATEGORY_SETS MDCS,
2278 MTL_CATEGORIES MC
2279 WHERE UPPER(MCK.CONCATENATED_SEGMENTS) = UPPER(p.category_name)
2280 AND MCK.ENABLED_FLAG = 'Y'
2281 AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
2282 NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
2283 MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
2284 MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
2285 AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
2286 AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
2287 (SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
2288 MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
2289 AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
2290 where batch_id = p_batch_id
2291 and category_name <> 'CAT_NON_ENTERED'
2292 and ( category_id is null or category_id = -1 );
2293 --
2294 -- Because we do case insensitive validation for category_name,
2295 -- we need to update all valid user entered category names to the
2296 -- actual case sensitive value
2297 /*
2298 update pon_item_prices_interface p
2299 set category_name = (select Nvl(MAX(category_name),p.category_name)
2300 from icx_por_categories_tl i
2301 where i.rt_category_id = p.category_id
2302 and type=2 and i.language= p_language)
2303 where batch_id = p_batch_id
2304 and category_name <> 'CAT_NON_ENTERED'
2305 AND category_id <> -1;
2306 */
2307 update pon_item_prices_interface p
2308 set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2309 from mtl_categories_kfv i
2310 where i.category_id = p.category_id)
2311 where batch_id = p_batch_id
2312 and category_name <> 'CAT_NON_ENTERED'
2313 AND category_id <> -1;
2314
2315 insert into PON_INTERFACE_ERRORS
2316 (interface_type,
2317 column_name,
2318 error_value,
2319 error_message_name,
2320 table_name,
2321 batch_id,
2322 interface_line_id)
2323 select interface_type,
2324 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2325 category_name,
2326 decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2327 'PON_ITEM_PRICES_INTERFACE',
2328 batch_id,
2329 interface_line_id
2330 from pon_item_prices_interface
2331 where category_id = -1
2332 AND batch_id = p_batch_id
2333 AND category_name <> 'CAT_NON_ENTERED';
2334
2335 -- validate category name for inventory items
2336
2337 insert into PON_INTERFACE_ERRORS
2338 (interface_type,
2339 column_name,
2340 error_value,
2341 error_message_name,
2342 table_name,
2343 batch_id,
2344 interface_line_id)
2345 select interface_type,
2346 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2347 category_name,
2348 'PON_AUCTS_INVALID_INV_CAT',
2349 'PON_ITEM_PRICES_INTERFACE',
2350 batch_id,
2351 interface_line_id
2352 from pon_item_prices_interface p1
2353 where category_id <> -1 and
2354 batch_id = p_batch_id and
2355 purchase_basis <> 'TEMP LABOR' and
2356 order_type_lookup_code <> 'FIXED PRICE' and
2357 item_number is not null and
2358 category_name <> (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2359 from mtl_default_sets_view mdsv,
2360 mtl_item_categories mic,
2361 mtl_categories_kfv mck
2362 where mdsv.functional_area_id = 2 and
2363 mic.inventory_item_id = p1.item_id and
2364 mic.organization_id = l_inventory_org_id and
2365 mic.category_set_id = mdsv.category_set_id and
2366 mck.category_id = mic.category_id);
2367
2368
2369
2370 END IF;
2371
2372
2373 IF (p_contract_type in ('BLANKET', 'CONTRACT')) THEN
2374
2375 -- default shopping category (ip category) using purchasing-iP category mappings
2376 -- when shopping category is missing...only for new lines since shopping category
2377 -- is optional
2378 IF (NVL(l_progress_payment_type,'NONE') = 'NONE') THEN
2379 update pon_item_prices_interface p1
2380 set ip_category_name = (select category_name
2381 from icx_cat_categories_v
2382 where rt_category_id = decode(pon_auction_pkg.get_mapped_ip_category(p1.category_id), -2, null, pon_auction_pkg.get_mapped_ip_category(p1.category_id)) and
2383 language = p_language)
2384 where batch_id = p_batch_id and
2385 (action is null or action = '+') and
2386 p1.category_id <> -1 and
2387 (p1.ip_category_name is null or p1.ip_category_name = 'IP_CAT_NONE_ENTERED');
2388
2389 -- iP category needs to be valid
2390
2391 insert into PON_INTERFACE_ERRORS
2392 (interface_type,
2393 column_name,
2394 error_message_name,
2395 table_name,
2396 batch_id,
2397 interface_line_id)
2398 select interface_type,
2399 fnd_message.get_string('PON','PON_SHOPPING_CAT'),
2400 'PON_SHOP_CAT_NOT_VALID',
2401 'PON_ITEM_PRICES_INTERFACE',
2402 batch_id,
2403 interface_line_id
2404 from pon_item_prices_interface p1
2405 where p1.batch_id = p_batch_id and
2406 p1.ip_category_name is not null and
2407 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2408 not exists (select null
2409 from icx_cat_categories_v icx
2410 where icx.category_name = p1.ip_category_name and
2411 icx.language = p_language);
2412
2413 -- set ip category name to null if ip cateogry is invalid
2414
2415 update pon_item_prices_interface p1
2416 set ip_category_name = null
2417 where p1.batch_id = p_batch_id and
2418 p1.ip_category_name is not null and
2419 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2420 not exists (select null
2421 from icx_cat_categories_v icx
2422 where icx.category_name = p1.ip_category_name and
2423 icx.language = p_language);
2424
2425 -- set ip category id
2426 update pon_item_prices_interface p1
2427 set ip_category_id = (select rt_category_id
2428 from icx_cat_categories_v icx
2429 where icx.category_name = p1.ip_category_name and
2430 language = p_language)
2431 where p1.batch_id = p_batch_id and
2432 p1.ip_category_name is not null and
2433 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED';
2434 END IF; -- If progress payment type is NONE(i.e non complex work style)
2435 END IF;
2436
2437 --
2438 -- Item description can't be null
2439 --
2440 insert into PON_INTERFACE_ERRORS
2441 (interface_type,
2442 column_name,
2443 error_message_name,
2444 table_name,
2445 batch_id,
2446 interface_line_id)
2447 select interface_type,
2448 fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2449 'PON_FIELD_MUST_BE_ENTERED',
2450 'PON_ITEM_PRICES_INTERFACE',
2451 batch_id,
2452 interface_line_id
2453 from pon_item_prices_interface
2454 where item_description = 'ITEM_NONE_ENTERED'
2455 AND batch_id = p_batch_id
2456 and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2457
2458 update pon_item_prices_interface p1
2459 set item_description = null
2460 where batch_id = p_batch_id and
2461 item_description = 'ITEM_NONE_ENTERED';
2462
2463 --
2464 --
2465 -- Unit of Measure
2466 -- AUCTION and BID --
2467 --
2468 IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
2469 insert into PON_INTERFACE_ERRORS
2470 (interface_type,
2471 column_name,
2472 error_message_name,
2473 table_name,
2474 batch_id,
2475 interface_line_id)
2476 select interface_type,
2477 fnd_message.get_string('PON','PON_ORDER_UNIT_H'),
2478 'PON_FIELD_MUST_BE_ENTERED',
2479 'PON_ITEM_PRICES_INTERFACE',
2480 batch_id,
2481 interface_line_id
2482 from pon_item_prices_interface
2483 where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2484 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2485 AND order_type_lookup_code <> 'FIXED PRICE'
2486 AND batch_id = p_batch_id
2487 and group_type <> 'GROUP';
2488 --
2489 -- Unit of Measure must be valid in mtl_units_of_measure_tl
2490 --
2491 -- Some modifications to avoid full table scan and more imp
2492 -- to incorporate the new demand in bug 2319969
2493 update pon_item_prices_interface p
2494 set uom_code = (select nvl(max(uom_code),'XXX') from
2495 mtl_units_of_measure_tl m
2496 where language = p_language
2497 and unit_of_measure_tl = p.unit_of_measure
2498 and (p.purchase_basis <> 'TEMP LABOR' or
2499 (p.purchase_basis = 'TEMP LABOR' and
2500 exists (select 1 from mtl_uom_conversions_val_v where
2501 m.unit_of_measure = unit_of_measure and
2502 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2503 where batch_id = p_batch_id
2504 and unit_of_measure <> 'UOM_NONE_ENTERED';
2505 --
2506 update pon_item_prices_interface p
2507 set uom_code = (select nvl(max(uom_code),'XXX') from
2508 mtl_units_of_measure_tl m
2509 where language = p_language
2510 and upper(unit_of_measure_tl) = upper(p.unit_of_measure)
2511 and (p.purchase_basis <> 'TEMP LABOR' or
2512 (p.purchase_basis = 'TEMP LABOR' and
2513 exists (select 1 from mtl_uom_conversions_val_v where
2514 m.unit_of_measure = unit_of_measure and
2515 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2516 where batch_id = p_batch_id
2517 and unit_of_measure <> 'UOM_NONE_ENTERED'
2518 and uom_code = 'XXX';
2519 --
2520 update pon_item_prices_interface p
2521 set uom_code = (select nvl(max(uom_code),'XXX') from
2522 mtl_units_of_measure_tl m
2523 where language = p_language
2524 and uom_code = p.unit_of_measure
2525 and (p.purchase_basis <> 'TEMP LABOR' or
2526 (p.purchase_basis = 'TEMP LABOR' and
2527 exists (select 1 from mtl_uom_conversions_val_v where
2528 m.unit_of_measure = unit_of_measure and
2529 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2530 where batch_id = p_batch_id
2531 and unit_of_measure <> 'UOM_NONE_ENTERED'
2532 and uom_code = 'XXX';
2533 --
2534 update pon_item_prices_interface p
2535 set uom_code = (select nvl(max(uom_code),'XXX') from
2536 mtl_units_of_measure_tl m
2537 where language = p_language
2538 and upper(uom_code) = upper(p.unit_of_measure)
2539 and (p.purchase_basis <> 'TEMP LABOR' or
2540 (p.purchase_basis = 'TEMP LABOR' and
2541 exists (select 1 from mtl_uom_conversions_val_v where
2542 m.unit_of_measure = unit_of_measure and
2543 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2544 where batch_id = p_batch_id
2545 and unit_of_measure <> 'UOM_NONE_ENTERED'
2546 and uom_code = 'XXX';
2547
2548 --
2549
2550 -- Because we do case insensitive validation for uom_code,
2551 -- we need to update all valid user entered unit of measures to the
2552 -- actual case sensitive value
2553 update pon_item_prices_interface p
2554 set unit_of_measure = (select nvl(max(unit_of_measure_tl),p.unit_of_measure) from
2555 mtl_units_of_measure_tl m
2556 where language = p_language
2557 and uom_code = p.uom_code)
2558 where batch_id = p_batch_id
2559 and unit_of_measure <> 'UOM_NONE_ENTERED'
2560 AND uom_code <> 'XXX';
2561
2562 insert into PON_INTERFACE_ERRORS
2563 (interface_type,
2564 column_name,
2565 error_value,
2566 error_message_name,
2567 table_name,
2568 batch_id,
2569 interface_line_id)
2570 select interface_type,
2571 fnd_message.get_string('PON','PON_AUCTION_UOM'),
2572 unit_of_measure,
2573 decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2574 'PON_ITEM_PRICES_INTERFACE',
2575 batch_id,
2576 interface_line_id
2577 from pon_item_prices_interface p
2578 where unit_of_measure <> 'UOM_NONE_ENTERED'
2579 AND batch_id = p_batch_id
2580 AND uom_code = 'XXX'
2581 AND nvl(price_and_quantity_apply, 'Y') = 'Y';
2582
2583 -- validate unit of measure for inventory items
2584
2585 insert into PON_INTERFACE_ERRORS
2586 (interface_type,
2587 column_name,
2588 error_value,
2589 error_message_name,
2590 table_name,
2591 batch_id,
2592 interface_line_id)
2593 select interface_type,
2594 fnd_message.get_string('PON','PON_AUCTION_UOM'),
2595 unit_of_measure,
2596 'PON_AUCTS_INVALID_INV_UOM',
2597 'PON_ITEM_PRICES_INTERFACE',
2598 batch_id,
2599 interface_line_id
2600 from pon_item_prices_interface p1
2601 where batch_id = p_batch_id and
2602 purchase_basis <> 'TEMP LABOR' and
2603 item_number is not null and
2604 uom_code not in (select uom_code
2605 from mtl_item_uoms_view
2606 where inventory_item_id = p1.item_id and
2607 organization_id = l_inventory_org_id) and
2608 nvl(price_and_quantity_apply, 'Y') = 'Y';
2609
2610 -- Defaulting of Unit of Measure still occurs for lines where quantity and price don't apply
2611 -- For these lines, we will reset the unit of measure fields
2612 IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
2613 update pon_item_prices_interface p1
2614 set unit_of_measure = null,
2615 uom_code = null
2616 where batch_id = p_batch_id and
2617 nvl(price_and_quantity_apply, 'Y') = 'N';
2618 END IF;
2619
2620 END IF;
2621
2622 --
2623 -- bug 3353248
2624 -- check quantity can't be null under certain conditions.
2625 --
2626 --
2627 if (p_contract_type = 'STANDARD' or p_contract_type is null) then
2628 insert into PON_INTERFACE_ERRORS
2629 (interface_type,
2630 column_name,
2631 error_message_name,
2632 table_name,
2633 batch_id,
2634 interface_line_id)
2635 select interface_type,
2636 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
2637 'PON_FIELD_MUST_BE_ENTERED',
2638 'PON_ITEM_PRICES_INTERFACE',
2639 batch_id,
2640 interface_line_id
2641 from pon_item_prices_interface
2642 where quantity is null
2643 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2644 and batch_id = p_batch_id
2645 and purchase_basis <> 'TEMP LABOR'
2646 and order_type_lookup_code <> 'AMOUNT'
2647 and order_type_lookup_code <> 'FIXED PRICE'
2648 and group_type <> 'GROUP';
2649 end if;
2650
2651 -- quantity cannot also be null if line is neither fixed-price based nor amount-based and it has a fixed amount price factor
2652 IF (p_contract_type IN ('BLANKET', 'CONTRACT')) THEN
2653 INSERT INTO pon_interface_errors
2654 (interface_type,
2655 column_name,
2656 error_value,
2657 error_message_name,
2658 token1_name,
2659 token1_value,
2660 table_name,
2661 batch_id,
2662 interface_line_id)
2663 SELECT
2664 interface_type,
2665 fnd_message.get_string('PON', 'PON_AUCTS_EST_QUANTITY'),
2666 quantity,
2667 'PON_AUC_QUAN_FIXED_AMT',
2668 'LINENUM',
2669 interface_line_id,
2670 'PON_ITEM_PRICES_INTERFACE',
2671 batch_id,
2672 interface_line_id
2673 FROM pon_item_prices_interface
2674 WHERE
2675 quantity IS NULL
2676 AND order_type_lookup_code <> 'FIXED PRICE'
2677 AND order_type_lookup_code <> 'AMOUNT'
2678 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2679 AND batch_id = p_batch_id
2680 AND EXISTS (SELECT 1
2681 FROM
2682 pon_auc_price_elements_int pfs,
2683 fnd_lookup_values lookups
2684 WHERE
2685 pfs.batch_id = pon_item_prices_interface.batch_id
2686 AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2687 AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2688 AND lookups.lookup_type = 'PON_PRICING_BASIS'
2689 AND lookups.lookup_code = 'FIXED_AMOUNT'
2690 AND lookups.view_application_id = 0
2691 AND lookups.security_group_id = 0
2692 AND lookups.meaning = pfs.pricing_basis_name
2693 AND lookups.language = USERENV('LANG'));
2694 END IF;
2695
2696 -- Quantity > 0 --
2697 -- AUCTION and BID --
2698 --
2699 IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
2700 insert into PON_INTERFACE_ERRORS
2701 (interface_type,
2702 column_name,
2703 error_value,
2704 error_message_name,
2705 table_name,
2706 batch_id,
2707 interface_line_id)
2708 select interface_type,
2709 decode(p_contract_type, 'BLANKET','PON_AUCTS_EST_QUANTITY', 'CONTRACT', 'PON_AUCTS_EST_QUANTITY', 'PON_AUCTS_QUANTITY'),
2710 quantity,
2711 'PON_MUST_BE_POSITIVE_NUMBER',
2712 'PON_ITEM_PRICES_INTERFACE',
2713 batch_id,
2714 interface_line_id
2715 from pon_item_prices_interface
2716 where quantity <= 0
2717 AND order_type_lookup_code <> 'FIXED PRICE'
2718 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2719 AND batch_id = p_batch_id;
2720
2721 END IF;
2722 --
2723 --
2724 -- Need by date > sysdate --
2725 -- AUCTION only --
2726 --
2727 IF (is_valid_rule(p_doctype_Id, 'NEED_BY_DATE')) THEN
2728 -- NEED_BY_FROM_DATE
2729 insert into PON_INTERFACE_ERRORS
2730 (interface_type,
2731 column_name,
2732 error_value,
2733 error_message_name,
2734 table_name,
2735 batch_id,
2736 interface_line_id)
2737 select interface_type,
2738 fnd_message.get_string('PON','PON_AUC_NEED_BY_FROM_DATE'),
2739 need_by_start_date,
2740 'PON_DATE_MUST_BE_GT_TODAY',
2741 'PON_ITEM_PRICES_INTERFACE',
2742 batch_id,
2743 interface_line_id
2744 from pon_item_prices_interface
2745 where need_by_start_date < sysdate
2746 and batch_id = p_batch_id;
2747 --
2748 -- NEED_BY_TO_DATE
2749 insert into PON_INTERFACE_ERRORS
2750 (interface_type,
2751 column_name,
2752 error_value,
2753 error_message_name,
2754 table_name,
2755 batch_id,
2756 interface_line_id)
2757 select interface_type,
2758 fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE'),
2759 need_by_date,
2760 'PON_DATE_MUST_BE_GT_TODAY',
2761 'PON_ITEM_PRICES_INTERFACE',
2762 batch_id,
2763 interface_line_id
2764 from pon_item_prices_interface
2765 where need_by_date < sysdate
2766 and batch_id = p_batch_id;
2767 --
2768 -- NEED_BY_TO_DATE vs NEED_BY_FROM_DATE
2769 insert into PON_INTERFACE_ERRORS
2770 (interface_type,
2771 column_name,
2772 error_value,
2773 error_message_name,
2774 table_name,
2775 batch_id,
2776 interface_line_id)
2777 select interface_type,
2778 fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE'),
2779 need_by_date,
2780 'PON_AUC_NEEDBY_BEFORE_FROM_SS',
2781 'PON_ITEM_PRICES_INTERFACE',
2782 batch_id,
2783 interface_line_id
2784 from pon_item_prices_interface
2785 where need_by_date < need_by_start_date
2786 and batch_id = p_batch_id;
2787 --
2788 -- need by required for planned inventory items for SPO
2789 IF(p_contract_type = 'STANDARD') THEN
2790 insert into PON_INTERFACE_ERRORS
2791 (interface_type,
2792 column_name,
2793 error_value,
2794 error_message_name,
2795 table_name,
2796 batch_id,
2797 interface_line_id)
2798 select ip.interface_type,
2799 fnd_message.get_string('PON','PON_AUCTS_NEEDBY'),
2800 null,
2801 'PON_NEED_BY_DATE_REQ_SPD',
2802 'PON_ITEM_PRICES_INTERFACE',
2803 ip.batch_id,
2804 ip.interface_line_id
2805 from pon_item_prices_interface ip
2806 where ip.batch_id = p_batch_id
2807 and ip.need_by_date is null
2808 and ip.need_by_start_date is null
2809 and ip.item_id is not null
2810 and exists ( SELECT 'x'
2811 FROM mtl_system_items_kfv msi,
2812 financials_system_params_all fsp
2813 WHERE nvl(fsp.org_id, -9999) = nvl(p_org_id,-9999)
2814 and msi.organization_id = fsp.inventory_organization_id
2815 and msi.inventory_item_id = ip.item_id
2816 and (msi.INVENTORY_PLANNING_CODE in (1, 2) or msi.MRP_PLANNING_CODE in
2817 (3, 4, 7, 8, 9))
2818 );
2819 END IF;
2820 END IF;
2821 --
2822 -- Ship to location, validating non null, auction only --
2823 -- The validation is needed for standard po only as ship-to location is
2824 -- removed for blanket/contract purchase agreement.
2825 --
2826 IF (is_valid_rule(p_doctype_Id, 'SHIP_TO_LOCATION') and (p_contract_type is null or p_contract_type = 'STANDARD')) THEN
2827
2828 IF (is_required_rule(p_doctype_Id, 'SHIP_TO_LOCATION')) THEN
2829 insert into PON_INTERFACE_ERRORS
2830 ( interface_type
2831 , column_name
2832 , error_message_name
2833 , table_name
2834 , batch_id
2835 , interface_line_id
2836 )
2837 select interface_type
2838 , fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC')
2839 , 'PON_FIELD_MUST_BE_ENTERED'
2840 , 'PON_ITEM_PRICES_INTERFACE'
2841 , batch_id
2842 , interface_line_id
2843 from pon_item_prices_interface
2844 where batch_id = p_batch_id
2845 AND ship_to_location = 'SHIP_NONE_ENTERED'
2846 and group_type <> 'GROUP';
2847 END IF;
2848 -- fph
2849 -- again to avoid some full table scan
2850 update pon_item_prices_interface p
2851 set ship_to_location_id = (select (nvl(max(location_id), -1))
2852 from po_ship_to_loc_org_v po_v
2853 where po_v.location_code = p.ship_to_location)
2854 where batch_id = p_batch_id
2855 and ship_to_location <> 'SHIP_NONE_ENTERED';
2856 --
2857 update pon_item_prices_interface p
2858 set ship_to_location_id = (select (nvl(max(location_id), -1))
2859 from po_ship_to_loc_org_v po_v
2860 where upper(po_v.location_code) = upper(p.ship_to_location))
2861 where batch_id = p_batch_id
2862 and ship_to_location <> 'SHIP_NONE_ENTERED'
2863 and ship_to_location_id = -1;
2864 --
2865 -- Because we do case insensitive validation for ship_to_location,
2866 -- we need to update all valid user entered shipping locations to the
2867 -- actual case sensitive value fph
2868 update pon_item_prices_interface p
2869 set ship_to_location = (select (nvl(max(location_code), -1))
2870 from po_ship_to_loc_org_v po_v
2871 where po_v.location_id = p.ship_to_location_id)
2872 where batch_id = p_batch_id
2873 and ship_to_location <> 'SHIP_NONE_ENTERED'
2874 AND ship_to_location_id <> -1;
2875 --
2876 --
2877
2878 insert into PON_INTERFACE_ERRORS
2879 (interface_type,
2880 column_name,
2881 error_value,
2882 error_message_name,
2883 table_name,
2884 batch_id,
2885 interface_line_id)
2886 select interface_type,
2887 fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC'),
2888 ship_to_location,
2889 'PON_CAT_INVALID_VALUE',
2890 'PON_ITEM_PRICES_INTERFACE',
2891 batch_id,
2892 interface_line_id
2893 from pon_item_prices_interface
2894 where ship_to_location_id = -1
2895 AND ship_to_location <> 'SHIP_NONE_ENTERED'
2896 AND batch_id = p_batch_id;
2897
2898 END IF;
2899 --
2900 -- Target price NOT <=0 --
2901 -- AUCTION only --
2902 --
2903 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
2904 insert into PON_INTERFACE_ERRORS
2905 (interface_type,
2906 column_name,
2907 error_value,
2908 error_message_name,
2909 table_name,
2910 batch_id,
2911 interface_line_id)
2912 select interface_type,
2913 fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
2914 target_price,
2915 'PON_MUST_BE_POSITIVE_NUMBER',
2916 'PON_ITEM_PRICES_INTERFACE',
2917 batch_id,
2918 interface_line_id
2919 from pon_item_prices_interface
2920 where TARGET_PRICE <= 0
2921 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2922 and batch_id = p_batch_id;
2923 END IF;
2924 --
2925 -- Bid start price NOT <=0 --
2926 -- AUCTION only --
2927 --
2928 IF (is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
2929 insert into PON_INTERFACE_ERRORS
2930 (interface_type,
2931 column_name,
2932 error_value,
2933 error_message_name,
2934 table_name,
2935 batch_id,
2936 interface_line_id)
2937 select interface_type,
2938 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
2939 bid_start_price,
2940 'PON_MUST_BE_POSITIVE_NUMBER',
2941 'PON_ITEM_PRICES_INTERFACE',
2942 batch_id,
2943 interface_line_id
2944 from pon_item_prices_interface
2945 where bid_start_price <= 0
2946 and batch_id = p_batch_id;
2947 END IF;
2948 --
2949 -- Current price NOT <=0 --
2950 -- AUCTION/RFQs/Offers --
2951 --
2952 IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
2953 insert into PON_INTERFACE_ERRORS
2954 (interface_type,
2955 column_name,
2956 error_value,
2957 error_message_name,
2958 table_name,
2959 batch_id,
2960 interface_line_id)
2961 select interface_type,
2962 fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
2963 current_price,
2964 'PON_MUST_BE_POSITIVE_NUMBER',
2965 'PON_ITEM_PRICES_INTERFACE',
2966 batch_id,
2967 interface_line_id
2968 from pon_item_prices_interface
2969 where current_price <= 0
2970 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2971 and batch_id = p_batch_id;
2972 END IF;
2973 --
2974 -- reserve price NOT <=0 --
2975 -- AUCTION only --
2976 --
2977 IF (is_valid_rule(p_doctype_Id, 'RESERVE_PRICE')) THEN
2978 insert into PON_INTERFACE_ERRORS
2979 (interface_type,
2980 column_name,
2981 error_value,
2982 error_message_name,
2983 table_name,
2984 batch_id,
2985 interface_line_id)
2986 select interface_type,
2987 fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
2988 reserve_price,
2989 'PON_MUST_BE_POSITIVE_NUMBER',
2990 'PON_ITEM_PRICES_INTERFACE',
2991 batch_id,
2992 interface_line_id
2993 from pon_item_prices_interface
2994 where reserve_price <= 0 AND
2995 batch_id = p_batch_id;
2996 END IF;
2997 --
2998 --
2999 -- get the transaction type
3000 SELECT TRANSACTION_TYPE
3001 INTO l_transaction_type
3002 FROM PON_AUC_DOCTYPES
3003 WHERE DOCTYPE_ID = p_doctype_Id;
3004 --
3005 -- DBMS_OUTPUT.PUT_LINE('l_transaction_type = ' || l_transaction_type);
3006 IF(l_transaction_type = 'REVERSE') THEN
3007 --
3008 -- Bid start price > TARGET PRICE --
3009 -- Buyer AUCTION only --
3010 --
3011
3012 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE') AND is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
3013 insert into PON_INTERFACE_ERRORS
3014 (interface_type,
3015 column_name,
3016 error_value,
3017 error_message_name,
3018 table_name,
3019 batch_id,
3020 interface_line_id)
3021 select interface_type,
3022 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3023 bid_start_price,
3024 'PON_TARGET_GTR_BID_START',
3025 'PON_ITEM_PRICES_INTERFACE',
3026 batch_id,
3027 interface_line_id
3028 from pon_item_prices_interface
3029 where bid_start_price <= target_price AND
3030 batch_id = p_batch_id;
3031 END IF;
3032 --
3033 ELSIF(l_transaction_type = 'FORWARD') THEN
3034 --
3035 -- Bid start price < TARGET PRICE --
3036 -- Seller AUCTION only --
3037 --
3038 insert into PON_INTERFACE_ERRORS
3039 (interface_type,
3040 column_name,
3041 error_value,
3042 error_message_name,
3043 table_name,
3044 batch_id,
3045 interface_line_id)
3046 select interface_type,
3047 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3048 bid_start_price,
3049 'PON_AUCTS_START_LT_TARGET',
3050 'PON_ITEM_PRICES_INTERFACE',
3051 batch_id,
3052 interface_line_id
3053 from pon_item_prices_interface
3054 where bid_start_price >= target_price AND
3055 batch_id = p_batch_id;
3056 --
3057 -- bid start price < reserve price
3058 -- Seller AUCTION only --
3059 --
3060 insert into PON_INTERFACE_ERRORS
3061 (interface_type,
3062 column_name,
3063 error_value,
3064 error_message_name,
3065 table_name,
3066 batch_id,
3067 interface_line_id)
3068 select interface_type,
3069 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3070 bid_start_price,
3071 'PON_AUCTS_START_LT_RESERVE',
3072 'PON_ITEM_PRICES_INTERFACE',
3073 batch_id,
3074 interface_line_id
3075 from pon_item_prices_interface
3076 where reserve_price < bid_start_price AND
3077 batch_id = p_batch_id;
3078 --
3079 -- reserve price < target price
3080 -- Seller AUCTION only --
3081 --
3082 insert into PON_INTERFACE_ERRORS
3083 (interface_type,
3084 column_name,
3085 error_value,
3086 error_message_name,
3087 table_name,
3088 batch_id,
3089 interface_line_id)
3090 select interface_type,
3091 fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3092 reserve_price,
3093 'PON_AUCTS_RESERVE_LT_TARGET',
3094 'PON_ITEM_PRICES_INTERFACE',
3095 batch_id,
3096 interface_line_id
3097 from pon_item_prices_interface
3098 where reserve_price > target_price AND
3099 batch_id = p_batch_id;
3100 --
3101 END IF;
3102 --
3103
3104 -- unit target price NOT < 0
3105 -- AUCTION only
3106
3107 insert into PON_INTERFACE_ERRORS
3108 (interface_type,
3109 column_name,
3110 error_value,
3111 error_message_name,
3112 table_name,
3113 batch_id,
3114 interface_line_id)
3115 select interface_type,
3116 fnd_message.get_string('PON','PON_ITEM_PRICE_TARGET_VALUE'),
3117 unit_target_price,
3118 'PON_AUC_POSITIVE_OR_ZERO',
3119 'PON_ITEM_PRICES_INTERFACE',
3120 batch_id,
3121 interface_line_id
3122 from pon_item_prices_interface
3123 where UNIT_TARGET_PRICE < 0
3124 and nvl(price_and_quantity_apply, 'Y') = 'Y'
3125 and batch_id = p_batch_id;
3126
3127 --R12 - Added for Complex work
3128 IF p_contract_type IN ('STANDARD','CONTRACT') AND l_progress_payment_type <> 'NONE' THEN
3129 -- Call complex work validations only if progress_payment_type
3130 -- is NOT NONE
3131 validate_complexwork(p_batch_id, l_progress_payment_type, p_contract_type, l_advance_negotiable_flag, l_recoupment_negotiable_flag);
3132 END IF;
3133
3134
3135 END IF; -- end of if p_source <> 'SBID'
3136 --
3137 -- DBMS_OUTPUT.PUT_LINE('< validate()');
3138 END validate;
3139 --
3140
3141 END pon_validate_item_prices_int;