[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_single_line (
x_result OUT NOCOPY VARCHAR2, --1
x_error_code OUT NOCOPY VARCHAR2, --2
x_error_message OUT NOCOPY VARCHAR2, --3
p_auction_header_id IN NUMBER, --4
p_line_number IN NUMBER, --5
p_delete_linked_option IN VARCHAR2 DEFAULT 'Y', --6
x_number_of_lines_deleted IN OUT NOCOPY NUMBER --11
) IS
l_module_name VARCHAR2 (30);
SELECT
line_number,
group_type,
line_origination_code,
org_id,
parent_line_number,
sub_line_sequence_number,
clm_base_line_num,
line_num_display,
clm_option_indicator
FROM pon_auction_item_prices_all
WHERE auction_header_id =p_auction_header_id
AND (line_number = p_line_number
OR clm_base_line_num = p_line_number);
SELECT
line_number,
group_type,
line_origination_code,
org_id,
parent_line_number,
sub_line_sequence_number,
clm_option_indicator
FROM pon_auction_item_prices_all
WHERE auction_header_id =p_auction_header_id
AND group_line_id = i_parent_line;
SELECT
line_number,
group_type,
line_origination_code,
org_id,
parent_line_number,
sub_line_sequence_number,
clm_option_indicator
FROM pon_auction_item_prices_all
WHERE auction_header_id =p_auction_header_id
AND clm_base_line_num = i_base_line_num;
SELECT
ROWNUM AS rownumber,
auction_header_id,
line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND clm_base_line_num = i_parent_line;
l_module_name := 'delete_single_line';
IF(p_delete_linked_option <> 'Y') THEN
--If linked option lines are not to be deleted, then unlink them first so that they
-- will not get picked by the op_line cursor for deletion
UPDATE pon_auction_item_prices_all
SET clm_base_line_num = null,
clm_option_num = null
WHERE auction_header_id =p_auction_header_id
AND clm_base_line_num = p_line_number;
IF (p_delete_linked_option = 'Y') THEN
FOR cross_linked_options IN CrossLinkedOptionLines(sl_line.line_number) LOOP
temp :=0;
message => 'Inside cross_linked_options loop: Calling delete_single_line for ' || cross_linked_options.line_number);
pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
p_document_id => p_auction_header_id,
p_document_line_id => cross_linked_options.line_number,
x_return_status=>l_return_status ,
x_msg_count=>l_msg_count,
x_msg_data =>l_msg_data );
pon_negotiation_helper_pvt.delete_single_line(
x_result => x_result,
x_error_code => x_error_code,
x_error_message => x_error_message,
p_auction_header_id => p_auction_header_id,
p_line_number => cross_linked_options.line_number,
p_group_type => cross_linked_options.group_type,
p_origination_code => cross_linked_options.line_origination_code,
p_org_id => cross_linked_options.org_id,
p_parent_line_number => cross_linked_options.parent_line_number,
p_sub_line_sequence_number => cross_linked_options.SUB_LINE_SEQUENCE_NUMBER,
x_number_of_lines_deleted => temp
);
message => 'Deleted cross linked option : ' || cross_linked_options.line_number);
UPDATE pon_auction_item_prices_all
SET clm_base_line_num = null,
clm_option_num = null
WHERE auction_header_id =p_auction_header_id
AND clm_base_line_num = sl_line.line_number;
message => 'Inside slinLines loop: Calling delete_single_line for ' || sl_line.line_number);
pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
p_document_id => p_auction_header_id,
p_document_line_id => sl_line.line_number,
x_return_status=>l_return_status ,
x_msg_count=>l_msg_count,
x_msg_data =>l_msg_data );
pon_negotiation_helper_pvt.delete_single_line(
x_result => x_result,
x_error_code => x_error_code,
x_error_message => x_error_message,
p_auction_header_id => p_auction_header_id,
p_line_number => sl_line.line_number,
p_group_type => sl_line.group_type,
p_origination_code => sl_line.line_origination_code,
p_org_id => sl_line.org_id,
p_parent_line_number => sl_line.parent_line_number,
p_sub_line_sequence_number => sl_line.SUB_LINE_SEQUENCE_NUMBER,
x_number_of_lines_deleted => temp
);
message => 'Deleted slin ' || sl_line.line_number);
message => 'after slinLines loop: Calling delete_single_line for ' || op_line.line_number);
pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
p_document_id => p_auction_header_id,
p_document_line_id => op_line.line_number,
x_return_status=>l_return_status ,
x_msg_count=>l_msg_count,
x_msg_data =>l_msg_data );
pon_negotiation_helper_pvt.delete_single_line(
x_result => x_result,
x_error_code => x_error_code,
x_error_message => x_error_message,
p_auction_header_id => p_auction_header_id,
p_line_number => op_line.line_number,
p_group_type => op_line.group_type,
p_origination_code => op_line.line_origination_code,
p_org_id => op_line.org_id,
p_parent_line_number => op_line.parent_line_number,
p_sub_line_sequence_number => op_line.SUB_LINE_SEQUENCE_NUMBER,
x_number_of_lines_deleted => temp
);
message => 'Deleted clin/option ' || op_line.line_number);
UPDATE pon_auction_item_prices_all SET clm_option_num = options.rownumber
WHERE auction_header_id = p_auction_header_id
AND line_number = options.line_number;
UPDATE pon_auction_item_prices_all SET clm_option_indicator = NULL
WHERE auction_header_id = p_auction_header_id
AND line_number = op_line.clm_base_line_num;
For requisition based clins, we need to find previously deleted slins also
for this clin and return them back to pool
*/
IF( x_slin_flag = 'N' and x_option_flag = 'N' AND x_is_req_backed='Y') THEN
IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
FND_LOG.string (log_level => FND_LOG.level_procedure,
module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
message => 'This is a clin.Checking previously deleted slins/options to return them to req pool ');
FOR rec IN (SELECT requisition_line_id FROM po_requisition_lines_all prl WHERE requisition_header_id = x_req_hdr_id
AND (group_line_id = x_req_line_id OR clm_base_line_num = x_req_line_id)
AND NOT EXISTS(SELECT 'Y' FROM pon_backing_requisitions
WHERE requisition_header_id = x_req_hdr_id
AND requisition_line_id = prl.requisition_line_id)) LOOP
update po_requisition_lines_all prla --Bug 4001965: use _all
set auction_header_id = null,
auction_display_number = null,
auction_line_number = null,
at_sourcing_flag = null, --
on_rfq_flag = null, -- bug 5370213
-- We don't want to set the reqs_in_pool_flag to 'Y'
--if any of the following conditions are met.
reqs_in_pool_flag =
(CASE
WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
or NVL(cancel_flag,'N') IN ('Y', 'I')
or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
or source_type_code = 'INVENTORY'
or NVL(line_location_id, -999) <> -999
or exists
(select 'Req Header auth_status is not approved or contractor_status is pending'
from po_requisition_headers_all prha
where prha.requisition_header_id = prla.requisition_header_id
and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
THEN null
ELSE 'Y'
END
), --
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where requisition_header_id = x_req_hdr_id
and requisition_line_id = rec.requisition_line_id;
x_number_of_lines_deleted := total_lines;
message => 'Number of lines deleted ' || x_number_of_lines_deleted);
END delete_single_line;
SELECT (Max(line_number) + 1) INTO l_new_line_number FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;
SELECT group_line_id,clm_info_flag,Nvl2(group_line_id,'N','Y'),SubStr(line_num_display,1,4)
INTO l_new_group_line_id,l_info_flag,l_is_clin, l_clin_num
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = p_src_line_number;
SELECT (Count(clm_option_num) + 1) INTO l_option_num FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND clm_base_line_num = p_src_line_number;
SELECT fnd_global.user_id INTO l_user_id FROM dual;
UPDATE pon_auction_item_prices_all
SET clm_option_Num = l_option_num,
clm_option_indicator = 'O',
clm_base_line_num = p_src_line_number
WHERE auction_header_id = p_auction_header_id
AND line_Number = l_new_line_number;
UPDATE pon_auction_item_prices_all
SET clm_option_indicator = 'B'
WHERE auction_header_id = p_auction_header_id
AND line_Number = p_src_line_number;
UPDATE pon_auction_item_prices_all
SET line_num_display = l_line_num_disp
WHERE auction_header_id = p_auction_header_id
AND line_Number = l_new_line_number;
SELECT line_number, clm_info_flag BULK COLLECT INTO l_line_numbers, l_info_flags
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = p_src_line_number;
SELECT Max(line_number) INTO l_max_line_number FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;
SELECT fnd_global.user_id INTO l_user_id FROM dual;
SELECT Nvl2(group_line_id,'N','Y'),nvl(group_line_id,l_max_line_number+1), decode(Nvl(clm_option_indicator,'N'),'O','Y','N') INTO l_clin_flag,l_new_group_line_id,l_is_option_copy
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = p_src_line_number;
SELECT pon_clo_renumber_pkg.NEXT_CLIN_NUM_WRAPPER(p_auction_header_id,'PON') INTO l_clin_num FROM dual;
SELECT SubStr(line_num_display,1,4) INTO l_clin_num FROM pon_auction_item_prices_all
WHERE auctioN_header_id = p_auction_header_id
AND line_number = p_src_line_number;
l_line_number := l_max_line_Number + 1; -- calculate line_number for the line to be inserted.
SELECT clm_option_indicator, clm_base_line_num INTO l_clm_base_option, l_clm_base_line
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
SELECT (Max(clm_option_num) + 1) INTO l_next_option_num FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND clm_base_line_num = l_clm_base_line;
UPDATE pon_auction_item_prices_all
SET clm_option_num = l_next_option_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
);
SELECT line_number, clm_info_flag BULK COLLECT INTO l_line_numbers, l_info_flags
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND (group_line_id = p_src_line_number
OR clm_base_line_num = p_src_line_number)
ORDER BY line_num_display;
l_line_number := l_max_line_Number + i + 1; -- calculate line_number for the line to be inserted.
SELECT clm_option_indicator, clm_base_line_num INTO l_clm_base_option, l_clm_base_line
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
UPDATE pon_auction_item_prices_all
SET CLM_BASE_LINE_NUM = copied_base_line_numbers(i)
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
SELECT (Max(clm_option_num) + 1) INTO l_next_option_num FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND clm_base_line_num = l_clm_base_line;
UPDATE pon_auction_item_prices_all
SET clm_option_num = l_next_option_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
);
SELECT line_num_display BULK COLLECT INTO clin_num_tbl
FROM pon_auction_item_prices_all
WHERE auctioN_header_id = p_auction_header_id
AND group_line_id IS NULL
order by line_num_display;
SELECT line_number BULK COLLECT INTO l_new_clins
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number > l_max_line_Number
AND group_line_id IS NULL;
UPDATE pon_auction_item_prices_all
SET line_num_display = next_clin_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_new_clins(i);
/* UPDATE pon_auction_item_prices_all
SET line_num_display = next_clin_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_max_line_Number + 1; -- this value would have been assigned to the new copied clin
INSERT INTO pon_auction_item_prices_all
(AUCTION_HEADER_ID,
--AWARD_STATUS,
LINE_NUMBER,
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
IP_CATEGORY_ID,
UOM_CODE,
QUANTITY,
NEED_BY_DATE,
SHIP_TO_LOCATION_ID,
--NUMBER_OF_BIDS,
--LOWEST_BID_PRICE,
--LOWEST_BID_QUANTITY,
--LOWEST_BID_PROMISED_DATE,
--LOWEST_BID_NUMBER,
--CLOSEST_PROMISED_DATE,
--CLOSEST_BID_PRICE,
--CLOSEST_BID_QUANTITY,
--CLOSEST_BID_NUMBER,
TARGET_PRICE,
THRESHOLD_PRICE,
BID_START_PRICE,
NOTE_TO_BIDDERS,
ATTACHMENT_FLAG,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_CREATION_DATE,
CLOSE_BIDDING_DATE,
NUMBER_OF_EXTENSIONS,
RESERVE_PRICE,
DISPLAY_TARGET_PRICE_FLAG,
CURRENT_PRICE,
--BEST_BID_PRICE,
--BEST_BID_QUANTITY,
--BEST_BID_PROMISED_DATE,
--BEST_BID_NUMBER,
TYPE,
--LOT_LINE_NUMBER,
MIN_BID_INCREMENT,
MIN_BID_DECREMENT,
--BEST_BID_PROXY_LIMIT_PRICE,
--BEST_BID_CURRENCY_PRICE,
--BEST_BID_CURRENCY_CODE,
PO_MIN_REL_AMOUNT,
--BEST_BID_FIRST_BID_PRICE,
UNIT_OF_MEASURE,
HAS_ATTRIBUTES_FLAG,
TRANSPORTATION_ORIGIN,
TRANSPORTATION_DEST,
AUCTION_HEADER_ID_ORIG_ROUND,
AUCTION_HEADER_ID_PREV_ROUND,
--LINE_NUMBER_ORIGINAL_ROUND,
--LINE_NUMBER_PREV_ROUND,
MULTIPLE_PRICES_FLAG,
--RESIDUAL_QUANTITY,
--PENDING_QUANTITY,
--CANCEL_QUANTITY,
--NUMBER_OF_COMMITMENTS,
--NUMBER_OF_PENDING_COMMITMENTS,
TBD_PRICING_FLAG,
NEED_BY_START_DATE,
PRICE,
FREIGHT_TERMS_CODE,
--AWARDED_QUANTITY,
MODIFIED_FLAG,
--BEST_BID_BID_PRICE,
--BEST_BID_SCORE,
--BEST_BID_BID_NUMBER,
--BEST_BID_BID_CURRENCY_PRICE,
--BEST_BID_BID_CURRENCY_CODE,
ORG_ID,
HAS_PRICE_ELEMENTS_FLAG,
LINE_TYPE_ID,
ORDER_TYPE_LOOKUP_CODE,
--LINE_ORIGINATION_CODE,
--REQUISITION_NUMBER,
ITEM_REVISION,
ITEM_ID,
ITEM_NUMBER,
PRICE_BREAK_TYPE,
PRICE_BREAK_NEG_FLAG,
HAS_SHIPMENTS_FLAG,
SOURCE_DOC_NUMBER,
SOURCE_LINE_NUMBER,
SOURCE_DOC_ID,
SOURCE_LINE_ID,
--ALLOCATION_STATUS,
PRICE_DISABLED_FLAG,
QUANTITY_DISABLED_FLAG,
JOB_ID,
ADDITIONAL_JOB_DETAILS,
PO_AGREED_AMOUNT,
HAS_PRICE_DIFFERENTIALS_FLAG,
DIFFERENTIAL_RESPONSE_TYPE,
PURCHASE_BASIS,
IS_QUANTITY_SCORED,
IS_NEED_BY_DATE_SCORED,
DISP_LINE_NUMBER,
LAST_UPDATE_LOGIN,
LAST_AMENDMENT_UPDATE,
MODIFIED_DATE,
PRICE_DIFF_SHIPMENT_NUMBER,
GROUP_TYPE,
PARENT_LINE_NUMBER,
DOCUMENT_DISP_LINE_NUMBER,
MAX_SUB_LINE_SEQUENCE_NUMBER,
SUB_LINE_SEQUENCE_NUMBER,
HAS_BUYER_PFS_FLAG,
UNIT_TARGET_PRICE,
UNIT_DISPLAY_TARGET_FLAG,
HAS_PAYMENTS_FLAG,
ADVANCE_AMOUNT,
RECOUPMENT_RATE_PERCENT,
PROGRESS_PYMT_RATE_PERCENT,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
WORK_APPROVER_USER_ID,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
HAS_QUANTITY_TIERS,
--- CLM - Clin Slin project Changes Start.
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.
--
UDA_TEMPLATE_ID,
CLM_CONTRACT_TYPE,
CLM_COST_CONSTRAINT,
CLM_IDC_TYPE,
CLM_AMOUNT,
CLM_NEED_BY_DATE,
EXHIBIT_NUMBER
)
SELECT
AUCTION_HEADER_ID,
--AWARD_STATUS,
p_dest_line_number, -- line_number
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
IP_CATEGORY_ID,
UOM_CODE,
QUANTITY,
NEED_BY_DATE,
SHIP_TO_LOCATION_ID,
--NUMBER_OF_BIDS,
--LOWEST_BID_PRICE,
--LOWEST_BID_QUANTITY,
--LOWEST_BID_PROMISED_DATE,
--LOWEST_BID_NUMBER,
--CLOSEST_PROMISED_DATE,
--CLOSEST_BID_PRICE,
--CLOSEST_BID_QUANTITY,
--CLOSEST_BID_NUMBER,
TARGET_PRICE,
THRESHOLD_PRICE,
BID_START_PRICE,
NOTE_TO_BIDDERS,
ATTACHMENT_FLAG,
LANGUAGE_CODE,
sysdate,
p_user_id,
sysdate,
p_user_id,
AUCTION_CREATION_DATE,
CLOSE_BIDDING_DATE,
NUMBER_OF_EXTENSIONS,
RESERVE_PRICE,
DISPLAY_TARGET_PRICE_FLAG,
CURRENT_PRICE,
--BEST_BID_PRICE,
--BEST_BID_QUANTITY,
--BEST_BID_PROMISED_DATE,
--BEST_BID_NUMBER,
TYPE,
--LOT_LINE_NUMBER,
MIN_BID_INCREMENT,
MIN_BID_DECREMENT,
--BEST_BID_PROXY_LIMIT_PRICE,
--BEST_BID_CURRENCY_PRICE,
--BEST_BID_CURRENCY_CODE,
PO_MIN_REL_AMOUNT,
--BEST_BID_FIRST_BID_PRICE,
UNIT_OF_MEASURE,
HAS_ATTRIBUTES_FLAG,
TRANSPORTATION_ORIGIN,
TRANSPORTATION_DEST,
AUCTION_HEADER_ID_ORIG_ROUND,
AUCTION_HEADER_ID_PREV_ROUND,
--LINE_NUMBER_ORIGINAL_ROUND,
--LINE_NUMBER_PREV_ROUND,
MULTIPLE_PRICES_FLAG,
--RESIDUAL_QUANTITY,
--PENDING_QUANTITY,
--CANCEL_QUANTITY,
--NUMBER_OF_COMMITMENTS,
--NUMBER_OF_PENDING_COMMITMENTS,
TBD_PRICING_FLAG,
NEED_BY_START_DATE,
PRICE,
FREIGHT_TERMS_CODE,
--AWARDED_QUANTITY,
MODIFIED_FLAG,
--BEST_BID_BID_PRICE,
--BEST_BID_SCORE,
--BEST_BID_BID_NUMBER,
--BEST_BID_BID_CURRENCY_PRICE,
--BEST_BID_BID_CURRENCY_CODE,
ORG_ID,
HAS_PRICE_ELEMENTS_FLAG,
LINE_TYPE_ID,
ORDER_TYPE_LOOKUP_CODE,
--LINE_ORIGINATION_CODE,
--REQUISITION_NUMBER,
ITEM_REVISION,
ITEM_ID,
ITEM_NUMBER,
PRICE_BREAK_TYPE,
PRICE_BREAK_NEG_FLAG,
HAS_SHIPMENTS_FLAG,
null,
null,
NULL,--SOURCE_DOC_ID,
NULL,--SOURCE_LINE_ID,
--ALLOCATION_STATUS,
PRICE_DISABLED_FLAG,
QUANTITY_DISABLED_FLAG,
JOB_ID,
ADDITIONAL_JOB_DETAILS,
PO_AGREED_AMOUNT,
HAS_PRICE_DIFFERENTIALS_FLAG,
DIFFERENTIAL_RESPONSE_TYPE,
PURCHASE_BASIS,
IS_QUANTITY_SCORED,
IS_NEED_BY_DATE_SCORED,
p_dest_line_number, -- disp_line_number
p_user_id,
0, -- last_amendment_update
MODIFIED_DATE,
PRICE_DIFF_SHIPMENT_NUMBER,
GROUP_TYPE,
PARENT_LINE_NUMBER,
p_dest_line_number,
p_dest_line_number,
p_dest_line_number,
HAS_BUYER_PFS_FLAG,
UNIT_TARGET_PRICE,
UNIT_DISPLAY_TARGET_FLAG,
HAS_PAYMENTS_FLAG,
ADVANCE_AMOUNT,
RECOUPMENT_RATE_PERCENT,
PROGRESS_PYMT_RATE_PERCENT,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
WORK_APPROVER_USER_ID,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
HAS_QUANTITY_TIERS,
--- CLM - Clin Slin project Changes Start.
NULL, --line_num_display - this will be calculated later
/*CASE WHEN l_clin_flag='N' THEN group_line_id -- if a slin is copied, then group_line_id wont change
WHEN (i>1 AND group_line_id IS NOT NULL) THEN l_max_line_number+1 end, */
Nvl2(group_line_id,p_group_line_id,null),
CLM_INFO_FLAG,
CLM_OPTION_INDICATOR, --CLM_OPTION_INDICATOR,
CLM_BASE_LINE_NUM,--CLM_BASE_LINE_NUM,
CLM_OPTION_NUM,--CLM_OPTION_NUM,
CLM_OPTION_FROM_DATE,--CLM_OPTION_FROM_DATE,
CLM_OPTION_TO_DATE,--CLM_OPTION_TO_DATE,
CLM_FUNDED_FLAG,
--- CLM - Clin Slin project Changes End.
--
UDA_TEMPLATE_ID,
CLM_CONTRACT_TYPE,
CLM_COST_CONSTRAINT,
CLM_IDC_TYPE,
CLM_AMOUNT,
CLM_NEED_BY_DATE,
EXHIBIT_NUMBER
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = p_src_line_number;
INSERT INTO pon_auction_attributes(
auction_header_id,
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE ,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL ,
ATTR_GROUP,
ATTR_MAX_SCORE,
INTERNAL_ATTR_FLAG,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER ,
MODIFIED_FLAG,
MODIFIED_DATE,
LAST_AMENDMENT_UPDATE,
IP_CATEGORY_ID,
IP_DESCRIPTOR_ID,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD)
SELECT p_auction_header_id,
p_dest_line_number,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE ,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
sysdate,
p_user_id,
sysdate,
p_user_id,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL ,
ATTR_GROUP,
ATTR_MAX_SCORE,
INTERNAL_ATTR_FLAG,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER ,
MODIFIED_FLAG,
MODIFIED_DATE,
LAST_AMENDMENT_UPDATE,
IP_CATEGORY_ID,
IP_DESCRIPTOR_ID,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD
FROM pon_auction_attributes paa
WHERE paa.auction_header_id = p_auction_header_id
AND paa.ATTR_LEVEL = 'LINE'
AND paa.line_number = p_src_line_number;
INSERT
INTO PON_ATTRIBUTE_SCORES
( AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE,
FROM_RANGE,
TO_RANGE,
SCORE,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
(SELECT
p_auction_header_id,
p_dest_line_number,
pas.ATTRIBUTE_SEQUENCE_NUMBER,
pas.VALUE,
pas.FROM_RANGE,
pas.TO_RANGE,
pas.SCORE,
pas.ATTRIBUTE_LIST_ID,
pas.SEQUENCE_NUMBER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id -- LAST_UPDATED_BY
FROM PON_ATTRIBUTE_SCORES pas,
PON_AUCTION_ATTRIBUTES paa
WHERE pas.AUCTION_HEADER_ID = p_auction_header_id
AND pas.auction_header_id = paa.auction_header_id
AND pas.line_number = paa.line_number
AND paa.attr_level = 'LINE'
AND pas.attribute_sequence_number = paa.sequence_number
AND pas.line_number = p_src_line_number
) ;
INSERT
INTO PON_PRICE_DIFFERENTIALS
( AUCTION_HEADER_ID,
LINE_NUMBER,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE,
MULTIPLIER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(SELECT
p_auction_header_id,
p_dest_line_number,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE,
MULTIPLIER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id -- LAST_UPDATE_LOGIN
FROM PON_PRICE_DIFFERENTIALS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = p_src_line_number) ;
INSERT INTO
PON_AUCTION_SHIPMENTS_ALL
( AUCTION_HEADER_ID,
LINE_NUMBER,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG,
DIFFERENTIAL_RESPONSE_TYPE,
MAX_QUANTITY
)
(SELECT p_auction_header_id,
p_dest_line_number,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE,
EFFECTIVE_START_DATE, -- EFFECTIVE_START_DATE
EFFECTIVE_END_DATE, -- EFFECTIVE_END_DATE
ORG_ID, -- Do we need to set thi OrgId to the current one
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id, -- LAST_UPDATE_LOGIN
HAS_PRICE_DIFFERENTIALS_FLAG, -- HAS_PRICE_DIFFERENTIALS_FLAG
DIFFERENTIAL_RESPONSE_TYPE, -- DIFFERENTIAL_RESPONSE_TYPE
MAX_QUANTITY
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND SHIPMENT_TYPE in ('PRICE BREAK','QUANTITY BASED')
AND line_number = p_src_line_number) ;
INSERT INTO
PON_PRICE_ELEMENTS
( AUCTION_HEADER_ID,
LINE_NUMBER,
LIST_ID,
PRICE_ELEMENT_TYPE_ID,
PRICING_BASIS,
VALUE,
DISPLAY_TARGET_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE,
DISPLAY_TO_SUPPLIERS_FLAG
)
(SELECT
p_auction_header_id,
p_dest_line_number,
P.LIST_ID,
P.PRICE_ELEMENT_TYPE_ID,
P.PRICING_BASIS,
P.VALUE,
P.DISPLAY_TARGET_FLAG,
P.SEQUENCE_NUMBER,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
PF_TYPE, -- Tranformation project related column
DISPLAY_TO_SUPPLIERS_FLAG -- Tranformation project related column
FROM PON_PRICE_ELEMENTS P,
PON_PRICE_ELEMENT_TYPES_VL VL
WHERE P.AUCTION_HEADER_ID = p_auction_header_id
AND P.PRICE_ELEMENT_TYPE_ID <> -10
AND P.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID
AND VL.ENABLED_FLAG = 'Y'
AND P.line_number= p_src_line_number) ;
INSERT INTO
PON_AUC_PAYMENTS_SHIPMENTS
( AUCTION_HEADER_ID,
PAYMENT_ID,
LINE_NUMBER,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_TYPE_CODE,
PAYMENT_DESCRIPTION,
SHIP_TO_LOCATION_ID,
QUANTITY,
UOM_CODE,
TARGET_PRICE,
NEED_BY_DATE,
WORK_APPROVER_USER_ID,
NOTE_TO_BIDDERS,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(SELECT
p_auction_header_id,
PON_AUC_PAYMENTS_SHIPMENTS_S1.NEXTVAL,
p_dest_line_number,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_TYPE_CODE,
PAYMENT_DESCRIPTION,
SHIP_TO_LOCATION_ID,
QUANTITY,
UOM_CODE,
TARGET_PRICE,
NEED_BY_DATE,
WORK_APPROVER_USER_ID,
NOTE_TO_BIDDERS,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
fnd_global.login_id
FROM pon_auc_payments_shipments
WHERE auction_header_id = p_auction_header_id
AND line_number =p_src_line_number );
select DISTINCT aip1.group_line_id option_number, aip2.group_line_id base_number
from pon_auction_item_prices_all aip1,
pon_auction_item_prices_all aip2
where aip1.auction_header_id = p_auction_header_id
AND aip1.clm_option_indicator = 'O'
AND Nvl(aip1.clm_info_flag,'N') = 'N'
and aip1.clm_base_line_num is not NULL
AND aip1.group_line_id IS NOT NULL
and aip1.auction_header_id = aip2.auction_header_id
AND aip2.group_line_id IS NOT null
and aip1.clm_base_line_num = aip2.line_number
and aip1.group_line_id <> aip2.group_line_id;
PROCEDURE SEND_LINE_DELETED_NOTIF(p_caller IN VARCHAR2,
p_document_id IN NUMBER,
p_document_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_slin_flag VARCHAR2(1);
l_module_name VARCHAR2(100) := 'SEND_LINE_DELETED_NOTIF';
SELECT pbr.REQUISITION_HEADER_ID,
pbr.REQUISITION_LINE_ID,
paip.requisition_number,
Nvl2(paip.group_line_id,'Y','N'),
Nvl2(paip.clm_base_line_num,'Y','N'),
'Y',
pah.document_number,
paip.line_num_display,
prl.line_num_display,
prh.preparer_id,
'SOLICITATION',
prl.item_description,
NULL -- Buyer ID
FROM pon_backing_requisitions pbr,
pon_auction_item_prices_all paip,
pon_auction_headers_all pah,
po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE pbr.auction_header_id = p_document_id
AND pbr.line_number = p_document_line_id
AND paip.auction_header_id = pbr.auction_header_id
AND paip.line_number = pbr.line_number
AND Nvl(paip.line_origination_code,'N') = 'REQUISITION'
AND pah.auction_header_id = paip.auction_header_id
AND prl.auction_header_id = pah.auction_header_id
AND prl.auction_line_number = paip.line_number
AND prl.requisition_header_id = prh.requisition_header_id;
SELECT DISTINCT
prh.REQUISITION_HEADER_ID,
prl.REQUISITION_LINE_ID,
prh.segment1,
Nvl2(pol.group_line_id,'Y','N'),
Nvl2(pol.clm_base_line_num,'Y','N'),
'Y',
Decode(p_caller,'PO_MOD',PD.MODIFICATION_NUMBER,poh.clm_document_number) ,
pol.line_num_display,
prl.line_num_display,
prh.preparer_id,
poh.type_lookup_code,
prl.item_description,
poh.agent_id
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh,
po_req_distributions_all prd,
po_headers_draft_all poh,
po_lines_draft_all pol,
po_distributions_draft_all pod,
po_drafts pd
WHERE
Nvl(pol.draft_id,-1)=Nvl(poh.draft_id,-1)
AND Nvl(pd.draft_id,-1)=Nvl(pol.draft_id,-1)
AND Nvl(pod.draft_id,-1)=Nvl(pol.draft_id,-1)
AND Nvl(pol.draft_id,-1)=p_document_id
AND pol.po_header_id = poh.po_header_id
AND pod.po_line_id = pol.po_line_id
AND prl.requisition_line_id= prd.requisition_line_id
AND prl.requisition_header_id = prh.requisition_header_id
AND pod.req_distribution_id = prd.distribution_id
AND Nvl(pol.delete_flag,'N') = 'Y'
AND (pol.group_line_id IS NOT NULL
AND NOT EXISTS (SELECT 'Parent Clin Deleted'
FROM po_lines_draft_all
WHERE po_header_id=pol.po_header_id
AND draft_id=pol.draft_id
AND Nvl(delete_flag,'N')='Y'
AND po_line_id=pol.group_line_id ))
UNION
-- Info Slins and Option lines will be linked to backing Req via po_line_id
SELECT DISTINCT
prh.REQUISITION_HEADER_ID,
prl.REQUISITION_LINE_ID,
prh.segment1,
Nvl2(pol.group_line_id,'Y','N'),
Nvl2(pol.clm_base_line_num,'Y','N'),
'Y',
Decode(p_caller,'PO_MOD',PD.MODIFICATION_NUMBER,poh.clm_document_number) ,
pol.line_num_display,
prl.line_num_display,
prh.preparer_id,
poh.type_lookup_code,
prl.item_description,
poh.agent_id
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh,
po_headers_draft_all poh,
po_lines_draft_all pol,
po_drafts pd
WHERE
Nvl(pol.draft_id,-1)=Nvl(poh.draft_id,-1)
AND Nvl(pd.draft_id,-1)=Nvl(pol.draft_id,-1)
AND Nvl(pol.draft_id,-1)=p_document_id
AND pol.po_header_id = poh.po_header_id
AND prl.requisition_header_id = prh.requisition_header_id
AND pol.po_line_id = prl.po_line_id
AND Nvl(pol.delete_flag,'N') = 'Y'
AND (Nvl(pol.clm_info_flag,'N')='Y'
OR Nvl(pol.clm_option_indicator,'B')='O')
AND ((pol.group_line_id IS NOT NULL
AND NOT EXISTS (SELECT 'Parent Clin Deleted'
FROM po_lines_draft_all
WHERE po_header_id=pol.po_header_id
AND draft_id=pol.draft_id
AND Nvl(delete_flag,'N')='Y'
AND po_line_id=pol.group_line_id ))
OR (pol.clm_base_line_num IS NOT NULL
AND NOT EXISTS ( SELECT 'Base Line Deleted'
FROM po_lines_draft_all
WHERE po_header_id=pol.po_header_id
AND draft_id=pol.draft_id
AND Nvl(delete_flag,'N')='Y'
AND po_line_id=pol.clm_base_line_num ))
);
update po_requisition_lines_all prla
set auction_header_id = null,
auction_display_number = null,
auction_line_number = null,
po_line_id = null,
line_location_id=null,
at_sourcing_flag = null,
on_rfq_flag = null,
reqs_in_pool_flag = NULL
WHERE requisition_line_id=l_req_line_id;
SELECT PON_LINE_DELETE_WF_S.nextval
INTO l_sequence
FROM dual;
SELECT papf.full_name
INTO l_buyer_name
FROM PER_ALL_PEOPLE_F papf
where papf.person_id =l_buyer_id
AND TRUNC(SYSDATE)
BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
select full_name
into l_buyer_name
from fnd_user usr,
per_all_people_f papf
where usr.user_name = l_ownerName
AND usr.employee_id = papf.person_id
AND TRUNC(SYSDATE)
BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
process => 'NEG_LINE_DELETED');
SELECT type_name
INTO l_doc_type_dsp
FROM po_document_types_vl
WHERE document_type_code = 'SOLICITATION'
AND document_subtype = 'SOLICITATION'
AND ORG_ID = Nvl(PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID, FND_GLOBAL.ORG_ID);
SELECT Decode(p_caller,'PO_MOD', type_name ||' '|| FND_MESSAGE.GET_STRING('PO','PO_MODIFICATION') ,type_name)
INTO l_doc_type_dsp
FROM po_document_types_vl pdt,
po_headers_draft_all poh
WHERE pdt.document_type_code IN ('PO', 'PA')
AND pdt.ORG_ID =Nvl(PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID,
FND_GLOBAL.ORG_ID)
AND pdt.document_subtype = POH.type_lookup_code
AND poh.draft_id=p_document_id;
END SEND_LINE_DELETED_NOTIF;
SELECT MAX(line_number)
INTO l_max_line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;
SELECT line_number, clm_info_flag,exhibit_number BULK COLLECT
INTO l_line_numbers, l_info_flags,l_exhibits
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_src_line_number;
SELECT fnd_global.user_id INTO l_user_id FROM dual;
SELECT Nvl2(group_line_id, 'N', 'Y'),
NVL(group_line_id, l_max_line_number + 1),
DECODE(NVL(clm_option_indicator, 'N'), 'O', 'Y', 'N')
INTO l_clin_flag, l_new_group_line_id, l_is_option_copy
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_src_line_number;
SELECT pon_clo_renumber_pkg.NEXT_CLIN_NUM_WRAPPER(p_auction_header_id,
'PON')
INTO l_clin_num
FROM dual;
SELECT SUBSTR(line_num_display, 1, 4)
INTO l_clin_num
FROM pon_auction_item_prices_all
WHERE auctioN_header_id = p_auction_header_id
AND line_number = l_src_line_number;
l_line_number := l_max_line_Number + 1; -- calculate line_number for the line to be inserted.
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
); */
SELECT clm_need_by_date,
need_by_start_date,
need_by_date,
clm_option_from_date,
clm_option_to_date,
clm_option_num,
clm_option_indicator,
clm_info_flag
INTO l_need_by_date,
l_pop_start_date,
l_pop_end_date,
l_option_from_date,
l_option_to_date,
l_clm_option_num,
l_clm_option_indicator,
l_clm_info_flag
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
UPDATE pon_auction_item_prices_all
SET clm_need_by_date = l_need_by_date,
need_by_start_date = l_pop_start_date,
need_by_date = l_pop_end_date,
clm_option_from_date = Nvl2(l_exhibits(1),NULL,l_option_from_date),
clm_option_to_date = Nvl2(l_exhibits(1),NULL,l_option_to_date),
clm_option_num = Nvl2(l_exhibits(1),NULL,l_clm_option_num),
clm_option_indicator = Nvl2(l_exhibits(1),NULL,l_clm_option_indicator),
-- clm_info_flag = l_clm_info_flag ,
clm_base_line_num = Nvl2(l_exhibits(1),NULL,l_clm_base_line)
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
SELECT line_number, clm_info_flag BULK COLLECT
INTO l_line_numbers, l_info_flags
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND (group_line_id = l_src_line_number)
ORDER BY line_num_display;
l_line_number := l_max_line_Number + j + 1; -- calculate line_number for the line to be inserted.
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
); */
SELECT clm_need_by_date,
need_by_start_date,
need_by_date,
clm_option_from_date,
clm_option_to_date,
clm_option_num,
clm_option_indicator,
clm_info_flag
INTO l_need_by_date,
l_pop_start_date,
l_pop_end_date,
l_option_from_date,
l_option_to_date,
l_clm_option_num,
l_clm_option_indicator,
l_clm_info_flag
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
UPDATE pon_auction_item_prices_all
SET clm_need_by_date = l_need_by_date,
need_by_start_date = l_pop_start_date,
need_by_date = l_pop_end_date,
clm_option_from_date = l_option_from_date,
clm_option_to_date = l_option_to_date,
clm_option_num = l_clm_option_num,
clm_option_indicator = l_clm_option_indicator,
-- clm_info_flag = l_clm_info_flag ,
clm_base_line_num = l_clm_base_line
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number;
SELECT line_num_display BULK COLLECT
INTO clin_num_tbl
FROM pon_auction_item_prices_all
WHERE auctioN_header_id = p_auction_header_id
AND group_line_id IS NULL
ORDER BY line_num_display;
SELECT line_number,exhibit_number BULK COLLECT
INTO l_new_clins,l_new_exhibits
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_number > l_max_line_Number
AND group_line_id IS NULL;
UPDATE pon_auction_item_prices_all
SET line_num_display = next_clin_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_new_clins(i);
/* UPDATE pon_auction_item_prices_all
SET line_num_display = next_clin_num
WHERE auction_header_id = p_auction_header_id
AND line_number = l_max_line_Number + 1; -- this value would have been assigned to the new copied clin
SELECT Count(*)
INTO x_max_line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;