-- транзакции с материалами: расход, приход 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
Более сложный вариант, с учетом разных типов транзакций:
-- транзакции с материалами 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;
Последние комментарии