金蝶mysql_金蝶数据库后台追踪

SET NOCOUNT ON

SELECT 0 AS F8F6F1018D8A04952B274D21C4EB0F6CB,0 AS FAuxBadQty,CAST(t4.FPlanPrice AS FLOAT) * CAST(t7.FCoefficient AS FLOAT) ASFAuxPlanPrice,

t4.FUnitGroupIDas FItemUnitGroupID, 0 AS FAuxPrice,t4.FAuxClassID ASFAuxPropCls,

v1.FAuxPropID,tAux.FNameAS FAuxPropName,tAux.FNumber AS FAuxPropNum,CASE WHEN t4.FProChkMde=352 THEN CASE WHEN (v1.FAuxQty-v1.FAuxCommitQty)>0 then (v1.FAuxQty-v1.FAuxCommitQty)*t7.FCoefficient/t11.FCoefficient ELSE 0 END ELSE CASE WHEN (v1.FAuxQtyPass-v1.FAuxCommitQty)>0 then (v1.FAuxQtyPass-v1.FAuxCommitQty)*t7.FCoefficient/t11.FCoefficient ELSE 0 END END ASFAuxQty,

v1.FAuxQtyForItem,CASE WHEN t4.FProChkMde=352 THEN CASE WHEN (v1.FAuxQty-v1.FAuxCommitQty)>0 then (v1.FAuxQty-v1.FAuxCommitQty)*t7.FCoefficient/t11.FCoefficient ELSE 0 END ELSE CASE WHEN (v1.FAuxQtyPass-v1.FAuxCommitQty)>0 then (v1.FAuxQtyPass-v1.FAuxCommitQty)*t7.FCoefficient/t11.FCoefficient ELSE 0 END END ASFAuxQtyMust,

v1.FAuxQtyScrap,0 AS FBadQty,t10.FName AS FBaseUnitName,v1.FGMPBatchNo ASFBatchNo,

v1.FBillNO,v1.FBillNoAS FBillNo0,v1.FBillNo AS FBillNo1,v1.FBillNo ASFBillNo2,1058 AS FChkPassItem,(SELECT FName FROM t_SubMessage WHERE FTypeID = 244 AND FInterID = 1058) ASFChkPassItemName,

(SELECT FID FROM t_SubMessage WHERE FTypeID = 244 AND FInterID = 1058) ASFChkPassItemNumber,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN t8.FSPID ELSE 0 END ASFDCSPID,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN t8.FName ELSE '' END ASFDCSPIDName,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN t8.FNumber ELSE '' END ASFDCSPIDNumber,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN t4.FDefaultLoc ELSE 0 END ASFDCStockID,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN t5.FName ELSE '' END ASFDCStockIDName,CASE WHEN t5.FTypeID NOT IN (501,502,503,504) THEN CASE WHEN (SELECT FValue FROM t_Systemprofile WHERE FCategory='IC' AND FKey='UseShortNumber')='0' THEN t5.FNumber else t5.FShortNumber END ELSE '' END ASFDCStockIDNumber,

v1.FBillNOAS FICMOBillNo,v1.FInterID AS FICMOInterID,v1.FInterID,v1.FItemID,t4.FName ASFMatName,

t4.FNumberasFMatNumber ,t4.Fmodel,v1.FMTONo,t4.FName,v1.FNote,t4.FNumber,v1.FPlanMode,

tPlanMode.FNameAS FPlanModeName,tPlanMode.FID AS FPlanModeNumber,0 ASFPPBomEntryID,

t4.FPriceDecimal,CASE '0' WHEN '0' THEN CASE WHEN t4.FProChkMde=352 THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END else CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END else CASE WHEN t4.FProChkMde=352 then CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (not exists (SELECT a.FInterID FROM ICQCBill a where a.Finstockinterid in (select b.Finterid from QMicmockRequest b WHERE b.FICMOInterID=v1.FInterID ))) THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END END END ASFQty,

t4.FQtyDecimal,v1.FQtyForItem,v1.FQtyScrap,v1.FSampleBreakAuxQty,v1.FSampleBreakQty,CASE WHEN t4.FSecCoefficient>0 THEN v1.FSampleBreakQty /t4.FSecCoefficient ELSE (CASE WHEN t501.FCoefficient >0 THEN v1.FSampleBreakQty/t501.FCoefficient ELSE 0 END) END ASFSampleBreakSecQty,0 AS FSecBadQty,Case When t4.FSecCoefficient>0 Then t4.FSecCoefficient Else t501.FCoefficient End ASFSecCoefficient,

