Архив

Архив автора

Список параметров процедуры SQL запросом

SQL запрос для получения списка параметров процедуры/функции

SELECT A.OBJECT_NAME, A.PACKAGE_NAME, A.ARGUMENT_NAME, A.DATA_TYPE, a.SEQUENCE
FROM USER_ARGUMENTS A
    ,ALL_OBJECTS    O
WHERE A.OBJECT_ID = O.OBJECT_ID
      AND O.OBJECT_NAME = 'FND_GLOBAL'      -- pkg
      AND A.OBJECT_NAME = 'APPS_INITIALIZE' -- procedure
/

OBJECT_NAME       PACKAGE_NAME  ARGUMENT_NAME       DATA_TYPE   SEQUENCE
----------------- ------------- ------------------- ----------- ----------
APPS_INITIALIZE   FND_GLOBAL    USER_ID             NUMBER      1
APPS_INITIALIZE   FND_GLOBAL    RESP_ID             NUMBER      2
APPS_INITIALIZE   FND_GLOBAL    RESP_APPL_ID        NUMBER      3
APPS_INITIALIZE   FND_GLOBAL    SECURITY_GROUP_ID   NUMBER      4
APPS_INITIALIZE   FND_GLOBAL    SERVER_ID           NUMBER      5

API для обновления полномочий (RESPONSIBILITY)

25 Февраль 2013 Нет комментариев

Пример использования API для обновления даты действия полномочий

BEGIN
  FOR cr IN (SELECT * FROM fnd_responsibility_vl
             WHERE responsibility_key LIKE 'XX%'
            ) 
  LOOP
    fnd_responsibility_pkg.update_row(
            x_responsibility_id         => cr.responsibility_id,
            x_application_id            => cr.application_id,
            x_web_host_name             => cr.web_host_name,
            x_web_agent_name            => cr.web_agent_name,
            x_data_group_application_id => cr.data_group_application_id,
            x_data_group_id             => cr.data_group_id,
            x_menu_id                   => cr.menu_id,
            x_start_date                => to_date('01-01-1951','dd-mm-yyyy'),
            x_end_date                  => cr.end_date,
            x_group_application_id      => cr.group_application_id,
            x_request_group_id          => cr.request_group_id,
            x_version                   => cr.VERSION,
            x_responsibility_key        => cr.responsibility_key,
            x_responsibility_name       => cr.responsibility_name,
            x_description               => cr.description,
            x_last_update_date          => SYSDATE,
            x_last_updated_by           => fnd_global.user_id,
            x_last_update_login         => fnd_global.login_id);
  END LOOP;

  COMMIT;
END;
/

AskTom in Moscow-2012

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


Удалось побывать на семинаре AskTom in Moscow-2012
и вот презентации с выступления Тома Кайта

What I learned at Oracle Open World 2012 Depositfiles Google Docs
All About Oracle Database Security Depositfiles Google Docs
What’s new in Oracle Database Application Development Depositfiles Google Docs
Five Things about SQL and PL/SQL you might not have known about Depositfiles Google Docs
Flashback Depositfiles Google Docs

OAF R12 Developer’s guide

4 Декабрь 2012 1 комментарий

Весьма полезные гайды по Oracle Application Framework (OAF)

Ноты на металинке:
Oracle Application Framework Developer’s Guide Release 12.1.3 [ID 1107973.1]
Oracle Application Framework Developer’s Guide Release 12.1.2 [ID 972774.1]
Oracle Application Framework Developer’s Guide Release 12.1.1 [ID 744832.1]

У кого нет доступа, тот может взять
Oracle Application Framework Developer’s Guide Release 12.1.3 по ссылкам:
Depositfiles
Google Docs

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

4 Декабрь 2012 Нет комментариев

Скрипт обновления альтернативного имени отделения поставщика.
В данном примере обнуляем альтернативное наименование.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  x_return_status varchar2(1);
  x_msg_count     number;
  x_msg_data      varchar2(4000);
  --
  l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
  --
  l_success_count NUMBER := 0;
  l_failure_count NUMBER := 0;
  l_all_count     NUMBER := 0;  
