problem in validation with copying from documents

[vasileiosfasolis]

Hello to all!

i have made a validation for the users in order to fill in Goods Receiipt PO a field called Location.

the function creatred is as follows

IF @object_type = N'20' AND (@transaction_type = N'A' OR @transaction_type = N'U') --OR @transaction_type = N'U')

    BEGIN

        SELECT @LOC4=U_Location FROM PDN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

        SELECT @LOC41=U_BarCode from  [@CHM_WHS1]

        select @BASEREF=BASEREF FROM PDN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

        IF (select @LOC4) IS NULL

        BEGIN

            SET @error=2

            SET @error_message=N'Please Fill The Location Field'

        END   

        If (select @LOC4) not in ( select U_BarCode from  [@CHM_WHS1])

       BEGIN

            SET @error=2

            SET @error_message= N'The Location '+@LOC4+N' is wrong '

END

END

if the user does not fill the field location the message 'Please Fill The Location Field' is displayed

if the user types a location that does not exist in a table then the message is the 'The Location '+@LOC4+N' is wrong '

the actual problem now is while a user makes a new Goods Receipt PO by using the Copy from an existing document such as a Purchase Order.

if it is considered that in the Purchase order, the field is filled with a wrong location, then in Goods Receipt my function does not validate this field.

i made some tests such as

i created a PO with a wrong Location

i saved it

i made a new Goods Receipt by using a copy from the one i created before

if i leave the document as is and there are some lines with not Location filled, then the validation will take place only for the lines that are empty and not for the values transfered from the PO

it is quite strange because i would like to lock the PO as well but it is not possible to fill the Location filled during this action because it is impossible to know where to keep these items while the Goods Receipt takes place so this field must remain empty

[former_member204969 · Accepted solution]

Try this code:

IF @object_type = N'20' AND @transaction_type in (N'A',N'U')

Begin

declare @loc4  nvarchar(10)

If exists

(Select 1 from PDN1 p where p.docentry=@LIST_OF_COLS_VAL_TAB_DEL

  and p.U_Location is null )

Select @error=2,@error_message= N'Please Fill The Location Field'

else

begin

(Select top 1 @loc4=p.U_Location from PDN1 p where p.docentry=@LIST_OF_COLS_VAL_TAB_DEL

  and p.U_Location not in  (select U_BarCode from  [@CHM_WHS1]) )

If @loc4 is not null

Select @error=3,@error_message= N'The Location '+@LOC4+N' is wrong'

end

End      

[vasileiosfasolis · Accepted solution]

Istvan you are totally right!