From b2b8e6f235d6a9e82adc67c7f81f2268083c37c4 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 5 Mar 2026 15:04:53 +0000 Subject: [PATCH] Bug 42001: Database updates Adds system preferences and notice templates for duplicate EDI purchase order email notifications. New preferences: - EdiDuplicateOrderEmailNotice (YesNo) - EdiDuplicateOrderEmailAddresses (Textarea) New notice templates: - EDI_DUP_ORD_LIBRARY - library staff notification - EDI_DUP_ORD_VENDOR - vendor contact notification Sponsored-by: Westminster City Council Sponsored-by: Royal Borough of Kensington and Chelsea --- .../data/mysql/atomicupdate/bug_42001.pl | 112 ++++++++++++++++++ .../mysql/en/mandatory/sample_notices.yml | 56 +++++++++ installer/data/mysql/mandatory/sysprefs.sql | 2 + 3 files changed, 170 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_42001.pl diff --git a/installer/data/mysql/atomicupdate/bug_42001.pl b/installer/data/mysql/atomicupdate/bug_42001.pl new file mode 100755 index 00000000000..fd05d39b4d6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_42001.pl @@ -0,0 +1,112 @@ +use Modern::Perl; + +return { + bug_number => "42001", + description => "Add email notifications for duplicate EDI purchase orders", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Email notification toggle + $dbh->do( + q{ + INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) + VALUES ( + 'EdiDuplicateOrderEmailNotice', + '0', + NULL, + 'Send email notification when a duplicate EDIFACT purchase order number is detected for the same supplier.', + 'YesNo' + ) + } + ); + say $out "Added system preference 'EdiDuplicateOrderEmailNotice'"; + + # Email recipient list + $dbh->do( + q{ + INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) + VALUES ( + 'EdiDuplicateOrderEmailAddresses', + '', + NULL, + 'Comma-separated list of email addresses to notify when duplicate EDIFACT purchase order numbers are detected (e.g., "purchasing@library.org,edi_support@library.org"). Requires EdiDuplicateOrderEmailNotice to be enabled.', + 'Textarea' + ) + } + ); + say $out "Added system preference 'EdiDuplicateOrderEmailAddresses'"; + + # Add notice template - library staff notification + $dbh->do( + q{ + INSERT IGNORE INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type, lang) + VALUES ( + 'acquisition', + 'EDI_DUP_ORD_LIBRARY', + '', + 'EDIFACT duplicate order detected - library notification', + 0, + 'EDIFACT Duplicate Purchase Order Blocked - [% po_number | html %]', + 'Duplicate EDIFACT Purchase Order Detected and Blocked + +Purchase Order Number: [% po_number | html %] +Vendor: [% aqbooksellers.name | html %] (ID: [% vendor_id | html %]) +Basket No: [% basketno | html %] +Existing Basket No: [% existing_basketno | html %] + +Status: The EDI order was NOT sent. The basket remains open. + +This purchase order number already exists for this vendor (basket [% existing_basketno | html %]). +A basket name/PO number must be unique per vendor when the vendor EDI account has "Use basket name as PO number" enabled. + +Action Required: +Rename the basket to use a unique purchase order number, then resend the EDI order. + +View Basket: [% OPACBaseURL | uri %]/cgi-bin/koha/acqui/basket.pl?basketno=[% basketno | uri %] +View Existing Basket: [% OPACBaseURL | uri %]/cgi-bin/koha/acqui/basket.pl?basketno=[% existing_basketno | uri %] + +This is an automated notification from your Koha system.', + 'email', + 'default' + ) + } + ); + say $out "Added letter template 'EDI_DUP_ORD_LIBRARY'"; + + # Add notice template - vendor notification + $dbh->do( + q{ + INSERT IGNORE INTO letter (module, code, branchcode, name, is_html, title, content, message_transport_type, lang) + VALUES ( + 'acquisition', + 'EDI_DUP_ORD_VENDOR', + '', + 'EDIFACT duplicate order detected - vendor notification', + 0, + 'Duplicate Purchase Order Number Received - [% po_number | html %]', + 'Dear Supplier, + +We have attempted to send an EDIFACT order to your system but the purchase order number is a duplicate. + +Purchase Order Number: [% po_number | html %] +Your Reference (SAN): [% vendor_san | html %] + +Issue: +This purchase order number has already been used in a previous order with your organisation. We were unable to send the order. + +No action is required from you at this stage. Our acquisitions team has been notified and will resubmit the order with a corrected purchase order number. + +If you have any questions, please contact our acquisitions department. + +Library: [% aqbooksellers.name | html %] + +This is an automated notification. Please do not reply to this email.', + 'email', + 'default' + ) + } + ); + say $out "Added letter template 'EDI_DUP_ORD_VENDOR'"; + }, +}; diff --git a/installer/data/mysql/en/mandatory/sample_notices.yml b/installer/data/mysql/en/mandatory/sample_notices.yml index 24f76542f94..dad0b3bcea8 100644 --- a/installer/data/mysql/en/mandatory/sample_notices.yml +++ b/installer/data/mysql/en/mandatory/sample_notices.yml @@ -100,6 +100,62 @@ tables: - "" - "This is an automated notification. Please do not reply to this email." + - module: acquisition + code: EDI_DUP_ORD_LIBRARY + branchcode: "" + name: "EDIFACT duplicate order detected - library notification" + is_html: 0 + title: "EDIFACT Duplicate Purchase Order Blocked - [% po_number %]" + message_transport_type: email + lang: default + content: + - "Duplicate EDIFACT Purchase Order Detected and Blocked" + - "" + - "Purchase Order Number: [% po_number %]" + - "Vendor: [% aqbooksellers.name %] (ID: [% vendor_id %])" + - "Basket No: [% basketno %]" + - "Existing Basket No: [% existing_basketno %]" + - "" + - "Status: The EDI order was NOT sent. The basket remains open." + - "" + - "This purchase order number already exists for this vendor (basket [% existing_basketno %])." + - "A basket name/PO number must be unique per vendor when the vendor EDI account has \"Use basket name as PO number\" enabled." + - "" + - "Action Required:" + - "Rename the basket to use a unique purchase order number, then resend the EDI order." + - "" + - "View Basket: [% OPACBaseURL %]/cgi-bin/koha/acqui/basket.pl?basketno=[% basketno %]" + - "View Existing Basket: [% OPACBaseURL %]/cgi-bin/koha/acqui/basket.pl?basketno=[% existing_basketno %]" + - "" + - "This is an automated notification from your Koha system." + + - module: acquisition + code: EDI_DUP_ORD_VENDOR + branchcode: "" + name: "EDIFACT duplicate order detected - vendor notification" + is_html: 0 + title: "Duplicate Purchase Order Number Received - [% po_number %]" + message_transport_type: email + lang: default + content: + - "Dear Supplier," + - "" + - "We have attempted to send an EDIFACT order to your system but the purchase order number is a duplicate." + - "" + - "Purchase Order Number: [% po_number %]" + - "Your Reference (SAN): [% vendor_san %]" + - "" + - "Issue:" + - "This purchase order number has already been used in a previous order with your organisation. We were unable to send the order." + - "" + - "No action is required from you at this stage. Our acquisitions team has been notified and will resubmit the order with a corrected purchase order number." + - "" + - "If you have any questions, please contact our acquisitions department." + - "" + - "Library: [% aqbooksellers.name %]" + - "" + - "This is an automated notification. Please do not reply to this email." + - module: bookings code: BOOKING_CANCELLATION branchcode: "" diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql index 2017465986d..d336101846d 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -243,6 +243,8 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` ('EdiBlockDuplicateInvoice', '0', NULL, 'Block processing of EDIFACT invoices when a duplicate invoice number is detected for the same supplier. When enabled, duplicate invoices will be rejected and logged as errors.', 'YesNo'), ('EdiBlockDuplicateInvoiceEmailAddresses', '', NULL, 'Comma-separated list of email addresses to notify when duplicate EDIFACT invoices are detected (e.g., "purchasing@library.org,edi_support@library.org"). Requires EdiBlockDuplicateInvoiceEmailNotice to be enabled.', 'Textarea'), ('EdiBlockDuplicateInvoiceEmailNotice', '0', NULL, 'Send email notification when duplicate EDIFACT invoices are detected. Requires EdiBlockDuplicateInvoice to be enabled.', 'YesNo'), +('EdiDuplicateOrderEmailAddresses', '', NULL, 'Comma-separated list of email addresses to notify when duplicate EDIFACT purchase order numbers are detected (e.g., "purchasing@library.org,edi_support@library.org"). Requires EdiDuplicateOrderEmailNotice to be enabled.', 'Textarea'), +('EdiDuplicateOrderEmailNotice', '0', NULL, 'Send email notification when a duplicate EDIFACT purchase order number is detected for the same supplier.', 'YesNo'), ('ElasticsearchBoostFieldMatch', '0', NULL, 'Add a "match" query to es when searching, will follow indexes chosen in advanced search, or use title-cover for generic keyword or title index search', 'YesNo'), ('ElasticsearchCrossFields', '1', NULL, 'Enable "cross_fields" option for searches using Elastic search.', 'YesNo'), ('ElasticsearchIndexStatus_authorities', '0', 'Authorities index status', NULL, NULL), -- 2.53.0