Evergreen ILL Billed Price Update

Ill Billed Item Price Update.sql

-- Problem: For ILL items, we don't know the price until after the item is billed.  We need to update the billed amount after the price 
-- changes.

-- set the replacement billing price = copy price when they don't match for ILL items.

begin;

update money.billing mb
set amount=acp.price
from 
asset.copy acp
join action.circulation ac on acp.id=ac.target_copy and ac.checkin_time is null
join money.billable_xact_summary mbxs on mbxs.id=ac.id
join actor.usr au on au.id=ac.usr
join actor.card acd on acd.id=au.card
join actor.org_unit aou on au.home_ou=aou.id
where
mb.xact=ac.id
and mb.billing_type='Lost Materials'
and acp.circ_modifier='ILL'
and acp.status=3
and acp.price != mb.amount
and mb.voided=false
returning aou.shortname "Patron Home Lib", acd.barcode "Patron Barcode", acp.price "Item Price", mbxs.balance_owed,mb.amount "Billed Amount", mbxs.id "Billing ID"
;

rollback;


select aou.shortname "Patron Home Lib", acd.barcode "Patron Barcode", acp.price "Item Price", mbxs.balance_owed,mb.amount "Billed Amount", mbxs.id "Billing ID"
from asset.copy acp
join action.circulation ac on acp.id=ac.target_copy and ac.checkin_time is null
join money.billable_xact_summary mbxs on mbxs.id=ac.id
join money.billing mb on mb.xact=ac.id and mb.billing_type='Lost Materials'
join actor.usr au on au.id=ac.usr
join actor.card acd on acd.id=au.card
join actor.org_unit aou on au.home_ou=aou.id
where
acp.circ_modifier='ILL'
and acp.status=3
and acp.price != mb.amount
and mb.voided=false
order by shortname, au.id