金蝶K3即时库存成本计算逻辑是什么？

最新推荐文章于 2022-03-08 18:18:18 发布

稀有物理理论家

最新推荐文章于 2022-03-08 18:18:18 发布

阅读量6.4k

点赞数 3

分类专栏： SQL学习 kingdee学习文章标签：金蝶

本文链接：https://blog.csdn.net/wacolly/article/details/78653689

版权

SQL学习同时被 2 个专栏收录

8 篇文章 1 订阅

订阅专栏

kingdee学习

6 篇文章 1 订阅

订阅专栏

主要想弄明白2个问题：

K3即时库存成本取数逻辑是什么？
- 核算参数勾选计算即时成本；
- 只支持加权平均【76】、分批认定法(批内加权平均法)【20309】；
K3即时库存为什么不能按天查询？商贸版却可以。

备忘：

文中的参考内容后续再做研究；

即时库存查询SQL语句分析

use AIS20171027150808 --晋西南TRQ
go
Set Nocount on;
--不显示详细的消息内容；off则显示.K3查询分析工具中如果需要创建临时表，则前面需要增加Set Nocount On.

Create Table #TempInventory( 
                            [FBrNo] [varchar] (10)  NOT NULL ,
                            [FItemID] [int] NOT NULL ,
                            [FBatchNo] [varchar] (200)  NOT NULL ,
                            [FMTONo] [varchar] (200)  NOT NULL ,
                            [FSupplyID] [int] NOT NULL ,
                            [FStockID] [int] NOT NULL ,
                            [FQty] [decimal](28, 10) NOT NULL ,
                            [FBal] [decimal](20, 2) NOT NULL ,
                            [FStockPlaceID] [int] NULL ,
                            [FKFPeriod] [int] NOT NULL Default(0),
                            [FKFDate] [varchar] (255)  NOT NULL ,
                            [FMyKFDate] [varchar] (255), 
                            [FStockTypeID] [Int] NOT NULL,
                            [FQtyLock] [decimal](28, 10) NOT NULL,
                            [FAuxPropID] [int] NOT NULL,
                            [FSecQty] [decimal](28, 10) NOT NULL,
                            [FHelpCode] [varchar](200) NOT NULL,
                            [FProperty] [int] NOT NULL Default(0),
                            [FChartNumber] [varchar](255) NOT NULL,
                            [FBatchNo2] [VarChar] (200) NOT NULL Default(''),
                            [FStockID2] [int]  NOT NULL Default(0)
                            );
--创建即时库存临时表;
--语法:
--create table #temptable_name ([column_name] [data_type](size) constraint,....)

Insert Into #TempInventory 
Select              u1.FBrNo,
u1.FItemID,
u1.FBatchNo,
u1.FMTONo,
u1.FSupplyID,
u1.FStockID,
u1.FQty,
u1.FBal,
u1.FStockPlaceID,
u1.FKFPeriod,
ISNULL(u1.FKFDate,''),
ISNULL(u1.FKFDate,''),
500,
u1.FQtyLock,
u1.FAuxPropID,
u1.FSecQty,
'',
s.FProperty ,
'',
'',
0 From ICInventory u1 left join t_stock s 
on u1.FStockID=s.FItemID  
where u1.FQty<>0 ;
--主题：在即时库存表中插入存货表的数据；
--释义：
--1.把ICInventory U1 （存货表）和 t_stock s （仓库表） 左连接；
--2.where u1.fqty <> 0 只显示数量不为0的物料；
--3.s.FProperty 库房属性：良品、不良品；
--此处直接把FstockTypeID（仓库类型）赋值为500，500在仓库表中表示普通仓，此处没有区分Inventory表里面的普通仓和其他；间接说明Inventory表不记录赠品仓数据；
--此步骤存货余额表中并未取到库存金额，只取到库存数量；
--语法：
--insert into #temptable_name select column_name from table u1 left jion table s on u1.Fid=s.FitemID where u1.FQty <> 0;

