INV Transaction historical summary

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Transaction historical summary (XML)
Short Name: INVTRHAN_XML
DB package: INV_INVTRHAN_XMLP_PKG
select decode(:p_sort_id,1,subinv,'X') subinventory,
decode(:p_sort_id,3,&p_cat_flex,'X') c_cat_flex,
msi.concatenated_segments item,
msi.description description,
msi.primary_uom_code uom,
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,
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)) cur_qty_val_old,
sum(nvl(cur_qty,0)) cur_qty,
sum(nvl(target_qty,0)) target_qty,
decode (:p_sort_id,1,msub.asset_inventory,0) ass_inv,
msi.inventory_item_id item_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','PADDED_VALUE') c_cat_pad,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','VALUE') c_cat_field,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field','INV','MSTK',101,msi.organization_id,msi.inventory_item_id,'ALL','Y','VALUE') c_item_field,
inv_invtrhan_xmlp_pkg.c_source_type5_cformula(
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)))
) c_source_type5_c,
inv_invtrhan_xmlp_pkg.c_othersformula
(
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))),
:c_cost_type,
msi.inventory_item_id,
decode(:p_sort_id,1,subinv,'X'),
sum(nvl(target_qty,0)),
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))),
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))),
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))),
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))),
:c_std_prec,
inv_invtrhan_xmlp_pkg.cur_qty_valformula
(
decode (:p_sort_id,1,msub.asset_inventory,0),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
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))),
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))),
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))),
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))),
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)))
)
) c_others,
inv_invtrhan_xmlp_pkg.cur_qty_valformula
(
decode (:p_sort_id,1,msub.asset_inventory,0),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
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))),
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))),
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))),
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))),
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)))
) cur_qty_val,
inv_invtrhan_xmlp_pkg.c_target_qty_valformula
(
:c_cost_type,
decode (:p_sort_id,1,msub.asset_inventory,0),
sum(nvl(target_qty,0)),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
inv_invtrhan_xmlp_pkg.cur_qty_valformula
(
decode (:p_sort_id,1,msub.asset_inventory,0),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
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))),
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))),
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))),
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))),
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)))
),
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))),
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))),
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))),
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))),
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))),
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))),
msi.inventory_item_id,
decode(:p_sort_id,1,subinv,'X'),
:c_std_prec
) c_target_qty_val,
inv_invtrhan_xmlp_pkg.c_change_valformula
(
inv_invtrhan_xmlp_pkg.c_target_qty_valformula
(
:c_cost_type,
decode (:p_sort_id,1,msub.asset_inventory,0),
sum(nvl(target_qty,0)),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
inv_invtrhan_xmlp_pkg.cur_qty_valformula
(
decode (:p_sort_id,1,msub.asset_inventory,0),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
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))),
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))),
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))),
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))),
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)))
),
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))),
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))),
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))),
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))),
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))),
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))),
msi.inventory_item_id,
decode(:p_sort_id,1,subinv,'X'),
:c_std_prec
),
inv_invtrhan_xmlp_pkg.cur_qty_valformula
(
decode (:p_sort_id,1,msub.asset_inventory,0),
decode(:p_selection,1,sum(nvl(cur_qty_val,0)),
round(sum(nvl(cur_qty_val,0)),:c_std_prec)),
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))),
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))),
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))),
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))),
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)))
)
) c_change_val,
inv_invtrhan_xmlp_pkg.c_change_qtyformula
(
sum(nvl(cur_qty,0)),
sum(nvl(target_qty,0))
) c_change_qty
from
mtl_secondary_inventories msub,
mtl_system_items_vl msi
&c_from_cat
,&p_view v
where
item_id=msi.inventory_item_id
and msub.organization_id=:p_org_id
and msub.secondary_inventory_name=subinv
and
(
(msub.asset_inventory=1) or
(msub.asset_inventory=2) and (msub.quantity_tracked=1)
)
and
(
((:p_selection=1 ) and
(((msub.asset_inventory=1) and (msub.quantity_tracked=1))
or ((msub.asset_inventory=2) and (msub.quantity_tracked=1)))
)
or
((:p_selection <>1 ) and
(msub.asset_inventory=1) and (msub.quantity_tracked=1) )
)
and &p_where_cat &c_where_cat
and msi.organization_id=:p_org_id
and &p_where_item
&c_where_subinv
and decode(:p_selection ,1 ,'N' ,:p_wms_pjm_enabled)='N'
group by decode(:p_sort_id,1,subinv,'X'),
decode(:p_sort_id,3,&p_cat_flex,'X'),
msi.segment1,msi.segment2,msi.segment3,
&p_item_order,
&p_item_flex,v.item_cost,msi.description,
msi.primary_uom_code,
decode(:p_sort_id,1,msub.asset_inventory,0),
msi.inventory_item_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','PADDED_VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field','INV','MSTK',101,msi.organization_id,msi.inventory_item_id,'ALL','Y','VALUE')
union
select decode(:p_sort_id,4,ccg.cost_group,'X') cg ,
decode(:p_sort_id,3,&p_cat_flex ,'X') c_cat_flex,
msi.concatenated_segments item,
msi.description description,
msi.primary_uom_code uom,
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 ,
round(
sum(ciqt.rollback_qty) * past_cost.item_cost,:c_std_prec
) cur_qty_val_old ,
sum(
decode(
ciqt.qty_source,
3,ciqt.rollback_qty,
4,ciqt.rollback_qty,
6,ciqt.rollback_qty,
7,ciqt.rollback_qty,
0
)
) cur_qty,
sum(ciqt . rollback_qty ) target_qty ,
0 ass_inv,
msi.inventory_item_id item_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','PADDED_VALUE') c_cat_pad,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','VALUE') c_cat_field,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field','INV','MSTK',101,msi.organization_id,msi.inventory_item_id,'ALL','Y','VALUE') c_item_field,
inv_invtrhan_xmlp_pkg.c_source_type5_cformula(0) c_source_type5_c,
inv_invtrhan_xmlp_pkg.c_othersformula(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
),
:c_cost_type,
msi.inventory_item_id,
decode(:p_sort_id,4,ccg.cost_group,'X'),
sum(ciqt . rollback_qty ),
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
),
:c_std_prec,
inv_invtrhan_xmlp_pkg.cur_qty_valformula(0,
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
),
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
))
) c_others,
--done
inv_invtrhan_xmlp_pkg.cur_qty_valformula(0,
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
),
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
)) cur_qty_val,
inv_invtrhan_xmlp_pkg.c_target_qty_valformula(:c_cost_type,0,
sum(ciqt . rollback_qty ),
round(
sum(ciqt.rollback_qty) * past_cost.item_cost,:c_std_prec
),
inv_invtrhan_xmlp_pkg.cur_qty_valformula(0,
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
),
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
)),
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
),
0,
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
),
msi.inventory_item_id,
decode(:p_sort_id,4,ccg.cost_group,'X'),
:c_std_prec) c_target_qty_val,
--not done
inv_invtrhan_xmlp_pkg.c_change_valformula(
inv_invtrhan_xmlp_pkg.c_target_qty_valformula(:c_cost_type,0,
sum(ciqt . rollback_qty ),
round(
sum(ciqt.rollback_qty) * past_cost.item_cost,:c_std_prec
),
inv_invtrhan_xmlp_pkg.cur_qty_valformula(0,
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
),
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
)),
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
),
0,
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
),
msi.inventory_item_id,
decode(:p_sort_id,4,ccg.cost_group,'X'),
:c_std_prec),
inv_invtrhan_xmlp_pkg.cur_qty_valformula(0,
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
),
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
))
) c_change_val,
--done
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 )) c_change_qty
from
cst_inv_qty_temp ciqt,
cst_inv_cost_temp current_cost,
cst_inv_cost_temp past_cost,
mtl_system_items_vl msi,
mtl_categories_kfv mc,
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 msi.organization_id=ciqt.organization_id
and msi.inventory_item_id=ciqt.inventory_item_id
and mc.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'
group by
decode(:p_sort_id,4,ccg.cost_group,'X' ),
decode(:p_sort_id,3,&p_cat_flex ,'X') ,
msi.segment1,msi.segment2,msi.segment3,
&p_item_order ,
&p_item_flex,
msi.description,
msi.primary_uom_code,
0 ,
msi.inventory_item_id,
past_cost.item_cost,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_pad','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','PADDED_VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_field','INV','MCAT',mc.structure_id,null,mc.category_id,'ALL','Y','VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field','INV','MSTK',101,msi.organization_id,msi.inventory_item_id,'ALL','Y','VALUE')
order by subinventory,ass_inv,c_cat_flex,--c_cat_pad,/*bug1414168,sorted by category should be depended on the sort option*/
--item_id,/*bug1414168,it doesnot make sense to order by item_id*/
3,4,5