comyearp.sql

这是用PB6开发的一个POS管理系统

AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on  
  begin tran
  insert alloc(AllocNo,InputDate,AllocDate,AuditDate,ReceiptNo,Operator,Employee,DestDept,
               SrcDept,InputDept,Cost,NTaxAmt,Amt,AuditFlag,AuditOperator,BusinessFlag,
               ReceiptFlag,Remark)
        select AllocNo,InputDate,AllocDate,AuditDate,ReceiptNo,Operator,Employee,DestDept,
               SrcDept,InputDept,Cost,NTaxAmt,Amt,AuditFlag,AuditOperator,BusinessFlag,
               ReceiptFlag,Remark 
       from com_alloc where allocno not in (select allocno from alloc)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin 
    rollback
    exec up_rcverror @err,'alloc','insert'
    return @err
  end
  commit
  exec up_rcvlog 'alloc',0,0,@inserts
  truncate table com_alloc
  return @@error
go
print 'up_rcvalloc created!'
go

/* up_rcvallocdetail */
if (select count(*) from sysobjects where name='up_rcvallocdetail')>0
   drop proc up_rcvallocdetail
go
CREATE PROC up_rcvallocdetail
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  declare @AllocNo TReceipt
  declare @GoodsNo TGoods
  declare @Qty TQty
  declare @AuditQty TQty
  declare @CostPrice TPrice
  declare @Price TPrice
  declare @NTaxPrice TPrice
  declare @RetailPrice TPrice
  declare @Amt TAmt
  declare @NTaxAmt TAmt
  declare @ValidDate datetime
  declare @VendorNo TVendor
  declare @OrgAllocNo TReceipt
  declare @BatchNo TBatch
  declare @reqno TReceipt

  set nocount on
  select @inserts=0
  declare c1 cursor for select AllocNo,GoodsNo,Qty,AuditQty,CostPrice,Price,NTaxPrice,RetailPrice,Amt,
     NTaxAmt,ValidDate,VendorNo,OrgAllocNo,BatchNo,ReqNo from com_allocdetail
 
  open c1
  select @err=@@error
  if @err<>0
  begin
     deallocate c1
     exec up_rcverror @err,'allocdetail','open'
     return @err
  end
  begin tran
  while 1 = 1
  begin
    fetch c1 into @AllocNo,@GoodsNo,@Qty,@AuditQty,@CostPrice,@Price,@NTaxPrice,@RetailPrice,@Amt,
     @NTaxAmt,@ValidDate,@VendorNo,@OrgAllocNo,@BatchNo,@ReqNo
    select @RetailPrice=(select isnull(retailprice,0) from goods where goodsno=@GoodsNo)
    if @@fetch_status <> 0
       break
      if (select count(*) from allocdetail where allocno=@allocno and goodsno=@goodsno)=0
      begin
        insert allocdetail(AllocNo,GoodsNo,Qty,AuditQty,CostPrice,Price,NTaxPrice,RetailPrice,Amt,
                           NTaxAmt,ValidDate,VendorNo,OrgAllocNo,BatchNo,ReqNo)
        values(@AllocNo,@GoodsNo,@Qty,@AuditQty,@CostPrice,@Price,@NTaxPrice,@RetailPrice,@Amt,
               @NTaxAmt,@ValidDate,@VendorNo,@OrgAllocNo,@BatchNo,@ReqNo)
        select @err=@@error
        if @err <>0
        begin
          rollback
          close c1
          deallocate c1
          exec up_rcverror @err,'allocdetail','insert'
          return @err
        end
        select @inserts=@inserts + 1
      end
  end
  commit
  close c1
  deallocate c1
  exec up_rcvlog 'allocdetail',0,0,@inserts
  truncate table com_allocdetail
  return @@error
go
print 'up_rcvallocdetail created!'
go

/* up_rcvalloc_all */
if (select count(*) from sysobjects where name='up_rcvalloc_all')>0

   drop proc up_rcvalloc_all
