0034962: Sales Invoice Dimensional Report: distinct cost calculated depending of dimension selected

select sum(convamount), sum(convcost)--, sum(k) 
from (
select nivel1, 
       nivel2, 
       Sum(amount)                      AS AMOUNT, 
       Sum(qty)                         AS QTY, 
       Sum(weight)                      AS WEIGHT, 
       Sum(cost)                        AS COST, 
       Sum(convamount)                  AS CONVAMOUNT, 
        --Sum(convcost) * Sum(convamount) / Sum(CASE WHEN groupcount = costcalculated THEN 0 ELSE convamount END) as k, 
       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, 
       C_currency_symbol('100', 0, 'Y') AS CONVSYM, 
       C_currency_isosym('100')         AS CONVISOSYM, 
       Sum(ZZ.costcalculated)           AS COSTCALCULATED 
FROM   (SELECT nivel1, 
               nivel2, 
               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, '100', 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, 
               trcurrencyid, 
               trdate, 
               trclientid, 
               trorgid, 
               Sum(AA.costcalculated) AS COSTCALCULATED, 
               C_currency_convert(Sum(costedamt), trcurrencyid, '100', To_date( 
               trdate), 
               NULL, 
               trclientid, trorgid)   AS costedamt, 
               Count(*)               AS GROUPCOUNT 
        FROM   (SELECT To_char(ad_org.NAME)                             AS 
                       NIVEL1, 
                       To_char(c_invoice.documentno)                    AS NIVEL2, 
                       --'a' as nivel2,
                       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, '100', 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 ( '29D81519EE794098A5CDAFF15CA6098F' ) 
        AND c_invoice.ad_client_id IN ( '0', '101C8C9C4A8D4C84857E3F5FBD722757'
                                      ) 
        AND 1 = 1 
        AND c_invoice.dateinvoiced >= To_date('11-01-2016') 
        AND c_invoice.dateinvoiced < To_date('11-23-2016') 
 ORDER  BY nivel1, 
           nivel2) AA 
 GROUP  BY nivel1, 
           nivel2, 
           trcurrencyid, 
           trdate, 
           trclientid, 
           trorgid) ZZ 
GROUP  BY nivel1, 
          nivel2) a

select nivel1,
       nivel2,
       Sum(amount)                      AS AMOUNT, 
       Sum(qty)                         AS QTY, 
       Sum(weight)                      AS WEIGHT, 
       Sum(cost)                        AS COST, 
       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 
FROM   (SELECT nivel1, 
               nivel2, 
               Sum(linenetamt)        AS AMOUNT, 
               Sum(qtyinvoiced)       AS QTY, 
               Sum(weight)            AS WEIGHT, 
               Sum(cost)              AS COST, 
               C_currency_convert(Sum(linenetamt), trcurrencyid, '100', 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, 
               trcurrencyid, 
               trdate, 
               trclientid, 
               trorgid, 
               Sum(AA.costcalculated) AS COSTCALCULATED, 
               C_currency_convert(Sum(costedamt), trcurrencyid, '100', To_date( 
               trdate), 
               NULL, 
               trclientid, trorgid)   AS costedamt, 
               Count(*)               AS GROUPCOUNT 
        FROM   (SELECT To_char(ad_org.NAME)                             AS 
                       NIVEL1, 
                       To_char('')                                      AS 
                       NIVEL2, 
                       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, '100', 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 ( '29D81519EE794098A5CDAFF15CA6098F' ) 
        AND c_invoice.ad_client_id IN ( '0', '101C8C9C4A8D4C84857E3F5FBD722757'
                                      ) 
        AND 1 = 1 
        AND c_invoice.dateinvoiced >= To_date('11-01-2016') 
        AND c_invoice.dateinvoiced < To_date('11-23-2016') 
--        and c_invoice.documentno not in ('022401-SI1002915','022401-SI1003099','022401-SI1003191')
 ORDER  BY nivel1, 
           nivel2) AA 
 GROUP  BY nivel1, 
           nivel2, 
           trcurrencyid, 
           trdate, 
           trclientid, 
           trorgid) ZZ 
GROUP  BY nivel1, 
          nivel2 
ORDER  BY nivel1