· 4 years ago · Sep 08, 2021, 11:52 AM
1-- || PERBAIKAN DATA UNTUK KASUS SI HASIL GENERATE DARI RE-SUBMIT GR YANG DIBATALKAN || --
2
3-- gunakan temp table dari file [20210906_update_data_so_with_multi_si_1.sql]
4
5SELECT * FROM temp_so_vs_multi_si_mf_83;
6
7SELECT * FROM temp_so_vs_si_vs_alloc_mf_83;
8
9DROP TABLE IF EXISTS temp_so_alloc_removed_gr_mf_83;
10-- init table temp
11CREATE TABLE IF NOT EXISTS temp_so_alloc_removed_gr_mf_83(
12 type CHARACTER varying,
13 so_id BIGINT NOT NULL,
14 ou_id CHARACTER varying,
15 doc_no CHARACTER varying(30),
16 doc_date CHARACTER varying(8),
17 so_counter BIGINT,
18 flg_removed CHARACTER varying(1),
19 do_id BIGINT,
20 do_no CHARACTER varying(30),
21 do_date CHARACTER varying(8),
22 invoice_id BIGINT,
23 invoice_no CHARACTER varying(30),
24 invoice_date CHARACTER varying(8),
25 allocation_id BIGINT,
26 allocation_no CHARACTER varying(30),
27 allocation_date CHARACTER varying(8)
28)WITH (
29 OIDS=FALSE
30);
31
32-- insert data so multi si multi alloc
33 INSERT INTO temp_so_alloc_removed_gr_mf_83(
34 type, so_id, ou_id, doc_no, doc_date,
35 so_counter, flg_removed,
36 invoice_id, invoice_no, invoice_date,
37 allocation_id, allocation_no, allocation_date
38 ) SELECT 'ALLOC', A.so_id, B.ou_id, A.doc_no, A.doc_date,
39 ROW_NUMBER() OVER (PARTITION BY A.so_id ORDER BY A.so_id, B.invoice_id ASC, E.allocation_ar_id ASC) AS so_counter,
40 'N', B.invoice_id, B.doc_no, B.doc_date,
41 E.allocation_ar_id, E.doc_no, E.doc_date
42 FROM temp_so_vs_multi_si_mf_83 A
43 INNER JOIN sl_invoice B ON A.so_id = B.ref_id
44 INNER JOIN fi_invoice_ar_balance C ON C.invoice_ar_id = B.invoice_id AND B.doc_type_id = C.doc_type_id
45 INNER JOIN fi_allocation_ar_invoice D ON D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.invoice_ar_balance_id
46 INNER JOIN fi_allocation_ar E ON E.allocation_ar_id = D.allocation_ar_id
47 WHERE A.flg_user_error = 'N';
48
49-- batalkan dokumen Alloc yang berlebih
50 SELECT fi_cancel_submit_alloc_cashbank_ar(
51 E.tenant_id, CONCAT('fix_data_mf_83 ', E.doc_no), 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
52 E.allocation_ar_id, 'batalkan dokumen Allocation yang salah', CONCAT(E.allocation_ar_id, '_', E.doc_no, '_1'))
53 FROM temp_so_alloc_removed_gr_mf_83 A
54 INNER JOIN fi_allocation_ar E ON A.allocation_id = E.allocation_ar_id
55 WHERE A.so_counter > 1 AND A.type = 'ALLOC';
56
57--| START HAPUS DATA ALLOCATION YANG DIBATALKAN |--
58 UPDATE fi_invoice_ar_balance X
59 SET flg_payment = 'N'
60 FROM vw_fi_allocation_ar_invoice_with_invoice_ar_balance A
61 INNER JOIN fi_allocation_ar B ON A.allocation_ar_id = B.allocation_ar_id
62 WHERE A.tenant_id = 10
63 AND A.ref_id = X.invoice_ar_balance_id
64 AND EXISTS (
65 SELECT 1
66 FROM temp_so_alloc_removed_gr_mf_83 V
67 WHERE V.allocation_id = B.allocation_ar_id
68 AND V.so_counter > 1
69 );
70
71 UPDATE fi_invoice_tax_ar_balance X
72 SET flg_payment = 'N'
73 FROM vw_fi_allocation_ar_invoice_with_invoice_ar_balance A
74 INNER JOIN fi_allocation_ar B ON A.allocation_ar_id = B.allocation_ar_id
75 WHERE A.tenant_id = 10
76 AND A.ref_id = X.invoice_tax_ar_balance_id
77 AND EXISTS (
78 SELECT 1
79 FROM temp_so_alloc_removed_gr_mf_83 V
80 WHERE V.allocation_id = B.allocation_ar_id
81 AND V.so_counter > 1
82 );
83
84 UPDATE fi_receipt_ar_balance A
85 SET flg_alloc = 'N', ref_alloc_id = -99
86 FROM fi_allocation_ar B
87 WHERE B.ref_id = A.receipt_ar_balance_id
88 AND EXISTS (
89 SELECT 1
90 FROM temp_so_alloc_removed_gr_mf_83 V
91 WHERE V.allocation_id = B.allocation_ar_id
92 AND V.so_counter > 1
93 );
94
95 DELETE FROM fi_allocation_ar_cost A
96 WHERE EXISTS (
97 SELECT 1
98 FROM temp_so_alloc_removed_gr_mf_83 V
99 WHERE V.allocation_id = A.allocation_ar_id
100 AND V.so_counter > 1
101 );
102
103 DELETE FROM fi_allocation_ar_invoice A
104 WHERE EXISTS (
105 SELECT 1
106 FROM temp_so_alloc_removed_gr_mf_83 V
107 WHERE V.allocation_id = A.allocation_ar_id
108 AND V.so_counter > 1
109 );
110
111 DELETE FROM fi_allocation_ar A
112 WHERE EXISTS (
113 SELECT 1
114 FROM temp_so_alloc_removed_gr_mf_83 V
115 WHERE V.allocation_id = A.allocation_ar_id
116 AND V.so_counter > 1
117 );
118
119 DELETE FROM awe_currdoc_status A
120 WHERE EXISTS (
121 SELECT 1
122 FROM temp_so_alloc_removed_gr_mf_83 V
123 WHERE V.allocation_no = A.doc_no
124 AND V.allocation_id = A.doc_id
125 AND V.so_counter > 1
126 ) AND A.scheme = 'DH01';
127
128--| END HAPUS DATA ALLOCATION YANG DIBATALKAN |--
129
130 UPDATE temp_so_alloc_removed_gr_mf_83 X
131 SET flg_removed = 'Y'
132 WHERE X.type = 'ALLOC' AND
133 X.so_counter > 1;
134
135-- get data SO - SI yang duplikat
136 INSERT INTO temp_so_alloc_removed_gr_mf_83(
137 type, so_id, ou_id, doc_no, doc_date,
138 so_counter, flg_removed,
139 invoice_id, invoice_no, invoice_date,
140 allocation_id, allocation_no, allocation_date
141 ) SELECT 'INVOICE', A.so_id, B.ou_id, A.doc_no, A.doc_date,
142 ROW_NUMBER() OVER (PARTITION BY A.so_id ORDER BY A.so_id, B.invoice_id ASC) AS so_counter, 'N',
143 B.invoice_id, B.doc_no AS invoice_no, B.doc_date AS invoice_date,
144 -99, '', ''
145 FROM temp_so_vs_multi_si_mf_83 A
146 INNER JOIN sl_invoice B ON A.so_id = B.ref_id
147 WHERE A.flg_user_error = 'N';
148
149-- update record SI yang akan dihapus
150 UPDATE temp_so_alloc_removed_gr_mf_83 X
151 SET flg_removed = 'Y'
152 WHERE X.type = 'INVOICE' AND
153 X.so_counter > 1;
154
155-- get data DO yang dobel
156 INSERT INTO temp_so_alloc_removed_gr_mf_83(
157 type, so_id, ou_id, doc_no, doc_date,
158 so_counter, flg_removed,
159 do_id, do_no, do_date)
160 SELECT 'DO', A.so_id, D.ou_id, A.doc_no, A.doc_date,
161 ROW_NUMBER() OVER (PARTITION BY A.so_id ORDER BY A.so_id, B.so_item_id ASC, D.do_id ASC) AS so_counter, 'N',
162 D.do_id, D.doc_no AS do_no, D.doc_date AS do_date
163 FROM temp_so_vs_multi_si_mf_83 A
164 INNER JOIN sl_so_item B ON A.so_id = B.so_id
165 INNER JOIN sl_do_item C ON B.so_item_id = C.ref_id
166 INNER JOIN sl_do D ON C.do_id = D.do_id
167 WHERE A.flg_user_error = 'N'
168 --ORDER BY A.so_id, B.so_item_id, D.do_id
169 AND NOT EXISTS (
170 SELECT 1
171 -- SELECT X.so_id, X.doc_no, X.so_counter, Y.*
172 FROM temp_so_alloc_removed_gr_mf_83 X
173 INNER JOIN sl_invoice_item Y ON X.invoice_id = Y.invoice_id
174 WHERE X.type = 'INVOICE' AND X.flg_removed = 'N'
175 AND Y.ref_id = D.do_id AND Y.ref_doc_type_id = D.doc_type_id AND Y.ref_item_id = C.do_item_id
176 );
177
178-- batalkan dokumen SI yang salah
179 SELECT sl_cancel_submit_sales_invoice(10, CONCAT('fix_data_mf_83 ', E.invoice_no), 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
180 E.invoice_id, 'batalkan dokumen Sales Invoice yang salah', CONCAT(E.invoice_id, '_', E.invoice_no, '_1'))
181 FROM temp_so_alloc_removed_gr_mf_83 E
182 WHERE E.type = 'INVOICE'
183 AND E.so_counter > 1;
184
185-- | START HAPUS DATA SI YANG DIBATALKAN | --
186 UPDATE sl_so_balance_invoice A
187 SET flg_invoice = 'N',
188 invoice_id = -99,
189 version = version + 1,
190 update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'),
191 update_user_id = 29
192 WHERE flg_invoice = 'I'
193 AND tenant_id = 10
194 AND EXISTS(
195 SELECT 1
196 FROM temp_so_alloc_removed_gr_mf_83 X
197 WHERE A.invoice_id = X.invoice_id
198 AND X.so_counter > 1 AND X.type = 'INVOICE'
199 );
200
201 UPDATE sl_so_balance_invoice_tax A
202 SET flg_invoice = 'N',
203 invoice_id = -99,
204 version = version + 1,
205 update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'),
206 update_user_id = 29
207 WHERE flg_invoice = 'I'
208 AND tenant_id = 10
209 AND EXISTS(
210 SELECT 1
211 FROM temp_so_alloc_removed_gr_mf_83 X
212 WHERE A.invoice_id = X.invoice_id
213 AND X.so_counter > 1 AND X.type = 'INVOICE'
214 );
215
216 DELETE FROM sl_invoice_tax A
217 WHERE EXISTS(
218 SELECT 1
219 FROM temp_so_alloc_removed_gr_mf_83 X
220 WHERE A.invoice_id = X.invoice_id
221 AND X.so_counter > 1 AND X.type = 'INVOICE'
222 );
223
224 DELETE FROM sl_invoice_cost A
225 WHERE EXISTS(
226 SELECT 1
227 FROM temp_so_alloc_removed_gr_mf_83 X
228 WHERE A.invoice_id = X.invoice_id
229 AND X.so_counter > 1 AND X.type = 'INVOICE'
230 );
231
232 DELETE FROM sl_invoice_item A
233 WHERE EXISTS(
234 SELECT 1
235 FROM temp_so_alloc_removed_gr_mf_83 X
236 WHERE A.invoice_id = X.invoice_id
237 AND X.so_counter > 1 AND X.type = 'INVOICE'
238 );
239
240 DELETE FROM sl_invoice A
241 WHERE EXISTS(
242 SELECT 1
243 FROM temp_so_alloc_removed_gr_mf_83 X
244 WHERE A.invoice_id = X.invoice_id
245 AND X.so_counter > 1 AND X.type = 'INVOICE'
246 );
247
248 DELETE FROM awe_currdoc_status A
249 WHERE EXISTS(
250 SELECT 1
251 FROM temp_so_alloc_removed_gr_mf_83 X
252 WHERE A.doc_id = X.invoice_id
253 AND A.doc_no = X.invoice_no
254 AND X.so_counter > 1 AND X.type = 'INVOICE'
255 );
256
257 DELETE FROM awe_historydoc A
258 WHERE A.scheme = 'FC01'
259 AND EXISTS(
260 SELECT 1
261 FROM temp_so_alloc_removed_gr_mf_83 X
262 WHERE A.doc_id = X.invoice_id
263 AND X.so_counter > 1 AND X.type = 'INVOICE'
264 );
265
266-- | END HAPUS DATA SI YANG DIBATALKAN | --
267
268-- batalkan dokumen DO yang duplikat
269 SELECT sl_cancel_submit_do(10, CONCAT('fix_data_mf_83 ', E.do_no), 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
270 E.do_id, 'batalkan dokumen Delivery Order yang salah', CONCAT(E.do_id, '_', E.do_no, '_1'))
271 FROM temp_so_alloc_removed_gr_mf_83 E
272 WHERE E.type = 'DO' AND E.flg_removed = 'N';
273
274-- | START HAPUS DATA DO YANG DIBATALKAN | --
275 UPDATE sl_so_balance_item A
276 SET qty_dlv = A.qty_dlv - (A.qty_so * B.qty_dlv_int / qty_so_int),
277 qty_dlv_int = A.qty_dlv_int - B.qty_dlv_int,
278 status_item = 'R'
279 FROM sl_do_item B
280 INNER JOIN sl_do C ON B.do_id = C.do_id
281 WHERE B.ref_id = A.so_item_id
282 AND EXISTS(
283 SELECT 1
284 FROM temp_so_alloc_removed_gr_mf_83 X
285 WHERE C.do_id = X.do_id
286 AND X.type = 'DO'
287 );
288
289 DELETE FROM sl_do_product A
290 WHERE EXISTS (
291 SELECT 1
292 FROM sl_do_item B
293 INNER JOIN sl_do C ON B.do_id = C.do_id
294 WHERE A.do_item_id = B.do_item_id
295 AND EXISTS (
296 SELECT 1
297 FROM temp_so_alloc_removed_gr_mf_83 X
298 WHERE C.do_id = X.do_id
299 AND X.type = 'DO'
300 )
301 );
302
303 DELETE FROM sl_do_item A
304 WHERE EXISTS (
305 SELECT 1
306 FROM sl_do B
307 WHERE A.do_id = B.do_id
308 AND EXISTS (
309 SELECT 1
310 FROM temp_so_alloc_removed_gr_mf_83 X
311 WHERE B.do_id = X.do_id
312 AND X.type = 'DO'
313 )
314 );
315
316 DELETE FROM sl_do B
317 WHERE EXISTS (
318 SELECT 1
319 FROM temp_so_alloc_removed_gr_mf_83 X
320 WHERE B.do_id = X.do_id
321 AND X.type = 'DO'
322 );
323
324 DELETE FROM awe_currdoc_status A
325 WHERE EXISTS(
326 SELECT 1
327 FROM temp_so_alloc_removed_gr_mf_83 X
328 WHERE A.doc_id = X.do_id
329 AND A.doc_no = X.do_no
330 AND X.type = 'DO'
331 );
332
333 DELETE FROM awe_historydoc A
334 WHERE A.scheme = 'FB01'
335 AND EXISTS(
336 SELECT 1
337 FROM temp_so_alloc_removed_gr_mf_83 X
338 WHERE A.doc_id = X.do_id
339 AND X.type = 'DO'
340 );
341
342-- | END HAPUS DATA DO YANG DIBATALKAN | --
343
344 UPDATE temp_so_alloc_removed_gr_mf_83 X
345 SET flg_removed = 'Y'
346 WHERE X.type = 'DO';
347
348-- hapus data sl_so_dropship_receive_goods_balance yang GR-nya sudah tidak ada
349 DELETE FROM sl_so_dropship_receive_goods_balance X
350 -- SELECT * FROM sl_so_dropship_receive_goods_balance X
351 WHERE EXISTS (
352 SELECT 1
353 FROM temp_so_vs_multi_si_mf_83 A
354 WHERE X.so_id = A.so_id
355 AND A.flg_user_error = 'N'
356 ) AND NOT EXISTS (
357 SELECT 1
358 FROM pu_receive_goods A
359 WHERE X.receive_goods_id = A.receive_goods_id
360 );
361
362-- update data GR yang yang nomor DO dan DO item-nya salah
363 UPDATE sl_so_dropship_receive_goods_balance X
364 SET do_id = A.do_id,
365 do_item_id = B.do_item_id,
366 do_product_id = COALESCE(C.do_product_id, -99)
367 FROM sl_do A
368 INNER JOIN sl_do_item B ON A.do_id = B.do_id
369 INNER JOIN sl_do_product C ON B.do_item_id = C.do_item_id AND B.line_no = C.line_no
370 WHERE B.ref_id = X.so_item_id
371 AND EXISTS (
372 SELECT 1
373 FROM temp_so_vs_multi_si_mf_83 E
374 WHERE E.so_id = X.so_id
375 AND A.ref_id = E.so_id
376 AND A.ref_doc_type_id = 301
377 AND E.flg_user_error = 'N'
378 );
379
380-- Perlu cek kembali data Alloc, SI, dan DO yang tersisa
381/*
382Summary proses update data:
3831. Batalkan dokumen Allocation yang berlebih (hasil generate dari submit ulang GR)
3842. Batalkan dokumen SI yang berlebih (hasil generate dari submit ulang GR)
3853. Batalkan DO yang terbentuk dari GR yang disubmit ulang
3864. Update data di sl_so_dropship_receive_goods_balance
387*/