-
U870全月平均价计算错误问题解决方案
- 问题一：平均单价计算错误
问题现象：平均单价明显不正确
解决方案：
1、更新补丁后恢复记帐，恢复期末初期并重新期末处理记帐（推荐解决方案）
2、如果已经出了报表不能重新处理的客户可以考虑在本月份进行调整平均单价计算错误带来的影响。具体方案如下：
以下语句从系统中查找出平均单价计算错误的单据和金额，客户根据情况，如果需要调整，可以手工生成调整单在本月进行调整：
第一步：
if exists (SELECT name FROM sysobjects WHERE name='IA_sp_CalcMyPrice' And xtype = 'P') Drop PROCEDURE IA_sp_CalcMyPrice
go
CREATE PROCEDURE IA_sp_CalcMyPrice
@imonth integer,--当前会计月（存货核算）
@caccounter nVarchar(20), --当前操作员
@bRollCall as tinyint,
@errmsg nVarchar(100) output
AS
--取存货核算方式
Declare @cAccount nvarchar(10)
Set @cAccount = (Select cValue From accinformation Where csysid = 'IA' and cName = 'cValueStyle')
--取零成本出库设置
Declare @cZero nvarchar(10)
Set @cZero = (Select cValue From accinformation Where csysid = 'IA' and cName = 'cZero')
--取小数位数
Declare @iQuantityBits int
Set @iQuantityBits = (Select cValue From accinformation Where csysid = 'AA' and cName = 'iStrsQuanDecDgt')
Declare @iCostBits int
Set @iCostBits = (Select cValue From accinformation Where csysid = 'AA' and cName = 'iStrsPriDecDgt')
--是否控制最大,最小单价
Declare @bmaxmin nvarchar(10)
Set @bmaxmin = (Select cValue From accinformation Where csysid = 'IA' and cName = 'bmaxmin')
--最大、最小单价控制
Declare @cMaxMinCost nvarchar(10)
Set @cMaxMinCost = (Select cValue From accinformation Where csysid = 'IA' and cName = 'cMaxMinCost')
--存储过程变量
Declare @iCost float
Declare @sSql nvarchar(4000)
Declare @AutoID int
Declare @cInvCode nvarchar(20)
Declare @cWhDepCode nvarchar(10)
Declare @cFree1 nvarchar(20)
Declare @cFree2 nvarchar(20)
Declare @cFree3 nvarchar(20)
Declare @cFree4 nvarchar(20)
Declare @cFree5 nvarchar(20)
Declare @cFree6 nvarchar(20)
Declare @cFree7 nvarchar(20)
Declare @cFree8 nvarchar(20)
Declare @cFree9 nvarchar(20)
Declare @cFree10 nvarchar(20)
--如果需要创建的临时表存在,则先删除他
if exists (SELECT name FROM sysobjects WHERE name='UnitCostMassRoll' And xtype = 'U') Drop Table UnitCostMassRoll
--创建计算平均单价临时表,本期期初,本期入库,本期出库,相应数量金额
Create Table UnitCostMassRoll (cInvCode nvarchar(20),cWhDepCode nvarchar(20),
[cFree1] [nvarchar] (20) NULL ,[cFree2] [nvarchar] (20) NULL ,[cFree3] [nvarchar] (20) NULL ,[cFree4] [nvarchar] (20) NULL ,[cFree5] [nvarchar] (20) NULL ,
[cFree6] [nvarchar] (20) NULL ,[cFree7] [nvarchar] (20) NULL ,[cFree8] [nvarchar] (20) NULL ,[cFree9] [nvarchar] (20) NULL ,[cFree10] [nvarchar] (20) NULL ,
iQcQuantity float default 0,iQcMoney float default 0,iInQuantity float default 0,iInMoney float default 0,
iOutQuantity float default 0,iOutMoney float default 0)
if @cAccount = N'按存货核算'
begin
--向临时表中插入本期期初数量,金额
Insert Into UnitCostMassRoll(cInvCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
iQcQuantity,iQcMoney)
Select My_PerSummary.cInvCode,isnull(cFree1, N''),isnull(cFree2, N''),isnull(cFree3, N''),isnull(cFree4, N''),isnull(cFree5, N''),isnull(cFree6, N''),isnull(cFree7, N''),isnull(cFree8, N''),isnull(cFree9, N''),isnull(cFree10, N''),
SUM(Isnull(My_PerSummary.iNum,0) - Isnull(My_PerSummary.iInum,0) + IsNull(My_PerSummary.iOnum,0)),
SUM(IsNull(My_PerSummary.iMoney,0) - IsNull(My_PerSummary.iIMoney,0) + IsNull(My_PerSummary.iOMoney,0))
From My_PerSummary
Left Join Inventory On My_PerSummary.cInvCode = Inventory.cInvcode
Inner Join My_PerInv On My_PerInv.cInvCode = My_PerSummary.cInvCode
where My_PerSummary.iMonth = @imonth And Inventory.cValueType =N'全月平均法' And IsNull(My_PerSummary.iDirect,0) = 0 And Isnull(My_PerSummary.iPeriod, 0) = 0
Group By My_PerSummary.cInvCode,isNull(cFree1,N''),isNull(cFree2,N''),isNull(cFree3,N''),isNull(cFree4,N''),isNull(cFree5,N''),
isNull(cFree6,N''),isNull(cFree7,N''),isNull(cFree8,N''),isNull(cFree9,N''),isNull(cFree10,N'')
--插入本期发生入库记录和本期发生自填单价的出库记录
Insert Into UnitCostMassRoll(cInvCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
iInQuantity,iInMoney,iOutQuantity,iOutMoney)
Select IA_Subsidiary.cInvCode,
isnull(My_PerSummary.cFree1, N''),isnull(My_PerSummary.cFree2, N''),isnull(My_PerSummary.cFree3, N''),isnull(My_PerSummary.cFree4, N''),isnull(My_PerSummary.cFree5, N''),
isnull(My_PerSummary.cFree6, N''),isnull(My_PerSummary.cFree7, N''),isnull(My_PerSummary.cFree8, N''),isnull(My_PerSummary.cFree9, N''),isnull(My_PerSummary.cFree10, N''),
Isnull(IA_Subsidiary.IaInQuantity,0),Isnull(IA_Subsidiary.IaInPrice,0),
IsNull(IA_Subsidiary.IaOutquantity,0),IsNull(IA_Subsidiary.IaOutPrice,0)
From IA_Subsidiary
Left Join Inventory On IA_Subsidiary.cInvCode = Inventory.cInvcode
Inner Join My_PerInv On My_PerInv.cInvCode = IA_Subsidiary.cInvCode
Inner Join My_PerSummary
On My_PerSummary.cInvCode = ia_subsidiary.cInvCode And My_PerSummary.iDirect = 0 And My_PerSummary.iMonth = @imonth
And (IsNull(My_PerSummary.cFree1, N'') = IsNull(ia_subsidiary.cFree1, N'') Or IsNull(My_PerSummary.cFree1, N'') = N'') And (IsNull(My_PerSummary.cFree2, N'') = IsNull(ia_subsidiary.cFree2, N'') Or IsNull(My_PerSummary.cFree2, N'') = N'')
And (IsNull(My_PerSummary.cFree3, N'') = IsNull(ia_subsidiary.cFree3, N'') Or IsNull(My_PerSummary.cFree3, N'') = N'') And (IsNull(My_PerSummary.cFree4, N'') = IsNull(ia_subsidiary.cFree4, N'') Or IsNull(My_PerSummary.cFree4, N'') = N'')
And (IsNull(My_PerSummary.cFree5, N'') = IsNull(ia_subsidiary.cFree5, N'') Or IsNull(My_PerSummary.cFree5, N'') = N'') And (IsNull(My_PerSummary.cFree6, N'') = IsNull(ia_subsidiary.cFree6, N'') Or IsNull(My_PerSummary.cFree6, N'') = N'')
And (IsNull(My_PerSummary.cFree7, N'') = IsNull(ia_subsidiary.cFree7, N'') Or IsNull(My_PerSummary.cFree7, N'') = N'') And (IsNull(My_PerSummary.cFree8, N'') = IsNull(ia_subsidiary.cFree8, N'') Or IsNull(My_PerSummary.cFree8, N'') = N'')
And (IsNull(My_PerSummary.cFree9, N'') = IsNull(ia_subsidiary.cFree9, N'') Or IsNull(My_PerSummary.cFree9, N'') = N'') And (IsNull(My_PerSummary.cFree10, N'') = IsNull(ia_subsidiary.cFree10, N'') Or IsNull(My_PerSummary.cFree10, N'') = N'')
where Inventory.cValueType = N'全月平均法' And IsNull(IA_Subsidiary.cBusType, N'') <> N'直运销售'
And IA_Subsidiary.iMonth = @imonth And (bRdflag = 1 OR bMoneyflag = 1) And Isnull(My_PerSummary.iPeriod, 0) = 0
end
if @cAccount = N'按仓库核算'
begin
--向临时表中插入本期期初数量,金额
Insert Into UnitCostMassRoll(cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,iQcQuantity,iQcMoney)
Select My_PerSummary.cInvCode, My_PerSummary.cWhCode,isnull(cFree1, N''),isnull(cFree2, N''),isnull(cFree3, N''),isnull(cFree4, N''),isnull(cFree5, N''),isnull(cFree6, N''),isnull(cFree7, N''),isnull(cFree8, N''),isnull(cFree9, N''),isnull(cFree10, N''),
SUM(IsNull(My_PerSummary.iNum,0) - IsNull(My_PerSummary.iInum,0) + IsNull(My_PerSummary.iOnum,0)),
SUM(IsNull(My_PerSummary.iMoney,0) - IsNull(My_PerSummary.iIMoney,0) + IsNull(My_PerSummary.iOMoney,0))
From My_PerSummary
Left Join Warehouse ON My_PerSummary.cWhCode=Warehouse.cWhCode
Inner Join My_PerWhDep On My_PerSummary.cWhCode = My_PerWhDep.cWhDepCode
Inner Join My_PerInv On My_PerSummary.cInvCode = My_PerInv.cInvCode
where My_PerSummary.iMonth = @imonth And IsNull(My_PerSummary.iDirect, 0) = 0 And Isnull(My_PerSummary.iPeriod, 0) = 0
and Warehouse.cWhValueStyle = N'全月平均法' Group By My_PerSummary.cInvCode, My_PerSummary.cWhCode,isNull(cFree1,N''),isNull(cFree2,N''),isNull(cFree3,N''),isNull(cFree4,N''),isNull(cFree5,N''),
isNull(cFree6,N''),isNull(cFree7,N''),isNull(cFree8,N''),isNull(cFree9,N''),isNull(cFree10,N'')
--插入本期发生入库记录和本期发生自填单价的出库记录
Insert Into UnitCostMassRoll(cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,iInQuantity,iInMoney,iOutQuantity,iOutMoney)
Select IA_Subsidiary.cInvCode,IA_Subsidiary.cWhCode,
isnull(My_PerSummary.cFree1, N''),isnull(My_PerSummary.cFree2, N''),isnull(My_PerSummary.cFree3, N''),isnull(My_PerSummary.cFree4, N''),isnull(My_PerSummary.cFree5, N''),
isnull(My_PerSummary.cFree6, N''),isnull(My_PerSummary.cFree7, N''),isnull(My_PerSummary.cFree8, N''),isnull(My_PerSummary.cFree9, N''),isnull(My_PerSummary.cFree10, N''),
Isnull(IA_Subsidiary.IaInQuantity,0),Isnull(IA_Subsidiary.IaInPrice,0),
IsNull(IA_Subsidiary.IaOutquantity,0),IsNull(IA_Subsidiary.IaOutPrice,0)
From IA_Subsidiary
Left Join Inventory On IA_Subsidiary.cInvCode = Inventory.cInvcode
Left Join Warehouse ON IA_Subsidiary.cWhCode=Warehouse.cWhCode
Inner Join My_PerWhDep On IA_Subsidiary.cWhCode = My_PerWhDep.cWhDepCode
Inner Join My_PerInv On IA_Subsidiary.cInvCode = My_PerInv.cInvCode
Inner Join My_PerSummary
On My_PerSummary.cInvCode = ia_subsidiary.cInvCode And My_PerSummary.iDirect = 0 And My_PerSummary.iMonth = @imonth
And My_PerSummary.cWhCode = IA_subsidiary.cWhCode
And (IsNull(My_PerSummary.cFree1, N'') = IsNull(ia_subsidiary.cFree1, N'') Or IsNull(My_PerSummary.cFree1, N'') = N'') And (IsNull(My_PerSummary.cFree2, N'') = IsNull(ia_subsidiary.cFree2, N'') Or IsNull(My_PerSummary.cFree2, N'') = N'')
And (IsNull(My_PerSummary.cFree3, N'') = IsNull(ia_subsidiary.cFree3, N'') Or IsNull(My_PerSummary.cFree3, N'') = N'') And (IsNull(My_PerSummary.cFree4, N'') = IsNull(ia_subsidiary.cFree4, N'') Or IsNull(My_PerSummary.cFree4, N'') = N'')
And (IsNull(My_PerSummary.cFree5, N'') = IsNull(ia_subsidiary.cFree5, N'') Or IsNull(My_PerSummary.cFree5, N'') = N'') And (IsNull(My_PerSummary.cFree6, N'') = IsNull(ia_subsidiary.cFree6, N'') Or IsNull(My_PerSummary.cFree6, N'') = N'')
And (IsNull(My_PerSummary.cFree7, N'') = IsNull(ia_subsidiary.cFree7, N'') Or IsNull(My_PerSummary.cFree7, N'') = N'') And (IsNull(My_PerSummary.cFree8, N'') = IsNull(ia_subsidiary.cFree8, N'') Or IsNull(My_PerSummary.cFree8, N'') = N'')
And (IsNull(My_PerSummary.cFree9, N'') = IsNull(ia_subsidiary.cFree9, N'') Or IsNull(My_PerSummary.cFree9, N'') = N'') And (IsNull(My_PerSummary.cFree10, N'') = IsNull(ia_subsidiary.cFree10, N'') Or IsNull(My_PerSummary.cFree10, N'') = N'')
where IA_Subsidiary.iMonth = @imonth And Isnull(My_PerSummary.iPeriod, 0) = 0
and Warehouse.cWhValueStyle = N'全月平均法' and (bRdflag = 1 OR bMoneyflag = 1)
end
if @cAccount = N'按部门核算'
begin
--向临时表中插入本期期初数量,金额
Insert Into UnitCostMassRoll (cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,iQcQuantity,iQcMoney)
Select My_PerSummary.cInvCode, My_PerSummary.cDepCode,isnull(cFree1, N''),isnull(cFree2, N''),isnull(cFree3, N''),isnull(cFree4, N''),isnull(cFree5, N''),isnull(cFree6, N''),isnull(cFree7, N''),isnull(cFree8, N''),isnull(cFree9, N''),isnull(cFree10, N''),
SUM(IsNull(My_PerSummary.iNum,0) - IsNull(My_PerSummary.iInum,0) + IsNull(My_PerSummary.iOnum,0)),
SUM(IsNull(My_PerSummary.iMoney,0) - IsNull(My_PerSummary.iIMoney,0) + IsNull(My_PerSummary.iOMoney,0))
From My_PerSummary
Left Join (Select distinct cDepCode, cWhValueStyle From Warehouse) Ia_DepValueType ON My_PerSummary.cDepCode=Ia_DepValueType.cDepCode
Inner Join My_PerWhDep On My_PerSummary.cDepCode = My_PerWhDep.cWhDepCode
Inner Join My_PerInv On My_PerSummary.cInvCode = My_PerInv.cInvCode
where My_PerSummary.iMonth = @imonth And IsNull(My_PerSummary.iDirect, 0) = 0 And Isnull(My_PerSummary.iPeriod, 0) = 0
and Ia_DepValueType.cWhValueStyle = N'全月平均法' Group By My_PerSummary.cInvCode, My_PerSummary.cDepCode,isNull(cFree1,N''),isNull(cFree2,N''),isNull(cFree3,N''),isNull(cFree4,N''),isNull(cFree5,N''),
isNull(cFree6,N''),isNull(cFree7,N''),isNull(cFree8,N''),isNull(cFree9,N''),isNull(cFree10,N'')
--插入本期发生入库记录和本期发生自填单价的出库记录
Insert Into UnitCostMassRoll (cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,iInQuantity,iInMoney,iOutQuantity,iOutMoney)
Select IA_Subsidiary.cInvCode, IA_Subsidiary.cDepCode,
isnull(My_PerSummary.cFree1, N''),isnull(My_PerSummary.cFree2, N''),isnull(My_PerSummary.cFree3, N''),isnull(My_PerSummary.cFree4, N''),isnull(My_PerSummary.cFree5, N''),
isnull(My_PerSummary.cFree6, N''),isnull(My_PerSummary.cFree7, N''),isnull(My_PerSummary.cFree8, N''),isnull(My_PerSummary.cFree9, N''),isnull(My_PerSummary.cFree10, N''),
Isnull(IA_Subsidiary.IaInQuantity,0),Isnull(IA_Subsidiary.IaInPrice,0),
IsNull(IA_Subsidiary.IaOutquantity,0),IsNull(IA_Subsidiary.IaOutPrice,0) From IA_Subsidiary
Left Join Inventory On IA_Subsidiary.cInvCode = Inventory.cInvcode
Left Join Warehouse ON IA_Subsidiary.cWhCode=Warehouse.cWhCode
Inner Join My_PerWhDep On IA_Subsidiary.cDepCode = My_PerWhDep.cWhDepCode
Inner Join My_PerInv On IA_Subsidiary.cInvCode = My_PerInv.cInvCode
Inner Join My_PerSummary
On My_PerSummary.cInvCode = ia_subsidiary.cInvCode And My_PerSummary.iDirect = 0 And My_PerSummary.iMonth = @imonth
And My_PerSummary.cDepCode = IA_subsidiary.cDepCode
And (IsNull(My_PerSummary.cFree1, N'') = IsNull(ia_subsidiary.cFree1, N'') Or IsNull(My_PerSummary.cFree1, N'') = N'') And (IsNull(My_PerSummary.cFree2, N'') = IsNull(ia_subsidiary.cFree2, N'') Or IsNull(My_PerSummary.cFree2, N'') = N'')
And (IsNull(My_PerSummary.cFree3, N'') = IsNull(ia_subsidiary.cFree3, N'') Or IsNull(My_PerSummary.cFree3, N'') = N'') And (IsNull(My_PerSummary.cFree4, N'') = IsNull(ia_subsidiary.cFree4, N'') Or IsNull(My_PerSummary.cFree4, N'') = N'')
And (IsNull(My_PerSummary.cFree5, N'') = IsNull(ia_subsidiary.cFree5, N'') Or IsNull(My_PerSummary.cFree5, N'') = N'') And (IsNull(My_PerSummary.cFree6, N'') = IsNull(ia_subsidiary.cFree6, N'') Or IsNull(My_PerSummary.cFree6, N'') = N'')
And (IsNull(My_PerSummary.cFree7, N'') = IsNull(ia_subsidiary.cFree7, N'') Or IsNull(My_PerSummary.cFree7, N'') = N'') And (IsNull(My_PerSummary.cFree8, N'') = IsNull(ia_subsidiary.cFree8, N'') Or IsNull(My_PerSummary.cFree8, N'') = N'')
And (IsNull(My_PerSummary.cFree9, N'') = IsNull(ia_subsidiary.cFree9, N'') Or IsNull(My_PerSummary.cFree9, N'') = N'') And (IsNull(My_PerSummary.cFree10, N'') = IsNull(ia_subsidiary.cFree10, N'') Or IsNull(My_PerSummary.cFree10, N'') = N'')
where IA_Subsidiary.iMonth = @imonth And Isnull(My_PerSummary.iPeriod, 0) = 0
and Warehouse.cWhValueStyle = N'全月平均法' and (bRdflag = 1 OR bMoneyflag = 1)
end
--如果需要创建的临时表存在,则先删除他
if exists (SELECT name FROM sysobjects WHERE name='UnitCostMassRollValue' And xtype = 'U') Drop Table UnitCostMassRollValue
Create Table UnitCostMassRollValue ([AutoID] [int] IDENTITY (1, 1) NOT NULL ,cInvCode nvarchar(20),cWhDepCode nvarchar(20),
[cFree1] [nvarchar] (20) NULL ,[cFree2] [nvarchar] (20) NULL ,[cFree3] [nvarchar] (20) NULL ,[cFree4] [nvarchar] (20) NULL ,[cFree5] [nvarchar] (20) NULL ,
[cFree6] [nvarchar] (20) NULL ,[cFree7] [nvarchar] (20) NULL ,[cFree8] [nvarchar] (20) NULL ,[cFree9] [nvarchar] (20) NULL ,[cFree10] [nvarchar] (20) NULL ,
iQcQuantity float default 0,iQcMoney float default 0,iInQuantity float default 0,iInMoney float default 0,iOutQuantity float default 0,iOutMoney float default 0,
iCalPrice float default 0,iQuantity float default 0,iMoney float default 0,Price float default 0,iMaxPrice float default 0,iMinPrice float default 0,iInvSPrice float default 0)
--将平均单价表中数据汇总后插入平均单价值表
if @cAccount=N'按存货核算'
Insert Into UnitCostMassRollValue (cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
iQcQuantity,iQcMoney,iInQuantity,iInMoney,iOutQuantity,iOutMoney,iCalPrice,iQuantity,iMoney,Price,iMaxPrice,iMinPrice)
select cInvCode, null,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
Sum(iQcQuantity),Sum(iQcMoney),Sum(iInQuantity),Sum(iInMoney),Sum(iOutQuantity),Sum(iOutMoney),
(Case when Isnull(Sum(iQcQuantity+iInQuantity-iOutQuantity),0)=0 Then Null Else Sum(iQcMoney+iInMoney-iOutMoney)/Sum(iQcQuantity+iInQuantity-iOutQuantity) end),
Sum(iQcQuantity+iInQuantity-iOutQuantity),Sum(iQcMoney+iInMoney-iOutMoney),
(Case when Isnull(Sum(iQcQuantity+iInQuantity-iOutQuantity),0)=0 Then Null Else Sum(iQcMoney+iInMoney-iOutMoney)/Sum(iQcQuantity+iInQuantity-iOutQuantity) end),
0,0 From UnitCostMassRoll
Where Not cInvCode Is Null Group By cInvCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10
else
Insert Into UnitCostMassRollValue (cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
iQcQuantity,iQcMoney,iInQuantity,iInMoney,iOutQuantity,iOutMoney,iCalPrice,iQuantity,iMoney,Price,iMaxPrice,iMinPrice)
select cInvCode,cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
Sum(iQcQuantity),Sum(iQcMoney),Sum(iInQuantity),Sum(iInMoney),Sum(iOutQuantity),Sum(iOutMoney),
(Case when Isnull(Sum(iQcQuantity+iInQuantity-iOutQuantity),0)=0 Then Null Else Sum(iQcMoney+iInMoney-iOutMoney)/Sum(iQcQuantity+iInQuantity-iOutQuantity) end),
Sum(iQcQuantity+iInQuantity-iOutQuantity),Sum(iQcMoney+iInMoney-iOutMoney),
(Case when Isnull(Sum(iQcQuantity+iInQuantity-iOutQuantity),0)=0 Then Null Else Sum(iQcMoney+iInMoney-iOutMoney)/Sum(iQcQuantity+iInQuantity-iOutQuantity) end),
0,0 From UnitCostMassRoll
Where Not cInvCode Is Null and Not cWhDepCode is Null Group By cInvCode, cWhDepCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10
--更新参考成本
Update UnitCostMassRollValue set iInvSPrice=Inventory.iInvSPrice
From UnitCostMassRollValue
Left Join Inventory On UnitCostMassRollValue.cInvCode=Inventory.cInvCode
--处理最高最低单价
if @bmaxmin = N'TRUE'
begin
if @cAccount = N'按存货核算'
begin
--更新临时表中最大,小单价
Update UnitCostMassRollValue set iMaxPrice = IA_MaxMinCostDif.MaxCost,iMinPrice = IA_MaxMinCostDif.MinCost
From UnitCostMassRollValue
Left Join IA_MaxMinCostDif On UnitCostMassRollValue.cInvCode = IA_MaxMinCostDif.cInvCode
And IsNull(IA_MaxMinCostDif.cFree1,N'') = IsNull(UnitCostMassRollValue.cFree1,N'') And IsNull(IA_MaxMinCostDif.cFree2,N'') = IsNull(UnitCostMassRollValue.cFree2,N'')
And IsNull(IA_MaxMinCostDif.cFree3,N'') = IsNull(UnitCostMassRollValue.cFree3,N'') And IsNull(IA_MaxMinCostDif.cFree4,N'') = IsNull(UnitCostMassRollValue.cFree4,N'')
And IsNull(IA_MaxMinCostDif.cFree5,N'') = IsNull(UnitCostMassRollValue.cFree5,N'') And IsNull(IA_MaxMinCostDif.cFree6,N'') = IsNull(UnitCostMassRollValue.cFree6,N'')
And IsNull(IA_MaxMinCostDif.cFree7,N'') = IsNull(UnitCostMassRollValue.cFree7,N'') And IsNull(IA_MaxMinCostDif.cFree8,N'') = IsNull(UnitCostMassRollValue.cFree8,N'')
And IsNull(IA_MaxMinCostDif.cFree9,N'') = IsNull(UnitCostMassRollValue.cFree9,N'') And IsNull(IA_MaxMinCostDif.cFree10,N'') = IsNull(UnitCostMassRollValue.cFree10,N'')
--控制最大最小单价
if @cMaxMinCost = N'最高最低单价'
begin
--处理最高单价
Update UnitCostMassRollValue set Price = iMaxPrice
From UnitCostMassRollValue where Price > isnull(iMaxPrice,Price)
--处理最低单价
Update UnitCostMassRollValue set Price = iMinPrice
From UnitCostMassRollValue where Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'参考成本'
begin
--处理最高/最低单价
Update UnitCostMassRollValue set Price = Isnull(iInvSPrice, 0)
From UnitCostMassRollValue Where Price > isnull(iMaxPrice,Price) Or Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'上次出库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iOutCost from IA_Subsidiary
where bRdFlag=0 and cVouType<> N'33' and cInvCode= @cInvCode And not iOutCost is null
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
if @cMaxMinCost = N'上次入库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iInCost from IA_Subsidiary
where bRdFlag=1 and cVouType<> N'33' and cInvCode= @cInvCode And not iInCost is null
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
end
if @cAccount = N'按仓库核算'
begin
Update UnitCostMassRollValue set iMaxPrice = IA_MaxMinCostDif.MaxCost,iMinPrice = IA_MaxMinCostDif.MinCost
From UnitCostMassRollValue
Left Join IA_MaxMinCostDif On UnitCostMassRollValue.cInvCode = IA_MaxMinCostDif.cInvCode
And (UnitCostMassRollValue.cWhDepCode = IsNull(ia_maxmincostdif.cwhdepcode, N'') Or IsNull(ia_maxmincostdif.cWhDepCode, N'') = N'')
And IsNull(IA_MaxMinCostDif.cFree1,N'') = IsNull(UnitCostMassRollValue.cFree1,N'') And IsNull(IA_MaxMinCostDif.cFree2,N'') = IsNull(UnitCostMassRollValue.cFree2,N'')
And IsNull(IA_MaxMinCostDif.cFree3,N'') = IsNull(UnitCostMassRollValue.cFree3,N'') And IsNull(IA_MaxMinCostDif.cFree4,N'') = IsNull(UnitCostMassRollValue.cFree4,N'')
And IsNull(IA_MaxMinCostDif.cFree5,N'') = IsNull(UnitCostMassRollValue.cFree5,N'') And IsNull(IA_MaxMinCostDif.cFree6,N'') = IsNull(UnitCostMassRollValue.cFree6,N'')
And IsNull(IA_MaxMinCostDif.cFree7,N'') = IsNull(UnitCostMassRollValue.cFree7,N'') And IsNull(IA_MaxMinCostDif.cFree8,N'') = IsNull(UnitCostMassRollValue.cFree8,N'')
And IsNull(IA_MaxMinCostDif.cFree9,N'') = IsNull(UnitCostMassRollValue.cFree9,N'') And IsNull(IA_MaxMinCostDif.cFree10,N'') = IsNull(UnitCostMassRollValue.cFree10,N'')
--控制最大最小单价
if @cMaxMinCost = N'最高最低单价'
begin
--处理最高单价
Update UnitCostMassRollValue set Price = iMaxPrice
From UnitCostMassRollValue where Price > isnull(iMaxPrice,Price)
--处理最低单价
Update UnitCostMassRollValue set Price = iMinPrice
From UnitCostMassRollValue where Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'参考成本'
begin
--处理最高/最低单价
Update UnitCostMassRollValue set Price = Isnull(iInvSPrice, 0)
From UnitCostMassRollValue Where Price > isnull(iMaxPrice,Price) Or Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'上次出库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,cWhDepCode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where (Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)) and not cWhDepCode is null
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iOutCost from IA_Subsidiary
where bRdFlag=0 and cVouType<> N'33' and cInvCode= @cInvCode And not iOutCost is null and cWhCode=@cWhDepCode
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
if @cMaxMinCost = N'上次入库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,cWhDepCode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where (Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)) and not cWhDepCode is null
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iInCost from IA_Subsidiary
where bRdFlag=1 and cVouType<> N'33' and cInvCode= @cInvCode And not iInCost is null and cWhCode=@cWhDepCode
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
end
if @cAccount = N'按部门核算'
begin
Update UnitCostMassRollValue set iMaxPrice = IA_MaxMinCostDif.MaxCost,iMinPrice = IA_MaxMinCostDif.MinCost
From UnitCostMassRollValue
Left Join IA_MaxMinCostDif On UnitCostMassRollValue.cInvCode = IA_MaxMinCostDif.cInvCode
And (UnitCostMassRollValue.cWhDepCode = IsNull(ia_maxmincostdif.cwhdepcode, N'') Or IsNull(ia_maxmincostdif.cWhDepCode, N'') = N'')
And IsNull(IA_MaxMinCostDif.cFree1,N'') = IsNull(UnitCostMassRollValue.cFree1,N'') And IsNull(IA_MaxMinCostDif.cFree2,N'') = IsNull(UnitCostMassRollValue.cFree2,N'')
And IsNull(IA_MaxMinCostDif.cFree3,N'') = IsNull(UnitCostMassRollValue.cFree3,N'') And IsNull(IA_MaxMinCostDif.cFree4,N'') = IsNull(UnitCostMassRollValue.cFree4,N'')
And IsNull(IA_MaxMinCostDif.cFree5,N'') = IsNull(UnitCostMassRollValue.cFree5,N'') And IsNull(IA_MaxMinCostDif.cFree6,N'') = IsNull(UnitCostMassRollValue.cFree6,N'')
And IsNull(IA_MaxMinCostDif.cFree7,N'') = IsNull(UnitCostMassRollValue.cFree7,N'') And IsNull(IA_MaxMinCostDif.cFree8,N'') = IsNull(UnitCostMassRollValue.cFree8,N'')
And IsNull(IA_MaxMinCostDif.cFree9,N'') = IsNull(UnitCostMassRollValue.cFree9,N'') And IsNull(IA_MaxMinCostDif.cFree10,N'') = IsNull(UnitCostMassRollValue.cFree10,N'')
--控制最大最小单价
if @cMaxMinCost = N'最高最低单价'
begin
--处理最高单价
Update UnitCostMassRollValue set Price = iMaxPrice
From UnitCostMassRollValue where Price > isnull(iMaxPrice,Price)
--处理最低单价
Update UnitCostMassRollValue set Price = iMinPrice
From UnitCostMassRollValue where Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'参考成本'
begin
--处理最高/最低单价
Update UnitCostMassRollValue set Price = Isnull(iInvSPrice, 0)
From UnitCostMassRollValue Where Price > isnull(iMaxPrice,Price) Or Price < isnull(iMinPrice,Price)
end
if @cMaxMinCost = N'上次出库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,cWhDepCode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where (Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)) and not cWhDepCode is null
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iOutCost from IA_Subsidiary
where bRdFlag=0 and cVouType<> N'33' and cInvCode= @cInvCode And not iOutCost is null and cDepCode=@cWhDepCode
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
if @cMaxMinCost = N'上次入库成本'
begin
--处理最高/最低单价
declare curMonthcost cursor for
Select AutoID,cinvcode,cWhDepCode,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]
From UnitCostMassRollValue
Where (Price > IsNull(iMaxPrice,Price) Or Price < IsNull(iMinPrice,Price)) and not cWhDepCode is null
open curMonthcost
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
Update UnitCostMassRollValue Set Price = (Select Top 1 iInCost from IA_Subsidiary
where bRdFlag=1 and cVouType<> N'33' and cInvCode= @cInvCode And not iInCost is null and cDepCode=@cWhDepCode
And Isnull(@cFree1,N'') = IsNull(IA_Subsidiary.cFree1,N'') And Isnull(@cFree2,N'') = IsNull(IA_Subsidiary.cFree2,N'')
And Isnull(@cFree3,N'') = IsNull(IA_Subsidiary.cFree3,N'') And Isnull(@cFree4,N'') = IsNull(IA_Subsidiary.cFree4,N'')
And Isnull(@cFree5,N'') = IsNull(IA_Subsidiary.cFree5,N'') And Isnull(@cFree6,N'') = IsNull(IA_Subsidiary.cFree6,N'')
And Isnull(@cFree7,N'') = IsNull(IA_Subsidiary.cFree7,N'') And Isnull(@cFree8,N'') = IsNull(IA_Subsidiary.cFree8,N'')
And Isnull(@cFree9,N'') = IsNull(IA_Subsidiary.cFree9,N'') And Isnull(@cFree10,N'') = IsNull(IA_Subsidiary.cFree10,N'')
And Left(cBusType, 2) <> N'直运' Order By IsNull(BakID, AutoID) desc) where AutoID=@AutoID
fetch next from curMonthcost into @AutoID,@cInvCode,@cWhDepCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curMonthcost
deallocate curMonthcost
end
end
end
else
begin
Update UnitCostMassRollValue set iMaxPrice = null,iMinPrice = null
end
--处理最大最小单价没有指定单价的情况
Declare curNoCostRds cursor for
Select AutoID, cInvCode, cWhDepCode, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10
From UnitCostMassRollValue where Price is null
open curNoCostRds
fetch next from curNoCostRds into @AutoID,@cInvCode,@cWhDepCode,@cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
while @@FETCH_STATUS = 0
begin
exec IA_sp_GetOptionCost @imonth,@cWhDepCode,@cInvCode,@cAccount,0,@cZero,@iCost out,1,@cFree1,@cFree2,@cFree3,@cFree4,@cFree5,
@cFree6,@cFree7,@cFree8,@cFree9,@cFree10,2
Update UnitCostMassRollValue Set Price = @iCost where AutoID=@AutoID
fetch next from curNoCostRds into @AutoID,@cInvCode,@cWhDepCode, @cFree1, @cFree2, @cFree3, @cFree4, @cFree5, @cFree6, @cFree7, @cFree8, @cFree9, @cFree10
end
Close curNoCostRds
deallocate curNoCostRds
if @bRollCall = 1
begin
Update UnitCostMassRollValue set Price = Isnull(iInvSPrice, 0) From UnitCostMassRollValue
where Price is null
end
GO
第二步：
declare @iMonth int
set @iMonth =11----------“需要检查的月份”
if exists(select Name from sysobjects where name = 'My_PerInv') Drop table My_PerInv
if exists(select Name from sysobjects where name = 'My_PerWhDep') Drop table My_PerWhDep
if exists(select Name from sysobjects where name = 'My_PerSummary') Drop table My_PerSummary
if exists(select Name from sysobjects where name = 'My_PerValue') Drop table My_PerValue
select distinct cInvcode Into My_PerInv from ia_summary where imonth = @iMonth
and isnull(ionum,0)<>0
select distinct IsNull(cWhCode,cDepCode) As cWhDepCode Into My_PerWhDep from ia_summary where imonth = @iMonth
and isnull(ionum,0)<>0
select * into My_PerSummary From Ia_summary Where iMonth = @iMonth And iPeriod = 1
update My_PerSummary Set iPeriod = 0
Exec dbo.IA_sp_CalcMyPrice @iMonth,'demo', 0,''
select Ia_Summary.iunitprice As iOldCost, UnitCostMassRollValue.Price As iNewCost, Ia_subsidiary.AutoID As AutoID Into My_PerValue from UnitCostMassRollValue
left join ia_summary on UnitCostMassRollValue.cinvcode= ia_summary.cinvcode And UnitCostMassRollValue.cWhDepCode= ia_summary.cwhCode
Left Join Ia_Subsidiary On Ia_Subsidiary.cInvCode = ia_Summary.cInvCode And Ia_Subsidiary.cWhCode = Ia_summary.cWhcode And ia_subsidiary.imonth = @iMonth
Where Ia_summary.iMonth = @iMonth And Abs(Ia_Summary.iunitprice - UnitCostMassRollValue.Price)>0.01
And Ia_subsidiary.iMonth = @iMonth And IA_Subsidiary.bMoneyFlag = 0 And Ia_Subsidiary.bRdFlag = 0
and UnitCostMassRollValue.Price <>0
if exists(select Name from sysobjects where name = 'My_PerInv') Drop table My_PerInv
if exists(select Name from sysobjects where name = 'My_PerWhDep') Drop table My_PerWhDep
if exists(select Name from sysobjects where name = 'My_PerSummary') Drop table My_PerSummary
select cVouCode As [单据号], dVouDate As [单据日期], dKeepDate As [记账日期], cWhCode As [仓库编码], cInvCode As [存货编码],
IaOutQuantity As [出库数量], IaOutPrice As [出库金额], Cast(IaOutQuantity * iNewCost As Decimal(34,2)) as [应该出库金额], Cast(IaOutQuantity * iNewCost As Decimal(34,2))- IaOutPrice As [调整单金额] from My_PerValue
Left Join Ia_subsidiary On My_PerValue.AutoID = Ia_subsidiary.AutoID
问题二：平均单价回写
问题现象：期末处理后平均单价没有回写到对应的单据
解决方案：
1、更新补丁后恢复记帐，恢复期末初期并重新期末处理记帐（推荐解决方案）
2、如果已经出了报表不能重新处理的客户可以考虑在本月份进行调整平均单价计算错误带来的影响。具体方案如下：
注意事项：只适用于按仓库核算，以下脚本只回写领料单单价，其它类型可以变通处理或者发现后再沟通。
Declare @sSql nvarchar(4000)
Declare @imonth integer
Declare @cDate nVarchar(10)
Set @imonth = 11
Set @cDate = '2006-11-30'
Update RdRecords Set iUnitCost= IA_Subsidiary.iOutCost,iPrice=round(iAOutPrice,2) From RdRecords
Left Join IA_Subsidiary On RdRecords.AutoID=IA_Subsidiary.ID
Left Join Warehouse On Warehouse.cWhCode = IA_Subsidiary.cWhCode
Inner Join Ia_Summary
On Ia_summary.cInvCode = ia_subsidiary.cInvCode And IsNull(Ia_summary.iDirect,0) = 0 And Ia_summary.iMonth = @imonth
And Ia_summary.cWhCode = IA_Subsidiary.cWhCode
And (IsNull(Ia_summary.cFree1, N'') = IsNull(ia_subsidiary.cFree1, N'') Or IsNull(Ia_summary.cFree1, N'') = N'') And (IsNull(Ia_summary.cFree2, N'') = IsNull(ia_subsidiary.cFree2, N'') Or IsNull(Ia_summary.cFree2, N'') = N'')
And (IsNull(Ia_summary.cFree3, N'') = IsNull(ia_subsidiary.cFree3, N'') Or IsNull(Ia_summary.cFree3, N'') = N'') And (IsNull(Ia_summary.cFree4, N'') = IsNull(ia_subsidiary.cFree4, N'') Or IsNull(Ia_summary.cFree4, N'') = N'')
And (IsNull(Ia_summary.cFree5, N'') = IsNull(ia_subsidiary.cFree5, N'') Or IsNull(Ia_summary.cFree5, N'') = N'') And (IsNull(Ia_summary.cFree6, N'') = IsNull(ia_subsidiary.cFree6, N'') Or IsNull(Ia_summary.cFree6, N'') = N'')
And (IsNull(Ia_summary.cFree7, N'') = IsNull(ia_subsidiary.cFree7, N'') Or IsNull(Ia_summary.cFree7, N'') = N'') And (IsNull(Ia_summary.cFree8, N'') = IsNull(ia_subsidiary.cFree8, N'') Or IsNull(Ia_summary.cFree8, N'') = N'')
And (IsNull(Ia_summary.cFree9, N'') = IsNull(ia_subsidiary.cFree9, N'') Or IsNull(Ia_summary.cFree9, N'') = N'') And (IsNull(Ia_summary.cFree10, N'') = IsNull(ia_subsidiary.cFree10, N'') Or IsNull(Ia_summary.cFree10, N'') = N'')
Where Warehouse.cWhValueStyle = N'全月平均法' And IA_Subsidiary.iMonth = @imonth AND bRdFlag = 0
And IA_Subsidiary.bMoneyFlag = 0 And Not IA_Subsidiary.cVouType in (N'25',N'26',N'27',N'28',N'29',N'6',N'5',N'05',N'06',N'70')
--增加调用委外接口
if exists(Select Top 1 IA_Subsidiary.ID From IA_Subsidiary
Left Join Warehouse On Warehouse.cWhCode = IA_Subsidiary.cWhCode
Inner Join Ia_Summary
On Ia_summary.cInvCode = ia_subsidiary.cInvCode And IsNull(Ia_summary.iDirect,0) = 0 And Ia_summary.iMonth = @imonth
And Ia_summary.cWhCode = IA_Subsidiary.cWhCode
And (IsNull(Ia_summary.cFree1, N'') = IsNull(ia_subsidiary.cFree1, N'') Or IsNull(Ia_summary.cFree1, N'') = N'') And (IsNull(Ia_summary.cFree2, N'') = IsNull(ia_subsidiary.cFree2, N'') Or IsNull(Ia_summary.cFree2, N'') = N'')
And (IsNull(Ia_summary.cFree3, N'') = IsNull(ia_subsidiary.cFree3, N'') Or IsNull(Ia_summary.cFree3, N'') = N'') And (IsNull(Ia_summary.cFree4, N'') = IsNull(ia_subsidiary.cFree4, N'') Or IsNull(Ia_summary.cFree4, N'') = N'')
And (IsNull(Ia_summary.cFree5, N'') = IsNull(ia_subsidiary.cFree5, N'') Or IsNull(Ia_summary.cFree5, N'') = N'') And (IsNull(Ia_summary.cFree6, N'') = IsNull(ia_subsidiary.cFree6, N'') Or IsNull(Ia_summary.cFree6, N'') = N'')
And (IsNull(Ia_summary.cFree7, N'') = IsNull(ia_subsidiary.cFree7, N'') Or IsNull(Ia_summary.cFree7, N'') = N'') And (IsNull(Ia_summary.cFree8, N'') = IsNull(ia_subsidiary.cFree8, N'') Or IsNull(Ia_summary.cFree8, N'') = N'')
And (IsNull(Ia_summary.cFree9, N'') = IsNull(ia_subsidiary.cFree9, N'') Or IsNull(Ia_summary.cFree9, N'') = N'') And (IsNull(Ia_summary.cFree10, N'') = IsNull(ia_subsidiary.cFree10, N'') Or IsNull(Ia_summary.cFree10, N'') = N'')
Where Warehouse.cWhValueStyle = N'全月平均法' And IA_Subsidiary.iMonth = @imonth AND bRdFlag = 0
And IA_Subsidiary.bMoneyFlag = 0 And IA_Subsidiary.cVouType=N'11' And Left(cBusType,2) = N'委外')
begin
Set @sSql = N'Select IA_Subsidiary.ID as autoid From IA_Subsidiary
Left Join Warehouse On Warehouse.cWhCode = IA_Subsidiary.cWhCode
Inner Join Ia_Summary
On Ia_summary.cInvCode = IA_Subsidiary.cInvCode And IsNull(Ia_summary.iDirect,0) = 0 And Ia_summary.iMonth = '+str(@imonth)+ N'
And Ia_summary.cWhCode = IA_Subsidiary.cWhCode
And (IsNull(Ia_summary.cFree1, N'''') = IsNull(ia_subsidiary.cFree1, N'''') Or IsNull(Ia_summary.cFree1, N'''') = N'''') And (IsNull(Ia_summary.cFree2, N'''') = IsNull(ia_subsidiary.cFree2, N'''') Or IsNull(Ia_summary.cFree2, N'''') = N'''')
And (IsNull(Ia_summary.cFree3, N'''') = IsNull(ia_subsidiary.cFree3, N'''') Or IsNull(Ia_summary.cFree3, N'''') = N'''') And (IsNull(Ia_summary.cFree4, N'''') = IsNull(ia_subsidiary.cFree4, N'''') Or IsNull(Ia_summary.cFree4, N'''') = N'''')
And (IsNull(Ia_summary.cFree5, N'''') = IsNull(ia_subsidiary.cFree5, N'''') Or IsNull(Ia_summary.cFree5, N'''') = N'''') And (IsNull(Ia_summary.cFree6, N'''') = IsNull(ia_subsidiary.cFree6, N'''') Or IsNull(Ia_summary.cFree6, N'''') = N'''')
And (IsNull(Ia_summary.cFree7, N'''') = IsNull(ia_subsidiary.cFree7, N'''') Or IsNull(Ia_summary.cFree7, N'''') = N'''') And (IsNull(Ia_summary.cFree8, N'''') = IsNull(ia_subsidiary.cFree8, N'''') Or IsNull(Ia_summary.cFree8, N'''') = N'''')
And (IsNull(Ia_summary.cFree9, N'''') = IsNull(ia_subsidiary.cFree9, N'''') Or IsNull(Ia_summary.cFree9, N'''') = N'''') And (IsNull(Ia_summary.cFree10, N'''') = IsNull(ia_subsidiary.cFree10, N'''') Or IsNull(Ia_summary.cFree10, N'''') = N'''')
Where Warehouse.cWhValueStyle = N''全月平均法'' And IA_Subsidiary.iMonth = '+str(@imonth)+ N' AND bRdFlag = 0
And IA_Subsidiary.bMoneyFlag = 0 And IA_Subsidiary.cVouType =N''11''And Left(cBusType,2) = N''委外'''
Exec sp_BatchSettleMaterialCost_OM 0, 4, 4, 4, @cDate, @sSql, N''
end
Set @sSql = N'Left Join IA_Subsidiary On RdRecords.AutoID=IA_Subsidiary.ID
Left Join Warehouse On Warehouse.cWhCode = IA_Subsidiary.cWhCode
Where Warehouse.cWhValueStyle = N''全月平均法'' And IA_Subsidiary.iMonth = ' + Cast(@imonth as nvarchar(3)) + N'AND IA_Subsidiary.bRdFlag = 1
And IA_Subsidiary.bMoneyFlag = 0
And Not IA_Subsidiary.cVouType in (N''25'',N''26'',N''27'',N''28'',N''29'',N''6'',N''5'',N''05'',N''06'',N''ex26'',N''ex27'',N''70'')'
Exec Pu_PWBRkdCostPrice @sSql, 4,4,4,''
问题三：其他问题
问题现象：上月期末结存金额和本月期初金额不一致，造成呢本月平均单价结算结果异常。
解决方案：
1、更新补丁后恢复记帐，恢复期末初期并重新期末处理记帐（推荐解决方案）
2、如果已经出了报表不能重新处理的客户可以检查出此类现象的影响程度，如果数量不大可以直接调整存货总帐。具体方案如下：
由于其它原因，可能是业务未做完或者非正常流程进行了月末结帐等造成上月期末结存金额和本月期初金额不一致问题，这个问题主要是存货总帐数据结转不正确（可能是业务原因），将直接影响本月平均单价计算和结存，这个问题由于不能确认真正的原因，现在没有较好的解决办法。建议通过以下办法变通解决:
1、 首先查出有问题的单据：
Select B.iMOney,A.imoney,a.iimoney,a.iomoney,*
FRom Ia_Summary A
Left Join Ia_summary B ON A.cInvCode = b.cInvcode And A.cWhcode = b.cwhcode
Where a.imonth = 12 and b.imonth = 11
And A.iMoney - Isnull(A.iImoney.0) + IsNull(a.iomoney,0) <> b.imoney
2、可以根据查询出来的imoney值，本月没有入库发生的单据可以在存货总帐中将上月的结存单价（imoney）直接调整到本月在进行期末处理和记帐，系统将直接调整本月平均单价；如有本月有差异，请根据查询出来的差异金额直接进行调整，调整完后再期末处理正常。
以上是根据最近发现的有限的几个问题总结的解决办法，如果有新现象和问题请尽快反映，以便及时提供解决方案。
注意事项
本方案中所有非推荐解决方案均为参考方案，本站不保证其有效性和安全性，请谨慎操作。如果您不是技术人员，欢迎致电烟台用友畅源软件需求援助。