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

栏目:云星空知识作者:金蝶来源:金蝶云社区发布:2024-09-16浏览:1

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,   --库存日期

@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--其他入库结束


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

很多时候,客户需要通过SQL语句插入其他入库单,本文就介绍如何通过SQL语句插入其他入库单的功能实现,主要涉及如下内容:1、其他入库单的...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息