INV Physical Inventory Tags

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Physical Inventory Tags
Application: Inventory
Source: Physical Inventory Tags (XML)
Short Name: INVARPTP_XML
DB package: INV_INVARPTP_XMLP_PKG
Run INV Physical Inventory Tags and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Physical Inventory
 
LOV Oracle
Subinventory
 
LOV Oracle
Sort By
 
LOV Oracle
Range
 
LOV Oracle
From Tag
 
Char
To
 
Char
From Locator
 
Char
To 2
 
Char
From Item
 
Char
To 3
 
Char