Архив

Архив автора

OEBS API: Добавление записи в lookup

Пример использования API для добавление записи в код поиска (lookup):

declare
 lx_rowid rowid;
begin
  for i in (select 'TEST'    as lookup_code,
                   'TEST'    as meaning,
                   null      as tag,
                   'TEST'    as descr,
                   sysdate   as start_date_active,
                   null      as end_date_active
              from dual t
            )
  loop
    -- Call the procedure
    fnd_lookup_values_pkg.insert_row(x_rowid               => lx_rowid,
                                     x_lookup_type         => 'LOOKUP_TYPE_NAME',
                                     x_security_group_id   => 0,
                                     x_view_application_id => 3,
                                     x_lookup_code         => i.lookup_code,
                                     x_tag                 => i.tag,
                                     x_attribute_category  => null,
                                     x_attribute1          => null,
                                     x_attribute2          => null,
                                     x_attribute3          => null,
                                     x_attribute4          => null,
                                     x_enabled_flag        => 'Y',
                                     x_start_date_active   => i.start_date_active,
                                     x_end_date_active     => i.end_date_active,
                                     x_territory_code      => null,
                                     x_attribute5          => null,
                                     x_attribute6          => null,
                                     x_attribute7          => null,
                                     x_attribute8          => null,
                                     x_attribute9          => null,
                                     x_attribute10         => null,
                                     x_attribute11         => null,
                                     x_attribute12         => null,
                                     x_attribute13         => null,
                                     x_attribute14         => null,
                                     x_attribute15         => null,
                                     x_meaning             => i.meaning,
                                     x_description         => i.descr,
                                     x_creation_date       => sysdate,
                                     x_created_by          => -1,
                                     x_last_update_date    => sysdate,
                                     x_last_updated_by     => -1,
                                     x_last_update_login   => -1);
  end loop;   
  commit;                                  
end;
/

Список параметров процедуры 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;