U8升级报错

249290043

-2147217900
列名 'autoid' 无效。

执行如下语句时出错:

Create PROCEDURE [dbo].[CaP_CalRollupMatWaste_Fx]
(
@iPeriod integer,              --计算期间
@iLevel integer,               --低阶码值
@bFirstCal bit,                --是否第一次计算成本
@bCalculate    bit,                           --是否计算
@bFXJZ bit,                                     --分项结转
@cErr nvarchar(1000)        out                   --错误信息
)
AS
begin
        if @bFXJZ=0 return

        declare @iBeginPeriod as integer
        Set @iBeginPeriod = Cast(@iPeriod/100 as integer) * 100


        --1.专用材料
        ----上期存料
        if @bFirstCal=1
        begin
                        truncate table ca_amoct_fx_temp_for_cal
                        insert into ca_amoct_fx_temp_for_cal(id,cPPID,cBatch,cAmotype,cAmoID,ifxDid,iQua,iTotalAmo)
                        select ID=A.autoid,A.cppid,A.cbatch,A.camotype,A.camoid,C.ifxDid,C.iQua,iTotalAmo=C.iOnpTotalAmo
                        from ca_amoct_fx C
                        inner join ca_costbom B on C.iperiod=@iBeginPeriod and C.camotype=0 and b.iLevel=@iLevel and C.cppid=b.icoid
                    inner join ca_amoct A on C.iperiod=@iBeginPeriod and A.iperiod=@iPeriod
                                                                            and A.cppid=C.cppid and A.cbatch=C.cbatch and A.camotype=C.camotype and A.camoid=C.camoid


                        update A set A.iQua=A.iQua+B.iQua,A.iTotalAmo=A.iTotalAmo+B.iTotalAmo,
                                A.lastperiodqua=B.iQua,A.lastperiodamo=B.iTotalAmo
                        from ca_amoct_fx A                       
                        inner join ca_amoct_fx_temp_for_cal B on A.iperiod=@iPeriod and A.cppid=B.cppid and A.cbatch=B.cbatch and A.camotype=B.camotype and A.camoid=B.camoid  and A.ifxDid=B.ifxDid
                       
                        insert into ca_amoct_fx(iperiod,id,cppid,cbatch,cAmoID,cAmotype,ifxDid,iQua,iTotalAmo,lastperiodqua,lastperiodamo)
                                select @iPeriod,B.ID,B.cppid,B.cbatch,B.cAmoID,B.cAmotype,B.ifxDid,B.iQua,B.iTotalAmo,B.iQua,B.iTotalAmo
                                from ca_amoct_fx_temp_for_cal B
                                left join ca_amoct_fx A on A.iperiod=@iPeriod and A.cppid=B.cppid and A.cbatch=B.cbatch and A.camotype=B.camotype and A.camoid=B.camoid and A.ifxDid=B.ifxDid
                                where A.cppid is null


        end
        else
        begin

                ------已存在的材料
                          --分项结转
                        truncate table ca_amoct_fx_temp_for_cal
                        insert into ca_amoct_fx_temp_for_cal(id,cPPID,cBatch,cAmotype,cAmoID,ifxDid,iQua,iTotalAmo)
                        select null,t1.cppid,t1.cbatch,0 as camotype,t1.cMatid,t1.ifxDid,iQua=isnull(t2.dBeginQ,0)+isnull(t3.dInQ,0)-isnull(t4.dOutQ,0),
                                                iTotalAmo=round(isnull(t2.dBeginA,0)+isnull(t3.dInA,0)-isnull(t4.dOutA,0),2)

                                from
                                (
                                        select distinct A.cppid,A.cbatch,A.cMatid,C.ifxDid
                                        from ca_moveEnmom a join ca_costbom b on a.cppid=b.iCOID
                                        inner join Ca_moveEnmom_Fx C on A.id=C.id
                                        where a.iperiod=@iPeriod-1 and b.iLevel=@iLevel
                                ) t1
                                left join
                                (
                                        select A.cppid,A.cbatch,A.cMatid,B.ifxDid,sum(B.iRestQua) as dBeginQ,sum(B.iRestQua*B.iPrice) as dBeginA
                                        From ca_moveEnmom A inner join Ca_moveEnmom_Fx B on A.id=B.id
                                        where A.iType=0 and A.iperiod=@iPeriod-1
                                        group by A.cppid,A.cbatch,A.cMatid,B.ifxDid
                                ) t2 on t1.cppid=t2.cppid and t1.cbatch=t2.cbatch and t1.cMatID=t2.cMatID and t1.ifxDid=t2.ifxDid
                                left join
                                (
                                        select  A.cppid,A.cbatch,A.cMatid,B.ifxDid,sum(B.iRestQua) as dInQ,sum(B.iRestQua*B.iPrice) as dInA
                                        From ca_moveEnmom A inner join Ca_moveEnmom_Fx B on A.id=B.id
                                        where A.iType=1 and A.iperiod=@iPeriod-1
                                        group by A.cppid,A.cbatch,A.cMatid,B.ifxDid
                                ) t3 on t1.cppid=t3.cppid and t1.cbatch=t3.cbatch and t1.cMatID=t3.cMatID and t1.ifxDid=t3.ifxDid
                                left join
                                (
                                        select A.cppid,A.cbatch,A.cMatid,B.ifxDid,sum(B.iRestQua) as dOutQ,sum(B.iRestQua*B.iPrice) as dOutA
                                        From ca_moveEnmom A inner join Ca_moveEnmom_Fx B on A.id=B.id
                                        where A.iType=2 and A.iperiod=@iPeriod-1
                                        group by A.cppid,A.cbatch,A.cMatid,B.ifxDid
                                ) t4 on t1.cppid=t4.cppid and t1.cbatch=t4.cbatch and t1.cMatID=t4.cMatID and t1.ifxDid=t4.ifxDid

                                --更新
                                update A set A.iQua=A.iQua+B.iQua,
                                        A.iTotalAmo=round(A.iTotalAmo+B.iTotalAmo,2),
                                        A.lastperiodamo = B.iTotalAmo,
                                        A.lastperiodqua = A.iQua
                            from ca_amoct_fx A
                            inner join ca_amoct_fx_temp_for_cal B on A.cppid=B.cppid and A.cbatch=B.cbatch and A.camotype=0 and A.camoid=B.camoid and A.ifxDid=B.ifxDid
                                      and A.iperiod=@iPeriod

                                --插入
                                insert into ca_amoct_fx(cppid,cbatch,cAmoID,cAmotype,ifxDid,iQua,iTotalAmo,iperiod,lastperiodqua,lastperiodamo)
                                select B.cppid,B.cbatch,B.camoid,0 as cAmotype,B.ifxDid,B.iQua,B.iTotalAmo,@iPeriod,B.iQua,B.iTotalAmo
                                from ca_amoct_fx_temp_for_cal B
                            left join ca_amoct_fx A on A.cppid=B.cppid and A.cbatch=B.cbatch and A.camotype=0 and A.camoid=B.camoid and A.ifxDid=B.ifxDid
                                      and A.iperiod=@iPeriod
                            where A.cppid is null

        end

        ----本期领用
           --分项结转
                truncate table ca_amoct_fx_temp_for_cal
                insert into ca_amoct_fx_temp_for_cal(cPPID,cBatch,cAmotype,cAmoID,ifxDid,iQua,iTotalAmo)
                select t1.cppid,t1.cbatch,0,t1.cMatID,t3.ifxDid,sum(t3.iQua),sum(t3.imatqua)
                from CA_MaBSW t1
                inner join ca_costBOM t2 on t1.iperiod=@iperiod  and t2.iLevel=@iLevel and t1.cppid=t2.iCOID
                inner join CA_MaBSW_fx t3 on t1.autoid=t3.id
                group by t1.cppid,t1.cbatch,t1.cMatID,t3.ifxDid

                update a set a.iQua=a.iQua+b.iQua,a.iTotalAmo=a.iTotalAmo+b.iTotalAmo
                from ca_amoct_fx a
                inner join ca_amoct_fx_temp_for_cal b on a.iperiod=@iPeriod and a.cppid=b.cppid and a.cbatch=b.cbatch and
                                                 a.camotype=b.camotype and a.camoid=b.camoid and a.ifxDid=b.ifxDid

                insert into ca_amoct_fx(iperiod,cppid,cbatch,cAmoID,cAmotype,ifxDid,iQua,iTotalAmo)
                        select @iPeriod,b.cppid,b.cbatch,b.cAmoID,b.cAmotype,b.ifxDid,b.iQua,b.iTotalAmo
                        from ca_amoct_fx_temp_for_cal b
                        left join ca_amoct_fx a on a.iperiod=@iPeriod and a.cppid=b.cppid and a.cbatch=b.cbatch and
                                                a.camotype=b.camotype and a.camoid=b.camoid and a.ifxDid=b.ifxDid
                        where a.cppid is null


        --准备专用材料盘点

        -------分项单价回写
            insert into CA_EnMoM_Fx(iPeriod,id,ifxDid,iPrice,iUnitQua)
        select @iperiod,a.autoid,C.ifxDid,
                 iprice=(case d.iQua when 0 then 0 else convert(float,c.iTotalAmo)/d.iQua end),
                 iUnitQua=(case d.iQua when 0 then 0 else convert(float,c.iQua)/d.iQua end)
                from caq_costBom b
                inner join ca_amoct_fx c on c.cppid=b.iCOID and b.iLevel=@iLevel
                inner join ca_amoct d on c.iperiod=d.iperiod and c.cppid=d.cppid and c.cbatch=d.cbatch and c.camotype=d.camotype and c.camoid=d.camoid
                inner join ca_enmom a on c.iperiod=a.iperiod and c.cppid=a.cppid and c.cbatch=a.cbatch and c.camotype=0 and c.camoid=a.cMatID
                where c.camotype=0 and c.iTotalAmo<>0 and c.iperiod=@iperiod


        -------反求单价
                update F set f.iprice=case when a.iRestQua=0 then 0
                                          else round(a.iRestQua * f.iPrice,2)/convert(float,a.iRestQua) end
                from caq_costBom b
                inner join ca_amoct_fx c on c.cppid=b.iCOID and b.iLevel=@iLevel
                inner join ca_enmom a on c.iperiod=a.iperiod and c.cppid=a.cppid and c.cbatch=a.cbatch and c.camotype=0 and c.camoid=a.cMatID
                inner join ca_enmom_fx f on a.autoid=f.id and f.iperiod=@iperiod and c.ifxdid=f.ifxdid
                where c.camotype=0 and c.iTotalAmo<>0 and c.iperiod=@iperiod and a.iRestQua<>0

        -------成本扣减
        update C set C.iQua=C.iQua-a.iRestQua * f.iUnitQua,
                     C.iTotalAmo=round(case when C.iTotalAmo-a.iRestQua* f.iPrice <0 then 0 else C.iTotalAmo-a.iRestQua* f.iPrice end,2)
                from caq_costBom b
                inner join ca_amoct_fx c on c.cppid=b.iCOID and b.iLevel=@iLevel
                inner join ca_enmom a on c.iperiod=a.iperiod and c.cppid=a.cppid and c.cbatch=a.cbatch and c.camotype=0 and c.camoid=a.cMatID
            inner join ca_enmom_fx f on a.autoid=f.id and f.iperiod=@iperiod and c.ifxdid=f.ifxdid
                where c.camotype=0 and c.iTotalAmo<>0 and c.iperiod=@iperiod

        --2.废品回收
        declare @ifxdid int
        Select top 1 @ifxdid = ifxdid from Ca_FxMapExpDetails where cAmotype = 0 and ISNULL(cAmoID,'') = -1
       
        if @ifxdid is not null
        begin
                Update A Set A.iTotalAmo = A.iTotalAmo + round(-iRecAmo,2) from Ca_Amoct_Fx A
                        inner join ca_waspr B on A.cPPid = B.cPPid and A.cbatch = B.cbatch and A.cAmoType = 0
                                and cAmoID = '-1' and A.iPeriod = B.iPeriod and A.ifxDid = @ifxdid
                        inner join ca_costbom C on A.cPPid = C.iCoid
                        where A.iPeriod = @iperiod and C.iLevel = @iLevel

                insert into ca_amoct_fx(cppid,cbatch,cAmoID,cAmotype,ifxDid,iQua,iTotalAmo,iperiod)
                        select distinct A.cppid,A.cbatch,'-1','0',@ifxdid,0,round(-iRecAmo,2),@iperiod
                                from CA_WasPR A
                                inner join Ca_CostBom b on a.cppid=b.iCOID
                                left join ca_amoct_fx C on A.cppid = C.cPPid and A.cbatch = C.cbatch and A.iPeriod = C.iPeriod and C.cAmoType = 0
                                        and cAmoID = '-1' and c.ifxDid = @ifxdid
                                where A.iPeriod=@iperiod and B.iLevel=@iLevel and iRecAmo <> 0 and C.cPPid is null
        end
