set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE  [dbo].[prcCursor]
AS
SET NOCOUNT ON
declare @QTYvarX as numeric(18,2)
declare @QTYvar as numeric(18,2)
declare @CostvarNew as numeric(18,2)
declare @UnitCount int
DECLARE @iRowIdX  int,@vchInv_noX  numeric(18,0),@vchbatchX  numeric(18,0),@vchInv_dateX datetime, @vchQty_inX numeric(18,0), @vchQty_OutX numeric(18,0),@vchCostX  numeric(18,2),@vchCost_AvgX numeric(18,2),@vchCodeX varchar(1000), @vchSERIALNOX numeric(18,2)
DECLARE @iRowId  int,@vchInv_no  numeric(18,0),@vchInv_date datetime, @vchQty_in numeric(18,0), @vchQty_Out numeric(18,0),@vchCost numeric(18,2)
DECLARE asSalesBatch CURSOR FOR                                                        

SELECT    iRowId,inv_no,batch,inv_date,qty_in, qty_out, cost,cost_avg,code,qty_balance FROM asSalesBatch order by code,inv_date,IROWID
update asbatch set qty_out=0
drop table [asSales]
CREATE TABLE [dbo].[asSales](	[code] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[inv_no] [numeric](18, 0) NULL,	[batch] [numeric](18, 0) NULL,	[inv_date] [datetime] NULL,	[deptno] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[qty_in] [numeric](18, 0) NULL,	[qty_out] [numeric](18, 0) NULL,	[qty_balance] [numeric](18, 0) NULL,	[cost] [numeric](18, 2) NULL,	[cost_avg] [numeric](18, 2) NULL,	[iRowId] [numeric](18, 0) IDENTITY(0,1) NOT NULL) ON [PRIMARY]
OPEN asSalesBatch 
FETCH asSalesBatch INTO @iRowIdX,@vchInv_noX,@vchbatchX,@vchInv_dateX,@vchqty_inX, @vchqty_outX, @vchcostX,@vchCost_AvgX,@vchCodeX, @vchSERIALNOX
WHILE @@Fetch_Status = 0
BEGIN
		DECLARE asBatch CURSOR FOR                                                        
		SELECT   iRowId,inv_no,inv_date,qty_in, qty_out, cost FROM asBatch where code= @vchCodeX  and (qty_in-qty_out)>0 order by inv_date,QTY_BALANCE,IROWID
		OPEN asBatch 
		FETCH asBatch INTO @iRowId,@vchInv_no,@vchInv_date,	@vchqty_in, @vchqty_out, @vchcost
		set @UnitCount=1
		set @QTYvar=0
		set @qtyVar=@vchqty_inx
		set @CostvarNew = @vchcost
		WHILE @UnitCount = 1
		   BEGIN
		set @CostvarNew = @vchcost
				if (@vchqty_in - @vchqty_out)>=@qtyVar
				begin
					insert into assales (code,inv_no,batch,inv_date,deptno, qty_in,qty_out, qty_balance, cost, cost_avg) values(@vchCodeX, @vchInv_noX, @vchInv_no,@vchInv_dateX,'',@qtyVar,0,@vchSERIALNOX,@CostvarNew,0)
					UPDATE asBatch SET QTY_OUT= QTY_OUT + @qtyVar WHERE IROWID=@IROWID
					set @unitCount=0
					set @qtyVar= 0
				end
			else
				begin
					insert into assales (code,inv_no,batch,inv_date,deptno, qty_in,qty_out, qty_balance, cost, cost_avg) values(@vchCodeX, @vchInv_noX, @vchInv_no,@vchInv_dateX,'x',(@vchqty_in - @vchqty_out),0,@vchSERIALNOX,@CostvarNew,0)
					
					UPDATE asBatch SET QTY_OUT= QTY_OUT + (@vchqty_in - @vchqty_out) WHERE IROWID=@IROWID
					set @qtyVar= @qtyVar - (@vchqty_in - @vchqty_out)
				end
		   FETCH asBatch INTO @iRowId, @vchInv_no, @vchInv_date,@vchqty_in,	@vchqty_out, @vchcost
		   END
		CLOSE asBatch
		DEALLOCATE asBatch
    FETCH asSalesBatch INTO @iRowIdx, @vchInv_nox,@vchBatchx, @vchInv_datex,@vchqty_inx,	@vchqty_outx, @vchcostx, @vchcost_avgx,@vchCodeX, @vchSERIALNOX

   END
CLOSE asSalesBatch
DEALLOCATE asSalesBatch
RETURN