Oracle Apps: Helpful Queries on FND Attachments tables and sample code to extract attachments

This post gives some helpful queries when working on FND Attachment tables. I have also given a sample program to extract these attachment into file system.

Attachment Types are stored in the fnd_document_datatypes table.

select * from fnd_document_datatypes;

Query to retrieve the 'Short Text' Attachments:

 select fad.document_id  
       ,fad.entity_name  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fds.short_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_short_text fds
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct  
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fds.media_id  
 and   fd.datatype_id  = fdd.datatype_id  
 and   fd.category_id  = fdct.category_id
 and   fdd.user_name   = 'Short Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Long Text' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fdl.long_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_long_text fdl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fdl.media_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Long Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'File' Attachments:

 select fad.entity_name   
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fd.datatype_id  
       ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name  
       ,fl.file_data  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_lobs fl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fl.file_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'File'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Web Page' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name  
       ,fd.url  
 from fnd_attached_documents fad  
     ,fnd_documents fd
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Web Page'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Sample program to extract the attachments into file system.

declare  
  v_file     utl_file.file_type;   
  v_line     varchar2(1000);   
  v_blob_len number;  
  v_pos      number;  
  v_buffer   raw(32764);  
  v_amt      binary_integer := 32764;
  
  cursor cur_short_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fds.short_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_short_text fds  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fds.media_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'Short Text'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_long_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fdl.long_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_long_text fdl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.media_id      = fdl.media_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Long Text'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
   
  cursor cur_files  
  is  
    select fad.entity_name    
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fd.datatype_id   
          ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name   
          ,fl.file_data   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_lobs fl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fl.file_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'File'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_url_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name   
          ,fd.url   
    from fnd_attached_documents fad   
        ,fnd_documents fd  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Web Page'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
begin
  
  -- Short Text Attachments   
  for c_file in cur_short_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.short_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- Long Text Attachments   
  for c_file in cur_long_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.long_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- File Attachments  
  for c_file in cur_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'wb', 32764);  
    v_blob_len := dbms_lob.getlength(c_file.file_data);  
    v_pos := 1;  
    while v_pos < v_blob_len  
    loop  
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);  
      utl_file.put_raw(v_file,v_buffer,true);  
      v_pos := v_pos + v_amt;  
    end loop;   
    utl_file.fclose(v_file);   
  end loop;
  
  --Web Page Attachments  
  for c_file in cur_url_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.url);  
    utl_file.fclose(v_file);   
  end loop;       
end;  

Note: The oracle directory object 'XX_EXTRACT_DIR' should be created before running the script. Refer the below blog to see how to use utl_file package.
Johny's Oracle Tips: PL/SQL: Sample code for UTL_FILE



Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: Query to get the profile option values from backend

The below given is a query to get the Profile Option values at all levels from the backend. This query is very useful when you want to check which all levels the Profile is being set and what are the values.

 select fpo.profile_option_id   
       ,fpo.profile_option_name   
       ,fpo.user_profile_option_name   
       ,decode(level_id,10001,'Site'             
                       ,10002,'Application'  
                       ,10003,'Responsibility'  
                       ,10004,'User'  
                       ,10006,'Organization') profile_level   
       ,decode(level_id,10002, (select application_name    
                                from apps.fnd_application_vl    
                                where application_id = level_value )   
                       ,10003, (select responsibility_name    
                                from apps.fnd_responsibility_vl    
                                where responsibility_id = level_value)   
                       ,10004, (select user_name    
                                from apps.fnd_user    
                                where user_id = level_value)   
                       ,10006, (select name      
                                from apps.hr_all_organization_units       
                                where organization_id = level_value)) level_value   
       ,profile_option_value   
  from apps.fnd_profile_options_vl fpo   
      ,apps.fnd_profile_option_values fpov   
  where fpo.profile_option_id = fpov.profile_option_id    
  and   (fpo.profile_option_name like 'AJ_TEST_PROFILE'    
         or fpo.user_profile_option_name like 'AJ Test Profile');   

Feel free to point out if anything is missing/wrong in this blog.