<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
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
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>