· 6 years ago · Nov 28, 2019, 07:34 AM
1-- Function: public.ul_upload_partner(bigint)
2
3-- DROP FUNCTION public.ul_upload_partner(bigint);
4
5CREATE OR REPLACE FUNCTION public.ul_upload_partner(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 vGood character varying(1) := 'G';
16 vActionInsert character varying(1) := 'I';
17 vActionUpdate character varying(1) := 'U';
18 vActionError character varying(1) := 'E';
19 vGroupCustomer character varying(1) := 'C';
20 vEmpty character varying(1) := '';
21 vSpaceValue character varying(1) := ' ';
22
23 vTenantId bigint;
24 vUserId bigint;
25 vTypePartnerId bigint;
26 vOuCompanyId bigint;
27 vDatetime character varying(14);
28 vCurrCode character varying;
29
30 vEmptyId bigint := -99;
31 vCountFail bigint := 0;
32
33 vComboIdPriceLevel character varying := 'PRICELEVEL';
34 vComboIdPartnerRank character varying := 'PARTNERRANK';
35 vComboIdCountry character varying := 'COUNTRY';
36 vComboIdLineOfBusiness character varying := 'LINEOFBUSINESS';
37 vComboIdIndustryType character varying := 'INDUSTRYTYPE';
38 vComboIdTaxCode character varying := 'TAXCODE';
39 vComboIdDepartment character varying := 'DEPARTMENT';
40 vComboIdJobLevel character varying := 'JOBLEVEL';
41 vComboIdYesno character varying := 'YESNO';
42 vComboIdBank character varying := 'BANK';
43 vComboIdCurrency character varying := 'CURRENCY';
44 vComboIdDay character varying := 'DAY';
45 vComboIdDate character varying := 'DATE';
46 vComboIdPaymentMode character varying := 'PAYMENTMODE';
47
48 vGroupPartnerEmployee character varying := 'E';
49 vGroupPartnerSupplier character varying := 'S';
50 vGroupPartnerCustomer character varying := 'C';
51 vTypePartnerSupplierInternal character varying := 'SUPI';
52 vTypePartnerCustomerInternal character varying := 'CUSI';
53
54BEGIN
55 -- siapkan parameter
56 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
57 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
58 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
59
60 -- Update kolom yang kosong ke nilai default
61 UPDATE ul_import_data_partner A
62 SET garis_bujur = '0'
63 WHERE A.upload_header_id = pUploadHeaderId
64 AND TRIM(A.garis_bujur) = vEmpty;
65
66 UPDATE ul_import_data_partner A
67 SET garis_lintang = '0'
68 WHERE A.upload_header_id = pUploadHeaderId
69 AND TRIM(A.garis_lintang) = vEmpty;
70
71 -- Validasi
72 -- # Kode partner harus diisi, dan Max character kode partner 50
73 -- # Kode partner harus belum ada dalam sistem
74 -- # Nama partner harus diisi, Max character nama partner adalah 1024
75 -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
76 -- # Nama eksternal jika diisi, maka max characternya harus 100
77 -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
78 -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
79 -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
80 -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
81 -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
82 -- # Flg Holding wajib diisi dengan nilai Y/N
83 -- # Jika diisi, Kode Partner Holding tidak boleh dirirnya sendiri
84 -- # Jika diisi, Kode Partner Holding harus ada dalam master partner dengan flag holding = Y atau file upload
85 -- # Address Desc wajib diisi & Max character Address Desc adalah 100
86 -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
87 -- # Max character Address 2 adalah 100 jika diisi
88 -- # Max character Address 3 adalah 100 jika diisi
89 -- # Max character City adalah 100 jika diisi
90 -- # Max character Zip Code adalah 100 jika diisi
91 -- # Max character State or Province adalah 100 jika diisi
92 -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
93 -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
94 -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
95 -- # Max character Phone 1 adalah 100, jika diisi
96 -- # Max character Phone 2 adalah 100, jika diisi
97 -- # Max character Fax 1 adalah 100, jika diisi
98 -- # Max character Fax 2 adalah 100, jika diisi
99 -- # Mailing Addr Wajib diisi dengan nilai Y/N
100 -- # Shipping Addr Wajib diisi dengan nilai Y/N
101 -- # Billing Addr Wajib diisi dengan nilai Y/N
102 -- # Others Addr Wajib diisi dengan nilai Y/N
103 -- # Validasi tidak boleh ada partner code yang duplikat dalam 1 csv
104
105 -- # PARTNER CP
106 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
107 -- # Nama CP, Pekerjaan CP, Kode Departemen CP, Kode Level Jabatan CP, Flag Notifikasi AP, Flag Notifikasi AR, Flag Responsibility
108 -- # Nama CP wajib diisi, dan max character Nama CP adalah 100
109 -- # Pekerjaan CP wajib diisi, dan max character Pekerjaan CP adalah 100
110 -- # Kode Departemen CP wajib diisi, dan harus ada dalam combo DEPARTMENT
111 -- # Kode Level Jabatan CP wajib diisi, dan harus ada dalam combo JOBLEVEL
112 -- # Jika diisi, max character Email CP adalah 100 dan harus dalam format email
113 -- # Flag Notifikasi AP wajib diisi, dan harus ada dalam combo YESNO
114 -- # Flag Notifikasi AR wajib diisi, dan harus ada dalam combo YESNO
115 -- # Jika diisi, max character Telepon 1 CP adalah 100
116 -- # Jika diisi, max character Telepon 2 CP adalah 100
117 -- # Jika diisi, max character Telepon Eksternal 1 CP adalah 100
118 -- # Jika diisi, max character Telepon Eksternal 2 CP adalah 100
119 -- # Jika diisi, max character Ponsel 1 CP adalah 100
120 -- # Jika diisi, max character Ponsel 2 CP adalah 100
121 -- # Jika diisi, max character Fax 1 CP adalah 100
122 -- # Jika diisi, max character Fax 2 CP adalah 100
123 -- # Flag Responsible wajib diisi, dan harus ada dalam combo YESNO
124
125 -- # PARTNER NPWP
126 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
127 -- # Nomor NPWP, Nama NPWP, Negara NPWP, Flag PKP
128 -- # Nomor NPWP wajib diisi, dan max character Nomor NPWP adalah 50
129 -- # Nama NPWP wajib diisi, dan max character Nama NPWP adalah 1024
130 -- # Jika diisi, Tanggal NPWP harus dalam format tanggal
131 -- # Jika diisi, max character Alamat 1 NPWP adalah 100
132 -- # Jika diisi, max character Alamat 2 NPWP adalah 100
133 -- # Jika diisi, max character Alamat 3 NPWP adalah 100
134 -- # Jika diisi, max character Kota NPWP adalah 100
135 -- # Jika diisi, max character Kode Pos NPWP adalah 100
136 -- # Jika diisi, max character Telepon 1 NPWP adalah 100
137 -- # Jika diisi, max character Telepon 2 NPWP adalah 100
138 -- # Jika diisi, max character Fax 1 NPWP adalah 100
139 -- # Jika diisi, max character Fax 2 NPWP adalah 100
140 -- # Negara NPWP wajib diisi, dan Negara NPWP harus ada dalam combo COUNTRY
141 -- # Flag PKP wajib diisi, dan Flag PKP harus ada dalam combo YESNO
142 -- # Validasi tidak boleh ada Nomor NPWP yang duplikat dalam 1 csv
143
144 -- # PARTNER BANK
145 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
146 -- # Kode Bank, Nomor Rekening, Kode Valuta Rekening, Nama Rekening 1
147 -- # Kode Bank wajib diisi, dan harus ada dalam combo BANK
148 -- # Nomor Rekening wajib diisi, max character Nomor Rekening adalah 50
149 -- # Kode Valuta Rekening wajib diisi, dan harus ada dalam combo CURRENCY
150 -- # Nama Rekening 1 wajib diisi, max character Nama Rekening 1 adalah 100
151 -- # Jika diisi, max character Nama Rekening 2 adalah 100
152 -- # Validasi tidak boleh ada Kode Partner - Kode Bank - Nomor Rekening yang duplikat dalam 1 csv
153
154 -- # PARTNER AS CUSTOMER
155 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
156 -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
157 -- Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
158 -- Tanggal Pembayaran Customer, Diskon Reguler
159 -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C
160 -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
161 -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
162 -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
163 -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
164 -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
165 -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
166 -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
167 -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
168 -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
169 -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
170 -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100
171
172 -- # PARTNER REL
173 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
174 -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman
175 -- # Data Manage As Customer atas partner harus diisi
176 -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer,
177 -- bukan employee, dan bukan dirinya sendiri
178 -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
179 -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO
180
181 -- # PARTNER AS SUPPLIER
182 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
183 -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman
184 -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
185 -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
186 -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
187 -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
188 -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0
189
190
191 -- # Kode partner harus diisi, dan Max character kode partner 50
192 UPDATE ul_import_data_partner A
193 SET status = vFail,
194 message = message || 'Kode Partner harus diisi, '
195 WHERE A.upload_header_id = pUploadHeaderId
196 AND TRIM(A.kode_partner) = vEmpty;
197
198 UPDATE ul_import_data_partner A
199 SET status = vFail,
200 message = message || 'Kode Partner maksimal 50 karakter, '
201 WHERE A.upload_header_id = pUploadHeaderId
202 AND length(A.kode_partner) > 50;
203
204 -- # Kode partner harus belum ada dalam sistem
205 UPDATE ul_import_data_partner A
206 SET status = vFail,
207 message = message || 'Kode Partner sudah ada dalam sistem, '
208 WHERE A.upload_header_id = pUploadHeaderId
209 AND EXISTS (
210 SELECT 1
211 FROM m_partner Z
212 WHERE Z.partner_code = A.kode_partner
213 AND Z.tenant_id = vTenantId
214 );
215
216 -- # Nama partner harus diisi, Max character nama partner adalah 1024
217 UPDATE ul_import_data_partner A
218 SET status = vFail,
219 message = message || 'Nama Partner harus diisi, '
220 WHERE A.upload_header_id = pUploadHeaderId
221 AND TRIM(A.nama_partner) = vEmpty;
222
223 UPDATE ul_import_data_partner A
224 SET status = vFail,
225 message = message || 'Nama Partner maksimal 1024 karakter, '
226 WHERE A.upload_header_id = pUploadHeaderId
227 AND length(A.nama_partner) > 1024;
228
229 -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
230 UPDATE ul_import_data_partner A
231 SET status = vFail,
232 message = message || 'Kode Kategori Partner harus diisi, '
233 WHERE A.upload_header_id = pUploadHeaderId
234 AND TRIM(A.kode_kategori_partner) = vEmpty;
235
236 UPDATE ul_import_data_partner A
237 SET status = vFail,
238 message = message || 'Kode Kategori Partner tidak ada dalam sistem, '
239 WHERE A.upload_header_id = pUploadHeaderId
240 AND TRIM(A.kode_kategori_partner) <> vEmpty
241 AND NOT EXISTS(
242 SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori_partner AND Z.tenant_id = vTenantId
243 );
244
245 -- # Nama external jika diisi, maka max characternya harus 100
246 UPDATE ul_import_data_partner A
247 SET status = vFail,
248 message = message || 'Nama Eksternal maksimal 100 karakter, '
249 WHERE A.upload_header_id = pUploadHeaderId
250 AND TRIM(A.nama_eksternal) <> vEmpty
251 AND length(A.nama_eksternal) > 100;
252
253 -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
254 UPDATE ul_import_data_partner A
255 SET status = vFail,
256 message = message || 'Level Harga harus diisi, '
257 WHERE A.upload_header_id = pUploadHeaderId
258 AND TRIM(A.level_harga) = vEmpty;
259
260 UPDATE ul_import_data_partner A
261 SET status = vFail,
262 message = message || 'Level Harga tidak ada dalam sistem, '
263 WHERE A.upload_header_id = pUploadHeaderId
264 AND TRIM(A.level_harga) <> vEmpty
265 AND NOT EXISTS(
266 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPriceLevel AND Z.code = A.level_harga
267 );
268
269 -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
270 UPDATE ul_import_data_partner A
271 SET status = vFail,
272 message = message || 'Rank harus diisi, '
273 WHERE A.upload_header_id = pUploadHeaderId
274 AND TRIM(A.ranking) = vEmpty;
275
276 UPDATE ul_import_data_partner A
277 SET status = vFail,
278 message = message || 'Rank tidak ada dalam sistem, '
279 WHERE A.upload_header_id = pUploadHeaderId
280 AND TRIM(A.ranking) <> vEmpty
281 AND NOT EXISTS(
282 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPartnerRank AND Z.code = A.ranking
283 );
284
285 -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
286 UPDATE ul_import_data_partner A
287 SET status = vFail,
288 message = message || 'Bidang Usaha tidak ada dalam sistem, '
289 WHERE A.upload_header_id = pUploadHeaderId
290 AND TRIM(A.bidang_usaha) <> vEmpty
291 AND NOT EXISTS(
292 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdLineOfBusiness AND Z.code = A.bidang_usaha
293 );
294
295 -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
296 UPDATE ul_import_data_partner A
297 SET status = vFail,
298 message = message || 'Jenis Industri harus diisi, '
299 WHERE A.upload_header_id = pUploadHeaderId
300 AND TRIM(A.jenis_industri) = vEmpty;
301
302 UPDATE ul_import_data_partner A
303 SET status = vFail,
304 message = message || 'Jenis industri tidak ada dalam sistem, '
305 WHERE A.upload_header_id = pUploadHeaderId
306 AND TRIM(A.jenis_industri) <> vEmpty
307 AND NOT EXISTS(
308 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdIndustryType AND Z.code = A.jenis_industri
309 );
310
311 -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
312 UPDATE ul_import_data_partner A
313 SET status = vFail,
314 message = message || 'Kode Status NPWP harus diisi, '
315 WHERE A.upload_header_id = pUploadHeaderId
316 AND TRIM(A.kode_status_npwp) = vEmpty;
317
318 UPDATE ul_import_data_partner A
319 SET status = vFail,
320 message = message || 'Kode Status NPWP tidak ada dalam sistem, '
321 WHERE A.upload_header_id = pUploadHeaderId
322 AND TRIM(A.kode_status_npwp) <> vEmpty
323 AND NOT EXISTS(
324 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdTaxCode AND Z.code = A.kode_status_npwp
325 );
326
327 -- # Flg Holding wajib diisi dengan nilai Y/N
328 UPDATE ul_import_data_partner A
329 SET status = vFail,
330 message = message || 'Flag Holding hanya dapat berisi Y atau N, '
331 WHERE A.upload_header_id = pUploadHeaderId
332 AND A.flag_holding NOT IN (vYes, vNo);
333
334 -- # Jika diisi, Kode Partner Holding tidak boleh dirinya sendiri
335 UPDATE ul_import_data_partner A
336 SET status = vFail,
337 message = message || 'Kode Partner Holding harus berbeda dengan Kode Partner, '
338 WHERE A.upload_header_id = pUploadHeaderId
339 AND TRIM(A.kode_partner_holding) <> vEmpty
340 AND A.kode_partner_holding = A.kode_partner;
341
342 -- # Jika diisi, Kode Partner Holding harus ada dalam master partner dengan flag holding = Y atau file upload
343 UPDATE ul_import_data_partner A
344 SET status = vFail,
345 message = message || 'Kode Partner Holding tidak ada dalam sistem ataupun CSV, '
346 WHERE A.upload_header_id = pUploadHeaderId
347 AND TRIM(A.kode_partner_holding) <> vEmpty
348 AND A.kode_partner_holding <> A.kode_partner
349 AND NOT EXISTS(
350 SELECT 1
351 FROM m_partner Z
352 WHERE Z.flg_holding = vYes
353 AND Z.tenant_id = vTenantId
354 AND Z.active = vYes
355 AND Z.partner_code = A.kode_partner_holding
356 ) AND NOT EXISTS(
357 SELECT 1
358 FROM ul_import_data_partner Z
359 WHERE Z.upload_header_id = A.upload_header_id
360 AND Z.flag_holding = vYes
361 AND Z.kode_partner = A.kode_partner_holding
362 );
363
364 -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
365 UPDATE ul_import_data_partner A
366 SET status = vFail,
367 message = message || 'Deskripsi Alamat harus diisi, '
368 WHERE A.upload_header_id = pUploadHeaderId
369 AND TRIM(A.kode_partner) <> vEmpty
370 AND TRIM(A.deskripsi_alamat) = vEmpty;
371
372 UPDATE ul_import_data_partner A
373 SET status = vFail,
374 message = message || 'Deskripsi Alamat maksima 100 karakter, '
375 WHERE A.upload_header_id = pUploadHeaderId
376 AND TRIM(A.deskripsi_alamat) <> vEmpty
377 AND length(A.deskripsi_alamat) > 100;
378
379 -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
380 UPDATE ul_import_data_partner A
381 SET status = vFail,
382 message = message || 'Alamat 1 wajib diisi, '
383 WHERE A.upload_header_id = pUploadHeaderId
384 AND TRIM(A.alamat_1) = vEmpty;
385
386 UPDATE ul_import_data_partner A
387 SET status = vFail,
388 message = message || 'Alamat 1 maksimal 100 karakter, '
389 WHERE A.upload_header_id = pUploadHeaderId
390 AND length(A.alamat_1) > 100;
391
392 -- # Max character Address 2 adalah 100 jika diisi
393 UPDATE ul_import_data_partner A
394 SET status = vFail,
395 message = message || 'Alamat 2 maksimal 100 karakter, '
396 WHERE A.upload_header_id = pUploadHeaderId
397 AND TRIM(A.alamat_2) <> vEmpty
398 AND length(A.alamat_2) > 100;
399
400 -- # Max character Address 3 adalah 100 jika diisi
401 UPDATE ul_import_data_partner A
402 SET status = vFail,
403 message = message || 'Alamat 3 maksimal 100 karakter, '
404 WHERE A.upload_header_id = pUploadHeaderId
405 AND TRIM(A.alamat_3) <> vEmpty
406 AND length(A.alamat_3) > 100;
407
408 -- # Max character City adalah 100 jika diisi
409 UPDATE ul_import_data_partner A
410 SET status = vFail,
411 message = message || 'Kota harus diisi, '
412 WHERE A.upload_header_id = pUploadHeaderId
413 AND TRIM(A.kota) = vEmpty;
414
415 -- # Max character Zip Code adalah 100 jika diisi
416 UPDATE ul_import_data_partner A
417 SET status = vFail,
418 message = message || 'Kode Pos maksimal 100 karakter, '
419 WHERE A.upload_header_id = pUploadHeaderId
420 AND length(A.kode_pos) > 100;
421
422 -- # Max character State or Province adalah 100 jika diisi
423 UPDATE ul_import_data_partner A
424 SET status = vFail,
425 message = message || 'Provinsi maksimal 100 karakter, '
426 WHERE A.upload_header_id = pUploadHeaderId
427 AND length(A.provinsi) > 100;
428
429 -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
430 UPDATE ul_import_data_partner A
431 SET status = vFail,
432 message = message || 'Kode Negara harus diisi, '
433 WHERE A.upload_header_id = pUploadHeaderId
434 AND TRIM(A.kode_negara) = vEmpty;
435
436 UPDATE ul_import_data_partner A
437 SET status = vFail,
438 message = message || 'Kode Negara tidak ada dalam sistem, '
439 WHERE A.upload_header_id = pUploadHeaderId
440 AND TRIM(A.kode_negara) <> vEmpty
441 AND NOT EXISTS(
442 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdCountry AND Z.code = A.kode_negara
443 );
444
445 -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
446 UPDATE ul_import_data_partner A
447 SET status = vFail,
448 message = message || 'Garis Bujur harus numerik, '
449 WHERE A.upload_header_id = pUploadHeaderId
450 AND TRIM(A.garis_bujur) <> vEmpty
451 AND NOT is_numeric(A.garis_bujur);
452
453 -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
454 UPDATE ul_import_data_partner A
455 SET status = vFail,
456 message = message || 'Garis Lintang harus numerik, '
457 WHERE A.upload_header_id = pUploadHeaderId
458 AND TRIM(A.garis_lintang) <> vEmpty
459 AND NOT is_numeric(A.garis_lintang);
460
461 -- # Max character Phone 1 adalah 100, jika diisi
462 UPDATE ul_import_data_partner A
463 SET status = vFail,
464 message = message || 'Telepon 1 maksimal 100 karakter, '
465 WHERE A.upload_header_id = pUploadHeaderId
466 AND TRIM(A.telepon_1) <> vEmpty
467 AND length(A.telepon_1) > 100;
468
469 -- # Max character Phone 2 adalah 100, jika diisi
470 UPDATE ul_import_data_partner A
471 SET status = vFail,
472 message = message || 'Telepon 2 maksimal 100 karakter, '
473 WHERE A.upload_header_id = pUploadHeaderId
474 AND TRIM(A.telepon_2) <> vEmpty
475 AND length(A.telepon_2) > 100;
476
477 -- # Max character Fax 1 adalah 100, jika diisi
478 UPDATE ul_import_data_partner A
479 SET status = vFail,
480 message = message || 'Fax 1 maksimal 100 karakter, '
481 WHERE A.upload_header_id = pUploadHeaderId
482 AND TRIM(A.fax_1) <> vEmpty
483 AND length(A.fax_1) > 100;
484
485 -- # Max character Fax 2 adalah 100, jika diisi
486 UPDATE ul_import_data_partner A
487 SET status = vFail,
488 message = message || 'Fax 2 maksimal 100 karakter, '
489 WHERE A.upload_header_id = pUploadHeaderId
490 AND TRIM(A.fax_2) <> vEmpty
491 AND length(A.fax_2) > 100;
492
493 -- # Mailing Addr wajib diisi dengan nilai Y/N
494 UPDATE ul_import_data_partner A
495 SET status = vFail,
496 message = message || 'Flag Mailing hanya dapat berisi Y atau N, '
497 WHERE A.upload_header_id = pUploadHeaderId
498 AND A.flag_mailing NOT IN (vYes, vNo);
499
500 -- # Shipping Addr Wajib diisi dengan nilai Y/N
501 UPDATE ul_import_data_partner A
502 SET status = vFail,
503 message = message || 'Flag Shipping hanya dapat berisi Y atau N, '
504 WHERE A.upload_header_id = pUploadHeaderId
505 AND A.flag_shipping NOT IN (vYes, vNo);
506
507 -- # Billing Addr Wajib diisi dengan nilai Y/N
508 UPDATE ul_import_data_partner A
509 SET status = vFail,
510 message = message || 'Flag Billing hanya dapat berisi Y atau N, '
511 WHERE A.upload_header_id = pUploadHeaderId
512 AND A.flag_billing NOT IN (vYes, vNo);
513
514 -- # Others Addr Wajib diisi dengan nilai Y/N
515 UPDATE ul_import_data_partner A
516 SET status = vFail,
517 message = message || 'Flag Others hanya dapat berisi Y atau N, '
518 WHERE A.upload_header_id = pUploadHeaderId
519 AND A.flag_others NOT IN (vYes, vNo);
520
521 -- # Validasi tidak boleh ada partner code yang duplikat dalam 1 csv
522 WITH duplicate_data_partner AS (
523 SELECT A.kode_partner
524 FROM ul_import_data_partner A
525 WHERE A.upload_header_id = pUploadHeaderId
526 AND TRIM(A.kode_partner) <> vEmpty
527 GROUP BY A.kode_partner
528 HAVING COUNT(1) > 1
529 )
530 UPDATE ul_import_data_partner A
531 SET status = vFail,
532 message = message || 'Kode Partner duplikat, '
533 WHERE A.upload_header_id = pUploadHeaderId
534 AND EXISTS (
535 SELECT 1 FROM duplicate_data_partner B WHERE A.kode_partner = B.kode_partner
536 );
537
538 -- # PARTNER CP
539 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
540 -- # Nama CP, Pekerjaan CP, Kode Departemen CP, Kode Level Jabatan CP, Flag Notifikasi AP, Flag Notifikasi AR, Flag Responsible
541
542 UPDATE ul_import_data_partner A
543 SET flg_cp = vYes
544 WHERE A.upload_header_id = pUploadHeaderId
545 AND (TRIM(A.nama_cp) <> vEmpty
546 OR TRIM(A.pekerjaan_cp) <> vEmpty
547 OR TRIM(A.kode_departemen_cp) <> vEmpty
548 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
549 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
550 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
551 OR TRIM(A.flag_responsible) <> vEmpty
552 OR TRIM(A.email_cp) <> vEmpty
553 OR TRIM(A.telepon_1_cp) <> vEmpty
554 OR TRIM(A.telepon_2_cp) <> vEmpty
555 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
556 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
557 OR TRIM(A.ponsel_1_cp) <> vEmpty
558 OR TRIM(A.ponsel_2_cp) <> vEmpty
559 OR TRIM(A.fax_1_cp) <> vEmpty
560 OR TRIM(A.fax_2_cp) <> vEmpty);
561
562 -- # Nama CP wajib diisi, dan max character Nama CP adalah 100
563 UPDATE ul_import_data_partner A
564 SET status = vFail,
565 message = message || 'Nama CP harus diisi, '
566 WHERE A.upload_header_id = pUploadHeaderId
567 AND TRIM(A.nama_cp) = vEmpty
568 AND A.flg_cp = vYes;
569
570 UPDATE ul_import_data_partner A
571 SET status = vFail,
572 message = message || 'Nama CP maksimal 100 karakter, '
573 WHERE A.upload_header_id = pUploadHeaderId
574 AND length(A.nama_cp) > 100
575 AND A.flg_cp = vYes;
576
577 -- # Pekerjaan CP wajib diisi, dan max character Pekerjaan CP adalah 100
578 UPDATE ul_import_data_partner A
579 SET status = vFail,
580 message = message || 'Pekerjaan CP harus diisi, '
581 WHERE A.upload_header_id = pUploadHeaderId
582 AND TRIM(A.pekerjaan_cp) = vEmpty
583 AND A.flg_cp = vYes;
584
585 UPDATE ul_import_data_partner A
586 SET status = vFail,
587 message = message || 'Pekerjaan CP maksimal 100 karakter, '
588 WHERE A.upload_header_id = pUploadHeaderId
589 AND length(A.pekerjaan_cp) > 100
590 AND A.flg_cp = vYes;
591
592 -- # Kode Departemen CP wajib diisi, dan harus ada dalam combo DEPARTMENT
593 UPDATE ul_import_data_partner A
594 SET status = vFail,
595 message = message || 'Kode Departemen CP harus diisi, '
596 WHERE A.upload_header_id = pUploadHeaderId
597 AND TRIM(A.kode_departemen_cp) = vEmpty
598 AND A.flg_cp = vYes;
599
600 UPDATE ul_import_data_partner A
601 SET status = vFail,
602 message = message || 'Kode Departemen CP tidak ada dalam sistem, '
603 WHERE A.upload_header_id = pUploadHeaderId
604 AND TRIM(A.kode_departemen_cp) <> vEmpty
605 AND A.flg_cp = vYes
606 AND NOT EXISTS(
607 SELECT 1
608 FROM t_combo_value Z
609 WHERE Z.combo_id = vComboIdDepartment
610 AND Z.code = A.kode_departemen_cp
611 );
612
613 -- # Kode Level Jabatan CP wajib diisi, dan harus ada dalam combo JOBLEVEL
614 UPDATE ul_import_data_partner A
615 SET status = vFail,
616 message = message || 'Kode Level Jabatan CP harus diisi, '
617 WHERE A.upload_header_id = pUploadHeaderId
618 AND TRIM(A.kode_level_jabatan_cp) = vEmpty
619 AND A.flg_cp = vYes;
620
621 UPDATE ul_import_data_partner A
622 SET status = vFail,
623 message = message || 'Kode Level Jabatan CP tidak ada dalam sistem, '
624 WHERE A.upload_header_id = pUploadHeaderId
625 AND TRIM(A.kode_level_jabatan_cp) <> vEmpty
626 AND A.flg_cp = vYes
627 AND NOT EXISTS(
628 SELECT 1
629 FROM t_combo_value Z
630 WHERE Z.combo_id = vComboIdJobLevel
631 AND Z.code = A.kode_level_jabatan_cp
632 );
633
634 -- # Jika diisi, max character Email CP adalah 100 dan harus dalam format email
635 UPDATE ul_import_data_partner A
636 SET status = vFail,
637 message = message || 'Email CP maksimal 100 karakter, '
638 WHERE A.upload_header_id = pUploadHeaderId
639 AND length(A.email_cp) > 100
640 AND A.flg_cp = vYes;
641
642 UPDATE ul_import_data_partner A
643 SET status = vFail,
644 message = message || 'Email CP tidak dalam format email, '
645 WHERE A.upload_header_id = pUploadHeaderId
646 AND TRIM(A.email_cp) <> vEmpty
647 AND NOT is_email(A.email_cp)
648 AND A.flg_cp = vYes;
649
650 -- # Flag Notifikasi AP wajib diisi, dan harus ada dalam combo YESNO
651 UPDATE ul_import_data_partner A
652 SET status = vFail,
653 message = message || 'Flag Notifikasi AP harus diisi, '
654 WHERE A.upload_header_id = pUploadHeaderId
655 AND TRIM(A.flag_notifikasi_ap) = vEmpty
656 AND A.flg_cp = vYes;
657
658 UPDATE ul_import_data_partner A
659 SET status = vFail,
660 message = message || 'Flag Notifikasi AP hanya dapat berisi Y atau N, '
661 WHERE A.upload_header_id = pUploadHeaderId
662 AND TRIM(A.flag_notifikasi_ap) <> vEmpty
663 AND A.flg_cp = vYes
664 AND NOT EXISTS(
665 SELECT 1
666 FROM t_combo_value Z
667 WHERE Z.combo_id = vComboIdYesNo
668 AND Z.code = A.flag_notifikasi_ap
669 );
670
671 -- # Flag Notifikasi AR wajib diisi, dan harus ada dalam combo YESNO
672 UPDATE ul_import_data_partner A
673 SET status = vFail,
674 message = message || 'Flag Notifikasi AR harus diisi, '
675 WHERE A.upload_header_id = pUploadHeaderId
676 AND TRIM(A.flag_notifikasi_ar) = vEmpty
677 AND A.flg_cp = vYes;
678
679 UPDATE ul_import_data_partner A
680 SET status = vFail,
681 message = message || 'Flag Notifikasi AR hanya dapat berisi Y atau N, '
682 WHERE A.upload_header_id = pUploadHeaderId
683 AND TRIM(A.flag_notifikasi_ar) <> vEmpty
684 AND A.flg_cp = vYes
685 AND NOT EXISTS(
686 SELECT 1
687 FROM t_combo_value Z
688 WHERE Z.combo_id = vComboIdYesNo
689 AND Z.code = A.flag_notifikasi_ar
690 );
691
692 -- # Jika diisi, max character Telepon 1 CP adalah 100
693 UPDATE ul_import_data_partner A
694 SET status = vFail,
695 message = message || 'Telepon 1 CP maksimal 100 karakter, '
696 WHERE A.upload_header_id = pUploadHeaderId
697 AND length(A.telepon_1_cp) > 100
698 AND A.flg_cp = vYes;
699
700 -- # Jika diisi, max character Telepon 2 CP adalah 100
701 UPDATE ul_import_data_partner A
702 SET status = vFail,
703 message = message || 'Telepon 2 CP maksimal 100 karakter, '
704 WHERE A.upload_header_id = pUploadHeaderId
705 AND length(A.telepon_2_cp) > 100
706 AND A.flg_cp = vYes;
707
708 -- # Jika diisi, max character Telepon Eksternal 1 CP adalah 100
709 UPDATE ul_import_data_partner A
710 SET status = vFail,
711 message = message || 'Telepon Eksternal 1 CP maksimal 100 karakter, '
712 WHERE A.upload_header_id = pUploadHeaderId
713 AND length(A.telepon_eksternal_1_cp) > 100
714 AND A.flg_cp = vYes;
715
716 -- # Jika diisi, max character Telepon Eksternal 2 CP adalah 100
717 UPDATE ul_import_data_partner A
718 SET status = vFail,
719 message = message || 'Telepon Eksternal 2 CP maksimal 100 karakter, '
720 WHERE A.upload_header_id = pUploadHeaderId
721 AND length(A.telepon_eksternal_2_cp) > 100
722 AND A.flg_cp = vYes;
723
724 -- # Jika diisi, max character Ponsel 1 CP adalah 100
725 UPDATE ul_import_data_partner A
726 SET status = vFail,
727 message = message || 'Ponsel 1 CP maksimal 100 karakter, '
728 WHERE A.upload_header_id = pUploadHeaderId
729 AND length(A.ponsel_1_cp) > 100
730 AND A.flg_cp = vYes;
731
732 -- # Jika diisi, max character Ponsel 2 CP adalah 100
733 UPDATE ul_import_data_partner A
734 SET status = vFail,
735 message = message || 'Ponsel 2 CP maksimal 100 karakter, '
736 WHERE A.upload_header_id = pUploadHeaderId
737 AND length(A.ponsel_2_cp) > 100
738 AND A.flg_cp = vYes;
739
740 -- # Jika diisi, max character Fax 1 CP adalah 100
741 UPDATE ul_import_data_partner A
742 SET status = vFail,
743 message = message || 'Fax 1 CP maksimal 100 karakter, '
744 WHERE A.upload_header_id = pUploadHeaderId
745 AND length(A.fax_1_cp) > 100
746 AND A.flg_cp = vYes;
747
748 -- # Jika diisi, max character Fax 2 CP adalah 100
749 UPDATE ul_import_data_partner A
750 SET status = vFail,
751 message = message || 'Fax 2 CP maksimal 100 karakter, '
752 WHERE A.upload_header_id = pUploadHeaderId
753 AND length(A.fax_2_cp) > 100
754 AND A.flg_cp = vYes;
755
756 -- # Flag Responsible wajib diisi, dan harus ada dalam combo YESNO
757 UPDATE ul_import_data_partner A
758 SET status = vFail,
759 message = message || 'Flag Responsible harus diisi, '
760 WHERE A.upload_header_id = pUploadHeaderId
761 AND TRIM(A.flag_responsible) = vEmpty
762 AND A.flg_cp = vYes;
763
764 UPDATE ul_import_data_partner A
765 SET status = vFail,
766 message = message || 'Flag Responsible hanya dapat berisi Y atau N, '
767 WHERE A.upload_header_id = pUploadHeaderId
768 AND TRIM(A.flag_responsible) <> vEmpty
769 AND A.flg_cp = vYes
770 AND NOT EXISTS(
771 SELECT 1
772 FROM t_combo_value Z
773 WHERE Z.combo_id = vComboIdYesNo
774 AND Z.code = A.flag_responsible
775 );
776
777 -- # PARTNER NPWP
778 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
779 -- # Nomor NPWP, Nama NPWP, Negara NPWP, Flag PKP
780
781 UPDATE ul_import_data_partner A
782 SET flg_npwp = vYes
783 WHERE A.upload_header_id = pUploadHeaderId
784 AND (TRIM(A.nomor_npwp) <> vEmpty
785 OR TRIM(A.nama_npwp) <> vEmpty
786 OR TRIM(A.kode_negara_npwp) <> vEmpty
787 OR TRIM(A.flag_pkp) <> vEmpty
788 OR TRIM(A.tanggal_npwp) <> vEmpty
789 OR TRIM(A.alamat_1_npwp) <> vEmpty
790 OR TRIM(A.alamat_2_npwp) <> vEmpty
791 OR TRIM(A.alamat_3_npwp) <> vEmpty
792 OR TRIM(A.kota_npwp) <> vEmpty
793 OR TRIM(A.kode_pos_npwp) <> vEmpty
794 OR TRIM(A.telepon_1_npwp) <> vEmpty
795 OR TRIM(A.telepon_2_npwp) <> vEmpty
796 OR TRIM(A.fax_1_npwp) <> vEmpty
797 OR TRIM(A.fax_2_npwp) <> vEmpty);
798
799 -- # Nomor NPWP wajib diisi, dan max character Nomor NPWP adalah 50
800 UPDATE ul_import_data_partner A
801 SET status = vFail,
802 message = message || 'Nomor NPWP harus diisi, '
803 WHERE A.upload_header_id = pUploadHeaderId
804 AND TRIM(A.nomor_npwp) = vEmpty
805 AND A.flg_npwp = vYes;
806
807 UPDATE ul_import_data_partner A
808 SET status = vFail,
809 message = message || 'Nomor NPWP maksimal 50 karakter, '
810 WHERE A.upload_header_id = pUploadHeaderId
811 AND length(A.nomor_npwp) > 50
812 AND A.flg_npwp = vYes;
813
814 -- # Nama NPWP wajib diisi, dan max character Nama NPWP adalah 1024
815 UPDATE ul_import_data_partner A
816 SET status = vFail,
817 message = message || 'Nama NPWP harus diisi, '
818 WHERE A.upload_header_id = pUploadHeaderId
819 AND TRIM(A.nama_npwp) = vEmpty
820 AND A.flg_npwp = vYes;
821
822 UPDATE ul_import_data_partner A
823 SET status = vFail,
824 message = message || 'Nama NPWP maksimal 1024 karakter, '
825 WHERE A.upload_header_id = pUploadHeaderId
826 AND length(A.nama_npwp) > 1024
827 AND A.flg_npwp = vYes;
828
829 -- # Jika diisi, Tanggal NPWP harus dalam format tanggal
830 UPDATE ul_import_data_partner A
831 SET status = vFail,
832 message = message || 'Tanggal NPWP harus dalam format tanggal, '
833 WHERE A.upload_header_id = pUploadHeaderId
834 AND NOT is_date(A.tanggal_npwp)
835 AND A.flg_npwp = vYes;
836
837 -- # Jika diisi, max character Alamat 1 NPWP adalah 100
838 UPDATE ul_import_data_partner A
839 SET status = vFail,
840 message = message || 'Alamat 1 NPWP maksimal 100 karakter, '
841 WHERE A.upload_header_id = pUploadHeaderId
842 AND length(A.alamat_1_npwp) > 100
843 AND A.flg_npwp = vYes;
844
845 -- # Jika diisi, max character Alamat 2 NPWP adalah 100
846 UPDATE ul_import_data_partner A
847 SET status = vFail,
848 message = message || 'Alamat 2 NPWP maksimal 100 karakter, '
849 WHERE A.upload_header_id = pUploadHeaderId
850 AND length(A.alamat_2_npwp) > 100
851 AND A.flg_npwp = vYes;
852
853 -- # Jika diisi, max character Alamat 3 NPWP adalah 100
854 UPDATE ul_import_data_partner A
855 SET status = vFail,
856 message = message || 'Alamat 3 NPWP maksimal 100 karakter, '
857 WHERE A.upload_header_id = pUploadHeaderId
858 AND length(A.alamat_3_npwp) > 100
859 AND A.flg_npwp = vYes;
860
861 -- # Jika diisi, max character Kota NPWP adalah 100
862 UPDATE ul_import_data_partner A
863 SET status = vFail,
864 message = message || 'Kota NPWP maksimal 100 karakter, '
865 WHERE A.upload_header_id = pUploadHeaderId
866 AND length(A.kota_npwp) > 100
867 AND A.flg_npwp = vYes;
868
869 -- # Jika diisi, max character Kode Pos NPWP adalah 100
870 UPDATE ul_import_data_partner A
871 SET status = vFail,
872 message = message || 'Kode Pos NPWP maksimal 100 karakter, '
873 WHERE A.upload_header_id = pUploadHeaderId
874 AND length(A.kode_pos) > 100
875 AND A.flg_npwp = vYes;
876
877 -- # Jika diisi, max character Telepon 1 NPWP adalah 100
878 UPDATE ul_import_data_partner A
879 SET status = vFail,
880 message = message || 'Telepon 1 NPWP maksimal 100 karakter, '
881 WHERE A.upload_header_id = pUploadHeaderId
882 AND length(A.telepon_1) > 100
883 AND A.flg_npwp = vYes;
884
885 -- # Jika diisi, max character Telepon 2 NPWP adalah 100
886 UPDATE ul_import_data_partner A
887 SET status = vFail,
888 message = message || 'Telepon 2 NPWP maksimal 100 karakter, '
889 WHERE A.upload_header_id = pUploadHeaderId
890 AND length(A.telepon_2) > 100
891 AND A.flg_npwp = vYes;
892
893 -- # Jika diisi, max character Fax 1 NPWP adalah 100
894 UPDATE ul_import_data_partner A
895 SET status = vFail,
896 message = message || 'Fax 1 NPWP maksimal 100 karakter, '
897 WHERE A.upload_header_id = pUploadHeaderId
898 AND length(A.fax_1_npwp) > 100
899 AND A.flg_npwp = vYes;
900
901 -- # Jika diisi, max character Fax 2 NPWP adalah 100
902 UPDATE ul_import_data_partner A
903 SET status = vFail,
904 message = message || 'Fax 2 NPWP maksimal 100 karakter, '
905 WHERE A.upload_header_id = pUploadHeaderId
906 AND length(A.fax_2_npwp) > 100
907 AND A.flg_npwp = vYes;
908
909 -- # Negara NPWP wajib diisi, dan Negara NPWP harus ada dalam combo COUNTRY
910 UPDATE ul_import_data_partner A
911 SET status = vFail,
912 message = message || 'Kode Negara NPWP harus diisi, '
913 WHERE A.upload_header_id = pUploadHeaderId
914 AND TRIM(A.kode_negara_npwp) = vEmpty
915 AND A.flg_npwp = vYes;
916
917 UPDATE ul_import_data_partner A
918 SET status = vFail,
919 message = message || 'Kode Negara NPWP tidak ada dalam sistem, '
920 WHERE A.upload_header_id = pUploadHeaderId
921 AND A.flg_npwp = vYes
922 AND NOT EXISTS(
923 SELECT 1
924 FROM t_combo_value Z
925 WHERE Z.combo_id = vComboIdCountry
926 AND Z.code = A.kode_negara_npwp
927 );
928
929 -- # Flag PKP wajib diisi, dan Flag PKP harus ada dalam combo YESNO
930 UPDATE ul_import_data_partner A
931 SET status = vFail,
932 message = message || 'Flag PKP harus diisi, '
933 WHERE A.upload_header_id = pUploadHeaderId
934 AND TRIM(A.flag_pkp) = vEmpty
935 AND A.flg_npwp = vYes;
936
937 UPDATE ul_import_data_partner A
938 SET status = vFail,
939 message = message || 'Flag PKP hanya dapat berisi Y atau N, '
940 WHERE A.upload_header_id = pUploadHeaderId
941 AND length(A.flag_pkp) > 1024
942 AND A.flg_npwp = vYes;
943
944 -- # Validasi tidak boleh ada Nomor NPWP yang duplikat dalam 1 csv
945 WITH duplicate_data_partner_npwp AS (
946 SELECT A.nomor_npwp
947 FROM ul_import_data_partner A
948 WHERE A.upload_header_id = pUploadHeaderId
949 AND TRIM(A.nomor_npwp) <> vEmpty
950 GROUP BY A.nomor_npwp
951 HAVING COUNT(1) > 1
952 )
953 UPDATE ul_import_data_partner A
954 SET status = vFail,
955 message = message || 'Nomor NPWP duplikat, '
956 WHERE A.upload_header_id = pUploadHeaderId
957 AND EXISTS (
958 SELECT 1 FROM duplicate_data_partner_npwp B WHERE A.nomor_npwp = B.nomor_npwp
959 );
960
961 -- # PARTNER BANK
962 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
963 -- # Kode Bank, Nomor Rekening, Kode Valuta Rekening, Nama Rekening 1
964
965 UPDATE ul_import_data_partner A
966 SET flg_bank = vYes
967 WHERE A.upload_header_id = pUploadHeaderId
968 AND (TRIM(A.kode_bank) <> vEmpty
969 OR TRIM(A.nomor_rekening) <> vEmpty
970 OR TRIM(A.kode_valuta_rekening) <> vEmpty
971 OR TRIM(A.nama_rekening_1) <> vEmpty
972 OR TRIM(A.nama_rekening_2) <> vEmpty);
973
974 -- # Kode Bank wajib diisi, dan harus ada dalam combo BANK
975 UPDATE ul_import_data_partner A
976 SET status = vFail,
977 flg_bank_valid = vNo,
978 message = message || 'Kode Bank harus diisi, '
979 WHERE A.upload_header_id = pUploadHeaderId
980 AND TRIM(A.kode_bank) = vEmpty
981 AND A.flg_bank = vYes;
982
983 UPDATE ul_import_data_partner A
984 SET status = vFail,
985 flg_bank_valid = vNo,
986 message = message || 'Kode Bank tidak ada dalam sistem, '
987 WHERE A.upload_header_id = pUploadHeaderId
988 AND TRIM(A.kode_bank) <> vEmpty
989 AND A.flg_bank = vYes
990 AND NOT EXISTS(
991 SELECT 1
992 FROM t_combo_value Z
993 WHERE Z.combo_id = vComboIdBank
994 AND Z.code = A.kode_bank
995 );
996
997 -- # Nomor Rekening wajib diisi, max character Nomor Rekening adalah 50
998 UPDATE ul_import_data_partner A
999 SET status = vFail,
1000 flg_bank_valid = vNo,
1001 message = message || 'Nomor Rekening harus diisi, '
1002 WHERE A.upload_header_id = pUploadHeaderId
1003 AND TRIM(A.nomor_rekening) = vEmpty
1004 AND A.flg_bank = vYes;
1005
1006 UPDATE ul_import_data_partner A
1007 SET status = vFail,
1008 flg_bank_valid = vNo,
1009 message = message || 'Nomor Rekening maksimal 50 karakter, '
1010 WHERE A.upload_header_id = pUploadHeaderId
1011 AND length(A.nomor_rekening) > 50
1012 AND A.flg_bank = vYes;
1013
1014 -- # Kode Valuta Rekening wajib diisi, dan harus ada dalam combo CURRENCY
1015 UPDATE ul_import_data_partner A
1016 SET status = vFail,
1017 flg_bank_valid = vNo,
1018 message = message || 'Kode Valuta Rekening harus diisi, '
1019 WHERE A.upload_header_id = pUploadHeaderId
1020 AND TRIM(A.kode_valuta_rekening) = vEmpty
1021 AND A.flg_bank = vYes;
1022
1023 UPDATE ul_import_data_partner A
1024 SET status = vFail,
1025 flg_bank_valid = vNo,
1026 message = message || 'Kode Valuta Rekening tidak ada dalam sistem, '
1027 WHERE A.upload_header_id = pUploadHeaderId
1028 AND TRIM(A.kode_valuta_rekening) <> vEmpty
1029 AND A.flg_bank = vYes
1030 AND NOT EXISTS(
1031 SELECT 1
1032 FROM t_combo_value Z
1033 WHERE Z.combo_id = vComboIdCurrency
1034 AND Z.code = A.kode_valuta_rekening
1035 );
1036
1037 -- # Nama 1 Rekening wajib diisi, max character Nama 1 Rekening adalah 100
1038 UPDATE ul_import_data_partner A
1039 SET status = vFail,
1040 flg_bank_valid = vNo,
1041 message = message || 'Nama Rekening 1 harus diisi, '
1042 WHERE A.upload_header_id = pUploadHeaderId
1043 AND TRIM(A.nama_rekening_1) = vEmpty
1044 AND A.flg_bank = vYes;
1045
1046 UPDATE ul_import_data_partner A
1047 SET status = vFail,
1048 flg_bank_valid = vNo,
1049 message = message || 'Nama Rekening 1 maksimal 100 karakter, '
1050 WHERE A.upload_header_id = pUploadHeaderId
1051 AND length(A.nama_rekening_1) > 100
1052 AND A.flg_bank = vYes;
1053
1054 -- # Jika diisi, max character Nama 2 Rekening adalah 100
1055 UPDATE ul_import_data_partner A
1056 SET status = vFail,
1057 flg_bank_valid = vNo,
1058 message = message || 'Nama Rekening 2 maksimal 100 karakter, '
1059 WHERE A.upload_header_id = pUploadHeaderId
1060 AND length(A.nama_rekening_2) > 100
1061 AND A.flg_bank = vYes;
1062
1063 -- # Validasi tidak boleh ada Kode Partner - Kode Bank - Nomor Rekening yang duplikat dalam 1 csv
1064 WITH duplicate_data_partner_bank AS (
1065 SELECT A.kode_partner, A.kode_bank, A.nomor_rekening
1066 FROM ul_import_data_partner A
1067 WHERE A.upload_header_id = pUploadHeaderId
1068 AND TRIM(A.kode_partner) <> vEmpty
1069 AND TRIM(A.kode_bank) <> vEmpty
1070 AND TRIM(A.nomor_rekening) <> vEmpty
1071 GROUP BY A.kode_partner, A.kode_bank, A.nomor_rekening
1072 HAVING COUNT(1) > 1
1073 )
1074 UPDATE ul_import_data_partner A
1075 SET status = vFail,
1076 flg_bank_valid = vNo,
1077 message = message || 'Pasangan Kode Partner - Kode Bank - Nomor Rekening duplikat, '
1078 WHERE A.upload_header_id = pUploadHeaderId
1079 AND EXISTS (
1080 SELECT 1
1081 FROM duplicate_data_partner_bank B
1082 WHERE A.kode_partner = B.kode_partner
1083 AND A.kode_bank = B.kode_bank
1084 AND A.nomor_rekening = B.nomor_rekening
1085 );
1086
1087 -- # PARTNER AS CUSTOMER
1088 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1089 -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
1090 -- Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
1091 -- Tanggal Pembayaran Customer, Diskon Reguler
1092
1093 UPDATE ul_import_data_partner A
1094 SET flg_customer = vYes
1095 WHERE A.upload_header_id = pUploadHeaderId
1096 AND (TRIM(A.kode_tipe_customer) <> vEmpty
1097 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1098 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1099 OR TRIM(A.flag_komisi) <> vEmpty
1100 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1101 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1102 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1103 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1104 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1105 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1106 OR TRIM(A.diskon_reguler) <> vEmpty);
1107
1108 -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C, dan bukan Internal
1109 UPDATE ul_import_data_partner A
1110 SET status = vFail,
1111 flg_customer_valid = vNo,
1112 message = message || 'Kode Tipe Customer harus diisi, '
1113 WHERE A.upload_header_id = pUploadHeaderId
1114 AND TRIM(A.kode_tipe_customer) = vEmpty
1115 AND A.flg_customer = vYes;
1116
1117 UPDATE ul_import_data_partner A
1118 SET status = vFail,
1119 flg_customer_valid = vNo,
1120 message = message || 'Kode Tipe Customer tidak ada dalam sistem, '
1121 WHERE A.upload_header_id = pUploadHeaderId
1122 AND TRIM(A.kode_tipe_customer) <> vEmpty
1123 AND A.flg_customer = vYes
1124 AND NOT EXISTS(
1125 SELECT 1
1126 FROM m_type_partner Z
1127 WHERE Z.tenant_id = vTenantId
1128 AND Z.group_partner = vGroupPartnerCustomer
1129 AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
1130 );
1131
1132 -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
1133 UPDATE ul_import_data_partner A
1134 SET status = vFail,
1135 flg_customer_valid = vNo,
1136 message = message || 'Kode Valuta Piutang harus diisi, '
1137 WHERE A.upload_header_id = pUploadHeaderId
1138 AND TRIM(A.kode_valuta_piutang) = vEmpty
1139 AND A.flg_customer = vYes;
1140
1141 UPDATE ul_import_data_partner A
1142 SET status = vFail,
1143 flg_customer_valid = vNo,
1144 message = message || 'Kode Valuta Piutang tidak ada dalam sistem, '
1145 WHERE A.upload_header_id = pUploadHeaderId
1146 AND TRIM(A.kode_valuta_piutang) <> vEmpty
1147 AND A.flg_customer = vYes
1148 AND NOT EXISTS(
1149 SELECT 1
1150 FROM t_combo_value Z
1151 WHERE Z.combo_id = vComboIdCurrency
1152 AND Z.code = A.kode_valuta_piutang
1153 );
1154
1155 -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
1156 UPDATE ul_import_data_partner A
1157 SET status = vFail,
1158 flg_customer_valid = vNo,
1159 message = message || 'Batas Jumlah Piutang harus diisi, '
1160 WHERE A.upload_header_id = pUploadHeaderId
1161 AND TRIM(A.batas_jumlah_piutang) = vEmpty
1162 AND A.flg_customer = vYes;
1163
1164 UPDATE ul_import_data_partner A
1165 SET status = vFail,
1166 flg_customer_valid = vNo,
1167 message = message || 'Batas Jumlah Piutang harus berupa bilangan bulat, '
1168 WHERE A.upload_header_id = pUploadHeaderId
1169 AND TRIM(A.batas_jumlah_piutang) <> vEmpty
1170 AND NOT is_integer(A.batas_jumlah_piutang)
1171 AND A.flg_customer = vYes;
1172
1173 WITH data_piutang_customer AS (
1174 SELECT ul_import_data_partner_id, batas_jumlah_piutang
1175 FROM ul_import_data_partner
1176 WHERE upload_header_id = pUploadHeaderId
1177 AND is_integer(batas_jumlah_piutang)
1178 )
1179 UPDATE ul_import_data_partner A
1180 SET status = vFail,
1181 flg_customer_valid = vNo,
1182 message = message || 'Batas Jumlah Piutang harus > 0, '
1183 FROM data_piutang_customer B
1184 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1185 AND B.batas_jumlah_piutang::int <= 0
1186 AND A.flg_customer = vYes;
1187
1188 -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
1189 UPDATE ul_import_data_partner A
1190 SET status = vFail,
1191 flg_customer_valid = vNo,
1192 message = message || 'Flag Komisi harus diisi, '
1193 WHERE A.upload_header_id = pUploadHeaderId
1194 AND TRIM(A.flag_komisi) = vEmpty
1195 AND A.flg_customer = vYes;
1196
1197 UPDATE ul_import_data_partner A
1198 SET status = vFail,
1199 flg_customer_valid = vNo,
1200 message = message || 'Flag Komisi hanya dapat berisi Y atau N, '
1201 WHERE A.upload_header_id = pUploadHeaderId
1202 AND TRIM(A.flag_komisi) <> vEmpty
1203 AND A.flg_customer = vYes
1204 AND NOT EXISTS(
1205 SELECT 1
1206 FROM t_combo_value Z
1207 WHERE Z.combo_id = vComboIdYesNo
1208 AND Z.code = A.flag_komisi
1209 );
1210
1211 -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
1212 UPDATE ul_import_data_partner A
1213 SET status = vFail,
1214 flg_customer_valid = vNo,
1215 message = message || 'Flag Referensi Invoice harus diisi, '
1216 WHERE A.upload_header_id = pUploadHeaderId
1217 AND TRIM(A.flag_referensi_invoice) = vEmpty
1218 AND A.flg_customer = vYes;
1219
1220 UPDATE ul_import_data_partner A
1221 SET status = vFail,
1222 flg_customer_valid = vNo,
1223 message = message || 'Flag Referensi Invoice hanya dapat berisi Y atau N, '
1224 WHERE A.upload_header_id = pUploadHeaderId
1225 AND TRIM(A.flag_referensi_invoice) <> vEmpty
1226 AND A.flg_customer = vYes
1227 AND NOT EXISTS(
1228 SELECT 1
1229 FROM t_combo_value Z
1230 WHERE Z.combo_id = vComboIdYesNo
1231 AND Z.code = A.flag_referensi_invoice
1232 );
1233
1234 -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
1235 UPDATE ul_import_data_partner A
1236 SET status = vFail,
1237 flg_customer_valid = vNo,
1238 message = message || 'Batas Pembayaran Customer harus diisi, '
1239 WHERE A.upload_header_id = pUploadHeaderId
1240 AND TRIM(A.batas_pembayaran_customer) = vEmpty
1241 AND A.flg_customer = vYes;
1242
1243 UPDATE ul_import_data_partner A
1244 SET status = vFail,
1245 flg_customer_valid = vNo,
1246 message = message || 'Batas Pembayaran Customer harus berupa angka, '
1247 WHERE A.upload_header_id = pUploadHeaderId
1248 AND TRIM(A.batas_pembayaran_customer) <> vEmpty
1249 AND NOT is_numeric(A.batas_pembayaran_customer)
1250 AND A.flg_customer = vYes;
1251
1252 WITH data_pembayaran_customer AS (
1253 SELECT ul_import_data_partner_id, batas_pembayaran_customer
1254 FROM ul_import_data_partner
1255 WHERE upload_header_id = pUploadHeaderId
1256 AND is_numeric(batas_pembayaran_customer)
1257 )
1258 UPDATE ul_import_data_partner A
1259 SET status = vFail,
1260 flg_customer_valid = vNo,
1261 message = message || 'Batas Pembayaran Customer harus >= 0, '
1262 FROM data_pembayaran_customer B
1263 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1264 AND B.batas_pembayaran_customer::numeric < 0
1265 AND A.flg_customer = vYes;
1266
1267 -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
1268 UPDATE ul_import_data_partner A
1269 SET status = vFail,
1270 flg_customer_valid = vNo,
1271 message = message || 'Cara Pembayaran Customer harus diisi, '
1272 WHERE A.upload_header_id = pUploadHeaderId
1273 AND TRIM(A.cara_pembayaran_customer) = vEmpty
1274 AND A.flg_customer = vYes;
1275
1276 UPDATE ul_import_data_partner A
1277 SET status = vFail,
1278 flg_customer_valid = vNo,
1279 message = message || 'Cara Pembayaran Customer hanya dapat berisi Y atau N, '
1280 WHERE A.upload_header_id = pUploadHeaderId
1281 AND TRIM(A.cara_pembayaran_customer) <> vEmpty
1282 AND A.flg_customer = vYes
1283 AND NOT EXISTS(
1284 SELECT 1
1285 FROM t_combo_value Z
1286 WHERE Z.combo_id = vComboIdPaymentMode
1287 AND Z.code = A.cara_pembayaran_customer
1288 );
1289
1290 -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
1291 -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
1292 UPDATE ul_import_data_partner A
1293 SET status = vFail,
1294 flg_customer_valid = vNo,
1295 message = message || 'Kode Bank Customer harus diisi, '
1296 WHERE A.upload_header_id = pUploadHeaderId
1297 AND TRIM(A.kode_bank_customer) = vEmpty
1298 AND A.flg_customer = vYes
1299 AND TRIM(A.nomor_rekening_customer) <> vEmpty;
1300
1301 UPDATE ul_import_data_partner A
1302 SET status = vFail,
1303 flg_customer_valid = vNo,
1304 message = message || 'Nomor Rekening Customer harus diisi, '
1305 WHERE A.upload_header_id = pUploadHeaderId
1306 AND TRIM(A.nomor_rekening_customer) = vEmpty
1307 AND A.flg_customer = vYes
1308 AND TRIM(A.kode_bank_customer) <> vEmpty;
1309
1310 UPDATE ul_import_data_partner A
1311 SET status = vFail,
1312 flg_customer_valid = vNo,
1313 message = message || 'Kode Bank Customer harus sama dengan Kode Bank, '
1314 WHERE A.upload_header_id = pUploadHeaderId
1315 AND TRIM(A.kode_bank_customer) <> vEmpty
1316 AND A.flg_customer = vYes
1317 AND A.kode_bank_customer <> kode_bank;
1318
1319 UPDATE ul_import_data_partner A
1320 SET status = vFail,
1321 flg_customer_valid = vNo,
1322 message = message || 'Kode Bank Customer tidak dapat digunakan, '
1323 WHERE A.upload_header_id = pUploadHeaderId
1324 AND TRIM(A.kode_bank_customer) <> vEmpty
1325 AND A.flg_customer = vYes
1326 AND A.kode_bank_customer = kode_bank
1327 AND (A.flg_bank <> vYes OR A.flg_bank_valid <> vYes);
1328
1329 UPDATE ul_import_data_partner A
1330 SET status = vFail,
1331 flg_customer_valid = vNo,
1332 message = message || 'Nomor Rekening Customer harus sama dengan Nomor Rekening, '
1333 WHERE A.upload_header_id = pUploadHeaderId
1334 AND TRIM(A.nomor_rekening_customer) <> vEmpty
1335 AND A.flg_customer = vYes
1336 AND A.nomor_rekening_customer <> nomor_rekening;
1337
1338 UPDATE ul_import_data_partner A
1339 SET status = vFail,
1340 flg_customer_valid = vNo,
1341 message = message || 'Nomor Rekening Customer tidak dapat digunakan, '
1342 WHERE A.upload_header_id = pUploadHeaderId
1343 AND TRIM(A.nomor_rekening_customer) <> vEmpty
1344 AND A.flg_customer = vYes
1345 AND A.nomor_rekening_customer = nomor_rekening
1346 AND (A.flg_bank <> vYes OR A.flg_bank_valid <> vYes);
1347
1348 -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
1349 UPDATE ul_import_data_partner A
1350 SET status = vFail,
1351 flg_customer_valid = vNo,
1352 message = message || 'Hari Pembayaran Customer harus diisi, '
1353 WHERE A.upload_header_id = pUploadHeaderId
1354 AND TRIM(A.hari_pembayaran_customer) = vEmpty
1355 AND A.flg_customer = vYes;
1356
1357 UPDATE ul_import_data_partner A
1358 SET status = vFail,
1359 flg_customer_valid = vNo,
1360 message = message || 'Hari Pembayaran Customer tidak ada dalam sistem, '
1361 WHERE A.upload_header_id = pUploadHeaderId
1362 AND TRIM(A.hari_pembayaran_customer) <> vEmpty
1363 AND A.flg_customer = vYes
1364 AND NOT EXISTS(
1365 SELECT 1
1366 FROM t_combo_value Z
1367 WHERE Z.combo_id = vComboIdDay
1368 AND Z.code = A.hari_pembayaran_customer
1369 );
1370
1371 -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
1372 UPDATE ul_import_data_partner A
1373 SET status = vFail,
1374 message = message || 'Tanggal Pembayaran Customer harus diisi, '
1375 WHERE A.upload_header_id = pUploadHeaderId
1376 AND TRIM(A.tanggal_pembayaran_customer) = vEmpty
1377 AND A.flg_customer = vYes;
1378
1379 UPDATE ul_import_data_partner A
1380 SET status = vFail,
1381 message = message || 'Tanggal Pembayaran Customer tidak ada dalam sistem, '
1382 WHERE A.upload_header_id = pUploadHeaderId
1383 AND TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1384 AND A.flg_customer = vYes
1385 AND NOT EXISTS(
1386 SELECT 1
1387 FROM t_combo_value Z
1388 WHERE Z.combo_id = vComboIdDate
1389 AND Z.code = A.tanggal_pembayaran_customer
1390 );
1391
1392 -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100
1393 UPDATE ul_import_data_partner A
1394 SET status = vFail,
1395 message = message || 'Diskon Reguler harus diisi, '
1396 WHERE A.upload_header_id = pUploadHeaderId
1397 AND TRIM(A.diskon_reguler) = vEmpty
1398 AND A.flg_customer = vYes;
1399
1400 UPDATE ul_import_data_partner A
1401 SET status = vFail,
1402 message = message || 'Diskon Reguler harus diisi, '
1403 WHERE A.upload_header_id = pUploadHeaderId
1404 AND TRIM(A.diskon_reguler) <> vEmpty
1405 AND NOT is_numeric(A.diskon_reguler)
1406 AND A.flg_customer = vYes;
1407
1408 WITH data_diskon_customer AS (
1409 SELECT ul_import_data_partner_id, diskon_reguler
1410 FROM ul_import_data_partner
1411 WHERE upload_header_id = pUploadHeaderId
1412 AND is_numeric(diskon_reguler)
1413 )
1414 UPDATE ul_import_data_partner A
1415 SET status = vFail,
1416 message = message || 'Diskon Reguler harus bernilai antara 0 s/d 100, '
1417 FROM data_diskon_customer B
1418 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1419 AND (B.diskon_reguler::numeric < 0 OR B.diskon_reguler::numeric > 100)
1420 AND A.flg_customer = vYes;
1421
1422 -- # PARTNER REL
1423 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1424 -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman
1425
1426 UPDATE ul_import_data_partner A
1427 SET flg_rel = vYes
1428 WHERE A.upload_header_id = pUploadHeaderId
1429 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1430 OR TRIM(A.flag_tagihan) <> vEmpty
1431 OR TRIM(A.flag_pengiriman) <> vEmpty);
1432
1433 -- # Data Manage As Customer atas partner harus diisi dan valid
1434 UPDATE ul_import_data_partner A
1435 SET status = vFail,
1436 message = message || 'Data Manage As Customer harus diisi dan valid, '
1437 WHERE A.upload_header_id = pUploadHeaderId
1438 AND A.flg_rel = vYes
1439 AND (A.flg_customer <> vYes OR A.flg_customer_valid <> vYes);
1440
1441 -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer
1442 -- bukan employee, dan bukan dirinya sendiri
1443 UPDATE ul_import_data_partner A
1444 SET status = vFail,
1445 message = message || 'Kode Partner Relasi harus diisi, '
1446 WHERE A.upload_header_id = pUploadHeaderId
1447 AND TRIM(A.kode_partner_relasi) = vEmpty
1448 AND A.flg_rel = vYes;
1449
1450 UPDATE ul_import_data_partner A
1451 SET status = vFail,
1452 message = message || 'Partner Relasi tidak ada dalam sistem ataupun file CSV, '
1453 WHERE A.upload_header_id = pUploadHeaderId
1454 AND TRIM(A.kode_partner_relasi) <> vEmpty
1455 AND A.flg_rel = vYes
1456 AND NOT EXISTS (
1457 SELECT 1
1458 FROM m_partner Z
1459 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
1460 WHERE Z.partner_code = A.kode_partner_relasi
1461 AND Z.tenant_id = vTenantId
1462 AND Z.active = vYes
1463 ) AND NOT EXISTS (
1464 SELECT 1
1465 FROM ul_import_data_partner Z
1466 WHERE Z.upload_header_id = A.upload_header_id
1467 AND Z.kode_partner = A.kode_partner_relasi
1468 );
1469
1470 UPDATE ul_import_data_partner A
1471 SET status = vFail,
1472 message = message || 'Partner Relasi bukan Customer, '
1473 WHERE A.upload_header_id = pUploadHeaderId
1474 AND TRIM(A.kode_partner_relasi) <> vEmpty
1475 AND A.flg_rel = vYes
1476 AND A.status <> vFail
1477 AND NOT EXISTS (
1478 SELECT 1
1479 FROM m_partner Z
1480 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
1481 WHERE Y.group_partner = vGroupPartnerCustomer
1482 AND Z.partner_code = A.kode_partner_relasi
1483 AND Z.partner_id NOT IN (
1484 SELECT X.partner_id
1485 FROM m_partner_type X
1486 WHERE X.tenant_id = vTenantId
1487 AND X.group_partner = vGroupPartnerEmployee
1488 )
1489 AND Z.tenant_id = vTenantId
1490 AND Z.active = vYes
1491 ) AND NOT EXISTS (
1492 SELECT 1
1493 FROM ul_import_data_partner Z
1494 WHERE Z.upload_header_id = A.upload_header_id
1495 AND Z.kode_partner = A.kode_partner_relasi
1496 AND Z.flg_customer = vYes
1497 );
1498
1499 UPDATE ul_import_data_partner A
1500 SET status = vFail,
1501 message = message || 'Kode Partner Relasi tidak boleh sama dengan Kode Partner, '
1502 WHERE A.upload_header_id = pUploadHeaderId
1503 AND TRIM(A.kode_partner_relasi) <> vEmpty
1504 AND A.flg_rel = vYes
1505 AND A.kode_partner_relasi = A.kode_partner;
1506
1507 -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
1508 UPDATE ul_import_data_partner A
1509 SET status = vFail,
1510 message = message || 'Flag Tagihan harus diisi, '
1511 WHERE A.upload_header_id = pUploadHeaderId
1512 AND TRIM(A.flag_tagihan) = vEmpty
1513 AND A.flg_rel = vYes;
1514
1515 UPDATE ul_import_data_partner A
1516 SET status = vFail,
1517 message = message || 'Flag Tagihan hanya dapat berisi Y atau N, '
1518 WHERE A.upload_header_id = pUploadHeaderId
1519 AND TRIM(A.flag_tagihan) <> vEmpty
1520 AND A.flg_rel = vYes
1521 AND NOT EXISTS(
1522 SELECT 1
1523 FROM t_combo_value Z
1524 WHERE Z.combo_id = vComboIdYesNo
1525 AND Z.code = A.flag_tagihan
1526 );
1527
1528 -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO
1529 UPDATE ul_import_data_partner A
1530 SET status = vFail,
1531 message = message || 'Flag Pengiriman harus diisi, '
1532 WHERE A.upload_header_id = pUploadHeaderId
1533 AND TRIM(A.flag_pengiriman) = vEmpty
1534 AND A.flg_rel = vYes;
1535
1536 UPDATE ul_import_data_partner A
1537 SET status = vFail,
1538 message = message || 'Flag Pengiriman hanya dapat berisi Y atau N, '
1539 WHERE A.upload_header_id = pUploadHeaderId
1540 AND TRIM(A.flag_pengiriman) <> vEmpty
1541 AND A.flg_rel = vYes
1542 AND NOT EXISTS(
1543 SELECT 1
1544 FROM t_combo_value Z
1545 WHERE Z.combo_id = vComboIdYesNo
1546 AND Z.code = A.flag_pengiriman
1547 );
1548
1549 -- # PARTNER AS SUPPLIER
1550 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1551 -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman
1552
1553 UPDATE ul_import_data_partner A
1554 SET flg_supplier = vYes
1555 WHERE A.upload_header_id = pUploadHeaderId
1556 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1557 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1558 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1559 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1560 OR TRIM(A.lama_pengiriman) <> vEmpty);
1561
1562 -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
1563 UPDATE ul_import_data_partner A
1564 SET status = vFail,
1565 message = message || 'Kode Tipe Supplier harus diisi, '
1566 WHERE A.upload_header_id = pUploadHeaderId
1567 AND TRIM(A.kode_tipe_supplier) = vEmpty
1568 AND A.flg_supplier = vYes;
1569
1570 UPDATE ul_import_data_partner A
1571 SET status = vFail,
1572 message = message || 'Kode Tipe Supplier tidak ada dalam sistem, '
1573 WHERE A.upload_header_id = pUploadHeaderId
1574 AND TRIM(A.kode_tipe_supplier) <> vEmpty
1575 AND A.flg_supplier = vYes
1576 AND NOT EXISTS(
1577 SELECT 1
1578 FROM m_type_partner Z
1579 WHERE Z.tenant_id = vTenantId
1580 AND Z.group_partner = vGroupPartnerSupplier
1581 AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
1582 );
1583
1584 -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
1585 UPDATE ul_import_data_partner A
1586 SET status = vFail,
1587 message = message || 'Kode Valuta Hutang harus diisi, '
1588 WHERE A.upload_header_id = pUploadHeaderId
1589 AND TRIM(A.kode_valuta_hutang) = vEmpty
1590 AND A.flg_supplier = vYes;
1591
1592 UPDATE ul_import_data_partner A
1593 SET status = vFail,
1594 message = message || 'Kode Valuta Hutang harus tidak ada dalam sistem, '
1595 WHERE A.upload_header_id = pUploadHeaderId
1596 AND TRIM(A.kode_valuta_hutang) <> vEmpty
1597 AND A.flg_supplier = vYes
1598 AND NOT EXISTS(
1599 SELECT 1
1600 FROM t_combo_value Z
1601 WHERE Z.combo_id = vComboIdCurrency
1602 AND Z.code = A.kode_valuta_hutang
1603 );
1604
1605 -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
1606 UPDATE ul_import_data_partner A
1607 SET status = vFail,
1608 message = message || 'Batas Jumlah Hutang harus diisi, '
1609 WHERE A.upload_header_id = pUploadHeaderId
1610 AND TRIM(A.batas_jumlah_hutang) = vEmpty
1611 AND A.flg_supplier = vYes;
1612
1613 UPDATE ul_import_data_partner A
1614 SET status = vFail,
1615 message = message || 'Batas Jumlah Hutang harus berupa angka, '
1616 WHERE A.upload_header_id = pUploadHeaderId
1617 AND TRIM(A.batas_jumlah_hutang) <> vEmpty
1618 AND NOT is_numeric(A.batas_jumlah_hutang)
1619 AND A.flg_supplier = vYes;
1620
1621 WITH data_hutang_supplier AS (
1622 SELECT ul_import_data_partner_id, batas_jumlah_hutang
1623 FROM ul_import_data_partner
1624 WHERE upload_header_id = pUploadHeaderId
1625 AND is_numeric(batas_jumlah_hutang)
1626 )
1627 UPDATE ul_import_data_partner A
1628 SET status = vFail,
1629 message = message || 'Batas Jumlah Hutang harus > 0, '
1630 FROM data_hutang_supplier B
1631 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1632 AND B.batas_jumlah_hutang::numeric <= 0
1633 AND A.flg_supplier = vYes;
1634
1635 -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
1636 UPDATE ul_import_data_partner A
1637 SET status = vFail,
1638 message = message || 'Batas Pembayaran Supplier harus diisi, '
1639 WHERE A.upload_header_id = pUploadHeaderId
1640 AND TRIM(A.batas_pembayaran_supplier) = vEmpty
1641 AND A.flg_supplier = vYes;
1642
1643 UPDATE ul_import_data_partner A
1644 SET status = vFail,
1645 message = message || 'Batas Pembayaran Supplier harus berupa bilangan bulat, '
1646 WHERE A.upload_header_id = pUploadHeaderId
1647 AND TRIM(A.batas_pembayaran_supplier) <> vEmpty
1648 AND NOT is_bigint(A.batas_pembayaran_supplier)
1649 AND A.flg_supplier = vYes;
1650
1651 WITH data_pembayaran_supplier AS (
1652 SELECT ul_import_data_partner_id, batas_pembayaran_supplier
1653 FROM ul_import_data_partner
1654 WHERE upload_header_id = pUploadHeaderId
1655 AND is_bigint(batas_pembayaran_supplier)
1656 )
1657 UPDATE ul_import_data_partner A
1658 SET status = vFail,
1659 message = message || 'Batas Pembayaran Supplier >= 0, '
1660 FROM data_pembayaran_supplier B
1661 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1662 AND B.batas_pembayaran_supplier::numeric < 0
1663 AND A.flg_supplier = vYes;
1664
1665 -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0
1666 UPDATE ul_import_data_partner A
1667 SET status = vFail,
1668 message = message || 'Lama Pengiriman harus diisi, '
1669 WHERE A.upload_header_id = pUploadHeaderId
1670 AND TRIM(A.lama_pengiriman) = vEmpty
1671 AND A.flg_supplier = vYes;
1672
1673 UPDATE ul_import_data_partner A
1674 SET status = vFail,
1675 message = message || 'Lama Pengiriman harus berupa bilangan bulat, '
1676 WHERE A.upload_header_id = pUploadHeaderId
1677 AND TRIM(A.lama_pengiriman) <> vEmpty
1678 AND NOT is_bigint(A.lama_pengiriman)
1679 AND A.flg_supplier = vYes;
1680
1681 WITH data_pembayaran_supplier AS (
1682 SELECT ul_import_data_partner_id, lama_pengiriman
1683 FROM ul_import_data_partner
1684 WHERE upload_header_id = pUploadHeaderId
1685 AND is_bigint(lama_pengiriman)
1686 )
1687 UPDATE ul_import_data_partner A
1688 SET status = vFail,
1689 message = message || 'Lama Pengiriman harus >= 0, '
1690 FROM data_pembayaran_supplier B
1691 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1692 AND B.lama_pengiriman::numeric < 0
1693 AND A.flg_supplier = vYes;
1694
1695
1696 -- Cek apakah ada item CSV yang statusnya fail
1697 IF EXISTS ( SELECT 1 FROM ul_import_data_partner B
1698 WHERE B.upload_header_id = pUploadHeaderId
1699 AND B.status = vFail ) THEN
1700
1701 -- Sesuaikan message, agar message paling belakang tidak ada koma
1702 UPDATE ul_import_data_partner A
1703 SET message = substr(A.message, 1, length(A.message)-2)
1704 WHERE A.upload_header_id = pUploadHeaderId
1705 AND A.status = vFail
1706 AND TRIM(A.message) != vEmpty;
1707
1708 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
1709 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
1710 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
1711 UPDATE ul_import_data_partner A
1712 SET status = vFail,
1713 update_status = vActionError
1714 WHERE upload_header_id = pUploadHeaderId;
1715
1716 -- Hitung jumlah data yang FAIL
1717 SELECT COUNT(1) INTO vCountFail
1718 FROM ul_import_data_partner
1719 WHERE upload_header_id = pUploadHeaderId
1720 AND status = vFail;
1721 ELSE
1722
1723 -- RESET table temp
1724 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
1725
1726 -- Update status non FAIL to OK
1727 UPDATE ul_import_data_partner
1728 SET status = vOk
1729 WHERE upload_header_id = pUploadHeaderId;
1730
1731 -- INSERT OK data untuk partner code baru dengan kode_partner holding kosong
1732 WITH insert_data_partner AS (
1733 INSERT INTO m_partner(
1734 tenant_id, partner_code, partner_name, ctgr_partner_id,
1735 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
1736 create_user_id, update_datetime, update_user_id, version, active,
1737 active_datetime, non_active_datetime,
1738 line_of_business, price_level,
1739 industry_type, npwp_status)
1740 SELECT vTenantId, A.kode_partner, A.nama_partner, B.ctgr_partner_id,
1741 A.nama_eksternal, vEmptyId, vEmptyId, A.flag_holding, A.ranking, vDatetime,
1742 vUserId, vDatetime, vUserId, 0, vYes,
1743 vDatetime, vSpaceValue,
1744 A.bidang_usaha, A.level_harga,
1745 A.jenis_industri, A.kode_status_npwp
1746 FROM ul_import_data_partner A
1747 INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
1748 WHERE A.upload_header_id = pUploadHeaderId
1749 AND TRIM(A.kode_partner_holding) = vEmpty
1750 AND NOT EXISTS (
1751 SELECT 1 FROM m_partner Z
1752 WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
1753 )
1754 RETURNING *
1755 ), update_ul AS (
1756 -- Ubah update_status menjadi I jika data digunakan untuk Insert
1757 UPDATE ul_import_data_partner A SET
1758 update_status = vActionInsert
1759 WHERE A.upload_header_id = pUploadHeaderId
1760 AND A.status = vOk
1761 AND EXISTS (
1762 SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
1763 )
1764 )
1765 INSERT INTO tt_ul_partner(
1766 upload_header_id, action_type, partner_id, tenant_id, partner_code,
1767 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1768 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1769 update_user_id, version, active, active_datetime, non_active_datetime,
1770 line_of_business, price_level, industry_type, npwp_status)
1771 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
1772 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1773 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1774 update_user_id, version, active, active_datetime, non_active_datetime,
1775 line_of_business, price_level, industry_type, npwp_status
1776 FROM insert_data_partner;
1777
1778 WHILE EXISTS (SELECT 1 FROM ul_import_data_partner B
1779 WHERE B.upload_header_id = pUploadHeaderId
1780 AND B.status = vOk
1781 AND COALESCE(B.update_status, vEmpty) = vEmpty) LOOP
1782
1783 -- INSERT OK data untuk partner code baru dengan kode partner holding tidak kosong
1784 WITH insert_data_partner AS (
1785 INSERT INTO m_partner(
1786 tenant_id, partner_code, partner_name, ctgr_partner_id,
1787 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
1788 create_user_id, update_datetime, update_user_id, version, active,
1789 active_datetime, non_active_datetime,
1790 line_of_business, price_level,
1791 industry_type, npwp_status)
1792 SELECT vTenantId, A.kode_partner, A.nama_partner, B.ctgr_partner_id,
1793 A.nama_eksternal, vEmptyId, C.partner_id, A.flag_holding, A.ranking, vDatetime,
1794 vUserId, vDatetime, vUserId, 0, vYes,
1795 vDatetime, vSpaceValue,
1796 A.bidang_usaha, A.level_harga,
1797 A.jenis_industri, A.kode_status_npwp
1798 FROM ul_import_data_partner A
1799 INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
1800 INNER JOIN m_partner C ON A.kode_partner_holding = C.partner_code AND C.tenant_id = vTenantId
1801 WHERE A.upload_header_id = pUploadHeaderId
1802 AND TRIM(A.kode_partner_holding) <> vEmpty
1803 AND COALESCE(A.update_status, vEmpty) = vEmpty
1804 AND NOT EXISTS (
1805 SELECT 1 FROM m_partner Z
1806 WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
1807 )
1808 RETURNING *
1809 ), update_ul AS (
1810 -- Ubah update_status menjadi I jika data digunakan untuk Insert
1811 UPDATE ul_import_data_partner A SET
1812 update_status = vActionInsert
1813 WHERE A.upload_header_id = pUploadHeaderId
1814 AND A.status = vOk
1815 AND EXISTS (
1816 SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
1817 )
1818 )
1819 INSERT INTO tt_ul_partner(
1820 upload_header_id, action_type, partner_id, tenant_id, partner_code,
1821 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1822 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1823 update_user_id, version, active, active_datetime, non_active_datetime,
1824 line_of_business, price_level, industry_type, npwp_status)
1825 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
1826 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1827 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1828 update_user_id, version, active, active_datetime, non_active_datetime,
1829 line_of_business, price_level, industry_type, npwp_status
1830 FROM insert_data_partner;
1831
1832 END LOOP ;
1833
1834 -- INSERT data partner address
1835 INSERT INTO m_partner_address(
1836 tenant_id, partner_id, address_desc, address1,
1837 address2, address3, city, zip_code, state_or_province, country,
1838 phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
1839 update_datetime, update_user_id, version, active, active_datetime,
1840 non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
1841 flg_others, longitude, latitude)
1842 SELECT vTenantId, A.partner_id, B.deskripsi_alamat, B.alamat_1,
1843 B.alamat_2, B.alamat_3, B.kota, B.kode_pos, B.provinsi, B.kode_negara,
1844 B.telepon_1, B.telepon_1, B.fax_1, B.fax_2, vYes, vDatetime, vUserId,
1845 vDatetime, vUserId, 0, vYes, vDatetime,
1846 vSpaceValue, B.flag_shipping, B.flag_billing, B.flag_mailing, vYes,
1847 B.flag_others, B.garis_bujur::numeric, B.garis_lintang::numeric
1848 FROM tt_ul_partner A
1849 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1850 WHERE A.upload_header_id = pUploadHeaderId
1851 AND A.upload_header_id = B.upload_header_id
1852 AND A.action_type = vActionInsert;
1853
1854 -- INSERT data partner cp
1855 INSERT INTO m_partner_cp(
1856 tenant_id, partner_id, cp_name, cp_job, address1,
1857 address2, address3, city, zip_code, country, phone1, phone2,
1858 fax1, fax2, email, create_datetime, create_user_id, update_datetime,
1859 update_user_id, version, active, active_datetime, non_active_datetime,
1860 department, job_level, phone_ext1, phone_ext2, mobile_phone1,
1861 mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
1862 flg_email_notif_ar)
1863 SELECT vTenantId, A.partner_id, B.nama_cp, B.pekerjaan_cp, vSpaceValue,
1864 vSpaceValue, vSpaceValue, vSpaceValue, vSpaceValue, vSpaceValue, B.telepon_1_cp, B.telepon_2_cp,
1865 B.fax_1_cp, B.fax_2_cp, B.email_cp, vDatetime, vUserId, vDatetime,
1866 vUserId, 0, vYes, vDatetime, vSpaceValue,
1867 B.kode_departemen_cp, B.kode_level_jabatan_cp, B.telepon_eksternal_1_cp, B.telepon_eksternal_2_cp, B.ponsel_1_cp,
1868 B.ponsel_2_cp, B.flag_responsible, vNo, B.flag_notifikasi_ap,
1869 B.flag_notifikasi_ar
1870 FROM tt_ul_partner A
1871 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1872 WHERE A.upload_header_id = pUploadHeaderId
1873 AND A.upload_header_id = B.upload_header_id
1874 AND A.action_type = vActionInsert
1875 AND B.flg_cp = vYes;
1876
1877 -- INSERT data partner npwp
1878 INSERT INTO m_partner_npwp(
1879 tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
1880 address1, address2, address3, city, zip_code, country, phone1,
1881 phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
1882 update_user_id, version, active, active_datetime, non_active_datetime,
1883 flg_pkp)
1884 SELECT vTenantId, A.partner_id, B.nama_npwp, B.nomor_npwp, B.tanggal_npwp,
1885 B.alamat_1_npwp, B.alamat_2_npwp, B.alamat_3_npwp, B.kota_npwp, B.kode_pos_npwp, B.kode_negara_npwp, B.telepon_1_npwp,
1886 B.telepon_2_npwp, B.fax_1_npwp, B.fax_2_npwp, vDatetime, vUserId, vDatetime,
1887 vUserId, 0, vYes, vDatetime, vSpaceValue,
1888 B.flag_pkp
1889 FROM tt_ul_partner A
1890 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1891 WHERE A.upload_header_id = pUploadHeaderId
1892 AND A.upload_header_id = B.upload_header_id
1893 AND A.action_type = vActionInsert
1894 AND B.flg_npwp = vYes;
1895
1896 -- INSERT data partner bank
1897 INSERT INTO m_partner_bank(
1898 tenant_id, partner_id, bank_code, account_no,
1899 curr_code, account_name1, account_name2, create_datetime, create_user_id,
1900 update_datetime, update_user_id, version, active, active_datetime,
1901 non_active_datetime)
1902 SELECT vTenantId, A.partner_id, B.kode_bank, B.nomor_rekening,
1903 B.kode_valuta_rekening, B.nama_rekening_1, B.nama_rekening_2, vDatetime, vUserId,
1904 vDatetime, vUserId, 0, vYes, vDatetime,
1905 vSpaceValue
1906 FROM tt_ul_partner A
1907 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1908 WHERE A.upload_header_id = pUploadHeaderId
1909 AND A.upload_header_id = B.upload_header_id
1910 AND A.action_type = vActionInsert
1911 AND B.flg_bank = vYes;
1912
1913 -- INSERT data partner customer
1914 INSERT INTO m_partner_type(
1915 tenant_id, partner_id, group_partner, type_partner_id,
1916 due_date, curr_code, amount_limit, flg_commision, create_datetime,
1917 create_user_id, update_datetime, update_user_id, version, active,
1918 active_datetime, non_active_datetime)
1919 SELECT vTenantId, A.partner_id, C.group_partner, C.type_partner_id,
1920 CAST(B.batas_pembayaran_customer AS int), B.kode_valuta_piutang, CAST(B.batas_jumlah_piutang AS numeric), B.flag_komisi, vDatetime,
1921 vUserId, vDatetime, vUserId, 0, vYes,
1922 vDatetime, vSpaceValue
1923 FROM tt_ul_partner A
1924 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1925 INNER JOIN m_type_partner C ON C.type_partner_code = B.kode_tipe_customer AND C.tenant_id = vTenantId AND C.active = vYes
1926 WHERE A.upload_header_id = pUploadHeaderId
1927 AND A.upload_header_id = B.upload_header_id
1928 AND A.action_type = vActionInsert
1929 AND B.flg_customer = vYes;
1930
1931 INSERT INTO m_cust_payment(
1932 tenant_id, partner_id, flg_invoice, due_payment_days,
1933 flg_payment_mode, payment_day, payment_date, partner_bank_id,
1934 create_datetime, create_user_id, update_datetime, update_user_id,
1935 version, active, active_datetime, non_active_datetime)
1936 SELECT vTenantId, A.partner_id, B.flag_referensi_invoice, CAST(B.batas_pembayaran_customer AS bigint),
1937 B.cara_pembayaran_customer, B.hari_pembayaran_customer, B.tanggal_pembayaran_customer, COALESCE(C.partner_bank_id, vEmptyId),
1938 vDatetime, vUserId, vDatetime, vUserId,
1939 0, vYes, vDatetime, vSpaceValue
1940 FROM tt_ul_partner A
1941 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1942 LEFT JOIN m_partner_bank C ON C.bank_code = B.kode_bank_customer AND C.account_no = B.nomor_rekening_customer
1943 WHERE A.upload_header_id = pUploadHeaderId
1944 AND A.upload_header_id = B.upload_header_id
1945 AND A.action_type = vActionInsert
1946 AND B.flg_customer = vYes;
1947
1948 INSERT INTO m_partner_rel(
1949 tenant_id, partner_id, relation_id, flg_bill,
1950 flg_shipp, create_datetime, create_user_id, update_datetime,
1951 update_user_id, version, active, active_datetime, non_active_datetime)
1952 SELECT vTenantId, A.partner_id, A.partner_id, vYes,
1953 vYes, vDatetime, vUserId, vDatetime,
1954 vUserId, 0, vYes, vDatetime, vSpaceValue
1955 FROM tt_ul_partner A
1956 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1957 WHERE A.upload_header_id = pUploadHeaderId
1958 AND A.upload_header_id = B.upload_header_id
1959 AND A.action_type = vActionInsert
1960 AND B.flg_customer = vYes;
1961
1962 INSERT INTO m_customer_ext(
1963 partner_id, tenant_id, regular_discount_percentage, create_datetime,
1964 create_user_id, update_datetime, update_user_id, version, active,
1965 active_datetime, non_active_datetime)
1966 SELECT A.partner_id, vTenantId, CAST(B.diskon_reguler AS numeric), vDatetime,
1967 vUserId, vDatetime, vUserId, 0, vYes,
1968 vDatetime, vSpaceValue
1969 FROM tt_ul_partner A
1970 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1971 WHERE A.upload_header_id = pUploadHeaderId
1972 AND A.upload_header_id = B.upload_header_id
1973 AND A.action_type = vActionInsert
1974 AND B.flg_customer = vYes;
1975
1976 -- INSERT data partner rel
1977 INSERT INTO m_partner_rel(
1978 tenant_id, partner_id, relation_id, flg_bill,
1979 flg_shipp, create_datetime, create_user_id, update_datetime,
1980 update_user_id, version, active, active_datetime, non_active_datetime)
1981 SELECT vTenantId, A.partner_id, C.partner_id, B.flag_tagihan,
1982 B.flag_pengiriman, vDatetime, vUserId, vDatetime,
1983 vUserId, 0, vYes, vDatetime, vSpaceValue
1984 FROM tt_ul_partner A
1985 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
1986 INNER JOIN m_partner C ON C.partner_code = B.kode_partner_relasi AND C.tenant_id = vTenantId AND C.active = vYes
1987 WHERE A.upload_header_id = pUploadHeaderId
1988 AND A.upload_header_id = B.upload_header_id
1989 AND A.action_type = vActionInsert
1990 AND B.flg_rel = vYes;
1991
1992 -- INSERT data partner supplier
1993 INSERT INTO m_partner_type(
1994 tenant_id, partner_id, group_partner, type_partner_id,
1995 due_date, curr_code, amount_limit, flg_commision, create_datetime,
1996 create_user_id, update_datetime, update_user_id, version, active,
1997 active_datetime, non_active_datetime)
1998 SELECT vTenantId, A.partner_id, C.group_partner, C.type_partner_id,
1999 CAST(B.batas_pembayaran_supplier AS int), B.kode_valuta_hutang, CAST(B.batas_jumlah_hutang AS numeric), vNo, vDatetime,
2000 vUserId, vDatetime, vUserId, 0, vYes,
2001 vDatetime, vSpaceValue
2002 FROM tt_ul_partner A
2003 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
2004 INNER JOIN m_type_partner C ON C.type_partner_code = B.kode_tipe_supplier AND C.tenant_id = vTenantId AND C.active = vYes
2005 WHERE A.upload_header_id = pUploadHeaderId
2006 AND A.upload_header_id = B.upload_header_id
2007 AND A.action_type = vActionInsert
2008 AND B.flg_supplier = vYes;
2009
2010 INSERT INTO m_supp_payment(
2011 tenant_id, partner_id, due_payment_days, due_receive_days,
2012 create_datetime, create_user_id, update_datetime, update_user_id,
2013 version, active, active_datetime, non_active_datetime)
2014 SELECT vTenantId, A.partner_id, CAST(B.batas_pembayaran_supplier AS bigint), CAST(B.lama_pengiriman AS bigint),
2015 vDatetime, vUserId, vDatetime, vUserId,
2016 0, vYes, vDatetime, vSpaceValue
2017 FROM tt_ul_partner A
2018 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
2019 WHERE A.upload_header_id = pUploadHeaderId
2020 AND A.upload_header_id = B.upload_header_id
2021 AND A.action_type = vActionInsert
2022 AND B.flg_supplier = vYes;
2023
2024 -- RESET table temp
2025 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
2026
2027 END IF;
2028
2029
2030 RETURN vCountFail;
2031
2032END;
2033$BODY$
2034 LANGUAGE plpgsql VOLATILE
2035 COST 100;
2036ALTER FUNCTION public.ul_upload_partner(bigint)
2037 OWNER TO sts;