0005501: G/L Journal Batch Copy Lines does not work

Description

'Copy Lines' inside 'G/L Journal' does not work due to two errors:
1) ERROR: operator does not exist: character varying = integer
2) ERROR: column "datedoc" is of type timestamp without time zone but expression is of type character varying
3) ERROR: null value in column "c_period_id" violates not-null constraint

Steps To Reproduce

1) Login as Openbravo. Role different than System administrator.
2) Go to Financial management || Accounting || Transaction || G/L Journal || Batch.
3) Click New and fill the the mandatory fields. Mark template flag.
4) Select one period.
5) Move to Header tab, click New.
6) Save and move to the Lines tab.
7) Click New and select:
8) Click on the Account combination icon and select one.
9) Fill the Foreign Currency Debit. Save.
10) Click New and select:
11) Click on the Account combination icon and select one.
12) Fill the Foreign Currency Credit. Save.
13) Move back to Header tab and click on button "Complete".
14) Move back to Batch and click new. Fill the mandatory fields and save.
11:54:48 [http-8385-Processor23] ERROR org.openbravo.erpCommon.ad_process.CopyFromGLJournalData - SQL error in query: SELECT G.GL_JOURNALBATCH_ID, G.DOCUMENTNO, G.DESCRIPTION, G.ISACTIVE,(SELECT L.NAME FROM AD_REF_LIST L WHERE L.AD_REFERENCE_ID='125' AND L.VALUE=G.POSTINGTYPE) AS POSTINGTYPE, G.DATEDOC, G.DATEACCT, G.CONTROLAMT, G.TOTALDR, G.TOTALCR FROM GL_JOURNALBATCH G WHERE G.ISACTIVE='Y' AND ISTEMPLATE='Y' AND 1=1 AND G.AD_CLIENT_ID IN(1000000) AND G.AD_ORG_ID IN('0','1000000','1000002','1000003','1000004','1000005','1000006','1000007','1000008','1000009') AND EXISTS (SELECT 1 FROM GL_JOURNAL WHERE GL_JOURNALBATCH_ID = G.GL_JOURNALBATCH_ID) ORDER BY 2Exception:org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
11:54:48 [http-8385-Processor23] ERROR org.openbravo.erpCommon.ad_process.CopyFromGLJournal - Error captured: javax.servlet.ServletException: @CODE=0@ERROR: operator does not exist: character varying = integer

Once it is resolved:
15) Press button Copy details button. Click search.
11:59:25 [http-8385-Processor24] ERROR org.openbravo.erpCommon.ad_process.CopyFromGLJournalData - SQL error in query: INSERT INTO GL_JOURNAL (GL_JOURNAL_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID,C_DOCTYPE_ID, DOCSTATUS,DOCACTION, ISAPPROVED, ISPRINTED, DESCRIPTION, POSTINGTYPE, GL_CATEGORY_ID, DATEDOC, DATEACCT,C_PERIOD_ID, C_CURRENCY_ID, CURRENCYRATETYPE,CURRENCYRATE, GL_JOURNALBATCH_ID, CONTROLAMT, DOCUMENTNO, PROCESSING, PROCESSED, POSTED) VALUES (?,?,?,now(),?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)Exception:org.postgresql.util.PSQLException: ERROR: column "datedoc" is of type timestamp without time zone but expression is of type character varying

Once fixed this, a new bug arises:
12:16:14 [http-8385-Processor23] ERROR org.openbravo.erpCommon.ad_process.CopyFromGLJournalData - SQL error in query: INSERT INTO GL_JOURNAL (GL_JOURNAL_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID,C_DOCTYPE_ID, DOCSTATUS,DOCACTION, ISAPPROVED, ISPRINTED, DESCRIPTION, POSTINGTYPE, GL_CATEGORY_ID, DATEDOC, DATEACCT,C_PERIOD_ID, C_CURRENCY_ID, CURRENCYRATETYPE,CURRENCYRATE, GL_JOURNALBATCH_ID, CONTROLAMT, DOCUMENTNO, PROCESSING, PROCESSED, POSTED) VALUES (?,?,?,now(),?, now(),?,?,?,?,?,?,?, ?,?,?,TO_DATE(?),TO_DATE(?),?,?, ?,TO_NUMBER(?),?,TO_NUMBER(?),?,?,?,?)Exception:org.postgresql.util.PSQLException: ERROR: null value in column "c_period_id" violates not-null constraint

Proposed Solution

