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