[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT ALL
-- Price and quantity are required fields if they are editable
WHEN p_blanket = 'N' AND s_price_editable = 'Y' AND s_price IS null
AND s_qty_editable = 'Y' AND s_bid_quantity IS null
THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_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_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_PRICE_QTY_REQD' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'Price',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Price is a required field if it is editable
WHEN s_price_editable = 'Y' AND s_price 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_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_ITEM_PRICES',
fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
p_batch_id,
s_interface_line_id,
'PON_BID_PRICE_REQUIRED' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'Price',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Quantity is a required field if it is editable
WHEN p_blanket = 'N' AND s_qty_editable = 'Y' AND s_bid_quantity 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_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_ITEM_PRICES',
fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
p_batch_id,
s_interface_line_id,
'PON_BID_QUANTITY_REQUIRED' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'Quantity',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Bug 7460446 Bid cannot be zero or negative
-- Doesn't apply for blanket agreements
WHEN p_blanket = 'N' AND s_bid_quantity <= 0 THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_QTY' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUC_BIDQTY_NEG' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'Quantity',
s_bid_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- The promised date cannot be earlier than the current date
-- Does not apply to blanket agreements
WHEN p_blanket = 'N' AND s_promised_date < s_current_date
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUC_PDATE_TOO_EARLY',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisedDate',
s_promised_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : Enter either promise date or POP dates
WHEN p_blanket = 'N'
AND (s_promised_date is not null and (s_clm_pop_start_date is not null OR s_clm_pop_end_date is not null))
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_PROMISED_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_CLM_PROMISE_POPDT_LINE_VAL',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisedDate',
s_promised_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : If Promise date is null, then both POP Start date is entered,then
-- pop end date is mandatory and vice versa.
WHEN p_blanket = 'N'
AND (s_promised_date is null and (s_clm_pop_start_date is not null AND s_clm_pop_end_date is null))
-- AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_END_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_CLM_PROMISE_POP_ENDDT_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePopEndDate',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : If Promise date is null, then both POP Start date is entered,then
-- pop end date is mandatory and vice versa.
WHEN p_blanket = 'N'
AND (s_promised_date is null and (s_clm_pop_start_date is null AND s_clm_pop_end_date is not null))
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_CLM_PROMISE_POP_STDT_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePopStartDate',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : POP Start date should be less than POP end date.
WHEN p_blanket = 'N'
AND ( s_promised_date is null AND
(s_clm_pop_start_date is not null AND s_clm_pop_end_date is not null AND (s_clm_pop_end_date <= s_clm_pop_start_date))
)
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_CLM_PROMISE_POP_DATE_VALX',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePopStartDate',
s_clm_pop_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : POP Start date should be less than current date.
WHEN p_blanket = 'N'
AND ( s_promised_date is null AND
(s_clm_pop_start_date is not null AND s_clm_pop_start_date < s_current_date)
)
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_START_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_POP_START_BEF_TODAY',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePopStartDate',
s_clm_pop_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINE_NUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--CLM Bug : 10212430 : POP End date should be less than current date.
WHEN p_blanket = 'N'
AND ( s_promised_date is null AND
(s_clm_pop_end_date is not null AND s_clm_pop_end_date < s_current_date)
)
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_CLM_PROMISE_POP_END_DATE'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_POP_END_BEF_TODAY',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePopEndDate',
s_clm_pop_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINE_NUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
--Event Based Delivery Project.
WHEN p_blanket = 'N'
AND (s_clm_del_event IS NOT NULL AND
(s_clm_promise_period IS NULL OR s_clm_promise_period_uom IS NULL OR s_clm_promise_period_uom = '') )
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'CLM_PROMISE_PERIOD'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_PERIOD_REQUIRED',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePeriod',
s_clm_promise_period,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINE_NUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
WHEN p_blanket = 'N'
AND (s_clm_del_event IS NOT NULL AND
s_clm_promise_period IS NOT NULL AND
s_clm_promise_period <= 0 )
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'CLM_PROMISE_PERIOD'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_INV_PERIOD',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePeriod',
s_clm_promise_period,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINE_NUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
WHEN p_blanket = 'N'
AND (s_clm_del_event IS NOT NULL AND
s_clm_pop_duration IS NOT NULL AND
s_clm_pop_duration <= 0 )
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'CLM_POP_DURATION'),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_BID_INV_POP_DUR',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisePOPDuration',
s_clm_pop_duration,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINE_NUM',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- po_bid_min_rel_amount should not be negative
WHEN p_blanket = 'Y' AND s_po_bid_min_rel_amount < 0 THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUC_MINREL_POS_ZERO' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PoBidMinRelAmount',
s_po_bid_min_rel_amount,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- po_bid_min_rel_amount should not exceed currency precision
WHEN p_blanket = 'Y' AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(s_po_bid_min_rel_amount, p_amt_precision) = 'F' THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_BID_MIN_REL_AMOUNT' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUC_MINREL_MIN_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PoBidMinRelAmount',
s_po_bid_min_rel_amount,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_unit_price should be positive
-- bid_currency_unit_price only validated when header disp_pf_flag is Y
-- Suppress error if spreadsheet case and no price factors
WHEN p_header_disp_pf = 'Y' AND s_bid_currency_unit_price <= 0
AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_LINEPRICE_MUST_BE_POS',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_unit_price only validated when header disp_pf_flag is Y
-- bid_currency_unit_price precision should not exceed price precision
-- Suppress error if spreadsheet case and no price factors
WHEN p_header_disp_pf = 'Y' AND
PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_unit_price, p_price_precision) = 'F'
AND (p_spreadsheet = g_online_mode OR s_display_price_factors_flag = 'Y')
--AND p_two_part_tech_surrogate_flag = 'N'
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTION_ITEM_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_LINEPRICE_MIN_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
sysdate s_current_date,
Decode(Nvl(bl.clm_info_flag,'N'),'Y','N',
Decode(Nvl(bl.clm_cost_constraint,'X'),'NSP','N','NC','N','Y')) s_price_editable,
Decode(Nvl(bl.clm_info_flag,'N'),'Y','N',
decode(bl.order_type_lookup_code, 'AMOUNT', 'N',
'RATE', 'N', 'FIXED PRICE', 'N', 'Y')) s_qty_editable,
bl.quantity s_bid_quantity,
bl.promised_date s_promised_date,
bl.PROMISE_POP_START_DATE s_clm_pop_start_date,
bl.PROMISE_POP_END_DATE s_clm_pop_end_date,
bl.bid_currency_unit_price s_bid_currency_unit_price,
bl.price s_price,
bl.po_bid_min_rel_amount s_po_bid_min_rel_amount,
bl.display_price_factors_flag s_display_price_factors_flag,
bl.line_number s_line_number,
decode(p_spreadsheet, g_xml_upload_mode, bl.interface_line_id,
g_txt_upload_mode, bl.interface_line_id,
to_number(null)) s_interface_line_id,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code,
bl.line_num_display s_clm_line_num_display,
-- Event Based Delivery Project
bl.CLM_DELIVERY_EVENT_CODE s_clm_del_event,
bl.CLM_PROMISE_PERIOD s_clm_promise_period,
bl.CLM_PROMISE_PERIOD_UOM s_clm_promise_period_uom,
bl.CLM_PROMISE_POP_DURATION s_clm_pop_duration
FROM pon_bid_item_prices bl
, pon_bid_headers pbh
WHERE pbh.auction_header_id = p_auc_header_id
AND pbh.bid_number = p_bid_number
AND bl.bid_number = pbh.bid_number
AND bl.auction_line_number = -1
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
INSERT ALL
-- bid_currency_price should be positive
WHEN (s_bid_currency_price <= 0 AND s_clm_cost_constraint NOT IN ('NSP', 'NC'))
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
decode(p_trans_view, 'N',
'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix,
decode(s_display_price_factors_flag, 'Y',
'PON_LINE_BIDPRICE_INVALID_2' || p_suffix,
'PON_AUC_BIDPRICE_MUST_BE_POS' || p_suffix)),
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_price precision should not exceed price precision
WHEN
( (s_order_type_lookup_code = 'QUANTITY') AND
PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_price, p_price_precision) = 'F')
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_BIDPRICE_MIN_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_price precision should not exceed price precision
WHEN
( s_order_type_lookup_code <> 'QUANTITY' AND
PON_BID_VALIDATIONS_PKG.validate_price_precision(s_bid_currency_price, p_amt_precision) = 'F')
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_BIDPRICE_MIN_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- When rebidding, the new price must be <= old_price, if changed
-- Does not apply to LOT_LINEs
-- Will not apply if price is disabled for this line, or negotiation
-- is not price driver
WHEN p_rebid = 'Y' AND p_price_driven = 'Y'
AND s_price <> s_old_price
AND s_price > s_old_price
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
AND p_two_part_tech_surrogate_flag = 'N'
---------------------------------------------------
THEN
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,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_PRICE' || p_suffix),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
decode(p_bid_decr_method,'BEST_PRICE','PON_BESTBID_PRICE_LOWER','PON_BID_PRICE_LOWER') || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'Price',
s_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- There was a previous bid on this line which was deleted
-- Not allowed to unbid when rebidding.
/*WHEN p_rebid = 'Y' AND s_is_changed_line_flag = 'Y'
AND s_has_bid_flag = 'N' THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_CANNOT_UNBID' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
s_clm_line_num_display,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)*/
SELECT
bl.bid_currency_price s_bid_currency_price,
bl.bid_currency_trans_price s_bid_currency_trans_price,
bl.bid_currency_limit_price s_bid_currency_limit_price,
bl.price s_price,
bl.proxy_bid_limit_price s_proxy_bid_limit_price,
bl.price_break_type s_price_break_type,
bl.display_price_factors_flag s_display_price_factors_flag,
bl.old_price s_old_price,
bl.has_bid_flag s_has_bid_flag,
bl.is_changed_line_flag s_is_changed_line_flag,
bl.line_number s_line_number,
decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
g_xml_upload_mode, bl.interface_line_id,
to_number(null)) s_interface_line_id,
bl.bid_start_price s_enforced_bid_start_price,
bh.surrog_bid_flag s_surrog_bid_flag,
sysdate s_current_date,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, to_number(null)) s_worksheet_sequence_number,
decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ITEMS', to_char(null)) s_entity_message_code,
bh.rate s_rate,
bh.number_price_decimals s_precision,
bl.line_num_display s_clm_line_num_display,
nvl(bl.clm_cost_constraint,'X') s_clm_cost_constraint, -- CLM QA Bug : 9835426
bl.order_type_lookup_code s_order_type_lookup_code --For clm bug : 10136754
FROM pon_bid_headers bh,
pon_bid_item_prices bl
WHERE bh.auction_header_id = p_auc_header_id
AND bh.bid_number = p_bid_number
AND bl.bid_number = bh.bid_number
AND bl.auction_line_number = -1
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);