易飞供应商采购模块最新单价

最新推荐文章于 2019-06-02 09:31:38 发布

原创最新推荐文章于 2019-06-02 09:31:38 发布 · 452 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一种在易飞8.06系统中通过存储过程批量导出特定供应商的所有产品报价的方法。该过程首先创建临时表存储查询结果，然后使用游标遍历供应商的产品列表，并选择最新的报价记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

易飞8.06 没有看到一个作业的导出所有产品报价，特写出如下过程：

USE [XXXX]

GO
/****** Object: StoredProcedure [dbo].[PURPRICE]    Script Date: 02/04/2018 21:29:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PURPRICE] ( @VENDOR VARCHAR(40))
AS
BEGIN
DECLARE @PH VARCHAR(20)
DECLARE @MAXRQ VARCHAR(8)

if exists(select name from tempdb..sysobjects Where name='#TEMPPUR')
drop table #TEMPPUR
ELSE
CREATE TABLE #TEMPPUR
(
MB001 VARCHAR(40),
MB002 VARCHAR(40),
MB003 VARCHAR(4),
MB004 VARCHAR(4),
MB008 VARCHAR(8),
MB010 VARCHAR(1),
MB011 NUMERIC(17,8),
MB013 VARCHAR(1),
MB014 VARCHAR(8),
MB016 VARCHAR(8)
)
DECLARE HJRQ CURSOR FOR SELECT DISTINCT MB001 FROM PURMB WHERE MB002=@VENDOR
OPEN HJRQ
FETCH NEXT FROM HJRQ INTO @PH
WHILE @@FETCH_STATUS =0
BEGIN
   SELECT @MAXRQ=MAX(MB014) FROM PURMB WHERE MB002=@VENDOR AND MB001=@PH
   INSERT INTO #TEMPPUR select MB001,MB002,MB003,MB004,MB008,MB010,MB011,MB013,MB014 ,MB016
from PURMB WHERE MB002=@VENDOR AND MB001=@PH AND MB014=@MAXRQ
FETCH NEXT FROM HJRQ INTO @PH
END
CLOSE HJRQ
DEALLOCATE HJRQ
SELECT A.MB001,A.MB002,A.MB003,T.MB002,T.MB003,T.MB004,T.MB008,T.MB010,T.MB011,T.MB013,T.MB014 ,T.MB016 FROM #TEMPPUR T
LEFT JOIN INVMB A ON A.MB001=T.MB001
END