· 6 years ago · Dec 26, 2019, 08:16 AM
1CREATE OR REPLACE FUNCTION ul_upload_sub_ctgr_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 vSessionId text;
21
22 vCountFail bigint := 0;
23
24 vTypeData character varying := 'SubCtgrProduct';
25 vModeLogAdd character varying := 'A';
26 vModeLogUpdate character varying := 'U';
27 vNullValueLong bigint := -99;
28
29BEGIN
30 -- siapkan parameter
31 vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
32 vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
33 vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
34 vSessionId = CAST( f_get_upload_parameter(pUploadHeaderId, 'sessionId') AS text );
35
36 -- Validasi
37 -- # Kode sub kategori wajib diisi
38 -- # Max character dari Kode sub kategori adalah 50
39 -- # Nama sub kategori wajib diisi
40 -- # Max character dari Nama sub kategori adalah 100
41 -- # Kode kategori harus diisi dan harus tedaftar disystem dengan tenant sesuai dengan tenant login
42 -- # Untuk field-field berikut, wajib diisi dan hanya boleh diisi Y/N :
43 -- # - Flag Serial Number
44 -- # - Flag Barcode
45 -- # - Flag Warranty
46 -- # - Flag Weight
47 -- # - Flag Parts
48 -- # - Flag Dimension
49 -- # - Flag Spec
50 -- # - Flag Expired
51 -- # - Flag Year Made
52 -- # - Active
53 -- # List pada CSV yang diupload, tidak boleh duplikat by Kode sub kategori
54
55 -- # Kode sub kategori wajib diisi
56 UPDATE ul_import_data_sub_ctgr_product A
57 SET status = vFail,
58 message = message || 'Sub category code must be filled, '
59 WHERE A.upload_header_id = pUploadHeaderId
60 AND TRIM(A.kode_sub_kategori) = vEmpty;
61
62 -- # Max character dari Kode sub kategori adalah 50
63 UPDATE ul_import_data_sub_ctgr_product A
64 SET status = vFail,
65 message = message || 'Max sub category code is 100 character, '
66 WHERE A.upload_header_id = pUploadHeaderId
67 AND length(A.kode_sub_kategori) > 100;
68
69 -- # Nama sub kategori wajib diisi
70 UPDATE ul_import_data_sub_ctgr_product A
71 SET status = vFail,
72 message = message || 'Sub category name must be filled, '
73 WHERE A.upload_header_id = pUploadHeaderId
74 AND TRIM(A.nama_sub_kategori) = vEmpty;
75
76 -- # Max character dari Nama sub kategori adalah 100
77 UPDATE ul_import_data_sub_ctgr_product A
78 SET status = vFail,
79 message = message || 'Max sub category name is 100 character, '
80 WHERE A.upload_header_id = pUploadHeaderId
81 AND length(A.nama_sub_kategori) > 100;
82
83 -- # Kode kategori harus diisi
84 UPDATE ul_import_data_sub_ctgr_product A
85 SET status = vFail,
86 message = message || 'Category product code must be filled, '
87 WHERE A.upload_header_id = pUploadHeaderId
88 AND TRIM(A.kode_kategori) = vEmpty;
89
90 -- # Kode kategori harus harus tedaftar disystem dengan tenant sesuai dengan tenant login
91 UPDATE ul_import_data_sub_ctgr_product A
92 SET status = vFail,
93 message = message || 'Category product is not registered on system, '
94 WHERE A.upload_header_id = pUploadHeaderId
95 AND TRIM(A.kode_kategori) <> vEmpty
96 AND NOT EXISTS(
97 SELECT 1 FROM m_ctgr_product B
98 WHERE A.kode_kategori = B.ctgr_product_code
99 AND B.tenant_id = vTenantId
100 AND B.active = vYes
101 );
102
103 -- # Untuk field-field berikut, wajib diisi dan hanya boleh diisi Y/N :
104 -- # - Flag Serial Number
105 -- # - Flag Barcode
106 -- # - Flag Warranty
107 -- # - Flag Weight
108 -- # - Flag Parts
109 -- # - Flag Dimension
110 -- # - Flag Spec
111 -- # - Flag Expired
112 -- # - Flag Year Made
113 -- # - Active
114 UPDATE ul_import_data_sub_ctgr_product A
115 SET status = vFail,
116 message = message || 'Flag serial number can only be filled with Y or N, '
117 WHERE A.upload_header_id = pUploadHeaderId
118 AND A.flag_serial_number NOT IN (vYes, vNo);
119
120 UPDATE ul_import_data_sub_ctgr_product A
121 SET status = vFail,
122 message = message || 'Flag barcode can only be filled with Y or N, '
123 WHERE A.upload_header_id = pUploadHeaderId
124 AND A.flag_barcode NOT IN (vYes, vNo);
125
126 UPDATE ul_import_data_sub_ctgr_product A
127 SET status = vFail,
128 message = message || 'Flag warranty can only be filled with Y or N, '
129 WHERE A.upload_header_id = pUploadHeaderId
130 AND A.flag_warranty NOT IN (vYes, vNo);
131
132 UPDATE ul_import_data_sub_ctgr_product A
133 SET status = vFail,
134 message = message || 'Flag weight can only be filled with Y or N, '
135 WHERE A.upload_header_id = pUploadHeaderId
136 AND A.flag_weight NOT IN (vYes, vNo);
137
138 UPDATE ul_import_data_sub_ctgr_product A
139 SET status = vFail,
140 message = message || 'Flag parts can only be filled with Y or N, '
141 WHERE A.upload_header_id = pUploadHeaderId
142 AND A.flag_parts NOT IN (vYes, vNo);
143
144
145 UPDATE ul_import_data_sub_ctgr_product A
146 SET status = vFail,
147 message = message || 'Flag dimension can only be filled with Y or N, '
148 WHERE A.upload_header_id = pUploadHeaderId
149 AND A.flag_dimension NOT IN (vYes, vNo);
150
151 UPDATE ul_import_data_sub_ctgr_product A
152 SET status = vFail,
153 message = message || 'Flag spec can only be filled with Y or N, '
154 WHERE A.upload_header_id = pUploadHeaderId
155 AND A.flag_spec NOT IN (vYes, vNo);
156
157 UPDATE ul_import_data_sub_ctgr_product A
158 SET status = vFail,
159 message = message || 'Flag expired can only be filled with Y or N, '
160 WHERE A.upload_header_id = pUploadHeaderId
161 AND A.flag_expired NOT IN (vYes, vNo);
162
163 UPDATE ul_import_data_sub_ctgr_product A
164 SET status = vFail,
165 message = message || 'Flag year made can only be filled with Y or N, '
166 WHERE A.upload_header_id = pUploadHeaderId
167 AND A.flag_year_made NOT IN (vYes, vNo);
168
169 UPDATE ul_import_data_sub_ctgr_product A
170 SET status = vFail,
171 message = message || 'Active can only be filled with Y or N, '
172 WHERE A.upload_header_id = pUploadHeaderId
173 AND A.active NOT IN (vYes, vNo);
174
175 -- # List pada CSV yang diupload, tidak boleh duplikat by Kode sub kategori
176 WITH duplicate_data_sub_ctgr_product AS (
177 SELECT A.kode_sub_kategori
178 FROM ul_import_data_sub_ctgr_product A
179 WHERE A.upload_header_id = pUploadHeaderId
180 AND TRIM(A.kode_sub_kategori) <> vEmpty
181 GROUP BY A.kode_sub_kategori
182 HAVING COUNT(1) > 1
183 )
184 UPDATE ul_import_data_sub_ctgr_product A
185 SET status = vFail,
186 message = message || 'Sub categori code is duplicate, '
187 WHERE A.upload_header_id = pUploadHeaderId
188 AND EXISTS (
189 SELECT 1 FROM duplicate_data_sub_ctgr_product B WHERE A.kode_sub_kategori = B.kode_sub_kategori
190 );
191
192 -- Cek apakah ada item CSV yang statusnya fail
193 IF EXISTS ( SELECT 1 FROM ul_import_data_sub_ctgr_product B
194 WHERE B.upload_header_id = pUploadHeaderId
195 AND B.status = vFail ) THEN
196
197 -- Sesuaikan message, agar message paling belakang tidak ada koma
198 UPDATE ul_import_data_sub_ctgr_product A
199 SET message = substr(A.message, 1, length(A.message)-2)
200 WHERE A.upload_header_id = pUploadHeaderId
201 AND A.status = vFail
202 AND TRIM(A.message) != vEmpty;
203
204 -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
205 -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
206 -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
207 UPDATE ul_import_data_sub_ctgr_product A
208 SET status = vFail,
209 update_status = vActionError
210 WHERE upload_header_id = pUploadHeaderId;
211
212 -- Hitung jumlah data yang FAIL
213 SELECT COUNT(1) INTO vCountFail
214 FROM ul_import_data_sub_ctgr_product
215 WHERE upload_header_id = pUploadHeaderId
216 AND status = vFail;
217
218 ELSE
219
220 -- Update status non FAIL to OK
221 UPDATE ul_import_data_sub_ctgr_product
222 SET status = vOk
223 WHERE upload_header_id = pUploadHeaderId;
224
225 -- UPDATE OK data untuk brand code terdaftar
226 WITH update_data_sub_ctgr_product AS (
227 UPDATE m_sub_ctgr_product A SET
228 sub_ctgr_product_name=B.nama_sub_kategori,
229 ctgr_product_id=C.ctgr_product_id,
230 flg_serial_number=B.flag_serial_number,
231 flg_warranty=B.flag_warranty,
232 flg_barcode=B.flag_barcode,
233 flg_expired=B.flag_expired,
234 flg_weight=B.flag_weight,
235 flg_dimension=B.flag_dimension,
236 flg_spec=B.flag_spec,
237 flg_parts=B.flag_parts,
238 update_datetime=vDatetime,
239 update_user_id=vUserId,
240 version=A.version+1,
241 active=B.active,
242 active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
243 THEN vDatetime
244 ELSE A.active_datetime
245 END,
246 non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
247 THEN vDatetime
248 ELSE A.non_active_datetime
249 END
250 FROM ul_import_data_sub_ctgr_product B
251 INNER JOIN m_ctgr_product C ON B.kode_kategori = C.ctgr_product_code AND C.tenant_id = vTenantId
252 WHERE B.upload_header_id = pUploadHeaderId
253 AND B.status = vOk
254 AND A.sub_ctgr_product_code = B.kode_sub_kategori
255 AND A.tenant_id = vTenantId
256
257 RETURNING A.*
258 )
259 -- Ubah update_status menjadi U jika data digunakan untuk Update
260 UPDATE ul_import_data_sub_ctgr_product A SET
261 update_status = vActionUpdate
262 WHERE A.upload_header_id = pUploadHeaderId
263 AND A.status = vOk
264 AND EXISTS (
265 SELECT 1 FROM update_data_sub_ctgr_product B WHERE A.kode_sub_kategori = B.sub_ctgr_product_code AND B.tenant_id = vTenantId
266 );
267
268 -- INSERT OK data untuk brand code baru
269 WITH insert_data_sub_ctgr_product AS (
270 INSERT INTO m_sub_ctgr_product(
271 tenant_id, sub_ctgr_product_code, sub_ctgr_product_name,
272 ctgr_product_id, flg_serial_number, flg_warranty, flg_lot, flg_barcode,
273 flg_expired, flg_weight, flg_dimension, flg_spec, flg_cust_info,
274 flg_supp_info, flg_parts, create_datetime, create_user_id, update_datetime,
275 update_user_id, version, active, active_datetime, non_active_datetime,
276 flg_year_made)
277 SELECT vTenantId, A.kode_sub_kategori, A.nama_sub_kategori,
278 B.ctgr_product_id, A.flag_serial_number, A.flag_warranty, vNo, A.flag_barcode,
279 A.flag_expired, A.flag_weight, A.flag_dimension, A.flag_spec, vNo,
280 vNo, A.flag_parts, vDatetime, vUserId, vDatetime,
281 vUserId, 0, A.active,
282 CASE WHEN A.active = vYes
283 THEN vDatetime
284 ELSE vSpaceValue
285 END AS active_datetime,
286 CASE WHEN A.active = vNo
287 THEN vDatetime
288 ELSE vSpaceValue
289 END AS non_active_datetime,
290 A.flag_year_made
291 FROM ul_import_data_sub_ctgr_product A
292 INNER JOIN m_ctgr_product B ON A.kode_kategori = B.ctgr_product_code AND B.tenant_id = vTenantId
293 WHERE A.upload_header_id = pUploadHeaderId
294 AND A.status = vOk
295 AND NOT EXISTS (
296 SELECT 1 FROM m_sub_ctgr_product Z
297 WHERE A.kode_sub_kategori = Z.sub_ctgr_product_code AND Z.tenant_id = vTenantId
298 )
299
300 RETURNING *
301 )
302 -- Ubah update_status menjadi I jika data digunakan untuk Insert
303 UPDATE ul_import_data_sub_ctgr_product A SET
304 update_status = vActionInsert
305 WHERE A.upload_header_id = pUploadHeaderId
306 AND A.status = vOk
307 AND EXISTS (
308 SELECT 1 FROM insert_data_sub_ctgr_product B WHERE A.kode_sub_kategori = B.sub_ctgr_product_code AND B.tenant_id = vTenantId
309 );
310
311 -- DELETE Temp Table
312 DELETE FROM tt_sub_ctgr_product_data_log WHERE session_id = vSessionId;
313
314 -- INSERT ke Table tt_sub_ctgr_product_data_log untuk mode log A
315 INSERT INTO tt_sub_ctgr_product_data_log(
316 sub_ctgr_product_id, tenant_id, session_id, mode_log, ou_id,
317 sub_ctgr_product_code, sub_ctgr_product_name, ctgr_product_id,
318 flg_serial_number, flg_warranty, flg_lot,
319 flg_barcode, flg_expired, flg_weight,
320 flg_dimension, flg_spec, flg_cust_info,
321 flg_supp_info, flg_parts, flg_year_made,
322 parent_id,
323 active, active_datetime, non_active_datetime,
324 create_datetime, create_user_id, update_datetime, update_user_id, version
325 )
326 SELECT A.sub_ctgr_product_id, A.tenant_id, vSessionId, vModeLogAdd, vNullValueLong,
327 vSpaceValue, A.sub_ctgr_product_name, A.ctgr_product_id,
328 A.flg_serial_number, A.flg_warranty, vNo,
329 A.flg_barcode, A.flg_expired, A.flg_weight,
330 A.flg_dimension, A.flg_spec, A.flg_cust_info,
331 A.flg_supp_info, A.flg_parts, A.flg_year_made,
332 A.parent_id,
333 A.active, A.active_datetime, A.non_active_datetime,
334 A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id, A.version
335 FROM m_sub_ctgr_product A
336 WHERE EXISTS(
337 SELECT 1
338 FROM ul_import_data_sub_ctgr_product Z
339 WHERE Z.upload_header_id = pUploadHeaderId
340 AND Z.status = vOk
341 AND Z.kode_sub_kategori = A.sub_ctgr_product_code
342 AND update_status = vActionInsert
343 );
344
345 -- INSERT ke Table tt_sub_ctgr_product_data_log untuk mode log U
346 INSERT INTO tt_sub_ctgr_product_data_log(
347 sub_ctgr_product_id, tenant_id, session_id, mode_log, ou_id,
348 sub_ctgr_product_code, sub_ctgr_product_name, ctgr_product_id,
349 flg_serial_number, flg_warranty, flg_lot,
350 flg_barcode, flg_expired, flg_weight,
351 flg_dimension, flg_spec, flg_cust_info,
352 flg_supp_info, flg_parts, flg_year_made,
353 parent_id,
354 active, active_datetime, non_active_datetime,
355 create_datetime, create_user_id, update_datetime, update_user_id, version
356 )
357 SELECT A.sub_ctgr_product_id, A.tenant_id, vSessionId, vModeLogUpdate, vNullValueLong,
358 A.sub_ctgr_product_code, A.sub_ctgr_product_name, A.ctgr_product_id,
359 A.flg_serial_number, A.flg_warranty, vNo,
360 A.flg_barcode, A.flg_expired, A.flg_weight,
361 A.flg_dimension, A.flg_spec, A.flg_cust_info,
362 A.flg_supp_info, A.flg_parts, A.flg_year_made,
363 A.parent_id,
364 A.active, A.active_datetime, A.non_active_datetime,
365 A.create_datetime, A.create_user_id, A.update_datetime, A.update_user_id, A.version
366 FROM m_sub_ctgr_product A
367 WHERE EXISTS(
368 SELECT 1
369 FROM ul_import_data_sub_ctgr_product Z
370 WHERE Z.upload_header_id = pUploadHeaderId
371 AND Z.status = vOk
372 AND Z.kode_sub_kategori = A.sub_ctgr_product_code
373 AND update_status IN (vActionInsert, vActionUpdate)
374 );
375
376 -- EXECUTE f_generate_data_log
377 PERFORM f_generate_data_log_all_ou(vTenantId, vSessionId, vTypeData);
378
379 -- DELETE Temp Table
380 DELETE FROM tt_sub_ctgr_product_data_log WHERE session_id = vSessionId;
381
382 END IF;
383
384 RETURN vCountFail;
385
386END;
387$BODY$
388 LANGUAGE plpgsql VOLATILE
389 COST 100;
390 /