Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0008422Openbravo ERP04. Warehouse managementpublic2009-04-03 13:142009-05-05 00:00
Reportersureshbabu 
Assigned Tojayakumar 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS30OS Version
Product Version2.50beta 
Target VersionFixed in Version2.50MP1 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0008422: Value stock report shows same value if the product as more than one attributes
DescriptionValue stock report shows same values if the product as more than one attributes

Note: check the screen shot for more details

Steps To Reproduce1) Create a PO with product as Grand beer <any attribute> & change the unit value.
2) Create one more PO with product as Grand Beer <any attribute other than the above one > and also change the unit price
3) receive both the PO's
4) Run the Generate Average cost process
5) move to value stock report, enter the required details and run the report

 Warehouse Management || Analysis Tools || Valued Stock Report || Valued Stock Report

Actual result: Values are repeated if the product as more than one different.
Proposed Solution
Additional Information
Tags250 final review, 250MP1, comsupchennai_sprint4
Relationships
depends on backport 0008535 closed jayakumar Value stock report shows same value if the product as more than one attributes 
Attached Filesjpg Value stock report shows the same value if it has more than one attribute.JPG (226,145) 2009-04-03 13:14
https://issues.openbravo.com/file_download.php?file_id=1120&type=bug
jpg
Issue History
Date ModifiedUsernameFieldChange
2009-04-03 13:14sureshbabuNew Issue
2009-04-03 13:14sureshbabuAssigned To => rafaroda
2009-04-03 13:14sureshbabuFile Added: Value stock report shows the same value if it has more than one attribute.JPG
2009-04-03 13:14sureshbabuRegression testing => No
2009-04-03 13:15sureshbabuTag Attached: 250 final review
2009-04-09 17:21rafarodaAssigned Torafaroda => sathiyan
2009-04-09 17:22rafarodaStatusnew => scheduled
2009-04-09 17:22rafarodafix_in_branch => pi
2009-04-15 08:31sathiyanAssigned Tosathiyan => jayakumar
2009-04-16 09:08jayakumarNote Added: 0015376
2009-04-18 07:51jayakumarNote Added: 0015409
2009-04-23 16:05psarobeTag Attached: 250MP1
2009-04-24 08:51hgbotCheckin
2009-04-24 08:51hgbotNote Added: 0015651
2009-04-24 08:51hgbotStatusscheduled => resolved
2009-04-24 08:51hgbotResolutionopen => fixed
2009-04-24 08:51hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/2031a79894c7d290d261541527d86760c04fdab8 [^]
2009-04-24 10:04jpabloaeStatusresolved => new
2009-04-24 10:04jpabloaeResolutionfixed => open
2009-04-24 10:04jpabloaeNote Added: 0015670
2009-04-24 12:23psarobePriorityhigh => urgent
2009-04-24 12:23psarobeStatusnew => scheduled
2009-04-24 12:23psarobefix_in_branchpi =>
2009-04-24 13:59hgbotCheckin
2009-04-24 13:59hgbotNote Added: 0015696
2009-04-24 13:59hgbotStatusscheduled => resolved
2009-04-24 13:59hgbotResolutionopen => fixed
2009-04-24 13:59hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/2031a79894c7d290d261541527d86760c04fdab8 [^] => http://code.openbravo.com/erp/devel/pi/rev/6dd15432743f49004c8d0f86e6b79ffd0c55155d [^]
2009-04-24 15:35jayakumarTag Attached: comsupchennai_sprint4
2009-05-04 18:57psarobeStatusresolved => closed
2009-05-05 00:00anonymoussf_bug_id0 => 2786890

Notes
(0015376)
jayakumar   
2009-04-16 09:08   
Reporter wants to have average cost for attribute basis. Currently “Generate Average Cost” process does not include attribute as one of the parameter. They have computed only product basis. If we want to fix it in “Valued Stock Report” (showing different average cost for different attribute), we have to fix it in “Generate Average Cost” process.

Approach 1:
We may not show this report based on the attribute set. It means same product should not repeat again and again (attribute set should not be considered). We have to show single line for any given product. For instance, “Boots” is repeating again and again in the given screen shot. It should not be repeat again and again.

Approach 2:
We can leave “Valued Stock Report” as it is (considering attribute set). But we have to fix it in “Generate Average Cost” process that need to be consider attribute set.

We need to get clarification, since it has business impact.
(0015409)
jayakumar   
2009-04-18 07:51   
Gorka's suggestion: The correct way to solve this bug is using the approach 1 that you suggested. the attribute set shouldn't be considered to calculate the average cost. for instance:

I bought 1 L of milk (Lot-AAA) : 1$
Today I've bought another bottle (Lot-BBB) : 2$

So this report should show only a single line for the product milk with the average price 1.50$. I've talked with the QA department and they told to me that this report was working fine some time ago so you could check first if someone has done some modifications on the SQL statement.
(0015651)
hgbot   
2009-04-24 08:51   
Repository: erp/devel/pi
Changeset: 2031a79894c7d290d261541527d86760c04fdab8
Author: Jayakumar Kasinathan <jayakumar.kasinathan <at> openbravo.com>
Date: Fri Apr 24 00:23:51 2009 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/2031a79894c7d290d261541527d86760c04fdab8 [^]

Fixes bug 8422 Value stock report shows same value if the product as more than one attributes

---
M src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
---
(0015670)
jpabloae   
2009-04-24 10:04   
This fix is broken on Oracle:
http://builds.openbravo.com/job/erp_devel_pi-inc-oracle/140/console [^]

     [java] 487 [main] INFO org.openbravo.data.Sqlc - File: ./org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql processed
     [java] java.sql.SQLException: ORA-00904: "TOTAL_COST": invalid identifier
     [java] 814 [main] ERROR org.openbravo.data.Sqlc - SQL error in query:
     [java]
     [java] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
     [java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
     [java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
     [java] at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
     [java] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
     [java] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
     [java] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
     [java]
     [java] at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
     [java] SELECT M_PRODUCT_CATEGORY.NAME AS CATEGORY_NAME, A.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER(TO_CHAR('M_Product'), TO_CHAR(M_PRODUCT.M_PRODUCT_ID), TO_CHAR(?)) AS PRODUCT_NAME,
     [java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
     [java] A.QTY, C_UOM.NAME AS UOM_NAME,
     [java] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
     [java] C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID,TO_DATE(?), NULL), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS AVERAGE_COST,
     [java] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
     [java] at org.openbravo.data.Sqlc.query(Sqlc.java:627)
     [java] at org.openbravo.data.Sqlc.endElement(Sqlc.java:507)
     [java] at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
     [java] at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanEndElement(Unknown Source)
     [java] A.QTY * C_CURRENCY_CONVERT(M_GET_PRODUCT_COST(A.M_PRODUCT_ID,TO_DATE(?), NULL), ?, ?, COALESCE(MC.DATEFROM,TO_DATE(?)), NULL, MC.AD_CLIENT_ID, MC.AD_ORG_ID) AS TOTAL_COST
     [java] FROM M_PRODUCT_CATEGORY,
     [java] at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
     [java] (SELECT M_PRODUCT_ID, SUM(MOVEMENTQTY) AS QTY, C_UOM_ID FROM M_TRANSACTION, M_LOCATOR
     [java] at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
     [java] at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
     [java] WHERE M_TRANSACTION.M_LOCATOR_ID = M_LOCATOR.M_LOCATOR_ID
     [java] AND MOVEMENTDATE < to_date(?)
     [java] AND M_LOCATOR.M_WAREHOUSE_ID = ?
     [java] GROUP BY M_PRODUCT_ID, C_UOM_ID) A, C_UOM,
     [java] M_PRODUCT LEFT JOIN M_COSTING MC ON MC.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
     [java] AND A.C_UOM_ID = C_UOM.C_UOM_ID
     [java] AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
     [java] AND 1=1
     [java] AND A.QTY <>0
     [java] GROUP BY A.M_PRODUCT_ID, CATEGORY_NAME ,PRODUCT_NAME,A.QTY, UOM_NAME, AVERAGE_COST, TOTAL_COST
     [java] ORDER BY CATEGORY_NAME, PRODUCT_NAME
     [java] Exception:java.sql.SQLException: ORA-00904: "TOTAL_COST": invalid identifier
     [java]
     [java] 850 [main] ERROR org.openbravo.data.Sqlc - 1 errors found!
(0015696)
hgbot   
2009-04-24 13:59   
Repository: erp/devel/pi
Changeset: 6dd15432743f49004c8d0f86e6b79ffd0c55155d
Author: Jayakumar Kasinathan <jayakumar.kasinathan <at> openbravo.com>
Date: Fri Apr 24 05:32:01 2009 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/6dd15432743f49004c8d0f86e6b79ffd0c55155d [^]

Fixes bug 8422 This is been fixed for adapting Oracle.

---
M src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql
---