Архив

Архив автора

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

Скрипт с примером вывода промежутка времени в часах,минутах, секундах (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

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

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

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]

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