go
CREATE PROC up_rcvalloc_all
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @inserts1 integer
  declare @err integer
  declare @AllocNo TReceipt
  declare @GoodsNo TGoods
  declare @Qty TQty
  declare @AuditQty TQty
  declare @CostPrice TPrice
  declare @Price TPrice
  declare @NTaxPrice TPrice
  declare @RetailPrice TPrice
  declare @Amt TAmt
  declare @NTaxAmt TAmt
  declare @ValidDate datetime
  declare @VendorNo TVendor
  declare @OrgAllocNo TReceipt
  declare @BatchNo TBatch
  declare @reqno TReceipt
  declare @InputDate datetime
  declare @AllocDate datetime
  declare @AuditDate datetime
  declare @ReceiptNo TReceipt
  declare @Operator TStaff
  declare @Employee TStaff
  declare @DestDept TDept
  declare @SrcDept TDept
  declare @InputDept TDept
  declare @Cost TPrice
  declare @TNTaxAmt TAmt
  declare @TAmt TAmt
  declare @AuditFlag Char
  declare @AuditOperator TStaff
  declare @BusinessFlag Char
  declare @ReceiptFlag char
  declare @Remark varchar(255)

  set nocount on
  select @inserts=0,@inserts1=0
  declare c1 cursor for select AllocNo,GoodsNo,Qty,AuditQty,CostPrice,Price,NTaxPrice,RetailPrice,Amt,
     NTaxAmt,ValidDate,VendorNo,OrgAllocNo,BatchNo,ReqNo,
     InputDate,AllocDate,AuditDate,ReceiptNo,Operator,Employee,DestDept,
     SrcDept,InputDept,Cost,TNTaxAmt,TAmt,AuditFlag,AuditOperator,BusinessFlag,
     ReceiptFlag,Remark from com_alloc_all
  open c1
  select @err=@@error
  if @err<>0
  begin
     deallocate c1
     exec up_rcverror @err,'allocdetail','open'
     return @err
  end
  begin tran
  while 1 = 1
  begin
    fetch c1 into @AllocNo,@GoodsNo,@Qty,@AuditQty,@CostPrice,@Price,@NTaxPrice,@RetailPrice,@Amt,
     @NTaxAmt,@ValidDate,@VendorNo,@OrgAllocNo,@BatchNo,@ReqNo,
     @InputDate,@AllocDate,@AuditDate,@ReceiptNo,@Operator,@Employee,@DestDept,
     @SrcDept,@InputDept,@Cost,@TNTaxAmt,@TAmt,@AuditFlag,@AuditOperator,@BusinessFlag,
     @ReceiptFlag,@Remark
    select @RetailPrice=(select isnull(retailprice,0) from goods where goodsno=@GoodsNo)
    if @@fetch_status <> 0
       break
    if (select count(*) from alloc where allocno=@allocno)=0
    begin
      insert alloc(AllocNo,InputDate,AllocDate,AuditDate,ReceiptNo,Operator,Employee,DestDept,
               SrcDept,InputDept,Cost,NTaxAmt,Amt,AuditFlag,AuditOperator,BusinessFlag,
               ReceiptFlag,Remark)
      values(@AllocNo,@InputDate,@AllocDate,@AuditDate,@ReceiptNo,@Operator,@Employee,@DestDept,
             @SrcDept,@InputDept,@Cost,@TNTaxAmt,@TAmt,@AuditFlag,@AuditOperator,@BusinessFlag,
             @ReceiptFlag,@Remark )
      select @err=@@error
      if @err <>0
      begin
        rollback
        close c1
        deallocate c1
        exec up_rcverror @err,'alloc_all','insert'
        return @err
      end
      select @inserts1=@inserts1 + 1
    end
    if (select count(*) from allocdetail where allocno=@allocno and goodsno=@goodsno)=0
    begin
      insert allocdetail(AllocNo,GoodsNo,Qty,AuditQty,CostPrice,Price,NTaxPrice,RetailPrice,Amt,
                         NTaxAmt,ValidDate,VendorNo,OrgAllocNo,BatchNo,ReqNo)
      values(@AllocNo,@GoodsNo,@Qty,@AuditQty,@CostPrice,@Price,@NTaxPrice,@RetailPrice,@Amt,
             @NTaxAmt,@ValidDate,@VendorNo,@OrgAllocNo,@BatchNo,@ReqNo)
      select @err=@@error
      if @err <>0
      begin
        rollback
        close c1
        deallocate c1
        exec up_rcverror @err,'alloc_all','insert'
        return @err
      end
      select @inserts=@inserts + 1
    end
  end
  commit
  close c1
  deallocate c1
  exec up_rcvlog 'allocdetail',0,0,@inserts
  exec up_rcvlog 'alloc',0,0,@inserts1
  truncate table com_alloc_all
  return @@error
