· 7 years ago · Nov 09, 2018, 07:36 AM
1CREATE OR REPLACE FUNCTION ul_upload_customer(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 vDefaultRoleId bigint := -99;
27 vDefaultPolicyId bigint := -99;
28 vEmptyId bigint := -99;
29 vCountFail bigint := 0;
30
31BEGIN
32 -- siapkan parameter
33 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
34 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
35 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
36
37 -- get default role
38 SELECT role_id INTO vDefaultRoleId
39 FROM t_role
40 WHERE role_name = f_get_value_system_config_by_param_code(vTenantId, 'default.role.customer');
41
42 IF FOUND THEN
43
44 -- RESET TEMP TABLE
45 DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
46 DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
47 DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
48
49 -- INIT data temp table
50 INSERT INTO tt_ul_customer_unique(
51 upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
52 kode_kategori, rank, line_of_business, industry_type, npwp_status,
53 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
54 npwp, status_pkp, active)
55 SELECT upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
56 kode_kategori, rank, line_of_business, industry_type, npwp_status,
57 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
58 npwp, status_pkp, active
59 FROM ul_customer
60 WHERE upload_header_id = pUploadHeaderId
61 AND trim(kode_pelanggan) <> vEmpty
62 GROUP BY upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
63 kode_kategori, rank, line_of_business, industry_type, npwp_status,
64 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
65 npwp, status_pkp, active;
66
67 INSERT INTO tt_ul_customer_partner_address(
68 upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
69 address_3, zip_code, city, state_or_province, country, official_addr,
70 shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
71 fax_1, fax_2)
72 SELECT upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
73 address_3, zip_code, city, state_or_province, country, official_addr,
74 shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
75 fax_1, fax_2
76 FROM ul_customer
77 WHERE upload_header_id = pUploadHeaderId
78 AND trim(kode_pelanggan) <> vEmpty
79 AND trim(address_desc) <> vEmpty;
80
81 INSERT INTO tt_ul_customer_partner_cp(
82 upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
83 cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
84 cp_mobile_2)
85 SELECT upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
86 cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
87 cp_mobile_2
88 FROM ul_customer
89 WHERE upload_header_id = pUploadHeaderId
90 AND trim(kode_pelanggan) <> vEmpty
91 AND trim(cp_name) <> vEmpty;
92
93 -- GET id type partner CUST
94 SELECT type_partner_id INTO vTypePartnerId
95 FROM m_type_partner
96 WHERE type_partner_code = 'CUST';
97
98
99 -- Update kolom yang kosong ke nilai default
100 UPDATE ul_customer A
101 SET longitude = '0'
102 WHERE A.upload_header_id = pUploadHeaderId
103 AND TRIM(A.longitude) = vEmpty;
104
105 UPDATE ul_customer A
106 SET latitude = '0'
107 WHERE A.upload_header_id = pUploadHeaderId
108 AND TRIM(A.latitude) = vEmpty;
109
110 -- Validasi
111 -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
112 -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
113 -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
114 -- # Nama external jika diisi, maka max characternya harus 100
115 -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
116 -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
117 -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
118 -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
119 -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
120 -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
121 -- # TOP in days wajib diisi & harus integer
122 -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
123 -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
124 -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
125 -- # Address Desc wajib diisi & Max character Address Desc adalah 100
126 -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
127 -- # Max character Address 2 adalah 100 jika diisi
128 -- # Max character Address 3 adalah 100 jika diisi
129 -- # Max character Zip Code adalah 100 jika diisi
130 -- # City wajib diisi & harus terdaftar di system (m_city)
131 -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
132 -- # Official addr wajib diisi dengan nilai Y/N
133 -- # Shipping Addr Wajib diisi dengan nilai Y/N
134 -- # Billing Addr Wajib diisi dengan nilai Y/N
135 -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
136 -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
137 -- # Max character Phone 1 adalah 100, jika diisi
138 -- # Max character Phone 2 adalah 100, jika diisi
139 -- # Max character Fax 1 adalah 100, jika diisi
140 -- # Max character Fax 2 adalah 100, jika diisi
141 -- # CP Name wajib diisi dan max character adalah 100
142 -- # CP Job wajib diisi dan max character adalah 100
143 -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
144 -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
145 -- # Max character CP Email adalah 100 jika diisi
146 -- # Max character CP Phone 1 adalah 100 jika diisi
147 -- # Max character CP Phone 2 adalah 100 jika diisi
148 -- # Max character CP Mobile 1 adalah 100 jika diisi
149 -- # Max character CP Mobile 2 adalah 100 jika diisi
150 -- # Jika NPWP diisi maka max characternya adalah 50
151 -- # Jika Status PKP diisi, maka nilainya harus Y/N
152 -- # Active harus diisi dengan nilai Y/N
153 -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"-- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
154 -- # Validasi Address desc harus unique untuk kode pelanggan yang sama
155 -- # Validasi CP Name harus unique untuk kode pelanggan yang sama
156
157 -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
158 UPDATE ul_customer A
159 SET status = vFail,
160 message = message || 'Customer code must be filled, '
161 WHERE A.upload_header_id = pUploadHeaderId
162 AND TRIM(A.kode_pelanggan) = vEmpty;
163
164 UPDATE ul_customer A
165 SET status = vFail,
166 message = message || 'Max customer code is 50 character, '
167 WHERE A.upload_header_id = pUploadHeaderId
168 AND length(A.kode_pelanggan) > 50;
169
170 -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
171 UPDATE ul_customer A
172 SET status = vFail,
173 message = message || 'Code already registered on system for non customer partner, '
174 WHERE A.upload_header_id = pUploadHeaderId
175 AND EXISTS (
176 SELECT 1 FROM m_partner Z
177 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
178 WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
179 AND Y.type_partner_id <> vTypePartnerId
180 );
181
182 -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
183 UPDATE ul_customer A
184 SET status = vFail,
185 message = message || 'Customer name must be filled, '
186 WHERE A.upload_header_id = pUploadHeaderId
187 AND TRIM(A.nama_pelanggan) = vEmpty;
188
189 UPDATE ul_customer A
190 SET status = vFail,
191 message = message || 'Max Customer name is 1024 character, '
192 WHERE A.upload_header_id = pUploadHeaderId
193 AND length(A.nama_pelanggan) > 1024;
194
195 -- # Nama external jika diisi, maka max characternya harus 100
196 UPDATE ul_customer A
197 SET status = vFail,
198 message = message || 'Max external name is 100 character, '
199 WHERE A.upload_header_id = pUploadHeaderId
200 AND TRIM(A.nama_external) <> vEmpty
201 AND length(A.nama_external) > 100;
202
203 -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
204 UPDATE ul_customer A
205 SET status = vFail,
206 message = message || 'Category code must be filled, '
207 WHERE A.upload_header_id = pUploadHeaderId
208 AND TRIM(A.kode_kategori) = vEmpty;
209
210 UPDATE ul_customer A
211 SET status = vFail,
212 message = message || 'Category is not registred on system, '
213 WHERE A.upload_header_id = pUploadHeaderId
214 AND TRIM(A.kode_kategori) <> vEmpty
215 AND NOT EXISTS(
216 SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori AND Z.tenant_id = vTenantId
217 );
218
219 -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
220 UPDATE ul_customer A
221 SET status = vFail,
222 message = message || 'Rank must be filled, '
223 WHERE A.upload_header_id = pUploadHeaderId
224 AND TRIM(A.rank) = vEmpty;
225
226 UPDATE ul_customer A
227 SET status = vFail,
228 message = message || 'Rank is not registred on system, '
229 WHERE A.upload_header_id = pUploadHeaderId
230 AND TRIM(A.rank) <> vEmpty
231 AND NOT EXISTS(
232 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'PARTNERRANK' AND Z.code = A.rank
233 );
234
235 -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
236 UPDATE ul_customer A
237 SET status = vFail,
238 message = message || 'Line of business is not registred on system, '
239 WHERE A.upload_header_id = pUploadHeaderId
240 AND TRIM(A.line_of_business) <> vEmpty
241 AND NOT EXISTS(
242 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'LINEOFBUSINESS' AND Z.code = A.line_of_business
243 );
244
245 -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
246 UPDATE ul_customer A
247 SET status = vFail,
248 message = message || 'Industry Type must be filled, '
249 WHERE A.upload_header_id = pUploadHeaderId
250 AND TRIM(A.industry_type) = vEmpty;
251
252 UPDATE ul_customer A
253 SET status = vFail,
254 message = message || 'Industry Type is not registred on system, '
255 WHERE A.upload_header_id = pUploadHeaderId
256 AND TRIM(A.industry_type) <> vEmpty
257 AND NOT EXISTS(
258 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'INDUSTRYTYPE' AND Z.code = A.industry_type
259 );
260
261 -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
262 UPDATE ul_customer A
263 SET status = vFail,
264 message = message || 'NPWP status must be filled, '
265 WHERE A.upload_header_id = pUploadHeaderId
266 AND TRIM(A.npwp_status) = vEmpty;
267
268 UPDATE ul_customer A
269 SET status = vFail,
270 message = message || 'NPWP status is not registred on system, '
271 WHERE A.upload_header_id = pUploadHeaderId
272 AND TRIM(A.npwp_status) <> vEmpty
273 AND NOT EXISTS(
274 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'TAXCODE' AND Z.code = A.npwp_status
275 );
276
277 -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
278 UPDATE ul_customer A
279 SET status = vFail,
280 message = message || 'Price level must be filled, '
281 WHERE A.upload_header_id = pUploadHeaderId
282 AND TRIM(A.price_level) = vEmpty;
283
284 UPDATE ul_customer A
285 SET status = vFail,
286 message = message || 'Price level is not registred on system, '
287 WHERE A.upload_header_id = pUploadHeaderId
288 AND TRIM(A.price_level) <> vEmpty
289 AND NOT EXISTS(
290 SELECT 1 FROM m_level_price Z WHERE Z.level_price_code = A.price_level AND Z.tenant_id = vTenantId
291 );
292
293 -- # TOP in days wajib diisi & harus integer, dan harus >= 0
294 UPDATE ul_customer A
295 SET status = vFail,
296 message = message || 'TOP in days must be filled, '
297 WHERE A.upload_header_id = pUploadHeaderId
298 AND TRIM(A.top_in_days) = vEmpty;
299
300 UPDATE ul_customer A
301 SET status = vFail,
302 message = message || 'TOP in days must be integer, '
303 WHERE A.upload_header_id = pUploadHeaderId
304 AND TRIM(top_in_days) <> vEmpty
305 AND NOT is_integer(A.top_in_days);
306
307 WITH data_customer AS (
308 SELECT ul_customer_id, top_in_days
309 FROM ul_customer
310 WHERE upload_header_id = pUploadHeaderId
311 AND TRIM(top_in_days) <> vEmpty
312 AND is_integer(top_in_days)
313 )
314 UPDATE ul_customer A
315 SET status = vFail,
316 message = message || 'TOP in days must be greater than or equals 0, '
317 FROM data_customer B
318 WHERE A.ul_customer_id = B.ul_customer_id
319 AND B.top_in_days::numeric < 0;
320
321 -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
322 UPDATE ul_customer A
323 SET status = vFail,
324 message = message || 'Region code must be filled, '
325 WHERE A.upload_header_id = pUploadHeaderId
326 AND TRIM(A.kode_region) = vEmpty;
327
328 UPDATE ul_customer A
329 SET status = vFail,
330 message = message || 'Region code is not registred on system, '
331 WHERE A.upload_header_id = pUploadHeaderId
332 AND TRIM(A.kode_region) <> vEmpty
333 AND NOT EXISTS(
334 SELECT 1 FROM m_region Z WHERE Z.region_code = A.kode_region AND Z.tenant_id = vTenantId
335 );
336
337 -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
338 UPDATE ul_customer A
339 SET status = vFail,
340 message = message || 'Credit limit must be filled, '
341 WHERE A.upload_header_id = pUploadHeaderId
342 AND TRIM(A.credit_limit) = vEmpty;
343
344 UPDATE ul_customer A
345 SET status = vFail,
346 message = message || 'Credit limit must be numeric, '
347 WHERE A.upload_header_id = pUploadHeaderId
348 AND TRIM(credit_limit) <> vEmpty
349 AND NOT is_numeric(A.credit_limit);
350
351 WITH data_customer AS (
352 SELECT ul_customer_id, credit_limit
353 FROM ul_customer
354 WHERE upload_header_id = pUploadHeaderId
355 AND TRIM(credit_limit) <> vEmpty
356 AND is_numeric(credit_limit)
357 )
358 UPDATE ul_customer A
359 SET status = vFail,
360 message = message || 'Credit limit must be greater than or equals -1, '
361 FROM data_customer B
362 WHERE A.ul_customer_id = B.ul_customer_id
363 AND B.credit_limit::numeric < -1;
364
365 -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
366 UPDATE ul_customer A
367 SET status = vFail,
368 message = message || 'Disc reguler pct must be filled, '
369 WHERE A.upload_header_id = pUploadHeaderId
370 AND TRIM(A.disc_reguler_pct) = vEmpty;
371
372 UPDATE ul_customer A
373 SET status = vFail,
374 message = message || 'Disc reguler pct must be numeric, '
375 WHERE A.upload_header_id = pUploadHeaderId
376 AND TRIM(disc_reguler_pct) <> vEmpty
377 AND NOT is_numeric(A.disc_reguler_pct);
378
379 WITH data_customer AS (
380 SELECT ul_customer_id, disc_reguler_pct
381 FROM ul_customer
382 WHERE upload_header_id = pUploadHeaderId
383 AND TRIM(disc_reguler_pct) <> vEmpty
384 AND is_numeric(disc_reguler_pct)
385 ), val_greater_or_equals_zero AS (
386 UPDATE ul_customer A
387 SET status = vFail,
388 message = message || 'Disc reguler pct must be greater than or equals to zero, '
389 FROM data_customer B
390 WHERE A.ul_customer_id = B.ul_customer_id
391 AND B.disc_reguler_pct::numeric < 0
392 )
393 UPDATE ul_customer A
394 SET status = vFail,
395 message = message || 'Disc reguler pct must be less than or equals to 100, '
396 FROM data_customer B
397 WHERE A.ul_customer_id = B.ul_customer_id
398 AND B.disc_reguler_pct::numeric > 100;
399
400 -- # Setiap kode customer baru harus memiliki minimal 1 partner address baru
401 UPDATE ul_customer A
402 SET status = vFail,
403 message = message || 'Customer must have address at least 1, '
404 WHERE A.upload_header_id = pUploadHeaderId
405 AND TRIM(A.kode_pelanggan) <> vEmpty
406 AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
407 AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
408 WHERE Z.upload_header_id = pUploadHeaderId
409 AND Z.kode_pelanggan = A.kode_pelanggan);
410
411 -- # Partner address harus unique by address desc untuk setiap kode pelanggan
412 WITH data_partner_address AS (
413 SELECT A.kode_pelanggan, A.address_desc
414 FROM tt_ul_customer_partner_address A
415 WHERE A.upload_header_id = pUploadHeaderId
416 GROUP BY A.kode_pelanggan, A.address_desc
417 HAVING COUNT(1) > 1
418 )
419 UPDATE ul_customer A
420 SET status = vFail,
421 message = message || 'Duplicate address desc, '
422 WHERE A.upload_header_id = pUploadHeaderId
423 AND EXISTS (
424 SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_desc = Z.address_desc
425 );
426
427 -- # Pada partner address desc yang sudah ada di system, address 1 & address 2 & address 3 nya harus belum ada di system
428 WITH partner_desc_existing AS (
429 SELECT A.upload_header_id, A.kode_pelanggan, A.address_desc, A.address_1, A.address_2, A.address_3
430 FROM tt_ul_customer_partner_address A
431 WHERE A.upload_header_id = pUploadHeaderId
432 AND EXISTS (
433 SELECT 1 FROM m_partner_address Z
434 WHERE A.kode_pelanggan = f_get_partner_code(Z.partner_id)
435 AND Z.tenant_id = vTenantId
436 )
437 )
438 UPDATE ul_customer A
439 SET status = vFail,
440 message = message || 'Address 1 and address 2 and address 3 already exists on system, '
441 FROM partner_desc_existing B
442 WHERE A.upload_header_id = pUploadHeaderId
443 AND A.upload_header_id = B.upload_header_id
444 AND A.kode_pelanggan = B.kode_pelanggan
445 AND A.address_1 = B.address_1
446 AND A.address_2 = B.address_2
447 AND A.address_3 = B.address_3
448 AND EXISTS (
449 SELECT 1 FROM m_partner_address Z
450 WHERE B.kode_pelanggan = f_get_partner_code(Z.partner_id)
451 AND Z.address1 = B.address_1
452 AND Z.address2 = B.address_2
453 AND Z.address3 = B.address_3
454 AND Z.tenant_id = vTenantId
455 AND Z.address_desc <> B.address_desc
456 );
457
458 -- # Partner address harus unique by address 1, address 2, address 3 untuk setiap kode pelanggan
459 WITH data_partner_address AS (
460 SELECT A.kode_pelanggan, A.address_1, A.address_2, A.address_3
461 FROM tt_ul_customer_partner_address A
462 WHERE A.upload_header_id = pUploadHeaderId
463 GROUP BY A.kode_pelanggan, A.address_1, A.address_2, A.address_3
464 HAVING COUNT(1) > 1
465 )
466 UPDATE ul_customer A
467 SET status = vFail,
468 message = message || 'Duplicate address 1 and address 2 and address 3, '
469 WHERE A.upload_header_id = pUploadHeaderId
470 AND EXISTS (
471 SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_1 = Z.address_1 AND A.address_2 = Z.address_2 AND A.address_3 = Z.address_3
472 );
473
474 -- # Official address yang bernilai Y harus cuma ada 1 untuk tiap customer
475 WITH data_official_addr AS (
476 SELECT A.kode_pelanggan, A.official_addr
477 FROM tt_ul_customer_partner_address A
478 WHERE A.upload_header_id = pUploadHeaderId
479 AND A.official_addr = vYes
480 GROUP BY A.kode_pelanggan, A.official_addr
481 HAVING COUNT(1) > 1
482 )
483 UPDATE ul_customer A
484 SET status = vFail,
485 message = message || 'Customer can only have 1 official address, '
486 WHERE A.upload_header_id = pUploadHeaderId
487 AND EXISTS (
488 SELECT 1 FROM data_official_addr Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.official_addr = Z.official_addr
489 );
490
491 -- # Untuk customer baru harus punya partner address dengan flg official = Y minimal 1
492 UPDATE ul_customer A
493 SET status = vFail,
494 message = message || 'Customer must have exactly one official address, '
495 WHERE A.upload_header_id = pUploadHeaderId
496 AND TRIM(A.kode_pelanggan) <> vEmpty
497 AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
498 AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
499 WHERE Z.upload_header_id = pUploadHeaderId
500 AND Z.kode_pelanggan = A.kode_pelanggan
501 AND Z.official_addr = vYes);
502
503 -- # Untuk customer existing hanya boleh mempunyai 1 Official address
504 WITH data_official_address AS (
505 SELECT A.kode_pelanggan, COUNT(1) AS result
506 FROM tt_ul_customer_partner_address A
507 WHERE A.upload_header_id = pUploadHeaderId
508 AND A.official_addr = vYes
509 GROUP BY A.kode_pelanggan
510
511 UNION ALL
512
513 SELECT f_get_partner_code(A.partner_id) AS kode_pelanggan, COUNT(1) AS result
514 FROM m_partner_address A
515 WHERE A.flg_official = vYes
516 AND EXISTS (
517 SELECT 1 FROM tt_ul_customer_unique Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
518 )
519 AND NOT EXISTS (
520 SELECT 1 FROM tt_ul_customer_partner_address Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
521 AND A.address_desc = Z.address_desc
522 )
523 GROUP BY A.partner_id
524 ), sum_data_official_address AS (
525 SELECT kode_pelanggan, SUM(result)
526 FROM data_official_address
527 GROUP BY kode_pelanggan
528 HAVING SUM(result) > 1
529 )
530 UPDATE ul_customer A
531 SET status = vFail,
532 message = message || 'Customer must have exactly 1 official address include existing address, '
533 WHERE A.upload_header_id = pUploadHeaderId
534 AND EXISTS (
535 SELECT 1 FROM m_partner Z WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
536 )
537 AND EXISTS (
538 SELECT 1 FROM sum_data_official_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan
539 );
540
541 -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
542 UPDATE ul_customer A
543 SET status = vFail,
544 message = message || 'Address desc must be filled, '
545 WHERE A.upload_header_id = pUploadHeaderId
546 AND TRIM(A.kode_pelanggan) <> vEmpty
547 AND TRIM(A.address_desc) = vEmpty
548 AND TRIM(A.address_1
549 ||A.address_2
550 ||A.address_3
551 ||A.zip_code
552 ||A.city
553 ||A.country
554 ||A.official_addr
555 ||A.shipping_addr
556 ||A.billing_addr
557 ||CASE WHEN A.longitude='0' THEN vEmpty ELSE A.longitude END
558 ||CASE WHEN A.latitude='0' THEN vEmpty ELSE A.latitude END
559 ||A.phone_1
560 ||A.phone_2
561 ||A.fax_1
562 ||A.fax_2) <> vEmpty;
563
564 UPDATE ul_customer A
565 SET status = vFail,
566 message = message || 'Max address desc is 100 character, '
567 WHERE A.upload_header_id = pUploadHeaderId
568 AND TRIM(A.address_desc) <> vEmpty
569 AND length(A.address_desc) > 100;
570
571 -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
572 UPDATE ul_customer A
573 SET status = vFail,
574 message = message || 'Address 1 must be filled, '
575 WHERE A.upload_header_id = pUploadHeaderId
576 AND TRIM(A.address_desc) <> vEmpty
577 AND TRIM(A.address_1) = vEmpty;
578
579 UPDATE ul_customer A
580 SET status = vFail,
581 message = message || 'Max address 1 is 100 character, '
582 WHERE A.upload_header_id = pUploadHeaderId
583 AND TRIM(A.address_1) <> vEmpty
584 AND length(A.address_1) > 100;
585
586 -- # Max character Address 2 adalah 100 jika diisi
587 UPDATE ul_customer A
588 SET status = vFail,
589 message = message || 'Max address 2 is 100 character, '
590 WHERE A.upload_header_id = pUploadHeaderId
591 AND TRIM(A.address_2) <> vEmpty
592 AND length(A.address_2) > 100;
593
594 -- # Max character Address 3 adalah 100 jika diisi
595 UPDATE ul_customer A
596 SET status = vFail,
597 message = message || 'Max address 3 is 100 character, '
598 WHERE A.upload_header_id = pUploadHeaderId
599 AND TRIM(A.address_3) <> vEmpty
600 AND length(A.address_3) > 100;
601
602 -- # Max character Zip Code adalah 100 jika diisi
603 UPDATE ul_customer A
604 SET status = vFail,
605 message = message || 'Max zip code is 100 character, '
606 WHERE A.upload_header_id = pUploadHeaderId
607 AND TRIM(A.zip_code) <> vEmpty
608 AND length(A.zip_code) > 100;
609
610 -- # City wajib diisi & harus terdaftar di system (m_city)
611 UPDATE ul_customer A
612 SET status = vFail,
613 message = message || 'City 1 must be filled, '
614 WHERE A.upload_header_id = pUploadHeaderId
615 AND TRIM(A.address_desc) <> vEmpty
616 AND TRIM(A.city) = vEmpty;
617
618 UPDATE ul_customer A
619 SET status = vFail,
620 message = message || 'City is not registred on system, '
621 WHERE A.upload_header_id = pUploadHeaderId
622 AND TRIM(A.city) <> vEmpty
623 AND NOT EXISTS(
624 SELECT 1 FROM m_city Z WHERE Z.city_code = A.city AND Z.tenant_id = vTenantId
625 );
626
627 -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
628 UPDATE ul_customer A
629 SET status = vFail,
630 message = message || 'Country must be filled, '
631 WHERE A.upload_header_id = pUploadHeaderId
632 AND TRIM(A.address_desc) <> vEmpty
633 AND TRIM(A.country) = vEmpty;
634
635 UPDATE ul_customer A
636 SET status = vFail,
637 message = message || 'Country is not registred on system, '
638 WHERE A.upload_header_id = pUploadHeaderId
639 AND TRIM(A.country) <> vEmpty
640 AND NOT EXISTS(
641 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'COUNTRY' AND Z.code = A.country
642 );
643
644 -- # Official addr wajib diisi dengan nilai Y/N
645 UPDATE ul_customer A
646 SET status = vFail,
647 message = message || 'Official addr can only be filled with Y or N, '
648 WHERE A.upload_header_id = pUploadHeaderId
649 AND TRIM(A.address_desc) <> vEmpty
650 AND A.official_addr NOT IN (vYes, vNo);
651
652 -- # Shipping Addr Wajib diisi dengan nilai Y/N
653 UPDATE ul_customer A
654 SET status = vFail,
655 message = message || 'Shipping Addr can only be filled with Y or N, '
656 WHERE A.upload_header_id = pUploadHeaderId
657 AND TRIM(A.address_desc) <> vEmpty
658 AND A.shipping_addr NOT IN (vYes, vNo);
659
660 -- # Billing Addr Wajib diisi dengan nilai Y/N
661 UPDATE ul_customer A
662 SET status = vFail,
663 message = message || 'Billing Addr can only be filled with Y or N, '
664 WHERE A.upload_header_id = pUploadHeaderId
665 AND TRIM(A.address_desc) <> vEmpty
666 AND A.billing_addr NOT IN (vYes, vNo);
667
668 -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
669 UPDATE ul_customer A
670 SET status = vFail,
671 message = message || 'Longitude must be numeric, '
672 WHERE A.upload_header_id = pUploadHeaderId
673 AND TRIM(A.longitude) <> vEmpty
674 AND NOT is_numeric(A.longitude);
675
676 -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
677 UPDATE ul_customer A
678 SET status = vFail,
679 message = message || 'Latitude must be numeric, '
680 WHERE A.upload_header_id = pUploadHeaderId
681 AND TRIM(A.latitude) <> vEmpty
682 AND NOT is_numeric(A.latitude);
683
684 -- # Max character Phone 1 adalah 100, jika diisi
685 UPDATE ul_customer A
686 SET status = vFail,
687 message = message || 'Max phone 1 is 100 character, '
688 WHERE A.upload_header_id = pUploadHeaderId
689 AND TRIM(A.phone_1) <> vEmpty
690 AND length(A.phone_1) > 100;
691
692 -- # Max character Phone 2 adalah 100, jika diisi
693 UPDATE ul_customer A
694 SET status = vFail,
695 message = message || 'Max phone 2 is 100 character, '
696 WHERE A.upload_header_id = pUploadHeaderId
697 AND TRIM(A.phone_2) <> vEmpty
698 AND length(A.phone_2) > 100;
699
700 -- # Max character Fax 1 adalah 100, jika diisi
701 UPDATE ul_customer A
702 SET status = vFail,
703 message = message || 'Max fax 1 is 100 character, '
704 WHERE A.upload_header_id = pUploadHeaderId
705 AND TRIM(A.fax_1) <> vEmpty
706 AND length(A.fax_1) > 100;
707
708 -- # Max character Fax 2 adalah 100, jika diisi
709 UPDATE ul_customer A
710 SET status = vFail,
711 message = message || 'Max fax 2 is 100 character, '
712 WHERE A.upload_header_id = pUploadHeaderId
713 AND TRIM(A.fax_2) <> vEmpty
714 AND length(A.fax_2) > 100;
715
716 -- # Setiap kode customer baru harus memiliki minimal 1 partner baru
717 UPDATE ul_customer A
718 SET status = vFail,
719 message = message || 'Customer must have Partner CP at least 1, '
720 WHERE A.upload_header_id = pUploadHeaderId
721 AND TRIM(A.kode_pelanggan) <> vEmpty
722 AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
723 AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_cp Z
724 WHERE Z.upload_header_id = pUploadHeaderId
725 AND Z.kode_pelanggan = A.kode_pelanggan);
726
727 -- # Partner CP harus unique by CP Name untuk setiap kode pelanggan
728 WITH data_partner_cp AS (
729 SELECT A.kode_pelanggan, A.cp_name
730 FROM tt_ul_customer_partner_cp A
731 WHERE A.upload_header_id = pUploadHeaderId
732 GROUP BY A.kode_pelanggan, A.cp_name
733 HAVING COUNT(1) > 1
734 )
735 UPDATE ul_customer A
736 SET status = vFail,
737 message = message || 'Duplicate CP Name, '
738 WHERE A.upload_header_id = pUploadHeaderId
739 AND EXISTS (
740 SELECT 1 FROM data_partner_cp Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.cp_name = Z.cp_name
741 );
742
743 -- # CP Name wajib diisi apabila kolom partner cp lainya ada yang diisi & Max character Address Desc adalah 100
744 UPDATE ul_customer A
745 SET status = vFail,
746 message = message || 'CP name must be filled, '
747 WHERE A.upload_header_id = pUploadHeaderId
748 AND TRIM(A.kode_pelanggan) <> vEmpty
749 AND TRIM(A.cp_name) = vEmpty
750 AND TRIM(A.cp_job
751 ||A.cp_department
752 ||A.cp_job_level
753 ||A.cp_email
754 ||A.cp_phone_1
755 ||A.cp_phone_2
756 ||A.cp_mobile_1
757 ||A.cp_mobile_2) <> vEmpty;
758
759 UPDATE ul_customer A
760 SET status = vFail,
761 message = message || 'Max CP name is 100 character, '
762 WHERE A.upload_header_id = pUploadHeaderId
763 AND TRIM(A.cp_name) <> vEmpty
764 AND length(A.cp_name) > 100;
765
766 -- # CP Job wajib diisi dan max character adalah 100
767 UPDATE ul_customer A
768 SET status = vFail,
769 message = message || 'CP job must be filled, '
770 WHERE A.upload_header_id = pUploadHeaderId
771 AND TRIM(A.cp_name) <> vEmpty
772 AND TRIM(A.cp_job) = vEmpty;
773
774 UPDATE ul_customer A
775 SET status = vFail,
776 message = message || 'Max CP job is 100 character, '
777 WHERE A.upload_header_id = pUploadHeaderId
778 AND TRIM(A.cp_job) <> vEmpty
779 AND length(A.cp_job) > 100;
780
781 -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
782 UPDATE ul_customer A
783 SET status = vFail,
784 message = message || 'CP Department must be filled, '
785 WHERE A.upload_header_id = pUploadHeaderId
786 AND TRIM(A.cp_name) <> vEmpty
787 AND TRIM(A.cp_department) = vEmpty;
788
789 UPDATE ul_customer A
790 SET status = vFail,
791 message = message || 'CP department is not registred on system, '
792 WHERE A.upload_header_id = pUploadHeaderId
793 AND TRIM(A.cp_department) <> vEmpty
794 AND NOT EXISTS(
795 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'DEPARTMENT' AND Z.code = A.cp_department
796 );
797
798 -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
799 UPDATE ul_customer A
800 SET status = vFail,
801 message = message || 'CP job level must be filled, '
802 WHERE A.upload_header_id = pUploadHeaderId
803 AND TRIM(A.cp_name) <> vEmpty
804 AND TRIM(A.cp_job_level) = vEmpty;
805
806 UPDATE ul_customer A
807 SET status = vFail,
808 message = message || 'CP job level is not registred on system, '
809 WHERE A.upload_header_id = pUploadHeaderId
810 AND TRIM(A.cp_job_level) <> vEmpty
811 AND NOT EXISTS(
812 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'JOBLEVEL' AND Z.code = A.cp_job_level
813 );
814
815 -- # Max character CP Email adalah 100 jika diisi
816 UPDATE ul_customer A
817 SET status = vFail,
818 message = message || 'Max CP Email is 100 character, '
819 WHERE A.upload_header_id = pUploadHeaderId
820 AND TRIM(A.cp_email) <> vEmpty
821 AND length(A.cp_email) > 100;
822
823 -- # Max character CP Phone 1 adalah 100 jika diisi
824 UPDATE ul_customer A
825 SET status = vFail,
826 message = message || 'Max CP Phone 1 is 100 character, '
827 WHERE A.upload_header_id = pUploadHeaderId
828 AND TRIM(A.cp_phone_1) <> vEmpty
829 AND length(A.cp_phone_1) > 100;
830
831 -- # Max character CP Phone 2 adalah 100 jika diisi
832 UPDATE ul_customer A
833 SET status = vFail,
834 message = message || 'Max CP Phone 2 is 100 character, '
835 WHERE A.upload_header_id = pUploadHeaderId
836 AND TRIM(A.cp_phone_2) <> vEmpty
837 AND length(A.cp_phone_2) > 100;
838
839 -- # Max character CP Mobile 1 adalah 100 jika diisi
840 UPDATE ul_customer A
841 SET status = vFail,
842 message = message || 'Max CP Mobile 1 is 100 character, '
843 WHERE A.upload_header_id = pUploadHeaderId
844 AND TRIM(A.cp_mobile_1) <> vEmpty
845 AND length(A.cp_mobile_1) > 100;
846
847 -- # Max character CP Mobile 2 adalah 100 jika diisi
848 UPDATE ul_customer A
849 SET status = vFail,
850 message = message || 'Max CP Mobile 2 is 100 character, '
851 WHERE A.upload_header_id = pUploadHeaderId
852 AND TRIM(A.cp_mobile_2) <> vEmpty
853 AND length(A.cp_mobile_2) > 100;
854
855 -- # NPWP harus diisi jika Status NPWP = Y
856 UPDATE ul_customer A
857 SET status = vFail,
858 message = message || 'NPWP must be filled when Status NPWP is Y, '
859 WHERE A.upload_header_id = pUploadHeaderId
860 AND TRIM(A.npwp) = vEmpty
861 AND A.status_pkp = vYes;
862
863 -- # Jika NPWP diisi maka max characternya adalah 50
864 UPDATE ul_customer A
865 SET status = vFail,
866 message = message || 'Max NPWP is 50 character, '
867 WHERE A.upload_header_id = pUploadHeaderId
868 AND TRIM(A.npwp) <> vEmpty
869 AND length(A.npwp) > 50;
870
871 -- # Status PKP wajib diisi dan nilainya harus Y/N
872 UPDATE ul_customer A
873 SET status = vFail,
874 message = message || 'Status PKP can only be filled with Y or N, '
875 WHERE A.upload_header_id = pUploadHeaderId
876 AND A.status_pkp NOT IN (vYes, vNo);
877
878 -- # Active harus diisi dengan nilai Y/N
879 UPDATE ul_customer A
880 SET status = vFail,
881 message = message || 'Active can only be filled with Y or N, '
882 WHERE A.upload_header_id = pUploadHeaderId
883 AND A.active NOT IN (vYes, vNo);
884
885 WITH invalid_data_customer AS (
886 SELECT A.kode_pelanggan
887 FROM tt_ul_customer_unique A
888 WHERE A.upload_header_id = pUploadHeaderId
889 GROUP BY A.kode_pelanggan
890 HAVING COUNT(1) > 1
891 )
892 -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
893 UPDATE ul_customer A
894 SET status = vFail,
895 message = message || 'Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active", '
896 WHERE A.upload_header_id = pUploadHeaderId
897 AND EXISTS (
898 SELECT 1 FROM invalid_data_customer Z WHERE A.kode_pelanggan = Z.kode_pelanggan
899 );
900
901 -- Cek apakah ada item CSV yang statusnya fail
902 IF EXISTS ( SELECT 1 FROM ul_customer B
903 WHERE B.upload_header_id = pUploadHeaderId
904 AND B.status = vFail ) THEN
905
906 -- Sesuaikan message, agar message paling belakang tidak ada koma
907 UPDATE ul_customer A
908 SET message = substr(A.message, 1, length(A.message)-2)
909 WHERE A.upload_header_id = pUploadHeaderId
910 AND A.status = vFail
911 AND TRIM(A.message) != vEmpty;
912
913 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
914 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
915 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
916 UPDATE ul_customer A
917 SET status = vFail,
918 update_status = vActionError
919 WHERE upload_header_id = pUploadHeaderId;
920
921 -- Hitung jumlah data yang FAIL
922 SELECT COUNT(1) INTO vCountFail
923 FROM ul_customer
924 WHERE upload_header_id = pUploadHeaderId
925 AND status = vFail;
926 ELSE
927
928 -- RESET table temp
929 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
930
931 -- GET id OU company
932 SELECT A.ou_id INTO vOuCompanyId
933 FROM t_ou A
934 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
935 WHERE A.tenant_id = vTenantId
936 AND B.flg_bu = vYes
937 AND B.flg_sub_bu = vNo
938 AND B.flg_branch = vNo
939 AND B.tenant_id = vTenantId;
940
941 -- Update status non FAIL to OK
942 UPDATE ul_customer
943 SET status = vOk
944 WHERE upload_header_id = pUploadHeaderId;
945
946 -- UPDATE OK data untuk customer code terdaftar
947 WITH update_data_customer AS (
948 UPDATE m_partner A SET
949 partner_name=B.nama_pelanggan,
950 ctgr_partner_id=C.ctgr_partner_id,
951 partner_ext_name=B.nama_external,
952 rank=B.rank,
953 line_of_business=B.line_of_business,
954 industry_type=B.industry_type,
955 npwp_status=B.npwp_status,
956 price_level=B.price_level,
957 update_datetime=vDatetime,
958 update_user_id=vUserId,
959 version=A.version+1,
960 active=B.active,
961 active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
962 THEN vDatetime
963 ELSE A.active_datetime
964 END,
965 non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
966 THEN vDatetime
967 ELSE A.non_active_datetime
968 END
969 FROM tt_ul_customer_unique B
970 INNER JOIN m_ctgr_partner C ON B.kode_kategori = C.ctgr_partner_code AND c.tenant_id = vTenantId
971 WHERE B.upload_header_id = pUploadHeaderId
972 AND A.partner_code = B.kode_pelanggan
973 AND A.tenant_id = vTenantId
974 RETURNING A.*
975 )
976 INSERT INTO tt_ul_partner(
977 upload_header_id, action_type, partner_id, tenant_id, partner_code,
978 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
979 flg_holding, rank, create_datetime, create_user_id, update_datetime,
980 update_user_id, version, active, active_datetime, non_active_datetime,
981 line_of_business, price_level, industry_type, npwp_status)
982 SELECT pUploadHeaderId, vActionUpdate, partner_id, tenant_id, partner_code,
983 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
984 flg_holding, rank, create_datetime, create_user_id, update_datetime,
985 update_user_id, version, active, active_datetime, non_active_datetime,
986 line_of_business, price_level, industry_type, npwp_status
987 FROM update_data_customer;
988
989 -- INSERT OK data untuk customer code baru
990 WITH insert_data_customer AS (
991 INSERT INTO m_partner(
992 tenant_id, partner_code, partner_name, ctgr_partner_id,
993 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
994 create_user_id, update_datetime, update_user_id, version, active,
995 active_datetime, non_active_datetime, line_of_business, price_level,
996 industry_type, npwp_status)
997 SELECT vTenantId, A.kode_pelanggan, A.nama_pelanggan, B.ctgr_partner_id,
998 A.nama_external, vEmptyId, vEmptyId, vNo, A.rank, vDatetime,
999 vUserId, vDatetime, vUserId, 0, A.active,
1000 CASE WHEN A.active = vYes
1001 THEN vDatetime
1002 ELSE vSpaceValue
1003 END AS active_datetime,
1004 CASE WHEN A.active = vNo
1005 THEN vDatetime
1006 ELSE vSpaceValue
1007 END AS non_active_datetime,
1008 vSpaceValue, vSpaceValue,
1009 vSpaceValue, vSpaceValue
1010 FROM tt_ul_customer_unique A
1011 INNER JOIN m_ctgr_partner B ON A.kode_kategori = B.ctgr_partner_code AND B.tenant_id = vTenantId
1012 WHERE A.upload_header_id = pUploadHeaderId
1013 AND NOT EXISTS (
1014 SELECT 1 FROM m_partner Z
1015 WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
1016 )
1017 RETURNING *
1018 )
1019 INSERT INTO tt_ul_partner(
1020 upload_header_id, action_type, partner_id, tenant_id, partner_code,
1021 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1022 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1023 update_user_id, version, active, active_datetime, non_active_datetime,
1024 line_of_business, price_level, industry_type, npwp_status)
1025 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
1026 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
1027 flg_holding, rank, create_datetime, create_user_id, update_datetime,
1028 update_user_id, version, active, active_datetime, non_active_datetime,
1029 line_of_business, price_level, industry_type, npwp_status
1030 FROM insert_data_customer;
1031
1032 -- ACTION FOR UPPDATE
1033 IF EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
1034
1035 -- Ubah update_status menjadi U jika data digunakan untuk Update
1036 UPDATE ul_customer A SET
1037 update_status = vActionUpdate
1038 WHERE A.upload_header_id = pUploadHeaderId
1039 AND EXISTS (
1040 SELECT 1 FROM tt_ul_partner B
1041 WHERE A.kode_pelanggan = B.partner_code
1042 AND B.tenant_id = vTenantId
1043 AND B.action_type = vActionUpdate
1044 );
1045
1046 -- UPDATE data partner address
1047 UPDATE m_partner_address A SET
1048 address1=C.address_1,
1049 address2=C.address_2,
1050 address3=C.address_3,
1051 city=C.city,
1052 zip_code=C.zip_code,
1053 state_or_province=D.province,
1054 country=C.country,
1055 phone1=C.phone_1,
1056 phone2=C.phone_2,
1057 fax1=C.fax_1,
1058 fax2=C.fax_2,
1059 flg_default=CASE WHEN C.official_addr = vYes THEN vYes ELSE vNo END,
1060 update_datetime=vDatetime,
1061 update_user_id=vUserId,
1062 version=A.version+1,
1063 flg_ship=C.shipping_addr,
1064 flg_bill=C.billing_addr,
1065 flg_official=C.official_addr,
1066 longitude=C.longitude::numeric,
1067 latitude=C.latitude::numeric
1068 FROM tt_ul_partner B
1069 INNER JOIN tt_ul_customer_partner_address C ON B.partner_code = C.kode_pelanggan
1070 INNER JOIN m_city D ON C.city = D.city_code AND D.tenant_id = vTenantId
1071 WHERE B.upload_header_id = pUploadHeaderId
1072 AND B.upload_header_id = C.upload_header_id
1073 AND A.partner_id = B.partner_id
1074 AND A.address_desc = C.address_desc
1075 AND A.tenant_id = B.tenant_id
1076 AND B.action_type = vActionUpdate;
1077
1078 -- INSERT data partner new address
1079 INSERT INTO m_partner_address(
1080 tenant_id, partner_id, address_desc, address1,
1081 address2, address3, city, zip_code, state_or_province, country,
1082 phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
1083 update_datetime, update_user_id, version, active, active_datetime,
1084 non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
1085 flg_others, longitude, latitude)
1086 SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
1087 B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
1088 B.phone_1, B.phone_2, B.fax_1, B.fax_2, CASE WHEN B.official_addr = vYes THEN vYes ELSE vNo END, vDatetime, vUserId,
1089 vDatetime, vUserId, 0, vYes, vDatetime,
1090 vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
1091 vNo, B.longitude::numeric, B.latitude::numeric
1092 FROM tt_ul_partner A
1093 INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
1094 INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
1095 WHERE A.upload_header_id = pUploadHeaderId
1096 AND A.upload_header_id = B.upload_header_id
1097 AND A.action_type = vActionUpdate
1098 AND NOT EXISTS (
1099 SELECT 1 FROM m_partner_address Z
1100 WHERE B.address_desc = Z.address_desc
1101 AND A.partner_id = Z.partner_id
1102 AND Z.tenant_id = vTenantId
1103 );
1104
1105 -- UPDATE data partner CP
1106 UPDATE m_partner_cp A SET
1107 cp_job=C.cp_job,
1108 phone1=C.cp_phone_1,
1109 phone2=C.cp_phone_2,
1110 email=C.cp_email,
1111 update_datetime=vDatetime,
1112 update_user_id=vUserId,
1113 version=A.version+1,
1114 department=C.cp_department,
1115 job_level=C.cp_job_level,
1116 mobile_phone1=C.cp_mobile_1,
1117 mobile_phone2=C.cp_mobile_2
1118 FROM tt_ul_partner B
1119 INNER JOIN tt_ul_customer_partner_cp C ON B.partner_code = C.kode_pelanggan
1120 WHERE B.upload_header_id = pUploadHeaderId
1121 AND B.upload_header_id = C.upload_header_id
1122 AND A.partner_id = B.partner_id
1123 AND A.cp_name = C.cp_name
1124 AND B.action_type = vActionUpdate;
1125
1126 -- INSERT data partner cp
1127 INSERT INTO m_partner_cp(
1128 tenant_id, partner_id, cp_name, cp_job, address1,
1129 address2, address3, city, zip_code, country, phone1, phone2,
1130 fax1, fax2, email, create_datetime, create_user_id, update_datetime,
1131 update_user_id, version, active, active_datetime, non_active_datetime,
1132 department, job_level, phone_ext1, phone_ext2, mobile_phone1,
1133 mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
1134 flg_email_notif_ar)
1135 SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
1136 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
1137 vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
1138 vUserId, 0, vYes, vDatetime, vSpaceValue,
1139 B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
1140 B.cp_mobile_2, vNo, vNo, vNo,
1141 vNo
1142 FROM tt_ul_partner A
1143 INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
1144 WHERE A.upload_header_id = pUploadHeaderId
1145 AND A.upload_header_id = B.upload_header_id
1146 AND A.action_type = vActionUpdate
1147 AND NOT EXISTS (
1148 SELECT 1 FROM m_partner_cp Z
1149 WHERE B.cp_name = Z.cp_name
1150 AND A.partner_id = Z.partner_id
1151 AND Z.tenant_id = vTenantId
1152 );
1153
1154 -- UPDATE data partner type
1155 UPDATE m_partner_type A SET
1156 due_date=C.top_in_days::integer,
1157 amount_limit=C.credit_limit::numeric,
1158 update_datetime=vDatetime,
1159 update_user_id=vUserId,
1160 version=A.version+1
1161 FROM tt_ul_partner B
1162 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
1163 WHERE B.upload_header_id = pUploadHeaderId
1164 AND B.upload_header_id = C.upload_header_id
1165 AND A.partner_id = B.partner_id
1166 AND B.action_type = vActionUpdate;
1167
1168 -- UPDATE data customer ext
1169 UPDATE m_customer_ext A SET
1170 regular_discount_percentage=C.disc_reguler_pct::numeric,
1171 update_datetime=vDatetime,
1172 update_user_id=vUserId,
1173 version=A.version+1
1174 FROM tt_ul_partner B
1175 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
1176 WHERE B.upload_header_id = pUploadHeaderId
1177 AND B.upload_header_id = C.upload_header_id
1178 AND A.partner_id = B.partner_id
1179 AND B.action_type = vActionUpdate;
1180
1181 -- UPDATE data customer ext for dlg
1182 UPDATE m_customer_ext_for_dlg A SET
1183 top_external=C.top_in_days::integer,
1184 update_datetime=vDatetime,
1185 update_user_id=vUserId,
1186 version=A.version+1
1187 FROM tt_ul_partner B
1188 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
1189 WHERE B.upload_header_id = pUploadHeaderId
1190 AND B.upload_header_id = C.upload_header_id
1191 AND A.partner_id = B.partner_id
1192 AND B.action_type = vActionUpdate;
1193
1194 -- DELETE existing data partner npwp
1195 DELETE FROM m_partner_npwp A
1196 WHERE EXISTS (
1197 SELECT 1 FROM tt_ul_partner Z WHERE A.partner_id = Z.partner_id
1198 );
1199
1200 -- INSERT data partner npwp
1201 INSERT INTO m_partner_npwp(
1202 tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
1203 address1, address2, address3, city, zip_code, country, phone1,
1204 phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
1205 update_user_id, version, active, active_datetime, non_active_datetime,
1206 flg_pkp, pkp_date)
1207 SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
1208 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
1209 vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
1210 vUserId, 0, vYes, vDatetime, vSpaceValue,
1211 B.status_pkp, to_char(current_date, 'YYYYMMDD')
1212 FROM tt_ul_partner A
1213 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1214 WHERE A.upload_header_id = pUploadHeaderId
1215 AND A.upload_header_id = B.upload_header_id
1216 AND A.action_type = vActionUpdate;
1217
1218 -- UPDATE data cust payment
1219 UPDATE m_cust_payment A SET
1220 due_payment_days=C.top_in_days::integer,
1221 update_datetime=vDatetime,
1222 update_user_id=vUserId,
1223 version=A.version+1
1224 FROM tt_ul_partner B
1225 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
1226 WHERE B.upload_header_id = pUploadHeaderId
1227 AND B.upload_header_id = C.upload_header_id
1228 AND A.partner_id = B.partner_id
1229 AND B.action_type = vActionUpdate;
1230
1231 END IF;
1232
1233 -- ACTION FOR INSERT
1234 IF EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
1235
1236 -- GET curr code from sysconfig
1237 SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
1238
1239 -- INSERT data partner address
1240 INSERT INTO m_partner_address(
1241 tenant_id, partner_id, address_desc, address1,
1242 address2, address3, city, zip_code, state_or_province, country,
1243 phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
1244 update_datetime, update_user_id, version, active, active_datetime,
1245 non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
1246 flg_others, longitude, latitude)
1247 SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
1248 B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
1249 B.phone_1, B.phone_2, B.fax_1, B.fax_2, CASE WHEN B.official_addr = vYes THEN vYes ELSE vNo END, vDatetime, vUserId,
1250 vDatetime, vUserId, 0, vYes, vDatetime,
1251 vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
1252 vNo, B.longitude::numeric, B.latitude::numeric
1253 FROM tt_ul_partner A
1254 INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
1255 INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
1256 WHERE A.upload_header_id = pUploadHeaderId
1257 AND A.upload_header_id = B.upload_header_id
1258 AND A.action_type = vActionInsert;
1259
1260 -- INSERT data partner cp
1261 INSERT INTO m_partner_cp(
1262 tenant_id, partner_id, cp_name, cp_job, address1,
1263 address2, address3, city, zip_code, country, phone1, phone2,
1264 fax1, fax2, email, create_datetime, create_user_id, update_datetime,
1265 update_user_id, version, active, active_datetime, non_active_datetime,
1266 department, job_level, phone_ext1, phone_ext2, mobile_phone1,
1267 mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
1268 flg_email_notif_ar)
1269 SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
1270 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
1271 vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
1272 vUserId, 0, vYes, vDatetime, vSpaceValue,
1273 B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
1274 B.cp_mobile_2, vNo, vNo, vNo,
1275 vNo
1276 FROM tt_ul_partner A
1277 INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
1278 WHERE A.upload_header_id = pUploadHeaderId
1279 AND A.upload_header_id = B.upload_header_id
1280 AND A.action_type = vActionInsert;
1281
1282 -- INSERT data customer ext
1283 INSERT INTO m_customer_ext(
1284 partner_id, tenant_id, regular_discount_percentage, create_datetime,
1285 create_user_id, update_datetime, update_user_id, version, active,
1286 active_datetime, non_active_datetime)
1287 SELECT A.partner_id, vTenantId, B.disc_reguler_pct::numeric, vDatetime,
1288 vUserId, vDatetime, vUserId, 0, vYes,
1289 vDatetime, vSpaceValue
1290 FROM tt_ul_partner A
1291 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1292 WHERE A.upload_header_id = pUploadHeaderId
1293 AND A.upload_header_id = B.upload_header_id
1294 AND A.action_type = vActionInsert;
1295
1296 -- INSERT data customer ext for dlg
1297 INSERT INTO m_customer_ext_for_dlg(
1298 partner_id, tenant_id, flg_show_inv_tax, create_datetime, create_user_id,
1299 update_datetime, update_user_id, version, top_external)
1300 SELECT A.partner_id, vTenantId, B.status_pkp, vDatetime, vUserId,
1301 vDatetime, vUserId, 0, B.top_in_days::integer
1302 FROM tt_ul_partner A
1303 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1304 WHERE A.upload_header_id = pUploadHeaderId
1305 AND A.upload_header_id = B.upload_header_id
1306 AND A.action_type = vActionInsert;
1307
1308 -- INSERT data partner npwp
1309 INSERT INTO m_partner_npwp(
1310 tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
1311 address1, address2, address3, city, zip_code, country, phone1,
1312 phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
1313 update_user_id, version, active, active_datetime, non_active_datetime,
1314 flg_pkp, pkp_date)
1315 SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
1316 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
1317 vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
1318 vUserId, 0, vYes, vDatetime, vSpaceValue,
1319 B.status_pkp, to_char(current_date, 'YYYYMMDD')
1320 FROM tt_ul_partner A
1321 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1322 WHERE A.upload_header_id = pUploadHeaderId
1323 AND A.upload_header_id = B.upload_header_id
1324 AND A.action_type = vActionInsert;
1325
1326 -- INSERT data partner ou
1327 INSERT INTO m_partner_ou(
1328 partner_id, ou_id, version, create_datetime, create_user_id,
1329 update_datetime, update_user_id)
1330 SELECT A.partner_id, vOuCompanyId, 0, vDatetime, vUserId,
1331 vDatetime, vUserId
1332 FROM tt_ul_partner A
1333 WHERE A.upload_header_id = pUploadHeaderId
1334 AND A.action_type = vActionInsert;
1335
1336 -- INSERT data partner type
1337 INSERT INTO m_partner_type(
1338 tenant_id, partner_id, group_partner, type_partner_id,
1339 due_date, curr_code, amount_limit, flg_commision, create_datetime,
1340 create_user_id, update_datetime, update_user_id, version, active,
1341 active_datetime, non_active_datetime)
1342 SELECT vTenantId, A.partner_id, vGroupCustomer, vTypePartnerId,
1343 B.top_in_days::integer, vCurrCode, B.credit_limit::numeric, vNo, vDatetime,
1344 vUserId, vDatetime, vUserId, 0, vYes,
1345 vDatetime, vSpaceValue
1346 FROM tt_ul_partner A
1347 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1348 WHERE A.upload_header_id = pUploadHeaderId
1349 AND A.upload_header_id = B.upload_header_id
1350 AND A.action_type = vActionInsert;
1351
1352 -- INSERT data partner rel
1353 INSERT INTO m_partner_rel(
1354 tenant_id, partner_id, relation_id, flg_bill,
1355 flg_shipp, create_datetime, create_user_id, update_datetime,
1356 update_user_id, version, active, active_datetime, non_active_datetime)
1357 SELECT vTenantId, A.partner_id, A.partner_id, vYes,
1358 vYes, vDatetime, vUserId, vDatetime,
1359 vUserId, 0, vYes, vDatetime, vSpaceValue
1360 FROM tt_ul_partner A
1361 WHERE A.upload_header_id = pUploadHeaderId
1362 AND A.action_type = vActionInsert;
1363
1364 -- INSERT data cust payment
1365 INSERT INTO m_cust_payment(
1366 tenant_id, partner_id, flg_invoice, due_payment_days,
1367 flg_payment_mode, payment_day, payment_date, partner_bank_id,
1368 create_datetime, create_user_id, update_datetime, update_user_id,
1369 version, active, active_datetime, non_active_datetime)
1370 SELECT vTenantId, A.partner_id, vNo, B.top_in_days::integer,
1371 vNo, vEmpty, vEmpty, vEmptyId,
1372 vDatetime, vUserId, vDatetime, vUserId,
1373 0, vYes, vDatetime, vSpaceValue
1374 FROM tt_ul_partner A
1375 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
1376 WHERE A.upload_header_id = pUploadHeaderId
1377 AND A.upload_header_id = B.upload_header_id
1378 AND A.action_type = vActionInsert;
1379
1380 END IF;
1381
1382 /**
1383 * Membuatkan data policy untuk OU yang belum ada pada data policy OU
1384 */
1385 WITH insert_data_policy AS (
1386 INSERT INTO t_policy(
1387 tenant_id, policy_code, policy_name, create_datetime,
1388 create_user_id, update_datetime, update_user_id, version, active,
1389 active_datetime, non_active_datetime)
1390 SELECT vTenantId, f_get_ou_code(vOuCompanyId)||'_'||vDatetime, f_get_ou_name(vOuCompanyId), vDatetime,
1391 vUserId, vDatetime, vUserId, 0, vYes,
1392 vDatetime, vSpaceValue
1393 WHERE NOT EXISTS (
1394 SELECT 1 FROM t_policy_ou Z
1395 WHERE Z.ou_id = vOuCompanyId
1396 )
1397
1398 RETURNING *
1399 )
1400 INSERT INTO t_policy_ou(
1401 policy_id, ou_id, create_datetime, create_user_id,
1402 update_datetime, update_user_id, version, active, active_datetime,
1403 non_active_datetime)
1404 SELECT A.policy_id, vOuCompanyId, vDatetime, vUserId,
1405 vDatetime, vUserId, 0, vYes, vDatetime,
1406 vSpaceValue
1407 FROM insert_data_policy A;
1408
1409 /**
1410 * Membuat / Update data user
1411 */
1412
1413 -- Update default role data user jika data user sudah ada
1414 UPDATE t_user A SET
1415 role_default_id=vDefaultRoleId,
1416 version=A.version+1,
1417 update_datetime=vDatetime,
1418 update_user_id=vUserId
1419 FROM tt_ul_partner B
1420 WHERE B.upload_header_id = pUploadHeaderId
1421 AND A.username = B.partner_code
1422 AND A.role_default_id <> vDefaultRoleId;
1423
1424 -- Buat data user role untuk user existing
1425 INSERT INTO t_user_role(
1426 user_id, role_id, policy_id, create_datetime, create_user_id,
1427 update_datetime, update_user_id, version, active, active_datetime,
1428 non_active_datetime)
1429 SELECT A.user_id, vDefaultRoleId, A.policy_default_id, vDatetime, vUserId,
1430 vDatetime, vUserId, 0, vYes, vDatetime,
1431 vSpaceValue
1432 FROM t_user A
1433 WHERE EXISTS (
1434 SELECT 1 FROM tt_ul_partner Z WHERE Z.upload_header_id = pUploadHeaderId AND A.username = Z.partner_code
1435 )
1436 AND NOT EXISTS (
1437 SELECT 1 FROM t_user_role Z
1438 WHERE Z.role_id = vDefaultRoleId
1439 AND A.user_id = Z.user_id
1440 );
1441
1442 -- Buat data user jika belum ada data user
1443 WITH insert_data_user AS (
1444 INSERT INTO t_user(
1445 tenant_id, username, email, fullname, password, phone,
1446 role_default_id, private_key, create_datetime, create_user_id,
1447 update_datetime, update_user_id, version, active, active_datetime,
1448 non_active_datetime, ou_default_id, policy_default_id)
1449 SELECT vTenantId, A.partner_code, vEmpty, CASE WHEN trim(A.partner_ext_name) <> vEmpty THEN A.partner_ext_name ELSE substr(A.partner_name,1,255) END, A.partner_code||'_'||vDatetime, vEmpty,
1450 vDefaultRoleId, md5(A.partner_code||vDatetime), vDatetime, vUserId,
1451 vDatetime, vUserId, 0, vYes, vDatetime,
1452 vSpaceValue, vOuCompanyId, f_get_policy_ou_for_upload(vOuCompanyId)
1453 FROM tt_ul_partner A
1454 WHERE A.upload_header_id = pUploadHeaderId
1455 AND NOT EXISTS (
1456 SELECT 1 FROM t_user Z WHERE Z.username = A.partner_code
1457 )
1458 RETURNING *
1459 ), insert_data_user_prop AS (
1460 -- Buat data user props
1461 INSERT INTO t_user_props(
1462 prop_key, user_id, prop_val, create_datetime, create_user_id,
1463 update_datetime, update_user_id, version)
1464 SELECT 'count.login', A.user_id, 0::character varying, vDatetime, vUserId,
1465 vDatetime, vUserId, 0
1466 FROM insert_data_user A
1467
1468 UNION ALL
1469
1470 SELECT 'last.login', A.user_id, ' ', vDatetime, vUserId,
1471 vDatetime, vUserId, 0
1472 FROM insert_data_user A
1473 )
1474 -- Buat data user role
1475 INSERT INTO t_user_role(
1476 user_id, role_id, policy_id, create_datetime, create_user_id,
1477 update_datetime, update_user_id, version, active, active_datetime,
1478 non_active_datetime)
1479 SELECT A.user_id, A.role_default_id, A.policy_default_id, vDatetime, vUserId,
1480 vDatetime, vUserId, 0, vYes, vDatetime,
1481 vSpaceValue
1482 FROM insert_data_user A;
1483
1484 -- RESET table temp
1485 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
1486
1487 END IF;
1488
1489 -- RESET TEMP TABLE
1490 DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
1491 DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
1492 DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
1493
1494 ELSE
1495 -- JIKA DEFAULT ROLE TIDAK DITEMUKAN, MAKA UPLOAD AKA DIGAGALKAN
1496 UPDATE ul_customer A
1497 SET status = vFail,
1498 update_status = vActionError,
1499 message = 'Default Role is not registered on system'
1500 WHERE upload_header_id = pUploadHeaderId;
1501
1502 -- Hitung jumlah data yang FAIL
1503 SELECT COUNT(1) INTO vCountFail
1504 FROM ul_customer
1505 WHERE upload_header_id = pUploadHeaderId
1506 AND status = vFail;
1507
1508 END IF;
1509
1510 RETURN vCountFail;
1511
1512END;
1513$BODY$
1514 LANGUAGE plpgsql VOLATILE
1515 COST 100;
1516 /