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 |