Архив

Публикации с меткой ‘SQL’

Модуль INV — Приход и расход по позициям

-- транзакции с материалами: расход, приход
select msi.organization_id         as org_id,
       mmt.transaction_date        as date_val,
       msi.inventory_item_id       as item_id,
       msi.segment1                as item,
       msi.primary_uom_code        as uom_code,
       -- приход
       case when mmt.primary_quantity > 0 then mmt.primary_quantity
       end receive_qty,
       -- расход
       case when mmt.primary_quantity < 0 then abs(mmt.primary_quantity)
       end charges_qty
       -- 
from mtl_system_items_b        msi,
     mtl_material_transactions mmt
where 1 = 1
      -- msi 
      and msi.inventory_item_status_code = 'Active'
      -- mmt 
      and mmt.organization_id   = msi.organization_id
      and mmt.inventory_item_id = msi.inventory_item_id
       -- ! ограничиваем период 
      and mmt.transaction_date  >= trunc(sysdate, 'mm')
order by msi.organization_id, msi.inventory_item_id, mmt.transaction_date

Более сложный вариант, с учетом разных типов транзакций:

Читать дальше про “Модуль INV — Приход и расход по позициям” »

Заполнение пропусков в выборке

9 Июнь 2010 2 comments

Заполнение пропусков в выборке.

Пробелы в столбце VAL заполняем по следующему принципу: берем следующее значение VAL и отнимаем от него текущее значение X

Читать дальше про “Заполнение пропусков в выборке” »

Убрать пустые блоки из таблицы

Если в таблице слишком много пустых блоков, то оптимизировать данные помогут следующие действия

ALTER TABLE name_table MOVE;
ALTER INDEX name_index REBUILD;

и не забыть обновить статистику для таблицы

begin
dbms_stats.gather_table_stats(schema,table);
end;

Читать дальше про “Убрать пустые блоки из таблицы” »

Categories: SQL Tags: , , ,

Связка между плановыми и складскими позициями

SELECT mp.compile_designator as plan_name,
       msc.plan_id,
       msi.organization_id,
       msc.sr_inventory_item_id,
       msi.inventory_item_id,
       msi.segment1,
       msi.description
FROM msc_system_items   msc,
     mtl_system_items_b msi,
     msc_plans          mp
where 1 = 1
     and msi.inventory_item_id = msc.sr_inventory_item_id
     and msi.organization_id   = msc.organization_id
     -- plan
     and mp.plan_id = msc.plan_id
order by  mp.compile_designator,
          msc.plan_id,
          msi.organization_id,
          msi.inventory_item_id

Модуль GME/GMD – Связка между формулой, рецептом и правилами применимости

select msi.organization_id    as org_id,
       md.formula_id          as formula_id,
       md.formulaline_id      as formulaline_id,
       md.line_type           as line_type,
       msi.inventory_item_id  as item_id,
       msi.segment1           as item_no,
       msi.description        as item_descr,
       rr.start_date          as start_date,
       nvl(rr.end_date,to_date('01-01-2099','dd-mm-yyyy')) as end_date
/**/
from mtl_system_items_b        msi,
     fm_matl_dtl               md,
     gmd_recipes               r,
     gmd_recipe_validity_rules rr
where 1 = 1
      -- formula
      and md.organization_id   = msi.organization_id
      and md.inventory_item_id = msi.inventory_item_id
      -- recipes
      and r.formula_id    = md.formula_id
      and r.recipe_status = 700 -- Утверждено для общего использования
      and r.delete_mark   = 0   -- не помечен на удаление
      and r.owner_organization_id =  md.organization_id
      -- recepe rules
      and rr.recipe_id       = r.RECIPE_ID
      and rr.organization_id = md.organization_id
      and rr.delete_mark          = 0   -- не помечен на удаление
      and rr.validity_rule_status = 700 -- Утверждено для общего использования
      and rr.recipe_use           = 0   -- использование рецепта = Производство
order by msi.organization_id, md.formula_id, md.line_type desc, md.line_no


Модуль GME/GMD — Получить количество из формулы для строки задания

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
Categories: Oracle e-Business Suite, SQL Tags: , ,

Получить данные о таблице по ROWID

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
Categories: SQL Tags: ,

Агрегатная функция stragg — складывает строки

26 Май 2010 4 comments

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

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

Установка профилей для пользователя

14 Май 2010 2 comments

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

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;