SQL语句插入其他入库单的功能实现

很多时候，客户需要通过SQL语句插入其他入库单，本文就介绍如何通过SQL语句插入其他入库单的功能实现，主要涉及如下内容：

1、其他入库单的相关表单结构

2、单据头和单体的内码问题

3、单据基础资料字段关联表结构

4、即时库存表结构

以下是详细语句：

--其他入库

if (Select OBJECT_ID('tempdb..#BillData1')) IS NOT NULL Drop Table #BillData1

Select identity(int,1,1) As FAutoID,* Into #BillData1

From t_STK_InventoryBillData Where FBillNo = @BillNo and FIndex in (Select F1 From f_splitstr(@Index,'@') Where F1 <> '') and ABS(FExtPYQty)+ABS(FPYQty)+ABS(FOtherPYQty) <> 0

if EXISTS (Select 1 #BillData1)

begin

Select @StockBillNo = 

   'QTRK'+

   Case When Len(Convert(int,FNumMax)+1) = 1 then '00000'+Convert(varchar(50),Convert(int,FNumMax)+1) 

When Len(Convert(int,FNumMax)+1) = 2 then '0000'+Convert(varchar(50),Convert(int,FNumMax)+1) 

When Len(Convert(int,FNumMax)+1) = 3 then '000'+Convert(varchar(50),Convert(int,FNumMax)+1) 

When Len(Convert(int,FNumMax)+1) = 4 then '00'+Convert(varchar(50),Convert(int,FNumMax)+1) 

When Len(Convert(int,FNumMax)+1) = 5 then '0'+Convert(varchar(50),Convert(int,FNumMax)+1) 

Else Convert(varchar(50),Convert(int,FNumMax)+1)

End

  From t_BAS_BillCodes Where FCodeID = 10433 --其他入库单编号

  Insert Into Z_STK_Miscellaneous (Column1) values(1)

  Select TOP 1 @aFID = ID From Z_STK_Miscellaneous

Update t_BAS_BillCodes Set FNumMax = FNumMax + 1 Where FCodeID = 10433 --其他入库单编号加1

         Insert Into Z_STK_MiscellaneousEntry (Column1) values(1)

     Select TOP 1 @EntryID = ID From Z_STK_MiscellaneousEntry

     Set @AutoID = 1

     While @AutoID < (Select MAX(FAutoID) From #BillData1)

     begin

       Insert Into Z_STK_MiscellaneousEntry (Column1) values(1)

       Set @AutoID = @AutoID + 1

     end

Insert Into t_STK_MiscellaneousEntry (FENTRYID,FID,FSEQ,

FMATERIALID,FLOT,FLOT_TEXT,FSTOCKID,FSTOCKLOCID,FUNITID,FQTY,FJOINQTY,FPRICE,FAMOUNT,FBOMID,FAUXPROPID,FPRODUCEDATE,

FEXPIRYDATE,FMTONO,FPROJECTNO,FSTOCKSTATUSID,FBASEUNITID,FBASEQTY,FJOINBASEQTY,FSECUNITID,FSECQTY,FOWNERTYPEID,FOWNERID,

FKEEPERTYPEID,FKEEPERID,FDISMOUNTCOSTRATE,FSTOCKFLAG,FNOTE,FSNUNITID,FSNQTY,FJOINSECQTY,FEXTAUXUNITID,FEXTAUXUNITQTY,

F_PAEZ_DECIMAL)

Select @EntryID+t1.FAutoID-1 As FEntryID,@aFID As FID,t1.FAutoID As FSeq,

t2.FMaterialID,0 As FLot,'' As FLot_Text,

t1.FStockID,0 As FStockLocID,t4.FStoreUnitID As FUnitID,t1.FPYQty As FQty,0,0,0,0,0,NULL,NULL,'','',

10000 As FStockStatusID,t600.FBaseUnitID As FBaseUnitID,t1.FPYQty As FBaseQty,0 As FJoinBaseQty,--10000为库存可用状态

t4.FAuxUnitID As FSecUnitID,

t1.FExtPYQty As FSecQty,'BD_OwnerOrg' As FOwnerTypeID,

@StockOrgID As FOwnerID,  

'BD_KeeperOrg' As FKeeperTypeID,@StockOrgID As FKeeperID,NULL,1 As FStockFlag,--FStockFlag=1表示库存审核状态，反审核时会反写库存，如果为0不反写库存

'',0,0,0,t4.FAuxUnitID As FExtAuxUnitID,t1.FExtPYQty As FExtAuxUnitQty,ISNULL(t1.FOtherPYQty,0) AS F_PAEZ_DECIMAL

  From #BillData1 t1 Inner Join t_BD_Material t2 On t1.FMaterialID = t2.FMaterialID--t1.FItemNum = t2.FNumber and t2.FUseOrgID = @StockOrgID

  --Inner Join t_BD_Stock t3 On t1.FStockNum = t3.FNumber and t3.FUseOrgID = @StockOrgID

  Left Join t_BD_MaterialStock t4 On t2.FMaterialID = t4.FMaterialID

  Left Join t_BD_MaterialBase t600 On t2.FMaterialID = t600.FMaterialID

  --Left Join t_BD_Unit_L t5 On t4.FStoreUnitID = t5.FUnitID  

  --Left Join t_BD_Unit_L t500 On t4.FAuxUnitID = t500.FUnitID

  Insert Into t_STK_MiscellaneousEntry_R(FENTRYID,FID,FBASEJOINQTY,FRECEIVEQTY,FSRCOBJECTID,FSRCBILLTYPEID,FSRCBILLID,FSRCBILLNO,FSRCROWID,FSRCSEQ)

  Select @EntryID+FAutoID-1 As FEntryID,@aFID As FID,0,0,'','','','','','' From #BillData1

   ----先插入表体数据，再插入表头数据有触发器反写第三单位数量

     Delete From Z_STK_Miscellaneous

       Insert Into t_STK_Miscellaneous (FID,

FBillTypeID,

FBillNo,

FStockOrgID,

FStockDirect,    --默认GENERAL

FSupplierID,     --默认0

FACCEPTANCE,     --默认0

FDEPTID,         --部门

FCURRENCYID,     --默认0

FBASECURRID,     --默认1

FEXCHANGETYPEID, --默认0

FEXCHANGERATE,   --默认0

FDATE,           --日期

FNOTE,           --规格调整入库

FSTOCKERID,      --默认0

FSTOCKERGROUPID, --默认0

FDOCUMENTSTATUS, --默认C为审核状态，并写入库存表

FCREATORID,      --制单人  

FCREATEDATE,     --当前时间

FMODIFIERID,     --同创建人

FMODIFYDATE,     --当前时间

FAPPROVERID,     --审核人等于创建人

FAPPROVEDATE,    --当前时间

FCANCELSTATUS,   --默认A

FCANCELLERID,    --默认0

FCANCELDATE,     --NULL

FOWNERTYPEID,    --默认BD_OwnerOrg 

FOWNERID,        --货主ID 

FOBJECTTYPEID,   --默认STK_MISCELLANEOUS 

FTRANSFERBIZTYPE) --NULL

  Select TOP 1 @aFID As FID,

'5c1b2a835fd066' As FBillTypeID, --库存调整入库

@StockBillNo As FBillNo,

FStockOrgID As FStockOrgID,

'GENERAL' As FStockDirect,    --默认GENERAL

0 As FSupplierID,     --默认0

0 As FACCEPTANCE,     --默认0

0 As FDEPTID,         --部门

0 As FCURRENCYID,     --默认0

1 As FBASECURRID,     --默认1

0 As FEXCHANGETYPEID, --默认0

0 As FEXCHANGERATE,   --默认0

--Convert(varchar(10),FDate,121) As FDate,   --库存日期