WMS Warehouse Space Utilization

➜ Back to Library
Description
Categories: BI Publisher
Application: Warehouse Management
Source: Warehouse Space Utilization Report (XML)
Short Name: WMSCPTYR_XML
DB package: WMS_WMSCPTYR_XMLP_PKG
Run WMS Warehouse Space Utilization and other Oracle EBS reports with Blitz Report™ on our demo environment
select  mil.organization_id organization_id,
          mil.SUBINVENTORY_CODE ,
decode(mil.physical_location_id,null,mil.inventory_location_id,mil.physical_location_id) locator_id,
          nvl(sum(decode(nvl(moqd.CONTAINERIZED_FLAG,2),2
                                  ,greatest(moqd.primary_transaction_quantity,0))),0) loose_qty,
          nvl(sum(decode(moqd.CONTAINERIZED_FLAG,1
                                   ,greatest(moqd.primary_transaction_quantity,0))),0) packed_qty,
           nvl(sum(greatest(moqd.primary_transaction_quantity,0)),0) total_qty, 
/*WMS_WMSCPTYR_XMLP_PKG.cf_calc_countersformula(:CS_WEIGHT, :CS_VOLUME, :CS_UNITS, :CS_MAX_SUB_WT, :CS_SUB_WT_PER, :CS_MAX_SUB_VOL, :CS_SUB_VOL_PER, :CS_SUB_UNITS_PER, :CS_MAX_SUB_UNITS, :CS_LOOSE_QTY, :CS_PACKED_QTY, :CS_TOTAL_QTY, :CS_NUM_LOCS) CF_CALC_COUNTERS,*/
	WMS_WMSCPTYR_XMLP_PKG.CP_UNITS_PER_p CP_UNITS_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_WEIGHT_PER_p CP_WEIGHT_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_VOLUME_PER_p CP_VOLUME_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_COUNT_p CP_COUNT,
	WMS_WMSCPTYR_XMLP_PKG.CP_DISPLAY_SUB_p CP_DISPLAY_SUB,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_LOOSE_QTY_p CP_SUB_LOOSE_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_PACKED_QTY_p CP_SUB_PACKED_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_TOTAL_QTY_p CP_SUB_TOTAL_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_WEIGHT_p CP_SUB_WEIGHT,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_VOLUME_p CP_SUB_VOLUME, 
	WMS_WMSCPTYR_XMLP_PKG.cf_loc_flex_fieldformula(decode ( mil.physical_location_id , null , mil.inventory_location_id , mil.physical_location_id ), mil.organization_id) CF_LOC_FLEX_FIELD, 
	WMS_WMSCPTYR_XMLP_PKG.cf_loc_capacityformula(mil.organization_id, decode ( mil.physical_location_id , null , mil.inventory_location_id , mil.physical_location_id ), nvl ( sum ( decode ( nvl ( moqd.CONTAINERIZED_FLAG , 2 ) , 2 , greatest ( moqd.primary_transaction_quantity , 0 ) ) ) , 0 ), nvl ( sum ( decode ( moqd.CONTAINERIZED_FLAG , 1 , greatest ( moqd.primary_transaction_quantity , 0 ) ) ) , 0 ), nvl ( sum ( greatest ( moqd.primary_transaction_quantity , 0 ) ) , 0 ), mil.SUBINVENTORY_CODE) CF_LOC_CAPACITY,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_WT_p CP_SUB_WT,
	WMS_WMSCPTYR_XMLP_PKG.CP_SUB_VOL_p CP_SUB_VOL,
	WMS_WMSCPTYR_XMLP_PKG.CP_MAX_SUB_WT_p CP_MAX_SUB_WT,
	WMS_WMSCPTYR_XMLP_PKG.CP_MAX_SUB_VOL_p CP_MAX_SUB_VOL,
	WMS_WMSCPTYR_XMLP_PKG.CP_WT_PER_p CP_WT_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_VOL_PER_p CP_VOL_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_DISPLAY_LOC_p CP_DISPLAY_LOC,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_LOOSE_QTY_p CP_LOC_LOOSE_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_PACKED_QTY_p CP_LOC_PACKED_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_UNITS_p CP_UNITS,
	WMS_WMSCPTYR_XMLP_PKG.CP_MAX_SUB_UNITS_p CP_MAX_SUB_UNITS,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_TOTAL_QTY_p CP_LOC_TOTAL_QTY,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_WT_PER_p CP_LOC_WT_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_VOL_PER_p CP_LOC_VOL_PER,
	WMS_WMSCPTYR_XMLP_PKG.CP_LOC_UNITS_PER_p CP_LOC_UNITS_PER,
	'X'  dummy
from mtl_onhand_quantities_detail moqd,
         mtl_item_locations mil,
         mtl_item_locations mill
where moqd.locator_id(+)    = mil.inventory_location_id
and mill.inventory_location_id = decode(mil.physical_location_id,null,mil.inventory_location_id,mil.physical_location_id)
and trunc(nvl(mil.disable_date,sysdate+1)) > trunc(sysdate )
and trunc(nvl(mil.start_date_active,sysdate)) < trunc(sysdate+1)
and trunc(nvl(mil.end_date_active,sysdate )) > trunc(sysdate-1)
and mil.enabled_flag = 'Y'  
&lp_where_clause
group by mil.SUBINVENTORY_CODE,
decode(mil.physical_location_id,null,mil.inventory_location_id,mil.physical_location_id),
 mil.organization_id,
 mill.picking_order
&LP_ORDER_BY
Parameter Name SQL text Validation
Organization 
 
 LOV Oracle
Detail level 
 
 LOV Oracle
Subinventory 
 
 LOV Oracle
Locator 
 
 Char
Weight Operator 
 
 LOV Oracle
Weight Fill % 
 
 Number
Output Weight UOM 
 
 LOV Oracle
Volume Operator 
 
 LOV Oracle
Volume Fill % 
 
 Number
Output Volume UOM 
 
 LOV Oracle
Units Operator 
 
 LOV Oracle
%Utilization By Units 
 
 Number
Order By 
 
 LOV Oracle
Include Non-Convertable Stock 
 
 LOV Oracle
Download
Related Blitz Reports
WIP Required Components GMD Item/Location Required Analysis CAC Where Used by Cost Type MRP Pegging RD INV Inventory Aging INV Locator quantities INV Subinventory quantities ECC Project Manufacturing, On-Hand Values ECC Project Manufacturing, On-Hand Balance ECC Project Manufacturing Genealogy & Trace, Project Locators
Blitz Report™

Blitz Report™ provides multiple benefits: