INV Physical inventory accuracy analysis

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Physical inventory accuracy analysis (XML)
Short Name: INVARPIA_XML
DB package: INV_INVARPIA_XMLP_PKG
select &P_cat_flex        C_cat_flex,
          &P_cat_sort        C_cat_sort,
       &P_item_flex       C_item_flex,  
       mpav.revision       rev,
       round(system_quantity,:P_qty_precision) system_quantity,
       msi.primary_uom_code UOM,
       round(nvl(count_quantity,0), :P_qty_precision) count_quantity,
       round(adjustment_quantity,:P_qty_precision) adjustment_quantity,
       round(nvl(system_quantity*mpav.actual_cost,0),:C_std_prec)   system_value,
       round(nvl(count_quantity*mpav.actual_cost,0),:C_std_prec)    count_value,
       round(nvl(nvl(count_quantity*mpav.actual_cost,0) -
       system_quantity*mpav.actual_cost,0),:C_std_prec)   adjustment_value,
      decode (round(nvl(count_quantity*mpav.actual_cost,0),:C_std_prec),  0,  0,
                    (round(nvl(nvl(count_quantity*mpav.actual_cost,0) -
                    system_quantity*mpav.actual_cost,0),:C_std_prec) *  
       100/round(nvl(count_quantity*mpav.actual_cost,0),:C_std_prec))) adj_percent, 
	&C_cat_pad 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, 
	INV_INVARPIA_XMLP_PKG.c_adj_per_catformula(:C_cnt_val_cat, :C_adj_val_cat, :C_std_prec) C_adj_per_cat, 
	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
from   
       mtl_system_items           msi,
       mtl_item_categories        mic,
       mtl_categories              mc,
       mtl_phy_adj_cost_v  mpav
where  mic.category_id = mc.category_id
and    mic.category_set_id = :P_category_set_id
and    msi.organization_id = mic.organization_id
and    mic.organization_id = mpav.organization_id
and    mic.inventory_item_id = msi.inventory_item_id
and    mpav.inventory_item_id = mic.inventory_item_id
and    mpav.organization_id = :P_org_id
and    (nvl(count_quantity,0) <> 0 or  nvl(system_quantity,0) <> 0)
and    mpav.physical_inventory_id = :P_phys_inv_id
-- P_condition this does not do any thing
&P_include_rej_items_opt
order by C_cat_field ASC,nvl(count_quantity*mpav.actual_cost-system_quantity*mpav.actual_cost,0) &C_sort, &P_order_item
Ask a question
Parameter Name SQL text Validation
Category Structure
 
Number
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
Include Rejected Items
 
LOV Oracle
Adjustment Value Sort Option
 
LOV Oracle
Physical Inventory
 
LOV Oracle
Category Set
 
LOV Oracle