供应链存货核算存货与总账对账不平用友 专题培训.docx
- 文档编号:390888
- 上传时间:2022-10-09
- 格式:DOCX
- 页数:13
- 大小:370.25KB
供应链存货核算存货与总账对账不平用友 专题培训.docx
《供应链存货核算存货与总账对账不平用友 专题培训.docx》由会员分享,可在线阅读,更多相关《供应链存货核算存货与总账对账不平用友 专题培训.docx(13页珍藏版)》请在冰豆网上搜索。
供应链存货核算存货与总账对账不平用友专题培训
存货与总账对账
产品线:
U8
版本:
U8V10.1
领域:
可参考excel
模块:
可参考excel
数据库版本:
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
fromia_subsidiary
leftjoincodeonia_subsidiary.cDifHead=code.ccodeandcode.iYear=2011
whereIA_subsidiary.iYear=2011
AndnotcPZIDisnull
andnotcDifHeadisnull
andia_subsidiary.cvoutype<>N'33'
andiMonth<1
--存货日常发生数抽取
Insertintotempdb..OptVouchFJX0
SelectcInvHead,cCode_Name,
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
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(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
fromia_subsidiary
leftjoincodeonia_subsidiary.cDifHead=code.ccodeandcode.iyear=2011
whereIA_subsidiary.iYear=2011
AndnotcPZIDisnull
andnotcDifHeadisnull
andia_subsidiary.cvoutype<>N'33'
andiMonth=1
--按科目汇总存货期初和日常发生数据
SelectcInvHead,cCode_Name,
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
--总账期初数据抽取
---总账期初未记账数据抽取
Insertintotempdb..OptVouchFJX0
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)<>1
Andccode=N'1403'
and((iyear=2011Andiperiod<1)Oriyear<2011)
GroupBycCode
--总账期初已记账数据抽取
Insertintotempdb..OptVouchFJX0
Selectccode,null,
cast(md-mcasdecimal(38,2)),
cast(nd_s-nc_sasdecimal(38,2)),
0,0,0,0
FromGl_accsum
Whereiperiod<1
AndGl_accsum.ccode=N'1403'
andiyear=2011
---总账日常未记账凭证数据抽取
Insertintotempdb..OptVouchFJX0
Selectccode,null,0,0,
sum(md),sum(nd_s),
sum(mc),sum(nc_s)
FromGL_accVouch
whereiperiod=1
Andisnull(iflag,0)<>1
AndisNull(ibook,0)<>1
Andccode=N'1403'
andiyear=2011
GroupBycCode
---总账日常已记账数据抽取
Insertintotempdb..OptVouchFJX0
Se
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 供应链存货核算存货与总账对账不平用友 专题培训 供应 存货 核算 总账 不平 用友 专题 培训