Validation Query

[Former Member]

Hi Experts,

To restrict inventory transfer to be posted from one whs to other warehouses. i have written below SP. Please check below query.

But whenever i update my bill of material, it is stopping me to update and add the bill of material. I am not getting how it is relating to Bill of Material. It is showing the error "[Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the nvarchar value 'SB 512HN4 P- L3' to datatype int.(CINF)".

If i remove my below query from SP. Then it is working fine. Please help me to solve this.

IF @transaction_type=N'A' AND @Object_type = N'67'

Declare @a nvarchar(10)

Declare @b nvarchar(10)

set @a = (Select a.Filler from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

                  where a.UserSign = 76 and a.DocEntry =@list_of_cols_val_tab_del)

set @b = (select a.ToWhsCode from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

                  where a.UserSign = 76 and a.ToWhsCode = b.WhsCode and a.DocEntry

=@list_of_cols_val_tab_del)

BEGIN

if (@a <> '12') --and (@b <> 05)

begin

SET @error = '201'

SET @error_message = N'You are not permitted to select other warehouse except 12....!'

end

if (@b <> 05)

begin

SET @error = '202'

SET @error_message = N'You are not permitted to select other warehouse except 05....!'

end

if exists (select a.DocEntry from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

                  where a.ToWhsCode <> b.WhsCode and a.DocEntry = @list_of_cols_val_tab_del

                  and a.UserSign = 76)

begin

SET @error = '203'

SET @error_message = N'Whs Code(ToWhsCode) at Header and Items level should be same....!'

end

END

Please let me know how can i come out of this error.

[former_member197621 · Accepted solution]

Hi,

I have checked your query in my test DB,am wondering that its not allowing me to update the BOM.

It may bug in the version. However i have changed the query as per below then its working fine.

If (@object_type = '67') and @transaction_type IN ('U','A')

 if exists (select Distinct 'Error'   from OWTR aj inner join WTR1 bj on aj.DocEntry = bj.DocEntry
                  where (aj.ToWhsCode <> bj.WhsCode or aj.Filler <> '12' or aj.ToWhsCode <> '05') and aj.DocEntry = @list_of_cols_val_tab_del
                  and aj.UserSign = '76')
begin
SET @error = '203'
SET @error_message = N'Warehouse combination is worng...From whs should be (12) OR To whs should be (05)....!'
end

[Former Member · Accepted solution]

Hi,

i have checked your query but not working fine, because my requirement is below.

1) From warehouse should be only 12 and To warehouse should be only 05. if user 76 selects other warehouse it should not be added.

2) From and To warehouse of Header item should be same as From and To warehouses of line items in matrix.

Because of which i have written below query. If you have alternative please provide.

If (@object_type = '67') and @transaction_type IN ('U','A')

Declare @a nvarchar(20)

Declare @b nvarchar(20)

set @a = (Select a.Filler from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

  where a.UserSign = '1' and a.DocEntry =@list_of_cols_val_tab_del)

set @b = (select a.ToWhsCode from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

  where a.UserSign = '1' and a.ToWhsCode = b.WhsCode and a.DocEntry

=@list_of_cols_val_tab_del)

BEGIN

if (@a <> '12') --and (@b <> 05)

begin

SET @error = '201'

SET @error_message = N'You are not permitted to select other warehouse except 12....!'

end

if (@b <> 05)

begin

SET @error = '202'

SET @error_message = N'You are not permitted to select other warehouse except 05....!'

end

if exists (select a.DocEntry from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

  where a.Filler <> b.FromWhsCod and a.DocEntry = @list_of_cols_val_tab_del

  and a.UserSign = '1')

begin

SET @error = '203'

SET @error_message = N'Whs Code(FromWhsCode) at Header and Items level should be same....!'

end

if exists (select a.DocEntry from OWTR a inner join WTR1 b on a.DocEntry = b.DocEntry

  where a.ToWhsCode <> b.WhsCode and a.DocEntry = @list_of_cols_val_tab_del

  and a.UserSign = '1')

begin

SET @error = '204'

SET @error_message = N'Whs Code(ToWhsCode) at Header and Items level should be same....!'

end

END

Thanks

[former_member197621 · Accepted solution]

Hi,

Check the below query and tell me exactly which condition is not working, so that i can help you easily

If (@object_type = '67') and @transaction_type IN ('U','A')

if exists (select Distinct 'Error'   from OWTR aj inner join WTR1 bj on aj.DocEntry = bj.DocEntry
                  where (aj.Filler <> '12' or aj.ToWhsCode <> '05'
  or aj.Filler <> bj.FromWhsCod or aj.ToWhsCode <> bj.WhsCode) and aj.DocEntry = @list_of_cols_val_tab_del
                  and aj.UserSign = '76')
begin
SET @error = '203'
SET @error_message = N'Warehouse combination is worng...From whs should be (12) OR To whs should be (05)....!'
end

[Former Member · Accepted solution]

For example.

1) I am giving From Warehouse = 12 and To Warehouse = 02. Then it is allowing me to add the document. This should not be allowed. It should be blocked.

If i give From warehouse = 12 and To warehouse = 05 then only it should allow me to add the document.

2) Moreover FromWhs and ToWhs of Header and Lineitems in matrix should be same.

Thanks

[former_member197621 · Accepted solution]

Hi,

Have you checked in user 76, Bcz query will block only for user code "76"

[Former Member · Accepted solution]

Yes i checked in mentioned user only.

i think you have to put 'AND' instead of 'OR'. But it is not working also.

Thanks

[former_member197621 · Accepted solution]

Hi,

Try to comment your old stored procedure and try only with this, i have checked in my database its working fine from my side

If (@object_type = '67') and @transaction_type IN ('U','A')

if exists (select Distinct 'Error'   from OWTR aj inner join WTR1 bj on aj.DocEntry = bj.DocEntry
                  where (aj.Filler <> '12' or aj.ToWhsCode <> '05'
  or aj.Filler <> bj.FromWhsCod or aj.ToWhsCode <> bj.WhsCode) and aj.DocEntry = @list_of_cols_val_tab_del
                  and aj.UserSign = '76')
begin
SET @error = '203'
SET @error_message = N'Warehouse combination is worng...From whs should be (12) OR To whs should be (05)....!'
end

[Former Member · Accepted solution]

Yes checked. Now it is working fine...

I am getting one more issue. I am having one addon which contain a screen/userdefined screen called "Inward Stock Posting".

Under this screen i am having three button 'ADD', 'CANCEL', 'StockPosting'. First user will add this document by filling necessary info.

Later user clicks on 'StockPosting' button, then automatically inventory transfer will be added.

Apart from the above, users are opening 'Inventory Transfer' Screen from the form setting and raising/adding manually. How to block this one.

If we remove the authorization then addon stock posting(inventory transfer) is not working

Thanks

[former_member197621 · Accepted solution]

Hi,

As per forum rules one question per thread. So pls close this thread and ask this question in new thread.

[Former Member · Accepted solution]

Thanks for helping me in finding the solution.

Regards

[kothandaraman_nagarajan]

Hi,

Did you check my query?

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Do you have any FMS or stored procedure for BOM?

Thanks & Regards,

Nagarajan

[Former Member]

thanks for reply..

I don't have any stored procedure but FMS. Whenever i am removing this validation query i am able to add and update. whereas when i activate my query in SP, it is throwing error and stopping to update and add the BOM.

Thanks

[kothandaraman_nagarajan]

Hi,

Temporarily remove FMS in BOM and then try.

Thanks & Regards,

Nagarajan

[Former Member]

I checked again. There is no FMS in BOM. Please guide me how to rectify this.

Thanks

[kothandaraman_nagarajan]

Hi,

'SB 512HN4 P- L3' is ware house code (filler) for header?

Thanks & Regards,

Nagarajan

[Former Member]

HI,

This is the "Product No" in "Bill of Material".

Thanks

[kothandaraman_nagarajan]

Hi,

Please check SAP notes:

1298609 - UDO_Error when updating user defined Table or
Object

967470
- Field type in SBO_SP_TransactionNotification was changed

If still error message appears let me know.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Everything is right at my end.

FYI.

ALTER proc [dbo].[SBO_SP_TransactionNotification]

@object_type nvarchar(20), -- SBO Object Type

@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

@num_of_cols_in_key int,

@list_of_key_cols_tab_del nvarchar(255),

@list_of_cols_val_tab_del nvarchar(255)

AS

begin

-- Return values

declare @error  int -- Result (0 for no error)

declare @error_message nvarchar (200) -- Error string to be displayed

select @error = 0

select @error_message = N'Ok'

Please help me for solution of the same.

Thanks

[kothandaraman_nagarajan]

Hi,

Not tested, try this:

IF @transaction_type in ('A','U') AND (@OBJECT_TYPE='67')

BEGIN

If  exists (SELECT T0.[Docentry] FROM OWTR T0  INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID inner join WTR1 t2 on t2.docentry = t0.docentry  WHERE T1.[UserID] = '76' and  T0.[Filler] <> 12 and T1.[WhsCode] <> 05 and T0.Docnum =  @list_of_cols_val_tab_del )

Begin

SET @error = 345575
SET @error_message = 'Only Super Is Authorized to Make The Changes'

End

End

Thanks & Regards,

Nagarajan