Create a custom form with Oracle Forms

Goal: create a custom form called "Extended Record History" and include it in the Tools (Сервис) menu. It will display extended HR information for the current record, such as full name, department, position, and other details.

To proceed, we will need the Oracle Developer Suite 10 software. I should note upfront that, theoretically, this IDE allows running forms via a Java applet in a browser. However, in practice, I was unable to achieve this despite trying various versions of JInitiator (1.3.22, 18, 26) and modifying the contents of formsweb.cfg. Therefore, this issue remains unresolved.

Stage 1 : Modifying the Forms menu. Download $AU_TOP/resource/RU/FNDMENU.mmb and open it in Oracle Developer Suite 10:
Add a new item to the very end of the list.
In the window opened from the context menu of the PL/SQL Editor, we specify the block with the trigger to be invoked. We will create this trigger later.
:global.menu_to_appcore := 'ADVANCED_RECORD_HISTORY';
execute_trigger('menu_to_appcore');
Save the file and deploy to the server
frmcmp_batch module=FNDMENU.mmb userid=apps/apps output_file=$AU_TOP/resource/RU/FNDMENU.mmx compile_all=special module_type=menu
 ...
 - Inserting menu FNDMENU.
 - Inserting menu FILE.
 - Inserting menu EDIT.
 - Inserting menu VIEW.
 - Inserting menu FOLDER.
 - Inserting menu SPECIAL.
 - Inserting menu SPECIAL_B.
 - Inserting menu SPECIAL_C.
 - Inserting menu HELP.
 - Inserting menu QUERY.
 - Inserting menu DIAGNOSTICS.
 - Inserting menu TRACE_MENU.
 - Inserting menu LOGGING_MENU.
 - Inserting menu PROPERTIES_MENU.
 - Inserting menu CUSTOM_CODE_MENU.
 - Inserting menu CLEAR.
 - Inserting menu DUPLICATE.
 - Inserting menu PREFERENCES.
 - Inserting menu RECORD.
 - Ready.
After restarting Forms, we will see our new menu item:
If party policy prohibits editing FNDMENU.mmb (as in our case), then the menu item will need to be added dynamically. To do this:
  • Add code to the EVENT handler in the CUSTOM.pll library. This library was specifically created for customizations. The code below will call the handler of our custom library for any Forms event. There, we will impose restrictions.
XXFND999.event(event_name);
Create a custom library XXFND999.pll with an EVENT handler. The handler performs a dual function: it creates the menu item once and calls the form when the corresponding trigger is activated.
-- Cancel the execution of functionality inside the form
if name_in('SYSTEM.CURRENT_FORM') = l_form_name then return; end if;
l_block := name_in('SYSTEM.CURRENT_BLOCK');
-- Check for the existence of a block
if length(l_block) = 0 then return; end if;
    -- Event when creating a form instance
    if p_event_name = 'WHEN-NEW-BLOCK-INSTANCE' then
       l_event_name  := xx_form_custom.create_special(l_item_label,'LINE');
    end if;
    /* Custom method to get the first
        unoccupied cell in SPECIAL */
    l_event_name  := xx_form_custom.get_special(l_item_label);
    -- Enabling visibility and activating the new menu item
    xx_form_custom.activate_special(l_item_label);
    -- Saving the menu item
    xx_form_custom.save_menu();
    -- If our form is called
    if (p_event_name = l_event_name)then
     l_frm_path := fnd_navigate.formpath(l_tgt_fn_app, l_tgt_fn_name);
     -- Check for the existence of the CREATED_BY field in the table with the current record
     l_created_by := find_item(name_in('SYSTEM.CURRENT_BLOCK')||'.CREATED_BY');
     if not id_null(l_created_by) then
        -- Check for the existence of a created parameter list
        param_id := get_parameter_list('FND_FORM_ARGS');
        if (not id_null(param_id))
        then
          destroy_parameter_list(param_id);
        end if;
       -- Create a parameter list and add information from service fields
       param_id := create_parameter_list('FND_FORM_ARGS');
       add_parameter( param_id, 'CREATED_BY', TEXT_PARAMETER, name_in(name_in('SYSTEM.CURSOR_BLOCK')||'.CREATED_BY'));
       add_parameter( param_id, 'LAST_UPDATED_BY', TEXT_PARAMETER, name_in(name_in('SYSTEM.CURSOR_BLOCK')||'.LAST_UPDATED_BY'));
       add_parameter( param_id, 'CREATION_DATE', TEXT_PARAMETER,name_in(name_in('SYSTEM.CURSOR_BLOCK')||'.CREATION_DATE'));
       add_parameter( param_id, 'LAST_UPDATE_DATE', TEXT_PARAMETER, name_in(name_in('SYSTEM.CURSOR_BLOCK')||'.LAST_UPDATE_DATE'));
       -- Open the form
        open_form ( formmodule_name => l_frm_path
            , activate_mode   => ACTIVATE
            , session_mode    => SESSION
            , data_mode       => NO_SHARE_LIBRARY_DATA
            , paramlist_id    => param_id
            );
    end if;
