Openbravo Issue Tracking System - Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0047868ModulesRemittancepublic2021-10-18 09:472021-10-21 09:54
Reporterfernando_merino 
Assigned Tofernando_merino 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Statusapproved
Regression date2021-10-15
Regression introduced by commit
Regression levelAutomated tests
Review Assigned To
Support ticket
OBNetwork customerNo
Regression introduced in release
Summary0047868: Character mismatch in sql query in PopulateBankInstructions_data.xsql
DescriptionColumn FIN_PAYMENT.description type was changed from VARCHAR to NVARCHAR in issue 47765(see below related issues). This is producing a character mismatch error in this sql query '...CASE WHEN COALESCE(substr(p.description, length(p.description)),'') = chr(10)...'.
Steps To Reproduce1. Execute the query from the method 'selectRemittanceLines' in org.openbravo.module.remittance.modulescript/PopulateBankInstructions_data.xsql. The execution will fail.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
caused by defect 0047765 closed fernando_merino Openbravo ERP Problem posting multiple invoices in the same collection 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-10-18 09:47fernando_merinoNew Issue
2021-10-18 09:47fernando_merinoAssigned To => fernando_merino
2021-10-18 09:47fernando_merinoOBNetwork customer => No
2021-10-18 10:09fernando_merinoRegression date => 2021-10-15
2021-10-18 10:09fernando_merinoRegression level => Automated tests
2021-10-18 10:09fernando_merinoSummaryPENDING => Character mismatch in sql query in PopulateBankInstructions_data.xsql
2021-10-18 10:09fernando_merinoDescription Updatedbug_revision_view_page.php?rev_id=23192#r23192
2021-10-18 10:09fernando_merinoSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=23194#r23194
2021-10-18 13:31hgbotMerge Request Status => open
2021-10-18 13:31hgbotNote Added: 0132409
2021-10-18 14:45aferrazRelationship addedcaused by 0047765
2021-10-18 14:47hgbotMerge Request Statusopen => approved
2021-10-18 14:48hgbotResolutionopen => fixed
2021-10-18 14:48hgbotStatusnew => closed
2021-10-18 14:48hgbotNote Added: 0132415
2021-10-18 14:48hgbotNote Added: 0132416
2021-10-18 14:49aferrazStatusclosed => new
2021-10-18 14:49aferrazResolutionfixed => open
2021-10-18 14:53hgbotNote Added: 0132417
2021-10-21 09:51fernando_merinoNote Added: 0132517
2021-10-21 09:54aferrazStatusnew => scheduled
2021-10-21 09:54aferrazStatusscheduled => resolved
2021-10-21 09:54aferrazResolutionopen => fixed
2021-10-21 09:54aferrazStatusresolved => closed

Notes
(0132409)
hgbot   
2021-10-18 13:31   
Merge Request created: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance/-/merge_requests/5 [^]
(0132415)
hgbot   
2021-10-18 14:48   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance [^]
Changeset: 8c0e1d2a397c3d4b4aefaf9dbaa509e1eb2c5aa4
Author: fernando_merino <fernando.merino@openbravo.com>
Date: 2021-10-18T13:27:49+02:00
URL: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance/-/commit/8c0e1d2a397c3d4b4aefaf9dbaa509e1eb2c5aa4 [^]

Fixes ISSUE-47868: Added TO_CHAR() to avoid character mismatch error

Column FIN_PAYMENT.description type was changed from VARCHAR to NVARCHAR. To avoid character mismatch error in the sql queryr from the selectRemittanceLines method
a TO_CHAR() function has been added.

---
M src-util/modulescript/src/org/openbravo/module/remittance/modulescript/PopulateBankInstructions_data.xsql
---
(0132416)
hgbot   
2021-10-18 14:48   
Merge request merged: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance/-/merge_requests/5 [^]
(0132417)
hgbot   
2021-10-18 14:53   
Repository: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance [^]
Changeset: d3d1e3bbd80b025f3de3ce8f8dfc9f6ea832a794
Author: Álvaro Ferraz <alvaro.ferraz@openbravo.com>
Date: 2021-10-18T14:50:32+02:00
URL: https://gitlab.com/openbravo/product/mods/org.openbravo.module.remittance/-/commit/d3d1e3bbd80b025f3de3ce8f8dfc9f6ea832a794 [^]

Related to ISSUE-47868: Compile PopulateBankInstructions_data.xsql

---
M build/classes/org/openbravo/module/remittance/modulescript/PopulateBankInstructionsData.class
---
(0132517)
fernando_merino   
2021-10-21 09:51   
In order to prevent future issues, the following commands have been used to look for similar problems:

--> grep -ril 'COALESCE(.*.description.*)' | xargs grep -ril 'fin_payment[^_A-Z]'

    (To look for files which include the fin_payment table and make use of the
     description column inside a COALESCE function)

--> grep -ril --include=\*.xml 'fin_payment[^_A-Z]' | xargs grep -ril 'function \|trigger ' | xargs grep -ril 'varchar' | xargs grep -ril 'description'

    (To look for .xml files which include fin_payment table and could either
     save fin_payment.description value into a varchar variable or update
     fin_payment.description value using a varchar variable)