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