ERP采购业务（三十七）

ERP采购业务（三十七）

产品构建表的添加存储过程：

CREATE PROCEDURE [dbo].[BioPurchaseAppInfo_ADD]@PurchaseID INT OUTPUT,@Subject NVARCHAR(100),@DepartMentID INT,@AppUserId INT,@RealUserID INT,@OrderCom NVARCHAR(50),@SendComID INT,@OrderTime DATETIME,@sendType NVARCHAR(50),@WholeType NVARCHAR(30),@AcceptUserid INT,@BillUserID INT,@BillNo NVARCHAR(30),@DeleteSate BIT AS INSERT INTO [BioPurchaseAppInfo]( [Subject],[DepartMentID],[AppUserId],[RealUserID],[OrderCom],[SendComID],[OrderTime],[sendType],[WholeType], [AcceptUserid],[BillUserID],[BillNo],[DeleteSate] )VALUES( @Subject,@DepartMentID,@AppUserId,@RealUserID,@OrderCom,@SendComID,@OrderTime,@sendType,@WholeType,@AcceptUserid,@BillUserID,@BillNo,@DeleteSate ) SET @PurchaseID = @@IDENTITY

修改的存储过程：

CREATE PROCEDURE [dbo].[BioPurchasePro_Update]@PurchaseProID INT,@PurchaseID INT,@ProID INT,@ProCount INT,@ProPrice MONEY,@isInvoice BIT,@isPay BIT,@InvoicePrice MONEY AS UPDATE [BioPurchasePro] SET [PurchaseID] = @PurchaseID,[ProID] = @ProID,[ProCount] = @ProCount,[ProPrice] = @ProPrice,[isInvoice] = @isInvoice,[isPay] = @isPay,[InvoicePrice] = @InvoicePrice WHERE PurchaseProID=@PurchaseProID

添加的存储过程：

CREATE PROCEDURE [dbo].[BioPurchasePro_ADD]@PurchaseID INT,@ProID INT,@ProCount INT,@ProPrice MONEY AS INSERT INTO [BioPurchasePro]( [PurchaseID],[ProID],[ProCount],[ProPrice] )VALUES( @PurchaseID,@ProID,@ProCount,@ProPrice )

修改的存储过程：

CREATE PROCEDURE [dbo].[BioPurchasePro_Update]@PurchaseProID INT,@PurchaseID INT,@ProID INT,@ProCount INT,@ProPrice MONEY,@isInvoice BIT,@isPay BIT,@InvoicePrice MONEY AS UPDATE [BioPurchasePro] SET [PurchaseID] = @PurchaseID,[ProID] = @ProID,[ProCount] = @ProCount,[ProPrice] = @ProPrice,[isInvoice] = @isInvoice,[isPay] = @isPay,[InvoicePrice] = @InvoicePrice WHERE PurchaseProID=@PurchaseProID

产品批号的存储过程：

CREATE PROCEDURE [dbo].[BioPuchaseProBatch_ADD]@ProBatchID INT OUTPUT,@purchaseProID INT,@batchNum NVARCHAR(50),@boxNum NVARCHAR(20),@proCount INT,@realityProCount INT,@expirationDate DATETIME,@makeDate DATETIME AS INSERT INTO [BioPuchaseProBatch]( [purchaseProID],[batchNum],[boxNum],[proCount],[realityProCount],[expirationDate],[makeDate] )VALUES( @purchaseProID,@batchNum,@boxNum,@proCount,@realityProCount,@expirationDate,@makeDate ) SET @ProBatchID = @@IDENTITY

转换时间的函数：

SELECT CONVERT(NVARCHAR(20),GETDATE(),120)

创建视图：

CREATE VIEW [dbo].[View_BioPurchaseAppInfo]ASSELECT PurchaseID, Subject, DepartMentID, DepartMent=dbo.FN_GetDepartMentByID(DepartMentID), AppUserId, AppUserName=dbo.getUserNameByUserID(AppUserId), RealUserID, RealUserName=dbo.getUserNameByUserID(RealUserID), OrderCom, sendComID, CopanyName=dbo.getCustomerByID(SendComID), CONVERT(NVARCHAR(10),ArrivedTime,120) AS ArrivedTime, CONVERT(NVARCHAR(10),OrderTime,120) AS OrderTime, sendType, WholeType, AcceptUserid, BillUserID, BillUserName=dbo.getUserNameByUserID(BillUserID), BillNo, DeleteSateFROM BioPurchaseAppInfo

根据产品的编号获取产品的名称：

-- Description: 根据产品的编号获取产品的名称-- =============================================CREATE FUNCTION [dbo].[FN_getProNameByProID]( @ProID INT )RETURNS NVARCHAR(100)ASBEGIN DECLARE @ProName NVARCHAR(50) SELECT @ProName= ProName FROM BiotbProduct WHERE ProID=@ProID RETURN @ProNameEND

创建视图：

CREATE VIEW [dbo].[View_PurchaseProInfo]ASSELECT a.*,b.ProBatchID,b.batchNum,b.boxNum,b.proCount AS BatchProCount,b.realityProCount,b.stockDate,b.expirationDate,b.makeDate,b.isDeleteSate,b.isAuditing,b.isprinted, ProName=dbo.FN_getProNameByProID(a.ProID) FROM BioPurchasePro AS a INNER JOIN BioPuchaseProBatch AS bON b.purchaseProID = a.PurchaseProID

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。