APPS-ORACLE.RU

select gbh.organization_id,
       gbh.batch_no,
       gmd.inventory_item_id,
       gmd.line_type,
       md.qty
from fm_matl_dtl md, gme_material_details gmd, gme_batch_header gbh
where 1 = 1
      and gmd.batch_id = gbh.batch_id
      and gmd.organization_id = gbh.organization_id
      and md.formula_id = gbh.formula_id
      and md.formulaline_id = gmd.formulaline_id
order by gbh.organization_id,
          gbh.batch_no,
          gmd.inventory_item_id,
          gmd.line_type

select t.object_name,
       t.object_type,
       dbms_rowid.rowid_object('AABDKKAGRAAAOXhAAR')       as "OBJECT",
       dbms_rowid.rowid_relative_fno('AABDKKAGRAAAOXhAAR') as "FILE",
       dbms_rowid.rowid_block_number('AABDKKAGRAAAOXhAAR') as "BLOCK",
       dbms_rowid.rowid_row_number('AABDKKAGRAAAOXhAAR')   as "ROW"
from dba_objects t
where dbms_rowid.rowid_object('AABDKKAGRAAAOXhAAR') =  t.data_object_id

OBJECT_NAME     OBJECT_TYPE    OBJECT     FILE     BLOCK      ROW
--------------- -------------- ---------- -------- ---------- ----------
FM_FORM_MST_B   TABLE          275082     401      58849      17

Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)

Процедура для удаления строки из формулы

Читать дальше про “Модуль GMD – Удалить строку из формулы” »

Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)

Процедура добавления ингредиента в формулу

Читать дальше про “Модуль GMD – Добавить ингредиент в формулу” »

Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)

Процедура для обновления нормы строки формулы

Читать дальше про “Модуль GMD — Обновление нормы строки формулы” »

Скрипт создания функции

create or replace type string_agg_type as object (
 total varchar2(4000),
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
 return number,
member function ODCIAggregateIterate(self IN OUT string_agg_type ,
                                     value IN varchar2 ) return number,
member function ODCIAggregateTerminate(self IN string_agg_type,
                                       returnValue OUT  varchar2,
                                       flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type) return number
);
/
create or replace type body string_agg_type is

  static function odciaggregateinitialize(sctx IN OUT string_agg_type)
    return number is
  begin
    sctx := string_agg_type(null);
    return odciconst.success;
  end;
member function odciaggregateiterate(self  IN OUT string_agg_type,
                                     value IN varchar2) return number is
  begin
    self.total := self.total || ',' || value;
    return odciconst.success;
   end;
member function odciaggregateterminate(self        IN string_agg_type,
                                       returnvalue OUT varchar2,
                                       flags       IN number) return number is
  begin
    returnvalue := ltrim(self.total, ',');
    return odciconst.success;
  end;
member function odciaggregatemerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type) return number is
  begin
    self.total := self.total || ctx2.total;
    return odciconst.success;
  end;
end;
/
CREATE or replace FUNCTION stragg(input varchar2) RETURN varchar2
  PARALLEL_ENABLE
  AGGREGATE USING string_agg_type;
/

Читать дальше про “Агрегатная функция stragg — складывает строки” »

Запрос для просмотра полномочий пользователя OEBS:

SELECT distinct
       u.user_id,
       u.user_name,
       fr.responsibility_key,
       r.responsibility_name as responsiblity,
       fa.application_short_name,
       a.application_name    as application,
       sg.security_group_key,
       'dbms_output.put_line('''|| r.responsibility_name ||''');'||chr(10)||'fnd_user_pkg.addresp(l_user_name, '''||fa.application_short_name||''', '''|| fr.responsibility_key ||''', '''||sg.security_group_key||''', ''Add by script'', sysdate, null);' as str
FROM fnd_user              u,
     fnd_user_resp_groups  g,
     fnd_application_tl    a,
     fnd_application       fa,
     fnd_responsibility_tl r,
     fnd_responsibility    fr,
     fnd_security_groups   sg
WHERE g.user_id(+) = u.user_id
      AND g.responsibility_application_id = a.application_id
      AND a.application_id = r.application_id
      and fa.application_id = r.application_id
      AND g.responsibility_id  = r.responsibility_id
      and fr.responsibility_id = r.responsibility_id
      and fr.application_id    = r.application_id
      and sg.security_group_id = g.security_group_id
      AND a.LANGUAGE = 'RU'
      AND r.LANGUAGE = 'RU'
      AND u.user_name = '!!!USER_NAME'
ORDER BY user_name, responsiblity, application

Довольно удобно выставлять профили через скрипт, особенно если их много.
Данный скрипт выставляет профили на уровне пользователя.

DECLARE
 l_user_name varchar2(100) := 'USER_NAME';

 PROCEDURE set_profile_at_user_level(p_user_name                fnd_user.user_name%TYPE,
                                     p_user_profile_option_name fnd_profile_options_vl.user_profile_option_name%TYPE,
                                     p_profile_option_value     fnd_profile_option_values.profile_option_value%TYPE) IS
   l_user_id             fnd_user.user_id%TYPE;
   l_profile_option_name fnd_profile_options_vl.profile_option_name%TYPE;
 BEGIN
 
   SELECT fu.user_id
     INTO l_user_id
     FROM fnd_user fu
    WHERE fu.user_name = p_user_name;
 
   SELECT fpo.profile_option_name
     INTO l_profile_option_name
     FROM fnd_profile_options_vl fpo
    WHERE fpo.user_profile_option_name = p_user_profile_option_name;
 
   IF Fnd_Profile.save(x_name        => l_profile_option_name,
                       x_value       => p_profile_option_value,
                       x_level_name  => 'USER',
                       x_level_value => l_user_id) THEN
     NULL;
   END IF;
 
 END set_profile_at_user_level;

BEGIN

  EXECUTE IMMEDIATE 'ALTER session SET NLS_LANGUAGE=''RUSSIAN''';

  set_profile_at_user_level (l_user_name, 'ВЕБ: время ожидания для сеанса','3600000');
  set_profile_at_user_level (l_user_name, 'ВЕБ: форматная маска даты','DD-MM-RRRR');
  set_profile_at_user_level (l_user_name, 'Служебные программы: диагностика','Y');
  set_profile_at_user_level (l_user_name, 'Цветовая схема Java','SWAN');
  
  set_profile_at_user_level (l_user_name, 'Настройка веб-определений','Y');
  set_profile_at_user_level (l_user_name, 'БОП: диагностика','Y');
  set_profile_at_user_level (l_user_name, 'БОП: активизация ссылки области индивидуальной настройки', 'Y');
  set_profile_at_user_level (l_user_name, 'БОП: режим встраивания индивидуальной настройки','Y');
  COMMIT;

END;

SELECT a.application_id         as app_id,
       a.application_short_name as app_code,
       aru.application_name     as ru_app_name,
       aus.application_name     as en_app_name,
       fpi.patch_level
FROM applsys.fnd_application a,
       (SELECT * FROM applsys.fnd_application_tl au 
                 WHERE au.LANGUAGE = 'US') aus,
       (SELECT * FROM applsys.fnd_application_tl ar
                 WHERE ar.LANGUAGE = 'RU') aru,
       fnd_product_installations fpi
WHERE a.application_id = aus.application_id
and a.application_id = aru.application_id
and fpi.application_id(+) = a.application_id
ORDER BY a.application_id, a.application_short_name, aru.application_name

Читать дальше про “Информация о модулях OEBS + номер версии” »

SELECT fpo.profile_option_name
      ,fpo.user_profile_option_name
      ,fpov.profile_option_value
FROM fnd_profile_option_values fpov,
       fnd_profile_options_vl    fpo
WHERE fpov.application_id    = fpo.application_id
      AND fpov.profile_option_id = fpo.profile_option_id
      AND fpov.level_value = (
                        SELECT fu.user_id
                        FROM fnd_user fu
                        WHERE fu.user_name = '!!!USER_NAME'
                       )