go
print 'up_rcvalloc_all created!'
go

/* up_rcvadjust */

if (select count(*) from sysobjects where name='up_rcvadjust')>0
   drop proc up_rcvadjust
go
CREATE PROC up_rcvadjust
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on
  begin tran
  insert adjust(AdjustNo,InputDate,AuditDate,InputDept,Operator,ReceiptNo,Employee,AuditFlag,
                AuditOperator,BusinessFlag,Remark)
         select AdjustNo,InputDate,AuditDate,InputDept,Operator,ReceiptNo,Employee,AuditFlag,
                AuditOperator,BusinessFlag,Remark
         from com_adjust where adjustno not in (select adjustno from adjust)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
     rollback
     exec up_rcverror @err,'adjust','insert'
     return @err
  end
  commit
  exec up_rcvlog 'adjust',0,0,@inserts
  truncate table com_adjust
  return @@error
go
print 'up_rcvadjust created!'
go

/* up_rcvadjustdetail */
if (select count(*) from sysobjects where name='up_rcvadjustdetail')>0
   drop proc up_rcvadjustdetail
go
CREATE PROC up_rcvadjustdetail
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  declare @AdjustNo TReceipt
  declare @GoodsNo TGoods
  declare @DeptNo TDept
  declare @StartDate TDate
  declare @EndDate TDate
  declare @OretailPrice TPrice
  declare @NretailPrice TPrice
  declare @OmemberPrice TPrice
  declare @NmemberPrice TPrice
  declare @OspecialFlag char
  declare @NSpecialFlag char
  declare @AllDepart char
  declare @id integer
  set nocount on

  declare c1 cursor for select AdjustNo,GoodsNo,DeptNo,StartDate,EndDate,OretailPrice,
       NretailPrice,Omemberprice,Nmemberprice,OspecialFlag,NspecialFlag,AllDepart,id 
                from com_adjustdetail

  select @inserts=0
  open c1
  select @err=@@error
  if @err<>0
  begin
     exec up_rcverror @err,'adjustdetail','open'
     deallocate c1
     return @err
  end
  begin tran
  while 1 = 1
  begin
    fetch c1 into @AdjustNo,@GoodsNo,@DeptNo,@StartDate,@EndDate,@OretailPrice,@NretailPrice,
        @OmemberPrice,@NmemberPrice,@OspecialFlag,@NSpecialFlag,@AllDepart,@id
    if @@fetch_status <> 0
       break
    if (select count(*) from adjustdetail where adjustno=@adjustno and goodsno=@goodsno)=0
    begin
      insert adjustdetail(AdjustNo,GoodsNo,DeptNo,StartDate,EndDate,OretailPrice,NretailPrice,
          OmemberPrice,NmemberPrice,OspecialFlag,NSpecialFlag,AllDepart)
      values(@AdjustNo,@GoodsNo,@DeptNo,@StartDate,@EndDate,@OretailPrice,@NretailPrice,
          @OmemberPrice,@NmemberPrice,@OspecialFlag,@NSpecialFlag,@AllDepart)
      select @err=@@error
      if @err <>0
      begin  
        rollback tran
        exec up_rcverror @err,'adjustdetail','insert'
        close c1
        deallocate c1
        return @err
      end
      select @inserts=@inserts+1
    end
  end  
  commit tran
  exec up_rcvlog 'adjustdetail',0,0,@inserts
  close c1
  deallocate c1
  truncate table com_adjustdetail
  return @@error
go
print 'up_rcvadjustdetail created!'
go

/* up_rcvimport */

if (select count(*) from sysobjects where name='up_rcvimport')>0
   drop proc up_rcvimport
go
CREATE PROC up_rcvimport
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on  
  begin tran
  insert import(ImportNo,OrderNo,InputDate,AuditDate,DeptNO,InputDept,Employee,VendorNo,
                InvoiceNo,InvoiceDate,NTaxAmt,Amt,PayMode,Operator,AuditFlag,AuditOperator,
                BusinessFlag,ReceiptFlag,Remark) 
         select ImportNo,OrderNo,InputDate,AuditDate,DeptNO,InputDept,Employee,VendorNo,
                InvoiceNo,InvoiceDate,NTaxAmt,Amt,PayMode,Operator,AuditFlag,AuditOperator,
                BusinessFlag,ReceiptFlag,Remark
         from com_import where importno not in (select importno from import)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'import','insert'
    return @err
  end
  commit
  exec up_rcvlog 'import',0,0,@inserts
  truncate table com_import
  return @@error
