Skip to Content
0
Mar 06, 2013 at 02:09 PM

adding values with FMS

15 Views

Hello to all!

i have created in marketing Documents a field called Location. i am interested to add a specific value to this field in marketing documents in case that the itemcodes start with 8

what i have done till now is

i made the UDF called Location with 2 values 1is empty and 2 is the number i want to.

i have made a UDF in lines of Marketing Docunments called Location as well. the queries i made are the followings

DECLARE

@ID AS INT,

@CODE AS NVARCHAR(15)

select @ID=$[OINV.U_location]

SELECT @CODE=$[inv1.ITEMCODE]

SELECT DBO.F_GL_PWLHSEIS(@ID,@CODE)

i call this query in lines of marketing documents

the funaction is the following one

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

alter FUNCTION [dbo].[F_GL_PWLHSEIS]

(

@ID1 AS INT,

@code AS nvarchar(10)

)

RETURNS NVARCHAR(30)

AS

BEGIN

DECLARE

@RESULT AS NVARCHAR(30)

IF @ID1=2 and @code LIKE '8%'

BEGIN

SELECT @RESULT='00-04-1'

END

RETURN @RESULT

END

unfortunately, it does not work as i want in case that i make an Invoice as a copy to or copy from.

in case that in an Order the lines have values in Location field in lines, then by using the Copy to Invoice, these values are being lost

i actually want to fill the Location field in A/R Invoice in case that the itemcode starts with 8 and in case that there are in parent documents values in the Location field these values have to stay as is.

thanks you so much for your time!