Oracle Integration Repository

Интеграционный репозиторий - это собранные в одном месте описания всех открытых интерфейсов ОЕБС по всем модулям системы.

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

Для версии 11.5.10 репозиторий доступен на сайте irep.oracle.com, а в 12 версии он уже встроен в систему. Для доступа нужно выбрать полномочие "Интегрированный шлюз SOA", в котором существует единственный пункт меню "Интеграционный репозиторий".

Oracle SQL Developer 2.1

В декабре 2009 года вышла новая версия SQL Developer - 2.1

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

Т.е. с основной задачей консультанта ОЕБС при работе с БД Oracle - выполнение запросов и сохранение результатов - эта версия справляется вполне успешно.

Это вовсе не признание заслуг SQL Developer.
Это признание того факта, что у Oracle появился свой собственный работоспособный продукт, в котором можно  выполнять запросы.

А самый главный его плюс - бесплатное использование - перевешивает все минусы.

Резюме.
SQL Developer 2.1 переведен в опытно-промышленную эксплуатацию.

Анализ данных: DISTINCT vs GROUP BY

При анализе содержимого таблиц часто нужно посмотреть на распределение значений в каком-либо столбце.
Например, чтобы узнать в каких валютах нам выставляют счета поставщики, нужно проанализировать содержимое столбца INVOICE_CURRENCY_CODE таблицы AP_INVOICES_ALL.
Для этого выполняем запрос:

SQL> SELECT   DISTINCT ai.invoice_currency_code
  2    FROM   ap_invoices_all ai;


INVOICE_CURRENC
---------------
RUR
CHF
GBP
EUR
SEK
USD
XDR

Однако это не самое лучшее решение для анализа.
Куда больше информации можно было бы получить переписав запрос так:

  1  SELECT   ai.invoice_currency_code, COUNT(*)
  2    FROM   ap_invoices_all ai
  3*  GROUP BY ROLLUP (ai.invoice_currency_code)
SQL> /


INVOICE_CURRENC   COUNT(*)
--------------- ----------
CHF                     50
EUR                    882
GBP                    153
RUR                 700121
SEK                     45
USD                   5545
XDR                   3641
                    710437

Теперь мы видим не только уникальные коды валют, но и их распределение по строкам, а в конце и общее число записей в таблице(потрясающий эффект от rollup). Да и для Oracle такой запрос совсем не в тягость, тот же объем работы, что и с DISTINCT.

А анализ на этом только начинается.
Увидев распределение значений (особенно когда их много), сразу становится понятно, что для начала нам нужны самые распространенные. Тогда мы добавляем в запрос ORDER BY:

SELECT   ai.invoice_currency_code, COUNT(*)
  FROM   ap_invoices_all ai
 GROUP BY ROLLUP (ai.invoice_currency_code)
 ORDED BY 2 DESC

Если "мелочь" нас не интересует, то отсекаем её при помощи HAVING:

SELECT   ai.invoice_currency_code, COUNT(*)
  FROM   ap_invoices_all ai
 GROUP BY ROLLUP (ai.invoice_currency_code)
 HAVING COUNT(*) > 100


И всего этого мы бы так и не узнали пользуясь обычным DISTINCT.

Нет DISTINCT-у!
Даешь GROUP BY!

P.S. Приведенные результаты запросов - "ненастояшие".

Free download

Оказывается не все знают, что ОЕБС и документацию к нему можно совершенно легально скачать. Доступ на metalink при этом не нужен.

Документация находится здесь, а сам ОЕБС здесь.

Интуитивно непонятное

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

Ну никак не укладывается в голове, что она именно здесь:
Защита -> Полномочия -> Запрос

Пусть трактор работает, он железный

"Пусть трактор работает, он железный".

Это поговорка уже из прошлого, двадцатого века. В наш двадцать первый, цифровой, слово "трактор" должно быть заменено на "компьютер".

Довольно часто бывает необходимо, чтобы какие-то работы в системе выполнялись автоматически, без участия человека.
Ну например. Eсли установку полезных профилей, добавление полномочий своему пользователю и т.д. нужно делать каждое утро на "свежем" экзмепляре системы, то зачем это делать руками?

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

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

Помимо этого потребуются две вещи.
Первое. Иметь элементарные навыки работы с SQL*Plus.
В частности подготовка и запуск скриптов.

Графические инструменты типа TOAD, SQL*Navigator, PL/SQL Developer, и т.д. хороши только тогда, когда нужно начать работу с запроса вида select * from ap_invoices_all, а затем доточить напильником.

Если нужно что-то делать автоматически, то за десятилетия существования СУБД Oracle
еще никто не придумал чего-то лучше, чем SQL*Plus.

И второе. Уметь воспользоваться, говоря по нашему, по ОЕБээСовскому, конкарент менеджером операционной системы. В Windows (XP) он где-то здесь:
Пуск -> Настройка -> Панель управления -> Назначенные задания.

Некоторые советы по организации процесса.
1. Сделать отдельную папку на диске (типа c:\autorun), которую сделать "Рабочей папкой" в назначенном задании. Именно в эту папку помещать все необходимые файлы.

2. В задании запускать не напрямую sqlplus, а командный файл.
Это удобно чтобы избежать ошибок при передачи параметров в sqlplus

Например, поместить в папку c:\autorun файл run.cmd.
В run.cmd - одна строка:

sqlplus apps/pwd@dbname @autorun.sql

,где
pwd нужно заменить на правильный пароль
dbname - на название экземпляра системы
autorun.sql - собственно скрипт для sqlplus, где всё и происходит

3. Вот шаблон и комментарии к autorun.sql, который также находится в c:\autorun

