Query help on SP TN - Block when doesn't meet predefined condition

[Former Member]

Hi,

I need help on the TN SP, the requirement as below:

1) I had created a UDF in Item Master Data with value "YES" / "NO"
2) There are two unique item named "NOTE" and "Battery"
3) When creating SO, item with UDF value YES should follow with item named "NOTE" OR "Battery" in the same order but different row, else will be block

Example as below:

Item with UDF value "YES" = ITEM001, ITEM002, ITEM003
Item with UDF value "NO" = ITEM004, ITEM005, ITEM006

Condition One:
SO # 001
Row 1: ITEM001
Row 2: NOTE
Result: Passed

Condition Two:
SO # 002
Row 1: ITEM001
Row 2: Battery
Result: Passed

Condition Three:
SO # 003
Row 1: ITEM001
Row 2: Battery / NOTE
Row 3: ITEM005
Result: Passed

Condition Four:
SO # 004
Row 1: ITEM004
Row 2: ITEM006
Result: Passed

Condition Five:
SO # 005
Row 1: ITEM004
Row 2: ITEM001
Result: Block

My query as below, but it doesn't meet my requriement

If @object_type = '17' and @transaction_type in( 'A','U')

BEGIN

IF EXISTS

(Select T0.ItemCode From RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

Where T0.ItemCode != 'NOTE' OR T0.ItemCode != 'BATTERY' AND T2.U_BATTERY = 'YES' AND T0.DocEntry=@list_of_cols_val_tab_del)

BEGIN

SET @error = 999

SET @error_message = '001'

END

END

[former_member185682 · Accepted solution]

Hi Olga,

I created an environment like your, and I tested all scenarios that you mentioned and this query works fine.

IF @object_type = '17' and @transaction_type in( 'A','U')
BEGIN
	--First check if you have some item with your udf equals yes
	IF (SELECT COUNT(*) FROM RDR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T1.U_BATTERY = 'YES') > 0
	BEGIN
		--Then check if you have a NOTE or BATTERY
		IF (SELECT COUNT(*) FROM RDR1 T0
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND UPPER(T0.ItemCode) = 'NOTE' OR UPPER(T0.ItemCode) = 'BATTERY') = 0
		BEGIN
			SET @error = 999
			SET @error_message = '001'
		END
	END
END

Hope it helps.

Kind Regards,

Diego Lother

[Former Member · Accepted solution]

Hi Diego,

The below query work for me, anyway, thank you for your help, really appreciated.

Thanks

IF @object_type = '17' and @transaction_type in( 'A','U')
BEGIN
	--First check if you have some item with your udf equals yes
	IF (SELECT COUNT(*) FROM RDR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T1.U_BATTERY = 'YES') > 0
	BEGIN
		--Then check if you have a NOTE or BATTERY
		IF (SELECT COUNT(*) FROM RDR1 T0
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND UPPER(T0.ItemCode) IN ('NOTE', 'BATTERY')) = 0
		BEGIN
			SET @error = 999
			SET @error_message = '001'
		END
END
END

[Former Member · Accepted solution]

Hi Diego,

can you help on this question?

https://answers.sap.com/questions/110432/approval-procedure-terms-based-on-query-help.html

Thanks

[Former Member]

Hi Diego,

Thank you for your reply and effort.

Refer below the image.

Thanks

[Former Member]

Hi Diego,

Refer below the image requested.

Anyway, I had revised the code on T2.U_BATTERY to T1.U_BATTERY as there is no T2

With the new query, I found out it allow to crate SO with TEST-PN01 directly, even without NOTE or BATTERY, can you confirm?

Thanks

[former_member185682]

Hi Olga,

About T2.U_BATTERY you are correct I made a mistake.

Could you share an image with the properties of your UDF field BATTERY, I will create in my environment for me to be able to test the situation.

Kind Regards,

Diego Lother

[Former Member]

Hi Diego,

Thank you for the reply.
I had tested with the query, it will block when item with "YES" + Battery.
It will go through if item with "YES" + NOTE

Can help to find out the cause?

Thanks

[former_member185682]

Hi Olga,

Could you share an image of your order in sap with your udf field and ItemCode showed in the picture?

If your item Battery is not in capital letters as NOTE, try this new query:

IF @object_type = '17' and @transaction_type in( 'A','U')
BEGIN
	--First check if you have some item with your udf equals yes
	IF (SELECT COUNT(*) FROM RDR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T2.U_BATTERY = 'YES') > 0
	BEGIN
		--Then check if you have a NOTE or BATTERY
		IF (SELECT COUNT(*) FROM RDR1 T0
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND UPPER(T0.ItemCode) = 'NOTE' OR UPPER(T0.ItemCode) = 'BATTERY') = 0
		BEGIN
			SET @error = 999
			SET @error_message = '001'
		END
	END
END

Kind Regards,

Diego Lother

[Former Member]

Hi Diego,

Can you help ?

Thanks

[former_member185682]

Hi Olga,

Try something like this:

IF @object_type = '17' and @transaction_type in( 'A','U')
BEGIN
	--First check if you have some item with your udf equals yes
	IF (SELECT COUNT(*) FROM RDR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T2.U_BATTERY = 'YES') > 0
	BEGIN
		--Then check if you have a NOTE or BATTERY
		IF (SELECT COUNT(*) FROM RDR1 T0
		WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T0.ItemCode = 'NOTE' OR T0.ItemCode = 'BATTERY') = 0
		BEGIN
			SET @error = 999
			SET @error_message = '001'
		END
	END
END

Hope it helps.

Kind Regards,

Diego Lother

[Former Member]

Hi Diego,

Do you mind to help me on these question?

https://answers.sap.com/questions/300895/stored-procedure-to-limit-a-udf-character-limit.html

Thanks