USE [DB1]
GO
/****** Object: StoredProcedure [dbo].[sp_JDE_FetchBomDetail] Script Date: 07/28/2011 09:47:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_JDE_FetchBomDetail](
@ParentItem nvarchar(30),
@ParentQty real,
@OrignItem nvarchar(30),
@isLastLeaf int output) AS
BEGIN
DECLARE @tmpVar int
DECLARE @strParentItem nvarchar(30)
DECLARE @p_item nvarchar(30)
DECLARE @p_uom nvarchar(30)
DECLARE @s_itm real
DECLARE @s_litm nvarchar(30)
DECLARE @s_atim nvarchar(30)
DECLARE @s_uom nvarchar(10)
DECLARE @s_qty real
DECLARE @p_OUom nvarchar(10)
DECLARE @S_Oum nvarchar(10)
DECLARE @isLastLeaf2 int
SET @tmpVar = 0;
SET @isLastLeaf =0;
SET @strParentItem = @ParentItem;
DECLARE @rowcount AS int
DECLARE mycur2 CURSOR Local FOR
select ixkitl, cpuom, ixitm, ixlitm, ixaitm, csum, isnull(csqty,0), ixuom, ixum
from costbom_Z1 where rtrim(ltrim(ixkitl)) =rtrim(ltrim(@strParentItem));
OPEN mycur2;
FETCH NEXT FROM mycur2
INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
--PRINT 'Count: '+cast(@ROWCOUNT AS nvarchar(20))
IF @@FETCH_STATUS <>0
BEGIN
SET @isLastLeaf=1
CLOSE mycur2
DEALLOCATE mycur2
RETURN
END
ELSE BEGIN
--PRINT 'isLeastLeaf21:'+cast(isnull(@isLastLeaf2,'') AS nvarchar(20))
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @qty real
SET @qty=@s_qty*@ParentQty
EXEC dbo.sp_JDE_FetchBomDetail @s_litm,@qty, @OrignItem, @isLastLeaf2 output;
--PRINT 'isLeastLeaf22:'+cast(@isLastLeaf2 AS nvarchar(20))
IF @isLastLeaf2=1
BEGIN
insert into costbom_z3 (ixkitl, ixitm, ixlitm, ixaitm,csum, csqty, cpuom, ixum, ixuom )
values(@OrignItem, @s_itm, @s_litm, @s_atim , @s_uom , @s_qty*@ParentQty, @p_uom, @s_Oum, @P_Ouom);
END
FETCH NEXT FROM mycur2
INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
END
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Factorial_ap](@Number Int,@RetVal Int OUTPUT)AS
begin
DECLARE @In Int
DECLARE @Out Int
IF @Number <> 1 BEGIN
set @In = @Number-1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE BEGIN
SELECT @RetVal = 1
END
print @RetVal
RETURN
end
-- exec dbo.[Factorial_ap] 5,''
CLOSE mycur2;
DEALLOCATE mycur2;
END