[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SAVEPOINT pon_before_insert;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
SELECT count(p_interface_id)
INTO l_number_of_lines
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id;
SELECT sob.currency_code, fc.precision INTO v_functional_currency_code, v_currency_precision
FROM gl_sets_of_books sob, financials_system_params_all fsp,fnd_currencies fc
WHERE nvl(fsp.org_id,-9999) = nvl(g_header_rec.org_id,-9999)
AND sob.set_of_books_id = fsp.set_of_books_id
AND sob.currency_code = fc.currency_code;
SELECT count(*) INTO v_amount_based_lines
FROM pon_auc_items_interface paii, po_line_types_b polt
WHERE paii.interface_auction_header_id = p_interface_id
AND paii.line_type_id = polt.line_type_id
AND polt.order_type_lookup_code = 'AMOUNT';
SELECT doctype_id, transaction_type
INTO v_doctype_id, v_transaction_type
FROM pon_auc_doctypes
WHERE internal_name = g_header_rec.neg_type;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'INSERT_PON_AUC_HEADERS';
'preparing to insert a row in the transaction table');
SELECT pon_auction_headers_all_s.NEXTVAL INTO x_document_number
FROM dual;
select fnd_user.employee_id
into v_auc_contact_id
from
fnd_user,
hz_relationships
where
fnd_user.user_id = fnd_global.user_id()
and hz_relationships.object_id = v_site_id
and hz_relationships.subject_id = fnd_user.person_party_id
and hz_relationships.relationship_type = 'POS_EMPLOYMENT'
and hz_relationships.relationship_code = 'EMPLOYEE_OF'
and hz_relationships.start_date <= SYSDATE
and hz_relationships.end_date >= SYSDATE
and nvl(fnd_user.end_date,sysdate) >= sysdate;
'Inserting row into PON_AUCTION_HEADERS_ALL with the following filed values -- ' ||
'auction_header_id (x_document_number) : '|| x_document_number ||
'; document_number (x_document_number) : ' || x_document_number ||
'; last_update_date : ' || sysdate ||
'; last_updated_by : ' || g_header_rec.user_id ||
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
SELECT STANDARD_FORM, DOCUMENT_FORMAT
INTO l_standard_form, l_document_format
FROM po_print_form_formats
WHERE document_type = 'PO_SOL_STD_FORM' AND default_flag = 'Y' ;
INSERT INTO pon_auction_headers_all
(auction_header_id,
document_number,
auction_header_id_orig_amend,
auction_header_id_orig_round,
amendment_number,
auction_status,
award_status,
auction_type,
contract_type,
trading_partner_name,
trading_partner_name_upper,
trading_partner_id,
language_code,
bid_visibility_code,
attachment_flag,
ship_to_location_id,
bill_to_location_id,
payment_terms_id,
freight_terms_code,
fob_code,
carrier_code,
note_to_bidders,
allow_other_bid_currency_flag,
rate_type,
po_agreed_amount,
po_min_rel_amount,
currency_code,
number_price_decimals,
global_agreement_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
auction_origination_code,
doctype_id,
org_id,
buyer_id,
manual_edit_flag,
source_doc_number,
source_doc_id,
source_doc_msg,
source_doc_line_msg,
source_doc_msg_app,
security_level_code,
share_award_decision,
approval_status,
po_style_id,
price_break_response,
attribute_line_number,
has_hdr_attr_flag,
has_items_flag,
complete_flag,
progress_payment_type,
price_tiers_indicator,
UDA_TEMPLATE_ID, -- uda_template_id
UDA_TEMPLATE_DATE,
SOLICITATION_TYPE,
REVISION,
standard_form, --Bug : 16690156
document_format --Bug : 16690156
)
VALUES
(x_document_number, -- auction_header_id
x_document_number, -- document_number
x_document_number, -- auction_header_id_orig_amend,
x_document_number, -- auction_header_id_orig_round,
0, -- amendment_number
'DRAFT', -- auction_status
'NO', -- award_status
v_transaction_type, -- auction_type
g_header_rec.contract_type, -- contract_type
v_site_name, -- trading_partner_name
Upper(v_site_name), -- trading_partner_name_upper
v_site_id, -- trading_partner_id
g_header_rec.language_code, -- language_code
'OPEN_BIDDING', -- bid_visibility_code
'N', -- attachment_flag
g_header_rec.ship_to_location_id, -- ship_to_location_id
g_header_rec.bill_to_location_id, -- bill_to_location_id
g_header_rec.payment_terms_id, -- payment_terms_id
g_header_rec.freight_terms_code, -- freight_terms_code
g_header_rec.fob_code, -- fob_code
g_header_rec.carrier_code, -- carrier_code
g_header_rec.note_to_bidders, -- note_to_bidders
Decode(v_set_as_bidders_curr, 'Y', Decode(v_blanket_bidders_curr,'Y','Y','N'), 'N'), -- allow_other_bid_currency_flag
Decode(v_blanket_bidders_curr,'Y',g_header_rec.rate_type, null), -- rate_type
round(g_header_rec.po_agreed_amount * v_bidders_currency_rate,v_currency_precision), -- po_agreed_amount
decode(g_header_rec.global_agreement_flag, 'Y', null, Round(g_header_rec.po_min_rel_amount * v_bidders_currency_rate,v_currency_precision)), -- po_min_release_amount
Decode (v_blanket_bidders_curr, 'Y',v_functional_currency_code,g_header_rec.currency_code), -- currency_code
10000, -- currency precision set to ANY
Nvl(g_header_rec.global_agreement_flag,'N'), -- global_agreement_flag
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_date
g_header_rec.origination_code, -- auction_origination_code
v_doctype_id, -- doctype_id
g_header_rec.org_id, -- org_id
g_header_rec.user_id, -- buyer_id
'N', -- manual_edit_flag
g_header_rec.source_doc_number, -- Source document number
g_header_rec.source_doc_id, -- Source doc id
g_header_rec.source_doc_msg, -- Source doc number message to be displayed
g_header_rec.source_doc_line_msg, -- Source doc line level message to be displayed
g_header_rec.source_doc_msg_app, -- 3 character message app name
'PUBLIC', -- Security level code
'N', -- Share Award Decision
'NOT_REQUIRED', -- Approval Status
g_header_rec.po_style_id, -- po style id
v_price_break_response, -- price_break_response,
-1, -- Attribute Line Number
'N', -- Flag to indicate if Header Attributes are present
decode(g_header_rec.contract_type,'CONTRACT','N','Y'), -- Has Items Flag
'N', --complete_flag
'NONE', --Progress_Payment_Type
v_price_tiers_indicator, --price_tiers_indicator
l_uda_template_id, -- uda_template_id
l_uda_template_date,
l_sol_type,
0,
l_standard_form, --Bug : 16690156
l_document_format --Bug : 16690156
);
/* Bug 9645160 - updated clm_document_number back in PON_AUCTION_HEADERS_ALL table. */
UPDATE pon_auction_headers_all
SET DOCUMENT_NUMBER = clm_doc_number
WHERE AUCTION_HEADER_ID = x_document_number;
select uda_template_id into l_src_template_id from po_headers_all where po_header_id = g_header_rec.source_doc_id;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
'Inserted recore into pon_auction_headers_all');
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
SELECT category_id
INTO v_att_category_id
FROM fnd_document_categories
WHERE upper(name) = 'VENDOR';
v_debug_status := 'UOM_SELECT';
SELECT preference_value
INTO v_uom_code
FROM pon_party_preferences
WHERE preference_name = 'AMOUNT_BASED_UOM'
AND app_short_name = 'PON'
AND party_id = v_site_id;
v_debug_status := 'UOM_SELECT_EACH';
SELECT uom_code
INTO v_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = 'Each';
UPDATE pon_auction_headers_all
SET price_tiers_indicator = 'PRICE_BREAKS'
Where exists (SELECT 'Y'
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
AND rownum=1)
AND auction_header_id = x_document_number;
SELECT nvl(max(interface_line_number),0) INTO l_max_line_number FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'INSERT_PON_AUC_ITEMS';
UPDATE pon_auc_items_interface paii
SET paii.order_type_lookup_code =
(SELECT polt.order_type_lookup_code
FROM po_line_types_b polt
WHERE paii.line_type_id = polt.line_type_id)
WHERE paii.interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end;
'inserting into pon_auction_item_prices_all for auction_header_id (x_document_number) : ' || x_document_number);
INSERT INTO pon_auction_item_prices_all
(auction_header_id,
line_number,
disp_line_number,
last_amendment_update,
modified_date,
item_description,
category_id,
category_name,
ip_category_id,
uom_code,
residual_quantity,
number_of_bids,
creation_date,
created_by,
last_update_date,
last_updated_by,
note_to_bidders,
has_attributes_flag,
org_id,
line_type_id,
order_type_lookup_code,
item_id,
item_number,
item_revision,
line_origination_code,
source_doc_id,
source_line_id,
source_doc_number,
source_line_number,
current_price,
quantity,
po_min_rel_amount,
price_break_type,
price_break_neg_flag,
has_shipments_flag,
has_quantity_tiers,
price_disabled_flag,
quantity_disabled_flag,
--ADDED FOR SERVICES PROCUREMENT PROJECT - additional 3 columns
job_id,
po_agreed_amount,
purchase_basis,
price_diff_shipment_number,
group_type,
document_disp_line_number,
sub_line_sequence_number,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
uda_template_id,
CLM_CONTRACT_TYPE, -- bug 9914034
CLM_COST_CONSTRAINT,
CLM_IDC_TYPE
)
SELECT x_document_number, -- auction_header_id,
interface_line_number, -- line_number
interface_line_number, -- disp_line_number
0, -- last_amendment_update
sysdate, -- modified_date
item_description, -- item_description
pon_auc_items_interface.category_id, -- category_id
FND_FLEX_EXT.get_segs('INV', 'MCAT', mtl_categories_kfv.STRUCTURE_ID, mtl_categories_kfv.CATEGORY_ID), -- category_name from mtl_categories_kfv table
pon_auc_items_interface.ip_category_id, -- ip_category_id
Decode (order_type_lookup_code,'AMOUNT',v_uom_code, uom_code), -- uom_code
Decode (order_type_lookup_code,'AMOUNT',1, quantity), -- residual quantity
0, -- number_of_bids
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
note_to_bidders, -- note_to_bidders
'N', -- has_attribute_flag
org_id, -- org_id
line_type_id, -- line_type_id
order_type_lookup_code, -- order_type_lookup_code
item_id, -- item_id
item_number, -- item_number
item_revision, -- item_revision
origination_code, -- line_origination_code
source_doc_id, -- source_doc_id
source_line_id, -- source_line_id
source_doc_number, -- source_doc_number
source_line_number, -- source_line_number
--Decode (order_type_lookup_code, 'AMOUNT',round(quantity * v_bidders_currency_rate,v_currency_precision),round(current_price * v_bidders_currency_rate, v_currency_precision)), -- current_price
decode(current_price, 0, to_number(null), current_price * v_bidders_currency_rate), -- current_price
Decode (order_type_lookup_code,'AMOUNT',1, quantity), -- quantity
round(po_min_rel_amount * v_bidders_currency_rate,v_currency_precision), -- po_min_rel_amount
Decode (order_type_lookup_code,'AMOUNT', 'NONE', 'FIXED PRICE', 'NONE', decode(price_break_type, null, 'NON-CUMULATIVE', 'NON CUMULATIVE', 'NON-CUMULATIVE', price_break_type)), -- price_break_type
'Y', -- price_break_neg_flag. Those pbs are from po, so should always be optional
'N', -- has_shipments_flag initially set to 'N'
'N', -- has_quantity_tiers initially set to 'N'
'N', -- price_disabled_flag initially set to 'N'
'N', -- quantity_disabled_flag initially set to 'N'
job_id, -- ADDED FOR SERVICES PROCUREMENT PROJECT - job id
po_agreed_amount, -- ADDED FOR SERVICES PROCUREMENT PROJECT - PO Agreed Amount,
purchase_basis, -- ADDED FOR SERVICES PROCUREMENT PROJECT - Purchase basis of line type
-1, -- Always set the price_diff_shipment_number to -1 for blankets
'LINE', -- Group Type
interface_line_number, -- document_disp_line_number
interface_line_number, -- sub_line_sequence_number
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
l_line_uda_temp_id,
CLM_CONTRACT_TYPE, -- bug 9914034
CLM_COST_CONSTRAINT,
CLM_IDC_TYPE
FROM pon_auc_items_interface,
mtl_categories_kfv
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end
AND mtl_categories_kfv.category_id (+) = pon_auc_items_interface.category_id;
UPDATE pon_auction_item_prices_all
SET has_shipments_flag = 'Y'
WHERE (line_number) IN
(SELECT interface_line_number
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end)
AND auction_header_id = x_document_number;
UPDATE pon_auction_item_prices_all
SET has_price_differentials_flag = 'Y',
differential_response_type = 'OPTIONAL'
WHERE (line_number) IN
(SELECT interface_line_number
FROM pon_price_differ_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end
AND interface_shipment_number = -1)
AND auction_header_id = x_document_number;
select interface_line_number bulk collect into l_lineno_list
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end;
select uda_template_id into l_src_template_id from po_lines_all where po_header_id = g_header_rec.source_doc_id and rownum<2;
select source_line_id into l_poline_id from pon_auc_items_interface where
interface_auction_header_id = p_interface_id and interface_line_number = l_lineno_list(i);
-- bug 9914034 update clm_unit_price and clm_amount columns
IF ( v_return_status <> 'E' ) THEN
SELECT order_type_lookup_code INTO v_order_type_lookup_code from pon_auc_items_interface where
interface_auction_header_id = p_interface_id and interface_line_number = l_lineno_list(i);
UPDATE pon_auction_item_prices_all paip
SET clm_amount =
(SELECT N_EXT_ATTR20
FROM pon_auction_item_prices_ext_b ext,po_uda_ag_template_usages usage
WHERE ext.auction_header_id = paip.auction_header_id
AND ext.line_number = paip.LINE_NUMBER
AND usage.TEMPLATE_ID = paip.UDA_TEMPLATE_ID
AND usage.ATTRIBUTE1 = paip.CLM_IDC_TYPE
AND usage.ATTRIBUTE2 = paip.CLM_CONTRACT_TYPE
AND ext.ATTR_GROUP_ID = usage.ATTRIBUTE_GROUP_ID
AND ROWNUM < 2)
WHERE auction_header_id = x_document_number
AND line_number = l_lineno_list(i);
UPDATE pon_auction_Item_prices_all
SET clm_unit_price = current_price,
clm_amount = (Nvl(current_price,0)*Nvl(quantity,0))
WHERE auction_header_id = x_document_number
AND line_number = l_lineno_list(i);
select line_location_id bulk collect into l_lineloc_list from po_line_locations_all where po_header_id = g_header_rec.source_doc_id and po_line_id = l_poline_id;
select uda_template_id into l_shp_template_id from po_line_locations_all where line_location_id = l_lineloc_list(j) and rownum<2;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
-- Insert price breaks information into the transaction table
v_debug_status := 'INSERT_SHIPMENTS';
INSERT INTO pon_auction_shipments_all
(auction_header_id,
line_number,
shipment_number,
shipment_type,
ship_to_organization_id,
ship_to_location_id,
quantity,
price,
effective_start_date,
effective_end_date,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
has_price_differentials_flag
)
SELECT
x_document_number, -- auction_header_id
interface_line_number, -- line_number
MIN(interface_ship_number), -- shipment_number
'PRICE BREAK', -- shipment_type
ship_to_organization_id, -- ship_to_organization_id
ship_to_location_id, -- ship_to_location_id
quantity, -- quantity
MIN(price * v_bidders_currency_rate), -- price
NULL, -- effective_start_date
NULL, -- effective_end_date
MIN(org_id), -- org_id
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
'N'
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end
GROUP BY interface_line_number,ship_to_organization_id, ship_to_location_id, quantity;
UPDATE pon_auction_shipments_all
SET has_price_differentials_flag = 'Y',
differential_response_type = 'OPTIONAL'
WHERE (shipment_number) IN
(SELECT interface_shipment_number
FROM pon_price_differ_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_shipment_number <> -1)
AND auction_header_id = x_document_number;
v_debug_status := 'INSERT_PRICE_DIFFERENTIALS_ITEM';
'Inserting price differentials information into the transaction table at item level' );
INSERT INTO pon_price_differentials
(auction_header_id,
line_number,
shipment_number,
price_differential_number,
price_type,
multiplier,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
x_document_number, -- auction_header_id
interface_line_number, -- line_number
interface_shipment_number, -- shipment_number
interface_price_differ_number, -- price differentials number
price_type, -- price differential type
multiplier, -- multiplier
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id -- last_updated_by
FROM pon_price_differ_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end
AND interface_shipment_number = -1;
v_debug_status := 'INSERT_PRICE_DIFFERENTIALS_SHIP';
'Inserting price differentials information into the transaction table at shipment level' );
INSERT INTO pon_price_differentials
(auction_header_id,
line_number,
shipment_number,
price_differential_number,
price_type,
multiplier,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
x_document_number, -- auction_header_id
interface_line_number, -- line_number
interface_shipment_number, -- shipment_number
interface_price_differ_number, -- price differentials number
price_type, -- price differential type
multiplier, -- multiplier
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id -- last_updated_by
FROM pon_price_differ_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end
AND interface_shipment_number <> -1
AND interface_shipment_number IN (SELECT min(interface_ship_number)
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
GROUP BY interface_line_number,ship_to_organization_id, ship_to_location_id, quantity);
v_debug_status := 'INSERT_LINE_ATTACHMENTS';
INSERT INTO fnd_attached_documents
( attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
column1
)
SELECT fnd_attached_documents_s.nextval, -- attached_document_id
document_id, -- document_id,
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
NULL, -- last_update_login
seq_num, -- seq_num
'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
x_document_number, -- pk1_value
interface_line_number, -- pk2_value
NULL, -- pk3_value
NULL, -- pk4_value
NULL, -- pk5_value
'N', -- automatically_added_flag
NULL -- column1
FROM pon_attachments_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number IS NOT NULL
AND interface_line_number >= l_batch_start
AND interface_line_number <= l_batch_end;
UPDATE pon_auction_item_prices_all
SET attachment_flag = 'Y'
WHERE auction_header_id = x_document_number
AND line_number IN (SELECT to_number(pk2_value)
FROM fnd_attached_documents
WHERE entity_name = 'PON_AUCTION_ITEM_PRICES_ALL'
AND pk1_value = to_char(x_document_number))
AND line_number >= l_batch_start
AND line_number <= l_batch_end;
/* Bug : 16690958 : Update exhibit_number field for the lines */
IF PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(g_header_rec.org_id, v_doctype_id) = 'Y' THEN
if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
fnd_log.string(fnd_log.level_statement,
'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
' Before updating exhibit numbers');
UPDATE pon_auction_item_prices_all ai
SET exhibit_number =
(SELECT clm_exhibit_name FROM po_lines pl
WHERE pl.po_header_id = ai.source_doc_id
AND pl.po_line_id = ai.source_line_id)
WHERE ai.auction_header_id = x_document_number;
' Insert exhibit details into pon_auction_exhibit_details');
INSERT INTO pon_auction_exhibit_details
(AUCTION_HEADER_ID,
EXHIBIT_NUMBER,
IS_CDRL,
ASSOCIATED_TO_LINE,
EXHIBIT_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
(SELECT
x_document_number,
ped.exhibit_name,
Nvl(ped.is_cdrl, 'N'),
paip.line_number,
ped.exhibit_description,
SYSDATE,
paip.created_by,
SYSDATE,
paip.created_by
FROM pon_auction_item_prices_all paip,
po_exhibit_details_merge_v ped
WHERE paip.auction_header_id = x_document_number
AND ped.po_header_id = paip.source_doc_id
AND ped.reference_line_id = paip.source_line_id
AND ped.draft_id = -1);
v_debug_status := 'INSERT_HEADER_ATTACHMENTS';
INSERT INTO fnd_attached_documents
( attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
column1
)
SELECT
fnd_attached_documents_s.nextval, -- attached_document_id
document_id, -- document_id,
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
NULL, -- last_update_login
seq_num, -- seq_num
'PON_AUCTION_HEADERS_ALL', -- entity_name
x_document_number, -- pk1_value
NULL, -- pk2_value
NULL, -- pk3_value
NULL, -- pk4_value
NULL, -- pk5_value
'N', -- automatically_added_flag
NULL -- column1
FROM pon_attachments_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number IS NULL;
v_debug_status := 'INSERT_BIDDERS_CURR';
INSERT INTO pon_auction_currency_rates
( auction_header_id,
auction_currency_code,
bid_currency_code,
--rate,
--rate_dsp,
number_price_decimals,
sequence_number,
last_update_date,
last_updated_by,
creation_date,
created_by
) VALUES
( x_document_number, -- auction_header_id
v_functional_currency_code, -- auction_currency_code
g_header_rec.currency_code, -- bid_currency_code
--Decode(g_header_rec.rate_type,'User',g_header_rec.rate, NULL), -- rate
--Decode(g_header_rec.rate_type,'User',1/g_header_rec.rate, NULL), -- rate_dsp
10000, -- number_price_decimals set to ANY
10, -- sequence_number
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
Sysdate, -- creation_date
g_header_rec.user_id -- created_by
) ;
v_debug_status := 'INSERT_SUPPLIER_INFO';
'Addding the supplier information on the blanket to the PON_BIDDING_PARTIES; selectiong supplier_site_id' );
select supplier_site_id
into v_supplier_site_id
from pon_auc_headers_interface
WHERE interface_auction_header_id = p_interface_id;
select vendor_site_code
into v_supplier_site_code
from po_vendor_sites_all
where vendor_site_id = v_supplier_site_id ;
UPDATE pon_auc_headers_interface
SET process_status = 'REJECTED'
WHERE interface_auction_header_id = p_interface_id;
'Inserting record into PON_BIDDING_PARTIES' );
INSERT INTO pon_bidding_parties
(auction_header_id,
list_id,
sequence,
trading_partner_name,
trading_partner_id,
trading_partner_contact_id,
trading_partner_contact_name,
--bid_currency_code,
--number_price_decimals,
--rate,
--rate_dsp,
vendor_site_id,
vendor_site_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
access_type
)VALUES
(x_document_number, -- auction_header_id
-1, -- list id
10, -- sequence
v_trading_partner_name, -- trading_partner_name
v_trading_partner_id, -- trading_partner_id
v_trading_partner_contact_id, -- trading_partner_contact_id
v_trading_partner_contact_name, -- trading_partner_contact_name
--g_header_rec.currency_code, -- bid_currency_code
--10000, -- number_price_decimals,set to ANY
--Decode(g_header_rec.rate_type,'USER',g_header_rec.rate, NULL), -- rate
--Decode(g_header_rec.rate_type,'USER',1/g_header_rec.rate, NULL), -- rate_dsp
v_supplier_site_id, -- default vendor site id
v_supplier_site_code, -- default vendor site code
Sysdate, -- last_update_date
g_header_rec.user_id, -- last_updated_by
Sysdate, -- creation_date
g_header_rec.user_id, -- created_by
'FULL' -- access_type
);
update pon_auction_headers_all set
request_id = l_request_id,
number_of_lines = 0,
requested_by = g_header_rec.user_id ,
request_date = sysdate,
last_update_date = sysdate,
last_updated_by = g_header_rec.user_id ,
complete_flag = 'N'
where auction_header_id = x_document_number;
UPDATE pon_auc_headers_interface
SET process_status = 'ACCEPTED'
WHERE interface_auction_header_id = p_interface_id;
SELECT
COUNT(LINE_NUMBER) number_of_lines, MAX (DECODE (GROUP_TYPE, 'LOT_LINE', 0, 'GROUP_LINE', 0, SUB_LINE_SEQUENCE_NUMBER)) last_line_number
INTO l_number_of_lines, l_last_line_number
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = x_document_number;
UPDATE pon_auction_headers_all
SET complete_flag = 'Y', number_of_lines = l_number_of_lines, last_line_number = l_last_line_number
WHERE auction_header_id = x_document_number;
ROLLBACK TO SAVEPOINT pon_before_insert;
SELECT COUNT(*) INTO v_count_auc_headers_interface
FROM pon_auc_headers_interface
WHERE interface_auction_header_id = p_interface_id;
SELECT process_status INTO v_process_status
FROM pon_auc_headers_interface
WHERE interface_auction_header_id = p_interface_id;
SELECT MIN(interface_line_number) INTO v_invalid_item_recs
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id
AND org_id <> g_header_rec.org_id;
SELECT MIN(interface_line_number) INTO v_invalid_item_recs
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id
AND origination_code <> 'BLANKET';
SELECT MIN(interface_line_number) INTO v_invalid_item_recs
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id
AND decode(price_break_type, null, 'NONE', 'NON CUMULATIVE', 'NON-CUMULATIVE', price_break_type) NOT IN ('CUMULATIVE', 'NON-CUMULATIVE','NONE');
SELECT MIN (To_char(interface_line_number) || '-' || To_char(interface_ship_number))
INTO v_shipment_rec
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
AND org_id <> g_header_rec.org_id;
SELECT MIN(To_char(interface_line_number) || '-' || To_char(interface_ship_number))
INTO v_shipment_rec
FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id
AND shipment_type <> 'PRICE BREAK';
SELECT party_name INTO x_trading_partner_name
FROM hz_parties
WHERE party_id = x_trading_partner_id;
SELECT object_id INTO x_trading_partner_contact_id
FROM hz_relationships
WHERE subject_id = x_trading_partner_id
AND relationship_type = 'CONTACT'
AND relationship_code = 'CONTACT_OF'
AND start_date < Sysdate
AND Nvl(end_date, Sysdate+1) > Sysdate
AND status = 'A'
AND ROWNUM = 1;
SELECT user_name INTO x_trading_partner_contact_name
FROM fnd_user
WHERE person_party_id = x_trading_partner_contact_id
AND nvl(fnd_user.end_date,sysdate) >= sysdate;
SELECT user_name
INTO x_trading_partner_contact_name
FROM fnd_user
WHERE person_party_id = x_trading_partner_contact_id
AND nvl(end_date,sysdate) >= sysdate
AND ROWNUM = 1;
v_debug_status := 'DELETE_HEADER';
DELETE FROM pon_auc_headers_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'DELETE_ITEM';
DELETE FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'DELETE_ATTRIBUTES';
DELETE FROM pon_attributes_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'DELETE_SHIPMENTS';
DELETE FROM pon_auc_shipments_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := 'DELETE_PRICE_DIFFERENTIALS';
DELETE FROM pon_price_differ_interface
WHERE interface_auction_header_id = p_interface_id;
v_debug_status := ' DELETE_ATTACHMENTS';
DELETE FROM pon_attachments_interface
WHERE interface_auction_header_id = p_interface_id;
select
document_disp_line_number
into
x_display_line_number
from
pon_auction_item_prices_all
where
auction_header_id = p_auction_header_id and
line_number = p_auction_line_number;
select
line_num_display
into
x_display_line_number
from
pon_auction_item_prices_all
where
auction_header_id = p_auction_header_id and
line_number = p_auction_line_number;
SELECT interface_line_number
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id
AND interface_line_number >= p_from_line_number
AND interface_line_number <= p_to_line_number;
select nvl(ppp.preference_value,'GENERAL'),
flv.meaning
into v_default_attr_group,
v_attr_group_name
from pon_party_preferences ppp,
fnd_lookup_values flv
where ppp.app_short_name = 'PON' and
ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
ppp.party_id = (select trading_partner_id from pon_auction_headers_all where auction_header_id = p_document_number) and
flv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS' and
nvl(ppp.preference_value,'GENERAL') = flv.lookup_code and
flv.view_application_id = 0 and
flv.security_group_id = 0 and
flv.language = nvl(g_header_rec.language_code, userenv('LANG'));
INSERT INTO PON_AUCTION_ATTRIBUTES (
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL,
ATTR_GROUP,
SECTION_NAME,
ATTR_MAX_SCORE,
INTERNAL_ATTR_FLAG,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER,
MODIFIED_FLAG,
MODIFIED_DATE,
LAST_AMENDMENT_UPDATE,
IP_CATEGORY_ID,
IP_DESCRIPTOR_ID
)
SELECT
P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
line.interface_line_number, -- LINE_NUMBER
ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
null, -- DESCRIPTION
DATATYPE, -- DATATYPE
'N', -- MANDATORY_FLAG
VALUE, -- VALUE
null, -- DISPLAY_PROMPT
null, -- HELP_TEXT
'N', -- DISPLAY_TARGET_FLAG
SYSDATE, -- CREATION_DATE
g_header_rec.user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
g_header_rec.user_id, -- LAST_UPDATED_BY
-1, -- ATTRIBUTE_LIST_ID
'N', -- DISPLAY_ONLY_FLAG
(ROWNUM*10), -- SEQUENCE_NUMBER
null, -- COPIED_FROM_CAT_FLAG
null, -- WEIGHT
null, -- SCORING_TYPE
'LINE', -- ATTR_LEVEL
v_default_attr_group, -- ATTR_GROUP
v_attr_group_name, -- SECTION_NAME
null, -- ATTR_MAX_SCORE
'N', -- INTERNAL_ATTR_FLAG
10, -- ATTR_GROUP_SEQ_NUMBER
(ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
null, -- MODIFIED_FLAG
null, -- MODIFIED_DATE
null, -- LAST_AMENDMENT_UPDATE
IP_CATEGORY_ID, -- IP_CATEGORY_ID
IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
FROM
(SELECT attribute_name, datatype, value, ip_category_id, ip_descriptor_id
FROM pon_attributes_interface
WHERE interface_auction_header_id = p_interface_id AND
interface_line_number = line.interface_line_number AND
((ip_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
(ip_category_id <> 0 and v_ip_attr_default_option in ('ALL', 'CATEGORY')))
ORDER BY nvl(interface_sequence_number, v_max_seq_number) asc);
SELECT * INTO g_header_rec
FROM pon_auc_headers_interface
WHERE interface_auction_header_id = p_interface_id;
update pon_auction_headers_all
set request_id = null
where auction_header_id = p_auction_header_id;
insert into pon_interface_errors (
ERROR_MESSAGE_NAME,
request_id,
auction_header_id,
application_short_name,
token1_name,
token1_value,
token2_name,
token2_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
expiration_date
)
values(
'PON_RENEG_ERROR_MSG_'||l_message_suffix,
l_request_id,
p_auction_header_id,
'PON',
'DOC_NUM',
p_auction_header_id,
'REQUEST_ID',
l_request_id,
g_header_rec.user_id,
SYSDATE,
g_header_rec.user_id,
SYSDATE,
fnd_global.login_id,
sysdate + 7);
SELECT interface_line_number, source_line_id
FROM pon_auc_items_interface
WHERE interface_auction_header_id = p_interface_id;
UPDATE pon_auc_items_interface
SET group_line_id = oneline.interface_line_number
WHERE interface_auction_header_id = p_interface_id
AND group_line_id = oneline.source_line_id;
UPDATE pon_auc_items_interface
SET clm_base_line_num = oneline.interface_line_number
WHERE interface_auction_header_id = p_interface_id
AND clm_base_line_num = oneline.source_line_id;