Questions about changing currency rates and encumbrances

Question

Because of the rising Canadian dollar, we have realized that our outstanding emcumbrances are not being kept in sync with the changing ratio between the USD and CDN dollar.

I checked in the CRM and found this:

"Adding a new ratio rather than modifying the existing ones will enable you to keep a history of currency changes.
When you add a new ratio, it will only take effect from the next transaction you make, while modifying the existing currency will change all previous transaction values as well."

We have always add a new ratio. So that would be why old encumbrances are remian untouched - until the point where the order is actually paid, when I believe it uses the currency in effect for the payment date. Have I got that right?

I need to understand what would happen if we switched to modifying the ratio rather than adding a new one. What exactly does "change all previous transaction values as well" mean? Surely paid invoices would not be affected?

And how and when does this happen? Each time an unpaid order is displayed in the GUI or printed via the batch report? Would the totals against a budget be recalculated on the fly?

In order to get our encumbrances to be more accurrate, we would need to go back into the past currency lines and delete them expect one, which we would use to maintain the 'current currency'. Have I got that right? And would we then have to use acq-08 to update local prices, which says: "Use this service only if you have retroactively changed or added a currency ratio"?

And what about year-end rollover? This from the CRM: "When renewing order encumbrances for serials (p-acq-06-b), the original currency ratio used to be maintained every year. This has been fixed so that now, the currency ratio and the local sum are updated each year when the encumbrances are renewed." What about monographs and SO?

Answer

1. So that would be why old encumbrances are remain untouched - until the point where the order is actually paid, when I believe it uses the currency in effect for the payment date. Have I got that right?

RESPONSE: Yes, that is correct.

2. What exactly does "change all previous transaction values as well" mean? Surely paid invoices would not be affected? And how and when does this happen? Each time an unpaid order is displayed in the GUI or printed via the batch report? Would the totals against a budget be recalculated on the fly?

RESPONSE: According to the help for the Update Local Price of Budget Transaction (acq-08) service [this would be run after currency update to update the local price of a budget transaction]: "This comprises all transaction types, including ENC (encumbrance) and INV (invoice)."

Totals against a budget would be recalculated when manual updates are made on a record-by-record basis or when the Update Local Price of Budget Transaction (acq-08) service is run and any new budget transactions that are created after the z82 (currency ratio) update would take effect immediately.

Do NOT to run this service if:

- The z601 (budget transaction) has a zero ("0") currency ratio
- There are no z82 currency-ratio tables for all the past years (no currency ratio history)

The Update Local Price of Budget Transaction (acq-08) service will recalculate the z601_local_sum based on the currency ratio it finds in the z82 table for the z601_currency_date (or z601_open_date) of this transaction. It takes the z601_active_sum and uses that to calculate the z601_local_sum. In doing this recalculation it won't consult the z601_currency_ratio because in most cases it will be zero, so it will consider the ratio in the z82 historical currency record closest to it, by date.

f you look at your PROD region, all but 16 of your z601s (budget transactions) in PROD have a zero currency ratio. So if you were to "go back into the past currency lines and delete them expect one" and ran acq-08, it would consult the one remaining currency ratio. I think this is what you want to happen, however, since acq-08 will update **all** transaction types, including INV (invoice), I don't think you would want to run this.

3. In order to get our encumbrances to be more accurate, we would need to go back into the past currency lines and delete them expect one, which we would use to maintain the 'current currency'. And would we then have to use acq-08 to update local prices, which says: "Use this service only if you have retroactively changed or added a currency ratio"?

RESPONSE: Depending on what currency dates you're thinking of, you may want to update these manually and/or with SQL rather than use the Update Local Price of Budget Transaction (acq-08) service. Also, be aware that there are two potentially undesirable consequences of running p_acq_08:

1. The initial allocation (z601_type "ILC") records are changed to zero (because the z601_active_sum in such records is zero, while the z601_local_sum has the actual active amount).

2. The recalculated z601_local_sum's for converted records will be incorrect for 2 reasons:
a. the z601_currency_ratio is almost never populated (it is zero)
b. sites typically do not have z82 currency-ratio tables for all the past years represented in the transactions. Therefore, the system will not have a proper ratio to use for these past transactions.

4. And what about year-end rollover?

RESPONSE: Are you wondering what will happen when open encumbrances for this fiscal year (and their currency ratios/dates) are rolled-over into the next fiscal year? Everything is based on the currency date. If it's an ENC (e.g. for an open order) it will look for the currency date closest to the order date.

5. What about monographs and SO? This from the CRM: "When renewing order encumbrances for serials (p-acq-06-b), the original currency ratio used to be maintained every year. This has been fixed so that now, the currency ratio and the local sum are updated each year when the encumbrances are renewed."

RESPONSE: If you run p-acq-06-b, the latest currency ratio (determined by date) will be used to update the encumbrances and local sums. I think it would be more accurate to say: "This has been fixed so that now, the local sum is updated each year when the encumbrances are renewed."

As per response #3, you may want to update these orders manually and/or with SQL rather than use the Update Local Price of Budget Transaction (acq-08) service (due to the two potentially undesired results outlined above).

If you need to check for ENC only (i.e. NOT INV) and you want the currency ratio for USD:CAN for the past two months to be 1:1 - you could do the following SQL:

update z601 set z601_local_sum = z601_active_sum where z601_open_date > '20070914' and z601_type = 'ENC' and z601_currency = 'USD' ;

Then you could use the GUI to correct the USD currency ratio for the appropriate

• Article last edited: 10/8/2013