Attached patch for errors 1 and 2 in ad_process/CopyFromGLJournal_data.xsql

Review the patch and correct error 3.

Tags

No tags attached.

Attached Files

patch-gljournal.diff [^] (1,774 bytes) 2008-10-11 13:30 [Show Content] [Hide Content]

### Eclipse Workspace Patch 1.0
#P openbravo
Index: src/org/openbravo/erpCommon/ad_process/CopyFromGLJournal_data.xsql
===================================================================
--- src/org/openbravo/erpCommon/ad_process/CopyFromGLJournal_data.xsql	(revision 8303)
+++ src/org/openbravo/erpCommon/ad_process/CopyFromGLJournal_data.xsql	(working copy)
@@ -81,7 +81,7 @@
     <Field name="position" value="count"/>
     <Parameter name="description" optional="true" after="AND 1=1"><![CDATA[ AND UPPER(COALESCE(TO_CHAR(G.DESCRIPTION),' ')) LIKE UPPER(?) ]]></Parameter>
     <Parameter name="documentNo" optional="true" after="AND 1=1"><![CDATA[ AND UPPER(G.DOCUMENTNO) LIKE UPPER(?) ]]></Parameter>
-    <Parameter name="adUserClient" type="replace" optional="true" after="G.AD_CLIENT_ID IN(" text="'1'"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="G.AD_CLIENT_ID IN('" text="1"/>
     <Parameter name="adOrgClient" type="replace" optional="true" after="G.AD_ORG_ID IN(" text="'1'"/>
   </SqlMethod>
   <SqlMethod name="insertGLJournal" type="preparedStatement" connection="true" return="rowCount">
@@ -92,7 +92,10 @@
         UPDATED, UPDATEDBY, C_ACCTSCHEMA_ID,C_DOCTYPE_ID, DOCSTATUS,DOCACTION, ISAPPROVED, ISPRINTED, 
         DESCRIPTION, POSTINGTYPE, GL_CATEGORY_ID, DATEDOC, DATEACCT,C_PERIOD_ID, C_CURRENCY_ID, 
         CURRENCYRATETYPE,CURRENCYRATE, GL_JOURNALBATCH_ID, CONTROLAMT, DOCUMENTNO, PROCESSING, PROCESSED, POSTED) 
-        VALUES (?,?,?,now(),?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+        VALUES (?,?,?,now(),?,
+        	now(),?,?,?,?,?,?,?,
+        	?,?,?,TO_DATE(?),TO_DATE(?),?,?,
+        	?,TO_NUMBER(?),?,TO_NUMBER(?),?,?,?,?)
       ]]>
     </Sql>
     <Parameter name="gl_journal_id"/>

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0012115) svnbot (viewer) 2009-01-12 15:30	Repository: openbravo Revision: 11797 Author: vmromanos Date: 2009-01-12 15:30:14 +0100 (Mon, 12 Jan 2009) Fixed bug 5501: G/L Journal Batch Copy Lines does not work --- U trunk/src/org/openbravo/erpCommon/ad_process/CopyFromGLJournal.java U trunk/src/org/openbravo/erpCommon/ad_process/CopyFromGLJournal_data.xsql --- https://dev.openbravo.com/websvn/openbravo/?rev=11797&sc=1 [^]

Issue History
Date Modified	Username	Field	Change
2008-10-11 13:30	rafaroda	New Issue
2008-10-11 13:30	rafaroda	Assigned To	=> dalsasua
2008-10-11 13:30	rafaroda	sf_bug_id	0 => 2159196
2008-10-11 13:30	rafaroda	File Added: patch-gljournal.diff
2008-10-11 13:30	rafaroda	OBNetwork customer	=> No
2008-10-11 13:30	rafaroda	Regression testing	=> No
2008-10-13 09:48	psarobe	Status	new => scheduled
2008-10-13 09:48	psarobe	fix_in_branch	=> trunk
2009-01-09 14:32	rafaroda	Assigned To	dalsasua => vmromanos
2009-01-12 15:30	svnbot	Checkin
2009-01-12 15:30	svnbot	Note Added: 0012115
2009-01-12 15:30	svnbot	Status	scheduled => resolved
2009-01-12 15:30	svnbot	Resolution	open => fixed
2009-01-12 15:30	svnbot	svn_revision	=> 11797
2009-04-21 12:19	psarobe	Status	resolved => closed
2024-03-20 13:07	ngarcia	Relationship added	related to 0055011
2024-03-20 13:07	ngarcia	Relationship deleted	related to 0055011