APPS-ORACLE.RU

Скрипт генерации параметров запуска канкарента

declare
 p_conc_code VARCHAR2(100) := 'AFBFULDL';
 --
 l_space_num number := 40;
 l_space varchar2(100) := lpad(' ',l_space_num,' ');
 l_appl_code varchar2(50);
 l_conc_prog_rec fnd_concurrent_programs_vl%rowtype;
 
begin
 
 select * into l_conc_prog_rec from fnd_concurrent_programs_vl t
 where concurrent_program_name = p_conc_code;
 
 select application_short_name into l_appl_code
 from fnd_application a where a.application_id = l_conc_prog_rec.application_id;
 
 dbms_output.put_line('-- '||l_conc_prog_rec.USER_CONCURRENT_PROGRAM_NAME);
 dbms_output.put_line('p_req_id := fnd_request.submit_request( application => '''
                      ||l_appl_code ||''',');
 dbms_output.put_line(l_space||'program     => '''||p_conc_code||''',');
 dbms_output.put_line(l_space||'start_time  => null,');
 dbms_output.put_line(l_space||'description => null,');
 dbms_output.put_line(l_space||'sub_request => false,');   
 
 for p in (select max(rownum) over() as max_rownum
                 ,row_number() over(order by column_seq_num) as row_num, t.*
           from fnd_descr_flex_col_usage_vl t
           where descriptive_flexfield_name = '$SRS$.'||p_conc_code
                 and enabled_flag='Y'
           order by column_seq_num)
 loop
   dbms_output.put_line(l_space||rpad('argument'||p.row_num,12,' ')
                     ||'=> null'||case when p.row_num!=p.max_rownum then ',' else ' ' end
                     ||' -- '||'REQUIRED='||p.REQUIRED_FLAG
                     ||' '||nvl(p.description,p.form_left_prompt)
                     ||' ('||p.end_user_column_name||')'
                     || case when p.DEFAULT_VALUE is not null 
                         then ' DEFAULT_VALUE='||p.DEFAULT_VALUE
                        end
                     );
 end loop;
 dbms_output.put_line(lpad(' ',l_space_num-2,' ')||');');  
 
end;

-- Пример вывода
-- Диспетчер масс-обработки файлов
p_req_id := fnd_request.submit_request( application => 'FND',
                                        program     => 'AFBFULDL',
                                        start_time  => null,
                                        description => null,
                                        sub_request => false,
                                        argument1   => null, -- REQUIRED=N Имя файла (File Name)
                                        argument2   => null, -- REQUIRED=N Тип содержимого файла (File Content Type)
                                        argument3   => null, -- REQUIRED=N Имя программы (Program Name)
                                        argument4   => null, -- REQUIRED=N Тег программы (Program Tag)
                                        argument5   => null, -- REQUIRED=N Язык (Language)
                                        argument6   => null, -- REQUIRED=N Формат файла (File Format)
                                        argument7   => null, -- REQUIRED=N Режим (upload/download или upload_help/download_help) (Mode)
                                        argument8   => null, -- REQUIRED=Y Загрузка/выгрузка каталога (Directory)
                                        argument9   => null, -- REQUIRED=N Создание каталога в случае его отсутствия (Create Directory) DEFAULT_VALUE=true
                                        argument10  => null, -- REQUIRED=N Переопределение каталога для выгрузки в случае, если выгружаемый файл является абсолютным (Override Directory) DEFAULT_VALUE=false
                                        argument11  => null, -- REQUIRED=N Конец строки (Line Break)
                                        argument12  => null, -- REQUIRED=N Заголовок (Title)
                                        argument13  => null  -- REQUIRED=N Имя контрольного значения (Target Name)
                                      );

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

declare 
  p_req_id    number;
  p_user_name varchar2(100) := '!!!USER_NAME';
  --
  l_user_id number;
begin
  select nvl(max(u.user_id),-1) into l_user_id from fnd_user u where u.user_name=p_user_name;
  fnd_global.apps_initialize(l_user_id, 20639, 200); --Диспетчер кредиторов - Кредиторы
 
  execute immediate 'alter session set NLS_LANGUAGE = ''RUSSIAN''';
  execute immediate 'alter session set NLS_DATE_FORMAT = ''DD.MM.YYYY HH24:MI:SS''';

  -- вставить запуск канкарента
  -- ...  
  
  dbms_output.put_line('p_req_id='||p_req_id);                                    
  commit;
end;
-- запрос для просмотра запущенного канкарента
-- select t.completion_text, t.* from APPLSYS.FND_CONCURRENT_REQUESTS t where t.request_id=

Если появилось желание проверить используется индекс или нет, то:

1) Сначала включаем мониторинг интересующих нас индексов

