Полезные профили. Автоматизация установки

Продолжая тему полезных профилей.

Если каждый день приходится делать одни и те же операции, то возникает естественная потребность в автоматизации этого процесса. Именно эта потребность имеется ввиду, когда некоторые программисты уверяют, что "лень это двигатель прогресса".

Ну разумеется, кому понравится постоянно заходить в форму с системными профилями и устанавливать для них значения. Если нужно ежедневно устанавливать одни и те же значения для одних и тех же профилей, то проще написать небольшую программку, которая будет это делать.

В основе программы вызов функции fnd_profile.save
Небольшая "обертка" сверху позволяет сделать установку профиля на уровне пользователя еще более удобной (опять же подтверждая тезис о лени).

DECLARE
   PROCEDURE set_profile_at_user_level (
      p_user_name fnd_user.user_name%TYPE
     ,p_user_profile_option_name fnd_profile_options_vl.user_profile_option_name%TYPE
     ,p_profile_option_value fnd_profile_option_values.profile_option_value%TYPE
   ) IS
      l_user_id fnd_user.user_id%TYPE;
      l_profile_option_name fnd_profile_options_vl.profile_option_name%TYPE; 
   BEGIN
      SELECT fu.user_id
        INTO l_user_id
        FROM fnd_user fu
       WHERE fu.user_name = p_user_name;
       
       SELECT fpo.profile_option_name
         INTO l_profile_option_name
         FROM fnd_profile_options_vl fpo
        WHERE fpo.user_profile_option_name = p_user_profile_option_name;
    
      IF Fnd_Profile.save (
         x_name => l_profile_option_name
        ,x_value => p_profile_option_value
        ,x_level_name => 'USER'
        ,x_level_value => l_user_id
      )
      THEN
         NULL;
      END IF;
   END set_profile_at_user_level;
BEGIN
   set_profile_at_user_level ('L_ELLISON', 'ВЕБ: время ожидания для сеанса', '300');
   set_profile_at_user_level ('L_ELLISON', 'Служебные программы: диагностика', 'Y');
   set_profile_at_user_level ('L_ELLISON', 'Цветовая схема Java', 'OLIVE');
   COMMIT;
END;

Проверочный запрос, показывает все профили установленные для пользователя

SELECT fpo.profile_option_name
      ,fpo.user_profile_option_name
      ,fpov.profile_option_value
  FROM fnd_profile_option_values fpov
      ,fnd_profile_options_vl fpo
 WHERE fpov.application_id = fpo.application_id
   AND fpov.profile_option_id = fpo.profile_option_id
   AND fpov.level_id = 10004
   AND fpov.level_value = (
          SELECT fu.user_id
            FROM fnd_user fu
           WHERE fu.user_name = 'L_ELLISON'
      ) 

Полезные профили

1. Если забота о ресурсах сервера вынудила вашего администратора ограничить время сессии в ОЕБС и вы устали заново вводить пароль возвращаясь с обеда, то на помощь приходит профиль:

"ВЕБ: время ожидания для сеанса" (profile_option_name = 'ICX_SESSION_TIMEOUT')

Значением профиля является время жизни сессии в минутах. Собственно этим профилем системный администратор и ограничивает время жизни сессии, но делает это на уровне Отделения. Нам же нужно поставить большее значение (зависит от вашей наглости :-)) на уровне пользователя.


2. Если вы устали вводить пароль APPS всякий раз при обращении к пунктам меню Справка -> Диагностика , то на помощь приходит профиль:

"Служебные программы: диагностика" (profile_option_name = 'DIAGNOSTICS')

В качестве значения нужно установить "Да"


3. Если вы хотите чтобы внешний вид экраннных форм для разных экземпляров системы (DEV, TEST, etc) явно различались друг от друга (ну чтобы не перепутать), то на помощь приходит профиль:

"Цветовая схема Java" (profile_option_name = 'FND_COLOR_SCHEME')

Значение выбираем из предложенного списка.


Разумеется речь идет не о Продуктивных экземплярах системы.
На Продуктиве консультантам делать нечего.
Речь идет об экземплярах, на которые у консультантов есть доступ (в том числе и пароль APPS), и которые они могут настроить под себя.

Файл->Экспорт

Замечательный пункт меню.
Позволяет получать огромное количество нестандартных отчетов из системы. Данные экспортируются в текстовый файл, в котором значения разделены табуляцией (tsv) и который отлично понимается Excel.

Но есть одно но. Экспорт доступен только из многострочных(многозаписных) блоков данных. А для однозаписных блоков этот пункт меню недоступен. А как же быть с формами где на экране одновременно только одна запись? Организации, Лица, Пользователи системы, Полномочия, Определения параллельных программ, и т.д., и т.д.

Самое удивительное в этом то, что с технической точки зрения для реализации экспорта строк нет никакой разницы сколько на экране записей у блока - одна или несколько. Это вам подтвердит любой, кто знаком с Oracle Forms.

Поиск "правды" принес следующие результаты.

Библиотека APPCORE.pll (номер строк ниже видимо может отличаться в зависимости от версии билиотеки, но сами строки врядли)
package body APP_SYNCH, 81 строка
package body APP_EXPORT, 327 строка

Обе эти строки имеют следующий вид:
if get_item_property(..., RECORDS_DISPLAYED) > 1 then

И вот именно из-за этих двух строк мы имеем то что имеем!

Строка из APP_SYNCH делает пункт меню "Файл->Экспорт" недоступным для однозаписных блоков и доступным для многозаписных. Дополнительное удивление вызывет то, что срабатывает эта синхронизация не при переходе в новый блок, а при переходе в новый элемент (WHEN-NEW-ITEM-INSTANCE)?! Но это уже так, мелочи.

А строчка из APP_EXPORT дополнительно гарантирует, что враг не пройдет. Ведь предыдущее ограничение можно легко обойти персонализацией, делая в WHEN-NEW-ITEM-INSTANCE пункт меню "Файл->Экспорт" доступным. И пункт меню становится действительно доступным в однозаписных блоках, только данные не экспортируются :-) Одним словом, честную кастомизацию сделать не получается.

