vw_trx_sales_indocom_with_gp

DROP VIEW IF EXISTS vw_trx_sales_indocom_with_gp;

CREATE OR REPLACE VIEW vw_trx_sales_indocom_with_gp
AS
WITH data_penjualan AS (
    SELECT E.brand_code, E.brand_name, F.ctgr_product_code, F.ctgr_product_name, G.partner_code as salesman_code, G.partner_name as salesman_name,
        H.line_of_business, SUBSTRING(A.ref_doc_date, 1, 6) AS periode,
        CASE WHEN A.ref_doc_type_id = 502 OR A.do_receipt_item_id <> -99 THEN 'Y' ELSE 'N' END AS is_return,
        (A.price_so * ABS(A.qty_dlv_so)) *  f_commercial_rate(A.tenant_id, A.ref_doc_date, A.curr_code, 'IDR') AS sell_amount,
        COALESCE(J.price * ABS(A.qty_dlv_so), 0) AS purch_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id
    INNER JOIN m_product D ON D.product_id = C.product_id
    INNER JOIN m_brand E ON D.brand_id = E.brand_id
    INNER JOIN m_ctgr_product F ON D.ctgr_product_id = F.ctgr_product_id
    INNER JOIN m_partner G ON B.salesman_id = G.partner_id
    INNER JOIN m_partner H ON A.partner_id = H.partner_id
    INNER JOIN m_ou_structure I ON A.ou_id = I.ou_id
    LEFT OUTER JOIN sl_so_item_purchasing J ON J.so_item_id = C.ref_id
    --LEFT JOIN in_summary_monthly_amount J ON J.doc_type_id = -99
        --AND J.date_year_month = TO_CHAR(TO_DATE(A.ref_doc_date,'YYYYMMDD') + INTERVAL '1 Month','YYYYMM')
        --AND J.ou_bu_id = I.ou_bu_id AND J.product_id = D.product_id AND D.base_uom_id = J.base_uom_id
)
SELECT brand_code, brand_name, ctgr_product_code, ctgr_product_name, salesman_code, salesman_name, line_of_business, periode,
    SUM(CASE WHEN is_return = 'Y' THEN -1 * (sell_amount - purch_amount) ELSE sell_amount - purch_amount END) AS profit_amount,
    CASE WHEN SUM(CASE WHEN is_return = 'Y' THEN -1 * sell_amount ELSE sell_amount END) = 0 THEN 0 ELSE
        SUM(CASE WHEN is_return = 'Y' THEN -1 * (sell_amount - purch_amount) ELSE sell_amount - purch_amount END) /
        SUM(CASE WHEN is_return = 'Y' THEN -1 * sell_amount ELSE sell_amount END) * 100
    END AS profit_percentage
FROM data_penjualan
GROUP BY brand_code, brand_name, ctgr_product_code, ctgr_product_name, salesman_code, salesman_name, line_of_business, periode
ORDER BY periode, brand_code, brand_name, ctgr_product_code, ctgr_product_name, salesman_code, salesman_name;