0027974: Year Out of Range error in Oracle calculating costs

Description

On an Legal Entity where Fix Backdated Transactions is set to false and using Oracle database.

When the cost is being calculated the following exception is thrown:

57112713 [DefaultQuartzScheduler_Worker-3] ERROR org.hibernate.util.JDBCExceptionReporter - Year out of range.
57112744 [DefaultQuartzScheduler_Worker-3] ERROR org.openbravo.costing.CostingBackground - could not execute query
org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    at org.openbravo.costing.CostAdjustmentUtils.getStockOnTransactionDate(CostAdjustmentUtils.java:417)
    at org.openbravo.costing.CostingServer.checkCostAdjustments(CostingServer.java:260)
    at org.openbravo.costing.CostingServer.process(CostingServer.java:126)
    at org.openbravo.costing.CostingBackground.doExecute(CostingBackground.java:105)
    at org.openbravo.service.db.DalBaseProcess.execute(DalBaseProcess.java:80)
    at org.openbravo.scheduling.DefaultJob.execute(DefaultJob.java:58)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525)

Steps To Reproduce

In "F&B International Group" client:

Ensure that costing rule for "F&B US, Inc." has the Fix Backdated Transactions flag unchecked. If it is checked create and process a new costing rule with the same parameters as the current one and do not check the flag.

Create a new Product named "Costing V911" for "F&B US, Inc." organization.
Go to Price tab, select "Be Soft Drinker" price list and enter a unit/list price of 15 €. Go to Costing tab and enter a new line for "F&B US, Inc." organization, Average cost type, cost 25, starting date 01-01-2014 date and ending date 31-12-9999

Go to Purchase Invoice window and create a new invoice for "F&B US, Inc." organization, product "Costing V911", business partner "Be Soft Drinker" invoice date 11-10-2014, quantity 500 and price 15.
Complete the invoice and post it. Let's say this is invoice 10000037.
Realize that the value posted to the ledger is 7,500.00 (500 units * 15 €/unit)

Go to Goods Shipment window and create a new shipment dated on 15-10-2014, product "Costing V911", quantity 400, storage bin L01. Complete. Let's say this is shipment 500024.

Navigate to Process Request window and run "Costing Background process".

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

related to

defect

0041015

closed

vmromanos

Modules

Filtering by 31-12-9999 in Oracle causes "Year out of range"

Notes
(0071166) hgbot (developer) 2014-10-24 12:14	Repository: erp/devel/pi Changeset: 962bfa536e18a7cc17a1b9d940a46947b5ca380b Author: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> Date: Fri Oct 24 10:46:29 2014 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/962bfa536e18a7cc17a1b9d940a46947b5ca380b [^] Fixed issue 27974.Do not filter queries by 31-12-9999 Some queries have been modified so when the costing rule is not fixing backdated transactions it is not needed to filter by 31-12-9999. This filter was causing a SQL Exception that caught by the Costing Background but no rollback was done. This is also fixed. --- M src/org/openbravo/costing/AverageCostAdjustment.java M src/org/openbravo/costing/CostAdjustmentUtils.java M src/org/openbravo/costing/CostingBackground.java ---

(0071355) umartirena (reporter) 2014-11-03 13:57	Code Review & Testing OK

(0072987) hudsonbot (developer) 2014-12-30 23:23	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/6525fe229e06 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2014-10-24 09:34	gorkaion	New Issue
2014-10-24 09:34	gorkaion	Assigned To	=> gorkaion
2014-10-24 09:34	gorkaion	Modules	=> Core
2014-10-24 09:34	gorkaion	Triggers an Emergency Pack	=> No
2014-10-24 12:14	hgbot	Checkin
2014-10-24 12:14	hgbot	Note Added: 0071166
2014-10-24 12:14	hgbot	Status	new => resolved
2014-10-24 12:14	hgbot	Resolution	open => fixed
2014-10-24 12:14	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/962bfa536e18a7cc17a1b9d940a46947b5ca380b [^]
2014-11-03 13:57	umartirena	Review Assigned To	=> umartirena
2014-11-03 13:57	umartirena	Note Added: 0071355
2014-11-03 13:57	umartirena	Status	resolved => closed
2014-11-03 13:57	umartirena	Fixed in Version	=> pi
2014-12-30 23:23	hudsonbot	Checkin
2014-12-30 23:23	hudsonbot	Note Added: 0072987
2019-06-03 18:19	migueldejuana	Relationship added	related to 0041015