Skip to Content
0
Jan 18, 2012 at 03:21 AM

Stored Procedures for GRPO over receipt control

13 Views

Hi all.

I need help in one of the SBO Stored Procedure.

I have used SBO_SP_TransactionNotification to control the maximum quantity that the system can received in GRPO when the quantity received is more than what we ordered in the PO.

Previously, I have set the system to receive POQty * 10% in GRPO. Now, I wish to change the condition to receive POQty * 10% or POQty + 5 or whichever higher.

Below is the code for the stored procedure and I don't know how to modify it to meet the above requirement:

IF @OBJECT_TYPE='20' AND @TRANSACTION_TYPE='A'

BEGIN
		DECLARE @ITEMCODE NVARCHAR (20)
		DECLARE @DOCENTRY INT
		DECLARE @LINENUM INT
		DECLARE @POQTY NUMERIC(19, 6)
		DECLARE @GRQTY NUMERIC(19, 6)
		SELECT @DOCENTRY = 0
		SELECT @POQTY = 0
		SELECT @GRQTY = 0
		SELECT @DOCENTRY = CONVERT(INT, SUBSTRING(@LIST_OF_COLS_VAL_TAB_DEL, 1, 10))
		SELECT GR.LINENUM, GR.ITEMCODE, POQTY=(PO.QUANTITY)*1.1, GRQTY=(SELECT SUM(GR1.QUANTITY) 
			FROM PDN1 GR1 WHERE GR1.BASEENTRY=GR.BASEENTRY AND GR1.BASELINE=GR.BASELINE 
			GROUP BY GR1.BASEENTRY, GR1.BASELINE)
		INTO #STR_GRPO
		FROM PDN1 GR INNER JOIN POR1 PO ON GR.BASETYPE='22' AND GR.BASEENTRY=PO.DOCENTRY AND GR.BASELINE=PO.LINENUM
		WHERE GR.DOCENTRY=@DOCENTRY 
		
		SELECT TOP 1 @LINENUM=LINENUM+1, @ITEMCODE=ITEMCODE, @POQTY=POQTY, @GRQTY=GRQTY 
			FROM #STR_GRPO WHERE GRQTY>POQTY ORDER BY LINENUM 

	IF @GRQTY>@POQTY
		BEGIN
			SELECT @error=1
			SELECT @error_message='ERROR: TOTAL RECEIVED QTY('+CONVERT(VARCHAR(20),@GRQTY)+') > PO QTY('+CONVERT(VARCHAR(20),@POQTY)+')! LINENUM: '+CONVERT(VARCHAR(3), @LINENUM)+', ITEMCODE: '+@ITEMCODE
		END
	END