[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
228: END determine_skipped_lines;
229:
230: PROCEDURE remove_invalid_skipped_lines
231: (
232: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
233: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
234: p_request_id IN pon_bid_headers.request_id%TYPE,
235: p_userid IN pon_interface_errors.created_by%TYPE,
236: p_full_qty IN VARCHAR2,
229:
230: PROCEDURE remove_invalid_skipped_lines
231: (
232: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
233: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
234: p_request_id IN pon_bid_headers.request_id%TYPE,
235: p_userid IN pon_interface_errors.created_by%TYPE,
236: p_full_qty IN VARCHAR2,
237: p_buyer_user IN VARCHAR2,
264:
265:
266: -- 1. LINES
267:
268: -- document_disp_line_number in pon_bid_item_prices_interface is the
269: -- line number specified in the spreadsheet. Since we do not lookup
270: -- the internal line number in the middle tier any longer, we
271: -- need to do that now and populate the children interface tables.
272: -- NOTE: if the line has an invalid document number, line_number is
270: -- the internal line number in the middle tier any longer, we
271: -- need to do that now and populate the children interface tables.
272: -- NOTE: if the line has an invalid document number, line_number is
273: -- set to g_error_int to indicate this
274: UPDATE pon_bid_item_prices_interface bli
275: SET bli.line_number =
276: nvl((SELECT al.line_number
277: FROM pon_auction_item_prices_all al
278: WHERE al.auction_header_id = bli.auction_header_id
313: 'TXT',
314: bli.auction_header_id,
315: bli.bid_number,
316: g_exp_date
317: FROM pon_bid_item_prices_interface bli
318: WHERE bli.batch_id = p_batch_id
319: AND bli.line_number = g_error_int);
320:
321: -- Determine if there are any lines to be skipped, mark them so.
341: DELETE FROM pon_bid_attr_values_interface bai
342: WHERE bai.batch_id = p_batch_id
343: AND bai.interface_line_id in (
344: select bli.interface_line_id
345: 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:
351: DELETE FROM pon_bid_price_elements_int bpfi
352: WHERE bpfi.batch_id = p_batch_id
353: AND bpfi.interface_line_id in (
354: select bli.interface_line_id
355: from pon_bid_item_prices_interface bli
356: where bli.batch_id = p_batch_id
357: AND (bli.line_number = g_error_int
358: OR bli.line_number = g_skip_int));
359:
361: DELETE FROM pon_bid_price_differ_int bpdi
362: WHERE bpdi.batch_id = p_batch_id
363: AND bpdi.interface_line_id in (
364: select bli.interface_line_id
365: from pon_bid_item_prices_interface bli
366: where bli.batch_id = p_batch_id
367: AND (bli.line_number = g_error_int
368: OR bli.line_number = g_skip_int));
369:
368: OR bli.line_number = g_skip_int));
369:
370: -- Delete from lines interface table
371:
372: DELETE FROM pon_bid_item_prices_interface bli
373: WHERE bli.batch_id = p_batch_id
374: AND (bli.line_number = g_error_int
375: OR bli.line_number = g_skip_int);
376:
381: -- valid line_numbers
382: UPDATE pon_bid_attr_values_interface bai
383: SET bai.line_number =
384: (SELECT bli.line_number
385: FROM pon_bid_item_prices_interface bli
386: WHERE bli.batch_id = bai.batch_id
387: AND bli.interface_line_id = bai.interface_line_id)
388: WHERE bai.batch_id = p_batch_id;
389:
394: -- valid line_numbers
395: UPDATE pon_bid_price_elements_int bpfi
396: SET bpfi.line_number =
397: (SELECT bli.line_number
398: FROM pon_bid_item_prices_interface bli
399: WHERE bli.batch_id = bpfi.batch_id
400: AND bli.interface_line_id = bpfi.interface_line_id)
401: WHERE bpfi.batch_id = p_batch_id;
402:
407: -- valid line_numbers
408: UPDATE pon_bid_price_differ_int bpdi
409: SET bpdi.auction_line_number =
410: (SELECT bli.line_number
411: FROM pon_bid_item_prices_interface bli
412: WHERE bli.batch_id = bpdi.batch_id
413: AND bli.interface_line_id = bpdi.interface_line_id)
414: WHERE bpdi.batch_id = p_batch_id;
415:
416: END remove_invalid_skipped_lines;
417:
418: PROCEDURE validate_attribute_datatypes
419: (
420: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
421: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
422: p_userid IN pon_interface_errors.created_by%TYPE,
423: p_suffix IN VARCHAR2,
424: p_batch_id IN pon_interface_errors.batch_id%TYPE,
417:
418: PROCEDURE validate_attribute_datatypes
419: (
420: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
421: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
422: p_userid IN pon_interface_errors.created_by%TYPE,
423: p_suffix IN VARCHAR2,
424: p_batch_id IN pon_interface_errors.batch_id%TYPE,
425: p_request_id IN pon_interface_errors.request_id%TYPE
610: END validate_attribute_datatypes;
611:
612: PROCEDURE validate_children
613: (
614: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
615: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
616: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
617: p_request_id IN pon_bid_headers.request_id%TYPE,
618: p_userid IN pon_interface_errors.created_by%TYPE,
611:
612: PROCEDURE validate_children
613: (
614: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
615: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
616: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
617: p_request_id IN pon_bid_headers.request_id%TYPE,
618: p_userid IN pon_interface_errors.created_by%TYPE,
619: p_has_pe IN VARCHAR2,
612: PROCEDURE validate_children
613: (
614: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
615: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
616: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
617: p_request_id IN pon_bid_headers.request_id%TYPE,
618: p_userid IN pon_interface_errors.created_by%TYPE,
619: p_has_pe IN VARCHAR2,
620: p_suffix IN VARCHAR2
679: AND (bai.bid_number, bai.line_number, bai.sequence_number) in
680: (
681: select bh.bid_number, bh.line_number, aa.sequence_number
682: from pon_auction_attributes aa
683: , pon_bid_item_prices bh
684: where bh.bid_number = bai.bid_number
685: and bh.line_number = bai.line_number
686: and aa.auction_header_id = bh.auction_header_id
687: and aa.line_number = bh.line_number
802: bli.auction_header_id,
803: bli.bid_number,
804: bli.line_number,
805: g_exp_date
806: FROM pon_bid_item_prices_interface bli,
807: pon_bid_item_prices bip
808: WHERE bli.batch_id = p_batch_id
809: AND bli.bid_number = bip.bid_number
810: AND bli.line_number = bip.line_number
803: bli.bid_number,
804: bli.line_number,
805: g_exp_date
806: FROM pon_bid_item_prices_interface bli,
807: pon_bid_item_prices bip
808: WHERE bli.batch_id = p_batch_id
809: AND bli.bid_number = bip.bid_number
810: AND bli.line_number = bip.line_number
811: AND bip.display_price_factors_flag = 'Y'
952:
953: -- Event Based Delivery
954: PROCEDURE validate_deliveryevent_fields
955: (
956: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
957: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
958: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
959: p_request_id IN pon_bid_headers.request_id%TYPE,
960: p_userid IN pon_interface_errors.created_by%TYPE,
953: -- Event Based Delivery
954: PROCEDURE validate_deliveryevent_fields
955: (
956: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
957: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
958: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
959: p_request_id IN pon_bid_headers.request_id%TYPE,
960: p_userid IN pon_interface_errors.created_by%TYPE,
961: p_has_pe IN VARCHAR2,
954: PROCEDURE validate_deliveryevent_fields
955: (
956: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
957: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
958: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
959: p_request_id IN pon_bid_headers.request_id%TYPE,
960: p_userid IN pon_interface_errors.created_by%TYPE,
961: p_has_pe IN VARCHAR2,
962: p_suffix IN VARCHAR2
963: ) IS
964:
965: BEGIN
966:
967: update pon_bid_item_prices_interface p
968: set p.CLM_PROMISE_PERIOD_UOM_CODE = (SELECT lookup_code
969: FROM fnd_lookup_values_vl
970: WHERE lookup_type = 'CLM_PERIOD'
971: AND meaning = p.CLM_PROMISE_PERIOD_UOM);
989: LINE_NUMBER,
990: EXPIRATION_DATE)
991: select 'BIDBYSPREADSHEET',
992: fnd_message.get_string('PON', 'PON_CLM_PRM_PERIOD_UOM'),
993: 'PON_BID_ITEM_PRICES',
994: p_batch_id,
995: p.INTERFACE_LINE_ID,
996: 'PON_PRM_PERIOD_UOM_ERR',
997: p_userid,
1004: p_auc_header_id,
1005: p_bid_number,
1006: p.INTERFACE_LINE_ID,
1007: g_exp_date
1008: from pon_bid_item_prices_interface p
1009: where p.CLM_PROMISE_PERIOD_UOM_CODE IS NULL
1010: and p.CLM_PROMISE_PERIOD_UOM IS NOT NULL;
1011:
1012: update pon_bid_item_prices_interface p
1008: from pon_bid_item_prices_interface p
1009: where p.CLM_PROMISE_PERIOD_UOM_CODE IS NULL
1010: and p.CLM_PROMISE_PERIOD_UOM IS NOT NULL;
1011:
1012: update pon_bid_item_prices_interface p
1013: set p.CLM_PROMISE_POP_DUR_UOM_CODE = (SELECT lookup_code
1014: FROM fnd_lookup_values_vl
1015: WHERE lookup_type = 'CLM_PERIOD'
1016: AND meaning = p.CLM_PROMISE_POP_DURATION_UOM);
1034: LINE_NUMBER,
1035: EXPIRATION_DATE)
1036: select 'BIDBYSPREADSHEET',
1037: fnd_message.get_string('PON', 'PON_CLM_PRM_POP_DUR_UOM'),
1038: 'PON_BID_ITEM_PRICES',
1039: p_batch_id,
1040: p.INTERFACE_LINE_ID,
1041: 'PON_PRM_POP_UOM_ERR',
1042: p_userid,
1049: p_auc_header_id,
1050: p_bid_number,
1051: p.INTERFACE_LINE_ID,
1052: g_exp_date
1053: from pon_bid_item_prices_interface p
1054: where p.CLM_PROMISE_POP_DUR_UOM_CODE IS NULL
1055: and p.CLM_PROMISE_POP_DURATION_UOM IS NOT NULL;
1056:
1057: END validate_deliveryevent_fields;
1057: END validate_deliveryevent_fields;
1058:
1059: PROCEDURE default_from_auction
1060: (
1061: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1062: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
1063: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1064: p_full_qty IN VARCHAR2,
1065: p_blanket IN VARCHAR2,
1058:
1059: PROCEDURE default_from_auction
1060: (
1061: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1062: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
1063: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1064: p_full_qty IN VARCHAR2,
1065: p_blanket IN VARCHAR2,
1066: p_auc_closed IN VARCHAR2
1069:
1070: -- If it is a super large negotiation, then proxy is not allowed
1071: IF (pon_large_auction_util_pkg.is_super_large_neg(p_auc_header_id)) THEN
1072:
1073: UPDATE pon_bid_item_prices_interface
1074: SET bid_currency_limit_price = null
1075: WHERE batch_id = p_batch_id;
1076:
1077: END IF;
1076:
1077: END IF;
1078:
1079: -- Default some values in the interface table for each line
1080: MERGE INTO pon_bid_item_prices_interface bli
1081: USING
1082: (SELECT bl.bid_number,
1083: bl.line_number,
1084: bl.display_price_factors_flag,
1087: al.quantity_disabled_flag,
1088: al.price_disabled_flag,
1089: al.group_type,
1090: al.order_type_lookup_code
1091: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1092: WHERE bl.bid_number = p_bid_number
1093: AND al.auction_header_id = bl.auction_header_id
1094: AND al.line_number = bl.line_number) lines
1095: ON (bli.bid_number = lines.bid_number
1128: END default_from_auction;
1129:
1130: PROCEDURE copy_interface_to_txn_tables
1131: (
1132: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1133: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1134: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1135: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
1136: p_hdr_disp_pf IN VARCHAR2,
1131: (
1132: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1133: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1134: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1135: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
1136: p_hdr_disp_pf IN VARCHAR2,
1137: p_blanket IN VARCHAR2,
1138: p_mas IN VARCHAR2,
1139: p_progress_payment_type IN VARCHAR2,
1148:
1149: BEGIN
1150:
1151: -- Update lines table with values in the interface table
1152: MERGE INTO pon_bid_item_prices bl
1153: USING
1154: (SELECT
1155: pbip.bid_number,
1156: pbip.line_number,
1180: pah.max_retainage_negotiable_flag,
1181: pah.recoupment_negotiable_flag,
1182: pbip.worksheet_name,
1183: pbip.worksheet_sequence_number
1184: FROM pon_bid_item_prices_interface pbip,
1185: pon_auction_headers_all pah
1186: WHERE batch_id = p_batch_id
1187: AND pah.auction_header_id = pbip.auction_header_id) bli
1188: ON (bl.bid_number = bli.bid_number
1220: bl.worksheet_name = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_name,to_char(null)),
1221: bl.worksheet_sequence_number = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_sequence_number,to_number(null));
1222:
1223: -- When a GROUP's child recieved a bid, we mark that group as part of the batch
1224: UPDATE pon_bid_item_prices bl
1225: SET batch_id = p_batch_id
1226: WHERE bl.bid_number = p_bid_number
1227: AND (SELECT al.group_type
1228: FROM pon_auction_item_prices_all al
1229: WHERE al.auction_header_id = bl.auction_header_id
1230: AND al.line_number = bl.line_number) = 'GROUP'
1231: AND EXISTS
1232: (SELECT bl2.line_number
1233: FROM pon_bid_item_prices bl2, pon_auction_item_prices_all al2
1234: WHERE bl2.bid_number = p_bid_number
1235: AND al2.auction_header_id = bl2.auction_header_id
1236: AND al2.line_number = bl2.line_number
1237: AND al2.parent_line_number = bl.line_number
1274: -- Update promised_date
1275: UPDATE pon_bid_attribute_values ba
1276: SET value =
1277: nvl((SELECT to_char(bl.promised_date, 'dd-mm-yyyy hh24:mi:ss')
1278: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1279: WHERE bl.bid_number = ba.bid_number
1280: AND bl.line_number = ba.line_number
1281: AND al.auction_header_id = bl.auction_header_id
1282: AND al.line_number = bl.line_number
1287: -- Update quantity
1288: UPDATE pon_bid_attribute_values ba
1289: SET value =
1290: nvl((SELECT to_char(bl.quantity)
1291: FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
1292: WHERE bl.bid_number = ba.bid_number
1293: AND bl.line_number = ba.line_number
1294: AND al.auction_header_id = bl.auction_header_id
1295: AND al.line_number = bl.line_number
1332: bpf.last_updated_by = p_userid,
1333: bpf.interface_line_id = bpfi.interface_line_id;
1334:
1335: -- Sync bid_currency_unit_price from price factors to lines table
1336: UPDATE pon_bid_item_prices bl
1337: SET bl.bid_currency_unit_price =
1338: (SELECT bpf.bid_currency_value
1339: FROM pon_bid_price_elements bpf
1340: WHERE bpf.bid_number = bl.bid_number
1495: END IF;
1496:
1497:
1498: -- Remove all records for the current batch from the interface table
1499: DELETE FROM pon_bid_item_prices_interface bli
1500: WHERE bli.batch_id = p_batch_id;
1501:
1502: DELETE FROM pon_bid_attr_values_interface bai
1503: WHERE bai.batch_id = p_batch_id;
1514: END copy_interface_to_txn_tables;
1515:
1516: PROCEDURE create_url_attachments
1517: (
1518: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1519: p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
1520: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1521: p_userid IN pon_bid_headers.created_by%TYPE
1522: ) IS
1527: CURSOR attachment_lines IS
1528: SELECT bli.attachment_url,
1529: bli.attachment_desc,
1530: bli.line_number
1531: FROM pon_bid_item_prices_interface bli,
1532: pon_auction_item_prices_all paip
1533: WHERE bli.batch_id = p_batch_id
1534: AND bli.attachment_desc IS NOT null
1535: AND bli.attachment_url IS NOT null
1560:
1561: SELECT nvl(max(seq_num), 0) + 1
1562: INTO l_seq_num
1563: FROM fnd_attached_documents
1564: WHERE entity_name = 'PON_BID_ITEM_PRICES'
1565: AND pk1_value = p_auc_header_id
1566: AND pk2_value = p_bid_number
1567: AND pk3_value = line.line_number;
1568:
1571: l_target_category,
1572: line.attachment_desc,
1573: 5,
1574: line.attachment_url,
1575: 'PON_BID_ITEM_PRICES',
1576: p_auc_header_id,
1577: p_bid_number,
1578: line.line_number,
1579: null,
1583: END create_url_attachments;
1584:
1585: PROCEDURE process_spreadsheet_data
1586: (
1587: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1588: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1589: p_request_id IN pon_bid_headers.request_id%TYPE,
1590: p_userid IN pon_interface_errors.created_by%TYPE,
1591: x_return_status OUT NOCOPY VARCHAR2,
1727: */
1728:
1729: PROCEDURE VALIDATE_HEADER
1730: (
1731: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1732: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1733: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1734: p_request_id IN pon_bid_item_prices.request_id%TYPE,
1735: p_user_id IN pon_interface_errors.created_by%TYPE
1730: (
1731: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1732: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
1733: p_bid_number IN pon_bid_headers.bid_number%TYPE,
1734: p_request_id IN pon_bid_item_prices.request_id%TYPE,
1735: p_user_id IN pon_interface_errors.created_by%TYPE
1736: ) IS
1737:
1738: l_loginid NUMBER;
2094: END VALIDATE_HEADER;
2095:
2096: PROCEDURE validate_xml_price_breaks
2097: (
2098: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2099: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2100: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2101: p_request_id IN pon_bid_item_prices.request_id%TYPE,
2102: p_user_id IN pon_interface_errors.created_by%TYPE
2097: (
2098: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2099: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2100: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2101: p_request_id IN pon_bid_item_prices.request_id%TYPE,
2102: p_user_id IN pon_interface_errors.created_by%TYPE
2103: )
2104: IS
2105: l_interface_type pon_interface_errors.interface_type%TYPE;
2171: bipi.worksheet_name s_worksheet_name,
2172: bipi.worksheet_sequence_number s_worksheet_seq_num,
2173: 'PON_AUCTS_PRICE_BREAKS' s_entity_name
2174: from
2175: pon_bid_item_prices_interface bipi,
2176: pon_bid_shipments_int bshi
2177: where bshi.batch_id = p_batch_id
2178: and bshi.bid_number = p_bid_number
2179: and bshi.action in (g_pb_required, g_pb_optional, g_pb_new)
2186:
2187:
2188: PROCEDURE VALIDATE_XML
2189: (
2190: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2191: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2192: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2193: p_request_id IN pon_bid_headers.request_id%TYPE,
2194: p_user_id IN pon_bid_item_prices.last_updated_by%TYPE,
2190: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2191: p_auction_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
2192: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2193: p_request_id IN pon_bid_headers.request_id%TYPE,
2194: p_user_id IN pon_bid_item_prices.last_updated_by%TYPE,
2195: p_suffix IN VARCHAR2,
2196: p_has_pe IN pon_auction_item_prices_all.HAS_PRICE_ELEMENTS_FLAG%TYPE,
2197: l_attr_enabled_flag IN pon_auction_headers_all.line_attribute_enabled_flag%TYPE,
2198: l_req_enabled_flag IN pon_auction_headers_all.hdr_attribute_enabled_flag%TYPE,
2276: bli.line_number,
2277: g_exp_date,
2278: bpei.worksheet_name,
2279: bpei.worksheet_sequence_number
2280: FROM pon_bid_item_prices_interface bli,
2281: pon_bid_item_prices bip,
2282: pon_bid_price_elements_int bpei
2283: WHERE bli.batch_id = p_batch_id
2284: AND bli.bid_number = bip.bid_number
2277: g_exp_date,
2278: bpei.worksheet_name,
2279: bpei.worksheet_sequence_number
2280: FROM pon_bid_item_prices_interface bli,
2281: pon_bid_item_prices bip,
2282: pon_bid_price_elements_int bpei
2283: WHERE bli.batch_id = p_batch_id
2284: AND bli.bid_number = bip.bid_number
2285: AND bli.line_number = bip.line_number
2308: -- 1) Change that is common for existing child entities: Joining key of interface_line_id should be changed to line ID
2309: -- 2) New logic should be added for new entities like Price Break, Price Break Level PD, Payment etc,
2310: PROCEDURE determine_xml_skipped_lines
2311: (
2312: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2313: p_full_qty IN VARCHAR2
2314: ) IS
2315: BEGIN
2316:
2317: -- PRECONDITIONS:
2318: -- line_number are set for lines; it need not be set for children
2319:
2320: -- Determine if any lines can be ignored
2321: UPDATE pon_bid_item_prices_interface bli
2322: SET bli.interface_line_id = g_skip_int
2323: WHERE bli.batch_id = p_batch_id
2324: AND EXISTS
2325: (SELECT 'Y'
2322: SET bli.interface_line_id = g_skip_int
2323: WHERE bli.batch_id = p_batch_id
2324: AND EXISTS
2325: (SELECT 'Y'
2326: FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
2327: WHERE bl.bid_number = bli.bid_number
2328: AND bl.line_number = bli.line_number
2329: AND al.auction_header_id = bl.auction_header_id
2330: AND al.line_number = bl.line_number
2345: OR al.order_type_lookup_code = 'AMOUNT'))
2346: AND bli.note_to_auction_owner IS null
2347: AND bli.promised_date IS null
2348: -- If the values of these columns are NOT same as that in the transaction table
2349: -- (pon_bid_item_prices) then DO NOT skip.
2350: -- If the values are same as that in the transaction table then skip.
2351: -- You do not have to check about optional or required pay items.
2352: AND NVL(bli.recoupment_rate_percent, -9999) = NVL(bl.recoupment_rate_percent, -9999)
2353: AND NVL(bli.bid_curr_advance_amount, -9999) = NVL(bl.bid_curr_advance_amount, -9999)
2412:
2413:
2414: PROCEDURE remove_xml_skipped_lines
2415: (
2416: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
2417: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2418: p_request_id IN pon_bid_headers.request_id%TYPE,
2419: p_userid IN pon_interface_errors.created_by%TYPE,
2420: p_full_qty IN VARCHAR2,
2413:
2414: PROCEDURE remove_xml_skipped_lines
2415: (
2416: p_auc_header_id IN pon_bid_item_prices_interface.auction_header_id%TYPE,
2417: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2418: p_request_id IN pon_bid_headers.request_id%TYPE,
2419: p_userid IN pon_interface_errors.created_by%TYPE,
2420: p_full_qty IN VARCHAR2,
2421: p_buyer_user IN VARCHAR2,
2437: DELETE FROM pon_bid_attr_values_interface bai
2438: WHERE bai.batch_id = p_batch_id
2439: AND bai.line_number in (
2440: select bli.line_number
2441: from pon_bid_item_prices_interface bli
2442: where bli.batch_id = p_batch_id
2443: AND bli.interface_line_id = g_skip_int);
2444:
2445: -- Delete from price elements interface table
2446: DELETE FROM pon_bid_price_elements_int bpfi
2447: WHERE bpfi.batch_id = p_batch_id
2448: AND bpfi.line_number in (
2449: select bli.line_number
2450: from pon_bid_item_prices_interface bli
2451: where bli.batch_id = p_batch_id
2452: AND bli.interface_line_id = g_skip_int);
2453:
2454: -- Delete from price differentials interface table
2455: DELETE FROM pon_bid_price_differ_int bpdi
2456: WHERE bpdi.batch_id = p_batch_id
2457: AND bpdi.auction_line_number in (
2458: select bli.line_number
2459: from pon_bid_item_prices_interface bli
2460: where bli.batch_id = p_batch_id
2461: AND bli.interface_line_id = g_skip_int);
2462:
2463: -- Delete from price breaks / price tiers interface table
2464: DELETE FROM pon_bid_shipments_int bshi
2465: WHERE bshi.batch_id = p_batch_id
2466: AND bshi.line_number in (
2467: select bli.line_number
2468: from pon_bid_item_prices_interface bli
2469: where bli.batch_id = p_batch_id
2470: AND bli.interface_line_id = g_skip_int);
2471:
2472: -- Delete from payments interface table
2473: DELETE FROM pon_bid_payments_interface pbpi
2474: WHERE pbpi.batch_id = p_batch_id
2475: AND pbpi.document_disp_line_number in (
2476: select pai.document_disp_line_number
2477: from pon_bid_item_prices_interface bli,
2478: pon_auction_item_prices_all pai
2479: where bli.batch_id = p_batch_id
2480: AND bli.interface_line_id = g_skip_int
2481: AND pai.auction_header_id = bli.auction_header_id
2481: AND pai.auction_header_id = bli.auction_header_id
2482: AND pai.line_number = bli.line_number);
2483:
2484: -- Delete all bid lines from interface table
2485: DELETE FROM pon_bid_item_prices_interface bli
2486: WHERE bli.batch_id = p_batch_id
2487: AND bli.interface_line_id = g_skip_int;
2488:
2489:
2490: END remove_xml_skipped_lines;
2491:
2492: PROCEDURE validate_xml_req_values
2493: (
2494: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2495: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2496: p_user_id IN pon_interface_errors.created_by%TYPE,
2497: p_suffix IN VARCHAR2,
2498: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2491:
2492: PROCEDURE validate_xml_req_values
2493: (
2494: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2495: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2496: p_user_id IN pon_interface_errors.created_by%TYPE,
2497: p_suffix IN VARCHAR2,
2498: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2499: p_request_id IN pon_interface_errors.request_id%TYPE
2681:
2682:
2683: PROCEDURE validate_xml_attr_values
2684: (
2685: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2686: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2687: p_user_id IN pon_interface_errors.created_by%TYPE,
2688: p_suffix IN VARCHAR2,
2689: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2682:
2683: PROCEDURE validate_xml_attr_values
2684: (
2685: p_auction_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
2686: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
2687: p_user_id IN pon_interface_errors.created_by%TYPE,
2688: p_suffix IN VARCHAR2,
2689: p_batch_id IN pon_interface_errors.batch_id%TYPE,
2690: p_request_id IN pon_interface_errors.request_id%TYPE
2890:
2891:
2892: PROCEDURE copy_shipment_interface_to_txn
2893: (
2894: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2895: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2896: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
2897: p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
2898: p_shipment_type IN pon_bid_shipments.shipment_type%TYPE
2892: PROCEDURE copy_shipment_interface_to_txn
2893: (
2894: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
2895: p_bid_number IN pon_bid_headers.bid_number%TYPE,
2896: p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
2897: p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
2898: p_shipment_type IN pon_bid_shipments.shipment_type%TYPE
2899: )
2900: is
2955: bsi.price_discount,
2956: bsi.bid_currency_unit_price,
2957: bip.bid_currency_unit_price item_price
2958: FROM pon_bid_shipments_int bsi,
2959: pon_bid_item_prices bip
2960: WHERE bsi.batch_id = p_batch_id
2961: and bsi.bid_number = p_bid_number
2962: and bsi.bid_number = bip.bid_number
2963: and bsi.auction_header_id = bip.auction_header_id
3225: bsd.shipment_number<>-1
3226: and bsd.bid_number=p_bid_number
3227: and bsd.line_number in
3228: (select bip.line_number
3229: from pon_bid_item_prices bip
3230: where bip.batch_id=p_batch_id
3231: and bip.bid_number=p_bid_number
3232: )
3233: and
3234: (bsd.bid_number, bsd.line_number, bsd.shipment_number)
3235: not in
3236: (
3237: select bsh.bid_number, bsh.line_number, bsh.shipment_number
3238: from pon_bid_shipments bsh,pon_bid_item_prices bip
3239: where bip.batch_id = p_batch_id
3240: and bip.bid_number = p_bid_number
3241: and bip.bid_number = bsh.bid_number
3242: and bip.line_number = bsh.line_number
3251:
3252:
3253: PROCEDURE process_xml_spreadsheet_data
3254: (
3255: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
3256: p_bid_number IN pon_bid_headers.bid_number%TYPE,
3257: p_request_id IN pon_bid_headers.request_id%TYPE,
3258: p_user_id IN pon_interface_errors.created_by%TYPE,
3259: x_return_status OUT NOCOPY NUMBER,
3407: END IS_UDA_DATA_IN_INTERFACE;
3408:
3409: PROCEDURE process_spreadsheet_uda_data
3410: (
3411: p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
3412: --bug 16654950
3413: p_transaction_id IN po_uda_interface.transaction_id%TYPE,
3414: p_bid_number IN pon_bid_headers.bid_number%TYPE,
3415: p_request_id IN pon_bid_headers.request_id%TYPE,
3426: msgData varchar2(240);
3427: msgCount Number;
3428: l_errorcode Number;
3429: l_return_status varchar2(1);
3430: Cursor lineCursor is select line_number lno from pon_bid_item_prices where bid_number = p_bid_number;
3431:
3432:
3433: begin
3434: po_uda_array.extend; --extend the po_uda_array
3431:
3432:
3433: begin
3434: po_uda_array.extend; --extend the po_uda_array
3435: SELECT DISTINCT uda_template_id into l_template_id FROM pon_bid_item_prices WHERE bid_number = p_bid_number;
3436: x_return_status := 'S';
3437: l_return_status := 'S';
3438:
3439: