How do you export/import files from Oracle EBS R12.0.6 or highest using FND GFM utility

This post was dedicated to Oracle EBS developers!

During some days i'm trying to find any blog, note or post about the functionality export/import files from Oracle EBS, but can’t found nothing about it. I needed anything for make reference in my actual activity, so decided write a document explaining how about works the FND GFM utility for export/import files.

This functionality was used in Oracle EBS R12.0.6. The frontend is Oracle OAF, but you can to call that feature from Oracle Forms according of Oracle EBS’s architecture.

☛ IMPORTANT NOTE: very important know what only the content of the file will be imported to Oracle EBS Database on the an exclusive table. When you choose the file by the button “Browse” on this window, the functionality will copy the binary content of  your file and only the content will be stored on FND_LOBS table. Your file will not be removed from original repository.

Talking about this Oracle EBS feature, some points are important to clarify this context and understand what will be implemented in your program:

• The developer team don’t need to create anything for use it, that feature is incorporate on release R12 of the Oracle EBS;
• In the import functionality doesn’t remove your original file;
• Just only the content of file will be imported between your computer (any folder what your computer can access) to Oracle EBS database;
• By definition of that feature, the target table defined to receive the content of the file is named FND_LOBS.

For you implement this feature on your Oracle Forms program, you need to follow some steps how i’ll trying describe bellow:

1. Create a button in your program, because my sample will be based in a button. You can use any other component what you wish;
2. Create some messages for ask to user things how: "Do you want to import a file?", "Do you want to export of this file?", "File imported successfully", any message what you prefer to show for the user;
3. Apply the codes in your program how defined bellow. You need to reference the codes #3 (Import files) or #4 (Export files), the other codes (#1 and #2) are complementary in this case.

Defining code for call the Import/Export OAF page

Code #1: used to create file on FND_LOBS table.

   PROCEDURE file_p( p_file_id   in out fnd_lobs.file_id%type
                   , p_download  in boolean default true ) IS
     l_nAccess_Id  NUMBER;
     l_vServer_Url VARCHAR2(255);
     l_vUrl        VARCHAR2(4000);
     l_nButton     NUMBER;
   BEGIN
     --
     l_nAccess_Id := fnd_gfm.authorize( NULL );
     fnd_profile.get( 'APPS_WEB_AGENT', l_vServer_Url );
     --
     if p_file_id is null then
        l_vUrl := rtrim( l_vServer_Url, '/' ) || '/fnd_file_upload.displayGFMform?access_id=' || to_char( l_nAccess_Id ) || chr(38) || 'l_vServer_Url=' || l_vServer_Url;
     else
        l_vUrl := fnd_gfm.construct_download_url( fnd_web_config.gfm_agent, p_file_id, FALSE );
     end if;
     --
     fnd_utilities.open_url( l_vUrl );
     --
     IF p_download THEN
        -- 
        -- Ask to user what the download was completed
        --
        fnd_message.set_name( 'XXBOL','XXBOL_DOWNLOAD_COMPLETE' );
        l_nButton := fnd_message.question( button1     => 'YES'
                                         , button2     => NULL
                                         , button3     => 'NO'
                                         , default_btn => 1
                                         , cancel_btn  => 3
                                         , icon        => 'question' );
     ELSE
       -- 
       -- Ask to  user what the upload was completed
       --
       fnd_message.set_name( 'XXBOL','XXBOL_UPLOAD_COMPLETE' );
       l_nButton := fnd_message.question( button1     => 'YES'
                                        , button2     => NULL
                                        , button3     => 'NO'
                                        , default_btn => 1
                                        , cancel_btn  => 3
                                        , icon        => 'question' );                                    
     END IF;
     -- 
     IF p_file_id IS NULL THEN
        p_file_id := fnd_gfm.get_file_id( l_nAccess_Id ); 
     ELSE
        clear_file_p( p_file_id => p_file_id );
     END IF;
     --

   END file_p;

Code #2: remove files no more used from FND_LOBS table.

   PROCEDURE clear_file_p( p_file_id in fnd_lobs.file_id%type ) IS
   BEGIN
      --
      DELETE fnd_lobs
       WHERE file_id = p_file_id;
      --
      STANDARD.COMMIT;

   END clear_file_p;   

