· 5 years ago · Jun 30, 2020, 06:52 AM
1CREATE OR REPLACE FUNCTION ul_upload_saldo_ar(bigint)
2 RETURNS bigint AS
3$BODY$
4DECLARE
5 pUploadHeaderId ALIAS FOR $1;
6
7 vFail character varying(4) := 'FAIL';
8 vOk character varying(2) := 'OK';
9 vYes character varying(1) := 'Y';
10 vNo character varying(1) := 'N';
11 vActionInsert character varying(1) := 'I';
12 vActionUpdate character varying(1) := 'U';
13 vActionError character varying(1) := 'E';
14 vEmpty character varying(1) := '';
15 vSpaceValue character varying(1) := ' ';
16 vDatetime character varying(14);
17 vStatusRelease character varying(1) := 'R';
18 vWorkflowApproved character varying(8) := 'APPROVED';
19
20 vTenantId bigint;
21 vUserId bigint;
22 vYearMonth character varying;
23 vActivityGlIdDebtNoteAr bigint;
24 vActivityGlIdCreditNoteAr bigint;
25 vCurrCode character varying;
26
27 vDocTypeDebtNoteAr bigint := 241;
28 vDocTypeCreditNoteAr bigint := 251;
29 vEmptyId bigint := -99;
30
31 vCountFail bigint := 0;
32
33BEGIN
34 -- siapkan parameter
35 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
36 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
37 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
38 vYearMonth = CAST( f_get_upload_parameter(pUploadHeaderId, 'yearMonth') AS character varying );
39 vActivityGlIdDebtNoteAr = CAST( f_get_upload_parameter(pUploadHeaderId, 'activityGlIdDebtNoteAr') AS bigint );
40 vActivityGlIdCreditNoteAr = CAST( f_get_upload_parameter(pUploadHeaderId, 'activityGlIdCreditNoteAr') AS bigint );
41
42 -- CHANGE EMPTY VALYE TO 0 FOR NUMERIC FIELD
43 -- MENENTUKAN DOC TYPE ID
44 UPDATE ul_import_data_saldo_ar A SET
45 gov_tax_amount = CASE WHEN TRIM(A.gov_tax_amount) = vEmpty THEN '0' ELSE A.gov_tax_amount END,
46 nett_amount = CASE WHEN TRIM(A.nett_amount) = vEmpty THEN '0' ELSE A.nett_amount END,
47 balance_nett_amount = CASE WHEN TRIM(A.balance_nett_amount) = vEmpty THEN '0' ELSE A.balance_nett_amount END,
48 balance_gov_tax_amount = CASE WHEN TRIM(A.balance_gov_tax_amount) = vEmpty THEN '0' ELSE A.balance_gov_tax_amount END,
49 doc_type_id = CASE WHEN is_numeric(A.nett_amount)
50 THEN
51 CASE WHEN A.nett_amount::numeric > 0
52 THEN vDocTypeDebtNoteAr
53 WHEN A.nett_amount::numeric < 0
54 THEN vDocTypeCreditNoteAr
55 ELSE vEmptyId
56 END
57 ELSE vEmptyId
58 END
59 WHERE A.upload_header_id = pUploadHeaderId;
60
61 /* validate doc date */
62 UPDATE ul_import_data_saldo_ar A
63 SET status = vFail,
64 message = message || 'Tanggal dokumen tidak valid, '
65 WHERE A.upload_header_id = pUploadHeaderId
66 AND NOT EXISTS (SELECT 1 FROM dt_date B
67 WHERE A.tanggal_dokumen = B.string_date);
68
69 /* validate due date */
70 UPDATE ul_import_data_saldo_ar A
71 SET status = vFail,
72 message = message || 'Tanggal jatuh tempo tidak valid, '
73 WHERE A.upload_header_id = pUploadHeaderId
74 AND NOT EXISTS (SELECT 1 FROM dt_date B
75 WHERE A.tanggal_jatuh_tempo = B.string_date);
76
77 /* validate tax date */
78 UPDATE ul_import_data_saldo_ar A
79 SET status = vFail,
80 message = message || 'Tanggal pajak tidak valid, '
81 WHERE A.upload_header_id = pUploadHeaderId
82 AND TRIM(A.tanggal_pajak) <> vEmpty
83 AND NOT EXISTS (SELECT 1 FROM dt_date B
84 WHERE A.tanggal_pajak = B.string_date);
85
86 /* ou harus terdaftar pada master */
87 UPDATE ul_import_data_saldo_ar A
88 SET status = vFail,
89 message = message || 'Kode OU tidak terdaftar, '
90 WHERE A.upload_header_id = pUploadHeaderId
91 AND NOT EXISTS (SELECT 1 FROM t_ou Z
92 WHERE A.kode_ou = Z.ou_code
93 AND Z.tenant_id = vTenantId);
94
95
96 /* ou terdaftar sbg bu / branch */
97 /* validasinya ditutup karna untuk AR harusnya outlet juga bisa masuk datanya
98 UPDATE ul_import_data_saldo_ar A
99 SET status = vFail,
100 message = message || 'Kode OU tidak terdaftar sebagai business unit atau branch, '
101 WHERE A.upload_header_id = pUploadHeaderId
102 AND NOT EXISTS (SELECT 1 FROM t_ou Z
103 INNER JOIN m_ou_structure Y ON Z.ou_id = Y.ou_id
104 WHERE A.kode_ou = Z.ou_code
105 AND Z.tenant_id = vTenantId
106 AND (Z.ou_id = Y.ou_bu_id OR Z.ou_id = Y.ou_branch_id));
107 */
108
109 /* validate doc no must be input */
110 UPDATE ul_import_data_saldo_ar A
111 SET status = vFail,
112 message = message || 'Nomor dokumen harus diisi (tidak boleh kosong), '
113 WHERE A.upload_header_id = pUploadHeaderId
114 AND TRIM(A.nomor_dokumen) = vEmpty;
115
116 /* partner tidak terdaftar sbg master */
117 UPDATE ul_import_data_saldo_ar A
118 SET status = vFail,
119 message = message || 'Kode pelanggan tidak terdaftar, '
120 WHERE A.upload_header_id = pUploadHeaderId
121 AND NOT EXISTS (SELECT 1 FROM m_partner Z
122 WHERE Z.tenant_id = vTenantId
123 AND Z.partner_code = A.kode_pelanggan);
124
125 /* partner tidak terdaftar sebagai customer */
126 UPDATE ul_import_data_saldo_ar A
127 SET status = vFail,
128 message = message || 'Kode pelanggan tidak terdaftar sebagai customer, '
129 WHERE A.upload_header_id = pUploadHeaderId
130 AND NOT EXISTS (SELECT 1 FROM m_partner Z
131 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
132 WHERE Z.tenant_id = vTenantId
133 AND Z.partner_code = A.kode_pelanggan
134 AND Y.group_partner = 'C');
135
136 /* validate due_date >= doc_date */
137 UPDATE ul_import_data_saldo_ar A
138 SET status = vFail,
139 message = message || 'Tanggal jatuh tempo harus lebih besar atau sama dengan tanggal dokumen, '
140 WHERE A.upload_header_id = pUploadHeaderId
141 AND A.tanggal_jatuh_tempo < A.tanggal_dokumen;
142
143 /**
144 * Validasi kolom berikut harus numeric jika ada isinya :
145 * gov_tax_amount
146 * nett_amount
147 * balance_nett_amount
148 * balance_gov_tax_amount
149 */
150 UPDATE ul_import_data_saldo_ar A
151 SET status = vFail,
152 message = message || 'Gov tax amount hanya boleh diisi dengan numeric, '
153 WHERE A.upload_header_id = pUploadHeaderId
154 AND TRIM(A.gov_tax_amount) <> vEmpty
155 AND NOT is_numeric(A.gov_tax_amount);
156
157 UPDATE ul_import_data_saldo_ar A
158 SET status = vFail,
159 message = message || 'Nett amount hanya boleh diisi dengan numeric, '
160 WHERE A.upload_header_id = pUploadHeaderId
161 AND TRIM(A.nett_amount) <> vEmpty
162 AND NOT is_numeric(A.nett_amount);
163
164 UPDATE ul_import_data_saldo_ar A
165 SET status = vFail,
166 message = message || 'Balance nett amount hanya boleh diisi dengan numeric, '
167 WHERE A.upload_header_id = pUploadHeaderId
168 AND TRIM(A.balance_nett_amount) <> vEmpty
169 AND NOT is_numeric(A.balance_nett_amount);
170
171 UPDATE ul_import_data_saldo_ar A
172 SET status = vFail,
173 message = message || 'Balance gov tax amount hanya boleh diisi dengan numeric, '
174 WHERE A.upload_header_id = pUploadHeaderId
175 AND TRIM(A.balance_gov_tax_amount) <> vEmpty
176 AND NOT is_numeric(A.balance_gov_tax_amount);
177
178 -- tax_no tidak boleh '' dan tidak boleh null
179 -- WTC, 20160128, nomor dan tanggal faktur pajak pada saldo awal, menjadi tidak mandatory, karena secara saldo sudah gabungkan antara DPP dan PPN
180 -- Namun, apabila salah satu dari nomor atau tanggal diisi, maka keduanya harus diisi
181 WITH data_saldo_ar AS (
182 SELECT ul_import_data_saldo_ar_id, gov_tax_amount
183 FROM ul_import_data_saldo_ar
184 WHERE upload_header_id = pUploadHeaderId
185 AND is_numeric(gov_tax_amount)
186 )
187 UPDATE ul_import_data_saldo_ar A
188 SET status = vFail,
189 message = message || 'Nomor pajak harus diisi (tidak boleh kosong), '
190 FROM data_saldo_ar B
191 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
192 AND TRIM(A.nomor_pajak) = vEmpty
193 AND (TRIM(A.tanggal_pajak) <> vEmpty OR B.gov_tax_amount::numeric <> 0);
194
195 -- tax_date tidak boleh '' dan tidak boleh null
196 WITH data_saldo_ar AS (
197 SELECT ul_import_data_saldo_ar_id, gov_tax_amount
198 FROM ul_import_data_saldo_ar
199 WHERE upload_header_id = pUploadHeaderId
200 AND is_numeric(gov_tax_amount)
201 )
202 UPDATE ul_import_data_saldo_ar A
203 SET status = vFail,
204 message = message || 'Tanggal pajak harus diisi (tidak boleh kosong), '
205 FROM data_saldo_ar B
206 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
207 AND TRIM(A.tanggal_pajak) = vEmpty
208 AND (TRIM(A.nomor_pajak) <> vEmpty OR B.gov_tax_amount::numeric <> 0);
209
210 -- validasi nett_amount < 0 maka balance_nett_amount < 0 dan jika nett_amount > 0 maka balance_nett_amount > 0
211 WITH data_saldo_ar AS (
212 SELECT ul_import_data_saldo_ar_id, nett_amount, balance_nett_amount
213 FROM ul_import_data_saldo_ar
214 WHERE upload_header_id = pUploadHeaderId
215 AND is_numeric(nett_amount)
216 AND is_numeric(balance_nett_amount)
217 )
218 UPDATE ul_import_data_saldo_ar A
219 SET status = vFail,
220 message = message || 'Jika nett amount < 0 maka nilai balance amount harus < 0 juga, begitu pula sebaliknya, '
221 FROM data_saldo_ar B
222 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
223 AND ( (B.nett_amount::numeric < 0 AND B.balance_nett_amount::numeric > 0)
224 OR (B.nett_amount::numeric > 0 AND B.balance_nett_amount::numeric < 0) );
225
226 -- validasi gov_tax_amount < 0 maka balance_gov_tax_amount < 0 dan jika gov_tax_amount > 0 maka balance_gov_tax_amount > 0
227 WITH data_saldo_ar AS (
228 SELECT ul_import_data_saldo_ar_id, gov_tax_amount, balance_gov_tax_amount
229 FROM ul_import_data_saldo_ar
230 WHERE upload_header_id = pUploadHeaderId
231 AND is_numeric(gov_tax_amount)
232 AND is_numeric(balance_gov_tax_amount)
233 )
234 UPDATE ul_import_data_saldo_ar A
235 SET status = vFail,
236 message = message || 'Jika gov tax amount < 0 maka nilai balance gov tax amount harus < 0 juga, begitu pula sebaliknya, '
237 FROM data_saldo_ar B
238 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
239 AND ( (B.gov_tax_amount::numeric < 0 AND B.balance_gov_tax_amount::numeric > 0)
240 OR (B.gov_tax_amount::numeric > 0 AND B.balance_gov_tax_amount::numeric < 0) );
241
242 -- validasi nett_amount < 0 maka gov_tax_amount < 0 dan jika nett_amount > 0 maka gov_tax_amount > 0
243 WITH data_saldo_ar AS (
244 SELECT ul_import_data_saldo_ar_id, nett_amount, gov_tax_amount
245 FROM ul_import_data_saldo_ar
246 WHERE upload_header_id = pUploadHeaderId
247 AND is_numeric(nett_amount)
248 AND is_numeric(gov_tax_amount)
249 )
250 UPDATE ul_import_data_saldo_ar A
251 SET status = vFail,
252 message = message || 'Jika nett amount < 0 maka nilai gov tax amount harus < 0 juga, begitu pula sebaliknya, '
253 FROM data_saldo_ar B
254 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
255 AND ( (B.nett_amount::numeric < 0 AND B.gov_tax_amount::numeric > 0)
256 OR (B.nett_amount::numeric > 0 AND B.gov_tax_amount::numeric < 0) );
257
258 -- validate gov_tax_amount harus 10% dari amount invoice
259 WITH data_saldo_ar AS (
260 SELECT ul_import_data_saldo_ar_id, nett_amount, gov_tax_amount
261 FROM ul_import_data_saldo_ar
262 WHERE upload_header_id = pUploadHeaderId
263 AND is_numeric(nett_amount)
264 AND is_numeric(gov_tax_amount)
265 )
266 UPDATE ul_import_data_saldo_ar A
267 SET status = vFail,
268 message = message || 'Amount PPN harus 10% dari DPP, '
269 FROM data_saldo_ar B
270 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
271 AND B.gov_tax_amount::numeric <> 0
272 AND ROUND(B.nett_amount::numeric * 0.1, 0) NOT BETWEEN B.gov_tax_amount::numeric - 1 AND B.gov_tax_amount::numeric + 1;
273
274 /* nett_amount tidak boleh = 0 */
275 UPDATE ul_import_data_saldo_ar A
276 SET status = vFail,
277 message = message || 'Nett Amount tidak boleh sama dengan 0 (nol), '
278 WHERE A.upload_header_id = pUploadHeaderId
279 AND A.nett_amount = '0';
280
281 -- validasi gov tax amount <> 0, jika tax no dan tax date diisi
282 UPDATE ul_import_data_saldo_ar A
283 SET status = vFail,
284 message = message || 'Gov Tax Amount tidak boleh sama dengan 0 (nol), '
285 WHERE A.upload_header_id = pUploadHeaderId
286 AND A.gov_tax_amount = '0'
287 AND (TRIM(A.nomor_pajak) <> vEmpty OR TRIM(A.tanggal_pajak) <> vEmpty);
288
289 /* validate balance_nett_amount <= nett_amount */
290 WITH data_saldo_ar AS (
291 SELECT ul_import_data_saldo_ar_id, balance_nett_amount, nett_amount
292 FROM ul_import_data_saldo_ar
293 WHERE upload_header_id = pUploadHeaderId
294 AND is_numeric(balance_nett_amount)
295 AND is_numeric(nett_amount)
296 )
297 UPDATE ul_import_data_saldo_ar A
298 SET status = vFail,
299 message = message || 'Balance Amount harus lebih kecil sama dengan nilai amount, '
300 FROM data_saldo_ar B
301 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
302 AND ABS(B.balance_nett_amount::numeric) > ABS(B.nett_amount::numeric);
303
304 /* validate balance_nett_amount <> 0*/
305 UPDATE ul_import_data_saldo_ar A
306 SET status = vFail,
307 message = message || 'Balance Amount tidak boleh sama dengan 0 (nol), '
308 WHERE A.upload_header_id = pUploadHeaderId
309 AND A.balance_nett_amount = '0';
310
311 /* validate balance_gov_tax_amount <= gov_tax_amount */
312 WITH data_saldo_ar AS (
313 SELECT ul_import_data_saldo_ar_id, balance_gov_tax_amount, gov_tax_amount
314 FROM ul_import_data_saldo_ar
315 WHERE upload_header_id = pUploadHeaderId
316 AND is_numeric(balance_gov_tax_amount)
317 AND is_numeric(gov_tax_amount)
318 )
319 UPDATE ul_import_data_saldo_ar A
320 SET status = vFail,
321 message = message || 'Balance Gov Tax Amount harus lebih kecil sama dengan nilai gov tax amount, '
322 FROM data_saldo_ar B
323 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
324 AND ABS(B.balance_gov_tax_amount::numeric) > ABS(B.gov_tax_amount::numeric);
325
326 -- balance gov_tax_amount tidak boleh = 0
327 -- validasi balance gov tax amount <> 0, jika gov tax amount <> 0
328 WITH data_saldo_ar AS (
329 SELECT ul_import_data_saldo_ar_id, balance_gov_tax_amount, gov_tax_amount
330 FROM ul_import_data_saldo_ar
331 WHERE upload_header_id = pUploadHeaderId
332 AND is_numeric(balance_gov_tax_amount)
333 AND is_numeric(gov_tax_amount)
334 )
335 UPDATE ul_import_data_saldo_ar A
336 SET status = vFail,
337 message = message || 'Balance Gov Tax Amount tidak boleh sama dengan 0 (nol), '
338 FROM data_saldo_ar B
339 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
340 AND B.balance_gov_tax_amount::numeric = 0
341 AND B.gov_tax_amount::numeric <> 0;
342
343 -- validasi tax no dan tax date harus diisi jika gov tax amount <> 0
344 WITH data_saldo_ar AS (
345 SELECT ul_import_data_saldo_ar_id, gov_tax_amount
346 FROM ul_import_data_saldo_ar
347 WHERE upload_header_id = pUploadHeaderId
348 AND is_numeric(gov_tax_amount)
349 )
350 UPDATE ul_import_data_saldo_ar A
351 SET status = vFail,
352 message = message || 'Gov Tax Amount tidak boleh sama dengan 0 (nol), '
353 FROM data_saldo_ar B
354 WHERE A.ul_import_data_saldo_ar_id = B.ul_import_data_saldo_ar_id
355 AND B.gov_tax_amount::numeric <> 0
356 AND (TRIM(A.nomor_pajak) = vEmpty OR TRIM(A.tanggal_pajak) = vEmpty);
357
358 /* validate tanggal ref doc harus valid jika diisi */
359 UPDATE ul_import_data_saldo_ar A
360 SET status = vFail,
361 message = message || 'Ref doc date tidak valid, '
362 WHERE A.upload_header_id = pUploadHeaderId
363 AND TRIM(A.ref_doc_date) <> vEmpty
364 AND NOT EXISTS (SELECT 1 FROM dt_date B
365 WHERE A.ref_doc_date = B.string_date);
366
367 /* validate Ref doc date harus diisi jika ref doc no diisi */
368 UPDATE ul_import_data_saldo_ar A
369 SET status = vFail,
370 message = message || 'Ref doc date harus diisi jika ref doc no diisi, '
371 WHERE A.upload_header_id = pUploadHeaderId
372 AND TRIM(A.ref_doc_no) <> vEmpty
373 AND TRIM(A.ref_doc_date) = vEmpty;
374
375 /* validate Ref doc no harus diisi jika ref doc date diisi */
376 UPDATE ul_import_data_saldo_ar A
377 SET status = vFail,
378 message = message || 'Ref doc no harus diisi jika ref doc date diisi, '
379 WHERE A.upload_header_id = pUploadHeaderId
380 AND TRIM(A.ref_doc_date) <> vEmpty
381 AND TRIM(A.ref_doc_no) = vEmpty;
382
383 /* validasi jika ref doc no dan ref doc date diisi, maka harus terdaftar di sl_order */
384 /*
385 UPDATE ul_import_data_saldo_ar A
386 SET status = vFail,
387 message = message || 'Ref doc no dan ref doc date tidak terdaftar disystem, '
388 WHERE A.upload_header_id = pUploadHeaderId
389 AND TRIM(A.ref_doc_date) <> vEmpty
390 AND TRIM(A.ref_doc_no) <> vEmpty
391 AND NOT EXISTS (
392 SELECT 1 FROM sl_order Z WHERE A.ref_doc_no = Z.doc_no AND A.ref_doc_date = Z.doc_date AND Z.tenant_id = vTenantId
393 );
394 */
395
396 -- # List tidak boleh duplikat by Type dokumen, nomor dokumen, tanggal dokumen, kode ou
397 WITH duplicate_data_saldo_ar AS (
398 SELECT A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, A.kode_ou
399 FROM ul_import_data_saldo_ar A
400 WHERE A.upload_header_id = pUploadHeaderId
401 AND TRIM(A.nomor_dokumen) <> vEmpty
402 AND TRIM(A.tanggal_dokumen) <> vEmpty
403 AND TRIM(A.kode_ou) <> vEmpty
404 GROUP BY A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, A.kode_ou
405 HAVING COUNT(1) > 1
406 )
407 UPDATE ul_import_data_saldo_ar A
408 SET status = vFail,
409 message = message || 'Duplicate data by "Type dokumen, nomor dokumen, tanggal dokumen, kode ou", '
410 WHERE A.upload_header_id = pUploadHeaderId
411 AND EXISTS (
412 SELECT 1 FROM duplicate_data_saldo_ar Z
413 WHERE A.nomor_dokumen = Z.nomor_dokumen
414 AND A.tanggal_dokumen = Z.tanggal_dokumen
415 AND A.kode_ou = Z.kode_ou
416 AND Z.doc_type_id = A.doc_type_id
417 );
418
419
420 -- Sesuaikan message, agar message paling belakang tidak ada koma
421 UPDATE ul_import_data_saldo_ar A
422 SET message = substr(A.message, 1, length(A.message)-2)
423 WHERE A.upload_header_id = pUploadHeaderId
424 AND A.status = vFail
425 AND TRIM(A.message) != vEmpty;
426
427 -- ubah update_status menjadi E (untuk status = FAIL): menandakan bahwa data error / tidak digunakan untuk membuat data
428 UPDATE ul_import_data_saldo_ar A
429 SET update_status = vActionError
430 WHERE upload_header_id = pUploadHeaderId
431 AND status = vFail;
432
433 -- Hitung jumlah data yang FAIL
434 SELECT COUNT(1) INTO vCountFail
435 FROM ul_import_data_saldo_ar
436 WHERE upload_header_id = pUploadHeaderId
437 AND status = vFail;
438
439 -- Update status non FAIL to OK
440 UPDATE ul_import_data_saldo_ar
441 SET status = vOk
442 WHERE upload_header_id = pUploadHeaderId
443 AND status <> vFail;
444
445 -- Cek apakah ada item CSV yang statusnya OK
446 IF EXISTS ( SELECT 1 FROM ul_import_data_saldo_ar B
447 WHERE B.upload_header_id = pUploadHeaderId
448 AND B.status = vOk ) THEN
449
450 -- RESET temp table
451 DELETE FROM tt_ul_invoice_ar WHERE upload_header_id = pUploadHeaderId;
452
453 -- GET curr code from sysconfig
454 SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
455
456 -- UPDATE OK data untuk saldo ar terdaftar
457 WITH update_data_saldo_ar AS (
458 UPDATE fi_invoice_ar A SET
459 partner_id=C.partner_id,
460 ext_doc_no=B.ref_doc_no,
461 ext_doc_date=B.ref_doc_date,
462 ref_doc_type_id=vEmptyId,
463 ref_id=vEmptyId,
464 due_date=B.tanggal_jatuh_tempo,
465 total_tax_base_amount=ABS(B.nett_amount::numeric),
466 total_amount=ABS(B.nett_amount::numeric),
467 tax_amount=ABS(B.gov_tax_amount::numeric),
468 remark=B.remark,
469 version=A.version+1,
470 update_datetime=vDatetime,
471 update_user_id=vUserId
472 FROM ul_import_data_saldo_ar B
473 INNER JOIN t_ou BX ON B.kode_ou = BX.ou_code AND BX.tenant_id = vTenantId
474 INNER JOIN m_partner C ON B.kode_pelanggan = C.partner_code AND C.tenant_id = vTenantId
475 --LEFT JOIN sl_order D ON B.ref_doc_no = D.doc_no AND B.ref_doc_date = D.doc_date AND D.tenant_id = vTenantId
476 WHERE B.upload_header_id = pUploadHeaderId
477 AND B.status = vOk
478 AND A.tenant_id = vTenantId
479 AND A.doc_type_id = B.doc_type_id
480 AND A.doc_no = B.nomor_dokumen
481 AND A.doc_date = B.tanggal_dokumen
482 AND A.ou_id = BX.ou_id
483 RETURNING A.*
484 )
485 INSERT INTO tt_ul_invoice_ar(
486 upload_header_id, action_type, invoice_ar_id, tenant_id, doc_type_id,
487 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
488 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
489 total_amount, tax_amount, remark, status_doc, workflow_status,
490 version, create_datetime, create_user_id, update_datetime, update_user_id)
491 SELECT pUploadHeaderId, vActionUpdate, invoice_ar_id, tenant_id, doc_type_id,
492 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
493 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
494 total_amount, tax_amount, remark, status_doc, workflow_status,
495 version, create_datetime, create_user_id, update_datetime, update_user_id
496 FROM update_data_saldo_ar;
497
498 -- INSERT OK data untuk saldo ar baru
499 WITH insert_data_saldo_ar AS (
500 INSERT INTO fi_invoice_ar(
501 tenant_id, doc_type_id, doc_no, doc_date, ou_id,
502 partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
503 due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
504 remark, status_doc, workflow_status, version, create_datetime,
505 create_user_id, update_datetime, update_user_id)
506 SELECT vTenantId, A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, B.ou_id,
507 C.partner_id, A.ref_doc_no, A.ref_doc_date, vEmptyId, vEmptyId,
508 A.tanggal_jatuh_tempo, vCurrCode, ABS(A.nett_amount::numeric), ABS(A.nett_amount::numeric), ABS(A.gov_tax_amount::numeric),
509 A.remark, vStatusRelease, vWorkflowApproved, 0, vDatetime,
510 vUserId, vDatetime, vUserId
511 FROM ul_import_data_saldo_ar A
512 INNER JOIN t_ou B ON A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
513 INNER JOIN m_partner C ON A.kode_pelanggan = C.partner_code AND C.tenant_id = vTenantId
514 --LEFT JOIN sl_order D ON A.ref_doc_no = D.doc_no AND A.ref_doc_date = D.doc_date AND D.tenant_id = vTenantId
515 WHERE A.upload_header_id = pUploadHeaderId
516 AND A.status = vOk
517 AND NOT EXISTS (
518 SELECT 1 FROM fi_invoice_ar Z
519 WHERE Z.tenant_id = vTenantId
520 AND Z.doc_type_id = A.doc_type_id
521 AND Z.doc_no = A.nomor_dokumen
522 AND Z.doc_date = A.tanggal_dokumen
523 AND Z.ou_id = B.ou_id
524 )
525 RETURNING *
526 )
527 INSERT INTO tt_ul_invoice_ar(
528 upload_header_id, action_type, invoice_ar_id, tenant_id, doc_type_id,
529 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
530 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
531 total_amount, tax_amount, remark, status_doc, workflow_status,
532 version, create_datetime, create_user_id, update_datetime, update_user_id)
533 SELECT pUploadHeaderId, vActionInsert, invoice_ar_id, tenant_id, doc_type_id,
534 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
535 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
536 total_amount, tax_amount, remark, status_doc, workflow_status,
537 version, create_datetime, create_user_id, update_datetime, update_user_id
538 FROM insert_data_saldo_ar;
539
540 -- ACTION FOR UPPDATE
541 IF EXISTS (SELECT 1 FROM tt_ul_invoice_ar WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
542
543 DELETE FROM fi_invoice_ar_tax A
544 WHERE A.tenant_id = vTenantId
545 AND EXISTS (
546 SELECT 1 FROM tt_ul_invoice_ar Z
547 WHERE A.invoice_ar_id = Z.invoice_ar_id
548 AND Z.action_type = vActionUpdate
549 AND Z.upload_header_id = pUploadHeaderId
550 );
551 INSERT INTO fi_invoice_ar_tax(
552 tenant_id, invoice_ar_id, tax_id, flg_amount,
553 tax_percentage, base_amount, tax_amount, tax_no, tax_date, tax_curr_code,
554 gov_tax_amount, remark, version, create_datetime, create_user_id,
555 update_datetime, update_user_id)
556 SELECT vTenantId, A.invoice_ar_id, C.tax_id, C.flg_amount,
557 C.percentage, ABS(B.nett_amount::numeric), ABS(B.gov_tax_amount::numeric), B.nomor_pajak, B.tanggal_pajak, vCurrCode,
558 ABS(B.gov_tax_amount::numeric), B.remark, 0, vDatetime, vUserId,
559 vDatetime, vUserId
560 FROM tt_ul_invoice_ar A
561 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
562 INNER JOIN ul_import_data_saldo_ar B ON A.doc_type_id = B.doc_type_id
563 AND A.doc_no = B.nomor_dokumen
564 AND A.doc_date = B.tanggal_dokumen
565 AND AX.ou_code = B.kode_ou
566 INNER JOIN m_tax C ON C.tenant_id = vTenantId
567 WHERE B.upload_header_id = pUploadHeaderId
568 AND B.status = vOk
569 AND A.upload_header_id = B.upload_header_id
570 AND A.action_type = vActionUpdate;
571
572 DELETE FROM fi_invoice_ar_cost A
573 WHERE A.tenant_id = vTenantId
574 AND EXISTS (
575 SELECT 1 FROM tt_ul_invoice_ar Z
576 WHERE A.invoice_ar_id = Z.invoice_ar_id
577 AND Z.action_type = vActionUpdate
578 AND Z.upload_header_id = pUploadHeaderId
579 );
580 INSERT INTO fi_invoice_ar_cost(
581 tenant_id, invoice_ar_id, line_no, activity_gl_id,
582 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
583 remark, version, create_datetime, create_user_id, update_datetime,
584 update_user_id, segment_id)
585 SELECT vTenantId, A.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY A.invoice_ar_id), CASE WHEN A.doc_type_id = vDocTypeDebtNoteAr THEN vActivityGlIdDebtNoteAr ELSE vActivityGlIdCreditNoteAr END,
586 vEmptyId, vCurrCode, ABS(B.nett_amount::numeric), C.tax_id, C.percentage, ABS(B.gov_tax_amount::numeric),
587 B.remark, 0, vDatetime, vUserId, vDatetime,
588 vUserId, vEmptyId
589 FROM tt_ul_invoice_ar A
590 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
591 INNER JOIN ul_import_data_saldo_ar B ON A.doc_type_id = B.doc_type_id
592 AND A.doc_no = B.nomor_dokumen
593 AND A.doc_date = B.tanggal_dokumen
594 AND AX.ou_code = B.kode_ou
595 INNER JOIN m_tax C ON C.tenant_id = vTenantId
596 WHERE B.upload_header_id = pUploadHeaderId
597 AND B.status = vOk
598 AND A.upload_header_id = B.upload_header_id
599 AND A.action_type = vActionUpdate;
600
601 UPDATE fi_invoice_ar_balance A SET
602 ext_doc_no=B.ext_doc_no,
603 ext_doc_date=B.ext_doc_date,
604 ref_id=B.ref_id,
605 ref_doc_type_id=B.ref_doc_type_id,
606 partner_id=B.partner_id,
607 due_date=B.due_date,
608 amount=(C.nett_amount::numeric + C.gov_tax_amount::numeric),
609 remark=C.remark,
610 payment_amount=(C.nett_amount::numeric + C.gov_tax_amount::numeric - C.balance_nett_amount::numeric - C.balance_gov_tax_amount::numeric),
611 flg_payment = vNo,
612 version=A.version+1,
613 update_datetime=vDatetime,
614 update_user_id=vUserId
615 FROM tt_ul_invoice_ar B
616 INNER JOIN t_ou BX ON B.ou_id = BX.ou_id
617 INNER JOIN ul_import_data_saldo_ar C ON B.doc_type_id = C.doc_type_id
618 AND B.doc_no = C.nomor_dokumen
619 AND B.doc_date = C.tanggal_dokumen
620 AND BX.ou_code = C.kode_ou
621 WHERE C.upload_header_id = pUploadHeaderId
622 AND C.status = vOk
623 AND B.upload_header_id = C.upload_header_id
624 AND B.action_type = vActionUpdate
625 AND A.tenant_id = B.tenant_id
626 AND A.doc_type_id = B.doc_type_id
627 AND A.invoice_ar_id = B.invoice_ar_id;
628
629 UPDATE fi_summary_monthly_ar A SET
630 invoice_amount=C.amount,
631 balance_amount=C.amount,
632 payment_amount=C.payment_amount,
633 version=A.version+1,
634 update_datetime=vDatetime,
635 update_user_id=vUserId
636 FROM tt_ul_invoice_ar B
637 INNER JOIN fi_invoice_ar_balance C ON B.tenant_id = C.tenant_id AND B.doc_type_id = C.doc_type_id AND B.invoice_ar_id = C.invoice_ar_id
638 INNER JOIN m_ou_structure D ON B.ou_id = D.ou_id
639 WHERE B.upload_header_id = pUploadHeaderId
640 AND B.action_type = vActionUpdate
641 AND A.tenant_id = B.tenant_id
642 AND A.date_year_month = vYearMonth
643 AND A.ou_id = D.ou_bu_id
644 AND A.doc_type_id = B.doc_type_id
645 AND A.invoice_id = C.invoice_ar_balance_id;
646
647 DELETE FROM fi_invoice_ar_balance_due_date A
648 WHERE EXISTS (
649 SELECT 1 FROM tt_ul_invoice_ar Z
650 INNER JOIN fi_invoice_ar_balance X ON Z.tenant_id = X.tenant_id AND Z.doc_type_id = X.doc_type_id AND Z.invoice_ar_id = X.invoice_ar_id
651 WHERE A.invoice_ar_balance_id = X.invoice_ar_balance_id
652 AND Z.upload_header_id = pUploadHeaderId
653 AND Z.action_type = vActionUpdate
654 );
655 INSERT INTO fi_invoice_ar_balance_due_date(
656 invoice_ar_balance_id, due_date, create_datetime, create_user_id, update_datetime, update_user_id)
657 SELECT B.invoice_ar_balance_id, B.due_date, vDatetime, vUserId, vDatetime, vUserId
658 FROM tt_ul_invoice_ar A
659 INNER JOIN fi_invoice_ar_balance B ON A.tenant_id = B.tenant_id AND A.doc_type_id = B.doc_type_id AND A.invoice_ar_id = B.invoice_ar_id
660 WHERE A.action_type = vActionUpdate
661 AND A.upload_header_id = pUploadHeaderId;
662
663
664 END IF;
665
666 -- ACTION FOR INSERT
667 IF EXISTS (SELECT 1 FROM tt_ul_invoice_ar WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
668
669 INSERT INTO fi_invoice_ar_tax(
670 tenant_id, invoice_ar_id, tax_id, flg_amount,
671 tax_percentage, base_amount, tax_amount, tax_no, tax_date, tax_curr_code,
672 gov_tax_amount, remark, version, create_datetime, create_user_id,
673 update_datetime, update_user_id)
674 SELECT vTenantId, A.invoice_ar_id, C.tax_id, C.flg_amount,
675 C.percentage, ABS(B.nett_amount::numeric), ABS(B.gov_tax_amount::numeric), B.nomor_pajak, B.tanggal_pajak, vCurrCode,
676 ABS(B.gov_tax_amount::numeric), B.remark, 0, vDatetime, vUserId,
677 vDatetime, vUserId
678 FROM tt_ul_invoice_ar A
679 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
680 INNER JOIN ul_import_data_saldo_ar B ON A.doc_type_id = B.doc_type_id
681 AND A.doc_no = B.nomor_dokumen
682 AND A.doc_date = B.tanggal_dokumen
683 AND AX.ou_code = B.kode_ou
684 INNER JOIN m_tax C ON C.tenant_id = vTenantId
685 WHERE B.upload_header_id = pUploadHeaderId
686 AND B.status = vOk
687 AND A.upload_header_id = B.upload_header_id
688 AND A.action_type = vActionInsert;
689
690 INSERT INTO fi_invoice_ar_cost(
691 tenant_id, invoice_ar_id, line_no, activity_gl_id,
692 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
693 remark, version, create_datetime, create_user_id, update_datetime,
694 update_user_id, segment_id)
695 SELECT vTenantId, A.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY A.invoice_ar_id), CASE WHEN A.doc_type_id = vDocTypeDebtNoteAr THEN vActivityGlIdDebtNoteAr ELSE vActivityGlIdCreditNoteAr END,
696 vEmptyId, vCurrCode, ABS(B.nett_amount::numeric), C.tax_id, C.percentage, ABS(B.gov_tax_amount::numeric),
697 B.remark, 0, vDatetime, vUserId, vDatetime,
698 vUserId, vEmptyId
699 FROM tt_ul_invoice_ar A
700 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
701 INNER JOIN ul_import_data_saldo_ar B ON A.doc_type_id = B.doc_type_id
702 AND A.doc_no = B.nomor_dokumen
703 AND A.doc_date = B.tanggal_dokumen
704 AND AX.ou_code = B.kode_ou
705 INNER JOIN m_tax C ON C.tenant_id = vTenantId
706 WHERE B.upload_header_id = pUploadHeaderId
707 AND B.status = vOk
708 AND A.upload_header_id = B.upload_header_id
709 AND A.action_type = vActionInsert;
710
711 INSERT INTO fi_invoice_ar_balance (
712 tenant_id, ou_id, doc_type_id, invoice_ar_id,
713 doc_no, doc_date, ext_doc_no, ext_doc_date,
714 ref_doc_type_id, ref_id, partner_id, due_date,
715 curr_code, amount, remark,
716 payment_amount, flg_payment,
717 version, create_datetime, create_user_id, update_datetime, update_user_id)
718 SELECT vTenantId, A.ou_id, A.doc_type_id, A.invoice_ar_id,
719 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
720 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
721 vCurrCode, (B.nett_amount::numeric + B.gov_tax_amount::numeric), A.remark,
722 (B.nett_amount::numeric + B.gov_tax_amount::numeric - B.balance_nett_amount::numeric - B.balance_gov_tax_amount::numeric), vNo,
723 0, vDatetime, vUserId, vDatetime, vUserId
724 FROM tt_ul_invoice_ar A
725 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
726 INNER JOIN ul_import_data_saldo_ar B ON A.doc_type_id = B.doc_type_id
727 AND A.doc_no = B.nomor_dokumen
728 AND A.doc_date = B.tanggal_dokumen
729 AND AX.ou_code = B.kode_ou
730 WHERE B.upload_header_id = pUploadHeaderId
731 AND B.status = vOk
732 AND A.upload_header_id = B.upload_header_id
733 AND A.action_type = vActionInsert;
734
735 INSERT INTO fi_summary_monthly_ar(
736 date_year_month, tenant_id, ou_id,
737 doc_type_id, invoice_id, invoice_amount, balance_amount, payment_amount,
738 version, create_datetime, create_user_id, update_datetime, update_user_id,
739 curr_code)
740 SELECT vYearMonth, vTenantId, (f_get_ou_bu_structure(A.ou_id)).ou_bu_id,
741 A.doc_type_id, B.invoice_ar_balance_id, B.amount, B.amount, B.payment_amount,
742 0, vDatetime, vUserId, vDatetime, vUserId,
743 vCurrCode
744 FROM tt_ul_invoice_ar A
745 INNER JOIN fi_invoice_ar_balance B ON A.tenant_id = B.tenant_id AND A.doc_type_id = B.doc_type_id AND A.invoice_ar_id = B.invoice_ar_id
746 WHERE A.action_type = vActionInsert
747 AND A.upload_header_id = pUploadHeaderId;
748
749 INSERT INTO fi_invoice_ar_balance_due_date(
750 invoice_ar_balance_id, due_date, create_datetime, create_user_id, update_datetime, update_user_id)
751 SELECT B.invoice_ar_balance_id, B.due_date, vDatetime, vUserId, vDatetime, vUserId
752 FROM tt_ul_invoice_ar A
753 INNER JOIN fi_invoice_ar_balance B ON A.tenant_id = B.tenant_id AND A.doc_type_id = B.doc_type_id AND A.invoice_ar_id = B.invoice_ar_id
754 WHERE A.action_type = vActionInsert
755 AND A.upload_header_id = pUploadHeaderId;
756
757 END IF;
758
759 -- UPDATE STATUS flg_payment PADA fi_invoice_ar_balance MENJADI Y UNTUK DATA DIMANA AMOUNT = PAYMENT AMOUNT
760 UPDATE fi_invoice_ar_balance A SET
761 flg_payment= vYes,
762 version=A.version+1,
763 update_datetime=vDatetime,
764 update_user_id=vUserId
765 WHERE A.amount = A.payment_amount;
766
767 /* 20200515, by HS
768 Data yg di fi_invoice_ar_balance & fi_summary_monthly_ar tetap dibiarkan,
769 jika memang dokumen invoice ar nya tidak ada didata upload nya
770 */
771
772 -- RESET temp table
773 DELETE FROM tt_ul_invoice_ar WHERE upload_header_id = pUploadHeaderId;
774
775 END IF;
776
777 RETURN vCountFail;
778
779END;
780$BODY$
781 LANGUAGE plpgsql VOLATILE
782 COST 100;
783 /