[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_line_attachments
(
p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
-- Determine which lines have attachments
CURSOR bid_lines_with_attachments IS
SELECT DISTINCT ad.pk3_value
FROM fnd_attached_documents ad
WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
AND ad.pk1_value = p_auc_header_id
AND ad.pk2_value = p_bid_number
AND ad.pk3_value IS NOT null
AND to_number(ad.pk3_value) BETWEEN p_batch_start AND p_batch_end;
-- Delete all line level attachments
FOR line IN bid_lines_with_attachments LOOP
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
(x_entity_name => 'PON_BID_ITEM_PRICES',
x_pk1_value => p_auc_header_id,
x_pk2_value => p_bid_number,
x_pk3_value => line.pk3_value,
x_delete_document_flag => 'Y');
END delete_line_attachments;
PROCEDURE delete_bid_payment_attachments
(
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
l_module_name VARCHAR2 (30);
SELECT pay.bid_payment_id, pay.bid_number, pay.bid_line_number
FROM fnd_attached_documents ad,
pon_bid_payments_shipments pay
WHERE ad.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS'
AND ad.pk1_value = pay.bid_number
AND ad.pk2_value = pay.bid_line_number
AND ad.pk3_value = pay.bid_payment_id
AND pay.bid_number = p_bid_number
AND pay.bid_line_number BETWEEN p_batch_start AND p_batch_end;
l_module_name := 'Delete_bid_Payment_Attachments';
-- Delete all attachments for the bid payments
FOR payment IN bid_payments_with_attachments LOOP
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 || l_module_name,
message => 'Deleting fnd attachments for bid payment id ' ||payment.bid_payment_id||'='|| l_module_name);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
(x_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
x_pk1_value => payment.bid_number,
x_pk2_value => payment.bid_line_number,
x_pk3_value => payment.bid_payment_id,
x_delete_document_flag => 'Y');
message => 'After Call FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS = ' || l_module_name);
END delete_bid_payment_attachments;
PROCEDURE delete_bid
(
p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE
) IS
l_max_line_number pon_bid_item_prices.line_number%TYPE;
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;
-- Delete header attributes here
DELETE FROM pon_bid_attribute_values
WHERE bid_number = p_bid_number
AND line_number = -1;
-- Delete price differentials
DELETE FROM pon_bid_price_differentials
WHERE bid_number = p_bid_number
AND line_number BETWEEN l_batch_start AND l_batch_end;
-- Delete attributes
DELETE FROM pon_bid_attribute_values
WHERE bid_number = p_bid_number
AND line_number BETWEEN l_batch_start AND l_batch_end;
-- Delete price elements
DELETE FROM pon_bid_price_elements
WHERE bid_number = p_bid_number
AND line_number BETWEEN l_batch_start AND l_batch_end;
-- Delete shipments
DELETE FROM pon_bid_shipments
WHERE bid_number = p_bid_number
AND line_number BETWEEN l_batch_start AND l_batch_end;
-- Delete payment attachments
delete_bid_payment_attachments
( p_bid_number,
l_batch_start,
l_batch_end);
-- Delete Payments
DELETE FROM pon_bid_payments_shipments
WHERE bid_number = p_bid_number
AND bid_line_number BETWEEN l_batch_start AND l_batch_end;
-- Delete line attachments
delete_line_attachments
(p_auc_header_id,
p_bid_number,
l_batch_start,
l_batch_end);
-- Delete lines
DELETE FROM pon_bid_item_prices
WHERE bid_number = p_bid_number
AND line_number BETWEEN l_batch_start AND l_batch_end;
END delete_bid;
SELECT 'Y'
INTO l_has_errors
FROM pon_interface_errors
WHERE request_id = p_request_id
AND rownum = 1;
SELECT count(request_id)
INTO l_error_count
FROM pon_interface_errors
WHERE request_id = p_request_id;
SELECT request_id
INTO l_request_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auc_header_id;
SELECT request_id
INTO l_request_id
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
SELECT '_' || dt.message_suffix
INTO l_suffix
FROM pon_auc_doctypes dt,
pon_auction_headers_all ah
WHERE dt.doctype_id = ah.doctype_id
AND ah.auction_header_id = p_auction_id
AND rownum =1;
* PROCEDURE: UPDATE_AUCTION_IMPORT_COLS PUBLIC
* PARAMETERS:
* p_auction_id IN auction-number
* p_request_id
* p_requested_by
* p_import_file_name
* p_request_date
* p_last_update_date
*
* COMMENT: THIS PROCEDURE SHOULD BE INVOKED WHEN A CONCURRENT REQUEST IS
* SUCCESSFULLY TRIGERRED FOR IMPORTING A SPEADSHEET IN THE FOLLOWING
* SCENARIOS -
* ** CREATE A SUPER-LARGE NEGOTIATION VIA SPREADSHEET IMPORT
* ** AWARD A SUPER-LARGE NEGOTIATION VIA SPREADSHEET IMPORT
*
* ====================================================================== */
PROCEDURE UPDATE_AUCTION_IMPORT_COLS(P_AUCTION_ID IN NUMBER,
P_REQUEST_ID IN NUMBER,
P_REQUESTED_BY IN NUMBER,
P_REQUEST_DATE IN DATE,
P_IMPORT_FILE IN VARCHAR2,
P_LAST_UPDATE_DATE IN DATE,
X_RESULT OUT NOCOPY VARCHAR2,
X_ERROR_CODE OUT NOCOPY VARCHAR2,
X_ERROR_MESG OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AUCTION_IMPORT_COLS';
l_last_update_date DATE;
|| p_last_update_date);
IF( IS_AUCTION_NOT_UPDATED( p_auction_id , p_last_update_date)) THEN
UPDATE PON_AUCTION_HEADERS_ALL
SET
REQUEST_ID = p_request_id,
REQUESTED_BY = p_requested_by,
REQUEST_DATE = p_request_date,
IMPORT_FILE_NAME= p_import_file,
LAST_UPDATE_DATE= sysdate
WHERE
AUCTION_HEADER_ID= p_auction_id;
X_ERROR_CODE := 'PON_AUCTION_UPDATED_ALREADY';
X_ERROR_MESG := 'Auction ' ||p_auction_id ||' has been updated in another session ' ;
|| p_last_update_date);
|| p_last_update_date);
X_ERROR_CODE := 'UPDATE_AUCTION_IMPORT_COLS_FAILED_COMPLETELY - ' || SQLCODE;
END UPDATE_AUCTION_IMPORT_COLS;
FUNCTION IS_AUCTION_NOT_UPDATED (p_auction_header_id IN NUMBER,
p_last_update_date IN DATE)
RETURN BOOLEAN IS
l_current_update_date DATE;
SELECT last_update_date
INTO l_current_update_date
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
IF (l_current_update_date = p_last_update_date) THEN
l_return_value := TRUE;
END IS_AUCTION_NOT_UPDATED;
Delete all the errors in the interface table that have
expiration date marked as less than the current date
*/
delete from pon_interface_errors
where trunc(expiration_date) <= trunc(sysdate);
Delete all the rows in the summary table used to store
auto-award recommendation - mapping to auctions that haven't
been updated in the past seven days (this is just a catch-all
to avoid this temporary table from growing too much)
please refer to bug 4947500 for further details
*/
delete from pon_auction_summary
where auction_id in (select auction_header_id
from pon_auction_headers_all
where last_update_date < sysdate - 7);
SELECT number_of_lines, trading_partner_id
INTO v_num_lines, v_tpid
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT to_number(preference_value)
INTO v_threshold
FROM PON_PARTY_PREFERENCES
WHERE party_id = v_tpid
AND app_short_name= 'PON'
AND preference_name='CONCURRENT_PROCESS_LINE_START';
SELECT large_neg_enabled_flag
INTO v_large_neg_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT nvl(complete_flag,'Y')
INTO v_complete_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auc_header_id;
SELECT REQUEST_ID INTO l_request_id
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_auc_header_id;
PROCEDURE delete_bid_by_header
(
p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
P_doc_type IN varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_conterms_exist_flag varchar2(1) := 'N';
-- Delete related records from tables for this bid
pon_large_auction_util_pkg.delete_bid( p_auc_header_id, p_bid_number);
-- Delete contract terms
select conterms_exist_flag into l_conterms_exist_flag
from pon_auction_headers_all
where auction_header_id = p_auc_header_id;
OKC_TERMS_UTIL_GRP.delete_doc (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_validate_commit => null,
p_validation_string => null,
p_doc_type => P_doc_type,
p_doc_id => p_bid_number );
-- Delete bid header attachment
PON_LARGE_AUCTION_UTIL_PKG.delete_bid_header_attachment
(
p_auc_header_id => p_auc_header_id,
p_bid_number => p_bid_number
);
-- Delete bid header
delete from pon_bid_headers where bid_number = p_bid_number;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'PON.PLSQL.PON_LARGE_AUCTION_UTIL_PKG.delete_bid_from_header', 'PON_FAIL_CALL_DEL_DOC');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'PON.PLSQL.PON_LARGE_AUCTION_UTIL_PKG.delete_bid_from_header', 'Others:' || substr(1, 255, sqlerrm) );
END delete_bid_by_header;
PROCEDURE delete_bid_header_attachment
(
p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE
) IS
BEGIN
-- Delete bid header attachment
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
(x_entity_name => 'PON_BID_HEADERS',
x_pk1_value => p_auc_header_id,
x_pk2_value => p_bid_number,
x_delete_document_flag => 'Y');