(Case When t4.FSecCoefficient>0 Then (CASE '0' WHEN '0' THEN CASE WHEN t4.FProChkMde=352 THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END else CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END else CASE WHEN t4.FProChkMde=352 then CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (not exists (SELECT a.FInterID FROM ICQCBill a where a.Finstockinterid in (select b.Finterid from QMicmockRequest b WHERE b.FICMOInterID=v1.FInterID ))) THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END END END)/t4.FSecCoefficient Else Case When t501.FCoefficient>0 Then (CASE '0' WHEN '0' THEN CASE WHEN t4.FProChkMde=352 THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END else CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END else CASE WHEN t4.FProChkMde=352 then CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (not exists (SELECT a.FInterID FROM ICQCBill a where a.Finstockinterid in (select b.Finterid from QMicmockRequest b WHERE b.FICMOInterID=v1.FInterID ))) THEN CASE WHEN (v1.FQty-v1.FCommitQty)>0 then (v1.FQty-v1.FCommitQty) else 0 END ELSE CASE WHEN (v1.FQtyPass-v1.FCommitQty)>0 then (v1.FQtyPass-v1.FCommitQty) else 0 END END END END)/t501.FCoefficient Else 0 End End) ASFSecQty,CASE WHEN t4.FSecCoefficient>0 THEN v1.FQtyForItem /t4.FSecCoefficient ELSE (CASE WHEN t501.FCoefficient >0 THEN v1.FQtyForItem/t501.FCoefficient ELSE 0 END) END ASFSecQtyForItem,CASE WHEN t4.FSecCoefficient>0 THEN v1.FQtyScrap /t4.FSecCoefficient ELSE (CASE WHEN t501.FCoefficient >0 THEN v1.FQtyScrap/t501.FCoefficient ELSE 0 END) END ASFSecQtyScrap,

t501.FNameASFSecUnitName,v1.FSelDiscardStockInAuxQty,v1.FSelDiscardStockInQty,CASE WHEN t4.FSecCoefficient>0 THEN v1.FSelDiscardStockInQty /t4.FSecCoefficient ELSE (CASE WHEN t501.FCoefficient >0 THEN v1.FSelDiscardStockInQty/t501.FCoefficient ELSE 0 END) END ASFSelDiscardStockInSecQty,

v1.FTranTypeAS FSelTranTypeID,(SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) ASFSelTranTypeName,

(SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) ASFSelTranTypeNumber,

v1.FBillNOAS FSourceBillNo,0 AS FSourceEntryID,v1.FInterID AS FSourceInterID,v1.FTranType ASFSourceTranType,

t4.FTrack,t11.FItemIDAS FUnitID,t11.FName AS FUnitName,t11.FNumber ASFUnitNumber,

v1.FWorkShop,t2.FNameAS FWorkShopName,t2.FNumber ASFWorkShopNumberINTO #TempF8F6F1018D8A04952B274D21C4EB0F6CB FROMICMO v1LEFT OUTER JOIN t_Department t2 ON v1.FWorkShop=t2.FItemIDINNER JOIN t_ICItem t4 ON v1.FItemID=t4.FItemIDINNER JOIN t_MeasureUnit t7 ON v1.FUnitID=t7.FItemIDLEFT OUTER JOIN t_SubMessage tplanmode ON v1.FPlanMode=tplanmode.FInterIDLEFT OUTER JOIN t_AuxItem taux ON v1.FAuxPropID=taux.FItemIDLEFT OUTER JOIN t_Stock t5 ON t4.FDefaultLoc=t5.FItemIDINNER JOIN t_MeasureUnit t10 ON t4.FUnitID=t10.FItemIDLEFT OUTER JOIN t_MeasureUnit t501 ON t4.FSecUnitID=t501.FItemIDLEFT OUTER JOIN t_stockPlace t8 ON t4.FSPID=t8.FSPIDINNER JOIN t_MeasureUnit t11 ON t4.FStoreUnitID=t11.FItemIDWHERE (v1.FInterID=11190)AND(

