ERP SQL Server 3 - 4

3、打印数据源：按职员打印
取ERP员工信息,显示职员姓名、职员编号、所属部门，区分条码的字母前缀。
数据源sql
Select ‘TT’ 类型,t1.FName 职员姓名,t1.FNumber 职员编号,t2.FName 所属部门,
0 as ‘内码’,1.00 包装数量,getdate() 当前日期,Convert(varchar(10),getdate(),112) 日期,cast(0 as bit) 是否

from t_emp t1
inner join t_Department t2 on t1.FDepartmentID=t2.FItemID
where t1.FDeleted=0

4、其它入库：需要选单（只能选审核过的订单,入库都没入的）
采集器功能点：原材料入库
选单内容：只显示已审核且数量大于入库数量的单子
入库条码扫描必须是当前选单范围物料
入库数量不允许超单据累计已入库数量
需要生成erp其他入库单,并且可以上查到bos单据
需要更新bos单据入库数量
根据物料反写
原单选择数据源sql

Select DISTINCT t1.FBillNo '源单单号',t1.ADate '审核日期'
from t_BOS200000001 t1 (nolock)
inner join t_BOS200000001Entry2 t2 (nolock) on t1.FID=t2.FID
Left Join t_icitem t3 (nolock)On t2.FBase=t3.FItemID
Left Join t_MeasureUnit t4 (nolock) On t3.FUnitID = t4.FMeasureUnitID
Left Join t_supplier t5 (nolock) On t2.FBase  = t5.FItemID
left join t_stock t6 (nolock) on t3.FDefaultLoc=t6.fitemid
left join t_StockPlace t7 (nolock)on t7.FSPID =t3.FSPID
left join (SELECT  FInterID,FEntryID, SUM(flabelqty) AS fydqty
			FROM   BR_CodeInfo (nolock)
			WHERE ISNULL(FStatus,0) = 0 and FNumber='001'
			GROUP BY FInterID, FEntryID) t8 on  t8.finterid=t2.FID and  t8.fentryid=t2.fentryid  
where 1=1 and t1.AUser>0 and t2.FQty - t2.SQty > 0
选单汇总数据源sql
Select t1.FBillNo '源单单号',t3.FNumber '物料代码',t3.FName '物料名称',FModel '规格型号',t4.FName '计量单位',
t2.FQty-t2.SQty '应收数量',isnull(t8.fydqty,0) as '数量',t5.FName '供应商',t5.FNumber '供应商代码',
t2.FBase '物料内码',t4.FMeasureUnitID as '单位内码',cast(t5.FItemID as varchar(50)) '供应商内码',
t1.FID '订单内码',0 订单分录,t1.FID '源单内码',0 源单分录
from t_BOS200000001 t1 (nolock)
inner join t_BOS200000001Entry2 t2 (nolock) on t1.FID=t2.FID
Left Join t_icitem t3 (nolock)On t2.FBase=t3.FItemID
Left Join t_MeasureUnit t4 (nolock) On t3.FUnitID = t4.FMeasureUnitID
Left Join t_supplier t5 (nolock) On t2.FBase  = t5.FItemID
left join t_stock t6 (nolock) on t3.FDefaultLoc=t6.fitemid
left join t_StockPlace t7 (nolock)on t7.FSPID =t3.FSPID
left join (SELECT  FInterID,FEntryID, SUM(flabelqty) AS fydqty
			FROM   BR_CodeInfo (nolock)
			WHERE ISNULL(FStatus,0) = 0 and FNumber='001'
			GROUP BY FInterID, FEntryID) t8 on  t8.finterid=t2.FID and  t8.fentryid=t2.fentryid  
where 1=1 and t1.AUser>0 and t2.FQty - t2.SQty > 0

条码扫描数据源sql

Select  cast(v1.fqty as float) 数量,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 调入仓位管理
,'' 供应商,'' 供应商代码,0 供应商内码,0 订单内码,0 订单分录
,0 源单内码,0 源单分录,v1.Fisin 条码状态
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
Where 1=1 and t.FBatchManager=1 and t.FISKFPeriod=1

反写入库关联数量 sql

select b.FSourceBillNo,a.FItemID,a.FInterID,sum(b.FQty) as fsqty into #tmp1 from BR_CodeInfo a  inner join #BR_InsertStock b on b.FCode=a.FCode group by b.FSourceBillNo,a.FItemID,a.FInterID
update b set SQty=a.FsQty+b.SQty
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

提交生成前 反写上查关联
bos中添加单据流转关系
添加反写

