select x.io
    , x.subinventory
    , x.subinv_desc
    , x.item_code
    , x.item_desc
    , x.item_cost
    , x.primary_uom
    , sum(case when x.transaction_type =  'S-1 Scrap' then x.quantity end) scrap_quantity
    , sum(case when x.transaction_type =  'S-1 Scrap' then x.amount end) scrap_amount
    , sum(case when x.transaction_type in ('E-1 Expense Another', 'E-4 Sample Expense') then x.quantity end) expense_quantity
    , sum(case when x.transaction_type in ('E-1 Expense Another', 'E-4 Sample Expense') then x.amount end) expense_amount
    , x.period_monyy period
from
(select ood.organization_code io
    , mmt.subinventory_code subinventory
    , ms.description subinv_desc
    , msi.segment1 item_code
    , msi.description item_desc
    , mmt.new_cost item_cost
    , msi.primary_uom_code primary_uom
    , mtt.transaction_type_name transaction_type
    , mmt.transaction_quantity quantity
    , mmt.new_cost * mmt.transaction_quantity amount
    , mmt.transaction_date
    , to_char(mmt.transaction_date, 'YYYY-MM') period_yyyymm
    , to_char(mmt.transaction_date, 'MON-YY') period_monyy
from apps.mtl_material_transactions mmt
    , apps.org_organization_definitions ood
    , apps.mtl_system_items msi
    , apps.mtl_secondary_inventories ms
    , apps.mtl_transaction_types mtt
where 1=1
and ood.organization_id = mmt.organization_id
and ood.operating_unit = 85
and ood.organization_code <> 'GM0'
and msi.organization_id = mmt.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and ms.organization_id = mmt.organization_id
and ms.secondary_inventory_name = mmt.subinventory_code
and mtt.transaction_type_id = mmt.transaction_type_id
and mtt.transaction_type_name in (
'S-1 Scrap'
, 'E-1 Expense Another'
, 'E-4 Sample Expense'
)
and (ood.organization_code = :io_code or :io_code is null)
and (msi.segment1 = :item_code or :item_code is null)
and (mmt.subinventory_code = :subinventory or : subinventory is null)
and (trunc(mmt.transaction_date) >= to_date(:txn_date_from, 'DD/MM/YYYY') or :txn_date_from is null)
and (trunc(mmt.transaction_date) <= to_date(:txn_date_to, 'DD/MM/YYYY') or :txn_date_to is null)
and (to_char(mmt.transaction_date, 'MON-YY') = :period or :period is null)
) x
group by x.io
    , x.subinventory
    , x.subinv_desc
    , x.item_code
    , x.item_desc
    , x.item_cost
    , x.primary_uom
    , x.period_yyyymm
    , x.period_monyy
order by x.io
    , x.subinventory
    , x.item_code
    , x.period_yyyymm desc