子件用途多阶反查报表
大约 2 分钟
效果图
![生产](/assets/19-x1oZEQdo.png)
使用方法
- 将创建存储过程代码执行到对应账套数据库
- 在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