$(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
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 12, 2017 at 09:47 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 20, 2016 at 07:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 21, 2016 at 07:40 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

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

    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

    Add comment
    10|10000 characters needed 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

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

    Hi Diego,

    Thank you for your reply and effort.

    Refer below the image.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded