[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE pon_bid_item_prices bl
SET bl.has_bid_flag = decode(p_use_batch_id, 'Y', 'Y', bl.has_bid_flag),
bl.is_changed_line_flag = decode(p_rebid_flag, 'N', 'Y', 'N'),
bl.has_user_bid_flag = Decode(g_is_fed, 'Y',
decode(p_use_batch_id, 'Y', 'Y', bl.has_user_bid_flag),'') -- ELIN project changes
WHERE bl.bid_number = p_bid_number
AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
OR (p_use_batch_id = 'N'
AND bl.line_number >= p_batch_start
AND bl.line_number <= p_batch_end))
--ELIN project changes
AND ( (Nvl(bl.user_marked_flag,'N') = 'Y' AND Nvl(bl.has_user_bid_flag, 'N') = 'Y')
OR Nvl(bl.user_marked_flag,'N') = 'N');
UPDATE pon_bid_item_prices bl
SET bl.is_changed_line_flag = 'Y'
WHERE bl.bid_number = p_bid_number
AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
OR (p_use_batch_id = 'N'
AND bl.line_number >= p_batch_start
AND bl.line_number <= p_batch_end))
--ELIN project changes
AND ( (Nvl(bl.user_marked_flag,'N') = 'Y' AND Nvl(bl.has_user_bid_flag, 'N') = 'Y')
OR Nvl(bl.user_marked_flag,'N') = 'N')
--ELIN project changes end
AND ((NVL(bl.bid_currency_unit_price, g_null_int) <> NVL(bl.old_bid_currency_unit_price, g_null_int)
OR NVL(bl.bid_currency_price, g_null_int) <> NVL(bl.old_bid_currency_price, g_null_int)
OR bl.proxy_bid_limit_price IS NOT NULL
AND NVL(bl.bid_currency_limit_price, g_null_int) <> NVL(bl.old_bid_currency_limit_price, g_null_int)
OR bl.promised_date IS NULL AND bl.old_promised_date IS NOT NULL
OR bl.promised_date IS NOT NULL AND bl.old_promised_date IS NULL
OR bl.promised_date <> bl.old_promised_date
OR NVL(bl.po_bid_min_rel_amount, g_null_int) <> NVL(bl.old_po_bid_min_rel_amount, g_null_int)
OR bl.note_to_auction_owner IS NULL AND bl.old_note_to_auction_owner IS NOT NULL
OR bl.note_to_auction_owner IS NOT NULL AND bl.old_note_to_auction_owner IS NULL
OR bl.note_to_auction_owner <> bl.old_note_to_auction_owner
OR bl.old_quantity IS NOT NULL AND NVL(bl.quantity, g_null_int) <> bl.old_quantity
OR NVL(bl.bid_curr_advance_amount, g_null_int) <> NVL(bl.old_bid_curr_advance_amount, g_null_int)
OR NVL(bl.recoupment_rate_percent, g_null_int) <> NVL(bl.old_recoupment_rate_percent, g_null_int)
OR NVL(bl.progress_pymt_rate_percent, g_null_int) <> NVL(bl.old_progress_pymt_rate_percent, g_null_int)
OR NVL(bl.retainage_rate_percent, g_null_int) <> NVL(bl.old_retainage_rate_percent, g_null_int)
OR NVL(bl.bid_curr_max_retainage_amt, g_null_int) <> NVL(bl.old_bid_curr_max_retainage_amt, g_null_int)
)
OR (bl.has_attributes_flag = 'Y'
AND EXISTS
-- Check attributes
(SELECT ba.line_number
FROM pon_bid_attribute_values ba
WHERE ba.bid_number = bl.bid_number
AND ba.line_number = bl.line_number
AND (ba.value IS NULL AND ba.old_value IS NOT NULL OR
ba.value IS NOT NULL AND ba.old_value IS NULL OR
ba.value <> ba.old_value)
AND rownum = 1))
OR EXISTS
-- Check price factors
(SELECT bpf.line_number
FROM pon_bid_price_elements bpf
WHERE bpf.bid_number = bl.bid_number
AND bpf.line_number = bl.line_number
AND bpf.pf_type = 'SUPPLIER'
AND NVL(bpf.bid_currency_value, g_null_int) <> NVL(old_bid_currency_value, g_null_int)
AND rownum = 1)
OR EXISTS
-- Check payments for xml spreadsheet upload case
(SELECT bps.bid_line_number
FROM pon_bid_payments_shipments bps
WHERE p_spreadsheet = g_xml_upload_mode
AND bps.bid_number = bl.bid_number
AND bps.bid_line_number = bl.line_number
AND (NVL(bps.payment_display_number, g_null_int) <> NVL(old_payment_display_number, g_null_int)
OR bps.payment_type_code IS NULL AND bps.old_payment_type_code IS NOT NULL
OR bps.payment_type_code IS NOT NULL AND bps.old_payment_type_code IS NULL
OR bps.payment_type_code <> bps.old_payment_type_code
OR bps.payment_description IS NULL AND bps.old_payment_description IS NOT NULL
OR bps.payment_description IS NOT NULL AND bps.old_payment_description IS NULL
OR bps.payment_description <> bps.old_payment_description
OR NVL(bps.quantity, g_null_int) <> NVL(old_quantity, g_null_int)
OR NVL(bps.uom_code, g_null_int) <> NVL(old_uom_code, g_null_int)
OR NVL(bps.bid_currency_price, g_null_int) <> NVL(old_bid_currency_price, g_null_int)
OR bps.promised_date IS NULL AND bps.old_promised_date IS NOT NULL
OR bps.promised_date IS NOT NULL AND bps.old_promised_date IS NULL
OR bps.promised_date <> bps.old_promised_date)
AND rownum = 1)
-- Check if any payments were deleted
OR (nvl(bl.old_no_of_payments,0) <> (select count(payment_display_number)
from pon_bid_payments_shipments bps
where bps.bid_number = bl.bid_number
and bps.bid_line_number = bl.line_number))
OR ( (bl.has_shipments_flag = 'Y' or bl.has_quantity_tiers = 'Y')
AND EXISTS
-- Check shipments
-- If auction_shipment_number is null then it is user defined
-- so we must check all possible values that can be changed
(SELECT bpb.line_number
FROM pon_bid_shipments bpb
WHERE bpb.bid_number = bl.bid_number
AND bpb.line_number = bl.line_number
AND (bpb.auction_shipment_number IS null
AND (NVL(bpb.ship_to_organization_id, g_null_int) <> NVL(bpb.old_ship_to_org_id, g_null_int)
OR NVL(bpb.ship_to_location_id, g_null_int) <> NVL(bpb.old_ship_to_loc_id, g_null_int)
OR bpb.effective_start_date IS NULL AND bpb.old_effective_start_date IS NOT NULL
OR bpb.effective_start_date IS NOT NULL AND bpb.old_effective_start_date IS NULL
OR bpb.effective_start_date <> bpb.old_effective_start_date
OR bpb.effective_end_date IS NULL AND bpb.old_effective_end_date IS NOT NULL
OR bpb.effective_end_date IS NOT NULL AND bpb.old_effective_end_date IS NULL
OR bpb.effective_end_date <> bpb.old_effective_end_date
OR NVL(bpb.quantity, g_null_int) <> NVL(bpb.old_quantity, g_null_int)
OR NVL(bpb.max_quantity, g_null_int) <> NVL(bpb.old_max_quantity, g_null_int)
OR bpb.price_type IS NULL AND bpb.old_price_type IS NOT NULL
OR bpb.price_type IS NOT NULL AND bpb.old_price_type IS NULL
OR bpb.price_type <> bpb.old_price_type)
OR bpb.price_type = 'PRICE' AND NVL(bpb.bid_currency_unit_price, g_null_int) <> NVL(bpb.old_bid_currency_unit_price, g_null_int)
OR bpb.price_type = 'PRICE DISCOUNT' AND NVL(bpb.price_discount, g_null_int) <> NVL(bpb.old_price_discount, g_null_int)
OR NVL(bpb.bid_currency_price, g_null_int) <> NVL(bpb.old_bid_currency_price, g_null_int))
AND rownum = 1))
OR (bl.has_price_differentials_flag = 'Y'
AND EXISTS
-- Check price differentials, including shipment price differentials
(SELECT bpd.line_number
FROM pon_bid_price_differentials bpd
WHERE bpd.bid_number = bl.bid_number
AND bpd.line_number = bl.line_number
AND NVL(bpd.multiplier, g_null_int) <> NVL(bpd.old_multiplier, g_null_int)
AND rownum = 1))
);
UPDATE pon_bid_item_prices pbip
SET pbip.is_changed_line_flag = 'Y'
WHERE pbip.bid_number = p_bid_number
AND pbip.line_number IN
(SELECT al.parent_line_number
FROM pon_bid_item_prices bl,
pon_auction_item_prices_all al
WHERE bl.bid_number = p_bid_number
AND bl.auction_header_id = al.auction_header_id
AND bl.line_number = al.line_number
AND al.group_type = 'GROUP_LINE'
AND bl.is_changed_line_flag = 'Y'
AND ((p_use_batch_id = 'Y' AND bl.batch_id = p_batch_id)
OR (p_use_batch_id = 'N'
AND bl.line_number >= p_batch_start
AND bl.line_number <= p_batch_end)));
UPDATE pon_bid_item_prices bl
SET bl.is_changed_line_flag = 'Y'
WHERE bl.bid_number = p_bid_number
AND bl.batch_id = p_batch_id
AND (EXISTS
-- Check if a shipment was deleted
(SELECT old_bpb.line_number
FROM pon_bid_shipments bpb, pon_bid_shipments old_bpb
WHERE old_bpb.bid_number = p_source_bid
AND old_bpb.line_number = bl.line_number
AND bpb.bid_number (+) = p_bid_number
AND bpb.line_number (+) = old_bpb.line_number
AND bpb.shipment_number (+) = old_bpb.shipment_number
AND bpb.shipment_number IS null
AND rownum = 1)
OR EXISTS
-- Check if a shipment was added
(SELECT bpb.line_number
FROM pon_bid_shipments bpb, pon_bid_shipments old_bpb
WHERE bpb.bid_number = p_bid_number
AND bpb.line_number = bl.line_number
AND old_bpb.bid_number (+) = p_source_bid
AND old_bpb.line_number (+) = bpb.line_number
AND old_bpb.shipment_number (+) = bpb.shipment_number
AND old_bpb.shipment_number IS null
AND rownum = 1)
);
UPDATE pon_bid_attribute_values ba
SET ba.score =
nvl((SELECT s.score
FROM pon_attribute_scores s
WHERE s.auction_header_id = ba.auction_header_id
AND s.line_number = ba.line_number
AND s.attribute_sequence_number = ba.sequence_number
AND (datatype = 'TXT' AND ba.value = s.value
OR datatype = 'NUM' AND to_number(ba.value, g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
BETWEEN to_number(nvl(s.from_range, ba.value), g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
AND to_number(nvl(s.to_range, ba.value), g_number_mask, 'NLS_NUMERIC_CHARACTERS=''.,''')
OR datatype = 'DAT'
AND (ba.sequence_number = -10 AND
to_date(ba.value, 'dd-mm-yyyy hh24:mi:ss') BETWEEN
to_date(nvl(s.from_range, ba.value), 'dd-mm-yyyy hh24:mi:ss')
AND to_date(nvl(s.to_range, ba.value), 'dd-mm-yyyy hh24:mi:ss')
OR ba.sequence_number <> -10 AND
to_date(ba.value, 'dd-mm-yyyy') BETWEEN
to_date(nvl(s.from_range, ba.value), 'dd-mm-yyyy')
AND to_date(nvl(s.to_range, ba.value), 'dd-mm-yyyy')))),
nvl2(ba.value, 0, null))
WHERE ba.bid_number = p_bid_number
AND EXISTS (SELECT bl.line_number
FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number = ba.line_number
AND bl.batch_id = p_batch_id);
UPDATE pon_bid_attribute_values ba
SET ba.weighted_score =
(SELECT decode(nvl(aa.scoring_type, 'NONE'),
'NONE', null,
aa.weight / 100.0 * nvl(ba.score, 0))
FROM pon_auction_attributes aa
WHERE aa.auction_header_id = ba.auction_header_id
AND aa.line_number = ba.line_number
AND aa.sequence_number = ba.sequence_number)
WHERE ba.bid_number = p_bid_number
AND EXISTS (SELECT bl.line_number
FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number = ba.line_number
AND bl.batch_id = p_batch_id);
UPDATE pon_bid_item_prices bl
SET bl.total_weighted_score =
(SELECT decode(sum(sign(aa.weight)),
null, decode(bl.has_bid_flag, 'Y', 100, null),
0, decode(bl.has_bid_flag, 'Y', 100, null),
sum(decode(sign(aa.weight), 1, nvl2(ba.value, 1, 0), 0)),
sum(aa.weight / 100.0 * nvl(ba.score, 0)), null)
FROM pon_bid_attribute_values ba, pon_auction_attributes aa
WHERE ba.bid_number = bl.bid_number
AND ba.line_number = bl.line_number
AND aa.auction_header_id = ba.auction_header_id
AND aa.line_number = ba.line_number
AND aa.sequence_number = ba.sequence_number)
WHERE bl.bid_number = p_bid_number
AND bl.batch_id = p_batch_id;
SELECT decode(count(bh.bid_number), 0, 'N', 'Y')
INTO l_header_modified
FROM pon_bid_headers bh
WHERE bh.bid_number = p_bid_number
AND ((bh.old_note_to_auction_owner IS null
AND bh.note_to_auction_owner IS NOT null
OR bh.old_note_to_auction_owner IS NOT null
AND bh.note_to_auction_owner IS null
OR bh.old_note_to_auction_owner <> bh.note_to_auction_owner)
OR nvl(bh.old_bidders_bid_number, -1) <> nvl(bh.bidders_bid_number, -1)
OR (bh.old_bid_expiration_date IS null
AND bh.bid_expiration_date IS NOT null
OR bh.old_bid_expiration_date IS NOT null
AND bh.bid_expiration_date IS null
OR bh.old_bid_expiration_date <> bh.bid_expiration_date)
OR nvl(bh.old_min_bid_change, -1) <> nvl(bh.min_bid_change, -1)
-- surrog bid receipt date must be non-null if surrogate bid
OR bh.surrog_bid_flag = 'Y'
AND bh.old_surrog_bid_receipt_date <> bh.surrog_bid_receipt_date
-- Check if a header attribute was modified
OR EXISTS
(SELECT null
FROM pon_bid_attribute_values ba
WHERE ba.bid_number = p_bid_number
AND ba.line_number = -1
AND (ba.old_value <> ba.value
OR ba.old_value IS null AND ba.value IS NOT null
OR ba.value IS null AND ba.old_value IS NOT null)));
SELECT Nvl(ALLOW_UNSOL_OFFER_LINES,'N')
INTO l_allow_unsol_lines
FROM pon_auction_headers_all
WHERE auction_header_id = p_auc_header_id;
SELECT Count(*)
INTO l_changed_unsol_line_count
FROM pon_bid_item_prices
WHERE bid_number = p_bid_number
AND auction_line_number = -1
AND Nvl(is_changed_line_flag,'N') = 'Y';
INSERT FIRST
-- When rebidding, the header or one line must be changed
/* Unsolicited Lines Project : During rebid, if neg lines are not changed, then check
if unsol line count is also zero. If so, throw error.
*/
WHEN p_rebid = 'Y' AND l_header_modified = 'N' AND
(s_changed_line_count = 0 AND (l_allow_unsol_lines = 'Y' AND l_changed_unsol_line_count = 0))THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_NAME,
BATCH_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE)
VALUES
(p_interface_type,
'PON_BID_HEADERS',
p_batch_id,
'PON_BID_NO_CHANGES',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
g_exp_date)
-- All lines must have recieved a bid if that is a requirement
-- Added the p_evaluation_flag condition for ER: Supplier Management: Supplier Evaluation
WHEN p_bid_all_lines = 'Y' AND s_avail_bidded_lines <> s_bidded_lines AND p_evaluation_flag = 'N' THEN
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_NAME,
BATCH_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE)
VALUES
(p_interface_type,
'PON_BID_HEADERS',
p_batch_id,
'PON_MUST_BIDALL_ITEMS' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
g_exp_date)
-- At least one line must have recieved a bid if the auction has lines
-- Added the p_evaluation_flag condition for ER: Supplier Management: Supplier Evaluation
/* modified for bug#12951763 : As this is a generic validation that should be fired at any stage of
rounds of bidding, removing bidding flag(p_rebid) from the condition */
-- WHEN p_rebid = 'N' AND p_auc_has_items = 'Y' AND s_has_bid_count = 0 AND p_evaluation_flag = 'N' THEN
/* 13565622 : Relax this validation, when priced unsolicited lines exist. */
WHEN p_auc_has_items = 'Y' AND s_has_bid_count = 0 AND p_evaluation_flag = 'N'
AND PON_UNSOL_UTIL_PKG.HAS_PRICED_UNSOL_RESP_LINES(p_bid_number) = 'N' THEN
/* modified for bug#12951763 */
INTO pon_interface_errors
(INTERFACE_TYPE,
TABLE_NAME,
BATCH_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
AUCTION_HEADER_ID,
BID_NUMBER,
EXPIRATION_DATE)
VALUES
(p_interface_type,
'PON_BID_HEADERS',
p_batch_id,
'PON_MUST_BID_ATLEAST_ONE' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
g_exp_date)
SELECT
count(bl.line_number) s_line_count,
-- modified by Allen Yang for surrogate bid bug #7703665, #8220778 2009/02/03
----------------------------------------------------------------------------
---sum(decode(bl.has_bid_flag, 'Y', 1, 0)) s_has_bid_count,
/* bug#10221791 changes for two part RFQ surrogate bid*/
sum(decode(bl.has_bid_flag, 'Y', 1,
decode(NVL(paha.Two_Part_Flag, 'N'), 'Y',
decode(NVL(paha.TECHNICAL_EVALUATION_STATUS, 'NOT_COMPLETED'), 'NOT_COMPLETED',
decode(NVL(pbh.surrog_bid_flag, 'N'), 'Y',
decode(NVL(paha.FULL_QUANTITY_BID_CODE, 'FULL_QTY_BIDS_REQD'), 'FULL_QTY_BIDS_REQD', 1,
Decode( Decode(paip.order_type_lookup_code,'QUANTITY','Y','N'),'N',1,
Decode( Decode(paha.contract_type,'STANDARD','Y','N'),'N',1,
0))), 0), 0), 0))) s_has_bid_count,
sum(decode(bl.is_changed_line_flag, 'Y', 1, 0)) s_changed_line_count,
sum(decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0)) s_avail_bidded_lines,
--sum(decode(bl.has_bid_flag, 'Y', decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0), 0)) s_bidded_lines
/* bug#10221791 changes for two part RFQ surrogate bid*/
sum(decode(bl.has_bid_flag, 'Y',
decode(paip.group_type, 'LINE', 1, 'LOT', 1, 'GROUP_LINE', 1, 0),
decode(NVL(paha.Two_Part_Flag, 'N'), 'Y',
decode(NVL(paha.TECHNICAL_EVALUATION_STATUS, 'NOT_COMPLETED'), 'NOT_COMPLETED',
decode(NVL(pbh.surrog_bid_flag, 'N'), 'Y',
decode(NVL(paha.FULL_QUANTITY_BID_CODE, 'FULL_QTY_BIDS_REQD'), 'FULL_QTY_BIDS_REQD',1,
Decode( Decode(paip.order_type_lookup_code,'QUANTITY','Y','N'),'N',1,
Decode( Decode(paha.contract_type,'STANDARD','Y','N'),'N',1,
0))), 0), 0), 0))) s_bidded_lines
----------------------------------------------------------------------------
FROM pon_bid_item_prices bl,
pon_auction_item_prices_all paip
-- added by Allen Yang for surrogate bid bug 7703665, #8220778 2009/02/03
---------------------------------------------------------------
, pon_auction_headers_all paha,
pon_bid_headers pbh
---------------------------------------------------------------
WHERE bl.bid_number = p_bid_number and
bl.auction_header_id = paip.auction_header_id and
bl.line_number = paip.line_number
-- added by Allen Yang for surrogate bid bug 7703665, #8220778 2009/02/03
---------------------------------------------------------------
and bl.bid_number = pbh.bid_number
and pbh.auction_header_id = paha.auction_header_id
---------------------------------------------------------------
AND nvl(bl.clm_info_flag, 'N') <> 'Y' -- CLM-CLIN/SLIN Changes
--CLM QA bug : 9835426:No need to offer on NC/NSP lines.
AND (Nvl(bl.clm_cost_constraint,'X') NOT IN ('NSP','NC'));
INSERT ALL
-- All lines in a GROUP must be bid on simultaneously
WHEN s_group_type = 'GROUP' AND
(SELECT decode(sum(decode(bl.has_bid_flag, 'Y', 1, 0)), 0, 'OK',
count(bl.bid_number), 'OK', 'N')
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
AND al.auction_header_id = p_auc_header_id
AND al.parent_line_number = s_line_number) = '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_GROUP_PARTIAL_BID_ERR',
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- If a lot has no bid, none of it's children can be bid on
WHEN s_group_type = 'LOT' AND s_has_bid_flag = 'N' AND EXISTS
(SELECT bl.bid_number
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
AND al.auction_header_id = p_auc_header_id
AND al.parent_line_number = s_line_number
AND bl.has_bid_flag = 'Y'
AND rownum = 1) 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_LOT_NOT_BID_ERR',
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
bl.line_number s_line_number,
bl.has_bid_flag s_has_bid_flag,
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,
al.group_type s_group_type,
al.document_disp_line_number s_document_disp_line_number,
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,
al.line_num_display s_clm_line_num_display
FROM pon_bid_item_prices bl
, pon_auction_item_prices_all al
, pon_bid_headers pbh
WHERE bl.bid_number = p_bid_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND al.auction_header_id = p_auc_header_id
AND al.group_type IN ('GROUP', 'LOT')
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT ALL
-- Header level field min_bid_change is required if there is a proxy line
WHEN p_min_bid_change IS null AND s_proxy_bid_limit_price 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_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,
decode(p_spreadsheet, g_xml_upload_mode,
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_DEC' || p_suffix),
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_MIN_DEC_NULL' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'ProxyBidLimitPrice',
s_proxy_bid_limit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- 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
-- 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,
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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
-- 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,
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Bid quantity should not exceed auction quantity
-- Does not apply for blanket agreements
WHEN p_blanket = 'N' AND s_bid_quantity > s_auc_quantity 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_INVALID_BID_QTY' || 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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- If the need_by_date is scored, then promised date is required
-- Does not apply for blanket agreements
WHEN p_blanket = 'N' AND s_need_by_date_scored = 'Y'
AND s_promised_date IS null
-- 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_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_PROMISED_DATE_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PromisedDate',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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
-- 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_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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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))
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_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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- EVENT BASED DELIVERY PROJECT
-- Promised Period should be positive.
WHEN p_blanket = 'N'
AND s_clm_del_event is not null
AND s_promised_period is not null
AND s_promised_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', '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_promised_period,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Promised POP Duration should be positive.
WHEN p_blanket = 'N'
AND s_clm_del_event is not null
AND s_promised_pop_duration is not null
AND s_promised_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', 'PON_CLM_PROMISE_PERIOD'),
'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_promised_pop_duration,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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 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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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')
-- 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_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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_unit_price precision should not exceed price precision
-- as part of bug 14657112, removed the condition checking
-- whether price factors are displayed.
-- Precision should be checked in any case
WHEN validate_price_precision(
s_bid_currency_unit_price, p_price_precision) = 'F'
-- 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_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_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- If the line has price factors, the line price is required
WHEN s_display_price_factors_flag = 'Y'
AND s_bid_currency_unit_price IS null
-- 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_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_BID_PRICE_REQUIRED' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyUnitPrice',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
sysdate s_current_date,
decode(al.price_disabled_flag, 'Y', 'N',
decode(bl.display_price_factors_flag, 'Y', 'N',
Decode(Nvl(al.clm_info_flag,'N'),'Y','N',-- added for clm clin/slin proj
decode(al.group_type, 'GROUP', 'N',
Decode(Nvl(al.clm_cost_constraint,'X'),'NSP','N','NC','N','Y'))))) s_price_editable, -- CLM QA bug : 9835426 : If cost constraint is NC or NSP, price is not editable
decode(p_full_qty_reqd, 'Y', 'N',
decode(al.quantity_disabled_flag, 'Y', 'N',
decode(al.group_type, 'GROUP', 'N',
Decode(Nvl(al.clm_info_flag,'N'),'Y','N', -- added for clm clin/slin proj
decode(al.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, --CLM bug : 10212430 : POP Start date.
bl.PROMISE_POP_END_DATE s_clm_pop_end_date, --CLM bug : 10212430 : POP End date.
-- Event Based Delivery Project
al.CLM_DELIVERY_EVENT_CODE s_clm_del_event,
bl.CLM_PROMISE_PERIOD s_promised_period,
bl.CLM_PROMISE_POP_DURATION s_promised_pop_duration,
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.proxy_bid_limit_price s_proxy_bid_limit_price,
bl.display_price_factors_flag s_display_price_factors_flag,
al.is_need_by_date_scored s_need_by_date_scored,
al.quantity s_auc_quantity,
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,
al.document_disp_line_number s_document_disp_line_number,
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,
al.line_num_display s_clm_line_num_display
FROM pon_auction_item_prices_all al
, pon_bid_item_prices bl
, pon_bid_headers pbh
WHERE al.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND al.line_number = bl.line_number
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT ALL
-- bid_currency_price should be positive
WHEN s_bid_currency_price <= 0
-- 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_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_document_disp_line_number,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_price precision should not exceed price precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
/*For clm bug : 10136754 : For federal : Only goods line types should be validated against solicitation precision */
( (( nvl(g_is_fed,'N')='Y' AND s_order_type_lookup_code = 'QUANTITY') OR nvl(g_is_fed,'N')='N') AND
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_price precision should not exceed price precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
/*For clm bug : 10136754 : For federal : Only goods line types should be validated against solicitation precision
* Amount/Firm Fixed price lines should be validated against fnd precision.
*/
( (nvl(g_is_fed,'N')='Y' AND s_order_type_lookup_code <> 'QUANTITY') AND
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- In untransformed view, bid_currency_trans_price should be positive
-- since bid_currency_price is the same as bid_currency_unit_price
-- Do not report the error if one will be reported for bid_currency_price
WHEN p_trans_view = 'Y' AND s_bid_currency_price > 0
AND s_bid_currency_trans_price <= 0
-- 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,
'PON_LINE_BIDPRICE_INVALID_1' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyTransPrice',
s_bid_currency_trans_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_limit_price should be positive
WHEN s_bid_currency_limit_price <= 0
-- 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,
decode(p_spreadsheet,
g_xml_upload_mode,
fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUC_LIMPRICE_MUST_BE_POS',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyLimitPrice',
s_bid_currency_limit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_limit_price precision should not exceed price precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
validate_price_precision(s_bid_currency_limit_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,
decode(p_spreadsheet,
g_xml_upload_mode,
fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_LIMIT_MIN_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyLimitPrice',
s_bid_currency_limit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- price should be lower then the line start price.
-- Will not apply if price is disabled for this line
-- bug 5701482
-- need use the bid currency price and do round up
-- to precision to avoid the small gap caused by
-- currency conversion
-- If the precision is ANY, round up to 10 decimal points
-- bug 12682523
-- using cost factors to transform s_enforced_bid_start_price inaddition to applying rate for currency conversion before validation
WHEN s_price_disabled_flag = 'N' AND
s_bid_currency_price > ROUND(decode(p_trans_view, 'Y', s_enforced_bid_start_price,
PON_TRANSFORM_BIDDING_PKG.untransform_one_price(p_auc_header_id,
s_line_number,
s_enforced_bid_start_price,
s_auc_quantity,
p_tpid,
p_vensid
)
)*s_rate, decode(s_precision, 10000, 10, s_precision)
)
-- 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,
TOKEN2_NAME,
TOKEN2_VALUE,
TOKEN3_NAME,
TOKEN3_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_BP_LTE_BIDSTARTPRICE' || 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,
'BIDPRICE',
format_price(s_bid_currency_price, l_price_mask, p_price_precision),
'STARTPRICE',
format_price(decode(p_trans_view, 'Y',
s_enforced_bid_start_price,
PON_TRANSFORM_BIDDING_PKG.untransform_one_price( p_auc_header_id,
s_line_number,
s_enforced_bid_start_price,
s_auc_quantity,
p_tpid,
p_vensid)) * p_rate, l_price_mask, p_price_precision),
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- When rebidding, the new price must be lower than the old_price
-- by the specified minimum bid decrement, if it was changed.
-- Does not apply to LOT_LINEs
-- Will not apply if price is disabled for this line, or negotiation
-- is not price driver
WHEN s_price_disabled_flag = 'N' AND p_rebid = 'Y' AND p_price_driven = 'Y'
AND p_bid_decr_method <> 'BEST_PRICE'
AND s_price <> s_old_price AND s_group_type <> 'LOT_LINE'
AND s_price + (nvl(s_min_bid_decr,0)/p_rate) > s_old_price
AND s_min_bid_decr IS NOT NULL
-- 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,
TOKEN2_NAME,
TOKEN2_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_MIN_BID_DECREMENT' || 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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'BIDDECREMENT',
format_price(s_min_bid_decr, l_price_mask, p_price_precision) || ' '||p_bid_curr_code,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code
)
-- When rebidding, the new price must be lower than the best_bid_price
-- by the specified minimum bid decrement, if suppliers are
-- supposed to reduce by best response price
-- Does not apply to LOT_LINEs
-- Will not apply if price is disabled for this line, or negotiation
-- is not price driven
WHEN s_price_disabled_flag = 'N' AND p_bid_decr_method = 'BEST_PRICE' AND p_price_driven = 'Y'
AND s_group_type <> 'LOT_LINE'
AND p_rebid = 'Y'
AND s_auc_best_bid_price IS NOT NULL
AND s_price <> s_old_price
AND s_price + (nvl(s_min_bid_decr,0)/p_rate) > s_best_bid_price
AND s_min_bid_decr IS NOT NULL
-- 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,
TOKEN2_NAME,
TOKEN2_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_MIN_BESTBID_DECREMENT' || 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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'BIDDECREMENT',
format_price(s_min_bid_decr, l_price_mask, p_price_precision) || ' '||p_bid_curr_code,
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 s_price_disabled_flag = 'N' AND p_rebid = 'Y' AND p_price_driven = 'Y'
AND s_price <> s_old_price AND s_group_type <> 'LOT_LINE'
AND s_price > decode(p_bid_decr_method,'BEST_PRICE',s_best_bid_price,s_old_price)
AND s_min_bid_decr IS NULL
-- 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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- proxy_bid_limit_price must be lower than price by the bid minimum
-- bid change (or auction minimum bid change if that is null)
-- Don't do this validation when buyer tries to place a
-- surrogate bid after auction is closed
WHEN (NOT (s_surrog_bid_flag = 'Y' AND
s_close_bidding_date < s_current_date) AND
(s_proxy_bid_limit_price + s_min_bid_change > s_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,
decode(p_spreadsheet,
g_xml_upload_mode,
fnd_message.get_string('PON', 'PON_AUCTS_PROXY_MIN'),
fnd_message.get_string('PON', 'PON_AUCTS_MIN_BID_PRICE')),
'PON_BID_ITEM_PRICES',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_LIMIT_MIN_LESS_PRI' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'BidCurrencyLimitPrice',
s_proxy_bid_limit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
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,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- CUMULATIVE price breaks not allowed in blanket agreements
WHEN p_blanket = 'Y' AND p_global = 'Y'
AND s_price_break_type = 'CUMULATIVE'
--CLM QA Bug : 9835426 : For NC, NSP lines, Price Tiers are not allowed
AND s_clm_cost_constraint NOT IN ('NC','NSP')
-- 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,
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,
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_AUC_BAD_PBTYPE_GLOBAL',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PriceBreakType',
s_price_break_type,
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- The line is not allowed to have shipments if price_break_type is NONE
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
s_price_break_type = 'NONE' AND
EXISTS (SELECT bpb.shipment_number
FROM pon_bid_shipments bpb
WHERE bpb.bid_number = p_bid_number
AND bpb.line_number = s_line_number
AND bpb.shipment_type = 'PRICE BREAK')
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,
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_AUC_BAD_PBTYPE_SHIPS',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
'PriceBreakType',
s_price_break_type,
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
decode(p_percent_decr, 'Y',
-- Bug 12943462
-- Modified the Bid decrement price for both the Controls Best Price checked and unchecked
p_min_bid_decr * (decode(p_bid_decr_method,'BEST_PRICE',nvl(al.best_bid_price,0),bl.old_price))/100,--bug 7601028:calculating the minimum decrement amount on the basis of previous bid
p_min_bid_decr) s_min_bid_decr,
decode(p_percent_decr, 'Y',
nvl(p_min_bid_change, p_min_bid_decr) * bl.price/100, --bug 7601028:calculating the minimum change amount on the basis of previous bid
nvl(p_min_bid_change, p_min_bid_decr)) s_min_bid_change,
al.group_type s_group_type,
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,
al.quantity s_auc_quantity,
al.bid_start_price s_bid_start_price,
nvl(al.price_disabled_flag, 'N') s_price_disabled_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,
al.document_disp_line_number s_document_disp_line_number,
nvl(al.best_bid_price,0) s_best_bid_price,
al.best_bid_price s_auc_best_bid_price,
bl.bid_start_price s_enforced_bid_start_price,
bh.surrog_bid_flag s_surrog_bid_flag,
al.close_bidding_date s_close_bidding_date,
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,
al.line_num_display s_clm_line_num_display,
nvl(bl.clm_cost_constraint,'X') s_clm_cost_constraint, -- CLM QA Bug : 9835426
al.order_type_lookup_code s_order_type_lookup_code --For clm bug : 10136754
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl,
pon_bid_headers bh
WHERE al.auction_header_id = p_auc_header_id
AND bh.bid_number = p_bid_number
AND bl.bid_number = p_bid_number
AND al.line_number = bl.line_number
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND bh.bid_number = bl.bid_number
AND (bh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT ALL
-- Retainage rate should be between 0 and 100
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RTNG_RATE_WRONG_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RetainageRatePercent', retainage_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', --s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- bid_curr_max_retainage_amt should be greater than equal to 0
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
bid_curr_max_retainage_amt IS NOT NULL AND bid_curr_max_retainage_amt < 0 THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_MAX_RTNG_WRONG_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', --s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- bid_curr_advance_amount should be greater than equal to 0
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
bid_curr_advance_amount IS NOT NULL AND bid_curr_advance_amount < 0 THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_ADV_AMT_WRONG_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrAdvanceAmount', bid_curr_advance_amount, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', --s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- progress_pymt_rate_percent should be between 0 and 100
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
progress_pymt_rate_percent IS NOT NULL AND
(progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_PROG_PYMT_RATE_WRONG_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'ProgressPymtRatePercent', progress_pymt_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', --s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- recoupment_rate_percent should be between 0 and 100
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
recoupment_rate_percent IS NOT NULL AND
(recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RECOUP_RATE_WRONG', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RecoupmentRatePercent', recoupment_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', --s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- progress_pymt_rate_percent is mandatory for progress_payment_type = FINANCE
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
progress_payment_type = 'FINANCE' AND contract_type='STANDARD'
AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND progress_pymt_rate_percent IS NULL THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_PROG_PYMT_NEEDED_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'ProgressPymtRatePercent', progress_pymt_rate_percent, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
null, null,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- retainage_rate_percent is mandatory if retainage is negotiable
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
retainage_negotiable_flag = 'Y'
AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND retainage_rate_percent IS NULL THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RETAINAGE_NEEDED_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RetainageRatePercent', retainage_rate_percent, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
null, null,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- bid_curr_max_retainage_amount is mandatory if maximum retainage amount is negotiable
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
max_retainage_negotiable_flag = 'Y'
AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND bid_curr_max_retainage_amt IS NULL THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_MAX_RETAINAGE_NEEDED_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
null, null,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- recoupment_rate_percent is mandatory if recoupment rate is negotiable
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
(s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND
((recoupment_negotiable_flag = 'Y' AND recoupment_rate_percent IS NULL) OR
((progress_pymt_rate_percent IS NOT NULL OR bid_curr_advance_amount IS NOT NULL) AND
recoupment_rate_percent IS NULL)))
THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RECUP_RATE_NEEDED_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RecoupmentRatePercent', recoupment_rate_percent, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
null, null,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- bid_curr_advance_amount is mandatory if advance amount is negotiable
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
advance_negotiable_flag = 'Y'
AND s_group_type NOT IN ('GROUP','LOT_LINE')
AND s_clm_info_flag <> 'Y'
AND bid_curr_advance_amount IS NULL THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_ADVANCE_AMT_NEEDED_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrAdvanceAmount', bid_curr_advance_amount, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
null, null, s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
SELECT
al.group_type s_group_type,
bl.quantity s_bid_quantity,
bl.bid_currency_unit_price s_bid_currency_unit_price,
bl.bid_curr_advance_amount,
bl.bid_curr_max_retainage_amt,
bl.recoupment_rate_percent,
bl.retainage_rate_percent,
bl.progress_pymt_rate_percent,
bl.has_bid_payments_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,
al.document_disp_line_number s_document_disp_line_number,
al.order_type_lookup_code s_order_type_lookup_code,
al.quantity s_auc_quantity,
pah.progress_payment_type,
pah.advance_negotiable_flag,
pah.retainage_negotiable_flag,
pah.max_retainage_negotiable_flag,
pah.recoupment_negotiable_flag,
pah.progress_pymt_negotiable_flag,
pah.contract_type,
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,
al.line_num_display s_clm_line_num_display,
Nvl(al.clm_info_flag, 'N') s_clm_info_flag
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl, pon_auction_headers_all pah
WHERE al.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND pah.auction_header_id = p_auc_header_id
AND al.line_number = bl.line_number
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
INSERT ALL
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
bid_curr_advance_amount IS NOT NULL AND s_bid_currency_unit_price IS NOT NULL AND
(bid_curr_advance_amount > (NVL(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, --7
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_ADV_AMT_MORE_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
NULL, bid_curr_advance_amount, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),--s_document_disp_line_number, --7
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
(p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'FINANCE' AND
recoupment_rate_percent IS NOT NULL AND progress_pymt_rate_percent IS NOT NULL AND
s_bid_currency_unit_price IS NOT NULL AND
recoupment_rate_percent < (((((progress_pymt_rate_percent/100) * (SELECT nvl(sum(nvl(bid_currency_price,0)*nvl(quantity,nvl(s_bid_quantity,1))),0)
FROM PON_BID_PAYMENTS_SHIPMENTS p_bps
WHERE p_bps.auction_header_id=p_auc_header_id
AND p_bps.bid_line_number=s_line_number
AND p_bps.bid_number=p_bid_number))
+ nvl(bid_curr_advance_amount,0)) * 100)/((nvl(s_bid_quantity, 1) * s_bid_currency_unit_price))) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,--7
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RECOUP_LESS_THAN_PYMT_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
NULL, recoupment_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),--7
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
(p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'ACTUAL' AND
recoupment_rate_percent IS NOT NULL AND bid_curr_advance_amount IS NOT NULL AND
s_bid_currency_unit_price IS NOT NULL AND
recoupment_rate_percent <((bid_curr_advance_amount * 100)/(nvl(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,--7
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_RECOUP_LESS_THAN_ADV_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
NULL, recoupment_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), --7
--s_document_disp_line_number,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
(p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'ACTUAL' AND
s_group_type NOT IN ('GROUP','LOT_LINE') AND
has_bid_payments_flag = 'Y' AND
s_bid_currency_unit_price IS NOT NULL AND
round(nvl(s_bid_quantity,1)* s_bid_currency_unit_price,p_amt_precision) <> s_sum_pymt_amt --bug 14657112
THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE,
TOKEN3_NAME, TOKEN3_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, NULL, 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_PYMNT_AMT_MORE_ACTUAL', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
NULL, NULL, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date,
'LINEAMT', round(nvl(s_bid_quantity,1)* s_bid_currency_unit_price,p_amt_precision),
'PYMTAMT', s_sum_pymt_amt,
'LINENUM', s_document_disp_line_number,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
(p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode ) AND progress_payment_type = 'FINANCE' AND
has_bid_payments_flag = 'Y' AND
s_group_type NOT IN ('GROUP','LOT_LINE') AND
s_bid_currency_unit_price IS NOT NULL AND
(nvl(s_bid_quantity,1)* s_bid_currency_unit_price)- nvl(bid_curr_advance_amount,0) < (SELECT nvl(sum(nvl(bid_currency_price,0) * DECODE(s_order_type_lookup_code,'GOODS',NVL(s_auc_quantity,1),NVL(quantity,1))),0)
FROM pon_bid_payments_shipments
WHERE auction_header_id = p_auc_header_id
AND bid_number = p_bid_number
AND bid_line_number = s_line_number) THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --7
VALUES
(p_interface_type, NULL, 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_PYMNT_AMT_MORE_FINANCE', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
NULL, NULL, 'TXT', --5
p_auc_header_id, p_bid_number, s_line_number, --6
g_exp_date, 'LINENUM', Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),--s_document_disp_line_number,
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --7
-- bid_curr_advance_amount should not exceed currency precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
bid_curr_advance_amount IS NOT NULL AND
validate_currency_precision(bid_curr_advance_amount, p_amt_precision) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_LINEAMT_INVALID_PRECISION', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrAdvanceAmount', bid_curr_advance_amount, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- bid_curr_max_retainage_amt should not exceed currency precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
bid_curr_max_retainage_amt IS NOT NULL AND
validate_currency_precision(bid_curr_max_retainage_amt, p_amt_precision) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_LINEAMT_INVALID_PRECISION', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'BidCurrMaxRetainageAmt', bid_curr_max_retainage_amt, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- progress_pymt_rate_percent should not exceed currency precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
progress_pymt_rate_percent IS NOT NULL AND
validate_currency_precision(progress_pymt_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'ProgressPymtRatePercent', progress_pymt_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- retainage_rate_percent should not exceed currency precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
retainage_rate_percent IS NOT NULL AND
validate_currency_precision(retainage_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME, WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RetainageRatePercent', retainage_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RETAINAGE_RATE'),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
-- recoupment_rate_percent should not exceed currency precision
WHEN
-- added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
p_two_part_tech_surrogate_flag = 'N' AND
---------------------------------------------------
recoupment_rate_percent IS NOT NULL AND
validate_currency_precision(recoupment_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
ENTITY_ATTR_NAME, ERROR_VALUE_NUMBER, ERROR_VALUE_DATATYPE, --5
AUCTION_HEADER_ID, BID_NUMBER, LINE_NUMBER, --6
BID_PAYMENT_ID, EXPIRATION_DATE, TOKEN1_NAME, --7
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, WORKSHEET_NAME,WORKSHEET_SEQUENCE_NUMBER, ENTITY_MESSAGE_CODE) --8
VALUES
(p_interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_BID_ITEM_PRICES', --1
p_batch_id, s_interface_line_id, 'PON_INVALID_RATE_PRECISION_L', --2
p_userid, sysdate, p_userid, --3
sysdate, p_request_id, 'BID_ITEMS', --4
'RecoupmentRatePercent', recoupment_rate_percent, 'NUM', --5
p_auc_header_id, p_bid_number, s_line_number, --6
NULL, g_exp_date, 'LINENUM', --7
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number), 'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RECOUPMENT_RATE'),
s_worksheet_name, s_worksheet_sequence_number, s_entity_message_code) --8
SELECT
al.group_type s_group_type,
bl.quantity s_bid_quantity,
bl.bid_currency_unit_price s_bid_currency_unit_price,
bl.bid_curr_advance_amount,
bl.bid_curr_max_retainage_amt,
bl.recoupment_rate_percent,
bl.retainage_rate_percent,
bl.progress_pymt_rate_percent,
bl.has_bid_payments_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,
al.document_disp_line_number s_document_disp_line_number,
al.order_type_lookup_code s_order_type_lookup_code,
al.quantity s_auc_quantity,
pah.progress_payment_type,
pah.advance_negotiable_flag,
pah.retainage_negotiable_flag,
pah.max_retainage_negotiable_flag,
pah.recoupment_negotiable_flag,
pah.progress_pymt_negotiable_flag,
pah.contract_type,
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,
al.line_num_display s_clm_line_num_display,
(SELECT nvl(Sum (round(NVL(pbps.bid_currency_price,0) * NVL(pbps.quantity,nvl(bl.quantity,1)),p_amt_precision)),0)
FROM pon_bid_payments_shipments pbps
WHERE pbps.auction_header_id = p_auc_header_id
AND pbps.bid_number = p_bid_number
AND pbps.bid_line_number = bl.line_number) s_sum_pymt_amt --bug 14657112
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl, pon_auction_headers_all pah
WHERE al.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND pah.auction_header_id = p_auc_header_id
AND al.line_number = bl.line_number
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id);
INSERT FIRST
-- value must be entered if it is a required requirement
-- added the s_evaluation_flag condition for ER: Supplier Management: Supplier Evaluation
WHEN (s_mandatory_flag = 'Y' AND s_value IS null AND s_evaluation_flag = 'N'
--added by Allen Yang for Surrogate Bid 2008/09/03
-------------------------------------------------------------------
AND p_two_part_tech_surrogate_flag = 'N') OR (s_mandatory_flag = 'Y'
AND s_value IS null AND p_two_part_tech_surrogate_flag = 'Y'
AND s_two_part_section_type = 'TECHNICAL')
-------------------------------------------------------------------
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,
ATTRIBUTE_NAME,
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_VALUE' || p_suffix),
'PON_BID_ATTRIBUTE_VALUES',
p_batch_id,
s_interface_line_id,
'PON_AUC_HDR_ATTR_REQ' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ATTRS',
'Value',
'TXT',
p_auc_header_id,
p_bid_number,
s_attribute_name,
g_exp_date,
'ATTRIBUTENAME',
s_trunc_attr_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- value must be among buyer specified values if type is LOV
WHEN p_spreadsheet in (g_txt_upload_mode, g_xml_upload_mode) AND s_scoring_type = 'LOV' AND s_value is not NULL AND NOT EXISTS
(SELECT bs.score
FROM pon_attribute_scores bs
WHERE bs.auction_header_id = p_auc_header_id
AND bs.line_number = s_line_number
AND bs.attribute_sequence_number = s_sequence_number
AND bs.value = s_value) 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,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTRIBUTE_VALUES',
p_batch_id,
s_interface_line_id,
'PON_AUC_INVALID_ATTR_VALUE' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ATTRS',
'Value',
s_value,
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_attribute_name,
g_exp_date,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
ba.value s_value,
ba.line_number s_line_number,
ba.sequence_number s_sequence_number,
ba.attribute_name s_attribute_name,
-- bug 8411749 using SUBSTRB instead of SUBSTR
-- to calculate the length of the string in bytes
substrb(ba.attribute_name, 0, 2000) s_trunc_attr_name,
aa.mandatory_flag s_mandatory_flag,
aa.scoring_type s_scoring_type,
decode(p_spreadsheet, g_xml_upload_mode, ba.interface_line_id, null) s_interface_line_id,
decode(p_spreadsheet, g_xml_upload_mode, ba.worksheet_name, null) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, ba.worksheet_sequence_number, null) s_worksheet_sequence_number,
decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_REQUIREMENTS', null) s_entity_message_code
--added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
, pas.two_part_section_type s_two_part_section_type
---------------------------------------------------
---- Supplier Management: Supplier Evaluation -----
, nvl(pbh.evaluation_flag, 'N') s_evaluation_flag
---------------------------------------------------
FROM pon_bid_attribute_values ba, pon_auction_attributes aa
--added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
, pon_auction_sections pas
---------------------------------------------------
---- Supplier Management: Supplier Evaluation -----
, pon_bid_headers pbh
---------------------------------------------------
WHERE ba.bid_number = p_bid_number
AND ba.line_number = -1
AND aa.auction_header_id = ba.auction_header_id
AND aa.line_number = ba.line_number
AND aa.sequence_number = ba.sequence_number
AND (p_spreadsheet = g_online_mode OR ba.batch_id = p_batch_id)
--added by Allen Yang for Surrogate Bid 2008/09/03
---------------------------------------------------
AND pas.auction_header_id = aa.auction_header_id
AND pas.section_name = aa.section_name
---------------------------------------------------
---- Supplier Management: Supplier Evaluation -----
AND pbh.auction_header_id = ba.auction_header_id
AND pbh.bid_number = ba.bid_number
---------------------------------------------------
;
INSERT FIRST
-- value must be entered if it is a required attribute
WHEN s_mandatory_flag = 'Y' AND s_value IS 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_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTRIBUTE_VALUES',
p_batch_id,
s_interface_line_id,
'PON_AUC_ATTR_VALUE_REQ' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ATTRS',
'Value',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_attribute_name,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'ATTRIBUTENAME',
s_trunc_attr_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- value must be among buyer specified values if type is LOV
WHEN p_spreadsheet in (g_txt_upload_mode, g_xml_upload_mode) AND s_scoring_type = 'LOV' AND s_value is not NULL AND NOT EXISTS
(SELECT bs.score
FROM pon_attribute_scores bs
WHERE bs.auction_header_id = p_auc_header_id
AND bs.line_number = s_line_number
AND bs.attribute_sequence_number = s_sequence_number
AND bs.value = s_value) 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,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
EXPIRATION_DATE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_BID_VALUE' || p_suffix),
'PON_BID_ATTRIBUTE_VALUES',
p_batch_id,
s_interface_line_id,
'PON_AUC_INVALID_ATTR_VALUE' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ATTRS',
'Value',
s_value,
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_attribute_name,
g_exp_date,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
ba.value s_value,
ba.line_number s_line_number,
ba.sequence_number s_sequence_number,
ba.attribute_name s_attribute_name,
substr(ba.attribute_name, 0, 2000) s_trunc_attr_name,
aa.mandatory_flag s_mandatory_flag,
aa.scoring_type s_scoring_type,
al.document_disp_line_number s_document_disp_line_number,
decode(p_spreadsheet, g_xml_upload_mode, ba.interface_line_id, g_txt_upload_mode, bl.interface_line_id, null) s_interface_line_id,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_sequence_number,
decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_ATTRIBUTES', null) s_entity_message_code,
al.line_num_display s_clm_line_num_display
FROM pon_bid_attribute_values ba
, pon_auction_attributes aa
, pon_auction_item_prices_all al
, pon_bid_item_prices bl
, pon_bid_headers pbh
WHERE ba.bid_number = p_bid_number
AND ba.line_number <> -1
AND aa.auction_header_id = ba.auction_header_id
AND aa.line_number = ba.line_number
AND aa.sequence_number = ba.sequence_number
AND al.auction_header_id = ba.auction_header_id
AND al.line_number = ba.line_number
AND bl.bid_number = ba.bid_number
AND bl.line_number = ba.line_number
AND nvl(bl.is_changed_line_flag, 'Y') = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT FIRST
-- bid_currency_value must recieve a bid
WHEN s_bid_currency_value IS 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_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
PRICE_ELEMENT_TYPE_ID,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
s_column_name,
'PON_BID_PRICE_ELEMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PE_VALUE_REQ' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PFS',
'BidCurrencyValue',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_price_element_type_id,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEELEMENTNAME',
s_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Line price cost factor must be positive
WHEN s_sequence_number = -10 AND s_bid_currency_value <= 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,
PRICE_ELEMENT_TYPE_ID,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
entity_message_code)
VALUES
(p_interface_type,
s_column_name,
'PON_BID_PRICE_ELEMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PE_MUST_BE_POS' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PFS',
'BidCurrencyValue',
s_bid_currency_value,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_price_element_type_id,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEELEMENTNAME',
s_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- Cost factor value must be postive or zero if not line price
WHEN s_sequence_number <> -10 AND s_bid_currency_value < 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,
PRICE_ELEMENT_TYPE_ID,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
s_column_name,
'PON_BID_PRICE_ELEMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PE_MUST_BE_POS_ZERO' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PFS',
'BidCurrencyValue',
s_bid_currency_value,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_price_element_type_id,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEELEMENTNAME',
s_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_precision must not exceed price precision for PER_UNIT cf
WHEN s_pricing_basis = 'PER_UNIT'
AND validate_price_precision(s_bid_currency_value,
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,
PRICE_ELEMENT_TYPE_ID,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
s_column_name,
'PON_BID_PRICE_ELEMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PE_INVALID_BID_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PFS',
'BidCurrencyValue',
s_bid_currency_value,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_price_element_type_id,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEELEMENTNAME',
s_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
-- bid_currency_precision must not exceed currency precision for FIXED_AMOUNT cf
WHEN s_pricing_basis = 'FIXED_AMOUNT'
AND validate_currency_precision(s_bid_currency_value,
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,
PRICE_ELEMENT_TYPE_ID,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
entity_message_code)
VALUES
(p_interface_type,
s_column_name,
'PON_BID_PRICE_ELEMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PE_INVALID_CURR_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PFS',
'BidCurrencyValue',
s_bid_currency_value,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_price_element_type_id,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEELEMENTNAME',
s_name,
s_worksheet_name,
s_worksheet_sequence_number,
s_entity_message_code)
SELECT
bpf.bid_currency_value s_bid_currency_value,
bpf.sequence_number s_sequence_number,
bpf.pricing_basis s_pricing_basis,
bpf.line_number s_line_number,
bpf.price_element_type_id s_price_element_type_id,
al.document_disp_line_number s_document_disp_line_number,
decode(p_spreadsheet, g_txt_upload_mode, bl.interface_line_id,
g_xml_upload_mode, bpf.interface_line_id,
to_number(null)) s_interface_line_id,
pft.name s_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, to_char(null)) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, 'PON_AUC_PRICE_ELEMENTS', to_char(null)) s_entity_message_code,
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,
fnd_message.get_string('PON', 'PON_AUCTS_ATTR_BID_VALUE' || p_suffix),
fnd_message.get_string('PON', 'PON_AUC_PE_BID_VALUE_REQ' || p_suffix)) s_column_name,
al.line_num_display s_clm_line_num_display
FROM pon_bid_price_elements bpf
, pon_auction_item_prices_all al
, pon_bid_item_prices bl
, pon_price_element_types_tl pft
, pon_bid_headers pbh
WHERE bpf.bid_number = p_bid_number
AND bpf.pf_type = 'SUPPLIER' -- only validate SUPPLIER cost factors
AND al.auction_header_id = bpf.auction_header_id
AND al.line_number = bpf.line_number
AND bl.bid_number = bpf.bid_number
AND bl.line_number= bpf.line_number
AND pft.price_element_type_id = bpf.price_element_type_id
AND pft.language = userenv('LANG')
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT ALL
-- quantity must be positive or zero
WHEN s_quantity < 0 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PB_QUANTITY_POSITIVE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'Quantity',
s_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- bid_currency_unit_price must be positive
-- Only applier if price_type is PRICE
WHEN s_price_type = 'PRICE' AND s_bid_currency_unit_price < 0 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_POS_OR_ZERO',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- bid_currency_unit_price precision must not exceed price precision
-- Only applier if price_type is PRICE
WHEN s_price_type = 'PRICE' AND validate_price_precision(
s_bid_currency_unit_price, p_price_precision) = 'F' 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_INVALID_BID_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- The transformed price should be positive
-- Since bid_currency_price is the same as bid_currency_unit_price
-- in untransformed view, we can use s_price (which is the
-- transformed price in auction currency)
-- NOTE: rate conversion is unneccessary since we only check sign
WHEN p_trans_view = 'N' AND s_price < 0 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_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
(p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
s_interface_line_id,
'PON_PB_BIDPRICE_INVALID_1',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- bid_currency_price must be positive
WHEN p_trans_view = 'Y' AND s_bid_currency_price < 0 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_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
(p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
s_interface_line_id,
decode(p_header_disp_pf, 'Y',
'PON_PB_BIDPRICE_INVALID_2' || p_suffix,
'PON_AUC_PB_POS_OR_ZERO'),
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- bid_currency_price precision must not exceed price precision
WHEN validate_price_precision(
s_bid_currency_price, p_price_precision) = 'F' 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_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
(p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_INVALID_BID_PREC' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'BidCurrencyPrice',
s_bid_currency_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- price_discount must be 0 to 100
-- Only applies if price_type is PRICE DISCOUNT
WHEN s_price_type = 'PRICE DISCOUNT'
AND (s_price_discount < 0 OR s_price_discount > 100) 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_INVALID_PRICE_DISCOUNT',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'PriceDiscount',
s_price_discount,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_start_date must be past the current date
WHEN s_effective_start_date < s_current_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_FROMDATE_AFTER_CURDATE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveStartDate',
s_effective_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_start_date must be after po start date
WHEN s_effective_start_date < p_po_start_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_BAD_PB_EFF_FDATE_2',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveStartDate',
s_effective_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_start_date must be before po end date
WHEN s_effective_start_date > p_po_end_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_BAD_PB_EFF_FDATE_3',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveStartDate',
s_effective_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective start date must be after auction close date
WHEN s_effective_start_date < p_auc_close_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_FROM'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_BAD_PB_EFF_FDATE_1',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveStartDate',
s_effective_start_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_end_date must be after current date
WHEN s_effective_end_date < s_current_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_TODATE_AFTER_CURDATE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveEndDate',
s_effective_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_end_date must be after po start date
WHEN s_effective_end_date < p_po_start_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_BAD_PB_EFF_TDATE_2',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveEndDate',
s_effective_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_end_date must be before po end date
WHEN s_effective_end_date > p_po_end_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_BAD_PB_EFF_TDATE_3',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveEndDate',
s_effective_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_end_date must be after auction close date
WHEN s_effective_end_date < p_auc_close_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
p_batch_id,
s_interface_line_id ,
'PON_AUC_BAD_PB_EFF_TDATE_1',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveEndDate',
s_effective_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- To be considered valid, the price break must have one of the following:
-- ship_to_organization_id, ship_location_id, quantity,
-- effective_start_date, effective_end_date
WHEN s_ship_to_organization_id IS null
AND s_ship_to_location_id IS null
AND s_quantity IS null
AND s_effective_start_date IS null
AND s_effective_end_date 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,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
ERROR_VALUE_DATATYPE,
ERROR_VALUE,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PB_MUST_BE_ENTERED',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
'TXT',
'',
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- effective_start_date must be before effective_end_date
WHEN s_effective_start_date > s_effective_end_date 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_AUCTS_EFFECTIVE_TO'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_INVALID_EFF_DATES',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
'EffectiveEndDate',
s_effective_end_date,
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- Either bid_currency_unit_price or price_discount must have a value
WHEN s_bid_currency_unit_price IS null AND s_price_discount 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,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
ERROR_VALUE_DATATYPE,
ERROR_VALUE,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_PRICE_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
'TXT',
'',
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- CUMULATIVE price breaks cannot have start and end dates
WHEN s_price_break_type = 'CUMULATIVE'
AND (s_effective_start_date IS NOT null
OR s_effective_end_date IS NOT 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_DATE,
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
(p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PB_CUMM_EFF_DATES',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PBS',
nvl2(s_effective_start_date, 'EffectiveEndDate',
'EffectiveStartDate'),
nvl(s_effective_end_date, s_effective_end_date),
'DAT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
SELECT
sysdate s_current_date,
bpb.ship_to_organization_id s_ship_to_organization_id,
bpb.ship_to_location_id s_ship_to_location_id,
bpb.quantity s_quantity,
bpb.price s_price,
bpb.price_type s_price_type,
bpb.bid_currency_price s_bid_currency_price,
bpb.bid_currency_unit_price s_bid_currency_unit_price,
bpb.price_discount s_price_discount,
bpb.effective_start_date s_effective_start_date,
bpb.effective_end_date s_effective_end_date,
bpb.line_number s_line_number,
bpb.shipment_number s_shipment_number,
decode(p_spreadsheet, g_xml_upload_mode,
bpb.interface_line_id,
bl.interface_line_id
) s_interface_line_id,
al.document_disp_line_number s_document_disp_line_number,
al.price_break_type s_price_break_type,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
decode(p_spreadsheet, g_xml_upload_mode,
'PON_AUCTS_PRICE_BREAKS',
null
) s_entity_name,
al.line_num_display s_clm_line_num_display
FROM pon_bid_shipments bpb
, pon_bid_item_prices bl
, pon_auction_item_prices_all al
, pon_bid_headers pbh
WHERE bpb.bid_number = p_bid_number
AND bl.bid_number = bpb.bid_number
AND bl.line_number = bpb.line_number
AND al.auction_header_id = bpb.auction_header_id
AND al.line_number = bpb.line_number
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT ALL
-- The min quantity is a required field. If the min quantity is null,
-- we insert rows into the interface errors table.
WHEN
(
s_min_quantity IS NULL
OR
s_min_quantity = g_null_int
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PT_MIN_QUANTITY_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'Quantity',
s_min_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- The max quantity is a required field. If the min quantity is null,
-- we insert rows into the interface errors table.
WHEN
(
s_max_quantity IS NULL
OR
s_max_quantity = g_null_int
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PT_MAX_QUANTITY_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'MaxQuantity',
s_max_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- The min quantity should be a positive number. i.e. strictly greater than zero.
WHEN
(
(s_min_quantity IS NOT NULL AND
s_min_quantity <= 0 AND
s_min_quantity <> g_null_int)
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PT_QUANTITY_POSITIVE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'Quantity',
s_min_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- The max quantity should be a positive number. i.e. strictly greater than zero.
WHEN
(
(s_max_quantity IS NOT NULL AND
s_max_quantity <= 0 AND
s_max_quantity <> g_null_int)
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_PT_QUANTITY_POSITIVE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'MaxQuantity',
s_max_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- max quantity should be greater or equal to the min quantity. i.e if min quantity should not
-- be greater than max quantity
WHEN
(
s_min_quantity > s_max_quantity
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_QT_MAX_MIN_QTY_ERR',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'MaxQuantity',
s_max_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
WHEN (
p_spreadsheet = g_xml_upload_mode AND
EXISTS (
SELECT 'Y' FROM pon_bid_shipments bpb1 WHERE
bpb1.auction_header_id = p_auc_header_id AND
bpb1.bid_number = p_bid_number AND
bpb1.line_number = s_line_number AND
bpb1.SHIPMENT_NUMBER <> s_shipment_number AND
(bpb1.quantity <= s_min_quantity AND
s_min_quantity <= bpb1.max_quantity)
)
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MIN_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_OVERLAP_RANGES_QT',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'Quantity',
s_min_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
--when max quantity is in some other range
--but min quantity is not in some other range
--For case like: row1 is 5-10, row2 is 1-100
--The error message only show up once.
WHEN (
p_spreadsheet = g_xml_upload_mode AND
EXISTS (
SELECT 'Y' FROM pon_bid_shipments bpb1 WHERE
bpb1.auction_header_id = p_auc_header_id AND
bpb1.bid_number = p_bid_number AND
bpb1.line_number = s_line_number AND
bpb1.SHIPMENT_NUMBER <> s_shipment_number AND
( NOT(bpb1.quantity <= s_min_quantity AND
s_min_quantity <= bpb1.max_quantity)
AND
(bpb1.quantity <= s_max_quantity AND
s_max_quantity <= bpb1.max_quantity))
)
)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_OVERLAP_RANGES_QT',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'MaxQuantity',
s_max_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- bid_currency_unit_price must not be null
WHEN (s_bid_currency_unit_price IS NULL
OR
s_bid_currency_unit_price = g_null_int) 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_PT_PRICE_REQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- bid_currency_unit_price must be positive
WHEN
(s_bid_currency_unit_price IS NOT NULL AND
s_bid_currency_unit_price <> g_null_int AND
s_bid_currency_unit_price <= 0)
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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUCTS_QT_PRICE_POSITIVE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- bid_currency_unit_price precision must not exceed price precision
WHEN validate_price_precision(s_bid_currency_unit_price, p_price_precision) = 'F' 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
(p_interface_type,
'PON_BID_SHIPMENTS',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_AUC_QT_INVALID_BID_PREC',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- in case of an SPO the quantity entered at the line level must be equal to the
-- maximum quantity of all the price tiers.
WHEN
(
p_contract_type = 'STANDARD'
and s_max_ship_qty = s_max_quantity
and s_max_ship_qty <> s_bid_quantity
)
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
(p_interface_type,
'PON_BID_ITEM_PRICES',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_MAX_QUANTITY'), null ),
p_batch_id,
s_interface_line_id,
'PON_BID_QTY_NOT_WITHIN_TIERS',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'MaxQuantity',
s_max_quantity,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- in case of an SPO the price entered at the line level must be equal to the price
-- corresponding to the maximum quantity of all the price tiers.
WHEN (p_contract_type = 'STANDARD'
and s_max_ship_qty = s_bid_quantity
and s_max_ship_qty = s_max_quantity
and s_bid_price <> s_shipments_price) 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
(p_interface_type,
'PON_BID_ITEM_PRICES',
decode(p_spreadsheet, g_xml_upload_mode, fnd_message.get_string('PON', 'PON_BIDS_PRICE'), null ),
p_batch_id,
s_interface_line_id,
'PON_BID_PRICE_NOT_WITHIN_TIERS',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'BidCurrencyUnitPrice',
s_bid_currency_unit_price,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
g_exp_date,
'LINENUM',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
SELECT bpb.quantity s_min_quantity,
bpb.max_quantity s_max_quantity,
bpb.price s_shipments_price,
bl.quantity s_bid_quantity,
bl.price s_bid_price,
bpb.bid_currency_unit_price s_bid_currency_unit_price,
bpb.line_number s_line_number,
bpb.shipment_number s_shipment_number,
decode(p_spreadsheet, g_xml_upload_mode, bpb.interface_line_id, bl.interface_line_id) s_interface_line_id,
al.document_disp_line_number s_document_disp_line_number,
max_bid_shipments.max_quantity s_max_ship_qty,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
decode(p_spreadsheet, g_xml_upload_mode,
'PON_AUCTS_PRICE_TIERS',
null
) s_entity_name,
al.line_num_display s_clm_line_num_display
FROM pon_bid_shipments bpb,
pon_bid_item_prices bl,
pon_auction_item_prices_all al,
(SELECT MAX(shipments.max_quantity) max_quantity,
shipments.bid_number,
shipments.line_number
FROM pon_bid_shipments shipments
WHERE shipments.bid_number = p_bid_number
GROUP BY shipments.bid_number,
shipments.line_number) max_bid_shipments
, pon_bid_headers pbh
WHERE bpb.bid_number = p_bid_number
AND bpb.bid_number = max_bid_shipments.bid_number
AND bpb.line_number = max_bid_shipments.line_number
AND bl.bid_number = bpb.bid_number
AND bl.line_number = bpb.line_number
AND al.auction_header_id = bpb.auction_header_id
AND al.line_number = bpb.line_number
AND bl.is_changed_line_flag = 'Y'
AND(p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
Insert 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)
SELECT
p_interface_type,
'PON_BID_SHIPMENTS',
p_batch_id,
bl.interface_line_id,
'PON_AUC_OVERLAP_RANGES_QT',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PTS',
'Quantity',
'NUM',
p_auc_header_id,
p_bid_number,
bl.line_number,
g_exp_date,
'LINENUM',
al.document_disp_line_number
From pon_bid_item_prices bl
, pon_auction_item_prices_all al
, pon_bid_headers pbh
where bl.bid_number = p_bid_number
AND al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND bl.is_changed_line_flag = 'Y'
AND p_spreadsheet = g_online_mode
and bl.line_number in
( Select distinct pbs.line_number
FROM pon_bid_shipments pbs,
pon_bid_shipments pbs1
WHERE pbs.bid_number = p_bid_number
and pbs1.bid_number = p_bid_number
AND pbs.line_number = pbs1.line_number
AND pbs.shipment_number <> pbs1.shipment_number
AND pbs1.quantity <= pbs.quantity
AND pbs.quantity <= pbs1.max_quantity)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT FIRST
-- Line price differential validation:
-- multiplier should be entered for REQUIRED price differentials
WHEN s_shipment_number = -1 AND s_multiplier IS null
AND s_differential_response_type = 'REQUIRED' 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,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PD_VALUE_REQ' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
'TXT',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- Line price differential validation:
-- multiplier should not be entered for DISPLAY_ONLY price differentials
WHEN s_shipment_number = -1 AND s_multiplier IS NOT null
AND s_differential_response_type = 'DISPLAY_ONLY' 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,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id ,
'PON_AUC_PD_VAL_NONENTERABLE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
s_multiplier,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name)
-- Line price differential validation:
-- multiplier should be greater than the target multiplier
WHEN s_shipment_number = -1 AND s_multiplier < s_target_multiplier 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,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER' || p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PD_INVALID_MULT' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
s_multiplier,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- Shipment price differential validation:
-- multiplier should be entered for REQUIRED price differentials
WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
and s_shipment_number <> -1 AND s_multiplier IS null
AND s_differential_response_type = 'REQUIRED' 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,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
ERROR_VALUE_DATATYPE,
ERROR_VALUE,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_PD_VALUE_REQ' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
'TXT',
'',
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- Shipment price differential validation:
-- multiplier should not be entered for DISPLAY_ONLY price differentials
WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
and s_shipment_number <> -1 AND s_multiplier IS NOT null
AND s_differential_response_type = 'DISPLAY_ONLY' 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,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_PD_VAL_NONENTERABLE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
s_multiplier,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
-- Shipment price differential validation:
-- multiplier should be greater than target multiplier
WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
and s_shipment_number <> -1
AND s_multiplier < s_target_multiplier 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,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
WORKSHEET_NAME,
WORKSHEET_SEQUENCE_NUMBER,
ENTITY_MESSAGE_CODE
)
VALUES
(p_interface_type,
fnd_message.get_string('PON', 'PON_AUC_RESP_MULTIPLIER'||p_suffix),
'PON_BID_PRICE_DIFFERENTIALS',
p_batch_id,
s_interface_line_id,
'PON_AUC_PB_PD_INVALID_MULT' || p_suffix,
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_PDS',
'Multiplier',
s_multiplier,
'NUM',
p_auc_header_id,
p_bid_number,
s_line_number,
s_shipment_number,
s_price_differential_number,
g_exp_date,
'LINENUMBER',
--s_document_disp_line_number,
Decode(g_is_fed,'Y',s_clm_line_num_display,s_document_disp_line_number),
'PRICEDIFFERENTIALNAME',
s_price_differential_name,
s_worksheet_name,
s_worksheet_seq_num,
s_entity_name
)
SELECT
bpd.multiplier s_multiplier,
apd.multiplier s_target_multiplier,
nvl(apb.differential_response_type, al.differential_response_type)
s_differential_response_type,
bpd.line_number s_line_number,
bpd.shipment_number s_shipment_number,
bpd.price_differential_number s_price_differential_number,
al.document_disp_line_number s_document_disp_line_number,
decode(p_spreadsheet, g_xml_upload_mode,
bpd.interface_line_id,
bl.interface_line_id
) s_interface_line_id,
pdl.price_differential_dsp s_price_differential_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_name, null) s_worksheet_name,
decode(p_spreadsheet, g_xml_upload_mode, bl.worksheet_sequence_number, null) s_worksheet_seq_num,
decode(p_spreadsheet, g_xml_upload_mode,
'PON_PRICE_DIFFERENTIALS',
null
) s_entity_name,
al.line_num_display s_clm_line_num_display
FROM pon_bid_price_differentials bpd
, pon_bid_item_prices bl
, pon_auction_item_prices_all al
, pon_price_differentials apd
, pon_auction_shipments_all apb
, po_price_diff_lookups_v pdl
, pon_bid_headers pbh
WHERE bpd.bid_number = p_bid_number
AND bl.bid_number = bpd.bid_number
AND bl.line_number = bpd.line_number
AND al.auction_header_id = bpd.auction_header_id
AND al.line_number = bpd.line_number
AND apd.auction_header_id = bpd.auction_header_id
AND apd.line_number = bpd.line_number
AND apd.shipment_number = decode(bpd.shipment_number, -1, -1, bpd.shipment_number - 1)
AND apd.price_differential_number = bpd.price_differential_number
AND apb.auction_header_id (+) = bpd.auction_header_id
AND apb.line_number (+) = bpd.line_number
AND apb.shipment_number (+) = decode(bpd.shipment_number, -1, -1, bpd.shipment_number - 1)
AND pdl.price_differential_type = bpd.price_type
AND bl.is_changed_line_flag = 'Y'
AND (p_spreadsheet = g_online_mode OR bl.batch_id = p_batch_id)
AND pbh.bid_number = bl.bid_number
AND (pbh.SURROG_BID_FLAG = 'Y' OR nvl(al.close_bidding_date, sysdate+1) > sysdate);
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
ERROR_MESSAGE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
ENTITY_TYPE,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE)
SELECT
p_interface_type,
fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'),
'PON_BID_ITEM_PRICES',
p_batch_id,
'PON_PYMT_NUM_NOT_UNQ',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
'BID_ITEMS',
NULL,
'TXT',
p_auc_header_id,
p_bid_number,
pbps.bid_line_number,
g_exp_date,
'LINENUM',
pai.document_disp_line_number,
'PAYITEMNUM',
pbps.payment_display_number
FROM PON_BID_PAYMENTS_SHIPMENTS pbps,
PON_AUCTION_ITEM_PRICES_ALL pai
WHERE pbps.auction_header_id= pai.auction_header_id
AND pbps.auction_header_id=p_auc_header_id
AND pbps.bid_line_number = pai.line_number
AND pbps.bid_number = p_bid_number
GROUP BY pbps.bid_number, pbps.bid_line_number,
pbps.payment_display_number, pai.document_disp_line_number
HAVING count(*) > 1;
INSERT ALL
WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'PaymentDisplayNumber', 'PON_PYMT_NUM_WRONG', -- 1
'NUM', payment_display_number, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_type_code = 'RATE' AND quantity < 0 THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'Quantity', 'PON_PYMT_QTY_WRONG', -- 1
'NUM', quantity, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN bid_currency_price IS NOT NULL AND bid_currency_price < 0 THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'BidCurrencyPrice', 'PON_PYMT_PRICE_WRONG', -- 1
'NUM', bid_currency_price, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_display_number IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'PaymentDisplayNumber', 'PON_PYMT_NUM_MISSING', -- 1
'TXT', payment_display_number, NULL, -- 2
'LINENUM', document_disp_line_number, NULL, -- 3
NULL, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_type_code IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'PaymentTypeCode', 'PON_PYMT_TYPE_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_description IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'PaymentDescription', 'PON_PYMT_DESC_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_type_code = 'RATE' AND quantity IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'Quantity', 'PON_PYMT_QTY_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN payment_type_code = 'RATE' AND uom_code IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'UomCode', 'PON_PYMT_UOM_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN bid_currency_price IS NULL THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'BidCurrencyPrice', 'PON_PYMT_BID_PRICE_NULL', -- 1
'TXT', bid_currency_price, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN promised_date IS NOT NULL AND promised_date <= close_bidding_date THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'PromisedDate', 'PON_PYMT_PDATE_LESS_CDATE', -- 1
'DAT', NULL, promised_date, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
WHEN bid_currency_price IS NOT NULL
AND validate_price_precision(bid_currency_price, p_price_precision) = 'F' THEN
INTO pon_interface_errors
(
request_id, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, bid_payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
p_request_id, 'BidCurrencyPrice', 'PON_QUOTEPRICE_INVALID_PREC_P', -- 1
'NUM', bid_currency_price, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, p_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
p_batch_id, NULL, 'BID_PYMTS', -- 5
auction_header_id, document_disp_line_number, bid_payment_id, -- 6
l_exp_date, p_userid, SYSDATE, -- 7
p_userid, SYSDATE, l_loginid -- 8
)
SELECT
pbp.payment_display_number,
pbp.payment_type_code,
pbp.uom_code,
pbp.payment_description,
pbp.auction_header_id auction_header_id,
pai.document_disp_line_number,
pbp.bid_currency_price,
pbp.quantity,
pbp.promised_date,
pai.line_number auction_line_number,
pai.close_bidding_date,
pbp.bid_payment_id,
pbi.line_number bid_line_number
FROM PON_BID_PAYMENTS_SHIPMENTS pbp,
PON_AUCTION_ITEM_PRICES_ALL pai,
PON_BID_ITEM_PRICES pbi
WHERE pbp.auction_header_id = pai.auction_header_id
AND pbp.auction_line_number = pai.line_number
AND pbi.auction_header_id = pai.auction_header_id
AND pbi.line_number = pai.line_number
AND pbp.bid_number = p_bid_number
AND pbi.bid_number = pbp.bid_number
AND nvl(pbi.has_bid_flag,'N') = 'Y';
print_debug_log(l_module,'After Insert all for validate_payments p_batch_id = '||p_batch_id);
select pah.emd_enable_flag
into l_emd_enable_flag
from pon_auction_headers_all pah
where pah.auction_header_id = p_auc_header_id;
select decode(petr.status_lookup_code,
null,
decode(pbp.exempt_flag,
null, 'NOT_PAID',
'N', 'NOT_PAID',
'Y', 'EXEMPTED'),
'RECEIVING', 'NOT_PAID',
'RECEIVE_ERROR', 'NOT_PAID',
petr.status_lookup_code)
into l_emd_status
from pon_emd_transactions petr,
pon_bid_headers pbh,
pon_bidding_parties pbp
where pbh.bid_number = p_bid_number
and pbh.auction_header_id=p_auc_header_id
and pbp.trading_partner_id = pbh.trading_partner_id
and pbh.vendor_site_id = pbp.vendor_site_id --Modify by Chaoqun on 17-Mar-2009
and pbp.auction_header_id= p_auc_header_id
and petr.auction_header_id(+) = pbp.auction_header_id
and petr.supplier_sequence(+) = pbp.sequence
and decode(petr.current_row_flag,null,'Y',petr.current_row_flag) = 'Y';
INSERT INTO pon_interface_errors
(INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_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)
values(
p_interface_type,
fnd_message.get_string('PON', 'PON_EMD_STATUS_VALUE'),
'PON_EMD_TRANSACTIONS',
p_batch_id,
'PON_EMD_STATUS_VALUE',
p_userid,
sysdate,
p_userid,
sysdate,
p_request_id,
p_auc_header_id,
p_bid_number,
1,
g_exp_date
);
SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = p_auc_header_id;
SELECT
Nvl(pon_auc_doctype_rules.DEFAULT_VALUE,'N') INTO g_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';
-- Select auction data
SELECT sysdate + g_exp_days_offset,
bh.rate,
bh.number_price_decimals,
fc.precision,
decode(ah.bid_ranking, 'MULTI_ATTRIBUTE_SCORING', 'Y', 'N'),
decode(ah.contract_type, 'STANDARD',
decode(ah.supplier_view_type, 'TRANSFORMED', 'Y', 'N'), 'N'),
decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
nvl(ah.global_agreement_flag, 'N'),
decode(ah.supplier_view_type, 'TRANSFORMED', 'Y', 'N'),
bh.display_price_factors_flag,
decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
nvl(ah.price_driven_auction_flag, 'Y'),
bh.min_bid_change,
ah.min_bid_decrement * bh.rate, -- convert to bid currency
decode(ah.min_bid_change_type, 'PERCENTAGE', 'Y', 'N'),
nvl(ah.bid_decrement_method, 'PREVIOUS_PRICE'),
ah.po_start_date,
ah.po_end_date,
ah.close_bidding_date,
decode(ah.bid_scope_code, 'MUST_BID_ALL_ITEMS', 'Y', 'N'),
ah.has_items_flag,
bh.trading_partner_id,
bh.vendor_site_id,
bh.bid_currency_code,
decode(old_bh.bid_status, 'ACTIVE', 'Y', 'N'),
nvl(old_bh.bid_number, 0),
ah.contract_type,
ah.progress_payment_type,
ah.price_tiers_indicator
--added by Allen Yang for Surrogate Bid 2008/09/03
--------------------------------------------------
, ah.TWO_PART_FLAG,
ah.TECHNICAL_EVALUATION_STATUS,
bh.SURROG_BID_FLAG,
--------------------------------------------------
nvl(bh.evaluation_flag, 'N') -- Added for ER: Supplier Management: Supplier Evaluation
INTO g_exp_date,
l_rate,
l_price_precision,
l_amt_precision,
l_mas,
l_spo_trans_view,
l_blanket,
l_global,
l_trans_view,
l_header_disp_pf,
l_full_qty_reqd,
l_price_driven,
l_min_bid_change,
l_min_bid_decr,
l_percent_decr,
l_bid_decr_method,
l_po_start_date,
l_po_end_date,
l_auc_close_date,
l_bid_all_lines,
l_auc_has_items,
l_tpid,
l_vensid,
l_bid_curr_code,
l_rebid,
l_source_bid,
l_contract_type,
l_progress_payment_type,
l_price_tiers_indicator
--added by Allen Yang for Surrogate Bid 2008/09/03
--------------------------------------------------
, l_two_part_flag,
l_technical_evaluation_status,
l_surrogate_bid_flag,
--------------------------------------------------
l_evaluation_flag -- Added for ER: Supplier Management: Supplier Evaluation
FROM pon_auction_headers_all ah, pon_bid_headers bh,
fnd_currencies fc, pon_bid_headers old_bh
WHERE ah.auction_header_id = p_auc_header_id
AND ah.auction_header_id = bh.auction_header_id
AND bh.bid_number = p_bid_number
AND fc.currency_code = bh.bid_currency_code
AND old_bh.bid_number (+) = bh.old_bid_number;
SELECT decode(count(auction_header_id), 0, 'N', 'Y')
INTO l_has_errors
FROM pon_interface_errors
WHERE (batch_id = p_batch_id OR request_id = p_request_id)
AND rownum = 1;
SELECT decode(count(auction_header_id), 0, 'N', 'Y')
INTO l_has_errors
FROM pon_interface_errors
WHERE (batch_id = p_batch_id OR request_id = p_request_id)
AND rownum = 1;
SELECT vendor_site_code
INTO l_vendor_site_code
FROM PO_VENDOR_SITES_ALL
WHERE vendor_site_id = p_vendor_site_id;