IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='過期未發運物料價值_PROC' AND TYPE='P')
DROP PROC 過期未發運物料價值_PROC
GO
CREATE PROC  過期未發運物料價值_PROC
@CO_TO INT,
@CO_FR INT
AS


CREATE TABLE [#過期未發運物料價值]
 (
	[銷售單號] [int] ,
	[行] [int]  ,
	[客戶編碼] [varchar] (10) ,
	[物料編碼] [varchar] (30) ,
	[計量單位] [varchar] (10),
        [出庫日期] DATETIME,
        [到期日期] DATETIME,
        [訂購數量] [numeric](18, 6)DEFAULT(0.00) ,
	[出庫數量] [numeric](18, 6)DEFAULT(0.00) ,
        [發票單價] [numeric](18, 6) DEFAULT(0.00),
        [未發運價值] AS (訂購數量-出庫數量)*發票單價)

INSERT INTO #過期未發運物料價值 (銷售單號,行,客戶編碼,物料編碼,
計量單位,出庫日期,到期日期,訂購數量,出庫數量,發票單價)
SELECT A.銷售單號,A.行,A.客戶編碼,A.物料編碼,
A.計量單位,A.出庫日期,B.到期日期,B.訂購數量,A.出庫數量,A.發票單價
from 銷售出庫單明細表 A JOIN 銷售訂單明細表 B ON A.物料編碼=B.物料編碼
WHERE A.銷售單號>=@CO_TO AND A.銷售單號<=@CO_FR

SELECT *FROM #過期未發運物料價值