-- Reading Attachment from Directory "MY_PDF2"
--create or replace directory MY_PDF2 as
'/uat3app/PDF'
DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_category_name VARCHAR2 (100) := 'Miscellaneous';
l_pk1_value varchar2(100);
l_filename VARCHAR2
(240);
l_seq_num NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
l_file BFILE;
l_security_id NUMBER;
l_entity_name VARCHAR2 (100) := 'PER_PEOPLE_F';
l_dstn_file BLOB;
l_file_size NUMBER;
l_file_exists NUMBER := 0;
CURSOR c1
IS
SELECT dei.document_extra_info_id,
per.person_id,
per.full_name,
hdt.category_code,
dei.document_number,
dei.date_from,
dei.date_to,
fl.file_name,
fl.file_content_type,
fl.file_data
FROM
hr_document_extra_info dei,
per_all_people_f per,
hr_document_types hdt,
fnd_attached_documents fat,
fnd_documents fd,
fnd_lobs fl
WHERE per.person_id
= dei.person_id
AND
per.business_group_id =
81
AND
per.employee_number =
'30836'
AND
per.current_employee_flag =
'Y'
AND
TRUNC (SYSDATE) BETWEEN per.effective_start_date
AND
per.effective_end_date
AND
hdt.document_type_id =
dei.document_type_id
AND
fat.entity_name =
'R_DOCUMENT_EXTRA_INFO'
AND pk1_value =
dei.document_extra_info_id
AND
fd.document_id =
fat.document_id
AND
fl.file_id =
fd.media_id
AND
hdt.category_code =
'VISA_INFO';
BEGIN
FOR
i IN c1
LOOP
l_pk1_value :=
i.person_id;
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 = l_pk1_value AND
entity_name = l_entity_name;
SELECT user_id
INTO
l_fnd_user_id
FROM
apps.fnd_user
WHERE user_name = 'BIJOY';
-- 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' AND language = 'US';
-- Select Category id for Attachments
SELECT category_id
INTO
l_category_id
FROM
apps.fnd_document_categories_vl
WHERE user_name = 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
INTO
l_document_id, l_attached_document_id
FROM
DUAL;
SELECT MAX (file_id)
+ 1 INTO l_media_id FROM
fnd_lobs;
l_filename :=
i.file_name;
l_file :=
BFILENAME ('MY_PDF2',
l_filename);
l_file_exists :=
DBMS_LOB.fileexists (l_file);
l_file_size :=
DBMS_LOB.getlength (l_file);
IF
l_file_exists = 1
THEN
DBMS_LOB.createtemporary
(l_dstn_file,
TRUE, DBMS_LOB.session);
DBMS_LOB.fileopen
(l_file,
DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile
(l_dstn_file,
l_file,
l_file_size,
1,
1);
COMMIT;
DBMS_LOB.fileclose
(l_file);
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,
l_dstn_file,
'US',
'UTF8',
'binary');
COMMIT;
DBMS_OUTPUT.put_line
('PDF Loaded in Lobs');
END
IF;
-- 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,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => l_fnd_user_id,
x_datatype_id => l_short_datatype_id, -- FILE
x_security_id => NULL, -- org id
x_publish_flag => 'Y',
--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,
x_file_name => l_filename,
x_media_id => l_media_id);
COMMIT;
DBMS_OUTPUT.put_line
('l_document_id: ' || l_document_id);
fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => l_fnd_user_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,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => l_fnd_user_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 => l_security_id,
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
(
'l_attached_document_id: ' || l_attached_document_id);
END
LOOP;
END;
Reference:
http://oracleappsengineering.blogspot.ae/2010/04/attachments-api-fnddocumentspkg.html
Why do you have to commit after each API call ?
ReplyDeleteSELECT MAX (file_id) + 1 INTO l_media_id FROM fnd_lobs; should you not use fnd_lobs_s.nextval for media id
ReplyDelete