Query Sales Dashboard

--getAuthorizedOuBuList
SELECT A.ou_id, A.ou_code, A.ou_name
FROM t_ou A
INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
WHERE A.tenant_id = 10
    AND B.flg_bu = 'Y'
    AND f_authorize_user_role_policy_ou(10, 15, 65, A.ou_id) = 1
ORDER BY B.flg_bu DESC, B.flg_sub_bu ASC, B.flg_branch ASC, A.ou_code ASC

--getAuthorizedSalesmanList
SELECT A.partner_id, A.tenant_id, A.partner_code, A.partner_name, A.active, A.version
FROM m_partner A
INNER JOIN m_partner_type B ON A.partner_id = B.partner_id
INNER JOIN m_type_partner C ON B.type_partner_id = C.type_partner_id
INNER JOIN m_partner_ou D ON A.partner_id = D.partner_id
WHERE C.type_partner_code='SLS'
    AND A.tenant_id = 10
    AND B.group_partner = 'E'
    AND D.ou_id = 10
    AND f_authorize_user_role_policy_salesman(10, 15, 65, A.partner_id) = 1
ORDER BY a.partner_name ASC

--getCurrentSalesAmountByMonth
SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
    SUM(
    CASE WHEN A.curr_code <> 'IDR' THEN
        CASE WHEN A.ref_doc_type_id = 311 THEN
            A.item_amount * f_commercial_rate(10, A.ref_doc_date, A.curr_code, 'IDR')
        ELSE
            -1 * A.item_amount * f_commercial_rate(10, A.ref_doc_date, A.curr_code, 'IDR')
        END
    ELSE
        CASE WHEN A.ref_doc_type_id = 311 THEN
            A.item_amount
        ELSE
            -1 * A.item_amount
        END
    END
    ) AS sales_amount
FROM sl_so_balance_invoice A
INNER JOIN sl_so B ON A.so_id = B.so_id
WHERE A.ou_id = 10
    AND B.salesman_id = 2616
    AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609'
GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6)

--getCurrentSalesPercentageByMonth
WITH tt_salesman_sales AS (
    SELECT A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6) AS year_month,
        SUM(
            CASE WHEN A.curr_code <> 'IDR' THEN
                CASE WHEN A.ref_doc_type_id = 311 THEN
                    A.item_amount * f_commercial_rate(10, A.ref_doc_date, A.curr_code, 'IDR')
                ELSE
                    -1 * A.item_amount * f_commercial_rate(10, A.ref_doc_date, A.curr_code, 'IDR')
                END
            ELSE
                CASE WHEN A.ref_doc_type_id = 311 THEN
                    A.item_amount
                ELSE
                    -1 * A.item_amount
                END
            END
        ) AS sales_amount
    FROM sl_so_balance_invoice A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    WHERE A.ou_id = 10
        AND B.salesman_id = 2616
        AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609'
    GROUP BY A.ou_id, B.salesman_id, SUBSTRING(A.ref_doc_date, 1, 6)

)
SELECT B.ou_id, B.salesman_id, B.year_month, (B.sales_amount / A.target_amount) * 100
FROM m_target_salesman A
INNER JOIN tt_salesman_sales B ON A.ou_id = B.ou_id AND A.salesman_id = B.salesman_id AND A.year_month = B.year_month
WHERE A.ou_id = 10
    AND A.salesman_id = 2616
    AND A.year_month = '201609'