[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'
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:
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
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
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);
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);
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,
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,
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
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
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
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
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,
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
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'
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:
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
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,