INV Locator quantities

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Locator quantities report (XML)
Short Name: INVIRILC_XML
DB package: INV_INVIRILC_XMLP_PKG
SELECT 
&P_LOC_FLEXDATA                LOC_FLEXDATA,
MIL.INVENTORY_LOCATION_ID     MIL_LOC_ID, 
MIL.DESCRIPTION               MIL_DESC, 
MIL.SUBINVENTORY_CODE         MIL_SUBINV_CD, 
MIL.DISABLE_DATE              MIL_DISABLE_DT, 
MIL.LOCATION_WEIGHT_UOM_CODE  MIL_WEIGHT_UOM, 
UMT1.DESCRIPTION              MIL_WT_UOM_DESC,
MIL.VOLUME_UOM_CODE           MIL_VOL_UOM, 
UMT2.DESCRIPTION              MIL_VOL_UOM_DESC,
ROUND(NVL(MIL.MAX_CUBIC_AREA ,0), :P_QTY_PRECISION)         MIL_MAX_CUBIC_AREA,
ROUND(NVL(MIL.MAX_WEIGHT,0), :P_QTY_PRECISION)             MIL_MAX_WEIGHT, 
ROUND(NVL(MIL.LOCATION_MAXIMUM_UNITS,0), :P_QTY_PRECISION)     MIL_MAX_UNITS,
MOQD.INVENTORY_ITEM_ID         MOH_ITEM_ID, 
MOQD.REVISION                  MOH_ITEM_REV,
&P_ITEM_FLEXDATA            ITEM_FLEXDATA,
MSI.DESCRIPTION               ITEM_DESC,
MSI.PRIMARY_UOM_CODE          MSI_UOM_CODE, 
ROUND(NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY,0), :P_QTY_PRECISION)    MOH_QTY, 
	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_INVIRILC_XMLP_PKG.loc_pct_max_wt(ROUND ( NVL ( MIL.MAX_WEIGHT , 0 ) , :P_QTY_PRECISION ), :C_GROSS_WT) C_MAX_WT_PCT, 
	INV_INVIRILC_XMLP_PKG.loc_pct_max_vol(ROUND ( NVL ( MIL.MAX_CUBIC_AREA , 0 ) , :P_QTY_PRECISION ), :C_GROSS_VOL) C_MAX_VOL_PCT, 
	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, 
	INV_INVIRILC_XMLP_PKG.dsply_wt_cnv_rate(INV_INVIRILC_XMLP_PKG.weight_conv_rate(MOQD.INVENTORY_ITEM_ID, MSI.PRIMARY_UOM_CODE, MIL.LOCATION_WEIGHT_UOM_CODE)) C_WT_CNV_RT_DSPLY, 
	INV_INVIRILC_XMLP_PKG.item_rev_wt_calc(:C_WEIGHT_CONV_RATE, :C_ITEM_REV_QTY) C_ITEM_REV_WEIGHT, 
	INV_INVIRILC_XMLP_PKG.dsply_vol_cnv_rate(INV_INVIRILC_XMLP_PKG.vol_conv_rate(MOQD.INVENTORY_ITEM_ID, MSI.PRIMARY_UOM_CODE, MIL.VOLUME_UOM_CODE) ) C_VOL_CVN_RT_DSPLY, 
	INV_INVIRILC_XMLP_PKG.item_rev_vol_calc(:C_VOL_CONV_RATE, :C_ITEM_REV_QTY) C_ITEM_REV_VOL, 
	INV_INVIRILC_XMLP_PKG.vol_conv_rate(MOQD.INVENTORY_ITEM_ID, MSI.PRIMARY_UOM_CODE, MIL.VOLUME_UOM_CODE) C_VOL_CONV_RATE, 
	INV_INVIRILC_XMLP_PKG.weight_conv_rate(MOQD.INVENTORY_ITEM_ID, MSI.PRIMARY_UOM_CODE, MIL.LOCATION_WEIGHT_UOM_CODE) C_WEIGHT_CONV_RATE
FROM 
MTL_ITEM_LOCATIONS            MIL,
MTL_UNITS_OF_MEASURE          UMT1, 
MTL_UNITS_OF_MEASURE          UMT2,
MTL_UNITS_OF_MEASURE          UMF1,
MTL_SYSTEM_ITEMS_VL	MSI,
MTL_ONHAND_QUANTITIES_DETAIL         MOQD
WHERE 
&P_LOC_WHERE 
AND MIL.LOCATION_WEIGHT_UOM_CODE =  UMT1.UOM_CODE (+)  
AND  MIL.VOLUME_UOM_CODE =   UMT2.UOM_CODE (+) 
AND MSI.PRIMARY_UOM_CODE =  UMF1.UOM_CODE (+) 
AND MOQD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID 
AND  MOQD.ORGANIZATION_ID    = MSI.ORGANIZATION_ID 
AND MIL.INVENTORY_LOCATION_ID = MOQD.LOCATOR_ID(+)
AND  MIL.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+)
AND MIL.SUBINVENTORY_CODE = MOQD.SUBINVENTORY_CODE(+)
AND  MIL.ORGANIZATION_ID = :P_ORG 
 order by 
 &P_LOC_ORDER_BY, 
 MIL.SUBINVENTORY_CODE, 
 &P_ITEM_ORDER_BY, 
 MOQD.REVISION
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
To
 
Locators From