Did You Know? -- June 2002

By Hervé Deschamps, Oracle Corporation.

Sorry, I skipped one month. I was on a very cool vacation. :-)

Forms 6i

A little productivity trick for developers like me: associate files like .fmx or .fmb with the corresponding forms executable WITH a complete connection string. The idea is that I want to just double click on an .fmx and have it run without having to type connection information everytime. With .fmx, the executable is ifrun60.exe. It is easy to associate this file extention with  ifrun60.exe in Windows. But with NT I could not add a complete connection string like apps/apps@orcl in the association dialog. So I went in the registry: \HKEY_CLASSES_ROOT\Oracle.FormCompiled\shell\open\command and modified the (Default) value to: c:\orant\bin\ifrun60.EXE "%1" userid=apps/apps@ecad. Et voilà!


A bit of basic good old SQL does not hurt :-)
Can you guess what date format mask: 'fmDay "the" ddthsp "of" Month RRRR' will return?
SELECT TO_CHAR(SYSDATE,'fmDay "the" ddthsp "of" Month RRRR')
FROM dual

Thursday the eleventh of April 2002


This is an old trick, but worth reminding.
When I want to make sure that a select statement returns only one row without incurring additional performance costs, I add rownum=1 in the where clause:

  function get_employee_number(p_person_id in number,
                               p_date      in date) return varchar2 is
    v_return varchar2(30);
    select employee_number into v_return
    from per_people_f
    where person_id = p_person_id
      and p_date between start_date and end_date
      and rownum = 1;
    return v_return;
    when NO_DATA_FOUND then
      return null;
  end get_employee_number;

Apps HR

If Oracle Apps HR, if you need a list of pay elements that fit a certain search pattern, together with their values that users can populate, here is a simple query. In this
case I am looking for anything that could be an 'allocation'.

select element_name, inpval.name val_name
from  pay_element_types_f pet,
      pay_input_values_f inpval
where element_name like '%lloc%' and
      pet.element_type_id = inpval.element_type_id

Allocations Child               Time Unit
Allocations Child               Pro Rata
Allocations Child               Pay Value
Allocations Child Manual Auto   Allocations Child
Allocations Child Manual Auto   Allocations Training


Apps ZOOM in CUSTOM.pll

How to make use of the ZOOM button in the Apps toolbar:

Whenever the cursor changes block in the form, the form calls the ZOOM_AVAILABLE function in the CUSTOM library. If this function returns TRUE, then the Zoom entries on the menu and toolbar are enabled; if it returns FALSE, then they are disabled.

Here is a code example to enable the Zoom functionality in Custom.pll:
  function zoom_available return boolean is
    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');
    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
      return FALSE;
    end if;
  end zoom_available;

And an example of how to process the event in Custom.pll:
  procedure event(event_name varchar2) is
    form_name      varchar2(30) := name_in('system.current_form');
    block_name     varchar2(30) := name_in('system.cursor_block');
    if (event_name = 'ZOOM') then
       if (form_name = 'CSXSRISR' and block_name = 'INCIDENT_TRACKING') then
             copy('Spanner is Cool', 'INCIDENT_TRACKING.PRODUCT_NAME');
             fnd_message.set_string ('Je fais ici mes dérivations de données.');
       end if;
    end if;
  end event;

All this good stuff and more are detailed in the online manual: "Oracle Applications Developer's Guide ".

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.