cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure for blocking Draft Returns if a UDF is blank

jun_dolor
Participant
0 Kudos

I created a UDF RetReas (Return Reason) for Sales AR > Return. The UDF is applied to the rows. I need to block the adding of drafts if the UDF is blank. My code on the stored procedure, SBO_SP_TransactionNotification is not working. The draft for Return is still being added even if the UDF is blank. Here is the code

IF (@object_type ='112' AND @transaction_type IN ('A','U'))  
    BEGIN
	DECLARE @RetReason nvarchar(30)
SELECT @LineNum = b.VisOrder+1, @ObjType = CAST(A.ObjType as varchar(32)), @RetReason = COALESCE(B.U_RetReas,'') FROM ODRF A INNER JOIN DRF1 B ON A.DocEntry = B.DocEntry WHERE A.DocEntry = @list_of_cols_val_tab_del and ISNULL(A.WddStatus,'-') <> '-' IF (@ObjType = '16') IF ( @RetReason = '') BEGIN SET @error = -100018 SET @error_message=N'Return reason is required!' END END

Accepted Solutions (0)

Answers (1)

Answers (1)

azizelmir
Contributor
0 Kudos

Dear Jaime,

Please try this SP code:

IF @object_type='112' AND @transaction_type IN ('A', 'U')
BEGIN
IF EXISTS
(
  SELECT t1.DocEntry
  FROM ODRF t1 inner join DRF1 t2 on t1.docentry=t2.docentry
  WHERE t1.DocEntry=@list_of_cols_val_tab_del
		AND ISNULL(t1.WddStatus,'-') <> '-' 
        AND t2.U_RetReas  IS NULL
        AND (t1.ObjType='16')
)
BEGIN
	SET @error_message='Return reason is required!';
	SET @error= -100018;
END
END
Thank you,Aziz
jun_dolor
Participant
0 Kudos

Hi Aziz,

I tried the code but still got the same results. I'll check the stored procedure in it's entirety and see if there is something in conflict. Thank you for your assistance.

Best regards

jun_dolor
Participant
0 Kudos

Hi Aziz,

I think I know where my problem is coming from. The Return drafts were created beforehand. I'm trying to have the stored procedure from posting the draft to Return if the UDF is blank. I'll make modifications based on your code.

Best regards