SELECT W.cWhCode, W.cWhName, I.cInvCode, I.cInvAddCode, I.cInvName, I.cInvStd, I.cInvCCode , IC.cInvCName, CU_M.cComUnitName AS cInvM_Unit, CASE WHEN I.iGroupType = 0 THEN NULL WHEN I.iGrouptype = 2 THEN CU_A.cComUnitName WHEN I.iGrouptype = 1 THEN CU_G.cComUnitName END AS cInvA_Unit,CASE WHEN I.iGroupType = 0 THEN NULL WHEN I.iGroupType = 2 THEN (CASE WHEN CS.iQuantity = 0.0 OR CS.iNum = 0.0 THEN NULL ELSE CS.iQuantity/CS.iNum END) WHEN I.iGroupType = 1 THEN CU_G.iChangRate END AS iExchRate, I.cInvDefine1,I.cInvDefine2,I.cInvDefine3,I.cInvDefine4,I.cInvDefine5,I.cInvDefine6,I.cInvDefine7,I.cInvDefine8,I.cInvDefine9,I.cInvDefine10, I.cInvDefine11,I.cInvDefine12,I.cInvDefine13,I.cInvDefine14,I.cInvDefine15,I.cInvDefine16, CS.cFree1 , CS.cFree2, CS.cFree3, CS.cFree4, CS.cFree5, CS.cFree6, CS.cFree7, CS.cFree8, CS.cFree9, CS.cFree10 ,cs.cBatch, cs.EnumName As iSoTypeName, cs.csocode as SOCode, convert(nvarchar,cs.isoseq) as iRowNo,cs.cvmivencode,v1.cvenabbname as cvmivenname , isnull(E.enumname,N'') as cMassUnitName,CS.dVDate, CS.dMdate,CS.iMassDate, (iQuantity) AS iQtty,( CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN iQuantity/ CU_G.iChangRate END) AS iNum, CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN iQuantity ELSE IsNull(fStopQuantity,0) END AS iStopQtty,
CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN iQuantity/ CU_G.iChangRate END) ELSE (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(fStopNum,0) WHEN iGroupType = 1 THEN fStopQuantity/ CU_G.iChangRate END) END AS iStopNum,
(fInQuantity) AS fInQtty, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) WHEN iGroupType = 1 THEN fInQuantity/ CU_G.iChangRate END) AS fInNum, (fTransInQuantity) AS fTransInQtty, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN fTransInQuantity/ CU_G.iChangRate END) AS fTransInNum, (ISNULL(fInQuantity,0) + ISNULL(fTransInQuantity,0)) AS fInQttySum, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) + ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN (ISNULL(fInQuantity,0) + ISNULL(fTransInNum,0))/ CU_G.iChangRate END) AS fInNumSum, (fOutQuantity) AS fOutQtty, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) WHEN iGroupType = 1 THEN fOutQuantity/ CU_G.iChangRate END) AS fOutNum, (fTransOutQuantity) AS fTransOutQtty, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN fTransOutQuantity/ CU_G.iChangRate END) AS fTransOutNum, (ISNULL(fOutQuantity,0) + ISNULL(fTransOutQuantity,0)) AS fOutQttySum , (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) + ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN (ISNULL(fOutQuantity,0) + ISNULL(fTransOutNum,0))/ CU_G.iChangRate END) AS fOutNumSum, (fDisableQuantity) AS fDisableQtty, (CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fDisableNum,0) WHEN iGroupType = 1 THEN fDisableQuantity/ CU_G.iChangRate END) AS fDisableNum, (CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END END) AS fAvailQtty, (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0) - IsNull(fStopNum,0) END ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0) - IsNull(fStopNum,0) END END WHEN iGroupType = 1 THEN (CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END ELSE CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END END)/CU_G.iChangRate ELSE NULL END) AS fAvailNum,CS.iSOType,CS.iSODID FROM v_CurrentStock CS inner join dbo.Inventory I ON I.cInvCode = CS.cInvCode left join dbo.InventoryClass IC ON IC.cInvCCode = I.cInvCCode LEFT OUTER JOIN dbo.ComputationUnit CU_G ON I.cSTComUnitCode =CU_G.cComUnitCode LEFT OUTER JOIN dbo.ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode LEFT OUTER JOIN dbo.Warehouse W ON CS.cWhCode = W.cWhCode left join vendor v1 on v1.cvencode = cs.cvmivencode LEFT OUTER JOIN dbo.v_aa_enum E with (nolock) on E.enumcode=convert(nchar,CS.cMassUnit) and E.enumType=N'ST.MassUnit'
WHERE 1=1 AND 1=1
--以下为料号过滤条件
And ((I.cInvCode >= N'6F339001') And (I.cInvCode <= N'6F339001'))