Rewrite of transfer fund

Rewrite of transfer fund

I am not sure how much of this rewrite is correct.

I believe I identified a number of places where currency conversions
were missing.  As well, I have cut out some IF branches.

My comments are verbose at the moment to help me figure out what is
going on.

I have also added a second function acq.current_fund_allocation, which
returns the total amount of money allocated to the current fund.

This allows the acq.transfer_fund to determine if it is trying to
transfer more money than is currently available.

A few more sanity checks have also been added.  And, the currency
conversion is done strictly from the database now.  It seems
inconsistent to me to convert between old_fund and new_fund according to
a rate defined outside of this funciton but to convert between the funds
and the funding sources with values from acq.exchange_rate

(sitka) [RT18248] Working on getting rollover to work

In the process of figuring out how to make rollover work with my
modified transfer_fund function.

Need to print out the values trying to be transferred back to the
funding source via RAISE EXCEPTION

(sitka) [RT18248] Fixing asq.transfer_fund function

The function has to be broken up into parts.  One for transfering funds
and one for returning funds to funding sources.  This work so far has
created the code for transfering between funds.  The acq.fund_allocation
table needs to be updated to have fund_amount and conversion_ratio
fields added.  fund_amount will allow the funds to display their total
without fluctuating depending on the worth of the funding source
currency and conversion ratio is good to have for accounting purposes.

(sitka) [RT18248]

Fixed transfer_fund.  It was using an amount to return to the funding
source based on the amount allocated by the funding source.  But, it was
converting this amount to the currency of the fund.  So, if the exchange
rate changes, then athe amount allocated to the fund will change for the
transfer when it should stay constant.

To do this, we add a fund_amount field to the acq.fund_allocation table.
This amount is stored in the curecny of the fund, which will allow us to
determine how much money is in a fund even when the exchange rates
change.

This commit also adds the fuction acq.return_funds_to_source.  This is
used when funds are returned to a funding source without then
transferring them to another fund.  This can happen when rolling over
acqusitions at year end depending on the options chosen.

(sitka) [RT18248] Added updates for rollover functions

Added updates for the acq.rollover_funds_by_org_unit and
acq.rollover_funds_by_org_tree functions, so they use the new
acq.return_funds_to_source function when appropriate.

As well, aded a commented out DROP line to remove the old version of the
acq.transfer_fund function.

(sitka) [RT18248] Tracked down all old transfer_fund references

This code modifies the upgrade to the acq.rollover_by_org_unit and
acq.rollover_by_org_tree to use acq.return_funds_to_source rather than
acq.transfer_fund.  As well, it modifies all old versions of transfer
fund because the new version has new parameters.

(sitka) [RT18248] Fixing stuff via pgTap

A number of syntax errors presented themselves when using pgTap to test
the changes.  These have been fixed.

(sitka) [RT18248] Fixing rollover

The transfer_fund function needed and still needs work.  But, the
current version is working.  It needs to be modified, so we can tell the
difference between potential transfers and real transfers.

The rollover_funds_by_org_tree function needed to be updated to use the
spent total when doing year end and only transferring encumbered funds.
By using the spent total, it now returns that amount to the funding
source.  We do not use the combined total because that includes the
encumbered funds and we want those to stay with the current fund so the
library can choose to move them manually to the new fund if the
encumberance is set to rollover, or do something else with them if they
are set to stay.

(sitka) [RT18248] Fixed acq.fund_transfer error

Updated the pgTap test to work with the acq.fund_transfer changes.
Tracked down an error in acq.fund_transfer.  In the case where the
transferring and receiving currencies are the same, the
inverted_conversion_ratio was not being set to 1, which meant it had a
NULL value.  This was causing the amount_credited to the funding_source
to be seen as NULL, which was breaking the transfer in the case where
the fund currenices are  the same.

(sitka) [RT18248] Fixing acq.return_funds_to_source

Started to write a pgTap test for acq.return_funds_to_source.  It is
breaking currently because of an 'input of anonymous composite types is
not implemented' error.

When I reloaded the acq.return_funds_to_source file into dev4's
database, I got an error about a RAISE NOTICE that was using variables
that were no longer in use such as old_fund.  Once this was fixed, it
appears that roll over is now working (minus the weird rounding).  I
need to verify that this is the case.  As well the balances do not show
as negative for 2014 even though the encumberances are being
transferred.  Need to figure out why that is.

(sitka) [RT18248] Update to acq.fund_allocation_total

acq.fund_allocation_total was returning NULL values when there were no
funds allocated by a source to a fund.  This causes a bug when
calculating balances.  For instance, acq.fund_combined_balance would
subtract debits from the fund allocations, but, because
acq.fund_allocation_total would return a NULL value, this would result
in the balance being reported as 0.  This was apparent when looking at
the new funds after rolling over an Org Unit's funds.  In those cases,
encumbrances existed in the new funds, but the balances were being
reported as 0.

(sitka) [RT18248] Fixed pgTab for acq.return_funds_to_source

The pgTap test now works with acq.return_funds_to_source.  There was an
error  input of anonymous composite types is not implemented, which was
causing trouble.  It was a result of trying to use ARRAY_AGG with
different column names.  The inner SELECT was using one name (amount),
and the outer SELECT was using a different name (return_amount).
Maybe, the types are defined by looking up the name of the column within
pg's internal state, so using different names causes a problem?

(sitka) [RT18248] Fixed allocation total bug

The current code was modifying the acq.fund_allocation.fund_amount,
which is being used to determine if the amount being returned to a fund
is greater than the amount that was originally allocated to the fund.
However, when funds are transferred, the allocations are changed, and
this interferres with determining if the amount being returned is
greater that the amount allocated.  This fix stores the values allocated
for each fund in a two dimensional array.  The indexes are the fund id
and the funding credit id.  This allows the code to determine everytime
through the LOOP if each amount beging transferred is greater than the
amount allocated.

(sitka) [RT18248] Fixing year end rollover

This commit uses spent_balance instead combined_balance to determine if
the amount to be returned in return_funds_to_source is more than the
amount allocated to the source.

As well, the rollover now returns the spent balance when transferring
and returning funds.

Signed-off-by: Liam Whalen <liam.whalen@bc.libraries.coop>
(sitka) [RT18248] Added new fields to acq.fund_allocation

Added fund_amount NUMERIC and allocation to acq.fund_allocation NUMERIC.

Signed-off-by: Liam Whalen <liam.whalen@bc.libraries.coop>
(sitka) [RT18248] Added acq.fund_allocation trigger

This trigger UPDATEs acq.fund_allocation.fund_amount and
acq.fund_allocation.conversion_ratio when a new entry is INSERTed into
the the acq.fund_allocation table.

If the amount being inserted is postiive then is converts from the
funding source currency to the fund currency because the amount is being
allocated to the fund.  If the amount being inserted is negative then it
converts from the fund currency to the funding source currency because
the amount is being returned to the funding source.