[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pb_delete CONSTANT NUMBER := 4;
g_pb_optional_updated CONSTANT NUMBER := 5;
SELECT nvl(ah.is_paused, 'N'), ah.last_pause_date
INTO l_is_paused, l_paused_date
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
UPDATE pon_bid_item_prices_interface bli
SET bli.line_number = g_closed_int
WHERE bli.batch_id = p_batch_id
AND bli.line_number <> g_error_int
AND bli.line_number <> g_skip_int
AND sysdate >
(SELECT decode(l_is_paused, 'N', al.close_bidding_date,
al.close_bidding_date + (sysdate - l_paused_date))
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = bli.auction_header_id
AND al.line_number = bli.line_number);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_id,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUCTS_AUCTION_LINE' || p_suffix),
p_batch_id,
bli.interface_line_id,
'PON_AUCTION_LINE_CLOSED' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
bli.document_disp_line_number,
'TXT',
bli.auction_header_id,
bli.bid_number,
bli.line_number,
g_exp_date
FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = p_batch_id
AND bli.line_number = g_closed_int);
UPDATE pon_bid_item_prices_interface bli
SET bli.line_number = g_skip_int
WHERE bli.batch_id = p_batch_id
AND EXISTS
(SELECT 'Y'
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
WHERE bl.bid_number = bli.bid_number
AND bl.line_number = bli.line_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND (
-- GROUPs ignored
al.group_type = 'GROUP'
-- clm change - info lines can be ignored
OR Nvl(al.clm_info_flag,'N') = 'Y'
-- proxy lines ignored
OR bl.copy_price_for_proxy_flag = 'Y'
-- empty lines ignored
OR (al.price_disabled_flag = 'Y'
OR bli.bid_currency_price IS null)
AND (al.quantity_disabled_flag = 'Y'
OR bli.quantity IS null
OR (p_full_qty = 'Y' OR al.group_type = 'LOT_LINE'
OR al.order_type_lookup_code = 'AMOUNT'))
AND bli.note_to_auction_owner IS null
AND bli.attachment_desc IS null
AND bli.attachment_url IS null
AND bli.promised_date IS null
AND bli.recoupment_rate_percent IS null
AND bli.bid_curr_advance_amount IS null
AND bli.bid_curr_max_retainage_amt IS null
AND bli.retainage_rate_percent IS null
AND bli.progress_pymt_rate_percent IS null
-- No price elements
AND(bl.display_price_factors_flag = 'N'
OR NOT EXISTS
(SELECT bpfi.price_element_type_id
FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = bli.batch_id
AND bpfi.interface_line_id = bli.interface_line_id
AND bpfi.bid_currency_value IS NOT null))
-- No price differentials
AND (al.has_price_differentials_flag = 'N'
OR NOT EXISTS
(SELECT bpdi.sequence_number
FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = bli.batch_id
AND bpdi.auction_line_number = bli.line_number
AND bpdi.multiplier IS NOT null))
-- No attributes
AND (al.has_attributes_flag = 'N'
OR NOT EXISTS -- no attributes
(SELECT bai.attribute_name
FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = bli.batch_id
AND bai.interface_line_id = bli.interface_line_id
AND bai.value IS NOT null))));
SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = p_auc_header_id;
SELECT
pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
, PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
UPDATE pon_bid_item_prices_interface bli
SET bli.line_number =
nvl((SELECT al.line_number
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = bli.auction_header_id
--AND al.document_disp_line_number = bli.document_disp_line_number),
AND Decode(Nvl(l_is_fed,'N'),'N',al.document_disp_line_number,al.line_num_display) = bli.document_disp_line_number),
g_error_int)
WHERE bli.batch_id = p_batch_id;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUCTS_AUCTION_LINE' || p_suffix),
p_batch_id,
bli.interface_line_id,
'PON_AUC_INVALID_LINE_NUMBER' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
bli.document_disp_line_number,
'TXT',
bli.auction_header_id,
bli.bid_number,
g_exp_date
FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = p_batch_id
AND bli.line_number = g_error_int);
-- Delete from attributes interface table
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND bai.interface_line_id in (
select bli.interface_line_id
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND (bli.line_number = g_error_int
OR bli.line_number = g_skip_int));
-- Delete from price elements interface table
DELETE FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = p_batch_id
AND bpfi.interface_line_id in (
select bli.interface_line_id
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND (bli.line_number = g_error_int
OR bli.line_number = g_skip_int));
-- Delete from price differentials interface table
DELETE FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = p_batch_id
AND bpdi.interface_line_id in (
select bli.interface_line_id
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND (bli.line_number = g_error_int
OR bli.line_number = g_skip_int));
-- Delete from lines interface table
DELETE FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = p_batch_id
AND (bli.line_number = g_error_int
OR bli.line_number = g_skip_int);
-- Update attributes' internal line numbers's
-- NOTE: we also update line_number for those attributes with
-- valid line_numbers
UPDATE pon_bid_attr_values_interface bai
SET bai.line_number =
(SELECT bli.line_number
FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = bai.batch_id
AND bli.interface_line_id = bai.interface_line_id)
WHERE bai.batch_id = p_batch_id;
-- Update price elements' internal line numbers's
-- NOTE: we also update line_number for those price elements with
-- valid line_numbers
UPDATE pon_bid_price_elements_int bpfi
SET bpfi.line_number =
(SELECT bli.line_number
FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = bpfi.batch_id
AND bli.interface_line_id = bpfi.interface_line_id)
WHERE bpfi.batch_id = p_batch_id;
-- Update price differentials' internal line numbers's
-- NOTE: we also update line_number for those price differentials with
-- valid line_numbers
UPDATE pon_bid_price_differ_int bpdi
SET bpdi.auction_line_number =
(SELECT bli.line_number
FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = bpdi.batch_id
AND bli.interface_line_id = bpdi.interface_line_id)
WHERE bpdi.batch_id = p_batch_id;
SELECT
bai.interface_line_id,
bai.line_number,
bai.attribute_name,
bai.datatype,
bai.value,
al.document_disp_line_number
BULK COLLECT INTO
l_int_lines,
l_line_numbers,
l_attr_names,
l_datatypes,
l_values,
l_disp_line_numbers
FROM pon_bid_attr_values_interface bai,
pon_auction_item_prices_all al
WHERE bai.batch_id = p_batch_id
AND al.auction_header_id = bai.auction_header_id
AND al.line_number = bai.line_number;
UPDATE pon_bid_attr_values_interface bai
SET bai.line_number = g_error_int
WHERE bai.batch_id = p_batch_id;
UPDATE pon_bid_attr_values_interface bai
SET bai.value = decode(l_datatypes(i),
'TXT', l_values(i),
'NUM', to_char(decode (l_has_profile_value_numeric, 'Y', to_number(replace(l_values(i), l_grouping_separator_character),
'FM9999999999999999999999999999999999999999999999D9999999999999999',
'NLS_NUMERIC_CHARACTERS=''' || l_numeric_characters || ''''),
decode(instr(l_values(i), l_decimal_separator_character),
0,
'FM999999999999999999999999999999999999999999999999999999999999999',
'FM9999999999999999999999999999999999999999999999D9999999999999999'),
'NLS_NUMERIC_CHARACTERS=''.,''', 'N', FND_NUMBER.canonical_to_number(l_values(i)))),
'DAT', to_char(to_date(l_values(i), l_date_format), 'DD-MM-RRRR'),
'URL', l_values(i)),
bai.line_number = l_line_numbers(i)
WHERE bai.batch_id = p_batch_id
AND bai.interface_line_id = l_int_lines(i)
AND bai.attribute_name = l_attr_names(i);
-- Insert errors for each erroneous attribute
FOR i IN 1..l_num_errors LOOP
l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE)
VALUES
('BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTR_VALUES',
p_batch_id,
l_int_lines(l_index),
'PON_AUC_ATTR_INVALID_TARGET' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
l_values(l_index),
'TXT',
p_auc_header_id,
p_bid_number,
l_line_numbers(l_index),
g_exp_date,
'LINENUMBER',
l_disp_line_numbers(l_index),
'ATTRIBUTENAME',
l_attr_names(l_index));
SELECT
paha.two_part_flag,
paha.technical_evaluation_status,
pbh.surrog_bid_flag
INTO
l_two_part_flag,
l_tech_evaluation_status,
l_surrogate_bid_flag
FROM pon_bid_headers pbh, pon_auction_headers_all paha
WHERE pbh.bid_number = p_bid_number
AND paha.auction_header_id = pbh.auction_header_id;
UPDATE pon_bid_attr_values_interface bai
SET bai.line_number =
nvl((SELECT ba.line_number
FROM pon_bid_attribute_values ba
WHERE ba.bid_number = bai.bid_number
AND ba.line_number = bai.line_number
AND ba.attribute_name = bai.attribute_name), g_error_int),
bai.datatype =
nvl((SELECT ba.datatype
FROM pon_bid_attribute_values ba
WHERE ba.bid_number = bai.bid_number
AND ba.line_number = bai.line_number
AND ba.attribute_name = bai.attribute_name), 'N/A'),
bai.sequence_number =
nvl((SELECT ba.sequence_number
FROM pon_bid_attribute_values ba
WHERE ba.bid_number = bai.bid_number
AND ba.line_number = bai.line_number
AND ba.attribute_name = bai.attribute_name), g_error_int)
WHERE bai.batch_id = p_batch_id;
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND (bai.bid_number, bai.line_number, bai.sequence_number) in
(
select bh.bid_number, bh.line_number, aa.sequence_number
from pon_auction_attributes aa
, pon_bid_item_prices bh
where bh.bid_number = bai.bid_number
and bh.line_number = bai.line_number
and aa.auction_header_id = bh.auction_header_id
and aa.line_number = bh.line_number
and aa.sequence_number = bai.sequence_number
and aa.display_only_flag = 'Y'
);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
get_message_1_token('PON_AUC_ATTRIBUTE_ATTRNAME',
'ATTRNAME', bai.attribute_name),
p_batch_id,
bai.interface_line_id,
'PON_INVALID_ATTR_NAME' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
bai.attribute_name,
'TXT',
bai.auction_header_id,
bai.bid_number,
bai.line_number,
g_exp_date
FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND bai.line_number = g_error_int);
-- Delete from attributes interface table
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND bai.line_number = g_error_int;
-- Delete from attributes interface table
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND bai.line_number = g_error_int;
-- Update price_element_type_id since the user specifies price
-- elements by name and not price_element_type_id
UPDATE pon_bid_price_elements_int bpfi
SET bpfi.price_element_type_id =
nvl((SELECT pft.price_element_type_id
FROM pon_price_element_types_tl pft
WHERE pft.name = bpfi.price_element_name
AND pft.language = userenv('LANG')), g_error_int)
WHERE bpfi.batch_id = p_batch_id;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUC_PRICE_ELEMENT'),
p_batch_id,
bli.interface_line_id,
'PON_AUC_NOT_ALL_BID_PE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'TXT',
bli.auction_header_id,
bli.bid_number,
bli.line_number,
g_exp_date
FROM pon_bid_item_prices_interface bli,
pon_bid_item_prices bip
WHERE bli.batch_id = p_batch_id
AND bli.bid_number = bip.bid_number
AND bli.line_number = bip.line_number
AND bip.display_price_factors_flag = 'Y'
AND EXISTS
(SELECT bpfi.price_element_name
FROM pon_bid_price_elements_int bpfi, pon_price_elements apf
WHERE apf.auction_header_id = p_auc_header_id
AND apf.line_number = bli.line_number
AND apf.pf_type = 'SUPPLIER'
AND bpfi.batch_id (+) = bli.batch_id
AND bpfi.line_number (+) = apf.line_number
AND bpfi.price_element_type_id (+) = apf.price_element_type_id
AND bpfi.price_element_type_id IS null
AND rownum = 1));
UPDATE pon_bid_price_elements_int bpfi
SET bpfi.price_element_type_id = g_error_int
WHERE bpfi.batch_id = p_batch_id
AND bpfi.price_element_type_id <> g_error_int
AND 'BUYER' =
(SELECT apf.pf_type
FROM pon_price_elements apf
WHERE apf.auction_header_id = bpfi.auction_header_id
AND apf.line_number = bpfi.line_number
AND apf.price_element_type_id = bpfi.price_element_type_id);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
get_message_1_token('PON_AUC_PRICE_ELEMENT_PENAME',
'PENAME', bpfi.price_element_name),
p_batch_id,
bpfi.interface_line_id,
'PON_AUC_INVALID_PRICE_NAME' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
bpfi.price_element_name,
'TXT',
bpfi.auction_header_id,
bpfi.bid_number,
bpfi.line_number,
g_exp_date
FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = p_batch_id
AND bpfi.price_element_type_id = g_error_int);
-- Delete from price elements interface table
DELETE FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = p_batch_id
AND bpfi.price_element_type_id = g_error_int;
-- Update sequence_number since it is internal and user specifies
-- price differentials by price differential name
-- price differentials on lines with differential_response_type
-- as DISPLAY_ONLY are marked as skipped so they will get purged
UPDATE pon_bid_price_differ_int bpdi
SET bpdi.sequence_number =
nvl((SELECT decode(al.differential_response_type, 'DISPLAY_ONLY',
g_skip_int, bpd.price_differential_number)
FROM pon_bid_price_differentials bpd, po_price_diff_lookups_v pdl,
pon_auction_item_prices_all al
WHERE pdl.price_differential_dsp = bpdi.price_type
AND bpd.bid_number = bpdi.bid_number
AND bpd.line_number = bpdi.auction_line_number
AND bpd.price_type = pdl.price_differential_type
AND al.auction_header_id = bpd.auction_header_id
AND al.line_number = bpd.line_number
AND al.price_diff_shipment_number = bpd.shipment_number), g_error_int)
WHERE bpdi.batch_id = p_batch_id;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
(SELECT
'BIDBYSPREADSHEET',
get_message_1_token('PON_PRICE_DIFF_TYPE_NAME',
'PDNAME', bpdi.price_type),
p_batch_id,
bpdi.interface_line_id,
'PON_INVALID_PRICE_DIFF_TYPE' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
bpdi.price_type,
'TXT',
bpdi.auction_header_id,
bpdi.bid_number,
bpdi.auction_line_number,
g_exp_date
FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = p_batch_id
AND bpdi.sequence_number = g_error_int);
-- Delete from price differentials interface table
DELETE FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = p_batch_id
AND bpdi.sequence_number = g_error_int;
update pon_bid_item_prices_interface p
set p.CLM_PROMISE_PERIOD_UOM_CODE = (SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CLM_PERIOD'
AND meaning = p.CLM_PROMISE_PERIOD_UOM);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
select 'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_CLM_PRM_PERIOD_UOM'),
'PON_BID_ITEM_PRICES',
p_batch_id,
p.INTERFACE_LINE_ID,
'PON_PRM_PERIOD_UOM_ERR',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p.CLM_PROMISE_PERIOD_UOM,
'TXT',
p_auc_header_id,
p_bid_number,
p.INTERFACE_LINE_ID,
g_exp_date
from pon_bid_item_prices_interface p
where p.CLM_PROMISE_PERIOD_UOM_CODE IS NULL
and p.CLM_PROMISE_PERIOD_UOM IS NOT NULL;
update pon_bid_item_prices_interface p
set p.CLM_PROMISE_POP_DUR_UOM_CODE = (SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CLM_PERIOD'
AND meaning = p.CLM_PROMISE_POP_DURATION_UOM);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE)
select 'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_CLM_PRM_POP_DUR_UOM'),
'PON_BID_ITEM_PRICES',
p_batch_id,
p.INTERFACE_LINE_ID,
'PON_PRM_POP_UOM_ERR',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p.CLM_PROMISE_POP_DURATION_UOM,
'TXT',
p_auc_header_id,
p_bid_number,
p.INTERFACE_LINE_ID,
g_exp_date
from pon_bid_item_prices_interface p
where p.CLM_PROMISE_POP_DUR_UOM_CODE IS NULL
and p.CLM_PROMISE_POP_DURATION_UOM IS NOT NULL;
UPDATE pon_bid_item_prices_interface
SET bid_currency_limit_price = null
WHERE batch_id = p_batch_id;
(SELECT bl.bid_number,
bl.line_number,
bl.display_price_factors_flag,
bl.copy_price_for_proxy_flag,
al.quantity,
al.quantity_disabled_flag,
al.price_disabled_flag,
al.group_type,
al.order_type_lookup_code
FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
WHERE bl.bid_number = p_bid_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number) lines
ON (bli.bid_number = lines.bid_number
AND bli.line_number = lines.line_number)
WHEN MATCHED THEN
UPDATE SET
-- price does not apply if item has price factors
-- or if price is disabled
bli.bid_currency_price =
decode(lines.display_price_factors_flag, 'Y', null,
decode(lines.price_disabled_flag, 'Y', null,
bli.bid_currency_price)),
-- quantity := auction quantity if full qty reqd, LOT_LINE
-- or AMOUNT/FIXED PRICE line (1/null)
-- NOTE: quantity := null if blanket
bli.quantity =
decode(p_blanket, 'Y', null,
decode(lines.quantity_disabled_flag, 'Y', null,
decode(p_full_qty, 'Y', lines.quantity,
decode(lines.group_type, 'LOT_LINE', lines.quantity,
decode(lines.order_type_lookup_code,
'AMOUNT', lines.quantity,
'FIXED PRICE', lines.quantity, bli.quantity))))),
-- null proxy fields for lot lines or if the auction is closed
-- proxy fields also do not apply if the line has price factors
-- and if an active proxy already exists
bli.bid_currency_limit_price =
decode(lines.group_type, 'LOT_LINE', null,
decode(p_auc_closed, 'Y', null,
decode(lines.display_price_factors_flag, 'Y', null,
decode(lines.copy_price_for_proxy_flag, 'Y', null,
bli.bid_currency_limit_price))));
p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
p_hdr_disp_pf IN VARCHAR2,
p_blanket IN VARCHAR2,
p_mas IN VARCHAR2,
p_progress_payment_type IN VARCHAR2,
p_spreadsheet_type IN VARCHAR2,
p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
p_price_tiers_indicator IN pon_auction_headers_all.PRICE_TIERS_INDICATOR%type
) IS
l_module CONSTANT VARCHAR2(32) := 'copy_interface_to_txn_tables';
-- Update lines table with values in the interface table
MERGE INTO pon_bid_item_prices bl
USING
(SELECT
pbip.bid_number,
pbip.line_number,
pbip.batch_id,
pbip.interface_line_id,
pbip.quantity,
pbip.bid_currency_price,
pbip.note_to_auction_owner,
pbip.promised_date,
pbip.promise_pop_start_date, -- CLM Bug : 10212430 : Merge POP Start date
pbip.promise_pop_end_date, -- CLM Bug : 10212430 : Merge POP End date
-- Event Based Delivery Fields
pbip.CLM_PROMISE_PERIOD,
pbip.CLM_PROMISE_PERIOD_UOM_CODE,
pbip.CLM_PROMISE_POP_DURATION,
pbip.CLM_PROMISE_POP_DUR_UOM_CODE,
pbip.bid_currency_limit_price,
pbip.po_bid_min_rel_amount,
pbip.bid_curr_advance_amount,
pbip.recoupment_rate_percent,
pbip.progress_pymt_rate_percent,
pbip.retainage_rate_percent,
pbip.bid_curr_max_retainage_amt,
pah.progress_pymt_negotiable_flag,
pah.advance_negotiable_flag,
pah.retainage_negotiable_flag,
pah.max_retainage_negotiable_flag,
pah.recoupment_negotiable_flag,
pbip.worksheet_name,
pbip.worksheet_sequence_number
FROM pon_bid_item_prices_interface pbip,
pon_auction_headers_all pah
WHERE batch_id = p_batch_id
AND pah.auction_header_id = pbip.auction_header_id) bli
ON (bl.bid_number = bli.bid_number
AND bl.line_number = bli.line_number
AND Nvl(bl.clm_info_flag,'N') = 'N')
WHEN MATCHED THEN
UPDATE SET
bl.batch_id = bli.batch_id,
bl.interface_line_id = bli.interface_line_id,
bl.quantity = bli.quantity,
-- NOTE: we copy into bid_currency_unit_price, NOT bid_currency_price
-- Later, the bid_currency_unit_price column is used to calculate
-- the other price columns since bid_currency_price doesn't always
-- have the same meaning for different price factors views
bl.bid_currency_unit_price = bli.bid_currency_price,
bl.note_to_auction_owner = bli.note_to_auction_owner,
bl.promised_date = bli.promised_date,
-- CLM Bug : 10212430 : Merge POP Start/End dates.
bl.promise_pop_start_date = bli.promise_pop_start_date,
bl.promise_pop_end_date = bli.promise_pop_end_date,
-- Event Based Delivery Fields
bl.CLM_PROMISE_PERIOD = bli.CLM_PROMISE_PERIOD,
bl.CLM_PROMISE_PERIOD_UOM = bli.CLM_PROMISE_PERIOD_UOM_CODE,
bl.CLM_PROMISE_POP_DURATION = bli.CLM_PROMISE_POP_DURATION,
bl.CLM_PROMISE_POP_DURATION_UOM = bli.CLM_PROMISE_POP_DUR_UOM_CODE,
bl.bid_currency_limit_price = bli.bid_currency_limit_price,
bl.po_bid_min_rel_amount = bli.po_bid_min_rel_amount,
bl.bid_curr_advance_amount = DECODE(bli.advance_negotiable_flag,'Y',bli.bid_curr_advance_amount,bl.bid_curr_advance_amount),
bl.recoupment_rate_percent = DECODE(bli.recoupment_negotiable_flag,'Y',bli.recoupment_rate_percent,bl.recoupment_rate_percent),
bl.progress_pymt_rate_percent = DECODE(bli.progress_pymt_negotiable_flag,'Y',bli.progress_pymt_rate_percent,bl.progress_pymt_rate_percent),
bl.retainage_rate_percent = DECODE(bli.retainage_negotiable_flag,'Y',bli.retainage_rate_percent,bl.retainage_rate_percent),
bl.bid_curr_max_retainage_amt = DECODE(bli.max_retainage_negotiable_flag,'Y',bli.bid_curr_max_retainage_amt,bl.bid_curr_max_retainage_amt),
bl.last_update_date = sysdate,
bl.last_updated_by = p_userid,
bl.worksheet_name = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_name,to_char(null)),
bl.worksheet_sequence_number = decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, bli.worksheet_sequence_number,to_number(null));
UPDATE pon_bid_item_prices bl
SET batch_id = p_batch_id
WHERE bl.bid_number = p_bid_number
AND (SELECT al.group_type
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number) = 'GROUP'
AND EXISTS
(SELECT bl2.line_number
FROM pon_bid_item_prices bl2, pon_auction_item_prices_all al2
WHERE bl2.bid_number = p_bid_number
AND al2.auction_header_id = bl2.auction_header_id
AND al2.line_number = bl2.line_number
AND al2.parent_line_number = bl.line_number
AND bl2.batch_id = p_batch_id);
-- Update requirements/attributes table from interface
MERGE INTO pon_bid_attribute_values ba
USING
(SELECT
auction_header_id,
bid_number,
line_number,
batch_id,
interface_line_id,
sequence_number,
value,
worksheet_name,
worksheet_sequence_number
FROM pon_bid_attr_values_interface
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND batch_id = p_batch_id) bai
ON (ba.auction_header_id = bai.auction_header_id
AND ba.bid_number = bai.bid_number
AND ba.line_number = bai.line_number
AND ba.sequence_number= bai.sequence_number)
WHEN MATCHED THEN
UPDATE SET
ba.batch_id = bai.batch_id,
ba.interface_line_id = bai.interface_line_id,
ba.value = bai.value,
ba.worksheet_name = bai.worksheet_name,
ba.worksheet_sequence_number = bai.worksheet_sequence_number,
ba.last_update_date = sysdate,
ba.last_updated_by = p_userid;
-- be updated in the bid attributes transaction table
IF (p_mas = 'Y' and p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_txt_upload_mode) THEN
-- Update promised_date
UPDATE pon_bid_attribute_values ba
SET value =
nvl((SELECT to_char(bl.promised_date, 'dd-mm-yyyy hh24:mi:ss')
FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
WHERE bl.bid_number = ba.bid_number
AND bl.line_number = ba.line_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND al.is_need_by_date_scored = 'Y'), ba.value)
WHERE ba.bid_number = p_bid_number
AND ba.sequence_number = -10;
-- Update quantity
UPDATE pon_bid_attribute_values ba
SET value =
nvl((SELECT to_char(bl.quantity)
FROM pon_bid_item_prices bl, pon_auction_item_prices_all al
WHERE bl.bid_number = ba.bid_number
AND bl.line_number = ba.line_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND al.is_quantity_scored = 'Y'), ba.value)
WHERE ba.bid_number = p_bid_number
AND ba.sequence_number = -20;
-- Update price elements transaction table from interface table
-- only set bid currency value. The auction currency value
-- will be recalculated later in
-- recalculate_auc_curr_prices
MERGE INTO pon_bid_price_elements bpf
USING
(SELECT
batch_id,
bid_number,
line_number,
price_element_type_id,
bid_currency_value,
interface_line_id
FROM pon_bid_price_elements_int
WHERE batch_id = p_batch_id
AND auction_header_id = p_auction_header_id
AND bid_number = p_bid_number) bpfi
ON (bpf.bid_number = bpfi.bid_number
AND bpf.line_number = bpfi.line_number
AND bpf.price_element_type_id = bpfi.price_element_type_id)
WHEN MATCHED THEN
UPDATE SET
bpf.batch_id = bpfi.batch_id,
bpf.bid_currency_value = bpfi.bid_currency_value,
bpf.last_update_date = sysdate,
bpf.last_updated_by = p_userid,
bpf.interface_line_id = bpfi.interface_line_id;
UPDATE pon_bid_item_prices bl
SET bl.bid_currency_unit_price =
(SELECT bpf.bid_currency_value
FROM pon_bid_price_elements bpf
WHERE bpf.bid_number = bl.bid_number
AND bpf.line_number = bl.line_number
AND bpf.price_element_type_id = -10)
WHERE bl.bid_number = p_bid_number
AND bl.display_price_factors_flag = 'Y'
AND bl.batch_id = p_batch_id;
(SELECT
bid_number,
auction_line_number,
auction_shipment_number,
sequence_number,
interface_line_id,
multiplier
FROM pon_bid_price_differ_int
WHERE batch_id = p_batch_id
and auction_shipment_number = -1) bpdi
ON (bpd.bid_number = bpdi.bid_number
AND bpd.line_number = bpdi.auction_line_number
AND bpd.shipment_number = bpdi.auction_shipment_number
AND bpd.price_differential_number = bpdi.sequence_number)
WHEN MATCHED THEN
UPDATE SET
bpd.multiplier = bpdi.multiplier,
bpd.interface_line_id = bpdi.interface_line_id,
bpd.last_update_date = sysdate,
bpd.last_updated_by = p_userid;
( select bpdi.bid_number,
bpdi.auction_line_number,
bsh.shipment_number,
bpdi.sequence_number,
bpdi.interface_line_id ,
bpdi.multiplier
from pon_bid_price_differ_int bpdi,
pon_bid_shipments bsh
where bpdi.batch_id = p_batch_id
and bpdi.auction_header_id = bsh.auction_header_id
and bpdi.bid_number = bsh.bid_number
and bpdi.auction_line_number = bsh.line_number
and bpdi.auction_shipment_number = bsh.auction_shipment_number
and bpdi.auction_shipment_number <> -1 ) bpdi2
ON (bpd.bid_number = bpdi2.bid_number
AND bpd.line_number = bpdi2.auction_line_number
AND bpd.shipment_number = bpdi2.shipment_number
AND bpd.price_differential_number = bpdi2.sequence_number)
WHEN MATCHED THEN
UPDATE SET
bpd.interface_line_id = bpdi2.interface_line_id,
bpd.multiplier = bpdi2.multiplier,
bpd.last_update_date = sysdate,
bpd.last_updated_by = p_userid;
-- 1. Update existing Price Breaks from Interface table to Transaction table
-- 2. Insert new Price Break to Transaction table
-- 3. Delete Price Breaks from Transaction table for those that are intended to be deleted
-- 4. Update Price Breaks from Buyer defined to Supplier owned for those that has structure changes
-- 5. Delete Price Differential associated with the deleted or structure changed Price Breaks
copy_shipment_interface_to_txn(
p_batch_id=>p_batch_id,
p_bid_number=>p_bid_number,
p_userid =>p_userid,
p_bid_currency_precision=> p_bid_currency_precision,
p_shipment_type => g_shipment_type_pricebreak
);
(SELECT
bid_number,
auction_line_number,
sequence_number,
multiplier
FROM pon_bid_price_differ_int
WHERE batch_id = p_batch_id) bpdi
ON (bpd.bid_number = bpdi.bid_number
AND bpd.line_number = bpdi.auction_line_number
AND bpd.shipment_number = -1
AND bpd.price_differential_number = bpdi.sequence_number)
WHEN MATCHED THEN
UPDATE SET
bpd.multiplier = bpdi.multiplier,
bpd.last_update_date = sysdate,
bpd.last_updated_by = p_userid;
UPDATE PON_BID_HEADERS pbh
SET (pbh.SURROG_BID_RECEIPT_DATE,
pbh.BIDDERS_BID_NUMBER,
pbh.BID_EXPIRATION_DATE,
pbh.NOTE_TO_AUCTION_OWNER,
pbh.MIN_BID_CHANGE) =
(SELECT decode(pbh.surrog_bid_flag, 'N', pbh.SURROG_BID_RECEIPT_DATE, pbhi.SURROG_BID_RECEIPT_DATE),
pbhi.BIDDERS_BID_NUMBER,
pbhi.BID_EXPIRATION_DATE,
pbhi.NOTE_TO_AUCTION_OWNER,
pbhi.MIN_BID_CHANGE
FROM PON_BID_HEADERS_INTERFACE pbhi
WHERE batch_id = p_batch_id)
WHERE pbh.bid_number = p_bid_number;
DELETE FROM PON_BID_HEADERS_INTERFACE WHERE batch_id = p_batch_id;
DELETE FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = p_batch_id;
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id;
DELETE FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = p_batch_id;
DELETE FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = p_batch_id;
DELETE FROM pon_bid_shipments_int bshi
WHERE bshi.batch_id = p_batch_id;
SELECT bli.attachment_url,
bli.attachment_desc,
bli.line_number
FROM pon_bid_item_prices_interface bli,
pon_auction_item_prices_all paip
WHERE bli.batch_id = p_batch_id
AND bli.attachment_desc IS NOT null
AND bli.attachment_url IS NOT null
AND bli.auction_header_id = paip.auction_header_id
AND bli.line_number = paip.line_number
AND Nvl(paip.clm_info_flag,'N') = 'N';
select nvl(two_part_flag, 'N')
into l_two_part_flag
from pon_auction_headers_all
where auction_header_id = p_auc_header_id;
SELECT nvl(max(seq_num), 0) + 1
INTO l_seq_num
FROM fnd_attached_documents
WHERE entity_name = 'PON_BID_ITEM_PRICES'
AND pk1_value = p_auc_header_id
AND pk2_value = p_bid_number
AND pk3_value = line.line_number;
SELECT sysdate + g_exp_days_offset,
ah.auction_header_id,
bh.display_price_factors_flag,
decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
decode(ah.auction_status, 'AUCTION_CLOSED', 'Y', 'N'),
bh.surrog_bid_flag,
decode(bh.surrog_bid_flag, 'Y', 'N', 'Y'),
nvl(ah.has_price_elements, 'N'),
ah.progress_payment_type,
bh.number_price_decimals
INTO g_exp_date,
l_auc_header_id,
l_header_disp_pf,
l_blanket,
l_mas,
l_full_qty,
l_auc_closed,
l_buyer_user,
l_supplier_user,
l_has_pe,
l_progress_payment_type,
l_price_precision
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE bh.bid_number = p_bid_number
AND ah.auction_header_id = bh.auction_header_id;
-- Update auction currency columns for the current batch
PON_RESPONSE_PVT.recalculate_auc_curr_prices(p_bid_number, 'N', p_batch_id);
-- Update group amounts for the current batch
-- NOTE: group amount is only calculated at the time of publish
-- PON_RESPONSE_PVT.calculate_group_amounts(p_bid_number, l_supplier_user, 'N', p_batch_id);
INSERT ALL
--1
WHEN s_min_bid_change IS NOT NULL
AND s_min_bid_change_type <> 'PERCENTAGE'
AND PON_BID_VALIDATIONS_PKG.validate_price_precision(
s_min_bid_change, s_bid_price_precision) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
p_batch_id,
s_proxy_bid_row,
'PON_AUC_MINDEC_INVALID_PREC' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_min_bid_change,
'NUM',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_lines_worksheet_name,
l_lines_worksheet_sequence
)
--2
WHEN s_min_bid_change IS NOT NULL
AND s_min_bid_change <= 0 THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
p_batch_id,
s_proxy_bid_row,
'PON_AUC_MINDEC_POS' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_min_bid_change,
'NUM',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_lines_worksheet_name,
l_lines_worksheet_sequence
)
--3
WHEN s_min_bid_change IS NOT NULL
AND s_min_bid_change < s_auc_min_bid_decrement THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' ||l_suffix),
p_batch_id,
s_proxy_bid_row,
'PON_AUCTS_MIN_DEC_LOWER' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_min_bid_change,
'NUM',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_lines_worksheet_name,
l_lines_worksheet_sequence
)
--4
WHEN s_surrogate_bid_flag = 'Y'
AND s_response_recvd_time IS NULL THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
p_batch_id,
s_response_recvd_row,
'PON_AUCTS_BAD_SURROG_1' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
'',
'TXT',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_header_worksheet_name,
l_header_worksheet_sequence
)
--5
WHEN s_surrogate_bid_flag = 'Y'
AND s_response_recvd_time IS NOT NULL
AND ((s_response_recvd_time > sysdate)
OR (s_open_bidding_date IS NOT NULL AND s_response_recvd_time < s_open_bidding_date)
OR (s_response_recvd_time > s_close_bidding_date)) THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
p_batch_id,
s_response_recvd_row,
'PON_AUCTS_BAD_SURROG_2' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_response_recvd_time,
'TIM',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_header_worksheet_name,
l_header_worksheet_sequence
)
--6
WHEN s_surrogate_bid_flag = 'Y'
AND s_old_response_recvd_time IS NOT NULL
AND s_response_recvd_time IS NOT NULL
AND s_response_recvd_time < s_old_response_recvd_time THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUC_SURROG_RECVD_TIME' ||l_suffix),
p_batch_id,
s_response_recvd_row,
'PON_AUCTS_BAD_SURROG_3' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_response_recvd_time,
'TIM',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_header_worksheet_name,
l_header_worksheet_sequence
)
--7
WHEN s_bid_valid_until IS NOT NULL
AND s_bid_valid_until < s_close_bidding_date THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER
)
VALUES
(
l_interface_type,
fnd_message.get_string('PON', 'PON_AUC_BID_VALID_UNTIL' ||l_suffix),
p_batch_id,
s_reference_number_row,
'PON_AUCTS_BAD_BID_CLOSE' || l_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_loginid,
p_request_id,
s_bid_valid_until,
'DAT',
s_auction_header_id,
s_bid_number,
g_exp_date,
s_header_worksheet_name,
l_header_worksheet_sequence
)
SELECT
pbhi.auction_header_id s_auction_header_id,
pbhi.bid_number s_bid_number,
pbhi.BID_EXPIRATION_DATE s_bid_valid_until,
pbhi.SURROG_BID_RECEIPT_DATE s_response_recvd_time,
pbhi.MIN_BID_CHANGE s_min_bid_change,
pbhi.PROXY_BID_ROW s_proxy_bid_row,
pbhi.REFERENCE_NUMBER_ROW s_reference_number_row,
(pbhi.REFERENCE_NUMBER_ROW -1) s_response_recvd_row,
pbhi.HEADER_WORKSHEET_NAME s_header_worksheet_name,
pbhi.LINES_WORKSHEET_NAME s_lines_worksheet_name,
nvl(pah.min_bid_change_type,'AMOUNT') s_min_bid_change_type,
pah.min_bid_decrement s_auc_min_bid_decrement,
pah.open_bidding_date s_open_bidding_date,
pah.close_bidding_date s_close_bidding_date,
pbh.old_surrog_bid_receipt_date s_old_response_recvd_time,
pbh.surrog_bid_flag s_surrogate_bid_flag,
pbh.number_price_decimals s_bid_price_precision
FROM PON_BID_HEADERS_INTERFACE pbhi,
PON_AUCTION_HEADERS_ALL pah,
PON_BID_HEADERS pbh
WHERE pbhi.batch_id = p_batch_id
AND pbhi.bid_number = p_bid_number
AND pbh.bid_number = pbhi.bid_number
AND pah.auction_header_id = pbh.auction_header_id;
INSERT ALL
-- Price Type can not be null. This secnario only possible for Xml Spreadsheet, which is not exist
-- for online case.
WHEN s_price_type is null THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_NAME,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(l_interface_type,
'PON_BID_SHIPMENTS',
fnd_message.get_string('PON', 'PON_BID_PRICE_OR_DISCOUNT'),
p_batch_id,
s_interface_line_id,
'PON_FIELD_MUST_BE_ENTERED',
p_user_id,
sysdate,
p_user_id,
sysdate,
p_request_id,
'BID_PBS',
'PriceType',
s_price_type,
'TXT',
p_auction_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
s_document_disp_line_number,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
select bshi.price_type s_price_type,
bshi.line_number s_line_number,
bshi.bid_shipment_number s_shipment_number,
bshi.interface_line_id s_interface_line_id,
bipi.document_disp_line_number s_document_disp_line_number,
bipi.worksheet_name s_worksheet_name,
bipi.worksheet_sequence_number s_worksheet_seq_num,
'PON_AUCTS_PRICE_BREAKS' s_entity_name
from
pon_bid_item_prices_interface bipi,
pon_bid_shipments_int bshi
where bshi.batch_id = p_batch_id
and bshi.bid_number = p_bid_number
and bshi.action in (g_pb_required, g_pb_optional, g_pb_new)
and bshi.batch_id = bipi.batch_id
and bshi.bid_number = bipi.bid_number
and bshi.line_number = bipi.line_number;
p_user_id IN pon_bid_item_prices.last_updated_by%TYPE,
p_suffix IN VARCHAR2,
p_has_pe IN pon_auction_item_prices_all.HAS_PRICE_ELEMENTS_FLAG%TYPE,
l_attr_enabled_flag IN pon_auction_headers_all.line_attribute_enabled_flag%TYPE,
l_req_enabled_flag IN pon_auction_headers_all.hdr_attribute_enabled_flag%TYPE,
l_has_hdr_attr_flag IN pon_auction_headers_all.has_hdr_attr_flag%TYPE,
p_progress_payment_type IN pon_auction_headers_all.progress_payment_type%TYPE,
p_blanket IN varchar2,
p_price_tiers_indicator IN pon_auction_headers_all.PRICE_TIERS_INDICATOR%type
) IS
BEGIN
--Validate header fields
VALIDATE_HEADER(p_batch_id, p_auction_header_id, p_bid_number, p_request_id, p_user_id);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER)
(SELECT
'BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUC_PRICE_ELEMENT'),
p_batch_id,
bpei.interface_line_id,
'PON_AUC_NOT_ALL_BID_PE',
p_user_id,
sysdate,
p_user_id,
sysdate,
p_request_id,
'TXT',
bli.auction_header_id,
bli.bid_number,
bli.line_number,
g_exp_date,
bpei.worksheet_name,
bpei.worksheet_sequence_number
FROM pon_bid_item_prices_interface bli,
pon_bid_item_prices bip,
pon_bid_price_elements_int bpei
WHERE bli.batch_id = p_batch_id
AND bli.bid_number = bip.bid_number
AND bli.line_number = bip.line_number
AND bip.display_price_factors_flag = 'Y'
AND bpei.batch_id = bli.batch_id
AND bpei.line_number = bli.line_number
AND EXISTS
(SELECT bpfi.price_element_type_id
FROM pon_bid_price_elements_int bpfi,
pon_price_elements apf
WHERE apf.auction_header_id = p_auction_header_id
AND apf.line_number = bli.line_number
AND apf.pf_type = 'SUPPLIER'
AND bpfi.batch_id (+) = bli.batch_id
AND bpfi.line_number (+) = apf.line_number
AND bpfi.price_element_type_id (+) = apf.price_element_type_id
AND rownum = 1));
UPDATE pon_bid_item_prices_interface bli
SET bli.interface_line_id = g_skip_int
WHERE bli.batch_id = p_batch_id
AND EXISTS
(SELECT 'Y'
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl
WHERE bl.bid_number = bli.bid_number
AND bl.line_number = bli.line_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND (
-- GROUPs ignored - do not skip a group
-- al.group_type = 'GROUP'
-- proxy lines ignored
--OR
bl.copy_price_for_proxy_flag = 'Y'
-- empty lines ignored
OR ( (al.price_disabled_flag = 'Y'
OR bli.bid_currency_price IS null)
AND (al.quantity_disabled_flag = 'Y'
OR bli.quantity IS null
OR (p_full_qty = 'Y' OR al.group_type = 'LOT_LINE'
OR al.order_type_lookup_code = 'AMOUNT'))
AND bli.note_to_auction_owner IS null
AND bli.promised_date IS null
-- If the values of these columns are NOT same as that in the transaction table
-- (pon_bid_item_prices) then DO NOT skip.
-- If the values are same as that in the transaction table then skip.
-- You do not have to check about optional or required pay items.
AND NVL(bli.recoupment_rate_percent, -9999) = NVL(bl.recoupment_rate_percent, -9999)
AND NVL(bli.bid_curr_advance_amount, -9999) = NVL(bl.bid_curr_advance_amount, -9999)
AND NVL(bli.bid_curr_max_retainage_amt, -9999) = NVL(bl.bid_curr_max_retainage_amt, -9999)
AND NVL(bli.retainage_rate_percent, -9999) = NVL(bl.retainage_rate_percent,-9999)
AND NVL(bli.progress_pymt_rate_percent, -9999) = NVL(bl.progress_pymt_rate_percent, -9999)
-- No price elements
AND(bl.display_price_factors_flag = 'N'
OR NOT EXISTS
(SELECT bpfi.price_element_type_id
FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = bli.batch_id
AND bpfi.line_number = bli.line_number
AND bpfi.bid_currency_value IS NOT null))
-- No line / Price Break level price differentials
AND ( NOT EXISTS
(SELECT bpdi.sequence_number
FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = bli.batch_id
and bpdi.auction_line_number = bli.line_number
--and bpdi.shipment_number = -1
AND bpdi.multiplier IS NOT null))
-- No Price Breaks - tricky part.
-- According to ECO:
-- If there is no value enterred for Price/Discount, user is intended to delete this PB.
-- This action will over write all other actions like update, Currently it is also decided that
-- the new PB will also driven by this Price/Discount column, if no value entered for this field
-- just consider intended to delete it, or do not insert it at all.
-- Thus, when determine skipped line, do not consider those that are intended to be deleted.
-- For Price Tier, only consider Price, Price Discount is always null.
AND ( NOT EXISTS
(SELECT bshi.line_number
FROM pon_bid_shipments_int bshi
WHERE bshi.batch_id = bli.batch_id
and bshi.line_number = bli.line_number
and bshi.action in ( g_pb_required, g_pb_optional, g_pb_new )
and (bshi.bid_currency_unit_price IS NOT null
or bshi.price_discount IS NOT null )) )
-- No attributes
AND (al.has_attributes_flag = 'N'
OR NOT EXISTS -- no attributes
(SELECT bai.attribute_name
FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = bli.batch_id
AND bai.line_number = bli.line_number
AND bai.value IS NOT null))
-- No payments
AND (NOT EXISTS -- no payments
(SELECT pbpi.interface_line_id
FROM pon_bid_payments_interface pbpi
WHERE pbpi.batch_id = bli.batch_id
AND pbpi.document_disp_line_number = al.document_disp_line_number
AND (pbpi.bid_currency_price IS NOT NULL
OR pbpi.promised_date IS NOT NULL)
))
)));
-- delete bid attribute values from interface tables
DELETE FROM pon_bid_attr_values_interface bai
WHERE bai.batch_id = p_batch_id
AND bai.line_number in (
select bli.line_number
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int);
-- Delete from price elements interface table
DELETE FROM pon_bid_price_elements_int bpfi
WHERE bpfi.batch_id = p_batch_id
AND bpfi.line_number in (
select bli.line_number
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int);
-- Delete from price differentials interface table
DELETE FROM pon_bid_price_differ_int bpdi
WHERE bpdi.batch_id = p_batch_id
AND bpdi.auction_line_number in (
select bli.line_number
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int);
-- Delete from price breaks / price tiers interface table
DELETE FROM pon_bid_shipments_int bshi
WHERE bshi.batch_id = p_batch_id
AND bshi.line_number in (
select bli.line_number
from pon_bid_item_prices_interface bli
where bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int);
-- Delete from payments interface table
DELETE FROM pon_bid_payments_interface pbpi
WHERE pbpi.batch_id = p_batch_id
AND pbpi.document_disp_line_number in (
select pai.document_disp_line_number
from pon_bid_item_prices_interface bli,
pon_auction_item_prices_all pai
where bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int
AND pai.auction_header_id = bli.auction_header_id
AND pai.line_number = bli.line_number);
-- Delete all bid lines from interface table
DELETE FROM pon_bid_item_prices_interface bli
WHERE bli.batch_id = p_batch_id
AND bli.interface_line_id = g_skip_int;
select person_party_id
into l_person_party_id
from fnd_user
where user_id = p_user_id;
SELECT paa.line_number,
pbai.interface_line_id,
paa.attribute_name,
paa.sequence_number,
paa.datatype,
pbai.value,
pbai.worksheet_name,
pbai.worksheet_sequence_number
BULK COLLECT INTO
l_line_numbers,
l_int_lines,
l_attr_names,
l_sequence_numbers,
l_datatypes,
l_values,
l_worksheet_names,
l_worksheet_seq_numbers
FROM
pon_bid_attr_values_interface pbai,
pon_auction_attributes paa
WHERE
pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number = -1
AND pbai.auction_header_id = paa.auction_header_id
AND pbai.line_number = paa.line_number
AND pbai.sequence_number = paa.sequence_number;
UPDATE pon_bid_attr_values_interface pbai
SET pbai.value = decode(l_datatypes(i),
'TXT', l_values(i),
'NUM', to_char(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,'''),
decode(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''') - floor(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')),
0,
g_xml_char_no_prec_mask,
g_xml_char_prec_mask),
'NLS_NUMERIC_CHARACTERS=''.,'''),
'DAT', decode(l_sequence_numbers(i), g_attr_need_by_date_seq,
to_char(decode(l_is_valid_timezone, 'Y',
PON_OEX_TIMEZONE_PKG.convert_time(to_date(l_values(i), g_xml_date_time_mask),
l_timezone, l_oex_timezone),
to_date(l_values(i), g_xml_date_time_mask)),
g_pon_date_time_mask),
to_char(to_date(l_values(i), g_xml_date_mask), g_pon_date_mask)),
'URL', l_values(i))
WHERE pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number = l_line_numbers(i)
AND pbai.sequence_number = l_sequence_numbers(i);
-- Insert errors for each erroneous requirement
FOR i IN 1..l_num_errors LOOP
l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
('BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTR_VALUES',
p_batch_id,
l_int_lines(l_index),
'PON_AUCTS_ATTR_INVALID_VALUE' || p_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_request_id,
l_values(l_index),
'TXT',
p_auction_header_id,
p_bid_number,
l_line_numbers(l_index),
g_exp_date,
l_worksheet_names(l_index),
l_worksheet_seq_numbers(l_index),
'PON_AUC_REQUIREMENTS');
UPDATE
pon_bid_attr_values_interface pbai
SET
pbai.value = null
WHERE
pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number = l_line_numbers(l_index)
AND pbai.sequence_number = l_sequence_numbers(l_index);
select person_party_id
into l_person_party_id
from fnd_user
where user_id = p_user_id;
SELECT
paip.document_disp_line_number,
paa.line_number,
pbai.interface_line_id,
paa.attribute_name,
paa.sequence_number,
paa.datatype,
pbai.value,
pbai.worksheet_name,
pbai.worksheet_sequence_number
BULK COLLECT INTO
l_disp_line_numbers,
l_line_numbers,
l_int_lines,
l_attr_names,
l_sequence_numbers,
l_datatypes,
l_values,
l_worksheet_names,
l_worksheet_seq_numbers
FROM
pon_bid_attr_values_interface pbai,
pon_auction_attributes paa,
pon_auction_item_prices_all paip
WHERE
pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number <> -1
AND pbai.auction_header_id = paa.auction_header_id
AND pbai.line_number = paa.line_number
AND pbai.sequence_number = paa.sequence_number
AND paa.auction_header_id = paip.auction_header_id
AND paa.line_number = paip.line_number;
UPDATE pon_bid_attr_values_interface pbai
SET pbai.value = decode(l_datatypes(i),
'TXT', l_values(i),
'NUM', to_char(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,'''),
decode(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''') - floor(to_number(l_values(i), g_xml_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')),
0,
g_xml_char_no_prec_mask,
g_xml_char_prec_mask),
'NLS_NUMERIC_CHARACTERS=''.,'''),
'DAT', decode(l_sequence_numbers(i), g_attr_need_by_date_seq,
to_char(decode(l_is_valid_timezone, 'Y',
PON_OEX_TIMEZONE_PKG.convert_time(to_date(l_values(i), g_xml_date_time_mask),
l_timezone, l_oex_timezone),
to_date(l_values(i), g_xml_date_time_mask)),
g_pon_date_time_mask),
to_char(to_date(l_values(i), g_xml_date_mask), g_pon_date_mask)),
'URL', l_values(i))
WHERE pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number = l_line_numbers(i)
AND pbai.sequence_number = l_sequence_numbers(i);
-- Insert errors for each erroneous attribute
FOR i IN 1..l_num_errors LOOP
l_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ERROR_VALUE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
('BIDBYSPREADSHEET',
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTR_VALUES',
p_batch_id,
l_int_lines(l_index),
'PON_AUC_ATTR_INVALID_TARGET' || p_suffix,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_request_id,
l_values(l_index),
'TXT',
p_auction_header_id,
p_bid_number,
l_line_numbers(l_index),
g_exp_date,
'LINENUMBER',
l_disp_line_numbers(l_index),
'ATTRIBUTENAME',
l_attr_names(l_index),
l_worksheet_names(l_index),
l_worksheet_seq_numbers(l_index),
'PON_AUC_ATTRIBUTES');
UPDATE
pon_bid_attr_values_interface pbai
SET
pbai.value = null
WHERE
pbai.auction_header_id = p_auction_header_id
AND pbai.bid_number = p_bid_number
AND pbai.batch_id = p_batch_id
AND pbai.line_number = l_line_numbers(l_index)
AND pbai.sequence_number = l_sequence_numbers(l_index);
p_userid IN pon_bid_item_prices.last_updated_by%TYPE,
p_bid_currency_precision IN pon_bid_headers.number_price_decimals%TYPE,
p_shipment_type IN pon_bid_shipments.shipment_type%TYPE
)
is
l_module CONSTANT VARCHAR2(32) := 'copy_shipment_interface_to_txn';
update pon_bid_shipments_int bshi
set bid_shipment_number =
NVL( (select bsh.shipment_number
from pon_bid_shipments bsh
where bsh.bid_number = bshi.bid_number
and bsh.line_number = bshi.line_number
and bsh.auction_shipment_number = bshi.auction_shipment_number),
bid_shipment_number)
where bshi.batch_id = p_batch_id
and bshi.bid_number =p_bid_number
and bshi.action in ( g_pb_required, g_pb_optional, g_pb_delete);
--2. Update Price Break / Price Tier Transaction based on bid_shipment_number
--Notes. The calculation between bid_currency_unit_price and Discount relies on the line's bid_currency_unit_price,
--thus this merge should happened after line entity has updated bid_currency_unit_price.
MERGE INTO pon_bid_shipments bsh
USING
(SELECT
bsi.action,
bsi.bid_number,
bsi.auction_header_id,
bsi.line_number,
bsi.batch_id,
bsi.interface_line_id,
bsi.bid_shipment_number,
bsi.auction_shipment_number,
bsi.ship_to_organization_id,
bsi.ship_to_location_id,
bsi.quantity,
bsi.max_quantity,
bsi.effective_start_date,
bsi.effective_end_date,
bsi.price_type,
bsi.price_discount,
bsi.bid_currency_unit_price,
bip.bid_currency_unit_price item_price
FROM pon_bid_shipments_int bsi,
pon_bid_item_prices bip
WHERE bsi.batch_id = p_batch_id
and bsi.bid_number = p_bid_number
and bsi.bid_number = bip.bid_number
and bsi.auction_header_id = bip.auction_header_id
and bsi.line_number = bip.line_number
and bsi.action in ( g_pb_required, g_pb_optional, g_pb_new)
) bshi
ON ( bsh.bid_number = bshi.bid_number
and bsh.line_number = bshi.line_number
and bsh.shipment_number = bshi.bid_shipment_number )
WHEN MATCHED THEN
UPDATE SET
bsh.interface_line_id = bshi.interface_line_id,
bsh.price_type = bshi.price_type,
bsh.ship_to_organization_id = decode ( bshi.action, g_pb_required, bsh.ship_to_organization_id, --No changes for required PB
bshi.ship_to_organization_id),
bsh.ship_to_location_id = decode ( bshi.action, g_pb_required, bsh.ship_to_location_id, --No changes for Required PB
bshi.ship_to_location_id),
bsh.quantity = decode ( bshi.action, g_pb_required, bsh.quantity, -- No changes for Required PB
bshi.quantity),
bsh.max_quantity = bshi.max_quantity,
bsh.effective_start_date = decode ( bshi.action, g_pb_required, bsh.effective_start_date, -- No changes for Required PB
bshi.effective_start_date),
bsh.effective_end_date = decode ( bshi.action, g_pb_required, bsh.effective_end_date, -- No changes for Required PB
bshi.effective_end_date),
bsh.bid_currency_unit_price =
NVL2(bshi.item_price,
-- If item_price is not null,
-- if it is PRICE_DISCOUNT type, caculate the bid_currency_unit
-- price based on the item_price and price_discount
-- and round it up based on bid currency precision.
decode(bshi.price_type, 'PRICE DISCOUNT',
nvl2(bshi.price_discount,
round(bshi.item_price*(1-bshi.price_discount/100),
P_bid_currency_precision),
null),
-- if it is PRICE type, copy bid_currency_unit_price
-- directly from interface table to transaction table
'PRICE',
bshi.bid_currency_unit_price,
null
),
-- if item_price is null,
-- if the price type is price_discount, then set bid_currency_unit_price as null
-- else if the price type is price, then set price_discount as null
-- else just copy as is. -- Notes, it is possible that hte price_type is null for Xml Spreadsheet.
decode(bshi.price_type,'PRICE DISCOUNT',
null,
'PRICE',
bshi.bid_currency_unit_price,
null
)
),
bsh.price_discount=
decode(p_shipment_type, g_shipment_type_quantitybased, null,
NVL2(bshi.item_price,
-- If item Price is not null
-- if it is "DISCOUNT" type, copy discount from interface to transaction table
decode(bshi.price_type, 'PRICE DISCOUNT',
bshi.price_discount,
-- if it is "PRICE" type, and
-- item_Price != 0 and Bid_currency_unit_price < item_price
-- sets the price discount to 1-(bid_currency_unit_price/item_price)
'PRICE',
nvl2(bshi.bid_currency_unit_price,
case when ( bshi.bid_currency_unit_price>=bshi.item_price)
then null
when ( bshi.bid_currency_unit_price<>0)
then (1- bshi.bid_currency_unit_price/bshi.item_price)*100
else null
end,
null
),
bshi.price_discount
),
-- If item_price is null, just copy discount as is, set Price as null.
decode(bshi.price_type,'PRICE',
null,
'PRICE DISCOUNT',
bshi.price_discount,
null)
)
),
bsh.last_update_date = sysdate,
bsh.last_updated_by = P_userid
-- 3. Insert new rows if not match for new PBs
WHEN NOT MATCHED THEN
INSERT
( bid_number,
line_number,
shipment_number,
auction_header_id,
auction_line_number,
auction_shipment_number,
shipment_type,
ship_to_organization_id,
ship_to_location_id,
quantity,
max_quantity,
price_type,
bid_currency_unit_price,
price_discount,
effective_start_date,
effective_end_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
has_price_differentials_flag,
interface_line_id )
values
(
p_bid_number,
bshi.line_number,
bshi.bid_shipment_number,
bshi.auction_header_id,
bshi.line_number,
null, -- set auction_shipment_number as null since it is Supplier owned PB
p_shipment_type,
bshi.ship_to_organization_id,
bshi.ship_to_location_id,
bshi.quantity,
bshi.max_quantity,
bshi.price_type,
NVL2(bshi.item_price,
-- If item_price is not null,
-- if it is PRICE_DISCOUNT type, caculate the bid_currency_unit
-- price based on the item_price and price_discount
-- and round it up based on bid currency precision.
decode(bshi.price_type, 'PRICE DISCOUNT',
nvl2(bshi.price_discount,
round(bshi.item_price*(1-bshi.price_discount/100),
P_bid_currency_precision),
null),
-- if it is PRICE type, copy bid_currency_unit_price
-- directly from interface table to transaction table
'PRICE',
bshi.bid_currency_unit_price,
null
),
-- if item_price is null,
-- if the price type is price_discount, then set bid_currency_unit_price as null
-- else if the price type is price, then set price_discount as null
-- else just copy as is. -- Notes, it is possible that hte price_type is null for Xml Spreadsheet.
decode(bshi.price_type,'PRICE DISCOUNT',
null,
'PRICE',
bshi.bid_currency_unit_price,
null
)
),
decode(p_shipment_type, g_shipment_type_quantitybased, null,
NVL2(bshi.item_price,
-- If item Price is not null
-- if it is "DISCOUNT" type, copy discount from interface to transaction table
decode(bshi.price_type, 'PRICE DISCOUNT',
bshi.price_discount,
-- if it is "PRICE" type, and
-- item_Price != 0 and Bid_currency_unit_price < item_price
-- sets the price discount to 1-(bid_currency_unit_price/item_price)
'PRICE',
nvl2(bshi.bid_currency_unit_price,
case when ( bshi.bid_currency_unit_price>=bshi.item_price)
then null
when ( bshi.bid_currency_unit_price<>0)
then (1- bshi.bid_currency_unit_price/bshi.item_price)*100
else null
end,
null
),
bshi.price_discount
),
-- If item_price is null, just copy discount as is, set Price as null.
decode(bshi.price_type,'PRICE',
null,
'PRICE DISCOUNT',
bshi.price_discount,
null)
)
),
bshi.effective_start_date,
bshi.effective_end_date,
sysdate,
p_userId,
sysdate,
p_userId,
'N',
bshi.interface_line_id)
where
bshi.action = g_pb_new;
--4. Delete data from transaction table for those Price Breaks that are flagged to be deleted based on
delete from pon_bid_shipments bsh
where bsh.bid_number = p_bid_number
and exists
(
select 1
from pon_bid_shipments_int bsi
where bsi.batch_id= p_batch_id
and bsi.bid_number = P_BID_NUMBER
and bsi.action = g_pb_delete
and bsi.bid_number = bsh.bid_number
and bsi.line_number = bsh.line_number
and bsi.bid_shipment_number = bsh.shipment_number
and rownum = 1 );
-- Notes, if need to use g_pb_optionals_updated flag to determine skipped line, move this part before determin_xml_skipped_line
update
pon_bid_shipments_int bsi
set bsi.action= g_pb_optional_updated
where bsi.batch_id = p_batch_id
and bsi.BID_NUMBER = p_bid_number
and bsi.action = g_pb_optional
and bsi.auction_shipment_number is not null
and exists(
select 1
from
pon_auction_shipments_all ash
where
ash.auction_header_id = bsi.auction_header_id
and ash.line_number = bsi.line_number
and ash.shipment_number = bsi.auction_shipment_number
and ( nvl(ash.ship_to_organization_id, g_null_int) <> nvl(bsi.ship_to_organization_id, g_null_int)
or
nvl(ash.ship_to_location_id,g_null_int) <> nvl(bsi.ship_to_location_id,g_null_int)
or
nvl(ash.quantity,g_null_int) <> nvl(bsi.quantity,g_null_int)
or
nvl(ash.effective_start_date,sysdate) <> nvl(bsi.effective_start_date,sysdate)
or
nvl(ash.effective_end_date,sysdate) <> nvl(bsi.effective_end_date,sysdate)
)
and rownum = 1
);
-- 5.2 Update shipment transaction table for supplier owned shipments.
update
pon_bid_shipments bsh
set bsh.auction_shipment_number = null,
bsh.has_price_differentials_flag = 'N'
where bsh.bid_number = p_bid_number
and exists
( select 1
from
pon_bid_shipments_int bshi
where bshi.batch_id = p_batch_id
and bshi.bid_number = p_bid_number
and bshi.action = g_pb_optional_updated
and bshi.auction_header_id = bsh.auction_header_id
and bshi.bid_number = bsh.bid_number
and bshi.line_number = bsh.line_number
and bshi.bid_shipment_number = bsh.shipment_number );
-- and deleted Price Break.
delete from pon_bid_price_differentials bsd
where
bsd.shipment_number<>-1
and bsd.bid_number=p_bid_number
and bsd.line_number in
(select bip.line_number
from pon_bid_item_prices bip
where bip.batch_id=p_batch_id
and bip.bid_number=p_bid_number
)
and
(bsd.bid_number, bsd.line_number, bsd.shipment_number)
not in
(
select bsh.bid_number, bsh.line_number, bsh.shipment_number
from pon_bid_shipments bsh,pon_bid_item_prices bip
where bip.batch_id = p_batch_id
and bip.bid_number = p_bid_number
and bip.bid_number = bsh.bid_number
and bip.line_number = bsh.line_number
and (bsh.auction_shipment_number is not null
and bsh.has_price_differentials_flag='Y')
);
SELECT sysdate + g_exp_days_offset,
ah.auction_header_id,
bh.display_price_factors_flag,
decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
decode(ah.auction_status, 'AUCTION_CLOSED', 'Y', 'N'),
nvl(ah.line_attribute_enabled_flag, 'N'),
nvl(ah.hdr_attribute_enabled_flag, 'N'),
nvl(ah.has_hdr_attr_flag, 'N'),
nvl(ah.progress_payment_type,'NONE'),
bh.number_price_decimals,
bh.surrog_bid_flag,
decode(bh.surrog_bid_flag, 'Y', 'N', 'Y'),
nvl(bh.display_price_factors_flag, 'N'),
ah.PRICE_TIERS_INDICATOR
INTO g_exp_date,
l_auc_header_id,
l_header_disp_pf,
l_blanket,
l_mas,
l_full_qty,
l_auc_closed,
l_attr_enabled_flag,
l_req_enabled_flag,
l_has_hdr_attr_flag,
l_progress_payment_type,
l_price_precision,
l_buyer_user,
l_supplier_user,
l_has_pe,
l_price_tiers_indicator
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE bh.bid_number = p_bid_number
AND ah.auction_header_id = bh.auction_header_id;
-- Update auction currency columns for the current batch
PON_RESPONSE_PVT.recalculate_auc_curr_prices(p_bid_number, 'N', p_batch_id);
-- Update group amounts for the current batch
-- NOTE: group amount is only calculated at the time of publish
PON_RESPONSE_PVT.calculate_group_amounts(p_bid_number, l_supplier_user, 'N', p_batch_id);
select count(*) into l_rec_count from po_uda_interface
where pk1_value = p_bid_number and pk2_value = p_line_number and process_status=1;
Cursor lineCursor is select line_number lno from pon_bid_item_prices where bid_number = p_bid_number;
SELECT DISTINCT uda_template_id into l_template_id FROM pon_bid_item_prices WHERE bid_number = p_bid_number;