供应链-存货核算-存货与总账对账不平.docx

ME技术与产品支持服务中心 呼叫中心/产品支持 84号工程师 方俊霞 存货与总账对账 产品线：U8 版本：U8V10.1 领域：可参考excel 模块：可参考excel 数据库版本：SQL2005 问题标题：存货与总账对账 问题描述：存货与总账对账不平的常见原因和处理思路分析。 问题分析： 【原理说明】 存货与总帐对帐，实际上就是一个按照科目抓取数据进行核对的过程，其中存货的数据来自于ia_subsidiary，总账的数据主要来自于Gl_accsum、gl_accvouch。 其处理过程主要为： --存货期初数据抽取 Select cInvHead,cCode_Name, cast(isnull(iAInPrice,0)-isnull(iAOutPrice,0) as decimal(38,2)) as QcPrice, cast(isnull(iAInQuantity,0)-isnull(iAOutQuantity,0) as float) as QcQuantity, cast(0 as float) as InPRice,cast(0 as float) as InQuantity, cast(0 as float) as OutPrice,cast(0 as float) as outQuantity --into tempdb..OptVouchFJX0 from ia_subsidiary left join code on ia_subsidiary.cInvHead=code.ccode and code.iyear = 2011 where IA_subsidiary.iYear = 2011 And not cPZID is null and not cInvHead is null and ia_subsidiary.cvoutype N33 and iMonth1 Insert into tempdb..OptVouchFJX0 Select cDifHead,cCode_Name, cast(isnull(iDebitDifCost,0)-isnull(iCreditDifCost,0) as decimal(38,2)) as QcPrice, cast(isnull((case when isnull(idebitdifcost,0)=0 then 0 else (cast(isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float)) end),0)-isnull((case when brdflag=1 then (case when isnull(iCreditDifCost,0)=0 then 0 else (cast(isnull(iAInQuantity,0) as float)- cast(isnull(iAOutQuantity,0) as float)) end) else (case when isnull(iCreditDifCost,0)=0 then 0 else (cast(isnull(iAoutQuantity,0) as float)- cast(isnull(iAinQuantity,0) as float)) end) end) ,0) as float) as QcQuantity, cast(0 as float) as InPRice, cast(0 as float) as InQuantity, cast(0 as float) as OutPrice, cast(0 as float) as outQuantity from ia_subsidiary left join code on ia_subsidiary.cDifHead=code.ccode and code.iYear = 2011 where IA_subsidiary.iYear = 2011 And not cPZID is null and not cDifHead is null and ia_subsidiary.cvoutype N33 and iMonth1 --存货日常发生数抽取 Insert into tempdb..OptVouchFJX0 Select cInvHead,cCode_Name, cast(0 as float) as qcPRice, cast(0 as float) as qcQuantity, cast(isnull(iAInPrice,0) as decimal(38,2)) as inPrice, cas