· 7 years ago · Oct 05, 2018, 10:28 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 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_stock_product A SET
354 update_status = vActionUpdate
355 WHERE A.upload_header_id = pUploadHeaderId
356 AND EXISTS (
357 SELECT 1 FROM ul_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_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_stock_product A SET
410 update_status = vActionInsert
411 WHERE A.upload_header_id = pUploadHeaderId
412 AND EXISTS (
413 SELECT 1 FROM ul_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_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_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 -- Update existing data summary monthly qty
481 UPDATE in_summary_monthly_qty A SET
482 qty = B.qty::NUMERIC,
483 VERSION = A.VERSION+1,
484 update_datetime = vDatetime,
485 update_user_id = vUserId
486 FROM ul_stock_product B
487 INNER JOIN m_product BX ON BX.product_code = B.kode_produk AND BX.tenant_id = vTenantId
488 INNER JOIN tt_ul_product_balance C
489 ON C.product_id = BX.product_id
490 AND C.serial_number = B.serial_number
491 AND C.upload_header_id = B.upload_header_id
492 AND C.tenant_id = vTenantId
493 AND C.lot_number = vSpaceValue
494 INNER JOIN m_warehouse D ON D.warehouse_code = B.kode_gudang AND D.tenant_id = vTenantId
495 WHERE B.upload_header_id = pUploadHeaderId
496 AND A.tenant_id = vTenantId
497 AND A.date_year_month = vYearMonth
498 AND A.ou_id = vOuCompanyId
499 AND A.sub_ou_id = vOuCompanyId
500 AND A.doc_type_id = vEmptyId
501 AND A.warehouse_id = D.warehouse_id
502 AND A.product_id = BX.product_id
503 AND A.product_balance_id = C.product_balance_id
504 AND A.product_status = B.status_produk;
505
506 -- Insert new data summary monthly qty
507 INSERT INTO in_summary_monthly_qty(
508 date_year_month, tenant_id, ou_id, sub_ou_id,
509 doc_type_id, warehouse_id, product_id, product_balance_id, product_status,
510 base_uom_id, qty, VERSION, create_datetime, create_user_id, update_datetime,
511 update_user_id)
512 SELECT vYearMonth, vTenantId, vOuCompanyId, vOuCompanyId,
513 vEmptyId, D.warehouse_id, B.product_id, C.product_balance_id, A.status_produk,
514 B.base_uom_id, A.qty::NUMERIC, 0, vDatetime, vUserId, vDatetime,
515 vUserId
516 FROM ul_stock_product A
517 INNER JOIN m_product B ON B.product_code = A.kode_produk AND B.tenant_id = vTenantId
518 INNER JOIN tt_ul_product_balance C
519 ON C.product_id = B.product_id
520 AND C.serial_number = A.serial_number
521 AND C.upload_header_id = A.upload_header_id
522 AND C.tenant_id = vTenantId
523 AND C.lot_number = vSpaceValue
524 INNER JOIN m_warehouse D ON A.kode_gudang = D.warehouse_code AND D.tenant_id = vTenantId
525 WHERE A.upload_header_id = pUploadHeaderId
526 AND NOT EXISTS (
527 SELECT 1 FROM in_summary_monthly_qty Z
528 WHERE Z.tenant_id = vTenantId
529 AND Z.date_year_month = vYearMonth
530 AND Z.ou_id = vOuCompanyId
531 AND Z.sub_ou_id = vOuCompanyId
532 AND Z.doc_type_id = vEmptyId
533 AND Z.warehouse_id = D.warehouse_id
534 AND Z.product_id = B.product_id
535 AND Z.product_balance_id = C.product_balance_id
536 AND Z.product_status = A.status_produk
537 );
538
539 -- RESET TEMP TABLE
540 DELETE FROM tt_ul_product_balance WHERE upload_header_id = pUploadHeaderId;
541
542 END IF;
543
544 RETURN vCountFail;
545
546END;
547$BODY$
548 LANGUAGE plpgsql VOLATILE
549 COST 100;
550 /