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