APPS-ORACLE.RU

-- транзакции с материалами
select org_id,
       date_val,
       item_id,
       uom_code,
       sum(receive_qty)      as receive_qty,
       abs(sum(charges_qty)) as charges_qty
from (
  select msi.organization_id         as org_id,
         trunc(mmt.transaction_date) as date_val,
         msi.inventory_item_id       as item_id,
         msi.primary_uom_code        as uom_code,
         null                        as demand_qty,
         null                        as ordered_qty,
         case when (mmt.primary_quantity > 0 and mmt.transaction_type_id != 3)
                   -- Возвраты
                   or (mmt.primary_quantity < 0 and mmt.transaction_type_id = 36)
                   -- Расход по прямому переносу
                   or (mmt.primary_quantity < 0 and mmt.transaction_type_id = 3)  then mmt.primary_quantity
              when -- Приход по прямому переносу
                   (mmt.primary_quantity > 0 and mmt.transaction_type_id = 3) then -1 * mmt.primary_quantity
         end receive_qty,
         --
         case when (mmt.primary_quantity < 0 and mmt.transaction_type_id not in (36,3) )
                   -- Приход по прямому переносу                  
                   or (mmt.primary_quantity > 0 and mmt.transaction_type_id = 3)  then mmt.primary_quantity
              when -- Возвраты
                   (mmt.primary_quantity < 0 and mmt.transaction_type_id = 36)
                   -- Расход по прямому переносу
                   or (mmt.primary_quantity < 0 and mmt.transaction_type_id = 3)   then 0
         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')
)
group by org_id,
         date_val,
         item_id,
         uom_code
order by org_id, item_id, date_val;

  select msi.organization_id         as org_id,      -- id организации
         mmt.subinventory_code       as subinv,      -- код складского подразделения
         trunc(mmt.transaction_date) as date_val,    -- дата транзакции
         msi.inventory_item_id       as rg_id,       -- id позиции
         mmt.primary_quantity        as receive_qty, -- полученное количество
         msi.primary_uom_code        as uom,         -- единица измерения
         rsh.receipt_num             as receipt_num, -- номер поступления
         rsh.waybill_airbill_num     as nakl_num,    -- номер накладной
         rsh.shipped_date            as nakl_date,   -- дата накладной
         pov.vendor_name             as vendor_name, -- имя поставщика
         poh.segment1                as po_num       -- номер заказа на приобретение
         --,mmt.transaction_type_id
    from mtl_system_items_b        msi,
         mtl_material_transactions mmt,
         rcv_transactions          rt,
         rcv_shipment_headers      rsh,
         po_headers_all            poh,
         po_vendors                pov
   where 1 = 1
        -- msi
        and msi.attribute_category = 'WB_Reagent'
        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, 'yyyy')
        -- rt
        and mmt.rcv_transaction_id = rt.transaction_id
        and mmt.organization_id    = rt.organization_id
        -- rsh
        and rt.shipment_header_id = rsh.shipment_header_id
        and rt.organization_id    = rsh.organization_id
        -- poh
        and rt.po_header_id = poh.po_header_id
        -- pov
        and pov.vendor_id = rsh.vendor_id;