SELECT nivel1, nivel2, nivel3, nivel4, nivel5, nivel6, nivel7, nivel8, nivel9, nivel10, Sum(amount) AS AMOUNT, Sum(qty) AS QTY, Sum(weight) AS WEIGHT, Sum(cost) AS COST, Sum(amountref) AS AMOUNTREF, Sum(qtyref) AS QTYREF, Sum(weightref) AS WEIGHTREF, Sum(convamount) AS CONVAMOUNT, CASE WHEN Sum(costcalculated) = 0 THEN Sum(convcost) WHEN Sum(costedamt) = 0 THEN 0 WHEN Sum(CASE WHEN groupcount = costcalculated THEN 0 ELSE convamount END) = 0 THEN 0 ELSE Sum(convcost) * Sum(convamount) / Sum( CASE WHEN groupcount = costcalculated THEN 0 ELSE convamount END) END AS CONVCOST, Sum(convamountref) AS CONVAMOUNTREF, Sum(convcostref) AS CONVCOSTREF, C_currency_symbol('116', 0, 'Y') AS CONVSYM, C_currency_isosym('116') AS CONVISOSYM, Sum(ZZ.costcalculated) AS COSTCALCULATED 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, '116', To_date( trdate), NULL, trclientid, trorgid) 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, trcurrencyid, trdate, trclientid, trorgid, Sum(AA.costcalculated) AS COSTCALCULATED, C_currency_convert(Sum(costedamt), trcurrencyid, '116', To_date( trdate), NULL, trclientid, trorgid) AS costedamt, Count(*) AS GROUPCOUNT FROM (SELECT To_char(Ad_column_identifier(To_char('M_Product'), To_char( m_product.m_product_id), To_char('en_US')) || CASE WHEN uomsymbol IS NULL THEN '' ELSE To_char(' (' || uomsymbol || ')') END) AS NIVEL1, To_char('') AS NIVEL2, To_char('') AS NIVEL3, To_char('') AS NIVEL4, To_char('') AS NIVEL5, To_char('') AS NIVEL6, To_char('') AS NIVEL7, To_char('') AS NIVEL8, To_char('') AS NIVEL9, To_char('') AS NIVEL10, CASE WHEN c_doctype.docbasetype = 'ARC' THEN c_invoiceline.linenetamt *- 1 ELSE c_invoiceline.linenetamt END AS LINENETAMT, CASE WHEN c_doctype.docbasetype = 'ARC' THEN c_invoiceline.qtyinvoiced *- 1 ELSE c_invoiceline.qtyinvoiced END AS QTYINVOICED, c_invoiceline.qtyinvoiced * m_product.weight AS WEIGHT, 0 AS LINENETREF, 0 AS QTYINVOICEDREF, 0 AS WEIGHT_REF, c_uom.uomsymbol, c_invoice.c_currency_id AS TRCURRENCYID , To_date(COALESCE(c_invoice.dateinvoiced, Now()) ) AS TRDATE, c_invoiceline.ad_client_id AS TRCLIENTID, c_invoiceline.ad_org_id AS TRORGID, CASE WHEN trxcost.cost IS NULL AND m_product.isstocked = 'Y' AND m_product.producttype = 'I' THEN 1 ELSE 0 END AS COSTCALCULATED, CASE WHEN ( m_product.isstocked = 'Y' AND m_product.producttype = 'I' ) THEN ( CASE WHEN trx.movementqty = 0 THEN 0 ELSE COALESCE( C_currency_convert_precision (Round( trxcost.cost / Abs(trx.movementqty) * ( CASE WHEN c_doctype.docbasetype = 'ARC' THEN c_invoiceline.qtyinvoiced *- 1 ELSE c_invoiceline.qtyinvoiced END ), C_get_currency_precision(trxcost.c_currency_id, 'C')), trxcost.c_currency_id, '116', trx.movementdate, NULL, trx.ad_client_id, trx.ad_org_id, 'C'), 0) END ) ELSE ( COALESCE(M_get_no_trx_product_cost(m_product.m_product_id, c_invoice.dateinvoiced, 'STA', ad_org.ad_org_id, NULL, c_invoice.c_currency_id), 0 ) * ( CASE WHEN c_doctype.docbasetype = 'ARC' THEN c_invoiceline.qtyinvoiced *- 1 ELSE c_invoiceline.qtyinvoiced END ) ) END AS COST, CASE WHEN 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 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 JOIN c_bp_group ON c_bpartner.c_bp_group_id = c_bp_group.c_bp_group_id JOIN ad_org ON c_invoice.ad_org_id = ad_org.ad_org_id LEFT JOIN c_bpartner CB ON c_bpartner.salesrep_id = CB.c_bpartner_id LEFT JOIN ad_user ON c_invoice.salesrep_id = ad_user.ad_user_id LEFT JOIN c_project ON c_invoice.c_project_id = c_project.c_project_id LEFT JOIN c_doctype ON c_invoice.c_doctype_id = c_doctype.c_doctype_id 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 m_inoutline ON c_invoiceline.m_inoutline_id = m_inoutline.m_inoutline_id 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 (SELECT Sum(cost) AS cost, m_transaction_id, c_currency_id FROM m_transaction_cost GROUP BY m_transaction_id, c_currency_id) trxcost ON trx.m_transaction_id = trxcost.m_transaction_id WHERE c_invoice.issotrx = 'Y' AND c_invoice.processed = 'Y' AND c_invoice.ad_org_id IN ( 'C002170F73544C6E9AAEC8DD7FBB5750', '0A733B3F5292414BA2310836A3EE9B73', '664FFE8B17974ABCBE4215D77996BB16', 'AA6E6D54ACC94E3DA142B1CA42C332E6', 'E168567EAFF94B4793996B0AB415930B', '24D0809B7ACB4BEEBFD742E6D3BF1004', '61D784C8E7B24C61864CAAC67AC3554F', '9F488865B7FF4341938EBA9D94204795', '2A85B2F756EA492897FA0C5061D35C04', '33DD7E752F9E4370AAE84B7B6B1EA5AD' ) AND c_invoice.ad_client_id IN ( '0', '101C8C9C4A8D4C84857E3F5FBD722757' ) AND 1 = 1 AND c_invoice.dateinvoiced >= To_date('04-01-2017') AND c_invoice.dateinvoiced < To_date('06-01-2017') AND c_bp_group.c_bp_group_id = '21FE15587FE048D4B898272F4D44176A' 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 ORDER BY nivel1