· 5 years ago · Oct 16, 2020, 03:52 AM
1/**
2 * DEPRECATED,
3 * Gunakan r_report_detail_commission()
4 */
5CREATE OR REPLACE FUNCTION rpt.r_report_commission(character varying, character varying, character varying, character varying, bigint, character varying, character varying, character varying, character varying)
6 RETURNS SETOF refcursor AS
7$BODY$
8DECLARE
9 pRefHeader REFCURSOR := 'refHeader';
10 pRefDetail REFCURSOR := 'refDetail';
11
12 pSessionId ALIAS FOR $1;
13 pUsername ALIAS FOR $2;
14 pDatetime ALIAS FOR $3;
15 pOutletAliasCode ALIAS FOR $4;
16 pSalesmanId ALIAS FOR $5;
17 pDateFrom ALIAS FOR $6;
18 pDateTo ALIAS FOR $7;
19 pProductCodeOrName ALIAS FOR $8;
20 pReportType ALIAS FOR $9; -- SERVICE / FG
21
22 vFinishGood character varying(2) := 'FG';
23 vService character varying(7) := 'SERVICE';
24 vRelease character varying(1) := 'R';
25 vEmptyString character varying(1) := '';
26
27 vEmptyId bigint := -99;
28
29 vSalesmanName character varying;
30 vOutlet character varying;
31
32 vFilterOutlet text;
33 vFilterSalesman text;
34 vFilterProduct text;
35 vFilterDateFrom text;
36 vFilterDateTo text;
37 vSql text;
38
39BEGIN
40
41 -- Clear temp table
42 DELETE FROM rpt.tt_report_commission WHERE session_id = pSessionId;
43
44 -- GET OUTLET code - name
45 SELECT A.ou_code_alias||' - '||A.ou_name_alias INTO vOutlet
46 FROM m_record_owner A
47 WHERE A.ou_code_alias = pOutletAliasCode
48 GROUP BY A.ou_code_alias, A.ou_name_alias;
49 IF NOT FOUND THEN
50 vOutlet := '-';
51 END IF;
52
53 -- GET Salesman Name
54 SELECT A.full_name INTO vSalesmanName
55 FROM m_salesman A
56 WHERE A.salesman_id = pSalesmanId;
57 IF NOT FOUND THEN
58 vSalesmanName := '-';
59 END IF;
60
61 -- GENERATE FILTER
62 IF (pOutletAliasCode = vEmptyString) THEN
63 vFilterOutlet := '';
64 ELSE
65 vFilterOutlet := ' AND F.ou_code_alias = '''||pOutletAliasCode||'''';
66 END IF;
67
68 IF (pSalesmanId = vEmptyId) THEN
69 vFilterSalesman := '';
70 ELSE
71 vFilterSalesman := ' AND G.salesman_id = '||pSalesmanId;
72 END IF;
73
74 IF (pProductCodeOrName = vEmptyString) THEN
75 vFilterProduct := '';
76 ELSE
77 vFilterProduct := ' AND (C.product_code ILIKE ''%'||pProductCodeOrName||'%'' OR C.product_name ILIKE ''%'||pProductCodeOrName||'%'')';
78 END IF;
79
80 IF (pDateFrom = vEmptyString) THEN
81 vFilterDateFrom := '';
82 ELSE
83 vFilterDateFrom := ' AND B.doc_date >= '''||pDateFrom||''' ';
84 END IF;
85
86 IF (pDateTo = vEmptyString) THEN
87 vFilterDateTo := '';
88 ELSE
89 vFilterDateTo := ' AND B.doc_date <= '''||pDateTo||''' ';
90 END IF;
91
92 -- REF HEADER
93 Open pRefHeader FOR
94 SELECT pUsername AS username, to_char(to_timestamp(pDatetime, 'YYYYMMDDHH24MISS'), 'FMDay, DD FMMon YYYY HH24:MI:SS') AS formatted_datetime,
95 pDatetime AS datetime, vOutlet AS filter_outlet, vSalesmanName AS filter_username, pDateFrom AS date_from, pDateTo AS date_to,
96 CASE WHEN (pDateFrom<>vEmptyString)
97 THEN to_char(to_date(pDateFrom, 'YYYYMMDD'), 'DD FMMon YYYY')
98 ELSE '-'
99 END AS formatted_date_from,
100 CASE WHEN (pDateTo<>vEmptyString) THEN
101 to_char(to_date(pDateTo, 'YYYYMMDD'), 'DD FMMon YYYY')
102 ELSE '-'
103 END AS formatted_date_to;
104 RETURN NEXT pRefHeader;
105
106 -- REF DETAIL
107 IF (pReportType = vService) THEN
108
109 -- INSERT ACC
110 EXECUTE '
111 INSERT INTO rpt.tt_report_commission(
112 session_id, pos_id, record_owner_id, record_owner_code, record_owner_name,
113 ou_code_alias, ou_name_alias,
114 salesman_id, short_name, full_name, role_default_id, role_default_name,
115 product_id, product_code, product_name, qty_acc_paid, qty_tindakan_1,
116 qty_tindakan_2, qty_tindakan_3)
117 SELECT $1, B.pos_id, B.record_owner_id, F.record_owner_code, F.ou_company,
118 F.ou_code_alias, F.ou_name_alias,
119 G.salesman_id, G.short_name, G.full_name, I.role_id, I.role_name,
120 C.product_id, C.product_code, C.product_name, A.qty AS qty_acc_paid, 0 AS qty_tindakan_1,
121 0 AS qty_tindakan_2, 0 AS qty_tindakan_3
122 FROM trx_pos_item A
123 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
124 INNER JOIN m_product C ON A.product_id = C.product_id
125 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
126 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
127 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
128 INNER JOIN m_salesman G ON A.salesman_id = G.salesman_id
129 INNER JOIN t_user H ON G.short_name = H.username
130 INNER JOIN t_role I ON H.role_default_id = I.role_id
131 WHERE E.group_product_code = $2
132 AND B.status_doc = $3 '
133 || vFilterOutlet
134 || vFilterSalesman
135 || vFilterProduct
136 || vFilterDateFrom
137 || vFilterDateTo
138 USING pSessionId, vService, vRelease;
139
140 -- UPDATE TINDAKAN 1
141 EXECUTE '
142 UPDATE rpt.tt_report_commission AA SET qty_tindakan_1 = AA.qty_tindakan_1 + A.qty
143 FROM trx_pos_item A
144 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
145 INNER JOIN m_product C ON A.product_id = C.product_id
146 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
147 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
148 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
149 INNER JOIN m_salesman G ON A.tindakan_by_1_salesman_id = G.salesman_id
150 INNER JOIN t_user H ON G.short_name = H.username
151 INNER JOIN t_role I ON H.role_default_id = I.role_id
152 WHERE AA.record_owner_id = B.record_owner_id
153 AND AA.pos_id = B.pos_id
154 AND AA.salesman_id = G.salesman_id
155 AND AA.role_default_id = I.role_id
156 AND AA.product_id = C.product_id
157 AND E.group_product_code = $2
158 AND B.status_doc = $3 '
159 || vFilterOutlet
160 || vFilterSalesman
161 || vFilterProduct
162 || vFilterDateFrom
163 || vFilterDateTo
164 USING pSessionId, vService, vRelease;
165
166 -- UPDATE TINDAKAN 2
167 EXECUTE '
168 UPDATE rpt.tt_report_commission AA SET qty_tindakan_2 = AA.qty_tindakan_2 + A.qty
169 FROM trx_pos_item A
170 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
171 INNER JOIN m_product C ON A.product_id = C.product_id
172 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
173 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
174 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
175 INNER JOIN m_salesman G ON A.tindakan_by_2_salesman_id = G.salesman_id
176 INNER JOIN t_user H ON G.short_name = H.username
177 INNER JOIN t_role I ON H.role_default_id = I.role_id
178 WHERE AA.record_owner_id = B.record_owner_id
179 AND AA.pos_id = B.pos_id
180 AND AA.salesman_id = G.salesman_id
181 AND AA.role_default_id = I.role_id
182 AND AA.product_id = C.product_id
183 AND E.group_product_code = $2
184 AND B.status_doc = $3 '
185 || vFilterOutlet
186 || vFilterSalesman
187 || vFilterProduct
188 || vFilterDateFrom
189 || vFilterDateTo
190 USING pSessionId, vService, vRelease;
191
192 -- UPDATE TINDAKAN 3
193 EXECUTE '
194 UPDATE rpt.tt_report_commission AA SET qty_tindakan_3 = AA.qty_tindakan_3 + A.qty
195 FROM trx_pos_item A
196 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
197 INNER JOIN m_product C ON A.product_id = C.product_id
198 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
199 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
200 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
201 INNER JOIN m_salesman G ON A.tindakan_by_3_salesman_id = G.salesman_id
202 INNER JOIN t_user H ON G.short_name = H.username
203 INNER JOIN t_role I ON H.role_default_id = I.role_id
204 WHERE AA.record_owner_id = B.record_owner_id
205 AND AA.pos_id = B.pos_id
206 AND AA.salesman_id = G.salesman_id
207 AND AA.role_default_id = I.role_id
208 AND AA.product_id = C.product_id
209 AND E.group_product_code = $2
210 AND B.status_doc = $3 '
211 || vFilterOutlet
212 || vFilterSalesman
213 || vFilterProduct
214 || vFilterDateFrom
215 || vFilterDateTo
216 USING pSessionId, vService, vRelease;
217
218 -- INSERT TINDAKAN 1
219 EXECUTE '
220 INSERT INTO rpt.tt_report_commission(
221 session_id, pos_id, record_owner_id, record_owner_code, record_owner_name,
222 ou_code_alias, ou_name_alias,
223 salesman_id, short_name, full_name, role_default_id, role_default_name,
224 product_id, product_code, product_name, qty_acc_paid, qty_tindakan_1,
225 qty_tindakan_2, qty_tindakan_3)
226 SELECT $1, B.pos_id, B.record_owner_id, F.record_owner_code, F.ou_company,
227 F.ou_code_alias, F.ou_name_alias,
228 G.salesman_id, G.short_name, G.full_name, I.role_id, I.role_name,
229 C.product_id, C.product_code, C.product_name, 0 AS qty_acc_paid, A.qty AS qty_tindakan_1,
230 0 AS qty_tindakan_2, 0 AS qty_tindakan_3
231 FROM trx_pos_item A
232 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
233 INNER JOIN m_product C ON A.product_id = C.product_id
234 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
235 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
236 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
237 INNER JOIN m_salesman G ON A.tindakan_by_1_salesman_id = G.salesman_id
238 INNER JOIN t_user H ON G.short_name = H.username
239 INNER JOIN t_role I ON H.role_default_id = I.role_id
240 WHERE E.group_product_code = $2
241 AND B.status_doc = $3
242 AND NOT EXISTS (
243 SELECT 1 FROM rpt.tt_report_commission Z
244 WHERE Z.session_id = $1
245 AND Z.pos_id = B.pos_id
246 AND Z.record_owner_id = B.record_owner_id
247 AND Z.salesman_id = G.salesman_id
248 AND Z.role_default_id = I.role_id
249 AND Z.product_id = C.product_id
250 )'
251 || vFilterOutlet
252 || vFilterSalesman
253 || vFilterProduct
254 || vFilterDateFrom
255 || vFilterDateTo
256 USING pSessionId, vService, vRelease;
257
258 -- INSERT TINDAKAN 2
259 EXECUTE '
260 INSERT INTO rpt.tt_report_commission(
261 session_id, pos_id, record_owner_id, record_owner_code, record_owner_name,
262 ou_code_alias, ou_name_alias,
263 salesman_id, short_name, full_name, role_default_id, role_default_name,
264 product_id, product_code, product_name, qty_acc_paid, qty_tindakan_1,
265 qty_tindakan_2, qty_tindakan_3)
266 SELECT $1, B.pos_id, B.record_owner_id, F.record_owner_code, F.ou_company,
267 F.ou_code_alias, F.ou_name_alias,
268 G.salesman_id, G.short_name, G.full_name, I.role_id, I.role_name,
269 C.product_id, C.product_code, C.product_name, 0 AS qty_acc_paid, 0 AS qty_tindakan_1,
270 A.qty AS qty_tindakan_2, 0 AS qty_tindakan_3
271 FROM trx_pos_item A
272 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
273 INNER JOIN m_product C ON A.product_id = C.product_id
274 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
275 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
276 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
277 INNER JOIN m_salesman G ON A.tindakan_by_2_salesman_id = G.salesman_id
278 INNER JOIN t_user H ON G.short_name = H.username
279 INNER JOIN t_role I ON H.role_default_id = I.role_id
280 WHERE E.group_product_code = $2
281 AND B.status_doc = $3
282 AND NOT EXISTS (
283 SELECT 1 FROM rpt.tt_report_commission Z
284 WHERE Z.session_id = $1
285 AND Z.pos_id = B.pos_id
286 AND Z.record_owner_id = B.record_owner_id
287 AND Z.salesman_id = G.salesman_id
288 AND Z.role_default_id = I.role_id
289 AND Z.product_id = C.product_id
290 )'
291 || vFilterOutlet
292 || vFilterSalesman
293 || vFilterProduct
294 || vFilterDateFrom
295 || vFilterDateTo
296 USING pSessionId, vService, vRelease;
297
298 -- INSERT TINDAKAN 3
299 EXECUTE '
300 INSERT INTO rpt.tt_report_commission(
301 session_id, pos_id, record_owner_id, record_owner_code, record_owner_name,
302 ou_code_alias, ou_name_alias,
303 salesman_id, short_name, full_name, role_default_id, role_default_name,
304 product_id, product_code, product_name, qty_acc_paid, qty_tindakan_1,
305 qty_tindakan_2, qty_tindakan_3)
306 SELECT $1, B.pos_id, B.record_owner_id, F.record_owner_code, F.ou_company,
307 F.ou_code_alias, F.ou_name_alias,
308 G.salesman_id, G.short_name, G.full_name, I.role_id, I.role_name,
309 C.product_id, C.product_code, C.product_name, 0 AS qty_acc_paid, 0 AS qty_tindakan_1,
310 0 AS qty_tindakan_2, A.qty AS qty_tindakan_3
311 FROM trx_pos_item A
312 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
313 INNER JOIN m_product C ON A.product_id = C.product_id
314 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
315 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
316 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
317 INNER JOIN m_salesman G ON A.tindakan_by_3_salesman_id = G.salesman_id
318 INNER JOIN t_user H ON G.short_name = H.username
319 INNER JOIN t_role I ON H.role_default_id = I.role_id
320 WHERE E.group_product_code = $2
321 AND B.status_doc = $3
322 AND NOT EXISTS (
323 SELECT 1 FROM rpt.tt_report_commission Z
324 WHERE Z.session_id = $1
325 AND Z.pos_id = B.pos_id
326 AND Z.record_owner_id = B.record_owner_id
327 AND Z.salesman_id = G.salesman_id
328 AND Z.role_default_id = I.role_id
329 AND Z.product_id = C.product_id
330 )'
331 || vFilterOutlet
332 || vFilterSalesman
333 || vFilterProduct
334 || vFilterDateFrom
335 || vFilterDateTo
336 USING pSessionId, vService, vRelease;
337
338 ELSIF (pReportType = vFinishGood) THEN
339 EXECUTE '
340 INSERT INTO rpt.tt_report_commission(
341 session_id, pos_id, record_owner_id, record_owner_code, record_owner_name,
342 ou_code_alias, ou_name_alias,
343 salesman_id, short_name, full_name, role_default_id, role_default_name,
344 product_id, product_code, product_name, qty_acc_paid, qty_tindakan_1,
345 qty_tindakan_2, qty_tindakan_3)
346 SELECT $1, B.pos_id, B.record_owner_id, F.record_owner_code, F.ou_company,
347 F.ou_code_alias, F.ou_name_alias,
348 G.salesman_id, G.short_name, G.full_name, I.role_id, I.role_name,
349 C.product_id, C.product_code, C.product_name, A.qty AS qty_acc_paid, 0 AS qty_tindakan_1,
350 0 AS qty_tindakan_2, 0 AS qty_tindakan_3
351 FROM trx_pos_item A
352 INNER JOIN trx_pos B ON A.pos_id = B.pos_id
353 INNER JOIN m_product C ON A.product_id = C.product_id
354 INNER JOIN m_ctgr_product D ON C.ctgr_product_id = D.ctgr_product_id
355 INNER JOIN m_group_product E ON D.group_product_id = E.group_product_id
356 INNER JOIN m_record_owner F ON B.record_owner_id = F.record_owner_id
357 INNER JOIN m_salesman G ON A.salesman_id = G.salesman_id
358 INNER JOIN t_user H ON G.short_name = H.username
359 INNER JOIN t_role I ON H.role_default_id = I.role_id
360 WHERE E.group_product_code = $2
361 AND B.status_doc = $3 '
362 || vFilterOutlet
363 || vFilterSalesman
364 || vFilterProduct
365 || vFilterDateFrom
366 || vFilterDateTo
367 USING pSessionId, vFinishGood, vRelease;
368 END IF;
369
370 OPEN pRefDetail FOR
371
372 SELECT A.ou_code_alias, A.ou_name_alias, A.short_name, A.full_name, A.role_default_name, A.product_code, A.product_name,
373 SUM(A.qty_acc_paid) AS qty_acc_paid, SUM(A.qty_tindakan_1) AS qty_tindakan_1, SUM(A.qty_tindakan_2) AS qty_tindakan_2,
374 SUM(A.qty_tindakan_3) AS qty_tindakan_3
375 FROM rpt.tt_report_commission A
376 WHERE session_id = pSessionId
377 GROUP BY A.ou_code_alias, A.ou_name_alias, A.short_name, A.full_name, A.role_default_name, A.product_code, A.product_name
378 ORDER BY A.ou_code_alias, A.ou_name_alias, A.short_name, A.full_name, A.role_default_name, A.product_code, A.product_name;
379
380 RETURN NEXT pRefDetail;
381
382 -- Clear temp table
383 DELETE FROM rpt.tt_report_commission WHERE session_id = pSessionId;
384
385END;
386$BODY$
387 LANGUAGE plpgsql VOLATILE
388 SET search_path = public, mstr, ptg, htg, pb, pj, batch
389 COST 100;
390 /