· 7 years ago · Dec 04, 2018, 07:18 AM
1CREATE OR REPLACE FUNCTION ul_upload_stock_product(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 vActionInsert character varying(1) := 'I';
12 vActionUpdate character varying(1) := 'U';
13 vActionError character varying(1) := 'E';
14 vEmpty character varying(1) := '';
15 vSpaceValue character varying(1) := ' ';
16
17 vTenantId bigint;
18 vUserId bigint;
19 vDatetime character varying(14);
20 vYearMonth character varying(6);
21
22 vEmptyId bigint := -99;
23 vOuCompanyId bigint := -99;
24 vCountFail bigint := 0;
25
26 vKeyYearMonth character varying := 'yearMonth';
27
28BEGIN
29 -- siapkan parameter
30 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
31 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
32 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
33
34 vYearMonth = CAST( f_get_upload_parameter(pUploadHeaderId, vKeyYearMonth) AS character varying );
35
36 -- GET id OU company
37 SELECT A.ou_id INTO vOuCompanyId
38 FROM t_ou A
39 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
40 WHERE A.tenant_id = vTenantId
41 AND B.flg_bu = vYes
42 AND B.flg_sub_bu = vNo
43 AND B.flg_branch = vNo
44 AND B.tenant_id = vTenantId;
45
46 -- CHANGE EMPTY VALYE TO SPACE VALUE
47 UPDATE ul_import_data_stock_product A SET
48 serial_number = CASE WHEN TRIM(A.serial_number) = vEmpty THEN vSpaceValue ELSE A.serial_number END,
49 expired_date = CASE WHEN TRIM(A.expired_date) = vEmpty THEN vSpaceValue ELSE A.expired_date END,
50 year_made = CASE WHEN TRIM(A.year_made) = vEmpty THEN vSpaceValue ELSE A.year_made END
51 WHERE A.upload_header_id = pUploadHeaderId;
52
53 -- Validasi
54 -- # Kode produk wajib diisi dan harus terdaftar di dalam system (m_product)
55 -- # Kode gudang wajib diisi dan harus terdaftar di dalam system (m_warehouse)
56 -- # Status product harus diisi, dan harus terdaftar di dala system (m_product_status)
57 -- # Qty wajib diisi, dengan type data adalah numeric, dan tidak boleh < 0
58 -- # Hanya produk dengan flg_serial_number = Y yang boleh diisi kolom Serial Number, Year Made dan Expired Date nya
59 -- # List tidak boleh duplikat by Kode produk, Kode gudang, Serial Number, Expired date, Year made, Status produk
60
61 -- # Kode produk wajib diisi dan harus terdaftar di dalam system (m_product)
62 UPDATE ul_import_data_stock_product A
63 SET status = vFail,
64 message = message || 'Product Code must be filled, '
65 WHERE A.upload_header_id = pUploadHeaderId
66 AND TRIM(A.kode_produk) = vEmpty;
67
68 UPDATE ul_import_data_stock_product A
69 SET status = vFail,
70 message = message || 'Product Code is not registered on system, '
71 WHERE A.upload_header_id = pUploadHeaderId
72 AND TRIM(A.kode_produk) <> vEmpty
73 AND NOT EXISTS (
74 SELECT 1
75 FROM m_product Z
76 WHERE Z.product_code = A.kode_produk
77 AND Z.tenant_id = vTenantId
78 );
79
80 -- # Kode gudang wajib diisi dan harus terdaftar di dalam system (m_warehouse)
81 UPDATE ul_import_data_stock_product A
82 SET status = vFail,
83 message = message || 'Warehouse Code must be filled, '
84 WHERE A.upload_header_id = pUploadHeaderId
85 AND TRIM(A.kode_gudang) = vEmpty;
86
87 UPDATE ul_import_data_stock_product A
88 SET status = vFail,
89 message = message || 'Warehouse Code is not registered on system, '
90 WHERE A.upload_header_id = pUploadHeaderId
91 AND TRIM(A.kode_gudang) <> vEmpty
92 AND NOT EXISTS (
93 SELECT 1
94 FROM m_warehouse Z
95 WHERE Z.warehouse_code = A.kode_gudang
96 AND Z.tenant_id = vTenantId
97 );
98
99 -- # Status product harus diisi, dan harus terdaftar di dala system (m_product_status)
100 UPDATE ul_import_data_stock_product A
101 SET status = vFail,
102 message = message || 'Status Produk must be filled, '
103 WHERE A.upload_header_id = pUploadHeaderId
104 AND TRIM(A.status_produk) = vEmpty;
105
106 UPDATE ul_import_data_stock_product A
107 SET status = vFail,
108 message = message || 'Status Produk is not registered on system, '
109 WHERE A.upload_header_id = pUploadHeaderId
110 AND TRIM(A.status_produk) <> vEmpty
111 AND NOT EXISTS (
112 SELECT 1
113 FROM m_product_status Z
114 WHERE Z.product_status_code = A.status_produk
115 AND Z.tenant_id = vTenantId
116 );
117
118 -- # Qty wajib diisi, dengan type data adalah numeric, dan tidak boleh < 0
119 UPDATE ul_import_data_stock_product A
120 SET status = vFail,
121 message = message || 'Qty must be filled, '
122 WHERE A.upload_header_id = pUploadHeaderId
123 AND TRIM(A.qty) = vEmpty;
124
125 UPDATE ul_import_data_stock_product A
126 SET status = vFail,
127 message = message || 'Qty must be numeric, '
128 WHERE A.upload_header_id = pUploadHeaderId
129 AND NOT is_numeric(A.qty);
130
131 WITH data_stock_product AS (
132 SELECT ul_import_data_stock_product_id, qty
133 FROM ul_import_data_stock_product
134 WHERE upload_header_id = pUploadHeaderId
135 AND TRIM(qty) <> vEmpty
136 AND is_numeric(qty)
137 )
138 UPDATE ul_import_data_stock_product A
139 SET status = vFail,
140 message = message || 'Qty must be greater than or equals zero, '
141 FROM data_stock_product B
142 WHERE A.ul_import_data_stock_product_id = B.ul_import_data_stock_product_id
143 AND B.qty::numeric < 0;
144
145 -- # Hanya produk dengan flg_serial_number = Y yang boleh diisi kolom Serial Number, Year Made dan Expired Date nya
146 UPDATE ul_import_data_stock_product A
147 SET status = vFail,
148 message = message || 'Serial Number must be filled, '
149 WHERE A.upload_header_id = pUploadHeaderId
150 AND TRIM(A.serial_number) = vEmpty
151 AND TRIM(A.kode_produk) <> vEmpty
152 AND EXISTS (
153 SELECT 1 FROM m_product Z
154 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
155 WHERE A.kode_produk = Z.product_code
156 AND Z.tenant_id = vTenantId
157 AND Y.flg_serial_number = vYes
158 );
159
160 UPDATE ul_import_data_stock_product A
161 SET status = vFail,
162 message = message || 'Year Made must be filled, '
163 WHERE A.upload_header_id = pUploadHeaderId
164 AND TRIM(A.year_made) = vEmpty
165 AND TRIM(A.kode_produk) <> vEmpty
166 AND EXISTS (
167 SELECT 1 FROM m_product Z
168 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
169 WHERE A.kode_produk = Z.product_code
170 AND Z.tenant_id = vTenantId
171 AND Y.flg_serial_number = vYes
172 );
173
174 WITH groupped_year AS (
175 SELECT year_date
176 FROM dt_date
177 GROUP BY year_date
178 )
179 UPDATE ul_import_data_stock_product A
180 SET status = vFail,
181 message = message || 'Year Made is not valid (format:YYYY), '
182 WHERE A.upload_header_id = pUploadHeaderId
183 AND TRIM(A.year_made) <> vEmpty
184 AND NOT EXISTS (
185 SELECT 1 FROM groupped_year Z WHERE A.year_made = Z.year_date
186 );
187
188 UPDATE ul_import_data_stock_product A
189 SET status = vFail,
190 message = message || 'Expired Date must be filled, '
191 WHERE A.upload_header_id = pUploadHeaderId
192 AND TRIM(A.expired_date) = vEmpty
193 AND TRIM(A.kode_produk) <> vEmpty
194 AND EXISTS (
195 SELECT 1 FROM m_product Z
196 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
197 WHERE A.kode_produk = Z.product_code
198 AND Z.tenant_id = vTenantId
199 AND Y.flg_serial_number = vYes
200 );
201
202 UPDATE ul_import_data_stock_product A
203 SET status = vFail,
204 message = message || 'Expired Date is not valid (format:YYYYMMDD), '
205 WHERE A.upload_header_id = pUploadHeaderId
206 AND TRIM(A.expired_date) <> vEmpty
207 AND NOT EXISTS (
208 SELECT 1 FROM dt_date Z WHERE A.expired_date = Z.string_date
209 );
210
211 UPDATE ul_import_data_stock_product A
212 SET status = vFail,
213 message = message || 'Serial Number must be empty value cause product does not support serial number, '
214 WHERE A.upload_header_id = pUploadHeaderId
215 AND TRIM(A.serial_number) <> vEmpty
216 AND TRIM(A.kode_produk) <> vEmpty
217 AND NOT EXISTS (
218 SELECT 1 FROM m_product Z
219 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
220 WHERE A.kode_produk = Z.product_code
221 AND Z.tenant_id = vTenantId
222 AND Y.flg_serial_number = vYes
223 );
224
225 UPDATE ul_import_data_stock_product A
226 SET status = vFail,
227 message = message || 'Year Made must be empty value cause product does not support serial number, '
228 WHERE A.upload_header_id = pUploadHeaderId
229 AND TRIM(A.year_made) <> vEmpty
230 AND TRIM(A.kode_produk) <> vEmpty
231 AND NOT EXISTS (
232 SELECT 1 FROM m_product Z
233 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
234 WHERE A.kode_produk = Z.product_code
235 AND Z.tenant_id = vTenantId
236 AND Y.flg_serial_number = vYes
237 );
238
239 UPDATE ul_import_data_stock_product A
240 SET status = vFail,
241 message = message || 'Expired Date must be empty value cause product does not support serial number, '
242 WHERE A.upload_header_id = pUploadHeaderId
243 AND TRIM(A.expired_date) <> vEmpty
244 AND TRIM(A.kode_produk) <> vEmpty
245 AND NOT EXISTS (
246 SELECT 1 FROM m_product Z
247 INNER JOIN m_sub_ctgr_product Y ON Z.sub_ctgr_product_id = Y.sub_ctgr_product_id
248 WHERE A.kode_produk = Z.product_code
249 AND Z.tenant_id = vTenantId
250 AND Y.flg_serial_number = vYes
251 );
252
253 -- # List tidak boleh duplikat by Kode produk, Kode gudang, Serial Number, Expired date, Year made, Status produk
254 WITH duplicate_data_stock_product AS (
255 SELECT A.kode_produk, A.kode_gudang, A.serial_number, A.expired_date, A.year_made, A.status_produk
256 FROM ul_import_data_stock_product A
257 WHERE A.upload_header_id = pUploadHeaderId
258 AND TRIM(A.kode_produk) <> vEmpty
259 AND TRIM(A.kode_gudang) <> vEmpty
260 AND TRIM(A.status_produk) <> vEmpty
261 GROUP BY A.kode_produk, A.kode_gudang, A.serial_number, A.expired_date, A.year_made, A.status_produk
262 HAVING COUNT(1) > 1
263 )
264 UPDATE ul_import_data_stock_product A
265 SET status = vFail,
266 message = message || 'Duplicate data by "Kode Produk, Kode Gudang, Serial Number, Expired Date, Year Made, Status Produk", '
267 WHERE A.upload_header_id = pUploadHeaderId
268 AND EXISTS (
269 SELECT 1 FROM duplicate_data_stock_product Z
270 WHERE A.kode_produk = Z.kode_produk
271 AND A.kode_gudang = Z.kode_gudang
272 AND A.serial_number = Z.serial_number
273 AND A.expired_date = Z.expired_date
274 AND A.year_made = Z.year_made
275 AND A.status_produk = Z.status_produk
276 );
277
278 -- Cek apakah ada item CSV yang statusnya fail
279 IF EXISTS ( SELECT 1 FROM ul_import_data_stock_product B
280 WHERE B.upload_header_id = pUploadHeaderId
281 AND B.status = vFail ) THEN
282
283 -- Sesuaikan message, agar message paling belakang tidak ada koma
284 UPDATE ul_import_data_stock_product A
285 SET message = substr(A.message, 1, length(A.message)-2)
286 WHERE A.upload_header_id = pUploadHeaderId
287 AND A.status = vFail
288 AND TRIM(A.message) != vEmpty;
289
290 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
291 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
292 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
293 UPDATE ul_import_data_stock_product A
294 SET status = vFail,
295 update_status = vActionError
296 WHERE upload_header_id = pUploadHeaderId;
297
298 -- Hitung jumlah data yang FAIL
299 SELECT COUNT(1) INTO vCountFail
300 FROM ul_import_data_stock_product
301 WHERE upload_header_id = pUploadHeaderId
302 AND status = vFail;
303 ELSE
304 -- RESET TEMP TABLE
305 DELETE FROM tt_ul_product_balance WHERE upload_header_id = pUploadHeaderId;
306
307 -- Update status non FAIL to OK
308 UPDATE ul_import_data_stock_product
309 SET status = vOk
310 WHERE upload_header_id = pUploadHeaderId;
311
312 -- UPDATE SEMUA DATA EXISTING DI SYSTME MENJADI qty = 0
313 UPDATE in_product_balance_stock A SET
314 qty = 0,
315 version = A.version+1,
316 update_datetime = vDatetime,
317 update_user_id = vUserId
318 WHERE A.tenant_id = vTenantId;
319
320 -- DELETE ALL DATA SUMMARY MONTHLY QTY
321 DELETE FROM in_summary_monthly_qty Z WHERE Z.tenant_id = vTenantId;
322
323 -- UPDATE EXISTING DATA PADA in_product_balance_stock
324 WITH update_data_balance AS (
325 UPDATE in_product_balance A SET
326 product_expired_date=B.expired_date,
327 product_year_made=B.year_made,
328 version = A.version+1,
329 update_datetime = vDatetime,
330 update_user_id = vUserId
331 FROM ul_import_data_stock_product B
332 INNER JOIN m_product C ON C.product_code = B.kode_produk AND C.tenant_id = vTenantId
333 WHERE B.upload_header_id = pUploadHeaderId
334 AND A.tenant_id = vTenantId
335 AND A.product_id = C.product_id
336 AND A.serial_number = B.serial_number
337 AND A.lot_number = vSpaceValue
338
339 RETURNING A.*
340 )
341 INSERT INTO tt_ul_product_balance(
342 upload_header_id, action_type, product_balance_id, tenant_id,
343 product_id, serial_number, lot_number, product_expired_date,
344 product_year_made, version, create_datetime, create_user_id,
345 update_datetime, update_user_id)
346 SELECT pUploadHeaderId, vActionUpdate, product_balance_id, tenant_id,
347 product_id, serial_number, lot_number, product_expired_date,
348 product_year_made, version, create_datetime, create_user_id,
349 update_datetime, update_user_id
350 FROM update_data_balance;
351
352 -- Ubah update_status menjadi U jika data digunakan untuk Update
353 UPDATE ul_import_data_stock_product A SET
354 update_status = vActionUpdate
355 WHERE A.upload_header_id = pUploadHeaderId
356 AND EXISTS (
357 SELECT 1 FROM ul_import_data_stock_product Z
358 INNER JOIN m_product ZX ON ZX.product_code = Z.kode_produk AND ZX.tenant_id = vTenantId
359 INNER JOIN tt_ul_product_balance Y
360 ON Y.product_id = ZX.product_id
361 AND Y.serial_number = Z.serial_number
362 AND Y.upload_header_id = Z.upload_header_id
363 AND Y.action_type = vActionUpdate
364 AND Y.tenant_id = vTenantId
365 AND Y.lot_number = vSpaceValue
366 WHERE Z.upload_header_id = pUploadHeaderId
367 AND A.kode_produk = ZX.product_code
368 AND A.kode_gudang = Z.kode_gudang
369 AND A.serial_number = Y.serial_number
370 AND A.expired_date = Y.product_expired_date
371 AND A.year_made = Y.product_year_made
372 AND A.status_produk = Z.status_produk
373 );
374
375 -- INSERT UNTUK DATA BARU
376 WITH insert_data_balance AS (
377 INSERT INTO in_product_balance(
378 tenant_id, product_id, serial_number, lot_number,
379 product_expired_date, product_year_made, version, create_datetime,
380 create_user_id, update_datetime, update_user_id)
381 SELECT vTenantId, B.product_id, A.serial_number, vSpaceValue,
382 A.expired_date, A.year_made, 0, vDatetime,
383 vUserId, vDatetime, vUserId
384 FROM ul_import_data_stock_product A
385 INNER JOIN m_product B ON A.kode_produk = B.product_code AND B.tenant_id = vTenantId
386 WHERE A.upload_header_id = pUploadHeaderId
387 AND NOT EXISTS (
388 SELECT 1 FROM in_product_balance Z
389 WHERE Z.tenant_id = vTenantId
390 AND Z.product_id = B.product_id
391 AND Z.serial_number = A.serial_number
392 AND Z.lot_number = vSpaceValue
393 )
394 GROUP BY B.product_id, A.serial_number, A.expired_date, A.year_made
395 RETURNING *
396 )
397 INSERT INTO tt_ul_product_balance(
398 upload_header_id, action_type, product_balance_id, tenant_id,
399 product_id, serial_number, lot_number, product_expired_date,
400 product_year_made, version, create_datetime, create_user_id,
401 update_datetime, update_user_id)
402 SELECT pUploadHeaderId, vActionInsert, product_balance_id, tenant_id,
403 product_id, serial_number, lot_number, product_expired_date,
404 product_year_made, version, create_datetime, create_user_id,
405 update_datetime, update_user_id
406 FROM insert_data_balance;
407
408 -- Ubah update_status menjadi I jika data digunakan untuk Insert
409 UPDATE ul_import_data_stock_product A SET
410 update_status = vActionInsert
411 WHERE A.upload_header_id = pUploadHeaderId
412 AND EXISTS (
413 SELECT 1 FROM ul_import_data_stock_product Z
414 INNER JOIN m_product ZX ON ZX.product_code = Z.kode_produk AND ZX.tenant_id = vTenantId
415 INNER JOIN tt_ul_product_balance Y
416 ON Y.product_id = ZX.product_id
417 AND Y.serial_number = Z.serial_number
418 AND Y.upload_header_id = Z.upload_header_id
419 AND Y.action_type = vActionInsert
420 AND Y.tenant_id = vTenantId
421 AND Y.lot_number = vSpaceValue
422 WHERE Z.upload_header_id = pUploadHeaderId
423 AND A.kode_produk = ZX.product_code
424 AND A.kode_gudang = Z.kode_gudang
425 AND A.serial_number = Y.serial_number
426 AND A.expired_date = Y.product_expired_date
427 AND A.year_made = Y.product_year_made
428 AND A.status_produk = Z.status_produk
429 );
430
431 -- Update existing data balance stock
432 UPDATE in_product_balance_stock A SET
433 qty = B.qty::NUMERIC,
434 VERSION = A.VERSION+1,
435 update_datetime = vDatetime,
436 update_user_id = vUserId
437 FROM ul_import_data_stock_product B
438 INNER JOIN m_product BX ON BX.product_code = B.kode_produk AND BX.tenant_id = vTenantId
439 INNER JOIN tt_ul_product_balance C
440 ON C.product_id = BX.product_id
441 AND C.serial_number = B.serial_number
442 AND C.upload_header_id = B.upload_header_id
443 AND C.tenant_id = vTenantId
444 AND C.lot_number = vSpaceValue
445 INNER JOIN m_warehouse D ON D.warehouse_code = B.kode_gudang AND D.tenant_id = vTenantId
446 WHERE B.upload_header_id = pUploadHeaderId
447 AND A.tenant_id = vTenantId
448 AND A.warehouse_id = D.warehouse_id
449 AND A.product_id = BX.product_id
450 AND A.product_balance_id = C.product_balance_id
451 AND A.product_status = B.status_produk;
452
453 -- Insert new data balance stock
454 INSERT INTO in_product_balance_stock(
455 tenant_id, warehouse_id, product_id,
456 product_balance_id, product_status, base_uom_id, qty, VERSION,
457 create_datetime, create_user_id, update_datetime, update_user_id)
458 SELECT vTenantId, D.warehouse_id, B.product_id,
459 C.product_balance_id, A.status_produk, B.base_uom_id, A.qty::NUMERIC, 0,
460 vDatetime, vUserId, vDatetime, vUserId
461 FROM ul_import_data_stock_product A
462 INNER JOIN m_product B ON B.product_code = A.kode_produk AND B.tenant_id = vTenantId
463 INNER JOIN tt_ul_product_balance C
464 ON C.product_id = B.product_id
465 AND C.serial_number = A.serial_number
466 AND C.upload_header_id = A.upload_header_id
467 AND C.tenant_id = vTenantId
468 AND C.lot_number = vSpaceValue
469 INNER JOIN m_warehouse D ON A.kode_gudang = D.warehouse_code AND D.tenant_id = vTenantId
470 WHERE A.upload_header_id = pUploadHeaderId
471 AND NOT EXISTS (
472 SELECT 1 FROM in_product_balance_stock Z
473 WHERE Z.tenant_id = vTenantId
474 AND Z.warehouse_id = D.warehouse_id
475 AND Z.product_id = B.product_id
476 AND Z.product_balance_id = C.product_balance_id
477 AND Z.product_status = A.status_produk
478 );
479
480 -- Insert data summary monthly qty
481 INSERT INTO in_summary_monthly_qty(
482 date_year_month, tenant_id, ou_id, sub_ou_id,
483 doc_type_id, warehouse_id, product_id, product_balance_id, product_status,
484 base_uom_id, qty, VERSION, create_datetime, create_user_id, update_datetime,
485 update_user_id)
486 SELECT vYearMonth, vTenantId, vOuCompanyId, vOuCompanyId,
487 vEmptyId, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status,
488 A.base_uom_id, A.qty, 0, vDatetime, vUserId, vDatetime,
489 vUserId
490 FROM in_product_balance_stock A
491 WHERE A.tenant_id = vTenantId;
492
493 -- RESET TEMP TABLE
494 DELETE FROM tt_ul_product_balance WHERE upload_header_id = pUploadHeaderId;
495
496 END IF;
497
498 RETURN vCountFail;
499
500END;
501$BODY$
502 LANGUAGE plpgsql VOLATILE
503 COST 100;
504 /