[Home] [Help]
44:
45:
46: PROCEDURE validate_xml_req_values
47: (
48: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
49: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
50: p_user_id IN pon_interface_errors.created_by%TYPE,
51: p_suffix IN VARCHAR2,
52: p_batch_id IN pon_interface_errors.batch_id%TYPE,
45:
46: PROCEDURE validate_xml_req_values
47: (
48: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
49: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
50: p_user_id IN pon_interface_errors.created_by%TYPE,
51: p_suffix IN VARCHAR2,
52: p_batch_id IN pon_interface_errors.batch_id%TYPE,
53: p_request_id IN pon_interface_errors.request_id%TYPE);
54:
55:
56: PROCEDURE validate_xml_attr_values
57: (
58: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
59: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
60: p_user_id IN pon_interface_errors.created_by%TYPE,
61: p_suffix IN VARCHAR2,
62: p_batch_id IN pon_interface_errors.batch_id%TYPE,
55:
56: PROCEDURE validate_xml_attr_values
57: (
58: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
59: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
60: p_user_id IN pon_interface_errors.created_by%TYPE,
61: p_suffix IN VARCHAR2,
62: p_batch_id IN pon_interface_errors.batch_id%TYPE,
63: p_request_id IN pon_interface_errors.request_id%TYPE);
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'
169: SET bli.line_number = g_skip_int
170: WHERE bli.batch_id = p_batch_id
171: AND EXISTS
172: (SELECT 'Y'
173: FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
174: WHERE bl.bid_number = bli.bid_number
175: AND bl.line_number = bli.line_number
176: AND al.auction_header_id = bl.auction_header_id
177: AND al.line_number = bl.line_number
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:
389: END remove_invalid_skipped_lines;
390:
391: PROCEDURE validate_attribute_datatypes
392: (
393: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
394: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
395: p_userid IN pon_interface_errors.created_by%TYPE,
396: p_suffix IN VARCHAR2,
397: p_batch_id IN pon_interface_errors.batch_id%TYPE,
390:
391: PROCEDURE validate_attribute_datatypes
392: (
393: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
394: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
395: p_userid IN pon_interface_errors.created_by%TYPE,
396: p_suffix IN VARCHAR2,
397: p_batch_id IN pon_interface_errors.batch_id%TYPE,
398: p_request_id IN pon_interface_errors.request_id%TYPE
533: END validate_attribute_datatypes;
534:
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,
534:
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,
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
602: AND (bai.bid_number, bai.line_number, bai.sequence_number) in
603: (
604: select bh.bid_number, bh.line_number, aa.sequence_number
605: from pon_auction_attributes aa
606: , pon_bid_item_prices bh
607: where bh.bid_number = bai.bid_number
608: and bh.line_number = bai.line_number
609: and aa.auction_header_id = bh.auction_header_id
610: and aa.line_number = bh.line_number
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
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
734: AND bip.display_price_factors_flag = 'Y'
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,
904: al.quantity_disabled_flag,
905: al.price_disabled_flag,
906: al.group_type,
907: al.order_type_lookup_code
908: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
909: WHERE bl.bid_number = p_bid_number
910: AND al.auction_header_id = bl.auction_header_id
911: AND al.line_number = bl.line_number) lines
912: ON (bli.bid_number = lines.bid_number
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,
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,
954: p_blanket IN VARCHAR2,
955: p_mas IN VARCHAR2,
956: p_progress_payment_type IN VARCHAR2,
965:
966: BEGIN
967:
968: -- Update lines table with values in the interface table
969: MERGE INTO pon_bid_item_prices bl
970: USING
971: (SELECT
972: pbip.bid_number,
973: pbip.line_number,
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
1021: bl.worksheet_name = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_name,to_char(null)),
1022: bl.worksheet_sequence_number = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_sequence_number,to_number(null));
1023:
1024: -- When a GROUP's child recieved a bid, we mark that group as part of the batch
1025: UPDATE pon_bid_item_prices bl
1026: SET batch_id = p_batch_id
1027: WHERE bl.bid_number = p_bid_number
1028: AND (SELECT al.group_type
1029: FROM pon_auction_item_prices_all al
1030: WHERE al.auction_header_id = bl.auction_header_id
1031: AND al.line_number = bl.line_number) = 'GROUP'
1032: AND EXISTS
1033: (SELECT bl2.line_number
1034: FROM pon_bid_item_prices bl2, pon_auction_item_prices_all al2
1035: WHERE bl2.bid_number = p_bid_number
1036: AND al2.auction_header_id = bl2.auction_header_id
1037: AND al2.line_number = bl2.line_number
1038: AND al2.parent_line_number = bl.line_number
1075: -- Update promised_date
1076: UPDATE pon_bid_attribute_values ba
1077: SET value =
1078: nvl((SELECT to_char(bl.promised_date, 'dd-mm-yyyy hh24:mi:ss')
1079: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1080: WHERE bl.bid_number = ba.bid_number
1081: AND bl.line_number = ba.line_number
1082: AND al.auction_header_id = bl.auction_header_id
1083: AND al.line_number = bl.line_number
1088: -- Update quantity
1089: UPDATE pon_bid_attribute_values ba
1090: SET value =
1091: nvl((SELECT to_char(bl.quantity)
1092: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1093: WHERE bl.bid_number = ba.bid_number
1094: AND bl.line_number = ba.line_number
1095: AND al.auction_header_id = bl.auction_header_id
1096: AND al.line_number = bl.line_number
1133: bpf.last_updated_by = p_userid,
1134: bpf.interface_line_id = bpfi.interface_line_id;
1135:
1136: -- Sync bid_currency_unit_price from price factors to lines table
1137: UPDATE pon_bid_item_prices bl
1138: SET bl.bid_currency_unit_price =
1139: (SELECT bpf.bid_currency_value
1140: FROM pon_bid_price_elements bpf
1141: WHERE bpf.bid_number = bl.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
1357:
1358: SELECT nvl(max(seq_num), 0) + 1
1359: INTO l_seq_num
1360: FROM fnd_attached_documents
1361: WHERE entity_name = 'PON_BID_ITEM_PRICES'
1362: AND pk1_value = p_auc_header_id
1363: AND pk2_value = p_bid_number
1364: AND pk3_value = line.line_number;
1365:
1368: l_target_category,
1369: line.attachment_desc,
1370: 5,
1371: line.attachment_url,
1372: 'PON_BID_ITEM_PRICES',
1373: p_auc_header_id,
1374: p_bid_number,
1375: line.line_number,
1376: null,
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
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
1523: ) IS
1524:
1525: l_loginid NUMBER;
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
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
1890: )
1891: IS
1892: l_interface_type pon_interface_errors.interface_type%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,
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,
1982: p_suffix IN VARCHAR2,
1983: p_has_pe IN pon_auction_item_prices_all.HAS_PRICE_ELEMENTS_FLAG%TYPE,
1984: l_attr_enabled_flag IN pon_auction_headers_all.line_attribute_enabled_flag%TYPE,
1985: l_req_enabled_flag IN pon_auction_headers_all.hdr_attribute_enabled_flag%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
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
2072: AND bli.line_number = bip.line_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'
2109: SET bli.interface_line_id = g_skip_int
2110: WHERE bli.batch_id = p_batch_id
2111: AND EXISTS
2112: (SELECT 'Y'
2113: FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
2114: WHERE bl.bid_number = bli.bid_number
2115: AND bl.line_number = bli.line_number
2116: AND al.auction_header_id = bl.auction_header_id
2117: AND al.line_number = bl.line_number
2132: OR al.order_type_lookup_code = 'AMOUNT'))
2133: AND bli.note_to_auction_owner IS null
2134: AND bli.promised_date IS null
2135: -- If the values of these columns are NOT same as that in the transaction table
2136: -- (pon_bid_item_prices) then DO NOT skip.
2137: -- If the values are same as that in the transaction table then skip.
2138: -- You do not have to check about optional or required pay items.
2139: AND NVL(bli.recoupment_rate_percent, -9999) = NVL(bl.recoupment_rate_percent, -9999)
2140: AND NVL(bli.bid_curr_advance_amount, -9999) = NVL(bl.bid_curr_advance_amount, -9999)
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:
2277: END remove_xml_skipped_lines;
2278:
2279: PROCEDURE validate_xml_req_values
2280: (
2281: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2282: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2283: p_user_id IN pon_interface_errors.created_by%TYPE,
2284: p_suffix IN VARCHAR2,
2285: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2278:
2279: PROCEDURE validate_xml_req_values
2280: (
2281: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2282: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2283: p_user_id IN pon_interface_errors.created_by%TYPE,
2284: p_suffix IN VARCHAR2,
2285: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2286: p_request_id IN pon_interface_errors.request_id%TYPE
2468:
2469:
2470: PROCEDURE validate_xml_attr_values
2471: (
2472: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2473: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2474: p_user_id IN pon_interface_errors.created_by%TYPE,
2475: p_suffix IN VARCHAR2,
2476: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2469:
2470: PROCEDURE validate_xml_attr_values
2471: (
2472: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2473: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2474: p_user_id IN pon_interface_errors.created_by%TYPE,
2475: p_suffix IN VARCHAR2,
2476: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2477: p_request_id IN pon_interface_errors.request_id%TYPE
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
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
2686: )
2687: is
2742: bsi.price_discount,
2743: bsi.bid_currency_unit_price,
2744: bip.bid_currency_unit_price item_price
2745: FROM pon_bid_shipments_int bsi,
2746: pon_bid_item_prices bip
2747: WHERE bsi.batch_id = p_batch_id
2748: and bsi.bid_number = p_bid_number
2749: and bsi.bid_number = bip.bid_number
2750: and bsi.auction_header_id = bip.auction_header_id
3012: bsd.shipment_number<>-1
3013: and bsd.bid_number=p_bid_number
3014: and bsd.line_number in
3015: (select bip.line_number
3016: from pon_bid_item_prices bip
3017: where bip.batch_id=p_batch_id
3018: and bip.bid_number=p_bid_number
3019: )
3020: and
3021: (bsd.bid_number, bsd.line_number, bsd.shipment_number)
3022: not in
3023: (
3024: select bsh.bid_number, bsh.line_number, bsh.shipment_number
3025: from pon_bid_shipments bsh,pon_bid_item_prices bip
3026: where bip.batch_id = p_batch_id
3027: and bip.bid_number = p_bid_number
3028: and bip.bid_number = bsh.bid_number
3029: and bip.line_number = bsh.line_number
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,