From 089e0b684cdd81216432eb6fd6f18f22523d3b99 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 12 Feb 2026 18:00:24 +0000 Subject: [PATCH] Bug 30144: Add servicing_instruction field to aqorders table This patch adds a new TEXT field 'servicing_instruction' to the aqorders table to store EDIFACT servicing instructions (GIR LVT/LVC segments) as JSON. The field stores an array of instruction groups, where each group is an array of instruction objects with 'type' (LVC or LVT) and 'value' fields. Structure: [[{type:"LVC",value:"BB"},{type:"LVT",value:"text"}],[...]] This allows for: - Multiple servicing instruction groups per order line - Combinations of coded (LVC) and free-text (LVT) instructions - Instructions that work together in groups Also adds EDIFACT_SI authorized value category with EDItEUR List 3B servicing instruction codes for coded (LVC) instructions. Test plan: 1. Run database update 2. Verify aqorders.servicing_instruction field exists 3. Verify EDIFACT_SI authorized value category is created 4. Verify authorized values include codes like BB, BI, etc. --- ...bug_30144_edifact_servicing_instruction.pl | 76 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 1 + 2 files changed, 77 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_30144_edifact_servicing_instruction.pl diff --git a/installer/data/mysql/atomicupdate/bug_30144_edifact_servicing_instruction.pl b/installer/data/mysql/atomicupdate/bug_30144_edifact_servicing_instruction.pl new file mode 100755 index 00000000000..55261be973a --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_30144_edifact_servicing_instruction.pl @@ -0,0 +1,76 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); + +return { + bug_number => "30144", + description => "Add servicing_instruction support for EDIFACT orders", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Add servicing_instruction column to aqorders table + if ( !column_exists( 'aqorders', 'servicing_instruction' ) ) { + $dbh->do( + q{ + ALTER TABLE aqorders + ADD COLUMN servicing_instruction TEXT DEFAULT NULL + COMMENT 'Servicing instructions from vendor (EDIFACT LVT/LVC) stored as JSON array' + AFTER suppliers_report + } + ); + say_success( $out, "Added column 'aqorders.servicing_instruction'" ); + } + + # Add EDIFACT_SI category for servicing instruction codes + $dbh->do( + q{ + INSERT IGNORE INTO authorised_value_categories (category_name, is_system) + VALUES ('EDIFACT_SI', 0) + } + ); + say_success( $out, "Added authorized value category 'EDIFACT_SI'" ); + + # Add EDItEUR List 3B servicing instruction codes + $dbh->do( + q{ + INSERT IGNORE INTO authorised_values (category, authorised_value, lib, lib_opac) + VALUES + ('EDIFACT_SI', 'BB', 'Barcode labelling', 'Barcode labelling'), + ('EDIFACT_SI', 'BBN', 'Do not apply barcode labels', 'Do not apply barcode labels'), + ('EDIFACT_SI', 'BC', 'Classification', 'Classification'), + ('EDIFACT_SI', 'BCN', 'Do not classify', 'Do not classify'), + ('EDIFACT_SI', 'BI', 'Binding: binding, reinforcing, laminating', 'Binding: binding, reinforcing, laminating'), + ('EDIFACT_SI', 'BIN', 'Do not apply normal binding', 'Do not apply normal binding'), + ('EDIFACT_SI', 'BJ', 'Sleeving: jackets, sleeves, wallets', 'Sleeving: jackets, sleeves, wallets'), + ('EDIFACT_SI', 'BJN', 'Do not supply normal sleeving', 'Do not supply normal sleeving'), + ('EDIFACT_SI', 'BP', 'Audio/CD-ROM packaging: special pouches', 'Audio/CD-ROM packaging: special pouches'), + ('EDIFACT_SI', 'BPN', 'Do not supply normal audio/CD-ROM packaging', 'Do not supply normal audio/CD-ROM packaging'), + ('EDIFACT_SI', 'BS', 'Security fitting: triggers, Knogo labels', 'Security fitting: triggers, Knogo labels'), + ('EDIFACT_SI', 'BSN', 'Do not apply usual security fitting', 'Do not apply usual security fitting'), + ('EDIFACT_SI', 'CA', 'Cataloguing: catalogue record supply', 'Cataloguing: catalogue record supply'), + ('EDIFACT_SI', 'CAN', 'Do not supply catalogue record', 'Do not supply catalogue record'), + ('EDIFACT_SI', 'JK', 'Plastic wallet on paperback', 'Plastic wallet on paperback'), + ('EDIFACT_SI', 'JKN', 'Do not supply plastic wallet', 'Do not supply plastic wallet'), + ('EDIFACT_SI', 'KA', 'Kapco: thick laminate film on paperback', 'Kapco: thick laminate film on paperback'), + ('EDIFACT_SI', 'KAN', 'Do not apply Kapco', 'Do not apply Kapco'), + ('EDIFACT_SI', 'LA', 'Thin laminate film on paperback', 'Thin laminate film on paperback'), + ('EDIFACT_SI', 'LAN', 'Do not laminate', 'Do not laminate'), + ('EDIFACT_SI', 'RE', 'Reinforcement of hardback', 'Reinforcement of hardback'), + ('EDIFACT_SI', 'REN', 'Do not reinforce hardback', 'Do not reinforce hardback'), + ('EDIFACT_SI', 'RP', 'Reinforcement of paperback', 'Reinforcement of paperback'), + ('EDIFACT_SI', 'RPN', 'Do not reinforce paperback', 'Do not reinforce paperback'), + ('EDIFACT_SI', 'SF', 'Sewn flexi', 'Sewn flexi'), + ('EDIFACT_SI', 'SFN', 'Do not sew', 'Do not sew'), + ('EDIFACT_SI', 'SL', 'Plastic sleeve on hardback', 'Plastic sleeve on hardback'), + ('EDIFACT_SI', 'SLN', 'Do not sleeve plastic on hardback', 'Do not sleeve plastic on hardback'), + ('EDIFACT_SI', 'TR', 'Traditional case binding of paperback', 'Traditional case binding of paperback'), + ('EDIFACT_SI', 'TRN', 'Do not case bind paperback', 'Do not case bind paperback'), + ('EDIFACT_SI', 'NF', 'Non-standard servicing: see free text', 'Non-standard servicing: see free text'), + ('EDIFACT_SI', 'NS', 'No servicing', 'No servicing'), + ('EDIFACT_SI', 'NX', 'Non-standard servicing: see instructions sent outside EDI', 'Non-standard servicing: see instructions sent outside EDI'), + ('EDIFACT_SI', 'PF', 'Binding as supplied by publisher', 'Binding as supplied by publisher') + } + ); + say_success( $out, "Added EDItEUR List 3B servicing instruction codes to 'EDIFACT_SI' category" ); + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 1222ef6387c..8ec9cbf8acb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -780,6 +780,7 @@ CREATE TABLE `aqorders` ( `suppliers_reference_number` varchar(35) DEFAULT NULL COMMENT 'Suppliers unique edifact quote ref', `suppliers_reference_qualifier` varchar(3) DEFAULT NULL COMMENT 'Type of number above usually ''QLI''', `suppliers_report` mediumtext DEFAULT NULL COMMENT 'reports received from suppliers', + `servicing_instruction` text DEFAULT NULL COMMENT 'Servicing instructions from vendor (EDIFACT LVT/LVC) stored as JSON array', `estimated_delivery_date` date DEFAULT NULL COMMENT 'Estimated delivery date', `invoice_unitprice` decimal(28,6) DEFAULT NULL COMMENT 'the unit price in foreign currency', `invoice_currency` varchar(10) DEFAULT NULL COMMENT 'the currency of the invoice_unitprice', -- 2.53.0