Если же заменить эти две строки, так чтобы они имели вид:

if get_item_property(..., RECORDS_DISPLAYED) > 0 then

т.е. заменить "> 1" на "> 0"

А после этого перекомпилировать APPCORE.pll, то Экспорт будет доступен и будет работать из любых блоков!

Но конечно же, это unsupported.

Однако, если выбирать между здравым смыслом и unsupported...
Как всё-таки жаль, что бывает нужно выбирать между здравым смыслом и unsupported!

Напоследок еще пара аргументов в защиту здравого смысла:
1. Как часто в патчах изменяется APPCORE.pll? Вопрос скорее риторический, ибо происходить это должно крайне редко.
2. Ну даже если она поменялась, самое худшее что случится, так это то, что пункт меню "Экспорт" перестанет быть доступным из однозаписных блоков.

Вопросы безопасности

То что в ОЕБС для ведения пользователей не используются встроенные возможности СУБД Oracle всегда казалось подозрительным.
Но что на столько!!!

Желающие могут убедиться сами.
Достаточно загуглить:
oracle.apps.fnd.security.WebSessionManagerProc.decrypt

Внешнее соединение

Человек старой закалки (кто видел СУБД Oracle версии меньше чем 9) на вопрос о внешнем соединении в SQL запросе уверенно ответит, что где то нужно поставить "плюсик".

И это правда.
Не смотря на появившийся в 9-й версии синтаксис ANSI для внешних соединений, "плюсик" привычнее и роднее. Вот, только, не всегда помнишь куда ж его нужно поставить. Собственно далее идет памятка о постановке "плюсика" во внешнем соединении.

Предположим есть у нас две таблицы: FND_USER и HR_EMPLOYEES. Вообще-то, HR_EMPLOYEES это view, но пусть для упрощения немного побудет таблицей. Соединяются они по столбцу EMPLOYEE_ID, который присутствует в обеих. Причем в HR_EMPLOYEES это еще и первичный ключ, а в FND_USER этот столбец не является обязательным.

Соединение этих таблиц в запросе выглядит так:

SELECT fu.*
      ,he.*
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id

А теперь вопрос.
Где же ставить "плюсик"?
Ответ оказывается не однозначным и всё зависит от того, что мы хотим получить в запросе.

Допустим, нам нужно для ряда пользователей (скажем, имя которых начинается с 'S') определить их Фамилию Имя Отчество. Информация о ФИО содержится в столбце HR_EMPLOYEES.full_name
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id(+) = fu.employee_id
   AND fu.user_name LIKE 'S%'

Правило гласящее о том, что "плюсик" ставится с той стороны где может отсутствовать значение, иногда вводит в заблуждение.

Ведь в таблице HR_EMPLOYEES столбец employee_id является первичным ключом, и значение в нем отсутствовать не может в принципе. Однако "плюсик" нужно поставить именно с этой стороны.
В чём же дело.
В данном случае, мы говорим о том, что в указанной постановке задачи (для пользователя найти ФИО) таблица FND_USER "главнее". Мы изначально имеем дело со списком пользователей, для части из которых, нужно найти дополнительную информацию (ФИО), если она существует.
Почему она может не существовать?
Да потому, что у пользователя в таблице FND_USER может отсутствовать значение в столбце EMPLOYEE_ID. Значит для ряда записей в FND_USER (где employee_id IS NULL) в HR_EMPLOYEES можно ничего и не искать. Именно поэтому "плюсик" ставится со стороны таблицы HR_EMPLOYEES

Кстати.
Аналогом этого запроса является:

SELECT fu.user_name
      ,(SELECT he.full_name
          FROM hr_employees he
         WHERE he.employee_id = fu.employee_id)
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'S%'

В таком виде наиболее очевидно, что справочник пользователей "главнее". Ведь FND_USER единственная таблица во фразе FROM. А информация о ФИО вытаскивается подзапросом, который либо её найдет, либо нет.
Основным минусом такого запроса является то, что подзапрос позволяет вернуть только одно значение. А ведь нас могло бы интересовать не только full_name, но и employee_num, creation_date и т.д.

Другая ситуация.
Для ряда сотрудников, у которых фамилия начинается на 'А' нужно найти имена пользователей, с которыми они работают в системе. Имена пользователей содержатся в столбце FND_USER.user_name.
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id(+)
   AND he.full_name LIKE 'А%'

Запрос вроде похожий, однако "плюсик" переехал к таблице FND_USER.
В данном случае, мы говорим о том, что в указанной постановке задачи (для ряда сотрудников найти user_name) таблица HR_EMPLOYEES "главнее". Мы изначально имеем дело со списком сотрудников, для части из которых, нужно найти дополнительную информацию (user_name), если она существует.
Почему она может не существовать?
Да потому, что для сотрудника из таблицы HR_EMPLOYEES может отсутствовать запись в таблице FND_USER с соответствующим значением столбца EMPLOYEE_ID. Значит для ряда записей в HR_EMPLOYEES мы ничего не найдем в FND_USER. Именно поэтому "плюсик" ставится со стороны таблицы FND_USER.

Однако и это еще не всё.
А что если мы хотим получить полный список и сотрудников, и пользователей зарегистрированных в системе. Теперь мы уже знаем, что для ряда сотрудников могут быть не найдены пользователи, а для ряда пользователей могут быть не найдены сотрудники. Т.е. "плюсик" нужен как бы с обеих сторон. Однако такой синтаксис команды не поддерживается.

Существуют разные способы переписать этот запрос по другому(без внешнего соединения), однако наша цель до конца разобраться именно во внешнем соединении.

Для реализации такого запроса необходимо полное внешнее соединение (FULL OUTER JOIN). И в этом случае придется воспользоваться синтаксисом ANSI.