col start_time for a20
col end_time for a20
col global_name for a30

SPOOL run.log
SELECT user 
      ,global_name
      ,TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS start_time
FROM global_name;

REM
REM Start here
REM

SELECT * FROM dual;

REM 
REM End here
REM
SELECT user
      ,global_name
      ,TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS end_time
  FROM global_name;
SPOOL OFF
EXIT

3.1. Обязательно нужно сделать SPOOL в файл.
Если возникнут сомнения в том, как отработал скрипт, да и отработал ли вообще, без лога не обойтись.
3.2. В начале и в конце работы полезно вывести информацию о том, во сколько всё началось, во сколько закончилось, а также кто и в какой базе это делал.
3.3. Ну а вместо SELECT * FROM dual; нужно вставить то, что нужно выполнить.

4. Когда файлы autorun.sql и run.cmd готовы, не поленитесь запустить run.cmd и убедиться, что всё работает как и ожидалось!
Если запускаете длительное задание, то для проверки закомментируйте "долгоиграющую часть", временно подставив SELECT * FROM dual;

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

Разумеется это лишь рекомендации. Файлы могут выглядеть и по другому.
Вот полезные ссылки, которые могут помочь освоить данный процесс на более продвинутом уровне:
П.Лузанов и В.Бегун. Understanding SQL*Plus. Конфигурации и запуск
П.Лузанов и В.Бегун. Understanding SQL*Plus. Использование переменных
В.Крачвук. Утилита SQL*Plus. Создание и выполнение сценариев. Часть I
В.Крачвук. Утилита SQL*Plus. Создание и выполнение сценариев. Часть II
В.Крачвук. Утилита SQL*Plus. Создание и выполнение сценариев. Часть III
В.Крачвук. Утилита SQL*Plus. Создание и выполнение сценариев. Часть IV

Patch_level

При заведении SR на metalink-е нужно указать patch_level используемого приложения (модуля).
Список приложений и нужные номера patch_level:

SELECT fa.application_id
      ,fa.application_short_name
      ,fa.product_code
      ,fpi.patch_level
  FROM fnd_product_installations fpi, fnd_application fa 
 WHERE fpi.application_id = fa.application_id
   AND fpi.patch_level IS NOT NULL
--    AND fa.application_short_name IN (
--        'INV', 'BOM', 'PO', 'FND', 'PA', 'PAY', 'PER'
--    )
 ORDER BY fa.application_short_name

Не по теме: Подвиг

С детства люблю все фильмы Марка Захарова, в том числе и про барона Мюнхгаузена.
И никогда не считал барона выдумщиком.

Ну вот смотрите. Визит к стоматологу очень для многих является героическим поступком.
Вот и в моем календаре имеется запись:
Понедельник, 09:00. Подвиг

ОЕБС Форум 2009

Кризис таки имеет положительные стороны.
Как известно, сейчас выигрывают эффективные решения: максимальный результат минимальными ресурсами.

Организаторам и участникам придется сегодня доказать что они к этому готовы.
Уложить 5-6 докладов в 1,5 часовые секции - это результат.

Сторно журналов ГК. Продолжение

В продолжении темы сторнирования журналов ГК.

Постановка задачи.
Получить список "честных" журналов ГК за период.
Что значит честных?
Предположим у нас 5 журналов. И пятый журнал оказался ошибочным (не важно по какой причине). Мы его сторнировали. Т.е. появился шестой журнал. Пятый и шестой - взаимно гасят друг друга и нам более не интересны. А вот первые 4 - это и есть список честных журналов. Из него исключены сторнированные журналы и то, чем их сторнировали.
Как получить такой список?

По идее, нам нужны несторнированные журналы (accrual_rev_status IS NULL). Однако под такое условие попадает и последний шестой журнал, а он - лишний.

Можно добавить условие, что журналы не должы быть сторнирующими (reversed_je_header_id IS NULL). И для нашего примера с шестью журналами этого достаточно. Однако нельзя исключать ситуацию, когда пользователь ошибся выполняя операцию сторнирования и сторнировал не то что нужно. А потом, поняв ошибку, сделал сторно на сторно. Т.е. появился 7-й журнал. И он нам нужен! 5-й и 6-ой гасят друг друга, а 7-й - "настоящий" и он нам нужен.

Здесь сделаем допущение, что из связки 5-6-7 журналы, нам нужен последний - 7-й. Хотя можно было бы говорить и об исходном - 5-ом.

Т.к. сторно на сторно можно делать не единожды, то в общем случае получается такая картина. Если есть цепочка сторнирования: исходный журнал(1)->сторно(2)->сторно(3)->сторно(4)->сторно(5)->сторно(6)-сторно(7)-... то нас в ней интересует последний журнал, но только в том случае, если он нечетный в цепочке.

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

SELECT * /* Обычные несторнированные и несторнирующие журналы */ 
  FROM gl_je_headers gjh 
 WHERE gjh.period_name = 'ЯНВ-2009'
   AND gjh.accrual_rev_status IS NULL
   AND gjh.reversed_je_header_id IS NULL
UNION ALL
SELECT * /* дополнительные сторнировочные журналы */ 
  FROM gl_je_headers gjh 
 WHERE gjh.period_name = 'ЯНВ-2009'
  AND gjh.accrual_rev_status IS NULL
  AND gjh.reversed_je_header_id IS NOT NULL
  AND MOD((SELECT COUNT(*)
             FROM gl_je_headers gjh2
            WHERE gjh.period_name = 'ЯНВ-2009'
            START WITH gjh2.je_header_id = gjh.je_header_id
           CONNECT BY gjh2.je_header_id = PRIOR gjh2.reversed_je_header_id)
   ,2) <> 0  

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