INV Transaction Historical Summary

select
 :p_org_code organization,
 x.subinventory "Subinventory/Cost Group",
 x.asset_inventory,
 x.category,
 x.item,
 x.item_description,
 x.user_item_type,
 x.uom,
 --
 :p_hist_date        rollback_date,
 :p_selection_dsp    report_type,
 case :p_selection
 when '1' then
  inv_invtrhan_xmlp_pkg.c_target_qty_valformula
   (:c_cost_type,
    x.ass_inv,
    x.target_quantity,
    x.current_quantity_value_old,
    inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type),
    x.source_type1,
    x.source_type2,
    x.source_type3,
    x.source_type4,
    x.source_type5,
    x.other_type,
    x.inventory_item_id,
    x.subinventory,
    :c_std_prec
   )
 else
    x.target_quantity
 end "Rollback Date Quantity",
 case :p_selection
 when '1' then x.current_quantity_value_old
 else x.current_quantity
 end "Current Quantity",
 x.change_quantity,
 --
 &lp_template_dummy_columns
 &lp_source_columns
 --
 inv_invtrhan_xmlp_pkg.c_othersformula
  (x.other_type,
   :c_cost_type,
   x.inventory_item_id,
   x.subinventory,
   x.target_quantity,
   x.source_type1,
   x.source_type2,
   x.source_type3,
   x.source_type4,
   :c_std_prec,
   inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type)
  ) other_types,
 --
 case when :p_selection in ('2','3')
 then
  inv_invtrhan_xmlp_pkg.c_target_qty_valformula
   (:c_cost_type,
    x.ass_inv,
    x.target_quantity,
    x.current_quantity_value_old,
    inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type),
    x.source_type1,
    x.source_type2,
    x.source_type3,
    x.source_type4,
    x.source_type5,
    x.other_type,
    x.inventory_item_id,
    x.subinventory,
    :c_std_prec
   )
 end "Rollback Date Value",
 --
 case when :p_selection in ('2','3')
 then inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type)
 end "Current Value",
 --
 inv_invtrhan_xmlp_pkg.c_change_valformula
  (inv_invtrhan_xmlp_pkg.c_target_qty_valformula
    (:c_cost_type,
     x.ass_inv,
     x.target_quantity,
     x.current_quantity_value_old,
     inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type),
     x.source_type1,
     x.source_type2,
     x.source_type3,
     x.source_type4,
     x.source_type5,
     x.other_type,
     x.inventory_item_id,
     x.subinventory,
     :c_std_prec
    ),
   inv_invtrhan_xmlp_pkg.cur_qty_valformula(x.ass_inv,x.current_quantity_value_old,x.source_type1,x.source_type2,x.source_type3,x.source_type4,x.other_type)
  ) "Change Value",
 --
 case :p_sort_id
 when '1' then x.subinventory -- subinventory
 when '2' then x.subinventory -- item
 when '3' then x.category     -- category     
 when '4' then x.subinventory -- costgroup
 end pivot_key1,
 case :p_sort_id
 when '1' then x.category     -- subinventory
 when '2' then x.category     -- item
 when '3' then x.subinventory -- category     
 when '4' then x.category     -- costgroup
 end pivot_key2
