問題一：平均單價計算錯誤 問題現象：平均單價明顯不正確
解決方案： 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
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）直接調整到本月在進行期末處理和記帳，系統將直接調整本月平均單價；如有本月有差異，請根據查詢出來的差異金額直接進行調整，調整完后再期末處理正常。