很多时候，客户需要通过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, --库存日期
@StockDate As FDate, --新的库存日期
'后台生成其他入库'+FBillNo+'库存调整入库' As FNOTE, --库存调整入库
0 As FSTOCKERID, --默认0
0 As FSTOCKERGROUPID, --默认0
'C' As FDOCUMENTSTATUS, --默认C为审核状态，并写入库存表
@K3UserID As FCREATORID, --制单人
GetDate() As FCREATEDATE, --当前时间
@K3UserID As FMODIFIERID, --同创建人
GetDate() As FMODIFYDATE, --当前时间
@K3UserID As FAPPROVERID, --审核人等于创建人
GetDate() As FAPPROVEDATE, --当前时间
'A' As FCANCELSTATUS, --默认A
0 As FCANCELLERID, --默认0
NULL As FCANCELDATE, --NULL
'BD_OwnerOrg' As FOWNERTYPEID, --默认BD_OwnerOrg
FStockOrgID As FOWNERID, --货主ID
'STK_MISCELLANEOUS' As FOBJECTTYPEID, --默认STK_MISCELLANEOUS
NULL As FTRANSFERBIZTYPE
From #BillData1
--存在该物料的库存记录直接更新
Update t1 Set t1.FBaseQty = t1.FBaseQty + t4.FPYQty,
t1.FSecQty = t1.FSecQty + t4.FExtPYQty
From t_STK_Inventory t1
Inner Join (Select t2.FMasterID,t3.FStockID,t0.FExtPYQty,t0.FPYQty From #BillData1 t0
Inner Join t_BD_Material t2 On t0.FItemNum = t2.FNumber and t2.FUseOrgID = @StockOrgID
Inner Join t_BD_Stock t3 On t0.FStockNum = t3.FNumber and t3.FUseOrgID = @StockOrgID
) t4 On t1.FOwnerID = @StockOrgID and t1.FStockOrgID = @StockOrgID and t1.FStockID = t4.FStockID and t1.FMaterialID = t4.FMasterID
Where ABS(t4.FPYQty)+ABS(t4.FExtPYQty) <> 0
--不存在该物料的库存记录则插入
Insert Into t_STK_Inventory (FID,FSTOCKORGID,FKEEPERTYPEID,FKEEPERID,FOWNERTYPEID,FOWNERID,FSTOCKID,FSTOCKLOCID,FAUXPROPID,FSTOCKSTATUSID,
FLOT,FBOMID,FMTONO,FPROJECTNO,FPRODUCEDATE,FEXPIRYDATE,FBASEUNITID,FBASEQTY,FBASELOCKQTY,FSECQTY,FSECLOCKQTY,
FSTOCKUNITID,FMATERIALID,FQTY,FLOCKQTY,FSECUNITID,FOBJECTTYPEID,FBASEAVBQTY,FAVBQTY,FSECAVBQTY,FUPDATETIME,
--FCOMBINEID,--公式自动计算
FISEFFECTIVED)
Select NEWID() As FID,@StockOrgID,'BD_KeeperOrg',@StockOrgID As FKEEPERID,--货主默认等于保管者
'BD_OwnerOrg',@StockOrgID As FOWNERID,A.FStockID,0,0,10000 As FSTOCKSTATUSID,--库存状态
0,0,'','',NULL,NULL,
A.FBaseUnitID,A.FPYQty As FBaseQty,0 As FBaseLockQty,
A.FExtPYQty As FSecQty,0 As FSecLockQty,
A.FStoreUnitID As FStockUnitID,A.FMasterID As FMaterialID,0 As FQty,0 As FLockQty,A.FAuxUnitID As FSecUnitID,
'STK_Inventory',0,0,0,GETDATE() As FUpdateTime,1 As FIseffectiveID
From (Select t2.FMasterID,t0.FStockID,t4.FStoreUnitID,t600.FBaseUnitID,t4.FAuxUnitID,t0.FExtPYQty,t0.FPYQty From #BillData1 t0
Inner Join t_BD_Material t2 On t0.FMaterialID = t2.FMaterialID--t0.FItemNum = t2.FNumber and t2.FUseOrgID = @StockOrgID
--Inner Join t_BD_Stock t3 On t0.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
) A
Where NOT EXISTS (Select 1 From t_STK_Inventory B Where B.FOwnerID = @StockOrgID and B.FStockOrgID = @StockOrgID and A.FStockID = B.FStockID and A.FMasterID = B.FMaterialID)
and ABS(A.FPYQty)+ABS(A.FExtPYQty) <> 0
Delete From Z_STK_MiscellaneousEntry
Update t_STK_InventoryBillData Set FIsExport = 1 Where FBillNo = @BillNo and FIndex in (Select F1 From f_splitstr(@Index,'@') Where F1 <> '') and ABS(FExtPYQty) +ABS(FPYQty)+ABS(FOtherPYQty) <> 0
end--其他入库结束
推荐阅读