cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure Notification

former_member645084
Participant
0 Kudos

Hi

I want Notification if Production Order has not been issued user should not be able to receipt it.

I am trying below code but it is not working

IF @transaction_type = 'A' AND @Object_type = '59'
BEGIN
IF EXISTS (SELECT T0.DOCENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)
BEGIN
DECLARE @entry INT
SELECT @entry = T0.BASEENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del
IF EXISTS (SELECT T1.ITEMCODE, T1.PLANNEDQTY, T2.QUANTITY, T2.BASEENTRY AS ISSUED QTY FROM dbo.OWOR T0 INNER JOIN dbo.WOR1 T1
ON T0.DOCENTRY = T1.DOCENTRY LEFT OUTER JOIN dbo.IGE1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE 
WHERE T1.PLANNEDQTY > ISNULL(T2.QUANTITY, 0) AND T0.DOCENTRY = @entry)
SELECT @Error = 1, @error_message = 'Components NOT ISSUED'
END
ELSE
SELECT @Error = 1, @error_message = 'Components NOT ISSUED'
END



The multipart could not be bound.

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

IHi,

Your code is working in my SQL. The only error i can find is, the issued quantity should be in quotation mark 'Issue Qty'

IF @transaction_type = 'A' AND @Object_type = '59'

BEGIN IF EXISTS

(SELECT T0.DOCENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)

BEGIN DECLARE @entry INT

SELECT @entry = T0.BASEENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del

IF EXISTS (SELECT T1.ITEMCODE, T1.PLANNEDQTY, T2.QUANTITY, T2.BASEENTRY AS 'ISSUED QTY' FROM dbo.OWOR T0 INNER JOIN dbo.WOR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT OUTER JOIN dbo.IGE1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE WHERE T1.PLANNEDQTY > ISNULL(T2.QUANTITY, 0) AND T0.DOCENTRY = @entry)

SELECT @Error = 1,

@error_message = 'Components NOT ISSUED' END ELSE SELECT

@Error = 1, @error_message = 'Components NOT ISSUED'

END

Regards,

Nagarajan

mgregur
Active Contributor
0 Kudos

Hi,

here you go (don't forget to declare, I usually do it at the beginning for all variables so it's not in this part of code):

IF :object_type=N'59' AND :transaction_type = N'A' THEN
	SELECT IFNULL (COUNT(*),1) INTO issue_cnt
	FROM IGN1 T1
	INNER JOIN OWOR T2 ON T1."ItemCode" = T2."ItemCode" AND T1."BaseEntry" = T2."DocEntry" AND T1."BaseType" = T2."ObjType"
	INNER JOIN WOR1 T3 ON T2."DocEntry" = T3."DocEntry" AND T1."BaseEntry" = T3."DocEntry" AND T1."BaseType" = '202'
	WHERE T3."IssuedQty" < T3."PlannedQty" AND T1."DocEntry" = :list_of_cols_val_tab_del;
	IF :issue_cnt > 0 THEN
		BEGIN
			error := 1;
			error_message := N'Not all quantities have been released for production';
		END;
	END IF;
END IF;

BR,

Matija