Архив

Архив раздела ‘SQL’

Удаление всех объектов схемы

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

select 'drop '||object_type||' '||object_name
       ||decode(object_type,'TABLE',' cascade constraints;','TYPE',' force;',';')
from user_objects
where object_type in ('TABLE','VIEW','SEQUENCE','PACKAGE','PROCEDURE','FUNCTION','TYPE')
order by object_type;

Дополнительно можно почистить корзину

  -- удалить все объекты из корзины текущего пользователя
  purge recyclebin;
Categories: SQL Tags: , ,

Использование RECYCLE BIN

15 Июнь 2010 1 комментарий

В 10 версии добавилась возможность востанавливать удаленные таблицы и индексы используя корзину.

Для того что бы включить\выключить корзину:

  -- Отключить корзину для сессии
  ALTER SESSION SET RECYCLEBIN=OFF;
  -- Отключить корзину
  ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;
  -- Включить корзину для сессии
  ALTER SESSION SET RECYCLEBIN=ON;
  -- Включить корзину
  ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;

По умолчанию корзина включена.

Читать дальше про “Использование RECYCLE BIN” »

Categories: SQL Tags: , ,

DML Error Logging

В 10.2  появилось логирование ошибок при выполнение DML команд INSERT, UPDATE, MERGE и DELETE.

Если DML-операция при массовой  обработке данных вызывает ошибки при добавлении или изменении, какой либо записи, то вся операция DML будет отменена. Новая инструкция — LOG ERRORS,  предназначена для обхода подобных ситуация. Если в процессе выполнения DML операции возникает ошибка, то информация о ней  запишется в заранее созданную (при помощи пакета DBMS_ERRLOG) таблицу логов, на которую указывает конструкция LOG ERRORS.

Читать дальше про “DML Error Logging” »

Categories: SQL Tags: , , , ,

Модуль INV — Текущее количество для позиции

Select moq.organization_id                   as org_id,
       sysdate                               as date_val,
       moq.inventory_item_id                 as item_id,
       moq.subinventory_code                 as subinventory_code,
       moq.locator_id                        as locator_id,
       moq.lot_number                        as lot_number,
       msi.segment1                          as item,
       msi.primary_uom_code                  as uom_code,
       sum(moq.primary_transaction_quantity) as actual_qty
  from mtl_onhand_quantities_detail moq,
       mtl_system_items_b           msi
where 1=1
      -- msi
      and msi.inventory_item_id  = moq.inventory_item_id
      and msi.organization_id    = moq.organization_id
      --and moq.inventory_item_id  = :1
      --and moq.organization_id    = :2
      --and moq.subinventory_code  = :3
      --and moq.lot_number         = :4
group by moq.organization_id ,
         sysdate,
         moq.inventory_item_id,
         msi.segment1,
         msi.primary_uom_code,
         moq.subinventory_code,
         moq.locator_id,
         moq.lot_number
order by moq.organization_id, moq.inventory_item_id

Так же есть API для просмотра текущего количества OEBS API INV – Текущее количество для позиции (ONHAND)

Модуль PO – Сколько запрошенно по позициям

-- заявка на приобретение
select prl.destination_organization_id as org_id,
       prl.need_by_date                as date_val,
       prl.item_id                     as item_id,
       msi.segment1                    as item,
       msi.primary_uom_code            as uom_code,
       -- запрошенно
       prl.quantity                    as demand_qty
from po_requisition_lines_all   prl,
     po_requisition_headers_all phr,
     mtl_system_items_b         msi
where 1=1
      -- prl
       -- ! ограничиваем период
      and prl.need_by_date  >= trunc(sysdate, 'mm')
      -- phr
      and phr.requisition_header_id = prl.requisition_header_id
      and phr.authorization_status = 'APPROVED' -- утверждённые заявки
      -- msi
      and msi.inventory_item_id  = prl.item_id
      and msi.organization_id    = prl.destination_organization_id
order by prl.destination_organization_id,
         msi.inventory_item_id,
         prl.need_by_date

Модуль PO — Сколько заказано по позициям

-- заказы на приобретение
Select pll.ship_to_organization_id as org_id,
       pll.need_by_date            as date_val,
       pla.item_id                 as item_id,
       msi.segment1                as item,
       msi.primary_uom_code        as uom_code,
       -- заказано
       pla.quantity                as ordered_qty
from po_line_locations_all pll,
       po_lines_all          pla,
       po_headers_all        pha,
       mtl_system_items_b    msi
where 1=1
      -- pll
      and pll.po_line_id = pla.po_line_id
       -- ! ограничиваем период
      and pll.need_by_date >= trunc(sysdate, 'mm')
      -- msi
      and msi.inventory_item_id  = pla.item_id
      and msi.organization_id    = pll.ship_to_organization_id
      --  pha
      and pha.po_header_id = pla.po_header_id
      and pha.authorization_status = 'APPROVED' -- только утвержденные ЗП
order by pll.ship_to_organization_id, msi.inventory_item_id, pll.need_by_date

Модуль 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