标题: item本期采购入库金额差错 [打印本页] 作者: stone 时间: 2013-3-5 20:17 标题: item本期采购入库金额差错
【Q】item本期采购入库金额为负
有一张crystal报表，叫收发存汇总表，表中有字段“本期收进/采购入库”无Item数量但有金额，或有Item数据无金额。
【A】从表的公式来看，如“本期收进/采购入库”，本期“采购入库”的数量和金额公式如下：
1）金额
if mid({?fromdate},1,4)=mid({?todate},1,4) then //从fromdate字段中把第1位到第4位取出，判断数值在范围内否，在这里是“如果输入的开始年度和结束年度是一样的”，那么……
if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) //IC Tansaction History的Fisc Year如果等于输入的年度
AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) AND
{ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) and
(({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
//IC Tansaction History的交易类型为Receipt、Receipt Ajustment、Receipt Return。
then {ICHIST.HOMEEXTCST} else 0 //Extended Cost - Functional，用本位币显示扩展成本
else
(if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR //输入的开始年度对应并且大于等于输入的期间，或者……
( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR //输入的结束年度并且小于等于输入的期间，或者……
( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
(({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
then {ICHIST.HOMEEXTCST} else 0 )
2）数量
if mid({?fromdate},1,4)=mid({?todate},1,4) then
if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) AND
{ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) AND
(({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0
else
(if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR
( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR
( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
(({ICHIST.TRANSTYPE}=1 OR {ICHIST.TRANSTYPE}=2 OR {ICHIST.TRANSTYPE}=3 ) )
then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0 )
----------------------------------------------------------
if mid({?fromdate},1,4)=mid({?todate},1,4) then
if {ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) AND
{ICHIST.FISCPERIOD} <= ToNumber( mid({?todate},5,2) ) AND
(({ICHIST.TRANSTYPE}=13) )//Stock Transfer To
then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0
else
(if ({ICHIST.FISCYEAR} = mid({?fromdate},1,4) AND {ICHIST.FISCPERIOD} >=ToNumber (mid({?fromdate},5,2)) ) OR
( {ICHIST.FISCYEAR} = mid({?todate},1,4) AND {ICHIST.FISCPERIOD} <= ToNumber(mid({?todate},5,2) )) OR
( {ICHIST.FISCYEAR} > mid({?fromdate},1,4) AND {ICHIST.FISCYEAR} < mid({?todate},1,4) ) AND
(({ICHIST.TRANSTYPE}=13) )
then {ICHIST.QUANTITY}*{ICUNIT.CONVERSION} else 0 )
