document attachments

Hi @Ramadass ,

If you just what to know the file extension you can do something, not so elegant, like this 

select edm_file_api.Get_File_Type(s.doc_class, s.doc_no, s.doc_sheet, s.doc_rev, s.doc_type, s.file_no) as file_type,
EDM_APPLICATION_api.Get_File_Extention(edm_file_api.Get_File_Type(s.doc_class, s.doc_no, s.doc_sheet, s.doc_rev, s.doc_type, s.file_no)) as file_extention,
s.* from edm_file_storage s
where s.doc_no = '1004956'

but IFS has no knowledge of what is actually in the blob. You cannot check-in a .pdf and get it out as .doc.

HI @Hans Andersen ,

if .pdf file checked in and I need to take that .pdf file not as doc file.

in edm_file_storage_tab ‘file data’ column we have blob data.

whether possible to extract the blob data as respective file format.

Hi,

I think you have to explain you scenario. What are you trying to do?

Ramadass,

Is it that you want to lift the pdf from the blob field? If so, the following might be a starting point:

 /*******************************************************************************
 Description: Copy image of invoice to directory

 Date Author Description
 -------------------------------------------------------------------------------
 26-03-2018 Steve Alink Initial create
 *******************************************************************************/
 function Cpy_Invoice(ora_dir_ in varchar2,
 doc_no_ in varchar2, doc_sheet_ in varchar2, doc_rev_ in varchar2,
 doc_type_ in varchar2 default 'ORIGINAL', file_no_ in varchar2 default 1, doc_ext_ in varchar2)
 return varchar2
 is
 pdf_file utl_file.file_type;
 Stor_buf_ raw(32000);
 pdf_blob blob;
 pdf_blob_len integer;
 vstart number := 1;
 addmax binary_integer := 32000;
 x number;
 l_filename_ varchar2(100);

 cursor get_lob is
 -- Get LOB locator
 select file_data
 from ifsapp.edm_file_storage
 where doc_no = doc_no_
 and doc_sheet = doc_sheet_
 and doc_rev = doc_rev_
 and doc_type = doc_type_
 and file_no = file_no_;

 begin
 open get_lob;
 fetch get_lob into pdf_blob;
 close get_lob;

 pdf_blob_len := dbms_lob.getlength(pdf_blob);

 -- Open the destination files.
 l_filename_ := source_ERP_ || '_' || doc_no_ || '.' || doc_ext_;
 pdf_file := utl_file.fopen(ora_dir_, l_filename_, 'wb', 32764);

 vstart := 1;

 -- Save blob length
 x := pdf_blob_len;

 -- If small enough for a single write
 IF pdf_blob_len < 32760 THEN
 utl_file.put_raw(pdf_file, pdf_blob);
 utl_file.fflush(pdf_file);
 ELSE
 -- Doesn't fit, have to write it in pieces
 vstart := 1;
 WHILE vstart < pdf_blob_len and addmax > 0 LOOP
 dbms_lob.read(pdf_blob, addmax, vstart, Stor_buf_);

 utl_file.put_raw(pdf_file, Stor_buf_);
 utl_file.fflush(pdf_file);

 -- Set the start position for the next cut
 vstart := vstart + addmax;

 -- Set the end position if less than 32000 bytes
 x := x - addmax;
 if x < 32000 then
 addmax := x;
 end if;
 end loop;
 end if;

 -- Close the files.
 utl_file.fclose(pdf_file);
 return 'RANOK';

 exception
 when others then
 -- Close the files if something goes wrong.
 if utl_file.is_open(pdf_file) then
 utl_file.fclose(pdf_file);
 return 'ERROR';
 end if;
 raise;
 end Cpy_Invoice;

Good luck,

Steve

HI @eqbstal ,

Thanks for your solution...

Could you please tell me how can we find ‘ora_dir_’ original directory for a file which we are going to export?

best regards,

Ramadass

Ramadass,

Use the following to see if there are any available for owner IFSAPP:

SELECT * FROM all_directories;

If you want to create a new one use:

CREATE or replace directory DOC_TRANSFER AS 'C:/ifs/oradb/APPS10/Docs';

Directory C:/ifs/oradb/APPS10/Docs must exist on the database server

GRANT read, write on directory DOC_TRANSFER to IFSAPP;

In PLSQL you would assign the directory as:

ora_dir_    varchar2(250) := ‘DOC_TRANSFER’;

In the example above I have declared ora_dir_ as a global variable.

Steve

Hi @eqbstal ,

I am taking the exist document so I need to find the exist directory name.

I got directory names from all_directories but there are many names.

how can we find the particular directory name which linked to that particular file?

Could you please give one example..

Thanks in advance…

best regards,

Ramadass

HI @eqbstal ,

I executed with valid directory but I am getting the error like below screenshot.

Please help to debug this error..

best regards,

Ramadass

My guess is that the windows directory that is connected to the Oracle MyDir_ value doesn't allow anything to be written from IFS Applications.

Check the security settings of the windows directory. In the above example \\fs01\apbio\bio\IFS\Eqeep should be checked and set the directory open for full access.

Altenative:

Try another directory as you got a lot of results back from select * from all_directories;

Steve  

HI @eqbstal ,

Thank you very much…

Its working fine.