|
|
问题现象：在用友U8erp系统中用友U8版本号：U8软件的供应链产品线销售管理模块出现如下问题：销售系统发货结算勾对表查出有很多负数未结算，但在开销售发票时参照不到这些未结算的发货单。请帮忙解答！
原因分析：发货结算勾对表错，当红字发货单（即退货单）已关闭时，在发货结算勾对表中还是显示未结算数量，而兰字发货单已关闭时则未结算数量为零，导致发货结算勾对表中有很多负数未结算，而开票时参照发货单时已关闭的发货单不显示，故两处显示结果不一致。
解决方案：在用友erp 系统版本U8软件中修改存储过程SA_MoveOutIncome，在判断发货单是否已关闭的条件前加上abs，使判断条件考虑红、兰两种发货单后两处显示结果一致。存储过程SA_MoveOutIncome：/*当有退补业务时发货结算勾对表查询无数据江苏省药材公司2003.06.27当发货单关闭时发货结算勾对表未结数量为0而当退货单关闭时发货结算勾对表未结数量为退货数量江苏省中医药科技发展总公司2003.07.18南京用友维护部丁德安*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='Sa_MoveOutIncome'andtype='P')DROPPROCEDURESa_MoveOutIncomeGOCREATEPROCEDURESa_MoveOutIncome(--取得发货发货明细账存储过程作者：刘小东--@chrtablevarchar(200)=null,--保存发货结算勾兑明细临时表--@chrWhere1varchar(255)=null,--查询条件名称--@chrOutDatevarchar(100)=null,--发货日期--@chrSaleDatevarchar(100)=null,--开票日期--@chrVouchTypevarchar(100)=null--单据类型--)ASdeclare@chrSQLvarchar(8000)declare@chrCostvarchar(50)declare@chrWherevarchar(1000)declare@chrstartDatevarchar(50)declare@chrstartDate1varchar(50)declare@chrstartdate2varchar(50)ifltrim(rtrim(@chrwhere1))='newReport_ParameterFromTempTable'begin--条件参数通过临时表传递--set@chrwhere=(selectnamefromtempdb..newReportParameter)endelse--条件参数直接传递--beginset@chrwhere=rtrim(ltrim(@chrwhere1))end--取销售系统启用日期--set@chrStartDate=(selectisnull(cValue,'1900-01-01')fromaccinformationwherecSysid='Sa'andcName='dStartDate')if@chrstartdate''beginiflen(ltrim(rtrim(@chrstartdate)))='''+@chrstartdate+''''set@chrstartDate2='andSalebillVouch.dDate>='''+@chrstartdate+''''endifexists(select*fromtempdb..sysobjectswherename='po_DispatchFirst')droptabletempdb..po_DispatchFirstprint'发货'--取得发货单内容----2003.06.27增加两字段iTB,TBQuantityset@chrsql='SelectDispatchlist.DLIDasDLID,Dispatchlist.cDLCodeAScDLCode,Dispatchlist.cSTCodeAScSTCode,Dispatchlist.ddateASddate,Dispatchlist.cDepCodeascDepCode,Dispatchlist.cPersonCodeascPersonCode,Dispatchlist.cCusCodeascCusCode,Dispatchlist.iExchRateasiExchRate,Dispatchlist.bFirstasbFirst,Dispatchlist.cVerifierascVerifier,Dispatchlist.cMakerascMaker,Dispatchlists.iDlsidasiDlsid,Dispatchlists.cWhCodeascWHCode,Dispatchlists.cInvCodeascInvCode,Dispatchlists.iQuantityasiQuantity,Dispatchlists.iNumasiNum,Dispatchlists.iTaxUnitPriceasiTaxUnitPrice,Dispatchlists.iMoneyasiMoney,Dispatchlists.iTaxasiTax,Dispatchlists.iSumasiSum,Dispatchlists.iDisCountasiDisCount,Dispatchlists.iNatUnitPriceasiNatUnitPrice,Dispatchlists.iNatMoneyasiNatMoney,Dispatchlists.iNatTaxasiNatTax,Dispatchlists.iNatSumasiNatSum,Dispatchlists.iNatDisCountasiNatDisCount,Dispatchlists.cBatchascBatch,Dispatchlists.cFree1ascFree1,Dispatchlists.cFree2ascFree2,Dispatchlists.iTaxRateasiTaxRate,Dispatchlists.cDefine22ascDefine22,Dispatchlists.cDefine23ascDefine23,Dispatchlists.cDefine24ascDefine24,Dispatchlists.cDefine25ascDefine25,Dispatchlists.cDefine26ascDefine26,Dispatchlists.cDefine27ascDefine27,Dispatchlist.cVouchtypeascVouchtype,Dispatchlist.cDefine1ascDefine1,Dispatchlist.cDefine2ascDefine2,Dispatchlist.cDefine3ascDefine3,Dispatchlist.cDefine4ascDefine4,Dispatchlist.cDefine5ascDefine5,Dispatchlist.cDefine6ascDefine6,Dispatchlist.cDefine7ascDefine7,Dispatchlist.cDefine8ascDefine8,Dispatchlist.cDefine9ascDefine9,Dispatchlist.cDefine10ascDefine10,Dispatchlists.bSettleallasbsettleall,Dispatchlists.iSettleQuantityasiSettleQuantity,Dispatchlists.iSettleNumasiSettlenum,Dispatchlists.iTBasiTB,Dispatchlists.TBQuantityasTBQuantity,Dispatchlists.iUnitPriceINTOtempdb..po_DispatchFirstFromDispatchlistsinnerjoinDispatchlistOnDispatchLists.DLID=Dispatchlist.DLIDWHERE(Dispatchlist.bFirst=1or'+@chrstartdate1+')'--追加单据类型条件ifnot(@chrVouchtypeisnull)andltrim(rtrim(@chrVouchType))''set@chrsql=@chrsql+'and'+@chrVouchtypeifnot(@chrOutDateisnull)andltrim(rtrim(@chrOutDate))''set@chrsql=@chrsql+'and'+@chrOutDateifnot(@chrwhereisnull)andltrim(rtrim(@chrwhere))''set@chrsql=@chrsql+'and'+@chrWhereexec(@chrsql)--取得发票内容，发票按发货单子表ID汇总--print'开票'ifexists(select*fromtempdb..sysobjectswherename='po_SaleBillFirst')droptabletempdb..po_salebillfirstset@chrsql='SelectSaleBillVouchs.iDLSIDasiDLSID,SaleBillVouchs.iQuantityasiQuantity,SaleBillVouchs.iNumasiNum,SaleBillVouchs.iMoneyasiMoney,SaleBillVouchs.iSumasiSum,SaleBillVouchs.iTaxasiTax,SaleBillVouchs.iNatMoneyasiNatMoney,SaleBillVouchs.iNatSumasiNatSum,SaleBillVouchs.iNatTaxasiNatTaxINTOtempdb..po_SaleBillfirstFromSaleBillVouchsinnerjoinSaleBillVouchOnSalebillVouchs.SBVID=SalebillVouch.SBVIDinnerjointempdb..po_dispatchfirstontempdb..po_dispatchfirst.idlsid=Salebillvouchs.idlsidWhereSalebillvouchs.idlsid0andSalebillvouchs.idlsidisnotnullandisnull(Salebillvouch.cInvalider,'''')=''''andisnull(Salebillvouch.cSTCode,'''')'''''+@chrstartdate2--作废发票不能计算在内,开票日期必须大于系统起用日期--ifnot(@chrSaleDateisnull)andltrim(rtrim(@chrSaleDate))''set@chrsql=@chrsql+'and'+@chrSaleDate--不要其它条件--ifnot(@chrwhereisnull)andltrim(rtrim(@chrwhere))''set@chrsql=@chrsql+'and'+@chrwhereexec(@chrsql)--发票内容按发货单子表ID号合计--ifexists(select*fromtempdb..sysobjectswherename='po_SaleBill')DropTabletempdb..po_salebillSelectiDlsid,Sum(iQuantity)asjs_iQuantity,Sum(iNum)asjs_iNum,Sum(iMoney)asjs_iMoney,Sum(iSum)asjs_iSum,Sum(iTax)asjs_iTax,Sum(iNatMoney)asjs_iNatMoney,Sum(iNatSum)asjs_iNatSum,Sum(iNatTax)asjs_iNatTaxINTOtempdb..po_SaleBillfromtempdb..po_salebillfirstGroupbyIdlsidifexists(select*fromtempdb..sysobjectswherename='po_DispSale')Droptabletempdb..po_DispSale--关联发货单和结算单----ye_iNatSum,ye_iNatTax考虑退补问题--2003.06.27--退货单关闭时问题，--casewhena.bsettleall=1and(a.iQuantity-a.iSettleQuantity)>0then--改成casewhena.bsettleall=1andabs(a.iQuantity-a.iSettleQuantity)>0then--2003.07.18set@chrsql='Selecta.DLIDasDLID,a.cDLCodeAScDLCode,a.cSTCodeAScSTCode,a.ddateASddate,a.cDepCodeascDepCode,a.cPersonCodeascPersonCode,a.cCusCodeascCusCode,a.iExchRateasiExchRate,a.bFirstasbFirst,a.cVerifierascVerifier,a.cMakerascMaker,a.cWhCodeascWHCode,a.cInvCodeascInvCode,a.iQuantityasiQuantity,a.iNumasiNum,a.iTaxUnitPriceasiTaxUnitPrice,a.iMoneyasiMoney,a.iTaxasiTax,a.iSumasiSum,a.iDisCountasiDisCount,a.iNatUnitPriceasiNatUnitPrice,a.iNatMoneyasiNatMoney,a.iNatTaxasiNatTax,a.iNatSumasiNatSum,a.iNatDisCountasiNatDisCount,a.cBatchascBatch,a。
|
|
|手机版|小黑屋|用友十万个为什么
( 赣ICP备13006939号 )
发表于 2016-9-25 15:40:23