-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 获得入库单表头信息
-- =============================================
ALTER PROCEDURE [dbo].[Get_InStock]
 @InStock_Ord varchar(20),
    @Sp_Id          int=-1,
    @State          int=-1
AS
BEGIN
 DECLARE @InStkCount    int
 DECLARE @InStock_Id    decimal
 DECLARE @OutStor_id    decimal
 DECLARE @TotalAmt    money
 DECLARE @TotalPerFee   money
 DECLARE @GoldPrice    money
 SELECT @InStkCount=COUNT(InStock_Id) FROM [ExInStock] 
  WHERE (InStock_Ord=@InStock_Ord OR @InStock_Ord='') AND
              (Sp_Id=@Sp_Id OR @Sp_Id=-1) AND
              (state=@state OR @state=-1) AND
              InStockType=1
 SELECT @OutStor_id=OutStor_id,
      @TotalAmt=TotalAmt,
   @TotalPerFee=TotalPerFee,
   @GoldPrice=GoldPrice
  FROM OutStorageOrd A 
--  INNER JOIN Depart_From_Batar B ON B.Dept_Id=A.Customer_Id
  WHERE OutStor_Number=@InStock_Ord AND
--   (@Sp_Id=-1 OR B.Number=(SELECT Number FROM DepartmentInfo WHERE Dept_Id=@Sp_Id))
            (A.Customer_Id=@Sp_Id OR @Sp_Id=-1)
 IF(@InStkCount=0 AND @OutStor_id IS NOT NULL)
 BEGIN TRY
  BEGIN TRAN
  INSERT [ExInStock]
           (InStock_Ord,OutStor_id,Sp_Id,Cost,PerFee,GoldPrice,InStockType,State)
           values(@InStock_Ord,@OutStor_id,@Sp_Id,@TotalAmt,@TotalPerFee,@GoldPrice,1,1)
  SELECT @InStock_Id=@@IDENTITY
  INSERT [ExInStockInfo]
           (BarCode,InStock_Id,Gp_Id,Pm_Id,Pq_Id,AllWeight,GoldWeight,InStock_Ord,InStockType,Sp_Id)
  SELECT A.BarCode,@InStock_Id,A.Gp_Id,A.Pm_Id,d.Pq_Id,A.Weight,A.Net_Weight,b.OutStor_Number,1,@Sp_Id
  FROM GoldProduct AS A
  INNER JOIN OutStorageOrd AS B ON A.OutStor_id=B.OutStor_id
  INNER JOIN ProductModel AS c ON c.Pm_Id=A.Pm_Id
        LEFT OUTER JOIN ProductQuality AS D ON D.Pq_Id_Batar=C.Pq_Id
  WHERE B.OutStor_Number=@InStock_Ord
  
--  FROM GoldProduct AS A,OutStorageOrd AS B,ProductQuality AS C,ProductModel AS D
--  WHERE B.OutStor_Number=@InStock_Ord AND A.OutStor_id=B.OutStor_id
--     AND D.Pm_Id=A.Pm_Id AND C.Pq_Id_Batar=D.Pq_Id
--SELECT @BarCode,@InStock_Id,@Gp_Id,@Pm_Id,@AllWeight,@GoldWeight,
--     @InStock_Ord
  COMMIT TRAN   
 END TRY
 BEGIN CATCH
  IF (@@ERROR<>0)
  BEGIN
   ROLLBACK TRAN
   SELECT ERROR_MESSAGE()
  END
 END CATCH
 SELECT * FROM [ExInStock]
  WHERE (InStock_Ord=@InStock_Ord OR @InStock_Ord='') AND
     (Sp_Id=@Sp_Id OR @Sp_Id=-1) AND
     (state=@state OR @state=-1) AND
              InStockType=1
END

posted on 2010-05-19 16:20  prayforsmile  阅读(179)  评论()    收藏  举报