BEGIN
  fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы

  l_success_count := 0;
  l_failure_count := 0;
  l_all_count     := 0;  
  -- цикл по отделениям
  for s in (select t.vendor_site_id from ap_supplier_sites_all t
            where 1=1
                  and t.vendor_site_code like '%XX%'
                  and vendor_site_code_alt is not null
           )
  loop
    l_all_count := l_all_count + 1;
    -- обнуляем альт.имя
    l_vendor_site_rec.VENDOR_SITE_CODE_ALT := chr(0);
    --
    ap_vendor_pub_pkg.update_vendor_site(p_api_version      => 1.0,
                                         p_init_msg_list    => fnd_api.g_true,
                                         p_commit           => fnd_api.g_false,
                                         p_validation_level => fnd_api.g_valid_level_full,
                                         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,
                                         p_vendor_site_id   => s.vendor_site_id);

    if (x_return_status <> 'S') then
      l_failure_count := l_failure_count + 1;
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);
     
      if x_msg_count = 1 then
        dbms_output.put_line('ERROR vendor_site_id='||s.vendor_site_id
                             ||'"; '|| x_msg_data );
      else
        FOR j IN 1 .. x_msg_count LOOP
          x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
          dbms_output.put_line('ERROR vendor_site_id='||s.vendor_site_id
                               ||'"; '|| x_msg_data );
        END LOOP;
      end if;
    else
      l_success_count := l_success_count + 1;
    end if;
      
  end loop; -- s

  dbms_output.put_line('+--------------------------------------+');    
  dbms_output.put_line('UPDATE INFORMATION');
  dbms_output.put_line('  All count      : '||l_all_count);
  dbms_output.put_line('  Update success : '||l_success_count);      
  dbms_output.put_line('  Update failure : '||l_failure_count);
  dbms_output.put_line('+--------------------------------------+');
  commit;
 
END;

OEBS API: Обновляем имя поставщика

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

Скрипт обновления имени и альтернативного имени поставщика.
Убираем лишние пробелы в конце и начале имени.

declare
  l_organization_rec  HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
  l_party_rec         HZ_PARTY_V2PUB.PARTY_REC_TYPE;
  x_profile_id        NUMBER;
  --
  l_success_count NUMBER := 0;
  l_failure_count NUMBER := 0;
  l_all_count     NUMBER := 0;
  --
  x_return_status VARCHAR2(1);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(4000);  
begin
  
  fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы
  for i in (SELECT vendor_id, 
                   vendor_name, 
                   vendor_name_alt, 
                   party_id, 
                  (select hp.object_version_number 
                   from hz_parties hp where party_id = pv.party_id
                  ) as object_version_number
            FROM po_vendors pv
            WHERE 1=1
                  and (vendor_name like '% ' or vendor_name like ' %'
                      or vendor_name_alt like '% '
                      or vendor_name_alt like ' %')
            )
  loop
    l_all_count := l_all_count + 1;
    
    --dbms_output.put_line('vendor_id='||i.vendor_id);
    l_party_rec.party_id                          := i.party_id;
    l_organization_rec.organization_name          := trim(i.vendor_name);
    l_organization_rec.organization_name_phonetic := trim(i.vendor_name);
    l_organization_rec.party_rec                  := l_party_rec;
    HZ_PARTY_V2PUB.update_organization(p_init_msg_list               => FND_API.G_TRUE,
                                       p_organization_rec            => l_organization_rec,
                                   p_party_object_version_number => i.object_version_number,
                                       x_profile_id                  => x_profile_id,
                                       x_return_status               => x_return_status,
                                       x_msg_count                   => x_msg_count,
                                       x_msg_data                    => x_msg_data);
      
    --dbms_output.put_line(x_return_status);
    if (x_return_status <> 'S') then
      l_failure_count := l_failure_count + 1;
      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                p_count   => x_msg_count,
                                p_data    => x_msg_data);
                                      
      if x_msg_count = 1 then
        dbms_output.put_line('ERROR vendor_id='||i.vendor_id
                             ||'; vendor_name="'||i.vendor_name||'"; '|| x_msg_data );
      else
        FOR j IN 1 .. x_msg_count LOOP
          x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
          dbms_output.put_line('ERROR vendor_id='||i.vendor_id
                               ||'; vendor_name="'||i.vendor_name||'"; '|| x_msg_data );
        END LOOP;
      end if;
    else
      l_success_count := l_success_count + 1;
    end if;
      
  end loop;  
    
  dbms_output.put_line('+--------------------------------------+'); 
  dbms_output.put_line('Обновление поставщиков');   
  dbms_output.put_line('  Общее количество    : '||l_all_count);
  dbms_output.put_line('  Обновлено успешно   : '||l_success_count);      
  dbms_output.put_line('  Не удалось обновить : '||l_failure_count);
  dbms_output.put_line('+--------------------------------------+');    
  
  COMMIT;
                            
