INV Physical Inventory Tags

➜ Back to Library
Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Physical Inventory Tags (XML)
Short Name: INVARPTP_XML
DB package: INV_INVARPTP_XMLP_PKG 
SELECT
	mpi.physical_inventory_name physical_inventory, 
	mp.organization_code organization_code, 
	hou.name  organization_name,  
	mpit.tag_number tag, 
	'' C_Item_Flexdata, 
   	mpit.inventory_item_id item_id,
	'' description, 
	mpit.revision revision, 
	'' primary_UOM,
	mpit.subinventory subinventory, 
	''  C_Loc_Flexdata,
	mpit.locator_id locator_id,
	mpit.lot_number lot_number, 
	mpit.serial_num serial_number,
                   mpit.parent_lpn_id parent_lpn,
                   mpit.outermost_lpn_id outermost_lpn,
	mpit.cost_group_id, 
	null C_ITEM_VALUE, 
	null C_LOC_VALUE, 
	/*fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_value', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_LOC_VALUE,*/ 
	INV_INVARPTP_XMLP_PKG.cf_parent_lpnformula(mpit.parent_lpn_id) CF_parent_lpn, 
	INV_INVARPTP_XMLP_PKG.cf_outermost_lpnformula(mpit.outermost_lpn_id,mpit.parent_lpn_id) CF_outermost_lpn, 
	INV_INVARPTP_XMLP_PKG.cf_cost_groupformula(mpit.cost_group_id) CF_COST_GROUP
FROM
	mtl_parameters mp, 
	mtl_physical_inventories mpi, 
	hr_organization_units hou, 
	mtl_physical_inventory_tags mpit
WHERE
	mpit.organization_id = :p_org_id and 
	mp.organization_id = mpit.organization_id and 
	hou.organization_id = mp.organization_id and
 	mpi.physical_inventory_id = :p_physical and
	mpit.physical_inventory_id = mpi.physical_inventory_id and
	mpit.inventory_item_id is null and
	&P_RANGE_SQL 
UNION
SELECT
	mpi.physical_inventory_name physical_inventory, 
	mp.organization_code organization_code, 
	hou.name organization_name,  
	mpit.tag_number tag, 
	null C_Item_Flexdata, 
   	mpit.inventory_item_id item_id,
	msi.description description, 
	mpit.revision revision, 
	msi.primary_uom_code primary_UOM,
	mpit.subinventory subinventory, 
	null  C_Loc_Flexdata,
	mpit.locator_id locator_id,
	mpit.lot_number lot_number, 
	mpit.serial_num serial_number,
                   mpit.parent_lpn_id parent_lpn,
                   mpit.outermost_lpn_id outermost_lpn,
	mpit.cost_group_id, 
	decode(msi.inventory_item_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_ITEM_VALUE, 
	decode(mil.inventory_location_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_value', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) C_LOC_VALUE, 
	INV_INVARPTP_XMLP_PKG.cf_parent_lpnformula(mpit.parent_lpn_id) CF_parent_lpn, 
	INV_INVARPTP_XMLP_PKG.cf_outermost_lpnformula(mpit.outermost_lpn_id,mpit.parent_lpn_id) CF_outermost_lpn, 
	INV_INVARPTP_XMLP_PKG.cf_cost_groupformula(mpit.cost_group_id) CF_COST_GROUP
FROM
	mtl_system_items_vl msi,
	mtl_item_locations mil,
	mtl_parameters mp, 
	mtl_physical_inventories mpi, 
	hr_organization_units hou, 
	mtl_physical_inventory_tags mpit
WHERE    
	mpit.organization_id = :p_org_id and
	msi.inventory_item_id = mpit.inventory_item_id and
	msi.organization_id = mpit.organization_id and
	mp.organization_id = msi.organization_id and 
	hou.organization_id = mp.organization_id and
	mil.inventory_location_id (+)= mpit.locator_id and
                  mil.organization_id (+)= :p_org_id and
 	mpi.physical_inventory_id = :p_physical and
	mpit.physical_inventory_id = mpi.physical_inventory_id and
	((mpit.subinventory is null and :p_subinventory is null) or
	(mpit.subinventory = nvl(:p_subinventory, mpit.subinventory))) and
	&P_RANGE_SQL and
	&P_RANGE_FLEX
   and &P_ITEM_WHERE
   and &P_LOC_WHERE
&P_ORDER_BY
Parameter Name SQL text Validation
Organization 
 
 Number
To 
 
From Item 
 
To 
 
From Locator 
 
To 
 
From Tag 
 
Range 
 
 LOV Oracle
Sort By 
 
 LOV Oracle
Subinventory 
 
 LOV Oracle
Physical Inventory 
 
 LOV Oracle
Download
 
Related Blitz Reports
INV Transaction register
INV Serial number transaction register
INV Inventory Aging
INV Physical inventory tag listing
INV Physical inventory counts
INV Physical inventory missing tag listing
INV Serial number detail
INV Lot transaction register
INV Physical inventory trend and summary analysis