[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ah.supplier_view_type,
ah.pf_type_allowed,
ah.has_price_elements
INTO l_supplier_view,
l_pf_type,
l_has_pfs
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
UPDATE pon_bid_item_prices
SET display_price_factors_flag = 'N'
WHERE bid_number = p_bid_number
AND line_number BETWEEN p_batch_start AND p_batch_end;
UPDATE pon_bid_item_prices bl
SET bl.display_price_factors_flag =
nvl((SELECT 'Y'
FROM pon_price_elements apf
WHERE apf.auction_header_id = p_auc_header_id
AND apf.line_number = bl.line_number
AND apf.pf_type = 'SUPPLIER'
AND apf.price_element_type_id <> -10
AND rownum = 1), 'N')
WHERE bl.bid_number = p_bid_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
UPDATE pon_bid_item_prices bl
SET bl.display_price_factors_flag =
nvl((SELECT 'Y'
FROM pon_pf_supplier_values pfv
WHERE pfv.auction_header_id = p_auc_header_id
AND pfv.line_number = bl.line_number
AND pfv.supplier_seq_number = p_supp_seq_number
AND nvl(pfv.value, 0) <> 0
AND rownum = 1), 'N')
WHERE bl.bid_number = p_bid_number
-- no need to update lines with supplier price factors
AND bl.display_price_factors_flag = 'N'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
SELECT decode(count(bl.bid_number), 0, 'Y', 'N')
INTO x_skip_pf_for_batch
FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.display_price_factors_flag = 'Y'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end
AND rownum = 1;
PROCEDURE insert_auction_lines
(
p_auc_header_id IN pon_bid_headers.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_auctpid IN pon_auction_headers_all.trading_partner_id%TYPE,
p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
p_has_pe_flag IN VARCHAR2,
p_blanket IN VARCHAR2,
p_full_qty IN VARCHAR2,
p_supp_seq_number IN pon_bidding_parties.sequence%TYPE,
p_rate IN pon_bid_headers.rate%TYPE,
p_price_prec IN pon_bid_headers.number_price_decimals%TYPE,
p_curr_prec IN fnd_currencies.precision%TYPE,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
l_skip_pf_for_batch VARCHAR2(1);
* STEP 1: For lines with negotiable shipments, insert all the
** missing bid shipments for only those lines that the supplier
** never attempted to bid on. We need to insert these shipments
** before we actually insert the bid lines so that the "not
** exists" clause doesn't cause data corruption.
*************************************************************/
-- Insert missing shipments for all non-negotiable shipments
INSERT INTO pon_bid_shipments
(
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
AUCTION_SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
MAX_QUANTITY,
PRICE_TYPE,
PRICE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG
)
(SELECT
p_bid_number, -- BID_NUMBER
apb.line_number, -- LINE_NUMBER
apb.shipment_number+1, -- SHIPMENT_NUMBER
p_auc_header_id, -- AUCTION_HEADER_ID
apb.line_number, -- AUCTION_LINE_NUMBER
apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
apb.shipment_type, -- SHIPMENT_TYPE
apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
apb.quantity, -- QUANTITY
apb.max_quantity, -- MAX_QUANTITY
'PRICE', -- PRICE_TYPE
apb.price, -- PRICE
apb.effective_start_date, -- EFFECTIVE_START_DATE
apb.effective_end_date, -- EFFECTIVE_END_DATE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
apb.has_price_differentials_flag -- HAS_PRICE_DIFFERENTIALS_FLAG
FROM pon_auction_shipments_all apb, pon_auction_item_prices_all aip
WHERE apb.auction_header_id = p_auc_header_id
AND aip.auction_header_id = apb.auction_header_id
AND apb.line_number BETWEEN p_batch_start AND p_batch_end
AND aip.line_number = apb.line_number
AND nvl(aip.price_break_neg_flag, 'N') = 'Y'
AND NOT EXISTS
(SELECT pbip.line_number
FROM pon_bid_item_prices pbip
WHERE pbip.bid_number = p_bid_number
AND pbip.line_number = apb.line_number));
** Logic used is to blindly insert all the lines and then
** delete the lines that the current supplier was excluded from.
*************************************************************/
BEGIN
-- Check if the supplier has restricted lines, and get sequence number
SELECT decode(bp.access_type, 'RESTRICTED', 'Y', 'N'), bp.sequence
INTO l_restricted_flag, l_supp_seq_number
FROM pon_bidding_parties bp
WHERE bp.auction_header_id = p_auc_header_id
AND bp.trading_partner_id = p_tpid
AND nvl(bp.vendor_site_id, -1) = p_vensid;
* STEP 3: Insert missing auction lines. pon_bid_item_prices
** has an index on (bid_number, line_number) so the EXISTS
** clause does not result in a full table scan.
*************************************************************/
INSERT INTO pon_bid_item_prices
(
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
BID_NUMBER,
LINE_NUMBER,
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
UOM,
QUANTITY,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_CREATION_DATE,
SHIP_TO_LOCATION_ID,
PROXY_BID_FLAG,
UNIT_OF_MEASURE,
HAS_ATTRIBUTES_FLAG,
FREIGHT_TERMS_CODE,
TBD_PRICING_FLAG,
AUC_TRADING_PARTNER_ID,
BID_TRADING_PARTNER_ID,
PRICE_BREAK_TYPE,
HAS_SHIPMENTS_FLAG,
IS_CHANGED_LINE_FLAG,
HAS_PRICE_DIFFERENTIALS_FLAG,
PRICE_DIFF_SHIPMENT_NUMBER,
HAS_BID_FLAG,
HAS_BID_PAYMENTS_FLAG,
BID_START_PRICE,
HAS_QUANTITY_TIERS
)
(SELECT
al.auction_header_id, -- AUCTION_HEADER_ID
al.line_number, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
al.line_number, -- LINE_NUMBER
al.item_description, -- ITEM_DESCRIPTION
al.category_id, -- CATEGORY_ID
al.category_name, -- CATEGORY_NAME
al.uom_code, -- UOM
decode(p_blanket, 'Y', null,
decode(p_full_qty, 'Y', al.quantity,
decode(al.group_type, 'LOT_LINE', al.quantity,
decode(al.order_type_lookup_code, 'AMOUNT',
al.quantity, null)))), -- QUANTITY
userenv('LANG'), -- LANGUAGE_CODE
SYSDATE, -- CREATION_DATE
p_userid, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
al.auction_creation_date, -- AUCTION_CREATION_DATE
al.ship_to_location_id, -- SHIP_TO_LOCATION_ID
'N', -- PROXY_BID_FLAG
al.unit_of_measure, -- UNIT_OF_MEASURE
al.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
al.freight_terms_code, -- FREIGHT_TERMS_CODE
'N', -- TBD_PRICING_FLAG
p_auctpid, -- AUC_TRADING_PARTNER_ID
p_tpid, -- BID_TRADING_PARTNER_ID
al.price_break_type, -- PRICE_BREAK_TYPE
al.has_shipments_flag, -- HAS_SHIPMENTS_FLAG
'N', -- IS_CHANGED_LINE_FLAG
al.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
al.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
'N', -- HAS_BID_FLAG
'N', -- HAS_BID_PAYMENTS_FLAG
al.bid_start_price, -- BID_START_PRICE
al.has_quantity_tiers -- HAS_QUANTITY_TIERS
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auc_header_id
AND al.line_number BETWEEN p_batch_start AND p_batch_end
AND NOT EXISTS
(SELECT bl.line_number
FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number = al.line_number));
** STEP 4: Delete all the excluded lines.
*************************************************************/
IF (l_restricted_flag = 'Y') THEN
DELETE FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end
AND EXISTS
(SELECT le.line_number
FROM pon_party_line_exclusions le, pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auc_header_id
AND al.line_number = bl.line_number
AND le.auction_header_id = al.auction_header_id
AND le.line_number = nvl(al.parent_line_number, al.line_number)
AND le.trading_partner_id = p_tpid
AND le.vendor_site_id = p_vensid);
** STEP 5: Insert missing line attributes
*************************************************************/
INSERT INTO pon_bid_attribute_values
(
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
DATATYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEQUENCE_NUMBER,
ATTR_LEVEL,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER
)
(SELECT
aa.auction_header_id, -- AUCTION_HEADER_ID
aa.line_number, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
aa.line_number, -- LINE_NUMBER
aa.attribute_name, -- ATTRIBUTE_NAME
aa.datatype, -- DATATYPE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
aa.sequence_number, -- SEQUENCE_NUMBER
aa.attr_level, -- ATTR_LEVEL
aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
aa.attr_disp_seq_number -- ATTR_DISP_SEQ_NUMBER
FROM pon_auction_attributes aa
WHERE aa.auction_header_id = p_auc_header_id
AND aa.line_number BETWEEN p_batch_start AND p_batch_end
AND NOT EXISTS
(SELECT pbav.attribute_name
FROM pon_bid_attribute_values pbav
WHERE pbav.bid_number = p_bid_number
AND pbav.line_number = aa.line_number
AND pbav.sequence_number = aa.sequence_number));
** STEP 6: Insert missing bid cost factors or price elements
** or price factors
*************************************************************/
/************************************************************
** STEP 6a: Populate display_price_factors flag as it is a
** rel12 column
*************************************************************/
populate_display_pf_flag (p_auc_header_id,
p_bid_number,
p_supp_seq_number,
p_batch_start,
p_batch_end,
l_skip_pf_for_batch);
** STEP 6b: Batching enabled inserts - if we are not supposed to
** skip this set of lines in the batch, then go ahead with
** inserts
*************************************************************/
IF (l_skip_pf_for_batch = 'N') THEN
-- Insert missing SUPPLIER price factors only if they exist
IF (p_has_pe_flag = 'Y') THEN
INSERT INTO pon_bid_price_elements
(
BID_NUMBER,
LINE_NUMBER,
PRICE_ELEMENT_TYPE_ID,
AUCTION_HEADER_ID,
PRICING_BASIS,
NEGATIVE_COST_FACTOR_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE
)
(SELECT
p_bid_number, -- BID_NUMBER
apf.line_number, -- LINE_NUMBER
apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_auc_header_id, -- AUCTION_HEADER_ID
apf.pricing_basis, -- PRICING_BASIS
apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
apf.sequence_number, -- SEQUENCE_NUMBER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
apf.pf_type -- PF_TYPE
FROM pon_price_elements apf
WHERE apf.auction_header_id = p_auc_header_id
AND apf.pf_type = 'SUPPLIER' -- only supplier price factors
AND apf.line_number BETWEEN p_batch_start AND p_batch_end
AND NOT EXISTS
(SELECT pbpe.price_element_type_id
FROM pon_bid_price_elements pbpe
WHERE pbpe.bid_number = p_bid_number
AND pbpe.line_number = apf.line_number
AND pbpe.price_element_type_id = apf.price_element_type_id));
-- Insert missing BUYER price factors if applicable
IF (p_supp_seq_number IS NOT null) THEN
INSERT INTO pon_bid_price_elements
(
BID_NUMBER,
LINE_NUMBER,
PRICE_ELEMENT_TYPE_ID,
AUCTION_HEADER_ID,
PRICING_BASIS,
NEGATIVE_COST_FACTOR_FLAG,
AUCTION_CURRENCY_VALUE,
BID_CURRENCY_VALUE,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE
)
(SELECT
p_bid_number, -- BID_NUMBER
apf.line_number, -- LINE_NUMBER
apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_auc_header_id, -- AUCTION_HEADER_ID
apf.pricing_basis, -- PRICING_BASIS
apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
pf.value, -- AUCTION_CURRENCY_VALUE
decode(apf.pricing_basis,
'PER_UNIT', round(pf.value * p_rate, p_price_prec),
'FIXED_AMOUNT', round(pf.value * p_rate, p_curr_prec),
'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
apf.sequence_number, -- SEQUENCE_NUMBER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
apf.pf_type -- PF_TYPE
FROM pon_price_elements apf,
pon_pf_supplier_values pf,
pon_bid_item_prices bl
WHERE apf.auction_header_id = p_auc_header_id
AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
AND apf.display_to_suppliers_flag = 'Y'
AND bl.bid_number = p_bid_number
AND bl.line_number = apf.line_number
AND bl.display_price_factors_flag = 'Y'
AND pf.auction_header_id = apf.auction_header_id
AND pf.line_number = apf.line_number
AND pf.pf_seq_number = apf.sequence_number
AND pf.supplier_seq_number = p_supp_seq_number
AND nvl(pf.value, 0) <> 0
AND apf.line_number BETWEEN p_batch_start AND p_batch_end
AND NOT EXISTS
(SELECT pbpe.price_element_type_id
FROM pon_bid_price_elements pbpe
WHERE pbpe.bid_number = p_bid_number
AND pbpe.line_number = apf.line_number
AND pbpe.price_element_type_id = apf.price_element_type_id));
-- Insert missing shipments for all non-negotiable shipments
/************************************************************
** STEP 7: Insert all the missing non-negotiable or required
** or mandatory shipments/price breaks.
*************************************************************/
/*
* Price Tiers Enhancements
* Quantity tiers are negotiable shipments so no need to copy the max_quantity field here
*/
INSERT INTO pon_bid_shipments
(
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
AUCTION_SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE_TYPE,
PRICE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG
)
(SELECT
p_bid_number, -- BID_NUMBER
apb.line_number, -- LINE_NUMBER
apb.shipment_number+1, -- SHIPMENT_NUMBER
p_auc_header_id, -- AUCTION_HEADER_ID
apb.line_number, -- AUCTION_LINE_NUMBER
apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
apb.shipment_type, -- SHIPMENT_TYPE
apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
apb.quantity, -- QUANTITY
'PRICE', -- PRICE_TYPE
apb.price, -- PRICE
apb.effective_start_date, -- EFFECTIVE_START_DATE
apb.effective_end_date, -- EFFECTIVE_END_DATE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
apb.has_price_differentials_flag -- HAS_PRICE_DIFFERENTIALS_FLAG
FROM pon_auction_shipments_all apb, pon_auction_item_prices_all aip
WHERE apb.auction_header_id = p_auc_header_id
AND apb.line_number BETWEEN p_batch_start AND p_batch_end
AND aip.auction_header_id = apb.auction_header_id
AND aip.line_number = apb.line_number
AND nvl(aip.price_break_neg_flag, 'Y') = 'N'
AND NOT EXISTS
(SELECT pbs.auction_shipment_number
FROM pon_bid_shipments pbs
WHERE pbs.bid_number = p_bid_number
AND pbs.line_number = apb.line_number
AND pbs.auction_shipment_number = apb.shipment_number));
** STEP 8: Insert all the missing line-level as well as shipment
** level price breaks in a single insert statement.
*************************************************************/
INSERT INTO pon_bid_price_differentials
(
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(SELECT
p_auc_header_id, -- AUCTION_HEADER_ID
p_bid_number, -- BID_NUMBER
apd.line_number, -- LINE_NUMBER
decode(apd.shipment_number, -1, -1, apd.shipment_number+1), -- SHIPMENT_NUMBER
apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
apd.price_type, -- PRICE_TYPE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null -- LAST_UPDATE_LOGIN
FROM pon_price_differentials apd
WHERE apd.auction_header_id = p_auc_header_id
AND apd.line_number BETWEEN p_batch_start AND p_batch_end
AND NOT EXISTS
(SELECT pbpd.price_differential_number
FROM pon_bid_price_differentials pbpd
WHERE pbpd.bid_number = p_bid_number
AND pbpd.line_number = apd.line_number
AND pbpd.price_differential_number = apd.price_differential_number));
END insert_auction_lines;
-- Update line level old value columns
UPDATE pon_bid_item_prices bl
SET (bl.old_price,
bl.old_bid_currency_unit_price,
bl.old_bid_currency_price,
bl.old_bid_currency_limit_price,
bl.old_po_bid_min_rel_amount,
bl.old_quantity,
bl.old_publish_date,
bl.old_promised_date,
bl.old_note_to_auction_owner,
bl.old_bid_curr_advance_amount,
bl.old_recoupment_rate_percent,
bl.old_progress_pymt_rate_percent,
bl.old_retainage_rate_percent,
bl.old_bid_curr_max_retainage_amt) =
(SELECT
old_bl.price,
old_bl.bid_currency_unit_price,
old_bl.bid_currency_price,
old_bl.bid_currency_limit_price,
old_bl.po_bid_min_rel_amount,
old_bl.quantity,
old_bl.publish_date,
old_bl.promised_date,
old_bl.note_to_auction_owner,
old_bl.bid_curr_advance_amount,
old_bl.recoupment_rate_percent,
old_bl.progress_pymt_rate_percent,
old_bl.retainage_rate_percent,
old_bl.bid_curr_max_retainage_amt
FROM pon_bid_item_prices old_bl
WHERE old_bl.bid_number = p_source_bid_num
AND old_bl.line_number = bl.line_number)
WHERE bl.bid_number = p_bid_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
-- Update attribute old value columns
UPDATE pon_bid_attribute_values ba
SET ba.old_value =
(SELECT old_ba.value
FROM pon_bid_attribute_values old_ba
WHERE old_ba.bid_number = p_source_bid_num
AND old_ba.line_number = ba.line_number
AND old_ba.attribute_name = ba.attribute_name)
WHERE ba.bid_number = p_bid_number
AND ba.line_number BETWEEN p_batch_start AND p_batch_end;
-- Update SUPPLIER price factor old value columns
UPDATE pon_bid_price_elements bpf
SET bpf.old_bid_currency_value =
(SELECT old_bpf.bid_currency_value
FROM pon_bid_price_elements old_bpf
WHERE old_bpf.bid_number = p_source_bid_num
AND old_bpf.line_number = bpf.line_number
AND old_bpf.price_element_type_id = bpf.price_element_type_id)
WHERE bpf.bid_number = p_bid_number
AND bpf.pf_type = 'SUPPLIER'
AND bpf.line_number BETWEEN p_batch_start AND p_batch_end;
-- Update price break old value columns
UPDATE pon_bid_shipments bpb
SET (bpb.old_bid_currency_unit_price,
bpb.old_bid_currency_price,
bpb.old_price_discount,
bpb.old_ship_to_org_id,
bpb.old_ship_to_loc_id,
bpb.old_effective_start_date,
bpb.old_effective_end_date,
bpb.old_quantity,
bpb.old_max_quantity,
bpb.old_price_type) =
(SELECT
old_bpb.bid_currency_unit_price,
old_bpb.bid_currency_price,
old_bpb.price_discount,
old_bpb.ship_to_organization_id,
old_bpb.ship_to_location_id,
old_bpb.effective_start_date,
old_bpb.effective_end_date,
old_bpb.quantity,
old_bpb.max_quantity,
old_bpb.price_type
FROM pon_bid_shipments old_bpb
WHERE old_bpb.bid_number = p_source_bid_num
AND old_bpb.line_number = bpb.line_number
AND old_bpb.shipment_number = bpb.shipment_number)
WHERE bpb.bid_number = p_bid_number
AND bpb.line_number BETWEEN p_batch_start AND p_batch_end;
-- Update price differential old value columns
UPDATE pon_bid_price_differentials bpd
SET bpd.old_multiplier =
(SELECT old_bpd.multiplier
FROM pon_bid_price_differentials old_bpd
WHERE old_bpd.bid_number = p_source_bid_num
AND old_bpd.line_number = bpd.line_number
AND old_bpd.shipment_number = bpd.shipment_number
AND old_bpd.price_differential_number = bpd.price_differential_number)
WHERE bpd.bid_number = p_bid_number
AND bpd.line_number BETWEEN p_batch_start AND p_batch_end;
SELECT bh.bid_number, 'Y'
INTO x_prev_bid_number, x_rebid_flag
FROM pon_bid_headers bh
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id =
(SELECT trading_partner_contact_id
FROM pon_bid_headers bh2
WHERE bh2.bid_number = p_draft_bid_num)
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'ACTIVE'
AND nvl(bh.evaluator_id, -1) = nvl(p_evaluator_id, -1)
AND nvl(bh.evaluation_flag, 'N') = p_eval_flag
AND rownum = 1
ORDER BY bh.publish_date DESC;
-- Update old_bid_number to new source bid
UPDATE pon_bid_headers bh
SET bh.old_bid_number = x_prev_bid_number
WHERE bh.bid_number = p_draft_bid_num;
SELECT ah.max_internal_line_num
INTO l_max_line_number
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
UPDATE pon_bid_item_prices bl
SET (bl.price, bl.proxy_bid_limit_price, bl.bid_currency_price,
bl.bid_currency_limit_price, bl.bid_currency_trans_price,
bl.unit_price, bl.bid_currency_unit_price, bl.copy_price_for_proxy_flag,
bl.old_price, bl.old_bid_currency_unit_price, bl.old_bid_currency_price,
bl.old_bid_currency_limit_price) =
(SELECT old_bl.price, old_bl.proxy_bid_limit_price, old_bl.bid_currency_price,
old_bl.bid_currency_limit_price, old_bl.bid_currency_trans_price,
old_bl.unit_price, old_bl.bid_currency_unit_price,
decode(sign(old_bl.proxy_bid_limit_price - old_bl.price),
0, 'N', 'Y'),
old_bl.price, old_bl.bid_currency_unit_price, old_bl.bid_currency_price, old_bl.bid_currency_limit_price
FROM pon_bid_item_prices old_bl
WHERE old_bl.bid_number = x_prev_bid_number
AND old_bl.line_number = bl.line_number)
WHERE bl.bid_number = p_draft_bid_num
AND bl.copy_price_for_proxy_flag = 'Y'
AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
UPDATE pon_bid_item_prices bl
SET rank =
(SELECT old_bl.rank
FROM pon_bid_item_prices old_bl
WHERE old_bl.bid_number = x_prev_bid_number
AND old_bl.line_number = bl.line_number)
WHERE bl.bid_number = p_draft_bid_num
AND bl.line_number BETWEEN l_batch_start AND l_batch_end;
SELECT bh.old_bid_number
INTO x_prev_bid_number
FROM pon_bid_headers bh
WHERE bh.bid_number = p_draft_bid_num;
PROCEDURE insert_into_bid_header
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
p_tpname IN pon_bid_headers.trading_partner_name%TYPE,
p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
p_tpcname IN pon_bid_headers.trading_partner_contact_name%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_venid IN pon_bid_headers.vendor_id%TYPE,
p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
p_venscode IN pon_bid_headers.vendor_site_code%TYPE,
p_auctpid IN pon_bid_headers.surrog_bid_created_tp_id%TYPE,
p_auctpcid IN pon_bid_headers.surrog_bid_created_contact_id%TYPE,
p_buyer_user IN VARCHAR2,
----------- Supplier Management: Supplier Evaluation -----------
p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
----------------------------------------------------------------
p_rebid_flag IN VARCHAR2,
p_prev_bid_disq IN VARCHAR2,
p_prev_bid_wthd IN VARCHAR2,
x_bid_number OUT NOCOPY pon_bid_headers.bid_number%TYPE
) IS
l_old_min_bid_change_type pon_auction_headers_all.min_bid_change_type%TYPE;
SELECT pah.min_bid_change_type,
pah.min_bid_decrement
-- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
-------------------------------------------------------
, pah.TWO_PART_FLAG
, pah.TECHNICAL_EVALUATION_STATUS
-------------------------------------------------------
INTO l_old_min_bid_change_type,
l_old_min_bid_change
-- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
-------------------------------------------------------
, l_two_part_flag
, l_technical_evaluation_status
-------------------------------------------------------
FROM PON_AUCTION_HEADERS_ALL pah,
PON_BID_HEADERS pbh
WHERE pah.auction_header_id = pbh.auction_header_id
AND pbh.bid_number = p_source_bid_num;
SELECT pon_bid_headers_s.nextval INTO x_bid_number
FROM dual;
INSERT INTO pon_bid_headers
(
BID_NUMBER,
AUCTION_HEADER_ID,
BIDDERS_BID_NUMBER,
BID_TYPE,
CONTRACT_TYPE,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
BID_STATUS,
BID_EFFECTIVE_DATE,
BID_EXPIRATION_DATE,
DISQUALIFY_REASON,
FREIGHT_TERMS_CODE,
CARRIER_CODE,
FOB_CODE,
NOTE_TO_AUCTION_OWNER,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
RATE,
MIN_BID_CHANGE ,
PROXY_BID_FLAG ,
NUMBER_PRICE_DECIMALS,
DOCTYPE_ID,
VENDOR_ID,
VENDOR_SITE_ID ,
RATE_DSP,
INITIATE_APPROVAL,
DRAFT_LOCKED,
DRAFT_LOCKED_BY,
DRAFT_LOCKED_BY_CONTACT_ID,
DRAFT_LOCKED_DATE,
VENDOR_SITE_CODE,
SHORTLIST_FLAG,
ATTRIBUTE_LINE_NUMBER,
NOTE_TO_SUPPLIER,
SURROG_BID_CREATED_TP_ID,
SURROG_BID_CREATED_CONTACT_ID,
SURROG_BID_RECEIPT_DATE,
SURROG_BID_ONLINE_ENTRY_DATE,
SURROG_BID_FLAG,
COLOR_SEQUENCE_ID,
OLD_NOTE_TO_AUCTION_OWNER,
OLD_BIDDERS_BID_NUMBER,
OLD_BID_EXPIRATION_DATE,
OLD_MIN_BID_CHANGE,
OLD_BID_STATUS,
OLD_SURROG_BID_RECEIPT_DATE,
REL12_DRAFT_FLAG,
OLD_BID_NUMBER
--added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
--------------------------------------------------------------
, SUBMIT_STAGE
--------------------------------------------------------------
---- Supplier Management: Supplier Evaluation ----
,EVALUATOR_ID
,EVALUATION_FLAG
--------------------------------------------------
)
(SELECT
x_bid_number, -- BID_NUMBER
ah.auction_header_id, -- AUCTION_HEADER_ID
bh.bidders_bid_number, -- BIDDERS_BID_NUMBER
'REVERSE', -- BID_TYPE
ah.contract_type, -- CONTRACT_TYPE
p_tpcname, -- TRADING_PARTNER_CONTACT_NAME
p_tpcid, -- TRADING_PARTNER_CONTACT_ID
p_tpname, -- TRADING_PARTNER_NAME
p_tpid, -- TRADING_PARTNER_ID
'DRAFT', -- BID_STATUS
bh.bid_effective_date, -- BID_EFFECTIVE_DATE
bh.bid_expiration_date, -- BID_EXPIRATION_DATE
decode(p_prev_bid_disq, 'Y', bh.disqualify_reason, null), -- DISQUALIFY_REASON
ah.freight_terms_code, -- FREIGHT_TERMS_CODE
ah.carrier_code, -- CARRIER_CODE
ah.fob_code, -- FOB_CODE
bh.note_to_auction_owner, -- NOTE_TO_AUCTION_OWNER
userenv('LANG'), -- LANGUAGE_CODE
SYSDATE, -- CREATION_DATE
p_userid, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
ah.creation_date, -- AUCTION_CREATION_DATE
nvl(bh.bid_currency_code, ah.currency_code), -- BID_CURRENCY_CODE
nvl(bh.rate, 1), -- RATE
decode(ah.min_bid_change_type, l_old_min_bid_change_type,
decode(ah.min_bid_decrement, l_old_min_bid_change, bh.min_bid_change, null), null), -- MIN_BID_CHANGE
'N', -- PROXY_BID_FLAG
nvl(bh.number_price_decimals, ah.number_price_decimals), -- NUMBER_PRICE_DECIMALS
ah.doctype_id, -- DOCTYPE_ID
p_venid, -- VENDOR_ID
p_vensid, -- VENDOR_SITE_ID
nvl(bh.rate_dsp, 1), -- RATE_DSP
bh.initiate_approval, -- INITIATE_APPROVAL
'Y', -- DRAFT_LOCKED
decode(p_buyer_user, 'Y', p_auctpid, p_tpid), -- DRAFT_LOCKED_BY
-- Begin Supplier Management: Supplier Evaluation
-- Modified the following:
decode(p_buyer_user, 'Y', p_auctpcid, decode(p_eval_flag, 'Y', p_evaluator_id, p_tpcid)), -- DRAFT_LOCKED_BY_CONTACT_ID
-- End Supplier Management: Supplier Evaluation
SYSDATE, -- DRAFT_LOCKED_DATE
p_venscode, -- VENDOR_SITE_CODE
'N', -- SHORTLIST_FLAG
-1, -- ATTRIBUTE_LINE_NUMBER
ah.note_to_bidders, -- NOTE_TO_SUPPLIER
decode(p_buyer_user, 'Y', p_auctpid, null), -- SURROG_BID_CREATED_TP_ID
decode(p_buyer_user, 'Y', p_auctpcid, null), -- SURROG_BID_CREATED_CONTACT_ID
decode(p_buyer_user, 'Y',
Decode((SELECT TWO_PART_FLAG FROM pon_auction_headers_all WHERE AUCTION_HEADER_ID = bh.AUCTION_HEADER_ID),'Y',
Decode(bh.SUBMIT_STAGE,'COMMERCIAL',bh.surrog_bid_receipt_date, null),NULL),NULL),
--decode(p_buyer_user, 'Y', bh.surrog_bid_receipt_date, null), -- SURROG_BID_RECEIPT_DATE
decode(p_buyer_user, 'Y', sysdate, null), -- SURROG_BID_ONLINE_ENTRY_DATE
p_buyer_user, -- SURROG_BID_FLAG
bh.color_sequence_id, -- COLOR_SEQUENCE_ID
decode(p_rebid_flag, 'Y', bh.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
decode(p_rebid_flag, 'Y', bh.bidders_bid_number, null), -- OLD_BIDDERS_BID_NUMBER
decode(p_rebid_flag, 'Y', bh.bid_expiration_date, null), -- OLD_BID_EXPIRATION_DATE
decode(p_rebid_flag, 'Y', bh.min_bid_change, null), -- OLD_MIN_BID_CHANGE
decode(p_rebid_flag, 'Y', bh.bid_status, null), -- OLD_BID_STATUS
decode(p_rebid_flag, 'Y', bh.surrog_bid_receipt_date, null), -- OLD_SURROG_BID_RECEIPT_DATE
'Y', -- REL12_DRAFT_FLAG
decode(p_source_bid_num, 0, null, p_source_bid_num) -- OLD_BID_NUMBER
-- added by Allen Yang 2009/01/06 for surrogate bid bug 7664486
-----------------------------------------------------------------------------
-- set submit_stage to TECHNICAL when requoting in commercial stage
, decode(p_rebid_flag, 'Y', -- SUBMIT_STAGE
decode(l_two_part_flag, 'Y',
decode(p_buyer_user, 'Y',
decode(l_technical_evaluation_status, 'COMPLETED', 'TECHNICAL', null), null), null), null)
-----------------------------------------------------------------------------
---------------- Supplier Management: Supplier Evaluation ----------------
,decode(p_eval_flag, 'Y', p_evaluator_id, null) -- EVALUATOR_ID
,p_eval_flag -- EVALUATION_FLAG
--------------------------------------------------------------------------
FROM pon_auction_headers_all ah, pon_bid_headers bh
WHERE ah.auction_header_id = p_auc_header_id
AND bh.bid_number (+) = p_source_bid_num
AND ah.auction_header_id >= bh.auction_header_id (+));
END insert_into_bid_header;
PROCEDURE insert_into_bid_items
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
p_tpcid IN pon_bid_headers.trading_partner_contact_id%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
p_rebid_flag IN VARCHAR2,
p_restricted_flag IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE,
p_surrog_bid_flag IN pon_bid_headers.surrog_bid_flag%TYPE
) IS
l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
select doctype_id, org_id
into l_doctype_id, l_org_id
from pon_auction_headers_all
where auction_header_id = p_auc_header_id;
SELECT ah.trading_partner_id,
decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
enforce_prevrnd_bid_price_flag,
auction_header_id_prev_round,
is_paused,
last_pause_date
INTO l_auctpid,
l_blanket,
l_full_qty,
l_enforce_prevrnd_price_flag,
l_auction_header_id_prev_round,
l_is_paused,
l_last_pause_date
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
INSERT INTO pon_bid_item_prices
(
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
BID_NUMBER,
LINE_NUMBER,
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
UOM,
QUANTITY,
PRICE,
MINIMUM_BID_PRICE,
PROMISED_DATE,
NOTE_TO_AUCTION_OWNER,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_CREATION_DATE,
SHIP_TO_LOCATION_ID,
PUBLISH_DATE,
PROXY_BID_LIMIT_PRICE,
PROXY_BID_LIMIT_PRICE_DATE,
BID_CURRENCY_PRICE,
BID_CURRENCY_LIMIT_PRICE,
PROXY_BID_FLAG,
FIRST_BID_PRICE,
UNIT_OF_MEASURE,
HAS_ATTRIBUTES_FLAG,
FREIGHT_TERMS_CODE,
TBD_PRICING_FLAG,
AUC_TRADING_PARTNER_ID,
BID_TRADING_PARTNER_ID,
TOTAL_WEIGHTED_SCORE,
RANK,
PO_MIN_REL_AMOUNT,
PO_BID_MIN_REL_AMOUNT,
PRICE_BREAK_TYPE,
HAS_SHIPMENTS_FLAG,
IS_CHANGED_LINE_FLAG,
HAS_PRICE_DIFFERENTIALS_FLAG,
PRICE_DIFF_SHIPMENT_NUMBER,
BID_CURRENCY_TRANS_PRICE,
UNIT_PRICE,
BID_CURRENCY_UNIT_PRICE,
GROUP_AMOUNT,
HAS_BID_PAYMENTS_FLAG,
ADVANCE_AMOUNT,
BID_CURR_ADVANCE_AMOUNT,
RECOUPMENT_RATE_PERCENT,
PROGRESS_PYMT_RATE_PERCENT,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
BID_CURR_MAX_RETAINAGE_AMT,
OLD_NO_OF_PAYMENTS,
OLD_PRICE,
OLD_BID_CURRENCY_UNIT_PRICE,
OLD_BID_CURRENCY_PRICE,
OLD_BID_CURRENCY_LIMIT_PRICE,
OLD_PO_BID_MIN_REL_AMOUNT,
OLD_QUANTITY,
OLD_PUBLISH_DATE,
OLD_PROMISED_DATE,
OLD_NOTE_TO_AUCTION_OWNER,
HAS_BID_FLAG,
OLD_BID_CURR_ADVANCE_AMOUNT,
OLD_RECOUPMENT_RATE_PERCENT,
OLD_PROGRESS_PYMT_RATE_PERCENT,
OLD_RETAINAGE_RATE_PERCENT,
OLD_BID_CURR_MAX_RETAINAGE_AMT,
COPY_PRICE_FOR_PROXY_FLAG,
BID_START_PRICE,
HAS_QUANTITY_TIERS,
/***********************************************************
* CLM - Clin Slin project Changes Start.
*********************************************************/
--- default the CLM attributes
LINE_NUM_DISPLAY ,
GROUP_LINE_ID,
CLM_INFO_FLAG,
CLM_OPTION_INDICATOR,
CLM_BASE_LINE_NUM,
CLM_OPTION_NUM,
CLM_OPTION_FROM_DATE,
CLM_OPTION_TO_DATE,
CLM_FUNDED_FLAG,
/***********************************************************
* CLM - Clin Slin project Changes End.
*********************************************************/
/***********************************************************
* CLM - Complex Pricing project Changes Start.
*********************************************************/
CLM_COST_CONSTRAINT,
CLM_CONTRACT_TYPE,
CLM_IDC_TYPE,
UDA_TEMPLATE_ID,
/***********************************************************
* CLM - Complex Pricing project Changes End.
*********************************************************/
HAS_USER_BID_FLAG, --ELINs project
IS_REBID_ON_LINE_FLAG, --ELINs project
-- Event Based Delivery Project
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
CLM_PROMISE_POP_DURATION,
CLM_PROMISE_POP_DURATION_UOM
)
(SELECT
al.auction_header_id, -- AUCTION_HEADER_ID
al.line_number, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
al.line_number, -- LINE_NUMBER
al.item_description, -- ITEM_DESCRIPTION
al.category_id, -- CATEGORY_ID
al.category_name, -- CATEGORY_NAME
al.uom_code, -- UOM
decode(al.modified_date-old_al.modified_date,
0, bl.quantity, decode(l_blanket, 'Y', null,
decode(l_full_qty, 'Y', al.quantity,
decode(al.group_type, 'LOT_LINE', al.quantity,
decode(al.order_type_lookup_code, 'AMOUNT',
al.quantity, null))))), -- QUANTITY
decode(al.modified_date-old_al.modified_date,
0, bl.price, null), -- PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.minimum_bid_price, null), -- MINIMUM_BID_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.promised_date, null), -- PROMISED_DATE
decode(al.modified_date-old_al.modified_date,
0, bl.note_to_auction_owner, null), -- NOTE_TO_AUCTION_OWNER
userenv('LANG'), -- LANGUAGE_CODE
SYSDATE, -- CREATION_DATE
p_userid, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
al.auction_creation_date, -- AUCTION_CREATION_DATE
al.ship_to_location_id, -- SHIP_TO_LOCATION_ID
decode(al.modified_date-old_al.modified_date,
0, bl.publish_date, null), -- PUBLISH_DATE
decode(al.modified_date-old_al.modified_date,
0, bl.proxy_bid_limit_price, null), -- PROXY_BID_LIMIT_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.proxy_bid_limit_price_date, null), -- PROXY_BID_LIMIT_PRICE_DATE
decode(al.modified_date-old_al.modified_date,
0, bl.bid_currency_price, null), -- BID_CURRENCY_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.bid_currency_limit_price, null), -- BID_CURRENCY_LIMIT_PRICE
'N', -- PROXY_BID_FLAG
decode(al.modified_date-old_al.modified_date,
0, bl.first_bid_price, null), -- FIRST_BID_PRICE
al.unit_of_measure, -- UNIT_OF_MEASURE
al.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
al.freight_terms_code, -- FREIGHT_TERMS_CODE
'N', -- TBD_PRICING_FLAG
l_auctpid, -- AUC_TRADING_PARTNER_ID
p_tpid, -- BID_TRADING_PARTNER_ID
decode(al.modified_date-old_al.modified_date,
0, bl.total_weighted_score, null), -- TOTAL_WEIGHTED_SCORE
decode(p_rebid_flag, 'Y', bl.rank, null), -- RANK
decode(al.modified_date-old_al.modified_date,
0, bl.po_min_rel_amount, null), -- PO_MIN_REL_AMOUNT
decode(al.modified_date-old_al.modified_date,
0, bl.po_bid_min_rel_amount, null), -- PO_BID_MIN_REL_AMOUNT
al.price_break_type, -- PRICE_BREAK_TYPE
decode(al.modified_date-old_al.modified_date,
0, bl.has_shipments_flag, al.has_shipments_flag), -- HAS_SHIPMENTS_FLAG
-- Rebid: set changed_line to N
-- Otherwise it is the same as the has_bid_flag
--CLM QA Bug : 9835426 : NC,NSP lines, assume as always modified
/* Decode(Nvl(al.clm_cost_constraint,'X'),'NC','Y',
decode(nvl(al.clm_cost_constraint,'X'),'NSP','Y',
decode(p_rebid_flag, 'Y', 'N',
decode(al.modified_date-old_al.modified_date, 0,
nvl(bl.has_bid_flag, 'N'), 'N')))),*/
decode(p_rebid_flag, 'Y', 'N',
decode(al.modified_date-old_al.modified_date, 0,
nvl(bl.has_bid_flag, 'N'), 'N')), -- IS_CHANGED_LINE_FLAG
al.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
al.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
decode(al.modified_date-old_al.modified_date,
0, bl.bid_currency_trans_price, null), -- BID_CURRENCY_TRANS_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.unit_price, null), -- UNIT_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.bid_currency_unit_price, null), -- BID_CURRENCY_UNIT_PRICE
decode(al.modified_date-old_al.modified_date,
0, bl.group_amount, null), -- GROUP_AMOUNT
decode(g_copy_only_from_auc, 'Y', al.has_payments_flag,
decode(al.modified_date-old_al.modified_date,0,bl.has_bid_payments_flag,al.has_payments_flag
)
),--HAS_BID_PAYMENTS_FLAG
decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.advance_amount,al.advance_amount
)
, al.advance_amount
),--ADVANCE_AMOUNT
decode(al.modified_date-old_al.modified_date, 0,decode(g_advance_negotiable,'Y',bl.bid_curr_advance_amount
,round(al.advance_amount * g_bid_rate, g_curr_prec)
)
, round(al.advance_amount * g_bid_rate, g_curr_prec)
),--BID_CURR_ADVANCE_AMOUNT
decode(al.modified_date-old_al.modified_date, 0,decode(g_recoupment_negotiable,'Y',bl.recoupment_rate_percent
,al.recoupment_rate_percent
)
, al.recoupment_rate_percent
),--RECOUPMENT_RATE_PERCENT
decode(al.modified_date-old_al.modified_date, 0,decode(g_prog_pymt_negotiable,'Y',bl.progress_pymt_rate_percent
,al.progress_pymt_rate_percent
)
, al.progress_pymt_rate_percent
),--PROGRESS_PYMT_RATE_PERCENT
decode(al.modified_date-old_al.modified_date, 0,decode(g_rtng_negotiable,'Y',bl.retainage_rate_percent,al.retainage_rate_percent
)
, al.retainage_rate_percent
),--RETAINAGE_RATE_PERCENT
decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.max_retainage_amount,al.max_retainage_amount
)
, al.max_retainage_amount
),--MAX_RETAINAGE_AMOUNT
decode(al.modified_date-old_al.modified_date, 0,decode(g_max_rtng_negotiable,'Y',bl.bid_curr_max_retainage_amt
, round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
)
, round(al.max_retainage_amount * g_bid_rate, g_curr_prec)
),--BID_CURR_MAX_RETAINAGE_AMT
decode(p_rebid_flag, 'Y', (select count(1) from pon_bid_payments_shipments
where bid_number=bl.bid_number and bid_line_number=bl.line_number)
,null
), --OLD_NO_OF_PAYMENTS
decode(p_rebid_flag, 'Y', bl.price, null), -- OLD_PRICE
decode(p_rebid_flag, 'Y', bl.bid_currency_unit_price, null),-- OLD_BID_CURRENCY_UNIT_PRICE
decode(p_rebid_flag, 'Y', bl.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
decode(p_rebid_flag, 'Y', bl.bid_currency_limit_price, null), -- OLD_BID_CURRENCY_LIMIT_PRICE
decode(p_rebid_flag, 'Y', bl.po_bid_min_rel_amount, null), -- OLD_PO_BID_MIN_REL_AMOUNT
decode(p_rebid_flag, 'Y', bl.quantity, null), -- OLD_QUANTITY
decode(p_rebid_flag, 'Y', bl.publish_Date, null), -- OLD_PUBLISH_DATE
decode(p_rebid_flag, 'Y', bl.promised_Date, null), -- OLD_PROMISED_DATE
decode(p_rebid_flag, 'Y', bl.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER
-- If the line was modified, set to N, else set to source has_bid_flag
-- If the source has_bid_flag is null, set to N since there was no source bid
--CLM QA Bug : 9835426 : For NC, NSP lines assume as always modified
/* decode(nvl(al.clm_cost_constraint,'X'),'NC','Y',
decode(nvl(al.clm_cost_constraint,'X'),'NSP','Y',
decode(al.modified_date-old_al.modified_date, 0,
nvl(bl.has_bid_flag, 'N'), 'N'))), */
decode(al.modified_date-old_al.modified_date, 0,
nvl(bl.has_bid_flag, 'N'), 'N'), -- HAS_BID_FLAG
decode(p_rebid_flag, 'Y', bl.bid_curr_advance_amount, null), -- OLD_BID_CURR_ADVANCE_AMOUNT
decode(p_rebid_flag, 'Y', bl.recoupment_rate_percent, null), -- OLD_RECOUPMENT_RATE_PERCENT
decode(p_rebid_flag, 'Y', bl.progress_pymt_rate_percent, null), -- OLD_PROGRESS_PYMT_RATE_PERCENT
decode(p_rebid_flag, 'Y', bl.retainage_rate_percent, null), -- OLD_RETAINAGE_RATE_PERCENT
decode(p_rebid_flag, 'Y', bl.bid_curr_max_retainage_amt, null), -- OLD_BID_CURR_MAX_RETAINAGE_AMT
decode(p_rebid_flag, 'Y',
decode(sign(bl.proxy_bid_limit_price-bl.price), -1, 'Y', 'N'), 'N'), -- COPY_PRICE_FOR_PROXY_FLAG
-- if re bid set the start price as source bid start price
decode(p_rebid_flag, 'Y', bl.bid_start_price, al.bid_start_price),
decode(al.modified_date-old_al.modified_date,
0, bl.has_quantity_tiers, al.has_quantity_tiers), -- HAS_quantity_tiers
/***********************************************************
* CLM - Clin Slin project Changes Start.
*********************************************************/
al.LINE_NUM_DISPLAY ,
al.GROUP_LINE_ID,
al.CLM_INFO_FLAG,
al.CLM_OPTION_INDICATOR,
al.CLM_BASE_LINE_NUM,
al.CLM_OPTION_NUM,
al.CLM_OPTION_FROM_DATE,
al.CLM_OPTION_TO_DATE,
al.CLM_FUNDED_FLAG,
/***********************************************************
* CLM - Clin Slin project Changes End.
*********************************************************/
/***********************************************************
* CLM - Complex Pricing project Changes Start.
*********************************************************/
al.CLM_COST_CONSTRAINT,
al.CLM_CONTRACT_TYPE,
al.CLM_IDC_TYPE,
-- Decode(al.CLM_CONTRACT_TYPE, NULL, NULL, l_uda_template_id)
l_uda_template_id,
/***********************************************************
* CLM - Complex Pricing project Changes End.
*********************************************************/
--ELINs project
decode(al.modified_date-old_al.modified_date, 0,
nvl(bl.has_user_bid_flag, 'N'), 'N'), -- HAS_USER_BID_FLAG
Decode(l_is_federal, 1,
Decode(p_rebid_flag, 'Y',Nvl(bl.has_bid_flag,'N'), 'N'), ''), --IS_REBID_ON_LINE_FLAG
-- Event Based Delivery Project
decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_PERIOD, null), -- CLM_PROMISE_PERIOD
decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_PERIOD_UOM, null), -- CLM_PROMISE_PERIOD_UOM
decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_POP_DURATION, null), -- CLM_PROMISE_POP_DURATION
decode(al.modified_date-old_al.modified_date,0, bl.CLM_PROMISE_POP_DURATION_UOM, null) -- CLM_PROMISE_POP_DURATION_UOM
FROM pon_auction_item_prices_all al,
pon_auction_item_prices_all old_al,
pon_bid_item_prices bl
WHERE al.auction_header_id = p_auc_header_id
AND bl.bid_number(+) = p_source_bid_num
AND bl.line_number(+) = al.line_number
AND old_al.auction_header_id (+) = bl.auction_header_id
AND old_al.line_number (+) = bl.line_number
AND al.line_number BETWEEN p_batch_start AND p_batch_end);
SELECT MAX(bid_number)
INTO l_prev_rnd_active_bid_number
FROM pon_bid_headers bh
WHERE bh.auction_header_id = l_auction_header_id_prev_round
AND bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id = p_tpcid
AND bh.bid_status ='ACTIVE'
AND NVL(bh.vendor_site_id, -1) = NVL(p_vensid, -1);
-- update the bid_start_price for the current bid lines
-- using values from the previous round auction/bid
UPDATE pon_bid_item_prices bl
SET bid_start_price = nvl((SELECT pon_bid_defaulting_pkg.apply_price_factors(p_auc_header_id, l_prev_rnd_active_bid_number, al.line_number, bl1.unit_price, bl1.quantity)
FROM pon_auction_item_prices_all al, pon_bid_item_prices bl1
WHERE al.auction_header_id = l_auction_header_id_prev_round
AND al.line_number = bl.line_number
AND al.line_number = bl1.line_number
AND bl1.bid_number = l_prev_rnd_active_bid_number), bid_start_price)
WHERE bl.bid_number = p_bid_number
AND bl.has_bid_flag = 'Y';
-- Delete excluded lines
IF (p_restricted_flag = 'Y') THEN
DELETE FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end
AND EXISTS
(SELECT le.line_number
FROM pon_party_line_exclusions le, pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auc_header_id
AND al.line_number = bl.line_number
AND le.auction_header_id = al.auction_header_id
AND le.line_number = coalesce(al.parent_line_number, al.group_line_id, al.clm_base_line_num,al.line_number)
AND le.trading_partner_id = p_tpid
AND le.vendor_site_id = p_vensid);
/* ELINs project : If exhibits are enabled, delete elins based on
* restriction on associated line.
* Delete elins associated to slins of a restricted clin.
* Delete elins associated to option lines of a restricted line.
*/
IF PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(l_org_id, l_doctype_id) = 'Y' THEN
DELETE FROM pon_bid_item_prices bl_outer
WHERE bl_outer.bid_number = p_bid_number
AND bl_outer.line_number BETWEEN p_batch_start AND p_batch_end
AND bl_outer.line_number IN
(SELECT al_outer.line_number
FROM pon_auction_exhibit_details exh_outer,
(SELECT al.line_number line_number
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auc_header_id
AND NOT EXISTS
(SELECT bl.line_number
FROM pon_bid_item_prices bl
WHERE bl.auction_header_id = al.auction_header_id
AND bl.bid_number = p_bid_number
AND bl.line_number = al.line_number)) missing_bid_lines,
pon_auction_item_prices_all al_outer
WHERE exh_outer.auction_header_id = p_auc_header_id
AND Nvl(exh_outer.is_cdrl, 'N') = 'N'
AND exh_outer.associated_to_line = missing_bid_lines.line_number
AND al_outer.auction_header_id = exh_outer.auction_header_id
AND al_outer.exhibit_number IS NOT NULL
AND al_outer.exhibit_number = exh_outer.exhibit_number);
log_message ('insert_into_bid_items', 'p_rebid_flag = ' || p_rebid_flag ||
', p_surrog_bid_flag = ' || p_surrog_bid_flag ||
', l_is_paused = ' || l_is_paused ||
', l_closed_compare_date = ' || to_char (l_closed_compare_date, 'dd-mon-yyyy hh24:mi:ss') ||
', l_last_pause_date = ' || to_char (l_last_pause_date, 'dd-mon-yyyy hh24:mi:ss'));
log_message ('insert_into_bid_items', 'This is not a rebid and this is not a surrogate bid.');
DELETE FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_bid_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end
AND EXISTS (SELECT al.line_number
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = p_auc_header_id
AND al.line_number = bl.line_number
AND al.close_bidding_date < l_closed_compare_date);
END insert_into_bid_items;
PROCEDURE insert_into_header_attributes
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
----------- Supplier Management: Supplier Evaluation -----------
p_evaluator_id IN pon_bid_headers.evaluator_id%TYPE,
p_eval_flag IN pon_bid_headers.evaluation_flag%TYPE,
----------------------------------------------------------------
p_rebid_flag IN VARCHAR2
) IS
l_has_scoring_teams_flag pon_auction_headers_all.has_scoring_teams_flag%TYPE;
SELECT has_scoring_teams_flag
INTO l_has_scoring_teams_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auc_header_id;
-- Insert header attributes
INSERT INTO pon_bid_attribute_values
(
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
DATATYPE,
VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SCORE,
SEQUENCE_NUMBER,
ATTR_LEVEL,
WEIGHTED_SCORE,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER,
OLD_VALUE
)
(SELECT
aa.auction_header_id, -- AUCTION_HEADER_ID
aa.line_number, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
aa.line_number, -- LINE_NUMBER
aa.attribute_name, -- ATTRIBUTE_NAME
aa.datatype, -- DATATYPE
decode(aa.modified_date-old_aa.modified_date,
0, ba.value, null), -- VALUE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
DECODE(l_has_scoring_teams_flag, 'N',decode(aa.modified_date-old_aa.modified_date,
0, ba.score, null),'Y', NULL), -- SCORE
aa.sequence_number, -- SEQUENCE_NUMBER
aa.attr_level, -- ATTR_LEVEL
decode(l_has_scoring_teams_flag, 'N', decode(aa.modified_date-old_aa.modified_date,
0, ba.weighted_score, null), 'Y', NULL), -- WEIGHTED_SCORE
aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
FROM pon_auction_attributes aa,
pon_bid_attribute_values ba,
pon_auction_attributes old_aa
WHERE aa.auction_header_id = p_auc_header_id
AND aa.line_number= -1
AND ba.bid_number (+) = p_source_bid_num
AND ba.line_number (+) = aa.line_number
AND ba.sequence_number (+) = aa.sequence_number
AND ba.auction_header_id = old_aa.auction_header_id (+)
AND ba.line_number = old_aa.line_number (+)
AND ba.sequence_number = old_aa.sequence_number (+));
UPDATE pon_bid_attribute_values
SET value = NULL,
score = NULL,
weighted_score = NULL
WHERE auction_header_id = p_auc_header_id
AND line_number = -1
AND bid_number = p_bid_number
AND attr_group_seq_number NOT IN
(SELECT pas.attr_group_seq_number
FROM pon_auction_sections pas,
pon_evaluation_team_sections pets,
pon_evaluation_team_members petm,
fnd_user fu
WHERE pas.auction_header_id = p_auc_header_id
AND pets.auction_header_id = p_auc_header_id
AND petm.auction_header_id = p_auc_header_id
AND pas.section_id = pets.section_id
AND pets.team_id = petm.team_id
AND petm.user_id = fu.user_id
AND fu.person_party_id = p_evaluator_id
);
END insert_into_header_attributes;
PROCEDURE insert_into_line_attributes
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_rebid_flag IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
BEGIN
-- Insert line attributes
INSERT INTO pon_bid_attribute_values
(
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
DATATYPE,
VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SCORE,
SEQUENCE_NUMBER,
ATTR_LEVEL,
WEIGHTED_SCORE,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER,
OLD_VALUE
)
-- NOTE: we check the has_bid_flag because it is 'N' if
-- the line has been modified since the defaulting happened
(SELECT
aa.auction_header_id, -- AUCTION_HEADER_ID
aa.line_number, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
aa.line_number, -- LINE_NUMBER
aa.attribute_name, -- ATTRIBUTE_NAME
aa.datatype, -- DATATYPE
decode(bl.has_bid_flag, 'Y', ba.value, null), -- VALUE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
decode(bl.has_bid_flag, 'Y', ba.score, null), -- SCORE
aa.sequence_number, -- SEQUENCE_NUMBER
aa.attr_level, -- ATTR_LEVEL
decode(bl.has_bid_flag, 'Y', ba.weighted_score, null), -- WEIGHTED_SCORE
aa.attr_group_seq_number, -- ATTR_GROUP_SEQ_NUMBER
aa.attr_disp_seq_number, -- ATTR_DISP_SEQ_NUMBER
decode(p_rebid_flag, 'Y', ba.value, null) -- OLD_VALUE
FROM pon_auction_attributes aa,
pon_bid_attribute_values ba,
pon_bid_item_prices bl
WHERE aa.auction_header_id = p_auc_header_id
AND aa.line_number > 0
AND bl.bid_number = p_bid_number
AND bl.line_number = aa.line_number
AND ba.bid_number (+) = p_source_bid_num
AND ba.line_number (+) = aa.line_number
AND ba.sequence_number (+) = aa.sequence_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
END insert_into_line_attributes;
PROCEDURE insert_into_price_factors
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_supp_seq_number IN pon_pf_supplier_values.supplier_seq_number%TYPE,
p_rebid_flag IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
l_price_prec pon_bid_headers.number_price_decimals%TYPE;
SELECT bh.number_price_decimals,
cu.precision,
bh.rate
INTO l_price_prec,
l_curr_prec,
l_rate
FROM pon_bid_headers bh,
fnd_currencies cu
WHERE bh.bid_number = p_bid_number
AND cu.currency_code = bh.bid_currency_code;
SELECT ah.supplier_view_type, ah.pf_type_allowed
INTO l_supplier_view, l_pf_type
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
INSERT INTO pon_bid_price_elements
(
BID_NUMBER,
LINE_NUMBER,
PRICE_ELEMENT_TYPE_ID,
AUCTION_HEADER_ID,
PRICING_BASIS,
NEGATIVE_COST_FACTOR_FLAG,
AUCTION_CURRENCY_VALUE,
BID_CURRENCY_VALUE,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE,
OLD_BID_CURRENCY_VALUE
)
(SELECT
p_bid_number, -- BID_NUMBER
apf.line_number, -- LINE_NUMBER
apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_auc_header_id, -- AUCTION_HEADER_ID
apf.pricing_basis, -- PRICING_BASIS
apf.NEGATIVE_COST_FACTOR_FLAG, -- NEGATIVE_COST_FACTOR_FLAG
decode(bl.has_bid_flag, 'Y',
bpf.auction_currency_value, null), -- AUCTION_CURRENCY_VALUE
decode(bl.has_bid_flag, 'Y',
bpf.bid_currency_value, null), -- BID_CURRENCY_VALUE
apf.sequence_number, -- SEQUENCE_NUMBER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
apf.pf_type, -- PF_TYPE
decode(p_rebid_flag, 'Y', bpf.bid_currency_value, null) -- OLD_BID_CURRENCY_VALUE
FROM pon_price_elements apf,
pon_bid_price_elements bpf,
pon_bid_item_prices bl
WHERE apf.auction_header_id = p_auc_header_id
AND apf.pf_type = 'SUPPLIER' -- only for supplier price factors
AND bl.bid_number = p_bid_number
AND bl.line_number = apf.line_number
AND bl.display_price_factors_flag = 'Y' -- only for lines with price factors
AND bpf.bid_number (+) = p_source_bid_num
AND bpf.line_number (+) = apf.line_number
AND bpf.price_element_type_id (+) = apf.price_element_type_id
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
INSERT INTO pon_bid_price_elements
(
BID_NUMBER,
LINE_NUMBER,
PRICE_ELEMENT_TYPE_ID,
AUCTION_HEADER_ID,
PRICING_BASIS,
NEGATIVE_COST_FACTOR_FLAG,
AUCTION_CURRENCY_VALUE,
BID_CURRENCY_VALUE,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE,
OLD_BID_CURRENCY_VALUE
)
(SELECT
p_bid_number, -- BID_NUMBER
apf.line_number, -- LINE_NUMBER
apf.price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_auc_header_id, -- AUCTION_HEADER_ID
apf.pricing_basis, -- PRICING_BASIS
apf.NEGATIVE_COST_FACTOR_FLAG, --NEGATIVE_COST_FACTOR_FLAG
pf.value, -- AUCTION_CURRENCY_VALUE
decode(apf.pricing_basis,
'PER_UNIT', round(pf.value * l_rate, l_price_prec),
'FIXED_AMOUNT', round(pf.value * l_rate, l_curr_prec),
'PERCENTAGE', pf.value), -- BID_CURRENCY_VALUE
apf.sequence_number, -- SEQUENCE_NUMBER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
apf.pf_type, -- PF_TYPE
null -- OLD_BID_CURRENCY_VALUE
FROM pon_price_elements apf,
pon_pf_supplier_values pf,
pon_bid_item_prices bl
WHERE apf.auction_header_id = p_auc_header_id
AND apf.pf_type = 'BUYER' -- only buyer pf that are to be displayed
AND apf.display_to_suppliers_flag = 'Y'
AND bl.bid_number = p_bid_number
AND bl.line_number = apf.line_number
AND bl.display_price_factors_flag = 'Y'
AND pf.auction_header_id = apf.auction_header_id
AND pf.line_number = apf.line_number
AND pf.pf_seq_number = apf.sequence_number
AND pf.supplier_seq_number = p_supp_seq_number
AND nvl(pf.value, 0) <> 0
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
END insert_into_price_factors;
PROCEDURE insert_into_price_tiers
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_rebid_flag IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
BEGIN
-- Get all auction side price breaks for modified lines that had a bid
-- or for unmodified lines that had no bid
INSERT INTO pon_bid_shipments
(
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
AUCTION_SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
MAX_QUANTITY,
PRICE_TYPE,
PRICE,
BID_CURRENCY_PRICE,
PRICE_DISCOUNT,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG,
UNIT_PRICE,
BID_CURRENCY_UNIT_PRICE
)
(SELECT
p_bid_number, -- BID_NUMBER
apb.line_number, -- LINE_NUMBER
apb.shipment_number+1, -- SHIPMENT_NUMBER
p_auc_header_id, -- AUCTION_HEADER_ID
apb.line_number, -- AUCTION_LINE_NUMBER
apb.shipment_number, -- AUCTION_SHIPMENT_NUMBER
apb.shipment_type, -- SHIPMENT_TYPE
apb.ship_to_organization_id, -- SHIP_TO_ORGANIZATION_ID
apb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
apb.quantity, -- QUANTITY
apb.max_quantity, --- MAX_QUANTITY
'PRICE', -- PRICE_TYPE
apb.price, -- PRICE
null, -- BID_CURRENCY_PRICE
null, -- PRICE_DISCOUNT
apb.effective_start_date, -- EFFECTIVE_START_DATE
apb.effective_end_date, -- EFFECTIVE_END_DATE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
apb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
null, -- UNIT_PRICE
null -- BID_CURRENCY_UNIT_PRICE
FROM pon_auction_shipments_all apb,
pon_bid_item_prices bl
WHERE apb.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND bl.line_number = apb.line_number
-- we only insert those price breaks for which the line was modified
-- or had no previosu bid on it - has_bid_flag = N in both cases
AND bl.has_bid_flag = 'N'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
INSERT INTO pon_bid_shipments
(
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
AUCTION_SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
MAX_QUANTITY,
PRICE_TYPE,
PRICE,
BID_CURRENCY_PRICE,
PRICE_DISCOUNT,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG,
UNIT_PRICE,
BID_CURRENCY_UNIT_PRICE,
OLD_BID_CURRENCY_UNIT_PRICE,
OLD_BID_CURRENCY_PRICE,
OLD_PRICE_DISCOUNT,
OLD_SHIP_TO_ORG_ID,
OLD_SHIP_TO_LOC_ID,
OLD_EFFECTIVE_START_DATE,
OLD_EFFECTIVE_END_DATE,
OLD_QUANTITY,
OLD_MAX_QUANTITY,
OLD_PRICE_TYPE
)
(SELECT
p_bid_number, -- BID_NUMBER
bpb.line_number, -- LINE_NUMBER
bpb.shipment_number, -- SHIPMENT_NUMBER
p_auc_header_id, -- AUCTION_HEADER_ID
bpb.line_number, -- AUCTION_LINE_NUMBER
bpb.auction_shipment_number,-- AUCTION_SHIPMENT_NUMBER
bpb.shipment_type, -- SHIPMENT_TYPE
bpb.ship_to_organization_id,-- SHIP_TO_ORGANIZATION_ID
bpb.ship_to_location_id, -- SHIP_TO_LOCATION_ID
bpb.quantity, -- QUANTITY
bpb.max_quantity, -- MAX_QUANTITY
bpb.price_type, -- PRICE_TYPE
bpb.price, -- PRICE
bpb.bid_currency_price, -- BID_CURRENCY_PRICE
bpb.price_discount, -- PRICE_DISCOUNT
bpb.effective_start_date, -- EFFECTIVE_START_DATE
bpb.effective_end_date, -- EFFECTIVE_END_DATE
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
bpb.has_price_differentials_flag, -- HAS_PRICE_DIFFERENTIALS_FLAG
bpb.unit_price, -- UNIT_PRICE
bpb.bid_currency_unit_price,-- BID_CURRENCY_UNIT_PRICE
decode(p_rebid_flag, 'Y', bpb.bid_currency_unit_price, null), -- OLD_BID_CURRENCY_UNIT_PRICE
decode(p_rebid_flag, 'Y', bpb.bid_currency_price, null), -- OLD_BID_CURRENCY_PRICE
decode(p_rebid_flag, 'Y', bpb.price_discount, null), -- OLD_PRICE_DISCOUNT
decode(p_rebid_flag, 'Y', bpb.ship_to_organization_id, null), -- OLD_SHIP_TO_ORG_ID
decode(p_rebid_flag, 'Y', bpb.ship_to_location_id, null), -- OLD_SHIP_TO_LOC_ID
decode(p_rebid_flag, 'Y', bpb.effective_start_date, null), -- OLD_EFFECTIVE_START_DATE
decode(p_rebid_flag, 'Y', bpb.effective_end_date, null), -- OLD_EFFECTIVE_END_DATE
decode(p_rebid_flag, 'Y', bpb.quantity, null), -- OLD_QUANTITY
decode(p_rebid_flag, 'Y', bpb.max_quantity, null), -- OLD_MAX_QUANTITY
decode(p_rebid_flag, 'Y', bpb.price_type, null) -- OLD_PRICE_TYPE
FROM pon_bid_shipments bpb,
pon_bid_item_prices bl
WHERE bpb.bid_number = p_source_bid_num
AND bl.bid_number = p_bid_number
AND bl.line_number = bpb.line_number
-- only unmodified lines with bids
AND bl.has_bid_flag = 'Y'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
END insert_into_price_tiers;
PROCEDURE insert_into_price_diff
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_rebid_flag IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
BEGIN
INSERT INTO pon_bid_price_differentials
(
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE,
MULTIPLIER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OLD_MULTIPLIER
)
(
-- Insert all line level price differentials
(SELECT
p_auc_header_id, -- AUCTION_HEADER_ID
p_bid_number, -- BID_NUMBER
apd.line_number, -- LINE_NUMBER
apd.shipment_number, -- SHIPMENT_NUMBER
apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
apd.price_type, -- PRICE_TYPE
decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
FROM pon_price_differentials apd,
pon_bid_price_differentials bpd,
pon_bid_item_prices bl
WHERE apd.auction_header_id = p_auc_header_id
AND apd.shipment_number = -1 -- only line level differentials
AND bl.auction_header_id = apd.auction_header_id
AND bl.bid_number = p_bid_number
AND bl.line_number = apd.line_number
AND bpd.bid_number (+) = p_source_bid_num
AND bpd.line_number (+) = apd.line_number
AND bpd.shipment_number (+) = apd.shipment_number
AND bpd.price_differential_number (+) = apd.price_differential_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
UNION
-- Insert shipment level price differentials, but only for
-- those shipments that got copied over
(SELECT
p_auc_header_id, -- AUCTION_HEADER_ID
p_bid_number, -- BID_NUMBER
apd.line_number, -- LINE_NUMBER
apd.shipment_number+1, -- SHIPMENT_NUMBER
apd.price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
apd.price_type, -- PRICE_TYPE
decode(bl.has_bid_flag, 'Y', bpd.multiplier, null), -- MULTIPLIER
sysdate, -- CREATION_DATE
p_userid, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
p_userid, -- LAST_UPDATED_BY
null, -- LAST_UPDATE_LOGIN
decode(p_rebid_flag, 'Y', bpd.multiplier, null) -- OLD_MULTIPLIER
FROM pon_price_differentials apd,
pon_bid_price_differentials bpd,
pon_bid_shipments bpb,
pon_bid_item_prices bl
WHERE apd.auction_header_id = p_auc_header_id
AND apd.shipment_number <> -1 -- only shipment differentials
AND bl.bid_number = p_bid_number
AND bl.line_number = apd.line_number
AND bpb.bid_number = p_bid_number
AND bpb.line_number = apd.line_number
AND bpb.shipment_type = 'PRICE BREAK'
AND bpb.shipment_number = apd.shipment_number + 1
AND bpd.bid_number (+) = p_source_bid_num
AND bpd.line_number (+) = apd.line_number
AND bpd.shipment_number (+) = apd.shipment_number + 1
AND bpd.price_differential_number (+) = apd.price_differential_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end)
);
END insert_into_price_diff;
PROCEDURE insert_into_payments
(
p_auc_header_id IN pon_auction_headers_all.auction_header_id%TYPE,
p_bid_number IN pon_bid_headers.bid_number%TYPE,
p_source_bid_num IN pon_bid_headers.bid_number%TYPE,
p_copy_only_from_auc IN VARCHAR2,
p_supplier_flag IN pon_auction_headers_all.SUPPLIER_ENTERABLE_PYMT_FLAG%TYPE,
p_userid IN pon_bid_headers.created_by%TYPE,
p_rebid_flag IN VARCHAR2,
p_new_round_or_amended IN VARCHAR2,
p_batch_start IN pon_bid_item_prices.line_number%TYPE,
p_batch_end IN pon_bid_item_prices.line_number%TYPE
) IS
CURSOR c_bid_pymnts_attach IS
SELECT
DISTINCT
source_pay.bid_number source_bid_number,
source_pay.bid_line_number source_bid_line_number,
source_pay.bid_payment_id source_bid_payment_id,
dest_pay.bid_number dest_bid_number,
dest_pay.bid_line_number dest_bid_line_number,
dest_pay.bid_payment_id dest_bid_payment_id
FROM
PON_BID_PAYMENTS_SHIPMENTS source_pay,
FND_ATTACHED_DOCUMENTS fnd,
PON_BID_PAYMENTS_SHIPMENTS dest_pay,
PON_BID_ITEM_PRICES bl
WHERE bl.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND bl.has_bid_flag = 'Y'
AND dest_pay.bid_number = bl.bid_number
AND dest_pay.bid_line_number = bl.line_number
AND source_pay.bid_number = p_source_bid_num
AND dest_pay.bid_line_number = source_pay.bid_line_number
AND dest_pay.payment_display_number = source_pay.payment_display_number
AND fnd.pk1_value = source_pay.bid_number
AND fnd.pk2_value = source_pay.bid_line_number
AND fnd.pk3_value = source_pay.bid_payment_id
AND fnd.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
l_module CONSTANT VARCHAR2(35) := 'Insert_into_payments';
message => 'entering insert_into_payments' );
INSERT INTO pon_bid_payments_shipments
(
BID_NUMBER,
BID_LINE_NUMBER,
BID_PAYMENT_ID,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_DESCRIPTION,
AUCTION_PAYMENT_ID,
PAYMENT_TYPE_CODE,
QUANTITY,
UOM_CODE,
PROMISED_DATE
)
(SELECT
p_bid_number, --BID_NUMBER,
bl.line_number, --BID_LINE_NUMBER,
pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
p_auc_header_id, --AUCTION_HEADER_ID,
bl.line_number, --AUCTION_LINE_NUMBER,
sysdate, --CREATION_DATE,
p_userid, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
p_userid, --LAST_UPDATED_BY,
fnd_global.login_id, --LAST_UPDATE_LOGIN
apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
apmt.PAYMENT_ID, --AUCTION_PAYMENT_ID,
apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
apmt.QUANTITY, --QUANTITY,
apmt.UOM_CODE, --UOM_CODE,
bl.promised_date --PROMISED_DATE,
FROM pon_bid_item_prices bl,pon_auc_payments_shipments apmt
WHERE bl.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND bl.auction_header_id = apmt.auction_header_id
AND bl.line_number = apmt.line_number
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
message => 'Insert for lines not bid' );
INSERT INTO pon_bid_payments_shipments
(
BID_NUMBER,
BID_LINE_NUMBER,
BID_PAYMENT_ID,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_DESCRIPTION,
AUCTION_PAYMENT_ID,
PAYMENT_TYPE_CODE,
QUANTITY,
UOM_CODE,
PROMISED_DATE
)
(SELECT
p_bid_number, --BID_NUMBER,
bl.line_number, --BID_LINE_NUMBER,
pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
p_auc_header_id, --AUCTION_HEADER_ID,
bl.line_number, --AUCTION_LINE_NUMBER,
sysdate, --CREATION_DATE,
p_userid, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
p_userid, --LAST_UPDATED_BY,
fnd_global.login_id, --LAST_UPDATE_LOGIN
apmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
apmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
decode(p_supplier_flag, 'N',apmt.PAYMENT_ID,null), --AUCTION_PAYMENT_ID,
apmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
apmt.QUANTITY, --QUANTITY,
apmt.UOM_CODE, --UOM_CODE,
bl.promised_date --PROMISED_DATE,
FROM pon_bid_item_prices bl,
pon_auc_payments_shipments apmt
WHERE bl.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND bl.auction_header_id = apmt.auction_header_id
AND bl.line_number = apmt.line_number
AND bl.has_bid_flag = 'N'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
message => 'Insert for lines that were bid' );
INSERT INTO pon_bid_payments_shipments
(
BID_NUMBER,
BID_LINE_NUMBER,
BID_PAYMENT_ID,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_DESCRIPTION,
AUCTION_PAYMENT_ID,
PAYMENT_TYPE_CODE,
QUANTITY,
UOM_CODE,
PRICE,
BID_CURRENCY_PRICE,
PROMISED_DATE,
OLD_PAYMENT_DISPLAY_NUMBER,
OLD_PAYMENT_TYPE_CODE,
OLD_PAYMENT_DESCRIPTION,
OLD_QUANTITY,
OLD_UOM_CODE,
OLD_BID_CURRENCY_PRICE,
OLD_PROMISED_DATE
)
(SELECT
p_bid_number, --BID_NUMBER,
bl.line_number, --BID_LINE_NUMBER,
pon_bid_payments_shipments_s1.nextval, --BID_PAYMENT_ID,
p_auc_header_id, --AUCTION_HEADER_ID,
bl.line_number, --AUCTION_LINE_NUMBER,
sysdate, --CREATION_DATE,
p_userid, --CREATED_BY,
sysdate, --LAST_UPDATE_DATE,
p_userid, --LAST_UPDATED_BY,
fnd_global.login_id, --LAST_UPDATE_LOGIN
bpmt.PAYMENT_DISPLAY_NUMBER, --PAYMENT_DISPLAY_NUMBER,
bpmt.PAYMENT_DESCRIPTION, --PAYMENT_DESCRIPTION,
decode(p_new_round_or_amended , 'Y' ,
decode(p_supplier_flag ,'N',(select payment_id from pon_auc_payments_shipments
where auction_header_id=p_auc_header_id
AND line_number = bl.line_number
AND payment_display_number= bpmt.PAYMENT_DISPLAY_NUMBER
)
, null
),bpmt.AUCTION_PAYMENT_ID
), --AUCTION_PAYMENT_ID,
bpmt.PAYMENT_TYPE_CODE, --PAYMENT_TYPE_CODE,
bpmt.QUANTITY, --QUANTITY,
bpmt.UOM_CODE, --UOM_CODE,
bpmt.PRICE, --PRICE,
bpmt.BID_CURRENCY_PRICE, --BID_CURRENCY_PRICE,
bpmt.promised_date, --PROMISED_DATE,
decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DISPLAY_NUMBER,null), --OLD_PAYMENT_DISPLAY_NUMBER,
decode(p_rebid_flag, 'Y',bpmt.PAYMENT_TYPE_CODE,null), --OLD_PAYMENT_TYPE_CODE,
decode(p_rebid_flag, 'Y',bpmt.PAYMENT_DESCRIPTION,null), --OLD_PAYMENT_DESCRIPTION,
decode(p_rebid_flag, 'Y',bpmt.QUANTITY,null), --OLD_QUANTITY,
decode(p_rebid_flag, 'Y',bpmt.UOM_CODE, null), --OLD_UOM_CODE,
decode(p_rebid_flag, 'Y',bpmt.BID_CURRENCY_PRICE, null), --OLD_BID_CURRENCY_PRICE,
decode(p_rebid_flag, 'Y',bpmt.PROMISED_DATE, null) --OLD_PROMISED_DATE
FROM pon_bid_item_prices bl,
pon_bid_payments_shipments bpmt
WHERE bl.auction_header_id = p_auc_header_id
AND bl.bid_number = p_bid_number
AND bpmt.bid_number = p_source_bid_num
AND bpmt.bid_line_number = bl.line_number
AND bl.has_bid_flag = 'Y'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end);
X_last_update_login => fnd_global.login_id);
message => 'exiting insert_into_payments' );
END insert_into_payments;
SELECT DISTINCT ad.pk3_value
FROM fnd_attached_documents ad, pon_bid_item_prices bl
WHERE ad.entity_name = 'PON_BID_ITEM_PRICES'
AND ad.pk1_value = p_source_header_id
AND ad.pk2_value = p_source_bid_num
AND ad.pk3_value IS NOT null
AND bl.bid_number = p_bid_number
AND bl.line_number = to_number(ad.pk3_value)
AND bl.has_bid_flag = 'Y'
AND bl.line_number BETWEEN p_batch_start AND p_batch_end;
x_last_update_login => fnd_global.login_id,
x_to_category_id => p_to_category_id);
x_last_update_login => fnd_global.login_id);
SELECT decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N')
INTO l_full_qty_reqd
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
UPDATE pon_bid_item_prices bl
SET bl.has_bid_flag = 'N'
WHERE bl.bid_number = p_bid_number;
UPDATE pon_bid_item_prices bl
SET bl.has_bid_flag = 'Y'
WHERE bl.bid_number = p_bid_number
AND ((bl.promised_date IS NOT null
OR bl.price IS NOT null
OR bl.proxy_bid_limit_price IS NOT null
OR bl.po_bid_min_rel_amount IS NOT null
OR bl.note_to_auction_owner IS NOT null)
OR EXISTS
-- Check auction side to check quantity
(SELECT al.line_number
FROM pon_auction_item_prices_all al
WHERE al.auction_header_id = bl.auction_header_id
AND al.line_number = bl.line_number
AND (l_full_qty_reqd <> 'Y'
AND al.order_type_lookup_code <> 'AMOUNT'
AND al.group_type <> 'LOT_LINE'
AND bl.quantity IS NOT null))
OR 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 NOT null
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 bpf.bid_currency_value IS NOT null
AND rownum = 1)
OR EXISTS
-- Check shipments
(SELECT bs.line_number
FROM pon_bid_shipments bs
WHERE bs.bid_number = bl.bid_number
AND bs.line_number = bl.line_number
AND (bs.auction_shipment_number IS null
OR bs.price_type = 'PRICE' AND bs.bid_currency_unit_price IS NOT null
OR bs.price_type = 'PRICE DISCOUNT' AND bs.price_discount IS NOT null
OR bs.bid_currency_price IS NOT null)
AND rownum = 1)
OR 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 bpd.multiplier IS NOT null
AND rownum = 1)
);
SELECT pbip.DISPLAY_PRICE_FACTORS_FLAG, pbip.quantity AS bid_quantity, pbip.bid_currency_unit_price, pbip.line_number, paip.order_type_lookup_code, paip.quantity AS auc_quantity
FROM PON_BID_ITEM_PRICES pbip, pon_auction_item_prices_all paip
WHERE paip.auction_header_id = p_auc_header_id
AND pbip.bid_number = x_bid_number
AND pbip.line_number(+) = paip.line_number;
SELECT PRICING_BASIS, BID_CURRENCY_VALUE, PRICE_ELEMENT_TYPE_ID
FROM PON_BID_PRICE_ELEMENTS
WHERE AUCTION_HEADER_ID = p_auc_header_id
AND BID_NUMBER = x_bid_number
AND LINE_NUMBER = v_line_number
AND PF_TYPE <> 'BUYER';
SELECT decode(bp.access_type, 'RESTRICTED', 'Y', 'N'), bp.sequence
INTO l_restricted_flag, l_supp_seq_number
FROM pon_bidding_parties bp
WHERE bp.auction_header_id = p_auc_header_id
AND bp.trading_partner_id = p_tpid
AND nvl(bp.vendor_site_id, -1) = p_vensid;
insert_into_bid_header
(p_auc_header_id,
p_source_bid_num,
p_tpid,
p_tpname,
p_tpcid,
p_tpcname,
p_userid,
p_venid,
p_vensid,
p_venscode,
p_auctpid,
p_auctpcid,
p_buyer_user,
---- Supplier Management: Supplier Evaluation ----
p_evaluator_id,
p_eval_flag,
--------------------------------------------------
p_rebid_flag,
p_prev_bid_disq,
p_prev_bid_wthd,
x_bid_number);
insert_into_header_attributes
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
---- Supplier Management: Supplier Evaluation ----
p_evaluator_id,
p_eval_flag,
--------------------------------------------------
p_rebid_flag);
SELECT bh.auction_header_id
INTO l_source_header_id
FROM pon_bid_headers bh
WHERE bh.bid_number = p_source_bid_num;
select decode(pah.auction_header_id_prev_round, null, 'N', 'Y'),
nvl(pah.two_part_flag, 'N'),
(select nvl(two_part_flag,'N') from pon_auction_headers_all
where auction_header_id = pah.auction_header_id_prev_round)
into l_is_new_round, l_curr_two_part, l_prev_two_part
from pon_auction_headers_all pah
where auction_header_id = p_auc_header_id;
select (select category_id from fnd_document_categories
where name = pon_auction_pkg.g_supplier_attachment),
(select category_id from fnd_document_categories
where name = pon_auction_pkg.g_technical_attachment)
into l_categ_id_supp, l_categ_id_supp_tech
from fnd_document_categories
where ROWNUM = 1;
x_last_update_login => fnd_global.login_id,
x_to_category_id => l_to_category_id);
x_last_update_login => fnd_global.login_id);
select nvl(ah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'N'), nvl(progress_payment_type,'NONE'), contract_type,
nvl(ADVANCE_NEGOTIABLE_FLAG,'N'),nvl(RECOUPMENT_NEGOTIABLE_FLAG,'N'),nvl(PROGRESS_PYMT_NEGOTIABLE_FLAG,'N'),
nvl(MAX_RETAINAGE_NEGOTIABLE_FLAG,'N'),nvl(RETAINAGE_NEGOTIABLE_FLAG,'N')
into l_supplier_flag, l_payment_type, l_contract_type,
g_advance_negotiable,g_recoupment_negotiable,g_prog_pymt_negotiable,g_max_rtng_negotiable,g_rtng_negotiable
FROM pon_auction_headers_all ah where ah.auction_header_id=p_auc_header_id;
SELECT cu.precision,
bh.rate,
bh.surrog_bid_flag
INTO g_curr_prec,
g_bid_rate,
l_surrog_bid_flag
FROM pon_bid_headers bh,
fnd_currencies cu
WHERE bh.bid_number = x_bid_number
AND cu.currency_code = bh.bid_currency_code;
select decode(oldah.SUPPLIER_ENTERABLE_PYMT_FLAG, 'Y','Y', 'N')
into g_copy_only_from_auc
FROM pon_auction_headers_all oldah
WHERE oldah.auction_header_id = l_source_header_id;
message => 'after selecting supplier flag copy only from auc value is' || g_copy_only_from_auc );
select doctype_id
into l_doctype_id
from pon_auction_headers_all
where auction_header_id = p_auc_header_id;
SELECT ah.max_internal_line_num ,ah.price_tiers_indicator, Nvl(ALLOW_UNSOL_OFFER_LINES,'N') --Unsolicited Lines Project
INTO l_max_line_number,l_price_tiers_indicator,l_allow_unsol_lines --Unsolicited Lines Project
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
insert_into_bid_items
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_tpid,
p_tpcid,
p_userid,
p_vensid,
p_rebid_flag,
l_restricted_flag,
l_batch_start,
l_batch_end,
l_surrog_bid_flag);
insert_into_line_attributes
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
p_rebid_flag,
l_batch_start,
l_batch_end);
insert_into_price_factors
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
l_supp_seq_number,
p_rebid_flag,
l_batch_start,
l_batch_end);
insert_into_price_tiers
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
p_rebid_flag,
l_batch_start,
l_batch_end);
insert_into_price_diff
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
p_rebid_flag,
l_batch_start,
l_batch_end);
message => 'before calling insert_into_payments' );
insert_into_payments
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
g_copy_only_from_auc,
l_supplier_flag,
p_userid,
p_rebid_flag,
p_new_round_or_amended,
l_batch_start,
l_batch_end);
message => 'after calling insert_into_payments' );
SELECT RATE, TRADING_PARTNER_ID, VENDOR_SITE_ID, NUMBER_PRICE_DECIMALS
INTO l_rate, l_tp_id, l_vendor_site_id, l_precision
FROM PON_BID_HEADERS
WHERE BID_NUMBER = x_bid_number
AND AUCTION_HEADER_ID = p_auc_header_id
AND BID_STATUS = 'DRAFT';
SELECT decode(full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'), supplier_view_type
INTO l_full_quan_req, l_supplier_view
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_auc_header_id;
SELECT unit_price, fixed_amount, percentage
INTO l_unit, l_amount, l_percentage
FROM pon_pf_supplier_formula
WHERE auction_header_id = p_auc_header_id
AND line_number = l_line_number
AND (trading_partner_id = l_tp_id AND
vendor_site_id = l_vendor_site_id);
select ROUND(l_total_price, decode(l_precision, 10000, 10, l_precision)) INTO l_truncated_value FROM dual;
SELECT ROUND(l_trans_price, decode(l_precision, 10000, 10, l_precision)) INTO l_bid_curr_trans_price FROM dual;
UPDATE PON_BID_ITEM_PRICES
SET BID_CURRENCY_PRICE = l_truncated_value,
BID_CURRENCY_TRANS_PRICE = l_bid_curr_trans_price,
PRICE = l_price
WHERE AUCTION_HEADER_ID = p_auc_header_id
AND BID_NUMBER = x_bid_number
AND LINE_NUMBER = l_line_number;
SELECT ROUND(l_trans_price, decode(l_precision, 10000, 10, l_precision)) INTO l_bid_curr_trans_price FROM dual;
UPDATE PON_BID_ITEM_PRICES
SET BID_CURRENCY_PRICE = l_bid_curr_unit_price,
BID_CURRENCY_TRANS_PRICE = l_bid_curr_trans_price,
PRICE = l_price
WHERE AUCTION_HEADER_ID = p_auc_header_id
AND BID_NUMBER = x_bid_number
AND LINE_NUMBER = l_line_number;
select bid_number,nvl(surrog_bid_flag, 'N')
into l_other_draft_bid_number,l_surrogate_bid_flag
from pon_bid_headers
where auction_header_id = p_auc_header_id
and bid_number <> x_bid_number
and bid_status = 'DRAFT'
and trading_partner_id = p_tpid
and trading_partner_contact_id = p_tpcid
and nvl(vendor_site_id, -1) = nvl(p_vensid, -1);
SELECT Decode(pbh.auction_header_id, p_auc_header_id, 'Y', 'N')
INTO l_can_copy_unsol_lines
FROM pon_bid_headers pbh
WHERE pbh.bid_number = p_source_bid_num;
UPDATE pon_bid_headers bh
SET bh.display_price_factors_flag =
nvl((SELECT 'Y'
FROM pon_bid_item_prices bl
WHERE bl.bid_number = bh.bid_number
AND bl.display_price_factors_flag = 'Y'
AND rownum = 1), 'N')
WHERE bh.bid_number = x_bid_number;
SELECT ah.auction_header_id,
ah.trading_partner_id,
decode(ah.has_price_elements, 'Y', 'Y', 'N'),
decode(ah.contract_type, 'BLANKET', 'Y', 'CONTRACT', 'Y', 'N'),
decode(ah.full_quantity_bid_code, 'FULL_QTY_BIDS_REQD', 'Y', 'N'),
bh.trading_partner_id,
bh.trading_partner_contact_id,
bh.vendor_site_id,
bh.created_by,
bh.rate,
bh.number_price_decimals,
cu.precision
INTO l_auc_header_id,
l_auctpid,
l_has_pe_flag,
l_blanket,
l_full_qty,
l_tpid,
l_tpcid,
l_vensid,
l_userid,
l_rate,
l_price_prec,
l_curr_prec
FROM pon_bid_headers bh, pon_auction_headers_all ah, fnd_currencies cu
WHERE bh.bid_number = p_bid_number
AND ah.auction_header_id = bh.auction_header_id
AND cu.currency_code = bh.bid_currency_code;
SELECT bp.sequence
INTO l_supp_seq_number
FROM pon_bidding_parties bp
WHERE bp.auction_header_id = l_auc_header_id
AND bp.trading_partner_id = l_tpid
AND bp.vendor_site_id = l_vensid;
SELECT bh.bid_number, 'Y'
INTO l_source_bid_num, x_rebid_flag
FROM pon_bid_headers bh
WHERE bh.auction_header_id = l_auc_header_id
AND bh.trading_partner_id = l_tpid
AND bh.trading_partner_contact_id = l_tpcid
AND bh.vendor_site_id = l_vensid
AND bh.bid_status = 'ACTIVE'
AND rownum = 1
ORDER BY bh.publish_date DESC;
SELECT ah.max_internal_line_num
INTO l_max_line_number
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = l_auc_header_id;
-- We need to insert those auction side lines which haven't recieved bids
-- line level display pf flag also populated
insert_auction_lines
(l_auc_header_id,
p_bid_number,
l_userid,
l_auctpid,
l_tpid,
l_vensid,
l_has_pe_flag,
l_blanket,
l_full_qty,
l_supp_seq_number,
l_rate,
l_price_prec,
l_curr_prec,
l_batch_start,
l_batch_end);
UPDATE pon_bid_headers bh
SET bh.display_price_factors_flag =
nvl((SELECT 'Y'
FROM pon_bid_item_prices bl
WHERE bl.bid_number = bh.bid_number
AND bl.display_price_factors_flag = 'Y'
AND rownum = 1), 'N')
WHERE bh.bid_number = p_bid_number;
update pon_bid_headers
set rel12_draft_flag = 'Y'
where bid_number = p_bid_number;
SELECT draft_locked, draft_locked_by, draft_locked_by_contact_id
INTO l_draft_locked, l_tpid, l_tpcid
FROM pon_bid_headers
WHERE bid_number = p_bid_number;
UPDATE pon_bid_headers
SET draft_locked = 'Y',
draft_locked_by = p_tpid,
draft_locked_by_contact_id = p_tpcid,
draft_locked_date = sysdate
WHERE bid_number = p_bid_number;
SELECT ah.auction_header_id_orig_amend
INTO l_orig_amend_id
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auc_header_id;
SELECT decode(count(rownum), 0, 'N', 'Y')
INTO l_amend_not_acked
FROM pon_auction_headers_all ah, pon_acknowledgements ac
WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
AND ah.auction_status IN ('AMENDED', 'ACTIVE')
-- ignore the original amendment
AND ah.amendment_number > 0
AND ac.auction_header_id (+) = ah.auction_header_id
AND ac.trading_partner_id (+) = p_tpid
AND ac.trading_partner_contact_id (+)= p_tpcid
AND ac.acknowledgement_response IS null;
SELECT vendor_site_id id, vendor_site_code code
FROM pon_bidding_parties
WHERE auction_header_id = p_auc_header_id
AND trading_partner_id = p_tpid;
-- select the various values
SELECT sysdate,
nvl(ah.view_by_date, ah.open_bidding_date),
ah.open_bidding_date,
ah.close_bidding_date,
ah.auction_status,
nvl(ah.award_status, 'NO'),
ah.bid_list_type,
ah.bid_frequency_code,
ah.auction_header_id_orig_amend,
nvl(ah.is_paused, 'N')
INTO l_current_date,
l_view_by_date,
l_open_date,
l_close_date,
l_auction_status,
l_award_status,
l_bid_list_type,
l_bid_freq_code,
l_orig_amend_id,
l_auction_paused
FROM pon_auction_headers_all ah
WHERE auction_header_id = p_auc_header_id;
IF (l_auction_status = 'DELETED') THEN
x_return_status := 1;
x_return_code := 'AUCTION_DELETED';
SELECT bid_number
INTO l_bid_number
FROM pon_bid_headers bh
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
--AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT'
AND nvl(bh.surrog_bid_flag,'N')='Y'
AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
SELECT bid_number
INTO l_bid_number
FROM pon_bid_headers bh
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
--AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT'
AND nvl(bh.surrog_bid_flag,'N')='N'
AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
SELECT nvl(pov.end_date_active, sysdate)
INTO l_supp_end_date
FROM pon_bid_headers bh, po_vendors pov
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
--AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT'
AND nvl(bh.surrog_bid_flag,'N')='Y'
AND pov.vendor_id = bh.vendor_id
AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
SELECT nvl(pov.end_date_active, sysdate)
INTO l_supp_end_date
FROM pon_bid_headers bh, po_vendors pov
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
--AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT'
AND nvl(bh.surrog_bid_flag,'N')='N'
AND pov.vendor_id = bh.vendor_id
AND nvl(bh.evaluation_flag, 'N') = l_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
SELECT sysdate
INTO l_supp_end_date
FROM pon_bid_headers bh, pos_supplier_registrations psr
WHERE bh.auction_header_id = p_auc_header_id
AND bh.trading_partner_id = p_tpid
AND psr.supplier_reg_id = bh.trading_partner_id
AND psr.registration_status in ('RIF_SUPPLIER', 'PENDING_APPROVAL') -- Bug 9037236
AND ((l_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(l_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND bh.bid_status = 'DRAFT'
AND bh.vendor_id = -1
AND bh.vendor_site_id = -1
AND nvl(bh.evaluation_flag, 'N') = l_eval_flag
AND ROWNUM = 1; -- Multiple Active Offers : Multiple draft offers may exist, just get the first draft offer.
SELECT decode(count(auction_header_id), 0, 'N', 'Y')
INTO l_invited_flag
FROM pon_bidding_parties
WHERE auction_header_id = p_auc_header_id
-- AND trading_partner_id = p_tpid; -- Modified for ER: Supplier Management: Supplier Evaluation
SELECT decode(count(auction_header_id), 0, 'N', 'Y')
INTO l_has_bid_flag
FROM pon_bid_headers
WHERE auction_header_id = p_auc_header_id
AND trading_partner_id = p_tpid
AND vendor_site_id = p_vensid
AND bid_status = 'ACTIVE'
AND nvl(evaluation_flag, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
SELECT decode(count(auction_header_id), 0, 'N', 'Y')
INTO l_has_draft_flag
FROM pon_bid_headers
WHERE auction_header_id = p_auc_header_id
AND trading_partner_id = p_tpid
AND trading_partner_contact_id <> p_tpcid
AND vendor_site_id = p_vensid
AND bid_status = 'DRAFT'
AND nvl(evaluation_flag, 'N') = 'N' -- Added for ER: Supplier Management: Supplier Evaluation
/*Offer Enhancements : If buyer, check only surrogate bids.
If supplier, check only supplier bids */
AND ( (p_buyer_user = 'Y' AND Nvl(surrog_bid_flag,'N') = 'Y')
OR (p_buyer_user = 'N' AND Nvl(surrog_bid_flag,'N') = 'N'));
SELECT decode(count(bh.auction_header_id), 0, 'N', 'Y')
INTO l_has_surrog_flag
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE ah.auction_header_id_orig_amend = l_orig_amend_id
AND bh.auction_header_id = ah.auction_header_id
AND bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id = p_tpcid
AND bh.vendor_site_id = p_vensid
AND bh.bid_status='ACTIVE'
AND nvl(bh.surrog_bid_flag, 'N') = decode(p_buyer_user, 'Y', 'N', 'Y')
AND nvl(bh.evaluation_flag, 'N') = 'N'; -- Added for ER: Supplier Management: Supplier Evaluation
-- select bids on all amendments/previous round by the current user for the current site
CURSOR current_users_bids_byr IS
SELECT bh.bid_number,
bh.auction_header_id,
decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
'RESUBMISSION', 3, 'ARCHIVED' , '4' ,'DISQUALIFIED', 5) status_order,
decode(bh.bid_status, 'ACTIVE',
decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
decode(ah.auction_header_id, p_auc_header_id, 'N',
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag,
decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_wthd,
bh.withdraw_reason
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE
-- look at all amendments on the current round
(ah.auction_header_id_orig_amend = l_orig_amend_id
-- look at all amendments on the previous round
OR ah.auction_header_id_orig_amend = l_prev_round_id)
AND bh.auction_header_id = ah.auction_header_id
AND nvl(bh.surrog_bid_flag,'N')='Y'
AND bh.trading_partner_id = p_tpid
-- AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND nvl(bh.vendor_site_id, -1) = p_vensid
-- we ignore DRAFT bids on previous rounds
AND ((bh.bid_status = 'DRAFT'
AND ah.auction_header_id_orig_amend = l_orig_amend_id)
OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
AND nvl(bh.evaluation_flag, 'N') = p_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
ORDER BY nvl(ah.auction_round_number, 1) DESC,
ah.amendment_number DESC, status_order ASC, bh.publish_date DESC;
SELECT bh.bid_number,
bh.auction_header_id,
decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
'RESUBMISSION', 3,'ARCHIVED' , '4', 'DISQUALIFIED', 5) status_order,
decode(bh.bid_status, 'ACTIVE',
decode(bh.auction_header_id, p_auc_header_id, 'Y', 'N'), 'N') rebid_flag,
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'N', 'Y') prev_round_def,
decode(ah.auction_header_id, p_auc_header_id, 'N',
decode(ah.auction_header_id_orig_amend, l_orig_amend_id, 'Y', 'N')) prev_amend_def,
decode(bh.bid_status, 'DISQUALIFIED', 'Y', 'N') prev_bid_disq,
nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag,
decode(bh.bid_status,'ARCHIVED',decode(bh.withdraw_reason,null,'N','Y'),'N') prev_bid_wthd,
bh.withdraw_reason
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE
-- look at all amendments on the current round
(ah.auction_header_id_orig_amend = l_orig_amend_id
-- look at all amendments on the previous round
OR ah.auction_header_id_orig_amend = l_prev_round_id)
AND bh.auction_header_id = ah.auction_header_id
AND nvl(bh.surrog_bid_flag,'N')='N'
AND bh.trading_partner_id = p_tpid
-- AND bh.trading_partner_contact_id = p_tpcid -- Modified for ER: Supplier Management: Supplier Evaluation
AND ((p_eval_flag = 'N' AND bh.trading_partner_contact_id = p_tpcid) OR
(p_eval_flag = 'Y' AND bh.evaluator_id = p_evaluator_id))
AND nvl(bh.vendor_site_id, -1) = p_vensid
-- we ignore DRAFT bids on previous rounds
AND ((bh.bid_status = 'DRAFT'
AND ah.auction_header_id_orig_amend = l_orig_amend_id)
OR bh.bid_status IN ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
OR (bh.bid_status = 'ARCHIVED' AND bh.withdraw_reason is not null))
AND nvl(bh.evaluation_flag, 'N') = p_eval_flag -- Added for ER: Supplier Management: Supplier Evaluation
ORDER BY nvl(ah.auction_round_number, 1) DESC,
ah.amendment_number DESC, status_order ASC, bh.publish_date DESC;
-- select bids on the current amendment/previous rounds by the current user for the current site
CURSOR other_users_bids IS
SELECT 1 return_status,
decode(bh.auction_header_id, p_auc_header_id,
decode(ah.bid_frequency_code, 'SINGLE_BID_ONLY',
decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT_SBB',
'ACTIVE', 'SINGLE_BEST_BID'),
decode(bh.bid_status, 'DRAFT', 'OTHER_USERS_DRAFT',
'ACTIVE', 'OTHER_USERS_ACTIVE')),
'OTHER_USERS_PREV_ROUND') return_code
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE
-- look at the current amendment, remove DRAFT status as part of Offer Enhancement ER
-- Offer Enhancement : We need to consider draft bids
(ah.auction_header_id = p_auc_header_id
AND bh.bid_status IN ('DRAFT','ACTIVE')
-- look at the previous round
OR ah.auction_header_id_orig_amend = l_prev_round_id
AND bh.bid_status = 'ACTIVE')
AND bh.auction_header_id = ah.auction_header_id
AND bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id <> p_tpcid
AND nvl(bh.vendor_site_id, -1) = p_vensid
AND nvl(bh.evaluation_flag, 'N') = 'N' -- Added for ER: Supplier Management: Supplier Evaluation
/* Offer Enhancements ER : If buyer user, consider only surrogate bids.
Else consider only supplier bids.
*/
AND ( (p_buyer_user = 'Y' AND Nvl(bh.surrog_bid_flag,'N') = 'Y')
OR (p_buyer_user = 'N' AND Nvl(bh.surrog_bid_flag,'N') = 'N'))
ORDER BY nvl(ah.auction_round_number, 1) DESC, ah.amendment_number DESC,
decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2) ASC, bh.publish_date DESC;
SELECT ah.auction_header_id_orig_amend, ah2.auction_header_id_orig_amend,
nvl2(ah.contract_id, 'Y', 'N'), ah.org_id
INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply,l_org_id
FROM pon_auction_headers_all ah, pon_auction_headers_all ah2
WHERE ah.auction_header_id = p_auc_header_id
and ah2.auction_header_id (+) = ah.auction_header_id_prev_round;
-- If it is a pre-release 12 draft, need to insert missing lines
-- NOTE: expand_draft is batched
IF (l_rel12_draft <> 'Y') THEN
expand_draft(x_prev_bid_number, x_rebid_flag);
UPDATE pon_bid_headers
SET bid_status = 'ARCHIVED_DRAFT',
last_update_date = sysdate
WHERE bid_number = x_prev_bid_number;
-- If pre-release 12, call handle_proxy to update price, limit_price
-- set has_bid_flag
IF (l_rel12_draft <> 'Y') THEN
handle_proxy
(p_auc_header_id,
x_prev_bid_number,
p_tpid,
p_tpcid,
p_vensid,
---- Supplier Management: Supplier Evaluation ----
p_evaluator_id,
p_eval_flag,
--------------------------------------------------
l_ignored_return,
x_rebid_flag);
-- Update Evaluation flow
UPDATE pon_bid_headers
SET bid_status = 'DRAFT',
last_update_date = sysdate
WHERE bid_number = x_prev_bid_number;
SELECT Count(*)
INTO l_count_org
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id ;
SELECT NVL(contract_status,'ACTIVE')
INTO l_org_contract_status
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id
AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
SELECT Count(*)
INTO l_count_org
FROM pon_contracts
WHERE NVL(org_id,-1) = l_org_id ;
SELECT enabled_flag
INTO l_enabled_flag
FROM pon_contracts pc
WHERE NVL(org_id,-1) = l_org_id
AND version_num = (select max(version_num) from pon_contracts where NVL(org_id,-1) = l_org_id);
-- Select out the header values from the previous draft
SELECT bh.trading_partner_id,
bh.trading_partner_contact_id,
bh.trading_partner_name,
bh.trading_partner_contact_name,
bh.created_by,
bh.vendor_id,
bh.vendor_site_id,
bh.vendor_site_code,
bh.surrog_bid_created_tp_id,
bh.surrog_bid_created_contact_id,
bh.surrog_bid_flag,
---- Supplier Management: Supplier Evaluation ----
bh.evaluator_id,
bh.evaluation_flag
--------------------------------------------------
INTO l_tpid,
l_tpcid,
l_tpname,
l_tpcname,
l_userid,
l_venid,
l_vensid,
l_venscode,
l_auctpid,
l_auctpcid,
l_buyer_user,
---- Supplier Management: Supplier Evaluation ----
l_evaluator_id,
l_eval_flag
--------------------------------------------------
FROM pon_bid_headers bh
WHERE bh.bid_number = p_source_bid;
-- Update the status of the previous bid
-- NOTE: if this procedure is used for cases other than spreadsheet upload
-- on a DRAFT after an amendment, the status will need to be set correctly
UPDATE pon_bid_headers bh
SET bh.bid_status = decode('DRAFT', 'ARCHIVED_DRAFT', bh.bid_status)
WHERE bh.bid_number = p_source_bid;
SELECT nvl(bh.rel12_draft_flag, 'N') rel12_draft_flag
INTO l_rel12_draft
FROM pon_bid_headers bh
WHERE bh.bid_number = p_draft_number;
SELECT SURROG_BID_FLAG INTO L_SURROGATE_BID_FLAG
FROM
PON_BID_HEADERS BH
WHERE
bh.auction_header_id = p_auc_header_id
AND BH.BID_NUMBER = x_bid_number
AND bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id = p_tpcid
AND bh.bid_status='DRAFT'
AND nvl(bh.evaluation_flag, 'N') = 'N';
DELETE FROM pon_mng_eval_bid_sections
WHERE bid_number = x_bid_number
AND status_code = 'A';
select bid_number
from pon_bid_headers
where auction_header_id = p_auc_header_id
and trading_partner_id = p_tpid
and trading_partner_contact_id = p_tpcid
and nvl(vendor_site_id, -1) = nvl(p_vensid, -1)
and bid_status in ('DRAFT', 'ACTIVE', 'DISQUALIFIED')
order by decode(bid_status, 'DRAFT', 3,
'ACTIVE', 2,
'DISQUALIFIED', 1) desc, publish_date desc;
select bh.bid_number
from
pon_bid_headers bh,
pon_auction_headers_all ah
where
bh.auction_header_id = ah.auction_header_id
and ah.auction_header_id_orig_amend = p_auc_header_id_orig_amend
and bh.trading_partner_id = p_tpid
and bh.trading_partner_contact_id = p_tpcid
and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1)
and bh.bid_status in ('DRAFT', 'RESUBMISSION', 'DISQUALIFIED')
order by ah.amendment_number desc,
decode(bh.bid_status, 'DRAFT', 3,
'RESUBMISSION' , 2,
'DISQUALIFIED', 1) desc,
bh.publish_date desc;
select max(bid_number) prev_round_bid
into x_prev_bid_number
from pon_bid_headers bh,
pon_auction_headers_all ah,
pon_auction_headers_all ah2
where bh.auction_header_id = ah.auction_header_id
and ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
and ah2.auction_header_id = p_prev_round_auc_header_id
and bh.trading_partner_id = p_tpid
and bh.trading_partner_contact_id = p_tpcid
and bh.bid_status in ('ACTIVE', 'RESUBMISSION', 'DISQUALIFIED')
and nvl(bh.vendor_site_id, -1) = nvl(p_vensid, -1);
SELECT 'Y'
INTO x_can_create_payments
FROM dual
WHERE EXISTS (SELECT 1
FROM PON_AUCTION_ITEM_PRICES_ALL pai,
PON_BID_ITEM_PRICES pbi,
PON_AUCTION_HEADERS_ALL pah
WHERE pai.auction_header_id = p_auction_header_id
AND pai.group_type NOT IN ('GROUP','LOT_LINE')
AND pbi.auction_header_id = pai.auction_header_id
AND pbi.line_number = pai.line_number
AND pbi.bid_number = p_bid_number
AND pah.auction_header_id = pai.auction_header_id
AND pah.progress_payment_type <> 'NONE'
AND pah.contract_type = 'STANDARD');
SELECT contract_type,
supplier_view_type
INTO l_contract_type,
l_supplier_view_type
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT (p_unit_price * ppsf.percentage) +
ppsf.unit_price +
ppsf.fixed_amount/decode(l_is_spo_transformed,
'Y', nvl(p_quantity, 1),
nvl(aip.quantity, 1)
)
INTO l_auc_pf_unit_price
FROM pon_pf_supplier_formula ppsf,
pon_auction_item_prices_all aip,
pon_bid_headers pbh
WHERE ppsf.auction_header_id = p_auction_header_id
AND ppsf.line_number = p_line_number
AND ppsf.trading_partner_id = pbh.trading_partner_id
AND ppsf.vendor_site_id = pbh.vendor_site_id
AND pbh.bid_number = p_prev_auc_active_bid_number
AND aip.auction_header_id = ppsf.auction_header_id
AND aip.line_number = ppsf.line_number;
SELECT nvl(sum(decode(spf.pricing_basis,
'PER_UNIT', spf.auction_currency_value,
'PERCENTAGE', spf.auction_currency_value/100 * p_unit_price,
(spf.auction_currency_value / decode(l_is_spo_transformed,
'Y', nvl(p_quantity, 1),
nvl(aip.quantity, 1)
)
)
)
)
,0)
INTO l_bid_line_pf_unit_price
FROM pon_bid_price_elements spf,
pon_auction_item_prices_all aip
WHERE spf.bid_number = p_prev_auc_active_bid_number
AND spf.line_number = p_line_number
AND spf.sequence_number <> -10
AND spf.pf_type = 'SUPPLIER'
AND aip.auction_header_id = spf.auction_header_id
AND aip.line_number = spf.line_number;
is used to update pon_bid_item_prices table's data. */
PROCEDURE update_bid_line_to_po_line(p_po_line_id IN po_lines_all.po_line_id%TYPE,
p_po_contract_type IN po_lines_all.contract_type%TYPE,
p_po_unit_price IN po_lines_all.unit_price%TYPE,
p_po_clm_idc_type IN po_lines_all.clm_idc_type%TYPE,
p_po_cost_constraint IN po_lines_all.cost_constraint%TYPE,
p_po_quantity IN po_lines_all.cost_constraint%TYPE,
p_po_type IN po_lines_all.order_type_lookup_code%TYPE,
p_bid_number IN NUMBER,
p_auction_header_id IN NUMBER,
p_bid_line_number IN NUMBER) IS
l_bid_curr_unit_price pon_bid_item_prices.bid_currency_unit_price%TYPE;
SELECT bh.number_price_decimals,
cu.precision,
bh.rate
INTO l_price_prec,
l_curr_prec,
l_rate
FROM pon_bid_headers bh,
fnd_currencies cu
WHERE bh.bid_number = p_bid_number
AND cu.currency_code = bh.bid_currency_code;
UPDATE pon_bid_item_prices
SET
idv_line_id = p_po_line_id,
clm_contract_type = p_po_contract_type,
price = Decode(p_po_type,'QUANTITY',p_po_unit_price,p_po_quantity),
unit_price = Decode(p_po_type,'QUANTITY',p_po_unit_price,p_po_quantity),
Bid_currency_unit_price = l_bid_curr_unit_price,
bid_currency_price = l_bid_curr_unit_price,
clm_idc_type = p_po_clm_idc_type,
clm_cost_constraint = p_po_cost_constraint,
quantity = NVL((SELECT quantity FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = p_bid_line_number),quantity)
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND line_number = p_bid_line_number;
END update_bid_line_to_po_line;
/* Fetch cost constraint and pass it to update_bid_line_to_po_line procedure.
This variable value is used to update pon_bid_item_prices table's data.
Update IS_CHANGED_LINE_FLAG to 'Y' whenever IDV reference lines matches with
Offer lines. */
PROCEDURE default_idv_line_id (p_po_header_id IN NUMBER,
p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER)
IS
TYPE bid_info_record_type IS RECORD
(line_number pon_bid_item_prices.line_number%TYPE,
description pon_bid_item_prices.item_description%TYPE,
category_id pon_bid_item_prices.category_id%TYPE,
line_type_id pon_auction_item_prices_all.line_type_id%TYPE);
select po_line_id,item_description,category_id,line_type_id,
contract_type,unit_price,clm_idc_type,cost_constraint,
quantity, order_type_lookup_code
bulk collect into po_line_info_table
from po_lines_all
where po_header_id = p_po_header_id;
select bid.line_number,bid.item_description,
bid.category_id,auction.line_type_id
bulk collect into bid_info_table
from
pon_bid_item_prices bid,
pon_auction_item_prices_all auction
where bid.bid_number = p_bid_number
and bid.auction_header_id = auction.auction_header_id;
update_bid_line_to_po_line(po_line_info_table(j).po_line_id,
po_line_info_table(j).contract_type,
po_line_info_table(j).unit_price,
po_line_info_table(j).clm_idc_type,
po_line_info_table(j).cost_constraint,
po_line_info_table(j).quantity,
po_line_info_table(j).order_type_lookup_code,
p_bid_number,
p_auction_header_id,
bid_info_table(i).line_number);
UPDATE pon_bid_item_prices
SET IS_CHANGED_LINE_FLAG = 'Y',
HAS_USER_BID_FLAG = 'Y'
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND line_number = bid_info_table(i).line_number
-- ELINs project
AND ((Nvl(USER_MARKED_FLAG,'N') = 'N')
OR (Nvl(USER_MARKED_FLAG,'N') = 'Y' AND Nvl(HAS_USER_BID_FLAG, 'N') = 'Y')) ;
/* Added cost constraint to update_bid_line_to_po_line procedure variables.
This variable value is used to update pon_bid_item_prices table's data. */
/* CLM : This is a wrapper procedure that calls uda copy procedure and
* updates some bid columns for po line.
*/
PROCEDURE COPY_PO_LINE_TO_BID_LINE(p_bid_header_id IN NUMBER,
p_bid_line_number IN NUMBER,
p_po_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_contract_type po_lines_all.contract_type%TYPE;
select auction_header_id
into l_auction_header_id
from pon_bid_headers
where bid_number = p_bid_header_id;
select contract_type,unit_price,clm_idc_type,cost_constraint,quantity,order_type_lookup_code
into l_contract_type,l_unit_price,l_clm_idc_type,l_cost_constraint,l_quantity,l_order_type_lookup_code
from po_lines_all
where po_line_id = p_po_line_id;
update_bid_line_to_po_line(p_po_line_id,
l_contract_type,
l_unit_price,
l_clm_idc_type,
l_cost_constraint,
l_quantity,
l_order_type_lookup_code,
p_bid_header_id,
l_auction_header_id,
p_bid_line_number);
/* 1) Update IS_CHANGED_LINE_FLAG, idv_line_id, unit_price, price,
Bid_currency_unit_price,bid_currency_price, clm_contract_type,
clm_idc_type, clm_cost_constraint values of pon_bid_item_prices.
2) Copy UDA data for each line or specific line by calling
PON_COPY_UDAS_GRP.COPY_BID_LINE_UDA procedure.
*/
PROCEDURE default_line_from_sol (p_auction_header_id IN NUMBER,
p_auction_line_number IN NUMBER DEFAULT NULL,
p_bid_number IN NUMBER)
IS
l_auction_template_id NUMBER;
UPDATE pon_bid_item_prices
SET IS_CHANGED_LINE_FLAG = 'N',
HAS_USER_BID_FLAG = 'N'
WHERE auction_header_id = p_auction_header_id AND
bid_number = p_bid_number AND
idv_line_id IS NOT NULL AND
--ELINs project
NOT (Nvl(user_marked_flag,'N') = 'Y' AND Nvl(has_user_bid_flag,'N') = 'Y');
UPDATE pon_bid_item_prices
SET idv_line_id = null,
unit_price = null,
price = null,
quantity=null,
Bid_currency_unit_price = null,
bid_currency_price = null
WHERE auction_header_id = p_auction_header_id AND
bid_number = p_bid_number;
select clm_contract_type, clm_idc_type, clm_cost_constraint, line_number
bulk collect into pon_line_info_table
from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id;
UPDATE pon_bid_item_prices
SET clm_contract_type = pon_line_info_table(i).contract_type,
clm_idc_type = pon_line_info_table(i).clm_idc_type,
clm_cost_constraint = pon_line_info_table(i).cost_constraint
WHERE auction_header_id = p_auction_header_id AND
bid_number = p_bid_number and
auction_line_number = pon_line_info_table(i).line_number;
SELECT uda_template_id INTO l_auction_template_id
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id AND
line_number = pon_line_info_table(i).line_number;
select doctype_id
into l_doctype_id
from pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
select clm_contract_type, clm_idc_type, clm_cost_constraint, uda_template_id
into l_contract_type, l_clm_idc_type, l_cost_constraint, l_auction_template_id
from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id AND
line_number = p_auction_line_number;
UPDATE pon_bid_item_prices
SET idv_line_id = null,
unit_price = null,
price = null,
Bid_currency_unit_price = null,
bid_currency_price = null,
clm_contract_type = l_contract_type,
clm_idc_type = l_clm_idc_type,
clm_cost_constraint = l_cost_constraint
WHERE auction_header_id = p_auction_header_id AND
bid_number = p_bid_number AND
auction_line_number = p_auction_line_number ;
select doctype_id
into l_doctype_id
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT
NVL (number_of_lines, 0)
--NVL (max_internal_line_num, 0),
--NVL (max_document_line_num, 0)
INTO
l_number_of_lines
--x_max_internal_line_num,
--x_max_document_line_num
FROM
pon_auction_headers_all
WHERE
auction_header_id = p_auction_header_id;
SELECT
GREATEST (NVL(MAX(bid_items.line_number),0), NVL(MAX(items.line_number),0))
--GREATEST (x_max_document_line_num, NVL(MAX(DECODE (items.group_type, 'LOT_LINE', 0, 'GROUP_LINE', 0, items.sub_line_sequence_number)),0)),
--NVL (MAX(items.disp_line_number), 0)
INTO
x_max_internal_line_num
--x_max_document_line_num,
--x_max_disp_line_num
FROM
pon_auction_item_prices_all items,
pon_bid_item_prices bid_items
WHERE
items.auction_header_id = p_auction_header_id
and bid_items.bid_number = p_bid_number
and items.auction_header_id = bid_items.auction_header_id;
UPDATE pon_bid_item_prices
SET idv_line_id = NULL
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND auction_line_number = -1;
SELECT vendor_site_id
INTO site_id
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_tp_id
AND ROWNUM = 1;
SELECT Decode(bid_frequency_code, 'SINGLE_BID_ONLY', 'Y', 'N' ), Decode (BID_LIST_TYPE ,'PUBLIC_BID_LIST', 'Y', 'N')
INTO is_single_bid_freq, is_public
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT vendor_site_id
INTO site_id
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_tp_id
AND vendor_site_id NOT IN
(SELECT vendor_site_id
FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_tp_id
AND bid_status = 'ACTIVE')
AND ROWNUM = 1;
SELECT vendor_site_id
INTO site_id
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id
AND trading_partner_id = p_tp_id
AND ROWNUM = 1;
SELECT Count(*) INTO l_count
FROM pon_supplier_activities
WHERE auction_header_id_orig_amend = p_auction_header_id_orig_amend
AND last_activity_code = 'ACCEPT_TERMSCOND'
AND trading_partner_id = p_trading_partner_id
AND trading_partner_contact_id = p_trading_partner_contact_id;