Архив

Архив раздела ‘Oracle e-Business Suite’

Вывод сообщения в лог (для concurrent)

24 Март 2010 2 comments
-- Вывод сообщения в лог
procedure output_log_data(p_log_str in varchar2) is
    g_output_dbms    number := 1; -- Вывод сообщений в dbms_output.put_line
    g_output_oebs    number := 2; -- Вывод сообщений в fnd_file.put_line
    v_output_current number := g_output_oebs;
begin
    if fnd_global.conc_request_id = -1 then
      v_output_current := g_output_dbms;
    end if;
    if v_output_current = g_output_dbms then
      dbms_output.put_line(p_log_str);
    elsif v_output_current = g_output_oebs then
    --fnd_file.put_line(fnd_file.output, p_log_str); --Строка выводится в "Просмотр Результатов"
      fnd_file.put_line(fnd_file.log, p_log_str); --Строка выводится в "Просмотр Журнала"
    end if;
end output_log_data;

Добавление отчета в меню

declare
  vr_Form_name   VARCHAR2(100) := 'XX_NAME';
  vr_Description VARCHAR2(200) := 'XX_DESCRIPTION';
begin
  -- Регистрация функции формы
  fnd_form_functions_pkg.load_row
  (
    x_function_name           => vr_Form_name
  , x_application_short_name  => 'FND'
  , x_form_name               => 'FNDRSRUN'
  , x_parameters              => 'CONCURRENT_PROGRAM_NAME="'||vr_Form_name
                                 ||'" PROGRAM_APPL_SHORT_NAME="'||'XX'
                                 ||'" TITLE="'||vr_Description||'"'
  , x_type                    => 'FORM'
  , x_web_host_name           => NULL
  , x_web_agent_name          => NULL
  , x_web_html_call           => NULL
  , x_web_encrypt_parameters  => NULL
  , x_web_secured             => NULL
  , x_web_icon                => NULL
  , x_object_name             => NULL
  , x_region_application_name => NULL
  , x_region_code             => NULL
  , x_user_function_name      => vr_Description
  , x_description             => vr_Description
  , x_owner                   => 'INITIAL SETUP'
  , x_custom_mode             => 'FORCE'
  );
-- Регистрация пункта меню
  fnd_menu_entries_pkg.load_row
  (
    x_mode          => 'REPLACE'
  , x_ent_sequence  => 910
  , x_menu_name     => 'MENU_NAME'
  , x_sub_menu_name => NULL
  , x_function_name => vr_Form_name
  , x_grant_flag    => 'Y'
  , x_prompt        => vr_Description
  , x_description   => vr_Description
  , x_owner         => USER
  );
--
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(substr('Error: ' || SQLERRM, 1, 255));
ROLLBACK;
end;
/
SHOW ERRORS

Создание пользователя

declare
  l_user_id number;

  l_responsibility_key fnd_responsibility.responsibility_key%TYPE
         := 'SYSTEM_ADMINISTRATOR';
  l_application_short_name fnd_application.application_short_name%TYPE
         := 'SYSADMIN';

  l_responsibility_id   number;
  l_application_id      number;
  l_responsibility_name varchar2(200) := 'System Administrator';
  l_security_group_id number := 0;
begin
  l_user_id := fnd_user_pkg.CreateUserId(
      x_user_name            => 'TEST'
    , x_owner                => 'CUST'
    , x_unencrypted_password => '123456'
  );
select application_id into l_application_id
from fnd_application
where application_short_name = l_application_short_name;

select responsibility_id into l_responsibility_id
from fnd_responsibility
where application_id     = l_application_id
and responsibility_key = l_responsibility_key;

  fnd_user_resp_groups_api.insert_assignment(
    user_id                         => l_user_id
    , responsibility_id             => l_responsibility_id
    , responsibility_application_id => l_application_id
    , security_group_id             => l_security_group_id
    , start_date                    => trunc(sysdate)
    , end_date                      => NULL
    , description                   => l_responsibility_name
    );

  fnd_wf_engine.default_event_raise(
     'oracle.apps.fnd.security.user.assignment.change'
     , l_user_id || ':' || l_responsibility_id
     );

  fnd_wf_engine.propagate_user_role(
'FND_USR'
    , l_user_id
    , 'FND_RESP' || l_application_id
    , l_responsibility_id
    , trunc(sysdate)
    , NULL
  );

  -- Added for Function Security Cache Invalidation Project
  fnd_function_security_cache.insert_user_resp(
      l_user_id
    , l_responsibility_id
    , l_application_id
    );
commit;
end;

Конвертирование единиц измерения

Пакет inv_convert

FUNCTION inv_um_convert(p_item_id       IN NUMBER,
                        p_from_uom_code IN VARCHAR2,
                        p_to_uom_code   IN VARCHAR2) RETURN NUMBER;

Пример:

select msi.segment1,
       msi.primary_uom_code,
       'кг' as second_uom,
       inv_convert.inv_um_convert(msi.inventory_item_id,'кг',
                                  msi.primary_uom_code) as coefficient
from mtl_system_items_b msi
where msi.primary_uom_code = 'т'
and rownum <= 10