[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. */
x_result:=-1;
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);
l_string := ' select rl.requisition_line_id,
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,
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 --
/*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 nvl(msi.organization_id, rl.destination_organization_id) =
rl.destination_organization_id
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
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) --
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 --
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 = 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 = paii.source_line_id AND
paii.interface_auction_header_id = l_interface_id and
--bug 2714549 end
fd.category_id <> 39;