[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.close_bidding_date, a.last_pause_date, a.is_paused
INTO v_auc_close_bidding_date, v_pause_date, v_is_paused
FROM pon_auction_headers_all a
WHERE a.auction_header_id = p_auction_header_id;
SELECT a.close_bidding_date
INTO v_line_close_bidding_date
FROM pon_auction_item_prices_all a
WHERE a.auction_header_id = p_auction_header_id
AND a.line_number = p_line_number;
SELECT paha.supplier_view_type,
paha.contract_type,
DECODE(paha.doctype_id, 21, 'RFI', 5, 'RFQ', 1, 'AUCTION') document_type
INTO v_supplier_view_type,
v_contract_type,
v_doctype
FROM pon_bid_headers pbh,
pon_auction_headers_all paha
WHERE pbh.bid_number = p_bid_number
AND paha.auction_header_id = pbh.auction_header_id;
UPDATE pon_bid_item_prices p1
SET p1.group_amount =
(SELECT SUM(decode(p_is_supplier,
'Y', p2.bid_currency_price,
p2.price) *
decode(a1.order_type_lookup_code,
'FIXED PRICE', 1,
decode(v_contract_type,
'STANDARD', p2.quantity,
nvl(p2.quantity, a1.quantity))))
FROM pon_bid_item_prices p2,
pon_auction_item_prices_all a1
WHERE p2.bid_number = p_bid_number
AND a1.auction_header_id = p2.auction_header_id
AND p2.line_number = a1.line_number
AND a1.parent_line_number = p1.line_number)
WHERE p1.bid_number = p_bid_number
AND (p_do_all_lines = 'Y'
OR p1.batch_id = p_batch_id)
AND (SELECT a2.group_type
FROM pon_auction_item_prices_all a2
WHERE a2.auction_header_id = p1.auction_header_id
AND a2.line_number = p1.line_number) = 'GROUP';
SELECT paha.supplier_view_type,
paha.contract_type,
DECODE(paha.doctype_id, 21, 'RFI', 5, 'RFQ', 1, 'AUCTION')
INTO v_supplier_view_type,
v_contract_type,
v_doctype
FROM pon_bid_headers pbh,
pon_auction_headers_all paha
WHERE pbh.bid_number = p_bid_number
AND paha.auction_header_id = pbh.auction_header_id;
SELECT SUM(decode(p_is_supplier,
'Y', group_line.bid_currency_price,
group_line.price) *
decode(auc_line.order_type_lookup_code,
'FIXED PRICE', 1,
decode(v_contract_type,
'STANDARD', group_line.quantity,
nvl(group_line.quantity, auc_line.quantity))))
INTO p_group_amount
FROM pon_bid_item_prices groups,
pon_bid_item_prices group_line,
pon_auction_item_prices_all auc_line
WHERE groups.bid_number = p_bid_number
AND group_line.bid_number = groups.bid_number
AND groups.line_number = p_line_number
AND group_line.auction_header_id = auc_line.auction_header_id
AND group_line.line_number = auc_line.line_number
AND auc_line.parent_line_number = groups.line_number;
SELECT number_price_decimals, rate, auction_header_id, surrog_bid_flag, old_bid_number
INTO v_precision, v_rate, v_auction_header_id, v_surrog_bid_flag, v_source_bid
FROM pon_bid_headers
WHERE bid_number=p_bid_number;
SELECT a.is_paused, a.last_pause_date, a.max_internal_line_num,
decode(a.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
a.price_tiers_indicator
INTO v_is_paused, v_paused_date, v_max_line_number,
v_is_blanket,v_price_tiers_indicator
FROM pon_auction_headers_all a
WHERE a.auction_header_id = v_auction_header_id;
UPDATE pon_bid_item_prices pbip
SET pbip.bid_currency_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
pbip.bid_currency_unit_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
pbip.bid_currency_trans_price = round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision),
pbip.price = (round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision)) / v_rate,
pbip.unit_price = (round(pbip.bid_currency_price * (1 - p_power_percentage / 100), v_precision)) / v_rate
WHERE pbip.bid_number = p_bid_number
AND (get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate
OR v_surrog_bid_flag = 'Y')
AND pbip.copy_price_for_proxy_flag = 'N'
AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
AND pbip.line_number IN (SELECT line_number
FROM PON_AUCTION_ITEM_PRICES_ALL paip,
PON_AUCTION_HEADERS_ALL pah
WHERE pah.auction_header_id = paip.auction_header_id
AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
AND paip.auction_header_id = pbip.auction_header_id
AND paip.line_number = pbip.line_number))
OR
(nvl(p_powerbidlosinglines,'N') = 'N'))
AND pbip.line_number >= v_batch_start
AND pbip.line_number <= v_batch_end ;
UPDATE pon_bid_shipments pbs
SET pbs.bid_currency_unit_price =
DECODE(pbs.price_type,
'PRICE', round(pbs.bid_currency_price * (1 - p_power_percentage/100), v_precision),
round((SELECT pbip.bid_currency_unit_price
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND pbip.line_number = pbs.line_number) *
(1 - pbs.price_discount/100), v_precision))
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number IN (SELECT pbip.line_number
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND ((get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate) OR (v_surrog_bid_flag = 'Y'))
AND pbip.copy_price_for_proxy_flag = 'N')
AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
AND pbs.line_number IN (SELECT line_number
FROM PON_AUCTION_ITEM_PRICES_ALL paip,
PON_AUCTION_HEADERS_ALL pah
WHERE pah.auction_header_id = paip.auction_header_id
AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
AND paip.auction_header_id = pbs.auction_header_id
AND paip.line_number = pbs.line_number))
OR
(nvl(p_powerbidlosinglines,'N') = 'N'))
AND pbs.line_number >= v_batch_start
AND pbs.line_number <= v_batch_end;
UPDATE pon_bid_shipments pbs
SET pbs.bid_currency_price = pbs.bid_currency_unit_price,
pbs.unit_price = pbs.bid_currency_unit_price / v_rate,
pbs.price = pbs.bid_currency_unit_price / v_rate
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number IN (SELECT pbip.line_number
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND ((get_line_close_bidding_date(pbip.auction_header_id, pbip.line_number, v_is_paused, v_paused_date) > sysdate) OR (v_surrog_bid_flag = 'Y'))
AND pbip.copy_price_for_proxy_flag = 'N')
AND ((nvl(p_powerbidlosinglines,'N') = 'Y'
AND pbs.line_number IN (SELECT line_number
FROM PON_AUCTION_ITEM_PRICES_ALL paip,
PON_AUCTION_HEADERS_ALL pah
WHERE pah.auction_header_id = paip.auction_header_id
AND DECODE(pah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING',paip.best_bid_bid_number,paip.best_bid_number) <> p_previous_bid_number
AND paip.auction_header_id = pbs.auction_header_id
AND paip.line_number = pbs.line_number))
OR
(nvl(p_powerbidlosinglines,'N') = 'N'))
AND pbs.line_number >= v_batch_start
AND pbs.line_number <= v_batch_end;
SELECT pbh.rate,
pbh.number_price_decimals,
fnd.precision,
pbh.display_price_factors_flag,
paha.supplier_view_type,
paha.contract_type,
paha.auction_header_id,
paha.max_internal_line_num,
nvl(paha.progress_payment_type,'NONE'),
nvl(paha.ADVANCE_NEGOTIABLE_FLAG,'N'),
nvl(paha.MAX_RETAINAGE_NEGOTIABLE_FLAG,'N')
INTO v_rate,
v_precision,
v_fnd_precision,
v_display_price_factors_flag,
v_supplier_view_type,
v_contract_type,
v_auction_header_id,
v_max_line_number,
v_progress_payment_type,
v_advance_negotiable,
v_max_rtng_negotiable
FROM pon_bid_headers pbh,
fnd_currencies fnd,
pon_auction_headers_all paha
WHERE pbh.bid_number = p_bid_number
AND paha.auction_header_id = pbh.auction_header_id
AND fnd.currency_code = pbh.bid_currency_code;
UPDATE pon_bid_price_elements pbpe
SET pbpe.bid_currency_value =
DECODE(pbpe.pricing_basis,
'PER_UNIT', round(pbpe.auction_currency_value*v_rate, v_precision),
'FIXED_AMOUNT', round(pbpe.auction_currency_value*v_rate, v_fnd_precision),
pbpe.auction_currency_value)
WHERE pbpe.bid_number = p_bid_number
AND pbpe.pf_type = 'BUYER'
AND pbpe.line_number >= v_batch_start
AND pbpe.line_number <= v_batch_end;
UPDATE pon_bid_price_elements pbpe
SET pbpe.auction_currency_value = pbpe.bid_currency_value / decode(pbpe.pricing_basis, 'PERCENTAGE', 1, v_rate)
WHERE pbpe.bid_number = p_bid_number
AND pbpe.pf_type = 'SUPPLIER'
-- process only batch, or all SUPPLIER price elements if currency changed
AND ((p_curr_changed = 'Y' AND
pbpe.line_number >= v_batch_start AND
pbpe.line_number <= v_batch_end)
OR pbpe.batch_id = p_batch_id);
UPDATE pon_bid_item_prices pbip
SET pbip.bid_currency_trans_price = nvl(
(SELECT (pbip.bid_currency_unit_price * ppsf.percentage) +
ppsf.unit_price*v_rate +
(ppsf.fixed_amount*v_rate / decode(v_is_spo_transformed,
'Y', nvl(pbip.quantity, 1),
nvl(aip.quantity, 1)))
FROM pon_pf_supplier_formula ppsf,
pon_bid_headers pbh,
pon_auction_item_prices_all aip
WHERE ppsf.auction_header_id = pbip.auction_header_id
AND ppsf.line_number = pbip.line_number
AND pbip.bid_number = pbh.bid_number
AND ppsf.trading_partner_id = pbh.trading_partner_id
AND ppsf.vendor_site_id = pbh.vendor_site_id
AND aip.auction_header_id = pbip.auction_header_id
AND aip.line_number = pbip.line_number),
pbip.bid_currency_unit_price)
WHERE pbip.bid_number = p_bid_number
-- process only batch, or all lines if currency change
AND ((p_curr_changed = 'Y' AND
pbip.line_number >= v_batch_start AND
pbip.line_number <= v_batch_end)
OR pbip.batch_id = p_batch_id);
UPDATE pon_bid_item_prices pbip
SET pbip.bid_currency_trans_price =
(SELECT pbip.bid_currency_trans_price +
nvl(sum( Decode(Nvl(negative_cost_factor_flag,'N'),'Y',
-decode(spf.pricing_basis,
'PER_UNIT', spf.bid_currency_value,
'PERCENTAGE', spf.bid_currency_value/100 * pbip.bid_currency_unit_price,
(spf.bid_currency_value / decode(v_is_spo_transformed,
'Y', nvl(pbip.quantity, 1),
nvl(aip.quantity, 1)))),
decode(spf.pricing_basis,
'PER_UNIT', spf.bid_currency_value,
'PERCENTAGE', spf.bid_currency_value/100 * pbip.bid_currency_unit_price,
(spf.bid_currency_value / decode(v_is_spo_transformed,
'Y', nvl(pbip.quantity, 1),
nvl(aip.quantity, 1)))))),
0)
FROM pon_bid_price_elements spf,
pon_auction_item_prices_all aip
WHERE spf.bid_number = p_bid_number
AND spf.line_number = pbip.line_number
AND spf.sequence_number <> -10
AND aip.auction_header_id = spf.auction_header_id
AND aip.line_number = spf.line_number
AND spf.pf_type = 'SUPPLIER')
WHERE pbip.bid_number = p_bid_number
-- process only batch, or all lines if currency change
AND ((p_curr_changed = 'Y' AND
pbip.line_number >= v_batch_start AND
pbip.line_number <= v_batch_end)
OR pbip.batch_id = p_batch_id);
UPDATE pon_bid_item_prices pbip
SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
pbip.price = pbip.bid_currency_trans_price / v_rate,
pbip.bid_currency_price = DECODE(v_supplier_view_type,
'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
'UNTRANSFORMED', pbip.bid_currency_unit_price),
pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
(pbip.bid_curr_advance_amount,
pbip.bid_curr_max_retainage_amt,
pbip.advance_amount,
pbip.max_retainage_amount)
= (SELECT nvl2(pbip.bid_curr_advance_amount,
decode(v_advance_negotiable, 'Y',
decode( pbip.advance_amount-paip.advance_amount, 0, round(pbip.advance_amount* v_rate,v_fnd_precision),pbip.bid_curr_advance_amount),
round(pbip.advance_amount* v_rate,v_fnd_precision)
), pbip.bid_curr_advance_amount),
nvl2(pbip.bid_curr_max_retainage_amt,
decode(v_max_rtng_negotiable, 'Y',
decode( pbip.max_retainage_amount-paip.max_retainage_amount, 0, round(pbip.max_retainage_amount* v_rate,v_fnd_precision),pbip.bid_curr_max_retainage_amt),
round(pbip.max_retainage_amount* v_rate,v_fnd_precision)
), pbip.bid_curr_max_retainage_amt),
nvl2(pbip.advance_amount,
decode(v_advance_negotiable, 'Y',
decode( pbip.advance_amount-paip.advance_amount, 0, pbip.advance_amount,pbip.bid_curr_advance_amount/v_rate),
pbip.advance_amount
), pbip.advance_amount),
nvl2(pbip.max_retainage_amount,
decode(v_max_rtng_negotiable, 'Y',
decode( pbip.max_retainage_amount-paip.max_retainage_amount, 0, pbip.max_retainage_amount,pbip.bid_curr_max_retainage_amt/v_rate),
pbip.max_retainage_amount
), pbip.max_retainage_amount)
FROM pon_auction_item_prices_all paip
WHERE paip.auction_header_id=pbip.auction_header_id
AND paip.line_number=pbip.line_number)
WHERE pbip.bid_number = p_bid_number
-- process only batch, or all lines if currency change
AND ((p_curr_changed = 'Y' AND
pbip.line_number >= v_batch_start AND
pbip.line_number <= v_batch_end)
OR pbip.batch_id = p_batch_id);
UPDATE pon_bid_item_prices pbip
SET pbip.unit_price = pbip.bid_currency_unit_price / v_rate,
pbip.price = pbip.bid_currency_trans_price / v_rate,
pbip.bid_currency_price = DECODE(v_supplier_view_type,
'TRANSFORMED', round(pbip.bid_currency_trans_price, v_precision),
'UNTRANSFORMED', pbip.bid_currency_unit_price),
pbip.bid_currency_trans_price = round(pbip.bid_currency_trans_price, v_precision),
pbip.proxy_bid_limit_price = pbip.bid_currency_limit_price / v_rate,
pbip.po_min_rel_amount = pbip.po_bid_min_rel_amount / v_rate,
pbip.advance_amount = pbip.bid_curr_advance_amount/ v_rate,
pbip.max_retainage_amount = pbip.bid_curr_max_retainage_amt/v_rate
WHERE pbip.bid_number = p_bid_number
-- process only batch, or all lines if currency change
AND ((p_curr_changed = 'Y' AND
pbip.line_number >= v_batch_start AND
pbip.line_number <= v_batch_end)
OR pbip.batch_id = p_batch_id);
UPDATE pon_bid_shipments pbs
SET pbs.bid_currency_price = nvl(
(SELECT (pbs.bid_currency_unit_price * ppsf.percentage) +
ppsf.unit_price*v_rate +
(ppsf.fixed_amount*v_rate / decode(v_is_spo_transformed,
'Y', nvl(pbip.quantity, 1),
nvl(aip.quantity, 1)))
FROM pon_pf_supplier_formula ppsf,
pon_bid_headers pbh,
pon_auction_item_prices_all aip,
pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND pbip.line_number = pbs.line_number
AND ppsf.auction_header_id = pbip.auction_header_id
AND ppsf.line_number = pbip.line_number
AND pbip.bid_number = pbh.bid_number
AND ppsf.trading_partner_id = pbh.trading_partner_id
AND ppsf.vendor_site_id = pbh.vendor_site_id
AND aip.auction_header_id = pbip.auction_header_id
AND aip.line_number = pbip.line_number),
pbs.bid_currency_unit_price)
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number >= v_batch_start
AND pbs.line_number <= v_batch_end;
UPDATE pon_bid_shipments pbs
SET pbs.bid_currency_price =
(SELECT pbs.bid_currency_price +
nvl(sum(decode(spf.pricing_basis,
'PER_UNIT', spf.bid_currency_value,
'PERCENTAGE', spf.bid_currency_value/100 * pbs.bid_currency_unit_price,
(spf.bid_currency_value / decode(v_is_spo_transformed,
'Y', nvl(pbip.quantity, 1),
nvl(aip.quantity, 1))))),
0)
FROM pon_bid_price_elements spf,
pon_auction_item_prices_all aip,
pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND pbip.line_number = pbs.line_number
AND spf.bid_number = p_bid_number
AND spf.line_number = pbip.line_number
AND spf.sequence_number <> -10
AND aip.auction_header_id = spf.auction_header_id
AND aip.line_number = spf.line_number
AND spf.pf_type = 'SUPPLIER')
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number >= v_batch_start
AND pbs.line_number <= v_batch_end;
UPDATE pon_bid_shipments pbs
SET pbs.unit_price = pbs.bid_currency_unit_price / v_rate,
pbs.price = pbs.bid_currency_price / v_rate,
pbs.bid_currency_price = DECODE(v_supplier_view_type,
'TRANSFORMED', round(pbs.bid_currency_price, v_precision),
'UNTRANSFORMED', pbs.bid_currency_unit_price)
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number >= v_batch_start
AND pbs.line_number <= v_batch_end;
UPDATE pon_bid_payments_shipments pbps
SET pbps.price = pbps.bid_currency_price / v_rate
WHERE pbps.bid_number = p_bid_number
AND pbps.bid_line_number >= v_batch_start
AND pbps.bid_line_number <= v_batch_end;
Cursor infoCursor Is select auction_header_id,auction_line_number,line_number,group_line_id from pon_bid_item_prices where
bid_number = p_bid_number and nvl(clm_info_flag,'N') = 'Y' AND auction_line_number = -1;
select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = infoLine.group_line_id;
update pon_bid_item_prices
set has_bid_flag = l_award_flag
where bid_number = p_bid_number
and line_number = infoLine.line_number;
select count(*) into l_bid_line_count from pon_bid_item_prices where
bid_number = p_bid_number and group_line_id = infoLine.line_number and nvl(has_bid_flag,'N') = 'Y';
update pon_bid_item_prices
set has_bid_flag = 'Y'
where bid_number = p_bid_number
and line_number = infoLine.line_number;
UPDATE pon_bid_item_prices
SET publish_date = p_publish_date,
proxy_bid_flag = 'N',
award_price = price,
first_bid_price = price, --(woojin) do we really need this?
bid_trading_partner_id = decode(p_surrog_bid_flag,
'Y', p_auc_tp_id, p_tp_id)
WHERE bid_number = p_bid_number
AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
UPDATE pon_bid_item_prices
SET publish_date = p_publish_date,
proxy_bid_flag = 'N',
award_price = price,
first_bid_price = nvl(first_bid_price, price), --(woojin) do we really need this?
bid_trading_partner_id = decode(p_surrog_bid_flag,
'Y', p_auc_tp_id, p_tp_id)
WHERE bid_number = p_bid_number
AND is_changed_line_flag = 'Y'
AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
UPDATE pon_bid_item_prices
SET award_price = price
WHERE bid_number = p_bid_number
AND is_changed_line_flag <> 'Y'
AND auction_line_number = -1; --Unsolicited Lines Project : Update unsol lines.
SELECT
paha.TWO_PART_FLAG
, paha.TECHNICAL_EVALUATION_STATUS
, pbh.SURROG_BID_FLAG
, Nvl(paha.ALLOW_UNSOL_OFFER_LINES,'N') --Unsolicited Lines Project
INTO
l_two_part_flag
, l_technical_evaluation_status
, l_surrogate_bid_flag
, l_allow_unsol_lines --Unsolicited Lines Project
FROM
PON_AUCTION_HEADERS_ALL paha
, PON_BID_HEADERS pbh
WHERE paha.auction_header_id = pbh.auction_header_id
AND pbh.bid_number = p_bid_number;
SELECT close_bidding_date, group_enabled_flag,
max_internal_line_num, trading_partner_id, sealed_auction_status, two_part_flag
INTO v_auc_close_bidding_date, v_group_enabled_flag,
v_maxLineNumber, v_auc_tp_id, v_sealed_auction_status, v_two_part_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id
FOR UPDATE OF CLOSE_BIDDING_DATE;
SELECT vendor_site_id, old_bid_number, surrog_bid_flag, trading_partner_contact_name, evaluation_flag
INTO v_vendor_site_id, v_prev_bid_number, v_surrog_bid_flag, v_tpc_name, v_evaluation_flag
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
SELECT surrog_bid_receipt_date
INTO v_surrog_bid_receipt_date
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
update_bid_header_fields_auto(p_bid_number, v_publish_date, p_publish_date, v_surrog_bid_flag, v_two_part_flag, v_sealed_auction_status);
update_bid_header_fields(p_bid_number, v_publish_date, p_publish_date, v_surrog_bid_flag, v_two_part_flag, v_sealed_auction_status);
print_log(l_api_name, p_bid_number || ' - update auction info');
PON_AUCTION_HEADERS_PKG.UPDATE_AUCTION_INFO(p_auction_header_id, p_bid_number, v_vendor_site_id, p_rebid_flag, v_prev_bid_number, 'N', v_surrog_bid_flag, p_user_id, v_subroutine_return_status, v_subroutine_return_code);
print_log(l_api_name, p_bid_number || ' - update auction info; error code=' || x_return_code);
print_log(l_api_name, p_bid_number || ' - update auction info; price changed');
SELECT wf_item_key
INTO v_wf_item_key
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_tp_id
AND nvl(supp_acknowledgement, 'N') = 'N'
AND rownum=1;
PON_EVAL_TEAM_UTIL_PVT.send_eval_update_scorer_notif(p_bid_number);
UPDATE pon_bid_headers
SET draft_locked = 'N',
draft_unlocked_by = p_tp_id,
draft_unlocked_by_contact_id = p_tpc_id,
draft_unlocked_date = sysdate,
bid_status = 'ACTIVE'
--added by Allen Yang for Surrogate Bid 2008/09/08
--------------------------------------------------
, submit_stage = l_submit_stage
--------------------------------------------------
WHERE bid_number = p_bid_number;
Cursor infoCursor Is select auction_header_id,auction_line_number,line_number,group_line_id from pon_bid_item_prices where
bid_number = p_bid_number and nvl(clm_info_flag,'N') = 'Y' AND auction_line_number <> -1;
select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = infoLine.group_line_id;
update pon_bid_item_prices
set has_bid_flag = l_award_flag
where bid_number = p_bid_number
and line_number = infoLine.line_number;
select count(*) into l_bid_line_count from pon_bid_item_prices where
bid_number = p_bid_number and group_line_id = infoLine.line_number and nvl(has_bid_flag,'N') = 'Y';
update pon_bid_item_prices
set has_bid_flag = 'Y'
where bid_number = p_bid_number
and line_number = infoLine.line_number;
Cursor optCursor Is select auction_header_id,auction_line_number,line_number,clm_base_line_num from pon_bid_item_prices where
bid_number = p_bid_number and clm_base_line_num is not null and nvl(has_bid_flag,'N') = 'N';
select nvl(has_bid_flag,'N') into l_award_flag from pon_bid_item_prices where bid_number = p_bid_number and line_number = optLine.clm_base_line_num;
update pon_bid_item_prices
set has_bid_flag = l_award_flag
where bid_number = p_bid_number
and line_number = optLine.line_number;
DELETE FROM pon_bid_attribute_values pbav
WHERE pbav.bid_number = p_bid_number
AND (EXISTS (SELECT null
FROM pon_auction_attributes paa
WHERE pbav.auction_header_id = paa.auction_header_id
AND pbav.line_number = paa.line_number
AND pbav.attribute_name = paa.attribute_name
AND paa.display_only_flag = 'Y')
OR pbav.value IS NULL
OR EXISTS (SELECT null
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbav.bid_number
AND pbip.line_number = pbav.line_number
AND pbip.has_bid_flag = 'N'))
AND pbav.line_number >= p_batch_start
AND pbav.line_number <= p_batch_end;
DELETE FROM pon_bid_price_elements pbpe
WHERE pbpe.bid_number = p_bid_number
AND EXISTS (SELECT null
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbpe.bid_number
AND pbip.line_number = pbpe.line_number
AND pbip.has_bid_flag = 'N')
AND pbpe.line_number >= p_batch_start
AND pbpe.line_number <= p_batch_end;
DELETE FROM pon_bid_price_differentials pbpd
WHERE pbpd.bid_number = p_bid_number
AND EXISTS (SELECT null
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbpd.bid_number
AND pbip.line_number = pbpd.line_number
AND pbip.has_bid_flag = 'N')
AND pbpd.line_number >= p_batch_start
AND pbpd.line_number <= p_batch_end;
DELETE FROM pon_bid_shipments pbs
WHERE pbs.bid_number = p_bid_number
AND EXISTS (SELECT null
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbs.bid_number
AND pbip.line_number = pbs.line_number
AND pbip.has_bid_flag = 'N')
AND pbs.line_number >= p_batch_start
AND pbs.line_number <= p_batch_end;
DELETE FROM pon_bid_payments_shipments pbps
WHERE pbps.bid_number = p_bid_number
AND EXISTS (SELECT null
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = pbps.bid_number
AND pbip.line_number = pbps.bid_line_number
AND pbip.has_bid_flag = 'N')
AND pbps.bid_line_number >= p_batch_start
AND pbps.bid_line_number <= p_batch_end;
DELETE FROM pon_bid_item_prices
WHERE bid_number = p_bid_number
AND has_bid_flag = 'N'
AND line_number >= p_batch_start
AND line_number <= p_batch_end;
PROCEDURE update_bid_header_fields
(p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_publish_date IN pon_bid_headers.publish_date%TYPE,
p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields';
print_log(l_api_name, p_bid_number || ' - begin update bid header fields');
print_log(l_api_name, p_bid_number || ' - update bid header fields: p_publish_date=' || p_publish_date);
UPDATE pon_bid_headers
SET publish_date = p_publish_date,
shortlist_flag = 'Y',
surrog_bid_online_entry_date = p_bid_entry_date,
technical_shortlist_flag = l_tech_shortlist_flag
--,bid_status = 'ACTIVE'
WHERE bid_number = p_bid_number;
print_log(l_api_name, p_bid_number || ' - end update bid header fields');
END update_bid_header_fields;
PROCEDURE update_bid_header_fields_auto
(p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_publish_date IN pon_bid_headers.publish_date%TYPE,
p_bid_entry_date IN pon_bid_headers.publish_date%TYPE,
p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE,
p_two_part_flag IN pon_auction_headers_all.two_part_flag%TYPE,
p_sealed_auction_status IN pon_auction_headers_all.sealed_auction_status%TYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'update_bid_header_fields_auto';
print_log(l_api_name, p_bid_number || ' - BEGIN update bid header fields AUTONOMOUS');
update_bid_header_fields(p_bid_number, p_publish_date, p_bid_entry_date, p_surrog_bid_flag, p_two_part_flag, p_sealed_auction_status);
print_log(l_api_name, p_bid_number || ' - update bid header fields: committed!');
print_log(l_api_name, p_bid_number || ' - END update bid header fields AUTONOMOUS');
END update_bid_header_fields_auto;
UPDATE pon_bid_item_prices
SET publish_date = p_publish_date,
proxy_bid_flag = 'N',
award_price = price,
first_bid_price = price, --(woojin) do we really need this?
bid_trading_partner_id = decode(p_surrog_bid_flag,
'Y', p_auc_tp_id, p_tp_id)
WHERE bid_number = p_bid_number
AND line_number >= p_batch_start
AND line_number <= p_batch_end
AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
UPDATE pon_bid_item_prices
SET publish_date = p_publish_date,
proxy_bid_flag = 'N',
award_price = price,
first_bid_price = nvl(first_bid_price, price), --(woojin) do we really need this?
bid_trading_partner_id = decode(p_surrog_bid_flag,
'Y', p_auc_tp_id, p_tp_id)
WHERE bid_number = p_bid_number
AND is_changed_line_flag = 'Y'
AND line_number >= p_batch_start
AND line_number <= p_batch_end
AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
UPDATE pon_bid_item_prices
SET award_price = price
WHERE bid_number = p_bid_number
AND is_changed_line_flag <> 'Y'
AND line_number >= p_batch_start
AND line_number <= p_batch_end
AND auction_line_number <> -1; --Unsolicited Lines Project : Update unsol lines seperately.
SELECT user_name
INTO v_user_name
FROM fnd_user
WHERE user_id = p_user_id;
UPDATE pon_bid_headers
SET request_id = null
WHERE bid_number = p_bid_number;
INSERT INTO pon_interface_errors
(bid_number, auction_header_id, interface_type, request_id,
error_message_name, expiration_date)
VALUES
(p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
v_error_msg_name, sysdate+7);
INSERT INTO pon_interface_errors
(bid_number, auction_header_id, interface_type, request_id,
error_message_name, expiration_date)
VALUES
(p_bid_number, p_auction_header_id, 'PUBLISHBID', v_request_id,
v_error_msg_name, sysdate+7);
UPDATE pon_bid_headers
SET bid_status = 'DRAFT'
WHERE bid_number = p_bid_number;
SELECT user_name
INTO v_user_name
FROM fnd_user
WHERE user_id = p_user_id;
SELECT decode (nvl (surrog_bid_flag, 'N'), 'Y', 'PONENQMGDR_MANAGEDRAFT_SURROG', 'PONENQMGDR_MANAGEDRAFT')
INTO v_success_destination
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
INSERT INTO pon_interface_errors
(bid_number, auction_header_id, interface_type, request_id,
error_message_name, expiration_date)
VALUES
(p_bid_number, p_auction_header_id, 'VALIDATEBID', v_request_id,
v_error_msg_name, sysdate+7);
SELECT message_suffix
INTO v_suffix
FROM pon_auc_doctypes
WHERE doctype_id = (SELECT doctype_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id);
SELECT
FND_USER.user_id
INTO
l_user_id
FROM
FND_USER
WHERE
FND_USER.PERSON_PARTY_ID = p_tpc_id
AND ROWNUM=1;
UPDATE pon_bid_attribute_values pbav1
SET (pbav1.score, pbav1.weighted_score) =
(SELECT AVG(pbav2.score) score,
AVG(pbav2.score)*paa2.weight/paa2.attr_max_score weighted_score
FROM pon_bid_attribute_values pbav2,
pon_auction_attributes paa2
WHERE pbav2.auction_header_id = p_auction_header_id
AND pbav2.bid_number IN
(SELECT pbh2.bid_number
FROM pon_bid_headers pbh2, pon_bid_headers pbh3
WHERE NVL(pbh2.evaluation_flag, 'N') = 'Y'
AND pbh2.auction_header_id = p_auction_header_id
AND pbh3.auction_header_id = p_auction_header_id
AND pbh2.trading_partner_id = pbh3.trading_partner_id
AND pbh2.bid_status = 'ACTIVE'
AND pbh3.bid_number = pbav1.bid_number
)
AND pbav2.attribute_name = pbav1.attribute_name
AND pbav2.auction_line_number = -1
AND paa2.auction_header_id = p_auction_header_id
AND paa2.attribute_name = pbav2.attribute_name
AND paa2.internal_attr_flag = 'Y'
AND paa2.line_number = -1
GROUP BY paa2.weight,
paa2.attr_max_score
)
WHERE pbav1.auction_header_id = p_auction_header_id
AND pbav1.bid_number IN
(SELECT pbh1.bid_number
FROM pon_bid_headers pbh1
WHERE pbh1.auction_header_id = p_auction_header_id
AND NVL(pbh1.evaluation_flag, 'N') = 'N'
AND pbh1.bid_status = 'ACTIVE'
AND EXISTS (SELECT NULL
FROM pon_bid_headers pbh4
WHERE NVL(pbh4.evaluation_flag, 'N') = 'Y'
AND pbh4.auction_header_id = p_auction_header_id
AND pbh4.trading_partner_id = pbh1.trading_partner_id
AND pbh4.bid_status = 'ACTIVE'
)
)
AND pbav1.attribute_name IN
(SELECT paa1.attribute_name
FROM pon_auction_attributes paa1
WHERE paa1.auction_header_id = p_auction_header_id
AND paa1.internal_attr_flag = 'Y'
AND paa1.line_number = -1
)
AND pbav1.auction_line_number = -1;