Insert Into #TempInventory 
Select u1.FBrNo,
u1.FItemID,
u1.FBatchNo,
u1.FMTONo,
u1.FSupplyID,
u1.FStockID,
u1.FQty,
u1.FBal,
u1.FStockPlaceID,
u1.FKFPeriod,
ISNULL(u1.FKFDate,''),
ISNULL(u1.FKFDate,''),
u1.FStockTypeID,
0,
u1.FAuxPropID,
u1.FSecQty,
'',
s.FProperty,
'' ,
'',
0 From POInventory u1 left join t_stock s 
on u1.FStockID=s.FItemID  
where u1.FQty<>0 ;
--在临时表中插入代管仓的数据；
--POInventory为代管仓库存余额表；

DECLARE @CalculateType AS INT 
--声明变量@CalculateType；

SELECT @CalculateType=FValue FROM t_SystemProfile 
WHERE FCategory='IC' AND FKey='CalculateType';
--存货核算方式；Fvalue=0为总仓核算；Fvalue=1为分仓核算；Fvalue=2为分仓库组核算；

UPDATE t1 SET 
FBatchNo2=(SELECT CASE t2.FTrack WHEN 80 THEN t1.FBatchNo WHEN 20309 THEN t1.FBatchNo ELSE '' END ), 
FStockID2=(SELECT CASE @CalculateType 
WHEN 0 THEN 0 WHEN 1 THEN t1.FStockID ELSE t3.FGroupID END)
FROM  #TempInventory t1 
INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID 
INNER JOIN t_Stock  t3 ON t1.FStockID=t3.FItemID ;
--给FbatchNo2和FstockID2赋值：
--1、当计价方法为80、20309时，FbatchNo2=FbatchNo，否则为null；[^Ftrack计价方法，20309批内加权，80批内移动加权；]
--2、当存货核算方式为总仓核算时，FStockID2=0，为分仓核算时，FStockID2=t1.FStockID，为分仓库组核算时，FStockID2=t3.FGroupID;
--这里为什么要把这2个计价方法的物料的批号复制一下呢？
--语法：
--update T1 set cloumn_name = （select case '' when '' then '' when '' then '' else '' end）,set .... from T1 inner join T2 ON T1.FID = T2.FID


Select distinct 
t2.FProperty as FProperty,--良品、不良品
u1.FAuxPropID,
case when u1.FSecQty=0 then 0 else ROUND(u1.FQty/u1.FSecQty,t1.FQtyDecimal) end as FConvRate,
u1.FStockTypeID,
t1.FName as FMaterialName,
t1.FModel as FMaterialModel,
t19.FName as FSecUnitName,
t19.FNumber as FSecUnitNumber,
u1.FBatchNo,
u1.FMTONo,
u1.FSupplyID,
t_8.FName AS FSupplyName,
t_8.FNumber AS FSupplyNumber,
t2.FName as FStockName ,
u1.FQtyLock as FBUQtyLock,
u1.FQtyLock/t4.FCoefficient as FCUUQtyLock,
t5.FName as FSPName,
u1.FKFPeriod,
case when isdate(u1.FKFDate)=1 then Convert(datetime,u1.FKFDate) else null end as FKFDate,
case when isdate(u1.FMyKFDate)=1 then Convert(datetime,u1.FMyKFDate) else null end as FMyKFDate, 
t3.FName as FBUUnitName,
t3.FNumber as FBUUnitNumber,
ROUND(u1.FQty,t1.FQtydecimal) as FBUQty,
t4.FName as FCUUnitName ,
ROUND(u1.FQty/t4.FCoefficient,
t1.FQtyDecimal) as FCUUQty,
t1.FQtyDecimal, 
t1.FPriceDecimal,
0 as FSumSort,
Case when isdate(u1.FKFDate)=0 then NULL else Convert(datetime,u1.FKFDate) + u1.FKFPeriod END AS FMaturityDate,
t2.FNumber AS FStockNumber, 
t2.FNumber AS FStockLongNumber ,
t1.FNumber AS FMaterialNumber,
t1.FNumber AS FLongNumber,
t5.FNumber as FSPNumber,
t4.FNumber as FCUUnitCode,
t4.FMeasureunitID as FCUUnitID,
t1.FitemID ,
T2.FitemID FStockID,
t2.FIncludeAccounting,
T5.FSPID FSPID,
t9.FName as FAuxPropName,
t9.FNumber as FAuxPropNumber,
ROUND(u1.FSecQty,t1.FQtyDecimal) AS FSecQty,
t1.FSecCoefficient AS FItemSecCoefficient,
t1.FHelpCode as FHelpCode,
t1.FChartNumber ,CASE WHEN  t2.FIncludeAccounting=1 THEN ISNULL(t20.FPrice,0) ELSE 0 END as FPrice,
CASE WHEN t2.FIncludeAccounting=1 THEN ISNULL(t20.FPrice * t4.FCoefficient,0) ELSE 0 END as FCUPrice,
CASE WHEN t2.FIncludeAccounting=1 THEN ISNULL(ROUND(t20.FPrice * u1.FQty,2),0) ELSE 0 END as FAmount  
From #TempInventory u1 
left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_Supplier t_8 on u1.FSupplyID=t_8.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID 
left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID   
left Join ICRealtimeCost t20 ON u1.FItemID=t20.FItemID AND u1.FBatchNo2=t20.FBatchNo AND u1.FStockID2=t20.FStockID  
--ICRealtimeCost即时库存表的成本来源，但是这个表的来源是什么？；
--来源是p_CheckRealtimeCost，点击‘校对’会执行该存储过程，然后更新ICRealtimeCost表；详细过程及说明见本文<即时成本>部分。

