· 4 years ago · Sep 06, 2021, 12:14 PM
1-- Create temp table
2DROP TABLE IF EXISTS temp_so_vs_multi_si_mf_83;
3-- init table temp
4CREATE TABLE IF NOT EXISTS temp_so_vs_multi_si_mf_83(
5 so_id BIGINT NOT NULL,
6 ou CHARACTER varying,
7 doc_no CHARACTER varying(30),
8 doc_date CHARACTER varying(8),
9 flg_user_error CHARACTER varying(1)
10)WITH (
11 OIDS=FALSE
12);
13
14DROP TABLE IF EXISTS temp_so_vs_si_vs_alloc_mf_83;
15-- init table temp
16CREATE TABLE IF NOT EXISTS temp_so_vs_si_vs_alloc_mf_83(
17 so_id BIGINT NOT NULL,
18 ou CHARACTER varying,
19 doc_no CHARACTER varying(30),
20 doc_date CHARACTER varying(8),
21 flg_user_error CHARACTER varying(1),
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 with multi SO
33WITH data_so_si AS (
34 WITH data_so_duplicate AS (
35 SELECT B.so_id, B.ou_id, B.doc_no, B.doc_date, B.partner_id, COUNT(B.so_id) AS counter
36 FROM sl_invoice A
37 INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
38 GROUP BY B.so_id, B.ou_id, B.doc_no, B.doc_date, B.partner_id
39 ) SELECT A.so_id, f_get_ou_name(a.ou_id) AS ou, A.doc_no, A.doc_date,
40 f_get_partner_name(A.partner_id) AS customer, COALESCE(B.flg_pkp, 'N') AS flg_pkp,
41 C.doc_no AS sales_invoice_no, C.doc_date AS sales_invoice_date, C.remark AS sales_invoice_remark,
42 C.update_datetime, C.update_user_id, f_get_username(C.update_user_id) AS last_update_user
43 FROM data_so_duplicate A
44 INNER JOIN sl_invoice C ON A.so_id = C.ref_id
45 LEFT JOIN m_partner_npwp B ON A.partner_id = B.partner_id
46 WHERE A.counter > 1
47 AND NOT EXISTS (
48 SELECT 1 FROM m_partner_npwp X
49 WHERE A.partner_id = X.partner_id --AND X.flg_pkp = 'Y'
50 )
51 --AND A.so_id NOT IN (51, 1078, 1372, 76)
52 ORDER BY A.doc_no, C.doc_no
53)
54INSERT INTO temp_so_vs_multi_si_mf_83(so_id, ou, doc_no, doc_date, flg_user_error)
55SELECT so_id, ou, doc_no, doc_date, CASE WHEN counter = 0 THEN 'N' ELSE 'Y' END
56FROM (
57 SELECT A.so_id, A.ou, A.doc_no, A.doc_date, SUM(CASE WHEN A.sales_invoice_remark NOT ILIKE '[AUTOMATIC GENERATED%' THEN 1 ELSE 0 END) AS counter
58 FROM data_so_si A
59 GROUP BY A.so_id, A.ou, A.doc_no, A.doc_date
60) A;
61
62-- get data allocation
63 INSERT INTO temp_so_vs_si_vs_alloc_mf_83(
64 so_id, ou, doc_no, doc_date, flg_user_error,
65 invoice_id, invoice_no, invoice_date,
66 allocation_id, allocation_no, allocation_date)
67 SELECT A.so_id, A.ou, A.doc_no, A.doc_date, A.flg_user_error,
68 B.invoice_id, B.doc_no, B.doc_date,
69 E.allocation_ar_id, E.doc_no, E.doc_date
70 FROM temp_so_vs_multi_si_mf_83 A
71 INNER JOIN sl_invoice B ON A.so_id = B.ref_id
72 INNER JOIN fi_invoice_ar_balance C ON C.invoice_ar_id = B.invoice_id AND B.doc_type_id = C.doc_type_id
73 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
74 INNER JOIN fi_allocation_ar E ON E.allocation_ar_id = D.allocation_ar_id
75 ORDER BY A.doc_no, B.doc_no;
76
77
78-- batalkan semua allocation yang salah karena SI dibuat oleh user
79 SELECT fi_cancel_submit_alloc_cashbank_ar(
80 E.tenant_id, CONCAT('fix_data_mf_83 ', E.doc_no), 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
81 E.allocation_ar_id, 'batalkan dokumen Allocation yang salah', CONCAT(E.allocation_ar_id, '_', E.doc_no, '_1'))
82 FROM temp_so_vs_si_vs_alloc_mf_83 A
83 INNER JOIN fi_allocation_ar E ON A.allocation_id = E.allocation_ar_id
84 WHERE A.flg_user_error = 'Y';
85
86--| START HAPUS DATA ALLOCATION YANG DIBATALKAN |--
87
88 UPDATE fi_invoice_ar_balance X
89 SET flg_payment = 'N'
90 FROM vw_fi_allocation_ar_invoice_with_invoice_ar_balance A
91 INNER JOIN fi_allocation_ar B ON A.allocation_ar_id = B.allocation_ar_id
92 WHERE A.tenant_id = 10
93 AND A.ref_id = X.invoice_ar_balance_id
94 AND EXISTS (
95 SELECT 1
96 FROM temp_so_vs_si_vs_alloc_mf_83 V
97 WHERE V.allocation_id = B.allocation_ar_id
98 AND V.flg_user_error = 'Y'
99 );
100
101 UPDATE fi_invoice_tax_ar_balance X
102 SET flg_payment = 'N'
103 FROM vw_fi_allocation_ar_invoice_with_invoice_ar_balance A
104 INNER JOIN fi_allocation_ar B ON A.allocation_ar_id = B.allocation_ar_id
105 WHERE A.tenant_id = 10
106 AND A.ref_id = X.invoice_tax_ar_balance_id
107 AND EXISTS (
108 SELECT 1
109 FROM temp_so_vs_si_vs_alloc_mf_83 V
110 WHERE V.allocation_id = B.allocation_ar_id
111 AND V.flg_user_error = 'Y'
112 );
113
114 UPDATE fi_receipt_ar_balance A
115 SET flg_alloc = 'N', ref_alloc_id = -99
116 FROM fi_allocation_ar B
117 WHERE B.ref_id = A.receipt_ar_balance_id
118 AND EXISTS (
119 SELECT 1
120 FROM temp_so_vs_si_vs_alloc_mf_83 V
121 WHERE V.allocation_id = B.allocation_ar_id
122 AND V.flg_user_error = 'Y'
123 );
124
125 DELETE FROM fi_allocation_ar_cost A
126 WHERE EXISTS (
127 SELECT 1
128 FROM temp_so_vs_si_vs_alloc_mf_83 V
129 WHERE V.allocation_id = A.allocation_ar_id
130 AND V.flg_user_error = 'Y'
131 );
132
133 DELETE FROM fi_allocation_ar_invoice A
134 WHERE EXISTS (
135 SELECT 1
136 FROM temp_so_vs_si_vs_alloc_mf_83 V
137 WHERE V.allocation_id = A.allocation_ar_id
138 AND V.flg_user_error = 'Y'
139 );
140
141 DELETE FROM fi_allocation_ar A
142 WHERE EXISTS (
143 SELECT 1
144 FROM temp_so_vs_si_vs_alloc_mf_83 V
145 WHERE V.allocation_id = A.allocation_ar_id
146 AND V.flg_user_error = 'Y'
147 );
148
149 DELETE FROM awe_currdoc_status A
150 WHERE EXISTS (
151 SELECT 1
152 FROM temp_so_vs_si_vs_alloc_mf_83 V
153 WHERE V.allocation_no = A.doc_no
154 AND V.allocation_id = A.doc_id
155 AND V.flg_user_error = 'Y'
156 ) AND A.scheme = 'DH01';
157
158--| END HAPUS DATA ALLOCATION YANG DIBATALKAN |--
159
160-- update flg_user_error untuk SI yang benar
161 UPDATE temp_so_vs_si_vs_alloc_mf_83 A
162 SET flg_user_error = 'F'
163 WHERE A.flg_user_error = 'Y'
164 AND A.allocation_no NOT ILIKE 'ACBIAR-%';
165 --> Kenapa pakai kondisi ini: dokumen Allocation yang salah, mengambil data autonum dari trigger approved SI
166 --> Ada typo pada trigger Approved GR, di mana autonum untuk Allocation generate dgn schema SI, sehingga docNo yang terbentuk serupa dengan nomor SI
167 --> Dengan menggunakan patokan ini, maka Allocation dari SI yang dibuat manual oleh User dapat dibedakan
168
169-- batalkan dokumen SI yang dibuat oleh user (yang salah)
170 SELECT sl_cancel_submit_sales_invoice(10, CONCAT('fix_data_mf_83 ', E.invoice_no), 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
171 E.invoice_id, 'batalkan dokumen Sales Invoice yang salah', CONCAT(E.invoice_id, '_', E.invoice_no, '_1'))
172 FROM temp_so_vs_si_vs_alloc_mf_83 E
173 WHERE E.flg_user_error = 'Y';
174
175-- | START HAPUS DATA SI YANG DIBATALKAN | --
176 UPDATE sl_so_balance_invoice A
177 SET flg_invoice = 'N',
178 invoice_id = -99,
179 version = version + 1,
180 update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'),
181 update_user_id = 29
182 WHERE flg_invoice = 'I'
183 AND tenant_id = 10
184 AND EXISTS(
185 SELECT 1
186 FROM temp_so_vs_si_vs_alloc_mf_83 X
187 WHERE A.invoice_id = X.invoice_id
188 AND X.flg_user_error = 'Y'
189 );
190
191 UPDATE sl_so_balance_invoice_tax A
192 SET flg_invoice = 'N',
193 invoice_id = -99,
194 version = version + 1,
195 update_datetime = to_char(NOW(), 'YYYYMMDDHH24MIss'),
196 update_user_id = 29
197 WHERE flg_invoice = 'I'
198 AND tenant_id = 10
199 AND EXISTS(
200 SELECT 1
201 FROM temp_so_vs_si_vs_alloc_mf_83 X
202 WHERE A.invoice_id = X.invoice_id
203 AND X.flg_user_error = 'Y'
204 );
205
206 DELETE FROM sl_invoice_tax A
207 WHERE EXISTS(
208 SELECT 1
209 FROM temp_so_vs_si_vs_alloc_mf_83 X
210 WHERE A.invoice_id = X.invoice_id
211 AND X.flg_user_error = 'Y'
212 );
213
214 DELETE FROM sl_invoice_cost A
215 WHERE EXISTS(
216 SELECT 1
217 FROM temp_so_vs_si_vs_alloc_mf_83 X
218 WHERE A.invoice_id = X.invoice_id
219 AND X.flg_user_error = 'Y'
220 );
221
222 DELETE FROM sl_invoice_item A
223 WHERE EXISTS(
224 SELECT 1
225 FROM temp_so_vs_si_vs_alloc_mf_83 X
226 WHERE A.invoice_id = X.invoice_id
227 AND X.flg_user_error = 'Y'
228 );
229
230 DELETE FROM sl_invoice A
231 WHERE EXISTS(
232 SELECT 1
233 FROM temp_so_vs_si_vs_alloc_mf_83 X
234 WHERE A.invoice_id = X.invoice_id
235 AND X.flg_user_error = 'Y'
236 );
237
238 DELETE FROM awe_currdoc_status A
239 WHERE EXISTS(
240 SELECT 1
241 FROM temp_so_vs_si_vs_alloc_mf_83 X
242 WHERE A.doc_id = X.invoice_id
243 AND A.doc_no = X.invoice_no
244 AND X.flg_user_error = 'Y'
245 );
246
247 DELETE FROM awe_historydoc A
248 WHERE A.scheme = 'FC01'
249 AND EXISTS(
250 SELECT 1
251 FROM temp_so_vs_si_vs_alloc_mf_83 X
252 WHERE A.doc_id = X.invoice_id
253 AND X.flg_user_error = 'Y'
254 );
255
256-- | END HAPUS DATA SI YANG DIBATALKAN | --
257
258-- Generate ulang Allocation CB In dari SI yang benar
259
260 SELECT fi_automatic_alloc_cb_in(
261 CONCAT('fix_data_mf_83 ', A.allocation_no), 10, 29, to_char(NOW(), 'YYYYMMDDHH24MIss'),
262 A.allocation_id, A.allocation_no, B.mapping_cbin_so_balance_item_id, C.invoice_ar_balance_id
263 )
264 FROM temp_so_vs_si_vs_alloc_mf_83 A
265 INNER JOIN cb_mapping_cbin_so_balance_item B ON A.so_id = B.so_id
266 INNER JOIN fi_invoice_ar_balance C ON C.ref_id = A.so_id AND C.ref_doc_type_id = 301
267 WHERE A.flg_user_error = 'F'
268 AND EXISTS(
269 SELECT 1
270 FROM sl_invoice X WHERE A.so_id = X.ref_id AND X.invoice_id = C.invoice_ar_id
271 );