А выглядеть это будет так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he FULL OUTER JOIN fnd_user fu
    ON he.employee_id = fu.employee_id

Подводя итоги, можно сказать следующее.
Если речь не идет о полном внешнем соединении, то нужно понять какая из таблиц является главной, а какая дополняющей. "Плюсик" в условии соединения таблиц (фраза WHERE) ставим со стороны дополняющей таблицы.

Ну и напоследок, чтобы окончательно запутаться.
А что если в запросе больше чем две таблицы? Допустим нам нужно для текущего пользователя (функция FND_GLOBAL.user_id) найти номер рабочего телефона сотрудника.

Информация о телефонах хранится в таблице PER_PHONES. Эта таблица связывается с HR_EMPLOYEES по условию:

   per_phones.parent_table = 'PER_ALL_PEOPLE_F'
   AND per_phones.parent_id = hr_employees.employee_id

Но при этом очевидно, что не у всех сотрудников есть записи о телефонах.

Итоговый запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
      ,pp.phone_number
  FROM fnd_user fu
      ,hr_employees he
      ,per_phones pp
 WHERE /* Соединяем fnd_user и hr_employees */
       he.employee_id(+) = fu.employee_id
       /* Соединяем hr_employees и per_phones */
   AND pp.parent_id(+) = he.employee_id
   AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
       /* Прочие ограничения */
   AND pp.phone_type(+) = 'W1' -- рабочий телефон
   AND SYSDATE BETWEEN pp.date_from(+) AND NVL(pp.date_to(+), SYSDATE) -- актуальная запись
   AND fu.user_id = FND_GLOBAL.user_id -- для текущего пользователя

Отметим следующее.
Таблицы соединяем попарно.
В каждой паре определяем главную и дополняющую таблицу.
В паре fnd_user и hr_employees главная - fnd_user, поэтому "плюсик" со стороны hr_employees.
В паре hr_employees и per_phones главная hr_employees, поэтому "плюсик" ставится со стороны per_phones и что особенно важно "плюсик" не ставится со стороны hr_employees.
В прочих условиях "плюсик" ставится для тех таблиц, которые хоть где-нибудь были дополнительными и не ставится у главной таблицы запроса.

Поиск полномочий для запуска параллельной программы

Если нужно выполнить параллельную программу, запускать которую еще не доводилось,
то для начала не плохо было бы понять из каких полномочий она доступна.

Вот болванка запроса для этих целей.
Отметим, что имя параллельной программы нужно задать дважды:

SELECT fr.responsibility_name
      ,(SELECT fa.application_name
          FROM fnd_application_vl fa
         WHERE fa.application_id = fr.application_id) AS application_name
      ,fr.responsibility_id
      ,fr.application_id
  FROM fnd_responsibility_vl fr
 WHERE SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date, SYSDATE)
   AND (fr.group_application_id, fr.request_group_id) IN (
           SELECT frgu.application_id
                 ,frgu.request_group_id
             FROM fnd_request_group_units frgu
            WHERE (frgu.request_unit_type = 'P' -- Program
                   AND 
                  (frgu.unit_application_id, frgu.request_unit_id) IN (
                      SELECT fcp.application_id
                            ,fcp.concurrent_program_id
                        FROM fnd_concurrent_programs_vl  fcp
                       WHERE  fcp.user_concurrent_program_name LIKE '%' 
                   ) 
                   )OR
                   (frgu.request_unit_type = 'A' -- Application
                    AND frgu.unit_application_id IN (
                       SELECT fcp.application_id
                         FROM fnd_concurrent_programs_vl  fcp
                        WHERE fcp.user_concurrent_program_name LIKE '%'
                    )
                    )
       )
 ORDER BY fr.responsibility_name

FND_CONCURRENT_REQUESTS

Параллельные программы в ОЕБС (concurrent programs) - замечательная особенность системы. Любые отчеты и процедуры обработки данных всегда оформляются, а затем и выполняются используя единый механизм. Так ведь мало того, всё это еще и протоколируется.

Поэтому, говоря о параллельных программах, мы можем ответить не только на вопросы популярной телепередачи, но и понять кто, сколько и во сколько :-)

Анализ запуска параллельных программ может быть полезен для ответов на многие вопросы.
Ну например:
1) Списки наиболее часто используемых отчетов
2) Списки наиболее медленно работающих отчетов
3) Какие программы работали в заданный промежуток времени.
4) Кто и что "забило" очередь конкарент менеджера
и т.д.

Специалисты поддержки разбирая жалобы пользователей на плохую работу того или иного отчета, могут предварительно проанализировать когда, с какими параметрами, из под каких полномочий этот пользователь запускал отчет (да и запускал ли?).

И всё это счастье доступно в одной таблице, имя которой FND_CONCURRENT_REQUESTS.

Вот заготовка запроса к этой таблице.
Во фразе WHERE подготовлены (закомментарены) наиболее популярные виды выборок: по пользоватедю, по периоду, по параллельной программе.

SELECT
       fcr.request_id 
      ,(SELECT fcp.user_concurrent_program_name
          FROM apps.fnd_concurrent_programs_vl fcp
         WHERE fcp.application_id = fcr.program_application_id
           AND fcp.concurrent_program_id = fcr.concurrent_program_id
       ) AS concurrent_name
      ,fcr.actual_start_date 
      ,fcr.actual_completion_date
      ,fcr.completion_text
      ,(SELECT fu.user_name FROM applsys.fnd_user fu 
          WHERE fu.user_id = fcr.requested_by
       ) AS user_name
      ,(SELECT fr.responsibility_name
          FROM apps.fnd_responsibility_vl fr
         WHERE fr.application_id = fcr.responsibility_application_id
           AND fr.responsibility_id = fcr.responsibility_id
       ) AS responsibility_name
      ,fcr.argument_text 
      ,fcr.*
  FROM applsys.fnd_concurrent_requests fcr
 WHERE 1=1
