| 解决方案： |
修改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
|