from
 (select
   v.subinv subinventory,
   msub.asset_inventory ass_inv,
   xxen_util.meaning(msub.asset_inventory,'SYS_YES_NO',700) asset_inventory,
   mck.concatenated_segments category,
   msiv.concatenated_segments item,
   msiv.description item_description,
   xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
   msiv.primary_uom_code uom,
   msiv.inventory_item_id,
   --
   sum(nvl(target_qty,0)) target_quantity,
   sum(nvl(cur_qty,0)) current_quantity,
   inv_invtrhan_xmlp_pkg.c_change_qtyformula(sum(nvl(cur_qty,0)),sum(nvl(target_qty,0))) change_quantity,
   decode(:p_selection,1,sum(nvl(cur_qty_val,0)),round(sum(nvl(cur_qty_val,0)),:c_std_prec)) current_quantity_value_old,
   --
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(source_type1,0))*v.item_cost,:c_std_prec),round(sum(nvl(source_type1,0)),:c_std_prec)),
          3,round(sum(nvl(source_type1,0)),:c_std_prec),
            sum(nvl(source_type1,0))
         ) source_type1,
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(source_type2,0))*v.item_cost,:c_std_prec),round(sum(nvl(source_type2,0)),:c_std_prec)),
          3,round(sum(nvl(source_type2,0)),:c_std_prec),
            sum(nvl(source_type2,0))
         ) source_type2,
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(source_type3,0))*v.item_cost,:c_std_prec),round(sum(nvl(source_type3,0)),:c_std_prec)),
          3,round(sum(nvl(source_type3,0)),:c_std_prec),
            sum(nvl(source_type3,0))
         ) source_type3,
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(source_type4,0))*v.item_cost,:c_std_prec),round(sum(nvl(source_type4,0)),:c_std_prec)),
          3,round(sum(nvl(source_type4,0)),:c_std_prec),
            sum(nvl(source_type4,0))
         ) source_type4,
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(source_type5,0))*v.item_cost,:c_std_prec),round(sum(nvl(source_type5,0)),:c_std_prec)),
          3,round(sum(nvl(source_type5,0)),:c_std_prec),
            sum(nvl(source_type5,0))
         ) source_type5,
   decode(:p_selection,
          2,decode(:c_cost_type,2,round(sum(nvl(other,0))*v.item_cost,:c_std_prec),round(sum(nvl(other,0)),:c_std_prec)),
          3,round(sum(nvl(other,0)),:c_std_prec),
            sum(nvl(other,0))
         ) other_type
  from
   mtl_secondary_inventories msub,
   mtl_system_items_vl msiv,
   mtl_item_categories mic,
   mtl_categories_kfv mck,
   (
   select
   moqd.subinventory_code subinv,
   moqd.inventory_item_id item_id,
   0 item_cost,
   0 source_type1,0 source_type2,0 source_type3,0 source_type4,0 source_type5,0 other,
   sum(moqd.primary_transaction_quantity) cur_qty_val,
   sum(moqd.primary_transaction_quantity) cur_qty,
   sum(moqd.primary_transaction_quantity) target_qty
   from
   mtl_onhand_quantities_detail moqd
   where
   :p_selection='1' and
   moqd.organization_id=:p_org_id and
   moqd.owning_tp_type=decode(:p_consigned,2,2,moqd.owning_tp_type)
   group by
   moqd.subinventory_code,moqd.inventory_item_id
   union all
   select
   mmt.subinventory_code,
   mmt.inventory_item_id,
   0,
   sum(decode(mtst.transaction_source_type_id,:p_stype1,mmt.primary_quantity)),
   sum(decode(mtst.transaction_source_type_id,:p_stype2,mmt.primary_quantity)),
   sum(decode(mtst.transaction_source_type_id,:p_stype3,mmt.primary_quantity)),
   sum(decode(mtst.transaction_source_type_id,:p_stype4,mmt.primary_quantity)),
   0,
   sum(decode(mtst.transaction_source_type_id,:p_stype1,0,:p_stype2,0,:p_stype3,0,:p_stype4,0,mmt.primary_quantity)),
   0,0,
   -sum(mmt.primary_quantity)
   from
   mtl_material_transactions mmt,
   mtl_txn_source_types mtst,
   mtl_parameters mp
   where
   :p_selection='1' and
   mmt.organization_id=:p_org_id and
   mp.organization_id=:p_org_id and
   mmt.transaction_date>=:p_hist_date+1 and
   nvl(mmt.owning_tp_type,2)=decode(:p_consigned,2,2,nvl(mmt.owning_tp_type,2)) and
   mmt.transaction_source_type_id=mtst.transaction_source_type_id and
   nvl(mmt.logical_transaction,2)<>1
   group by
   mmt.subinventory_code,mmt.inventory_item_id,mp.primary_cost_method
   union all
   select
   moqv.subinventory_code,
   moqv.inventory_item_id,
   round(moqv.item_cost,15),
   0,0,0,0,0,0,
   decode(:p_selection,1,sum(moqv.transaction_quantity),sum(moqv.transaction_quantity*nvl(moqv.item_cost,0))),
   sum(moqv.transaction_quantity),
   sum(moqv.transaction_quantity)
   from
   mtl_onhand_qty_cost_v moqv
   where
   :p_selection<>'1' and
   moqv.organization_id=:p_org_id
   group by
   moqv.subinventory_code,moqv.inventory_item_id,moqv.item_cost
   union all
   select
   mmt.subinventory_code,
   mmt.inventory_item_id,
   round(cst.item_cost,15),
   sum(decode(mtst.transaction_source_type_id,:p_stype1,decode(:p_selection,1,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(:p_stype1,11,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),13,decode(mmt.transaction_action_id,24,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),mmt.primary_quantity*mmt.actual_cost),mmt.primary_quantity*mmt.actual_cost))),0)),
   sum(decode(mtst.transaction_source_type_id,:p_stype2,decode(:p_selection,1,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(:p_stype2,11,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),13,decode(mmt.transaction_action_id,24,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),mmt.primary_quantity*mmt.actual_cost),mmt.primary_quantity*mmt.actual_cost))),0)),
   sum(decode(mtst.transaction_source_type_id,:p_stype3,decode(:p_selection,1,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(:p_stype3,11,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),13,decode(mmt.transaction_action_id,24,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),mmt.primary_quantity*mmt.actual_cost),mmt.primary_quantity*mmt.actual_cost))),0)),
   sum(decode(mtst.transaction_source_type_id,:p_stype4,decode(:p_selection,1,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(:p_stype4,11,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),13,decode(mmt.transaction_action_id,24,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),mmt.primary_quantity*mmt.actual_cost),mmt.primary_quantity*mmt.actual_cost)))),0)),
   0,
   sum(decode(mtst.transaction_source_type_id,:p_stype1,0,:p_stype2,0,:p_stype3,0,:p_stype4,0,decode(:p_selection,1,mmt.primary_quantity,decode(mp.primary_cost_method,2,mmt.primary_quantity,decode(mtst.transaction_source_type_id,11,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),13,decode(mmt.transaction_action_id,24,mmt.quantity_adjusted*(mmt.new_cost-mmt.prior_cost),mmt.primary_quantity*mmt.actual_cost),mmt.primary_quantity*mmt.actual_cost))))),
   0,0,
   -sum(mmt.primary_quantity)
   from
   mtl_material_transactions mmt,
   mtl_txn_source_types mtst,
   mtl_parameters mp,
   cst_item_costs_for_gl_view cst
   where
   :p_selection<>'1' and
   mmt.organization_id=:p_org_id and
   mp.organization_id=:p_org_id and
   cst.organization_id=:p_org_id and
   cst.inventory_item_id=mmt.inventory_item_id and
   mmt.transaction_date>=:p_hist_date+1 and
   nvl(mmt.owning_tp_type,2)=2 and
   mmt.transaction_source_type_id=mtst.transaction_source_type_id and
   nvl(mmt.logical_transaction,2)<>1
   group by
   mmt.subinventory_code,mmt.inventory_item_id,cst.item_cost,mp.primary_cost_method
   &lp_template_dummy_view) v
  where
   v.item_id=msiv.inventory_item_id and
   msub.organization_id=:p_org_id and
   msub.secondary_inventory_name=v.subinv and
   msub.asset_inventory in (1,2) and
   msub.quantity_tracked=1 and
   (:p_selection=1 or :p_selection<>1 and msub.asset_inventory=1) and
   msiv.inventory_item_id = mic.inventory_item_id and 
   mic.category_id = mck.category_id and 
   mic.organization_id = to_char(:p_org_id) and 
   mic.category_set_id = to_char(:p_cat_set_id) and
   msiv.organization_id=:p_org_id and
   decode(:p_selection ,1 ,'N' ,:p_wms_pjm_enabled)='N' and
   1=1
  group by
   v.subinv,
   mck.concatenated_segments,
   msiv.concatenated_segments,
   v.item_cost,
   msiv.description,
   xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3),
   msiv.primary_uom_code,
   msub.asset_inventory,
   xxen_util.meaning(msub.asset_inventory,'SYS_YES_NO',700),
   msiv.inventory_item_id
  union
  select
   ccg.cost_group subinventory,
   0 ass_inv,
   null asset_inventory,
   mck.concatenated_segments category,
   msiv.concatenated_segments item,
   msiv.description item_description,
   xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
   msiv.primary_uom_code uom,
   msiv.inventory_item_id item_id,
   --
   sum(ciqt.rollback_qty ) target_quantity,
   sum(decode(ciqt.qty_source,3,ciqt.rollback_qty,4,ciqt.rollback_qty,6,ciqt.rollback_qty,7,ciqt.rollback_qty,0)) current_quantity,
   inv_invtrhan_xmlp_pkg.c_change_qtyformula
    (sum(decode(ciqt.qty_source,3,ciqt.rollback_qty,4,ciqt.rollback_qty,6,ciqt.rollback_qty,7,ciqt.rollback_qty,0)),
     sum(ciqt.rollback_qty)
    ) change_quantity,
   round(sum(ciqt.rollback_qty) * past_cost.item_cost,:c_std_prec) current_quantity_value_old,
   --
   round(sum(decode(ciqt.txn_source_type_id,:p_stype1,nvl(ciqt.rollback_value,0),0)),:c_std_prec) source_type1,
   round(sum(decode(ciqt.txn_source_type_id,:p_stype2,nvl(ciqt.rollback_value,0),0)),:c_std_prec) source_type2,
   round(sum(decode(ciqt.txn_source_type_id,:p_stype3,nvl(ciqt.rollback_value,0),0)),:c_std_prec) source_type3,
   round(sum(decode(ciqt.txn_source_type_id,:p_stype4,nvl(ciqt.rollback_value,0),0)),:c_std_prec) source_type4,
   0 source_type5,
   round(sum(decode(ciqt.qty_source,3 ,ciqt.rollback_qty ,4 ,ciqt.rollback_qty ,6 ,ciqt.rollback_qty ,7 ,ciqt.rollback_qty ,0 ) * current_cost.item_cost),:c_std_prec)
    - round(sum(ciqt.rollback_qty) * past_cost.item_cost,:c_std_prec)
    - round(sum(decode(ciqt.txn_source_type_id,:p_stype1,nvl(ciqt.rollback_value,0),0)),:c_std_prec)
    - round(sum(decode(ciqt.txn_source_type_id,:p_stype2,nvl(ciqt.rollback_value,0),0)),:c_std_prec)
    - round(sum(decode(ciqt.txn_source_type_id,:p_stype3,nvl(ciqt.rollback_value,0),0)),:c_std_prec )
    - round(sum(decode(ciqt.txn_source_type_id,:p_stype4,nvl(ciqt.rollback_value,0),0)),:c_std_prec) other_type
  from
   cst_inv_qty_temp ciqt,
   cst_inv_cost_temp current_cost,
   cst_inv_cost_temp past_cost,
   mtl_system_items_vl msiv,
   mtl_categories_kfv mck,
   cst_cost_groups ccg
  where
   current_cost.organization_id=ciqt.organization_id and
   current_cost.inventory_item_id=ciqt.inventory_item_id and
   current_cost.cost_source=1 and
   past_cost.organization_id=ciqt.organization_id and
   past_cost.inventory_item_id=ciqt.inventory_item_id and
   past_cost.cost_source= 2 and
   msiv.organization_id=ciqt.organization_id and
   msiv.inventory_item_id=ciqt.inventory_item_id and
   mck.category_id=ciqt.category_id and
   ccg.cost_group_id=ciqt.cost_group_id and
   decode(:p_selection ,1 ,'N' ,:p_wms_pjm_enabled)='Y' and
   2=2
  group by
   ccg.cost_group,
   mck.concatenated_segments,
   msiv.concatenated_segments,
   msiv.description,
   xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3),
   msiv.primary_uom_code,
   msiv.inventory_item_id,
   past_cost.item_cost
 ) x
order by
 case :p_sort_id
 when '1' then x.subinventory -- subinventory
 when '2' then null           -- item
 when '3' then x.category     -- category     
 when '4' then x.subinventory -- costgroup
 end,
 case :p_sort_id
 when '1' then x.category     -- subinventory
 when '2' then null           -- item
 when '3' then x.subinventory -- category     
 when '4' then x.category     -- costgroup
 end,
 x.item