[Evergreen-catalogers] Deleting fund allocations

[Miller, Jeremy]

I had to fix a ton of stuff with ours a while back.
Here's some snippets from my pile of queries.  Anyone comfortable with SQL should have no trouble at all:


--- show all details for funding sources (MONEY HOLDERS)
---------------------------------------------------------
SELECT s.id AS sourceid, s.code, c.amount AS credits, a.amount AS allocated, b.amount AS balance
FROM acq.funding_source s
JOIN acq.funding_source_balance b ON (s.id = b.funding_source)
JOIN acq.funding_source_allocation_total a ON (s.id = a.funding_source)
JOIN acq.funding_source_credit_total c ON (s.id = c.funding_source);


-- show credits to a funding source (MONEY IN for source_id = 12)
------------------------------------------------------------------
SELECT c.id AS credit, s.id AS sourceid,s.code AS source,c.amount, c.effective_date
FROM acq.funding_source_credit c
JOIN acq.funding_source s ON (s.id = c.funding_source)
WHERE s.id = '12'
ORDER BY c.effective_date;


-- show fund allocations (MONEY OUT from source_id = 12)
---------------------------------------------------------
SELECT a.id AS allocation, s.id AS sourceid, s.code AS source, a.amount,a.fund AS fundid, f.code AS fund, f.year, a.create_time
FROM acq.fund_allocation a
JOIN acq.fund f on (a.fund = f.id)
JOIN acq.funding_source s on (a.funding_source = s.id)
WHERE s.id = '12'
ORDER BY a.create_time;



And the deadly bits for fixing/deleting things found above (be careful, of course):

-- delete credits
DELETE FROM acq.funding_source_credit WHERE id IN ('14','15','16','17');

-- change credit amount
UPDATE acq.funding_source_credit SET amount = '100' WHERE id = '13';

-- change allocation amount
UPDATE acq.fund_allocation SET amount = '100' WHERE id IN ('421','422','423');

-- kill all allocations done in a time period
DELETE FROM acq.fund_allocation WHERE create_time BETWEEN '2015-07-01' AND '2015-07-02';

-- delete an allocation
DELETE FROM acq.fund_allocation WHERE id IN (433,437);



---------------------------------
Jeremiah Miller  | 541-917-7597
Sysadmin | Albany Public Library
---------------------------------






From: Evergreen-catalogers [mailto:evergreen-catalogers-bounces at list.evergreen-ils.org] On Behalf Of Rogan Hamby
Sent: Thursday, May 26, 2016 11:16 AM
To: Evergreen Community Catalogers
Subject: Re: [Evergreen-catalogers] Deleting fund allocations

If you have support that is comfortable at the database level the skills to do it are pretty basic.

On Thu, May 26, 2016 at 1:02 PM, Jennifer Pringle <jennifer.pringle at bc.libraries.coop> wrote:
Hello Millissa,

There's no way to delete an allocation in the staff client but you can delete the allocation from the database.   I can ask our tech for the sql he uses if that would be helpful. (We only delete allocation in the database when no further actions have been taken involving the funds.)

Is this an allocation to a funding source or a fund?  If this is an allocation to a fund you can transfer part or all of the allocation into the correct fund through the staff client.

Jennifer

Quoting Millissa Macomber <millissam at burlingtonwa.gov>:
I mistakenly allocated funds. Is there a way to delete an allocation?

Millissa Macomber
Technical Services Coordinator
Burlington Public Library
millissam at burlingtonwa.gov<mailto:millissam at burlingtonwa.gov>
360-755-0760


--
Jennifer Pringle
Co-op Support
BC Libraries Cooperative
Tel: 1-888-848-9250
Email:jennifer.pringle at bc.libraries.coop
Website: http://bc.libraries.coop

_______________________________________________
Evergreen-catalogers mailing list
Evergreen-catalogers at list.evergreen-ils.org
http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-catalogers




--
--------------------------------------------------------------
Rogan R. Hamby, Data and Project Analyst
Equinox - Open Your Library
rogan at esilibrary.com
1-877-OPEN-ILS | www.esilibrary.com


DISCLAIMER: This e-mail may be a public record of the City of Albany and may be subject to the State of Oregon Retention Schedule and may be subject to public disclosure under the Oregon Public Records Law. This e-mail, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, please send a reply e-mail to let the sender know of the error and destroy all copies of the original message.