u870升级U8 10.1报错

x417292941

数据库名: UFDATA_004_2005
C:\U8SOFT\Admin\SQLFILE8710\Main\Ufdata\Structure\Data_PRP_CA_mix_NL.sql
错误信息:
-2147217900
列名 'irate' 无效。

执行如下语句时出错:


CREATE PROCEDURE CaP_CalPrepare
(
@dMaxDate nvarchar(12),                --计算期间的最大日
@dMinDate nvarchar(12),                --计算期间的最小日
@iPeriod  int

kevin_1218

ca_enmop,ca_compd表中加入irate字段

x417292941

AS
        -- 工时
        insert into CA_timst(cppid,cbatch,irealwkt,iperiod)
                select B.irealcoid,B.cbatch,isnull(sum(A.irealwkt),0) as irealwkt,@iPeriod from ca_batchmap_cur B
                inner join CA_daytis A on A.cppid = B.irealcoid and A.cbatch = B.cbatch
                where A.ddate>=@dMinDate and A.ddate<=@dMaxDate
                group by B.irealcoid,B.cbatch;

        insert into CA_timst(cppid,cbatch,irealwkt,iperiod)
                select B.irealcoid,B.cbatch,0,@iPeriod from ca_batchmap_cur B
                left join CA_timst A on A.cppid = B.irealcoid and A.cbatch = B.cbatch and A.iperiod = @iPeriod
                Where A.cPPID Is Null;
       
        -- 完工
        insert into CA_comps(cdeptid,cppid,cbatch,iwastequa,iqua,iperiod,wasteflag)
                select B.cdeptid,B.irealcoid,B.cbatch,isnull(sum(A.iwastequa),0) as iwastequa,isnull(sum(A.iqua),0) as iqua,@iPeriod,0 from ca_batchmap_cur B
                inner join CA_dacps A on A.cppid = B.irealcoid and A.cbatch = B.cbatch
                where A.ddate>=@dMinDate and A.ddate<=@dMaxDate
                group by B.cdeptid,B.irealcoid , B.cbatch;
       
        insert into CA_comps(cdeptid,cppid,cbatch,iwastequa,iqua,iperiod,wasteflag)
                select B.cdeptid,B.irealcoid,B.cbatch,0,0,@iPeriod,0 from ca_batchmap_cur B
                left join CA_comps A on A.cppid = B.irealcoid and A.cbatch = B.cbatch and A.iperiod = @iperiod
                Where A.cPPID Is Null;
       
        -- 耗用
        insert into CA_propu(cppid,cbatch,cuseppid,iqua,iperiod)
                select A.cPPID,A.cBatch,A.cuseppid,sum(A.iqua) as iqua,@iPeriod from CA_ppudr A
                inner join ca_batchmap_cur B on A.cppid = B.irealcoid and A.cbatch = B.cbatch
                inner join (select distinct irealcoid,ibomtype from ca_batchmap_cur) C on A.cppid = C.irealcoid
                where A.ddate>=@dMinDate and A.ddate<=@dMaxDate and isnull(iqua,0) <> 0 and A.cppid <> A.cuseppid
                group by a.cPPID , a.cBatch, a.cuseppid;

        -- 盘点
        insert into ca_enmop(cppid,cbatch,ionpdtamo,ionpdtamo_auxi,irate,ifinpdtamo,ifinpdtamo_auxi,ionpwkt,iperiod,cunit)
                select B.irealcoid,B.cbatch,0,0,1,0,0,0,@iPeriod,'' from ca_batchmap_cur B
                left join ca_enmop A on A.cppid = B.irealcoid and A.cbatch = B.cbatch and A.iperiod = @iPeriod
                Where A.cPPID Is Null;

        insert into ca_enmop(cppid,cbatch,ionpdtamo,ionpdtamo_auxi,irate,ifinpdtamo,ifinpdtamo_auxi,ionpwkt,iperiod,cunit)
                select B.irealcoid,B.cbatch,0,0,1,0,0,0,@iPeriod-1,'' from ca_batchmap_cur B
                left join ca_enmop A on A.cppid = B.irealcoid and A.cbatch = B.cbatch and A.iperiod = @iPeriod - 1
                Where A.cPPID Is Null;
       
        --处理在产品盘点表取数方式切换问题
        if dbo.caf_getaccinfo('ca','8602','0') = 1
        begin
                --更新盘点表上道工序累计完工数
                update A set A.iTotalFinQty = isnull(T.iTotalFinQty,0) from ca_enmop A inner join
                (
                        select B.curid,sum(isnull(C.iintoamo,0)) inputqua, Sum(case when D.lastFinQty = 0 then isnull(E.iSumintoAmo,0) + isnull(F.iBeginFinQty,0) else isnull(C.iintoamo,0) + isnull(D.lastFinQty,0) end) totalin,
                        Sum(case when D.lastFinQty = 0 then isnull(E.iSumintoAmo,0) + isnull(F.iBeginFinQty,0) else isnull(C.iintoamo,0) + isnull(D.lastFinQty,0) end) iTotalFinQty from
                        (select pid curid ,cid previd from ca_realco_rel where reltype = 4 and pid <> cid) B
                        left join (select cppid,sum(isnull(iqua,0)) - sum(isnull(iwastequa,0)) as iintoamo from ca_dacps where ddate <= @dMaxDate And ddate >= @dMinDate group by cppid) C on B.previd = C.cppid
                        left join (select cppid,isnull(iTotalFinQty,0) as lastFinQty from ca_enmop where iperiod = @iPeriod - 1 ) D on B.curid = D.cppid
                        left join (select cppid,sum(isnull(iqua,0)) - sum(isnull(iwastequa,0)) as iSumintoAmo from ca_dacps where ddate <= @dMaxDate group by cppid) E on B.previd = E.cppid
                        left join (select cppid,isnull(iTotalFinQty,0) ibeginFinQty from ca_enmop where iPeriod = 0) F on B.Previd = F.cppid
                        inner join ca_batchmap_cur T on T.irealcoid = B.curid
                        Where T.ompflag = 0 group by B.curid
                ) T on A.cppid = T.curid
                inner join Ca_Batchmap_cur P on A.cppid = P.irealcoid and A.cBatch = P.cbatch
                Where A.iPeriod = @iPeriod and P.ompflag = 0
               
                --如果本道工序有委外，则自制工序累计投产需要减去委外工序的累计数
                update  A set A.iTotalFinQty = IsNull(A.iTotalFinQty, 0) - IsNull(B.iTotalFinQty, 0)
                From
                (
                    select A.irealcoid cppid,B.irealcoid ompcppid from ca_batchmap_cur A inner join ca_batchmap_cur B
                        on A.cMocode = B.cMocode and A.iMosubSn = B.iMoSubSn and A.iMoRouteSn = B.iMoRouteSn and A.iPartid = B.ipartid and A.cDeptid = B.cDeptid and A.ompflag = 0 and B.ompflag = 1
                ) T inner join ca_enmop A on A.cppid = T.cppid
                inner join ca_enmop B on B.cppid = T.ompCppid
                Where A.iPeriod = @iPeriod
               
                --更新盘点表首道工序最大套数
                update A set A.iTotalMaxUnitQty = B.iTotalMaxUnitQty from ca_Enmop A
                        inner join (Select cppid,cbatch,iTotalmaxUnitQty from ca_enmop where iTotalmaxUnitQty is not null and iPeriod = @iPeriod - 1) B
                        on A.cppid = B.cppid and A.cbatch = B.cbatch
                        where A.iTotalmaxUnitQty is null and iPeriod = @iPeriod
        end
       
        -- 完工处理
        insert into ca_compd(cppid,cbatch,ilosingincost,ilosingincost_auxi,irate,iperiod)
                select B.irealcoid,B.cbatch,0,0,1,@iPeriod from ca_batchmap_cur B
                left join ca_compd A on A.cppid = B.irealcoid and A.cbatch = B.cbatch and A.iperiod = @iperiod
                where A.cppid is null
       
        --更新累计完工数
        update A set A.iTotalQua = isnull(B.iQua,0) + isnull(C.ilastQua,0) from ca_compd A
                left join (Select cppid,cbatch,Sum(isnull(iQua,0)) - Sum(isnull(iWasteQua,0)) as iQua from ca_dacps where ddate <= @dMaxDate group by cppid,cbatch) B on A.cppid = B.cppid and A.cbatch = B.cbatch
                left join (Select cppid,cbatch,iTotalQua iLastQua from ca_compd where iPeriod = 0) C on A.cppid = C.cppid  and A.cbatch = C.cbatch
                where A.iPeriod = @iPeriod
       
        -- 约当
        insert into ca_onpqu(cppid)
                select A.ibasecoid from ca_cobase A
                left join ca_onpqu B on A.ibasecoid = B.cppid
                where B.cppid is null
        delete from ca_onpqu where cppid = -1
        insert into ca_onpqu(cppid) values(-1)

        insert into ca_onpce(cppid,cbatch,iperiod)
                select A.irealcoid,A.cbatch,@iperiod from ca_batchmap_cur A
                left join ca_onpce B on A.irealcoid = B.cppid and A.cbatch = B.cbatch and B.iperiod = @iperiod
                where B.cppid is null
       
     -- 如果在产品分配是按材料倒记,清空相应成本中心的ca_quo标志,
        update A set A.bchkflag = 0
                from ca_quoall A
                inner join ca_batchmap_cur B on A.cppid = B.irealcoid and A.cbatch = B.cbatch
                where A.bchkflag = 1 and isnull(A.iquo,0) > 0

        -- 删除为bchkflag=1并且没有录入定额的数据
--        delete from ca_quoall where bchkflag = 1
         -- 归集耗用品成本数据
         exec CaP_FillCurYearRoute
         
         -- 检查无效产品
         exec CaP_CheckClosedOrder

x417292941

求大家告知 我要改哪张表

x417292941

我顶…我顶…

x417292941

谢谢，我先去试下

x417292941

这个问题是解决了 ，但是后面还有一堆报错 。还是用升降工具先升到890吧