SP_TransactionNotification base on UDT as Valid Values

[former_member541807]

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

[KennedyT21 · Accepted solution]

Try this

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 isnull(T1.U_HEQUIPT,'')='' ) and T0.[ObjType] = '60') 

  BEGIN 

  Select @error = 10, @error_message = N'Pls. Fill-up Assetcode' 

  END 

  END 

Regards

Kennedy

[former_member541807 · Accepted solution]

Hello Kennedy,

thanks for the response.

the script you had given will only block to add draft if my UDF "U_HEQUIPT" is null, but it does not evaluate if the given value in UDF "U_HEQUIPT" is valid based on "@HEQUIPT" UDT.... that is my problem,  i can only trap if the field is empty or null but i cannot trap if the user had entered a valid value in the field....

please help with my requirement, every input is highly appreciated.

thank you..

Fidel

[KennedyT21 · Accepted solution]

Try this

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