[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE
pon_bid_item_prices pbip
SET
pbip.per_unit_price_component = pbip.unit_price,
pbip.fixed_amount_component = 0
WHERE
pbip.bid_number = p_bid_number;
UPDATE
pon_bid_item_prices pbip
SET
pbip.per_unit_price_component = pbip.unit_price +
nvl((
SELECT
SUM(
DECODE (pbpe.pricing_basis,
'PER_UNIT',
Decode (Nvl(pbpe.negative_cost_factor_flag,'N'),'Y',-pbpe.auction_currency_value,pbpe.auction_currency_value),
'PERCENTAGE',
Decode (Nvl(pbpe.negative_cost_factor_flag,'N'),'Y',-pbpe.auction_currency_value,pbpe.auction_currency_value) / 100 * pbip.unit_price, 0))
FROM
pon_bid_price_elements pbpe
WHERE
pbpe.bid_number = pbip.bid_number
AND pbpe.line_number = pbip.line_number
AND pbpe.pricing_basis IN ('PER_UNIT', 'PERCENTAGE')
AND pbpe.sequence_number <> -10), 0),
pbip.fixed_amount_component =
nvl((
SELECT
Sum(Decode (Nvl(pbpe.negative_cost_factor_flag,'N'),'Y',-pbpe.auction_currency_value,pbpe.auction_currency_value))
FROM
pon_bid_price_elements pbpe
WHERE
pbpe.bid_number = pbip.bid_number
AND pbpe.line_number = pbip.line_number
AND pbpe.pricing_basis = 'FIXED_AMOUNT'
AND pbpe.sequence_number <> -10), 0)
WHERE
pbip.bid_number = p_bid_number;
UPDATE
pon_bid_shipments pbs
SET
pbs.per_unit_price_component = pbs.unit_price +
nvl((
SELECT SUM(DECODE(pbpe.pricing_basis,
'PER_UNIT', pbpe.auction_currency_value,
'PERCENTAGE', pbpe.auction_currency_value / 100 * pbs.unit_price, 0))
FROM
pon_bid_price_elements pbpe
WHERE
pbpe.bid_number = pbs.bid_number
AND pbpe.line_number = pbs.line_number
AND pbpe.pricing_basis IN ('PER_UNIT', 'PERCENTAGE')
AND pbpe.sequence_number <> -10), 0)
WHERE
pbs.bid_number = p_bid_number;
SELECT sequence
INTO l_supplier_sequence_number
FROM pon_bidding_parties
WHERE
auction_header_id = p_auction_header_id
AND trading_partner_id = p_trading_partner_id
AND vendor_site_id = p_vendor_site_id;
SELECT rate
INTO l_currency_rate
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
INSERT INTO
pon_bid_price_elements (
bid_number,
line_number,
price_element_type_id,
auction_header_id,
pricing_basis,
auction_currency_value,
bid_currency_value,
sequence_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
pf_type
)
SELECT
p_bid_number,
pfs.line_number,
pfs.price_element_type_id,
pfs.auction_header_id,
pfs.pricing_basis,
bid_lines.unit_price,
bid_lines.bid_currency_unit_price,
pfs.sequence_number,
SYSDATE,
p_login_user_id,
SYSDATE,
p_login_user_id,
pfs.pf_type
FROM
pon_price_elements pfs,
pon_bid_item_prices bid_lines
WHERE
pfs.auction_header_id = p_auction_header_id
AND bid_lines.bid_number = p_bid_number
AND bid_lines.line_number = pfs.line_number
AND pfs.price_element_type_id = -10
AND EXISTS (SELECT NULL
FROM pon_pf_supplier_values pf_values
WHERE
pf_values.auction_header_id = bid_lines.auction_header_id
AND pf_values.line_number = bid_lines.line_number
AND pf_values.supplier_seq_number = l_supplier_sequence_number
AND NVL(pf_values.value, 0) <> 0);
INSERT INTO
pon_bid_price_elements (
bid_number,
line_number,
price_element_type_id,
auction_header_id,
pricing_basis,
auction_currency_value,
bid_currency_value,
sequence_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
pf_type
)
SELECT
p_bid_number,
pfs.line_number,
pfs.price_element_type_id,
pfs.auction_header_id,
pfs.pricing_basis,
pf_values.value,
DECODE('PERCENTAGE', pfs.pricing_basis, pf_values.value,
pf_values.value * l_currency_rate),
pfs.sequence_number,
SYSDATE,
p_login_user_id,
SYSDATE,
p_login_user_id,
pfs.pf_type
FROM
pon_price_elements pfs,
pon_pf_supplier_values pf_values
WHERE
pfs.auction_header_id = p_auction_header_id
AND pf_values.auction_header_id = pfs.auction_header_id
AND pf_values.line_number = pfs.line_number
AND pf_values.pf_seq_number = pfs.sequence_number
AND pfs.pf_type = 'BUYER'
AND pf_values.supplier_seq_number = l_supplier_sequence_number
AND NVL(pf_values.value, 0) <> 0
AND NOT EXISTS (SELECT NULL
FROM pon_bid_price_elements bid_pfs
WHERE
bid_pfs.bid_number = p_bid_number
AND bid_pfs.line_number = pfs.line_number
AND bid_pfs.price_element_type_id = pfs.price_element_type_id);
SELECT trading_partner_id,
trading_partner_contact_id,
Nvl(surrog_bid_flag,'N') --Multiple Active Offers
INTO v_bidTradingPartnerId,
v_bidTradingPartnerContactId,
v_surrog_bid_flag --Multiple Active Offers
FROM pon_bid_headers
WHERE bid_number = p_bidNumber;
SELECT doctype_id,
nvl(amendment_number, 0),
auction_header_id_orig_amend
INTO v_doctypeId,
v_amendmentNumber,
v_auctionHeaderIdOrigAmend
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionheaderid;
SELECT fixed_value
INTO v_fixedValue
FROM pon_auc_bizrules pab,
pon_auc_doctype_rules padr
WHERE pab.name = 'AWARD_TYPE'
AND pab.bizrule_id = padr.bizrule_id
AND padr.doctype_id = v_doctypeId;
UPDATE PON_BID_HEADERS
SET BID_STATUS = 'ARCHIVED',
LAST_UPDATE_DATE = SYSDATE
WHERE bid_number = p_oldBidNumber
AND bid_status IN ('ACTIVE','RESUBMISSION');
UPDATE PON_BID_HEADERS
SET BID_STATUS = 'ARCHIVED',
LAST_UPDATE_DATE = SYSDATE
WHERE AUCTION_HEADER_ID = p_auctionHeaderId
AND BID_NUMBER = p_oldBidNumber
AND BID_STATUS = 'ACTIVE';
UPDATE PON_BID_HEADERS
SET BID_STATUS = 'ARCHIVED',
LAST_UPDATE_DATE = SYSDATE
WHERE AUCTION_HEADER_ID in (
SELECT AUCTION_HEADER_ID
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID_ORIG_AMEND = v_auctionHeaderIdOrigAmend)
AND BID_NUMBER <> p_bidNumber
AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1)
AND TRADING_PARTNER_ID = v_bidTradingPartnerId
AND TRADING_PARTNER_CONTACT_ID = v_bidtradingpartnercontactid
AND BID_STATUS in ('ACTIVE', 'RESUBMISSION');
UPDATE pon_bid_headers bh
SET partial_response_flag = 'N'
WHERE bh.bid_number = p_bidNumber;
UPDATE pon_bid_headers bh
SET partial_response_flag = 'Y'
WHERE bh.bid_number = p_bidNumber
AND (EXISTS (select 'x'
from pon_bid_item_prices bl,
pon_auction_item_prices_all al
where bl.bid_number = bh.bid_number
and bl.auction_header_id = al.auction_header_id
and bl.line_number = al.line_number
and al.group_type <> 'GROUP'
and al.group_type <> 'LOT_LINE'
and al.quantity <> bl.quantity
and al.quantity is not null
and bl.quantity is not null
and Nvl(al.clm_info_flag,'N') <> 'Y') -- bug#9745944
OR
EXISTS (select 'x'
from pon_bid_item_prices bl,
pon_auction_item_prices_all al
where bl.bid_number(+) = bh.bid_number
and al.auction_header_id = bh.auction_header_id
and bl.auction_header_id(+) = al.auction_header_id
and bl.line_number(+) = al.line_number
and al.group_type <> 'GROUP'
and al.group_type <> 'LOT_LINE'
and bl.line_number is null
and Nvl(al.clm_info_flag,'N') <> 'Y')); -- bug#9745944
PROCEDURE update_group_amount (p_bidNumber IN NUMBER )
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_group_amount';
print_log(l_api_name || '.BEGIN', p_bidNumber ||' Begin - update_group_amount');
update pon_bid_item_prices bl
set group_amount = (select sum(nvl(bl2.quantity, decode(al.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', 1, al.quantity))*bl2.price)
from pon_bid_item_prices bl2,
pon_auction_item_prices_all al
where bl2.auction_header_id = al.auction_header_id
and bl2.line_number = al.line_number
and bl2.bid_number = bl.bid_number
and al.parent_line_number = bl.line_number)
where bl.bid_number = p_bidNumber
and (select a2.group_type
from pon_auction_item_prices_all a2
where a2.auction_header_id = bl.auction_header_id
and a2.line_number = bl.line_number) = 'GROUP';
print_log(l_api_name || '.END', p_bidNumber ||' End - update_group_amount');
END update_group_amount;
SELECT fndat.entity_name entity, fndat.attached_document_id attached_document_id,
fndat.seq_num seq_num, dc.datatype_id datatype_id,
dt.description description, dt.file_name file_name, dc.media_id media_id,
fndat.pk3_value pk3,
fndat.pk4_value pk4
FROM fnd_documents dc, fnd_documents_tl dt, fnd_attached_documents fndat
WHERE fndat.document_id = dt.document_id
AND dt.document_id = dc.document_id
AND dt.language = userenv('LANG')
AND fndat.entity_name IN ('PON_BID_ITEM_PRICES', 'PON_BID_HEADERS')
AND fndat.pk1_value = to_char(p_auctionHeaderId)
AND fndat.pk2_value = to_char(p_oldBidNum);
SELECT short_text
INTO v_text
FROM fnd_documents_short_text
WHERE media_id = bid_att.media_id;
SELECT category_id
INTO v_attachment_cat_id
FROM fnd_document_categories
WHERE name = 'Vendor';
FUNCTION clone_update_bid
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_new_publish_date IN DATE,
p_triggerBidNumber IN NUMBER
) RETURN NUMBER IS
--
v_nextBid NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'clone_update_bid';
SELECT pon_bid_headers_s.nextval
INTO v_nextBid
FROM dual;
INSERT INTO pon_bid_headers (
bid_status,
bid_status_name,
bid_number,
shortlist_flag,
proxy_bid_flag,
auction_header_id,
bidders_bid_number,
bid_type,
contract_type,
trading_partner_contact_name,
trading_partner_contact_id,
trading_partner_name,
trading_partner_id,
bid_effective_date,
bid_expiration_date,
publish_date,
cancelled_date,
payment_terms_id,
freight_terms_code,
carrier_code,
fob_code,
note_to_auction_owner,
creation_date,
created_by,
last_update_date,
last_updated_by,
auction_creation_date,
attachments_flag,
disqualify_reason,
language_code,
award_status,
award_status_name,
phone,
fax,
email,
--operator_id,
bid_currency_code,
rate,
rate_type,
rate_date,
min_bid_change,
number_price_decimals,
doctype_id,
vendor_id,
vendor_site_id,
vendor_site_code,
agent_id,
create_sourcing_rules,
update_sourcing_rules,
release_method,
po_header_id,
po_error_code,
po_wf_creation_rnd,
surrog_bid_flag,
surrog_bid_created_tp_id,
SURROG_BID_CREATED_CONTACT_ID,
SURROG_BID_RECEIPT_DATE,
SURROG_BID_ONLINE_ENTRY_DATE,
ATTRIBUTE_LINE_NUMBER,
partial_response_flag,
color_sequence_id,
old_bid_number)
SELECT bid_status,
bid_status_name,
v_nextBid,
'Y',
'Y',
auction_header_id,
bidders_bid_number,
bid_type,
contract_type,
trading_partner_contact_name,
trading_partner_contact_id,
trading_partner_name,
trading_partner_id,
bid_effective_date,
bid_expiration_date,
p_new_publish_date,
cancelled_date,
payment_terms_id,
freight_terms_code,
carrier_code,
fob_code,
note_to_auction_owner,
sysdate,
created_by,
sysdate,
last_updated_by,
auction_creation_date,
attachments_flag,
disqualify_reason,
language_code,
award_status,
award_status_name,
phone,
fax,
email,
--operator_id,
bid_currency_code,
rate,
rate_type,
rate_date,
min_bid_change,
number_price_decimals,
doctype_id,
vendor_id,
vendor_site_id,
vendor_site_code,
agent_id,
create_sourcing_rules,
update_sourcing_rules,
release_method,
po_header_id,
po_error_code,
po_wf_creation_rnd,
surrog_bid_flag,
surrog_bid_created_tp_id,
surrog_bid_created_contact_id,
surrog_bid_receipt_date,
sysdate,
-1,
partial_response_flag,
color_sequence_id,
old_bid_number
FROM PON_BID_HEADERS
WHERE auction_header_id= p_auctionHeaderId
AND bid_number = p_bidNumber;
INSERT INTO pon_bid_item_prices (
auction_header_id,
auction_line_number,
bid_number,
line_number,
item_description,
category_id,
UOM,
unit_of_measure,
quantity,
price,
unit_price,
minimum_bid_price,
promised_date,
award_status,
award_date,
note_to_auction_owner,
last_update_date,
creation_date,
created_by,
last_updated_by,
auction_creation_date,
attachments_flag,
order_number,
award_status_name,
category_name,
language_code,
ship_to_location_id,
--operator_id,
publish_date,
bid_currency_price,
bid_currency_unit_price,
bid_currency_trans_price,
proxy_bid_limit_price,
bid_currency_limit_price,
proxy_bid_flag,
first_bid_price,
has_attributes_flag,
total_weighted_score,
rank,
trigger_bid_number,
group_amount,
HAS_BID_PAYMENTS_FLAG,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
BID_CURR_MAX_RETAINAGE_AMT,
has_bid_flag,
per_unit_price_component, --bug 7673590
fixed_amount_component)
SELECT auction_header_id,
auction_line_number,
v_nextBid,
line_number,
item_description,
category_id,
UOM,
unit_of_measure,
quantity,
price,
unit_price,
minimum_bid_price,
promised_date,
award_status,
award_date,
note_to_auction_owner,
sysdate,
sysdate,
created_by,
last_updated_by,
auction_creation_date,
attachments_flag,
order_number,
award_status_name,
category_name,
language_code,
ship_to_location_id,
--operator_id,
publish_date,
bid_currency_price,
bid_currency_unit_price,
bid_currency_trans_price,
proxy_bid_limit_price,
bid_currency_limit_price,
proxy_bid_flag,
first_bid_price,
has_attributes_flag,
total_weighted_score,
rank,
p_triggerBidNumber,
group_amount,
HAS_BID_PAYMENTS_FLAG,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
BID_CURR_MAX_RETAINAGE_AMT,
has_bid_flag,
per_unit_price_component,
fixed_amount_component
FROM pon_bid_item_prices
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber;
INSERT INTO pon_bid_attribute_values (
auction_header_id,
auction_line_number,
bid_number,
line_number,
attribute_name,
attr_level,
datatype,
value,
creation_date,
created_by,
last_update_date,
last_updated_by,
score,
weighted_score,
sequence_number,
attr_group_seq_number,
attr_disp_seq_number)
SELECT auction_header_id,
auction_line_number,
v_nextBid,
line_number,
attribute_name,
attr_level,
datatype,
value,
sysdate,
created_by,
sysdate,
last_updated_by,
score,
weighted_score,
sequence_number,
attr_group_seq_number,
attr_disp_seq_number
FROM pon_bid_attribute_values
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber;
INSERT INTO pon_bid_price_elements (
bid_number,
line_number,
price_element_type_id,
auction_header_id,
pricing_basis,
auction_currency_value,
bid_currency_value,
sequence_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
pf_type)
SELECT v_nextBid,
line_number,
price_element_type_id,
auction_header_id,
pricing_basis,
auction_currency_value,
bid_currency_value,
sequence_number,
sysdate,
created_by,
sysdate,
last_updated_by,
pf_type
FROM pon_bid_price_elements
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber;
UPDATE pon_bid_headers
SET bid_status = 'ARCHIVED',
bid_status_name = (SELECT meaning
FROM fnd_lookups
WHERE lookup_type='PON_BID_STATUS'
AND lookup_code = 'ARCHIVED')
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber;
UPDATE pon_auction_item_prices_all
SET best_bid_number = v_nextBid,
lowest_bid_number = decode(lowest_bid_number, null, null,
v_nextBid)
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_number = p_bidNumber;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_number = v_nextBid
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_bid_number = p_bidNumber;
select conterms_exist_flag into v_contermsExist
from pon_auction_headers_all
where auction_header_id = p_auctionHeaderId;
'clone_update_bid failed for auction_id=' || p_auctionHeaderId || ':' || v_nextBid || ':' || p_bidNumber || ', msg_data=' || l_msg_data);
END CLONE_UPDATE_BID;
SELECT nvl(auction_header_id_orig_round, auction_header_id)
INTO p_auctionHeaderIdOrigRound
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
PROCEDURE update_new_bid_line
( p_auctionHeaderId IN NUMBER,
p_bidNum IN NUMBER,
p_line IN NUMBER,
p_price IN NUMBER,
p_bid_curr_price IN NUMBER,
p_publish_date IN DATE)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'update_new_bid_line';
UPDATE pon_bid_item_prices
SET price = p_price,
bid_currency_trans_price = p_bid_curr_price,
bid_currency_price = p_bid_curr_price,
unit_price = p_price,
bid_currency_unit_price = p_bid_curr_price,
publish_date = p_publish_date,
proxy_bid_flag = 'Y'
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNum
AND line_number = p_line;
UPDATE pon_auction_item_prices_all
SET best_bid_price = p_price,
best_bid_currency_price = p_bid_curr_price,
lowest_bid_price = decode (lowest_bid_price, null,null,
p_price)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = p_line
AND best_bid_number = p_bidNum;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_price = p_price,
best_bid_bid_currency_price = p_bid_curr_price
WHERE auction_header_id = p_auctionHeaderId
AND line_number = p_line
AND best_bid_bid_number = p_bidNum;
END update_new_bid_line;
SELECT trading_partner_id,
trading_partner_contact_id,
vendor_site_id
INTO v_tpid,
v_tpcid,
v_vendorSiteId
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber;
SELECT max(bid_number)
INTO v_oldBidNumber
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_id = v_tpid
AND trading_partner_contact_id = v_tpcid
AND nvl(vendor_site_id,-1) = nvl(v_vendorSiteId, -1)
AND bid_number <> p_bidNumber
AND nvl(award_status, 'NONE') <> 'COMMITTED'
AND bid_status <> 'DRAFT'
AND bid_status <> 'ARCHIVED';
SELECT trading_partner_id,
trading_partner_contact_id,
nvl(evaluator_id, -1),
nvl(evaluation_flag, 'N'),
Nvl(surrog_bid_flag,'N') --Offer Enhancements
INTO v_tpid,
v_tpcid,
v_evaluator_id,
v_eval_flag,
v_surrog_bid_flag --Offer Enhancements
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidnumber;
SELECT max(bid_number)
INTO v_oldNonProxyBidNumber
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_id = v_tpid
AND trading_partner_contact_id = v_tpcid
AND nvl(vendor_site_id, -1) = nvl(p_vendorSiteId, -1)
AND ((proxy_bid_flag IS null) OR (proxy_bid_flag <> 'Y'))
AND bid_number <> p_bidnumber
AND nvl(award_status, 'NONE') <> 'COMMITTED'
AND bid_status <> 'DISQUALIFIED'
AND nvl(evaluator_id, -1) = v_evaluator_id
AND nvl(evaluation_flag, 'N') = v_eval_flag
/*Offer Enhancements : If current bid is surrogate, check only surrogate bids.
* Check only supplier offers for suppliers. */
AND Nvl(surrog_bid_flag,'N') = v_surrog_bid_flag
/* Bug#13590450 should not consider the technical stage draft bids once the techincal stage is completed, for 2 stage neg */
AND NOT (EXISTS (select 1 FROM pon_auction_headers_all ah WHERE ah.auction_header_id = p_auctionHeaderId
AND Nvl(ah.two_part_flag, 'N') = 'Y'
AND Nvl(ah.technical_evaluation_status, 'N') ='COMPLETED')
AND Nvl(surrog_bid_flag, 'N') = 'Y'
AND submit_stage IS NULL);
SELECT trading_partner_id,
trading_partner_contact_id
INTO v_tpid,
v_tpcid
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidnumber;
SELECT max(bid_number)
INTO v_activeBidNumber
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_id = v_tpid
AND trading_partner_contact_id = v_tpcid
AND ((proxy_bid_flag IS null) OR (proxy_bid_flag <> 'Y'))
AND nvl(award_status, 'NONE') <> 'COMMITTED';
SELECT bid_status
INTO v_recentBidStatus
FROM pon_bid_headers
WHERE bid_number = v_activeBidNumber;
PROCEDURE update_unchanged_rank( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_vendorSiteId IN NUMBER,
p_batchStart IN NUMBER,
p_batchEnd IN NUMBER,
p_discard_tech_nonshort IN VARCHAR2) IS
v_publish_date DATE;
l_api_name CONSTANT VARCHAR2(30) := 'update_unchanged_rank';
SELECT publish_date,
trading_partner_id,
trading_partner_contact_id
INTO v_publish_date,
v_tpid,
v_tpcid
FROM pon_bid_headers
WHERE bid_number = p_bidNumber;
SELECT max(bid_number)
INTO v_oldBid
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_id = v_tpid
AND trading_partner_contact_id = v_tpcid
AND bid_status <> 'DISQUALIFIED'
AND decode (p_discard_tech_nonshort, 'Y', technical_shortlist_flag, 'Y') = 'Y'
AND bid_number <> p_bidNumber
AND vendor_site_id = p_vendorSiteId
AND bid_status <> 'DRAFT';
UPDATE pon_bid_item_prices bidline
SET rank = (SELECT oldbidline.rank
FROM pon_bid_item_prices oldbidline
WHERE oldbidline.bid_number = v_oldBid
AND oldbidline.line_number= bidline.line_number)
WHERE bidline.bid_number = p_bidNumber
AND bidline.publish_date <> v_publish_date
AND bidline.line_number >= p_batchStart
AND bidline.line_number <= p_batchEnd;
END update_unchanged_rank;
select count(*)
into return_value
from PON_BID_PRICE_ELEMENTS b1,PON_BID_PRICE_ELEMENTS b2
where b1.bid_number = x_oldBidNumber
and b1.line_number = x_lineNumber
and b2.bid_number = x_newBidNumber
and b2.line_number = b1.line_number
and b2.SEQUENCE_NUMBER = b1.SEQUENCE_NUMBER
and b2.BID_CURRENCY_VALUE <> b1.BID_CURRENCY_VALUE;
select count(*)
into return_value
from PON_BID_ATTRIBUTE_VALUES b1,PON_BID_ATTRIBUTE_VALUES b2
where b1.bid_number = x_oldBidNumber
and b1.line_number = x_lineNumber
and b2.bid_number = x_newBidNumber
and b2.line_number = b1.line_number
and b2.SEQUENCE_NUMBER = b1.SEQUENCE_NUMBER
--and b2.SCORE <> b1.SCORE;
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = x_auction_header_id
AND trading_partner_id = x_trading_partner_id
AND trading_partner_contact_id = x_trading_partner_contact_id
AND bid_status IN ('ACTIVE', 'DRAFT', 'DISQUALIFIED')
AND Nvl(award_status, 'NONE') <> 'COMMITTED'
ORDER BY decode(bid_status, 'DRAFT', 1, 'ACTIVE', 2, 'DISQUALIFIED', 3) ASC;
SELECT bid_number
INTO x_bidNumber
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_id = p_tradingPartnerId
AND trading_partner_contact_id = p_tradingPartnerContactId
AND bid_status = 'ACTIVE';
UPDATE pon_bid_item_prices
SET proxy_bid_limit_price = price,
bid_currency_limit_price = bid_currency_price,
cancelled_limit_price = p_proxyBidLimitPrice,
publish_date = sysdate,
last_update_date = sysdate
WHERE bid_number = p_bidNumber
AND line_number = p_lineNumber;
UPDATE pon_auction_item_prices_all
SET best_bid_proxy_limit_price = best_bid_price
WHERE auction_header_id = p_auctionHeaderId
AND line_number = p_lineNumber
AND best_bid_number = p_bidNumber;
SELECT
pbip.line_number,
pbip.price,
pbip.proxy_bid_limit_price
FROM
pon_bid_item_prices pbip,
pon_auction_item_prices_all paip,
pon_auction_headers_all paha
WHERE
pbip.bid_number = p_bidNumber
AND paip.auction_header_id = p_auctionHeaderId
AND paha.auction_header_id = p_auctionHeaderId
AND paip.line_number = pbip.line_number
AND pbip.proxy_bid_limit_price IS NOT NULL
AND pbip.price <> pbip.proxy_bid_limit_price
AND nvl(paip.close_bidding_date, paha.close_bidding_date) >= decode (nvl (paha.is_paused, 'N'), 'Y', paha.last_pause_date, sysdate);
SELECT auction_header_id, bid_ranking, auction_status, DECODE ( NVL( is_paused, 'N'), 'Y', ( sysdate + ( close_bidding_date - last_pause_date ) ), close_bidding_date )
INTO l_auctionHeaderId, l_bidRanking, l_auctionStatus, l_closeBiddingDate
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId
FOR UPDATE OF CLOSE_BIDDING_DATE;
SELECT auction_header_id, bid_ranking, auction_status, DECODE ( NVL( is_paused, 'N'), 'Y', ( sysdate + ( close_bidding_date - last_pause_date ) ), close_bidding_date )
INTO l_auctionHeaderId, l_bidRanking, l_auctionStatus, l_closeBiddingDate
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId
FOR UPDATE OF CLOSE_BIDDING_DATE;
SELECT price, proxy_bid_limit_price
INTO l_price, l_proxyBidLimitPrice
FROM pon_bid_item_prices
WHERE bid_number = l_bidNumber
AND line_number = p_lineNumber;
SELECT paip.line_number
INTO v_negotiation_closed_line_num
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip,
pon_auction_headers_all paha
WHERE paip.auction_header_id = p_auctionHeaderId
AND pbip.auction_header_id = p_auctionHeaderId
AND paha.auction_header_id = p_auctionHeaderId
AND pbip.bid_number = p_bidNumber
AND paip.line_number = pbip.auction_line_number
AND pbip.is_changed_line_flag = 'Y'
AND p_publishDate > nvl (paip.close_bidding_date, paha.close_bidding_date)
AND rownum = 1;
SELECT bid_status
INTO v_bidStatus
FROM pon_bid_headers
WHERE bid_number = v_oldBidNumber;
SELECT bid_frequency_code
INTO v_bidFrequencyCode
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
SELECT TRADING_PARTNER_ID
INTO v_bidTradingPartnerId
FROM PON_BID_HEADERS
WHERE AUCTION_HEADER_ID = p_auctionHeaderId
AND BID_NUMBER = p_bidNumber;
SELECT COUNT(bid_number)
INTO v_sameCompanyBids
FROM PON_BID_HEADERS
WHERE AUCTION_HEADER_ID = p_auctionHeaderId
AND BID_STATUS = 'ACTIVE'
AND TRADING_PARTNER_ID = v_bidTradingPartnerId
AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1)
AND NVL(EVALUATION_FLAG, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
SELECT COUNT(bid_number)
INTO v_sameCompanyDrafts
FROM PON_BID_HEADERS
WHERE AUCTION_HEADER_ID = p_auctionHeaderId
AND (BID_STATUS = 'ACTIVE')
AND TRADING_PARTNER_ID = v_bidTradingPartnerId
AND NVL(VENDOR_SITE_ID, -1) = NVL(p_vendorSiteId, -1)
AND NVL(EVALUATION_FLAG, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
SELECT nvl(is_paused, 'N')
INTO v_is_paused
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
PROCEDURE update_disq_lines
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_rankIndicator IN pon_auction_headers_all.rank_indicator%TYPE,
p_bidRanking IN pon_auction_headers_all.bid_ranking%TYPE,
p_tpId IN pon_bid_headers.trading_partner_id%TYPE,
p_tpcId IN pon_bid_headers.trading_partner_contact_id%TYPE,
p_batchStart IN NUMBER,
p_batchEnd IN NUMBER,
p_ignore_tech_nonshortlist IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_disq_lines';
SELECT pbip.line_number, pbip.rank, pbh.technical_shortlist_flag
FROM pon_bid_item_prices pbip, pon_bid_headers pbh
WHERE pbh.auction_header_id = p_auctionHeaderId
AND pbip.auction_header_id = pbh.auction_header_id
AND pbh.bid_number = p_bidNumber
AND pbip.bid_number = pbh.bid_number
AND pbip.line_number >= p_batchStart
AND pbip.line_number <= p_batchEnd
ORDER BY pbip.line_number;
SELECT line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_number = p_bidNumber
AND line_number >= p_batchStart
AND line_number <= p_batchEnd
ORDER BY line_number;
SELECT line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_bid_number = p_bidNumber
AND line_number >= p_batchStart
AND line_number <= p_batchEnd
ORDER BY line_number;
print_log(l_api_name, p_bidNumber || ': begin update_disq_lines');
t_line_number.DELETE;
t_price.DELETE;
t_quantity.DELETE;
t_promised_date.DELETE;
t_bid_number.DELETE;
t_bid_currency_price.DELETE;
t_bid_currency_code.DELETE;
t_first_bid_price.DELETE;
t_proxy_bid_limit_price.DELETE;
print_log(l_api_name, p_bidNumber || ': update best bid lines');
SELECT best_bid_number
INTO v_bestBidNumber
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auctionHeaderId
AND line_number = best_bid_item_record.line_number;
|| ': need to update best_bid info');
SELECT line_number,
price,
quantity,
promised_date,
bid_number,
bid_currency_price,
bid_currency_code,
first_bid_price,
proxy_bid_limit_price
INTO t_line_number(v_counter),
t_price(v_counter),
t_quantity(v_counter),
t_promised_date(v_counter),
t_bid_number(v_counter),
t_bid_currency_price(v_counter),
t_bid_currency_code(v_counter),
t_first_bid_price(v_counter),
t_proxy_bid_limit_price(v_counter)
FROM (SELECT bidline.line_number,
bidline.price,
bidline.quantity,
bidline.promised_date,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code,
bidline.first_bid_price,
bidline.proxy_bid_limit_price
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE bidline.auction_header_id = p_auctionHeaderId
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND bidheader.bid_status = 'ACTIVE'
AND (
(p_ignore_tech_nonshortlist = 'Y' AND bidheader.technical_shortlist_flag = 'Y')
OR
(p_ignore_tech_nonshortlist = 'N')
)
AND bidheader.bid_number <> p_bidNumber
AND bidline.line_number = best_bid_item_record.line_number
ORDER BY decode(group_amount,null,bidline.price, group_amount), bidline.publish_date asc)
WHERE rownum = 1;
UPDATE pon_auction_item_prices_all
SET best_bid_price = t_price(x),
best_bid_quantity = t_quantity(x),
best_bid_promised_date = t_promised_date(x),
best_bid_number = t_bid_number(x),
best_bid_currency_price = t_bid_currency_price(x),
best_bid_currency_code = t_bid_currency_code(x),
best_bid_first_bid_price = t_first_bid_price(x),
best_bid_proxy_limit_price = t_proxy_bid_limit_price(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_line_number(x);
t_line_number.DELETE;
t_price.DELETE;
t_bid_number.DELETE;
t_bid_currency_price.DELETE;
t_bid_currency_code.DELETE;
t_score.DELETE;
SELECT best_bid_bid_number
INTO v_bestBidBidNumber
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auctionHeaderId
AND line_number = best_bid_bid_item_record.line_number;
SELECT line_number,
price,
total_weighted_score,
bid_number,
bid_currency_price,
bid_currency_code
INTO t_line_number(v_counter),
t_price(v_counter),
t_score(v_counter),
t_bid_number(v_counter),
t_bid_currency_price(v_counter),
t_bid_currency_code(v_counter)
FROM (SELECT bidline.line_number,
bidline.price,
bidline.total_weighted_score,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE bidline.auction_header_id = p_auctionHeaderId
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND bidheader.bid_status = 'ACTIVE'
AND (
(p_ignore_tech_nonshortlist = 'Y' AND bidheader.technical_shortlist_flag = 'Y')
OR
(p_ignore_tech_nonshortlist = 'N')
)
AND bidheader.bid_number <> p_bidNumber
AND bidline.line_number = best_bid_bid_item_record.line_number
ORDER BY decode(bidline.group_amount, null, bidline.total_weighted_score/bidline.price, -bidline.group_amount) desc,
bidline.publish_date asc)
WHERE rownum = 1;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_price = t_price(x),
best_bid_score = t_score(x),
best_bid_bid_number = t_bid_number(x),
best_bid_bid_currency_price = t_bid_currency_price(x),
best_bid_bid_currency_code = t_bid_currency_code(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_line_number(x);
print_log(l_api_name, p_bidNumber || ': for each line, update the numOfBids and rank');
t_line_number.DELETE;
t_number_of_bids.DELETE;
SELECT number_of_bids - (SELECT COUNT(bh.bid_number)
FROM pon_bid_headers bh,
pon_bid_item_prices bip
WHERE bh.auction_header_id = p_auctionHeaderId
AND bh.trading_partner_contact_id = p_tpcId
AND bh.trading_partner_id = p_tpId
AND (bh.bid_status = 'ARCHIVED' OR bh.bid_number = p_bidNumber)
AND bh.bid_number = bip.bid_number
AND bh.publish_date = bip.publish_date
AND bip.line_number = bid_item_record.line_number),
line_number
INTO t_number_of_bids(v_counter),
t_line_number(v_counter)
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auctionHeaderId
AND line_number = bid_item_record.line_number;
UPDATE pon_bid_item_prices bip
SET rank = rank - 1
WHERE auction_header_id = p_auctionHeaderId
AND line_number = bid_item_record.line_number
AND EXISTS (SELECT 1
FROM pon_bid_headers h
WHERE h.bid_number = bip.bid_number
AND h.bid_status = 'ACTIVE')
AND rank > bid_item_record.rank;
print_log(l_api_name, p_bidNumber || ': for each line, update the numOfBids and rank using structs');
UPDATE pon_auction_item_prices_all
SET number_of_bids = t_number_of_bids(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_line_number(x);
print_log(l_api_name, p_bidNumber || ': END update_disq_lines');
END update_disq_lines;
PROCEDURE update_disq_lines_batched
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_rankIndicator IN pon_auction_headers_all.rank_indicator%TYPE,
p_bidRanking IN pon_auction_headers_all.bid_ranking%TYPE,
p_tpId IN pon_bid_headers.trading_partner_id%TYPE,
p_tpcId IN pon_bid_headers.trading_partner_contact_id%TYPE,
p_maxLineNumber IN NUMBER,
p_batchSize IN NUMBER,
p_ignore_tech_nonshortlist IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'update_disq_lines_batched';
print_log(l_api_name, p_bidNumber || ': BEGIN update_disq_lines_batched');
update_disq_lines(p_auctionHeaderId, p_bidNumber, p_rankIndicator, p_bidRanking,
p_tpId, p_tpcId, v_batchStart, v_batchEnd, p_ignore_tech_nonshortlist);
print_log(l_api_name, p_bidNumber || ': END update_disq_lines_batched');
END update_disq_lines_batched;
PROCEDURE update_auction_info_disqualify
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_auction_info_disqualify';
print_log(l_api_name, 'BEGIN update_auction_info_disqualify for p_bidNumber=' || p_bidNumber);
SELECT rank_indicator,
bid_ranking,
max_internal_line_num,
nvl (two_part_flag, 'N'),
sealed_auction_status
INTO v_rankIndicator,
v_bidRanking,
v_maxLineNumber,
v_two_part_flag,
v_sealed_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId
FOR UPDATE OF CLOSE_BIDDING_DATE;
SELECT trading_partner_contact_id,
trading_partner_id
INTO v_tpcId,
v_tpId
FROM pon_bid_headers
WHERE bid_number = p_bidNumber;
SELECT number_of_bids
INTO v_numOfBids
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
UPDATE pon_auction_headers_all
SET last_update_date = sysdate,
number_of_bids = (number_of_bids -
(SELECT count(*)+1
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_contact_id = v_tpcId
AND bid_status = 'ARCHIVED'))
WHERE auction_header_id = p_auctionHeaderId;
SELECT number_of_bids
INTO v_numOfBids
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
print_log(l_api_name, p_bidNumber || ': calling update_disq_lines_batched');
update_disq_lines_batched(p_auctionHeaderId, p_bidNumber, v_rankIndicator, v_bidRanking,
v_tpId, v_tpcId, v_maxLineNumber, v_batchSize, v_ignore_tech_nonshortlist);
print_log(l_api_name, p_bidNumber || ': calling update_disq_lines');
update_disq_lines(p_auctionHeaderId, p_bidNumber, v_rankIndicator, v_bidRanking,
v_tpId, v_tpcId, 1, v_maxLineNumber, v_ignore_tech_nonshortlist);
UPDATE pon_bid_headers
SET bid_status = 'DISQUALIFIED'
WHERE auction_header_id = p_auctionHeaderId
AND trading_partner_contact_id = v_tpcId
AND bid_status = 'ARCHIVED';
END update_auction_info_disqualify;
procedure update_rank
(
p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_lineNumber IN NUMBER,
p_scoring_method IN VARCHAR2,
p_auction_type IN VARCHAR2,
p_oldRank IN NUMBER,
p_price IN NUMBER,
p_score IN NUMBER,
p_proxy IN VARCHAR2,
p_date IN DATE
)
IS
v_new_rank NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'update_rank';
select count(*) + 1
into v_new_rank
from pon_bid_item_prices ip, pon_bid_headers h
where ip.auction_header_id = h.auction_header_id
and h.auction_header_id = p_auctionHeaderId
and h.bid_number = ip.bid_number
and ip.line_number = p_lineNumber
and h.bid_status = 'ACTIVE'
and h.bid_number <> p_bidNumber
and decode(p_scoring_method, 'MULTI_ATTRIBUTE_SCORING',
is_better_proxy_price_by_score(p_price,
p_score,
p_proxy,
p_bidNumber,
p_date,
nvl(ip.group_amount,ip.price),
ip.total_weighted_score,
ip.trigger_bid_number,
ip.publish_date)
,
is_better_proxy_price(p_price,
p_bidNumber,
p_proxy,
p_date,
nvl(ip.group_amount,ip.price),
ip.trigger_bid_number,
ip.publish_date)
) = 'FALSE';
update pon_bid_item_prices ip
set rank = rank + 1
where auction_header_id = p_auctionHeaderId
and line_number = p_lineNumber
and exists (select 1
from pon_bid_headers h
where h.bid_number = ip.bid_number
and h.bid_status = 'ACTIVE')
and rank between v_new_rank and p_oldRank;
update pon_bid_item_prices ip
set rank = rank - 1
where auction_header_id = p_auctionHeaderId
and line_number = p_lineNumber
and exists (select 1
from pon_bid_headers h
where h.bid_number = ip.bid_number
and h.bid_status = 'ACTIVE')
and rank between p_oldRank and v_new_rank ;
update pon_bid_item_prices
set rank = v_new_rank
where auction_header_id = p_auctionHeaderId
and bid_number = p_bidNumber
and line_number = p_lineNumber;
SELECT pbs.unit_price
FROM pon_bid_shipments pbs
WHERE pbs.shipment_type = 'PRICE BREAK'
AND pbs.bid_number = p_bid_number
AND pbs.line_number = p_line_number
AND nvl(pbs.quantity, 0) <= nvl(p_quantity, 0)
AND ((p_ship_to_org = pbs.ship_to_organization_id) OR
(pbs.ship_to_organization_id is null))
AND ((p_ship_to_loc = pbs.ship_to_location_id) OR
(pbs.ship_to_location_id is null))
AND (p_need_by_date IS NULL OR
((trunc(p_need_by_date) >= pbs.effective_start_date OR
pbs.effective_start_date is null)
AND
(trunc(p_need_by_date) <= pbs.effective_end_date OR
pbs.effective_end_date is null)))
ORDER BY pbs.ship_to_organization_id ASC, pbs.ship_to_location_id ASC,
NVL(pbs.quantity,-1) DESC,
pbs.price ASC;
SELECT pbs.price
FROM pon_bid_shipments pbs
WHERE pbs.shipment_type = 'PRICE BREAK'
AND pbs.bid_number = p_bid_number
AND pbs.line_number = p_line_number
AND nvl(pbs.quantity, 0) <= nvl(p_quantity, 0)
AND ((p_ship_to_org = pbs.ship_to_organization_id) OR
(pbs.ship_to_organization_id is null))
AND ((p_ship_to_loc = pbs.ship_to_location_id) OR
(pbs.ship_to_location_id is null))
AND (p_need_by_date IS NULL OR
((trunc(p_need_by_date) >= pbs.effective_start_date OR
pbs.effective_start_date is null)
AND
(trunc(p_need_by_date) <= pbs.effective_end_date OR
pbs.effective_end_date is null)))
ORDER BY pbs.ship_to_organization_id ASC, pbs.ship_to_location_id ASC,
NVL(pbs.quantity,-1) DESC,
pbs.price ASC;
SELECT
price_element_type_id,
pricing_basis,
bid_currency_value
FROM pon_bid_price_elements bid_pfs
WHERE
bid_pfs.bid_number = p_bid_number
AND bid_pfs.line_number = p_line_number
AND bid_pfs.price_element_type_id <> -10
AND bid_pfs.pf_type = 'SUPPLIER';
SELECT
auctions.contract_type,
auctions.full_quantity_bid_code,
items.order_type_lookup_code,
items.quantity,
NVL(pf_formula.unit_price, 0) pf_unit_price,
NVL(pf_formula.fixed_amount, 0) pf_fixed_amount,
NVL(pf_formula.percentage, 1) pf_percentage
INTO
l_contract_type,
l_full_quantity_bid_code,
l_order_type_lookup_code,
l_auction_quantity,
l_auc_pf_unit_price_formula,
l_auc_pf_fixed_amount_formula,
l_auc_pf_percentage_formula
FROM
pon_auction_headers_all auctions,
pon_auction_item_prices_all items,
pon_pf_supplier_formula pf_formula
WHERE
auctions.auction_header_id = p_auction_header_id
AND items.line_number = p_line_number
AND auctions.auction_header_id = items.auction_header_id
AND pf_formula.auction_header_id(+) = items.auction_header_id
AND pf_formula.line_number(+) = items.line_number
AND pf_formula.trading_partner_id(+) = p_trading_partner_id
AND pf_formula.vendor_site_id(+) = p_vendor_site_id;
select user_id into x_user_id from fnd_user where person_party_id = p_person_party_id;
UPDATE pon_bid_headers
SET
bid_status = 'DRAFT',
last_update_date = SYSDATE,
last_updated_by = p_login_user_id
WHERE
bid_number = (
SELECT bid_number
FROM
(SELECT
bh.bid_number,
bh.bid_status,
decode(bh.bid_status,
'ARCHIVED_DRAFT', 3,
'RESUBMISSION' , 2,
'DISQUALIFIED', 1) bid_status_order,
nvl(ah.amendment_number, 0) amendment_number,
bh.publish_date
FROM
pon_bid_headers bh,
pon_auction_headers_all ah
WHERE
bh.auction_header_id = ah.auction_header_id
AND ah.auction_header_id_orig_amend = p_auction_header_id_orig_amend
AND bh.trading_partner_id = p_trading_partner_id
AND bh.trading_partner_contact_id = p_trading_partner_contact_id
AND nvl(bh.vendor_site_id, -1) = nvl(p_vendor_site_id, -1)
AND bh.bid_status in ('ARCHIVED_DRAFT', 'RESUBMISSION', 'DISQUALIFIED')
ORDER BY amendment_number DESC, bid_status_order DESC, bh.publish_date DESC
)
WHERE ROWNUM = 1
)
AND bid_status = 'ARCHIVED_DRAFT';
SELECT Count(*)
INTO l_unsol_line_count
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = p_bid_number
AND pbip.auction_line_number = -1;
UPDATE pon_bid_headers pbh
SET buyer_bid_total = (SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(pbip.quantity, paip.quantity)) *
pbip.price)
-- hack to set bid total to null if at least one line has a quantity of null
+ decode(min(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(paip.quantity, -9999))), -9999, NULL, 0) bid_total
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
WHERE pbip.auction_header_id = pbh.auction_header_id AND
pbip.bid_number = pbh.bid_number AND
pbip.auction_header_id = paip.auction_header_id AND
pbip.line_number = paip.line_number AND
paip.group_type in ('LOT', 'LINE', 'GROUP_LINE') AND
Nvl(paip.clm_info_flag,'N') = 'N') -- bug 9282390
WHERE pbh.auction_header_id = p_auction_header_id AND
pbh.bid_number = p_bid_number;
SELECT sum(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(pbip.quantity, paip.quantity)) * pbip.price)
+ decode(min(decode(paip.order_type_lookup_code, 'FIXED PRICE', 1, nvl(paip.quantity, -9999))), -9999, NULL, 0) bid_total
INTO l_neg_line_total
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
WHERE pbip.auction_header_id = p_auction_header_id AND
pbip.bid_number = p_bid_number AND
pbip.auction_header_id = paip.auction_header_id AND
pbip.line_number = paip.line_number AND
paip.group_type in ('LOT', 'LINE', 'GROUP_LINE') AND
Nvl(paip.clm_info_flag,'N') = 'N';
SELECT Count(*)
INTO l_offer_sol_line_total
FROM PON_BID_ITEM_PRICES
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND auction_line_number <> -1;
SELECT sum(pbip.quantity * pbip.price)
+ decode(Min(nvl(pbip.quantity, -9999)), -9999, NULL, 0) bid_total
INTO l_unsol_line_total
FROM pon_bid_item_prices pbip
WHERE pbip.auction_header_id = p_auction_header_id AND
pbip.bid_number = p_bid_number AND
pbip.auction_line_number = -1 AND
Nvl(pbip.clm_info_flag,'N') = 'N';
UPDATE pon_bid_headers pbh
SET buyer_bid_total = (l_neg_line_total + l_unsol_line_total)
WHERE pbh.auction_header_id = p_auction_header_id AND
pbh.bid_number = p_bid_number;
update
pon_auction_item_prices_all
SET
number_of_extensions = nvl(number_of_extensions,0) + 1,
close_bidding_date = close_bidding_date + p_extensionInterval
WHERE
auction_header_id = p_auctionHeaderId
AND close_bidding_date >= p_bidPublishDate
AND line_number >= p_batchStart
AND line_number <= p_batchEnd;
UPDATE pon_auction_item_prices_all
SET number_of_extensions = nvl(number_of_extensions, 0) + 1,
close_bidding_date = decode(p_autoExtendTypeFlag, 'FROM_AUCTION_CLOSE_DATE',
nvl(close_bidding_date, p_hdrCloseBiddingDate) + p_hdrExtensionTime,
p_bidPublishDate + p_hdrExtensionTime)
WHERE auction_header_id = p_auctionHeaderId
AND line_number IN
(SELECT al.line_number
FROM pon_bid_headers bh,
pon_bid_item_prices bl,
pon_auction_item_prices_all al
WHERE bh.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
AND bl.bid_number = bh.bid_number
AND al.auction_header_id = bh.auction_header_id
AND bl.line_number = al.line_number
-- consider only lines changed during this publish
AND bl.publish_date = bh.publish_date
-- consider only lines with bids placed within the AutoExtend period
AND bl.publish_date > (nvl(al.close_bidding_date, p_hdrCloseBiddingDate) - p_hdrExtensionTime)
-- consider only lines which have extensions left
AND nvl(al.number_of_extensions, p_hdrNumberOfExtensions) < p_autoExtendNumber
-- We extend when any bid will trigger AutoExtend
-- or we extend when the top bid is placed
-- or, if ranking is numbered, we extend when the bid placed is within the top rank specified
AND (p_autoExtendMinTriggerRank = 10000
OR decode(p_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
al.best_bid_bid_number, al.best_bid_number) = bl.bid_number
OR (p_rankIndicator = 'NUMBERING' AND bl.rank <= p_autoExtendMinTriggerRank))
-- consider only lines in the current batch
AND al.line_number >= p_batchStart
AND al.line_number <= p_batchEnd);
UPDATE pon_auction_item_prices_all al
SET (number_of_extensions,
close_bidding_date) =
(SELECT max(number_of_extensions),
max(close_bidding_date)
FROM pon_auction_item_prices_all al2
WHERE al2.auction_header_id = al.auction_header_id
AND nvl(al2.parent_line_number, al2.line_number) =
nvl(al.parent_line_number, al.line_number))
WHERE auction_header_id = p_auctionHeaderId
AND group_type in ('LOT', 'LOT_LINE', 'GROUP', 'GROUP_LINE')
AND line_number >= p_batchStart
AND line_number <= p_batchEnd;
select
pah.auction_type,
pah.bid_ranking,
pah.rank_indicator,
nvl(pah.auto_extend_all_lines_flag,'Y'),
pah.auto_extend_type_flag,
pah.auto_extend_number,
pah.auto_extend_duration,
pah.close_bidding_date,
nvl(pah.number_of_extensions,0),
pah.auto_extend_min_trigger_rank,
pah.staggered_closing_interval,
bh.publish_date
into
v_auctionType,
v_bidRanking,
v_rankIndicator,
v_autoExtendAllLinesFlag,
v_autoExtendTypeFlag,
v_autoExtendNumber,
v_autoExtendDuration,
v_hdrCloseBiddingDate,
v_hdrNumberOfExtensions,
v_autoExtendMinTriggerRank,
v_staggeredClosingInterval,
v_bidPublishDate
from
pon_bid_headers bh,
pon_auction_headers_all pah
where bh.bid_number = p_bidNumber
AND pah.auction_header_id = bh.auction_header_id;
select
nvl(a.close_bidding_date, v_hdrCloseBiddingDate),
b.publish_date
into
v_triggerLineCloseBiddingDate,
v_bidPublishDate
from
pon_auction_item_prices_all a,
pon_bid_item_prices b
where
-- We need to consider every bid generated during this publish - the current bid + all proxy bids
b.bid_number IN (SELECT * FROM TABLE(CAST (g_bidsGenerated AS fnd_table_of_number)))
and a.auction_header_id = p_auctionHeaderId
and a.line_number = b.line_number
-- We extend when any bid will trigger AutoExtend
-- or we extend when the top bid is placed
-- or, if ranking is numbered, we extend when the bid placed is within the top rank specified
and (v_autoExtendMinTriggerRank = 10000
OR decode(v_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
a.best_bid_bid_number, a.best_bid_number) = b.bid_number
OR (v_rankIndicator = 'NUMBERING' AND b.rank <= v_autoExtendMinTriggerRank))
and nvl(a.number_of_extensions, v_hdrNumberOfExtensions) < v_autoExtendNumber
and b.publish_date > (nvl(a.close_bidding_date, v_hdrCloseBiddingDate) - v_hdrExtensionTime)
and v_bidPublishDate <= nvl (a.close_bidding_date, v_hdrCloseBiddingDate)
and rownum = 1;
PROCEDURE update_proxy_bid
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_oldBidNumber IN NUMBER,
p_isSurrogateBid IN VARCHAR2,
p_isAuctionClosed IN VARCHAR2,
x_isPriceChanged OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_proxy_bid';
select biditem.line_number,
biditem.price,
biditem.proxy_bid_limit_price,
biditem.first_bid_price,
item.best_bid_number,
item.best_bid_price,
item.best_bid_proxy_limit_price,
item.best_bid_first_bid_price,
NVL(item.number_of_bids, 0) as number_of_bids,
bestbid.trading_partner_id as best_bid_tp_id,
bestbid.trading_partner_contact_id as best_bid_tpc_id,
NVL(bestbid.min_bid_change,0) as best_bid_min_change,
NVL(bestbid.rate,1.0) as best_bid_rate,
NVL(bestbid.number_price_decimals,10) as best_bid_decimals
from pon_bid_headers bestbid,
pon_bid_item_prices biditem,
pon_auction_item_prices_all item
where bestbid.bid_number = item.best_bid_number
and item.auction_header_id = biditem.auction_header_id
and item.line_number = biditem.line_number
and biditem.auction_header_id= p_auctionHeaderId
and biditem.bid_number = p_bidNumber
and biditem.publish_date = v_publish_date
and (item.best_bid_proxy_limit_price is not null
or biditem.proxy_bid_limit_price is not null);
print_log(l_api_name, p_bidNumber || ': beginning update_proxy_bid');
SELECT auc.auction_type,
auc.trading_partner_id,
auc.number_price_decimals,
auc.min_bid_change_type,
NVL(bid.min_bid_change,0),
bid.trading_partner_id,
bid.trading_partner_contact_id,
NVL(bid.rate, 1.0),
bid.publish_date,
NVL(bid.number_price_decimals,10),
NVL(bid.bid_revision_number,1)
INTO v_auction_type,
v_trading_partner_id,
v_auction_decimals,
v_bid_change_type,
v_bid_min_change,
v_tpid,
v_tpcid,
v_rate,
v_publish_date,
v_bid_decimals,
v_revision_number
FROM pon_bid_headers bid,pon_auction_headers_all auc
WHERE auc.auction_header_id = bid.auction_header_id
AND bid.bid_number = p_bidNumber;
print_log(l_api_name, p_bidNumber || ': update_proxy point 1');
UPDATE pon_bid_item_prices newbid
SET (price,
bid_currency_price ,
proxy_bid_limit_price ,
bid_currency_limit_price ,
publish_date) =
(SELECT price,
bid_currency_price,
proxy_bid_limit_price,
bid_currency_limit_price,
publish_date
FROM pon_bid_item_prices prevbid
WHERE prevbid.auction_header_id = newbid.auction_header_id
AND prevbid.bid_number = p_oldBidNumber
AND prevbid.line_number = newbid.line_number)
WHERE newbid.auction_header_id = p_auctionHeaderId
AND newbid.bid_number = p_bidNumber
AND newbid.proxy_bid_limit_price IS NOT NULL
AND newbid.publish_date <> v_publish_date;
print_log(l_api_name, p_bidNumber || ': update_proxy point 2');
t_itm_line_number.DELETE;
t_itm_number_of_bids.DELETE;
t_itm_best_bid_proxy_limit_prc.DELETE;
print_log(l_api_name, p_bidNumber || ': update_proxy before main loop (3)');
UPDATE pon_bid_item_prices
SET price = v_new_bid_price,
unit_price = price,
bid_currency_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
round(v_new_bid_price * v_rate,v_bid_decimals)),
bid_currency_unit_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
round(v_new_bid_price * v_rate,v_bid_decimals)),
bid_currency_trans_price = decode(v_new_bid_price,v_proxy_bid_limit_price,bid_currency_limit_price,
round(v_new_bid_price * v_rate,v_bid_decimals)),
proxy_bid_flag = 'Y'
WHERE auction_header_id = p_auctionHeaderId
AND bid_number = p_bidNumber
AND line_number = bidlist.line_number;
UPDATE pon_auction_item_prices_all
SET number_of_bids = t_itm_number_of_bids(x),
best_bid_proxy_limit_price = t_itm_best_bid_proxy_limit_prc(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_itm_line_number(x);
print_log(l_api_name, p_bidNumber || ': (proxy) after mass update');
update_group_amount(p_bidNumber);
print_log(l_api_name, 'calling subroutine clone_update_bid');
v_newBidNum := clone_update_bid(p_auctionHeaderId, v_oldBidNum, v_proxy_publish_date,p_bidNumber);
print_log(l_api_name, 'calling subroutine update_new_bid_line');
update_new_bid_line(p_auctionHeaderId,
v_newBidNum,
v_proxyBidList(v_idx).line_number,
v_proxyBidList(v_idx).bid_price,
v_proxyBidList(v_idx).bid_currency_price,
v_proxy_publish_date);
update_group_amount(v_newBidNum);
print_log(l_api_name, p_bidNumber || ': ending update_proxy_bid');
END UPDATE_PROXY_BID;
PROCEDURE update_proxy_bid_auto
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_oldBidNumber IN NUMBER,
p_isSurrogateBid IN VARCHAR2,
p_isAuctionClosed IN VARCHAR2,
x_isPriceChanged OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_proxy_bid_auto';
print_log(l_api_name, p_bidNumber || ': beginning update_proxy_bid_auto');
update_proxy_bid(p_auctionHeaderId, p_bidNumber, p_oldBidNumber,
p_isSurrogateBid, p_isAuctionClosed, x_isPriceChanged);
print_log(l_api_name, p_bidNumber || ': ending update_proxy_bid_auto');
END update_proxy_bid_auto;
PROCEDURE update_and_rerank_group_lines
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_rankIndicator VARCHAR2,
p_bidRanking VARCHAR2,
p_discard_tech_nonshort VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_and_rerank_group_lines';
SELECT groupline.line_number
FROM pon_bid_item_prices groupline,
pon_auction_item_prices_all auctionline
WHERE groupline.bid_number = p_bidNumber
AND groupline.publish_date = v_publish_date
AND auctionline.auction_header_id = groupline.auction_header_id
AND auctionline.line_number = groupline.line_number
AND auctionline.group_type = 'GROUP'
AND groupline.group_amount IS NOT NULL;
SELECT groupline.bid_number
FROM pon_bid_item_prices groupline,
pon_bid_headers groupheader
WHERE groupline.auction_header_id = p_auctionHeaderId
AND groupline.bid_number = groupheader.bid_number
AND (groupheader.bid_status = 'ACTIVE'
OR groupheader.bid_number = p_bidNumber)
AND groupline.line_number = v_line_number
AND (
(
groupheader.bid_status = 'ACTIVE'
AND
decode (p_discard_tech_nonshort, 'Y', groupheader.technical_shortlist_flag, 'Y') = 'Y'
)
OR
groupheader.bid_number = p_bidNumber
)
ORDER BY groupline.group_amount, groupline.publish_date ASC;
print_log(l_api_name, p_bidNumber || ': BEGIN update_and_rerank_group_lines');
t_itm_line_number.DELETE;
t_itm_bid_number.DELETE;
t_itm_rank.DELETE;
UPDATE pon_auction_item_prices_all
SET best_bid_number = t_itm_bid_number(x),
best_bid_bid_number = decode(p_bidRanking,
'MULTI_ATTRIBUTE_SCORING',
t_itm_bid_number(x),
null)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_itm_line_number(x)
AND t_itm_rank(x) = 1;
UPDATE pon_bid_item_prices
SET rank = t_itm_rank(x)
WHERE bid_number = t_itm_bid_number(x)
AND line_number = t_itm_line_number(x);
print_log(l_api_name, p_bidNumber || ': END update_and_rerank_group_lines');
END update_and_rerank_group_lines;
PROCEDURE update_worsened_lines
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_bidRanking VARCHAR2,
p_discard_tech_nonshort VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_worsened_lines';
SELECT bidline.line_number
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader,
pon_bid_headers bestbidheader,
pon_auction_item_prices_all auctionline
WHERE auctionline.auction_header_id = bidline.auction_header_id
AND bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number
AND bidheader.bid_number = bidline.bid_number
AND bestbidheader.bid_number = auctionline.best_bid_number
AND bestbidheader.auction_header_id = bidheader.auction_header_id
AND bidheader.trading_partner_id = bestbidheader.trading_partner_id
AND bidheader.trading_partner_contact_id = bestbidheader.trading_partner_contact_id
AND bidline.publish_date = v_publish_date
AND bidline.price >= auctionline.best_bid_price;
SELECT bidline.line_number
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader,
pon_bid_headers bestbidheader,
pon_auction_item_prices_all auctionline
WHERE auctionline.auction_header_id = bidline.auction_header_id
AND bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number
AND bidheader.bid_number = bidline.bid_number
AND bestbidheader.bid_number = auctionline.best_bid_bid_number
AND bestbidheader.auction_header_id = bidheader.auction_header_id
AND bidheader.trading_partner_id = bestbidheader.trading_partner_id
AND bidheader.trading_partner_contact_id = bestbidheader.trading_partner_contact_id
AND bidline.publish_date = v_publish_date
AND (bidline.total_weighted_score / bidline.price) <=
(auctionline.best_bid_score / auctionline.best_bid_bid_price);
print_log(l_api_name, p_bidNumber || ' - BEGIN update worsened lines');
t_line_number.DELETE;
t_price.DELETE;
t_quantity.DELETE;
t_promised_date.DELETE;
t_bid_number.DELETE;
t_bid_currency_price.DELETE;
t_bid_currency_code.DELETE;
t_first_bid_price.DELETE;
t_proxy_bid_limit_price.DELETE;
t_score.DELETE;
SELECT line_number,
price,
quantity,
promised_date,
bid_number,
bid_currency_price,
bid_currency_code,
first_bid_price,
proxy_bid_limit_price
INTO t_line_number(v_counter),
t_price(v_counter),
t_quantity(v_counter),
t_promised_date(v_counter),
t_bid_number(v_counter),
t_bid_currency_price(v_counter),
t_bid_currency_code(v_counter),
t_first_bid_price(v_counter),
t_proxy_bid_limit_price(v_counter)
FROM (SELECT bidline.line_number,
bidline.price,
bidline.quantity,
bidline.promised_date,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code,
bidline.first_bid_price,
bidline.proxy_bid_limit_price
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE bidline.auction_header_id = p_auctionHeaderId
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND (
(
bidheader.bid_status = 'ACTIVE'
AND
decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
)
OR
bidheader.bid_number = p_bidNumber
)
AND bidline.line_number = rerank_line.line_number
ORDER BY bidline.price, bidline.publish_date asc)
WHERE rownum = 1;
UPDATE pon_auction_item_prices_all
SET best_bid_price = t_price(x),
best_bid_quantity = t_quantity(x),
best_bid_promised_date = t_promised_date(x),
best_bid_number = t_bid_number(x),
best_bid_currency_price = t_bid_currency_price(x),
best_bid_currency_code = t_bid_currency_code(x),
best_bid_first_bid_price = t_first_bid_price(x),
best_bid_proxy_limit_price = t_proxy_bid_limit_price(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_line_number(x);
t_line_number.DELETE;
t_price.DELETE;
t_score.DELETE;
t_bid_number.DELETE;
t_bid_currency_price.DELETE;
t_bid_currency_code.DELETE;
SELECT line_number,
price,
total_weighted_score,
bid_number,
bid_currency_price,
bid_currency_code
INTO t_line_number(v_counter),
t_price(v_counter),
t_score(v_counter),
t_bid_number(v_counter),
t_bid_currency_price(v_counter),
t_bid_currency_code(v_counter)
FROM (SELECT bidline.line_number,
bidline.price,
bidline.total_weighted_score,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE bidline.auction_header_id = p_auctionHeaderId
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND (
(
bidheader.bid_status = 'ACTIVE'
AND
decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
)
OR
bidheader.bid_number = p_bidNumber
)
AND bidline.line_number = mas_rerank_line.line_number
ORDER BY bidline.total_weighted_score/bidline.price desc,
bidline.publish_date asc)
WHERE rownum = 1;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_price = t_price(x),
best_bid_score = t_score(x),
best_bid_bid_number = t_bid_number(x),
best_bid_bid_currency_price = t_bid_currency_price(x),
best_bid_bid_currency_code = t_bid_currency_code(x)
WHERE auction_header_id = p_auctionHeaderId
AND line_number = t_line_number(x);
print_log(l_api_name, p_bidNumber || ' - END update_worsened_lines');
END update_worsened_lines;
PROCEDURE update_new_best_lines
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_bidRanking VARCHAR2,
p_bidCurrencyCode VARCHAR2,
p_batchStart NUMBER,
p_batchEnd NUMBER)
IS
BEGIN
UPDATE pon_auction_item_prices_all auctionline
SET (auctionline.best_bid_number,
auctionline.best_bid_price,
auctionline.best_bid_quantity,
auctionline.best_bid_promised_date,
auctionline.best_bid_proxy_limit_price,
auctionline.best_bid_currency_price,
auctionline.best_bid_currency_code,
auctionline.best_bid_first_bid_price) =
(SELECT bidline.bid_number,
bidline.price,
bidline.quantity,
bidline.promised_date,
bidline.proxy_bid_limit_price,
bidline.bid_currency_price,
p_bidCurrencyCode,
bidline.first_bid_price
FROM pon_bid_item_prices bidline
WHERE bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number)
WHERE auctionline.auction_header_id = p_auctionHeaderId
AND auctionline.group_type <> 'GROUP'
AND EXISTS (SELECT 'x'
FROM pon_bid_item_prices bidline
WHERE bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number
AND bidline.publish_date = p_publishDate)
AND (auctionline.best_bid_number IS NULL
OR
NVL((SELECT is_better_proxy_price(bidline.price,
bidline.bid_number,
bidline.proxy_bid_flag,
bidline.publish_date,
bestbidline.price,
bestbidline.trigger_bid_number,
bestbidline.publish_date)
FROM pon_bid_item_prices bidline,
pon_bid_item_prices bestbidline
WHERE bidline.bid_number = p_bidNumber
AND bestbidline.bid_number = auctionline.best_bid_number
AND bidline.line_number = auctionline.line_number
AND bestbidline.line_number = auctionline.line_number),
'FALSE') = 'TRUE')
AND auctionline.line_number >= p_batchStart
AND auctionline.line_number <= p_batchEnd;
UPDATE pon_auction_item_prices_all auctionline
SET (auctionline.best_bid_bid_number,
auctionline.best_bid_bid_price,
auctionline.best_bid_score,
auctionline.best_bid_bid_currency_price,
auctionline.best_bid_bid_currency_code) =
(SELECT bidline.bid_number,
bidline.price,
bidline.total_weighted_score,
bidline.bid_currency_price,
p_bidCurrencyCode
FROM pon_bid_item_prices bidline
WHERE bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number)
WHERE auctionline.auction_header_id = p_auctionHeaderId
AND auctionline.group_type <> 'GROUP'
AND EXISTS (SELECT 'x'
FROM pon_bid_item_prices bidline
WHERE bidline.bid_number = p_bidNumber
AND bidline.line_number = auctionline.line_number
AND bidline.publish_date = p_publishDate)
AND (auctionline.best_bid_bid_number IS NULL
OR
NVL((SELECT is_better_proxy_price_by_score(bidline.price,
bidline.total_weighted_score,
bidline.proxy_bid_flag,
bidline.bid_number,
bidline.publish_date,
bestbidline.price,
bestbidline.total_weighted_score,
bestbidline.trigger_bid_number,
bestbidline.publish_date)
FROM pon_bid_item_prices bidline,
pon_bid_item_prices bestbidline
WHERE bidline.bid_number = p_bidNumber
AND bestbidline.bid_number = auctionline.best_bid_bid_number
AND bidline.line_number = auctionline.line_number
AND bestbidline.line_number = auctionline.line_number),
'FALSE') = 'TRUE')
AND auctionline.line_number >= p_batchStart
AND auctionline.line_number <= p_batchEnd;
END update_new_best_lines;
SELECT bidline.line_number,
bidline.price,
bidline.total_weighted_score,
bidline.proxy_bid_flag,
NVL(oldBidline.rank, DEAD_LAST) as old_rank
FROM pon_bid_item_prices bidline,
pon_auction_item_prices_all auctionline,
pon_bid_item_prices oldBidline
WHERE bidline.bid_number = p_bidNumber
AND bidline.publish_date = v_publish_date
AND auctionline.auction_header_id = bidline.auction_header_id
AND auctionline.line_number = bidline.line_number
AND auctionline.group_type <> 'GROUP'
AND auctionline.line_number >= p_batchStart
AND auctionline.line_number <= p_batchEnd
AND bidline.line_number = oldBidline.line_number(+)
and oldBidline.bid_number(+) = NVL(P_oldBidNumber, -1);
SELECT bid_number,
rank
FROM pon_bid_item_prices bidline
WHERE auction_header_id = p_auctionHeaderId
AND bid_number <> p_bidNumber
AND line_number = v_line_number
AND EXISTS (SELECT 'x'
FROM pon_bid_headers bidheader
WHERE bidheader.bid_number = bidline.bid_number
AND bidheader.bid_status = 'ACTIVE'
AND decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y')
AND rank BETWEEN DECODE(sign(v_new_rank - v_old_rank), 1, v_old_rank, v_new_rank)
AND DECODE(sign(v_new_rank - v_old_rank), 1, v_new_rank, v_old_rank);
t_itm_line_number.DELETE;
t_itm_bid_number.DELETE;
t_itm_rank.DELETE;
SELECT count(*) + 1
INTO v_newRank
FROM pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE bidline.auction_header_id = bidheader.auction_header_id
AND bidheader.auction_header_id = p_auctionHeaderId
AND bidheader.bid_number = bidline.bid_number
AND bidline.line_number = rerank_line.line_number
AND bidheader.bid_status = 'ACTIVE'
AND decode (p_discard_tech_nonshort, 'Y', bidheader.technical_shortlist_flag, 'Y') = 'Y'
AND bidheader.bid_number <> p_bidNumber
AND decode(p_bidRanking, 'MULTI_ATTRIBUTE_SCORING',
is_better_proxy_price_by_score(v_price,
v_score,
v_proxyFlag,
p_bidNumber,
p_publishDate,
nvl(bidline.group_amount, bidline.price),
bidline.total_weighted_score,
bidline.trigger_bid_number,
bidline.publish_date),
is_better_proxy_price(v_price,
p_bidNumber,
v_proxyFlag,
p_publishDate,
nvl(bidline.group_amount, bidline.price),
bidline.trigger_bid_number,
bidline.publish_date)) = 'FALSE';
UPDATE pon_bid_item_prices
SET rank = t_itm_rank(x)
WHERE bid_number = t_itm_bid_number(x)
AND line_number = t_itm_line_number(x);
PROCEDURE update_old_best_bid_number
(p_auctionHeaderId IN NUMBER,
p_oldBidNumber IN NUMBER,
p_bidNumber IN NUMBER,
p_batchStart IN NUMBER,
p_batchEnd IN NUMBER)
IS
BEGIN
UPDATE pon_auction_item_prices_all
SET best_bid_number = p_bidNumber
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_number = p_oldBidNumber
AND line_number >= p_batchStart
AND line_number <= p_batchEnd;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_number = p_bidNumber
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_bid_number = p_oldBidNumber
AND line_number >= p_batchStart
AND line_number <= p_batchEnd;
END update_old_best_bid_number;
PROCEDURE update_non_batched_part
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_rankIndicator VARCHAR2,
p_bidRanking VARCHAR2,
p_discard_tech_nonshort VARCHAR2)
IS
BEGIN
-- UPDATE GROUPS
-- applies to lines: GROUP lines that were affected by this bid
-- action performed: (1) re-set best_bid* attributes in pon_auction_item_prices_all
-- (2) if ranking is NUMBERING, rerank all bids for each GROUP
update_and_rerank_group_lines(p_auctionHeaderId, p_bidNumber, p_publishDate,
p_rankIndicator, p_bidRanking, p_discard_tech_nonshort);
update_worsened_lines(p_auctionHeaderId, p_bidNumber, p_publishDate, p_bidRanking, p_discard_tech_nonshort);
END update_non_batched_part;
PROCEDURE update_non_batched_part_auto
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_rankIndicator VARCHAR2,
p_bidRanking VARCHAR2,
p_discard_tech_nonshort VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
update_non_batched_part(p_auctionHeaderId, p_bidNumber, p_publishDate,
p_rankIndicator, p_bidRanking, p_discard_tech_nonshort);
END update_non_batched_part_auto;
PROCEDURE update_batched_part
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_oldBidNumber NUMBER,
p_vendorSiteId NUMBER,
p_bidCurrencyCode VARCHAR2,
p_bidRanking VARCHAR2,
p_rankIndicator VARCHAR2,
p_batchStart NUMBER,
p_batchEnd NUMBER,
p_discard_tech_nonshort VARCHAR2)
IS
BEGIN
-- 1) UPDATE NEW BEST LINES
-- applies to lines: non-GROUP lines where this supplier user has submitted a bid
-- that is better than the existing best_bid.
-- action performed: (1) re-set best_bid* attributes for price-only and MAS negs
-- (2) re-set best_bid_bid* attributes for MAS negs
update_new_best_lines(p_auctionHeaderId, p_bidNumber, p_publishDate,
p_bidRanking, p_bidCurrencyCode, p_batchStart, p_batchEnd);
update_unchanged_rank(p_auctionHeaderId,p_bidNumber,p_vendorSiteId, p_batchStart, p_batchEnd, p_discard_tech_nonshort);
update_old_best_bid_number(p_auctionHeaderId, p_oldBidNumber, p_bidNumber, p_batchStart, p_batchEnd);
UPDATE pon_auction_item_prices_all
SET number_of_bids = nvl(number_of_bids,0) + 1
WHERE auction_header_id = p_auctionHeaderId
AND line_number IN (SELECT line_number
FROM pon_bid_item_prices
WHERE bid_number = p_bidNumber
AND publish_date = p_publishDate
AND line_number >= p_batchStart
AND line_number <= p_batchEnd);
END update_batched_part;
PROCEDURE update_batched_part_batch
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_publishDate DATE,
p_oldBidNumber NUMBER,
p_vendorSiteId NUMBER,
p_bidCurrencyCode VARCHAR2,
p_bidRanking VARCHAR2,
p_rankIndicator VARCHAR2,
p_maxLineNumber NUMBER,
p_batchSize NUMBER,
p_discard_tech_nonshort VARCHAR2)
IS PRAGMA AUTONOMOUS_TRANSACTION;
update_batched_part(p_auctionHeaderId, p_bidNumber, p_publishDate,
p_oldBidNumber, p_vendorSiteId, p_bidCurrencyCode,
p_bidRanking, p_rankIndicator, v_batchStart, v_batchEnd, p_discard_tech_nonshort);
END update_batched_part_batch;
PROCEDURE update_all_ranks
(p_auctionHeaderId NUMBER,
p_bidNumber NUMBER,
p_vendorSiteId NUMBER,
p_oldBidNumber NUMBER,
p_maxLineNumber NUMBER,
p_batchSize NUMBER,
p_discard_tech_nonshort VARCHAR2)
IS
v_publishDate pon_bid_headers.publish_date%TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'update_all_ranks';
SELECT publish_date,
bid_currency_code
INTO v_publishDate,
v_bidCurrencyCode
FROM pon_bid_headers
WHERE bid_number = p_bidNumber;
SELECT pah.bid_ranking,
pah.rank_indicator
INTO v_bidRanking,
v_rankIndicator
FROM pon_auction_headers_all pah
WHERE pah.auction_header_id = p_auctionHeaderId;
print_log(l_api_name, p_bidNumber || ' - BEGIN update all ranks');
update_non_batched_part_auto(p_auctionHeaderId, p_bidNumber, v_publishDate,
v_rankIndicator, v_bidRanking, p_discard_tech_nonshort);
update_non_batched_part(p_auctionHeaderId, p_bidNumber, v_publishDate,
v_rankIndicator, v_bidRanking, p_discard_tech_nonshort);
update_batched_part_batch(p_auctionHeaderId, p_bidNumber, v_publishDate,
p_oldBidNumber, p_vendorSiteId, v_bidCurrencyCode,
v_bidRanking, v_rankIndicator, p_maxLineNumber, p_batchSize, p_discard_tech_nonshort);
update_batched_part(p_auctionHeaderId, p_bidNumber, v_publishDate,
p_oldBidNumber, p_vendorSiteId, v_bidCurrencyCode,
v_bidRanking, v_rankIndicator, 1, p_maxLineNumber, p_discard_tech_nonshort);
print_log(l_api_name, p_bidNumber || ' - END update all ranks');
END update_all_ranks;
PROCEDURE UPDATE_AUCTION_INFO
( p_auctionHeaderId IN NUMBER,
p_bidNumber IN NUMBER,
p_vendorSiteId IN NUMBER,
p_isRebid IN VARCHAR2,
p_prevBidNumber IN NUMBER,
p_isSavingDraft IN VARCHAR2,
p_isSurrogateBid IN VARCHAR2,
p_loginUserId IN NUMBER,
x_return_status OUT NOCOPY NUMBER,
x_return_code OUT NOCOPY VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'update_auction_info';
print_log(l_api_name, p_bidNumber || ' - beginning of update auction info');
SELECT bid_status
INTO v_oldBidStatus
FROM pon_bid_headers
WHERE bid_number = v_oldBidNumber;
SELECT auto_extend_flag,
nvl(auto_extend_all_lines_flag,'Y'),
close_bidding_date,
trading_partner_id,
rank_indicator,
doctype_id,
nvl(is_paused, 'N'),
supplier_view_type,
max_internal_line_num,
DECODE(nvl(max_bid_color_sequence_id,-99),-99,-1, max_bid_color_sequence_id), --Added for Live Console
pf_type_allowed,
price_tiers_indicator,
sealed_auction_status,
two_part_flag
-- added by Allen Yang for Surrogate Bid 2008/10/07
---------------------------------------------------
, technical_evaluation_status
---------------------------------------------------
INTO v_autoExtendFlag,
v_autoExtendAllLinesFlag,
v_closeBiddingDate_old,
v_tradingPartnerId,
v_rankIndicator,
v_doctypeid,
v_is_paused,
v_supplierViewType,
v_maxLineNumber,
v_max_bid_color_sequence_id, --Added for Live Console
v_pfTypeAllowed,
v_priceTiersIndicator,
v_sealed_auction_status,
v_two_part_flag
-- added by Allen Yang for Surrogate Bid 2008/10/07
---------------------------------------------------
, v_technical_evaluation_status
---------------------------------------------------
FROM pon_auction_headers_all pah
WHERE auction_header_id = p_auctionHeaderId;
SELECT publish_date,
trading_partner_id,
color_sequence_id --Added for Live Console
INTO v_publishDate,
v_bidTradingPartnerId,
v_color_sequence_id --Added for Live Console
FROM pon_bid_headers
WHERE bid_number = p_bidNumber;
SELECT display_flag
INTO v_proxyEnabled
FROM pon_auc_doctype_rules rules,
pon_auc_bizrules bizrules
WHERE rules.doctype_id = v_doctypeid
AND rules.bizrule_id = bizrules.bizrule_id
AND bizrules.name = 'ALLOW_PROXYBID';
print_log(l_api_name, p_bidNumber || ' - calling subroutine update_proxy_bid');
update_proxy_bid_auto(p_auctionHeaderId, p_bidNumber, v_oldBidNumber,
p_isSurrogateBid, v_hasCloseDateReached,v_isPriceChanged);
update_proxy_bid(p_auctionHeaderId, p_bidNumber, v_oldBidNumber,
p_isSurrogateBid, v_hasCloseDateReached,v_isPriceChanged);
update_all_ranks(p_auctionHeaderId, p_bidNumber, p_vendorSiteId, v_prevActiveBidNumber, v_maxLineNumber, v_batchSize, v_discard_tech_nonshort);
g_bidsGenerated.DELETE;
UPDATE PON_BID_HEADERS
SET Color_Sequence_Id = v_max_bid_color_sequence_id +1
WHERE bid_number = p_bidNumber;
UPDATE pon_auction_item_prices_all
SET best_bid_number = p_bidNumber
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_number = v_oldBidNumber;
UPDATE pon_auction_item_prices_all
SET best_bid_bid_number = p_bidNumber
WHERE auction_header_id = p_auctionHeaderId
AND best_bid_bid_number = v_oldBidNumber;
UPDATE pon_auction_headers_all
SET max_bid_color_sequence_id = v_max_bid_color_sequence_id, --Added for Live Console
last_update_date = sysdate,
number_of_bids = (SELECT COUNT(auction_header_id)
FROM pon_bid_headers
WHERE auction_header_id = p_auctionHeaderId
AND (bid_status in ('ACTIVE', 'ARCHIVED')
OR bid_number = p_bidNumber))
WHERE auction_header_id = p_auctionHeaderId;
SELECT max(close_bidding_date),
max(number_of_extensions)
INTO v_max_close_bidding_date,
v_max_num_of_extensions
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auctionHeaderId;
SELECT first_line_close_date,
close_bidding_date,
nvl2(staggered_closing_interval,'Y','N')
INTO v_first_line_close_date,
v_orig_close_bidding_date,
v_is_staggered_auction
FROM pon_auction_headers_all
WHERE auction_header_id = p_auctionHeaderId;
UPDATE pon_auction_headers_all
SET close_bidding_date = v_max_close_bidding_date,
number_of_extensions = v_max_num_of_extensions,
first_line_close_date = v_first_line_close_date
WHERE auction_header_id = p_auctionHeaderId;
select conterms_exist_flag into v_contermsExist
from pon_auction_headers_all
where auction_header_id = p_auctionHeaderId;
select max(close_bidding_date)
into v_closeBiddingDateH
from pon_auction_item_prices_all
where auction_header_id = p_auctionHeaderId;
print_log(l_api_name, p_bidNumber || ': ' ||'calling subroutine PON_CONTERMS_UTL_PVT.updateDeliverables');
PON_CONTERMS_UTL_PVT.updateDeliverables(p_auctionHeaderId,
v_doctypeid,
v_closeBiddingDateH,
l_msg_data,
l_msg_count,
l_return_status);
print_log(l_api_name, p_bidNumber || ' - update auction info returns with price change=Y');
print_log(l_api_name, p_bidNumber || ' - END update auction info');
END update_auction_info;
SELECT contract_type,
supplier_view_type,
pf_type_allowed
INTO l_contract_type,
l_supplier_view_type,
l_pf_type_allowed
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT unit_price,
quantity
INTO l_bid_auction_curr_unit_price,
l_bid_quantity
FROM pon_bid_item_prices
WHERE bid_number = p_prev_auc_active_bid_number
AND line_number = p_line_number;
SELECT (l_bid_auction_curr_unit_price * ppsf.percentage) +
ppsf.unit_price +
ppsf.fixed_amount/decode(l_is_spo_transformed,
'Y', nvl(l_bid_quantity, 1),
nvl(aip.quantity, 1)
)
INTO l_auc_pf_unit_price
FROM pon_pf_supplier_formula ppsf,
pon_auction_item_prices_all aip,
pon_bid_headers pbh
WHERE ppsf.auction_header_id = p_auction_header_id
AND ppsf.line_number = p_line_number
AND ppsf.trading_partner_id = pbh.trading_partner_id
AND ppsf.vendor_site_id = pbh.vendor_site_id
AND pbh.bid_number = p_prev_auc_active_bid_number
AND aip.auction_header_id = ppsf.auction_header_id
AND aip.line_number = ppsf.line_number;
SELECT nvl(sum(decode(spf.pricing_basis,
'PER_UNIT', spf.auction_currency_value,
'PERCENTAGE', spf.auction_currency_value/100 * l_bid_auction_curr_unit_price,
(spf.auction_currency_value / decode(l_is_spo_transformed,
'Y', nvl(l_bid_quantity, 1),
nvl(aip.quantity, 1)
)
)
)
)
,0)
INTO l_bid_line_pf_unit_price
FROM pon_bid_price_elements spf,
pon_auction_item_prices_all aip
WHERE spf.bid_number = p_prev_auc_active_bid_number
AND spf.line_number = p_line_number
AND spf.sequence_number <> -10
AND spf.pf_type = 'SUPPLIER'
AND aip.auction_header_id = spf.auction_header_id
AND aip.line_number = spf.line_number;
SELECT line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number >= p_batch_start
AND line_number <= p_batch_end
ORDER BY line_number;
SELECT
pbh.bid_number
INTO
v_bid_number
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 nvl (pbh.shortlist_flag, 'Y') = 'N'
AND pbh.bid_status = 'ACTIVE'
AND pbip.line_number = auction_item_record.line_number
AND ROWNUM = 1;
SELECT
paip.best_bid_number,
paip.best_bid_bid_number,
paip.group_type
INTO
v_best_bid_number,
v_best_bid_bid_number,
v_currentline_group_type
FROM
pon_auction_item_prices_all paip
WHERE
paip.auction_header_id = p_auction_header_id
AND paip.line_number = auction_item_record.line_number;
SELECT
nvl (pbh.shortlist_flag, 'Y')
INTO
v_bestbid_shortlist_flag
FROM
pon_bid_headers pbh
WHERE
pbh.bid_number = v_best_bid_number;
SELECT
nvl (pbh.shortlist_flag, 'Y')
INTO
v_bestbid_shortlist_flag
FROM
pon_bid_headers pbh
WHERE
pbh.bid_number = v_best_bid_bid_number;
SELECT
price,
quantity,
promised_date,
bid_number,
bid_currency_price,
bid_currency_code,
first_bid_price,
proxy_bid_limit_price
INTO
t_price,
t_quantity,
t_promised_date,
t_bid_number,
t_bid_currency_price,
t_bid_currency_code,
t_first_bid_price,
t_proxy_bid_limit_price
FROM
(SELECT
bidline.line_number,
bidline.price,
bidline.quantity,
bidline.promised_date,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code,
bidline.first_bid_price,
bidline.proxy_bid_limit_price
FROM
pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE
bidline.auction_header_id = p_auction_header_id
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND bidheader.bid_status = 'ACTIVE'
AND nvl (bidheader.shortlist_flag, 'Y') = 'Y'
AND bidline.line_number = auction_item_record.line_number
ORDER BY
decode (v_currentline_group_type, 'GROUP', bidline.group_amount, bidline.price),
bidline.publish_date asc)
WHERE
rownum = 1;
UPDATE
pon_auction_item_prices_all
SET
best_bid_number = t_bid_number
WHERE
auction_header_id = p_auction_header_id
AND line_number = auction_item_record.line_number;
UPDATE
pon_auction_item_prices_all
SET
best_bid_price = t_price,
best_bid_quantity = t_quantity,
best_bid_promised_date = t_promised_date,
best_bid_number = t_bid_number,
best_bid_currency_price = t_bid_currency_price,
best_bid_currency_code = t_bid_currency_code,
best_bid_first_bid_price = t_first_bid_price,
best_bid_proxy_limit_price = t_proxy_bid_limit_price
WHERE
auction_header_id = p_auction_header_id
AND line_number = auction_item_record.line_number;
message => 'Updated pon_auction_item_prices_all');
UPDATE
pon_auction_item_prices_all
SET
best_bid_bid_number = decode (p_bid_ranking,
'MULTI_ATTRIBUTE_SCORING', t_bid_number,
null)
WHERE
auction_header_id = p_auction_header_id
AND line_number = auction_item_record.line_number;
SELECT
price,
total_weighted_score,
bid_number,
bid_currency_price,
bid_currency_code
INTO
t_price,
t_score,
t_bid_number,
t_bid_currency_price,
t_bid_currency_code
FROM
(SELECT
bidline.line_number,
bidline.price,
bidline.total_weighted_score,
bidline.bid_number,
bidline.bid_currency_price,
bidheader.bid_currency_code
FROM
pon_bid_item_prices bidline,
pon_bid_headers bidheader
WHERE
bidline.auction_header_id = p_auction_header_id
AND bidheader.auction_header_id = bidline.auction_header_id
AND bidheader.bid_number = bidline.bid_number
AND bidheader.bid_status = 'ACTIVE'
AND nvl (bidheader.shortlist_flag, 'Y') = 'Y'
AND bidline.line_number = auction_item_record.line_number
ORDER BY
bidline.total_weighted_score/bidline.price desc,
bidline.publish_date asc)
WHERE
rownum = 1;
UPDATE
pon_auction_item_prices_all
SET
best_bid_bid_price = t_price,
best_bid_score = t_score,
best_bid_bid_number = t_bid_number,
best_bid_bid_currency_price = t_bid_currency_price,
best_bid_bid_currency_code = t_bid_currency_code
WHERE
auction_header_id = p_auction_header_id
AND line_number = auction_item_record.line_number;
message => 'Updated pon_auction_item_prices_all');
v_bid_numbers_bulk.DELETE;
v_rank_bulk.DELETE;
SELECT
pbh.bid_number,
pbip.rank
BULK COLLECT INTO
v_bid_numbers_bulk,
v_rank_bulk
FROM
pon_bid_headers pbh,
pon_bid_item_prices pbip
WHERE
pbh.auction_header_id = p_auction_header_id
AND pbh.bid_number = pbip.bid_number
AND pbh.bid_status = 'ACTIVE'
AND pbip.line_number = auction_item_record.line_number
AND nvl (pbh.shortlist_flag, 'Y') = 'Y'
ORDER BY
pbip.rank;
UPDATE pon_bid_item_prices pbip
SET rank = v_rank_bulk (x)
WHERE bid_number = v_bid_numbers_bulk (x)
AND line_number = auction_item_record.line_number;
PROCEDURE update_auction_info_tech_short ( -- {
x_result OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
p_auction_header_id IN NUMBER,
p_user_id IN NUMBER
)
IS
l_module_name VARCHAR2(40) := 'update_auction_info_tech_short';
SELECT
bid_number
INTO
v_bid_number
FROM
pon_bid_headers
WHERE
auction_header_id = p_auction_header_id
AND nvl (shortlist_flag, 'Y') = 'N'
AND bid_status = 'ACTIVE'
AND ROWNUM = 1;
SELECT
rank_indicator,
bid_ranking,
max_internal_line_num
INTO
v_rank_indicator,
v_bid_ranking,
v_max_internal_line_num
FROM
pon_auction_headers_all
WHERE
auction_header_id = p_auction_header_id
FOR
UPDATE OF close_bidding_date;
UPDATE
pon_bid_headers pbh
SET
pbh.last_update_date = sysdate,
pbh.last_updated_by = fnd_global.user_id,
pbh.technical_shortlist_flag = decode (pbh.bid_status, 'ACTIVE', nvl (pbh.shortlist_flag, 'Y'), 'N')
WHERE
pbh.auction_header_id = p_auction_header_id;
UPDATE pon_team_member_bid_scores
SET SCORE_STATUS = 'NA'
WHERE
SCORE_STATUS = 'SUBMIT'
AND auction_header_id = p_auction_header_id;
UPDATE
pon_auction_headers_all paha
SET
paha.last_update_date = sysdate,
paha.last_updated_by = fnd_global.user_id,
paha.scoring_lock_date = null,
paha.scoring_lock_tp_contact_id = p_user_id,
paha.sealed_auction_status = 'UNLOCKED',
paha.sealed_actual_unlock_date = sysdate,
paha.sealed_unlock_tp_contact_id = p_user_id
WHERE
paha.auction_header_id = p_auction_header_id;
UPDATE pon_bid_headers bh
SET bh.bid_status='ARCHIVED_DRAFT'
WHERE
bh.auction_header_id = p_auction_header_id
AND (EXISTS (select 1 FROM pon_auction_headers_all ah WHERE ah.auction_header_id = bh.auction_header_id
AND Nvl(ah.two_part_flag, 'N') = 'Y'
AND Nvl(ah.technical_evaluation_status, 'N') ='COMPLETED')
AND Nvl(bh.surrog_bid_flag, 'N') = 'Y'
AND bh.bid_status = 'DRAFT'
AND bh.submit_stage IS NULL);
END update_auction_info_tech_short; --}