IF OBJECT_ID('spFluctuationPaidBill') IS NOT NULL
 DROP PROC spFluctuationPaidBill
GO

CREATE PROCEDURE spFluctuationPaidBill
          @FromDate DateTime,
		  @ToDate DateTime
AS
BEGIN

SET NOCOUNT ON;

CREATE TABLE #FluctuationTable(
ID INT IDENTITY(1,1),ItemCode nvarchar(100),ItemDesc nvarchar(100),OnHandQty int,GRPONo int,
GRPODate datetime,ICRate decimal(18,12),GRPOQty int,RestQty int,RatePerQty decimal(18,12),APInvNo int,
APInvDate datetime,OGRate decimal(18,12))

INSERT INTO #FluctuationTable
SELECT OITM.ItemCode,OITM.ItemName,sum(OITW.onhand) as onhand,abc.basenum as GRPONo, abc.docdate as GRPODate,
(select top 1 docrate from opdn where docentry = abc.baseentry and docnum = abc.basenum) as GRPORate,
(select  sum(quantity) from pdn1 where docentry = abc.baseentry and abc.itemcode = itemcode) As GRPOQty,null,

(select top 1 price from pdn1 where docentry = abc.baseentry and abc.itemcode = itemcode) As GRPOUnitPrice, 

(select distinct top 1 opch.docnum from opch inner join pch1 on opch.docentry = pch1.docentry 
where pch1.baseentry = abc.baseentry) as APInvNo,

(select distinct top 1 opch.docdate from opch inner join pch1 on opch.docentry = pch1.docentry 
where pch1.baseentry = abc.baseentry) as APInvDt,
(select distinct top 1 opch.u_dodamt from opch inner join pch1 on opch.docentry = pch1.docentry
where pch1.baseentry = abc.baseentry) as APInvDocRate

FROM OITW 

INNER JOIN OITM ON OITW.ItemCode = OITM.ItemCode 
INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod 
left join sri1 as abc on OITW.ItemCode = abc.ItemCode

WHERE(OITW.OnHand <> 0 Or oitw.iscommited <> 0 Or oitw.onorder <> 0 Or oitw.minstock <> 0)
AND (OITW.ItemCode IN (SELECT pdn1.ItemCode FROM Opdn 

inner join pdn1 on opdn.docentry = pdn1.docentry 
where opdn.series = 314 or opdn.series = 94 or opdn.series = 663))and (abc.Direction = 0) 
AND (abc.LineNum =(SELECT MAX(LineNum) AS Expr1 FROM  SRI1
WHERE (ItemCode = abc.ItemCode) AND (SysSerial = abc.SysSerial))) AND basetype = 20
and abc.docdate >= (@FromDate) and abc.docdate <= (@ToDate)

group by OITM.ItemCode,OITM.ItemName, oitm.suppcatnum, 
abc.basenum, abc.sysserial, abc.baseentry, abc.basenum, abc.docdate, 
oitm.ItmsGrpcod, oitb.ItmsGrpNam, abc.baseentry, abc.SysSerial, abc.ItemCode,oitw.itemcode
order by OITM.ItemName,abc.basenum

DECLARE @ID INT
DECLARE @COUNT INT
DECLARE @ITEM VARCHAR(20)
DECLARE @GRPO INT
DECLARE @TOTAL INT
DECLARE @SUM INT
SELECT @COUNT = COUNT(*) FROM #FluctuationTable
SET @ID =1


select @COUNT


WHILE (@ID  <= @COUNT)
BEGIN
	SELECT @ITEM=ItemDesc, @GRPO=GRPOQty,@TOTAL=OnHandQty FROM #FluctuationTable WHERE ID=@ID 

	IF (@TOTAL > @GRPO)
		UPDATE #FluctuationTable SET RestQty = @GRPO WHERE ID=@ID
	ELSE
		BEGIN

			select SUM(GRPOQty) FROM #FluctuationTable WHERE ItemDesc=@ITEM 
			AND OnHandQty=@TOTAL AND GRPOQty<>@GRPO AND ID > @ID

select SUM(GRPOQty) FROM #FluctuationTable where OnHandQty=28 and GRPOQty<>40 AND ID > @ID

			UPDATE #FluctuationTable SET RestQty = (OnHandQty-@SUM) WHERE ID=@ID
		END	
	SET @ID = @ID + 1
END


select itemcode,ItemDesc,OnHandQty,GRPONo,GRPOQty,RestQty from #FluctuationTable
group by itemcode,ItemDesc,OnHandQty,GRPONo,GRPOQty,RestQty order by itemcode,GRPONo

END
GO

EXEC spFluctuationPaidBill '05/01/2009','12/01/2009'