ALTER INDEX OWNER.INDEX_NAME MONITORING USAGE;

2) После того, как отработала программа, которая потенциально должна была использовать индекс, выполняем запрос

select io.name as index_name
      ,t.name  as table_name
      ,decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring
      ,decode(bitand(ou.flags, 1), 0, 'NO', 'YES')    as used
      ,ou.start_monitoring as start_monitoring
      ,ou.end_monitoring   as end_monitoring
from sys.obj$         io
    ,sys.obj$         t
    ,sys.ind$         i
    ,sys.object_usage ou
where i.obj# = ou.obj#
      and io.obj# = ou.obj#
      and t.obj# = i.bo#;

3) Выключаем мониторинг индекса

ALTER INDEX OWNER.INDEX_NAME NOMONITORING USAGE;

Есть также view V$OBJECT_USAGE, но она показывает данные только для текущей схемы.

Скрипт с примером вывода промежутка времени в часах,минутах, секундах (hh:mi:ss)

SET SERVEROUTPUT ON
declare
  l_point1 NUMBER;
  l_point2 NUMBER;
  l_diff   NUMBER;
  l_time_s NUMBER;

  -- Возвращает затраченное время в секундах
  function get_time_diff(p_time number) return number
  is
  begin
    return round( p_time/100 , 0);
  end get_time_diff;
  
  -- Возвращает секунды
  function get_time_diff_ss(p_all_time number) return number
  is
  begin
    return round(mod(p_all_time,60),0);
  end get_time_diff_ss; 
  
  -- Возвращает минуты
  function get_time_diff_mi(p_all_time number) return number
  is
  begin
    return mod(floor(p_all_time/60),60);
  end get_time_diff_mi;    
  
  -- Возвращает часы
  function get_time_diff_hh(p_all_time number) return number
  is
  begin
    return floor(p_all_time/(60*60));
  end get_time_diff_hh;     
  
  -- Возвращает разницу в hh:mi:ss
  function get_time_diff_hhmiss(p_all_time number) return varchar2
  is
    l_time_s NUMBER;
  begin
    l_time_s := round( p_all_time/100 , 0);
    return        lpad( floor(round(l_time_s /3600,2)) ,2,'0')
           ||':'||lpad( mod(floor(l_time_s /60),60)    ,2,'0')
           ||':'||lpad( round(mod(l_time_s ,60),0)     ,2,'0');
  end get_time_diff_hhmiss;     
   
begin
  l_point1 := dbms_utility.get_time();
  dbms_lock.sleep(5);
  l_point2 := dbms_utility.get_time();

  l_diff   := l_point2 - l_point1;
  l_time_s := get_time_diff(l_diff);
  
  dbms_output.put_line('весь период времени в секундах = '|| l_time_s || 's');
  
  dbms_output.put_line('только секунды = '|| get_time_diff_ss(l_time_s)||'s' );
  
  dbms_output.put_line('только минуты = '|| get_time_diff_mi(l_time_s)||'m' );  

  dbms_output.put_line('только часы = '|| get_time_diff_hh(l_time_s)||'h' );  

  dbms_output.put_line('h:m:s = '|| get_time_diff_hh(l_time_s)||'h:'
                      ||get_time_diff_mi(l_time_s)
                      ||'m:'||get_time_diff_ss(l_time_s)||'s' ); 

  dbms_output.put_line('hh:mi:ss = '|| lpad(get_time_diff_hh(l_time_s),2,'0')
                        ||':'||lpad(get_time_diff_mi(l_time_s),2,0)
                        ||':'||lpad(get_time_diff_ss(l_time_s),2,'0') );
  
  dbms_output.put_line('get_time_diff_hhmiss = '|| get_time_diff_hhmiss(l_diff));
    
end;
/

весь период времени в секундах = 5s
только секунды = 5s
только минуты = 0m
только часы = 0h
h:m:s = 0h:0m:5s
hh:mi:ss = 00:00:05
get_time_diff_hhmiss = 00:00:05

Скрипт добавления(назначения) счета на отделение заказчика.
В данном примере счета копируются из одного отделения в другое.

DECLARE
  p_org_id_from number := 111; 
  p_org_id_to   number := 112; 
  --
  l_payee_rec      iby_disbursement_setup_pub.payeecontext_rec_type;
  l_pay_instr_rec  iby_fndcpt_setup_pub.pmtinstrument_rec_type;  
  l_pay_assign_rec iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
  --
  x_assign_id           NUMBER;
  x_return_status       VARCHAR2(1);
  x_msg_count           NUMBER;
  x_msg_data            VARCHAR2(4000);
  x_response            apps.iby_fndcpt_common_pub.result_rec_type;  
