Need a help on a query using user defined fields UDFs and the main formtype

[Former Member]

Dear Experts

I need a help to write a query. I'm not a developper but an implementation consultant.

This my problem

I'm working on the Table: OWOR

Formtype 1: 65211, Item $[$10.0.0] (Is a combobox also with 'Planned' and 'Released' as options.

Formtype 2: -65211; item $[$U_MRP.0.0] (A user defined filds combobox : 'Pending'; 'QC'; PTL' as options

I want to write a query in SBO 9.2 that block user to change the statut Planned into Released if the statut in the UDFs is deferent from 'QC'

Please give a help

Best regards / My email gahafranck@yahoo.com

[former_member545090 · Accepted solution]

Hi,

put below code in sql SBO

 IF(@transaction_type in (N'A',N'U'))
    BEGIN 	
			IF EXISTS  
			(
					SELECT		1 
					--"Docnum","status" 
					FROM		OWOR 
								INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry                      
					WHERE		OWOR.Status='R'AND OWOR.U_MRP='QC' -- this is UDF condtion
AND 
								OWOR.DocEntry= @list_of_cols_val_tab_del				
			)
			BEGIN
					SET @OutPut=1		
					SET @error_no=10001
					SET @error_text=' You cannot change the status Planned into Released '
			END
			
	END	    

Regards,

Tejas P

[Former Member · Accepted solution]

Thanks a lot Tejas patis

I tested it and it works.

Kind regards

Franck GAHA

[Former Member]

Can you advise me query code for thi store procedure?

Best regards

franck GAHA

[Former Member]

Dear ANKIT

Thank a lot for your reply. I don't have access to the server tools. I'm working from a SBO client access. And the client use B1 USABILITY as add-on to manage validations and workflows.

But if I have the proper query based on SP-transaction i coulld use it as user query in B1 usability.

But I'm not skilled for that.

Best regards

Franck GAHA

[ANKIT_CHAUHAN]

Hi franck aubert joel thomas gaha,

Why don't you handle this scenario under SBO_SP_TransactionNotofication?

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[Former Member]

Please can you advise a store procedure transaction for this?