Skip to Content
0
Aug 17, 2015 at 06:37 AM

SP_TransactionNotification base on UDT as Valid Values

30 Views

Hello Everyone,

im doing a draft Goods Issue.

this is my requirement. i want to prevent adding draft Goods Issue when my UDF called "U_HEQUIPT" is null and Values must be VALID base on my UDT called "@HEQUIPT". currently this my SP_TransactionNotification code, but it only block adding the draft Goods Issue if UDF "U_HEQUIPT" is EMPTY but it does not check if it is Valid Values base on my UDT "@HEQUIPT". please see image attached

IF (@object_type = '112') and (@transaction_type IN (N'A', N'U'))
BEGIN
IF EXISTS (SELECT T0.Docentry from ODRF T0 Inner Join DRF1 T1 ON T0.Docentry=T1.Docentry where
T1.DocEntry = @list_of_cols_val_tab_del and (T1.U_HEQUIPT is null or T1.U_HEQUIPT ='' ) and T0.[ObjType] = '60')
  BEGIN
  Select @error = 10, @error_message = N'Pls. Fill-up Assetcode'
  END
  END

here is my second code but still cannot get the result i want.

IF (@object_type = '112') and (@transaction_type IN (N'A', N'U'))
BEGIN
IF EXISTS (SELECT T0.Docentry from ODRF T0 Inner Join DRF1 T1 ON T0.Docentry=T1.Docentry where
T1.DocEntry = @list_of_cols_val_tab_del and (T1.U_HEQUIPT Not In(SELECT T3.[Code] FROM [dbo].[@HEQUIPT]  T3)) and T0.[ObjType] = '60')
  BEGIN
  Select @error = 10, @error_message = N'Pls. Fill-up AssetCode'
  END
  END

Please help.

thank you.

Fidel

Message was edited by: Avelino Fidel

Attachments

ValidAssetCode.png (77.4 kB)