/* Start. Фильтр по пользователю */
--    AND fcr.requested_by IN (
--          SELECT fu.user_id 
--            FROM applsys.fnd_user fu 
--           WHERE fu.user_name LIKE '%'
--        )
/* End. Фильтр по пользователю */
/* Start. Фильтр по периоду */
--   AND fcr.actual_start_date >= TO_DATE('01.01.2008', 'DD.MM.YYYY')
--   AND fcr.actual_completion_date < TO_DATE('31.12.2008', 'DD.MM.YYYY')
/* End. Фильтр по периоду */
/* Start. Фильтр по concurrent_name */
--    AND (fcr.program_application_id, fcr.concurrent_program_id) IN (
--          SELECT fcp.application_id, fcp.concurrent_program_id 
--            FROM apps.fnd_concurrent_programs_vl fcp
--           WHERE fcp.user_concurrent_program_name LIKE '%'
--        )
/* End. Фильтр по concurrent_name */
/* Start. Фильтр по работающим сейчас конкарентам */
--   AND fcr.phase_code = 'R'
/* End. Фильтр по работающим сейчас конкарентам */
ORDER BY fcr.request_id DESC

Поиск полномочий назначенных пользователям

Экранная форма для заведения новых пользователей и выдачи/отбора полномочий не дает возможности посмотреть у кого есть то или иное полномочие. Для этих целей есть отчет в системе. Ну неужели мы будем запускать отчет, когда это можно сделать несложным запросом:

SELECT fu.user_name
      ,fr.responsibility_name
      ,furg.start_date
      ,furg.end_date 
  FROM fnd_user_resp_groups furg
      ,fnd_user fu
      ,fnd_responsibility_vl fr
 WHERE furg.user_id = fu.user_id
   AND furg.responsibility_application_id = fr.application_id
   AND furg.responsibility_id = fr.responsibility_id
   AND fu.user_name LIKE '%' -- Поиск по пользователю
   AND fr.responsibility_name LIKE '%' -- Поиск по полномочию
   AND SYSDATE BETWEEN NVL(furg.start_date,SYSDATE) 
                   AND NVL(furg.end_date,SYSDATE) -- только активные

ORA-01427: подзапрос одиночной строки возвращает более одной строки

Приходилось сталкиваться с такой ошибкой?
Читаем дальше.

Зачем же использовать такие подзапросы, коль возможны ошибки?
Но ведь удобно же!

Пример.
Ест у нас запрос, который скажем выводит некий список расходных транзакций модуля Inventory

SELECT ...
  FROM mtl_material_transactions mmt
 WHERE ...

Нам здесь не важно, что выводит этот список и по какому критерию, но нужно отметить, что за многоточиями может скрываться не один десяток, а то и не одна сотня, строк кода.

Но вот возникла необходимость добавить в запрос еще одну колонку - счет ГК с которого списали ТМЦ. Мы знаем, что в таблице mtl_transaction_accounts по коду складской транзакции можно найти две полупроводки, одна с положительной суммой (дебет), другая с отрицательной (кредит). Ну вот значит счет кредитовой полупроводки нас и интересует. Самым простым способом "вклиниться" в существующий запрос будет что-то такое:

SELECT ...
      ,(SELECT mta.reference_account
          FROM mtl_transaction_accounts mta
         WHERE mta.transaction_id = mmt.transaction_id
           AND mta.base_transaction_value < 0
       ) AS "Счет учета ТМЦ"
  FROM mtl_material_transactions mmt
 WHERE ...

Запускаем - беда!
ORA-01427: подзапрос одиночной строки возвращает более одной строки

Почему возникла ошибка?
Для некоторых складских транзакций наши предположения оказались неверны.
И это плохо.
А плохо потому, что проблемными могут оказаться всего лишь пара записей из нескольких тысяч, а мы не увидим ничего, т.к. весь запрос возвращает ошибку.

Прежде чем начинать исправить ситуацию, нужно понять а какой собственно результат запроса был бы приемлемым, учитывая наличие складских транзакций с неожиданными распределениями(проводками)?

А хотелось бы, чтобы запрос таки отработал, и все сотни, а то и тысячи (а то и больше) "правильных" записей мы увидели, а для тех нескольких ошибочных пусть вернется хоть что-нибудь - мы с ними отдельно разберемся, главное чтобы их отличить от правильных можно было.

Теперь приступаем.
Итак для того чтобы ошибка не возникала, нужно чтобы там где подзапрос возвращает несколько записей - возвращалась одна.

Одна запись из нескольких может получиться при использовании групповых функций.
Ну что же:

SELECT ...
      ,(SELECT MAX(mta.reference_account)
          FROM mtl_transaction_accounts mta
         WHERE mta.transaction_id = mmt.transaction_id
           AND mta.base_transaction_value < 0
       ) AS "Счет учета ТМЦ"
  FROM mtl_material_transactions mmt
 WHERE ...

Однако. Как оказалось, вернуть что-нибудь - не проблема, проблема потом понять что получили. Применив групповую функцию MAX мы гарантируем, что ошибки ORA-01427 больше не будет. Какой-нибудь счет да вернется. Но при таком подходе, мы никогда и не узнаем, что у нас есть записи с некорректно
определенным счетом.

Тем не менее, главный шаг к правильному решению уже сделан, осталось чуть-чуть. Ведь во всех случаях, где подзапрос возвращает одну запись, использование MAX не является ошибкой - максисум от одного значения равен самому значению. Значит нам нужно в тех случаях, где подзапрос возвращает одну запись - использовать
MAX (ну хотите MIN). А там где больше чем одну - возвращать значение, указывающее на ошибку.

Так ведь это же совсем не сложно сделать!
Количество записей подзапроса - это COUNT(*), условную логику можно реализовать через CASE или, по старинке, через DECODE. Не забудем и про то, что подзапрос может совсем не вернуть записей:

  
SELECT ...
      ,(SELECT DECODE(COUNT(*),  1,MAX(mta.reference_account),  0,NULL,  -999)
          FROM mtl_transaction_accounts mta
         WHERE mta.transaction_id = mmt.transaction_id
           AND mta.base_transaction_value < 0
       ) AS "Счет учета ТМЦ"
  FROM mtl_material_transactions mmt
 WHERE ...

