BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0889', :eg_version); -- Update ACQ cancel reason names, but only those that -- have not already been locally modified from stock values. UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Canceled: Invalid ISBN', 'acqcr', 'label') WHERE id = 1 AND label = 'invalid_isbn'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Canceled: Postpone', 'acqcr', 'label') WHERE id = 2 AND label = 'postpone'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Delivered but Lost', 'acqcr', 'label') WHERE id = 3 AND label = 'delivered_but_lost'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Canceled: Deleted', 'acqcr', 'label') WHERE id = 1002 AND label = 'Deleted'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Changed', 'acqcr', 'label') WHERE id = 1003 AND label = 'Changed'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: No Action', 'acqcr', 'label') WHERE id = 1004 AND label = 'No action'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Accepted without amendment', 'acqcr', 'label') WHERE id = 1005 AND label = 'Accepted without amendment'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Canceled: Not Accepted', 'acqcr', 'label') WHERE id = 1007 AND label = 'Not accepted'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Canceled: Not Found', 'acqcr', 'label') WHERE id = 1010 AND label = 'Not found'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Accepted with amendment', 'acqcr', 'label') WHERE id = 1024 AND label = 'Accepted with amendment, no confirmation required'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Split Quantity', 'acqcr', 'label') WHERE id = 1211 AND label = 'Split quantity'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Ordered Quantity', 'acqcr', 'label') WHERE id = 1221 AND label = 'Ordered quantity'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Pieces Delivered', 'acqcr', 'label') WHERE id = 1246 AND label = 'Pieces delivered'; UPDATE acq.cancel_reason SET label = oils_i18n_gettext(1,'Delayed: Backorder', 'acqcr', 'label') WHERE id = 1283 AND label = 'Backorder quantity'; -- action/trigger additions -- All following changes are only applied where the source data matches -- the stock data. IOW, if a template has been locally modified, -- it's left unchanged. DO $$ BEGIN -- avoid collisions by testing for the presence of the -- desired environment addition. PERFORM 1 FROM action_trigger.environment WHERE event_def = 4 AND path = 'lineitems.cancel_reason'; IF NOT FOUND THEN INSERT INTO action_trigger.environment (event_def, path) VALUES (4, 'lineitems.cancel_reason'); END IF; PERFORM 1 FROM action_trigger.environment WHERE event_def = 14 AND path = 'cancel_reason'; IF NOT FOUND THEN INSERT INTO action_trigger.environment (event_def, path) VALUES ( 14, 'cancel_reason' ); END IF; PERFORM 1 FROM action_trigger.environment WHERE event_def = 14 AND path = 'lineitem_details.cancel_reason'; IF NOT FOUND THEN INSERT INTO action_trigger.environment (event_def, path) VALUES ( 14, 'lineitem_details.cancel_reason' ); END IF; END $$; UPDATE action_trigger.event_definition SET template = $$ [%- USE date -%] [%- # find a lineitem attribute by name and optional type BLOCK get_li_attr; FOR attr IN li.attributes; IF attr.attr_name == attr_name; IF !attr_type OR attr_type == attr.attr_type; attr.attr_value; LAST; END; END; END; END -%]

Purchase Order: [% target.name %] ([% target.id %])


date [% date.format(date.now, '%Y%m%d') %]
Vendor
[% target.provider.name %]
[% target.provider.addresses.0.street1 %]
[% target.provider.addresses.0.street2 %]
[% target.provider.addresses.0.city %]
[% target.provider.addresses.0.state %]
[% target.provider.addresses.0.country %]
[% target.provider.addresses.0.post_code %]
Ship to / Bill to
[% target.ordering_agency.name %]
[% target.ordering_agency.billing_address.street1 %]
[% target.ordering_agency.billing_address.street2 %]
[% target.ordering_agency.billing_address.city %]
[% target.ordering_agency.billing_address.state %]
[% target.ordering_agency.billing_address.country %]
[% target.ordering_agency.billing_address.post_code %]


Notes to the Vendor


[% subtotal = 0 %] [% FOR li IN target.lineitems %] [% count = li.lineitem_details.size %] [% price = li.estimated_unit_price %] [% litotal = (price * count) %] [% subtotal = subtotal + litotal %] [% ident_attr = helpers.get_li_order_ident(li.attributes); SET ident_value = ident_attr.attr_value IF ident_attr; %] [% END %]
PO# ISBN or Item # Title Quantity Unit Price Line Total Delayed / Canceled Notes
[% target.id %] [% ident_value %] [% PROCESS get_li_attr attr_name = 'title' %] [% count %] [% price %] [% litotal %] [% li.cancel_reason.label %]
    [% FOR note IN li.lineitem_notes %] [% IF note.vendor_public == 't' %]
  • [% note.value %]
  • [% END %] [% END %]
Subtotal [% subtotal %]

