[Home] [Help]
81: END get_message_1_token;
82:
83: PROCEDURE validate_close_bidding_date
84: (
85: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
86: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
87: p_request_id IN pon_bid_headers.request_id%TYPE,
88: p_userid IN pon_interface_errors.created_by%TYPE,
89: p_suffix IN VARCHAR2
82:
83: PROCEDURE validate_close_bidding_date
84: (
85: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
86: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
87: p_request_id IN pon_bid_headers.request_id%TYPE,
88: p_userid IN pon_interface_errors.created_by%TYPE,
89: p_suffix IN VARCHAR2
90: ) IS
100: FROM pon_auction_headers_all ah
101: WHERE ah.auction_header_id = p_auc_header_id;
102:
103: -- Flag all lines with expired close bidding dates
104: UPDATE pon_bid_item_prices_interface bli
105: SET bli.line_number = g_closed_int
106: WHERE bli.batch_id = p_batch_id
107: AND bli.line_number <> g_error_int
108: AND bli.line_number <> g_skip_int
147: bli.auction_header_id,
148: bli.bid_number,
149: bli.line_number,
150: g_exp_date
151: FROM pon_bid_item_prices_interface bli
152: WHERE bli.batch_id = p_batch_id
153: AND bli.line_number = g_closed_int);
154:
155: END validate_close_bidding_date;
155: END validate_close_bidding_date;
156:
157: PROCEDURE determine_skipped_lines
158: (
159: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
160: p_full_qty IN VARCHAR2
161: ) IS
162: BEGIN
163:
164: -- PRECONDITIONS:
165: -- line_number are set for lines; it need not be set for children
166:
167: -- Determine if any lines can be ignored
168: UPDATE pon_bid_item_prices_interface bli
169: SET bli.line_number = g_skip_int
170: WHERE bli.batch_id = p_batch_id
171: AND EXISTS
172: (SELECT 'Y'
226: END determine_skipped_lines;
227:
228: PROCEDURE remove_invalid_skipped_lines
229: (
230: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
231: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
232: p_request_id IN pon_bid_headers.request_id%TYPE,
233: p_userid IN pon_interface_errors.created_by%TYPE,
234: p_full_qty IN VARCHAR2,
227:
228: PROCEDURE remove_invalid_skipped_lines
229: (
230: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
231: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
232: p_request_id IN pon_bid_headers.request_id%TYPE,
233: p_userid IN pon_interface_errors.created_by%TYPE,
234: p_full_qty IN VARCHAR2,
235: p_buyer_user IN VARCHAR2,
238: BEGIN
239:
240: -- 1. LINES
241:
242: -- document_disp_line_number in pon_bid_item_prices_interface is the
243: -- line number specified in the spreadsheet. Since we do not lookup
244: -- the internal line number in the middle tier any longer, we
245: -- need to do that now and populate the children interface tables.
246: -- NOTE: if the line has an invalid document number, line_number is
244: -- the internal line number in the middle tier any longer, we
245: -- need to do that now and populate the children interface tables.
246: -- NOTE: if the line has an invalid document number, line_number is
247: -- set to g_error_int to indicate this
248: UPDATE pon_bid_item_prices_interface bli
249: SET bli.line_number =
250: nvl((SELECT al.line_number
251: FROM pon_auction_item_prices_all al
252: WHERE al.auction_header_id = bli.auction_header_id
286: 'TXT',
287: bli.auction_header_id,
288: bli.bid_number,
289: g_exp_date
290: FROM pon_bid_item_prices_interface bli
291: WHERE bli.batch_id = p_batch_id
292: AND bli.line_number = g_error_int);
293:
294: -- Determine if there are any lines to be skipped, mark them so.
314: DELETE FROM pon_bid_attr_values_interface bai
315: WHERE bai.batch_id = p_batch_id
316: AND bai.interface_line_id in (
317: select bli.interface_line_id
318: from pon_bid_item_prices_interface bli
319: where bli.batch_id = p_batch_id
320: AND (bli.line_number = g_error_int
321: OR bli.line_number = g_skip_int));
322:
324: DELETE FROM pon_bid_price_elements_int bpfi
325: WHERE bpfi.batch_id = p_batch_id
326: AND bpfi.interface_line_id in (
327: select bli.interface_line_id
328: from pon_bid_item_prices_interface bli
329: where bli.batch_id = p_batch_id
330: AND (bli.line_number = g_error_int
331: OR bli.line_number = g_skip_int));
332:
334: DELETE FROM pon_bid_price_differ_int bpdi
335: WHERE bpdi.batch_id = p_batch_id
336: AND bpdi.interface_line_id in (
337: select bli.interface_line_id
338: from pon_bid_item_prices_interface bli
339: where bli.batch_id = p_batch_id
340: AND (bli.line_number = g_error_int
341: OR bli.line_number = g_skip_int));
342:
341: OR bli.line_number = g_skip_int));
342:
343: -- Delete from lines interface table
344:
345: DELETE FROM pon_bid_item_prices_interface bli
346: WHERE bli.batch_id = p_batch_id
347: AND (bli.line_number = g_error_int
348: OR bli.line_number = g_skip_int);
349:
354: -- valid line_numbers
355: UPDATE pon_bid_attr_values_interface bai
356: SET bai.line_number =
357: (SELECT bli.line_number
358: FROM pon_bid_item_prices_interface bli
359: WHERE bli.batch_id = bai.batch_id
360: AND bli.interface_line_id = bai.interface_line_id)
361: WHERE bai.batch_id = p_batch_id;
362:
367: -- valid line_numbers
368: UPDATE pon_bid_price_elements_int bpfi
369: SET bpfi.line_number =
370: (SELECT bli.line_number
371: FROM pon_bid_item_prices_interface bli
372: WHERE bli.batch_id = bpfi.batch_id
373: AND bli.interface_line_id = bpfi.interface_line_id)
374: WHERE bpfi.batch_id = p_batch_id;
375:
380: -- valid line_numbers
381: UPDATE pon_bid_price_differ_int bpdi
382: SET bpdi.auction_line_number =
383: (SELECT bli.line_number
384: FROM pon_bid_item_prices_interface bli
385: WHERE bli.batch_id = bpdi.batch_id
386: AND bli.interface_line_id = bpdi.interface_line_id)
387: WHERE bpdi.batch_id = p_batch_id;
388:
535: PROCEDURE validate_children
536: (
537: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
538: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
539: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
540: p_request_id IN pon_bid_headers.request_id%TYPE,
541: p_userid IN pon_interface_errors.created_by%TYPE,
542: p_has_pe IN VARCHAR2,
543: p_suffix IN VARCHAR2
725: bli.auction_header_id,
726: bli.bid_number,
727: bli.line_number,
728: g_exp_date
729: FROM pon_bid_item_prices_interface bli,
730: pon_bid_item_prices bip
731: WHERE bli.batch_id = p_batch_id
732: AND bli.bid_number = bip.bid_number
733: AND bli.line_number = bip.line_number
874: END validate_children;
875:
876: PROCEDURE default_from_auction
877: (
878: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
879: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
880: p_bid_number IN pon_bid_headers.bid_number%TYPE,
881: p_full_qty IN VARCHAR2,
882: p_blanket IN VARCHAR2,
875:
876: PROCEDURE default_from_auction
877: (
878: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
879: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
880: p_bid_number IN pon_bid_headers.bid_number%TYPE,
881: p_full_qty IN VARCHAR2,
882: p_blanket IN VARCHAR2,
883: p_auc_closed IN VARCHAR2
886:
887: -- If it is a super large negotiation, then proxy is not allowed
888: IF (pon_large_auction_util_pkg.is_super_large_neg(p_auc_header_id)) THEN
889:
890: UPDATE pon_bid_item_prices_interface
891: SET bid_currency_limit_price = null
892: WHERE batch_id = p_batch_id;
893:
894: END IF;
893:
894: END IF;
895:
896: -- Default some values in the interface table for each line
897: MERGE INTO pon_bid_item_prices_interface bli
898: USING
899: (SELECT bl.bid_number,
900: bl.line_number,
901: bl.display_price_factors_flag,
945: END default_from_auction;
946:
947: PROCEDURE copy_interface_to_txn_tables
948: (
949: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
950: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
951: p_bid_number IN pon_bid_headers.bid_number%TYPE,
952: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
953: p_hdr_disp_pf IN VARCHAR2,
990: pah.max_retainage_negotiable_flag,
991: pah.recoupment_negotiable_flag,
992: pbip.worksheet_name,
993: pbip.worksheet_sequence_number
994: FROM pon_bid_item_prices_interface pbip,
995: pon_auction_headers_all pah
996: WHERE batch_id = p_batch_id
997: AND pah.auction_header_id = pbip.auction_header_id) bli
998: ON (bl.bid_number = bli.bid_number
1296: END IF;
1297:
1298:
1299: -- Remove all records for the current batch from the interface table
1300: DELETE FROM pon_bid_item_prices_interface bli
1301: WHERE bli.batch_id = p_batch_id;
1302:
1303: DELETE FROM pon_bid_attr_values_interface bai
1304: WHERE bai.batch_id = p_batch_id;
1315: END copy_interface_to_txn_tables;
1316:
1317: PROCEDURE create_url_attachments
1318: (
1319: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1320: p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
1321: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1322: p_userid IN pon_bid_headers.created_by%TYPE
1323: ) IS
1328: CURSOR attachment_lines IS
1329: SELECT bli.attachment_url,
1330: bli.attachment_desc,
1331: bli.line_number
1332: FROM pon_bid_item_prices_interface bli
1333: WHERE bli.batch_id = p_batch_id
1334: AND bli.attachment_desc IS NOT null
1335: AND bli.attachment_url IS NOT null;
1336: BEGIN
1380: END create_url_attachments;
1381:
1382: PROCEDURE process_spreadsheet_data
1383: (
1384: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1385: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1386: p_request_id IN pon_bid_headers.request_id%TYPE,
1387: p_userid IN pon_interface_errors.created_by%TYPE,
1388: x_return_status OUT NOCOPY VARCHAR2,
1514: */
1515:
1516: PROCEDURE VALIDATE_HEADER
1517: (
1518: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1519: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1520: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1521: p_request_id IN pon_bid_item_prices.request_id%TYPE,
1522: p_user_id IN pon_interface_errors.created_by%TYPE
1881: END VALIDATE_HEADER;
1882:
1883: PROCEDURE validate_xml_price_breaks
1884: (
1885: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1886: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1887: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1888: p_request_id IN pon_bid_item_prices.request_id%TYPE,
1889: p_user_id IN pon_interface_errors.created_by%TYPE
1958: bipi.worksheet_name s_worksheet_name,
1959: bipi.worksheet_sequence_number s_worksheet_seq_num,
1960: 'PON_AUCTS_PRICE_BREAKS' s_entity_name
1961: from
1962: pon_bid_item_prices_interface bipi,
1963: pon_bid_shipments_int bshi
1964: where bshi.batch_id = p_batch_id
1965: and bshi.bid_number = p_bid_number
1966: and bshi.action in (g_pb_required, g_pb_optional, g_pb_new)
1973:
1974:
1975: PROCEDURE VALIDATE_XML
1976: (
1977: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1978: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1979: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1980: p_request_id IN pon_bid_headers.request_id%TYPE,
1981: p_user_id IN pon_bid_item_prices.last_updated_by%TYPE,
2063: bli.line_number,
2064: g_exp_date,
2065: bpei.worksheet_name,
2066: bpei.worksheet_sequence_number
2067: FROM pon_bid_item_prices_interface bli,
2068: pon_bid_item_prices bip,
2069: pon_bid_price_elements_int bpei
2070: WHERE bli.batch_id = p_batch_id
2071: AND bli.bid_number = bip.bid_number
2095: -- 1) Change that is common for existing child entities: Joining key of interface_line_id should be changed to line ID
2096: -- 2) New logic should be added for new entities like Price Break, Price Break Level PD, Payment etc,
2097: PROCEDURE determine_xml_skipped_lines
2098: (
2099: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2100: p_full_qty IN VARCHAR2
2101: ) IS
2102: BEGIN
2103:
2104: -- PRECONDITIONS:
2105: -- line_number are set for lines; it need not be set for children
2106:
2107: -- Determine if any lines can be ignored
2108: UPDATE pon_bid_item_prices_interface bli
2109: SET bli.interface_line_id = g_skip_int
2110: WHERE bli.batch_id = p_batch_id
2111: AND EXISTS
2112: (SELECT 'Y'
2199:
2200:
2201: PROCEDURE remove_xml_skipped_lines
2202: (
2203: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
2204: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2205: p_request_id IN pon_bid_headers.request_id%TYPE,
2206: p_userid IN pon_interface_errors.created_by%TYPE,
2207: p_full_qty IN VARCHAR2,
2200:
2201: PROCEDURE remove_xml_skipped_lines
2202: (
2203: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
2204: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2205: p_request_id IN pon_bid_headers.request_id%TYPE,
2206: p_userid IN pon_interface_errors.created_by%TYPE,
2207: p_full_qty IN VARCHAR2,
2208: p_buyer_user IN VARCHAR2,
2224: DELETE FROM pon_bid_attr_values_interface bai
2225: WHERE bai.batch_id = p_batch_id
2226: AND bai.line_number in (
2227: select bli.line_number
2228: from pon_bid_item_prices_interface bli
2229: where bli.batch_id = p_batch_id
2230: AND bli.interface_line_id = g_skip_int);
2231:
2232: -- Delete from price elements interface table
2233: DELETE FROM pon_bid_price_elements_int bpfi
2234: WHERE bpfi.batch_id = p_batch_id
2235: AND bpfi.line_number in (
2236: select bli.line_number
2237: from pon_bid_item_prices_interface bli
2238: where bli.batch_id = p_batch_id
2239: AND bli.interface_line_id = g_skip_int);
2240:
2241: -- Delete from price differentials interface table
2242: DELETE FROM pon_bid_price_differ_int bpdi
2243: WHERE bpdi.batch_id = p_batch_id
2244: AND bpdi.auction_line_number in (
2245: select bli.line_number
2246: from pon_bid_item_prices_interface bli
2247: where bli.batch_id = p_batch_id
2248: AND bli.interface_line_id = g_skip_int);
2249:
2250: -- Delete from price breaks / price tiers interface table
2251: DELETE FROM pon_bid_shipments_int bshi
2252: WHERE bshi.batch_id = p_batch_id
2253: AND bshi.line_number in (
2254: select bli.line_number
2255: from pon_bid_item_prices_interface bli
2256: where bli.batch_id = p_batch_id
2257: AND bli.interface_line_id = g_skip_int);
2258:
2259: -- Delete from payments interface table
2260: DELETE FROM pon_bid_payments_interface pbpi
2261: WHERE pbpi.batch_id = p_batch_id
2262: AND pbpi.document_disp_line_number in (
2263: select pai.document_disp_line_number
2264: from pon_bid_item_prices_interface bli,
2265: pon_auction_item_prices_all pai
2266: where bli.batch_id = p_batch_id
2267: AND bli.interface_line_id = g_skip_int
2268: AND pai.auction_header_id = bli.auction_header_id
2268: AND pai.auction_header_id = bli.auction_header_id
2269: AND pai.line_number = bli.line_number);
2270:
2271: -- Delete all bid lines from interface table
2272: DELETE FROM pon_bid_item_prices_interface bli
2273: WHERE bli.batch_id = p_batch_id
2274: AND bli.interface_line_id = g_skip_int;
2275:
2276:
2677:
2678:
2679: PROCEDURE copy_shipment_interface_to_txn
2680: (
2681: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2682: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2683: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
2684: p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
2685: p_shipment_type IN pon_bid_shipments.shipment_type%TYPE
3038:
3039:
3040: PROCEDURE process_xml_spreadsheet_data
3041: (
3042: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
3043: p_bid_number IN pon_bid_headers.bid_number%TYPE,
3044: p_request_id IN pon_bid_headers.request_id%TYPE,
3045: p_user_id IN pon_interface_errors.created_by%TYPE,
3046: x_return_status OUT NOCOPY NUMBER,