Всё.
Теперь не только ошибка ORA-01427 больше не появится, но и можно легко найти те записи, где наша логика определения счета учета ТМЦ дала сбой.

Дополнительно отметим, что так как mta.reference_account имеет числовой тип данных, то и ошибочное значение должно быть числовым (-999). Для строковых типов данных можно было бы использовать - 'Ошибка' или 'ORA-01427'. Для дат - что-то из далекого прошлого или будущего. Важно лишь, чтобы такого значения гарантированно не было в реальных данных.

Подводим итоги.
При использовании подзапросов вместо

SELECT
       (SELECT t2.column
          FROM table2
         WHERE ...)
  FROM table1 t1
 WHERE ...

лучше использовать

SELECT
       (SELECT DECODE(COUNT(*), 0,NULL, 1,MAX(t2.column), 'ORA-01427')
          FROM table2
         WHERE ...)
  FROM table1 t1
 WHERE ...

И не забыть разобраться почему появились записи с 'ORA-01427'

Forms. Особенности режима ввода запроса

Режим ввода запроса это то, что делает Forms продуктом с "интуитивно непонятным пользовательским интерфейсом" с одной стороны, и замечательная, очень функциональная особенность с другой стороны.

Разработчику нужно лишь правильно привязать блок с его элементами(полями) к источнику данных(грубо, к таблице и столбцам), а Forms выдает приложение(форму), с отлично реализованной функцией поиска.

В чем же состоит эта реализация.

Для того чтобы выполнить поиск пользователю нужно запомнить и уметь нажимать на две "магические" клавиши [Ввести запрос] и [Выполнить запрос]. В зависимости от выбранной раскладки клавиатуры ими часто бывают F11/Ctrl-F11 или F7/F8 (задаются/меняются на сервере приложений в текстовом файле frmweb.res)

IMHO, именно необходимость запоминания и делает Forms интуитивно непонятным, ибо интуитивно понятным выглядит движение мыши в сторону кнопки с надписью "Найти".

Так какие же горизонты открываются перед пользователем, после того как клавиша [Ввести запрос] нажата либо им, либо заботливой рукой рядом стоящего консультанта.

Для начала отметим, что действия пользователя в режиме ввода запроса сводятся к тому, чтобы вводить шаблоны значений в поля формы. При выполнении запроса, итоговая фраза WHERE будет содержать отдельное условие для каждого поля, в которое вводился шаблон. Отдельные условия будут соединены через логическое AND.
Здесь рассказывается о том, как узнать какой же в итоге запрос выполнялся.

Ниже перечислены возможности режима ввода запроса, начиная с широко известных и продолжая менее именитыми:

1. Шаблон поля не содержит символов % и _ при этом не начинается с < > = != # :
Условие строится по правилу:
WHERE поле = 'шаблон'

Используется когда нужно найти все записи по точному значению поля.

Пример

Поле       Тип    Шаблон поиска Условие
---------- ------ ------------- --------------------
USER_NAME  Строка SYSADMIN      WHERE USER_NAME = 'SYSADMIN'
ENTERED_DB Число  234.12        WHERE ENTERED_DB = 234.12
START_DATE Дата   01.07.08      WHERE START_DATE = TO_DATE('01.07.08','DD.MM.RRRR')*

*Формат даты для функции TO_DATE задается параметром FORMS60_USER_DATE_FORMAT и обычно выставляется в 'DD.MM.RRRR'.

2. Шаблон поля содержит символы % и/или _ при этом не начинается с < > = != # :
Условие строится по правилу:
WHERE поле LIKE 'шаблон'

Используется когда нужно найти все записи по заданному шаблону,
где % заменяет любое количество символов, а _ заменяет любой один символ.

Чаще всего используется для поиска по известной подстроке.
Для дат можно хитрым образом задавать диапазоны, в примере ниже показан поиск всех дат за месяц.

Пример

Поле        Тип     Шаблон поиска  Условие
----------  ------  -------------  --------------------
USER_NAME   Строка  SYS%           WHERE USER_NAME LIKE 'SYS%'
ENTERED_DB  Число   234.%          WHERE ENTERED_DB LIKE '234.%'
START_DATE  Дата    %.07.2008      WHERE TO_CHAR(START_DATE, 'DD.MM.RRRR' LIKE '%.07.2008')

3. Для чисел и дат работают операторы сравнения
Для этого шаблон должен начинаться с >, >=, <, <=, <>
или для поиска по диапазону значений
#BETWEEN min AND max

Пример

Поле       Тип    Шаблон поиска     Условие
---------- ------ -------------     --------------------
ENTERED_DB Число  >234              WHERE ENTERED_DB > 234
ENTERED_DB Число  <=0               WHERE ENTERED_DB <= 0
ENTERED_DB Число  #BETWEEN 0 AND 10 WHERE ENTERED_DB BETWEEN 0 AND 10
START_DATE Дата   <>01.07.2008      WHERE START_DATE <> TO_DATE('01.07.2008','DD.MM.RRRR')

4. Для любых типов данных работает проверка на неопределенные(пустые) значения
Для этого используется один из шаблонов #IS NULL или #IS NOT NULL

Пример

Поле        Тип    Шаблон поиска Условие
----------  ------ ------------- ----------------------------
START_DATE  Дата   #IS NULL      WHERE START_DATE IS NULL
DESCRIPTION Строка #IS NOT NULL  WHERE DESCRIPTION IS NOT NULL

5. Шаблоны, начинающиеся с # в общем случае.
Вообще то, после начального # можно использовать не только BETWEEN или IS [NOT] NULL, но и любой другой текст, подстановка которого сразу после имени поля приведет к правильно построенному условию.

Пример

