Formatted Search

[Former Member]

We want to create a mandatory UDF to identify invoice order line quantites with quantity of specified inventory items are not between ranges identified per each item in user defined fields. We have created a UDF with the following formatted search to accomplish this:

SELECT ' '

WHERE

( $[$38.11.NUMBER] NOT BETWEEN

$[$38.U_RWeightMin.NUMBER] AND $[$38.U_RWeightMax.NUMBER])

When we use this query, instead of blanking out the fields for items that meet the criteria, it blanks out every quantity per each line of the invoice.

Any ideas on what we are doing wrong?

Thanks for your help

[former_member583013 · Accepted solution]

Stephanie,

Is this the same scenario like the requirement of creating an Approval Procedure that we discussed earlier.

I might have a different solution if you would be interested.

Suda

[Former Member · Accepted solution]

Suda,

Yes it is the same one- please tell me you have a solution. We are anxious to get this done.

Thanks for your help

[former_member583013 · Accepted solution]

Where are these two user fields U_RWeightMin and U_RWeightMax.

Are they in the Item Master too.

Please add the following Code to your SBO_SP_TransactionNotification stored proc and it should do the trick. I am presuming the above two user fields are in Item Master. If your field names are different change the names according in my code below.

IF @object_type = '13' AND @transaction_type = 'A'

BEGIN

IF EXISTS (SELECT @status = 'T' FROM [DBO].[OINV] T0 INNER JOIN [DBO].[INV1] T1 ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN [DBO].[OITM] T2 ON T2.ITEMCODE = T1.ITEMCODE

WHERE (T1.QUANTITY NOT BETWEEN T2.U_RWeightMin AND U_RWeightMax) AND T0.DOCENTRY = @list_of_cols_val_tab_del )

BEGIN

SELECT @Error = 1, @error_message = 'Quantity not in Min Max range'

END

END

Regards

Suda

[Former Member · Accepted solution]

> Where are these two user fields U_RWeightMin and

> U_RWeightMax.

>

> Are they in the Item Master too.

>

> Please add the following Code to your

> SBO_SP_TransactionNotification stored proc and it

> should do the trick. I am presuming the above two

> user fields are in Item Master. If your field names

> are different change the names according in my code

> below.

>

> IF @object_type = '13' AND @transaction_type = 'A'

> BEGIN

> IF EXISTS (SELECT @status = 'T' FROM [DBO].[OINV] T0

> INNER JOIN [DBO].[INV1] T1 ON T0.DOCENTRY =

> T1.DOCENTRY

> INNER JOIN [DBO].[OITM] T2 ON T2.ITEMCODE =

> T1.ITEMCODE

> WHERE (T1.QUANTITY NOT BETWEEN T2.U_RWeightMin AND

> U_RWeightMax) AND T0.DOCENTRY =

> @list_of_cols_val_tab_del )

> BEGIN

> SELECT @Error = 1, @error_message = 'Quantity not in

> Min Max range'

> END

> END

>

> Regards

>

> Suda

Thanks Suda,

I have never edit a sp before, do I just open it and past the script in?

Thanks for your help

[Former Member · Accepted solution]

Hi Suda,

I am getting the following error when I attempt to save the script: error 137, must declare the variable @status. What do I need to do?

Thanks

[former_member583013 · Accepted solution]

Login to SQL Server Management Studio. Start > Run ..SqlWb.exe

Under Database listing on the left hand window (Object explorer) Click the plus and Expand..go to Progammability....Stored Procedure..locate the SBO_SP_TransactionNotification Procedure here. Right mouse click and select modify.

You will a area with words "-- ADD YOUR CODE HERE".

Paste my code below it and Click the execute button on the Top tool Bar.

It would show "Command(s) completed successfully."

You can now close this window and continue with SBO.

Sorry my mistake, I changed the code but forgot to remove the variable

Use this one

IF @object_type = '13' AND @transaction_type = 'A'

BEGIN

IF EXISTS (SELECT T1.DOCENTRY FROM [DBO].[OINV] T0 INNER JOIN [DBO].[INV1] T1 ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN [DBO].[OITM] T2 ON T2.ITEMCODE = T1.ITEMCODE

WHERE (T1.QUANTITY NOT BETWEEN T2.U_RWeightMin AND T2.U_RWeightMax) AND T0.DOCENTRY = @list_of_cols_val_tab_del )

BEGIN

SELECT @Error = 1, @error_message = 'Quantity not in Min Max range'

END

END

[Former Member · Accepted solution]

> Login to SQL Server Management Studio. Start > Run

> ..SqlWb.exe

>

> Under Database listing on the left hand window

> (Object explorer) Click the plus and Expand..go to

> Progammability....Stored Procedure..locate the

> SBO_SP_TransactionNotification Procedure here. Right

> mouse click and select modify.

>

> You will a area with words "-- ADD YOUR CODE HERE".

>

> Paste my code below it and Click the execute button

> on the Top tool Bar.

>

> It would show "Command(s) completed successfully."

>

> You can now close this window and continue with SBO.

>

> Sorry my mistake, I changed the code but forgot to

> remove the variable

>

> Use this one

>

> IF @object_type = '13' AND @transaction_type = 'A'

> BEGIN

> IF EXISTS (SELECT T1.DOCENTRY [DBO].[OINV] T0 INNER

> JOIN [DBO].[INV1] T1 ON T0.DOCENTRY = T1.DOCENTRY

> INNER JOIN [DBO].[OITM] T2 ON T2.ITEMCODE =

> T1.ITEMCODE

> WHERE (T1.QUANTITY NOT BETWEEN T2.U_RWeightMin AND

> U_RWeightMax) AND T0.DOCENTRY =

> @list_of_cols_val_tab_del )

> BEGIN

> SELECT @Error = 1, @error_message = 'Quantity not in

> Min Max range'

> END

> END

Suda,

Thanks for the information; I appreciate your help.

I am still having a problem getting the sp to run; I am getting an error 170,"incorrect syntex near'.' on the third line of your sp.

Any ideas on how to correct?

thanks again

[former_member583013 · Accepted solution]

Stephanie,

I have corrected the SQL on the previous message. You may use it now.

Suda

[Former Member · Accepted solution]

Hi Suda,

Thanks for all of your help

[Former Member · Accepted solution]

Hi Suda-

I would I change this sp, if the Min/Max is at the invoice line item level instead of at the item master level.

Thanks for your help!