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

110 阅读 0 评论 73 点赞

我是靠谱客的博主谦让便当，这篇文章主要介绍金蝶K3即时库存成本计算逻辑是什么？，现在分享给大家，希望可以做个参考。

- 主要想弄明白2个问题
- 备忘
- 即时库存查询SQL语句分析
- 校对即时成本SQL语句分析
- 附
  - 单词学习
  - 变量学习
  - 语法学习
- 总结

主要想弄明白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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
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语句分析

关键词：

复制代码

1
2
3
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
/****** 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帐套里面使用，根据[@单号]和[@单据类型]查询单据明细

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
);
--使用

语法学习

复制代码

1
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

最后

以上就是谦让便当最近收集整理的关于金蝶K3即时库存成本计算逻辑是什么？的全部内容，更多相关金蝶K3即时库存成本计算逻辑是什么内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：SQL学习
浏览次数：110 次浏览
发布日期：2024-01-22 02:35:51
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_okfz_13__23_gx.html

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

主要想弄明白2个问题：

备忘：

即时库存查询SQL语句分析

校对即时成本SQL语句分析

附：

单词学习

变量学习

语法学习

总结

最后

评论列表共有 0 条评论

发表评论取消回复

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

主要想弄明白2个问题：

备忘：

即时库存查询SQL语句分析

校对即时成本SQL语句分析

附：

单词学习

变量学习

语法学习

总结

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

微信扫一扫：分享

发表评论取消回复