0051814: Error Dimensional Invoice Reports

--- /home/jnavarro/Descargas/Mincon/ReportInvoiceCustomerDimensionalAnalysesJR_data_OLD.xsql
+++ /home/jnavarro/Descargas/Mincon/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql
@@ -843,7 +843,9 @@
     FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, 
       SUM(LINENETAMT) AS AMOUNT, SUM(QTYINVOICED) AS QTY, SUM(WEIGHT) AS WEIGHT, SUM(COST) AS COST,
       SUM(LINENETREF) AS AMOUNTREF, SUM(QTYINVOICEDREF) AS QTYREF, SUM(WEIGHT_REF) AS WEIGHTREF,
-      C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) AS CONVAMOUNT,
+        case when convrate is null then C_CURRENCY_CONVERT(SUM(LINENETAMT), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) 
+        else round(SUM(LINENETAMT) * convrate, C_GET_CURRENCY_PRECISION(?, 'A'))
+        end AS CONVAMOUNT,
       CASE WHEN SUM(COSTCALCULATED) = 0 THEN SUM(COST) WHEN SUM(COSTEDAMT) = 0 THEN 0 ELSE SUM(COST)*SUM(LINENETAMT)/SUM(COSTEDAMT) END AS CONVCOST,
           0 AS CONVAMOUNTREF,
           0 AS CONVCOSTREF,
@@ -866,7 +868,8 @@
                 END AS COST,
                 CASE WHEN sum(trxcost.cost) is null AND m_product.isstocked = 'Y' AND m_product.producttype = 'I' THEN 0
                      ELSE CASE WHEN C_DOCTYPE.DOCBASETYPE='ARC' THEN C_INVOICELINE.LINENETAMT*-1 ELSE C_INVOICELINE.LINENETAMT END
-                END AS COSTEDAMT
+                END AS COSTEDAMT,
+                conversionrate.rate as convrate
             FROM C_INVOICELINE 
                 join C_INVOICE on C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
                 join C_BPARTNER on C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
@@ -883,18 +886,23 @@
                 left join M_INOUT ON M_INOUTLINE.M_INOUT_ID = M_INOUT.M_INOUT_ID
                 left join m_transaction trx ON trx.m_inoutline_id = m_inoutline.m_inoutline_id
                 left join m_transaction_cost trxcost ON trx.m_transaction_id = trxcost.m_transaction_id
+                 left join C_Conversion_Rate_Document conversionrate on conversionrate.c_invoice_id = C_INVOICE.C_INVOICE_ID
+                    and conversionrate.c_currency_id_to = ?
+                    and conversionrate.c_currency_id = C_INVOICE.C_CURRENCY_ID                
       WHERE C_INVOICE.ISSOTRX = 'Y'
         AND C_INVOICE.PROCESSED = 'Y'
         AND C_INVOICE.AD_ORG_ID IN ('1')
         AND C_INVOICE.AD_CLIENT_ID IN ('7')
         AND ((trxcost.m_transaction_cost_id is not null and trxcost.isunitcost = 'Y') or (trxcost.m_transaction_cost_id is null))
         AND 1=1
-      GROUP BY trxcost.m_transaction_id, C_INVOICELINE.C_INVOICELINE_ID, C_BPARTNER.C_BPARTNER_ID, M_PRODUCT_CATEGORY.NAME, C_INVOICE.DOCUMENTNO, AD_ORG.NAME, AD_USER.AD_USER_ID, C_PROJECT.NAME, AD_USER.FIRSTNAME, AD_USER.LASTNAME, C_BP_GROUP.NAME, M_INOUT.C_BPARTNER_LOCATION_ID, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID, C_INVOICELINE.LINENETAMT, C_INVOICELINE.QTYINVOICED, C_UOM.UOMSYMBOL, c_doctype.docbasetype, ad_org.ad_org_id, C_INVOICE.C_CURRENCY_ID, C_INVOICE.DATEINVOICED, trx.movementqty, trx.movementdate, trx.ad_client_id, trx.ad_org_id, trxcost.c_currency_id, M_PRODUCT.WEIGHT, M_PRODUCT.M_PRODUCT_ID, M_PRODUCT.ISSTOCKED, M_PRODUCT.PRODUCTTYPE
+      GROUP BY trxcost.m_transaction_id, C_INVOICELINE.C_INVOICELINE_ID, C_BPARTNER.C_BPARTNER_ID, M_PRODUCT_CATEGORY.NAME, C_INVOICE.DOCUMENTNO, AD_ORG.NAME, AD_USER.AD_USER_ID, C_PROJECT.NAME, AD_USER.FIRSTNAME, AD_USER.LASTNAME, C_BP_GROUP.NAME, M_INOUT.C_BPARTNER_LOCATION_ID, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID, C_INVOICELINE.LINENETAMT, C_INVOICELINE.QTYINVOICED, C_UOM.UOMSYMBOL, c_doctype.docbasetype, ad_org.ad_org_id, C_INVOICE.C_CURRENCY_ID, C_INVOICE.DATEINVOICED, trx.movementqty, trx.movementdate, trx.ad_client_id, trx.ad_org_id, trxcost.c_currency_id, M_PRODUCT.WEIGHT, M_PRODUCT.M_PRODUCT_ID, M_PRODUCT.ISSTOCKED, M_PRODUCT.PRODUCTTYPE, conversionrate.rate
       ORDER BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10) AA