Total Line Item Count: [% target.lineitems.size %] $$ WHERE id = 4 AND template = $$ [%- USE date -%] [%- # find a lineitem attribute by name and optional type BLOCK get_li_attr; FOR attr IN li.attributes; IF attr.attr_name == attr_name; IF !attr_type OR attr_type == attr.attr_type; attr.attr_value; LAST; END; END; END; END -%]

Purchase Order: [% target.name %] ([% target.id %])


date [% date.format(date.now, '%Y%m%d') %]
Vendor
[% target.provider.name %]
[% target.provider.addresses.0.street1 %]
[% target.provider.addresses.0.street2 %]
[% target.provider.addresses.0.city %]
[% target.provider.addresses.0.state %]
[% target.provider.addresses.0.country %]
[% target.provider.addresses.0.post_code %]
Ship to / Bill to
[% target.ordering_agency.name %]
[% target.ordering_agency.billing_address.street1 %]
[% target.ordering_agency.billing_address.street2 %]
[% target.ordering_agency.billing_address.city %]
[% target.ordering_agency.billing_address.state %]
[% target.ordering_agency.billing_address.country %]
[% target.ordering_agency.billing_address.post_code %]


Notes to the Vendor


[% subtotal = 0 %] [% FOR li IN target.lineitems %] [% count = li.lineitem_details.size %] [% price = li.estimated_unit_price %] [% litotal = (price * count) %] [% subtotal = subtotal + litotal %] [% ident_attr = helpers.get_li_order_ident(li.attributes); SET ident_value = ident_attr.attr_value IF ident_attr; %] [% END %]
PO# ISBN or Item # Title Quantity Unit Price Line Total Notes
[% target.id %] [% ident_value %] [% PROCESS get_li_attr attr_name = 'title' %] [% count %] [% price %] [% litotal %]
    [% FOR note IN li.lineitem_notes %] [% IF note.vendor_public == 't' %]
  • [% note.value %]
  • [% END %] [% END %]
Subtotal [% subtotal %]

Total Line Item Count: [% target.lineitems.size %] $$; -- lineitem worksheet UPDATE action_trigger.event_definition SET template = $$ [%- USE date -%] [%- SET li = target; -%]
Title: [% helpers.get_li_attr("title", "", li.attributes) %]
Author: [% helpers.get_li_attr("author", "", li.attributes) %]
Item Count: [% li.lineitem_details.size %]
Lineitem ID: [% li.id %]
Open Holds: [% helpers.bre_open_hold_count(li.eg_bib_id) %]
[% IF li.cancel_reason.label %]
[% li.cancel_reason.label %]
[% END %] [% IF li.distribution_formulas.size > 0 %] [% SET forms = [] %] [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
Distribution Formulas: [% forms.join(',') %]
[% END %] [% IF li.lineitem_notes.size > 0 %] Lineitem Notes: [% END %]

[% FOREACH detail IN li.lineitem_details %] [% detail.owning_lib = detail.owning_lib.shortname %] [% END %] [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %] [% IF detail.eg_copy_id; SET copy = detail.eg_copy_id; SET cn_label = copy.call_number.label; ELSE; SET copy = detail; SET cn_label = detail.cn_label; END %] [% END %]
Branch Barcode Call Number Fund Shelving Location Recd. Notes Delayed / Canceled
[% detail.owning_lib %] [% IF copy.barcode %][% detail.barcode %][% END %] [% IF cn_label %][% cn_label %][% END %] [% IF detail.fund %][% detail.fund.code %] ([% detail.fund.year %])[% END %] [% copy.location.name %] [% IF detail.recv_time %][% detail.recv_time %][% END %] [% detail.note %] [% detail.cancel_reason.label %]
$$ WHERE id = 14 AND template = $$ [%- USE date -%] [%- SET li = target; -%]
Title: [% helpers.get_li_attr("title", "", li.attributes) %]
Author: [% helpers.get_li_attr("author", "", li.attributes) %]
Item Count: [% li.lineitem_details.size %]
Lineitem ID: [% li.id %]
Open Holds: [% helpers.bre_open_hold_count(li.eg_bib_id) %]
[% IF li.distribution_formulas.size > 0 %] [% SET forms = [] %] [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
Distribution Formulas: [% forms.join(',') %]
[% END %] [% IF li.lineitem_notes.size > 0 %] Lineitem Notes: [% END %]

[% FOREACH detail IN li.lineitem_details %] [% detail.owning_lib = detail.owning_lib.shortname %] [% END %] [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %] [% IF detail.eg_copy_id; SET copy = detail.eg_copy_id; SET cn_label = copy.call_number.label; ELSE; SET copy = detail; SET cn_label = detail.cn_label; END %] [% END %]
Branch Barcode Call Number Fund Shelving Location Recd. Notes
[% detail.owning_lib %] [% IF copy.barcode %][% detail.barcode %][% END %] [% IF cn_label %][% cn_label %][% END %] [% IF detail.fund %][% detail.fund.code %] ([% detail.fund.year %])[% END %] [% copy.location.name %] [% IF detail.recv_time %][% detail.recv_time %][% END %] [% detail.note %]
$$; COMMIT;