Product: | PMI - Process Manufacturing Intelligence (Obsolete) |
---|---|
Description: | Qty and availability info. for Items in Inv. at the present time by whse, loc, lot and item. |
Implementation/DBA Data: | APPS.PMIFV_CURRENTINVENTORY_V |
SELECT INV.LOCT_ONHAND
, INV.LOCT_ONHAND2
, INV.WHSE_CODE
, LOT.LOT_ID
, INV.LOCATION
, LOT.ITEM_ID
, INV.LOT_STATUS
, INV.QCHOLD_RES_CODE
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, LOT.QC_GRADE
, LOT.EXPIRE_DATE
, LOT.CREATION_DATE
, LOT.VENDOR_LOT_NO
, WAREHOUSE.WHSE_NAME
, WAREHOUSE.ORGN_CODE
, SY.CO_CODE
, LOCATION.LOCT_DESC
, LOCATION.CAPACITY
, LOCATION.CAPACITY_UM
, LOT_STAT.STATUS_DESC
, QCHOLD_REASON.QCHOLD_RES_DESC
, INV.LAST_UPDATED_BY
, INV.CREATED_BY
, INV.LAST_UPDATE_DATE
, INV.CREATION_DATE
FROM IC_LOCT_INV INV
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
, IC_WHSE_MST WAREHOUSE
, IC_LOCT_MST LOCATION
, IC_LOTS_STS LOT_STAT
, GMD_HOLD_REASONS_VL QCHOLD_REASON
, SY_ORGN_MST SY
WHERE INV.ITEM_ID(+) = LOT.ITEM_ID
AND INV.LOT_ID (+) = LOT.LOT_ID
AND LOT.ITEM_ID = ITEM.ITEM_ID
AND INV.WHSE_CODE = WAREHOUSE.WHSE_CODE(+)
AND WAREHOUSE.ORGN_CODE = SY.ORGN_CODE
AND INV.WHSE_CODE = LOCATION.WHSE_CODE(+)
AND INV.LOCATION = LOCATION.LOCATION(+)
AND INV.LOT_STATUS = LOT_STAT.LOT_STATUS (+)
AND INV.QCHOLD_RES_CODE = QCHOLD_REASON.QCHOLD_RES_CODE(+)
AND (INV.DELETE_MARK = 0 OR INV.DELETE_MARK IS NULL)
AND (WAREHOUSE.ORGN_CODE IS NULL OR WAREHOUSE.ORGN_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) WITH READ ONLY