$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

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

Dec 20, 2016 at 07:31 AM

105

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
DIEGO LOTHER Jan 12, 2017 at 09:47 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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
0
Former Member
0
DIEGO LOTHER Dec 20, 2016 at 07:39 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member
0
avatar image
Former Member Dec 21, 2016 at 07:40 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0
Former Member
DIEGO LOTHER

Hi Diego,

Can you help ?

Thanks

0
avatar image
Former Member Dec 30, 2016 at 07:28 AM
0

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


1.jpg (61.1 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
avatar image
Former Member Jan 12, 2017 at 02:15 AM
0

Hi Diego,

Thank you for your reply and effort.

Refer below the image.

Thanks


1.jpg (51.8 kB)
Share
10 |10000 characters needed characters left characters exceeded