Tuesday, September 18, 2012

R12 Upgrade: 'Function not available to this responsibility' when accessing Custom Form

1. Navigate to $ORA_CONFIG_HOME/10.1.2/forms/server/default.env
2. Create an entry for $CUSTOM_TOP directory.
3. Save the file and restart the middle tier services

Monday, September 17, 2012

Query to find Installed Applications in Oracle

SELECT   fatl.application_name,
         SUBSTR (fa.application_short_name, 1, 8) product,
         SUBSTR (fpi.product_version, 1, 14) VERSION,
         SUBSTR (fpi.patch_level, 1, 11) patch_level, fpi.status,
         flv.meaning status_meaning
    FROM fnd_application fa,
         fnd_product_installations fpi,
         fnd_application_tl fatl,
         fnd_lookup_values flv
   WHERE fa.application_id = fpi.application_id
     AND fatl.application_id = fa.application_id
     AND UPPER (flv.lookup_type) = 'APP_INSTALL_STATUS'
     AND fpi.status = flv.lookup_code
     AND flv.LANGUAGE = 'US'
     AND flv.lookup_type = 'APP_INSTALL_STATUS'
     AND fatl.LANGUAGE = 'US'
ORDER BY fa.application_short_name

Wednesday, September 12, 2012

Script that queries the Menu structure

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence