[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.47.12020000.7 2013/05/31 07:22:06 sgulkota 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 (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
99 FND_LOG.string(log_level => FND_LOG.level_statement,
100 module => g_module_prefix || 'is_valid_rule',
101 message => 'In procedure is_valid_rule, Doc Type id = '
102 || p_doctype_Id || ' ; Biz Rule Name = '|| p_bizrule_name);
103 END IF;
104
105 IF (l_valid_flag = 'Y') THEN
106 RETURN TRUE;
107 END IF;
108 --
109 RETURN FALSE;
110 --
111 END is_valid_rule;
112 --
113 --
114 FUNCTION is_required_rule( p_doctype_Id NUMBER
115 , p_bizrule_name VARCHAR2
116 )
117 RETURN BOOLEAN IS
118 --
119 l_required_flag pon_auc_doctype_rules.REQUIRED_FLAG%TYPE;
120 --
121 BEGIN
122 --
123 SELECT pon_auc_doctype_rules.REQUIRED_FLAG
124 INTO l_required_flag
125 FROM
126 PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
127 , PON_AUC_BIZRULES pon_auc_bizrules
128 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
129 AND pon_auc_doctype_rules.DOCTYPE_ID = p_doctype_Id
130 AND pon_auc_bizrules.NAME = p_bizrule_name;
131
132 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
133 FND_LOG.string(log_level => FND_LOG.level_statement,
134 module => g_module_prefix || 'is_required_rule',
135 message => 'In procedure is_required_rule, Doc Type id = '
136 || p_doctype_Id || ' ; Biz Rule Name = '|| p_bizrule_name);
137 END IF;
138 --
139 IF (l_required_flag = 'Y') THEN
140 RETURN TRUE;
141 END IF;
142 --
143 RETURN FALSE;
144 --
145 END is_required_rule;
146 --
147 --
148
149
150 PROCEDURE get_default_uom(p_language VARCHAR2
151 ,p_trading_partner_id NUMBER
152 ,p_amount_based_uom OUT NOCOPY VARCHAR2
153 ,p_amount_based_unit_of_measure OUT NOCOPY VARCHAR2
154 ) AS
155 BEGIN
156 SELECT
157 m.uom_code, m.unit_of_measure_tl
158 INTO
159 p_amount_based_uom,
160 p_amount_based_unit_of_measure
161 FROM
162 mtl_units_of_measure_tl m
163 ,pon_party_preferences p
164 WHERE
165 p.PARTY_ID = p_trading_partner_id
166 and PREFERENCE_NAME = 'AMOUNT_BASED_UOM'
167 and m.language = p_language
168 and m.uom_code = p.PREFERENCE_VALUE;
169 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
170 FND_LOG.string(log_level => FND_LOG.level_statement,
171 module => g_module_prefix || 'get_default_uom',
172 message => 'In procedure get_default_uom, Language = ' || p_language
173 || ' ; Trading Partener Id = '|| p_trading_partner_id
174 || ' ; p_amount_based_uom = '|| p_amount_based_uom
175 || ' ; p_amount_based_unit_of_measure = '
176 || p_amount_based_unit_of_measure);
177 END IF;
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 p_amount_based_uom := '';
182 p_amount_based_unit_of_measure := '';
183 END get_default_uom;
184 --
185 --
186 PROCEDURE get_inventory_org_id(p_org_id NUMBER
187 ,p_inventory_org OUT NOCOPY NUMBER) AS
188
189 BEGIN
190
191 SELECT inventory_organization_id
192 INTO p_inventory_org
193 FROM financials_system_params_all
194 WHERE nvl(org_id, -9999) = nvl(p_org_id, -9999);
195
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 p_inventory_org := p_org_id;
200
201 END get_inventory_org_id;
202 --
203
204 PROCEDURE validateAwardBid(p_batch_Id IN NUMBER,
205 p_spreadsheet_type IN VARCHAR2) IS
206 --
207 l_auction_id PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE;
208 l_request_id PON_AUCTION_HEADERS_ALL.request_id%TYPE;
209 l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
210 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.price_tiers_indicator%TYPE;
211 l_suffix VARCHAR2(3);
212 l_user_id NUMBER;
213 l_login_id NUMBER;
214 l_exp_date DATE;
215 l_num_of_award_lines NUMBER;
216
217 BEGIN
218
219 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN --{
220 FND_LOG.string(log_level => FND_LOG.level_statement,
221 module => g_module_prefix || 'validateAwardBid',
222 message => 'Entering the procedure for batch id = '
223 || p_batch_id || ' ; p_spreadsheet_type = '|| p_spreadsheet_type);
224 END IF; --}
225
226 select count(*)
227 into l_num_of_award_lines
228 from pon_award_items_interface
229 where batch_id = p_batch_id
230 and rownum = 1;
231
232 IF (l_num_of_award_lines = 0) THEN
233
234 RETURN;
235
236 END IF;
237
238 l_user_id := fnd_global.user_id;
239 l_login_id := fnd_global.login_id;
240 l_exp_date := SYSDATE+7;
241
242 select paha.auction_header_id , paha.request_id, paha.contract_type, paha.price_tiers_indicator
243 into l_auction_id, l_request_id, l_contract_type, l_price_tiers_indicator
244 from pon_auction_headers_all paha,
245 pon_award_items_interface paii
246 where paii.auction_header_id = paha.auction_header_id
247 and paii.batch_id = p_batch_id
248 and rownum = 1;
249
250 l_suffix := getDoctypeMessageSuffix(l_auction_id);
251
252 --
253 -- For Quantity Tiers
254 -- Setting the award_shipment_number = -1 by default.
255 -- Then updating it to the shipment number as per the award quantity
256 --
257
258 IF (p_spreadsheet_type = g_txt_upload_mode) THEN --{
259
260 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN --{
261 FND_LOG.string(log_level => FND_LOG.level_statement,
262 module => g_module_prefix || 'validateAwardBid',
263 message => 'updating the award shipment number PON_AWARD_ITEMS_INTERFACE for batch id = '
264 || p_batch_id || ' ; p_spreadsheet_type = '|| p_spreadsheet_type);
265 END IF; --}
266
267 UPDATE PON_AWARD_ITEMS_INTERFACE PAII
268 set AWARD_SHIPMENT_NUMBER = ( select -1
269 from pon_bid_item_prices pbip
270 where pbip.bid_number = PAII.bid_number
271 and pbip.auction_line_number = PAII.auction_line_number
272 and pbip.has_quantity_tiers = 'Y'
273 )
274 WHERE PAII.batch_id = p_batch_id
275 AND PAII.award_status = 'Y';
276
277 UPDATE PON_AWARD_ITEMS_INTERFACE PAII
278 set AWARD_SHIPMENT_NUMBER = (select nvl(( select shipment_number
279 from pon_bid_shipments pbs
280 where pbs.bid_number = PAII.bid_number
281 and pbs.auction_line_number = PAII.AUCTION_LINE_NUMBER
282 and PAII.award_quantity >= pbs.quantity
283 and PAII.award_quantity <= pbs.max_quantity ),-1)
284 from dual)
285 WHERE PAII.batch_id = p_batch_id
286 AND PAII.award_status = 'Y'
287 AND PAII.AWARD_SHIPMENT_NUMBER = -1;
288
289 END IF;--}
290
291
292 INSERT ALL
293 -- VALIDATION #1:
294 -- Check that the bid number is valid for this auction and this line number
295 WHEN NOT EXISTS (SELECT 'Y'
296 FROM pon_bid_item_prices bp
297 WHERE s_auction_header_id = bp.auction_header_id
298 AND s_auction_line_number = bp.line_number
299 AND s_bid_number = bp.bid_number)
300 THEN
301 INTO PON_INTERFACE_ERRORS
302 ( interface_type
303 , column_name
304 , error_message_name
305 , error_value
306 , table_name
307 , batch_id
308 , worksheet_name
309 , worksheet_sequence_number
310 , entity_message_code
311 , interface_line_id
312 , request_id
313 , expiration_date
314 , created_by
315 , creation_date
316 , last_updated_by
317 , last_update_date
318 , last_update_login
319 )
320 VALUES ( 'AWARDBID'
321 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
322 , 'PON_AUC_BID_NUMBER_INVALID' || l_suffix
323 , s_bid_number
324 , 'PON_AWARD_ITEMS_INTERFACE'
325 , s_batch_id
326 , s_worksheet_name
327 , s_worksheet_sequence_number
328 , s_entity_message_code
329 , s_interface_line_id
330 , l_request_id
331 , l_exp_date
332 , l_user_id
333 , sysdate
334 , l_user_id
335 , sysdate
336 , l_login_id
337 )
338 -- VALIDATION #2:
339 -- Check that there is no award decision made on this auction and this line number
340 WHEN (s_bid_line_award_status = 'Y' OR
341 s_award_quantity > 0 OR
342 s_awardreject_reason is not null)
343 AND s_line_award_status = 'COMPLETED'
344 THEN
345 INTO PON_INTERFACE_ERRORS
346 ( interface_type
347 , column_name
348 , error_message_name
349 , table_name
350 , batch_id
351 , worksheet_name
352 , worksheet_sequence_number
353 , entity_message_code
354 , interface_line_id
355 , request_id
356 , expiration_date
357 , created_by
358 , creation_date
359 , last_updated_by
360 , last_update_date
361 , last_update_login
362 )
363 VALUES ( 'AWARDBID'
364 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
365 , 'PON_AUC_ITEM_AWARDED'
366 , 'PON_AWARD_ITEMS_INTERFACE'
367 , s_batch_id
368 , s_worksheet_name
369 , s_worksheet_sequence_number
370 , s_entity_message_code
371 , s_interface_line_id
372 , l_request_id
373 , l_exp_date
374 , l_user_id
375 , sysdate
376 , l_user_id
377 , sysdate
378 , l_login_id
379 )
380 -- VALIDATION #3
381 -- Check if any award reco made for NOT shortlisted bids
382 WHEN (s_bid_line_award_status = 'Y' OR
383 s_award_quantity > 0 OR
384 s_awardreject_reason is not null)
385 AND s_shortlist_flag = 'N'
386 THEN
387 INTO PON_INTERFACE_ERRORS
388 ( interface_type
389 , column_name
390 , error_message_name
391 , error_value
392 , table_name
393 , batch_id
394 , worksheet_name
395 , worksheet_sequence_number
396 , entity_message_code
397 , interface_line_id
398 , request_id
399 , expiration_date
400 , created_by
401 , creation_date
402 , last_updated_by
403 , last_update_date
404 , last_update_login
405 )
406 VALUES ( 'AWARDBID'
407 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
408 , 'PON_AWARD_EXCLUDE_SHLIST_ERR' || l_suffix
409 , s_bid_number
410 , 'PON_AWARD_ITEMS_INTERFACE'
411 , s_batch_id
412 , s_worksheet_name
413 , s_worksheet_sequence_number
414 , s_entity_message_code
415 , s_interface_line_id
416 , l_request_id
417 , l_exp_date
418 , l_user_id
419 , sysdate
420 , l_user_id
421 , sysdate
422 , l_login_id
423 )
424 -- VALIDATION #4
425 -- Check if bid is active
426 WHEN (s_bid_line_award_status = 'Y' OR
427 s_award_quantity > 0 OR
428 s_awardreject_reason is not null)
429 AND s_bid_status <> 'ACTIVE'
430 THEN
431 INTO PON_INTERFACE_ERRORS
432 ( interface_type
433 , column_name
434 , error_message_name
435 , error_value
436 , table_name
437 , batch_id
438 , worksheet_name
439 , worksheet_sequence_number
440 , entity_message_code
441 , interface_line_id
442 , request_id
443 , expiration_date
444 , created_by
445 , creation_date
446 , last_updated_by
447 , last_update_date
448 , last_update_login
449 )
450 VALUES ( 'AWARDBID'
451 , fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
452 , 'PON_AWARD_BID_NOT_ACTIVE' || l_suffix
453 , s_bid_number
454 , 'PON_AWARD_ITEMS_INTERFACE'
455 , s_batch_id
456 , s_worksheet_name
457 , s_worksheet_sequence_number
458 , s_entity_message_code
459 , s_interface_line_id
460 , l_request_id
461 , l_exp_date
462 , l_user_id
463 , sysdate
464 , l_user_id
465 , sysdate
466 , l_login_id
467 )
468 -- VALIDATION #5
469 -- Check if supplier is active
470 WHEN (s_bid_line_award_status = 'Y' OR
471 s_award_quantity > 0 OR
472 s_awardreject_reason is not null)
473 AND s_end_date_active is not null and s_end_date_active <= trunc(sysdate)
474 THEN
475 INTO PON_INTERFACE_ERRORS
476 ( interface_type
477 , column_name
478 , error_message_name
479 , error_value
480 , table_name
481 , batch_id
482 , worksheet_name
483 , worksheet_sequence_number
484 , entity_message_code
485 , interface_line_id
486 , request_id
487 , expiration_date
488 , created_by
489 , creation_date
490 , last_updated_by
491 , last_update_date
492 , last_update_login
493 )
494 VALUES ( 'AWARDBID'
495 , 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))
496 , 'PON_AWARD_INACTIVE_SUPPLIER'
497 , s_trading_partner_name
498 , 'PON_AWARD_ITEMS_INTERFACE'
499 , s_batch_id
500 , s_worksheet_name
501 , s_worksheet_sequence_number
502 , s_entity_message_code
503 , s_interface_line_id
504 , l_request_id
505 , l_exp_date
506 , l_user_id
507 , sysdate
508 , l_user_id
509 , sysdate
510 , l_login_id
511 )
512 -- VALIDATION #6
513 -- Check if award quantity is > 0 when uploading an XML file
514 WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
515 AND s_award_quantity < 0
516 THEN
517 INTO PON_INTERFACE_ERRORS
518 ( interface_type
519 , column_name
520 , error_message_name
521 , error_value_number
522 , error_value_datatype
523 , table_name
524 , batch_id
525 , worksheet_name
526 , worksheet_sequence_number
527 , entity_message_code
528 , interface_line_id
529 , request_id
530 , expiration_date
531 , created_by
532 , creation_date
533 , last_updated_by
534 , last_update_date
535 , last_update_login
536 )
537 VALUES ( 'AWARDBID'
538 , decode(l_contract_type, 'STANDARD', fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY'), fnd_message.get_string('PON','PON_AUCTS_AGREED_QUANTITY'))
539 , decode(l_contract_type, 'STANDARD', 'PON_AUC_AWARD_QTY_COL_NEG', 'PON_AUC_QTY_AGREED_COL_NEG')
540 , s_award_quantity
541 , 'NUM'
542 , 'PON_AWARD_ITEMS_INTERFACE'
543 , s_batch_id
544 , s_worksheet_name
545 , s_worksheet_sequence_number
546 , s_entity_message_code
547 , s_interface_line_id
548 , l_request_id
549 , l_exp_date
550 , l_user_id
551 , sysdate
552 , l_user_id
553 , sysdate
554 , l_login_id
555 )
556 -- VALIDATION #7
557 -- Check if award quantity is entered when document is of type SPO, award status is Y and
558 -- an XML file is being uploaded
559 WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
560 AND l_contract_type = 'STANDARD'
561 AND s_order_type_lookup_code = 'QUANTITY'
562 AND s_bid_line_award_status = 'Y'
563 AND s_award_quantity is null
564 THEN
565 INTO PON_INTERFACE_ERRORS
566 ( interface_type
567 , column_name
568 , error_message_name
569 , table_name
570 , batch_id
571 , worksheet_name
572 , worksheet_sequence_number
573 , entity_message_code
574 , interface_line_id
575 , request_id
576 , expiration_date
577 , created_by
578 , creation_date
579 , last_updated_by
580 , last_update_date
581 , last_update_login
582 )
583 VALUES ( 'AWARDBID'
584 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
585 , 'PON_AUCTS_MUST_AWARD'
586 , 'PON_AWARD_ITEMS_INTERFACE'
587 , s_batch_id
588 , s_worksheet_name
589 , s_worksheet_sequence_number
590 , s_entity_message_code
591 , s_interface_line_id
592 , l_request_id
593 , l_exp_date
594 , l_user_id
595 , sysdate
596 , l_user_id
597 , sysdate
598 , l_login_id
599 )
600
601 -- VALIDATION #8
602 -- Awarded Qty should fall within Qty tiers provided on the bid, by supplier
603 -- for tab-delimited spreasheet
604
605 WHEN l_price_tiers_indicator = 'QUANTITY_BASED'
606 AND s_bid_line_award_status = 'Y'
607 AND s_award_shipment_number = -1
608 AND p_spreadsheet_type = g_txt_upload_mode
609 AND nvl(s_award_quantity , 0) > 0
610 THEN
611 INTO PON_INTERFACE_ERRORS
612 ( interface_type
613 , column_name
614 , error_message_name
615 , table_name
616 , error_value
617 , batch_id
618 , worksheet_name
619 , worksheet_sequence_number
620 , entity_message_code
621 , interface_line_id
622 , request_id
623 , expiration_date
624 , created_by
625 , creation_date
626 , last_updated_by
627 , last_update_date
628 , last_update_login
629 , TOKEN1_NAME
630 , TOKEN1_VALUE
631 )
632 VALUES ( 'AWARDBID'
633 , fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
634 , 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
635 , 'PON_AWARD_ITEMS_INTERFACE'
636 , s_award_quantity
637 , s_batch_id
638 , s_worksheet_name
639 , s_worksheet_sequence_number
640 , s_entity_message_code
641 , s_interface_line_id
642 , l_request_id
643 , l_exp_date
644 , l_user_id
645 , sysdate
646 , l_user_id
647 , sysdate
648 , l_login_id
649 , 'BID_NUM'
650 , s_bid_number
651 )
652
653 SELECT
654 ap.batch_id AS s_batch_id
655 , ap.auction_header_id AS s_auction_header_id
656 , ap.bid_number AS s_bid_number
657 , ap.auction_line_number AS s_auction_line_number
658 , ap.worksheet_name AS s_worksheet_name
659 , ap.worksheet_sequence_number AS s_worksheet_sequence_number
660 , 'PON_AUC_ITEMS' AS s_entity_message_code
661 , ap.interface_line_id AS s_interface_line_id
662 , ap.award_status AS s_bid_line_award_status
663 , ap.award_quantity AS s_award_quantity
664 , ap.awardreject_reason AS s_awardreject_reason
665 , ai.award_status AS s_line_award_status
666 , ai.order_type_lookup_code AS s_order_type_lookup_code
667 , bh.trading_partner_name AS s_trading_partner_name
668 , bh.shortlist_flag AS s_shortlist_flag
669 , bh.bid_status AS s_bid_status
670 , pv.end_date_active AS s_end_date_active
671 , ap.award_shipment_number as s_award_shipment_number
672 FROM pon_award_items_interface ap
673 , pon_auction_item_prices_all ai
674 , pon_bid_headers bh
675 , po_vendors pv
676 WHERE ap.batch_id = p_batch_id
677 AND ap.auction_header_id = ai.auction_header_id
678 AND ap.auction_line_number = ai.line_number
679 AND ap.bid_number = bh.bid_number (+)
680 AND bh.vendor_id = pv.vendor_id (+);
681
682
683 --
684 INSERT INTO PON_INTERFACE_ERRORS
685 ( interface_type
686 , column_name
687 , error_message_name
688 , error_value
689 , table_name
690 , batch_id
691 , worksheet_name
692 , worksheet_sequence_number
693 , entity_message_code
694 , interface_line_id
695 , request_id
696 , expiration_date
697 , created_by
698 , creation_date
699 , last_updated_by
700 , last_update_date
701 , last_update_login
702 , TOKEN1_NAME
703 , TOKEN1_VALUE
704 )
705 SELECT
706 'AWARDBID'
707 , fnd_message.get_string('PON','PON_AUC_LINE_TYPE')
708 , 'PON_AWARD_FIXED_PRICE'|| l_suffix
709 , pltt.line_type
710 , 'PON_AWARD_ITEMS_INTERFACE'
711 , paii.BATCH_ID
712 , paii.worksheet_name
713 , paii.worksheet_sequence_number
714 , 'PON_AUC_ITEMS' entity_message_code
715 , to_number(null) interface_line_id
716 , l_request_id
717 , l_exp_date
718 , l_user_id
719 , sysdate
720 , l_user_id
721 , sysdate
722 , l_login_id
723 , 'LINE_NUMBER'
724 , ai.document_disp_line_number
725 FROM PON_AWARD_ITEMS_INTERFACE paii,
726 pon_auction_item_prices_all ai,
727 pon_auction_headers_all ah,
728 po_line_types_tl pltt
729 WHERE paii.batch_id = p_batch_id
730 AND paii.award_status = 'Y'
731 AND ah.auction_header_id = paii.auction_header_id
732 AND ah.contract_type = 'STANDARD'
733 AND ai.line_number = paii.auction_line_number
734 AND ai.auction_header_id = paii.auction_header_id
735 AND ai.order_type_lookup_code = 'FIXED PRICE'
736 AND ai.line_type_id = pltt.line_type_id (+)
737 AND pltt.language (+) = userenv('LANG')
738 GROUP BY paii.batch_id,
739 paii.worksheet_name,
740 paii.worksheet_sequence_number,
741 ai.document_disp_line_number,
742 pltt.line_type
743 HAVING count(paii.award_status) >1;
744
745
746 END validateAwardBid;
747 --
748
749 PROCEDURE validate_complexwork(p_batch_id IN NUMBER
750 ,p_progress_payment_type IN VARCHAR2
751 ,p_contract_type IN VARCHAR2
752 ,p_advance_negotiable_flag IN VARCHAR2
753 ,p_recoupment_negotiable_flag IN VARCHAR2
754 )
755 IS
756 l_userid NUMBER;
757 l_loginid NUMBER;
758 l_exp_date DATE;
759 CURSOR l_proj_cursor IS
760 SELECT pipi.interface_line_id, pipi.document_disp_line_number, pipi.auction_line_number,
761 pipi.project_id, pipi.project_task_id, pipi.project_expenditure_type,
762 pipi.project_exp_organization_id, pipi.project_expenditure_item_date,
763 pipi.auction_header_id, pipi.interface_type
764 FROM PON_ITEM_PRICES_INTERFACE pipi
765 WHERE pipi.batch_id=p_batch_id
766 AND pipi.project_id IS NOT NULL
767 AND pipi.project_task_id IS NOT NULL
768 AND pipi.project_expenditure_type IS NOT NULL
769 AND pipi.project_exp_organization_id IS NOT NULL
770 AND pipi.project_expenditure_item_date IS NOT NULL;
771
772 BEGIN
773 l_userid := fnd_global.user_id;
774 l_loginid := fnd_global.login_id;
775 l_exp_date := SYSDATE+7;
776
777
778 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
779 FND_LOG.string(log_level => FND_LOG.level_statement,
780 module => g_module_prefix || 'validate_complexwork',
781 message => 'Entering the procedure validate_complexwork for batch id = '
782 || p_batch_id || ' ; p_progress_payment_type = '|| p_progress_payment_type
783 || ' ; p_contract_type = '|| p_contract_type|| ' ; p_advance_negotiable_flag = '
784 || p_advance_negotiable_flag|| ' ; p_recoupment_negotiable_flag = '|| p_recoupment_negotiable_flag);
785 END IF;
786 IF p_contract_type = 'CONTRACT' THEN
787 INSERT ALL
788 WHEN (p_progress_payment_type <> 'NONE' AND
789 line_type_id IS NOT NULL AND
790 NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
791 (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
792 OR po_outside_operation_flag = 'Y' THEN
793 INTO pon_interface_errors
794 (
795 interface_type, column_name, table_name, -- 1
796 error_value, error_message_name, batch_id, -- 2
797 interface_line_id, auction_header_id, line_number, -- 3
798 token1_name, token1_value, expiration_date, -- 4
799 created_by, creation_date, last_updated_by, -- 5
800 last_update_date, last_update_login -- 6
801 )
802 VALUES
803 (
804 interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
805 line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
806 interface_line_id, auction_header_id, s_line_number, -- 3
807 NULL, NULL, l_exp_date, -- 4
808 l_userid, SYSDATE, l_userid, -- 5
809 SYSDATE, l_loginid -- 6
810 )
811 SELECT
812 pipi.BATCH_ID,
813 pipi.INTERFACE_LINE_ID,
814 pipi.INTERFACE_TYPE,
815 pipi.AUCTION_HEADER_ID,
816 pipi.DOCUMENT_DISP_LINE_NUMBER,
817 pipi.PURCHASE_BASIS,
818 pipi.ORDER_TYPE_LOOKUP_CODE,
819 pipi.auction_line_number s_line_number,
820 plt.outside_operation_flag po_outside_operation_flag,
821 plt.line_type_id,
822 plt.line_type
823 FROM PON_ITEM_PRICES_INTERFACE pipi,
824 PO_LINE_TYPES plt
825 WHERE batch_id = p_batch_id
826 AND pipi.line_type_id = plt.line_type_id (+)
827 AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
828
829 ELSIF p_contract_type = 'STANDARD' THEN
830
831 INSERT ALL
832 WHEN S_CLM_INFO_FLAG = 'N' AND
833 retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
834 INTO pon_interface_errors
835 (
836 interface_type, column_name, table_name, -- 1
837 error_value, error_message_name, batch_id, -- 2
838 interface_line_id, auction_header_id, line_number, -- 3
839 token1_name, token1_value, expiration_date, -- 4
840 created_by, creation_date, last_updated_by, -- 5
841 last_update_date, last_update_login -- 6
842 )
843 VALUES
844 (
845 interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
846 retainage_rate_percent, 'PON_RTNG_RATE_WRONG', batch_id, -- 2
847 interface_line_id, auction_header_id, s_line_number, -- 3
848 NULL, NULL, l_exp_date, -- 4
849 l_userid, SYSDATE, l_userid, -- 5
850 SYSDATE, l_loginid -- 6
851 )
852
853 WHEN S_CLM_INFO_FLAG = 'N' AND
854 max_retainage_amount IS NOT NULL AND max_retainage_amount < 0 THEN
855 INTO pon_interface_errors
856 (
857 interface_type, column_name, table_name, -- 1
858 error_value, error_message_name, batch_id, -- 2
859 interface_line_id, auction_header_id, line_number, -- 3
860 token1_name, token1_value, expiration_date, -- 4
861 created_by, creation_date, last_updated_by, -- 5
862 last_update_date, last_update_login -- 6
863 )
864 VALUES
865 (
866 interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
867 max_retainage_amount , 'PON_MAX_RTNG_WRONG', batch_id, -- 2
868 interface_line_id, auction_header_id, s_line_number, -- 3
869 NULL, NULL, l_exp_date, -- 4
870 l_userid, SYSDATE, l_userid, -- 5
871 SYSDATE, l_loginid -- 6
872 )
873
874 WHEN S_CLM_INFO_FLAG = 'N' AND
875 advance_amount IS NOT NULL AND advance_amount < 0 THEN
876 INTO pon_interface_errors
877 (
878 interface_type, column_name, table_name, -- 1
879 error_value, error_message_name, batch_id, -- 2
880 interface_line_id, auction_header_id, line_number, -- 3
881 token1_name, token1_value, expiration_date, -- 4
882 created_by, creation_date, last_updated_by, -- 5
883 last_update_date, last_update_login -- 6
884 )
885 VALUES
886 (
887 interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
888 advance_amount, 'PON_ADV_AMT_WRONG', batch_id, -- 2
889 interface_line_id, auction_header_id, s_line_number, -- 3
890 NULL, NULL, l_exp_date, -- 4
891 l_userid, SYSDATE, l_userid, -- 5
892 SYSDATE, l_loginid -- 6
893 )
894
895 WHEN S_CLM_INFO_FLAG = 'N' AND
896 progress_pymt_rate_percent IS NOT NULL AND (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) then
897 INTO pon_interface_errors
898 (
899 interface_type, column_name, table_name, -- 1
900 error_value, error_message_name, batch_id, -- 2
901 interface_line_id, auction_header_id, line_number, -- 3
902 token1_name, token1_value, expiration_date, -- 4
903 created_by, creation_date, last_updated_by, -- 5
904 last_update_date, last_update_login -- 6
905 )
906 VALUES
907 (
908 interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
909 progress_pymt_rate_percent, 'PON_PROG_PYMT_RATE_WRONG', batch_id, -- 2
910 interface_line_id, auction_header_id, s_line_number, -- 3
911 NULL, NULL, l_exp_date, -- 4
912 l_userid, SYSDATE, l_userid, -- 5
913 SYSDATE, l_loginid -- 6
914 )
915 WHEN S_CLM_INFO_FLAG = 'N' AND
916 recoupment_rate_percent IS NOT NULL AND (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
917 INTO pon_interface_errors
918 (
919 interface_type, column_name, table_name, -- 1
920 error_value, error_message_name, batch_id, -- 2
921 interface_line_id, auction_header_id, line_number, -- 3
922 token1_name, token1_value, expiration_date, -- 4
923 created_by, creation_date, last_updated_by, -- 5
924 last_update_date, last_update_login -- 6
925 )
926 VALUES
927 (
928 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
929 recoupment_rate_percent, 'PON_RECOUP_RATE_WRONG', batch_id, -- 2
930 interface_line_id, auction_header_id, s_line_number, -- 3
931 NULL, NULL, l_exp_date, -- 4
932 l_userid, SYSDATE, l_userid, -- 5
933 SYSDATE, l_loginid -- 6
934 )
935
936 WHEN S_CLM_INFO_FLAG = 'N' AND
937 p_progress_payment_type = 'FINANCE' AND progress_pymt_rate_percent IS NULL THEN
938 INTO pon_interface_errors
939 (
940 interface_type, column_name, table_name, -- 1
941 error_value, error_message_name, batch_id, -- 2
942 interface_line_id, auction_header_id, line_number, -- 3
943 token1_name, token1_value, expiration_date, -- 4
944 created_by, creation_date, last_updated_by, -- 5
945 last_update_date, last_update_login -- 6
946 )
947 VALUES
948 (
949 interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
950 progress_pymt_rate_percent, 'PON_FIELD_MUST_BE_ENTERED', batch_id, -- 2
951 interface_line_id, auction_header_id, s_line_number, -- 3
952 NULL, NULL, l_exp_date, -- 4
953 l_userid, SYSDATE, l_userid, -- 5
954 SYSDATE, l_loginid -- 6
955 )
956
957
958 WHEN S_CLM_INFO_FLAG = 'N' AND
959 progress_pymt_rate_percent IS NOT NULL AND
960 recoupment_rate_percent IS NULL AND
961 p_recoupment_negotiable_flag = 'N' THEN
962 INTO pon_interface_errors
963 (
964 interface_type, column_name, table_name, -- 1
965 error_value, error_message_name, batch_id, -- 2
966 interface_line_id, auction_header_id, line_number, -- 3
967 token1_name, token1_value, expiration_date, -- 4
968 created_by, creation_date, last_updated_by, -- 5
969 last_update_date, last_update_login -- 6
970 )
971 VALUES
972 (
973 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
974 recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_PPRATE', batch_id, -- 2
975 interface_line_id, auction_header_id, s_line_number, -- 3
976 NULL, NULL, l_exp_date, -- 4
977 l_userid, SYSDATE, l_userid, -- 5
978 SYSDATE, l_loginid -- 6
979 )
980
981 WHEN S_CLM_INFO_FLAG = 'N' AND
982 ((advance_amount IS NOT NULL OR p_advance_negotiable_flag = 'Y') AND
983 (recoupment_rate_percent IS NULL AND p_recoupment_negotiable_flag = 'N')) THEN
984 INTO pon_interface_errors
985 (
986 interface_type, column_name, table_name, -- 1
987 error_value, error_message_name, batch_id, -- 2
988 interface_line_id, auction_header_id, line_number, -- 3
989 token1_name, token1_value, expiration_date, -- 4
990 created_by, creation_date, last_updated_by, -- 5
991 last_update_date, last_update_login -- 6
992 )
993 VALUES
994 (
995 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
996 recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_ADVAMT', batch_id, -- 2
997 interface_line_id, auction_header_id, s_line_number, -- 3
998 NULL, NULL, l_exp_date, -- 4
999 l_userid, SYSDATE, l_userid, -- 5
1000 SYSDATE, l_loginid -- 6
1001 )
1002
1003 WHEN S_CLM_INFO_FLAG = 'N' AND
1004 target_price IS NOT NULL AND advance_amount IS NOT NULL
1005 AND (advance_amount > nvl(s_quantity,1) * target_price) THEN
1006 INTO pon_interface_errors
1007 (
1008 interface_type, column_name, table_name, -- 1
1009 error_value, error_message_name, batch_id, -- 2
1010 interface_line_id, auction_header_id, line_number, -- 3
1011 token1_name, token1_value, expiration_date, -- 4
1012 created_by, creation_date, last_updated_by, -- 5
1013 last_update_date, last_update_login -- 6
1014 )
1015 VALUES
1016 (
1017 interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1018 advance_amount, 'PON_ADV_AMT_MORE', batch_id, -- 2
1019 interface_line_id, auction_header_id, s_line_number, -- 3
1020 NULL, NULL, l_exp_date, -- 4
1021 l_userid, SYSDATE, l_userid, -- 5
1022 SYSDATE, l_loginid -- 6
1023 )
1024
1025 WHEN S_CLM_INFO_FLAG = 'N' AND
1026 p_progress_payment_type <> 'NONE' AND recoupment_rate_percent IS NOT NULL
1027 AND advance_amount IS NOT NULL AND target_price IS NOT NULL
1028 AND (recoupment_rate_percent < (advance_amount * 100)/(nvl(s_quantity,1) * target_price)) THEN
1029 INTO pon_interface_errors
1030 (
1031 interface_type, column_name, table_name, -- 1
1032 error_value, error_message_name, batch_id, -- 2
1033 interface_line_id, auction_header_id, line_number, -- 3
1034 token1_name, token1_value, expiration_date, -- 4
1035 created_by, creation_date, last_updated_by, -- 5
1036 last_update_date, last_update_login -- 6
1037 )
1038 VALUES
1039 (
1040 interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1041 recoupment_rate_percent, 'PON_RECOUP_LESS_THAN_ADV', batch_id, -- 2
1042 interface_line_id, auction_header_id, s_line_number, -- 3
1043 NULL, NULL, l_exp_date, -- 4
1044 l_userid, SYSDATE, l_userid, -- 5
1045 SYSDATE, l_loginid -- 6
1046 )
1047
1048 WHEN S_CLM_INFO_FLAG = 'N' AND
1049 (p_progress_payment_type <> 'NONE' AND
1050 line_type_id IS NOT NULL AND
1051 NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
1052 (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
1053 OR po_outside_operation_flag = 'Y' THEN
1054 INTO pon_interface_errors
1055 (
1056 interface_type, column_name, table_name, -- 1
1057 error_value, error_message_name, batch_id, -- 2
1058 interface_line_id, auction_header_id, line_number, -- 3
1059 token1_name, token1_value, expiration_date, -- 4
1060 created_by, creation_date, last_updated_by, -- 5
1061 last_update_date, last_update_login -- 6
1062 )
1063 VALUES
1064 (
1065 interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1066 line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
1067 interface_line_id, auction_header_id, s_line_number, -- 3
1068 NULL, NULL, l_exp_date, -- 4
1069 l_userid, SYSDATE, l_userid, -- 5
1070 SYSDATE, l_loginid -- 6
1071 )
1072
1073 WHEN S_CLM_INFO_FLAG = 'N' AND line_origination_code <> 'REQUISITION' AND project_number IS NOT NULL AND pro_project_id IS NULL THEN
1074 INTO pon_interface_errors
1075 (
1076 interface_type, column_name, table_name, -- 1
1077 error_value, error_message_name, batch_id, -- 2
1078 interface_line_id, auction_header_id, line_number, -- 3
1079 token1_name, token1_value, expiration_date, -- 4
1080 created_by, creation_date, last_updated_by, -- 5
1081 last_update_date, last_update_login -- 6
1082 )
1083 VALUES
1084 (
1085 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1086 project_number, 'PON_PROJ_NUM_INVALID', batch_id, -- 2
1087 interface_line_id, auction_header_id, s_line_number, -- 3
1088 NULL, NULL, l_exp_date, -- 4
1089 l_userid, SYSDATE, l_userid, -- 5
1090 SYSDATE, l_loginid -- 6
1091 )
1092
1093 WHEN S_CLM_INFO_FLAG = 'N' AND
1094 line_origination_code <> 'REQUISITION'
1095 AND pro_project_id IS NOT NULL
1096 AND project_task_number IS NOT NULL
1097 AND NOT EXISTS (SELECT 1
1098 FROM PA_TASKS_EXPEND_V task
1099 WHERE task.project_id = pro_project_id AND task.task_number = project_task_number) THEN
1100 INTO pon_interface_errors
1101 (
1102 interface_type, column_name, table_name, -- 1
1103 error_value, error_message_name, batch_id, -- 2
1104 interface_line_id, auction_header_id, line_number, -- 3
1105 token1_name, token1_value, expiration_date, -- 4
1106 created_by, creation_date, last_updated_by, -- 5
1107 last_update_date, last_update_login -- 6
1108 )
1109 VALUES
1110 (
1111 interface_type, fnd_message.get_string('PON','PON_AUCTS_TASK'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1112 project_task_number, 'PON_PROJ_TASK_INVALID', batch_id, -- 2
1113 interface_line_id, auction_header_id, s_line_number, -- 3
1114 NULL, NULL, l_exp_date, -- 4
1115 l_userid, SYSDATE, l_userid, -- 5
1116 SYSDATE, l_loginid -- 6
1117 )
1118
1119 WHEN S_CLM_INFO_FLAG = 'N' AND
1120 line_origination_code <> 'REQUISITION'
1121 AND pro_project_id IS NOT NULL
1122 AND project_task_number IS NOT NULL
1123 AND project_award_number IS NOT NULL
1124 AND NOT EXISTS (SELECT 1
1125 FROM GMS_AWARDS_BASIC_V award,
1126 PA_TASKS_EXPEND_V task
1127 WHERE award.project_id = pro_project_id
1128 AND task.task_number = project_task_number
1129 AND award.task_id = task.task_id
1130 AND task.project_id = pro_project_id) THEN
1131 INTO pon_interface_errors
1132 (
1133 interface_type, column_name, table_name, -- 1
1134 error_value, error_message_name, batch_id, -- 2
1135 interface_line_id, auction_header_id, line_number, -- 3
1136 token1_name, token1_value, expiration_date, -- 4
1137 created_by, creation_date, last_updated_by, -- 5
1138 last_update_date, last_update_login -- 6
1139 )
1140 VALUES
1141 (
1142 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1143 project_award_number, 'PON_PROJ_AWARD_INVALID', batch_id, -- 2
1144 interface_line_id, auction_header_id, s_line_number, -- 3
1145 NULL, NULL, l_exp_date, -- 4
1146 l_userid, SYSDATE, l_userid, -- 5
1147 SYSDATE, l_loginid -- 6
1148 )
1149
1150 WHEN S_CLM_INFO_FLAG = 'N' AND
1151 line_origination_code <> 'REQUISITION' AND project_exp_organization_name IS NOT NULL
1152 AND porg_proj_exp_organization_id IS NULL THEN
1153 INTO pon_interface_errors
1154 (
1155 interface_type, column_name, table_name, -- 1
1156 error_value, error_message_name, batch_id, -- 2
1157 interface_line_id, auction_header_id, line_number, -- 3
1158 token1_name, token1_value, expiration_date, -- 4
1159 created_by, creation_date, last_updated_by, -- 5
1160 last_update_date, last_update_login -- 6
1161 )
1162 VALUES
1163 (
1164 interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1165 project_exp_organization_name,'PON_PROJ_EXPORG_INVALID', batch_id, -- 2
1166 interface_line_id, auction_header_id, s_line_number, -- 3
1167 NULL, NULL, l_exp_date, -- 4
1168 l_userid, SYSDATE, l_userid, -- 5
1169 SYSDATE, l_loginid -- 6
1170 )
1171 WHEN S_CLM_INFO_FLAG = 'N' AND
1172 s_project_exp_type IS NOT NULL
1173 AND NOT EXISTS (SELECT 1
1174 FROM pa_expenditure_types_expend_v exptype
1175 WHERE system_linkage_function = 'VI'
1176 AND exptype.expenditure_type = s_project_exp_type
1177 AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
1178 AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
1179 AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
1180 AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
1181
1182 INTO pon_interface_errors
1183 (
1184 interface_type, column_name, table_name, -- 1
1185 error_value, error_message_name, batch_id, -- 2
1186 interface_line_id, auction_header_id, line_number, -- 3
1187 token1_name, token1_value, expiration_date, -- 4
1188 created_by, creation_date, last_updated_by, -- 5
1189 last_update_date, last_update_login -- 6
1190 )
1191 VALUES
1192 (
1193 interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1194 s_project_exp_type, 'PON_PROJ_EXPTYPE_INVALID', batch_id, -- 2
1195 interface_line_id, auction_header_id, s_line_number, -- 3
1196 NULL, NULL, l_exp_date, -- 4
1197 l_userid, SYSDATE, l_userid, -- 5
1198 SYSDATE, l_loginid -- 6
1199 )
1200
1201 WHEN S_CLM_INFO_FLAG = 'N' AND
1202 pro_project_id IS NOT NULL
1203 AND project_award_number IS NULL
1204 AND PON_NEGOTIATION_PUBLISH_PVT.IS_PROJECT_SPONSORED(pro_project_id) = 'Y' THEN
1205 INTO pon_interface_errors
1206 (
1207 interface_type, column_name, table_name, -- 1
1208 error_value, error_message_name, batch_id, -- 2
1209 interface_line_id, auction_header_id, line_number, -- 3
1210 token1_name, token1_value, expiration_date, -- 4
1211 created_by, creation_date, last_updated_by, -- 5
1212 last_update_date, last_update_login -- 6
1213 )
1214 VALUES
1215 (
1216 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1217 project_award_number, 'PON_PROJ_AWARD_NULL', batch_id, -- 2
1218 interface_line_id, auction_header_id, s_line_number, -- 3
1219 NULL, NULL, l_exp_date, -- 4
1220 l_userid, SYSDATE, l_userid, -- 5
1221 SYSDATE, l_loginid -- 6
1222 )
1223 WHEN S_CLM_INFO_FLAG = 'N' AND
1224 line_origination_code <> 'REQUISITION' AND
1225 ((project_number IS NULL OR project_task_number IS NULL OR s_project_exp_type IS NULL
1226 OR project_exp_organization_name IS NULL OR project_expenditure_item_date IS NULL) AND
1227 (project_number IS NOT NULL OR project_task_number IS NOT NULL OR s_project_exp_type IS NOT NULL
1228 OR project_exp_organization_name IS NOT NULL OR project_expenditure_item_date IS NOT NULL)) THEN
1229 INTO pon_interface_errors
1230 (
1231 interface_type, column_name, table_name, -- 1
1232 error_value, error_message_name, batch_id, -- 2
1233 interface_line_id, auction_header_id, line_number, -- 3
1234 token1_name, token1_value, expiration_date, -- 4
1235 created_by, creation_date, last_updated_by, -- 5
1236 last_update_date, last_update_login -- 6
1237 )
1238 VALUES
1239 (
1240 interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1241 NULL, 'PON_PROJ_INFO_INCOMPLETE', batch_id, -- 2
1242 interface_line_id, auction_header_id, s_line_number, -- 3
1243 NULL, NULL, l_exp_date, -- 4
1244 l_userid, SYSDATE, l_userid, -- 5
1245 SYSDATE, l_loginid -- 6
1246 )
1247
1248 WHEN S_CLM_INFO_FLAG = 'N' AND
1249 work_approver_user_name IS NOT NULL
1250 AND NOT EXISTS (SELECT 1
1251 FROM PER_WORKFORCE_CURRENT_X peo,
1252 FND_USER fu
1253 WHERE fu.user_name = work_approver_user_name
1254 AND fu.employee_id = peo.person_id
1255 AND SYSDATE >= nvl(fu.start_date, SYSDATE)
1256 AND SYSDATE <= nvl(fu.end_date, SYSDATE) )
1257 THEN
1258 INTO pon_interface_errors
1259 (
1260 interface_type, column_name, table_name, -- 1
1261 error_value, error_message_name, batch_id, -- 2
1262 interface_line_id, auction_header_id, line_number, -- 3
1263 token1_name, token1_value, expiration_date, -- 4
1264 created_by, creation_date, last_updated_by, -- 5
1265 last_update_date, last_update_login -- 6
1266 )
1267 VALUES
1268 (
1269 interface_type, fnd_message.get_string('PON','PON_DEFAULT_OWNER'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1270 NULL, 'PON_LIN_OWNER_INVALID', batch_id, -- 2
1271 interface_line_id, auction_header_id, s_line_number, -- 3
1272 NULL, NULL, l_exp_date, -- 4
1273 l_userid, SYSDATE, l_userid, -- 5
1274 SYSDATE, l_loginid -- 6
1275 )
1276 SELECT
1277 pipi.BATCH_ID,
1278 pipi.INTERFACE_LINE_ID,
1279 pipi.INTERFACE_TYPE,
1280 pipi.AUCTION_HEADER_ID,
1281 pipi.DOCUMENT_DISP_LINE_NUMBER,
1282 pipi.ADVANCE_AMOUNT,
1283 pipi.RECOUPMENT_RATE_PERCENT,
1284 pipi.PROGRESS_PYMT_RATE_PERCENT,
1285 pipi.RETAINAGE_RATE_PERCENT,
1286 pipi.MAX_RETAINAGE_AMOUNT,
1287 pipi.WORK_APPROVER_USER_NAME,
1288 pipi.PROJECT_NUMBER,
1289 pipi.PROJECT_TASK_NUMBER,
1290 pipi.PROJECT_AWARD_NUMBER,
1291 pipi.PROJECT_EXPENDITURE_TYPE s_project_exp_type,
1292 pipi.PROJECT_EXP_ORGANIZATION_NAME,
1293 pipi.PROJECT_EXPENDITURE_ITEM_DATE,
1294 pipi.PURCHASE_BASIS,
1295 pipi.ORDER_TYPE_LOOKUP_CODE,
1296 NVL(pipi.LINE_ORIGINATION_CODE,'-9997') LINE_ORIGINATION_CODE,
1297 pipi.auction_line_number s_line_number,
1298 pipi.target_price,
1299 pipi.quantity s_quantity,
1300 pro.project_id pro_project_id,
1301 porg.organization_id porg_proj_exp_organization_id,
1302 plt.outside_operation_flag po_outside_operation_flag,
1303 plt.line_type_id,
1304 plt.line_type,
1305 Nvl(pipi.CLM_INFO_FLAG, 'N') S_CLM_INFO_FLAG
1306 FROM PON_ITEM_PRICES_INTERFACE pipi,
1307 PA_PROJECTS_EXPEND_V pro,
1308 PA_ORGANIZATIONS_EXPEND_V porg,
1309 PO_LINE_TYPES plt
1310 WHERE batch_id = p_batch_id
1311 AND pipi.project_number = pro.project_number (+)
1312 AND pipi.project_exp_organization_name = porg.name(+)
1313 AND pipi.line_type_id = plt.line_type_id (+)
1314 AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
1315
1316 --Derive id columns and update the interface table
1317 UPDATE PON_ITEM_PRICES_INTERFACE pipi1
1318 SET (PROJECT_ID, PROJECT_TASK_ID, PROJECT_AWARD_ID, PROJECT_EXP_ORGANIZATION_ID) =
1319 (SELECT pro.project_id, task.task_id, award.award_id, porg.organization_id
1320 FROM PA_PROJECTS_ALL pro,
1321 PA_TASKS_EXPEND_V task,
1322 GMS_AWARDS_ALL award,
1323 HR_ALL_ORGANIZATION_UNITS porg,
1324 PON_ITEM_PRICES_INTERFACE pipi
1325 WHERE pipi.project_number = pro.segment1
1326 AND pipi.project_task_number = task.task_number
1327 AND pro.project_id = task.project_id
1328 AND pipi.project_award_number = award.award_number(+)
1329 AND pipi.project_exp_organization_name = porg.name
1330 AND pipi.batch_id = pipi1.batch_id
1331 AND pipi.interface_line_id = pipi1.interface_line_id)
1332 WHERE pipi1.batch_id = p_batch_id;
1333
1334 UPDATE PON_ITEM_PRICES_INTERFACE pipi
1335 SET (WORK_APPROVER_USER_ID) =
1336 (SELECT fu.user_id
1337 FROM FND_USER fu
1338 WHERE pipi.work_approver_user_name = fu.user_name)
1339 WHERE batch_id = p_batch_id;
1340
1341 END IF; -- End of if p_contract_type = 'STANDARD'
1342
1343 --Validate project fields with PATC
1344 FOR l_proj_record IN l_proj_cursor LOOP
1345 PON_NEGOTIATION_PUBLISH_PVT.VALIDATE_PROJECTS_DETAILS (
1346 p_project_id => l_proj_record.project_id,
1347 p_task_id => l_proj_record.project_task_id,
1348 p_expenditure_date => l_proj_record.project_expenditure_item_date,
1349 p_expenditure_type => l_proj_record.project_expenditure_type,
1350 p_expenditure_org => l_proj_record.project_exp_organization_id,
1351 p_person_id => null,
1352 p_auction_header_id => l_proj_record.auction_header_id,
1353 p_line_number => l_proj_record.auction_line_number,
1354 p_document_disp_line_number => l_proj_record.document_disp_line_number,
1355 p_payment_id => null,
1356 p_interface_line_id => l_proj_record.interface_line_id,
1357 p_payment_display_number => null,
1358 p_batch_id => p_batch_id,
1359 p_table_name => 'PON_ITEM_PRICES_INTERFACE',
1360 p_interface_type => l_proj_record.interface_type,
1361 p_entity_type => null,
1362 p_called_from => 'LINES_SP');
1363 END LOOP;
1364
1365
1366 END validate_complexwork;
1367
1368
1369 -- The procedure loads necessary po style settings for validation.
1370 -- Current usage including line type validation in line upload.
1371
1372 -- Params: p_batch_id batch id (used to get auction header id)
1373 -- x_po_style_id po style id for the negotiation
1374 -- x_line_type_restriction the associated po style setting (line type)
1375
1376 PROCEDURE get_po_style_settings (p_batch_id IN NUMBER,
1377 x_po_style_id OUT NOCOPY NUMBER,
1378 x_line_type_restriction OUT NOCOPY VARCHAR2) IS
1379 l_dummy1 VARCHAR2(240);
1380 l_dummy2 VARCHAR2(240);
1381 l_dummy3 VARCHAR2(30);
1382 l_dummy4 VARCHAR2(30);
1383 l_dummy5 VARCHAR2(1);
1384 l_dummy6 VARCHAR2(1);
1385 l_dummy7 VARCHAR2(1);
1386 l_dummy8 VARCHAR2(1);
1387 l_dummy9 VARCHAR2(1);
1388 l_dummy10 VARCHAR2(1);
1389
1390
1391 BEGIN
1392
1393 BEGIN
1394
1395 select ah.po_style_id
1396 into x_po_style_id
1397 from pon_auction_headers_all ah,
1398 pon_item_prices_interface ipi
1399 where ipi.batch_id = p_batch_id
1400 and ipi.auction_header_id = ah.auction_header_id
1401 and rownum = 1;
1402
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 x_po_style_id := NULL;
1406 END;
1407
1408
1409 -- invoke po api to get settings for the po style
1410 IF ( x_po_style_id is not null) THEN -- RFI has no po style, so add the check
1411
1412 BEGIN
1413 PO_DOC_STYLE_GRP.GET_DOCUMENT_STYLE_SETTINGS(
1414 P_API_VERSION => '1.0',
1415 P_STYLE_ID => x_po_style_id,
1416 X_STYLE_NAME => l_dummy1,
1417 X_STYLE_DESCRIPTION => l_dummy2,
1418 X_STYLE_TYPE => l_dummy3,
1419 X_STATUS => l_dummy4,
1420 X_ADVANCES_FLAG => l_dummy5,
1421 X_RETAINAGE_FLAG => l_dummy6,
1422 X_PRICE_BREAKS_FLAG => l_dummy7,
1423 X_PRICE_DIFFERENTIALS_FLAG => l_dummy8,
1424 X_PROGRESS_PAYMENT_FLAG=> l_dummy9,
1425 X_CONTRACT_FINANCING_FLAG=> l_dummy10,
1426 X_LINE_TYPE_ALLOWED => x_line_type_restriction);
1427
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 x_line_type_restriction := 'ALL'; -- no restriction
1431 END;
1432
1433 END IF;
1434
1435 END get_po_style_settings;
1436
1437
1438 --
1439 PROCEDURE validate (p_source IN VARCHAR2,
1440 p_batch_Id IN NUMBER,
1441 p_doctype_Id IN NUMBER,
1442 p_user_Id IN NUMBER,
1443 p_trading_partner_id IN NUMBER,
1444 p_trading_partner_contact_id IN NUMBER,
1445 p_language IN VARCHAR2,
1446 p_contract_type IN VARCHAR2,
1447 p_global_flag IN VARCHAR2,
1448 p_org_id IN NUMBER) IS
1449 --
1450 CURSOR C_ship_to (c_batch_id NUMBER) IS
1451 SELECT
1452 INTERFACE_LINE_ID
1453 , SHIP_TO_LOCATION_ID
1454 FROM PON_ITEM_PRICES_INTERFACE
1455 WHERE BATCH_ID = c_batch_id
1456 AND SHIP_TO_LOCATION_ID <> -1
1457 AND SHIP_TO_LOCATION <> 'SHIP_NONE_ENTERED';
1458 --
1459 l_country VARCHAR2(60);
1460 l_address1 VARCHAR2(240);
1461 l_address2 VARCHAR2(240);
1462 l_address3 VARCHAR2(240);
1463 l_address4 VARCHAR2(240);
1464 l_city VARCHAR2(60);
1465 l_postal_code VARCHAR2(60);
1466 l_state VARCHAR2(60);
1467 l_province VARCHAR2(60);
1468 l_ship_site_use_type_id NUMBER;
1469 l_transaction_type VARCHAR2(25);
1470 --
1471 -- params to projects api
1472 --l_project_number VARCHAR2(25);
1473 --l_project_name VARCHAR2(100);
1474 --l_project_id NUMBER;
1475 --l_task_number VARCHAR2(25);
1476 --l_task_name VARCHAR2(100);
1477 --l_task_id NUMBER;
1478 l_return_status VARCHAR2(10);
1479 l_msg_count NUMBER;
1480 l_msg_data VARCHAR2(100);
1481 l_return_status2 VARCHAR2(10);
1482 l_line_type_id NUMBER;
1483 l_line_type VARCHAR2(25);
1484 l_line_lookup_code VARCHAR2(25);
1485 l_deafult_line_type_category VARCHAR2(122);
1486 l_default_line_type_uom VARCHAR2(25);
1487 l_amount_based_uom VARCHAR2(25);
1488 l_amount_based_unit_of_measure VARCHAR2(25);
1489 l_item_number_delimiter varchar2(1);
1490 l_inventory_org_id number;
1491 l_progress_payment_type PON_AUCTION_HEADERS_ALL.progress_payment_type%TYPE;
1492 l_advance_negotiable_flag PON_AUCTION_HEADERS_ALL.advance_negotiable_flag%TYPE;
1493 l_recoupment_negotiable_flag PON_AUCTION_HEADERS_ALL.recoupment_negotiable_flag%TYPE;
1494 l_po_style_id PON_AUCTION_HEADERS_ALL.po_style_id%TYPE;
1495 l_line_type_restriction VARCHAR2(30);
1496 l_auction_header_id NUMBER;
1497 l_auction_round_number NUMBER;
1498
1499 -- clm clin/slin change
1500 l_is_fed VARCHAR2(1);
1501 l_doctype_id NUMBER;
1502
1503 l_sol_line_count NUMBER;
1504 l_int_line_count NUMBER;
1505 l_price_break_type VARCHAR2(4000);
1506 l_price_break_neg_flag VARCHAR2(4000);
1507 l_price_tiers_indicator PON_AUCTION_HEADERS_ALL.price_tiers_indicator%TYPE;
1508 l_cumulative VARCHAR2(4000);
1509 l_noncumulative VARCHAR2(4000);
1510 l_required VARCHAR2(4000);
1511 l_optional VARCHAR2(4000);
1512 l_none VARCHAR2(4000);
1513
1514
1515 --
1516 BEGIN
1517
1518 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
1519 FND_LOG.string(log_level => FND_LOG.level_statement,
1520 module => g_module_prefix || 'validateAwardBid',
1521 message => 'Entering the procedure validate for batch id = ' ||
1522 p_batch_id || ' ; p_source = '|| p_source|| ' ; p_doctype_Id = '|| p_doctype_Id
1523 || ' ; p_user_Id = '|| p_user_Id|| ' ; p_trading_partner_id = '|| p_trading_partner_id
1524 || ' ; p_trading_partner_contact_id = '|| p_trading_partner_contact_id|| ' ; p_language = '
1525 || p_language|| ' ; p_contract_type = '|| p_contract_type
1526 || ' ; p_global_flag = '|| p_global_flag|| ' ; p_org_id = '|| p_org_id);
1527 END IF;
1528 -- DBMS_OUTPUT.PUT_LINE('> validate()');
1529 --
1530 -- AWARDBID specific validation
1531 IF p_source = 'AWARDBID' THEN
1532 validateAwardBid(p_batch_id, g_txt_upload_mode);
1533 RETURN;
1534 END IF;
1535 --
1536 --
1537 --
1538
1539 -- the rest of the validation only for importItems not for importBids
1540 IF p_source <> 'SBID' THEN
1541 --
1542 -- Category name has reference to category id --
1543 -- AUCTION and BID --
1544 --
1545 -- First of all get the default UOM for the amount based line type.
1546 get_default_uom(p_language,p_trading_partner_id,l_amount_based_uom,l_amount_based_unit_of_measure);
1547
1548 -- Retrive the inventory org id
1549 get_inventory_org_id(p_org_id, l_inventory_org_id);
1550
1551 -- load po style settings
1552 get_po_style_settings(p_batch_id, l_po_style_id, l_line_type_restriction);
1553
1554 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
1555 FND_LOG.string(log_level => FND_LOG.level_statement,
1556 module => g_module_prefix || 'validate',
1557 message => 'In procedure validate l_amount_based_uom = ' || l_amount_based_uom
1558 || ' ; l_amount_based_unit_of_measure = '|| l_amount_based_unit_of_measure
1559 || ' ; l_inventory_org_id = '|| l_inventory_org_id|| ' ; l_po_style_id = '
1560 || l_po_style_id|| ' ; l_line_type_restriction = '|| l_line_type_restriction);
1561 END IF;
1562
1563
1564 -- If a negotiation document allows a buyer to select whether price and quantity are applicable for a line,
1565 -- and the buyer specifies that it is NOT applicable, but still enters a quantity and price(s), we need to
1566 -- report these errors
1567
1568
1569 select auction_header_id
1570 into l_auction_header_id
1571 from pon_item_prices_interface
1572 where batch_id = p_batch_id
1573 and auction_header_id is not null
1574 and rownum =1;
1575
1576 SELECT nvl(auction_round_number,0),
1577 progress_payment_type,
1578 advance_negotiable_flag,
1579 recoupment_negotiable_flag
1580 INTO l_auction_round_number,
1581 l_progress_payment_type,
1582 l_advance_negotiable_flag,
1583 l_recoupment_negotiable_flag
1584 FROM
1585 pon_auction_headers_all
1586 WHERE
1587 auction_header_id = l_auction_header_id;
1588
1589 IF (FND_LOG.level_statement>= FND_LOG.g_current_runtime_level) THEN
1590 FND_LOG.string(log_level => FND_LOG.level_statement,
1591 module => g_module_prefix || 'validate',
1592 message => 'In procedure validate l_auction_round_number = '
1593 || l_auction_round_number || ' ; l_progress_payment_type = '
1594 || l_progress_payment_type|| ' ; l_advance_negotiable_flag = '
1595 || l_advance_negotiable_flag|| ' ; l_recoupment_negotiable_flag = '
1596 || l_recoupment_negotiable_flag);
1597 END IF;
1598
1599 if(l_auction_round_number > 1) then
1600 pon_cp_intrfac_to_transaction.default_prev_round_amend_lines(l_auction_header_id,p_batch_id);
1601 END if;
1602
1603 --clm clin/slin change
1604 BEGIN
1605
1606 SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = l_auction_header_id;
1607
1608 SELECT
1609 pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
1610 FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
1611 , PON_AUC_BIZRULES pon_auc_bizrules
1612 WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
1613 AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
1614 AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
1615
1616 -- maximum allowed clin number is 9999
1617 -- so we wont accept more than 9999 clins
1618 IF(l_is_fed = 'Y') THEN
1619 SELECT Count(*) INTO l_sol_line_count FROM pon_auction_item_prices_all
1620 WHERE auction_header_id = l_auction_header_id
1621 AND group_line_id IS NULL;
1622
1623 SELECT Count(*) INTO l_int_line_count FROM pon_item_prices_interface
1624 WHERE batch_id = p_batch_id
1625 AND group_line_id IS NULL;
1626
1627 IF( (l_sol_line_count+l_int_line_count) > 9999 ) THEN
1628 insert into PON_INTERFACE_ERRORS
1629 (interface_type,
1630 column_name,
1631 error_value,
1632 error_message_name,
1633 table_name,
1634 batch_id,
1635 interface_line_id)
1636 select interface_type,
1637 fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
1638 'Number of clins can not exceed 9999',
1639 'PON_TOO_MANY_CLINS',
1640 'PON_ITEM_PRICES_INTERFACE',
1641 batch_id,
1642 interface_line_id
1643 from pon_item_prices_interface
1644 where batch_id = p_batch_id
1645 AND ROWNUM < 2;
1646
1647 END IF;
1648
1649 /*
1650 bug 9818972
1651 Number of priced slins can't exceed 576 (i.e. 24 * 24) and
1652 number of info slins can't exceed 99 for a clin)
1653 */
1654 FOR more_slins IN (SELECT group_line_id,clm_info_flag FROM pon_item_prices_interface WHERE batch_id = p_batch_id
1655 AND group_line_id IS NOT NULL
1656 GROUP BY clm_info_flag,group_line_id
1657 HAVING Count(*) > Decode(nvl(clm_info_flag,'N'),'Y',99,'N',576)) LOOP
1658
1659 insert into PON_INTERFACE_ERRORS
1660 (interface_type,
1661 column_name,
1662 error_value,
1663 error_message_name,
1664 table_name,
1665 batch_id,
1666 interface_line_id)
1667 select interface_type,
1668 fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
1669 'Slins',
1670 'PON_TOO_MANY_SLINS',
1671 'PON_ITEM_PRICES_INTERFACE',
1672 batch_id,
1673 interface_line_id
1674 from pon_item_prices_interface
1675 where batch_id = p_batch_id
1676 AND auction_line_number = more_slins.group_line_id;
1677
1678 END LOOP;
1679
1680 END IF; -- end l_is_fed
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 NULL;
1685 END; --end of clm clin/slin change
1686
1687
1688 IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
1689
1690 IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
1691 -- Quantity should be empty
1692 insert into PON_INTERFACE_ERRORS
1693 (interface_type,
1694 column_name,
1695 error_value,
1696 error_message_name,
1697 table_name,
1698 batch_id,
1699 interface_line_id)
1700 select interface_type,
1701 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
1702 quantity,
1703 'PON_AUCTS_PR_QT_NOT_APPLY',
1704 'PON_ITEM_PRICES_INTERFACE',
1705 batch_id,
1706 interface_line_id
1707 from pon_item_prices_interface
1708 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1709 quantity is not null and
1710 batch_id = p_batch_id;
1711 END IF;
1712
1713 IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
1714 -- UOM should be empty
1715 insert into PON_INTERFACE_ERRORS
1716 (interface_type,
1717 column_name,
1718 error_value,
1719 error_message_name,
1720 table_name,
1721 batch_id,
1722 interface_line_id)
1723 select interface_type,
1724 fnd_message.get_string('PON','PON_AUCTION_UOM'),
1725 unit_of_measure,
1726 'PON_AUCTS_PR_QT_NOT_APPLY',
1727 'PON_ITEM_PRICES_INTERFACE',
1728 batch_id,
1729 interface_line_id
1730 from pon_item_prices_interface
1731 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1732 (unit_of_measure is not null and unit_of_measure <> 'UOM_NONE_ENTERED') and
1733 batch_id = p_batch_id;
1734 END IF;
1735
1736 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
1737 -- Target Price should be empty
1738 insert into PON_INTERFACE_ERRORS
1739 (interface_type,
1740 column_name,
1741 error_value,
1742 error_message_name,
1743 table_name,
1744 batch_id,
1745 interface_line_id)
1746 select interface_type,
1747 fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
1748 target_price,
1749 'PON_AUCTS_PR_QT_NOT_APPLY',
1750 'PON_ITEM_PRICES_INTERFACE',
1751 batch_id,
1752 interface_line_id
1753 from pon_item_prices_interface
1754 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1755 target_price is not null and
1756 batch_id = p_batch_id;
1757 END IF;
1758
1759 IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
1760 -- Current Price should be empty
1761 insert into PON_INTERFACE_ERRORS
1762 (interface_type,
1763 column_name,
1764 error_value,
1765 error_message_name,
1766 table_name,
1767 batch_id,
1768 interface_line_id)
1769 select interface_type,
1770 fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
1771 current_price,
1772 'PON_AUCTS_PR_QT_NOT_APPLY',
1773 'PON_ITEM_PRICES_INTERFACE',
1774 batch_id,
1775 interface_line_id
1776 from pon_item_prices_interface
1777 where nvl(price_and_quantity_apply, 'Y') = 'N' and
1778 current_price is not null and
1779 batch_id = p_batch_id;
1780 END IF;
1781
1782 END IF;
1783
1784 -- For Line Type Check.
1785 insert into PON_INTERFACE_ERRORS
1786 (interface_type,
1787 column_name,
1788 error_message_name,
1789 table_name,
1790 batch_id,
1791 interface_line_id)
1792 select interface_type,
1793 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1794 'PON_FIELD_MUST_BE_ENTERED',
1795 'PON_ITEM_PRICES_INTERFACE',
1796 batch_id,
1797 interface_line_id
1798 from pon_item_prices_interface
1799 where line_type = 'LINE_TYPE_NONE_ENTERED'
1800 and batch_id = p_batch_id
1801 and group_type <> 'GROUP'
1802 AND Nvl(clm_info_flag,'N') <> 'Y';
1803
1804 --update the pon_item_prices_interface table
1805
1806 update pon_item_prices_interface p1
1807 set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1808 (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1809 FROM po_line_types_vl po2 WHERE upper(p1.line_type) = upper(po2.line_type(+))
1810 and (po2.inactive_date is null or po2.inactive_date > sysdate))
1811 where batch_id = p_batch_id
1812 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1813 AND Nvl(clm_info_flag,'N') <> 'Y'
1814 and line_type is not null;
1815
1816
1817 insert into PON_INTERFACE_ERRORS
1818 (interface_type,
1819 column_name,
1820 error_value,
1821 error_message_name,
1822 table_name,
1823 batch_id,
1824 interface_line_id)
1825 select interface_type,
1826 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1827 line_type,
1828 'PON_AUC_LINE_TYPE_ERR',
1829 'PON_ITEM_PRICES_INTERFACE',
1830 batch_id,
1831 interface_line_id
1832 from pon_item_prices_interface
1833 where line_type_id is null
1834 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1835 and batch_id = p_batch_id
1836 AND Nvl(clm_info_flag,'N') <> 'Y';
1837
1838
1839 -- perform the following check if po style has restricted line types
1840 if (l_line_type_restriction = 'SPECIFIED') then
1841 insert into PON_INTERFACE_ERRORS
1842 (interface_type,
1843 column_name,
1844 error_value,
1845 error_message_name,
1846 table_name,
1847 batch_id,
1848 interface_line_id)
1849 select interface_type,
1850 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1851 line_type,
1852 'PON_AUC_LINE_TYPE_ERR',
1853 'PON_ITEM_PRICES_INTERFACE',
1854 batch_id,
1855 interface_line_id
1856 from pon_item_prices_interface
1857 where line_type_id not in ( select line_type_id
1858 from po_style_enabled_line_types
1859 where style_id = l_po_style_id)
1860 and line_type_id is not null
1861 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1862 and batch_id = p_batch_id;
1863 end if;
1864
1865 -- Bug 4722286.
1866 -- perform the following check if po style has restricted purchase basis
1867
1868 if (l_line_type_restriction = 'ALL') then
1869 insert into PON_INTERFACE_ERRORS
1870 (interface_type,
1871 column_name,
1872 error_value,
1873 error_message_name,
1874 table_name,
1875 batch_id,
1876 interface_line_id)
1877 select interface_type,
1878 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1879 line_type,
1880 'PON_AUC_LINE_TYPE_ERR',
1881 'PON_ITEM_PRICES_INTERFACE',
1882 batch_id,
1883 interface_line_id
1884 from pon_item_prices_interface
1885 where purchase_basis not in ( select purchase_basis
1886 from po_style_enabled_pur_bases
1887 where style_id = l_po_style_id)
1888 and line_type_id is not null
1889 and line_type <> 'LINE_TYPE_NONE_ENTERED'
1890 and batch_id = p_batch_id
1891 AND Nvl(clm_info_flag,'N') <> 'Y';
1892 end if;
1893
1894 --
1895 -- Begin major services lines validation
1896 --
1897 -- First, make sure no services lines
1898 -- are on a standard outcome document
1899 --
1900 IF(p_contract_type = 'STANDARD') THEN
1901 --
1902 INSERT INTO pon_interface_errors
1903 (interface_type,
1904 column_name,
1905 error_value,
1906 error_message_name,
1907 table_name,
1908 batch_id,
1909 interface_line_id)
1910 SELECT interface_type,
1911 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1912 line_type,
1913 'PON_STANDARD_LINE_TYPES',
1914 'PON_ITEM_PRICES_INTERFACE',
1915 batch_id,
1916 interface_line_id
1917 FROM pon_item_prices_interface
1918 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1919 AND batch_id = p_batch_id
1920 AND purchase_basis = 'TEMP LABOR';
1921 END IF;
1922 --
1923 IF(p_contract_type = 'BLANKET' OR p_contract_type = 'CONTRACT') AND
1924 (p_global_flag = 'N') THEN
1925 INSERT INTO pon_interface_errors
1926 (interface_type,
1927 column_name,
1928 error_value,
1929 error_message_name,
1930 table_name,
1931 batch_id,
1932 interface_line_id)
1933 SELECT interface_type,
1934 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1935 line_type,
1936 'PON_GLOBAL_LINE_TYPES',
1937 'PON_ITEM_PRICES_INTERFACE',
1938 batch_id,
1939 interface_line_id
1940 FROM pon_item_prices_interface
1941 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1942 AND batch_id = p_batch_id
1943 AND purchase_basis = 'TEMP LABOR';
1944 END IF;
1945 --
1946 -- Do not allow Outside Processing lines for Global Agreements
1947 --
1948 IF(p_contract_type = 'BLANKET' and p_global_flag = 'Y') THEN
1949 insert into pon_interface_errors
1950 (interface_type,
1951 column_name,
1952 error_value,
1953 error_message_name,
1954 table_name,
1955 batch_id,
1956 interface_line_id)
1957 SELECT interface_type,
1958 fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1959 line_type,
1960 'PON_AUC_GLOBAL_OP_LINE',
1961 'PON_ITEM_PRICES_INTERFACE',
1962 batch_id,
1963 interface_line_id
1964 FROM pon_item_prices_interface
1965 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1966 AND batch_id = p_batch_id
1967 AND outside_operation_flag = 'Y';
1968 END IF;
1969 --
1970 -- For Outside Processing lines, the Item is required
1971 --
1972 insert into pon_interface_errors
1973 (interface_type,
1974 column_name,
1975 error_value,
1976 error_message_name,
1977 table_name,
1978 batch_id,
1979 interface_line_id)
1980 SELECT interface_type,
1981 fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1982 null,
1983 'PON_AUC_OPL_ITEM_REQ',
1984 'PON_ITEM_PRICES_INTERFACE',
1985 batch_id,
1986 interface_line_id
1987 FROM pon_item_prices_interface
1988 WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1989 AND item_number = 'ITEM_NUMBER_NONE_ENTERED'
1990 AND batch_id = p_batch_id
1991 AND outside_operation_flag = 'Y';
1992 --
1993 -- For temp labor lines, the job is required
1994 --
1995 INSERT INTO PON_INTERFACE_ERRORS
1996 (interface_type,
1997 column_name,
1998 error_value,
1999 error_message_name,
2000 table_name,
2001 batch_id,
2002 interface_line_id)
2003 SELECT interface_type,
2004 fnd_message.get_string('PON','PON_ITEM_JOB'),
2005 '',
2006 'PON_LINE_TYPE_JOB_REQ',
2007 'PON_ITEM_PRICES_INTERFACE',
2008 batch_id,
2009 interface_line_id
2010 FROM pon_item_prices_interface
2011 WHERE batch_id = p_batch_id
2012 AND purchase_basis = 'TEMP LABOR'
2013 AND item_number = 'ITEM_NUMBER_NONE_ENTERED';
2014 --
2015 -- Get job information for Services lines for valid jobs
2016 -- We are ignoring whatever the user entered for the description and category columns
2017 -- because there are not enterable on the UI.
2018 --
2019 -- First just get the job_id...
2020 --
2021 UPDATE pon_item_prices_interface p1
2022 SET job_id =
2023 (SELECT nvl(max(poj.job_id),-1)
2024 FROM po_job_associations poj,
2025 per_jobs pj,
2026 per_jobs_vl pjvl
2027 WHERE pjvl.name = p1.item_number AND
2028 pjvl.job_id = pj.job_id AND
2029 pj.job_id = poj.job_id AND
2030 sysdate < nvl(poj.inactive_date, sysdate + 1) AND
2031 sysdate between pj.date_from and nvl(pj.date_to, sysdate + 1))
2032 WHERE batch_id = p_batch_id AND
2033 purchase_basis = 'TEMP LABOR' AND
2034 item_number <> 'ITEM_NUMBER_NONE_ENTERED';
2035 --
2036 -- Update the rest of the information for the service lines with job id's
2037 --
2038 UPDATE pon_item_prices_interface p1
2039 SET (item_description, category_name) =
2040 (SELECT poj.job_long_description,
2041 FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
2042 FROM po_job_associations poj,
2043 mtl_categories_kfv cat
2044 WHERE cat.category_id = poj.category_id AND
2045 poj.job_id = p1.job_id)
2046 WHERE batch_id = p_batch_id AND
2047 purchase_basis = 'TEMP LABOR' AND
2048 item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
2049 job_id <> -1;
2050 --
2051 -- Validate the job
2052 --
2053 INSERT INTO PON_INTERFACE_ERRORS (
2054 interface_type,
2055 column_name,
2056 error_value,
2057 error_message_name,
2058 table_name,
2059 batch_id,
2060 interface_line_id)
2061 SELECT interface_type,
2062 fnd_message.get_string('PON','PON_ITEM_JOB'),
2063 item_number,
2064 'PON_JOB_INVALID',
2065 'PON_ITEM_PRICES_INTERFACE',
2066 batch_id,
2067 interface_line_id
2068 FROM pon_item_prices_interface p1
2069 WHERE batch_id = p_batch_id AND
2070 purchase_basis = 'TEMP LABOR' AND
2071 item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
2072 job_id = -1;
2073 --
2074 -- Set quantity to null for general services lines
2075 --
2076 UPDATE pon_item_prices_interface p1
2077 SET quantity = NULL,
2078 unit_of_measure = NULL
2079 WHERE batch_id = p_batch_id
2080 AND order_type_lookup_code = 'FIXED PRICE'
2081 AND purchase_basis = 'SERVICES';
2082 --
2083 -- Set quantity to null for fixed price temp labor lines
2084 --
2085 UPDATE pon_item_prices_interface p1
2086 SET quantity = null
2087 WHERE batch_id = p_batch_id
2088 AND order_type_lookup_code = 'FIXED PRICE'
2089 AND purchase_basis = 'TEMP LABOR';
2090 --
2091 -- Default category, job_description,
2092 -- additional_job_details for temp labor lines
2093 --
2094 UPDATE pon_item_prices_interface p1
2095 SET (item_description, additional_job_details, category_id, category_name) =
2096 (SELECT poj.job_description,
2097 decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
2098 cat.category_id,
2099 FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
2100 FROM po_job_associations poj,
2101 mtl_categories_kfv cat
2102 WHERE poj.job_id = p1.job_id
2103 AND cat.category_id = poj.category_id)
2104 WHERE batch_id = p_batch_id AND
2105 purchase_basis = 'TEMP LABOR' AND
2106 job_id <> -1;
2107 --
2108 -- Validate differential response type
2109 --
2110 insert into PON_INTERFACE_ERRORS
2111 (interface_type,
2112 column_name,
2113 error_value,
2114 error_message_name,
2115 table_name,
2116 batch_id,
2117 interface_line_id)
2118 select interface_type,
2119 fnd_message.get_string('PON','PON_PRICE_DIFF_RESPONSE'),
2120 differential_response_type,
2121 'PON_INVALID_DIFF_RESPONSE',
2122 'PON_ITEM_PRICES_INTERFACE',
2123 batch_id,
2124 interface_line_id
2125 from pon_item_prices_interface p1
2126 where batch_id = p_batch_id and
2127 purchase_basis = 'TEMP LABOR' and
2128 differential_response_type not in ('DIFF_NONE_ENTERED',
2129 fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
2130 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
2131 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'));
2132 --
2133 -- clear out the differential response type
2134 -- if it does not apply
2135 update pon_item_prices_interface
2136 set differential_response_type = null
2137 where batch_id = p_batch_id and
2138 (purchase_basis = 'TEMP LABOR' and
2139 differential_response_type not in (fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
2140 fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
2141 fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'))) or
2142 (purchase_basis <> 'TEMP LABOR');
2143 --
2144 -- End major services validation
2145 --
2146 -- checks for invalid item numbers
2147 -- ignore inventory item related fields for amount based lines
2148
2149 l_item_number_delimiter := '.';
2150
2151 insert into PON_INTERFACE_ERRORS
2152 (interface_type,
2153 column_name,
2154 error_value,
2155 error_message_name,
2156 table_name,
2157 batch_id,
2158 interface_line_id)
2159 select interface_type,
2160 fnd_message.get_string('PON','PON_AUCTS_ITEM'),
2161 item_number,
2162 'PON_AUCTS_SS_INVALID_INV_NUM',
2163 'PON_ITEM_PRICES_INTERFACE',
2164 batch_id,
2165 interface_line_id
2166 from pon_item_prices_interface p1
2167 where batch_id = p_batch_id and
2168 item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
2169 upper(order_type_lookup_code) <> upper('AMOUNT') and
2170 purchase_basis <> 'TEMP LABOR' and
2171 order_type_lookup_code <> 'FIXED PRICE' and
2172 not exists (SELECT '1'
2173 FROM mtl_system_items_kfv msi,
2174 mtl_default_sets_view mdsv,
2175 mtl_item_categories mic,
2176 mtl_categories_kfv mck
2177 WHERE msi.concatenated_segments = p1.item_number and
2178 msi.organization_id = l_inventory_org_id and
2179 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
2180 msi.purchasing_enabled_flag = 'Y' and
2181 mdsv.functional_area_id = 2 and
2182 mic.inventory_item_id = msi.inventory_item_id and
2183 mic.organization_id = msi.organization_id and
2184 mic.category_set_id = mdsv.category_set_id and
2185 mck.category_id = mic.category_id
2186 and mck.enabled_flag = 'Y'
2187 and sysdate between nvl(mck.start_date_active, sysdate) and
2188 nvl(mck.end_date_active, sysdate)
2189 and nvl(mck.disable_date, sysdate + 1) > sysdate
2190 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'));
2191
2192 -- set item number and revision to null if this is a group
2193 update pon_item_prices_interface p1
2194 set item_number = null,
2195 item_revision = null
2196 where batch_id = p_batch_id and
2197 item_number = 'ITEM_NUMBER_NONE_ENTERED' and
2198 (group_type = 'GROUP' or Nvl(clm_info_flag,'N') = 'Y');
2199
2200 -- sets item number and revision to null if item number is invalid or line type is amount based
2201 -- by setting the item number to null, sourcing one-time item validation will occur for
2202 -- unit of measure, category, etc..
2203
2204 update pon_item_prices_interface p1
2205 set item_number = null,
2206 item_revision = null
2207 where batch_id = p_batch_id AND
2208 purchase_basis = 'SERVICES' or
2209 (purchase_basis = 'GOODS' AND
2210 (item_number = 'ITEM_NUMBER_NONE_ENTERED') OR
2211 (item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
2212 not exists (select '1'
2213 from mtl_system_items_kfv msi,
2214 mtl_default_sets_view mdsv,
2215 mtl_item_categories mic,
2216 mtl_categories_kfv mck
2217 where msi.concatenated_segments = p1.item_number and
2218 msi.organization_id = l_inventory_org_id and
2219 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
2220 msi.purchasing_enabled_flag = 'Y' and
2221 mdsv.functional_area_id = 2 and
2222 mic.inventory_item_id = msi.inventory_item_id and
2223 mic.organization_id = msi.organization_id and
2224 mic.category_set_id = mdsv.category_set_id and
2225 mck.category_id = mic.category_id
2226 and mck.enabled_flag = 'Y'
2227 and sysdate between nvl(mck.start_date_active, sysdate) and
2228 nvl(mck.end_date_active, sysdate)
2229 and nvl(mck.disable_date, sysdate + 1) > sysdate
2230 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'))));
2231
2232
2233 -- set inventory item id, the description update flag, and default the description and unit of measure
2234 -- it not entered for inventory items
2235
2236 update pon_item_prices_interface p1
2237 set (item_id, item_description, allow_item_desc_update_flag, unit_of_measure) =
2238 (select msi.inventory_item_id,
2239 decode(p1.item_description, 'ITEM_NONE_ENTERED', msitl.description, p1.item_description),
2240 msi.allow_item_desc_update_flag,
2241 decode(p1.unit_of_measure, 'UOM_NONE_ENTERED', uom.unit_of_measure_tl, p1.unit_of_measure)
2242 from mtl_system_items_kfv msi,
2243 mtl_system_items_tl msitl,
2244 mtl_units_of_measure_tl uom
2245 where msi.concatenated_segments = p1.item_number and
2246 msi.organization_id = l_inventory_org_id and
2247 nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
2248 msi.purchasing_enabled_flag = 'Y' and
2249 msi.inventory_item_id = msitl.inventory_item_id and
2250 msi.organization_id = msitl.organization_id and
2251 msitl.language = p_language and
2252 msi.primary_uom_code = uom.uom_code and
2253 uom.language = p_language)
2254 where batch_id = p_batch_id and
2255 item_number is not NULL and
2256 purchase_basis = 'GOODS';
2257
2258 UPDATE pon_item_prices_interface
2259 SET unit_of_measure = NULL
2260 WHERE batch_id = p_batch_id
2261 AND Nvl(clm_info_flag,'N') = 'Y'
2262 AND unit_of_measure = 'UOM_NONE_ENTERED'; -- bug 9504539
2263
2264 -- default the category if not entered for valid item numbers
2265
2266 update pon_item_prices_interface p1
2267 set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2268 from mtl_default_sets_view mdsv,
2269 mtl_item_categories mic,
2270 mtl_categories_kfv mck
2271 where mdsv.functional_area_id = 2 and
2272 mic.inventory_item_id = p1.item_id and
2273 mic.organization_id = l_inventory_org_id and
2274 mic.category_set_id = mdsv.category_set_id and
2275 mck.category_id = mic.category_id)
2276 where batch_id = p_batch_id and
2277 purchase_basis <> 'TEMP LABOR' and
2278 item_number is not null and
2279 (category_name = 'CAT_NONE_ENTERED' or category_name is null);
2280
2281 -- validate description for inventory items where the modified flag is set to false
2282
2283 insert into PON_INTERFACE_ERRORS
2284 (interface_type,
2285 column_name,
2286 error_value,
2287 error_message_name,
2288 table_name,
2289 batch_id,
2290 interface_line_id)
2291 select interface_type,
2292 fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2293 item_description,
2294 'PON_AUCTS_INVALID_INV_DESC',
2295 'PON_ITEM_PRICES_INTERFACE',
2296 batch_id,
2297 interface_line_id
2298 from pon_item_prices_interface p1
2299 where batch_id = p_batch_id and
2300 purchase_basis <> 'TEMP LABOR' and
2301 item_number is not null and
2302 allow_item_desc_update_flag = 'N' and
2303 item_description <> (select msitl.description
2304 from mtl_system_items_kfv msi,
2305 mtl_system_items_tl msitl
2306 where msi.inventory_item_id = p1.item_id and
2307 msi.organization_id = l_inventory_org_id and
2308 msi.inventory_item_id = msitl.inventory_item_id and
2309 msi.organization_id = msitl.organization_id and
2310 msitl.language = p_language);
2311
2312 -- validate revision for inventory items
2313
2314 insert into PON_INTERFACE_ERRORS
2315 (interface_type,
2316 column_name,
2317 error_value,
2318 error_message_name,
2319 table_name,
2320 batch_id,
2321 interface_line_id)
2322 select interface_type,
2323 fnd_message.get_string('PON','PON_AUCTS_REVISION'),
2324 item_revision,
2325 'PON_AUCTS_INVALID_INV_REV',
2326 'PON_ITEM_PRICES_INTERFACE',
2327 batch_id,
2328 interface_line_id
2329 from pon_item_prices_interface p1
2330 where batch_id = p_batch_id and
2331 purchase_basis <> 'TEMP LABOR' and
2332 item_number is not null and
2333 item_revision not in (select revision
2334 from mtl_item_revisions_all_v
2335 where inventory_item_id = p1.item_id and
2336 organization_id = l_inventory_org_id);
2337
2338
2339 update pon_item_prices_interface p1
2340 set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2341 from
2342 MTL_CATEGORIES_KFV mck
2343 ,PO_LINE_TYPES plt
2344 where
2345 plt.line_type_id = p1.line_type_id
2346 and plt.category_id = mck.category_id)
2347 where batch_id = p_batch_id
2348 and ((CATEGORY_NAME = 'CAT_NONE_ENTERED') or (CATEGORY_NAME is null));
2349
2350 update pon_item_prices_interface p1
2351 set UNIT_OF_MEASURE = nvl(l_amount_based_unit_of_measure,'UOM_NONE_ENTERED')
2352 where batch_id = p_batch_id
2353 and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2354 and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2355 AND Nvl(clm_info_flag,'N') <> 'Y';
2356
2357 update pon_item_prices_interface p1
2358 set UNIT_OF_MEASURE = (select plt.unit_of_measure
2359 from
2360 PO_LINE_TYPES plt
2361 where
2362 plt.line_type_id = p1.line_type_id)
2363 where batch_id = p_batch_id
2364 and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2365 AND Nvl(clm_info_flag,'N') <> 'Y';
2366
2367
2368 INSERT INTO PON_INTERFACE_ERRORS
2369 ( interface_type
2370 , column_name
2371 , error_value
2372 , error_message_name
2373 , table_name
2374 , batch_id
2375 , interface_line_id
2376 )
2377 SELECT INTERFACE_TYPE
2378 , fnd_message.get_string('PON','PON_AUCTION_UOM')
2379 , unit_of_measure
2380 , 'PON_AUC_LINE_UOM_ERR'
2381 , 'PON_ITEM_PRICES_INTERFACE'
2382 , BATCH_ID
2383 , INTERFACE_LINE_ID
2384 FROM pon_item_prices_interface
2385 where batch_id = p_batch_id
2386 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2387 and order_type_lookup_code = 'AMOUNT'
2388 and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
2389 (upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_unit_of_measure) ))
2390 AND Nvl(clm_info_flag,'N') <> 'Y';
2391
2392 INSERT INTO PON_INTERFACE_ERRORS
2393 ( interface_type
2394 , column_name
2395 , error_value
2396 , error_message_name
2397 , table_name
2398 , batch_id
2399 , interface_line_id
2400 )
2401 SELECT
2402 INTERFACE_TYPE
2403 , fnd_message.get_string('PON',decode(p_contract_type,'STANDARD','PON_AUCTS_QUANTITY','PON_AUCTS_EST_QUANTITY'))
2404 , quantity
2405 , 'PON_AUC_LINE_QUAN_ERR'
2406 , 'PON_ITEM_PRICES_INTERFACE'
2407 , BATCH_ID
2408 , INTERFACE_LINE_ID
2409 FROM pon_item_prices_interface
2410 where batch_id = p_batch_id
2411 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2412 and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2413 --Bug 16801061
2414 --When quantity is given as null, Error message should not be
2415 --thrown for amount based lines
2416 and ( NOT(nvl(quantity,1) = 1)) ;
2417
2418 -- END Line Type Check
2419
2420 IF (is_valid_rule(p_doctype_Id, 'CATEGORY')) THEN
2421 insert into PON_INTERFACE_ERRORS
2422 (interface_type,
2423 column_name,
2424 error_message_name,
2425 table_name,
2426 batch_id,
2427 interface_line_id)
2428 select interface_type,
2429 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2430 'PON_FIELD_MUST_BE_ENTERED',
2431 'PON_ITEM_PRICES_INTERFACE',
2432 batch_id,
2433 interface_line_id
2434 from pon_item_prices_interface
2435 where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2436 AND batch_id = p_batch_id
2437 and group_type <> 'GROUP'
2438 AND Nvl(clm_info_flag,'N') <> 'Y';
2439
2440 -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2441 update pon_item_prices_interface p
2442 set category_id = (select Nvl(MAX(MCK.category_id),-1)
2443 FROM (select category_id,
2444 FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2445 ENABLED_FLAG,
2446 START_DATE_ACTIVE,
2447 END_DATE_ACTIVE,
2448 STRUCTURE_ID,
2449 DISABLE_DATE
2450 from MTL_CATEGORIES_KFV) MCK,
2451 MTL_CATEGORY_SETS MCS,
2452 MTL_DEFAULT_CATEGORY_SETS MDCS,
2453 MTL_CATEGORIES MC
2454 WHERE MCK.CONCATENATED_SEGMENTS = p.category_name
2455 AND MCK.ENABLED_FLAG = 'Y'
2456 AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
2457 NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
2458 MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
2459 MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
2460 AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
2461 AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
2462 (SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
2463 MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
2464 AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
2465 where batch_id = p_batch_id
2466 and category_name <> 'CAT_NON_ENTERED';
2467
2468 -- For those which were not caught in the previous SQL
2469 update pon_item_prices_interface p
2470 set category_id = (select Nvl(MAX(MCK.category_id),-1)
2471 FROM (select category_id,
2472 FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2473 ENABLED_FLAG,
2474 START_DATE_ACTIVE,
2475 END_DATE_ACTIVE,
2476 STRUCTURE_ID,
2477 DISABLE_DATE
2478 from MTL_CATEGORIES_KFV) MCK,
2479 MTL_CATEGORY_SETS MCS,
2480 MTL_DEFAULT_CATEGORY_SETS MDCS,
2481 MTL_CATEGORIES MC
2482 WHERE UPPER(MCK.CONCATENATED_SEGMENTS) = UPPER(p.category_name)
2483 AND MCK.ENABLED_FLAG = 'Y'
2484 AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
2485 NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
2486 MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
2487 MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
2488 AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
2489 AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
2490 (SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
2491 MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
2492 AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
2493 where batch_id = p_batch_id
2494 and category_name <> 'CAT_NON_ENTERED'
2495 and ( category_id is null or category_id = -1 );
2496 --
2497 -- Because we do case insensitive validation for category_name,
2498 -- we need to update all valid user entered category names to the
2499 -- actual case sensitive value
2500 /*
2501 update pon_item_prices_interface p
2502 set category_name = (select Nvl(MAX(category_name),p.category_name)
2503 from icx_por_categories_tl i
2504 where i.rt_category_id = p.category_id
2505 and type=2 and i.language= p_language)
2506 where batch_id = p_batch_id
2507 and category_name <> 'CAT_NON_ENTERED'
2508 AND category_id <> -1;
2509 */
2510 update pon_item_prices_interface p
2511 set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2512 from mtl_categories_kfv i
2513 where i.category_id = p.category_id)
2514 where batch_id = p_batch_id
2515 and category_name <> 'CAT_NON_ENTERED'
2516 AND category_id <> -1;
2517
2518 insert into PON_INTERFACE_ERRORS
2519 (interface_type,
2520 column_name,
2521 error_value,
2522 error_message_name,
2523 table_name,
2524 batch_id,
2525 interface_line_id)
2526 select interface_type,
2527 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2528 category_name,
2529 decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2530 'PON_ITEM_PRICES_INTERFACE',
2531 batch_id,
2532 interface_line_id
2533 from pon_item_prices_interface
2534 where category_id = -1
2535 AND batch_id = p_batch_id
2536 AND category_name <> 'CAT_NON_ENTERED';
2537
2538 -- validate category name for inventory items
2539
2540 insert into PON_INTERFACE_ERRORS
2541 (interface_type,
2542 column_name,
2543 error_value,
2544 error_message_name,
2545 table_name,
2546 batch_id,
2547 interface_line_id)
2548 select interface_type,
2549 fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2550 category_name,
2551 'PON_AUCTS_INVALID_INV_CAT',
2552 'PON_ITEM_PRICES_INTERFACE',
2553 batch_id,
2554 interface_line_id
2555 from pon_item_prices_interface p1
2556 where category_id <> -1 and
2557 batch_id = p_batch_id and
2558 purchase_basis <> 'TEMP LABOR' and
2559 order_type_lookup_code <> 'FIXED PRICE' and
2560 item_number is not null and
2561 category_name <> (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2562 from mtl_default_sets_view mdsv,
2563 mtl_item_categories mic,
2564 mtl_categories_kfv mck
2565 where mdsv.functional_area_id = 2 and
2566 mic.inventory_item_id = p1.item_id and
2567 mic.organization_id = l_inventory_org_id and
2568 mic.category_set_id = mdsv.category_set_id and
2569 mck.category_id = mic.category_id);
2570
2571
2572
2573 END IF;
2574
2575
2576 IF (p_contract_type in ('BLANKET', 'CONTRACT')) THEN
2577
2578 -- default shopping category (ip category) using purchasing-iP category mappings
2579 -- when shopping category is missing...only for new lines since shopping category
2580 -- is optional
2581 IF (NVL(l_progress_payment_type,'NONE') = 'NONE') THEN
2582 update pon_item_prices_interface p1
2583 set ip_category_name = (select category_name
2584 from icx_cat_categories_v
2585 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
2586 language = p_language)
2587 where batch_id = p_batch_id and
2588 (action is null or action = '+') and
2589 p1.category_id <> -1 and
2590 (p1.ip_category_name is null or p1.ip_category_name = 'IP_CAT_NONE_ENTERED');
2591
2592 -- iP category needs to be valid
2593
2594 insert into PON_INTERFACE_ERRORS
2595 (interface_type,
2596 column_name,
2597 error_message_name,
2598 table_name,
2599 batch_id,
2600 interface_line_id)
2601 select interface_type,
2602 fnd_message.get_string('PON','PON_SHOPPING_CAT'),
2603 'PON_SHOP_CAT_NOT_VALID',
2604 'PON_ITEM_PRICES_INTERFACE',
2605 batch_id,
2606 interface_line_id
2607 from pon_item_prices_interface p1
2608 where p1.batch_id = p_batch_id and
2609 p1.ip_category_name is not null and
2610 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2611 not exists (select null
2612 from icx_cat_categories_v icx
2613 where icx.category_name = p1.ip_category_name and
2614 icx.language = p_language)
2615 AND Nvl(clm_info_flag,'N') <> 'Y';
2616
2617 -- set ip category name to null if ip cateogry is invalid
2618
2619 update pon_item_prices_interface p1
2620 set ip_category_name = null
2621 where p1.batch_id = p_batch_id and
2622 p1.ip_category_name is not null and
2623 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2624 not exists (select null
2625 from icx_cat_categories_v icx
2626 where icx.category_name = p1.ip_category_name and
2627 icx.language = p_language);
2628
2629 -- set ip category id
2630 update pon_item_prices_interface p1
2631 set ip_category_id = (select rt_category_id
2632 from icx_cat_categories_v icx
2633 where icx.category_name = p1.ip_category_name and
2634 language = p_language and rownum=1 )
2635 where p1.batch_id = p_batch_id and
2636 p1.ip_category_name is not null and
2637 p1.ip_category_name <> 'IP_CAT_NONE_ENTERED';
2638 END IF; -- If progress payment type is NONE(i.e non complex work style)
2639 END IF;
2640
2641 --
2642 -- Item description can't be null
2643 --
2644 insert into PON_INTERFACE_ERRORS
2645 (interface_type,
2646 column_name,
2647 error_message_name,
2648 table_name,
2649 batch_id,
2650 interface_line_id)
2651 select interface_type,
2652 fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2653 'PON_FIELD_MUST_BE_ENTERED',
2654 'PON_ITEM_PRICES_INTERFACE',
2655 batch_id,
2656 interface_line_id
2657 from pon_item_prices_interface
2658 where item_description = 'ITEM_NONE_ENTERED'
2659 AND batch_id = p_batch_id
2660 and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2661
2662 update pon_item_prices_interface p1
2663 set item_description = null
2664 where batch_id = p_batch_id and
2665 item_description = 'ITEM_NONE_ENTERED';
2666
2667 --
2668 --
2669 -- Unit of Measure
2670 -- AUCTION and BID --
2671 --
2672 IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
2673 insert into PON_INTERFACE_ERRORS
2674 (interface_type,
2675 column_name,
2676 error_message_name,
2677 table_name,
2678 batch_id,
2679 interface_line_id)
2680 select interface_type,
2681 fnd_message.get_string('PON','PON_ORDER_UNIT_H'),
2682 'PON_FIELD_MUST_BE_ENTERED',
2683 'PON_ITEM_PRICES_INTERFACE',
2684 batch_id,
2685 interface_line_id
2686 from pon_item_prices_interface
2687 where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2688 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2689 AND order_type_lookup_code <> 'FIXED PRICE'
2690 AND batch_id = p_batch_id
2691 and group_type <> 'GROUP'
2692 AND Nvl(clm_info_flag,'N') <> 'Y';
2693 --
2694 -- Unit of Measure must be valid in mtl_units_of_measure_tl
2695 --
2696 -- Some modifications to avoid full table scan and more imp
2697 -- to incorporate the new demand in bug 2319969
2698 update pon_item_prices_interface p
2699 set uom_code = (select nvl(max(uom_code),'XXX') from
2700 mtl_units_of_measure_tl m
2701 where language = p_language
2702 and unit_of_measure_tl = p.unit_of_measure
2703 and (p.purchase_basis <> 'TEMP LABOR' or
2704 (p.purchase_basis = 'TEMP LABOR' and
2705 exists (select 1 from mtl_uom_conversions_val_v where
2706 m.unit_of_measure = unit_of_measure and
2707 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2708 where batch_id = p_batch_id
2709 and unit_of_measure <> 'UOM_NONE_ENTERED';
2710 --
2711 update pon_item_prices_interface p
2712 set uom_code = (select nvl(max(uom_code),'XXX') from
2713 mtl_units_of_measure_tl m
2714 where language = p_language
2715 and upper(unit_of_measure_tl) = upper(p.unit_of_measure)
2716 and (p.purchase_basis <> 'TEMP LABOR' or
2717 (p.purchase_basis = 'TEMP LABOR' and
2718 exists (select 1 from mtl_uom_conversions_val_v where
2719 m.unit_of_measure = unit_of_measure and
2720 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2721 where batch_id = p_batch_id
2722 and unit_of_measure <> 'UOM_NONE_ENTERED'
2723 and uom_code = 'XXX';
2724 --
2725 update pon_item_prices_interface p
2726 set uom_code = (select nvl(max(uom_code),'XXX') from
2727 mtl_units_of_measure_tl m
2728 where language = p_language
2729 and uom_code = p.unit_of_measure
2730 and (p.purchase_basis <> 'TEMP LABOR' or
2731 (p.purchase_basis = 'TEMP LABOR' and
2732 exists (select 1 from mtl_uom_conversions_val_v where
2733 m.unit_of_measure = unit_of_measure and
2734 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2735 where batch_id = p_batch_id
2736 and unit_of_measure <> 'UOM_NONE_ENTERED'
2737 and uom_code = 'XXX';
2738 --
2739 update pon_item_prices_interface p
2740 set uom_code = (select nvl(max(uom_code),'XXX') from
2741 mtl_units_of_measure_tl m
2742 where language = p_language
2743 and upper(uom_code) = upper(p.unit_of_measure)
2744 and (p.purchase_basis <> 'TEMP LABOR' or
2745 (p.purchase_basis = 'TEMP LABOR' and
2746 exists (select 1 from mtl_uom_conversions_val_v where
2747 m.unit_of_measure = unit_of_measure and
2748 uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2749 where batch_id = p_batch_id
2750 and unit_of_measure <> 'UOM_NONE_ENTERED'
2751 and uom_code = 'XXX';
2752
2753 --
2754
2755 -- Because we do case insensitive validation for uom_code,
2756 -- we need to update all valid user entered unit of measures to the
2757 -- actual case sensitive value
2758 update pon_item_prices_interface p
2759 set unit_of_measure = (select nvl(max(unit_of_measure_tl),p.unit_of_measure) from
2760 mtl_units_of_measure_tl m
2761 where language = p_language
2762 and uom_code = p.uom_code)
2763 where batch_id = p_batch_id
2764 and unit_of_measure <> 'UOM_NONE_ENTERED'
2765 AND uom_code <> 'XXX';
2766
2767 insert into PON_INTERFACE_ERRORS
2768 (interface_type,
2769 column_name,
2770 error_value,
2771 error_message_name,
2772 table_name,
2773 batch_id,
2774 interface_line_id)
2775 select interface_type,
2776 fnd_message.get_string('PON','PON_AUCTION_UOM'),
2777 unit_of_measure,
2778 decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2779 'PON_ITEM_PRICES_INTERFACE',
2780 batch_id,
2781 interface_line_id
2782 from pon_item_prices_interface p
2783 where unit_of_measure <> 'UOM_NONE_ENTERED'
2784 AND batch_id = p_batch_id
2785 AND uom_code = 'XXX'
2786 AND nvl(price_and_quantity_apply, 'Y') = 'Y';
2787
2788 -- validate unit of measure for inventory items
2789
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 interface_type,
2799 fnd_message.get_string('PON','PON_AUCTION_UOM'),
2800 unit_of_measure,
2801 'PON_AUCTS_INVALID_INV_UOM',
2802 'PON_ITEM_PRICES_INTERFACE',
2803 batch_id,
2804 interface_line_id
2805 from pon_item_prices_interface p1
2806 where batch_id = p_batch_id and
2807 purchase_basis <> 'TEMP LABOR' and
2808 item_number is not null and
2809 uom_code not in (select uom_code
2810 from mtl_item_uoms_view
2811 where inventory_item_id = p1.item_id and
2812 organization_id = l_inventory_org_id) and
2813 nvl(price_and_quantity_apply, 'Y') = 'Y';
2814
2815 -- Defaulting of Unit of Measure still occurs for lines where quantity and price don't apply
2816 -- For these lines, we will reset the unit of measure fields
2817 IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
2818 update pon_item_prices_interface p1
2819 set unit_of_measure = null,
2820 uom_code = null
2821 where batch_id = p_batch_id and
2822 nvl(price_and_quantity_apply, 'Y') = 'N';
2823 END IF;
2824
2825 END IF;
2826
2827 --
2828 -- bug 3353248
2829 -- check quantity can't be null under certain conditions.
2830 --
2831 --
2832 if (p_contract_type = 'STANDARD' or p_contract_type is null) then
2833 insert into PON_INTERFACE_ERRORS
2834 (interface_type,
2835 column_name,
2836 error_message_name,
2837 table_name,
2838 batch_id,
2839 interface_line_id)
2840 select interface_type,
2841 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
2842 'PON_FIELD_MUST_BE_ENTERED',
2843 'PON_ITEM_PRICES_INTERFACE',
2844 batch_id,
2845 interface_line_id
2846 from pon_item_prices_interface
2847 where quantity is null
2848 and nvl(price_and_quantity_apply, 'Y') = 'Y'
2849 and batch_id = p_batch_id
2850 and purchase_basis <> 'TEMP LABOR'
2851 and order_type_lookup_code <> 'AMOUNT'
2852 and order_type_lookup_code <> 'FIXED PRICE'
2853 and group_type <> 'GROUP'
2854 AND Nvl(clm_info_flag,'N') <> 'Y';
2855 end if;
2856
2857 -- quantity cannot also be null if line is neither fixed-price based nor amount-based and it has a fixed amount price factor
2858 IF (p_contract_type IN ('BLANKET', 'CONTRACT')) THEN
2859 INSERT INTO pon_interface_errors
2860 (interface_type,
2861 column_name,
2862 error_value,
2863 error_message_name,
2864 token1_name,
2865 token1_value,
2866 table_name,
2867 batch_id,
2868 interface_line_id)
2869 SELECT
2870 interface_type,
2871 fnd_message.get_string('PON', 'PON_AUCTS_EST_QUANTITY'),
2872 quantity,
2873 'PON_AUC_QUAN_FIXED_AMT',
2874 'LINENUM',
2875 interface_line_id,
2876 'PON_ITEM_PRICES_INTERFACE',
2877 batch_id,
2878 interface_line_id
2879 FROM pon_item_prices_interface
2880 WHERE
2881 quantity IS NULL
2882 AND order_type_lookup_code <> 'FIXED PRICE'
2883 AND order_type_lookup_code <> 'AMOUNT'
2884 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2885 AND batch_id = p_batch_id
2886 AND EXISTS (SELECT 1
2887 FROM
2888 pon_auc_price_elements_int pfs,
2889 fnd_lookup_values lookups
2890 WHERE
2891 pfs.batch_id = pon_item_prices_interface.batch_id
2892 AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2893 AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2894 AND lookups.lookup_type = 'PON_PRICING_BASIS'
2895 AND lookups.lookup_code = 'FIXED_AMOUNT'
2896 AND lookups.view_application_id = 0
2897 AND lookups.security_group_id = 0
2898 AND lookups.meaning = pfs.pricing_basis_name
2899 AND lookups.language = USERENV('LANG'))
2900 AND Nvl(clm_info_flag,'N') <> 'Y';
2901 END IF;
2902
2903 -- Quantity > 0 --
2904 -- AUCTION and BID --
2905 --
2906 IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
2907 insert into PON_INTERFACE_ERRORS
2908 (interface_type,
2909 column_name,
2910 error_value,
2911 error_message_name,
2912 table_name,
2913 batch_id,
2914 interface_line_id)
2915 select interface_type,
2916 decode(p_contract_type, 'BLANKET','PON_AUCTS_EST_QUANTITY', 'CONTRACT', 'PON_AUCTS_EST_QUANTITY', 'PON_AUCTS_QUANTITY'),
2917 quantity,
2918 'PON_MUST_BE_POSITIVE_NUMBER',
2919 'PON_ITEM_PRICES_INTERFACE',
2920 batch_id,
2921 interface_line_id
2922 from pon_item_prices_interface
2923 where quantity <= 0
2924 AND order_type_lookup_code <> 'FIXED PRICE'
2925 AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2926 AND batch_id = p_batch_id;
2927
2928 END IF;
2929 --
2930 --
2931 -- Need by date > sysdate --
2932 -- AUCTION only --
2933 --
2934 IF (is_valid_rule(p_doctype_Id, 'NEED_BY_DATE')) THEN
2935 /* CLM Period of Performance Dates : For federal document, for outcome doc style
2936 * as SPO, all the POP dates can not be null.
2937 */
2938 insert into PON_INTERFACE_ERRORS
2939 (interface_type,
2940 column_name,
2941 error_value,
2942 error_message_name,
2943 table_name,
2944 batch_id,
2945 interface_line_id)
2946 select interface_type,
2947 '',
2948 '',
2949 'PON_NEED_BY_DATE_POP_DATE',
2950 'PON_ITEM_PRICES_INTERFACE',
2951 batch_id,
2952 interface_line_id
2953 from pon_item_prices_interface
2954 where ( (nvl(l_is_fed,'N')='Y') and
2955 (p_contract_type = 'STANDARD') and
2956 ( (clm_need_by_date is not null and need_by_start_date is not null and need_by_date is not null)
2957 /* Bug : 13700330 : Need By or POP dates are not mandatory for non-inventory lines. */
2958 --or (clm_need_by_date is null and ( (need_by_start_date is null and need_by_date is null))
2959 or (clm_need_by_date is not null and ( (need_by_start_date is null and need_by_date is not null)
2960 or (need_by_start_date is not null and need_by_date is null))
2961 )
2962 )
2963 )
2964 and batch_id = p_batch_id
2965 and nvl(clm_info_flag,'N') = 'N';
2966
2967 /* CLM Period of Performance Dates : For federal document, for outcome doc style
2968 * as SPO, need_by_start_date is required, when need_by_date is entered.
2969 */
2970 insert into PON_INTERFACE_ERRORS
2971 (interface_type,
2972 column_name,
2973 error_value,
2974 error_message_name,
2975 table_name,
2976 batch_id,
2977 interface_line_id)
2978 select interface_type,
2979 '',
2980 '',
2981 'PON_POP_START_DATE_REQD',
2982 'PON_ITEM_PRICES_INTERFACE',
2983 batch_id,
2984 interface_line_id
2985 from pon_item_prices_interface
2986 where ( (nvl(l_is_fed,'N')='Y') and
2987 (p_contract_type = 'STANDARD') and
2988 ( (clm_need_by_date is null and (need_by_start_date is null and need_by_date is not null)))
2989 )
2990 and batch_id = p_batch_id
2991 and nvl(clm_info_flag,'N') = 'N';
2992
2993 /* CLM Period of Performance Dates : For federal document, for outcome doc style
2994 * as SPO, need_by_date is required, when need_by_start_date is entered.
2995 */
2996 insert into PON_INTERFACE_ERRORS
2997 (interface_type,
2998 column_name,
2999 error_value,
3000 error_message_name,
3001 table_name,
3002 batch_id,
3003 interface_line_id)
3004 select interface_type,
3005 '',
3006 '',
3007 'PON_POP_END_DATE_REQUIRED',
3008 'PON_ITEM_PRICES_INTERFACE',
3009 batch_id,
3010 interface_line_id
3011 from pon_item_prices_interface
3012 where ( (nvl(l_is_fed,'N')='Y') and
3013 (p_contract_type = 'STANDARD') and
3014 ( (clm_need_by_date is null and (need_by_start_date is not null and need_by_date is null)))
3015 )
3016 and batch_id = p_batch_id
3017 and nvl(clm_info_flag,'N') = 'N';
3018 -- NEED_BY_FROM_DATE
3019 insert into PON_INTERFACE_ERRORS
3020 (interface_type,
3021 column_name,
3022 error_value,
3023 error_message_name,
3024 table_name,
3025 batch_id,
3026 interface_line_id)
3027 select interface_type,
3028 -- CLM : For federal documents, display Period Of Performance Start Date
3029 decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_START_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_FROM_DATE')),
3030 need_by_start_date,
3031 'PON_DATE_MUST_BE_GT_TODAY',
3032 'PON_ITEM_PRICES_INTERFACE',
3033 batch_id,
3034 interface_line_id
3035 from pon_item_prices_interface
3036 where need_by_start_date < sysdate
3037 and batch_id = p_batch_id;
3038 --
3039 -- NEED_BY_TO_DATE
3040 insert into PON_INTERFACE_ERRORS
3041 (interface_type,
3042 column_name,
3043 error_value,
3044 error_message_name,
3045 table_name,
3046 batch_id,
3047 interface_line_id)
3048 select interface_type,
3049 -- CLM : For federal documents, display Period Of Performance End Date
3050 decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
3051 need_by_date,
3052 'PON_DATE_MUST_BE_GT_TODAY',
3053 'PON_ITEM_PRICES_INTERFACE',
3054 batch_id,
3055 interface_line_id
3056 from pon_item_prices_interface
3057 where need_by_date < sysdate
3058 and batch_id = p_batch_id;
3059
3060
3061 -- CLM : Period Of Performance Date Range : CLM_NEED_BY_DATE Validations
3062 insert into PON_INTERFACE_ERRORS
3063 (interface_type,
3064 column_name,
3065 error_value,
3066 error_message_name,
3067 table_name,
3068 batch_id,
3069 interface_line_id)
3070 select interface_type,
3071 fnd_message.get_string('PON','PON_AUCTS_NEED_BY_DATE'),
3072 clm_need_by_date,
3073 'PON_DATE_MUST_BE_GT_TODAY',
3074 'PON_ITEM_PRICES_INTERFACE',
3075 batch_id,
3076 interface_line_id
3077 from pon_item_prices_interface
3078 where clm_need_by_date < sysdate
3079 and batch_id = p_batch_id;
3080 --
3081 -- NEED_BY_TO_DATE vs NEED_BY_FROM_DATE
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 --CLM : For Federal, show Period of Performance end date
3092 decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
3093 need_by_date,
3094 -- CLM : For federal display Period Of Perf dates message
3095 decode(l_is_fed,'Y','PON_AUC_IMPORT_POP_BEFORE_FROM','PON_AUC_NEEDBY_BEFORE_FROM_SS'),
3096 'PON_ITEM_PRICES_INTERFACE',
3097 batch_id,
3098 interface_line_id
3099 from pon_item_prices_interface
3100 --Bug : 13700330 : For clm, POP Start Date should be less than End Date
3101 where ( (l_is_fed = 'Y' AND (clm_need_by_date IS NULL AND (need_by_date <= need_by_start_date)))
3102 OR (l_is_fed = 'N' AND need_by_date < need_by_start_date))
3103 and batch_id = p_batch_id;
3104 --
3105 -- need by required for planned inventory items for SPO
3106 /*Bug : 13700330 : We need this validation for clm also.
3107 For clm, clm_need_by_date field is mandatory for inventory item lines. */
3108 IF(p_contract_type = 'STANDARD') THEN --and nvl(l_is_fed,'N')<>'Y') THEN
3109 insert into PON_INTERFACE_ERRORS
3110 (interface_type,
3111 column_name,
3112 error_value,
3113 error_message_name,
3114 table_name,
3115 batch_id,
3116 interface_line_id)
3117 select ip.interface_type,
3118 fnd_message.get_string('PON','PON_AUCTS_NEEDBY'),
3119 null,
3120 'PON_NEED_BY_DATE_REQ_SPD',
3121 'PON_ITEM_PRICES_INTERFACE',
3122 ip.batch_id,
3123 ip.interface_line_id
3124 from pon_item_prices_interface ip
3125 where ip.batch_id = p_batch_id
3126 and ( (l_is_fed = 'Y' AND ip.clm_need_by_date IS null)
3127 OR (l_is_fed = 'N' AND (ip.need_by_date is null AND ip.need_by_start_date is NULL)))
3128 and ip.item_id is not null
3129 and exists ( SELECT 'x'
3130 FROM mtl_system_items_kfv msi,
3131 financials_system_params_all fsp
3132 WHERE nvl(fsp.org_id, -9999) = nvl(p_org_id,-9999)
3133 and msi.organization_id = fsp.inventory_organization_id
3134 and msi.inventory_item_id = ip.item_id
3135 and (msi.INVENTORY_PLANNING_CODE in (1, 2) or msi.MRP_PLANNING_CODE in
3136 (3, 4, 7, 8, 9))
3137 )
3138 AND Nvl(clm_info_flag,'N') <> 'Y';
3139 END IF;
3140
3141 -- CLM: Event Based Delivery Management
3142
3143 update pon_item_prices_interface p
3144 set p.CLM_DELIVERY_EVENT_CODE = (SELECT lookup_code
3145 FROM fnd_lookup_values_vl
3146 WHERE lookup_type = 'CLM_DELIVERY_EVENT'
3147 AND meaning = p.CLM_DELIVERY_EVENT);
3148
3149 insert into PON_INTERFACE_ERRORS
3150 (interface_type,
3151 column_name,
3152 error_value,
3153 error_message_name,
3154 table_name,
3155 batch_id,
3156 interface_line_id)
3157 select interface_type,
3158 fnd_message.get_string('PON','PON_CLM_DEL_EVENT_CODE'),
3159 CLM_DELIVERY_EVENT,
3160 'PON_INV_DEL_EVENT_CODE',
3161 'PON_ITEM_PRICES_INTERFACE',
3162 batch_id,
3163 interface_line_id
3164 from pon_item_prices_interface
3165 where CLM_DELIVERY_EVENT_CODE IS NULL
3166 and CLM_DELIVERY_EVENT IS NOT NULL;
3167
3168 update pon_item_prices_interface p
3169 set p.CLM_DELIVERY_PERIOD_UOM_CODE = (SELECT lookup_code
3170 FROM fnd_lookup_values_vl
3171 WHERE lookup_type = 'CLM_PERIOD'
3172 AND meaning = p.CLM_DELIVERY_PERIOD_UOM);
3173
3174 insert into PON_INTERFACE_ERRORS
3175 (interface_type,
3176 column_name,
3177 error_value,
3178 error_message_name,
3179 table_name,
3180 batch_id,
3181 interface_line_id)
3182 select interface_type,
3183 fnd_message.get_string('PON','PON_CLM_DEL_PERIOD_UOM'),
3184 CLM_DELIVERY_PERIOD_UOM,
3185 'PON_INV_PERIOD_UOM_CODE',
3186 'PON_ITEM_PRICES_INTERFACE',
3187 batch_id,
3188 interface_line_id
3189 from pon_item_prices_interface
3190 where CLM_DELIVERY_PERIOD_UOM_CODE IS NULL
3191 and CLM_DELIVERY_PERIOD_UOM IS NOT NULL;
3192
3193 update pon_item_prices_interface p
3194 set p.CLM_POP_DURATION_UOM_CODE = (SELECT lookup_code
3195 FROM fnd_lookup_values_vl
3196 WHERE lookup_type = 'CLM_PERIOD'
3197 AND meaning = p.CLM_POP_DURATION_UOM);
3198
3199 insert into PON_INTERFACE_ERRORS
3200 (interface_type,
3201 column_name,
3202 error_value,
3203 error_message_name,
3204 table_name,
3205 batch_id,
3206 interface_line_id)
3207 select interface_type,
3208 fnd_message.get_string('PON','PON_CLM_POP_DUR_UOM'),
3209 CLM_POP_DURATION_UOM,
3210 'PON_INV_POP_UOM_CODE',
3211 'PON_ITEM_PRICES_INTERFACE',
3212 batch_id,
3213 interface_line_id
3214 from pon_item_prices_interface
3215 where CLM_POP_DURATION_UOM_CODE IS NULL
3216 and CLM_POP_DURATION_UOM IS NOT NULL;
3217
3218 END IF;
3219 --
3220 -- Ship to location, validating non null, auction only --
3221 -- The validation is needed for standard po only as ship-to location is
3222 -- removed for blanket/contract purchase agreement.
3223 --
3224 IF (is_valid_rule(p_doctype_Id, 'SHIP_TO_LOCATION') and (p_contract_type is null or p_contract_type = 'STANDARD')) THEN
3225
3226 IF (is_required_rule(p_doctype_Id, 'SHIP_TO_LOCATION')) THEN
3227 insert into PON_INTERFACE_ERRORS
3228 ( interface_type
3229 , column_name
3230 , error_message_name
3231 , table_name
3232 , batch_id
3233 , interface_line_id
3234 )
3235 select interface_type
3236 , fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC')
3237 , 'PON_FIELD_MUST_BE_ENTERED'
3238 , 'PON_ITEM_PRICES_INTERFACE'
3239 , batch_id
3240 , interface_line_id
3241 from pon_item_prices_interface
3242 where batch_id = p_batch_id
3243 AND ship_to_location = 'SHIP_NONE_ENTERED'
3244 and group_type <> 'GROUP'
3245 AND Nvl(clm_info_flag,'N') <> 'Y';
3246 END IF;
3247 -- fph
3248 -- again to avoid some full table scan
3249 update pon_item_prices_interface p
3250 set ship_to_location_id = (select (nvl(max(location_id), -1))
3251 from po_ship_to_loc_org_v po_v
3252 where po_v.location_code = p.ship_to_location)
3253 where batch_id = p_batch_id
3254 and ship_to_location <> 'SHIP_NONE_ENTERED';
3255 --
3256 update pon_item_prices_interface p
3257 set ship_to_location_id = (select (nvl(max(location_id), -1))
3258 from po_ship_to_loc_org_v po_v
3259 where upper(po_v.location_code) = upper(p.ship_to_location))
3260 where batch_id = p_batch_id
3261 and ship_to_location <> 'SHIP_NONE_ENTERED'
3262 and ship_to_location_id = -1;
3263 --
3264 -- Because we do case insensitive validation for ship_to_location,
3265 -- we need to update all valid user entered shipping locations to the
3266 -- actual case sensitive value fph
3267 update pon_item_prices_interface p
3268 set ship_to_location = (select (nvl(max(location_code), -1))
3269 from po_ship_to_loc_org_v po_v
3270 where po_v.location_id = p.ship_to_location_id)
3271 where batch_id = p_batch_id
3272 and ship_to_location <> 'SHIP_NONE_ENTERED'
3273 AND ship_to_location_id <> -1;
3274 --
3275 --
3276
3277 insert into PON_INTERFACE_ERRORS
3278 (interface_type,
3279 column_name,
3280 error_value,
3281 error_message_name,
3282 table_name,
3283 batch_id,
3284 interface_line_id)
3285 select interface_type,
3286 fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC'),
3287 ship_to_location,
3288 'PON_CAT_INVALID_VALUE',
3289 'PON_ITEM_PRICES_INTERFACE',
3290 batch_id,
3291 interface_line_id
3292 from pon_item_prices_interface
3293 where ship_to_location_id = -1
3294 AND ship_to_location <> 'SHIP_NONE_ENTERED'
3295 AND batch_id = p_batch_id;
3296
3297 END IF;
3298 --
3299 -- Target price NOT <=0 --
3300 -- AUCTION only --
3301 --
3302 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
3303 insert into PON_INTERFACE_ERRORS
3304 (interface_type,
3305 column_name,
3306 error_value,
3307 error_message_name,
3308 table_name,
3309 batch_id,
3310 interface_line_id)
3311 select interface_type,
3312 fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
3313 target_price,
3314 'PON_MUST_BE_POSITIVE_NUMBER',
3315 'PON_ITEM_PRICES_INTERFACE',
3316 batch_id,
3317 interface_line_id
3318 from pon_item_prices_interface
3319 where TARGET_PRICE <= 0
3320 and nvl(price_and_quantity_apply, 'Y') = 'Y'
3321 and batch_id = p_batch_id;
3322 END IF;
3323 --
3324 -- Bid start price NOT <=0 --
3325 -- AUCTION only --
3326 --
3327 IF (is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
3328 insert into PON_INTERFACE_ERRORS
3329 (interface_type,
3330 column_name,
3331 error_value,
3332 error_message_name,
3333 table_name,
3334 batch_id,
3335 interface_line_id)
3336 select interface_type,
3337 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3338 bid_start_price,
3339 'PON_MUST_BE_POSITIVE_NUMBER',
3340 'PON_ITEM_PRICES_INTERFACE',
3341 batch_id,
3342 interface_line_id
3343 from pon_item_prices_interface
3344 where bid_start_price <= 0
3345 and batch_id = p_batch_id;
3346 END IF;
3347 --
3348 -- Current price NOT <=0 --
3349 -- AUCTION/RFQs/Offers --
3350 --
3351 IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
3352 insert into PON_INTERFACE_ERRORS
3353 (interface_type,
3354 column_name,
3355 error_value,
3356 error_message_name,
3357 table_name,
3358 batch_id,
3359 interface_line_id)
3360 select interface_type,
3361 fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
3362 current_price,
3363 'PON_MUST_BE_POSITIVE_NUMBER',
3364 'PON_ITEM_PRICES_INTERFACE',
3365 batch_id,
3366 interface_line_id
3367 from pon_item_prices_interface
3368 where current_price <= 0
3369 and nvl(price_and_quantity_apply, 'Y') = 'Y'
3370 and batch_id = p_batch_id;
3371 END IF;
3372 --
3373 -- reserve price NOT <=0 --
3374 -- AUCTION only --
3375 --
3376 IF (is_valid_rule(p_doctype_Id, 'RESERVE_PRICE')) THEN
3377 insert into PON_INTERFACE_ERRORS
3378 (interface_type,
3379 column_name,
3380 error_value,
3381 error_message_name,
3382 table_name,
3383 batch_id,
3384 interface_line_id)
3385 select interface_type,
3386 fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3387 reserve_price,
3388 'PON_MUST_BE_POSITIVE_NUMBER',
3389 'PON_ITEM_PRICES_INTERFACE',
3390 batch_id,
3391 interface_line_id
3392 from pon_item_prices_interface
3393 where reserve_price <= 0 AND
3394 batch_id = p_batch_id;
3395 END IF;
3396 --
3397 --
3398 -- get the transaction type
3399 SELECT TRANSACTION_TYPE
3400 INTO l_transaction_type
3401 FROM PON_AUC_DOCTYPES
3402 WHERE DOCTYPE_ID = p_doctype_Id;
3403 --
3404 -- DBMS_OUTPUT.PUT_LINE('l_transaction_type = ' || l_transaction_type);
3405 IF(l_transaction_type = 'REVERSE') THEN
3406 --
3407 -- Bid start price > TARGET PRICE --
3408 -- Buyer AUCTION only --
3409 --
3410
3411 IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE') AND is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
3412 insert into PON_INTERFACE_ERRORS
3413 (interface_type,
3414 column_name,
3415 error_value,
3416 error_message_name,
3417 table_name,
3418 batch_id,
3419 interface_line_id)
3420 select interface_type,
3421 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3422 bid_start_price,
3423 'PON_TARGET_GTR_BID_START',
3424 'PON_ITEM_PRICES_INTERFACE',
3425 batch_id,
3426 interface_line_id
3427 from pon_item_prices_interface
3428 where bid_start_price <= target_price AND
3429 batch_id = p_batch_id;
3430 END IF;
3431 --
3432 ELSIF(l_transaction_type = 'FORWARD') THEN
3433 --
3434 -- Bid start price < TARGET PRICE --
3435 -- Seller AUCTION only --
3436 --
3437 insert into PON_INTERFACE_ERRORS
3438 (interface_type,
3439 column_name,
3440 error_value,
3441 error_message_name,
3442 table_name,
3443 batch_id,
3444 interface_line_id)
3445 select interface_type,
3446 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3447 bid_start_price,
3448 'PON_AUCTS_START_LT_TARGET',
3449 'PON_ITEM_PRICES_INTERFACE',
3450 batch_id,
3451 interface_line_id
3452 from pon_item_prices_interface
3453 where bid_start_price >= target_price AND
3454 batch_id = p_batch_id;
3455 --
3456 -- bid start price < reserve price
3457 -- Seller AUCTION only --
3458 --
3459 insert into PON_INTERFACE_ERRORS
3460 (interface_type,
3461 column_name,
3462 error_value,
3463 error_message_name,
3464 table_name,
3465 batch_id,
3466 interface_line_id)
3467 select interface_type,
3468 fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3469 bid_start_price,
3470 'PON_AUCTS_START_LT_RESERVE',
3471 'PON_ITEM_PRICES_INTERFACE',
3472 batch_id,
3473 interface_line_id
3474 from pon_item_prices_interface
3475 where reserve_price < bid_start_price AND
3476 batch_id = p_batch_id;
3477 --
3478 -- reserve price < target price
3479 -- Seller AUCTION only --
3480 --
3481 insert into PON_INTERFACE_ERRORS
3482 (interface_type,
3483 column_name,
3484 error_value,
3485 error_message_name,
3486 table_name,
3487 batch_id,
3488 interface_line_id)
3489 select interface_type,
3490 fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3491 reserve_price,
3492 'PON_AUCTS_RESERVE_LT_TARGET',
3493 'PON_ITEM_PRICES_INTERFACE',
3494 batch_id,
3495 interface_line_id
3496 from pon_item_prices_interface
3497 where reserve_price > target_price AND
3498 batch_id = p_batch_id;
3499 --
3500 END IF;
3501 --
3502
3503 -- unit target price NOT < 0
3504 -- AUCTION only
3505
3506 insert into PON_INTERFACE_ERRORS
3507 (interface_type,
3508 column_name,
3509 error_value,
3510 error_message_name,
3511 table_name,
3512 batch_id,
3513 interface_line_id)
3514 select interface_type,
3515 fnd_message.get_string('PON','PON_ITEM_PRICE_TARGET_VALUE'),
3516 unit_target_price,
3517 'PON_AUC_POSITIVE_OR_ZERO',
3518 'PON_ITEM_PRICES_INTERFACE',
3519 batch_id,
3520 interface_line_id
3521 from pon_item_prices_interface
3522 where UNIT_TARGET_PRICE < 0
3523 and nvl(price_and_quantity_apply, 'Y') = 'Y'
3524 and batch_id = p_batch_id;
3525
3526 -- clm validations
3527 insert into PON_INTERFACE_ERRORS
3528 (interface_type,
3529 column_name,
3530 error_message_name,
3531 table_name,
3532 batch_id,
3533 interface_line_id)
3534 select interface_type,
3535 fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
3536 'PON_FIELD_MUST_BE_ENTERED',
3537 'PON_ITEM_PRICES_INTERFACE',
3538 batch_id,
3539 interface_line_id
3540 from pon_item_prices_interface
3541 where CLM_BASE_LINE_NUM IS NOT NULL
3542 AND CLM_OPTION_FROM_DATE IS NULL
3543 and batch_id = p_batch_id;
3544
3545 insert into PON_INTERFACE_ERRORS
3546 (interface_type,
3547 column_name,
3548 error_message_name,
3549 table_name,
3550 batch_id,
3551 interface_line_id)
3552 select interface_type,
3553 fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
3554 'PON_FIELD_MUST_BE_ENTERED',
3555 'PON_ITEM_PRICES_INTERFACE',
3556 batch_id,
3557 interface_line_id
3558 from pon_item_prices_interface
3559 where CLM_BASE_LINE_NUM IS NOT NULL
3560 AND CLM_OPTION_TO_DATE IS NULL
3561 and batch_id = p_batch_id;
3562
3563 insert into PON_INTERFACE_ERRORS
3564 (interface_type,
3565 column_name,
3566 error_value,
3567 error_message_name,
3568 table_name,
3569 batch_id,
3570 interface_line_id)
3571 select interface_type,
3572 fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
3573 CLM_OPTION_FROM_DATE,
3574 'PON_CLM_OPT_FROMDATE_ERR',
3575 'PON_ITEM_PRICES_INTERFACE',
3576 batch_id,
3577 interface_line_id
3578 from pon_item_prices_interface
3579 WHERE CLM_BASE_LINE_NUM IS NOT NULL
3580 AND CLM_OPTION_FROM_DATE < sysdate
3581 and batch_id = p_batch_id;
3582 --
3583 insert into PON_INTERFACE_ERRORS
3584 (interface_type,
3585 column_name,
3586 error_value,
3587 error_message_name,
3588 table_name,
3589 batch_id,
3590 interface_line_id)
3591 select interface_type,
3592 fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
3593 CLM_OPTION_TO_DATE,
3594 'PON_CLM_OPT_TODATE_ERR',
3595 'PON_ITEM_PRICES_INTERFACE',
3596 batch_id,
3597 interface_line_id
3598 from pon_item_prices_interface
3599 where CLM_BASE_LINE_NUM IS NOT NULL
3600 AND CLM_OPTION_TO_DATE < CLM_OPTION_FROM_DATE
3601 and batch_id = p_batch_id;
3602
3603
3604 --R12 - Added for Complex work
3605 IF p_contract_type IN ('STANDARD','CONTRACT') AND l_progress_payment_type <> 'NONE' THEN
3606 -- Call complex work validations only if progress_payment_type
3607 -- is NOT NONE
3608 validate_complexwork(p_batch_id, l_progress_payment_type, p_contract_type, l_advance_negotiable_flag, l_recoupment_negotiable_flag);
3609 END IF;
3610
3611 IF (l_is_fed = 'Y') THEN
3612 /* CLM_AMOUNT*/
3613 insert into PON_INTERFACE_ERRORS
3614 (interface_type,
3615 column_name,
3616 error_value,
3617 error_message_name,
3618 table_name,
3619 batch_id,
3620 interface_line_id)
3621 (select interface_type,
3622 fnd_message.get_string('PON', 'PON_CLM_AMOUNT'),
3623 'Slins',
3624 'PON_CLM_AMOUNT_ERR',
3625 'PON_ITEM_PRICES_INTERFACE',
3626 batch_id,
3627 interface_line_id
3628 from pon_item_prices_interface P1
3629 where batch_id = p_batch_id
3630 AND P1.CLM_AMOUNT IS NOT NULL
3631 AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'AMOUNT')
3632 );
3633
3634 /* CLM_UNIT_PRICE */
3635 insert into PON_INTERFACE_ERRORS
3636 (interface_type,
3637 column_name,
3638 error_value,
3639 error_message_name,
3640 table_name,
3641 batch_id,
3642 interface_line_id)
3643 (select interface_type,
3644 fnd_message.get_string('PON', 'PON_CLM_UNIT_PRICE'),
3645 'Slins',
3646 'PON_CLM_UNIT_PRICE_ERR',
3647 'PON_ITEM_PRICES_INTERFACE',
3648 batch_id,
3649 interface_line_id
3650 from pon_item_prices_interface P1
3651 where batch_id = p_batch_id
3652 AND P1.CLM_UNIT_PRICE IS NOT NULL
3653 AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'QUANTITY')
3654 );
3655
3656
3657 /* CLM_CONTRACT_TYPE*/
3658 insert into PON_INTERFACE_ERRORS
3659 (interface_type,
3660 column_name,
3661 error_value,
3662 error_message_name,
3663 table_name,
3664 batch_id,
3665 interface_line_id)
3666 --Bug 16567154
3667 --Removing spaces in the error message
3668 (select interface_type,
3669 fnd_message.get_string('PON', 'PON_CLM_CONTRACT_TYPE'),
3670 'Slins',
3671 'PON_CLM_CONTRACT_TYPE_ERR',
3672 'PON_ITEM_PRICES_INTERFACE',
3673 batch_id,
3674 interface_line_id
3675 from pon_item_prices_interface
3676 where batch_id = p_batch_id
3677 AND CLM_CONTRACT_TYPE IS NOT NULL
3678 AND CLM_CONTRACT_TYPE NOT IN
3679 (SELECT LOOKUP_CODE
3680 FROM FND_LOOKUP_VALUES
3681 WHERE LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_QTY'
3682 AND LANGUAGE = USERENV('LANG')
3683 AND ENABLED_FLAG = 'Y'
3684 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
3685 ));
3686
3687
3688 /* CLM_COST_CONSTRAINT*/
3689 insert into PON_INTERFACE_ERRORS
3690 (interface_type,
3691 column_name,
3692 error_value,
3693 error_message_name,
3694 table_name,
3695 batch_id,
3696 interface_line_id)
3697 --Bug 16567154
3698 --Removing spaces in the error message
3699 (select interface_type,
3700 fnd_message.get_string('PON', 'PON_CLM_COST_CONSTRAINT'),
3701 'Slins',
3702 'PON_CLM_COST_CONSTRAINT_ERR',
3703 'PON_ITEM_PRICES_INTERFACE',
3704 batch_id,
3705 interface_line_id
3706 from pon_item_prices_interface
3707 where batch_id = p_batch_id
3708 AND CLM_COST_CONSTRAINT IS NOT NULL
3709 AND CLM_COST_CONSTRAINT NOT IN
3710 (SELECT LOOKUP_CODE
3711 FROM FND_LOOKUP_VALUES
3712 WHERE LOOKUP_TYPE = 'PO_FEDERAL_COST_CONSTRAINTS'
3713 AND LANGUAGE = USERENV('LANG')
3714 AND ENABLED_FLAG = 'Y'
3715 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
3716 ));
3717
3718
3719 /* CLM_BASE_LINE_NUM*/
3720 insert into PON_INTERFACE_ERRORS
3721 (interface_type,
3722 column_name,
3723 error_value,
3724 error_message_name,
3725 table_name,
3726 batch_id,
3727 interface_line_id)
3728 --Bug 16567154
3729 --Removing spaces in the error message
3730 (select interface_type,
3731 fnd_message.get_string('PON', 'PON_CLM_BASE_LINE_NUM'),
3732 'Slins',
3733 'PON_CLM_BASE_LINE_NUM_ERR',
3734 'PON_ITEM_PRICES_INTERFACE',
3735 batch_id,
3736 interface_line_id
3737 from pon_item_prices_interface P1
3738 where batch_id = p_batch_id
3739 AND P1.CLM_BASE_LINE_NUM IS NOT NULL
3740 --bug 16567154
3741 --Modifying the condition for option lines without base lines spreadsheet import
3742 AND (P1.CLM_BASE_LINE_NUM<>-9999)
3743 AND P1.CLM_BASE_LINE_NUM NOT IN
3744 (SELECT P2.AUCTION_LINE_NUMBER from
3745 pon_item_prices_interface P2
3746 where auction_header_id = l_auction_header_id )
3747 );
3748
3749
3750 /* CLM_OPTION_INDICATOR */
3751 insert into PON_INTERFACE_ERRORS
3752 (interface_type,
3753 column_name,
3754 error_value,
3755 error_message_name,
3756 table_name,
3757 batch_id,
3758 interface_line_id)
3759 --Bug 16567154
3760 --Removing spaces in the error message
3761 (select interface_type,
3762 fnd_message.get_string('PON', 'PON_CLM_OPTION_INDICATOR'),
3763 'Slins',
3764 'PON_CLM_OPTION_INDICATOR_ERR',
3765 'PON_ITEM_PRICES_INTERFACE',
3766 batch_id,
3767 interface_line_id
3768 from pon_item_prices_interface P1
3769 where batch_id = p_batch_id
3770 AND P1.CLM_OPTION_INDICATOR = 'Y' AND
3771 (P1.CLM_OPTION_FROM_DATE IS NULL OR P1.CLM_OPTION_TO_DATE IS NULL )
3772 );
3773
3774
3775 END IF;
3776 END IF; -- end of if p_source <> 'SBID'
3777 --
3778 -- Price Breaks ER
3779 PON_AUCTION_PKG.get_default_pb_settings (l_auction_header_id,
3780 l_price_break_type,
3781 l_price_break_neg_flag);
3782
3783 SELECT price_tiers_indicator INTO l_price_tiers_indicator FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
3784
3785 IF(l_price_tiers_indicator = 'PRICE_BREAKS' ) THEN
3786
3787 l_cumulative := pon_auction_pkg.getmessage('PON_AUC_CUMULATIVE');
3788 l_noncumulative := pon_auction_pkg.getmessage('PON_AUC_NON_CUMULATIVE');
3789 l_none:=pon_auction_pkg.getmessage('PON_AUC_NONE');
3790 l_required:=pon_auction_pkg.getmessage('PON_AUCTS_REQUIRED');
3791 l_optional:=pon_auction_pkg.getmessage('PON_AUCTS_OPTIONAL');
3792
3793
3794 -- Should not set values for Fixed Price Line types
3795 /* insert into PON_INTERFACE_ERRORS
3796 (interface_type,
3797 column_name,
3798 error_value,
3799 error_message_name,
3800 table_name,
3801 batch_id,
3802 interface_line_id)
3803 select interface_type,
3804 fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3805 null,
3806 'PON_AUCTS_PB_FP_LINE_ERR',
3807 'PON_ITEM_PRICES_INTERFACE',
3808 batch_id,
3809 interface_line_id
3810 from pon_item_prices_interface
3811 where ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3812 AND ( price_break_type IS NOT NULL OR price_break_neg_flag IS NOT NULL )
3813 AND Nvl(clm_info_flag,'N') <> 'Y'
3814 AND group_type <> 'GROUP'
3815 and batch_id = p_batch_id;
3816
3817 -- Update default PB setting for amt based and fixed price line types
3818 UPDATE pon_item_prices_interface p1
3819 SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
3820 WHERE ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3821 AND Nvl(clm_info_flag,'N') <> 'Y'
3822 AND group_type <> 'GROUP'
3823 and batch_id = p_batch_id;*/
3824
3825 UPDATE pon_item_prices_interface p1
3826 SET price_break_type = null,price_break_neg_flag = null
3827 WHERE ( ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3828 OR Nvl(clm_info_flag,'N') = 'Y'
3829 OR group_type = 'GROUP' )
3830 AND batch_id = p_batch_id;
3831
3832
3833 -- update the pb settings
3834 UPDATE pon_item_prices_interface p1
3835 SET p1.price_break_type =
3836 (SELECT Decode(Nvl(p2.price_break_type,'NULL'),'NULL',l_price_break_type,Decode(p2.price_break_neg_flag,l_none,'NONE',Decode(p2.price_break_type,l_cumulative,'CUMULATIVE',Decode(p2.price_break_type,l_noncumulative,'NON-CUMULATIVE',null))))
3837 FROM pon_item_prices_interface p2
3838 WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
3839 AND p2.batch_id = p_batch_id
3840 ),
3841 p1.price_break_neg_flag =
3842 (SELECT Decode(Nvl(p2.price_break_neg_flag,'NULL'),'NULL',l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_none,l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_required,'N',Decode(p2.price_break_neg_flag,l_optional,'Y',null))))
3843 FROM pon_item_prices_interface p2
3844 WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
3845 AND p2.batch_id = p_batch_id
3846 )
3847
3848 where ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3849 AND Nvl(clm_info_flag,'N') <> 'Y'
3850 AND group_type <> 'GROUP'
3851 and batch_id = p_batch_id;
3852
3853
3854 SELECT price_tiers_indicator INTO l_price_tiers_indicator FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
3855
3856 -- Validate price break seetings
3857 insert into PON_INTERFACE_ERRORS
3858 (interface_type,
3859 column_name,
3860 error_value,
3861 error_message_name,
3862 table_name,
3863 batch_id,
3864 interface_line_id)
3865 select interface_type,
3866 fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3867 null,
3868 'PON_INVALID_PB_SETTING',
3869 'PON_ITEM_PRICES_INTERFACE',
3870 batch_id,
3871 interface_line_id
3872 from pon_item_prices_interface
3873 where ( price_break_type IS NULL OR price_break_neg_flag IS NULL)
3874 AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3875 AND Nvl(clm_info_flag,'N') <> 'Y'
3876 AND group_type <> 'GROUP'
3877 and batch_id = p_batch_id;
3878
3879 -- Response type cannot be null if there are any shipments exists
3880 insert into PON_INTERFACE_ERRORS
3881 (interface_type,
3882 column_name,
3883 error_value,
3884 error_message_name,
3885 table_name,
3886 batch_id,
3887 interface_line_id)
3888 select interface_type,
3889 fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3890 null,
3891 'PON_AUC_PB_NOT_EMPTY',
3892 'PON_ITEM_PRICES_INTERFACE',
3893 batch_id,
3894 interface_line_id
3895 from pon_item_prices_interface p1
3896 where price_break_type = 'NONE'
3897 AND Nvl((SELECT has_shipments_flag FROM pon_auction_item_prices_all WHERE line_number = p1.auction_line_number AND auction_header_id = l_auction_header_id),'N') = 'Y'
3898 AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3899 AND Nvl(clm_info_flag,'N') <> 'Y'
3900 AND group_type <> 'GROUP'
3901 AND batch_id = p_batch_id;
3902
3903
3904 -- For Global agreements release quantity must be cumulative.
3905 INSERT INTO PON_INTERFACE_ERRORS
3906 (
3907 COLUMN_NAME ,
3908 INTERFACE_TYPE ,
3909 ERROR_MESSAGE_NAME,
3910 batch_id ,
3911 table_name ,
3912 AUCTION_HEADER_ID ,
3913 TOKEN1_NAME ,
3914 TOKEN1_VALUE ,
3915 interface_line_id
3916
3917 )
3918 select
3919 pon_auction_pkg.getMessage('PON_AGREEMENT_RELEASE_QUANTITY'),
3920 interface_type , --INTERFACE_TYPE
3921 'PON_AUC_BAD_PBTYPE_GLOBAL' , -- ERROR_MESSAGE_NAME
3922 batch_id , -- BATCH_ID
3923 'PON_ITEM_PRICES_INTERFACE' , -- ENTITY_TYPE
3924 auction_header_id , -- AUCTION_HEADER_ID
3925 'LINENUMBER' , -- TOKEN1_NAME
3926 null, -- TOKEN1_VALUE
3927 interface_line_id
3928 FROM pon_item_prices_interface p1
3929 where price_break_type = 'CUMULATIVE'
3930 AND Nvl((SELECT global_agreement_flag FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id),'N') = 'Y'
3931 AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3932 AND Nvl(clm_info_flag,'N') <> 'Y'
3933 AND group_type <> 'GROUP'
3934 AND batch_id = p_batch_id;
3935
3936 -- Temp based labor cannot have cumilative response
3937 insert into PON_INTERFACE_ERRORS
3938 (interface_type,
3939 column_name,
3940 error_value,
3941 error_message_name,
3942 table_name,
3943 batch_id,
3944 interface_line_id)
3945 select interface_type,
3946 fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'),
3947 null,
3948 'PON_AUCTS_PB_TBL_LINE_ERR',
3949 'PON_ITEM_PRICES_INTERFACE',
3950 batch_id,
3951 interface_line_id
3952 from pon_item_prices_interface p1
3953 where (PURCHASE_BASIS = 'TEMP LABOR' and order_type_lookup_code = 'RATE')
3954 AND price_break_type = 'CUMULATIVE'
3955 AND Nvl(clm_info_flag,'N') <> 'Y'
3956 AND group_type <> 'GROUP'
3957 AND batch_id = p_batch_id;
3958
3959
3960
3961 ELSE
3962 UPDATE pon_item_prices_interface p1
3963 SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
3964 WHERE batch_id = p_batch_id;
3965
3966 END IF;
3967
3968 -- DBMS_OUTPUT.PUT_LINE('< validate()');
3969 END validate;
3970 --
3971
3972 END pon_validate_item_prices_int;