Архив

Архив раздела ‘SQL’

Oracle Создание PARTITION BY RANGE по полю DATE

21 Ноябрь 2012 1 комментарий

Скрипт помогающий создать PARTITION BY RANGE по полю DATE

SET LINESIZE 150
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER SQLERROR CONTINUE
SPOOL C:\create_partition_date.log
declare
 iter number;
 l_date_start date := to_date('01-11-2011','dd-mm-yyyy');
 l_date_end   date := to_date('01-01-2023','dd-mm-yyyy');
 l_name_part  varchar2(20) := 'TEST';
 l_part_field varchar2(20) := 'MY_DATE';
begin
 dbms_output.put_line('PARTITION BY RANGE('||l_part_field||')');
 dbms_output.put_line('(');
 dbms_output.put_line(' PARTITION '||l_name_part
                        ||'_MIN     VALUES LESS THAN (to_date('''
                        ||to_char(add_months(l_date_start,-1),'dd-mm-yyyy')
                        ||''',''dd-mm-yyyy''))');
 for j in (
              with t as (
              select l_date_start d1,
                     l_date_end d2
              from dual
            )
            --
            select decode(level,1,d1,
                          trunc(add_months(d1,level-1),'mm')
                         ) as date_from
            from t
            connect by add_months(trunc(d1,'mm'),level-1) <= d2
           ) 
 loop
   dbms_output.put_line(',PARTITION '||l_name_part||'_'
                        ||to_char(add_months(j.date_from,-1),'mm_yyyy')
                        ||' VALUES LESS THAN (to_date('''
                        ||to_char(j.date_from,'dd-mm-yyyy')
                        ||''',''dd-mm-yyyy''))');
 end loop;
 dbms_output.put_line(',PARTITION '||l_name_part
                      ||'_MAX     VALUES LESS THAN (MAXVALUE)'); 
 dbms_output.put_line(')');
end;
/
spool off

PARTITION BY RANGE(MY_DATE)
(
 PARTITION TEST_MIN     VALUES LESS THAN (to_date('01-10-2011','dd-mm-yyyy'))
,PARTITION TEST_10_2011 VALUES LESS THAN (to_date('01-11-2011','dd-mm-yyyy'))
,PARTITION TEST_11_2011 VALUES LESS THAN (to_date('01-12-2011','dd-mm-yyyy'))
...
,PARTITION TEST_11_2022 VALUES LESS THAN (to_date('01-12-2022','dd-mm-yyyy'))
,PARTITION TEST_12_2022 VALUES LESS THAN (to_date('01-01-2023','dd-mm-yyyy'))
,PARTITION TEST_MAX     VALUES LESS THAN (MAXVALUE)
)
 
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to C:\create_partition_date.log

OEBS API: Создание поставщика и отделения

19 Ноябрь 2012 Нет комментариев

OEBS Пример использования API для создания поставщика и отделения поставщика:

declare
  l_vendor_rec      AP_VENDOR_PUB_PKG.r_vendor_rec_type;
  l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
  x_vendor_id       number;
  x_party_id        number;
  x_vendor_site_id  number;
  x_party_site_id   number;
  x_location_id     number;
  l_party_site_id   number;
  --
  x_return_status VARCHAR2(1);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(4000);
begin
  l_vendor_rec.SEGMENT1     := '1234575';
  l_vendor_rec.VENDOR_NAME  := 'MYTESTSUPPLIER2';
  l_vendor_rec.SUMMARY_FLAG := 'N';
  l_vendor_rec.ENABLED_FLAG := 'Y';

  AP_VENDOR_PUB_PKG.Create_Vendor(p_api_version   => 1,
                                  x_return_status => x_return_status,
                                  x_msg_count     => x_msg_count,
                                  x_msg_data      => x_msg_data,
                                  p_vendor_rec    => l_vendor_rec,
                                  x_vendor_id     => x_vendor_id,
                                  x_party_id      => x_party_id);

  if (x_return_status <> 'S') then
    dbms_output.put_line('ERROR in supplier creation');
    dbms_output.put_line('--------------------------------------');
  
    if x_msg_count = 1 then
      dbms_output.put_line(x_msg_data);
    else
      FOR i IN 1 .. x_msg_count LOOP
        dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                             fnd_api.g_false));
      END LOOP;
    end if;
  else
  
    l_vendor_site_rec.vendor_id            := x_vendor_id;
    l_vendor_site_rec.VENDOR_SITE_CODE     := 'MYHOME';
    l_vendor_site_rec.org_id               := 82;
    l_vendor_site_rec.COUNTRY              := 'RU';
    l_vendor_site_rec.ADDRESS_LINE1        := 'Lenina';
    l_vendor_site_rec.PURCHASING_SITE_FLAG := 'Y';
    l_vendor_site_rec.PAY_SITE_FLAG        := 'Y';
  
    AP_VENDOR_PUB_PKG.Create_Vendor_Site(p_api_version     => 1,
                                         x_return_status   => x_return_status,
                                         x_msg_count       => x_msg_count,
                                         x_msg_data        => x_msg_data,
                                         p_vendor_site_rec => l_vendor_site_rec,
                                         x_vendor_site_id  => x_vendor_site_id,
                                         x_party_site_id   => x_party_site_id,
                                         x_location_id     => x_location_id);
  
    if (x_return_status <> 'S') then
      dbms_output.put_line('ERROR in supplier site creation');
      dbms_output.put_line('--------------------------------------');
    
      if x_msg_count = 1 then
        dbms_output.put_line(x_msg_data);
      else
        FOR i IN 1 .. x_msg_count LOOP
          dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                               fnd_api.g_false));
        END LOOP;
      end if;
    else
      dbms_output.put_line('Supplier Site Created!');
    end if;
  
  end if;
  
  commit;
end;

OEBS API Параллельные программы — отправка, ожидание, скрытый режим

9 Ноябрь 2012 5 comments

OEBS API Запуск параллельной программы

declare
  l_request_id NUMBER;
  --
  p_app_name     VARCHAR2(100);
  p_request_name VARCHAR2(100);
begin
  l_request_id:=apps.fnd_request.submit_request
                         (application => p_app_name     -- код приложения
                         ,program     => p_request_name -- код параллельной программы
                         ,description => NULL           -- описание
                         ,start_time  => NULL           -- когда запустить программу
                         ,sub_request => FALSE          -- признак дочерней программы
                         ,argument1   => 1              -- параметр параллельной программы
                         -- ...
                         --,argument100 =>
                         );
  COMMIT;
  if l_request_id = 0 then
    --write_log('При отправке запроса произошла ошибка');
    raise fnd_api.g_exc_error;
  end if;                                               
end;

Если,
start_time = NULL, то запуск происходит сразу.
start_time = fnd_date.date_to_canonical(sysdate + (1/26/60)*5) — запуск через 5 минут

Читать дальше про “OEBS API Параллельные программы — отправка, ожидание, скрытый режим” »

Даты в oracle — получить первый/последний день

6 Ноябрь 2012 1 комментарий
with t as (select trunc(sysdate) d from dual)
--
select 'ГОД - первый день' descr,trunc(d,'YY') new_date from t
union all
select 'ГОД - последний день', add_months(trunc(d,'YY'),12)-1 from t
union all
select 'КВАРТАЛ - первый день', trunc(d,'Q') from t
union all
select 'КВАРТАЛ - последний день', trunc(add_months(d, 3), 'Q')-1 from t
union all
select 'МЕСЯЦ - первый день' ,trunc(d,'MM') from t
union all
-- LAST_DAY не изменяет время
select 'МЕСЯЦ - последний день',last_day(d) from t 
union all
-- какой день недели считается первым, зависит от параметра NLS_TERRITORY
select 'НЕДЕЛЯ - первый день', trunc(d,'D') from t 
union all
select 'НЕДЕЛЯ - последний день', trunc(d,'D')+6 from t

DESCR                    NEW_DATE
------------------------ -----------
ГОД - первый день        01.01.2012
ГОД - последний день     31.12.2012
КВАРТАЛ - первый день    01.10.2012
КВАРТАЛ - последний день 31.12.2012
МЕСЯЦ - первый день      01.11.2012
МЕСЯЦ - последний день   30.11.2012
НЕДЕЛЯ - первый день     05.11.2012
НЕДЕЛЯ - последний день  11.11.2012
 
8 rows selected
SQL> select (sysdate-trunc(sysdate-1)) day to second + time '00:00:00' as diff_time from dual;
 
DIFF_TIME
---------
15:32:2
Categories: SQL Tags: , , , ,

Трассировка сессии — event 10046

19 Сентябрь 2012 2 comments

Трассировка сессии Oracle помогает найти проблемные запросы и понять в чем именно причина.

Что именно будет записываться в трассировочный файл, определяется номером события (event).
Стандартный номер события 10046. Именно такой уровень устанавливается при выполнении команды

alter session set sql_trace=true;

Список данных событий и для чего они предназначены можно посмотреть здесь EventReference

 

Событие трассировки 10046 / Trace event 10046

Включить трассировку в текущей сессии

alter session set events '10046 trace name context forever, level 1';

выключить трассировку в текущей сессии

alter session set events '10046 trace name context off';

Уровни:

  • 0 — трассировка отключена
  • 1 — стандартная трассировка
  • 4 — добавлены значения связываемых (binds) переменных
  • 8 — добавлены события ожидания (waits)
  • 12 — связываемые переменные + события ожидания

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

sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');

Путь к директории, где появится файл трассировки

select value from v$parameter where name = 'user_dump_dest';

Задать имя для trace файла

alter session set tracefile_identifier = 'mytrace1';

 

Понимание трасировочного файла

Читать дальше про “Трассировка сессии — event 10046” »

Categories: SQL Tags: , , ,

Кто съел весь TEMP?

13 Сентябрь 2012 Нет комментариев

Запрос для просмотра сессий использующих TEMP

select s.sid
     , s.client_identifier
     , s.status
     , sum(round(u.blocks*8192/1024/1024,3)) "TEMP usage, Mb"
     , s.osuser, s.machine, s.module, s.action
  from v$session s,
       v$sort_usage u
 where s.saddr = u.session_addr
   and u.blocks*8192/1024 > 128 -- отсеиваем мелочь
group by s.sid, s.osuser, s.machine, s.module, s.action, s.client_identifier,s.status
order by "TEMP usage, Mb" desc
Categories: SQL Tags: , , ,

Поиск VIEW по используемым объектам

24 Апрель 2012 Нет комментариев

Поиск VIEW

Запрос для поиска VIEW, в которой используются определенные объекты.
Можно, например, использовать для поиска связки между таблицами.

with s as (
select 'XLA_AE_HEADERS' obj1, 'XLA_AE_LINES' obj2 from dual
)
select owner, name from All_Dependencies t, s
where type='VIEW' and t.REFERENCED_NAME like s.obj1
 intersect      
select owner, name from All_Dependencies t, s
where type='VIEW' and t.REFERENCED_NAME like s.obj2

OWNER                          NAME
------------------------------ ------------------------------
APPS                           ARFV_ADJ_DISTS_ALC_V
APPS                           ARFV_APP_DISTS_ALC_V
APPS                           ARFV_CASH_DISTS_ALC_V
APPS                           ARFV_FREIGHT_DISTS_ALC_V
APPS                           ARFV_REVENUE_DISTS_ALC_V
APPS                           ARFV_TAX_DISTS_ALC_V
APPS                           ARFV_UNREC_DISTS_ALC_V
APPS                           ARFV_UNREV_DISTS_ALC_V
...
Categories: SQL Tags: , , ,

SLA — Запрос для просмотра данных учета (Subledger Accounting)

13 Апрель 2012 Нет комментариев

Запрос для просмотра данных учета

select xte.application_id        as appl_id
      ,xte.entity_code           as entity_code
      ,ec.event_class_code       as event_class_code
      ,ec.name                   as event_class
      ,xal.code_combination_id   as cc_id     
      ,fnd_flex_ext.get_segs('SQLGL','GL#',gl.chart_of_accounts_id, xal.code_combination_id)      as ACCOUNT
      --,xla_oa_functions_pkg.get_ccid_description(gl.chart_of_accounts_id,xal.code_combination_id) as account_description
      ,xah.ae_header_id          as xah_ae_header_id
      ,xal.ae_line_num           as xal_ae_line_num
      ,xal.ledger_id             as xal_ledger_id
      ,xte.ledger_id             as xte_ledger_id
      ,xae.event_type_code       as event_type_code
      ,et.name                   as event_type
      ,xae.event_number          as event_number
      ,xal.accounting_class_code as acc_class_code   -- класс учета
      ,lk1.meaning               as accounting_class
      ,xal.accounting_date       as accounting_date
      ,xal.accounted_dr          as accounted_dr
      ,xal.accounted_cr          as accounted_cr
      ,xal.currency_code         as currency_code
      ,nvl(xte.source_id_int_1, -99) as source_id_int_1
      ,decode(xal.party_type_code, 'C', xal.party_id)      as customer_id
      ,decode(xal.party_type_code, 'C', xal.party_site_id) as customer_site_id
from xla.xla_transaction_entities  xte
   , xla.xla_ae_headers            xah
   , xla.xla_ae_lines              xal
   , xla.xla_events                xae
   , xla_lookups                   lk1
   , xla_gl_ledgers_v              gl   
   , xla.xla_event_types_tl        et
   , xla.xla_event_classes_tl      ec
where 1=1
      -- sla xah
      and xah.entity_id      = xte.entity_id
      and xah.application_id = xte.application_id
      and xah.ledger_id      = xal.ledger_id
      -- sla xal
      and xal.ae_header_id   = xah.ae_header_id
      and xal.application_id = xah.application_id
      and (nvl(xal.accounted_dr,0) != 0 or nvl(xal.accounted_cr,0)!=0)
      -- sla xae
      and xae.application_id  = xah.application_id
      and xae.event_id        = xah.event_id  
      -- lk1
      and lk1.lookup_code = xal.accounting_class_code
      and lk1.lookup_type = 'XLA_ACCOUNTING_CLASS'
      -- gl
      and gl.ledger_id = xah.ledger_id
      -- et 
      and et.application_id  = xte.application_id
      and et.entity_code     = xte.entity_code      
      and et.event_type_code = xae.event_type_code
      and et.language        = USERENV('LANG')
      -- ec
      and ec.application_id   = xte.application_id
      and ec.entity_code      = xte.entity_code
      and ec.event_class_code = et.event_class_code
      and ec.language         = USERENV('LANG')
      -- ======
      -- and xte.entity_code    = 'RECEIPTS'
      -- and xte.application_id = 222
      -- and xte.ledger_id = fnd_profile.value('GL_SET_OF_BKS_ID')
      -- and nvl(source_id_int_1,(-99)) = 

Список расчетных счетов для поставщиков

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

SELECT party_supp.party_name             supplier_name
      ,aps.segment1                      supplier_number
      ,ass.vendor_site_code              supplier_site
      ,ieb.bank_account_num              bank_account_num
      ,ieb.bank_account_name             bank_account_name
      ,party_bank.party_name             bank_name
      ,branch_prof.bank_or_branch_number bank_number
      ,party_branch.party_name           branch_name
      ,branch_prof.bank_or_branch_number branch_number
  FROM hz_parties               party_supp
      ,ap_suppliers             aps
      ,hz_party_sites           site_supp
      ,ap_supplier_sites_all    ass
      ,iby_external_payees_all  iep
      ,iby_pmt_instr_uses_all   ipi
      ,iby_ext_bank_accounts    ieb
      ,hz_parties               party_bank
      ,hz_parties               party_branch
      ,hz_organization_profiles bank_prof
      ,hz_organization_profiles branch_prof
 WHERE party_supp.party_id = aps.party_id
   AND party_supp.party_id = site_supp.party_id
   AND site_supp.party_site_id = ass.party_site_id
   AND ass.vendor_id = aps.vendor_id
   AND iep.payee_party_id = party_supp.party_id
   AND iep.party_site_id = site_supp.party_site_id
   AND iep.supplier_site_id = ass.vendor_site_id
   AND iep.ext_payee_id = ipi.ext_pmt_party_id
   AND ipi.instrument_id = ieb.ext_bank_account_id
   AND ieb.bank_id = party_bank.party_id
   AND ieb.branch_id = party_branch.party_id
   AND party_branch.party_id = branch_prof.party_id
   AND party_bank.party_id = bank_prof.party_id
 ORDER BY party_supp.party_name, ass.vendor_site_code;

Выборка неотсторнированных журналов

Для выбора неотсторнированных журналов можно использовать следующий запрос:

SELECT  * FROM gl_je_batches        b,
               gl_je_headers        h,
               gl_je_lines          l,
               gl_code_combinations gcc
WHERE h.je_header_id = l.je_header_id
      AND b.je_batch_id  = h.je_batch_id
      AND gcc.code_combination_id = l.code_combination_id
      AND (  --Неотсторнированные журналы
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NULL
             )
             OR -- сторно на сторно на сторно ...
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NOT NULL
              AND MOD((SELECT COUNT(*)
                          FROM gl_je_headers jh
                         START WITH jh.je_header_id = h.je_header_id
                       CONNECT BY jh.je_header_id = PRIOR jh.reversed_je_header_id
                        ), 2 ) != 0
             )
          )