问题:旗般版5.0,SQL2008R2 生产投料单变更单按F7选不到源单,只有一个帐套有问题,其他帐套没问题。
从生产投料单上可以正常下推生产投料变更单,但从新增生产投料变更单在按F7无法选到源单,实际有末关闭的投料单。
SQL后台跟踪:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SET NOCOUNT OFF
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select top 20000 v1.FTranType as FTranType,v1.FInterID as FInterID,v1.FBillNo as FBillNo,v1.FDate as FDate,case when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end as FStatus,i1.FBillNo as FICMOBillNo,u1.FEntryID as FEntryID, 0 As FBOSCloseFlag from PPBOM v1 INNER JOIN PPBOMEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID<>0 AND v1.FType=1067
INNER JOIN ICSubContract i1 ON v1.FICMOInterID = i1.FInterID AND i1.FInterID<>0
INNER JOIN ICSubContractEntry i2 ON i1.FInterID = i2.FInterID AND i2.FInterID<>0 AND i2.FEntryID = v1.FOrderEntryID
INNER JOIN t_ICItem t4 ON v1.FItemID = t4.FItemID AND t4.FItemID<>0
INNER JOIN t_MeasureUnit t7 ON v1.FUnitID = t7.FItemID AND t7.FItemID<>0
LEFT OUTER JOIN t_User t2 ON v1.FBillerID = t2.FUserID AND t2.FUserID<>0
LEFT OUTER JOIN t_User t12 ON v1.FCheckerID = t12.FUserID AND t12.FUserID<>0
INNER JOIN t_ICItem t14 ON u1.FItemID = t14.FItemID AND t14.FItemID<>0
INNER JOIN t_MeasureUnit t17 ON u1.FUnitID = t17.FItemID AND t17.FItemID<>0
LEFT OUTER JOIN t_Stock t8 ON u1.FStockID = t8.FItemID AND t8.FItemID<>0
LEFT OUTER JOIN t_MeasureUnit t15 ON t4.FunitID = t15.FMeasureUnitID AND t15.FMeasureUnitID<>0
LEFT OUTER JOIN t_MeasureUnit t16 ON t14.FunitID = t16.FMeasureUnitID AND t16.FMeasureUnitID<>0
LEFT OUTER JOIN t_MeasureUnit t20 ON t4.FProductUnitID = t20.FMeasureUnitID AND t20.FMeasureUnitID<>0
LEFT OUTER JOIN t_Submessage t30 ON u1.FMaterieltype = t30.FInterID AND t30.FInterID<>0
LEFT OUTER JOIN t_stockPlace t33 ON u1.FSPID = t33.FSPID AND t33.FSPID<>0
LEFT OUTER JOIN t_Submessage t40 ON t14.FUseState = t40.FInterID AND t40.FInterID<>0
LEFT OUTER JOIN t_Supplier t18 ON i1.FSupplyID = t18.FItemID AND t18.FItemID<>0
LEFT OUTER JOIN t_Submessage t44 ON u1.FBackFlush = t44.FInterID AND t44.FInterID<>0
LEFT OUTER JOIN icbom t51 ON u1.FBomInterID = t51.FInterID AND t51.FInterID<>0
where 1=1 AND ((v1.FICMOinterID in (Select FInterID from ICMO i1 where FSuspend=0 and i1.FCancellation=0 and i1.FTranType=85 and i1.FStatus in (1,2,5)) Or v1.FICMOinterID in (Select FInterID from ICSubContract i1 where i1.FCancellation=0 and i1.FClassTypeID=1007105 and i1.FStatus>0 and i1.FClosed=0)) and v1.FStatus>0 and v1.FICMOinterID in (Select FInterID from ICMO i1 where FSuspend=0 and i1.FCancellation=0 and i1.FTranType in (85,571) and i1.FStatus in (1,2,5) and i1.FType in (1054,1055,11059))) order by v1.FInterID,u1.FEntryID
go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SET NOCOUNT OFF
go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select top 20000 v1.FTranType as FTranType,v1.FInterID as FInterID,i2.FAuxQty as FAuxQty,i2.FQty as FBaseQty,case when t4.FProductUnitID=0 then 0 else i2.FQty/t20.FCoEfficient end as FCUQty,u1.FEntryID as FEntryID,u1.FQtyScrap as FQtyScrap,u1.FAuxQtyScrap as FAuxQtyScrap,u1.FQtyPick as FBaseQtyPick,u1.FAuxQtyPick as FAuxQtyPick,u1.FBomInputQty as FBomQty,u1.FBomInputAuxQty as FBomInputAuxQty,round(( CASE WHEN u1.FStockQty > 0 AND u1.FDiscardQty > 0 THEN u1.FDiscardQty / u1.FStockQty ELSE 0 END)*100,2) as FTrueScrap,u1.FQtyLoss as FLostScrapQty,u1.FAuxQtyLoss as FLostScrapAuxQty,u1.FQtyMust as FQtyMust,u1.FAuxQtyMust as FAuxQtyMust,u1.FQty as FPPBomQty,u1.FAuxQty as FPPBomAuxQty,u1.FStockQty as FStockQty,u1.FAuxStockQty as FAuxStockQty,u1.fseldiscardqty as fseldiscardqty,u1.fseldiscardauxqty as fseldiscardauxqty,u1.fdiscardqty as fdiscardqty,u1.fdiscardauxqty as fdiscardauxqty,u1.FWIPQty as FWIPQty,u1.FWIPAuxQty as FWIPAuxQty,( SELECT ( CASE WHEN SUM(FQty) < 0 THEN 0 ELSE SUM(FQTY) END ) FROM t_lockStock WHERE FStockID = u1.FStockID AND FInterID = u1.FInterID AND FEntryID = u1.FEntryID and FTranType = v1.FTranType) /cast(t17.FCoefficient as float) as FLockQty,( SELECT ( CASE WHEN SUM(FQty) < 0 THEN 0 ELSE SUM(FQTY) END ) FROM t_lockStock WHERE FStockID = u1.FStockID AND FInterID = u1.FInterID AND FEntryID = u1.FEntryID and FTranType = v1.FTranType) as FLockAuxQty,u1.FQtySupply as FQtySupply,u1.FAuxQtySupply as FAuxQtySupply,t14.FQtyDecimal as FQtyDecimal,t14.FPriceDecimal as FPriceDecimal,u1.FTransLateAuxQty as FTransLateAuxQty,u1.FTransLateQty as FTransLateQty,u1.FSelTransLateAuxQty as FSelTransLateAuxQty,u1.FSelTransLateQty as FSelTransLateQty,u1.FChangeTimes as FChangeTimes from PPBOM v1 INNER JOIN PPBOMEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID<>0 AND v1.FType=1067
INNER JOIN ICSubContract i1 ON v1.FICMOInterID = i1.FInterID AND i1.FInterID<>0
INNER JOIN ICSubContractEntry i2 ON i1.FInterID = i2.FInterID AND i2.FInterID<>0 AND i2.FEntryID = v1.FOrderEntryID
INNER JOIN t_ICItem t4 ON v1.FItemID = t4.FItemID AND t4.FItemID<>0
INNER JOIN t_MeasureUnit t7 ON v1.FUnitID = t7.FItemID AND t7.FItemID<>0
LEFT OUTER JOIN t_User t2 ON v1.FBillerID = t2.FUserID AND t2.FUserID<>0
LEFT OUTER JOIN t_User t12 ON v1.FCheckerID = t12.FUserID AND t12.FUserID<>0
INNER JOIN t_ICItem t14 ON u1.FItemID = t14.FItemID AND t14.FItemID<>0
INNER JOIN t_MeasureUnit t17 ON u1.FUnitID = t17.FItemID AND t17.FItemID<>0
LEFT OUTER JOIN t_Stock t8 ON u1.FStockID = t8.FItemID AND t8.FItemID<>0
LEFT OUTER JOIN t_MeasureUnit t15 ON t4.FunitID = t15.FMeasureUnitID AND t15.FMeasureUnitID<>0
LEFT OUTER JOIN t_MeasureUnit t16 ON t14.FunitID = t16.FMeasureUnitID AND t16.FMeasureUnitID<>0
LEFT OUTER JOIN t_MeasureUnit t20 ON t4.FProductUnitID = t20.FMeasureUnitID AND t20.FMeasureUnitID<>0
LEFT OUTER JOIN t_Submessage t30 ON u1.FMaterieltype = t30.FInterID AND t30.FInterID<>0
LEFT OUTER JOIN t_stockPlace t33 ON u1.FSPID = t33.FSPID AND t33.FSPID<>0
LEFT OUTER JOIN t_Submessage t40 ON t14.FUseState = t40.FInterID AND t40.FInterID<>0
LEFT OUTER JOIN t_Supplier t18 ON i1.FSupplyID = t18.FItemID AND t18.FItemID<>0
LEFT OUTER JOIN t_Submessage t44 ON u1.FBackFlush = t44.FInterID AND t44.FInterID<>0
LEFT OUTER JOIN icbom t51 ON u1.FBomInterID = t51.FInterID AND t51.FInterID<>0
where 1=1 AND ((v1.FICMOinterID in (Select FInterID from ICMO i1 where FSuspend=0 and i1.FCancellation=0 and i1.FTranType=85 and i1.FStatus in (1,2,5)) Or v1.FICMOinterID in (Select FInterID from ICSubContract i1 where i1.FCancellation=0 and i1.FClassTypeID=1007105 and i1.FStatus>0 and i1.FClosed=0)) and v1.FStatus>0 and v1.FICMOinterID in (Select FInterID from ICMO i1 where FSuspend=0 and i1.FCancellation=0 and i1.FTranType in (85,571) and i1.FStatus in (1,2,5) and i1.FType in (1054,1055,11059)))
go
发现语句套用委外投料单变更单上的语句,取委外订单上的表,而不是取生产任务单上的表,请帮忙处理。
9.png(71.64KB)
推荐阅读