Анализ данных: 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  

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

Складские организации. Статус закрытия периодов

Как правило в системе есть несколько операционных единиц (ведь в мелких конторах ОЕБС не внедряется), а в каждой из них несколько складских организаций, причем в разных ORG_ID может быть разное количество складских организаций.

В отличии от Дебиторов/Кредиторов, где период закрывается сразу на всю операционную единицу, в Запасах периоды нужно закрывать в каждой конкретной складской организации.

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

Так вот оказалось, что наглядную картину можно получить одним запросом (чуть подправив).
А всё благодаря аналитическим функциям.

SELECT t.period_name
       -- Вместо 1,2,3 нужно подставить реальные значения.
       -- полный список складских ORG_ID: 
       -- SELECT DISTINCT operating_unit FROM org_organization_definitions
      ,MAX(DECODE(t.org_id, 1, t.NAME, NULL)) AS ORG_ID_1
      ,MAX(DECODE(t.org_id, 2, t.NAME, NULL)) AS ORG_ID_2
      ,MAX(DECODE(t.org_id, 3, t.NAME, NULL)) AS ORG_ID_3
       -- и так далее, для каждой ORG_ID
  FROM (
        SELECT oap.period_name
              ,ood.operating_unit AS org_id
              ,DECODE(oap.open_flag, 'N','Закрыто', 'Y','Открыто', oap.open_flag) 
               || ' - ' || ood.organization_code||' '||ood.organization_name AS NAME
              ,oap.period_start_date AS start_date
              ,oap.schedule_close_date AS end_date
              ,rank() OVER (
                  PARTITION BY oap.period_name, ood.operating_unit 
                      ORDER BY DECODE(oap.open_flag, 'N','Закрыто', 'Y','Открыто', oap.open_flag) 
                               || ' - ' || ood.organization_code||' '||ood.organization_name
               ) AS row_num
          FROM org_organization_definitions ood
              ,org_acct_periods oap
         WHERE oap.organization_id = ood.organization_id
           -- диапазон дат можно задавать в несколько периодов
           AND oap.period_start_date >= TO_DATE('01.01.2009', 'DD.MM.YYYY')
           AND oap.schedule_close_date <= TO_DATE('31.03.2009', 'DD.MM.YYYY')
           -- можно задать конкретный тип периода (если используется несколько)
           --AND oap.period_set_name = ''
           -- откинем "левые" органзицаии, типа мастер организации позиций и закрытые
           AND ood.organization_code <> '000'
           AND ood.disable_date IS NULL
         ORDER BY 4,5,2,3
  ) t
GROUP BY t.start_date, t.end_date, t.period_name, t.row_num
ORDER BY t.start_date, t.end_date, t.period_name, t.row_num

ОЕБС. Взгляд изнутри

А ведь для тех, кто не видел ОЕБС, будет весьма любопытно как "оно" устроено изнутри.

SQL> SELECT o.object_type, COUNT(*)
  2    FROM dba_objects o
  3        ,applsys.fnd_oracle_userid fou
  4   WHERE o.owner = fou.oracle_username
  5     AND fou.oracle_username NOT LIKE 'XX%'
  6   GROUP BY ROLLUP (o.object_type)
  7
SQL> /

OBJECT_TYPE          COUNT(*)
------------------ ----------
DATABASE LINK               6
EVALUATION CONTEXT         28
FUNCTION                   27
INDEX                   38740
INDEX PARTITION          1124
INDEXTYPE                   4
JAVA CLASS               1102
JAVA RESOURCE              42
JAVA SOURCE                 4
LIBRARY                     2
LOB                       596
MATERIALIZED VIEW         386
OPERATOR                    5
PACKAGE                 40756
PACKAGE BODY            39725
PROCEDURE                  30
QUEUE                     141
RULE                       29
RULE SET                   59
SEQUENCE                 9023
SYNONYM                 30325
TABLE                   21835
TABLE PARTITION           500
TRIGGER                  3523
TYPE                      792
TYPE BODY                  30
VIEW                    25767
                       214601

В версии 11.5.10 легко можно найти пару сотен тысяч объектов БД.
И это без MRC.

Сторно журналов ГК

Журналы ГК можно сторнировать.
Более того, можно делать сторно 'на сторно'.
И нет ничего удивительного в том, что можно делать сторно 'на сторно на сторно'.

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

SQL> SELECT LEVEL
  2        ,gjh.je_header_id
  3        ,gjh.reversed_je_header_id
  4        ,gjh.accrual_rev_status
  5        ,gjh.accrual_rev_je_header_id
  6    FROM gl_je_headers gjh
  7    START WITH gjh.je_header_id = 219693 AND gjh.accrual_rev_status = 'R'
  8    CONNECT BY PRIOR gjh.je_header_id = gjh.reversed_je_header_id
  9  /

     LEVEL JE_HEADER_ID REVERSED_JE_HEADER_ID ACCRUAL_REV_STATUS ACCRUAL_REV_JE_HEADER_ID