declare @fsbillno  varchar(50)
select @fsbillno = (select  top 1 FSourceBillNo from #BR_InsertStock)
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  

根据行号反写
打印中心维护 sql 添加订单内码、订单分录

Select 
u.FBillNo '订单号',Convert(varchar(10),u.fdate,112) '订单日期',v.FIndex As '订单分录',v.fid '订单内码',
t.FNumber '物料代码',t.FName '物料名称',FModel '规格型号',0 '源单分录',0 '源单内码',
case when isnull(t.FBatchManager,0)=0 then null else Right(Convert(varchar(10),getdate(),112),6) end As  '批次号',
case when isnull(t.FISKFPeriod,0)=0 then null else Convert(datetime , Convert(varchar(10),getdate(),23)) end as '生产日期',
FQty-SQty '应收数量',t1.FName '计量单位',t.FSecCoefficient '换算率',
Cast(t.FBatchAppendQty As Decimal(28,4)) '包装数量', 
isnull(t5.fydqty,0) as '已打数量',v.FQty-isnull(t5.fydqty,0)'可打数量',
case when isnull(t.FISKFPeriod,0)=0 then 0 else cast(FKFPeriod as int) end  as '有效期',
'' as '辅助属性',t.FAuxClassID '辅助属性类别',cast(0 as int) as '是否在库',
getdate() As '当前日期',Right(Convert(varchar(10),getdate(),112),6) As '日期',
u.FiD '单据内码',v.Fbase '物料内码',
cast(t2.FItemID as varchar(50)) '供应商内码', 0 as '辅助属性内码',t1.FMeasureUnitID as '计量单位内码',
t3.fitemid as'仓库内码',t4.FSPID as '仓位内码',0 as'内码',u.fdate GYS订单日期
From t_BOS200000001 u (nolock)
Inner Join t_BOS200000001Entry2 v  (nolock) On u.FID = v.FID 
Left Join t_icitem t (nolock)On t.FItemID = v.fbase
Left Join t_MeasureUnit t1 (nolock) On t.FUnitID = t1.FMeasureUnitID
Left Join t_supplier t2 (nolock) On v.fbase  = t2.FItemID
left join t_stock t3 (nolock) on t.FDefaultLoc=t3.fitemid 
left join t_StockPlace t4 (nolock)on t4.FSPID =t.FSPID 
left join (SELECT  FInterID,FEntryID, SUM(flabelqty) AS fydqty
			FROM   BR_CodeInfo (nolock)
			WHERE ISNULL(FStatus,0) = 0 and FNumber='089'
			GROUP BY FInterID, FEntryID) t5 on  t5.finterid=v.fid and  t5.fentryid=v.fentryid  
where 1=1 and t.FBatchManager=1 and t.FISKFPeriod=1

采集器功能维护
源单选择数据源

Select DISTINCT t1.FBillNo '源单单号',t1.ADate '审核日期'
from t_BOS200000001 t1 (nolock)
inner join t_BOS200000001Entry2 t2 (nolock) on t1.FID=t2.FID
--Left Join t_icitem t3 (nolock)On t2.FBase=t3.FItemID
--Left Join t_MeasureUnit t4 (nolock) On t3.FUnitID = t4.FMeasureUnitID
--Left Join t_supplier t5 (nolock) On t2.FBase  = t5.FItemID
--left join t_stock t6 (nolock) on t3.FDefaultLoc=t6.fitemid
--left join t_StockPlace t7 (nolock)on t7.FSPID =t3.FSPID
--left join (SELECT  FInterID,FEntryID, SUM(flabelqty) AS fydqty
			--FROM   BR_CodeInfo (nolock)
			--WHERE ISNULL(FStatus,0) = 0 and FNumber='089'
			--GROUP BY FInterID, FEntryID) t8 on  t8.finterid=t2.FID and  t8.fentryid=t2.fentryid  
where 1=1 and t1.AUser>0 and t2.FQty - t2.SQty > 0

选单选择数据源

Select t1.FBillNo '源单单号',t3.FNumber '物料代码',t3.FName '物料名称',FModel '规格型号',t4.FName '计量单位',
t2.FQty-t2.SQty '应收数量',isnull(t8.fydqty,0) as '数量',t5.FName '供应商',t5.FNumber '供应商代码',
t2.FBase '物料内码',t4.FMeasureUnitID as '单位内码',cast(t5.FItemID as varchar(50)) '供应商内码',
t1.FID '订单内码',t2.Findex '订单分录',0 源单内码,0 源单分录
from t_BOS200000001 t1 (nolock)
inner join t_BOS200000001Entry2 t2 (nolock) on t1.FID=t2.FID
Left Join t_icitem t3 (nolock)On t2.FBase=t3.FItemID
Left Join t_MeasureUnit t4 (nolock) On t3.FUnitID = t4.FMeasureUnitID
Left Join t_supplier t5 (nolock) On t2.FBase  = t5.FItemID
--left join t_stock t6 (nolock) on t3.FDefaultLoc=t6.fitemid
--left join t_StockPlace t7 (nolock)on t7.FSPID =t3.FSPID
left join (SELECT  FInterID,FEntryID, SUM(flabelqty) AS fydqty
			FROM   BR_CodeInfo (nolock)
			WHERE ISNULL(FStatus,0) = 0 and FNumber='001'
			GROUP BY FInterID, FEntryID) t8 on  t8.finterid=t2.FID and  t8.fentryid=t2.fentryid  
where 1=1 and t1.AUser>0 and t2.FQty - t2.SQty > 0

条码扫描数据源

Select  cast(v1.fqty as float) 数量,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 调入仓位管理
,'' 供应商,'' 供应商代码,0 供应商内码,V1.FInterID 订单内码,V1.FEntryID 订单分录,0 源单内码,0 源单分录
,v1.Fisin 条码状态
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
Where 1=1

提交生成前 反写入库数量 根据行号

select b.FSourceBillNo,a.FEntryID,a.FInterID,b.FQty into #tmp from BR_CodeInfo a  inner join #BR_InsertStock b on b.FCode=a.FCode
update b set SQty=a.FQty+b.SQty
from #tmp a
inner join t_BOS200000001Entry2 b on b.FIndex=a.FEntryID and b.fid=a.FInterID
drop table #tmp

提交生成前 反写上查关联
bos中添加单据流转关系
添加反写

declare @fsbillno  varchar(50)
select @fsbillno = (select  top 1 FSourceBillNo from #BR_InsertStock)
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