Query for Approval Procedure

[Former Member]

Hi all,

I set up an approval procedure for sales orders, the term is based in this user query:

SELECT DISTINCT 'TRUE'  WHERE $[$38.14.Number]<$[$38.U_PrecMin.Number]

We need that query working for every single row of the sales order, because it only works when it's true on row one, but when a unit price is less than the value of that UDF on row two or above, the procedure does not work.

Any suggestion?

Thanks

[former_member204969 · Accepted solution]

Sorry to say, but it is the limitation of the system that in an approval query you can check only data from the first line and at the header level.

[Former Member · Accepted solution]

Hi,

¿Any other way to validate changes in RDR1.Price when is below at the value of U_PrecMin, at row level?

Thanks in advance

[Former Member]

Hi......

Try this in SP Transnotification.......

If @Object_type='17' and (@transaction_type ='A' or @transaction_type ='U')
BEGIN
declare @Item1 as varchar(100)
declare @minline1 int
declare @maxline1 int
declare @Price1 float
declare @Price2 float
set @minline1 = (select min(T0.linenum) from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del)
set @maxline1 = (select max(T0.linenum) from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del)

while @minline1<=@maxline1
		begin
		select @Item1=T0.itemcode from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del and T0.linenum=@minline1			
		select @Price1=T0.Price from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del and  T0.linenum=@minline1
			select @Price2=T0.U_PrecMin from RDR1 T0 where T0.docentry=@list_of_cols_val_tab_del and  T0.linenum=@minline1
		if 	@Price1<@Price2
			begin
				set @error=-1
				set @error_message = 'Price is Less ' + @Item1
			End
			set @minline1=@minline1+1
		end
End

Regards,

Rahul

[Former Member]

Hi,

Thanks for all your replies.

If we set a SP would be a limitant, because if they change unit prices, they just need an approval, not a warning, because it wouldn't let them save the document, and it's not what we need.

The approval procedure only verifies data at header level, so I created an extra UDF at row level, it shows quantity*PriceMin, a sort of Minimum Total by row, Is there a way to sum up that values row by row on a UDF of a header level by a formatted search?, to compare it with ORDR.DocTotal.

¿Any suggestion?

Thanks in advance

[Former Member]

Hi....

Your solution is also good but you have to manage it bit manually.......

But its good solution......

Regards,

Rahul

[Former Member]

Hi,

You may check this:

Thanks,

Gordon