[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,
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.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,
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
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 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,
p_rebid_flag IN VARCHAR2,
p_prev_bid_disq 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
--------------------------------------------------------------
)
(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
decode(p_buyer_user, 'Y', p_auctpcid, p_tpcid), -- DRAFT_LOCKED_BY_CONTACT_ID
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', 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)
-----------------------------------------------------------------------------
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 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
)
(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
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
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
al.bid_start_price,
decode(al.modified_date-old_al.modified_date,
0, bl.has_quantity_tiers, al.has_quantity_tiers) -- HAS_quantity_tiers
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;
-- 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 = nvl(al.parent_line_number, al.line_number)
AND le.trading_partner_id = p_tpid
AND le.vendor_site_id = p_vensid);
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,
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 (+));
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,
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
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,
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
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 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,
p_rebid_flag,
p_prev_bid_disq,
x_bid_number);
insert_into_header_attributes
(p_auc_header_id,
x_bid_number,
p_source_bid_num,
p_userid,
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 ah.max_internal_line_num ,ah.price_tiers_indicator
INTO l_max_line_number,l_price_tiers_indicator
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 bid_number
into l_other_draft_bid_number
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);
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
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT';
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
AND bh.vendor_site_id = p_vensid
AND bh.bid_status = 'DRAFT'
AND pov.vendor_id = bh.vendor_id;
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;
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';
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';
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 nvl(bh.surrog_bid_flag, 'N') = decode(p_buyer_user, 'Y', 'N', 'Y');
-- select bids on all amendments/previous round by the current user for the current site
CURSOR current_users_bids IS
SELECT bh.bid_number,
bh.auction_header_id,
decode(bh.bid_status, 'DRAFT', 1, 'ACTIVE', 2,
'RESUBMISSION', 3, 'DISQUALIFIED', 4) 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
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 bh.trading_partner_id = p_tpid
AND bh.trading_partner_contact_id = p_tpcid
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'))
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
(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
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')
INTO l_orig_amend_id, l_prev_round_id, l_terms_cond_apply
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,
l_ignored_return,
x_rebid_flag);
-- 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
INTO l_tpid,
l_tpcid,
l_tpname,
l_tpcname,
l_userid,
l_venid,
l_vensid,
l_venscode,
l_auctpid,
l_auctpcid,
l_buyer_user
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 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;