Поле          Шаблон           Условие
----------    ------           --------------------
CREATION_DATE #>TRUNC(SYSDATE) WHERE CREATION_DATE > TRUNC(SYSDATE)
CODE          #<>RTRIM(CODE)   WHERE CODE <> RTRIM(CODE)

Отметим использование функций (TRUNC, RTRIM,...).
Причем функции могут быть не только системными, но и пользовательскими.

6. Венцом возможностей по построению запроса является использование диалога Query/Where.
По своей сути это возможность напрямую написать текст команды SELECT после фразы WHERE.

Для человека понимающего в синтаксисе команды SELECT это дает огромные возможности по формированию запроса и не только.

Но вот из-за этого "и не только" в ОЕБС начиная с версии 11.5.9 эта возможность обрезана, также как и пункт 5 (Использование # в общем случае). Дело в том, что и 5-й, и 6-й пункты позволяют пользователю включать в текст запроса пользовательские функции. А пользовательские функции, начиная с версии СУБД 8i, могут не только читать данные, но и ,окаймленные автономной транзакцией, изменять их.
А это серьезная дыра в безопасности. Ведь технически подкованный пользователь, сговорившись со специалистом службы поддержки, может творить в системе всё что угодно.
Второй будет как бы случайно писать функции, модифицирующие данные, а первый, как бы случайно их вызывать, выполняя невинные запросы в экранной форме.

Пресекается это переменной FORMS60_RESTRICT_ENTER_QUERY, выставляемой в TRUE на сервере приложений.

Подробнее об этом можно почитать в ноте
257788.1 - Enter Query + :a Does Not Open Query/Where Window ORA-1403

P.S. Некая ироничность свойственная этой заметке(да видимо и другим) является,
в первую очередь, состоянием души и никоим образом не призвана нанести моральный урон
любой категории читающих.

Дата создания экземпляра

Если нужно узнать когда был сделан тот или иной клон продуктива,
то это можно сделать запросом:

SELECT creation_date FROM fnd_apps_system


SQL запрос в экранной форме

Существует два способа определить SQL запрос, который был выполнен в экранной форме ОЕБС. Обычный способ, которым многие пользуются, и правильный.

Обычный способ заключается в том, чтобы выбрать пункт меню "Справка->Диагностика->Проверить..." и далее, в открывшемся окошке, в поле "Блок" поставить "SYSTEM", а в "Поле" - "LAST_QUERY".

Это работает правильно во многих случаях, однако, не во всех.

Возьмем, например, форму "Пользователи" (FNDSCAUS) и выполним в ней любой запрос. А теперь попробуем, используя "обычный" способ получить запрос из верхнего блока. Понятно что он должен строиться на таблице FND_USER. Однако "Справка->Диагностика->Проверить...", и далее "SYSTEM", и далее "LAST_QUERY" показывает совсем другой запрос.

Для тех кто знаком с Oracle Forms очевидно, что "обычный" способ выдает нам значение системной переменной SYSTEM.LAST_QUERY. Также очевидно, что SYSTEM.LAST_QUERY возвращает значение последнего выполненного запроса для любого из блоков формы (а не для того, в котором мы хотим). И не менее очевидно, что в формах содержащих несколько блоков, соединенных отношением "мастер-деталь" сначала выполняется запрос в мастер-блоке, а затем в детальном. Ну и уж точно понятно, что для получения запроса по пользователям SYSTEM.LAST_QUERY не подходит, а подходит функция GET_BLOCK_PROPERTY (<имя_блока>, LAST_QUERY). Вот с её помощью можно получить последний выполненный запрос в любом блоке, если знать его имя.

Воспользоваться этой функцией можно используя второй, правильный способ.
1. Сначала нужно поместить курсор в любое поле того блока,
для которого нужно получить последний запрос (например, "Пользователь").
2. Затем выбираем пункт меню "Справка->Диагностика->Свойства->Позиция".
3. В поле "Объект 1" находится имя блока, запоминаем его ("USER")
4. Меняем(выбираем из списка) значение поля "Объект" с "Элемент" на "Блок"
5. Меняем(выбираем из LOV) значение поля "Объект 1" на имя запомненного блока ("USER")
6. В поле "Свойство" выбираем "Last Query" (должно быть уже выбрано)
И вот теперь мы можем увидеть искомый запрос по таблице FND_USER.

Алиасы

При написании запросов мы используем алиасы для имен таблиц.

SELECT gcc.concatenated_segments
 FROM gl_je_lines gjl
     ,gl_code_combinations_kfv gcc
WHERE gjl.code_combination_id = gcc.code_combination_id
  AND ...

Алиасы настолько входят в нашу жизнь, что зачастую и в разговоре можно услышать
что-то вроде gcc, ccid, mmt, etc.
Так удобнее выражаться, коротко и кому надо - поймет.

Так почему бы не "научить" наш любимый редактор текста
(где мы пишем и выполняем запросы) этому языку.

Вот пример, как это можно сделать для SQL*Plus.
Добавим в login.sql следующие строчки:

DEFINE gcc="gl_code_combinations_kfv gcc"
DEFINE ccid="code_combination_id"
DEFINE gjl="gl_je_lines gjl"
DEFINE gjh="gl_je_headers gjh"
DEFINE mmt="mtl_material_transactions mmt"
...

Теперь тот же запрос написать гораздо проще:

SQL> select &cs
 2    from &gjl, &gcc
 3   where gjl.&ccid = gcc.&ccid
 4     and ...

Можно пойти дальше и сделать алиасы для "любимых" подзапросов:

DEFINE ccname="SELECT gcc_.concatenated_segments -
FROM gl_code_combinations_kfv gcc_ -
WHERE gcc_.code_combination_id"
DEFINE user="SELECT fu_.user_name FROM fnd_user fu_ WHERE fu_.user_id"

Вот тот же запрос, плюс информация о пользователе, создавшем запись:

SQL> select (&ccname=gjl.&ccid) AS account
 2        ,(&user=gjl.created_by) AS created_by
 3    from &gjl
 4   where ...

Discoverer. Статистика использования

Есть в Discoverer приятные штучки.
Например, табличка EUL5_QPP_STATS (мы говорим о версии 10g).
В этой табличке собрается информация о запусках пользователями отчетов.

Соответственно, простыми SQL запросами можно посмотреть насколько популярен Discoverer.
Вот пара примеров.

• Самые используемые отчеты:

SELECT
      eqs.qs_doc_name
     ,COUNT(*) AS total_runs
     ,TRUNC(MIN(eqs.qs_created_date)) AS first_date
     ,TRUNC(MAX(eqs.qs_created_date)) AS last_date
     ,TRUNC(MAX(eqs.qs_created_date)-MIN(eqs.qs_created_date))+1 AS days_in_use
     ,ROUND(COUNT(*)/(TRUNC(MAX(eqs.qs_created_date)-MIN(eqs.qs_created_date))+1),2) AS runs_per_day     
     ,COUNT(DISTINCT eqs.qs_created_by) AS num_users
 FROM eul10_us.eul5_qpp_stats  eqs
WHERE eqs.qs_doc_name NOT LIKE 'Workbook%'
GROUP BY eqs.qs_doc_name
ORDER BY COUNT(*) DESC

• Самые активные пользователи:

SELECT
      fu.user_name
     ,fu.description
     ,eqs.qs_doc_name
     ,COUNT(*) AS runs
     ,TRUNC(MIN(eqs.qs_created_date)) AS first_date
     ,TRUNC(MAX(eqs.qs_created_date)) AS last_date
     , SUM(COUNT(*)) over (PARTITION BY fu.user_name) AS total_runs
 FROM eul10_us.eul5_qpp_stats  eqs
     ,fnd_user fu
WHERE fu.user_id = TO_NUMBER(SUBSTR(eqs.qs_created_by,2))
  AND eqs.qs_doc_name NOT LIKE 'Workbook%'
GROUP BY fu.user_name, fu.description, eqs.qs_doc_name
ORDER BY total_runs DESC, user_name, runs DESC

Денормализация данных

Попробуем измерить её в ширину :-)

