[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_line(p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_line_number IN NUMBER)
IS
l_module_name VARCHAR2 (30);
l_module_name := 'DELETE_LINE';
SELECT doctype_id
INTO l_doctype_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
DELETE FROM
pon_bid_item_references
WHERE
bid_number = p_bid_number AND
line_number = p_line_number;
DELETE FROM
pon_bid_item_prices_ext_b
WHERE
bid_number = p_bid_number AND
line_number = p_line_number;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
x_entity_name => 'PON_BID_ITEM_PRICES',
x_pk1_value => p_auction_header_id,
x_pk2_value => p_bid_number,
x_pk3_value => p_line_number);
message => 'Deleted the line attachments');
DELETE FROM
pon_bid_item_prices
WHERE
bid_number = p_bid_number AND
line_number = p_line_number;
message => 'Deleted the line.');
END delete_line;
PROCEDURE DELETE_SINGLE_LINE(p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_line_number IN NUMBER,
x_result OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2 (30);
SELECT
line_number
FROM pon_bid_item_prices
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND group_line_id = p_line_number;
l_module_name := 'DELETE_SINGLE_LINE';
SELECT
line_number
INTO
l_line_number
FROM
pon_bid_item_prices
WHERE
auction_header_id = p_auction_header_id and
bid_number = p_bid_number and
line_number = p_line_number;
delete_line(p_auction_header_id,p_bid_number,sl_line.line_number);
/* Delete Parent Line */
delete_line(p_auction_header_id,p_bid_number,p_line_number);
END DELETE_SINGLE_LINE;
PROCEDURE DELETE_ALL_LINES(p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
x_result OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2 (30);
SELECT line_number
FROM pon_bid_item_prices
WHERE auction_header_id = p_auction_header_id
AND bid_number = p_bid_number
AND auction_line_number = -1;
l_module_name := 'DELETE_LINE';
SELECT doctype_id
INTO l_doctype_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
DELETE FROM
pon_bid_item_references
WHERE
bid_number = p_bid_number;
DELETE FROM
pon_bid_item_prices_ext_b ext
WHERE
ext.bid_number = p_bid_number
AND ext.line_number IN
(SELECT bid_item.line_number FROM pon_bid_item_prices bid_item
WHERE bid_item.bid_number = ext.bid_number AND bid_item.auction_line_number = -1);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
x_entity_name => 'PON_BID_ITEM_PRICES',
x_pk1_value => p_auction_header_id,
x_pk2_value => p_bid_number,
x_pk3_value => line.line_number);
message => 'Deleted the line attachments');
DELETE FROM
pon_bid_item_prices
WHERE
bid_number = p_bid_number AND
auction_line_number = -1;
message => 'Deleted all the lines.');
END DELETE_ALL_LINES;
SELECT 'Y'
INTO l_has_ref
FROM dual
WHERE EXISTS
(SELECT 1 FROM pon_bid_item_references WHERE bid_number = p_bid_number AND line_number = p_line_number);
SELECT 'Y'
INTO l_has_unsol_lines
FROM dual
WHERE EXISTS
(SELECT 1 FROM pon_bid_item_prices WHERE auction_header_id = p_auc_header_id AND auction_line_number = -1);
SELECT 'Y'
INTO l_has_unsol_lines
FROM dual
WHERE EXISTS
(SELECT 1 FROM pon_bid_item_prices WHERE bid_number = p_bid_number AND auction_line_number = -1);
SELECT Nvl(ALLOW_UNSOL_OFFER_LINES,'N')
INTO l_unsol_lines_allowed
FROM pon_auction_headers_all
WHERE auction_header_id = p_auc_header_id;
PROCEDURE INSERT_INTO_BID_ITEMS(p_auction_id IN NUMBER,
p_source_bid_num IN NUMBER,
p_bid_number IN NUMBER,
p_uda_template_id IN NUMBER,
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)
IS
l_auctpid pon_auction_headers_all.trading_partner_id%TYPE;
SELECT ah.trading_partner_id
INTO l_auctpid
FROM pon_auction_headers_all ah
WHERE ah.auction_header_id = p_auction_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.
*********************************************************/
LINE_TYPE_ID,
ORDER_TYPE_LOOKUP_CODE,
PURCHASE_BASIS,
-- Event Based Delivery Project
CLM_DELIVERY_EVENT_CODE,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
CLM_PROMISE_POP_DURATION,
CLM_PROMISE_POP_DURATION_UOM
)
(SELECT
bl.auction_header_id, -- AUCTION_HEADER_ID
-1, -- AUCTION_LINE_NUMBER
p_bid_number, -- BID_NUMBER
bl.line_number, -- LINE_NUMBER
bl.item_description, -- ITEM_DESCRIPTION
bl.category_id, -- CATEGORY_ID
bl.category_name, -- CATEGORY_NAME
bl.uom, -- UOM
bl.quantity, -- QUANTITY
bl.price, -- PRICE
null, -- MINIMUM_BID_PRICE
bl.promised_date, -- PROMISED_DATE
bl.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
null, -- AUCTION_CREATION_DATE
bl.ship_to_location_id, -- SHIP_TO_LOCATION_ID
bl.publish_date, -- PUBLISH_DATE
bl.proxy_bid_limit_price, -- PROXY_BID_LIMIT_PRICE
bl.proxy_bid_limit_price_date, -- PROXY_BID_LIMIT_PRICE_DATE
bl.bid_currency_price, -- BID_CURRENCY_PRICE
bl.bid_currency_limit_price, -- BID_CURRENCY_LIMIT_PRICE
'N', -- PROXY_BID_FLAG
bl.first_bid_price, -- FIRST_BID_PRICE
bl.unit_of_measure, -- UNIT_OF_MEASURE
bl.has_attributes_flag, -- HAS_ATTRIBUTES_FLAG
bl.freight_terms_code, -- FREIGHT_TERMS_CODE
'N', -- TBD_PRICING_FLAG
l_auctpid, -- AUC_TRADING_PARTNER_ID
p_tpid, -- BID_TRADING_PARTNER_ID
bl.total_weighted_score, -- TOTAL_WEIGHTED_SCORE
bl.rank, -- RANK
bl.po_min_rel_amount, -- PO_MIN_REL_AMOUNT
bl.po_bid_min_rel_amount, -- PO_BID_MIN_REL_AMOUNT
bl.price_break_type, -- PRICE_BREAK_TYPE
bl.has_shipments_flag, -- HAS_SHIPMENTS_FLAG
-- Rebid: set changed_line to N
--CLM QA Bug : 9835426 : NC,NSP lines, assume as always modified
Decode(Nvl(bl.clm_cost_constraint,'X'),'NC','Y',
decode(nvl(bl.clm_cost_constraint,'X'),'NSP','Y','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
bl.has_price_differentials_flag,-- HAS_PRICE_DIFFERENTIALS_FLAG
bl.price_diff_shipment_number, -- PRICE_DIFF_SHIPMENT_NUMBER *
bl.bid_currency_trans_price, -- BID_CURRENCY_TRANS_PRICE
bl.unit_price, -- UNIT_PRICE
bl.bid_currency_unit_price, -- BID_CURRENCY_UNIT_PRICE
bl.group_amount, -- GROUP_AMOUNT
bl.has_bid_payments_flag, --HAS_BID_PAYMENTS_FLAG
bl.advance_amount, --ADVANCE_AMOUNT
bl.bid_curr_advance_amount, --BID_CURR_ADVANCE_AMOUNT
bl.recoupment_rate_percent, --RECOUPMENT_RATE_PERCENT
bl.progress_pymt_rate_percent, --PROGRESS_PYMT_RATE_PERCENT
bl.retainage_rate_percent, --RETAINAGE_RATE_PERCENT
bl.max_retainage_amount, --MAX_RETAINAGE_AMOUNT
bl.bid_curr_max_retainage_amt, --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 */
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.publish_Date, null), -- OLD_PROMISED_DATE
decode(p_rebid_flag, 'Y', bl.note_to_auction_owner, null), -- OLD_NOTE_TO_AUCTION_OWNER */
bl.price,
bl.bid_currency_unit_price,
bl.bid_currency_price,
bl.bid_currency_limit_price,
bl.po_bid_min_rel_amount,
bl.quantity,
bl.publish_Date,
bl.publish_Date,
bl.note_to_auction_owner,
'Y', -- HAS_BID_FLAG
/*decode(nvl(bl.clm_cost_constraint,'X'),'NC','Y',
decode(nvl(bl.clm_cost_constraint,'X'),'NSP','Y',nvl(bl.has_bid_flag, '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 */
bl.bid_start_price,
bl.has_quantity_tiers, -- HAS_quantity_tiers
/***********************************************************
* CLM - Clin Slin project Changes Start.
*********************************************************/
bl.LINE_NUM_DISPLAY ,
bl.GROUP_LINE_ID,
bl.CLM_INFO_FLAG,
bl.CLM_OPTION_INDICATOR,
bl.CLM_BASE_LINE_NUM,
bl.CLM_OPTION_NUM,
bl.CLM_OPTION_FROM_DATE,
bl.CLM_OPTION_TO_DATE,
bl.CLM_FUNDED_FLAG,
/***********************************************************
* CLM - Clin Slin project Changes End.
*********************************************************/
/***********************************************************
* CLM - Complex Pricing project Changes Start.
*********************************************************/
bl.CLM_COST_CONSTRAINT,
bl.CLM_CONTRACT_TYPE,
bl.CLM_IDC_TYPE,
-- Decode(al.CLM_CONTRACT_TYPE, NULL, NULL, l_uda_template_id)
p_uda_template_id,
/***********************************************************
* CLM - Complex Pricing project Changes End.
*********************************************************/
bl.LINE_TYPE_ID,
bl.ORDER_TYPE_LOOKUP_CODE,
bl.PURCHASE_BASIS,
-- Event Based Delivery Project
bl.CLM_DELIVERY_EVENT_CODE,
bl.CLM_PROMISE_PERIOD,
bl.CLM_PROMISE_PERIOD_UOM,
bl.CLM_PROMISE_POP_DURATION,
bl.CLM_PROMISE_POP_DURATION_UOM
FROM pon_bid_item_prices bl
WHERE bl.bid_number = p_source_bid_num
AND bl.auction_line_number = -1);
END INSERT_INTO_BID_ITEMS;
INSERT INTO pon_bid_item_references
(BID_NUMBER,
LINE_NUMBER ,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
LINK_TYPE,
LINK_DONE_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
(SELECT
p_bid_number,
reference.line_number,
reference.auction_header_id,
reference.auction_line_number,
reference.link_type,
'BOTH',
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id
FROM
pon_bid_item_references reference
WHERE reference.bid_number = p_source_bid_num);
SELECT line_number,uda_template_id
FROM pon_bid_item_prices
WHERE bid_number=p_source_bid_num
AND auction_line_number = -1;
/*insert into vhk_debug_msg values('Calling COPY_BID_LINE_UDA');*/
/*insert into vhk_debug_msg values('line_number :'||line.line_number
||' uda_template_id : '||line.uda_template_id
||' bid_uda_template_id : '||p_bid_template_id
||' copyfrom : '||line.copyfrom
||' x_return_status : '||x_return_status);*/
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.auction_line_number = -1;
x_last_update_login => fnd_global.login_id);
INSERT_INTO_BID_ITEMS(p_auction_id,
p_source_bid_num,
p_bid_number,
p_uda_template_id,
p_tpid,
p_tpcid,
p_userid,
p_vensid);
SELECT 1
INTO l_has_supplier_data
FROM pon_bid_item_references
WHERE
AUCTION_HEADER_ID = p_auction_id
AND LINK_DONE_BY = 'SUPPLIER'
AND ROWNUM = 1;
UPDATE pon_bid_item_references
SET LINK_DONE_BY = 'SUPPLIER'
WHERE AUCTION_HEADER_ID = p_auction_id
AND LINE_NUMBER IN
(SELECT LINE_NUMBER FROM pon_bid_item_prices
WHERE AUCTION_HEADER_ID = p_auction_id
AND AUCTION_LINE_NUMBER = -1)
--Fix for bug 13643475
--Need to mark all the unsolicited lines linked as Supplier
--AND AWARD_STATUS = 'AWARDED')
;
INSERT INTO pon_bid_item_references (BID_NUMBER,
LINE_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
LINK_TYPE,
LINK_DONE_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
(SELECT BID_NUMBER,
LINE_NUMBER,
AUCTION_HEADER_ID,
AUCTION_LINE_NUMBER,
LINK_TYPE,
'BOTH',
sysdate,
CREATED_BY,
sysdate,
l_user_id,
l_login_id
FROM pon_bid_item_references
WHERE AUCTION_HEADER_ID = p_auction_id
AND LINE_NUMBER IN
(SELECT LINE_NUMBER FROM pon_bid_item_prices
WHERE AUCTION_HEADER_ID = p_auction_id
AND AUCTION_LINE_NUMBER = -1
AND AWARD_STATUS = 'AWARDED'));
SELECT 'Y'
INTO l_has_unsol_lines
FROM dual
WHERE EXISTS
(SELECT 1 FROM pon_bid_item_prices
WHERE bid_number = p_bid_number
AND auction_line_number = -1
AND Nvl(CLM_INFO_FLAG, 'N') = 'N'
AND Nvl(clm_cost_constraint, 'X') NOT IN ('NSP', 'NC'));
SELECT 'Y'
INTO l_has_awarded_ref
FROM dual
WHERE EXISTS
(SELECT 1
FROM pon_bid_item_prices pbip,
pon_bid_item_references pbir
WHERE pbip.auction_header_id = p_auction_header_id
AND pbip.auction_line_number = -1
AND Nvl(pbip.award_status,'NO') = 'AWARDED'
AND pbir.auction_header_id = pbip.auction_header_id
AND pbir.line_number = pbip.line_number
AND pbir.auction_line_number = p_line_number
AND pbir.link_done_by = 'BOTH');