· 5 years ago · Oct 07, 2020, 12:16 PM
1DROP FUNCTION IF EXISTS f_update_and_insert_product_list_from_wiser(bigint,character varying,bigint,character varying);
2CREATE OR REPLACE FUNCTION f_update_and_insert_product_list_from_wiser(bigint, character varying, bigint, character varying)
3 RETURNS void AS
4$BODY$
5DECLARE
6 --pRefCountResult REFCURSOR := 'refCountResult';
7
8 pTenantId ALIAS FOR $1;
9 pSessionId ALIAS FOR $2;
10 pUserId ALIAS FOR $3;
11 pDatetime ALIAS FOR $4;
12
13 vYes character varying := 'Y';
14 vNo character varying := 'N';
15 vEmptyId BIGINT := -99;
16 vZeroValue BIGINT := 0;
17 vGroupProductFGId BIGINT := -99;
18 vGroupProductSERVICEId BIGINT := -99;
19 vActionAdd character varying := 'A';
20 vActionUpdate character varying := 'U';
21 vEmptyString character varying := '';
22 vGroupProductCodeFG character varying := 'FG';
23 vGroupProductCodeSERVICE character varying := 'SERVICE';
24 vUomClassQTY character varying := 'QTY';
25 vCountInsert BIGINT := -99;
26 vCountUpdate BIGINT := -99;
27
28BEGIN
29
30
31 -- Get Group Product ID berdasarkan Group Product Code "FG"
32 SELECT A.group_product_id FROM m_group_product A WHERE A.group_product_code = vGroupProductCodeFG AND tenant_id = pTenantId INTO vGroupProductFGId;
33
34 -- Get Group Product ID berdasarkan Group Product Code "SERVICE"
35 SELECT A.group_product_id FROM m_group_product A WHERE A.group_product_code = vGroupProductCodeSERVICE AND tenant_id = pTenantId INTO vGroupProductSERVICEId;
36
37
38 --UPDATE ACTION PADA DATA PRODUK YANG TERDAPAT PADA TABLE MASTER PRODUK MENJADI U (UPDATE) DAN UPDATE PRODUCT ID DARI m_product
39 UPDATE tt_migrate_product_from_wiser Z
40 SET action = vActionUpdate, product_id = A.product_id
41 FROM m_product A
42 WHERE A.tenant_id = pTenantId
43 AND A.product_code = Z.product_code
44 AND Z.session_id = pSessionId;
45
46 --UPDATE ACTION PADA SEMUA DATA MENJADI A (ADD) DAN ADD product_id DENGAN SEQUENCE UNTUK DATA PRODUK YANG BELUM ADA DI TABLE m_product
47 UPDATE tt_migrate_product_from_wiser Z
48 SET action = vActionAdd, product_id = nextval('product_seq')
49 WHERE Z.session_id = pSessionId
50 AND NOT EXISTS(
51 SELECT 1 FROM m_product X
52 WHERE X.tenant_id = pTenantId
53 AND X.product_code = Z.product_code
54 );
55
56 --INSERT DATA BRAND YANG DATA BRAND NYA TIDAK TERDAFTAR DI MASTER BRAND m_brand
57 INSERT INTO m_brand
58 (tenant_id, brand_code, brand_name, create_datetime,
59 create_user_id, update_datetime, update_user_id, version, active,
60 active_datetime, non_active_datetime)
61 SELECT pTenantId, A.brand_name, A.brand_name, pDatetime, pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vEmptyString
62 FROM tt_migrate_product_from_wiser A
63 WHERE A.session_id = pSessionId
64 AND NOT EXISTS(
65 SELECT 1 FROM m_brand X
66 WHERE X.tenant_id = pTenantId
67 AND X.brand_code = A.brand_name
68 );
69
70 --INSERT DATA KATEGORI PRODUK YANG DATA KATEGORI PRODUK NYA TIDAK TERDAFTAR DI MASTER KATEGORI PRODUK m_ctgr_product
71 INSERT INTO m_ctgr_product
72 (tenant_id, ctgr_product_code, ctgr_product_name, group_product_id,
73 create_datetime, create_user_id, update_datetime, update_user_id,
74 version, active, active_datetime, non_active_datetime)
75 SELECT pTenantId, A.category_product_name, A.category_product_name,
76 CASE WHEN A.flag_jasa = vYes THEN vGroupProductSERVICEId
77 WHEN A.flag_jasa = vNo THEN vGroupProductFGId
78 END,
79 pDatetime, pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vEmptyString
80 FROM tt_migrate_product_from_wiser A
81 WHERE A.session_id = pSessionId
82 AND NOT EXISTS(
83 SELECT 1 FROM m_ctgr_product X
84 WHERE X.tenant_id = pTenantId
85 AND X.ctgr_product_code = A.category_product_name
86 );
87
88 --INSERT DATA SUB KATEGORI PRODUK YANG DATA SUB KATEGORI PRODUK NYA TIDAK TERDAFTAR DI MASTER SUB KATEGORI PRODUK m_sub_ctgr_product
89 INSERT INTO m_sub_ctgr_product
90 (tenant_id, sub_ctgr_product_code, sub_ctgr_product_name, ctgr_product_id,
91 flg_serial_number, flg_warranty, flg_lot, flg_barcode, flg_expired, flg_weight,
92 flg_dimension, flg_spec, flg_cust_info, flg_supp_info, flg_parts,
93 create_datetime, create_user_id, update_datetime, update_user_id, version, active, active_datetime, non_active_datetime,
94 flg_year_made)
95 SELECT pTenantId, A.sub_category_product_name, A.sub_category_product_name, B.ctgr_product_id,
96 vNo, vNo, vNo, vNo, vNo, vNo,
97 vNo, vYes, vNo, vNo, vNo,
98 pDatetime, pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vEmptyString, vNo
99 FROM tt_migrate_product_from_wiser A
100 INNER JOIN m_ctgr_product B ON B.tenant_id = pTenantId AND B.ctgr_product_name = A.category_product_name
101 WHERE A.session_id = pSessionId
102 AND NOT EXISTS(
103 SELECT 1 FROM m_sub_ctgr_product X
104 INNER JOIN m_ctgr_product Y ON X.ctgr_product_id = Y.ctgr_product_id
105 WHERE X.tenant_id = pTenantId
106 AND X.sub_ctgr_product_code = A.sub_category_product_name
107 );
108
109 --INSERT DATA UOM YANG DATA UOM NYA TIDAK TERDAFTAR DI MASTER UOM m_uom
110 INSERT INTO m_uom
111 (tenant_id, uom_code, uom_name, uom_class,
112 create_datetime, create_user_id, update_datetime, update_user_id,
113 version, active, active_datetime, non_active_datetime)
114 SELECT pTenantId, A.uom_name, A.uom_name, vUomClassQTY,
115 pDatetime, pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vEmptyString
116 FROM tt_migrate_product_from_wiser A
117 WHERE A.session_id = pSessionId
118 AND NOT EXISTS(
119 SELECT 1 FROM m_uom X
120 WHERE X.tenant_id = pTenantId
121 AND X.uom_code = A.uom_name
122 );
123
124 --UPDATE DATA GROUP BRAND NAME DAN PARENT GROUP BRAND CODE YANG GROUP BRAND CODE NYA SUDAH TERDAFTAR DI MASTER GROUP BRAND (m_group_brand)
125 UPDATE m_group_brand Z
126 SET group_brand_name = A.group_brand_name, parent_group_brand = A.parent_group_brand_code,
127 version = (Z.version + 1), update_datetime = pDatetime, update_user_id = pUserId
128 FROM tt_migrate_product_from_wiser A
129 INNER JOIN m_group_brand B ON B.tenant_id = pTenantId AND B.group_brand_code = A.group_brand_code
130 WHERE A.session_id = pSessionId
131 AND Z.tenant_id = pTenantId
132 AND Z.group_brand_code = A.group_brand_code;
133
134 --INSERT DATA GROUP BRAND NAME DAN PARENT GROUP BRAND CODE YANG GROUP BRAND CODE NYA BELUM TERDAFTAR DI MASTER GROUP BRAND (m_group_brand)
135 INSERT INTO m_group_brand
136 (tenant_id, group_brand_code, group_brand_name, parent_group_brand, flg_genuine,
137 active, active_datetime, non_active_datetime, create_user_id, update_user_id, create_datetime, update_datetime,
138 version, term_of_payment)
139 --nextval('group_brand_seq')
140 SELECT pTenantId, A.group_brand_code, A.group_brand_name, A.parent_group_brand_code, vNo,
141 vYes,pDatetime, vEmptyString, pUserId, pUserId, pDatetime, pDatetime, 0, 0
142 FROM tt_migrate_product_from_wiser A
143 WHERE A.session_id = pSessionId
144 AND NOT EXISTS(
145 SELECT 1 FROM m_group_brand X
146 WHERE X.tenant_id = pTenantId
147 AND X.group_brand_code = A.group_brand_code
148 );
149
150 --UPDATE DATA PRODUCT YANG PRODUCT NYA SUDAH TERDAFTAR DI MASTER PRODUCT (m_product)
151 UPDATE m_product Z
152 SET product_name = A.product_name, ctgr_product_id = C.ctgr_product_id, sub_ctgr_product_id = D.sub_ctgr_product_id,
153 brand_id = E.brand_id, flg_buy = A.flag_buy, flg_sell = A.flag_sell,
154 version = (Z.version + 1), update_datetime = pDatetime, update_user_id = pUserId
155 FROM tt_migrate_product_from_wiser A
156 INNER JOIN m_product B ON B.tenant_id = pTenantId AND B.product_code = A.product_code
157 INNER JOIN m_ctgr_product C ON C.tenant_id = pTenantId AND C.ctgr_product_code = A.category_product_name
158 INNER JOIN m_sub_ctgr_product D ON D.tenant_id = pTenantId AND D.sub_ctgr_product_code = A.sub_category_product_name
159 INNER JOIN m_brand E ON E.tenant_id = pTenantId AND E.brand_code = A.brand_name
160 WHERE A.session_id = pSessionId
161 AND Z.tenant_id = pTenantId
162 AND Z.product_id = A.product_id;
163
164 --INSERT DATA PRODUCT YANG PRODUCT NYA BELUM TERDAFTAR DI MASTER PRODUCT (m_product)
165 INSERT INTO m_product
166 (product_id, tenant_id, product_code, product_name, ctgr_product_id,
167 sub_ctgr_product_id, brand_id, base_uom_id, uom_id_1, uom_id_2, uom_id_3,
168 flg_buy, flg_sell, min_qty, max_qty, create_datetime,
169 create_user_id, update_datetime, update_user_id, version, active,
170 active_datetime, non_active_datetime, class_product)
171 SELECT A.product_id, pTenantId, A.product_code, A.product_name, B.ctgr_product_id,
172 C.sub_ctgr_product_id, D.brand_id, E.uom_id, vEmptyId, vEmptyId, vEmptyId,
173 flag_buy, flag_sell, 0, 0, pDatetime, pUserId, pDatetime, pUserId, 0, vYes,
174 pDatetime, vEmptyString, vEmptyString
175 FROM tt_migrate_product_from_wiser A
176 INNER JOIN m_ctgr_product B ON B.tenant_id = pTenantId AND A.category_product_name = B.ctgr_product_code
177 INNER JOIN m_sub_ctgr_product C ON C.tenant_id = pTenantId AND A.sub_category_product_name = C.sub_ctgr_product_code
178 INNER JOIN m_brand D ON D.tenant_id = pTenantId AND D.brand_code = A.brand_name
179 INNER JOIN m_uom E ON E.tenant_id = pTenantId AND E.uom_code = A.uom_name
180 WHERE A.session_id = pSessionId
181 AND NOT EXISTS(
182 SELECT 1 FROM m_product X
183 WHERE X.tenant_id = pTenantId
184 AND X.product_code = A.product_code
185 );
186
187
188
189 ----UPDATE DAN INSERT KE TABLE PENDUKUNG----
190 --------------------------------------------
191 --UPDATE KE TABLE PENDUKUNG (m_product_barcode_group) JIKA SUDAH ADA PRODUK YANG SAMA
192 UPDATE m_product_barcode_group Z
193 SET single_barcode = A.barcode_satuan, group_barcode = A.barcode_koli, group_qty = A.barcode_koli_qty,
194 version = (Z.version + 1), update_datetime = pDatetime, update_user_id = pUserId
195 FROM tt_migrate_product_from_wiser A
196 WHERE A.session_id = pSessionId
197 AND Z.tenant_id = pTenantId
198 AND Z.product_id = A.product_id;
199
200 --INSERT KE TABLE PENDUKUNG (m_product_barcode_group) JIKA BELUM ADA DATA PRODUK DAN BARCODE YANG SAMA
201 INSERT INTO m_product_barcode_group
202 (product_id, tenant_id, single_barcode, group_barcode, group_qty,
203 create_datetime, create_user_id, update_datetime, update_user_id,
204 version, active, active_datetime, non_active_datetime)
205 SELECT A.product_id, pTenantId, A.barcode_satuan, A.barcode_koli, A.barcode_koli_qty,
206 pDatetime, pUserId, pDatetime, pUserId,
207 0, vYes, pDatetime, vEmptyString
208 FROM tt_migrate_product_from_wiser A
209 WHERE A.session_id = pSessionId
210 AND NOT EXISTS(
211 SELECT 1 FROM m_product_barcode_group X
212 WHERE X.tenant_id = pTenantId
213 AND X.product_id = A.product_id
214 );
215
216 --INSERT KE TABLE PENDUKUNG (m_product_weight_dimension) JIKA BELUM ADA DATA PRODUCT WEIGHT DIMENSION BERDASARKAN PRODUCT YANG SAMA
217 INSERT INTO m_product_weight_dimension
218 (product_id, tenant_id, weight, dimension_length, dimension_width,
219 dimension_height, create_datetime, create_user_id, update_datetime,
220 update_user_id, version, active, active_datetime, non_active_datetime)
221 SELECT A.product_id, pTenantId, 0.0000000000, 0.00, 0.00,
222 0.00, pDatetime, pUserId, pDatetime, pUserId, 0, vYes, pDatetime, vEmptyString
223 FROM tt_migrate_product_from_wiser A
224 WHERE A.session_id = pSessionId
225 AND NOT EXISTS(
226 SELECT 1 FROM m_product_weight_dimension X
227 WHERE X.tenant_id = pTenantId
228 AND X.product_id = A.product_id
229 );
230
231 --INSERT KE TABLE PENDUKUNG (m_product_consignment_supp_info) JIKA BELUM ADA PRODUK YANG SAMA
232 INSERT INTO m_product_consignment_supp_info
233 (product_id, supplier_id, supplier_product_code, create_datetime,
234 create_user_id, update_datetime, update_user_id, version, active,
235 active_datetime, non_active_datetime)
236 SELECT A.product_id, vEmptyId, vEmptyString,
237 pDatetime, pUserId, pDatetime, pUserId,
238 0, vYes, pDatetime, vEmptyString
239 FROM tt_migrate_product_from_wiser A
240 WHERE A.session_id = pSessionId
241 AND NOT EXISTS(
242 SELECT 1 FROM m_product_consignment_supp_info X
243 WHERE X.product_id = A.product_id
244 );
245
246 --INSERT KE TABLE PENDUKUNG (m_product_custom) JIKA BELUM ADA PRODUK YANG SAMA
247 INSERT INTO m_product_custom
248 (product_id, flg_buy_konsinyasi, style_product, create_datetime,
249 create_user_id, update_datetime, update_user_id, version, active,
250 active_datetime, non_active_datetime, color, size)
251 SELECT A.product_id, vNo, vEmptyString,
252 pDatetime, pUserId, pDatetime, pUserId,
253 0, vYes, pDatetime, vEmptyString, vEmptyString, vEmptyString
254 FROM tt_migrate_product_from_wiser A
255 WHERE A.session_id = pSessionId
256 AND NOT EXISTS(
257 SELECT 1 FROM m_product_custom X
258 WHERE X.product_id = A.product_id
259 );
260
261 --UPDATE KE TABLE PENDUKUNG (m_product_custom_for_sasa) JIKA SUDAH ADA PRODUK YANG SAMA
262 UPDATE m_product_custom_for_sasa Z
263 SET group_brand_id = B.group_brand_id,
264 version = (Z.version + 1), update_datetime = pDatetime, update_user_id = pUserId
265 FROM tt_migrate_product_from_wiser A
266 INNER JOIN m_group_brand B ON B.tenant_id = pTenantId AND B.group_brand_code = A.group_brand_code
267 WHERE A.session_id = pSessionId
268 AND Z.tenant_id = pTenantId
269 AND Z.product_id = A.product_id;
270
271 --INSERT KE TABLE PENDUKUNG (m_product_custom_for_sasa) JIKA BELUM ADA PRODUK YANG SAMA
272 INSERT INTO m_product_custom_for_sasa
273 (product_id, tenant_id, group_brand_id, create_datetime, create_user_id,
274 update_datetime, update_user_id, version, supplier_id)
275 SELECT A.product_id, pTenantId, B.group_brand_id,
276 pDatetime, pUserId, pDatetime, pUserId,
277 0, vEmptyId
278 FROM tt_migrate_product_from_wiser A
279 INNER JOIN m_group_brand B ON B.tenant_id = pTenantId AND B.group_brand_code = A.group_brand_code
280 WHERE A.session_id = pSessionId
281 AND NOT EXISTS(
282 SELECT 1 FROM m_product_custom_for_sasa X
283 WHERE X.tenant_id = pTenantId
284 AND X.product_id = A.product_id
285 );
286
287
288
289END;
290$BODY$
291 LANGUAGE plpgsql VOLATILE
292 COST 100;
293/