· 7 years ago · Dec 21, 2018, 07:16 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 REL
151 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
152 -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman
153 -- # Data Manage As Customer atas partner harus diisi
154 -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer,
155 -- bukan employee, dan bukan dirinya sendiri
156 -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
157 -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO
158
159 -- # PARTNER AS SUPPLIER
160 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
161 -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman
162 -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
163 -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
164 -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
165 -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
166 -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0
167
168 -- # PARTNER AS CUSTOMER
169 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
170 -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
171 -- Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
172 -- Tanggal Pembayaran Customer, Diskon Reguler
173 -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C
174 -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
175 -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
176 -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
177 -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
178 -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
179 -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
180 -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
181 -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
182 -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
183 -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
184 -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100
185
186
187 -- # Kode partner harus diisi, dan Max character kode partner 50
188 UPDATE ul_import_data_partner A
189 SET status = vFail,
190 message = message || 'Kode Partner harus diisi, '
191 WHERE A.upload_header_id = pUploadHeaderId
192 AND TRIM(A.kode_partner) = vEmpty;
193
194 UPDATE ul_import_data_partner A
195 SET status = vFail,
196 message = message || 'Kode Partner maksimal 50 karakter, '
197 WHERE A.upload_header_id = pUploadHeaderId
198 AND length(A.kode_partner) > 50;
199
200 -- # Kode partner harus belum ada dalam sistem
201 UPDATE ul_import_data_partner A
202 SET status = vFail,
203 message = message || 'Kode Partner sudah ada dalam sistem, '
204 WHERE A.upload_header_id = pUploadHeaderId
205 AND EXISTS (
206 SELECT 1
207 FROM m_partner Z
208 WHERE Z.partner_code = A.kode_partner
209 AND Z.tenant_id = vTenantId
210 );
211
212 -- # Nama partner harus diisi, Max character nama partner adalah 1024
213 UPDATE ul_import_data_partner A
214 SET status = vFail,
215 message = message || 'Nama Partner harus diisi, '
216 WHERE A.upload_header_id = pUploadHeaderId
217 AND TRIM(A.nama_partner) = vEmpty;
218
219 UPDATE ul_import_data_partner A
220 SET status = vFail,
221 message = message || 'Nama Partner maksimal 1024 karakter, '
222 WHERE A.upload_header_id = pUploadHeaderId
223 AND length(A.nama_partner) > 1024;
224
225 -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
226 UPDATE ul_import_data_partner A
227 SET status = vFail,
228 message = message || 'Kode Kategori Partner harus diisi, '
229 WHERE A.upload_header_id = pUploadHeaderId
230 AND TRIM(A.kode_kategori_partner) = vEmpty;
231
232 UPDATE ul_import_data_partner A
233 SET status = vFail,
234 message = message || 'Kode Kategori Partner tidak ada dalam sistem, '
235 WHERE A.upload_header_id = pUploadHeaderId
236 AND TRIM(A.kode_kategori_partner) <> vEmpty
237 AND NOT EXISTS(
238 SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori_partner AND Z.tenant_id = vTenantId
239 );
240
241 -- # Nama external jika diisi, maka max characternya harus 100
242 UPDATE ul_import_data_partner A
243 SET status = vFail,
244 message = message || 'Nama Eksternal maksimal 100 karakter, '
245 WHERE A.upload_header_id = pUploadHeaderId
246 AND TRIM(A.nama_eksternal) <> vEmpty
247 AND length(A.nama_eksternal) > 100;
248
249 -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
250 UPDATE ul_import_data_partner A
251 SET status = vFail,
252 message = message || 'Level Harga harus diisi, '
253 WHERE A.upload_header_id = pUploadHeaderId
254 AND TRIM(A.level_harga) = vEmpty;
255
256 UPDATE ul_import_data_partner A
257 SET status = vFail,
258 message = message || 'Level Harga tidak ada dalam sistem, '
259 WHERE A.upload_header_id = pUploadHeaderId
260 AND TRIM(A.level_harga) <> vEmpty
261 AND NOT EXISTS(
262 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPriceLevel AND Z.code = A.level_harga
263 );
264
265 -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
266 UPDATE ul_import_data_partner A
267 SET status = vFail,
268 message = message || 'Rank harus diisi, '
269 WHERE A.upload_header_id = pUploadHeaderId
270 AND TRIM(A.ranking) = vEmpty;
271
272 UPDATE ul_import_data_partner A
273 SET status = vFail,
274 message = message || 'Rank tidak ada dalam sistem, '
275 WHERE A.upload_header_id = pUploadHeaderId
276 AND TRIM(A.ranking) <> vEmpty
277 AND NOT EXISTS(
278 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPartnerRank AND Z.code = A.ranking
279 );
280
281 -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
282 UPDATE ul_import_data_partner A
283 SET status = vFail,
284 message = message || 'Bidang Usaha tidak ada dalam sistem, '
285 WHERE A.upload_header_id = pUploadHeaderId
286 AND TRIM(A.bidang_usaha) <> vEmpty
287 AND NOT EXISTS(
288 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdLineOfBusiness AND Z.code = A.bidang_usaha
289 );
290
291 -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
292 UPDATE ul_import_data_partner A
293 SET status = vFail,
294 message = message || 'Jenis Industri harus diisi, '
295 WHERE A.upload_header_id = pUploadHeaderId
296 AND TRIM(A.jenis_industri) = vEmpty;
297
298 UPDATE ul_import_data_partner A
299 SET status = vFail,
300 message = message || 'Jenis industri tidak ada dalam sistem, '
301 WHERE A.upload_header_id = pUploadHeaderId
302 AND TRIM(A.jenis_industri) <> vEmpty
303 AND NOT EXISTS(
304 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdIndustryType AND Z.code = A.jenis_industri
305 );
306
307 -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
308 UPDATE ul_import_data_partner A
309 SET status = vFail,
310 message = message || 'Kode Status NPWP harus diisi, '
311 WHERE A.upload_header_id = pUploadHeaderId
312 AND TRIM(A.kode_status_npwp) = vEmpty;
313
314 UPDATE ul_import_data_partner A
315 SET status = vFail,
316 message = message || 'Kode Status NPWP tidak ada dalam sistem, '
317 WHERE A.upload_header_id = pUploadHeaderId
318 AND TRIM(A.kode_status_npwp) <> vEmpty
319 AND NOT EXISTS(
320 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdTaxCode AND Z.code = A.kode_status_npwp
321 );
322
323 -- # Flg Holding wajib diisi dengan nilai Y/N
324 UPDATE ul_import_data_partner A
325 SET status = vFail,
326 message = message || 'Flag Holding hanya dapat berisi Y atau N, '
327 WHERE A.upload_header_id = pUploadHeaderId
328 AND A.flag_holding NOT IN (vYes, vNo);
329
330 -- # Jika diisi, Kode Partner Holding tidak boleh dirinya sendiri
331 UPDATE ul_import_data_partner A
332 SET status = vFail,
333 message = message || 'Kode Partner Holding harus berbeda dengan Kode Partner, '
334 WHERE A.upload_header_id = pUploadHeaderId
335 AND TRIM(A.kode_partner_holding) <> vEmpty
336 AND A.kode_partner_holding = A.kode_partner;
337
338 -- # Jika diisi, Kode Partner Holding harus ada dalam master partner dengan flag holding = Y atau file upload
339 UPDATE ul_import_data_partner A
340 SET status = vFail,
341 message = message || 'Kode Partner Holding tidak ada dalam sistem ataupun CSV, '
342 WHERE A.upload_header_id = pUploadHeaderId
343 AND TRIM(A.kode_partner_holding) <> vEmpty
344 AND A.kode_partner_holding <> A.kode_partner
345 AND NOT EXISTS(
346 SELECT 1
347 FROM m_partner Z
348 WHERE Z.flg_holding = vYes
349 AND Z.tenant_id = vTenantId
350 AND Z.active = vYes
351 AND Z.partner_code = A.kode_partner_holding
352 ) AND NOT EXISTS(
353 SELECT 1
354 FROM ul_import_data_partner Z
355 WHERE Z.upload_header_id = A.upload_header_id
356 AND Z.flag_holding = vYes
357 AND Z.kode_partner = A.kode_partner_holding
358 );
359
360 -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
361 UPDATE ul_import_data_partner A
362 SET status = vFail,
363 message = message || 'Deskripsi Alamat harus diisi, '
364 WHERE A.upload_header_id = pUploadHeaderId
365 AND TRIM(A.kode_partner) <> vEmpty
366 AND TRIM(A.deskripsi_alamat) = vEmpty;
367
368 UPDATE ul_import_data_partner A
369 SET status = vFail,
370 message = message || 'Deskripsi Alamat maksima 100 karakter, '
371 WHERE A.upload_header_id = pUploadHeaderId
372 AND TRIM(A.deskripsi_alamat) <> vEmpty
373 AND length(A.deskripsi_alamat) > 100;
374
375 -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
376 UPDATE ul_import_data_partner A
377 SET status = vFail,
378 message = message || 'Alamat 1 wajib diisi, '
379 WHERE A.upload_header_id = pUploadHeaderId
380 AND TRIM(A.alamat_1) = vEmpty;
381
382 UPDATE ul_import_data_partner A
383 SET status = vFail,
384 message = message || 'Alamat 1 maksimal 100 karakter, '
385 WHERE A.upload_header_id = pUploadHeaderId
386 AND length(A.alamat_1) > 100;
387
388 -- # Max character Address 2 adalah 100 jika diisi
389 UPDATE ul_import_data_partner A
390 SET status = vFail,
391 message = message || 'Alamat 2 maksimal 100 karakter, '
392 WHERE A.upload_header_id = pUploadHeaderId
393 AND TRIM(A.alamat_2) <> vEmpty
394 AND length(A.alamat_2) > 100;
395
396 -- # Max character Address 3 adalah 100 jika diisi
397 UPDATE ul_import_data_partner A
398 SET status = vFail,
399 message = message || 'Alamat 3 maksimal 100 karakter, '
400 WHERE A.upload_header_id = pUploadHeaderId
401 AND TRIM(A.alamat_3) <> vEmpty
402 AND length(A.alamat_3) > 100;
403
404 -- # Max character City adalah 100 jika diisi
405 UPDATE ul_import_data_partner A
406 SET status = vFail,
407 message = message || 'Kota harus diisi, '
408 WHERE A.upload_header_id = pUploadHeaderId
409 AND TRIM(A.kota) = vEmpty;
410
411 -- # Max character Zip Code adalah 100 jika diisi
412 UPDATE ul_import_data_partner A
413 SET status = vFail,
414 message = message || 'Kode Pos maksimal 100 karakter, '
415 WHERE A.upload_header_id = pUploadHeaderId
416 AND length(A.kode_pos) > 100;
417
418 -- # Max character State or Province adalah 100 jika diisi
419 UPDATE ul_import_data_partner A
420 SET status = vFail,
421 message = message || 'Provinsi maksimal 100 karakter, '
422 WHERE A.upload_header_id = pUploadHeaderId
423 AND length(A.provinsi) > 100;
424
425 -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
426 UPDATE ul_import_data_partner A
427 SET status = vFail,
428 message = message || 'Kode Negara harus diisi, '
429 WHERE A.upload_header_id = pUploadHeaderId
430 AND TRIM(A.kode_negara) = vEmpty;
431
432 UPDATE ul_import_data_partner A
433 SET status = vFail,
434 message = message || 'Kode Negara tidak ada dalam sistem, '
435 WHERE A.upload_header_id = pUploadHeaderId
436 AND TRIM(A.kode_negara) <> vEmpty
437 AND NOT EXISTS(
438 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdCountry AND Z.code = A.kode_negara
439 );
440
441 -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
442 UPDATE ul_import_data_partner A
443 SET status = vFail,
444 message = message || 'Garis Bujur harus numerik, '
445 WHERE A.upload_header_id = pUploadHeaderId
446 AND TRIM(A.garis_bujur) <> vEmpty
447 AND NOT is_numeric(A.garis_bujur);
448
449 -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
450 UPDATE ul_import_data_partner A
451 SET status = vFail,
452 message = message || 'Garis Lintang harus numerik, '
453 WHERE A.upload_header_id = pUploadHeaderId
454 AND TRIM(A.garis_lintang) <> vEmpty
455 AND NOT is_numeric(A.garis_lintang);
456
457 -- # Max character Phone 1 adalah 100, jika diisi
458 UPDATE ul_import_data_partner A
459 SET status = vFail,
460 message = message || 'Telepon 1 maksimal 100 karakter, '
461 WHERE A.upload_header_id = pUploadHeaderId
462 AND TRIM(A.telepon_1) <> vEmpty
463 AND length(A.telepon_1) > 100;
464
465 -- # Max character Phone 2 adalah 100, jika diisi
466 UPDATE ul_import_data_partner A
467 SET status = vFail,
468 message = message || 'Telepon 2 maksimal 100 karakter, '
469 WHERE A.upload_header_id = pUploadHeaderId
470 AND TRIM(A.telepon_2) <> vEmpty
471 AND length(A.telepon_2) > 100;
472
473 -- # Max character Fax 1 adalah 100, jika diisi
474 UPDATE ul_import_data_partner A
475 SET status = vFail,
476 message = message || 'Fax 1 maksimal 100 karakter, '
477 WHERE A.upload_header_id = pUploadHeaderId
478 AND TRIM(A.fax_1) <> vEmpty
479 AND length(A.fax_1) > 100;
480
481 -- # Max character Fax 2 adalah 100, jika diisi
482 UPDATE ul_import_data_partner A
483 SET status = vFail,
484 message = message || 'Fax 2 maksimal 100 karakter, '
485 WHERE A.upload_header_id = pUploadHeaderId
486 AND TRIM(A.fax_2) <> vEmpty
487 AND length(A.fax_2) > 100;
488
489 -- # Mailing Addr wajib diisi dengan nilai Y/N
490 UPDATE ul_import_data_partner A
491 SET status = vFail,
492 message = message || 'Flag Mailing hanya dapat berisi Y atau N, '
493 WHERE A.upload_header_id = pUploadHeaderId
494 AND A.flag_mailing NOT IN (vYes, vNo);
495
496 -- # Shipping Addr Wajib diisi dengan nilai Y/N
497 UPDATE ul_import_data_partner A
498 SET status = vFail,
499 message = message || 'Flag Shipping hanya dapat berisi Y atau N, '
500 WHERE A.upload_header_id = pUploadHeaderId
501 AND A.flag_shipping NOT IN (vYes, vNo);
502
503 -- # Billing Addr Wajib diisi dengan nilai Y/N
504 UPDATE ul_import_data_partner A
505 SET status = vFail,
506 message = message || 'Flag Billing hanya dapat berisi Y atau N, '
507 WHERE A.upload_header_id = pUploadHeaderId
508 AND A.flag_billing NOT IN (vYes, vNo);
509
510 -- # Others Addr Wajib diisi dengan nilai Y/N
511 UPDATE ul_import_data_partner A
512 SET status = vFail,
513 message = message || 'Flag Others hanya dapat berisi Y atau N, '
514 WHERE A.upload_header_id = pUploadHeaderId
515 AND A.flag_others NOT IN (vYes, vNo);
516
517 -- # Validasi tidak boleh ada partner code yang duplikat dalam 1 csv
518 WITH duplicate_data_partner AS (
519 SELECT A.kode_partner
520 FROM ul_import_data_partner A
521 WHERE A.upload_header_id = pUploadHeaderId
522 AND TRIM(A.kode_partner) <> vEmpty
523 GROUP BY A.kode_partner
524 HAVING COUNT(1) > 1
525 )
526 UPDATE ul_import_data_partner A
527 SET status = vFail,
528 message = message || 'Kode Partner duplikat, '
529 WHERE A.upload_header_id = pUploadHeaderId
530 AND EXISTS (
531 SELECT 1 FROM duplicate_data_partner B WHERE A.kode_partner = B.kode_partner
532 );
533
534 -- # PARTNER CP
535 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
536 -- # Nama CP, Pekerjaan CP, Kode Departemen CP, Kode Level Jabatan CP, Flag Notifikasi AP, Flag Notifikasi AR, Flag Responsible
537
538 UPDATE ul_import_data_partner A
539 SET flg_cp = vYes
540 WHERE A.upload_header_id = pUploadHeaderId
541 AND (TRIM(A.nama_cp) <> vEmpty
542 OR TRIM(A.pekerjaan_cp) <> vEmpty
543 OR TRIM(A.kode_departemen_cp) <> vEmpty
544 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
545 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
546 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
547 OR TRIM(A.flag_responsible) <> vEmpty
548 OR TRIM(A.email_cp) <> vEmpty
549 OR TRIM(A.telepon_1_cp) <> vEmpty
550 OR TRIM(A.telepon_2_cp) <> vEmpty
551 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
552 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
553 OR TRIM(A.ponsel_1_cp) <> vEmpty
554 OR TRIM(A.ponsel_2_cp) <> vEmpty
555 OR TRIM(A.fax_1_cp) <> vEmpty
556 OR TRIM(A.fax_2_cp) <> vEmpty);
557
558 -- # Nama CP wajib diisi, dan max character Nama CP adalah 100
559 UPDATE ul_import_data_partner A
560 SET status = vFail,
561 message = message || 'Nama CP harus diisi, '
562 WHERE A.upload_header_id = pUploadHeaderId
563 AND TRIM(A.nama_cp) = vEmpty
564 AND (TRIM(A.pekerjaan_cp) <> vEmpty
565 OR TRIM(A.kode_departemen_cp) <> vEmpty
566 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
567 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
568 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
569 OR TRIM(A.flag_responsible) <> vEmpty
570 OR TRIM(A.email_cp) <> vEmpty
571 OR TRIM(A.telepon_1_cp) <> vEmpty
572 OR TRIM(A.telepon_2_cp) <> vEmpty
573 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
574 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
575 OR TRIM(A.ponsel_1_cp) <> vEmpty
576 OR TRIM(A.ponsel_2_cp) <> vEmpty
577 OR TRIM(A.fax_1_cp) <> vEmpty
578 OR TRIM(A.fax_2_cp) <> vEmpty);
579
580 UPDATE ul_import_data_partner A
581 SET status = vFail,
582 message = message || 'Nama CP maksimal 100 karakter, '
583 WHERE A.upload_header_id = pUploadHeaderId
584 AND length(A.nama_cp) > 100
585 AND (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 -- # Pekerjaan CP wajib diisi, dan max character Pekerjaan CP adalah 100
602 UPDATE ul_import_data_partner A
603 SET status = vFail,
604 message = message || 'Pekerjaan CP harus diisi, '
605 WHERE A.upload_header_id = pUploadHeaderId
606 AND TRIM(A.pekerjaan_cp) = vEmpty
607 AND (TRIM(A.nama_cp) <> vEmpty
608 OR TRIM(A.kode_departemen_cp) <> vEmpty
609 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
610 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
611 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
612 OR TRIM(A.flag_responsible) <> vEmpty
613 OR TRIM(A.email_cp) <> vEmpty
614 OR TRIM(A.telepon_1_cp) <> vEmpty
615 OR TRIM(A.telepon_2_cp) <> vEmpty
616 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
617 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
618 OR TRIM(A.ponsel_1_cp) <> vEmpty
619 OR TRIM(A.ponsel_2_cp) <> vEmpty
620 OR TRIM(A.fax_1_cp) <> vEmpty
621 OR TRIM(A.fax_2_cp) <> vEmpty);
622
623 UPDATE ul_import_data_partner A
624 SET status = vFail,
625 message = message || 'Pekerjaan CP maksimal 100 karakter, '
626 WHERE A.upload_header_id = pUploadHeaderId
627 AND length(A.pekerjaan_cp) > 100
628 AND (TRIM(A.nama_cp) <> vEmpty
629 OR TRIM(A.kode_departemen_cp) <> vEmpty
630 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
631 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
632 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
633 OR TRIM(A.flag_responsible) <> vEmpty
634 OR TRIM(A.email_cp) <> vEmpty
635 OR TRIM(A.telepon_1_cp) <> vEmpty
636 OR TRIM(A.telepon_2_cp) <> vEmpty
637 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
638 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
639 OR TRIM(A.ponsel_1_cp) <> vEmpty
640 OR TRIM(A.ponsel_2_cp) <> vEmpty
641 OR TRIM(A.fax_1_cp) <> vEmpty
642 OR TRIM(A.fax_2_cp) <> vEmpty);
643
644 -- # Kode Departemen CP wajib diisi, dan harus ada dalam combo DEPARTMENT
645 UPDATE ul_import_data_partner A
646 SET status = vFail,
647 message = message || 'Kode Departemen CP harus diisi, '
648 WHERE A.upload_header_id = pUploadHeaderId
649 AND TRIM(A.kode_departemen_cp) = vEmpty
650 AND (TRIM(A.nama_cp) <> vEmpty
651 OR TRIM(A.pekerjaan_cp) <> vEmpty
652 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
653 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
654 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
655 OR TRIM(A.flag_responsible) <> vEmpty
656 OR TRIM(A.email_cp) <> vEmpty
657 OR TRIM(A.telepon_1_cp) <> vEmpty
658 OR TRIM(A.telepon_2_cp) <> vEmpty
659 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
660 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
661 OR TRIM(A.ponsel_1_cp) <> vEmpty
662 OR TRIM(A.ponsel_2_cp) <> vEmpty
663 OR TRIM(A.fax_1_cp) <> vEmpty
664 OR TRIM(A.fax_2_cp) <> vEmpty);
665
666 UPDATE ul_import_data_partner A
667 SET status = vFail,
668 message = message || 'Kode Departemen CP tidak ada dalam sistem, '
669 WHERE A.upload_header_id = pUploadHeaderId
670 AND TRIM(A.kode_departemen_cp) <> vEmpty
671 AND (TRIM(A.nama_cp) <> vEmpty
672 OR TRIM(A.pekerjaan_cp) <> vEmpty
673 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
674 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
675 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
676 OR TRIM(A.flag_responsible) <> vEmpty
677 OR TRIM(A.email_cp) <> vEmpty
678 OR TRIM(A.telepon_1_cp) <> vEmpty
679 OR TRIM(A.telepon_2_cp) <> vEmpty
680 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
681 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
682 OR TRIM(A.ponsel_1_cp) <> vEmpty
683 OR TRIM(A.ponsel_2_cp) <> vEmpty
684 OR TRIM(A.fax_1_cp) <> vEmpty
685 OR TRIM(A.fax_2_cp) <> vEmpty
686 ) AND NOT EXISTS(
687 SELECT 1
688 FROM t_combo_value Z
689 WHERE Z.combo_id = vComboIdDepartment
690 AND Z.code = A.kode_departemen_cp
691 );
692
693 -- # Kode Level Jabatan CP wajib diisi, dan harus ada dalam combo JOBLEVEL
694 UPDATE ul_import_data_partner A
695 SET status = vFail,
696 message = message || 'Kode Level Jabatan CP harus diisi, '
697 WHERE A.upload_header_id = pUploadHeaderId
698 AND TRIM(A.kode_level_jabatan_cp) = vEmpty
699 AND (TRIM(A.nama_cp) <> vEmpty
700 OR TRIM(A.pekerjaan_cp) <> vEmpty
701 OR TRIM(A.kode_departemen_cp) <> vEmpty
702 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
703 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
704 OR TRIM(A.flag_responsible) <> vEmpty
705 OR TRIM(A.email_cp) <> vEmpty
706 OR TRIM(A.telepon_1_cp) <> vEmpty
707 OR TRIM(A.telepon_2_cp) <> vEmpty
708 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
709 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
710 OR TRIM(A.ponsel_1_cp) <> vEmpty
711 OR TRIM(A.ponsel_2_cp) <> vEmpty
712 OR TRIM(A.fax_1_cp) <> vEmpty
713 OR TRIM(A.fax_2_cp) <> vEmpty);
714
715 UPDATE ul_import_data_partner A
716 SET status = vFail,
717 message = message || 'Kode Level Jabatan CP tidak ada dalam sistem, '
718 WHERE A.upload_header_id = pUploadHeaderId
719 AND TRIM(A.kode_level_jabatan_cp) <> vEmpty
720 AND (TRIM(A.nama_cp) <> vEmpty
721 OR TRIM(A.pekerjaan_cp) <> vEmpty
722 OR TRIM(A.kode_departemen_cp) <> vEmpty
723 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
724 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
725 OR TRIM(A.flag_responsible) <> vEmpty
726 OR TRIM(A.email_cp) <> vEmpty
727 OR TRIM(A.telepon_1_cp) <> vEmpty
728 OR TRIM(A.telepon_2_cp) <> vEmpty
729 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
730 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
731 OR TRIM(A.ponsel_1_cp) <> vEmpty
732 OR TRIM(A.ponsel_2_cp) <> vEmpty
733 OR TRIM(A.fax_1_cp) <> vEmpty
734 OR TRIM(A.fax_2_cp) <> vEmpty
735 ) AND NOT EXISTS(
736 SELECT 1
737 FROM t_combo_value Z
738 WHERE Z.combo_id = vComboIdJobLevel
739 AND Z.code = A.kode_level_jabatan_cp
740 );
741
742 -- # Jika diisi, max character Email CP adalah 100 dan harus dalam format email
743 UPDATE ul_import_data_partner A
744 SET status = vFail,
745 message = message || 'Email CP maksimal 100 karakter, '
746 WHERE A.upload_header_id = pUploadHeaderId
747 AND length(A.email_cp) > 100
748 AND (TRIM(A.nama_cp) <> vEmpty
749 OR TRIM(A.pekerjaan_cp) <> vEmpty
750 OR TRIM(A.kode_departemen_cp) <> vEmpty
751 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
752 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
753 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
754 OR TRIM(A.flag_responsible) <> vEmpty);
755
756 UPDATE ul_import_data_partner A
757 SET status = vFail,
758 message = message || 'Email CP tidak dalam format email, '
759 WHERE A.upload_header_id = pUploadHeaderId
760 AND NOT is_email(A.email_cp)
761 AND (TRIM(A.nama_cp) <> vEmpty
762 OR TRIM(A.pekerjaan_cp) <> vEmpty
763 OR TRIM(A.kode_departemen_cp) <> vEmpty
764 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
765 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
766 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
767 OR TRIM(A.flag_responsible) <> vEmpty);
768
769 -- # Flag Notifikasi AP wajib diisi, dan harus ada dalam combo YESNO
770 UPDATE ul_import_data_partner A
771 SET status = vFail,
772 message = message || 'Flag Notifikasi AP harus diisi, '
773 WHERE A.upload_header_id = pUploadHeaderId
774 AND TRIM(A.flag_notifikasi_ap) = vEmpty
775 AND (TRIM(A.nama_cp) <> vEmpty
776 OR TRIM(A.pekerjaan_cp) <> vEmpty
777 OR TRIM(A.kode_departemen_cp) <> vEmpty
778 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
779 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
780 OR TRIM(A.flag_responsible) <> vEmpty
781 OR TRIM(A.email_cp) <> vEmpty
782 OR TRIM(A.telepon_1_cp) <> vEmpty
783 OR TRIM(A.telepon_2_cp) <> vEmpty
784 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
785 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
786 OR TRIM(A.ponsel_1_cp) <> vEmpty
787 OR TRIM(A.ponsel_2_cp) <> vEmpty
788 OR TRIM(A.fax_1_cp) <> vEmpty
789 OR TRIM(A.fax_2_cp) <> vEmpty
790 );
791
792 UPDATE ul_import_data_partner A
793 SET status = vFail,
794 message = message || 'Flag Notifikasi AP hanya dapat berisi Y atau N, '
795 WHERE A.upload_header_id = pUploadHeaderId
796 AND TRIM(A.flag_notifikasi_ap) <> vEmpty
797 AND (TRIM(A.nama_cp) <> vEmpty
798 OR TRIM(A.pekerjaan_cp) <> vEmpty
799 OR TRIM(A.kode_departemen_cp) <> vEmpty
800 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
801 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
802 OR TRIM(A.flag_responsible) <> vEmpty
803 OR TRIM(A.email_cp) <> vEmpty
804 OR TRIM(A.telepon_1_cp) <> vEmpty
805 OR TRIM(A.telepon_2_cp) <> vEmpty
806 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
807 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
808 OR TRIM(A.ponsel_1_cp) <> vEmpty
809 OR TRIM(A.ponsel_2_cp) <> vEmpty
810 OR TRIM(A.fax_1_cp) <> vEmpty
811 OR TRIM(A.fax_2_cp) <> vEmpty
812 ) AND NOT EXISTS(
813 SELECT 1
814 FROM t_combo_value Z
815 WHERE Z.combo_id = vComboIdYesNo
816 AND Z.code = A.flag_notifikasi_ap
817 );
818
819 -- # Flag Notifikasi AR wajib diisi, dan harus ada dalam combo YESNO
820 UPDATE ul_import_data_partner A
821 SET status = vFail,
822 message = message || 'Flag Notifikasi AR harus diisi, '
823 WHERE A.upload_header_id = pUploadHeaderId
824 AND TRIM(A.flag_notifikasi_ar) = vEmpty
825 AND (TRIM(A.nama_cp) <> vEmpty
826 OR TRIM(A.pekerjaan_cp) <> vEmpty
827 OR TRIM(A.kode_departemen_cp) <> vEmpty
828 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
829 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
830 OR TRIM(A.flag_responsible) <> vEmpty
831 OR TRIM(A.email_cp) <> vEmpty
832 OR TRIM(A.telepon_1_cp) <> vEmpty
833 OR TRIM(A.telepon_2_cp) <> vEmpty
834 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
835 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
836 OR TRIM(A.ponsel_1_cp) <> vEmpty
837 OR TRIM(A.ponsel_2_cp) <> vEmpty
838 OR TRIM(A.fax_1_cp) <> vEmpty
839 OR TRIM(A.fax_2_cp) <> vEmpty);
840
841 UPDATE ul_import_data_partner A
842 SET status = vFail,
843 message = message || 'Flag Notifikasi AR hanya dapat berisi Y atau N, '
844 WHERE A.upload_header_id = pUploadHeaderId
845 AND TRIM(A.flag_notifikasi_ar) <> vEmpty
846 AND (TRIM(A.nama_cp) <> vEmpty
847 OR TRIM(A.pekerjaan_cp) <> vEmpty
848 OR TRIM(A.kode_departemen_cp) <> vEmpty
849 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
850 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
851 OR TRIM(A.flag_responsible) <> vEmpty
852 OR TRIM(A.email_cp) <> vEmpty
853 OR TRIM(A.telepon_1_cp) <> vEmpty
854 OR TRIM(A.telepon_2_cp) <> vEmpty
855 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
856 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
857 OR TRIM(A.ponsel_1_cp) <> vEmpty
858 OR TRIM(A.ponsel_2_cp) <> vEmpty
859 OR TRIM(A.fax_1_cp) <> vEmpty
860 OR TRIM(A.fax_2_cp) <> vEmpty
861 ) AND NOT EXISTS(
862 SELECT 1
863 FROM t_combo_value Z
864 WHERE Z.combo_id = vComboIdYesNo
865 AND Z.code = A.flag_notifikasi_ar
866 );
867
868 -- # Jika diisi, max character Telepon 1 CP adalah 100
869 UPDATE ul_import_data_partner A
870 SET status = vFail,
871 message = message || 'Telepon 1 CP maksimal 100 karakter, '
872 WHERE A.upload_header_id = pUploadHeaderId
873 AND length(A.telepon_1_cp) > 100
874 AND (TRIM(A.nama_cp) <> vEmpty
875 OR TRIM(A.pekerjaan_cp) <> vEmpty
876 OR TRIM(A.kode_departemen_cp) <> vEmpty
877 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
878 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
879 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
880 OR TRIM(A.flag_responsible) <> vEmpty);
881
882 -- # Jika diisi, max character Telepon 2 CP adalah 100
883 UPDATE ul_import_data_partner A
884 SET status = vFail,
885 message = message || 'Telepon 2 CP maksimal 100 karakter, '
886 WHERE A.upload_header_id = pUploadHeaderId
887 AND length(A.telepon_2_cp) > 100
888 AND (TRIM(A.nama_cp) <> vEmpty
889 OR TRIM(A.pekerjaan_cp) <> vEmpty
890 OR TRIM(A.kode_departemen_cp) <> vEmpty
891 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
892 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
893 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
894 OR TRIM(A.flag_responsible) <> vEmpty);
895
896 -- # Jika diisi, max character Telepon Eksternal 1 CP adalah 100
897 UPDATE ul_import_data_partner A
898 SET status = vFail,
899 message = message || 'Telepon Eksternal 1 CP maksimal 100 karakter, '
900 WHERE A.upload_header_id = pUploadHeaderId
901 AND length(A.telepon_eksternal_1_cp) > 100
902 AND (TRIM(A.nama_cp) <> vEmpty
903 OR TRIM(A.pekerjaan_cp) <> vEmpty
904 OR TRIM(A.kode_departemen_cp) <> vEmpty
905 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
906 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
907 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
908 OR TRIM(A.flag_responsible) <> vEmpty);
909
910 -- # Jika diisi, max character Telepon Eksternal 2 CP adalah 100
911 UPDATE ul_import_data_partner A
912 SET status = vFail,
913 message = message || 'Telepon Eksternal 2 CP maksimal 100 karakter, '
914 WHERE A.upload_header_id = pUploadHeaderId
915 AND length(A.telepon_eksternal_2_cp) > 100
916 AND (TRIM(A.nama_cp) <> vEmpty
917 OR TRIM(A.pekerjaan_cp) <> vEmpty
918 OR TRIM(A.kode_departemen_cp) <> vEmpty
919 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
920 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
921 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
922 OR TRIM(A.flag_responsible) <> vEmpty);
923
924 -- # Jika diisi, max character Ponsel 1 CP adalah 100
925 UPDATE ul_import_data_partner A
926 SET status = vFail,
927 message = message || 'Ponsel 1 CP maksimal 100 karakter, '
928 WHERE A.upload_header_id = pUploadHeaderId
929 AND length(A.ponsel_1_cp) > 100
930 AND (TRIM(A.nama_cp) <> vEmpty
931 OR TRIM(A.pekerjaan_cp) <> vEmpty
932 OR TRIM(A.kode_departemen_cp) <> vEmpty
933 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
934 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
935 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
936 OR TRIM(A.flag_responsible) <> vEmpty);
937
938 -- # Jika diisi, max character Ponsel 2 CP adalah 100
939 UPDATE ul_import_data_partner A
940 SET status = vFail,
941 message = message || 'Ponsel 2 CP maksimal 100 karakter, '
942 WHERE A.upload_header_id = pUploadHeaderId
943 AND length(A.ponsel_2_cp) > 100
944 AND (TRIM(A.nama_cp) <> vEmpty
945 OR TRIM(A.pekerjaan_cp) <> vEmpty
946 OR TRIM(A.kode_departemen_cp) <> vEmpty
947 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
948 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
949 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
950 OR TRIM(A.flag_responsible) <> vEmpty);
951
952 -- # Jika diisi, max character Fax 1 CP adalah 100
953 UPDATE ul_import_data_partner A
954 SET status = vFail,
955 message = message || 'Fax 1 CP maksimal 100 karakter, '
956 WHERE A.upload_header_id = pUploadHeaderId
957 AND length(A.fax_1_cp) > 100
958 AND (TRIM(A.nama_cp) <> vEmpty
959 OR TRIM(A.pekerjaan_cp) <> vEmpty
960 OR TRIM(A.kode_departemen_cp) <> vEmpty
961 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
962 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
963 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
964 OR TRIM(A.flag_responsible) <> vEmpty);
965
966 -- # Jika diisi, max character Fax 2 CP adalah 100
967 UPDATE ul_import_data_partner A
968 SET status = vFail,
969 message = message || 'Fax 2 CP maksimal 100 karakter, '
970 WHERE A.upload_header_id = pUploadHeaderId
971 AND length(A.fax_2_cp) > 100
972 AND (TRIM(A.nama_cp) <> vEmpty
973 OR TRIM(A.pekerjaan_cp) <> vEmpty
974 OR TRIM(A.kode_departemen_cp) <> vEmpty
975 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
976 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
977 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
978 OR TRIM(A.flag_responsible) <> vEmpty);
979
980 -- # Flag Responsible wajib diisi, dan harus ada dalam combo YESNO
981 UPDATE ul_import_data_partner A
982 SET status = vFail,
983 message = message || 'Flag Responsible harus diisi, '
984 WHERE A.upload_header_id = pUploadHeaderId
985 AND TRIM(A.flag_responsible) = vEmpty
986 AND (TRIM(A.nama_cp) <> vEmpty
987 OR TRIM(A.pekerjaan_cp) <> vEmpty
988 OR TRIM(A.kode_departemen_cp) <> vEmpty
989 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
990 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
991 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
992 OR TRIM(A.email_cp) <> vEmpty
993 OR TRIM(A.telepon_1_cp) <> vEmpty
994 OR TRIM(A.telepon_2_cp) <> vEmpty
995 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
996 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
997 OR TRIM(A.ponsel_1_cp) <> vEmpty
998 OR TRIM(A.ponsel_2_cp) <> vEmpty
999 OR TRIM(A.fax_1_cp) <> vEmpty
1000 OR TRIM(A.fax_2_cp) <> vEmpty);
1001
1002 UPDATE ul_import_data_partner A
1003 SET status = vFail,
1004 message = message || 'Flag Responsible hanya dapat berisi Y atau N, '
1005 WHERE A.upload_header_id = pUploadHeaderId
1006 AND TRIM(A.flag_responsible) <> vEmpty
1007 AND (TRIM(A.nama_cp) <> vEmpty
1008 OR TRIM(A.pekerjaan_cp) <> vEmpty
1009 OR TRIM(A.kode_departemen_cp) <> vEmpty
1010 OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
1011 OR TRIM(A.flag_notifikasi_ap) <> vEmpty
1012 OR TRIM(A.flag_notifikasi_ar) <> vEmpty
1013 OR TRIM(A.email_cp) <> vEmpty
1014 OR TRIM(A.telepon_1_cp) <> vEmpty
1015 OR TRIM(A.telepon_2_cp) <> vEmpty
1016 OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
1017 OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
1018 OR TRIM(A.ponsel_1_cp) <> vEmpty
1019 OR TRIM(A.ponsel_2_cp) <> vEmpty
1020 OR TRIM(A.fax_1_cp) <> vEmpty
1021 OR TRIM(A.fax_2_cp) <> vEmpty
1022 ) AND NOT EXISTS(
1023 SELECT 1
1024 FROM t_combo_value Z
1025 WHERE Z.combo_id = vComboIdYesNo
1026 AND Z.code = A.flag_responsible
1027 );
1028
1029 -- # PARTNER NPWP
1030 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1031 -- # Nomor NPWP, Nama NPWP, Negara NPWP, Flag PKP
1032
1033 UPDATE ul_import_data_partner A
1034 SET flg_npwp = vYes
1035 WHERE A.upload_header_id = pUploadHeaderId
1036 AND (TRIM(A.nomor_npwp) <> vEmpty
1037 OR TRIM(A.nama_npwp) <> vEmpty
1038 OR TRIM(A.negara_npwp) <> vEmpty
1039 OR TRIM(A.flg_pkp) <> vEmpty
1040 OR TRIM(A.tanggal_npwp) <> vEmpty
1041 OR TRIM(A.alamat_1_npwp) <> vEmpty
1042 OR TRIM(A.alamat_2_npwp) <> vEmpty
1043 OR TRIM(A.alamat_3_npwp) <> vEmpty
1044 OR TRIM(A.kota_npwp) <> vEmpty
1045 OR TRIM(A.kode_pos_npwp) <> vEmpty
1046 OR TRIM(A.telepon_1_npwp) <> vEmpty
1047 OR TRIM(A.telepon_2_npwp) <> vEmpty
1048 OR TRIM(A.fax_1_npwp) <> vEmpty
1049 OR TRIM(A.fax_2_npwp) <> vEmpty);
1050
1051 -- # Nomor NPWP wajib diisi, dan max character Nomor NPWP adalah 50
1052 UPDATE ul_import_data_partner A
1053 SET status = vFail,
1054 message = message || 'Nomor NPWP harus diisi, '
1055 WHERE A.upload_header_id = pUploadHeaderId
1056 AND TRIM(A.nomor_npwp) = vEmpty
1057 AND (TRIM(A.nama_npwp) <> vEmpty
1058 OR TRIM(A.negara_npwp) <> vEmpty
1059 OR TRIM(A.flg_pkp) <> vEmpty
1060 OR TRIM(A.tanggal_npwp) <> vEmpty
1061 OR TRIM(A.alamat_1_npwp) <> vEmpty
1062 OR TRIM(A.alamat_2_npwp) <> vEmpty
1063 OR TRIM(A.alamat_3_npwp) <> vEmpty
1064 OR TRIM(A.kota_npwp) <> vEmpty
1065 OR TRIM(A.kode_pos_npwp) <> vEmpty
1066 OR TRIM(A.telepon_1_npwp) <> vEmpty
1067 OR TRIM(A.telepon_2_npwp) <> vEmpty
1068 OR TRIM(A.fax_1_npwp) <> vEmpty
1069 OR TRIM(A.fax_2_npwp) <> vEmpty);
1070
1071 UPDATE ul_import_data_partner A
1072 SET status = vFail,
1073 message = message || 'Nomor NPWP maksimal 50 karakter, '
1074 WHERE A.upload_header_id = pUploadHeaderId
1075 AND length(A.nomor_npwp) > 50
1076 AND (TRIM(A.nama_npwp) <> vEmpty
1077 OR TRIM(A.negara_npwp) <> vEmpty
1078 OR TRIM(A.flg_pkp) <> vEmpty
1079 OR TRIM(A.tanggal_npwp) <> vEmpty
1080 OR TRIM(A.alamat_1_npwp) <> vEmpty
1081 OR TRIM(A.alamat_2_npwp) <> vEmpty
1082 OR TRIM(A.alamat_3_npwp) <> vEmpty
1083 OR TRIM(A.kota_npwp) <> vEmpty
1084 OR TRIM(A.kode_pos_npwp) <> vEmpty
1085 OR TRIM(A.telepon_1_npwp) <> vEmpty
1086 OR TRIM(A.telepon_2_npwp) <> vEmpty
1087 OR TRIM(A.fax_1_npwp) <> vEmpty
1088 OR TRIM(A.fax_2_npwp) <> vEmpty);
1089
1090 -- # Nama NPWP wajib diisi, dan max character Nama NPWP adalah 1024
1091 UPDATE ul_import_data_partner A
1092 SET status = vFail,
1093 message = message || 'Nama NPWP harus diisi, '
1094 WHERE A.upload_header_id = pUploadHeaderId
1095 AND TRIM(A.nama_npwp) = vEmpty
1096 AND (TRIM(A.nomor_npwp) <> vEmpty
1097 OR TRIM(A.negara_npwp) <> vEmpty
1098 OR TRIM(A.flg_pkp) <> vEmpty
1099 OR TRIM(A.tanggal_npwp) <> vEmpty
1100 OR TRIM(A.alamat_1_npwp) <> vEmpty
1101 OR TRIM(A.alamat_2_npwp) <> vEmpty
1102 OR TRIM(A.alamat_3_npwp) <> vEmpty
1103 OR TRIM(A.kota_npwp) <> vEmpty
1104 OR TRIM(A.kode_pos_npwp) <> vEmpty
1105 OR TRIM(A.telepon_1_npwp) <> vEmpty
1106 OR TRIM(A.telepon_2_npwp) <> vEmpty
1107 OR TRIM(A.fax_1_npwp) <> vEmpty
1108 OR TRIM(A.fax_2_npwp) <> vEmpty);
1109
1110 UPDATE ul_import_data_partner A
1111 SET status = vFail,
1112 message = message || 'Nama NPWP maksimal 1024 karakter, '
1113 WHERE A.upload_header_id = pUploadHeaderId
1114 AND length(A.nama_npwp) > 1024
1115 AND (TRIM(A.nomor_npwp) <> vEmpty
1116 OR TRIM(A.negara_npwp) <> vEmpty
1117 OR TRIM(A.flg_pkp) <> vEmpty
1118 OR TRIM(A.tanggal_npwp) <> vEmpty
1119 OR TRIM(A.alamat_1_npwp) <> vEmpty
1120 OR TRIM(A.alamat_2_npwp) <> vEmpty
1121 OR TRIM(A.alamat_3_npwp) <> vEmpty
1122 OR TRIM(A.kota_npwp) <> vEmpty
1123 OR TRIM(A.kode_pos_npwp) <> vEmpty
1124 OR TRIM(A.telepon_1_npwp) <> vEmpty
1125 OR TRIM(A.telepon_2_npwp) <> vEmpty
1126 OR TRIM(A.fax_1_npwp) <> vEmpty
1127 OR TRIM(A.fax_2_npwp) <> vEmpty);
1128
1129 -- # Jika diisi, Tanggal NPWP harus dalam format tanggal
1130 UPDATE ul_import_data_partner A
1131 SET status = vFail,
1132 message = message || 'Tanggal NPWP harus dalam format tanggal, '
1133 WHERE A.upload_header_id = pUploadHeaderId
1134 AND NOT is_date(A.tanggal_npwp) > 1024
1135 AND (TRIM(A.nomor_npwp) <> vEmpty
1136 OR TRIM(A.nama_npwp) <> vEmpty
1137 OR TRIM(A.negara_npwp) <> vEmpty
1138 OR TRIM(A.flg_pkp) <> vEmpty);
1139
1140 -- # Jika diisi, max character Alamat 1 NPWP adalah 100
1141 UPDATE ul_import_data_partner A
1142 SET status = vFail,
1143 message = message || 'Alamat 1 NPWP maksimal 100 karakter, '
1144 WHERE A.upload_header_id = pUploadHeaderId
1145 AND length(A.alamat_1_npwp) > 100
1146 AND (TRIM(A.nomor_npwp) <> vEmpty
1147 OR TRIM(A.nama_npwp) <> vEmpty
1148 OR TRIM(A.negara_npwp) <> vEmpty
1149 OR TRIM(A.flg_pkp) <> vEmpty);
1150
1151 -- # Jika diisi, max character Alamat 2 NPWP adalah 100
1152 UPDATE ul_import_data_partner A
1153 SET status = vFail,
1154 message = message || 'Alamat 2 NPWP maksimal 100 karakter, '
1155 WHERE A.upload_header_id = pUploadHeaderId
1156 AND length(A.alamat_2_npwp) > 100
1157 AND (TRIM(A.nomor_npwp) <> vEmpty
1158 OR TRIM(A.nama_npwp) <> vEmpty
1159 OR TRIM(A.negara_npwp) <> vEmpty
1160 OR TRIM(A.flg_pkp) <> vEmpty);
1161
1162 -- # Jika diisi, max character Alamat 3 NPWP adalah 100
1163 UPDATE ul_import_data_partner A
1164 SET status = vFail,
1165 message = message || 'Alamat 3 NPWP maksimal 100 karakter, '
1166 WHERE A.upload_header_id = pUploadHeaderId
1167 AND length(A.alamat_3_npwp) > 100
1168 AND (TRIM(A.nomor_npwp) <> vEmpty
1169 OR TRIM(A.nama_npwp) <> vEmpty
1170 OR TRIM(A.negara_npwp) <> vEmpty
1171 OR TRIM(A.flg_pkp) <> vEmpty);
1172
1173 -- # Jika diisi, max character Kota NPWP adalah 100
1174 UPDATE ul_import_data_partner A
1175 SET status = vFail,
1176 message = message || 'Kota NPWP maksimal 100 karakter, '
1177 WHERE A.upload_header_id = pUploadHeaderId
1178 AND length(A.kota_npwp) > 100
1179 AND (TRIM(A.nomor_npwp) <> vEmpty
1180 OR TRIM(A.nama_npwp) <> vEmpty
1181 OR TRIM(A.negara_npwp) <> vEmpty
1182 OR TRIM(A.flg_pkp) <> vEmpty);
1183
1184 -- # Jika diisi, max character Kode Pos NPWP adalah 100
1185 UPDATE ul_import_data_partner A
1186 SET status = vFail,
1187 message = message || 'Kode Pos NPWP maksimal 100 karakter, '
1188 WHERE A.upload_header_id = pUploadHeaderId
1189 AND length(A.kode_pos) > 100
1190 AND (TRIM(A.nomor_npwp) <> vEmpty
1191 OR TRIM(A.nama_npwp) <> vEmpty
1192 OR TRIM(A.negara_npwp) <> vEmpty
1193 OR TRIM(A.flg_pkp) <> vEmpty);
1194
1195 -- # Jika diisi, max character Telepon 1 NPWP adalah 100
1196 UPDATE ul_import_data_partner A
1197 SET status = vFail,
1198 message = message || 'Telepon 1 NPWP maksimal 100 karakter, '
1199 WHERE A.upload_header_id = pUploadHeaderId
1200 AND length(A.telepon_1) > 100
1201 AND (TRIM(A.nomor_npwp) <> vEmpty
1202 OR TRIM(A.nama_npwp) <> vEmpty
1203 OR TRIM(A.negara_npwp) <> vEmpty
1204 OR TRIM(A.flg_pkp) <> vEmpty);
1205
1206 -- # Jika diisi, max character Telepon 2 NPWP adalah 100
1207 UPDATE ul_import_data_partner A
1208 SET status = vFail,
1209 message = message || 'Telepon 2 NPWP maksimal 100 karakter, '
1210 WHERE A.upload_header_id = pUploadHeaderId
1211 AND length(A.telepon_2) > 100
1212 AND (TRIM(A.nomor_npwp) <> vEmpty
1213 OR TRIM(A.nama_npwp) <> vEmpty
1214 OR TRIM(A.negara_npwp) <> vEmpty
1215 OR TRIM(A.flg_pkp) <> vEmpty);
1216
1217 -- # Jika diisi, max character Fax 1 NPWP adalah 100
1218 UPDATE ul_import_data_partner A
1219 SET status = vFail,
1220 message = message || 'Fax 1 NPWP maksimal 100 karakter, '
1221 WHERE A.upload_header_id = pUploadHeaderId
1222 AND length(A.fax_1_npwp) > 100
1223 AND (TRIM(A.nomor_npwp) <> vEmpty
1224 OR TRIM(A.nama_npwp) <> vEmpty
1225 OR TRIM(A.negara_npwp) <> vEmpty
1226 OR TRIM(A.flg_pkp) <> vEmpty);
1227
1228 -- # Jika diisi, max character Fax 2 NPWP adalah 100
1229 UPDATE ul_import_data_partner A
1230 SET status = vFail,
1231 message = message || 'Fax 2 NPWP maksimal 100 karakter, '
1232 WHERE A.upload_header_id = pUploadHeaderId
1233 AND length(A.fax_2_npwp) > 100
1234 AND (TRIM(A.nomor_npwp) <> vEmpty
1235 OR TRIM(A.nama_npwp) <> vEmpty
1236 OR TRIM(A.negara_npwp) <> vEmpty
1237 OR TRIM(A.flg_pkp) <> vEmpty);
1238
1239 -- # Negara NPWP wajib diisi, dan Negara NPWP harus ada dalam combo COUNTRY
1240 UPDATE ul_import_data_partner A
1241 SET status = vFail,
1242 message = message || 'Negara NPWP harus diisi, '
1243 WHERE A.upload_header_id = pUploadHeaderId
1244 AND TRIM(A.negara_npwp) = vEmpty
1245 AND (TRIM(A.nomor_npwp) <> vEmpty
1246 OR TRIM(A.nama_npwp) <> vEmpty
1247 OR TRIM(A.flg_pkp) <> vEmpty
1248 OR TRIM(A.tanggal_npwp) <> vEmpty
1249 OR TRIM(A.alamat_1_npwp) <> vEmpty
1250 OR TRIM(A.alamat_2_npwp) <> vEmpty
1251 OR TRIM(A.alamat_3_npwp) <> vEmpty
1252 OR TRIM(A.kota_npwp) <> vEmpty
1253 OR TRIM(A.kode_pos_npwp) <> vEmpty
1254 OR TRIM(A.telepon_1_npwp) <> vEmpty
1255 OR TRIM(A.telepon_2_npwp) <> vEmpty
1256 OR TRIM(A.fax_1_npwp) <> vEmpty
1257 OR TRIM(A.fax_2_npwp) <> vEmpty);
1258
1259 UPDATE ul_import_data_partner A
1260 SET status = vFail,
1261 message = message || 'Negara NPWP tidak ada dalam sistem, '
1262 WHERE A.upload_header_id = pUploadHeaderId
1263 AND length(A.negara_npwp) > 1024
1264 AND (TRIM(A.nomor_npwp) <> vEmpty
1265 OR TRIM(A.nama_npwp) <> vEmpty
1266 OR TRIM(A.flg_pkp) <> vEmpty
1267 OR TRIM(A.tanggal_npwp) <> vEmpty
1268 OR TRIM(A.alamat_1_npwp) <> vEmpty
1269 OR TRIM(A.alamat_2_npwp) <> vEmpty
1270 OR TRIM(A.alamat_3_npwp) <> vEmpty
1271 OR TRIM(A.kota_npwp) <> vEmpty
1272 OR TRIM(A.kode_pos_npwp) <> vEmpty
1273 OR TRIM(A.telepon_1_npwp) <> vEmpty
1274 OR TRIM(A.telepon_2_npwp) <> vEmpty
1275 OR TRIM(A.fax_1_npwp) <> vEmpty
1276 OR TRIM(A.fax_2_npwp) <> vEmpty);
1277
1278 -- # Flag PKP wajib diisi, dan Flag PKP harus ada dalam combo YESNO
1279 UPDATE ul_import_data_partner A
1280 SET status = vFail,
1281 message = message || 'Flag PKP harus diisi, '
1282 WHERE A.upload_header_id = pUploadHeaderId
1283 AND TRIM(A.flg_pkp) = vEmpty
1284 AND (TRIM(A.nomor_npwp) <> vEmpty
1285 OR TRIM(A.nama_npwp) <> vEmpty
1286 OR TRIM(A.negara_npwp) <> vEmpty
1287 OR TRIM(A.tanggal_npwp) <> vEmpty
1288 OR TRIM(A.alamat_1_npwp) <> vEmpty
1289 OR TRIM(A.alamat_2_npwp) <> vEmpty
1290 OR TRIM(A.alamat_3_npwp) <> vEmpty
1291 OR TRIM(A.kota_npwp) <> vEmpty
1292 OR TRIM(A.kode_pos_npwp) <> vEmpty
1293 OR TRIM(A.telepon_1_npwp) <> vEmpty
1294 OR TRIM(A.telepon_2_npwp) <> vEmpty
1295 OR TRIM(A.fax_1_npwp) <> vEmpty
1296 OR TRIM(A.fax_2_npwp) <> vEmpty);
1297
1298 UPDATE ul_import_data_partner A
1299 SET status = vFail,
1300 message = message || 'Flag PKP hanya dapat berisi Y atau N, '
1301 WHERE A.upload_header_id = pUploadHeaderId
1302 AND length(A.flg_pkp) > 1024
1303 AND (TRIM(A.nomor_npwp) <> vEmpty
1304 OR TRIM(A.nama_npwp) <> vEmpty
1305 OR TRIM(A.negara_npwp) <> vEmpty
1306 OR TRIM(A.tanggal_npwp) <> vEmpty
1307 OR TRIM(A.alamat_1_npwp) <> vEmpty
1308 OR TRIM(A.alamat_2_npwp) <> vEmpty
1309 OR TRIM(A.alamat_3_npwp) <> vEmpty
1310 OR TRIM(A.kota_npwp) <> vEmpty
1311 OR TRIM(A.kode_pos_npwp) <> vEmpty
1312 OR TRIM(A.telepon_1_npwp) <> vEmpty
1313 OR TRIM(A.telepon_2_npwp) <> vEmpty
1314 OR TRIM(A.fax_1_npwp) <> vEmpty
1315 OR TRIM(A.fax_2_npwp) <> vEmpty);
1316
1317 -- # Validasi tidak boleh ada Nomor NPWP yang duplikat dalam 1 csv
1318 WITH duplicate_data_partner_npwp AS (
1319 SELECT A.nomor_npwp
1320 FROM ul_import_data_partner A
1321 WHERE A.upload_header_id = pUploadHeaderId
1322 AND TRIM(A.nomor_npwp) <> vEmpty
1323 GROUP BY A.nomor_npwp
1324 HAVING COUNT(1) > 1
1325 )
1326 UPDATE ul_import_data_partner A
1327 SET status = vFail,
1328 message = message || 'Nomor NPWP duplikat, '
1329 WHERE A.upload_header_id = pUploadHeaderId
1330 AND EXISTS (
1331 SELECT 1 FROM duplicate_data_partner_npwp B WHERE A.nomor_npwp = B.nomor_npwp
1332 );
1333
1334 -- # PARTNER BANK
1335 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1336 -- # Kode Bank, Nomor Rekening, Kode Valuta Rekening, Nama Rekening 1
1337
1338 UPDATE ul_import_data_partner A
1339 SET flg_bank = vYes
1340 WHERE A.upload_header_id = pUploadHeaderId
1341 AND (TRIM(A.kode_bank) <> vEmpty
1342 OR TRIM(A.nomor_rekening) <> vEmpty
1343 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1344 OR TRIM(A.nama_rekening_1) <> vEmpty
1345 OR TRIM(A.nama_rekening_2) <> vEmpty);
1346
1347 -- # Kode Bank wajib diisi, dan harus ada dalam combo BANK
1348 UPDATE ul_import_data_partner A
1349 SET status = vFail,
1350 message = message || 'Kode Bank harus diisi, '
1351 WHERE A.upload_header_id = pUploadHeaderId
1352 AND TRIM(A.kode_bank) = vEmpty
1353 AND (TRIM(A.nomor_rekening) <> vEmpty
1354 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1355 OR TRIM(A.nama_rekening_1) <> vEmpty
1356 OR TRIM(A.nama_rekening_2) <> vEmpty);
1357
1358 UPDATE ul_import_data_partner A
1359 SET status = vFail,
1360 message = message || 'Kode Bank tidak ada dalam sistem, '
1361 WHERE A.upload_header_id = pUploadHeaderId
1362 AND TRIM(A.kode_bank) <> vEmpty
1363 AND (TRIM(A.nomor_rekening) <> vEmpty
1364 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1365 OR TRIM(A.nama_rekening_1) <> vEmpty
1366 OR TRIM(A.nama_rekening_2) <> vEmpty
1367 ) AND NOT EXISTS(
1368 SELECT 1
1369 FROM t_combo_value Z
1370 WHERE Z.combo_id = vComboIdBank
1371 AND Z.code = A.kode_bank
1372 );
1373
1374 -- # Nomor Rekening wajib diisi, max character Nomor Rekening adalah 50
1375 UPDATE ul_import_data_partner A
1376 SET status = vFail,
1377 message = message || 'Nomor Rekening harus diisi, '
1378 WHERE A.upload_header_id = pUploadHeaderId
1379 AND TRIM(A.nomor_rekening) = vEmpty
1380 AND (TRIM(A.kode_bank) <> vEmpty
1381 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1382 OR TRIM(A.nama_rekening_1) <> vEmpty
1383 OR TRIM(A.nama_rekening_2) <> vEmpty);
1384
1385 UPDATE ul_import_data_partner A
1386 SET status = vFail,
1387 message = message || 'Nomor Rekening harus diisi, '
1388 WHERE A.upload_header_id = pUploadHeaderId
1389 AND length(A.nomor_rekening) > 50
1390 AND (TRIM(A.kode_bank) <> vEmpty
1391 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1392 OR TRIM(A.nama_rekening_1) <> vEmpty
1393 OR TRIM(A.nama_rekening_2) <> vEmpty);
1394
1395 -- # Kode Valuta Rekening wajib diisi, dan harus ada dalam combo CURRENCY
1396 UPDATE ul_import_data_partner A
1397 SET status = vFail,
1398 message = message || 'Kode Valuta Rekening harus diisi, '
1399 WHERE A.upload_header_id = pUploadHeaderId
1400 AND TRIM(A.kode_valuta_rekening) = vEmpty
1401 AND (TRIM(A.kode_bank) <> vEmpty
1402 OR TRIM(A.nomor_rekening) <> vEmpty
1403 OR TRIM(A.nama_rekening_1) <> vEmpty
1404 OR TRIM(A.nama_rekening_2) <> vEmpty);
1405
1406 UPDATE ul_import_data_partner A
1407 SET status = vFail,
1408 message = message || 'Kode Valuta Rekening tidak ada dalam sistem, '
1409 WHERE A.upload_header_id = pUploadHeaderId
1410 AND TRIM(A.kode_valuta_rekening) <> vEmpty
1411 AND (TRIM(A.nomor_rekening) <> vEmpty
1412 OR TRIM(A.kode_bank) <> vEmpty
1413 OR TRIM(A.nama_rekening_1) <> vEmpty
1414 OR TRIM(A.nama_rekening_2) <> vEmpty
1415 ) AND NOT EXISTS(
1416 SELECT 1
1417 FROM t_combo_value Z
1418 WHERE Z.combo_id = vComboIdCurrency
1419 AND Z.code = A.kode_valuta_rekening
1420 );
1421
1422 -- # Nama 1 Rekening wajib diisi, max character Nama 1 Rekening adalah 100
1423 UPDATE ul_import_data_partner A
1424 SET status = vFail,
1425 message = message || 'Nama Rekening 1 harus diisi, '
1426 WHERE A.upload_header_id = pUploadHeaderId
1427 AND TRIM(A.nama_rekening_1) = vEmpty
1428 AND (TRIM(A.nomor_rekening) <> vEmpty
1429 OR TRIM(A.kode_bank) <> vEmpty
1430 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1431 OR TRIM(A.nama_rekening_2) <> vEmpty);
1432
1433 UPDATE ul_import_data_partner A
1434 SET status = vFail,
1435 message = message || 'Nama Rekening 1 maksimal 100 karakter, '
1436 WHERE A.upload_header_id = pUploadHeaderId
1437 AND length(A.nama_rekening_1) > 100
1438 AND (TRIM(A.nomor_rekening) <> vEmpty
1439 OR TRIM(A.kode_bank) <> vEmpty
1440 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1441 OR TRIM(A.nama_rekening_2) <> vEmpty);
1442
1443 -- # Jika diisi, max character Nama 2 Rekening adalah 100
1444 UPDATE ul_import_data_partner A
1445 SET status = vFail,
1446 message = message || 'Nama Rekening 2 maksimal 100 karakter, '
1447 WHERE A.upload_header_id = pUploadHeaderId
1448 AND length(A.nama_rekening_2) > 100
1449 AND (TRIM(A.nomor_rekening) <> vEmpty
1450 OR TRIM(A.kode_bank) <> vEmpty
1451 OR TRIM(A.kode_valuta_rekening) <> vEmpty
1452 OR TRIM(A.nama_rekening_1) <> vEmpty);
1453
1454 -- # Validasi tidak boleh ada Kode Partner - Kode Bank - Nomor Rekening yang duplikat dalam 1 csv
1455 WITH duplicate_data_partner_bank AS (
1456 SELECT A.kode_partner, A.kode_bank, A.nomor_rekening
1457 FROM ul_import_data_partner A
1458 WHERE A.upload_header_id = pUploadHeaderId
1459 AND TRIM(A.kode_partner) <> vEmpty
1460 AND TRIM(A.kode_bank) <> vEmpty
1461 AND TRIM(A.nomor_rekening) <> vEmpty
1462 GROUP BY A.kode_partner, A.kode_bank, A.nomor_rekening
1463 HAVING COUNT(1) > 1
1464 )
1465 UPDATE ul_import_data_partner A
1466 SET status = vFail,
1467 message = message || 'Pasangan Kode Partner - Kode Bank - Nomor Rekening duplikat, '
1468 WHERE A.upload_header_id = pUploadHeaderId
1469 AND EXISTS (
1470 SELECT 1
1471 FROM duplicate_data_partner_bank B
1472 WHERE A.kode_partner = B.kode_partner
1473 AND A.kode_bank = B.kode_bank
1474 AND A.nomor_rekening = B.nomor_rekening
1475 );
1476
1477 -- # PARTNER REL
1478 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1479 -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman
1480
1481 UPDATE ul_import_data_partner A
1482 SET flg_rel = vYes
1483 WHERE A.upload_header_id = pUploadHeaderId
1484 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1485 OR TRIM(A.flag_tagihan) <> vEmpty
1486 OR TRIM(A.flag_pengiriman) <> vEmpty);
1487
1488 -- # Data Manage As Customer atas partner harus diisi
1489 -- Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
1490 -- Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
1491 -- Tanggal Pembayaran Customer, Diskon Reguler
1492 UPDATE ul_import_data_partner A
1493 SET status = vFail,
1494 message = message || 'Data Manage As Customer harus diisi, '
1495 WHERE A.upload_header_id = pUploadHeaderId
1496 AND flg_rel = vYes
1497 AND (TRIM(A.kode_tipe_customer) = vEmpty
1498 OR TRIM(A.kode_valuta_piutang) = vEmpty
1499 OR TRIM(A.batas_jumlah_piutang) = vEmpty
1500 OR TRIM(A.flag_komisi) = vEmpty
1501 OR TRIM(A.flag_referensi_invoice) = vEmpty
1502 OR TRIM(A.batas_pembayaran_customer) = vEmpty
1503 OR TRIM(A.cara_pembayaran_customer) = vEmpty
1504 OR TRIM(A.cara_pembayaran_customer) = vEmpty
1505 OR TRIM(A.hari_pembayaran_customer) = vEmpty
1506 OR TRIM(A.tanggal_pembayaran_customer) = vEmpty
1507 OR TRIM(A.diskon_reguler) = vEmpty);
1508
1509 -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer
1510 -- bukan employee, dan bukan dirinya sendiri
1511 UPDATE ul_import_data_partner A
1512 SET status = vFail,
1513 message = message || 'Kode Partner Relasi harus diisi, '
1514 WHERE A.upload_header_id = pUploadHeaderId
1515 AND TRIM(A.kode_partner_relasi) = vEmpty
1516 AND (TRIM(A.flag_tagihan) <> vEmpty
1517 OR TRIM(A.flag_pengiriman) <> vEmpty);
1518
1519 UPDATE ul_import_data_partner A
1520 SET status = vFail,
1521 message = message || 'Partner Relasi tidak ada dalam sistem ataupun file CSV, '
1522 WHERE A.upload_header_id = pUploadHeaderId
1523 AND TRIM(A.kode_partner_relasi) <> vEmpty
1524 AND (TRIM(A.flag_tagihan) <> vEmpty
1525 OR TRIM(A.flag_pengiriman) <> vEmpty)
1526 AND (
1527 NOT EXISTS (
1528 SELECT 1
1529 FROM m_partner Z
1530 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
1531 WHERE Z.partner_code = A.kode_partner_relasi
1532 AND Z.tenant_id = vTenantId
1533 AND Z.active = vYes
1534 ) OR NOT EXISTS (
1535 SELECT 1
1536 FROM ul_import_data_partner Z
1537 WHERE Z.upload_header_id = A.upload_header_id
1538 AND Z.kode_partner = A.kode_partner
1539 )
1540 );
1541
1542 UPDATE ul_import_data_partner A
1543 SET status = vFail,
1544 message = message || 'Partner Relasi bukan Customer, '
1545 WHERE A.upload_header_id = pUploadHeaderId
1546 AND TRIM(A.kode_partner_relasi) <> vEmpty
1547 AND (TRIM(A.flag_tagihan) <> vEmpty
1548 OR TRIM(A.flag_pengiriman) <> vEmpty)
1549 AND (
1550 NOT EXISTS (
1551 SELECT 1
1552 FROM m_partner Z
1553 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
1554 WHERE Y.group_partner = vGroupPartnerCustomer
1555 AND Z.partner_code = A.kode_partner_relasi
1556 AND Z.partner_id IN (
1557 SELECT X.partner_id
1558 FROM m_partner_type X
1559 WHERE X.tenant_id = vTenantId
1560 AND X.group_partner = vGroupPartnerEmployee
1561 )
1562 AND Z.tenant_id = vTenantId
1563 AND Z.active = vYes
1564 ) OR NOT EXISTS (
1565 SELECT 1
1566 FROM ul_import_data_partner Z
1567 WHERE Z.upload_header_id = A.upload_header_id
1568 AND Z.kode_partner = A.kode_partner
1569 AND TRIM(Z.kode_tipe_customer) <> vEmpty
1570 AND TRIM(Z.kode_valuta_piutang) <> vEmpty
1571 AND TRIM(Z.batas_jumlah_piutang) <> vEmpty
1572 AND TRIM(Z.flag_komisi) <> vEmpty
1573 AND TRIM(Z.flag_referensi_invoice) <> vEmpty
1574 AND TRIM(Z.batas_pembayaran_customer) <> vEmpty
1575 AND TRIM(Z.cara_pembayaran_customer) <> vEmpty
1576 AND TRIM(Z.cara_pembayaran_customer) <> vEmpty
1577 AND TRIM(Z.hari_pembayaran_customer) <> vEmpty
1578 AND TRIM(Z.tanggal_pembayaran_customer) <> vEmpty
1579 AND TRIM(Z.diskon_reguler) <> vEmpty
1580 )
1581 );
1582
1583 UPDATE ul_import_data_partner A
1584 SET status = vFail,
1585 message = message || 'Kode Partner Relasi tidak boleh sama dengan Kode Partner, '
1586 WHERE A.upload_header_id = pUploadHeaderId
1587 AND TRIM(A.kode_partner_relasi) <> vEmpty
1588 AND (TRIM(A.flag_tagihan) <> vEmpty
1589 OR TRIM(A.flag_pengiriman) <> vEmpty)
1590 AND A.kode_partner_relasi = Z.kode_partner;
1591
1592 -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
1593 UPDATE ul_import_data_partner A
1594 SET status = vFail,
1595 message = message || 'Flag Tagihan harus diisi, '
1596 WHERE A.upload_header_id = pUploadHeaderId
1597 AND TRIM(A.flag_tagihan) = vEmpty
1598 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1599 OR TRIM(A.flag_pengiriman) <> vEmpty);
1600
1601 UPDATE ul_import_data_partner A
1602 SET status = vFail,
1603 message = message || 'Flag Tagihan hanya dapat berisi Y atau N, '
1604 WHERE A.upload_header_id = pUploadHeaderId
1605 AND TRIM(A.flag_tagihan) <> vEmpty
1606 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1607 OR TRIM(A.flag_pengiriman) <> vEmpty
1608 ) AND NOT EXISTS(
1609 SELECT 1
1610 FROM t_combo_value Z
1611 WHERE Z.combo_id = vComboIdYesNo
1612 AND Z.code = A.flag_tagihan
1613 );
1614
1615 -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO
1616 UPDATE ul_import_data_partner A
1617 SET status = vFail,
1618 message = message || 'Flag Pengiriman harus diisi, '
1619 WHERE A.upload_header_id = pUploadHeaderId
1620 AND TRIM(A.flag_pengiriman) = vEmpty
1621 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1622 OR TRIM(A.flag_tagihan) <> vEmpty);
1623
1624 UPDATE ul_import_data_partner A
1625 SET status = vFail,
1626 message = message || 'Flag Pengiriman hanya dapat berisi Y atau N, '
1627 WHERE A.upload_header_id = pUploadHeaderId
1628 AND TRIM(A.flag_pengiriman) <> vEmpty
1629 AND (TRIM(A.kode_partner_relasi) <> vEmpty
1630 OR TRIM(A.flag_tagihan) <> vEmpty
1631 ) AND NOT EXISTS(
1632 SELECT 1
1633 FROM t_combo_value Z
1634 WHERE Z.combo_id = vComboIdYesNo
1635 AND Z.code = A.flag_pengiriman
1636 );
1637
1638 -- # PARTNER AS SUPPLIER
1639 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1640 -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman
1641
1642 UPDATE ul_import_data_partner A
1643 SET flg_customer = vYes
1644 WHERE A.upload_header_id = pUploadHeaderId
1645 AND (TRIM(A.kode_tipe_supplier) = vEmpty
1646 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1647 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1648 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1649 OR TRIM(A.lama_pengiriman) <> vEmpty);
1650
1651 -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
1652 UPDATE ul_import_data_partner A
1653 SET status = vFail,
1654 message = message || 'Kode Tipe Supplier harus diisi, '
1655 WHERE A.upload_header_id = pUploadHeaderId
1656 AND TRIM(A.kode_tipe_supplier) = vEmpty
1657 AND (TRIM(A.kode_valuta_hutang) <> vEmpty
1658 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1659 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1660 OR TRIM(A.lama_pengiriman) <> vEmpty);
1661
1662 UPDATE ul_import_data_partner A
1663 SET status = vFail,
1664 message = message || 'Kode Tipe Supplier tidak ada dalam sistem, '
1665 WHERE A.upload_header_id = pUploadHeaderId
1666 AND TRIM(A.kode_tipe_supplier) <> vEmpty
1667 AND (TRIM(A.kode_valuta_hutang) <> vEmpty
1668 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1669 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1670 OR TRIM(A.lama_pengiriman) <> vEmpty
1671 ) AND NOT EXISTS(
1672 SELECT 1
1673 FROM m_type_partner Z
1674 WHERE Z.tenant_id = vTenantId
1675 AND Z.group_partner = vGroupPartnerSupplier
1676 AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
1677 );
1678
1679 -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
1680 UPDATE ul_import_data_partner A
1681 SET status = vFail,
1682 message = message || 'Kode Valuta Hutang harus diisi, '
1683 WHERE A.upload_header_id = pUploadHeaderId
1684 AND TRIM(A.kode_valuta_hutang) = vEmpty
1685 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1686 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1687 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1688 OR TRIM(A.lama_pengiriman) <> vEmpty);
1689
1690 UPDATE ul_import_data_partner A
1691 SET status = vFail,
1692 message = message || 'Kode Valuta Hutang harus tidak ada dalam sistem, '
1693 WHERE A.upload_header_id = pUploadHeaderId
1694 AND TRIM(A.kode_valuta_hutang) <> vEmpty
1695 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1696 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1697 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1698 OR TRIM(A.lama_pengiriman) <> vEmpty
1699 ) AND NOT EXISTS(
1700 SELECT 1
1701 FROM t_combo_value Z
1702 WHERE Z.combo_id = vComboIdCurrency
1703 AND Z.code = A.kode_valuta_hutang
1704 );
1705
1706 -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
1707 UPDATE ul_import_data_partner A
1708 SET status = vFail,
1709 message = message || 'Batas Jumlah Hutang harus diisi, '
1710 WHERE A.upload_header_id = pUploadHeaderId
1711 AND TRIM(A.batas_jumlah_hutang) = vEmpty
1712 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1713 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1714 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1715 OR TRIM(A.lama_pengiriman) <> vEmpty);
1716
1717 UPDATE ul_import_data_partner A
1718 SET status = vFail,
1719 message = message || 'Batas Jumlah Hutang harus berupa angka, '
1720 WHERE A.upload_header_id = pUploadHeaderId
1721 AND TRIM(A.batas_jumlah_hutang) <> vEmpty
1722 AND NOT is_numeric(A.batas_jumlah_hutang)
1723 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1724 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1725 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1726 OR TRIM(A.lama_pengiriman) <> vEmpty);
1727
1728 WITH data_hutang_supplier AS (
1729 SELECT ul_import_data_partner_id, batas_jumlah_hutang
1730 FROM ul_import_data_partner
1731 WHERE upload_header_id = pUploadHeaderId
1732 AND is_numeric(batas_jumlah_hutang)
1733 )
1734 UPDATE ul_import_data_partner A
1735 SET status = vFail,
1736 message = message || 'Batas Jumlah Hutang harus > 0, '
1737 FROM data_hutang_supplier B
1738 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1739 AND B.batas_jumlah_hutang::numeric <= 0
1740 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1741 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1742 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
1743 OR TRIM(A.lama_pengiriman) <> vEmpty);
1744
1745 -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
1746 UPDATE ul_import_data_partner A
1747 SET status = vFail,
1748 message = message || 'Batas Pembayaran Supplier harus diisi, '
1749 WHERE A.upload_header_id = pUploadHeaderId
1750 AND TRIM(A.batas_pembayaran_supplier) = vEmpty
1751 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1752 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1753 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1754 OR TRIM(A.lama_pengiriman) <> vEmpty);
1755
1756 UPDATE ul_import_data_partner A
1757 SET status = vFail,
1758 message = message || 'Batas Pembayaran Supplier harus berupa angka, '
1759 WHERE A.upload_header_id = pUploadHeaderId
1760 AND TRIM(A.batas_pembayaran_supplier) <> vEmpty
1761 AND NOT is_numeric(A.batas_pembayaran_supplier)
1762 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1763 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1764 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1765 OR TRIM(A.lama_pengiriman) <> vEmpty);
1766
1767 WITH data_pembayaran_supplier AS (
1768 SELECT ul_import_data_partner_id, batas_pembayaran_supplier
1769 FROM ul_import_data_partner
1770 WHERE upload_header_id = pUploadHeaderId
1771 AND is_numeric(batas_pembayaran_supplier)
1772 )
1773 UPDATE ul_import_data_partner A
1774 SET status = vFail,
1775 message = message || 'Batas Pembayaran Supplier >= 0, '
1776 FROM data_pembayaran_supplier B
1777 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1778 AND B.batas_pembayaran_supplier::numeric < 0
1779 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1780 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1781 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1782 OR TRIM(A.lama_pengiriman) <> vEmpty);
1783
1784 -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0
1785 UPDATE ul_import_data_partner A
1786 SET status = vFail,
1787 message = message || 'Lama Pengiriman harus diisi, '
1788 WHERE A.upload_header_id = pUploadHeaderId
1789 AND TRIM(A.lama_pengiriman) = vEmpty
1790 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1791 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1792 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1793 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);
1794
1795 UPDATE ul_import_data_partner A
1796 SET status = vFail,
1797 message = message || 'Lama Pengiriman harus berupa angka, '
1798 WHERE A.upload_header_id = pUploadHeaderId
1799 AND TRIM(A.lama_pengiriman) <> vEmpty
1800 AND NOT is_numeric(A.lama_pengiriman)
1801 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1802 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1803 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1804 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);
1805
1806 WITH data_pembayaran_supplier AS (
1807 SELECT ul_import_data_partner_id, lama_pengiriman
1808 FROM ul_import_data_partner
1809 WHERE upload_header_id = pUploadHeaderId
1810 AND is_numeric(lama_pengiriman)
1811 )
1812 UPDATE ul_import_data_partner A
1813 SET status = vFail,
1814 message = message || 'Lama Pengiriman harus >= 0, '
1815 FROM data_pembayaran_supplier B
1816 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1817 AND B.lama_pengiriman::numeric < 0
1818 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1819 OR TRIM(A.kode_valuta_hutang) <> vEmpty
1820 OR TRIM(A.batas_jumlah_hutang) <> vEmpty
1821 OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);
1822
1823
1824 -- # PARTNER AS CUSTOMER
1825 -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
1826 -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
1827 -- Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
1828 -- Tanggal Pembayaran Customer, Diskon Reguler
1829
1830 UPDATE ul_import_data_partner A
1831 SET flg_customer = vYes
1832 WHERE A.upload_header_id = pUploadHeaderId
1833 AND (TRIM(A.kode_tipe_customer) <> vEmpty
1834 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1835 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1836 OR TRIM(A.flag_komisi) <> vEmpty
1837 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1838 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1839 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1840 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1841 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1842 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1843 OR TRIM(A.diskon_reguler) <> vEmpty);
1844
1845 -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C, dan bukan Internal
1846 UPDATE ul_import_data_partner A
1847 SET status = vFail,
1848 message = message || 'Kode Tipe Customer harus diisi, '
1849 WHERE A.upload_header_id = pUploadHeaderId
1850 AND TRIM(A.kode_tipe_customer) = vEmpty
1851 AND (TRIM(A.kode_valuta_piutang) <> vEmpty
1852 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1853 OR TRIM(A.flag_komisi) <> vEmpty
1854 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1855 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1856 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1857 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1858 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1859 OR TRIM(A.diskon_reguler) <> vEmpty
1860 OR TRIM(A.kode_bank_customer) <> vEmpty
1861 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
1862
1863 UPDATE ul_import_data_partner A
1864 SET status = vFail,
1865 message = message || 'Kode Tipe Supplier tidak ada dalam sistem, '
1866 WHERE A.upload_header_id = pUploadHeaderId
1867 AND TRIM(A.kode_tipe_supplier) <> vEmpty
1868 AND (TRIM(A.kode_valuta_piutang) <> vEmpty
1869 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1870 OR TRIM(A.flag_komisi) <> vEmpty
1871 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1872 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1873 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1874 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1875 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1876 OR TRIM(A.diskon_reguler) <> vEmpty
1877 OR TRIM(A.kode_bank_customer) <> vEmpty
1878 OR TRIM(A.nomor_rekening_customer) <> vEmpty
1879 ) AND NOT EXISTS(
1880 SELECT 1
1881 FROM m_type_partner Z
1882 WHERE Z.tenant_id = vTenantId
1883 AND Z.group_partner = vGroupPartnerCustomer
1884 AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
1885 );
1886
1887 -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
1888 UPDATE ul_import_data_partner A
1889 SET status = vFail,
1890 message = message || 'Kode Valuta Piutang harus diisi, '
1891 WHERE A.upload_header_id = pUploadHeaderId
1892 AND TRIM(A.kode_valuta_piutang) = vEmpty
1893 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1894 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1895 OR TRIM(A.flag_komisi) <> vEmpty
1896 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1897 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1898 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1899 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1900 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1901 OR TRIM(A.diskon_reguler) <> vEmpty
1902 OR TRIM(A.kode_bank_customer) <> vEmpty
1903 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
1904
1905 UPDATE ul_import_data_partner A
1906 SET status = vFail,
1907 message = message || 'Kode Valuta Piutang tidak ada dalam sistem, '
1908 WHERE A.upload_header_id = pUploadHeaderId
1909 AND TRIM(A.kode_valuta_piutang) <> vEmpty
1910 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1911 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1912 OR TRIM(A.flag_komisi) <> vEmpty
1913 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1914 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1915 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1916 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1917 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1918 OR TRIM(A.diskon_reguler) <> vEmpty
1919 OR TRIM(A.kode_bank_customer) <> vEmpty
1920 OR TRIM(A.nomor_rekening_customer) <> vEmpty
1921 ) AND NOT EXISTS(
1922 SELECT 1
1923 FROM t_combo_value Z
1924 WHERE Z.combo_id = vComboIdCurrency
1925 AND Z.code = A.kode_valuta_piutang
1926 );
1927
1928 -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
1929 UPDATE ul_import_data_partner A
1930 SET status = vFail,
1931 message = message || 'Batas Jumlah Piutang harus diisi, '
1932 WHERE A.upload_header_id = pUploadHeaderId
1933 AND TRIM(A.batas_jumlah_piutang) = vEmpty
1934 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1935 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1936 OR TRIM(A.flag_komisi) <> vEmpty
1937 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1938 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1939 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1940 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1941 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1942 OR TRIM(A.diskon_reguler) <> vEmpty
1943 OR TRIM(A.kode_bank_customer) <> vEmpty
1944 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
1945
1946 UPDATE ul_import_data_partner A
1947 SET status = vFail,
1948 message = message || 'Batas Jumlah Piutang harus berupa angka, '
1949 WHERE A.upload_header_id = pUploadHeaderId
1950 AND TRIM(A.batas_jumlah_piutang) <> vEmpty
1951 AND NOT is_numeric(A.batas_jumlah_piutang)
1952 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1953 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1954 OR TRIM(A.flag_komisi) <> vEmpty
1955 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1956 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1957 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1958 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1959 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1960 OR TRIM(A.diskon_reguler) <> vEmpty
1961 OR TRIM(A.kode_bank_customer) <> vEmpty
1962 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
1963
1964 WITH data_piutang_customer AS (
1965 SELECT ul_import_data_partner_id, batas_jumlah_piutang
1966 FROM ul_import_data_partner
1967 WHERE upload_header_id = pUploadHeaderId
1968 AND is_numeric(batas_jumlah_piutang)
1969 )
1970 UPDATE ul_import_data_partner A
1971 SET status = vFail,
1972 message = message || 'Batas Jumlah Piutang harus > 0, '
1973 FROM data_piutang_customer B
1974 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
1975 AND B.batas_jumlah_piutang::numeric <= 0
1976 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1977 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1978 OR TRIM(A.flag_komisi) <> vEmpty
1979 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1980 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1981 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
1982 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
1983 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
1984 OR TRIM(A.diskon_reguler) <> vEmpty
1985 OR TRIM(A.kode_bank_customer) <> vEmpty
1986 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
1987
1988 -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
1989 UPDATE ul_import_data_partner A
1990 SET status = vFail,
1991 message = message || 'Flag Komisi harus diisi, '
1992 WHERE A.upload_header_id = pUploadHeaderId
1993 AND TRIM(A.flag_komisi) = vEmpty
1994 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
1995 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
1996 OR TRIM(A.kode_valuta_piutang) <> vEmpty
1997 OR TRIM(A.flag_referensi_invoice) <> vEmpty
1998 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
1999 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2000 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2001 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2002 OR TRIM(A.diskon_reguler) <> vEmpty
2003 OR TRIM(A.kode_bank_customer) <> vEmpty
2004 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2005
2006 UPDATE ul_import_data_partner A
2007 SET status = vFail,
2008 message = message || 'Flag Komisi hanya dapat berisi Y atau N, '
2009 WHERE A.upload_header_id = pUploadHeaderId
2010 AND TRIM(A.flag_komisi) <> vEmpty
2011 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2012 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2013 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2014 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2015 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2016 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2017 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2018 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2019 OR TRIM(A.diskon_reguler) <> vEmpty
2020 OR TRIM(A.kode_bank_customer) <> vEmpty
2021 OR TRIM(A.nomor_rekening_customer) <> vEmpty
2022 ) AND NOT EXISTS(
2023 SELECT 1
2024 FROM t_combo_value Z
2025 WHERE Z.combo_id = vComboIdYesNo
2026 AND Z.code = A.flag_komisi
2027 );
2028
2029 -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
2030 UPDATE ul_import_data_partner A
2031 SET status = vFail,
2032 message = message || 'Flag Referensi Invoice harus diisi, '
2033 WHERE A.upload_header_id = pUploadHeaderId
2034 AND TRIM(A.flag_referensi_invoice) = vEmpty
2035 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2036 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2037 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2038 OR TRIM(A.flag_komisi) <> vEmpty
2039 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2040 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2041 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2042 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2043 OR TRIM(A.diskon_reguler) <> vEmpty
2044 OR TRIM(A.kode_bank_customer) <> vEmpty
2045 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2046
2047 UPDATE ul_import_data_partner A
2048 SET status = vFail,
2049 message = message || 'Flag Referensi Invoice hanya dapat berisi Y atau N, '
2050 WHERE A.upload_header_id = pUploadHeaderId
2051 AND TRIM(A.flag_referensi_invoice) <> vEmpty
2052 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2053 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2054 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2055 OR TRIM(A.flag_komisi) <> vEmpty
2056 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2057 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2058 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2059 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2060 OR TRIM(A.diskon_reguler) <> vEmpty
2061 OR TRIM(A.kode_bank_customer) <> vEmpty
2062 OR TRIM(A.nomor_rekening_customer) <> vEmpty
2063 ) AND NOT EXISTS(
2064 SELECT 1
2065 FROM t_combo_value Z
2066 WHERE Z.combo_id = vComboIdYesNo
2067 AND Z.code = A.flag_referensi_invoice
2068 );
2069
2070 -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
2071 UPDATE ul_import_data_partner A
2072 SET status = vFail,
2073 message = message || 'Batas Pembayaran Customer harus diisi, '
2074 WHERE A.upload_header_id = pUploadHeaderId
2075 AND TRIM(A.batas_pembayaran_customer) = vEmpty
2076 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2077 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2078 OR TRIM(A.flag_komisi) <> vEmpty
2079 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2080 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2081 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2082 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2083 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2084 OR TRIM(A.diskon_reguler) <> vEmpty
2085 OR TRIM(A.kode_bank_customer) <> vEmpty
2086 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2087
2088 UPDATE ul_import_data_partner A
2089 SET status = vFail,
2090 message = message || 'Batas Pembayaran Customer harus berupa angka, '
2091 WHERE A.upload_header_id = pUploadHeaderId
2092 AND TRIM(A.batas_pembayaran_customer) <> vEmpty
2093 AND NOT is_numeric(A.batas_pembayaran_customer)
2094 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2095 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2096 OR TRIM(A.flag_komisi) <> vEmpty
2097 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2098 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2099 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2100 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2101 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2102 OR TRIM(A.diskon_reguler) <> vEmpty
2103 OR TRIM(A.kode_bank_customer) <> vEmpty
2104 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2105
2106 WITH data_pembayaran_customer AS (
2107 SELECT ul_import_data_partner_id, batas_pembayaran_customer
2108 FROM ul_import_data_partner
2109 WHERE upload_header_id = pUploadHeaderId
2110 AND is_numeric(batas_pembayaran_customer)
2111 )
2112 UPDATE ul_import_data_partner A
2113 SET status = vFail,
2114 message = message || 'Batas Pembayaran Customer harus >= 0, '
2115 FROM data_piutang_customer B
2116 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
2117 AND B.batas_pembayaran_customer::numeric < 0
2118 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2119 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2120 OR TRIM(A.flag_komisi) <> vEmpty
2121 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2122 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2123 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2124 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2125 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2126 OR TRIM(A.diskon_reguler) <> vEmpty
2127 OR TRIM(A.kode_bank_customer) <> vEmpty
2128 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2129
2130 -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
2131 UPDATE ul_import_data_partner A
2132 SET status = vFail,
2133 message = message || 'Cara Pembayaran Customer harus diisi, '
2134 WHERE A.upload_header_id = pUploadHeaderId
2135 AND TRIM(A.cara_pembayaran_customer) = vEmpty
2136 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2137 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2138 OR TRIM(A.flag_komisi) <> vEmpty
2139 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2140 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2141 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2142 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2143 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2144 OR TRIM(A.diskon_reguler) <> vEmpty
2145 OR TRIM(A.kode_bank_customer) <> vEmpty
2146 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2147
2148 UPDATE ul_import_data_partner A
2149 SET status = vFail,
2150 message = message || 'Cara Pembayaran Customer hanya dapat berisi Y atau N, '
2151 WHERE A.upload_header_id = pUploadHeaderId
2152 AND TRIM(A.cara_pembayaran_customer) <> vEmpty
2153 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2154 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2155 OR TRIM(A.flag_komisi) <> vEmpty
2156 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2157 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2158 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2159 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2160 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2161 OR TRIM(A.diskon_reguler) <> vEmpty
2162 OR TRIM(A.kode_bank_customer) <> vEmpty
2163 OR TRIM(A.nomor_rekening_customer) <> vEmpty
2164 ) AND NOT EXISTS(
2165 SELECT 1
2166 FROM t_combo_value Z
2167 WHERE Z.combo_id = vComboIdYesNo
2168 AND Z.code = A.cara_pembayaran_customer
2169 );
2170
2171 -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
2172 -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
2173 UPDATE ul_import_data_partner A
2174 SET status = vFail,
2175 message = message || 'Kode Bank Customer harus diisi, '
2176 WHERE A.upload_header_id = pUploadHeaderId
2177 AND TRIM(A.kode_bank_customer) = vEmpty
2178 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2179 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2180 OR TRIM(A.flag_komisi) <> vEmpty
2181 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2182 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2183 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2184 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2185 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2186 OR TRIM(A.diskon_reguler) <> vEmpty
2187 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2188 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2189
2190 UPDATE ul_import_data_partner A
2191 SET status = vFail,
2192 message = message || 'Nomor Rekening Customer harus diisi, '
2193 WHERE A.upload_header_id = pUploadHeaderId
2194 AND TRIM(A.nomor_rekening_customer) = vEmpty
2195 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2196 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2197 OR TRIM(A.flag_komisi) <> vEmpty
2198 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2199 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2200 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2201 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2202 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2203 OR TRIM(A.diskon_reguler) <> vEmpty
2204 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2205 OR TRIM(A.kode_bank_customer) <> vEmpty);
2206
2207 UPDATE ul_import_data_partner A
2208 SET status = vFail,
2209 message = message || 'Kode Bank Customer harus sama dengan Kode Bank, '
2210 WHERE A.upload_header_id = pUploadHeaderId
2211 AND TRIM(A.kode_bank_customer) <> vEmpty
2212 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2213 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2214 OR TRIM(A.flag_komisi) <> vEmpty
2215 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2216 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2217 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2218 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2219 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2220 OR TRIM(A.diskon_reguler) <> vEmpty
2221 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2222 OR TRIM(A.nomor_rekening_customer) <> vEmpty)
2223 AND A.kode_bank_customer <> kode_bank;
2224
2225 UPDATE ul_import_data_partner A
2226 SET status = vFail,
2227 message = message || 'Nomor Rekening Customer harus sama dengan Kode Bank, '
2228 WHERE A.upload_header_id = pUploadHeaderId
2229 AND TRIM(A.nomor_rekening_customer) <> vEmpty
2230 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2231 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2232 OR TRIM(A.flag_komisi) <> vEmpty
2233 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2234 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2235 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2236 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2237 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2238 OR TRIM(A.diskon_reguler) <> vEmpty
2239 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2240 OR TRIM(A.kode_bank_customer) <> vEmpty)
2241 AND A.nomor_rekening_customer <> nomor_rekening;
2242
2243 -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
2244 UPDATE ul_import_data_partner A
2245 SET status = vFail,
2246 message = message || 'Hari Pembayaran Customer harus diisi, '
2247 WHERE A.upload_header_id = pUploadHeaderId
2248 AND TRIM(A.hari_pembayaran_customer) = vEmpty
2249 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2250 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2251 OR TRIM(A.flag_komisi) <> vEmpty
2252 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2253 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2254 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2255 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2256 OR TRIM(A.diskon_reguler) <> vEmpty
2257 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2258 OR TRIM(A.kode_bank_customer) <> vEmpty
2259 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2260
2261 UPDATE ul_import_data_partner A
2262 SET status = vFail,
2263 message = message || 'Hari Pembayaran Customer tidak ada dalam sistem, '
2264 WHERE A.upload_header_id = pUploadHeaderId
2265 AND TRIM(A.hari_pembayaran_customer) <> vEmpty
2266 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2267 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2268 OR TRIM(A.flag_komisi) <> vEmpty
2269 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2270 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2271 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2272 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2273 OR TRIM(A.diskon_reguler) <> vEmpty
2274 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2275 OR TRIM(A.kode_bank_customer) <> vEmpty
2276 OR TRIM(A.nomor_rekening_customer) <> vEmpty
2277 ) AND NOT EXISTS(
2278 SELECT 1
2279 FROM t_combo_value Z
2280 WHERE Z.combo_id = vComboIdDay
2281 AND Z.code = A.hari_pembayaran_customer
2282 );
2283
2284 -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
2285 UPDATE ul_import_data_partner A
2286 SET status = vFail,
2287 message = message || 'Hari Pembayaran Customer harus diisi, '
2288 WHERE A.upload_header_id = pUploadHeaderId
2289 AND TRIM(A.tanggal_pembayaran_customer) = vEmpty
2290 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2291 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2292 OR TRIM(A.flag_komisi) <> vEmpty
2293 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2294 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2295 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2296 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2297 OR TRIM(A.diskon_reguler) <> vEmpty
2298 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2299 OR TRIM(A.kode_bank_customer) <> vEmpty
2300 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2301
2302 UPDATE ul_import_data_partner A
2303 SET status = vFail,
2304 message = message || 'Hari Pembayaran Customer tidak ada dalam sistem, '
2305 WHERE A.upload_header_id = pUploadHeaderId
2306 AND TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2307 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2308 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2309 OR TRIM(A.flag_komisi) <> vEmpty
2310 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2311 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2312 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2313 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2314 OR TRIM(A.diskon_reguler) <> vEmpty
2315 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2316 OR TRIM(A.kode_bank_customer) <> vEmpty
2317 OR TRIM(A.nomor_rekening_customer) <> vEmpty
2318 ) AND NOT EXISTS(
2319 SELECT 1
2320 FROM t_combo_value Z
2321 WHERE Z.combo_id = vComboIdDate
2322 AND Z.code = A.tanggal_pembayaran_customer
2323 );
2324
2325 -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100
2326 UPDATE ul_import_data_partner A
2327 SET status = vFail,
2328 message = message || 'Diskon Reguler harus diisi, '
2329 WHERE A.upload_header_id = pUploadHeaderId
2330 AND TRIM(A.diskon_reguler) = vEmpty
2331 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2332 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2333 OR TRIM(A.flag_komisi) <> vEmpty
2334 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2335 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2336 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2337 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2338 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2339 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2340 OR TRIM(A.kode_bank_customer) <> vEmpty
2341 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2342
2343 UPDATE ul_import_data_partner A
2344 SET status = vFail,
2345 message = message || 'Diskon Reguler harus diisi, '
2346 WHERE A.upload_header_id = pUploadHeaderId
2347 AND TRIM(A.diskon_reguler) <> vEmpty
2348 AND NOT is_numeric(A.diskon_reguler)
2349 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2350 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2351 OR TRIM(A.flag_komisi) <> vEmpty
2352 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2353 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2354 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2355 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2356 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2357 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2358 OR TRIM(A.kode_bank_customer) <> vEmpty
2359 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2360
2361 WITH data_diskon_customer AS (
2362 SELECT ul_import_data_partner_id, diskon_reguler
2363 FROM ul_import_data_partner
2364 WHERE upload_header_id = pUploadHeaderId
2365 AND is_numeric(diskon_reguler)
2366 )
2367 UPDATE ul_import_data_partner A
2368 SET status = vFail,
2369 message = message || 'Diskon Reguler harus bernilai antara 0 s/d 100, '
2370 FROM data_diskon_customer B
2371 WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
2372 AND (B.diskon_reguler::numeric < 0 OR B.diskon_reguler::numeric > 100)
2373 AND (TRIM(A.kode_tipe_supplier) <> vEmpty
2374 OR TRIM(A.kode_valuta_piutang) <> vEmpty
2375 OR TRIM(A.flag_komisi) <> vEmpty
2376 OR TRIM(A.flag_referensi_invoice) <> vEmpty
2377 OR TRIM(A.batas_jumlah_piutang) <> vEmpty
2378 OR TRIM(A.cara_pembayaran_customer) <> vEmpty
2379 OR TRIM(A.hari_pembayaran_customer) <> vEmpty
2380 OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
2381 OR TRIM(A.batas_pembayaran_customer) <> vEmpty
2382 OR TRIM(A.kode_bank_customer) <> vEmpty
2383 OR TRIM(A.nomor_rekening_customer) <> vEmpty);
2384
2385
2386 -- Cek apakah ada item CSV yang statusnya fail
2387 IF EXISTS ( SELECT 1 FROM ul_import_data_partner B
2388 WHERE B.upload_header_id = pUploadHeaderId
2389 AND B.status = vFail ) THEN
2390
2391 -- Sesuaikan message, agar message paling belakang tidak ada koma
2392 UPDATE ul_import_data_partner A
2393 SET message = substr(A.message, 1, length(A.message)-2)
2394 WHERE A.upload_header_id = pUploadHeaderId
2395 AND A.status = vFail
2396 AND TRIM(A.message) != vEmpty;
2397
2398 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
2399 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
2400 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
2401 UPDATE ul_import_data_partner A
2402 SET status = vFail,
2403 update_status = vActionError
2404 WHERE upload_header_id = pUploadHeaderId;
2405
2406 -- Hitung jumlah data yang FAIL
2407 SELECT COUNT(1) INTO vCountFail
2408 FROM ul_import_data_partner
2409 WHERE upload_header_id = pUploadHeaderId
2410 AND status = vFail;
2411 ELSE
2412
2413 -- RESET table temp
2414 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
2415
2416 -- Update status non FAIL to OK
2417 UPDATE ul_import_data_partner
2418 SET status = vOk
2419 WHERE upload_header_id = pUploadHeaderId;
2420
2421 -- INSERT OK data untuk partner code baru dengan kode_partner holding kosong
2422 WITH insert_data_partner AS (
2423 INSERT INTO m_partner(
2424 tenant_id, partner_code, partner_name, ctgr_partner_id,
2425 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
2426 create_user_id, update_datetime, update_user_id, version, active,
2427 active_datetime, non_active_datetime,
2428 line_of_business, price_level,
2429 industry_type, npwp_status)
2430 SELECT vTenantId, A.kode_partner, A.nama_partner, B.ctgr_partner_id,
2431 A.nama_eksternal, vEmptyId, vEmptyId, A.flag_holding, A.ranking, vDatetime,
2432 vUserId, vDatetime, vUserId, 0, vYes,
2433 vDatetime, vSpaceValue,
2434 A.bidang_usaha, A.level_harga,
2435 A.jenis_industri, A.kode_status_npwp
2436 FROM ul_import_data_partner A
2437 INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
2438 WHERE A.upload_header_id = pUploadHeaderId
2439 AND TRIM(A.kode_partner_holding) = vEmpty
2440 AND NOT EXISTS (
2441 SELECT 1 FROM m_partner Z
2442 WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
2443 )
2444 RETURNING *
2445 ), update_ul AS (
2446 -- Ubah update_status menjadi I jika data digunakan untuk Insert
2447 UPDATE ul_import_data_partner A SET
2448 update_status = vActionInsert
2449 WHERE A.upload_header_id = pUploadHeaderId
2450 AND A.status = vOk
2451 AND EXISTS (
2452 SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
2453 )
2454 )
2455 INSERT INTO tt_ul_partner(
2456 upload_header_id, action_type, partner_id, tenant_id, partner_code,
2457 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
2458 flg_holding, rank, create_datetime, create_user_id, update_datetime,
2459 update_user_id, version, active, active_datetime, non_active_datetime,
2460 line_of_business, price_level, industry_type, npwp_status)
2461 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
2462 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
2463 flg_holding, rank, create_datetime, create_user_id, update_datetime,
2464 update_user_id, version, active, active_datetime, non_active_datetime,
2465 line_of_business, price_level, industry_type, npwp_status
2466 FROM insert_data_partner;
2467
2468 WHILE EXISTS (SELECT 1 FROM ul_import_data_partner B
2469 WHERE B.upload_header_id = pUploadHeaderId
2470 AND B.status = vOk
2471 AND COALESCE(B.update_status, vEmpty) = vEmpty) LOOP
2472
2473 -- INSERT OK data untuk partner code baru dengan kode partner holding tidak kosong
2474 WITH insert_data_partner AS (
2475 INSERT INTO m_partner(
2476 tenant_id, partner_code, partner_name, ctgr_partner_id,
2477 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
2478 create_user_id, update_datetime, update_user_id, version, active,
2479 active_datetime, non_active_datetime,
2480 line_of_business, price_level,
2481 industry_type, npwp_status)
2482 SELECT vTenantId, A.kode_partner, A.nama_partner, B.ctgr_partner_id,
2483 A.nama_eksternal, vEmptyId, C.partner_id, A.flag_holding, A.ranking, vDatetime,
2484 vUserId, vDatetime, vUserId, 0, vYes,
2485 vDatetime, vSpaceValue,
2486 A.bidang_usaha, A.level_harga,
2487 A.jenis_industri, A.kode_status_npwp
2488 FROM ul_import_data_partner A
2489 INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
2490 INNER JOIN m_partner C ON A.kode_partner_holding = C.partner_code AND C.tenant_id = vTenantId
2491 WHERE A.upload_header_id = pUploadHeaderId
2492 AND TRIM(A.kode_partner_holding) <> vEmpty
2493 AND COALESCE(A.update_status, vEmpty) = vEmpty
2494 AND NOT EXISTS (
2495 SELECT 1 FROM m_partner Z
2496 WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
2497 )
2498 RETURNING *
2499 ), update_ul AS (
2500 -- Ubah update_status menjadi I jika data digunakan untuk Insert
2501 UPDATE ul_import_data_partner A SET
2502 update_status = vActionInsert
2503 WHERE A.upload_header_id = pUploadHeaderId
2504 AND A.status = vOk
2505 AND EXISTS (
2506 SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
2507 )
2508 )
2509 INSERT INTO tt_ul_partner(
2510 upload_header_id, action_type, partner_id, tenant_id, partner_code,
2511 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
2512 flg_holding, rank, create_datetime, create_user_id, update_datetime,
2513 update_user_id, version, active, active_datetime, non_active_datetime,
2514 line_of_business, price_level, industry_type, npwp_status)
2515 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
2516 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
2517 flg_holding, rank, create_datetime, create_user_id, update_datetime,
2518 update_user_id, version, active, active_datetime, non_active_datetime,
2519 line_of_business, price_level, industry_type, npwp_status
2520 FROM insert_data_partner;
2521
2522 END LOOP ;
2523
2524 -- INSERT data partner address
2525 INSERT INTO m_partner_address(
2526 tenant_id, partner_id, address_desc, address1,
2527 address2, address3, city, zip_code, state_or_province, country,
2528 phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
2529 update_datetime, update_user_id, version, active, active_datetime,
2530 non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
2531 flg_others, longitude, latitude)
2532 SELECT vTenantId, A.partner_id, B.deskripsi_alamat, B.alamat_1,
2533 B.alamat_2, B.alamat_3, B.kota, B.kode_pos, B.provinsi, B.kode_negara,
2534 B.telepon_1, B.telepon_1, B.fax_1, B.fax_2, vYes, vDatetime, vUserId,
2535 vDatetime, vUserId, 0, vYes, vDatetime,
2536 vSpaceValue, B.flag_shipping, B.flag_billing, B.flag_mailing, vYes,
2537 B.flag_others, B.garis_bujur::numeric, B.garis_lintang::numeric
2538 FROM tt_ul_partner A
2539 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
2540 WHERE A.upload_header_id = pUploadHeaderId
2541 AND A.upload_header_id = B.upload_header_id
2542 AND A.action_type = vActionInsert;
2543
2544 -- INSERT data partner cp
2545 INSERT INTO m_partner_cp(
2546 tenant_id, partner_id, cp_name, cp_job, address1,
2547 address2, address3, city, zip_code, country, phone1, phone2,
2548 fax1, fax2, email, create_datetime, create_user_id, update_datetime,
2549 update_user_id, version, active, active_datetime, non_active_datetime,
2550 department, job_level, phone_ext1, phone_ext2, mobile_phone1,
2551 mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
2552 flg_email_notif_ar)
2553 SELECT vTenantId, A.partner_id, B.nama_cp, B.pekerjaan_cp, B.alamat_1_cp,
2554 B.alamat_2_cp, B.alamat_3_cp, B.kota_cp, B.kode_pos_cp, B.negara_cp, B.telepon_1_cp, B.telepon_2_cp,
2555 B.fax_1_cp, B.fax_2_cp, B.email_cp, vDatetime, vUserId, vDatetime,
2556 vUserId, 0, vYes, vDatetime, vSpaceValue,
2557 B.departemen_cp, B.level_pekerjaan_cp, B.telepon_eksternal_1_cp, B.telepon_eksternal_2_cp, B.ponsel_1_cp,
2558 B.ponsel_2_cp, B.flag_responsible, vNo, B.flag_notifikasi_ap,
2559 B.flag_notifikasi_ar
2560 FROM tt_ul_partner A
2561 INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
2562 WHERE A.upload_header_id = pUploadHeaderId
2563 AND A.upload_header_id = B.upload_header_id
2564 AND A.action_type = vActionInsert
2565 AND B.flg_cp = vYes;
2566
2567 -- RESET table temp
2568 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
2569
2570 END IF;
2571
2572
2573 RETURN vCountFail;
2574
2575END;
2576$BODY$
2577 LANGUAGE plpgsql VOLATILE
2578 COST 100;
2579 /