[Home] [Help]
271: p_auctioneer_id,
272: pbip.price,
273: null
274: from
275: pon_auction_item_prices_all paip,
276: pon_auction_headers_all paha
277: where pbip.bid_number = p_bid_number
278: and pbip.auction_header_id = paip.auction_header_id
279: and pbip.line_number = paip.line_number
283: pbip.bid_number = p_bid_number and
284: pbip.line_number >= l_batch_start and
285: pbip.line_number <= l_batch_end AND
286: pbip.auction_line_number IN (SELECT line_number
287: FROM pon_auction_item_prices_all
288: WHERE auction_header_id = pbip.auction_header_id
289: AND Nvl(award_status, 'NO') <> 'COMPLETED'); --Staggered Awards project
290:
291: l_batch_start := l_batch_end + 1;
326: l_commit_flag := FALSE;
327:
328: select nvl(max(line_number),0)
329: into l_max_line_number
330: from pon_auction_item_prices_all
331: where auction_header_id = p_auction_id;
332:
333: l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
334:
342: END IF;
343:
344: WHILE (l_batch_start <= l_max_line_number) LOOP --{ main-batching-loop
345:
346: UPDATE pon_auction_item_prices_all paip
347: SET
348: (paip.award_status,
349: paip.awarded_quantity,
350: paip.award_mode,
435: nvl(al.award_status,'NO'),
436: bl.bid_number,
437: bl.order_number,
438: bl.award_quantity
439: from pon_auction_item_prices_all al,
440: pon_bid_item_prices bl,
441: pon_bid_headers bh
442: where al.auction_header_id = p_auction_header_id
443: and bl.auction_header_id = al.auction_header_id
459: and nvl(bh.bid_status,'NONE') = 'ACTIVE'
460: and nvl(bh.award_status,'NO') = 'NO';
461:
462:
463: x_line_number pon_auction_item_prices_all.line_number%type;
464: x_old_line_number pon_auction_item_prices_all.line_number%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
460: and nvl(bh.award_status,'NO') = 'NO';
461:
462:
463: x_line_number pon_auction_item_prices_all.line_number%type;
464: x_old_line_number pon_auction_item_prices_all.line_number%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
468: x_award_quantity pon_bid_item_prices.award_quantity%type;
461:
462:
463: x_line_number pon_auction_item_prices_all.line_number%type;
464: x_old_line_number pon_auction_item_prices_all.line_number%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
468: x_award_quantity pon_bid_item_prices.award_quantity%type;
469: x_line_award_status pon_auction_item_prices_all.award_status%type;
465: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
466: x_bid_number pon_bid_headers.bid_number%type;
467: x_order_number pon_bid_headers.order_number%type;
468: x_award_quantity pon_bid_item_prices.award_quantity%type;
469: x_line_award_status pon_auction_item_prices_all.award_status%type;
470: x_stored_note_to_rejected pon_acceptances.reason%type;
471: x_error_code VARCHAR2(20);
472:
473: x_bid_number_list integerList;
670: p_has_scoring_teams_flag IN VARCHAR2,
671: p_scoring_lock_tpc_id IN NUMBER,
672: p_finally_complete IN VARCHAR2 DEFAULT 'N') IS
673:
674: x_line_number pon_auction_item_prices_all.line_number%type;
675: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
676: x_error_code VARCHAR2(20);
677: x_awarded_quantity NUMBER;
678: l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE; -- FPK: CPA
671: p_scoring_lock_tpc_id IN NUMBER,
672: p_finally_complete IN VARCHAR2 DEFAULT 'N') IS
673:
674: x_line_number pon_auction_item_prices_all.line_number%type;
675: x_line_origination_code pon_auction_item_prices_all.line_origination_code%type;
676: x_error_code VARCHAR2(20);
677: x_awarded_quantity NUMBER;
678: l_neg_has_lines PON_AUCTION_HEADERS_ALL.HAS_ITEMS_FLAG%TYPE; -- FPK: CPA
679:
686: /*Bug : 14134092 : If there are no bids for autocreated lines, check if any unsol lines are linked
687: and line type is enabled. */
688: CURSOR items_with_reqs_no_bids(p_auction_header_id NUMBER, p_is_line_type_enabled VARCHAR2) IS
689: SELECT line_number, line_origination_code
690: FROM PON_AUCTION_ITEM_PRICES_ALL paip
691: WHERE auction_header_id = p_auction_header_id
692: AND nvl(number_of_bids,0) = 0
693: AND line_origination_code = 'REQUISITION'
694: AND NOT EXISTS
709:
710:
711: CURSOR auction_items_all (p_auction_header_id NUMBER) IS
712: SELECT line_number, nvl(awarded_quantity, 0)
713: FROM PON_AUCTION_ITEM_PRICES_ALL
714: WHERE auction_header_id = p_auction_header_id;
715:
716: --Line Type and Structute Changes Project
717: CURSOR c_has_linked_pr_refs IS
739: l_neg_has_lines := PON_AUCTION_PKG.neg_has_lines(p_auction_header_id); -- FPK: CPA
740:
741: select nvl(allow_staggered_awards, 'N') into l_allow_stag_awards from pon_auction_headers_all where auction_header_id = p_auction_header_id;
742:
743: select count(DISTINCT line_number) into l_unawarded_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id and nvl(award_status,'NO') = 'NO';
744:
745: select count(DISTINCT line_number) into l_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id;
746:
747: if (l_unawarded_line_count > 0 AND l_unawarded_line_count <> l_line_count AND p_finally_complete <> 'Y') then
741: select nvl(allow_staggered_awards, 'N') into l_allow_stag_awards from pon_auction_headers_all where auction_header_id = p_auction_header_id;
742:
743: select count(DISTINCT line_number) into l_unawarded_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id and nvl(award_status,'NO') = 'NO';
744:
745: select count(DISTINCT line_number) into l_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id;
746:
747: if (l_unawarded_line_count > 0 AND l_unawarded_line_count <> l_line_count AND p_finally_complete <> 'Y') then
748: l_has_unawarded_lines := 'Y';
749: else
860: need to add batching here
861: */
862: --complete award should update only for awarded lines as COMPLETED if staggered awards are enabled
863: if l_allow_stag_awards = 'Y' and l_has_unawarded_lines = 'Y' then
864: update pon_auction_item_prices_all
865: set AWARD_STATUS = 'COMPLETED',
866: LAST_UPDATE_DATE = sysdate,
867: AWARDED_QUANTITY = nvl(awarded_quantity,0)
868: where auction_header_id = p_auction_header_id
867: AWARDED_QUANTITY = nvl(awarded_quantity,0)
868: where auction_header_id = p_auction_header_id
869: and AWARD_STATUS = 'AWARDED';
870: else
871: update pon_auction_item_prices_all
872: set AWARD_STATUS = 'COMPLETED',
873: LAST_UPDATE_DATE = sysdate,
874: AWARDED_QUANTITY = nvl(awarded_quantity,0)
875: where auction_header_id = p_auction_header_id;
1041: INTO x_completed_lines
1042: FROM pon_bid_item_prices
1043: WHERE auction_header_id = p_auction_header_id
1044: AND bid_number = x_bid_number
1045: AND auction_line_number IN (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id AND Nvl(award_status, 'NO') = 'COMPLETED');
1046:
1047: IF x_completed_lines > 0 THEN
1048:
1049: pon_auction_pkg.AWARD_BID(x_bid_number,
1094: p_line_number IN NUMBER,
1095: p_award_quantity IN NUMBER) IS
1096:
1097: BEGIN
1098: update pon_auction_item_prices_all
1099: set AWARD_STATUS = 'COMPLETED',
1100: LAST_UPDATE_DATE = sysdate,
1101: AWARDED_QUANTITY = p_award_quantity
1102: where auction_header_id = p_auction_header_id
1107:
1108: ----------------------------------------------------------------
1109: -- award item disposition
1110: -- mirrors complete_item_disposition
1111: -- except that pon_auction_item_prices_all.award_status column is set to AWARDED (instead of COMPLETED)
1112: ----------------------------------------------------------------
1113:
1114: PROCEDURE award_item_disposition (p_auction_header_id IN NUMBER,
1115: p_line_number IN NUMBER,
1115: p_line_number IN NUMBER,
1116: p_award_quantity IN NUMBER) IS
1117:
1118: BEGIN
1119: update pon_auction_item_prices_all
1120: set AWARD_STATUS = 'AWARDED',
1121: LAST_UPDATE_DATE = sysdate,
1122: AWARDED_QUANTITY = p_award_quantity
1123: where auction_header_id = p_auction_header_id
1137: IS
1138: Cursor exhibitsCursor Is
1139: select paip.line_number
1140: from pon_auction_exhibit_details paed,
1141: pon_auction_item_prices_all paip
1142: where paed.auction_header_id = p_auction_header_id
1143: and paed.associated_to_line = p_line_num
1144: AND paip.auction_header_id = paed.auction_header_id
1145: AND paip.exhibit_number IS NOT NULL
1178: 'AMOUNT', 1,
1179: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
1180: INTO l_award_quantity
1181: FROM pon_bid_item_prices pbip,
1182: pon_auction_item_prices_all paip
1183: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1184: AND pbip.auction_line_number = elin.line_number
1185: AND paip.auction_header_id = pbip.auction_header_id
1186: AND paip.line_number = pbip.line_number;
1192: SELECT decode(pbip.award_status, 'REJECTED', null,
1193: pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
1194: INTO l_award_price
1195: FROM pon_bid_item_prices pbip,
1196: pon_auction_item_prices_all paip
1197: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1198: AND pbip.auction_line_number = elin.line_number
1199: AND paip.auction_header_id = pbip.auction_header_id
1200: AND paip.line_number = pbip.line_number;
1210: END IF; -- End check_bid_line_exist
1211:
1212: END LOOP; -- End p_award_lines loop
1213:
1214: UPDATE pon_auction_item_prices_all paip
1215: SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
1216: 'FIXED PRICE', 1,
1217: 'AMOUNT', 1,
1218: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
1229: p_line_num IN Number,
1230: p_award_lines IN t_award_lines,
1231: p_auctioneer_id IN Number)
1232: IS
1233: l_parent_line pon_auction_item_prices_all.line_number%TYPE;
1234: is_parent_slin VARCHAR2(1);
1235: l_award_price pon_bid_item_prices.award_price%TYPE;
1236: l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
1237: l_size NUMBER;
1241: BEGIN
1242: -- This sql gets parent line for an elin. Also whether parent is clin/slin.
1243: SELECT paed.associated_to_line, Decode(Nvl(paip_parent.group_line_id, -1), -1, 'N', 'Y')
1244: INTO l_parent_line, is_parent_slin
1245: FROM pon_auction_item_prices_all paip_elin,
1246: pon_auction_exhibit_details paed,
1247: pon_auction_item_prices_all paip_parent
1248: WHERE paip_elin.auction_header_id = p_auction_header_id
1249: AND paip_elin.line_number = p_line_num
1243: SELECT paed.associated_to_line, Decode(Nvl(paip_parent.group_line_id, -1), -1, 'N', 'Y')
1244: INTO l_parent_line, is_parent_slin
1245: FROM pon_auction_item_prices_all paip_elin,
1246: pon_auction_exhibit_details paed,
1247: pon_auction_item_prices_all paip_parent
1248: WHERE paip_elin.auction_header_id = p_auction_header_id
1249: AND paip_elin.line_number = p_line_num
1250: AND paed.auction_header_id = paip_elin.auction_header_id
1251: AND paed.exhibit_number = paip_elin.exhibit_number
1281: END IF; --end check_bid_line_exist() = 1
1282:
1283: END LOOP;
1284: -- update only when award_status = 'REJECTED'
1285: UPDATE pon_auction_item_prices_all
1286: set award_status = l_award_status,
1287: last_update_date = sysdate,
1288: last_updated_by = p_auctioneer_id
1289: where auction_header_id = p_auction_header_id
1302: 'AMOUNT', 1,
1303: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
1304: INTO l_award_quantity
1305: FROM pon_bid_item_prices pbip,
1306: pon_auction_item_prices_all paip
1307: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1308: AND pbip.auction_line_number = l_parent_line
1309: AND paip.auction_header_id = pbip.auction_header_id
1310: AND paip.line_number = pbip.line_number;
1316: SELECT decode(pbip.award_status, 'REJECTED', null,
1317: pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
1318: INTO l_award_price
1319: FROM pon_bid_item_prices pbip,
1320: pon_auction_item_prices_all paip
1321: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
1322: AND pbip.auction_line_number = l_parent_line
1323: AND paip.auction_header_id = pbip.auction_header_id
1324: AND paip.line_number = pbip.line_number;
1343: --If parent line is clin, update info slins.
1344: award_info_slin_lines(p_auction_header_id, l_parent_line, p_award_lines, p_auctioneer_id);
1345: END IF;
1346:
1347: UPDATE pon_auction_item_prices_all paip
1348: SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
1349: 'FIXED PRICE', 1,
1350: 'AMOUNT', 1,
1351: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
1389: --
1390: l_current_bid_number NUMBER;
1391: l_bid_list_index NUMBER;
1392: l_tmp_award_quantity NUMBER;
1393: l_group_type pon_auction_item_prices_all.group_type%TYPE;
1394: l_award_date DATE;
1395: TYPE BID_LIST_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1396: l_bid_list BID_LIST_TYPE;
1397: l_winning_bid NUMBER;
1397: l_winning_bid NUMBER;
1398: l_neg_contract_type pon_auction_headers_all.contract_type%TYPE;
1399:
1400: --
1401: l_has_quantity_tiers pon_auction_item_prices_all.has_quantity_tiers%TYPE;
1402: l_award_shipment_number NUMBER;
1403: l_suffix VARCHAR2(2);
1404:
1405: --Unsolicited Lines Project
1547: -- Construct Matrix only in case of awarded bids
1548: IF l_rec.award_outcome = g_AWARD_OUTCOME_WIN THEN
1549: IF ( l_neg_contract_type = 'BLANKET' ) THEN --Get Award Qty FROM Response Qty
1550: SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , ai.quantity) INTO l_tmp_award_quantity
1551: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1552: WHERE bi.bid_number = l_rec.bid_number
1553: AND bi.line_number = l_rec.line_number
1554: AND ai.auction_header_id = bi.auction_header_id
1555: AND ai.line_number = bi.line_number;
1554: AND ai.auction_header_id = bi.auction_header_id
1555: AND ai.line_number = bi.line_number;
1556: ELSE ---- Get Award Qty FROM response Qty
1557: SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , bi.quantity) INTO l_tmp_award_quantity
1558: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1559: WHERE bi.bid_number = l_rec.bid_number
1560: AND bi.line_number = l_rec.line_number
1561: AND ai.auction_header_id = bi.auction_header_id
1562: AND ai.line_number = bi.line_number;
1562: AND ai.line_number = bi.line_number;
1563: END IF;
1564: --
1565: SELECT ai.group_type INTO l_group_type
1566: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
1567: WHERE bi.bid_number = l_rec.bid_number
1568: AND bi.line_number = l_rec.line_number
1569: AND ai.auction_header_id = bi.auction_header_id
1570: AND ai.line_number = bi.line_number;
1615: END IF;
1616: --
1617: l_bid_list_index := 0;
1618: SELECT ai.group_type INTO l_group_type
1619: FROM pon_auction_item_prices_all ai
1620: WHERE ai.auction_header_id = p_auction_header_id
1621: AND ai.line_number = p_line_num;
1622: --
1623: -- Getting the suffix to display the error message correctly.
1661: THEN --{
1662: l_award_shipment_number := -1;
1663:
1664: select nvl((select pbs.shipment_number
1665: from pon_bid_shipments pbs, pon_auction_item_prices_all paip
1666: where pbs.bid_number = l_rec.bid_number
1667: and pbs.line_number = p_line_num
1668: AND l_rec.award_quantity >= pbs.quantity
1669: AND l_rec.award_quantity <= pbs.max_quantity
1934: )) THEN
1935: l_award_status := 'AWARDED';
1936: end if;
1937: end loop;
1938: UPDATE pon_auction_item_prices_all
1939: set award_status = l_award_status,
1940: last_update_date = sysdate,
1941: last_updated_by = p_auctioneer_id
1942: where auction_header_id = p_auction_header_id
1948: p_line_num IN Number,
1949: p_award_lines IN t_award_lines,
1950: p_auctioneer_id IN Number)
1951: IS
1952: Cursor slinCursor Is select line_number from pon_auction_item_prices_all where
1953: auction_header_id = p_auction_header_id and group_line_id = p_line_num and nvl(clm_info_flag,'N') = 'Y';
1954: Begin
1955:
1956: for slinLine in slinCursor Loop
1965: p_line_num IN Number,
1966: p_award_lines IN t_award_lines,
1967: p_auctioneer_id IN Number)
1968: IS
1969: Cursor optCursor Is select line_number from pon_auction_item_prices_all where
1970: auction_header_id = p_auction_header_id and clm_base_line_num = p_line_num;
1971: l_award_price pon_bid_item_prices.award_price%TYPE;
1972: l_award_quantity pon_bid_item_prices.award_quantity%TYPE;
1973: l_size NUMBER;
2012: 'AMOUNT', 1,
2013: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', pbip.quantity, 1), pbip.quantity ))
2014: INTO l_award_quantity
2015: FROM pon_bid_item_prices pbip,
2016: pon_auction_item_prices_all paip
2017: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
2018: AND pbip.auction_line_number = optLine.line_number
2019: AND paip.auction_header_id = pbip.auction_header_id
2020: AND paip.line_number = pbip.line_number;
2026: SELECT decode(pbip.award_status, 'REJECTED', null,
2027: pbip.per_unit_price_component + pbip.fixed_amount_component /l_award_quantity)
2028: INTO l_award_price
2029: FROM pon_bid_item_prices pbip,
2030: pon_auction_item_prices_all paip
2031: WHERE pbip.bid_number = p_award_lines(l_index).bid_Number
2032: AND pbip.auction_line_number = optLine.line_number
2033: AND paip.auction_header_id = pbip.auction_header_id
2034: AND paip.line_number = pbip.line_number;
2044:
2045: END LOOP;
2046:
2047: -- bug 9940219
2048: UPDATE pon_auction_item_prices_all paip
2049: SET paip.awarded_quantity = decode (paip.order_type_lookup_code,
2050: 'FIXED PRICE', 1,
2051: 'AMOUNT', 1,
2052: 'RATE', decode (paip.purchase_basis, 'TEMP LABOR', l_auction_award_qty, 1),l_auction_award_qty )
2064: p_auctioneer_id IN Number)
2065:
2066: IS
2067:
2068: CURSOR optLineCur IS SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id=p_auction_header_id
2069: AND clm_base_line_num=p_line_num AND group_line_id<>(SELECT group_line_id FROM pon_auction_item_prices_all WHERE
2070: line_number=p_line_num AND auction_header_id=p_auction_header_id );
2071:
2072: BEGIN
2065:
2066: IS
2067:
2068: CURSOR optLineCur IS SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id=p_auction_header_id
2069: AND clm_base_line_num=p_line_num AND group_line_id<>(SELECT group_line_id FROM pon_auction_item_prices_all WHERE
2070: line_number=p_line_num AND auction_header_id=p_auction_header_id );
2071:
2072: BEGIN
2073:
2087: p_auctioneer_id IN Number)
2088: IS
2089: l_group_line_id Number;
2090: l_not_awd_lines Number;
2091: l_award_status pon_auction_item_prices_all.award_status%TYPE;
2092: l_award_lines NUMBER;
2093:
2094: Begin
2095: select group_line_id,award_status into l_group_line_id,l_award_status from
2092: l_award_lines NUMBER;
2093:
2094: Begin
2095: select group_line_id,award_status into l_group_line_id,l_award_status from
2096: pon_auction_item_prices_all where
2097: auction_header_id = p_auction_header_id and line_number = p_line_num;
2098: select count(*) into l_not_awd_lines from pon_auction_item_prices_all where
2099: auction_header_id = p_auction_header_id and group_line_id = l_group_line_id
2100: and award_status is null
2094: Begin
2095: select group_line_id,award_status into l_group_line_id,l_award_status from
2096: pon_auction_item_prices_all where
2097: auction_header_id = p_auction_header_id and line_number = p_line_num;
2098: select count(*) into l_not_awd_lines from pon_auction_item_prices_all where
2099: auction_header_id = p_auction_header_id and group_line_id = l_group_line_id
2100: and award_status is null
2101: AND Nvl(clm_info_flag,'N') <> 'Y'; -- bug 9746442
2102: if ( l_not_awd_lines =0 OR l_award_status IS NULL ) then --all lines are awarded
2110:
2111: --Line Type and Structure Changes
2112: --Update CLIN as 'AWARDED' if atleast one slin in structure is 'AWARDED'
2113:
2114: SELECT Count(*) INTO l_award_lines FROM pon_auction_item_prices_all where
2115: auction_header_id = p_auction_header_id and group_line_id = l_group_line_id
2116: and award_status = 'AWARDED';
2117:
2118: IF(l_award_lines> 0) THEN
2146: --This cursor gets all the elins associated to particular line.
2147: CURSOR get_elins_for_line IS
2148: SELECT paip.line_number
2149: FROM pon_auction_exhibit_details exhibit,
2150: pon_auction_item_prices_all paip,
2151: pon_bid_item_prices pbip
2152: WHERE exhibit.auction_header_id = p_auction_id
2153: AND exhibit.associated_to_line = p_line_number
2154: AND paip.auction_header_id = exhibit.auction_header_id
2175: END IF;
2176: IF Nvl(p_award_status, 'REJECTED') = 'AWARDED' THEN
2177: IF ( p_contract_type IN ('BLANKET','CONTRACT') ) THEN --Get Award Qty FROM Response Qty
2178: SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , ai.quantity) INTO l_tmp_award_quantity
2179: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2180: WHERE bi.bid_number = p_bid_number
2181: AND bi.line_number = elin.line_number
2182: AND ai.auction_header_id = bi.auction_header_id
2183: AND ai.line_number = bi.line_number;
2182: AND ai.auction_header_id = bi.auction_header_id
2183: AND ai.line_number = bi.line_number;
2184: ELSE ---- Get Award Qty FROM response Qty
2185: SELECT decode (ai.order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, 'RATE', decode(ai.purchase_basis , 'TEMP LABOR' ,bi.quantity, 1) , bi.quantity) INTO l_tmp_award_quantity
2186: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2187: WHERE bi.bid_number = p_bid_number
2188: AND bi.line_number = elin.line_number
2189: AND ai.auction_header_id = bi.auction_header_id
2190: AND ai.line_number = bi.line_number;
2228: )
2229: IS
2230: l_size NUMBER;
2231: l_index NUMBER;
2232: l_group_type pon_auction_item_prices_all.group_type%type;
2233: l_award_quantity pon_bid_item_prices.award_quantity%type;
2234: l_award_shipment_number NUMBER;
2235: --
2236: /* for updating group for each bid's group line,
2241: */
2242: type bid_line_asso is table of varchar2(30) index by varchar2(30);
2243: l_bid_group_map bid_line_asso;
2244: l_bid_line_key VARCHAR2(30);
2245: l_parent_line_number pon_auction_item_prices_all.parent_line_number%type;
2246: l_bid_number pon_bid_item_prices.bid_number%type;
2247:
2248: --ELINs project
2249: l_are_exhibits_enabled VARCHAR2(1);
2355: AND (l_group_type = 'GROUP_LINE') ) THEN
2356:
2357: -- get parent line number
2358: SELECT parent_line_number INTO l_parent_line_number
2359: FROM pon_auction_item_prices_all
2360: WHERE auction_header_id = p_auction_id
2361: AND line_number = p_award_lines(l_index).line_number;
2362:
2363: /* Key will be bid:group 1234:56 */
2436: decode(nvl(p_award_quantity,0), 0,pbip.price,
2437: pbip.per_unit_price_component + pbip.fixed_amount_component /p_award_quantity))
2438: INTO l_award_price
2439: FROM pon_bid_item_prices pbip,
2440: pon_auction_item_prices_all paip
2441: WHERE pbip.bid_number = p_bid_number
2442: AND pbip.line_number = p_line_number
2443: AND paip.auction_header_id = pbip.auction_header_id
2444: AND paip.line_number = pbip.line_number;
2515: DECODE (NVL(p_award_quantity,0), 0, pbs.price,
2516: pbs.per_unit_price_component+pbip.fixed_amount_component/p_award_quantity))
2517: INTO l_award_price
2518: FROM pon_bid_item_prices pbip,
2519: pon_auction_item_prices_all paip,
2520: pon_bid_shipments pbs
2521: WHERE pbip.bid_number = p_bid_number
2522: AND pbip.line_number = p_line_number
2523: AND paip.auction_header_id = pbip.auction_header_id
2685:
2686: ((SELECT bi.Line_number,
2687: bi.award_status,
2688: nvl(bi.award_price , bi.price) * bi.award_quantity award_price
2689: FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
2690: WHERE bi.bid_number = c_bid_number
2691: and bi.auction_header_id = ai.auction_header_id
2692: and bi.line_number = ai.line_number
2693: and ai.group_type in ('LOT', 'LINE', 'GROUP_LINE'))
2766: )
2767: IS
2768: CURSOR c_auction_items (c_auction_id NUMBER) IS
2769: SELECT line_number, group_type
2770: FROM pon_auction_item_prices_all
2771: WHERE auction_header_id = c_auction_id
2772: AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
2773: l_auction_items_rec c_auction_items%ROWTYPE;
2774: --
2773: l_auction_items_rec c_auction_items%ROWTYPE;
2774: --
2775: CURSOR c_item_sublines (c_auction_id NUMBER, c_parent_line_number NUMBER) IS
2776: SELECT line_number, group_type
2777: FROM pon_auction_item_prices_all
2778: WHERE auction_header_id = c_auction_id
2779: AND parent_line_number = c_parent_line_number
2780: AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
2781:
2780: AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
2781:
2782: l_item_sublines_rec c_item_sublines%ROWTYPE;
2783: --
2784: l_group_type pon_auction_item_prices_all.group_type%TYPE;
2785: --
2786: BEGIN
2787:
2788: OPEN c_auction_items(p_auction_id);
2802: p_line_number,
2803: p_auctioneer_id,
2804: p_mode);
2805:
2806: SELECT group_type INTO l_group_type FROM pon_auction_item_prices_all
2807: WHERE auction_header_id = p_auction_id
2808: AND line_number = p_line_number;
2809: IF ((p_mode = g_AWARD_LINE AND l_group_type = 'LOT')
2810: OR (p_mode = g_AWARD_LINE_H AND l_group_type = 'LOT')
2843: (
2844: p_auction_id IN NUMBER,
2845: p_line_number IN NUMBER,
2846: p_auctioneer_id IN NUMBER,
2847: p_mode IN pon_auction_item_prices_all.award_mode%type
2848: )
2849: IS
2850: CURSOR c_bid_items (c_auction_id NUMBER, c_line_number NUMBER) IS
2851: SELECT bi.Line_number,
2854: bi.award_quantity,
2855: ai.group_type
2856: FROM pon_bid_item_prices bi,
2857: pon_bid_headers bh,
2858: pon_auction_item_prices_all ai
2859: WHERE bi.auction_header_id = c_auction_id
2860: AND bi.line_number = c_line_number
2861: AND bh.bid_status = 'ACTIVE'
2862: AND bh.auction_header_id = bi.auction_header_id
2870: l_award_quantity NUMBER;
2871: l_line_type VARCHAR2(20);
2872: l_contract_type VARCHAR2(20);
2873: l_group_type VARCHAR2(20);
2874: l_item_award_mode pon_auction_item_prices_all.award_mode%type;
2875: --
2876: BEGIN
2877: l_award_status := null;
2878: l_award_quantity := null;
2898: END IF;
2899: IF ((l_award_status is null)
2900: AND ((l_bid_items_rec.award_status = 'AWARDED'))
2901: --Bug 12573845
2902: --PON_AUCTION_ITEM_PRICES_ALL table was being updated with AWARDED status regardless of the bid status
2903: --Modified such that only if Bid line is awarded, Negotiation line is set to AWARDED
2904:
2905: --OR (l_bid_items_rec.award_status = 'REJECTED') --bug No: 9741473
2906: --OR (l_bid_items_rec.award_status = 'PARTIAL'))
2918: -- set award mode = GROUP for group level award.
2919: IF(p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) AND l_group_type = 'GROUP' THEN
2920: l_item_award_mode := 'GROUP';
2921: END IF;
2922: UPDATE pon_auction_item_prices_all
2923: SET award_status = l_award_status,
2924: awarded_quantity = l_award_quantity,
2925: award_mode = l_item_award_mode,
2926: last_update_date = SYSDATE,
2944: --
2945: /*
2946: CURSOR c_auction_lines (c_auction_id NUMBER) is
2947: SELECT Line_number, award_status
2948: FROM pon_auction_item_prices_all
2949: WHERE auction_header_id = c_auction_id
2950: AND number_of_bids > 0
2951: AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
2952:
3011: */
3012:
3013: SELECT count(Line_number), sum(decode(award_status, 'AWARDED', 1, 0))
3014: INTO l_total_lines, l_awarded_lines
3015: FROM pon_auction_item_prices_all
3016: WHERE auction_header_id = p_auction_id
3017: AND number_of_bids > 0
3018: AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
3019:
3115: -- Insert empty notes for group lines
3116: -- Delete notes for a line
3117: DELETE FROM pon_acceptances
3118: WHERE auction_header_id = p_auction_header_id
3119: AND line_number IN (SELECT line_number FROM pon_auction_item_prices_all
3120: WHERE parent_line_number = p_line_number
3121: AND auction_header_id = p_auction_header_id);
3122: INSERT INTO pon_acceptances (
3123: acceptance_id,
3139: p_award_date,
3140: null,
3141: p_award_date,
3142: p_auctioneer_id
3143: FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3144: WHERE bi.auction_header_id = ai.auction_header_id
3145: AND ai.line_number = bi.line_number
3146: AND (bi.award_status = 'AWARDED'
3147: OR bi.award_status = 'REJECTED')
3217: 'REJECTED', p_note_to_rejected,
3218: null),
3219: p_award_date,
3220: p_auctioneer_id
3221: FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
3222: WHERE bi.auction_header_id = p_auction_header_id
3223: AND (bi.award_status = 'AWARDED'
3224: OR bi.award_status = 'REJECTED')
3225: AND bi.bid_number = bh.bid_number
3360: AND auction_line_number <> -1; -- Unsolicited Lines Project : Donot delete unsolicited lines records.
3361:
3362:
3363: -- reset the award mode at auction item level
3364: UPDATE pon_auction_item_prices_all
3365: SET award_mode = null
3366: WHERE auction_header_id = p_auction_header_id;
3367: ELSE
3368: -- Group Level awards need to be rejected first
3381: WHERE auction_header_id = p_auction_header_id
3382: AND bid_status = 'ACTIVE'
3383: )
3384: AND line_number IN (SELECT line_number
3385: FROM pon_auction_item_prices_all
3386: WHERE auction_header_id = p_auction_header_id
3387: AND (line_number = p_line_number
3388: OR parent_line_number = p_line_number));
3389:
3390: -- Delete All group line awards since it is a group-level awarding
3391: DELETE FROM pon_acceptances
3392: WHERE auction_header_id = p_auction_header_id
3393: AND line_number IN (SELECT line_number
3394: FROM pon_auction_item_prices_all
3395: WHERE auction_header_id = p_auction_header_id
3396: AND parent_line_number = p_line_number);
3397: END IF;
3398: END IF;
3512: por.bid_number,
3513: por.line_number,
3514: por.award_quantity,
3515: por.award_shipment_number
3516: FROM pon_optimize_results por, pon_auction_item_prices_all paip,
3517: pon_auction_headers_all pah,
3518: pon_bid_item_prices pbip
3519: WHERE pah.auction_header_id = l_auction_header_id
3520: AND pah.auction_header_id = paip.auction_header_id
3580: pas.batch_id,
3581: ai.group_type,
3582: pas.award_shipment_number
3583: FROM pon_auction_summary pas
3584: , pon_auction_item_prices_all ai
3585: , pon_auction_headers_all ah
3586: WHERE
3587: pas.award_quantity >0
3588: AND pas.batch_id = c_batch_id
4044: aii.awardreject_reason,
4045: ai.group_type,
4046: aii.award_shipment_number
4047: FROM pon_award_items_interface aii,
4048: pon_auction_item_prices_all ai,
4049: pon_bid_item_prices bi,
4050: pon_auction_headers_all pah
4051: WHERE
4052: aii.batch_id = c_batch_id
4323: 'LOT_LINE', null, decode (aii.order_type_lookup_code,
4324: 'FIXED PRICE', 1,
4325: 'AMOUNT', 1,
4326: 'RATE', decode (aii.purchase_basis, 'TEMP LABOR', bi.quantity, 1), bi.quantity )), null) award_quantity
4327: FROM pon_bid_item_prices bi, pon_auction_item_prices_all aii
4328: WHERE
4329: bi.bid_number = c_bid_number
4330: AND bi.line_number IN (SELECT ai.line_number
4331: FROM pon_auction_item_prices_all ai
4327: FROM pon_bid_item_prices bi, pon_auction_item_prices_all aii
4328: WHERE
4329: bi.bid_number = c_bid_number
4330: AND bi.line_number IN (SELECT ai.line_number
4331: FROM pon_auction_item_prices_all ai
4332: WHERE ai.parent_line_number = c_parent_line_number
4333: AND ai.auction_header_id = bi.auction_header_id )
4334: AND aii.auction_header_id = bi.auction_header_id
4335: AND aii.line_number = bi.line_number
4362: ----------------------------------------------------------------
4363: PROCEDURE update_bi_group_award (
4364: p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
4365: p_bid_number IN pon_bid_headers.bid_number%TYPE,
4366: p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
4367: p_award_date IN pon_bid_item_prices.award_date%TYPE,
4368: p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
4369: IS
4370: l_total_lines NUMBER;
4379: sum(decode(bi.award_status,'REJECTED',1,0))
4380: INTO l_total_lines,
4381: l_awarded_lines,
4382: l_rejected_lines
4383: FROM pon_auction_item_prices_all ai, pon_bid_item_prices bi
4384: WHERE ai.parent_line_number = p_parent_line_number
4385: AND ai.auction_header_id = p_auction_header_id
4386: and ai.auction_header_id = bi.auction_header_id(+)
4387: and bi.bid_number = p_bid_number
4418: IS
4419: l_total_lines NUMBER;
4420: l_awarded_lines NUMBER;
4421: l_parent_line_number NUMBER;
4422: l_award_status pon_auction_item_prices_all.award_status%TYPE;
4423: BEGIN
4424: --get total and awarded lines
4425: SELECT parent_line_number INTO l_parent_line_number FROM pon_auction_item_prices_all
4426: WHERE auction_header_id = p_auction_header_id AND line_number = p_line_number;
4421: l_parent_line_number NUMBER;
4422: l_award_status pon_auction_item_prices_all.award_status%TYPE;
4423: BEGIN
4424: --get total and awarded lines
4425: SELECT parent_line_number INTO l_parent_line_number FROM pon_auction_item_prices_all
4426: WHERE auction_header_id = p_auction_header_id AND line_number = p_line_number;
4427: --
4428: -- all the group lines have bids if a single group line has a bid
4429: -- hence all group lines are awardable and hence considered for the count
4427: --
4428: -- all the group lines have bids if a single group line has a bid
4429: -- hence all group lines are awardable and hence considered for the count
4430: SELECT COUNT(*) INTO l_total_lines
4431: FROM pon_auction_item_prices_all ai
4432: WHERE parent_line_number = l_parent_line_number
4433: and auction_header_id = p_auction_header_id
4434: AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
4435: --
4433: and auction_header_id = p_auction_header_id
4434: AND Nvl(award_status, 'NO') <> 'COMPLETED'; --Staggered Awards project
4435: --
4436: select COUNT(*) INTO l_awarded_lines
4437: FROM pon_auction_item_prices_all
4438: WHERE parent_line_number = l_parent_line_number
4439: AND award_status IN ('AWARDED', 'COMPLETED') --Staggered Awards project
4440: and auction_header_id = p_auction_header_id;
4441:
4446: ELSE
4447: l_award_status := null;
4448: END IF;
4449: --
4450: UPDATE pon_auction_item_prices_all
4451: SET award_status = l_award_status,
4452: awarded_quantity = null,
4453: award_mode = null,
4454: last_update_date = p_award_date,
4486: sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))))
4487: INTO p_current_total
4488: FROM pon_bid_item_prices PBIP,
4489: pon_bid_headers PBH,
4490: pon_auction_item_prices_all PAIP
4491: WHERE PAIP.auction_header_id = p_auction_header_id
4492: AND PAIP.auction_header_id = PBIP.auction_header_id (+)
4493: AND PAIP.line_number = PBIP.line_number (+)
4494: AND PBIP.bid_number = PBH.bid_number (+)
4612: IS
4613:
4614: l_new_scenario_id NUMBER;
4615: l_status VARCHAR2(10);
4616: l_order_type_lookup_code PON_AUCTION_ITEM_PRICES_ALL.order_type_lookup_code%TYPE;
4617: l_auction_qty NUMBER;
4618: l_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
4619: l_module VARCHAR2(200);
4620: l_priority VARCHAR2(30);
4699: INTO l_contract_type,
4700: l_order_type_lookup_code,
4701: l_auction_qty
4702: FROM pon_auction_headers_all pah,
4703: pon_auction_item_prices_all pai,
4704: pon_optimize_scenarios pos
4705: WHERE pah.auction_header_id = pai.auction_header_id
4706: AND pah.auction_header_id = pos.auction_header_id
4707: AND pai.line_number = p_line_number
5494: l_fixed_incentive_col,
5495: l_current_rebate_col
5496: FROM
5497: pon_bid_item_prices bi,
5498: pon_auction_item_prices_all ai,
5499: pon_optimize_scenarios pos,
5500: pon_optimize_results por,
5501: pon_bid_shipments pbs,
5502: pon_bid_headers pbh
5612: sysdate,
5613: l_user_id,
5614: l_login_id,
5615: SYSDATE
5616: FROM pon_auction_item_prices_all paip, pon_backing_requisitions pbr
5617: WHERE pbr.auction_header_id = p_auction_header_id
5618: AND paip.auction_header_id = pbr.auction_header_id
5619: AND paip.line_number = pbr.line_number);
5620:
5647: bi.award_status,
5648: NVL(bi.award_price , bi.price) * Nvl(BI.AWARD_QUANTITY, DECODE(AI.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE',1 , ai.quantity)) award_amount,
5649: pbh.po_agreed_amount
5650: FROM pon_bid_item_prices bi,
5651: pon_auction_item_prices_all ai,
5652: pon_bid_headers pbh
5653: WHERE bi.bid_number = p_bid_number
5654: AND bi.auction_header_id = ai.auction_header_id
5655: AND ai. auction_header_id = pbh.auction_header_id
5726: AND bid_status = 'ACTIVE'
5727: )
5728: AND auction_line_number <> -1 -- Unsolicited Lines Project : Donot clear unsolicited lines awarded.
5729: AND auction_line_number NOT IN (SELECT line_number
5730: FROM pon_auction_item_prices_all
5731: WHERE auction_header_id = p_auction_header_id
5732: AND Nvl(award_status, 'NO') = 'COMPLETED');
5733:
5734: -- Delete All Awards since it is a header-level awarding
5735: DELETE FROM pon_acceptances
5736: WHERE auction_header_id = p_auction_header_id
5737: AND auction_line_number <> -1 -- Unsolicited Lines Project : Donot delete unsolicited lines records.
5738: AND auction_line_number NOT IN (SELECT line_number
5739: FROM pon_auction_item_prices_all
5740: WHERE auction_header_id = p_auction_header_id
5741: AND Nvl(award_status, 'NO') = 'COMPLETED');
5742:
5743:
5741: AND Nvl(award_status, 'NO') = 'COMPLETED');
5742:
5743:
5744: -- reset the award mode at auction item level
5745: UPDATE pon_auction_item_prices_all
5746: SET award_mode = null
5747: WHERE auction_header_id = p_auction_header_id
5748: AND Nvl(award_status, 'NO') = 'COMPLETED';
5749: ELSE
5763: WHERE auction_header_id = p_auction_header_id
5764: AND bid_status = 'ACTIVE'
5765: )
5766: AND line_number IN (SELECT line_number
5767: FROM pon_auction_item_prices_all
5768: WHERE auction_header_id = p_auction_header_id
5769: AND (line_number = p_line_number
5770: OR parent_line_number = p_line_number));
5771:
5772: -- Delete All group line awards since it is a group-level awarding
5773: DELETE FROM pon_acceptances
5774: WHERE auction_header_id = p_auction_header_id
5775: AND line_number IN (SELECT line_number
5776: FROM pon_auction_item_prices_all
5777: WHERE auction_header_id = p_auction_header_id
5778: AND parent_line_number = p_line_number);
5779: END IF;
5780: END IF;