SQL> SELECT *
  2    FROM (
  3        SELECT c.owner, c.table_name, COUNT(*)
  4          FROM dba_tab_columns c
  5              ,dba_objects o
  6              ,applsys.fnd_oracle_userid fou
  7         WHERE o.owner = fou.oracle_username
  8           AND o.owner = c.owner
  9           AND o.object_name = c.table_name
 10           AND o.object_type = 'TABLE'
 11           AND fou.oracle_username NOT LIKE 'XX%'
 12         GROUP BY c.owner, c.table_name
 13         ORDER BY COUNT(*) DESC
 14  )
 15   WHERE ROWNUM <= 30
 16  /

OWNER      TABLE_NAME                       COUNT(*)
---------- ------------------------------ ----------
BIS        EDWCMPALLCLASSES                      729
IGF        IGF_AP_CSS_INTERFACE_ALL              689
EC         ECE_STAGE                             519
ONT        OE_LINE_ACKS                          507
ICX        ICX_CAT_ITEMS_GT                      500
ICX        ICX_CAT_UPLOAD_IT_DUMP                500
ENI        EDW_ITEMS_M                           495
IGF        IGF_AP_ISIR_INTRFACE_ALL              475
CSI        CSI_HISTORY_ARCHIVE                   473
ONT        OE_LINES_IFACE_ALL                    467
CS         CS_INCIDENTS_AUDIT_B                  432
AMS        AMS_LIST_ENTRIES                      416
AMS        AMS_IMP_SOURCE_LINES                  416
EGO        EGO_BULKLOAD_INTF                     402
IGF        IGF_AP_CSS_FNAR_ALL                   371
ONT        OE_BLANKET_LINES_HIST                 371
AMS        AMS_LIST_IMPORT_ERRORS                367
INV        MTL_SYSTEM_ITEMS_INTERFACE            358
ONT        OE_ORDER_LINES_HISTORY                357
AR         HZ_CUSTOMER_MERGE_LOG                 356
ICX        ICX_POR_UPLOAD_IT_DUMP                345
ONT        OE_HEADER_ACKS                        341
ONT        OE_ORDER_LINES_ALL                    340
ICX        ICX_CAT_ITEMS_TLP                     339
RLM        RLM_INTERFACE_LINES_ALL               339
INV        MTL_SYSTEM_ITEMS_B                    337
ONT        OE_HEADERS_IFACE_ALL                  330
IGF        IGF_AP_CSS_PROFILE_ALL                328
OSM        AS_IMPORT_INTERFACE                   327
QRM        QRM_SAVED_ANALYSES_ROW                327

30 строк выбрано.

ADF vs OAF

Не далее как 14.04.2008 Oracle выпустил разъяснения относительно того, на чем клиенты должны разрабатывать расширения для Oracle E-Business Suite - Note:563047.1 E-Business Suite Application Development using Oracle Application Framework (OAF) and Application Development Framework (ADF)

Вопрос всплыл в связи с грядущем наступлением "next generation Fusion Middleware technology stack". Как известно, Fusion Middleware основан на ADF, а пользовательский интерфейс ОЕБС разработан на Forms и OAF.

И каковы же рекомендации Oracle:

• Для разработки хорошо интегрированных расширений ОЕБС (т.е. обычные кастомизации) нужно использовать OAF

• Для разработки приложений использующих данные из ОЕБС и которые требуют специфические ОЕБС-овые возможности (гибкие поля, персонализация) опять же нужно использовать OAF

• Для разработки приложений для ОЕБС, которые не требуют тесной интеграции с ОЕБС и не требуют использования ОЕБС-совых штучек, типа гибкие поля, нужно использовать ADF 10g, а интеграцию с ОЕБС делать через SOA.

• Для разработки приложений, которые вообще не требуют никакой интеграции с ОЕБС и для которых нужен "AJAX-style rich client user interface" нужно использовать ADF 11g!!!!!

