用友U8工艺线路，物料清单资料维护（BOM），生产订单子件信息

--- 工艺路线一览表 ---
SELECT 
  a.cinvcode as 物料编码, 
  g.cInvName AS 物料名称, 
  g.cInvStd AS 规格型号, 
  '被公用' = case when SharingPartId = '0' then '否' else '是' end, 
  c.OpSeq as 工序行号, 
  d.OperationId AS 标准工序资料ID,
  c.Description as 工序说明, 
  e.Description as 工作中心, 
  '类型' = case when b.RountingType = '1' then '主' else '替代' end, 
  b.Version as 版本号, 
  b.IdentDesc as 替代说明, 
  f.cPersonName as 建档人, 
  b.Createdate as 建档日期, 
  Person1.cPersonName as 修改人, 
  b.ModifyDate as 修改日期, 
  Person2.cPersonName as 审核人, 
  b.relsDate as 审核日期, 
  b.VersionDesc as 版本说明, 
  b.IdentCode as 替代标识,
  b.VersionEffDate as 版本生效日期,
  c.EffBegDate as 生效日期
FROM 
  v_sfc_proutingpart_rpt a 
  LEFT JOIN v_sfc_prouting_rpt b ON a.PRoutingId = b.PRoutingId 
  LEFT JOIN v_sfc_proutingdetail_rpt c ON b.PRoutingId = c.PRoutingId 
  left join v_sfc_operation_rpt d on c.OperationId = d.OperationId 
  LEFT JOIN sfc_workcenter e ON c.WcId = e.WcId 
  LEFT JOIN Person f ON b.CreateUser = f.cPersonCode 
  LEFT JOIN Person Person1 ON b.ModifyUser = Person1.cPersonCode 
  LEFT JOIN Person Person2 ON b.relsUser = Person2.cPersonCode 
  LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
  --where  a.cinvcode = '0226005'
  where d.OperationId is not null
  and g.cInvName like 'PTFE%'
order by 
  a.cinvcode

  --更新工艺子件工序生效日期

  UPDATE c
SET c.EffBegDate = '2000-01-01 00:00:00.000'
FROM v_sfc_proutingdetail_rpt c
LEFT JOIN v_sfc_proutingpart_rpt a ON a.PRoutingId = c.PRoutingId
LEFT JOIN v_sfc_prouting_rpt b ON a.PRoutingId = b.PRoutingId
LEFT JOIN v_sfc_operation_rpt d ON c.OperationId = d.OperationId
LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
WHERE g.cInvName LIKE 'PTFE%' 
  AND c.EffBegDate = '2024-01-01 00:00:00.000'
  AND d.OperationId IS NOT NULL;



--更新工艺版本日期
  UPDATE b
SET b.VersionEffDate = '2000-01-01 00:00:00.000'
FROM v_sfc_prouting_rpt b
LEFT JOIN v_sfc_proutingpart_rpt a ON a.PRoutingId = b.PRoutingId
LEFT JOIN v_sfc_proutingdetail_rpt c ON b.PRoutingId = c.PRoutingId
LEFT JOIN v_sfc_operation_rpt d ON c.OperationId = d.OperationId
LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
WHERE g.cInvName LIKE 'PTFE%' 
  AND b.VersionEffDate = '2024-01-01 00:00:00.000'
  AND d.OperationId IS NOT NULL;


-- 更新 d.OperationId 为 1000000036，当 c.OpSeq = '0010' 且 d.OperationId 为 NULL
UPDATE c
SET c.OperationId = 1000000036
FROM v_sfc_proutingdetail_rpt c
LEFT JOIN v_sfc_operation_rpt d ON c.OperationId = d.OperationId
LEFT JOIN v_sfc_proutingpart_rpt a ON c.PRoutingId = a.PRoutingId
LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
WHERE c.OpSeq = '0010'
  AND d.OperationId IS NULL
  AND g.cInvName LIKE 'PTFE%';

-- 更新 d.OperationId 为 1000000003，当 c.OpSeq = '0020' 且 d.OperationId 为 NULL
UPDATE c
SET c.OperationId = 1000000003
FROM v_sfc_proutingdetail_rpt c
LEFT JOIN v_sfc_operation_rpt d ON c.OperationId = d.OperationId
LEFT JOIN v_sfc_proutingpart_rpt a ON c.PRoutingId = a.PRoutingId
LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
WHERE c.OpSeq = '0020'
  AND d.OperationId IS NULL
  AND g.cInvName LIKE 'PTFE%';

  --物料清单资料维护

SELECT bom.Version,
		c.ParentId AS TParentId,
		g.cinvcode AS 母件编码,
		f.cinvcode AS 子件编码,
		f.cInvName AS 名称,
		f.cInvStd AS 型号,
		a.OpSeq AS 工序代号 ,
		f.bSelf,
		c.bomid,
		a.OpComponentId,
		a.ComponentId,
		b.wiptype,
		c.ParentId,
		0 AS LevelT ,
		bom.Status
FROM bom_bom bom
LEFT JOIN bom_opcomponent a
	ON a.BomId = bom.BomId
LEFT JOIN bom_opcomponentopt b
	ON a.OptionsId=b.OptionsId
LEFT JOIN bom_parent c
	ON a.BomId=c.BomId
LEFT JOIN bas_part d
	ON a.componentid=d.partid ---物料表跟子件关联
LEFT JOIN bas_part e
	ON c.parentid=e.partid ---物料表跟母件关联
LEFT JOIN inventory f
	ON d.invcode=f.cinvcode ---子件
LEFT JOIN inventory g
	ON e.invcode=g.cinvcode ---母件
WHERE g.cinvcode LIKE '0226%'and a.OpSeq = '0010'

--更新工序代号 

UPDATE a
SET a.OpSeq = '0010'
FROM bom_opcomponent a
LEFT JOIN bom_bom bom
    ON a.BomId = bom.BomId
LEFT JOIN bom_opcomponentopt b
    ON a.OptionsId = b.OptionsId
LEFT JOIN bom_parent c
    ON a.BomId = c.BomId
LEFT JOIN bas_part d
    ON a.ComponentId = d.PartId -- 物料表跟子件关联
LEFT JOIN bas_part e
    ON c.ParentId = e.PartId -- 物料表跟母件关联
LEFT JOIN inventory f
    ON d.InvCode = f.CInvCode -- 子件
LEFT JOIN inventory g
    ON e.InvCode = g.CInvCode -- 母件
WHERE g.CInvCode LIKE '0226%' 
  AND a.OpSeq = '0000';

--生产订单子件信息查看工艺编码是否为空（opcode）
SELECT ma.AllocateId,
       ma.MoDId,
       ma.SortSeq,
       ma.OpSeq,
       ma.ComponentId,
       ma.Qty,
       ma.IssQty,
       ma.DeclaredQty,
       ma.WhCode,
       ma.LotNo,
       vam.OpDecs,
       so.OpCode,
       mo.MoCode AS 生产订单号
FROM mom_moallocate ma
    LEFT JOIN mom_orderdetail
        ON mom_orderdetail.MoDId = ma.MoDId
    LEFT JOIN v_mom_moallocate vam
        ON vam.AllocateId = ma.AllocateId
    LEFT JOIN sfc_operation so
        ON so.Description = vam.OpDecs
    LEFT JOIN mom_order mo
        ON mo.MoId = mom_orderdetail.MoId
--WHERE ma.MoDId = '1000005871'
      AND so.OpCode IS NOT NULL
	--WHERE MoCode = 'DKT0111001'
	ORDER BY AllocateId

本内容仅供参考，请谨慎操作，造成数据异常，与此内容无关。