[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM po_price_differentials
WHERE entity_type = 'REQ LINE'
AND entity_id = p_req_line_id;
select org_id
into x_org_id
from po_system_parameters;
This Code is to check if the Requisition Line which we are trying to AutoCreate has already been locked or deleted or cancelled.
If so, simply return to the caller and show a message saying Requisition Line can't be auto created.
*****/
BEGIN
SAVEPOINT CHECK_LOCK;
SELECT Nvl(reqs_in_pool_flag,'Y'),cancel_flag,closed_code
INTO x_req_in_pool_flag,
x_cancel_flag,
x_closed_code
FROM po_requisition_lines WHERE requisition_line_id=t_req_lines(i)
FOR UPDATE OF auction_header_id NOWAIT;
/* The req line has been deleted since it was queried up. */
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string(fnd_log.level_statement,g_log_head,'Exception1 : '||SQLERRM);
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
DELETE FROM po_session_gt WHERE index_char1='PO_NEGOTIATIONS_SV1';
INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, date1)
SELECT prl.requisition_line_id,
'PO_NEGOTIATIONS_SV1',
prh.segment1,
prl.line_num,
prl.item_id,
prl.category_id,
decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, num4, char2, date1)
SELECT prl.requisition_line_id,
'PO_NEGOTIATIONS_SV1',
prh.segment1,
prl.line_num,
prl.item_id,
prl.category_id,
t_neg_lines(i),
t_neg_line_num(i),
decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = t_req_lines(i);
l_string := ' select rl.requisition_line_id,
Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,rl.LINE_TYPE_ID), -- line_type_id
rl.ITEM_ID, -- item_id
rl.ITEM_REVISION, -- item_revision
rl.CATEGORY_ID, -- category id
rl.QUANTITY, -- quantity
Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,RL.UNIT_MEAS_LOOKUP_CODE), -- unit meas lookup code
rl.JOB_ID, -- job id
rl.requisition_header_id,
psg.char1,
RL.order_type_lookup_code, --
rl.note_to_vendor,
rl.need_by_date,
RL.amount, --
msi.concatenated_segments,
rl.deliver_to_location_id,
rl.unit_price,
rl.item_description,
PJ.name, --
RL.job_long_description, --
RL.org_id, --
-- added for CLIN SLIN Project
rl.LINE_NUM_DISPLAY,
rl.GROUP_LINE_ID,
rl.CLM_INFO_FLAG,
rl.CLM_OPTION_INDICATOR,
rl.CLM_OPTION_NUM,
rl.CLM_OPTION_FROM_DATE,
rl.CLM_OPTION_TO_DATE,
rl.CLM_FUNDED_FLAG,
rl.CLM_BASE_LINE_NUM,
rl.CONTRACT_TYPE,
rl.COST_CONSTRAINT,
decode(rl.CONTRACT_TYPE, null, null, ''IDC_NA''),
rl.UDA_TEMPLATE_ID, --
psg.num4,
psg.char2
/*into x_req_header_id,
l_value_basis, --
x_note_to_vendor,
x_need_by_date,
l_amount, --
x_item_num,
x_deliver_to_location_id,
x_unit_price,
x_item_desc,
l_job_name, --
l_job_long_description, --
l_from_org_id */ --
from po_requisition_lines_all rl, --
mtl_system_items_kfv msi,
per_jobs_vl PJ,
PO_SESSION_GT psg --
where requisition_line_id = psg.key --
and rl.item_id = msi.inventory_item_id(+)
and coalesce(msi.organization_id, rl.destination_organization_id,-1) =
nvl(rl.destination_organization_id,-1)
AND RL.job_id = PJ.job_id(+) --
AND psg.index_char1 = ''PO_NEGOTIATIONS_SV1''' ;
select segment1
into x_req_num
from po_requisition_headers_all --
where requisition_header_id=x_req_header_id;
SELECT nvl(ship_to_location_id,location_id)
INTO x_ship_to_location_id
FROM hr_locations
WHERE location_id = x_deliver_to_location_id;
select mum.uom_code
into x_uom_code
from mtl_units_of_measure mum
where mum.unit_of_measure = x_uom; --bug 5841426
update po_requisition_lines_all --
set on_rfq_flag = 'Y',
auction_header_id = x_negotiation_id,
auction_display_number = to_char(x_negotiation_id) ,
auction_line_number = x_negotiation_line_num,
at_sourcing_flag = 'Y', --
reqs_in_pool_flag = NULL, --
last_update_date = sysdate,
last_updated_by = to_number(FND_PROFILE.VALUE('user_id')),
last_update_login = to_number(FND_PROFILE.VALUE('user_id'))
where requisition_line_id = x_req_line_id ; --5841426
SELECT document_number INTO x_document_number FROM pon_auction_headers_all WHERE auction_header_id=x_negotiation_id;
update po_requisition_lines_all
SET auction_display_number = x_document_number
where requisition_line_id = x_req_line_id ;
Name: DELETE_NEGOTIATION_REF
DESC: Delete negotiation reference from the backing requisition
==============================================================================*/
PROCEDURE DELETE_NEGOTIATION_REF (x_negotiation_id in number,
x_negotiation_line_num in number,
x_error_code out NOCOPY varchar2) is
BEGIN
if x_negotiation_line_num is null then
-- : added update of at_sourcing_flag and of
-- WHO columns.
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 auction_header_id = x_negotiation_id;
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 auction_header_id = x_negotiation_id
and auction_line_number = x_negotiation_line_num;
Name: UPDATE_NEGOTIATION_REF
DESC: Update negotiation reference in the backing requisition
==============================================================================*/
PROCEDURE UPDATE_NEGOTIATION_REF (x_old_negotiation_id in number ,
x_new_negotiation_id in number ,
x_new_negotiation_num in varchar2 ,
x_error_code out NOCOPY varchar2) is
BEGIN
update po_requisition_lines_all --Bug 4001965: use _all
set auction_header_id = x_new_negotiation_id,
auction_display_number = x_new_negotiation_num
where auction_header_id = x_old_negotiation_id;
Name: UPDATE_NEGOTIATION_LINE_REF
DESC: Update negotiation reference in the backing requisition line to
point to another negotiation line.
Input parameters :
p_api_version: Version of the API expected by caller. Current value 1.0
p_old_negotiation_id : negotiation whose reference has to be replaced
p_old_negotiation_line_num : negotiation line whose reference has to be replaced
p_new_negotiation_num/id : new negotiation reference
p_new_negotiation_line_num : new negotiation line where reference has
to be added
Output parameters :
x_return_status: The return status of the API. Valid values are:
FND_API.G_RET_STS_SUCCESS
FND_API.G_RET_STS_ERROR
FND_API.G_RET_STS_UNEXP_ERROR
x_error_message: Contain translated error message in case the return status
is G_RET_STS_ERROR or G_RET_STS_UNEXP_ERROR
Version: Current Version 1.0
Changed: Initial design 1/27/2003
Previous Version 1.0
==============================================================================*/
PROCEDURE UPDATE_NEGOTIATION_LINE_REF (
p_api_version IN NUMBER,
p_old_negotiation_id IN NUMBER,
p_old_negotiation_line_num IN NUMBER,
p_new_negotiation_id IN NUMBER,
p_new_negotiation_line_num IN NUMBER,
p_new_negotiation_num IN varchar2,
x_return_status OUT NOCOPY varchar2,
x_error_message OUT NOCOPY varchar2) is
l_api_name CONSTANT varchar2(30) := 'UPDATE_NEGOTIATION_LINE_REF';
update po_requisition_lines_all --Bug 4001965: use _all
set auction_header_id = p_new_negotiation_id,
auction_display_number = p_new_negotiation_num,
auction_line_number = p_new_negotiation_line_num
where auction_header_id = p_old_negotiation_id and
auction_line_number = p_old_negotiation_line_num;
END UPDATE_NEGOTIATION_LINE_REF;
Name: UPDATE_REQ_POOL
DESC: Update requisition pool flag in the backing requisition
==============================================================================*/
PROCEDURE UPDATE_REQ_POOL (x_negotiation_id in number,
x_negotiation_line_num in number,
x_flag_value in varchar2,
x_error_code out NOCOPY varchar2) is
x_sourcing_flag_value PO_REQUISITION_LINES_ALL.at_sourcing_flag%TYPE; --
update po_requisition_lines_all prla --Bug 4001965: use _all
set reqs_in_pool_flag = x_new_pool_value,
at_sourcing_flag = x_sourcing_flag_value, --
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where auction_header_id = x_negotiation_id
-- We only want to set the reqs_in_pool_flag to 'Y'
--if all of the following conditions are met.
and nvl(modified_by_agent_flag,'N') <> 'Y' --
and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
and source_type_code <> 'INVENTORY'
and NVL(line_location_id, -999) = -999
and not 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'));
update po_requisition_lines_all prla --Bug 4001965: use _all
set reqs_in_pool_flag = x_new_pool_value,
at_sourcing_flag = x_sourcing_flag_value, --
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where auction_header_id = x_negotiation_id
and auction_line_number = x_negotiation_line_num
-- We only want to set the reqs_in_pool_flag to 'Y'
--if all of the following conditions are met.
and nvl(modified_by_agent_flag,'N') <> 'Y' --
and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
and source_type_code <> 'INVENTORY'
and NVL(line_location_id, -999) = -999
and not 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'));
select at_sourcing_flag --
from po_requisition_lines_all --Bug 4001965: use _all
where requisition_line_id = X_doc_line_id;
select at_sourcing_flag --
from po_requisition_lines_all --Bug 4001965: use _all
where requisition_header_id = X_doc_header_id;
insert into pon_auc_headers_interface(
interface_auction_header_id,
neg_type,
contract_type,
ship_to_location_id,
bill_to_location_id,
payment_terms_id,
freight_terms_code,
fob_code,
carrier_code,
note_to_bidders,
creation_date,
created_by,
last_update_date,
last_updated_by,
allow_other_bid_currency_flag,
po_agreed_amount,
origination_code,
global_agreement_flag,
po_min_rel_amount,
currency_code,
rate_type,
rate_date,
rate,
user_id,
org_id,
supplier_id,
source_doc_id,
source_doc_number,
source_doc_msg,
source_doc_line_msg,
source_doc_msg_app,
po_style_id, -- BUG#5532470
language_code, --Bug#4911361
supplier_site_id)
select PON_AUC_HEADERS_INTERFACE_S.nextval,
p_negotiation_type,
'BLANKET',
ship_to_location_id,
bill_to_location_id,
terms_id,
freight_terms_lookup_code,
fob_lookup_code,
ship_via_lookup_code,
note_to_vendor,
creation_date,
created_by,
last_update_date,
last_updated_by,
'Y',
blanket_total_amount,
type_lookup_code,
global_agreement_flag,
min_release_amount,
currency_code,
rate_type,
rate_date,
rate,
l_user_id,
org_id,
vendor_id,
po_header_id,
segment1,
--
--Sourcing team wants the name of the messages and
--not the message text. So inserting the names of messages
--for 'Blanket Agreement' and 'Line' respectively
'PO_POTYPE_BLKT',
'PO_SOURCING_LINE_NUMBER',
--
'PO',
style_id, -- BUG#5532470
created_language, --Bug#4911361
vendor_site_id --
from po_headers
where po_header_id=p_po_header_id;
select PON_AUC_HEADERS_INTERFACE_S.currval
into l_interface_id
from dual;
select segment1
into l_po_num
from po_headers
where po_header_id = p_po_header_id;
insert into pon_auc_items_interface(
interface_auction_header_id,
interface_line_number,
line_type_id,
item_description,
org_id,
category_id,
quantity,
current_price, --Bug#4915340
note_to_bidders,
uom_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
origination_code,
po_min_rel_amount,
price_break_type,
item_id,
item_number,
item_revision,
source_doc_number,
source_line_number,
source_doc_id,
source_line_id,
job_id, --
po_agreed_amount, --
purchase_basis, --
ip_category_id, --
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
clm_contract_type,
clm_cost_constraint,
clm_idc_type
)
select l_interface_id,
rownum, --bug 2714549: renumbers lines
pl.line_type_id,
item_description,
pl.org_id,
pl.category_id,
quantity_committed, --Bug #2706156
--Bug #2737797
decode ( PL.order_type_lookup_code --
, 'AMOUNT' , PL.committed_amount
, 'FIXED PRICE' , PL.amount
, PL.unit_price
),
note_to_vendor,
mum.uom_code,
pl.creation_date,
pl.created_by,
pl.last_update_date,
pl.last_updated_by,
'BLANKET',
min_release_amount,
price_break_lookup_code,
item_id,
msi.concatenated_segments,
item_revision,
l_po_num,
line_num, --original (non-renumbered) line num
po_header_id,
po_line_id,
PL.job_id, --
decode ( PL.order_type_lookup_code --
, 'FIXED PRICE' , PL.committed_amount
, 'RATE' , PL.committed_amount
, NULL),
PL.purchase_basis, --
pl.ip_category_id, --
-- Clin Slin Changes
pl.line_num_display,
pl.group_line_id,
pl.clm_info_flag,
pl.clm_option_indicator,
pl.clm_option_num,
pl.clm_option_from_date,
pl.clm_option_to_date,
pl.clm_funded_flag,
pl.clm_base_line_num,
pl.contract_type,
pl.cost_constraint,
pl.clm_idc_type
from po_lines pl, mtl_units_of_measure mum, mtl_system_items_kfv msi,
financials_system_parameters fsp --
where po_header_id=p_po_header_id and
mum.unit_of_measure (+) = pl.unit_meas_lookup_code and --
--bug #2716412: made pl/msi join an outer join
pl.item_id = msi.inventory_item_id(+) and
--
(pl.item_id IS NULL OR fsp.inventory_organization_id = msi.organization_id);
SELECT created_language into l_po_created_language
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
insert into pon_auc_shipments_interface(
interface_auction_header_id,
interface_line_number,
interface_ship_number,
shipment_type,
ship_to_organization_id,
ship_to_location_id,
quantity,
price,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by)
select l_interface_id,
--bug 2714549: get renumbered line#
paii.interface_line_number,
pll.shipment_num,
pll.shipment_type,
pll.ship_to_organization_id,
pll.ship_to_location_id,
pll.quantity,
pll.price_override,
pll.org_id,
pll.creation_date,
pll.created_by,
pll.last_update_date,
pll.last_updated_by
from po_line_locations pll,
-- bug 2714549: added paii to join; removed join to po_lines
INSERT INTO pon_price_differ_interface
( interface_auction_header_id
, interface_line_number
, interface_shipment_number
, interface_price_differ_number
, price_type
, multiplier
, process_status
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT l_interface_id
, POL.line_num
, -1 -- Insert -1 when shipment not present.
, PD.price_differential_num
, PD.price_type
, PD.min_multiplier
, NULL
, PD.creation_date
, PD.created_by
, PD.last_update_date
, PD.last_updated_by
, PD.last_update_login
FROM po_price_differentials PD
, po_lines_all POL
WHERE PD.entity_type = 'BLANKET LINE'
AND PD.entity_id = POL.po_line_id
AND POL.po_header_id = p_po_header_id;
INSERT INTO pon_price_differ_interface
( interface_auction_header_id
, interface_line_number
, interface_shipment_number
, interface_price_differ_number
, price_type
, multiplier
, process_status
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT l_interface_id
, POL.line_num
, POLL.shipment_num
, PD.price_differential_num
, PD.price_type
, PD.min_multiplier
, NULL
, PD.creation_date
, PD.created_by
, PD.last_update_date
, PD.last_updated_by
, PD.last_update_login
FROM po_price_differentials PD
, po_lines_all POL
, po_line_locations_all POLL
WHERE PD.entity_type = 'PRICE BREAK'
AND PD.entity_id = POLL.line_location_id
AND POLL.po_line_id = POL.po_line_id
AND POL.po_header_id = p_po_header_id;
insert into pon_attachments_interface(
interface_auction_header_id,
interface_line_number,
document_id,
seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by)
select l_interface_id,
NULL,
fad.document_id,
fad.seq_num,
fad.last_update_date,
fad.last_updated_by,
fad.creation_date,
fad.created_by
from fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdtl
where fad.document_id = fd.document_id AND
fd.document_id = fdtl.document_id AND
fdtl.language = userenv('LANG') AND
fad.entity_name = 'PO_HEADERS' AND
fad.pk1_value = to_char(p_po_header_id) AND
fd.category_id <> 39;
insert into pon_attachments_interface(
interface_auction_header_id,
interface_line_number,
document_id,
seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by)
select l_interface_id,
--bug 2714549: get renumbered line#
paii.interface_line_number,
fad.document_id,
fad.seq_num,
fad.last_update_date,
fad.last_updated_by,
fad.creation_date,
fad.created_by
from fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdtl,
--bug 2714549: replaced join to po_lines
-- with join to paii.
pon_auc_items_interface paii
where fad.document_id = fd.document_id AND
fd.document_id = fdtl.document_id AND
fdtl.language = userenv('LANG') AND
fad.entity_name = 'PO_LINES' AND
--bug 2714549 start: changed join conditions from po_lines
-- to paii and added auction_header cond
-- to ensure unique doc_id/line_id from paii
paii.source_doc_id = p_po_header_id AND
fad.pk1_value = to_char(paii.source_line_id) AND
paii.interface_auction_header_id = l_interface_id and
--bug 2714549 end
fd.category_id <> 39;
SELECT *
FROM po_price_differentials
WHERE entity_type = 'REQ LINE'
AND entity_id = p_req_line_id;
select org_id into x_org_id from pon_auction_headers_all where auction_header_id = p_auction_header_id;
select contract_type into l_contract_type from pon_auction_headers_all where auction_header_id = p_auction_header_id;
select uda_template_id into l_line_uda_temp_id from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id and rownum <2;
SELECT Nvl(reqs_in_pool_flag,'Y'),cancel_flag,closed_code
INTO x_req_in_pool_flag,
x_cancel_flag,
x_closed_code
FROM po_requisition_lines WHERE requisition_line_id=p_req_line_id
FOR UPDATE OF auction_header_id NOWAIT;
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = p_req_line_id;
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = p_req_line_id;
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = p_req_line_id;
SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = p_req_line_id;
DELETE FROM po_session_gt WHERE index_char1='PO_NEGOTIATIONS_SV1';
INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, date1)
SELECT prl.requisition_line_id,
'PO_NEGOTIATIONS_SV1',
prh.segment1,
prl.line_num,
prl.item_id,
prl.category_id,
decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
FROM po_requisition_lines_all prl,
po_requisition_headers_all prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = p_req_line_id;
l_string := ' select rl.requisition_line_id,
Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,rl.LINE_TYPE_ID), -- line_type_id
rl.ITEM_ID, -- item_id
rl.ITEM_REVISION, -- item_revision
rl.CATEGORY_ID, -- category id
rl.QUANTITY, -- quantity
rl.UNIT_MEAS_LOOKUP_CODE, -- unit meas lookup code
rl.JOB_ID, -- job id
rl.requisition_header_id,
psg.char1,
Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,RL.UNIT_MEAS_LOOKUP_CODE), --
rl.note_to_vendor,
rl.need_by_date,
RL.amount, --
msi.concatenated_segments,
rl.deliver_to_location_id,
rl.unit_price,
rl.item_description,
PJ.name, --
RL.job_long_description, --
RL.org_id, --
-- added for CLIN SLIN Project
rl.LINE_NUM_DISPLAY,
rl.GROUP_LINE_ID,
rl.CLM_INFO_FLAG,
rl.CLM_OPTION_INDICATOR,
rl.CLM_OPTION_NUM,
rl.CLM_OPTION_FROM_DATE,
rl.CLM_OPTION_TO_DATE,
rl.CLM_FUNDED_FLAG,
rl.CLM_BASE_LINE_NUM,
rl.CONTRACT_TYPE,
rl.COST_CONSTRAINT,
decode(rl.CONTRACT_TYPE, null, null, ''IDC_NA''),
rl.UDA_TEMPLATE_ID --
from po_requisition_lines_all rl, --
mtl_system_items_kfv msi,
per_jobs_vl PJ,
PO_SESSION_GT psg --
where requisition_line_id = psg.key --
and rl.item_id = msi.inventory_item_id(+)
and coalesce(msi.organization_id, rl.destination_organization_id,-1) =
nvl(rl.destination_organization_id,-1)
AND RL.job_id = PJ.job_id(+) --
AND psg.index_char1 = ''PO_NEGOTIATIONS_SV1''' ;
SELECT nvl(ship_to_location_id,location_id)
INTO x_ship_to_location_id
FROM hr_locations
WHERE location_id = x_deliver_to_location_id;
select mum.uom_code
into x_uom_code
from mtl_units_of_measure mum
where mum.unit_of_measure = x_uom; --bug 5841426
update po_requisition_lines_all --
set on_rfq_flag = 'Y',
auction_header_id = p_auction_header_id,
auction_display_number = to_char(p_auction_header_id) ,
auction_line_number = x_negotiation_line_num,
at_sourcing_flag = 'Y', --
reqs_in_pool_flag = NULL, --
last_update_date = sysdate,
last_updated_by = to_number(FND_PROFILE.VALUE('user_id')),
last_update_login = to_number(FND_PROFILE.VALUE('user_id'))
where requisition_line_id = x_req_line_id ;
SELECT document_number INTO x_document_number FROM pon_auction_headers_all WHERE auction_header_id=p_auction_header_id;
update po_requisition_lines_all
SET auction_display_number = x_document_number
where requisition_line_id = x_req_line_id ;
STEP 4) Delete Requesting Office, Suggested COTR Office, and Suggested Property Administration Office addresses
from UDA tables (PON_AUCTION_HEADERS_EXT_B, PON_AUCTION_HEADERS_EXT_TL) because these addresses should
come from Requisition.
STEP 5) If the Issuing Office copied from User Preferences has null data then delete that record, else take the
backup of the record by updating it to ISSUING_OFFICE_BACKUP.
STEP 6) Call PON_COPY_UDAS_GRP.COPY_REQ_HEADER_UDA procedure to copy addresses (Requesting Office,
Suggested COTR Office, Suggested Property Administration Office, and Issuing Office) and
Business Priority and Project Information UDA from Requisition to Solicitation.
STEP 7) After Step 5, if the data copied for Issuing Office is valid, then delete the records related to Issuing
Office which were copied in STEP 6 i.e., Issuing Office Copied from Requisition to Solicitaion. Also update
the record modified in STEP 5 back to ISSUING_OFFICE.
STEP 8) After Step 5, if the data copied for Issuing Office is not valid, then don't perform any logic
i.e., Issuing Office Copied from Requisition to Solicitaion should be kept as is.
*/
PROCEDURE COPY_REQ_UDA_TO_SOL(p_req_lines IN PO_TBL_NUMBER,
p_auction_header_id IN NUMBER)
IS
l_req_line_id NUMBER;
SELECT EXTENSION_ID
FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 IN ('PRO_ADMIN_OFFICE','COTR_OFFICE','REQ_OFFICE') AND
AUCTION_HEADER_ID = auc_hdr_id;
SELECT requisition_header_id INTO l_req_header_id FROM po_requisition_lines_all WHERE requisition_line_id = l_req_line_id;
SELECT uda_template_id INTO l_sol_uda_temp_id FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
SELECT uda_template_id INTO l_req_uda_temp_id FROM po_requisition_headers_all WHERE REQUISITION_HEADER_ID= l_req_header_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 IN ('PRO_ADMIN_OFFICE','COTR_OFFICE','REQ_OFFICE') AND
AUCTION_HEADER_ID = p_auction_header_id;
SELECT Count(*) INTO l_user_pref_addr_cnt FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
SELECT EXTENSION_ID, C_EXT_ATTR5 INTO l_iss_off_extn_id, l_address_val FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_EXT_B SET C_EXT_ATTR1='ISSUING_OFFICE_BACKUP' WHERE EXTENSION_ID = l_iss_off_extn_id;
SELECT EXTENSION_ID INTO l_extn_id FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_B WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
fnd_log.string(fnd_log.level_statement,g_log_head,'Deleted issuing office record copied from user preferences.');
SELECT EXTENSION_ID INTO l_extn_id FROM PON_AUCTION_HEADERS_EXT_B
WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_B WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
UPDATE PON_AUCTION_HEADERS_EXT_B SET C_EXT_ATTR1='ISSUING_OFFICE' WHERE EXTENSION_ID = l_iss_off_extn_id;
UPDATE po_requisition_lines_all
SET auction_header_id = p_auction_header_id,
auction_line_number = -1,
auction_display_number = -1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_line_id = p_requisition_line_id(i)
AND auction_header_id IS NULL
returning requisition_header_id INTO l_requisition_header_id;
FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
UPDATE po_requisition_lines_all
SET auction_header_id = p_auction_header_id,
auction_line_number = -1,
auction_display_number = -1,
bid_number = -1,
bid_line_number = -1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_line_id = p_requisition_line_id(i)
AND auction_header_id IS NULL
returning requisition_header_id INTO l_requisition_header_id;
FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
--looping thru the line_id array and updating the reqs_in_pool_flag and at_sourcing_flag for the entire structure(including slins and options) of the selected line
--removing the slins and options of all the selected clins from the requisition pool
UPDATE po_requisition_lines_all
SET reqs_in_pool_flag = null,
at_sourcing_flag = 'Y',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_header_id = l_requisition_header_id
AND Nvl(reqs_in_pool_flag, 'N') = 'Y'
AND ((requisition_line_id = p_requisition_line_id(i))
OR (group_line_id = p_requisition_line_id(i))
OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num = p_requisition_line_id(i))
OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id )
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)));
FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Update the selected requisitions reqs_in_pool_flag and at_sourcing_flag for the selected lines');
l_req_line_to_update number_table;
SELECT requisition_header_id, Decode(Nvl(group_line_id, -999), -999, 'Y','N') INTO l_requisition_header_id, l_priced_clin_YN
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i);
UPDATE po_requisition_lines_all prla SET at_sourcing_flag = NULL,
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 = l_requisition_header_id
AND ((requisition_line_id = p_req_line_ids(i) )
OR (group_line_id = p_req_line_ids(i))
OR (clm_base_line_num = p_req_line_ids(i))
OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)));
SELECT requisition_line_id INTO l_req_id
FROM po_requisition_lines_all
WHERE requisition_header_id = l_requisition_header_id
AND ((requisition_line_id = p_req_line_ids(i) )
OR (requisition_line_id = (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num = p_req_line_ids(i))
OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)))
AND auction_header_id IS NOT NULL ;
UPDATE po_requisition_lines_all prla SET at_sourcing_flag = NULL,
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 = l_requisition_header_id
AND ((requisition_line_id = p_req_line_ids(i) )
OR (requisition_line_id = (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num = p_req_line_ids(i))
OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id))
OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id )
AND requisition_header_id = l_requisition_header_id))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_ids(i)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)
AND requisition_header_id = l_requisition_header_id)) );
PROCEDURE update_sol_ref_delete_all(p_auction_header_id IN NUMBER,
p_delete_pbr_yn IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_req_line_id_csr IS
SELECT DISTINCT(requisition_line_id)
FROM pon_backing_requisitions
WHERE auction_header_id = p_auction_header_id;
IF p_delete_pbr_yn = 'Y' THEN
DELETE FROM pon_backing_requisitions WHERE auction_header_id = p_auction_header_id;
update po_requisition_lines_all prla
set auction_header_id = null,
auction_display_number = null,
auction_line_number = null,
at_sourcing_flag = null,
on_rfq_flag = null,
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 auction_header_id = p_auction_header_id;
END update_sol_ref_delete_all;
PROCEDURE update_sol_ref_delete_line(p_auction_header_id IN NUMBER,
p_auction_line_number IN NUMBER,
p_bid_number IN NUMBER DEFAULT NULL,
p_bid_line_number IN NUMBER DEFAULT NULL,
p_requisition_line_id IN NUMBER DEFAULT NULL,
p_table_name IN VARCHAR2 DEFAULT 'PON_BACKING_REQUISITIONS',
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_req_line_id_csr IS
SELECT requisition_line_id
FROM pon_backing_requisitions
WHERE auction_header_id = p_auction_header_id
AND EXISTS ( SELECT line_number
FROM pon_auction_item_prices_ALL
WHERE auction_header_id = p_auction_header_id
AND (line_number = p_auction_line_number
OR group_line_id = p_auction_line_number))
GROUP BY requisition_line_id;
DELETE FROM pon_backing_requisitions
WHERE auction_header_id = p_auction_header_id
AND line_number = p_auction_line_number
AND requisition_line_id = p_requisition_line_id;
DELETE FROM pon_bid_backing_requisitions
WHERE auction_header_id = p_auction_header_id
AND auction_line_number = p_auction_line_number
and bid_number = p_bid_number
and bid_line_number = p_bid_line_number
AND requisition_line_id = p_requisition_line_id;
DELETE FROM pon_backing_requisitions
WHERE auction_header_id = p_auction_header_id
AND line_number = p_auction_line_number;
/*IF the deleted req line id is not referred anywhere other than in this line for the
current auction_header_id then update sol ref in po_requisition_lines_all*/
IF (p_table_name = 'PON_BACKING_REQUISITIONS') THEN
UPDATE po_requisition_lines_all prl SET auction_header_id = NULL,
auction_line_number = NULL,
auction_display_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE auction_header_id = p_auction_header_id
AND NOT EXISTS (SELECT requisition_line_id
FROM pon_backing_requisitions pbr
WHERE auction_header_id = p_auction_header_id
AND prl.requisition_line_id = pbr.requisition_line_id
GROUP BY requisition_line_id )
RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids;
UPDATE po_requisition_lines_all prl SET auction_header_id = NULL,
auction_line_number = NULL,
auction_display_number = NULL,
bid_number = NULL,
bid_line_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE auction_header_id = p_auction_header_id
AND NOT EXISTS (SELECT requisition_line_id
FROM pon_bid_backing_requisitions pbr
WHERE auction_header_id = p_auction_header_id
AND prl.requisition_line_id = pbr.requisition_line_id
GROUP BY requisition_line_id )
RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids;
END update_sol_ref_delete_line;
PROCEDURE update_sol_ref_amend(p_old_auction_id IN NUMBER,
p_new_auction_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2)
IS
CURSOR c_deleted_pr_ref_csr IS
SELECT DISTINCT(requisition_line_id)
FROM pon_backing_requisitions
WHERE auction_header_id = p_old_auction_id
MINUS (SELECT requisition_line_id
FROM pon_backing_requisitions
WHERE auction_header_id = p_new_auction_id);
OPEN c_deleted_pr_ref_csr;
FETCH c_deleted_pr_ref_csr BULK COLLECT INTO l_req_line_ids;
CLOSE c_deleted_pr_ref_csr;
/*Update ref of auction in po_requisition_lines_all table only for those lines copied to the new auction*/
UPDATE po_requisition_lines_all prl SET auction_header_id = p_new_auction_id
WHERE EXISTS (SELECT DISTINCT(requisition_line_id)
FROM pon_backing_requisitions pbr
WHERE auction_header_id = p_new_auction_id
AND prl.requisition_line_id = pbr.requisition_line_id);
/* For deleted PR references remove sol references in po_requisition_lines_all*/
UPDATE po_requisition_lines_all SET auction_header_id = NULL,
auction_line_number = NULL,
auction_display_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE EXISTS (SELECT DISTINCT(requisition_line_id)
FROM pon_backing_requisitions
WHERE auction_header_id = p_old_auction_id
MINUS (SELECT requisition_line_id
FROM pon_backing_requisitions
WHERE auction_header_id = p_new_auction_id))
RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids ;
END update_sol_ref_amend;