end;

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: Создание расчетного счета на отделении поставщика

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

Пример скрипта для создания расчетного счета на отделении поставщика:

DECLARE
  l_bank_acct_rec       apps.iby_ext_bankacct_pub.extbankacct_rec_type;
  out_mesg              apps.iby_fndcpt_common_pub.result_rec_type;
  l_acct                NUMBER;
  l_bank_id             NUMBER := 0; --!!! установить значение
  l_branch_id           NUMBER := 0; --!!!
  l_acct_owner_party_id NUMBER := 0; --!!!
  l_supplier_site_id    NUMBER := 0; --!!!
  l_party_site_id       NUMBER := 0; --!!!
  --
  x_return_status       VARCHAR2(1);
  x_msg_count           NUMBER;
  x_msg_data            VARCHAR2(4000);  
BEGIN
  FND_MSG_PUB.initialize;
  
  fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы

  l_bank_acct_rec.bank_id               := l_bank_id; 
  l_bank_acct_rec.branch_id             := l_branch_id;
  l_bank_acct_rec.country_code          := 'RU';
  l_bank_acct_rec.bank_account_name     := '3321615678101831567890';
  l_bank_acct_rec.bank_account_num      := 3321615678101831567890;
  l_bank_acct_rec.acct_owner_party_id   := l_acct_owner_party_id;
  l_bank_acct_rec.currency              := 'RUB';
  l_bank_acct_rec.object_version_number := 1.0;
  l_bank_acct_rec.start_date            := sysdate;
  l_bank_acct_rec.foreign_payment_use_flag := 'Y';
  l_bank_acct_rec.payment_factor_flag      := 'N';

  apps.iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version       => 1.0,
                                                 p_init_msg_list     => 'F',
                                                 p_ext_bank_acct_rec => l_bank_acct_rec,
                                                 p_association_level => 'SS',
                                                 p_supplier_site_id  => l_supplier_site_id,
                                                 p_party_site_id     => l_party_site_id,
                                                 p_org_id            => 82,
                                                 p_org_type          => 'OPERATING_UNIT',
                                                 x_acct_id           => l_acct,
                                                 x_return_status     => x_return_status,
                                                 x_msg_count         => x_msg_count,
                                                 x_msg_data          => x_msg_data,
                                                 x_response          => out_mesg);
  COMMIT;  
                                 
  dbms_output.put_line('x_return_status='||x_return_status);
  dbms_output.put_line('out_mesg.Result_Code     ='||out_mesg.Result_Code);
  dbms_output.put_line('out_mesg.Result_Category ='||out_mesg.Result_Category);
  dbms_output.put_line('out_mesg.Result_Message  ='||out_mesg.Result_Message);
  
  if x_return_status <> 'S' THEN
    fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                              p_count   => x_msg_count,
                              p_data    => x_msg_data);
                                
    dbms_output.put_line(chr(10)||'x_msg_count='||x_msg_count);
    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;    
  end if;
end;

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 Программа – сторнирование журналов SQLGL GLPREV

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

ОЕБС Пример вызова параллельной программы для сторнирования журналов

