sp_orderlogreport.sql

(Java+SQL)-大型企业JAVA的ERP系统

CREATE PROCEDURE sp_OrderLogReport -- 定货记录表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@beginDate datetime  -- 开始时间
	,@endDate datetime    -- 结束时间

AS DECLARE 
	 @error_var int 
	,@rowcount_var int
        ,@purchId varchar(10)      -- 采购申请编号	
        ,@needDate datetime	   -- 用款日期
        ,@deptId varchar(10)       -- 用款部门编号
	,@deptName varchar(30)     -- 用款部门
        ,@customerId varchar(10)   -- 收款单位编号                
	,@customerName varchar(30) -- 收款单位
	,@useFor varchar(30)       -- 资金用途
        ,@productId varchar(10)    -- 产品编码
	,@productName  varchar(30) -- 产品名称
        ,@prepayQty float          -- 预定数量	
        ,@unitPrice float          -- 单价
        ,@prepayAmt float          -- 预付金额
	,@useWay int               -- 付款方式
        ,@bank varchar(30)         -- 开户银行                        		        
        ,@bankAccount varchar(20)  -- 银行帐号                        		        
        ,@appDate datetime         -- 申请日期                        		                
        ,@comment varchar(50)       -- 备注
        ,@countNum int             -- 记录数
                                
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 


SELECT @prepayQty = 0;
SELECT @unitPrice = 0;
SELECT @prepayAmt = 0;


-- 创建临时表
CREATE TABLE #Tmp_OrderLogReport 
(        purchId varchar(10)      -- 采购申请编号	
        ,needDate datetime	   -- 用款日期
	,deptName varchar(30)     -- 用款部门
        ,customerId varchar(10)   -- 收款单位编号                	
	,customerName varchar(30) -- 收款单位
	,useFor varchar(30)       -- 资金用途
	,productName  varchar(30) -- 产品名称
        ,prepayQty float          -- 预定数量	
        ,unitPrice float          -- 单价
        ,prepayAmt float          -- 预付金额
	,useWay int               -- 付款方式
        ,bank varchar(30)         -- 开户银行                        		        
        ,bankAccount varchar(20)  -- 银行帐号    
        ,appDate datetime         -- 申请日期                        		                                            		        
        ,comment varchar(50)       -- 备注
        ,beginDate datetime 
	,endDate datetime
        ,countNum int
)   

DECLARE purch_cur CURSOR FOR 
SELECT purchId,needDate,deptId,customerId,useFor,useWay,appDate,comment FROM Purch
 WHERE appDate >= @beginDate
   AND appDate < @endDate
   
OPEN purch_cur

FETCH NEXT FROM purch_cur
INTO @purchId,@needDate,@deptId,@customerId,@useFor,@useWay,@appDate,@comment


WHILE @@FETCH_STATUS = 0
BEGIN
		  -- 用款部门
		    SELECT @deptName = deptName
		      FROM Department
		     WHERE deptId = @deptId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 收款单位
		    SELECT @customerName = customerName
		      FROM Customer
		     WHERE customerId = @customerId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

                  -- 产品编号
		    SELECT @productId = productId
		      FROM PurchProduct
		     WHERE purchId = @purchId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   


                  -- 产品名称
		    SELECT @productName = productName
		      FROM Production
		     WHERE productId = @productId	
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 预定数量
		    SELECT @prepayQty = quantity
		      FROM PurchProduct
		     WHERE purchId = @purchId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 单价
		    SELECT @unitPrice = unitPrice
		      FROM PurchProduct
		     WHERE purchId = @purchId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    
	            
	            INSERT INTO #Tmp_OrderLogReport(purchId,needDate,deptName,customerId,customerName,useFor,productName,prepayQty,unitPrice,prepayAmt,useWay,bank,bankAccount,appDate,comment,beginDate,endDate)	          
	                                   VALUES(@purchId,@needDate,@deptName,@customerId,@customerName,@useFor,@productName,@prepayQty,@unitPrice,@prepayAmt,@useWay,@bank,@bankAccount,@appDate,@comment,@beginDate,@endDate)	            

	FETCH NEXT FROM purch_cur
        INTO @purchId,@needDate,@deptId,@customerId,@useFor,@useWay,@comment
END

CLOSE purch_cur

DEALLOCATE purch_cur

SELECT @countNum  = count(*)
FROM #Tmp_OrderLogReport

UPDATE #Tmp_OrderLogReport
   SET countNum = @countNum

SELECT purchId,needDate,deptName,customerId,customerName,useFor,productName,prepayQty,
       unitPrice,prepayAmt,useWay,bank,bankAccount,comment,beginDate,endDate,countNum
  FROM #Tmp_OrderLogReport ORDER BY appDate
RETURN 0


















GO