---------- ------------ --------------------- ------------------ ------------------------
         1       219693                                        R                   219694
         2       219694                219693                  R                   219695
         3       219695                219694                  R                   219697
         4       219697                219695                                      219695

Столбец ACCRUAL_REV_STATUS.
Значение 'R' говорит о том, этот журнал был сторнирован
Значение NULL - журнал не был сторнирован

Столбец REVERSED_JE_HEADER_ID.
Значение NULL говорит о том, что это исходный в нашей цепочке журнал. Значение отличное от NULL - это JE_HEADER_ID того журнала, который был сторнирован этим журналом

Столбец ACCRUAL_REV_JE_HEADER_ID.
Если журнал был сторнирован (ACCRUAL_REV_STATUS='R'), то это JE_HEADER_ID того журнала, который сторнировал текущий. Но только если текущий журнал был сторнирован. Как видим у последнего несторнированного журнала ACCRUAL_REV_JE_HEADER_ID не пустой, а значение совпадает с REVERSED_JE_HEADER_ID.

Полезные клавиши

Многие считают плохим интерфейс экранных форм в ОЕБС.
Имеются ввиду формы сделанные на Oracle Forms.

Заблуждение.

Интерфейс не плохой, он другой.
Им просто нужно уметь пользоваться.

Shift-F5 - Дублировать поле (конкретные клавиши на клавиатуре могут отличаться в зависимости от настроек сервера форм. Полный список: Справка->Использование клавиатуры)
Если нужно ввести несколько похожих записей, то это просто незаменимая клавиша.
Позволяет вставить в текущее поле значение из предыдущей записи.

Shift-F6 - Дублировать запись
Позволяет дублировать все поля предыдущей записи.
К сожалению, мало где работает, из-за лени/квалификации конкретных разработчиков форм. При разработке форму нужно чуть-чуть подкрутить для корректного дублирования записи.

Ctrl-E - Редактировать
Значение поля слишком большое и на экране всё не помещается?
Открываем редактор и смотрим.

Чисто ОЕБС-овые штучки:
Ctrl-L - Список значений
В Навигаторе не помните в каком меню/подменю запрятана нужная форма?
Открываем Список значений и смотрим на все доступные формы этих полномочий.

Досье или Папки
Суперфункциональность.
Позволяет настроить внешний вид данных (количество, порядок, размеры и заголовки столбцов), а также сортировку и фильтр данных.
Надоело при вводе счетов фактур пролистывать колонки до "Метода платежа"?
Можно создать несколько досье в этой форме для более удобной работы, например:
- Предоплаты.
Выводятся только предоплаты и колонки нужные для заполнения. Остальные колонки скрываем
- Стандартные счета-фактуры
И т.д.

Интерфейс не плохой, он другой.
Им просто нужно уметь пользоваться.

FND: Override Directory

Интересный профиль для разработчиков.

Если нужно поэкспериментировать с формой, так чтобы это не было заметно остальным, то можно выставить на уровне пользователя профиль "FND: Override Directory" (он же "БОП: переопределение каталога"), указав в нем каталог на сервере приложений с модифицированной формой.

Файл -> Экспорт. Продолжение

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

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

Совершенно непонятно зачем так делать.
Кажется очевидным, что лучше выгрузить всё что есть, а пользователь затем сам в Excel оставит нужное.

Так вот оказалось, что не всё так плохо.
Просто "не повезло" в том, что пришлось иметь дело с 11i.ATG_PF.H Rollup 3.
А именно в нем используется такой алгоритм экспорта.

В более свежих версиях, вообще сделано красиво.
При помощи небольшой персонализации можно устанавливать псевдо-свойство блока EXPORT_HIDDEN_CANVASES, которым можно задавать всё ли столбцы блока будут экспортироваться или только видимые.

Ну а счастливые обладатели 12 версии могут еще играться профилем "FND Export All Block Data"

Подробности здесь: 391414.1 Export Behavior in Oracle Applications Forms

Профили на полномочия

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

DECLARE
   PROCEDURE set_profile_at_resp_level (
      p_resp_name fnd_responsibility_vl.responsibility_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_responsibility_id fnd_responsibility_vl.responsibility_id%TYPE;
      l_application_id fnd_responsibility_vl.application_id%TYPE;
      
      l_profile_option_name fnd_profile_options_vl.profile_option_name%TYPE; 
   BEGIN
      SELECT fr.responsibility_id
            ,fr.application_id
        INTO l_responsibility_id
            ,l_application_id
        FROM fnd_responsibility_vl fr
       WHERE fr.responsibility_name = p_resp_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 => 'RESP'
        ,x_level_value => l_responsibility_id
        ,x_level_value_app_id => l_application_id
      )
      THEN
         NULL;
      END IF;
   END set_profile_at_resp_level;
BEGIN
   set_profile_at_resp_level ('Системный администратор', 'Служебные программы: диагностика', 'Y');
   -- set_profile_at_resp_level ('...', 'НО: операционная единица', '...');
   COMMIT;
END;