[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_cache_rec; -- bug 6374353
select has_items_flag
into x_has_items_flag
from pon_auction_headers_all
where auction_header_id = p_auction_number;
select pbp.trading_partner_name,
pbp.trading_partner_id,
pbp.trading_partner_contact_id,
pbp.trading_partner_contact_name,
pbp.additional_contact_email,
pbp.wf_user_name,
pbp.registration_id,
pbp.vendor_site_id,
decode(pbp.vendor_site_code, '-1', null, pbp.vendor_site_code) vendor_site_code,
pbp.requested_supplier_id,
pbp.requested_supplier_name,
pbp.requested_supplier_contact_id,
pbp.requested_supp_contact_name,
pcr.email_address rs_contact_email
-- lxchen
from pon_bidding_parties pbp,
pos_contact_requests pcr
where pbp.auction_header_id = x_auction_header_id and
pbp.requested_supplier_contact_id = pcr.contact_request_id (+)
union
select distinct trading_partner_name,
trading_partner_id,
trading_partner_contact_id,
pon_locale_pkg.get_party_display_name(trading_partner_contact_id) trading_partner_contact_name,
null additional_contact_email,
null wf_user_name,
to_number(null) registration_id,
vendor_site_id,
decode(vendor_site_code, '-1', null, vendor_site_code) vendor_site_code,
null requested_supplier_id,
null requested_supplier_name,
null requested_supplier_contact_id,
null requested_supp_contact_name,
null rs_contact_email
from pon_bid_headers
where x_isAmendment = 'Y' and
auction_header_id in (select auction_header_id
from pon_auction_headers_all
where auction_header_id_orig_amend = (select auction_header_id_orig_amend
from pon_auction_headers_all
where auction_header_id = x_auction_header_id)) and
bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED', 'DRAFT') and
trading_partner_contact_id NOT IN
(SELECT nvl(trading_partner_contact_id, -1)
FROM pon_bidding_parties
WHERE auction_header_id = x_auction_header_id);
select auction_header_id_orig_amend, nvl(amendment_number,0), nvl(auction_round_number, 1), auction_type,
event_id, event_title, trading_partner_id,
trading_partner_contact_id, original_close_bidding_date, trading_partner_contact_name,
staggered_closing_interval
from pon_auction_headers_all
where auction_header_id = x_auction_header_id;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
select user_name,
person_party_id
into x_user_name,
x_contact_id
from fnd_user
where person_party_id = x_auction_contact_id
and nvl(end_date, sysdate+1) > sysdate;
select user_name,
person_party_id
into x_user_name,
x_contact_id
from fnd_user
where person_party_id = x_auction_contact_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select document_number
into x_orig_document_number
from pon_auction_headers_all
where auction_header_id = x_auction_header_id_orig_amend;
select registration_key
into x_registration_key
from fnd_registrations
where registration_id = bidder.registration_id;
update pon_bidding_parties
set wf_item_key = x_itemkey
where auction_header_id = x_auction_header_id and
((trading_partner_id = bidder.trading_partner_id and
vendor_site_id = bidder.vendor_site_id) or
requested_supplier_id = bidder.requested_supplier_id);
select trading_partner_contact_name
from pon_bid_headers
where auction_header_id = x_auction_header_id;
select document_number, auction_header_id_orig_amend, dt.doctype_group_name,
nvl(auction_round_number, 1), nvl(amendment_number, 0),
hz.person_first_name || ' ' || hz.person_last_name,
decode(sign(close_bidding_date - nvl(view_by_date, open_bidding_date) - 7), 1, nvl(view_by_date, open_bidding_date)+3,
decode(sign(close_bidding_date - nvl(view_by_date, open_bidding_date) - 1), 1, nvl(view_by_date, open_bidding_date) + 1,
nvl(view_by_date, open_bidding_date) + 1/24)) reminder_time
from pon_auction_headers_all auh, hz_parties hz, pon_auc_doctypes dt
where hz.party_id = auh.trading_partner_contact_id
and auh.doctype_id = dt.doctype_id
and auction_header_id = p_auction_header_id;
SELECT pon_auction_wf_s.nextval
INTO x_sequence
FROM dual;
select nvl(view_by_date,open_bidding_date)
into x_notification_date
from pon_auction_headers_all where auction_header_id = p_auction_header_id;
select 'Y' into x_new_bidders_flag from pon_bidding_parties
where auction_header_id = p_auction_header_id
and trading_partner_id is null group by auction_header_id;
select view_by_date
into x_preview_date
from pon_auction_headers_all where auction_header_id = p_auction_header_id;
select document_number
into x_orig_document_number
from pon_auction_headers_all
where auction_header_id = x_auction_header_id_orig_amend;
UPDATE pon_auction_headers_all set
wf_item_key = x_itemkey,
reminder_date = x_reminder_date
WHERE auction_header_id = p_auction_header_id;
SELECT pon_auction_wf_bid_s.nextval
INTO x_sequence
FROM dual;
select ah.document_number, ah.trading_partner_name, dt.doctype_group_name,
nvl(substr(bhz.attribute2,0,3),''),
bhz.person_first_name || ' ' || bhz.person_last_name,
bih.trading_partner_name, decode(bih.vendor_site_code, '-1', null, bih.vendor_site_code) vendor_site_code,
ah.view_by_date, ah.staggered_closing_interval
from hz_parties bhz, pon_bid_headers bih, pon_auction_headers_all ah, pon_auc_doctypes dt
where bhz.party_id = bih.trading_partner_contact_id
and bih.bid_number = p_bid_id
and ah.auction_header_id = bih.auction_header_id
and ah.doctype_id = dt.doctype_id;
SELECT pon_auction_wf_dqbid_s.nextval
INTO x_sequence
FROM dual;
select trading_partner_contact_id
into x_bidder_contact_id
from pon_bid_headers
where bid_number = p_bid_id ;
select open_bidding_date, close_bidding_date, trading_partner_contact_name
into x_startdate, x_enddate, x_auction_tp_contact_name
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select wf_role_name, decode(bid_visibility_code,'OPEN_BIDDING','N','SEALED_BIDDING','Y','N')
into x_role_name, x_sealed_flag
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT pon_auction_wf_rtbid_s.nextval
INTO x_sequence
FROM dual;
select bid_type, nvl(ah.auction_round_number, 1) auction_round_number, bih.note_to_supplier,
ah.document_number, dt.doctype_group_name, dt.doctype_id,
bhz.person_first_name || ' ' || bhz.person_last_name, ah.trading_partner_contact_name,
bih.trading_partner_name, decode(bih.vendor_site_code, '-1', null, bih.vendor_site_code) vendor_site_code,
view_by_date, nvl(ah.has_items_flag, 'Y'), ah.staggered_closing_interval,bih.award_status
from hz_parties bhz, pon_bid_headers bih, pon_auction_headers_all ah, pon_auc_doctypes dt
where bhz.party_id = bih.trading_partner_contact_id
and bih.bid_number = p_bid_id
and ah.auction_header_id = bih.auction_header_id
and ah.doctype_id = dt.doctype_id;
SELECT pon_auction_wf_acbid_s.nextval
INTO x_sequence
FROM dual;
select reason
into x_note_to_supplier
FROM pon_acceptances
WHERE auction_header_id = p_auction_header_id
and bid_number = p_bid_id
AND ACCEPTANCE_TYPE = 'REJECTED'
AND rownum = 1;
select count(pbip.line_number)
into x_number_awarded
from pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
where paip.auction_header_id = p_auction_header_id
and paip.line_number = pbip.line_number
and pbip.bid_number = p_bid_id
and nvl(pbip.award_status,'NONE')= 'AWARDED'
and paip.group_type in ('LINE', 'LOT', 'GROUP_LINE');
select count(pbip.line_number)
into x_number_rejected
from pon_bid_item_prices pbip,
pon_auction_item_prices_all paip
where paip.auction_header_id = p_auction_header_id
and paip.line_number = pbip.line_number
and pbip.bid_number = p_bid_id
and nvl(pbip.award_status,'NONE')= 'REJECTED'
and paip.group_type in ('LINE', 'LOT', 'GROUP_LINE');
select open_bidding_date, close_bidding_date, award_date, event_id
into x_auction_open_bidding_date, x_auction_close_bidding_date, x_award_date, x_event_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select trading_partner_contact_id
into x_bidder_contact_id
from pon_bid_headers
where bid_number = p_bid_id;
select wf_item_key into x_itemkey
from pon_auction_headers_all
where auction_header_id = (select auction_header_id_prev_round
from pon_auction_headers_all where auction_header_id = x_doc_header_id);
select auction_header_id_prev_amend
into x_prev_doc_header_id
from pon_auction_headers_all
where auction_header_id = x_doc_header_id;
select wf_item_key, nvl(amendment_number, 0)
into x_itemkey, x_prev_doc_amendment_number
from pon_auction_headers_all
where auction_header_id = x_prev_doc_header_id;
select activity_label
into x_current_activity
from wf_item_activity_statuses_v
where item_type = x_itemtype
AND item_key = x_itemkey
and activity_status_code = 'NOTIFIED';
select pbp.wf_item_key wf_item_key, act.activity_label activity_label
from pon_bidding_parties pbp,
wf_item_activity_statuses_v act
where pbp.auction_header_id = p_prev_doc_header_id and
act.item_type = x_itemtype and
act.item_key = pbp.wf_item_key and
act.activity_status_code = 'NOTIFIED';
x_deleted_contact_flag BOOLEAN;
SELECT fu.user_name user_name,
trading_partner_id party_id,
trading_partner_name party_name,
DECODE(vendor_site_code, '-1', NULL, vendor_site_code) vendor_site_code,
vendor_site_id
FROM pon_bidding_parties pbp,
fnd_user fu
WHERE auction_header_id = x_doc_header_id_prev_round
AND fu.person_party_id = pbp.trading_partner_contact_id
AND nvl(fu.end_date, sysdate+1) > sysdate
UNION
-- Then pick all the additional contacts from bidding parties table.
SELECT DISTINCT(wlur.user_name) user_name,
pbp.trading_partner_id party_id,
pbp.trading_partner_name party_name,
DECODE(pbp.vendor_site_code, '-1', NULL, pbp.vendor_site_code) vendor_site_code,
pbp.vendor_site_id
FROM pon_bidding_parties pbp,
wf_local_user_roles wlur
WHERE pbp.auction_header_id = x_doc_header_id_prev_round
AND wlur.role_name = x_role_name
AND wlur.user_name = pbp.wf_user_name
AND pbp.trading_partner_id is not null --leave out requested suppliers
UNION
-- Then pick all the suppliers who have bid.
-- The responses for negotiation may or may not be invited.
SELECT DISTINCT(wlur.user_name) user_name,
pbh.trading_partner_id party_id,
pbh.trading_partner_name party_name,
DECODE(pbh.vendor_site_code, '-1', NULL, pbh.vendor_site_code) vendor_site_code,
pbh.vendor_site_id
FROM wf_local_user_roles wlur,
pon_bid_headers pbh
WHERE wlur.role_name = x_role_name
AND x_doc_header_id_prev_round = pbh.auction_header_id
AND wlur.user_name = pbh.trading_partner_contact_name(+);
select trading_partner_id,vendor_site_id
from pon_bidding_parties pbp
where auction_header_id = x_doc_header_id;
select auction_header_id_prev_round, trading_partner_contact_name,
staggered_closing_interval
into x_doc_header_id_prev_round, x_auctioneer_user_name,
x_staggered_closing_interval
from pon_auction_headers_all where auction_header_id = x_doc_header_id;
select wf_role_name
into x_role_name
from pon_auction_headers_all
where auction_header_id = x_doc_header_id_prev_round;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
select language into x_language_code
from wf_users where name = x_bidder_username;
select party_name into x_bidder_name
from hz_parties where party_id = x_prev_trading_partner_id;
select party_name into x_bidder_name
from hz_parties where
party_id = (select person_party_id from fnd_user where user_name = prevBidder.user_name);
SELECT pon_auction_wf_role_s.nextval
INTO x_sequence
FROM dual;
UPDATE pon_auction_headers_all set
wf_role_name = x_role_name
WHERE auction_header_id = x_auction_header_id;
select user_name
from wf_user_roles
where role_name = x_prev_doc_role_name;
select wf_role_name
into x_prev_doc_role_name
from pon_auction_headers_all
where auction_header_id = (select auction_header_id_prev_amend
from pon_auction_headers_all
where auction_header_id = x_auction_header_id);
select pbp.trading_partner_contact_name,
pbp.trading_partner_contact_id,
pbp.trading_partner_name,
pbp.trading_partner_id,
pbp.additional_contact_email,
pbp.vendor_site_id,
pbp.requested_supplier_id,
pbp.requested_supplier_contact_id,
pcr.email_address rs_contact_email
from pon_bidding_parties pbp, pos_contact_requests pcr
where pbp.auction_header_id = x_auction_header_id
and pbp.requested_supplier_contact_id = pcr.contact_request_id (+);
select user_name
from fnd_user where person_party_id = (select trading_partner_contact_id
from pon_auction_headers_all
where auction_header_id = x_auction_header_id)
and nvl(end_date,sysdate+1) > sysdate
and rownum = 1;
select NLS_LANGUAGE into l_auctioneer_nls_language
from fnd_languages
where language_code = x_language_code;
select nls_territory into l_auctioneer_nls_territory
from fnd_territories
where territory_code = x_territory_code;
select user_name
into x_user_name
from fnd_user where person_party_id = x_person_party_id
and nvl(end_date, sysdate+1) > sysdate;
select user_name
into x_user_name
from fnd_user
where person_party_id = x_person_party_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select to_char(user_id)
into x_user_orig_system_id
from fnd_user
where user_name = x_user_name;
select count(*)
into x_bidder_count
from wf_local_user_roles
where role_name = x_role_name
and user_name = x_user_name;
select NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY
into x_contact_notif_pref, x_contact_lang, x_contact_territory
from wf_users where name = x_user_name;
select NLS_LANGUAGE into x_nls_language
from fnd_languages
where language_code = x_language_code;
select nls_territory into x_nls_territory
from fnd_territories
where territory_code = x_territory_code;
SELECT pon_auction_wf_bidder_s.nextval
INTO x_sequence
FROM dual;
UPDATE pon_bidding_parties set
wf_user_name = x_user_name
WHERE auction_header_id = x_auction_header_id
AND trading_partner_id = bidder.trading_partner_id
AND vendor_site_id = bidder.vendor_site_id;
SELECT pon_auction_wf_bidder_s.nextval
INTO x_sequence
FROM dual;
UPDATE pon_bidding_parties
set wf_user_name = x_user_name
WHERE auction_header_id = x_auction_header_id
AND requested_supplier_id = bidder.requested_supplier_id;
select wf_role_name
into x_role_name
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select user_name
into x_bidder_user_name
from fnd_user
where person_party_id= p_trading_partner_contact_id
and nvl(end_date, sysdate+1) > sysdate;
select user_name
into x_bidder_user_name
from fnd_user
where person_party_id = p_trading_partner_contact_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select 'Y' into x_flag
from pon_bidding_parties
where auction_header_id = x_auction_header_id
and trading_partner_contact_id = x_bidder_contact_id
group by trading_partner_contact_id;
select count(*)
into x_bidder_count
from wf_local_user_roles
where role_name = p_role_name
and user_name = p_user_name;
select wf_item_key, sysdate, cancel_date
into x_itemkey, x_now, x_cancel_date
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select activity_label
into x_current_activity
from wf_item_activity_statuses_v
where item_type = x_itemtype
AND item_key = x_itemkey
and activity_status_code = 'NOTIFIED';
select action_note
into x_cancel_reason
from pon_action_history
where object_id = p_auction_header_id
and object_type_code = 'PON_AUCTION'
and action_type = 'CANCEL';
select pah.close_bidding_date
into v_end_date
from pon_auction_headers_all pah
where pah.auction_header_id = p_auction_header_id;
select pah.auction_status, pah.creation_date, pah.close_bidding_date, nvl( pah.is_paused, 'N' ), nvl( pah.last_pause_date, sysdate )
, auction_header_id_orig_round, nvl(auction_round_number,0),
nvl(amendment_number,0)
into v_auction_status, v_creation_date, v_end_date, v_is_paused, v_last_pause_date
, v_auction_header_id_orig_round, v_auction_round_number,
v_amendment_number
from pon_auction_headers_all pah
where pah.auction_header_id = p_auction_header_id;
select auction_status, open_bidding_date, close_bidding_date, nvl( last_pause_date, sysdate ), nvl( is_paused, 'N' ), staggered_closing_interval
into v_auction_status, v_startdate, v_enddate, v_pausedate, v_ispaused, v_staggered_closing_interval
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select pah.auction_status, pah.open_bidding_date, nvl(paip.close_bidding_date, pah.close_bidding_date),
nvl( pah.last_pause_date, sysdate ), nvl( pah.is_paused, 'N' )
into v_auction_status, v_startdate, v_enddate, v_pausedate, v_ispaused
from pon_auction_headers_all pah, pon_auction_item_prices_all paip
where pah.auction_header_id = p_auction_header_id and
paip.auction_header_id = pah.auction_header_id and
paip.line_number = p_line_number;
update pon_auction_headers_all
set auction_status = 'OPEN_FOR_BIDDING',
auction_status_name = (select meaning from fnd_lookups
where lookup_type = 'PON_AUCTION_STATUS' and
lookup_code = 'OPEN_FOR_BIDDING')
where auction_header_id = x_auction_header_id;
update pon_auction_headers_all
set auction_status = 'CLOSED_FOR_BIDDING',
auction_status_name = (select meaning from fnd_lookups
where lookup_type = 'PON_AUCTION_STATUS' and
lookup_code = 'CLOSED_FOR_BIDDING')
where auction_header_id = x_auction_header_id;
select meaning into lookupMeaning
from fnd_lookup_values
where lookup_type = lookupType and
language = langCode and
lookup_code = lookupCode;
select sum(AWARD_QUANTITY * BID_CURRENCY_PRICE)
from pon_bid_item_prices
where bid_number = p_po_id
and award_status = 'AWARDED';
select 'Y'
into x_flag
from pon_auction_headers_all
where auction_header_id = p_auction_number
and event_id is not null;
select wf_item_key, sysdate
into x_itemkey, x_now
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select activity_label
into x_current_activity
from wf_item_activity_statuses_v
where item_type = x_itemtype
AND item_key = x_itemkey
and activity_status_code = 'NOTIFIED';
select wf_item_key, trading_partner_contact_id
into x_itemkey, x_contact_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select user_name
into x_user_name
from fnd_user
where person_party_id = x_contact_id
and nvl(end_date, sysdate+1) > sysdate;
select user_name
into x_user_name
from fnd_user
where person_party_id = x_contact_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select activity_label
into x_current_activity
from wf_item_activity_statuses_v
where item_type = x_itemtype
AND item_key = x_itemkey
and activity_status_code = 'NOTIFIED';
select wf_item_key, trading_partner_contact_id
into x_itemkey, x_contact_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select user_name
into x_user_name
from fnd_user
where person_party_id = x_contact_id
and nvl(end_date, sysdate+1) > sysdate;
select user_name
into x_user_name
from fnd_user
where person_party_id = x_contact_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select activity_label
into x_current_activity
from wf_item_activity_statuses_v
where item_type = x_itemtype
AND item_key = x_itemkey
and activity_status_code = 'NOTIFIED';
select event.event_title
into eventTitle
from pon_auction_headers_all ah,pon_auction_events event
where auction_header_id = p_auction_number
and ah.event_id=event.event_id;
MsgTokens.DELETE;
MsgTokenValues.DELETE;
MsgTokens.DELETE;
MsgTokenValues.DELETE;
MsgTokens.DELETE;
MsgTokenValues.DELETE;
MsgTokens.DELETE;
MsgTokenValues.DELETE;
MsgTokens.DELETE;
MsgTokenValues.DELETE;
SELECT message_suffix
INTO x_msg_suffix
FROM pon_auc_doctypes
WHERE doctype_group_name = x_doctype_group_name;
SELECT transaction_type
INTO x_trans_type
FROM pon_auc_doctypes
WHERE doctype_group_name = p_doctype_group_name;
message => 'g_original_lang_code is not null so selecting g_original_language from the DB ');
select nls_language
into g_original_language
from fnd_languages
where language_code = g_original_lang_code;
message => 'g_original_lang_code is : ' || g_original_lang_code || ' after selecting it from the DB ');
message => 'p_language_code is NULL so selecting it from fnd_language ');
select language_code
into x_language_code
from fnd_languages
where nls_language = p_language;
message => 'p_language is NULL so selecting it from fnd_languages');
select nls_language
into x_language
from fnd_languages
where language_code = x_language_code;
select wfu.user_name user_name, person_party_id
from wf_user_roles wfu, fnd_user fnd
where role_name = x_wf_role_name
and fnd.user_name (+) = wfu.user_name
AND wfu.user_name NOT IN (SELECT wf_user_name FROM pon_bidding_parties
WHERE auction_header_id = x_doc_number AND trading_partner_id IS NULL);
select trading_partner_name, decode(vendor_site_code, '-1', null, vendor_site_code) vendor_site_code, nvl(vendor_site_id, -1) vendor_site_id
from pon_bidding_parties
where trading_partner_contact_id = x_trading_partner_contact_id
and auction_header_id = x_doc_number
union
select trading_partner_name, decode(vendor_site_code, '-1', null, vendor_site_code) vendor_site_code, nvl(vendor_site_id, -1) vendor_site_id
from pon_bid_headers
where trading_partner_contact_id = x_trading_partner_contact_id
and auction_header_id = x_doc_number;
select wf_role_name, wf_item_key, original_close_bidding_date, event_id, trading_partner_contact_name,
staggered_closing_interval
into x_wf_role_name, x_wf_item_key, x_original_close_bidding_date, x_event_id, x_tp_contact_name,
x_staggered_closing_interval
from pon_auction_headers_all
where auction_header_id = x_doc_number;
select trading_partner_name, decode(vendor_site_code, '-1', null, vendor_site_code) vendor_site_code, nvl(vendor_site_id, -1) vendor_site_id
into x_bidder_tp_name, x_vendor_site_code, x_vendor_site_id
from pon_bidding_parties
where wf_user_name = bidder.user_name
and auction_header_id = x_doc_number;
select wf_item_key into p_itemkey
from pon_auction_headers_all
where auction_header_id = p_auction_id;
select dt.doctype_group_name
into x_doctype_group_name
from pon_auction_headers_all auh, pon_auc_doctypes dt
where auh.auction_header_id = x_auction_header_id
and auh.doctype_id = dt.doctype_id;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
select view_by_date
into x_preview_date
from pon_auction_headers_all where auction_header_id = x_auction_header_id;
select
trading_partner_contact_name,
trading_partner_contact_id,
trading_partner_name,
trading_partner_id,
wf_user_name,
additional_contact_email,
registration_id,
decode(pbp.vendor_site_code, '-1', null, pbp.vendor_site_code) vendor_site_code,
pbp.vendor_site_id,
pbp.requested_supplier_id,
pbp.requested_supplier_name,
pbp.requested_supplier_contact_id,
pbp.requested_supp_contact_name,
pcr.email_address rs_contact_email
from pon_bidding_parties pbp,
pos_contact_requests pcr
where auction_header_id = x_auction_header_id
and pbp.requested_supplier_contact_id = pcr.contact_request_id(+)
and wf_item_key IS NULL;
select auction_type, event_id, event_title, open_bidding_date, trading_partner_id,
staggered_closing_interval
from pon_auction_headers_all
where auction_header_id = x_auction_header_id;
select wf_item_key , trading_partner_contact_name into p_itemkey, x_auctioneer_user_name
from pon_auction_headers_all
where auction_header_id = x_auction_header_id;
select NLS_LANGUAGE into x_nls_language
from fnd_languages
where language_code = x_language_code;
select nls_territory into x_nls_territory
from fnd_territories
where territory_code = x_territory_code;
select wf_role_name, wf_item_key into x_role_name, p_itemkey
from pon_auction_headers_all
where auction_header_id = x_auction_header_id;
select count(*) into x_bidder_count
from wf_local_user_roles
where role_name = x_role_name
and user_name = x_user_name;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
SELECT pon_auction_wf_bidder_s.nextval
INTO x_sequence
FROM dual;
select count(*) into x_bidder_count
from wf_local_user_roles
where role_name = x_role_name
and user_name = bidder.wf_user_name;
select registration_key
into x_registration_key
from fnd_registrations
where registration_id = bidder.registration_id;
select NLS_LANGUAGE into x_nls_addnl_language
from fnd_languages
where language_code = x_language_code;
select nls_territory into x_nls_addnl_territory
from fnd_territories
where territory_code = x_territory_code;
UPDATE pon_bidding_parties set
wf_user_name = x_additional_user_name
WHERE auction_header_id = x_auction_header_id
AND trading_partner_id = bidder.trading_partner_id
AND vendor_site_id = bidder.vendor_site_id;
SELECT pon_auction_wf_bidder_s.nextval
INTO x_sequence
FROM dual;
SELECT count(*) INTO x_bidder_count
FROM wf_local_user_roles
WHERE role_name = x_role_name
AND user_name = bidder.wf_user_name;
UPDATE pon_bidding_parties SET
wf_user_name = x_rs_user_name
WHERE auction_header_id = x_auction_header_id
AND (trading_partner_id = bidder.trading_partner_id
or requested_supplier_id = bidder.requested_supplier_id)
AND vendor_site_id = bidder.vendor_site_id;
update pon_bidding_parties
set wf_item_key = t_itemkey
where auction_header_id = x_auction_header_id and
(trading_partner_id = bidder.trading_partner_id
or requested_supplier_id = bidder.requested_supplier_Id) and
vendor_site_id = bidder.vendor_site_id and
wf_item_key is null;
select wf_item_key into x_item_key
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select user_name
into x_user_name
from fnd_user
where person_party_id = p_user_id
and nvl(end_date,sysdate+1) > sysdate;
select user_name
into x_user_name
from fnd_user
where person_party_id = p_user_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select count(*) into x_count
from fnd_user where user_name = p_user_name;
select trading_partner_contact_id
into x_user_id
from pon_bidding_parties
where auction_header_id = p_doc_id
and wf_user_name = p_user_name;
select name
into x_timezone_desc
from fnd_timezones_tl tl, fnd_timezones_b b
where b.upgrade_tz_id = p_timezone_id
and b.timezone_code = tl.timezone_code
and tl.language = lang;
select user_id
into x_user_id
from fnd_user
where person_party_id = contact_id
and nvl(end_date, sysdate+1) > sysdate;
select user_id
into x_user_id
from fnd_user
where person_party_id = contact_id
and nvl(end_date, sysdate+1) > sysdate
and rownum=1;
select user_id
into x_user_id
from fnd_user
where person_party_id = contact_id
and rownum=1;
SELECT person_party_id
INTO x_contact_id
FROM fnd_user
WHERE user_name = contact_name;
select need_by_start_date,need_by_date
from pon_auction_item_prices_all
where auction_header_id = auctionID and
line_number = lineNumber;
select parameter_value into l_debug_level from pon_operator_parameters where parameter_name ='xmlDebugLevel' ;
select to_char(PON_PO_WF_ITEMKEY_S.NEXTVAL)
into l_seq from sys.dual;
select PON_PO_WF_ITEMKEY_S.nextval into l_wf_item_seq from dual;
SELECT close_bidding_date, reminder_date
INTO x_closedate, x_reminderdate
FROM pon_auction_headers_all
WHERE auction_header_id=x_this_auction_header_id;
PROCEDURE update_ack_to_YES ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
uncmode in varchar2,
resultout out NOCOPY varchar2)
IS
x_trading_partner_id NUMBER;
SELECT close_bidding_date,auction_status
INTO x_closedate, x_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id=x_doc_number;
IF (x_closedate>x_now AND x_auction_status<>'CANCELLED' AND x_auction_status<>'DELETED') then
UPDATE pon_bidding_parties
SET supp_acknowledgement='Y',
ack_note_to_auctioneer = x_note,
ack_partner_contact_id = trading_partner_contact_id,
acknowledgement_time = x_now
WHERE (trading_partner_id= x_trading_partner_id
or wf_user_name= x_wf_user_name )
and nvl(vendor_site_id, -1) = nvl(x_vendor_site_id , -1)
and auction_header_id=x_doc_number;
END update_ack_to_yes;
PROCEDURE UPDATE_ACK_TO_NO ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
uncmode in varchar2,
resultout out NOCOPY varchar2)
IS
x_trading_partner_id NUMBER;
SELECT close_bidding_date,auction_status
INTO x_closedate, x_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id=x_doc_number;
IF (x_closedate>x_now AND x_auction_status<>'CANCELLED' AND x_auction_status<>'DELETED') then
UPDATE pon_bidding_parties
SET supp_acknowledgement='N',
ack_note_to_auctioneer = x_note,
ack_partner_contact_id = trading_partner_contact_id,
acknowledgement_time = x_now
WHERE (trading_partner_id= x_trading_partner_id
or wf_user_name= x_wf_user_name )
and nvl(vendor_site_id,-1) = nvl(x_vendor_site_id,-1)
and auction_header_id=x_doc_number;
END update_ack_to_no;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
UPDATE pon_bidding_parties set
wf_item_key = x_itemkey
WHERE trading_partner_id = x_tp_id
AND auction_header_id = x_auction_header_id;
select
fu.user_name my_user_name,
pbd.trading_partner_id,
pbd.trading_partner_name my_user_display_name,
decode(pbd.vendor_site_code, '-1', null, pbd.vendor_site_code) vendor_site_code,
pbd.wf_user_name additional_name,
pbd.wf_item_key,
pbd.additional_contact_email,
pbd.registration_id,
pbd.trading_partner_contact_id, -- Bug 3824928 added
pbd.vendor_site_id
from pon_bidding_parties pbd,
fnd_user fu
WHERE pbd.trading_partner_contact_id=fu.person_party_id (+) AND -- Bug 3824928: added outer join
nvl(fu.end_date, sysdate+1) > sysdate AND -- Added for TCA project
pbd.auction_header_id=x_doc_number AND
nvl(pbd.supp_acknowledgement,'havenot')= 'havenot'
and pbd.trading_partner_id is not null;
SELECT wf_item_key
INTO x_wf_item_key
FROM pon_auction_headers_all
WHERE auction_header_id=x_doc_number;
select trading_partner_id, trading_partner_contact_name
into x_tp_id, x_auction_tp_contact_name
from pon_auction_headers_all
where auction_header_id = x_doc_number;
SELECT COUNT(bid_number)
INTO x_bids_by_company
FROM pon_bid_headers
WHERE auction_header_id=x_doc_number
AND trading_partner_id=bidder.trading_partner_id;
SELECT person_party_id
INTO x_person_party_id
FROM fnd_user
WHERE user_name = bidder.my_user_name;
SELECT pon_auction_wf_publish_s.nextval
INTO x_sequence
FROM dual;
select registration_key
into x_registration_key
from fnd_registrations
where registration_id = bidder.registration_id;
UPDATE pon_bidding_parties set
wf_item_key = x_itemkey
WHERE trading_partner_id = bidder.trading_partner_id
AND auction_header_id = x_doc_number
AND vendor_site_id = bidder.vendor_site_id; -- Supplier/supplier site combination can be different in 11.5.10
PROCEDURE : DELETE_NEGOTIATION_LINE_REF PUBLIC
PARAMETERS:
x_negotiation_id in auction header id
x_negotiation_line_num in negotiation line number
x_org_id in organization id
x x_error_code out internal code for error
COMMENT : delete negotiation line references
======================================================================*/
PROCEDURE DELETE_NEGOTIATION_LINE_REF (
x_negotiation_id in number,
x_negotiation_line_num in number,
x_org_id in number,
x_error_code out NOCOPY varchar2) is
BEGIN
-- first, remove records in pon_backing_requisitions
delete
from pon_backing_requisitions
where auction_header_id = x_negotiation_id
and line_number = x_negotiation_line_num;
PO_NEGOTIATIONS_SV1.DELETE_NEGOTIATION_REF(
X_NEGOTIATION_ID => x_negotiation_id,
X_NEGOTIATION_LINE_NUM => x_negotiation_line_num,
X_ERROR_CODE => x_error_code);
END DELETE_NEGOTIATION_LINE_REF;
PROCEDURE : DELETE_NEGOTIATION_REF PUBLIC
PARAMETERS:
x_negotiation_id in auction header id
x_error_code out internal code for error
COMMENT : delete negotiation references
======================================================================*/
PROCEDURE DELETE_NEGOTIATION_REF (
x_negotiation_id in number,
x_error_code out NOCOPY varchar2) is
BEGIN
PO_NEGOTIATIONS_SV1.DELETE_NEGOTIATION_REF(
X_NEGOTIATION_ID => x_negotiation_id,
X_NEGOTIATION_LINE_NUM => null,
X_ERROR_CODE => x_error_code);
END DELETE_NEGOTIATION_REF;
PO_NEGOTIATIONS_SV1.UPDATE_REQ_POOL(
X_NEGOTIATION_ID => x_negotiation_id,
X_NEGOTIATION_LINE_NUM => null,
X_FLAG_VALUE => 'Y',
X_ERROR_CODE => x_error_code);
PROCEDURE : UPDATE_NEGOTIATION_REF PUBLIC
PARAMETERS:
x_old_negotiation_id in old auction header id
x_old_negotiation_num in old auction display number
x_new_negotiation_id in new auction header id
x_new_negotiation_num in new auction display number
x_error_code out internal code for error
x_error_message out error message
COMMENT : update negotiation references
======================================================================*/
PROCEDURE UPDATE_NEGOTIATION_REF(
x_old_negotiation_id in number,
x_old_negotiation_num in varchar2,
x_new_negotiation_id in number,
x_new_negotiation_num in varchar2,
x_error_code out NOCOPY varchar2,
x_error_message out NOCOPY varchar2 ) is
l_line_number number;
CURSOR deletedItems IS
SELECT LINE_NUMBER
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE auction_header_id = x_old_negotiation_id and
line_number not in ( select line_number
from PON_AUCTION_ITEM_PRICES_ALL
where auction_header_id = x_new_negotiation_id);
PO_NEGOTIATIONS_SV1.UPDATE_NEGOTIATION_REF(
X_OLD_NEGOTIATION_ID => x_old_negotiation_id,
X_NEW_NEGOTIATION_ID => x_new_negotiation_id,
X_NEW_NEGOTIATION_NUM => x_new_negotiation_num,
X_ERROR_CODE => x_error_code);
OPEN deletedItems;
FETCH deletedItems INTO
l_line_number;
EXIT WHEN deletedItems%NOTFOUND OR (x_error_code <> FND_API.G_RET_STS_SUCCESS);
PO_NEGOTIATIONS_SV1.UPDATE_NEGOTIATION_LINE_REF(
P_API_VERSION => 1.0,
P_OLD_NEGOTIATION_ID => x_new_negotiation_id,
P_OLD_NEGOTIATION_LINE_NUM => l_line_number,
P_NEW_NEGOTIATION_ID => x_old_negotiation_id,
P_NEW_NEGOTIATION_LINE_NUM => l_line_number,
P_NEW_NEGOTIATION_NUM => x_old_negotiation_num,
X_RETURN_STATUS => x_error_code,
X_ERROR_MESSAGE => x_error_message);
PO_NEGOTIATIONS_SV1.UPDATE_REQ_POOL(
X_NEGOTIATION_ID => x_old_negotiation_id,
X_NEGOTIATION_LINE_NUM => null,
X_FLAG_VALUE => 'Y',
X_ERROR_CODE => x_error_code);
END UPDATE_NEGOTIATION_REF;
COMMENT : update negotiation references
======================================================================*/
PROCEDURE COPY_BACKING_REQ(x_old_negotiation_id in number,
x_new_negotiation_id in number,
x_error_code out NOCOPY varchar2) is
neg_item_count NUMBER;
SELECT LINE_NUMBER,
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,
REQUISITION_QUANTITY,
REQUISITION_NUMBER
FROM PON_BACKING_REQUISITIONS
WHERE auction_header_id = x_old_negotiation_id;
SELECT count(*)
INTO l_count
FROM PON_BACKING_REQUISITIONS
WHERE auction_header_id = x_new_negotiation_id and
line_number = l_line_number and
requisition_header_id = l_req_header_id and
requisition_line_id = l_req_line_id;
SELECT count(*)
INTO neg_item_count
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE auction_header_id = x_new_negotiation_id and
line_number = l_line_number;
INSERT INTO PON_BACKING_REQUISITIONS(
AUCTION_HEADER_ID,
LINE_NUMBER,
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,
REQUISITION_QUANTITY,
REQUISITION_NUMBER)
VALUES (
x_new_negotiation_id,
l_line_number,
l_req_header_id,
l_req_line_id,
l_req_quantity,
l_req_number);
PO_NEGOTIATIONS_SV1.UPDATE_REQ_POOL(
X_NEGOTIATION_ID => x_negotiation_id,
X_NEGOTIATION_LINE_NUM => x_negotiation_line_id,
X_FLAG_VALUE => 'Y',
X_ERROR_CODE => x_error_code);
SELECT org_id, contract_type
INTO v_org_id, v_contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_id;
select notification_id, ACTIVITY_STATUS
into l_notification_id, l_activity_status
from wf_item_activity_statuses
where item_key = p_wf_item_key
and item_type = 'PONPBLSH'
and notification_id is not null
and activity_status = 'NOTIFIED'
and rownum = 1;
select (sysdate - close_bidding_date) into p_time_remaining
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select application_id
into x_app_id
from fnd_application
where application_short_name = x_product_name ;
PROCEDURE : DELETE_NEGOTIATION_AMENDMENTS PUBLIC
PARAMETERS:
x_negotiation_id in auction header id
x_error_code out internal code for error
COMMENT : delete negotiation amendments created
======================================================================*/
PROCEDURE DELETE_NEGOTIATION_AMENDMENTS (
x_negotiation_id in number,
x_error_code out NOCOPY varchar2) is
BEGIN
x_error_code := 'SUCCESS';
UPDATE PON_AUCTION_HEADERS_ALL
SET AUCTION_STATUS = 'DELETED'
WHERE
AUCTION_HEADER_ID_ORIG_AMEND IS NOT NULL
AND AUCTION_HEADER_ID_ORIG_AMEND = x_negotiation_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET AUCTION_STATUS = 'DELETED'
WHERE
AUCTION_HEADER_ID = x_negotiation_id;
END DELETE_NEGOTIATION_AMENDMENTS;
select auction_status
into v_auction_status
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select auction_header_id_orig_amend
into v_auction_header_id_orig_amend
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select auction_header_id
into v_most_recent_amendment
from pon_auction_headers_all
where auction_header_id_orig_amend = v_auction_header_id_orig_amend and
auction_status <> 'AMENDED' and auction_status <> 'DRAFT' and
rownum = 1; -- rownum = 1 is a sanity check
select nvl(member_type,'X')
into v_member_type
from pon_neg_team_members
where auction_header_id =p_auction_header_id
and user_id = p_user_id;
select display_flag
into l_price_break_display_flag
from PON_AUC_DOCTYPE_RULES dr,
PON_AUC_BIZRULES b
where b.bizrule_id = dr.bizrule_id
and dr.doctype_id = p_doctype_id
and b.name = 'PRICE_BREAK';
select contract_type, doctype_id, price_break_response, po_style_id, template_id, org_id, price_tiers_indicator
into l_contract_type, l_doctype_id, l_price_break_response, l_po_style_id, l_template_id, l_org_id, l_price_tiers_indicator
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select display_flag
into l_price_break_display_flag
from PON_AUC_DOCTYPE_RULES dr,
PON_AUC_BIZRULES b
where b.bizrule_id = dr.bizrule_id
and dr.doctype_id = l_doctype_id
and b.name = 'PRICE_BREAK';
select price_break_type
into l_template_pb_type
from pon_auction_item_prices_all
where auction_header_id = l_template_id;
select decode(price_break_lookup_code, 'NON CUMULATIVE',
'NON-CUMULATIVE', price_break_lookup_code)
into l_po_pb_type
from po_system_parameters_all
where org_id = l_org_id;
SELECT 'N'
INTO x_req_backed
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 nvl(al.line_origination_code,'-9998') <> 'REQUISITION');
SELECT LANGUAGE_CODE
INTO l_lang_code
FROM FND_LANGUAGES
WHERE NLS_LANGUAGE = l_language_code;
select auh.DOCUMENT_NUMBER,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
hz.PARTY_NAME PREPARER_TP_NAME,
pntm.TASK_NAME,
pntm.TARGET_DATE TASK_TARGET_DATE,
pntm.LAST_NOTIFIED_DATE TASK_ASSIGNMENT_DATE,
auh.TRADING_PARTNER_CONTACT_NAME,
fu.person_party_id,
fu.user_name,
auh.open_auction_now_flag,
auh.publish_auction_now_flag
into l_doc_number,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_preparer_tp_name,
l_task_name,
l_task_target_date,
l_task_assignment_date,
l_auctioneer_user_name,
l_person_party_id,
l_user_name,
l_open_auction_now_flag,
l_publish_auction_now_flag
from pon_auction_headers_all auh,
hz_parties hz,
pon_neg_team_members pntm,
fnd_user fu,
pon_auc_doctypes pad
where auh.auction_header_id = p_auction_header_id
AND hz.party_id = auh.trading_partner_id
AND pntm.auction_header_id = auh.auction_header_id
AND pntm.user_id = p_user_id
AND fu.user_id = pntm.user_id
AND pad.doctype_id = auh.doctype_id;
SELECT pon_auction_wf_publish_s.nextval
INTO l_sequence
FROM dual;
select auh.DOCUMENT_NUMBER,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
auh.TRADING_PARTNER_CONTACT_NAME,
auh.TRADING_PARTNER_CONTACT_ID,
auh.TRADING_PARTNER_ID,
fu.user_id,
auh.open_auction_now_flag,
auh.publish_auction_now_flag
into l_doc_number,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_auctioneer_user_name,
l_tp_contact_id,
l_trading_partner_id,
l_tp_contact_usr_id,
l_open_auction_now_flag,
l_publish_auction_now_flag
from pon_auction_headers_all auh,
pon_auc_doctypes pad,
fnd_user fu
where auh.auction_header_id = p_auction_header_id
AND pad.doctype_id = auh.doctype_id
AND fu.person_party_id = auh.TRADING_PARTNER_CONTACT_ID
AND nvl(fu.end_date,sysdate+1) > sysdate;
select auh.DOCUMENT_NUMBER,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
auh.TRADING_PARTNER_CONTACT_NAME,
auh.TRADING_PARTNER_CONTACT_ID,
auh.TRADING_PARTNER_ID,
fu.user_id,
auh.open_auction_now_flag,
auh.publish_auction_now_flag
into l_doc_number,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_auctioneer_user_name,
l_tp_contact_id,
l_trading_partner_id,
l_tp_contact_usr_id,
l_open_auction_now_flag,
l_publish_auction_now_flag
from pon_auction_headers_all auh,
pon_auc_doctypes pad,
fnd_user fu
where auh.auction_header_id = p_auction_header_id
AND pad.doctype_id = auh.doctype_id
AND fu.person_party_id = auh.TRADING_PARTNER_CONTACT_ID
AND nvl(fu.end_date,sysdate+1) > sysdate
AND rownum=1;
SELECT LANGUAGE_CODE
INTO l_lang_code
FROM FND_LANGUAGES
WHERE NLS_LANGUAGE = l_language_code;
select hz.PARTY_NAME PREPARER_TP_NAME,
pntm.TASK_NAME,
pntm.TARGET_DATE TASK_TARGET_DATE,
pntm.COMPLETION_DATE TASK_COMPLETION_DATE,
fu.user_name
into l_preparer_tp_name,
l_task_name,
l_task_target_date,
l_task_completion_date,
l_user_name
from hz_parties hz,
pon_neg_team_members pntm,
fnd_user fu
where pntm.auction_header_id = p_auction_header_id
AND hz.party_id = l_trading_partner_id
AND pntm.user_id = p_user_id
AND fu.user_id = pntm.user_id;
SELECT pon_auction_wf_publish_s.nextval
INTO l_sequence
FROM dual;
select auh.DOCUMENT_NUMBER,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
auh.TRADING_PARTNER_CONTACT_NAME,
auh.TRADING_PARTNER_NAME PREPARER_TP_NAME,
auh.TRADING_PARTNER_CONTACT_ID,
auh.TRADING_PARTNER_ID,
fu.user_name,
decode(pbh.VENDOR_SITE_CODE, null, '', pbh.VENDOR_SITE_CODE) SUPPLIER_SITE_NAME,
pbh.TRADING_PARTNER_NAME BIDDER_TP_NAME,
pbh.PUBLISH_DATE BID_PUBLISH_DATE,
pbh.TRADING_PARTNER_CONTACT_ID BIDDER_TPC_ID,
auh.AUCTION_HEADER_ID
into l_doc_number,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_auctioneer_user_name,
l_preparer_tp_name,
l_tp_contact_id,
l_trading_partner_id,
l_bidder_user_name,
l_supplier_site_name,
l_bidder_tp_name,
l_bid_publish_date,
l_bidder_tpc_id,
l_auction_header_id
from pon_auction_headers_all auh,
pon_auc_doctypes pad,
pon_bid_headers pbh,
fnd_user fu
where pbh.bid_number = p_bid_number
AND pad.doctype_id = auh.doctype_id
AND auh.auction_header_id = pbh.auction_header_id
AND fu.person_party_id = pbh.trading_partner_contact_id
AND nvl(fu.end_date, sysdate+1) > sysdate;
select auh.DOCUMENT_NUMBER,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
auh.TRADING_PARTNER_CONTACT_NAME,
auh.TRADING_PARTNER_NAME PREPARER_TP_NAME,
auh.TRADING_PARTNER_CONTACT_ID,
auh.TRADING_PARTNER_ID,
fu.user_name,
decode(pbh.VENDOR_SITE_CODE, null, '', pbh.VENDOR_SITE_CODE) SUPPLIER_SITE_NAME,
pbh.TRADING_PARTNER_NAME BIDDER_TP_NAME,
pbh.PUBLISH_DATE BID_PUBLISH_DATE,
pbh.TRADING_PARTNER_CONTACT_ID BIDDER_TPC_ID,
auh.AUCTION_HEADER_ID
into l_doc_number,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_auctioneer_user_name,
l_preparer_tp_name,
l_tp_contact_id,
l_trading_partner_id,
l_bidder_user_name,
l_supplier_site_name,
l_bidder_tp_name,
l_bid_publish_date,
l_bidder_tpc_id,
l_auction_header_id
from pon_auction_headers_all auh,
pon_auc_doctypes pad,
pon_bid_headers pbh,
fnd_user fu
where pbh.bid_number = p_bid_number
AND pad.doctype_id = auh.doctype_id
AND auh.auction_header_id = pbh.auction_header_id
AND fu.person_party_id = pbh.trading_partner_contact_id
AND nvl(fu.end_date, sysdate+1) > sysdate
AND rownum = 1;
SELECT
hz.party_id TO_PARTY_ID,
PON_LOCALE_PKG.get_party_display_name(hz.party_id,2, userenv('LANG')) TO_PARTY_NAME,
hz.person_first_name FIRST_NAME,
hz.person_last_name LAST_NAME
FROM pon_neg_team_members pntm,
HZ_PARTIES hz,
fnd_user fu
WHERE hz.party_id=fu.person_party_id
AND nvl(fu.end_date,sysdate+1) > sysdate
AND fu.user_id=pntm.user_id
AND fu.person_party_id <> p_sender_id
AND pntm.auction_header_id = p_auction_header_id
UNION
SELECT
distinct pte.from_id TO_PARTY_ID,
PON_LOCALE_PKG.get_party_display_name(pte.from_id,2, userenv('LANG')) TO_PARTY_NAME,
pte.from_first_name FIRST_NAME, pte.from_last_name LAST_NAME
FROM pon_threads pt,
pon_thread_entries pte
WHERE pt.discussion_id = p_discussion_id
AND pt.discussion_id = pte.discussion_id
AND pt.thread_number = pte.thread_number
AND pte.from_id <> p_sender_id
AND pte.vendor_id is null;
SELECT DISTINCT
PBP.TRADING_PARTNER_CONTACT_ID TO_PARTY_ID,
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(PBP.TRADING_PARTNER_CONTACT_ID, 2 , USERENV('LANG'))||' - '||NVL(HZ.PARTY_NAME,'') TO_PARTY_NAME,
HZ1.PERSON_FIRST_NAME FIRST_NAME,
HZ1.PERSON_LAST_NAME LAST_NAME,
HZ.PARTY_ID COMPANY_ID,
HZ.PARTY_NAME COMPANY_NAME
FROM PON_BIDDING_PARTIES PBP,
HZ_PARTIES HZ,
HZ_PARTIES HZ1
WHERE PBP.AUCTION_HEADER_ID = p_auction_header_id
AND HZ.PARTY_ID=PBP.TRADING_PARTNER_ID
AND HZ1.PARTY_ID= PBP.TRADING_PARTNER_CONTACT_ID
UNION
SELECT DISTINCT
PBH.TRADING_PARTNER_CONTACT_ID TO_PARTY_ID,
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(PBH.TRADING_PARTNER_CONTACT_ID,2,USERENV('LANG'))||' - '||NVL(HZ.PARTY_NAME,'') TO_PARTY_NAME,
HZ1.PERSON_FIRST_NAME FIRST_NAME,
HZ1.PERSON_LAST_NAME LAST_NAME,
HZ.PARTY_ID COMPANY_ID,
HZ.PARTY_NAME COMPANY_NAME
FROM PON_BID_HEADERS PBH,
HZ_PARTIES HZ,
HZ_PARTIES HZ1
WHERE PBH.AUCTION_HEADER_ID = p_auction_header_id
AND HZ.PARTY_ID=PBH.TRADING_PARTNER_ID
AND HZ1.PARTY_ID=PBH.TRADING_PARTNER_CONTACT_ID
AND PBH.BID_STATUS NOT IN ('ARCHIVED','DISQUALIFIED')
UNION
SELECT DISTINCT
PTE.FROM_ID TO_PARTY_ID,
PON_LOCALE_PKG.GET_PARTY_DISPLAY_NAME(PTE.FROM_ID, 2,USERENV('LANG')) ||' - '||NVL(PTE.FROM_COMPANY_NAME,'') TO_PARTY_NAME,
PTE.FROM_FIRST_NAME FIRST_NAME,
PTE.FROM_LAST_NAME LAST_NAME,
PTE.FROM_COMPANY_ID COMPANY_ID,
PTE.FROM_COMPANY_NAME COMPANY_NAME
FROM PON_THREADS PT,
PON_THREAD_ENTRIES PTE,
PON_TE_RECIPIENTS PTR
WHERE PT.DISCUSSION_ID = p_discussion_id
AND PT.DISCUSSION_ID = PTE.DISCUSSION_ID
AND PT.THREAD_NUMBER = PTE.THREAD_NUMBER
AND PTE.ENTRY_ID = PTR.ENTRY_ID
AND PTR.TO_ID = p_auctioneer_tpc_id
AND PTE.VENDOR_ID IS NOT NULL;
SELECT
hz.party_id TO_PARTY_ID,
PON_LOCALE_PKG.get_party_display_name(hz.party_id,2, userenv('LANG')) TO_PARTY_NAME,
hz.person_first_name FIRST_NAME,
hz.person_last_name LAST_NAME
FROM pon_scoring_team_members pntm,
HZ_PARTIES hz,
fnd_user fu
WHERE hz.party_id=fu.person_party_id
AND fu.user_id=pntm.user_id
AND nvl(fu.end_date,sysdate+1) > sysdate
AND fu.person_party_id <> p_sender_id
AND pntm.auction_header_id = p_auction_header_id
AND pntm.team_id = p_team_id;
SELECT distinct
hz.party_id TO_PARTY_ID,
PON_LOCALE_PKG.get_party_display_name(hz.party_id,2, userenv('LANG')) TO_PARTY_NAME,
hz.person_first_name FIRST_NAME,
hz.person_last_name LAST_NAME
FROM pon_scoring_team_members pntm,
HZ_PARTIES hz,
fnd_user fu
WHERE hz.party_id=fu.person_party_id
AND fu.user_id=pntm.user_id
AND nvl(fu.end_date,sysdate+1) > sysdate
AND fu.person_party_id <> p_sender_id
AND pntm.auction_header_id = p_auction_header_id;
SELECT ptr.to_id TO_PARTY_ID,
PON_LOCALE_PKG.get_party_display_name(ptr.to_id,2, userenv('LANG')) TO_PARTY_NAME,
ptr.to_first_name FIRST_NAME,
ptr.to_last_name LAST_NAME
FROM pon_te_recipients ptr
WHERE ptr.entry_id = p_entryId
AND ptr.to_id <> p_sender_id;
SELECT
auh.DOCUMENT_NUMBER,
auh.AUCTION_HEADER_ID,
auh.OPEN_BIDDING_DATE,
auh.CLOSE_BIDDING_DATE,
auh.VIEW_BY_DATE,
pad.MESSAGE_SUFFIX,
auh.AUCTION_TITLE,
auh.TRADING_PARTNER_CONTACT_NAME,
auh.TRADING_PARTNER_CONTACT_ID,
auh.STAGGERED_CLOSING_INTERVAL,
decode(nvl(auh.bid_visibility_code,'N'),
'SEALED_AUCTION','Y',
'N'),
SYSDATE,
pt.SUBJECT,
pte.FROM_ID,
pte.FROM_COMPANY_ID,
pd.discussion_id,
open_auction_now_flag,
publish_auction_now_flag
INTO
l_doc_number,
l_auction_header_id,
l_auction_start_date,
l_auction_end_date,
l_preview_date,
l_msg_suffix,
l_auction_title,
l_auctioneer_user_name,
l_auctioneer_tpc_id,
l_staggered_cls_intrvl,
l_is_sealed_neg,
l_msg_sent_date,
l_subject,
l_from_id,
l_from_company_id,
l_discussion_id,
l_open_auction_now_flag ,
l_publish_auction_now_flag
FROM pon_auction_headers_all auh,
pon_auc_doctypes pad,
pon_thread_entries pte,
pon_threads pt,
pon_discussions pd
WHERE pte.entry_id = p_entryid
AND pd.discussion_id = pte.discussion_id
AND pt.discussion_id = pte.discussion_id
AND pt.thread_number = pte.thread_number
AND auh.auction_header_id = pd.pk1_value
AND pad.doctype_id = auh.doctype_id;
SELECT
USER_ID,
USER_NAME
INTO
l_user_id,
l_user_name
FROM FND_USER
WHERE PERSON_PARTY_ID = p_userPartyId
AND NVL(END_DATE, SYSDATE+1) > SYSDATE;
SELECT
USER_ID,
USER_NAME
INTO
l_user_id,
l_user_name
FROM FND_USER
WHERE PERSON_PARTY_ID = p_userPartyId
AND NVL(END_DATE, SYSDATE+1) > SYSDATE
AND ROWNUM=1;
SELECT
USER_NAME
INTO
l_sender_user
FROM FND_USER
WHERE PERSON_PARTY_ID = p_from_id
AND NVL(END_DATE, SYSDATE+1) > SYSDATE;
SELECT
USER_NAME
INTO
l_sender_user
FROM FND_USER
WHERE PERSON_PARTY_ID = p_from_id
AND NVL(END_DATE, SYSDATE+1) > SYSDATE
AND ROWNUM=1;
SELECT LANGUAGE_CODE
INTO l_lang_code
FROM FND_LANGUAGES
WHERE NLS_LANGUAGE = l_language;
select nvl(shopping_category_id, -2)
into v_ip_category_id
from icx_cat_purchasing_cat_map_v
where po_category_id = p_po_category_id and
rownum = 1;
select nvl(po_category_id, -2)
into v_po_category_id
from icx_cat_shopping_cat_map_v
where shopping_category_id = p_ip_category_id and
rownum = 1;
IF (p_publish_auction_now_flag = 'Y') THEN --if Immediately is selected
wf_engine.SetItemAttrDate (itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'PREVIEW_DATE',
avalue => null);
IF (p_open_auction_now_flag = 'Y') THEN --if Immediately is selected
wf_engine.SetItemAttrDate (itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'AUCTION_START_DATE',
avalue => null);
SELECT
org_id,
carrier_code,
decode(two_part_flag,'Y',decode(sealed_auction_status,'LOCKED',technical_unlock_tp_contact_id,sealed_unlock_tp_contact_id),sealed_unlock_tp_contact_id),
decode(two_part_flag,'Y',decode(sealed_auction_status,'ACTIVE', sealed_unseal_tp_contact_id,technical_unseal_tp_contact_id),sealed_unseal_tp_contact_id),
is_paused,
staggered_closing_interval,
trading_partner_contact_id
INTO
v_org_id,
v_carrier_code,
v_sealed_unlock_tp_contact_id,
v_sealed_unseal_tp_contact_id,
v_is_paused,
v_staggered_closing_interval,
v_trading_partner_contact_id
FROM
pon_auction_headers_all_v
WHERE
auction_header_id = p_auction_header_id;
SELECT
a.auction_header_id
INTO
v_temp
FROM
pon_auction_headers_all a,
pon_bid_headers b
WHERE
a.auction_header_id = b.auction_header_id
and a.auction_header_id = p_auction_header_id
and b.trading_partner_id = p_user_trading_partner_id
and b.bid_status = 'ACTIVE'
AND rownum = 1;
SELECT
ppbp.auction_header_id
INTO
v_temp
FROM
pon_bidding_parties ppbp
WHERE
ppbp.auction_header_id = p_auction_header_id
and ppbp.trading_partner_id = p_user_trading_partner_id
AND rownum = 1;
SELECT
DECODE(
(SELECT
count(distinct vendor_site_id)
FROM
pon_bid_headers ppbh
WHERE
ppbh.auction_header_id = p_auction_header_id
and ppbh.trading_partner_id = p_user_trading_partner_id
and ppbh.bid_status = 'ACTIVE'
),
(SELECT
count(pbp.auction_header_id)
FROM
pon_bidding_parties pbp
WHERE
pbp.auction_header_id = p_auction_header_id
and pbp.trading_partner_id = p_user_trading_partner_id
), 'Y', 'N')
INTO
x_all_site_bid_on
FROM
DUAL;
SELECT
DECODE( nvl(v_is_paused, 'N'), 'Y', 'Y', nvl2(v_staggered_closing_interval, 'S', 'N'))
INTO
x_is_paused
FROM
DUAL;
SELECT
ps.display_name,
pdsh.advances_flag,
pdsh.retainage_flag,
nvl2(pspay.pay_item_type , 'Y','N')
INTO
x_outcome_display,
x_advances_flag,
x_retainage_flag,
x_payment_rate_rype_enabled
FROM
po_all_doc_style_lines ps,
pon_auction_headers_all_v ah,
po_doc_style_headers pdsh,
po_style_enabled_pay_items pspay,
po_lookup_codes fl_pay_item
WHERE
ah.auction_header_id = p_auction_header_id
AND ah.po_style_id = ps.style_id(+)
AND ah.contract_type = ps.document_subtype(+)
AND USERENV('LANG') = ps.language(+)
AND ah.po_style_id = pdsh.style_id(+)
AND ah.po_style_id = pspay.style_id(+)
AND pspay.pay_item_type(+) ='RATE'
AND fl_pay_item.lookup_type(+) = 'PAYMENT TYPE'
AND fl_pay_item.lookup_code(+) = pspay.pay_item_type;
SELECT (close_bidding_date - decode(is_paused, 'Y', last_pause_date, sysdate)), auction_status
INTO v_time_remaining, v_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id
AND open_bidding_date < sysdate;
SELECT (close_bidding_date-decode(is_paused, 'Y', last_pause_date, sysdate)), nvl2(staggered_closing_interval, 'Y', 'N'),
-- if auto extend is enabled and auto extends all lines, 'N' else 'Y'
decode(auto_extend_flag, 'Y', decode(auto_extend_all_lines_flag, 'N', 'Y', 'N'), 'N')
INTO v_time_left, v_is_staggered, v_ext_all_lines
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
update_cache_rec;
update_cache_rec;
SELECT bid_number, trading_partner_contact_name, nvl(shortlist_flag, 'Y') as shortlist_flag
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND bid_status = 'ACTIVE';
SELECT auction_title, document_number, trading_partner_name
INTO l_auction_title, l_document_number, l_trading_partner_name
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT pon_auction_wf_dqbid_s.nextval
INTO x_item_key
FROM dual;
SELECT nvl(two_part_flag,'N'),
nvl(technical_lock_status,''),
nvl(technical_evaluation_status,''),
nvl(sealed_auction_status, ''),
decode(nvl(is_paused, 'N'), 'Y', 'NOTCLOSED',
decode(SIGN(close_bidding_date - sysdate) , -1, 'CLOSED', 'NOTCLOSED')), --auction status
trading_partner_id,
auction_status,
award_status
INTO
l_two_part_flag,
l_technical_lock_status,
l_technical_evaluation_status,
l_commercial_lock_status,
l_auction_status,
l_auction_trading_partner_id,
l_auction_status2,
l_award_status
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT fl.meaning,
nvl2(pav.sealed_auction_status,
' (' ||
(select meaning
from fnd_lookups
where lookup_type = 'PON_SEALED_AUCTION_STATUS'
and (
(nvl(pav.two_part_flag, 'N') <> 'Y' and lookup_code = pav.sealed_auction_status)
or lookup_code = pav.technical_lock_status))
||')', '')
INTO l_buyer_meaning, l_buyer_sealed_meaning
FROM fnd_lookups fl, pon_auction_headers_all_v pav
WHERE pav.auction_header_id = p_auction_header_id
AND fl.lookup_type = 'PON_AUCTION_STATUS'
AND fl.lookup_code = pav.negotiation_status;
SELECT fl.meaning, nvl2(pav.sealed_auction_status,
' (' || (select meaning
from fnd_lookups
where lookup_type = 'PON_SEALED_AUCTION_STATUS'
and (
(nvl(pav.two_part_flag, 'N') <> 'Y' and lookup_code = pav.sealed_auction_status)
or lookup_code = decode(pav.sealed_auction_status, 'UNLOCKED', pav.sealed_auction_status,pav.technical_lock_status)) )
||')', '')
INTO l_supp_meaning, l_supp_sealed_meaning
FROM fnd_lookups fl, pon_auction_headers_all_v pav
WHERE pav.auction_header_id = p_auction_header_id
AND fl.lookup_type = 'PON_AUCTION_STATUS'
AND fl.lookup_code = pav.suppl_negotiation_status;
SELECT (SELECT meaning from fnd_lookups
WHERE lookup_type = 'PON_SEALED_AUCTION_STATUS' and
lookup_code = nvl(technical_lock_status,'')), --technical status
(SELECT meaning from fnd_lookups
WHERE lookup_type = 'PON_SEALED_AUCTION_STATUS' and
lookup_code = nvl(sealed_auction_status,'')) --commercial status
INTO l_technical_lock_meaning, l_commercial_lock_meaning
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT doctype_id
INTO l_rfi_doctype_id
FROM PON_AUC_DOCTYPES
WHERE doctype_group_name = 'REQUEST_FOR_INFORMATION';
SELECT flv1.LANGUAGE, flv1.meaning AS technical_meaning, flv2.meaning AS commercial_meaning
BULK COLLECT INTO g_two_part_cache
FROM fnd_lookup_values flv1, fnd_lookup_values flv2
WHERE flv1.lookup_type='PON_TWO_PART_TYPE'
AND flv2.lookup_type=flv1.lookup_type
AND flv1.lookup_code='TECHNICAL'
AND flv2.lookup_code='COMMERCIAL'
AND flv1.LANGUAGE=flv2.LANGUAGE;
PROCEDURE update_cache_rec
AS
BEGIN
IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN --{
FND_LOG.string(log_level => FND_LOG.level_procedure,
module => g_module_prefix || 'update_cache_rec',
message => 'Entered procedure update_cache_rec');
module => g_module_prefix || 'update_cache_rec',
message => 'User session language is now ' || USERENV('LANG'));
module => g_module_prefix || 'update_cache_rec',
message => 'Exiting procedure update_cache_rec');
END update_cache_rec;