BEGIN
  FND_MSG_PUB.initialize;
 
  fnd_global.apps_initialize(-1, 20639, 200); --Диспетчер кредиторов - Кредиторы
  
  for i in (
            SELECT sup.vendor_type_lookup_code
                 , sup.vendor_id
                 , ss.party_site_id
                 , ss2.party_site_id  party_site_id_to
                 , ss.vendor_site_id  vendor_site_id_from
                 , ss2.vendor_site_id vendor_site_id_to
                 , sup.party_id
                 , sup.segment1
                 , sup.vendor_name
                 , epa.org_id
                 , ss.vendor_site_code
                 , piu.order_of_preference priority
                 , eba.ext_bank_account_id
                 , eba.bank_id
                 , eba.branch_id
                 , eba.country_code
                 , eba.bank_account_name
                 , eba.bank_account_num
                 , eba.currency_code
                 , eba.foreign_payment_use_flag
                 , eba.payment_factor_flag
              FROM apps.ap_suppliers sup
                 , apps.ap_supplier_sites_all   ss
                 , apps.ap_supplier_sites_all   ss2
                 , apps.iby_external_payees_all epa
                 , apps.iby_pmt_instr_uses_all  piu
                 , apps.iby_ext_bank_accounts   eba
             WHERE sup.vendor_id     = ss.vendor_id
               AND ss.vendor_site_id = epa.supplier_site_id
               and ss2.vendor_id        = sup.vendor_id  
               and ss2.vendor_site_code = ss.vendor_site_code
               and ss2.org_id           = p_org_id_to
               AND epa.ext_payee_id  = piu.ext_pmt_party_id
               AND piu.instrument_id = eba.ext_bank_account_id
               AND epa.org_id    = p_org_id_from
               and not exists (select 1 
                               from apps.iby_external_payees_all epa1
                                  , apps.iby_pmt_instr_uses_all  piu1
                                  , apps.iby_ext_bank_accounts   eba1
                               where 1=1
                                     and epa1.supplier_site_id = ss2.vendor_site_id
                                     and epa1.ext_payee_id     = piu1.ext_pmt_party_id
                                     and piu1.instrument_id    = eba1.ext_bank_account_id
                                   and eba1.ext_bank_account_id = eba.ext_bank_account_id
                              )
                        
           )
  loop
    dbms_output.put_line('sup.vendor_id='||i.vendor_id);
 
    x_response := null;
    --    
    l_payee_rec.Party_Id         := i.party_id; 
    l_payee_rec.Payment_Function := 'PAYABLES_DISB';
    l_payee_rec.Party_Site_id    := i.party_site_id_to;
    l_payee_rec.Supplier_Site_id := i.vendor_site_id_to;
    l_payee_rec.Org_Id           := p_org_id_to;
    l_payee_rec.Org_Type         := 'OPERATING_UNIT';
    
    -- Instrument Record.
    l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
    l_pay_instr_rec.Instrument_Id   := i.ext_bank_account_id;
          
    -- Assignment Record.
    l_pay_assign_rec.Instrument := l_pay_instr_rec;
    l_pay_assign_rec.Priority   := i.priority;
    l_pay_assign_rec.Start_Date := trunc(sysdate);    
    
    iby_disbursement_setup_pub.set_payee_instr_assignment(
        p_api_version        => 1.0,
        p_init_msg_list      => 'T',
        p_commit             => 'F',
        x_return_status      => x_return_status,
        x_msg_count          => x_msg_count,
        x_msg_data           => x_msg_data,
        p_payee              => l_payee_rec,
        p_assignment_attribs => l_pay_assign_rec,
        x_assign_id          => x_assign_id,
        x_response           => x_response);    
      
    dbms_output.put_line('x_return_status='||x_return_status);
    dbms_output.put_line('out_mesg.Result_Code     ='||x_response.Result_Code);
    dbms_output.put_line('out_mesg.Result_Category ='||x_response.Result_Category);
    dbms_output.put_line('out_mesg.Result_Message  ='||x_response.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 j 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 loop;
  
  commit;
end;

Доп. информацию можно прочитать в ноте How to create bank account through API and associate to multiple supplier sites. [ID 1299614.1]

Пример использования 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 запрос для получения списка параметров процедуры/функции

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 для обновления даты действия полномочий

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
и вот презентации с выступления Тома Кайта

Весьма полезные гайды по 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

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

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;