[Home] [Help]
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
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
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:
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
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
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
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
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
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
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
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
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
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
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
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
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
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