子件用途多阶反查报表

效果图

生产

使用方法

• 将创建存储过程代码执行到对应账套数据库
• 在B1系统内执行查询报表代码
• 注意：若之前创建过相同的存储过程，请删除旧存储过程，重新执行一次创建代码

创建存储过程代码

/* Select Code From OITM T0 Where T0.ItemCode ='[%0]' */ 
/* Select Code From OITM T0 Where T0.ItemName ='[%1]' */ 
/* Select Code From OITM T0 Where T0.FrgnName ='[%2]' */ Exec SBO_sap560_GetFatherByItem '[%0]','[%1]','[%2]'

代码示例

GO

/******  By: 五六零网校 www.sap560.com ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- 子件用途多阶反查报表 
-- =============================================
create PROCEDURE [dbo].[SBO_sap560_GetFatherByItem]
	@ccItemCode nvarchar(30),@ccItemName nvarchar(30),@ccFrgnName nvarchar(30)
AS
BEGIN
declare @cItemCode nvarchar(40);

set @ccItemCode = '%' + @ccItemCode + '%';
set @ccItemName = '%' + @ccItemName + '%';
set @ccFrgnName = '%' + @ccFrgnName + '%';


declare @WL_result1 table (cItemCode nvarchar(30), ItemCode nvarchar(30),ItemName nvarchar(100),qty numeric(19,2),numstr nvarchar(200),Lvl int,IsTop int,VisOrder int,Del int,ID nvarchar(50),Comment nvarchar(254)) --0 非顶  1 顶
declare @WL_result2 table (cItemCode nvarchar(30),ItemCode nvarchar(30),ItemName nvarchar(100),qty numeric(19,2),numstr nvarchar(200),VisOrder int,Lvl int,Comment nvarchar(254))

Declare @ItemCode nvarchar(30), @numstr nvarchar(200),@ID nvarchar(50),@Comment nvarchar(254),@qty numeric(19,2)

declare cursor_test cursor
for
select  ItemCode from OITM where (ItemCode like @ccItemCode or @ccItemCode='%%') and (ItemName like @ccItemName or @ccItemName='%%' ) and ( FrgnName like @ccFrgnName or @ccFrgnName='%%' );
declare @tmp nvarchar(40)
open cursor_test
fetch next from cursor_test
into @tmp
WHILE @@FETCH_STATUS = 0

begin

set @cItemCode = @tmp


Declare @Count int,@VisOrder int ,@Lvl int ;

Set @Lvl = 1;

Insert Into @WL_result1 (ID,cItemCode,ItemCode,ItemName,Lvl,numstr,IsTop,VisOrder,Del,Comment,qty) Select NEWID(), @cItemCode,T0.Code,T0.Name,@Lvl,T0.Code,0,T1.VisOrder,0,T1.Comment,t1.Quantity  From OITT T0 inner Join ITT1 T1 On T0.Code=T1.Father Where T1.Code=@cItemCode

Select @Count=count(1) From @WL_result1 Where IsTop = 0 ;

While @Count <> 0
Begin
	
	declare GET_1 CURSOR     
	                        
         FOR Select cItemCode,ItemCode,VisOrder,ID,numstr,Lvl,Comment,qty From @WL_result1  Where IsTop = 0 And Del=0
         OPEN GET_1                                       
         FETCH GET_1 INTO @cItemCode,@ItemCode ,@VisOrder ,@ID,@numstr,@Lvl,@Comment,@qty
         WHILE @@fetch_status=0                          
         begin 
		     
			 Declare @count1 int

			 Select @count1 =  COUNT(1) From @WL_result2;
				
			 Insert Into @WL_result2 (cItemCode,ItemCode,ItemName,numstr,VisOrder,Lvl,Comment,qty) Select @cItemCode,T0.Code,T0.Name, @numstr + '-'+ T0.Code ,@VisOrder,@Lvl+1,@Comment,@qty  From OITT T0 inner Join ITT1 T1 On T0.Code=T1.Father Where T1.Code = @ItemCode

			 If(Select COUNT(1) From @WL_result2) <> @count1
			 Begin
				Update  @WL_result1 Set Del = 1 Where ID=@ID;
			 End
			 
			 FETCH next from GET_1 INTO @cItemCode,@ItemCode ,@VisOrder ,@ID,@numstr,@Lvl ,@Comment,@qty
         end                                             
         CLOSE GET_1                                      
         DEALLOCATE GET_1        

	
	
	Update @WL_result1 Set IsTop = 1;
	Delete @WL_result1 Where ISNULL(Del,0) = 1

	Insert Into @WL_result1 (ID,cItemCode,ItemCode,ItemName,numstr,IsTop,VisOrder,Del,Lvl,Comment,qty) Select NEWID(),cItemCode,ItemCode,ItemName,numstr,0,VisOrder,0,Lvl,Comment,qty From @WL_result2
	delete @WL_result2;
	Select @Count=count(1) From @WL_result1 Where IsTop = 0 ;

End

fetch next from cursor_test
into @tmp

end
close cursor_test
deallocate cursor_test

Select T1.ItemCode as '子件编码',T1.ItemName as '子件名称',T1.FrgnName as '子件规格描述',t0.VisOrder as '子件行号',T2.ItemCode as '母件编号',t2.ItemName as '母件名称',t2.FrgnName as '母件规格描述',
		T0.qty  as '用量',T1.Invntryuom  as '单位',t0.Comment  as '备注',Lvl as '级别',T0.numstr as '级别描述' From @WL_result1 T0 Inner Join OITM T1 On T0.cItemCode=T1.ItemCode Inner Join OITM T2 On T0.ItemCode=T2.ItemCode 


END

GO