这个单据状态:计划、确认、下达、结案 分别对应的sql如下
--计划
Select top 20000 v1.FBillNo as FBillNo,v1.FStatus as FStatus,v1.FTranType as FTranType,v1.FInterID as FInterID,
case when v1.FCancellation=1 then 'Y' else '' end as FCancellation,CASE V1.FSuspend WHEN 0 THEN '' ELSE 'Y' END as FSuspend,t9.FQtyDecimal as FQtyDecimal,
v1.FType as FICMOType2,v1.FWorktypeID as FWorkTypeID2, 0 As FBOSCloseFlag from ICMO v1 INNER JOIN t_ICItem t9 ON v1.FItemID = t9.FItemID AND t9.FItemID<>0
where 1=1 AND (v1.FTranType = 85 AND ( v1.FType <> 11060 ) AND (v1.FStatus=0 AND v1.FCancellation = 0)) order by v1.FInterID
---确认
Select top 20000 v1.FBillNo as FBillNo,v1.FStatus as FStatus,v1.FTranType as FTranType,v1.FInterID as FInterID,
case when v1.FCancellation=1 then 'Y' else '' end as FCancellation,CASE V1.FSuspend WHEN 0 THEN '' ELSE 'Y' END as FSuspend,t9.FQtyDecimal as FQtyDecimal,
v1.FType as FICMOType2,v1.FWorktypeID as FWorkTypeID2, 0 As FBOSCloseFlag from ICMO v1 INNER JOIN t_ICItem t9 ON v1.FItemID = t9.FItemID AND t9.FItemID<>0
where 1=1 AND (v1.FTranType = 85 AND ( v1.FType <> 11060 ) AND (v1.FStatus=5 AND v1.FCancellation = 0)) order by v1.FInterID
--下达
Select top 20000 v1.FBillNo as FBillNo,v1.FStatus as FStatus,v1.FTranType as FTranType,v1.FInterID as FInterID,
case when v1.FCancellation=1 then 'Y' else '' end as FCancellation,CASE V1.FSuspend WHEN 0 THEN '' ELSE 'Y' END as FSuspend,t9.FQtyDecimal as FQtyDecimal,
v1.FType as FICMOType2,v1.FWorktypeID as FWorkTypeID2, 0 As FBOSCloseFlag from ICMO v1 INNER JOIN t_ICItem t9 ON v1.FItemID = t9.FItemID AND t9.FItemID<>0
where 1=1 AND (v1.FTranType = 85 AND ( v1.FType <> 11060 ) AND ((v1.FStatus=1 or v1.FStatus=2) AND v1.FCancellation = 0)) order by v1.FInterID
--结案
Select top 20000 v1.FBillNo as FBillNo,v1.FStatus as FStatus,v1.FTranType as FTranType,v1.FInterID as FInterID,
case when v1.FCancellation=1 then 'Y' else '' end as FCancellation,CASE V1.FSuspend WHEN 0 THEN '' ELSE 'Y' END as FSuspend,t9.FQtyDecimal as FQtyDecimal,
v1.FType as FICMOType2,v1.FWorktypeID as FWorkTypeID2, 0 As FBOSCloseFlag from ICMO v1 INNER JOIN t_ICItem t9 ON v1.FItemID = t9.FItemID AND t9.FItemID<>0
where 1=1 AND (v1.FTranType = 85 AND ( v1.FType <> 11060 ) AND (v1.FStatus=3 AND v1.FCancellation = 0)) order by v1.FInterID