BOM查詢(通過物料找最成品)

 2009-09-26 11:39:31

 

方法1. 遞歸法

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUN_RECURRENT_CHILDREN_PARENT]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUN_RECURRENT_CHILDREN_PARENT]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
功能：實現BOM查詢(通過物料找成品，不包括SA層)
方法：遞歸法
作者：鄧星傑
時間：2009.09.24
說明：ENGBOMM表為BOM表、PARTFLM為物料檔案表
         ：字段ASSM_NO為父階料號、PART_NO為子階料號; CAT_CODE為料品類別：FG表示成品、SA表示半成品
         ：參數@Children_Item_Disp隻是為了在插入結果時保持子階料號不變
         ：參數@Children_Item為要查找的料號
         ：傳入值時，此兩參數值一樣
*/
CREATE FUNCTION FUN_RECURRENT_CHILDREN_PARENT(@Children_Item_Disp varchar(36),@Children_Item varchar(36))
RETURNS @Result TABLE(ChildrenItem varchar(36),ParentItem varchar(36))
AS
BEGIN
 DECLARE @TmpAssmNo varchar(36)
 DECLARE @Parent_Item varchar(36)
 DECLARE @CatCode varchar(20)

 DECLARE BOM_CUR   CURSOR LOCAL SCROLL  FOR SELECT ASSM_NO FROM ENGBOMM WHERE PART_NO=@Children_Item

 OPEN BOM_CUR

 FETCH NEXT FROM BOM_CUR
 INTO @Parent_Item

 WHILE @@FETCH_STATUS=0
 BEGIN
  
  SET @TmpAssmNo=''
  SET @CatCode=''
  SELECT TOP 1 @CatCode=CAT_CODE FROM PARTFLM WHERE PART_NO=@Parent_Item
  IF @CatCode='FG' --如果是成品則插入到結果表
  BEGIN
   INSERT INTO @Result
   SELECT @Children_Item_Disp,@Parent_Item

  END --因為是成品也有可能是其它成品或半成品的子階，所以還得查找其是否有父階

  SELECT TOP 1 @TmpAssmNo=ASSM_NO FROM ENGBOMM WHERE PART_NO=@Parent_Item
   --有父階，遞歸調用
  --如果沒有父階，但CATCODE=FG時，因為在前已經插入過，所以在此不考慮
  --如果沒有父階，但CATCODE<>FG時，在此PartNo隻能是半成品，所以在此也不考慮
  IF @TmpAssmNo<>'' 
  BEGIN
   INSERT INTO @Result SELECT* from FUN_RECURRENT_CHILDREN_PARENT(@Children_Item_Disp,@Parent_Item)
  END


  FETCH NEXT FROM BOM_CUR
  INTO @Parent_Item
 END

 CLOSE BOM_CUR

 DEALLOCATE BOM_CUR

 RETURN
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
調用舉例
SELECT * FROM  FUN_RECURRENT_CHILDREN_PARENT('001-20122AN' ,'001-20122AN') 
*/

 

 

方法2: 篩選法

 

if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[FUN_FILTER_CHILDREN_PARENT]') and xtype in (N'FN', N'IF', N'TF'))
drop functiON [dbo].[FUN_FILTER_CHILDREN_PARENT]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
功能：實現BOM查詢(通過成品或半成品找最底層的物料清單，不包括SA層)
方法：篩選法
作者：鄧星傑
時間：2009.09.24
說明：ENGBOMM表為BOM表、PARTFLM為物料檔案表
         ：字段ASSM_NO為父階料號、PART_NO為子階料號; CAT_CODE為料品類別：FG表示成品、SA表示半成品
         ：參數@Parent_Item為要查找的成品或半成品
*/

CREATE FUNCTION FUN_FILTER_CHILDREN_PARENT(@Children_Item VARCHAR(36))
RETURNS @Result TABLE(ChildrenItem VARCHAR(36),ParentItem VARCHAR(36))
AS
BEGIN

 DECLARE @tmpParentItem TABLE(Parent_Item VARCHAR(36) COLLATE Chinese_Taiwan_Stroke_CI_AS)
 DECLARE @tmpChildrenItem TABLE(Children_Item VARCHAR(36) COLLATE Chinese_Taiwan_Stroke_CI_AS)

 --為了直觀才用此變量，否則直接可用@@ROWCOUNT
 DECLARE @Rows INT
 SET @Rows=0

 --父件是FG的部分，直接插入結果表
 INSERT INTO @Result
 SELECT @Children_Item ,ASSM_NO FROM ENGBOMM WHERE PART_NO=@Children_Item  and exists (SELECT * FROM PARTFLM WHERE PART_NO=ENGBOMM.ASSM_NO and CAT_CODE='FG')


 --父件是SA的部分+父件是FG且在BOM中作為其它FG的子件的部分
 INSERT INTO @tmpChildrenItem
 SELECT ASSM_NO FROM ENGBOMM a join PartFLM b ON a.ASSM_NO=b.PART_NO
 WHERE a.PART_NO=@Children_Item  and (b.CAT_CODE='SA'  or  (b.CAT_CODE='FG'   and  not exists (SELECT * FROM ENGBOMM WHERE PART_NO=a.ASSM_NO)))
 SET @Rows=@@ROWCOUNT
 
 WHILE @Rows>0
 BEGIN
  --父件是FG的部分，直接插入結果表
  INSERT INTO @Result
  SELECT @Children_Item,ASSM_NO FROM ENGBOMM
  WHERE PART_NO in (SELECT Children_Item FROM @tmpChildrenItem)  and exists (SELECT * FROM PARTFLM WHERE PART_NO=ENGBOMM.ASSM_NO and CAT_CODE='FG')
 
  DELETE FROM @tmpParentItem
  INSERT INTO @tmpParentItem
  SELECT Children_Item FROM @tmpChildrenItem
 
  DELETE FROM @tmpChildrenItem
  INSERT INTO @tmpChildrenItem
  SELECT ASSM_NO FROM ENGBOMM a join PartFLM b ON a.ASSM_NO=b.PART_NO
  WHERE a.PART_NO  in (SELECT Parent_Item FROM @tmpParentItem)  and (b.CAT_CODE='SA'  or  (b.CAT_CODE='FG'   and  not exists (SELECT * FROM ENGBOMM WHERE PART_NO=a.ASSM_NO)))
  SET @Rows=@@ROWCOUNT
 
 END


 RETURN


END


/*
調用舉例 
SELECT * FROM FUN_FILTER_CHILDREN_PARENT('001-20122AN')
*/

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO