· 4 years ago · May 17, 2021, 03:46 AM
1-- 1. prepare temp table
2-- 1. temp table utama
3DROP TABLE IF EXISTS tr_data_omzet_toko_detail;
4CREATE TABLE tr_data_omzet_toko_detail
5(
6 ou_id bigint,
7 ou_code character varying(100),
8 partner_id bigint,
9 partner_code character varying(100),
10 product_id bigint,
11 product_code character varying(100),
12 product_group_brand character varying(100),
13 year_month character varying(6),
14 qty numeric,
15 gross_amount numeric,
16 nett_amount numeric,
17 tax_amount numeric
18)
19WITH (
20 OIDS=FALSE
21);
22
23-- 2. temp table sum per parent group brand
24DROP TABLE IF EXISTS tr_data_omzet_toko_parent_group_brand;
25CREATE TABLE tr_data_omzet_toko_parent_group_brand
26(
27 ou_id bigint,
28 partner_id bigint,
29 parent_group_brand character varying(100),
30 year_month character varying(6),
31 gross_amount numeric,
32 nett_amount numeric,
33 tax_amount numeric
34)
35WITH (
36 OIDS=FALSE
37);
38
39-- 3. temp table sum per parent group brand
40DROP TABLE IF EXISTS tr_data_omzet_toko_summary;
41CREATE TABLE tr_data_omzet_toko_summary
42(
43 ou_id bigint,
44 partner_id bigint,
45 parent_be character varying(100),
46 month_count_be numeric,
47 gross_amount_be numeric,
48 gross_amount_average_be numeric,
49
50 parent_a character varying(100),
51 month_count_a numeric,
52 gross_amount_a numeric,
53 gross_amount_average_a numeric,
54
55 parent_fx character varying(100),
56 month_count_fx numeric,
57 gross_amount_fx numeric,
58 gross_amount_average_fx numeric
59)
60WITH (
61 OIDS=FALSE
62);
63
64DELETE FROM tr_data_omzet_toko_detail;
65DELETE FROM tr_data_omzet_toko_parent_group_brand;
66DELETE FROM tr_data_omzet_toko_summary;
67
68-- 2. get data trx (berdasarkan stock date)
69-- 1. POS (non nempil)
70INSERT INTO tr_data_omzet_toko_detail(
71 ou_id, ou_code, partner_id, partner_code,
72 product_id, product_code, product_group_brand, year_month,
73 qty, gross_amount, nett_amount, tax_amount
74) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
75 G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
76 F.qty, F.qty * F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item AS nett_item_amount, F.tax_amount
77FROM i_trx_pos A
78 INNER JOIN t_ou B on A.ou_id = B.ou_id
79 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
80 INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
81 INNER JOIN m_product G on F.product_id = G.product_id
82 LEFT JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
83 LEFT JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
84 WHERE A.tenant_id = 10
85 --AND A.ou_id = 15
86 AND A.doc_date BETWEEN '20201001' AND '20210430';
87
88-- 2. POS Void (non nempil)
89INSERT INTO tr_data_omzet_toko_detail(
90 ou_id, ou_code, partner_id, partner_code,
91 product_id, product_code, product_group_brand, year_month,
92 qty, gross_amount, nett_amount, tax_amount
93) SELECT B.ou_id, B.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
94 G.product_id, G.product_code AS product_code, Q.group_brand_code, SUBSTRING(E.doc_date, 1, 6) AS year_month,
95 (F.qty)*-1, (F.qty*-1)* F.gross_sell_price AS omzet_bruto_amount, F.nett_amount_item*(-1)AS nett_item_amount, F.tax_amount*(-1)
96FROM i_trx_pos A
97 INNER JOIN t_ou B on A.ou_id = B.ou_id
98 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
99 INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
100 INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
101 INNER JOIN m_product G on F.product_id = G.product_id
102 INNER JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
103 INNER JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
104 WHERE A.status = 'V'
105 AND A.tenant_id = 10
106 AND E.doc_date BETWEEN '20201001' AND '20210430';
107
108-- 3. Penjualan (dari DO)
109INSERT INTO tr_data_omzet_toko_detail(
110 ou_id, ou_code, partner_id, partner_code,
111 product_id, product_code, product_group_brand, year_month,
112 qty, gross_amount, nett_amount, tax_amount
113) SELECT B.ou_id, B.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
114 E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
115 SUM(J.qty_dlv_so) AS qty,
116 SUM((J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) + I.tax_amount) AS omzet_bruto_amount,
117 SUM(J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) AS nett_item_amount,
118 SUM(I.tax_amount) AS tax_amount
119FROM sl_do A
120 INNER JOIN t_ou B ON A.ou_id = B.ou_id
121 INNER JOIN sl_do_item C ON A.do_id = C.do_id
122 INNER JOIN m_product E ON C.product_id = E.product_id
123 INNER JOIN sl_so_item H ON C.ref_id = H.so_item_id
124 INNER JOIN sl_so_balance_invoice J ON J.ref_doc_type_id = A.doc_type_id
125 AND J.ref_id = A.do_id
126 AND J.ref_item_id = C.do_item_id
127 AND J.do_receipt_item_id = -99
128 INNER JOIN sl_so_balance_invoice_tax I ON I.ref_doc_type_id = A.doc_type_id
129 AND I.ref_id = A.do_id
130 AND I.ref_item_id = C.do_item_id
131 AND I.do_receipt_item_id = -99
132 INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
133 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
134 INNER JOIN sl_so Q ON J.so_id = Q.so_id
135 WHERE A.tenant_id = 10
136 AND A.status_doc = 'R'
137 AND A.doc_date BETWEEN '20201001' AND '20210430'
138 AND A.doc_type_id = 311 -- docType DO
139 GROUP BY B.ou_id, B.ou_code, A.doc_date, Q.partner_id, E.product_id, E.product_code, P.group_brand_code
140 HAVING SUM(J.qty_dlv_so) <> 0;
141
142-- 4. Retur (RN, RRS, DOR)
143INSERT INTO tr_data_omzet_toko_detail( -- RN
144 ou_id, ou_code, partner_id, partner_code,
145 product_id, product_code, product_group_brand, year_month,
146 qty, gross_amount, nett_amount, tax_amount
147) SELECT I.ou_id, I.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
148 E.product_id, E.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
149 SUM(C.qty_dlv_so)*(-1) AS qty,
150 SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount)*-1,
151 SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0)))*-1,
152 SUM(F.tax_amount)*-1 AS tax_amount
153FROM in_inventory A
154 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
155 INNER JOIN sl_so_balance_invoice C ON A.doc_type_id = C.ref_doc_type_id AND C.ref_id = A.inventory_id AND B.ref_item_id = C.ref_item_id
156 INNER JOIN m_product E ON B.product_id = E.product_id
157 INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
158 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
159 INNER JOIN sl_so Q ON C.so_id = Q.so_id
160 INNER JOIN sl_so_balance_invoice_tax F ON A.inventory_id = F.ref_id AND A.doc_type_id = F.ref_doc_type_id AND B.ref_item_id = F.ref_item_id
161 INNER JOIN sl_do_item G ON B.ref_item_id = G.do_item_id
162 INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
163 INNER JOIN t_ou I ON A.ou_from_id = I.ou_id
164 WHERE A.tenant_id = 10
165 AND A.status_doc = 'R'
166 AND A.doc_date BETWEEN '20201001' AND '20210430'
167 AND A.doc_type_id = 502 -- docType RN
168 GROUP BY I.ou_id, I.ou_code, A.partner_id, E.product_id, E.product_code, P.group_brand_code, A.doc_date;
169
170INSERT INTO tr_data_omzet_toko_detail( -- RN from RRS
171 ou_id, ou_code, partner_id, partner_code,
172 product_id, product_code, product_group_brand, year_month,
173 qty, gross_amount, nett_amount, tax_amount
174) SELECT X.ou_id, X.ou_code, A.partner_id, COALESCE(f_get_partner_code(A.partner_id), 'UMUM') AS partner_code,
175 D.product_id, D.product_code AS product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
176 (G.qty_dlv_so)*-1,
177 C.gross_sell_price * G.qty_dlv_so * (-1) AS omzet_bruto_amount,
178 G.item_amount * (-1) AS nett_item_amount,
179 ROUND(((C.tax_percentage / (100.0 + C.tax_percentage)) * C.gross_sell_price) * G.qty_dlv_so * (-1)) AS tax_amount
180FROM in_inventory A
181 INNER JOIN sl_so_balance_invoice G ON a.tenant_id = G.tenant_id
182 AND G.ou_id = A.ou_from_id
183 AND G.ref_doc_type_id = 502
184 AND G.ref_id = A.inventory_id
185 INNER JOIN sl_so_balance_invoice_ext_for_return_note B ON G.so_balance_invoice_id = B.so_balance_invoice_id
186 AND B.flg_from_rrs = 'Y'
187 INNER JOIN sl_request_return_sales_item C ON a.tenant_id = C.tenant_id
188 AND B.request_return_sales_item_id = C.request_return_sales_item_id
189 INNER JOIN m_product D ON A.tenant_id = D.tenant_id
190 AND C.product_id = D.product_id
191 INNER JOIN t_ou X ON X.ou_id = A.ou_from_id
192 INNER JOIN m_product_custom_for_sasa O ON D.product_id = O.product_id
193 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
194 LEFT OUTER JOIN sl_invoice H ON H.invoice_id = G.invoice_id AND G.flg_invoice = 'Y'
195WHERE A.tenant_id = 10
196AND A.status_doc = 'R'
197AND A.doc_date BETWEEN '20201001' AND '20210430'
198AND A.ref_doc_type_id= 381 -- RRS
199AND A.doc_type_id = 502; -- RN
200
201INSERT INTO tr_data_omzet_toko_detail( -- DO Receipt
202 ou_id, ou_code, partner_id, partner_code,
203 product_id, product_code, product_group_brand, year_month,
204 qty, gross_amount, nett_amount, tax_amount
205) SELECT I.ou_id, I.ou_code, Q.partner_id, COALESCE(f_get_partner_code(Q.partner_id), 'UMUM') AS partner_code,
206E.product_id, E.product_code, P.group_brand_code, SUBSTRING(A.doc_date, 1, 6) AS year_month,
207SUM(C.qty_dlv_so) AS qty,
208SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount) AS omzet_bruto_amount,
209SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) AS nett_item_amount,
210SUM(F.tax_amount) AS tax_amount
211FROM in_do_receipt A
212 INNER JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
213 INNER JOIN sl_do_item G ON B.ref_id = G.do_item_id AND G.do_id = A.ref_id
214 INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
215 INNER JOIN sl_so_balance_invoice C ON B.ref_doc_type_id = C.ref_doc_type_id
216 AND C.ref_id = A.ref_id
217 AND B.ref_id = C.ref_item_id
218 AND C.do_receipt_item_id = B.do_receipt_item_id
219 INNER JOIN m_product E ON B.product_id = E.product_id
220 INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
221 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
222 INNER JOIN sl_so Q ON C.so_id = Q.so_id
223 INNER JOIN sl_so_balance_invoice_tax F ON B.ref_doc_type_id = F.ref_doc_type_id
224 AND F.ref_id = A.ref_id
225 AND B.ref_id = F.ref_item_id
226 AND F.do_receipt_item_id = B.do_receipt_item_id
227 INNER JOIN t_ou I ON A.ou_id = I.ou_id
228 WHERE A.tenant_id = 10
229 AND A.status_doc = 'R'
230 AND A.doc_date BETWEEN '20201001' AND '20210430'
231 AND A.doc_type_id = 526 -- docType DOR
232 GROUP BY I.ou_id, I.ou_code, A.doc_date, Q.partner_id,E.product_id, E.product_code, P.group_brand_code;
233
234-- 3. kelompokkan berdasarkan parent group brand, masukkan ke tr_data_omzet_toko_parent_group_brand
235 --insert data BE
236 INSERT INTO tr_data_omzet_toko_parent_group_brand(
237 ou_id, partner_id, parent_group_brand, year_month,
238 gross_amount, nett_amount, tax_amount
239 )
240 SELECT A.ou_id, A.partner_id, B.parent_group_brand, A.year_month,
241 SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
242 FROM tr_data_omzet_toko_detail A
243 INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
244 WHERE parent_group_brand = 'BE'
245 AND group_brand_code <> 'FXR'
246 GROUP BY A.ou_id, A.partner_id, B.parent_group_brand, A.year_month
247 ORDER BY ou_id, partner_id, year_month;
248
249 -- insert data barang A
250 INSERT INTO tr_data_omzet_toko_parent_group_brand(
251 ou_id, partner_id, parent_group_brand, year_month,
252 gross_amount, nett_amount, tax_amount
253 )
254 SELECT A.ou_id, A.partner_id, 'BarangA' AS type, A.year_month,
255 SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
256 FROM tr_data_omzet_toko_detail A
257 INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
258 WHERE group_brand_code IN ('FA2', 'FA4', 'FAB')
259 GROUP BY A.ou_id, A.partner_id, A.year_month;
260
261 -- insert data omzet FX
262 INSERT INTO tr_data_omzet_toko_parent_group_brand(
263 ou_id, partner_id, parent_group_brand, year_month,
264 gross_amount, nett_amount, tax_amount
265 )
266 SELECT A.ou_id, A.partner_id, 'BarangFX' AS type, A.year_month,
267 SUM(A.gross_amount), SUM(A.nett_amount), SUM(A.tax_amount)
268 FROM tr_data_omzet_toko_detail A
269 INNER JOIN m_group_brand B ON A.product_group_brand = B.group_brand_code
270 WHERE parent_group_brand NOT IN ('BE', 'NONBE')
271 AND group_brand_code <> 'FAB'
272 GROUP BY A.ou_id, A.partner_id, A.year_month;
273
274-- 4. dari tr_data_omzet_toko_parent_group_brand, hitung jumlah bulan untuk tiap group brand, masukkan ke tr_data_omzet_toko_summary
275-- insert data BE dulu
276 INSERT INTO tr_data_omzet_toko_summary(
277 ou_id, partner_id,
278 parent_be, month_count_be, gross_amount_be, gross_amount_average_be,
279 parent_a, month_count_a, gross_amount_a, gross_amount_average_a,
280 parent_fx, month_count_fx, gross_amount_fx, gross_amount_average_fx
281 )
282 SELECT ou_id, partner_id,
283 parent_group_brand, COUNT(year_month) AS month_trx, SUM(gross_amount), 0,
284 '', 0, 0, 0,
285 '', 0, 0, 0
286 FROM tr_data_omzet_toko_parent_group_brand
287 WHERE parent_group_brand = 'BE'
288 GROUP BY ou_id, partner_id, parent_group_brand;
289
290--update, masukkan data Barang A
291 WITH data_barang_a AS (
292 SELECT A.ou_id, A.partner_id, A.parent_group_brand, COUNT(A.year_month) AS month_trx,
293 SUM(A.gross_amount) AS gross_amount
294 FROM tr_data_omzet_toko_parent_group_brand A
295 WHERE A.parent_group_brand = 'BarangA'
296 GROUP BY A.ou_id, A.partner_id, A.parent_group_brand
297 )
298 UPDATE tr_data_omzet_toko_summary A
299 SET parent_a = B.parent_group_brand, month_count_a = B.month_trx,
300 gross_amount_a = B.gross_amount, gross_amount_average_a = 0
301 FROM data_barang_a B
302 WHERE B.parent_group_brand = 'BarangA'
303 AND B.ou_id = A.ou_id
304 AND A.partner_id = B.partner_id;
305
306 INSERT INTO tr_data_omzet_toko_summary(
307 ou_id, partner_id,
308 parent_be, month_count_be, gross_amount_be, gross_amount_average_be,
309 parent_a, month_count_a, gross_amount_a, gross_amount_average_a,
310 parent_fx, month_count_fx, gross_amount_fx, gross_amount_average_fx
311 )
312 SELECT A.ou_id, A.partner_id,
313 '', 0, 0, 0,
314 A.parent_group_brand, COUNT(A.year_month) AS month_trx, SUM(A.gross_amount), 0,
315 '', 0, 0, 0
316 FROM tr_data_omzet_toko_parent_group_brand A
317 WHERE parent_group_brand = 'BarangA'
318 AND NOT EXISTS (
319 SELECT 1
320 FROM tr_data_omzet_toko_summary X
321 WHERE X.ou_id = A.ou_id AND A.partner_id = X.partner_id
322 )
323 GROUP BY ou_id, partner_id, parent_group_brand;
324
325--update, masukkan data Barang FX
326 WITH data_barang_a AS (
327 SELECT A.ou_id, A.partner_id, A.parent_group_brand, COUNT(A.year_month) AS month_trx,
328 SUM(A.gross_amount) AS gross_amount
329 FROM tr_data_omzet_toko_parent_group_brand A
330 WHERE A.parent_group_brand = 'BarangFX'
331 GROUP BY A.ou_id, A.partner_id, A.parent_group_brand
332 )
333 UPDATE tr_data_omzet_toko_summary A
334 SET parent_fx = B.parent_group_brand, month_count_fx = month_trx,
335 gross_amount_fx = B.gross_amount, gross_amount_average_fx = 0
336 FROM data_barang_a B
337 WHERE B.parent_group_brand = 'BarangFX'
338 AND B.ou_id = A.ou_id
339 AND A.partner_id = B.partner_id;
340
341 INSERT INTO tr_data_omzet_toko_summary(
342 ou_id, partner_id,
343 parent_be, month_count_be, gross_amount_be, gross_amount_average_be,
344 parent_a, month_count_a, gross_amount_a, gross_amount_average_a,
345 parent_fx, month_count_fx, gross_amount_fx, gross_amount_average_fx
346 )
347 SELECT A.ou_id, A.partner_id,
348 '', 0, 0, 0,
349 '', 0, 0, 0,
350 A.parent_group_brand, COUNT(A.year_month) AS month_trx, SUM(A.gross_amount), 0
351 FROM tr_data_omzet_toko_parent_group_brand A
352 WHERE parent_group_brand = 'BarangFX'
353 AND NOT EXISTS (
354 SELECT 1
355 FROM tr_data_omzet_toko_summary X
356 WHERE X.ou_id = A.ou_id AND A.partner_id = X.partner_id
357 )
358 GROUP BY ou_id, partner_id, parent_group_brand;
359
360-- select terakhir
361SELECT B.ou_code, C.partner_code, C.partner_name, D.address1, D.address2, D.state_or_province, D.city, D.address3 AS kecamatan, C.price_level,
362parent_be, month_count_be, gross_amount_be, CASE WHEN month_count_be <> 0 THEN ROUND(gross_amount_be/month_count_be) ELSE 0 END AS average_be,
363parent_a, month_count_a, gross_amount_a, CASE WHEN month_count_a <> 0 THEN ROUND(gross_amount_a/month_count_a) ELSE 0 END AS average_a,
364parent_fx, month_count_fx, gross_amount_fx, CASE WHEN month_count_fx <> 0 THEN ROUND(gross_amount_fx/month_count_fx) ELSE 0 END AS average_fx
365-- SELECT *
366FROM tr_data_omzet_toko_summary A
367INNER JOIN t_ou B ON A.ou_id = B.ou_id
368INNER JOIN m_partner C ON A.partner_id = C.partner_id
369INNER JOIN m_partner_address D ON D.partner_id = C.partner_id AND flg_official = 'Y'
370ORDER BY B.ou_code, C.partner_code;