· 5 years ago · Jun 30, 2020, 06:52 AM
1CREATE OR REPLACE FUNCTION ul_upload_saldo_ap(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 vActivityGlIdCreditNoteAp bigint;
24 vActivityGlIdDebtNoteAp bigint;
25 vCurrCode character varying;
26
27 vDocTypeDebtNoteAp bigint := 211;
28 vDocTypeCreditNoteAp bigint := 201;
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 vActivityGlIdCreditNoteAp = CAST( f_get_upload_parameter(pUploadHeaderId, 'activityGlIdCreditNoteAp') AS bigint );
40 vActivityGlIdDebtNoteAp = CAST( f_get_upload_parameter(pUploadHeaderId, 'activityGlIdDebtNoteAp') AS bigint );
41
42 -- CHANGE EMPTY VALYE TO 0 FOR NUMERIC FIELD
43 -- MENENTUKAN DOC TYPE ID
44 UPDATE ul_import_data_saldo_ap 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 vDocTypeCreditNoteAp
53 WHEN A.nett_amount::numeric < 0
54 THEN vDocTypeDebtNoteAp
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_ap 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_ap 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_ap 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_ap 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_import_data_saldo_ap 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_import_data_saldo_ap 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 di data master */
114 UPDATE ul_import_data_saldo_ap A
115 SET status = vFail,
116 message = message || 'Kode supplier 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_supplier);
121
122 /* partner tidak terdaftar sebagai supplier */
123 UPDATE ul_import_data_saldo_ap A
124 SET status = vFail,
125 message = message || 'Kode supplier tidak terdaftar sebagai supplier, '
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_supplier
131 AND Y.group_partner = 'S');
132
133 /* validate due_date >= doc_date */
134 UPDATE ul_import_data_saldo_ap 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_import_data_saldo_ap 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_import_data_saldo_ap 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_import_data_saldo_ap 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_import_data_saldo_ap 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_ap AS (
179 SELECT ul_import_data_saldo_ap_id, gov_tax_amount
180 FROM ul_import_data_saldo_ap
181 WHERE upload_header_id = pUploadHeaderId
182 AND is_numeric(gov_tax_amount)
183 )
184 UPDATE ul_import_data_saldo_ap A
185 SET status = vFail,
186 message = message || 'Nomor pajak harus diisi (tidak boleh kosong), '
187 FROM data_saldo_ap B
188 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
194 SELECT ul_import_data_saldo_ap_id, gov_tax_amount
195 FROM ul_import_data_saldo_ap
196 WHERE upload_header_id = pUploadHeaderId
197 AND is_numeric(gov_tax_amount)
198 )
199 UPDATE ul_import_data_saldo_ap A
200 SET status = vFail,
201 message = message || 'Tanggal pajak harus diisi (tidak boleh kosong), '
202 FROM data_saldo_ap B
203 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
209 SELECT ul_import_data_saldo_ap_id, nett_amount, balance_nett_amount
210 FROM ul_import_data_saldo_ap
211 WHERE upload_header_id = pUploadHeaderId
212 AND is_numeric(nett_amount)
213 AND is_numeric(balance_nett_amount)
214 )
215 UPDATE ul_import_data_saldo_ap 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_ap B
219 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
225 SELECT ul_import_data_saldo_ap_id, gov_tax_amount, balance_gov_tax_amount
226 FROM ul_import_data_saldo_ap
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_import_data_saldo_ap 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_ap B
235 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
241 SELECT ul_import_data_saldo_ap_id, nett_amount, gov_tax_amount
242 FROM ul_import_data_saldo_ap
243 WHERE upload_header_id = pUploadHeaderId
244 AND is_numeric(nett_amount)
245 AND is_numeric(gov_tax_amount)
246 )
247 UPDATE ul_import_data_saldo_ap 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_ap B
251 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
257 SELECT ul_import_data_saldo_ap_id, nett_amount, gov_tax_amount
258 FROM ul_import_data_saldo_ap
259 WHERE upload_header_id = pUploadHeaderId
260 AND is_numeric(nett_amount)
261 AND is_numeric(gov_tax_amount)
262 )
263 UPDATE ul_import_data_saldo_ap A
264 SET status = vFail,
265 message = message || 'Amount PPN harus 10% dari DPP, '
266 FROM data_saldo_ap B
267 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_import_data_saldo_ap 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_import_data_saldo_ap 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_ap AS (
288 SELECT ul_import_data_saldo_ap_id, balance_nett_amount, nett_amount
289 FROM ul_import_data_saldo_ap
290 WHERE upload_header_id = pUploadHeaderId
291 AND is_numeric(balance_nett_amount)
292 AND is_numeric(nett_amount)
293 )
294 UPDATE ul_import_data_saldo_ap A
295 SET status = vFail,
296 message = message || 'Balance Amount harus lebih kecil sama dengan nilai amount, '
297 FROM data_saldo_ap B
298 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_id
299 AND ABS(B.balance_nett_amount::numeric) > ABS(B.nett_amount::numeric);
300
301 /* validate balance_nett_amount <> 0*/
302 UPDATE ul_import_data_saldo_ap 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_ap AS (
310 SELECT ul_import_data_saldo_ap_id, balance_gov_tax_amount, gov_tax_amount
311 FROM ul_import_data_saldo_ap
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_import_data_saldo_ap 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_ap B
320 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
326 SELECT ul_import_data_saldo_ap_id, balance_gov_tax_amount, gov_tax_amount
327 FROM ul_import_data_saldo_ap
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_import_data_saldo_ap A
333 SET status = vFail,
334 message = message || 'Balance Gov Tax Amount tidak boleh sama dengan 0 (nol), '
335 FROM data_saldo_ap B
336 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_ap AS (
342 SELECT ul_import_data_saldo_ap_id, gov_tax_amount
343 FROM ul_import_data_saldo_ap
344 WHERE upload_header_id = pUploadHeaderId
345 AND is_numeric(gov_tax_amount)
346 )
347 UPDATE ul_import_data_saldo_ap A
348 SET status = vFail,
349 message = message || 'Gov Tax Amount tidak boleh sama dengan 0 (nol), '
350 FROM data_saldo_ap B
351 WHERE A.ul_import_data_saldo_ap_id = B.ul_import_data_saldo_ap_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_import_data_saldo_ap 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_import_data_saldo_ap 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_import_data_saldo_ap 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 /*
382 UPDATE ul_import_data_saldo_ap A
383 SET status = vFail,
384 message = message || 'Ref doc no dan ref doc date tidak terdaftar disystem, '
385 WHERE A.upload_header_id = pUploadHeaderId
386 AND TRIM(A.ref_doc_date) <> vEmpty
387 AND TRIM(A.ref_doc_no) <> vEmpty
388 AND NOT EXISTS (
389 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
390 );
391 */
392
393 -- # List tidak boleh duplikat by Type dokumen, nomor dokumen, tanggal dokumen, kode ou
394 WITH duplicate_data_saldo_ap AS (
395 SELECT A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, A.kode_ou
396 FROM ul_import_data_saldo_ap A
397 WHERE A.upload_header_id = pUploadHeaderId
398 AND TRIM(A.nomor_dokumen) <> vEmpty
399 AND TRIM(A.tanggal_dokumen) <> vEmpty
400 AND TRIM(A.kode_ou) <> vEmpty
401 GROUP BY A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, A.kode_ou
402 HAVING COUNT(1) > 1
403 )
404 UPDATE ul_import_data_saldo_ap A
405 SET status = vFail,
406 message = message || 'Duplicate data by "Type dokumen, nomor dokumen, tanggal dokumen, kode ou", '
407 WHERE A.upload_header_id = pUploadHeaderId
408 AND EXISTS (
409 SELECT 1 FROM duplicate_data_saldo_ap Z
410 WHERE A.nomor_dokumen = Z.nomor_dokumen
411 AND A.tanggal_dokumen = Z.tanggal_dokumen
412 AND A.kode_ou = Z.kode_ou
413 AND Z.doc_type_id = A.doc_type_id
414 );
415
416 -- Sesuaikan message, agar message paling belakang tidak ada koma
417 UPDATE ul_import_data_saldo_ap A
418 SET message = substr(A.message, 1, length(A.message)-2)
419 WHERE A.upload_header_id = pUploadHeaderId
420 AND A.status = vFail
421 AND TRIM(A.message) != vEmpty;
422
423 -- ubah update_status menjadi E (untuk status = FAIL): menandakan bahwa data error / tidak digunakan untuk membuat data
424 UPDATE ul_import_data_saldo_ap A
425 SET update_status = vActionError
426 WHERE upload_header_id = pUploadHeaderId
427 AND status = vFail;
428
429 -- Hitung jumlah data yang FAIL
430 SELECT COUNT(1) INTO vCountFail
431 FROM ul_import_data_saldo_ap
432 WHERE upload_header_id = pUploadHeaderId
433 AND status = vFail;
434
435 -- Update status non FAIL to OK
436 UPDATE ul_import_data_saldo_ap
437 SET status = vOk
438 WHERE upload_header_id = pUploadHeaderId
439 AND status <> vFail;
440
441 -- Cek apakah ada item CSV yang statusnya OK
442 IF EXISTS ( SELECT 1 FROM ul_import_data_saldo_ap B
443 WHERE B.upload_header_id = pUploadHeaderId
444 AND B.status = vOk ) THEN
445
446 -- RESET temp table
447 DELETE FROM tt_ul_invoice_ap WHERE upload_header_id = pUploadHeaderId;
448
449 -- GET curr code from sysconfig
450 SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
451
452 -- UPDATE OK data untuk saldo ap terdaftar
453 WITH update_data_saldo_ap AS (
454 UPDATE fi_invoice_ap 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=vEmptyId,
459 ref_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_import_data_saldo_ap 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_supplier = 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 B.status = vOk
474 AND A.tenant_id = vTenantId
475 AND A.doc_type_id = B.doc_type_id
476 AND A.doc_no = B.nomor_dokumen
477 AND A.doc_date = B.tanggal_dokumen
478 AND A.ou_id = BX.ou_id
479 RETURNING A.*
480 )
481 INSERT INTO tt_ul_invoice_ap(
482 upload_header_id, action_type, invoice_ap_id, tenant_id, doc_type_id,
483 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
484 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
485 total_amount, tax_amount, remark, status_doc, workflow_status,
486 version, create_datetime, create_user_id, update_datetime, update_user_id)
487 SELECT pUploadHeaderId, vActionUpdate, invoice_ap_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 FROM update_data_saldo_ap;
493
494 -- INSERT OK data untuk saldo ap baru
495 WITH insert_data_saldo_ap AS (
496 INSERT INTO fi_invoice_ap(
497 tenant_id, doc_type_id, doc_no, doc_date, ou_id,
498 partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
499 due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
500 remark, status_doc, workflow_status, version, create_datetime,
501 create_user_id, update_datetime, update_user_id)
502 SELECT vTenantId, A.doc_type_id, A.nomor_dokumen, A.tanggal_dokumen, B.ou_id,
503 C.partner_id, A.ref_doc_no, A.ref_doc_date, vEmptyId, vEmptyId,
504 A.tanggal_jatuh_tempo, vCurrCode, ABS(A.nett_amount::numeric), ABS(A.nett_amount::numeric), ABS(A.gov_tax_amount::numeric),
505 A.remark, vStatusRelease, vWorkflowApproved, 0, vDatetime,
506 vUserId, vDatetime, vUserId
507 FROM ul_import_data_saldo_ap A
508 INNER JOIN t_ou B ON A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
509 INNER JOIN m_partner C ON A.kode_supplier = C.partner_code AND C.tenant_id = vTenantId
510 --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
511 WHERE A.upload_header_id = pUploadHeaderId
512 AND A.status = vOk
513 AND NOT EXISTS (
514 SELECT 1 FROM fi_invoice_ap Z
515 WHERE Z.tenant_id = vTenantId
516 AND Z.doc_type_id = A.doc_type_id
517 AND Z.doc_no = A.nomor_dokumen
518 AND Z.doc_date = A.tanggal_dokumen
519 AND Z.ou_id = B.ou_id
520 )
521 RETURNING *
522 )
523 INSERT INTO tt_ul_invoice_ap(
524 upload_header_id, action_type, invoice_ap_id, tenant_id, doc_type_id,
525 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
526 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
527 total_amount, tax_amount, remark, status_doc, workflow_status,
528 version, create_datetime, create_user_id, update_datetime, update_user_id)
529 SELECT pUploadHeaderId, vActionInsert, invoice_ap_id, tenant_id, doc_type_id,
530 doc_no, doc_date, ou_id, partner_id, ext_doc_no, ext_doc_date,
531 ref_doc_type_id, ref_id, due_date, curr_code, total_tax_base_amount,
532 total_amount, tax_amount, remark, status_doc, workflow_status,
533 version, create_datetime, create_user_id, update_datetime, update_user_id
534 FROM insert_data_saldo_ap;
535
536 -- ACTION FOR UPPDATE
537 IF EXISTS (SELECT 1 FROM tt_ul_invoice_ap WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
538
539 DELETE FROM fi_invoice_ap_tax A
540 WHERE A.tenant_id = vTenantId
541 AND EXISTS (
542 SELECT 1 FROM tt_ul_invoice_ap Z
543 WHERE A.invoice_ap_id = Z.invoice_ap_id
544 AND Z.action_type = vActionUpdate
545 AND Z.upload_header_id = pUploadHeaderId
546 );
547 INSERT INTO fi_invoice_ap_tax(
548 tenant_id, invoice_ap_id, tax_id, flg_amount,
549 tax_percentage, base_amount, tax_amount, tax_no, tax_date, tax_curr_code,
550 gov_tax_amount, remark, version, create_datetime, create_user_id,
551 update_datetime, update_user_id)
552 SELECT vTenantId, A.invoice_ap_id, C.tax_id, C.flg_amount,
553 C.percentage, ABS(B.nett_amount::numeric), ABS(B.gov_tax_amount::numeric), B.nomor_pajak, B.tanggal_pajak, vCurrCode,
554 ABS(B.gov_tax_amount::numeric), B.remark, 0, vDatetime, vUserId,
555 vDatetime, vUserId
556 FROM tt_ul_invoice_ap A
557 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
558 INNER JOIN ul_import_data_saldo_ap B ON A.doc_type_id = B.doc_type_id
559 AND A.doc_no = B.nomor_dokumen
560 AND A.doc_date = B.tanggal_dokumen
561 AND AX.ou_code = B.kode_ou
562 INNER JOIN m_tax C ON C.tenant_id = vTenantId
563 WHERE B.upload_header_id = pUploadHeaderId
564 AND B.status = vOk
565 AND A.upload_header_id = B.upload_header_id
566 AND A.action_type = vActionUpdate;
567
568 DELETE FROM fi_invoice_ap_cost A
569 WHERE A.tenant_id = vTenantId
570 AND EXISTS (
571 SELECT 1 FROM tt_ul_invoice_ap Z
572 WHERE A.invoice_ap_id = Z.invoice_ap_id
573 AND Z.action_type = vActionUpdate
574 AND Z.upload_header_id = pUploadHeaderId
575 );
576 INSERT INTO fi_invoice_ap_cost(
577 tenant_id, invoice_ap_id, line_no, activity_gl_id,
578 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
579 remark, version, create_datetime, create_user_id, update_datetime,
580 update_user_id, segment_id)
581 SELECT vTenantId, A.invoice_ap_id, ROW_NUMBER() OVER (PARTITION BY A.invoice_ap_id), CASE WHEN A.doc_type_id = vDocTypeDebtNoteAp THEN vActivityGlIdDebtNoteAp ELSE vActivityGlIdCreditNoteAp END,
582 vEmptyId, vCurrCode, ABS(B.nett_amount::numeric), C.tax_id, C.percentage, ABS(B.gov_tax_amount::numeric),
583 B.remark, 0, vDatetime, vUserId, vDatetime,
584 vUserId, vEmptyId
585 FROM tt_ul_invoice_ap A
586 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
587 INNER JOIN ul_import_data_saldo_ap B ON A.doc_type_id = B.doc_type_id
588 AND A.doc_no = B.nomor_dokumen
589 AND A.doc_date = B.tanggal_dokumen
590 AND AX.ou_code = B.kode_ou
591 INNER JOIN m_tax C ON C.tenant_id = vTenantId
592 WHERE B.upload_header_id = pUploadHeaderId
593 AND B.status = vOk
594 AND A.upload_header_id = B.upload_header_id
595 AND A.action_type = vActionUpdate;
596
597 UPDATE fi_invoice_ap_balance A SET
598 ext_doc_no=B.ext_doc_no,
599 ext_doc_date=B.ext_doc_date,
600 ref_id=B.ref_id,
601 ref_doc_type_id=B.ref_doc_type_id,
602 partner_id=B.partner_id,
603 due_date=B.due_date,
604 amount=(C.nett_amount::numeric + C.gov_tax_amount::numeric),
605 remark=C.remark,
606 payment_amount=(C.nett_amount::numeric + C.gov_tax_amount::numeric - C.balance_nett_amount::numeric - C.balance_gov_tax_amount::numeric),
607 flg_payment = vNo,
608 version=A.version+1,
609 update_datetime=vDatetime,
610 update_user_id=vUserId
611 FROM tt_ul_invoice_ap B
612 INNER JOIN t_ou BX ON B.ou_id = BX.ou_id
613 INNER JOIN ul_import_data_saldo_ap 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 C.status = vOk
619 AND B.upload_header_id = C.upload_header_id
620 AND B.action_type = vActionUpdate
621 AND A.tenant_id = B.tenant_id
622 AND A.doc_type_id = B.doc_type_id
623 AND A.invoice_ap_id = B.invoice_ap_id;
624
625 UPDATE fi_summary_monthly_ap A SET
626 invoice_amount=C.amount,
627 balance_amount=C.amount,
628 payment_amount=C.payment_amount,
629 version=A.version+1,
630 update_datetime=vDatetime,
631 update_user_id=vUserId
632 FROM tt_ul_invoice_ap B
633 INNER JOIN fi_invoice_ap_balance C ON B.tenant_id = C.tenant_id AND B.doc_type_id = C.doc_type_id AND B.invoice_ap_id = C.invoice_ap_id
634 INNER JOIN m_ou_structure D ON B.ou_id = D.ou_id
635 WHERE B.upload_header_id = pUploadHeaderId
636 AND B.action_type = vActionUpdate
637 AND A.tenant_id = B.tenant_id
638 AND A.date_year_month = vYearMonth
639 AND A.ou_id = D.ou_bu_id
640 AND A.doc_type_id = B.doc_type_id
641 AND A.invoice_id = C.invoice_ap_balance_id;
642
643 END IF;
644
645 -- ACTION FOR INSERT
646 IF EXISTS (SELECT 1 FROM tt_ul_invoice_ap WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
647
648 INSERT INTO fi_invoice_ap_tax(
649 tenant_id, invoice_ap_id, tax_id, flg_amount,
650 tax_percentage, base_amount, tax_amount, tax_no, tax_date, tax_curr_code,
651 gov_tax_amount, remark, version, create_datetime, create_user_id,
652 update_datetime, update_user_id)
653 SELECT vTenantId, A.invoice_ap_id, C.tax_id, C.flg_amount,
654 C.percentage, ABS(B.nett_amount::numeric), ABS(B.gov_tax_amount::numeric), B.nomor_pajak, B.tanggal_pajak, vCurrCode,
655 ABS(B.gov_tax_amount::numeric), B.remark, 0, vDatetime, vUserId,
656 vDatetime, vUserId
657 FROM tt_ul_invoice_ap A
658 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
659 INNER JOIN ul_import_data_saldo_ap B ON A.doc_type_id = B.doc_type_id
660 AND A.doc_no = B.nomor_dokumen
661 AND A.doc_date = B.tanggal_dokumen
662 AND AX.ou_code = B.kode_ou
663 INNER JOIN m_tax C ON C.tenant_id = vTenantId
664 WHERE B.upload_header_id = pUploadHeaderId
665 AND B.status = vOk
666 AND A.upload_header_id = B.upload_header_id
667 AND A.action_type = vActionInsert;
668
669 INSERT INTO fi_invoice_ap_cost(
670 tenant_id, invoice_ap_id, line_no, activity_gl_id,
671 ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
672 remark, version, create_datetime, create_user_id, update_datetime,
673 update_user_id, segment_id)
674 SELECT vTenantId, A.invoice_ap_id, ROW_NUMBER() OVER (PARTITION BY A.invoice_ap_id), CASE WHEN A.doc_type_id = vDocTypeDebtNoteAp THEN vActivityGlIdDebtNoteAp ELSE vActivityGlIdCreditNoteAp END,
675 vEmptyId, vCurrCode, ABS(B.nett_amount::numeric), C.tax_id, C.percentage, ABS(B.gov_tax_amount::numeric),
676 B.remark, 0, vDatetime, vUserId, vDatetime,
677 vUserId, vEmptyId
678 FROM tt_ul_invoice_ap A
679 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
680 INNER JOIN ul_import_data_saldo_ap 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_ap_balance (
691 tenant_id, ou_id, doc_type_id, invoice_ap_id,
692 doc_no, doc_date, ext_doc_no, ext_doc_date,
693 ref_doc_type_id, ref_id, partner_id, due_date,
694 curr_code, amount, remark,
695 payment_amount, flg_payment,
696 version, create_datetime, create_user_id, update_datetime, update_user_id)
697 SELECT vTenantId, A.ou_id, A.doc_type_id, A.invoice_ap_id,
698 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
699 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
700 vCurrCode, (B.nett_amount::numeric + B.gov_tax_amount::numeric), A.remark,
701 (B.nett_amount::numeric + B.gov_tax_amount::numeric - B.balance_nett_amount::numeric - B.balance_gov_tax_amount::numeric), vNo,
702 0, vDatetime, vUserId, vDatetime, vUserId
703 FROM tt_ul_invoice_ap A
704 INNER JOIN t_ou AX ON A.ou_id = AX.ou_id
705 INNER JOIN ul_import_data_saldo_ap B ON A.doc_type_id = B.doc_type_id
706 AND A.doc_no = B.nomor_dokumen
707 AND A.doc_date = B.tanggal_dokumen
708 AND AX.ou_code = B.kode_ou
709 WHERE B.upload_header_id = pUploadHeaderId
710 AND B.status = vOk
711 AND A.upload_header_id = B.upload_header_id
712 AND A.action_type = vActionInsert;
713
714 INSERT INTO fi_summary_monthly_ap(
715 date_year_month, tenant_id, ou_id,
716 doc_type_id, invoice_id, invoice_amount, balance_amount, payment_amount,
717 version, create_datetime, create_user_id, update_datetime, update_user_id,
718 curr_code)
719 SELECT vYearMonth, vTenantId, (f_get_ou_bu_structure(A.ou_id)).ou_bu_id,
720 A.doc_type_id, B.invoice_ap_balance_id, B.amount, B.amount, B.payment_amount,
721 0, vDatetime, vUserId, vDatetime, vUserId,
722 vCurrCode
723 FROM tt_ul_invoice_ap A
724 INNER JOIN fi_invoice_ap_balance B ON A.tenant_id = B.tenant_id AND A.doc_type_id = B.doc_type_id AND A.invoice_ap_id = B.invoice_ap_id
725 WHERE A.action_type = vActionInsert
726 AND A.upload_header_id = pUploadHeaderId;
727
728 END IF;
729
730
731 -- UPDATE STATUS flg_payment PADA fi_invoice_ap_balance MENJADI Y UNTUK DATA DIMANA AMOUNT = PAYMENT AMOUNT
732 UPDATE fi_invoice_ap_balance A SET
733 flg_payment= vYes,
734 version=A.version+1,
735 update_datetime=vDatetime,
736 update_user_id=vUserId
737 WHERE A.amount = A.payment_amount;
738
739 /* 20200515, by HS
740 Data yg di fi_invoice_ap_balance & fi_summary_monthly_ap tetap dibiarkan,
741 jika memang dokumen invoice ar nya tidak ada didata upload nya
742 */
743
744 -- RESET temp table
745 DELETE FROM tt_ul_invoice_ap WHERE upload_header_id = pUploadHeaderId;
746
747 END IF;
748
749 RETURN vCountFail;
750
751END;
752$BODY$
753 LANGUAGE plpgsql VOLATILE
754 COST 100;
755 /