· 8 years ago · Apr 24, 2018, 08:50 AM
1--Adrian, Mar 12, 2018
2--Untuk mendapatkan data parent salesman dalam Mapping Salesman Hierarchy
3
4CREATE OR REPLACE FUNCTION f_insert_leader_summary_for_report_achievment_vs_target(character varying, bigint, bigint, character varying, bigint)
5 RETURNS void AS
6$BODY$
7DECLARE
8 pSessionId ALIAS FOR $1;
9 pTenantId ALIAS FOR $2;
10 pSalesmanId ALIAS FOR $3;
11 pGroupBrand ALIAS FOR $4;
12 pMaxSalesmanLevel ALIAS FOR $5;
13
14 vSalesmanLevel bigint;
15 vFilterSalesman character varying;
16 vZero bigint;
17 vEmptyId bigint;
18
19BEGIN
20
21 vSalesmanLevel := -99;
22 vFilterSalesman := '';
23 vZero := 0;
24 vEmptyId := -99;
25
26 -- Ambil level salesman yang akan dicari atasannya
27 SELECT f_get_salesman_level(pSalesmanId) INTO vSalesmanLevel;
28
29 -- Tentukan filter berdasarkan salesman yang akan dicari atasannya
30 IF(vSalesmanLevel = 1) THEN
31 vFilterSalesman := ' AND A.bod_id = '||pSalesmanId||' ';
32 ELSIF (vSalesmanLevel = 2) THEN
33 vFilterSalesman := ' AND A.saso_id = '||pSalesmanId||' ';
34 ELSIF (vSalesmanLevel = 3) THEN
35 vFilterSalesman := ' AND A.sales_manager_id = '||pSalesmanId||' ';
36 ELSIF (vSalesmanLevel = 4) THEN
37 vFilterSalesman := ' AND A.sales_supervisor_id = '||pSalesmanId||' ';
38 ELSIF (vSalesmanLevel = 5) THEN
39 vFilterSalesman := ' AND A.salesman_id = '||pSalesmanId||' ';
40 ELSIF (vSalesmanLevel = 6) THEN
41 vFilterSalesman := ' AND A.salesman_junior_id = '||pSalesmanId||' ';
42 END IF;
43
44 -- Ambil data atasan, set amount dan target menjadi 0
45 -- Ambil hanya sebelumnya belum ada pada table temporary untuk summary
46 -- Ambil hanya yang memiliki level >= level salesman dalam filter report
47 EXECUTE '
48 WITH data_detail AS (
49 SELECT A.salesman_junior_id AS id, A.tenant_id, A.group_brand,
50 CASE WHEN f_get_salesman_level(A.salesman_junior_id) <> $6 THEN f_get_salesman_level(A.salesman_junior_id) ELSE 6 END AS level
51 FROM tt_salesman_customer_info_for_report_achievement_and_target A
52 WHERE A.tenant_id = $1
53 AND A.session_id = $2
54 AND A.group_brand = $4 '
55 || vFilterSalesman || '
56
57 UNION ALL
58
59 SELECT A.salesman_id AS id, A.tenant_id, A.group_brand,
60 CASE WHEN f_get_salesman_level(A.salesman_id) <> $6 THEN f_get_salesman_level(A.salesman_id) ELSE 5 END AS level
61 FROM tt_salesman_customer_info_for_report_achievement_and_target A
62 WHERE A.tenant_id = $1
63 AND A.session_id = $2
64 AND A.group_brand = $4 '
65 || vFilterSalesman || '
66
67 UNION ALL
68
69 SELECT A.sales_supervisor_id AS id, A.tenant_id, A.group_brand,
70 CASE WHEN f_get_salesman_level(A.sales_supervisor_id) <> $6 THEN f_get_salesman_level(A.sales_supervisor_id) ELSE 4 END AS level
71 FROM tt_salesman_customer_info_for_report_achievement_and_target A
72 WHERE A.tenant_id = $1
73 AND A.session_id = $2
74 AND A.group_brand = $4 '
75 || vFilterSalesman || '
76
77 UNION ALL
78
79 SELECT A.sales_manager_id AS id, A.tenant_id, A.group_brand,
80 CASE WHEN f_get_salesman_level(A.sales_manager_id) <> $6 THEN f_get_salesman_level(A.sales_manager_id) ELSE 3 END AS level
81 FROM tt_salesman_customer_info_for_report_achievement_and_target A
82 WHERE A.tenant_id = $1
83 AND A.session_id = $2
84 AND A.group_brand = $4 '
85 || vFilterSalesman || '
86 )
87 INSERT INTO tt_summary_for_report_achievement_and_target (
88 session_id, tenant_id, salesman_id,
89 original_target_amount, target_amount, original_year_to_date_accumulative_amount, year_to_date_accumulative_amount,
90 salesman_level, group_brand_parent_code,
91 salesman_handle_id, salesman_handle_level
92 )
93 SELECT $2, A.tenant_id, A.id,
94 $5, $5, $5, $5,
95 A.level, A.group_brand,
96 $7, $3
97 FROM data_detail A
98 WHERE A.level < $3
99 AND A.level >= $8
100 AND NOT EXISTS(
101 SELECT 1
102 FROM tt_summary_for_report_achievement_and_target Z
103 WHERE Z.session_id = $2
104 AND Z.tenant_id = A.tenant_id
105 AND Z.salesman_id = A.id
106 AND Z.salesman_level = A.level
107 AND Z.group_brand_parent_code = A.group_brand
108 AND Z.salesman_handle_id = $7
109 AND Z.salesman_handle_level = $3
110 )
111 GROUP BY A.id, A.tenant_id, A.level, A.group_brand'
112 USING pTenantId, pSessionId, vSalesmanLevel, pGroupBrand, vZero, vEmptyId, pSalesmanId, pMaxSalesmanLevel;
113
114END;
115$BODY$
116 LANGUAGE plpgsql VOLATILE
117 COST 200;
118/