end if;
Stage 2 : Form creation.
We create a Data Block , add a WHEN-NEW-BLOCK-INSTANCE trigger, and create an item called USER_INFO , to which we will assign a string containing user data separated by line breaks. We create a canvas, place the USER_INFO item on it, and set its properties to Multiline = Yes and Length = 32000 .
Next, we create several parameters identical to those defined in the custom library: CREATED_BY , LAST_UPDATED_BY , CREATION_DATE , and LAST_UPDATE_DATE .
We then begin editing the PL/SQL code of the created data block and add the following:
declare 
  l_create_id          varchar2(50) := :PARAMETER.CREATED_BY;
  l_update_id         varchar2(50) := :PARAMETER.LAST_UPDATED_BY;
  l_create_date          varchar2(50) := :PARAMETER.CREATION_DATE;
  l_update_date          varchar2(50) := :PARAMETER.LAST_UPDATE_DATE;
begin
  :XXFND361_TEST_V.CREATED_BY_FULLNAME := XXFND361_PKG.get_record_history(p_create_id => l_create_id,
                                              p_update_id => l_update_id,
                                              p_create_date => l_create_date,
                                              p_update_date => l_update_date
                                              );
XXFND999_PKG is a typical PL/SQL package:
 function get_record_history(p_create_id in varchar2,
                              p_update_id in varchar2,
                              p_create_date in varchar2,
                              p_update_date in varchar2
                             ) return varchar2
  is
        l_result_line          varchar2(32000);

        l_create_person        varchar2(150);
        l_create_org_id        varchar2(150);
        l_create_job           varchar2(150);
        l_create_contact       varchar2(150);
       
        l_update_person        varchar2(150);
        l_update_org_id        varchar2(150);
        l_update_job           varchar2(150);
        l_update_contact       varchar2(150); 
        l_is_person            varchar2(1);
       
        l_create_organization_id number;
        l_update_organization_id number;
       
        l_create_id          varchar2(150) := p_create_id;
        l_update_id         varchar2(150) := p_update_id;
        l_create_date          varchar2(150) := p_create_date;
        l_update_date          varchar2(150) := p_update_date;       
begin
 if l_create_id is null then
   return '';
 end if; 
    select case when user_det.full_name is null then
             fnu.user_name
       else
             decode(regexp_substr(upper(user_det.full_name),'I'), null, user_det.full_name, fnu.user_name) || ' ' ||
       user_det.employee_number end,
       decode(user_det.full_name, null, 'N', 'Y') into l_create_person, l_is_person
    from FND_USER fnu,
           per_all_people_f    user_det
    where fnu.employee_id = user_det.person_id(+)
      and trunc(sysdate) between user_det.effective_start_date(+) and user_det.effective_end_date(+)
      and fnu.user_id = to_number(l_create_id);
   --
   if l_is_person = 'Y' then
    
   begin 
   select asg.organization_id into l_create_organization_id
    from per_all_people_f          user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu
    where user_det.person_id = asg.person_id
     and fnu.employee_id = user_det.person_id
     and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
     and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
     and fnu.user_id = to_number(l_create_id);
   exception
     when NO_DATA_FOUND then
       null;
   end;
 
   --
  
   begin 
   select j.name into l_create_job
    from per_all_people_f      user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu,
      per_jobs                  j
   where user_det.person_id = asg.person_id
     and fnu.employee_id = user_det.person_id
     and asg.job_id = j.job_id
     and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
     and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
     and trunc(sysdate) >= j.date_from and j.date_to is null
     and fnu.user_id = to_number(l_create_id)
     and rownum = 1;
   exception
     when NO_DATA_FOUND then
       null;
   end;        
   --
   begin
   select coalesce(user_det.email_address, fnu.Email_Address, ' ') || ' ' ||
      user_det.work_telephone
     into l_create_contact
    from per_all_people_f          user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu
   where user_det.person_id = asg.person_id
     and fnu.employee_id = user_det.person_id
    and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
    and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
    and fnu.user_id = to_number(l_create_id);
   exception
     when NO_DATA_FOUND then
       null;
   end;      
   end if;
   --------------------------
    l_is_person   := '';
   --------------------------
   select case when user_det.full_name is null then
             fnu.user_name
       else
             decode(regexp_substr(upper(user_det.full_name),'I'), null, user_det.full_name, fnu.user_name) || ' ' ||
       user_det.employee_number end,
       decode(user_det.full_name, null, 'N', 'Y') into l_update_person, l_is_person
    from FND_USER fnu,
         per_all_people_f    user_det
    where fnu.employee_id = user_det.person_id(+)
      and trunc(sysdate) between user_det.effective_start_date(+) and user_det.effective_end_date(+)
      and fnu.user_id = to_number(l_update_id);
     
   if l_is_person = 'Y' then

   begin
   select asg.organization_id into l_update_organization_id
    from per_all_people_f          user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu
   where user_det.person_id = asg.person_id
     and fnu.employee_id = user_det.person_id
     and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
     and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
     and fnu.user_id = to_number(l_update_id);
   exception
     when NO_DATA_FOUND then
       null;
   end;      
   --
   begin
   select j.name into l_update_job
     from per_all_people_f      user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu,
      per_jobs                  j
 where user_det.person_id = asg.person_id
   and fnu.employee_id = user_det.person_id
   and asg.job_id = j.job_id
   and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
   and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
   and trunc(sysdate) >= j.date_from and j.date_to is null
   and fnu.user_id = to_number(l_update_id);
   exception
     when NO_DATA_FOUND then
       null;
   end;   
   --
   begin
   select coalesce(user_det.email_address, fnu.Email_Address, ' ') || ' ' ||
       user_det.work_telephone
    into l_update_contact
    from per_all_people_f          user_det,
      per_all_assignments_f     asg,
      FND_USER                  fnu
 where user_det.person_id = asg.person_id
   and fnu.employee_id = user_det.person_id
   and trunc(sysdate) between user_det.effective_start_date and user_det.effective_end_date
   and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
   and fnu.user_id = to_number(l_update_id);
   exception
     when NO_DATA_FOUND then
       null;
   end;    
  end if;
   ---------------
   select decode(l_create_person, null, '', 'Создал запись ' || l_create_person || chr(10)) into l_create_person from dual;
   select decode(l_create_org_id, null, '', 'Структурное подразделение ' || l_create_org_id || chr(10)) into l_create_org_id from dual;
   select decode(l_create_job, null, '', 'Должность ' || l_create_job || chr(10)) into l_create_job from dual;
   if length(trim(l_create_contact)) > 0 then
      l_create_contact := 'Контакты ' || l_create_contact || chr(10);
   else
      l_create_contact := '';
   end if;
   select decode(l_create_date, null, '', 'Дата создания записи ' || l_create_date || chr(10)) into l_create_date from dual;

   select decode(l_update_person, null, '', 'Изменил запись ' || l_update_person || chr(10)) into l_update_person from dual;
   select decode(l_update_org_id, null, '', 'Структурное подразделение ' || l_update_org_id || chr(10)) into l_update_org_id from dual;
   select decode(l_update_job, null, '', 'Должность ' || l_update_job || chr(10)) into l_update_job from dual;
   if length(trim(l_update_contact)) > 0 then
      l_update_contact := 'Контакты ' || l_update_contact || chr(10);
   else
      l_update_contact := '';
   end if;
   select decode(l_update_date, null, '', 'Дата изменения записи ' || l_update_date) into l_update_date from dual;
  
  l_result_line := l_create_person ||
  l_create_org_id ||
  l_create_job ||
  l_create_contact ||
  l_create_date || chr(10) ||
 
  l_update_person ||
  l_update_org_id ||
  l_update_job ||
  l_update_contact ||
  l_update_date;
  return l_result_line;
  exception
    when OTHERS then
      return 'Ошибка! p_create_id=' || l_create_id || ';' ||
      'l_update_id=' || l_update_id || ';' ||
      'l_create_date=' || l_create_date || ';' ||
      'l_update_date=' || l_update_date ||
      'error info: ' || sqlerrm;   
 end get_record_history;
Stage 3 : Creating and linking the function and form in Oracle Forms. Once everything is created, the setup is performed using the following script:
cp ./forms/XXFND999_HISTORY_RECORD_ADV.fmb $AU_TOP/forms/RU
cp ./forms/XXFND999_HISTORY_RECORD_ADV.fmb $AU_TOP/forms/US
cp ./resource/CUSTOM.pll $AU_TOP/resource/CUSTOM.pll
cp ./resource/XXFND999_CUSTOM.pll $AU_TOP/resource/XXFND999_CUSTOM.pll
cd $AU_TOP/forms/RU
frmcmp_batch.sh module=$AU_TOP/forms/RU/XXFND999_HISTORY_RECORD_ADV.fmb userid=$APPSID module_type=form compile_all=yes output_file=$XX_TOP/forms/RU/XXFND999_HISTORY_RECORD_ADV.fmx
cd $AU_TOP/forms/US
frmcmp_batch.sh module=$AU_TOP/forms/US/XXFND999_HISTORY_RECORD_ADV.fmb userid=$APPSID module_type=form compile_all=yes output_file=$XX_TOP/forms/US/XXFND999_HISTORY_RECORD_ADV.fmx
cd $AU_TOP/resource
frmcmp_batch.sh module=$AU_TOP/resource/XXFND999_CUSTOM.pll userid=$APPSID module_type=library compile_all=yes output_file=$AU_TOP/resource/XXFND999_CUSTOM.plx
frmcmp_batch.sh module=$AU_TOP/resource/CUSTOM.pll userid=$APPSID module_type=library compile_all=yes output_file=$AU_TOP/resource/CUSTOM.plx
Result:
Pitfalls
1. FRM-41106: Cannot create record without parent
The BLOCK.ITEM data access model was not followed. If the block can be any block, the correct way to retrieve a value from an item is as follows:
name_in(name_in('SYSTEM.CURSOR_BLOCK')||'.CREATED_BY'));

2. FRM-10056: Incorrect module type stored in the file
Incorrect parameter in the shell script for installing the extension. Attention:
frmcmp_batch.sh module=$AU_TOP/forms/US/XXFND999_HISTORY_RECORD_ADV.fmb userid=$APPSID module_type=form compile_all=yes output_file=$XX_TOP/forms/US/XXFND999_HISTORY_RECORD_ADV.fmx
1. FRM-91507: Internal Error: Unable to generate library
  • Error in the custom library : This error occurs when there is an issue with the custom library (e.g., CUSTOM.pll or another PL/SQL library). It could be caused by syntax errors, missing dependencies, or incorrect compilation of the library.
  • Solution : Check the library code for errors, ensure all dependencies are correctly linked, and recompile the library.
2. FRM-30085: Unable to adjust form for output
  • The form is too large, or elements extend beyond the form's boundaries : This error occurs when the form's layout exceeds the visible area or when certain items are placed outside the allowable dimensions of the form.
  • Solution : Resize the form canvas, ensure all items fit within the visible area, and adjust the layout accordingly.
3. FRM-47023: No such parameter named G_QUERY_FIND exists in form
  • Tricky error that initially confuses because G_QUERY_FIND is a standard parameter that should always exist : This error can occur if there is a mismatch in the data type of the parameters. For example, even if a date is being passed, the parameter type must be set to CHAR. After making this adjustment, the issue was resolved.
  • Solution : Verify the data types of all parameters and ensure they match the expected types. For example, use CHARfor parameters like G_QUERY_FIND, even if they represent dates.
4. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  • By default, the size of the output field is limited to 30 characters. This error occurs if a variable contains a string value longer than this limit : The default buffer size may be insufficient for larger strings.
  • Solution : Increase the buffer size to accommodate larger values. Setting it to 32,000 characters is a good practice to avoid this issue.
ceo@thomsonslegacy.com

We customize Oracle e-Business Suite R12 modules with Oracle Applications Framework and providing Oracle ADF based solutions


© Thomsonslegacy.com, 2025.
If you publish the materials of the site, the reference to the source is obligatory. The site uses cookies.