INV Physical inventory accuracy analysis

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Physical inventory accuracy analysis
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
Parameter Name SQL text Validation
Category Set
 
LOV Oracle
Physical Inventory
 
LOV Oracle
Adjustment Value Sort Option
 
LOV Oracle
Include Rejected Items
 
LOV Oracle