供应链存货核算存货与总账对账不平用友 专题培训Word文件下载.docx
《供应链存货核算存货与总账对账不平用友 专题培训Word文件下载.docx》由会员分享,可在线阅读,更多相关《供应链存货核算存货与总账对账不平用友 专题培训Word文件下载.docx(13页珍藏版)》请在冰豆网上搜索。
SQL2005
问题标题:
问题描述:
存货与总账对账不平的常见原因和处理思路分析。
问题分析:
◆【原理说明】
存货与总帐对帐,实际上就是一个按照科目抓取数据进行核对的过程,其中存货的数据来自于ia_subsidiary,总账的数据主要来自于Gl_accsum、gl_accvouch。
其处理过程主要为:
--存货期初数据抽取
SelectcInvHead,cCode_Name,
cast(isnull(iAInPrice,0)-isnull(iAOutPrice,0)asdecimal(38,2))asQcPrice,
cast(isnull(iAInQuantity,0)-isnull(iAOutQuantity,0)asfloat)asQcQuantity,
cast(0asfloat)asInPRice,cast(0asfloat)asInQuantity,
cast(0asfloat)asOutPrice,cast(0asfloat)asoutQuantity
--intotempdb..OptVouchFJX0
fromia_subsidiary
leftjoincodeonia_subsidiary.cInvHead=code.ccodeandcode.iyear=2011
whereIA_subsidiary.iYear=2011
AndnotcPZIDisnull
andnotcInvHeadisnull
andia_subsidiary.cvoutype<
>
N'
33'
andiMonth<
1
Insertintotempdb..OptVouchFJX0
SelectcDifHead,cCode_Name,
cast(isnull(iDebitDifCost,0)-isnull(iCreditDifCost,0)asdecimal(38,2))asQcPrice,
cast(isnull((casewhenisnull(idebitdifcost,0)=0then0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end),0)-isnull((casewhenbrdflag=1then(casewhenisnull(iCreditDifCost,0)=0then0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)else(casewhenisnull(iCreditDifCost,0)=0then0else(cast(isnull(iAoutQuantity,0)asfloat)-cast(isnull(iAinQuantity,0)asfloat))end)end),0)asfloat)asQcQuantity,
cast(0asfloat)asInPRice,
cast(0asfloat)asInQuantity,
cast(0asfloat)asOutPrice,
cast(0asfloat)asoutQuantity
leftjoincodeonia_subsidiary.cDifHead=code.ccodeandcode.iYear=2011
andnotcDifHeadisnull
--存货日常发生数抽取
cast(0asfloat)asqcPRice,
cast(0asfloat)asqcQuantity,
cast(isnull(iAInPrice,0)asdecimal(38,2))asinPrice,
cast(isnull(iAInQuantity,0)asfloat)asinQuantity,
cast(isnull(iAOutPrice,0)asdecimal(38,2))asOutPrice,
cast(isnull(iAOutQuantity,0)asfloat)asoutQuantity
andiMonth=1
cast(0asfloat)asQcPrice,0asqcQuantity,
isnull(iDebitDifCost,0)asInPrice,
(casewhenisnull(idebitdifcost,0)=0then0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)asinQuantity,
cast(isnull(iCreditDifCost,0)asdecimal(38,2))asOutPrice,
(casewhenbrdflag=1then(casewhenisnull(iCreditDifCost,0)=0then0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)else(casewhenisnull(iCreditDifCost,0)=0then0else(cast(isnull(iAoutQuantity,0)asfloat)-cast(isnull(iAinQuantity,0)asfloat))end)end)asoutQuantity
leftjoincodeonia_subsidiary.cDifHead=code.ccodeandcode.iyear=2011
--按科目汇总存货期初和日常发生数据
ltrim(str(cast(sum(cast(qcPriceasdecimal(38,2)))asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(qcQuantity)asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(cast(InPriceasdecimal(38,2)))asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(inQuantity)asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(cast(OutPriceasdecimal(38,2)))asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(OutQuantity)asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(cast(qcPriceasdecimal(38,2))+cast(InPRiceasdecimal(38,2))-cast(Outpriceasdecimal(38,2)))asdecimal(38,2)),20,2)),
ltrim(str(cast(sum(qcquantity+inquantity-outquantity)asdecimal(38,2)),20,2))
fromtempdb..OptVouchFJX0
groupbycInvHead,cCode_Name
orderbycInvHead
--总账期初数据抽取
---总账期初未记账数据抽取
Selectccode,null,
cast(sum(md)-sum(mc)asdecimal(38,2)),
cast(sum(nd_s)-sum(nc_s)asdecimal(38,2)),0,0,0,0
FromGL_accVouch
Whereisnull(iflag,0)<
1
AndisNull(ibook,0)<
Andccode=N'
1403'
and((iyear=2011Andiperiod<
1)Oriyear<
2011)
GroupBycCode
--总账期初已记账数据抽取
cast(md-mcasdecimal(38,2)),
cast(nd_s-nc_sasdecimal(38,2)),
0,0,0,0
FromGl_accsum
Whereiperiod<
1
AndGl_accsum.ccode=N'
andiyear=2011
---总账日常未记账凭证数据抽取
Selectccode,null,0,0,
sum(md),sum(nd_s),
sum(mc),sum(nc_s)
whereiperiod=1
Andisnull(iflag,0)<
andiyear=2011
---总账日常已记账数据抽取
Se