[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*PROCEDURE insert_into_vhk_dummy(message VARCHAR2)
AS PRAGMA autonomous_transaction;
INSERT INTO vhk_dummy values(message);
SELECT Decode(Nvl(ALLOW_MULTIPLE_ACTIVE_OFFERS,'N'),'Y',
decode(p_buyer_user,'Y',
Decode(Nvl(two_part_flag,'N'),'Y','N','Y'),'Y'),'N')
INTO l_multi_resp_enabled
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT ah.auction_header_id,
Decode(ah.auction_header_id_orig_amend,l_orig_amend_id,'Y','N')
FROM pon_auction_headers_all ah,
pon_bid_headers bh
WHERE (ah.auction_header_id_orig_amend = l_orig_amend_id
OR ah.auction_header_id_orig_amend = l_prev_round_id)
AND ah.auction_header_id <> p_auc_header_id
AND EXISTS
(SELECT 1 FROM pon_bid_headers bh
WHERE bh.auction_header_id = ah.auction_header_id
AND Nvl(bh.surrog_bid_flag,'N') = p_buyer_user
AND bh.trading_partner_id = p_tpid
AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND nvl(bh.vendor_site_id, -1) = p_vensid
AND ((bh.bid_status = 'DRAFT' AND ah.auction_header_id_orig_amend = l_orig_amend_id)
OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
AND nvl(bh.evaluation_flag, 'N') = p_eval_flag)
ORDER BY ah.publish_date desc;
SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend
INTO l_orig_amend_id, l_prev_round_id
FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
WHERE ah.auction_header_id = p_auc_header_id
and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
SELECT Count(*)
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE
-- look at all amendments on the current round
(ah.auction_header_id_orig_amend = l_orig_amend_id
-- look at all amendments on the previous round
OR ah.auction_header_id_orig_amend = l_prev_round_id)
AND bh.auction_header_id = ah.auction_header_id
AND ( (Nvl(p_buyer_user,'N') = 'Y' AND nvl(bh.surrog_bid_flag,'N')='Y')
OR (Nvl(p_buyer_user,'N') = 'N' AND nvl(bh.surrog_bid_flag,'N')='N'))
AND bh.trading_partner_id = p_tpid
AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND nvl(bh.vendor_site_id, -1) = p_vensid
-- we ignore DRAFT bids on previous rounds
AND ((bh.bid_status = 'DRAFT'
AND ah.auction_header_id_orig_amend = l_orig_amend_id)
OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
AND nvl(bh.evaluation_flag, 'N') = p_eval_flag;
SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend,
nvl2(ah.contract_id, 'Y', 'N'), ah.org_id
INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply, l_org_id
FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
WHERE ah.auction_header_id = p_auc_header_id
and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
SELECT Count(*)
INTO l_count_org
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id ;
SELECT NVL(contract_status,'ACTIVE')
INTO l_org_contract_status
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id
AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
SELECT Count(*)
INTO l_count_org
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id ;
SELECT enabled_flag
INTO l_enabled_flag
FROM pon_contracts pc
WHERE NVL(org_id,-1) = l_org_id
AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
SELECT bh.old_bid_number
INTO x_prev_bid_number
FROM pon_bid_headers bh
WHERE bh.bid_number = p_draft_bid_num;
SELECT bh.bid_number, Decode(bh.bid_status,'ACTIVE','Y','N')
INTO x_prev_bid_number, x_rebid_flag
FROM pon_bid_headers bh
WHERE bh.bid_number = x_prev_bid_number;
SELECT ah.max_internal_line_num
INTO l_max_line_number
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
/*UPDATE pon_bid_item_prices bl
SET (bl.price, bl.proxy_bid_limit_price, bl.bid_currency_price,
bl.bid_currency_limit_price, bl.bid_currency_trans_price,
bl.unit_price, bl.bid_currency_unit_price, bl.copy_price_for_proxy_flag,
bl.old_price, bl.old_bid_currency_unit_price, bl.old_bid_currency_price,
bl.old_bid_currency_limit_price) =
(SELECT old_bl.price, old_bl.proxy_bid_limit_price, old_bl.bid_currency_price,
old_bl.bid_currency_limit_price, old_bl.bid_currency_trans_price,
old_bl.unit_price, old_bl.bid_currency_unit_price,
decode(sign(old_bl.proxy_bid_limit_price - old_bl.price),
0, 'N', 'Y'),
old_bl.price, old_bl.bid_currency_unit_price, old_bl.bid_currency_price, old_bl.bid_currency_limit_price
FROM pon_bid_item_prices old_bl
WHERE old_bl.bid_number = x_prev_bid_number
AND old_bl.line_number = bl.line_number)
WHERE bl.bid_number = p_draft_bid_num
AND bl.copy_price_for_proxy_flag = 'Y'
AND bl.line_number BETWEEN l_batch_start AND l_batch_end; */
UPDATE pon_bid_item_prices bl
SET rank =
(SELECT old_bl.rank
FROM pon_bid_item_prices old_bl
WHERE old_bl.bid_number = x_prev_bid_number
AND old_bl.line_number = bl.line_number)
WHERE bl.bid_number = p_draft_bid_num
AND bl.line_number BETWEEN l_batch_start AND l_batch_end
AND bl.auction_line_number <> -1; --Donot update unsolicited lines.
SELECT bid_number
INTO p_draft_bid_num
FROM pon_bid_headers
WHERE auction_header_id = p_auc_header_id
AND old_bid_number = p_source_bid_num;
SELECT ah.auction_header_id_orig_amend
INTO l_orig_amend_id
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
SELECT
bh.bid_status,
decode(bh.bid_status, 'ACTIVE', decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
decode(ah.auction_header_id, p_auc_header_id, 'N',
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_withdrawn,
bh.withdraw_reason
INTO
l_bid_status,
x_rebid_flag,
x_round_bid_def,
x_amend_bid_def,
x_prev_bid_disq,
x_prev_bid_wthd,
x_withdraw_reason
FROM pon_bid_headers bh,
pon_auction_headers_all ah
WHERE bid_number = p_source_bid_num
AND ah.auction_header_id = bh.auction_header_id;
UPDATE pon_bid_headers
SET bid_status = 'ARCHIVED_DRAFT',
last_update_date = SYSDATE
WHERE bid_number = p_source_bid_num;
lock and open the draft for update.
If no draft exists, create a new draft by defaulting this bid.
*/
IF p_rev_bid_number IS NOT NULL THEN
--insert_into_vhk_dummy('p_rev_bid_number is not null, calling check_draft_exists');