销售和销售订单表之间的FIFO分配基于计划

问题描述

这是一个与订单处理相关的查询。有一个销售订单，其中包含具有特定数量的不同产品。每个数量都有交货时间表。在这种情况下，对于55个，它的时间表为10,15,30。同样，它具有一次又一组的时间表。在销售明细表中，您可以获得针对每个项目销售的物料的详细信息。您将获得销售订单号和项目行号等详细信息。我们需要在FIFO基础上采用schedule qty并分配所需的数量。我需要获得如图所示的结果集，并且必须更新销售订单明细表。请帮我

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

推荐答案

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

这篇关于销售和销售订单表之间的FIFO分配基于计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！