go
print 'up_rcvimport created!'
go

/* up_rcvimportdetail */
if (select count(*) from sysobjects where name='up_rcvimportdetail')>0
   drop proc up_rcvimportdetail
go
CREATE PROC up_rcvimportdetail
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  declare @ImportNo TReceipt
  declare @GoodsNo  TGoods
  declare @Qty TQty
  declare @AuditQty TQty
  declare @NTaxPrice TPrice
  declare @TaxPrice TPrice
  declare @RetailPrice TPrice
  declare @Amt TAMT
  declare @NTaxAmt TAmt
  declare @ValidDate TDate
  declare @OrgImportNo TReceipt
  declare @BatchNo TBatch
  declare @UD1 TUDF
  declare @UD2 TUDF  
  declare @ID integer
  set nocount on

  select @inserts=0
  declare c1 cursor for select ImportNo,GoodsNo,Qty,AuditQty,NTaxPrice,TaxPrice,RetailPrice,
             Amt,NTaxAmt,ValidDate,OrgImportNo,BatchNo,Ud1,Ud2,id from com_importdetail 
  open c1
  select @err=@@error
  if @err<>0
  begin
     exec up_rcverror @err,'importdetail','open'
     deallocate c1
     return @err
  end
  begin tran
  while 1 = 1
  begin
    fetch c1 into @ImportNo,@GoodsNo,@Qty,@AuditQty,@NTaxPrice,@TaxPrice,@RetailPrice,
             @Amt,@NTaxAmt,@ValidDate,@OrgImportNo,@BatchNo,@Ud1,@Ud2,@id
    if @@fetch_status <> 0
       break
    if (select count(*) from importdetail where importno=@importno and goodsno=@goodsno)=0
    begin
        insert importdetail(ImportNo,GoodsNo,Qty,AuditQty,NTaxPrice,TaxPrice,RetailPrice,
             Amt,NTaxAmt,ValidDate,OrgImportNo,BatchNo,Ud1,Ud2)
        values(@ImportNo,@GoodsNo,@Qty,@AuditQty,@NTaxPrice,@TaxPrice,@RetailPrice,
             @Amt,@NTaxAmt,@ValidDate,@OrgImportNo,@BatchNo,@Ud1,@Ud2)
        select @err=@@error
        if @err <>0
        begin
          rollback tran
          exec up_rcverror @err,'importdetail','insert'
          close c1
          deallocate c1
          return @err
        end
        select @inserts=@inserts+1
    end
  end
  commit tran
  exec up_rcvlog 'importdetail',0,0,@inserts
  close c1
  deallocate c1
  truncate table com_importdetail
  return @@error
go
print 'up_rcvimportdetail created!'
go

/* up_rcvsale */

if (select count(*) from sysobjects where name='up_rcvsale')>0
   drop proc up_rcvsale
go
CREATE PROC up_rcvsale
AS
  declare @deletes integer
  declare @updates integer
  declare @inserts integer
  declare @err integer
  set nocount on  
  begin tran
  insert sale(SaleNo,DeptNo,InputDate,AuditDate,CustNo,ReceiptNo,Employee,Operator,Cost,
              NTaxAmt,Amt,PayMode,AuditFlag,AuditOperator,BusinessFlag,Remark) 
       select SaleNo,DeptNo,InputDate,AuditDate,CustNo,ReceiptNo,Employee,Operator,Cost,
              NTaxAmt,Amt,PayMode,AuditFlag,AuditOperator,BusinessFlag,Remark 
       from com_sale where saleno not in (select saleno from sale)
  select @err=@@error,@inserts=@@rowcount
  if @err<>0
  begin
    rollback
    exec up_rcverror @err,'sale','insert'
    return @err
  end
  commit
  exec up_rcvlog 'sale',0,0,@inserts
  truncate table com_sale
  return @@error
go
print 'up_rcvsale created!'
go

/* up_rcvsaledetail */