INV Physical inventory counts

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Physical inventory counts report
Application: Inventory
Source: Physical inventory counts report (XML)
Short Name: INVARPSL_XML
DB package: INV_INVARPSL_XMLP_PKG
Run INV Physical inventory counts and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Sort By
 
LOV Oracle
Category Set
 
LOV Oracle
Physical Inventory
 
LOV Oracle