Какие можно сделать выводы:

• Текущая версия ADF 10g до сих пор не готова к тому, чтобы на ней делать серьезные приложения

• Вот уж действительно нужно быть "rich client" для того, чтобы принять решение о разработке нового приложения на ADF 11g зная что "ADF 11g is currently available in preview beta".

• Любопытно отметить, что в ноте рассматриваются и сравниваются только OAF и ADF. Т.е. похоже на то, что Oracle Forms в светлое будущее(next generation Fusion Middleware technology stack) не попадает.

Однако так и осталось непонятным сколько же еще лет потребуется Oracle для того, чтобы выпустить продуктивную версию next generation technology stack. Вопрос этот становится особенно актуальным в свете покупки Bea Systems, не самого последнего игрока на рынке продуктов для middleware.

Список полномочий для заданной операционной единицы (ORG_ID)

Запрос выдает список всех полномочий, у которых установлено заданное значение профиля
"НО: операционная единица" (ORG_ID) :

SELECT fr.responsibility_name
      ,hou.name AS operating_unit_name
      ,hou.organization_id AS org_id 
  FROM fnd_profile_option_values fpov
      ,hr_operating_units hou
      ,fnd_responsibility_vl fr
 WHERE profile_option_id = 1991 -- org_id
   AND level_id = 10003 -- Responsibility level
   AND hou.organization_id = TO_NUMBER(fpov.profile_option_value)
   AND fr.responsibility_id = TO_NUMBER(fpov.level_value)
   AND fr.application_id = TO_NUMBER(fpov.level_value_application_id)
   AND hou.name = '&operating_unit_name'
--   AND hou.organization_id = &org_id
/

Инициализация APPS-овой сессии

===== Start script =====
/* $Header: fnd_initialize.sql 11.5.10 2 15.04.2008 oebs-ru $ */
-- Назначение
--  Ининциализации APPS-овой сессии
-- Параметры
--  1 - имя пользователя OEBS. Регистр знаков не важен
--  2 - имя полномочий.
--      Регистр знаков важен
--      Используется LANGUAGE от текущей сессии
--      Возможно указание шаблона (для LIKE), типа 'Сис%админ%'
--      Если искомому шаблону удовлетворяют несколько полномочий,
--      то будет выбрано случайное
-- Особенности
--  Необрабатывается ситуация, когда одно и тоже имя полномочий используется
--  в разных приложениях.
--  В случае успешной инициализации, в логе скрипта выводятся имена
--  и ID (в скобках) для пользователя, полномочий и приложения
--
SET VERIFY OFF
SET FEEDBACK OFF
DEFINE user_name = "&&1"
DEFINE resp_name = "&&2"
DECLARE
  user_name_v fnd_user.user_name%TYPE := '&user_name';
  resp_name_v fnd_responsibility_tl.responsibility_name%TYPE := '&resp_name';
  --
  user_id_v fnd_user.user_id%TYPE;
  responsibility_id_v fnd_responsibility_tl.responsibility_id%TYPE;
  responsibility_name_v fnd_responsibility_tl.responsibility_name%TYPE;
  application_id_v fnd_responsibility_tl.application_id%TYPE;
   application_name_v fnd_application_tl.application_name%TYPE;
   security_group_id_v fnd_security_groups.security_group_id%TYPE
                          := Fnd_Global.security_group_id;

  err_msg_v VARCHAR2(255);
BEGIN                    
  err_msg_v := 'User "'||user_name_v||'" does not exist or inactive';
  SELECT fu.user_id
    INTO user_id_v
    FROM fnd_user fu
   WHERE fu.user_name = UPPER(user_name_v)
      AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE);
 
  err_msg_v := 'Responsibility "'||resp_name_v||'" does not exist or inactive';
  SELECT frt.application_id
         ,frt.responsibility_id
           ,frt.responsibility_name
        ,(SELECT fav.application_name
            FROM fnd_application_vl fav
           WHERE fav.application_id = fr.application_id
            ) AS application_name
    INTO application_id_v
         ,responsibility_id_v
           ,responsibility_name_v
           ,application_name_v
    FROM fnd_responsibility_tl frt
         ,fnd_responsibility fr
   WHERE fr.application_id = frt.application_id
      AND fr.responsibility_id = frt.responsibility_id
       AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date, SYSDATE)
      AND frt.responsibility_name LIKE resp_name_v
     AND frt.LANGUAGE = USERENV ('LANG')
     AND ROWNUM = 1;
   
  err_msg_v := 'Error in Fnd_Global.apps_initialize';
  Fnd_Global.apps_initialize (
     user_id => user_id_v
    ,resp_id => responsibility_id_v
    ,resp_appl_id => application_id_v
    ,security_group_id => security_group_id_v
  );

  DBMS_OUTPUT.PUT_LINE ('Session initialized');
  DBMS_OUTPUT.PUT_LINE (
       ' User          : '||UPPER(user_name_v)||' ('||user_id_v ||')'
   );
  DBMS_OUTPUT.PUT_LINE (
       ' Responsibility: '||responsibility_name_v||' ('||responsibility_id_v||')'
   );
  DBMS_OUTPUT.PUT_LINE (SUBSTR(
       ' Application   : '||application_name_v||' ('||application_id_v||')'
   ,1,255));
EXCEPTION
  WHEN OTHERS
  THEN
     DBMS_OUTPUT.PUT_LINE (err_msg_v);
     DBMS_OUTPUT.PUT_LINE (SUBSTR(sqlerrm,1,255));
END;
/
SET FEEDBACK ON
UNDEFINE user_name
UNDEFINE resp_name
===== End script =====

Скрипт

Эпиграф

Согласно известной армейской шутке
"Значение синуса в военное время может достигать и четырех!"

ОЕБС - сложная система. Здесь всё как на войне:

SQL> select * from fnd_dual;

     DUMMY
----------
         1
         1
         1
         1

P.S.
Note:1016620.6