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