(EXISTS(Select 1 from t_SystemProfile Where FCategory='SH' and FKey='ReportInStockExceedsUpperLimit' AND FValue = 0) AND v1.FAuxCommitQty

)OR (v1.FQtyScrap+v1.FQtyForItem+v1.FSampleBreakQty>v1.FSelDiscardStockInQty)

)INSERT INTO#TempF8F6F1018D8A04952B274D21C4EB0F6CB(F8F6F1018D8A04952B274D21C4EB0F6CB,FAuxPrice,FAuxPlanPrice,FItemUnitGroupID,FAuxPropCls,FAuxPropID,FAuxPropName,FAuxPropNum,FAuxQty,FBaseUnitName,FBatchNo,FBillNO,FDCSPID,FDCSPIDName,FDCSPIDNumber,FDCStockID,FDCStockIDName,FDCStockIDNumber,FICMOBillNo,FICMOINterid,FInterID,FItemID,FMatName,FMatNumber,Fmodel,FName,FNote,FNumber,FPPBOMEntryID,FPriceDecimal,

FQty,FQtyDecimal,FSecCoefficient,FSecQty,FSecUnitName,FSelTranTypeID,FSelTranTypeName,FSelTranTypeNumber,FSourceBillNo,FSourceEntryID,FSourceInterID,FSourceTranType,FTrack,FUnitID,FUnitName,FUnitNumber,FWorkShop,FWorkShopName,FWorkShopNumber,FPlanMode,FMtoNO,FPlanModeName,FPlanModeNumber,

FChkPassItem,FChkPassItemName,FChkPassItemNumber,

FSelDiscardStockInSecQty,FSelDiscardStockInAuxQty,FSelDiscardStockInQty,FBadQty,FAuxBadQty,FSecBadQty,FSampleBreakQty,FSampleBreakAuxQty,FSampleBreakSecQty,

