FIFO Allocation between Sales and Sales Order Table based on Schedule

<blockquote class="quote"><div class="op">Quote:</div>ALTER PROCEDURE [dbo].[ISP_INT_DELFIFO] 
@ENTRY_TY VARCHAR(2),@TRAN_CD INT
AS
BEGIN	
	SET NOCOUNT ON;

	IF OBJECT_ID('TEMPDB..#MAIN') IS NOT NULL
		DROP TABLE #MAIN

	DELETE FROM I_DELIVER_REF WHERE ENTRY_TY=@ENTRY_TY AND TRAN_CD=@TRAN_CD 

	SELECT I.entry_ty SENTRY_TY,I.TRAN_CD STRAN_CD,I.ITSERIAL SITSERIAL,I.QTY SQTY,O.entry_ty OENTRY_TY,O.Tran_cd OTran_cd,O.itserial Oitserial
	INTO #MAIN
	FROM STITEM I 
	INNER JOIN STITREF R ON (I.ENTRY_TY=R.ENTRY_TY AND I.TRAN_CD=R.TRAN_CD AND I.ITSERIAL=R.ITSERIAL) 
	INNER JOIN SOITEM O ON (O.ENTRY_TY=R.rentry_ty AND O.TRAN_CD=R.Itref_tran AND O.ITSERIAL=R.RITSERIAL) 
	WHERE I.ENTRY_TY=@ENTRY_TY AND I.Tran_cd=@TRAN_CD
	ORDER BY I.DATE,I.INV_NO,I.TRAN_CD,I.ITSERIAL,I.QTY,O.Tran_cd,O.itserial,O.qty
	--select * from #MAIN
	DECLARE @SENTRY_TY VARCHAR(2),@STRAN_CD INT,@SITSERIAL VARCHAR(5),@SQTY DECIMAL(20,2),@OENTRY_TY VARCHAR(2),@OTRAN_CD INT,@OITSERIAL VARCHAR(5)
	DECLARE MAIN_CURSOR CURSOR FOR SELECT * FROM #MAIN
	OPEN MAIN_CURSOR
	FETCH NEXT FROM MAIN_CURSOR INTO @SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY,@OENTRY_TY,@OTRAN_CD,@OITSERIAL
	WHILE @@FETCH_STATUS = 0
		BEGIN

			IF OBJECT_ID('TEMPDB..#DEL') IS NOT NULL
				DROP TABLE #DEL
			
			SELECT D.DEL_ID,D.sched_qty-SUM(ISNULL(R.QTY,0.00)) QTY 
			INTO #DEL
			FROM I_DELIVER D 
			LEFT JOIN I_DELIVER_REF R ON (D.del_id=R.DEL_ID) 
			WHERE D.entry_ty=@OENTRY_TY AND D.tran_cd=@OTRAN_CD AND D.ITSERIAL=@OITSERIAL
			GROUP BY D.DEL_ID,D.sched_qty,D.sched_dt
			HAVING D.sched_qty-SUM(ISNULL(R.QTY,0.00))>0
			ORDER BY D.sched_dt,D.del_id

			--SELECT * FROM #DEL

			DECLARE @DEL_ID INT,@QTY DECIMAL(20,2)
			DECLARE DEL_CURSOR CURSOR FOR SELECT * FROM #DEL
			OPEN DEL_CURSOR
			FETCH NEXT FROM DEL_CURSOR INTO @DEL_ID,@QTY
			WHILE @@FETCH_STATUS = 0 AND @SQTY>0
				BEGIN
					IF @QTY>=@SQTY 
						BEGIN
							INSERT INTO I_DELIVER_REF VALUES(@DEL_ID,@SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY)
							SET @SQTY=0
						END
					ELSE 
						BEGIN
							INSERT INTO I_DELIVER_REF VALUES(@DEL_ID,@SENTRY_TY,@STRAN_CD,@SITSERIAL,@QTY)
							SET @SQTY=@SQTY-@QTY
						END

					FETCH NEXT FROM DEL_CURSOR INTO @DEL_ID,@QTY
				END
			CLOSE DEL_CURSOR
			DEALLOCATE DEL_CURSOR

			DROP TABLE #DEL

			FETCH NEXT FROM MAIN_CURSOR INTO @SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY,@OENTRY_TY,@OTRAN_CD,@OITSERIAL
		END
	CLOSE MAIN_CURSOR
	DEALLOCATE MAIN_CURSOR

	DROP TABLE #MAIN

END
</blockquote>