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