Click here to Skip to main content
15,939,620 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
It's a an Order Processing related query. There is a sales order which will have different products with some specific quantity. Each qty will have schedule for delivery. In this case, for 55 nos it has the schedule as 10, 15, 30. same way it has once more set of schedule. In Sales details table you get detail of the material sold against each item. You will get details like sale order no and item line no. We need to take schedule qty in FIFO basis and allocate the required qty. I need to get a result set as shown and have to update the sales order detail table. Please help me
Posted
Updated 27-Jun-14 18:07pm
v2
Comments
Andrius Leonavicius 27-Jun-14 23:28pm    
Hi,

I think you're missing some important information. Please check your question (use "Improve question" button).
Sandeep Mogaveer 28-Jun-14 0:07am    
Question updated.

hi,

I think that you will need to push history of your inserted data.
The question is not clear can you explain more plz ?

Bechir
 
Share this answer
 
SQL
<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>
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900