where (
Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0
) 
 and t1.FDeleted=0 
 AND t2.FItemID=1714
--select * from #TempInventory U1 left join T1 left join T2 left join .... where 
--Round(数值,四舍五入到几位小数)；示例：select round(1.235,2)=1.24
 Order By t1.FNumber,u1.FBatchNo,u1.FMTONo 
 Drop Table #TempInventory

校对即时成本SQL语句分析

关键词：

EXEC p_CheckRealtimeCost;
-- 校对即时库存时执行该存储过程;
-- EXEC主要用来执行存储过程或动态SQL语句串

详细内容：

/****** Object:  StoredProcedure [dbo].[p_CheckRealtimeCost]    Script Date: 12/04/2017 10:52:10 ******/
--上面的注释怎么生成的？每次修改存储过程日期会自动改变。
--下面2个set是SQL-92设置语句，使sql2000/2005/2008遵从SQL-92规则；具体的说明参考<[博文1](http://blog.sina.com.cn/s/blog_5e7917a50100bzq6.html)>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_CheckRealtimeCost]  
AS 
--***alter procedure as ...这句话的后半部分是哪里???
--'下面是官方注释：
--BT779284
--BT780524
--经过多次的确认，只支持加权平均【76】、分批认定法(批内加权平均法)【20309】
--其他的一概不支持，如果还要支持直接找李老师'   
--商贸版为什么可以支持所有的计价方法?
    SET NOCOUNT ON
    --这个看懂了，不返回详细的消息信息      
    DECLARE @CurYear     INT            --当前年份      
    DECLARE @CurPeriod   INT            --起始的会计期间      
    DECLARE @StartTime   DATETIME       --期间开始日期      
    DECLARE @EndTime     DATETIME       --期间结束日期      
    DECLARE @CalculateType INT          --核算方式     
    --↑声明变量declare，↓给变量赋值：
    --关于数据库局部变量的详细说明请[参考]博客或文末附件(blog.csdn.net/changwei07080/article/details/7561602)  

    SELECT @CalculateType=FValue   FROM t_Systemprofile WHERE FKey='CalculateType' And FCategory='IC'      
    SELECT @CurPeriod=FValue FROM t_Systemprofile WHERE FKey='CurrentPeriod' And FCategory='IC'      
    SELECT @CurYear=FValue   FROM t_Systemprofile WHERE FKey='CurrentYear' And FCategory='IC'               
    EXECUTE GetPeriodStartEnd 0, @CurPeriod, @StartTime OUTPUT, @EndTime OUTPUT      
--***************************************计算加权平均法的物料**********************************         
--计算期初数据    
    SELECT t1.FItemID, t1.FStockID, ISNULL(t1.FBatchNO, '') as FBatchNO,(t1.FBegQty) AS FQty, (t1.FBegBal) AS FAmount    
      INTO #RealTimeCost      
      FROM ICInvBal t1  --'库存余额表，字段包含期初、收入、发出、结存、本年累计；结账时生成本月的期初，上月的收入、上月的发出、上月的结存；'
      inner join t_ICItem t2 on t1.FItemID=t2.FItemID     
      inner join t_Stock t3 on t1.FStockID =t3.FItemID 
                         and t3.FIncludeAccounting =1 --'仓库属性：是否参与核算'
     WHERE t2.FTrack=76 
     AND t1.FPeriod = @CurPeriod      
     AND t1.FYear = @CurYear 
--入库单据和红字出库单  
--'（个人注解：此处统计的是入库类单据和出库类单据；出库类单据统计时要（*-1），因为在明细表里面蓝字单据的出库数量显示是正数，红字单据数量显示是负数。）
--ICSTOCKBILLENTRY明细表中的实际显示如下
--+—————————————————————————+
--|单据类型|物料|数量|单价|金额 |  
--|——————|———|————|————|————+
--|蓝字入库|A | 2  |2.00|4.00|
--|红字入库|A | -1 |2.00|-2.0|
--|蓝字出库|A | 1  |2.00|2.00|
--|红字出库|A | -1 |2.00|-2.0|
--#realtimecost表中需要上述合计，就需要把出库*-1 '
    INSERT INTO #RealTimeCost        
            SELECT  t1.FItemID, 
            case when t2.FTranType=24 then t1.FSCStockID else  t1.FDCStockID end  AS FStockID,--仓库
            ISNULL(t1.FBatchNO, '')  AS FBatchNo,--批号
            (CASE WHEN t2.FTranType IN (21,24,28,29,43)  THEN (-1* t1.FQty) ELSE t1.FQty END ) AS FQty, --数量（出库类型*-1）     
            (CASE WHEN t2.FTranType IN (21,24,28,29,43) THEN (-1* t1.FAmount) ELSE t1.FAmount END ) AS FAmount      
           FROM ICStockBillEntry t1   
           inner join ICStockBill t2 on t1.FInterID = t2.FInterID   
           inner join t_ICItem t3    on  t1.FItemID=t3.FItemID    
          WHERE t2.FCancelLation = 0  --'作废（0未作废）'
          AND t3.FTrack=76    --'加权平均，看到这里就明白了，为什么只支持了加权和批内加权，如果是其他计价方法就需要重新写sql了，可能需求不多，没继续做这个功能吧。之后有时间看看商贸版的即时库存表吧。'
          AND t2.FTranType IN (1,2,5,10,40,100,101,102,21,24,28,29)  
             --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算'
            and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 

            AND FDate >= @StartTime  
--调拨单 调入成本    
  INSERT INTO #RealTimeCost      
         SELECT t1.FItemID, t1.FDCStockID AS FStockID, ISNULL(t1.FBatchNO, ''),    
                t1.FQty, (t1.FAmtRef) AS FAmount    
           FROM ICStockBillEntry t1
           inner join ICStockBill t2 on t1.FInterID = t2.FInterID 
           inner join t_ICItem t3   on t1.FItemID=t3.FItemID    
          WHERE  t2.FTranType =41   AND t2.FCancelLation = 0  AND t3.FTrack=76  
          --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算'
            and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 
            AND FDate >= @StartTime      

--调拨单 调出成本         
    INSERT INTO #RealTimeCost      
         SELECT t1.FItemID, t1.FSCStockID AS FStockID, ISNULL(t1.FBatchNO, ''),    
                (-t1.FQty) AS FQty, (-t1.FAmount) AS FAmount    
           FROM ICStockBillEntry t1
           inner join  ICStockBill t2 on t1.FInterID = t2.FInterID 
           inner join t_ICItem t3     on t1.FItemID=t3.FItemID 
          WHERE  t2.FTranType =41   AND t2.FCancelLation = 0  AND t3.FTrack=76
            --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算 '
and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 
            AND FDate >= @StartTime      
--VMI入库单 BT782711
   INSERT INTO #RealTimeCost
   select t1.FItemID,t1.FStockID,ISNULL(t1.FBatchNo,''),t1.FQty,t1.FAmount from ICVMIInStockEntry t1 inner join t_ICItem t2
    on t1.FItemID =t2.FItemID  AND t2.FTrack=76  

--********计算成本***********************    
   DELETE ICRealtimeCost     
   SELECT t1.FItemID, t1.FStockID,t1.FQty,t1.FAmount    
     INTO #RealTime76     
     FROM #RealTimeCost t1     
   IF @CalculateType=0  --总仓核算    
      BEGIN     
 INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
             SELECT t1.FItemID, 0,'',SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
               FROM #RealTime76 t1    
           GROUP BY t1.FItemID    
     END    
   ELSE     
      IF @CalculateType=1 --分仓核算    
  BEGIN     
     INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
          SELECT t1.FItemID, t1.FStockID,'',SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
            FROM #RealTime76 t1    
        GROUP BY t1.FItemID,t1.FStockID    
  END    

      ELSE  --分仓组核算    
  BEGIN     
     INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
          SELECT t1.FItemID, t2.FGroupID,'',SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
           FROM #RealTime76  t1    
            INNER JOIN t_Stock   t2 ON t1.FStockID=t2.FItemID    
              GROUP BY t1.FItemID,t2.FGroupID    
        END    

--***************************************计算分批認定法(批內加權平均法)物料**********************************   
       DELETE #RealTimeCost   
    ----期初数据    
    INSERT INTO #RealTimeCost      
    SELECT t1.FItemID, t1.FStockID, t1.FBatchNo,(t1.FBegQty) AS FQty, (t1.FBegBal) AS FAmount     
      FROM ICInvBal t1
      inner join t_ICItem t2  on t1.FItemID=t2.FItemID  
      inner join t_Stock t3 on t1.FStockID =t3.FItemID and t3.FIncludeAccounting =1
       WHERE t2.FTrack=20309 AND t1.FPeriod = @CurPeriod      
       AND t1.FYear = @CurYear    
--入库单据和红字出库单    
    INSERT INTO #RealTimeCost        
            SELECT  t1.FItemID, case when t2.FTranType=24 then t1.FSCStockID else  t1.FDCStockID end  AS FStockID, ISNULL(t1.FBatchNO, '') as FBatchNO,
                (CASE WHEN t2.FTranType IN (21,24,28,29,43)  THEN (-1* t1.FQty)    
                             ELSE t1.FQty END ) AS FQty,      
                (CASE WHEN t2.FTranType IN (21,24,28,29,43) THEN (-1* t1.FAmount)    
                             ELSE t1.FAmount END ) AS FAmount      
           FROM ICStockBillEntry t1   
           inner join ICStockBill t2 on t1.FInterID = t2.FInterID   
           inner join t_ICItem t3    on  t1.FItemID=t3.FItemID    
          WHERE t2.FCancelLation = 0  
          AND t3.FTrack=20309    
          AND t2.FTranType IN (1,2,5,10,40,100,101,102,21,24,28,29)   
            --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算'
            and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 
            AND FDate >= @StartTime  
--调拨单 调入成本    
  INSERT INTO #RealTimeCost      
         SELECT t1.FItemID, t1.FDCStockID AS FStockID, ISNULL(t1.FBatchNO, ''),    
                t1.FQty, (t1.FAmtRef) AS FAmount    
           FROM ICStockBillEntry t1
           inner join  ICStockBill t2 on t1.FInterID = t2.FInterID 
           inner join t_ICItem t3   on t1.FItemID=t3.FItemID   
          WHERE  t2.FCancelLation = 0  AND t3.FTrack=20309      
            AND t2.FTranType =41    
            --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算'
            and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 
            AND FDate >= @StartTime      
--调拨单 调出成本         
    INSERT INTO #RealTimeCost      
         SELECT t1.FItemID, t1.FSCStockID AS FStockID, ISNULL(t1.FBatchNO, ''),    
                (-t1.FQty) AS FQty, (-t1.FAmount) AS FAmount    
           FROM ICStockBillEntry t1
           inner join  ICStockBill t2 on t1.FInterID = t2.FInterID 
           inner join t_ICItem t3 on t1.FItemID=t3.FItemID   
          WHERE  t2.FTranType =41  AND t2.FCancelLation = 0  AND t3.FTrack=20309   
          --'efeng_zhong 2013.11.21 废品专项，核算仓库数据才参与核算'
            and ((EXISTS(select FItemID  from t_Stock where FItemID =t1.FSCStockID and FIncludeAccounting=1 ) )
            or(EXISTS (select FItemID  from t_Stock where FItemID =t1.FDCStockID and FIncludeAccounting=1 ))) 
            AND FDate >= @StartTime      
--VMI入库单 BT782711
   INSERT INTO #RealTimeCost
   select t1.FItemID,t1.FStockID,ISNULL(t1.FBatchNo,''),t1.FQty,t1.FAmount from ICVMIInStockEntry t1 inner join t_ICItem t2
    on t1.FItemID =t2.FItemID  AND t2.FTrack=20309 
---********计算成本***********************    

   SELECT t1.FItemID, t1.FStockID,t1.FQty,t1.FAmount,t1.FBatchNo   
     INTO #RealTime20309     
     FROM #RealTimeCost t1      
   IF @CalculateType=0  --总仓核算    
      BEGIN     
 INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
             SELECT t1.FItemID, 0,t1.FBatchNo,SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
               FROM #RealTime20309 t1    
           GROUP BY t1.FItemID,t1.FBatchNo    
     END    
   ELSE     
      IF @CalculateType=1 --分仓核算    
  BEGIN     
     INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
          SELECT t1.FItemID, t1.FStockID,t1.FBatchNo,SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
            FROM #RealTime20309 t1    
        GROUP BY t1.FItemID,t1.FStockID ,t1.FBatchNo    
  END    

      ELSE  --分仓组核算    
  BEGIN     
     INSERT INTO ICRealtimeCost (FItemID,FStockID,FBatchNo,FQty,FAmount)    
          SELECT t1.FItemID, t2.FGroupID,t1.FBatchNo,SUM(t1.FQty) AS FQty, SUM(t1.FAmount) AS FAmount    
           FROM #RealTime20309  t1    
            INNER JOIN t_Stock  t2 ON t1.FStockID=t2.FItemID    
              GROUP BY t1.FItemID,t2.FGroupID ,t1.FBatchNo   
        END      
--************计算单位成本***************    
    UPDATE t1    
    --PT097541 csli_liu 20150917 出库核算提示算数溢出错误
    --数量为0.0000000002当0处理
       SET t1.FPrice=(SELECT CASE ROUND(t1.FQty,8) WHEN 0 THEN 0 ELSE ROUND(t1.FAmount/t1.FQty,t2.FPriceDecimal) END )     
      FROM ICRealtimeCost t1    
    INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID    

    --删除临时表   
    DROP TABLE #RealTime76          
    DROP TABLE #RealTimeCost      
    DROP TABLE #RealTime20309

附：

单词学习

单词	发音	释义
quoted	[kwəʊtid]	引证
procedure	[prə’siːdʒə]	过程，程序
declare	[dɪ’kleə]	声明
current	[‘kʌr(ə)nt]	现在的，当前的
period	[‘pɪərɪəd]	周期，期间

变量学习

示例：在K3帐套里面使用，根据[@单号]和[@单据类型]查询单据明细

declare @tran int
declare @fnumber varchar(255) ;
    --声明

select @tran='24',@fnumber = 'SOUT000002';
--or
set @tran='24'
set @fnumber = 'SOUT000002';
    --赋值;有2种方法：select可以连续赋值，通过逗号分割；set需要分别赋值；语法：select(set) @column = table.column from table where column operater value;

select @tran,@fnumber
--or
print @tran
print @fnumber;
     --输出;有2种方法：select可以连续输出，为表格形式；print需要分别输出，为文本形式

select * from ICStockBillEntry where FInterID in 
(select FInterID from ICStockBill where FTranType = @tran and FBillNo = @fnumber  );
     --使用

语法学习

if...begin....end(else...if...begin...end)

总结

0、把ICInventory库存表中的数量插入#TempInventory；
————————————————————————————
1、创建#RealTimeCost用来计算#RealTime76
2、删除#RealTimeCost
3、创建#RealTimeCost用来计算#RealTime20309
4、把#RealTime76和#RealTime20309插入ICRealtimeCost
5、把ICRealtimeCost的单价插入#TempInventory