-      GROUP BY  NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID) ZZ
+      GROUP BY  NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID, convrate) ZZ
       GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, NIVEL7, NIVEL8, NIVEL9, NIVEL10, 1
       ) B
      ]]></Sql>
+    <Parameter name="cCurrencyConv"/>
+    <Parameter name="cCurrencyConv"/>    
     <Parameter name="cCurrencyConv"/>
     <Parameter name="cCurrencyConv"/>
     <Parameter name="cCurrencyConv"/>

--- /home/jnavarro/Descargas/Mincon/ReportInvoiceVendorDimensionalAnalysesJR_data_OLD.xsql
+++ /home/jnavarro/Descargas/Mincon/ReportInvoiceVendorDimensionalAnalysesJR_data.xsql
@@ -129,7 +129,12 @@
       FROM (SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, 
       SUM(LINENETAMT) AS AMOUNT, SUM(CASE WHEN DOCBASETYPE = 'APC' THEN QTYINVOICED*-1 ELSE QTYINVOICED END) AS QTY,
       SUM(LINENETREF) AS AMOUNTREF, SUM(QTYINVOICEDREF) AS QTYREF,     
-	  CASE WHEN (SELECT COUNT(C_CONVERSION_RATE_DOCUMENT_ID) FROM C_CONVERSION_RATE_DOCUMENT WHERE C_INVOICE_ID=INVOICEID) > 0 THEN ((SELECT RATE FROM C_CONVERSION_RATE_DOCUMENT WHERE C_INVOICE_ID=INVOICEID)*SUM(LINENETAMT)) ELSE C_CURRENCY_CONVERT(SUM(CASE WHEN DOCBASETYPE = 'APC' THEN LINENETAMT*-1 ELSE LINENETAMT END), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID) END AS CONVAMOUNT,
+        case when convrate is null then C_CURRENCY_CONVERT(SUM(CASE
+                                              WHEN DOCBASETYPE = 'APC' THEN LINENETAMT*-1
+                                              ELSE LINENETAMT
+                                          END), TRCURRENCYID, ?, TO_DATE(TRDATE), NULL, TRCLIENTID, TRORGID)
+        else round(SUM(LINENETAMT) * convrate, C_GET_CURRENCY_PRECISION(?, 'A'))
+        end AS CONVAMOUNT,
 	  0 AS CONVAMOUNTREF,
       TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID
       FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, 
@@ -140,12 +145,16 @@
 	  C_INVOICELINE.AD_CLIENT_ID AS TRCLIENTID,      
 	  C_INVOICELINE.AD_ORG_ID AS TRORGID,
       C_INVOICE.C_INVOICE_ID AS INVOICEID,
-      C_DOCTYPE.DOCBASETYPE AS DOCBASETYPE
+      C_DOCTYPE.DOCBASETYPE AS DOCBASETYPE,
+      conversionrate.rate as convrate
       FROM C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
                          left join C_UOM     on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID 
                          left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
                          left join C_INVOICE on C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
-                         left join C_DOCTYPE on C_INVOICE.C_DOCTYPE_ID=C_DOCTYPE.C_DOCTYPE_ID,
+                         left join C_DOCTYPE on C_INVOICE.C_DOCTYPE_ID=C_DOCTYPE.C_DOCTYPE_ID
+                          left join C_Conversion_Rate_Document conversionrate on conversionrate.c_invoice_id = C_INVOICE.C_INVOICE_ID
+                            and conversionrate.c_currency_id_to = ?
+                            and conversionrate.c_currency_id = C_INVOICE.C_CURRENCY_ID,
       C_BPARTNER, C_BP_GROUP
       WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
@@ -155,9 +164,11 @@
       AND 0=0 AND C_INVOICE.AD_ORG_ID IN ('1')
       AND C_INVOICE.AD_CLIENT_ID IN ('6')
       AND 1=1) AA
-      GROUP BY NIVEL1,  NIVEL2, NIVEL3, NIVEL4, NIVEL5, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID, INVOICEID) ZZ
+      GROUP BY NIVEL1,  NIVEL2, NIVEL3, NIVEL4, NIVEL5, TRCURRENCYID, TRDATE, TRCLIENTID, TRORGID, INVOICEID, convrate) ZZ
       GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, 1
      ]]></Sql>
+    <Parameter name="cCurrencyConv"/>
+    <Parameter name="cCurrencyConv"/>
     <Parameter name="cCurrencyConv"/>
 	<Parameter name="cCurrencyConv"/>
 	<Parameter name="cCurrencyConv"/>