INV Physical inventory counts

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Physical inventory counts report (XML)
Short Name: INVARPSL_XML
DB package: INV_INVARPSL_XMLP_PKG
select 	&P_item_flex	C_item_flex,
                  pit.revision,
	&P_cat_flex   	C_cat_flex,
	pit.tag_number,
	pit.subinventory,
	&P_loc_flex   	C_loc_flex,
                  loc.inventory_location_id loc_id,
	nvl(decode(pit.void_flag,1,0,2,pit.TAG_QUANTITY_AT_STANDARD_UOM),0) Quantity,
                  msi.primary_uom_code      UOM,
	round(nvl(pit.item_cost,0) ,:C_ext_prec )           Cost,
                  ppx.last_name              Counted_by,
                   decode(pit.item_cost, null, 1, 2)     expense_flag,
	pit.parent_lpn_id  parent_lpn_id,
	pit.outermost_lpn_id  outermost_lpn_id,
	pit.cost_group_id cost_group_id,
	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,
	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_loc_field', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_loc_field,
	INV_INVARPSL_XMLP_PKG.c_total_valueformula(nvl ( decode ( pit.void_flag , 1 , 0 , 2 , pit.TAG_QUANTITY_AT_STANDARD_UOM ) , 0 ), round ( nvl ( pit.item_cost , 0 ) , :C_ext_prec ), :C_std_prec) C_total_value,
	INV_INVARPSL_XMLP_PKG.cf_outermost_lpnformula(pit.outermost_lpn_id) CF_OUTERMOST_LPN,
	INV_INVARPSL_XMLP_PKG.cf_parent_lpnformula(pit.parent_lpn_id) CF_parent_lpn,
	INV_INVARPSL_XMLP_PKG.cf_cost_groupformula(pit.cost_group_id) CF_COST_GROUP
from
	mtl_system_items	    msi,
	mtl_categories                       mc,
	mtl_item_categories              mic,
	mtl_item_locations                loc,
                  per_people_x                          ppx,
	mtl_phy_inv_tags_cost_v       pit
where
pit.organization_id = :P_org_id
and     pit.physical_inventory_id =  :P_phys_inv_id
and    msi.organization_id = pit.organization_id
and     mic.organization_id = pit.organization_id
and     loc.organization_id(+) = pit.organization_id
and     pit.inventory_item_id =  msi.inventory_item_id
and     pit.inventory_item_id =  mic.inventory_item_id
and     mic.category_set_id = :P_category_set_id
and     mic.category_id = mc.category_id
and     pit.locator_id =  loc.inventory_location_id(+)
and     pit.counted_by_employee_id = ppx.person_id(+)
--order by C_order_by
order by  MSI.SEGMENT1,MC.SEGMENT1, MC.SEGMENT2,4,5
Parameter Name SQL text Validation
Category Structure
 
Number
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
Physical Inventory
 
LOV Oracle
Category Set
 
LOV Oracle
Sort By
 
LOV Oracle