how to make field mandatory

[Former Member]

Hi all,

i want to make price ,tax code and ware house code in purchase order rows mandatory.

i have designed following Stored procedure..

IF @transaction_type IN (N'A', N'U') AND 
(@Object_type = N'22')

BEGIN 
if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE   T1.[Price] = 0 
and T1.DOCENTRY = @list_of_cols_val_tab_del)

select @Error = 1, @error_message = 
'Enter Price'


else

if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[TaxCode] is  null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin
select @Error = 2, @error_message = 
'Enter Tax Code'
end

else if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[WhsCode] is  null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin

select @Error = 3, @error_message = 
'Enter Warehouse Code'
end
END

but system checks only for price and not for other two validation .

kindly suggest me way where system will check for all the validation defind in the Stored Procedure.

[Former Member]

thanks suda.

[Former Member]

Try this then:

IF @transaction_type IN (N'A', N'U') AND 
(@Object_type = N'22')
 
BEGIN 
if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE   T1.[Price] = 0 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin 
select @Error = 1, @error_message = 
'Enter Price'
end
 
else if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[TaxCode] is null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin
select @Error = 2, @error_message = 
'Enter Tax Code'
end
 
else if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[WhsCode] is null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin
 
select @Error = 3, @error_message = 
'Enter Warehouse Code'
end
END

If your price is 0, the other conditions will have no chances to check.

[Former Member]

Hi gordon,

thanx for ur reply.

my previous error is solved but validation is working only for PRICE and not for Tax code and ware house.

if i click on the tax code then only validaiton for tax code is working.

if i enter price and keep the tax code,whs code blank with out clicking those field then its allowing to add purchase order.

[former_member583013]

Try this one.

IF @transaction_type IN (N'A', N'U') AND 
(@Object_type = N'22')
 
BEGIN 
if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE   T1.[Price] = 0 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin 
select @Error = 1, @error_message = 
'Enter Price'
end
 
If exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[TaxCode] is null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin
select @Error = 2, @error_message = 
'Enter Tax Code'
end
 
If exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE    T1.[WhsCode] is null 
and T1.DOCENTRY = @list_of_cols_val_tab_del)
begin
 
select @Error = 3, @error_message = 
'Enter Warehouse Code'
end
END

[reno1]

Hi Chetan

Try this, This should definitely work

if @object_type = '22' and @transaction_type in ('A','U') 
begin
select @Total = (select T1.Price from OPOR T0 join POR1 T1 on T0.docentry = T1.docentry 
where T0.docentry=cast(@list_of_cols_val_tab_del as int))
Select @comm = (select T1.TaxCode from OPOR T0 join POR1 T1 on T0.docentry = T1.docentry 
where T0.docentry=cast(@list_of_cols_val_tab_del as int))
Select @venref = (select T1.TaxCode from OPOR T0 join POR1 T1 on T0.docentry = T1.docentry 
where T0.docentry=cast(@list_of_cols_val_tab_del as int))

if @Total = 0
begin
set @Error =11
set @Error_Message = 'Cannot have Zero Price'
end
else 
if @comm is null 
begin
set @Error =12
set @Error_Message = 'Select Tax'
end
else 
if @venref is null 
begin
set @Error =13
set @Error_Message = 'Select Warehouse'
end

end

Regards

Reno

[Former Member]

hi rohan,

i tried ur solution but syill system only checking for price and allowing to add purchase order if tax code is empty.

[Former Member]

Try this one:

IF @transaction_type IN (N'A', N'U') AND 
(@Object_type = N'22')
 
BEGIN 
if exists (SELECT T0.docnum FROM [dbo].[OPOR] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.docENTRY
WHERE ((T1.[Price] = 0 OR T1.[TaxCode] is null OR T1.[WhsCode] is null)
and T1.DOCENTRY = @list_of_cols_val_tab_del)

begin

if T1.[Price] = 0
begin
select @Error = 1, @error_message = 'Enter Price' 
end

else if T1.[TaxCode] is null
begin
select @Error = 2, @error_message = 'Enter Tax Code' 
end
 
else if T1.[WhsCode] is null
begin
select @Error = 3, @error_message = 'Enter Warehouse Code'
end

end

END

Thanks,

Gordon

[Former Member]

Hi gordon,

i got following error

Msg 4104, Level 16, State 1, Procedure SBO_SP_TransactionNotification, Line 525
The multi-part identifier "T1.Price" could not be bound.
Msg 4104, Level 16, State 1, Procedure SBO_SP_TransactionNotification, Line 530
The multi-part identifier "T1.TaxCode" could not be bound.
Msg 4104, Level 16, State 1, Procedure SBO_SP_TransactionNotification, Line 535
The multi-part identifier "T1.WhsCode" could not be bound.

[Former Member]

Hi,

Thanks

Mansoor