View: PMIFV_PERIODINVENTORY_V

Product:	PMI - Process Manufacturing Intelligence
Description:	Qty & usage info. for Items in Inv. for closed periods by whse, loc, lot & itm.
Implementation/DBA Data:	APPS.PMIFV_PERIODINVENTORY_V

View Text

SELECT INV.LOCT_ONHAND
, INV.LOCT_ONHAND2
, INV.LOCT_USAGE
, INV.LOCT_USAGE2
, INV.LOCT_YIELD
, INV.LOCT_YIELD2
, INV.LOCT_VALUE
, INV.LOG_END_DATE
, INV.WHSE_CODE
, LOT.LOT_ID
, INV.LOCATION
, LOT.ITEM_ID
, INV.LOT_STATUS
, INV.QCHOLD_RES_CODE
, INV.PERIOD_ID
, INV.FISCAL_YEAR
, PERIODS.CALENDAR_DESC
, PERIODS.PERIOD_DESC
, INV.PERIOD
, 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
, PERIODS.START_DATE
, PERIODS.END_DATE
, LOT.CREATED_BY
, LOT.CREATION_DATE
, LOT.LAST_UPDATED_BY
, LOT.LAST_UPDATE_DATE
, 1
, SUBSTR(PMI_COST_ANALZ_PACK.GMCA_GET_MEANING('PERIOD_STATUS'
, 1)
, 1
, 10)
FROM IC_PERD_BAL 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
, PMI_INV_CALENDAR_V PERIODS
, 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.PERIOD_ID = PERIODS.PERIOD_ID
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')) UNION ALL SELECT LOCTINV.LOCT_ONHAND
, LOCTINV.LOCT_ONHAND2
, 0
, 0
, 0
, 0
, 0
, SYSDATE
, LOCTINV.WHSE_CODE
, LOT.LOT_ID
, LOCTINV.LOCATION
, LOT.ITEM_ID
, LOCTINV.LOT_STATUS
, LOCTINV.QCHOLD_RES_CODE
, PERIODS.PERIOD_ID
, PERIODS.FISCAL_YEAR
, PERIODS.CALENDAR_DESC
, PERIODS.PERIOD_DESC
, PERIODS.PERIOD
, 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
, ORGNMST.CO_CODE
, LOCATION.LOCT_DESC
, LOCATION.CAPACITY
, LOCATION.CAPACITY_UM
, LOT_STAT.STATUS_DESC
, QCHOLD_REASON. QCHOLD_RES_DESC
, PERIODS.START_DATE
, PERIODS.END_DATE
, LOT.CREATED_BY
, LOT.CREATION_DATE
, LOT.LAST_UPDATED_BY
, LOT.LAST_UPDATE_DATE
, 0
, SUBSTR(PMI_COST_ANALZ_PACK.GMCA_GET_MEANING('PERIOD_STATUS'
, 0)
, 1
, 10)
FROM IC_LOCT_INV LOCTINV
, 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 ORGNMST
, PMI_INV_CALENDAR_V PERIODS
WHERE LOT.ITEM_ID = LOCTINV.ITEM_ID(+)
AND LOT.LOT_ID = LOCTINV.LOT_ID(+)
AND LOT.ITEM_ID = ITEM.ITEM_ID
AND LOCTINV.WHSE_CODE = WAREHOUSE.WHSE_CODE(+)
AND LOCTINV.WHSE_CODE = LOCATION.WHSE_CODE(+)
AND LOCTINV.LOCATION = LOCATION.LOCATION(+)
AND LOCTINV.LOT_STATUS = LOT_STAT.LOT_STATUS(+)
AND LOCTINV.QCHOLD_RES_CODE = QCHOLD_REASON.QCHOLD_RES_CODE(+)
AND WAREHOUSE.ORGN_CODE = ORGNMST.ORGN_CODE
AND ORGNMST.CO_CODE = PERIODS.ORGN_CODE
AND TRUNC(SYSDATE) BETWEEN TRUNC(PERIODS.START_DATE)
AND TRUNC(PERIODS.END_DATE)
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

Columns