FQtyScrap,FAuxQtyScrap,FSecQtyScrap,FAuxQtyMust)SELECT 1,CAST(t4.FPlanPrice AS FLOAT) * CAST(t7.FCoefficient AS FLOAT) AS FAuxPrice,t4.FPlanPrice * t7.FCoefficient AS FAuxPlanPrice,t4.FUnitGroupID as FItemUnitGroupID,t4.FAuxClassID AS FAuxPropCls,ISNULL(u2.FAuxPropID,0) as FAuxPropID,ISNULL(tAux.FName,'') as FAuxPropName,ISNULL(tAux.FNumber,'') as FAuxPropNum, CASE WHEN (u2.FAuxQtyMust - isnull(u2.FAuxQty, 0))<0 THEN 0 ELSE (u2.FAuxQtyMust - isnull(u2.FAuxQty, 0)) END AS FAuxQty, t10.FName AS FBaseUnitName,u2.FBatchNO as FBatchNo,v1.FBillNO,(CASE WHEN ISNULL(u2.FSPID,0)>0 THEN u2.FSPID ELSE t4.FSPID END) AS FDCSPID,(CASE WHEN ISNULL(u2.FSPID,0)>0 THEN t12.FName ELSE t11.FName END ) AS FDCSPIDName,(CASE WHEN ISNULL(u2.FSPID,0)>0 THEN t12.FNumber ELSE t11.FNumber END ) AS FDCSPIDNumber,(CASE WHEN ISNULL(u2.FStockID,0)>0 THEN u2.FStockID ELSE t4.FDefaultLoc END) AS FDCStockID,(CASE WHEN ISNULL(u2.FStockID,0)>0 THEN t51.FName ELSE t5.FName END) AS FDCStockIDName, CASE WHEN (SELECT FValue FROM t_Systemprofile WHERE FCategory='IC' AND FKey='UseShortNumber')='0' THEN (CASE WHEN ISNULL(u2.FStockID,0)>0 THEN t51.FNumber ELSE t5.FNumber END) else (CASE WHEN ISNULL(u2.FStockID,0)>0 THEN t51.FShortNumber ELSE t5.FShortNumber END) END AS FDCStockIDNumber, v1.FBillNO AS FICMOBillNo,v1.FInterid as FICMOINterid,v1.FInterID,u2.FItemID,t4.FName AS FMatName,t4.FNumber AS FMatNumber,t4.Fmodel,t4.FName,t4.FNote,t4.FNumber,u2.FENTRYID as FPPBOMEntryID,t4.FPriceDecimal, CASE WHEN (u2.FQtyMust - isnull(u2.FQty, 0))<0 THEN 0 ELSE (u2.FQtyMust - isnull(u2.FQty, 0)) END AS FQty, t4.FQtyDecimal,t4.FSecCoefficient as FSecCoefficient,(Case When t4.FSecCoefficient>0 Then CASE WHEN (u2.FQtyMust - isnull(u2.FQty, 0))<0 THEN 0 ELSE (u2.FQtyMust - isnull(u2.FQty, 0)) END/t4.FSecCoefficient Else 0 End) AS FSecQty, t501.FName AS FSecUnitName,v1.FTranType AS FSelTranTypeID, (SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) AS FSelTranTypeName,(SELECT FName FROM v_ICTransType WHERE FID=v1.FTranType) AS FSelTranTypeNumber, v1.FBillNO AS FSourceBillNo,u2.FEntryID AS FSourceEntryID,v1.FInterID AS FSourceInterID,v1.FTranType AS FSourceTranType, t4.FTrack,u2.FUnitID,t7.FName AS FUnitName,t7.FShortNumber AS FUnitNumber,v1.FWorkShop,t2.FName AS FWorkShopName, t2.FShortNumber AS FWorkShopNumber,u2.FPlanMode,u2.FMtoNO,tPlanMode.FName AS FPlanModeName,tPlanMode.FID AS FPlanModeNumber ,1058,(SELECT FName FROM t_SubMessage WHERE FTypeID = 244 AND FInterID = 1058),(SELECT FID FROM t_SubMessage WHERE FTypeID = 244 AND FInterID = 1058),0,0,0,0,0,0,0,0,0,0,0,0,CASE WHEN (u2.FAuxQtyMust - isnull(u2.FAuxQty, 0))<0 THEN 0 ELSE (u2.FAuxQtyMust - isnull(u2.FAuxQty, 0)) END AS FAuxQtyMust FROM ICMO v1 left join ppbom v2 on v1.finterid=v2.FICMOinterID left join PPBomEntry u2 on v2.FinterID=u2.FInteriD left join t_Department t2 on v1.FWorkShop=t2.FItemID INNER join t_ICItem t4 on u2.FItemID=t4.FItemID INNER join t_MeasureUnit t10 on t4.FUnitID=t10.FItemID INNER join t_MeasureUnit t7 on u2.FUnitID=t7.FItemID left join t_Stock t5 on t4.FDefaultLoc=t5.FItemID AND t5.FTypeID=500 --取物料上的仓库

LEFT JOIN t_Stock t51 ON u2.FStockID=t51.FItemID AND t51.FTypeID=500--取投料单的仓库

left join t_Supplier t8 on v1.FSupplyID=t8.FItemID left join t_MeasureUnit t501 ON t4.FSecUnitID=t501.FItemID LEFT OUTER JOIN t_stockPlace t11 ON t4.FSPID=t11.FSPID--取物料的仓位

LEFT OUTER JOIN t_stockPlace t12 ON u2.FSPID=t12.FSPID --取投料单的仓位

left join t_submessage tPlanMode on u2.fplanmode=tPlanMode.finteridLEFT OUTER JOIN t_AuxItem tAux ON ISNULL(u2.FAuxPropID,0)=tAux.FItemIDwhere u2.FMaterielType IN (372,373,374) AND v2.FICMOinterid in ( select FInterID from ICMO v1 Where (v1.FInterID=11190) )--代管仓特殊处理,不处:仓库，仓位的携带(业务背景：生产的东西不能代管)

Updatet1Set t1.FDCSPID=0,t1.FDCSPIDName='',t1.FDCSPIDNumber='',t1.FDCStockID=0,t1.FDCStockIDName='',t1.FDCStockIDNumber=''

From#TempF8F6F1018D8A04952B274D21C4EB0F6CB t1Left Join t_Stock t2 ON t1.FDCStockID=t2.FItemIDWHERE ISNULl(t2.FTypeID,0)=503

SELECT * FROM #TempF8F6F1018D8A04952B274D21C4EB0F6CB where FQty<>0 ORDER BYFICMOInterID,F8F6F1018D8A04952B274D21C4EB0F6CBDROP TABLE #TempF8F6F1018D8A04952B274D21C4EB0F6CB