2 Replies Latest reply: Dec 10, 2010 12:47 PM by 734103 RSS

    Uploading attachments using APIs does not work

    user9197772
      Hi friends,
      I am trying to upload the attachments using the following code. When I go to the attachment window and click on the Open Document the document does not open up. Can someone please tell me what is wrong in the following code
      declare
      l_rowid rowid;
      l_attached_document_id number;
      l_document_id number;
      l_media_id number;
      l_category_id number := 1;
      l_pk1_value fnd_attached_documents.pk1_value%TYPE := '98';
      l_description fnd_documents_tl.description%TYPE := 'Testing attachment';
      --l_filename fnd_documents_tl.file_name%TYPE := '/u13/app/gdevappl/per/11.5.0/media/images/rafa.jpg';
      l_filename fnd_documents_tl.file_name%TYPE := null;
      l_seq_num number;
      l_data_type_id number := 6;
      l_security_type number := 2;
      l_file_path_dir varchar2(1000):=NULL;

      file_num number:= null;
      dest_loc BLOB;
      src_loc BFILE := BFILENAME('EXAMPLE_LOB_DIR', 'logo.jpg');
      l_file_id number:=null;
      l_file_id1 number:=null;
      l_access_id number:=null;
      file_name varchar2(100):= null;

      CURSOR File_Path IS
      SELECT value,Substr(value,instr(value,',',1,1)+2,instr(value,',',1,2) - instr(value,',',1,1)-2) file_path_dir
      FROM v$parameter
      WHERE name = 'utl_file_dir';

      begin
      fnd_global.apps_initialize(1668,50230,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 = l_pk1_value
      and entity_name = 'PER_PEOPLE_F';

      /*
      FOR l_File_path IN File_Path LOOP
      l_file_path_dir:=l_file_path.file_path_dir;
      END LOOP;
      */

      fnd_documents_pkg.insert_row
      ( X_ROWID => l_rowid
      , X_DOCUMENT_ID => l_document_id
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
      , X_DATATYPE_ID => l_data_type_id
      , X_CATEGORY_ID => l_category_id
      , X_SECURITY_TYPE => l_security_type -- check this shekar
      , X_PUBLISH_FLAG => 'Y'
      , X_USAGE_TYPE => 'O'
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );

      fnd_documents_pkg.insert_tl_row
      ( X_DOCUMENT_ID => l_document_id
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );

      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 => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
      , X_SEQ_NUM => l_seq_num
      , X_ENTITY_NAME => 'PER_PEOPLE_F'
      , 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 => l_data_type_id
      , X_CATEGORY_ID => l_category_id
      , X_SECURITY_TYPE => l_security_type
      , X_PUBLISH_FLAG => 'Y'
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );
      BEGIN

      file_name := 'logo.jpg';
      insert into fnd_lobs (file_id, file_name, file_content_type,
      file_data, upload_date, expiration_date, program_name, program_tag,
      language,oracle_charset,file_format)
      values (fnd_lobs_s.nextval, file_name, 'image/pjpeg',
      EMPTY_BLOB(), sysdate, null, 'FNDATTCH', null,
      'US', fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset), 'binary')
      returning file_data, file_id into dest_loc, l_file_id;

      l_access_id := fnd_gfm.authorize(l_file_id);
      dbms_output.put_line('file_id :'||l_file_id);
      dbms_output.put_line('file_name :'||file_name);
      dbms_output.put_line('l_access_id :'||l_access_id);

      DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
      dbms_output.put_line('src_loc :'||DBMS_LOB.getLength(src_loc));

      DBMS_LOB.LOADFROMFILE(
      dest_lob => dest_loc
      , src_lob => src_loc
      , amount => DBMS_LOB.getLength(src_loc));

      DBMS_LOB.CLOSE(dest_loc);
      DBMS_LOB.CLOSE(src_loc);
      COMMIT;
      l_file_id1 := FND_GFM.confirm_upload(l_access_id,file_name,'FNDATTCH',NULL,NULL,'US',null);
      dbms_output.put_line('l_file_id1 :'||l_file_id1);
      exception when others then
      dbms_output.put_line('error12 :'||sqlerrm);
      END;

      dbms_output.put_line('file_num '||file_num);
      dbms_output.put_line('Done ');
      commit;
      exception when others then
      dbms_output.put_line('error :'||sqlerrm);
      end;
      /