[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select auction_header_id
into x_auction_header_id
from pon_bid_headers
where bid_number = p_contracts_doc_id;
select DOCTYPE_GROUP_NAME
into x_doctype_grp_name
from pon_auc_doctypes
where DOCTYPE_ID = p_doc_type_id;
select DOCTYPE_GROUP_NAME
into x_doctype_grp_name
from pon_auc_doctypes
where DOCTYPE_ID = p_doc_type_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_document_id;
select
hrl.address_line_1 || ' ' || hrl.address_line_2 || ' ' || hrl.address_line_3 || ' ' || hrl.town_or_city || ' ',
hrl.region_1 || ' ' || hrl.region_2 || ' ' || hrl.region_3 || ' ' || hrl.postal_code || ' ' || nvl(ftl.territory_short_name, hrl.country)
into
v_address1,
v_address2
from
hr_locations_all hrl,
fnd_territories_tl ftl
where
hrl.location_id = p_location_id and
ftl.territory_code(+) = hrl.country and
ftl.territory_code(+) NOT IN ('ZR','FX','LX') and
ftl.language(+) = userenv('LANG');
select doctype_id, open_bidding_date, close_bidding_date, org_id
into l_doc_type_id, l_open_date, l_close_date, l_org_id
from pon_auction_headers_all
where auction_header_id = p_auction_id;
select bid_status
into l_new_bid_status
from pon_bid_headers
where bid_number = p_new_bid_number;
select bid_status into l_old_bid_status from pon_bid_headers where bid_number = p_old_bid_number;
-- bug 3608706 - new api to update the status history
OKC_MANAGE_DELIVERABLES_GRP.postDelStatusChanges (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => p_new_bid_number,
p_bus_doc_type => l_bus_doc_type,
p_bus_doc_version => -99,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
PROCEDURE : updateDeliverables PUBLIC
PARAMETERS:
p_auction_header_id IN NUMBER auction header id for negotiation
p_doc_type_id IN NUMBER doc type id for negotiation
p_close_bidding_date IN NUMBER new close bidding date for negotiation
x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
size is 250.
COMMENT : This procedure is to be called whenever there is a changed in close
bidding date of any negotiation.
==============================================================================================*/
PROCEDURE updateDeliverables (
p_auction_header_id IN NUMBER,
p_doc_type_id IN NUMBER,
p_close_bidding_date IN DATE,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_negotiation_doc_type VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'updateDeliverables';
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id in (select a.auction_header_id
from pon_auction_headers_all a,
pon_auction_headers_all b
where b.auction_header_id = p_auction_header_id
and b.auction_header_id_orig_amend = a.auction_header_id_orig_amend)
AND bid_status in ( 'ACTIVE', 'RESUBMISSION') ;
SELECT auction_header_id
FROM pon_auction_headers_all
WHERE auction_header_id in (select a.auction_header_id
from pon_auction_headers_all a,
pon_auction_headers_all b
where b.auction_header_id = p_auction_header_id
and b.auction_header_id_orig_amend = a.auction_header_id_orig_amend);
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => p_auction_header_id,
p_bus_doc_type => l_negotiation_doc_type,
p_bus_doc_version => -99,
p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
x_msg_data => l_msg_data,
x_msg_count => x_msg_count,
x_return_status => l_return_status
);
-- need to update deliverables on them as well
IF (current_amendment.auction_header_id <> p_auction_header_id) THEN
OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => current_amendment.auction_header_id,
p_bus_doc_type => l_negotiation_doc_type,
p_bus_doc_version => -99,
p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
x_msg_data => l_msg_data,
x_msg_count => x_msg_count,
x_return_status => l_return_status
);
OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => active_bid.bid_number,
p_bus_doc_type => l_response_doc_type,
p_bus_doc_version => -99,
p_bus_doc_date_events_tbl => l_bus_doc_dates_tbl,
x_msg_data => l_msg_data,
x_msg_count => x_msg_count,
x_return_status => l_return_status
);
END updateDeliverables;
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
and bid_status = 'ACTIVE';
select doctype_id into x_doctype_id
from pon_auction_headers_all
where auction_header_id=p_auction_header_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
PROCEDURE : Delete_Doc PUBLIC
PARAMETERS:
p_auction_header_id IN NUMBER auction header id for negotiation
p_doc_type_id IN NUMBER doc type id for negotiation
x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
size is 250.
COMMENT : This procedure is to be called whenever negotiation gets deleted. As
of now only draft negotiation is allowed to be deleted. Therefore this API
should only be called for draft negotiation deletion only.
=============================================================================================== */
PROCEDURE Delete_Doc (
p_auction_header_id IN NUMBER,
p_doc_type_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_negotiation_doc_type VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
OKC_TERMS_UTIL_GRP.Delete_Doc(
p_api_version => 1.0,
p_doc_id =>p_auction_header_id,
p_doc_type => l_negotiation_doc_type,
x_msg_data => l_msg_data,
x_msg_count => x_msg_count,
x_return_status => l_return_status
);
END Delete_Doc;
SELECT doctype_id, view_by_Date, open_bidding_date, close_bidding_date, org_id
INTO l_doc_type_id, l_view_by_date, l_open_date, l_close_bidding_date, l_org_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
select h.org_id
into l_org_id
from pon_auction_headers_all h,
pon_bid_headers b
where b.bid_number = p_source_bid_number
and h.auction_header_id = b.auction_header_id;
select b.doctype_id ,
a.org_id
into l_doc_type_id,
l_org_id
from pon_bid_headers b,
pon_auction_headers_all a
where b.bid_number = p_bid_number
and a.auction_header_id = b.auction_header_id;
SELECT bid_number
FROM pon_bid_headers
WHERE auction_header_id =p_auction_number
and bid_status = 'ACTIVE';
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_auction_number;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = v_doc_id;
PROCEDURE updateDelivOnVendorMerge
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(60) := 'updateDeliverablesOnVendorMerge';
OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_document_class => 'SOURCING',
p_from_external_party_id => p_from_vendor_id,
p_from_external_party_site_id => p_from_site_id,
p_to_external_party_id => p_to_vendor_id,
p_to_external_party_site_id => p_to_site_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status
);
message => 'UPDATE_DELIV_ON_VENDOR_MERGE_FAILED: '
|| 'p_from_external_party_id = ' || p_from_vendor_id
|| ' p_from_external_party_site_id=' || p_from_site_id
|| ' p_to_external_party_id=' || p_to_vendor_id
|| ' p_to_external_party_site_id=' || p_to_site_id);
END updateDelivOnVendorMerge;
PROCEDURE : updateDelivOnAmendment PUBLIC
PARAMETERS:
p_auction_header_id_orig IN NUMBER auction header id of the original amendment
p_auction_header_id_prev IN NUMBER auction header id of the previous amendment
p_doc_type_id IN NUMBER doc-type-id for the current negotiation
p_close_bidding_date IN DATE new close date for the new amendment
p_close_date_changed IN VARCHAR2 flag to indicate whether the close date was changed
x_result OUT NOCOPY VARCHAR2 result returned to called indicating SUCCESS or FAILURE
x_error_code OUT NOCOPY VARCHAR2 error code if x_result is FAILURE, NULL otherwise
x_error_message OUT NOCOPY VARCHAR2 error message if x_result is FAILURE, NULL otherwise
size is 250.
COMMENT : This procedure is to be called whenever amendment is getting published.
In OA Implementation, this should be called in beforeCommit method which publishes the negotiation.
=============================================================================================== */
PROCEDURE updateDelivOnAmendment (
p_auction_header_id_orig IN NUMBER,
p_auction_header_id_prev IN NUMBER,
p_doc_type_id IN NUMBER,
p_close_bidding_date IN DATE,
x_result OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
)
IS
l_old_close_date DATE;
l_api_name CONSTANT VARCHAR2(30) := 'updateDelivOnAmendment';
select close_bidding_date into l_old_close_date
from pon_auction_headers_all
where auction_header_id = p_auction_header_id_prev;
-- then we need to update all the deliverables in the new and old
-- amendments that are based upon the close date event
IF( p_close_bidding_date <> l_old_close_date) THEN
PON_CONTERMS_UTL_PVT.updateDeliverables(p_auction_header_id => p_auction_header_id_orig,
p_doc_type_id => p_doc_type_id,
p_close_bidding_date => p_close_bidding_date,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
x_error_code := 'UPDATE_DELIV_AMEND_FAILED';
x_error_message := 'Unable to update deliverables for auction ' || p_auction_header_id_orig;
x_error_code := 'UPDATE_DELIV_AMEND_FAILED_COMPLETELY - ' || SQLCODE;
END updateDelivOnAmendment;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_document_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_document_id;
select org_id
into l_org_id
from pon_auction_headers_all
where auction_header_id = p_document_id;