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.segment1,
msi.segment2,
msi.segment3,
&P_item_flex C_item_flex,
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.segment1,
MSI.segment2,
MSI.segment3,
&P_item_flex C_item_flex,
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 3,4,5*/
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
Parameter Name SQL text Validation
Category Structure
 
Number
INV_SRS_CG_SELECTION_DUMMY
 
Number
INV_SRS_CHARACTER
 
PJM Enabled Flag
 
WMS Enabled Flag
 
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
Source Type for Column Four
 
LOV Oracle
Source Type for Column Three
 
LOV Oracle
Source Type for Column Two
 
LOV Oracle
Source Type for Column One
 
LOV Oracle
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
Category Set
 
LOV Oracle
Rollback to this Date
 
Date
Include Consigned
 
LOV Oracle
To
 
LOV Oracle
Cost Groups From
 
LOV Oracle
Sort By
 
LOV Oracle
Selection Option
 
LOV Oracle