用友U8BOM查询语句

SELECT A.BomId,
       C.InvCode AS 母件编码,
       A.Version AS 版本号,
       A.VersionEffDate AS 版本生效日期,
       A.Status AS 状态
FROM dbo.bom_bom A
    JOIN dbo.bom_parent b
        ON A.BomId = b.BomId
    JOIN dbo.bas_part C
        ON b.ParentId = C.PartId
    JOIN dbo.Inventory d
        ON C.InvCode = d.cInvCode
    JOIN dbo.ComputationUnit E
        ON d.cComUnitCode = E.cComunitCode
WHERE C.InvCode = 'V21001.03.00.00.00';

--子件详细信息
SELECT b.SortSeq AS '序号',
       c.InvCode AS '子件编码',
       d.cInvName AS '子件名称',
       d.cInvAddCode AS '子件代码',
       d.cInvStd AS '子件规格',
       e.cComUnitName AS '计量单位',
       b.BaseQtyN AS '基本用量',
       b.BaseQtyD AS '基本用量',
       CompScrap AS '子件损耗率',
       EffBegDate AS '子件生效日',
       EffEndDate AS '子件失效日'
FROM bom_bom AS a
    JOIN bom_opcomponent AS b
        ON a.BomId = b.BomId
    JOIN bas_part AS c
        ON b.ComponentId = c.PartId
    JOIN Inventory AS d
        ON c.InvCode = d.cInvCode
    JOIN ComputationUnit AS e
        ON d.cComUnitCode = e.cComunitCode
WHERE b.BomId = '1000001272'; --对应BOM对应修改 order by SortSeq

SELECT cInvCode,
       dbo.Inventory.dEDate
FROM Inventory
WHERE cInvCode IN
      ( --子件详细信息
          SELECT c.InvCode AS '子件编码'
          FROM bom_bom AS a
              JOIN bom_opcomponent AS b
                  ON a.BomId = b.BomId
              JOIN bas_part AS c
                  ON b.ComponentId = c.PartId
              JOIN Inventory AS d
                  ON c.InvCode = d.cInvCode
              JOIN ComputationUnit AS e
                  ON d.cComUnitCode = e.cComunitCode
          WHERE b.BomId = '1000001276'
      );