sp_arrivedlogreport.sql

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

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

AS DECLARE 
	 @error_var int 
	,@rowcount_var int
        ,@productId varchar(10)    -- 产品编码
        ,@receiptId varchar(10)	   -- 凭证编码
        ,@contractId varchar(10)   -- 合同编号        
        ,@customerId varchar(10)   -- 客户编号                
	,@contractDate datetime    -- 定货日
	,@inventoryDate datetime   -- 到货日
	,@deptName varchar(30)     -- 定货部门
	,@customerName varchar(30) -- 销货单位
	,@productName  varchar(30) -- 品种	
        ,@contractProductQty float -- 预定数量		
        ,@inventoryQty float       -- 实到数量	
        ,@unitPrice float          -- 单价
        ,@amt float                -- 金额
        ,@prepayAmt float          -- 预付金额
        ,@spareAmt  float          -- 余额
        ,@remark varchar(80)       -- 备注
        ,@countNum int             -- 记录数
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 


SELECT @contractProductQty = 0;
SELECT @inventoryQty = 0;
SELECT @unitPrice = 0;
SELECT @prepayAmt = 0;
SELECT @spareAmt = 0;


-- 创建临时表
CREATE TABLE #Tmp_ArrivedLogReport 
( 	contractDate datetime,    -- 定货日
	inventoryDate datetime,   -- 到货日
	deptName varchar(30),     -- 定货部门
	customerName varchar(30), -- 销货单位
	productId  varchar(10),   -- 产品编码		
	productName  varchar(30), -- 品种	
        contractProductQty float, -- 预定数量		
        inventoryQty float,       -- 实到数量	
        unitPrice float,          -- 单价
        amt float,                -- 金额
        prepayAmt float,          -- 预付金额
        spareAmt  float,          -- 余额
        remark varchar(80),       -- 备注
        beginDate datetime, 
	endDate datetime,
        countNum int
)   

DECLARE inventory_cur CURSOR FOR 
SELECT productId,receiptId,inventoryDate,quantity FROM Inventory
 WHERE status=1
   AND inventoryDate >= @beginDate
   AND inventoryDate < @endDate
   
OPEN inventory_cur

FETCH NEXT FROM inventory_cur
INTO @productId,@receiptId,@inventoryDate,@inventoryQty


WHILE @@FETCH_STATUS = 0
BEGIN


		  -- 合同编号
		    SELECT @contractId = Purch.contractId
		      FROM Purch,Inventory
		     WHERE Purch.purchId = Inventory.receiptId  
		       AND Inventory.receiptId = @receiptId 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 定货日
		    SELECT @contractDate = Contract.contractDate 
		      FROM Contract
		     WHERE Contract.contractId = @contractId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

                  -- 定货部门
		    SELECT @customerName = Customer.customerName
		      FROM Customer,Contract
		     WHERE Customer.customerId = Contract.customerId	
		       AND Contract.contractId = @contractId	 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 销货部门		 
		    SELECT @deptName = Department.deptName
		      FROM Department,Employee,Contract
		     WHERE Department.deptId = Employee.deptId
		       AND Employee.employeeId = Contract.proposer
		       AND Contract.contractId = @contractId	 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   
		
		  -- 品种
		    SELECT @productName = Production.productName
		      FROM Production
		     WHERE Production.productId = @productId 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

		  -- 预定数量
                    SELECT @contractProductQty = quantity 
                      FROM ContractProduct
                     WHERE contractId = @contractId
                       AND productId = @productId 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

		  -- 单价
                    SELECT @unitPrice = unitPrice 
                      FROM ContractProduct
                     WHERE contractId = @contractId
                       AND productId = @productId 
                    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   
                    
		  -- 金额
                    SELECT @amt = @contractProductQty*@unitPrice 
                    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END                       


	            
	            INSERT INTO #Tmp_ArrivedLogReport(contractDate,inventoryDate,deptName,customerName,productId,productName,contractProductQty,inventoryQty,unitPrice,amt,prepayAmt,spareAmt,remark,beginDate,endDate,countNum)	          
	                                   VALUES(@contractDate,@inventoryDate,@deptName,@customerName,@productId,@productName,@contractProductQty,@inventoryQty,@unitPrice,@amt,@prepayAmt,@spareAmt,@remark,@beginDate,@endDate,@countNum)	            

	FETCH NEXT FROM inventory_cur
        INTO @productId,@receiptId,@inventoryDate,@inventoryQty
END

CLOSE inventory_cur

DEALLOCATE inventory_cur

SELECT @countNum  = count(*)
FROM #Tmp_ArrivedLogReport

UPDATE #Tmp_ArrivedLogReport
   SET countNum = @countNum

SELECT contractDate,inventoryDate,deptName,customerName
     ,productId,productName,contractProductQty,inventoryQty
     ,unitPrice,amt,prepayAmt,spareAmt,remark,beginDate,endDate,countNum
  FROM #Tmp_ArrivedLogReport ORDER BY inventoryDate
RETURN 0


















GO