解决方案: | 修改Sa_MoveTrustDetail存储过程如下,即可解决问题: IF EXISTS (SELECT * FROM sysobjects WHERE name=’Sa_MoveTrustDetail’ and type=’P’) DROP PROCEDURE Sa_MoveTrustDetail GO /* 改正当数据精度设置中存货数量小数位大于4位时委托代销统计表 发货数量、结算数量、结存数量只显示4位的问题 2003.06.13 南京用友维护部 丁德安 */ CREATE PROCEDURE Sa_MoveTrustDetail @chrTableName varchar(255) = null, @chrWhere1 varchar(255) = null, @chrStartDate varchar(30) = null, @chrEndDate varchar(30) = null AS declare @chrSQL varchar(4000) declare @chrWhere varchar(4000) /*取得超长参数方法*/ if ltrim(rtrim(@chrWhere1)) = ‘newReport_ParameterFromTempTable’ begin–从临时表中取得超长参数 为了解决SQL SERVER 7.0 中的问题 set @chrWhere = (select name from tempdb..newReportParameter ) end else begin set @chrWhere = @chrWhere1 end /*取得需要处理的最小数据集合*/ /*取委托代销发货数据 */ if exists(select name from tempdb..sysobjects where name=’tmp_saletrust’) drop table tempdb..tmp_saletrust set @chrSQL = ‘Select ddate, autoid, iQuantity, iNum, iMoney, iTax, iSum, iDisCount, iNatMoney, iNatTax, iNatSum, iNatDisCount into tempdb..tmp_saletrust from Endispatchs Inner join Endispatch On EnDispatchs.EDID = Endispatch.EDID ‘ if ltrim(rtrim(@chrWhere)) = ” or @chrWhere is null begin/*设置日期条件 */ if rtrim(ltrim(@chrEndDate)) ” and not (@chrEndDate is null) set @chrSQL = @chrSQL + ‘Where ddate <= ''' + @chrEndDate + '''' end else/* 设置其他条件 */ begin set @chrSQL = @chrSQL + ‘Where ‘ + @chrWhere /*设置日期条件 */ if rtrim(ltrim(@chrEndDate)) ” and not (@chrEndDate is null) set @chrSQL = @chrSQL + ‘ and ddate <= ''' + @chrEndDate + '''' end exec (@chrSQL) /*取得委托结算数据*/ if exists(select name from tempdb..sysobjects where name = ‘tmp_resulttrust’) drop table tempdb..tmp_resulttrust set @chrSQL = ‘Select dDate, iCorID as autoid, iQuantity, iNum, iMoney, iTax, iSum, iDisCount, iNatMoney, iNatTax, iNatSum, iNatDisCount into tempdb..tmp_resulttrust from Dispatchlists inner join dispatchlist on Dispatchlists.dlid = Dispatchlist.dlid Where Dispatchlist.cVouchtype = ”07”’ if ltrim(rtrim(@chrWhere)) ” and not (@chrWhere is null) set @chrSQL = @chrSQL + ‘ and ‘ + @chrWhere if rtrim(ltrim(@chrEndDate)) ” and not (@chrEndDate is null) set @chrSQL = @chrSQL + ‘ and ddate <= ''' + @chrEndDate + '''' exec (@chrSQL) /*分析出委托代销期初,本期发货,本期结算和结存数据 */ /*从临时表 tempdb..tmp_saletrust 取委托发货数据,从 tempdb..tmp_resulttrust 取结算数据*/ if exists(select name from tempdb..sysobjects where name=’tmp_trustDetail’) drop table tempdb..tmp_trustDetail /*计算期初*/ if ltrim(rtrim(@chrStartDate)) ” and not (@chrStartDate is null) begin /* 期初发货 */ Select autoid, dDate, iQuantity as qc_iQuantity, iNum as qc_iNum, iMoney as qc_iMoney, iTax as qc_iTax, iSum as qc_iSum, iDiscount as qc_iDiscount, iNatMoney as qc_iNatMoney, iNatTax as qc_iNatTax, iNatSum as qc_iNatSum, iNatDiscount as qc_iNatDiscount, iQuantity – iQuantity as fh_iQuantity , iNum – iNum as fh_iNum, iMoney – iMoney as fh_iMoney, iTax – iTax as fh_iTax, iSum – iSum as fh_iSum, iDiscount – iDiscount as fh_iDiscount, iNatMoney – iNatMoney as fh_iNatMoney, iNatTax – iNatTax as fh_iNatTax, iNatSum – iNatSum as fh_iNatSum, iNatDiscount – iNatDiscount as fh_iNatDiscount, iQuantity – iQuantity as js_iQuantity, iNum – iNum as js_iNum , iMoney – iMoney as js_iMoney, iTax – iTax as js_iTax, iSum – iSum as js_iSum, iDiscount – iDiscount as js_iDiscount, iNatMoney – iNatMoney as js_iNatMoney, iNatTax – iNatTax as js_iNatTax, iNatSum – iNatSum as js_iNatSum, iNatDiscount – iNatDiscount as js_iNatDiscount, iQuantity – iQuantity as wj_iQuantity, iNum – iNum as wj_iNum, iMoney – iMoney as wj_iMoney, iTax – iTax as wj_iTax, iSum – iSum as wj_iSum, iDiscount – iDiscount as wj_iDiscount, iNatMoney – iNatMoney as wj_iNatMoney, iNatTax – iNatTax as wj_iNatTax, iNatSum – iNatSum as wj_iNatSum, iNatDiscount – iNatDiscount as wj_iNatDiscount into tempdb..tmp_trustDetail from tempdb..tmp_saletrust where dDate < @chrStartDate /* 期初结算 */ insert into tempdb..tmp_trustDetail Select autoid, dDate, – iQuantity as qc_iQuantity, – iNum as qc_iNum, – iMoney as qc_iMoney, – iTax as qc_iTax, – iSum as qc_iSum, – iDiscount as qc_iDiscount, – iNatMoney as qc_iNatMoney, – iNatTax as qc_iNatTax, – iNatSum as qc_iNatSum, – iNatDiscount as qc_iNatDiscount, 0 as fh_iQuantity, 0 as fh_iNum , 0 as fh_iMoney, 0 as fh_iTax, 0 as fh_iSum, 0 as fh_iDiscount , 0 as fh_iNatMoney, 0 as fh_iNatTax, 0 as fh_iNatSum, 0 as fh_iNatDiscount, 0 as js_iQuantity, 0 as js_iNum, 0 as js_iMoney, 0 as js_iTax, 0 as js_iSum, 0 as js_iDiscount, 0 as js_iNatMoney, 0 as js_iNatTax, 0 as js_iNatSum, 0 as js_iNatDiscount, 0 as wj_iQuantity, 0 as wj_iNum, 0 as wj_iMoney, 0 as wj_iTax, 0 as wj_iSum, 0 as wj_iDiscount, 0 as wj_iNatMoney, 0 as wj_iNatTax, 0 as wj_iNatSum, 0 as wj_iNatDiscount from tempdb..tmp_resulttrust where dDate < @chrStartDate end /*计算本期发货 */ if ltrim(rtrim(@chrStartDate)) = ” or @chrStartDate is null begin/* 没有输入期初日期的情况 */ Select autoid, dDate, iQuantity – iQuantity as qc_iQuantity, iNum – iNum as qc_iNum, iMoney – iMoney as qc_iMoney, iTax – iTax as qc_iTax, iSum – iSum as qc_iSum, iDiscount – iDiscount as qc_iDiscount, iNatMoney – iNatMoney as qc_iNatMoney, iNatTax – iNatTax as qc_iNatTax, iNatSum – iNatSum as qc_iNatSum, iNatDiscount – iNatDiscount as qc_iNatDiscount, iQuantity as fh_iQuantity, iNum as fh_iNum, iMoney as fh_iMoney, iTax as fh_iTax, iSum as fh_iSum, iDiscount as fh_iDiscount, iNatMoney as fh_iNatMoney, iNatTax as fh_iNatTax , iNatSum as fh_iNatSum , iNatDiscount as fh_iNatDiscount, iQuantity – iQuantity as js_iQuantity, iNum – iNum as js_iNum, iMoney – iMoney as js_iMoney, iTax – iTax as js_iTax, iSum – iSum as js_iSum, iDiscount – iDiscount as js_iDiscount, iNatMoney – iNatMoney as js_iNatMoney, iNatTax – iNatTax as js_iNatTax, iNatSum – iNatSum as js_iNatSum, iNatDiscount – iNatDiscount as js_iNatDiscount, iQuantity – iQuantity as wj_iQuantity, iNum – iNum as wj_iNum, iMoney – iMoney as wj_iMoney, iTax – iTax as wj_iTax, iSum – iSum as wj_iSum, iDiscount – iDiscount as wj_iDiscount, iNatMoney – iNatMoney as wj_iNatMoney, iNatTax – iNatTax as wj_iNatTax, iNatSum – iNatSum as wj_iNatSum, iNatDiscount – iNatDiscount as wj_iNatDiscount into tempdb..tmp_trustDetail from tempdb..tmp_saletrust end else begin/*有期初日期的情况 */ insert into tempdb..tmp_trustDetail Select autoid, dDate, 0 as qc_iQuantity, 0 as qc_iNum, 0 as qc_iMoney, 0 as qc_iTax, 0 as qc_iSum, 0 as qc_iDiscount, 0 as qc_iNatMoney, 0 as qc_iNatTax, 0 as qc_iNatSum, 0 as qc_iNatDiscount, iQuantity as fh_iQuantity, iNum as fh_iNum, iMoney as fh_iMoney, iTax as fh_iTax, iSum as fh_iSum, iDiscount as fh_iDiscount, iNatMoney as fh_iNatMoney, iNatTax as fh_iNatTax, iNatSum as fh_iNatSum, iNatDiscount as fh_iNatDiscount, 0 as js_iQuantity, 0 as js_iNum, 0 as js_iMoney, 0 as js_iTax, 0 as js_iSum, 0 as js_iDiscount, 0 as js_iNatMoney, 0 as js_iNatTax, 0 as js_iNatSum, 0 as js_iNatDiscount, 0 as wj_iQuantity, 0 as wj_iNum, 0 as wj_iMoney, 0 as wj_iTax, 0 as wj_iSum, 0 as wj_iDiscount, 0 as wj_iNatMoney, 0 as wj_iNatTax, 0 as wj_iNatSum, 0 as wj_iNatDiscount from tempdb..tmp_saletrust where dDate >= @chrStartDate end /*计算本期结算 */ if rtrim(ltrim(@chrStartDate)) = ” or @chrStartDate is null begin/*没有输入期初日期的情况 */ insert into tempdb..tmp_trustDetail Select autoid, dDate, 0 as qc_iQuantity, 0 as qc_iNum, 0 as qc_iMoney, 0 as qc_iTax, 0 as qc_iSum, 0 as qc_iDiscount, 0 as qc_iNatMoney, 0 as qc_iNatTax, 0 as qc_iNatSum, 0 as qc_iNatDiscount, 0 as fh_iQuantity, 0 as fh_iNum, 0 as fh_iMoney, 0 as fh_iTax, 0 as fh_iSum, 0 as fh_iDiscount , 0 as fh_iNatMoney, 0 as fh_iNatTax, 0 as fh_iNatSum, 0 as fh_iNatDiscount, iQuantity as js_iQuantity, iNum as js_iNum, iMoney as js_iMoney, iTax as js_iTax, iSum as js_iSum, iDiscount as js_iDiscount, iNatMoney as js_iNatMoney, iNatTax as js_iNatTax, iNatSum as js_iNatSum, iNatDiscount as js_iNatDiscount, 0 as wj_iQuantity, 0 as wj_iNum, 0 as wj_iMoney, 0 as wj_iTax, 0 as wj_iSum, 0 as wj_iDiscount, 0 as wj_iNatMoney, 0 as wj_iNatTax, 0 as wj_iNatSum, 0 as wj_iNatDiscount from tempdb..tmp_resulttrust end else/*输入期初日期的情况 */ begin insert into tempdb..tmp_trustDetail Select autoid, dDate, 0 as qc_iQuantity, 0 as qc_iNum, 0 as qc_iMoney, 0 as qc_iTax, 0 as qc_iSum, 0 as qc_iDiscount, 0 as qc_iNatMoney, 0 as qc_iNatTax, 0 as qc_iNatSum, 0 as qc_iNatDiscount, 0 as fh_iQuantity, 0 as fh_iNum, 0 as fh_iMoney, 0 as fh_iTax, 0 as fh_iSum, 0 as fh_iDiscount, 0 as fh_iNatMoney, 0 as fh_iNatTax, 0 as fh_iNatSum, 0 as fh_iNatDiscount, iQuantity as js_iQuantity, iNum as js_iNum, iMoney as js_iMoney, iTax as js_iTax, iSum as js_iSum, iDiscount as js_iDiscount, iNatMoney as js_iNatMoney, iNatTax as js_iNatTax, iNatSum as js_iNatSum, iNatDiscount as js_iNatDiscount, 0 as wj_iQuantity, 0 as wj_iNum, 0 as wj_iMoney, 0 as wj_iTax, 0 as wj_iSum, 0 as wj_iDiscount, 0 as wj_iNatMoney, 0 as wj_iNatTax, 0 as wj_iNatSum, 0 as wj_iNatDiscount from tempdb..tmp_resulttrust where ddate >= @chrStartDate end /*组织最后的数据源临时表 */ /*初始化临时表 */ if exists(select name from tempdb..sysobjects where name = @chrTableName ) exec (‘drop table tempdb..’+ @chrTableName) /*生成临时表 */ /*数据源输出列:仓库, 销售类型, 部门,业务员,客户名称,存货名称,规格型号,主计量单位, 辅助计量单位,存货供应商,币种,客户自定义项(1-3),存货自定义项(1-3) 单据体自定义项(1-6),单据头自定义项(1-10),期初(数量,件数,金额,税额, 价税合计,折扣,本币金额,本币税额,本币价税合计,本币折扣)本期发货(数量, 件数,金额,税额,价税合计,折扣,本币金额,本币税额,本币价税合计,本币折扣), 本期结算(数量,件数,金额,税额,价税合计,折扣,本币金额,本币税额, 本币价税合计,本币折扣),期末结存 (数量,件数,金额,税额,价税合计,折扣, 本币金额,本币税额,本币价税合计,本币折扣)*/ set @chrSQL = ‘Select a.*, warehouse.cwhname, saletype.cstname, department.cDepname, person.cPersonname, customer.cCusname, customer.cCusDefine1, customer.cCusDefine2, customer.cCusDefine3, inventory.cinvName, inventory.cinvstd, inventory.cInvM_Unit, inventory.cInvA_Unit, vendor.cVenName, inventory.cInvDefine1, inventory.cInvDefine2, inventory.cInvDefine3, c.cexch_name, c.cDefine1, c.cDefine2, c.cDefine3, c.cDefine4, c.cDefine5, c.cDefine6, c.cDefine7, c.cDefine8, c.cDefine9, c.cDefine10, b.cDefine22, b.cDefine23, b.cDefine24, b.cDefine25, b.cDefine26, b.cDefine27 into tempdb..’+ @chrTableName +’ from tempdb..tmp_trustdetail a inner join endispatchs b on a.autoid = b.autoid inner join endispatch c on b.EDID = c.EDID left join inventory on b.cInvCode = inventory.cInvCode left join warehouse on b.cWhCode = warehouse.cWhcode left join department on c.cDepCode = department.cDepcode left join person on c.cPersoncode = person.cpersoncode left join customer on c.cCuscode = customer.cCuscode left join vendor on inventory.cVencode = vendor.cVencode left join saletype on c.cstcode = saletype.cstcode’ exec (@chrSQL) GO |
---|