end

八鼎

走过路过看过

HLJ_C

走过路过看过 并且知道怎么解决 哈哈

yaoxiaoxiang

执行这段语句。看看哪里报错，找到根源，也可以找我。。。

sztlbb

报错语句非常明确，缺少AUTOID字段，加上估计也不行。

sztlbb

ID=A.autoid,A.cppid,A.cbatch,A.camotype,A.camoid,C.ifxDid,C.iQua,iTotalAmo=C.iOnpTotalAmo
                         from ca_amoct_fx C
                         inner join ca_costbom B on C.iperiod=@iBeginPeriod and C.camotype=0 and b.iLevel=@iLevel and C.cppid=b.icoid
                     inner join ca_amoct A on C.iperiod=@iBeginPeriod and A.iperiod=@iPeriod
                                                                             and A.cppid=C.cppid and A.cbatch=C.cbatch and A.camotype=C.camotype and A.camoid=C.camoid
-------------------------------------------------------
很明显是CA_AMOCT这个表少了AUTOID这个字段


如果你没有用到生产制造，那么你在数据库中加上这个字段就可以了。如果用到了，那是不可能的，你没有这个字段，你生产制造不知道出了多少问题了。这是个必须有的字段。

xunxin

HLJ_C 发表于 2018-8-18 17:51
走过路过看过 并且知道怎么解决 哈哈

这个问题怎么解决的啊，我刚好也碰到一样的问题了