Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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 --tenantId
- AND B.flg_bu = 'Y'
- AND f_authorize_user_role_policy_ou(10 /*(tenantId)*/, 15 /*(userId)*/, 65 /*(roleId)*/, 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 --tenantId
- AND B.group_partner = 'E'
- AND D.ou_id = 10 --ouId
- AND f_authorize_user_role_policy_salesman(10 /*(tenantId)*/, 15 /*(userId)*/, 65 /*(roleId)*/, 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 IN (311, 312) THEN
- A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
- ELSE
- -1 * A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
- END
- ELSE
- CASE WHEN A.ref_doc_type_id IN (311, 312) 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 --ouId
- AND B.salesman_id = 2616 --salesmanId
- AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
- 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 IN (311, 312) THEN
- A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
- ELSE
- -1 * A.item_amount * f_commercial_rate(10 /*(tenantId)*/, A.ref_doc_date, A.curr_code, 'IDR')
- END
- ELSE
- CASE WHEN A.ref_doc_type_id IN (311, 312) 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 --tenantId
- AND B.salesman_id = 2616 --salesmanId
- AND SUBSTRING(A.ref_doc_date, 1, 6) = '201609' --yearMonth
- 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 --tenantId
- AND A.salesman_id = 2616 --salesmanId
- AND A.year_month = '201609' --yearMonth
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement