[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_all_bid_item_prices
(
p_bid_number IN NUMBER,
p_award_status IN VARCHAR2,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER
) IS
l_max_line_number NUMBER;
select nvl(max(line_number),0)
into l_max_line_number
from pon_bid_item_prices
where bid_number = p_bid_number;
UPDATE PON_BID_ITEM_PRICES pbip
SET
(pbip.award_status,
pbip.award_quantity,
pbip.award_date,
pbip.last_update_date,
pbip.last_updated_by,
pbip.award_price,
pbip.award_shipment_number) =
(
select
p_award_status,
decode (paip.group_type, 'LOT_LINE', null,
'GROUP', null,
decode (paha.contract_type, 'BLANKET', null,
decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
'AMOUNT', 1,
'RATE', 1, pbip.quantity ))),
p_award_date,
p_award_date,
p_auctioneer_id,
pbip.price,
null
from
pon_auction_item_prices_all paip,
pon_auction_headers_all paha
where pbip.bid_number = p_bid_number
and pbip.auction_header_id = paip.auction_header_id
and pbip.line_number = paip.line_number
and paha.auction_header_id = pbip.auction_header_id
)
where
pbip.bid_number = p_bid_number and
pbip.line_number >= l_batch_start and
pbip.line_number <= l_batch_end;
END update_all_bid_item_prices;
PROCEDURE update_all_auction_item_prices
(
p_auction_id IN NUMBER,
p_bid_number IN NUMBER,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER
) IS
l_batch_size NUMBER;
select nvl(max(line_number),0)
into l_max_line_number
from pon_auction_item_prices_all
where auction_header_id = p_auction_id;
UPDATE pon_auction_item_prices_all paip
SET
(paip.award_status,
paip.awarded_quantity,
paip.award_mode,
paip.last_update_date,
paip.last_updated_by) =
(
select
decode (pbip.award_status, 'AWARDED', 'AWARDED', 'REJECTED', 'AWARDED', 'PARTIAL', 'AWARDED', to_char(null)),
decode (paip.group_type, 'LOT_LINE', null,
'GROUP', null,
decode (paip.order_type_lookup_code, 'FIXED PRICE', 1,
'AMOUNT', 1,
'RATE', 1,
decode (paha.contract_type, 'BLANKET', 1, 'CONTRACT', 1, pbip.quantity) ) ),
g_AWARD_QUOTE,
p_award_date,
p_auctioneer_id
from
pon_bid_item_prices pbip,
pon_auction_headers_all paha
where pbip.bid_number = p_bid_number
and pbip.auction_header_id = paip.auction_header_id
and pbip.line_number = paip.line_number
and paha.auction_header_id = pbip.auction_header_id
)
where
paip.auction_header_id = p_auction_id and
paip.line_number >= l_batch_start and
paip.line_number <= l_batch_end;
END update_all_auction_item_prices;
UPDATE pon_award_items_interface
SET award_quantity = 0
WHERE batch_id = p_batch_id
AND award_quantity < 0;
select al.line_number,
al.line_origination_code,
nvl(al.award_status,'NO'),
bl.bid_number,
bl.order_number,
bl.award_quantity
from pon_auction_item_prices_all al,
pon_bid_item_prices bl,
pon_bid_headers bh
where al.auction_header_id = p_auction_header_id
and bl.auction_header_id = al.auction_header_id
and bl.line_number = al.line_number
and bh.bid_number = bl.bid_number
and nvl(bh.bid_status,'NONE') = 'ACTIVE'
-- we get lines with award decision made but have some bids unawarded
--and nvl(al.award_status,'NO') = 'NO'
and nvl(bl.award_status,'NO') = 'NO';
select nvl(ah.award_status,'NO'),
bh.bid_number
from pon_auction_headers_all ah,
pon_bid_headers bh
where bh.auction_header_id = p_auction_header_id
and bh.auction_header_id = ah.auction_header_id
and nvl(bh.bid_status,'NONE') = 'ACTIVE'
and nvl(bh.award_status,'NO') = 'NO';
update_single_bid_item_prices
(
x_bid_number,
x_line_number,
'REJECTED',
x_award_quantity,
sysdate,
p_user_id
);
-- Update acceptances for the lines with no award decision made
update_unawarded_acceptances(
p_auction_header_id, -- auction header id
x_line_number, -- line number
p_note_to_rejected, --note to rejected suppliers
SYSDATE, -- award_date
p_user_id);
-- Update acceptances for the lines with award decision already made
x_stored_note_to_rejected := null;
SELECT count(*) INTO x_count FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number = x_line_number
AND ACCEPTANCE_TYPE = 'REJECTED';
SELECT distinct REASON INTO x_stored_note_to_rejected
FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number = x_line_number
AND ACCEPTANCE_TYPE = 'REJECTED';
update_unawarded_acceptances(
p_auction_header_id, -- auction header id
x_line_number, -- line number
x_stored_note_to_rejected, --note to rejected suppliers
SYSDATE, -- award_date
p_user_id);
update_single_bid_header(x_bid_number_list(i), p_user_id);
update_auction_headers(p_auction_header_id, g_AWARD_LINE, SYSDATE, p_user_id, 'Y');
-- update the award status for the active bids in this auction where no
-- award decision made (all bids will be rejected)
FORALL k IN 1..x_bid_number_list.COUNT
UPDATE PON_BID_HEADERS
SET AWARD_STATUS = 'REJECTED',
AWARD_DATE = SYSDATE, /* new column created as part of CPA project.
It will be updated only when negotiation does
not have lines. */
last_update_date = SYSDATE,
last_updated_by = p_user_id
WHERE bid_number = x_bid_number_list(k);
-- update the award status for the auction that was bidded on
-- and no award decision made
IF x_bid_number_list.COUNT > 0 THEN
update_auction_headers(p_auction_header_id, g_AWARD_QUOTE, SYSDATE,
p_user_id, 'N');
SELECT line_number, line_origination_code
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE auction_header_id = p_auction_header_id
AND nvl(number_of_bids,0) = 0
AND line_origination_code = 'REQUISITION';
SELECT line_number, nvl(awarded_quantity, 0)
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE auction_header_id = p_auction_header_id;
instead of looping over all the lines, we can update all
lines in a single query
*/
/*
rrkulkar-large-auction-support : commented out the call to complete_item_disposition
need to add batching here
*/
update pon_auction_item_prices_all
set AWARD_STATUS = 'COMPLETED',
LAST_UPDATE_DATE = sysdate,
AWARDED_QUANTITY = nvl(awarded_quantity,0)
where auction_header_id = p_auction_header_id;
update pon_auction_headers_all
set outcome_status = p_outcome_status,
award_complete_date = sysdate,
source_reqs_flag = p_source_reqs_flag,
share_award_decision = decode(p_no_bids_flag, 'Y', 'I', share_award_decision),
last_update_date = sysdate
where auction_header_id = p_auction_header_id;
update pon_auction_headers_all
set AWARD_STATUS = 'COMPLETED',
AUCTION_STATUS = 'AUCTION_CLOSED',
REQUEST_ID = NULL,
LAST_UPDATE_DATE = sysdate
where auction_header_id = p_auction_header_id;
select b.bid_number,
b.trading_partner_contact_name contact,
a.trading_partner_name auctioneer,
a.auction_title
from pon_bid_headers b,
pon_auction_headers_all a
where b.auction_header_id = p_auction_header_id
and not nvl(b.bid_status,'NONE') in ('ARCHIVED','DISQUALIFIED')
and a.auction_header_id = b.auction_header_id;
update pon_auction_headers_all
set SHARE_AWARD_DECISION = p_shared_award_decision
where auction_header_id = p_auction_header_id;
update pon_auction_item_prices_all
set AWARD_STATUS = 'COMPLETED',
LAST_UPDATE_DATE = sysdate,
AWARDED_QUANTITY = p_award_quantity
where auction_header_id = p_auction_header_id
and line_number = p_line_number;
update pon_auction_item_prices_all
set AWARD_STATUS = 'AWARDED',
LAST_UPDATE_DATE = sysdate,
AWARDED_QUANTITY = p_award_quantity
where auction_header_id = p_auction_header_id
and line_number = p_line_number;
, p_last_update_date IN DATE
, p_mode IN VARCHAR2
, p_line_num IN NUMBER
, p_award_table IN PON_AWARD_TABLE
, p_note_to_accepted IN VARCHAR2
, p_note_to_rejected IN VARCHAR2
, p_batch_id IN NUMBER
, x_status OUT NOCOPY VARCHAR2
)
IS
--
l_counter BINARY_INTEGER;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.AWARD_AUCTION',' p_last_update_date : '|| p_last_update_date || ' ,p_mode : '|| p_mode || ' ,p_line_num : '|| p_line_num);
select contract_type
into l_neg_contract_type
from pon_auction_headers_all
where auction_header_id = p_auction_header_id
and rownum =1;
/* update auction-header by nulling out the request-id */
update pon_auction_headers_all
set request_id = to_number(null)
where auction_header_id = p_auction_header_id;
update_all_bid_item_prices(l_winning_bid, get_award_status(l_rec.award_outcome), l_award_date, p_auctioneer_id);
update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
--update total agreed amount (if any)
IF l_rec.total_agreement_amount is not null THEN
UPDATE pon_bid_headers
SET po_agreed_amount = l_rec.total_agreement_amount
WHERE bid_number = l_rec.bid_number;
-- update notes
update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
update_all_auction_item_prices(p_auction_header_id, l_winning_bid, l_award_date, p_auctioneer_id);
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
FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
WHERE bi.bid_number = l_rec.bid_number
AND bi.line_number = l_rec.line_number
AND ai.auction_header_id = bi.auction_header_id
AND ai.line_number = bi.line_number;
SELECT ai.group_type INTO l_group_type
FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
WHERE bi.bid_number = l_rec.bid_number
AND bi.line_number = l_rec.line_number
AND ai.auction_header_id = bi.auction_header_id
AND ai.line_number = bi.line_number;
-- Update Internal Notes and Notes to Suppliers for each bid
IF (l_current_bid_number <> l_rec.bid_number) THEN
update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
--update total agreed amount (if any)
IF l_rec.total_agreement_amount is not null THEN
UPDATE pon_bid_headers
SET po_agreed_amount = l_rec.total_agreement_amount
WHERE bid_number = l_rec.bid_number;
SELECT ai.group_type INTO l_group_type
FROM pon_auction_item_prices_all ai
WHERE ai.auction_header_id = p_auction_header_id
AND ai.line_number = p_line_num;
select nvl(has_quantity_tiers,'N') into l_has_quantity_tiers
from pon_bid_item_prices
where bid_number = l_rec.bid_number
and line_number = p_line_num;
Update the award shipment number acoordingly.
*/
IF ( 'Y' = l_has_quantity_tiers AND l_rec.award_quantity IS NOT NULL)
THEN --{
l_award_shipment_number := -1;
select nvl((select pbs.shipment_number
from pon_bid_shipments pbs, pon_auction_item_prices_all paip
where pbs.bid_number = l_rec.bid_number
and pbs.line_number = p_line_num
AND l_rec.award_quantity >= pbs.quantity
AND l_rec.award_quantity <= pbs.max_quantity
AND paip.auction_header_id = pbs.auction_header_id
AND paip.line_number = pbs.line_number ),-1)
into l_award_shipment_number from dual;
-- Insert errors in interface table.
INSERT INTO PON_INTERFACE_ERRORS(
batch_id
, column_name
, error_message_name
, table_name
, INTERFACE_LINE_ID
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, TOKEN1_NAME
, TOKEN1_VALUE
)
Values(
p_batch_id
, fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
, 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
, 'PON_BID_ITEM_PRICES'
, p_line_num
, SYSDATE+7
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.login_id
, 'BID_NUM'
, l_rec.bid_number
);
--Update Notes only in case of Award Line V Page and NOT for Award Line H Page
IF ((p_mode = g_AWARD_LINE OR p_mode = g_AWARD_GROUP) AND ((x_status is NULL) OR (x_status = 'SUCCESS'))) THEN
update_notes_for_bid(l_rec.bid_number, l_rec.note_to_supplier, l_rec.internal_note, p_auctioneer_id);
update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
update_bid_headers(p_auction_header_id, p_auctioneer_id, l_awarded_bid_headers, l_neg_has_lines);
update_auction_item_prices(p_auction_header_id, null, l_award_date, p_auctioneer_id, p_mode);
update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
bulk_update_pon_acceptances(
p_auction_header_id,
null, null, null,
l_award_date, p_auctioneer_id, p_mode);
update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
update_auction_item_prices(p_auction_header_id,p_line_num, l_award_date, p_auctioneer_id, p_mode);
update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, l_neg_has_lines);
bulk_update_pon_acceptances(
p_auction_header_id, p_line_num,
p_note_to_accepted, p_note_to_rejected,
l_award_date, p_auctioneer_id, p_mode);
IF (((x_status is NULL) OR (x_status = 'SUCCESS')) AND (is_auction_not_updated (p_auction_header_id, p_last_update_date))) THEN
x_status := 'SUCCESS';
-- update the last update date
UPDATE pon_Auction_headers_all
SET last_update_date = SYSDATE
WHERE auction_header_id = p_auction_header_id;
PROCEDURE update_bid_item_prices
(
p_auction_id IN NUMBER,
p_award_lines IN t_award_lines,
p_auctioneer_id IN NUMBER,
p_mode IN VARCHAR2
)
IS
l_size NUMBER;
and once all bid lines are updated, we traverse through this map
and update the required bid groups
*/
type bid_line_asso is table of varchar2(30) index by varchar2(30);
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Entering procedure with p_mode: ' || p_mode || ' ,p_auction_id : ' || p_auction_id || ' ,p_auctioneer_id : '|| p_auctioneer_id);
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'l_index : ' || l_index || ' ; award shipment number : ' || l_award_shipment_number || ' ; award quantity : ' || l_award_quantity);
update_single_bid_item_prices
(
p_award_lines(l_index).bid_number,
p_award_lines(l_index).line_number,
p_award_lines(l_index).award_status,
l_award_quantity,
p_award_lines(l_index).award_date,
p_auctioneer_id
);
SELECT parent_line_number INTO l_parent_line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_id
AND line_number = p_award_lines(l_index).line_number;
update_bi_group_award(
p_auction_id,
l_bid_number,
l_parent_line_number,
sysdate,
p_auctioneer_id
);
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'Returning to the caller.....');
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_BID_ITEM_PRICES', 'An exception occurred during the execution. Raising the exception.....');
END update_bid_item_prices;
PROCEDURE update_single_bid_item_prices
(
p_bid_number IN NUMBER,
p_line_number IN NUMBER,
p_award_status IN VARCHAR2,
p_award_quantity IN NUMBER,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER
)
IS
l_award_price NUMBER;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'Entering the procedure for p_bid_number : ' || p_bid_number || ' ; p_line_number : ' || p_line_number);
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'p_award_quantity : ' || p_award_quantity || ' ;p_award_date : ' || p_award_date || ' ;p_auctioneer_id : ' || p_auctioneer_id);
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES',' ;p_award_status : ' || p_award_status);
SELECT decode(p_award_status, 'REJECTED', null,
decode(nvl(p_award_quantity,0), 0,pbip.price,
pbip.per_unit_price_component + pbip.fixed_amount_component /p_award_quantity))
INTO l_award_price
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
WHERE pbip.bid_number = p_bid_number
AND pbip.line_number = p_line_number
AND paip.auction_header_id = pbip.auction_header_id
AND paip.line_number = pbip.line_number;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'award_price: ' || l_award_price);
UPDATE PON_BID_ITEM_PRICES
SET award_quantity = p_award_quantity,
award_price = l_award_price,
award_status = p_award_status,
award_date = p_award_date,
last_update_date = p_award_date,
last_updated_by = p_auctioneer_id,
award_shipment_number = NULL
WHERE Bid_number = p_bid_number AND
Line_Number = p_line_number;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPDATE_SINGLE_BID_ITEM_PRICES', 'PON_BID_ITEM_PRICES has been updated. Returning to the caller....');
END update_single_bid_item_prices;
* PARAMETERS: 1. p_bid_number - bid number for which the award_price and shipment no to be updated.
* 2. p_line_number - corresponding line number
* 3. p_award_status - award status 'AWARDED' or 'REJECTED'
* 4. p_award_quantity - The quantity awarded
* 5. p_award_date -- Award Datw
* 6. p_auctioneer_id - Id of person who is saving award
* 7. p_award_shipment_number - Quantity awarded falls in the tiers range corresponding to the shipment number
* COMMENT : This procedure calculates the award price based on the per unit and fixed amount component and
* corresponding to the award shipment number. PON_BID_ITEM_PRICES is updated accordingly
*==========================================================================================================================*/
PROCEDURE upd_single_bid_item_prices_qt
(
p_bid_number IN NUMBER,
p_line_number IN NUMBER,
p_award_status IN VARCHAR2,
p_award_quantity IN NUMBER,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER,
p_award_shipment_number IN NUMBER
)
IS
l_award_price NUMBER;
SELECT DECODE(p_award_status, 'REJECTED', NULL,
DECODE (NVL(p_award_quantity,0), 0, pbs.price,
pbs.per_unit_price_component+pbip.fixed_amount_component/p_award_quantity))
INTO l_award_price
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip,
pon_bid_shipments pbs
WHERE pbip.bid_number = p_bid_number
AND pbip.line_number = p_line_number
AND paip.auction_header_id = pbip.auction_header_id
AND paip.line_number = pbip.line_number
AND pbs.bid_number = pbip.bid_number
AND pbs.line_number = pbip.line_number
AND pbs.shipment_number = p_award_shipment_number;
UPDATE PON_BID_ITEM_PRICES
SET award_quantity = p_award_quantity,
award_status = p_award_status,
award_date = p_award_date,
last_update_date = p_award_date,
last_updated_by = p_auctioneer_id,
award_price = l_award_price,
award_shipment_number = p_award_shipment_number
WHERE Bid_number = p_bid_number AND
Line_Number = p_line_number;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, 'pon.plsql.PON_AWARD_PKG.UPD_SINGLE_BID_ITEM_PRICES_QT', 'PON_BID_ITEM_PRICES has been updated. Returning to the caller....');
PROCEDURE update_bid_headers
(
p_auction_id IN NUMBER,
p_auctioneer_id IN NUMBER,
p_awarded_bid_headers IN t_awarded_bid_headers DEFAULT t_emptytbl, -- FPK: CPA
p_neg_has_lines IN VARCHAR2 -- FPK: CPA
)
IS
--
CURSOR c_active_bids (c_auction_id NUMBER) is
SELECT bh.bid_number
FROM pon_bid_headers bh
WHERE bh.auction_header_id = c_auction_id
AND bid_status = 'ACTIVE';
update_single_bid_header (l_active_bids_rec.bid_number,
p_auctioneer_id );
-- Loop through the matrix to update bid headers
-- Map all values into single table arrays to avoid Oracle errors
-- caused by using rec(i).field
IF p_awarded_bid_headers.count > 0 THEN
FOR l_index IN p_awarded_bid_headers.first..p_awarded_bid_headers.last
LOOP
l_bid_headers_count := l_bid_headers_count + 1;
UPDATE PON_BID_HEADERS
SET AWARD_STATUS = l_award_status_tbl(k),
AWARD_DATE = l_award_date_tbl(k), /* new column created as part of CPA project.
It will be updated only when negotiation does
not have lines. */
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE bid_number = l_bid_number_tbl(k);
END update_bid_headers;
PROCEDURE update_single_bid_header
(
p_bid_number IN NUMBER,
p_auctioneer_id IN NUMBER
)
IS
--
CURSOR c_bid_lines (c_bid_number NUMBER) is
SELECT bi.Line_number,
bi.award_status,
nvl(bi.award_price , bi.price) * bi.award_quantity award_price
FROM pon_bid_item_prices bi, pon_auction_item_prices_all ai
WHERE bi.bid_number = c_bid_number
and bi.auction_header_id = ai.auction_header_id
and bi.line_number = ai.line_number
and ai.group_type in ('LOT', 'LINE', 'GROUP_LINE');
SELECT ah.contract_type INTO l_contract_type
FROM pon_auction_headers_all ah, pon_bid_headers bh
WHERE bh.bid_number = p_bid_number
AND bh.auction_header_id = ah.auction_header_id;
UPDATE PON_BID_HEADERS
SET AWARD_STATUS = l_award_status,
total_award_amount = l_award_amount,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE bid_number = p_bid_number;
END update_single_bid_header;
PROCEDURE update_auction_item_prices
(
p_auction_id IN NUMBER,
p_line_number IN NUMBER,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER,
p_mode IN VARCHAR2
)
IS
CURSOR c_auction_items (c_auction_id NUMBER) IS
SELECT line_number, group_type
FROM pon_auction_item_prices_all
WHERE auction_header_id = c_auction_id;
SELECT line_number, group_type
FROM pon_auction_item_prices_all
WHERE auction_header_id = c_auction_id
AND parent_line_number = c_parent_line_number;
update_single_auction_item(p_auction_id,
l_auction_items_rec.line_number,
p_auctioneer_id, p_mode);
update_single_auction_item (p_auction_id,
p_line_number,
p_auctioneer_id,
p_mode);
SELECT group_type INTO l_group_type FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_id
AND line_number = p_line_number;
-- update the child lines
update_single_auction_item(p_auction_id,
l_item_sublines_rec.line_number,
p_auctioneer_id,
p_mode);
update_ai_group_award(p_auction_id,
p_line_number,
p_award_date,
p_auctioneer_id);
END update_auction_item_prices;
PROCEDURE update_single_auction_item
(
p_auction_id IN NUMBER,
p_line_number IN NUMBER,
p_auctioneer_id IN NUMBER,
p_mode IN pon_auction_item_prices_all.award_mode%type
)
IS
CURSOR c_bid_items (c_auction_id NUMBER, c_line_number NUMBER) IS
SELECT bi.Line_number,
ai.order_type_lookup_code,
bi.award_status,
bi.award_quantity,
ai.group_type
FROM pon_bid_item_prices bi,
pon_bid_headers bh,
pon_auction_item_prices_all ai
WHERE bi.auction_header_id = c_auction_id
AND bi.line_number = c_line_number
AND bh.bid_status = 'ACTIVE'
AND bh.auction_header_id = bi.auction_header_id
AND bh.bid_number = bi.bid_number
AND ai.auction_header_id = bi.auction_header_id
AND ai.line_number = bi.line_number;
SELECT ah.contract_type INTO l_contract_type
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auction_id;
UPDATE pon_auction_item_prices_all
SET award_status = l_award_status,
awarded_quantity = l_award_quantity,
award_mode = l_item_award_mode,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE auction_header_id = p_auction_id
AND line_number = p_line_number;
END update_single_auction_item;
PROCEDURE update_auction_headers
(
p_auction_id IN NUMBER,
p_mode IN VARCHAR2,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER,
p_neg_has_lines IN VARCHAR2 -- FPK: CPA
)
IS
--
/*
CURSOR c_auction_lines (c_auction_id NUMBER) is
SELECT Line_number, award_status
FROM pon_auction_item_prices_all
WHERE auction_header_id = c_auction_id
AND number_of_bids > 0
AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
SELECT count(Line_number), sum(decode(award_status, 'AWARDED', 1, 0))
INTO l_total_lines, l_awarded_lines
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_id
AND number_of_bids > 0
AND group_type in ('LOT', 'LINE', 'GROUP_LINE');
select 'AWARDED' -- it means an award decision was made
into l_award_status
from dual
where exists (select 1
from pon_bid_headers
where auction_header_id = p_auction_id
and bid_status = 'ACTIVE'
and award_status IN ('AWARDED', 'REJECTED'));
UPDATE PON_Auction_HEADERS_all
SET AWARD_STATUS = l_award_status,
award_mode = l_award_mode,
award_date = p_award_date,
last_updated_by = p_auctioneer_id
-- modified after last update date check
--award_approval_status = 'REQUIRED'
--last_update_date = SYSDATE
WHERE auction_header_id = p_auction_id ;
UPDATE PON_AUCTION_HEADERS_ALL
SET award_approval_status = 'REQUIRED'
WHERE auction_header_id = p_auction_id
AND nvl(award_approval_flag, 'N') = 'Y';
END update_auction_headers;
PROCEDURE update_award_agreement_amount
(
p_auction_id IN NUMBER,
p_auctioneer_id IN NUMBER
)
IS
BEGIN
-- Updates approval_status if approval flag is set
UPDATE PON_AUCTION_HEADERS_ALL
SET award_approval_status = 'REQUIRED'
WHERE auction_header_id = p_auction_id
AND nvl(award_approval_flag, 'N') = 'Y';
UPDATE PON_Auction_HEADERS_all
SET award_date = SYSDATE,
last_updated_by = p_auctioneer_id,
last_update_date = SYSDATE
WHERE auction_header_id = p_auction_id ;
END update_award_agreement_amount;
PROCEDURE bulk_update_pon_acceptances
( p_auction_header_id IN NUMBER,
p_line_number IN NUMBER,
p_note_to_accepted IN VARCHAR2,
p_note_to_rejected IN VARCHAR2,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER,
p_mode IN VARCHAR2
)
IS
BEGIN
IF(p_line_number > 0 ) THEN
IF (p_mode = g_AWARD_GROUP OR p_mode = g_AWARD_GROUP_H) THEN
-- Group Level Awards
-- Insert empty notes for group lines
-- Delete notes for a line
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number IN (SELECT line_number FROM pon_auction_item_prices_all
WHERE parent_line_number = p_line_number
AND auction_header_id = p_auction_header_id);
INSERT INTO pon_acceptances (
acceptance_id,
auction_header_id,
auction_line_number,
bid_number,
line_number,
acceptance_type,
acceptance_date,
reason,
creation_date,
created_by)
SELECT pon_acceptances_s.nextval,
bi.auction_header_id,
bi.auction_line_number,
bi.bid_number,
bi.line_number,
bi.award_status,
p_award_date,
null,
p_award_date,
p_auctioneer_id
FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
WHERE bi.auction_header_id = ai.auction_header_id
AND ai.line_number = bi.line_number
AND (bi.award_status = 'AWARDED'
OR bi.award_status = 'REJECTED')
AND bi.bid_number = bh.bid_number
AND bh.bid_status = 'ACTIVE'
AND ai.auction_header_id = p_auction_header_id
AND ai.parent_line_number = p_line_number;
-- Delete notes for a line
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number = p_line_number;
INSERT INTO pon_acceptances (
acceptance_id,
auction_header_id,
auction_line_number,
bid_number,
line_number,
acceptance_type,
acceptance_date,
reason,
creation_date,
created_by)
SELECT pon_acceptances_s.nextval,
bi.auction_header_id,
bi.auction_line_number,
bi.bid_number,
bi.line_number,
bi.award_status,
p_award_date,
decode (bi.award_status,
'AWARDED', p_note_to_accepted,
'REJECTED', p_note_to_rejected,
null),
SYSDATE,
p_auctioneer_id
FROM pon_bid_item_prices bi, pon_bid_headers bh
WHERE bi.auction_header_id = p_auction_header_id
AND bi.line_number = p_line_number
AND (bi.award_status = 'AWARDED'
OR bi.award_status = 'REJECTED')
AND bi.bid_number = bh.bid_number
AND bh.bid_status = 'ACTIVE';
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id;
INSERT INTO pon_acceptances (
acceptance_id,
auction_header_id,
auction_line_number,
bid_number,
line_number,
acceptance_type,
acceptance_date,
reason,
creation_date,
created_by)
SELECT pon_acceptances_s.nextval,
bi.auction_header_id,
bi.auction_line_number,
bi.bid_number,
bi.line_number,
bi.award_status,
p_award_date,
decode (bi.award_status,
'AWARDED', p_note_to_accepted,
'REJECTED', p_note_to_rejected,
null),
p_award_date,
p_auctioneer_id
FROM pon_bid_item_prices bi, pon_bid_headers bh, pon_auction_item_prices_all ai
WHERE bi.auction_header_id = p_auction_header_id
AND (bi.award_status = 'AWARDED'
OR bi.award_status = 'REJECTED')
AND bi.bid_number = bh.bid_number
AND bh.bid_status = 'ACTIVE'
AND bi.auction_header_id = ai.auction_header_id
AND bi.line_number = ai.line_number
AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE');
END bulk_update_pon_acceptances;
PROCEDURE update_unawarded_acceptances
( p_auction_header_id IN NUMBER,
p_line_number IN NUMBER,
p_note_to_rejected IN VARCHAR2,
p_award_date IN DATE,
p_auctioneer_id IN NUMBER
)
IS
BEGIN
-- Award Line Mode
-- Delete rejected notes for a line
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number = p_line_number
AND acceptance_type = 'REJECTED';
INSERT INTO pon_acceptances (
acceptance_id,
auction_header_id,
auction_line_number,
bid_number,
line_number,
acceptance_type,
acceptance_date,
reason,
creation_date,
created_by)
SELECT pon_acceptances_s.nextval,
bi.auction_header_id,
bi.auction_line_number,
bi.bid_number,
bi.line_number,
'REJECTED',
p_award_date,
p_note_to_rejected,
p_award_date,
p_auctioneer_id
FROM pon_bid_item_prices bi, pon_bid_headers bh
WHERE bi.auction_header_id = p_auction_header_id
AND bi.line_number = p_line_number
AND nvl(bi.award_status, 'NO') <> 'AWARDED' -- can be REJECTED/ NO
AND bi.bid_number = bh.bid_number
AND bh.bid_status = 'ACTIVE';
END update_unawarded_acceptances;
PROCEDURE update_notes_for_bid
(
p_bid_number IN NUMBER,
p_note_to_supplier IN VARCHAR2,
p_internal_note IN VARCHAR2,
p_auctioneer_id IN NUMBER
)
IS
BEGIN
UPDATE pon_bid_headers
SET Internal_note = p_internal_note,
note_to_supplier = p_note_to_supplier
WHERE bid_number = p_bid_number;
END update_notes_for_bid;
UPDATE pon_bid_item_prices
SET award_status = 'REJECTED',
award_quantity = NULL,
award_date = p_award_date,
last_update_date = p_award_date,
last_updated_by = p_auctioneer_id,
award_shipment_number = NULL,
award_price = NULL
WHERE bid_number IN (
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE'
);
-- Delete All Awards since it is a header-level awarding
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id;
UPDATE pon_auction_item_prices_all
SET award_mode = null
WHERE auction_header_id = p_auction_header_id;
UPDATE pon_bid_item_prices
SET award_status = 'REJECTED',
award_quantity = NULL,
award_date = p_award_date,
last_update_date = p_award_date,
last_updated_by = p_auctioneer_id,
award_shipment_number = NULL,
award_price = NULL
WHERE bid_number IN (
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE'
)
AND line_number IN (SELECT line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND (line_number = p_line_number
OR parent_line_number = p_line_number));
DELETE FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
AND line_number IN (SELECT line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND parent_line_number = p_line_number);
Reset notes for all the bids and update all active bids award status to REJECTED no matter
if negotiation has lines or not. Previoulsy award_status was not being updated to REJECTED when
negotiation had lines, but there is no harm in doing so at this point, as award_status will be
updated later in update_single_bid_header procedure. */
UPDATE pon_bid_headers
SET award_status = 'REJECTED',
note_to_supplier = NULL,
internal_note = NULL,
po_agreed_amount = NULL,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE';
UPDATE pon_bid_item_prices
SET award_status = 'REJECTED',
award_quantity = NULL,
award_date = p_award_date,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id,
award_price = NULL
WHERE bid_number IN (
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE'
);
UPDATE pon_bid_headers
SET po_agreed_amount = NULL,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE bid_number IN (
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE'
);
p_last_update_date IN DATE,
x_status OUT NOCOPY VARCHAR2
)
IS
l_auction_header_id NUMBER;
SELECT COUNT(DISTINCT pbh.bid_number)
INTO l_num_of_non_shortlisted_supp
FROM pon_optimize_results por, pon_bid_headers pbh
WHERE por.bid_number = pbh.bid_number
AND pbh.shortlist_flag = 'N'
AND por.scenario_id = p_scenario_id;
SELECT auction_header_id, pon_auction_summary_s.NEXTVAL
INTO l_auction_header_id, l_batch_id
FROM pon_optimize_scenarios
WHERE scenario_id = p_scenario_id;
INSERT into pon_auction_summary
(batch_id,
auction_id,
bid_number,
line_number,
award_quantity,
award_shipment_number)
SELECT
l_batch_id,
l_auction_header_id,
por.bid_number,
por.line_number,
por.award_quantity,
por.award_shipment_number
FROM pon_optimize_results por, pon_auction_item_prices_all paip,
pon_auction_headers_all pah,
pon_bid_item_prices pbip
WHERE pah.auction_header_id = l_auction_header_id
AND pah.auction_header_id = paip.auction_header_id
AND por.bid_number = pbip.bid_number
AND por.line_number = pbip.line_number
AND por.scenario_id = p_scenario_id
AND paip.line_number = por.line_number;
save_award_recommendation(l_batch_id, p_auctioneer_id, p_last_update_date, l_mode, x_status);
UPDATE pon_optimize_scenarios
SET accepted_date = NULL
WHERE accepted_date IS NOT NULL
AND auction_header_id= l_auction_header_id;
UPDATE pon_optimize_scenarios
SET accepted_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = p_auctioneer_id
WHERE scenario_id = p_scenario_id;
DELETE FROM pon_auction_summary
WHERE batch_id = l_batch_id;
p_last_update_date IN DATE,
p_mode IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_reco_awards (c_batch_id NUMBER) IS
SELECT
pas.auction_id,
pas.line_number,
pas.bid_number,
decode(p_mode,
g_AWARD_OPTIMIZATION, decode(ai.order_type_lookup_code, 'RATE', decode(ai.quantity, NULL, NULL, pas.award_quantity), 'QUANTITY', decode(ai.quantity, NULL, NULL, pas.award_quantity), pas.award_quantity),
decode(ai.order_type_lookup_code, 'RATE', decode(ai.quantity, NULL, NULL, pas.award_quantity), 'QUANTITY', decode(ai.quantity, NULL, NULL, pas.award_quantity), pas.award_quantity))award_quantity,
pas.bid_price,
pas.trading_partner_id,
pas.trading_partner_contact_id,
pas.batch_id,
ai.group_type,
pas.award_shipment_number
FROM pon_auction_summary pas
, pon_auction_item_prices_all ai
, pon_auction_headers_all ah
WHERE
pas.award_quantity >0
AND pas.batch_id = c_batch_id
AND ah.auction_header_id = pas.auction_id
AND ai.auction_header_id = pas.auction_id
AND ai.line_number = pas.line_number
ORDER BY
pas.line_number;
l_current_update_date DATE;
update_bid_item_prices(l_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
update_bid_headers(l_auction_header_id, p_auctioneer_id, t_emptytbl, 'Y');
update_auction_item_prices(l_auction_header_id,null, l_award_date, p_auctioneer_id, p_mode);
update_auction_headers(l_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
bulk_update_pon_acceptances(
l_auction_header_id,
null, null, null,
l_award_date, p_auctioneer_id, p_mode);
IF (is_auction_not_updated (l_auction_header_id, p_last_update_date)) THEN
x_status := 'SUCCESS';
-- update the last update date
UPDATE PON_Auction_HEADERS_all
SET last_update_date = SYSDATE
WHERE auction_header_id = l_auction_header_id;
SELECT USER_ID
INTO l_fnd_user_id
FROM FND_USER
WHERE PERSON_PARTY_ID = p_user_id
AND NVL(END_DATE,SYSDATE+1) > SYSDATE;
SELECT USER_ID
INTO l_fnd_user_id
FROM FND_USER
WHERE PERSON_PARTY_ID = p_user_id
AND NVL(END_DATE,SYSDATE+1) > SYSDATE
AND ROWNUM = 1;
select auction_header_id into l_auction_header_id
from pon_optimize_scenarios
where scenario_id = p_scenario_id;
select pon_optimize_scenarios_s.nextval
into l_new_scenario_id from dual;
select max(scenario_number) + 1
into l_next_scenario_number
from pon_optimize_scenarios
where auction_header_id = l_auction_header_id
and (cost_scenario_flag is null or cost_scenario_flag <> 'Y');
INSERT INTO PON_OPTIMIZE_SCENARIOS(
auction_header_id,
scenario_id,
scenario_name,
scenario_number,
objective_code,
status,
price_type,
internal_note,
updated_tp_contact_id,
last_tp_update_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
cost_scenario_flag,
parent_scenario_id,
constraint_priority_type)
SELECT auction_header_id,
l_new_scenario_id,
scenario_name,
l_next_scenario_number,
objective_code,
'NOT_RUN',
price_type,
internal_note,
p_user_id,
sysdate,
sysdate,
l_fnd_user_id,
sysdate,
l_fnd_user_id,
l_fnd_user_id,
p_cost_scenario_flag,
decode(p_cost_scenario_flag, 'Y', p_scenario_id, to_number(null)),
nvl(constraint_priority_type, 'MANDATORY')
FROM pon_optimize_scenarios
WHERE scenario_id = p_scenario_id;
select count(*) into l_num_constraints
from pon_optimize_constraints
where scenario_id = p_scenario_id;
INSERT INTO PON_OPTIMIZE_CONSTRAINTS(
scenario_id,
sequence_number,
auction_header_id,
constraint_type,
line_number,
min_amount,
max_amount,
amount_type,
min_quantity,
max_quantity,
quantity_cutoff,
price_cutoff,
split_award_flag,
integral_qty_award_flag,
excluded_flag,
from_date,
to_date,
min_score,
supp_classification,
attr_sequence_number,
attr_group_name,
trading_partner_id,
trading_partner_contact_id,
vendor_site_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
MIN_MAX_AMOUNT_PRIORITY
,MIN_MAX_AMOUNT_COST
,MIN_MAX_AMOUNT_INFEAS_FLAG
,MIN_MAX_QUANTITY_PRIORITY
,MIN_MAX_QUANTITY_COST
,MIN_MAX_QUANTITY_INFEAS_FLAG
,QUANTITY_CUTOFF_PRIORITY
,QUANTITY_CUTOFF_COST
,QUANTITY_CUTOFF_INFEAS_FLAG
,PRICE_CUTOFF_PRIORITY
,PRICE_CUTOFF_COST
,PRICE_CUTOFF_INFEAS_FLAG
,SPLIT_AWARD_PRIORITY
,SPLIT_AWARD_INFEAS_FLAG
,SPLIT_AWARD_COST
,INTEGRAL_QTY_AWARD_PRIORITY
,INTEGRAL_QTY_AWARD_INFEAS_FLAG
,INTEGRAL_QTY_AWARD_COST
,EXCLUDED_SUPPLIER_PRIORITY
,EXCLUDED_SUPPLIER_INFEAS_FLAG
,EXCLUDED_SUPPLIER_COST
,PROMISED_DATE_PRIORITY
,PROMISED_DATE_COST
,PROMISED_DATE_INFEAS_FLAG
,MIN_SCORE_PRIORITY
,MIN_SCORE_COST
,MIN_SCORE_INFEAS_FLAG)
SELECT l_new_scenario_id,
sequence_number,
auction_header_id,
constraint_type,
line_number,
min_amount,
max_amount,
amount_type,
min_quantity,
max_quantity,
quantity_cutoff,
price_cutoff,
split_award_flag,
integral_qty_award_flag,
excluded_flag,
from_date,
to_date,
min_score,
supp_classification,
attr_sequence_number,
attr_group_name,
trading_partner_id,
trading_partner_contact_id,
vendor_site_id,
sysdate,
l_fnd_user_id,
sysdate,
l_fnd_user_id,
l_fnd_user_id,
MIN_MAX_AMOUNT_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,MIN_MAX_QUANTITY_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,QUANTITY_CUTOFF_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,PRICE_CUTOFF_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,SPLIT_AWARD_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,INTEGRAL_QTY_AWARD_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,EXCLUDED_SUPPLIER_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,PROMISED_DATE_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
,MIN_SCORE_PRIORITY
,TO_NUMBER(NULL)
,TO_CHAR(NULL)
FROM pon_optimize_constraints
WHERE scenario_id = p_scenario_id;
select count(*) into l_num_bid_classes
from pon_optimize_bid_class
where scenario_id = p_scenario_id;
INSERT INTO PON_OPTIMIZE_BID_CLASS(
scenario_id,
sequence_number,
bid_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT l_new_scenario_id,
sequence_number,
bid_number,
sysdate,
l_fnd_user_id,
sysdate,
l_fnd_user_id,
l_fnd_user_id
FROM pon_optimize_bid_class
WHERE scenario_id = p_scenario_id;
p_last_update_date IN DATE,
x_status OUT NOCOPY VARCHAR2
)
IS
l_award_date DATE;
update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
x_status := 'SUCCESS';
-- update the last update date
UPDATE pon_Auction_headers_all
SET last_update_date = SYSDATE
WHERE auction_header_id = p_auction_header_id;
p_last_update_date IN DATE,
p_batch_enabled IN VARCHAR2,
p_is_xml_upload IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_spsheet_awards (c_batch_id NUMBER, c_auction_header_id NUMBER, c_is_xml_upload VARCHAR2) IS
--Query retrives rows ordered in way exported in spreadhsheet
SELECT
aii.auction_header_id,
aii.auction_line_number,
aii.bid_number,
DECODE (nvl(aii.award_status,'N'),'N',null,
DECODE (ai.ORDER_TYPE_LOOKUP_CODE,
'QUANTITY', aii.award_quantity,
'RATE' , decode (ai.purchase_basis , 'TEMP LABOR' , aii.award_quantity ,1) ,
1) )award_quantity,
decode (nvl(aii.award_status,'N'),'Y', g_AWARD_OUTCOME_WIN,
g_AWARD_OUTCOME_LOSE) award_outcome,
aii.awardreject_reason,
ai.group_type,
aii.award_shipment_number
FROM pon_award_items_interface aii,
pon_auction_item_prices_all ai,
pon_bid_item_prices bi,
pon_auction_headers_all pah
WHERE
aii.batch_id = c_batch_id
AND aii.auction_header_id = c_auction_header_id
AND aii.auction_header_id = ai.auction_header_id
AND aii.AUCTION_LINE_NUMBER = ai.LINE_NUMBER
AND bi.bid_number = aii.bid_number
AND bi.line_number = aii.AUCTION_LINE_NUMBER
AND pah.auction_header_id = aii.auction_header_id
ORDER BY
ai.disp_line_number asc,
decode(c_is_xml_upload, 'Y', decode(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', decode(nvl(bi.PRICE,0), 0, 0, nvl(bi.TOTAL_WEIGHTED_SCORE,0)/bi.PRICE), decode(bi.PRICE,0,0, 1/bi.PRICE)), decode(bi.PRICE, 0, 0, 1/bi.PRICE)) desc, bi.PUBLISH_DATE asc;
l_current_update_date DATE;
SELECT DISTINCT auction_line_number BULK COLLECT INTO l_item_list
FROM pon_award_items_interface
WHERE batch_id = p_batch_id
AND auction_header_id = p_auction_header_id;
SELECT DISTINCT bid_number BULK COLLECT INTO l_bid_list
FROM pon_award_items_interface
WHERE batch_id = p_batch_id
AND auction_header_id = p_auction_header_id;
-- new line ; update curr line
-- this procedure updates all the bid lines one-by-one
update_bid_item_prices(p_auction_header_id,l_award_lines,p_auctioneer_id, p_mode);
-- this procedure updates the award_status
-- for all the bids at the bid-header level
-- we don't need to invoke this over here -> this should be invoked after all
-- batches are exhausted
update_single_bid_header(l_bid_list(l_index),p_auctioneer_id);
update_auction_item_prices(p_auction_header_id, l_item_list(l_index), l_award_date, p_auctioneer_id, p_mode);
bulk_update_pon_acceptances (
p_auction_header_id, l_item_list(l_index),
l_accept_list(l_item_list(l_index)), l_reject_list(l_item_list(l_index)),
l_award_date, p_auctioneer_id, p_mode);
update_auction_headers(p_auction_header_id, p_mode, l_award_date, p_auctioneer_id, 'Y');
IF (is_auction_not_updated (p_auction_header_id, p_last_update_date)) THEN
x_status := 'SUCCESS';
-- update the last update date
UPDATE pon_Auction_headers_all
SET last_update_date = SYSDATE
WHERE auction_header_id = p_auction_header_id;
delete from pon_award_items_interface
where batch_id = p_batch_id;
FUNCTION is_auction_not_updated (
p_auction_header_id NUMBER,
p_last_update_date DATE
) RETURN BOOLEAN
IS
l_current_update_date DATE;
SELECT last_update_date INTO l_current_update_date
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
IF (l_current_update_date = p_last_update_date) THEN
RETURN TRUE;
END is_auction_not_updated;
SELECT PERSON_PARTY_ID INTO l_person_id
FROM FND_USER
WHERE user_id = p_user_id;
UPDATE PON_BID_HEADERS
SET SHORTLIST_FLAG = 'N'
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = p_user_id
, SHORTLIST_TPC_ID = l_person_id
WHERE BID_NUMBER = p_bid_number;
UPDATE PON_BID_HEADERS
SET SHORTLIST_FLAG = 'Y'
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = p_user_id
, SHORTLIST_TPC_ID = l_person_id
WHERE BID_NUMBER = p_bid_number;
SELECT SUM(DECODE(ah.contract_type, 'STANDARD', bh.total_award_amount, bh.po_agreed_amount * (1/nvl(bh.rate, 1))))
INTO l_award_amount
FROM
pon_auction_headers_all ah,
pon_bid_headers bh
WHERE
ah.auction_header_id = p_auction_header_id
AND ah.auction_header_id = bh.auction_header_id
AND bh.award_status in ('AWARDED', 'PARTIAL');
p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE)
IS
CURSOR c_sublines (c_auction_header_id pon_bid_headers.auction_header_id%TYPE,
c_bid_number pon_bid_headers.bid_number%TYPE,
c_parent_line_number pon_bid_item_prices.line_number%TYPE) IS
--Query retrives sublines for the given parent line
SELECT
bi.line_number,
DECODE (p_award_status, 'AWARDED',decode (aii.group_type,
'LOT_LINE', null, decode (aii.order_type_lookup_code,
'FIXED PRICE', 1,
'AMOUNT', 1,
'RATE', decode (aii.purchase_basis, 'TEMP LABOR', bi.quantity, 1), bi.quantity )), null) award_quantity
FROM pon_bid_item_prices bi, pon_auction_item_prices_all aii
WHERE
bi.bid_number = c_bid_number
AND bi.line_number IN (SELECT ai.line_number
FROM pon_auction_item_prices_all ai
WHERE ai.parent_line_number = c_parent_line_number
AND ai.auction_header_id = bi.auction_header_id )
AND aii.auction_header_id = bi.auction_header_id
AND aii.line_number = bi.line_number;
-- update the child lines
update_single_bid_item_prices
(
p_bid_number,
l_sublines_rec.line_number,
p_award_status,
l_sublines_rec.award_quantity,
p_award_date,
p_auctioneer_id
);
PROCEDURE update_bi_group_award (
p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
p_award_date IN pon_bid_item_prices.award_date%TYPE,
p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE )
IS
l_total_lines NUMBER;
SELECT count (*) ,
sum(decode(bi.award_status,'AWARDED',1,0)) ,
sum(decode(bi.award_status,'REJECTED',1,0))
INTO l_total_lines,
l_awarded_lines,
l_rejected_lines
FROM pon_auction_item_prices_all ai, pon_bid_item_prices bi
WHERE ai.parent_line_number = p_parent_line_number
AND ai.auction_header_id = p_auction_header_id
and ai.auction_header_id = bi.auction_header_id(+)
and bi.bid_number = p_bid_number
and bi.line_number = ai.line_number;
update_single_bid_item_prices
(
p_bid_number,
p_parent_line_number,
l_award_status,
null,
p_award_date,
p_auctioneer_id
);
END update_bi_group_award;
PROCEDURE update_ai_group_award (
p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_line_number IN pon_bid_item_prices.line_number%TYPE,
p_award_date IN pon_bid_item_prices.award_date%TYPE,
p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE)
IS
l_total_lines NUMBER;
SELECT parent_line_number INTO l_parent_line_number FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id AND line_number = p_line_number;
SELECT COUNT(*) INTO l_total_lines
FROM pon_auction_item_prices_all ai
WHERE parent_line_number = l_parent_line_number
and auction_header_id = p_auction_header_id;
select COUNT(*) INTO l_awarded_lines
FROM pon_auction_item_prices_all
WHERE parent_line_number = l_parent_line_number
AND award_status = 'AWARDED'
and auction_header_id = p_auction_header_id;
UPDATE pon_auction_item_prices_all
SET award_status = l_award_status,
awarded_quantity = null,
award_mode = null,
last_update_date = p_award_date,
last_updated_by = p_auctioneer_id
WHERE auction_header_id = p_auction_header_id
AND line_number = l_parent_line_number;
END update_ai_group_award;
SELECT sum(nvl2(PAIP.current_price,
PAIP.current_price * nvl(PAIP.awarded_quantity, 0),
sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))))
INTO p_current_total
FROM pon_bid_item_prices PBIP,
pon_bid_headers PBH,
pon_auction_item_prices_all PAIP
WHERE PAIP.auction_header_id = p_auction_header_id
AND PAIP.auction_header_id = PBIP.auction_header_id (+)
AND PAIP.line_number = PBIP.line_number (+)
AND PBIP.bid_number = PBH.bid_number (+)
AND PBH.bid_status (+) = 'ACTIVE'
AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED')
GROUP BY
PAIP.line_number, PAIP.current_price, PAIP.awarded_quantity;
SELECT sum(decode(PBIP.award_status, 'AWARDED', nvl(PBIP.award_quantity, 0), 0) * nvl(PBIP.award_price, 0))
INTO p_award_total
FROM pon_bid_item_prices PBIP,
pon_bid_headers PBH
WHERE PBH.auction_header_id = p_auction_header_id
AND PBIP.bid_number = PBH.bid_number (+)
AND PBH.bid_status (+) = 'ACTIVE'
AND NVL(PBH.award_status, 'NONE') IN ('PARTIAL', 'AWARDED');
SELECT 'Y'
INTO l_bid_exists
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_optimize_bid_class pobc
WHERE pobc.scenario_id = p_scenario_id
AND pobc.sequence_number = p_sequence_number
AND pobc.bid_number = p_bid_number);
SELECT 'Y'
INTO l_scored_attribute_exists
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_attribute_scores pas
WHERE pas.auction_header_id = p_auction_header_id
AND pas.line_number = p_line_number);
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET SPLIT_AWARD_FLAG = decode(SPLIT_AWARD_FLAG, 'Y', 'N', 'Y'),
SPLIT_AWARD_PRIORITY = NVL2(SPLIT_AWARD_PRIORITY,l_priority,null),
SPLIT_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
SPLIT_AWARD_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET INTEGRAL_QTY_AWARD_FLAG = decode(INTEGRAL_QTY_AWARD_FLAG, 'Y', 'N', 'Y'),
INTEGRAL_QTY_AWARD_PRIORITY = NVL2(INTEGRAL_QTY_AWARD_PRIORITY,l_priority,null),
INTEGRAL_QTY_AWARD_INFEAS_FLAG = TO_CHAR(NULL),
INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
SELECT pah.contract_type,
pai.order_type_lookup_code,
nvl(pai.quantity, 1)
INTO l_contract_type,
l_order_type_lookup_code,
l_auction_qty
FROM pon_auction_headers_all pah,
pon_auction_item_prices_all pai,
pon_optimize_scenarios pos
WHERE pah.auction_header_id = pai.auction_header_id
AND pah.auction_header_id = pos.auction_header_id
AND pai.line_number = p_line_number
AND pos.scenario_id = l_new_scenario_id;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED, l_module, 'Selecting auction info in LINE_AWARD_QTY internal type constraint if condition caused error');
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_QUANTITY = 0,
MAX_QUANTITY = DECODE(l_order_type_lookup_code, 'FIXED PRICE', 1, 'AMOUNT', 1, l_auction_qty),
MIN_MAX_QUANTITY_PRIORITY = NVL2(MIN_MAX_QUANTITY_PRIORITY,l_priority,null),
MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_AMOUNT = TO_NUMBER(NULL),
MAX_AMOUNT = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET QUANTITY_CUTOFF = TO_NUMBER(NULL),
QUANTITY_CUTOFF_PRIORITY = TO_CHAR(NULL),
QUANTITY_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
QUANTITY_CUTOFF_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET PRICE_CUTOFF = TO_NUMBER(NULL),
PRICE_CUTOFF_PRIORITY = TO_CHAR(NULL),
PRICE_CUTOFF_INFEAS_FLAG = TO_CHAR(NULL),
PRICE_CUTOFF_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_SCORE = TO_NUMBER(NULL),
MIN_SCORE_PRIORITY = TO_CHAR(NULL),
MIN_SCORE_INFEAS_FLAG = TO_CHAR(NULL),
MIN_SCORE_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET FROM_DATE = TO_DATE(NULL),
TO_DATE = TO_DATE(NULL),
PROMISED_DATE_PRIORITY = TO_CHAR(NULL),
PROMISED_DATE_INFEAS_FLAG = TO_CHAR(NULL),
PROMISED_DATE_COST = TO_NUMBER(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_QUANTITY = TO_NUMBER(NULL),
MAX_QUANTITY = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_AMOUNT = TO_NUMBER(NULL),
MAX_AMOUNT = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
--DELETE FROM PON_OPTIMIZE_CONSTRAINTS
--WHERE SCENARIO_ID = p_scenario_id
--AND CONSTRAINT_TYPE = p_constraint_type
--AND SEQUENCE_NUMBER = p_sequence_number;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_AMOUNT = TO_NUMBER(NULL),
MAX_AMOUNT = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_AMOUNT = TO_NUMBER(NULL),
MAX_AMOUNT = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_PRIORITY = TO_CHAR(NULL),
MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
MIN_MAX_AMOUNT_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_QUANTITY = TO_NUMBER(NULL),
MAX_QUANTITY = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_PRIORITY = TO_CHAR(NULL),
MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_INFEAS_FLAG = TO_CHAR(NULL)
WHERE SCENARIO_ID = l_new_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
-- we can delete the row from pon_optimize_constraints
-- using the sequence_number
DELETE FROM PON_OPTIMIZE_CONSTRAINTS
WHERE SCENARIO_ID = l_new_scenario_id
AND CONSTRAINT_TYPE = p_constraint_type
AND SEQUENCE_NUMBER = p_sequence_number;
SELECT (PARENT_SCENARIO.TOTAL_AWARD_AMOUNT - COST_SCENARIO.TOTAL_AWARD_AMOUNT),
PARENT_SCENARIO.SCENARIO_ID
INTO L_COST_OF_CONSTRAINT,
L_PARENT_SCENARIO_ID
FROM PON_OPTIMIZE_SCENARIOS COST_SCENARIO,
PON_OPTIMIZE_SCENARIOS PARENT_SCENARIO
WHERE COST_SCENARIO.SCENARIO_ID = p_scenario_id
AND PARENT_SCENARIO.SCENARIO_ID = COST_SCENARIO.PARENT_SCENARIO_ID;
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'After selecting cost: L_COST_OF_CONSTRAINT: ' || L_COST_OF_CONSTRAINT || ' , L_PARENT_SCENARIO_ID: '||L_PARENT_SCENARIO_ID );
-- UPDATE THE CORRESPONDING ROW IN CONSTRAINTS TABLE
-- OF PARENT SCENARIO WITH THIS COST OF CONSTRAINT
IF (p_constraint_type = 'LINE_CONST') THEN
IF (p_internal_type = 'LINE_SPLIT_AWARD') THEN
IF (g_debug_mode = 'Y') THEN
IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT, l_module, 'Before updating LINE_SPLIT_AWARD internal type constraint');
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET SPLIT_AWARD_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET INTEGRAL_QTY_AWARD_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET QUANTITY_CUTOFF_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET PRICE_CUTOFF_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_SCORE_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET PROMISED_DATE_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_AMOUNT_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_MAX_QUANTITY_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET MIN_SCORE_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET EXCLUDED_SUPPLIER_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET SPLIT_AWARD_COST = l_cost_of_constraint
WHERE SCENARIO_ID = l_parent_scenario_id
AND SEQUENCE_NUMBER = p_sequence_number
AND CONSTRAINT_TYPE = p_constraint_type;
DELETE FROM PON_OPTIMIZE_SCENARIOS WHERE SCENARIO_ID = P_SCENARIO_ID;
select count(*) into l_num_constraints
from pon_optimize_constraints
where scenario_id = p_scenario_id;
DELETE FROM PON_OPTIMIZE_CONSTRAINTS WHERE SCENARIO_ID = P_SCENARIO_ID;
select count(*) into l_num_bid_classes
from pon_optimize_bid_class
where scenario_id = p_scenario_id;
DELETE FROM PON_OPTIMIZE_BID_CLASS WHERE SCENARIO_ID = P_SCENARIO_ID;
select count(*) into l_num_results
from pon_optimize_results
where scenario_id = p_scenario_id;
DELETE FROM PON_OPTIMIZE_RESULTS WHERE SCENARIO_ID = P_SCENARIO_ID;
select count(*) into l_num_constraints
from pon_optimize_constraints
where scenario_id = p_scenario_id;
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET
MIN_MAX_AMOUNT_COST = TO_NUMBER(NULL),
MIN_MAX_QUANTITY_COST = TO_NUMBER(NULL),
PRICE_CUTOFF_COST = TO_NUMBER(NULL),
SPLIT_AWARD_COST = TO_NUMBER(NULL),
QUANTITY_CUTOFF_COST = TO_NUMBER(NULL),
INTEGRAL_QTY_AWARD_COST = TO_NUMBER(NULL),
EXCLUDED_SUPPLIER_COST = TO_NUMBER(NULL),
PROMISED_DATE_COST = TO_NUMBER(NULL),
MIN_SCORE_COST = TO_NUMBER(NULL)
WHERE
SCENARIO_ID = P_SCENARIO_ID;
SELECT
bi.bid_number as selected_bid_number,
SUM(por.award_quantity * decode(nvl(por.award_shipment_number,-1),-1,bi.per_unit_price_component,pbs.per_unit_price_component) + bi.fixed_amount_component) AS award_total_sum,
SUM(por.award_quantity * nvl2(ai.current_price, (ai.current_price -por.award_price), 0)) AS savings_amount,
SUM(por.award_quantity * nvl(ai.current_price,por.award_price)) AS current_amount,
nvl(pbh.CURRENT_TOTAL_SPEND, 0) CURRENT_TOTAL_SPEND,
nvl(pbh.FIXED_INCENTIVE, 0) FIXED_INCENTIVE,
nvl(pbh.CURRENT_REBATE,0) CURRENT_REBATE
BULK COLLECT INTO
l_bid_number_col ,
l_award_total_sum_col ,
l_savings_amount_col,
l_current_amount_col,
l_current_total_spend_col,
l_fixed_incentive_col,
l_current_rebate_col
FROM
pon_bid_item_prices bi,
pon_auction_item_prices_all ai,
pon_optimize_scenarios pos,
pon_optimize_results por,
pon_bid_shipments pbs,
pon_bid_headers pbh
WHERE
por.scenario_id = pos.scenario_id
AND
por.bid_number = bi.bid_number
AND
pos.auction_header_id = bi.auction_header_id
AND
ai.auction_header_id = bi.auction_header_id
AND
bi.line_number = por.line_number
AND
ai.line_number = bi.line_number
AND
ai.group_type in ('LINE', 'LOT', 'GROUP_LINE')
AND
nvl(por.award_quantity, -1) > 0
AND
por.bid_number = pbh.bid_number
and
pos.scenario_id = p_scenario_id
AND por.bid_number = pbs.bid_number(+)
AND por.line_number = pbs.line_number(+)
AND nvl(por.award_shipment_number, -1) = pbs.shipment_number(+)
GROUP BY bi.bid_number, pbh.CURRENT_TOTAL_SPEND, pbh.FIXED_INCENTIVE, pbh.CURRENT_REBATE;
select rebate
into l_new_rebate
from pon_bid_rebates
where bid_number = l_bid_number_col(i)
and l_total_award between lower_spend and upper_spend;