Code #3: exclusive use for open OAF page for import files

   PROCEDURE import_file_p IS
      CURSOR c_file( pc_file_id fnd_lobs.file_id%type ) IS
        SELECT file_data
          FROM fnd_lobs
         WHERE file_id = pc_file_id;
      l_rFile             c_file%ROWTYPE;
      --
      l_cFile             CLOB := empty_clob;
      l_nHeaderId         NUMBER;
      l_nSeqNumber        NUMBER;
      l_vErrorCode        VARCHAR2(50);
      l_vErrorMessage     VARCHAR2(3000);
      l_nRequestId        NUMBER;
      --
      l_nPos              NUMBER := 1;
      l_nFim              NUMBER := 1;
      l_nQtd_Chars        NUMBER := 1;
      l_vString           CLOB;
      l_nQtd_Occur        NUMBER := 4;
      l_nIndex            NUMBER := 0;
      l_nFile_Id          NUMBER := NULL;
      l_nButton           NUMBER;
      --
      l_nDest_OffSet      NUMBER := 1;
      l_nSource_OffSet    NUMBER := 1;
      l_nLang_Context     NUMBER := 0;
      l_nWarning          NUMBER := 1;
      l_nLobMaxSize       NUMBER := 18446744073709551615;
      l_nDefaultCSid      NUMBER := 0;
      --
   BEGIN  
      -- 
      -- Ask to user about the importation
      --
      FND_MESSAGE.SET_NAME( 'XXBOL', 'XXBOL_IMPORT_FILE' );     
      l_nButton := fnd_message.question( button1     => 'Yes'
                                       , button2     => NULL
                                       , button3     => 'No'
                                       , default_btn => 1
                                       , cancel_btn  => 3
                                       , icon        => 'question' );
      --
      IF l_nButton = 3 THEN 
          RETURN; 
      END IF; 
      file_p( p_file_id  => l_nFile_Id
            , p_download => FALSE ); 
      --
      DBMS_LOB.createtemporary( l_cFile, TRUE );
      --
      OPEN c_file( pc_file_id  => l_nFile_Id );
      FETCH c_file INTO l_rFile;
      IF c_file%NOTFOUND THEN
        CLOSE c_file;
        -- 
        -- Notify the user what no file was imported
        --
        FND_MESSAGE.set_name( 'XXBOL', 'XXBOL_NO_FILE_IMPORTED' ); 
        FND_MESSAGE.show;
        RETURN;
      END IF; 
      CLOSE c_file;
      --
      DBMS_LOB.ConvertToClob( dest_lob     => l_cFile
                            , src_blob     => l_rFile.file_data
                            , amount       => l_nLobMaxSize
                            , dest_offset  => l_nDest_OffSet
                            , src_offset   => l_nSource_OffSet
                            , blob_csid    => l_nDefaultCSid
                            , lang_context => l_nLang_Context
                            , warning      => l_nWarning );
      l_nQtd_Chars := DBMS_LOB.getlength(lob_loc => l_rFile.file_data ); 
      -- 
      clear_file_p( p_file_id => l_nFile_Id );
      --

   END;

Code #4: exclusive use for open OAF page for export files

   PROCEDURE export_file_p IS 
      l_vUserChoice     NUMBER;
      --
      l_vContentFile    CLOB;
      l_bFile           BLOB;
      l_vFileName       VARCHAR2(100);
      l_nDest_OffSet    NUMBER := 1;
      l_nSource_OffSet  NUMBER := 1;
      l_nLang_Context   NUMBER := 0;
      l_nWarning        NUMBER := 1;
      l_nLobMaxSize     NUMBER := 18446744073709551615;
      l_nDefaultCSid    NUMBER := 0;
      l_rFLOBS          fnd_lobs%ROWTYPE;
      l_nButton         NUMBER;
      --
   BEGIN  
      -- 
      FND_MESSAGE.SET_NAME( 'XXBOL', 'XXBOL_EXPORT_FILE' );     
      l_nButton := fnd_message.question( button1     => 'Yes'
                                       , button2     => NULL
                                       , button3     => 'No'
                                       , default_btn => 1
                                       , cancel_btn  => 3
                                       , icon        => 'question' );
      --
      IF l_nButton = 3 THEN 
          RETURN; 
      END IF;  
      -- 
      l_vFileName    := :HEADERS.LAYOUT_CODE || '.xls';
      l_vContentFile := get_excel_file_f;
      --
      -- create a temporary file
      DBMS_LOB.createtemporary( l_bFile, TRUE );
      DBMS_LOB.converttoblob( dest_lob     => l_bFile
                            , src_clob     => l_vContentFile
                            , amount       => l_nLobMaxSize
                            , dest_offset  => l_nDest_OffSet
                            , src_offset   => l_nSource_OffSet
                            , blob_csid    => l_nDefaultCSid
                            , lang_context => l_nLang_Context
                            , warning      => l_nWarning );
      -- 
      SELECT fnd_lobs_s.NEXTVAL
        INTO l_rFLOBS.file_id
        FROM DUAL;  
      --
      l_rFLOBS.file_name               := l_vFileName;
      l_rFLOBS.file_content_type       := 'application/octet-stream';
      l_rFLOBS.file_data               := l_bFile;
      l_rFLOBS.upload_date             := SYSDATE;
      l_rFLOBS.program_name            := 'XXBOL_EXPORT_EXCEL';
      l_rFLOBS.language                := USERENV( 'LANG' );
      l_rFLOBS.oracle_charset          := 'WE8ISO8859P1';
      l_rFLOBS.file_format             := 'IGNORE';          
      --
      INSERT INTO fnd_lobs VALUES( l_rFLOBS ); 
      --
      standard.commit;                 
      --  
      file_p( p_file_id => l_rFLOBS.file_id );                         
      --
      DBMS_LOB.FREETEMPORARY( l_bFile ); -- clear file

   END;

Defining messages for the above codification

Use the document related bellow on session "Related Documents" for understand the component FND Message of the Oracle EBS, because the codification do use this in all long code.

XXBOL_EXPORT_FILE
-----------------------
Do you want export this file?


XXBOL_IMPORT_FILE
-----------------------
Would you like to import a file?


XXBOL_DOWNLOAD_COMPLETE
-----------------------
Has the file been downloaded successfully?


XXBOL_UPLOAD_COMPLETE
-----------------------
The upload page must be opened and must be used for upload of your file.

Has the file been uploaded successfully?


XXBOL_NO_FILE_IMPORTED
-----------------------
No file imported. Please look for following instructions:

1 - File upload completed successfully on upload page.
2 - Please close the web browser.
3 - Return to the Attachments form and select the Yes button to indicate file upload is complete.

Related Documents

• Developer Guide creating messages for your customization, look on session 12 - Message Dictionary (at oct/2014).

Collaborators

Rodrigo Hataya
Daiane Bitencourt