INV Physical Inventory Tags

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