ERP SQL Server 7 - 8

7、其它出库：不选单
采集器功能点：原材料出库 入库条码必须有汇报记录且没有不良数量，条码数量对应任务派工完工数量 需要生成erp其他出库单,并且可以上查到bos单据 需要更新bos单据出库数量
条码扫描数据源sql

Select distinct v1.FLabelQty 数量,t4.fzqty 完工数量,t.FNumber 物料代码,t.FName 物料名称,isnull(t.FModel,'') 规格型号,
t1.FName 计量单位,v1.fbatchno 批次号,IsNull(t12.FName,'') 仓库名称,IsNull(t13.FName,'') 仓位名称,
v1.fstockid 仓库内码,v1.fspid 仓位内码,isnull(t12.FISStockMgr,0) 仓位管理,
v1.FItemID 物料内码,v1.FUnitID 单位内码,t.FBatchManager 批次管理,
v1.FCode 条码信息,
IsNull(t12.FName,'') 调出仓库,IsNull(t13.FName,'') 调出仓位,isnull(t12.FItemID,0)  调出仓库内码,isnull(t13.FSPID,0) 调出仓位内码,ISNull(t12.FIsStockMgr,0) 调出仓位管理,
'' 调入仓库,'' 调入仓位,0 调入仓库内码,0 调入仓位内码,0 调入仓位管理,v1.Fisin 条码状态,cast(v1.FLabelQty-isnull(t3.fsqty,0) as float) 未派工数量
From BR_CodeInfo v1
Inner Join t_icitem t On t.FItemID = v1.FItemID
Left Join t_MeasureUnit t1 On t1.FMeasureUnitID = v1.FUnitID 
Left Join t_Stock t12 On v1.fstockid= t12.FItemID
Left Join t_stockplace t13 On v1.fspid= t13.FSPID
inner join ( select FCode fcode1,FProcFlag,FSourceBillNo,FItemID from  [M10]..BR_InsertStock ) b on v1.FCode=b.fcode1
inner join [M10]..M_Task_111 d on d.FPKValue=b.fcode1 and d.fitemid=b.FItemID
inner join (select T1.FLQty,fbillno,sum(fqty) fzqty
			from m10..M_TaskReportHB t1 (nolock) where t1.flqty=0
			group by t1.FLQty,FBillNo) t4 on t4.FBillNo=d.FBillNo
left join (select T2.fpkvalue,fitemid,sum(fpqty) fsqty
		   from [M10]..M_Task_111 t2 (nolock) WHERE isnull(fstatus,0)<>9
		   group by T2.fpkvalue,fitemid) t3 on b.fcode1=t3.fpkvalue and t3.fitemid=b.FItemID
Where 1=1 and v1.FLabelQty=t4.fzqty and v1.FCode like '%CL%' and FISIN<>2

反写出库关联数量

select b.FSourceBillNo,a.FItemID,a.FInterID,sum(b.FQty) as fsqty into #tmp1 from BR_CodeInfo a
inner join [M10]..BR_InsertStock b on b.FCode=a.FCode and b.FProcFlag='S'
inner join #BR_InsertStock c on c.FCode=a.FCode and b.FSourceBillNo<>''
group by b.FSourceBillNo,a.FItemID,a.FInterID
update b set RQty=a.fsqty+b.RQty
from #tmp1 a
inner join t_BOS200000001 b1 on b1.FBillNo=a.FSourceBillNo
inner join t_BOS200000001Entry2 b on b1.FID=b.FID and b.FBase=a.FItemID
drop table #tmp1

反写上查关联
新建单据流程 关系复制
反写 ICStockBillEntry 4个字段

declare @fsbillno varchar(50)
select @fsbillno = (
select top 1 a2.FSourceBillNo from #BR_InsertStock a1
inner join [M10]..BR_InsertStock a2 on a1.FCode=a2.FCode)
update a set
a.FSourceBillNo = b.FBillNo,a.FSourceTranType = b.FClassTypeID,a.FSourceEntryID = b.FID,a.FSourceInterId = b.FID
from ICStockBillEntry a
inner join t_BOS200000001 b on b.FBillNo=@fsbillno
where a.FInterID = @FinterID 

8、M10报表
在M10前台显示，发布到数据中心-基础报表
输入条码号，可以看到汇报序号，条码号，打印时间，汇报数量，打印用户，入库单号，出库单号，汇报时间。 （同一标签多次汇报，逐一显示该标签记录的汇报时间，汇报数量和对应的汇报序号，其余重复信息只显示一条) 过滤条件模糊查询，可以查看全部已汇报且没有不良数量的数据

select 
汇报序列号,
case when rank > 1 then  '' else 条码号 end as 条码号,
case when rank > 1 then  null else 打印时间 end as 打印时间,
汇报数量,
case when rank > 1 then  '' else 打印用户 end as 打印用户,
case when rank > 1 then  '' else 入库单号 end as 入库单号,
case when rank > 1 then  '' else 出库单号 end as 出库单号,
汇报时间
from
(
		select ROW_NUMBER() over(PARTITION by t.条码号 order by t.条码号) rank,*
		from (
				Select 
				distinct e.fid 汇报序列号,v1.FCode 条码号,v1.FCreateDate 打印时间,e.FQty 汇报数量,v1.FCreater 打印用户,q1.FBillNo 入库单号,q2.FBillNo 出库单号,d.FCreateDate 汇报时间
				From BR_CodeInfo v1
				inner join BR_InsertStock b on b.FProcFlag='S' and v1.FCode=b.fcode
				inner join ERP1_t_icitem c on v1.fitemid=c.fitemid
				inner join M_Task_111 d on d.FPKValue=b.Fcode and d.fitemid=b.FItemID
				inner join  M_TaskReportHB  e on e.FBillNo=d.FBillNo and e.FLQty=0
				inner join BR_InsertStock q1 on q1.FBillNo like '%QIN%' and q1.FCode=v1.FCode
				left join BR_InsertStock q2 on q2.FBillNo like '%QOUT%' and q2.FCode=v1.FCode 
				where 1=1
				) t
)t1
 where 1=1 and 条码号 like '%#TMH#%'