Currency document rate in invoice ?

Hello everyone, i need your help and opinion here.

My company released a new policy so every time we printed sales invoice with rate other than my native rate the invoice would have currency document number printed on it. Rate are always updated every week and always inserted to a new row in currency exchange rates table.

For example:

rate HKD in 01/07/12 is 8.946,00 and the currency document no. HKD_KM21021

next week rate are…

rate HKD in 01/14/12 is 9.000,00 and the currency document no. HKD_KM21230

I have been thinking about adding another field in table 330 for the document no and add more field in sales header and sales inv header. but i don’t know what to do next.

Can someone give a solution on what to do to solve this?

Can someone help ? I’m still stuck.

Do you want to print currency rate & document no on Invoice ? or will this currency rate will have any impact on Transaction ?

@amol

Yes. I do want to print currency rate & currency document no. Allow me to describe it with more detail:

For example, i added another field in Table 330 named “Curr Document No”. I also add it on sales header table, sales line table, sales invoice header table, sales invoice line table, sales shipment header table and sales shipment line table.

I need this curr document no. to show up on sales header, but i need it to work like currency rate in sales header.

As you already know, everytime posting date on sales order changed it will check table 330, and if the exchange rate on the new posting date is different from the previous one nav will ask us if we want to change current currency rate to follow currency rate on the new date.

I also need the “curr document no” value to show up on sales header everytime i create a new sales order sales order. (assuming i filled “curr document no” for my default currency on table 330).

Are you sure you are going to update exchange rate on regular basis ??

If yes then you can validate on the basis of date of exchange rate and sales header by adding field in Table 330 .

You need to write a code on Currency Code field and OnValidate trigger to show the required field on sales header.

Yes, i usually update rate every one week, thats a policy in my country and my company followed them. The only problem i stuck here actually on code, because i don’t know on what trigger it should be and what code i need.

You need to write a code in UpdateCurrencyFactor function in Sales header table to update currency document no field in Sales order.

I have tried to write it but seems like it didn’t work out

Here’s what i add (a text in bold):

IF “Currency Code” <> ‘’ THEN BEGIN

IF (“Document Type” IN [“Document Type”::Quote,“Document Type”::“Blanket Order”]) AND

(“Posting Date” = 0D)

THEN

CurrencyDate := WORKDATE

ELSE

CurrencyDate := “Posting Date”;

“Currency Factor” := CurrExchRate.ExchangeRate(CurrencyDate,“Currency Code”);

"Currency Document No" := CurrExchRate.“Currency Document No”;

END ELSE

“Currency Factor” := 0;

"Currency Document No" := ‘’;

Could you pinpoint with more detail what i should write ?

If you are printing invoice from posted invoice then

IF salesinvheader.“Currency Code” <> ‘’ THEN BEGIN

CurrExchRate.reset;

CurrExchRate.setrange(“currency code”,salesinvheader.“Currency Code”);

CurrExchRate.setrange(“starting date”,0D,salesinvheader.“posting date”);

IF CurrExchRate.find(’+’) then

“Currency Document No” := CurrExchRate.“Currency Document No”

else

“Currency Document No” := ‘’;

end;

Also you can check the code written on the Sales Invoice Header Form->Foreign Trade and when you click in the assistbutton.

Where to put this code ?

It seems didn’t work when i print it, i am using post and print in sales order.

I did found codes on assist edit below. But what to do with this code ?

ChangeExchangeRate.SetParameter(“Currency Code”,“Currency Factor”,“Posting Date”);

IF ChangeExchangeRate.RUNMODAL = ACTION::OK THEN BEGIN

VALIDATE(“Currency Factor”,ChangeExchangeRate.GetParameter);

CurrForm.UPDATE;

END;

CLEAR(ChangeExchangeRate);

You need to write your code in the report which is getting printed after you post the order.

Tried it and still can’t, perhaps i put it on wrong place.

Is there no other way ? for example to put the currency document no. from currency exchange rate to sales header automatically? The reason i put field currency document no in sales header because user demands so that they are able to edit currency document no. in sales order before post it to invoice

Then you need to follow what Amol has suggested.

Create a new field in Sales Header & Sales Invoice Header with same ID.

Write your code to get currency document in sales header.

As same id is there the data will be transferred to sales invoice header automatically(i.e. Currency Document).

I 've already create Currency Document No. in three table as follows:

50076 Currency Exchange No. in table Currency Exchange Rate

50083 Currency Exchange No. in table Sales Header

50083 Currency Exchange No. in table Sales Invoice Header

What code i should write in sales header ?

Write the code to retrieve the Currency Exchange No. from Currency Exchange Rate & store it in the sales header table field.

That is what i don’t know. I never learned to program c/al before. could you be so kind to give me a sample ?

Perhaps i could learn from your sample.