金蝶K3 工单齐料查询和锁库存储过程

-- 查询库存视图，包括物料ID，产品信息，锁库数量
create view view_p2mes_bom_stock as
	select 齐料率 = IIF(库存数量 IS NULL, 0, IIF(库存数量 - ISNULL(锁库数量, 0) < 物料需求量, 库存数量, 物料需求量) / 物料需求量 * 100),
		n6.FName 订单类型, n1.*, n3.锁库数量, n2.FNumber 产品编号, n2.FName 产品名称, n2.FModel 产品型号, n4.FOrderBillNo 销售订单编号, n5.FName 工厂名称
	from
	(
		select
		 m1.FInterID as 工单ID, m1.FBillNo as 工单编号, m1.FTranType as 生产任务单事务类型, m1.FStatus as 状态, m1.FWorkTypeID 订单类型ID, m1.FWorkShop as 生产车间ID, 
			m1.FItemID as 产品ID, m1.FOrderInterID as 销售订单ID, m1.FQty as 工单数量, m1.FPlanCommitDate as 计划开工时间, m1.FPlanFinishDate as 计划完成日期,
			m2.*,
			m3.FStockID, m3.FQty 库存数量 , 物料需求量 = m2.物料数量 * m1.FQty * m2.物料用量
		from icmo m1,
		(
			-- 求子物料信息
			select t1.FInterID 物料单ID, t3.FBOMNumber 物料单编号, t3.FQty 物料数量, t1.FItemID 物料ID, t1.FStockID 库存ID, t2.FName 物料名称, t2.FNumber 物料编号, t2.FModel 子物料型号, t1.FQty 物料用量
			from icbomchild t1 left join t_icitem t2 on t1.FItemID = t2.FItemID
			left join icbom t3 on t1.FInterID = t3.FInterID
			-- order by t1.FInterID
		) m2
		left join ICInventory m3 on m2.物料ID = m3.FItemID and m3.FStockID = m2.库存ID
		where m1.FBomInterID = m2.物料单ID and m1.FBillNO like 'WO2%' and m1.FStatus = 1
	) n1
	left join t_icitem n2 on n1.产品ID = n2.FItemID
	left join (
				select FStockID, FItemID, 锁库数量 = sum(x1.Fqty) 
				from t_Lockstock x1
				group by x1.FStockID, x1.FItemID
			) n3 on n1.物料ID = n3.FItemID and n1.库存ID = n3.FStockID
	left join PPBOM n4 on n4.FInterID = n1.销售订单ID
	left join t_department n5 on n1.生产车间ID = n5.FItemID
	left join t_worktype n6 on n6.FInterID = n1.订单类型ID
	-- order by 锁库数量 desc, 工单ID, 物料名称

select 齐料率 = avg(齐料率), 产品型号, 产品名称, 产品编号, 工单编号, 销售订单ID, 工单数量, 计划开工时间, 计划完成日期, 物料单ID, 物料单编号, 销售订单编号, 工厂名称, 订单类型, 工单ID
from view_p2mes_bom_stock x1
group by 产品型号, 产品名称, 产品编号, 工单编号, 销售订单ID, 工单数量, 计划开工时间, 计划完成日期, 物料单ID, 物料单编号, 销售订单编号, 工厂名称, 订单类型, 工单ID
having avg(齐料率) = 100
order by 齐料率 desc, 计划开工时间, 计划完成日期, 工单编号, 销售订单ID, 物料单ID, 产品编号, 物料单编号

create procedure SP_P2MES_LOCK_STOCK(@WO_NO varchar(100)) AS
begin
	insert into t_lockstock(FTranType, FInterID, FEntryID, FItemID, FStockID, FQty, FOrgQty, FDate, FAuxPropID)
	select 88, a1.FInterID, a1.FEntryID, FItemID, FStockID, FAuxQtyMust, 0, NULL, FAuxPropID
	from PPBOMEntry a1, (select FInterID from ICMO where FBillNo = @WO_NO ) a2
	where a1.FICMOInterID = a2.FInterID
end