Home » Applications » Oracle Fusion Apps & E-Business Suite » upload attachments
| upload attachments [message #151173] |
Tue, 13 December 2005 09:43  |
praksh
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
I need to upload attachemnts into oracle applications 11.5.10.
Can someone please help me by giving an idea how to proceed( or send me any related code)
Thanks in advance
|
|
|
|
|
|
| Re: upload attachments [message #151186 is a reply to message #151183] |
Tue, 13 December 2005 10:54  |
praksh
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
I need to upload attachemnts in oracle sales module.
I searched thru metalink,they used a utility names FNDGFU to upload attachemtns and then FND_DOCUMENTS .pkg to link them but it is mentioned that we need to write a custom procedure for this.I want to get some idea about this what the procedure shud contain?
|
|
|
|
|
|
| Re: upload attachments [message #151264 is a reply to message #151173] |
Wed, 14 December 2005 00:43 |
manwadkar
Messages: 104
Registered: September 2005
Location: Washington DC
|
Senior Member |
|
|
YOur code will look like below. It is not complete.
DECLARE
l_doc_category_id NUMBER;
l_document_id NUMBER;
l_attached_document_id NUMBER;
ll_media_id NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
BEGIN
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name ='ACHADDA';
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE name ='SHORT_TEXT';
-- Select Category id for "Vendor/To Supplier" Attachments
SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories
WHERE name = 'Vendor';
-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;
INSERT INTO apps.fnd_documents
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
datatype_id,
category_id,
security_type,
security_id,
publish_flag,
usage_type
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT'
l_doc_category_id, -- Category_id
1, -- 'Organization' Level Security
352, -- Organization id for Inventory Item Master Org
'Y', -- Publish_flag
'O' -- Usage_type of 'One Time'
);
INSERT INTO apps.fnd_documents_tl
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
language,
description,
media_id,
translated
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
'AMERICAN', -- language
'EXTENDED DESCRIPTION', -- description
l_media_id, -- media_id
'Y' -- translated
);
INSERT INTO apps.fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
seq_num,
entity_name,
pk1_value,
pk2_value,
automatically_added_flag
)
VALUES
(l_attached_document_id,
l_document_id,
SYSDATE,
lcl_fnd_user_id,
SYSDATE,
lcl_fnd_user_id,
20, -- Sequence Number of attachment.
'MTS_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment
352, -- Organization id for Inventory Item Master Org
567, -- Inventory Item Id
'N' -- Automatically_added_flag
);
INSERT INTO apps.fnd_documents_short_text
(media_id,
short_text
)
VALUES
(lcl_media_id,
'Write your Short Text Here' -- Notes/Attachments text
);
COMMIT;
END;
/
|
|
|
|
| Re: upload attachments [message #153161 is a reply to message #151264] |
Thu, 29 December 2005 10:48 |
praksh
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
Hi,
can someone send me a example command to be used for FNDGFU utility(used for uploading attachments) with all the parameters specified.
Thanks in advance
|
|
|
|
| Re: upload attachments [message #153376 is a reply to message #153161] |
Sun, 01 January 2006 21:58 |
manwadkar
Messages: 104
Registered: September 2005
Location: Washington DC
|
Senior Member |
|
|
Loading Attachment Files into the Database
During An Upgrade to Release 11i There are a few additional steps that need to be performed to load attachment files into the
database during an upgrade from 10.7 NCA or 11.0.3 to Release 11i. The steps are as follows:
1. Change to the directory where your attachment files exist from 10.7NCA or 11.0.3
2. Type the following command on one line replacing ‘apps/apps@ist10’ with your database specific connection text:
FNDGFU apps/apps@ist10 0 Y UPLOAD PROGRAM_NAME=FND_ATTACH
PLS_CALLBACK=FND_ATTACHMENT_UTIL_PKG.UPDATE_FILE_METADATA *.*
3. It is important to note that the end of the command is ‘*.*’
4. You should see the following and this is not an error:
Log filename: L1570349.log
Report filename: O1570349.out
UPLOAD: could not open file
PROGRAM_NAME=FND_ATTACH: could not open file
PLS_CALLBACK=FND_ATTACHMENT_UTIL_PKG.UPDATE_FILE_METADATA: could not open file
5. You should then see successful uploads:
A20063.doc: upload succeeded (file id=2459)
A20064.doc: upload succeeded (file id=2460)
A20065.xls: upload succeeded (file id=2461)
A20069.doc: upload succeeded (file id=2462)
6. Move the files already uploaded out of that directory if the following errors occur such as:
ORA-01480: trailing null missing from STR bind value
ORA-01036: illegal variable name/number
ORA-01036: illegal variable name/number
Bus Error
7. You may also have to move the next file to be uploaded out of that directory since that file may be the cause of the error
8. Restart the FNDGFU command exactly like Step 2 above until all attachment files are uploaded
9. Verify that you can see these uploaded attachments from within your 11i Application
10. If you are not able to see them then run the following sql as the apps user (it may run for a while if you have a large number of attachments):
-------------------- this is the beginning of the sql code -------------------------------------
set serverout on
declare
CURSOR c_lobs IS
SELECT file_id, file_name FROM fnd_lobs
WHERE (file_name like 'A%');
v_lobs c_lobs%ROWTYPE;
v_file_name fnd_lobs.file_name%TYPE;
CURSOR c_documents(c_file_name fnd_lobs.file_name%TYPE) IS
SELECT document_id,file_name
FROM fnd_documents_tl
WHERE file_name like '%'||c_file_name||'%';
v_documents c_documents%ROWTYPE;
doc_count number := 0;
lob_count number := 0;
EGIN
dbms_output.enable;
FOR v_lobs IN c_lobs
LOOP
v_file_name := substr(v_lobs.file_name,instr(v_lobs.file_name,'A',-1));
-- dbms_output.put_line('File Id'||v_lobs.file_id);
-- dbms_output.put_line(v_file_name);
FOR v_documents IN c_documents(v_file_name)
LOOP
-- dbms_output.put_line('Document Id'||v_documents.document_id);
-- dbms_output.put_line(v_documents.file_name);
BEGIN
UPDATE fnd_documents_tl
SET file_name = 'INTERNAL',
media_id = v_lobs.file_id
WHERE document_id = v_documents.document_id;
doc_count := doc_count + 1;
EXCEPTION
WHEN others THEN
NULL;
END;
BEGIN
UPDATE fnd_lobs
SET program_name = 'FNDATTCH'
WHERE file_id = v_lobs.file_id;
lob_count := lob_count + 1;
EXCEPTION
WHEN others THEN
NULL;
END;
END LOOP;
END LOOP;
COMMIT;
dbms_output.put_line('Total Records Update in fnd_documents_tl Table::'||doc_count);
dbms_output.put_line('Total Records Update in fnd_lobs Table::'||lob_count);
EXCEPTION
WHEN others THEN
dbms_output.put_line('Program Failed in the Main Block');
END;
/
------------------------- this is the end of the sql code -------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Mon May 06 11:53:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
