cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure Notification

ramco1917
Participant
0 Kudos

Hi

  I want if Item is only "Inventory Item" then user must enter in Remarks column & preferred vendor should not be greater than 1

Thanks

View Entire Topic
SonTran
Active Contributor
0 Kudos

Hi,

Try this

IF :object_type = '4' and (:transaction_type = 'A') THEN
	BEGIN

		IF EXISTS (
					SELECT T0."ItemCode"
					FROM OITM T0 LEFT JOIN ITM2 T1 on T0."ItemCode"=T1."ItemCode"
					WHERE T0."ItemCode" = :list_of_cols_val_tab_del AND T0."InvntItem"='Y' AND T0."SellItem"='N' AND T0."PrchseItem"='N' 
							AND IFNULL(TO_NVARCHAR(T0."UserText"),'')=''										
					)
		THEN
			BEGIN
				error := 401;
				error_message := N'Inventory Item Only must input remark';
			END;
		END IF;
		
		IF EXISTS (
					SELECT T0."ItemCode"
					FROM OITM T0 LEFT JOIN ITM2 T1 on T0."ItemCode"=T1."ItemCode"
					WHERE T0."ItemCode" = :list_of_cols_val_tab_del AND T0."InvntItem"='Y' AND T0."SellItem"='N' AND T0."PrchseItem"='N' 					
					GROUP BY T0."ItemCode"
					HAVING count(T1."VendorCode")>1										
					)
		THEN
			BEGIN
				error := 402;
				error_message := N'Inventory Item Only have one preferred vendor only';
			END;
		END IF;
	END;
END IF;

Hope this helps,

Son Tran