[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fsp.inventory_organization_id
into x_inventory_organization_id
from financials_system_parameters fsp;
SELECT inventory_organization_id
INTO l_ship_to_organization_id
FROM hr_locations_all
WHERE location_id = x_ship_to_location_id
AND ship_to_site_flag = 'Y';
SELECT expense_accrual_code
INTO x_expense_accrual_code
FROM po_system_parameters;
select msi.receipt_required_flag
into x_receipt_required_flag
from mtl_system_items msi
where msi.inventory_item_id = x_item_id
and msi.organization_id = l_ship_to_organization_id;
select msi.receipt_required_flag
into x_receipt_required_flag
from mtl_system_items msi
where msi.inventory_item_id = x_item_id
and msi.organization_id = x_inventory_organization_id;
select plt.receiving_flag
into x_receipt_required_flag
from po_line_types plt
where plt.line_type_id=x_line_type_id;
select pov.receipt_required_flag
into x_receipt_required_flag
from po_vendors pov
where pov.vendor_id=x_vendor_id;
select psp.receiving_flag
into x_receipt_required_flag
from po_system_parameters psp;
Insert into po_distributions_interface table.
*******************************************************************/
INSERT INTO po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
charge_account_id,
set_of_books_id,
quantity_ordered,
amount_ordered, --
rate,
rate_date,
req_distribution_id,
deliver_to_location_id,
deliver_to_person_id,
encumbered_flag,
gl_encumbered_date,
gl_encumbered_period_name,
destination_type_code,
destination_organization_id,
destination_subinventory,
budget_account_id,
accrual_account_id,
variance_account_id,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
prevent_encumbrance_flag,
project_id,
task_id,
end_item_unit_number,
expenditure_type,
project_accounting_context,
destination_context,
expenditure_organization_id,
expenditure_item_date
)
values(x_interface_header_id,
x_interface_line_id,
po_distributions_interface_s.nextval,
1, --prd.distribution_num,
x_charge_account_id, --prd.code_combination_id,
x_def_sob_id, --prd.set_of_books_id,
x_quantity,
x_amount, --
x_rate,
x_rate_date,
null, --prd.distribution_id, no ref to a req.
x_ship_to_location_id,--x_destination_locatin_id
x_deliver_to_person_id,
x_po_encumbrance_flag, --prd.encumbered_flag,
l_gl_encumbered_date,
l_gl_encumbered_period,
x_destination_type_code,
l_ship_to_organization_id, --prl.destination_organization_id,
l_destination_subinventory,
x_budget_account_id,
x_accrual_account_id,
x_variance_account_id,
x_wip_entity_id,
x_wip_line_id,
x_wip_repetitive_schedule_id,
x_wip_operation_seq_num,
x_wip_resource_seq_num,
x_bom_resource_id,
x_prevent_encumbrance_flag,
x_project_id,
x_task_id,
x_end_item_unit_number,
x_expenditure_type,
x_project_accounting_context,
x_destination_context,
x_expenditure_organization_id,
x_expenditure_item_date
);
X_last_update_login IN NUMBER DEFAULT NULL)
IS
x_progress varchar2(4);
x_last_update_login,
null,
null,
null,
'NEG');
x_last_update_login,
'',
'',
null,
'NEG');
x_last_update_login,
'',
'',
null,
'NEG');
x_last_update_login,
'',
'',
null,
'NEG');
X_last_update_login ,
null,
null,
null);
X_last_update_login ,
null,
null,
null);
X_last_update_login ,
null,
null,
null);
X_last_update_login ,
null,
null,
null);
, x_last_update_login => x_last_update_login
, x_program_application_id => NULL
, x_program_id => NULL
, x_request_id => NULL
);
x_last_update_login,
'',
'',
null,
x_column1);
x_last_update_login,
'',
'',
null,
x_column1);
X_last_update_login IN NUMBER DEFAULT NULL,
X_program_application_id IN NUMBER DEFAULT NULL,
X_program_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL,
X_column1 IN VARCHAR2 DEFAULT NULL) IS
/*
Bug 5938614 : UNABLE TO CREATE STANDARD PO FROM SOURCING RFQ WHEN MULTIPLE REQUISITIONS USED ,
This is because when we create a sourcing RFQ that combines 2 req lines from 2 different requisitions
which are having the one time attachement , and if we publish a negotiation then it is inserting 2 lines
into fnd_attached_documents with pk1_value as negotiation number,and pk2_value
as requisition line number.
Before this fix , At the time of PO Creation ,the one time address is being copied from
entity type 'PON_AUCTION_ITEM_PRICES_ALL' which is causing the problem.
For a single req line it is inserting two rows into fnd_attached_documents because
the below cursor returning two rows while selecting from entity type 'PON_AUCTION_ITEM_PRICES_ALL'.
Modified the query so that the one time attachments will be copied from entity type 'REQ_LINES' .
*/
CURSOR doclist IS
SELECT fad.seq_num, fad.document_id,
fad.attribute_category, fad.attribute1, fad.attribute2,
fad.attribute3, fad.attribute4, fad.attribute5,
fad.attribute6, fad.attribute7, fad.attribute8,
fad.attribute9, fad.attribute10, fad.attribute11,
fad.attribute12, fad.attribute13, fad.attribute14,
fad.attribute15, fad.column1, fad.automatically_added_flag,
fdvl.datatype_id, fdvl.category_id, fdvl.security_type, fdvl.security_id,
fdvl.publish_flag, fdvl.image_type, fdvl.storage_type,
fdvl.usage_type, fdvl.start_date_active, fdvl.end_date_active,
userenv('LANG') language, fdvl.description, fdvl.file_name,
fdvl.media_id, --bug 4620207: get media_id from fd table
fdvl.doc_attribute_category dattr_cat,
fdvl.doc_attribute1 dattr1, fdvl.doc_attribute2 dattr2,
fdvl.doc_attribute3 dattr3, fdvl.doc_attribute4 dattr4,
fdvl.doc_attribute5 dattr5, fdvl.doc_attribute6 dattr6,
fdvl.doc_attribute7 dattr7, fdvl.doc_attribute8 dattr8,
fdvl.doc_attribute9 dattr9, fdvl.doc_attribute10 dattr10,
fdvl.doc_attribute11 dattr11, fdvl.doc_attribute12 dattr12,
fdvl.doc_attribute13 dattr13, fdvl.doc_attribute14 dattr14,
fdvl.doc_attribute15 dattr15,
fdvl.title, fdvl.url -- Bug 5000065
FROM fnd_attached_documents fad,
fnd_documents_vl fdvl
WHERE fad.document_id = fdvl.document_id
AND fad.entity_name = X_from_entity_name
AND fad.pk1_value = X_from_pk1_value
AND (X_from_pk2_value IS NULL
OR fad.pk2_value = X_from_pk2_value)
AND (X_from_pk3_value IS NULL
OR fad.pk3_value = X_from_pk3_value)
AND (X_from_pk4_value IS NULL
OR fad.pk4_value = X_from_pk4_value)
AND (X_from_pk5_value IS NULL
OR fad.pk5_value = X_from_pk5_value)
--5938614
AND ((X_column1 = 'NEGREQ' and (nvl(fdvl.category_id,-99) <> g_cat_TO_SUPPLIER or fdvl.description LIKE 'POR:%'))
or (X_column1='NEG' AND fdvl.description NOT LIKE 'POR:%'))
--5938614
AND ((X_column1 = 'NEGREQ')
or
((X_column1='NEG') and
nvl(fad.column1,'NOVAL') <> 'MTL_SYSTEM_ITEMS'
));
SELECT short_text
FROM fnd_documents_short_text
WHERE media_id = mid;
SELECT long_text
FROM fnd_documents_long_text
WHERE media_id = mid;
SELECT file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format
FROM fnd_lobs
WHERE file_id = mid;
select category_id
into l_intern_sourcing_cat_id
from fnd_document_categories
where name='InternaltoSourcing';
FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
document_id_tmp,
SYSDATE,
NVL(X_created_by,0),
SYSDATE,
NVL(X_created_by,0),
X_last_update_login,
docrec.datatype_id,
-- docrec.category_id,
x_category_id_tmp,
docrec.security_type,
docrec.security_id,
docrec.publish_flag,
docrec.image_type,
docrec.storage_type,
docrec.usage_type,
docrec.start_date_active,
docrec.end_date_active,
X_request_id,
X_program_application_id,
X_program_id,
SYSDATE,
x_language_temp, --docrec.language,
docrec.description,--x_description_tmp
docrec.file_name,
media_id_tmp,
docrec.dattr_cat, docrec.dattr1,
docrec.dattr2, docrec.dattr3,
docrec.dattr4, docrec.dattr5,
docrec.dattr6, docrec.dattr7,
docrec.dattr8, docrec.dattr9,
docrec.dattr10, docrec.dattr11,
docrec.dattr12, docrec.dattr13,
docrec.dattr14, docrec.dattr15,
-- Bug 5000065 START
-- Copy the URL/title (for web page attachments)
'N', -- x_create_doc
docrec.url,
docrec.title
-- Bug 5000065 END
);
-- cursor for later insert into
-- fnd_attached_documents
docrec.document_id := document_id_tmp;
INSERT INTO fnd_documents_short_text (
media_id,
short_text)
VALUES (
media_id_tmp,
short_text_tmp);
INSERT INTO fnd_documents_long_text (
media_id,
long_text)
VALUES (
media_id_tmp,
long_text_tmp);
INSERT INTO fnd_lobs (
file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format)
VALUES (
media_id_tmp,
fnd_lobs_rec.file_name,
fnd_lobs_rec.file_content_type,
fnd_lobs_rec.upload_date,
fnd_lobs_rec.expiration_date,
fnd_lobs_rec.program_name,
fnd_lobs_rec.program_tag,
fnd_lobs_rec.file_data,
fnd_lobs_rec.language,
fnd_lobs_rec.oracle_charset,
fnd_lobs_rec.file_format);
INSERT INTO fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value, pk2_value, pk3_value,
pk4_value, pk5_value,
automatically_added_flag,
program_application_id, program_id,
program_update_date, request_id,
attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, column1) VALUES
(fnd_attached_documents_s.nextval,
docrec.document_id,
sysdate,
NVL(X_created_by,0),
sysdate,
NVL(X_created_by,0),
X_last_update_login,
docrec.seq_num,
X_to_entity_name,
X_to_pk1_value, X_to_pk2_value, X_to_pk3_value,
X_to_pk4_value, X_to_pk5_value,
docrec.automatically_added_flag,
X_program_application_id, X_program_id,
sysdate, X_request_id,
docrec.attribute_category, docrec.attribute1,
docrec.attribute2, docrec.attribute3,
docrec.attribute4, docrec.attribute5,
docrec.attribute6, docrec.attribute7,
docrec.attribute8, docrec.attribute9,
docrec.attribute10, docrec.attribute11,
docrec.attribute12, docrec.attribute13,
docrec.attribute14, docrec.attribute15,
docrec.column1);
-- Update the document to be a std document if it
-- was an ole or image that wasn't already a std doc
-- (images should be created as Std, but just in case)
IF (docrec.datatype_id IN (3,4)
AND docrec.usage_type <> 'S') THEN
UPDATE fnd_documents
SET usage_type = 'S'
WHERE document_id = docrec.document_id;
, x_last_update_login IN NUMBER DEFAULT NULL
, x_program_application_id IN NUMBER DEFAULT NULL
, x_program_id IN NUMBER DEFAULT NULL
, x_request_id IN NUMBER DEFAULT NULL
, p_auction_payment_id IN NUMBER DEFAULT NULL --
)
IS
media_id_tmp NUMBER;
l_who_rec.last_update_date := sysdate;
l_who_rec.last_updated_by := nvl(x_created_by, 0);
l_who_rec.last_update_login := x_last_update_login;
FND_DOCUMENTS_PKG.insert_row
( x_rowid => l_rowid -- IN/OUT
, x_document_id => l_document_id -- IN/OUT
, x_creation_date => nvl(p_who_rec.creation_date, sysdate)
, x_created_by => nvl(p_who_rec.created_by, 0)
, x_last_update_date => nvl(p_who_rec.last_update_date, sysdate)
, x_last_updated_by => nvl(p_who_rec.last_updated_by, 0)
, x_last_update_login => nvl(p_who_rec.last_update_login, 0)
, x_datatype_id => 2
, x_category_id => p_category_id
, x_security_type => 1
, x_security_id => l_security_id
, x_publish_flag => 'Y'
, x_usage_type => 'O'
, x_program_update_date => sysdate
, x_language => userenv('LANG')
, x_description => p_description
, x_media_id => l_media_id -- IN/OUT
);
INSERT INTO fnd_documents_long_text
( media_id
, long_text
)
VALUES
( l_media_id
, p_long_text
);
SELECT max(seq_num)
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = p_to_pk1_value
AND entity_name = p_to_entity_name;
INSERT INTO fnd_attached_documents
( attached_document_id
, document_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, seq_num
, entity_name
, pk1_value
, automatically_added_flag
, program_update_date
)
VALUES
( FND_ATTACHED_DOCUMENTS_S.nextval
, l_document_id
, nvl(p_who_rec.creation_date, sysdate)
, nvl(p_who_rec.created_by, 0)
, nvl(p_who_rec.last_update_date, sysdate)
, nvl(p_who_rec.last_updated_by, 0)
, nvl(p_who_rec.last_update_login, 0)
, l_seq_num
, p_to_entity_name
, p_to_pk1_value
, 'N'
, sysdate
);
, p_last_update_login IN NUMBER
, p_auction_header_id IN NUMBER
, p_auction_line_number IN NUMBER
, p_bid_number IN NUMBER
, p_bid_line_number IN NUMBER
)
IS
d_progress NUMBER;
PO_LOG.proc_begin(d_module, 'p_last_update_login', p_last_update_login);
SELECT polli.bid_payment_id, polli.auction_payment_id
INTO l_bid_payment_id, l_auction_payment_id
FROM po_line_locations_interface polli
WHERE polli.line_location_id = p_line_location_id;
, X_last_update_login => p_last_update_login
, X_column1 => 'NEG'
);
, x_last_update_login => p_last_update_login
, x_program_id => NULL
, x_request_id => NULL
);
, X_last_update_login => p_last_update_login
, X_column1 => 'NEG'
);