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