Suda! Again to restriction of items from PO based on SO

[Former Member]

Hi Suda!

Let me remind u abt the thread "Restriction for items from Purchase Order based on Sale Order" ( ), after tht

I have Tried in bringing out the stored procedure for Serial Number block too similarly from Batch Block stored procedure. as the client has many serial numbered item, i did so. it is working well Suda. But the problem is i cud'nt bring the SONO in the error message. Here is the procedure.

IF @Object_type = '15' AND @transaction_type = 'A'

BEGIN

If NOT Exists (SELECT DISTINCT T0.IntrSerial AS 'GR PO Serial', T10.ItemCode AS 'GRPO ItemNo.',

T10.BaseEntry AS 'GRPO DocEntry', T3.BaseEntry AS 'Delivery DocEntry'

FROM [dbo].[OSRI] T0

INNER JOIN [dbo].[SRI1] T10 ON T0.SysSerial = T10.SysSerial AND T0.ItemCode = T10.ItemCode

INNER JOIN [dbo].[PDN1] T1 ON T1.DocEntry = T0.BaseEntry

INNER JOIN [dbo].[POR1] T2 ON T2.DocEntry = T1.BaseEntry

INNER JOIN [dbo].[SRI1] T3 ON T3.SysSerial = T10.SysSerial

INNER JOIN [dbo].[DLN1] T4 ON T4.DocEntry = T3.BaseEntry

Where T2.BaseEntry != -1 AND T2.BaseEntry = T4.BaseEntry AND T4.DocEntry = @list_of_cols_val_tab_del)

BEGIN

DECLARE @SONO1 VARCHAR(10) SELECT DISTINCT @SONO1 = T3.DocEntry FROM [dbo].[OSRI] T0

INNER JOIN [dbo].[SRI1] T10 ON T0.SysSerial = T10.SysSerial and T0.ItemCode = T10.ItemCode

INNER JOIN [dbo].[PDN1] T1 ON T1.DocEntry = T10.BaseEntry

INNER JOIN [dbo].[POR1] T2 ON T2.DocEntry = T1.BaseEntry

INNER JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.BaseEntry

WHERE T0.IntrSerial = (SELECT T4.IntrSerial FROM OSRI T4 INNER JOIN SRI1 T5 ON T4.SysSerial = T5.SysSerial and

T4.ItemCode = T5.ItemCode where T5.BaseType = 15 AND T4.BaseEntry = @list_of_cols_val_tab_del AND

T5.ItemCode = T0.ItemCode)

SELECT @Error = 1, @error_message = 'This SERIAL no. linked with another Sales Order ' + @SONO1

END

END

I think some where i am going wrong in this above high lightened Procedure. Kindly help me to bring the Value of @SONO1.

i tried a lot to bring tht but failed to bring tht SONO num alone. The error msg is thrown as 'ADOC' alone.

thanking you in advance,

Meera.

[former_member583013 · Accepted solution]

You had everything correct but instead of T5.BaseEntry you had it as T4.BaseEntry. Please change it to T5.BaseEntry as shown below and it should work.

where T5.BaseType = 15 AND <Font Color="Brown" > T5.BaseEntry </Font> = @list_of_cols_val_tab_del

It had nothing to do with DataType.

Suda

[Former Member · Accepted solution]

Great Suda! it works well ... thk u so much!

Meera.

[Former Member]

Hi,

its due the docentry is integer and you try to push it into varchar. You`l have to do conversion to varchar as

... SELECT DISTINCT @SONO1 = CONVERT(varchar(10), T3.DocEntry) FROM ....

hope it helps

[former_member583013]

I will review and get back on this

Suda