Use
the Below Prgram for Document Upload.
The
primary Key value(l_pk1_value)is
the vaule attached to the title of the Document for each Employee.
DECLARE
l_rowid
ROWID;
l_attached_document_id
NUMBER;
l_document_id
NUMBER;
l_media_id
NUMBER;
l_category_id
NUMBER;
l_pk1_value
number:=40582;---apps.fnd_attached_documents.pk1_value%TYPE;--:=;
l_description
VARCHAR2(240):='Test Attachment';
l_filename
VARCHAR2(240) ;--:= 'Sankara.pdf';
l_seq_num
NUMBER;
l_blob_data
BLOB;
l_blob
BLOB;
l_bfile
BFILE;
l_byte
NUMBER;
l_fnd_user_id
NUMBER;
l_short_datatype_id
NUMBER;
x_blob
BLOB;
fils BFILE;
blob_length
INTEGER;
l_entity_name
VARCHAR2(100) := 'R_DOCUMENT_EXTRA_INFO';
l_category_name
VARCHAR2(100) := 'Documents of Record';
BEGIN
--fnd_global.apps_initialize
(4659, 50773,800);
SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;
SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value =40582 AND entity_name = l_entity_name;
--
Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE employee_id=81399;--83269;--user_name = ;
--
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 = 'FILE';
--
Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_TL
WHERE USER_NAME ='Documents of Record';-- l_category_name;
--
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_long_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id
--l_media_id
FROM DUAL;
SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;
fils := BFILENAME ( 'XXHR_FND_BLOBS','Sankara
Rao Dasari- Employment Visa.pdf');
--
Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length
:= DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);
--
Insert a new record into the table containing the
--
filename you have specified and a LOB LOCATOR.
--
Return the LOB LOCATOR and assign it to x_blob.
/*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
(l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf
SYSDATE,
NULL,
'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,
'US',
'UTF8', 'binary'
)
RETURNING
file_data
INTO
x_blob;*/
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 (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf
SYSDATE,
NULL, 'FNDATTCH', NULL,EMPTY_BLOB (), --l_blob_data,
NULL, 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;
--
Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
--
Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);
DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);
COMMIT;
--
This package allows user to share file across multiple orgs or restrict to
single org
fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id
=> l_document_id,
x_creation_date
=> SYSDATE,
x_created_by
=> l_fnd_user_id,--
fnd_profile.value('USER_ID')
x_last_update_date
=> SYSDATE,
x_last_updated_by
=> l_fnd_user_id,--
fnd_profile.value('USER_ID')
x_last_update_login
=> fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id
=> l_short_datatype_id, -- FILE
X_security_id
=>NULL,
x_publish_flag
=> 'N', --This flag allow the file to share across multiple organization
x_category_id
=> l_category_id,
x_security_type
=> 1,
x_usage_type
=> 'S',
x_language
=> 'US',
x_description
=> l_filename,--l_description,
x_file_name
=> l_filename,
x_media_id
=> l_media_id
);
commit;
fnd_documents_pkg.insert_tl_row
(x_document_id =>
l_document_id,
x_creation_date
=> SYSDATE,
x_created_by
=> l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date
=> SYSDATE,
x_last_updated_by
=> l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login
=> fnd_profile.VALUE('LOGIN_ID'),
x_language
=> 'US',
x_description
=> l_filename--l_description
);
COMMIT;
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id
=> l_attached_document_id,
x_document_id
=> l_document_id,
x_creation_date
=> SYSDATE,
x_created_by
=> l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date
=> SYSDATE,
x_last_updated_by
=> l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login
=> fnd_profile.VALUE('LOGIN_ID'),
x_seq_num
=> l_seq_num,
x_entity_name
=> l_entity_name,
x_column1
=> NULL,
x_pk1_value
=> l_pk1_value,
x_pk2_value
=> NULL,
x_pk3_value
=> NULL,
x_pk4_value
=> NULL,
x_pk5_value
=> NULL,
x_automatically_added_flag
=> 'N',
x_datatype_id
=> 6,
x_category_id
=> l_category_id,
x_security_type
=> 1,
X_security_id
=>NULL,
x_publish_flag
=> 'Y',
x_language
=> 'US',
x_description
=> l_filename,--l_description,
x_file_name
=> l_filename,
x_media_id
=> l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
dbms_output.put_line ('attachment_document id is
l_attached_document_id '||l_attached_document_id);
dbms_output.put_line ('document id is l_document_id '||l_document_id);
dbms_output.put_line (' l_media_id '||l_media_id);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Failed
'||sqlerrm);
commit;
end;
,
No comments:
Post a Comment
Thanks for your comments submitted.,will review and Post soon! by admin.