· 6 years ago · Jul 18, 2019, 06:58 AM
1CREATE OR REPLACE FUNCTION public.f_import_product_api(
2 bigint,
3 bigint,
4 character varying)
5 RETURNS void AS
6$BODY$
7DECLARE
8
9 pUploadHeaderId ALIAS FOR $1;
10 pUserId ALIAS FOR $2;
11 pDatetime ALIAS FOR $3;
12
13 vYes character varying;
14 vNo character varying;
15 vInProgress character varying;
16 vEmpty character varying;
17 vFail character varying;
18 vMessageProgress character varying;
19 vDoneWithFail character varying;
20 vDoneAllOk character varying;
21 vCountError bigint;
22 vSuccess character varying;
23 vTenantId bigint;
24 vOk character varying(2) := 'OK';
25 vActionInsert character varying(1) := 'I';
26 vActionUpdate character varying(1) := 'U';
27 vActionError character varying(1) := 'E';
28
29 vSpaceValue character varying(1) := ' ';
30
31BEGIN
32 vYes := 'Y';
33 vNo := 'N';
34 vInProgress := 'I';
35 vEmpty := '';
36 vFail := 'FAIL';
37 vSuccess := 'SUCCESS';
38 vMessageProgress := 'IN PROGRESS';
39 vDoneWithFail := 'DONE WITH FAILS';
40 vDoneAllOk := 'DONE ALL OK';
41
42 RAISE NOTICE 'Function f_migrate_product dimulai: ';
43 RAISE NOTICE 'Upload Header ID: %', pUploadHeaderId;
44 RAISE NOTICE 'Tenant ID: %', vTenantId ;
45
46 SELECT tenant_id INTO vTenantId FROM ul_header WHERE ul_header_id = pUploadHeaderId;
47
48 /**
49 * VALIDASI:
50 * # Validasi kolom - kolom wajib di isi
51 * 1. Validasi tenant_id harus terdaftar di table t_tenant (tenant_id)
52 * 2. Validasi kode supplier harus terdaftar disystem CASE SENSITIVE, dan pastikan tenant sesuai
53 * 3. Validasi kode group brand harus terdaftar di t_combo_value CASE SENSITIVE
54 * 4. Validasi max kode produk 50 character, dan wajib diisi
55 * 5. Validasi max nama produk 100 character, dan wajib diisi
56 * 6. Validasi kode kategori harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
57 * 7. Validasi kode sub kategori harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
58 * 8. Validasi kode sub kategori merupakan sub dari kode kategori yang diinput (alias kode kategori dari data sub kategori harus sama dengan kode kategori yang diinput)
59 * 9. Validasi kode brand harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
60 * 10. Validasi Base Unit harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
61 * 11. Validasi UOM 1, 2, 3 harus terdaftar di system, dan pastikan tenant sesuai (jika diisi)
62 * 12. Jika UOM 1 diisi, maka tidak boleh sama dengan Base Unit
63 * 13. Jika UOM 2 diisi, maka tidak boleh sama dengan Base Unit maupun UOM 1 (jika UOM 1 diisi)
64 * 14. Jika UOM 3 diisi, maka tidak boleh sama dengan Base Unit, UOM 1 (jika UOM 1 diisi), maupun UOM 2 (jika UOM 2 diisi)
65 * 15. Validasi Max Qty >= Min Qty
66 * 16. Validasi Min Qty, Max Qty, Weight, Dimension harus numeric dan lebih besar dari 0
67 * 17. Validasi sellable, buyable, active harus Y/N
68 * 18. Validasi Status Produksi harus terdaftar di t_combo_value CASE SENSITIVE, jika diisi
69 * 19. Validasi Barcode tidak boleh lebih dari 100 character jika diisi
70 * 20. Validasi Barcode harus belum ada pada system, jika diisi
71 * 21. Validasi Golongan Product harus terdaftar di t_combo_value CASE SENSITIVE
72 * 22. Validasi color, dan size tidak boleh lebih dari 255 character jika diisi
73 * 23. Validasi tidak boleh ada product code yang duplikat dalam 1 csv
74 * 24. Validasi jika Conv UOM 1 to base diisi, maka UOM 1 wajib diisi dan nilai Conv UOM 1 to base harus numeric dan lebih besar dari 0
75 * 25. Validasi jika Conv UOM 2 to base diisi, maka UOM 2 wajib diisi dan nilai Conv UOM 2 to base harus numeric dan lebih besar dari 0
76 * 26. Validasi jika Conv UOM 3 to base diisi, maka UOM 3 wajib diisi dan nilai Conv UOM 3 to base harus numeric dan lebih besar dari 0
77 * 27. Validasi tidak boleh ada barcode yang duplikat dalam 1 csv
78 * 28. Validasi product specs tidak boleh diisi jika sub category tidak support product specs
79 * 29. Validasi max keyword 100 character per 1 keyword (pemisah keyword adalah KOMA)
80 * 30. Jika diisi, Validasi vehicle brand year harus angka dan lebih besar dari 0
81 * 31. Jika diisi, Validasi vehicle engine capacity harus angka dan lebih besar dari 0
82 */
83
84 -- By default untuk semua flg_validate = Y terlebih dahulu
85 -- Kemudian validasi akan dijalankan, flg_validate berubah menjadi I dan
86 -- yang tidak lolos validasi flg_validate akan diupdate menjadi N
87
88 UPDATE ul_header
89 SET status = vMessageProgress
90 WHERE ul_header_id = pUploadHeaderId;
91
92 UPDATE ul_product
93 SET flg_validate = vInProgress, message = ''
94 WHERE upload_header_id = pUploadHeaderId;
95
96 -- # Validasi kolom - kolom wajib di isi
97 -- UPDATE ul_product A
98 -- SET status = vFail, flg_validate = vNo, message = message || 'Kode Supplier tidak boleh kosong, '
99 -- WHERE A.upload_header_id = pUploadHeaderId
100 -- AND TRIM(A.kode_supplier) = vEmpty;
101
102 UPDATE ul_product A
103 SET status = vFail, flg_validate = vNo, message = message || 'Kode Group Brand tidak boleh kosong, '
104 WHERE A.upload_header_id = pUploadHeaderId
105 AND TRIM(A.kode_group_brand) = vEmpty;
106
107 -- UPDATE ul_product A
108 -- SET status = vFail, flg_validate = vNo, message = message || 'Kode Kategori tidak boleh kosong, '
109 -- WHERE A.upload_header_id = pUploadHeaderId
110 -- AND TRIM(A.kode_kategori) = vEmpty;
111
112 UPDATE ul_product A
113 SET status = vFail, flg_validate = vNo, message = message || 'Sub Kode Kategori tidak boleh kosong, '
114 WHERE A.upload_header_id = pUploadHeaderId
115 AND TRIM(A.kode_sub_kategori) = vEmpty;
116
117 UPDATE ul_product A
118 SET status = vFail, flg_validate = vNo, message = message || 'Kode Brand tidak boleh kosong, '
119 WHERE A.upload_header_id = pUploadHeaderId
120 AND TRIM(A.kode_brand) = vEmpty;
121
122 UPDATE ul_product A
123 SET status = vFail, flg_validate = vNo, message = message || 'Base Uom Code tidak boleh kosong, '
124 WHERE A.upload_header_id = pUploadHeaderId
125 AND TRIM(A.base_unit) = vEmpty;
126
127 UPDATE ul_product A
128 SET status = vFail, flg_validate = vNo, message = message || 'Flg Sell tidak boleh kosong, '
129 WHERE A.upload_header_id = pUploadHeaderId
130 AND TRIM(A.sellable) = vEmpty;
131
132 UPDATE ul_product A
133 SET status = vFail, flg_validate = vNo, message = message || 'Flg Buy tidak boleh kosong, '
134 WHERE A.upload_header_id = pUploadHeaderId
135 AND TRIM(A.buyable) = vEmpty;
136
137 -- 1. Validasi tenant_id harus terdaftar di table t_tenant (tenant_id)
138 UPDATE ul_header A
139 SET status = vFail
140 WHERE A.ul_header_id = pUploadHeaderId
141 AND NOT EXISTS (
142 SELECT 1 FROM t_tenant B WHERE A.tenant_id = B.tenant_id
143 );
144
145 -- 2. Validasi kode supplier harus terdaftar disystem CASE SENSITIVE, dan pastikan tenant sesuai
146 -- UPDATE ul_product A
147 -- SET status = vFail, flg_validate = vNo, message = message || 'Supplier code tidak terdaftar di sistem, '
148 -- WHERE A.upload_header_id = pUploadHeaderId
149 -- AND A.kode_supplier <> vEmpty
150 -- AND NOT EXISTS (
151 -- SELECT 1
152 -- FROM m_partner B
153 -- INNER JOIN m_partner_type C ON B.partner_id = C.partner_id
154 -- WHERE A.kode_supplier = B.partner_code
155 -- AND B.tenant_id = vTenantId
156 -- AND C.group_partner = 'S'
157 -- );
158
159 -- 3. Validasi kode group brand harus terdaftar di t_combo_value CASE SENSITIVE
160 UPDATE ul_product A
161 SET status = vFail, flg_validate = vNo,
162 message = message || 'Group brand code tidak terdaftar di sistem, '
163 WHERE A.upload_header_id = pUploadHeaderId
164 AND A.kode_group_brand <> vEmpty
165 AND NOT EXISTS (
166 SELECT 1 FROM t_combo_value B
167 WHERE A.kode_group_brand = B.code
168 AND B.combo_id = 'GROUPBRANDPRODUCT'
169 );
170
171 -- 4. Validasi max kode produk 50 character, dan wajib diisi
172 UPDATE ul_product A
173 SET status = vFail, flg_validate = vNo,
174 message = message || 'Product code tidak boleh kosong, '
175 WHERE A.upload_header_id = pUploadHeaderId
176 AND TRIM(A.kode_produk) = vEmpty;
177
178 UPDATE ul_product A
179 SET status = vFail, flg_validate = vNo,
180 message = message || 'Product code maksimal 50 karakter, '
181 WHERE A.upload_header_id = pUploadHeaderId
182 AND length(A.kode_produk) > 50;
183
184 -- 5. Validasi max nama produk 100 character, dan wajib diisi
185 UPDATE ul_product A
186 SET status = vFail, flg_validate = vNo,
187 message = message || 'Product name tidak boleh kosong, '
188 WHERE A.upload_header_id = pUploadHeaderId
189 AND TRIM(A.nama_produk) = vEmpty;
190
191 UPDATE ul_product A
192 SET status = vFail, flg_validate = vNo,
193 message = message || 'Product name maksimal 100 karakter, '
194 WHERE A.upload_header_id = pUploadHeaderId
195 AND length(A.nama_produk) > 100;
196
197 -- 6. Validasi kode kategori harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
198 -- UPDATE ul_product A
199 -- SET status = vFail, flg_validate = vNo,
200 -- message = message || 'Kategori code tidak terdaftar di sistem, '
201 -- WHERE A.upload_header_id = pUploadHeaderId
202 -- AND A.kode_kategori <> vEmpty
203 -- AND NOT EXISTS (
204 -- SELECT 1 FROM m_ctgr_product B
205 -- WHERE A.kode_kategori = B.ctgr_product_code AND B.tenant_id = vTenantId
206 -- );
207
208 -- 7. Validasi kode sub kategori harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
209 UPDATE ul_product A
210 SET status = vFail, flg_validate = vNo,
211 message = message || 'Sub categori code tidak terdaftar di sistem, '
212 WHERE A.upload_header_id = pUploadHeaderId
213 AND A.kode_sub_kategori <> vEmpty
214 AND NOT EXISTS (
215 SELECT 1 FROM m_sub_ctgr_product B
216 WHERE A.kode_sub_kategori = B.sub_ctgr_product_code AND B.tenant_id = vTenantId
217 );
218
219 -- 8. Validasi kode sub kategori merupakan sub dari kode kategori yang diinput (alias kode kategori dari data sub kategori harus sama dengan kode kategori yang diinput)
220 -- UPDATE ul_product A
221 -- SET status = vFail, flg_validate = vNo,
222 -- message = message || 'Sub category code harusnya berkategori ('||kode_kategori||') , '
223 -- WHERE A.upload_header_id = pUploadHeaderId
224 -- AND EXISTS (
225 -- SELECT 1 FROM m_sub_ctgr_product B
226 -- INNER JOIN m_ctgr_product C ON B.ctgr_product_id = C.ctgr_product_id
227 -- WHERE A.kode_sub_kategori = B.sub_ctgr_product_code AND B.tenant_id = vTenantId
228 -- AND C.ctgr_product_code <> A.kode_kategori
229 -- );
230
231 -- 9. Validasi kode brand harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
232 -- UPDATE ul_product A
233 -- SET status = vFail, flg_validate = vNo,
234 -- message = message || 'Brand code tidak terdaftar di sistem, '
235 -- WHERE A.upload_header_id = pUploadHeaderId
236 -- AND A.kode_brand <> vEmpty
237 -- AND NOT EXISTS (
238 -- SELECT 1 FROM m_brand B
239 -- WHERE A.kode_brand = B.brand_code AND B.tenant_id = vTenantId
240 -- );
241
242 -- 10. Validasi Base Unit harus terdaftar di system CASE SENSITIVE, dan pastikan tenant sesuai
243 UPDATE ul_product A
244 SET status = vFail, flg_validate = vNo,
245 message = message || 'Base Unit tidak terdaftar di sistem, '
246 WHERE A.upload_header_id = pUploadHeaderId
247 AND A.base_unit <> vEmpty
248 AND NOT EXISTS (
249 SELECT 1 FROM m_uom B
250 WHERE A.base_unit = B.uom_code AND B.tenant_id = vTenantId
251 );
252
253 -- 11. Validasi UOM 1, 2, 3 harus terdaftar di system, dan pastikan tenant sesuai (jika diisi)
254 UPDATE ul_product A
255 SET status = vFail, flg_validate = vNo,
256 message = message || 'UOM 1 is tidak terdaftar di sistem, '
257 WHERE A.upload_header_id = pUploadHeaderId
258 AND TRIM(A.uom_1) <> vEmpty
259 AND NOT EXISTS (
260 SELECT 1 FROM m_uom B
261 WHERE A.uom_1 = B.uom_code AND B.tenant_id = vTenantId
262 );
263
264 UPDATE ul_product A
265 SET status = vFail, flg_validate = vNo,
266 message = message || 'UOM 2 tidak terdaftar di sistem, '
267 WHERE A.upload_header_id = pUploadHeaderId
268 AND TRIM(A.uom_2) <> vEmpty
269 AND NOT EXISTS (
270 SELECT 1 FROM m_uom B
271 WHERE A.uom_2 = B.uom_code AND B.tenant_id = vTenantId
272 );
273
274 UPDATE ul_product A
275 SET status = vFail, flg_validate = vNo,
276 message = message || 'UOM 3 tidak terdaftar di sistem, '
277 WHERE A.upload_header_id = pUploadHeaderId
278 AND TRIM(A.uom_3) <> vEmpty
279 AND NOT EXISTS (
280 SELECT 1 FROM m_uom B
281 WHERE A.uom_3 = B.uom_code AND B.tenant_id = vTenantId
282 );
283
284 -- 12. Jika UOM 1 diisi, maka tidak boleh sama dengan Base Unit
285 UPDATE ul_product A
286 SET status = vFail, flg_validate = vNo,
287 message = message || 'UOM 1 harus berbeda dengan Base Unit, '
288 WHERE A.upload_header_id = pUploadHeaderId
289 AND TRIM(A.uom_1) <> vEmpty
290 AND A.base_unit = A.uom_1;
291
292 -- 13. Jika UOM 2 diisi, maka tidak boleh sama dengan Base Unit maupun UOM 1 (jika UOM 1 diisi)
293 UPDATE ul_product A
294 SET status = vFail, flg_validate = vNo,
295 message = message || 'UOM 2 harus berbeda dengan Base Unit dan UOM 1, '
296 WHERE A.upload_header_id = pUploadHeaderId
297 AND TRIM(A.uom_2) <> vEmpty
298 AND (A.base_unit = A.uom_2 OR A.uom_1 = A.uom_2);
299
300 -- 14. Jika UOM 3 diisi, maka tidak boleh sama dengan Base Unit, UOM 1 (jika UOM 1 diisi), maupun UOM 2 (jika UOM 2 diisi)
301 UPDATE ul_product A
302 SET status = vFail, flg_validate = vNo,
303 message = message || 'UOM 3 harus berbeda dengan Base Unit dan UOM 1 dan UOM 2, '
304 WHERE A.upload_header_id = pUploadHeaderId
305 AND TRIM(A.uom_3) <> vEmpty
306 AND (A.base_unit = A.uom_3 OR A.uom_1 = A.uom_3 OR A.uom_2 = A.uom_3);
307
308 -- 15. Validasi Max Qty >= Min Qty
309 WITH data_product AS (
310 SELECT ul_product_id, max_qty, min_qty
311 FROM ul_product
312 WHERE upload_header_id = pUploadHeaderId
313 AND is_numeric(max_qty)
314 AND is_numeric(min_qty)
315 )
316 UPDATE ul_product A
317 SET status = vFail, flg_validate = vNo,
318 message = message || 'Max Qty harus lebih besar atau sama dengan Min Qty, '
319 FROM data_product B
320 WHERE A.ul_product_id = B.ul_product_id
321 AND B.max_qty::numeric < B.min_qty::numeric;
322
323 -- 16. Validasi Min Qty, Max Qty, Weight, Dimension harus numeric dan lebih besar dari 0
324 UPDATE ul_product A
325 SET status = vFail, flg_validate = vNo,
326 message = message || 'Max Qty harus berformat angka, '
327 WHERE A.upload_header_id = pUploadHeaderId
328 AND NOT is_numeric(max_qty)
329 AND max_qty <> vEmpty;
330
331 UPDATE ul_product A
332 SET status = vFail, flg_validate = vNo,
333 message = message || 'Max Qty tidak boleh minus, '
334 WHERE A.upload_header_id = pUploadHeaderId
335 AND max_qty::numeric < 0
336 AND max_qty <> vEmpty;
337
338 UPDATE ul_product A
339 SET status = vFail, flg_validate = vNo,
340 message = message || 'Min Qty harus berformat angka, '
341 WHERE A.upload_header_id = pUploadHeaderId
342 AND NOT is_numeric(min_qty)
343 AND min_qty <> vEmpty;
344
345 UPDATE ul_product A
346 SET status = vFail, flg_validate = vNo,
347 message = message || 'Min Qty tidak boleh minus, '
348 WHERE A.upload_header_id = pUploadHeaderId
349 AND min_qty::numeric < 0
350 AND min_qty <> vEmpty;
351
352 UPDATE ul_product A
353 SET status = vFail, flg_validate = vNo,
354 message = message || 'Weight dalam kg harus berformat angka, '
355 WHERE A.upload_header_id = pUploadHeaderId
356 AND NOT is_numeric(weight_in_kg)
357 AND weight_in_kg <> vEmpty;
358
359 UPDATE ul_product A
360 SET status = vFail, flg_validate = vNo,
361 message = message || 'Weight dalam kg tidak boleh minus, '
362 WHERE A.upload_header_id = pUploadHeaderId
363 AND weight_in_kg::numeric < 0
364 AND weight_in_kg <> vEmpty;
365
366 UPDATE ul_product A
367 SET status = vFail, flg_validate = vNo,
368 message = message || 'Length dalam cm harus berformat angka, '
369 WHERE A.upload_header_id = pUploadHeaderId
370 AND NOT is_numeric(length_in_cm)
371 AND length_in_cm <> vEmpty;
372
373 UPDATE ul_product A
374 SET status = vFail, flg_validate = vNo,
375 message = message || 'Length dalam cm tidak boleh minus, '
376 WHERE A.upload_header_id = pUploadHeaderId
377 AND length_in_cm::numeric < 0
378 AND length_in_cm <> vEmpty;
379
380 UPDATE ul_product A
381 SET status = vFail, flg_validate = vNo,
382 message = message || 'Width dalam cm harus berformat angka, '
383 WHERE A.upload_header_id = pUploadHeaderId
384 AND NOT is_numeric(width_in_cm)
385 AND width_in_cm <> vEmpty;
386
387 UPDATE ul_product A
388 SET status = vFail, flg_validate = vNo,
389 message = message || 'Width dalam cm tidak boleh minus, '
390 WHERE A.upload_header_id = pUploadHeaderId
391 AND width_in_cm::numeric < 0
392 AND width_in_cm <> vEmpty;
393
394 UPDATE ul_product A
395 SET status = vFail, flg_validate = vNo,
396 message = message || 'Heigth dalam cm harus berformat angka, '
397 WHERE A.upload_header_id = pUploadHeaderId
398 AND NOT is_numeric(height_in_cm)
399 AND height_in_cm <> vEmpty;
400
401 UPDATE ul_product A
402 SET status = vFail, flg_validate = vNo,
403 message = message || 'Heigth dalam cm tidak boleh minus, '
404 WHERE A.upload_header_id = pUploadHeaderId
405 AND height_in_cm::numeric < 0
406 AND height_in_cm <> vEmpty;
407
408 -- 17. Validasi sellable, buyable, active harus Y/N
409 UPDATE ul_product A
410 SET status = vFail, flg_validate = vNo,
411 message = message || 'Sellable harus berformat Y atau N, '
412 WHERE A.upload_header_id = pUploadHeaderId
413 AND A.sellable NOT IN (vYes, vNo);
414
415 UPDATE ul_product A
416 SET status = vFail, flg_validate = vNo,
417 message = message || 'Buyable harus berformat Y atau N, '
418 WHERE A.upload_header_id = pUploadHeaderId
419 AND A.buyable NOT IN (vYes, vNo);
420
421 UPDATE ul_product A
422 SET status = vFail, flg_validate = vNo,
423 message = message || 'Active harus berformat Y atau N, '
424 WHERE A.upload_header_id = pUploadHeaderId
425 AND A.active NOT IN (vYes, vNo);
426
427 -- 18. Validasi Status Produksi harus terdaftar di t_combo_value CASE SENSITIVE, jika diisi
428 UPDATE ul_product A
429 SET status = vFail, flg_validate = vNo,
430 message = message || 'Status produksi tidak terdaftar di sistem, '
431 WHERE A.upload_header_id = pUploadHeaderId
432 AND TRIM(A.status_produksi) <> vEmpty
433 AND NOT EXISTS (
434 SELECT 1 FROM t_combo_value B
435 WHERE A.status_produksi = B.code
436 AND B.combo_id = 'CLASSPRODUCT'
437 );
438
439 -- 19. Validasi Barcode tidak boleh lebih dari 100 character jika diisi
440 UPDATE ul_product A
441 SET status = vFail, flg_validate = vNo,
442 message = message || 'Max barcode adalah 100 karakter, '
443 WHERE A.upload_header_id = pUploadHeaderId
444 AND length(A.barcode) > 100;
445
446 -- 20. Validasi Barcode harus belum ada pada system, jika diisi
447 WITH unique_data_product AS (
448 SELECT A.kode_produk
449 FROM ul_product A
450 WHERE A.upload_header_id = pUploadHeaderId
451 AND TRIM(A.kode_produk) <> vEmpty
452 GROUP BY A.kode_produk
453 HAVING COUNT(1) = 1
454 ), uniqeu_data_barcode AS (
455 SELECT A.kode_produk, A.barcode
456 FROM ul_product A
457 WHERE A.upload_header_id = pUploadHeaderId
458 AND TRIM(A.barcode) <> vEmpty
459 AND EXISTS (
460 SELECT 1 FROM unique_data_product Z WHERE A.kode_produk = Z.kode_produk
461 )
462 GROUP BY A.kode_produk, A.barcode
463 HAVING COUNT(1) = 1
464 ), existing_data_barcode AS (
465 SELECT f_get_product_code(A.product_id) AS kode_produk, A.barcode
466 FROM m_product_barcode A
467 WHERE NOT EXISTS (
468 SELECT 1 FROM unique_data_product Z WHERE f_get_product_code(A.product_id) = Z.kode_produk
469 )
470 ), all_data_barcode AS (
471 SELECT A.barcode
472 FROM uniqeu_data_barcode A
473
474 UNION ALL
475
476 SELECT B.barcode
477 FROM existing_data_barcode B
478 ), get_duplicate_data_barcode AS (
479 SELECT A.barcode
480 FROM all_data_barcode A
481 GROUP BY A.barcode
482 HAVING COUNT(1) > 1
483 )
484 UPDATE ul_product A
485 SET status = vFail, flg_validate = vNo,
486 message = message || 'Barcode sudah terdaftar di sistem, '
487 WHERE A.upload_header_id = pUploadHeaderId
488 AND EXISTS (
489 SELECT 1 FROM get_duplicate_data_barcode Z
490 WHERE Z.barcode = A.barcode
491 );
492
493 -- 21. Validasi Golongan Product harus terdaftar di t_combo_value CASE SENSITIVE
494 UPDATE ul_product A
495 SET status = vFail, flg_validate = vNo,
496 message = message || 'Golongan Product tidak terdaftar di sistem, '
497 WHERE A.upload_header_id = pUploadHeaderId
498 AND TRIM(A.golongan_product) <> vEmpty
499 AND NOT EXISTS (
500 SELECT 1 FROM t_combo_value B
501 WHERE A.golongan_product = B.code
502 AND B.combo_id = 'STYLEPRODUCT'
503 );
504
505 -- 22. Validasi color, dan size tidak boleh lebih dari 255 character jika diisi
506 UPDATE ul_product A
507 SET status = vFail, flg_validate = vNo,
508 message = message || 'Max color adalah 255 karakter, '
509 WHERE A.upload_header_id = pUploadHeaderId
510 AND length(A.color) > 255;
511
512 UPDATE ul_product A
513 SET status = vFail, flg_validate = vNo,
514 message = message || 'Max size adalah 255 karakter, '
515 WHERE A.upload_header_id = pUploadHeaderId
516 AND length(A.size) > 255;
517
518 -- 23. Validasi tidak boleh ada product code yang duplikat dalam 1 csv
519 WITH duplicate_data_product AS (
520 SELECT A.kode_produk
521 FROM ul_product A
522 WHERE A.upload_header_id = pUploadHeaderId
523 AND TRIM(A.kode_produk) <> vEmpty
524 GROUP BY A.kode_produk
525 HAVING COUNT(1) > 1
526 )
527 UPDATE ul_product A
528 SET status = vFail, flg_validate = vNo,
529 message = message || 'Kode Produk tidak boleh duplikat, '
530 WHERE A.upload_header_id = pUploadHeaderId
531 AND EXISTS (
532 SELECT 1 FROM duplicate_data_product B WHERE A.kode_produk = B.kode_produk
533 );
534
535 -- 24. Validasi jika Conv UOM 1 to base diisi, maka UOM 1 wajib diisi dan nilai Conv UOM 1 to base harus numeric dan lebih besar dari 0
536 UPDATE ul_product A
537 SET status = vFail, flg_validate = vNo,
538 message = message || 'Conv UOM 1 to base harus berformat angka, '
539 WHERE A.upload_header_id = pUploadHeaderId
540 AND TRIM(A.conv_uom_1_to_base) <> vEmpty
541 AND NOT is_numeric(A.conv_uom_1_to_base);
542
543 UPDATE ul_product A
544 SET status = vFail, flg_validate = vNo,
545 message = message || 'Conv UOM 1 to base tidak boleh minus, '
546 WHERE A.upload_header_id = pUploadHeaderId
547 AND TRIM(A.conv_uom_1_to_base) <> vEmpty
548 AND conv_uom_1_to_base::numeric < 0;
549
550 WITH data_product AS (
551 SELECT ul_product_id, conv_uom_1_to_base
552 FROM ul_product
553 WHERE upload_header_id = pUploadHeaderId
554 AND TRIM(uom_1) = vEmpty
555 AND TRIM(conv_uom_1_to_base) <> vEmpty
556 AND is_numeric(conv_uom_1_to_base)
557 )
558 UPDATE ul_product A
559 SET status = vFail, flg_validate = vNo,
560 message = message || 'UOM 1 harus di isi karen Conv UOM 1 to base lebih besar dari 0, '
561 FROM data_product B
562 WHERE A.ul_product_id = B.ul_product_id
563 AND B.conv_uom_1_to_base::numeric > 0;
564
565 -- 25. Validasi jika Conv UOM 2 to base diisi, maka UOM 2 wajib diisi dan nilai Conv UOM 2 to base harus numeric dan lebih besar dari 0
566 UPDATE ul_product A
567 SET status = vFail, flg_validate = vNo,
568 message = message || 'Conv UOM 2 to base harus berformat angka, '
569 WHERE A.upload_header_id = pUploadHeaderId
570 AND TRIM(A.conv_uom_2_to_base) <> vEmpty
571 AND NOT is_numeric(A.conv_uom_1_to_base);
572
573 UPDATE ul_product A
574 SET status = vFail, flg_validate = vNo,
575 message = message || 'Conv UOM 2 to base tidak boleh minus, '
576 WHERE A.upload_header_id = pUploadHeaderId
577 AND TRIM(A.conv_uom_2_to_base) <> vEmpty
578 AND conv_uom_2_to_base::numeric < 0;
579
580 WITH data_product AS (
581 SELECT ul_product_id, conv_uom_2_to_base
582 FROM ul_product
583 WHERE upload_header_id = pUploadHeaderId
584 AND TRIM(uom_2) = vEmpty
585 AND TRIM(conv_uom_2_to_base) <> vEmpty
586 AND is_numeric(conv_uom_2_to_base)
587 )
588 UPDATE ul_product A
589 SET status = vFail, flg_validate = vNo,
590 message = message || 'UOM 2 harus di isi karena Conv UOM 2 to base lebih besar dari 0, '
591 FROM data_product B
592 WHERE A.ul_product_id = B.ul_product_id
593 AND B.conv_uom_2_to_base::numeric > 0;
594
595 -- 26. Validasi jika Conv UOM 3 to base diisi, maka UOM 3 wajib diisi dan nilai Conv UOM 3 to base harus numeric dan lebih besar dari 0
596 UPDATE ul_product A
597 SET status = vFail, flg_validate = vNo,
598 message = message || 'Conv UOM 3 to base harus berformat angka, '
599 WHERE A.upload_header_id = pUploadHeaderId
600 AND TRIM(A.conv_uom_3_to_base) <> vEmpty
601 AND NOT is_numeric(A.conv_uom_3_to_base);
602
603 UPDATE ul_product A
604 SET status = vFail, flg_validate = vNo,
605 message = message || 'Conv UOM 3 to base tidak boleh minus, '
606 WHERE A.upload_header_id = pUploadHeaderId
607 AND TRIM(A.conv_uom_3_to_base) <> vEmpty
608 AND conv_uom_3_to_base::numeric < 0;
609
610 WITH data_product AS (
611 SELECT ul_product_id, conv_uom_3_to_base
612 FROM ul_product
613 WHERE upload_header_id = pUploadHeaderId
614 AND TRIM(uom_3) = vEmpty
615 AND TRIM(conv_uom_3_to_base) <> vEmpty
616 AND is_numeric(conv_uom_3_to_base)
617 )
618 UPDATE ul_product A
619 SET status = vFail, flg_validate = vNo,
620 message = message || 'UOM 3 harus di isi karena Conv UOM 3 to base lebih besar dari 0, '
621 FROM data_product B
622 WHERE A.ul_product_id = B.ul_product_id
623 AND B.conv_uom_3_to_base::numeric > 0;
624
625 -- 27. Validasi tidak boleh ada barcode yang duplikat dalam 1 csv
626 WITH duplicate_data_barcode AS (
627 SELECT A.barcode
628 FROM ul_product A
629 WHERE A.upload_header_id = pUploadHeaderId
630 AND TRIM(A.barcode) <> vEmpty
631 GROUP BY A.barcode
632 HAVING COUNT(1) > 1
633 )
634 UPDATE ul_product A
635 SET status = vFail, flg_validate = vNo,
636 message = message || 'Barcode tidak boleh duplikat, '
637 WHERE A.upload_header_id = pUploadHeaderId
638 AND EXISTS (
639 SELECT 1 FROM duplicate_data_barcode B WHERE A.barcode = B.barcode
640 );
641
642 -- 28. Validasi product specs tidak boleh diisi jika sub category tidak support product specs
643 UPDATE ul_product A
644 SET status = vFail, flg_validate = vNo,
645 message = message || 'Specs harus kosong karena sub category tidak support produk specs, '
646 WHERE A.upload_header_id = pUploadHeaderId
647 AND TRIM(A.specs) != vEmpty
648 AND NOT EXISTS (
649 SELECT 1 FROM m_sub_ctgr_product B
650 WHERE A.kode_sub_kategori = B.sub_ctgr_product_code
651 AND B.flg_spec = vYes
652 );
653
654 -- 29. Validasi max keyword 100 character per 1 keyword (pemisah keyword adalah KOMA)
655 WITH detail_keyword AS (
656 SELECT A.ul_product_id, LTRIM(unnest(string_to_array(A.keywords, ','))) AS keywords
657 FROM ul_product A
658 WHERE A.upload_header_id = pUploadHeaderId
659 AND TRIM(A.keywords)<>vEmpty
660 )
661 UPDATE ul_product A
662 SET status = vFail, flg_validate = vNo,
663 message = message || 'Masing - masing Keyword maksimal 100 Karakter, '
664 WHERE A.upload_header_id = pUploadHeaderId
665 AND TRIM(A.keywords) != vEmpty
666 AND EXISTS (
667 SELECT 1
668 FROM detail_keyword Z
669 WHERE A.ul_product_id=Z.ul_product_id
670 AND length(Z.keywords) > 100
671 );
672
673 -- 30. Jika diisi, Validasi vehicle brand year harus angka dan lebih besar dari 0
674 UPDATE ul_product A
675 SET status = vFail,
676 message = message || 'Vehicle Year must be numeric, '
677 WHERE A.upload_header_id = pUploadHeaderId
678 AND TRIM(A.vehicle_year) != vEmpty
679 AND NOT is_numeric(A.vehicle_year);
680
681 UPDATE ul_product A
682 SET status = vFail,
683 message = message || 'Vehicle Year tidak boleh minus, '
684 WHERE A.upload_header_id = pUploadHeaderId
685 AND TRIM(A.vehicle_year) != vEmpty
686 AND A.vehicle_year::numeric < 0;
687
688 -- 31. Jika diisi, Validasi vehicle engine capacity harus angka dan lebih besar dari 0
689 UPDATE ul_product A
690 SET status = vFail,
691 message = message || 'Vehicle engine capacity must be numeric, '
692 WHERE A.upload_header_id = pUploadHeaderId
693 AND TRIM(A.vehicle_engine_capacity) != vEmpty
694 AND NOT is_numeric(A.vehicle_engine_capacity);
695
696 UPDATE ul_product A
697 SET status = vFail,
698 message = message || 'Vehicle engine tidak boleh minus, '
699 WHERE A.upload_header_id = pUploadHeaderId
700 AND TRIM(A.vehicle_engine_capacity) != vEmpty
701 AND A.vehicle_engine_capacity::numeric < 0;
702
703 -- Untuk semua flg_validate yang masih I, artinya LOLOS SEMUA VALIDASI, maka diubah menjadi Y
704 UPDATE ul_product
705 SET flg_validate = vYes, status = vSuccess
706 WHERE upload_header_id = pUploadHeaderId
707 AND flg_validate = vInProgress;
708
709 -- Migrasi data ke m_brand jika kode_brand tidak terdaftar di sistem
710 INSERT INTO m_brand(
711 tenant_id, brand_code, brand_name, create_datetime,
712 create_user_id, update_datetime, update_user_id, version, active,
713 active_datetime, non_active_datetime)
714 SELECT
715 vTenantId, TRIM(UPPER(A.kode_brand)), TRIM(UPPER(A.kode_brand)), pDatetime,
716 pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vSpaceValue
717 FROM ul_product A
718 WHERE upload_header_id = pUploadHeaderId
719 AND NOT EXISTS(
720 SELECT 1 FROM m_brand B
721 WHERE A.kode_brand = B.brand_code AND B.tenant_id = vTenantId
722 )
723 GROUP BY TRIM(UPPER(A.kode_brand));
724
725 SELECT COUNT(*) INTO vCountError FROM ul_product
726 WHERE upload_header_id = pUploadHeaderId AND flg_validate = vNo;
727
728 IF(vCountError > 0) THEN
729 UPDATE ul_header
730 SET status = vDoneWithFail
731 WHERE ul_header_id = pUploadHeaderId;
732 ELSE
733 UPDATE ul_header
734 SET status = vDoneAllOk
735 WHERE ul_header_id = pUploadHeaderId;
736 END IF;
737
738 /**
739 * Step - step:
740 * 1. Migrasi ke data m_product
741 * 2. Migrasi ke data m_product_specs
742 * 3. Migrasi ke data m_product_barcode
743 * 4. Migrasi ke data m_product_weight_dimension
744 * 5. Migrasi ke data m_product_keyword
745 * 6. Migrasi ke data m_product_custom
746 * 7. Migrasi ke data m_product_uom_cnv
747 * 8. Migrasi ke data m_product_group_brand
748 * 9. Migrasi ke data m_product_custom_for_dlg
749 * 10. Migrasi ke data m_product_consignment_supp_info
750 * 11. Migrasi ke data m_product_sparepart
751 * 12. Migrasi ke data m_product_keyword untuk product alias
752 */
753
754 -- Update to default value, untuk kolom2 yang tidak diisi (kolom optional)
755 UPDATE ul_product A
756 SET max_qty = '0'
757 WHERE A.upload_header_id = pUploadHeaderId
758 AND TRIM(A.max_qty) = vEmpty;
759
760 UPDATE ul_product A
761 SET min_qty = '0'
762 WHERE A.upload_header_id = pUploadHeaderId
763 AND TRIM(A.min_qty) = vEmpty;
764
765 UPDATE ul_product A
766 SET weight_in_kg = '0'
767 WHERE A.upload_header_id = pUploadHeaderId
768 AND TRIM(A.weight_in_kg) = vEmpty;
769
770 UPDATE ul_product A
771 SET length_in_cm = '0'
772 WHERE A.upload_header_id = pUploadHeaderId
773 AND TRIM(A.length_in_cm) = vEmpty;
774
775 UPDATE ul_product A
776 SET width_in_cm = '0'
777 WHERE A.upload_header_id = pUploadHeaderId
778 AND TRIM(A.width_in_cm) = vEmpty;
779
780 UPDATE ul_product A
781 SET height_in_cm = '0'
782 WHERE A.upload_header_id = pUploadHeaderId
783 AND TRIM(A.height_in_cm) = vEmpty;
784
785 UPDATE ul_product A
786 SET vehicle_year = '0'
787 WHERE A.upload_header_id = pUploadHeaderId
788 AND TRIM(A.vehicle_year) = vEmpty;
789
790 UPDATE ul_product A
791 SET vehicle_engine_capacity = '0'
792 WHERE A.upload_header_id = pUploadHeaderId
793 AND TRIM(A.vehicle_engine_capacity) = vEmpty;
794
795 -- RESET table temp
796 DELETE FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId;
797
798 -- Update status non FAIL to OK
799 UPDATE ul_product
800 SET status = vOk
801 WHERE upload_header_id = pUploadHeaderId
802 AND flg_validate = vYes;
803
804 -- UPDATE OK data untuk product code terdaftar
805 WITH update_data_product AS (
806 UPDATE m_product A SET
807 product_name=B.nama_produk,
808 ctgr_product_id=C.ctgr_product_id,
809 sub_ctgr_product_id=C.sub_ctgr_product_id,
810 brand_id=D.brand_id,
811 base_uom_id=E.uom_id,
812 uom_id_1=COALESCE(F.uom_id, -99),
813 uom_id_2=COALESCE(G.uom_id, -99),
814 uom_id_3=COALESCE(H.uom_id, -99),
815 flg_buy=B.buyable,
816 flg_sell=B.sellable,
817 min_qty=B.min_qty::numeric,
818 max_qty=B.max_qty::numeric,
819 class_product=CASE WHEN TRIM(B.status_produksi) = vEmpty THEN 'RUTIN PRODUKSI' ELSE B.status_produksi END,
820 update_datetime=pDatetime,
821 update_user_id=pUserId,
822 version=A.version+1,
823 active=B.active,
824 active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
825 THEN pDatetime
826 ELSE A.active_datetime
827 END,
828 non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
829 THEN pDatetime
830 ELSE A.non_active_datetime
831 END
832 FROM ul_product B
833 INNER JOIN m_sub_ctgr_product C ON B.kode_sub_kategori = C.sub_ctgr_product_code AND C.tenant_id = vTenantId
834 INNER JOIN m_brand D ON B.kode_brand = D.brand_code AND D.tenant_id = vTenantId
835 INNER JOIN m_uom E ON B.base_unit = E.uom_code AND E.tenant_id = vTenantId
836 LEFT JOIN m_uom F ON B.uom_1 = F.uom_code AND F.tenant_id = vTenantId
837 LEFT JOIN m_uom G ON B.uom_2 = G.uom_code AND G.tenant_id = vTenantId
838 LEFT JOIN m_uom H ON B.uom_3 = H.uom_code AND H.tenant_id = vTenantId
839 WHERE B.upload_header_id = pUploadHeaderId
840 AND B.status = vOk
841 AND A.product_code = B.kode_produk
842 AND A.tenant_id = vTenantId
843 RETURNING A.*
844 )
845 INSERT INTO tt_ul_product(
846 upload_header_id, action_type, product_id, tenant_id, product_code,
847 product_name, ctgr_product_id, sub_ctgr_product_id, brand_id,
848 base_uom_id, uom_id_1, uom_id_2, uom_id_3, flg_buy, flg_sell,
849 min_qty, max_qty, create_datetime, create_user_id, update_datetime,
850 update_user_id, version, active, active_datetime, non_active_datetime,
851 class_product)
852 SELECT pUploadHeaderId, vActionUpdate, product_id, tenant_id, product_code,
853 product_name, ctgr_product_id, sub_ctgr_product_id, brand_id,
854 base_uom_id, uom_id_1, uom_id_2, uom_id_3, flg_buy, flg_sell,
855 min_qty, max_qty, create_datetime, create_user_id, update_datetime,
856 update_user_id, version, active, active_datetime, non_active_datetime,
857 class_product
858 FROM update_data_product;
859
860 -- INSERT OK data untuk product code baru
861 WITH insert_data_product AS (
862 INSERT INTO m_product(
863 tenant_id, product_code, product_name, ctgr_product_id,
864 sub_ctgr_product_id, brand_id, base_uom_id,
865 uom_id_1, uom_id_2, uom_id_3,
866 flg_buy, flg_sell, min_qty, max_qty, create_datetime,
867 create_user_id, update_datetime, update_user_id, version, active,
868 active_datetime, non_active_datetime, class_product)
869 SELECT vTenantId, A.kode_produk, A.nama_produk, B.ctgr_product_id,
870 B.sub_ctgr_product_id, C.brand_id, D.uom_id,
871 COALESCE(E.uom_id, -99), COALESCE(F.uom_id, -99), COALESCE(G.uom_id, -99),
872 A.buyable, A.sellable, A.min_qty::numeric, A.max_qty::numeric, pDatetime,
873 pUserId, pDatetime, pUserId, 0, A.active,
874 CASE WHEN A.active = vYes
875 THEN pDatetime
876 ELSE vSpaceValue
877 END AS active_datetime,
878 CASE WHEN A.active = vNo
879 THEN pDatetime
880 ELSE vSpaceValue
881 END AS non_active_datetime,
882 CASE WHEN TRIM(A.status_produksi) = vEmpty THEN 'RUTIN PRODUKSI' ELSE A.status_produksi END
883 FROM ul_product A
884 INNER JOIN m_sub_ctgr_product B ON A.kode_sub_kategori = B.sub_ctgr_product_code AND B.tenant_id = vTenantId
885 INNER JOIN m_brand C ON A.kode_brand = C.brand_code AND C.tenant_id = vTenantId
886 INNER JOIN m_uom D ON A.base_unit = D.uom_code AND D.tenant_id = vTenantId
887 LEFT JOIN m_uom E ON A.uom_1 = E.uom_code AND E.tenant_id = vTenantId
888 LEFT JOIN m_uom F ON A.uom_2 = F.uom_code AND F.tenant_id = vTenantId
889 LEFT JOIN m_uom G ON A.uom_3 = G.uom_code AND G.tenant_id = vTenantId
890 WHERE A.upload_header_id = pUploadHeaderId
891 AND A.status = vOk
892 AND A.flg_validate = vYes
893 AND NOT EXISTS (
894 SELECT 1 FROM m_product Z
895 WHERE A.kode_produk = Z.product_code AND Z.tenant_id = vTenantId
896 )
897 RETURNING *
898 )
899 INSERT INTO tt_ul_product(
900 upload_header_id, action_type, product_id, tenant_id, product_code,
901 product_name, ctgr_product_id, sub_ctgr_product_id, brand_id,
902 base_uom_id, uom_id_1, uom_id_2, uom_id_3, flg_buy, flg_sell,
903 min_qty, max_qty, create_datetime, create_user_id, update_datetime,
904 update_user_id, version, active, active_datetime, non_active_datetime,
905 class_product)
906 SELECT pUploadHeaderId, vActionInsert, product_id, tenant_id, product_code,
907 product_name, ctgr_product_id, sub_ctgr_product_id, brand_id,
908 base_uom_id, uom_id_1, uom_id_2, uom_id_3, flg_buy, flg_sell,
909 min_qty, max_qty, create_datetime, create_user_id, update_datetime,
910 update_user_id, version, active, active_datetime, non_active_datetime,
911 class_product
912 FROM insert_data_product;
913
914 -- ACTION FOR UPDATE
915 IF EXISTS (SELECT 1 FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
916
917 -- Ubah update_status menjadi U jika data digunakan untuk Update
918 UPDATE ul_product A SET
919 update_status = vActionUpdate
920 WHERE A.upload_header_id = pUploadHeaderId
921 AND EXISTS (
922 SELECT 1 FROM tt_ul_product B
923 WHERE A.kode_produk = B.product_code
924 AND B.tenant_id = vTenantId
925 AND B.action_type = vActionUpdate
926 );
927
928 -- REMOVE existing data specs untuk product yang di upload
929 DELETE FROM m_product_specs A
930 WHERE EXISTS (
931 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
932 AND Z.upload_header_id = pUploadHeaderId
933 );
934
935 -- INSERT specs untuk semua product dari upload yang specsnya tidak kosong
936 INSERT INTO m_product_specs(
937 product_id, tenant_id, product_specs, create_datetime, create_user_id,
938 update_datetime, update_user_id, version, active, active_datetime,
939 non_active_datetime)
940 SELECT A.product_id, A.tenant_id, B.specs, pDatetime, pUserId,
941 pDatetime, pUserId, 0, vYes, pDatetime, vSpaceValue
942 FROM tt_ul_product A
943 INNER JOIN ul_product B ON A.product_code = B.kode_produk
944 WHERE A.upload_header_id = pUploadHeaderId
945 AND A.upload_header_id=B.upload_header_id
946 AND A.action_type = vActionUpdate
947 AND TRIM(B.specs)<>vEmpty;
948
949 -- UPDATE product custom
950 UPDATE m_product_custom A SET
951 style_product=C.golongan_product,
952 color=C.color,
953 size=C.size,
954 update_datetime=pDatetime,
955 update_user_id=pUserId,
956 version=A.version+1
957 FROM tt_ul_product B
958 INNER JOIN ul_product C ON B.product_code = C.kode_produk AND B.tenant_id = vTenantId
959 WHERE B.upload_header_id = pUploadHeaderId
960 AND B.upload_header_id=C.upload_header_id
961 AND A.product_id = B.product_id
962 AND B.action_type = vActionUpdate;
963
964 -- UPDATE product custom for dlg
965 UPDATE m_product_custom_for_dlg A SET
966 supplier_id=D.partner_id,
967 update_datetime=pDatetime,
968 update_user_id=pUserId,
969 version=A.version+1
970 FROM tt_ul_product B
971 INNER JOIN ul_product C ON B.product_code = C.kode_produk
972 INNER JOIN m_partner D ON C.kode_supplier = D.partner_code AND D.tenant_id = B.tenant_id
973 INNER JOIN m_partner_type E ON D.partner_id = E.partner_id
974 WHERE B.upload_header_id = pUploadHeaderId
975 AND B.upload_header_id=C.upload_header_id
976 AND TRIM(C.kode_supplier)<>vEmpty
977 AND A.product_id = B.product_id
978 AND B.action_type = vActionUpdate
979 AND E.group_partner = 'S';
980
981 UPDATE m_product_custom_for_dlg A SET
982 supplier_id=-99,
983 update_datetime=pDatetime,
984 update_user_id=pUserId,
985 version=A.version+1
986 FROM tt_ul_product B
987 INNER JOIN ul_product C ON B.product_code = C.kode_produk
988 WHERE B.upload_header_id = pUploadHeaderId
989 AND B.upload_header_id=C.upload_header_id
990 AND TRIM(C.kode_supplier)=vEmpty
991 AND A.product_id = B.product_id
992 AND B.action_type = vActionUpdate;
993
994 -- UPDATE product weight dimension
995 UPDATE m_product_weight_dimension A SET
996 weight=C.weight_in_kg::numeric,
997 dimension_length=C.length_in_cm::numeric,
998 dimension_width=C.width_in_cm::numeric,
999 dimension_height=C.height_in_cm::numeric,
1000 update_datetime=pDatetime,
1001 update_user_id=pUserId,
1002 version=A.version+1
1003 FROM tt_ul_product B
1004 INNER JOIN ul_product C ON B.product_code = C.kode_produk
1005 WHERE B.upload_header_id = pUploadHeaderId
1006 AND B.upload_header_id=C.upload_header_id
1007 AND A.product_id = B.product_id
1008 AND B.action_type = vActionUpdate;
1009
1010 -- REMOVE EXISTING PRODUCT KEYWORD BY PRODUCT YANG DI UPDATE
1011 DELETE FROM m_product_keyword A
1012 WHERE EXISTS (
1013 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
1014 );
1015
1016 -- INSERT PRODUCT KEYWORD BARU
1017 INSERT INTO m_product_keyword(
1018 product_id, tenant_id, keyword, create_datetime,
1019 create_user_id, update_datetime, update_user_id, version, active,
1020 active_datetime, non_active_datetime)
1021 SELECT A.product_id, A.tenant_id, LTRIM(unnest(string_to_array(B.keywords, ','))) AS keywords, pDatetime,
1022 pUserId, pDatetime, pUserId, 0, vYes,
1023 pDatetime, vSpaceValue
1024 FROM tt_ul_product A
1025 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1026 WHERE A.upload_header_id = pUploadHeaderId
1027 AND A.upload_header_id=B.upload_header_id
1028 AND A.action_type = vActionUpdate
1029 AND TRIM(B.keywords)<>vEmpty;
1030
1031 -- REMOVE DATA BARCODE untuk semua product yang diupload
1032 DELETE FROM m_product_barcode A
1033 WHERE EXISTS (
1034 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
1035 );
1036
1037 -- INSERT DATA BARCODE untuk semua product yang diupload
1038 INSERT INTO m_product_barcode(
1039 product_id, tenant_id, barcode, create_datetime, create_user_id,
1040 update_datetime, update_user_id, version, active, active_datetime,
1041 non_active_datetime)
1042 SELECT A.product_id, A.tenant_id, B.barcode, pDatetime, pUserId,
1043 pDatetime, pUserId, 0, vYes, pDatetime,
1044 vSpaceValue
1045 FROM tt_ul_product A
1046 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1047 WHERE A.upload_header_id = pUploadHeaderId
1048 AND A.upload_header_id=B.upload_header_id
1049 AND A.action_type = vActionUpdate
1050 AND TRIM(B.barcode)<>vEmpty;
1051
1052 -- REMOVE EXISTING PRODUCT UOM CNV BY PRODUCT YANG DI UPDATE
1053 DELETE FROM m_product_uom_cnv A
1054 WHERE EXISTS (
1055 SELECT 1 FROM tt_ul_product Z WHERE A.product_id = Z.product_id AND Z.action_type = vActionUpdate
1056 );
1057
1058 -- INSERT PRODUCT UOM CNV ATAS UOM 1
1059 INSERT INTO m_product_uom_cnv(
1060 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1061 amount_uom_to, create_datetime, create_user_id, update_datetime,
1062 update_user_id, version, active, active_datetime, non_active_datetime)
1063 SELECT A.product_id, A.tenant_id, A.uom_id_1, 1, A.base_uom_id,
1064 B.conv_uom_1_to_base::numeric, pDatetime, pUserId, pDatetime,
1065 pUserId, 0, vYes, pDatetime, vSpaceValue
1066 FROM tt_ul_product A
1067 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1068 WHERE A.upload_header_id = pUploadHeaderId
1069 AND A.upload_header_id=B.upload_header_id
1070 AND A.action_type = vActionUpdate
1071 AND TRIM(B.conv_uom_1_to_base)<>vEmpty
1072 AND is_numeric(B.conv_uom_1_to_base)
1073 AND B.conv_uom_1_to_base::numeric > 0;
1074
1075 -- INSERT PRODUCT UOM CNV ATAS UOM 2
1076 INSERT INTO m_product_uom_cnv(
1077 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1078 amount_uom_to, create_datetime, create_user_id, update_datetime,
1079 update_user_id, version, active, active_datetime, non_active_datetime)
1080 SELECT A.product_id, A.tenant_id, A.uom_id_2, 1, A.base_uom_id,
1081 B.conv_uom_2_to_base::numeric, pDatetime, pUserId, pDatetime,
1082 pUserId, 0, vYes, pDatetime, vSpaceValue
1083 FROM tt_ul_product A
1084 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1085 WHERE A.upload_header_id = pUploadHeaderId
1086 AND A.upload_header_id=B.upload_header_id
1087 AND A.action_type = vActionUpdate
1088 AND TRIM(B.conv_uom_2_to_base)<>vEmpty
1089 AND is_numeric(B.conv_uom_2_to_base)
1090 AND B.conv_uom_2_to_base::numeric > 0;
1091
1092 -- INSERT PRODUCT UOM CNV ATAS UOM 3
1093 INSERT INTO m_product_uom_cnv(
1094 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1095 amount_uom_to, create_datetime, create_user_id, update_datetime,
1096 update_user_id, version, active, active_datetime, non_active_datetime)
1097 SELECT A.product_id, A.tenant_id, A.uom_id_3, 1, A.base_uom_id,
1098 B.conv_uom_3_to_base::numeric, pDatetime, pUserId, pDatetime,
1099 pUserId, 0, vYes, pDatetime, vSpaceValue
1100 FROM tt_ul_product A
1101 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1102 WHERE A.upload_header_id = pUploadHeaderId
1103 AND A.upload_header_id=B.upload_header_id
1104 AND A.action_type = vActionUpdate
1105 AND TRIM(B.conv_uom_3_to_base)<>vEmpty
1106 AND is_numeric(B.conv_uom_3_to_base)
1107 AND B.conv_uom_3_to_base::numeric > 0;
1108
1109 -- Update product group brand
1110 UPDATE m_product_group_brand A SET
1111 group_brand_product=C.kode_group_brand,
1112 update_datetime=pDatetime,
1113 update_user_id=pUserId,
1114 version=A.version+1
1115 FROM tt_ul_product B
1116 INNER JOIN ul_product C ON B.product_code = C.kode_produk
1117 WHERE B.upload_header_id = pUploadHeaderId
1118 AND B.upload_header_id = C.upload_header_id
1119 AND A.product_id = B.product_id
1120 AND B.action_type = vActionUpdate;
1121
1122
1123 -- Update product sparepart
1124 UPDATE m_product_sparepart A SET
1125 vehicle_brand=C.vehicle_brand,
1126 vehicle_brand_type1=C.vehicle_brand_type1,
1127 vehicle_brand_type2=C.vehicle_brand_type2,
1128 vehicle_brand_type3=C.vehicle_brand_type3,
1129 vehicle_year=C.vehicle_year::numeric,
1130 vehicle_engine_capacity=C.vehicle_engine_capacity::numeric,
1131 vehicle_transmission=C.vehicle_transmission,
1132 vehicle_type=C.vehicle_type,
1133 part_no=C.part_no,
1134 remark=C.remark,
1135 update_datetime=pDatetime,
1136 update_user_id=pUserId,
1137 version=A.version+1
1138 FROM tt_ul_product B
1139 INNER JOIN ul_product C ON B.product_code = C.kode_produk
1140 WHERE B.upload_header_id = pUploadHeaderId
1141 AND B.upload_header_id = C.upload_header_id
1142 AND A.product_id = B.product_id
1143 AND B.action_type = vActionUpdate;
1144
1145 END IF;
1146
1147 -- ACTION FOR INSERT
1148 IF EXISTS (SELECT 1 FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
1149
1150 -- Ubah update_status menjadi I jika data digunakan untuk Insert
1151 UPDATE ul_product A SET
1152 update_status = vActionInsert
1153 WHERE A.upload_header_id = pUploadHeaderId
1154 AND EXISTS (
1155 SELECT 1 FROM tt_ul_product B
1156 WHERE A.kode_produk = B.product_code
1157 AND B.tenant_id = vTenantId
1158 AND B.action_type = vActionInsert
1159 );
1160
1161 -- INSERT specs
1162 INSERT INTO m_product_specs(
1163 product_id, tenant_id, product_specs, create_datetime, create_user_id,
1164 update_datetime, update_user_id, version, active, active_datetime,
1165 non_active_datetime)
1166 SELECT A.product_id, A.tenant_id, B.specs, pDatetime, pUserId,
1167 pDatetime, pUserId, 0, vYes, pDatetime, vSpaceValue
1168 FROM tt_ul_product A
1169 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1170 WHERE A.upload_header_id = pUploadHeaderId
1171 AND A.upload_header_id=B.upload_header_id
1172 AND B.flg_validate = vYes
1173 AND A.action_type = vActionInsert
1174 AND TRIM(B.specs)<>vEmpty;
1175
1176 -- INSERT product custom
1177 INSERT INTO m_product_custom(
1178 product_id, flg_buy_konsinyasi, style_product, color, size,
1179 create_datetime, create_user_id, update_datetime,
1180 update_user_id, version, active, active_datetime,
1181 non_active_datetime)
1182 SELECT A.product_id, vNo, B.golongan_product, B.color, B.size,
1183 pDatetime, pUserId, pDatetime,
1184 pUserId, 0, vYes, pDatetime,
1185 vSpaceValue
1186 FROM tt_ul_product A
1187 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1188 WHERE A.upload_header_id = pUploadHeaderId
1189 AND A.upload_header_id=B.upload_header_id
1190 AND B.flg_validate = vYes
1191 AND A.action_type = vActionInsert;
1192
1193 -- INSERT product custom for dlg
1194 INSERT INTO m_product_custom_for_dlg(
1195 product_id, supplier_id,
1196 create_datetime, create_user_id, update_datetime,
1197 update_user_id, version, active, active_datetime,
1198 non_active_datetime)
1199 SELECT A.product_id, C.partner_id,
1200 pDatetime, pUserId, pDatetime,
1201 pUserId, 0, vYes, pDatetime,
1202 vSpaceValue
1203 FROM tt_ul_product A
1204 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1205 INNER JOIN m_partner C ON B.kode_supplier = C.partner_code AND C.tenant_id = A.tenant_id
1206 INNER JOIN m_partner_type D ON C.partner_id = D.partner_id
1207 WHERE A.upload_header_id = pUploadHeaderId
1208 AND A.upload_header_id=B.upload_header_id
1209 AND TRIM(B.kode_supplier)<>vEmpty
1210 AND B.flg_validate = vYes
1211 AND A.action_type = vActionInsert
1212 AND D.group_partner = 'S';
1213
1214 INSERT INTO m_product_custom_for_dlg(
1215 product_id, supplier_id,
1216 create_datetime, create_user_id, update_datetime,
1217 update_user_id, version, active, active_datetime,
1218 non_active_datetime)
1219 SELECT A.product_id, -99,
1220 pDatetime, pUserId, pDatetime,
1221 pUserId, 0, vYes, pDatetime,
1222 vSpaceValue
1223 FROM tt_ul_product A
1224 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1225 WHERE A.upload_header_id = pUploadHeaderId
1226 AND A.upload_header_id=B.upload_header_id
1227 AND TRIM(B.kode_supplier) = vEmpty
1228 AND B.flg_validate = vYes
1229 AND A.action_type = vActionInsert;
1230
1231 -- INSERT product weight dimension
1232 INSERT INTO m_product_weight_dimension(
1233 product_id, tenant_id, weight, dimension_length,
1234 dimension_width, dimension_height, create_datetime, create_user_id, update_datetime,
1235 update_user_id, version, active, active_datetime, non_active_datetime)
1236 SELECT A.product_id, A.tenant_id, B.weight_in_kg::numeric, B.length_in_cm::numeric,
1237 B.width_in_cm::numeric, B.height_in_cm::numeric, pDatetime, pUserId, pDatetime,
1238 pUserId, 0, vYes, pDatetime, vSpaceValue
1239 FROM tt_ul_product A
1240 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1241 WHERE A.upload_header_id = pUploadHeaderId
1242 AND A.upload_header_id=B.upload_header_id
1243 AND B.flg_validate = vYes
1244 AND A.action_type = vActionInsert;
1245
1246 -- INSERT product keyword
1247 WITH detail_keyword AS (
1248 SELECT A.product_id, A.tenant_id, LTRIM(unnest(string_to_array(B.keywords, ','))) AS keyword
1249 FROM tt_ul_product A
1250 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1251 WHERE A.upload_header_id = pUploadHeaderId
1252 AND A.upload_header_id = B.upload_header_id
1253 AND B.flg_validate = vYes
1254 AND A.action_type = vActionInsert
1255 AND TRIM(B.keywords)<>vEmpty
1256 )
1257 INSERT INTO m_product_keyword(
1258 product_id, tenant_id, keyword, create_datetime,
1259 create_user_id, update_datetime, update_user_id, version, active,
1260 active_datetime, non_active_datetime)
1261 SELECT A.product_id, A.tenant_id, A.keyword, pDatetime,
1262 pUserId, pDatetime, pUserId, 0, vYes,
1263 pDatetime, vSpaceValue
1264 FROM detail_keyword A
1265 WHERE TRIM(A.keyword)<>vEmpty;
1266
1267 -- INSERT product barcode
1268 INSERT INTO m_product_barcode(
1269 product_id, tenant_id, barcode, create_datetime, create_user_id,
1270 update_datetime, update_user_id, version, active, active_datetime,
1271 non_active_datetime)
1272 SELECT A.product_id, A.tenant_id, B.barcode, pDatetime, pUserId,
1273 pDatetime, pUserId, 0, vYes, pDatetime,
1274 vSpaceValue
1275 FROM tt_ul_product A
1276 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1277 WHERE A.upload_header_id = pUploadHeaderId
1278 AND A.upload_header_id=B.upload_header_id
1279 AND B.flg_validate = vYes
1280 AND A.action_type = vActionInsert
1281 AND TRIM(B.barcode)<>vEmpty;
1282
1283 -- INSERT PRODUCT UOM CNV ATAS UOM 1
1284 INSERT INTO m_product_uom_cnv(
1285 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1286 amount_uom_to, create_datetime, create_user_id, update_datetime,
1287 update_user_id, version, active, active_datetime, non_active_datetime)
1288 SELECT A.product_id, A.tenant_id, A.uom_id_1, 1, A.base_uom_id,
1289 B.conv_uom_1_to_base::numeric, pDatetime, pUserId, pDatetime,
1290 pUserId, 0, vYes, pDatetime, vSpaceValue
1291 FROM tt_ul_product A
1292 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1293 WHERE A.upload_header_id = pUploadHeaderId
1294 AND A.upload_header_id=B.upload_header_id
1295 AND B.flg_validate = vYes
1296 AND A.action_type = vActionInsert
1297 AND TRIM(B.conv_uom_1_to_base)<>vEmpty
1298 AND is_numeric(B.conv_uom_1_to_base)
1299 AND B.conv_uom_1_to_base::numeric > 0;
1300
1301 -- INSERT PRODUCT UOM CNV ATAS UOM 2
1302 INSERT INTO m_product_uom_cnv(
1303 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1304 amount_uom_to, create_datetime, create_user_id, update_datetime,
1305 update_user_id, version, active, active_datetime, non_active_datetime)
1306 SELECT A.product_id, A.tenant_id, A.uom_id_2, 1, A.base_uom_id,
1307 B.conv_uom_2_to_base::numeric, pDatetime, pUserId, pDatetime,
1308 pUserId, 0, vYes, pDatetime, vSpaceValue
1309 FROM tt_ul_product A
1310 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1311 WHERE A.upload_header_id = pUploadHeaderId
1312 AND A.upload_header_id=B.upload_header_id
1313 AND B.flg_validate = vYes
1314 AND A.action_type = vActionInsert
1315 AND TRIM(B.conv_uom_2_to_base)<>vEmpty
1316 AND is_numeric(B.conv_uom_2_to_base)
1317 AND B.conv_uom_2_to_base::numeric > 0;
1318
1319 -- INSERT PRODUCT UOM CNV ATAS UOM 3
1320 INSERT INTO m_product_uom_cnv(
1321 product_id, tenant_id, uom_id_from, amount_uom_from, uom_id_to,
1322 amount_uom_to, create_datetime, create_user_id, update_datetime,
1323 update_user_id, version, active, active_datetime, non_active_datetime)
1324 SELECT A.product_id, A.tenant_id, A.uom_id_3, 1, A.base_uom_id,
1325 B.conv_uom_3_to_base::numeric, pDatetime, pUserId, pDatetime,
1326 pUserId, 0, vYes, pDatetime, vSpaceValue
1327 FROM tt_ul_product A
1328 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1329 WHERE A.upload_header_id = pUploadHeaderId
1330 AND A.upload_header_id=B.upload_header_id
1331 AND B.flg_validate = vYes
1332 AND A.action_type = vActionInsert
1333 AND TRIM(B.conv_uom_3_to_base)<>vEmpty
1334 AND is_numeric(B.conv_uom_3_to_base)
1335 AND B.conv_uom_3_to_base::numeric > 0;
1336
1337 -- INSERT product group brand
1338 INSERT INTO m_product_group_brand(
1339 product_id, tenant_id, group_brand_product, create_datetime,
1340 create_user_id, update_datetime, update_user_id, version)
1341 SELECT A.product_id, A.tenant_id, B.kode_group_brand, pDatetime,
1342 pUserId, pDatetime, pUserId, 0
1343 FROM tt_ul_product A
1344 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1345 WHERE A.upload_header_id = pUploadHeaderId
1346 AND B.flg_validate = vYes
1347 AND A.upload_header_id=B.upload_header_id
1348 AND A.action_type = vActionInsert;
1349
1350 -- INSERT data product consignment supp info, dengan nilai -99 dan empty value
1351 -- INSERT INTO m_product_consignment_supp_info(
1352 -- product_id, supplier_id, supplier_product_code, create_datetime,
1353 -- create_user_id, update_datetime, update_user_id, version, active,
1354 -- active_datetime, non_active_datetime)
1355 -- SELECT A.product_id, -99, vEmpty, pDatetime,
1356 -- pUserId, pDatetime, pUserId, 0, vYes,
1357 -- pDatetime, vSpaceValue
1358 -- FROM tt_ul_product A
1359 -- INNER JOIN ul_product B ON A.product_code = B.kode_produk
1360 -- WHERE A.upload_header_id = pUploadHeaderId
1361 -- AND A.upload_header_id=B.upload_header_id
1362 -- AND B.flg_validate = vYes
1363 -- AND A.action_type = vActionInsert;
1364
1365 -- INSERT product sparepart
1366 INSERT INTO m_product_sparepart(
1367 product_id, tenant_id, vehicle_brand, vehicle_brand_type1, vehicle_brand_type2,
1368 vehicle_brand_type3, vehicle_year, vehicle_engine_capacity, vehicle_transmission,
1369 vehicle_type, part_no,
1370 create_datetime, create_user_id, update_datetime,
1371 update_user_id, version, active, active_datetime,
1372 non_active_datetime)
1373 SELECT
1374 A.product_id, tenant_id, B.vehicle_brand, B.vehicle_brand_type1, B.vehicle_brand_type2,
1375 B.vehicle_brand_type3, B.vehicle_year::numeric, B.vehicle_engine_capacity::numeric, B.vehicle_transmission,B.vehicle_type, B.part_no,
1376 pDatetime, pUserId, pDatetime,
1377 pUserId, 0, vYes, pDatetime,
1378 vSpaceValue
1379 FROM tt_ul_product A
1380 INNER JOIN ul_product B ON A.product_code = B.kode_produk
1381 WHERE A.upload_header_id = pUploadHeaderId
1382 AND A.upload_header_id=B.upload_header_id
1383 AND B.flg_validate = vYes
1384 AND A.action_type = vActionInsert;
1385
1386 END IF;
1387
1388 -- RESET table temp
1389 DELETE FROM tt_ul_product WHERE upload_header_id = pUploadHeaderId;
1390
1391
1392END;
1393$BODY$
1394 LANGUAGE plpgsql VOLATILE
1395 COST 100;