details: https://code.openbravo.com/erp/devel/pi/rev/c3acd6c8dc33 changeset: 25905:c3acd6c8dc33 user: Unai Martirena <unai.martirena <at> openbravo.com> date: Thu Feb 12 09:06:42 2015 +0100 summary: Related to Bug 28890: Valued Stock Report shows correct qty with Cost Adjusments
The query is working in Postgres and in Oracle details: https://code.openbravo.com/erp/devel/pi/rev/77ed398f859c changeset: 25906:77ed398f859c user: Unai Martirena <unai.martirena <at> openbravo.com> date: Thu Feb 12 13:35:34 2015 +0100 summary: Related to bug 28896: Missing entry on group by clause details: https://code.openbravo.com/erp/devel/pi/rev/3cf79f7adef9 changeset: 25907:3cf79f7adef9 user: Unai Martirena <unai.martirena <at> openbravo.com> date: Thu Feb 12 18:43:18 2015 +0100 summary: Related to bug 28854: Fix on whereClause, add missing or clause diffstat: modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java | 6 +- src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql | 60 +++++---- 2 files changed, 38 insertions(+), 28 deletions(-) diffs (118 lines): diff -r d66afa655b19 -r 3cf79f7adef9 modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java --- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java Fri Feb 13 13:44:15 2015 +0100 +++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java Thu Feb 12 18:43:18 2015 +0100 @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2014 Openbravo SLU + * All portions are Copyright (C) 2014 - 2015 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -242,7 +242,7 @@ isFirst = true; } } - whereClause.append(")"); + whereClause.append(") or "); } if ("I".equals(transactionType)) { @@ -294,6 +294,7 @@ groupByClause.append(" COALESCE(ipriority.priority, opriority.priority), "); groupByClause.append(" inv.salesTransaction, "); groupByClause.append(" oinfo.organization, "); + groupByClause.append(" oinfo.aPRMPaymentDescription, "); groupByClause.append(" inv.orderReference, "); } else if ("O".equals(transactionType)) { groupByClause.append(" ord.documentNo, "); @@ -310,6 +311,7 @@ groupByClause.append(" COALESCE(ipriority.priority, opriority.priority), "); groupByClause.append(" inv.salesTransaction, "); groupByClause.append(" oinfo.organization, "); + groupByClause.append(" oinfo.aPRMPaymentDescription, "); groupByClause.append(" inv.orderReference, "); } groupByClause.append(" COALESCE(invbp.id, ordbp.id), "); diff -r d66afa655b19 -r 3cf79f7adef9 src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Fri Feb 13 13:44:15 2015 +0100 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportValuationStock_data.xsql Thu Feb 12 18:43:18 2015 +0100 @@ -28,36 +28,43 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT CATEGORY_NAME, M_PRODUCT_ID, PRODUCT_NAME, SUM(QTY) AS QTY, UOM_NAME, CASE ISCOSTCALCULATED - WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(QTY) + SELECT CATEGORY_NAME, ZZ.M_PRODUCT_ID, PRODUCT_NAME, SUM(M.MOVEMENTQTY) AS QTY, UOM_NAME, + CASE ZZ.ISCOSTCALCULATED + WHEN 'Y' THEN SUM(TOTAL_COST) / SUM(M.MOVEMENTQTY) ELSE NULL END AS AVERAGE_COST, SUM(TOTAL_COST) AS TOTAL_COST - FROM (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, - A.QTY, C_UOM.NAME AS UOM_NAME, - CASE a.iscostcalculated - WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (Suma,A.c_currency_id,?,A.movementdate,NULL,A.AD_CLIENT_ID,?,'C') + FROM M_TRANSACTION M + LEFT JOIN (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, + C_UOM.NAME AS UOM_NAME, SUM(A.SUMA) AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.C_CURRENCY_ID, A.M_TRANSACTION_ID + FROM M_PRODUCT_CATEGORY, + M_TRANSACTION TR + LEFT JOIN (SELECT TRX.M_TRANSACTION_ID, TRX.M_PRODUCT_ID, + CASE TRX.ISCOSTCALCULATED + WHEN 'Y' THEN C_CURRENCY_CONVERT_PRECISION (SUM(CASE WHEN TRX.MOVEMENTQTY < 0 THEN - TC.TRXCOST ELSE TC.TRXCOST END),TC.C_CURRENCY_ID,?,TC.MOVEMENTDATE,NULL,TRX.AD_CLIENT_ID,?,'C') ELSE NULL - END AS TOTAL_COST, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.MOVEMENTDATE, A.C_CURRENCY_ID - FROM M_PRODUCT_CATEGORY, - (SELECT trx.m_transaction_id, trx.M_PRODUCT_ID, trx.MOVEMENTQTY AS QTY, sum(CASE WHEN trx.MOVEMENTQTY < 0 THEN- tc.cost ELSE tc.cost END) AS Suma, - trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(tc.dateacct, tc.COSTDATE) as movementdate - FROM M_TRANSACTION trx - JOIN M_LOCATOR l ON trx.M_LOCATOR_ID = l.M_LOCATOR_ID - LEFT JOIN M_TRANSACTION_COST tc ON trx.m_transaction_id = tc.m_transaction_id - WHERE trx.MOVEMENTDATE < to_date(?) - AND COALESCE(tc.DATEACCT, tc.COSTDATE) < to_date(?) - AND l.M_WAREHOUSE_ID = ? - GROUP BY trx.m_transaction_id, trx.M_PRODUCT_ID, trx.MOVEMENTQTY, trx.C_UOM_ID, trx.AD_CLIENT_ID, trx.iscostcalculated, tc.c_currency_id, coalesce(tc.dateacct, tc.COSTDATE)) A, - C_UOM, - M_PRODUCT - WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - AND A.C_UOM_ID = C_UOM.C_UOM_ID - AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID - AND 1 = 1 - AND (A.QTY <> 0 OR A.Suma <> 0)) ZZ - GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ISCOSTCALCULATED - HAVING SUM(QTY) <>0 + END AS SUMA, + TRX.C_UOM_ID, TRX.AD_CLIENT_ID, TRX.ISCOSTCALCULATED, TC.C_CURRENCY_ID + FROM M_TRANSACTION TRX + JOIN M_LOCATOR L ON TRX.M_LOCATOR_ID = L.M_LOCATOR_ID + LEFT JOIN (SELECT SUM(COST) AS TRXCOST, M_TRANSACTION_ID, C_CURRENCY_ID, COALESCE(DATEACCT, COSTDATE) as MOVEMENTDATE + FROM M_TRANSACTION_COST + WHERE COALESCE(DATEACCT, COSTDATE) < to_date(?) + GROUP BY m_transaction_id, C_CURRENCY_ID, COALESCE(DATEACCT, COSTDATE)) TC ON TRX.M_TRANSACTION_ID = TC.M_TRANSACTION_ID + WHERE TRX.MOVEMENTDATE < to_date(?) + AND L.M_WAREHOUSE_ID = ? + GROUP BY TRX.M_TRANSACTION_ID, TRX.M_PRODUCT_ID, TRX.C_UOM_ID, TRX.AD_CLIENT_ID, TRX.ISCOSTCALCULATED, TC.C_CURRENCY_ID, TC.MOVEMENTDATE) A ON TR.M_TRANSACTION_ID = A.M_TRANSACTION_ID, + C_UOM, + M_PRODUCT + WHERE A.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID + AND A.C_UOM_ID = C_UOM.C_UOM_ID + AND M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID + AND 1 = 1 + AND ( A.SUMA <> 0 OR TR.MOVEMENTQTY <> 0) + GROUP BY M_PRODUCT_CATEGORY.NAME, A.M_PRODUCT_ID, M_PRODUCT.M_PRODUCT_ID, AD_COLUMN_IDENTIFIER (to_char('M_Product'),to_char(M_PRODUCT.M_PRODUCT_ID),to_char(?)), C_UOM.NAME, A.ISCOSTCALCULATED, A.AD_CLIENT_ID, A.C_CURRENCY_ID, A.M_TRANSACTION_ID) ZZ + ON M.M_TRANSACTION_ID = ZZ.M_TRANSACTION_ID + GROUP BY ZZ.M_PRODUCT_ID, CATEGORY_NAME, PRODUCT_NAME, UOM_NAME, ZZ.ISCOSTCALCULATED + HAVING SUM(M.MOVEMENTQTY) <>0 ORDER BY CATEGORY_NAME, PRODUCT_NAME ]]></Sql> <Field name="rownum" value="count"/> @@ -67,6 +74,7 @@ <Parameter name="datePlus"/> <Parameter name="datePlus"/> <Parameter name="warehouse"/> + <Parameter name="adLanguage"/> <Parameter name="categoryProduct" optional="true" after="AND 1 = 1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_CATEGORY_ID= ? ]]></Parameter> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> ------------------------------------------------------------------------------ Dive into the World of Parallel Programming. The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