-- -------------------------------------------------------------------------------
-- Сторнирование журнала
PROCEDURE reverse_journals(x_return_status OUT VARCHAR2
                          ,x_mess          OUT VARCHAR2) 
IS
  TYPE t_integers IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  l_request_ids   t_integers;
  l_result        BOOLEAN := TRUE;
  l_access_set_id NUMBER;
BEGIN
  write_log('START REVERSE_JOURNALS');
  x_return_status := 'E';
  l_access_set_id := fnd_profile.value('GL_ACCESS_SET_ID');
  FOR c_je IN (SELECT jh.je_header_id
                     ,jh.rowid        AS row_id
                     ,jh.name         AS journal_name
                     ,b.name          AS batch_name
                 FROM gl.gl_je_headers jh, gl_je_batches b
                WHERE 1 = 1
                  AND jh.je_batch_id = b.je_batch_id
                     --
                  AND jh.ledger_id = G_LEDGER_ID
                  AND jh.je_source = G_JE_SOURCE_NAME
                  AND jh.je_category = G_JE_CATEGORY_NAME
                  AND jh.period_name = P_PERIOD_NAME
                  AND (b.name LIKE g_bath_name_in || '%' OR
                      b.name LIKE g_bath_name_out || '%')
                  AND (jh.name LIKE g_jh_name_in || '%' OR
                      jh.name LIKE g_jh_name_out || '%')
                  AND ( --Неотсторнированные журналы
                       (jh.accrual_rev_status IS NULL AND
                 -- сторно на сторно на сторно ...
                       jh.reversed_je_header_id IS NULL) OR 
                       (jh.accrual_rev_status IS NULL AND
                       jh.reversed_je_header_id IS NOT NULL AND
                       MOD((SELECT COUNT(*)
                              FROM gl_je_headers jh0
                             START WITH jh0.je_header_id = jh.je_header_id
                            CONNECT BY jh0.je_header_id = PRIOR
                                       jh0.reversed_je_header_id)
                           ,2) != 0))
                ORDER BY jh.je_header_id)
  LOOP
    write_log('  Сторнирование журнала "' || c_je.journal_name ||
           '" из пакета "' || c_je.batch_name || '"');
    UPDATE gl_je_headers
       SET accrual_rev_flag             = 'Y'
          ,accrual_rev_period_name      = P_PERIOD_NAME
          ,accrual_rev_change_sign_flag = 'Y'
          ,accrual_rev_effective_date   = SYSDATE
     WHERE ROWID = c_je.row_id;
    --
    l_request_ids(l_request_ids.COUNT + 1) :=
     fnd_request.submit_request(application => 'SQLGL'
                                ,program     => 'GLPREV'
                                ,description => NULL
                                ,start_time  => NULL
                                ,sub_request => FALSE
                                ,argument1   => l_access_set_id
                                ,argument2   => c_je.je_header_id);
    COMMIT;
    IF l_request_ids(l_request_ids.COUNT) = 0 THEN
      write_log('Ошибка при отправке параллельного запроса Сторнирование журналов.');
      l_request_ids.DELETE(l_request_ids.COUNT);
      l_result := FALSE;
      --exit;
    END IF;
  END LOOP;
  --
  WHILE l_request_ids.COUNT > 0
  LOOP
    -- описание функции http://apps-oracle.ru/fnd_request_exec/#WAIT_CONCURRENT
    if NOT (wait_concur(l_request_ids(l_request_ids.FIRST),x_mess)) then
      write_log(x_mess);
      l_result := FALSE;
      exit;
    end if;
    l_request_ids.DELETE(l_request_ids.FIRST);
  END LOOP;
  write_log('END REVERSE_JOURNALS' || chr(10));
  --
  IF l_result THEN
    x_return_status := 'S';
    x_mess          := 'Сторнирование журналов завершено успешно.';
    RETURN;
  ELSE
    x_return_status := 'E';
    x_mess          := 'Сторнирование журналов завершено с ошибками. '||x_mess;
    RETURN;
  END IF;
  --
END reverse_journals;