Executing below query takes 1:30 mins to 2 hours, pls. help me get out of this problem

• Samstag, 5. Januar 2013 09:57

   

   
  

  0

  My query:

  Declare @F_WH nvarchar(20), 
   @T_WH nvarchar(20), 
   @F_ItmGrp nvarchar(20), 
   @T_ItmGrp nvarchar(20),  
   @F_Date nvarchar(10),  
   @T_Date nvarchar(10) 
   Select @F_WH = (isnull(T0.whscode, '')) from [dbo].[owhs] T0 
   where T0.whscode = '' 
   Select @T_WH = (isnull(T1.whscode, '')) from [dbo].[owhs] T1 
   where T1.whscode = '' 
   SELECT @F_ItmGrp = ( isnull(T2.ItmsGrpCod, '')) FROM [dbo].[OITB] T2 
   WHERE T2.ItmsGrpCod = '103' 
   SELECT @T_ItmGrp = (isnull(T3.ItmsGrpCod, '')) FROM [dbo].[OITB] T3 
   WHERE T3.ItmsGrpCod = '103' Select @F_Date =  '2012-04-01' 
   Select @T_Date =  '2012-04-01' if @F_WH is null set @F_WH = '' 
   if @T_WH is null set @T_WH = '' if @F_ItmGrp is null set @F_ItmGrp = ''  
   if @T_ItmGrp is null set @T_ItmGrp = '' if @F_Date is null set @F_Date = ''  
   if @T_Date is null set @T_Date = ''  
   select tt.ItemGroup,tt.ItemCode,tt.ItemDescription,sum(tt.OpeningStock) 
   as OpeningStock,sum(tt.TotalInwardQty) as TotalInwardQty,  
   sum(tt.TotalOutwardQty) as TotalOutwardQty,sum(tt.ClosingStockValue) 
   as ClosingStockValue,  closing = (sum(tt.OpeningStock)+ sum(tt.TotalInwardQty) - sum(tt.TotalOutwardQty)),   
   price = case 
   when (sum(tt.ClosingStockValue)) = 0 then 0   
   else (sum(tt.ClosingStockValue)/(sum(tt.OpeningStock) + sum(tt.TotalInwardQty) - sum(tt.TotalOutwardQty))) 
   end   
   from 
   (Select WH AS 'WarehouseCode', ITEMGROUPNAME AS 'ItemGroup', 
   ITEMCODE AS 'ItemCode', ITEMDESC AS 'ItemDescription', OB AS 'OpeningStock',   
   OB_VAL AS 'OpeningStockValue', IN_QTY AS 'TotalInwardQty', 
   IN_VAL AS 'TotalInwardValue',  ITMGRP_IN_QTY AS 'ItemGroupwiseInwardQtyTotal',   
   OUT_QTY AS 'TotalOutwardQty', OUT_VAL AS 'TotalOutwardValue', 
   ITMGRP_OUT_QTY AS 'ItemGroupwiseOutwardQtyTotal',  
   CB_QTY AS 'ClosingStock',   
   CB_VAL AS 'ClosingStockValue' from [dbo].[FUNC_STOCK_SUMMARY_NEW](@F_WH,@T_WH,@F_ItmGrp,@T_ItmGrp,@F_Date,@T_Date)) tt    
   group by tt.ItemGroup,tt.ItemCode,tt.ItemDescription

  The Function:

  USE [SFP_09.10.2012]
  GO
  /****** Object:  UserDefinedFunction [dbo].[FUNC_STOCK_SUMMARY_NEW]    Script Date: 01/05/2013 14:52:59 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER	FUNCTION [dbo].[FUNC_STOCK_SUMMARY_NEW](@F_WAREHOUSE NVARCHAR(20), @T_WAREHOUSE NVARCHAR(20), @F_ITEMGROUP NVARCHAR(20), @T_ITEMGROUP NVARCHAR(20), @F_DATE DATETIME, @T_DATE DATETIME)
  Returns
  @TAB_STOCK Table (WH NVARCHAR(20),ITEMGROUPNAME NVARCHAR(100),ITEMCODE NVARCHAR(20), ITEMDESC NVARCHAR(100), OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_VAL NUMERIC(16,4), ITMGRP_IN_QTY NUMERIC(16,4), ITMGRP_IN_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_VAL NUMERIC(16,4), ITMGRP_OUT_QTY NUMERIC(16,4), ITMGRP_OUT_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_VAL NUMERIC(16,4),packcartons NUMERIC(16,4),itmprice NUMERIC(16,4),ITMGRPCD NVARCHAR(20),DTE DATETIME)
  AS
  BEGIN
  	--Cursor
  	--------
  	--Warehouse
  	--	Item Group
  	--		Item
  	--			Date
  	--
  	--			End Date
  	--		End Item
  	--	End Item Group
  	--End Warehouse
  	--Declaration Part
  	DECLARE @WAREHOUSE NVARCHAR(20), @ITEMCODE NVARCHAR(20), @ITEMDESC NVARCHAR(100), @ITEMGROUP NVARCHAR(20), @OPENQTY NUMERIC(16,4), @OPENVALUE NUMERIC(16,4), @RECEIPTQTY NUMERIC(16,4), @RECEIPTVALUE NUMERIC(16,4), @GROUPTOT_RECEIPTQTY NUMERIC(16,4), @GROUPTOT_RECEIPTVALUE NUMERIC(16,4), @ISSUEDQTY NUMERIC(16,4), @ISSUEDVALUE NUMERIC(16,4), @CLOSINGQTY NUMERIC(16,4), @CLOSINGVALUE NUMERIC(16,4), @GROUPTOT_ISSUEDQTY NUMERIC(16,4), @GROUPTOT_ISSUEDVALUE NUMERIC(16,4)
  	DECLARE @DATE DATETIME --NVARCHAR(10)
  	DECLARE @INQTY NUMERIC(16,4), @OUTQTY NUMERIC(16,4), @TRANSTYPE NVARCHAR(6), @CALCPRICE NUMERIC(16,4)
  	DECLARE @TOT_IN NUMERIC(16,4), @TOT_OUT NUMERIC(16,4), @TOT_IN_PRICE NUMERIC(16,4), @TOT_OUT_PRICE NUMERIC(16,4)
  	DECLARE @OPENQTYPRICE NUMERIC(16,4), @OpQty NUMERIC(16,4), @OpVal NUMERIC(16,4), @ClQty NUMERIC(16,4), @ClVal NUMERIC(16,4)
  	DECLARE @PREVDAYCLOSING NUMERIC(16,4), @PREVDAYCLOSINGVAL NUMERIC(16,4), @DAYOPENINGBALANCE NUMERIC(16,4), @DAYOPENINGBALANCEVAL NUMERIC(16,4), @DAYCLOSINGBALANCE NUMERIC(16,4), @DAYCLOSINGBALANCEVAL NUMERIC(16,4)
  	DECLARE @TAB_DAY_STOCK TABLE (WH NVARCHAR(20),ITMCORTON NUMERIC(16,4), ITEMGROUP NVARCHAR(20),ITEMCODE NVARCHAR(20), DATE DATETIME, OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_QTY_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_QTY_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_QTY_VAL NUMERIC(16,4), INV_REV_FLAG INT,itmprice NUMERIC(16,4))
  	DECLARE @ST_DATE DATETIME, @EN_DATE DATETIME, @IN_DATE DATETIME
  	DECLARE @ITMCOD NVARCHAR(20)
  	DECLARE @INV_REVALUE NUMERIC(16,4), @INV_REV_FLAG INT
  	DECLARE @TOT_BALANCE NUMERIC(16,4), @TOT_TRANS_BALANCE NUMERIC(16,4)
      declare @packunit NUMERIC(16,4), @packcartons NUMERIC(16,4),@itmprice NUMERIC(16,4) 
      SET @packunit = 0
      SET @packcartons = 0
  	SET @TOT_BALANCE = 0
  	SET @TOT_TRANS_BALANCE = 0
      
  	SET @INV_REV_FLAG = 0
  	SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
  	SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
  	SET @DATE = @ST_DATE
  	--Warehouse Cursor
  	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
  	OPEN CUR_WAREHOUSE
  	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
  	WHILE(@@FETCH_STATUS = 0)
  	BEGIN
  		--Item Group Cursor
  		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
  WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
  OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
  ORDER BY ITMSGRPCOD
  		OPEN CUR_ITEMGROUP
  		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
  		WHILE(@@FETCH_STATUS = 0)
  		BEGIN
  			--Item Cursor
  			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE -- AND ((T6.DOCDATE >= @F_DATE AND T6.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )) ORDER BY T6.ITEMCODE
  			OPEN CUR_ITEM
  			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
  			WHILE(@@FETCH_STATUS = 0)
  			BEGIN
  				SET @PREVDAYCLOSING = 0
  				SET @PREVDAYCLOSINGVAL = 0
  				SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
  				SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
  				SET @DATE = @ST_DATE
  				--DECLARE CUR_DATE CURSOR FOR SELECT DISTINCT T0.DOCDATE FROM OINM T0 ORDER BY T0.DOCDATE --WHERE  ((T0.DOCDATE >= @F_DATE AND T0.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') ) ORDER BY T0.DOCDATE
  				--OPEN CUR_DATE
  				--FETCH NEXT FROM CUR_DATE INTO @DATE
  				--WHILE (@@FETCH_STATUS = 0)
  				WHILE (@DATE <= @EN_DATE)
  				BEGIN
  					SET @INQTY = 0
  					SET @OUTQTY = 0
  					SET @CALCPRICE = 0
  					SET @TOT_IN = 0
  					SET @TOT_OUT = 0
  					SET @TOT_IN_PRICE = 0
  					SET @TOT_OUT_PRICE = 0
  					SET @OPENQTY = 0
  					SET @OPENQTYPRICE = 0
  					SET @DAYOPENINGBALANCE = 0
  					SET @DAYOPENINGBALANCEVAL = 0
  					SET @DAYCLOSINGBALANCE = 0
  					SET @DAYCLOSINGBALANCEVAL = 0
  					SET @INV_REVALUE = 0
  --modified for excluding the transtype = 67   starts on 11-10-2011
  					DECLARE CUR_ITEM_IN CURSOR FOR SELECT  (CONVERT(NUMERIC(16,4), T6.INQTY)), (CONVERT(NUMERIC(16,4), T6.OUTQTY)), (CONVERT(NUMERIC(16,4), T6.TRANSVALUE)), T6.TRANSTYPE  FROM OINM T6 WHERE ((T6.ITEMCODE = @ITEMCODE) AND (T6.WAREHOUSE = @WAREHOUSE) AND (T6.DOCDATE = @DATE) and t6.transtype <> 67)
  --end		
  			OPEN CUR_ITEM_IN
  					FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
  					WHILE(@@FETCH_STATUS = 0)
  					BEGIN
  						IF @INQTY = 0 AND @OUTQTY = 0 
  							SET @INV_REVALUE = @INV_REVALUE + @CALCPRICE
  						IF @CALCPRICE < 0 
  							SET @CALCPRICE = @CALCPRICE * (-1)
  						IF (@INQTY > 0) --AND (@TRANSTYPE != '-2')
  						BEGIN
  					
  							SET @TOT_IN = @TOT_IN + @INQTY
  							SET @TOT_IN_PRICE  = @TOT_IN_PRICE + @CALCPRICE
  						END
  						IF (@OUTQTY > 0) --AND (@TRANSTYPE != '-2')
  						BEGIN
  					
  							SET @TOT_OUT = @TOT_OUT + @OUTQTY
  							SET @TOT_OUT_PRICE  = @TOT_OUT_PRICE + @CALCPRICE
  						END
  --						IF (@INQTY > 0) AND (@TRANSTYPE = '-2')
  --						BEGIN
  --							
  --							SET @OPENQTY = @OPENQTY + @INQTY
  --							SET @OPENQTYPRICE = @OPENQTYPRICE + @CALCPRICE	
  --
  --						END
  						FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
  					END
  					CLOSE CUR_ITEM_IN
  					DEALLOCATE CUR_ITEM_IN
  					SET @INV_REV_FLAG = 0
  					IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0 OR @F_DATE = @DATE OR @T_DATE = @DATE OR @INV_REVALUE != 0
  					BEGIN
  	
  						IF @INV_REVALUE != 0 AND @TOT_IN = 0 AND @TOT_OUT = 0
  							SET @INV_REV_FLAG = 1
  	
  						SET @DAYOPENINGBALANCE = @PREVDAYCLOSING
  						SET @DAYOPENINGBALANCEVAL = @PREVDAYCLOSINGVAL
  						SET @DAYCLOSINGBALANCE = @DAYOPENINGBALANCE + (@OPENQTY + @TOT_IN) - @TOT_OUT
  						SET @DAYCLOSINGBALANCEVAL = @DAYOPENINGBALANCEVAL + (@OPENQTYPRICE + @TOT_IN_PRICE) - @TOT_OUT_PRICE + @INV_REVALUE
  						--IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0
  						--IF @DAYOPENINGBALANCE != 0 OR @DAYCLOSINGBALANCE != 0
  INSERT INTO @TAB_DAY_STOCK VALUES(@WAREHOUSE,@packcartons, @ITEMGROUP, @ITEMCODE, @DATE, @DAYOPENINGBALANCE, @DAYOPENINGBALANCEVAL, (@OPENQTY + @TOT_IN), (@OPENQTYPRICE + @TOT_IN_PRICE), @TOT_OUT, @TOT_OUT_PRICE, @DAYCLOSINGBALANCE, @DAYCLOSINGBALANCEVAL, @INV_REV_FLAG,@itmprice)
  						SET @PREVDAYCLOSING = @DAYCLOSINGBALANCE
  						SET @PREVDAYCLOSINGVAL = @DAYCLOSINGBALANCEVAL	
  			
  						IF @T_DATE = @DATE			
  							SET @TOT_BALANCE = @TOT_BALANCE + @DAYCLOSINGBALANCEVAL
  	
  					END
  					--FETCH NEXT FROM CUR_DATE INTO @DATE
  					SET @DATE = @DATE + 1				
  				END
  				--CLOSE CUR_DATE
  				--DEALLOCATE CUR_DATE
  --SALE PACKUNIT
  					
  				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
  			END
  			CLOSE CUR_ITEM
  			DEALLOCATE CUR_ITEM
  			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
  		END
  		CLOSE CUR_ITEMGROUP
  		DEALLOCATE CUR_ITEMGROUP
  		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
  	END
  	CLOSE CUR_WAREHOUSE
  	DEALLOCATE CUR_WAREHOUSE
  	DECLARE @WH_TS NVARCHAR(20), @ITEMGROUP_TS NVARCHAR(20),@ITEMCODE_TS NVARCHAR(20), @DATE_TS DATETIME, @OB_TS NUMERIC(16,4), @OB_VAL_TS NUMERIC(16,4), @IN_QTY_TS NUMERIC(16,4), @IN_QTY_VAL_TS NUMERIC(16,4), @OUT_QTY_TS NUMERIC(16,4), @OUT_QTY_VAL_TS NUMERIC(16,4), @CB_QTY_TS NUMERIC(16,4), @CB_QTY_VAL_TS NUMERIC(16,4), @INV_REV_FLAG_TS INT
  	DECLARE @TEMP NUMERIC(16,4)
  	DECLARE @DATE_IN DATETIME
  	DECLARE @FIRST_REC INT
  	SET @FIRST_REC = 0
  	DECLARE @OB_QTY_IN NUMERIC(16,4), @OB_VAL_IN NUMERIC(16,4), @CL_QTY_IN NUMERIC(16,4), @CL_VAL_IN NUMERIC(16,4)
  	DECLARE @TOT_IN_QTY_IN NUMERIC(16,4), @TOT_IN_VAL_IN NUMERIC(16,4), @TOT_OUT_QTY_IN NUMERIC(16,4), @TOT_OUT_VAL_IN NUMERIC(16,4), @TOT_INV_REV_FALG INT
  	DECLARE @GTOT_IN_QTY_IN NUMERIC(16,4), @GTOT_IN_VAL_IN NUMERIC(16,4), @GTOT_OUT_QTY_IN NUMERIC(16,4), @GTOT_OUT_VAL_IN NUMERIC(16,4)
  	DECLARE @GRPNAME NVARCHAR(100), @ITMDESC NVARCHAR(100)
  	DECLARE @GRAND_TOT_IN NUMERIC(16,4), @GRAND_TOT_OUT NUMERIC(16,4)
  	SET @GRAND_TOT_IN = 0
  	SET @GRAND_TOT_OUT = 0
  	--Warehouse Cursor
  	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
  	OPEN CUR_WAREHOUSE
  	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
  	WHILE(@@FETCH_STATUS = 0)
  	BEGIN
  		--Item Group Cursor
  		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
  WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
  OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
  ORDER BY ITMSGRPCOD
  		OPEN CUR_ITEMGROUP
  		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
  		WHILE(@@FETCH_STATUS = 0)
  		BEGIN
  			SET @GTOT_IN_QTY_IN = 0
  			SET @GTOT_IN_VAL_IN = 0
  			SET @GTOT_OUT_QTY_IN = 0
  			SET @GTOT_OUT_VAL_IN = 0
  			--Item Cursor
  			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE -- AND ((T6.DOCDATE >= @F_DATE AND T6.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )) ORDER BY T6.ITEMCODE
  			OPEN CUR_ITEM
  			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
  			WHILE(@@FETCH_STATUS = 0)
  			BEGIN
  				SET @TOT_IN_QTY_IN = 0
  				SET @TOT_IN_VAL_IN = 0
  				SET @TOT_OUT_QTY_IN = 0
  				SET @TOT_OUT_VAL_IN = 0
  				SET @TOT_INV_REV_FALG = 0
  				SET @FIRST_REC = 0
  		
  				DECLARE CUR_DATE_IN CURSOR FOR SELECT DISTINCT DATE FROM @TAB_DAY_STOCK WHERE (WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP)AND (ITEMCODE = @ITEMCODE) AND ((DATE >= @F_DATE AND DATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )
  				OPEN CUR_DATE_IN
  				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
  				WHILE(@@FETCH_STATUS = 0)
  				BEGIN
  					DECLARE CUR_TRANS CURSOR FOR SELECT * FROM @TAB_DAY_STOCK WHERE ((WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP) AND (DATE = @DATE_IN) AND (ITEMCODE = @ITEMCODE))
  					OPEN CUR_TRANS
  FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
  					WHILE (@@FETCH_STATUS = 0)
  					BEGIN
  						SET @FIRST_REC = @FIRST_REC + 1
  						IF @FIRST_REC = 1 
  						BEGIN
  							SET @OB_QTY_IN = @OB_TS
  							SET @OB_VAL_IN = @OB_VAL_TS
  						END
  						SET @TOT_IN_QTY_IN = @TOT_IN_QTY_IN + @IN_QTY_TS
  						SET @TOT_IN_VAL_IN = @TOT_IN_VAL_IN + @IN_QTY_VAL_TS
  						SET @TOT_OUT_QTY_IN = @TOT_OUT_QTY_IN + @OUT_QTY_TS
  						SET @TOT_OUT_VAL_IN = @TOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
  						SET @GTOT_IN_QTY_IN = @GTOT_IN_QTY_IN + @IN_QTY_TS
  						SET @GTOT_IN_VAL_IN = @GTOT_IN_VAL_IN + @IN_QTY_VAL_TS
  						SET @GTOT_OUT_QTY_IN = @GTOT_OUT_QTY_IN + @OUT_QTY_TS
  						SET @GTOT_OUT_VAL_IN = @GTOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
  						SET @CL_QTY_IN = @CB_QTY_TS
  						SET @CL_VAL_IN = @CB_QTY_VAL_TS
  						SET @TOT_INV_REV_FALG = @TOT_INV_REV_FALG + @INV_REV_FLAG_TS
  						
  						FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
  					END	
  					CLOSE CUR_TRANS
  					DEALLOCATE CUR_TRANS
  				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
  				END
  				CLOSE CUR_DATE_IN
  				DEALLOCATE CUR_DATE_IN
  				SET @GRPNAME = (SELECT ITMSGRPNAM FROM OITB WHERE ITMSGRPCOD = @ITEMGROUP)
  				SET @ITMDESC = (SELECT ITEMNAME FROM OITM WHERE ITEMCODE = @ITEMCODE)
  -----------==========================================
  declare cur_item_packunit cursor for select SALPACKUN from oitm  where itemcode = @ITEMCODE
  					open cur_item_packunit
  					fetch  next from cur_item_packunit into @packunit
  					while(@@FETCH_STATUS = 0)
  				begin
  					SET @packcartons = @CL_QTY_IN/@packunit
  					fetch next from cur_item_packunit into @packunit
                  end
  					close cur_item_packunit
  					deallocate cur_item_packunit
  ---
  ---- item price
  declare cur_item_price cursor for select price from itm1  where itemcode = @ITEMCODE
  					open cur_item_price
  					fetch  next from cur_item_price into @itmprice
  					set @itmprice=@itmprice
  					close cur_item_price
  					deallocate cur_item_price
  -----------------
  				
  				--IF @TOT_IN_QTY_IN != 0 OR @TOT_OUT_QTY_IN != 0 OR @TOT_INV_REV_FALG > 0
  				--BEGIN
  INSERT INTO @TAB_STOCK VALUES(@WH_TS,@GRPNAME, @ITEMCODE_TS, @ITMDESC, @OB_QTY_IN, @OB_VAL_IN, @TOT_IN_QTY_IN, @TOT_IN_VAL_IN, @GTOT_IN_QTY_IN, @GTOT_IN_VAL_IN, @TOT_OUT_QTY_IN, @TOT_OUT_VAL_IN, @GTOT_OUT_QTY_IN, @GTOT_OUT_VAL_IN, @CL_QTY_IN, @CL_VAL_IN,@packcartons,@itmprice,@ITEMGROUP,@DATE)
  					--SET @TOT_TRANS_BALANCE = @TOT_TRANS_BALANCE + @CL_VAL_IN
  				--END
  				IF @TOT_IN_QTY_IN != 0 OR @TOT_OUT_QTY_IN != 0 OR @TOT_INV_REV_FALG > 0
  				BEGIN
  					SET @TOT_TRANS_BALANCE = @TOT_TRANS_BALANCE + @CL_VAL_IN
  				END
  				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
  			END
  			CLOSE CUR_ITEM
  			DEALLOCATE CUR_ITEM
  			IF @GTOT_IN_VAL_IN != 0 OR @GTOT_OUT_VAL_IN != 0
  			BEGIN
  				--INSERT INTO @TAB_STOCK VALUES('', '', '', 'Sub Total(' + @GRPNAME + ')', NULL, NULL, NULL, @GTOT_IN_VAL_IN, NULL, NULL, NULL, @GTOT_OUT_VAL_IN, NULL, NULL, NULL, NULL)
  				SET @GRAND_TOT_IN = @GRAND_TOT_IN + @GTOT_IN_VAL_IN
  				SET @GRAND_TOT_OUT = @GRAND_TOT_OUT + @GTOT_OUT_VAL_IN
  			END
  			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
  		END
  		CLOSE CUR_ITEMGROUP
  		DEALLOCATE CUR_ITEMGROUP
  		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
  	END
  	CLOSE CUR_WAREHOUSE
  	DEALLOCATE CUR_WAREHOUSE
  --
  --	IF @GRAND_TOT_IN != 0 OR @GRAND_TOT_OUT != 0
  --	BEGIN
  --		INSERT INTO @TAB_STOCK VALUES('','', '', 'Grand Total', NULL, NULL, NULL, @GRAND_TOT_IN, NULL, NULL, NULL, @GRAND_TOT_OUT, NULL, NULL, NULL, NULL,'','','','')
  --		--INSERT INTO @TAB_STOCK VALUES('','','',NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
  --		--INSERT INTO @TAB_STOCK VALUES('','','','Total Transaction Balance',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @TOT_TRANS_BALANCE)
  --		--INSERT INTO @TAB_STOCK VALUES('','','',NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
  --		--INSERT INTO @TAB_STOCK VALUES('','','','G/L Balance',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @TOT_BALANCE)
    Return
  	END
  --
  --
  --
  --select whscode from owhs
  --select itemcode,itmsgrpcod from oitm
  --select itemcode,whscode from oibt
  --select itmsgrpcod,itmsgrpnam from oitb
  --
  --select distinct wh.whscode,tb.itmsgrpnam,tb.itmsgrpcod,tm.salpackun from owhs wh inner join oibt bt
  --on wh.whscode=bt.whscode
  --inner join oitm tm on tm.itemcode=bt.itemcode
  --inner join oitb tb on tm.itmsgrpcod=tb.itmsgrpcod
  --where tb.itmsgrpnam='Eau De Parfum FG'

  
  
  • Antworten
  • Zitieren