USE [Veldheres]
GO
/****** Object:  View [dbo].[VI_IM_ITEM_WITH_INV]    Script Date: 12/6/2019 3:08:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* VI_IM_ITEM_WITH_INV */
CREATE view [dbo].[VI_IM_ITEM_WITH_INV] as
select
  IM_ITEM.*,
  -- Cannot do IM_INV.* because some field names are then repeated (e.g.,
  -- ITEM_NO). So we list each field individually.
  IM_INV.LOC_ID,
  IM_INV.BIN_1,
  IM_INV.BIN_2,
  IM_INV.BIN_3,
  IM_INV.BIN_4,
  IM_INV.MIN_QTY,
  IM_INV.MAX_QTY,
  IM_INV.QTY_COMMIT,
  IM_INV.QTY_ON_HND,
  IM_INV.QTY_ON_PO,
  IM_INV.QTY_ON_BO,
  IM_INV.QTY_ON_XFER_OUT,
  IM_INV.QTY_ON_XFER_IN,
  IM_INV.QTY_AVAIL,
  IM_INV.NET_QTY,
  IM_INV.LST_AVG_COST,
  IM_INV.LST_COST as INV_LST_COST,          /* <== Special case since we now have IM_ITEM.LST_COST */
  IM_INV.STD_COST,
  IM_INV.COST_OF_SLS_PCT,
  IM_INV.FST_RECV_DAT,
  IM_INV.LST_RECV_DAT as INV_LST_RECV_DAT,  /* <== Special case since we now have IM_ITEM.LST_RECV_DAT */
  IM_INV.FST_SAL_DAT,
  IM_INV.LST_SAL_DAT,
  IM_INV.LST_CNT_DAT,
  IM_INV.LST_ORD_DAT,
  IM_INV.NXT_EXPECTD_DAT,
  IM_INV.NXT_EXPECTD_PO,
  IM_INV.GL_VAL,
  IM_INV.AVG_COST,
  IM_INV.QTY_ON_ORD,
  IM_INV.QTY_ON_LWY,
  IM_INV.QTY_ON_SO,
  IM_INV.DROPSHIP_QTY_ON_CUST_ORD,
  IM_INV.DROPSHIP_QTY_ON_PO,
  IM_INV.ANNL_HLD_COST,
  IM_INV.ANNL_HLD_COST_PERC,
  IM_INV.EOQ,
  IM_INV.EOQ_CALC_ON,
  IM_INV.ORD_COST_PER_ORDER,
  IM_INV.REORD_PNT,
  IM_INV.SFTY_STK,
  IM_INV.SRVC_LEVL,
  IM_INV.STK_STAT_PER_NET_QTY,
  IM_INV.STK_STAT_PER_QTY_AVAIL,
  IM_INV.STK_STAT_PER_QTY_ON_HND
from IM_ITEM
join IM_INV
on IM_ITEM.ITEM_NO = IM_INV.ITEM_NO
GO