seed data for Currency Types has wrong code for Canadian Dollars

[Andrea Neiman]

The Currency Types grid in Acq Admin includes three stock currencies -- US Dollar, Euro, and Canadian Dollar.

Per ISO 4217, Canadian Dollar should have an alphabetic code of CAD -- we have this as CAN. Since the other two currencies list the correct ISO alphabetic code, the Canadian one should be corrected as well.

[Erica Rohlfs (erohlfs)]

Confirmed in 3.2.1

I'll add (and maybe a separate bug) that attempting to manually adjust the code doesn't save.

Administration -> Acquisitions Administration -> Currency Types -> Double click the Canadian Dollar currency type -> secondary window opens, Record Editor: Currency Type -> manually adjust CAN to CAD -> Save

A message displays that the Currency Type Update Succeeded, but it did not save the change.

[Andrea Neiman (aneiman)]

I filed the save failure at bug 1808012.

[Rogan Hamby (rogan-hamby)]

I independently just found the save bug myself and didn't know there was a separate ticket for it.

Patch for seed data and setting acq.exchange_rates to_currency and from_currency to update on cascade.

patch here:

user/rogan/lp1807998cad_not_can

[Jane Sandberg (sandbergja)]

Thanks for your patch, Rogan. A few thoughts:

1) There is a small copy/pasto on lines 4-5 of the upgrade script.

UPDATE acq.exchange_rate SET from_currency = 'CAD' WHERE code = 'CAN';
UPDATE acq.exchange_rate SET to_currency = 'CAD' WHERE code = 'CAN';

Should have from_currency and to_currency, respectively, instead of code. I'm also curious if those two lines are necessary at all. If you put the second half of the script first, the CAN => CAD change should cascade.

2) Several other tables reference currency_type, apart from exchange_rate. At a quick glance, I saw acq.fund, acq.funding_source, and acq.fund_debit. Could you please include those in your script too?

FWIW, I get this error message when I try to run your upgrade script on a concerto database:

ERROR: update or delete on table "currency_type" violates foreign key constraint "funding_source_currency_type_fkey" on table "funding_source"
DETAIL: Key (code)=(CAN) is still referenced from table "funding_source".

[Jason Stephenson (jstephenson)]

I took a quick look at this just now. (The tag update email caught my eye.)

I made some edits to the db upgrade so it works, but I wonder if the funding_source_currency_type_fkey on acq.funding_source should cascade on update. It should probably remain a deferred constraint?

[Jason Stephenson (jstephenson)]

I pushed a branch with a single commit to collab/dyrcona/lp1807998_cad_not_can

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/dyrcona/lp1807998_cad_not_can

It "steals" Rogan's work, modifies the acq.funding_source.currency_type constraint to cascade. modifies the database upgrade so it working in 1 go as a single transaction, and adds a PgTap regression test.

It works for me on concerto and production data, so I think it is ready to go.

[Lindsay Stratton (lstratton)]

On testing, code for Canadian Dollars is CAD and the code appears in secondary interfaces for funds, fund sources, etc.

I have tested this code and consent to signing off on it with my name, Lindsay Stratton and my email address, <email address hidden>.

[Jane Sandberg (sandbergja)]

Thanks, Rogan, Jason, and Lindsay. Pushed to rel_3_12 and above.

[Blake GH (bmagic)]

All,

Running this upgrade script on a production database, I found that this upgrade script doesn't take care of all of the foreign keys:

ERROR: update or delete on table "currency_type" violates foreign key constraint "provider_currency_type_fkey"

ERROR: update or delete on table "currency_type" violates foreign key constraint "fund_debit_origin_currency_type_f....

We ended up having to edit the upgrade script with these two UPDATE's:

UPDATE acq.fund_debit SET origin_currency_type ='CAD' WHERE origin_currency_type = 'CAN';
UPDATE acq.provider SET currency_type ='CAD' WHERE currency_type = 'CAN';

FYI.

I'll make a new LP for this.

[Jeff Davis (jdavis-sitka)]

See bug 2086105 for Blake's follow-up fix.