vw_trx_sales_indocom_with_detail_product

DROP VIEW IF EXISTS vw_trx_sales_indocom_with_detail_product;

CREATE OR REPLACE VIEW vw_trx_sales_indocom_with_detail_product
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(CASE WHEN (J.gl_amount <> 0 AND J.qty = 0) OR (J.gl_amount = 0 AND J.qty = 0) THEN 0 ELSE J.gl_amount / J.qty * ABS(A.qty_dlv_so) END, 0) AS purch_amount_summary_amount,
        COALESCE(K.price * ABS(A.qty_dlv_so), 0) AS purch_amount_so,
        COALESCE(L.avg_price * ABS(A.qty_dlv_so), 0) AS purch_amount_summary_cogs,
        B.doc_no AS so_no, B.doc_date AS so_date, A.ref_doc_no AS do_no, A.ref_doc_date AS do_date, D.product_code, D.product_name,
        A.so_balance_invoice_id, B.so_id, B.doc_type_id, C.ref_id AS so_item_id,
        A.tenant_id, A.qty_dlv_so
    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 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
    LEFT OUTER JOIN sl_so_item_purchasing K ON K.so_item_id = C.ref_id
    LEFT OUTER JOIN in_summary_monthly_cogs L ON L.ou_id = I.ou_bu_id AND L.product_id = D.product_id AND L.date_year_month = SUBSTRING(A.ref_doc_date, 1, 6)
), data_harga_po AS (
    SELECT B.so_balance_invoice_id, B.so_id, B.so_item_id,
    CASE WHEN SUM(ABS(K.qty_po)) = 0 THEN 0 ELSE
        SUM(f_commercial_rate(B.tenant_id, J.doc_date, K.curr_code, 'IDR') * K.nett_price_po * ABS(K.qty_po)) / SUM(ABS(K.qty_po))
    END * ABS(B.qty_dlv_so) AS purch_amount
    FROM data_penjualan B
    INNER JOIN pu_po J ON J.ref_id = B.so_id AND J.ref_doc_type_id = B.doc_type_id
    INNER JOIN pu_po_item K ON J.po_id = K.po_id AND B.so_item_id = K.ref_id
    WHERE J.status_doc <> 'V'
    GROUP BY B.so_balance_invoice_id, B.so_id, B.so_item_id, B.qty_dlv_so
)
SELECT A.so_no, A.so_date, A.do_no, A.do_date, A.salesman_name, A.product_name,
CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END AS sell_amount,
CASE WHEN is_return = 'Y' THEN -1 * A.purch_amount_summary_amount ELSE A.purch_amount_summary_amount END AS purch_amount_summary_amount,
CASE WHEN is_return = 'Y' THEN -1 * A.purch_amount_so ELSE A.purch_amount_so END AS purch_amount_so,
CASE WHEN is_return = 'Y' THEN -1 * COALESCE(B.purch_amount, 0) ELSE COALESCE(B.purch_amount, 0) END AS purch_amount_po,
CASE WHEN is_return = 'Y' THEN -1 * A.purch_amount_summary_cogs ELSE A.purch_amount_summary_cogs END AS purch_amount_summary_cogs,
CASE WHEN (
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END = 0
) THEN
    0
ELSE
    CASE WHEN is_return = 'Y' THEN -1 * (A.sell_amount - A.purch_amount_summary_amount) ELSE A.sell_amount - A.purch_amount_summary_amount END * 100 /
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END
END AS purch_amount_summary_amount_percentage,
CASE WHEN (
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END = 0
) THEN
    0
ELSE
    CASE WHEN is_return = 'Y' THEN -1 * (A.sell_amount - A.purch_amount_so) ELSE A.sell_amount - A.purch_amount_so END * 100 /
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END
END AS purch_amount_so_percentage,
CASE WHEN (
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END = 0
) THEN
    0
ELSE
    CASE WHEN is_return = 'Y' THEN -1 * (A.sell_amount - COALESCE(B.purch_amount, 0)) ELSE A.sell_amount - COALESCE(B.purch_amount, 0) END * 100 /
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END
END AS purch_amount_po_percentage,
CASE WHEN (
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END = 0
) THEN
    0
ELSE
    CASE WHEN is_return = 'Y' THEN -1 * (A.sell_amount - A.purch_amount_summary_cogs) ELSE A.sell_amount - A.purch_amount_summary_cogs END * 100 /
    CASE WHEN is_return = 'Y' THEN -1 * A.sell_amount ELSE A.sell_amount END
END AS purch_amount_summary_cogs_percentage
FROM data_penjualan A
LEFT OUTER JOIN data_harga_po B ON B.so_id = A.so_id AND B.so_item_id = A.so_item_id AND B.so_balance_invoice_id = A.so_balance_invoice_id
ORDER BY A.so_no, A.so_date, A.do_no, A.do_date, A.product_name;