[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(bh.publish_date)
INTO l_graph_start_date
FROM pon_bid_headers bh
WHERE bh.auction_header_id = p_auction_header_id
AND bh.bid_status IN ('ACTIVE', 'ARCHIVED');
SELECT 'N'
INTO x_est_qty_available_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auction_header_id
AND al.group_type NOT IN ('GROUP','LOT_LINE')
AND al.order_type_lookup_code <> 'FIXED PRICE'
AND NVL(quantity, 0) = 0);
SELECT 'N'
INTO x_est_qty_available_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auction_header_id
AND al.line_number = p_auction_line_number
AND al.group_type NOT IN ('GROUP','LOT_LINE')
AND al.order_type_lookup_code <> 'FIXED PRICE'
AND NVL(quantity, 0) = 0);
SELECT bh.trading_partner_id,
bh.trading_partner_contact_id,
bh.vendor_site_id,
MAX(bid_number)
FROM pon_bid_headers bh
WHERE bh.auction_header_id = p_auction_header_id
GROUP BY
bh.trading_partner_id,
bh.trading_partner_contact_id,
bh.vendor_site_id;
UPDATE pon_bid_headers bh
SET bh.color_sequence_id = l_color_sequence_id,
bh.last_update_date = sysdate,
bh.last_updated_by = fnd_global.user_id
WHERE bh.trading_partner_id = c.trading_partner_id
AND bh.trading_partner_contact_id = c.trading_partner_contact_id
AND bh.vendor_site_id = c.vendor_site_id
AND bh.auction_header_id = p_auction_header_id;
UPDATE pon_auction_headers_all ah
SET ah.max_bid_color_sequence_id = l_color_sequence_id,
ah.last_update_date = sysdate,
ah.last_updated_by = fnd_global.user_id
WHERE ah.auction_header_id = p_auction_header_id;
l_insert_flag VARCHAR2(1);
SELECT last_activity_code
, rowid
FROM pon_supplier_activities
WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
AND trading_partner_id = p_trading_partner_id
AND trading_partner_contact_id = p_trading_partner_contact_id
AND last_action_flag = 'Y';
l_insert_flag := 'N';
UPDATE pon_supplier_activities
SET last_activity_time = l_sysdate
, last_update_date = l_sysdate
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE rowid = l_rowid;
UPDATE pon_supplier_activities
SET last_action_flag = 'N'
, last_update_date = l_sysdate
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE rowid = l_rowid;
l_insert_flag := 'Y';
l_insert_flag := 'Y';
IF (l_insert_flag = 'Y') THEN
l_progress := '006';
INSERT INTO pon_supplier_activities
( auction_header_id
, auction_header_id_orig_amend
, trading_partner_id
, trading_partner_contact_id
, session_id
, last_activity_code
, last_activity_time
, last_action_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, bid_number)
VALUES ( p_auction_header_id
, p_auction_header_id_orig_amend
, p_trading_partner_id
, p_trading_partner_contact_id
, p_session_id
, p_last_activity_code
, l_sysdate
, 'Y'
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
, p_bid_number);
PROCEDURE update_supplier_access(p_auction_header_id IN NUMBER
, p_auction_header_id_orig_amend IN NUMBER
, p_supplier_trading_partner_id IN NUMBER
, p_buyer_tp_contact_id IN NUMBER
, p_lock_status IN VARCHAR2
, p_lock_reason IN VARCHAR2
, x_record_status OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- {
l_api_name CONSTANT VARCHAR2(25) := 'update_supplier_access';
UPDATE pon_supplier_access
SET active_flag = 'N'
, last_update_date = l_sysdate
, last_updated_by = l_user_id
, last_update_login = l_login_id
WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
AND supplier_trading_partner_id = p_supplier_trading_partner_id;
INSERT INTO pon_supplier_access(auction_header_id_orig_amend
,supplier_trading_partner_id
,lock_date
,buyer_tp_contact_id
,lock_status
,active_flag
,auction_header_id
,lock_reason
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
VALUES (p_auction_header_id_orig_amend
,p_supplier_trading_partner_id
,l_sysdate
,p_buyer_tp_contact_id
,p_lock_status
,'Y'
,p_auction_header_id
,p_lock_reason
,l_sysdate
,l_user_id
,l_sysdate
,l_user_id
,l_login_id);
END update_supplier_access; --}
SELECT SUM(nvl(current_price, 0)
* decode (order_type_lookup_code, 'FIXED PRICE', 1, quantity)) AUCTION_VALUE,
SUM(decode(nvl(number_of_bids,0),0,1,0)) NO_BID_LINES,
SUM(decode(nvl(number_of_bids,0),0,
(nvl(current_price, 0) * decode(order_type_lookup_code, 'FIXED PRICE', 1, quantity)),0)) NO_BID_VALUE
INTO l_auction_value,
l_no_bid_lines,
l_no_bid_value
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE group_type in ('LOT', 'LINE', 'GROUP_LINE')
AND auction_header_id = P_AUCTION_ID;
SELECT
bl.line_number,
bl.price bid_price,
nvl(bl.quantity,nvl(ai.quantity,1)) bid_quantity,
ai.current_price,
nvl(ai.quantity,1) auction_qty
BULK COLLECT INTO l_line_number_tbl, l_bid_price_tbl, l_bid_qty_tbl, l_current_price_tbl, l_auction_qty_tbl
FROM pon_bid_headers bh,
pon_bid_item_prices bl,
pon_auction_headers_all ah,
pon_auction_item_prices_all ai
WHERE ah.auction_header_id = bh.auction_header_id
and bh.auction_header_id = bl.auction_header_id
and bh.bid_number = bl.bid_number
and bh.bid_status = 'ACTIVE'
and bh.auction_header_id = P_AUCTION_ID
and nvl(bh.SHORTLIST_FLAG, 'Y') = 'Y'
and ai.auction_header_id = ah.auction_header_id
and ai.line_number = bl.line_number
AND ai.group_type IN ('LOT', 'LINE', 'GROUP_LINE')
ORDER BY bl.line_number, decode(ah.bid_ranking, 'PRICE_ONLY', 1/bl.price, nvl(bl.total_weighted_score,0)/bl.price) desc ,bl.publish_date asc;