SELECT f_get_doc_desc(C.doc_type_id) AS doc_type, C.doc_no AS doc_no, TO_DATE(C.doc_date, 'YYYYMMDD') AS doc_date,
         C.remark, D.doc_no AS so_no, TO_DATE(D.doc_date, 'YYYYMMDD') AS so_date, f_get_partner_code(A.partner_id) AS customer_code,
         f_get_product_catalog_code(B.product_catalog_id) AS product_catalog_code,
         CASE WHEN E.product_id IS NULL THEN ' ' ELSE f_get_product_code(E.product_id) END AS product_code,
         CASE WHEN E.catalog_id IS NULL THEN ' ' ELSE f_get_catalog_code(E.catalog_id) END AS catalog_code,
         A.qty_dlv_so AS qty, A.tax_percentage, A.price_so AS harga_katalok,
         A.commission_percentage AS fee_ds_percentage, A.commission_amount AS fee_ds_amount,
         A.item_amount - A.item_amount_member_price AS diskon_member_amount,
         A.discount_member_percentage AS diskon_member_percentage,
         0 AS discount_promo_amount, -- saat ini masih belum ada discount promo, jadi dipantek 0 dulu, need changes jika suatu saat discount promo sudah dipakai
         A.discount_promo_percentage AS discount_promo_percentage,
         A.item_amount AS subtotal_harga_katalok, A.item_amount_member_price AS subtotal_harga_member,
         (A.item_amount_member_price -  A.commission_amount -  ROUND( f_get_amount_before_tax(A.item_amount_member_price -  A.commission_amount , 'Y', A.tax_percentage, 0, 'RD'), 0 )) AS tax_amount,
         C.create_datetime, C.update_datetime,
         A.item_amount_member_price - A.commission_amount AS subtotal_harga_nett,
         ROUND( f_get_amount_before_tax(A.item_amount_member_price - A.commission_amount , 'Y', A.tax_percentage, 0, 'RD'), 0) AS dpp_amount,
         TRUNC(COALESCE(G.product_value, 0) * A.item_amount_member_price * 0.01) AS subtotal_product_value,
         CASE WHEN F.flg_buy_konsinyasi = 'Y'
            THEN COALESCE(J.gross_price_po * A.qty_dlv_so, 0)
            ELSE COALESCE(K.amount_total * A.qty_dlv_so / K.qty_total, 0)
         END AS cogs_amount,
         CASE WHEN F.flg_buy_konsinyasi = 'Y'
            THEN ROUND( f_get_amount_before_tax(A.item_amount_member_price - A.commission_amount , 'Y', A.tax_percentage, 0, 'RD'), 0) - COALESCE(J.gross_price_po * A.qty_dlv_so, 0)
            ELSE ROUND( f_get_amount_before_tax(A.item_amount_member_price - A.commission_amount , 'Y', A.tax_percentage, 0, 'RD'), 0) - COALESCE(K.amount_total * A.qty_dlv_so / K.qty_total, 0)
         END AS gross_margin,
         COALESCE(F.style_product, '') AS style,
         (COALESCE(EX.dimension_length, 0) * COALESCE(EX.dimension_width, 0) * COALESCE(EX.dimension_height, 0) / 6000) * A.qty_dlv_so AS subtotal_volume_weight,
         (COALESCE(EX.weight, 0) / 1000.0) * A.qty_dlv_so AS subtotal_weight,
         CASE WHEN (COALESCE(EX.dimension_length, 0) * COALESCE(EX.dimension_width, 0) * COALESCE(EX.dimension_height, 0) / 6000.0) * A.qty_dlv_so >  (COALESCE(EX.weight, 0) / 1000.0) * A.qty_dlv_so
            THEN (COALESCE(EX.dimension_length, 0) * COALESCE(EX.dimension_width, 0) * COALESCE(EX.dimension_height, 0) / 6000.0) * A.qty_dlv_so
            ELSE (COALESCE(EX.weight, 0) / 1000.0) * A.qty_dlv_so
         END AS used_volume_weight
FROM sl_so_mlm_balance_invoice A
INNER JOIN sl_do_mlm_item B ON B.do_mlm_id = A.ref_id AND B.do_mlm_item_id = A.ref_item_id
INNER JOIN sl_do_mlm C ON C.do_mlm_id = A.ref_id AND B.do_mlm_id = C.do_mlm_id AND A.ref_doc_type_id = C.doc_type_id
INNER JOIN sl_so_mlm D ON A.so_id = D.so_mlm_id AND C.ref_id = D.so_mlm_id
LEFT JOIN m_product_catalog E ON B.product_catalog_id = E.product_catalog_id
LEFT JOIN m_product_weight_dimension EX ON E.product_id = EX.product_id
LEFT JOIN m_product_custom F ON E.product_id = F.product_id
LEFT JOIN m_style_product G ON F.style_product = G.style_product AND E.catalog_id = G.catalog_id
LEFT JOIN mlm_ds H ON A.partner_id = H.partner_id
LEFT JOIN mlm_member_profile I ON H.member_id = I.member_id
LEFT JOIN m_product_catalog_cogs J ON B.product_catalog_id = J.product_catalog_id
LEFT JOIN in_summary_monthly_cogs K ON C.tenant_id = K.tenant_id AND substr(C.doc_date, 1, 6) = K.date_year_month AND C.ou_id = K.ou_id AND E.product_id = K.product_id
WHERE A.ref_doc_type_id = 313