数据库升级失败！SQL文件不全或SQL语句有误!
错误描述:列名 'FBrNo' 无效。
Sql文件名:C:\Program Files (x86)\Kingdee\KIS\Commerce\Standard\SqlSrv\KISV10_Public_Data.sql
Sql内容:Create PROCEDURE StockINIDataOn
AS
DECLARE
@pPeriodTmp VarChar(255),
@Period Int, --当前会计期间
@Ptype VarChar(255),
@CurYear Int, --当前年份
@CalType Int, --核算方式
@CurStartDate Datetime,
@CurEndDate Datetime,
@BillNo VARCHAR(255) --单据号码
DECLARE @AuditChoice INT ---核算方式，0--数量核算，1--数量、金额核算
SET @AuditChoice=0
SELECT @CurYear =CONVERT(int,Fvalue) FROM t_SystemProfile WHERE FCategory='IC' AND FKey='CurrentYear'
If @CurYear=Null
BEGIN
Raiserror('~$~系统参数: 《IC--CurrentYear》 数据被破坏^|^！',16,1)
Return 0
END
exec GetSysPrm 'IC','CurrentPeriod',@pPeriodTmp OutPut,@pType OutPut
Select @Period=Convert(Int,@pPeriodTmp)
SELECT @CalType =CONVERT(int,Fvalue) FROM t_SystemProfile WHERE FCategory='IC' AND FKey='CalculateType'
If @CalType=Null
BEGIN
Raiserror('~$~系统参数: 《IC--CalculateType 数据被破坏^|^！',16,1)
Return 0
END
IF @CalType=1
BEGIN
Update t_Stock set FGroupID=FItemID where FTypeID In (500,20291,20293)
END
IF @CalType=0
BEGIN
Update t_Stock set FGroupID=0 where FTypeID In (500,20291,20293)
END
--当前会计期间的起止日期
exec GetPeriodStartEnd 0,@Period,@CurStartDate output,@CurEndDate output
If (Select count(*) From (Select Distinct FPeriod From ICInvInitial) as t1)=1
Update ICInvInitial Set FPeriod=@Period
Else If (Select count(*) From (Select Distinct FPeriod From ICInvInitial) as t1)>1
BEGIN
Raiserror('~$~系统提示: 系统初始化表《ICInvInitial》含有多期数据，不符合启用条件^|^！',16,1)
Return 0
END
--TRUNCATE TABLE ICInvBal
TRUNCATE TABLE T_CC_Bal
--加入库存余额表,合并顺序号--
/*JXP--20070614--现在没有这个表了
INSERT INTO ICInvBal(FBrNo,Fyear,Fperiod,FStockID,FStockPlaceID,FItemID,FBatchNo,
FBegQty,Freceive,Fsend,FYtdReceive,FYtdSend,FEndQty,
FBegBal,Fdebit,Fcredit,FYtdDebit,FYtdCredit,FEndBal,
FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff,
FKFDate,FKFPeriod,FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate)
-- FBillInterID)
SELECT '0',@CurYear,@Period,FStockID,FSPID,FItemID,FBatchNo,
Sum(FBegQty),Sum(Freceive),Sum(Fsend),Sum(FYtdReceive),Sum(FYtdSend),Sum(FEndQty),
Sum(FBegBal),Sum(Fdebit),Sum(Fcredit),Sum(FYtdDebit),Sum(FYtdCredit),Sum(FEndBal),
Sum(FBegDiff),Sum(FReceiveDiff),Sum(FSendDiff),Sum(FYtdReceiveDiff),Sum(FYtdSendDiff),
FKFDate,FKFPeriod,FAuxPropID,Sum(FAuxUnitBegQty),Sum(FYtdAuxUnitSend),Sum(FYtdAuxUnitReceive),FStockInDate
-- FBillInterID
FROM ICInvInitial WHERE FPeriod=@Period
GROUP BY FStockID,FSPID,FItemID,FBatchNo,FKFDate,FKFPeriod,FAuxPropID,FStockInDate
-- FBillInterID
*/
--根据核算的最新算法，不再需要区分物料的计价方法来考虑批次、顺序号的合并
INSERT INTO T_CC_Bal
(
FBrNo,FYear,FPeriod,FItemID,FBatchNo,FStockID,
FBegQty,FReceive,FSend,FYtdReceive,FYtdSend,FEndQty,
FBegBal,FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,
FBegDiff,FReceiveDiff,FSendDiff,FYtdReceiveDiff,FYtdSendDiff,
FBillInterID,FAuxPropID,FSecBegQty,FSecYtdSend,FSecYtdReceive,FStockInDate,
FKFPeriod,FKFDate,FPeriodDate
)
SELECT
'0',@CurYear,@Period,t1.FItemID,t1.FBatchNo,t1.FStockID,
Sum(t1.FQTY),0,0,0,0,0,
Sum(t1.FAmount),0,0,0,0,0,
0,0,0,0,0,
t1.FBillInterID,t1.FAuxPropID,Sum(t1.FSecQTY),0,0,t1.FStockInDate,
t3.FKFPeriod,t1.FKFDate,t1.FDestDate
FROM ICInvInitial t1,t_Stock t2 ,t_icitem t3
WHERE t1.FItemID=t3.FItemID and t1.FStockID=t2.FItemID And t1.FPeriod=@Period and t1.FQTY<>0 And t2.FTypeID<>502
GROUP BY t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FBillInterID,t1.FAuxPropID,t1.FStockInDate,t3.FKFPeriod,t1.FKFDate,t1.FDestDate
--期初数据入核算加速表,移动平均物料
If @CalType = 0
Begin
Insert Into ICSpeedBal(FDay,FTableName,FClassTypeID,FBillTypeID,FID,FEntryID,FStockID,FItemID,FQty,FAmount)
Select 0,'',0,0,0,0,0,b.FItemID,Sum(b.FBegQty),Sum(b.FBegBal)
From t_CC_Bal b Inner Join t_ICItem i On b.FItemID=i.FItemID And i.FTrack=77
--Inner Join t_Stock s On b.FStockID=s.FItemID And s.FTypeID<>502
Group By b.FItemID
End
Else
Begin
Insert Into ICSpeedBal(FDay,FTableName,FClassTypeID,FBillTypeID,FID,FEntryID,FStockID,FItemID,FQty,FAmount)
Select 0,'',0,0,0,0,b.FStockID,b.FItemID,b.FBegQty,b.FBegBal
From t_CC_Bal b Inner Join t_ICItem i On b.FItemID=i.FItemID And i.FTrack=77
--Inner Join t_Stock s On b.FStockID=s.FItemID And s.FTypeID<>502
End
--将数据加入即时库存表--
Truncate table t_CC_Inventory
INSERT INTO t_CC_Inventory
(
FBrNo,FItemID,FBatchNo,FStockID,FQty,FBal,
FKFDate,FKFPeriod,FAuxPropID,FSecQty,FPeriodDate
)
SELECT
'0',t1.FItemID,t1.FBatchNo,t1.FStockID,Sum(FQty),Sum(FAmount),
t1.FKFDate,t2.FKFPeriod,t1.FAuxPropID,Sum(FSecQTY),t1.FDestDate
FROM ICInvInitial t1 inner join t_ICItem t2 on t1.FItemID=t2.FItemID
WHERE t1.FPeriod=@Period and t1.FQTY<>0
Group By t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FKFDate,t2.FKFPeriod,t1.FAuxPropID,t1.FDestDate
--更新库存表 rickey 2007.07.23
INSERT INTO t_cc_stock
(
FYear,FPeriod,FStockID,FItemID,FBatchNo,FAuxPropID,FBegQty,FSecBegQty,
FBegBal,FReceive,FSecReceive,FDebit,FSend,FSecSend,FCredit,FKFPeriod,FKFDate,FPeriodDate
)
SELECT
@CurYear,@Period,t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FAuxPropID,
Sum(t1.FQTY),Sum(t1.FSecQTY),Sum(t1.FAmount),0,0,0,0,0,0,t3.FKFPeriod,t1.FKFDate,t1.FDestDate
FROM ICInvInitial t1,t_Stock t2,t_icitem t3
WHERE t1.FItemID=t3.FItemID and t1.FStockID=t2.FItemID And t1.FPeriod=@Period and t1.FQTY<>0
GROUP BY t1.FStockID,t1.FItemID,t1.FBatchNo,t1.FAuxPropID,t3.FKFPeriod,t1.FKFDate,t1.FDestDate
--备份初始化数据--
--Update ICInvInitial Set FPeriod=0
Update t_SystemProFile Set FValue='0' Where FCategory='IC' And FKey='InvDataPeriod'
Update t_SystemProFile Set FValue='1' Where FCategory='IC' And FKey='ICClosed'
Update t_SystemProFile Set FValue='1' Where FCategory='ARAP' And FKey='Closed'
--加入应收应付启用期间--JXP--20060831--Begin
Update t_SystemProFile Set FValue=(select FValue from t_SystemProfile Where FCategory='IC' And FKey='StartYear') Where FCategory='ArAp' And FKey='StartYear'
Update t_SystemProFile Set FValue=(select FValue from t_SystemProfile Where FCategory='IC' And FKey='StartPeriod') Where FCategory='ArAp' And FKey='StartPeriod'
--加入应收应付启用期间--JXP--20060831--End
--处理启用前的单据
Update t_CC_StockBill Set FClassTypeID=1020003 Where FClassTypeID=1080001
Update t_CC_StockBill Set FClassTypeID=1030003 Where FClassTypeID=1080002
Update t_SearchInfo
Set FSearchText=replace(FSearchText,'启用期前的','')
Update t_SearchInfo
Set Fkey=replace(FKey,'1080001','1020003')
where Fkey Like '%1080001%'
Update t_SearchInfo
Set Fkey=replace(FKey,'1080002','1030003')
Where Fkey Like '%1080002%'
Return 1
推荐阅读